· 6 years ago · Oct 21, 2019, 10:20 AM
1CREATE OR REPLACE FUNCTION f_upload_nrb(bigint)
2 RETURNS integer AS
3$BODY$
4
5DECLARE
6 pUlHeaderId ALIAS FOR $1;
7
8 vStatusOk character varying := 'OK';
9 vStatusFail character varying := 'FAIL';
10 vStatusFail2 character varying := 'FAIL2';
11 vEmpty character varying := '';
12 vDocTypeIdRetur bigint:=502;
13 vDocTypeIdSi bigint:=321;
14 vDocTypeIdSo bigint:=301;
15 vDocTypeIdDrafRetur bigint:=598;
16 vDocTypeIdRg bigint:=111;
17 vDocTypeIdPo bigint:=101;
18
19 vFlgYes character varying := 'Y';
20 vFlgNo character varying := 'N';
21 vZero bigint:=0;
22 vCount bigint;
23 vTenantId bigint;
24 vUserId bigint;
25 vNullId bigint := -99;
26 vZeroLpbRef character varying := '000-000000';
27 vRemark character varying;
28 vDefaultPartnerId bigint;
29 vSessionId character varying;
30 vDatetime character varying;
31
32 vStatusDocInProgress character varying:='I';
33 vStatusDocApproved character varying:='R';
34 vLedgerCodeInv character varying:='INV';
35 vLedgerCodeSales character varying:='SALES';
36 vLedgerCodePURCH character varying:='PURCH';
37 vDefaultOuId bigint := 10;
38 vDocDateCnRpi character varying;
39
40BEGIN
41
42 vSessionId := f_make_uid();
43 vDocDateCnRpi := f_get_upload_parameter(pUlHeaderId, 'cnRpiDate');
44 vRemark := f_get_upload_parameter(pUlHeaderId, 'remark');
45 vTenantId := f_get_upload_parameter(pUlHeaderId, 'tenantId');
46 vDefaultPartnerId := f_get_value_system_config_by_param_code(vTenantId, 'default.partner.for.retur')::bigint;
47 vDatetime := f_get_upload_parameter(pUlHeaderId, 'datetime');
48-- vOuId := f_get_upload_parameter(pUlHeaderId, 'ouId');
49 vUserId := f_get_upload_parameter(pUlHeaderId, 'userId');
50
51
52 IF EXISTS(SELECT 1 FROM jl_upload_skip_detail WHERE upload_header_id = pUlHeaderId) THEN
53 RAISE NOTICE 'ADA ITEM YANG DI SKIP';
54 END IF;
55 -- 0 Update NULL DATA
56 UPDATE ul_retur_header
57 SET invoice_id = vNullId
58 WHERE invoice_id IS NULL;
59
60 UPDATE ul_retur_detail
61 SET product_id = vNullId,
62 supplier_id = vNullId,
63 flag_dropship = vFlgNo
64 WHERE product_id IS NULL;
65
66 -- 1. validasi data yang di upload harus terdapat pada data NRB
67 UPDATE ul_nrb A
68 SET status = vStatusFail,
69 message = 'NRB tidak terdaftar, '
70 WHERE status = vEmpty
71 AND upload_header_id = pUlHeaderId
72 AND NOT EXISTS (
73 SELECT 1
74 FROM ul_retur_header B
75 WHERE A.nrb = B.retur_no
76 );
77
78 UPDATE ul_nrb A
79 SET status = vStatusFail,
80 message = 'terdapat nomor nrb duplikat, '
81 WHERE A.status = vEmpty
82 AND A.upload_header_id = pUlHeaderId
83 AND EXISTS (
84 SELECT nrb
85 FROM ul_nrb B
86 WHERE B.upload_header_id = pUlHeaderId
87 AND A.nrb = B.nrb
88 GROUP BY B.nrb
89 HAVING count(1) > 1
90 );
91
92 -- 2. validasi dokumen NRB sudah dibentuk
93 UPDATE ul_nrb A
94 SET status = vStatusFail,
95 message = 'Dokumen retur untuk NRB ini sudah pernah dibuat, '
96 WHERE upload_header_id = pUlHeaderId
97 AND status <> vStatusFail
98 AND EXISTS (
99 SELECT 1
100 FROM m_nrb_generated_document B
101 WHERE A.nrb = B.retur_no
102 AND B.ref_doc_type_id = vDocTypeIdRetur
103 );
104
105 -- 3. Mapping produk NRB dan validasi
106 -- 3.a mapping
107 UPDATE ul_retur_detail C
108 SET product_id = D.product_id
109 FROM ul_nrb A, ul_retur_header B, m_product D
110 WHERE A.nrb = B.retur_no
111 AND B.retur_header_id = C.retur_header_id
112 AND C.plu = D.sat_product_code
113 AND A.upload_header_id = pUlHeaderId
114 AND A.status <> vStatusFail
115 AND C.product_id = vNullId;
116
117 -- 3.b validasi
118 UPDATE ul_nrb A
119 SET status = vStatusFail,
120 message = 'mapping produk tidak ditemukan, '
121 WHERE upload_header_id = pUlHeaderId
122 AND status <> vStatusFail
123 AND EXISTS (
124 SELECT 1
125 FROM ul_retur_header B
126 JOIN ul_retur_detail C ON B.retur_header_id = C.retur_header_id
127 WHERE A.nrb = B.retur_no
128 AND C.product_id = vNullId
129 );
130
131 -- 4. mapping supplier product
132 UPDATE ul_retur_detail C
133 SET supplier_id = D.supplier_id,
134 flag_dropship = E.flag_dropship
135 FROM ul_nrb A, ul_retur_header B, m_product_consignment_supp_info D, m_partner_supplier_info E
136 WHERE A.nrb = B.retur_no
137 AND B.retur_header_id = C.retur_header_id
138 AND C.product_id = D.product_id
139 AND D.supplier_id = E.partner_id
140 AND A.upload_header_id = pUlHeaderId
141 AND A.status <> vStatusFail;
142
143-- -- 4b. validasi 1 nrb memiliki item dgn flg dropship supplier berbeda
144-- UPDATE ul_nrb A
145-- SET status = vStatusFail,
146-- message = 'NRB memiliki item dengan flg dropship supplier berbeda, '
147-- WHERE upload_header_id = pUlHeaderId
148-- AND status <> vStatusFail
149-- AND EXISTS (
150-- SELECT B.retur_no, count(retur_no)
151-- FROM (
152-- SELECT B.retur_no, C.flag_dropship
153-- FROM ul_retur_header B
154-- join ul_retur_detail C ON B.retur_header_id = C.retur_header_id
155-- GROUP BY B.retur_no, C.flag_dropship
156-- ) B
157-- WHERE A.nrb = B.retur_no
158-- GROUP BY B.retur_no
159-- HAVING count(retur_no) > 1
160-- );
161
162 -- 5. Mapping SI dan validasi
163 -- 5.a validasi 1 nrb terdapat lebih dari 1 SI
164-- UPDATE ul_nrb A
165-- SET status = vStatusFail,
166-- message = 'ditemukan lebih dari 1 Sales Invoice saat mapping SI dilakukan, '
167-- WHERE upload_header_id = pUlHeaderId
168-- AND status <> vStatusFail
169-- AND EXISTS (
170-- SELECT 1
171-- FROM ul_retur_header B
172-- JOIN sl_invoice C ON B.lpb_ref = C.lpb_sat_no AND C.doc_type_id = vDocTypeIdSi
173-- WHERE A.nrb = B.retur_no
174-- AND B.lpb_ref <> vZeroLpbRef
175-- HAVING count(lpb_sat_no) > 1
176-- );
177
178 -- 5.b.1 mapping untuk nrb yang hanya memiliki 1 SI
179 UPDATE ul_retur_header A
180 SET invoice_id = C.invoice_id,
181 invoice_no = C.doc_no,
182 invoice_date = C.doc_date
183 FROM ul_nrb B, sl_invoice C
184 WHERE A.retur_no = B.nrb
185 AND A.lpb_ref = C.lpb_sat_no
186 AND A.lpb_ref <> vZeroLpbRef
187 AND B.upload_header_id = pUlHeaderId
188 AND B.status <> vStatusFail
189 AND A.invoice_id = vNullId
190 AND NOT EXISTS (
191 SELECT 1
192 FROM ul_retur_header D
193 JOIN sl_invoice E ON D.lpb_ref = E.lpb_sat_no AND E.doc_type_id = vDocTypeIdSi
194 WHERE B.nrb = D.retur_no
195 AND D.lpb_ref <> vZeroLpbRef
196 HAVING count(lpb_sat_no) > 1
197 );
198
199 -- 5.b.2 mapping untuk nrb yang memiliki lebih dari 1 SI
200 WITH duplicate_lpb_sat_no_with_min_date AS (
201 -- GET MIN DOC DATE FOR DUPLICATE INVOICE WITH SAME LPB_SAT_NO
202 SELECT C.lpb_sat_no, MIN(C.doc_date) AS min_invoice_date
203 FROM ul_nrb A
204 JOIN ul_retur_header B ON A.nrb = B.retur_no
205 JOIN sl_invoice C ON B.lpb_ref = C.lpb_sat_no AND C.doc_type_id = vDocTypeIdSi
206 WHERE A.nrb = B.retur_no
207 AND B.lpb_ref <> vZeroLpbRef
208 AND A.upload_header_id = pUlHeaderId
209 AND A.status <> vStatusFail
210 GROUP BY C.lpb_sat_no
211 HAVING count(C.lpb_sat_no) > 1
212 ), duplicate_lpb_sat_no_in_invoice AS (
213 -- GET min invoice_id for duplicate invoice with lpb_sat_no
214 SELECT A.lpb_sat_no, B.doc_date AS invoice_date, MIN(B.invoice_id) AS min_invoice_id
215 FROM duplicate_lpb_sat_no_with_min_date A
216 JOIN sl_invoice B ON A.lpb_sat_no = B.lpb_sat_no AND B.doc_date = A.min_invoice_date
217 WHERE B.doc_type_id = vDocTypeIdSi
218 GROUP BY A.lpb_sat_no, B.doc_date
219 )
220 UPDATE ul_retur_header A
221 SET invoice_id = C.invoice_id,
222 invoice_no = C.doc_no,
223 invoice_date = C.doc_date
224 FROM ul_nrb B, sl_invoice C, duplicate_lpb_sat_no_in_invoice D
225 WHERE A.retur_no = B.nrb
226 AND A.lpb_ref = C.lpb_sat_no
227 AND C.lpb_sat_no = D.lpb_sat_no
228 AND A.lpb_ref <> vZeroLpbRef
229 AND B.upload_header_id = pUlHeaderId
230 AND B.status <> vStatusFail
231 AND A.invoice_id = vNullId
232 AND C.doc_date = D.invoice_date
233 AND C.invoice_id = D.min_invoice_id;
234
235 -- 5.c validasi NRB yang mapping SI nya tidak ditemukan
236 UPDATE ul_nrb A
237 SET status = vStatusFail,
238 message = 'tidak ditemukan mapping nomor lpb sat terhadap Sales Invoice, '
239 FROM ul_retur_header B
240 WHERE A.nrb = B.retur_no
241 AND A.upload_header_id = pUlHeaderId
242 AND B.lpb_ref <> vZeroLpbRef
243 AND A.status <> vStatusFail
244 AND B.invoice_id = vNullId;
245
246-- --validasi tgl nrb belum tutup bulan inv
247-- UPDATE ul_nrb A
248-- SET status = vStatusFail,
249-- message = 'Nrb dengan tgl dokumen : '||B.retur_date||' tidak bisa dibuat dokumen Return Note / Claim Note karena sudah tutup bulan Inventory : ' ||B.date_year_month||' ,'
250-- FROM (SELECT B.retur_no, B.draf_ref,C.flag_dropship, B.retur_date, D.date_year_month
251-- FROM ul_retur_header B
252-- join ul_retur_detail C ON B.retur_header_id = C.retur_header_id
253-- INNER JOIN m_admin_process_ledger D ON D.ledger_code = vLedgerCodeInv AND
254-- D.flag_process <> vFlgNo AND
255-- D.date_year_month = SUBSTRING(B.retur_date, 1, 6) AND
256-- D.ou_id = vDefaultOuId AND
257-- D.tenant_id = vTenantId
258-- GROUP BY B.retur_no, B.draf_ref, C.flag_dropship, B.retur_date, D.date_year_month) B
259-- WHERE A.nrb = B.retur_no
260-- AND A.upload_header_id = pUlHeaderId
261-- AND A.status <> vStatusFail;
262--
263-- --validasi tgl nrb belum tutup bulan sales
264-- UPDATE ul_nrb A
265-- SET status = vStatusFail,
266-- message = 'Nrb dengan tgl dokumen : '||B.retur_date||' tidak bisa dibuat dokumen Return Sales Invoice karena sudah tutup bulan Sales : ' ||B.date_year_month||' ,'
267-- FROM (SELECT B.retur_no, B.draf_ref,C.flag_dropship, B.retur_date, D.date_year_month
268-- FROM ul_retur_header B
269-- join ul_retur_detail C ON B.retur_header_id = C.retur_header_id
270-- INNER JOIN m_admin_process_ledger D ON D.ledger_code = vLedgerCodeSales AND
271-- D.flag_process <> vFlgNo AND
272-- D.date_year_month = SUBSTRING(B.retur_date, 1, 6) AND
273-- D.ou_id = vDefaultOuId AND
274-- D.tenant_id = vTenantId
275-- GROUP BY B.retur_no, B.draf_ref, C.flag_dropship, B.retur_date, D.date_year_month) B
276-- WHERE A.nrb = B.retur_no
277-- AND A.upload_header_id = pUlHeaderId
278-- AND A.status <> vStatusFail;
279
280
281 --validasi tgl nrb belum tutup bulan purch
282 UPDATE ul_nrb A
283 SET status = vStatusFail,
284 message = 'Nrb dengan tgl dokumen : '||B.retur_date||' tidak bisa dibuat dokumen Return Purch Invoice karena sudah tutup bulan Purch : ' ||B.date_year_month||' ,'
285 FROM (SELECT B.retur_no, B.draf_ref,C.flag_dropship, B.retur_date, D.date_year_month
286 FROM ul_retur_header B
287 join ul_retur_detail C ON B.retur_header_id = C.retur_header_id
288 INNER JOIN m_admin_process_ledger D ON D.ledger_code = vLedgerCodePURCH AND
289 D.flag_process <> vFlgNo AND
290 D.date_year_month = SUBSTRING(B.retur_date, 1, 6) AND
291 D.ou_id = vDefaultOuId AND
292 D.tenant_id = vTenantId
293 GROUP BY B.retur_no, B.draf_ref, C.flag_dropship, B.retur_date, D.date_year_month) B
294 WHERE A.nrb = B.retur_no
295 AND A.upload_header_id = pUlHeaderId
296 AND A.status <> vStatusFail
297 AND B.flag_dropship = vFlgYes;
298
299 -- 6.A Validasi Draft retur untuk NRB non dropship
300 -- belum dapat di test karena dokumen draft retur belum dibuat
301 UPDATE ul_nrb A
302 SET status = vStatusFail,
303 message = 'dokumen DRAF RETUR tidak ditemukan , '
304 FROM (SELECT B.retur_no, B.draf_ref,C.flag_dropship
305 FROM ul_retur_header B
306 join ul_retur_detail C ON B.retur_header_id = C.retur_header_id
307 GROUP BY B.retur_no, B.draf_ref, C.flag_dropship) B
308 WHERE A.nrb = B.retur_no
309 AND A.upload_header_id = pUlHeaderId
310 AND A.status <> vStatusFail
311 AND B.flag_dropship = vFlgNo
312 AND NOT EXISTS (
313 SELECT 1
314 FROM in_inventory C
315 WHERE B.draf_ref = C.doc_no
316 AND C.doc_type_id = vDocTypeIdDrafRetur
317 AND C.status_doc = vStatusDocApproved
318 );
319
320 -- 6.B Validasi terdaat item NRB yang tidak terdaftar pada Draft retur
321 -- belum dapat di test karena dokumen draft retur belum dibuat
322 UPDATE ul_nrb A
323 SET status = vStatusFail,
324 message = 'terdapat item NRB yang tidak terdafter pada dokumen Draf Retur, '
325 FROM ul_retur_header B
326 JOIN ul_retur_detail C ON B.retur_header_id = C.retur_header_id
327 WHERE A.nrb = B.retur_no
328 AND A.upload_header_id = pUlHeaderId
329 AND A.status <> vStatusFail
330 AND C.flag_dropship = vFlgNo
331 AND NOT EXISTS (
332 SELECT 1
333 FROM in_inventory D
334 JOIN in_inventory_item E ON D.inventory_id = E.inventory_id
335 WHERE B.draf_ref = D.doc_no
336 AND D.doc_type_id = vDocTypeIdDrafRetur
337 AND E.product_id = C.product_id
338 );
339
340 -- 6.C Validasi qty retur lbh kecil sama dengan qty terima pada dokumen draft etur
341 -- belum dapat di test karena dokumen draft retur belum dibuat
342 UPDATE ul_nrb A
343 SET status = vStatusFail,
344 message = 'terdapat product dengan cummulative qty retur yang melebihi qty request draft retur, '
345 FROM (
346 SELECT A.upload_header_id, A.nrb, D.inventory_id, C.product_id, C.qty_pcs, E.qty_request, E.qty_retur, E.qty_retur + SUM(C.qty_pcs::numeric) over (partition by D.inventory_id, E.product_id) as total_retur
347 FROM ul_nrb A
348 JOIN (SELECT B.retur_no, B.retur_header_id, B.draf_ref,C.flag_dropship
349 FROM ul_retur_header B
350 join ul_retur_detail C ON B.retur_header_id = C.retur_header_id
351 GROUP BY B.retur_no, B.retur_header_id, B.draf_ref, C.flag_dropship) B ON A.nrb = B.retur_no
352 JOIN ul_retur_detail C ON B.retur_header_id = C.retur_header_id
353 JOIN in_inventory D ON B.draf_ref = D.doc_no AND D.doc_type_id = vDocTypeIdDrafRetur
354 JOIN in_balance_draft_retur E ON D.inventory_id = E.draft_retur_id AND C.product_id = E.product_id
355 WHERE A.upload_header_id = pUlHeaderId
356 AND A.status <> vStatusFail
357 AND B.flag_dropship = vFlgNo
358 ) B
359 WHERE A.nrb = B.nrb
360 AND A.upload_header_id = pUlHeaderId
361 AND B.total_retur > B.qty_request;
362
363 -- 6. D Validasi tutup bulan harus dibuka sesuai tanggal dokumen yg digunakan. NRB non dropship menggunakan tanggal DR, NRB dropship menggunakan tgl NRB
364 -- validasi dokumen RN atas NRB non dropship
365 UPDATE ul_nrb A
366 SET status = vStatusFail,
367 message = 'untuk melanjutkan proses dokumen NRB non dropship dengan nomor '||B.retur_no||' silahkan lakukan buka tutup bulan INV untuk tahun bulan '||LEFT(C.doc_date, 6)||' terlebih dahulu, '
368 FROM (SELECT B.retur_no, B.draf_ref,C.flag_dropship
369 FROM ul_retur_header B
370 join ul_retur_detail C ON B.retur_header_id = C.retur_header_id
371 GROUP BY B.retur_no, B.draf_ref, C.flag_dropship) B
372 JOIN in_inventory C ON B.draf_ref = C.doc_no AND C.doc_type_id = vDocTypeIdDrafRetur AND C.status_doc = vStatusDocApproved
373 JOIN m_admin_process_ledger D ON LEFT(C.doc_date,6) = D.date_year_month
374 WHERE A.nrb = B.retur_no
375 AND A.upload_header_id = pUlHeaderId
376 AND A.status <> vStatusFail
377 AND B.flag_dropship = vFlgNo
378 AND D.status_ledger = 1::CHARACTER VARYING
379 AND D.flag_process = vFlgYes
380 AND D.ledger_code = vLedgerCodeInv;
381
382 -- 6. E Validasi tutup bulan harus dibuka sesuai tanggal dokumen yg digunakan. NRB non dropship menggunakan tanggal DR, NRB dropship menggunakan tgl NRB
383 -- validasi dokumen RN atas NRB dropship
384 UPDATE ul_nrb A
385 SET status = vStatusFail,
386 message = 'untuk melanjutkan proses dokumen NRB dropship dengan nomor '||B.retur_no||' silahkan lakukan buka tutup bulan INV untuk tahun bulan '||LEFT(B.retur_date, 6)||' terlebih dahulu, '
387 FROM (SELECT B.retur_no, B.retur_date,C.flag_dropship
388 FROM ul_retur_header B
389 join ul_retur_detail C ON B.retur_header_id = C.retur_header_id
390 GROUP BY B.retur_no, B.retur_date, C.flag_dropship) B
391 JOIN m_admin_process_ledger C ON LEFT(B.retur_date,6) = C.date_year_month
392 WHERE A.nrb = B.retur_no
393 AND A.upload_header_id = pUlHeaderId
394 AND A.status <> vStatusFail
395 AND B.flag_dropship = vFlgYes
396 AND C.status_ledger = 1::CHARACTER VARYING
397 AND C.flag_process = vFlgYes
398 AND C.ledger_code = vLedgerCodeInv;
399
400 -- 6. F Validasi tutup bulan harus dibuka sesuai tanggal dokumen yg digunakan. NRB non dropship menggunakan tanggal DR, NRB dropship menggunakan tgl NRB
401 -- validasi dokumen RSI atas NRB non dropship
402 UPDATE ul_nrb A
403 SET status = vStatusFail,
404 message = 'untuk melanjutkan proses dokumen NRB non dropship dengan nomor '||B.retur_no||' silahkan lakukan buka tutup bulan SALES untuk tahun bulan '||LEFT(C.doc_date, 6)||' terlebih dahulu, '
405 FROM (SELECT B.retur_no, B.draf_ref,C.flag_dropship
406 FROM ul_retur_header B
407 join ul_retur_detail C ON B.retur_header_id = C.retur_header_id
408 GROUP BY B.retur_no, B.draf_ref, C.flag_dropship) B
409 JOIN in_inventory C ON B.draf_ref = C.doc_no AND C.doc_type_id = vDocTypeIdDrafRetur AND C.status_doc = vStatusDocApproved
410 JOIN m_admin_process_ledger D ON LEFT(C.doc_date,6) = D.date_year_month
411 WHERE A.nrb = B.retur_no
412 AND A.upload_header_id = pUlHeaderId
413 AND A.status <> vStatusFail
414 AND B.flag_dropship = vFlgNo
415 AND D.status_ledger = 1::CHARACTER VARYING
416 AND D.flag_process = vFlgYes
417 AND D.ledger_code = vLedgerCodeSales;
418
419 -- 6. G Validasi tutup bulan harus dibuka sesuai tanggal dokumen yg digunakan. NRB non dropship menggunakan tanggal DR, NRB dropship menggunakan tgl NRB
420 -- validasi dokumen RSI validasi NRB dropship
421 UPDATE ul_nrb A
422 SET status = vStatusFail,
423 message = 'untuk melanjutkan proses dokumen NRB dropship dengan nomor '||B.retur_no||' silahkan lakukan buka tutup bulan SALES untuk tahun bulan '||LEFT(B.retur_date, 6)||' terlebih dahulu, '
424 FROM (SELECT B.retur_no, B.retur_date,C.flag_dropship
425 FROM ul_retur_header B
426 join ul_retur_detail C ON B.retur_header_id = C.retur_header_id
427 GROUP BY B.retur_no, B.retur_date, C.flag_dropship) B
428 JOIN m_admin_process_ledger C ON LEFT(B.retur_date,6) = C.date_year_month
429 WHERE A.nrb = B.retur_no
430 AND A.upload_header_id = pUlHeaderId
431 AND A.status <> vStatusFail
432 AND B.flag_dropship = vFlgYes
433 AND C.status_ledger = 1::CHARACTER VARYING
434 AND C.flag_process = vFlgYes
435 AND C.ledger_code = vLedgerCodeSales;
436
437 -- 7. tarik data header NRB ke 1 tabel
438 INSERT INTO tt_retur_header(
439 session_id, retur_header_id, nrb, retur_date, draf_ref, lpb_ref,
440 invoice_id, invoice_no, invoice_date,
441 partner_id, so_id, so_doc_type_id, do_id,
442 tax_no, tax_date, tax_curr_code,
443 tot_netto, tot_ppn, tot_ppn_bm, total_retur)
444 SELECT vSessionId, B.retur_header_id, A.nrb, B.retur_date, B.draf_ref, B.lpb_ref,
445 COALESCE(D.invoice_id, vNullId), COALESCE(D.doc_no, vEmpty), COALESCE(D.doc_date,vEmpty),
446 COALESCE(D.partner_id, vDefaultPartnerId), COALESCE(D.ref_id, vNullId), COALESCE(D.ref_doc_type_id, vNullId), COALESCE(E.do_id, vNullId),
447 COALESCE(DD.tax_no, vEmpty), COALESCE(DD.tax_date, vEmpty), COALESCE(DD.tax_curr_code, vEmpty),
448 F.tot_netto::numeric, F.tot_ppn::numeric, F.tot_ppn_bm::numeric, F.total_retur::numeric
449 FROM ul_nrb A
450 JOIN ul_retur_header B ON A.nrb = B.retur_no
451 JOIN ul_retur_total F ON B.retur_header_id = F.retur_header_id
452 LEFT JOIN sl_invoice D ON B.invoice_id = D.invoice_id
453 LEFT JOIN sl_invoice_tax DD ON D.invoice_id = DD.invoice_id
454 LEFT JOIN sl_do E ON D.ref_id = E.ref_id AND D.ref_doc_type_id = E.ref_doc_type_id
455 WHERE A.upload_header_id = pUlHeaderId
456 AND A.status <> vStatusFail;
457
458 -- 8. tarik data detail item NRB ke 1 tabel
459 INSERT INTO tt_retur_detail(
460 session_id, retur_header_id, retur_detail_id, plu, item_name,
461 qty_pcs, qty_crt, retur_price, ppn_bm, ppn_k, net, total, remark,
462 line_no, product_id, supplier_id, flag_dropship,
463 ref_doc_type_id, ref_id, ref_item_id)
464 SELECT vSessionId, A.retur_header_id, B.retur_detail_id,
465 B.plu, B.item_name, B.qty_pcs::numeric, B.qty_crt::numeric, B.retur_price::numeric, B.ppn_bm::numeric, B.ppn_k::numeric, B.net::numeric,
466 B.total::numeric, B.remark, B.line_no::bigint, B.product_id, B.supplier_id, B.flag_dropship,
467 COALESCE(C.doc_type_id, vNullId), COALESCE(C.inventory_id, vNullId), COALESCE(D.inventory_item_id, vNullId)
468 FROM tt_retur_header A
469 JOIN ul_retur_detail B ON A.retur_header_id = B.retur_header_id
470 LEFT JOIN in_inventory C ON A.draf_ref = C.doc_no AND C.doc_type_id = vDocTypeIdDrafRetur
471 LEFT JOIN in_inventory_item D ON C.inventory_id = D.inventory_id AND B.product_id = D.product_id
472 WHERE A.session_id = vSessionId;
473
474 -- 27 september 2019, tgl retur untuk NRB non dropship = tanggal DR
475 -- 9. update retur date jadi tgl DR khusus NRB non dropshup
476 UPDATE tt_retur_header A
477 SET retur_date = C.doc_date
478 FROM in_inventory C
479 WHERE A.draf_ref = C.doc_no
480 AND C.doc_type_id = vDocTypeIdDrafRetur
481 AND EXISTS (
482 SELECT 1
483 FROM tt_retur_detail B
484 WHERE A.retur_header_id = B.retur_header_id
485 AND B.flag_dropship = vFlgNo
486 )
487 AND A.session_id = vSessionId;
488
489 IF EXISTS (
490 SELECT 1
491 FROM ul_nrb
492 WHERE upload_header_id = pUlHeaderId
493 AND status <> vStatusFail
494 ) THEN
495 -- generate dokumen RSI
496 PERFORM f_generate_retur_note_by_nrb(vSessionId, vTenantId, vUserId, vDatetime, vRemark);
497
498 -- generate dokumen RSi
499 PERFORM f_generate_retur_sales_invoice_by_nrb(vSessionId, vTenantId, vUserId, vDatetime, vRemark);
500
501 UPDATE ul_retur_header A
502 SET status = vStatusDocInProgress
503 FROM ul_nrb B
504 WHERE A.retur_no::character varying = B.nrb
505 AND B.upload_header_id = pUlHeaderId
506 AND B.status <> vStatusFail;
507
508 END IF;
509
510 -- after training 18 june 19, CN RPI hanya dibuat untuk NRB non dropship
511 IF EXISTS(
512 SELECT 1
513 FROM tt_retur_detail A
514 WHERE A.session_id = vSessionId
515 AND A.flag_dropship = vFlgYes
516 )THEN
517 -- PROSES CARI RG ATAS ITEM NRB --
518 -- 1.A CARI RG UNTUK ITEM DROPSHIP YANG LPB REF NYA SAMA DENGAN EXT DOC NO RG EXISTING
519 -- KHUSUS UNTUK 1 NRB 1 INVOICE
520 INSERT INTO tt_retur_nrb_rg_checker (
521 session_id, retur_header_id, nrb, rg_id, rg_item_id,
522 po_id, po_item_id, product_id, qty_return_nrb,
523 current_return, qty_rcv, qty_return,
524 method, flag_dropship
525 )
526 SELECT vSessionId, A.retur_header_id, A.nrb, C.receive_goods_id AS rg_id, D.receive_goods_item_id AS rg_item_id,
527 C.ref_id AS po_id, D.ref_id AS po_item_id, B.product_id, B.qty_pcs,
528 E.qty_return + SUM(B.qty_pcs) over (partition by B.product_id, C.receive_goods_id ORDER BY B.qty_pcs, A.retur_header_id) AS current_return, E.qty_rcv, E.qty_return,
529 'DROPSHIP', B.flag_dropship
530 FROM tt_retur_header A
531 JOIN tt_retur_detail B ON A.retur_header_id = B.retur_header_id AND A.session_id = B.session_id
532 JOIN pu_receive_goods C ON C.doc_type_id = vDocTypeIdRg and C.ext_doc_no = A.lpb_ref AND B.supplier_id = C.partner_id
533 JOIN pu_receive_goods_item D ON C.receive_goods_id = D.receive_goods_id AND B.product_id = D.product_id
534 JOIN in_balance_receive_goods_item E ON D.receive_goods_item_id = E.receive_goods_item_id
535 WHERE A.session_id = vSessionId
536 AND A.lpb_ref <> vZeroLpbRef
537 AND B.flag_dropship = vFlgYes
538 AND NOT EXISTS (
539 SELECT 1
540 FROM ul_retur_header F
541 JOIN sl_invoice G ON F.lpb_ref = G.lpb_sat_no AND G.doc_type_id = vDocTypeIdSi
542 WHERE A.nrb = F.retur_no
543 AND F.lpb_ref <> vZeroLpbRef
544 HAVING count(lpb_sat_no) > 1
545 );
546
547 -- 1.B CARI RG UNTUK ITEM DROPSHIP YANG LPB REF NYA SAMA DENGAN EXT DOC NO RG EXISTING
548 -- KHUSUS UNTUK 1 NRB n INVOICE
549 INSERT INTO tt_retur_nrb_rg_checker (
550 session_id, retur_header_id, nrb, rg_id, rg_item_id,
551 po_id, po_item_id, product_id, qty_return_nrb,
552 current_return, qty_rcv, qty_return,
553 method, flag_dropship
554 )
555 SELECT vSessionId, A.retur_header_id, A.nrb, C.receive_goods_id AS rg_id, D.receive_goods_item_id AS rg_item_id,
556 C.ref_id AS po_id, D.ref_id AS po_item_id, B.product_id, B.qty_pcs,
557 E.qty_return + SUM(B.qty_pcs) over (partition by B.product_id, C.receive_goods_id ORDER BY B.qty_pcs, A.retur_header_id) AS current_return, E.qty_rcv, E.qty_return,
558 'DROPSHIP', B.flag_dropship
559 FROM tt_retur_header A
560 JOIN tt_retur_detail B ON A.retur_header_id = B.retur_header_id AND A.session_id = B.session_id
561 JOIN sl_invoice BB ON A.invoice_id = BB.invoice_id AND BB.doc_type_id = vDocTypeIdSi
562 JOIN pu_po BBB ON BB.ref_doc_type_id = BBB.ref_doc_type_id AND BB.ref_id = BBB.ref_id
563 JOIN pu_receive_goods C ON C.doc_type_id = vDocTypeIdRg and C.ext_doc_no = A.lpb_ref AND B.supplier_id = C.partner_id AND C.ref_doc_type_id = vDocTypeIdPo AND C.ref_id = BBB.po_id
564 JOIN pu_receive_goods_item D ON C.receive_goods_id = D.receive_goods_id AND B.product_id = D.product_id
565 JOIN in_balance_receive_goods_item E ON D.receive_goods_item_id = E.receive_goods_item_id
566 WHERE A.session_id = vSessionId
567 AND A.lpb_ref <> vZeroLpbRef
568 AND B.flag_dropship = vFlgYes
569 AND EXISTS (
570 SELECT 1
571 FROM ul_retur_header F
572 JOIN sl_invoice G ON F.lpb_ref = G.lpb_sat_no AND G.doc_type_id = vDocTypeIdSi
573 WHERE A.nrb = F.retur_no
574 AND F.lpb_ref <> vZeroLpbRef
575 HAVING count(lpb_sat_no) > 1
576 );
577
578 -- 2. UPDATE NRB YANG KETEMU RG BERDASARKAN LPB REF NAMUN QTY RCV RG NYA < QTY RETUR
579 UPDATE ul_nrb A
580 SET status = vStatusFail2,
581 message = 'terdapat product dengan qty rcv PO < dari qty retur'
582 FROM tt_retur_nrb_rg_checker B
583 WHERE A.nrb = B.nrb
584 AND A.upload_header_id = pUlHeaderId
585 AND B.session_id = vSessionId
586 AND B.qty_rcv < B.current_return
587 AND B.method = 'DROPSHIP';
588
589 -- 3. DELETE ITEM DROPSHIP DARI TT YANG KETEMU RG BERDASARKAN LPB REF NAMUN QTY RCV RG NYA < QTY RETUR
590 DELETE FROM tt_retur_nrb_rg_checker A
591 WHERE EXISTS (
592 SELECT 1
593 FROM ul_nrb B
594 WHERE A.nrb = B.nrb
595 AND A.session_id = vSessionId
596 AND B.upload_header_id = pUlHeaderId
597 AND B.status = vStatusFail2
598 );
599
600 -- 4. CARI ITEM DROPSHIP YANG TIDAK KETEMU RG NYA DAN NON DROPSHIP UNTUK DICARIKAN RG MENGGUNAKAN METODE NON DROPSHIP
601 INSERT INTO tt_item_without_rg_list
602 (
603 session_id, retur_header_id, upload_header_id, retur_date,
604 nrb, product_id, supplier_id, qty_pcs, flag_dropship
605 )
606 SELECT vSessionId, B.retur_header_id, A.upload_header_id, B.retur_date, B.nrb, C.product_id,
607 C.supplier_id, C.qty_pcs, C.flag_dropship
608 FROM ul_nrb A
609 JOIN tt_retur_header B ON A.nrb = B.nrb
610 JOIN tt_retur_detail C ON B.retur_header_id = C.retur_header_id AND B.session_id = C.session_id
611 WHERE A.upload_header_id = pUlHeaderId
612 AND B.session_id = vSessionId
613 --AND A.status <> vStatusFail
614 AND A.status <> vStatusFail2
615 AND NOT EXISTS(
616 SELECT 1
617 FROM tt_retur_nrb_rg_checker D
618 WHERE B.retur_header_id = D.retur_header_id
619 AND D.session_id = vSessionId
620 )
621 AND C.flag_dropship = vFlgYes;
622
623 -- 5. CARI RG UNTK ITEM NONDROPSHIP DAN DROPSHIP YANG TIDAK KETEMU RG NYA BY LPB REF
624 -- BILA ITEMNYA DROPSHIP, MAKA CEK RG DAN ITEM NYA SAMA ATAU TIDAK DGN RG YANG DICARI SEBELUMNYA QTY RETUR, BILA SAMA QTY RETUR RETUR SEBELUMNYA DITAMBAH KE QTY RETURPADA QUERY UNTUK DIAKUMULASI UNTUK DICEK KEMBALI QTY RETUR <= QTY RCV BALANCE
625 -- RG DICARI DALAM 6 BULAN TERAKHIR DIHITUNG DARI TANGGAL RETUR (NRB)
626 WITH find_rg_raw AS(
627 -- CARI LIST RG
628 SELECT D.retur_header_id, D.nrb, A.receive_goods_id, B.receive_goods_item_id,
629 B.product_id,
630 D.qty_pcs,
631 COALESCE(E.used_return_qty,0 ) AS used_return,
632 --COALESCE(E.used_return_qty,0 ) + C.qty_return + SUM(D.qty_pcs) over (partition by B.product_id, A.receive_goods_id ORDER BY D.retur_header_id desc, C.qty_rcv) AS current_retur,
633 C.qty_rcv, C.qty_return, D.flag_Dropship
634 FROM pu_receive_goods A
635 JOIN pu_receive_goods_item B ON A.receive_goods_id = B.receive_goods_id
636 JOIN in_balance_receive_goods_item C ON B.receive_goods_item_id = C.receive_goods_item_id
637 JOIN tt_item_without_rg_list D ON B.product_id = D.product_id AND A.partner_id = D.supplier_id AND D.session_id= vSessionId
638 LEFT JOIN (
639 SELECT Z.product_id, SUM(qty_return_nrb) AS used_return_qty, rg_id, rg_item_id
640 FROM tt_retur_nrb_rg_checker Z
641 WHERE Z.session_id = vSessionId
642 GROUP BY Z.product_id, Z.rg_id, Z.rg_item_id
643 ) E ON D.product_id = E.product_id AND A.receive_goods_id = E.rg_id AND B.receive_goods_item_id = E.rg_item_id
644 WHERE
645 A.doc_date BETWEEN to_char(to_timestamp(D.retur_date,'YYYYMMDD') - '6 MONTH'::INTERVAL, 'YYYYMMDD') AND D.retur_date
646 AND C.qty_rcv > D.qty_pcs + C.qty_return
647 AND EXISTS (
648 SELECT 1
649 FROM pu_invoice Z
650 WHERE A.ref_id = Z.ref_id
651 AND A.ref_doc_type_id = Z.ref_doc_type_id
652 AND Z.doc_type_id = 131
653 )
654 ORDER BY B.product_id, A.receive_goods_id, D.qty_pcs, C.qty_rcv
655 ), filter AS (
656 SELECT A.retur_header_id, A.nrb, A.receive_goods_id, A.receive_goods_item_id,
657 A.product_id,
658 A.qty_pcs,
659 A.used_return,
660 A.used_return + A.qty_return + SUM(A.qty_pcs) over (partition by A.product_id,A.receive_goods_id ORDER BY A.qty_pcs DESC , A.product_id,A.receive_goods_id, A.retur_header_id) AS current_retur,
661 A.qty_rcv, A.qty_return, A.flag_Dropship
662 FROM find_rg_raw A
663 ), find_rg AS (
664 -- RG DIFILTER 1 NRB, 1 PRODUCT, 1 RG
665 SELECT A.retur_header_id, A.nrb, A.product_id, MAX(B.receive_goods_id) AS rg_id
666 FROM tt_item_without_rg_list A
667 JOIN filter B ON A.product_id = B.product_id
668 AND A.retur_header_id = B.retur_header_id
669 WHERE A.session_id = vSessionId
670 AND B.current_retur <= B.qty_rcv
671 GROUP BY A.retur_header_id, A.product_id, A.nrb
672 )
673 -- DICATAT KE TEMPORER TABLE
674 INSERT INTO tt_retur_nrb_rg_checker (
675 session_id, retur_header_id, nrb, rg_id, rg_item_id,
676 po_id, po_item_id, product_id, qty_return_nrb, current_return, qty_rcv, qty_return,
677 method, flag_dropship
678 )
679 SELECT vSessionId, A.retur_header_id, A.nrb, A.rg_id, BB.receive_goods_item_id,
680 B.ref_id, BB.ref_id, A.product_id, C.qty_pcs, D.current_retur, D.qty_rcv, D.qty_return,
681 'NONDROPSHIP', D.flag_dropship
682 FROM find_rg A
683 JOIN pu_receive_goods B ON A.rg_id = B.receive_goods_id
684 JOIN pu_receive_goods_item BB ON B.receive_goods_id = BB.receive_goods_id AND A.product_id = BB.product_id
685 JOIN tt_item_without_rg_list C ON BB.product_id = C.product_id
686 AND A.retur_header_id = C.retur_header_id
687 JOIN filter D ON C.product_id = D.product_id
688 AND B.receive_goods_id = D.receive_goods_id
689 AND BB.receive_goods_item_id = D.receive_goods_item_id
690 AND C.qty_pcs = D.qty_pcs
691 WHERE C.session_id = vSessionId;
692
693 -- 6. UPDATE NRB YANG ITEM DROPSHIP/ NONDROPSHIP NYA TIDAK MEMILIKI RG
694 UPDATE ul_nrb A
695 SET status = vStatusFail,
696 message = 'terdapat product yang tidak ditemukan RG dalam 6 bulan terhitung dari tanggal retur NRB'
697 FROM tt_retur_header B
698 JOIN tt_retur_detail C ON B.session_id = C.session_id AND B.retur_header_id = C.retur_header_id
699 WHERE A.nrb = B.nrb
700 AND A.upload_header_id = pUlHeaderId
701 AND B.session_id = vSessionId
702 AND A.status <> vStatusFail
703 AND NOT EXISTS (
704 SELECT 1
705 FROM tt_retur_nrb_rg_checker D
706 WHERE A.nrb = D.nrb
707 AND C.retur_header_id = D.retur_header_id
708 AND D.session_id = vSessionId
709 AND C.product_id = D.product_id
710 )
711 AND C.flag_dropship = vFlgYes;
712
713
714
715
716 -- 7 ADD CLAIM ABLE QTY TO TEMPORER_TABLE
717 -- a. update qty claim yang memiliki draft retur
718 UPDATE tt_retur_nrb_rg_checker B
719 SET draft_retur_id = A.ref_id,
720 qty_claim = CASE WHEN A.qty_realization > A.qty_retur THEN A.qty_return_nrb ELSE CASE WHEN A.qty_realization > (A.qty_retur - A.sum_over_qty) THEN A.qty_realization - (A.qty_retur - A.sum_over_qty) ELSE 0 END END
721 FROM (
722 SELECT A.retur_header_id, A.product_id, B.ref_id, A.qty_return_nrb, C.qty_realization, C.qty_retur, SUM(A.qty_return_nrb) OVER (PARTITION BY A.product_id, C.draft_retur_id ORDER BY A.qty_return_nrb, A.retur_header_id) AS sum_over_qty
723 FROM tt_retur_nrb_rg_checker A
724 JOIN tt_retur_detail B ON A.retur_header_id = B.retur_header_id
725 AND A.product_id = B.product_id
726 AND A.session_id = B.session_id
727 JOIN in_balance_draft_retur C ON B.ref_id = C.draft_retur_id
728 WHERE A.session_id = vSessionId
729 ORDER BY A.qty_return_nrb ASC
730 ) A
731 WHERE A.retur_header_id = B.retur_header_id AND A.product_id = B.product_id
732 AND B.session_id = vSessionId;
733
734 -- b. update qty claim yang tidak memiliki draft retur
735 UPDATE tt_retur_nrb_rg_checker B
736 SET qty_claim = B.qty_return_nrb
737 WHERE B.session_id = vSessionId
738 AND B.draft_retur_id = vNullId;
739
740 --validasi sudah tutup bulan purch dan inv sebelum buat Claim note dan return purchase invoice
741 IF EXISTS (
742 SELECT 1 FROM m_admin_process_ledger WHERE ledger_code IN(vLedgerCodePURCH, vLedgerCodeINV) AND
743 flag_process <> vFlgNo AND
744 date_year_month = SUBSTRING(vDocDateCnRpi, 1, 6) AND
745 ou_id = vDefaultOuId AND
746 tenant_id = vTenantId
747 ) THEN
748 UPDATE ul_nrb A
749 SET status = vStatusFail,
750 message = 'Tidak bisa dibuatkan Claim Note dan Return Purch Invoice karena sudah tutup bulan Inventory atau Purch : '||SUBSTRING(vDocDateCnRpi,1,6)
751 FROM tt_retur_header B
752 JOIN tt_retur_detail C ON B.session_id = C.session_id AND B.retur_header_id = C.retur_header_id
753 WHERE A.nrb = B.nrb
754 AND A.upload_header_id = pUlHeaderId
755 AND B.session_id = vSessionId
756 AND A.status <> vStatusFail
757 AND EXISTS (
758 SELECT 1
759 FROM tt_retur_nrb_rg_checker D
760 WHERE A.nrb = D.nrb
761 AND C.retur_header_id = D.retur_header_id
762 AND D.session_id = vSessionId
763 AND C.product_id = D.product_id
764 )
765 AND C.flag_dropship = vFlgYes;
766
767 DELETE FROM tt_retur_nrb_rg_checker WHERE session_id = vSessionId;
768
769 END IF;
770
771 IF EXISTS (
772 SELECT 1
773 FROM tt_retur_nrb_rg_checker
774 WHERE session_id = vSessionId
775 ) THEN
776
777 -- generate dokumen CN
778 PERFORM f_generate_claim_note_by_nrb(vSessionId, vTenantId, vUserId, vDatetime, vRemark, pUlHeaderId);
779
780 -- generate dokumen CN
781 PERFORM f_generate_return_purchase_invoice_by_nrb(vSessionId, vTenantId, vUserId, vDatetime, vRemark);
782
783 UPDATE ul_retur_header A
784 SET status = vStatusDocApproved
785 FROM ul_nrb B
786 WHERE A.retur_no::character varying = B.nrb
787 AND B.upload_header_id = pUlHeaderId
788 AND B.status <> vStatusFail;
789 END IF;
790 END IF;
791
792 -- Update semua yang vEmpty jadi vStatusOk dan update remark sesuai inputan
793 UPDATE ul_nrb
794 SET status = vStatusOk
795 WHERE status <> vStatusFail
796 AND upload_header_id = pUlHeaderId;
797
798 SELECT COUNT(ul_nrb_id) INTO vCount
799 FROM ul_nrb
800 WHERE upload_header_id = pUlHeaderId
801 AND status = vStatusFail;
802
803 DELETE FROM tt_retur_header WHERE session_id = vSessionId;
804 DELETE FROM tt_retur_detail WHERE session_id = vSessionId;
805 DELETE FROM tt_retur_nrb_rg_checker WHERE session_id = vSessionId;
806 DELETE FROM tt_item_without_rg_list WHERE session_id = vSessionId;
807
808 RETURN vCount;
809
810END;
811$BODY$
812 LANGUAGE plpgsql VOLATILE
813 COST 100;
814 /