Laporan Kartu Piutang

CREATE OR REPLACE FUNCTION rpt.r_report_kartu_piutang(character varying, BIGINT, BIGINT, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader                  REFCURSOR := 'refHeader';
    pRefDetail                  REFCURSOR := 'refDetail';

    pSessionId                  ALIAS FOR $1;
    pRecordOwnerId              ALIAS FOR $2;
    pCustomerId                 ALIAS FOR $3;
    pDate                       ALIAS FOR $4;

    vEmptyId                    bigint := -99;
    vNullLongId                 bigint := -1;

    vEmpty                      character varying := '';
    vNo                         character varying := 'N';
    vYes                        character varying := 'Y';

    vYearMonth                  character varying := '';
    vFilerCustomer              character varying := '';

BEGIN

    IF pCustomerId <> vNullLongId THEN
        vFilerCustomer := ' AND A.customer_id =  '''|| pCustomerId ||'''';
    END IF;

    OPEN pRefHeader FOR
        SELECT f_get_record_owner_name(pRecordOwnerId) AS record_owner_name, CASE WHEN pCustomerId <> vNullLongId
            THEN concat(f_get_customer_code(pCustomerId), ' - ', f_get_customer_name(pCustomerId)) ELSE 'ALL' END AS customer,
            pDate AS date;
    RETURN NEXT pRefHeader;

    OPEN pRefDetail FOR
    EXECUTE '
        SELECT A.doc_date, A.doc_no, f_get_doc_desc(A.doc_type_id) AS doc_desc, A.due_date,
                f_get_customer_code(A.customer_id) AS customer_code, f_get_customer_name(A.customer_id) AS customer_name,
                B.invoice_amount, B.payment_amount, B.invoice_amount - B.payment_amount AS balance_amount
        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 = $1
            ' || vFilerCustomer|| '
            AND A.doc_date <= $2
        ORDER BY A.doc_date, A.doc_no DESC'
    USING pRecordOwnerId, pDate;
    RETURN NEXT pRefDetail;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  SET search_path = public, mstr, ptg, htg, pb, pj, batch
  COST 100;
  /