r_sales_invoice_list_ORIGINAL_20180326

-- Function: r_sales_invoice_list(character varying, bigint, bigint, character varying, character varying, bigint, bigint, bigint, bigint)

-- DROP FUNCTION r_sales_invoice_list(character varying, bigint, bigint, character varying, character varying, bigint, bigint, bigint, bigint);

CREATE OR REPLACE FUNCTION r_sales_invoice_list(character varying, bigint, bigint, character varying, character varying, bigint, bigint, bigint, bigint)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader          REFCURSOR := 'refHeader';
    pRefDetail          REFCURSOR := 'refDetail';
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pOuId               ALIAS FOR $3;
    pDateFrom           ALIAS FOR $4;
    pDateTo             ALIAS FOR $5;
    pPartnerId          ALIAS FOR $6;
    pCtgrProductId      ALIAS FOR $7;
    pSubCtgrProductId   ALIAS FOR $8;
    pUserId             ALIAS FOR $9;

    vEmptyId            bigint := -99;
    vDocSalesInvoice    bigint := 321;
    vReturnSalesInvoiceDocType bigint := 331;
    vYes                character varying := 'Y';
BEGIN
    -- Get header
    Open pRefHeader FOR
    SELECT fullName AS full_name, pDateFrom AS date_from, pDateTo AS date_to
    FROM t_user
    WHERE user_id = pUserId;

    RETURN NEXT pRefHeader;


    -- CLEAR TT table
    DELETE FROM tt_sl_r_sales_invoice_list WHERE session_id = pSessionId;

    INSERT INTO tt_sl_r_sales_invoice_list(
                session_id, tenant_id, ou_id, invoice_item_id, partner_id, partner_name,
                curr_code, doc_no, doc_date, so_no, so_date, do_no, do_date,
                product_id, product_code, product_name, qty_so, sell_price, item_amount,
                tax_amount, ctgr_product_id, ctgr_product_code, ctgr_product_name,
                sub_ctgr_product_id, sub_ctgr_product_code, sub_ctgr_product_name, uom_so)
    SELECT      pSessionId, A.tenant_id, A.ou_id, D.invoice_item_id, C.partner_id, C.partner_name,
                A.curr_code, A.doc_no, A.doc_date, B.doc_no AS so_no, B.doc_date AS so_date, E.doc_no AS do_no, E.doc_date AS do_date,
                G.product_id, G.product_code, G.product_name, SUM(L.qty_dlv_so), L.price_so,
                CASE WHEN A.doc_type_id = vReturnSalesInvoiceDocType THEN -1 * SUM(L.item_amount) ELSE SUM(L.item_amount) END,
                CASE WHEN A.doc_type_id = vReturnSalesInvoiceDocType THEN -1 * SUM(H.tax_amount) ELSE SUM(H.tax_amount) END,
                I.ctgr_product_id, I.ctgr_product_code, I.ctgr_product_name,
                J.sub_ctgr_product_id, J.sub_ctgr_product_code, J.sub_ctgr_product_name , K.uom_code
    FROM sl_invoice A
    INNER JOIN sl_so B ON A.ref_id = B.so_id
    INNER JOIN m_partner C ON B.partner_id = C.partner_id
    INNER JOIN sl_invoice_item D ON A.invoice_id = D.invoice_id
    INNER JOIN sl_do E ON D.ref_id = E.do_id
    INNER JOIN sl_do_item F ON D.ref_item_id = F.do_item_id
    INNER JOIN m_product G ON F.product_id = G.product_id
    INNER JOIN sl_so_item H ON F.ref_id = H.so_item_id AND D.ref_item_id=F.do_item_id
    INNER JOIN m_ctgr_product I ON G.ctgr_product_id = I.ctgr_product_id
    INNER JOIN m_sub_ctgr_product J ON G.sub_ctgr_product_id = J.sub_ctgr_product_id
    INNER JOIN m_uom K ON H.so_uom_id = K.uom_id
    INNER JOIN sl_so_balance_invoice L ON L.invoice_id=A.invoice_id AND L.flg_invoice = vYes  AND F.do_item_id=L.ref_item_id
    WHERE A.doc_type_id = vDocSalesInvoice
        AND A.ou_id = pOuId
        AND A.tenant_id = pTenantId
        AND A.doc_date BETWEEN pDateFrom AND pDateTo
    GROUP BY A.tenant_id, A.ou_id, D.invoice_item_id, C.partner_id, C.partner_name,
                A.curr_code, A.doc_no, A.doc_date, B.doc_no, B.doc_date, E.doc_no, E.doc_date,
                G.product_id, G.product_code, G.product_name, L.price_so, I.ctgr_product_id, I.ctgr_product_code, I.ctgr_product_name,
                J.sub_ctgr_product_id, J.sub_ctgr_product_code, J.sub_ctgr_product_name , K.uom_code, A.doc_type_id
    HAVING SUM(L.qty_dlv_so) > 0;

--  INSERT INTO tt_sl_r_sales_invoice_list(
--              session_id, tenant_id, ou_id, invoice_item_id, partner_id, partner_name,
--              curr_code, doc_no, doc_date, so_no, so_date, do_no, do_date,
--              product_id, product_code, product_name, qty_so, sell_price, item_amount,
--              tax_amount, ctgr_product_id, ctgr_product_code, ctgr_product_name,
--              sub_ctgr_product_id, sub_ctgr_product_code, sub_ctgr_product_name, uom_so)
--  SELECT      pSessionId, A.tenant_id, A.ou_id, D.invoice_item_id, C.partner_id, C.partner_name,
--              A.curr_code, A.doc_no, A.doc_date, B.doc_no AS so_no, B.doc_date AS so_date, E.doc_no AS do_no, E.doc_date AS do_date,
--              G.product_id, G.product_code, G.product_name, SUM(L.qty_dlv_so), L.price_so, SUM(L.item_amount),
--              SUM(H.tax_amount), I.ctgr_product_id, I.ctgr_product_code, I.ctgr_product_name,
--              J.sub_ctgr_product_id, J.sub_ctgr_product_code, J.sub_ctgr_product_name , K.uom_code
--  FROM sl_invoice A
--  INNER JOIN sl_so B ON A.ref_id = B.so_id
--  INNER JOIN m_partner C ON B.partner_id = C.partner_id
--  INNER JOIN sl_invoice_item D ON A.invoice_id = D.invoice_id
--  INNER JOIN sl_do E ON D.ref_id = E.do_id
--  INNER JOIN sl_do_item F ON D.ref_item_id = F.do_item_id
--  INNER JOIN m_product G ON F.product_id = G.product_id
--  INNER JOIN sl_so_item H ON F.ref_id = H.so_item_id
--  INNER JOIN m_ctgr_product I ON G.ctgr_product_id = I.ctgr_product_id
--  INNER JOIN m_sub_ctgr_product J ON G.sub_ctgr_product_id = J.sub_ctgr_product_id
--  INNER JOIN m_uom K ON H.so_uom_id = K.uom_id
--  INNER JOIN sl_so_balance_invoice L ON L.invoice_id=A.invoice_id AND L.flg_invoice = vYes
--  WHERE A.doc_type_id = vDocSalesInvoice
--      AND A.ou_id = pOuId
--      AND A.tenant_id = pTenantId
--      AND A.doc_date BETWEEN pDateFrom AND pDateTo
--      AND (pPartnerId = vEmptyId OR C.partner_id = pPartnerId)
--      AND (pCtgrProductId = vEmptyId OR G.ctgr_product_id = pCtgrProductId)
--      AND (pSubCtgrProductId = vEmptyId OR G.sub_ctgr_product_id = pSubCtgrProductId)
--  GROUP BY A.tenant_id, A.ou_id, D.invoice_item_id, C.partner_id, C.partner_name,
--              A.curr_code, A.doc_no, A.doc_date, B.doc_no, B.doc_date, E.doc_no,
--              E.doc_date, G.product_id, G.product_code, G.product_name, L.price_so,
--              I.ctgr_product_id, I.ctgr_product_code, I.ctgr_product_name,
--              J.sub_ctgr_product_id, J.sub_ctgr_product_code, J.sub_ctgr_product_name , K.uom_code
--  HAVING SUM(L.qty_dlv_so) > 0;

    OPEN pRefDetail FOR
    SELECT A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
            A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
            A.product_code, A.product_name, A.qty_so AS qty_so, A.uom_so, A.sell_price,
            A.item_amount AS item_amount, A.tax_amount AS tax_amount
    FROM tt_sl_r_sales_invoice_list A
    WHERE A.session_id = pSessionId
    GROUP BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
            A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
            A.product_code, A.product_name, A.uom_so, A.sell_price,
            A.qty_so, A.item_amount,A.tax_amount
    ORDER BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
            A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
            A.product_code, A.product_name, A.uom_so, A.sell_price,
            A.qty_so, A.item_amount,A.tax_amount;

--  INSERT INTO tt_sl_r_sales_invoice_list(
--              session_id, tenant_id, ou_id, invoice_item_id, partner_id, partner_name,
--              curr_code, doc_no, doc_date, so_no, so_date, do_no, do_date,
--              product_id, product_code, product_name, qty_so, sell_price, item_amount,
--              tax_amount, ctgr_product_id, ctgr_product_code, ctgr_product_name,
--              sub_ctgr_product_id, sub_ctgr_product_code, sub_ctgr_product_name, uom_so)
--  SELECT      pSessionId, A.tenant_id, A.ou_id, D.invoice_item_id, C.partner_id, C.partner_name,
--              A.curr_code, A.doc_no, A.doc_date, B.doc_no AS so_no, B.doc_date AS so_date, E.doc_no AS do_no, E.doc_date AS do_date,
--              G.product_id, G.product_code, G.product_name, H.qty_so, H.nett_sell_price, H.nett_item_amount,
--              H.tax_amount, I.ctgr_product_id, I.ctgr_product_code, I.ctgr_product_name,
--              J.sub_ctgr_product_id, J.sub_ctgr_product_code, J.sub_ctgr_product_name , K.uom_code
--  FROM sl_invoice A
--  INNER JOIN sl_so B ON A.ref_id = B.so_id
--  INNER JOIN m_partner C ON B.partner_id = C.partner_id
--  INNER JOIN sl_invoice_item D ON A.invoice_id = D.invoice_id
--  INNER JOIN sl_do E ON D.ref_id = E.do_id
--  INNER JOIN sl_do_item F ON D.ref_item_id = F.do_item_id
--  INNER JOIN m_product G ON F.product_id = G.product_id
--  INNER JOIN sl_so_item H ON F.ref_id = H.so_item_id
--  INNER JOIN m_ctgr_product I ON G.ctgr_product_id = I.ctgr_product_id
--  INNER JOIN m_sub_ctgr_product J ON G.sub_ctgr_product_id = J.sub_ctgr_product_id
--  INNER JOIN m_uom K ON H.so_uom_id = K.uom_id
--  WHERE A.doc_type_id = vDocSalesInvoice
--      AND A.ou_id = pOuId
--      AND A.tenant_id = pTenantId
--      AND A.doc_date BETWEEN pDateFrom AND pDateTo;

--  IF pPartnerId <> vEmptyId THEN
--      IF pCtgrProductId <> vEmptyId THEN
--          IF pSubCtgrProductId <> vEmptyId THEN
--              OPEN pRefDetail FOR
--              SELECT A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, SUM(A.qty_so) AS qty_so, A.uom_so, A.sell_price,
--                      SUM(A.item_amount) AS item_amount, SUM(A.tax_amount) AS tax_amount
--              FROM tt_sl_r_sales_invoice_list A
--              WHERE A.session_id = pSessionId
--                  AND A.partner_id = pPartnerId
--                  AND A.ctgr_product_id = pCtgrProductId
--                  AND A.sub_ctgr_product_id = pCtgrProductId
--              GROUP BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, A.uom_so, A.sell_price
--              ORDER BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, A.uom_so, A.sell_price;
--          ELSE
--              OPEN pRefDetail FOR
--              SELECT A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, SUM(A.qty_so) AS qty_so, A.uom_so, A.sell_price,
--                      SUM(A.item_amount) AS item_amount, SUM(A.tax_amount) AS tax_amount
--              FROM tt_sl_r_sales_invoice_list A
--              WHERE A.session_id = pSessionId
--                  AND A.partner_id = pPartnerId
--                  AND A.ctgr_product_id = pCtgrProductId
--              GROUP BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, A.uom_so, A.sell_price
--              ORDER BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, A.uom_so, A.sell_price;
--          END IF;
--      ELSE
--          IF pSubCtgrProductId <> vEmptyId THEN
--              OPEN pRefDetail FOR
--              SELECT A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, SUM(A.qty_so) AS qty_so, A.uom_so, A.sell_price,
--                      SUM(A.item_amount) AS item_amount, SUM(A.tax_amount) AS tax_amount
--              FROM tt_sl_r_sales_invoice_list A
--              WHERE A.session_id = pSessionId
--                  AND A.partner_id = pPartnerId
--                  AND A.sub_ctgr_product_id = pCtgrProductId
--              GROUP BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, A.uom_so, A.sell_price
--              ORDER BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, A.uom_so, A.sell_price;
--          ELSE
--              OPEN pRefDetail FOR
--              SELECT A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, SUM(A.qty_so) AS qty_so, A.uom_so, A.sell_price,
--                      SUM(A.item_amount) AS item_amount, SUM(A.tax_amount) AS tax_amount
--              FROM tt_sl_r_sales_invoice_list A
--              WHERE A.session_id = pSessionId
--                  AND A.partner_id = pPartnerId
--              GROUP BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, A.uom_so, A.sell_price
--              ORDER BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, A.uom_so, A.sell_price;
--          END IF;
--      END IF;
--  ELSE
--      IF pCtgrProductId <> vEmptyId THEN
--          IF pSubCtgrProductId <> vEmptyId THEN
--              OPEN pRefDetail FOR
--              SELECT A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, SUM(A.qty_so) AS qty_so, A.uom_so, A.sell_price,
--                      SUM(A.item_amount) AS item_amount, SUM(A.tax_amount) AS tax_amount
--              FROM tt_sl_r_sales_invoice_list A
--              WHERE A.session_id = pSessionId
--                  AND A.ctgr_product_id = pCtgrProductId
--                  AND A.sub_ctgr_product_id = pCtgrProductId
--              GROUP BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, A.uom_so, A.sell_price
--              ORDER BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, A.uom_so, A.sell_price;
--          ELSE
--              OPEN pRefDetail FOR
--              SELECT A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, SUM(A.qty_so) AS qty_so, A.uom_so, A.sell_price,
--                      SUM(A.item_amount) AS item_amount, SUM(A.tax_amount) AS tax_amount
--              FROM tt_sl_r_sales_invoice_list A
--              WHERE A.session_id = pSessionId
--                  AND A.ctgr_product_id = pCtgrProductId
--              GROUP BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, A.uom_so, A.sell_price
--              ORDER BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, A.uom_so, A.sell_price;
--          END IF;
--      ELSE
--          IF pSubCtgrProductId <> vEmptyId THEN
--              OPEN pRefDetail FOR
--              SELECT A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, SUM(A.qty_so) AS qty_so, A.uom_so, A.sell_price,
--                      SUM(A.item_amount) AS item_amount, SUM(A.tax_amount) AS tax_amount
--              FROM tt_sl_r_sales_invoice_list A
--              WHERE A.session_id = pSessionId
--                  AND A.sub_ctgr_product_id = pCtgrProductId
--              GROUP BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, A.uom_so, A.sell_price
--              ORDER BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, A.uom_so, A.sell_price;
--          ELSE
--              OPEN pRefDetail FOR
--              SELECT A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, SUM(A.qty_so) AS qty_so, A.uom_so, A.sell_price,
--                      SUM(A.item_amount) AS item_amount, SUM(A.tax_amount) AS tax_amount
--              FROM tt_sl_r_sales_invoice_list A
--              WHERE A.session_id = pSessionId
--              GROUP BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, A.uom_so, A.sell_price
--              ORDER BY A.curr_code, A.partner_name, A.ctgr_product_name, A.sub_ctgr_product_name,
--                      A.doc_no, A.doc_date, A.so_no, A.so_date, A.do_no, A.do_date,
--                      A.product_code, A.product_name, A.uom_so, A.sell_price;
--          END IF;
--      END IF;
--  END IF;

        -- CLEAR TT table
    DELETE FROM tt_sl_r_sales_invoice_list WHERE session_id = pSessionId;

    RETURN NEXT pRefDetail;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION r_sales_invoice_list(character varying, bigint, bigint, character varying, character varying, bigint, bigint, bigint, bigint)
  OWNER TO sts;
GRANT EXECUTE ON FUNCTION r_sales_invoice_list(character varying, bigint, bigint, character varying, character varying, bigint, bigint, bigint, bigint) TO sts;
GRANT EXECUTE ON FUNCTION r_sales_invoice_list(character varying, bigint, bigint, character varying, character varying, bigint, bigint, bigint, bigint) TO public;