Function Report Rekap Piutang

CREATE OR REPLACE FUNCTION rpt.r_rekap_piutang(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;
    pCustomer               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 := '';
    vFilterCustomer         CHARACTER VARYING := '';

BEGIN

    DELETE FROM tt_summary_hutang_piutang WHERE session_id = pSessionId;

    -- hari sekarang
    SELECT TO_CHAR(NOW(),'YYYYMMDD') INTO vDateNow;

    IF pCustomer <> vEmpty THEN
        vFilterCustomer := ' AND ( UPPER(f_get_customer_code(partner_id)) LIKE UPPER(''%'|| pCustomer ||'%'') OR UPPER(f_get_customer_name(partner_id)) LIKE UPPER(''%'|| pCustomer ||'%'') )';
    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.customer_id, A.record_owner_id, COALESCE(SUM(B.invoice_amount), 0), 0, 0
    FROM fi_invoice_ar A
    INNER JOIN fi_invoice_ar_balance B ON A.invoice_ar_id = B.invoice_ar_id
    WHERE A.record_owner_id = pRecordOwnerId
        AND A.doc_date BETWEEN pDateFrom AND pDateTo
        AND A.due_date >= vDateNow
    GROUP BY A.customer_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.customer_id, A.record_owner_id, 0, COALESCE(SUM(B.invoice_amount), 0), 0
    FROM fi_invoice_ar A
    INNER JOIN fi_invoice_ar_balance B ON A.invoice_ar_id = B.invoice_ar_id
    WHERE A.record_owner_id = pRecordOwnerId
        AND A.doc_date BETWEEN pDateFrom AND pDateTo
        AND A.due_date < vDateNow
    GROUP BY A.customer_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.customer_id, A.record_owner_id, 0, 0, COALESCE(SUM(B.payment_amount), 0)
    FROM fi_invoice_ar A
    INNER JOIN fi_invoice_ar_balance B ON A.invoice_ar_id = B.invoice_ar_id
    WHERE A.record_owner_id = pRecordOwnerId
        AND A.doc_date BETWEEN pDateFrom AND pDateTo
    GROUP BY A.customer_id, A.record_owner_id;

    OPEN pRefDetail FOR
        EXECUTE '
            SELECT f_get_customer_code(partner_id) AS customer_code, f_get_customer_name(partner_id) AS customer_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
                ' || vFilterCustomer || '
            GROUP BY partner_id
            ORDER BY customer_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;
  /