· 6 years ago · Apr 16, 2019, 07:14 AM
1-- Function: sl_submit_do(bigint, character varying, character varying)
2
3-- DROP FUNCTION sl_submit_do(bigint, character varying, character varying);
4
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 vStatusRelease character varying(1);
20 vStatusDraft character varying(1);
21 vStatusFinal character varying(1);
22 vEmptyValue character varying(1);
23 vProductStatus character varying(5);
24 vSignDebit character varying(1);
25 vSignCredit character varying(1);
26 vTypeRate character varying(3);
27 vProductCOA character varying(10);
28 vSystemCOA character varying(10);
29 vSoId bigint;
30 vUnfinishedItem bigint;
31 vParentOuId bigint;
32 vJournalTrxId bigint;
33 vJournalType character varying(20);
34 vTypeSob character varying(10);
35
36 vDocJournal DOC_JOURNAL%ROWTYPE;
37 vOuStructure OU_BU_STRUCTURE%ROWTYPE;
38 result RECORD;
39
40 vDeliveryOrderDocTypeId bigint;
41 vRoundingModeNonTax character varying(5);
42 vDigitDppRoundingTax integer;
43
44 vSOBDocTypeId bigint;
45 vSequenceName character varying;
46 vMaxLineNo bigint;
47
48 vOriginalAmount numeric := 0;
49 vFinalAmount numeric := 0;
50 vDataId character varying(20) := 'VALIDATE-BRAND';
51 vTagKeyMou character varying(3) := 'MOU';
52 vMouId bigint := -99;
53 vReturnValue character varying := 'RETURN';
54 vCursorForDoProduct sl_do_product%ROWTYPE;
55 vCursorForProduct tt_update_product_balance_stock_for_do%ROWTYPE;
56 vTempTotal numeric := 0;
57 vQty numeric;
58 vQtyMinus numeric := 0;
59 vProductBalanceId bigint;
60 vResult character varying;
61 vFlgNo character varying := 'N';
62 vFlgYes character varying := 'Y';
63 vDocTypeLogBook bigint := 440;
64 vLogBookType character varying(5);
65 vFlgDelivery character varying(1);
66 vBelumCetak character varying(2);
67 vZero numeric := 0;
68 vOriginalAmountTax numeric := 0;
69 vFinalAmountTax numeric := 0;
70
71 vEmptyAmount numeric := 0;
72 vDOKonsinyasiDocTypeId bigint := 352;
73
74BEGIN
75
76 vFlagInvoice := 'N';
77 vEmptyId := -99;
78 vStatusRelease := 'R';
79 vStatusDraft := 'D';
80 vStatusFinal := 'F';
81 vEmptyValue := ' ';
82 vProductStatus := 'GOOD';
83 vSignDebit := 'D';
84 vSignCredit := 'C';
85 vTypeRate := 'COM';
86 vProductCOA := 'PRODUCT';
87 vSystemCOA := 'SYSTEM';
88 vUnfinishedItem := 0;
89 vFlgDelivery := 'Y';
90 vBelumCetak := 'BC';
91
92 vDeliveryOrderDocTypeId = 311;
93 vSOBDocTypeId = 398;
94
95 SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
96 SELECT f_get_value_system_config_by_param_code(pTenantId, 'dpp.rounding.decimal.tax')::integer INTO vDigitDppRoundingTax;
97
98 --create Fake Sequence for line No, will be deleted later
99 vSequenceName := 'fake_line_no_seq_'||replace(pSessionId, '-', '');
100 EXECUTE 'CREATE SEQUENCE '||vSequenceName||
101 ' INCREMENT 1
102 MINVALUE 0
103 MAXVALUE 1000
104 START 0
105 CACHE 1';
106
107 SELECT A.process_message_id INTO vProcessId
108 FROM t_process_message A
109 WHERE A.tenant_id = pTenantId AND
110 A.process_name = 'sl_submit_do' AND
111 A.process_no = pProcessNo;
112
113 SELECT CAST(A.process_parameter_value AS bigint) INTO vDoId
114 FROM t_process_parameter A
115 WHERE A.process_message_id = vProcessId AND
116 A.process_parameter_key = 'doId';
117
118 SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
119 FROM t_process_parameter A
120 WHERE A.process_message_id = vProcessId AND
121 A.process_parameter_key = 'userId';
122
123 SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
124 FROM t_process_parameter A
125 WHERE A.process_message_id = vProcessId AND
126 A.process_parameter_key = 'datetime';
127
128 -- DELETE table temp
129 DELETE FROM tt_coin_detail_brand WHERE session_id = pSessionId;
130 DELETE FROM tt_coin_summary WHERE session_id = pSessionId;
131 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
132 DELETE FROM tt_update_product_balance_stock_for_do WHERE session_id = pSessionId;
133 DELETE FROM tt_data_product_verification WHERE session_id = pSessionId;
134
135
136
137
138 SELECT A.ref_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
139 FROM sl_do A
140 WHERE A.do_id = vDoId INTO result;
141
142 vSoId := result.ref_id;
143 vOuStructure := result.ou;
144 vDocJournal := result.doc;
145 --lock data do
146 PERFORM 1 FROM sl_do WHERE do_id = vDoId FOR UPDATE;
147 PERFORM 1 FROM sl_do_item WHERE do_id = vDoId FOR UPDATE;
148
149 --lock data so
150 PERFORM 1 FROM sl_so WHERE so_id = vSoId FOR UPDATE;
151 PERFORM 1 FROM sl_so_item WHERE so_id = vSoId FOR UPDATE;
152 PERFORM 1 FROM sl_so_balance_item A
153 WHERE EXISTS ( SELECT 1 FROM sl_so_item B
154 WHERE B.so_item_id = A.so_item_id AND
155 B.so_id = vSoId ) FOR UPDATE;
156 PERFORM 1 FROM sl_so_brand_item WHERE so_id = vSoId FOR UPDATE;
157
158 --lock data so balance invoice
159 PERFORM 1 FROM sl_so_balance_invoice
160 WHERE ref_id = vDoId AND
161 ref_doc_type_id = vDeliveryOrderDocTypeId AND
162 so_id = vSoId;
163
164 -- GET flg_type_so
165 SELECT flg_type_so INTO vTypeSob
166 FROM sl_so_info
167 WHERE so_id = vSoId;
168
169 --update status doc sl_do
170 UPDATE sl_do
171 SET status_doc = vStatusRelease,
172 version = version + 1,
173 update_datetime = vDatetime,
174 update_user_id = vUserId
175 WHERE do_id = vDoId;
176
177 --update sl_so_item & sl_so_balance_item untuk item-item yang memang sudah existing sejak SO & Settlement
178 UPDATE sl_so_item A
179 SET qty_so = C.qty_dlv_so,
180 qty_int = C.qty_dlv_int,
181 version = A.version + 1,
182 update_datetime = vDatetime,
183 update_user_id = vUserId,
184 tax_amount = f_get_tax_amount_after_discount(f_get_gross_amount_after_discount(A.gross_sell_price, A.flg_disc, A.discount_percentage, A.discount_amount, C.qty_dlv_int, 0), A.flg_tax_amount, A.tax_percentage, 0),
185 nett_item_amount = f_get_dpp_after_discount(f_get_gross_amount_after_discount(A.gross_sell_price, A.flg_disc, A.discount_percentage, A.discount_amount, C.qty_dlv_int, 0), A.flg_tax_amount, f_get_tax_amount_after_discount(f_get_gross_amount_after_discount(A.gross_sell_price, A.flg_disc, A.discount_percentage, A.discount_amount, C.qty_dlv_int, 0), A.flg_tax_amount, A.tax_percentage, 0)),
186 tax_price = f_get_tax_amount_after_discount(f_get_gross_amount_after_discount(A.gross_sell_price, A.flg_disc, A.discount_percentage, A.discount_amount, C.qty_dlv_int, 0), A.flg_tax_amount, A.tax_percentage, 0)/C.qty_dlv_int,
187 nett_sell_price = f_get_dpp_after_discount(f_get_gross_amount_after_discount(A.gross_sell_price, A.flg_disc, A.discount_percentage, A.discount_amount, C.qty_dlv_int, 0), A.flg_tax_amount, f_get_tax_amount_after_discount(f_get_gross_amount_after_discount(A.gross_sell_price, A.flg_disc, A.discount_percentage, A.discount_amount, C.qty_dlv_int, 0), A.flg_tax_amount, A.tax_percentage, 0))/C.qty_dlv_int,
188 discount_amount = ((C.qty_dlv_int*A.gross_sell_price) - f_get_gross_amount_after_discount(A.gross_sell_price, A.flg_disc, A.discount_percentage, A.discount_amount, C.qty_dlv_int, 0))/C.qty_dlv_int
189 FROM sl_do B
190 INNER JOIN sl_do_item C ON B.do_id = C.do_id
191 WHERE B.do_id = vDoId
192 AND A.so_item_id = C.ref_id;
193
194 UPDATE sl_so_balance_item A
195 SET qty_so = C.qty_dlv_so,
196 qty_dlv = C.qty_dlv_so,
197 qty_so_int = C.qty_dlv_int,
198 qty_dlv_int = C.qty_dlv_int,
199 status_item = vStatusFinal,
200 version = A.version + 1,
201 update_datetime = vDatetime,
202 update_user_id = vUserId
203 FROM sl_do B
204 INNER JOIN sl_do_item C ON B.do_id = C.do_id
205 WHERE B.do_id = vDoId
206 AND A.so_item_id = C.ref_id;
207
208 --delete sl_so_item & sl_so_balance_item untuk item-item yang sudah tidak existing saat DO
209 WITH deleted_so_item AS (
210 DELETE FROM sl_so_item
211 WHERE so_id = vSoId
212 AND so_item_id NOT IN (SELECT ref_id FROM sl_do_item WHERE do_id = vDoId)
213 RETURNING *
214 )
215 DELETE FROM sl_so_balance_item
216 WHERE so_item_id IN (SELECT so_item_id FROM deleted_so_item);
217
218 --Get Max line no SO Item
219 SELECT MAX(line_no) INTO vMaxLineNo
220 FROM sl_so_item
221 WHERE so_id = vSoId;
222
223 --update fake sequence
224 PERFORM setval(vSequenceName, vMaxLineNo, true);
225
226 --insert sl_so_item & sl_so_balance_item untuk item-item yang baru muncul saat DO
227 WITH inserted_so_item AS (
228 INSERT INTO sl_so_item (
229 tenant_id, so_id, line_no, ref_doc_type_id, ref_id,
230 partner_ship_to_id, partner_ship_address_id, product_id, qty_so,
231 so_uom_id, qty_int, base_uom_id, curr_code, gross_sell_price,
232 flg_tax_amount, tax_id, tax_percentage,
233 tax_price, promo_code,
234 discount_percentage, discount_amount,
235 nett_sell_price,
236 tax_amount,
237 nett_item_amount,
238 eta, tolerance_dlv_qty, remark, eta_day, flg_disc,
239 version, create_datetime, create_user_id, update_datetime, update_user_id)
240 SELECT A.tenant_id, C.so_id, nextval(vSequenceName) AS line_no, vEmptyId, vEmptyId,
241 C.partner_ship_to_id, C.partner_ship_address_id, B.product_id, B.qty_dlv_so,
242 B.so_uom_id, B.qty_dlv_int, B.base_uom_id, D.curr_code, D.gross_sell_price,
243 D.flg_tax_amount, D.tax_id, D.tax_percentage,
244 f_get_tax_amount_after_discount(f_get_gross_amount_after_discount(D.gross_sell_price, D.flg_disc, D.discount_percentage, D.discount_amount, B.qty_dlv_int, 0), D.flg_tax_amount, D.tax_percentage, 0)/B.qty_dlv_int AS tax_price, '' AS promo_code,
245 D.discount_percentage, ((B.qty_dlv_int*D.gross_sell_price) - f_get_gross_amount_after_discount(D.gross_sell_price, D.flg_disc, D.discount_percentage, D.discount_amount, B.qty_dlv_int, 0))/B.qty_dlv_int AS discount_amount,
246 f_get_dpp_after_discount(f_get_gross_amount_after_discount(D.gross_sell_price, D.flg_disc, D.discount_percentage, D.discount_amount, B.qty_dlv_int, 0), D.flg_tax_amount, f_get_tax_amount_after_discount(f_get_gross_amount_after_discount(D.gross_sell_price, D.flg_disc, D.discount_percentage, D.discount_amount, B.qty_dlv_int, 0), D.flg_tax_amount, D.tax_percentage, 0))/B.qty_dlv_int AS nett_sell_price,
247 f_get_tax_amount_after_discount(f_get_gross_amount_after_discount(D.gross_sell_price, D.flg_disc, D.discount_percentage, D.discount_amount, B.qty_dlv_int, 0), D.flg_tax_amount, D.tax_percentage, 0) AS tax_amount,
248 f_get_dpp_after_discount(f_get_gross_amount_after_discount(D.gross_sell_price, D.flg_disc, D.discount_percentage, D.discount_amount, B.qty_dlv_int, 0), D.flg_tax_amount, f_get_tax_amount_after_discount(f_get_gross_amount_after_discount(D.gross_sell_price, D.flg_disc, D.discount_percentage, D.discount_amount, B.qty_dlv_int, 0), D.flg_tax_amount, D.tax_percentage, 0)) AS nett_item_amount,
249 C.doc_date, 0, '', '', D.flg_disc,
250 0, vDatetime, vUserId, vDatetime, vUserId
251 FROM sl_do A
252 INNER JOIN sl_do_item B ON A.do_id = B.do_id
253 INNER JOIN sl_so C ON A.ref_id = C.so_id
254 INNER JOIN sl_so_brand_item D ON C.tenant_id = D.tenant_id AND C.so_id = D.so_id AND f_get_brand_by_product_id(B.product_id) = D.brand_id
255 WHERE A.do_id = vDoId
256 AND B.ref_id = vEmptyId
257 RETURNING *
258 ), inserted_so_balance_item AS (
259 INSERT INTO sl_so_balance_item (
260 so_item_id, tenant_id, ou_id, qty_so, qty_dlv, qty_return, qty_cancel, qty_add, so_uom_id,
261 qty_so_int, qty_dlv_int, qty_return_int, qty_cancel_int, qty_add_int, base_uom_id,
262 tolerance_dlv_qty, status_item, version, create_datetime, create_user_id, update_datetime, update_user_id)
263 SELECT A.so_item_id, A.tenant_id, B.ou_id, A.qty_so, A.qty_so, 0, 0, 0, A.so_uom_id,
264 A.qty_int, A.qty_int, 0, 0, 0, A.base_uom_id,
265 A.tolerance_dlv_qty, vStatusFinal, 0, vDatetime, vUserId, vDatetime, vUserId
266 FROM inserted_so_item A
267 INNER JOIN sl_so B ON A.so_id = B.so_id
268 )
269 UPDATE sl_do_item A
270 SET ref_id = B.so_item_id,
271 ref_doc_type_id = vEmptyId
272 FROM inserted_so_item B
273 WHERE A.do_id = vDoId
274 AND B.product_id = A.product_id;
275
276 --update so tax
277 WITH summary_tax AS (
278 SELECT A.tenant_id, A.so_id, A.tax_id, B.flg_amount, A.tax_percentage, B.tax_name,
279 SUM(f_get_dpp_after_discount(f_get_gross_amount_after_discount(A.gross_sell_price, A.flg_disc, A.discount_percentage, A.discount_amount, A.qty_so, 0), A.flg_tax_amount, f_get_tax_amount_after_discount(f_get_gross_amount_after_discount(A.gross_sell_price, A.flg_disc, A.discount_percentage, A.discount_amount, A.qty_so, 0), A.flg_tax_amount, A.tax_percentage, 0))) AS base_amount,
280 SUM(f_get_tax_amount_after_discount(f_get_gross_amount_after_discount(A.gross_sell_price, A.flg_disc, A.discount_percentage, A.discount_amount, A.qty_so, 0), A.flg_tax_amount, A.tax_percentage, 0)) AS tax_amount
281 FROM sl_so_item A
282 INNER JOIN m_tax B ON A.tax_id = B.tax_id
283 WHERE A.so_id = vSoId AND
284 A.tax_id <> vEmptyId
285 GROUP BY A.tenant_id, A.so_id, A.tax_id, B.flg_amount, A.tax_percentage, B.tax_name, A.curr_code
286 )
287 UPDATE sl_so_tax A
288 SET base_amount = B.base_amount,
289 tax_amount = B.tax_amount
290 FROM summary_tax B
291 WHERE A.tenant_id = B.tenant_id
292 AND A.so_id = B.so_id
293 AND A.tax_id = B.tax_id;
294
295 --add sl_log_so_balance_item (initial log & final log)
296 INSERT INTO sl_log_so_balance_item (
297 tenant_id, so_id, so_item_id, ref_doc_type_id, ref_id, ref_item_id,
298 qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
299 version, create_datetime, create_user_id, update_datetime, update_user_id)
300 SELECT A.tenant_id, A.so_id, A.so_item_id, vEmptyId, vEmptyId, vEmptyId,
301 A.qty_so, A.so_uom_id, A.qty_int, A.base_uom_id, A.remark,
302 0, vDatetime, vUserId, vDatetime, vUserId
303 FROM sl_so_item A
304 WHERE A.so_id = vSoId;
305
306 INSERT INTO sl_log_so_balance_item (
307 tenant_id, so_id, so_item_id, ref_doc_type_id, ref_id, ref_item_id,
308 qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
309 version, create_datetime, create_user_id, update_datetime, update_user_id)
310 SELECT A.tenant_id, C.so_id, C.so_item_id, A.doc_type_id, A.do_id, B.do_item_id,
311 B.qty_dlv_so * -1, B.so_uom_id, B.qty_dlv_int *-1, B.base_uom_id, B.remark,
312 0, vDatetime, vUserId, vDatetime, vUserId
313 FROM sl_do A
314 INNER JOIN sl_do_item B ON A.do_id = B.do_id
315 INNER JOIN sl_so_item C ON B.ref_id = C.so_item_id
316 WHERE A.do_id = vDoId;
317
318 IF (vTypeSob = 'CON') THEN
319
320 INSERT INTO in_borrowed_product_balance_stock
321 (tenant_id, ou_id, partner_id, partner_ship_address_id,
322 product_id, product_balance_id, product_status,
323 base_uom_id, qty,
324 "version", create_datetime, create_user_id, update_datetime, update_user_id)
325 SELECT A.tenant_id, A.ou_id, D.partner_ship_to_id, D.partner_ship_address_id,
326 B.product_id, C.product_balance_id, B.product_status,
327 B.base_uom_id, 0,
328 0, vDatetime, vUserId, vDatetime, vUserId
329 FROM sl_do A
330 INNER JOIN sl_do_item B ON A.do_id = B.do_id
331 INNER JOIN sl_do_product C ON B.do_item_id = C.do_item_id
332 INNER JOIN sl_so D ON A.ref_id = D.so_id
333 WHERE A.do_id = vDoId AND
334 NOT EXISTS (
335 SELECT 1
336 FROM in_borrowed_product_balance_stock Z
337 WHERE A.tenant_id = Z.tenant_id AND
338 A.ou_id = Z.ou_id AND
339 D.partner_ship_to_id = Z.partner_id AND
340 D.partner_ship_address_id = Z.partner_ship_address_id AND
341 B.product_id = Z.product_id AND
342 C.product_balance_id = Z.product_balance_id AND
343 B.product_status = Z.product_status
344 );
345
346 UPDATE in_borrowed_product_balance_stock E SET
347 qty = E.qty + C.qty_dlv_int,
348 version = E.version + 1,
349 update_datetime = vDateTime,
350 update_user_id = vUserId
351 FROM sl_do A
352 INNER JOIN sl_do_item B ON A.do_id = B.do_id
353 INNER JOIN sl_do_product C ON B.do_item_id = C.do_item_id
354 INNER JOIN sl_so D ON A.ref_id = D.so_id
355 WHERE A.do_id = vDoId AND
356 A.tenant_id = E.tenant_id AND
357 A.ou_id = E.ou_id AND
358 D.partner_ship_to_id = E.partner_id AND
359 D.partner_ship_address_id = E.partner_ship_address_id AND
360 B.product_id = E.product_id AND
361 C.product_balance_id = E.product_balance_id AND
362 B.product_status = E.product_status;
363
364 INSERT INTO in_log_borrowed_product_balance_stock
365 (tenant_id, ou_id, doc_type_id, ref_id,
366 doc_no, doc_date, partner_id, partner_ship_address_id,
367 product_id, product_balance_id, product_status,
368 base_uom_id, qty,
369 "version", create_datetime, create_user_id, update_datetime, update_user_id)
370 SELECT A.tenant_id, A.ou_id, vDOKonsinyasiDocTypeId, A.do_id,
371 A.doc_no, A.doc_date, D.partner_ship_to_id, D.partner_ship_address_id,
372 B.product_id, C.product_balance_id, B.product_status,
373 B.base_uom_id, C.qty_dlv_int,
374 0, vDatetime, vUserId, vDatetime, vUserId
375 FROM sl_do A
376 INNER JOIN sl_do_item B ON A.do_id = B.do_id
377 INNER JOIN sl_do_product C ON B.do_item_id = C.do_item_id
378 INNER JOIN sl_so D ON A.ref_id = D.so_id
379 WHERE A.do_id = vDoId;
380
381 INSERT INTO in_inventory_borrow_balance_item
382 (inventory_item_id, tenant_id, inventory_id,
383 partner_id, partner_ship_address_id, ou_id,
384 qty_do, qty_return, qty_sales, due_date, base_uom_id, status_item,
385 "version", create_datetime, create_user_id, update_datetime, update_user_id)
386 SELECT B.do_item_id, A.tenant_id, A.do_id,
387 D.partner_ship_to_id, D.partner_ship_address_id, A.ou_id,
388 SUM(C.qty_dlv_int) AS qty_dlv_int, 0, 0, D.due_date, B.base_uom_id, vStatusRelease,
389 0, vDatetime, vUserId, vDatetime, vUserId
390 FROM sl_do A
391 INNER JOIN sl_do_item B ON A.do_id = B.do_id
392 INNER JOIN sl_do_product C ON B.do_item_id = C.do_item_id
393 INNER JOIN sl_so D ON A.ref_id = D.so_id
394 WHERE A.do_id = vDoId
395 GROUP BY B.do_item_id, A.tenant_id, A.do_id, D.partner_ship_to_id, D.partner_ship_address_id, A.ou_id,
396 D.due_date, B.base_uom_id;
397 ELSE
398
399 --add sl_so_balance_invoice
400 WITH insert_balance AS (
401 SELECT A.tenant_id, A.ou_id, D.partner_bill_to_id, A.ref_id,
402 A.doc_type_id, A.do_id, A.doc_no, A.doc_date, B.do_item_id, B.qty_dlv_so, B.so_uom_id,
403 C.curr_code, C.nett_sell_price, C.flg_tax_amount, C.tax_percentage,
404 (B.qty_dlv_so * C.gross_sell_price) AS gross_amount,
405 f_get_gross_amount_after_discount(C.gross_sell_price, C.flg_disc, C.discount_percentage, C.discount_amount, B.qty_dlv_so, 0) AS gross_after_disc
406 FROM sl_do A
407 INNER JOIN sl_do_item B ON A.do_id = B.do_id
408 INNER JOIN sl_so_item C ON B.ref_id = C.so_item_id
409 INNER JOIN sl_so D ON C.so_id = D.so_id
410 WHERE A.do_id = vDoId
411 )
412 INSERT INTO sl_so_balance_invoice (
413 tenant_id, ou_id, partner_id, so_id,
414 ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_dlv_so, so_uom_id,
415 curr_code, price_so, item_amount, flg_invoice, invoice_id,
416 regular_disc_amount, promo_disc_amount, adj_regular_disc_amount, adj_promo_disc_amount,
417 version, create_datetime, create_user_id, update_datetime, update_user_id)
418 SELECT tenant_id, ou_id, partner_bill_to_id, ref_id,
419 doc_type_id, do_id, doc_no, doc_date, do_item_id, qty_dlv_so, so_uom_id,
420 curr_code, nett_sell_price,
421 f_get_dpp_after_discount(gross_after_disc, flg_tax_amount, f_get_tax_amount_after_discount(gross_after_disc, flg_tax_amount, tax_percentage, 0)) + (gross_amount - gross_after_disc),
422 vFlagInvoice, vEmptyId,
423 (gross_amount - gross_after_disc), 0, 0, 0,
424 0, vDatetime, vUserId, vDatetime, vUserId
425 FROM insert_balance;
426
427 --add sl_so_balance_invoice_tax
428 WITH insert_balance_tax AS (
429 SELECT A.tenant_id, A.ou_id, E.partner_bill_to_id, A.ref_id,
430 A.doc_type_id, A.do_id, B.do_item_id, C.tax_id, D.flg_amount,
431 C.tax_percentage, C.curr_code, C.flg_tax_amount,
432 (B.qty_dlv_so * C.gross_sell_price) AS gross_amount,
433 f_get_gross_amount_after_discount(C.gross_sell_price, C.flg_disc, C.discount_percentage, C.discount_amount, B.qty_dlv_so, 0) AS gross_after_disc
434 FROM sl_do A
435 INNER JOIN sl_do_item B ON A.do_id = B.do_id
436 INNER JOIN sl_so_item C ON B.ref_id = C.so_item_id
437 INNER JOIN m_tax D ON C.tax_id = D.tax_id
438 INNER JOIN sl_so E ON C.so_id = E.so_id
439 WHERE A.do_id = vDoId
440 )
441 INSERT INTO sl_so_balance_invoice_tax (
442 tenant_id, ou_id, partner_id, so_id,
443 ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
444 tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
445 version, create_datetime, create_user_id, update_datetime, update_user_id)
446 SELECT tenant_id, ou_id, partner_bill_to_id, ref_id,
447 doc_type_id, do_id, do_item_id, tax_id, flg_amount,
448 tax_percentage, curr_code,
449 f_get_dpp_after_discount(gross_after_disc, flg_tax_amount, f_get_tax_amount_after_discount(gross_after_disc, flg_tax_amount, tax_percentage, 0)) + (gross_amount - gross_after_disc),
450 f_get_tax_amount_after_discount(gross_after_disc, flg_tax_amount, tax_percentage, 0), vFlagInvoice, vEmptyId,
451 0, vDatetime, vUserId, vDatetime, vUserId
452 FROM insert_balance_tax;
453
454 END IF;
455
456 /*
457 * mengurangi reserved stock
458 * JIKA product tsb masuk verification, berarti berdasar qty verification
459 * JIKA TIDAK, berarti berdasar qty settlement
460 */
461 /* Masukan daftar product do, jika dilakukan verification */
462 INSERT INTO tt_data_product_verification(
463 session_id, tenant_id, ou_id, product_id, qty_verify)
464 SELECT pSessionId, A.tenant_id, A.ou_id, C.product_id, C.qty_verify
465 FROM sl_do A
466 INNER JOIN in_verification_header B ON A.ref_id = B.doc_id AND B.doc_type_id = vSOBDocTypeId
467 INNER JOIN in_verification_item C ON B.verification_id = C.verification_id
468 WHERE A.do_id = vDoId;
469
470 /* Mengurangi reserved stock berdasarkan qty verification (jika ada dilakukan verification) */
471 UPDATE in_product_balance_stock_reserved A
472 SET qty = A.qty - B.qty_verify,
473 update_datetime = vDatetime,
474 update_user_id = vUserId,
475 version = A.version + 1
476 FROM tt_data_product_verification B
477 WHERE B.session_id = pSessionId AND
478 A.tenant_id = B.tenant_id AND
479 A.ou_id = B.ou_id AND
480 A.product_id = B.product_id AND
481 A.product_status = 'GOOD';
482
483 /* Mengurangi reserved stock berdasarkan qty settlement (jika tidak ada dilakukan verification) */
484 UPDATE in_product_balance_stock_reserved A
485 SET qty = A.qty - D.qty_settlement,
486 update_datetime = vDatetime,
487 update_user_id = vUserId,
488 version = A.version + 1
489 FROM sl_do B
490 INNER JOIN sl_so_settlement C ON B.ref_id = C.ref_id
491 INNER JOIN sl_so_settlement_item D ON C.so_settlement_id = D.so_settlement_id
492 INNER JOIN sl_so_item E ON B.ref_id = E.so_id AND D.product_id = E.product_id
493 INNER JOIN sl_so_balance_item F ON E.so_item_id = F.so_item_id
494 WHERE A.tenant_id = B.tenant_id AND
495 A.ou_id = B.ou_id AND
496 A.product_id = D.product_id AND
497 A.product_status = 'GOOD' AND
498 B.do_id = vDoId AND
499 D.qty_settlement > 0 AND
500 F.status_item = vStatusFinal AND
501 NOT EXISTS (SELECT 1 FROM tt_data_product_verification X
502 WHERE X.session_id = pSessionId AND
503 X.tenant_id = A.tenant_id AND
504 X.ou_id = A.ou_id AND
505 X.product_id = A.product_id);
506
507 /**
508 * ===================================================
509 * Potong Stock berdasarkan qty DO & warehouse priority
510 * ===================================================
511 */
512
513 --ambil data stock per gudang beserta prioritas gudang
514 INSERT INTO tt_update_product_balance_stock_for_do (
515 session_id, warehouse_id, product_id, qty, qty_minus, product_balance_id,
516 warehouse_priority, product_status)
517 SELECT pSessionId, A.warehouse_id, A.product_id, SUM(A.qty), 0 AS qty_minus, A.product_balance_id,
518 CASE WHEN A.product_status = vReturnValue THEN '0' ELSE D.priority_no END, A.product_status
519 FROM in_product_balance_stock A
520 INNER JOIN sl_do_product E on A.product_balance_id = E.product_balance_id
521 INNER JOIN sl_do_item B on E.do_item_id = B.do_item_id
522 INNER JOIN m_warehouse C on A.warehouse_id = C.warehouse_Id
523 INNER JOIN temp_warehouse_priority D on D.whs_id = C.warehouse_id
524 WHERE B.do_id = vDoId
525 AND A.product_status IN (vProductStatus, vReturnValue)
526 GROUP BY A.product_id, D.priority_no, A.warehouse_id, A.product_balance_id, A.product_status;
527
528 -- melengkapi data stok yg tidak ada di in_product_balance_stock
529 INSERT INTO tt_update_product_balance_stock_for_do (
530 session_id, warehouse_id, product_id, qty, qty_minus,
531 product_balance_id, warehouse_priority, product_status)
532 SELECT pSessionId, C.whs_id, B.product_id, 0, 0,
533 B.product_balance_id, C.priority_no, vProductStatus
534 FROM sl_do_item A, sl_do_product D, in_product_balance B, temp_warehouse_priority C
535 WHERE A.do_id = vDoId AND
536 A.do_item_id = D.do_item_id AND
537 D.product_balance_id = B.product_balance_id AND
538 C.priority_no = '1' AND
539 C.ctgr_product_id = f_get_ctgr_product_by_product_id(B.product_id) AND
540 NOT EXISTS (SELECT 1 FROM tt_update_product_balance_stock_for_do D
541 WHERE D.session_id = pSessionId AND
542 A.product_id = D.product_id AND
543 B.product_balance_id = D.product_balance_id AND
544 C.whs_id = D.warehouse_id AND
545 D.product_status = vProductStatus);
546
547 --mengisi qty minus berdasarkan urutan prioritas
548 FOR vCursorForDoProduct IN
549 SELECT A.*
550 FROM sl_do_product A
551 INNER JOIN sl_do_item B ON A.do_item_id = B.do_item_id
552 WHERE B.do_id = vDoId
553 LOOP
554 vQty = vCursorForDoProduct.qty_dlv_int;
555 vProductBalanceId = vCursorForDoProduct.product_balance_id;
556 vTempTotal = vQty;
557 FOR vCursorForProduct IN
558 SELECT * FROM tt_update_product_balance_stock_for_do
559 WHERE session_id = pSessionId
560 AND product_balance_id = vProductBalanceId
561 ORDER BY warehouse_priority
562 LOOP
563
564 IF vTempTotal = 0 THEN
565 EXIT;
566 ELSEIF vCursorForProduct.qty > 0 AND vTempTotal > vCursorForProduct.qty THEN
567 vQtyMinus = vCursorForProduct.qty;
568 UPDATE tt_update_product_balance_stock_for_do
569 SET qty_minus = vQtyMinus
570 WHERE session_id = pSessionId
571 AND product_balance_id = vProductBalanceId
572 AND id = vCursorForProduct.id;
573 vTempTotal = vTempTotal - vCursorForProduct.qty;
574
575 ELSEIF vCursorForProduct.qty > 0 THEN
576
577 vQtyMinus = vTempTotal;
578 RAISE NOTICE '%',vCursorForProduct.id;
579 UPDATE tt_update_product_balance_stock_for_do
580 SET qty_minus = vQtyMinus
581 WHERE session_id = pSessionId
582 AND product_balance_id = vProductBalanceId
583 AND id = vCursorForProduct.id;
584 vTempTotal :=0;
585 END IF;
586
587 END LOOP;
588 IF vTempTotal > 0 THEN
589 UPDATE tt_update_product_balance_stock_for_do
590 SET qty_minus = qty_minus + vTempTotal
591 WHERE session_id = pSessionId AND
592 product_balance_id = vProductBalanceId AND
593 warehouse_priority = '1' AND
594 product_status = vProductStatus;
595 END IF;
596 END LOOP;
597
598 --potong stock berdasarkan qty minus
599 UPDATE in_product_balance_stock A
600 SET qty = A.qty - B.qty_minus,
601 update_datetime = vDatetime,
602 update_user_id = vUserId,
603 version = a.version + 1
604 FROM tt_update_product_balance_stock_for_do B
605 WHERE B.session_id = pSessionId AND
606 B.qty_minus > 0 AND
607 A.product_id = B.product_id AND
608 A.warehouse_id = B.warehouse_id AND
609 A.product_balance_id = B.product_balance_id AND
610 A.product_status = B.product_status;
611
612 INSERT INTO in_product_balance_stock
613 (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
614 version, create_datetime, create_user_id, update_datetime, update_user_id)
615 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,
616 0,vDatetime, vUserId, vDatetime , vUserId
617 FROM tt_update_product_balance_stock_for_do A
618 INNER JOIN m_product B ON A.product_id = B.product_id
619 WHERE A.session_id = pSessionId AND
620 A.qty_minus > 0 AND
621 NOT EXISTS (SELECT 1 FROM in_product_balance_stock B
622 WHERE A.product_id = B.product_id AND
623 A.warehouse_id = B.warehouse_id AND
624 A.product_balance_id = B.product_balance_id AND
625 A.product_status = B.product_status);
626
627 /*
628 * buat data log product balance stock
629 * ref item id = do_product_id
630 *
631 * Mod by henik, 29 Des 2017
632 * Nilai doc_type_id untuk DO atas SOB Konsinyasi diisi doc_type_id DO Konsinyasi (352), terkait KAT-249
633 */
634 INSERT INTO in_log_product_balance_stock (
635 tenant_id, ou_id, doc_type_id,
636 ref_id, doc_no, doc_date,
637 partner_id, product_id, warehouse_id, product_balance_id,
638 product_status, base_uom_id, qty,
639 version, create_datetime, create_user_id, update_datetime, update_user_id)
640 SELECT A.tenant_id, A.ou_id, CASE WHEN vTypeSob = 'CON' THEN vDOKonsinyasiDocTypeId ELSE A.doc_type_id END,
641 A.do_id, A.doc_no, A.doc_date,
642 A.partner_ship_to_id, E.product_id, E.warehouse_id, E.product_balance_id,
643 E.product_status, f_get_base_uom_by_product_id(E.product_id), SUM(E.qty_minus) * -1,
644 0, vDatetime, vUserId, vDatetime, vUserId
645 FROM sl_do A
646 INNER JOIN sl_do_item B ON A.do_id = B.do_id
647 INNER JOIN sl_do_product C ON B.do_item_id = C.do_item_id
648 INNER JOIN tt_update_product_balance_stock_for_do E ON C.product_balance_id = E.product_balance_id
649 WHERE A.do_id = vDoId
650 AND E.session_id = pSessionId
651 AND E.qty_minus > 0
652 GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.do_id, A.doc_no, A.doc_date,
653 A.partner_ship_to_id, E.product_id, E.product_balance_id, E.warehouse_id, E.product_status;
654
655 /*
656 * add data balance do item yang akan digunakan di inventory untuk pembuatan return note,
657 * saat akan membuat return note
658 */
659 INSERT INTO in_balance_do_item (
660 do_item_id, tenant_id, ou_id, do_id, doc_no, doc_date, partner_id,
661 so_id, so_no, so_date, so_item_id,
662 qty_dlv, qty_return, so_uom_id, qty_dlv_int,
663 qty_return_int, base_uom_id, status_item,
664 version, create_datetime, create_user_id, update_datetime, update_user_id)
665 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,
666 A.ref_id, C.doc_no, C.doc_date, B.ref_id,
667 SUM(B.qty_dlv_so), 0, B.so_uom_id, SUM(B.qty_dlv_int),
668 0, B.base_uom_id, vStatusRelease,
669 0, vDatetime, vUserId, vDatetime, vUserId
670 FROM sl_do A
671 INNER JOIN sl_do_item B ON A.do_id = B.do_id
672 INNER JOIN sl_so C ON A.ref_id = C.so_id
673 WHERE A.do_id = vDoId
674 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,
675 A.ref_id, C.doc_no, C.doc_date, B.ref_id, B.so_uom_id, B.base_uom_id;
676
677 --update status sl_so menjadi Final.
678 UPDATE sl_so
679 SET status_doc = vStatusFinal
680 WHERE so_id = vSoId;
681
682 -- get amount so
683 WITH data_brand_item_so AS (
684 SELECT A.flg_tax_amount, A.tax_percentage, A.nett_brand_amount,
685 (A.qty * A.gross_sell_price) AS gross_amount,
686 f_get_gross_amount_after_discount(A.gross_sell_price, A.flg_disc, A.discount_percentage, A.discount_amount, A.qty, 0) AS gross_after_disc
687 FROM sl_so_brand_item A
688 WHERE A.so_id = vSoId
689 )
690 SELECT SUM(nett_brand_amount) AS nett_brand_amount,
691 SUM(f_get_tax_amount_after_discount(gross_after_disc, flg_tax_amount, tax_percentage, 0)) AS tax_amount
692 INTO vOriginalAmount, vOriginalAmountTax
693 FROM data_brand_item_so;
694
695 --tandai untuk brand-brand yg qty nya berubah antara SO original dan DO
696 /** UPDATE tt_validate_brand_so
697 SET flg_change_qty = 'Y'
698 WHERE qty_do <> qty_so;
699 */
700
701 /*
702 * --Cek apakah perlu membuat SO baru, buat SO Jika nilai QTY per brand "bergerak"
703 IF EXISTS(SELECT 1 FROM tt_validate_brand_so WHERE session_id = pSessionId AND data_id = vDataId AND flg_change_qty = 'Y') THEN
704
705 -- BUAT DOC SO OTOMATIS DENEGAN STATUS DRAFT
706 SELECT sl_automatic_create_so_by_brand(pSessionId, pTenantId, vUserId, vDatetime, vDoId, vSoId) INTO vResult;
707
708 END IF;
709 */
710
711 --update qty brand item SO
712 WITH summary_item_per_brand AS (
713 SELECT A.brand_id, COALESCE(SUM(B.qty_int),0) AS qty, A.flg_tax_amount, A.tax_percentage,
714 (COALESCE(SUM(B.qty_int),0) * A.gross_sell_price) AS gross_amount,
715 f_get_gross_amount_after_discount(A.gross_sell_price, A.flg_disc, A.discount_percentage, A.discount_amount, COALESCE(SUM(B.qty_int),0), 0) AS gross_after_disc
716 FROM sl_so_brand_item A
717 LEFT JOIN sl_so_item B ON A.brand_id = f_get_brand_by_product_id(B.product_id)
718 AND A.so_id = B.so_id
719 WHERE A.so_id = vSoId
720 GROUP BY A.brand_id, A.flg_tax_amount, A.tax_percentage, A.gross_sell_price, A.flg_disc, A.discount_percentage, A.discount_amount
721 )
722 UPDATE sl_so_brand_item A
723 SET qty = B.qty,
724 nett_brand_amount = f_get_dpp_after_discount(B.gross_after_disc, B.flg_tax_amount, f_get_tax_amount_after_discount(B.gross_after_disc, B.flg_tax_amount, B.tax_percentage, 0)),
725 nett_sell_price = CASE WHEN B.qty <> vZero THEN f_get_dpp_after_discount(B.gross_after_disc, B.flg_tax_amount, f_get_tax_amount_after_discount(B.gross_after_disc, B.flg_tax_amount, B.tax_percentage, 0))/B.qty ELSE vZero END ,
726 tax_price = CASE WHEN B.qty <> vZero THEN f_get_tax_amount_after_discount(B.gross_after_disc, B.flg_tax_amount, B.tax_percentage, 0)/B.qty ELSE vZero END,
727 discount_amount = CASE WHEN B.qty <> vZero THEN (B.gross_amount - B.gross_after_disc)/B.qty ELSE vZero END,
728 version = A.version+1,
729 update_user_id = vUserId,
730 update_datetime = vDatetime
731 FROM summary_item_per_brand B
732 WHERE A.so_id = vSoId
733 AND A.brand_id = B.brand_id;
734
735 -- GET AMOUNT setelah do
736 SELECT SUM(A.item_amount-A.regular_disc_amount) INTO vFinalAmount
737 FROM sl_so_balance_invoice A
738 WHERE A.tenant_id = pTenantId
739 AND A.ref_id = vDoId
740 AND A.ref_doc_type_id = vDeliveryOrderDocTypeId;
741
742 SELECT SUM(A.tax_amount) INTO vFinalAmountTax
743 FROM sl_so_balance_invoice_tax A
744 WHERE A.tenant_id = pTenantId
745 AND A.ref_id = vDoId
746 AND A.ref_doc_type_id = vDeliveryOrderDocTypeId;
747
748 --GET MOU ID
749 SELECT tag_doc_id INTO vMouId
750 FROM sl_so_tagging
751 WHERE so_id = vSoId
752 AND tag_key = vTagKeyMou;
753
754 -- POTONG MOU jika nilai amount per SO "bergerak", UPDATE TABLE BALANCE MOU JIKA TAG KE MOU
755 IF (vMouId != -99 AND (vOriginalAmount + vOriginalAmountTax) <> (vFinalAmount + vFinalAmountTax)) THEN
756
757 -- update balance
758 UPDATE sl_mou_balance
759 SET usage_balance = usage_balance - ((vOriginalAmount + vOriginalAmountTax)-(vFinalAmount + vFinalAmountTax)),
760 version = version + 1,
761 update_datetime = vDatetime,
762 update_user_id = vUserId
763 WHERE mou_id = vMouId;
764
765 -- insert ke balance log
766 INSERT INTO sl_mou_balance_log(
767 tenant_id, ou_id, mou_id, ref_doc_type_id,
768 ref_id, ref_doc_no, ref_doc_date, partner_id,
769 curr_code, amount, remark,
770 create_datetime, create_user_id, update_datetime, update_user_id, version)
771 SELECT tenant_id, ou_id, vMouId, doc_type_id,
772 so_id, doc_no, doc_date, partner_id,
773 curr_code, (vOriginalAmount-vFinalAmount), CASE WHEN (vOriginalAmount < vFinalAmount) THEN 'Pengurangan sisa amount dari SO Revisi' ELSE 'Penambahan sisa amount dari SO Revisi' END AS remark,
774 vDatetime, vUserId, vDatetime, vUserId, 0
775 FROM sl_so
776 WHERE so_id = vSoId;
777
778 -- insert ke balance log - TAX
779 INSERT INTO sl_mou_balance_log(
780 tenant_id, ou_id, mou_id, ref_doc_type_id,
781 ref_id, ref_doc_no, ref_doc_date, partner_id,
782 curr_code, amount, remark,
783 create_datetime, create_user_id, update_datetime, update_user_id, version)
784 SELECT tenant_id, ou_id, vMouId, doc_type_id,
785 so_id, doc_no, doc_date, partner_id,
786 curr_code, (vOriginalAmountTax-vFinalAmountTax), CASE WHEN (vOriginalAmountTax < vFinalAmountTax) THEN 'Pengurangan sisa amount dari SO Revisi - TAX' ELSE 'Penambahan sisa amount dari SO Revisi - TAX' END AS remark,
787 vDatetime, vUserId, vDatetime, vUserId, 0
788 FROM sl_so
789 WHERE so_id = vSoId;
790
791 END IF;
792
793 --insert to history log SO
794 INSERT INTO sl_so_brand_item_history_log(
795 tenant_id, so_id, line_no, brand_id, data_type, qty, remark,
796 version, create_datetime, create_user_id, update_datetime, update_user_id)
797 SELECT tenant_id, so_id, line_no, brand_id, 'DO' AS data_type, qty, 'Update Qty DO' AS remark,
798 0, vDatetime, vUserId, vDatetime, vUserId
799 FROM sl_so_brand_item
800 WHERE so_id = vSoId;
801
802 INSERT INTO sl_so_item_history_log(
803 tenant_id, so_id, line_no,
804 product_id, brand_id, data_type, qty, remark,
805 version, create_datetime, create_user_id, update_datetime, update_user_id)
806 SELECT tenant_id, so_id, line_no,
807 product_id, f_get_brand_by_product_id(product_id), 'DO' AS data_type, qty_int, 'Update Qty DO' AS remark,
808 0, vDatetime, vUserId, vDatetime, vUserId
809 FROM sl_so_item
810 WHERE so_id = vSoId;
811
812 --Modify by Agik, 30 Mei 2016
813 --update data SO Status for Inquiry & insert Log
814 UPDATE sl_so_status
815 SET status_do = vFlgYes,
816 version = version + 1,
817 update_datetime = vDatetime,
818 update_user_id = vUserId
819 WHERE so_id = vSoId;
820
821 INSERT INTO sl_so_status_log (
822 so_id, state, doc_type_id, doc_id, doc_action,
823 create_datetime, create_user_id, update_datetime, update_user_id, version)
824 SELECT ref_id, 'DELIVERY' AS state, doc_type_id, do_id, 'SUBMIT' AS doc_action,
825 vDatetime, vUserId, vDatetime, vUserId, 0
826 FROM sl_do
827 WHERE do_id = vDoId;
828
829 -- Modify henik (15 juli 2016) -> for Katamata
830 -- BUAT DOC TEMPORARY SALES INVOICE -> APPROVED
831 -- Modify by congky untuk SOB dengan tipe FoC, Trial, Sample, Revisi tetap melakukan generate temporary sales invoice
832 -- Berdasarkan instruksi email dari ko david
833 IF (vTypeSob IN ('REG','FOC','TRL','SMP','RVS')) THEN
834 -- Hanya SO yang total amountnya > 0 (REGULAR) yang automatic dibuatkan document Temp Sales Invoice
835 SELECT sl_automatic_create_temporary_sales_invoice(pSessionId, pTenantId, vUserId, vDatetime, vSoId, vDoId) INTO vResult;
836 END IF;
837
838 /* Modify by Henik, 22 Juli 2016 -> for Katamata
839 * - Check Doc DO merupakan type log book apa ?
840 * - INSERT KE TABLE in_item_log_book_balance -> untuk keperluan print log book untuk barang DO
841 * */
842
843 -- Get flg delivery
844 SELECT B.flg_delivery INTO vFlgDelivery
845 FROM sl_do A
846 INNER JOIN sl_so B ON A.ref_id = B.so_id
847 WHERE A.do_id = vDoId;
848
849 -- Check log book type
850 IF (vFlgDelivery = vFlgNo) THEN
851 -- pick up (diambil sendiri)
852 vLogBookType := 'DAS';
853 ELSE
854 -- Deliver (dikirim)
855
856 IF EXISTS(SELECT 1 FROM sl_do A
857 INNER JOIN m_partner B ON A.partner_ship_to_id = B.partner_id
858 INNER JOIN in_group_log_book C ON C.group_log_book = 'PP' AND B.partner_code = C.parameter
859 WHERE A.tenant_id = pTenantId AND A.do_id = vDoId) THEN
860
861 -- type log book pelanggan
862 vLogBookType := 'PP';
863
864 ELSIF EXISTS(SELECT 1 FROM sl_do A
865 INNER JOIN m_partner_address B ON A.partner_ship_address_id = B.partner_address_id
866 INNER JOIN in_group_log_book C ON C.group_log_book = 'DK' AND B.city = C.parameter
867 WHERE A.tenant_id = pTenantId AND A.do_id = vDoId) THEN
868
869 -- type log book dalam kota
870 vLogBookType := 'DK';
871
872 ELSE
873 -- Selain yang diatas berarti type log book luar kota
874 vLogBookType := 'LK';
875 END IF;
876 END IF;
877
878 WITH data_item AS(
879 SELECT A.do_id, f_get_brand_by_product_id(C.product_id) AS brand_id,
880 f_get_brand_code(f_get_brand_by_product_id(C.product_id)) AS brand_code,
881 (SUM(C.qty_dlv_int))::character varying(30) AS qty_dlv, E.ctgr_product_id
882 FROM sl_do A
883 INNER JOIN sl_so B ON A.ref_id = B.so_id
884 INNER JOIN sl_do_item C ON A.do_id = C.do_id
885 INNER JOIN sl_so_item D ON C.ref_id = D.so_item_id AND B.so_id = D.so_id
886 INNER JOIN m_product E ON C.product_id = E.product_id
887 WHERE A.tenant_id = pTenantId
888 AND A.do_id= vDoId
889 AND A.status_doc = vStatusRelease
890 GROUP BY A.do_id, f_get_brand_by_product_id(C.product_id), E.ctgr_product_id
891 ORDER BY f_get_brand_code(f_get_brand_by_product_id(C.product_id))
892 )
893 INSERT INTO in_item_log_book_balance(
894 tenant_id, ou_id, doc_type_id, ref_doc_type_id, ref_doc_date, ref_doc_no,
895 log_book_type, partner_id, partner_ship_to_id, partner_ship_address_id, partner_city,
896 summary_product, expedition_id, courier_id,
897 receipt_doc_no, remark, log_book_id, line_no, status_item, ctgr_product_id,
898 create_datetime, create_user_id, update_datetime, update_user_id, version)
899 SELECT A.tenant_id, A.ou_id, vDocTypeLogBook, A.doc_type_id, A.doc_date, A.doc_no,
900 vLogBookType, B.partner_id, A.partner_ship_to_id, A.partner_ship_address_id, D.city,
901 string_agg(C.brand_code||':'||C.qty_dlv,', '), vEmptyId, vEmptyId,
902 vEmptyValue, vEmptyValue, vEmptyId, vEmptyId, vBelumCetak, C.ctgr_product_id,
903 vDatetime, vUserId, vDatetime, vUserId, 0
904 FROM sl_do A
905 INNER JOIN sl_so B ON A.ref_id = B.so_id
906 INNER JOIN data_item C ON A.do_id = C.do_id
907 INNER JOIN m_partner_address D ON A.partner_ship_address_id = D.partner_address_id
908 WHERE A.tenant_id = pTenantId
909 AND A.do_id= vDoId
910 AND A.status_doc = vStatusRelease
911 GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_date, A.doc_no, B.partner_id, A.partner_ship_to_id,
912 A.partner_ship_address_id, D.city, C.ctgr_product_id;
913
914 -- Modify by Henik, 22 Juli 2016 About log book END
915
916 ----------------------------------------------------------------------------------------------------------------------------------------
917 -- Modify by Henik, 08 Agustus Mengenai perhitungan coin
918
919 -- Pastikan ada pilih promo saat buat dokumen SOB, tidak perlu cek sub_promo maupun launching dulu.
920 -- Karena kunci utama perhitungan coin ada di promo, tidak pilih promo so pasti tidak ada perhitungan coin
921 IF EXISTS (SELECT 1 FROM sl_so_info A WHERE A.so_id = vSoId AND A.promo_id <> vEmptyId) THEN
922
923 -- Prepare data untuk dilakukan proses perhitungan coin yg diperoleh
924 -- Insert data Info dan qty SOB per brand yg sudah di DO ke table temp (tt_coin_detail_brand)
925 INSERT INTO tt_coin_detail_brand (
926 session_id,tenant_id, partner_id, so_id, ref_id, ref_doc_type_id, salesman_id,
927 brand_id, group_brand, qty,
928 promo_id, flg_promo_coin, sub_promo_id,
929 flg_sub_promo_coin, product_launching_id, flg_launching_coin,
930 coin_promo, coin_sub_promo, coin_launching,
931 coin_adjustment, coin_periodic_adjustment
932 )
933 SELECT pSessionId, A.tenant_id, C.partner_id, C.so_id, B.do_id, B.doc_type_id, C.salesman_id,
934 E.brand_id, E.group_brand, SUM(A.qty_dlv_int) AS qty,
935 D.promo_id, D.flg_promo_coin, D.sub_promo_id,
936 D.flg_sub_promo_coin, D.product_launching_id, D.flg_launching_coin,
937 vEmptyAmount, vEmptyAmount, vEmptyAmount,
938 vEmptyAmount, vEmptyAmount
939 FROM sl_do_item A
940 INNER JOIN sl_do B ON A.do_id = B.do_id
941 INNER JOIN sl_so C ON B.ref_id = C.so_id AND B.ref_doc_type_id = C.doc_type_id
942 INNER JOIN sl_so_info D ON C.so_id = D.so_id
943 INNER JOIN m_brand_ext E ON f_get_brand_by_product_id(A.product_id) = E.brand_id
944 WHERE A.tenant_id = pTenantId
945 AND A.do_id = vDoId
946 GROUP BY A.tenant_id, C.partner_id, C.so_id, B.do_id, B.doc_type_id, C.salesman_id,
947 E.brand_id, E.group_brand, D.promo_id, D.flg_promo_coin, D.sub_promo_id,
948 D.flg_sub_promo_coin, D.product_launching_id, D.flg_launching_coin;
949
950 -- Execute function perhitungan coin
951 PERFORM f_coin_calculation(pSessionId);
952
953 -- Update data coin promo balance jika data nya sudah ada (lihat berdasarkan partner_id dan promo_id yg sama)
954 -- Berdasarkan data di table temp tt_coin_summary hasil dari function f_coin_calculation
955 UPDATE sl_coin_promo_balance A SET
956 coin_promo = A.coin_promo + B.coin_promo,
957 coin_sub_promo = A.coin_sub_promo + B.coin_sub_promo,
958 coin_launching = A.coin_launching + B.coin_launching,
959 coin_total = A.coin_total + B.coin_total,
960 version = A.version + 1,
961 update_user_id = vUserId,
962 update_datetime = vDatetime
963 FROM tt_coin_summary B
964 WHERE B.session_id = pSessionId
965 AND A.tenant_id = B.tenant_id
966 AND A.partner_id = B.partner_id
967 AND A.promo_id = B.promo_id;
968
969 -- Insert data coin balance jika belum ada datanya
970 -- Berdasarkan data di table temp tt_coin_summary hasil dari function f_coin_calculation
971 INSERT INTO sl_coin_promo_balance (
972 tenant_id, partner_id, promo_id, coin_promo, coin_sub_promo,
973 coin_launching, coin_adjustment, coin_periodic_adjustment, coin_total,
974 create_datetime, create_user_id, update_datetime, update_user_id, version
975 )
976 SELECT A.tenant_id, A.partner_id, A.promo_id, A.coin_promo, A.coin_sub_promo,
977 A.coin_launching, A.coin_adjustment, A.coin_periodic_adjustment, A.coin_total,
978 vDatetime, vUserId, vDatetime, vUserId, 0
979 FROM tt_coin_summary A
980 WHERE session_id = pSessionId
981 AND NOT EXISTS (SELECT 1 FROM sl_coin_promo_balance B
982 WHERE A.tenant_id = B.tenant_id
983 AND A.partner_id = B.partner_id
984 AND A.promo_id = B.promo_id);
985
986 -- Insert data log coin ke table sl_log_coin_promo_balance
987 -- Berdasarkan data di table temp tt_coin_detail_brand hasil dari olahan function f_coin_calculation
988 INSERT INTO sl_log_coin_promo_balance (
989 tenant_id, partner_id, so_id, ref_id, ref_doc_type_id, promo_id, flg_promo_coin,
990 sub_promo_id, flg_sub_promo_coin, product_launching_id, flg_launching_coin,
991 brand_id, group_brand, salesman_id, qty, coin_promo, coin_sub_promo,
992 coin_launching, coin_adjustment, coin_periodic_adjustment,
993 create_datetime, create_user_id, update_datetime, update_user_id, version
994 )
995 SELECT tenant_id, partner_id, so_id, ref_id, ref_doc_type_id, promo_id, flg_promo_coin,
996 sub_promo_id, flg_sub_promo_coin, product_launching_id, flg_launching_coin,
997 brand_id, group_brand, salesman_id, qty, coin_promo, coin_sub_promo,
998 coin_launching, coin_adjustment, coin_periodic_adjustment,
999 vDatetime, vUserId, vDatetime, vUserId, 0
1000 FROM tt_coin_detail_brand A
1001 WHERE A.session_id = pSessionId
1002 ORDER BY A.brand_id;
1003
1004 END IF;
1005
1006
1007 ----------------------------------------------------------------------------------------------------------------------------------------
1008
1009 /*
1010 * Modify by Henik (29 Des 2017),
1011 * untuk DO atas SOB Konsinyasi tidak perlu buat journal terkait KAT-249
1012 */
1013 IF (vTypeSob = 'CON') THEN
1014 -- tidak ada tulis journal
1015 ELSE
1016 /*
1017 * membuat data transaksi jurnal :
1018 * 1. buat admin
1019 * 2. buat temlate jurnal
1020 */
1021
1022 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)
1023 FROM sl_do A
1024 WHERE A.do_id = vDoId;
1025
1026 SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
1027
1028 --add gl_journal_trx
1029 INSERT INTO gl_journal_trx
1030 (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
1031 ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
1032 ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
1033 "version", create_datetime, create_user_id, update_datetime, update_user_id)
1034 SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.do_id, A.doc_no, A.doc_date,
1035 (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,
1036 A.ref_doc_type_id, A.ref_id, A.doc_date, B.curr_code, A.remark, vStatusDraft, 'DRAFT',
1037 0, vDatetime, vUserId, vDatetime, vUserId
1038 FROM sl_do A, sl_so B
1039 WHERE A.do_id = vDoId AND
1040 A.ref_doc_type_id = B.doc_type_id AND
1041 A.ref_id = B.so_id;
1042
1043 INSERT INTO tt_journal_trx_item
1044 (session_id, tenant_id, journal_trx_id, line_no,
1045 ref_doc_type_id, ref_id,
1046 partner_id, product_id, cashbank_id, ou_rc_id,
1047 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
1048 coa_id, curr_code, qty, uom_id,
1049 amount, journal_date, type_rate,
1050 numerator_rate, denominator_rate, journal_desc, remark)
1051 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
1052 A.doc_type_id, B.do_item_id,
1053 A.partner_ship_to_id, B.product_id, vEmptyId, vEmptyId,
1054 vEmptyId, vSignCredit, vProductCOA, vEmptyId,
1055 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,
1056 0, A.doc_date, vTypeRate,
1057 1, 1, 'PRODUCT_STOCK', B.remark
1058 FROM sl_do A, sl_do_item B, sl_so_item C
1059 WHERE A.do_id = vDoId AND
1060 A.do_id = B.do_id AND
1061 B.ref_id = C.so_item_id;
1062
1063 --add gl_journal_trx_item
1064 INSERT INTO gl_journal_trx_item
1065 (tenant_id, journal_trx_id, line_no,
1066 ref_doc_type_id, ref_id,
1067 partner_id, product_id, cashbank_id, ou_rc_id,
1068 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
1069 coa_id, curr_code, qty, uom_id,
1070 amount, journal_date, type_rate,
1071 numerator_rate, denominator_rate, journal_desc, remark,
1072 "version", create_datetime, create_user_id, update_datetime, update_user_id)
1073 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
1074 A.ref_doc_type_id, A.ref_id,
1075 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
1076 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
1077 A.coa_id, A.curr_code, A.qty, A.uom_id,
1078 A.amount, A.journal_date, A.type_rate,
1079 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
1080 0, vDatetime, vUserId, vDatetime, vUserId
1081 FROM tt_journal_trx_item A
1082 WHERE A.session_id = pSessionId;
1083
1084 --add gl_journal_trx_mapping
1085 INSERT INTO gl_journal_trx_mapping
1086 (tenant_id, journal_trx_id, line_no,
1087 ref_doc_type_id, ref_id,
1088 partner_id, product_id, cashbank_id, ou_rc_id,
1089 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
1090 coa_id, curr_code, qty, uom_id,
1091 amount, journal_date, type_rate,
1092 numerator_rate, denominator_rate, journal_desc, remark,
1093 "version", create_datetime, create_user_id, update_datetime, update_user_id)
1094 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
1095 vEmptyId, vEmptyId,
1096 vEmptyId, vEmptyId, vEmptyId, vEmptyId,
1097 vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
1098 f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
1099 0, A.journal_date, A.type_rate,
1100 1, 1, 'COGS', vEmptyValue,
1101 0, vDatetime, vUserId, vDatetime, vUserId
1102 FROM tt_journal_trx_item A
1103 WHERE A.session_id = pSessionId
1104 GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate;
1105
1106 END IF;
1107
1108 -- DELETE table temp
1109 DELETE FROM tt_update_product_balance_stock_for_do WHERE session_id = pSessionId;
1110 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
1111 DELETE FROM tt_coin_detail_brand WHERE session_id = pSessionId;
1112 DELETE FROM tt_coin_summary WHERE session_id = pSessionId;
1113 DELETE FROM tt_data_product_verification WHERE session_id = pSessionId;
1114
1115 --delete sequence
1116 EXECUTE 'DROP SEQUENCE '||vSequenceName;
1117
1118END;
1119$BODY$
1120 LANGUAGE plpgsql VOLATILE
1121 COST 100;
1122 /