[Home] [Help]
PACKAGE BODY: APPS.JAI_FBT_PROCESS_P
Source
1 PACKAGE BODY JAI_FBT_PROCESS_P AS
2 --$Header: jainfbtprc.plb 120.2.12010000.5 2008/12/31 06:40:37 huhuliu ship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| jai_fbt_process_p.plb |
10 --| |
11 --| DESCRIPTION |
12 --| To fetch eligible ap invoices and gl journals for FBT assessment |
13 --| and calculate the tax and insert data into jai_fbt_repository |
14 --| table |
15 --| |
16 --| PROCEDURE LIST |
17 --| PROCEDURE Fbt_Inv_Process |
18 --| PROCEDURE Insert_Fbt_Repository |
19 --| PROCEDURE Calculate_Fbt_Amount |
20 --| FUNCTION Check_Inv_Validation |
21 --| FUNCTION Get_Natural_Acc_Seg |
22 --| FUNCTION Get_Balance_Acc_Seg |
23 --| FUNCTION currency_conversion |
24 --| |
25 --| HISTORY |
26 --| 2007/10/11 Kevin Cheng Created |
27 --| 2008/03/21 Kevin Cheng bug#6908012 |
28 --| Add legal entity criteria for illegible invoice |
29 --| fetching. |
30 --| 2008/07/22 Eric Ma Code change for 11.5 backport |
31 --| 2008/07/29 Eric Ma Code change for 11.5 backport |
32 --| 2008/08/11 Xiao Lv Code change for 11i new changes |
33 --| 2008/08/25 Xiao Lv Fixing bug#7347306, bug#7347401 |
34 --| Commented this piece of code in Fbt_Inv_process |
35 --| UPDATE jai_fbt_repository |
36 --| SET settlement_id = NULL |
37 --| WHERE legal_entity_id = pn_legal_entity_id |
38 --| AND period_start_date >= ld_start_date |
39 --| AND period_end_date <= ld_end_date |
40 --| AND settlement_id IS NOT NULL; |
41 --| Commented the benifit_type_code query condition of |
42 --| judging settlement flag |
43 --| 2008/11/06 Xiao Lv Code change for FBT new changes in R12 |
44 --| 2008/12/23 Xiao Lv Fixing bug#7661991 |
45 --| 2008/12/26 Xiao Lv Fixing bug#7670949 |
46 --| 2008/12/30 Jia Li Fixing bug#7675638 |
47 --+======================================================================*/
48
49 --==========================================================================
50 -- FUNCTION NAME:
51 --
52 -- currency_conversion Private
53 --
54 -- DESCRIPTION:
55 --
56 -- As jai_cmn_utils_pkg.currency_conversion is not available in the IL 11.5
57 -- This function is added for compalibity with R12 FBT code.
58 --
59 -- PARAMETERS:
60 -- In: c_set_of_books_id sob id
61 -- In: c_from_currency_code currency code
62 -- In: c_conversion_date currency conversion date
63 -- In: c_conversion_type currency conversion type
64 -- In: c_conversion_rate currency conversion rate
65 --
66 -- DESIGN REFERENCES:
67 --
68 --
69 -- CHANGE HISTORY:
70 --
71 -- 17-JUL-2008 Eric Ma created
72
73
74 FUNCTION currency_conversion
75 (
76 c_set_of_books_id IN NUMBER
77 ,c_from_currency_code IN VARCHAR2
78 ,c_conversion_date IN DATE
79 ,c_conversion_type IN VARCHAR2
80 ,c_conversion_rate IN NUMBER
81 ) RETURN NUMBER IS
82 v_func_curr VARCHAR2(15);
83 ret_value NUMBER;
84
85 CURSOR currency_code_cur IS
86 SELECT currency_code
87 FROM gl_sets_of_books
88 WHERE set_of_books_id = c_set_of_books_id;
89
90 /* Added by Ramananda for bug#4407165 */
91 lv_object_name CONSTANT VARCHAR2(61) := 'JAI_FBT_PROCESS_P.currency_conversion';
92
93 lv_procedure_name VARCHAR2(40) := 'currency_conversion';
94 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
95 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
96
97 BEGIN
98
99 --logging for debug
100 IF (ln_proc_level >= ln_dbg_level)
101 THEN
102 FND_LOG.STRING( ln_proc_level
103 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
104 , 'Enter Function'
105 );
106
107 END IF; --l_proc_level>=l_dbg_level
108
109 -- Bug 5148770. Added by Lakshmi Gopalsami
110
111 --print_log('jai_cmn_utils_pkg.currency_conversion.log',' SOB'|| c_set_of_books_id);
112
113 OPEN currency_code_cur;
114 FETCH currency_code_cur
115 INTO v_func_curr;
116 CLOSE currency_code_cur;
117
118 -- Bug 5148770. Added by Lakshmi Gopalsami
119
120 --print_log('jai_cmn_utils_pkg.currency_conversion.log',' Func curr '|| v_func_curr);
121 --print_log('jai_cmn_utils_pkg.currency_conversion.log', 'FROM curr code '|| c_from_currency_code);
122
123 IF NVL(v_func_curr,'NO') = c_from_currency_code
124 THEN
125 -- Bug 5148770. Added by Lakshmi Gopalsami
126 --print_log('jai_cmn_utils_pkg.currency_conversion.log',' func curr and from curr same - return 1');
127
128 ret_value := 1;
129
130 ELSIF upper(c_conversion_type) = 'USER'
131 THEN
132 -- Bug 5148770. Added by Lakshmi Gopalsami
133 --print_log('jai_cmn_utils_pkg.currency_conversion.log',' User entered the rate - return ' || c_conversion_rate);
134 ret_value := c_conversion_rate;
135 ELSE
136
137 DECLARE
138
139 v_frm_curr VARCHAR2(10) := c_from_currency_code; -- added by Subbu, Sri on 02-NOV-2000
140
141 v_dr_type VARCHAR2(20); -- added by Subbu, Sri on 02-NOV-2000
142
143 -- Cursor for checking currency whether derived from Euro Derived / Euro Currency or not
144 -- added by Subbu, Sri on 02-NOV-2000
145
146 CURSOR Chk_Derived_Type_Cur IS
147 SELECT Derive_type
148 FROM Fnd_Currencies
149 WHERE Currency_Code IN (v_frm_curr);
150 /* Bug 5148770. Added by Lakshmi Gopalsami
151 Changed the select to get the rate into cursor.
152 */
153 CURSOR get_curr_rate(p_to_curr IN VARCHAR2, p_from_curr IN VARCHAR2) IS
154 SELECT Conversion_Rate
155 FROM Gl_Daily_Rates
156 WHERE To_Currency = p_to_curr
157 AND From_Currency = p_from_curr
158 AND trunc(Conversion_Date) = trunc(nvl(c_conversion_date,SYSDATE))
159 AND Conversion_Type = c_conversion_type;
160 BEGIN
161
162 OPEN Chk_Derived_Type_Cur;
163 FETCH Chk_Derived_Type_Cur
164 INTO v_dr_type;
165 CLOSE Chk_Derived_Type_Cur;
166
167 -- Bug 5148770. Added by Lakshmi Gopalsami
168 --print_log('jai_cmn_utils_pkg.currency_conversion.log',' derived type ' || v_dr_type);
169
170 IF v_dr_type IS NULL
171 THEN
172
173 -- If currency is not derived from Euro derived / Euro Currency by Subbu, Sri on 02-NOV-2000
174 /* Bug 5148770. Added by Lakshmi Gopalsami
175 Removed the select and changed the same into a cursor.
176 */
177 OPEN get_curr_rate(v_func_curr,v_frm_curr);
178 FETCH get_curr_rate
179 INTO ret_value;
180 CLOSE get_curr_rate;
181
182 -- Bug 5148770. Added by Lakshmi Gopalsami
183 --print_log('jai_cmn_utils_pkg.currency_conversion.log',' derive type null - return value ' || ret_value);ELSE
184
185 IF v_dr_type IN
186 ('EMU', 'EURO')
187 THEN
188
189 -- If currency is derived from Euro derived / Euro Currency by Subbu, Sri on 02-NOV-2000
190
191 v_frm_curr := 'EUR';
192
193 /* Bug 5148770. Added by Lakshmi Gopalsami
194 Removed the select and changed the same into a cursor.
195 */
196 OPEN get_curr_rate(v_func_curr,v_frm_curr);
197 FETCH get_curr_rate
198 INTO ret_value;
199 CLOSE get_curr_rate;
200
201 -- Bug 5148770. Added by Lakshmi Gopalsami
202 --print_log('jai_cmn_utils_pkg.currency_conversion.log',' EURO/EMU - derive type - return value '|| ret_value);
203 END IF;
204
205 END IF;
206
207 EXCEPTION
208 WHEN OTHERS THEN
209 --old code ret_value := 1;
210 RAISE_APPLICATION_ERROR(-20120,'Currency Conversion Rate Not Defined In The System');
211 END;
212 END IF;
213
214 --logging for debug
215 IF (ln_proc_level >= ln_dbg_level)
216 THEN
217 FND_LOG.STRING( ln_proc_level
218 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
219 , 'Enter Function'
220 );
221
222 END IF; --l_proc_level>=l_dbg_level
223
224 RETURN(nvl(ret_value,1));
225
226 /* Added by Ramananda for bug#4407165 */
227 EXCEPTION
228 WHEN OTHERS THEN
229 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
230 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG',lv_object_name || '. Err:' || SQLERRM);
231 app_exception.raise_exception;
232 END currency_conversion;
233
234 --==========================================================================
235 -- FUNCTION NAME:
236 --
237 -- Check_Inv_Validation Public
238 --
239 -- DESCRIPTION:
240 --
241 -- This function checks whether the invoice is validate or not
242 --
243 -- PARAMETERS:
244 -- In: pn_invoice_id Identifier of ap invoices
245 --
246 -- DESIGN REFERENCES:
247 -- FBT Technical Design Document 1.1.doc
248 --
249 -- CHANGE HISTORY:
250 --
251 -- 11-OCT-2007 Kevin Cheng created
252
253 FUNCTION Check_Inv_Validation
254 ( pn_invoice_id IN NUMBER
255 )
256 RETURN VARCHAR2
257 IS
258
259 lv_val VARCHAR2(25);
260
261 CURSOR check_not_validated_cur
262 ( pn_invoice_id NUMBER
263 )
264 IS
265 SELECT
266 SUM( decode(match_status_flag, 'A', 1, 0))
267 , COUNT(invoice_distribution_id)
268 FROM
269 ap_invoice_distributions_all
270 WHERE invoice_id = pn_invoice_id;
271
272 ln_total_count NUMBER;
273 ln_validated_cnt NUMBER;
274
275 lv_procedure_name VARCHAR2(40) := 'Check_Inv_Validation';
276 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
277 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
278
279 BEGIN
280 --logging for debug
281 IF (ln_proc_level >= ln_dbg_level)
282 THEN
283 FND_LOG.STRING( ln_proc_level
284 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
285 , 'Enter Function'
286 );
287 END IF; --l_proc_level>=l_dbg_level
288
289 OPEN check_not_validated_cur(pn_invoice_id);
290 FETCH check_not_validated_cur
291 INTO
292 ln_total_count
293 , ln_validated_cnt;
294 CLOSE check_not_validated_cur;
295
296 IF ln_total_count = ln_validated_cnt
297 THEN
298 lv_val := 'VALIDATED';
299 ELSE --ln_total_count <> ln_validated_cnt
300 lv_val := 'UNVALIDATED';
301 END IF; --ln_total_count = ln_validated_cnt
302
303 --logging for debug
304 IF (ln_proc_level >= ln_dbg_level)
305 THEN
306 FND_LOG.STRING( ln_proc_level
307 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
308 , 'Exit Function'
309 );
310 END IF; -- (ln_proc_level>=ln_dbg_level)
311 RETURN lv_val;
312
313 EXCEPTION
314 WHEN OTHERS THEN
315 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
316 THEN
317 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
318 , GV_MODULE_PREFIX ||'.' || lv_procedure_name
319 || '.Other_Exception '
320 , Sqlcode||Sqlerrm);
321 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
322 END Check_Inv_Validation;
323
324 --==========================================================================
325 -- FUNCTION NAME:
326 --
327 -- Get_Natural_Acc_Seg Public
328 --
329 -- DESCRIPTION:
330 --
331 -- This function is used to get the natural account segment value
332 --
333 -- PARAMETERS:
334 -- In: pv_col_name Identifier of natural account name
335 -- pn_ccid Identifier of code combination id
336 --
337 -- DESIGN REFERENCES:
338 -- FBT Technical Design Document 1.1.doc
339 --
340 -- CHANGE HISTORY:
341 --
342 -- 11-OCT-2007 Kevin Cheng created
343 -- 14-NOV-2008 Xiao Lv modified
344 -- Return value type changes to VARCHAR2 from NUMBER
345
346 FUNCTION Get_Natural_Acc_Seg
347 ( pv_col_name IN VARCHAR2
348 , pn_ccid IN NUMBER
349 )
350 RETURN VARCHAR2
351 IS
352
353 --ln_val NUMBER;
354 lv_val VARCHAR2(25);
355
356 lv_procedure_name VARCHAR2(40) := 'Get_Natural_Acc_Seg';
357 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
358 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
359
360 BEGIN
361 --logging for debug
362 IF (ln_proc_level >= ln_dbg_level)
363 THEN
364 FND_LOG.STRING( ln_proc_level
365 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
366 , 'Enter Function'
367 );
368 END IF; --l_proc_level>=l_dbg_level
369
370 EXECUTE IMMEDIATE
371 'SELECT '
372 || pv_col_name
373 || ' FROM gl_code_combinations WHERE code_combination_id = :a'
374 INTO
375 lv_val
376 USING
377 pn_ccid;
378
379 IF lv_val IS NULL
380 THEN
381 lv_val := '-999';
382 END IF; --ln_val IS NULL
383
384 --logging for debug
385 IF (ln_proc_level >= ln_dbg_level)
386 THEN
387 FND_LOG.STRING( ln_proc_level
388 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
389 , 'Exit Function'
390 );
391 END IF; -- (ln_proc_level>=ln_dbg_level)
392
393 RETURN lv_val;
394 EXCEPTION
395 WHEN no_data_found THEN
396 RETURN '-999';
397 WHEN OTHERS THEN
398 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
399 THEN
400 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
401 , GV_MODULE_PREFIX ||'.' || lv_procedure_name
402 || '.Other_Exception '
403 , Sqlcode||Sqlerrm);
404 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
405 END Get_Natural_Acc_Seg;
406
407 --==========================================================================
408 -- FUNCTION NAME:
409 --
410 -- Get_Balance_Acc_Seg Public
411 --
412 -- DESCRIPTION:
413 --
414 -- This function is used to get the balance account segment value
415 --
416 -- PARAMETERS:
417 -- In: pv_col_name Identifier of natural account name
418 -- pn_ccid Identifier of code combination id
419 --
420 -- DESIGN REFERENCES:
421 -- FBT Technical Design Document 1.1.doc
422 --
423 -- CHANGE HISTORY:
424 --
425 -- 11-AUG-2008 Xiao Lv created
426
427 FUNCTION Get_Balance_Acc_Seg
428 ( pv_col_name IN VARCHAR2
429 , pn_ccid IN NUMBER
430 )
431 RETURN VARCHAR2
432 IS
433
434 lv_val VARCHAR2(25);
435
436 lv_procedure_name VARCHAR2(40) := 'Get_Balance_Acc_Seg';
437 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
438 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
439
440 BEGIN
441 --logging for debug
442 IF (ln_proc_level >= ln_dbg_level)
443 THEN
444 FND_LOG.STRING( ln_proc_level
445 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
446 , 'Enter Function'
447 );
448 END IF; --l_proc_level>=l_dbg_level
449
450 EXECUTE IMMEDIATE
451 'SELECT '
452 || pv_col_name
453 || ' FROM gl_code_combinations WHERE code_combination_id = :a'
454 INTO
455 lv_val
456 USING
457 pn_ccid;
458
459 IF lv_val IS NULL
460 THEN
461 lv_val := '-999';
462 END IF; --ln_val IS NULL
463
464 --logging for debug
465 IF (ln_proc_level >= ln_dbg_level)
466 THEN
467 FND_LOG.STRING( ln_proc_level
468 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
469 , 'Exit Function'
470 );
471 END IF; -- (ln_proc_level>=ln_dbg_level)
472
473 RETURN lv_val;
474 EXCEPTION
475 WHEN no_data_found THEN
476 RETURN '-999';
477 WHEN OTHERS THEN
478 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
479 THEN
480 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
481 , GV_MODULE_PREFIX ||'.' || lv_procedure_name
482 || '.Other_Exception '
483 , Sqlcode||Sqlerrm);
484 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
485 END Get_Balance_Acc_Seg;
486
487
488 --==========================================================================
489 -- PROCEDURE NAME:
490 --
491 -- Calculate_Fbt_Amount Private
492 --
493 -- DESCRIPTION:
494 --
495 -- This procedure calculates the various tax amounts which need to be
496 -- populated in jai_fbt_repository table
497 --
498 -- PARAMETERS:
499 -- In: pn_legal_entity_id Identifier of legal entity
500 -- pv_fringe_benefit_type_code Identifier of FB type code
501 -- pn_inv_dist_id Identifier of invoice dist id
502 -- pn_inv_dist_amt Identifier of invoice dist amount
503 -- pv_currency Identifier of invoice currency
504 -- pv_exchange_rate_type Identifier of exchange rate type
505 -- pd_exchange_date Identifier of exchange date
506 -- pn_source Identifier of Source, 'Others' or 'Payables'
507 -- pn_je_header_id Identifier of GL JE Header
508 -- pn_fbt_year Identifier of FBT Year
509 --
510 -- Out: x_fbt_repository_type Returns the repository record
511 --
512 -- DESIGN REFERENCES:
513 -- FBT Technical Design Document 1.1.doc
514 --
515 -- CHANGE HISTORY:
516 --
517 -- 11-OCT-2007 Kevin Cheng created
518 -- 11-AUG-2008 Xiao Lv modified for 11i new changes
519 -- 06-NOV-2008 Xiao Lv modified for R12 new changes
520 -- 23-DEV-2008 Xiao Lv modified for bug#7661991
521 -- 26-DEV-2008 Xiao Lv modified for bug#7670949
522
523 PROCEDURE Calculate_Fbt_Amount
524 ( pn_legal_entity_id IN NUMBER
525 , pv_fringe_benefit_type_code IN VARCHAR2
526 , pn_inv_dist_id IN NUMBER
527 , pn_inv_dist_amt IN NUMBER
528 , pv_currency IN VARCHAR2
529 , pv_exchange_rate_type IN VARCHAR2
530 , pd_exchange_date IN DATE
531 , pn_source IN VARCHAR2
532 , pn_je_header_id IN NUMBER
533 , pn_fbt_year IN NUMBER
534 , x_fbt_repository_type OUT NOCOPY JAI_FBT_REPOSITORY%ROWTYPE
535 )
536 IS
537 CURSOR get_fbt_rates_cur
538 IS
539 SELECT DISTINCT
540 line.taxable_basis
541 , head.fbt_rate
542 , head.surcharge_rate
543 , head.edu_cess_rate
544 , head.sh_cess_rate
545 FROM
546 jai_fbt_setup_lines line
547 , jai_fbt_setup_headers head
548 WHERE line.legal_entity_id = pn_legal_entity_id
549 AND line.legal_entity_id = head.legal_entity_id
550 --modified by lvxiao for R12 new changegs on 06-Nov-2008, begin
551 -----------------------------------------------------------------------------------
552 AND head.fbt_year = pn_fbt_year
553 -----------------------------------------------------------------------------------
554 --modified by lvxiao for R12 new changegs on 06-Nov-2008, end
555 AND fringe_benefit_type_code = pv_fringe_benefit_type_code;
556
557 --modified by lvxiao for upgrade code to R12 on 06-Nov-2008, begin
558 -----------------------------------------------------------------------------------
559 CURSOR get_sob_id_cur
560 IS
561 SELECT
562 ledger_id
563 FROM
564 xle_fp_ou_ledger_v
565 WHERE legal_entity_id = pn_legal_entity_id;
566 /*
567 CURSOR get_sob_id_cur
568 IS
569 SELECT
570 org_information1
571 FROM
572 HR_ORGANIZATION_INFORMATION
573 WHERE ORGANIZATION_ID=pn_legal_entity_id
574 AND ORG_INFORMATION_CONTEXT ='Legal Entity Accounting';*/
575 -----------------------------------------------------------------------------------
576 --modified by lvxiao for upgrade code to R12 on 06-Nov-2008, end
577
578 fbt_rates_rec get_fbt_rates_cur%ROWTYPE;
579 ln_sob_id NUMBER;
580 ln_conv_rate NUMBER;
581
582 lv_procedure_name VARCHAR2(40) := 'Calculate_Fbt_Amount';
583 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
584 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
585 ln_fbt_cum_amt NUMBER;
586 ln_precision NUMBER;
587
588 --modified by lvxiao to fix bug#7325653 on 14-August-2008, begin
589 -----------------------------------------------------------------------------------
590 ln_exchange_rate NUMBER := 0;
591 -----------------------------------------------------------------------------------
592 --modified by lvxiao to fix bug#7325653 on 14-August-2008, end
593
594 BEGIN
595 --logging for debug
596 IF (ln_proc_level >= ln_dbg_level)
597 THEN
598 FND_LOG.STRING( ln_proc_level
599 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
600 , 'Enter procedure'
601 );
602 END IF; --l_proc_level>=l_dbg_level
603
604 OPEN get_fbt_rates_cur;
605 FETCH get_fbt_rates_cur INTO fbt_rates_rec;
606 CLOSE get_fbt_rates_cur;
607
608 OPEN get_sob_id_cur;
609 FETCh get_sob_id_cur INTO ln_sob_id;
610 CLOSE get_sob_id_cur;
611
612 SELECT
613 NVL( fc.precision
614 , 2
615 ) precision
616 INTO
617 ln_precision
618 FROM
619 gl_sets_of_books gsob
620 , fnd_currencies fc
621 WHERE gsob.set_of_books_id = fnd_profile.Value('GL_SET_OF_BKS_ID')
622 AND gsob.currency_code = fc.currency_code;
623
624 /* this package is called for converting the amounts in SOB currency */
625
626 --modified by lvxiao to fix bug#7325653 on 14-August-2008, begin
627 -----------------------------------------------------------------------------------
628 IF upper(pv_exchange_rate_type) = 'USER'
629 THEN
630 IF pn_source = 'Payables' THEN
631 SELECT aia.exchange_rate
632 INTO ln_exchange_rate
633 FROM AP_INVOICES_ALL aia
634 , Ap_Invoice_Distributions_All aida
635 WHERE aida.invoice_distribution_id = pn_inv_dist_id
636 AND aida.invoice_id = aia.invoice_id;
637 ELSE --pn_source = 'Others'
638 SELECT currency_conversion_rate
639 INTO ln_exchange_rate
640 FROM gl_je_headers head
641 WHERE je_header_id = pn_je_header_id;
642 END IF;
643 END IF;
644
645 /*
646 ln_conv_rate := jai_cmn_utils_pkg.currency_conversion( ln_sob_id
647 , pv_currency
648 , pd_exchange_date
649 , pv_exchange_rate_type
650 , 1 );
651 */
652 /* ln_conv_rate := currency_conversion( ln_sob_id
653 , pv_currency
654 , pd_exchange_date
655 , pv_exchange_rate_type
656 , 1 );
657 */
658 ln_conv_rate := currency_conversion( ln_sob_id
659 , pv_currency
660 , pd_exchange_date
661 , pv_exchange_rate_type
662 , ln_exchange_rate );
663 -----------------------------------------------------------------------------------
664 --modified by lvxiao to fix bug#7325653 on 14-August-2008, end
665
666 --modified by lvxiao to R12 new changes on 06-nov-2008, begin
667 -----------------------------------------------------------------------------------
668 x_fbt_repository_type.converted_amount:= pn_inv_dist_amt * ln_conv_rate;
669 x_fbt_repository_type.conversion_rate := ln_conv_rate;
670 x_fbt_repository_type.conversion_type := pv_exchange_rate_type;
671 x_fbt_repository_type.conversion_date := pd_exchange_date;
672
673 x_fbt_repository_type.taxable_basis
674 := fbt_rates_rec.taxable_basis;
675
676 x_fbt_repository_type.fbt_taxable_amount
677 := ROUND( x_fbt_repository_type.converted_amount
678 *fbt_rates_rec.taxable_basis / 100
679 , ln_precision
680 );
681
682 /* x_fbt_repository_type.fbt_taxable_amount
683 := ROUND( pn_inv_dist_amt*fbt_rates_rec.taxable_basis*ln_conv_rate/100
684 , ln_precision
685 ); */
686 -----------------------------------------------------------------------------------
687 --modified by lvxiao to R12 new changes on 06-nov-2008, end
688
689
690
691 x_fbt_repository_type.fbt_tax_amount
692 := ROUND( x_fbt_repository_type.fbt_taxable_amount
693 * fbt_rates_rec.fbt_rate / 100
694 , ln_precision
695 );
696
697 --modified by Lv Xiao for bug#7661991 on 22-Dec-2008, begin
698 -----------------------------------------------------------------------------------
699 /*
700 x_fbt_repository_type.fbt_surcharge_amount
701 := ROUND( X_FBT_REPOSITORY_TYPE.fbt_tax_amount
702 * fbt_rates_rec.surcharge_rate / 100
703 , ln_precision
704 );
705
706 */
707 x_fbt_repository_type.fbt_surcharge_amount
708 := ROUND( X_FBT_REPOSITORY_TYPE.fbt_taxable_amount
709 * fbt_rates_rec.surcharge_rate / 100
710 , ln_precision
711 );
712
713 -----------------------------------------------------------------------------------
714 --modified by Lv Xiao for bug#7661991 on 22-Dec-2008, end
715
716 ln_fbt_cum_amt := x_fbt_repository_type.fbt_tax_amount
717 + x_fbt_repository_type.fbt_surcharge_amount;
718
719 x_fbt_repository_type.fbt_edu_cess_amount
720 := ROUND( ln_fbt_cum_amt * fbt_rates_rec.edu_cess_rate / 100
721 , ln_precision
722 );
723 x_fbt_repository_type.fbt_sh_cess_amount
724 := ROUND( ln_fbt_cum_amt * fbt_rates_rec.sh_cess_rate / 100
725 , ln_precision
726 );
727 x_fbt_repository_type.legal_entity_id
728 := pn_legal_entity_id;
729 x_fbt_repository_type.fringe_benefit_type_code
730 := pv_fringe_benefit_type_code;
731
732 SELECT
733 currency_code
734 INTO
735 x_fbt_repository_type.FBT_CURRENCY
736 FROM
737 gl_sets_of_books
738 WHERE set_of_books_id = ln_sob_id;
739
740 IF ( pn_source = 'Payables')
741 THEN
742 x_fbt_repository_type.invoice_distribution_id
743 := pn_inv_dist_id;
744 x_fbt_repository_type.distribution_amt
745 := pn_inv_dist_amt;
746 x_fbt_repository_type.invoice_currency_code
747 := pv_currency;
748
749 --set invoice_date as ap_invoice_distributions_all.accounting_date.
750 SELECT aida.accounting_date
751 INTO x_fbt_repository_type.invoice_date
752 FROM ap_invoice_distributions_all aida
753 WHERE aida.invoice_distribution_id = pn_inv_dist_id;
754
755 SELECT invoice_num
756 INTO x_fbt_repository_type.je_name
757 FROM ap_invoices_all aia
758 , ap_invoice_distributions_all aida
759 WHERE invoice_distribution_id = pn_inv_dist_id
760 AND aia.invoice_id = aida.invoice_id;
761
762 --modified by Lv Xiao for bug#7670949 on 26-Dec-2008, begin
763 -----------------------------------------------------------------------------------
764 --get invoice line number according to the distribution id
765 SELECT aida.invoice_line_number
766 --aida.distribution_line_number
767 INTO x_fbt_repository_type.JE_LINE_NUM
768 FROM ap_invoice_distributions_all aida
769 WHERE aida.invoice_distribution_id = pn_inv_dist_id;
770 -----------------------------------------------------------------------------------
771 --modified by Lv Xiao for bug#7670949 on 26-Dec-2008, end
772
773 END IF; -- (pn_source = 'Payables')
774
775 --logging for debug
776 IF (ln_proc_level >= ln_dbg_level)
777 THEN
778 FND_LOG.STRING( ln_proc_level
779 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
780 , 'Exit procedure'
781 );
782 END IF; -- (ln_proc_level>=ln_dbg_level)
783
784 EXCEPTION
785 WHEN OTHERS THEN
786 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
787 THEN
788 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
789 , GV_MODULE_PREFIX ||'.' || lv_procedure_name
790 || '.Other_Exception '
791 , Sqlcode||Sqlerrm);
792 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
793 END Calculate_Fbt_Amount;
794
795 --==========================================================================
796 -- PROCEDURE NAME:
797 --
798 -- Insert_Fbt_Repository Private
799 --
800 -- DESCRIPTION:
801 --
802 -- This procedure insert one record into table jai_fbt_repository
803 --
804 -- PARAMETERS:
805 -- In: p_fbt_repository Identifier of record containing
806 -- fbt repository information
807 --
808 -- DESIGN REFERENCES:
809 -- FBT Technical Design Document 1.1.doc
810 --
811 -- CHANGE HISTORY:
812 --
813 -- 11-OCT-2007 Kevin Cheng created
814 -- 11-AUG-2008 Xiao Lv modified for 11i new changes
815 -- 14-NOV-2008 Xiao Lv modified for R12 new changes
816
817 PROCEDURE Insert_Fbt_Repository
818 ( p_fbt_repository IN JAI_FBT_REPOSITORY%ROWTYPE
819 )
820 IS
821 ln_fbt_trans_id NUMBER;
822 lv_procedure_name VARCHAR2(40) := 'Insert_Fbt_Repository';
823 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
824 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
825
826 BEGIN
827 --logging for debug
828 IF (ln_proc_level >= ln_dbg_level)
829 THEN
830 FND_LOG.STRING( ln_proc_level
831 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
832 , 'Enter procedure'
833 );
834 END IF; --l_proc_level>=l_dbg_level
835
836 SELECT Jai_Fbt_Repository_s.NEXTVAL
837 INTO ln_fbt_trans_id
838 FROM dual;
839
840 INSERT INTO JAI_FBT_REPOSITORY
841 ( FBT_TRANSACTION_ID
842 , LEGAL_ENTITY_ID
843 , PERIOD_START_DATE
844 , PERIOD_END_DATE
845
846 , SOURCE
847 , JE_HEADER_ID
848 , BATCH_NAME
849 , JE_SOURCE
850 , JE_NAME
851 , PERIOD_NAME
852 , JE_LINE_NUM
853
854 , INVOICE_DISTRIBUTION_ID
855 , INVOICE_DATE
856 , INVOICE_CURRENCY_CODE
857 , DISTRIBUTION_AMT
858 , DIST_CODE_COMBINATION_ID
859 , DIST_NATURAL_ACCOUNT_VALUE
860 , DIST_BALANCE_ACCOUNT_VALUE
861
862 , FRINGE_BENEFIT_TYPE_CODE
863 , FBT_CURRENCY
864 , TAXABLE_BASIS
865 , FBT_TAXABLE_AMOUNT
866 , FBT_TAX_AMOUNT
867 , FBT_SURCHARGE_AMOUNT
868 , FBT_EDU_CESS_AMOUNT
869 , FBT_SH_CESS_AMOUNT
870 , MANUAL_FLAG
871 --modified by lvxiao for R12 new changes on 06-Nov-2008, begin
872 -----------------------------------------------------------------------------------
873 , CONVERSION_RATE
874 , CONVERTED_AMOUNT
875 , CONVERSION_TYPE
876 , CONVERSION_DATE
877 , MODIFIED_FLAG
878 -----------------------------------------------------------------------------------
879 --modified by lvxiao for R12 new changes on 06-Nov-2008, end
880 , CREATION_DATE
881 , CREATED_BY
882 , LAST_UPDATE_DATE
883 , LAST_UPDATED_BY
884 , LAST_UPDATE_LOGIN
885 )
886 VALUES ( ln_fbt_trans_id
887 , P_FBT_REPOSITORY.LEGAL_ENTITY_ID
888 , P_FBT_REPOSITORY.PERIOD_START_DATE
889 , P_FBT_REPOSITORY.PERIOD_END_DATE
890
891 , P_FBT_REPOSITORY.SOURCE
892 , P_FBT_REPOSITORY.JE_HEADER_ID
893 , P_FBT_REPOSITORY.BATCH_NAME
894 , P_FBT_REPOSITORY.JE_SOURCE
895 , P_FBT_REPOSITORY.JE_NAME
896 , P_FBT_REPOSITORY.PERIOD_NAME
897 , P_FBT_REPOSITORY.JE_LINE_NUM
898
899 , P_FBT_REPOSITORY.INVOICE_DISTRIBUTION_ID
900 , P_FBT_REPOSITORY.INVOICE_DATE
901 , P_FBT_REPOSITORY.INVOICE_CURRENCY_CODE
902 , P_FBT_REPOSITORY.DISTRIBUTION_AMT
903 , P_FBT_REPOSITORY.DIST_CODE_COMBINATION_ID
904 , P_FBT_REPOSITORY.DIST_NATURAL_ACCOUNT_VALUE
905 , P_FBT_REPOSITORY.DIST_BALANCE_ACCOUNT_VALUE
906
907 , P_FBT_REPOSITORY.FRINGE_BENEFIT_TYPE_CODE
908 , P_FBT_REPOSITORY.FBT_CURRENCY
909 , P_FBT_REPOSITORY.TAXABLE_BASIS
910 , P_FBT_REPOSITORY.FBT_TAXABLE_AMOUNT
911 , P_FBT_REPOSITORY.FBT_TAX_AMOUNT
912 , P_FBT_REPOSITORY.FBT_SURCHARGE_AMOUNT
913 , P_FBT_REPOSITORY.FBT_EDU_CESS_AMOUNT
914 , P_FBT_REPOSITORY.FBT_SH_CESS_AMOUNT
915 , 'N' -- indicate manual transactions
916 --modified by lvxiao for R12 new changes on 06-Nov-2008, begin
917 -----------------------------------------------------------------------------------
918 , P_FBT_REPOSITORY.CONVERSION_RATE
919 , P_FBT_REPOSITORY.CONVERTED_AMOUNT
920 , P_FBT_REPOSITORY.CONVERSION_TYPE
921 , P_FBT_REPOSITORY.CONVERSION_DATE
922 , 0 -- indicate no modification transactions
923 -----------------------------------------------------------------------------------
924 --modified by lvxiao for R12 new changes on 06-Nov-2008, end
925 , SYSDATE
926 , fnd_global.user_id
927 , SYSDATE
928 , fnd_global.user_id
929 , fnd_global.login_id
930 );
931
932 --logging for debug
933 IF (ln_proc_level >= ln_dbg_level)
934 THEN
935 FND_LOG.STRING( ln_proc_level
936 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
937 , 'Exit procedure'
938 );
939 END IF; -- (ln_proc_level>=ln_dbg_level)
940
941 EXCEPTION
942 WHEN OTHERS THEN
943 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
944 THEN
945 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
946 , GV_MODULE_PREFIX ||'.' || lv_procedure_name
947 || '.Other_Exception '
948 , Sqlcode||Sqlerrm);
949 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
950 END Insert_Fbt_Repository;
951
952 --==========================================================================
953 -- PROCEDURE NAME:
954 --
955 -- Fbt_Inv_Process Public
956 --
957 -- DESCRIPTION:
958 --
959 -- This is the main procedure which will be called by the concurrent
960 -- program to check eligible invoices, calculate FBT taxes and insert
961 -- data into jai_fbt_repository table
962 --
963 -- PARAMETERS:
964 -- In: pn_legal_entity_id Identifier of legal entity
965 -- pv_start_date Identifier of period start date
966 -- pv_end_date Identifier of period end date
967 -- pv_fringe_benefit_type_code Identifier of FB type code
968 -- pv_generate_return Identifier of supplier id
969 --
970 -- Out: pv_errbuf Returns the error if concurrent program
971 -- does not execute completely
972 -- pv_retcode Returns success or failure
973 --
974 -- DESIGN REFERENCES:
975 -- FBT Technical Design Document 1.1.doc
976 --
977 -- CHANGE HISTORY:
978 --
979 -- 11-OCT-2007 Kevin Cheng created
980 -- 21-MAR-2008 Kevin Cheng bug#6908012
981 -- Add legal entity criteria for illegible invoice
982 -- fetching.
983 -- 29-Jul-2008 Eric commented out code in the cursor get_eligible_invoices_cur
984 -- to leave the invoice case of accrue_on_receipt_flag ='Y' to GL module
985 -- 11-Aug-2008 Lv Xiao modified for 11i new changes.
986 -- 25-Aug-2008 Lv Xiao fix bug#7347306 and bug#7347401.
987 -- 06-Nov-2008 Lv Xiao modified for FBT R12 new changes.
988 -- 30-Dec-2008 Li Jia fix bug#7675638
989
990 PROCEDURE Fbt_Inv_Process
991 ( pv_errbuf OUT NOCOPY VARCHAR2
992 , pv_retcode OUT NOCOPY VARCHAR2
993 , pn_legal_entity_id IN NUMBER
994 , pn_fbt_year IN NUMBER
995 , pv_start_date IN VARCHAR2
996 , pv_end_date IN VARCHAR2
997 , pv_fringe_benefit_type_code IN VARCHAR2
998 )
999 IS
1000
1001 ld_start_date DATE;
1002 ld_end_date DATE;
1003
1004 -- Deleted by Jia for bug#7675638 on 30-Nov-2008, Begin
1005 --------------------------------------------------------------
1006 /*
1007 \* fetch all the operating nits within the LE *\
1008 --modified by lvxiao for upgrade code to R12 from 11i on 06-Nov-2008,begin
1009 -----------------------------------------------------------------------------------
1010
1011 CURSOR GET_OPERATING_UNIT_CUR IS
1012 SELECT
1013 OPERATING_UNIT_ID
1014 FROM
1015 XLE_FP_OU_LEDGER_V
1016 WHERE LEGAL_ENTITY_ID = pn_legal_entity_id;
1017
1018 \*
1019 CURSOR GET_OPERATING_UNIT_CUR
1020 IS
1021 SELECT
1022 organization_id operating_unit_id
1023 FROM
1024 hr_organization_information
1025 WHERE org_information2 = pn_legal_entity_id --LEGAL ENTITY ID
1026 AND ORG_INFORMATION_CONTEXT='Operating Unit Information';
1027 *\
1028 ----------------------------------------------------------------------------------
1029 --modified by lvxiao for upgrade code to R12 from 11i on 06-Nov-2008,end
1030 */
1031 --------------------------------------------------------------
1032 -- Deleted by Jia for bug#7675638 on 30-Nov-2008, End
1033
1034
1035 /* chart_of_accounts_id related with SOB which is attached to an LE */
1036 CURSOR get_chart_of_accounts_id_cur IS
1037 SELECT
1038 chart_of_accounts_id
1039 FROM
1040 gl_sets_of_books
1041 WHERE
1042 set_of_books_id = fnd_profile.Value('GL_SET_OF_BKS_ID');
1043
1044 -- get the natural account segment
1045 CURSOR get_natural_account_col_cur
1046 ( pn_coa NUMBER
1047 )
1048 IS
1049 SELECT
1050 application_column_name
1051 FROM
1052 FND_SEGMENT_ATTRIBUTE_VALUES
1053 WHERE application_id = 101
1054 AND id_flex_code ='GL#'
1055 AND segment_attribute_type = 'GL_ACCOUNT'
1056 AND id_flex_num = pn_coa
1057 AND attribute_value = 'Y';
1058
1059 -- get the balance account segment
1060 CURSOR get_balance_account_col_cur
1061 ( pn_coa NUMBER
1062 )
1063 IS
1064 SELECT
1065 application_column_name
1066 FROM
1067 FND_SEGMENT_ATTRIBUTE_VALUES
1068 WHERE application_id = 101
1069 AND id_flex_code ='GL#'
1070 AND segment_attribute_type = 'GL_BALANCING'
1071 AND id_flex_num = pn_coa
1072 AND attribute_value = 'Y';
1073
1074 /* cursor to select all the eligible invoices exclude manual modified transactions in JAI_FBT_REPOSITORY
1075 The invoice to be eligible for FBT should meet the following criteria
1076 1) invoice should be of type
1077 ('STANDARD','DEBIT', 'CREDIT', 'EXPENSE REPORT','MIXED')
1078 2) invoice should be validated invoice
1079 3) only non-recoverable tax lines are eligible for FBT
1080 4) match the criteria entered by the user in CP parameters form
1081 5) do not re-fetch transactions that have been modified in JAI_FBT_REPOSITORY
1082 */
1083 -- this clause gets all the matched invoices with non-recoverable tax lines
1084 CURSOR get_eligible_invoices_cur
1085 ( pv_nat_acc_seg VARCHAR2
1086 , pv_bal_acc_seg VARCHAR2
1087 -- Modified by Jia for bug#7675638 on 30-Dec-2008, Begin
1088 ----------------------------------------------------------
1089 --, pn_operating_unit NUMBER
1090 , pn_legal_entity_id NUMBER
1091 ----------------------------------------------------------
1092 -- Modified by Jia for bug#7675638 on 30-Dec-2008, End
1093 , pn_fbt_year NUMBER
1094 )
1095 IS
1096 SELECT
1097 Get_Natural_Acc_Seg( pv_nat_acc_seg
1098 , /*commented out the below section for FBT 11.5 backport by Eric Ma on 29-July-2008
1099 decode( NVL(accrue_on_receipt_flag, 'N')
1100 , 'N'
1101 , det.DIST_CODE_COMBINATION_ID
1102 , 'Y'
1103 , po.code_combination_id
1104 )
1105 */
1106 det.DIST_CODE_COMBINATION_ID
1107 ) nat_acct_seg
1108 , Get_Balance_Acc_Seg( pv_bal_acc_seg
1109 , det.DIST_CODE_COMBINATION_id
1110 ) bal_acct_seg
1111 , det.amount
1112 , det.dist_match_type
1113 , det.invoice_distribution_id
1114 , det.dist_code_combination_id
1115 , head.invoice_currency_code
1116 , head.exchange_rate_type
1117 , head.exchange_rate
1118 , head.exchange_date
1119 FROM
1120 ap_invoices_all head
1121 , ap_invoice_distributions_all det
1122 , po_distributions_all po
1123 WHERE head.invoice_id = det.invoice_id
1124 -- Modified by Jia for bug#7675638 on 30-Dec-2008, Begin
1125 --------------------------------------------------------------
1126 --AND head.org_id = pn_operating_unit
1127 AND head.legal_entity_id = pn_legal_entity_id
1128 --------------------------------------------------------------
1129 -- Modified by Jia for bug#7675638 on 30-Dec-2008, End
1130
1131 AND det.po_distribution_id = po.po_distribution_id
1132 AND invoice_type_lookup_code IN ( 'STANDARD'
1133 , 'DEBIT'
1134 , 'CREDIT'
1135 , 'EXPENSE REPORT'
1136 ,'MIXED'
1137 )
1138 AND Check_Inv_Validation( head.invoice_id
1139 ) = 'VALIDATED'
1140 AND (det.dist_match_type IN ( 'ITEM_TO_PO'
1141 , 'ITEM_TO_RECEIPT'
1142 )
1143 --modified by lvxiao for upgrade code for new changes to R12 from 11i on 06-Nov-2008, begin
1144 -------------------------------------------------------------------------------------------
1145 OR EXISTS (SELECT 1
1146 FROM jai_ap_match_inv_taxes jamit
1147 , ap_invoice_distributions_all aida1
1148 WHERE jamit.invoice_distribution_id
1149 = det.invoice_distribution_id
1150 AND jamit.parent_invoice_distribution_id
1151 = aida1.invoice_distribution_id
1152 AND aida1.dist_match_type IN ('ITEM_TO_PO'
1153 , 'ITEM_TO_RECEIPT'
1154 )
1155 /* ja_in_ap_tax_distributions jiatd
1156 , ap_invoice_distributions_all aida1
1157 WHERE jiatd.invoice_id
1158 = det.invoice_id
1159 AND jiatd.distribution_line_number
1160 = det.distribution_line_number
1161 AND jiatd.parent_invoice_distribution_id
1162 = aida1.invoice_distribution_id */
1163 )
1164 )
1165 AND det.accounting_date BETWEEN ld_start_date AND ld_end_date
1166 -- Modified by Jia for bug#7675638 on 30-Dec-2008, Begin
1167 --------------------------------------------------------------
1168 --AND det.org_id = pn_operating_unit
1169 AND det.org_id = head.org_id
1170 --------------------------------------------------------------
1171 -- Modified by Jia for bug#7675638 on 30-Dec-2008, Begin
1172 AND det.invoice_distribution_id IN ( SELECT
1173 aida.invoice_distribution_id
1174 FROM
1175 AP_INVOICE_DISTRIBUTIONS_ALL AIDA
1176 WHERE aida.invoice_id = head.invoice_id
1177 AND NOT EXISTS
1178 ( SELECT 1
1179 FROM JAI_AP_MATCH_INV_TAXES JAMIT
1180 WHERE JAMIT.INVOICE_DISTRIBUTION_ID
1181 = AIDA.INVOICE_DISTRIBUTION_ID
1182 AND RECOVERABLE_FLAG = 'Y'
1183 /* ja_in_ap_tax_distributions jiatdi
1184 WHERE jiatdi.invoice_id
1185 = AIDA.invoice_id
1186 AND jiatdi.distribution_line_number
1187 = AIDA.distribution_line_number */
1188 )
1189 )
1190 /* following code excludes the manual modified transactions in JAI_FBT_REPOSITORY
1191 this is for FBT R12 new change
1192 */
1193 AND NOT EXISTS
1194 ( SELECT 1
1195 FROM JAI_FBT_REPOSITORY jfr
1196 WHERE jfr.invoice_distribution_id
1197 = det.invoice_distribution_id
1198 )
1199 -------------------------------------------------------------------------------------------
1200 --modified by lvxiao for upgrade code for new changes to R12 from 11i on 06-Nov-2008, end
1201
1202 AND ( ( NVL(accrue_on_receipt_flag, 'N') = 'N'
1203 AND Get_Natural_Acc_Seg( pv_nat_acc_seg
1204 , det.DIST_CODE_COMBINATION_id
1205 ) IN ( SELECT NATURAL_ACCOUNT_VALUE
1206 FROM jai_fbt_setup_lines
1207 WHERE legal_entity_id = pn_legal_entity_id
1208 AND fbt_year = pn_fbt_year
1209 AND FRINGE_BENEFIT_TYPE_CODE
1210 = NVL(pv_fringe_benefit_type_code
1211 , FRINGE_BENEFIT_TYPE_CODE)
1212 )
1213 )
1214 )
1215
1216 UNION
1217
1218 -- this clause gets all the unmatched invoices
1219 SELECT
1220 Get_Natural_Acc_Seg( pv_nat_acc_seg
1221 , det.DIST_CODE_COMBINATION_id
1222 ) nat_acct_seg
1223 , Get_Balance_Acc_Seg( pv_bal_acc_seg
1224 , det.DIST_CODE_COMBINATION_id
1225 ) bal_acct_seg
1226 , det.amount
1227 , det.dist_match_type
1228 , det.invoice_distribution_id
1229 , det.dist_code_combination_id
1230 , head.invoice_currency_code
1231 , head.exchange_rate_type
1232 , head.exchange_rate
1233 , head.exchange_date
1234 FROM
1235 ap_invoices_all head
1236 , ap_invoice_distributions_all det
1237 WHERE head.invoice_id = det.invoice_id
1238 -- Modified by Jia for bug#7675638 on 30-Dec-2008, Begin
1239 --------------------------------------------------------------
1240 --AND head.org_id = pn_operating_unit
1241 AND head.legal_entity_id = pn_legal_entity_id
1242 --------------------------------------------------------------
1243 -- Modified by Jia for bug#7675638 on 30-Dec-2008, End
1244
1245 AND invoice_type_lookup_code IN ( 'STANDARD'
1246 , 'DEBIT'
1247 , 'CREDIT'
1248 , 'EXPENSE REPORT'
1249 , 'MIXED'
1250 )
1251 AND Check_Inv_Validation( head.invoice_id
1252 ) = 'VALIDATED'
1253 AND (det.dist_match_type IS NULL
1254 OR det.dist_match_type NOT IN ( 'ITEM_TO_PO'
1255 , 'ITEM_TO_RECEIPT'
1256 )
1257 )
1258 --modified by lvxiao for upgrade code for new changes to R12 from 11i on 06-Nov-2008, begin
1259 -------------------------------------------------------------------------------------------
1260 AND NOT EXISTS ( SELECT 1
1261 FROM jai_ap_match_inv_taxes jamit
1262 WHERE jamit.invoice_distribution_id
1263 = det.invoice_distribution_id
1264 /* ja_in_ap_tax_distributions jiatd
1265 WHERE jiatd.invoice_id
1266 = det.invoice_id
1267 AND jiatd.distribution_line_number
1268 = det.distribution_line_number */
1269 )
1270
1271 ------------------------------------------------------------------------------------------
1272 --modified by lvxiao for upgrade code for new changes to R12 from 11i on 06-Nov-2008, end
1273
1274 /* following code excludes the manual modified transactions in JAI_FBT_REPOSITORY
1275 this is for FBT R12 new change
1276 */
1277 AND NOT EXISTS
1278 ( SELECT 1
1279 FROM JAI_FBT_REPOSITORY jfr
1280 WHERE jfr.invoice_distribution_id
1281 = det.invoice_distribution_id
1282 )
1283 AND det.accounting_date BETWEEN ld_start_date AND ld_end_date
1284 -- Modified by Jia for bug#7675638 on 30-Dec-2008, Begin
1285 --------------------------------------------------------------
1286 --AND det.org_id = pn_operating_unit
1287 AND det.org_id = head.org_id
1288 --------------------------------------------------------------
1289 -- Modified by Jia for bug#7675638 on 30-Dec-2008, End
1290 AND Get_Natural_Acc_Seg( pv_nat_acc_seg
1291 , det.DIST_CODE_COMBINATION_id
1292 ) IN ( SELECT
1293 NATURAL_ACCOUNT_VALUE
1294 FROM
1295 jai_fbt_setup_lines
1296 WHERE legal_entity_id = pn_legal_entity_id
1297 AND fbt_year = pn_fbt_year
1298 AND FRINGE_BENEFIT_TYPE_CODE =
1299 NVL( pv_fringe_benefit_type_code
1300 , FRINGE_BENEFIT_TYPE_CODE
1301 )
1302 );
1303
1304
1305 /* cursor to select all the eligible journals from GL exclude manual modified journals in JAI_FBT_REPOSITORY
1306 The invoice to be eligible for FBT should meet the following criteria
1307 1) all journal entries should be considered for FBT process excluding the
1308 following source:('Payables')
1309 2) only posted journals are considered
1310 3) only journal entries with matched nature account are considered
1311 4) match the criteria entered by the user in CP parameters form
1312 5) do not re-fetch journals that have been modified in JAI_FBT_REPOSITORY
1313 */
1314
1315 CURSOR get_eligible_journals_cur
1316 ( pv_nat_acc_seg VARCHAR2,
1317 pv_bal_acc_seg VARCHAR2,
1318 pn_fbt_year NUMBER
1319 )
1320 IS
1321
1322 SELECT /* Rowid(head)
1323 Index(head,GL_JE_HEADERS_U1)
1324 Index(batch,GL_JE_BATCHES_U1)
1325 */
1326
1327 Get_Natural_Acc_Seg(PV_NAT_ACC_SEG
1328 , LINE.CODE_COMBINATION_ID) NAT_ACCT_SEG,
1329 Get_Balance_Acc_Seg(PV_BAL_ACC_SEG
1330 , LINE.CODE_COMBINATION_ID) BAL_ACCT_SEG,
1331 (NVL(LINE.ENTERED_DR, 0)
1332 -NVL(LINE.ENTERED_CR, 0)) DISTRIBUTION_AMT,
1333 LINE.JE_LINE_NUM JE_LINE_NUM,
1334 LINE.CODE_COMBINATION_ID JE_LINE_CCID,
1335 HEAD.JE_HEADER_ID JE_HEADER_ID,
1336 BATCH.NAME JE_BATCH_NAME,
1337 HEAD.JE_SOURCE JE_SOURCE,
1338 HEAD.NAME JE_NAME,
1339 HEAD.PERIOD_NAME PERIOD_NAME,
1340 LINE.EFFECTIVE_DATE JE_LINE_EFFECTIVE_DATE,
1341 HEAD.CURRENCY_CODE CURRENCY_CODE,
1342 HEAD.CURRENCY_CONVERSION_TYPE,
1343 HEAD.CURRENCY_CONVERSION_RATE,
1344 HEAD.CURRENCY_CONVERSION_DATE
1345
1346 FROM
1347 gl_je_headers head
1348 , gl_je_lines line
1349 , gl_je_batches batch
1350 WHERE line.effective_date >= ld_start_date
1351 AND line.effective_date <= ld_end_date
1352 AND line.STATUS = 'P'
1353 AND head.je_source <> 'Payables'
1354 AND head.JE_HEADER_ID = line.JE_HEADER_ID
1355 AND head.JE_BATCH_ID = batch.JE_BATCH_ID
1356 AND Get_Natural_Acc_Seg( pv_nat_acc_seg
1357 , line.CODE_COMBINATION_ID
1358 ) IN ( SELECT NATURAL_ACCOUNT_VALUE
1359 FROM jai_fbt_setup_lines
1360 WHERE legal_entity_id = pn_legal_entity_id
1361 AND fbt_year = pn_fbt_year
1362 AND FRINGE_BENEFIT_TYPE_CODE =
1363 NVL( pv_fringe_benefit_type_code
1364 , FRINGE_BENEFIT_TYPE_CODE
1365 )
1366 )
1367 --modified by lvxiao for upgrade code to R12 on 06-Nov-2008,begin
1368 -----------------------------------------------------------------------------------
1369 AND Get_Balance_Acc_Seg( pv_bal_acc_seg
1370 , line.CODE_COMBINATION_ID
1371 ) IN ( SELECT segment_value
1372 FROM GL_LEDGER_NORM_SEG_VALS
1373 WHERE legal_entity_id = pn_legal_entity_id
1374 AND SEGMENT_TYPE_CODE = 'B'
1375 )
1376 -----------------------------------------------------------------------------------
1377 --modified by lvxiao for upgrade code to R12 on 06-Nov-2008,end
1378
1379 --Following code excludes manual modified journals in JAI_FBT_REPOSITORY
1380 AND NOT EXISTS
1381 ( SELECT 1
1382 FROM JAI_FBT_REPOSITORY jfr
1383 WHERE jfr.je_header_id = head.JE_HEADER_ID
1384 AND jfr.je_line_num = line.JE_LINE_NUM
1385 AND head.je_header_id = line.je_header_id
1386 );
1387
1388
1389 /* cursor to check whether the returned ccid has the fbt benefit type
1390 attached to it
1391 */
1392 CURSOR get_benefit_type_cur
1393 ( pn_nat_account NUMBER
1394 )
1395 IS
1396 SELECT
1397 fringe_benefit_type_code
1398 FROM
1399 jai_fbt_setup_lines
1400 WHERE natural_account_value = pn_nat_account
1401 AND legal_entity_id = pn_legal_entity_id
1402 --modified by lvxiao for R12 new change on 06-Nov-2008, begin
1403 -----------------------------------------------------------------------------------
1404 AND fbt_year = pn_fbt_year;
1405 -----------------------------------------------------------------------------------
1406 --modified by lvxiao for R12 new change on 06-Nov-2008, end
1407 ln_coa_id NUMBER;
1408 lv_nat_acc_seg_name VARCHAR2(30);
1409 ln_nat_acct NUMBER;
1410 ln_dist_ccid NUMBER;
1411
1412 lv_bal_acct VARCHAR2(15);
1413 lv_bal_acc_seg_name VARCHAR2(30);
1414 ln_settlement_id NUMBER(15);
1415 lv_source VARCHAR2(30);
1416
1417 ld_period_end_date DATE;
1418
1419 lv_fringe_ben_type_code JAI_FBT_SETUP_HEADERS.BUSINESS_TYPE_CODE%TYPE;
1420
1421 fbt_repository_rec jai_fbt_repository%ROWTYPE;
1422
1423 lv_procedure_name VARCHAR2(40) := 'Fbt_Inv_Process';
1424 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1425 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1426
1427 BEGIN
1428 --logging for debug
1429 IF (ln_proc_level >= ln_dbg_level)
1430 THEN
1431 FND_LOG.STRING( ln_proc_level
1432 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
1433 , 'Enter procedure'
1434 );
1435 END IF; --l_proc_level>=l_dbg_level
1436
1437 -- the source date format like '2007/06/01 00:00:00', format it to standard format
1438 ld_start_date := TRUNC(fnd_date.canonical_to_date (pv_start_date));
1439 ld_end_date := TRUNC(fnd_date.canonical_to_date (pv_end_date));
1440
1441 -- ld_start_date := TO_DATE(pv_start_date, GV_DATE_MASK);
1442 -- ld_end_date := TO_DATE(pv_end_date, GV_DATE_MASK);
1443
1444 DELETE
1445 FROM
1446 jai_fbt_repository
1447 WHERE legal_entity_id = pn_legal_entity_id
1448 --modified by lvxiao for R12 new change on 06-Nov-2008, begin
1449 -----------------------------------------------------------------------------------
1450 AND invoice_date >= ld_start_date
1451 AND invoice_date <= ld_end_date
1452 /* AND period_start_date >= ld_start_date
1453 AND period_end_date <= ld_end_date*/
1454 AND fringe_benefit_type_code = NVL( pv_fringe_benefit_type_code
1455 , FRINGE_BENEFIT_TYPE_CODE
1456 )
1457 AND (manual_flag IS NULL OR manual_flag = 'N') -- not manual transactions
1458 AND (modified_flag IS NULL OR modified_flag = 0 ); -- transactions not been modified.
1459 -----------------------------------------------------------------------------------
1460 --modified by lvxiao for R12 new change on 06-Nov-2008, end
1461
1462 OPEN get_chart_of_accounts_id_cur;
1463 FETCH get_chart_of_accounts_id_cur
1464 INTO ln_coa_id;
1465 CLOSE get_chart_of_accounts_id_cur;
1466
1467 OPEN get_natural_account_col_cur(ln_coa_id);
1468 FETCH get_natural_account_col_cur
1469 INTO lv_nat_acc_seg_name;
1470 CLOSE get_natural_account_col_cur;
1471
1472
1473 OPEN get_balance_account_col_cur(ln_coa_id);
1474 FETCH get_balance_account_col_cur
1475 INTO lv_bal_acc_seg_name;
1476 CLOSE get_balance_account_col_cur;
1477
1478 -- Removed by Jia for bug#6775638 on 30-Dec-2008, Begin
1479 ----------------------------------------------------------------
1480 /* FOR operating_unit_rec IN get_operating_unit_cur
1481 LOOP
1482 */
1483 ----------------------------------------------------------------
1484 -- Removed by Jia for bug#6775638 on 30-Dec-2008, End
1485 FOR eligible_invoices_rec IN
1486 get_eligible_invoices_cur( lv_nat_acc_seg_name
1487 , lv_bal_acc_seg_name
1488 -- Modified by Jia for bug#7675638 on 30-Dec-2008, Begin
1489 ----------------------------------------------------------
1490 --, operating_unit_rec.operating_unit_id
1491 , pn_legal_entity_id
1492 ----------------------------------------------------------
1493 -- Modified by Jia for bug#7675638 on 30-Dec-2008, End
1494 , pn_fbt_year
1495 )
1496 LOOP
1497
1498 ln_nat_acct := eligible_invoices_rec.nat_acct_seg;
1499 lv_bal_acct := eligible_invoices_rec.bal_acct_seg;
1500 ln_dist_ccid := eligible_invoices_rec.dist_code_combination_id;
1501
1502 lv_source := 'Payables';
1503
1504 OPEN get_benefit_type_cur(ln_nat_acct);
1505 FETCH get_benefit_type_cur
1506 INTO lv_fringe_ben_type_code;
1507
1508 IF get_benefit_type_cur%FOUND THEN
1509 Calculate_Fbt_Amount( pn_legal_entity_id
1510 , lv_fringe_ben_type_code
1511 , eligible_invoices_rec.invoice_distribution_id
1512 , eligible_invoices_rec.amount
1513 , eligible_invoices_rec.invoice_currency_code
1514 , eligible_invoices_rec.exchange_rate_type
1515 , eligible_invoices_rec.exchange_date
1516 , lv_source
1517 , NULL
1518 , pn_fbt_year
1519 , fbt_repository_rec
1520 );
1521 END IF; --get_benefit_type_cur%FOUND
1522 CLOSE get_benefit_type_cur;
1523
1524 fbt_repository_rec.source := 'Payables';
1525 fbt_repository_rec.settlement_id := ln_settlement_id;
1526
1527 fbt_repository_rec.PERIOD_START_DATE := ld_start_date;
1528 fbt_repository_rec.PERIOD_END_DATE := ld_end_date;
1529 fbt_repository_rec.dist_code_combination_id := ln_dist_ccid;
1530 fbt_repository_rec.dist_natural_account_value := ln_nat_acct;
1531 fbt_repository_rec.dist_balance_account_value := lv_bal_acct;
1532
1533 /* put all the required values in the record type for insertion
1534 into jai_fbt_repository table
1535 */
1536 INSERT_FBT_REPOSITORY( fbt_repository_rec
1537 );
1538 END LOOP; -- eligible_invoices_rec IN get_eligible_invoices_cur
1539
1540 -- Removed by Jia for bug#7675638 on 30-Dec-2008, Begin
1541 ----------------------------------------------------------
1542 --END LOOP; -- operating_unit_rec IN get_operating_unit_cur
1543 -----------------------------------------------------------
1544 -- Removed by Jia for bug#7675638 on 30-Dec-2008, End
1545
1546
1547 --get journals and import into FBT repository on 28-July-2008,begin
1548 -----------------------------------------------------------------------------------
1549 FOR eligible_journals IN
1550 get_eligible_journals_cur( lv_nat_acc_seg_name
1551 , lv_bal_acc_seg_name
1552 , pn_fbt_year
1553 )
1554 LOOP
1555 ln_nat_acct := eligible_journals.nat_acct_seg;
1556 lv_bal_acct := eligible_journals.bal_acct_seg;
1557
1558 lv_source := 'Others';
1559
1560 OPEN get_benefit_type_cur(ln_nat_acct);
1561 FETCH get_benefit_type_cur
1562 INTO lv_fringe_ben_type_code;
1563
1564 IF get_benefit_type_cur%FOUND THEN
1565 Calculate_Fbt_Amount( pn_legal_entity_id
1566 , lv_fringe_ben_type_code
1567 , eligible_journals.je_line_ccid
1568 , eligible_journals.distribution_amt
1569 , eligible_journals.currency_code
1570 , eligible_journals.CURRENCY_CONVERSION_TYPE
1571 , eligible_journals.CURRENCY_CONVERSION_DATE
1572 , lv_source
1573 , eligible_journals.je_header_id
1574 , pn_fbt_year
1575 , fbt_repository_rec
1576 );
1577
1578 END IF; --get_benefit_type_cur%FOUND
1579 CLOSE get_benefit_type_cur;
1580
1581 fbt_repository_rec.source := 'Others'; -- Indicate journals from GL
1582 fbt_repository_rec.je_header_id := eligible_journals.je_header_id;
1583 fbt_repository_rec.batch_name := eligible_journals.je_batch_name;
1584 fbt_repository_rec.je_source := eligible_journals.je_source;
1585 fbt_repository_rec.je_name := eligible_journals.je_name;
1586 fbt_repository_rec.period_name := eligible_journals.period_name;
1587 fbt_repository_rec.je_line_num := eligible_journals.je_line_num;
1588
1589 fbt_repository_rec.invoice_date := eligible_journals.je_line_effective_date;
1590 fbt_repository_rec.invoice_currency_code := eligible_journals.currency_code;
1591 fbt_repository_rec.distribution_amt := eligible_journals.distribution_amt;
1592 fbt_repository_rec.dist_code_combination_id := eligible_journals.je_line_ccid;
1593 fbt_repository_rec.settlement_id := ln_settlement_id;
1594
1595 fbt_repository_rec.dist_natural_account_value := ln_nat_acct;
1596 fbt_repository_rec.dist_balance_account_value := lv_bal_acct;
1597
1598 fbt_repository_rec.PERIOD_START_DATE := ld_start_date;
1599 fbt_repository_rec.PERIOD_END_DATE := ld_end_date;
1600
1601 /* put all the required values in the record type for insertion
1602 into jai_fbt_repository table
1603 */
1604 INSERT_FBT_REPOSITORY( fbt_repository_rec
1605 );
1606 END LOOP; -- eligible_journals IN get_eligible_journals_cur
1607
1608
1609 -----------------------------------------------------------------------------------
1610 --get journals and import into FBT repository on 28-July-2008,end
1611
1612
1613 --update processing date in JAI_FBT_PROCESS_DATE for R12 new change on 06-Nov-2008, begin
1614 -----------------------------------------------------------------------------------------
1615 BEGIN
1616 SELECT period_end_date
1617 INTO ld_period_end_date
1618 FROM jai_fbt_process_date
1619 WHERE legal_entity_id = pn_legal_entity_id
1620 AND fbt_year = pn_fbt_year;
1621
1622 IF (ld_end_date >= ld_period_end_date) THEN
1623 UPDATE jai_fbt_process_date
1624 SET period_end_date = ld_end_date
1625 WHERE legal_entity_id = pn_legal_entity_id
1626 AND fbt_year = pn_fbt_year;
1627 END IF;
1628 EXCEPTION
1629 WHEN NO_DATA_FOUND THEN
1630 INSERT
1631 INTO JAI_FBT_PROCESS_DATE(LEGAL_ENTITY_ID,
1632 FBT_YEAR,
1633 PERIOD_START_DATE,
1634 PERIOD_END_DATE,
1635 CREATION_DATE,
1636 CREATED_BY,
1637 LAST_UPDATE_DATE,
1638 LAST_UPDATED_BY,
1639 LAST_UPDATE_LOGIN)
1640 VALUES(pn_legal_entity_id,
1641 pn_fbt_year,
1642 ld_start_date,
1643 ld_end_date,
1644 SYSDATE,
1645 FND_GLOBAL.USER_ID,
1646 SYSDATE,
1647 FND_GLOBAL.USER_ID,
1648 FND_GLOBAL.LOGIN_ID);
1649
1650 END;
1651 -----------------------------------------------------------------------------------------
1652 --update processing date in JAI_FBT_PROCESS_DATE for R12 new change on 06-Nov-2008, end
1653
1654 COMMIT;
1655
1656
1657 --logging for debug
1658 IF (ln_proc_level >= ln_dbg_level)
1659 THEN
1660 FND_LOG.STRING( ln_proc_level
1661 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
1662 , 'Exit procedure'
1663 );
1664 END IF; -- (ln_proc_level>=ln_dbg_level)
1665
1666 EXCEPTION
1667 WHEN OTHERS THEN
1668 pv_errbuf :=SQLERRM;
1669 pv_retcode:=2;
1670 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1671 THEN
1672 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1673 , GV_MODULE_PREFIX ||'.' || lv_procedure_name
1674 || '.Other_Exception '
1675 , Sqlcode||Sqlerrm);
1676 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1677 END Fbt_Inv_Process;
1678
1679 END JAI_FBT_PROCESS_P; --END OF PACKAGE
1680
1681