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.
[Home] [Help]
PACKAGE BODY: APPS.HRDPP_CREATE_LIFE_EVENT_REASON

Source


4  * Generated for API: ben_life_event_reason_api.CREATE_LIFE_EVENT_REASON
1 package body hrdpp_CREATE_LIFE_EVENT_REASON as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2012/11/27 04:11:04
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
21  end if;
18 if p<c_sot then
19  if p<>trunc(p) then
20   return to_char(p,'SYYYY/MM/DD HH24:MI:SS');
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_NAME in varchar2 default null
83 ,P_TYP_CD in varchar2 default null
84 ,P_LF_EVT_OPER_CD in varchar2 default null
85 ,P_SHORT_NAME in varchar2 default null
86 ,P_SHORT_CODE in varchar2 default null
87 ,P_PTNL_LER_TRTMT_CD in varchar2 default null
88 ,P_CK_RLTD_PER_ELIG_FLAG in varchar2 default null
89 ,P_LER_EVAL_RL in number default null
90 ,P_CM_APLY_FLAG in varchar2 default null
91 ,P_OVRIDG_LE_FLAG in varchar2 default null
92 ,P_QUALG_EVT_FLAG in varchar2 default null
93 ,P_WHN_TO_PRCS_CD in varchar2 default null
94 ,P_DESC_TXT in varchar2 default null
95 ,P_TMLNS_EVAL_CD in varchar2 default null
96 ,P_TMLNS_PERD_CD in varchar2 default null
97 ,P_TMLNS_DYS_NUM in number default null
98 ,P_TMLNS_PERD_RL in number default null
99 ,P_OCRD_DT_DET_CD in varchar2 default null
100 ,P_LER_STAT_CD in varchar2 default null
101 ,P_SLCTBL_SLF_SVC_CD in varchar2 default null
102 ,P_SS_PCP_DISP_CD in varchar2 default null
103 ,P_LER_ATTRIBUTE_CATEGORY in varchar2 default null
104 ,P_LER_ATTRIBUTE1 in varchar2 default null
105 ,P_LER_ATTRIBUTE2 in varchar2 default null
106 ,P_LER_ATTRIBUTE3 in varchar2 default null
107 ,P_LER_ATTRIBUTE4 in varchar2 default null
108 ,P_LER_ATTRIBUTE5 in varchar2 default null
109 ,P_LER_ATTRIBUTE6 in varchar2 default null
110 ,P_LER_ATTRIBUTE7 in varchar2 default null
111 ,P_LER_ATTRIBUTE8 in varchar2 default null
112 ,P_LER_ATTRIBUTE9 in varchar2 default null
113 ,P_LER_ATTRIBUTE10 in varchar2 default null
114 ,P_LER_ATTRIBUTE11 in varchar2 default null
115 ,P_LER_ATTRIBUTE12 in varchar2 default null
116 ,P_LER_ATTRIBUTE13 in varchar2 default null
117 ,P_LER_ATTRIBUTE14 in varchar2 default null
118 ,P_LER_ATTRIBUTE15 in varchar2 default null
119 ,P_LER_ATTRIBUTE16 in varchar2 default null
120 ,P_LER_ATTRIBUTE17 in varchar2 default null
121 ,P_LER_ATTRIBUTE18 in varchar2 default null
122 ,P_LER_ATTRIBUTE19 in varchar2 default null
123 ,P_LER_ATTRIBUTE20 in varchar2 default null
124 ,P_LER_ATTRIBUTE21 in varchar2 default null
125 ,P_LER_ATTRIBUTE22 in varchar2 default null
126 ,P_LER_ATTRIBUTE23 in varchar2 default null
127 ,P_LER_ATTRIBUTE24 in varchar2 default null
128 ,P_LER_ATTRIBUTE25 in varchar2 default null
129 ,P_LER_ATTRIBUTE26 in varchar2 default null
130 ,P_LER_ATTRIBUTE27 in varchar2 default null
131 ,P_LER_ATTRIBUTE28 in varchar2 default null
132 ,P_LER_ATTRIBUTE29 in varchar2 default null
133 ,P_LER_ATTRIBUTE30 in varchar2 default null
134 ,P_EFFECTIVE_DATE in date) is
135 blid number := p_data_pump_batch_line_id;
136 begin
137 if blid is not null then
138 delete from hr_pump_batch_lines where batch_line_id = blid;
139 delete from hr_pump_batch_exceptions
140 where source_type = 'BATCH_LINE' and source_id = blid;
141 end if;
142 insert into hr_pump_batch_lines
143 (batch_id
144 ,batch_line_id
145 ,business_group_name
146 ,api_module_id
147 ,line_status
148 ,user_sequence
149 ,link_value
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
190 ,pval044
187 ,pval041
188 ,pval042
189 ,pval043
191 ,pval045
192 ,pval046
193 ,pval047
194 ,pval048
195 ,pval049
196 ,pval050
197 ,pval051
198 ,pval052
199 ,pval053
200 ,pval054
201 ,pval055
202 ,pval057)
203 values
204 (p_batch_id
205 ,nvl(blid,hr_pump_batch_lines_s.nextval)
206 ,p_data_pump_business_grp_name
207 ,474
208 ,'U'
209 ,p_user_sequence
210 ,p_link_value
211 ,P_NAME
212 ,P_TYP_CD
213 ,P_LF_EVT_OPER_CD
214 ,P_SHORT_NAME
215 ,P_SHORT_CODE
216 ,P_PTNL_LER_TRTMT_CD
217 ,P_CK_RLTD_PER_ELIG_FLAG
218 ,P_LER_EVAL_RL
219 ,P_CM_APLY_FLAG
220 ,P_OVRIDG_LE_FLAG
221 ,P_QUALG_EVT_FLAG
222 ,P_WHN_TO_PRCS_CD
223 ,P_DESC_TXT
224 ,P_TMLNS_EVAL_CD
225 ,P_TMLNS_PERD_CD
226 ,P_TMLNS_DYS_NUM
227 ,P_TMLNS_PERD_RL
228 ,P_OCRD_DT_DET_CD
229 ,P_LER_STAT_CD
230 ,P_SLCTBL_SLF_SVC_CD
231 ,P_SS_PCP_DISP_CD
232 ,P_LER_ATTRIBUTE_CATEGORY
233 ,P_LER_ATTRIBUTE1
234 ,P_LER_ATTRIBUTE2
235 ,P_LER_ATTRIBUTE3
236 ,P_LER_ATTRIBUTE4
237 ,P_LER_ATTRIBUTE5
238 ,P_LER_ATTRIBUTE6
239 ,P_LER_ATTRIBUTE7
240 ,P_LER_ATTRIBUTE8
241 ,P_LER_ATTRIBUTE9
242 ,P_LER_ATTRIBUTE10
243 ,P_LER_ATTRIBUTE11
244 ,P_LER_ATTRIBUTE12
245 ,P_LER_ATTRIBUTE13
246 ,P_LER_ATTRIBUTE14
247 ,P_LER_ATTRIBUTE15
248 ,P_LER_ATTRIBUTE16
249 ,P_LER_ATTRIBUTE17
250 ,P_LER_ATTRIBUTE18
251 ,P_LER_ATTRIBUTE19
252 ,P_LER_ATTRIBUTE20
253 ,P_LER_ATTRIBUTE21
254 ,P_LER_ATTRIBUTE22
255 ,P_LER_ATTRIBUTE23
256 ,P_LER_ATTRIBUTE24
257 ,P_LER_ATTRIBUTE25
258 ,P_LER_ATTRIBUTE26
259 ,P_LER_ATTRIBUTE27
260 ,P_LER_ATTRIBUTE28
261 ,P_LER_ATTRIBUTE29
262 ,P_LER_ATTRIBUTE30
263 ,dc(P_EFFECTIVE_DATE));
264 end insert_batch_lines;
265 --
266 procedure call
267 (p_business_group_id in number,
268 p_batch_line_id     in number) is
269 cursor cr is
270 select l.rowid myrowid,
271 l.pval001 p1,
272 decode(l.pval002,cn,dn,d(l.pval002)) p2,
273 decode(l.pval003,cn,dn,d(l.pval003)) p3,
274 decode(l.pval004,cn,vn,vn,vn,l.pval004) p4,
275 l.pval004 d4,
276 decode(l.pval005,cn,vn,vn,vn,
277  hr_pump_get.gl(l.pval005,'BEN_LER_TYP',d(l.pval057),vn)) p5,
278 l.pval005 d5,
279 decode(l.pval006,cn,vn,vn,vn,l.pval006) p6,
280 l.pval006 d6,
281 decode(l.pval007,cn,vn,vn,vn,l.pval007) p7,
282 l.pval007 d7,
283 decode(l.pval008,cn,vn,vn,vn,l.pval008) p8,
284 l.pval008 d8,
285 decode(l.pval009,cn,vn,vn,vn,
286  hr_pump_get.gl(l.pval009,'BEN_PTNL_LER_TRTMT',d(l.pval057),vn)) p9,
287 l.pval009 d9,
288 decode(l.pval010,cn,vn,vn,vn,
289  hr_pump_get.gl(l.pval010,'YES_NO',d(l.pval057),vn)) 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,vn,vn,vn,
294  hr_pump_get.gl(l.pval012,'YES_NO',d(l.pval057),vn)) p12,
295 l.pval012 d12,
296 decode(l.pval013,cn,vn,vn,vn,
297  hr_pump_get.gl(l.pval013,'YES_NO',d(l.pval057),vn)) p13,
298 l.pval013 d13,
299 decode(l.pval014,cn,vn,vn,vn,
300  hr_pump_get.gl(l.pval014,'YES_NO',d(l.pval057),vn)) p14,
301 l.pval014 d14,
302 decode(l.pval015,cn,vn,vn,vn,
303  hr_pump_get.gl(l.pval015,'BEN_WHN_TO_PRCS_LER',d(l.pval057),vn)) p15,
304 l.pval015 d15,
305 decode(l.pval016,cn,vn,vn,vn,l.pval016) p16,
306 l.pval016 d16,
307 decode(l.pval017,cn,vn,vn,vn,
308  hr_pump_get.gl(l.pval017,'BEN_LER_TMLNS_EVAL',d(l.pval057),vn)) p17,
309 l.pval017 d17,
310 decode(l.pval018,cn,vn,vn,vn,
311  hr_pump_get.gl(l.pval018,'BEN_LER_TMLNS_PERD',d(l.pval057),vn)) p18,
312 l.pval018 d18,
313 decode(l.pval019,cn,nn,vn,nn,n(l.pval019)) p19,
314 l.pval019 d19,
315 decode(l.pval020,cn,nn,vn,nn,n(l.pval020)) p20,
316 l.pval020 d20,
317 decode(l.pval021,cn,vn,vn,vn,
318  hr_pump_get.gl(l.pval021,'BEN_OCRD_DT_DET',d(l.pval057),vn)) p21,
319 l.pval021 d21,
320 decode(l.pval022,cn,vn,vn,vn,l.pval022) p22,
321 l.pval022 d22,
322 decode(l.pval023,cn,vn,vn,vn,
323  hr_pump_get.gl(l.pval023,'BEN_SLCTBL_SLF_SVC_CD',d(l.pval057),vn)) p23,
324 l.pval023 d23,
325 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
326 l.pval024 d24,
327 decode(l.pval025,cn,vn,vn,vn,l.pval025) p25,
328 l.pval025 d25,
329 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
330 l.pval026 d26,
331 decode(l.pval027,cn,vn,vn,vn,l.pval027) p27,
332 l.pval027 d27,
333 decode(l.pval028,cn,vn,vn,vn,l.pval028) p28,
334 l.pval028 d28,
335 decode(l.pval029,cn,vn,vn,vn,l.pval029) p29,
336 l.pval029 d29,
337 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
338 l.pval030 d30,
339 decode(l.pval031,cn,vn,vn,vn,l.pval031) p31,
340 l.pval031 d31,
341 decode(l.pval032,cn,vn,vn,vn,l.pval032) p32,
342 l.pval032 d32,
343 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
344 l.pval033 d33,
345 decode(l.pval034,cn,vn,vn,vn,l.pval034) p34,
346 l.pval034 d34,
347 decode(l.pval035,cn,vn,vn,vn,l.pval035) p35,
348 l.pval035 d35,
349 decode(l.pval036,cn,vn,vn,vn,l.pval036) p36,
350 l.pval036 d36,
351 decode(l.pval037,cn,vn,vn,vn,l.pval037) p37,
352 l.pval037 d37,
353 decode(l.pval038,cn,vn,vn,vn,l.pval038) p38,
354 l.pval038 d38,
355 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
356 l.pval039 d39,
357 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
358 l.pval040 d40,
359 decode(l.pval041,cn,vn,vn,vn,l.pval041) p41,
360 l.pval041 d41,
361 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
362 l.pval042 d42,
363 decode(l.pval043,cn,vn,vn,vn,l.pval043) p43,
364 l.pval043 d43,
365 decode(l.pval044,cn,vn,vn,vn,l.pval044) p44,
366 l.pval044 d44,
367 decode(l.pval045,cn,vn,vn,vn,l.pval045) p45,
368 l.pval045 d45,
372 l.pval047 d47,
369 decode(l.pval046,cn,vn,vn,vn,l.pval046) p46,
370 l.pval046 d46,
371 decode(l.pval047,cn,vn,vn,vn,l.pval047) p47,
373 decode(l.pval048,cn,vn,vn,vn,l.pval048) p48,
374 l.pval048 d48,
375 decode(l.pval049,cn,vn,vn,vn,l.pval049) p49,
376 l.pval049 d49,
377 decode(l.pval050,cn,vn,vn,vn,l.pval050) p50,
378 l.pval050 d50,
379 decode(l.pval051,cn,vn,vn,vn,l.pval051) p51,
380 l.pval051 d51,
381 decode(l.pval052,cn,vn,vn,vn,l.pval052) p52,
382 l.pval052 d52,
383 decode(l.pval053,cn,vn,vn,vn,l.pval053) p53,
384 l.pval053 d53,
385 decode(l.pval054,cn,vn,vn,vn,l.pval054) p54,
386 l.pval054 d54,
387 decode(l.pval055,cn,vn,vn,vn,l.pval055) p55,
388 l.pval055 d55,
389 l.pval056 p56,
390 decode(l.pval057,cn,dn,d(l.pval057)) p57
391 from hr_pump_batch_lines l
392 where l.batch_line_id = p_batch_line_id;
393 --
394 c cr%rowtype;
395 l_validate boolean := false;
396 --
397 begin
398 hr_data_pump.entry('call');
399 open cr;
400 fetch cr into c;
401 if cr%notfound then
402 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
403 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
404 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
405 hr_utility.set_message_token('VALUE',p_batch_line_id);
406 hr_utility.raise_error;
407 end if;
408 --
409 hr_data_pump.api_trc_on;
410 ben_life_event_reason_api.CREATE_LIFE_EVENT_REASON
411 (p_validate => l_validate
412 ,P_LER_ID => c.p1
413 ,P_EFFECTIVE_START_DATE => c.p2
414 ,P_EFFECTIVE_END_DATE => c.p3
415 ,P_NAME => c.p4
416 ,p_business_group_id => p_business_group_id
417 ,P_TYP_CD => c.p5
418 ,P_LF_EVT_OPER_CD => c.p6
419 ,P_SHORT_NAME => c.p7
420 ,P_SHORT_CODE => c.p8
421 ,P_PTNL_LER_TRTMT_CD => c.p9
422 ,P_CK_RLTD_PER_ELIG_FLAG => c.p10
423 ,P_LER_EVAL_RL => c.p11
424 ,P_CM_APLY_FLAG => c.p12
425 ,P_OVRIDG_LE_FLAG => c.p13
426 ,P_QUALG_EVT_FLAG => c.p14
427 ,P_WHN_TO_PRCS_CD => c.p15
428 ,P_DESC_TXT => c.p16
429 ,P_TMLNS_EVAL_CD => c.p17
430 ,P_TMLNS_PERD_CD => c.p18
431 ,P_TMLNS_DYS_NUM => c.p19
432 ,P_TMLNS_PERD_RL => c.p20
433 ,P_OCRD_DT_DET_CD => c.p21
434 ,P_LER_STAT_CD => c.p22
435 ,P_SLCTBL_SLF_SVC_CD => c.p23
436 ,P_SS_PCP_DISP_CD => c.p24
437 ,P_LER_ATTRIBUTE_CATEGORY => c.p25
438 ,P_LER_ATTRIBUTE1 => c.p26
439 ,P_LER_ATTRIBUTE2 => c.p27
440 ,P_LER_ATTRIBUTE3 => c.p28
441 ,P_LER_ATTRIBUTE4 => c.p29
442 ,P_LER_ATTRIBUTE5 => c.p30
443 ,P_LER_ATTRIBUTE6 => c.p31
444 ,P_LER_ATTRIBUTE7 => c.p32
445 ,P_LER_ATTRIBUTE8 => c.p33
446 ,P_LER_ATTRIBUTE9 => c.p34
447 ,P_LER_ATTRIBUTE10 => c.p35
448 ,P_LER_ATTRIBUTE11 => c.p36
449 ,P_LER_ATTRIBUTE12 => c.p37
450 ,P_LER_ATTRIBUTE13 => c.p38
451 ,P_LER_ATTRIBUTE14 => c.p39
452 ,P_LER_ATTRIBUTE15 => c.p40
453 ,P_LER_ATTRIBUTE16 => c.p41
454 ,P_LER_ATTRIBUTE17 => c.p42
455 ,P_LER_ATTRIBUTE18 => c.p43
456 ,P_LER_ATTRIBUTE19 => c.p44
457 ,P_LER_ATTRIBUTE20 => c.p45
458 ,P_LER_ATTRIBUTE21 => c.p46
459 ,P_LER_ATTRIBUTE22 => c.p47
460 ,P_LER_ATTRIBUTE23 => c.p48
461 ,P_LER_ATTRIBUTE24 => c.p49
462 ,P_LER_ATTRIBUTE25 => c.p50
463 ,P_LER_ATTRIBUTE26 => c.p51
464 ,P_LER_ATTRIBUTE27 => c.p52
465 ,P_LER_ATTRIBUTE28 => c.p53
466 ,P_LER_ATTRIBUTE29 => c.p54
467 ,P_LER_ATTRIBUTE30 => c.p55
468 ,P_OBJECT_VERSION_NUMBER => c.p56
469 ,P_EFFECTIVE_DATE => c.p57);
470 hr_data_pump.api_trc_off;
471 
472 --
473 update hr_pump_batch_lines l set
474 l.pval001 = decode(c.p1,null,cn,c.p1),
475 l.pval002 = decode(c.p2,null,cn,dc(c.p2)),
476 l.pval003 = decode(c.p3,null,cn,dc(c.p3)),
477 l.pval056 = decode(c.p56,null,cn,c.p56)
478 where l.rowid = c.myrowid;
479 --
480 close cr;
481 --
482 hr_data_pump.exit('call');
483 exception
484  when hr_multi_message.error_message_exist then
485    if cr%isopen then
486     close cr;
487    end if;
488    hr_pump_utils.set_multi_msg_error_flag(true);
489  when others then
490  if cr%isopen then
491   close cr;
492  end if;
493  raise;
494 end call;
495 end hrdpp_CREATE_LIFE_EVENT_REASON;