r_claim_note_vs_return_purch_invoice_list

/**
 * Adrian, Nov 22, 2017
 * Menampilkan Claim Note yang sudah APPROVED
 * dan Return Purchase Invoice yang dibuat berdasarkan Claim Note tersebut
 */
CREATE OR REPLACE FUNCTION r_claim_note_vs_return_purch_invoice_list(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader          REFCURSOR := 'refHeader';
    pRefDetail          REFCURSOR := 'refDetail';
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pUserId             ALIAS FOR $3;
    pRoleId             ALIAS FOR $4;
    pDatetime           ALIAS FOR $5;
    pOuId               ALIAS FOR $6;
    pDateFrom           ALIAS FOR $7;
    pDateTo             ALIAS FOR $8;
    pPartnerCodeName    ALIAS FOR $9;
    pProductCodeName    ALIAS FOR $10;


    vEmptyId                bigint := -99;
    vEmpty                  character varying := '';
    vFilterPartner          text := '';
    vFilterProduct          text := '';

    vClaimNoteDocType       bigint := 511;
    vStatusDocRelease       character varying := 'R';
BEGIN

    IF (pPartnerCodeName <> vEmpty) THEN
        vFilterPartner := ' AND ( UPPER(f_get_partner_code(A.partner_id)) LIKE ''%' || UPPER(pPartnerCodeName) || '%'' OR UPPER(f_get_partner_name(A.partner_id)) LIKE ''%' || UPPER(pPartnerCodeName) || '%'' ) ';
    END IF;

    IF (pProductCodeName <> vEmpty) THEN
        vFilterProduct := ' AND ( UPPER(f_get_product_code(B.product_id)) LIKE ''%' || UPPER(pProductCodeName) || '%'' OR UPPER(f_get_product_name(B.product_id)) LIKE ''%' || UPPER(pProductCodeName) || '%'' ) ';
    END IF;

    Open pRefHeader FOR
        SELECT pOuId AS ou_id, f_get_ou_code(pOuId) AS ou_code, f_get_ou_name(pOuId) AS ou_name,
            pDateFrom AS date_from, pDateTo AS date_to,
            pPartnerCodeName AS partner_code_name,
            pProductCodeName AS product_code_name,
            f_get_username(pUserId)AS username, pDatetime AS datetime;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
        EXECUTE
            'SELECT A.doc_date, A.doc_no,
                f_get_partner_code(A.partner_id) AS partner_code, f_get_partner_name(A.partner_id) AS partner_name,
                COALESCE(D.doc_date, $7) AS rpi_date, COALESCE(D.doc_no, $7) AS rpi_no,
                E.tax_no, E.tax_date,
                f_get_product_code(B.product_id) AS product_code, f_get_product_name(B.product_id)  AS product_name,
                C.qty_rcv_po,
                C.po_uom_id, f_get_uom_code(C.po_uom_id) AS po_uom_code, f_get_uom_name(C.po_uom_id) AS po_uom_name,
                C.curr_code, C.price_po
            FROM in_inventory A
            INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
            INNER JOIN pu_po_balance_invoice C ON A.doc_no = C.ref_doc_no AND A.inventory_id = C.ref_id AND B.ref_item_id = C.ref_item_id
            LEFT JOIN pu_invoice D ON A.ref_doc_type_id = D.ref_doc_type_id AND A.ref_id = D.ref_id AND C.invoice_id = D.invoice_id AND D.doc_type_id = 141
            LEFT JOIN pu_invoice_tax E ON D.invoice_id = E.invoice_id
            WHERE A.tenant_id = $1
                AND A.ou_from_id = $2
                AND A.doc_type_id = $3
                AND A.status_doc = $4
                AND A.doc_date BETWEEN $5 AND $6 ' ||
                vFilterPartner ||
                vFilterProduct ||
            'ORDER BY A.doc_date'
        USING pTenantId, pOuId, vClaimNoteDocType, vStatusDocRelease, pDateFrom, pDateTo, vEmpty;
    RETURN NEXT pRefDetail ;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
/