· 5 years ago · Oct 09, 2020, 01:56 AM
1--Fitra 25 Agustus 2017
2--2017-12-15 fitra, menambahkan kodisi tidak sedang / sudah finalisasi diquery mendapatkan data RGTO
3CREATE OR REPLACE FUNCTION r_report_rekap_saldo_stock_nasional(BIGINT, BIGINT, CHARACTER VARYING, CHARACTER VARYING)
4 RETURNS SETOF refcursor AS
5$BODY$
6DECLARE
7 pRefHeader REFCURSOR := 'refHeader';
8 pRefDetail REFCURSOR := 'refDetail';
9 pTenantId ALIAS FOR $1;
10 pReportMessageId ALIAS FOR $2;
11 pSessionId ALIAS FOR $3;
12 pProcessNo ALIAS FOR $4;
13
14 vOuId bigint;
15 vCtgrProductId bigint;
16 vSubCtgrProductId bigint;
17
18 vGolonganProduct character varying;
19 vProductCodeName character varying;
20 vFlgActive character varying;
21
22 vOuBuInfoReport OU_INFO_REPORT%ROWTYPE;
23 result record;
24 vFilterCtgrProduct character varying= '';
25 vFilterSubCtgrProduct character varying= '';
26 vFilterGolonganProduct character varying= '';
27 vFilterProductCodeName character varying= '';
28 vFilterActive character varying= '';
29 vEmptyId bigint := -99;
30 vEmptyString character varying := '';
31 vFlgStock character varying := 'STOCK';
32 vFlgReserved character varying := 'RSVD';
33 vFlgRgto character varying := 'RGTO';
34 vFlgBuffer character varying := 'BUFFER';
35 vFlgRekap character varying := 'REKAP';
36 vAll character varying := 'ALL';
37 vNo character varying := 'N';
38 vYes character varying := 'Y';
39 vInProgress character varying := 'I';
40 vflgFinalRgto character varying := 'V';
41 vRgtoDocId bigint := 538;
42 vGtoDocId bigint := 533;
43 vFinalizationRgtoDocId bigint := 589;
44 vWarehouse RECORD;
45 vValueColoumNameStock text[];
46 vValueColoumNameRsvd text[];
47 vValueColoumNameRgto text[];
48 vValueColoumNameBuffer text[];
49 vValueColoumNameAll text[];
50
51 vColoumNameStock text := '';
52 vColoumNameRsvd text := '';
53 vColoumNameRgto text := '';
54 vColoumNameBuffer text := '';
55 vColoumNameForHeader text := '';
56
57
58 vColoumNameTotalStock text := 'TOTAL NASIONAL';
59 vColoumNameTotalBuffer text := 'BUFFER NASIONAL';
60 vJumlahKolom bigint := 0;
61 vCounter bigint := 0;
62
63BEGIN
64 --ambil parameter
65 vOuId := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'ouId') AS bigint);
66 vCtgrProductId := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'ctgrProductId') AS bigint);
67 vSubCtgrProductId := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'subCtgrProductId') AS bigint);
68 vGolonganProduct := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'golonganProduct') AS character varying);
69 vProductCodeName := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'productCodeName') AS character varying);
70 vFlgActive := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'flgActive') AS character varying);
71
72
73 raise notice 'vOuId: % vCtgrProductId: % vSubCtgrProductId: % vGolonganProduct: % vProductCodeName: % vFlgActive: %',
74 vOuId,vCtgrProductId,vSubCtgrProductId,vGolonganProduct,vProductCodeName,vFlgActive;
75
76 --fillter terhadap ctgr product
77 IF vCtgrProductId != vEmptyId THEN
78 vFilterCtgrProduct = ' A.ctgr_product_id = '||vCtgrProductId ||' AND ';
79 END IF;
80
81 --fillter terhadap sub ctgr product
82 IF vSubCtgrProductId != vEmptyId THEN
83 vFilterSubCtgrProduct = ' A.sub_ctgr_product_id = '||vSubCtgrProductId ||' AND ';
84 END IF;
85
86 --fillter terhadap golongan
87 IF vGolonganProduct != vEmptyString THEN
88 vFilterGolonganProduct = ' C.style_product = '''||vGolonganProduct||''' AND ';
89 END IF;
90
91 --fillter terhadap product code name
92 IF vProductCodeName != vEmptyString THEN
93 vFilterProductCodeName = ' (UPPER(A.product_code) LIKE UPPER('''||'%'||vProductCodeName||'%'||''') OR UPPER(A.product_name) LIKE UPPER('''||'%'||vProductCodeName||'%'||''')) AND ';
94 END IF;
95 IF vFlgActive != vAll THEN
96 vFilterActive = ' A.active = '''||vFlgActive||''' AND ';
97 END IF;
98
99 DELETE FROM tt_product_saldo_stock_nasional WHERE session_id = pSessionId;
100 DELETE FROM tt_detail_saldo_stock_nasional WHERE session_id = pSessionId;
101
102 --1. Ambil data product yg memenuhi filter yg diinput user.
103 EXECUTE '
104 INSERT INTO tt_product_saldo_stock_nasional(
105 session_id, product_id, product_code, product_name,
106 ctgr_product_id, sub_ctgr_product_id, style_product, product_active)
107 SELECT $1, A.product_id, A.product_code,A.product_name,
108 A.ctgr_product_id, A.sub_ctgr_product_id, C.style_product, A.active
109 FROM m_product A
110 INNER JOIN m_product_custom C ON A.product_id = C.product_id
111 WHERE '||
112 vFilterCtgrProduct ||
113 vFilterSubCtgrProduct ||
114 vFilterGolonganProduct ||
115 vFilterProductCodeName||
116 vFilterActive ||
117 ' A.tenant_id = $2 '
118 USING pSessionId, pTenantId;
119
120 --2. Ambil dari data saldo stock (in_product_balance_stock)
121 -- yang terdaftar di tt_product_saldo_stock_nasional
122 -- dan ada stok nya (qty > 0)
123 INSERT INTO tt_detail_saldo_stock_nasional(
124 session_id, flg_data, product_id, warehouse_id,
125 qty_stock, qty_reserved, qty_rgto, qty_buffer)
126 SELECT pSessionId, vFlgStock, A.product_id, A.warehouse_id,
127 SUM(A.qty), 0, 0, 0
128 FROM in_product_balance_stock A
129 INNER JOIN tt_product_saldo_stock_nasional B ON A.product_id = B.product_id
130 INNER JOIN m_warehouse_ou C ON A.warehouse_id = C.warehouse_id
131 WHERE A.tenant_id = pTenantId AND
132 (f_get_ou_bu_structure(C.ou_id)).ou_bu_id = vOuId AND
133 A.qty > 0 AND
134 B.session_id = pSessionId
135 GROUP BY A.product_id, A.warehouse_id;
136
137 --3. Ambil data saldo reserved stok (in_product_balance_stock_reserved)
138 -- yang terdaftar di tt_product_saldo_stock_nasional
139 -- dan ada stok nya (qty > 0)
140 INSERT INTO tt_detail_saldo_stock_nasional(
141 session_id, flg_data, product_id, warehouse_id,
142 qty_stock, qty_reserved, qty_rgto, qty_buffer)
143 SELECT pSessionId, vFlgReserved, A.product_id, A.warehouse_id,
144 0, A.qty, 0, 0
145 FROM in_product_balance_stock_reserved A
146 INNER JOIN tt_product_saldo_stock_nasional B ON A.product_id = B.product_id
147 INNER JOIN m_warehouse_ou C ON A.warehouse_id = C.warehouse_id
148 WHERE A.tenant_id = pTenantId AND
149 (f_get_ou_bu_structure(C.ou_id)).ou_bu_id = vOuId AND
150 A.qty > 0 AND
151 B.session_id = pSessionId;
152
153
154 --4. Ambil data RGTO yang masih outstanding
155 -- 2017-12-15 fitra, menambahkan kondisi tidak sedang / sudah finalisasi
156 INSERT INTO tt_detail_saldo_stock_nasional(
157 session_id, flg_data, product_id, warehouse_id,
158 qty_stock, qty_reserved, qty_rgto, qty_buffer)
159 SELECT pSessionId, vFlgRgto, C.product_id, A.warehouse_id,
160 0, 0, COALESCE(F.qty_req_int- F.qty_rcv_int,C.qty_request), 0
161 FROM in_balance_req_transfer_out A
162 INNER JOIN in_inventory_item C ON A.inventory_id = C.inventory_id
163 INNER JOIN tt_product_saldo_stock_nasional B ON C.product_id = B.product_id
164 LEFT JOIN in_req_trf_out_po_balance_item F ON F.inventory_item_id = C.inventory_item_id
165 WHERE A.tenant_id = pTenantId AND
166 A.flg_real NOT IN (vYes,vflgFinalRgto) AND
167 (f_get_ou_bu_structure(A.ou_id)).ou_bu_id = vOuId AND
168 B.session_id = pSessionId AND
169 NOT EXISTS ( SELECT 1
170 FROM in_inventory D
171 INNER JOIN in_inventory_item E ON E.inventory_id = D.inventory_id AND E.ref_id = C.inventory_item_id AND E.ref_doc_type_id = vRgtoDocId
172 WHERE D.ref_id = A.inventory_id AND
173 D.ref_doc_type_id = vRgtoDocId AND
174 D.doc_type_id = vGtoDocId) AND
175 NOT EXISTS( SELECT 1
176 FROM in_inventory E
177 WHERE E.ref_id = A.inventory_id AND
178 E.ref_doc_type_id = vRgtoDocId AND
179 E.doc_type_id = vFinalizationRgtoDocId );
180
181 --5. Ambil data qty buffer stock (m_warehouse_buffer_stock) yg ada qty buffer
182 -- (qty_buffer > 0)
183 INSERT INTO tt_detail_saldo_stock_nasional(
184 session_id, flg_data, product_id, warehouse_id,
185 qty_stock, qty_reserved, qty_rgto, qty_buffer)
186 SELECT pSessionId, vFlgBuffer, A.product_id, A.warehouse_id,
187 0, 0, 0, A.qty_buffer
188 FROM m_warehouse_buffer_stock A
189 INNER JOIN tt_product_saldo_stock_nasional B ON A.product_id = B.product_id
190 WHERE A.tenant_id = pTenantId AND
191 (f_get_ou_bu_structure(A.ou_id)).ou_bu_id = vOuId AND
192 A.active = vYes AND
193 A.qty_buffer > 0 AND
194 B.session_id = pSessionId;
195
196 --6. Ambil data detail saldo stok nasional, rekap berdasarkan session, product,
197 -- dan warehouse. Tulis hasil rekap ke table temp dan product tt_product_saldo_stock_nasional yang tidak ada di tt_detail_saldo_stock_nasional
198 INSERT INTO tt_detail_saldo_stock_nasional(
199 session_id, flg_data, product_id, warehouse_id,
200 qty_stock, qty_reserved, qty_rgto, qty_buffer)
201 SELECT pSessionId, vFlgRekap, A.product_id, A.warehouse_id,
202 SUM(qty_stock), SUM(qty_reserved), SUM(qty_rgto), SUM(qty_buffer)
203 FROM tt_detail_saldo_stock_nasional A
204 WHERE A.session_id = pSessionId
205 GROUP BY A.session_id, A.product_id, A.warehouse_id;
206
207 --untuk product yang tidak di warehouse maka diseting 0 untuk qty nya
208 WITH warehouse AS (select warehouse_id
209 from tt_detail_saldo_stock_nasional
210 WHERE session_id = pSessionId AND flg_data = vFlgRekap
211 group by warehouse_id )
212 INSERT INTO tt_detail_saldo_stock_nasional(
213 session_id, flg_data, product_id, warehouse_id,
214 qty_stock, qty_reserved, qty_rgto, qty_buffer)
215 SELECT pSessionId, vFlgRekap, A.product_id, B.warehouse_id,
216 0, 0, 0, 0
217 FROM tt_product_saldo_stock_nasional A, warehouse B
218 WHERE A.session_id = pSessionId AND
219 NOT EXISTS (
220 SELECT 1 from tt_detail_saldo_stock_nasional C
221 WHERE A.session_id = C.session_id AND
222 C.flg_data = vFlgRekap AND
223 A.product_id = C.product_id AND
224 B.warehouse_id = C.warehouse_id
225 );
226 -- looping untuk menentukan nama kolom yang dinamis berdasarkan Qty Stock, Qty Reserved,
227 -- Qty RGTO, Qty Buffer
228 FOR vWarehouse IN SELECT f_get_warehouse_name(warehouse_id) as warehouse_name
229 FROM tt_detail_saldo_stock_nasional
230 WHERE session_id = pSessionId
231 GROUP BY warehouse_id
232 ORDER BY warehouse_id LOOP
233 vValueColoumNameStock := array_append(vValueColoumNameStock,CONCAT('Qty Stock ',vWarehouse.warehouse_name));
234 vValueColoumNameRsvd := array_append(vValueColoumNameRsvd,CONCAT('Qty Reserved ',vWarehouse.warehouse_name));
235 vValueColoumNameRgto := array_append(vValueColoumNameRgto,CONCAT('Qty RGTO ',vWarehouse.warehouse_name));
236 vValueColoumNameBuffer := array_append(vValueColoumNameBuffer,CONCAT('Qty Buffer ',vWarehouse.warehouse_name));
237
238 vColoumNameStock := CONCAT(vColoumNameStock,',','"Qty_Stock_',vWarehouse.warehouse_name,'" numeric');
239 vColoumNameRsvd := CONCAT(vColoumNameRsvd,',','"Qty_Reserved_',vWarehouse.warehouse_name,'" numeric');
240 vColoumNameRgto := CONCAT(vColoumNameRgto,',','"Qty_RGTO_',vWarehouse.warehouse_name,'" numeric');
241 vColoumNameBuffer := CONCAT(vColoumNameBuffer,',','"Qty_Buffer_',vWarehouse.warehouse_name,'" numeric');
242 END LOOP;
243 --vColoumNameStock := CONCAT(vColoumNameStock,',"',vColoumNameTotalStock,'" numeric');
244 --vColoumNameBuffer := CONCAT(vColoumNameBuffer,',"',vColoumNameTotalBuffer,'" numeric');
245
246 --penjagaan jika tidak ada item yang ditemukan makan hanya menampilkan header product code dan product name
247 IF NOT EXISTS (SELECT 1 FROM tt_detail_saldo_stock_nasional) THEN
248 vValueColoumNameAll = ARRAY[''];
249 Open pRefHeader FOR
250 EXECUTE '
251 SELECT 3 AS _COUNT, ''NO'', ''Product Code'', ''Product Name'', 1 AS ordinal
252 order by ordinal';
253 RETURN NEXT pRefHeader;
254 ELSE
255 -- menggabungkan nama kolom menjadi satu
256 vValueColoumNameStock := array_append(vValueColoumNameStock,vColoumNameTotalStock);
257 vValueColoumNameBuffer := array_append(vValueColoumNameBuffer,vColoumNameTotalBuffer);
258 vValueColoumNameAll = array_cat(vValueColoumNameStock,vValueColoumNameRsvd);
259 vValueColoumNameAll = array_cat(vValueColoumNameAll,vValueColoumNameRgto);
260 vValueColoumNameAll = array_cat(vValueColoumNameAll,vValueColoumNameBuffer);
261 vJumlahKolom := array_length(vValueColoumNameAll,1);
262
263 --membuat nama reaf header yang dinamis
264 SELECT ARRAY_TO_STRING(ARRAY( SELECT unnest(vValueColoumNameAll::text::text[])),''',''') INTO vColoumNameForHeader;
265 vColoumNameForHeader := CONCAT('''',vColoumNameForHeader,'''');
266
267 Open pRefHeader FOR
268 EXECUTE '
269 SELECT $1 + 3 AS _COUNT, ''NO'', ''Product Code'', ''Product Name'', '||vColoumNameForHeader||', 1 AS ordinal
270 order by ordinal'
271 USING vJumlahKolom;
272 RETURN NEXT pRefHeader;
273 END IF;
274
275 --item dibuat menggunakan crosstab sesuai kolom dinamis di atas
276 Open pRefDetail FOR
277 EXECUTE
278 ' SELECT row_number() OVER(),* FROM crosstab(
279 ''SELECT B.product_code, B.product_name, concat(''''Qty Stock '''',f_get_warehouse_name(warehouse_id)) as warehouse, qty_stock
280 FROM tt_detail_saldo_stock_nasional A
281 INNER JOIN tt_product_saldo_stock_nasional B ON B.session_id = A.session_id AND B.product_id = A.product_id
282 WHERE A.session_id = '''''||pSessionId||''''' AND A.flg_data = '''''||vFlgRekap||'''''
283 GROUP BY A.session_id, A.product_id, B.product_code, B.product_name, A.warehouse_id, A.qty_stock
284
285 UNION
286 SELECT B.product_code, B.product_name, ''''TOTAL NASIONAL'''' as warehouse, SUM(qty_stock)
287 FROM tt_detail_saldo_stock_nasional A
288 INNER JOIN tt_product_saldo_stock_nasional B ON B.session_id = A.session_id AND B.product_id = A.product_id
289 WHERE A.session_id = '''''||pSessionId||''''' AND A.flg_data = '''''||vFlgRekap||'''''
290 GROUP BY A.session_id, A.product_id, B.product_code, B.product_name
291
292 UNION
293 SELECT B.product_code, B.product_name, concat(''''Qty Reserved '''',f_get_warehouse_name(warehouse_id)) as warehouse, qty_reserved
294 FROM tt_detail_saldo_stock_nasional A
295 INNER JOIN tt_product_saldo_stock_nasional B ON B.session_id = A.session_id AND B.product_id = A.product_id
296 WHERE A.session_id = '''''||pSessionId||''''' AND A.flg_data = '''''||vFlgRekap||'''''
297 GROUP BY A.session_id, A.product_id, B.product_code, B.product_name, A.warehouse_id, A.qty_reserved
298
299 UNION
300 SELECT B.product_code, B.product_name, concat(''''Qty RGTO '''',f_get_warehouse_name(warehouse_id)) as warehouse, qty_rgto
301 FROM tt_detail_saldo_stock_nasional A
302 INNER JOIN tt_product_saldo_stock_nasional B ON B.session_id = A.session_id AND B.product_id = A.product_id
303 WHERE A.session_id = '''''||pSessionId||''''' AND A.flg_data = '''''||vFlgRekap||'''''
304 GROUP BY A.session_id, A.product_id, B.product_code, B.product_name, A.warehouse_id, A.qty_rgto
305
306 UNION
307 SELECT B.product_code, B.product_name, concat(''''Qty Buffer '''',f_get_warehouse_name(warehouse_id)) as warehouse, qty_buffer
308 FROM tt_detail_saldo_stock_nasional A
309 INNER JOIN tt_product_saldo_stock_nasional B ON B.session_id = A.session_id AND B.product_id = A.product_id
310 WHERE A.session_id = '''''||pSessionId||''''' AND A.flg_data = '''''||vFlgRekap||'''''
311 GROUP BY A.session_id, A.product_id, B.product_code, B.product_name, A.warehouse_id, A.qty_buffer
312
313 UNION
314 SELECT B.product_code, B.product_name, ''''BUFFER NASIONAL'''' as warehouse, SUM(qty_buffer)
315 FROM tt_detail_saldo_stock_nasional A
316 INNER JOIN tt_product_saldo_stock_nasional B ON B.session_id = A.session_id AND B.product_id = A.product_id
317 WHERE A.session_id = '''''||pSessionId||''''' AND A.flg_data = '''''||vFlgRekap||'''''
318 GROUP BY A.session_id, A.product_id, B.product_code, B.product_name
319 ORDER BY product_code, product_name'',
320 $$SELECT unnest('''||vValueColoumNameAll::text||'''::text[])$$
321 )
322 AS B ("product_code" character varying, "product_name" character varying
323 '||vColoumNameStock||', "'||vColoumNameTotalStock||'" numeric '||vColoumNameRsvd||' '||vColoumNameRgto||' '||vColoumNameBuffer||', "'||vColoumNameTotalBuffer||'" numeric)';
324 RETURN NEXT pRefDetail;
325
326 DELETE FROM tt_product_saldo_stock_nasional WHERE session_id = pSessionId;
327 DELETE FROM tt_detail_saldo_stock_nasional WHERE session_id = pSessionId;
328
329
330
331END;
332$BODY$
333 LANGUAGE plpgsql VOLATILE
334 COST 100
335 ROWS 1000;
336 /