[Home] [Help]
PACKAGE BODY: APPS.PAY_RI_AC_TAX_RULE
Source
1 package body pay_ri_ac_tax_rule as
2 /* $Header: payacemptaxwrap.pkb 120.0 2005/09/10 03:48:48 psnellin noship $ */
3
4 l_pay_us_fedtax_rule_rec pay_us_emp_fed_tax_rules_f%ROWTYPE;
5 l_pay_us_statetax_rule_rec PAY_US_EMP_STATE_TAX_RULES_F%ROWTYPE;
6 l_pay_us_countytax_rule_rec PAY_US_EMP_COUNTY_TAX_RULES_F%ROWTYPE;
7 l_pay_us_citytax_rule_rec PAY_US_EMP_CITY_TAX_RULES_F%ROWTYPE;
8
9 l_pay_ca_fedtax_rule_rec PAY_CA_EMP_FED_TAX_INFO_F%ROWTYPE;
10 l_pay_ca_provtax_rule_rec PAY_CA_EMP_PROV_TAX_INFO_F%ROWTYPE;
11 g_employee varchar2(250);
12 g_assignment varchar2(20);
13 g_link_value number(15);
14 g_user_sequence number(15);
15
16 -- =============================================================================
17 -- HR_DataPump:
18 --
19 -- NOTE : p_data_pump_batch_line_id is used as link_value_id in the procedure
20 -- as in future we may have to have it as batch_line_ids concatenated
21 -- string
22 -- =============================================================================
23 PROCEDURE HR_DataPump
24 (p_dp_mode IN Varchar2
25 ,p_effective_date IN Date
26 ,p_spreadsheet_identifier IN Varchar2
27 ) AS
28
29 l_datetrack_update_mode Varchar2(50);
30 l_dt_correction Boolean;
31 l_dt_update Boolean;
32 l_dt_upd_override Boolean;
33 l_upd_chg_ins Boolean;
34
35 l_effective_date Date;
36
37 /* Added for wrap pkg datapump */
38 ln_ovn number;
39 ld_eff_start_date date;
40 ld_eff_end_date date;
41 ln_emp_state_tax_rule_id number;
42 ln_emp_county_tax_rule_id number;
43 ln_emp_city_tax_rule_id number;
44 ln_ca_emp_fed_tax_inf_id number;
45 ln_ca_emp_prov_tax_inf_id number;
46
47 begin
48
49 l_effective_date := trunc(p_effective_date);
50 ln_ovn := NULL;
51
52 -- Create a batch line for US Emp Fed Tax Rule Information
53
54 if P_SPREADSHEET_IDENTIFIER = 'PAYRIUSFED' then
55
56 hr_utility.trace('p_dp_mode :'||p_dp_mode);
57
58 -- check for valid datatrack update modes
59 Dt_Api.Find_DT_Upd_Modes
60 (p_effective_date => l_effective_date
61 ,p_base_table_name => 'PAY_US_EMP_FED_TAX_RULES_F'
62 ,p_base_key_column => 'EMP_FED_TAX_RULE_ID'
63 ,p_base_key_value => l_pay_us_fedtax_rule_rec.emp_fed_tax_rule_id
64 ,p_correction => l_dt_correction
65 ,p_update => l_dt_update
66 ,p_update_override => l_dt_upd_override
67 ,p_update_change_insert => l_upd_chg_ins
68 );
69
70
71 IF l_dt_update THEN
72
73 l_datetrack_update_mode := 'UPDATE';
74 hr_utility.trace('l_datetrack_update_mode :'||l_datetrack_update_mode);
75
76 ELSIF l_dt_upd_override OR
77 l_upd_chg_ins THEN
78 -- Cannnot update the record because future dated record found
79 -- NULL;
80 hr_utility.set_message(801, 'HR_7211_DT_UPD_ROWS_IN_FUTURE');
81 hr_utility.raise_error;
82 ELSE
83 l_datetrack_update_mode := 'CORRECTION';
84 hr_utility.trace('l_datetrack_update_mode :'||l_datetrack_update_mode);
85 END IF;
86
87
88 ln_ovn := l_pay_us_fedtax_rule_rec.OBJECT_VERSION_NUMBER;
89
90 pay_federal_tax_rule_api.update_fed_tax_rule
91 (
92 p_effective_date => p_effective_date
93 ,p_datetrack_update_mode => l_datetrack_update_mode
94 ,p_emp_fed_tax_rule_id => l_pay_us_fedtax_rule_rec.emp_fed_tax_rule_id
95 ,p_object_version_number => ln_ovn
96 ,p_sui_state_code => l_pay_us_fedtax_rule_rec.sui_state_code
97 ,p_filing_status_code => l_pay_us_fedtax_rule_rec.FILING_STATUS_CODE
98 ,p_fit_override_amount => l_pay_us_fedtax_rule_rec.FIT_OVERRIDE_AMOUNT
99 ,p_fit_override_rate => l_pay_us_fedtax_rule_rec.FIT_OVERRIDE_RATE
100 ,p_withholding_allowances => l_pay_us_fedtax_rule_rec.WITHHOLDING_ALLOWANCES
101 ,p_cumulative_taxation => l_pay_us_fedtax_rule_rec.CUMULATIVE_TAXATION
102 ,p_eic_filing_status_code => l_pay_us_fedtax_rule_rec.EIC_FILING_STATUS_CODE
103 ,p_fit_additional_tax => l_pay_us_fedtax_rule_rec.FIT_ADDITIONAL_TAX
104 ,p_fit_exempt => l_pay_us_fedtax_rule_rec.FIT_EXEMPT
105 ,p_futa_tax_exempt => l_pay_us_fedtax_rule_rec.FUTA_TAX_EXEMPT
106 ,p_medicare_tax_exempt => l_pay_us_fedtax_rule_rec.MEDICARE_TAX_EXEMPT
107 ,p_ss_tax_exempt => l_pay_us_fedtax_rule_rec.SS_TAX_EXEMPT
108 ,p_statutory_employee => l_pay_us_fedtax_rule_rec.STATUTORY_EMPLOYEE
109 ,p_supp_tax_override_rate => l_pay_us_fedtax_rule_rec.SUPP_TAX_OVERRIDE_RATE
110 ,p_excessive_wa_reject_date => l_pay_us_fedtax_rule_rec.EXCESSIVE_WA_REJECT_DATE
111 ,p_effective_start_date => ld_eff_start_date
112 ,p_effective_end_date => ld_eff_end_date
113 );
114
115 hr_utility.trace('call to hrdpp_UPDATE_FED_TAX_RULE.insert_batch_lines done');
116
117 end if; -- p_spreadsheet_identifier = 'PAYRIUSFED'
118
119
120
121 if P_SPREADSHEET_IDENTIFIER = 'PAYRIUSSTATE' then
122
123 if p_dp_mode = 'UPDATE' then
124
125 hr_utility.trace('p_dp_mode :'||p_dp_mode);
126
127 -- check for valid datatrack update modes
128 Dt_Api.Find_DT_Upd_Modes
129 (p_effective_date => l_effective_date
130 ,p_base_table_name => 'PAY_US_EMP_STATE_TAX_RULES_F'
131 ,p_base_key_column => 'EMP_STATE_TAX_RULE_ID'
132 ,p_base_key_value => l_pay_us_statetax_rule_rec.EMP_STATE_TAX_RULE_ID
133 ,p_correction => l_dt_correction
134 ,p_update => l_dt_update
135 ,p_update_override => l_dt_upd_override
136 ,p_update_change_insert => l_upd_chg_ins
137 );
138
139
140 IF l_dt_update THEN
141
142 l_datetrack_update_mode := 'UPDATE';
143 hr_utility.trace('l_datetrack_update_mode :'||l_datetrack_update_mode);
144
145 ELSIF l_dt_upd_override OR
146 l_upd_chg_ins THEN
147 hr_utility.set_message(801, 'HR_7211_DT_UPD_ROWS_IN_FUTURE');
148 hr_utility.raise_error;
149 ELSE
150 l_datetrack_update_mode := 'CORRECTION';
151 hr_utility.trace('l_datetrack_update_mode :'||l_datetrack_update_mode);
152 END IF;
153
154
155 ln_ovn := l_pay_us_statetax_rule_rec.OBJECT_VERSION_NUMBER;
156
157 hr_utility.trace('Calling PAY_STATE_TAX_RULE_API.UPDATE_STATE_TAX_RULE');
158 PAY_STATE_TAX_RULE_API.UPDATE_STATE_TAX_RULE
159 (P_EFFECTIVE_DATE => l_effective_date
160 ,P_DATETRACK_UPDATE_MODE => l_datetrack_update_mode
161 ,P_EMP_STATE_TAX_RULE_ID => l_pay_us_statetax_rule_rec.EMP_STATE_TAX_RULE_ID
162 ,p_object_version_number => ln_ovn
163 ,P_ADDITIONAL_WA_AMOUNT => l_pay_us_statetax_rule_rec.ADDITIONAL_WA_AMOUNT
164 ,P_FILING_STATUS_CODE => l_pay_us_statetax_rule_rec.FILING_STATUS_CODE
165 ,P_SECONDARY_WA => l_pay_us_statetax_rule_rec.SECONDARY_WA
166 ,P_SIT_ADDITIONAL_TAX => l_pay_us_statetax_rule_rec.SIT_ADDITIONAL_TAX
167 ,P_SIT_OVERRIDE_AMOUNT => l_pay_us_statetax_rule_rec.SIT_OVERRIDE_AMOUNT
168 ,P_SIT_OVERRIDE_RATE => l_pay_us_statetax_rule_rec.SIT_OVERRIDE_RATE
169 ,P_WITHHOLDING_ALLOWANCES => l_pay_us_statetax_rule_rec.WITHHOLDING_ALLOWANCES
170 ,P_EXCESSIVE_WA_REJECT_DATE => l_pay_us_statetax_rule_rec.EXCESSIVE_WA_REJECT_DATE
171 ,P_SDI_EXEMPT => l_pay_us_statetax_rule_rec.SDI_EXEMPT
172 ,P_SIT_EXEMPT => l_pay_us_statetax_rule_rec.SIT_EXEMPT
173 ,P_SIT_OPTIONAL_CALC_IND => l_pay_us_statetax_rule_rec.SIT_OPTIONAL_CALC_IND
174 ,P_STATE_NON_RESIDENT_CERT => l_pay_us_statetax_rule_rec.STATE_NON_RESIDENT_CERT
175 ,P_SUI_EXEMPT => l_pay_us_statetax_rule_rec.SUI_EXEMPT
176 ,P_WC_EXEMPT => l_pay_us_statetax_rule_rec.WC_EXEMPT
177 ,P_SUI_WAGE_BASE_OVERRIDE_AMOUN => l_pay_us_statetax_rule_rec.SUI_WAGE_BASE_OVERRIDE_AMOUNT
178 ,P_SUPP_TAX_OVERRIDE_RATE => l_pay_us_statetax_rule_rec.SUPP_TAX_OVERRIDE_RATE
179 ,P_STA_INFORMATION_CATEGORY => l_pay_us_statetax_rule_rec.STA_INFORMATION_CATEGORY
180 ,P_STA_INFORMATION1 => l_pay_us_statetax_rule_rec.STA_INFORMATION1
181 ,P_STA_INFORMATION2 => l_pay_us_statetax_rule_rec.STA_INFORMATION2
182 ,P_STA_INFORMATION3 => l_pay_us_statetax_rule_rec.STA_INFORMATION3
183 ,P_STA_INFORMATION4 => l_pay_us_statetax_rule_rec.STA_INFORMATION4
184 ,P_STA_INFORMATION5 => l_pay_us_statetax_rule_rec.STA_INFORMATION5
185 ,P_STA_INFORMATION6 => l_pay_us_statetax_rule_rec.STA_INFORMATION6
186 ,P_STA_INFORMATION7 => l_pay_us_statetax_rule_rec.STA_INFORMATION7
187 ,P_STA_INFORMATION8 => l_pay_us_statetax_rule_rec.STA_INFORMATION8
188 ,P_STA_INFORMATION9 => l_pay_us_statetax_rule_rec.STA_INFORMATION9
189 ,P_STA_INFORMATION10 => l_pay_us_statetax_rule_rec.STA_INFORMATION10
190 ,P_STA_INFORMATION11 => l_pay_us_statetax_rule_rec.STA_INFORMATION11
191 ,P_STA_INFORMATION12 => l_pay_us_statetax_rule_rec.STA_INFORMATION12
192 ,P_STA_INFORMATION13 => l_pay_us_statetax_rule_rec.STA_INFORMATION13
193 ,P_STA_INFORMATION14 => l_pay_us_statetax_rule_rec.STA_INFORMATION14
194 ,P_STA_INFORMATION15 => l_pay_us_statetax_rule_rec.STA_INFORMATION15
195 ,P_STA_INFORMATION16 => l_pay_us_statetax_rule_rec.STA_INFORMATION16
196 ,P_STA_INFORMATION17 => l_pay_us_statetax_rule_rec.STA_INFORMATION17
197 ,P_STA_INFORMATION18 => l_pay_us_statetax_rule_rec.STA_INFORMATION18
198 ,P_STA_INFORMATION19 => l_pay_us_statetax_rule_rec.STA_INFORMATION19
199 ,P_STA_INFORMATION20 => l_pay_us_statetax_rule_rec.STA_INFORMATION20
200 ,P_STA_INFORMATION21 => l_pay_us_statetax_rule_rec.STA_INFORMATION21
201 ,P_STA_INFORMATION22 => l_pay_us_statetax_rule_rec.STA_INFORMATION22
202 ,P_STA_INFORMATION23 => l_pay_us_statetax_rule_rec.STA_INFORMATION23
203 ,P_STA_INFORMATION24 => l_pay_us_statetax_rule_rec.STA_INFORMATION24
204 ,P_STA_INFORMATION25 => l_pay_us_statetax_rule_rec.STA_INFORMATION25
205 ,P_STA_INFORMATION26 => l_pay_us_statetax_rule_rec.STA_INFORMATION26
206 ,P_STA_INFORMATION27 => l_pay_us_statetax_rule_rec.STA_INFORMATION27
207 ,P_STA_INFORMATION28 => l_pay_us_statetax_rule_rec.STA_INFORMATION28
208 ,P_STA_INFORMATION29 => l_pay_us_statetax_rule_rec.STA_INFORMATION29
209 ,P_STA_INFORMATION30 => l_pay_us_statetax_rule_rec.STA_INFORMATION30
210 ,p_effective_start_date => ld_eff_start_date
211 ,p_effective_end_date => ld_eff_end_date
212 );
213
214 hr_utility.trace('call to hrdpp_UPDATE_STATE_TAX_RULE.insert_batch_lines done');
215
216 elsif p_dp_mode = 'INSERT' then
217
218 hr_utility.trace('p_dp_mode :'||p_dp_mode);
219 hr_utility.trace('calling PAY_STATE_TAX_RULE_API.CREATE_STATE_TAX_RULE ');
220
221 PAY_STATE_TAX_RULE_API.CREATE_STATE_TAX_RULE
222 (P_EFFECTIVE_DATE => p_effective_date
223 ,P_ASSIGNMENT_ID => l_pay_us_statetax_rule_rec.ASSIGNMENT_ID
224 ,P_STATE_CODE => l_pay_us_statetax_rule_rec.STATE_CODE
225 ,P_ADDITIONAL_WA_AMOUNT => NVL(l_pay_us_statetax_rule_rec.ADDITIONAL_WA_AMOUNT,0)
226 ,P_FILING_STATUS_CODE => l_pay_us_statetax_rule_rec.FILING_STATUS_CODE
227 ,p_remainder_percent => NVL(l_pay_us_statetax_rule_rec.REMAINDER_PERCENT,0)
228 ,P_SECONDARY_WA => NVL(l_pay_us_statetax_rule_rec.SECONDARY_WA,0)
229 ,P_SIT_ADDITIONAL_TAX => NVL(l_pay_us_statetax_rule_rec.SIT_ADDITIONAL_TAX,0)
230 ,P_SIT_OVERRIDE_AMOUNT => NVL(l_pay_us_statetax_rule_rec.SIT_OVERRIDE_AMOUNT,0)
231 ,P_SIT_OVERRIDE_RATE => NVL(l_pay_us_statetax_rule_rec.SIT_OVERRIDE_RATE,0)
232 ,P_WITHHOLDING_ALLOWANCES => NVL(l_pay_us_statetax_rule_rec.WITHHOLDING_ALLOWANCES,0)
233 ,P_EXCESSIVE_WA_REJECT_DATE => l_pay_us_statetax_rule_rec.EXCESSIVE_WA_REJECT_DATE
234 ,P_SDI_EXEMPT => l_pay_us_statetax_rule_rec.SDI_EXEMPT
235 ,P_SIT_EXEMPT => l_pay_us_statetax_rule_rec.SIT_EXEMPT
236 ,P_SIT_OPTIONAL_CALC_IND => l_pay_us_statetax_rule_rec.SIT_OPTIONAL_CALC_IND
237 ,P_STATE_NON_RESIDENT_CERT => l_pay_us_statetax_rule_rec.STATE_NON_RESIDENT_CERT
238 ,P_SUI_EXEMPT => l_pay_us_statetax_rule_rec.SUI_EXEMPT
239 ,P_WC_EXEMPT => l_pay_us_statetax_rule_rec.WC_EXEMPT
240 ,P_SUI_WAGE_BASE_OVERRIDE_AMOUN => l_pay_us_statetax_rule_rec.SUI_WAGE_BASE_OVERRIDE_AMOUNT
241 ,P_SUPP_TAX_OVERRIDE_RATE => NVL(l_pay_us_statetax_rule_rec.SUPP_TAX_OVERRIDE_RATE,0)
242 ,P_STA_INFORMATION_CATEGORY => l_pay_us_statetax_rule_rec.STA_INFORMATION_CATEGORY
243 ,P_STA_INFORMATION1 => l_pay_us_statetax_rule_rec.STA_INFORMATION1
244 ,P_STA_INFORMATION2 => l_pay_us_statetax_rule_rec.STA_INFORMATION2
245 ,P_STA_INFORMATION3 => l_pay_us_statetax_rule_rec.STA_INFORMATION3
246 ,P_STA_INFORMATION4 => l_pay_us_statetax_rule_rec.STA_INFORMATION4
247 ,P_STA_INFORMATION5 => l_pay_us_statetax_rule_rec.STA_INFORMATION5
248 ,P_STA_INFORMATION6 => l_pay_us_statetax_rule_rec.STA_INFORMATION6
249 ,P_STA_INFORMATION7 => l_pay_us_statetax_rule_rec.STA_INFORMATION7
250 ,P_STA_INFORMATION8 => l_pay_us_statetax_rule_rec.STA_INFORMATION8
251 ,P_STA_INFORMATION9 => l_pay_us_statetax_rule_rec.STA_INFORMATION9
252 ,P_STA_INFORMATION10 => l_pay_us_statetax_rule_rec.STA_INFORMATION10
253 ,P_STA_INFORMATION11 => l_pay_us_statetax_rule_rec.STA_INFORMATION11
254 ,P_STA_INFORMATION12 => l_pay_us_statetax_rule_rec.STA_INFORMATION12
255 ,P_STA_INFORMATION13 => l_pay_us_statetax_rule_rec.STA_INFORMATION13
256 ,P_STA_INFORMATION14 => l_pay_us_statetax_rule_rec.STA_INFORMATION14
257 ,P_STA_INFORMATION15 => l_pay_us_statetax_rule_rec.STA_INFORMATION15
258 ,P_STA_INFORMATION16 => l_pay_us_statetax_rule_rec.STA_INFORMATION16
259 ,P_STA_INFORMATION17 => l_pay_us_statetax_rule_rec.STA_INFORMATION17
260 ,P_STA_INFORMATION18 => l_pay_us_statetax_rule_rec.STA_INFORMATION18
261 ,P_STA_INFORMATION19 => l_pay_us_statetax_rule_rec.STA_INFORMATION19
262 ,P_STA_INFORMATION20 => l_pay_us_statetax_rule_rec.STA_INFORMATION20
263 ,P_STA_INFORMATION21 => l_pay_us_statetax_rule_rec.STA_INFORMATION21
264 ,P_STA_INFORMATION22 => l_pay_us_statetax_rule_rec.STA_INFORMATION22
265 ,P_STA_INFORMATION23 => l_pay_us_statetax_rule_rec.STA_INFORMATION23
266 ,P_STA_INFORMATION24 => l_pay_us_statetax_rule_rec.STA_INFORMATION24
267 ,P_STA_INFORMATION25 => l_pay_us_statetax_rule_rec.STA_INFORMATION25
268 ,P_STA_INFORMATION26 => l_pay_us_statetax_rule_rec.STA_INFORMATION26
269 ,P_STA_INFORMATION27 => l_pay_us_statetax_rule_rec.STA_INFORMATION27
270 ,P_STA_INFORMATION28 => l_pay_us_statetax_rule_rec.STA_INFORMATION28
271 ,P_STA_INFORMATION29 => l_pay_us_statetax_rule_rec.STA_INFORMATION29
272 ,P_STA_INFORMATION30 => l_pay_us_statetax_rule_rec.STA_INFORMATION30
273 ,p_emp_state_tax_rule_id => ln_emp_state_tax_rule_id
274 ,p_object_version_number => ln_ovn
275 ,p_effective_start_date => ld_eff_start_date
276 ,p_effective_end_date => ld_eff_end_date
277 );
278
279 hr_utility.trace('emp_state_tax_rule_id : '||to_char(ln_emp_state_tax_rule_id));
280 hr_utility.trace('call to PAY_STATE_TAX_RULE_API.CREATE_STATE_TAX_RULE done');
281 end if; -- end if for p_dp_mode validation for US State Tax
282
283 end if; -- p_spreadsheet_identifier = 'PAYRIUSSTATE'
284
285
286 if P_SPREADSHEET_IDENTIFIER = 'PAYRIUSCOUNTY' then
287
288 if p_dp_mode = 'UPDATE' then
289
290 hr_utility.trace('p_dp_mode :'||p_dp_mode);
291
292
293 -- check for valid datatrack update modes
294 Dt_Api.Find_DT_Upd_Modes
295 (p_effective_date => l_effective_date
296 ,p_base_table_name => 'PAY_US_EMP_COUNTY_TAX_RULES_F'
297 ,p_base_key_column => 'EMP_COUNTY_TAX_RULE_ID'
298 ,p_base_key_value => l_pay_us_countytax_rule_rec.EMP_COUNTY_TAX_RULE_ID
299 ,p_correction => l_dt_correction
300 ,p_update => l_dt_update
301 ,p_update_override => l_dt_upd_override
302 ,p_update_change_insert => l_upd_chg_ins
303 );
304
305
306 IF l_dt_update THEN
307
308 l_datetrack_update_mode := 'UPDATE';
309 hr_utility.trace('l_datetrack_update_mode :'||l_datetrack_update_mode);
310
311 ELSIF l_dt_upd_override OR
312 l_upd_chg_ins THEN
313 -- Need to check if future datetrack records exist
314 -- if yes then raise error
315 -- NULL;
316 hr_utility.set_message(801, 'HR_7211_DT_UPD_ROWS_IN_FUTURE');
317 hr_utility.raise_error;
318 ELSE
319 l_datetrack_update_mode := 'CORRECTION';
320 hr_utility.trace('l_datetrack_update_mode :'||l_datetrack_update_mode);
321 END IF;
322
323 ln_ovn := l_pay_us_countytax_rule_rec.OBJECT_VERSION_NUMBER;
324
325 pay_county_tax_rule_api.update_county_tax_rule
326 (p_emp_county_tax_rule_id => l_pay_us_countytax_rule_rec.EMP_COUNTY_TAX_RULE_ID
327 ,p_effective_start_date => ld_eff_start_date
328 ,p_effective_end_date => ld_eff_end_date
329 ,p_additional_wa_rate => l_pay_us_countytax_rule_rec.ADDITIONAL_WA_RATE
330 ,p_filing_status_code => l_pay_us_countytax_rule_rec.FILING_STATUS_CODE
331 ,p_lit_additional_tax => l_pay_us_countytax_rule_rec.LIT_ADDITIONAL_TAX
332 ,p_lit_override_amount => l_pay_us_countytax_rule_rec.LIT_OVERRIDE_AMOUNT
333 ,p_lit_override_rate => l_pay_us_countytax_rule_rec.LIT_OVERRIDE_RATE
334 ,p_withholding_allowances => l_pay_us_countytax_rule_rec.WITHHOLDING_ALLOWANCES
335 ,p_lit_exempt => l_pay_us_countytax_rule_rec.LIT_EXEMPT
336 ,p_sd_exempt => l_pay_us_countytax_rule_rec.SD_EXEMPT
337 ,p_ht_exempt => l_pay_us_countytax_rule_rec.HT_EXEMPT
338 ,p_school_district_code => l_pay_us_countytax_rule_rec.SCHOOL_DISTRICT_CODE
339 ,p_object_version_number => ln_ovn
340 ,p_effective_date => l_effective_date
341 ,p_datetrack_mode => l_datetrack_update_mode
342 );
343 hr_utility.trace('call to pay_county_tax_rule_api.update_county_tax_rule done');
344
345 elsif p_dp_mode = 'INSERT' then
346
347 hr_utility.trace('p_dp_mode :'||p_dp_mode);
348
349 pay_county_tax_rule_api.create_county_tax_rule
350 (p_emp_county_tax_rule_id => ln_emp_county_tax_rule_id
351 ,p_effective_start_date => ld_eff_start_date
352 ,p_effective_end_date => ld_eff_end_date
353 ,p_assignment_id => l_pay_us_countytax_rule_rec.assignment_id
354 ,p_state_code => l_pay_us_countytax_rule_rec.state_code
355 ,p_county_code => l_pay_us_countytax_rule_rec.county_code
356 ,p_additional_wa_rate => l_pay_us_countytax_rule_rec.ADDITIONAL_WA_RATE
357 ,p_filing_status_code => l_pay_us_countytax_rule_rec.FILING_STATUS_CODE
358 ,p_lit_additional_tax => l_pay_us_countytax_rule_rec.LIT_ADDITIONAL_TAX
359 ,p_lit_override_amount => l_pay_us_countytax_rule_rec.LIT_OVERRIDE_AMOUNT
360 ,p_lit_override_rate => l_pay_us_countytax_rule_rec.LIT_OVERRIDE_RATE
361 ,p_withholding_allowances => l_pay_us_countytax_rule_rec.WITHHOLDING_ALLOWANCES
362 ,p_lit_exempt => l_pay_us_countytax_rule_rec.LIT_EXEMPT
363 ,p_sd_exempt => l_pay_us_countytax_rule_rec.SD_EXEMPT
364 ,p_ht_exempt => l_pay_us_countytax_rule_rec.HT_EXEMPT
365 ,p_school_district_code => l_pay_us_countytax_rule_rec.SCHOOL_DISTRICT_CODE
366 ,p_object_version_number => ln_ovn
367 ,p_effective_date => l_effective_date
368 );
369
370
371 hr_utility.trace('call to pay_county_tax_rule_api.create_county_tax_rule done');
372 end if; -- end if for p_dp_mode validation for US County Tax
373
374 end if; -- P_SPREADSHEET_IDENTIFIER = 'PAYRIUSCOUNTY'
375
376
377 if P_SPREADSHEET_IDENTIFIER = 'PAYRIUSCITY' then
378
379 if p_dp_mode = 'UPDATE' then
380
381 hr_utility.trace('p_dp_mode :'||p_dp_mode);
382
383 -- check for valid datatrack update modes
384 Dt_Api.Find_DT_Upd_Modes
385 (p_effective_date => l_effective_date
386 ,p_base_table_name => 'PAY_US_EMP_CITY_TAX_RULES_F'
387 ,p_base_key_column => 'EMP_CITY_TAX_RULE_ID'
388 ,p_base_key_value => l_pay_us_citytax_rule_rec.EMP_CITY_TAX_RULE_ID
389 ,p_correction => l_dt_correction
390 ,p_update => l_dt_update
391 ,p_update_override => l_dt_upd_override
392 ,p_update_change_insert => l_upd_chg_ins
393 );
394
395 IF l_dt_update THEN
396
397 l_datetrack_update_mode := 'UPDATE';
398 hr_utility.trace('l_datetrack_update_mode :'||l_datetrack_update_mode);
399
400 ELSIF l_dt_upd_override OR
401 l_upd_chg_ins THEN
402 -- Need to check if future datetrack records exist
403 -- if yes then raise error
404 -- NULL;
405 hr_utility.set_message(801, 'HR_7211_DT_UPD_ROWS_IN_FUTURE');
406 hr_utility.raise_error;
407 ELSE
408 l_datetrack_update_mode := 'CORRECTION';
409 hr_utility.trace('l_datetrack_update_mode :'||l_datetrack_update_mode);
410 END IF;
411
412
413 ln_ovn := l_pay_us_citytax_rule_rec.OBJECT_VERSION_NUMBER;
414
415 pay_city_tax_rule_api.update_city_tax_rule
416 (P_DATETRACK_MODE => l_datetrack_update_mode
417 ,P_EFFECTIVE_DATE => l_effective_date
418 ,P_ADDITIONAL_WA_RATE => l_pay_us_citytax_rule_rec.ADDITIONAL_WA_RATE
419 ,P_FILING_STATUS_CODE => l_pay_us_citytax_rule_rec.FILING_STATUS_CODE
420 ,P_LIT_ADDITIONAL_TAX => l_pay_us_citytax_rule_rec.LIT_ADDITIONAL_TAX
421 ,P_LIT_OVERRIDE_AMOUNT => l_pay_us_citytax_rule_rec.LIT_OVERRIDE_AMOUNT
422 ,P_LIT_OVERRIDE_RATE => l_pay_us_citytax_rule_rec.LIT_OVERRIDE_RATE
423 ,P_WITHHOLDING_ALLOWANCES => l_pay_us_citytax_rule_rec.WITHHOLDING_ALLOWANCES
424 ,P_LIT_EXEMPT => l_pay_us_citytax_rule_rec.LIT_EXEMPT
425 ,P_SD_EXEMPT => l_pay_us_citytax_rule_rec.SD_EXEMPT
426 ,P_HT_EXEMPT => l_pay_us_citytax_rule_rec.HT_EXEMPT
427 ,P_SCHOOL_DISTRICT_CODE => l_pay_us_citytax_rule_rec.SCHOOL_DISTRICT_CODE
428 ,p_emp_city_tax_rule_id => l_pay_us_citytax_rule_rec.EMP_CITY_TAX_RULE_ID
429 ,p_object_version_number => ln_ovn
430 ,p_effective_start_date => ld_eff_start_date
431 ,p_effective_end_date => ld_eff_end_date
432 );
433
434 hr_utility.trace('call to pay_city_tax_rule_api.update_city_tax_rule done');
435
436 elsif p_dp_mode = 'INSERT' then
437
438 hr_utility.trace('p_dp_mode :'||p_dp_mode);
439
440 pay_city_tax_rule_api.create_city_tax_rule
441 (P_ASSIGNMENT_ID => l_pay_us_citytax_rule_rec.ASSIGNMENT_ID
442 ,P_STATE_CODE => l_pay_us_citytax_rule_rec.state_code
443 ,P_COUNTY_CODE => l_pay_us_citytax_rule_rec.county_code
444 ,P_CITY_CODE => l_pay_us_citytax_rule_rec.city_code
445 ,P_EFFECTIVE_DATE => l_effective_date
446 ,P_ADDITIONAL_WA_RATE => l_pay_us_citytax_rule_rec.ADDITIONAL_WA_RATE
447 ,P_FILING_STATUS_CODE => l_pay_us_citytax_rule_rec.FILING_STATUS_CODE
448 ,P_LIT_ADDITIONAL_TAX => l_pay_us_citytax_rule_rec.LIT_ADDITIONAL_TAX
449 ,P_LIT_OVERRIDE_AMOUNT => l_pay_us_citytax_rule_rec.LIT_OVERRIDE_AMOUNT
450 ,P_LIT_OVERRIDE_RATE => l_pay_us_citytax_rule_rec.LIT_OVERRIDE_RATE
451 ,P_WITHHOLDING_ALLOWANCES => l_pay_us_citytax_rule_rec.WITHHOLDING_ALLOWANCES
452 ,P_LIT_EXEMPT => l_pay_us_citytax_rule_rec.LIT_EXEMPT
453 ,P_SD_EXEMPT => l_pay_us_citytax_rule_rec.SD_EXEMPT
454 ,P_HT_EXEMPT => l_pay_us_citytax_rule_rec.HT_EXEMPT
455 ,P_SCHOOL_DISTRICT_CODE => l_pay_us_citytax_rule_rec.SCHOOL_DISTRICT_CODE
456 ,p_emp_city_tax_rule_id => ln_emp_city_tax_rule_id
457 ,p_effective_start_date => ld_eff_start_date
458 ,p_effective_end_date => ld_eff_end_date
459 ,p_object_version_number => ln_ovn
460 );
461
462 hr_utility.trace('call to pay_city_tax_rule_api.create_city_tax_rule done');
463 end if; -- end if for p_dp_mode validation for US County Tax
464
465 end if; -- P_SPREADSHEET_IDENTIFIER = 'PAYRIUSCITY'
466
467 if P_SPREADSHEET_IDENTIFIER = 'PAYRICAFED' then
468
469 if p_dp_mode = 'UPDATE' then
470
471 hr_utility.trace('p_dp_mode :'||p_dp_mode);
472
473 -- check for valid datatrack update modes
474 Dt_Api.Find_DT_Upd_Modes
475 (p_effective_date => l_effective_date
476 ,p_base_table_name => 'PAY_CA_EMP_FED_TAX_INFO_F'
477 ,p_base_key_column => 'EMP_FED_TAX_INF_ID'
478 ,p_base_key_value => l_pay_ca_fedtax_rule_rec.EMP_FED_TAX_INF_ID
479 ,p_correction => l_dt_correction
480 ,p_update => l_dt_update
481 ,p_update_override => l_dt_upd_override
482 ,p_update_change_insert => l_upd_chg_ins
483 );
484
485 IF l_dt_update THEN
486
487 l_datetrack_update_mode := 'UPDATE';
488 hr_utility.trace('l_datetrack_update_mode :'||l_datetrack_update_mode);
489
490 ELSIF l_dt_upd_override OR
491 l_upd_chg_ins THEN
492 -- Need to check if future dated record exists
493 -- if yes then raise error
494 -- NULL;
495 hr_utility.set_message(801, 'HR_7211_DT_UPD_ROWS_IN_FUTURE');
496 hr_utility.raise_error;
497 ELSE
498 l_datetrack_update_mode := 'CORRECTION';
499 hr_utility.trace('l_datetrack_update_mode :'||l_datetrack_update_mode);
500 END IF;
501
502 ln_ovn := l_pay_ca_fedtax_rule_rec.OBJECT_VERSION_NUMBER;
503
504 pay_ca_emp_fedtax_inf_api.update_ca_emp_fedtax_inf
505 (P_ASSIGNMENT_ID => l_pay_ca_fedtax_rule_rec.ASSIGNMENT_ID
506 ,P_LEGISLATION_CODE => l_pay_ca_fedtax_rule_rec.LEGISLATION_CODE
507 ,P_EMPLOYMENT_PROVINCE => l_pay_ca_fedtax_rule_rec.EMPLOYMENT_PROVINCE
508 ,P_TAX_CREDIT_AMOUNT => l_pay_ca_fedtax_rule_rec.TAX_CREDIT_AMOUNT
509 ,P_BASIC_EXEMPTION_FLAG => l_pay_ca_fedtax_rule_rec.BASIC_EXEMPTION_FLAG
510 ,P_ADDITIONAL_TAX => l_pay_ca_fedtax_rule_rec.ADDITIONAL_TAX
511 ,P_ANNUAL_DEDN => l_pay_ca_fedtax_rule_rec.ANNUAL_DEDN
512 ,P_TOTAL_EXPENSE_BY_COMMISSION => l_pay_ca_fedtax_rule_rec.TOTAL_EXPENSE_BY_COMMISSION
513 ,P_TOTAL_REMNRTN_BY_COMMISSION => l_pay_ca_fedtax_rule_rec.TOTAL_REMNRTN_BY_COMMISSION
514 ,P_PRESCRIBED_ZONE_DEDN_AMT => l_pay_ca_fedtax_rule_rec.PRESCRIBED_ZONE_DEDN_AMT
515 ,P_CPP_QPP_EXEMPT_FLAG => l_pay_ca_fedtax_rule_rec.CPP_QPP_EXEMPT_FLAG
516 ,P_FED_EXEMPT_FLAG => l_pay_ca_fedtax_rule_rec.FED_EXEMPT_FLAG
517 ,P_EI_EXEMPT_FLAG => l_pay_ca_fedtax_rule_rec.EI_EXEMPT_FLAG
518 ,P_FED_OVERRIDE_AMOUNT => l_pay_ca_fedtax_rule_rec.FED_OVERRIDE_AMOUNT
519 ,P_FED_OVERRIDE_RATE => l_pay_ca_fedtax_rule_rec.FED_OVERRIDE_RATE
520 ,P_OTHER_FEDTAX_CREDITS => l_pay_ca_fedtax_rule_rec.OTHER_FEDTAX_CREDITS
521 ,P_CA_TAX_INFORMATION_CATEGORY => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION_CATEGORY
522 ,P_CA_TAX_INFORMATION1 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION1
523 ,P_CA_TAX_INFORMATION2 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION2
524 ,P_CA_TAX_INFORMATION3 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION3
525 ,P_CA_TAX_INFORMATION4 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION4
526 ,P_CA_TAX_INFORMATION5 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION5
527 ,P_CA_TAX_INFORMATION6 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION6
528 ,P_CA_TAX_INFORMATION7 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION7
529 ,P_CA_TAX_INFORMATION8 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION8
530 ,P_CA_TAX_INFORMATION9 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION9
531 ,P_CA_TAX_INFORMATION10 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION10
532 ,P_CA_TAX_INFORMATION11 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION11
533 ,P_CA_TAX_INFORMATION12 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION12
534 ,P_CA_TAX_INFORMATION13 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION13
535 ,P_CA_TAX_INFORMATION14 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION14
536 ,P_CA_TAX_INFORMATION15 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION15
537 ,P_CA_TAX_INFORMATION16 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION16
538 ,P_CA_TAX_INFORMATION17 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION17
539 ,P_CA_TAX_INFORMATION18 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION18
540 ,P_CA_TAX_INFORMATION19 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION19
541 ,P_CA_TAX_INFORMATION20 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION20
542 ,P_CA_TAX_INFORMATION21 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION21
543 ,P_CA_TAX_INFORMATION22 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION22
544 ,P_CA_TAX_INFORMATION23 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION23
545 ,P_CA_TAX_INFORMATION24 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION24
546 ,P_CA_TAX_INFORMATION25 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION25
547 ,P_CA_TAX_INFORMATION26 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION26
548 ,P_CA_TAX_INFORMATION27 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION27
549 ,P_CA_TAX_INFORMATION28 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION28
550 ,P_CA_TAX_INFORMATION29 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION29
551 ,P_CA_TAX_INFORMATION30 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION30
552 ,P_FED_LSF_AMOUNT => l_pay_ca_fedtax_rule_rec.FED_LSF_AMOUNT
553 ,p_emp_fed_tax_inf_id => l_pay_ca_fedtax_rule_rec.emp_fed_tax_inf_id
554 ,P_EFFECTIVE_DATE => l_effective_date
555 ,P_DATETRACK_MODE => l_datetrack_update_mode
556 ,p_effective_start_date => ld_eff_start_date
557 ,p_effective_end_date => ld_eff_end_date
558 ,p_object_version_number => ln_ovn);
559
560 hr_utility.trace('call to pay_ca_emp_fedtax_inf_api.update_ca_emp_fedtax_inf done');
561
562 elsif p_dp_mode = 'INSERT' then
563
564 hr_utility.trace('p_dp_mode :'||p_dp_mode);
565
566 pay_ca_emp_fedtax_inf_api.create_ca_emp_fedtax_inf
567 (P_ASSIGNMENT_ID => l_pay_ca_fedtax_rule_rec.ASSIGNMENT_ID
568 ,P_LEGISLATION_CODE => l_pay_ca_fedtax_rule_rec.LEGISLATION_CODE
569 ,p_business_group_id => l_pay_ca_fedtax_rule_rec.business_group_id
570 ,P_EMPLOYMENT_PROVINCE => l_pay_ca_fedtax_rule_rec.EMPLOYMENT_PROVINCE
571 ,P_TAX_CREDIT_AMOUNT => l_pay_ca_fedtax_rule_rec.TAX_CREDIT_AMOUNT
572 ,P_BASIC_EXEMPTION_FLAG => l_pay_ca_fedtax_rule_rec.BASIC_EXEMPTION_FLAG
573 ,P_ADDITIONAL_TAX => l_pay_ca_fedtax_rule_rec.ADDITIONAL_TAX
574 ,P_ANNUAL_DEDN => l_pay_ca_fedtax_rule_rec.ANNUAL_DEDN
575 ,P_TOTAL_EXPENSE_BY_COMMISSION => l_pay_ca_fedtax_rule_rec.TOTAL_EXPENSE_BY_COMMISSION
576 ,P_TOTAL_REMNRTN_BY_COMMISSION => l_pay_ca_fedtax_rule_rec.TOTAL_REMNRTN_BY_COMMISSION
577 ,P_PRESCRIBED_ZONE_DEDN_AMT => l_pay_ca_fedtax_rule_rec.PRESCRIBED_ZONE_DEDN_AMT
578 ,P_CPP_QPP_EXEMPT_FLAG => l_pay_ca_fedtax_rule_rec.CPP_QPP_EXEMPT_FLAG
579 ,P_FED_EXEMPT_FLAG => l_pay_ca_fedtax_rule_rec.FED_EXEMPT_FLAG
580 ,P_EI_EXEMPT_FLAG => l_pay_ca_fedtax_rule_rec.EI_EXEMPT_FLAG
581 ,P_FED_OVERRIDE_AMOUNT => l_pay_ca_fedtax_rule_rec.FED_OVERRIDE_AMOUNT
582 ,P_FED_OVERRIDE_RATE => l_pay_ca_fedtax_rule_rec.FED_OVERRIDE_RATE
583 ,P_OTHER_FEDTAX_CREDITS => l_pay_ca_fedtax_rule_rec.OTHER_FEDTAX_CREDITS
584 ,P_CA_TAX_INFORMATION_CATEGORY => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION_CATEGORY
585 ,P_CA_TAX_INFORMATION1 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION1
586 ,P_CA_TAX_INFORMATION2 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION2
587 ,P_CA_TAX_INFORMATION3 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION3
588 ,P_CA_TAX_INFORMATION4 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION4
589 ,P_CA_TAX_INFORMATION5 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION5
590 ,P_CA_TAX_INFORMATION6 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION6
591 ,P_CA_TAX_INFORMATION7 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION7
592 ,P_CA_TAX_INFORMATION8 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION8
593 ,P_CA_TAX_INFORMATION9 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION9
594 ,P_CA_TAX_INFORMATION10 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION10
595 ,P_CA_TAX_INFORMATION11 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION11
596 ,P_CA_TAX_INFORMATION12 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION12
597 ,P_CA_TAX_INFORMATION13 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION13
598 ,P_CA_TAX_INFORMATION14 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION14
599 ,P_CA_TAX_INFORMATION15 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION15
600 ,P_CA_TAX_INFORMATION16 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION16
601 ,P_CA_TAX_INFORMATION17 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION17
602 ,P_CA_TAX_INFORMATION18 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION18
603 ,P_CA_TAX_INFORMATION19 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION19
604 ,P_CA_TAX_INFORMATION20 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION20
605 ,P_CA_TAX_INFORMATION21 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION21
606 ,P_CA_TAX_INFORMATION22 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION22
607 ,P_CA_TAX_INFORMATION23 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION23
608 ,P_CA_TAX_INFORMATION24 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION24
609 ,P_CA_TAX_INFORMATION25 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION25
610 ,P_CA_TAX_INFORMATION26 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION26
611 ,P_CA_TAX_INFORMATION27 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION27
612 ,P_CA_TAX_INFORMATION28 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION28
613 ,P_CA_TAX_INFORMATION29 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION29
614 ,P_CA_TAX_INFORMATION30 => l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION30
615 ,P_FED_LSF_AMOUNT => l_pay_ca_fedtax_rule_rec.FED_LSF_AMOUNT
616 ,p_emp_fed_tax_inf_id => ln_ca_emp_fed_tax_inf_id
617 ,P_EFFECTIVE_DATE => l_effective_date
618 ,p_effective_start_date => ld_eff_start_date
619 ,p_effective_end_date => ld_eff_end_date
620 ,p_object_version_number => ln_ovn);
621
622 hr_utility.trace('call to pay_ca_emp_fedtax_inf_api.create_ca_emp_fedtax_inf done');
623 end if; -- end if for p_dp_mode validation for Canada Federal Tax
624
625 end if; -- p_spreadsheet_identifier = 'PAYRICAFED'
626
627 if P_SPREADSHEET_IDENTIFIER = 'PAYRICAPROVINCE' then
628
629 if p_dp_mode = 'UPDATE' then
630
631 hr_utility.trace('p_dp_mode :'||p_dp_mode);
632
633 -- check for valid datatrack update modes
634 Dt_Api.Find_DT_Upd_Modes
635 (p_effective_date => l_effective_date
636 ,p_base_table_name => 'PAY_CA_EMP_PROV_TAX_INFO_F'
637 ,p_base_key_column => 'EMP_PROVINCE_TAX_INF_ID'
638 ,p_base_key_value => l_pay_ca_provtax_rule_rec.EMP_PROVINCE_TAX_INF_ID
639 ,p_correction => l_dt_correction
640 ,p_update => l_dt_update
641 ,p_update_override => l_dt_upd_override
642 ,p_update_change_insert => l_upd_chg_ins
643 );
644
645
646 IF l_dt_update THEN
647
648 l_datetrack_update_mode := 'UPDATE';
649 hr_utility.trace('l_datetrack_update_mode :'||l_datetrack_update_mode);
650
651 ELSIF l_dt_upd_override OR
652 l_upd_chg_ins THEN
653 -- Need to check if future dated record exists
654 -- if yes then raise error
655 -- NULL;
656 hr_utility.set_message(801, 'HR_7211_DT_UPD_ROWS_IN_FUTURE');
657 hr_utility.raise_error;
658 ELSE
659 l_datetrack_update_mode := 'CORRECTION';
660 hr_utility.trace('l_datetrack_update_mode :'||l_datetrack_update_mode);
661 END IF;
662
663 ln_ovn := l_pay_ca_provtax_rule_rec.OBJECT_VERSION_NUMBER;
664
665 PAY_CA_EMP_PRVTAX_INF_API.update_ca_emp_prvtax_inf
666 (p_emp_province_tax_inf_id => l_pay_ca_provtax_rule_rec.EMP_PROVINCE_TAX_INF_ID
667 ,P_LEGISLATION_CODE => l_pay_ca_provtax_rule_rec.LEGISLATION_CODE
668 ,P_ASSIGNMENT_ID => l_pay_ca_provtax_rule_rec.ASSIGNMENT_ID
669 ,P_PROVINCE_CODE => l_pay_ca_provtax_rule_rec.PROVINCE_CODE
670 ,P_TAX_CREDIT_AMOUNT => l_pay_ca_provtax_rule_rec.TAX_CREDIT_AMOUNT
671 ,P_BASIC_EXEMPTION_FLAG => l_pay_ca_provtax_rule_rec.BASIC_EXEMPTION_FLAG
672 ,P_MARRIAGE_STATUS => l_pay_ca_provtax_rule_rec.MARRIAGE_STATUS
673 ,P_NO_OF_INFIRM_DEPENDANTS => l_pay_ca_provtax_rule_rec.NO_OF_INFIRM_DEPENDANTS
674 ,P_NON_RESIDENT_STATUS => l_pay_ca_provtax_rule_rec.NON_RESIDENT_STATUS
675 ,P_DISABILITY_STATUS => l_pay_ca_provtax_rule_rec.DISABILITY_STATUS
676 ,P_NO_OF_DEPENDANTS => l_pay_ca_provtax_rule_rec.NO_OF_DEPENDANTS
677 ,P_ANNUAL_DEDN => l_pay_ca_provtax_rule_rec.ANNUAL_DEDN
678 ,P_TOTAL_EXPENSE_BY_COMMISSION => l_pay_ca_provtax_rule_rec.TOTAL_EXPENSE_BY_COMMISSION
679 ,P_TOTAL_REMNRTN_BY_COMMISSION => l_pay_ca_provtax_rule_rec.TOTAL_REMNRTN_BY_COMMISSION
680 ,P_PRESCRIBED_ZONE_DEDN_AMT => l_pay_ca_provtax_rule_rec.PRESCRIBED_ZONE_DEDN_AMT
681 ,P_ADDITIONAL_TAX => l_pay_ca_provtax_rule_rec.ADDITIONAL_TAX
682 ,P_PROV_OVERRIDE_RATE => l_pay_ca_provtax_rule_rec.PROV_OVERRIDE_RATE
683 ,P_PROV_OVERRIDE_AMOUNT => l_pay_ca_provtax_rule_rec.PROV_OVERRIDE_AMOUNT
684 ,P_PROV_EXEMPT_FLAG => l_pay_ca_provtax_rule_rec.PROV_EXEMPT_FLAG
685 ,P_PMED_EXEMPT_FLAG => l_pay_ca_provtax_rule_rec.PMED_EXEMPT_FLAG
686 ,P_WC_EXEMPT_FLAG => l_pay_ca_provtax_rule_rec.WC_EXEMPT_FLAG
687 ,P_QPP_EXEMPT_FLAG => l_pay_ca_provtax_rule_rec.QPP_EXEMPT_FLAG
688 ,P_EXTRA_INFO_NOT_PROVIDED => l_pay_ca_provtax_rule_rec.EXTRA_INFO_NOT_PROVIDED
689 ,P_OTHER_TAX_CREDIT => l_pay_ca_provtax_rule_rec.OTHER_TAX_CREDIT
690 ,P_CA_TAX_INFORMATION_CATEGORY => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION_CATEGORY
691 ,P_CA_TAX_INFORMATION1 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION1
692 ,P_CA_TAX_INFORMATION2 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION2
693 ,P_CA_TAX_INFORMATION3 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION3
694 ,P_CA_TAX_INFORMATION4 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION4
695 ,P_CA_TAX_INFORMATION5 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION5
696 ,P_CA_TAX_INFORMATION6 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION6
697 ,P_CA_TAX_INFORMATION7 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION7
698 ,P_CA_TAX_INFORMATION8 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION8
699 ,P_CA_TAX_INFORMATION9 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION9
700 ,P_CA_TAX_INFORMATION10 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION10
701 ,P_CA_TAX_INFORMATION11 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION11
702 ,P_CA_TAX_INFORMATION12 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION12
703 ,P_CA_TAX_INFORMATION13 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION13
704 ,P_CA_TAX_INFORMATION14 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION14
705 ,P_CA_TAX_INFORMATION15 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION15
706 ,P_CA_TAX_INFORMATION16 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION16
707 ,P_CA_TAX_INFORMATION17 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION17
708 ,P_CA_TAX_INFORMATION18 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION18
709 ,P_CA_TAX_INFORMATION19 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION19
710 ,P_CA_TAX_INFORMATION20 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION20
711 ,P_CA_TAX_INFORMATION21 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION21
712 ,P_CA_TAX_INFORMATION22 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION22
713 ,P_CA_TAX_INFORMATION23 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION23
714 ,P_CA_TAX_INFORMATION24 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION24
715 ,P_CA_TAX_INFORMATION25 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION25
716 ,P_CA_TAX_INFORMATION26 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION26
717 ,P_CA_TAX_INFORMATION27 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION27
718 ,P_CA_TAX_INFORMATION28 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION28
719 ,P_CA_TAX_INFORMATION29 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION29
720 ,P_CA_TAX_INFORMATION30 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION30
721 ,P_PROV_LSP_AMOUNT => l_pay_ca_provtax_rule_rec.PROV_LSP_AMOUNT
722 ,P_EFFECTIVE_DATE => l_effective_date
723 ,P_DATETRACK_MODE => l_datetrack_update_mode
724 ,p_effective_start_date => ld_eff_start_date
725 ,p_effective_end_date => ld_eff_end_date
726 ,p_object_version_number => ln_ovn);
727
728 hr_utility.trace('call to PAY_CA_EMP_PRVTAX_INF_API.update_ca_emp_prvtax_inf done');
729
730 elsif p_dp_mode = 'INSERT' then
731
732 hr_utility.trace('p_dp_mode :'||p_dp_mode);
733
734 hr_utility.trace('Calling PAY_CA_EMP_PRVTAX_INF_API.create_ca_emp_prvtax_inf API ');
735 PAY_CA_EMP_PRVTAX_INF_API.create_ca_emp_prvtax_inf(
736 p_emp_province_tax_inf_id => ln_ca_emp_prov_tax_inf_id
737 ,P_LEGISLATION_CODE => l_pay_ca_provtax_rule_rec.LEGISLATION_CODE
738 ,p_business_group_id => l_pay_ca_provtax_rule_rec.business_group_id
739 ,P_ASSIGNMENT_ID => l_pay_ca_provtax_rule_rec.ASSIGNMENT_ID
740 ,P_PROVINCE_CODE => l_pay_ca_provtax_rule_rec.PROVINCE_CODE
741 ,P_TAX_CREDIT_AMOUNT => l_pay_ca_provtax_rule_rec.TAX_CREDIT_AMOUNT
742 ,P_BASIC_EXEMPTION_FLAG => l_pay_ca_provtax_rule_rec.BASIC_EXEMPTION_FLAG
743 ,P_EXTRA_INFO_NOT_PROVIDED => l_pay_ca_provtax_rule_rec.EXTRA_INFO_NOT_PROVIDED
744 ,P_OTHER_TAX_CREDIT => l_pay_ca_provtax_rule_rec.OTHER_TAX_CREDIT
745 ,P_MARRIAGE_STATUS => l_pay_ca_provtax_rule_rec.MARRIAGE_STATUS
746 ,P_NO_OF_INFIRM_DEPENDANTS => l_pay_ca_provtax_rule_rec.NO_OF_INFIRM_DEPENDANTS
747 ,P_NON_RESIDENT_STATUS => l_pay_ca_provtax_rule_rec.NON_RESIDENT_STATUS
748 ,P_DISABILITY_STATUS => l_pay_ca_provtax_rule_rec.DISABILITY_STATUS
749 ,P_NO_OF_DEPENDANTS => l_pay_ca_provtax_rule_rec.NO_OF_DEPENDANTS
750 ,P_ANNUAL_DEDN => l_pay_ca_provtax_rule_rec.ANNUAL_DEDN
751 ,P_TOTAL_EXPENSE_BY_COMMISSION => l_pay_ca_provtax_rule_rec.TOTAL_EXPENSE_BY_COMMISSION
752 ,P_TOTAL_REMNRTN_BY_COMMISSION => l_pay_ca_provtax_rule_rec.TOTAL_REMNRTN_BY_COMMISSION
753 ,P_PRESCRIBED_ZONE_DEDN_AMT => l_pay_ca_provtax_rule_rec.PRESCRIBED_ZONE_DEDN_AMT
754 ,P_ADDITIONAL_TAX => l_pay_ca_provtax_rule_rec.ADDITIONAL_TAX
755 ,P_PROV_OVERRIDE_RATE => l_pay_ca_provtax_rule_rec.PROV_OVERRIDE_RATE
756 ,P_PROV_OVERRIDE_AMOUNT => l_pay_ca_provtax_rule_rec.PROV_OVERRIDE_AMOUNT
757 ,P_PROV_EXEMPT_FLAG => l_pay_ca_provtax_rule_rec.PROV_EXEMPT_FLAG
758 ,P_PMED_EXEMPT_FLAG => l_pay_ca_provtax_rule_rec.PMED_EXEMPT_FLAG
759 ,P_WC_EXEMPT_FLAG => l_pay_ca_provtax_rule_rec.WC_EXEMPT_FLAG
760 ,P_QPP_EXEMPT_FLAG => l_pay_ca_provtax_rule_rec.QPP_EXEMPT_FLAG
761 ,P_CA_TAX_INFORMATION_CATEGORY => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION_CATEGORY
762 ,P_CA_TAX_INFORMATION1 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION1
763 ,P_CA_TAX_INFORMATION2 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION2
764 ,P_CA_TAX_INFORMATION3 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION3
765 ,P_CA_TAX_INFORMATION4 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION4
766 ,P_CA_TAX_INFORMATION5 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION5
767 ,P_CA_TAX_INFORMATION6 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION6
768 ,P_CA_TAX_INFORMATION7 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION7
769 ,P_CA_TAX_INFORMATION8 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION8
770 ,P_CA_TAX_INFORMATION9 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION9
771 ,P_CA_TAX_INFORMATION10 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION10
772 ,P_CA_TAX_INFORMATION11 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION11
773 ,P_CA_TAX_INFORMATION12 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION12
774 ,P_CA_TAX_INFORMATION13 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION13
775 ,P_CA_TAX_INFORMATION14 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION14
776 ,P_CA_TAX_INFORMATION15 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION15
777 ,P_CA_TAX_INFORMATION16 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION16
778 ,P_CA_TAX_INFORMATION17 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION17
779 ,P_CA_TAX_INFORMATION18 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION18
780 ,P_CA_TAX_INFORMATION19 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION19
781 ,P_CA_TAX_INFORMATION20 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION20
782 ,P_CA_TAX_INFORMATION21 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION21
783 ,P_CA_TAX_INFORMATION22 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION22
784 ,P_CA_TAX_INFORMATION23 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION23
785 ,P_CA_TAX_INFORMATION24 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION24
786 ,P_CA_TAX_INFORMATION25 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION25
787 ,P_CA_TAX_INFORMATION26 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION26
788 ,P_CA_TAX_INFORMATION27 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION27
789 ,P_CA_TAX_INFORMATION28 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION28
790 ,P_CA_TAX_INFORMATION29 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION29
791 ,P_CA_TAX_INFORMATION30 => l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION30
792 ,P_PROV_LSP_AMOUNT => l_pay_ca_provtax_rule_rec.PROV_LSP_AMOUNT
793 ,P_EFFECTIVE_DATE => l_effective_date
794 ,p_effective_start_date => ld_eff_start_date
795 ,p_effective_end_date => ld_eff_end_date
796 ,p_object_version_number => ln_ovn);
797
798
799 hr_utility.trace('call to PAY_CA_EMP_PRVTAX_INF_API.create_ca_emp_prvtax_inf done');
800 end if; -- end if for p_dp_mode validation for Canada Provincial Tax
801
802
803 end if; -- p_spreadsheet_identifier = 'PAYRICAPROVINCE'
804
805
806 end HR_DataPump;
807
808
809 procedure create_ac_emptaxrule
810 (
811 p_business_group IN number default hr_api.g_number
812 ,p_effective_date IN date default hr_api.g_date
813 ,p_employee IN varchar2 default hr_api.g_varchar2
814 ,p_assignment IN varchar2 default hr_api.g_varchar2
815 ,p_legislation IN varchar2 default hr_api.g_varchar2
816 ,p_spreadsheet_identifier IN varchar2 default hr_api.g_varchar2
817 ,p_sui_state IN varchar2 default hr_api.g_varchar2
818 ,p_state_prov_code IN varchar2 default hr_api.g_varchar2
819 ,p_county IN varchar2 default hr_api.g_varchar2
820 ,p_city IN varchar2 default hr_api.g_varchar2
821 ,p_override_prov_of_emplt IN varchar2 default hr_api.g_varchar2
822 ,p_basic_amt_flag IN varchar2 default hr_api.g_varchar2
823 ,p_basic_amt IN number default hr_api.g_number
824 ,p_tax_credit IN number default hr_api.g_number
825 ,p_filing_status_code IN varchar2 default hr_api.g_varchar2
826 ,p_allowances IN number default hr_api.g_number
827 ,p_additional_tax IN number default hr_api.g_number
828 ,p_secondary_allowances IN number default hr_api.g_number
829 ,p_exemption_amt IN number default hr_api.g_number
830 ,p_sit_optional_calc_ind IN varchar2 default hr_api.g_varchar2
831 ,p_addtl_allowance_rate IN number default hr_api.g_number
832 ,p_pres_zone_dedn IN number default hr_api.g_number
833 ,p_tax_exempt1 IN varchar2 default hr_api.g_varchar2
834 ,p_tax_exempt2 IN varchar2 default hr_api.g_varchar2
835 ,p_tax_exempt3 IN varchar2 default hr_api.g_varchar2
836 ,p_tax_exempt4 IN varchar2 default hr_api.g_varchar2
837 ,p_override_rate IN number default hr_api.g_number
838 ,p_override_amount IN number default hr_api.g_number
839 ,p_override_supp_rate IN number default hr_api.g_number
840 ,p_annual_dedn IN number default hr_api.g_number
841 ,p_labor_fund_contr IN number default hr_api.g_number
842 ,p_allowance_reject_date IN date default hr_api.g_date
843 ,p_eic_filing_status IN varchar2 default hr_api.g_varchar2
844 ,p_statutory_employee IN varchar2 default hr_api.g_varchar2
845 ,p_cumulative_taxation IN varchar2 default hr_api.g_varchar2
846 ,p_non_resident IN varchar2 default hr_api.g_varchar2
847 ,p_sui_base_override_amount IN number default hr_api.g_number
848 ,p_school_district IN varchar2 default hr_api.g_varchar2
849 ,p_comm_renumeration IN number default hr_api.g_number
850 ,p_comm_expenses IN number default hr_api.g_number
851 ,p_spouse_or_equivalent IN varchar2 default hr_api.g_varchar2
852 ,p_disability_status IN varchar2 default hr_api.g_varchar2
853 ,p_number_of_dependents IN number default hr_api.g_number
854 ,p_number_of_infirm_dependents IN number default hr_api.g_number
855 ,p_sta_information_category in varchar2 default hr_api.g_varchar2
856 ,p_sta_information1 in varchar2 default hr_api.g_varchar2
857 ,p_sta_information2 in varchar2 default hr_api.g_varchar2
858 ,p_sta_information3 in varchar2 default hr_api.g_varchar2
859 ,p_sta_information4 in varchar2 default hr_api.g_varchar2
860 ,p_sta_information5 in varchar2 default hr_api.g_varchar2
861 ,p_sta_information6 in varchar2 default hr_api.g_varchar2
862 ,p_sta_information7 in varchar2 default hr_api.g_varchar2
863 ,p_sta_information8 in varchar2 default hr_api.g_varchar2
864 ,p_sta_information9 in varchar2 default hr_api.g_varchar2
865 ,p_sta_information10 in varchar2 default hr_api.g_varchar2
866 ,p_sta_information11 in varchar2 default hr_api.g_varchar2
867 ,p_sta_information12 in varchar2 default hr_api.g_varchar2
868 ,p_sta_information13 in varchar2 default hr_api.g_varchar2
869 ,p_sta_information14 in varchar2 default hr_api.g_varchar2
870 ,p_sta_information15 in varchar2 default hr_api.g_varchar2
871 ,p_sta_information16 in varchar2 default hr_api.g_varchar2
872 ,p_sta_information17 in varchar2 default hr_api.g_varchar2
873 ,p_sta_information18 in varchar2 default hr_api.g_varchar2
874 ,p_sta_information19 in varchar2 default hr_api.g_varchar2
875 ,p_sta_information20 in varchar2 default hr_api.g_varchar2
876 ,p_sta_information21 in varchar2 default hr_api.g_varchar2
877 ,p_sta_information22 in varchar2 default hr_api.g_varchar2
878 ,p_sta_information23 in varchar2 default hr_api.g_varchar2
879 ,p_sta_information24 in varchar2 default hr_api.g_varchar2
880 ,p_sta_information25 in varchar2 default hr_api.g_varchar2
881 ,p_sta_information26 in varchar2 default hr_api.g_varchar2
882 ,p_sta_information27 in varchar2 default hr_api.g_varchar2
883 ,p_sta_information28 in varchar2 default hr_api.g_varchar2
884 ,p_sta_information29 in varchar2 default hr_api.g_varchar2
885 ,p_sta_information30 in varchar2 default hr_api.g_varchar2
886 ,p_ca_tax_information_category in varchar2 default hr_api.g_varchar2
887 ,p_ca_tax_information1 in varchar2 default hr_api.g_varchar2
888 ,p_ca_tax_information2 in varchar2 default hr_api.g_varchar2
889 ,p_ca_tax_information3 in varchar2 default hr_api.g_varchar2
890 ,p_ca_tax_information4 in varchar2 default hr_api.g_varchar2
891 ,p_ca_tax_information5 in varchar2 default hr_api.g_varchar2
892 ,p_ca_tax_information6 in varchar2 default hr_api.g_varchar2
893 ,p_ca_tax_information7 in varchar2 default hr_api.g_varchar2
894 ,p_ca_tax_information8 in varchar2 default hr_api.g_varchar2
895 ,p_ca_tax_information9 in varchar2 default hr_api.g_varchar2
896 ,p_ca_tax_information10 in varchar2 default hr_api.g_varchar2
897 ,p_ca_tax_information11 in varchar2 default hr_api.g_varchar2
898 ,p_ca_tax_information12 in varchar2 default hr_api.g_varchar2
899 ,p_ca_tax_information13 in varchar2 default hr_api.g_varchar2
900 ,p_ca_tax_information14 in varchar2 default hr_api.g_varchar2
901 ,p_ca_tax_information15 in varchar2 default hr_api.g_varchar2
902 ,p_ca_tax_information16 in varchar2 default hr_api.g_varchar2
903 ,p_ca_tax_information17 in varchar2 default hr_api.g_varchar2
904 ,p_ca_tax_information18 in varchar2 default hr_api.g_varchar2
905 ,p_ca_tax_information19 in varchar2 default hr_api.g_varchar2
906 ,p_ca_tax_information20 in varchar2 default hr_api.g_varchar2
907 ,p_ca_tax_information21 in varchar2 default hr_api.g_varchar2
908 ,p_ca_tax_information22 in varchar2 default hr_api.g_varchar2
909 ,p_ca_tax_information23 in varchar2 default hr_api.g_varchar2
910 ,p_ca_tax_information24 in varchar2 default hr_api.g_varchar2
911 ,p_ca_tax_information25 in varchar2 default hr_api.g_varchar2
912 ,p_ca_tax_information26 in varchar2 default hr_api.g_varchar2
913 ,p_ca_tax_information27 in varchar2 default hr_api.g_varchar2
914 ,p_ca_tax_information28 in varchar2 default hr_api.g_varchar2
915 ,p_ca_tax_information29 in varchar2 default hr_api.g_varchar2
916 ,p_ca_tax_information30 in varchar2 default hr_api.g_varchar2
917 ,p_additional_info1 in varchar2 default hr_api.g_varchar2
918 ,p_additional_info2 in varchar2 default hr_api.g_varchar2
919 ,p_additional_info3 in varchar2 default hr_api.g_varchar2
920 ,p_additional_info4 in varchar2 default hr_api.g_varchar2
921 ,p_additional_info5 in varchar2 default hr_api.g_varchar2
922 ,p_additional_info6 in number default hr_api.g_number
923 ,p_additional_info7 in number default hr_api.g_number
924 ,p_additional_info8 in number default hr_api.g_number
925 ,p_additional_info9 in number default hr_api.g_number
926 ,p_additional_info10 in number default hr_api.g_number
927 ) is
928
929 ln_fedtax_rule_id pay_us_emp_fed_tax_rules_f.EMP_FED_TAX_RULE_ID%TYPE;
930 ln_statetax_rule_id pay_us_emp_state_tax_rules_f.EMP_state_TAX_RULE_ID%TYPE;
931 ln_countytax_rule_id pay_us_emp_county_tax_rules_f.EMP_county_TAX_RULE_ID%TYPE;
932 ln_citytax_rule_id pay_us_emp_city_tax_rules_f.EMP_city_TAX_RULE_ID%TYPE;
933 ln_cafedtax_inf_id PAY_CA_EMP_FED_TAX_INFO_F.EMP_FED_TAX_INF_ID%TYPE;
934 ln_caprovtax_inf_id PAY_CA_EMP_PROV_TAX_INFO_F.EMP_PROVINCE_TAX_INF_ID%TYPE;
935
936 ln_assignment_id per_all_assignments_f.assignment_id%TYPE;
937 ln_batch_exists number;
938 lv_lookup_code varchar2(20);
939
940 -- Added for datapump wrapper
941 ln_ovn2 number;
942 lv_county_code varchar2(10);
943 lv_city_code varchar2(10);
944 lv_county_school_distcode varchar2(10);
945 lv_city_school_distcode varchar2(10);
946
947
948 /* US Employee Federal Tax Rule record */
949 CURSOR csr_get_fedtax_id(cp_asg_id number) is
950 select ftr.EMP_FED_TAX_RULE_ID,ftr.object_version_number
951 from pay_us_emp_fed_tax_rules_f ftr
952 where ftr.assignment_id = cp_asg_id
953 and p_effective_date between ftr.effective_start_date and ftr.effective_end_date
954 and ftr.business_group_id = p_business_group;
955
956
957 /* US Employee State Tax Rule record */
958 CURSOR csr_get_statetax_id(cp_asg_id number) is
959 select str.EMP_STATE_TAX_RULE_ID,str.object_version_number
960 from PAY_US_EMP_STATE_TAX_RULES_F str
961 where str.assignment_id = cp_asg_id
962 and p_effective_date between str.effective_start_date and str.effective_end_date
963 and str.business_group_id = p_business_group
964 and str.state_code = ltrim(rtrim(p_state_prov_code));
965
966
967 /* US Employee County Tax Rule record */
968 CURSOR csr_get_countytax_id(cp_asg_id number,cp_county_code varchar2) is
969 select ctr.EMP_COUNTY_TAX_RULE_ID,ctr.object_version_number
970 from PAY_US_EMP_COUNTY_TAX_RULES_F ctr
971 where ctr.assignment_id = cp_asg_id
972 and p_effective_date between ctr.effective_start_date and ctr.effective_end_date
973 and ctr.business_group_id = p_business_group
974 and ctr.state_code = ltrim(rtrim(p_state_prov_code))
975 and ctr.county_code = ltrim(rtrim(cp_county_code));
976
977 /* US County code cursor */
978 CURSOR csr_get_county_code is
979 select county_code
980 from pay_us_counties
981 where state_code = ltrim(rtrim(p_state_prov_code))
982 and county_name = ltrim(rtrim(p_county));
983
984 /* US County School district code */
985 CURSOR csr_get_county_school_distcode(cp_county_code varchar2) is
986 select school_dst_code
987 from pay_us_county_school_dsts
988 where state_code = ltrim(rtrim(p_state_prov_code))
989 and county_code = cp_county_code
990 and school_dst_name = ltrim(rtrim(P_SCHOOL_DISTRICT));
991
992 /* US Employee City Tax Rule record */
993 CURSOR csr_get_citytax_id(cp_asg_id number,
994 cp_county_code varchar2,
995 cp_city_code varchar2) is
996 select ctr.EMP_CITY_TAX_RULE_ID,ctr.object_version_number
997 from PAY_US_EMP_CITY_TAX_RULES_F ctr
998 where ctr.assignment_id = cp_asg_id
999 and ctr.business_group_id = p_business_group
1000 and p_effective_date between ctr.effective_start_date and ctr.effective_end_date
1001 and ctr.state_code = ltrim(rtrim(p_state_prov_code))
1002 and ctr.county_code = ltrim(rtrim(cp_county_code))
1003 and ctr.city_code = ltrim(rtrim(cp_city_code));
1004
1005 /* Get US City code */
1006 CURSOR csr_get_city_code(cp_county_code varchar2) is
1007 select city_code
1008 from pay_us_city_names
1009 where state_code = ltrim(rtrim(p_state_prov_code))
1010 and county_code = ltrim(rtrim(cp_county_code))
1011 and city_name = ltrim(rtrim(p_city));
1012
1013 /* Get US City School District code */
1014 CURSOR csr_get_city_school_distcode(cp_county_code varchar2,
1015 cp_city_code varchar2) is
1016
1017 select SCHOOL_DST_CODE
1018 from PAY_US_CITY_SCHOOL_DSTS
1019 where state_code = ltrim(rtrim(p_state_prov_code))
1020 and county_code = cp_county_code
1021 and city_code = cp_city_code
1022 and SCHOOL_DST_NAME = ltrim(rtrim(P_SCHOOL_DISTRICT));
1023
1024 /* cursor to get the assignment_id */
1025 CURSOR csr_get_asg_id is
1026 select paf.assignment_id
1027 from per_all_assignments_f paf,
1028 per_all_people_f ppf
1029 where ppf.full_name = p_employee
1030 and ppf.person_id = paf.person_id
1031 and ppf.business_group_id = p_business_group
1032 and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
1033 and paf.assignment_number = p_assignment
1034 and p_effective_date between paf.effective_start_date and paf.effective_end_date
1035 and paf.business_group_id = p_business_group;
1036
1037 /* Get lookup code */
1038 CURSOR c_get_lookup_code(cp_lookup_type varchar2,
1039 cp_meaning varchar2) IS
1040 select lookup_code
1041 from hr_lookups
1042 where lookup_type = cp_lookup_type
1043 and meaning = cp_meaning;
1044
1045 /* Check Canada Federal Tax Record */
1046 CURSOR csr_get_cafedtax_id(cp_asg_id number) is
1047 select ftr.EMP_FED_TAX_INF_ID,ftr.object_version_number
1048 from PAY_CA_EMP_FED_TAX_INFO_F ftr
1049 where ftr.assignment_id = cp_asg_id
1050 and p_effective_date between ftr.effective_start_date and ftr.effective_end_date
1051 and ftr.business_group_id = p_business_group;
1052
1053
1054 /* Check Canada Provincial Tax Record */
1055 CURSOR csr_get_caprovtax_id(cp_asg_id number) is
1056 select pti.EMP_PROVINCE_TAX_INF_ID,pti.object_version_number
1057 from PAY_CA_EMP_PROV_TAX_INFO_F pti
1058 where pti.assignment_id = cp_asg_id
1059 and p_effective_date between pti.effective_start_date and pti.effective_end_date
1060 and pti.business_group_id = p_business_group
1061 and pti.province_code = ltrim(rtrim(p_state_prov_code));
1062
1063
1064 begin
1065 g_employee := p_employee;
1066 g_assignment := p_assignment;
1067
1068
1069 /*hr_utility.trace_on(null,'TESTTAX');*/
1070
1071 hr_utility.trace('p_business_group :'||to_char(p_business_group));
1072 hr_utility.trace('p_effective_date :'||to_char(p_effective_date,'YYYY/MM/DD'));
1073 hr_utility.trace('p_employee :'||to_char(p_employee));
1074 hr_utility.trace('p_assignment :'||to_char(p_assignment));
1075 hr_utility.trace('p_legislation :'||p_legislation);
1076 hr_utility.trace('p_spreadsheet_identifier :'||p_spreadsheet_identifier);
1077 hr_utility.trace('p_sui_state :'||p_sui_state);
1078 hr_utility.trace('p_state_prov_code :'||p_state_prov_code);
1079 hr_utility.trace('p_county :'||p_county);
1080 hr_utility.trace('p_city :'||p_city);
1081 hr_utility.trace('p_override_prov_of_emplt :'||p_override_prov_of_emplt);
1082 hr_utility.trace('p_basic_amt_flag :'||p_basic_amt_flag);
1083 hr_utility.trace('p_basic_amt :'||to_char(p_basic_amt));
1084 hr_utility.trace('p_tax_credit :'||to_char(p_tax_credit));
1085 hr_utility.trace('p_filing_status_code :'||p_filing_status_code);
1086 hr_utility.trace('p_allowances :'||to_char(p_allowances));
1087 hr_utility.trace('p_additional_tax :'||to_char(p_additional_tax));
1088 hr_utility.trace('p_secondary_allowances :'||to_char(p_secondary_allowances));
1089 hr_utility.trace('p_exemption_amt :'||to_char(p_exemption_amt));
1090 hr_utility.trace('p_sit_optional_calc_ind :'||p_sit_optional_calc_ind);
1091 hr_utility.trace('p_addtl_allowance_rate :'||to_char(p_addtl_allowance_rate));
1092 hr_utility.trace('p_pres_zone_dedn :'||to_char(p_pres_zone_dedn));
1093 hr_utility.trace('p_tax_exempt1 :'||p_tax_exempt1);
1094 hr_utility.trace('p_tax_exempt2 :'||p_tax_exempt2);
1095 hr_utility.trace('p_tax_exempt3 :'||p_tax_exempt3);
1096 hr_utility.trace('p_tax_exempt4 :'||p_tax_exempt4);
1097 hr_utility.trace('p_override_rate :'||to_char(p_override_rate));
1098 hr_utility.trace('p_override_amount :'||to_char(p_override_amount));
1099 hr_utility.trace('p_override_supp_rate :'||to_char(p_override_supp_rate));
1100 hr_utility.trace('p_annual_dedn :'||to_char(p_annual_dedn));
1101 hr_utility.trace('p_labor_fund_contr :'||to_char(p_labor_fund_contr));
1102 hr_utility.trace('p_allowance_reject_date :'||to_char(p_allowance_reject_date,'YYYY/MM/DD'));
1103 hr_utility.trace('p_eic_filing_status :'||p_eic_filing_status);
1104 hr_utility.trace('p_statutory_employee :'||p_statutory_employee);
1105 hr_utility.trace('p_statutory_employee :'||to_char(p_annual_dedn));
1106 hr_utility.trace('p_cumulative_taxation :'||p_cumulative_taxation);
1107 hr_utility.trace('p_non_resident :'||p_non_resident);
1108 hr_utility.trace('p_sui_base_override_amount :'||to_char(p_sui_base_override_amount));
1109 hr_utility.trace('p_school_district :'||p_school_district);
1110 hr_utility.trace('p_comm_renumeration :'||to_char(p_comm_renumeration));
1111 hr_utility.trace('p_comm_expenses :'||to_char(p_comm_expenses));
1112 hr_utility.trace('p_spouse_or_equivalent :'||p_spouse_or_equivalent);
1113 hr_utility.trace('p_disability_status :'||p_disability_status);
1114 hr_utility.trace('p_number_of_dependents :'||to_char(p_number_of_dependents));
1115 hr_utility.trace('p_number_of_infirm_dependents :'||to_char(p_number_of_infirm_dependents));
1116 hr_utility.trace('sta_information_category :'||p_sta_information_category);
1117 hr_utility.trace('sta_information1 :'||p_sta_information1);
1118 hr_utility.trace('sta_information2 :'||p_sta_information2);
1119 hr_utility.trace('sta_information3 :'||p_sta_information3);
1120 hr_utility.trace('sta_information4 :'||p_sta_information4);
1121 hr_utility.trace('sta_information5 :'||p_sta_information5);
1122 hr_utility.trace('sta_information6 :'||p_sta_information6);
1123 hr_utility.trace('sta_information7 :'||p_sta_information7);
1124 hr_utility.trace('sta_information8 :'||p_sta_information8);
1125 hr_utility.trace('sta_information9 :'||p_sta_information9);
1126 hr_utility.trace('sta_information10 :'||p_sta_information10);
1127 hr_utility.trace('sta_information11 :'||p_sta_information11);
1128 hr_utility.trace('sta_information12 :'||p_sta_information12);
1129 hr_utility.trace('sta_information13 :'||p_sta_information13);
1130 hr_utility.trace('sta_information14 :'||p_sta_information14);
1131 hr_utility.trace('sta_information15 :'||p_sta_information15);
1132 hr_utility.trace('sta_information16 :'||p_sta_information16);
1133 hr_utility.trace('sta_information17 :'||p_sta_information17);
1134 hr_utility.trace('sta_information18 :'||p_sta_information18);
1135 hr_utility.trace('sta_information19 :'||p_sta_information19);
1136 hr_utility.trace('sta_information20 :'||p_sta_information20);
1137 hr_utility.trace('sta_information21 :'||p_sta_information21);
1138 hr_utility.trace('sta_information22 :'||p_sta_information22);
1139 hr_utility.trace('sta_information23 :'||p_sta_information23);
1140 hr_utility.trace('sta_information24 :'||p_sta_information24);
1141 hr_utility.trace('sta_information25 :'||p_sta_information25);
1142 hr_utility.trace('sta_information26 :'||p_sta_information26);
1143 hr_utility.trace('sta_information27 :'||p_sta_information27);
1144 hr_utility.trace('sta_information28 :'||p_sta_information28);
1145 hr_utility.trace('sta_information29 :'||p_sta_information29);
1146 hr_utility.trace('sta_information30 :'||p_sta_information30);
1147
1148 open csr_get_asg_id;
1149 fetch csr_get_asg_id into ln_assignment_id;
1150 close csr_get_asg_id;
1151 hr_utility.trace('Assignment_id :'||to_char(ln_assignment_id));
1152
1153
1154 -- ========================================================================
1155 -- US Federal Tax Rule Information
1156 -- ========================================================================
1157
1158 if P_SPREADSHEET_IDENTIFIER = 'PAYRIUSFED' then
1159
1160 l_pay_us_fedtax_rule_rec.sui_state_code := p_sui_state;
1161 l_pay_us_fedtax_rule_rec.FILING_STATUS_CODE := p_filing_status_code;
1162 l_pay_us_fedtax_rule_rec.FIT_OVERRIDE_AMOUNT := NVL(p_override_amount,0);
1163 l_pay_us_fedtax_rule_rec.FIT_OVERRIDE_RATE := NVL(p_override_rate,0);
1164 l_pay_us_fedtax_rule_rec.WITHHOLDING_ALLOWANCES := p_allowances;
1165 l_pay_us_fedtax_rule_rec.CUMULATIVE_TAXATION := p_cumulative_taxation;
1166 l_pay_us_fedtax_rule_rec.EIC_FILING_STATUS_CODE := p_eic_filing_status;
1167 l_pay_us_fedtax_rule_rec.FIT_ADDITIONAL_TAX := NVL(p_additional_tax,0);
1168 l_pay_us_fedtax_rule_rec.FIT_EXEMPT := P_TAX_EXEMPT1;
1169 l_pay_us_fedtax_rule_rec.FUTA_TAX_EXEMPT := P_TAX_EXEMPT2;
1170 l_pay_us_fedtax_rule_rec.MEDICARE_TAX_EXEMPT := P_TAX_EXEMPT3;
1171 l_pay_us_fedtax_rule_rec.SS_TAX_EXEMPT := P_TAX_EXEMPT4;
1172 l_pay_us_fedtax_rule_rec.STATUTORY_EMPLOYEE := P_STATUTORY_EMPLOYEE ;
1173 l_pay_us_fedtax_rule_rec.SUPP_TAX_OVERRIDE_RATE := NVL(P_OVERRIDE_SUPP_RATE,0);
1174 l_pay_us_fedtax_rule_rec.EXCESSIVE_WA_REJECT_DATE := P_ALLOWANCE_REJECT_DATE;
1175
1176 open csr_get_fedtax_id(ln_assignment_id);
1177 fetch csr_get_fedtax_id into ln_fedtax_rule_id,ln_ovn2;
1178 hr_utility.trace('emp_fed_tax_rule_id :'||to_char(ln_fedtax_rule_id));
1179 hr_utility.trace('object_version_number :'||to_char(ln_ovn2));
1180
1181 if csr_get_fedtax_id%FOUND then
1182 l_pay_us_fedtax_rule_rec.EMP_FED_TAX_RULE_ID := ln_fedtax_rule_id;
1183 l_pay_us_fedtax_rule_rec.OBJECT_VERSION_NUMBER := ln_ovn2;
1184 close csr_get_fedtax_id;
1185
1186 HR_DataPump( p_dp_mode => 'CORRECTION'
1187 ,p_effective_date => p_effective_date
1188 ,p_spreadsheet_identifier => p_spreadsheet_identifier
1189 );
1190 end if;
1191
1192 -- end of p_spreadsheet_identifier = 'PAYRIUSFED'
1193
1194 -- ========================================================================
1195 -- US State Tax Rule Information
1196 -- ========================================================================
1197
1198 elsif P_SPREADSHEET_IDENTIFIER = 'PAYRIUSSTATE' then
1199
1200 open c_get_lookup_code('US_FS_'||P_STATE_PROV_CODE,p_filing_status_code);
1201 fetch c_get_lookup_code into lv_lookup_code;
1202 close c_get_lookup_code;
1203
1204 -- Fix for bug#4526310
1205 lv_lookup_code := '0'||lv_lookup_code;
1206
1207 hr_utility.trace('Filing Status Code :'||lv_lookup_code);
1208 l_pay_us_statetax_rule_rec := null;
1209
1210 l_pay_us_statetax_rule_rec.state_code := P_STATE_PROV_CODE;
1211 l_pay_us_statetax_rule_rec.FILING_STATUS_CODE := lv_lookup_code;
1212 l_pay_us_statetax_rule_rec.SECONDARY_WA := P_SECONDARY_ALLOWANCES;
1213 l_pay_us_statetax_rule_rec.SIT_OVERRIDE_AMOUNT := NVL(p_override_amount,0);
1214 l_pay_us_statetax_rule_rec.SIT_OVERRIDE_RATE := NVL(p_override_rate,0);
1215 l_pay_us_statetax_rule_rec.WITHHOLDING_ALLOWANCES := p_allowances;
1216 l_pay_us_statetax_rule_rec.SIT_ADDITIONAL_TAX := NVL(p_additional_tax,0);
1217 l_pay_us_statetax_rule_rec.SIT_EXEMPT := P_TAX_EXEMPT1;
1218 l_pay_us_statetax_rule_rec.SDI_EXEMPT := P_TAX_EXEMPT2;
1219 l_pay_us_statetax_rule_rec.SUI_EXEMPT := P_TAX_EXEMPT3;
1220 l_pay_us_statetax_rule_rec.WC_EXEMPT := P_TAX_EXEMPT4;
1221 l_pay_us_statetax_rule_rec.SIT_OPTIONAL_CALC_IND := P_SIT_OPTIONAL_CALC_IND;
1222 l_pay_us_statetax_rule_rec.STATE_NON_RESIDENT_CERT := P_NON_RESIDENT;
1223 l_pay_us_statetax_rule_rec.SUI_WAGE_BASE_OVERRIDE_AMOUNT := P_SUI_BASE_OVERRIDE_AMOUNT;
1224 l_pay_us_statetax_rule_rec.SUPP_TAX_OVERRIDE_RATE := NVL(P_OVERRIDE_SUPP_RATE,0);
1225 l_pay_us_statetax_rule_rec.EXCESSIVE_WA_REJECT_DATE := P_ALLOWANCE_REJECT_DATE;
1226 l_pay_us_statetax_rule_rec.ADDITIONAL_WA_AMOUNT := NVL(P_EXEMPTION_AMT,0);
1227 l_pay_us_statetax_rule_rec.ASSIGNMENT_ID := ln_assignment_id;
1228 l_pay_us_statetax_rule_rec.STA_INFORMATION_CATEGORY := p_sta_information_category;
1229 l_pay_us_statetax_rule_rec.STA_INFORMATION1 := p_sta_information1;
1230 l_pay_us_statetax_rule_rec.STA_INFORMATION2 := p_sta_information2;
1231 l_pay_us_statetax_rule_rec.STA_INFORMATION3 := p_sta_information3;
1232 l_pay_us_statetax_rule_rec.STA_INFORMATION4 := p_sta_information4;
1233 l_pay_us_statetax_rule_rec.STA_INFORMATION5 := p_sta_information5;
1234 l_pay_us_statetax_rule_rec.STA_INFORMATION6 := p_sta_information6;
1235 l_pay_us_statetax_rule_rec.STA_INFORMATION7 := p_sta_information7;
1236 l_pay_us_statetax_rule_rec.STA_INFORMATION8 := p_sta_information8;
1237 l_pay_us_statetax_rule_rec.STA_INFORMATION9 := p_sta_information9;
1238 l_pay_us_statetax_rule_rec.STA_INFORMATION10 := p_sta_information10;
1239 l_pay_us_statetax_rule_rec.STA_INFORMATION11 := p_sta_information11;
1240 l_pay_us_statetax_rule_rec.STA_INFORMATION12 := p_sta_information12;
1241 l_pay_us_statetax_rule_rec.STA_INFORMATION13 := p_sta_information13;
1242 l_pay_us_statetax_rule_rec.STA_INFORMATION14 := p_sta_information14;
1243 l_pay_us_statetax_rule_rec.STA_INFORMATION15 := p_sta_information15;
1244 l_pay_us_statetax_rule_rec.STA_INFORMATION16 := p_sta_information16;
1245 l_pay_us_statetax_rule_rec.STA_INFORMATION17 := p_sta_information17;
1246 l_pay_us_statetax_rule_rec.STA_INFORMATION18 := p_sta_information18;
1247 l_pay_us_statetax_rule_rec.STA_INFORMATION19 := p_sta_information19;
1248 l_pay_us_statetax_rule_rec.STA_INFORMATION20 := p_sta_information20;
1249 l_pay_us_statetax_rule_rec.STA_INFORMATION21 := p_sta_information21;
1250 l_pay_us_statetax_rule_rec.STA_INFORMATION22 := p_sta_information22;
1251 l_pay_us_statetax_rule_rec.STA_INFORMATION23 := p_sta_information23;
1252 l_pay_us_statetax_rule_rec.STA_INFORMATION24 := p_sta_information24;
1253 l_pay_us_statetax_rule_rec.STA_INFORMATION25 := p_sta_information25;
1254 l_pay_us_statetax_rule_rec.STA_INFORMATION26 := p_sta_information26;
1255 l_pay_us_statetax_rule_rec.STA_INFORMATION27 := p_sta_information27;
1256 l_pay_us_statetax_rule_rec.STA_INFORMATION28 := p_sta_information28;
1257 l_pay_us_statetax_rule_rec.STA_INFORMATION29 := p_sta_information29;
1258 l_pay_us_statetax_rule_rec.STA_INFORMATION30 := p_sta_information30;
1259
1260
1261 open csr_get_statetax_id(ln_assignment_id);
1262 fetch csr_get_statetax_id into ln_statetax_rule_id,ln_ovn2;
1263 hr_utility.trace('emp_state_tax_rule_id :'||to_char(ln_statetax_rule_id));
1264 hr_utility.trace('object_version_number :'||to_char(ln_ovn2));
1265
1266 if csr_get_statetax_id%FOUND then
1267 l_pay_us_statetax_rule_rec.EMP_STATE_TAX_RULE_ID := ln_statetax_rule_id;
1268 l_pay_us_statetax_rule_rec.OBJECT_VERSION_NUMBER := ln_ovn2;
1269
1270 close csr_get_statetax_id;
1271 HR_DataPump( p_dp_mode => 'UPDATE'
1272 ,p_effective_date => p_effective_date
1273 ,p_spreadsheet_identifier => p_spreadsheet_identifier
1274 );
1275 else
1276 l_pay_us_statetax_rule_rec.ASSIGNMENT_ID := ln_assignment_id;
1277
1278 close csr_get_statetax_id;
1279 HR_DataPump( p_dp_mode => 'INSERT'
1280 ,p_effective_date => p_effective_date
1281 ,p_spreadsheet_identifier => p_spreadsheet_identifier
1282 );
1283 end if;
1284
1285 -- end of p_spreadsheet_identifier = 'PAYRIUSSTATE'
1286
1287 -- ========================================================================
1288 -- US County Tax Rule Information
1289 -- ========================================================================
1290
1291 elsif p_spreadsheet_identifier = 'PAYRIUSCOUNTY' then
1292
1293 l_pay_us_countytax_rule_rec.state_code := P_STATE_PROV_CODE;
1294 l_pay_us_countytax_rule_rec.FILING_STATUS_CODE := p_filing_status_code;
1295 l_pay_us_countytax_rule_rec.ADDITIONAL_WA_RATE := NVL(P_ADDTL_ALLOWANCE_RATE,0);
1296 l_pay_us_countytax_rule_rec.LIT_OVERRIDE_AMOUNT := NVL(p_override_amount,0);
1297 l_pay_us_countytax_rule_rec.LIT_OVERRIDE_RATE := NVL(p_override_rate,0);
1298 l_pay_us_countytax_rule_rec.WITHHOLDING_ALLOWANCES := p_allowances;
1299 l_pay_us_countytax_rule_rec.LIT_ADDITIONAL_TAX := NVL(p_additional_tax,0);
1300 l_pay_us_countytax_rule_rec.LIT_EXEMPT := P_TAX_EXEMPT1;
1301 l_pay_us_countytax_rule_rec.SD_EXEMPT := P_TAX_EXEMPT2;
1302 l_pay_us_countytax_rule_rec.HT_EXEMPT := P_TAX_EXEMPT3;
1303 l_pay_us_countytax_rule_rec.ASSIGNMENT_ID := ln_assignment_id;
1304
1305
1306 open csr_get_county_code;
1307 fetch csr_get_county_code into lv_county_code;
1308 close csr_get_county_code;
1309
1310 if P_SCHOOL_DISTRICT is not null then
1311 open csr_get_county_school_distcode(lv_county_code);
1312 fetch csr_get_county_school_distcode into lv_county_school_distcode;
1313 close csr_get_county_school_distcode;
1314 end if;
1315
1316 l_pay_us_countytax_rule_rec.SCHOOL_DISTRICT_CODE := lv_county_school_distcode;
1317 l_pay_us_countytax_rule_rec.county_code := lv_county_code;
1318
1319 open csr_get_countytax_id(ln_assignment_id,lv_county_code);
1320 fetch csr_get_countytax_id into ln_countytax_rule_id,ln_ovn2;
1321 hr_utility.trace('emp_county_tax_rule_id :'||to_char(ln_countytax_rule_id));
1322 hr_utility.trace('object_version_number :'||to_char(ln_ovn2));
1323
1324 if csr_get_countytax_id%FOUND then
1325 l_pay_us_countytax_rule_rec.EMP_COUNTY_TAX_RULE_ID := ln_countytax_rule_id;
1326 l_pay_us_countytax_rule_rec.OBJECT_VERSION_NUMBER := ln_ovn2;
1327
1328 close csr_get_countytax_id;
1329 HR_DataPump(
1330 p_dp_mode => 'UPDATE'
1331 ,p_effective_date => p_effective_date
1332 ,p_spreadsheet_identifier => p_spreadsheet_identifier
1333 );
1334 else
1335 l_pay_us_countytax_rule_rec.ASSIGNMENT_ID := ln_assignment_id;
1336
1337 close csr_get_countytax_id;
1338 HR_DataPump(
1339 p_dp_mode => 'INSERT'
1340 ,p_effective_date => p_effective_date
1341 ,p_spreadsheet_identifier => p_spreadsheet_identifier
1342 );
1343 end if;
1344 -- end of p_spreadsheet_identifier = 'PAYRIUSCOUNTY'
1345
1346 -- ========================================================================
1347 -- US City Tax Rule Information
1348 -- ========================================================================
1349
1350 elsif p_spreadsheet_identifier = 'PAYRIUSCITY' then
1351
1352 l_pay_us_citytax_rule_rec.state_code := P_STATE_PROV_CODE;
1353 l_pay_us_citytax_rule_rec.FILING_STATUS_CODE := p_filing_status_code;
1354 l_pay_us_citytax_rule_rec.ADDITIONAL_WA_RATE := NVL(P_ADDTL_ALLOWANCE_RATE,0);
1355 l_pay_us_citytax_rule_rec.LIT_OVERRIDE_AMOUNT := NVL(p_override_amount,0);
1356 l_pay_us_citytax_rule_rec.LIT_OVERRIDE_RATE := NVL(p_override_rate,0);
1357 l_pay_us_citytax_rule_rec.WITHHOLDING_ALLOWANCES := p_allowances;
1358 l_pay_us_citytax_rule_rec.LIT_ADDITIONAL_TAX := NVL(p_additional_tax,0);
1359 l_pay_us_citytax_rule_rec.LIT_EXEMPT := P_TAX_EXEMPT1;
1360 l_pay_us_citytax_rule_rec.SD_EXEMPT := P_TAX_EXEMPT2;
1361 l_pay_us_citytax_rule_rec.HT_EXEMPT := P_TAX_EXEMPT3;
1362 l_pay_us_citytax_rule_rec.ASSIGNMENT_ID := ln_assignment_id;
1363
1364 open csr_get_county_code;
1365 fetch csr_get_county_code into lv_county_code;
1366 close csr_get_county_code;
1367
1368 open csr_get_city_code(lv_county_code);
1369 fetch csr_get_city_code into lv_city_code;
1370 close csr_get_city_code;
1371
1372 if ltrim(rtrim(P_SCHOOL_DISTRICT)) is not null then
1373 open csr_get_city_school_distcode(lv_county_code,lv_city_code);
1374 fetch csr_get_city_school_distcode into lv_city_school_distcode;
1375 close csr_get_city_school_distcode;
1376 end if;
1377
1378 l_pay_us_citytax_rule_rec.county_code := lv_county_code;
1379 l_pay_us_citytax_rule_rec.CITY_CODE := lv_city_code;
1380 l_pay_us_citytax_rule_rec.SCHOOL_DISTRICT_CODE := lv_city_school_distcode;
1381
1382 open csr_get_citytax_id(ln_assignment_id,lv_county_code,lv_city_code);
1383 fetch csr_get_citytax_id into ln_citytax_rule_id,ln_ovn2;
1384 hr_utility.trace('emp_city_tax_rule_id :'||to_char(ln_citytax_rule_id));
1385 hr_utility.trace('object_version_number :'||to_char(ln_ovn2));
1386
1387 if csr_get_citytax_id%FOUND then
1388 l_pay_us_citytax_rule_rec.EMP_CITY_TAX_RULE_ID := ln_citytax_rule_id;
1389 l_pay_us_citytax_rule_rec.OBJECT_VERSION_NUMBER := ln_ovn2;
1390
1391 close csr_get_citytax_id;
1392 HR_DataPump(
1393 p_dp_mode => 'UPDATE'
1394 ,p_effective_date => p_effective_date
1395 ,p_spreadsheet_identifier => p_spreadsheet_identifier
1396 );
1397 else
1398 l_pay_us_citytax_rule_rec.ASSIGNMENT_ID := ln_assignment_id;
1399
1400 close csr_get_citytax_id;
1401 HR_DataPump(
1402 p_dp_mode => 'INSERT'
1403 ,p_effective_date => p_effective_date
1404 ,p_spreadsheet_identifier => p_spreadsheet_identifier
1405 );
1406 end if;
1407 -- end of p_spreadsheet_identifier = 'PAYRIUSCITY'
1408
1409
1410 elsif P_SPREADSHEET_IDENTIFIER = 'PAYRICAFED' then
1411
1412 if P_BASIC_AMT_FLAG = 'Y' and P_TAX_CREDIT is NOT NULL then
1413 hr_utility.set_message(800, 'HR_74007_BOTH_NOT_NULL');
1414 hr_utility.raise_error;
1415 elsif (P_BASIC_AMT_FLAG = 'N' and P_TAX_CREDIT is null) then
1416 l_pay_ca_fedtax_rule_rec.TAX_CREDIT_AMOUNT := 0;
1417 elsif (P_BASIC_AMT_FLAG = 'N' and P_TAX_CREDIT is Not null) then
1418 l_pay_ca_fedtax_rule_rec.TAX_CREDIT_AMOUNT := P_TAX_CREDIT;
1419 else
1420 l_pay_ca_fedtax_rule_rec.TAX_CREDIT_AMOUNT := P_TAX_CREDIT;
1421 end if;
1422
1423
1424 l_pay_ca_fedtax_rule_rec.LEGISLATION_CODE := 'CA';
1425 l_pay_ca_fedtax_rule_rec.business_group_id := p_business_group;
1426 l_pay_ca_fedtax_rule_rec.EMPLOYMENT_PROVINCE := p_override_prov_of_emplt;
1427 l_pay_ca_fedtax_rule_rec.BASIC_EXEMPTION_FLAG := P_BASIC_AMT_FLAG;
1428 l_pay_ca_fedtax_rule_rec.ADDITIONAL_TAX := P_ADDITIONAL_TAX;
1429 l_pay_ca_fedtax_rule_rec.ANNUAL_DEDN := P_ANNUAL_DEDN;
1430 l_pay_ca_fedtax_rule_rec.TOTAL_EXPENSE_BY_COMMISSION := P_COMM_EXPENSES;
1431 l_pay_ca_fedtax_rule_rec.TOTAL_REMNRTN_BY_COMMISSION := P_COMM_RENUMERATION;
1432 l_pay_ca_fedtax_rule_rec.PRESCRIBED_ZONE_DEDN_AMT := P_PRES_ZONE_DEDN;
1433 l_pay_ca_fedtax_rule_rec.OTHER_FEDTAX_CREDITS := 0;
1434 l_pay_ca_fedtax_rule_rec.CPP_QPP_EXEMPT_FLAG := NVL(P_TAX_EXEMPT3,'N');
1435 l_pay_ca_fedtax_rule_rec.FED_EXEMPT_FLAG := NVL(P_TAX_EXEMPT1,'N');
1436 l_pay_ca_fedtax_rule_rec.EI_EXEMPT_FLAG := NVL(P_TAX_EXEMPT2,'N');
1437 --l_pay_ca_fedtax_rule_rec.TAX_CALC_METHOD := ?;
1438 l_pay_ca_fedtax_rule_rec.FED_OVERRIDE_AMOUNT := P_OVERRIDE_AMOUNT;
1439 l_pay_ca_fedtax_rule_rec.FED_OVERRIDE_RATE := P_OVERRIDE_RATE;
1440 l_pay_ca_fedtax_rule_rec.FED_LSF_AMOUNT := P_LABOR_FUND_CONTR;
1441 l_pay_ca_fedtax_rule_rec.ASSIGNMENT_ID := ln_assignment_id;
1442 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION_CATEGORY := p_CA_TAX_INFORMATION_CATEGORY;
1443 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION1 := p_CA_TAX_INFORMATION1;
1444 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION2 := p_CA_TAX_INFORMATION2;
1445 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION3 := p_CA_TAX_INFORMATION3;
1446 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION4 := p_CA_TAX_INFORMATION4;
1447 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION5 := p_CA_TAX_INFORMATION5;
1448 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION6 := p_CA_TAX_INFORMATION6;
1449 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION7 := p_CA_TAX_INFORMATION7;
1450 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION8 := p_CA_TAX_INFORMATION8;
1451 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION9 := p_CA_TAX_INFORMATION9;
1452 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION10 := p_CA_TAX_INFORMATION10;
1453 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION11 := p_CA_TAX_INFORMATION11;
1454 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION12 := p_CA_TAX_INFORMATION12;
1455 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION13 := p_CA_TAX_INFORMATION13;
1456 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION14 := p_CA_TAX_INFORMATION14;
1457 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION15 := p_CA_TAX_INFORMATION15;
1458 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION16 := p_CA_TAX_INFORMATION16;
1459 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION17 := p_CA_TAX_INFORMATION17;
1460 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION18 := p_CA_TAX_INFORMATION18;
1461 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION19 := p_CA_TAX_INFORMATION19;
1462 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION20 := p_CA_TAX_INFORMATION20;
1463 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION21 := p_CA_TAX_INFORMATION21;
1464 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION22 := p_CA_TAX_INFORMATION22;
1465 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION23 := p_CA_TAX_INFORMATION23;
1466 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION24 := p_CA_TAX_INFORMATION24;
1467 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION25 := p_CA_TAX_INFORMATION25;
1468 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION26 := p_CA_TAX_INFORMATION26;
1469 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION27 := p_CA_TAX_INFORMATION27;
1470 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION28 := p_CA_TAX_INFORMATION28;
1471 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION29 := p_CA_TAX_INFORMATION29;
1472 l_pay_ca_fedtax_rule_rec.CA_TAX_INFORMATION30 := p_CA_TAX_INFORMATION30;
1473
1474 open csr_get_cafedtax_id(ln_assignment_id);
1475 fetch csr_get_cafedtax_id into ln_cafedtax_inf_id,ln_ovn2;
1476 hr_utility.trace('emp_cafed_tax_inf_id :'||to_char(ln_cafedtax_inf_id));
1477 hr_utility.trace('object_version_number :'||to_char(ln_ovn2));
1478
1479 if csr_get_cafedtax_id%FOUND then
1480 l_pay_ca_fedtax_rule_rec.EMP_FED_TAX_INF_ID := ln_cafedtax_inf_id;
1481 l_pay_ca_fedtax_rule_rec.OBJECT_VERSION_NUMBER := ln_ovn2;
1482
1483 close csr_get_cafedtax_id;
1484 HR_DataPump(
1485 p_dp_mode => 'UPDATE'
1486 ,p_effective_date => p_effective_date
1487 ,p_spreadsheet_identifier => p_spreadsheet_identifier
1488 );
1489 else
1490 l_pay_ca_fedtax_rule_rec.ASSIGNMENT_ID := ln_assignment_id;
1491
1492 close csr_get_cafedtax_id;
1493 HR_DataPump(
1494 p_dp_mode => 'INSERT'
1495 ,p_effective_date => p_effective_date
1496 ,p_spreadsheet_identifier => p_spreadsheet_identifier
1497 );
1498 end if;
1499
1500 -- end of p_spreadsheet_identifier = 'PAYRICAFED'
1501
1502
1503 elsif P_SPREADSHEET_IDENTIFIER = 'PAYRICAPROVINCE' then
1504
1505 if P_BASIC_AMT_FLAG = 'Y' and P_TAX_CREDIT is not NULL then
1506 hr_utility.set_message(800, 'HR_74007_BOTH_NOT_NULL');
1507 hr_utility.raise_error;
1508 elsif (P_BASIC_AMT_FLAG = 'N' and P_TAX_CREDIT is null) then
1509 l_pay_ca_provtax_rule_rec.TAX_CREDIT_AMOUNT := 0;
1510 elsif (P_BASIC_AMT_FLAG = 'N' and P_TAX_CREDIT is Not null) then
1511 l_pay_ca_provtax_rule_rec.TAX_CREDIT_AMOUNT := P_TAX_CREDIT;
1512 else
1513 l_pay_ca_provtax_rule_rec.TAX_CREDIT_AMOUNT := P_TAX_CREDIT;
1514 end if;
1515
1516 if P_STATE_PROV_CODE = 'QC' then
1517 l_pay_ca_provtax_rule_rec.PROV_EXEMPT_FLAG := NVL(P_TAX_EXEMPT1,'N');
1518 l_pay_ca_provtax_rule_rec.QPP_EXEMPT_FLAG := NVL(P_TAX_EXEMPT4,'N');
1519 l_pay_ca_provtax_rule_rec.PROV_OVERRIDE_AMOUNT := NVL(P_OVERRIDE_AMOUNT,0);
1520 l_pay_ca_provtax_rule_rec.PROV_OVERRIDE_RATE := NVL(P_OVERRIDE_RATE,0);
1521 l_pay_ca_provtax_rule_rec.ADDITIONAL_TAX := NVL(P_ADDITIONAL_TAX,0);
1522
1523 elsif P_STATE_PROV_CODE in ('NT','NU') then
1524 l_pay_ca_provtax_rule_rec.PROV_EXEMPT_FLAG := NVL(P_TAX_EXEMPT1,'N');
1525 l_pay_ca_provtax_rule_rec.QPP_EXEMPT_FLAG := 'N';
1526 l_pay_ca_provtax_rule_rec.PROV_OVERRIDE_AMOUNT := NVL(P_OVERRIDE_AMOUNT,0);
1527 l_pay_ca_provtax_rule_rec.PROV_OVERRIDE_RATE := NVL(P_OVERRIDE_RATE,0);
1528 l_pay_ca_provtax_rule_rec.ADDITIONAL_TAX := NVL(P_ADDITIONAL_TAX,0);
1529
1530 else
1531 -- These values are hardcoded because they are not enabled in PUI forms
1532 -- when end user enters other province values
1533 l_pay_ca_provtax_rule_rec.PROV_EXEMPT_FLAG := 'N';
1534 l_pay_ca_provtax_rule_rec.QPP_EXEMPT_FLAG := 'N';
1535 l_pay_ca_provtax_rule_rec.PROV_OVERRIDE_AMOUNT := 0;
1536 l_pay_ca_provtax_rule_rec.PROV_OVERRIDE_RATE := 0;
1537 l_pay_ca_provtax_rule_rec.ADDITIONAL_TAX := 0;
1538 end if;
1539
1540 hr_utility.trace('Assigning parameter values to province record ');
1541 l_pay_ca_provtax_rule_rec.LEGISLATION_CODE := 'CA';
1542 l_pay_ca_provtax_rule_rec.business_group_id := p_business_group;
1543 l_pay_ca_provtax_rule_rec.PROVINCE_CODE := ltrim(rtrim(P_STATE_PROV_CODE));
1544 l_pay_ca_provtax_rule_rec.BASIC_EXEMPTION_FLAG := P_BASIC_AMT_FLAG;
1545 l_pay_ca_provtax_rule_rec.MARRIAGE_STATUS := p_spouse_or_equivalent;
1546 l_pay_ca_provtax_rule_rec.NO_OF_INFIRM_DEPENDANTS := NVL(p_number_of_infirm_dependents,0);
1547 l_pay_ca_provtax_rule_rec.NON_RESIDENT_STATUS := p_non_resident;
1548 l_pay_ca_provtax_rule_rec.DISABILITY_STATUS := p_disability_status;
1549 l_pay_ca_provtax_rule_rec.NO_OF_DEPENDANTS := NVL(p_number_of_dependents,0);
1550 l_pay_ca_provtax_rule_rec.ANNUAL_DEDN := P_ANNUAL_DEDN;
1551 l_pay_ca_provtax_rule_rec.TOTAL_EXPENSE_BY_COMMISSION := P_COMM_EXPENSES;
1552 l_pay_ca_provtax_rule_rec.TOTAL_REMNRTN_BY_COMMISSION := P_COMM_RENUMERATION;
1553 l_pay_ca_provtax_rule_rec.PRESCRIBED_ZONE_DEDN_AMT := P_PRES_ZONE_DEDN;
1554 l_pay_ca_provtax_rule_rec.WC_EXEMPT_FLAG := NVL(P_TAX_EXEMPT3,'N');
1555 l_pay_ca_provtax_rule_rec.PMED_EXEMPT_FLAG := NVL(P_TAX_EXEMPT2,'N');
1556 l_pay_ca_provtax_rule_rec.PROV_LSP_AMOUNT := NVL(P_LABOR_FUND_CONTR,0);
1557 l_pay_ca_provtax_rule_rec.ASSIGNMENT_ID := ln_assignment_id;
1558 l_pay_ca_provtax_rule_rec.EXTRA_INFO_NOT_PROVIDED := 'Y';
1559 l_pay_ca_provtax_rule_rec.OTHER_TAX_CREDIT := 0;
1560 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION_CATEGORY := p_CA_TAX_INFORMATION_CATEGORY;
1561 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION1 := p_CA_TAX_INFORMATION1;
1562 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION2 := p_CA_TAX_INFORMATION2;
1563 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION3 := p_CA_TAX_INFORMATION3;
1564 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION4 := p_CA_TAX_INFORMATION4;
1565 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION5 := p_CA_TAX_INFORMATION5;
1566 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION6 := p_CA_TAX_INFORMATION6;
1567 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION7 := p_CA_TAX_INFORMATION7;
1568 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION8 := p_CA_TAX_INFORMATION8;
1569 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION9 := p_CA_TAX_INFORMATION9;
1570 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION10 := p_CA_TAX_INFORMATION10;
1571 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION11 := p_CA_TAX_INFORMATION11;
1572 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION12 := p_CA_TAX_INFORMATION12;
1573 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION13 := p_CA_TAX_INFORMATION13;
1574 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION14 := p_CA_TAX_INFORMATION14;
1575 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION15 := p_CA_TAX_INFORMATION15;
1576 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION16 := p_CA_TAX_INFORMATION16;
1577 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION17 := p_CA_TAX_INFORMATION17;
1578 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION18 := p_CA_TAX_INFORMATION18;
1579 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION19 := p_CA_TAX_INFORMATION19;
1580 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION20 := p_CA_TAX_INFORMATION20;
1581 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION21 := p_CA_TAX_INFORMATION21;
1582 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION22 := p_CA_TAX_INFORMATION22;
1583 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION23 := p_CA_TAX_INFORMATION23;
1584 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION24 := p_CA_TAX_INFORMATION24;
1585 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION25 := p_CA_TAX_INFORMATION25;
1586 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION26 := p_CA_TAX_INFORMATION26;
1587 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION27 := p_CA_TAX_INFORMATION27;
1588 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION28 := p_CA_TAX_INFORMATION28;
1589 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION29 := p_CA_TAX_INFORMATION29;
1590 l_pay_ca_provtax_rule_rec.CA_TAX_INFORMATION30 := p_CA_TAX_INFORMATION30;
1591
1592 --l_pay_ca_provtax_rule_rec.TAX_CALC_METHOD := ?;
1593
1594 open csr_get_caprovtax_id(ln_assignment_id);
1595 fetch csr_get_caprovtax_id into ln_caprovtax_inf_id,ln_ovn2;
1596 hr_utility.trace('emp_caprov_tax_inf_id :'||to_char(ln_caprovtax_inf_id));
1597 hr_utility.trace('object_version_number :'||to_char(ln_ovn2));
1598
1599
1600 if csr_get_caprovtax_id%FOUND then
1601 l_pay_ca_provtax_rule_rec.EMP_PROVINCE_TAX_INF_ID := ln_caprovtax_inf_id;
1602 l_pay_ca_provtax_rule_rec.OBJECT_VERSION_NUMBER := ln_ovn2;
1603
1604 close csr_get_caprovtax_id;
1605 HR_DataPump(
1606 p_dp_mode => 'UPDATE'
1607 ,p_effective_date => p_effective_date
1608 ,p_spreadsheet_identifier => p_spreadsheet_identifier
1609 );
1610 else
1611 hr_utility.trace('province record not found ');
1612 l_pay_ca_provtax_rule_rec.ASSIGNMENT_ID := ln_assignment_id;
1613
1614 close csr_get_caprovtax_id;
1615 HR_DataPump(
1616 p_dp_mode => 'INSERT'
1617 ,p_effective_date => p_effective_date
1618 ,p_spreadsheet_identifier => p_spreadsheet_identifier
1619 );
1620 end if;
1621
1622 -- end of p_spreadsheet_identifier = 'PAYRICAPROVINCE'
1623
1624 end if; -- p_spreadsheet_identifier validation
1625
1626 end create_ac_emptaxrule;
1627
1628 end pay_ri_ac_tax_rule;