Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Adrian, Mar 16, 2017
- CREATE OR REPLACE FUNCTION r_outstanding_goods_transfer_in_receipt(character varying, bigint, bigint, bigint, character varying, bigint, 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;
- pDateFrom ALIAS FOR $7;
- pDateTo ALIAS FOR $8;
- pProductCodeName ALIAS FOR $9;
- vFilterProduct text = '';
- vEmptyString character varying := '';
- vEmptyDate 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';
- vFlgNo character varying := 'N';
- vDocTypeIdtransferIn bigint := 535;
- vZero bigint := 0;
- BEGIN
- 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, pProductCodeName AS product_code_name;
- RETURN NEXT vRefHeader;
- IF (pProductCodeName <> vEmptyString) THEN
- vFilterProduct := ' AND (UPPER(f_get_product_code(A.product_id)) LIKE UPPER(''%' || pProductCodeName || '%'') OR UPPER(f_get_product_name(A.product_id)) LIKE UPPER(''%' || pProductCodeName || '%''))';
- END IF;
- OPEN vRefDetail FOR
- EXECUTE '
- SELECT B.doc_date AS transfer_in_date, B.doc_no AS transfer_in_no,
- f_get_warehouse_name(A.warehouse_from_id) AS warehouse_from,
- f_get_warehouse_name(A.warehouse_to_id) AS warehouse_to,
- f_get_product_name(A.product_id) AS product_name,
- f_get_product_code(A.product_id) AS product_code,
- f_get_uom_name(A.base_uom_id) AS base_uom,
- A.qty_out AS qty_out, A.qty_in AS qty_in, (A.qty_out - A.qty_in) AS qty_deviation
- FROM in_balance_transfer_in_item A
- INNER JOIN in_inventory B ON B.inventory_id = A.inventory_id
- WHERE A.tenant_id = $1 AND
- A.ou_from_id = $2 AND
- B.doc_type_id = $3 AND
- A.flg_receipt = $4 AND
- B.doc_date BETWEEN $5 AND $6 AND
- (A.qty_out - A.qty_in) <> $7 '
- || vFilterProduct ||'
- ORDER BY B.doc_date'
- USING pTenantId, pOu, vDocTypeIdtransferIn, vFlgNo, pDateFrom, pDateTo, vZero;
- RETURN NEXT vRefDetail;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- ROWS 1000;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement