· 4 years ago · Aug 02, 2021, 10:54 AM
1-- Function: in_submit_exchange_out(bigint, character varying, character varying)
2
3-- DROP FUNCTION in_submit_exchange_out(bigint, character varying, character varying);
4
5CREATE OR REPLACE FUNCTION in_submit_exchange_out(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 vDocId bigint;
15 vUserId bigint;
16 vDatetime character varying(14);
17 vStatusDraft character varying(1);
18 vStatusRelease character varying(1);
19 vEmptyId bigint;
20 vZeroQty bigint;
21 vFlgNo character varying(1);
22 vEmptyValue character varying(1);
23 vSignDebit character varying(1);
24 vSignCredit character varying(1);
25 vProductCOA character varying(10);
26 vSystemCOA character varying(10);
27 vTypeRate character varying(3);
28 vJournalTrxId bigint;
29
30 vDocJournal DOC_JOURNAL%ROWTYPE;
31 vOuStructure OU_BU_STRUCTURE%ROWTYPE;
32 result RECORD;
33
34 vReqId bigint;
35 vProductStatus character varying := 'GOOD';
36 vReturnValue character varying := 'RETURN';
37 vCursorForItem in_in_out_exchange_item%ROWTYPE;
38 vCursorForProduct tt_update_product_balance_stock_for_do%ROWTYPE;
39 vTempTotal numeric := 0;
40 vQty numeric;
41 vQtyMinus numeric := 0;
42 vProductBalanceId bigint;
43 vReqExchangeOut bigint := 599;
44 vResult character varying;
45 vFlgYes character varying := 'Y';
46 vDocTypeLogBook bigint := 440;
47 vLogBookType character varying(5);
48 vFlgDelivery character varying(1);
49 vBelumCetak character varying(2);
50
51BEGIN
52
53 vStatusDraft := 'D';
54 vStatusRelease := 'R';
55 vEmptyId := -99;
56 vZeroQty := 0;
57 vFlgNo := 'N';
58 vEmptyValue := '';
59 vSignDebit := 'D';
60 vSignCredit := 'C';
61 vProductCOA := 'PRODUCT';
62 vSystemCOA := 'SYSTEM';
63 vTypeRate := 'COM';
64 vFlgDelivery := 'Y';
65 vBelumCetak := 'BC';
66
67 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
68 DELETE FROM tt_update_product_balance_stock_for_exchange WHERE session_id = pSessionId;
69
70 SELECT A.process_message_id INTO vProcessId
71 FROM t_process_message A
72 WHERE A.tenant_id = pTenantId AND
73 A.process_name = 'in_submit_exchange_out' AND
74 A.process_no = pProcessNo;
75
76 SELECT CAST(A.process_parameter_value AS bigint) INTO vDocId
77 FROM t_process_parameter A
78 WHERE A.process_message_id = vProcessId AND
79 A.process_parameter_key = 'exchangeId';
80
81 SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
82 FROM t_process_parameter A
83 WHERE A.process_message_id = vProcessId AND
84 A.process_parameter_key = 'userId';
85
86 SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
87 FROM t_process_parameter A
88 WHERE A.process_message_id = vProcessId AND
89 A.process_parameter_key = 'datetime';
90
91 SELECT f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
92 FROM in_in_out_exchange A
93 WHERE A.in_out_exchange_id = vDocId INTO result;
94
95 vOuStructure := result.ou;
96 vDocJournal := result.doc;
97
98 -- update in_in_out_exchange : status doc to R
99 UPDATE in_in_out_exchange
100 SET status_doc = vStatusRelease,
101 version = version + 1,
102 update_datetime = vDatetime,
103 update_user_id = vUserId
104 WHERE in_out_exchange_id = vDocId;
105
106 --get document request exchange out
107 SELECT req_id INTO vReqId
108 FROM in_in_out_exchange
109 WHERE in_out_exchange_id = vDocId;
110
111 /*
112 * mengurangi reserved stock
113 * JIKA product tsb masuk verification, berarti berdasar qty verification
114 * JIKA TIDAK, berarti berdasar qty request exchange
115 */
116 /* Masukan daftar product req exchange, jika dilakukan verification */
117 INSERT INTO tt_data_product_verification(
118 session_id, tenant_id, ou_id, product_id, qty_verify)
119 SELECT pSessionId, A.tenant_id, A.ou_id, C.product_id, C.qty_verify
120 FROM in_in_out_exchange A
121 INNER JOIN in_verification_header B ON A.in_out_exchange_id = B.doc_id AND B.doc_type_id = vReqExchangeOut
122 INNER JOIN in_verification_item C ON B.verification_id = C.verification_id
123 WHERE A.in_out_exchange_id = vReqId;
124
125 -- LOCK data in_product_balance_stock_reserved atas produk yg di verifikasi
126 PERFORM 1 FROM in_product_balance_stock_reserved A
127 WHERE EXISTS(SELECT 1 FROM tt_data_product_verification B
128 WHERE B.session_id = pSessionId AND
129 A.tenant_id = B.tenant_id AND
130 A.ou_id = B.ou_id AND
131 A.product_id = B.product_id AND
132 A.product_status = 'GOOD')
133 FOR UPDATE;
134
135
136 -- LOCK data in_product_balance_stock_reserved atas produk yg di req exchange dan tidak diverifikasi
137 PERFORM 1 FROM in_product_balance_stock_reserved A
138 WHERE EXISTS (SELECT 1 FROM in_in_out_exchange B, in_in_out_exchange_item C
139 WHERE B.in_out_exchange_id = vReqId AND
140 B.in_out_exchange_id = C.in_out_exchange_id AND
141 A.tenant_id = B.tenant_id AND
142 A.ou_id = B.ou_id AND
143 A.product_id = C.product_id AND
144 A.product_status = 'GOOD')
145 AND NOT EXISTS (SELECT 1 FROM tt_data_product_verification X
146 WHERE X.session_id = pSessionId AND
147 A.tenant_id = X.tenant_id AND
148 A.ou_id = X.ou_id AND
149 A.product_id = X.product_id AND
150 A.product_status = 'GOOD')
151 FOR UPDATE;
152
153
154 /* Mengurangi reserved stock berdasarkan qty verification (jika ada dilakukan verification) */
155 UPDATE in_product_balance_stock_reserved A
156 SET qty = A.qty - B.qty_verify,
157 update_datetime = vDatetime,
158 update_user_id = vUserId,
159 version = A.version + 1
160 FROM tt_data_product_verification B
161 WHERE B.session_id = pSessionId AND
162 A.tenant_id = B.tenant_id AND
163 A.ou_id = B.ou_id AND
164 A.product_id = B.product_id AND
165 A.product_status = 'GOOD';
166
167 INSERT INTO in_log_product_balance_stock_reserved(
168 tenant_id, ou_id, ref_doc_type_id,
169 ref_id, doc_no, doc_date, product_id,
170 product_status, base_uom_id, qty,
171 remark,
172 version, create_datetime, create_user_id, update_datetime, update_user_id)
173 SELECT B.tenant_id, B.ou_id, C.doc_type_id,
174 C.in_out_exchange_id, C.doc_no, C.doc_date, C.partner_id, B.product_id,
175 A.product_status, A.base_uom_id, (B.qty_verify * -1),
176 'Pengurangan saldo reserved stock pada function submit EXCHANGE OUT',
177 0, vDatetime, vUserId, vDatetime, vUserId
178 FROM in_product_balance_stock_reserved A, tt_data_product_verification B, in_in_out_exchange C
179 WHERE B.session_id = pSessionId
180 AND A.tenant_id = B.tenant_id
181 AND A.ou_id = B.ou_id
182 AND A.product_id = B.product_id
183 AND A.product_status = 'GOOD'
184 AND C.in_out_exchange_id = vReqId;
185
186 /* Mengurangi reserved stock berdasarkan qty req exchange (untuk data yg tidak dilakukan verification) */
187 UPDATE in_product_balance_stock_reserved A
188 SET qty = A.qty - C.qty,
189 update_datetime = vDatetime,
190 update_user_id = vUserId,
191 version = A.version + 1
192 FROM in_in_out_exchange B
193 INNER JOIN in_in_out_exchange_item C ON B.in_out_exchange_id = C.in_out_exchange_id
194 WHERE A.tenant_id = B.tenant_id AND
195 A.ou_id = B.ou_id AND
196 A.product_id = C.product_id AND
197 A.product_status = 'GOOD' AND
198 B.in_out_exchange_id = vReqId AND
199 NOT EXISTS (SELECT 1 FROM tt_data_product_verification X
200 WHERE X.session_id = pSessionId AND
201 X.tenant_id = A.tenant_id AND
202 X.ou_id = A.ou_id AND
203 X.product_id = A.product_id);
204
205 INSERT INTO in_log_product_balance_stock_reserved(
206 tenant_id, ou_id, ref_doc_type_id,
207 ref_id, doc_no, doc_date, partner_id, product_id,
208 product_status, base_uom_id, qty,
209 remark,
210 version, create_datetime, create_user_id, update_datetime, update_user_id)
211 SELECT B.tenant_id, B.ou_id, B.doc_type_id,
212 B.in_out_exchange_id, B.doc_no, B.doc_date, B.partner_id, A.product_id.
213 A.product_status, A.base_uom_id, (C.qty * -1),
214 'Pengurangan saldo reserved stock pada function submit EXCHANGE OUT',
215 0, vDatetime, vUserId, vDatetime, vUserId
216 FROM in_product_balance_stock_reserved A
217 INNER JOIN in_in_out_exchange B ON A.tenant_id = B.tenant_id
218 AND A.ou_id = B.ou_id
219 AND B.in_out_exchange_id = vReqId
220 INNER JOIN in_in_out_exchange_item C ON B.in_out_exchange_id = C.in_out_exchange_id
221 AND A.product_id = C.product_id
222 WHERE A.product_status = 'GOOD'
223 AND NOT EXISTS (
224 SELECT 1
225 FROM tt_data_product_verification X
226 WHERE X.session_id = pSessionId
227 AND X.tenant_id = A.tenant_id
228 AND X.ou_id = A.ou_id
229 AND X.product_id = A.product_id
230 );
231
232 /**
233 * ==============================================================
234 * Potong Stock berdasarkan qty Exchange Out & warehouse priority
235 * ==============================================================
236 */
237
238 --ambil data stock per gudang beserta prioritas gudang
239 INSERT INTO tt_update_product_balance_stock_for_exchange (
240 session_id, warehouse_id, product_id, qty, qty_minus, product_balance_id,
241 warehouse_priority, product_status)
242 SELECT pSessionId, A.warehouse_id, A.product_id, SUM(A.qty), 0 AS qty_minus, A.product_balance_id,
243 CASE WHEN A.product_status = vReturnValue THEN '0' ELSE D.priority_no END, A.product_status
244 FROM in_product_balance_stock A
245 JOIN in_in_out_exchange_item B on A.product_balance_id = B.product_balance_id
246 JOIN m_warehouse C on A.warehouse_id = C.warehouse_Id
247 JOIN temp_warehouse_priority D on D.whs_id = C.warehouse_id
248 WHERE B.in_out_exchange_id = vDocId
249 AND A.product_status IN (vProductStatus, vReturnValue)
250 GROUP BY A.product_id, D.priority_no, A.warehouse_id, A.product_balance_id, A.product_status;
251
252 -- melengkapi data stok yg tidak ada di in_product_balance_stock
253 INSERT INTO tt_update_product_balance_stock_for_exchange (
254 session_id, warehouse_id, product_id, qty, qty_minus,
255 product_balance_id, warehouse_priority, product_status)
256 SELECT pSessionId, C.whs_id, B.product_id, 0, 0,
257 B.product_balance_id, C.priority_no, vProductStatus
258 FROM in_in_out_exchange_item A, in_product_balance B, temp_warehouse_priority C
259 WHERE A.in_out_exchange_id = vDocId AND
260 A.product_balance_id = B.product_balance_id AND
261 C.priority_no = '1' AND
262 C.ctgr_product_id = f_get_ctgr_product_by_product_id(B.product_id) AND
263 NOT EXISTS (SELECT 1 FROM tt_update_product_balance_stock_for_exchange D
264 WHERE D.session_id = pSessionId AND
265 A.product_id = D.product_id AND
266 B.product_balance_id = D.product_balance_id AND
267 C.whs_id = D.warehouse_id AND
268 D.product_status = vProductStatus);
269
270 --mengisi qty minus berdasarkan urutan prioritas
271 FOR vCursorForItem IN
272 SELECT * FROM in_in_out_exchange_item
273 WHERE in_out_exchange_id = vDocId
274 LOOP
275 vQty = vCursorForItem.qty;
276 vProductBalanceId = vCursorForItem.product_balance_id;
277 vTempTotal = vQty;
278 FOR vCursorForProduct IN
279 SELECT * FROM tt_update_product_balance_stock_for_exchange
280 WHERE session_id = pSessionId
281 AND product_balance_id = vProductBalanceId
282 ORDER BY warehouse_priority
283 LOOP
284
285 IF vTempTotal = 0 THEN
286 EXIT;
287 ELSEIF vCursorForProduct.qty > 0 AND vTempTotal > vCursorForProduct.qty THEN
288 vQtyMinus = vCursorForProduct.qty;
289 UPDATE tt_update_product_balance_stock_for_exchange
290 SET qty_minus = vQtyMinus
291 WHERE session_id = pSessionId
292 AND product_balance_id = vProductBalanceId
293 AND id = vCursorForProduct.id;
294 vTempTotal = vTempTotal - vCursorForProduct.qty;
295
296 ELSEIF vCursorForProduct.qty > 0 THEN
297
298 vQtyMinus = vTempTotal;
299 RAISE NOTICE '%',vCursorForProduct.id;
300 UPDATE tt_update_product_balance_stock_for_exchange
301 SET qty_minus = vQtyMinus
302 WHERE session_id = pSessionId
303 AND product_balance_id = vProductBalanceId
304 AND id = vCursorForProduct.id;
305 vTempTotal :=0;
306 END IF;
307
308 END LOOP;
309 IF vTempTotal > 0 THEN
310 UPDATE tt_update_product_balance_stock_for_exchange
311 SET qty_minus = qty_minus + vTempTotal
312 WHERE session_id = pSessionId AND
313 product_balance_id = vProductBalanceId AND
314 warehouse_priority = '1' AND
315 product_status = vProductStatus;
316 END IF;
317 END LOOP;
318
319 --potong stock berdasarkan qty minus
320 UPDATE in_product_balance_stock A
321 SET qty = A.qty - B.qty_minus,
322 update_datetime = vDatetime,
323 update_user_id = vUserId,
324 version = a.version + 1
325 FROM tt_update_product_balance_stock_for_exchange B
326 WHERE B.session_id = pSessionId AND
327 B.qty_minus > 0 AND
328 A.product_id = B.product_id AND
329 A.warehouse_id = B.warehouse_id AND
330 A.product_balance_id = B.product_balance_id AND
331 A.product_status = B.product_status;
332
333 INSERT INTO in_product_balance_stock
334 (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
335 version, create_datetime, create_user_id, update_datetime, update_user_id)
336 SELECT pTenantId, A.warehouse_id, A.product_id, A.product_balance_id, A.product_status, f_get_base_uom_by_product_id(A.product_id), A.qty_minus * -1,
337 0,vDatetime, vUserId, vDatetime , vUserId
338 FROM tt_update_product_balance_stock_for_exchange A
339 INNER JOIN m_product B ON A.product_id = B.product_id
340 WHERE A.session_id = pSessionId AND
341 A.qty_minus > 0 AND
342 NOT EXISTS (SELECT 1 FROM in_product_balance_stock B
343 WHERE A.product_id = B.product_id AND
344 A.warehouse_id = B.warehouse_id AND
345 A.product_balance_id = B.product_balance_id AND
346 A.product_status = B.product_status);
347
348 /*
349 * buat data log product balance stock
350 */
351 INSERT INTO in_log_product_balance_stock (
352 tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date,
353 partner_id, product_id, warehouse_id, product_balance_id,
354 product_status, base_uom_id, qty,
355 version, create_datetime, create_user_id, update_datetime, update_user_id)
356 SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.in_out_exchange_id, A.doc_no, A.doc_date,
357 A.partner_id, E.product_id, E.warehouse_id, E.product_balance_id,
358 E.product_status, f_get_base_uom_by_product_id(E.product_id), SUM(E.qty_minus) * -1,
359 0, vDatetime, vUserId, vDatetime, vUserId
360 FROM in_in_out_exchange A
361 INNER JOIN in_in_out_exchange_item B ON A.in_out_exchange_id = B.in_out_exchange_id
362 INNER JOIN tt_update_product_balance_stock_for_exchange E ON B.product_balance_id = E.product_balance_id
363 WHERE A.in_out_exchange_id = vDocId
364 AND E.session_id = pSessionId
365 AND E.qty_minus > 0
366 GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.in_out_exchange_id, A.doc_no, A.doc_date,
367 A.partner_id, E.product_id, E.product_balance_id, E.warehouse_id, E.product_status;
368
369 --insert data exchange balance
370 INSERT INTO in_in_out_exchange_balance (
371 tenant_id, ou_id, exchange_id, partner_id, brand_id, product_id,
372 qty_exchange, qty_mapping, qty_settlement, status_mapping,
373 version, create_datetime, create_user_id, update_datetime, update_user_id)
374 SELECT A.tenant_id, A.ou_id, A.in_out_exchange_id, A.partner_id, A.brand_id, B.product_id,
375 SUM(B.qty) * -1 AS qty_exchange, vZeroQty, vZeroQty, vFlgNo,
376 0, vDatetime, vUserId, vDatetime, vUserId
377 FROM in_in_out_exchange A
378 INNER JOIN in_in_out_exchange_item B ON A.in_out_exchange_id = B.in_out_exchange_id
379 WHERE A.in_out_exchange_id = vDocId
380 GROUP BY A.tenant_id, A.ou_id, A.in_out_exchange_id, A.partner_id, A.brand_id, B.product_id;
381
382 --insert data log
383 INSERT INTO in_in_out_exchange_balance_log(
384 tenant_id, ou_id, exchange_balance_id, partner_id,
385 product_id, qty_exchange, ref_doc_type_id, ref_id, remark,
386 version, create_datetime, create_user_id, update_datetime, update_user_id)
387 SELECT A.tenant_id, A.ou_id, A.exchange_balance_id, A.partner_id,
388 A.product_id, A.qty_exchange, B.doc_type_id, B.in_out_exchange_id, 'Init Log Balance' AS remark,
389 0, vDatetime, vUserId, vDatetime, vUserId
390 FROM in_in_out_exchange_balance A
391 INNER JOIN in_in_out_exchange B ON A.exchange_id = B.in_out_exchange_id
392 WHERE A.exchange_id = vDocId;
393
394 /* Modify by Henik, 22 Juli 2016
395 * - Check Doc EXO merupakan type log book apa ?
396 * - INSERT KE TABLE in_item_log_book_balance -> untuk keperluan print log book untuk barang exchange out
397 * */
398
399 -- Get flg delivery
400 SELECT flg_delivery INTO vFlgDelivery FROM in_in_out_exchange WHERE in_out_exchange_id = vDocId;
401
402 -- Check log book type
403 IF (vFlgDelivery = vFlgNo) THEN
404 -- pick up (diambil sendiri)
405 vLogBookType := 'DAS';
406 ELSE
407 -- Deliver (dikirim)
408
409 IF EXISTS(SELECT 1 FROM in_in_out_exchange A
410 INNER JOIN m_partner B ON A.partner_ship_to_id = B.partner_id
411 INNER JOIN in_group_log_book C ON C.group_log_book = 'PP' AND B.partner_code = C.parameter
412 WHERE A.tenant_id = pTenantId AND A.in_out_exchange_id = vDocId) THEN
413
414 -- type log book pelanggan
415 vLogBookType := 'PP';
416
417 ELSIF EXISTS(SELECT 1 FROM in_in_out_exchange A
418 INNER JOIN m_partner_address B ON A.partner_ship_address_id = B.partner_address_id
419 INNER JOIN in_group_log_book C ON C.group_log_book = 'DK' AND B.city = C.parameter
420 WHERE A.tenant_id = pTenantId AND A.in_out_exchange_id = vDocId) THEN
421
422 -- type log book dalam kota
423 vLogBookType := 'DK';
424
425 ELSE
426 -- Selain yang diatas berarti type log book luar kota
427 vLogBookType := 'LK';
428 END IF;
429 END IF;
430
431 INSERT INTO in_item_log_book_balance(
432 tenant_id, ou_id, doc_type_id, ref_doc_type_id, ref_doc_date, ref_doc_no,
433 log_book_type, partner_id, partner_ship_to_id, partner_ship_address_id, partner_city,
434 summary_product, expedition_id, courier_id,
435 receipt_doc_no, remark, log_book_id, line_no, status_item, ctgr_product_id,
436 create_datetime, create_user_id, update_datetime, update_user_id, version)
437 SELECT A.tenant_id, A.ou_id, vDocTypeLogBook, A.doc_type_id, A.doc_date, A.doc_no,
438 vLogBookType, A.partner_id, A.partner_ship_to_id, A.partner_ship_address_id, C.city,
439 f_get_brand_code(A.brand_id)||':'||SUM(B.qty), vEmptyId, vEmptyId,
440 vEmptyValue, vEmptyValue, vEmptyId, vEmptyId, vBelumCetak, D.ctgr_product_id,
441 vDatetime, vUserId, vDatetime, vUserId, 0
442 FROM in_in_out_exchange A
443 INNER JOIN in_in_out_exchange_item B ON A.in_out_exchange_id = B.in_out_exchange_id
444 INNER JOIN m_partner_address C ON A.partner_ship_address_id = C.partner_address_id
445 INNER JOIN m_product D ON B.product_id = D.product_id
446 WHERE A.tenant_id = pTenantId
447 AND A.in_out_exchange_id = vDocId
448 AND A.status_doc = vStatusRelease
449 GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_date, A.doc_no, A.partner_id,
450 A.partner_ship_to_id, A.partner_ship_address_id, C.city, A.brand_id, D.ctgr_product_id;
451
452 /* Modify by Henik, 22 Juli 2016
453 * About log book END
454 **/
455
456 -- UPDATE STATUS REQ EXCHANGE OUT
457 UPDATE in_in_out_exchange_status SET
458 status_exchange_out = vFlgYes,
459 exchange_out_id = vDocId,
460 update_datetime = vDatetime,
461 update_user_id = vUserId,
462 version = version + 1
463 WHERE in_out_exchange_id = vReqId;
464
465 --Create JURNAL
466 PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', vDatetime, vUserId)
467 FROM in_in_out_exchange A
468 WHERE A.in_out_exchange_id = vDocId;
469
470 SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
471
472 INSERT INTO gl_journal_trx
473 (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
474 ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id,
475 cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
476 ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
477 "version", create_datetime, create_user_id, update_datetime, update_user_id)
478 SELECT vJournalTrxId, tenant_id, (vDocJournal).journal_type, doc_type_id, in_out_exchange_id, doc_no, doc_date,
479 (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, partner_id,
480 vEmptyId, warehouse_id, ext_doc_no, ext_doc_date,
481 ref_doc_type_id, ref_id, doc_date, vEmptyValue, remark, vStatusDraft, 'DRAFT',
482 0, vDatetime, vUserId, vDatetime, vUserId
483 FROM in_in_out_exchange
484 WHERE in_out_exchange_id = vDocId;
485
486 INSERT INTO tt_journal_trx_item
487 (session_id, tenant_id, journal_trx_id, line_no,
488 ref_doc_type_id, ref_id,
489 partner_id, product_id, cashbank_id, ou_rc_id,
490 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
491 coa_id, curr_code, qty, uom_id,
492 amount, journal_date, type_rate,
493 numerator_rate, denominator_rate, journal_desc, remark)
494 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
495 A.doc_type_id, B.exchange_item_id,
496 A.partner_id, B.product_id, vEmptyId, vEmptyId,
497 vEmptyId, vSignCredit, vProductCOA, vEmptyId,
498 f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty, vEmptyId,
499 0, A.doc_date, vTypeRate,
500 1, 1, 'PRODUCT_STOCK', B.remark
501 FROM in_in_out_exchange A
502 INNER JOIN in_in_out_exchange_item B ON A.in_out_exchange_id = B.in_out_exchange_id
503 WHERE A.in_out_exchange_id = vDocId;
504
505 INSERT INTO gl_journal_trx_item
506 (tenant_id, journal_trx_id, line_no,
507 ref_doc_type_id, ref_id,
508 partner_id, product_id, cashbank_id, ou_rc_id,
509 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
510 coa_id, curr_code, qty, uom_id,
511 amount, journal_date, type_rate,
512 numerator_rate, denominator_rate, journal_desc, remark,
513 "version", create_datetime, create_user_id, update_datetime, update_user_id)
514 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
515 A.ref_doc_type_id, A.ref_id,
516 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
517 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
518 A.coa_id, A.curr_code, A.qty, A.uom_id,
519 A.amount, A.journal_date, A.type_rate,
520 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
521 0, vDatetime, vUserId, vDatetime, vUserId
522 FROM tt_journal_trx_item A
523 WHERE A.session_id = pSessionId;
524
525 INSERT INTO gl_journal_trx_mapping
526 (tenant_id, journal_trx_id, line_no,
527 ref_doc_type_id, ref_id,
528 partner_id, product_id, cashbank_id, ou_rc_id,
529 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
530 coa_id, curr_code, qty, uom_id,
531 amount, journal_date, type_rate,
532 numerator_rate, denominator_rate, journal_desc, remark,
533 "version", create_datetime, create_user_id, update_datetime, update_user_id)
534 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
535 vEmptyId, vEmptyId,
536 vEmptyId, vEmptyId, vEmptyId, vEmptyId,
537 vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
538 f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
539 0, A.journal_date, A.type_rate,
540 1, 1, 'COGS', vEmptyValue,
541 0, vDatetime, vUserId, vDatetime, vUserId
542 FROM tt_journal_trx_item A
543 WHERE A.session_id = pSessionId
544 GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate;
545
546 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
547 DELETE FROM tt_update_product_balance_stock_for_exchange WHERE session_id = pSessionId;
548
549END
550$BODY$
551 LANGUAGE plpgsql VOLATILE
552 COST 100;
553/