R TAGGING PO INTERNAL - SO IGS

--Adrian, Jan 30 2017

CREATE OR REPLACE FUNCTION r_tagging_po_internal_so_igs(character varying, bigint, bigint, bigint, character varying, 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;
    pTenantId           ALIAS FOR $2;
    pUserId             ALIAS FOR $3;
    pRoleId             ALIAS FOR $4;
    pDatetime           ALIAS FOR $5;

    pFilterBy           ALIAS FOR $6;
    pOuId               ALIAS FOR $7;
    pDocNo              ALIAS FOR $8;
    pYear               ALIAS FOR $9;
    pMonth              ALIAS FOR $10;

    vEmptyString        character varying := '';
    vFilterDocNo        character varying := '';

    vDocTypeSalesInvoice    bigint := 321;
    vDocTypePo              bigint := 101;
    vFilterByPoInternal     character varying := 'PO Internal';
    vUpperDocNo             character varying := '';
    vYearMonth              character varying := '';

BEGIN

    SELECT pYear || pMonth INTO vYearMonth;

    IF (pDocNo <> vEmptyString) THEN
        SELECT UPPER (pDocNo) INTO vUpperDocNo;
        vFilterDocNo := ' AND UPPER(C.doc_no) LIKE ''%' || vUpperDocNo || '%''';
    END IF;

    Open pRefHeader FOR
    SELECT pFilterBy AS filter_by,
        pOuId AS ou_id, f_get_ou_code(pOuId) AS ou_code, f_get_ou_name(pOuId) AS ou_name,
        pDocNo AS doc_no,
        pYear AS year,
        pMonth AS month;
    RETURN NEXT pRefHeader;


    IF (pFilterBy = vFilterByPoInternal) THEN
        Open pRefDetail FOR
            EXECUTE '
                WITH product_so AS (
                    SELECT C.product_id, B.doc_no, B.doc_date, C.nett_item_amount, B.curr_code, C.qty_so
                    FROM fi_po_internal_so_igs_tagging A
                    INNER JOIN sl_so B ON A.so_id = B.so_id
                    INNER JOIN sl_so_item C ON C.so_id = B.so_id
                    WHERE A.tenant_id = $1
                )
                SELECT COALESCE(G.doc_no, '''') AS so_no, COALESCE(G.doc_date, '''') AS so_date,
                    f_get_product_code(E.product_id) AS product_code, f_get_product_name(E.product_id) AS product_name,
                    COALESCE(G.nett_item_amount, 0) AS so_item_amount, B.ref_doc_no AS sales_invoice_no, D.ref_item_amount AS sales_invoice_item_amount,
                    COALESCE(G.curr_code, '''') AS so_curr_code, F.curr_code AS sales_invoice_curr_code,
                    COALESCE(G.qty_so, 0) AS qty_so, E.qty_dlv_so AS qty_sales_invoice
                FROM fi_po_internal_so_igs_tagging A
                INNER JOIN pu_po_internal_doc_no B ON B.po_internal_id = A.po_internal_id
                INNER JOIN pu_po_internal C ON C.po_internal_id = B.po_internal_id
                INNER JOIN sl_invoice_item D ON D.invoice_id = B.ref_id
                INNER JOIN sl_do_item E ON E.do_item_id = D.ref_item_id
                INNER JOIN sl_invoice F ON F.invoice_id = D.invoice_id
                LEFT JOIN product_so G ON G.product_id = E.product_id
                WHERE B.ref_doc_type_id = $2
                    AND A.tenant_id = $1
                    AND A.po_internal_ou_id = $3
                    AND SUBSTR(C.doc_date, 1, 6) = $4'
                    || vFilterDocNo
            USING pTenantId, vDocTypeSalesInvoice, pOuId, vYearMonth;
        RETURN NEXT pRefDetail ;
    ELSE
        Open pRefDetail FOR
            EXECUTE '
                WITH product_po AS (
                    SELECT D.product_id, B.ref_doc_no, C.ref_item_amount, E.curr_code, D.qty_dlv_so
                    FROM fi_po_internal_so_igs_tagging A
                    INNER JOIN pu_po_internal_doc_no B ON B.po_internal_id = A.po_internal_id
                    INNER JOIN sl_invoice_item C ON C.invoice_id = B.ref_id
                    INNER JOIN sl_do_item D ON D.do_item_id = C.ref_item_id
                    INNER JOIN sl_invoice E ON E.invoice_id = C.invoice_id
                    WHERE B.ref_doc_type_id = $1 AND A.tenant_id = $2
                )
                SELECT B.doc_no AS so_no, B.doc_date AS so_date,
                    f_get_product_code(C.product_id) AS product_code, f_get_product_name(C.product_id) AS product_name,
                    C.nett_item_amount AS so_item_amount, COALESCE(D.ref_doc_no, '''') AS sales_invoice_no, COALESCE(D.ref_item_amount, 0) AS sales_invoice_item_amount,
                    B.curr_code AS so_curr_code, COALESCE(D.curr_code, '''') AS sales_invoice_curr_code,
                    C.qty_so AS qty_so, COALESCE(D.qty_dlv_so, 0) AS qty_sales_invoice
                FROM fi_po_internal_so_igs_tagging A
                INNER JOIN sl_so B ON A.so_id = B.so_id
                INNER JOIN sl_so_item C ON C.so_id = B.so_id
                LEFT JOIN product_po D ON D.product_id = C.product_id
                WHERE A.tenant_id = $2
                    AND A.so_ou_id = $3
                    AND SUBSTR(B.doc_date, 1, 6) = $4'
                    || vFilterDocNo
            USING vDocTypeSalesInvoice, pTenantId, pOuId, vYearMonth;
        RETURN NEXT pRefDetail ;
    END IF;

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