1 PACKAGE BODY ZX_TAX_RECOVERY_PKG AS
2 /* $Header: zxpotrxrecb.pls 120.2 2005/09/21 23:44:57 hongliu ship $ */
3 -- PUBLIC FUNCTION
4 -- Get_Po_Distribution_Rate
5 --
6 -- DESCRIPTION
7 --REM------------------------------------------------------------------------+
8 -- This function is called when the invoice is matched with the purchase order
9 -- and the tax_from_po_flag in the ap_system_parameters is 'Y'.
10 --REM------------------------------------------------------------------------+
11
12 debug_info varchar2(100);
13 debug_loc varchar2(30);
14
15 PG_DEBUG varchar2(1);
16
17 -- These variables added for bulk collect.
18
19 type num_tab is table of number index by binary_integer;
20 type var2000_tab is table of varchar2(2000) index by binary_integer;
21 type var255_tab is table of varchar2(255) index by binary_integer;
22 g_delimiter varchar2(1);
23 g_chart_of_accts gl_sets_of_books.chart_of_accounts_id%TYPE;
24
25
26
27 l_rec_rate_tab num_tab;
28 l_concatenate_segment_low_tab var2000_tab;
29 l_concatenate_segment_high_tab var2000_tab;
30 l_procedure_tab var255_tab;
31
32
33 -- PROCEDURE initialize;
34
35 FUNCTION Get_Po_Distribution_Rate (p_distribution_id IN
36 po_distributions_all.po_distribution_id%TYPE
37 ) RETURN NUMBER IS
38
39 l_rec_rate po_distributions_all.recovery_rate%TYPE;
40 --l_curr_calling_sequence VARCHAR2(2000);
41
42 CURSOR c_recovery_rate IS
43 SELECT pod.recovery_rate
44 FROM po_distributions_all pod
45 WHERE pod.po_distribution_id = p_distribution_id;
46 BEGIN
47 --REM-------------------DEBUG INFORMATION------------------------------------+
48
49
50
51 debug_loc := 'Get_Po_Distribution_Rate';
52 --AP_LOGGING_PKG.AP_Begin_Block(debug_loc);
53 debug_info:='Getting PO Distribution Rate';
54 --AP_LOGGING_PKG.AP_Log(debug_info,debug_loc);
55
56 OPEN c_recovery_rate;
57 FETCH c_recovery_rate INTO l_rec_rate;
58 CLOSE c_recovery_rate;
59 --AP_LOGGING_PKG.AP_End_Block(debug_loc);
60 --AP_LOGGING_PKG.AP_End_Log;
61
62 return(l_rec_rate);
63 EXCEPTION
64 WHEN OTHERS THEN
65
66 IF (c_recovery_rate%ISOPEN) THEN
67 CLOSE c_recovery_rate;
68 END IF;
69 RAISE;
70 END Get_Po_Distribution_Rate;
71
72
73
74 ------------------------------------------------------------------------
75 --
76 -- PUBLIC FUNCTION
77 -- account_in_range
78 --
79 -- PURPOSE
80 -- This is a new function added to the ap_tax_recovery_pkg in order
81 -- to fix bugs in the ap_tax_recovery_pkg introduced by bug#897398
82 -- in form Define Tax Recovery Rules (APXTADRR.fmb).
83 -- Since fixing bug #897398 will affect the way defaulting recovery
84 -- rate in funciton get_rule_rate in the ap_tax_recovery_pkg
85 -- (aptxrecs.pls, aptxrecb.pls), changes should be made accordingly.
86 --
87 -- This function is called by get_rule_rate in ap_tax_recovery_pkg.
88 --
89 -- ASSUMPTION
90 -- This solution assumes that the requirements for get_rule_rate are
91 -- same as the requirements in GBV (U.K localization)
92 -- The logic of this function is based on JEUKVVLG.get_reclaim_percent.
93 -- Confirmed with Fiona Purves on June 28, 1999.
94 --
95 -- DESCRIPTION
96 -- This function takes three IN parameters which are concatenated
97 -- segments string, break up those string into segments, check from
98 -- right most segment and return 1 if ALL segments in p_passed_concat_segs
99 -- are between ALL segments in p_db_concat_segs_low and each segment
100 -- in p_db_concat_segs_high, return 0 if one segment is not in the range.
101 --
102 -- PARAMETERS
103 -- p_passed_concat_segs IN VARCHAR2
104 -- p_db_concat_segs_low IN VARCHAR2
105 -- p_db_concat_segs_high IN VARCHAR2
106 --
107 -- RETURN
108 -- in_range NUMBER
109 --
110 -- HISTORY
111 -- Wei Feng 30-JUNE-99 Created
112 --
113 -- Wei Feng 30-Sept-99 Fixed bug 961973
114 --
115 -- Null segements in the account low will be replaced by an enterable
116 -- character with lowest ASCII value ('!'), null segements in the
117 -- account high will be replaced by an enterable character with
118 -- highest ASCII value ('~').
119 --
120 --------------------------------------------------------------------------
121
122 FUNCTION account_in_range
123 (p_passed_concat_segs IN VARCHAR2,
124 p_db_concat_segs_low IN VARCHAR2,
125 p_db_concat_segs_high IN VARCHAR2,
126 p_chart_of_accts IN VARCHAR2) return NUMBER IS
127
128
129 l_nsegs number;
130 l_passed_segs_array fnd_flex_ext.SegmentArray;
131 l_db_segs_array_low fnd_flex_ext.SegmentArray;
132 l_db_segs_array_high fnd_flex_ext.SegmentArray;
133 i number;
134 -- 1 stands for account in the range, 0 stands for account NOT in range.
135 in_range number := 1;
136
137 BEGIN
138
139
140
141 --------------------DEBUG INFORMATION------------------------------------
142 debug_loc := 'account_in_range';
143 --AP_LOGGING_PKG.AP_Begin_Block(debug_loc);
144 debug_info:='Check if the passing account is in the range.';
145 --AP_LOGGING_PKG.AP_Log(debug_info,debug_loc);
146
147 -- Bugfix 1798261
148 --AP_LOGGING_PKG.AP_End_Block(debug_loc);
149 --AP_LOGGING_PKG.AP_End_Log;
150
151
152 -- Call fnd_flex_ext.breakup_segments to break up segments
153 l_nsegs := fnd_flex_ext.breakup_segments
154 (concatenated_segs => p_passed_concat_segs,
155 delimiter => g_delimiter,
156 segments => l_passed_segs_array);
157
158
159
160 -- Check if l_nsegs = 0 or p_passed_concat_segs is null.
161 -- Then we assume that the passed account IS NOT in range, and do not
162 -- need to check any furthure.
163 IF l_nsegs = 0 or p_passed_concat_segs is null THEN
164 in_range := 0;
165
166
167 END IF;
168 RETURN in_range;
169
170
171 l_nsegs := fnd_flex_ext.breakup_segments
172 (concatenated_segs => p_db_concat_segs_low,
173 delimiter => g_delimiter,
174 segments => l_db_segs_array_low);
175
176 l_nsegs := fnd_flex_ext.breakup_segments
177 (concatenated_segs => p_db_concat_segs_high,
178 delimiter => g_delimiter,
179 segments => l_db_segs_array_high);
180
181
182
183 -- Check if every segment in the passed in account is in the range of every segment in
184 -- db account low and high,
185 -- Return 1 if all segements are in the range, return 0 if any one segment
186 -- is not in the range.
187
188 FOR i in REVERSE 1 .. l_nsegs LOOP
189
190 --IF all l_passed_segs_array(i) BETWEEN NVL(l_db_segs_array_low(i), '!') AND
191 --NVL(l_db_segs_array_high(i), '~') is not true
192 -- in another words is that IF any one segement in the passed account is NOT in the range
193 --
194 -- THEN this passed account IS NOT in the range
195 -- in_range := 0;
196 --
197 -- Please refer to the logic in JEUKVVLG.get_reclaim_percent.
198
199 IF l_passed_segs_array(i) NOT BETWEEN NVL(l_db_segs_array_low(i), '!') AND
200 NVL(l_db_segs_array_high(i), '~') THEN
201 --This segment IS NOT in the range and this account IS NOT in range
202 in_range := 0;
203 END IF;
204
205 END LOOP;
206
207
208 RETURN in_range;
209
210 EXCEPTION
211 WHEN OTHERS THEN
212
213 RAISE;
214 END account_in_range;
215
216
217
218 ------------------------------------------------------------------------
219 --
220 -- PUBLIC FUNCTION
221 -- account_overlap
222 --
223 -- PURPOSE
224 -- This is a new function added to fix bug#897398
225 --
226 -- This function is called by procedure rec_rates.check_overlapping
227 -- in APXTADRR.fmb.
228 --
229 -- ASSUMPTION
230 -- This solution assumes that the requirements for this form are
231 -- same as the requirements in GBV (U.K localization)
232 -- The logic of this function is based on GBV solution.
233 -- Confirmed with Fiona Purves on June 28, 1999.
234 --
235 -- DESCRIPTION
236 -- This function takes four IN parameters
237 -- (p_form_concat_segs_low (FRML)
238 -- p_form_concat_segs_high (FRMH)
239 -- p_db_concat_segs_low (DBL)
240 -- p_db_concat_segs_high (DBH)
241 -- )
242 -- which are concatenated segments string, break up those string into segments,
243 -- check from right most segment, (assume the accounts have 4 segments)
244 --
245 -- IF DBL(4) <= FRMH(4) AND
246 -- DBH(4) >= FRML(4) AND
247 -- DBL(3) <= FRMH(3) AND
248 -- DBH(3) >= FRML(3) AND
249 -- DBL(2) <= FRMH(2) AND
250 -- DBH(2) >= FRML(2) AND
251 -- DBL(1) <= FRMH(1) AND
252 -- DBH(1) >= FRML(1)
253 -- (ALL the above conditions are true) THEN
254 -- account IS overlapping, return 1;
255 --
256 -- ELSE
257 -- for ANY of the above condition IS NOT true
258 -- account IS NOT overlapping, return 0;
259 -- END IF;
260 --
261 --
262 -- PARAMETERS
263 -- p_form_concat_segs_low IN VARCHAR2
264 -- p_form_concat_segs_high IN VARCHAR2
265 -- p_db_concat_segs_low IN VARCHAR2
266 -- p_db_concat_segs_high IN VARCHAR2
267 --
268 -- RETURN
269 -- account_overlap NUMBER
270 --
271 -- EXAMPLES
272 --
273 --1. Example for account NOT overlapping:
274 --
275 -- FROM (LOW) TO (HIGH)
276 --Account Range #1: 01-110-7740-0000-000 01-840-7740-0000-000 (saved in database)
277 --Account Range #2: 01-110-7710-0000-000 01-840-7710-0000-000 (just entered in form not commit yet)
278 --
279 --
280 --2. Example for account overlapping:
281 --
282 -- FROM (LOW) TO (HIGH)
283 --Account Range #1: 01-000-1000-0000-000 01-000-1500-0000-000 (saved in database)
284 --Account Range #2: 01-000-1200-0000-000 01-000-1900-0000-200 (just entered in form not commit yet)
285 --
286 --
287 --
288 -- HISTORY
289 -- Wei Feng 30-JUNE-99 Created
290 --
291 -- Wei Feng 30-Sept-99 Fixed bug 961973
292 --
293 -- Null segements in the account low will be replaced by an enterable
294 -- character with lowest ASCII value ('!'), null segements in the
295 -- account high will be replaced by an enterable character with
296 -- highest ASCII value ('~').
297 --
298 --
299 --
300 --
301 --------------------------------------------------------------------------
302
303 FUNCTION account_overlap
304 (p_form_concat_segs_low IN VARCHAR2,
305 p_form_concat_segs_high IN VARCHAR2,
306 p_db_concat_segs_low IN VARCHAR2,
307 p_db_concat_segs_high IN VARCHAR2) return NUMBER IS
308
309 l_chart_of_accts gl_sets_of_books.chart_of_accounts_id%TYPE;
310 l_delimiter varchar2(1);
311 l_nsegs number;
312 l_form_segs_array_low fnd_flex_ext.SegmentArray;
313 l_form_segs_array_high fnd_flex_ext.SegmentArray;
314 l_db_segs_array_low fnd_flex_ext.SegmentArray;
315 l_db_segs_array_high fnd_flex_ext.SegmentArray;
316 i number;
317 -- 1 stands for overlapping, 0 stands for NOT overlapping
318 account_overlap number :=1;
319
320 BEGIN
321
322
323 --------------------DEBUG INFORMATION------------------------------------
324 debug_loc := 'account_overlap';
325 --AP_LOGGING_PKG.AP_Begin_Block(debug_loc);
326 debug_info:='Check if the account is overlapping.';
327 --AP_LOGGING_PKG.AP_Log(debug_info,debug_loc);
328
329 -- Get chart_of_account_id
330
331 SELECT chart_of_accounts_id
332 INTO l_chart_of_accts
333 FROM gl_sets_of_books,ap_system_parameters_all
334 WHERE gl_sets_of_books.set_of_books_id = ap_system_parameters_all.set_of_books_id;
335
336
337 -- Call fnd_flex_ext.get_delimiter to get delimiter
338
339
340 l_delimiter := fnd_flex_ext.get_delimiter
341 (application_short_name => 'SQLGL',
342 key_flex_code => 'GL#',
343 structure_number => l_chart_of_accts);
344
345
346 -- Call fnd_flex_ext.breakup_segments to break up segments
347
348
349 l_nsegs := fnd_flex_ext.breakup_segments
350 (concatenated_segs => p_form_concat_segs_low,
351 delimiter => l_delimiter,
352 segments => l_form_segs_array_low);
353
354
355 l_nsegs := fnd_flex_ext.breakup_segments
356 (concatenated_segs => p_form_concat_segs_high,
360
357 delimiter => l_delimiter,
358 segments => l_form_segs_array_high);
359
361
362
363 l_nsegs := fnd_flex_ext.breakup_segments
364 (concatenated_segs => p_db_concat_segs_low,
365 delimiter => l_delimiter,
366 segments => l_db_segs_array_low);
367
368
369 l_nsegs := fnd_flex_ext.breakup_segments
370 (concatenated_segs => p_db_concat_segs_high,
371 delimiter => l_delimiter,
372 segments => l_db_segs_array_high);
373
374
375
376 -- Check if account is overlapping,
377 -- Return 1 if yes, return 0 if no.
378 -- The logic is based on the GBV solution.
379
380 FOR i in REVERSE 1 .. l_nsegs LOOP
381
382 --IF any NVL(l_db_segs_array_low(i), '!') <= NVL(l_form_segs_array_high(i), '~') AND
383 --NVL(l_db_segs_array_high(i), '~') >= NVL(l_form_segs_array_low(i), '!')
384 --when looping IS NOT TRUE (which is the following case) THEN
385 --This IS NOT overlapping
386
387 IF NVL(l_db_segs_array_low(i), '!') > NVL(l_form_segs_array_high(i), '~') OR
388 NVL(l_db_segs_array_high(i), '~') < NVL(l_form_segs_array_low(i), '!') THEN
389
390 --This IS NOT overlapping (Please refer to GBV solution for rational)
391 account_overlap := 0;
392 END IF;
393
394 END LOOP;
395
396
397
398 --AP_LOGGING_PKG.AP_End_Block(debug_loc);
399 --AP_LOGGING_PKG.AP_End_Log;
400
401 RETURN account_overlap;
402
403 EXCEPTION
404 WHEN OTHERS THEN
405
406
407 RAISE;
408 END account_overlap;
409
410
411
412 --REM ----------------------------------------------------------------------+
413 --REM This function is called when the tax code has a rule
414 --REM associated with it.
415 --REM It can be called by Payables or Purchasing.The function returns the
416 --REM recovery rate based on the following parameters : p_code_combination_id
417 --REM p_tax_date(document date) and p_vendor_id.
418 --REM Bug Fix 1137973 : Changed by Debasis on 06-Jan-2000 .
419 --REM For uniform naming convention l_rule parameter IN changed to " p_rule"
420 --REM ----------------------------------------------------------------------+
421 FUNCTION Get_Rule_Rate (p_rule IN NUMBER,
422 p_tax_date IN DATE default SYSDATE,
423 p_vendorclass in po_vendors.vendor_type_lookup_code%TYPE,
424 p_concatenate in VARCHAR2) RETURN NUMBER IS
425
426 l_rec_rate ap_tax_recvry_rates_all.recovery_rate%TYPE;
427 l_procedure ap_tax_recvry_rates_all.function%TYPE;
428 p_rate NUMBER;
429 return_val NUMBER;
430 cid NUMBER;
431 sql_string varchar2(255);
432 -- l_curr_calling_sequence varchar2(2000);
433 l_concatenated_segment_low ap_tax_recvry_rates_all.concatenated_segment_low%type;
434 l_concatenated_segment_high ap_tax_recvry_rates_all.concatenated_segment_high%type;
435
436
437 --REM--To get the Recovery Rate if the start and end dates are specified
438 -- BUG 2576240 Replace Sysdate with p_tax_date to allow comparison to succeed
439
440 CURSOR c_recovery_rate_cond_null IS
441 SELECT tr.recovery_rate,tr.function,
442 tr.concatenated_segment_low,tr.concatenated_segment_high
443 FROM ap_tax_recvry_rates_all tr
444 WHERE tr.rule_id = p_rule
445 AND p_tax_date BETWEEN tr.start_date AND nvl(tr.end_date,p_tax_date+ 1)
446 AND tr.enabled_flag = 'Y'
447 AND tr.condition_value IS NULL;
448
449
450 CURSOR c_recovery_rate_cond_notnull IS
451 SELECT tr.recovery_rate,tr.function,
452 tr.concatenated_segment_low,tr.concatenated_segment_high
453 FROM ap_tax_recvry_rates_all tr
454 WHERE tr.rule_id = p_rule
455 AND p_tax_date BETWEEN tr.start_date AND nvl(tr.end_date,p_tax_date+ 1)
456 AND tr.enabled_flag = 'Y'
457 AND tr.condition_value = p_vendorclass;
458
459
460 BEGIN
461 --REM -----------------------DEBUG INFORMATION--------------------------------+
462
463
464 debug_loc := 'Get_Rule_Rate';
465 --AP_LOGGING_PKG.AP_Begin_Block(debug_loc);
466 debug_info:='Getting Tax Rule Rate';
467 --AP_LOGGING_PKG.AP_Log(debug_info,debug_loc);
468
469 IF p_concatenate IS NULL THEN
470 l_rec_rate := 0;
471
472 return(l_rec_rate);
473 END IF;
474
475
476 OPEN c_recovery_rate_cond_notnull;
477
478 <<outer>>
479 LOOP
480 FETCH c_recovery_rate_cond_notnull Bulk collect into
481 l_rec_rate_tab,
482 l_procedure_tab,
483 l_concatenate_segment_low_tab,
484 l_concatenate_segment_high_tab
485 LIMIT 1000;
486
487 FOR i in 1..l_rec_rate_tab.count LOOP
488
489 If account_in_range(p_concatenate,l_concatenate_segment_low_tab(i),l_concatenate_segment_high_tab(i),g_chart_of_accts) <> 0 then
490 l_rec_rate := l_rec_rate_tab(i);
491 l_procedure := l_procedure_tab(i);
492 exit outer;
493 End If;
494
495 l_rec_rate := Null;
496 l_procedure := NUll;
497
498 END LOOP;
499 EXIT WHEN c_recovery_rate_cond_notnull%NOTFOUND;
500 END LOOP;
501
502 CLOSE c_recovery_rate_cond_notnull;
503
504 if l_rec_rate is null then
505 OPEN c_recovery_rate_cond_null;
506
507 <<outer1>>
508 LOOP
509 FETCH c_recovery_rate_cond_null Bulk collect into
510 l_rec_rate_tab,
511 l_procedure_tab,
512 l_concatenate_segment_low_tab,
513 l_concatenate_segment_high_tab
514 LIMIT 1000;
515
516 FOR i in 1..l_rec_rate_tab.count LOOP
517 If account_in_range(p_concatenate,l_concatenate_segment_low_TAB(I),l_concatenate_segment_high_TAB(I),g_chart_of_accts) <> 0 then
518 l_rec_rate := l_rec_rate_tab(i);
519 l_procedure := l_procedure_tab(i);
520 exit outer1;
521 End If;
522 l_rec_rate := Null;
523 l_procedure := NUll;
524 END LOOP;
525
526 EXIT WHEN c_recovery_rate_cond_null%NOTFOUND;
527
528 END LOOP;
529
530 CLOSE c_recovery_rate_cond_null;
531
532 end if;
533
534 --REM -----------------------------------------------------------------------+
535 --REM If function column in the table ap_tax_recvry_rates is not null then call
536 --REM the User Defined Procedure that takes no parameters and writes all the
540 --REM -----------------------------------------------------------------------+
537 --REM calculated values to the Global area G_tax_info_rec in AP_TAX_ENGINE_PKG
538 --REM ------------------------------------------------------------------------+
539 if l_procedure IS NOT NULL then
541 -- Prepare for the Dynamic SQL
542 --REM ------------------------------------------------------------------------+
543
544
545 begin
546 --Used Native Dynamic SQL 1064036
547
548 sql_string := 'begin '||l_procedure||'; end;';
549
550 EXECUTE IMMEDIATE sql_string ;
551
552
553
554 exception
555 when others then
556
557
558 raise;
559 end ;
560 --REM -----------------------------------------------------------------------+
561 --REM Get the tax_recovery_rate from the G_tax_info_rec that was populated
562 --REM by the user Defined Procedure
563 --REM------------------------------------------------------------------------+
564 p_rate := AP_TAX_ENGINE_PKG.G_tax_info_rec.tax_recovery_rate;
565 --AP_LOGGING_PKG.AP_End_Block(debug_loc);
566 --AP_LOGGING_PKG.AP_End_Log;
567
568 return (p_rate);
569 else
570 --REM ----------------------------------------------------------------------+
571 -- Return the recovery_rate if the function was not available in the
572 -- ap_tax_recvry_rates table
573 --REM ----------------------------------------------------------------------+
574
575 return(l_rec_rate);
576 end if;
577 EXCEPTION
578 WHEN OTHERS THEN
579
580 IF (c_recovery_rate_cond_null%ISOPEN) THEN
581 CLOSE c_recovery_rate_cond_null;
582 ELSIF (c_recovery_rate_cond_notnull%ISOPEN) THEN
583 CLOSE c_recovery_rate_cond_notnull;
584 END IF;
585 RAISE;
586 END Get_Rule_Rate;
587
588
589 -- PUBLIC PROCEDURE
590 -- Get_Default_Rate
591 -- DESCRIPTION
592 --
593 -- The procedure is passed a variety of parameters to get
594 -- the default rate that is to be used by Payables and Purchasing.
595 --
596 -- PARAMETERS
597 -- p_tax_code IN
598 -- p_tax_id IN
599 -- p_tax_date IN
600 -- p_code_combination_id IN
601 -- p_vendor_id IN
602 -- p_distribution_id IN
603 -- p_tax_user_override_flag IN
604 -- If the override_flag is 'Y' the user_defined recovery rate is returned.
605 -- p_user_tax_recovery_rate IN
606 -- p_concatenated_segments IN
607 -- p_vendor_site_id IN
608 -- p_inventory_item_id IN
609 -- p_item_org_id IN
610 -- APPL_SHORT_NAME IN
611 -- FUNC_SHORT_NAME IN
612 -- p_calling_sequence IN
613 -- p_tax_recovery_rate IN OUT NOCOPY
614
615 /* Get_Default_Rate */
616 PROCEDURE Get_Default_Rate (p_tax_code IN ap_tax_codes_all.name%TYPE,
617 p_tax_id IN ap_tax_codes_all.tax_id%TYPE,
618 p_tax_date IN DATE default SYSDATE,
619 p_code_combination_id IN gl_code_combinations.code_combination_id%TYPE,
620 p_vendor_id IN po_vendors.vendor_id%TYPE,
621 p_distribution_id IN po_distributions_all.po_distribution_id%TYPE,
622 p_tax_user_override_flag IN VARCHAR2,
623 p_user_tax_recovery_rate IN ap_tax_codes_all.tax_rate%TYPE,
624 p_concatenated_segments IN VARCHAR2,
625 p_vendor_site_id IN po_vendor_sites_all.vendor_site_id%TYPE,
626 p_inventory_item_id IN mtl_system_items.inventory_item_id%TYPE,
627 p_item_org_id IN mtl_system_items.organization_id%TYPE,
628 APPL_SHORT_NAME IN fnd_application.application_short_name%TYPE,
629 FUNC_SHORT_NAME IN VARCHAR2 default 'NONE',
630 p_calling_sequence IN VARCHAR2,
631 p_chart_of_accounts_id IN gl_ledgers.chart_of_accounts_id%TYPE,
632 p_tc_tax_recovery_rule_id IN ap_tax_codes_all.tax_recovery_rule_id%TYPE,
633 p_tc_tax_recovery_rate IN ap_tax_codes_all.tax_recovery_rate%TYPE,
634 p_vendor_type_lookup_code IN po_vendors.vendor_type_lookup_code%TYPE,
635 p_tax_recovery_rate IN OUT NOCOPY number) AS
636
637
638 l_curr_calling_sequence VARCHAR2(2000);
639 l_rule_id ap_tax_codes_all.tax_recovery_rule_id%TYPE;
640 l_tax_rate ap_tax_codes_all.tax_recovery_rate%TYPE;
641 -- TYPE get_rec_rate_tbl1 IS TABLE OF PO_DISTRIBUTIONS_ALL.RECOVERY_RATE%TYPE INDEX BY BINARY_INTEGER;
642 -- l_tax_rate get_rec_rate_tbl1;
643 -- l_tax_rate get_rec_rate_tbl;
644 i number;
645 l_rec_rate NUMBER;
646 l_tax_code_id NUMBER ;
647 -- == l_non_rec_tax_flag financials_system_params_all.non_recoverable_tax_flag%TYPE;
648 -- == l_tax_from_po_flag ap_system_parameters_all.tax_from_po_flag%TYPE;
649 l_tax_rule_rate ap_tax_recvry_rates_all.recovery_rate%TYPE;
650 lp_concatenate varchar2(2000);
651 l_cond_val po_vendors.vendor_type_lookup_code%TYPE;
652 l_tax_type ap_tax_codes_all.tax_type%type;
653 l_get_rule_rate NUMBER;
654 -- == l_match_on_tax_flag ap_system_parameters_all.match_on_tax_flag%type;--added for bug 3960162.
655
656 --REM
657 --REM-----------------------------------------------------------------------+
658 --REM To get the Tax code from PO
659 --REM-----------------------------------------------------------------------+
660 --REM
661 /* ==
662 CURSOR c_tax_code_id IS
663 SELECT pll.tax_code_id
664 from po_line_locations_all pll,
665 po_distributions_all po
666 where pll.line_location_id = po.line_location_id and
667 po.po_distribution_id = p_distribution_id;
668
669 == */
670
671 --REM-----------------------------------------------------------------------+
672 --REM To get the rule_id and rate based on the tax name and document date
673 --REM ----------------------------------------------------------------------+
674 /* ==
675 --Fixed bug 1036684, removed nvl from cursor.
676 CURSOR c_rule_rate_cd IS
677 SELECT tc.tax_recovery_rule_id, tc.tax_recovery_rate
678 FROM ap_tax_codes_all tc
679 WHERE tc.name = p_tax_code
680 AND p_tax_date BETWEEN tc.start_date
681 AND nvl(tc.inactive_date,p_tax_date)
682 AND tc.enabled_flag='Y' ;
683 == */
684
685 --Fixed bug 1811026 changed sysdate +1 to p_tax_date
686 --REM ----------------------------------------------------------------------+
687 --REM To get the rule_id and rate based on the tax_id
688 --REM ----------------------------------------------------------------------+
689 /* ==
690 --Fixed bug 1036684, removed nvl from cursor.
691 CURSOR c_rule_rate_id IS
692 SELECT tc.tax_recovery_rule_id,tc.tax_recovery_rate
693 FROM ap_tax_codes_all tc
694 WHERE tc.tax_id = p_tax_id
695 AND tc.enabled_flag='Y';
696 -- Bug1094321 fix , added Condition value for the vendor
697 CURSOR c_cond_val IS
698 SELECT vendor_type_lookup_code
699 FROM po_vendors
700 WHERE po_vendors.vendor_id = p_vendor_id;
701
702 == */
703
704 /*created the cursor for bug 3960162 to fetch the match_on_tax_flag to default
705 the recovery rate when PO matched with invoice.*/
706 /* ==
707 CURSOR c_get_match_on_tax_flag IS
708 SELECT match_on_tax_flag
709 FROM ap_system_parameters_all;
710 --end for 3960162
711 == */
712
713 -- Bug Fix for 1003548 and Bug fix 1094321
714 -- To get the recovery rate for a rule within an account range for Bug 1003548
715 -- Added the conditon_value to the query for Bug 1094321
716 /* ==
717 CURSOR c_get_fin_param IS
718 SELECT non_recoverable_tax_flag
719 FROM FINANCIALS_SYSTEM_PARAMS_ALL;
720 == */
721
722 /* ==
723 CURSOR c_get_ap_system_parameter IS
724 SELECT tax_from_po_flag
725 FROM ap_system_parameters_all;
726 == */
727
728 /* Bugfix 1161905 */
729
730 /* ==
731 CURSOR c_tax_type is
732 SELECT tax_type
733 FROM ap_tax_codes_all
734 WHERE tax_id = p_tax_id;
735
736 CURSOR c_tax_type_csr is
737 SELECT tax_type
738 FROM ap_tax_codes_all
739 WHERE tax_id in (SELECT
740 tax_id
741 FROM ap_tax_codes_all
742 WHERE
743 name = p_tax_code and
744 p_tax_date between start_date and
745 inactive_date and enabled_flag = 'Y');
746 == */
747
748 BEGIN
749
750 -- initialize;
751 g_chart_of_accts := p_chart_of_accounts_id;
752
753 -- Call fnd_flex_ext.get_delimiter to get delimiter
754
755 g_delimiter := fnd_flex_ext.get_delimiter
756 (application_short_name => 'SQLGL',
757 key_flex_code => 'GL#',
758 structure_number => g_chart_of_accts);
759
760 --AP_LOGGING_PKG.AP_Begin_Log('AP_TAX_RECOVERY_PKG',2000);
761 --REM -------------------DEBUG INFORMATION------------------------------------+
762 debug_loc:= 'Get_Default_Rate';
766 /* ==
763 --AP_LOGGING_PKG.AP_Begin_Block(debug_loc);
764 l_curr_calling_sequence := 'AP_TAX_RECOVERY_PKG.'||debug_loc||'<-'||p_calling_sequence;
765 debug_info := 'Getting default tax rate';
767 --AP_LOGGING_PKG.AP_Log(debug_info,debug_loc);
768 OPEN c_get_fin_param;
769 FETCH c_get_fin_param INTO l_non_rec_tax_flag;
770 CLOSE c_get_fin_param;
771 == */
772 /* ==
773 OPEN c_get_ap_system_parameter;
774 FETCH c_get_ap_system_parameter INTO l_tax_from_po_flag;
775 CLOSE c_get_ap_system_parameter;
776 == */
777 /* ==
778 OPEN c_cond_val;
779 FETCH c_cond_val into l_cond_val;
780 CLOSE c_cond_val;
781 ==*/
782 l_cond_val := p_vendor_type_lookup_code;
783
784 /* ==
785 OPEN c_get_match_on_tax_flag;
786 FETCH c_get_match_on_tax_flag into l_match_on_tax_flag;
787 CLOSE c_get_match_on_tax_flag;
788 == */
789
790 lp_concatenate := fnd_flex_ext.get_segs
791 ('SQLGL','GL#', g_chart_of_accts, p_code_combination_id);
792
793 IF lp_concatenate is null then
794 lp_concatenate := p_concatenated_segments;
795 END IF;
796
797 IF p_tax_id IS NOT NULL THEN
798
799 /* == OPEN c_rule_rate_id;
800 FETCH c_rule_rate_id into l_rule_id,l_tax_rate;
801 CLOSE c_rule_rate_id;
802 == */
803 l_rule_id := p_tc_tax_recovery_rule_id;
804 l_tax_rate := p_tc_tax_recovery_rate;
805
806 IF l_rule_id IS NULL THEN
807 p_tax_recovery_rate := l_tax_rate;
808
809 ELSE
810 l_tax_rule_rate := Get_Rule_Rate(l_rule_id,
811 p_tax_date,
812 l_cond_val,
813 lp_concatenate);
814
815 p_tax_recovery_rate := l_tax_rule_rate;
816
817 END IF;
818
819 END IF;
820
821 EXCEPTION
822 WHEN NO_DATA_FOUND THEN
823 NULL;
824
825 END Get_Default_Rate;
826
827 /* ==
828
829 PROCEDURE initialize IS
830 --REM -----------------------------------------------------------------------+
831 --REM To get the chart_of accounts which is used to get the concatenated segment
832 --REM -----------------------------------------------------------------------+
833
834
835 CURSOR c_chart_of_accts IS
836 SELECT chart_of_accounts_id
837 FROM gl_sets_of_books,ap_system_parameters_all
838 WHERE gl_sets_of_books.set_of_books_id = ap_system_parameters_all.set_of_books_id;
839
840 BEGIN
841
842 -- PG_DEBUG := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
843 PG_DEBUG := 'Y';
844
845 OPEN c_chart_of_accts;
846 FETCH c_chart_of_accts INTO g_chart_of_accts;
847 CLOSE c_chart_of_accts;
848
849 -- Call fnd_flex_ext.get_delimiter to get delimiter
850
851 g_delimiter := fnd_flex_ext.get_delimiter
852 (application_short_name => 'SQLGL',
853 key_flex_code => 'GL#',
854 structure_number => g_chart_of_accts);
855
856 END initialize;
857 == */
858 -------------------------------------------------------------------------------
859 --
860 -- get_system_tax_defaults
861 --
862 -------------------------------------------------------------------------------
863 BEGIN
864
865 -- initialize;
866 NULL;
867
868 END ZX_TAX_RECOVERY_PKG;
[Home] [Help]