Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement