· 6 years ago · Dec 30, 2019, 05:00 AM
1/*
2 * MODIFY FOR KATAMATA : (From version 3.3.0)
3 * 1. update data balance MOU (substract) & insert log
4 * 2. insert ke table return tagging sesuai tagging SO nya
5 */
6
7CREATE OR REPLACE FUNCTION in_submit_return_note(bigint, character varying, character varying)
8 RETURNS void AS
9$BODY$
10DECLARE
11 pTenantId ALIAS FOR $1;
12 pSessionId ALIAS FOR $2;
13 pProcessNo ALIAS FOR $3;
14
15 vProcessId bigint;
16 vReturnNoteId bigint;
17 vUserId bigint;
18 vDatetime character varying(14);
19 vFlagInvoice character varying(1);
20 vEmptyId bigint;
21 vStatusRelease character varying(1);
22 vEmptyValue character varying(1);
23 vStatusFinal character varying(1);
24 vStatusDraft character varying(1);
25 vFlagYes character varying(1);
26 vFlagNo character varying(1);
27 vEmptyString character varying(1);
28 vJournalType character varying(20);
29 vSignDebit character varying(1);
30 vSignCredit character varying(1);
31 vTypeRate character varying(3);
32 vProductCOA character varying(10);
33 vSystemCOA character varying(10);
34 vParentOuId bigint;
35 vJournalTrxId bigint;
36 vTagKeyMou character varying(10);
37
38 vDocJournal DOC_JOURNAL%ROWTYPE;
39 vOuStructure OU_BU_STRUCTURE%ROWTYPE;
40 result RECORD;
41
42 vReturnNoteDocTypeId bigint;
43 vRoundingModeNonTax character varying(5);
44
45 vSoId bigint;
46 vDigitDppRoundingTax integer;
47
48 vEmptyAmount numeric := 0;
49 vFlgYes character varying(1) := 'Y';
50 vFlgNo character varying(1) := 'N';
51
52BEGIN
53
54 vFlagInvoice := 'N';
55 vEmptyId := -99;
56 vEmptyValue := ' ';
57 vStatusRelease := 'R';
58 vStatusFinal := 'F';
59 vStatusDraft := 'D';
60 vFlagYes := 'Y';
61 vFlagNo := 'N';
62 vEmptyString := ' ';
63 vSignDebit := 'D';
64 vSignCredit := 'C';
65 vTypeRate := 'COM';
66 vProductCOA := 'PRODUCT';
67 vSystemCOA := 'SYSTEM';
68 vTagKeyMou := 'MOU';
69
70 vReturnNoteDocTypeId := 502;
71 SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
72
73
74 SELECT A.process_message_id INTO vProcessId
75 FROM t_process_message A
76 WHERE A.tenant_id = pTenantId AND
77 A.process_name = 'in_submit_return_note' AND
78 A.process_no = pProcessNo;
79
80 SELECT CAST(A.process_parameter_value AS bigint) INTO vReturnNoteId
81 FROM t_process_parameter A
82 WHERE A.process_message_id = vProcessId AND
83 A.process_parameter_key = 'returnNoteId';
84
85 SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
86 FROM t_process_parameter A
87 WHERE A.process_message_id = vProcessId AND
88 A.process_parameter_key = 'userId';
89
90 SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
91 FROM t_process_parameter A
92 WHERE A.process_message_id = vProcessId AND
93 A.process_parameter_key = 'datetime';
94
95 -- DELETE table temporary
96 DELETE FROM tt_in_so_balance_item WHERE session_id = pSessionId;
97 DELETE FROM tt_coin_detail_brand WHERE session_id = pSessionId;
98 DELETE FROM tt_coin_summary WHERE session_id = pSessionId;
99
100 SELECT f_get_value_system_config_by_param_code(pTenantId, 'dpp.rounding.decimal.tax')::integer INTO vDigitDppRoundingTax;
101
102 /*
103 * 1.update status doc in_inventory
104 * 2.update data ke in_product_balance_stock / insert data ke in_product-balance_stock
105 * 3.insert data ke in_log_product_balance_stock
106 * 4.update data sl_so_balance_item
107 * 5.insert data sl_log_so_balance_item
108 * 6.insert data sl_so_balance_invoice
109 * 7.insert data sl_so_balance_invoice_tax
110 * 8.update data in_balance_do_item
111 * 9.add trx jurnal
112 */
113
114 SELECT f_get_ou_bu_structure(A.ou_from_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
115 FROM in_inventory A
116 WHERE A.inventory_id = vReturnNoteId INTO result;
117
118 vOuStructure := result.ou;
119 vDocJournal := result.doc;
120
121 UPDATE in_inventory SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
122 WHERE inventory_id = vReturnNoteId;
123
124 /*
125 * mengambil data dari in_inventory_item tanpa melakukan group by untuk ditampung ke tt_in_product_balance
126 */
127 INSERT INTO tt_in_product_balance
128 (session_id, tenant_id, inventory_id, inventory_item_id, product_balance_id, product_id, product_status,
129 serial_number, product_expired_date, product_year_made, lot_number, qty_realization, base_uom_id)
130 SELECT pSessionId, A.tenant_id, A.inventory_id, B.inventory_item_id, B.product_balance_id, B.product_id, B.product_status,
131 B.serial_number, B.product_expired_date, B.product_year_made, B.lot_number, B.qty_realization, B.base_uom_id
132 FROM in_inventory A, in_inventory_item B
133 WHERE A.inventory_id = B.inventory_id AND
134 A.inventory_id = vReturnNoteId;
135
136 /*
137 * add product balance yang belum ada
138 */
139 INSERT INTO in_product_balance
140 (tenant_id, product_id, serial_number, lot_number,
141 product_expired_date, product_year_made,
142 "version", create_datetime, create_user_id, update_datetime, update_user_id)
143 SELECT A.tenant_id, A.product_id, A.serial_number, A.lot_number,
144 A.product_expired_date, A.product_year_made,
145 0, vDatetime, vUserId, vDatetime, vUserId
146 FROM tt_in_product_balance A
147 WHERE A.session_id = pSessionId AND
148 A.product_balance_id = vEmptyId AND
149 NOT EXISTS (SELECT 1 FROM in_product_balance B
150 WHERE A.tenant_id = B.tenant_id AND
151 A.product_id = B.product_id AND
152 A.serial_number = B.serial_number AND
153 A.lot_number = B.lot_number)
154 GROUP BY A.tenant_id, A.product_id, A.serial_number, A.lot_number,
155 A.product_expired_date, A.product_year_made;
156
157 -- Update product_balance_id yang ada di table temp
158 UPDATE tt_in_product_balance A
159 SET product_balance_id = B.product_balance_id
160 FROM in_product_balance B
161 WHERE A.session_id = pSessionId AND
162 A.product_balance_id = vEmptyId AND
163 A.tenant_id = B.tenant_id AND
164 A.product_id = B.product_id AND
165 A.serial_number = B.serial_number AND
166 A.lot_number = B.lot_number;
167
168 /*
169 * create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock
170 */
171 INSERT INTO tt_in_product_balance_summary_stock
172 (session_id, tenant_id, inventory_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty)
173 SELECT B.session_id, B.tenant_id, B.inventory_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status,
174 B.base_uom_id, SUM(B.qty_realization)
175 FROM in_inventory A, tt_in_product_balance B
176 WHERE A.inventory_id = B.inventory_id
177 AND B.session_id = pSessionId
178 GROUP BY B.session_id, B.tenant_id, B.inventory_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status,
179 B.base_uom_id;
180
181 /*
182 * update product_balance_stock
183 */
184 UPDATE in_product_balance_stock
185 SET version = in_product_balance_stock.version + 1,
186 qty = in_product_balance_stock.qty + A.qty,
187 update_datetime = vDatetime,
188 update_user_id = vUserId
189 FROM tt_in_product_balance_summary_stock A
190 WHERE A.session_id = pSessionId AND
191 in_product_balance_stock.tenant_id = A.tenant_id AND
192 in_product_balance_stock.warehouse_id = A.warehouse_id AND
193 in_product_balance_stock.product_id = A.product_id AND
194 in_product_balance_stock.product_balance_id = A.product_balance_id AND
195 in_product_balance_stock.product_status = A.product_status;
196
197 /*
198 * insert data in_product_balance_stock
199 */
200 INSERT INTO in_product_balance_stock
201 (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
202 "version", create_datetime, create_user_id, update_datetime, update_user_id)
203 SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.product_status, A.base_uom_id, A.qty,
204 0, vDatetime, vUserId, vDatetime, vUserId
205 FROM tt_in_product_balance_summary_stock A
206 WHERE A.session_id = pSessionId AND
207 NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
208 WHERE C.tenant_id = A.tenant_id AND
209 C.warehouse_id = A.warehouse_id AND
210 C.product_id = A.product_id AND
211 C.product_balance_id = A.product_balance_id AND
212 C.product_status = A.product_status);
213
214 /*
215 * insert data in_log_product_balance_stock
216 */
217 INSERT INTO in_log_product_balance_stock
218 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
219 product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
220 "version", create_datetime, create_user_id, update_datetime, update_user_id)
221 SELECT A.tenant_id, A.ou_to_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
222 B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, B.qty,
223 0, vDatetime, vUserId, vDatetime, vUserId
224 FROM in_inventory A, tt_in_product_balance_summary_stock B
225 WHERE B.session_id = pSessionId AND
226 A.inventory_id = B.inventory_id;
227
228
229 /*
230 * insert data temporer tt_in_so_balance_item
231 * custom ambil dari RRS
232 *
233 * Modify @author Thea, 15 Jul 2016
234 * ubah ambil nilai item
235 * --- untuk keperluan insert ke data so balance invoice ---
236 */
237 INSERT INTO tt_in_so_balance_item
238 (session_id, tenant_id, ou_id, doc_type_id,
239 doc_no, doc_date, inventory_id, partner_id,
240 inventory_item_id, so_id, do_id, do_item_id,
241 qty_return, base_uom_id, remark,
242 so_item_id, curr_code, price,
243 flg_tax_amount, qty_so, qty_int_so,
244 so_uom_id, tax_id, tax_percentage)
245 SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
246 A.doc_no, A.doc_date, A.inventory_id, C.partner_id,
247 B.inventory_item_id, A.ref_id, B.ref_id, D.request_return_sales_brand_item_id,
248 SUM(B.qty_realization), B.base_uom_id, A.remark,
249 vEmptyId, D.curr_code, D.nett_sell_price,
250 D.flg_tax_amount, D.qty_return, D.qty_return,
251 D.uom_id, D.tax_id, D.tax_percentage
252 FROM in_inventory A, in_inventory_item B, sl_request_return_sales C, sl_request_return_sales_brand_item D, m_product E
253 WHERE A.inventory_id = vReturnNoteId AND
254 A.inventory_id = B.inventory_id AND
255 A.ref_id = C.request_return_sales_id AND
256 A.ref_doc_type_id = C.doc_type_id AND
257 C.request_return_sales_id = D.request_return_sales_id AND
258 E.product_id = B.product_id AND
259 E.brand_id = D.brand_id
260 GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
261 A.doc_no, A.doc_date, A.inventory_id, C.partner_id,
262 B.inventory_item_id, A.ref_id, B.ref_id, D.request_return_sales_brand_item_id,
263 B.base_uom_id, A.remark, D.curr_code, D.nett_sell_price,
264 D.flg_tax_amount, D.qty_so,
265 D.uom_id, D.tax_id, D.tax_percentage;
266
267 /*
268 * buat data sl_so_balance_invoice
269 * --B.regular_disc_amount langsung SET 0 karena nilai regular disc amount nya selalu 0, tidak di set pada RRS
270 */
271 INSERT INTO sl_so_balance_invoice
272 (tenant_id, ou_id, partner_id, so_id,
273 ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_dlv_so, so_uom_id,
274 curr_code, price_so, item_amount, flg_invoice, invoice_id,
275 regular_disc_amount, promo_disc_amount, adj_regular_disc_amount, adj_promo_disc_amount,
276 "version", create_datetime, create_user_id, update_datetime, update_user_id)
277 SELECT A.tenant_id, A.ou_id, A.partner_id, A.so_id,
278 A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.do_item_id, ROUND(SUM((A.qty_return * A.qty_so) / A.qty_int_so)), A.so_uom_id,
279 A.curr_code, A.price,
280 f_get_dpp_after_discount(ROUND(SUM((A.qty_return * A.qty_so) / A.qty_int_so)) * B.gross_sell_price, A.flg_tax_amount, f_get_tax_amount_after_discount(ROUND(SUM((A.qty_return * A.qty_so) / A.qty_int_so)) * B.gross_sell_price, A.flg_tax_amount, A.tax_percentage, 0)),
281 vFlagInvoice, vEmptyId,
282 0, 0, 0, 0,
283 0, vDatetime, vUserId, vDatetime, vUserId
284 FROM tt_in_so_balance_item A
285 INNER JOIN sl_request_return_sales_brand_item B ON A.do_item_id = B.request_return_sales_brand_item_id
286 WHERE A.session_id = pSessionId
287 GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.so_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.do_item_id, A.so_uom_id, A.curr_code, A.price,
288 B.gross_sell_price, A.flg_tax_amount, A.tax_percentage;
289
290 /*
291 * buat data sl_so_balance_invoice_tax
292 */
293 INSERT INTO sl_so_balance_invoice_tax
294 (tenant_id, ou_id, partner_id, so_id,
295 ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
296 tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
297 "version", create_datetime, create_user_id, update_datetime, update_user_id)
298 SELECT A.tenant_id, A.ou_id, A.partner_id, A.so_id,
299 A.doc_type_id, A.inventory_id, A.do_item_id, A.tax_id, D.flg_amount,
300 A.tax_percentage, A.curr_code,
301 f_get_dpp_after_discount(ROUND(SUM((A.qty_return * A.qty_so) / A.qty_int_so)) * B.gross_sell_price, A.flg_tax_amount, f_get_tax_amount_after_discount(ROUND(SUM((A.qty_return * A.qty_so) / A.qty_int_so)) * B.gross_sell_price, A.flg_tax_amount, A.tax_percentage, 0)),
302 f_get_tax_amount_after_discount(ROUND(SUM((A.qty_return * A.qty_so) / A.qty_int_so)) * B.gross_sell_price, A.flg_tax_amount, A.tax_percentage, 0),
303 vFlagInvoice, vEmptyId,
304 0, vDatetime, vUserId, vDatetime, vUserId
305 FROM tt_in_so_balance_item A, m_tax D, sl_request_return_sales_brand_item B
306 WHERE A.session_id = pSessionId AND
307 A.tax_id = D.tax_id AND
308 A.do_item_id = B.request_return_sales_brand_item_id
309 GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.so_id, A.doc_type_id, A.inventory_id, A.do_item_id, A.tax_id,
310 D.flg_amount, A.tax_percentage, A.curr_code, B.gross_sell_price, A.flg_tax_amount;
311
312
313 -- hapus data pada table temp tt_in_so_balance_item
314 DELETE FROM tt_in_so_balance_item WHERE session_id = pSessionId;
315
316 /*
317 * Insert data ke table tt_in_so_balance_item
318 * untuk keperluan update/insert data :
319 * 1. in_balance_do_item
320 * 2. sl_so_balance_item
321 * 3. sl_log_so_balance_item
322 */
323 INSERT INTO tt_in_so_balance_item(
324 session_id, tenant_id, ou_id, doc_type_id,
325 doc_no, doc_date, inventory_id, partner_id,
326 inventory_item_id, so_id, do_id, do_item_id,
327 qty_return, base_uom_id, remark,
328 so_item_id, curr_code, price,
329 flg_tax_amount, qty_so, qty_int_so,
330 so_uom_id, tax_id, tax_percentage)
331 SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
332 A.doc_no, A.doc_date, A.inventory_id, B.partner_id,
333 D.inventory_item_id, B.ref_id, E.do_id, F.do_item_id,
334 D.qty_realization, D.base_uom_id, A.remark,
335 G.so_item_id, C.curr_code, C.nett_sell_price,
336 C.flg_tax_amount, G.qty_so, G.qty_int,
337 F.so_uom_id, C.tax_id, C.tax_percentage
338 FROM in_inventory A
339 INNER JOIN sl_request_return_sales B ON A.ref_id = B.request_return_sales_id AND
340 A.ref_doc_type_id = B.doc_type_id
341 INNER JOIN sl_request_return_sales_brand_item C ON B.request_return_sales_id = C.request_return_sales_id
342 INNER JOIN in_inventory_item D ON A.inventory_id = D.inventory_id AND
343 C.brand_id = f_get_brand_by_product_id(D.product_id)
344 INNER JOIN sl_do E ON B.ref_id = E.ref_id AND
345 B.ref_doc_type_id = E.ref_doc_type_id
346 INNER JOIN sl_do_item F ON E.do_id = F.do_id AND D.product_id = F.product_id
347 INNER JOIN sl_so_item G ON F.ref_id = G.so_item_id AND E.ref_id = G.so_id
348 WHERE A.inventory_id = vReturnNoteId
349 AND B.ref_id <> -99;
350
351 /* update data in_balance_do_item */
352 UPDATE in_balance_do_item B
353 SET qty_return_int = B.qty_return_int + A.qty_return,
354 qty_return = (B.qty_return_int + A.qty_return) * (B.qty_dlv / B.qty_dlv_int),
355 update_datetime = vDatetime,
356 update_user_id = vUserId
357 FROM tt_in_so_balance_item A
358 WHERE A.session_id = pSessionId AND
359 A.do_item_id = B.do_item_id AND
360 A.qty_return > 0;
361
362 UPDATE in_balance_do_item SET status_item = vStatusFinal
363 FROM tt_in_so_balance_item A
364 WHERE A.session_id = pSessionId AND
365 in_balance_do_item.do_item_id = A.do_item_id AND
366 in_balance_do_item.qty_dlv -in_balance_do_item.qty_return <= 0;
367
368 UPDATE in_balance_do_item SET status_item = vStatusRelease
369 FROM tt_in_so_balance_item A
370 WHERE A.session_id = pSessionId AND
371 in_balance_do_item.do_item_id = A.do_item_id AND
372 in_balance_do_item.qty_dlv -in_balance_do_item.qty_return > 0;
373
374 /* Update data in_balance_do_brand_item */
375 WITH retur_brand_item AS (
376 SELECT A.inventory_id, B.ref_id AS so_id, f_get_brand_by_product_id(D.product_id) AS brand_id,
377 SUM(D.qty_realization) AS qty_return
378 FROM in_inventory A
379 INNER JOIN sl_request_return_sales B ON A.ref_id = B.request_return_sales_id AND
380 A.ref_doc_type_id = B.doc_type_id
381 INNER JOIN in_inventory_item D ON A.inventory_id = D.inventory_id
382 WHERE A.inventory_id = vReturnNoteId
383 AND B.ref_id <> -99
384 AND D.qty_realization > 0
385 GROUP BY A.inventory_id, B.ref_id, brand_id
386 )
387 UPDATE in_balance_do_brand_item A
388 SET qty_return_int = A.qty_return_int + B.qty_return,
389 qty_return = (A.qty_return_int + B.qty_return) * (A.qty_dlv / A.qty_dlv_int),
390 update_datetime = vDatetime,
391 update_user_id = vUserId
392 FROM retur_brand_item B
393 WHERE A.so_id = B.so_id AND
394 A.brand_id = B.brand_id;
395
396 WITH retur_brand_item AS (
397 SELECT A.inventory_id, B.ref_id AS so_id, f_get_brand_by_product_id(D.product_id) AS brand_id,
398 SUM(D.qty_realization) AS qty_return
399 FROM in_inventory A
400 INNER JOIN sl_request_return_sales B ON A.ref_id = B.request_return_sales_id AND
401 A.ref_doc_type_id = B.doc_type_id
402 INNER JOIN in_inventory_item D ON A.inventory_id = D.inventory_id
403 WHERE A.inventory_id = vReturnNoteId
404 AND B.ref_id <> -99
405 AND D.qty_realization > 0
406 GROUP BY A.inventory_id, B.ref_id, brand_id
407 )
408 UPDATE in_balance_do_brand_item A
409 SET status_item = CASE WHEN (A.qty_dlv - A.qty_return) > 0 THEN vStatusRelease ELSE vStatusFinal END
410 FROM retur_brand_item B
411 WHERE A.so_id = B.so_id AND
412 A.brand_id = B.brand_id;
413
414 /*
415 * update qty return di data sl_so_balance_item
416 */
417 UPDATE sl_so_balance_item B
418 SET qty_return = B.qty_return + ((A.qty_return * A.qty_so) / A.qty_int_so),
419 qty_return_int = B.qty_return_int + A.qty_return,
420 update_datetime = vDatetime,
421 update_user_id = vUserId
422 FROM tt_in_so_balance_item A
423 WHERE A.session_id = pSessionId AND
424 B.so_item_id = A.so_item_id;
425
426 /*
427 * buat data sl_log_so_balance_item
428 */
429 INSERT INTO sl_log_so_balance_item
430 (tenant_id, so_id, so_item_id, ref_doc_type_id, ref_id, ref_item_id,
431 qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
432 "version", create_datetime, create_user_id, update_datetime, update_user_id)
433 SELECT A.tenant_id, A.so_id, A.so_item_id, A.doc_type_id, A.inventory_id, A.inventory_item_id,
434 ((A.qty_return * A.qty_so) / A.qty_int_so ), A.so_uom_id, A.qty_return, A.base_uom_id, A.remark,
435 0, vDatetime, vUserId, vDatetime, vUserId
436 FROM tt_in_so_balance_item A
437 WHERE A.session_id = pSessionId;
438
439 /*
440 * Henik
441 * For Project Katamata Only, update data balance MOU & insert Log if SO was tagging to MOU
442 * For Project Katamata Only, insert into return tagging based on SO if SO was tagging to MOU
443 *
444 * ======= BEGIN =======
445 */
446 -- update plafon mou di mou balance
447 -- And insert ke table in_return_tagging
448 SELECT B.ref_id INTO vSoId
449 FROM in_inventory A
450 INNER JOIN sl_request_return_sales B
451 ON A.ref_id = B.request_return_sales_id
452 AND A.ref_doc_type_id = B.doc_type_id
453 WHERE A.inventory_id = vReturnNoteId;
454
455 IF EXISTS(SELECT 1 FROM sl_so_tagging WHERE tenant_id = pTenantId AND so_id = vSoId AND tag_key = vTagKeyMou) THEN
456 --update data MOU balance if so = so by brand yang tagging ke mou ( and mou_id != -99 )
457 WITH summary AS (
458 SELECT E.ref_id AS so_id, B.tag_doc_id AS mou_id,
459 SUM(A.item_amount-A.regular_disc_amount)+SUM(COALESCE(C.tax_amount,0)) AS amount_and_tax
460 FROM sl_so_balance_invoice A
461 INNER JOIN in_inventory D ON A.ref_id = D.inventory_id AND A.ref_doc_type_id = D.doc_type_id
462 INNER JOIN sl_request_return_sales E ON D.ref_id = E.request_return_sales_id AND D.ref_doc_type_id = E.doc_type_id
463 INNER JOIN sl_so_tagging B ON E.ref_id = B.so_id AND A.tenant_id = B.tenant_id AND B.tag_key = vTagKeyMou
464 LEFT JOIN sl_so_balance_invoice_tax C
465 ON A.partner_id = C.partner_id
466 AND A.ref_doc_type_id = C.ref_doc_type_id
467 AND A.ref_id = C.ref_id
468 AND A.ref_item_id = C.ref_item_id
469 AND A.do_receipt_item_id = C.do_receipt_item_id
470 WHERE A.tenant_id = pTenantId
471 AND A.ref_id = vReturnNoteId
472 AND A.ref_doc_type_id = vReturnNoteDocTypeId
473 GROUP BY E.ref_id, B.tag_doc_id
474 )
475 UPDATE sl_mou_balance D
476 SET usage_balance = D.usage_balance - E.amount_and_tax,
477 version = D.version + 1,
478 update_datetime = vDatetime,
479 update_user_id = vUserId
480 FROM summary E
481 WHERE D.mou_id = E.mou_id;
482
483 --insert log untuk amount nya
484 WITH summary AS (
485 SELECT A.tenant_id, A.ou_from_id, C.tag_doc_id AS mou_id, A.doc_type_id,
486 A.inventory_id, A.doc_no, A.doc_date, A.partner_id, B.curr_code,
487 SUM(B.item_amount-B.regular_disc_amount) AS amount
488 FROM in_inventory A
489 INNER JOIN sl_request_return_sales D ON A.ref_id = D.request_return_sales_id AND A.ref_doc_type_id = D.doc_type_id
490 INNER JOIN sl_so_balance_invoice B ON A.inventory_id = B.ref_id AND A.doc_type_id = B.ref_doc_type_id
491 INNER JOIN sl_so_tagging C ON D.ref_id = C.so_id AND A.tenant_id = C.tenant_id AND C.tag_key = vTagKeyMou
492 WHERE A.tenant_id = pTenantId
493 AND A.inventory_id = vReturnNoteId
494 AND A.doc_type_id = vReturnNoteDocTypeId
495 GROUP BY A.tenant_id, A.ou_from_id, C.tag_doc_id, A.doc_type_id,
496 A.inventory_id, A.doc_no, A.doc_date, A.partner_id, B.curr_code
497 )
498 INSERT INTO sl_mou_balance_log (
499 tenant_id, ou_id, mou_id, ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
500 partner_id, curr_code, amount, remark,
501 create_datetime, create_user_id, update_datetime, update_user_id, version)
502 SELECT D.tenant_id, D.ou_from_id, D.mou_id, D.doc_type_id, D.inventory_id, D.doc_no, D.doc_date,
503 D.partner_id, D.curr_code, D.amount , 'Return Balance MOU from Return-Note',
504 vDatetime, vUserId, vDatetime, vUserId, 0
505 FROM summary D;
506
507 --insert log untuk amount - tax nya
508 WITH summary AS (
509 SELECT A.tenant_id, A.ou_from_id, C.tag_doc_id AS mou_id, A.doc_type_id,
510 A.inventory_id, A.doc_no, A.doc_date, A.partner_id, B.curr_code,
511 SUM(B.tax_amount) AS tax_amount
512 FROM in_inventory A
513 INNER JOIN sl_request_return_sales D ON A.ref_id = D.request_return_sales_id AND A.ref_doc_type_id = D.doc_type_id
514 INNER JOIN sl_so_balance_invoice_tax B ON A.inventory_id = B.ref_id AND A.doc_type_id = B.ref_doc_type_id
515 INNER JOIN sl_so_tagging C ON D.ref_id = C.so_id AND A.tenant_id = C.tenant_id AND C.tag_key = vTagKeyMou
516 WHERE A.tenant_id = pTenantId
517 AND A.inventory_id = vReturnNoteId
518 AND A.doc_type_id = vReturnNoteDocTypeId
519 GROUP BY A.tenant_id, A.ou_from_id, C.tag_doc_id, A.doc_type_id,
520 A.inventory_id, A.doc_no, A.doc_date, A.partner_id, B.curr_code
521 )
522 INSERT INTO sl_mou_balance_log (
523 tenant_id, ou_id, mou_id, ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
524 partner_id, curr_code, amount, remark,
525 create_datetime, create_user_id, update_datetime, update_user_id, version)
526 SELECT D.tenant_id, D.ou_from_id, D.mou_id, D.doc_type_id, D.inventory_id, D.doc_no, D.doc_date,
527 D.partner_id, D.curr_code, D.tax_amount , 'Return Balance MOU from Return-Note - Tax',
528 vDatetime, vUserId, vDatetime, vUserId, 0
529 FROM summary D;
530
531 -- Insert ke table in_return_tagging
532 INSERT INTO in_return_tagging (
533 tenant_id, ou_id, inventory_id, tag_key, tag_doc_id, tag_doc_no, remark,
534 create_datetime, create_user_id, update_datetime, update_user_id, version)
535 SELECT A.tenant_id, A.ou_from_id, vReturnNoteId, vTagKeyMou, B.tag_doc_id, B.tag_doc_no, 'Tag To MOU (Default by SO)',
536 vDatetime, vUserId, vDatetime, vUserId, 0
537 FROM in_inventory A
538 INNER JOIN sl_request_return_sales C
539 ON A.ref_id = C.request_return_sales_id
540 AND A.ref_doc_type_id = C.doc_type_id
541 INNER JOIN sl_so_tagging B ON C.ref_id = B.so_id AND C.tenant_id = B.tenant_id AND B.tag_key = vTagKeyMou
542 WHERE A.inventory_id = vReturnNoteId;
543
544 END IF;
545 /*======== END ==========*/
546
547 ---------------------------------------------------------------------------------------------------------------------------------
548 -- Modif by Henik , 25 Agustus 2017
549 /* Perhitungan coin (pengurang) dari dok Return Note
550 * 1. Selalu lakukan perhitungan coin, baik yg ada referensi SOB maupun tidak
551 * 2. Hanya dapat coin promo
552 * 3. Menentukan masuk ke promo mana
553 * 1. Berdasarkan tgl return, dan brand yg direturn
554 * 2. Cek tgl return masuk ke periode promo mana yg masih aktif,
555 * jika masuk ke lebih dari 1 promo maka ambil date from paling akhir
556 * ( date_return BETWEEN date_from AND date_to) -> lebih dr 1 : ambil MAX(date_from)
557 * -> lebih dr 1 : ambil MIN(date_to) ambil satu data yg dibuat paling duluan
558 *
559 * 4. Perhitungan : coin promo * (qty return per brand * -1)
560 *
561 */
562
563 -- Prepare data Return Note , dan cari promo mana yg diperoleh
564 -- Qty Return di kali -1
565 -- nilai promo coin di set bernilai Yes
566 WITH prepare_get_promo_return AS (
567
568 -- Menentukan RN masuk ke daftar promo mana saja
569 SELECT A.tenant_id, A.partner_id, A.inventory_id, A.doc_type_id,
570 E.promo_id, E.date_from, E.date_to
571 FROM in_inventory A
572 INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
573 INNER JOIN sl_request_return_sales_brand_item C ON A.ref_id = C.request_return_sales_id
574 AND f_get_brand_by_product_id(B.product_id) = C.brand_id
575 INNER JOIN m_promo E ON A.tenant_id = E.tenant_id
576 AND E.flg_launching = vFlgNo
577 AND E.active = vFlgYes
578 WHERE A.inventory_id = vReturnNoteId
579 AND A.doc_date BETWEEN E.date_from AND E.date_to
580 AND EXISTS(SELECT 1 FROM m_promo_item X
581 WHERE E.promo_id = X.promo_id
582 AND C.brand_id = X.brand_id)
583 GROUP BY A.tenant_id, A.partner_id, A.inventory_id, A.doc_type_id, E.promo_id, E.date_from, E.date_to
584
585 ), select_promo_for_return AS (
586
587 -- Mengambil promo yg date_from nya paling mendekati tgl return
588 SELECT A.tenant_id, A.partner_id, A.inventory_id, A.doc_type_id,
589 A.promo_id, A.date_from, A.date_to
590 FROM prepare_get_promo_return A
591 WHERE EXISTS(SELECT 1
592 FROM prepare_get_promo_return B
593 WHERE A.inventory_id = B.inventory_id
594 HAVING A.date_from = MAX(B.date_from)
595 )
596
597 ), filter_promo_for_return AS (
598
599 -- jika dari max(date_from) promo ada lebih dari 1, maka ambil date_to promo yg paling dekat dengan tgl RN
600 -- data promo akan diorder by promo_id dan dilimit 1 data yg diambil
601 SELECT A.tenant_id, A.partner_id, A.inventory_id, A.doc_type_id,
602 A.promo_id, A.date_from, A.date_to
603 FROM select_promo_for_return A
604 WHERE EXISTS(SELECT 1
605 FROM select_promo_for_return B
606 WHERE A.inventory_id = B.inventory_id
607 HAVING A.date_to = MIN(B.date_to)
608 )
609 ORDER BY A.promo_id
610 LIMIT 1
611
612 )
613 -- insert data prepare promo for return note untuk di follow up proses perhitungan coinnya
614 INSERT INTO tt_coin_detail_brand (
615 session_id,tenant_id, partner_id, so_id, ref_id, ref_doc_type_id, salesman_id,
616 brand_id, group_brand, qty,
617 promo_id, flg_promo_coin, sub_promo_id,
618 flg_sub_promo_coin, product_launching_id, flg_launching_coin,
619 coin_promo, coin_sub_promo, coin_launching,
620 coin_adjustment, coin_periodic_adjustment
621 )
622 SELECT pSessionId, A.tenant_id, A.partner_id, vEmptyId, A.inventory_id, A.doc_type_id, vEmptyId,
623 C.brand_id, C.group_brand, SUM(B.qty_realization *-1) AS qty,
624 A.promo_id, vFlgYes, vEmptyId,
625 vFlgNo, vEmptyId, vFlgNo,
626 vEmptyAmount, vEmptyAmount, vEmptyAmount,
627 vEmptyAmount, vEmptyAmount
628 FROM filter_promo_for_return A
629 INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
630 INNER JOIN m_brand_ext C ON f_get_brand_by_product_id(B.product_id) = C.brand_id
631 GROUP BY A.tenant_id, A.partner_id, A.inventory_id, A.doc_type_id,
632 C.brand_id, C.group_brand, A.promo_id;
633
634 -- Dilakukan perhitungan coin dan update data coin promo balance jika dokumen return note memiliki promo
635 IF EXISTS(SELECT 1 FROM tt_coin_detail_brand WHERE session_id = pSessionId AND ref_id = vReturnNoteId AND promo_id <> vEmptyId) THEN
636
637 -- Execute function perhitungan coin
638 PERFORM f_coin_calculation(pSessionId);
639
640 -- Update data coin promo balance jika data nya sudah ada (lihat berdasarkan partner_id dan promo_id yg sama)
641 -- Berdasarkan data di table temp tt_coin_summary hasil dari function f_coin_calculation
642 UPDATE sl_coin_promo_balance A SET
643 coin_promo = A.coin_promo + B.coin_promo,
644 coin_sub_promo = A.coin_sub_promo + B.coin_sub_promo,
645 coin_launching = A.coin_launching + B.coin_launching,
646 coin_total = A.coin_total + B.coin_total,
647 version = A.version + 1,
648 update_user_id = vUserId,
649 update_datetime = vDatetime
650 FROM tt_coin_summary B
651 WHERE B.session_id = pSessionId
652 AND A.tenant_id = B.tenant_id
653 AND A.partner_id = B.partner_id
654 AND A.promo_id = B.promo_id;
655
656 -- Insert data coin balance jika belum ada datanya
657 -- Berdasarkan data di table temp tt_coin_summary hasil dari function f_coin_calculation
658 INSERT INTO sl_coin_promo_balance (
659 tenant_id, partner_id, promo_id, coin_promo, coin_sub_promo,
660 coin_launching, coin_adjustment, coin_periodic_adjustment, coin_total,
661 create_datetime, create_user_id, update_datetime, update_user_id, version
662 )
663 SELECT A.tenant_id, A.partner_id, A.promo_id, A.coin_promo, A.coin_sub_promo,
664 A.coin_launching, A.coin_adjustment, A.coin_periodic_adjustment, A.coin_total,
665 vDatetime, vUserId, vDatetime, vUserId, 0
666 FROM tt_coin_summary A
667 WHERE session_id = pSessionId
668 AND NOT EXISTS (SELECT 1 FROM sl_coin_promo_balance B
669 WHERE A.tenant_id = B.tenant_id
670 AND A.partner_id = B.partner_id
671 AND A.promo_id = B.promo_id);
672
673 -- Insert data log coin ke table sl_log_coin_promo_balance
674 -- Berdasarkan data di table temp tt_coin_detail_brand hasil dari olahan function f_coin_calculation
675 INSERT INTO sl_log_coin_promo_balance (
676 tenant_id, partner_id, so_id, ref_id, ref_doc_type_id, promo_id, flg_promo_coin,
677 sub_promo_id, flg_sub_promo_coin, product_launching_id, flg_launching_coin,
678 brand_id, group_brand, salesman_id, qty, coin_promo, coin_sub_promo,
679 coin_launching, coin_adjustment, coin_periodic_adjustment,
680 create_datetime, create_user_id, update_datetime, update_user_id, version
681 )
682 SELECT tenant_id, partner_id, so_id, ref_id, ref_doc_type_id, promo_id, flg_promo_coin,
683 sub_promo_id, flg_sub_promo_coin, product_launching_id, flg_launching_coin,
684 brand_id, group_brand, salesman_id, qty, coin_promo, coin_sub_promo,
685 coin_launching, coin_adjustment, coin_periodic_adjustment,
686 vDatetime, vUserId, vDatetime, vUserId, 0
687 FROM tt_coin_detail_brand A
688 WHERE A.session_id = pSessionId
689 ORDER BY A.brand_id;
690
691 END IF;
692
693
694 ---------------------------------------------------------------------------------------------------------------------------------
695
696 /*
697 * journal return note
698 * Debit Inventory = dari nilai COGS
699 * Credit HPP
700 */
701 /*
702 * membuat data transaksi jurnal :
703 * 1. buat admin
704 * 2. buat temlate jurnal
705 */
706 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)
707 FROM in_inventory A
708 WHERE A.inventory_id = vReturnNoteId;
709
710 SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
711
712 INSERT INTO gl_journal_trx
713 (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
714 ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
715 ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
716 "version", create_datetime, create_user_id, update_datetime, update_user_id)
717 SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date,
718 (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,
719 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',
720 0, vDatetime, vUserId, vDatetime, vUserId
721 FROM in_inventory A
722 WHERE A.inventory_id = vReturnNoteId;
723
724 INSERT INTO tt_journal_trx_item
725 (session_id, tenant_id, journal_trx_id, line_no,
726 ref_doc_type_id, ref_id,
727 partner_id, product_id, cashbank_id, ou_rc_id,
728 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
729 coa_id, curr_code, qty, uom_id,
730 amount, journal_date, type_rate,
731 numerator_rate, denominator_rate, journal_desc, remark)
732 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
733 A.doc_type_id, B.inventory_item_id,
734 A.partner_id, B.product_id, vEmptyId, vEmptyId,
735 vEmptyId, vSignDebit, vProductCOA, vEmptyId,
736 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,
737 0 , A.doc_date, vTypeRate,
738 1, 1, 'PRODUCT_STOCK', B.remark
739 FROM in_inventory A, in_inventory_item B
740 WHERE A.inventory_id = vReturnNoteId AND
741 A.inventory_id = B.inventory_id;
742
743
744/* NK, 1 Feb 2014, journal HPP tidak perlu break down sampai ke product
745 INSERT INTO tt_journal_trx_item
746 (session_id, tenant_id, journal_trx_id, line_no,
747 ref_doc_type_id, ref_id,
748 partner_id, product_id, cashbank_id, ou_rc_id,
749 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
750 coa_id, curr_code, qty, uom_id,
751 amount, journal_date, type_rate,
752 numerator_rate, denominator_rate, journal_desc, remark)
753 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
754 A.doc_type_id, B.inventory_item_id,
755 A.partner_id, B.product_id, vEmptyId, vEmptyId,
756 vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
757 f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
758 0 , A.doc_date, vTypeRate,
759 1, 1, 'HPP', B.remark
760 FROM in_inventory A, in_inventory_item B
761 WHERE A.inventory_id = vReturnNoteId AND
762 A.inventory_id = B.inventory_id;
763*/
764/*
765 INSERT INTO tt_journal_trx_item
766 (session_id, tenant_id, journal_trx_id, line_no,
767 ref_doc_type_id, ref_id, ou_id, sub_ou_id,
768 partner_id, product_id, cashbank_id, ou_rc_id,
769 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
770 coa_id, curr_code, qty, uom_id,
771 amount, journal_date, type_rate,
772 numerator_rate, denominator_rate, journal_desc, remark)
773 SELECT pSessionId, A.tenant_id, B.journal_trx_id, 1,
774 A.doc_type_id, A.inventory_item_id, B.ou_id, B.sub_ou_id,
775 vEmptyId, C.product_id, vEmptyId, vEmptyId,
776 vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
777 f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), A.curr_code, A.qty_return, A.base_uom_id,
778 f_get_amount_before_tax((A.qty_return * A.qty_so * A.price) / A.qty_int_so, A.flg_tax_amount, A.tax_percentage,0),
779 A.doc_date, vTypeRate,
780 1, 1, 'HPP', A.remark
781 FROM tt_in_so_balance_item A, gl_journal_trx B, in_inventory_item C
782 WHERE A.session_id = pSessionId AND
783 B.journal_trx_id = vJournalTrxId AND
784 A.inventory_item_id = C.inventory_item_id;
785
786 INSERT INTO gl_journal_trx_mapping
787 (tenant_id, journal_trx_id, line_no,
788 ref_doc_type_id, ref_id,
789 partner_id, product_id, cashbank_id, ou_rc_id,
790 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
791 coa_id, curr_code, qty, uom_id,
792 amount, journal_date, type_rate,
793 numerator_rate, denominator_rate, journal_desc, remark,
794 "version", create_datetime, create_user_id, update_datetime, update_user_id)
795 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
796 A.ref_doc_type_id, A.ref_id,
797 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
798 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
799 A.coa_id, A.curr_code, A.qty, A.uom_id,
800 A.amount, A.journal_date, A.type_rate,
801 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
802 0, vDatetime, vUserId, vDatetime, vUserId
803 FROM tt_journal_trx_item A
804 WHERE A.session_id = pSessionId AND
805 A.journal_desc = 'HPP';
806
807*/
808 INSERT INTO gl_journal_trx_item
809 (tenant_id, journal_trx_id, line_no,
810 ref_doc_type_id, ref_id,
811 partner_id, product_id, cashbank_id, ou_rc_id,
812 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
813 coa_id, curr_code, qty, uom_id,
814 amount, journal_date, type_rate,
815 numerator_rate, denominator_rate, journal_desc, remark,
816 "version", create_datetime, create_user_id, update_datetime, update_user_id)
817 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
818 A.ref_doc_type_id, A.ref_id,
819 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
820 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
821 A.coa_id, A.curr_code, A.qty, A.uom_id,
822 A.amount, A.journal_date, A.type_rate,
823 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
824 0, vDatetime, vUserId, vDatetime, vUserId
825 FROM tt_journal_trx_item A
826 WHERE A.session_id = pSessionId AND
827 A.journal_desc = 'PRODUCT_STOCK';
828
829 INSERT INTO gl_journal_trx_mapping
830 (tenant_id, journal_trx_id, line_no,
831 ref_doc_type_id, ref_id,
832 partner_id, product_id, cashbank_id, ou_rc_id,
833 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
834 coa_id, curr_code, qty, uom_id,
835 amount, journal_date, type_rate,
836 numerator_rate, denominator_rate, journal_desc, remark,
837 "version", create_datetime, create_user_id, update_datetime, update_user_id)
838 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
839 vEmptyId, vEmptyId,
840 vEmptyId, vEmptyId, vEmptyId, vEmptyId,
841 vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
842 f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
843 0, A.journal_date, A.type_rate,
844 1, 1, 'COGS', vEmptyValue,
845 0, vDatetime, vUserId, vDatetime, vUserId
846 FROM tt_journal_trx_item A
847 WHERE A.session_id = pSessionId
848 GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate;
849
850 -- DELETE table temporary
851 DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;
852 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
853 DELETE FROM tt_in_so_balance_item WHERE session_id = pSessionId;
854 DELETE FROM tt_coin_detail_brand WHERE session_id = pSessionId;
855 DELETE FROM tt_coin_summary WHERE session_id = pSessionId;
856END;
857$BODY$
858 LANGUAGE plpgsql VOLATILE
859 COST 100;
860 /