· 4 years ago · Jul 07, 2021, 06:36 AM
1--pu_submit_goods_purchasing
2
3-- DROP FUNCTION pu_submit_goods_purchasing(bigint, character varying, character varying);
4
5CREATE OR REPLACE FUNCTION pu_submit_goods_purchasing(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 vPoId bigint;
15 vGoodsPurchasingId bigint;
16 vUserId bigint;
17 vDatetime character varying(14);
18 vStatusRelease character varying(1);
19 vStatusFinal character varying(1);
20 vEmptyValue character varying(1);
21 vFlagInvoice character varying(1);
22 vFlagInvoiceY character varying(1);
23 vStatusDraft character varying(1);
24 vSignJournalD character varying(1);
25 vSignJournalC character varying(1);
26 vFlagPayment character varying(1);
27 vFlgSourceCoa character varying(7);
28 vTypeRate character varying(3);
29 vEmptyId bigint;
30 vPurchaseOrderDocTypeId bigint;
31 vJournalTrxId bigint;
32 vJournalTrxIdNew bigint;
33 vReceiveGoodsDocTypeId bigint;
34 vInvoiceApBalanceId bigint;
35 vRoundingModeNonTax character varying(5);
36 vProductStatus character varying(50);
37 vGoodsPurchasingDocTypeId bigint;
38 vJournalReceiveGoodsId bigint;
39 vTypeDocumentJournal bigint;
40 vJointDppPpn character varying(1);
41
42 vDocJournalRg DOC_JOURNAL%ROWTYPE;
43 vDocJournalPi DOC_JOURNAL%ROWTYPE;
44 vOuStructure OU_BU_STRUCTURE%ROWTYPE;
45 result RECORD;
46 vDocTypeReceiveGoodsId bigint;
47 vDocTypePurchaseInvoiceId bigint;
48
49BEGIN
50
51 vFlagInvoice := 'N';
52 vFlagInvoiceY := 'Y';
53 vFlagPayment := 'N';
54 vStatusRelease := 'R';
55 vStatusFinal := 'F';
56 vStatusDraft := 'D';
57 vSignJournalD := 'D';
58 vSignJournalC := 'C';
59 vFlgSourceCoa := 'PRODUCT';
60 vTypeRate := 'COM';
61 vEmptyId := -99;
62 vEmptyValue := ' ';
63 vDocTypeReceiveGoodsId := 111;
64 vDocTypePurchaseInvoiceId := 131;
65
66 --vJournalReceiveGoodsId := 111;
67 vReceiveGoodsDocTypeId := 111;
68 vGoodsPurchasingDocTypeId := 160;
69 vTypeDocumentJournal := 131;
70
71 vJointDppPpn := f_get_value_system_config_by_param_code(pTenantId, 'joint.dpp.ppn.balance');
72
73 vProductStatus := 'GOOD';
74
75
76 /*
77 SELECT product_status_code INTO vProductStatus
78 FROM m_product_status
79 WHERE tenant_id = pTenantId AND flg_buy = 'Y';
80 */
81
82 SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
83
84 SELECT A.process_message_id INTO vProcessId
85 FROM t_process_message A
86 WHERE A.tenant_id = pTenantId AND
87 A.process_name = 'pu_submit_goods_purchasing' AND
88 A.process_no = pProcessNo;
89
90 SELECT CAST(A.process_parameter_value AS bigint) INTO vGoodsPurchasingId
91 FROM t_process_parameter A
92 WHERE A.process_message_id = vProcessId AND
93 A.process_parameter_key = 'goodsPurchasingId';
94
95 SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
96 FROM t_process_parameter A
97 WHERE A.process_message_id = vProcessId AND
98 A.process_parameter_key = 'userId';
99
100 SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
101 FROM t_process_parameter A
102 WHERE A.process_message_id = vProcessId AND
103 A.process_parameter_key = 'datetime';
104
105
106
107 DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;
108
109 UPDATE pu_po SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
110 WHERE po_id = vGoodsPurchasingId;
111
112
113 INSERT INTO pu_po_balance_invoice
114 (tenant_id, ou_id, partner_id, po_id,
115 ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
116 curr_code, price_po, item_amount, flg_invoice, invoice_id,
117 "version", create_datetime, create_user_id, update_datetime, update_user_id)
118 SELECT pTenantId, A.ou_id, A.partner_id, A.ref_id,
119 A.doc_type_id, A.po_id, A.doc_no, A.doc_date, B.po_item_id, B.qty_po, B.po_uom_id,
120 B.curr_code, B.nett_price_po,
121 f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax),
122 vFlagInvoiceY, vEmptyId,
123 0, vDatetime, vUserId, vDatetime, vUserId
124 FROM pu_po A
125 INNER JOIN pu_po_item B
126 ON A.po_id = B.po_id
127 WHERE A.po_id = vGoodsPurchasingId
128 AND A.po_id = B.po_id
129 AND A.tenant_id = pTenantId;
130 --FILTER LAINNYA
131
132
133 INSERT INTO pu_po_balance_invoice_tax
134 (tenant_id, ou_id, partner_id, po_id,
135 ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
136 tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
137 "version", create_datetime, create_user_id, update_datetime, update_user_id)
138 SELECT pTenantId, A.ou_id, A.partner_id, A.po_id,
139 A.doc_type_id, A.po_id, B.po_item_id, B.tax_id, C.flg_amount,
140 B.tax_percentage, B.curr_code,
141 f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax),
142 f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage),
143 vFlagInvoiceY, vEmptyId,
144 0, vDatetime, vUserId, vDatetime, vUserId
145 FROM pu_po A
146 INNER JOIN pu_po_item B
147 ON A.po_id = B.po_id
148 INNER JOIN m_tax C
149 ON B.tax_id = C.tax_id
150 WHERE A.po_id = vGoodsPurchasingId
151 AND A.po_id = B.po_id
152 AND B.tax_id = C.tax_id
153 AND A.tenant_id = pTenantId;
154 --FILTER LAINNYA
155
156
157 --Insert ke tt_pu_product_balance produck nya belum terdaftar di product balance--
158 INSERT INTO tt_pu_product_balance
159 (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
160 serial_number, lot_number, product_expired_date, product_year_made,
161 product_price_balance_id, product_buy_date, partner_id,
162 doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
163 po_id, po_no, po_date, po_item_id,
164 curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
165 SELECT pSessionId, B.warehouse_id, vEmptyId, pTenantId, B.ou_id, A.product_id,
166 A.serial_number, A.lot_number, A.expired_date, A.year_made,
167 vEmptyId, B.doc_date, B.partner_id,
168 B.doc_type_id, A.po_id, A.po_item_id, B.doc_no, B.doc_date,
169 A.po_id, B.doc_no, B.doc_date, A.po_item_id,
170 C.curr_code, C.gross_price_po, SUM(C.qty_po), C.po_uom_id, SUM(C.qty_int), C.base_uom_id, C.flg_stock
171 FROM pu_po_item_product A
172 INNER JOIN pu_po B
173 ON A.po_id = B.po_id
174 INNER JOIN pu_po_item C
175 ON C.po_item_id = A.po_item_id
176 WHERE A.po_id = vGoodsPurchasingId
177 AND A.tenant_id = pTenantId
178 AND NOT EXISTS
179 (
180 SELECT 1
181 FROM in_product_balance Z
182 WHERE Z.tenant_id = pTenantId
183 AND Z.product_id = A.product_id
184 AND Z.serial_number = A.serial_number
185 AND Z.lot_number = A.lot_number
186 )
187 GROUP BY B.warehouse_id, A.tenant_id, B.ou_id, A.product_id, B.doc_date, B.partner_id,
188 B.doc_type_id, A.po_id, A.po_item_id, B.doc_no, B.doc_date,
189 A.po_id, B.doc_no, B.doc_date, A.po_item_id,
190 C.curr_code, C.gross_price_po, C.po_uom_id, C.base_uom_id, C.flg_stock;
191
192
193
194 --Insert ke tt_pu_product_balance produck nya terdaftar di product balance--
195 INSERT INTO tt_pu_product_balance
196 (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
197 serial_number, lot_number, product_expired_date, product_year_made,
198 product_price_balance_id, product_buy_date, partner_id,
199 doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
200 po_id, po_no, po_date, po_item_id,
201 curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
202 SELECT pSessionId, B.warehouse_id, vEmptyId, A.tenant_id, B.ou_id, A.product_id,
203 A.serial_number, A.lot_number, A.expired_date, A.year_made,
204 vEmptyId, B.doc_date, B.partner_id,
205 B.doc_type_id, A.po_id, A.po_item_id, B.doc_no, B.doc_date,
206 A.po_id, B.doc_no, B.doc_date, A.po_item_id,
207 C.curr_code, C.gross_price_po, SUM(C.qty_po), C.po_uom_id, SUM(C.qty_int), C.base_uom_id, C.flg_stock
208 FROM pu_po_item_product A
209 INNER JOIN pu_po B
210 ON B.po_id = A.po_id
211 INNER JOIN pu_po_item C
212 ON C.po_item_id = A.po_item_id
213 INNER JOIN in_product_balance D
214 ON D.tenant_id = A.tenant_id
215 AND D.product_id = A.product_id
216 AND D.serial_number = A.serial_number
217 AND D.lot_number = A.lot_number
218 WHERE A.po_id = vGoodsPurchasingId
219 AND A.tenant_id = pTenantId
220 GROUP BY B.warehouse_id, A.tenant_id, B.ou_id, A.product_id, B.doc_date, B.partner_id,
221 B.doc_type_id, A.po_id, A.po_item_id, B.doc_no, B.doc_date,
222 A.po_id, B.doc_no, B.doc_date, A.po_item_id,
223 C.curr_code, C.gross_price_po, C.po_uom_id, C.base_uom_id, C.flg_stock;
224
225
226
227 /*
228 * update product_balance_id dari in_product_balance
229 */
230 UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
231 FROM in_product_balance A
232 WHERE tt_pu_product_balance.session_id = pSessionId AND
233 tt_pu_product_balance.tenant_id = A.tenant_id AND
234 tt_pu_product_balance.product_id = A.product_id AND
235 tt_pu_product_balance.serial_number = A.serial_number AND
236 tt_pu_product_balance.lot_number = A.lot_number;
237
238
239 --Insert ke in_product_balance yang id nya masih emptyId/ tidak ada di in product balance--
240 INSERT INTO in_product_balance
241 (tenant_id, product_id,
242 serial_number, lot_number, product_expired_date, product_year_made,
243 "version", create_datetime, create_user_id, update_datetime, update_user_id)
244 SELECT A.tenant_id, A.product_id,
245 A.serial_number, A.lot_number, A.product_expired_date, A.product_year_made,
246 0, vDatetime, vUserId, vDatetime, vUserId
247 FROM tt_pu_product_balance A
248 WHERE A.session_id = pSessionId AND
249 A.product_balance_id = vEmptyId
250 GROUP BY A.tenant_id, A.product_id, serial_number, lot_number, product_expired_date, product_year_made;
251
252
253 /*
254 * update product_balance_id yang masih empty, ambil dari in_product_balance yang sebelumnya sudah diinsert
255 */
256 UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
257 FROM in_product_balance A
258 WHERE tt_pu_product_balance.session_id = pSessionId AND
259 tt_pu_product_balance.product_balance_id = vEmptyId AND
260 tt_pu_product_balance.tenant_id = A.tenant_id AND
261 tt_pu_product_balance.product_id = A.product_id AND
262 tt_pu_product_balance.serial_number = A.serial_number AND
263 tt_pu_product_balance.lot_number = A.lot_number;
264
265
266 --Insert ke in_product_price_balance dengan product balance yang sudah diinsert sebelumnya--
267 INSERT INTO in_product_price_balance
268 (tenant_id, ou_id, product_id, product_balance_id,
269 product_buy_date, partner_id, doc_type_id, ref_id, doc_no, doc_date,
270 curr_code, amount, qty, uom_id,
271 "version", create_datetime, create_user_id, update_datetime, update_user_id)
272 SELECT A.tenant_id, A.ou_id, A.product_id, A.product_balance_id,
273 A.doc_date, A.partner_id, A.doc_type_id, A.ref_item_id, A.doc_no, A.doc_date,
274 A.curr_code, SUM(f_get_amount_before_tax(B.qty_po * A.price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingModeNonTax)),
275 SUM(B.qty_int), A.base_uom_id,
276 0, vDatetime, vUserId, vDatetime, vUserId
277 FROM tt_pu_product_balance A
278 INNER JOIN pu_po_item B
279 ON A.po_id = B.po_id
280 WHERE A.session_id = pSessionId AND
281 A.po_id = B.po_id
282 GROUP BY A.tenant_id, A.ou_id, A.product_id, A.product_balance_id, A.doc_date,
283 A.partner_id, A.doc_type_id, A.ref_item_id, A.doc_no, A.curr_code, A.base_uom_id;
284
285
286 UPDATE tt_pu_product_balance SET product_price_balance_id = A.product_price_balance_id
287 FROM in_product_price_balance A
288 WHERE tt_pu_product_balance.session_id = pSessionId AND
289 tt_pu_product_balance.tenant_id = A.tenant_id AND
290 tt_pu_product_balance.ou_id = A.ou_id AND
291 tt_pu_product_balance.product_id = A.product_id AND
292 tt_pu_product_balance.product_balance_id = A.product_balance_id AND
293 tt_pu_product_balance.partner_id = A.partner_id AND
294 tt_pu_product_balance.doc_type_id = A.doc_type_id AND
295 tt_pu_product_balance.ref_item_id = A.ref_id AND
296 tt_pu_product_balance.doc_no = A.doc_no AND
297 tt_pu_product_balance.doc_date = A.doc_date;
298
299
300 /*
301 * create summary dari tt_pu_product_balance untuk update yg sudah ada di in_product_balance_stock
302 */
303 INSERT INTO tt_pu_product_balance_summary_stock
304 (session_id, warehouse_id, product_balance_id, tenant_id, product_id,
305 product_price_balance_id, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
306 SELECT pSessionId, A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
307 vEmptyId, SUM(A.qty_rcv), A.po_uom_id, SUM(A.qty_int_rcv), A.base_uom_id, A.flg_stock
308 FROM tt_pu_product_balance A
309 WHERE A.session_id = pSessionId
310 GROUP BY A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
311 A.po_uom_id, A.base_uom_id, A.flg_stock;
312
313
314 /*
315 * update product_balance_stock, yang sudah ada di in_product_balance_stock
316 */
317 UPDATE in_product_balance_stock SET qty = qty + A.qty_int_rcv,
318 update_datetime = vDatetime, update_user_id = vUserId,
319 version = version + 1
320 FROM tt_pu_product_balance_summary_stock A
321 WHERE A.session_id = pSessionId AND
322 in_product_balance_stock.product_id = A.product_id AND
323 in_product_balance_stock.tenant_id = A.tenant_id AND
324 in_product_balance_stock.warehouse_id = A.warehouse_id AND
325 in_product_balance_stock.product_balance_id = A.product_balance_id AND
326 in_product_balance_stock.product_status = vProductStatus AND
327 A.flg_stock = 'Y';
328
329
330
331 /*
332 * insert data in_product_balance_stock yang datanya belum ada
333 */
334 INSERT INTO in_product_balance_stock
335 (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
336 "version", create_datetime, create_user_id, update_datetime, update_user_id)
337 SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, vProductStatus,
338 A.base_uom_id, SUM(A.qty_int_rcv),
339 0, vDatetime, vUserId, vDatetime, vUserId
340 FROM tt_pu_product_balance A
341 WHERE A.session_id = pSessionId AND
342 A.flg_stock = 'Y' AND
343 NOT EXISTS (SELECT 1 FROM in_product_balance_stock B
344 WHERE A.tenant_id = B.tenant_id AND
345 A.warehouse_id = B.warehouse_id AND
346 A.product_id = B.product_id AND
347 A.product_balance_id = B.product_balance_id AND
348 B.product_status = vProductStatus)
349 GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.base_uom_id;
350
351
352 /*
353 * insert data product_price_balance_stock
354 */
355 INSERT INTO in_product_price_balance_stock
356 (tenant_id, warehouse_id, product_id, product_balance_id,
357 product_price_balance_id, product_status, base_uom_id, qty,
358 "version", create_datetime, create_user_id, update_datetime, update_user_id)
359 SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id,
360 A.product_price_balance_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
361 0, vDatetime, vUserId, vDatetime, vUserId
362 FROM tt_pu_product_balance A
363 WHERE A.session_id = pSessionId AND
364 A.flg_stock = 'Y'
365 GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.product_price_balance_id, A.base_uom_id;
366
367
368 /*
369 * buat data log product balance stock
370 */
371 INSERT INTO in_log_product_balance_stock
372 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
373 product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
374 "version", create_datetime, create_user_id, update_datetime, update_user_id)
375 SELECT A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
376 A.product_id, A.product_balance_id, A.warehouse_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
377 0, vDatetime, vUserId, vDatetime, vUserId
378 FROM tt_pu_product_balance A, m_warehouse_ou B
379 WHERE A.session_id = pSessionId AND
380 A.flg_stock = 'Y' AND
381 A.warehouse_id = B.warehouse_id
382 GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
383 A.product_id, A.product_balance_id, A.warehouse_id, A.base_uom_id;
384
385
386 /*
387 * buat data log product price balance stock
388 */
389 INSERT INTO in_log_product_price_balance_stock
390 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
391 product_id, warehouse_id, product_balance_id, product_price_balance_id, product_status, base_uom_id, qty,
392 "version", create_datetime, create_user_id, update_datetime, update_user_id)
393 SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
394 A.product_id, A.warehouse_id, A.product_balance_id, A.product_price_balance_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
395 0, vDatetime, vUserId, vDatetime, vUserId
396 FROM tt_pu_product_balance A
397 WHERE A.session_id = pSessionId AND
398 A.flg_stock = 'Y'
399 GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
400 A.product_id, A.warehouse_id, A.product_balance_id, A.product_price_balance_id, A.base_uom_id;
401
402
403 /*
404 * add data balance receive goods item yang akan digunakan di inventory,
405 * saat akan membuat claim note
406 */
407 INSERT INTO in_balance_receive_goods_item
408 (receive_goods_item_id, tenant_id, ou_id, receive_goods_id, doc_no, doc_date, partner_id,
409 po_id, po_no, po_date, po_item_id,
410 qty_rcv, qty_return, po_uom_id, qty_int_rcv,
411 qty_int_return, base_uom_id, status_item,
412 "version", create_datetime, create_user_id, update_datetime, update_user_id)
413 SELECT A.ref_item_id, A.tenant_id, A.ou_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
414 A.po_id, A.po_no, A.po_date, A.po_item_id,
415 SUM(A.qty_rcv), 0, A.po_uom_id, SUM(A.qty_int_rcv),
416 0, A.base_uom_id, vStatusRelease,
417 0, vDatetime, vUserId, vDatetime, vUserId
418 FROM tt_pu_product_balance A
419 WHERE A.session_id = pSessionId
420 GROUP BY A.ref_item_id, A.tenant_id, A.ou_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
421 A.po_id, A.po_no, A.po_date, A.po_item_id, A.po_uom_id, A.base_uom_id;
422
423
424
425 /*
426 * create monthly price product
427 */
428 INSERT INTO tt_pu_monthly_price_product
429 (session_id, tenant_id, ou_id, year_month_date,
430 product_id, curr_code, amount, qty, base_uom_id,
431 ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, source_price,
432 flg_tax_amount, tax_id, tax_percentage)
433 SELECT pSessionId, A.tenant_id, A.ou_id, C.year_month_date,
434 A.product_id, A.curr_code, SUM(f_get_amount_before_tax(A.qty_rcv * A.price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingModeNonTax)),
435 SUM(A.qty_int_rcv), A.base_uom_id,
436 A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
437 'PURCHASING PO NO : '|| A.po_no || ' DATE : '|| A.po_date,
438 B.flg_tax_amount, B.tax_id, B.tax_percentage
439 FROM tt_pu_product_balance A, pu_po_item B, dt_date C
440 WHERE A.session_id = pSessionId AND
441 A.po_item_id = B.po_item_id AND
442 A.doc_date = C.string_date
443 GROUP BY A.tenant_id, A.ou_id, C.year_month_date, A.product_id, A.curr_code, A.base_uom_id,
444 A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.po_no, A.po_date, B.flg_tax_amount, B.tax_id, B.tax_percentage;
445
446 /*
447 * Update pu_monthly_price_product
448 */
449 UPDATE pu_monthly_price_product SET curr_code = A.curr_code, amount = A.amount,
450 qty = A.qty, ref_doc_type_id = A.ref_doc_type_id,
451 ref_id = A.ref_id, ref_doc_no = A.ref_doc_no, ref_doc_date = A.ref_doc_date,
452 source_price = A.source_price, flg_tax_amount = A.flg_tax_amount,
453 tax_id = A.tax_id, tax_percentage = A.tax_percentage
454 FROM tt_pu_monthly_price_product A
455 WHERE A.session_id = pSessionId AND
456 pu_monthly_price_product.tenant_id = A.tenant_id AND
457 pu_monthly_price_product.ou_id = A.ou_id AND
458 pu_monthly_price_product.year_month_date = A.year_month_date AND
459 pu_monthly_price_product.product_id = A.product_id;
460
461 INSERT INTO pu_monthly_price_product
462 (tenant_id, ou_id, year_month_date, product_id,
463 curr_code, amount, qty, base_uom_id, source_price,
464 ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
465 "version", create_datetime, create_user_id, update_datetime, update_user_id,
466 flg_tax_amount, tax_id, tax_percentage)
467 SELECT A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
468 A.curr_code, SUM(A.amount), SUM(A.qty), A.base_uom_id, A.source_price,
469 A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date,
470 0, vDatetime, vUserId, vDatetime, vUserId,
471 A.flg_tax_amount, A.tax_id, A.tax_percentage
472 FROM tt_pu_monthly_price_product A
473 WHERE A.session_id = pSessionId AND
474 NOT EXISTS (SELECT 1 FROM pu_monthly_price_product B
475 WHERE A.tenant_id = B.tenant_id AND
476 A.ou_id = B.ou_id AND
477 A.product_id = B.product_id AND
478 A.year_month_date = B.year_month_date)
479 GROUP BY A.tenant_id, A.ou_id, A.year_month_date, A.product_id, A.curr_code, A.base_uom_id, A.source_price,
480 A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date, A.flg_tax_amount, A.tax_id, A.tax_percentage;
481
482 INSERT INTO pu_log_monthly_price_product
483 (tenant_id, ou_id, year_month_date, product_id,
484 curr_code, amount, qty, base_uom_id, source_price,
485 ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
486 "version", create_datetime, create_user_id, update_datetime, update_user_id,
487 flg_tax_amount, tax_id, tax_percentage)
488 SELECT A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
489 A.curr_code, SUM(A.amount), SUM(A.qty), A.base_uom_id, A.source_price,
490 A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date,
491 0, vDatetime, vUserId, vDatetime, vUserId,
492 A.flg_tax_amount, A.tax_id, A.tax_percentage
493 FROM tt_pu_monthly_price_product A
494 WHERE A.session_id = pSessionId
495 GROUP BY A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
496 A.curr_code, A.base_uom_id, A.source_price, A.ref_doc_type_id, A.ref_id,
497 A.ref_doc_no, A.ref_doc_date, A.flg_tax_amount, A.tax_id, A.tax_percentage;
498
499 /*
500 * membuat data transaksi jurnal
501 */
502
503 SELECT f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(vDocTypePurchaseInvoiceId) as doc_pi, f_get_document_journal(vDocTypeReceiveGoodsId) as doc_rg
504 FROM pu_po A
505 WHERE A.po_id = vGoodsPurchasingId INTO result;
506
507 vOuStructure := result.ou;
508 vDocJournalPi := result.doc_pi;
509 vDocJournalRg := result.doc_rg;
510
511 -- Sebagai Journal Purchase Invoice
512 PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournalPi).journal_type, (vDocJournalPi).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', vDatetime, vUserId)
513 FROM pu_po A
514 WHERE A.po_id = vGoodsPurchasingId;
515
516 -- Sebagai Journal Goods Receive
517 PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournalRg).journal_type, (vDocJournalRg).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', vDatetime, vUserId)
518 FROM pu_po A
519 WHERE A.po_id = vGoodsPurchasingId;
520
521
522 SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
523
524 INSERT INTO gl_journal_trx
525 (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
526 ou_bu_id, ou_branch_id, ou_sub_bu_id,
527 partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
528 ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
529 due_date, curr_code, remark, status_doc, workflow_status,
530 "version", create_datetime, create_user_id, update_datetime, update_user_id)
531 SELECT vJournalTrxId, pTenantId, (f_get_document_journal(vReceiveGoodsDocTypeId)).journal_type, vReceiveGoodsDocTypeId, A.po_id, A.doc_no, A.doc_date,
532 (f_get_ou_bu_structure(A.ou_id)).ou_bu_id, (f_get_ou_bu_structure(A.ou_id)).ou_branch_id, (f_get_ou_bu_structure(A.ou_id)).ou_sub_bu_id,
533 A.partner_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
534 A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
535 A.doc_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
536 0, vDatetime, vUserId, vDatetime, vUserId
537 FROM pu_po A
538 WHERE A.po_id = vGoodsPurchasingId
539 AND A.tenant_id = pTenantId;
540 --A.ref_doc_type_id = B.doc_type_id AND
541 --A.ref_id = B.po_id;
542
543 /*------------------------------------------------*/
544
545 INSERT INTO gl_journal_trx_item
546 (tenant_id, journal_trx_id, line_no,
547 ref_doc_type_id, ref_id,
548 partner_id, product_id, cashbank_id, ou_rc_id,
549 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
550 coa_id, curr_code, qty, uom_id,
551 amount,
552 journal_date, type_rate,
553 numerator_rate, denominator_rate, journal_desc, remark,
554 "version", create_datetime, create_user_id, update_datetime, update_user_id,
555 ou_branch_id, ou_sub_bu_id)
556 SELECT pTenantId, vJournalTrxId, ROW_NUMBER() OVER ( PARTITION BY vJournalTrxId ),
557 A.doc_type_id, B.po_item_id,
558 A.partner_id, B.product_id, vEmptyId, B.ou_rc_id,
559 vEmptyId, vSignJournalD, 'PRODUCT', B.activity_gl_id,
560 B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id,
561 SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)),
562 A.doc_date, 'COM',
563 1, 1, 'PRODUCT_STOCK', B.remark,
564 0, vDatetime, vUserId, vDatetime, vUserId,
565 vEmptyId, vEmptyId
566 FROM pu_po A
567 INNER JOIN pu_po_item B
568 ON B.po_id = A.po_id
569 WHERE A.po_id = vGoodsPurchasingId
570 AND A.tenant_id = pTenantId
571 AND B.flg_stock = 'Y'
572 GROUP BY A.tenant_id, A.doc_type_id, B.po_item_id, A.partner_id, B.product_id, B.ou_rc_id,
573 B.activity_gl_id, B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id, A.doc_date;
574
575 /*------------------------------------------------*/
576
577 INSERT INTO gl_journal_trx_mapping
578 (tenant_id, journal_trx_id, line_no,
579 ref_doc_type_id, ref_id,
580 partner_id, product_id, cashbank_id, ou_rc_id,
581 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
582 coa_id,
583 curr_code, qty, uom_id,
584 amount,
585 journal_date, type_rate,
586 numerator_rate, denominator_rate, journal_desc, remark,
587 "version", create_datetime, create_user_id, update_datetime, update_user_id)
588 SELECT A.tenant_id, vJournalTrxId, ROW_NUMBER() OVER ( PARTITION BY vJournalTrxId ),
589 vEmptyId, vEmptyId,
590 A.partner_id, vEmptyId, vEmptyId, vEmptyId,
591 vEmptyId, vSignJournalC, 'SYSTEM', vEmptyId,
592 f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'),
593 A.curr_code, 0, vEmptyId,
594 SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)),
595 A.doc_date, 'COM',
596 1, 1, 'ACCR_AP', B.remark,
597 0, vDatetime, vUserId, vDatetime, vUserId
598 FROM pu_po A
599 INNER JOIN pu_po_item B
600 ON B.po_id = A.po_id
601 WHERE A.po_id = vGoodsPurchasingId
602 AND A.tenant_id = pTenantId
603 AND B.flg_stock = 'Y'
604 GROUP BY A.tenant_id, A.doc_type_id, B.po_item_id, A.partner_id, B.product_id, B.ou_rc_id,
605 A.curr_code, B.activity_gl_id, B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id, A.doc_date;
606
607 /*------------------------------------------------*/
608
609 SELECT nextval('fi_invoice_ap_balance_seq') INTO vInvoiceApBalanceId;
610
611 IF vJointDppPpn = 'N' THEN
612
613 INSERT INTO fi_invoice_ap_balance
614 ( invoice_ap_balance_id, tenant_id, ou_id, doc_type_id, invoice_ap_id,
615 doc_no, doc_date, ext_doc_no, ext_doc_date,
616 ref_doc_type_id, ref_id, partner_id, due_date,
617 curr_code, amount, remark, payment_amount, flg_payment,
618 "version", create_datetime, create_user_id, update_datetime, update_user_id)
619 SELECT vInvoiceApBalanceId, pTenantId, A.ou_id, A.doc_type_id, A.po_id,
620 A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
621 vEmptyId, vEmptyId, A.partner_id, A.doc_date,
622 A.curr_code, SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)),
623 A.remark, 0, vFlagPayment,
624 0, vDatetime, vUserId, vDatetime, vUserId
625 FROM pu_po A
626 INNER JOIN pu_po_item B
627 ON B.po_id = A.po_id
628 WHERE A.po_id = vGoodsPurchasingId
629 AND A.tenant_id = pTenantId
630 GROUP BY A.ou_id, A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
631 A.curr_code, A.ext_doc_no, A.ext_doc_date, A.partner_id, A.doc_date, A.curr_code;
632
633
634 INSERT INTO fi_invoice_tax_ap_balance
635 ( tenant_id, ou_id, doc_type_id, invoice_ap_balance_id,
636 partner_id, tax_id, tax_no, tax_date,
637 curr_code, tax_amount, tax_curr_code, gov_tax_amount,
638 due_date, remark, payment_amount, flg_payment,
639 "version", create_datetime, create_user_id, update_datetime, update_user_id,
640 gov_base_amount )
641 SELECT pTenantId, A.ou_id, A.doc_type_id, vInvoiceApBalanceId,
642 A.partner_id, B.tax_id, B.tax_no, B.tax_date,
643 A.curr_code,
644 SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage)),
645 A.curr_code,
646 SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage)),
647 A.doc_date, A.remark, 0, vFlagPayment,
648 0, vDatetime, vUserId, vDatetime, vUserId,
649 SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage))
650 FROM pu_po A
651 INNER JOIN pu_po_item B
652 ON B.po_id = A.po_id
653 INNER JOIN m_tax C
654 ON C.tax_id = B.tax_id
655 WHERE A.po_id = vGoodsPurchasingId
656 AND A.tenant_id = pTenantId;
657
658 perform fi_insert_vat_in_for_efaktur(pTenantId, pSessionId, vGoodsPurchasingId, vUserId, vDatetime);
659
660 ELSE
661
662 INSERT INTO fi_invoice_ap_balance
663 ( invoice_ap_balance_id, tenant_id, ou_id, doc_type_id, invoice_ap_id,
664 doc_no, doc_date, ext_doc_no, ext_doc_date,
665 ref_doc_type_id, ref_id, partner_id, due_date, curr_code,
666 amount, remark, payment_amount, flg_payment,
667 "version", create_datetime, create_user_id, update_datetime, update_user_id)
668 SELECT vInvoiceApBalanceId, pTenantId, A.ou_id, A.doc_type_id, A.po_id,
669 A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
670 vEmptyId, vEmptyId, A.partner_id, A.doc_date, A.curr_code,
671 SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax))
672 + SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), COALESCE(B.tax_percentage,0))),
673 A.remark, 0, vFlagPayment,
674 0, vDatetime, vUserId, vDatetime, vUserId
675 FROM pu_po A
676 INNER JOIN pu_po_item B
677 ON B.po_id = A.po_id
678 LEFT JOIN m_tax C
679 ON C.tax_id = B.tax_id
680 WHERE A.po_id = vGoodsPurchasingId
681 AND A.tenant_id = pTenantId
682 GROUP BY A.ou_id, A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
683 A.ext_doc_no, A.ext_doc_date, A.partner_id, A.doc_date, A.curr_code;
684
685 /*
686 INSERT INTO fi_invoice_ap_balance
687 ( invoice_ap_balance_id, tenant_id, ou_id, doc_type_id, invoice_ap_id,
688 doc_no, doc_date, ext_doc_no, ext_doc_date,
689 ref_doc_type_id, ref_id, partner_id, due_date, curr_code,
690 amount, remark, payment_amount, flg_payment,
691 "version", create_datetime, create_user_id, update_datetime, update_user_id)
692 SELECT vInvoiceApBalanceId, pTenantId, A.ou_id, A.doc_type_id, A.po_id,
693 A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
694 vEmptyId, vEmptyId, A.partner_id, A.doc_date, A.curr_code,
695 SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax))
696 + SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage)),
697 A.remark, 0, vFlagPayment,
698 0, vDatetime, vUserId, vDatetime, vUserId
699 FROM pu_po A
700 INNER JOIN pu_po_item B
701 ON B.po_id = A.po_id
702 INNER JOIN m_tax C
703 ON C.tax_id = B.tax_id
704 WHERE A.po_id = vGoodsPurchasingId
705 AND A.tenant_id = pTenantId
706 GROUP BY A.ou_id, A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
707 A.ext_doc_no, A.ext_doc_date, A.partner_id, A.doc_date, A.curr_code;
708 */
709
710 perform fi_insert_vat_in_invoice_ap_for_efaktur(pTenantId, pSessionId, vGoodsPurchasingId, vUserId, vDatetime);
711
712 END IF;
713
714
715 /*
716 * Insert ke journal gl
717 */
718 SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxIdNew;
719
720 INSERT INTO gl_journal_trx
721 (journal_trx_id, tenant_id, journal_type,
722 doc_type_id, doc_id, doc_no, doc_date,
723 ou_bu_id, ou_branch_id, ou_sub_bu_id,
724 partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
725 ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
726 due_date, curr_code, remark, status_doc, workflow_status,
727 "version", create_datetime, create_user_id, update_datetime, update_user_id)
728 SELECT vJournalTrxIdNew, pTenantId, (f_get_document_journal(vTypeDocumentJournal)).journal_type,
729 vTypeDocumentJournal, A.po_id, A.doc_no, A.doc_date,
730 (f_get_ou_bu_structure(A.ou_id)).ou_bu_id, (f_get_ou_bu_structure(A.ou_id)).ou_branch_id, (f_get_ou_bu_structure(A.ou_id)).ou_sub_bu_id,
731 A.partner_id, vEmptyId, vEmptyId, A.ext_doc_no, A.ext_doc_date,
732 A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
733 A.doc_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
734 0, vDatetime, vUserId, vDatetime, vUserId
735 FROM pu_po A
736 WHERE A.po_id = vGoodsPurchasingId
737 AND A.tenant_id = pTenantId;
738
739
740 --Insert untuk item 1--
741 INSERT INTO tt_journal_trx_item
742 (session_id, tenant_id, journal_trx_id, line_no,
743 ref_doc_type_id, ref_id,
744 partner_id, product_id, cashbank_id, ou_rc_id,
745 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
746 coa_id, curr_code, qty, uom_id,
747 amount,
748 journal_date, type_rate,
749 numerator_rate, denominator_rate, journal_desc, remark,
750 gl_curr_code, gl_amount )
751 SELECT pSessionId, pTenantId, vJournalTrxIdNew, 1,
752 A.doc_type_id, B.po_item_id,
753 A.partner_id, vEmptyId, vEmptyId, vEmptyId,
754 vEmptyId, vSignJournalD, 'SYSTEM', vEmptyId,
755 f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), B.curr_code, 0, vEmptyId,
756 SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)),
757 A.doc_date, 'COM',
758 1, 1, 'ACCR_AP', B.remark,
759 vEmptyValue, vEmptyId
760 FROM pu_po A
761 INNER JOIN pu_po_item B
762 ON B.po_id = A.po_id
763 WHERE A.po_id = vGoodsPurchasingId
764 AND A.tenant_id = pTenantId
765 AND B.flg_stock = 'Y'
766 GROUP BY A.tenant_id, A.doc_type_id, B.po_item_id, A.partner_id, B.product_id, B.ou_rc_id,
767 B.activity_gl_id, B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id, A.doc_date;
768
769 --Insert untuk item 2--
770 INSERT INTO tt_journal_trx_item
771 (session_id, tenant_id, journal_trx_id, line_no,
772 ref_doc_type_id, ref_id,
773 partner_id, product_id, cashbank_id, ou_rc_id,
774 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
775 coa_id, curr_code, qty, uom_id,
776 amount,
777 journal_date, type_rate,
778 numerator_rate, denominator_rate, journal_desc, remark,
779 gl_curr_code, gl_amount )
780 SELECT pSessionId, pTenantId, vJournalTrxIdNew, 1,
781 A.doc_type_id, B.po_id,
782 A.partner_id, B.product_id, vEmptyId, vEmptyId,
783 vEmptyId, vSignJournalD, 'TAX', vEmptyId,
784 C.receive_coa_id, B.curr_code, 0, vEmptyId,
785 SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage)),
786 A.doc_date, 'COM',
787 1, 1, 'VAT_IN', B.remark,
788 vEmptyId, vEmptyId
789 FROM pu_po A
790 INNER JOIN pu_po_item B
791 ON B.po_id = A.po_id
792 INNER JOIN m_tax C
793 ON C.tax_id = B.tax_id
794 WHERE A.po_id = vGoodsPurchasingId
795 AND A.tenant_id = pTenantId
796 AND B.flg_stock = 'Y'
797 GROUP BY A.tenant_id, A.doc_type_id, B.po_item_id, A.partner_id, B.product_id, B.ou_rc_id,
798 C.receive_coa_id, B.activity_gl_id, B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id, A.doc_date;
799
800
801 INSERT INTO gl_journal_trx_item
802 (tenant_id, journal_trx_id, line_no,
803 ref_doc_type_id, ref_id,
804 partner_id, product_id, cashbank_id, ou_rc_id,
805 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
806 coa_id, curr_code, qty, uom_id,
807 amount, journal_date, type_rate,
808 numerator_rate, denominator_rate, journal_desc, remark,
809 "version", create_datetime, create_user_id, update_datetime, update_user_id,
810 ou_branch_id, ou_sub_bu_id)
811 SELECT pTenantId, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
812 A.ref_doc_type_id, A.ref_id,
813 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
814 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
815 A.coa_id, A.curr_code, A.qty, A.uom_id,
816 A.amount, A.journal_date, A.type_rate,
817 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
818 0, vDatetime, vUserId, vDatetime, vUserId,
819 vEmptyId, vEmptyId
820 FROM tt_journal_trx_item A
821 WHERE A.session_id = pSessionId;
822
823
824 INSERT INTO gl_journal_trx_mapping
825 (tenant_id, journal_trx_id, line_no,
826 ref_doc_type_id, ref_id,
827 partner_id, product_id, cashbank_id, ou_rc_id,
828 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
829 coa_id,
830 curr_code, qty, uom_id,
831 amount,
832 journal_date, type_rate,
833 numerator_rate, denominator_rate, journal_desc, remark,
834 "version", create_datetime, create_user_id, update_datetime, update_user_id)
835 SELECT pTenantId, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
836 vEmptyId, vEmptyId,
837 A.partner_id, vEmptyId, vEmptyId, vEmptyId,
838 vEmptyId, vSignJournalC, 'SYSTEM', vEmptyId,
839 f_get_ap_coa_partner(A.tenant_id, A.partner_id),
840 A.curr_code, 0, vEmptyId,
841 SUM(A.amount),
842 A.journal_date, 'COM',
843 1, 1, 'AP', A.remark,
844 0, vDatetime, vUserId, vDatetime, vUserId
845 FROM tt_journal_trx_item A
846 WHERE A.session_id = pSessionId
847 GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate, A.remark;
848
849
850 --Delete table temp tt_pu_product_balance--
851 DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;
852
853END;
854$BODY$
855 LANGUAGE plpgsql VOLATILE
856 COST 100;
857 /