--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 := '';

    vDocTypeSalesInvoice    bigint := 321;
    vDocTypePo              bigint := 101;

BEGIN

    IF (pPoInternalNo <> vEmptyString) THEN
        vFilterPoInternalNo := ' AND C.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 '
            WITH product_po AS (
            SELECT C.product_id, A.ref_doc_no, B.ref_item_amount, D.curr_code, C.qty_dlv_so
            FROM pu_po_internal_doc_no A
            INNER JOIN sl_invoice_item B ON B.invoice_id = A.ref_id
            INNER JOIN sl_do_item C ON C.do_item_id = B.ref_item_id
            INNER JOIN sl_invoice D ON D.invoice_id = B.invoice_id
            WHERE A.ref_doc_type_id = '|| vDocTypeSalesInvoice ||' AND D.tenant_id = $5
                AND A.po_internal_id IN (
                    SELECT A.po_internal_id
                    FROM pu_po_internal_doc_no A
                    INNER JOIN pu_po B ON B.po_id = A.ref_id
                    INNER JOIN pu_po_internal C ON C.po_internal_id = A.po_internal_id
                    WHERE A.ref_doc_type_id = '|| vDocTypePo ||' AND A.ref_doc_no = $1 AND C.ou_buy_id = $2
                    '|| vFilterPoInternalNo ||'
                )
        )
        SELECT A.doc_no AS so_no, A.doc_date AS so_date,
        f_get_product_code(B.product_id) AS product_code, f_get_product_name(B.product_id) AS product_code,
        (B.qty_so * B.nett_item_amount) AS so_item_amount, COALESCE(C.ref_doc_no, '''') AS sales_invoice_no, COALESCE(C.ref_item_amount * C.qty_dlv_so, 0) AS sales_invoice_item_amount,
        A.curr_code AS so_curr_code, COALESCE(C.curr_code, '''') AS sales_invoice_curr_code,
        B.qty_so AS qty_so, C.qty_dlv_so AS qty_sales_invoice
        FROM sl_so A
        INNER JOIN sl_so_item B ON B.so_id = A.so_id
        LEFT JOIN product_po C ON C.product_id = B.product_id
        WHERE A.doc_no = $3 AND A.ou_id = $4 AND B.tenant_id = $5 '
        USING pPoNo, pPoInternalOuId, pSoNo, pSoOuId, pTenantId;
    RETURN NEXT pRefDetail ;

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