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_LIFE_EVENT_REASON

Source


1 package body hrdpp_CREATE_LIFE_EVENT_REASON as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2007/01/04 02:01:28
4  * Generated for API: ben_life_event_reason_api.CREATE_LIFE_EVENT_REASON
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_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_LER_ATTRIBUTE_CATEGORY in varchar2 default null
103 ,P_LER_ATTRIBUTE1 in varchar2 default null
104 ,P_LER_ATTRIBUTE2 in varchar2 default null
105 ,P_LER_ATTRIBUTE3 in varchar2 default null
106 ,P_LER_ATTRIBUTE4 in varchar2 default null
107 ,P_LER_ATTRIBUTE5 in varchar2 default null
108 ,P_LER_ATTRIBUTE6 in varchar2 default null
109 ,P_LER_ATTRIBUTE7 in varchar2 default null
110 ,P_LER_ATTRIBUTE8 in varchar2 default null
111 ,P_LER_ATTRIBUTE9 in varchar2 default null
112 ,P_LER_ATTRIBUTE10 in varchar2 default null
113 ,P_LER_ATTRIBUTE11 in varchar2 default null
114 ,P_LER_ATTRIBUTE12 in varchar2 default null
115 ,P_LER_ATTRIBUTE13 in varchar2 default null
116 ,P_LER_ATTRIBUTE14 in varchar2 default null
117 ,P_LER_ATTRIBUTE15 in varchar2 default null
118 ,P_LER_ATTRIBUTE16 in varchar2 default null
119 ,P_LER_ATTRIBUTE17 in varchar2 default null
120 ,P_LER_ATTRIBUTE18 in varchar2 default null
121 ,P_LER_ATTRIBUTE19 in varchar2 default null
122 ,P_LER_ATTRIBUTE20 in varchar2 default null
123 ,P_LER_ATTRIBUTE21 in varchar2 default null
124 ,P_LER_ATTRIBUTE22 in varchar2 default null
125 ,P_LER_ATTRIBUTE23 in varchar2 default null
126 ,P_LER_ATTRIBUTE24 in varchar2 default null
127 ,P_LER_ATTRIBUTE25 in varchar2 default null
128 ,P_LER_ATTRIBUTE26 in varchar2 default null
129 ,P_LER_ATTRIBUTE27 in varchar2 default null
130 ,P_LER_ATTRIBUTE28 in varchar2 default null
131 ,P_LER_ATTRIBUTE29 in varchar2 default null
132 ,P_LER_ATTRIBUTE30 in varchar2 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 ,pval004
150 ,pval005
151 ,pval006
152 ,pval007
153 ,pval008
154 ,pval009
155 ,pval010
156 ,pval011
157 ,pval012
158 ,pval013
159 ,pval014
160 ,pval015
161 ,pval016
162 ,pval017
163 ,pval018
164 ,pval019
165 ,pval020
166 ,pval021
167 ,pval022
168 ,pval023
169 ,pval024
170 ,pval025
171 ,pval026
172 ,pval027
173 ,pval028
174 ,pval029
175 ,pval030
176 ,pval031
177 ,pval032
178 ,pval033
179 ,pval034
180 ,pval035
181 ,pval036
182 ,pval037
183 ,pval038
184 ,pval039
185 ,pval040
186 ,pval041
187 ,pval042
188 ,pval043
189 ,pval044
190 ,pval045
191 ,pval046
192 ,pval047
193 ,pval048
194 ,pval049
195 ,pval050
196 ,pval051
197 ,pval052
198 ,pval053
199 ,pval054
200 ,pval056)
201 values
202 (p_batch_id
203 ,nvl(blid,hr_pump_batch_lines_s.nextval)
204 ,p_data_pump_business_grp_name
205 ,474
206 ,'U'
207 ,p_user_sequence
208 ,p_link_value
209 ,P_NAME
210 ,P_TYP_CD
211 ,P_LF_EVT_OPER_CD
212 ,P_SHORT_NAME
213 ,P_SHORT_CODE
214 ,P_PTNL_LER_TRTMT_CD
215 ,P_CK_RLTD_PER_ELIG_FLAG
216 ,P_LER_EVAL_RL
217 ,P_CM_APLY_FLAG
218 ,P_OVRIDG_LE_FLAG
219 ,P_QUALG_EVT_FLAG
220 ,P_WHN_TO_PRCS_CD
221 ,P_DESC_TXT
222 ,P_TMLNS_EVAL_CD
223 ,P_TMLNS_PERD_CD
224 ,P_TMLNS_DYS_NUM
225 ,P_TMLNS_PERD_RL
226 ,P_OCRD_DT_DET_CD
227 ,P_LER_STAT_CD
228 ,P_SLCTBL_SLF_SVC_CD
229 ,P_LER_ATTRIBUTE_CATEGORY
230 ,P_LER_ATTRIBUTE1
231 ,P_LER_ATTRIBUTE2
232 ,P_LER_ATTRIBUTE3
233 ,P_LER_ATTRIBUTE4
234 ,P_LER_ATTRIBUTE5
235 ,P_LER_ATTRIBUTE6
236 ,P_LER_ATTRIBUTE7
237 ,P_LER_ATTRIBUTE8
238 ,P_LER_ATTRIBUTE9
239 ,P_LER_ATTRIBUTE10
240 ,P_LER_ATTRIBUTE11
241 ,P_LER_ATTRIBUTE12
242 ,P_LER_ATTRIBUTE13
243 ,P_LER_ATTRIBUTE14
244 ,P_LER_ATTRIBUTE15
245 ,P_LER_ATTRIBUTE16
246 ,P_LER_ATTRIBUTE17
247 ,P_LER_ATTRIBUTE18
248 ,P_LER_ATTRIBUTE19
249 ,P_LER_ATTRIBUTE20
250 ,P_LER_ATTRIBUTE21
251 ,P_LER_ATTRIBUTE22
252 ,P_LER_ATTRIBUTE23
253 ,P_LER_ATTRIBUTE24
254 ,P_LER_ATTRIBUTE25
255 ,P_LER_ATTRIBUTE26
256 ,P_LER_ATTRIBUTE27
257 ,P_LER_ATTRIBUTE28
258 ,P_LER_ATTRIBUTE29
259 ,P_LER_ATTRIBUTE30
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,vn,vn,vn,
274  hr_pump_get.gl(l.pval005,'BEN_LER_TYP',d(l.pval056),vn)) p5,
275 l.pval005 d5,
276 decode(l.pval006,cn,vn,vn,vn,l.pval006) p6,
277 l.pval006 d6,
278 decode(l.pval007,cn,vn,vn,vn,l.pval007) p7,
279 l.pval007 d7,
280 decode(l.pval008,cn,vn,vn,vn,l.pval008) p8,
281 l.pval008 d8,
282 decode(l.pval009,cn,vn,vn,vn,
283  hr_pump_get.gl(l.pval009,'BEN_PTNL_LER_TRTMT',d(l.pval056),vn)) p9,
284 l.pval009 d9,
285 decode(l.pval010,cn,vn,vn,vn,
286  hr_pump_get.gl(l.pval010,'YES_NO',d(l.pval056),vn)) p10,
287 l.pval010 d10,
288 decode(l.pval011,cn,nn,vn,nn,n(l.pval011)) p11,
289 l.pval011 d11,
290 decode(l.pval012,cn,vn,vn,vn,
291  hr_pump_get.gl(l.pval012,'YES_NO',d(l.pval056),vn)) p12,
292 l.pval012 d12,
293 decode(l.pval013,cn,vn,vn,vn,
294  hr_pump_get.gl(l.pval013,'YES_NO',d(l.pval056),vn)) p13,
295 l.pval013 d13,
296 decode(l.pval014,cn,vn,vn,vn,
297  hr_pump_get.gl(l.pval014,'YES_NO',d(l.pval056),vn)) p14,
298 l.pval014 d14,
299 decode(l.pval015,cn,vn,vn,vn,
300  hr_pump_get.gl(l.pval015,'BEN_WHN_TO_PRCS_LER',d(l.pval056),vn)) p15,
301 l.pval015 d15,
302 decode(l.pval016,cn,vn,vn,vn,l.pval016) p16,
303 l.pval016 d16,
304 decode(l.pval017,cn,vn,vn,vn,
305  hr_pump_get.gl(l.pval017,'BEN_LER_TMLNS_EVAL',d(l.pval056),vn)) p17,
306 l.pval017 d17,
307 decode(l.pval018,cn,vn,vn,vn,
308  hr_pump_get.gl(l.pval018,'BEN_LER_TMLNS_PERD',d(l.pval056),vn)) p18,
309 l.pval018 d18,
310 decode(l.pval019,cn,nn,vn,nn,n(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,vn,vn,vn,
315  hr_pump_get.gl(l.pval021,'BEN_OCRD_DT_DET',d(l.pval056),vn)) p21,
316 l.pval021 d21,
317 decode(l.pval022,cn,vn,vn,vn,l.pval022) p22,
318 l.pval022 d22,
319 decode(l.pval023,cn,vn,vn,vn,
320  hr_pump_get.gl(l.pval023,'BEN_SLCTBL_SLF_SVC_CD',d(l.pval056),vn)) p23,
321 l.pval023 d23,
322 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
323 l.pval024 d24,
324 decode(l.pval025,cn,vn,vn,vn,l.pval025) p25,
325 l.pval025 d25,
326 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
327 l.pval026 d26,
328 decode(l.pval027,cn,vn,vn,vn,l.pval027) p27,
329 l.pval027 d27,
330 decode(l.pval028,cn,vn,vn,vn,l.pval028) p28,
331 l.pval028 d28,
332 decode(l.pval029,cn,vn,vn,vn,l.pval029) p29,
333 l.pval029 d29,
337 l.pval031 d31,
334 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
335 l.pval030 d30,
336 decode(l.pval031,cn,vn,vn,vn,l.pval031) p31,
338 decode(l.pval032,cn,vn,vn,vn,l.pval032) p32,
339 l.pval032 d32,
340 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
341 l.pval033 d33,
342 decode(l.pval034,cn,vn,vn,vn,l.pval034) p34,
343 l.pval034 d34,
344 decode(l.pval035,cn,vn,vn,vn,l.pval035) p35,
345 l.pval035 d35,
346 decode(l.pval036,cn,vn,vn,vn,l.pval036) p36,
347 l.pval036 d36,
348 decode(l.pval037,cn,vn,vn,vn,l.pval037) p37,
349 l.pval037 d37,
350 decode(l.pval038,cn,vn,vn,vn,l.pval038) p38,
351 l.pval038 d38,
352 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
353 l.pval039 d39,
354 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
355 l.pval040 d40,
356 decode(l.pval041,cn,vn,vn,vn,l.pval041) p41,
357 l.pval041 d41,
358 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
359 l.pval042 d42,
360 decode(l.pval043,cn,vn,vn,vn,l.pval043) p43,
361 l.pval043 d43,
362 decode(l.pval044,cn,vn,vn,vn,l.pval044) p44,
363 l.pval044 d44,
364 decode(l.pval045,cn,vn,vn,vn,l.pval045) p45,
365 l.pval045 d45,
366 decode(l.pval046,cn,vn,vn,vn,l.pval046) p46,
367 l.pval046 d46,
368 decode(l.pval047,cn,vn,vn,vn,l.pval047) p47,
369 l.pval047 d47,
370 decode(l.pval048,cn,vn,vn,vn,l.pval048) p48,
371 l.pval048 d48,
372 decode(l.pval049,cn,vn,vn,vn,l.pval049) p49,
373 l.pval049 d49,
374 decode(l.pval050,cn,vn,vn,vn,l.pval050) p50,
375 l.pval050 d50,
376 decode(l.pval051,cn,vn,vn,vn,l.pval051) p51,
377 l.pval051 d51,
378 decode(l.pval052,cn,vn,vn,vn,l.pval052) p52,
379 l.pval052 d52,
380 decode(l.pval053,cn,vn,vn,vn,l.pval053) p53,
381 l.pval053 d53,
382 decode(l.pval054,cn,vn,vn,vn,l.pval054) p54,
383 l.pval054 d54,
384 l.pval055 p55,
385 decode(l.pval056,cn,dn,d(l.pval056)) p56
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 --
392 begin
393 hr_data_pump.entry('call');
394 open cr;
395 fetch cr into c;
396 if cr%notfound then
397 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
398 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
399 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
400 hr_utility.set_message_token('VALUE',p_batch_line_id);
401 hr_utility.raise_error;
402 end if;
403 --
404 hr_data_pump.api_trc_on;
405 ben_life_event_reason_api.CREATE_LIFE_EVENT_REASON
406 (p_validate => l_validate
407 ,P_LER_ID => c.p1
408 ,P_EFFECTIVE_START_DATE => c.p2
409 ,P_EFFECTIVE_END_DATE => c.p3
410 ,P_NAME => c.p4
411 ,p_business_group_id => p_business_group_id
412 ,P_TYP_CD => c.p5
413 ,P_LF_EVT_OPER_CD => c.p6
414 ,P_SHORT_NAME => c.p7
415 ,P_SHORT_CODE => c.p8
416 ,P_PTNL_LER_TRTMT_CD => c.p9
417 ,P_CK_RLTD_PER_ELIG_FLAG => c.p10
418 ,P_LER_EVAL_RL => c.p11
419 ,P_CM_APLY_FLAG => c.p12
420 ,P_OVRIDG_LE_FLAG => c.p13
421 ,P_QUALG_EVT_FLAG => c.p14
422 ,P_WHN_TO_PRCS_CD => c.p15
423 ,P_DESC_TXT => c.p16
424 ,P_TMLNS_EVAL_CD => c.p17
425 ,P_TMLNS_PERD_CD => c.p18
426 ,P_TMLNS_DYS_NUM => c.p19
427 ,P_TMLNS_PERD_RL => c.p20
428 ,P_OCRD_DT_DET_CD => c.p21
429 ,P_LER_STAT_CD => c.p22
430 ,P_SLCTBL_SLF_SVC_CD => c.p23
431 ,P_LER_ATTRIBUTE_CATEGORY => c.p24
432 ,P_LER_ATTRIBUTE1 => c.p25
433 ,P_LER_ATTRIBUTE2 => c.p26
434 ,P_LER_ATTRIBUTE3 => c.p27
435 ,P_LER_ATTRIBUTE4 => c.p28
436 ,P_LER_ATTRIBUTE5 => c.p29
437 ,P_LER_ATTRIBUTE6 => c.p30
438 ,P_LER_ATTRIBUTE7 => c.p31
439 ,P_LER_ATTRIBUTE8 => c.p32
440 ,P_LER_ATTRIBUTE9 => c.p33
441 ,P_LER_ATTRIBUTE10 => c.p34
442 ,P_LER_ATTRIBUTE11 => c.p35
443 ,P_LER_ATTRIBUTE12 => c.p36
444 ,P_LER_ATTRIBUTE13 => c.p37
445 ,P_LER_ATTRIBUTE14 => c.p38
446 ,P_LER_ATTRIBUTE15 => c.p39
447 ,P_LER_ATTRIBUTE16 => c.p40
448 ,P_LER_ATTRIBUTE17 => c.p41
449 ,P_LER_ATTRIBUTE18 => c.p42
450 ,P_LER_ATTRIBUTE19 => c.p43
451 ,P_LER_ATTRIBUTE20 => c.p44
452 ,P_LER_ATTRIBUTE21 => c.p45
453 ,P_LER_ATTRIBUTE22 => c.p46
454 ,P_LER_ATTRIBUTE23 => c.p47
455 ,P_LER_ATTRIBUTE24 => c.p48
456 ,P_LER_ATTRIBUTE25 => c.p49
457 ,P_LER_ATTRIBUTE26 => c.p50
458 ,P_LER_ATTRIBUTE27 => c.p51
459 ,P_LER_ATTRIBUTE28 => c.p52
460 ,P_LER_ATTRIBUTE29 => c.p53
461 ,P_LER_ATTRIBUTE30 => c.p54
462 ,P_OBJECT_VERSION_NUMBER => c.p55
463 ,P_EFFECTIVE_DATE => c.p56);
464 hr_data_pump.api_trc_off;
465 
466 --
467 update hr_pump_batch_lines l set
468 l.pval001 = decode(c.p1,null,cn,c.p1),
469 l.pval002 = decode(c.p2,null,cn,dc(c.p2)),
470 l.pval003 = decode(c.p3,null,cn,dc(c.p3)),
471 l.pval055 = decode(c.p55,null,cn,c.p55)
472 where l.rowid = c.myrowid;
473 --
474 close cr;
475 --
476 hr_data_pump.exit('call');
477 exception
478  when hr_multi_message.error_message_exist then
479    if cr%isopen then
480     close cr;
481    end if;
482    hr_pump_utils.set_multi_msg_error_flag(true);
483  when others then
484  if cr%isopen then
485   close cr;
486  end if;
487  raise;
488 end call;
489 end hrdpp_CREATE_LIFE_EVENT_REASON;