--Adrian, Jan 30 2017

CREATE OR REPLACE FUNCTION r_tagging_po_internal_so_igs(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, bigint, 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;

    pPoInternalOuId     ALIAS FOR $6;
    pPoInternalNo       ALIAS FOR $7;
    pPoNo               ALIAS FOR $8;
    pSoOuId             ALIAS FOR $9;
    pSoNo               ALIAS FOR $10;

    vFilterPoInternalNo text := '';
    vFilterPoNo         text := '';
    vFilterSoNo         text := '';

    vEmptyString        character varying := '';

BEGIN

    IF (pPoInternalNo <> vEmptyString) THEN
        vFilterPoInternalNo := ' AND A.doc_no = ' || pPoInternalNo || '';
    END IF;

    Open pRefHeader FOR
    SELECT pPoInternalOuId AS po_internal_ou_id, f_get_ou_code(pPoInternalOuId) AS po_internal_ou_code, f_get_ou_name(pPoInternalOuId) AS po_internal_ou_name,
        pPoInternalNo AS po_internal_no, pPoNo AS po_no,
        pSoOuId AS so_ou_id, f_get_ou_code(pSoOuId) AS so_ou_code, f_get_ou_name(pSoOuId) AS so_ou_name,
        pSoNo AS so_no;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
    EXECUTE '
    SELECT C.doc_no AS so_no, C.doc_date AS so_date,
        f_get_product_code(D.product_id) AS product_code, f_get_product_name(D.product_id) AS product_name,
        D.nett_item_amount AS so_item_amount, G.doc_no AS sales_invoice_no, F.ref_item_amount AS sales_invoice_item_amount,
        D.curr_code AS curr_code
    FROM pu_po_internal A
    INNER JOIN pu_po_internal_doc_no B ON B.po_internal_id = A.po_internal_id
    INNER JOIN sl_so C ON C.doc_no = B.ref_doc_no
    INNER JOIN sl_so_item D ON D.so_id = C.so_id
    LEFT JOIN sl_do_item E ON E.ref_id = D.so_item_id
    LEFT JOIN sl_invoice_item F ON F.ref_item_id = E.do_item_id
    INNER JOIN sl_invoice G ON G.invoice_id = F.invoice_id
    INNER JOIN pu_po H ON H.ref_id = C.ref_id
    WHERE A.ou_buy_id = $1 AND A.ou_sell_id = $2 AND A.tenant_id = $3 '||
        vFilterPoInternalNo ||
        ' AND C.doc_no = $4 AND H.doc_no = $5' USING pPoInternalOuId, pSoOuId, pTenantId, pSoNo, pPoNo;
    RETURN NEXT pRefDetail ;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
/