· 6 years ago · Jun 17, 2019, 08:36 AM
1po-- Function: in_cancel_submit_return_note(bigint, character varying, bigint, character varying, bigint, character varying, character varying)
2
3-- DROP FUNCTION in_cancel_submit_return_note(bigint, character varying, bigint, character varying, bigint, character varying, character varying);
4
5CREATE OR REPLACE FUNCTION in_cancel_submit_return_note(bigint, character varying, bigint, character varying, bigint, character varying, character varying)
6 RETURNS void AS
7$BODY$
8DECLARE
9 pTenantId ALIAS FOR $1;
10 pSessionId ALIAS FOR $2;
11 pUserId ALIAS FOR $3; -- for update user id in transaction
12 pDatetime ALIAS FOR $4;
13 pReturnNoteId ALIAS FOR $5; -- Inventory id
14 pRemarkApproval ALIAS FOR $6; -- Remark for appear in approval reset history
15 pPrevProcessNo ALIAS FOR $7; -- Substitute for renaming prevous process no
16
17 vFlagYes character varying := 'Y';
18 vFunctionSubmit character varying := 'in_submit_return_note';
19 vStatusDraft character varying := 'D';
20 vStatusInProgress character varying := 'I';
21 vStatusRelease character varying := 'R';
22 vWorkflowStatusDraft character varying := 'DRAFT';
23 vFlgInvoice character varying;
24 vDocNo character varying;
25 vDocDate character varying;
26 vProductStatus character varying;
27 vScheme character varying;
28 vPkpStartDate character varying(8);
29 vReturnNoteDocDate character varying(8);
30 vDocTypeIdDo bigint := 311;
31 vReturnNoteDocTypeId bigint := 502;
32 vJournalTrxId bigint;
33 vOuId bigint;
34 vWarehouseId bigint;
35 vProductId bigint;
36 vProductBalanceId bigint;
37 vPartnerIdMagento bigint;
38 vTaxId bigint;
39 vReturnNotePartnerId bigint;
40 vTaxPercentage numeric;
41 result RECORD;
42 vClaimNoteDocTypeId bigint := 511;
43 vClaimNoteScheme character varying := 'EA02';
44 vClaimNoteId bigint;
45 vClaimNoteDocNo character varying;
46 vClaimNoteDocDate character varying;
47 vClaimNoteOuId bigint;
48 vClaimNoteJournalTrxId bigint;
49
50 vLedgerCode character varying := 'INV';
51 vStatusLedgerNotDone character varying := '0';
52
53 vDocJournal DOC_JOURNAL%ROWTYPE;
54 vOuStructure OU_BU_STRUCTURE%ROWTYPE;
55 vNo character varying := 'N';
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 IF EXISTS (SELECT (1) FROM pu_po_balance_invoice A, tt_in_po_balance_item B
574 WHERE A.tenant_id = B.tenant_id
575 AND A.ou_id = B.ou_id
576 AND A.po_id = B.po_id
577 AND A.ref_doc_type_id = B.doc_type_id
578 AND A.ref_id = B.inventory_id
579 AND A.ref_item_id = B.receive_goods_item_id
580 AND A.flg_invoice <> vNo) THEN
581 RAISE EXCEPTION 'Document Claim Note % already followed up', vClaimNoteDocNo;
582 END IF;
583
584 DELETE FROM pu_po_balance_invoice A
585 WHERE EXISTS(
586 SELECT (1) FROM tt_in_po_balance_item B
587 WHERE A.tenant_id = B.tenant_id
588 AND A.ou_id = B.ou_id
589 AND A.po_id = B.po_id
590 AND A.ref_doc_type_id = B.doc_type_id
591 AND A.ref_id = B.inventory_id
592 AND A.ref_item_id = B.receive_goods_item_id
593 );
594
595 DELETE FROM pu_po_balance_invoice_tax A
596 WHERE EXISTS(
597 SELECT (1) FROM tt_in_po_balance_item B, m_tax C
598 WHERE B.tax_id = C.tax_id AND
599 A.tenant_id = B.tenant_id AND
600 A.ou_id = B.ou_id AND
601 A.po_id = B.po_id AND
602 A.ref_doc_type_id = B.doc_type_id AND
603 A.ref_id = B.inventory_id AND
604 A.ref_item_id = B.receive_goods_item_id AND
605 A.tax_id = C.tax_id
606 );
607
608 UPDATE in_balance_receive_goods_item SET status_item = vStatusInProgress
609 FROM tt_in_po_balance_item A
610 WHERE A.session_id = pSessionId AND
611 in_balance_receive_goods_item.receive_goods_item_id = A.receive_goods_item_id;
612
613 -- PERFORM cancel gl_cancel_admin_journal_trx
614 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);
615
616 -- Find journal trx id
617 SELECT journal_trx_id INTO vClaimNoteJournalTrxId
618 FROM gl_journal_trx
619 WHERE tenant_id = pTenantId
620 AND journal_type = (f_get_document_journal(vClaimNoteDocTypeId)).journal_type
621 AND doc_type_id = vClaimNoteDocTypeId
622 AND doc_id = vClaimNoteId
623 AND doc_no = vClaimNoteDocNo
624 AND doc_date = vClaimNoteDocDate
625 AND ou_bu_id = (f_get_ou_bu_structure(vClaimNoteOuId)).ou_bu_id
626 AND ou_branch_id = (f_get_ou_bu_structure(vClaimNoteOuId)).ou_branch_id
627 AND ou_sub_bu_id = (f_get_ou_bu_structure(vClaimNoteOuId)).ou_sub_bu_id;
628
629 DELETE FROM gl_journal_trx WHERE journal_trx_id = vClaimNoteJournalTrxId;
630
631 DELETE FROM gl_journal_trx_item WHERE journal_trx_id = vClaimNoteJournalTrxId;
632
633 DELETE FROM gl_journal_trx_mapping WHERE journal_trx_id = vClaimNoteJournalTrxId;
634
635 WITH grouped_tt_in_po_balance_item AS(
636 SELECT A.tenant_id, A.ou_id, A.po_id, A.doc_type_id,
637 A.inventory_id, A.receive_goods_item_id
638 FROM tt_in_po_balance_item A
639 WHERE A.session_id = pSessionId
640 GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.po_id,
641 A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date,
642 A.receive_goods_item_id, A.po_uom_id, A.curr_code, A.price
643 )
644 DELETE FROM pu_po_balance_invoice_ext_invoice_doc_type A
645 WHERE EXISTS (
646 SELECT (1) FROM grouped_tt_in_po_balance_item B
647 WHERE A.tenant_id = B.tenant_id
648 AND A.ou_id = B.ou_id
649 AND A.po_id = B.po_id
650 AND A.ref_doc_type_id = B.doc_type_id
651 AND A.ref_id = B.inventory_id
652 AND A.ref_item_id = B.receive_goods_item_id
653 );
654
655 WITH grouped_tt_in_po_balance_item AS(
656 SELECT A.tenant_id, A.ou_id, A.po_id, A.doc_type_id,
657 A.inventory_id, A.receive_goods_item_id
658 FROM tt_in_po_balance_item A
659 INNER JOIN m_tax B ON B.tax_id = A.tax_id
660 WHERE A.session_id = pSessionId
661 GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.po_id,
662 A.doc_type_id, A.inventory_id, A.receive_goods_item_id,
663 A.tax_id, B.flg_amount, A.tax_percentage, A.curr_code
664 )
665 DELETE FROM pu_po_balance_invoice_tax_ext A
666 WHERE EXISTS (
667 SELECT (1) FROM grouped_tt_in_po_balance_item B
668 WHERE A.tenant_id = B.tenant_id
669 AND A.ou_id = B.ou_id
670 AND A.po_id = B.po_id
671 AND A.ref_doc_type_id = B.doc_type_id
672 AND A.ref_id = B.inventory_id
673 AND A.ref_item_id = B.receive_goods_item_id
674 );
675
676 DELETE FROM awe_currdoc_status WHERE req_id = vClaimNoteId||'_'||vClaimNoteDocNo AND tenant_id = pTenantId
677 AND scheme = vClaimNoteScheme AND doc_id = vClaimNoteId;
678
679 DELETE FROM awe_historydoc WHERE req_id = vClaimNoteId||'_'||vClaimNoteDocNo AND tenant_id = pTenantId
680 AND scheme = vClaimNoteScheme AND doc_id = vClaimNoteId;
681
682 DELETE FROM in_inventory_logistic WHERE inventory_id = vClaimNoteId;
683
684 DELETE FROM in_inventory_item WHERE inventory_id = vClaimNoteId;
685
686 DELETE FROM in_inventory WHERE inventory_id = vClaimNoteId;
687
688 DELETE FROM tt_in_po_balance_item WHERE session_id = pSessionId;
689
690 END IF;
691
692 DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;
693 DELETE FROM tt_in_so_balance_item WHERE session_id = pSessionId;
694 ELSE
695 RAISE EXCEPTION 'Document with id % is not found or document is on approval progress', pReturnNoteId;
696 END IF;
697END;
698$BODY$
699 LANGUAGE plpgsql VOLATILE
700 COST 100;
701ALTER FUNCTION in_cancel_submit_return_note(bigint, character varying, bigint, character varying, bigint, character varying, character varying)
702 OWNER TO sts;
703GRANT EXECUTE ON FUNCTION in_cancel_submit_return_note(bigint, character varying, bigint, character varying, bigint, character varying, character varying) TO sts;
704GRANT EXECUTE ON FUNCTION in_cancel_submit_return_note(bigint, character varying, bigint, character varying, bigint, character varying, character varying) TO public;