· 8 years ago · May 15, 2018, 04:42 AM
1-- 5 Des 2017, modified fitra menambahkan update in_so_balance_for_rgto untuk mengembalikan nilai flg_rekap
2-- 15 Mei 2018 modified by Fitra
3-- jika OU pada warehouse tidak sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id di tabel gl_journal_trx_id
4-- menyesuaikan dengan ou structure dari ou warehouse
5CREATE OR REPLACE FUNCTION sl_submit_do(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 vDoId bigint;
15 vUserId bigint;
16 vDatetime character varying(14);
17 vFlagInvoice character varying(1);
18 vEmptyId bigint;
19 vStatusInProgress character varying(1);
20 vStatusRelease character varying(1);
21 vStatusDraft character varying(1);
22 vStatusFinal character varying(1);
23 vEmptyValue character varying(1);
24 vProductStatus character varying(5);
25 vSignDebit character varying(1);
26 vSignCredit character varying(1);
27 vTypeRate character varying(3);
28 vProductCOA character varying(10);
29 vSystemCOA character varying(10);
30 vSoId bigint;
31 vOuId bigint;
32 vUnfinishedItem bigint;
33 vParentOuId bigint;
34 vJournalTrxId bigint;
35 vJournalType character varying(20);
36
37 vDocJournal DOC_JOURNAL%ROWTYPE;
38 vOuStructure OU_BU_STRUCTURE%ROWTYPE;
39 result RECORD;
40
41 vDeliveryOrderDocTypeId bigint;
42 vRoundingModeNonTax character varying(5);
43 vNo character varying(1);
44 vRemarkModifyItemSo text;
45 vRoleId bigint;
46 vFlgUserRole character varying;
47 vSchemeDo character varying(20) := 'FB01';
48 vDocNoNewSo character varying(30);
49 vDocNoModifyItemSo character varying(30);
50 vDocDate character varying(8);
51 vModifyItemSoId bigint;
52 vAutonumIdModifyItemSo bigint;
53 vAutonumIdSo bigint;
54 vYes character varying(1);
55 vEmptyAmount numeric := 0;
56 vPromoEvent character varying :='PROMOEVENT';
57 vSoDocTypeId bigint := 301;
58
59 vOuWarehouseId bigint;
60 vOuStructureJournalItem OU_BU_STRUCTURE%ROWTYPE;
61
62BEGIN
63
64 vFlagInvoice := 'N';
65 vEmptyId := -99;
66 vStatusInProgress := 'I';
67 vStatusRelease := 'R';
68 vStatusDraft := 'D';
69 vStatusFinal := 'F';
70 vEmptyValue := ' ';
71 vProductStatus := 'GOOD';
72 vSignDebit := 'D';
73 vSignCredit := 'C';
74 vTypeRate := 'COM';
75 vProductCOA := 'PRODUCT';
76 vSystemCOA := 'SYSTEM';
77 vUnfinishedItem := 0;
78 vNo := 'N';
79 vYes := 'Y';
80
81 vDeliveryOrderDocTypeId = 311;
82 SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
83
84
85 SELECT A.process_message_id INTO vProcessId
86 FROM t_process_message A
87 WHERE A.tenant_id = pTenantId AND
88 A.process_name = 'sl_submit_do' AND
89 A.process_no = pProcessNo;
90
91 SELECT CAST(A.process_parameter_value AS bigint) INTO vDoId
92 FROM t_process_parameter A
93 WHERE A.process_message_id = vProcessId AND
94 A.process_parameter_key = 'doId';
95
96 SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
97 FROM t_process_parameter A
98 WHERE A.process_message_id = vProcessId AND
99 A.process_parameter_key = 'userId';
100
101 SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
102 FROM t_process_parameter A
103 WHERE A.process_message_id = vProcessId AND
104 A.process_parameter_key = 'datetime';
105
106 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
107 DELETE FROM tt_calculate_amount_so WHERE session_id = pSessionId;
108
109/*
110 * 1. update status doc sl_do
111 * 2. add sl_log_so_balance_item
112 * 3. add sl_so_balance_invoice
113 * 4. add sl_so_balance_invoice_tax
114 * 5. add in_log_product_balance_stock
115 * 6. add in_balance_do_item
116 * 7. update status sl_so_balance_item
117 * 8. update status sl_so. Jika seluruh balance item sudah final/cancel, maka status menjadi Final.
118 * 9. add gl_journal_trx
119 * 10. add gl_journal_trx_item
120 * 11. add gl_journal_trx_mapping
121 *
122 */
123
124 SELECT A.ref_id, A.ou_id, A.doc_date, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
125 FROM sl_do A
126 WHERE A.do_id = vDoId INTO result;
127
128 vOuId := result.ou_id;
129 vSoId := result.ref_id;
130 vDocDate := result.doc_date;
131 vOuStructure := result.ou;
132 vDocJournal := result.doc;
133
134 /*
135 *
136 * Cek jika OU pada warehouse sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id =-99
137 * 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;
138 */
139
140 SELECT B.ou_id INTO vOuWarehouseId
141 FROM sl_do A
142 INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
143 WHERE A.do_id = vDoId;
144
145 IF (vOuId <> vOuWarehouseId) THEN
146 SELECT f_get_ou_bu_structure(vOuWarehouseId) as ou_structure INTO result;
147 vOuStructureJournalItem := result.ou_structure;
148 ELSE
149 vOuStructureJournalItem := ROW(-99, -99, -99);
150 END IF;
151
152 UPDATE sl_do SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
153 WHERE do_id = vDoId;
154
155 INSERT INTO sl_log_so_balance_item
156 (tenant_id, so_id, so_item_id, ref_doc_type_id, ref_id, ref_item_id,
157 qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
158 "version", create_datetime, create_user_id, update_datetime, update_user_id)
159 SELECT A.tenant_id, C.so_id, C.so_item_id, A.doc_type_id, A.do_id, B.do_item_id,
160 B.qty_dlv_so * -1, B.so_uom_id, B.qty_dlv_int *-1, B.base_uom_id, B.remark,
161 0, vDatetime, vUserId, vDatetime, vUserId
162 FROM sl_do A, sl_do_item B, sl_so_item C
163 WHERE A.do_id = vDoId AND
164 A.do_id = B.do_id AND
165 B.ref_id = C.so_item_id;
166
167 INSERT INTO sl_balance_do_custom_for_dlg(
168 do_id, tenant_id, doc_no, doc_date, ou_id, warehouse_id, partner_id,
169 partner_ship_to_id, so_id, so_no, so_date, flg_dkb, dkb_id, flg_packing_list,
170 packing_list_id, create_datetime, create_user_id, update_datetime,
171 update_user_id, version)
172 SELECT A.do_id,A.tenant_id,A.doc_no,A.doc_date,A.ou_id,A.warehouse_id,B.partner_id,
173 A.partner_ship_to_id,B.so_id AS so_id,B.doc_no AS so_no,B.doc_date AS so_date,vNo,-99,'N',-99,A.create_datetime, A.create_user_id, A.update_datetime,
174 A.update_user_id, A.version
175 FROM sl_do A
176 INNER JOIN sl_so B ON A.ref_id = B.so_id
177 WHERE A.do_id = vDoId;
178
179 /*
180 * Masukan ke table temp untuk data item SO yg di DO
181 * Data untuk dilakukan perhitungan saat insert ke sl_so_balance_invoice dan sl_so_balance_invoice_tax
182 */
183 INSERT INTO tt_calculate_amount_so(
184 session_id, tenant_id, ou_id, partner_id, so_id, ref_id, ref_doc_type_id,
185 ref_doc_no, ref_doc_date, ref_item_id, gross_sell_price, qty, so_uom_id,
186 promo_percentage, flg_tax_amount, tax_id, tax_percentage, gross_amount, curr_code,
187 gross_after_disc,
188 total_gross_after_disc, total_promo_disc_amount,
189 regular_disc_percentage, reg_disc_amount, total_reg_disc_amount, gross_after_regular_disc,
190 tax_amount, nett_amount, tax_price, nett_price, price_so, item_amount, do_receipt_item_id,
191 add_disc_percentage, total_add_disc_amount, add_disc_amount, gross_after_add_disc)
192 SELECT pSessionId, A.tenant_id, A.ou_id, D.partner_bill_to_id, A.ref_id, A.do_id, A.doc_type_id,
193 A.doc_no, A.doc_date, B.do_item_id, C.gross_sell_price, B.qty_dlv_so, B.so_uom_id,
194 E.promo_percentage, C.flg_tax_amount, C.tax_id, C.tax_percentage, (B.qty_dlv_so*C.gross_sell_price) AS gross_amount, C.curr_code,
195 f_get_gross_amount_after_promo_discount((B.qty_dlv_so*C.gross_sell_price), E.promo_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code)) AS gross_amount_after_disc,
196 vEmptyAmount, (B.qty_dlv_so*C.gross_sell_price) - f_get_gross_amount_after_promo_discount((B.qty_dlv_so*C.gross_sell_price), E.promo_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code)) AS total_promo_disc_amount,
197 D.regular_discount_percentage, vEmptyAmount, vEmptyAmount, vEmptyAmount,
198 vEmptyAmount, vEmptyAmount, vEmptyAmount, vEmptyAmount, vEmptyAmount, vEmptyAmount, vEmptyId,
199 D.add_discount_percentage, D.add_discount_amount, vEmptyAmount, vEmptyAmount
200 FROM sl_do A
201 INNER JOIN sl_do_item B ON A.do_id = B.do_id
202 INNER JOIN sl_so_item C ON B.ref_id = C.so_item_id
203 INNER JOIN sl_so D ON C.so_id = D.so_id
204 INNER JOIN sl_so_item_additional_for_dlg E ON E.so_item_id = C.so_item_id
205 WHERE A.do_id = vDoId;
206
207 /*
208 * - Hitung total gross amount after promo discount
209 * - Hitung total regular discount amount
210 * - Hitung prorate regular disc amount untuk masing2 item
211 */
212 WITH hitung_total_reg_disc AS(
213 SELECT A.tenant_id, A.ref_id AS do_id, SUM(A.gross_after_disc) AS total_gross_after_disc,
214 ROUND(( SUM(A.gross_after_disc) * A.regular_disc_percentage)/100, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, A.curr_code)) AS total_reg_disc_amount
215 FROM tt_calculate_amount_so A
216 WHERE A.session_id = pSessionId
217 GROUP BY A.tenant_id, A.ref_id, A.regular_disc_percentage, A.curr_code
218 )
219 UPDATE tt_calculate_amount_so A
220 SET total_gross_after_disc = B.total_gross_after_disc,
221 total_reg_disc_amount = B.total_reg_disc_amount,
222 reg_disc_amount = ROUND(( A.gross_after_disc * B.total_reg_disc_amount)/B.total_gross_after_disc, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, A.curr_code))
223 FROM hitung_total_reg_disc B
224 WHERE A.session_id = pSessionId
225 AND A.tenant_id = B.tenant_id
226 AND A.ref_id = B.do_id;
227
228 /*
229 * Perhitungan regular discount amount
230 *
231 * Cari selisih : (total regular disc amount - SUM (regular disc amount hasil prorate) )
232 * Jika selisih regular disc amount <> 0, tambahkan ke item yang regular disc amountnya paling besar
233 */
234 WITH selisih_regular_disc_amount AS (
235 -- Mencari selisih regular disc amount
236 SELECT ref_id, (total_reg_disc_amount - SUM(reg_disc_amount)) AS selisih_reg_disc
237 FROM tt_calculate_amount_so
238 WHERE session_id = pSessionId
239 AND ref_id = vDoId
240 GROUP BY ref_id, total_reg_disc_amount
241 HAVING (total_reg_disc_amount - SUM(reg_disc_amount)) <> vEmptyAmount
242
243 ), item_max_regular_discount AS (
244 -- Persiapkan item dengan regular disc amount paling besar untuk diupdate nilai selisih regular disc amount
245 SELECT A.ref_id, A.ref_item_id, A.reg_disc_amount, B.selisih_reg_disc
246 FROM tt_calculate_amount_so A
247 INNER JOIN selisih_regular_disc_amount B ON A.ref_id = B.ref_id
248 WHERE A.session_id = pSessionId
249 AND A.ref_id = vDoId
250 ORDER BY A.reg_disc_amount DESC
251 LIMIT 1
252 )
253 -- Update selisih regular discount amount
254 UPDATE tt_calculate_amount_so A
255 SET reg_disc_amount = A.reg_disc_amount + B.selisih_reg_disc
256 FROM item_max_regular_discount B
257 WHERE A.session_id = pSessionId
258 AND A.ref_id = B.ref_id
259 AND A.ref_item_id = B.ref_item_id;
260
261 /*
262 * Hitung dan update nilai (di table tt_calculate_amount_so):
263 * - gross amount after regular discount
264 */
265 UPDATE tt_calculate_amount_so
266 SET gross_after_regular_disc = gross_after_disc - reg_disc_amount
267 WHERE session_id = pSessionId
268 AND ref_id = vDoId;
269
270 /*
271 * - Hitung ulang total_add_disc_amount jika bentuknya persentasi
272 * - Hitung prorate add disc amount untuk masing2 item
273 */
274 WITH hitung_total_add_disc AS(
275 SELECT A.tenant_id, A.ref_id AS do_id, SUM(A.gross_after_regular_disc) AS total_gross_after_regular_disc,
276 CASE WHEN A.add_disc_percentage > vEmptyAmount THEN ROUND(( SUM(A.gross_after_regular_disc) * A.add_disc_percentage)/100, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, A.curr_code))
277 ELSE A.total_add_disc_amount END AS total_add_disc_amount
278 FROM tt_calculate_amount_so A
279 WHERE A.session_id = pSessionId
280 GROUP BY A.tenant_id, A.ref_id, A.add_disc_percentage, A.curr_code, A.total_add_disc_amount
281 )
282 UPDATE tt_calculate_amount_so A
283 SET total_add_disc_amount = B.total_add_disc_amount,
284 add_disc_amount = ROUND(( A.gross_after_regular_disc * B.total_add_disc_amount)/B.total_gross_after_regular_disc, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, A.curr_code))
285 FROM hitung_total_add_disc B
286 WHERE A.session_id = pSessionId
287 AND A.tenant_id = B.tenant_id
288 AND A.ref_id = B.do_id;
289
290 /*
291 * Perhitungan additional discount amount
292 *
293 * Cari selisih : (total additional disc amount - SUM (additional disc amount hasil prorate) )
294 * Jika selisih additional disc amount <> 0, tambahkan ke item yang additional disc amountnya paling besar
295 */
296 WITH selisih_add_disc_amount AS (
297 -- Mencari selisih additional disc amount
298 SELECT ref_id, (total_add_disc_amount - SUM(add_disc_amount)) AS selisih_add_disc
299 FROM tt_calculate_amount_so
300 WHERE session_id = pSessionId
301 AND ref_id = vDoId
302 GROUP BY ref_id, total_add_disc_amount
303 HAVING (total_add_disc_amount - SUM(add_disc_amount)) <> vEmptyAmount
304
305 ), item_max_additional_discount AS (
306 -- Persiapkan item dengan additional disc amount paling besar untuk diupdate nilai selisih additional disc amount
307 SELECT A.ref_id, A.ref_item_id, A.add_disc_amount, B.selisih_add_disc
308 FROM tt_calculate_amount_so A
309 INNER JOIN selisih_add_disc_amount B ON A.ref_id = B.ref_id
310 WHERE A.session_id = pSessionId
311 AND A.ref_id = vDoId
312 ORDER BY A.add_disc_amount DESC
313 LIMIT 1
314 )
315 -- Update selisih additional discount amount
316 UPDATE tt_calculate_amount_so A
317 SET add_disc_amount = A.add_disc_amount + B.selisih_add_disc
318 FROM item_max_additional_discount B
319 WHERE A.session_id = pSessionId
320 AND A.ref_id = B.ref_id
321 AND A.ref_item_id = B.ref_item_id;
322
323 /*
324 * Hitung dan update nilai (di table tt_calculate_amount_so):
325 * - gross amount after additional discount
326 * - tax amount
327 * - nett amount
328 * - tax price
329 * - nett price
330 */
331 UPDATE tt_calculate_amount_so
332 SET gross_after_add_disc = gross_after_regular_disc - add_disc_amount,
333 tax_amount = f_get_tax_amount_after_discount((gross_after_regular_disc - add_disc_amount), flg_tax_amount, tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, curr_code)),
334 nett_amount = f_get_dpp_after_discount((gross_after_regular_disc - add_disc_amount), flg_tax_amount, f_get_tax_amount_after_discount((gross_after_regular_disc - add_disc_amount), flg_tax_amount, tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, curr_code))),
335 tax_price = f_get_tax_amount_after_discount((gross_after_regular_disc - add_disc_amount), flg_tax_amount, tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, curr_code))/qty,
336 nett_price = f_get_dpp_after_discount((gross_after_regular_disc - add_disc_amount), flg_tax_amount, f_get_tax_amount_after_discount((gross_after_regular_disc - add_disc_amount), flg_tax_amount, tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, curr_code)))/qty
337 WHERE session_id = pSessionId
338 AND ref_id = vDoId;
339
340 /*
341 * Hitung dan update nilai (di table tt_calculate_amount_so):
342 * - price so
343 * - item amount
344 */
345 UPDATE tt_calculate_amount_so
346 SET price_so = ROUND((nett_amount+total_promo_disc_amount+reg_disc_amount+add_disc_amount)/qty, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, curr_code)),
347 item_amount= (nett_amount+total_promo_disc_amount+reg_disc_amount+add_disc_amount)
348 WHERE session_id = pSessionId
349 AND ref_id = vDoId;
350
351
352 /*
353 * Buat data sl_so_balance_invoice
354 */
355 INSERT INTO sl_so_balance_invoice(
356 tenant_id, ou_id, partner_id, so_id, ref_doc_type_id, ref_id,
357 ref_doc_no, ref_doc_date, ref_item_id, qty_dlv_so, so_uom_id, curr_code,
358 price_so, item_amount, flg_invoice, invoice_id, regular_disc_amount,
359 promo_disc_amount,
360 adj_regular_disc_amount, adj_promo_disc_amount, "version",
361 create_datetime, create_user_id, update_datetime, update_user_id)
362 SELECT A.tenant_id, A.ou_id, A.partner_id, A.so_id, A.ref_doc_type_id, A.ref_id,
363 A.ref_doc_no, A.ref_doc_date, A.ref_item_id, A.qty, A.so_uom_id, A.curr_code,
364 A.price_so, A.item_amount, vFlagInvoice, vEmptyId, A.reg_disc_amount,
365 CASE WHEN B.promo_sales_id = vEmptyId THEN A.add_disc_amount
366 ELSE A.total_promo_disc_amount END,
367 0, 0, 0,
368 vDatetime, vUserId, vDatetime, vUserId
369 FROM tt_calculate_amount_so A
370 INNER JOIN sl_so_additional_for_dlg B ON A.so_id = B.so_id
371 WHERE session_id = pSessionId
372 AND ref_id = vDoId;
373
374
375 /*
376 * Buat data sl_so_balance_invoice_tax
377 */
378 INSERT INTO sl_so_balance_invoice_tax(
379 tenant_id, ou_id, partner_id, so_id, ref_doc_type_id, ref_id,
380 ref_item_id, tax_id, flg_amount, tax_percentage, curr_code, base_amount,
381 tax_amount, flg_invoice, invoice_id,
382 "version", create_datetime, create_user_id, update_datetime, update_user_id)
383 SELECT A.tenant_id, A.ou_id, A.partner_id, A.so_id, A.ref_doc_type_id, A.ref_id,
384 A.ref_item_id, A.tax_id, B.flg_amount, A.tax_percentage, A.curr_code, A.item_amount,
385 A.tax_amount, vFlagInvoice, vEmptyId,
386 0, vDatetime, vUserId, vDatetime, vUserId
387 FROM tt_calculate_amount_so A
388 INNER JOIN m_tax B ON A.tax_id = B.tax_id
389 WHERE A.session_id = pSessionId
390 AND A.ref_id = vDoId;
391
392 /*
393 * buat data log product balance stock
394 * ref item id = do_product_id
395 */
396 IF EXISTS(SELECT 1 FROM m_product_assembly A, sl_do B, sl_do_item C, sl_do_product D WHERE B.do_id = vDoId AND B.do_id = C.do_id AND C.do_item_id = D.do_item_id AND C.product_id = A.parent_product_id) THEN
397
398 INSERT INTO in_log_product_balance_stock
399 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
400 product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
401 "version", create_datetime, create_user_id, update_datetime, update_user_id)
402 SELECT D.tenant_id, G.ou_id, D.doc_type_id, D.do_id, D.doc_no, D.doc_date, D.partner_ship_to_id,
403 A.child_product_id, E.product_balance_id, D.warehouse_id, E.product_status, F.base_uom_id, A.qty_base_uom * B.qty_dlv_int * -1,
404 0, vDatetime, vUserId, vDatetime, vUserId
405 FROM m_product_assembly A
406 INNER JOIN sl_do_product B ON B.product_id = A.parent_product_id
407 INNER JOIN sl_do_item C ON C.do_item_id = B.do_item_id
408 INNER JOIN sl_do D ON D.do_id = C.do_id
409 INNER JOIN in_product_balance_stock E ON E.tenant_id = A.tenant_id AND E.product_id = A.child_product_id AND E.warehouse_id = D.warehouse_id
410 INNER JOIN m_product F ON F.product_id = A.child_product_id
411 INNER JOIN m_warehouse_ou G ON D.warehouse_id = G.warehouse_id
412 WHERE D.do_id = vDoId;
413
414 /*update in_product_balance_stock_reserved untuk product asembly*/
415 UPDATE in_product_balance_stock_reserved SET
416 ou_id=D.ou_id,
417 tenant_id=D.tenant_id,
418 warehouse_id=D.warehouse_id,
419 product_id=D.child_product_id,
420 qty=qty-D.child_qty,
421 update_datetime = vDatetime,
422 update_user_id = vUserId,
423 version=version+1
424 FROM (SELECT H.ou_id,A.tenant_id,A.warehouse_id,F.child_product_id,SUM(B.qty_dlv_int*F.qty_base_uom) AS child_qty
425 FROM sl_do A
426 JOIN sl_do_item B ON A.do_id=B.do_id
427 JOIN m_product_assembly F on B.product_id=F.parent_product_id
428 JOIN m_warehouse_ou H ON H.warehouse_id = A.warehouse_id
429 WHERE A.Do_id=vDoId
430 GROUP BY H.ou_id,A.tenant_id,A.warehouse_id,F.child_product_id ) AS D
431 WHERE in_product_balance_stock_reserved.ou_id=D.ou_id AND
432 in_product_balance_stock_reserved.tenant_id=D.tenant_id AND
433 in_product_balance_stock_reserved.product_id=D.child_product_id AND
434 in_product_balance_stock_reserved.warehouse_id=D.warehouse_id;
435
436 /*insert in_log_product_balance_stock_reserved*/
437 INSERT INTO in_log_product_balance_stock_reserved(
438 ou_id,
439 tenant_id,
440 warehouse_id,
441 product_id,
442 base_uom_id,
443 qty,
444 doc_type_id,
445 ref_id,
446 doc_no,
447 doc_date,
448 partner_id,
449 create_datetime,
450 create_user_id,
451 update_datetime,
452 update_user_id,
453 version)
454 SELECT H.ou_id,
455 A.tenant_id,
456 A.warehouse_id,
457 F.child_product_id,
458 G.base_uom_id,
459 B.qty_dlv_int*F.qty_base_uom*-1,
460 vDeliveryOrderDocTypeId,
461 vSoId,
462 A.doc_no,
463 A.doc_date,
464 C.partner_id,
465 vDatetime,
466 vUserId,
467 vDatetime,
468 vUserId,
469 0
470 FROM sl_do A
471 JOIN sl_do_item B ON A.do_id=B.do_id
472 JOIN sl_so C ON A.ref_id=C.so_id
473 JOIN m_product_assembly F on B.product_id=F.parent_product_id
474 JOIN m_product G on B.product_id=G.product_id
475 JOIN m_warehouse_ou H ON H.warehouse_id = A.warehouse_id
476 WHERE A.do_id=vDoId;
477 ELSE
478
479 INSERT INTO in_log_product_balance_stock
480 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
481 product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
482 "version", create_datetime, create_user_id, update_datetime, update_user_id)
483 SELECT A.tenant_id, D.ou_id, A.doc_type_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
484 C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.base_uom_id, SUM(C.qty_dlv_int) * -1,
485 0, vDatetime, vUserId, vDatetime, vUserId
486 FROM sl_do A, sl_do_item B, sl_do_product C, m_warehouse_ou D
487 WHERE A.do_id = vDoId AND
488 A.do_id = B.do_id AND
489 B.do_item_id = C.do_item_id AND
490 A.warehouse_id = D.warehouse_id
491 GROUP BY A.tenant_id, D.ou_id, A.doc_type_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
492 C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.base_uom_id;
493
494 /*update in_product_balance_stock_reserved*/
495 UPDATE in_product_balance_stock_reserved SET
496 ou_id=D.ou_id,
497 tenant_id=D.tenant_id,
498 warehouse_id=D.warehouse_id,
499 product_id=D.product_id,
500 qty=qty-D.qty_dlv_int,
501 update_datetime = vDatetime,
502 update_user_id = vUserId,
503 version=version+1
504 FROM (SELECT H.ou_id,A.tenant_id,A.warehouse_id,B.product_id,B.qty_dlv_int
505 FROM sl_do A
506 JOIN sl_do_item B ON A.do_id=B.do_id
507 JOIN m_warehouse_ou H ON H.warehouse_id = A.warehouse_id
508 WHERE A.do_id=vDoId) AS D
509 WHERE in_product_balance_stock_reserved.ou_id=D.ou_id AND
510 in_product_balance_stock_reserved.tenant_id=D.tenant_id AND
511 in_product_balance_stock_reserved.product_id=D.product_id AND
512 in_product_balance_stock_reserved.warehouse_id=D.warehouse_id;
513
514 /*insert in_log_product_balance_stock_reserved*/
515 INSERT INTO in_log_product_balance_stock_reserved(
516 ou_id,
517 tenant_id,
518 warehouse_id,
519 product_id,
520 base_uom_id,
521 qty,
522 doc_type_id,
523 ref_id,
524 doc_no,
525 doc_date,
526 partner_id,
527 create_datetime,
528 create_user_id,
529 update_datetime,
530 update_user_id,
531 version)
532 SELECT H.ou_id,
533 A.tenant_id,
534 A.warehouse_id,
535 B.product_id,
536 B.base_uom_id,
537 B.qty_dlv_int*-1,
538 vDeliveryOrderDocTypeId,
539 vSoId,
540 A.doc_no,
541 A.doc_date,
542 C.partner_id,
543 vDatetime,
544 vUserId,
545 vDatetime,
546 vUserId,
547 0
548 FROM sl_do A
549 JOIN sl_do_item B ON A.do_id=B.do_id
550 JOIN sl_so C on A.ref_id=C.so_id
551 JOIN m_warehouse_ou H ON H.warehouse_id = A.warehouse_id
552 WHERE A.do_id=vDoId;
553
554 END IF;
555
556 /*
557 * add data balance do item yang akan digunakan di inventory untuk pembuatan return note,
558 * saat akan membuat return note
559 */
560 INSERT INTO in_balance_do_item
561 (do_item_id, tenant_id, ou_id, do_id, doc_no, doc_date, partner_id,
562 so_id, so_no, so_date, so_item_id,
563 qty_dlv, qty_return, so_uom_id, qty_dlv_int,
564 qty_return_int, base_uom_id, status_item,
565 "version", create_datetime, create_user_id, update_datetime, update_user_id)
566 SELECT B.do_item_id, A.tenant_id, A.ou_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
567 A.ref_id, C.doc_no, C.doc_date, B.ref_id,
568 SUM(B.qty_dlv_so), 0, B.so_uom_id, SUM(B.qty_dlv_int),
569 0, B.base_uom_id, vStatusRelease,
570 0, vDatetime, vUserId, vDatetime, vUserId
571 FROM sl_do A, sl_do_item B, sl_so C
572 WHERE A.do_id = vDoId AND
573 A.do_id = B.do_id AND
574 A.ref_id = C.so_id
575 GROUP BY B.do_item_id, A.tenant_id, A.ou_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
576 A.ref_id, C.doc_no, C.doc_date, B.ref_id, B.so_uom_id, B.base_uom_id;
577
578 --kembalikan status_item so yg tidak di buat DO, dan statusnya I
579 UPDATE sl_so_balance_item Z SET
580 status_item = vStatusRelease,
581 update_datetime = vDatetime,
582 update_user_id = vUserId,
583 version = Z.version+1
584 FROM sl_so_item A
585 WHERE Z.so_item_id = A.so_item_id AND
586 Z.tenant_id = A.tenant_id AND
587 A.so_id = vSoId AND
588 Z.status_item = vStatusInProgress AND
589 NOT EXISTS (SELECT 1
590 FROM sl_do_item B
591 WHERE B.do_id = vDoId AND
592 B.ref_id = Z.so_item_id AND
593 B.tenant_id = Z.tenant_id);
594
595 UPDATE sl_so_balance_item Z SET
596 status_item = vStatusRelease,
597 qty_dlv = Z.qty_dlv + A.qty_dlv_so,
598 qty_dlv_int = Z.qty_dlv_int + A.qty_dlv_int,
599 update_datetime = vDatetime,
600 update_user_id = vUserId,
601 version = Z.version+1
602 FROM sl_do_item A
603 WHERE Z.so_item_id = A.ref_id AND
604 Z.tenant_id = A.tenant_id AND
605 A.do_id = vDoId;
606-- sl_so_balance_item.qty_so - sl_so_balance_item.qty_cancel + sl_so_balance_item.qty_add - sl_so_balance_item.qty_dlv > 0;
607
608 UPDATE sl_so_balance_item SET status_item = vStatusFinal, update_datetime = vDatetime, update_user_id = vUserId
609 FROM sl_do_item A
610 WHERE sl_so_balance_item.so_item_id = A.ref_id AND
611 sl_so_balance_item.tenant_id = A.tenant_id AND
612 A.do_id = vDoId AND
613 sl_so_balance_item.qty_so - sl_so_balance_item.qty_cancel + sl_so_balance_item.qty_add - sl_so_balance_item.qty_dlv <= 0;
614
615 -- update in_so_balance_for_rgto untuk mengembalikan nilai flg_rekap
616 UPDATE in_so_balance_for_rgto A SET
617 flg_rekap = CASE WHEN A.rgto_id = vEmptyId THEN vNo ELSE vYes END,
618 update_datetime = vDatetime,
619 update_user_id = vUserId,
620 version = A.version + 1
621 FROM sl_do B
622 WHERE A.so_id = B.ref_id AND
623 B.ref_doc_type_id = vSoDocTypeId AND
624 B.do_id = vDoId;
625
626
627 SELECT COUNT(1) INTO vUnfinishedItem
628 FROM sl_so_balance_item A, sl_so_item B
629 WHERE A.so_item_id = B.so_item_id AND
630 B.so_id = vSoId AND
631 A.status_item = vStatusRelease;
632
633 -- Jika item nya sudah di DO semua
634 IF vUnfinishedItem = 0 THEN
635 UPDATE sl_so SET status_doc = vStatusFinal
636 WHERE so_id = vSoId;
637
638 -- Jika hanya sebagian yang di DO
639 ELSE
640
641 -- Set remark untuk Cancel SO
642 SELECT 'Automatic Modify Item SO from undelivered items of SO : '||A.doc_no INTO vRemarkModifyItemSo
643 FROM sl_so A
644 WHERE A.so_id = vSoId;
645
646 -- Get role id, flg user role awe submit DO
647 SELECT role_id, flg_user_role INTO vRoleId, vFlgUserRole
648 FROM awe_historydoc
649 WHERE scheme = vSchemeDo
650 AND doc_id = vDoId
651 AND activity = 'SUBMIT';
652
653 -- Ambil autonum id Cancel SO
654 SELECT A.process_parameter_value::bigint INTO vAutonumIdModifyItemSo
655 FROM t_process_parameter A
656 WHERE A.process_message_id = vProcessId AND
657 A.process_parameter_key = 'autonumIdModifyItemSo';
658
659 -- Ambil doc no Cancel SO
660 SELECT A.process_parameter_value INTO vDocNoModifyItemSo
661 FROM t_process_parameter A
662 WHERE A.process_message_id = vProcessId AND
663 A.process_parameter_key = 'autonumModifyItemSo';
664
665 -- Buatkan modify item SO ( APPROVED ), Function akan mengembalikan cancel so id yang akan di gunakan di pembuatan SO baru
666 SELECT sl_automatic_modify_item_so(pSessionId, pTenantId, vUserId, vRoleId, vFlgUserRole, vDatetime, vSoId, vOuId, vAutonumIdModifyItemSo, vDocNoModifyItemSo, vDocDate, vRemarkModifyItemSo) INTO vModifyItemSoId;
667
668 IF EXISTS (select 1 from sl_do_additional_for_dlg WHERE do_id = vDoId AND flg_auto_so = vYes) THEN
669 -- Ambil autonum id SO
670 SELECT A.process_parameter_value::bigint INTO vAutonumIdSo
671 FROM t_process_parameter A
672 WHERE A.process_message_id = vProcessId AND
673 A.process_parameter_key = 'autonumIdSo';
674
675 -- Ambil doc no SO baru
676 SELECT A.process_parameter_value INTO vDocNoNewSo
677 FROM t_process_parameter A
678 WHERE A.process_message_id = vProcessId AND
679 A.process_parameter_key = 'autonumSo';
680
681 -- Buatkan SO baru ( APPROVED )
682 PERFORM sl_automatic_so(pSessionId, pTenantId, vUserId, vRoleId, vFlgUserRole, vDatetime, vSoId, vAutonumIdSo, vDocNoNewSo, vDocDate, vModifyItemSoId);
683 ELSE
684 UPDATE autonum_generated SET flg_unused = vYes WHERE autonum_generated_id = vAutonumIdSo;
685 END IF;
686 END IF;
687
688
689
690 /*
691 * membuat data transaksi jurnal :
692 * 1. buat admin
693 * 2. buat temlate jurnal
694 */
695
696 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)
697 FROM sl_do A
698 WHERE A.do_id = vDoId;
699
700 SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
701
702 INSERT INTO gl_journal_trx
703 (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
704 ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
705 ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
706 "version", create_datetime, create_user_id, update_datetime, update_user_id)
707 SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.do_id, A.doc_no, A.doc_date,
708 (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_ship_to_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
709 A.ref_doc_type_id, A.ref_id, A.doc_date, B.curr_code, A.remark, vStatusDraft, 'DRAFT',
710 0, vDatetime, vUserId, vDatetime, vUserId
711 FROM sl_do A, sl_so B
712 WHERE A.do_id = vDoId AND
713 A.ref_doc_type_id = B.doc_type_id AND
714 A.ref_id = B.so_id;
715
716 INSERT INTO tt_journal_trx_item
717 (session_id, tenant_id, journal_trx_id, line_no,
718 ref_doc_type_id, ref_id,
719 partner_id, product_id, cashbank_id, ou_rc_id,
720 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
721 coa_id, curr_code, qty, uom_id,
722 amount, journal_date, type_rate,
723 numerator_rate, denominator_rate, journal_desc, remark)
724 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
725 A.doc_type_id, B.do_item_id,
726 A.partner_ship_to_id, B.product_id, vEmptyId, vEmptyId,
727 vEmptyId, vSignCredit, vProductCOA, vEmptyId,
728 f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_dlv_int, B.base_uom_id,
729 0, A.doc_date, vTypeRate,
730 1, 1, 'PRODUCT_STOCK', B.remark
731 FROM sl_do A, sl_do_item B, sl_so_item C
732 WHERE A.do_id = vDoId AND
733 A.do_id = B.do_id AND
734 B.ref_id = C.so_item_id;
735
736 INSERT INTO gl_journal_trx_item
737 (tenant_id, journal_trx_id, line_no,
738 ref_doc_type_id, ref_id,
739 partner_id, product_id, cashbank_id, ou_rc_id,
740 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
741 coa_id, curr_code, qty, uom_id,
742 amount, journal_date, type_rate,
743 numerator_rate, denominator_rate, journal_desc, remark,
744 "version", create_datetime, create_user_id, update_datetime, update_user_id,
745 ou_branch_id, ou_sub_bu_id)
746 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
747 A.ref_doc_type_id, A.ref_id,
748 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
749 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
750 A.coa_id, A.curr_code, A.qty, A.uom_id,
751 A.amount, A.journal_date, A.type_rate,
752 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
753 0, vDatetime, vUserId, vDatetime, vUserId,
754 (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
755 FROM tt_journal_trx_item A
756 WHERE A.session_id = pSessionId;
757
758 INSERT INTO gl_journal_trx_mapping
759 (tenant_id, journal_trx_id, line_no,
760 ref_doc_type_id, ref_id,
761 partner_id, product_id, cashbank_id, ou_rc_id,
762 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
763 coa_id, curr_code, qty, uom_id,
764 amount, journal_date, type_rate,
765 numerator_rate, denominator_rate, journal_desc, remark,
766 "version", create_datetime, create_user_id, update_datetime, update_user_id)
767 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
768 vEmptyId, vEmptyId,
769 vEmptyId, vEmptyId, vEmptyId, vEmptyId,
770 vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
771 f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
772 0, A.journal_date, A.type_rate,
773 1, 1, 'COGS', vEmptyValue,
774 0, vDatetime, vUserId, vDatetime, vUserId
775 FROM tt_journal_trx_item A
776 WHERE A.session_id = pSessionId
777 GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate;
778
779 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
780 DELETE FROM tt_calculate_amount_so WHERE session_id = pSessionId;
781
782END;
783$BODY$
784 LANGUAGE plpgsql VOLATILE
785 COST 100;
786 /