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