Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION r_claim_consignment(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, character varying, character varying, character varying)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- vRefHeader REFCURSOR := 'refHeader';
- vRefDetail REFCURSOR := 'refDetail';
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pUserId ALIAS FOR $3;
- pRoleId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- pOu ALIAS FOR $6;
- pStatusDoc ALIAS FOR $7;
- pDocNo ALIAS FOR $8;
- pDateFrom ALIAS FOR $9;
- pDateTo ALIAS FOR $10;
- pCustomer ALIAS FOR $11;
- vFilterStatusDoc text;
- vFilterDocNo text;
- vFilterCustomer text;
- vEmptyString character varying := ' ';
- vFlgInProgress character varying := 'I';
- vStatusInProgress character varying := 'In Progress';
- vFlgDraft character varying := 'D';
- vStatusDraft character varying := 'Draft';
- vFLgApprove character varying := 'R';
- vStatusApprove character varying := 'Approve';
- vDocTypeClaimConsignment bigint := 561;
- BEGIN
- IF (pStatusDoc <> vEmptyString) THEN
- vFilterStatusDoc := ' AND UPPER(A.status_doc) LIKE UPPER(''%' || pStatusDoc || '%'') ';
- END IF;
- IF (pDocNo <> vEmptyString) THEN
- vFilterDocNo := ' AND UPPER(A.doc_no) LIKE UPPER(''%' || pDocNo || '%'') ';
- END IF;
- IF (pCustomer <> vEmptyString) THEN
- vFilterCustomer := ' AND ( UPPER(f_get_partner_name(A.partner_id)) LIKE UPPER(''%' || pCustomer || '%'')
- OR UPPER(f_get_partner_code(A.partner_id)) LIKE UPPER(''%' || pCustomer || '%'') )';
- END IF;
- OPEN vRefHeader FOR
- SELECT f_get_ou_name(pOu) AS ou_name, pDateFrom AS date_from, pDateTo AS date_to, pDatetime AS datetime,
- f_get_username(pUserId) AS username,
- CASE WHEN pStatusDoc = vFlgInProgress THEN vStatusInProgress
- WHEN pStatusDoc = vFlgDraft THEN vStatusDraft
- WHEN pStatusDoc = vFLgApprove THEN vStatusApprove ELSE 'ALL' END AS status_doc,
- CASE WHEN pDocNo = vEmptyString THEN 'ALL' ELSE pDocNo END AS doc_no,
- CASE WHEN pCustomer = vEmptyString THEN 'ALL' ELSE pCustomer END AS customer_name;
- RETURN NEXT vRefHeader;
- OPEN vRefDetail FOR
- EXECUTE '
- SELECT A.doc_date AS claim_consignment_date, A.doc_no AS claim_consignment_no, C.doc_date AS po_date,
- C.doc_no AS po_no, D.doc_date AS rg_consignment_date, D.doc_no AS rg_consignment_no,
- f_get_partner_name(A.partner_id) AS partner_name, f_get_partner_code(A.partner_id) AS partner_code,
- f_get_product_name(B.product_id) AS product_name, f_get_product_code(B.product_id) AS product_code,
- SUM(B.qty_realization) AS qty, f_get_uom_name(B.base_uom_id) AS base_uom,
- CASE WHEN A.status_doc = '''|| vFlgInProgress ||''' THEN '''|| vStatusInProgress ||'''
- WHEN A.status_doc = '''|| vFlgDraft ||''' THEN '''|| vStatusDraft ||''' ELSE '''|| vStatusApprove ||''' END AS status_doc
- FROM in_inventory A
- INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
- INNER JOIN pu_po C ON A.ref_doc_type_id = C.doc_type_id AND A.ref_id = C.po_id
- INNER JOIN pu_receive_goods_consignment D ON D.ref_doc_type_id = C.doc_type_id AND D.ref_id = C.po_id
- WHERE A.tenant_id = $1 AND
- A.ou_from_id = $2 AND
- A.doc_type_id = $3 AND
- A.doc_date BETWEEN $4 AND $5 '
- || vFilterDocNo
- || vFilterCustomer
- || vFilterStatusDoc ||'
- GROUP BY A.doc_date, A.doc_no, C.doc_date, C.doc_no, D.doc_date, D.doc_no, A.partner_id, B.product_id, B.base_uom_id, A.status_doc
- ORDER BY A.doc_date'
- USING pTenantId, pOu, vDocTypeClaimConsignment, pDateFrom, pDateTo;
- RETURN NEXT vRefDetail;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- ROWS 1000;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement