· 6 years ago · Mar 26, 2019, 08:46 AM
1-- Function: pu_submit_receive_goods(bigint, character varying, character varying)
2
3-- DROP FUNCTION pu_submit_receive_goods(bigint, character varying, character varying);
4
5CREATE OR REPLACE FUNCTION pu_submit_receive_goods(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 vRgId 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(50);
24 vSignDebit character varying(1);
25 vSignCredit character varying(1);
26 vTypeRate character varying(3);
27 vActivityCOA character varying(10);
28 vProductCOA character varying(10);
29 vSystemCOA character varying(10);
30 vPoId bigint;
31 vUnfinishedItem bigint;
32 vJournalTrxId bigint;
33
34 vDocJournal DOC_JOURNAL%ROWTYPE;
35 vOuStructure OU_BU_STRUCTURE%ROWTYPE;
36 result RECORD;
37
38 vReceiveGoodsDocTypeId bigint;
39 vRoundingModeNonTax character varying(5);
40 vDigitDppRoundingTax integer;
41
42
43 /*
44 * NK, 2 Jan 2015
45 * untuk kepentingan auto serial number
46 */
47 vDocDate character varying(8);
48
49 vOutletId bigint;
50 vOuWarehouseId bigint;
51 vDbVersion character varying(10);
52BEGIN
53
54 vFlagInvoice := 'N';
55 vEmptyId := -99;
56 vStatusRelease := 'R';
57 vStatusDraft := 'D';
58 vStatusFinal := 'F';
59 vEmptyValue := ' ';
60 vOutletId := -99;
61 vOuWarehouseId = -99;
62 vDbVersion = '1.0';
63
64 /*
65 * NK, 31 Des 2014
66 * diganti dng membaca m_product_status
67 */
68 --vProductStatus := 'GOOD';
69 SELECT product_status_code INTO vProductStatus
70 FROM m_product_status
71 WHERE flg_buy = 'Y';
72
73 vSignDebit := 'D';
74 vSignCredit := 'C';
75 vTypeRate := 'COM';
76 vActivityCOA := 'ACTIVITY';
77 vProductCOA := 'PRODUCT';
78 vSystemCOA := 'SYSTEM';
79 vUnfinishedItem := 0;
80
81 vReceiveGoodsDocTypeId := 111;
82 SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
83 SELECT f_get_value_system_config_by_param_code(pTenantId, 'dpp.rounding.decimal.tax')::integer INTO vDigitDppRoundingTax;
84
85
86 SELECT A.process_message_id INTO vProcessId
87 FROM t_process_message A
88 WHERE A.tenant_id = pTenantId AND
89 A.process_name = 'pu_submit_receive_goods' AND
90 A.process_no = pProcessNo;
91
92 SELECT CAST(A.process_parameter_value AS bigint) INTO vRgId
93 FROM t_process_parameter A
94 WHERE A.process_message_id = vProcessId AND
95 A.process_parameter_key = 'receiveGoodsId';
96
97 SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
98 FROM t_process_parameter A
99 WHERE A.process_message_id = vProcessId AND
100 A.process_parameter_key = 'userId';
101
102 SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
103 FROM t_process_parameter A
104 WHERE A.process_message_id = vProcessId AND
105 A.process_parameter_key = 'datetime';
106
107 DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;
108 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
109 DELETE FROM tt_pu_product_balance_summary_stock WHERE session_id = pSessionId;
110 DELETE FROM tt_pu_monthly_price_product WHERE session_id = pSessionId;
111/*
112 * 1. add pu_log_po_balance_item
113 * 2. add pu_po_balance_invoice
114 * 3. add pu_po_balance_invoice_tax
115 * 4. add in_produt_balance jika belum exists ( bisa ada serial number dan yang tidak ada serial number )
116 * 5. add in_product_price_balance
117 * 6. add/update in_product_balance_stock
118 * 7. add in_log_product_balance_stock
119 * 7b. add data log untuk outlet jika gudang nya adalah gudang outlet
120 * 8. add in_log_product_price_balance_stock
121 * 9. update status pu_receive_goods
122 * 10.update status pu_po_balance_item
123 * 10b.update status pu_po. Jika seluruh balance item sudah final/cancel, maka status menjadi Final.
124 * 11.add balance receive goods item
125 * 12.add gl_journal_trx
126 * 13.add gl_journal_trx_item
127 * 14.add gl_journal_trx_mapping
128 *
129 * ini di tambahin untuk penambahan doc type id invoice
130 * 15. add pu_po_balance_invoice_ext_invoice_doc_type
131 * 16. add pu_po_balance_invoice_tax_ext
132 *
133 */
134/* pakai cara lain, NK, 14 Jan 2014
135 SELECT A.ref_id, f_get_parent_ou_bu(A.tenant_id, A.ou_id), f_get_journal_type(A.doc_type_id) INTO vPoId, vParentOuId, vJournalType
136 FROM pu_receive_goods A
137 WHERE A.receive_goods_id = vRgId;
138*/
139
140 SELECT A.ref_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc, A.doc_date
141 FROM pu_receive_goods A
142 WHERE A.receive_goods_id = vRgId INTO result;
143
144 vPoId := result.ref_id;
145 vOuStructure := result.ou;
146 vDocJournal := result.doc;
147 vDocDate := result.doc_date;
148/*
149 SELECT INTO vOuStructure f_get_ou_bu_structure(A.ou_id)
150 FROM pu_receive_goods A
151 WHERE A.receive_goods_id = vRgId;
152
153 SELECT INTO vDocJournal f_get_document_journal(A.doc_type_id)
154 FROM pu_receive_goods A
155 WHERE A.receive_goods_id = vRgId;
156
157 SELECT A.ref_id INTO vPoId
158 FROM pu_receive_goods A
159 WHERE A.receive_goods_id = vRgId;
160 */
161
162 /*
163 * NK, 2 Jan 2015
164 * memanggil function untuk membuat auto serial number
165 */
166 PERFORM pu_create_sn_receive_goods(pTenantId, pSessionId, vDocDate, vRgId, vUserId, vDatetime );
167
168 INSERT INTO pu_log_po_balance_item
169 (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
170 qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
171 "version", create_datetime, create_user_id, update_datetime, update_user_id)
172 SELECT A.tenant_id, C.po_id, C.po_item_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id,
173 B.qty_rcv_po * -1, B.po_uom_id, B.qty_rcv_int * -1, B.base_uom_id, B.remark,
174 0, vDatetime, vUserId, vDatetime, vUserId
175 FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
176 WHERE A.receive_goods_id = vRgId AND
177 A.receive_goods_id = B.receive_goods_id AND
178 B.ref_id = C.po_item_id;
179
180 /*
181 * Modify Widana, 08 Nov 2016
182 * table:pu_po_balance_invoice, mengubah f_get_amount_before_tax
183 *
184 * before :
185 * f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax),
186 *
187 * After :
188 * f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount),C.flg_tax_amount,C.tax_percentage,vDigitDppRoundingTax, vRoundingModeNonTax),
189 *
190 */
191
192 INSERT INTO pu_po_balance_invoice
193 (tenant_id, ou_id, partner_id, po_id,
194 ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
195 curr_code, price_po, item_amount, flg_invoice, invoice_id,
196 "version", create_datetime, create_user_id, update_datetime, update_user_id)
197 SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
198 A.doc_type_id, A.receive_goods_id, A.doc_no, A.doc_date, B.receive_goods_item_id, B.qty_rcv_po, B.po_uom_id,
199 C.curr_code, C.nett_price_po,
200 f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount),C.flg_tax_amount,C.tax_percentage,vDigitDppRoundingTax, vRoundingModeNonTax),
201
202-- f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax),
203
204 vFlagInvoice, vEmptyId,
205 0, vDatetime, vUserId, vDatetime, vUserId
206 FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
207 WHERE A.receive_goods_id = vRgId AND
208 A.receive_goods_id = B.receive_goods_id AND
209 B.ref_id = C.po_item_id;
210
211 /*
212 * Modify Widana, 08 Nov 2016
213 * table:pu_po_balance_invoice_tax, mengubah f_get_amount_before_tax,f_tax_rounding
214 *
215 * before :
216 * f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax),
217 * f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax), C.tax_percentage),
218 *
219 * After :
220 * f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount),C.flg_tax_amount,C.tax_percentage,vDigitDppRoundingTax, vRoundingModeNonTax),
221 * f_tax_rounding(A.tenant_id,f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount),C.flg_tax_amount,C.tax_percentage,vDigitDppRoundingTax, vRoundingModeNonTax), C.tax_percentage),
222 *
223 */
224
225
226 INSERT INTO pu_po_balance_invoice_tax
227 (tenant_id, ou_id, partner_id, po_id,
228 ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
229 tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
230 "version", create_datetime, create_user_id, update_datetime, update_user_id)
231 SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
232 A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, C.tax_id, D.flg_amount,
233 C.tax_percentage, C.curr_code,
234
235 f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount),C.flg_tax_amount,C.tax_percentage,vDigitDppRoundingTax, vRoundingModeNonTax),
236 f_tax_rounding(A.tenant_id,f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount),C.flg_tax_amount,C.tax_percentage,vDigitDppRoundingTax, vRoundingModeNonTax), C.tax_percentage),
237
238-- f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax),
239-- f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax), C.tax_percentage),
240 vFlagInvoice, vEmptyId,
241 0, vDatetime, vUserId, vDatetime, vUserId
242 FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, m_tax D
243 WHERE A.receive_goods_id = vRgId AND
244 A.receive_goods_id = B.receive_goods_id AND
245 B.ref_id = C.po_item_id AND
246 C.tax_id = D.tax_id;
247
248 INSERT INTO pu_po_balance_invoice_ext
249 (po_id, ref_doc_type_id, ref_id, ref_item_id, po_item_id, curr_code, po_uom_id,
250 qty_receive, discount_percentage, discount_amount, total_discount_amount,
251 "version", create_datetime, create_user_id, update_datetime, update_user_id)
252 SELECT A.ref_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, B.ref_id, C.curr_code, B.po_uom_id,
253 B.qty_rcv_po, C.discount_percentage, C.discount_amount, B.qty_rcv_po * C.discount_amount,
254 0, vDatetime, vUserId, vDatetime, vUserId
255 FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
256 WHERE A.receive_goods_id = vRgId AND
257 A.receive_goods_id = B.receive_goods_id AND
258 B.ref_id = C.po_item_id;
259
260 /*
261 * tampung data item receive goods, untuk dapat digunakan dan disimpan ke product_balance, product_price_balance
262 */
263 -- Insert untuk data product yang tidak memiliki serial number
264 INSERT INTO tt_pu_product_balance
265 (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
266 serial_number, lot_number, product_expired_date, product_year_made,
267 product_price_balance_id, product_buy_date, partner_id,
268 doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
269 po_id, po_no, po_date, po_item_id,
270 curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
271 SELECT pSessionId, A.warehouse_id, vEmptyId, A.tenant_id, A.ou_id, B.product_id,
272 vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue,
273 vEmptyId, A.doc_date, A.partner_id,
274 A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no, A.doc_date,
275 E.po_id, E.doc_no, E.doc_date, D.po_item_id,
276 D.curr_code, D.gross_price_po - D.discount_amount, SUM(B.qty_rcv_po), D.po_uom_id, SUM(B.qty_rcv_int), D.base_uom_id, D.flg_stock
277 FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item D, pu_po E
278 WHERE A.receive_goods_id = vRgId AND
279 A.receive_goods_id = B.receive_goods_id AND
280 NOT EXISTS (SELECT 1 FROM pu_receive_goods_product C
281 WHERE B.receive_goods_item_id = C.receive_goods_item_id) AND
282 NOT EXISTS (SELECT 1 FROM pu_receive_goods_product_auto_sn C
283 WHERE B.receive_goods_item_id = C.receive_goods_item_id) AND
284 B.ref_id = D.po_item_id AND
285 D.po_id = E.po_id
286 GROUP BY A.warehouse_id, A.tenant_id, A.ou_id, B.product_id,
287 A.doc_date, A.partner_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no,
288 E.po_id, E.doc_no, E.doc_date, D.po_item_id,
289 D.curr_code, D.nett_price_po, D.po_uom_id, D.base_uom_id, D.flg_stock;
290
291 -- Insert untuk data product yang memiliki serial number dan serial number tidak di-generate
292 INSERT INTO tt_pu_product_balance
293 (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
294 serial_number, lot_number, product_expired_date, product_year_made,
295 product_price_balance_id, product_buy_date, partner_id,
296 doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
297 po_id, po_no, po_date, po_item_id,
298 curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
299 SELECT pSessionId, A.warehouse_id, vEmptyId, A.tenant_id, A.ou_id, B.product_id,
300 C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
301 vEmptyId, A.doc_date, A.partner_id,
302 A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no, A.doc_date,
303 E.po_id, E.doc_no, E.doc_date, D.po_item_id,
304 D.curr_code, D.gross_price_po - D.discount_amount, SUM(C.qty_rcv_po), D.po_uom_id, SUM(C.qty_rcv_int), D.base_uom_id, D.flg_stock
305 FROM pu_receive_goods A, pu_receive_goods_item B, pu_receive_goods_product C, pu_po_item D, pu_po E
306 WHERE A.receive_goods_id = vRgId AND
307 A.receive_goods_id = B.receive_goods_id AND
308 B.receive_goods_item_id = C.receive_goods_item_id AND
309 B.ref_id = D.po_item_id AND
310 D.po_id = E.po_id
311 GROUP BY A.warehouse_id, A.tenant_id, A.ou_id, B.product_id,
312 C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
313 A.doc_date, A.partner_id, A.doc_type_id, a.receive_goods_id, B.receive_goods_item_id, A.doc_no,
314 E.po_id, E.doc_no, E.doc_date, D.po_item_id,
315 D.curr_code, D.nett_price_po, D.po_uom_id, D.base_uom_id, D.flg_stock;
316
317/* NK, 5 Jan 2015 sudah diganti dengan function pu_create_sn_receive_goods
318 -- Insert untuk data product yang memiliki serial number dan serial number di-generate
319 INSERT INTO tt_pu_product_balance
320 (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
321 serial_number, lot_number, product_expired_date, product_year_made,
322 product_price_balance_id, product_buy_date, partner_id,
323 doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
324 po_id, po_no, po_date, po_item_id,
325 curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
326 SELECT pSessionId, A.warehouse_id, vEmptyId, A.tenant_id, A.ou_id, B.product_id,
327 C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
328 vEmptyId, A.doc_date, A.partner_id,
329 A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no, A.doc_date,
330 E.po_id, E.doc_no, E.doc_date, D.po_item_id,
331 D.curr_code, D.gross_price_po - D.discount_amount, SUM(C.qty_rcv_po), D.po_uom_id, 1, D.base_uom_id, D.flg_stock
332 FROM pu_receive_goods A, pu_receive_goods_item B, pu_receive_goods_product_auto_sn C, pu_po_item D, pu_po E
333 WHERE A.receive_goods_id = vRgId AND
334 A.receive_goods_id = B.receive_goods_id AND
335 B.receive_goods_item_id = C.receive_goods_item_id AND
336 B.ref_id = D.po_item_id AND
337 D.po_id = E.po_id;
338*
339 */
340 /*
341 * update product_balance_id dari in_product_balance
342 */
343 UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
344 FROM in_product_balance A
345 WHERE tt_pu_product_balance.session_id = pSessionId AND
346 tt_pu_product_balance.tenant_id = A.tenant_id AND
347 tt_pu_product_balance.product_id = A.product_id AND
348 tt_pu_product_balance.serial_number = A.serial_number AND
349 tt_pu_product_balance.lot_number = A.lot_number;
350
351 /*
352 * insert data ke in_product_balance yang product_balance_id masih empty
353 */
354 INSERT INTO in_product_balance
355 (tenant_id, product_id,
356 serial_number, lot_number, product_expired_date, product_year_made,
357 "version", create_datetime, create_user_id, update_datetime, update_user_id)
358 SELECT A.tenant_id, A.product_id,
359 A.serial_number, A.lot_number, A.product_expired_date, A.product_year_made,
360 0, vDatetime, vUserId, vDatetime, vUserId
361 FROM tt_pu_product_balance A
362 WHERE A.session_id = pSessionId AND
363 A.product_balance_id = vEmptyId
364 GROUP BY A.tenant_id, A.product_id, serial_number, lot_number, product_expired_date, product_year_made;
365
366 /*
367 * update product_balance_id yang masih empty, ambil dari in_product_balance yang sebelumnya sudah diinsert
368 */
369 UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
370 FROM in_product_balance A
371 WHERE tt_pu_product_balance.session_id = pSessionId AND
372 tt_pu_product_balance.product_balance_id = vEmptyId AND
373 tt_pu_product_balance.tenant_id = A.tenant_id AND
374 tt_pu_product_balance.product_id = A.product_id AND
375 tt_pu_product_balance.serial_number = A.serial_number AND
376 tt_pu_product_balance.lot_number = A.lot_number;
377
378 INSERT INTO in_product_price_balance
379 (tenant_id, ou_id, product_id, product_balance_id,
380 product_buy_date, partner_id, doc_type_id, ref_id, doc_no, doc_date,
381 curr_code, amount, qty, uom_id,
382 "version", create_datetime, create_user_id, update_datetime, update_user_id)
383 SELECT A.tenant_id, A.ou_id, A.product_id, A.product_balance_id,
384 A.doc_date, A.partner_id, A.doc_type_id, A.ref_item_id, A.doc_no, A.doc_date,
385 A.curr_code, SUM(f_get_amount_before_tax(A.qty_rcv * A.price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingModeNonTax)),
386 SUM(A.qty_int_rcv), A.base_uom_id,
387 0, vDatetime, vUserId, vDatetime, vUserId
388 FROM tt_pu_product_balance A, pu_po_item B
389 WHERE A.session_id = pSessionId AND
390 A.po_item_id = B.po_item_id
391 GROUP BY A.tenant_id, A.ou_id, A.product_id, A.product_balance_id, A.doc_date,
392 A.partner_id, A.doc_type_id, A.ref_item_id, A.doc_no, A.curr_code, A.base_uom_id;
393
394 UPDATE tt_pu_product_balance SET product_price_balance_id = A.product_price_balance_id
395 FROM in_product_price_balance A
396 WHERE tt_pu_product_balance.session_id = pSessionId AND
397 tt_pu_product_balance.tenant_id = A.tenant_id AND
398 tt_pu_product_balance.ou_id = A.ou_id AND
399 tt_pu_product_balance.product_id = A.product_id AND
400 tt_pu_product_balance.product_balance_id = A.product_balance_id AND
401 tt_pu_product_balance.partner_id = A.partner_id AND
402 tt_pu_product_balance.doc_type_id = A.doc_type_id AND
403 tt_pu_product_balance.ref_item_id = A.ref_id AND
404 tt_pu_product_balance.doc_no = A.doc_no AND
405 tt_pu_product_balance.doc_date = A.doc_date;
406
407 /*
408 * create summary dari tt_pu_product_balance untuk update yg sudah ada di in_product_balance_stock
409 */
410 INSERT INTO tt_pu_product_balance_summary_stock
411 (session_id, warehouse_id, product_balance_id, tenant_id, product_id,
412 product_price_balance_id, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
413 SELECT pSessionId, A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
414 vEmptyId, SUM(A.qty_rcv), A.po_uom_id, SUM(A.qty_int_rcv), A.base_uom_id, A.flg_stock
415 FROM tt_pu_product_balance A
416 WHERE A.session_id = pSessionId
417 GROUP BY A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
418 A.po_uom_id, A.base_uom_id, A.flg_stock;
419
420 /*
421 * update product_balance_stock, yang sudah ada di in_product_balance_stock
422 */
423 UPDATE in_product_balance_stock SET qty = qty + A.qty_int_rcv, update_datetime = vDatetime, update_user_id = vUserId,
424 version = version + 1
425 FROM tt_pu_product_balance_summary_stock A
426 WHERE A.session_id = pSessionId AND
427 in_product_balance_stock.product_id = A.product_id AND
428 in_product_balance_stock.tenant_id = A.tenant_id AND
429 in_product_balance_stock.warehouse_id = A.warehouse_id AND
430 in_product_balance_stock.product_balance_id = A.product_balance_id AND
431 in_product_balance_stock.product_status = vProductStatus AND
432 A.flg_stock = 'Y';
433
434 /*
435 * insert data in_product_balance_stock
436 */
437 INSERT INTO in_product_balance_stock
438 (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
439 "version", create_datetime, create_user_id, update_datetime, update_user_id)
440 SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, vProductStatus,
441 A.base_uom_id, SUM(A.qty_int_rcv),
442 0, vDatetime, vUserId, vDatetime, vUserId
443 FROM tt_pu_product_balance A
444 WHERE A.session_id = pSessionId AND
445 A.flg_stock = 'Y' AND
446 NOT EXISTS (SELECT 1 FROM in_product_balance_stock B
447 WHERE A.tenant_id = B.tenant_id AND
448 A.warehouse_id = B.warehouse_id AND
449 A.product_id = B.product_id AND
450 A.product_balance_id = B.product_balance_id AND
451 B.product_status = vProductStatus)
452 GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.base_uom_id;
453
454 /*
455 * insert data product_price_balance_stock
456 */
457 INSERT INTO in_product_price_balance_stock
458 (tenant_id, warehouse_id, product_id, product_balance_id,
459 product_price_balance_id, product_status, base_uom_id, qty,
460 "version", create_datetime, create_user_id, update_datetime, update_user_id)
461 SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id,
462 A.product_price_balance_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
463 0, vDatetime, vUserId, vDatetime, vUserId
464 FROM tt_pu_product_balance A
465 WHERE A.session_id = pSessionId AND
466 A.flg_stock = 'Y'
467 GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.product_price_balance_id, A.base_uom_id;
468
469 /*
470 * buat data log product balance stock
471 */
472 INSERT INTO in_log_product_balance_stock
473 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
474 product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
475 "version", create_datetime, create_user_id, update_datetime, update_user_id)
476 SELECT A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
477 A.product_id, A.product_balance_id, A.warehouse_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
478 0, vDatetime, vUserId, vDatetime, vUserId
479 FROM tt_pu_product_balance A, m_warehouse_ou B
480 WHERE A.session_id = pSessionId AND
481 A.flg_stock = 'Y' AND
482 A.warehouse_id = B.warehouse_id
483 GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
484 A.product_id, A.product_balance_id, A.warehouse_id, A.base_uom_id;
485
486 /*
487 * 7b. buat data log untuk update stok outlet jika ou ini adalah ou outlet
488 */
489 -- cek dulu warehouse ou nya
490 SELECT B.ou_id INTO vOuWarehouseId
491 FROM pu_receive_goods A
492 INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
493 WHERE receive_goods_id = vRgId;
494
495 IF vOuWarehouseId IS NULL THEN
496 vOuWarehouseId = vEmptyId;
497 END IF;
498
499 -- cek dulu apakah ou yang digunakan ini merupakan ou outlet
500 IF EXISTS(SELECT 1 FROM i_outlet A WHERE A.ou_id = vOuWarehouseId) THEN
501 SELECT A.outlet_id INTO vOutletId
502 FROM i_outlet A WHERE A.ou_id = vOuWarehouseId;
503 END IF;
504
505 IF vOutletId IS NULL THEN
506 vOutletId = vEmptyId;
507 END IF;
508
509 -- jika outlet, maka harus tulis data log agar outlet bisa lakukan update
510 IF vOutletId <> vEmptyId THEN
511 vDbVersion := f_get_value_system_config_by_param_code(pTenantId, 'DbVersion');
512
513 INSERT INTO i_trx_data_log(
514 tenant_id, ou_id, doc_date, db_version, type_data,
515 mode_log, data_log, version, create_datetime, create_user_id,
516 update_datetime, update_user_id)
517 SELECT B.tenant_id, B.ou_id, B.doc_date, vDbVersion, 'in_log_product_balance_stock',
518 'A', '{"tenantId":'||B.tenant_id||',"createDateTime":"'||B.create_datetime||'","baseUomId":'||B.base_uom_id||',"ouId":'||B.ou_id||',"docNo":"'||B.doc_no||'","refId":'||B.ref_id||',"partnerId":'||B.partner_id||',"qty":'||B.qty||',"warehouseId":'||B.warehouse_id||',"version":'||B.version||',"productId":'||B.product_id||',"updateUserId":'||B.update_user_id||',"productStatus":"'||B.product_status||'","docDate":"'||B.doc_date||'","updateDateTime":"'||B.update_datetime||'","productBalanceId":'||B.product_balance_id||',"createUserId":'||B.create_user_id||',"docTypeId":'||B.doc_type_id||'}',
519 0, vDatetime, vUserId, vDatetime, vUserId
520 FROM pu_receive_goods A
521 INNER JOIN in_log_product_balance_stock B
522 ON A.tenant_id = B.tenant_id
523 AND A.doc_no = B.doc_no
524 AND A.doc_date = B.doc_date
525 AND A.doc_type_id = B.doc_type_id
526 AND A.receive_goods_id = B.ref_id
527 AND A.warehouse_id = B.warehouse_id
528 WHERE A.receive_goods_id = vRgId;
529 END IF;
530
531 /*
532 * buat data log product price balance stock
533 */
534 INSERT INTO in_log_product_price_balance_stock
535 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
536 product_id, warehouse_id, product_balance_id, product_price_balance_id, product_status, base_uom_id, qty,
537 "version", create_datetime, create_user_id, update_datetime, update_user_id)
538 SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
539 A.product_id, A.warehouse_id, A.product_balance_id, A.product_price_balance_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
540 0, vDatetime, vUserId, vDatetime, vUserId
541 FROM tt_pu_product_balance A
542 WHERE A.session_id = pSessionId AND
543 A.flg_stock = 'Y'
544 GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
545 A.product_id, A.warehouse_id, A.product_balance_id, A.product_price_balance_id, A.base_uom_id;
546
547 /*
548 * add data balance receive goods item yang akan digunakan di inventory,
549 * saat akan membuat claim note
550 */
551 INSERT INTO in_balance_receive_goods_item
552 (receive_goods_item_id, tenant_id, ou_id, receive_goods_id, doc_no, doc_date, partner_id,
553 po_id, po_no, po_date, po_item_id,
554 qty_rcv, qty_return, po_uom_id, qty_int_rcv,
555 qty_int_return, base_uom_id, status_item,
556 "version", create_datetime, create_user_id, update_datetime, update_user_id)
557 SELECT A.ref_item_id, A.tenant_id, A.ou_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
558 A.po_id, A.po_no, A.po_date, A.po_item_id,
559 SUM(A.qty_rcv), 0, A.po_uom_id, SUM(A.qty_int_rcv),
560 0, A.base_uom_id, vStatusRelease,
561 0, vDatetime, vUserId, vDatetime, vUserId
562 FROM tt_pu_product_balance A
563 WHERE A.session_id = pSessionId
564 GROUP BY A.ref_item_id, A.tenant_id, A.ou_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
565 A.po_id, A.po_no, A.po_date, A.po_item_id, A.po_uom_id, A.base_uom_id;
566
567 UPDATE pu_receive_goods SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
568 WHERE receive_goods_id = vRgId;
569
570 UPDATE pu_po_balance_item SET status_item = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
571 FROM pu_receive_goods_item A
572 WHERE pu_po_balance_item.po_item_id = A.ref_id AND
573 pu_po_balance_item.tenant_id = A.tenant_id AND
574 A.receive_goods_id = vRgId AND
575 pu_po_balance_item.qty_po - pu_po_balance_item.qty_cancel + pu_po_balance_item.qty_add - pu_po_balance_item.qty_rcv > 0;
576
577 UPDATE pu_po_balance_item SET status_item = vStatusFinal, update_datetime = vDatetime, update_user_id = vUserId
578 FROM pu_receive_goods_item A
579 WHERE pu_po_balance_item.po_item_id = A.ref_id AND
580 pu_po_balance_item.tenant_id = A.tenant_id AND
581 A.receive_goods_id = vRgId AND
582 pu_po_balance_item.qty_po - pu_po_balance_item.qty_cancel + pu_po_balance_item.qty_add - pu_po_balance_item.qty_rcv <= 0;
583
584 SELECT COUNT(1) INTO vUnfinishedItem
585 FROM pu_po_balance_item A, pu_po_item B
586 WHERE A.po_item_id = B.po_item_id AND
587 B.po_id = vPoId AND
588 A.status_item = vStatusRelease;
589
590 IF vUnfinishedItem = 0 THEN
591 UPDATE pu_po SET status_doc = vStatusFinal, update_datetime = vDatetime, update_user_id = vUserId
592 WHERE po_id = vPoId;
593 END IF;
594
595 /*
596 * create monthly price product
597 */
598 INSERT INTO tt_pu_monthly_price_product
599 (session_id, tenant_id, ou_id, year_month_date,
600 product_id, curr_code, amount, qty, base_uom_id,
601 ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, source_price,
602 flg_tax_amount, tax_id, tax_percentage)
603 SELECT pSessionId, A.tenant_id, A.ou_id, C.year_month_date,
604 A.product_id, A.curr_code, SUM(f_get_amount_before_tax(A.qty_rcv * A.price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingModeNonTax)),
605 SUM(A.qty_int_rcv), A.base_uom_id,
606 A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
607 'PURCHASING PO NO : '|| A.po_no || ' DATE : '|| A.po_date,
608 B.flg_tax_amount, B.tax_id, B.tax_percentage
609 FROM tt_pu_product_balance A, pu_po_item B, dt_date C
610 WHERE A.session_id = pSessionId AND
611 A.po_item_id = B.po_item_id AND
612 A.doc_date = C.string_date
613 GROUP BY A.tenant_id, A.ou_id, C.year_month_date, A.product_id, A.curr_code, A.base_uom_id,
614 A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.po_no, A.po_date, B.flg_tax_amount, B.tax_id, B.tax_percentage;
615
616 UPDATE pu_monthly_price_product SET curr_code = A.curr_code, amount = A.amount,
617 qty = A.qty, ref_doc_type_id = A.ref_doc_type_id,
618 ref_id = A.ref_id, ref_doc_no = A.ref_doc_no, ref_doc_date = A.ref_doc_date,
619 source_price = A.source_price, flg_tax_amount = A.flg_tax_amount,
620 tax_id = A.tax_id, tax_percentage = A.tax_percentage
621 FROM tt_pu_monthly_price_product A
622 WHERE A.session_id = pSessionId AND
623 pu_monthly_price_product.tenant_id = A.tenant_id AND
624 pu_monthly_price_product.ou_id = A.ou_id AND
625 pu_monthly_price_product.year_month_date = A.year_month_date AND
626 pu_monthly_price_product.product_id = A.product_id;
627
628 INSERT INTO pu_monthly_price_product
629 (tenant_id, ou_id, year_month_date, product_id,
630 curr_code, amount, qty, base_uom_id, source_price,
631 ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
632 "version", create_datetime, create_user_id, update_datetime, update_user_id,
633 flg_tax_amount, tax_id, tax_percentage)
634 SELECT A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
635 A.curr_code, SUM(A.amount), SUM(A.qty), A.base_uom_id, A.source_price,
636 A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date,
637 0, vDatetime, vUserId, vDatetime, vUserId,
638 A.flg_tax_amount, A.tax_id, A.tax_percentage
639 FROM tt_pu_monthly_price_product A
640 WHERE A.session_id = pSessionId AND
641 NOT EXISTS (SELECT 1 FROM pu_monthly_price_product B
642 WHERE A.tenant_id = B.tenant_id AND
643 A.ou_id = B.ou_id AND
644 A.product_id = B.product_id AND
645 A.year_month_date = B.year_month_date)
646 GROUP BY A.tenant_id, A.ou_id, A.year_month_date, A.product_id, A.curr_code, A.base_uom_id, A.source_price,
647 A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date, A.flg_tax_amount, A.tax_id, A.tax_percentage;
648
649 INSERT INTO pu_log_monthly_price_product
650 (tenant_id, ou_id, year_month_date, product_id,
651 curr_code, amount, qty, base_uom_id, source_price,
652 ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
653 "version", create_datetime, create_user_id, update_datetime, update_user_id,
654 flg_tax_amount, tax_id, tax_percentage)
655 SELECT A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
656 A.curr_code, SUM(A.amount), SUM(A.qty), A.base_uom_id, A.source_price,
657 A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date,
658 0, vDatetime, vUserId, vDatetime, vUserId,
659 A.flg_tax_amount, A.tax_id, A.tax_percentage
660 FROM tt_pu_monthly_price_product A
661 WHERE A.session_id = pSessionId
662 GROUP BY A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
663 A.curr_code, A.base_uom_id, A.source_price, A.ref_doc_type_id, A.ref_id,
664 A.ref_doc_no, A.ref_doc_date, A.flg_tax_amount, A.tax_id, A.tax_percentage;
665
666 /*
667 * membuat data transaksi jurnal :
668 * 1. buat admin
669 * 2. buat temlate jurnal
670 */
671
672 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), 'DAILY', vDatetime, vUserId)
673 FROM pu_receive_goods A
674 WHERE A.receive_goods_id = vRgId;
675
676 SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
677
678 INSERT INTO gl_journal_trx
679 (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
680 ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
681 ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
682 "version", create_datetime, create_user_id, update_datetime, update_user_id)
683 SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.receive_goods_id, A.doc_no, A.doc_date,
684 (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
685 A.ref_doc_type_id, A.ref_id, A.doc_date, B.curr_code, A.remark, vStatusDraft, 'DRAFT',
686 0, vDatetime, vUserId, vDatetime, vUserId
687 FROM pu_receive_goods A, pu_po B
688 WHERE A.receive_goods_id = vRgId AND
689 A.ref_doc_type_id = B.doc_type_id AND
690 A.ref_id = B.po_id;
691
692 INSERT INTO tt_journal_trx_item
693 (session_id, tenant_id, journal_trx_id, line_no,
694 ref_doc_type_id, ref_id,
695 partner_id, product_id, cashbank_id, ou_rc_id,
696 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
697 coa_id, curr_code, qty, uom_id,
698 amount, journal_date, type_rate,
699 numerator_rate, denominator_rate, journal_desc, remark)
700 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
701 A.doc_type_id, B.receive_goods_item_id,
702 A.partner_id, B.product_id, vEmptyId, C.ou_rc_id,
703 vEmptyId, vSignDebit, vProductCOA, C.activity_gl_id,
704 C.product_coa_id, C.curr_code, B.qty_rcv_int, B.base_uom_id,
705 f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage,f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax),
706 A.doc_date, vTypeRate,
707 1, 1, 'PRODUCT_STOCK', B.remark
708 FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
709 WHERE A.receive_goods_id = vRgId AND
710 A.receive_goods_id = B.receive_goods_id AND
711 B.ref_id = C.po_item_id AND
712 C.flg_stock = 'Y';
713
714 INSERT INTO tt_journal_trx_item
715 (session_id, tenant_id, journal_trx_id, line_no,
716 ref_doc_type_id, ref_id,
717 partner_id, product_id, cashbank_id, ou_rc_id,
718 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
719 coa_id, curr_code, qty, uom_id,
720 amount, journal_date, type_rate,
721 numerator_rate, denominator_rate, journal_desc, remark)
722 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
723 A.doc_type_id, B.receive_goods_item_id,
724 A.partner_id, B.product_id, vEmptyId, C.ou_rc_id,
725-- NK, 30 Agustus 2014 - add segment_id
726 C.segment_id, vSignDebit, vActivityCOA, C.activity_gl_id,
727 E.coa_id, C.curr_code, B.qty_rcv_int, B.base_uom_id,
728 f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax),
729 A.doc_date, vTypeRate,
730 1, 1, 'PRODUCT_NON_STOCK', B.remark
731 FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, m_activity_gl E
732 WHERE A.receive_goods_id = vRgId AND
733 A.receive_goods_id = B.receive_goods_id AND
734 B.ref_id = C.po_item_id AND
735 C.flg_stock = 'N' AND
736 C.activity_gl_id = E.activity_gl_id;
737
738
739 INSERT INTO gl_journal_trx_item
740 (tenant_id, journal_trx_id, line_no,
741 ref_doc_type_id, ref_id,
742 partner_id, product_id, cashbank_id, ou_rc_id,
743 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
744 coa_id, curr_code, qty, uom_id,
745 amount, journal_date, type_rate,
746 numerator_rate, denominator_rate, journal_desc, remark,
747 "version", create_datetime, create_user_id, update_datetime, update_user_id)
748 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
749 A.ref_doc_type_id, A.ref_id,
750 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
751 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
752 A.coa_id, A.curr_code, A.qty, A.uom_id,
753 A.amount, A.journal_date, A.type_rate,
754 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
755 0, vDatetime, vUserId, vDatetime, vUserId
756 FROM tt_journal_trx_item A
757 WHERE A.session_id = pSessionId;
758
759 INSERT INTO gl_journal_trx_mapping
760 (tenant_id, journal_trx_id, line_no,
761 ref_doc_type_id, ref_id,
762 partner_id, product_id, cashbank_id, ou_rc_id,
763 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
764 coa_id, curr_code, qty, uom_id,
765 amount, journal_date, type_rate,
766 numerator_rate, denominator_rate, journal_desc, remark,
767 "version", create_datetime, create_user_id, update_datetime, update_user_id)
768 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
769 vEmptyId, vEmptyId,
770 A.partner_id, vEmptyId, vEmptyId, vEmptyId,
771 vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
772 f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId,
773 SUM(A.amount), A.journal_date, A.type_rate,
774 1, 1, 'ACCR_AP', vEmptyValue,
775 0, vDatetime, vUserId, vDatetime, vUserId
776 FROM tt_journal_trx_item A
777 WHERE A.session_id = pSessionId
778 GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate;
779
780 /*
781 * Modify Sarah, 27 Des 2016
782 * table:pu_po_balance_invoice_ext_invoice_doc_type, add data ke table baru
783 *
784 */
785
786 INSERT INTO pu_po_balance_invoice_ext_invoice_doc_type
787 (tenant_id, ou_id, po_id, ref_doc_type_id, ref_id,
788 ref_item_id, invoice_id, invoice_doc_type_id,
789 "version", create_datetime, create_user_id, update_datetime, update_user_id)
790 SELECT A.tenant_id, A.ou_id, A.ref_id, A.doc_type_id, A.receive_goods_id,
791 B.receive_goods_item_id, vEmptyId, vEmptyId,
792 0, vDatetime, vUserId, vDatetime, vUserId
793 FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
794 WHERE A.receive_goods_id = vRgId AND
795 A.receive_goods_id = B.receive_goods_id AND
796 B.ref_id = C.po_item_id;
797
798 INSERT INTO pu_po_balance_invoice_tax_ext
799 (tenant_id, ou_id, po_id, ref_doc_type_id, ref_id,
800 ref_item_id, tax_id, invoice_id, invoice_doc_type_id,
801 "version", create_datetime, create_user_id, update_datetime, update_user_id)
802 SELECT A.tenant_id, A.ou_id, A.ref_id, A.doc_type_id, A.receive_goods_id,
803 B.receive_goods_item_id, C.tax_id, vEmptyId, vEmptyId,
804 0, vDatetime, vUserId, vDatetime, vUserId
805 FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, m_tax D
806 WHERE A.receive_goods_id = vRgId AND
807 A.receive_goods_id = B.receive_goods_id AND
808 B.ref_id = C.po_item_id AND
809 C.tax_id = D.tax_id;
810
811
812 DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;
813 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
814 DELETE FROM tt_pu_product_balance_summary_stock WHERE session_id = pSessionId;
815 DELETE FROM tt_pu_monthly_price_product WHERE session_id = pSessionId;
816END;
817$BODY$
818 LANGUAGE plpgsql VOLATILE
819 COST 100;
820
821/