· 2 years ago · Jul 05, 2023, 04:35 AM
1CREATE OR REPLACE FUNCTION in_submit_adjusment_qty(
2 bigint,
3 character varying,
4 character varying)
5 RETURNS void
6 LANGUAGE 'plpgsql'
7 COST 100
8 VOLATILE PARALLEL UNSAFE
9AS $BODY$
10DECLARE
11 pTenantId ALIAS FOR $1;
12 pSessionId ALIAS FOR $2;
13 pProcessNo ALIAS FOR $3;
14
15 vProcessId bigint;
16 vAdjQtyId bigint;
17 vUserId bigint;
18 vDatetime character varying(14);
19 vFlagNo character varying(1);
20 vFlagYes character varying(1);
21 vEmptyId bigint;
22 vActivityCOA character varying(20);
23 vStatusRelease character varying(1);
24 vFinalTransaction character varying(1);
25 vStatusDraft character varying(1);
26 vEmptyValue character varying(1);
27 vJournalType character varying(20);
28 vSignDebit character varying(1);
29 vSignCredit character varying(1);
30 vFlgKonsinyasi character varying(1);
31 vTypeRate character varying(3);
32 vProductCOA character varying(10);
33 vParentOuId bigint;
34 vJournalTrxId bigint;
35 vOuId bigint;
36 vOuWarehouseIdForJournal bigint;
37 vStockOpnameDoc character varying(10);
38 vRefDocTypeId character varying(10);
39 vRefId bigint;
40
41 vDocJournal DOC_JOURNAL%ROWTYPE;
42 vOuStructure OU_BU_STRUCTURE%ROWTYPE;
43 vOuStructureJournalItem OU_BU_STRUCTURE%ROWTYPE;
44 result RECORD;
45
46 vOuWarehouseId bigint;
47 vOutletId bigint;
48 vDbVersion character varying(10);
49
50BEGIN
51
52 vEmptyId := -99;
53 vFlagNo := 'N';
54 vFlagYes := 'Y';
55 vActivityCOA := 'ACTIVITY';
56 vStatusRelease := 'R';
57 vStatusDraft := 'D';
58 vFinalTransaction := 'F';
59 vSignDebit := 'D';
60 vSignCredit := 'C';
61 vTypeRate := 'COM';
62 vProductCOA := 'PRODUCT';
63 vEmptyValue := ' ';
64 vFlgKonsinyasi := '';
65 vStockOpnameDoc := 539L;
66
67
68 SELECT A.process_message_id INTO vProcessId
69 FROM t_process_message A
70 WHERE A.tenant_id = pTenantId AND
71 A.process_name = 'in_submit_adjusment_qty' AND
72 A.process_no = pProcessNo;
73
74 SELECT CAST(A.process_parameter_value AS bigint) INTO vAdjQtyId
75 FROM t_process_parameter A
76 WHERE A.process_message_id = vProcessId AND
77 A.process_parameter_key = 'adjQtyId';
78
79 SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
80 FROM t_process_parameter A
81 WHERE A.process_message_id = vProcessId AND
82 A.process_parameter_key = 'userId';
83
84 SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
85 FROM t_process_parameter A
86 WHERE A.process_message_id = vProcessId AND
87 A.process_parameter_key = 'datetime';
88
89 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
90 DELETE FROM tt_in_product_balance WHERE session_id = pSessionId;
91 /*
92 * 1.update status doc in_inventory
93 * 2.add in_produuct_balance untuk product_balance_id = -99
94 * 3.add/update in_product_balance_stock, krn qty > 0
95 * 4.insert data ke in_log_product_balance_stock
96 * 5.buat log jika ou merupakan ou outlet
97 * 6.jurnal trx :
98 * jika qty > 0
99 * Debit Stok
100 * Credit activity
101 * jika qty < 0
102 * Credit Stok
103 * Debit activity
104 */
105
106 SELECT f_get_ou_bu_structure(A.ou_from_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
107 FROM in_inventory A
108 WHERE A.inventory_id = vAdjQtyId INTO result;
109
110 vOuStructure := result.ou;
111 vDocJournal := result.doc;
112
113 UPDATE in_inventory SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
114 WHERE inventory_id = vAdjQtyId;
115
116 /*
117 * mengambil data dari in_inventory_item tanpa melakukan group by untuk ditampung ke tt_in_product_balance
118 */
119 INSERT INTO tt_in_product_balance
120 (session_id, tenant_id, inventory_id, inventory_item_id, product_balance_id, product_id, product_status,
121 serial_number, product_expired_date, product_year_made, lot_number, qty_realization, base_uom_id, flg_buy_konsinyasi)
122 SELECT pSessionId, A.tenant_id, A.inventory_id, B.inventory_item_id, B.product_balance_id, B.product_id, B.product_status,
123 B.serial_number, B.product_expired_date, B.product_year_made, B.lot_number, B.qty_realization, B.base_uom_id, vFlagNo
124 FROM in_inventory A, in_inventory_item B
125 WHERE A.inventory_id = B.inventory_id AND
126 A.inventory_id = vAdjQtyId;
127
128 /**
129 * Update untuk yang flg_buy_konsinyasi Y
130 *
131 */
132 UPDATE tt_in_product_balance Z
133 SET flg_buy_konsinyasi = vFlagYes
134 FROM m_product_custom A
135 WHERE A.product_id = Z.product_id
136 AND A.flg_buy_konsinyasi = vFlagYes
137 AND session_id = pSessionId;
138
139 /*
140 * add product balance yang belum ada
141 */
142 INSERT INTO in_product_balance
143 (tenant_id, product_id, serial_number, lot_number,
144 product_expired_date, product_year_made,
145 "version", create_datetime, create_user_id, update_datetime, update_user_id)
146 SELECT A.tenant_id, A.product_id, A.serial_number, A.lot_number,
147 A.product_expired_date, A.product_year_made,
148 0, vDatetime, vUserId, vDatetime, vUserId
149 FROM tt_in_product_balance A
150 WHERE A.session_id = pSessionId AND
151 A.product_balance_id = vEmptyId AND
152 NOT EXISTS (SELECT 1 FROM in_product_balance B
153 WHERE A.tenant_id = B.tenant_id AND
154 A.product_id = B.product_id AND
155 A.serial_number = B.serial_number AND
156 A.lot_number = B.lot_number)
157 GROUP BY A.tenant_id, A.product_id, A.serial_number, A.lot_number,
158 A.product_expired_date, A.product_year_made;
159
160 /*
161 * update product balance id sesuai dengan data yang ada
162 */
163 UPDATE tt_in_product_balance SET product_balance_id = A.product_balance_id
164 FROM in_product_balance A
165 WHERE tt_in_product_balance.session_id = pSessionId AND
166 tt_in_product_balance.product_id = A.product_id AND
167 tt_in_product_balance.tenant_id = A.tenant_id AND
168 tt_in_product_balance.serial_number = A.serial_number AND
169 tt_in_product_balance.lot_number = A.lot_number AND
170 tt_in_product_balance.product_balance_id = vEmptyId;
171
172 WITH tt_in_product_price_balance_summary AS (
173 SELECT B.session_id, A.inventory_id, A.warehouse_from_id AS warehouse_id, A.tenant_id, B.product_id,
174 B.product_balance_id, B.product_status, SUM(B.qty_realization) AS qty_sum
175 FROM in_inventory A, tt_in_product_balance B
176 WHERE B.session_id = pSessionId
177 AND A.inventory_id = B.inventory_id
178 GROUP BY B.session_id, A.inventory_id, A.warehouse_from_id, A.tenant_id, B.product_id,
179 B.product_balance_id, B.product_status
180 )
181 UPDATE in_product_balance_stock
182 SET qty = qty + B.qty_sum, update_datetime = vDatetime, update_user_id = vUserId, version = version + 1
183 FROM tt_in_product_price_balance_summary B
184 WHERE B.inventory_id = vAdjQtyId AND
185 B.session_id = pSessionId AND
186 in_product_balance_stock.tenant_id = B.tenant_id AND
187 in_product_balance_stock.warehouse_id = B.warehouse_id AND
188 in_product_balance_stock.product_id = B.product_id AND
189 in_product_balance_stock.product_balance_id = B.product_balance_id AND
190 in_product_balance_stock.product_status = B.product_status AND
191 B.qty_sum > 0;
192
193 /*
194 * insert data in_product_balance_stock
195 */
196 INSERT INTO in_product_balance_stock
197 (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
198 "version", create_datetime, create_user_id, update_datetime, update_user_id)
199 SELECT A.tenant_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
200 0, vDatetime, vUserId, vDatetime, vUserId
201 FROM in_inventory A, tt_in_product_balance B
202 WHERE B.session_id = pSessionId AND
203 A.inventory_id = B.inventory_id AND
204 A.inventory_id = vAdjQtyId AND
205 NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
206 WHERE C.tenant_id = A.tenant_id AND
207 C.warehouse_id = A.warehouse_from_id AND
208 C.product_id = B.product_id AND
209 C.product_balance_id = B.product_balance_id AND
210 C.product_status = B.product_status)
211 GROUP BY A.tenant_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;
212
213 /*
214 * membuat data in_log_product_balance_stock, dengan cara memisahkan antara qty > 0 dan qty < 0
215 */
216 INSERT INTO in_log_product_balance_stock
217 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
218 product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
219 "version", create_datetime, create_user_id, update_datetime, update_user_id)
220 SELECT A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
221 B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
222 0, vDatetime, vUserId, vDatetime, vUserId
223 FROM in_inventory A, tt_in_product_balance B, m_warehouse_ou C
224 WHERE A.inventory_id = vAdjQtyId AND
225 A.inventory_id = B.inventory_id AND
226 B.session_id = pSessionId AND
227 B.qty_realization > 0 AND
228 B.flg_buy_konsinyasi = vFlagNo AND
229 A.warehouse_from_id = C.warehouse_id
230 GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
231 B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
232
233 INSERT INTO in_log_product_balance_stock
234 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
235 product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
236 "version", create_datetime, create_user_id, update_datetime, update_user_id)
237 SELECT A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
238 B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
239 0, vDatetime, vUserId, vDatetime, vUserId
240 FROM in_inventory A, tt_in_product_balance B, m_warehouse_ou C
241 WHERE A.inventory_id = vAdjQtyId AND
242 A.inventory_id = B.inventory_id AND
243 B.session_id = pSessionId AND
244 B.qty_realization < 0 AND
245 B.flg_buy_konsinyasi = vFlagNo AND
246 A.warehouse_from_id = C.warehouse_id
247 GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
248 B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
249
250 /*
251 * Membuat log untuk produk konsinyasi
252 */
253 INSERT INTO in_log_product_consignment_balance_stock
254 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
255 product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
256 "version", create_datetime, create_user_id, update_datetime, update_user_id)
257 SELECT A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
258 B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
259 0, vDatetime, vUserId, vDatetime, vUserId
260 FROM in_inventory A, tt_in_product_balance B, m_warehouse_ou C
261 WHERE A.inventory_id = vAdjQtyId AND
262 A.inventory_id = B.inventory_id AND
263 B.session_id = pSessionId AND
264 B.qty_realization > 0 AND
265 B.flg_buy_konsinyasi = vFlagYes AND
266 A.warehouse_from_id = C.warehouse_id
267 GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
268 B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
269
270 INSERT INTO in_log_product_consignment_balance_stock
271 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
272 product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
273 "version", create_datetime, create_user_id, update_datetime, update_user_id)
274 SELECT A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
275 B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
276 0, vDatetime, vUserId, vDatetime, vUserId
277 FROM in_inventory A, tt_in_product_balance B, m_warehouse_ou C
278 WHERE A.inventory_id = vAdjQtyId AND
279 A.inventory_id = B.inventory_id AND
280 B.session_id = pSessionId AND
281 B.qty_realization < 0 AND
282 B.flg_buy_konsinyasi = vFlagYes AND
283 A.warehouse_from_id = C.warehouse_id
284 GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
285 B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
286
287
288 /*
289 * buat data log untuk update stok outlet jika ou ini adalah ou outlet
290 * */
291
292 -- cek dulu warehouse ou nya
293 SELECT B.ou_id INTO vOuWarehouseId
294 FROM in_inventory A
295 INNER JOIN m_warehouse_ou B ON A.warehouse_from_id = B.warehouse_id
296 WHERE A.inventory_id = vAdjQtyId;
297
298 IF vOuWarehouseId IS NULL THEN
299 vOuWarehouseId = vEmptyId;
300 END IF;
301
302 -- cek dulu apakah ou yang digunakan ini merupakan ou outlet
303 -- 25 okt 2021, erl,cari yg outler nya non embedded
304 IF EXISTS(SELECT 1 FROM i_outlet A WHERE A.ou_id = vOuWarehouseId) THEN
305 SELECT A.outlet_id INTO vOutletId
306 FROM i_outlet A WHERE A.ou_id = vOuWarehouseId AND A.flg_embedded = vFlagNo;
307 END IF;
308
309 IF vOutletId IS NULL THEN
310 vOutletId = vEmptyId;
311 END IF;
312
313 -- jika outlet, maka harus tulis data log agar outlet bisa lakukan update
314 -- 25 okt 2021, erl,hanya insert i_trx_data_log jika outlet non embeded
315 IF vOutletId <> vEmptyId THEN
316 vDbVersion := f_get_value_system_config_by_param_code(pTenantId, 'DbVersion');
317
318 INSERT INTO i_trx_data_log(
319 tenant_id, ou_id, doc_date, db_version, type_data,
320 mode_log, data_log, version, create_datetime, create_user_id,
321 update_datetime, update_user_id)
322 SELECT B.tenant_id, B.ou_id, B.doc_date, vDbVersion, 'in_product_balance',
323 'A', '{"id":'||C.product_balance_id||',"tenantId":"'||C.tenant_id||'","productId":'||C.product_id||',"serialNumber":"'||C.serial_number||'","lotNumber":"'||C.lot_number||'","productExpiredDate":"'||C.product_expired_date||'","productYearMade":"'||C.product_year_made||'","createDateTime":"'||C.create_datetime||'","createUserId":'||C.create_user_id||',"version":'||C.version||',"updateDateTime":"'||C.update_datetime||'","updateUserId":'||C.update_user_id||'}',
324 0, vDatetime, vUserId, vDatetime, vUserId
325 FROM in_inventory A
326 INNER JOIN in_log_product_balance_stock B
327 ON A.tenant_id = B.tenant_id
328 AND A.doc_no = B.doc_no
329 AND A.doc_date = B.doc_date
330 AND A.doc_type_id = B.doc_type_id
331 AND A.inventory_id = B.ref_id
332 AND A.warehouse_from_id = B.warehouse_id
333 INNER JOIN in_product_balance C
334 ON B.product_balance_id = C.product_balance_id
335 WHERE A.inventory_id = vAdjQtyId
336 GROUP BY B.tenant_id, B.ou_id, B.doc_date, C.product_balance_id;
337
338 INSERT INTO i_trx_data_log(
339 tenant_id, ou_id, doc_date, db_version, type_data,
340 mode_log, data_log, version, create_datetime, create_user_id,
341 update_datetime, update_user_id)
342 SELECT B.tenant_id, B.ou_id, B.doc_date, vDbVersion, 'in_log_product_balance_stock',
343 'A', '{"tenantId":'||B.tenant_id||',"createDateTime":"'||B.create_datetime||'","baseUomId":'||B.base_uom_id||',"ouId":'||B.ou_id||',"docNo":"'||B.doc_no||'","refId":'||B.ref_id||',"partnerId":'||B.partner_id||',"qty":'||B.qty||',"warehouseId":'||B.warehouse_id||',"version":'||B.version||',"productId":'||B.product_id||',"updateUserId":'||B.update_user_id||',"productStatus":"'||B.product_status||'","docDate":"'||B.doc_date||'","updateDateTime":"'||B.update_datetime||'","productBalanceId":'||B.product_balance_id||',"createUserId":'||B.create_user_id||',"docTypeId":'||B.doc_type_id||'}',
344 0, vDatetime, vUserId, vDatetime, vUserId
345 FROM in_inventory A
346 INNER JOIN in_log_product_balance_stock B
347 ON A.tenant_id = B.tenant_id
348 AND A.doc_no = B.doc_no
349 AND A.doc_date = B.doc_date
350 AND A.doc_type_id = B.doc_type_id
351 AND A.inventory_id = B.ref_id
352 AND A.warehouse_from_id = B.warehouse_id
353 WHERE A.inventory_id = vAdjQtyId;
354 END IF;
355
356 /*
357 * @author TKP, 9 Jun 2016
358 * Cek jika OU pada warehouse sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id =-99
359 * jika OU pada warehouse tidak sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id didapat pada f_get_ou_bu_structure;
360 */
361
362 SELECT A.ou_from_id, B.ou_id INTO vOuId, vOuWarehouseIdForJournal
363 FROM in_inventory A
364 INNER JOIN m_warehouse_ou B ON A.warehouse_from_id = B.warehouse_id
365 WHERE A.inventory_id = vAdjQtyId;
366
367 IF (vOuId <> vOuWarehouseIdForJournal) THEN
368 SELECT f_get_ou_bu_structure(vOuWarehouseIdForJournal) as ou_structure INTO result;
369 vOuStructureJournalItem := result.ou_structure;
370 ELSE
371 vOuStructureJournalItem := ROW(-99, -99, -99);
372 END IF;
373
374 /*
375 * @author anton, 19 April 2022
376 * Add data ke table pj_wo_project_real_product
377 */
378
379 PERFORM in_insert_real_product(vAdjQtyId, vDatetime, vUserId);
380
381 /*
382 * @author PS, 23 Agustus 2016
383 * cek product konsinyasi, apabila semua productnya konsinyasi, maka tidak perlu tulis ke jurnal
384 */
385 SELECT flg_buy_konsinyasi FROM tt_in_product_balance WHERE flg_buy_konsinyasi = vFlagNo INTO vFlgKonsinyasi;
386
387 IF FOUND THEN
388 /*
389 * journal adjusment qty
390 * Debit Inventory, jika qty > 0
391 * Credit Inventory, jika qty < 0
392 *
393 * Mapping ke account activity gl
394 */
395 /*
396 * membuat data transaksi jurnal :
397 * 1. buat admin
398 * 2. buat temlate jurnal
399 */
400 PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_from_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', vDatetime, vUserId)
401 FROM in_inventory A
402 WHERE A.inventory_id = vAdjQtyId;
403
404 SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
405
406 INSERT INTO gl_journal_trx
407 (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
408 ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
409 ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
410 "version", create_datetime, create_user_id, update_datetime, update_user_id)
411 SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date,
412 (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, A.warehouse_from_id, A.ext_doc_no, A.ext_doc_date,
413 A.ref_doc_type_id, A.ref_id, A.doc_date, f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), A.remark, vStatusDraft, 'DRAFT',
414 0, vDatetime, vUserId, vDatetime, vUserId
415 FROM in_inventory A
416 WHERE A.inventory_id = vAdjQtyId;
417
418 INSERT INTO tt_journal_trx_item
419 (session_id, tenant_id, journal_trx_id, line_no,
420 ref_doc_type_id, ref_id,
421 partner_id, product_id, cashbank_id, ou_rc_id,
422 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
423 coa_id, curr_code, qty, uom_id,
424 amount, journal_date, type_rate,
425 numerator_rate, denominator_rate, journal_desc, remark)
426 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
427 A.doc_type_id, B.inventory_item_id,
428 A.partner_id, B.product_id, vEmptyId, vEmptyId,
429 vEmptyId, vSignDebit, vProductCOA, vEmptyId,
430 f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
431 0 , A.doc_date, vTypeRate,
432 1, 1, 'PRODUCT_STOCK', B.remark
433 FROM in_inventory A, in_inventory_item B
434 WHERE A.inventory_id = vAdjQtyId AND
435 A.inventory_id = B.inventory_id AND
436 B.qty_realization > 0 AND
437 NOT EXISTS (SELECT 1 FROM m_product_custom C
438 WHERE B.product_id = C.product_id AND
439 C.flg_buy_konsinyasi = vFlagYes);
440
441 INSERT INTO tt_journal_trx_item
442 (session_id, tenant_id, journal_trx_id, line_no,
443 ref_doc_type_id, ref_id,
444 partner_id, product_id, cashbank_id, ou_rc_id,
445 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
446 coa_id, curr_code, qty, uom_id,
447 amount, journal_date, type_rate,
448 numerator_rate, denominator_rate, journal_desc, remark)
449 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
450 A.doc_type_id, B.inventory_item_id,
451 A.partner_id, B.product_id, vEmptyId, vEmptyId,
452 vEmptyId, vSignCredit, vProductCOA, vEmptyId,
453 f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), ABS(B.qty_realization), B.base_uom_id,
454 0 , A.doc_date, vTypeRate,
455 1, 1, 'PRODUCT_STOCK', B.remark
456 FROM in_inventory A, in_inventory_item B
457 WHERE A.inventory_id = vAdjQtyId AND
458 A.inventory_id = B.inventory_id AND
459 B.qty_realization < 0 AND
460 NOT EXISTS (SELECT 1 FROM m_product_custom C
461 WHERE B.product_id = C.product_id AND
462 C.flg_buy_konsinyasi = vFlagYes);
463 /*
464 * NK, 1 Feb 2014
465 * jurnal activity gl tidak di break down per product
466 */
467 INSERT INTO tt_journal_trx_item
468 (session_id, tenant_id, journal_trx_id, line_no,
469 ref_doc_type_id, ref_id,
470 partner_id, product_id, cashbank_id, ou_rc_id,
471 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
472 coa_id, curr_code, qty, uom_id,
473 amount, journal_date, type_rate,
474 numerator_rate, denominator_rate, journal_desc, remark)
475 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
476 vEmptyId, vEmptyId,
477 vEmptyId, vEmptyId, vEmptyId, A.ou_rc_id,
478 -- NK, 1 September 2014 - add segment_id
479 A.segment_id, vSignCredit, vActivityCOA, A.activity_gl_id,
480 D.coa_id, f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
481 0 , A.doc_date, vTypeRate,
482 1, 1, 'ADJUSMENT_STOCK', vEmptyValue
483 FROM in_inventory A, m_activity_gl D
484 WHERE A.inventory_id = vAdjQtyId AND
485 A.activity_gl_id = D.activity_gl_id;
486
487 /*
488 INSERT INTO tt_journal_trx_item
489 (session_id, tenant_id, journal_trx_id, line_no,
490 ref_doc_type_id, ref_id,
491 partner_id, product_id, cashbank_id, ou_rc_id,
492 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
493 coa_id, curr_code, qty, uom_id,
494 amount, journal_date, type_rate,
495 numerator_rate, denominator_rate, journal_desc, remark)
496 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
497 A.doc_type_id, B.inventory_item_id,
498 A.partner_id, B.product_id, vEmptyId, vEmptyId,
499 vEmptyId, vSignCredit, vActivityCOA, A.ou_rc_id,
500 D.coa_id, f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
501 0 , A.doc_date, vTypeRate,
502 1, 1, 'ADJUSMENT_STOCK', B.remark
503 FROM in_inventory A, in_inventory_item B, m_activity_gl D
504 WHERE A.inventory_id = vAdjQtyId AND
505 A.inventory_id = B.inventory_id AND
506 B.qty_realization > 0 AND
507 A.activity_gl_id = D.activity_gl_id;
508
509 INSERT INTO tt_journal_trx_item
510 (session_id, tenant_id, journal_trx_id, line_no,
511 ref_doc_type_id, ref_id,
512 partner_id, product_id, cashbank_id, ou_rc_id,
513 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
514 coa_id, curr_code, qty, uom_id,
515 amount, journal_date, type_rate,
516 numerator_rate, denominator_rate, journal_desc, remark)
517 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
518 A.doc_type_id, B.inventory_item_id,
519 A.partner_id, B.product_id, vEmptyId, vEmptyId,
520 vEmptyId, vSignDebit, vActivityCOA, A.ou_rc_id,
521 D.coa_id, f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
522 0 , A.doc_date, vTypeRate,
523 1, 1, 'ADJUSMENT_STOCK', B.remark
524 FROM in_inventory A, in_inventory_item B, m_activity_gl D
525 WHERE A.inventory_id = vAdjQtyId AND
526 A.inventory_id = B.inventory_id AND
527 B.qty_realization < 0 AND
528 A.activity_gl_id = D.activity_gl_id;
529 */
530 INSERT INTO gl_journal_trx_item
531 (tenant_id, journal_trx_id, line_no,
532 ref_doc_type_id, ref_id,
533 partner_id, product_id, cashbank_id, ou_rc_id,
534 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
535 coa_id, curr_code, qty, uom_id,
536 amount, journal_date, type_rate,
537 numerator_rate, denominator_rate, journal_desc, remark,
538 "version", create_datetime, create_user_id, update_datetime, update_user_id,
539 ou_branch_id, ou_sub_bu_id)
540 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
541 A.ref_doc_type_id, A.ref_id,
542 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
543 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
544 A.coa_id, A.curr_code, A.qty, A.uom_id,
545 A.amount, A.journal_date, A.type_rate,
546 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
547 0, vDatetime, vUserId, vDatetime, vUserId,
548 (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
549 FROM tt_journal_trx_item A
550 WHERE A.session_id = pSessionId AND
551 A.journal_desc = 'PRODUCT_STOCK';
552
553 INSERT INTO gl_journal_trx_mapping
554 (tenant_id, journal_trx_id, line_no,
555 ref_doc_type_id, ref_id,
556 partner_id, product_id, cashbank_id, ou_rc_id,
557 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
558 coa_id, curr_code, qty, uom_id,
559 amount, 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, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
563 A.ref_doc_type_id, A.ref_id,
564 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
565 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
566 A.coa_id, A.curr_code, A.qty, A.uom_id,
567 A.amount, A.journal_date, A.type_rate,
568 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
569 0, vDatetime, vUserId, vDatetime, vUserId
570 FROM tt_journal_trx_item A
571 WHERE A.session_id = pSessionId AND
572 A.journal_desc = 'ADJUSMENT_STOCK';
573
574 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
575 END IF;
576 DELETE FROM tt_in_product_balance WHERE session_id = pSessionId;
577
578 -- Ubah status item menjadi final approved
579 SELECT A.ref_doc_type_id, A.ref_id INTO vRefDocTypeId, vRefId
580 FROM in_inventory A
581 INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
582 WHERE A.inventory_id = vAdjQtyId;
583 IF vRefDocTypeId = vStockOpnameDoc THEN
584 UPDATE in_stock_opname_item_balance
585 SET status_item = vFinalTransaction
586 WHERE stock_opname_result_id = vRefId;
587 END IF;
588 -- end update status item
589
590END;
591$BODY$;
592
593ALTER FUNCTION in_submit_adjusment_qty(bigint, character varying, character varying)
594 OWNER TO sts;
595