[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_TAXABILITY_RULES_PKG
Source
1 PACKAGE BODY pay_ca_taxability_rules_pkg as
2 /* $Header: paycaetw.pkb 120.1 2006/02/21 11:42:26 ssouresr noship $ */
3 --
4 --
5 /*
6 /*
7 ******************************************************************
8 * *
9 * Copyright (C) 1993 Oracle Corporation. *
10 * All rights reserved. *
11 * *
12 * This material has been provided pursuant to an agreement *
13 * containing restrictions on its use. The material is also *
14 * protected by copyright law. No part of this material may *
15 * be copied or distributed, transmitted or transcribed, in *
16 * any form or by any means, electronic, mechanical, magnetic, *
17 * manual, or otherwise, or disclosed to third parties without *
18 * the express written permission of Oracle Corporation, *
19 * 500 Oracle Parkway, Redwood City, CA, 94065. *
20 * *
21 ******************************************************************
22
23
24 Name : pay_ca_taxability_rules_pkg
25
26 Description : This package holds building blocks used in maintenace
27 of Canadian taxability rules using PAY_TAXABILITY_RULES
28 table.
29
30 Uses : hr_utility
31
32 Change List
33 -----------
34 Date Name Vers Bug No Description
35 ---- ---- ---- ------ -----------
36 8/15/98 RAMURTHY 110.0 Created.
37
38 05-NOV-05 SSOURESR 115.2 Added tax type PPIP
39 */
40 --
41
42 PROCEDURE get_or_update(X_MODE VARCHAR2,
43 X_CONTEXT VARCHAR2,
44 X_JURISDICTION VARCHAR2,
45 X_TAX_CAT VARCHAR2,
46 X_classification_id NUMBER,
47 X_legislation_code VARCHAR2,
48 X_taxability_rules_date_id out nocopy NUMBER,
49 X_valid_date_from in out nocopy DATE,
50 X_valid_date_to in out nocopy DATE,
51 X_session_date DATE,
52 X_BOX1 IN OUT nocopy VARCHAR2,
53 X_BOX2 IN OUT nocopy VARCHAR2,
54 X_BOX3 IN OUT nocopy VARCHAR2,
55 X_BOX4 IN OUT nocopy VARCHAR2,
56 X_BOX5 IN OUT nocopy VARCHAR2,
57 X_BOX6 IN OUT nocopy VARCHAR2,
58 X_BOX7 IN OUT nocopy VARCHAR2,
59 X_BOX8 IN OUT nocopy VARCHAR2,
60 X_BOX9 IN OUT nocopy VARCHAR2,
61 X_BOX10 IN OUT nocopy VARCHAR2) IS
62
63 TYPE character_data_table IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
64
65 -- Local Variables
66 P_ret VARCHAR2(1) := 'N';
67 P_User_Id Number := FND_PROFILE.Value('USER_ID');
68 P_login_id Number := FND_PROFILE.Value('LOGIN_ID');
69 p_comp VARCHAR2(1) := 'N';
70 l_fed_tax_type character_data_table;
71 l_qbc_tax_type character_data_table;
72 l_prv_tax_type character_data_table;
73
74 procedure get_date_info(P_legislation_code VARCHAR2 default 'CA',
75 P_taxability_rules_date_id out nocopy number,
76 P_valid_date_from out nocopy date,
77 P_valid_date_to out nocopy date,
78 P_date DATE default sysdate) is
79 CURSOR csr_get_info is
80 select TRD.TAXABILITY_RULES_DATE_ID,
81 TRD.VALID_DATE_FROM, TRD.VALID_DATE_TO
82 from PAY_TAXABILITY_RULES_DATES TRD
83 where p_date between TRD.VALID_DATE_FROM and
84 TRD.VALID_DATE_TO
85 and TRD.LEGISLATION_CODE = p_legislation_code;
86 begin
87 OPEN csr_get_info;
88 FETCH csr_get_info INTO P_taxability_rules_date_id, P_valid_date_from,
89 P_valid_date_to;
90 CLOSE csr_get_info;
91 --
92 END get_date_info;
93
94 FUNCTION check_row_exist(P_jurisdiction VARCHAR2,
95 P_tax_type VARCHAR2,
96 P_category VARCHAR2,
97 P_classification_id NUMBER,
98 P_taxability_rules_date_id number)
99 RETURN VARCHAR2 is
100 --
101 ret VARCHAR2(1) := 'N';
102 --
103 CURSOR csr_check is
104 select 'Y'
105 from PAY_TAXABILITY_RULES PTR
106 where PTR.JURISDICTION_CODE = P_jurisdiction
107 and PTR.TAX_TYPE = P_tax_type
108 and PTR.TAX_CATEGORY = P_category
109 and PTR.CLASSIFICATION_ID = p_classification_id
110 and PTR.TAXABILITY_RULES_DATE_ID = p_taxability_rules_date_id;
111 begin
112 OPEN csr_check;
113 FETCH csr_check INTO ret;
114 IF csr_check%NOTFOUND then
115 ret := 'N';
116 else
117 ret := 'Y';
118 end if;
119 CLOSE csr_check;
120
121 --
122 RETURN ret;
123 --
124 END check_row_exist;
125
126 PROCEDURE insert_rules(P_jurisdiction VARCHAR2,
127 P_tax_type VARCHAR2,
128 P_category VARCHAR2,
129 P_classification_id NUMBER,
130 P_taxability_rules_date_id NUMBER) IS
131 --
132 begin
133
134 INSERT INTO pay_taxability_rules(
135 JURISDICTION_CODE,
136 TAX_TYPE,
137 TAX_CATEGORY,
138 classification_id,
139 TAXABILITY_RULES_DATE_ID,
140 LEGISLATION_CODE,
141 LAST_UPDATE_DATE,
142 LAST_UPDATED_BY,
143 LAST_UPDATE_LOGIN,
144 CREATED_BY,
145 CREATION_DATE)
146 VALUES (
147 P_jurisdiction,
148 P_tax_type,
149 P_category,
150 P_classification_id,
151 P_taxability_rules_date_id,
152 'CA',
153 SYSDATE,
154 P_user_id,
155 P_Login_Id,
156 P_user_id,
157 SYSDATE);
158 IF SQL%NOTFOUND then
159 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
160 hr_utility.set_message_token('PROCEDURE','pay_us_taxability_rules_pkg.insert');
161 hr_utility.set_message_token('STEP','1');
162 hr_utility.raise_error;
163 end if;
164 --
165 END insert_rules;
166 --
167
168 PROCEDURE delete_rules(P_jurisdiction VARCHAR2,
169 P_tax_type VARCHAR2,
170 P_category VARCHAR2,
171 p_classification_id NUMBER,
172 P_taxability_rules_date_id NUMBER) IS
173 --
174 begin
175 delete from pay_taxability_rules
176 where jurisdiction_code = P_jurisdiction
177 and tax_type = P_tax_type
178 and tax_category = P_category
179 and classification_id = p_classification_id
180 and taxability_rules_date_id = P_taxability_rules_date_id;
181 IF SQL%NOTFOUND then
182 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
183 hr_utility.set_message_token('PROCEDURE','pay_us_taxability_rules_pkg.delete');
184 hr_utility.set_message_token('STEP','1');
185 hr_utility.raise_error;
186 end if;
187 --
188 --
189 END delete_rules;
190 --
191 --
192 -- MAIN PROCEDURE
193 begin
194 -- Set up the arrays with the tax types.
195
196 l_fed_tax_type(1) := 'FED';
197 l_fed_tax_type(2) := 'CPP';
198 l_fed_tax_type(3) := 'EIM';
199
200 l_prv_tax_type(1) := 'PRV';
201 l_prv_tax_type(2) := 'PPT';
202 l_prv_tax_type(3) := 'RSTI';
203 l_prv_tax_type(4) := 'PST';
204 l_prv_tax_type(5) := 'GST';
205 l_prv_tax_type(6) := 'HST';
206 l_prv_tax_type(7) := 'PMED';
207 l_prv_tax_type(8) := 'VAC';
208 l_prv_tax_type(9) := 'WCB';
209
210 l_qbc_tax_type(1) := 'PRV';
211 l_qbc_tax_type(2) := 'PPT';
212 l_qbc_tax_type(3) := 'RSTI';
213 l_qbc_tax_type(4) := 'GST';
214 l_qbc_tax_type(5) := 'QST';
215 l_qbc_tax_type(6) := 'QPP';
216 l_qbc_tax_type(7) := 'PMED';
217 l_qbc_tax_type(8) := 'VAC';
218 l_qbc_tax_type(9) := 'WCB';
219 l_qbc_tax_type(10):= 'PPIP';
220
221 IF X_MODE = 'QUERY' then
222 get_date_info(X_legislation_code, X_taxability_rules_date_id,
223 X_valid_date_from, X_valid_date_to, X_session_date);
224 if X_CONTEXT = 'FEDERAL' then
225 X_BOX1 := check_row_exist(X_jurisdiction,
226 'FED',
227 X_tax_cat,
228 X_classification_id,
229 X_taxability_rules_date_id);
230 X_BOX2 := check_row_exist(X_jurisdiction,
231 'CPP',
232 X_tax_cat,
233 X_classification_id,
234 X_taxability_rules_date_id);
235 X_BOX3 := check_row_exist(X_jurisdiction,
236 'EIM',
237 X_tax_cat,
238 X_classification_id,
239 X_taxability_rules_date_id);
240 elsif X_CONTEXT = 'PROVINCE' then
241 X_BOX1 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
242 'PRV',
243 X_tax_cat,
244 X_classification_id,
245 X_taxability_rules_date_id);
246 X_BOX2 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
247 'PPT',
248 X_tax_cat,
249 X_classification_id,
250 X_taxability_rules_date_id);
251 X_BOX3 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
252 'RSTI',
253 X_tax_cat,
254 X_classification_id,
255 X_taxability_rules_date_id);
256 X_BOX4 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
257 'PST',
258 X_tax_cat,
259 X_classification_id,
260 X_taxability_rules_date_id);
261 X_BOX5 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
262 'GST',
263 X_tax_cat,
264 X_classification_id,
265 X_taxability_rules_date_id);
266 X_BOX6 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
267 'HST',
268 X_tax_cat,
269 X_classification_id,
270 X_taxability_rules_date_id);
271 X_BOX7 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
272 'PMED',
273 X_tax_cat,
274 X_classification_id,
275 X_taxability_rules_date_id);
276 X_BOX8 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
277 'VAC',
278 X_tax_cat,
279 X_classification_id,
280 X_taxability_rules_date_id);
281 X_BOX9 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
282 'WCB',
283 X_tax_cat,
284 X_classification_id,
285 X_taxability_rules_date_id);
286 elsif X_CONTEXT = 'QUEBEC' then
287 X_BOX1 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
288 'PRV',
289 X_tax_cat,
290 X_classification_id,
291 X_taxability_rules_date_id);
292 X_BOX2 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
293 'PPT',
294 X_tax_cat,
295 X_classification_id,
296 X_taxability_rules_date_id);
297 X_BOX3 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
298 'RSTI',
299 X_tax_cat,
300 X_classification_id,
301 X_taxability_rules_date_id);
302 X_BOX4 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
303 'GST',
304 X_tax_cat,
305 X_classification_id,
306 X_taxability_rules_date_id);
307 X_BOX5 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
308 'QST',
309 X_tax_cat,
310 X_classification_id,
311 X_taxability_rules_date_id);
312 X_BOX6 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
313 'QPP',
314 X_tax_cat,
315 X_classification_id,
316 X_taxability_rules_date_id);
317 X_BOX7 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
318 'PMED',
319 X_tax_cat,
320 X_classification_id,
321 X_taxability_rules_date_id);
322 X_BOX8 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
323 'VAC',
324 X_tax_cat,
325 X_classification_id,
326 X_taxability_rules_date_id);
327 X_BOX9 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
328 'WCB',
329 X_tax_cat,
330 X_classification_id,
331 X_taxability_rules_date_id);
332 X_BOX10 := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
333 'PPIP',
334 X_tax_cat,
335 X_classification_id,
336 X_taxability_rules_date_id);
337 end if;
338 --
339 elsif X_MODE = 'UPDATE' then
340 P_User_Id := FND_PROFILE.Value('USER_ID');
341 P_Login_Id := FND_PROFILE.Value('LOGIN_ID');
342 if X_taxability_rules_date_id IS NULL then
343 select taxability_rules_date_id
344 into X_taxability_rules_date_id
345 from pay_taxability_rules_dates
346 where X_session_date between valid_date_from and valid_date_to
347 and legislation_code = X_legislation_code;
348 end if;
349
350 if X_CONTEXT = 'FEDERAL' then
351 for i in 1..3 loop
352 P_ret := check_row_exist(X_jurisdiction,
353 l_fed_tax_type(i),
354 X_tax_cat,
355 X_classification_id,
356 X_taxability_rules_date_id);
357 if i = 1 then
358 p_comp := X_box1;
359 elsif i = 2 then
360 p_comp := X_box2;
361 else
362 p_comp := X_box3;
363 end if;
364
365 if P_ret = p_comp then
366 null;
367 elsif P_ret = 'Y' and p_comp = 'N' then
368 delete_rules(X_jurisdiction, l_fed_tax_type(i), X_tax_cat,
369 X_classification_id, X_taxability_rules_date_id);
370 elsif P_ret = 'N' and p_comp = 'Y' then
371 insert_rules(X_jurisdiction,l_fed_tax_type(i), X_tax_cat,
372 X_classification_id, X_taxability_rules_date_id);
373 end if;
374 end loop;
375 --
376 elsif X_CONTEXT = 'PROVINCE' then
377 for i in 1..9 loop
378 P_ret := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
379 l_prv_tax_type(i),
380 X_tax_cat,
381 X_classification_id,
382 X_taxability_rules_date_id);
383 if i = 1 then
384 p_comp := X_box1;
385 elsif i = 2 then
386 p_comp := X_box2;
387 elsif i = 3 then
388 p_comp := X_box3;
389 elsif i = 4 then
390 p_comp := X_box4;
391 elsif i = 5 then
392 p_comp := X_box5;
393 elsif i = 6 then
394 p_comp := X_box6;
395 elsif i = 7 then
396 p_comp := X_box7;
397 elsif i = 8 then
398 p_comp := X_box8;
399 else
400 p_comp := X_box9;
401 end if;
402
403 if P_ret = p_comp then
404 null;
405 elsif P_ret = 'Y' and p_comp = 'N' then
406 delete_rules(substr(X_jurisdiction,1,2)||'-000-0000', l_prv_tax_type(i), X_tax_cat,
407 X_classification_id, X_taxability_rules_date_id);
408 elsif P_ret = 'N' and p_comp = 'Y' then
409 insert_rules(substr(X_jurisdiction,1,2)||'-000-0000',l_prv_tax_type(i), X_tax_cat,
410 X_classification_id, X_taxability_rules_date_id);
411 end if;
412 end loop;
413 elsif X_CONTEXT = 'QUEBEC' then
414 for i in 1..10 loop
415 P_ret := check_row_exist(substr(X_jurisdiction,1,2)||'-000-0000',
416 l_qbc_tax_type(i),
417 X_tax_cat,
418 X_classification_id,
419 X_taxability_rules_date_id);
420 if i = 1 then
421 p_comp := X_box1;
422 elsif i = 2 then
423 p_comp := X_box2;
424 elsif i = 3 then
425 p_comp := X_box3;
426 elsif i = 4 then
427 p_comp := X_box4;
428 elsif i = 5 then
429 p_comp := X_box5;
430 elsif i = 6 then
431 p_comp := X_box6;
432 elsif i = 7 then
433 p_comp := X_box7;
434 elsif i = 8 then
435 p_comp := X_box8;
436 elsif i = 9 then
437 p_comp := X_box9;
438 else
439 p_comp := X_box10;
440 end if;
441
442 if P_ret = p_comp then
443 null;
444 elsif P_ret = 'Y' and p_comp = 'N' then
445 delete_rules(substr(X_jurisdiction,1,2)||'-000-0000', l_qbc_tax_type(i), X_tax_cat,
446 X_classification_id, X_taxability_rules_date_id);
447 elsif P_ret = 'N' and p_comp = 'Y' then
448 insert_rules(substr(X_jurisdiction,1,2)||'-000-0000',l_qbc_tax_type(i), X_tax_cat,
449 X_classification_id, X_taxability_rules_date_id);
450 end if;
451 end loop;
452 --
453 end if;
454 --
455 end if;
456 --
457 END get_or_update;
458 --
459 --
460 --
461 FUNCTION get_classification_id (p_classification_name VARCHAR2) RETURN NUMBER IS
462 --
463 -- declare cursor
464 --
465 CURSOR get_class_id IS
466 SELECT classification_id
467 FROM pay_element_classifications
468 WHERE classification_name = p_classification_name
469 AND legislation_code = 'CA';
470 --
471 l_classification_id NUMBER(9);
472 --
473 BEGIN
474 --
475 OPEN get_class_id;
476 FETCH get_class_id INTO l_classification_id;
477 CLOSE get_class_id;
478 --
479 RETURN l_classification_id;
480 --
481 END get_classification_id;
482 --
483 END pay_ca_taxability_rules_pkg;