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: 2007/01/03 23:01:37
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;
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');
25 end if;
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_EFFECTIVE_DATE in date) is
134 blid number := p_data_pump_batch_line_id;
135 begin
136 if blid is not null then
137 delete from hr_pump_batch_lines where batch_line_id = blid;
138 delete from hr_pump_batch_exceptions
139 where source_type = 'BATCH_LINE' and source_id = blid;
140 end if;
141 insert into hr_pump_batch_lines
142 (batch_id
143 ,batch_line_id
144 ,business_group_name
145 ,api_module_id
146 ,line_status
147 ,user_sequence
148 ,link_value
149 ,pval001
150 ,pval004
151 ,pval005
152 ,pval006
153 ,pval007
154 ,pval008
155 ,pval009
156 ,pval010
157 ,pval011
158 ,pval012
159 ,pval013
160 ,pval014
161 ,pval015
162 ,pval016
163 ,pval017
164 ,pval018
165 ,pval019
166 ,pval020
167 ,pval021
168 ,pval022
169 ,pval023
170 ,pval024
171 ,pval025
172 ,pval026
173 ,pval027
174 ,pval028
175 ,pval029
176 ,pval030
177 ,pval031
178 ,pval032
179 ,pval033
180 ,pval034
181 ,pval035
182 ,pval036
183 ,pval037
184 ,pval038
185 ,pval039
186 ,pval040
187 ,pval041
188 ,pval042
189 ,pval043
190 ,pval044
191 ,pval045
192 ,pval046
193 ,pval047
194 ,pval048
195 ,pval049
196 ,pval050
197 ,pval051
198 ,pval052
199 ,pval054
200 ,pval055)
201 values
202 (p_batch_id
203 ,nvl(blid,hr_pump_batch_lines_s.nextval)
204 ,p_data_pump_business_grp_name
205 ,1153
206 ,'U'
207 ,p_user_sequence
208 ,p_link_value
209 ,P_EMP_FED_TAX_INF_USER_KEY
210 ,P_LEGISLATION_CODE
211 ,P_ASSIGNMENT_ID
212 ,P_EMPLOYMENT_PROVINCE
213 ,P_TAX_CREDIT_AMOUNT
214 ,P_CLAIM_CODE
215 ,P_BASIC_EXEMPTION_FLAG
216 ,P_ADDITIONAL_TAX
217 ,P_ANNUAL_DEDN
218 ,P_TOTAL_EXPENSE_BY_COMMISSION
219 ,P_TOTAL_REMNRTN_BY_COMMISSION
220 ,P_PRESCRIBED_ZONE_DEDN_AMT
221 ,P_OTHER_FEDTAX_CREDITS
222 ,P_CPP_QPP_EXEMPT_FLAG
223 ,P_FED_EXEMPT_FLAG
224 ,P_EI_EXEMPT_FLAG
225 ,P_TAX_CALC_METHOD
226 ,P_FED_OVERRIDE_AMOUNT
227 ,P_FED_OVERRIDE_RATE
228 ,P_CA_TAX_INFORMATION_CATEGORY
229 ,P_CA_TAX_INFORMATION1
230 ,P_CA_TAX_INFORMATION2
231 ,P_CA_TAX_INFORMATION3
232 ,P_CA_TAX_INFORMATION4
233 ,P_CA_TAX_INFORMATION5
234 ,P_CA_TAX_INFORMATION6
235 ,P_CA_TAX_INFORMATION7
236 ,P_CA_TAX_INFORMATION8
237 ,P_CA_TAX_INFORMATION9
238 ,P_CA_TAX_INFORMATION10
239 ,P_CA_TAX_INFORMATION11
240 ,P_CA_TAX_INFORMATION12
241 ,P_CA_TAX_INFORMATION13
242 ,P_CA_TAX_INFORMATION14
243 ,P_CA_TAX_INFORMATION15
244 ,P_CA_TAX_INFORMATION16
245 ,P_CA_TAX_INFORMATION17
246 ,P_CA_TAX_INFORMATION18
247 ,P_CA_TAX_INFORMATION19
248 ,P_CA_TAX_INFORMATION20
249 ,P_CA_TAX_INFORMATION21
250 ,P_CA_TAX_INFORMATION22
251 ,P_CA_TAX_INFORMATION23
252 ,P_CA_TAX_INFORMATION24
253 ,P_CA_TAX_INFORMATION25
254 ,P_CA_TAX_INFORMATION26
255 ,P_CA_TAX_INFORMATION27
256 ,P_CA_TAX_INFORMATION28
257 ,P_CA_TAX_INFORMATION29
258 ,P_CA_TAX_INFORMATION30
259 ,P_FED_LSF_AMOUNT
260 ,dc(P_EFFECTIVE_DATE));
261 end insert_batch_lines;
262 --
263 procedure call
264 (p_business_group_id in number,
265 p_batch_line_id     in number) is
266 cursor cr is
267 select l.rowid myrowid,
268 l.pval001 p1,
269 decode(l.pval002,cn,dn,d(l.pval002)) p2,
270 decode(l.pval003,cn,dn,d(l.pval003)) p3,
271 decode(l.pval004,cn,vn,vn,vn,l.pval004) p4,
272 l.pval004 d4,
273 decode(l.pval005,cn,nn,vn,nn,n(l.pval005)) p5,
274 l.pval005 d5,
275 decode(l.pval006,cn,vn,vn,vn,l.pval006) p6,
276 l.pval006 d6,
277 decode(l.pval007,cn,nn,vn,nn,n(l.pval007)) p7,
278 l.pval007 d7,
279 decode(l.pval008,cn,vn,vn,vn,l.pval008) p8,
280 l.pval008 d8,
281 decode(l.pval009,cn,vn,vn,vn,l.pval009) p9,
282 l.pval009 d9,
283 decode(l.pval010,cn,nn,vn,nn,n(l.pval010)) p10,
284 l.pval010 d10,
285 decode(l.pval011,cn,nn,vn,nn,n(l.pval011)) p11,
286 l.pval011 d11,
287 decode(l.pval012,cn,nn,vn,nn,n(l.pval012)) p12,
288 l.pval012 d12,
289 decode(l.pval013,cn,nn,vn,nn,n(l.pval013)) p13,
290 l.pval013 d13,
291 decode(l.pval014,cn,nn,vn,nn,n(l.pval014)) p14,
292 l.pval014 d14,
293 decode(l.pval015,cn,vn,vn,vn,l.pval015) p15,
294 l.pval015 d15,
295 decode(l.pval016,cn,vn,vn,vn,
296  hr_pump_get.gl(l.pval016,'YES_NO',d(l.pval055),vn)) p16,
297 l.pval016 d16,
298 decode(l.pval017,cn,vn,vn,vn,
299  hr_pump_get.gl(l.pval017,'YES_NO',d(l.pval055),vn)) p17,
300 l.pval017 d17,
301 decode(l.pval018,cn,vn,vn,vn,
302  hr_pump_get.gl(l.pval018,'YES_NO',d(l.pval055),vn)) p18,
303 l.pval018 d18,
304 decode(l.pval019,cn,vn,vn,vn,l.pval019) p19,
305 l.pval019 d19,
306 decode(l.pval020,cn,nn,vn,nn,n(l.pval020)) p20,
307 l.pval020 d20,
308 decode(l.pval021,cn,nn,vn,nn,n(l.pval021)) p21,
309 l.pval021 d21,
310 decode(l.pval022,cn,vn,vn,vn,l.pval022) p22,
311 l.pval022 d22,
312 decode(l.pval023,cn,vn,vn,vn,l.pval023) p23,
313 l.pval023 d23,
314 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
315 l.pval024 d24,
316 decode(l.pval025,cn,vn,vn,vn,l.pval025) p25,
317 l.pval025 d25,
318 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
319 l.pval026 d26,
320 decode(l.pval027,cn,vn,vn,vn,l.pval027) p27,
321 l.pval027 d27,
322 decode(l.pval028,cn,vn,vn,vn,l.pval028) p28,
323 l.pval028 d28,
324 decode(l.pval029,cn,vn,vn,vn,l.pval029) p29,
325 l.pval029 d29,
326 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
327 l.pval030 d30,
328 decode(l.pval031,cn,vn,vn,vn,l.pval031) p31,
329 l.pval031 d31,
330 decode(l.pval032,cn,vn,vn,vn,l.pval032) p32,
331 l.pval032 d32,
332 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
333 l.pval033 d33,
334 decode(l.pval034,cn,vn,vn,vn,l.pval034) p34,
335 l.pval034 d34,
336 decode(l.pval035,cn,vn,vn,vn,l.pval035) p35,
337 l.pval035 d35,
338 decode(l.pval036,cn,vn,vn,vn,l.pval036) p36,
339 l.pval036 d36,
340 decode(l.pval037,cn,vn,vn,vn,l.pval037) p37,
341 l.pval037 d37,
342 decode(l.pval038,cn,vn,vn,vn,l.pval038) p38,
343 l.pval038 d38,
344 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
345 l.pval039 d39,
346 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
347 l.pval040 d40,
348 decode(l.pval041,cn,vn,vn,vn,l.pval041) p41,
349 l.pval041 d41,
350 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
351 l.pval042 d42,
352 decode(l.pval043,cn,vn,vn,vn,l.pval043) p43,
353 l.pval043 d43,
354 decode(l.pval044,cn,vn,vn,vn,l.pval044) p44,
355 l.pval044 d44,
356 decode(l.pval045,cn,vn,vn,vn,l.pval045) p45,
357 l.pval045 d45,
358 decode(l.pval046,cn,vn,vn,vn,l.pval046) p46,
359 l.pval046 d46,
360 decode(l.pval047,cn,vn,vn,vn,l.pval047) p47,
361 l.pval047 d47,
362 decode(l.pval048,cn,vn,vn,vn,l.pval048) p48,
363 l.pval048 d48,
364 decode(l.pval049,cn,vn,vn,vn,l.pval049) p49,
365 l.pval049 d49,
366 decode(l.pval050,cn,vn,vn,vn,l.pval050) p50,
367 l.pval050 d50,
368 decode(l.pval051,cn,vn,vn,vn,l.pval051) p51,
372 l.pval053 p53,
369 l.pval051 d51,
370 decode(l.pval052,cn,vn,vn,vn,l.pval052) p52,
371 l.pval052 d52,
373 decode(l.pval054,cn,nn,vn,nn,n(l.pval054)) p54,
374 l.pval054 d54,
375 decode(l.pval055,cn,dn,d(l.pval055)) p55
376 from hr_pump_batch_lines l
377 where l.batch_line_id = p_batch_line_id;
378 --
379 c cr%rowtype;
380 l_validate boolean := false;
381 L_EMP_FED_TAX_INF_ID number;
382 --
383 begin
384 hr_data_pump.entry('call');
385 open cr;
386 fetch cr into c;
387 if cr%notfound then
388 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
389 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
390 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
391 hr_utility.set_message_token('VALUE',p_batch_line_id);
392 hr_utility.raise_error;
393 end if;
394 --
395 hr_data_pump.api_trc_on;
396 PAY_CA_EMP_FEDTAX_INF_API.CREATE_CA_EMP_FEDTAX_INF
397 (p_validate => l_validate
398 ,P_EMP_FED_TAX_INF_ID => L_EMP_FED_TAX_INF_ID
399 ,P_EFFECTIVE_START_DATE => c.p2
400 ,P_EFFECTIVE_END_DATE => c.p3
401 ,P_LEGISLATION_CODE => c.p4
402 ,P_ASSIGNMENT_ID => c.p5
403 ,p_business_group_id => p_business_group_id
404 ,P_EMPLOYMENT_PROVINCE => c.p6
405 ,P_TAX_CREDIT_AMOUNT => c.p7
406 ,P_CLAIM_CODE => c.p8
407 ,P_BASIC_EXEMPTION_FLAG => c.p9
408 ,P_ADDITIONAL_TAX => c.p10
409 ,P_ANNUAL_DEDN => c.p11
410 ,P_TOTAL_EXPENSE_BY_COMMISSION => c.p12
411 ,P_TOTAL_REMNRTN_BY_COMMISSION => c.p13
412 ,P_PRESCRIBED_ZONE_DEDN_AMT => c.p14
413 ,P_OTHER_FEDTAX_CREDITS => c.p15
414 ,P_CPP_QPP_EXEMPT_FLAG => c.p16
415 ,P_FED_EXEMPT_FLAG => c.p17
416 ,P_EI_EXEMPT_FLAG => c.p18
417 ,P_TAX_CALC_METHOD => c.p19
418 ,P_FED_OVERRIDE_AMOUNT => c.p20
419 ,P_FED_OVERRIDE_RATE => c.p21
420 ,P_CA_TAX_INFORMATION_CATEGORY => c.p22
421 ,P_CA_TAX_INFORMATION1 => c.p23
422 ,P_CA_TAX_INFORMATION2 => c.p24
423 ,P_CA_TAX_INFORMATION3 => c.p25
424 ,P_CA_TAX_INFORMATION4 => c.p26
425 ,P_CA_TAX_INFORMATION5 => c.p27
426 ,P_CA_TAX_INFORMATION6 => c.p28
427 ,P_CA_TAX_INFORMATION7 => c.p29
428 ,P_CA_TAX_INFORMATION8 => c.p30
429 ,P_CA_TAX_INFORMATION9 => c.p31
430 ,P_CA_TAX_INFORMATION10 => c.p32
431 ,P_CA_TAX_INFORMATION11 => c.p33
432 ,P_CA_TAX_INFORMATION12 => c.p34
433 ,P_CA_TAX_INFORMATION13 => c.p35
434 ,P_CA_TAX_INFORMATION14 => c.p36
435 ,P_CA_TAX_INFORMATION15 => c.p37
436 ,P_CA_TAX_INFORMATION16 => c.p38
437 ,P_CA_TAX_INFORMATION17 => c.p39
438 ,P_CA_TAX_INFORMATION18 => c.p40
439 ,P_CA_TAX_INFORMATION19 => c.p41
440 ,P_CA_TAX_INFORMATION20 => c.p42
441 ,P_CA_TAX_INFORMATION21 => c.p43
442 ,P_CA_TAX_INFORMATION22 => c.p44
443 ,P_CA_TAX_INFORMATION23 => c.p45
444 ,P_CA_TAX_INFORMATION24 => c.p46
445 ,P_CA_TAX_INFORMATION25 => c.p47
446 ,P_CA_TAX_INFORMATION26 => c.p48
447 ,P_CA_TAX_INFORMATION27 => c.p49
448 ,P_CA_TAX_INFORMATION28 => c.p50
449 ,P_CA_TAX_INFORMATION29 => c.p51
450 ,P_CA_TAX_INFORMATION30 => c.p52
451 ,P_OBJECT_VERSION_NUMBER => c.p53
452 ,P_FED_LSF_AMOUNT => c.p54
453 ,P_EFFECTIVE_DATE => c.p55);
454 hr_data_pump.api_trc_off;
455 --
456 iuk(p_batch_line_id,c.p1,L_EMP_FED_TAX_INF_ID);
457 --
458 update hr_pump_batch_lines l set
459 l.pval001 = decode(c.p1,null,cn,c.p1),
460 l.pval002 = decode(c.p2,null,cn,dc(c.p2)),
461 l.pval003 = decode(c.p3,null,cn,dc(c.p3)),
462 l.pval053 = decode(c.p53,null,cn,c.p53)
463 where l.rowid = c.myrowid;
464 --
465 close cr;
466 --
467 hr_data_pump.exit('call');
468 exception
469  when hr_multi_message.error_message_exist then
470    if cr%isopen then
471     close cr;
472    end if;
473    hr_pump_utils.set_multi_msg_error_flag(true);
474  when others then
475  if cr%isopen then
476   close cr;
477  end if;
478  raise;
479 end call;
480 end hrdpp_CREATE_CA_EMP_FEDTAX_INF;