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