[Home] [Help]
PACKAGE BODY: APPS.PAY_US_TAXABILITY_RULES_PKG_F
Source
1 PACKAGE BODY pay_us_taxability_rules_pkg_f as
2 /* $Header: pydia01t.pkb 120.0 2005/05/29 04:18:58 appldev noship $ */
3 --
4 PROCEDURE get_or_update(X_MODE VARCHAR2,
5 X_CONTEXT VARCHAR2,
6 X_JURISDICTION VARCHAR2,
7 X_TAX_CAT VARCHAR2,
8 X_classification_id NUMBER,
9 X_BOX1 IN OUT NOCOPY VARCHAR2,
10 X_BOX2 IN OUT NOCOPY VARCHAR2,
11 X_BOX3 IN OUT NOCOPY VARCHAR2) IS
12 -- Local Variables
13 P_ret VARCHAR2(1) := 'N';
14 P_User_Id Number := FND_PROFILE.Value('USER_ID');
15 P_login_id Number := FND_PROFILE.Value('LOGIN_ID');
16 P_i Number := 0;
17 p_taxability_rules_date_id number;
18 p_valid_date_from date;
19 p_valid_date_to date;
20 p_legislation_code VARCHAR2(2) := 'US';
21
22 procedure get_date_info(P_legislation_code VARCHAR2 default 'US',
23 P_taxability_rules_date_id out nocopy number,
24 P_valid_date_from out nocopy date,
25 P_valid_date_to out nocopy date,
26 P_date DATE default sysdate) is
27 CURSOR csr_get_info is
28 select TRD.TAXABILITY_RULES_DATE_ID,
29 TRD.VALID_DATE_FROM, TRD.VALID_DATE_TO
30 from PAY_TAXABILITY_RULES_DATES TRD
31 where p_date between TRD.VALID_DATE_FROM and
32 TRD.VALID_DATE_TO
33 and TRD.LEGISLATION_CODE = p_legislation_code;
34 begin
35 OPEN csr_get_info;
36 FETCH csr_get_info INTO P_taxability_rules_date_id, P_valid_date_from,
37 P_valid_date_to;
38 CLOSE csr_get_info;
39 --
40 END get_date_info;
41
42
43 FUNCTION check_row_exist(P_jurisdiction VARCHAR2,
44 P_tax_type VARCHAR2,
45 P_category VARCHAR2,
46 P_classification_id NUMBER,
47 P_taxability_rules_date_id NUMBER)
48 RETURN VARCHAR2 is
49 --
50 ret VARCHAR2(1) := 'N';
51 --
52 CURSOR csr_check is
53 select 'Y'
54 from PAY_TAXABILITY_RULES
55 where JURISDICTION_CODE = P_jurisdiction
56 and TAX_TYPE = P_tax_type
57 and TAX_CATEGORY = P_category
58 and CLASSIFICATION_ID = p_classification_id
59 and TAXABILITY_RULES_DATE_ID = p_taxability_rules_date_id
60 and LEGISLATION_CODE = 'US'
61 and nvl(STATUS,'VALID') <> 'D';
62 begin
63 OPEN csr_check;
64 FETCH csr_check INTO ret;
65 IF csr_check%NOTFOUND then
66 ret := 'N';
67 else
68 ret := 'Y';
69 end if;
70 CLOSE csr_check;
71 --
72 RETURN ret;
73 --
74 END check_row_exist;
75 FUNCTION insert_rules(P_jurisdiction VARCHAR2,
76 P_tax_type VARCHAR2,
77 P_category VARCHAR2,
78 P_classification_id NUMBER,
79 P_taxability_rules_date_id NUMBER)
80 RETURN NUMBER IS
81 --
82 ret number := 0;
83 begin
84
85 update pay_taxability_rules
86 set status = null
87 where jurisdiction_code = P_jurisdiction
88 and tax_type = P_tax_type
89 and tax_category = P_category
90 and classification_id = p_classification_id
91 and taxability_rules_date_id = P_taxability_rules_date_id;
92
93 if sql%notfound then
94
95 INSERT INTO pay_taxability_rules(
96 JURISDICTION_CODE,
97 TAX_TYPE,
98 TAX_CATEGORY,
99 classification_id,
100 TAXABILITY_RULES_DATE_ID,
101 LEGISLATION_CODE,
102 LAST_UPDATE_DATE,
103 LAST_UPDATED_BY,
104 LAST_UPDATE_LOGIN,
105 CREATED_BY,
106 CREATION_DATE)
107 VALUES (
108 P_jurisdiction,
109 P_tax_type,
110 P_category,
111 P_classification_id,
112 P_taxability_rules_date_id,
113 'US',
114 SYSDATE,
115 P_user_id,
116 P_Login_Id,
117 P_user_id,
118 SYSDATE);
119 IF SQL%NOTFOUND then
120 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
121 hr_utility.set_message_token('PROCEDURE','pay_us_taxability_rules_pkg.insert');
122 hr_utility.set_message_token('STEP','1');
123 hr_utility.raise_error;
124 end if;
125 end if;
126 --
127 RETURN ret;
128 --
129 END insert_rules;
130 --
131 FUNCTION delete_rules(P_jurisdiction VARCHAR2,
132 P_tax_type VARCHAR2,
133 P_category VARCHAR2,
134 p_classification_id NUMBER,
135 p_taxability_rules_date_id NUMBER)
136 RETURN NUMBER IS
137 --
138 ret number := 0;
139 begin
140 update pay_taxability_rules
141 set status = 'D'
142 where jurisdiction_code = P_jurisdiction
143 and tax_type = P_tax_type
144 and tax_category = P_category
145 and classification_id = p_classification_id
146 and taxability_rules_date_id = P_taxability_rules_date_id;
147
148 IF SQL%NOTFOUND then
149 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
150 hr_utility.set_message_token('PROCEDURE','pay_us_taxability_rules_pkg.delete');
151 hr_utility.set_message_token('STEP','1');
152 hr_utility.raise_error;
153 end if;
154 --
155 RETURN ret;
156 --
157 END delete_rules;
158 --
159 --
160 -- This function checks whether another row for a given tax type,
161 -- jursidiction and classification id exist. This is primarily for tax type
162 -- of WC - as only one row can exist at any time for category of OT or S
163 -- however, if in the future for any reason this rules is extended to other
164 -- tax types this functin is stil applicable.
165 --
166 -- RETURN TRUE if rules are not mutually exclusive
167 --
168 FUNCTION chk_mutually_exclusive (p_jurisdiction_code VARCHAR2,
169 p_tax_category VARCHAR2,
170 p_tax_type VARCHAR2,
171 p_classification_id NUMBER )
172 RETURN BOOLEAN IS
173 --
174 -- declare local cursor
175 --
176 CURSOR get_other_rule IS
177 SELECT 'Y'
178 FROM pay_taxability_rules
179 WHERE jurisdiction_code = p_jurisdiction_code
180 AND tax_type = p_tax_type
181 AND classification_id = p_classification_id
182 AND tax_category <> p_tax_category
183 AND legislation_code = 'US';
184 --
185 l_exists VARCHAR2(1) := 'N';
186 --
187 BEGIN
188 --
189 OPEN get_other_rule;
190 FETCH get_other_rule INTO l_exists;
191 CLOSE get_other_rule;
192 --
193 IF l_exists = 'N'
194 THEN
195 RETURN FALSE;
196 ELSE
197 RETURN TRUE;
198 END IF;
199 --
200 END chk_mutually_exclusive;
201 --
202 --
203 -- MAIN PROCEDURE
204 begin
205 get_date_info(P_legislation_code ,
206 P_taxability_rules_date_id,
207 P_valid_date_from,
208 P_valid_date_to);
209
210 IF X_MODE = 'QUERY' then
211
212
213 X_BOX1 := check_row_exist(X_jurisdiction,
214 'CSDI',
215 X_tax_cat,
216 X_classification_id,
217 p_taxability_rules_date_id);
218 X_BOX2 := check_row_exist(X_jurisdiction,
219 'GDI',
220 X_tax_cat,
221 X_classification_id,
222 p_taxability_rules_date_id);
223 X_BOX3 := check_row_exist(X_jurisdiction,
224 'DCIA',
225 X_tax_cat,
226 X_classification_id,
227 p_taxability_rules_date_id);
228 --
229 elsif X_MODE = 'UPDATE' then
230 P_User_Id := FND_PROFILE.Value('USER_ID');
231 P_Login_Id := FND_PROFILE.Value('LOGIN_ID');
232
233 SELECT taxability_rules_date_id
234 INTO p_taxability_rules_date_id
235 FROM pay_taxability_rules_dates
236 WHERE sysdate between valid_date_from and valid_date_to
237 AND legislation_code = p_legislation_code;
238
239 P_ret := check_row_exist(X_jurisdiction,
240 'CSDI',
241 X_tax_cat,
242 X_classification_id,
243 p_taxability_rules_date_id);
244 if P_ret = X_box1 then
245 null;
246 elsif P_ret = 'Y' and X_box1 = 'N' then
247 P_i := delete_rules(X_jurisdiction,'CSDI',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
248 elsif P_ret = 'N' and X_box1 = 'Y' then
249 P_i := insert_rules(X_jurisdiction,'CSDI',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
250 end if;
251 --
252 P_ret := check_row_exist(X_jurisdiction,
253 'GDI',
254 X_tax_cat,
255 X_classification_id,
256 p_taxability_rules_date_id);
257 if P_ret = X_box2 then
258 null;
259 elsif P_ret = 'Y' and X_box2 = 'N' then
260 P_i := delete_rules(X_jurisdiction,'GDI',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
261 elsif P_ret = 'N' and X_box2 = 'Y' then
262 P_i := insert_rules(X_jurisdiction,'GDI',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
263 end if;
264
265 P_ret := check_row_exist(X_jurisdiction,
266 'DCIA',
267 X_tax_cat,
268 X_classification_id,
269 p_taxability_rules_date_id);
270 if P_ret = X_box3 then
271 null;
272 elsif P_ret = 'Y' and X_box3 = 'N' then
273 P_i := delete_rules(X_jurisdiction,'DCIA',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
274 elsif P_ret = 'N' and X_box3 = 'Y' then
275 P_i := insert_rules(X_jurisdiction,'DCIA',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
276 end if;
277
278 --
279 --
280 end if;
281 --
282 END get_or_update;
283 --
284 --
285 -- function to get the classification id fro 'Earnings'.
286 -- This is the default classification id for the WC OT category
287 -- tax rules from the WC form (DJC).
288 --
289 FUNCTION get_classification_id (p_classification_name VARCHAR2) RETURN NUMBER IS
290 --
291 -- declare cursor
292 --
293 CURSOR get_class_id IS
294 SELECT pec.classification_id
295 FROM pay_element_classifications pec
296 WHERE pec.classification_name = p_classification_name
297 AND pec.legislation_code = 'US';
298 --
299 l_classification_id NUMBER(9);
300 --
301 BEGIN
302 --
303 OPEN get_class_id;
304 FETCH get_class_id INTO l_classification_id;
305 CLOSE get_class_id;
306 --
307 RETURN l_classification_id;
308 --
309 END get_classification_id;
310 --
311 END pay_us_taxability_rules_pkg_f;