Function Report Kartu Hutang

CREATE OR REPLACE FUNCTION rpt.r_report_kartu_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;

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

    vYearMonth                  character varying := '';
    vFilterSupplier             character varying := '';

BEGIN

    IF pSupplier <> vEmpty THEN
        vFilterSupplier := ' AND ( UPPER(f_get_supplier_code(A.supplier_id)) LIKE UPPER(''%'|| pSupplier ||'%'') OR UPPER(f_get_supplier_name(A.supplier_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;

    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_supplier_code(A.supplier_id) AS supplier_code, f_get_supplier_name(A.supplier_id) AS supplier_name,
                B.invoice_amount, B.payment_amount, B.invoice_amount - B.payment_amount AS balance_amount
        FROM fi_invoice_ap A
        INNER JOIN fi_invoice_ap_balance B ON A.invoice_ap_id = B.invoice_ap_id
        WHERE A.record_owner_id = $1
            ' || vFilterSupplier|| '
            AND A.doc_date BETWEEN $2 AND $3
        ORDER BY A.doc_date, A.doc_no DESC'
    USING pRecordOwnerId, pDateFrom, pDateTo;
    RETURN NEXT pRefDetail;

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