eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.HRDPP_CREATE_CA_EMP_FEDTAX_INF

Source


1 package body hrdpp_CREATE_CA_EMP_FEDTAX_INF as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2012/11/27 04:11:57
4  * Generated for API: PAY_CA_EMP_FEDTAX_INF_API.CREATE_CA_EMP_FEDTAX_INF
5  */
6 --
7 dh constant date := hr_api.g_date;
8 nh constant number := hr_api.g_number;
9 vh constant varchar2(64) := hr_api.g_varchar2;
10 c_sot constant date := to_date('01010001','DDMMYYYY');
11 cn constant varchar2(32) := '<NULL>';
12 dn constant date := null;
13 nn constant number := null;
14 vn constant varchar2(1) := null;
15 --
16 function dc(p in date) return varchar2 is
17 begin
18 if p<c_sot then
19  if p<>trunc(p) then
20   return to_char(p,'SYYYY/MM/DD HH24:MI:SS');
21  end if;
25 end if;
22  return to_char(p,'SYYYY/MM/DD');
23 elsif p<>trunc(p) then
24  return to_char(p,'YYYY/MM/DD HH24:MI:SS');
26 return to_char(p,'YYYY/MM/DD');
27 end dc;
28 function d(p in varchar2) return date is
29 begin
30 if length(p)=10 then
31 return to_date(p,'YYYY/MM/DD');
32 elsif length(p)=19 then
33 return to_date(p,'YYYY/MM/DD HH24:MI:SS');
34 elsif length(p)=11 then
35 return to_date(p,'SYYYY/MM/DD');
36 elsif length(p)=20 then
37 return to_date(p,'SYYYY/MM/DD HH24:MI:SS');
38 end if;
39 -- Try default format as last resort.
40 return to_date(p,'YYYY/MM/DD');
41 end d;
42 function n(p in varchar2) return number is
43 begin
44 return to_number(p);
45 end n;
46 function dd(p in date,i in varchar2)
47 return varchar2 is
48 begin
49 if upper(i) = 'N' then return dc(p);
50 else return cn; end if;
51 end dd;
52 function nd(p in number,i in varchar2)
53 return varchar2 is
54 begin
55 if upper(i) = 'N' then return to_char(p);
56 else return cn; end if;
57 end nd;
58 --
59 procedure iuk
60 (p_batch_line_id  in number,
61 p_user_key_value in varchar2,
62 p_unique_key_id  in number)
63 is
64 begin
65 hr_data_pump.entry('ins_user_key');
66 insert into hr_pump_batch_line_user_keys
67 (user_key_id, batch_line_id,user_key_value,unique_key_id)
68 values
69 (hr_pump_batch_line_user_keys_s.nextval,
70 p_batch_line_id,
71 p_user_key_value,
72 p_unique_key_id);
73 hr_data_pump.exit('ins_user_key');
74 end iuk;
75 --
76 procedure insert_batch_lines
77 (p_batch_id      in number
78 ,p_data_pump_batch_line_id in number default null
79 ,p_data_pump_business_grp_name in varchar2 default null
80 ,p_user_sequence in number default null
81 ,p_link_value    in number default null
82 ,P_EMP_FED_TAX_INF_USER_KEY in varchar2
83 ,P_LEGISLATION_CODE in varchar2 default null
84 ,P_ASSIGNMENT_ID in number default null
85 ,P_EMPLOYMENT_PROVINCE in varchar2 default null
86 ,P_TAX_CREDIT_AMOUNT in number default null
87 ,P_CLAIM_CODE in varchar2 default null
88 ,P_BASIC_EXEMPTION_FLAG in varchar2 default null
89 ,P_ADDITIONAL_TAX in number default null
90 ,P_ANNUAL_DEDN in number default null
91 ,P_TOTAL_EXPENSE_BY_COMMISSION in number default null
92 ,P_TOTAL_REMNRTN_BY_COMMISSION in number default null
93 ,P_PRESCRIBED_ZONE_DEDN_AMT in number default null
94 ,P_OTHER_FEDTAX_CREDITS in varchar2 default null
95 ,P_CPP_QPP_EXEMPT_FLAG in varchar2 default null
96 ,P_FED_EXEMPT_FLAG in varchar2 default null
97 ,P_EI_EXEMPT_FLAG in varchar2 default null
98 ,P_TAX_CALC_METHOD in varchar2 default null
99 ,P_FED_OVERRIDE_AMOUNT in number default null
100 ,P_FED_OVERRIDE_RATE in number default null
101 ,P_CA_TAX_INFORMATION_CATEGORY in varchar2 default null
102 ,P_CA_TAX_INFORMATION1 in varchar2 default null
103 ,P_CA_TAX_INFORMATION2 in varchar2 default null
104 ,P_CA_TAX_INFORMATION3 in varchar2 default null
105 ,P_CA_TAX_INFORMATION4 in varchar2 default null
106 ,P_CA_TAX_INFORMATION5 in varchar2 default null
107 ,P_CA_TAX_INFORMATION6 in varchar2 default null
108 ,P_CA_TAX_INFORMATION7 in varchar2 default null
109 ,P_CA_TAX_INFORMATION8 in varchar2 default null
110 ,P_CA_TAX_INFORMATION9 in varchar2 default null
111 ,P_CA_TAX_INFORMATION10 in varchar2 default null
112 ,P_CA_TAX_INFORMATION11 in varchar2 default null
113 ,P_CA_TAX_INFORMATION12 in varchar2 default null
114 ,P_CA_TAX_INFORMATION13 in varchar2 default null
115 ,P_CA_TAX_INFORMATION14 in varchar2 default null
116 ,P_CA_TAX_INFORMATION15 in varchar2 default null
117 ,P_CA_TAX_INFORMATION16 in varchar2 default null
118 ,P_CA_TAX_INFORMATION17 in varchar2 default null
119 ,P_CA_TAX_INFORMATION18 in varchar2 default null
120 ,P_CA_TAX_INFORMATION19 in varchar2 default null
121 ,P_CA_TAX_INFORMATION20 in varchar2 default null
122 ,P_CA_TAX_INFORMATION21 in varchar2 default null
123 ,P_CA_TAX_INFORMATION22 in varchar2 default null
124 ,P_CA_TAX_INFORMATION23 in varchar2 default null
125 ,P_CA_TAX_INFORMATION24 in varchar2 default null
126 ,P_CA_TAX_INFORMATION25 in varchar2 default null
127 ,P_CA_TAX_INFORMATION26 in varchar2 default null
128 ,P_CA_TAX_INFORMATION27 in varchar2 default null
129 ,P_CA_TAX_INFORMATION28 in varchar2 default null
130 ,P_CA_TAX_INFORMATION29 in varchar2 default null
131 ,P_CA_TAX_INFORMATION30 in varchar2 default null
132 ,P_FED_LSF_AMOUNT in number default null
133 ,P_CPP_ELECTION_DATE in date default null
134 ,P_CPP_REVOCATION_DATE in date default null
135 ,P_EFFECTIVE_DATE in date) is
136 blid number := p_data_pump_batch_line_id;
137 begin
138 if blid is not null then
139 delete from hr_pump_batch_lines where batch_line_id = blid;
140 delete from hr_pump_batch_exceptions
141 where source_type = 'BATCH_LINE' and source_id = blid;
142 end if;
143 insert into hr_pump_batch_lines
144 (batch_id
145 ,batch_line_id
146 ,business_group_name
147 ,api_module_id
148 ,line_status
149 ,user_sequence
150 ,link_value
151 ,pval001
152 ,pval004
153 ,pval005
154 ,pval006
155 ,pval007
156 ,pval008
157 ,pval009
158 ,pval010
159 ,pval011
160 ,pval012
161 ,pval013
162 ,pval014
163 ,pval015
164 ,pval016
165 ,pval017
166 ,pval018
167 ,pval019
168 ,pval020
169 ,pval021
170 ,pval022
171 ,pval023
172 ,pval024
173 ,pval025
174 ,pval026
175 ,pval027
176 ,pval028
177 ,pval029
178 ,pval030
179 ,pval031
180 ,pval032
181 ,pval033
182 ,pval034
183 ,pval035
184 ,pval036
185 ,pval037
186 ,pval038
187 ,pval039
188 ,pval040
189 ,pval041
190 ,pval042
191 ,pval043
195 ,pval047
192 ,pval044
193 ,pval045
194 ,pval046
196 ,pval048
197 ,pval049
198 ,pval050
199 ,pval051
200 ,pval052
201 ,pval054
202 ,pval055
203 ,pval056
204 ,pval057)
205 values
206 (p_batch_id
207 ,nvl(blid,hr_pump_batch_lines_s.nextval)
208 ,p_data_pump_business_grp_name
209 ,1153
210 ,'U'
211 ,p_user_sequence
212 ,p_link_value
213 ,P_EMP_FED_TAX_INF_USER_KEY
214 ,P_LEGISLATION_CODE
215 ,P_ASSIGNMENT_ID
216 ,P_EMPLOYMENT_PROVINCE
217 ,P_TAX_CREDIT_AMOUNT
218 ,P_CLAIM_CODE
219 ,P_BASIC_EXEMPTION_FLAG
220 ,P_ADDITIONAL_TAX
221 ,P_ANNUAL_DEDN
222 ,P_TOTAL_EXPENSE_BY_COMMISSION
223 ,P_TOTAL_REMNRTN_BY_COMMISSION
224 ,P_PRESCRIBED_ZONE_DEDN_AMT
225 ,P_OTHER_FEDTAX_CREDITS
226 ,P_CPP_QPP_EXEMPT_FLAG
227 ,P_FED_EXEMPT_FLAG
228 ,P_EI_EXEMPT_FLAG
229 ,P_TAX_CALC_METHOD
230 ,P_FED_OVERRIDE_AMOUNT
231 ,P_FED_OVERRIDE_RATE
232 ,P_CA_TAX_INFORMATION_CATEGORY
233 ,P_CA_TAX_INFORMATION1
234 ,P_CA_TAX_INFORMATION2
235 ,P_CA_TAX_INFORMATION3
236 ,P_CA_TAX_INFORMATION4
237 ,P_CA_TAX_INFORMATION5
238 ,P_CA_TAX_INFORMATION6
239 ,P_CA_TAX_INFORMATION7
240 ,P_CA_TAX_INFORMATION8
241 ,P_CA_TAX_INFORMATION9
242 ,P_CA_TAX_INFORMATION10
243 ,P_CA_TAX_INFORMATION11
244 ,P_CA_TAX_INFORMATION12
245 ,P_CA_TAX_INFORMATION13
246 ,P_CA_TAX_INFORMATION14
247 ,P_CA_TAX_INFORMATION15
248 ,P_CA_TAX_INFORMATION16
249 ,P_CA_TAX_INFORMATION17
250 ,P_CA_TAX_INFORMATION18
251 ,P_CA_TAX_INFORMATION19
252 ,P_CA_TAX_INFORMATION20
253 ,P_CA_TAX_INFORMATION21
254 ,P_CA_TAX_INFORMATION22
255 ,P_CA_TAX_INFORMATION23
256 ,P_CA_TAX_INFORMATION24
257 ,P_CA_TAX_INFORMATION25
258 ,P_CA_TAX_INFORMATION26
259 ,P_CA_TAX_INFORMATION27
260 ,P_CA_TAX_INFORMATION28
261 ,P_CA_TAX_INFORMATION29
262 ,P_CA_TAX_INFORMATION30
263 ,P_FED_LSF_AMOUNT
264 ,dc(P_CPP_ELECTION_DATE)
265 ,dc(P_CPP_REVOCATION_DATE)
266 ,dc(P_EFFECTIVE_DATE));
267 end insert_batch_lines;
268 --
269 procedure call
270 (p_business_group_id in number,
271 p_batch_line_id     in number) is
272 cursor cr is
273 select l.rowid myrowid,
274 l.pval001 p1,
275 decode(l.pval002,cn,dn,d(l.pval002)) p2,
276 decode(l.pval003,cn,dn,d(l.pval003)) p3,
277 decode(l.pval004,cn,vn,vn,vn,l.pval004) p4,
278 l.pval004 d4,
279 decode(l.pval005,cn,nn,vn,nn,n(l.pval005)) p5,
280 l.pval005 d5,
281 decode(l.pval006,cn,vn,vn,vn,l.pval006) p6,
282 l.pval006 d6,
283 decode(l.pval007,cn,nn,vn,nn,n(l.pval007)) p7,
284 l.pval007 d7,
285 decode(l.pval008,cn,vn,vn,vn,l.pval008) p8,
286 l.pval008 d8,
287 decode(l.pval009,cn,vn,vn,vn,l.pval009) p9,
288 l.pval009 d9,
289 decode(l.pval010,cn,nn,vn,nn,n(l.pval010)) p10,
290 l.pval010 d10,
291 decode(l.pval011,cn,nn,vn,nn,n(l.pval011)) p11,
292 l.pval011 d11,
293 decode(l.pval012,cn,nn,vn,nn,n(l.pval012)) p12,
294 l.pval012 d12,
295 decode(l.pval013,cn,nn,vn,nn,n(l.pval013)) p13,
296 l.pval013 d13,
297 decode(l.pval014,cn,nn,vn,nn,n(l.pval014)) p14,
298 l.pval014 d14,
299 decode(l.pval015,cn,vn,vn,vn,l.pval015) p15,
300 l.pval015 d15,
301 decode(l.pval016,cn,vn,vn,vn,
302  hr_pump_get.gl(l.pval016,'YES_NO',d(l.pval057),vn)) p16,
303 l.pval016 d16,
304 decode(l.pval017,cn,vn,vn,vn,
305  hr_pump_get.gl(l.pval017,'YES_NO',d(l.pval057),vn)) p17,
306 l.pval017 d17,
307 decode(l.pval018,cn,vn,vn,vn,
308  hr_pump_get.gl(l.pval018,'YES_NO',d(l.pval057),vn)) p18,
309 l.pval018 d18,
310 decode(l.pval019,cn,vn,vn,vn,l.pval019) p19,
311 l.pval019 d19,
312 decode(l.pval020,cn,nn,vn,nn,n(l.pval020)) p20,
313 l.pval020 d20,
314 decode(l.pval021,cn,nn,vn,nn,n(l.pval021)) p21,
315 l.pval021 d21,
316 decode(l.pval022,cn,vn,vn,vn,l.pval022) p22,
317 l.pval022 d22,
318 decode(l.pval023,cn,vn,vn,vn,l.pval023) p23,
319 l.pval023 d23,
320 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
321 l.pval024 d24,
322 decode(l.pval025,cn,vn,vn,vn,l.pval025) p25,
323 l.pval025 d25,
324 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
325 l.pval026 d26,
326 decode(l.pval027,cn,vn,vn,vn,l.pval027) p27,
327 l.pval027 d27,
328 decode(l.pval028,cn,vn,vn,vn,l.pval028) p28,
329 l.pval028 d28,
330 decode(l.pval029,cn,vn,vn,vn,l.pval029) p29,
331 l.pval029 d29,
332 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
333 l.pval030 d30,
334 decode(l.pval031,cn,vn,vn,vn,l.pval031) p31,
335 l.pval031 d31,
336 decode(l.pval032,cn,vn,vn,vn,l.pval032) p32,
337 l.pval032 d32,
338 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
339 l.pval033 d33,
340 decode(l.pval034,cn,vn,vn,vn,l.pval034) p34,
341 l.pval034 d34,
342 decode(l.pval035,cn,vn,vn,vn,l.pval035) p35,
343 l.pval035 d35,
344 decode(l.pval036,cn,vn,vn,vn,l.pval036) p36,
345 l.pval036 d36,
346 decode(l.pval037,cn,vn,vn,vn,l.pval037) p37,
347 l.pval037 d37,
348 decode(l.pval038,cn,vn,vn,vn,l.pval038) p38,
349 l.pval038 d38,
350 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
351 l.pval039 d39,
352 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
353 l.pval040 d40,
354 decode(l.pval041,cn,vn,vn,vn,l.pval041) p41,
355 l.pval041 d41,
356 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
357 l.pval042 d42,
358 decode(l.pval043,cn,vn,vn,vn,l.pval043) p43,
359 l.pval043 d43,
360 decode(l.pval044,cn,vn,vn,vn,l.pval044) p44,
361 l.pval044 d44,
362 decode(l.pval045,cn,vn,vn,vn,l.pval045) p45,
363 l.pval045 d45,
364 decode(l.pval046,cn,vn,vn,vn,l.pval046) p46,
365 l.pval046 d46,
366 decode(l.pval047,cn,vn,vn,vn,l.pval047) p47,
367 l.pval047 d47,
368 decode(l.pval048,cn,vn,vn,vn,l.pval048) p48,
369 l.pval048 d48,
370 decode(l.pval049,cn,vn,vn,vn,l.pval049) p49,
371 l.pval049 d49,
372 decode(l.pval050,cn,vn,vn,vn,l.pval050) p50,
373 l.pval050 d50,
374 decode(l.pval051,cn,vn,vn,vn,l.pval051) p51,
375 l.pval051 d51,
376 decode(l.pval052,cn,vn,vn,vn,l.pval052) p52,
377 l.pval052 d52,
378 l.pval053 p53,
379 decode(l.pval054,cn,nn,vn,nn,n(l.pval054)) p54,
380 l.pval054 d54,
381 decode(l.pval055,cn,dn,vn,dn,d(l.pval055)) p55,
382 l.pval055 d55,
383 decode(l.pval056,cn,dn,vn,dn,d(l.pval056)) p56,
384 l.pval056 d56,
385 decode(l.pval057,cn,dn,d(l.pval057)) p57
386 from hr_pump_batch_lines l
387 where l.batch_line_id = p_batch_line_id;
388 --
389 c cr%rowtype;
390 l_validate boolean := false;
391 L_EMP_FED_TAX_INF_ID number;
392 --
393 begin
394 hr_data_pump.entry('call');
395 open cr;
396 fetch cr into c;
397 if cr%notfound then
398 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
399 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
400 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
401 hr_utility.set_message_token('VALUE',p_batch_line_id);
402 hr_utility.raise_error;
403 end if;
404 --
405 hr_data_pump.api_trc_on;
406 PAY_CA_EMP_FEDTAX_INF_API.CREATE_CA_EMP_FEDTAX_INF
407 (p_validate => l_validate
408 ,P_EMP_FED_TAX_INF_ID => L_EMP_FED_TAX_INF_ID
409 ,P_EFFECTIVE_START_DATE => c.p2
410 ,P_EFFECTIVE_END_DATE => c.p3
411 ,P_LEGISLATION_CODE => c.p4
412 ,P_ASSIGNMENT_ID => c.p5
413 ,p_business_group_id => p_business_group_id
414 ,P_EMPLOYMENT_PROVINCE => c.p6
415 ,P_TAX_CREDIT_AMOUNT => c.p7
416 ,P_CLAIM_CODE => c.p8
417 ,P_BASIC_EXEMPTION_FLAG => c.p9
418 ,P_ADDITIONAL_TAX => c.p10
419 ,P_ANNUAL_DEDN => c.p11
420 ,P_TOTAL_EXPENSE_BY_COMMISSION => c.p12
421 ,P_TOTAL_REMNRTN_BY_COMMISSION => c.p13
422 ,P_PRESCRIBED_ZONE_DEDN_AMT => c.p14
423 ,P_OTHER_FEDTAX_CREDITS => c.p15
424 ,P_CPP_QPP_EXEMPT_FLAG => c.p16
425 ,P_FED_EXEMPT_FLAG => c.p17
426 ,P_EI_EXEMPT_FLAG => c.p18
427 ,P_TAX_CALC_METHOD => c.p19
428 ,P_FED_OVERRIDE_AMOUNT => c.p20
429 ,P_FED_OVERRIDE_RATE => c.p21
430 ,P_CA_TAX_INFORMATION_CATEGORY => c.p22
431 ,P_CA_TAX_INFORMATION1 => c.p23
432 ,P_CA_TAX_INFORMATION2 => c.p24
433 ,P_CA_TAX_INFORMATION3 => c.p25
434 ,P_CA_TAX_INFORMATION4 => c.p26
435 ,P_CA_TAX_INFORMATION5 => c.p27
436 ,P_CA_TAX_INFORMATION6 => c.p28
437 ,P_CA_TAX_INFORMATION7 => c.p29
438 ,P_CA_TAX_INFORMATION8 => c.p30
439 ,P_CA_TAX_INFORMATION9 => c.p31
440 ,P_CA_TAX_INFORMATION10 => c.p32
441 ,P_CA_TAX_INFORMATION11 => c.p33
442 ,P_CA_TAX_INFORMATION12 => c.p34
443 ,P_CA_TAX_INFORMATION13 => c.p35
444 ,P_CA_TAX_INFORMATION14 => c.p36
445 ,P_CA_TAX_INFORMATION15 => c.p37
446 ,P_CA_TAX_INFORMATION16 => c.p38
447 ,P_CA_TAX_INFORMATION17 => c.p39
448 ,P_CA_TAX_INFORMATION18 => c.p40
449 ,P_CA_TAX_INFORMATION19 => c.p41
450 ,P_CA_TAX_INFORMATION20 => c.p42
451 ,P_CA_TAX_INFORMATION21 => c.p43
452 ,P_CA_TAX_INFORMATION22 => c.p44
453 ,P_CA_TAX_INFORMATION23 => c.p45
454 ,P_CA_TAX_INFORMATION24 => c.p46
455 ,P_CA_TAX_INFORMATION25 => c.p47
456 ,P_CA_TAX_INFORMATION26 => c.p48
457 ,P_CA_TAX_INFORMATION27 => c.p49
458 ,P_CA_TAX_INFORMATION28 => c.p50
459 ,P_CA_TAX_INFORMATION29 => c.p51
460 ,P_CA_TAX_INFORMATION30 => c.p52
461 ,P_OBJECT_VERSION_NUMBER => c.p53
462 ,P_FED_LSF_AMOUNT => c.p54
463 ,P_CPP_ELECTION_DATE => c.p55
464 ,P_CPP_REVOCATION_DATE => c.p56
465 ,P_EFFECTIVE_DATE => c.p57);
466 hr_data_pump.api_trc_off;
467 --
468 iuk(p_batch_line_id,c.p1,L_EMP_FED_TAX_INF_ID);
469 --
470 update hr_pump_batch_lines l set
471 l.pval001 = decode(c.p1,null,cn,c.p1),
472 l.pval002 = decode(c.p2,null,cn,dc(c.p2)),
473 l.pval003 = decode(c.p3,null,cn,dc(c.p3)),
474 l.pval053 = decode(c.p53,null,cn,c.p53)
475 where l.rowid = c.myrowid;
476 --
477 close cr;
478 --
479 hr_data_pump.exit('call');
480 exception
481  when hr_multi_message.error_message_exist then
482    if cr%isopen then
483     close cr;
484    end if;
485    hr_pump_utils.set_multi_msg_error_flag(true);
486  when others then
487  if cr%isopen then
488   close cr;
489  end if;
490  raise;
491 end call;
492 end hrdpp_CREATE_CA_EMP_FEDTAX_INF;