r_claim_note_vs_return_purch_invoice_list

/**
 * Adrian, Nov 22, 2017
 * Menampilkan Claim Note
 * dan Return Purchase Invoice yang dibuat berdasarkan Claim Note tersebut
 */
/**
 * Adrian, Dec 21, 2017
 * Menampikan PO, status Penagihan, dan status Pembayaran
 */
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, 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;
    pClaimNoteStatus        ALIAS FOR $11;
    pReturnPurchInvStatus   ALIAS FOR $12;


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

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

    vStatusAll                      character varying := 'ALL STATUS';
    vStatusApproved                 character varying := 'APPROVED';
    vStatusApprovedOnly             character varying := 'APPROVED ONLY';
    vStatusDraft                    character varying := 'DRAFT';
    vStatusInProgress               character varying := 'IN PROGRESS';
    vStatusDraftInProgress          character varying := 'DRAFT/IN PROGRESS';
    vStatusUnpaid                   character varying := 'UNPAID';
    vStatusPaid                     character varying := 'PAID';
    vStatusNotCreatedYet            character varying := 'NOT CREATED YET';
    vFilterClaimNoteStatus          text := '';
    vFilterReturnPurchInvStatus     text := '';
    vStatusDocDraft                 character varying := 'D';
    vStatusDocInProgress            character varying := 'I';
    vFlgYes                         character varying := 'Y';

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;

    IF (pClaimNoteStatus = vStatusApprovedOnly) THEN
        vFilterClaimNoteStatus := ' AND A.status_doc = ''' || vStatusDocRelease || ''' ';
    END IF;

    IF (pReturnPurchInvStatus = vStatusDraftInProgress) THEN
        vFilterReturnPurchInvStatus := ' AND (D.status_doc IN ( ''' || vStatusDocDraft || ''' , ''' || vStatusDocInProgress || ''' )) ';
    ELSIF (pReturnPurchInvStatus = vStatusUnpaid) THEN
        vFilterReturnPurchInvStatus := ' AND (G.flg_payment IS NULL OR H.flg_payment IS NULL OR G.flg_payment <> ''' || vFlgYes || ''' OR H.flg_payment <> ''' || vFlgYes || ''' ) ';
    ELSIF (pReturnPurchInvStatus = vStatusPaid) THEN
        vFilterReturnPurchInvStatus := ' AND (G.flg_payment = ''' || vFlgYes || ''' OR   H.flg_payment = ''' || vFlgYes || ''' ) ';
    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,
            pClaimNoteStatus AS claim_note_status,
            pReturnPurchInvStatus AS return_purch_inv_status,
            f_get_username(pUserId)AS username, pDatetime AS datetime;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
        EXECUTE
            'SELECT A.doc_date, A.doc_no,
                CASE WHEN A.status_doc = $9 THEN
                    $10
                WHEN A.status_doc = $11 THEN
                    $12
                WHEN A.status_doc = $4 THEN
                    $13
                ELSE
                    $7
                END AS doc_status,
                f_get_partner_code(A.partner_id) AS partner_code, f_get_partner_name(A.partner_id) AS partner_name,
                F.doc_date AS po_date, F.doc_no AS po_no,
                CASE WHEN D.status_doc IS NULL THEN
                    $8
                WHEN D.status_doc = $9 THEN
                    $10
                WHEN D.status_doc = $11 THEN
                    $12
                WHEN D.status_doc = $4 THEN
                    $13
                ELSE
                    $7
                END AS invoice_status,
                COALESCE(D.doc_date, $7) AS rpi_date, COALESCE(D.doc_no, $7) AS rpi_no,
                E.tax_no, E.tax_date,
                CASE WHEN G.flg_payment IS NULL THEN
                    $14
                WHEN (G.flg_payment = $15 AND H.flg_payment = $15) THEN
                    $16
                ELSE
                    $14
                END AS paid_status,
                f_get_product_code(B.product_id) AS product_code, f_get_product_name(B.product_id)  AS product_name,
                COALESCE(C.qty_rcv_po, B.qty_realization * J.qty_po / J.qty_int) AS qty_rcv_po,
                COALESCE(C.po_uom_id, J.po_uom_id) AS po_uom_id, f_get_uom_code(COALESCE(C.po_uom_id, J.po_uom_id)) AS po_uom_code, f_get_uom_name(COALESCE(C.po_uom_id, J.po_uom_id)) AS po_uom_name,
                COALESCE(C.curr_code, J.curr_code) AS curr_code, COALESCE(C.price_po, J.gross_price_po - J.discount_amount) AS price_po
            FROM in_inventory A
            INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
            LEFT 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
            INNER JOIN pu_po F ON A.ref_doc_type_id = F.doc_type_id AND A.ref_id = F.po_id
            INNER JOIN in_balance_receive_goods_item I ON B.ref_item_id = I.receive_goods_item_id
            INNER JOIN pu_po_item J ON I.po_item_id = J.po_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
            LEFT JOIN fi_invoice_ap_balance G ON D.tenant_id = G.tenant_id AND D.doc_type_id = G.doc_type_id AND D.invoice_id = G.invoice_ap_id
            LEFT JOIN fi_invoice_tax_ap_balance H ON G.tenant_id = H.tenant_id AND G.invoice_ap_balance_id = H.invoice_ap_balance_id
            WHERE A.tenant_id = $1
                AND A.ou_from_id = $2
                AND A.doc_type_id = $3
                AND A.doc_date BETWEEN $5 AND $6 ' ||
                vFilterPartner ||
                vFilterProduct ||
                vFilterClaimNoteStatus ||
                vFilterReturnPurchInvStatus ||
            'ORDER BY A.doc_date'
        USING pTenantId, pOuId, vClaimNoteDocType, vStatusDocRelease, pDateFrom, pDateTo, vEmpty,
            vStatusNotCreatedYet,
            vStatusDocDraft, vStatusDraft,
            vStatusDocInProgress, vStatusDraftInProgress,
            vStatusApproved,
            vStatusUnpaid, vFlgYes, vStatusPaid;
    RETURN NEXT pRefDetail ;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
/