[Home] [Help]
PACKAGE BODY: APPS.JAI_FBT_SETTLEMENT_P
Source
1 PACKAGE BODY JAI_FBT_SETTLEMENT_P AS
2 --$Header: jainfbtset.plb 120.2 2008/06/04 02:28:20 jianliu noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| jai_fbt_settlement_p.pls |
10 --| |
11 --| DESCRIPTION |
12 --| To be called by the concurrent program for inserting the |
13 --| data into jai_fbt_settlement table and ap interface tables |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Insert_Fbt_Settlement |
17 --| PROCEDURE Insert_Interface_Table |
18 --| PROCEDURE Fbt_Settlement |
19 --| |
20 --| HISTORY |
21 --| 2007/10/18 Jason Liu Created |
22 --| |
23 --+======================================================================*/
24
25 --==========================================================================
26 -- PROCEDURE NAME:
27 --
28 -- Insert_Fbt_Settlement Private
29 --
30 -- DESCRIPTION:
31 --
32 -- This procedure is to insert the data into jai_fbt_settlement
33 --
34 -- PARAMETERS:
35 -- In: p_fbt_settlement jai_fbt_repository record type
36 --
37 -- DESIGN REFERENCES:
38 -- FBT Technical Design Document 1.1.doc
39 --
40 -- CHANGE HISTORY:
41 --
42 -- 18-OCT-2007 Jason Liu created
43
44 PROCEDURE Insert_Fbt_Settlement
45 ( p_fbt_settlement IN jai_fbt_settlement%ROWTYPE
46 )
47 IS
48 lv_procedure_name VARCHAR2(40):='Insert_Fbt_Settlement';
49 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
50 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
51
52 BEGIN
53 --logging for debug
54 IF (ln_proc_level >= ln_dbg_level)
55 THEN
56 FND_LOG.STRING( ln_proc_level
57 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
58 , 'Enter procedure'
59 );
60 END IF; --l_proc_level>=l_dbg_level
61
62 -- insert into jai_fbt_settlement
63 INSERT INTO jai_fbt_settlement
64 ( legal_entity_id
65 , period_start_date
66 , period_end_date
67 , consolidated_amount
68 , projected_amount
69 , settlement_date
70 , inv_supplier_id
71 , inv_supplier_site_id
72 , inv_ou_id
73 , it_challan
74 , return_generate_flag
75 , creation_date
76 , created_by
77 , last_update_date
78 , last_updated_by
79 , last_update_login
80 , deposit_date
81 , bank_name
82 , branch_name
83 , settlement_id
84 )
85 VALUES ( p_fbt_settlement.legal_entity_id
86 , p_fbt_settlement.period_start_date
87 , p_fbt_settlement.period_end_date
88 , p_fbt_settlement.consolidated_amount
89 , p_fbt_settlement.projected_amount
90 , p_fbt_settlement.settlement_date
91 , p_fbt_settlement.inv_supplier_id
92 , p_fbt_settlement.inv_supplier_site_id
93 , p_fbt_settlement.inv_ou_id
94 , p_fbt_settlement.it_challan
95 , p_fbt_settlement.return_generate_flag
96 , p_fbt_settlement.creation_date
97 , p_fbt_settlement.created_by
98 , p_fbt_settlement.last_update_date
99 , p_fbt_settlement.last_updated_by
100 , p_fbt_settlement.last_update_login
101 , p_fbt_settlement.deposit_date
102 , p_fbt_settlement.bank_name
103 , p_fbt_settlement.branch_name
104 , p_fbt_settlement.settlement_id
105 );
106
107 --logging for debug
108 IF (ln_proc_level >= ln_dbg_level)
109 THEN
110 FND_LOG.STRING( ln_proc_level
111 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
112 , 'Exit procedure'
113 );
114 END IF; -- (ln_proc_level>=ln_dbg_level)
115
116 EXCEPTION
117 WHEN OTHERS THEN
118 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
119 THEN
120 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
121 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
122 , Sqlcode||Sqlerrm);
123 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
124 END Insert_Fbt_Settlement;
125
126 --==========================================================================
127 -- PROCEDURE NAME:
128 --
129 -- Insert_Interface_Table Private
130 --
131 -- DESCRIPTION:
132 --
133 -- This procedure is to ap_invoices_interface,
134 -- ap_invoice_lines_interface tables
135 --
136 -- PARAMETERS:
137 -- In: p_inv_interface inv_interface_rec_type record type
138 -- p_inv_lines_interface inv_lines_interface_rec_type record type
139 --
140 -- DESIGN REFERENCES:
141 -- FBT Technical Design Document 1.1.doc
142 --
143 -- CHANGE HISTORY:
144 --
145 -- 18-OCT-2007 Jason Liu created
146
147 PROCEDURE Insert_Interface_Table
148 ( p_inv_interface IN inv_interface_rec_type
149 , p_inv_lines_interface IN inv_lines_interface_rec_type
150 )
151 IS
152 lv_procedure_name VARCHAR2(40):='Insert_Interface_Table';
153 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
154 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
155
156 BEGIN
157 --logging for debug
158 IF (ln_proc_level >= ln_dbg_level)
159 THEN
160 FND_LOG.STRING( ln_proc_level
161 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
162 , 'Enter procedure'
163 );
164 END IF; --l_proc_level>=l_dbg_level
165
166 -- insert into ap_invoices_interface
167 INSERT INTO ap_invoices_interface
168 ( invoice_id
169 , invoice_num
170 , invoice_date
171 , vendor_id
172 , vendor_site_id
173 , invoice_amount
174 , invoice_currency_code
175 , accts_pay_code_combination_id
176 , source
177 , org_id
178 , legal_entity_id
179 , payment_method_lookup_code
180 , created_by
181 , creation_date
182 , last_updated_by
183 , last_update_date
184 , last_update_login
185 )
186 VALUES ( p_inv_interface.invoice_id
187 , p_inv_interface.invoice_num
188 , p_inv_interface.invoice_date
189 , p_inv_interface.vendor_id
190 , p_inv_interface.vendor_site_id
191 , p_inv_interface.invoice_amount
192 , p_inv_interface.invoice_currency_code
193 , p_inv_interface.accts_pay_ccid
194 , p_inv_interface.source
195 , p_inv_interface.org_id
196 , p_inv_interface.legal_entity_id
197 , p_inv_interface.payment_method_lookup_code
198 , p_inv_interface.created_by
199 , p_inv_interface.creation_date
200 , p_inv_interface.last_updated_by
201 , p_inv_interface.last_update_date
202 , p_inv_interface.last_update_login
203 );
204
205 -- insert into ap_invoice_lines_interface
206 INSERT INTO ap_invoice_lines_interface
207 ( invoice_id
208 , invoice_line_id
209 , line_number
210 , line_type_lookup_code
211 , amount
212 , accounting_date
213 , description
214 , dist_code_combination_id
215 , org_id
216 , created_by
217 , creation_date
218 , last_updated_by
219 , last_update_date
220 , last_update_login
221 )
222 VALUES ( p_inv_lines_interface.invoice_id
223 , p_inv_lines_interface.invoice_line_id
224 , p_inv_lines_interface.line_number
225 , p_inv_lines_interface.line_type_lookup_code
226 , p_inv_lines_interface.amount
227 , p_inv_lines_interface.accounting_date
228 , p_inv_lines_interface.description
229 , p_inv_lines_interface.dist_code_combination_id
230 , p_inv_lines_interface.org_id
231 , p_inv_lines_interface.created_by
232 , p_inv_lines_interface.creation_date
233 , p_inv_lines_interface.last_updated_by
234 , p_inv_lines_interface.last_update_date
235 , p_inv_lines_interface.last_update_login
236 );
237 --logging for debug
238 IF (ln_proc_level >= ln_dbg_level)
239 THEN
240 FND_LOG.STRING( ln_proc_level
241 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
242 , 'Exit procedure'
243 );
244 END IF; -- (ln_proc_level>=ln_dbg_level)
245
246 EXCEPTION
247 WHEN OTHERS THEN
248 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
249 THEN
250 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
251 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
252 , Sqlcode||Sqlerrm);
253 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
254 END Insert_Interface_Table;
255
256 --==========================================================================
257 -- PROCEDURE NAME:
258 --
259 -- Fbt_Settlement Public
260 --
261 -- DESCRIPTION:
262 --
263 -- This is the main procedure which will be called by the concurrent
264 -- program for inserting the data into jai_fbt_settlement table and
265 -- ap interface tables
266 --
267 -- PARAMETERS:
268 -- In: pn_legal_entity_id Identifier of legal entity
269 -- pv_start_date Identifier of period start date
270 -- pv_end_date Identifier of period end date
271 -- pn_projected_amount Identifier of projected FBT amount
272 -- pn_supplier_id Identifier of supplier id
273 -- pn_supplier_site_id Identifier of supplier site id
274 --
275 -- Out: pv_errbuf Returns the error if concurrent program
276 -- does not execute completely
277 -- pv_retcode Returns success or failure
278 --
279 -- DESIGN REFERENCES:
280 -- FBT Technical Design Document 1.1.doc
281 --
282 -- CHANGE HISTORY:
283 --
284 -- 18-OCT-2007 Jason Liu created
285 -- 04-Jun-2008 Jia Li Modified for bug#7146038
286 -- Issue: Running settlement for different legal entity with one Period,
287 -- the invoice number in AP interface was the same.
288 -- Fixed: Modified invoice number to 'FBT/Invoice/legal_entity_id/end_date'
289
290 PROCEDURE Fbt_Settlement
291 ( pv_errbuf OUT NOCOPY VARCHAR2
292 , pv_retcode OUT NOCOPY VARCHAR2
293 , pn_legal_entity_id IN jai_fbt_settlement.legal_entity_id%TYPE
294 , pv_start_date IN VARCHAR2
295 , pv_end_date IN VARCHAR2
296 , pn_projected_amount IN jai_fbt_settlement.Projected_Amount%TYPE
297 , pn_supplier_id IN jai_fbt_settlement.inv_supplier_id%TYPE
298 , pn_supplier_site_id IN jai_fbt_settlement.inv_supplier_site_id%TYPE
299 )
300 IS
301 lv_start_date VARCHAR2(30);
302 lv_end_date VARCHAR2(30);
303 ld_start_date DATE;
304 ld_end_date DATE;
305 lv_jan VARCHAR2(2);
306 lv_return_generate_flag VARCHAR2(1);
307 ln_org_id NUMBER;
308 lv_settled_flag VARCHAR2(1);
309 ln_settlement_id NUMBER;
310 ln_consolidated_amount NUMBER;
311 ln_invoice_amout NUMBER;
312 fbt_settlement_rec jai_fbt_settlement%ROWTYPE;
313 ln_invoice_id ap_invoices_interface.invoice_id%TYPE;
314 ln_invoice_num ap_invoices_interface.invoice_num%TYPE;
315 lv_currency_code ap_invoices_interface.invoice_currency_code%TYPE;
316 ln_accts_pay_ccid ap_invoices_interface.accts_pay_code_combination_id%TYPE;
317 lv_payment_method_code ap_invoices_interface.payment_method_lookup_code%TYPE;
318 ln_invoice_line_id ap_invoice_lines_interface.invoice_line_id%TYPE;
319 inv_interface_rec inv_interface_rec_type;
320 inv_lines_interface_rec inv_lines_interface_rec_type;
321 ln_user_id NUMBER := fnd_global.user_id;
322 ln_login_id NUMBER := fnd_global.login_id;
323 ln_precision NUMBER;
324 lv_procedure_name VARCHAR2(40):='Fbt_Settlement';
325 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
326 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
327
328 CURSOR cummulative_tax_cur IS
329 SELECT SUM(fbt_tax_amount) + SUM(fbt_surcharge_amount)
330 + SUM(fbt_edu_cess_amount) + SUM(fbt_sh_cess_amount)
331 FROM jai_fbt_repository
332 WHERE legal_entity_id = pn_legal_entity_id
333 AND period_start_date = ld_start_date
334 AND period_end_date = ld_end_date;
335
336 -- Check if settled for this period
337 CURSOR check_settled IS
338 SELECT 1
339 FROM dual
340 WHERE EXISTS( SELECT 1
341 FROM jai_fbt_settlement
342 WHERE legal_entity_id = pn_legal_entity_id
343 AND period_start_date = ld_start_date
344 AND period_end_date = ld_end_date);
345
346 CURSOR po_vendors_cur IS
347 SELECT
348 pvsa.org_id
349 , pvsa.accts_pay_code_combination_id
350 , pvsa.payment_method_lookup_code
351 FROM po_vendor_sites_all pvsa
352 WHERE pvsa.vendor_site_id = pn_supplier_site_id;
353
354 CURSOR currency_code_cur IS
355 SELECT
356 gsob.currency_code
357 FROM
358 gl_sets_of_books gsob
359 , xle_fp_ou_ledger_v xfolv
360 WHERE gsob.set_of_books_id = xfolv.ledger_id
361 AND xfolv.legal_entity_id = pn_legal_entity_id;
362
363 BEGIN
364 --logging for debug
365 IF (ln_proc_level >= ln_dbg_level)
366 THEN
367 FND_LOG.STRING( ln_proc_level
368 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
369 , 'Enter procedure'
370 );
371 END IF; --l_proc_level>=l_dbg_level
372
373 lv_start_date := pv_start_date;
374 lv_end_date := pv_end_date;
375 -- change from VARCHAR2 to DATE type
376 ld_start_date := TO_DATE(lv_start_date, GV_DATE_MASK);
377 ld_end_date := TO_DATE(lv_end_date, GV_DATE_MASK);
378
379 OPEN check_settled;
380 FETCH check_settled
381 INTO lv_settled_flag;
382 CLOSE check_settled;
383
384 IF(NVL(lv_settled_flag, 0) = 1) THEN
385 FND_FILE.PUT_LINE(FND_FILE.LOG,
386 'Already run the settlement for this period.');
387 ELSE
388 -- get the precision
389 SELECT
390 NVL(fc.precision, 2)
391 INTO
392 ln_precision
393 FROM
394 gl_sets_of_books gsob
395 , fnd_currencies fc
396 WHERE gsob.set_of_books_id = fnd_profile.VALUE('GL_SET_OF_BKS_ID')
397 AND gsob.currency_code = fc.currency_code;
398
399 SELECT
400 TO_CHAR(ld_start_date,'MM')
401 INTO
402 lv_jan
403 FROM
404 dual;
405 -- get the return_generate_flag
406 IF (lv_jan = '01')
407 THEN
408 lv_return_generate_flag := 'Y';
409 ELSE
410 lv_return_generate_flag := 'N';
411 END IF; -- ((TO_CHAR(lv_constant_date,'MM')) = '01')
412
413 -- get the consolidated_amount
414 OPEN cummulative_tax_cur;
415 FETCH cummulative_tax_cur
416 INTO ln_consolidated_amount;
417 CLOSE cummulative_tax_cur;
418
419 -- get the org id, accts_pay_code_combination_id, payment_method_lookup_code
420 OPEN po_vendors_cur;
421 FETCH po_vendors_cur
422 INTO
423 ln_org_id
424 , ln_accts_pay_ccid
425 , lv_payment_method_code;
426 CLOSE po_vendors_cur;
427
428 -- get the settlement id
429 SELECT jai_fbt_settlement_s.NEXTVAL
430 INTO ln_settlement_id
431 FROM dual;
432
433 -- insert the data into the table jai_fbt_settlement
434 fbt_settlement_rec.legal_entity_id := pn_legal_entity_id;
435 fbt_settlement_rec.period_start_date := ld_start_date;
436 fbt_settlement_rec.period_end_date := ld_end_date;
437 fbt_settlement_rec.consolidated_amount := NVL(ln_consolidated_amount, 0);
438 IF (lv_jan = '01')
439 THEN
440 fbt_settlement_rec.projected_amount := ROUND(NVL(pn_projected_amount, 0), ln_precision);
441 ELSE
442 FND_FILE.PUT_LINE(FND_FILE.LOG,
443 'Projected FBT Amount will be considered only for the period commencing from 01-JAN.');
444 END IF; -- (lv_jan = '01')
445 fbt_settlement_rec.settlement_date := SYSDATE;
446 fbt_settlement_rec.inv_supplier_id := pn_supplier_id;
447 fbt_settlement_rec.inv_supplier_site_id := pn_supplier_site_id;
448 fbt_settlement_rec.inv_ou_id := ln_org_id;
449 fbt_settlement_rec.it_challan := NULL;
450 fbt_settlement_rec.return_generate_flag := lv_return_generate_flag;
451 fbt_settlement_rec.creation_date := SYSDATE;
452 fbt_settlement_rec.created_by := ln_user_id;
453 fbt_settlement_rec.last_update_date := SYSDATE;
454 fbt_settlement_rec.last_updated_by := ln_user_id;
455 fbt_settlement_rec.last_update_login := ln_login_id;
456 fbt_settlement_rec.settlement_id := ln_settlement_id;
457
458 Insert_Fbt_Settlement(fbt_settlement_rec);
459
460 -- update all the transactions in the jai_fbt_repository
461 -- with the settlement_id created above
462
463 UPDATE jai_fbt_repository
464 SET settlement_id = ln_settlement_id
465 WHERE legal_entity_id = pn_legal_entity_id
466 AND period_start_date = ld_start_date
467 AND period_end_date = ld_end_date;
468
469 -- insert the data into the table ap_invoices_interface and
470 -- ap_invoice_lines_interface
471 -- get invoice id
472 SELECT ap_invoices_interface_s.nextval
473 INTO ln_invoice_id
474 FROM dual;
475
476 -- get invoice num
477 -- SELECT 'FBT/Invoice/' || TO_CHAR(ld_end_date)
478 SELECT 'FBT/Invoice/'||pn_legal_entity_id||'/'|| TO_CHAR(ld_end_date) -- Modified by jia for bug#7146038 on 2008/06/04
479 INTO ln_invoice_num
480 FROM dual;
481
482 -- get currency code
483 OPEN currency_code_cur;
484 FETCH currency_code_cur
485 INTO lv_currency_code;
486 CLOSE currency_code_cur;
487
488 IF (lv_jan = '01')
489 THEN
490 ln_invoice_amout := ln_consolidated_amount + NVL(pn_projected_amount, 0);
491 ELSE
492 ln_invoice_amout := ln_consolidated_amount;
493 END IF; --(lv_jan = '01')
494 ln_invoice_amout := ROUND(ln_invoice_amout, ln_precision);
495 inv_interface_rec.invoice_id := ln_invoice_id;
496 inv_interface_rec.invoice_num := ln_invoice_num;
497 inv_interface_rec.invoice_date := SYSDATE;
498 inv_interface_rec.vendor_id := pn_supplier_id;
499 inv_interface_rec.vendor_site_id := pn_supplier_site_id;
500 inv_interface_rec.invoice_amount := ln_invoice_amout;
501 inv_interface_rec.invoice_currency_code := lv_currency_code;
502 inv_interface_rec.accts_pay_ccid := ln_accts_pay_ccid;
503 inv_interface_rec.source := 'FBT';
504 inv_interface_rec.org_id := ln_org_id;
505 inv_interface_rec.legal_entity_id := pn_legal_entity_id;
506 inv_interface_rec.payment_method_lookup_code := lv_payment_method_code;
507 inv_interface_rec.creation_date := SYSDATE;
508 inv_interface_rec.created_by := ln_user_id;
509 inv_interface_rec.last_update_date := SYSDATE;
510 inv_interface_rec.last_updated_by := ln_user_id;
511 inv_interface_rec.last_update_login := ln_login_id;
512
513 -- ap_invoice_lines_interface
514 -- get invoice line id
515 SELECT ap_invoice_lines_interface_s.nextval
516 INTO ln_invoice_line_id
517 FROM dual;
518
519 inv_lines_interface_rec.invoice_id := ln_invoice_id;
520 inv_lines_interface_rec.invoice_line_id := ln_invoice_line_id;
521 inv_lines_interface_rec.line_number := 1;
522 inv_lines_interface_rec.line_type_lookup_code := 'ITEM';
523 inv_lines_interface_rec.amount := ln_invoice_amout;
524 inv_lines_interface_rec.accounting_date := SYSDATE;
525 inv_lines_interface_rec.description := 'Invoice for FBT Payment';
526 inv_lines_interface_rec.dist_code_combination_id := ln_accts_pay_ccid;
527 inv_lines_interface_rec.org_id := ln_org_id;
528 inv_lines_interface_rec.creation_date := SYSDATE;
529 inv_lines_interface_rec.created_by := ln_user_id;
530 inv_lines_interface_rec.last_update_date := SYSDATE;
531 inv_lines_interface_rec.last_updated_by := ln_user_id;
532 inv_lines_interface_rec.last_update_login := ln_login_id;
533
534 Insert_Interface_Table( inv_interface_rec
535 , inv_lines_interface_rec
536 );
537 END IF; --(NVL(lv_settled_flag, 0) = 1)
538
539 --logging for debug
540 IF (ln_proc_level >= ln_dbg_level)
541 THEN
542 FND_LOG.STRING( ln_proc_level
543 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
544 , 'Exit procedure'
545 );
546 END IF; -- (ln_proc_level>=ln_dbg_level)
547
548 EXCEPTION
549 WHEN OTHERS THEN
550 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
551 THEN
552 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
553 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
554 , Sqlcode||Sqlerrm);
555 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
556 END Fbt_Settlement;
557
558 END JAI_FBT_SETTLEMENT_P;