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