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