1 PACKAGE BODY PQP_CPYTAXRUL AS
2 /* $Header: pqtrulcp.pkb 115.2 2004/05/14 14:11:24 tmehra noship $ */
3
4 PROCEDURE COPY_TAX_RULES (
5 errbuf OUT NOCOPY VARCHAR2,
6 retcode OUT NOCOPY NUMBER,
7 p_classification_name IN pay_element_classifications.classification_name%type,
8 p_source_category IN pay_taxability_rules.tax_category%type,
9 p_target_category IN pay_taxability_rules.tax_category%type,
10 p_source_state_code IN pay_us_states.state_code%type,
11 p_target_state_code IN pay_us_states.state_code%type
12 )
13 IS
14
15 l_classification_id pay_element_classifications.classification_id%type;
16 l_tax_rules_date_id pay_taxability_rules_dates.taxability_rules_date_id%type;
17 l_lookup_type fnd_lookup_values.lookup_code%type ;
18 l_temp number(1) ;
19 l_jsd_code pay_taxability_rules.jurisdiction_code%type;
20
21 -- Cursor to get Classification Id. Assuming that the Script is
22 -- used only for US legislation and taxability and the wage attachment
23 -- rules are defined for the 3 classifications
24
25 cursor c_classification_id is
26 select classification_id
27 from pay_element_classifications pec
28 where pec.classification_name = p_classification_name
29 and pec.legislation_code = 'US'
30 and p_classification_name in
31 ('Supplemental Earnings','Imputed Earnings','Pre-Tax Deductions');
32
33 -- Cursor to get Taxability Rule id based on current date
34
35 cursor c_taxability_rules_date_id is
36 select taxability_rules_date_id
37 from pay_taxability_rules_dates trd
38 where sysdate between trd.valid_date_from
39 and trd.valid_date_to
40 and trd.legislation_code = 'US' ;
41
42 --cursor to get Taxability Rules
43
44 cursor c_pay_taxability_rules is
45 select jurisdiction_code,
46 tax_type,
47 status
48 from pay_taxability_rules ptr
49 where ptr.classification_id = l_classification_id
50 and ptr.legislation_code = 'US'
51 and ptr.tax_category = p_source_category
52 and (ptr.jurisdiction_code like p_source_state_code||'%' or
53 p_source_state_code is null) ;
54
55
56 BEGIN
57
58 -- Get Classification Id.
59
60 open c_classification_id ;
61 fetch c_classification_id into l_classification_id ;
62 close c_classification_id ;
63
64
65 -- Based on the Classification fix the Lookup Types.
66
67 if p_classification_name = 'Supplemental Earnings' then
68 l_lookup_type := 'US_SUPPLEMENTAL_EARNINGS' ;
69 elsif p_classification_name = 'Imputed Earnings' then
70 l_lookup_type := 'US_IMPUTED_EARNINGS' ;
71 elsif p_classification_name = 'Pre-Tax Deductions' then
72 l_lookup_type := 'US_PRE_TAX_DEDUCTIONS' ;
73 end if;
74
75
76 -- Get Taxability Rule id
77
78 open c_taxability_rules_date_id ;
79 fetch c_taxability_rules_date_id into l_tax_rules_date_id ;
80 close c_taxability_rules_date_id ;
81
82 -- If both source category and target category, source state
83 -- and target state are choosen same then there should not be any effect.
84
85 if p_source_category = p_target_category and
86 nvl(p_source_state_code,'XX') = nvl(p_target_state_code,'XX') then
87
88 null ;
89
90 else
91
92 -- Delete the Taxability Rules if any for Target Category Code.
93
94 Delete pay_taxability_rules
95 where classification_id = l_classification_id
96 and legislation_code = 'US'
97 and tax_category = p_target_category
98 and jurisdiction_code like p_target_state_code||'%' ;
99
100 --Get Taxability Rules
101
102 for tax_rul in c_pay_taxability_rules loop
103
104 -- If the rules are to be copied from one state to other then
105 -- the jurisdiction code can be changed accordingly.
106
107 if p_target_state_code is null then
108 l_jsd_code := tax_rul.jurisdiction_code ;
109 else
110 l_jsd_code := p_target_state_code||substr(tax_rul.jurisdiction_code,3,9);
111 end if;
112
113 insert into pay_taxability_rules (jurisdiction_code,
114 tax_type,
115 tax_category,
116 classification_id,
117 taxability_rules_date_id,
118 legislation_code,
119 last_update_date,
120 last_updated_by,
121 last_update_login,
122 created_by,
123 creation_date,
124 status )
125 values ( l_jsd_code,
126 tax_rul.tax_type,
127 p_target_category,
128 l_classification_id,
129 l_tax_rules_date_id,
130 'US',
131 sysdate,
132 -1,
133 -1,
134 -1,
135 sysdate,
136 tax_rul.status );
137
138 end loop;
139 end if;
140
141 EXCEPTION
142 WHEN OTHERS THEN
143
144 errbuf := SQLERRM;
145 retcode := SQLCODE;
146
147 raise;
148
149
150 END COPY_TAX_RULES ;
151
152 END PQP_CPYTAXRUL;
[Home] [Help]