· 6 years ago · Apr 08, 2019, 04:42 AM
1/*
2 * PS, 23 April 2015
3 */
4--Modified by Adrian, Jul 4, 2017, mengubah query hapus in_log_product_balance_stock
5
6CREATE OR REPLACE FUNCTION in_cancel_submit_return_note(bigint, character varying, bigint, character varying, bigint, character varying, character varying)
7 RETURNS void AS
8$BODY$
9DECLARE
10 pTenantId ALIAS FOR $1;
11 pSessionId ALIAS FOR $2;
12 pUserId ALIAS FOR $3; -- for update user id in transaction
13 pDatetime ALIAS FOR $4;
14 pReturnNoteId ALIAS FOR $5; -- Inventory id
15 pRemarkApproval ALIAS FOR $6; -- Remark for appear in approval reset history
16 pPrevProcessNo ALIAS FOR $7; -- Substitute for renaming prevous process no
17
18 vFlagYes character varying := 'Y';
19 vFunctionSubmit character varying := 'in_submit_return_note';
20 vStatusDraft character varying := 'D';
21 vStatusInProgress character varying := 'I';
22 vStatusRelease character varying := 'R';
23 vWorkflowStatusDraft character varying := 'DRAFT';
24 vFlgInvoice character varying;
25 vDocNo character varying;
26 vDocDate character varying;
27 vProductStatus character varying;
28 vScheme character varying;
29 vPkpStartDate character varying(8);
30 vReturnNoteDocDate character varying(8);
31 vDocTypeIdDo bigint := 311;
32 vReturnNoteDocTypeId bigint := 502;
33 vJournalTrxId bigint;
34 vOuId bigint;
35 vWarehouseId bigint;
36 vProductId bigint;
37 vProductBalanceId bigint;
38 vPartnerIdMagento bigint;
39 vTaxId bigint;
40 vReturnNotePartnerId bigint;
41 vTaxPercentage numeric;
42 result RECORD;
43 vClaimNoteDocTypeId bigint := 511;
44 vClaimNoteScheme character varying := 'EA02';
45 vClaimNoteId bigint;
46 vClaimNoteDocNo character varying;
47 vClaimNoteDocDate character varying;
48 vClaimNoteOuId bigint;
49 vClaimNoteJournalTrxId bigint;
50
51 vLedgerCode character varying := 'INV';
52 vStatusLedgerNotDone character varying := '0';
53
54 vDocJournal DOC_JOURNAL%ROWTYPE;
55 vOuStructure OU_BU_STRUCTURE%ROWTYPE;
56
57BEGIN
58
59 -- Menampung nilai partner_id magento dari sysconfig ke variable vPartnerIdMagento
60 SELECT f_get_value_system_config_by_param_code(pTenantId, 'partner.id.e.commerce')::bigint INTO vPartnerIdMagento;
61
62 -- Menampung nilai tax_id dan tax_percentage dari table m_tax ke variable vTaxId dan vTaxPercentage
63 SELECT tax_id, percentage INTO vTaxId, vTaxPercentage FROM m_tax WHERE tenant_id = pTenantId and tax_code = 'PPn';
64
65 -- Menampung nilai pkp start date dari sysconfig ke variable vPkpStartDate
66 SELECT f_get_value_system_config_by_param_code(pTenantId, 'pkp.start.date') INTO vPkpStartDate;
67
68 -- Menampung nilai doc_date return note dan partner_id return note ke dalam variable vReturnNoteDocDate dan vReturnNotePartnerId
69 SELECT A.doc_date, B.partner_bill_to_id INTO vReturnNoteDocDate, vReturnNotePartnerId
70 FROM in_inventory A
71 INNER JOIN sl_so B ON a.ref_doc_type_id = 301 AND A.ref_id = B.so_id
72 WHERE A.inventory_id = pReturnNoteId;
73
74 /*
75 * 1. Ubah status_doc D
76 * 2. Create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock (sama)
77 * 3. Update product_balance_stock (+ jadi -) X
78 * 4. Delete data from in_product_balance_stock
79 * 5. Delete data from in_log_product_balance_stock
80 * 6. Insert data temporer tt_in_so_balance_item
81 * 7. Update qty return di data sl_so_balance_item /->*, *->/
82 * 8. Hapus data sl_log_so_balance_item
83 * 9. Hapus data sl_so_balance_invoice (do_receipt_item_id ?????)
84 * 10. Hapus data sl_so_balance_invoice_tax (do_receipt_item_id ????)
85 * 11. PERFORM cancel gl_cancel_admin_journal_trx
86 * 12. DELETE gl_journal_trx
87 * 13. DELETE gl_journal_trx_item
88 * 14. DELETE gl_journal_trx_mapping
89 * 15. PERFORM f_reset_approval_to_draft
90 * 16. UPDATE t_process_message
91 */
92
93 -- get data
94 select f_get_ou_bu_structure(A.ou_to_id) AS ou,A.ou_to_id, A.warehouse_from_id, A.doc_no, A.doc_date, B.product_id,
95 B.product_balance_id, B.product_status, f_get_document_journal(A.doc_type_id) as doc, C.scheme
96 FROM in_inventory A, in_inventory_item B, m_document C
97 WHERE A.inventory_id = B.inventory_id AND
98 A.doc_type_id = C.doc_type_id AND
99 A.inventory_id = pReturnNoteId AND
100 A.doc_type_id = vReturnNoteDocTypeId INTO result;
101
102 IF FOUND THEN
103 vOuStructure := result.ou;
104 vOuId := result.ou_to_id;
105 vWarehouseId := result.warehouse_from_id;
106 vDocNo := result.doc_no;
107 vDocDate := result.doc_date;
108 vProductId := result.product_id;
109 vProductBalanceId := result.product_balance_id;
110 vProductStatus := result.product_status;
111 vDocJournal := result.doc;
112 vScheme := result.scheme;
113
114 IF NOT EXISTS (
115 SELECT 1
116 FROM m_admin_process_ledger e, m_ou_structure f, t_ou g
117 WHERE e.tenant_id = pTenantId
118 AND e.ou_id = f.ou_bu_id
119 AND f.ou_id = vOuId
120 AND e.date_year_month = SUBSTR(vDocDate, 1, 6)
121 AND e.ledger_code = vLedgerCode AND
122 f.ou_id = g.ou_id AND e.status_ledger = vStatusLedgerNotDone) THEN
123 RAISE EXCEPTION 'Admin Process Ledger for Inventory in year month % is already closed', SUBSTR(vDocDate, 1, 6);
124 END IF;
125
126 SELECT flg_invoice INTO vFlgInvoice
127 FROM sl_so_balance_invoice
128 WHERE ref_id = pReturnNoteId
129 AND ref_doc_type_id = vReturnNoteDocTypeId;
130
131 IF FOUND AND vFlgInvoice IN ('Y','I') THEN
132 RAISE EXCEPTION 'Return Note document already been used by Return Sales Invoice';
133 END IF;
134
135 -- 1. ubah status_doc D
136 UPDATE in_inventory SET status_doc = vStatusDraft, workflow_status = vWorkflowStatusDraft, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
137 WHERE inventory_id = pReturnNoteId
138 AND status_doc = vStatusRelease;
139
140 /*
141 * 2. create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock
142 */
143 INSERT INTO tt_in_product_balance_summary_stock
144 (session_id, tenant_id, inventory_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty)
145 SELECT pSessionId, A.tenant_id, A.inventory_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status,
146 B.base_uom_id, SUM(B.qty_realization)
147 FROM in_inventory A, in_inventory_item B
148 WHERE A.inventory_id = B.inventory_id
149 AND A.inventory_id = pReturnNoteId
150 GROUP BY A.tenant_id, A.inventory_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;
151
152 /*
153 * 3. update product_balance_stock
154 */
155 UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty - A.qty, update_datetime = pDatetime, update_user_id = pUserId,
156 version = version + 1
157 FROM tt_in_product_balance_summary_stock A
158 WHERE A.session_id = pSessionId AND
159 A.inventory_id = pReturnNoteId AND
160 in_product_balance_stock.tenant_id = A.tenant_id AND
161 in_product_balance_stock.warehouse_id = A.warehouse_id AND
162 in_product_balance_stock.product_id = A.product_id AND
163 in_product_balance_stock.product_balance_id = A.product_balance_id AND
164 in_product_balance_stock.product_status = A.product_status;
165
166 -- QTY tidak boleh < 0
167 IF NOT EXISTS (
168 SELECT (1)
169 FROM in_return_note_for_finance A
170 WHERE A.inventory_id = pReturnNoteId
171 AND A.flg_for_finance = vFlagYes
172 ) THEN
173 IF EXISTS (SELECT 1
174 FROM in_product_balance_stock A
175 INNER JOIN tt_in_product_balance_summary_stock B ON B.tenant_id = A.tenant_id AND
176 B.warehouse_id = A.warehouse_id AND B.product_id = A.product_id AND
177 B.product_balance_id = A.product_balance_id AND B.product_status = A.product_status
178 WHERE B.session_id = pSessionId
179 AND B.inventory_id = pReturnNoteId
180 AND A.qty < 0)
181 THEN
182 RAISE EXCEPTION 'Qty product in stock product less than zero';
183 END IF;
184 END IF;
185
186 /*
187 * 4. delete data from in_product_balance_stock
188 */
189 -- DELETE FROM in_product_balance_stock
190 -- WHERE tenant_id = pTenantId AND
191 -- warehouse_id = vWarehouseId AND
192 -- product_id = vProductId AND
193 -- product_balance_id = vProductBalanceId AND
194 -- product_status = vProductStatus;
195
196 /*
197 * 5. delete data in_log_product_balance_stock
198 */
199 /*DELETE FROM in_log_product_balance_stock
200 WHERE tenant_id = pTenantId AND
201 ou_id = vOuId AND
202 doc_type_id = vReturnNoteDocTypeId AND
203 ref_id = pReturnNoteId AND
204 doc_no = vDocNo AND
205 doc_date = vDocDate AND
206 warehouse_id = vWarehouseId AND
207 product_id = vProductId AND
208 product_balance_id = vProductBalanceId AND
209 product_status = vProductStatus;*/
210 WITH grouped_in_inventory_item AS (
211 SELECT A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
212 B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) AS qty
213 FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
214 WHERE A.inventory_id = pReturnNoteId AND
215 A.inventory_id = B.inventory_id AND
216 A.warehouse_to_id = C.warehouse_id
217 GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
218 B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id
219 )
220 DELETE
221 FROM in_log_product_balance_stock Z
222 WHERE EXISTS (
223 SELECT (1)
224 FROM grouped_in_inventory_item A
225 WHERE Z.tenant_id = A.tenant_id
226 AND Z.ou_id = A.ou_id
227 AND Z.doc_type_id = A.doc_type_id
228 AND Z.ref_id = A.inventory_id
229 AND Z.doc_no = A.doc_no
230 AND Z.doc_date = A.doc_date
231 AND Z.partner_id = A.partner_id
232 AND Z.product_id = A.product_id
233 AND Z.product_balance_id = A.product_balance_id
234 AND Z.warehouse_id = A.warehouse_to_id
235 AND Z.product_status = A.product_status
236 AND Z.base_uom_id = A.base_uom_id
237 AND Z.qty = A.qty
238 );
239
240 /*
241 * 6.insert data temporer tt_in_so_balance_item
242 */
243 IF vReturnNotePartnerId = vPartnerIdMagento AND vDocDate >= vPkpStartDate THEN
244 INSERT INTO tt_in_so_balance_item
245 (session_id, tenant_id, ou_id, doc_type_id,
246 doc_no, doc_date, inventory_id, partner_id,
247 inventory_item_id, so_id, do_id, do_item_id,
248 qty_return, base_uom_id, remark,
249 so_item_id, curr_code, price,
250 flg_tax_amount, qty_so, qty_int_so,
251 so_uom_id, tax_id, tax_percentage)
252 SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
253 A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
254 B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
255 SUM(B.qty_realization), B.base_uom_id, A.remark,
256 D.so_item_id, D.curr_code, D.gross_sell_price,
257 vFlagYes, D.qty_so, D.qty_int,
258 D.so_uom_id, vTaxId, vTaxPercentage
259 FROM in_inventory A, in_inventory_item B, in_balance_do_item C, sl_so_item D, sl_so E
260 WHERE A.inventory_id = pReturnNoteId AND
261 A.inventory_id = B.inventory_id AND
262 B.ref_item_id = C.do_item_id AND
263 C.so_item_id = D.so_item_id AND
264 D.so_id = E.so_id
265 GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
266 A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
267 B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
268 B.base_uom_id, D.so_item_id, D.curr_code, D.nett_sell_price, D.qty_so, D.qty_int, D.so_uom_id, D.tax_id;
269 ELSE
270 -- default code insert ke tt_in_so_balance_item
271 INSERT INTO tt_in_so_balance_item
272 (session_id, tenant_id, ou_id, doc_type_id,
273 doc_no, doc_date, inventory_id, partner_id,
274 inventory_item_id, so_id, do_id, do_item_id,
275 qty_return, base_uom_id, remark,
276 so_item_id, curr_code, price,
277 flg_tax_amount, qty_so, qty_int_so,
278 so_uom_id, tax_id, tax_percentage)
279 SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
280 A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
281 B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
282 SUM(B.qty_realization), B.base_uom_id, A.remark,
283 D.so_item_id, D.curr_code, D.gross_sell_price,
284 D.flg_tax_amount, D.qty_so, D.qty_int,
285 D.so_uom_id, D.tax_id, D.tax_percentage
286 FROM in_inventory A, in_inventory_item B, in_balance_do_item C, sl_so_item D, sl_so E
287 WHERE A.inventory_id = pReturnNoteId AND
288 A.inventory_id = B.inventory_id AND
289 B.ref_item_id = C.do_item_id AND
290 C.so_item_id = D.so_item_id AND
291 D.so_id = E.so_id
292 GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
293 A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
294 B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
295 B.base_uom_id, D.so_item_id, D.curr_code, D.nett_sell_price, D.qty_so, D.qty_int, D.so_uom_id, D.tax_id ;
296 END IF;
297
298 /*
299 * 7. update qty return di data sl_so_balance_item
300 */
301 UPDATE sl_so_balance_item SET qty_return = sl_so_balance_item.qty_return - ((A.qty_return * A.qty_int_so) / A.qty_so),
302 qty_return_int = sl_so_balance_item.qty_return_int - A.qty_return, update_datetime = pDatetime, update_user_id = pUserId
303 FROM tt_in_so_balance_item A
304 WHERE A.session_id = pSessionId AND
305 sl_so_balance_item.so_item_id = A.so_item_id;
306
307 /*
308 * 8. hapus data sl_log_so_balance_item
309 */
310 -- DELETE FROM sl_log_so_balance_item
311 -- where tenant_id = pTenantId AND
312 -- ou_id = vOuId AND
313 -- ref_doc_type_id = vReturnNoteDocTypeId AND
314 -- ref_id = pReturnNoteId;
315
316 DELETE FROM sl_log_so_balance_item B
317 WHERE B.ref_doc_type_id = vReturnNoteDocTypeId AND
318 EXISTS (SELECT 1 FROM tt_in_so_balance_item A
319 WHERE A.inventory_id = pReturnNoteId AND
320 A.ou_id = vOuId AND
321 A.doc_type_id = vReturnNoteDocTypeId AND
322 A.doc_no = vDocNo AND
323 A.doc_date = vDocDate AND
324 B.ref_id = A.inventory_id AND
325 B.ref_item_id = A.inventory_item_id AND
326 B.so_id = A.so_id AND
327 B.so_item_id = A.so_item_id AND
328 B.ref_doc_type_id=A.doc_type_id);
329
330 /*
331 * 9. hapus data sl_so_balance_invoice
332 */
333 DELETE FROM sl_so_balance_invoice B
334 WHERE B.ref_doc_type_id = vReturnNoteDocTypeId AND
335 EXISTS (SELECT 1 FROM tt_in_so_balance_item A
336 WHERE A.inventory_id = pReturnNoteId AND
337 A.ou_id = vOuId AND
338 A.doc_type_id = vReturnNoteDocTypeId AND
339 A.doc_no = vDocNo AND
340 A.doc_date = vDocDate AND
341 B.tenant_id = A.tenant_id AND
342 B.ref_id = A.inventory_id AND
343 B.ou_id = A.ou_id AND
344 B.partner_id = A.partner_id AND
345 B.ref_doc_type_id = A.doc_type_id AND
346 B.ref_id = A.inventory_id AND
347 B.ref_item_id = A.do_item_id);
348 /*
349 * 10. hapus data sl_so_balance_invoice_tax
350 */
351 DELETE FROM sl_so_balance_invoice_tax B
352 WHERE B.ref_doc_type_id = vReturnNoteDocTypeId AND
353 EXISTS (SELECT 1 FROM tt_in_so_balance_item A, m_tax D
354 WHERE A.session_id = pSessionId AND
355 A.tax_id = D.tax_id AND
356 A.inventory_id = pReturnNoteId AND
357 A.ou_id = vOuId AND
358 A.doc_type_id = vReturnNoteDocTypeId AND
359 A.doc_no = vDocNo AND
360 A.doc_date = vDocDate AND
361 B.tenant_id=A.tenant_id AND
362 B.ou_id=A.ou_id AND
363 B.partner_id=A.partner_id AND
364 B.ref_doc_type_id=A.doc_type_id AND
365 B.ref_id=A.inventory_id AND
366 B.ref_item_id=A.do_item_id AND
367 B.tax_id=A.tax_id);
368
369 UPDATE in_balance_do_item SET status_item = vStatusInProgress
370 FROM tt_in_so_balance_item A
371 WHERE A.session_id = pSessionId AND
372 in_balance_do_item.do_item_id = A.do_item_id;
373
374 IF EXISTS(
375 SELECT 1 FROM pu_po_balance_item_consignment_sold_manual A
376 INNER JOIN in_inventory_item B ON B.ref_item_id = A.do_item_id
377 INNER JOIN in_inventory C ON B.inventory_id = C.inventory_id AND B.ref_id = A.do_id
378 WHERE A.status_item IN (vStatusInProgress)
379 AND C.inventory_id = pReturnNoteId
380 AND B.ref_doc_type_id = vDocTypeIdDo
381 )
382 THEN
383 RAISE EXCEPTION 'Return Note document have been used In another transaction';
384 END IF;
385
386 UPDATE pu_po_balance_item_consignment_sold_manual Z
387 SET status_item = vStatusInProgress
388 FROM in_inventory_item Y
389 JOIN in_inventory X ON Y.inventory_id = X.inventory_id
390 WHERE X.inventory_id = pReturnNoteId
391 AND X.doc_type_id = vReturnNoteDocTypeId
392 AND Y.ref_doc_type_id = vDocTypeIdDo
393 AND Y.ref_id = Z.do_id
394 AND Y.ref_item_id = Z.do_item_id;
395
396 UPDATE pu_po_balance_item_consignment_sold_manual Z
397 SET qty_return = Z.qty_return - Y.qty_realization
398 FROM in_inventory_item Y
399 JOIN in_inventory X ON Y.inventory_id = X.inventory_id
400 WHERE X.inventory_id = pReturnNoteId
401 AND X.doc_type_id = vReturnNoteDocTypeId
402 AND Y.ref_doc_type_id = vDocTypeIdDo
403 AND Y.ref_id = Z.do_id
404 AND Y.ref_item_id = Z.do_item_id;
405
406 --* 11. PERFORM cancel gl_cancel_admin_journal_trx
407 PERFORM gl_cancel_admin_journal_trx(pTenantId, (vOuStructure).ou_bu_id, vOuId, (vDocJournal).journal_type, f_get_year_month_date(vDocDate), 'DAILY', pDatetime, pUserId);
408
409 -- pre 9 Find journal trx id
410 SELECT journal_trx_id INTO vJournalTrxId
411 FROM gl_journal_trx
412 WHERE tenant_id = pTenantId
413 AND journal_type = (vDocJournal).journal_type
414 AND doc_type_id = vReturnNoteDocTypeId
415 AND doc_id = pReturnNoteId
416 AND doc_no = vDocNo
417 AND doc_date = vDocDate
418 AND ou_bu_id = (vOuStructure).ou_bu_id
419 AND ou_branch_id = (vOuStructure).ou_branch_id
420 AND ou_sub_bu_id = (vOuStructure).ou_sub_bu_id;
421
422 -- * 12. DELETE gl_journal_trx
423 DELETE FROM gl_journal_trx
424 WHERE journal_trx_id = vJournalTrxId;
425
426 -- * 13. DELETE gl_journal_trx_item
427 DELETE FROM gl_journal_trx_item
428 WHERE journal_trx_id = vJournalTrxId;
429
430 -- * 14. DELETE gl_journal_trx_mapping
431 DELETE FROM gl_journal_trx_mapping
432 WHERE journal_trx_id = vJournalTrxId;
433
434 -- * 15. PERFORM f_reset_approval_to_draft
435 PERFORM f_reset_approval_to_draft(pTenantId, pSessionId, vScheme, pReturnNoteId, vDocNo, pDatetime, pRemarkApproval);
436
437 -- * 16. UPDATE t_process_message
438 UPDATE t_process_message
439 SET process_no = pPrevProcessNo,
440 update_datetime = pDatetime,
441 update_user_id = pUserId,
442 version = version + 1
443 WHERE tenant_id = pTenantId
444 AND process_name = vFunctionSubmit
445 AND process_no = pReturnNoteId || '_' || vDocNo;
446
447 --Jika Return Note adalah for Finance, hapus data Claim Note
448 IF EXISTS (
449 SELECT (1)
450 FROM in_return_note_for_finance A
451 WHERE A.inventory_id = pReturnNoteId
452 AND A.flg_for_finance = vFlagYes
453 ) THEN
454
455 SELECT A.inventory_id, A.doc_no, A.doc_date, A.ou_from_id
456 INTO vClaimNoteId, vClaimNoteDocNo, vClaimNoteDocDate, vClaimNoteOuId
457 FROM in_inventory A
458 WHERE EXISTS(
459 SELECT 1 FROM in_inventory B
460 INNER JOIN in_return_note_for_finance C ON B.inventory_id = C.inventory_id
461 INNER JOIN pu_po D ON C.po_id = D.po_id
462 WHERE B.inventory_id = pReturnNoteId
463 AND A.doc_date = B.doc_date
464 AND A.ou_from_id = D.ou_id
465 AND A.ou_to_id = D.ou_id
466 AND A.partner_id = D.partner_id
467 AND A.ext_doc_no = B.doc_no
468 AND A.ext_doc_date = B.doc_date
469 AND A.ref_doc_type_id = D.doc_type_id
470 AND A.ref_id = D.po_id
471 ) AND A.doc_type_id = vClaimNoteDocTypeId;
472
473 UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty + A.qty, update_datetime = pDatetime, update_user_id = pUserId,
474 version = version + 1
475 FROM tt_in_product_balance_summary_stock A
476 WHERE A.session_id = pSessionId AND
477 A.inventory_id = pReturnNoteId AND
478 in_product_balance_stock.tenant_id = A.tenant_id AND
479 in_product_balance_stock.warehouse_id = A.warehouse_id AND
480 in_product_balance_stock.product_id = A.product_id AND
481 in_product_balance_stock.product_balance_id = A.product_balance_id AND
482 in_product_balance_stock.product_status = A.product_status;
483
484 IF EXISTS (SELECT 1
485 FROM in_product_balance_stock A
486 INNER JOIN tt_in_product_balance_summary_stock B ON B.tenant_id = A.tenant_id AND
487 B.warehouse_id = A.warehouse_id AND B.product_id = A.product_id AND
488 B.product_balance_id = A.product_balance_id AND B.product_status = A.product_status
489 WHERE B.session_id = pSessionId
490 AND B.inventory_id = pReturnNoteId
491 AND A.qty < 0)
492 THEN
493 RAISE EXCEPTION 'Qty product in stock product less than zero';
494 END IF;
495
496 WITH grouped_in_inventory_item AS (
497 SELECT A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
498 B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) * -1 AS qty
499 FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
500 WHERE A.inventory_id = vClaimNoteId AND
501 A.inventory_id = B.inventory_id AND
502 C.warehouse_id = A.warehouse_from_id
503 GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
504 B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id
505 )
506 DELETE
507 FROM in_log_product_balance_stock Z
508 WHERE EXISTS (
509 SELECT (1)
510 FROM grouped_in_inventory_item A
511 WHERE Z.tenant_id = A.tenant_id
512 AND Z.ou_id = A.ou_id
513 AND Z.doc_type_id = A.doc_type_id
514 AND Z.ref_id = A.inventory_id
515 AND Z.doc_no = A.doc_no
516 AND Z.doc_date = A.doc_date
517 AND Z.partner_id = A.partner_id
518 AND Z.product_id = A.product_id
519 AND Z.product_balance_id = A.product_balance_id
520 AND Z.warehouse_id = A.warehouse_from_id
521 AND Z.product_status = A.product_status
522 AND Z.base_uom_id = A.base_uom_id
523 AND Z.qty = A.qty
524 );
525
526 INSERT INTO tt_in_po_balance_item
527 (session_id, tenant_id, ou_id, doc_type_id,
528 doc_no, doc_date, inventory_id, partner_id,
529 inventory_item_id, po_id, receive_goods_id, receive_goods_item_id,
530 qty_return, base_uom_id, remark,
531 po_item_id, curr_code, price,
532 flg_tax_amount, qty_po, qty_int_po,
533 po_uom_id, tax_id, tax_percentage)
534 SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
535 A.doc_no, A.doc_date, A.inventory_id, A.partner_id,
536 B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
537 SUM(B.qty_realization), B.base_uom_id, A.remark,
538 D.po_item_id, D.curr_code, D.gross_price_po - D.discount_amount,
539 D.flg_tax_amount, D.qty_po, D.qty_int,
540 D.po_uom_id, D.tax_id, D.tax_percentage
541 FROM in_inventory A, in_inventory_item B, in_balance_receive_goods_item C, pu_po_item D
542 WHERE A.inventory_id = vClaimNoteId AND
543 A.inventory_id = B.inventory_id AND
544 B.ref_item_id = C.receive_goods_item_id AND
545 C.po_item_id = D.po_item_id
546 GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
547 A.doc_no, A.doc_date, A.inventory_id, A.partner_id,
548 B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
549 B.base_uom_id, D.po_item_id, D.curr_code, D.nett_price_po, D.qty_po, D.qty_int, D.po_uom_id, D.tax_id;
550
551 UPDATE pu_po_balance_item SET qty_return = pu_po_balance_item.qty_return - ((A.qty_return * A.qty_po) / A.qty_int_po),
552 qty_int_return = pu_po_balance_item.qty_int_return - A.qty_return, update_datetime = pDatetime, update_user_id = pUserId
553 FROM tt_in_po_balance_item A
554 WHERE A.session_id = pSessionId AND
555 pu_po_balance_item.po_item_id = A.po_item_id;
556
557 DELETE FROM pu_log_po_balance_item A
558 WHERE EXISTS(
559 SELECT (1) FROM tt_in_po_balance_item B
560 WHERE A.tenant_id = B.tenant_id
561 AND A.po_id = B.po_id
562 AND A.po_item_id = B.po_item_id
563 AND A.ref_doc_type_id = B.doc_type_id
564 AND A.ref_id = B.inventory_id
565 AND A.ref_item_id = B.inventory_item_id
566 AND A.qty_trx = ((B.qty_return * B.qty_po) / B.qty_int_po )
567 AND A.trx_uom_id = B.po_uom_id
568 AND A.qty_int = B.qty_return
569 AND A.base_uom_id = B.base_uom_id
570 AND A.remark = B.remark
571 );
572
573 DELETE FROM pu_po_balance_invoice A
574 WHERE EXISTS(
575 SELECT (1) FROM tt_in_po_balance_item B
576 WHERE A.tenant_id = B.tenant_id
577 AND A.ou_id = B.ou_id
578 AND A.po_id = B.po_id
579 AND A.ref_doc_type_id = B.doc_type_id
580 AND A.ref_id = B.inventory_id
581 AND A.ref_item_id = B.receive_goods_item_id
582 );
583
584 DELETE FROM pu_po_balance_invoice_tax A
585 WHERE EXISTS(
586 SELECT (1) FROM tt_in_po_balance_item B, m_tax C
587 WHERE B.tax_id = C.tax_id AND
588 A.tenant_id = B.tenant_id AND
589 A.ou_id = B.ou_id AND
590 A.po_id = B.po_id AND
591 A.ref_doc_type_id = B.doc_type_id AND
592 A.ref_id = B.inventory_id AND
593 A.ref_item_id = B.receive_goods_item_id AND
594 A.tax_id = C.tax_id
595 );
596
597 UPDATE in_balance_receive_goods_item SET status_item = vStatusInProgress
598 FROM tt_in_po_balance_item A
599 WHERE A.session_id = pSessionId AND
600 in_balance_receive_goods_item.receive_goods_item_id = A.receive_goods_item_id;
601
602 -- PERFORM cancel gl_cancel_admin_journal_trx
603 PERFORM gl_cancel_admin_journal_trx(pTenantId, (f_get_ou_bu_structure(vClaimNoteOuId)).ou_bu_id, vClaimNoteOuId, (f_get_document_journal(vClaimNoteDocTypeId)).journal_type, f_get_year_month_date(vClaimNoteDocDate), 'DAILY', pDatetime, pUserId);
604
605 -- Find journal trx id
606 SELECT journal_trx_id INTO vClaimNoteJournalTrxId
607 FROM gl_journal_trx
608 WHERE tenant_id = pTenantId
609 AND journal_type = (f_get_document_journal(vClaimNoteDocTypeId)).journal_type
610 AND doc_type_id = vClaimNoteDocTypeId
611 AND doc_id = vClaimNoteId
612 AND doc_no = vClaimNoteDocNo
613 AND doc_date = vClaimNoteDocDate
614 AND ou_bu_id = (f_get_ou_bu_structure(vClaimNoteOuId)).ou_bu_id
615 AND ou_branch_id = (f_get_ou_bu_structure(vClaimNoteOuId)).ou_branch_id
616 AND ou_sub_bu_id = (f_get_ou_bu_structure(vClaimNoteOuId)).ou_sub_bu_id;
617
618 DELETE FROM gl_journal_trx WHERE journal_trx_id = vClaimNoteJournalTrxId;
619
620 DELETE FROM gl_journal_trx_item WHERE journal_trx_id = vClaimNoteJournalTrxId;
621
622 DELETE FROM gl_journal_trx_mapping WHERE journal_trx_id = vClaimNoteJournalTrxId;
623
624 WITH grouped_tt_in_po_balance_item AS(
625 SELECT A.tenant_id, A.ou_id, A.po_id, A.doc_type_id,
626 A.inventory_id, A.receive_goods_item_id
627 FROM tt_in_po_balance_item A
628 WHERE A.session_id = pSessionId
629 GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.po_id,
630 A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date,
631 A.receive_goods_item_id, A.po_uom_id, A.curr_code, A.price
632 )
633 DELETE FROM pu_po_balance_invoice_ext_invoice_doc_type A
634 WHERE EXISTS (
635 SELECT (1) FROM grouped_tt_in_po_balance_item B
636 WHERE A.tenant_id = B.tenant_id
637 AND A.ou_id = B.ou_id
638 AND A.po_id = B.po_id
639 AND A.ref_doc_type_id = B.doc_type_id
640 AND A.ref_id = B.inventory_id
641 AND A.ref_item_id = B.receive_goods_item_id
642 );
643
644 WITH grouped_tt_in_po_balance_item AS(
645 SELECT A.tenant_id, A.ou_id, A.po_id, A.doc_type_id,
646 A.inventory_id, A.receive_goods_item_id
647 FROM tt_in_po_balance_item A
648 INNER JOIN m_tax B ON B.tax_id = A.tax_id
649 WHERE A.session_id = pSessionId
650 GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.po_id,
651 A.doc_type_id, A.inventory_id, A.receive_goods_item_id,
652 A.tax_id, B.flg_amount, A.tax_percentage, A.curr_code
653 )
654 DELETE FROM pu_po_balance_invoice_tax_ext A
655 WHERE EXISTS (
656 SELECT (1) FROM grouped_tt_in_po_balance_item B
657 WHERE A.tenant_id = B.tenant_id
658 AND A.ou_id = B.ou_id
659 AND A.po_id = B.po_id
660 AND A.ref_doc_type_id = B.doc_type_id
661 AND A.ref_id = B.inventory_id
662 AND A.ref_item_id = B.receive_goods_item_id
663 );
664
665 DELETE FROM awe_currdoc_status WHERE req_id = vClaimNoteId||'_'||vClaimNoteDocNo AND tenant_id = pTenantId
666 AND scheme = vClaimNoteScheme AND doc_id = vClaimNoteId;
667
668 DELETE FROM awe_historydoc WHERE req_id = vClaimNoteId||'_'||vClaimNoteDocNo AND tenant_id = pTenantId
669 AND scheme = vClaimNoteScheme AND doc_id = vClaimNoteId;
670
671 DELETE FROM in_inventory_logistic WHERE inventory_id = vClaimNoteId;
672
673 DELETE FROM in_inventory_item WHERE inventory_id = vClaimNoteId;
674
675 DELETE FROM in_inventory WHERE inventory_id = vClaimNoteId;
676
677 DELETE FROM tt_in_po_balance_item WHERE session_id = pSessionId;
678
679 END IF;
680
681 DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;
682 DELETE FROM tt_in_so_balance_item WHERE session_id = pSessionId;
683 ELSE
684 RAISE EXCEPTION 'Document with id % is not found or document is on approval progress', pReturnNoteId;
685 END IF;
686END;
687$BODY$
688 LANGUAGE plpgsql VOLATILE
689 COST 100;
690 /