[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_TAX_RULES_GARN_PKG
Source
1 PACKAGE BODY pay_ca_tax_rules_garn_pkg as
2 /* $Header: pycadiat.pkb 120.1 2005/10/05 22:01:39 saurgupt 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_legislation_code VARCHAR2,
10 X_taxability_rules_date_id out nocopy NUMBER,
11 X_valid_date_from in out nocopy DATE,
12 X_valid_date_to in out nocopy DATE,
13 X_session_date DATE,
14 X_BOX1 IN OUT nocopy VARCHAR2,
15 X_BOX2 IN OUT nocopy VARCHAR2) IS
16 -- Local Variables
17 P_ret VARCHAR2(1) := 'N';
18 P_User_Id Number := FND_PROFILE.Value('USER_ID');
19 P_login_id Number := FND_PROFILE.Value('LOGIN_ID');
20 P_i Number := 0;
21 l_jurisdiction VARCHAR2(11);
22
23 procedure get_date_info(P_legislation_code VARCHAR2,
24 P_taxability_rules_date_id out nocopy number,
25 P_valid_date_from out nocopy date,
26 P_valid_date_to out nocopy date,
27 P_date DATE default sysdate) is
28 CURSOR csr_get_info is
29 select TRD.TAXABILITY_RULES_DATE_ID,
30 TRD.VALID_DATE_FROM, TRD.VALID_DATE_TO
31 from PAY_TAXABILITY_RULES_DATES TRD
32 where p_date between TRD.VALID_DATE_FROM and
33 TRD.VALID_DATE_TO
34 and TRD.LEGISLATION_CODE = p_legislation_code;
35 begin
36 OPEN csr_get_info;
37 FETCH csr_get_info INTO P_taxability_rules_date_id, P_valid_date_from,
38 P_valid_date_to;
39 CLOSE csr_get_info;
40 --
41 END get_date_info;
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
61 begin
62 OPEN csr_check;
63 FETCH csr_check INTO ret;
64 IF csr_check%NOTFOUND then
65 ret := 'N';
66 else
67 ret := 'Y';
68 end if;
69 CLOSE csr_check;
70
71 --
72 RETURN ret;
73 --
74 END check_row_exist;
75 PROCEDURE 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 P_legislation_code VARCHAR2) IS
81 --
82 begin
83 INSERT INTO pay_taxability_rules(
84 JURISDICTION_CODE,
85 TAX_TYPE,
86 TAX_CATEGORY,
87 CLASSIFICATION_ID,
88 TAXABILITY_RULES_DATE_ID,
89 LEGISLATION_CODE,
90 LAST_UPDATE_DATE,
91 LAST_UPDATED_BY,
92 LAST_UPDATE_LOGIN,
93 CREATED_BY,
94 CREATION_DATE)
95 VALUES (
96 P_jurisdiction,
97 P_tax_type,
98 P_category,
99 P_classification_id,
100 P_taxability_rules_date_id,
101 P_legislation_code,
102 SYSDATE,
103 P_user_id,
104 P_Login_Id,
105 P_user_id,
106 SYSDATE);
107 IF SQL%NOTFOUND then
108 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
109 hr_utility.set_message_token('PROCEDURE','pay_us_taxability_rules_pkg.insert');
110 hr_utility.set_message_token('STEP','1');
111 hr_utility.raise_error;
112 end if;
113 --
114 END insert_rules;
115 --
116 PROCEDURE delete_rules(P_jurisdiction VARCHAR2,
117 P_tax_type VARCHAR2,
118 P_category VARCHAR2,
119 P_classification_id NUMBER,
120 P_taxability_rules_date_id NUMBER,
121 P_legislation_code VARCHAR2) IS
122 --
123 begin
124 delete from pay_taxability_rules
125 where jurisdiction_code = P_jurisdiction
126 and tax_type = P_tax_type
127 and tax_category = P_category
128 and classification_id = p_classification_id
129 and taxability_rules_date_id = P_taxability_rules_date_id
130 and legislation_code = P_legislation_code;
131
132 IF SQL%NOTFOUND then
133 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
134 hr_utility.set_message_token('PROCEDURE','pay_us_taxability_rules_pkg.delete');
135 hr_utility.set_message_token('STEP','1');
136 hr_utility.raise_error;
137 end if;
138 --
139 END delete_rules;
140 --
141 --
142 --
143 -- MAIN PROCEDURE
144 begin
145 l_jurisdiction := substr(X_jurisdiction,1,2)||'-000-0000';
146
147 IF X_MODE = 'QUERY' then
148 get_date_info(X_legislation_code, X_taxability_rules_date_id,
149 X_valid_date_from, X_valid_date_to, X_session_date);
150 X_BOX1 := check_row_exist(l_jurisdiction,
151 'CSDI',
152 X_tax_cat,
153 X_classification_id,
154 X_taxability_rules_date_id);
155 X_BOX2 := check_row_exist(l_jurisdiction,
156 'GDI',
157 X_tax_cat,
158 X_classification_id,
159 X_taxability_rules_date_id);
160 --
161 elsif X_MODE = 'UPDATE' then
162 P_User_Id := FND_PROFILE.Value('USER_ID');
163 P_Login_Id := FND_PROFILE.Value('LOGIN_ID');
164 if X_taxability_rules_date_id IS NULL then
165 select taxability_rules_date_id
166 into X_taxability_rules_date_id
167 from pay_taxability_rules_dates
168 where X_session_date between valid_date_from and valid_date_to
169 and legislation_code = X_legislation_code;
170 end if;
171
172 P_ret := check_row_exist(l_jurisdiction,
173 'CSDI',
174 X_tax_cat,
175 X_classification_id,
176 X_taxability_rules_date_id);
177 if P_ret = X_box1 then
178 null;
179 elsif P_ret = 'Y' and X_box1 = 'N' then
180 delete_rules(l_jurisdiction,'CSDI',X_tax_cat, X_classification_id,
181 X_taxability_rules_date_id, X_legislation_code);
182 elsif P_ret = 'N' and X_box1 = 'Y' then
183 insert_rules(l_jurisdiction,'CSDI',X_tax_cat, X_classification_id,
184 X_taxability_rules_date_id, X_legislation_code);
185 end if;
186 --
187 P_ret := check_row_exist(l_jurisdiction,
188 'GDI',
189 X_tax_cat,
190 X_classification_id,
191 X_taxability_rules_date_id);
192 if P_ret = X_box2 then
193 null;
194 elsif P_ret = 'Y' and X_box2 = 'N' then
195 delete_rules(l_jurisdiction,'GDI',X_tax_cat, X_classification_id,
196 X_taxability_rules_date_id, X_legislation_code);
197 elsif P_ret = 'N' and X_box2 = 'Y' then
198 insert_rules(l_jurisdiction,'GDI',X_tax_cat, X_classification_id,
199 X_taxability_rules_date_id, X_legislation_code);
200 end if;
201 --
202 --
203 end if;
204 --
205 END get_or_update;
206 --
207 --
208 -- Function to get the classification id.
209 --
210 FUNCTION get_classification_id (p_classification_name VARCHAR2,
211 p_legislation_code VARCHAR2) RETURN NUMBER IS
212 --
213 -- declare cursor
214 --
215 CURSOR get_class_id IS
216 SELECT classification_id
217 FROM pay_element_classifications
218 WHERE classification_name = p_classification_name
219 AND legislation_code = p_legislation_code;
220 --
221 l_classification_id NUMBER(9);
222 --
223 BEGIN
224 --
225 OPEN get_class_id;
226 FETCH get_class_id INTO l_classification_id;
227 CLOSE get_class_id;
228 --
229 RETURN l_classification_id;
230 --
231 END get_classification_id;
232 --
233 END pay_ca_tax_rules_garn_pkg;