Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION rpt.r_rekap_hutang(CHARACTER VARYING, BIGINT, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetail REFCURSOR := 'refDetail';
- pSessionId ALIAS FOR $1;
- pRecordOwnerId ALIAS FOR $2;
- pSupplier ALIAS FOR $3;
- pDateFrom ALIAS FOR $4;
- pDateTo ALIAS FOR $5;
- vEmptyId BIGINT := -99;
- vEmpty TEXT := '';
- vNone CHARACTER VARYING := '-';
- vNo CHARACTER VARYING := 'N';
- vYes CHARACTER VARYING := 'Y';
- vDateNow CHARACTER VARYING := '';
- vFilterSupplier CHARACTER VARYING := '';
- BEGIN
- DELETE FROM tt_summary_hutang_piutang WHERE session_id = pSessionId;
- -- hari sekarang
- SELECT TO_CHAR(NOW(),'YYYYMMDD') INTO vDateNow;
- IF pSupplier <> vEmpty THEN
- vFilterSupplier := ' AND ( UPPER(f_get_supplier_code(partner_id)) LIKE UPPER(''%'|| pSupplier ||'%'') OR UPPER(f_get_supplier_name(partner_id)) LIKE UPPER(''%'|| pSupplier ||'%'') )';
- END IF;
- Open pRefHeader FOR
- SELECT f_get_record_owner_name(pRecordOwnerId) AS record_owner_name, pDateFrom AS date_from, pDateTo AS date_to;
- RETURN NEXT pRefHeader;
- /*
- * Ambil hutang lancar dimana due date lebih besar sama dengan hari ini
- */
- INSERT INTO tt_summary_hutang_piutang (
- session_id, partner_id, record_owner_id, before_due_amount, over_due_amount, payment_amount
- )
- SELECT pSessionId, A.supplier_id, A.record_owner_id, COALESCE(SUM(B.invoice_amount), 0), 0, 0
- FROM fi_invoice_ap A
- INNER JOIN fi_invoice_ap_balance B ON A.invoice_ap_id = A.invoice_ap_id
- WHERE A.record_owner_id = pRecordOwnerId
- AND A.doc_date BETWEEN pDateFrom AND pDateTo
- AND A.due_date >= vDateNow
- GROUP BY A.supplier_id, A.record_owner_id;
- /**
- * Ambil Hutang sudah jatuh tempo dimana due date lebih kecil dari hari ini
- */
- INSERT INTO tt_summary_hutang_piutang (
- session_id, partner_id, record_owner_id, before_due_amount, over_due_amount, payment_amount
- )
- SELECT pSessionId, A.supplier_id, A.record_owner_id, 0, COALESCE(SUM(B.invoice_amount), 0), 0
- FROM fi_invoice_ap A
- INNER JOIN fi_invoice_ap_balance B ON A.invoice_ap_id = A.invoice_ap_id
- WHERE A.record_owner_id = pRecordOwnerId
- AND A.doc_date BETWEEN pDateFrom AND pDateTo
- AND A.due_date < vDateNow
- GROUP BY A.supplier_id, A.record_owner_id;
- /**
- * Ambil semua hutang yang sudah dibayar
- */
- INSERT INTO tt_summary_hutang_piutang (
- session_id, partner_id, record_owner_id, before_due_amount, over_due_amount, payment_amount
- )
- SELECT pSessionId, A.supplier_id, A.record_owner_id, 0, 0, COALESCE(SUM(B.payment_amount), 0)
- FROM fi_invoice_ap A
- INNER JOIN fi_invoice_ap_balance B ON A.invoice_ap_id = A.invoice_ap_id
- WHERE A.record_owner_id = pRecordOwnerId
- AND A.doc_date BETWEEN pDateFrom AND pDateTo
- GROUP BY A.supplier_id, A.record_owner_id;
- OPEN pRefDetail FOR
- EXECUTE '
- SELECT f_get_supplier_code(partner_id) AS partner_code, f_get_supplier_name(partner_id) AS partner_name,
- COALESCE(SUM(before_due_amount), 0) AS before_due_amount, COALESCE(SUM(over_due_amount), 0) AS over_due_amount,
- COALESCE(SUM(payment_amount), 0) AS payment_amount
- FROM tt_summary_hutang_piutang
- WHERE session_id = $1
- ' || vFilterSupplier || '
- GROUP BY partner_id
- ORDER BY partner_name'
- USING pSessionId;
- RETURN NEXT pRefDetail;
- DELETE FROM tt_summary_hutang_piutang WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- SET search_path = public, mstr, ptg, htg, pb, pj, batch
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement