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