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