· 8 years ago · Dec 20, 2017, 10:30 AM
1/**
2 * Modified by Adrian, Dec 19, 2017
3 * Menambahkan generate Claim Note untuk Return Note for Finance
4 */
5
6CREATE OR REPLACE FUNCTION in_submit_return_note(bigint, character varying, character varying)
7 RETURNS void AS
8$BODY$
9DECLARE
10 pTenantId ALIAS FOR $1;
11 pSessionId ALIAS FOR $2;
12 pProcessNo ALIAS FOR $3;
13
14 vProcessId bigint;
15 vReturnNoteId bigint;
16 vUserId bigint;
17 vDatetime character varying(14);
18 vFlagInvoice character varying(1);
19 vEmptyId bigint;
20 vStatusRelease character varying(1);
21 vEmptyValue character varying(1);
22 vStatusFinal character varying(1);
23 vStatusDraft character varying(1);
24 vFlagYes character varying(1);
25 vFlagNo character varying(1);
26 vEmptyString character varying(1);
27 vJournalType character varying(20);
28 vSignDebit character varying(1);
29 vSignCredit character varying(1);
30 vTypeRate character varying(3);
31 vProductCOA character varying(10);
32 vSystemCOA character varying(10);
33 vParentOuId bigint;
34 vJournalTrxId bigint;
35 vNol numeric;
36 vOuId bigint;
37 vOuWarehouseId bigint;
38 vDocTypeId bigint;
39
40 vDocJournal DOC_JOURNAL%ROWTYPE;
41 vOuStructure OU_BU_STRUCTURE%ROWTYPE;
42 vOuStructureJournalItem OU_BU_STRUCTURE%ROWTYPE;
43 result RECORD;
44
45 vReturnNoteDocTypeId bigint;
46 vRoundingModeNonTax character varying(5);
47
48 vTaxPercentage numeric;
49 vTaxId bigint;
50 vPartnerIdMagento bigint;
51 vReturnNotePartnerId bigint;
52 vReturnNoteDocDate character varying(8);
53 vPkpStartDate character varying(8);
54
55 vSalesOrderDocTypeId bigint;
56 vClaimNoteId bigint;
57 vClaimNoteDocTypeId bigint;
58 vClaimNoteDocNo character varying(30);
59 vClaimNoteNumId bigint;
60 vEmpty character varying(1);
61 vEmptyIdString character varying;
62 vRemarkPrefix text;
63 vWorkflowApproved character varying;
64 vReceiveGoodsDocTypeId bigint;
65
66 vRoleIdForGenerateDoc bigint := -99;
67 vUserIdForGenerateDoc bigint := -99;
68 vClaimNoteFlowId bigint := -99;
69 vClaimNoteScheme character varying := 'EA02';
70 vFlgUserRole character varying := 'R';
71 vParamUserIdForGenerateDoc character varying := 'user.id.for.return.note.for.finance';
72 vParamRoleIdForGenerateDoc character varying := 'role.id.for.return.note.for.finance';
73
74BEGIN
75
76 vFlagInvoice := 'N';
77 vEmptyId := -99;
78 vEmptyValue := ' ';
79 vStatusRelease := 'R';
80 vStatusFinal := 'F';
81 vStatusDraft := 'D';
82 vFlagYes := 'Y';
83 vFlagNo := 'N';
84 vEmptyString := ' ';
85 vSignDebit := 'D';
86 vSignCredit := 'C';
87 vTypeRate := 'COM';
88 vProductCOA := 'PRODUCT';
89 vSystemCOA := 'SYSTEM';
90 vNol := 0;
91
92 vDocTypeId := 311;
93 vReturnNoteDocTypeId := 502;
94 vSalesOrderDocTypeId := 301;
95 vClaimNoteDocTypeId := 511;
96 vEmpty := '';
97 vRemarkPrefix := '[AUTO GENERATED] ';
98 vWorkflowApproved := 'APPROVED';
99 vReceiveGoodsDocTypeId := 111;
100 vEmptyIdString = '-99';
101 SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
102
103 -- Menampung nilai tax_id dan tax_percentage dari table m_tax ke variable vTaxId dan vTaxPercentage
104 SELECT tax_id, percentage INTO vTaxId, vTaxPercentage FROM m_tax WHERE tenant_id = pTenantId and tax_code = 'PPn';
105
106 -- Menampung nilai partner_id magento dari sysconfig ke variable vPartnerIdMagento
107 SELECT f_get_value_system_config_by_param_code(pTenantId, 'partner.id.e.commerce')::bigint INTO vPartnerIdMagento;
108
109 -- Menampung nilai pkp start date dari sysconfig ke variable vPkpStartDate
110 SELECT f_get_value_system_config_by_param_code(pTenantId, 'pkp.start.date') INTO vPkpStartDate;
111
112
113 SELECT A.process_message_id INTO vProcessId
114 FROM t_process_message A
115 WHERE A.tenant_id = pTenantId AND
116 A.process_name = 'in_submit_return_note' AND
117 A.process_no = pProcessNo;
118
119 SELECT CAST(A.process_parameter_value AS bigint) INTO vReturnNoteId
120 FROM t_process_parameter A
121 WHERE A.process_message_id = vProcessId AND
122 A.process_parameter_key = 'returnNoteId';
123
124 SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
125 FROM t_process_parameter A
126 WHERE A.process_message_id = vProcessId AND
127 A.process_parameter_key = 'userId';
128
129 SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
130 FROM t_process_parameter A
131 WHERE A.process_message_id = vProcessId AND
132 A.process_parameter_key = 'datetime';
133
134 -- Menampung nilai doc_date return note dan partner_id return note ke dalam variable vReturnNoteDocDate dan vReturnNotePartnerId
135 SELECT A.doc_date, B.partner_bill_to_id INTO vReturnNoteDocDate, vReturnNotePartnerId
136 FROM in_inventory A
137 INNER JOIN sl_so B ON a.ref_doc_type_id = 301 AND A.ref_id = B.so_id
138 WHERE A.inventory_id = vReturnNoteId;
139
140 DELETE FROM tt_in_so_balance_item WHERE session_id = pSessionId;
141
142 /*
143 * 1.update status doc in_inventory
144 * 2.update data ke in_product_balance_stock / insert data ke in_product-balance_stock
145 * 3.insert data ke in_log_product_balance_stock
146 * 4.update data sl_so_balance_item
147 * 5.insert data sl_log_so_balance_item
148 * 6.insert data sl_so_balance_invoice
149 * 7.insert data sl_so_balance_invoice_tax
150 * 8.update data in_balance_do_item
151 * 9.add trx jurnal
152 */
153
154 SELECT f_get_ou_bu_structure(A.ou_from_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
155 FROM in_inventory A
156 WHERE A.inventory_id = vReturnNoteId INTO result;
157
158 vOuStructure := result.ou;
159 vDocJournal := result.doc;
160
161 UPDATE in_inventory SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
162 WHERE inventory_id = vReturnNoteId;
163
164 /*
165 * create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock
166 */
167 INSERT INTO tt_in_product_balance_summary_stock
168 (session_id, tenant_id, inventory_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty)
169 SELECT pSessionId, A.tenant_id, A.inventory_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status,
170 B.base_uom_id, SUM(B.qty_realization)
171 FROM in_inventory A, in_inventory_item B
172 WHERE A.inventory_id = B.inventory_id
173 AND A.inventory_id = vReturnNoteId
174 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;
175
176 /*
177 * update product_balance_stock
178 */
179 UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty + A.qty, update_datetime = vDatetime, update_user_id = vUserId,
180 version = version + 1
181 FROM tt_in_product_balance_summary_stock A
182 WHERE A.session_id = pSessionId AND
183 A.inventory_id = vReturnNoteId AND
184 in_product_balance_stock.tenant_id = A.tenant_id AND
185 in_product_balance_stock.warehouse_id = A.warehouse_id AND
186 in_product_balance_stock.product_id = A.product_id AND
187 in_product_balance_stock.product_balance_id = A.product_balance_id AND
188 in_product_balance_stock.product_status = A.product_status;
189
190 /*
191 * insert data in_product_balance_stock
192 */
193 INSERT INTO in_product_balance_stock
194 (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
195 "version", create_datetime, create_user_id, update_datetime, update_user_id)
196 SELECT A.tenant_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
197 0, vDatetime, vUserId, vDatetime, vUserId
198 FROM in_inventory A, in_inventory_item B
199 WHERE A.inventory_id = B.inventory_id AND
200 A.inventory_id = vReturnNoteId AND
201 NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
202 WHERE C.tenant_id = A.tenant_id AND
203 C.warehouse_id = A.warehouse_from_id AND
204 C.product_id = B.product_id AND
205 C.product_balance_id = B.product_balance_id AND
206 C.product_status = B.product_status)
207 GROUP BY A.tenant_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;
208
209 /*
210 * insert data in_log_product_balance_stock
211 */
212 INSERT INTO in_log_product_balance_stock
213 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
214 product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
215 "version", create_datetime, create_user_id, update_datetime, update_user_id)
216 SELECT A.tenant_id, A.ou_to_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
217 B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
218 0, vDatetime, vUserId, vDatetime, vUserId
219 FROM in_inventory A, in_inventory_item B
220 WHERE A.inventory_id = vReturnNoteId AND
221 A.inventory_id = B.inventory_id
222 GROUP BY A.tenant_id, A.ou_to_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
223 B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;
224
225
226 /*
227 Added Julius Tanuwijaya, 16 Nov 2016
228 1. buat sysConfig -> pkp.start.date : diisi dengan 20161001
229 2. Ambil nilai syconfig tampung ke variabel vPkpStartDate
230 3. Ambil doc_date dari return note yang disubmit tampung ke variabel vReturnNoteDate
231 4. Ambil dari m_tax untuk tenant id bersangkutan dgn tax code PPn, tampung vUsedTaxId, dan vUsedTaxPercentage
232 5. Ambil partner_id dari dokumen return note tampung ke vReturnNotePartnerId
233 6. Ambil partner_id eCommerce (MAGENTO) dari sysconfig tampung ke vMagentoPartnerId
234 7. IF vReturnNotePartnerId = vMagentoPartnerId AND vReturnNoteDate >= vPkpStartDate THEN
235 --custom code
236 -- INSERT ke tt_in_so_balance_item
237 flg_tax_amount selalu Yes (Y)
238 tax_id pakai vUsedTaxId
239 tax_percentage pakai vUsedTaxPercentage
240 ELSE
241
242 -- default code insert ke tt_in_so_balance_item
243 END IF
244 */
245 /*
246 * insert data temporer tt_in_so_balance_item
247 */
248 IF vReturnNotePartnerId = vPartnerIdMagento AND vReturnNoteDocDate >= vPkpStartDate THEN
249 -- INSERT ke tt_in_so_balance_item
250 -- flg_tax_amount selalu Yes (Y)
251 -- tax_id pakai vUsedTaxId
252 -- tax_percentage pakai vUsedTaxPercentage
253 INSERT INTO tt_in_so_balance_item
254 (session_id, tenant_id, ou_id, doc_type_id,
255 doc_no, doc_date, inventory_id, partner_id,
256 inventory_item_id, so_id, do_id, do_item_id,
257 qty_return, base_uom_id, remark,
258 so_item_id, curr_code, price,
259 flg_tax_amount, qty_so, qty_int_so,
260 so_uom_id, tax_id, tax_percentage)
261 SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
262 A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
263 B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
264 SUM(B.qty_realization), B.base_uom_id, A.remark,
265 D.so_item_id, D.curr_code, D.gross_sell_price,
266 vFlagYes, D.qty_so, D.qty_int,
267 D.so_uom_id, vTaxId, vTaxPercentage
268 FROM in_inventory A, in_inventory_item B, in_balance_do_item C, sl_so_item D, sl_so E
269 WHERE A.inventory_id = vReturnNoteId AND
270 A.inventory_id = B.inventory_id AND
271 B.ref_item_id = C.do_item_id AND
272 C.so_item_id = D.so_item_id AND
273 D.so_id = E.so_id
274 GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
275 A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
276 B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
277 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 ;
278
279 ELSE
280 -- default code insert ke tt_in_so_balance_item
281 INSERT INTO tt_in_so_balance_item
282 (session_id, tenant_id, ou_id, doc_type_id,
283 doc_no, doc_date, inventory_id, partner_id,
284 inventory_item_id, so_id, do_id, do_item_id,
285 qty_return, base_uom_id, remark,
286 so_item_id, curr_code, price,
287 flg_tax_amount, qty_so, qty_int_so,
288 so_uom_id, tax_id, tax_percentage)
289 SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
290 A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
291 B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
292 SUM(B.qty_realization), B.base_uom_id, A.remark,
293 D.so_item_id, D.curr_code, D.gross_sell_price,
294 D.flg_tax_amount, D.qty_so, D.qty_int,
295 D.so_uom_id, D.tax_id, D.tax_percentage
296 FROM in_inventory A, in_inventory_item B, in_balance_do_item C, sl_so_item D, sl_so E
297 WHERE A.inventory_id = vReturnNoteId AND
298 A.inventory_id = B.inventory_id AND
299 B.ref_item_id = C.do_item_id AND
300 C.so_item_id = D.so_item_id AND
301 D.so_id = E.so_id
302 GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
303 A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
304 B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
305 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 ;
306 END IF;
307
308
309 /*
310 * update qty return di data sl_so_balance_item
311 */
312 UPDATE sl_so_balance_item SET qty_return = sl_so_balance_item.qty_return + ((A.qty_return * A.qty_so) / A.qty_int_so), qty_return_int = sl_so_balance_item.qty_return_int + A.qty_return, update_datetime = vDatetime, update_user_id = vUserId
313 FROM tt_in_so_balance_item A
314 WHERE A.session_id = pSessionId AND
315 sl_so_balance_item.so_item_id = A.so_item_id;
316
317 /*
318 * buat data sl_log_so_balance_item
319 */
320 INSERT INTO sl_log_so_balance_item
321 (tenant_id, so_id, so_item_id, ref_doc_type_id, ref_id, ref_item_id,
322 qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
323 "version", create_datetime, create_user_id, update_datetime, update_user_id)
324 SELECT A.tenant_id, A.so_id, A.so_item_id, A.doc_type_id, A.inventory_id, A.inventory_item_id,
325 ((A.qty_return * A.qty_so) / A.qty_int_so ), A.so_uom_id, A.qty_return, A.base_uom_id, A.remark,
326 0, vDatetime, vUserId, vDatetime, vUserId
327 FROM tt_in_so_balance_item A
328 WHERE A.session_id = pSessionId;
329
330
331 /*
332 * buat data sl_so_balance_invoice
333 */
334 INSERT INTO sl_so_balance_invoice
335 (tenant_id, ou_id, partner_id, so_id,
336 ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_dlv_so, so_uom_id,
337 curr_code, price_so, item_amount, flg_invoice, invoice_id,
338 regular_disc_amount, promo_disc_amount, adj_regular_disc_amount, adj_promo_disc_amount,
339 "version", create_datetime, create_user_id, update_datetime, update_user_id)
340 SELECT A.tenant_id, A.ou_id, A.partner_id, A.so_id,
341 A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.do_item_id, SUM((A.qty_return * A.qty_so) / A.qty_int_so), A.so_uom_id,
342 A.curr_code, f_get_price_before_tax_and_disc(A.price, ((B.regular_disc_amount / B.qty_dlv_so) + (B.promo_disc_amount / B.qty_dlv_so)), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReturnNoteDocTypeId, A.curr_code), vRoundingModeNonTax),
343 SUM(f_get_amount_before_tax_and_disc((A.qty_return * A.qty_so * A.price) / A.qty_int_so, (B.regular_disc_amount + B.promo_disc_amount) * (A.qty_return * A.qty_so) / (A.qty_int_so / B.qty_dlv_so), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReturnNoteDocTypeId, A.curr_code), vRoundingModeNonTax)),
344 vFlagInvoice, vEmptyId,
345 B.regular_disc_amount * SUM((A.qty_return * A.qty_so) / A.qty_int_so) / B.qty_dlv_so, 0, 0, 0,
346 0, vDatetime, vUserId, vDatetime, vUserId
347 FROM tt_in_so_balance_item A
348 INNER JOIN sl_so_balance_invoice B ON B.ref_id = A.do_id AND B.ref_item_id = A.do_item_id AND B.do_receipt_item_id = vEmptyId
349 WHERE A.session_id = pSessionId
350 GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.so_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.do_item_id, A.so_uom_id, A.curr_code, A.price, B.regular_disc_amount, B.qty_dlv_so, B.promo_disc_amount, A.flg_tax_amount, A.tax_percentage;
351
352 /*
353 * buat data sl_so_balance_invoice_tax
354 */
355 INSERT INTO sl_so_balance_invoice_tax
356 (tenant_id, ou_id, partner_id, so_id,
357 ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
358 tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
359 "version", create_datetime, create_user_id, update_datetime, update_user_id)
360 SELECT A.tenant_id, A.ou_id, A.partner_id, A.so_id,
361 A.doc_type_id, A.inventory_id, A.do_item_id, A.tax_id, D.flg_amount,
362 A.tax_percentage, A.curr_code,
363 SUM(f_get_amount_before_tax_and_disc((A.qty_return * A.qty_so * A.price) / A.qty_int_so, (A.qty_return * A.qty_so * B.discount_amount) / A.qty_int_so, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReturnNoteDocTypeId, A.curr_code), vRoundingModeNonTax)),
364 f_tax_rounding(A.tenant_id, SUM(f_get_amount_before_tax((A.qty_return * A.qty_so * (A.price - B.discount_amount)) / A.qty_int_so, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReturnNoteDocTypeId, A.curr_code), vRoundingModeNonTax)), A.tax_percentage),
365 vFlagInvoice, vEmptyId,
366 0, vDatetime, vUserId, vDatetime, vUserId
367 FROM tt_in_so_balance_item A, m_tax D, sl_so_item B
368 WHERE A.session_id = pSessionId AND
369 A.tax_id = D.tax_id AND
370 A.so_item_id = B.so_item_id
371 GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.so_id, A.doc_type_id, A.inventory_id, A.do_item_id, A.tax_id,
372 D.flg_amount, A.tax_percentage, A.curr_code;
373
374
375 UPDATE in_balance_do_item SET status_item = vStatusFinal
376 FROM tt_in_so_balance_item A
377 WHERE A.session_id = pSessionId AND
378 in_balance_do_item.do_item_id = A.do_item_id AND
379 in_balance_do_item.qty_dlv -in_balance_do_item.qty_return <= 0;
380
381 UPDATE in_balance_do_item SET status_item = vStatusRelease
382 FROM tt_in_so_balance_item A
383 WHERE A.session_id = pSessionId AND
384 in_balance_do_item.do_item_id = A.do_item_id AND
385 in_balance_do_item.qty_dlv -in_balance_do_item.qty_return > 0;
386
387 /*
388 * @author TKP, 9 Jun 2016
389 * Cek jika OU pada warehouse sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id =-99
390 * jika OU pada warehouse tidak sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id didapat pada f_get_ou_bu_structure;
391 */
392 SELECT A.ou_from_id, B.ou_id INTO vOuId, vOuWarehouseId
393 FROM in_inventory A
394 INNER JOIN m_warehouse_ou B ON A.warehouse_from_id = B.warehouse_id
395 WHERE A.inventory_id = vReturnNoteId;
396
397 IF (vOuId <> vOuWarehouseId) THEN
398 SELECT f_get_ou_bu_structure(vOuWarehouseId) as ou_structure INTO result;
399 vOuStructureJournalItem := result.ou_structure;
400 ELSE
401 vOuStructureJournalItem := ROW(-99, -99, -99);
402 END IF;
403
404 /*
405 * journal return note
406 * Debit Inventory = dari nilai COGS
407 * Credit HPP
408 */
409 /*
410 * membuat data transaksi jurnal :
411 * 1. buat admin
412 * 2. buat temlate jurnal
413 */
414 PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_from_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', vDatetime, vUserId)
415 FROM in_inventory A
416 WHERE A.inventory_id = vReturnNoteId;
417
418 SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
419
420 INSERT INTO gl_journal_trx
421 (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
422 ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
423 ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
424 "version", create_datetime, create_user_id, update_datetime, update_user_id)
425 SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date,
426 (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, A.warehouse_from_id, A.ext_doc_no, A.ext_doc_date,
427 A.ref_doc_type_id, A.ref_id, A.doc_date, f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), A.remark, vStatusDraft, 'DRAFT',
428 0, vDatetime, vUserId, vDatetime, vUserId
429 FROM in_inventory A
430 WHERE A.inventory_id = vReturnNoteId;
431
432 INSERT INTO tt_journal_trx_item
433 (session_id, tenant_id, journal_trx_id, line_no,
434 ref_doc_type_id, ref_id,
435 partner_id, product_id, cashbank_id, ou_rc_id,
436 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
437 coa_id, curr_code, qty, uom_id,
438 amount, journal_date, type_rate,
439 numerator_rate, denominator_rate, journal_desc, remark)
440 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
441 A.doc_type_id, B.inventory_item_id,
442 A.partner_id, B.product_id, vEmptyId, vEmptyId,
443 vEmptyId, vSignDebit, vProductCOA, vEmptyId,
444 f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
445 0 , A.doc_date, vTypeRate,
446 1, 1, 'PRODUCT_STOCK', B.remark
447 FROM in_inventory A, in_inventory_item B
448 WHERE A.inventory_id = vReturnNoteId AND
449 A.inventory_id = B.inventory_id;
450
451
452/* NK, 1 Feb 2014, journal HPP tidak perlu break down sampai ke product
453 INSERT INTO tt_journal_trx_item
454 (session_id, tenant_id, journal_trx_id, line_no,
455 ref_doc_type_id, ref_id,
456 partner_id, product_id, cashbank_id, ou_rc_id,
457 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
458 coa_id, curr_code, qty, uom_id,
459 amount, journal_date, type_rate,
460 numerator_rate, denominator_rate, journal_desc, remark)
461 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
462 A.doc_type_id, B.inventory_item_id,
463 A.partner_id, B.product_id, vEmptyId, vEmptyId,
464 vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
465 f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
466 0 , A.doc_date, vTypeRate,
467 1, 1, 'HPP', B.remark
468 FROM in_inventory A, in_inventory_item B
469 WHERE A.inventory_id = vReturnNoteId AND
470 A.inventory_id = B.inventory_id;
471*/
472/*
473 INSERT INTO tt_journal_trx_item
474 (session_id, tenant_id, journal_trx_id, line_no,
475 ref_doc_type_id, ref_id, ou_id, sub_ou_id,
476 partner_id, product_id, cashbank_id, ou_rc_id,
477 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
478 coa_id, curr_code, qty, uom_id,
479 amount, journal_date, type_rate,
480 numerator_rate, denominator_rate, journal_desc, remark)
481 SELECT pSessionId, A.tenant_id, B.journal_trx_id, 1,
482 A.doc_type_id, A.inventory_item_id, B.ou_id, B.sub_ou_id,
483 vEmptyId, C.product_id, vEmptyId, vEmptyId,
484 vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
485 f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), A.curr_code, A.qty_return, A.base_uom_id,
486 f_get_amount_before_tax((A.qty_return * A.qty_so * A.price) / A.qty_int_so, A.flg_tax_amount, A.tax_percentage,0),
487 A.doc_date, vTypeRate,
488 1, 1, 'HPP', A.remark
489 FROM tt_in_so_balance_item A, gl_journal_trx B, in_inventory_item C
490 WHERE A.session_id = pSessionId AND
491 B.journal_trx_id = vJournalTrxId AND
492 A.inventory_item_id = C.inventory_item_id;
493
494 INSERT INTO gl_journal_trx_mapping
495 (tenant_id, journal_trx_id, line_no,
496 ref_doc_type_id, ref_id,
497 partner_id, product_id, cashbank_id, ou_rc_id,
498 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
499 coa_id, curr_code, qty, uom_id,
500 amount, journal_date, type_rate,
501 numerator_rate, denominator_rate, journal_desc, remark,
502 "version", create_datetime, create_user_id, update_datetime, update_user_id)
503 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
504 A.ref_doc_type_id, A.ref_id,
505 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
506 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
507 A.coa_id, A.curr_code, A.qty, A.uom_id,
508 A.amount, A.journal_date, A.type_rate,
509 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
510 0, vDatetime, vUserId, vDatetime, vUserId
511 FROM tt_journal_trx_item A
512 WHERE A.session_id = pSessionId AND
513 A.journal_desc = 'HPP';
514
515*/
516 INSERT INTO gl_journal_trx_item
517 (tenant_id, journal_trx_id, line_no,
518 ref_doc_type_id, ref_id,
519 partner_id, product_id, cashbank_id, ou_rc_id,
520 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
521 coa_id, curr_code, qty, uom_id,
522 amount, journal_date, type_rate,
523 numerator_rate, denominator_rate, journal_desc, remark,
524 "version", create_datetime, create_user_id, update_datetime, update_user_id,
525 ou_branch_id, ou_sub_bu_id)
526 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
527 A.ref_doc_type_id, A.ref_id,
528 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
529 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
530 A.coa_id, A.curr_code, A.qty, A.uom_id,
531 A.amount, A.journal_date, A.type_rate,
532 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
533 0, vDatetime, vUserId, vDatetime, vUserId,
534 (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
535 FROM tt_journal_trx_item A
536 WHERE A.session_id = pSessionId AND
537 A.journal_desc = 'PRODUCT_STOCK';
538
539 INSERT INTO gl_journal_trx_mapping
540 (tenant_id, journal_trx_id, line_no,
541 ref_doc_type_id, ref_id,
542 partner_id, product_id, cashbank_id, ou_rc_id,
543 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
544 coa_id, curr_code, qty, uom_id,
545 amount, journal_date, type_rate,
546 numerator_rate, denominator_rate, journal_desc, remark,
547 "version", create_datetime, create_user_id, update_datetime, update_user_id)
548 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
549 vEmptyId, vEmptyId,
550 vEmptyId, vEmptyId, vEmptyId, vEmptyId,
551 vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
552 f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
553 0, A.journal_date, A.type_rate,
554 1, 1, 'COGS', vEmptyValue,
555 0, vDatetime, vUserId, vDatetime, vUserId
556 FROM tt_journal_trx_item A
557 WHERE A.session_id = pSessionId
558 GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate;
559
560 /*
561 * 1.TODO: update ke pu_po_balance_balance_sold_manual ubah status item I setelah menjalankan function submit return note
562 * pastikan pu_po_balance_balance_sold_manual do item tersebut status item berubah menjadi R
563 * 2.TODO update qty return di table pu_po_balance_balance_sold_manual menjadi qty_return + qty qty_realization di table in_inventory_item
564 * berdasarkan:
565 * 1. in_inventory_item.ref id = pu_po_balance_balance_sold_manual.do id
566 * 2. in_inventory_item.ref doc type id = pu_po_balance_balance_sold_manual.doc_type_id (DO) 311
567 * 3. in_inventory_item.ref item id = pu_po_balance_balance_sold_manual.do_item_id
568 * 4. in_inventory_item.doc_type_id = doc_type_id_return note (RN) 502
569 * 5. in_inventory.in_inventory_id = inventory_id
570 */
571
572 UPDATE pu_po_balance_item_consignment_sold_manual Z
573 SET status_item = vStatusRelease
574 FROM in_inventory_item Y
575 JOIN in_inventory X ON Y.inventory_id = X.inventory_id
576 WHERE X.inventory_id = vReturnNoteId
577 AND X.doc_type_id = vReturnNoteDocTypeId
578 AND Y.ref_doc_type_id = vDocTypeId
579 AND Y.ref_id = Z.do_id
580 AND Y.ref_item_id = Z.do_item_id;
581
582 UPDATE pu_po_balance_item_consignment_sold_manual Z
583 SET qty_return = Z.qty_return + Y.qty_realization
584 FROM in_inventory_item Y
585 JOIN in_inventory X ON Y.inventory_id = X.inventory_id
586 WHERE X.inventory_id = vReturnNoteId
587 AND X.doc_type_id = vReturnNoteDocTypeId
588 AND Y.ref_doc_type_id = vDocTypeId
589 AND Y.ref_id = Z.do_id
590 AND Y.ref_item_id = Z.do_item_id;
591
592 --Jika Return Note adalah for Finance, buat data Claim Note
593 IF EXISTS (
594 SELECT (1)
595 FROM in_return_note_for_finance A
596 WHERE A.inventory_id = vReturnNoteId
597 AND A.flg_for_finance = vFlagYes
598 ) THEN
599
600 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
601 DELETE FROM tt_in_po_balance_item WHERE session_id = pSessionId;
602
603 SELECT NEXTVAL('in_inventory_seq') INTO vClaimNoteId;
604
605 SELECT CAST(A.process_parameter_value AS character varying(30)) INTO vClaimNoteDocNo FROM t_process_parameter A
606 WHERE A.process_message_id = vProcessId AND A.process_parameter_key = 'CLAIM_NOTE_NO';
607
608 SELECT CAST(A.process_parameter_value AS bigint) INTO vClaimNoteNumId FROM t_process_parameter A
609 WHERE A.process_message_id = vProcessId AND A.process_parameter_key = 'CLAIM_NOTE_NUM_ID';
610
611
612 INSERT INTO in_inventory(
613 inventory_id, tenant_id, doc_type_id, doc_no, doc_date, ou_from_id,
614 ou_to_id, partner_id, ext_doc_no, ext_doc_date, ref_doc_type_id,
615 ref_id, remark, warehouse_from_id, warehouse_to_id, transfer_date,
616 receive_date, activity_gl_id, ou_rc_id, no_vehicle, flg_delivery,
617 delivery_code, status_doc, workflow_status, version, create_datetime,
618 create_user_id, update_datetime, update_user_id, segment_id,
619 group_product_id)
620 SELECT vClaimNoteId, A.tenant_id, vClaimNoteDocTypeId, vClaimNoteDocNo, A.doc_date, C.ou_id,
621 C.ou_id, C.partner_id, vEmpty, vEmpty, C.doc_type_id,
622 C.po_id, vRemarkPrefix || A.remark, A.warehouse_from_id, A.warehouse_to_id, A.transfer_date,
623 A.receive_date, vEmptyId, vEmptyId, A.no_vehicle, A.flg_delivery,
624 A.delivery_code, vStatusRelease, vWorkflowApproved, 0, vDatetime,
625 vUserId, vDatetime, vUserId, vEmptyId,
626 vEmptyId
627 FROM in_inventory A
628 INNER JOIN in_return_note_for_finance B
629 ON A.inventory_id = B.inventory_id
630 INNER JOIN pu_po C
631 ON B.po_id = C.po_id
632 WHERE A.inventory_id = vReturnNoteId;
633
634 INSERT INTO in_inventory_item(
635 tenant_id, inventory_id, line_no, product_id,
636 product_balance_id, product_status, serial_number, product_expired_date,
637 product_year_made, lot_number, ref_doc_type_id, ref_id, ref_item_id,
638 curr_code, amount, base_uom_id, qty_request, qty_realization,
639 remark, version, create_datetime, create_user_id, update_datetime,
640 update_user_id)
641 SELECT A.tenant_id, vClaimNoteId, D.line_no, E.product_id,
642 E.product_balance_id, F.product_status, G.serial_number, G.product_expired_date,
643 G.product_year_made, G.lot_number, vReceiveGoodsDocTypeId, C.receive_goods_id, C.receive_goods_item_id,
644 vEmptyIdString, 0, C.base_uom_id,
645 CASE WHEN H.qty < E.qty THEN
646 H.qty
647 ELSE
648 E.qty
649 END,
650 A.qty_return,
651 D.remark, 0, vDatetime, vUserId, vDatetime,
652 vUserId
653 FROM tt_in_so_balance_item A
654 INNER JOIN pu_po_item B
655 ON A.so_item_id = B.ref_id
656 INNER JOIN in_balance_receive_goods_item C
657 ON B.po_item_id = C.po_item_id
658 INNER JOIN in_inventory_item D
659 ON A.inventory_item_id = D.inventory_item_id
660 INNER JOIN in_log_product_balance_stock E
661 ON C.tenant_id = E.tenant_id
662 AND E.ref_id = C.receive_goods_id
663 AND C.doc_date = E.doc_date
664 AND B.product_id = E.product_id
665 AND E.qty > 0
666 INNER JOIN tt_in_product_balance_summary_stock F
667 ON F.tenant_id = E.tenant_id
668 --AND F.warehouse_id = E.warehouse_id
669 AND F.product_id = E.product_id
670 AND F.product_balance_id = E.product_balance_id
671 INNER JOIN in_product_balance G
672 ON F.product_balance_id = G.product_balance_id
673 INNER JOIN in_product_balance_stock H
674 ON H.tenant_id = F.tenant_id
675 AND H.warehouse_id = F.warehouse_id
676 AND H.product_id = F.product_id
677 AND H.product_balance_id = F.product_balance_id
678 AND H.product_status = F.product_status
679 WHERE A.session_id = pSessionId
680 AND B.ref_doc_type_id = vSalesOrderDocTypeId
681 AND E.doc_type_id = vReceiveGoodsDocTypeId
682 AND F.session_id = pSessionId
683 AND F.inventory_id = vReturnNoteId;
684
685
686 --Sudah update di Add/Edit/Remove Return Note for Finance
687 /*UPDATE in_balance_receive_goods_item Z
688 SET qty_int_return = Z.qty_int_return + A.qty_return,
689 qty_return = (Z.qty_int_return + A.qty_return) * (Z.qty_int_rcv / Z.qty_rcv),
690 status_item = vStatusRelease,
691 update_user_id = vUserId,
692 update_datetime = vDatetime
693 FROM tt_in_so_balance_item A
694 INNER JOIN pu_po_item B
695 ON A.so_item_id = B.ref_id
696 WHERE A.session_id = pSessionId
697 AND Z.po_item_id = B.po_item_id
698 AND B.ref_doc_type_id = vSalesOrderDocTypeId;*/
699
700 /*
701 * update product_balance_stock
702 */
703 UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty - A.qty, update_datetime = vDatetime, update_user_id = vUserId,
704 version = version + 1
705 FROM tt_in_product_balance_summary_stock A
706 WHERE A.session_id = pSessionId AND
707 A.inventory_id = vReturnNoteId AND
708 in_product_balance_stock.tenant_id = A.tenant_id AND
709 in_product_balance_stock.warehouse_id = A.warehouse_id AND
710 in_product_balance_stock.product_id = A.product_id AND
711 in_product_balance_stock.product_balance_id = A.product_balance_id AND
712 in_product_balance_stock.product_status = A.product_status;
713
714 INSERT INTO in_inventory_logistic(
715 tenant_id, inventory_id, partner_ship_address_id,
716 partner_ship_cp_id, cp_name, cp_phone1, cp_phone2, remark, due_date,
717 version, create_datetime, create_user_id, update_datetime, update_user_id)
718 SELECT A.tenant_id, vClaimNoteId, B.partner_address_id,
719 C.partner_cp_id, C.cp_name, C.phone1, C.phone2, A.remark, A.transfer_date,
720 0, vDatetime, vUserId, vDatetime, vUserId
721 FROM in_inventory A
722 INNER JOIN m_partner_address B
723 ON A.tenant_id = B.tenant_id AND A.partner_id = B.partner_id
724 INNER JOIN m_partner_cp C
725 ON A.tenant_id = C.tenant_id AND A.partner_id = C.partner_id
726 WHERE A.inventory_id = vClaimNoteId
727 AND B.active = vFlagYes
728 AND B.flg_default = vFlagYes
729 AND C.active = vFlagYes
730 AND C.flg_responsibility = vFlagYes;
731
732 SELECT f_get_ou_bu_structure(A.ou_from_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
733 FROM in_inventory A
734 WHERE A.inventory_id = vClaimNoteId INTO result;
735
736 vOuStructure := result.ou;
737 vDocJournal := result.doc;
738
739 INSERT INTO in_log_product_balance_stock
740 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
741 product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
742 "version", create_datetime, create_user_id, update_datetime, update_user_id)
743 SELECT A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
744 B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) * -1,
745 0, vDatetime, vUserId, vDatetime, vUserId
746 FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
747 WHERE A.inventory_id = vClaimNoteId AND
748 A.inventory_id = B.inventory_id AND
749 C.warehouse_id = A.warehouse_from_id
750 GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
751 B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
752
753 INSERT INTO tt_in_po_balance_item
754 (session_id, tenant_id, ou_id, doc_type_id,
755 doc_no, doc_date, inventory_id, partner_id,
756 inventory_item_id, po_id, receive_goods_id, receive_goods_item_id,
757 qty_return, base_uom_id, remark,
758 po_item_id, curr_code, price,
759 flg_tax_amount, qty_po, qty_int_po,
760 po_uom_id, tax_id, tax_percentage)
761 SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
762 A.doc_no, A.doc_date, A.inventory_id, A.partner_id,
763 B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
764 SUM(B.qty_realization), B.base_uom_id, A.remark,
765 D.po_item_id, D.curr_code, D.gross_price_po - D.discount_amount,
766 D.flg_tax_amount, D.qty_po, D.qty_int,
767 D.po_uom_id, D.tax_id, D.tax_percentage
768 FROM in_inventory A, in_inventory_item B, in_balance_receive_goods_item C, pu_po_item D
769 WHERE A.inventory_id = vClaimNoteId AND
770 A.inventory_id = B.inventory_id AND
771 B.ref_item_id = C.receive_goods_item_id AND
772 C.po_item_id = D.po_item_id
773 GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
774 A.doc_no, A.doc_date, A.inventory_id, A.partner_id,
775 B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
776 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 ;
777
778 UPDATE pu_po_balance_item SET qty_return = pu_po_balance_item.qty_return + ((A.qty_return * A.qty_po) / A.qty_int_po), qty_int_return = pu_po_balance_item.qty_int_return + A.qty_return, update_datetime = vDatetime, update_user_id = vUserId
779 FROM tt_in_po_balance_item A
780 WHERE A.session_id = pSessionId AND
781 pu_po_balance_item.po_item_id = A.po_item_id;
782
783 INSERT INTO pu_log_po_balance_item
784 (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
785 qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
786 "version", create_datetime, create_user_id, update_datetime, update_user_id)
787 SELECT A.tenant_id, A.po_id, A.po_item_id, A.doc_type_id, A.inventory_id, A.inventory_item_id,
788 ((A.qty_return * A.qty_po) / A.qty_int_po ), A.po_uom_id, A.qty_return, A.base_uom_id, A.remark,
789 0, vDatetime, vUserId, vDatetime, vUserId
790 FROM tt_in_po_balance_item A
791 WHERE A.session_id = pSessionId;
792
793 INSERT INTO pu_po_balance_invoice
794 (tenant_id, ou_id, partner_id, po_id,
795 ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
796 curr_code, price_po, item_amount, flg_invoice, invoice_id,
797 "version", create_datetime, create_user_id, update_datetime, update_user_id)
798 SELECT A.tenant_id, A.ou_id, A.partner_id, A.po_id,
799 A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.receive_goods_item_id, SUM((A.qty_return * A.qty_po) / A.qty_int_po), A.po_uom_id,
800 A.curr_code, A.price,
801 SUM(f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vClaimNoteDocTypeId, A.curr_code), vRoundingModeNonTax)),
802 vFlagInvoice, vEmptyId,
803 0, vDatetime, vUserId, vDatetime, vUserId
804 FROM tt_in_po_balance_item A
805 WHERE A.session_id = pSessionId
806 GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.po_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.receive_goods_item_id, A.po_uom_id, A.curr_code, A.price;
807
808 INSERT INTO pu_po_balance_invoice_tax
809 (tenant_id, ou_id, partner_id, po_id,
810 ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
811 tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
812 "version", create_datetime, create_user_id, update_datetime, update_user_id)
813 SELECT A.tenant_id, A.ou_id, A.partner_id, A.po_id,
814 A.doc_type_id, A.inventory_id, A.receive_goods_item_id, A.tax_id, D.flg_amount,
815 A.tax_percentage, A.curr_code,
816 SUM(f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vClaimNoteDocTypeId, A.curr_code), vRoundingModeNonTax)),
817 f_tax_rounding(A.tenant_id, SUM(f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vClaimNoteDocTypeId, A.curr_code), vRoundingModeNonTax)), A.tax_percentage),
818 vFlagInvoice, vEmptyId,
819 0, vDatetime, vUserId, vDatetime, vUserId
820 FROM tt_in_po_balance_item A, m_tax D
821 WHERE A.session_id = pSessionId AND
822 A.tax_id = D.tax_id
823 GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.po_id, A.doc_type_id, A.inventory_id, A.receive_goods_item_id, A.tax_id,
824 D.flg_amount, A.tax_percentage, A.curr_code;
825
826 UPDATE in_balance_receive_goods_item SET status_item = vStatusFinal
827 FROM tt_in_po_balance_item A
828 WHERE A.session_id = pSessionId AND
829 in_balance_receive_goods_item.receive_goods_item_id = A.receive_goods_item_id AND
830 in_balance_receive_goods_item.qty_rcv -in_balance_receive_goods_item.qty_return <= 0;
831
832 UPDATE in_balance_receive_goods_item SET status_item = vStatusRelease
833 FROM tt_in_po_balance_item A
834 WHERE A.session_id = pSessionId AND
835 in_balance_receive_goods_item.receive_goods_item_id = A.receive_goods_item_id AND
836 in_balance_receive_goods_item.qty_rcv -in_balance_receive_goods_item.qty_return > 0;
837
838 SELECT A.ou_from_id, B.ou_id INTO vOuId, vOuWarehouseId
839 FROM in_inventory A
840 INNER JOIN m_warehouse_ou B ON A.warehouse_from_id = B.warehouse_id
841 WHERE A.inventory_id = vClaimNoteId;
842
843 IF (vOuId <> vOuWarehouseId) THEN
844 SELECT f_get_ou_bu_structure(vOuWarehouseId) as ou_structure INTO result;
845 vOuStructureJournalItem := result.ou_structure;
846 ELSE
847 vOuStructureJournalItem := ROW(-99, -99, -99);
848 END IF;
849
850 PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_from_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', vDatetime, vUserId)
851 FROM in_inventory A
852 WHERE A.inventory_id = vClaimNoteId;
853
854 SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
855
856 INSERT INTO gl_journal_trx
857 (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
858 ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
859 ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
860 "version", create_datetime, create_user_id, update_datetime, update_user_id)
861 SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date,
862 (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, A.warehouse_from_id, A.ext_doc_no, A.ext_doc_date,
863 A.ref_doc_type_id, A.ref_id, A.doc_date, f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), A.remark, vStatusDraft, 'DRAFT',
864 0, vDatetime, vUserId, vDatetime, vUserId
865 FROM in_inventory A
866 WHERE A.inventory_id = vClaimNoteId;
867
868 INSERT INTO tt_journal_trx_item
869 (session_id, tenant_id, journal_trx_id, line_no,
870 ref_doc_type_id, ref_id,
871 partner_id, product_id, cashbank_id, ou_rc_id,
872 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
873 coa_id, curr_code, qty, uom_id,
874 amount, journal_date, type_rate,
875 numerator_rate, denominator_rate, journal_desc, remark)
876 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
877 A.doc_type_id, B.inventory_item_id,
878 A.partner_id, B.product_id, vEmptyId, vEmptyId,
879 vEmptyId, vSignCredit, vProductCOA, vEmptyId,
880 f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
881 f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vClaimNoteDocTypeId, A.curr_code), vRoundingModeNonTax),
882 C.doc_date, vTypeRate,
883 1, 1, 'PRODUCT_STOCK', B.remark
884 FROM tt_in_po_balance_item A, in_inventory_item B, pu_receive_goods C
885 WHERE A.inventory_id = vClaimNoteId AND
886 A.inventory_item_id = B.inventory_item_id AND
887 A.receive_goods_id = C.receive_goods_id;
888
889 INSERT INTO tt_journal_trx_item
890 (session_id, tenant_id, journal_trx_id, line_no,
891 ref_doc_type_id, ref_id,
892 partner_id, product_id, cashbank_id, ou_rc_id,
893 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
894 coa_id, curr_code, qty, uom_id,
895 amount, journal_date, type_rate,
896 numerator_rate, denominator_rate, journal_desc, remark)
897 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
898 A.doc_type_id, A.inventory_item_id,
899 A.partner_id, C.product_id, vEmptyId, vEmptyId,
900 vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
901 f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, A.qty_return, A.base_uom_id,
902 f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vClaimNoteDocTypeId, A.curr_code), vRoundingModeNonTax),
903 A.doc_date, vTypeRate,
904 1, 1, 'ACCR_AP', A.remark
905 FROM tt_in_po_balance_item A, in_inventory_item C
906 WHERE A.session_id = pSessionId AND
907 A.inventory_item_id = C.inventory_item_id;
908
909 INSERT INTO gl_journal_trx_item
910 (tenant_id, journal_trx_id, line_no,
911 ref_doc_type_id, ref_id,
912 partner_id, product_id, cashbank_id, ou_rc_id,
913 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
914 coa_id, curr_code, qty, uom_id,
915 amount, journal_date, type_rate,
916 numerator_rate, denominator_rate, journal_desc, remark,
917 "version", create_datetime, create_user_id, update_datetime, update_user_id,
918 ou_branch_id, ou_sub_bu_id)
919 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
920 A.ref_doc_type_id, A.ref_id,
921 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
922 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
923 A.coa_id, A.curr_code, A.qty, A.uom_id,
924 A.amount, A.journal_date, A.type_rate,
925 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
926 0, vDatetime, vUserId, vDatetime, vUserId,
927 (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
928 FROM tt_journal_trx_item A
929 WHERE A.session_id = pSessionId AND
930 A.journal_desc = 'PRODUCT_STOCK';
931
932 INSERT INTO gl_journal_trx_mapping
933 (tenant_id, journal_trx_id, line_no,
934 ref_doc_type_id, ref_id,
935 partner_id, product_id, cashbank_id, ou_rc_id,
936 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
937 coa_id, curr_code, qty, uom_id,
938 amount, journal_date, type_rate,
939 numerator_rate, denominator_rate, journal_desc, remark,
940 "version", create_datetime, create_user_id, update_datetime, update_user_id)
941 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
942 A.ref_doc_type_id, A.ref_id,
943 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
944 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
945 A.coa_id, A.curr_code, A.qty, A.uom_id,
946 A.amount, A.journal_date, A.type_rate,
947 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
948 0, vDatetime, vUserId, vDatetime, vUserId
949 FROM tt_journal_trx_item A
950 WHERE A.session_id = pSessionId AND
951 A.journal_desc = 'ACCR_AP';
952
953 INSERT INTO pu_po_balance_invoice_ext_invoice_doc_type
954 (tenant_id, ou_id, po_id, ref_doc_type_id, ref_id,
955 ref_item_id, invoice_id, invoice_doc_type_id,
956 "version", create_datetime, create_user_id, update_datetime, update_user_id)
957 SELECT A.tenant_id, A.ou_id, A.po_id, A.doc_type_id, A.inventory_id,
958 A.receive_goods_item_id, vEmptyId, vEmptyId,
959 0, vDatetime, vUserId, vDatetime, vUserId
960 FROM tt_in_po_balance_item A
961 WHERE A.session_id = pSessionId
962 GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.po_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date,
963 A.receive_goods_item_id, A.po_uom_id, A.curr_code, A.price;
964
965 INSERT INTO pu_po_balance_invoice_tax_ext
966 (tenant_id, ou_id, po_id, ref_doc_type_id, ref_id,
967 ref_item_id, tax_id, invoice_id, invoice_doc_type_id,
968 "version", create_datetime, create_user_id, update_datetime, update_user_id)
969 SELECT A.tenant_id, A.ou_id, A.po_id, A.doc_type_id, A.inventory_id,
970 A.receive_goods_item_id, A.tax_id, vEmptyId, vEmptyId,
971 0, vDatetime, vUserId, vDatetime, vUserId
972 FROM tt_in_po_balance_item A, m_tax D
973 WHERE A.session_id = pSessionId AND
974 A.tax_id = D.tax_id
975 GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.po_id, A.doc_type_id, A.inventory_id, A.receive_goods_item_id, A.tax_id,
976 D.flg_amount, A.tax_percentage, A.curr_code;
977
978 /**
979 * GENERATE APPROVAL
980 */
981
982 -- Set user id from sysconfig
983 SELECT f_get_value_system_config_by_param_code(pTenantId, vParamUserIdForGenerateDoc)::bigint INTO vUserIdForGenerateDoc;
984
985 -- Set role id from sysconfig
986 SELECT f_get_value_system_config_by_param_code(pTenantId, vParamRoleIdForGenerateDoc)::bigint INTO vRoleIdForGenerateDoc;
987
988
989 -- Mendapatkan default approval flow ID
990 SELECT awe_flow_id INTO vClaimNoteFlowId
991 FROM awe_flow
992 WHERE scheme = vClaimNoteScheme AND
993 flg_validate = vFlagYes AND
994 active = vFlagYes;
995
996 -- Generate data awe_currdoc_status
997 INSERT INTO awe_currdoc_status(
998 req_id, tenant_id, scheme, doc_id, doc_no, doc_date, current_state,
999 remark, current_user_id, current_role_id, flg_user_role, label,
1000 data, flow_id, create_datetime, create_user_id, create_role_id,
1001 update_datetime, update_user_id, update_role_id, version)
1002 SELECT A.inventory_id||'_'||A.doc_no, A.tenant_id, vClaimNoteScheme, A.inventory_id, A.doc_no, A.doc_date, vWorkflowApproved,
1003 A.remark, vUserIdForGenerateDoc, vRoleIdForGenerateDoc, vFlgUserRole, 'CLAIM NOTE '||A.doc_no,
1004 '{}', vClaimNoteFlowId, vDatetime, vUserIdForGenerateDoc, vRoleIdForGenerateDoc,
1005 vDatetime, vUserIdForGenerateDoc, vRoleIdForGenerateDoc, 0
1006 FROM in_inventory A
1007 WHERE A.inventory_id = vClaimNoteId;
1008
1009 -- Generate data awe_historydoc
1010 INSERT INTO awe_historydoc(
1011 tenant_id, req_id, doc_id, scheme, user_id, role_id,
1012 activity, previous_state, next_state, remark, next_user_id, next_role_id,
1013 flg_user_role, activity_datetime, version)
1014 SELECT pTenantId, B.req_id, A.inventory_id, vClaimNoteScheme, vUserIdForGenerateDoc, vRoleIdForGenerateDoc,
1015 'AUTO GENERATE', '', '', A.remark, vEmptyId, vEmptyId,
1016 vFlgUserRole, pDateTime, 0
1017 FROM in_inventory A
1018 INNER JOIN awe_currdoc_status B ON B.doc_id = A.invoice_id AND B.doc_no = A.doc_no AND B.doc_date = A.doc_date
1019 WHERE A.tenant_id = pTenantId
1020 AND A.inventory_id = vClaimNoteId;
1021
1022 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
1023 DELETE FROM tt_in_po_balance_item WHERE session_id = pSessionId;
1024
1025 END IF;
1026
1027 DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;
1028 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
1029 DELETE FROM tt_in_so_balance_item WHERE session_id = pSessionId;
1030END;
1031$BODY$
1032LANGUAGE plpgsql VOLATILE
1033COST 100;
1034/