· 5 years ago · May 12, 2020, 10:38 AM
1CREATE OR REPLACE FUNCTION r_report_pemenuhan_stock(character varying, bigint, bigint, bigint, character varying, bigint, bigint, bigint, character varying)
2 RETURNS SETOF refcursor AS
3$BODY$
4DECLARE
5 pRefHeader REFCURSOR := 'refHeader';
6 pRefDetail REFCURSOR := 'refDetail';
7 pSessionId ALIAS FOR $1;
8 pTenantId ALIAS FOR $2;
9 pUserId ALIAS FOR $3;
10 pRoleId ALIAS FOR $4;
11 pDatetime ALIAS FOR $5;
12
13 pOuId ALIAS FOR $6;
14 pWarehouseId ALIAS FOR $7;
15 pGroupBrandId ALIAS FOR $8;
16 pProductCodeName ALIAS FOR $9;
17
18 vOuCodeName character varying(100);
19 vWarehouseCodeName character varying(100);
20 vGroupBrandCodeName character varying(100);
21
22 vFilterProduct text := '';
23 vEmptyString character varying := '';
24 vFlgYes character varying := 'Y';
25 vFlgNo character varying := 'N';
26
27 vStatusDraft character varying := 'D';
28 vStatusReleased character varying := 'R';
29 vStatusInProgress character varying := 'I';
30
31 vEmptyId bigint := -99;
32
33
34 vDocTypeDO bigint := 311;
35 vDocTypeRgto bigint := 538;
36 vDocTypeGTO bigint := 533;
37 vDocTypeRG bigint := 111;
38
39BEGIN
40
41 DELETE FROM tt_r_pemenuhan_stock WHERE session_id = pSessionId;
42
43 -- filter ou id
44 IF(pOuId <> vEmptyId) THEN
45 SELECT ou_code||' - '||ou_name INTO vOuCodeName
46 FROM t_ou
47 WHERE ou_id = pOuId;
48 END IF;
49
50 -- filter warehouse id
51 IF(pWarehouseId <> vEmptyId) THEN
52 SELECT warehouse_code||' - '||warehouse_name INTO vWarehouseCodeName
53 FROM m_warehouse
54 WHERE warehouse_id = pWarehouseId;
55 END IF;
56
57 -- filter group brand id
58 IF(pGroupBrandId <> vEmptyId) THEN
59 SELECT group_brand_code||' - '||group_brand_name INTO vGroupBrandCodeName
60 FROM m_group_brand
61 WHERE group_brand_id = pGroupBrandId;
62 END IF;
63
64
65 -- insert qty_current into table tt_r_pemenuhan_stock from table in_product_balance_stock
66 INSERT INTO tt_r_pemenuhan_stock(
67 session_id, product_id, product_code, product_name,
68 group_brand, qty_current)
69 SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
70 vEmptyString AS group_brand, COALESCE (SUM(A.qty),0) AS qty_current
71 FROM in_product_balance_stock A
72 INNER JOIN m_product_status B ON A.tenant_id = B.tenant_id AND A.product_status = B.product_status_code
73 WHERE B.flg_sell = vFlgYes
74 AND A.tenant_id = pTenantId
75 AND A.warehouse_id = pWarehouseId
76 GROUP BY A.product_id;
77
78 -- insert qty_os_so (Belum Jadi DO) into table tt_r_pemenuhan_stock from table sl_so_balance_item
79 INSERT INTO tt_r_pemenuhan_stock(
80 session_id, product_id, product_code, product_name,
81 group_brand, qty_os_so)
82 SELECT pSessionId, B.product_id, f_get_product_code(B.product_id) AS product_code, f_get_product_name(B.product_id) AS product_name,
83 vEmptyString AS group_brand, (SUM(C.qty_so + C.qty_add - C.qty_dlv - C.qty_cancel )) AS qty_os_so
84 FROM sl_so A
85 INNER JOIN sl_so_item B ON A.so_id = B.so_id
86 INNER JOIN sl_so_balance_item C ON B.so_item_id = C.so_item_id
87 WHERE C.status_item IN (vStatusReleased, vStatusInProgress)
88 AND A.tenant_id = pTenantId
89 AND A.ou_id = pOuId
90 AND NOT EXISTS (SELECT 1 FROM sl_do Y
91 WHERE Y.ref_id = A.so_id
92 AND Y.ref_doc_type_id = A.doc_type_id
93 AND Y.doc_type_id = vDocTypeDO)
94 GROUP BY B.product_id;
95
96 -- insert qty_rgto (Belum kebuat GTO) into table tt_r_pemenuhan_stock from table in_inventory
97 INSERT INTO tt_r_pemenuhan_stock(
98 session_id, product_id, product_code, product_name,
99 group_brand, qty_rgto)
100 SELECT pSessionId, B.product_id, f_get_product_code(B.product_id) AS product_code, f_get_product_name(B.product_id) AS product_name,
101 vEmptyString AS group_brand, COALESCE (SUM(B.qty_request),0) AS qty_rgto
102 FROM in_inventory A
103 INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
104 INNER JOIN in_balance_req_transfer_out C ON B.inventory_id = C.inventory_id
105 WHERE A.status_doc = vStatusReleased
106 AND A.doc_type_id = vDocTypeRgto
107 AND A.tenant_id = pTenantId
108 AND A.ou_from_id = pOuId
109 AND A.warehouse_from_id = pWarehouseId
110 AND C.flg_real = vFlgNo
111 AND NOT EXISTS (SELECT 1 FROM in_inventory Y
112 WHERE Y.ref_id = A.inventory_id
113 AND Y.ref_doc_type_id = A.doc_type_id
114 AND Y.doc_type_id = vDocTypeGTO)
115 GROUP BY B.product_id;
116
117 -- insert qty_os_po (belum kebuat received Good) into table tt_r_pemenuhan_stock from table pu_po_balance_item
118 INSERT INTO tt_r_pemenuhan_stock(
119 session_id, product_id, product_code, product_name,
120 group_brand, qty_os_po)
121 SELECT pSessionId, B.product_id, f_get_product_code(B.product_id) AS product_code, f_get_product_name(B.product_id) AS product_name,
122 vEmptyString AS group_brand, (SUM(C.qty_po + C.qty_add - C.qty_rcv - C.qty_cancel)) AS qty_os_po
123 FROM pu_po A
124 INNER JOIN pu_po_item B ON A.po_id = B.po_id
125 INNER JOIN pu_po_balance_item C ON B.po_item_id = C.po_item_id
126 WHERE C.status_item IN (vStatusReleased, vStatusInProgress)
127 AND A.ou_id = pOuId
128 AND A.tenant_id = pTenantId
129 AND A.warehouse_id = pWarehouseId
130 GROUP BY B.product_id;
131
132 OPEN pRefHeader FOR
133 SELECT CASE WHEN pOuId = vEmptyId THEN -99 ELSE pOuId END AS ou_id, vOuCodeName AS ou_code_name,
134 CASE WHEN pWarehouseId = vEmptyId THEN -99 ELSE pWarehouseId END AS warehouse_id, vWarehouseCodeName AS warehouse_code_name,
135 CASE WHEN pGroupBrandId = vEmptyId THEN -99 ELSE pGroupBrandId END AS group_brand_id, vGroupBrandCodeName AS group_brand_code_name,
136 pDatetime AS datetime, f_get_username(pUserId) AS username;
137 RETURN NEXT pRefHeader;
138
139 -- filter product code / name
140 IF (pProductCodeName <> vEmptyString) THEN
141 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 || '%''))';
142 END IF;
143
144 OPEN pRefDetail FOR
145 EXECUTE '
146 SELECT A.product_code, A.product_name, CONCAT(B.group_brand_code, '' - '', B.group_brand_name) AS group_brand,
147 SUM(A.qty_current) AS qty_current, SUM(A.qty_os_so) AS qty_os_so, SUM(A.qty_rgto) AS qty_rgto, SUM(A.qty_os_po) AS qty_os_po,
148 (SUM(A.qty_current) + SUM(A.qty_os_po) - SUM(A.qty_os_so) - SUM(A.qty_rgto)) AS qty_need_to_order
149 FROM tt_r_pemenuhan_stock A
150 INNER JOIN m_group_brand B ON $1 = B.group_brand_id AND $2 = B.tenant_id
151 INNER JOIN m_product_custom_for_sasa C ON A.product_id = C.product_id
152 WHERE A.session_id = $3
153 AND C.group_brand_id = $1'
154 || vFilterProduct ||'
155 GROUP BY A.product_code, A.product_name, B.group_brand_code, B.group_brand_name
156 HAVING SUM (A.qty_os_so) <> 0 OR SUM(A.qty_os_po) <> 0 OR SUM(A.qty_rgto) <> 0
157 ORDER BY product_name'
158 USING pGroupBrandId, pTenantId, pSessionId ;
159
160 RETURN NEXT pRefDetail;
161
162 DELETE FROM tt_r_pemenuhan_stock WHERE session_id = pSessionId;
163
164END;
165$BODY$
166 LANGUAGE plpgsql VOLATILE
167 COST 100
168 ROWS 1000;
169/