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_ENROLLMENT

Source


4  * Generated for API: BEN_ENROLLMENT_PROCESS.CREATE_ENROLLMENT
1 package body hrdpp_CREATE_ENROLLMENT as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2012/11/27 04:11:10
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;
55 if upper(i) = 'N' then return to_char(p);
52 function nd(p in number,i in varchar2)
53 return varchar2 is
54 begin
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_LIFE_EVENT_DATE in date
83 ,P_ENDED_BNFT_VAL in number default null
84 ,P_EFFECTIVE_DATE in date
85 ,P_BNFT_VAL in number default null
86 ,P_RT_VAL1 in number default null
87 ,P_ANN_RT_VAL1 in number default null
88 ,P_RT_STRT_DT1 in date default null
89 ,P_RT_END_DT1 in date default null
90 ,P_RT_VAL2 in number default null
91 ,P_ANN_RT_VAL2 in number default null
92 ,P_RT_STRT_DT2 in date default null
93 ,P_RT_END_DT2 in date default null
94 ,P_RT_VAL3 in number default null
95 ,P_ANN_RT_VAL3 in number default null
96 ,P_RT_STRT_DT3 in date default null
97 ,P_RT_END_DT3 in date default null
98 ,P_RT_VAL4 in number default null
99 ,P_ANN_RT_VAL4 in number default null
100 ,P_RT_STRT_DT4 in date default null
101 ,P_RT_END_DT4 in date default null
102 ,P_ENRT_CVG_STRT_DT in date default null
103 ,P_ENRT_CVG_THRU_DT in date default null
104 ,P_ORGNL_ENRT_DT in date default null
105 ,P_PROC_CD in varchar2 default null
106 ,P_RECORD_TYP_CD in varchar2
107 ,P_PROGRAM in varchar2 default null
108 ,P_PROGRAM_NUM in number default null
109 ,P_PLAN in varchar2 default null
110 ,P_PLAN_NUM in number default null
111 ,P_OPTION in varchar2 default null
112 ,P_OPTION_NUM in number default null
113 ,P_LIFE_EVENT_REASON in varchar2
114 ,P_ENDED_PLAN in varchar2 default null
115 ,P_ENDED_PLAN_NUM in number default null
116 ,P_ENDED_OPTION in varchar2 default null
117 ,P_ENDED_OPTION_NUM in number default null
118 ,P_EMPLOYEE_NUMBER in varchar2
119 ,P_NATIONAL_IDENTIFIER in varchar2
120 ,P_FULL_NAME in varchar2
121 ,P_DATE_OF_BIRTH in date
122 ,P_PERSON_NUM in number
123 ,P_ACTY_BASE_RATE_NAME1 in varchar2 default null
124 ,P_ACTY_BASE_RATE_NUM1 in number default null
125 ,P_ACTY_BASE_RATE_NAME2 in varchar2 default null
126 ,P_ACTY_BASE_RATE_NUM2 in number default null
127 ,P_ACTY_BASE_RATE_NAME3 in varchar2 default null
128 ,P_ACTY_BASE_RATE_NUM3 in number default null
129 ,P_ACTY_BASE_RATE_NAME4 in varchar2 default null
130 ,P_ACTY_BASE_RATE_NUM4 in number default null) is
131 blid number := p_data_pump_batch_line_id;
132 begin
133 if blid is not null then
134 delete from hr_pump_batch_lines where batch_line_id = blid;
135 delete from hr_pump_batch_exceptions
136 where source_type = 'BATCH_LINE' and source_id = blid;
137 end if;
138 insert into hr_pump_batch_lines
139 (batch_id
140 ,batch_line_id
141 ,business_group_name
142 ,api_module_id
143 ,line_status
144 ,user_sequence
145 ,link_value
146 ,pval001
147 ,pval002
148 ,pval003
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 values
196 (p_batch_id
197 ,nvl(blid,hr_pump_batch_lines_s.nextval)
198 ,p_data_pump_business_grp_name
199 ,3845
200 ,'U'
201 ,p_user_sequence
202 ,p_link_value
203 ,dc(P_LIFE_EVENT_DATE)
204 ,P_ENDED_BNFT_VAL
205 ,dc(P_EFFECTIVE_DATE)
206 ,P_BNFT_VAL
207 ,P_RT_VAL1
208 ,P_ANN_RT_VAL1
209 ,dc(P_RT_STRT_DT1)
210 ,dc(P_RT_END_DT1)
211 ,P_RT_VAL2
212 ,P_ANN_RT_VAL2
213 ,dc(P_RT_STRT_DT2)
214 ,dc(P_RT_END_DT2)
215 ,P_RT_VAL3
216 ,P_ANN_RT_VAL3
217 ,dc(P_RT_STRT_DT3)
218 ,dc(P_RT_END_DT3)
219 ,P_RT_VAL4
220 ,P_ANN_RT_VAL4
221 ,dc(P_RT_STRT_DT4)
222 ,dc(P_RT_END_DT4)
223 ,dc(P_ENRT_CVG_STRT_DT)
224 ,dc(P_ENRT_CVG_THRU_DT)
225 ,dc(P_ORGNL_ENRT_DT)
226 ,P_PROC_CD
227 ,P_RECORD_TYP_CD
228 ,P_PROGRAM
229 ,P_PROGRAM_NUM
230 ,P_PLAN
231 ,P_PLAN_NUM
232 ,P_OPTION
233 ,P_OPTION_NUM
234 ,P_LIFE_EVENT_REASON
235 ,P_ENDED_PLAN
236 ,P_ENDED_PLAN_NUM
237 ,P_ENDED_OPTION
238 ,P_ENDED_OPTION_NUM
239 ,P_EMPLOYEE_NUMBER
240 ,P_NATIONAL_IDENTIFIER
241 ,P_FULL_NAME
242 ,dc(P_DATE_OF_BIRTH)
243 ,P_PERSON_NUM
244 ,P_ACTY_BASE_RATE_NAME1
245 ,P_ACTY_BASE_RATE_NUM1
249 ,P_ACTY_BASE_RATE_NUM3
246 ,P_ACTY_BASE_RATE_NAME2
247 ,P_ACTY_BASE_RATE_NUM2
248 ,P_ACTY_BASE_RATE_NAME3
250 ,P_ACTY_BASE_RATE_NAME4
251 ,P_ACTY_BASE_RATE_NUM4);
252 end insert_batch_lines;
253 --
254 procedure call
255 (p_business_group_id in number,
256 p_batch_line_id     in number) is
257 cursor cr is
258 select l.rowid myrowid,
259 decode(l.pval001,cn,dn,d(l.pval001)) p1,
260 decode(l.pval002,cn,nn,vn,nn,n(l.pval002)) p2,
261 l.pval002 d2,
262 decode(l.pval003,cn,dn,d(l.pval003)) p3,
263 decode(l.pval004,cn,nn,vn,nn,n(l.pval004)) p4,
264 l.pval004 d4,
265 decode(l.pval005,cn,nn,vn,nn,n(l.pval005)) p5,
266 l.pval005 d5,
267 decode(l.pval006,cn,nn,vn,nn,n(l.pval006)) p6,
268 l.pval006 d6,
269 decode(l.pval007,cn,dn,vn,dn,d(l.pval007)) p7,
270 l.pval007 d7,
271 decode(l.pval008,cn,dn,vn,dn,d(l.pval008)) p8,
272 l.pval008 d8,
273 decode(l.pval009,cn,nn,vn,nn,n(l.pval009)) p9,
274 l.pval009 d9,
275 decode(l.pval010,cn,nn,vn,nn,n(l.pval010)) p10,
276 l.pval010 d10,
277 decode(l.pval011,cn,dn,vn,dn,d(l.pval011)) p11,
278 l.pval011 d11,
279 decode(l.pval012,cn,dn,vn,dn,d(l.pval012)) p12,
280 l.pval012 d12,
281 decode(l.pval013,cn,nn,vn,nn,n(l.pval013)) p13,
282 l.pval013 d13,
283 decode(l.pval014,cn,nn,vn,nn,n(l.pval014)) p14,
284 l.pval014 d14,
285 decode(l.pval015,cn,dn,vn,dn,d(l.pval015)) p15,
286 l.pval015 d15,
287 decode(l.pval016,cn,dn,vn,dn,d(l.pval016)) p16,
288 l.pval016 d16,
289 decode(l.pval017,cn,nn,vn,nn,n(l.pval017)) p17,
290 l.pval017 d17,
291 decode(l.pval018,cn,nn,vn,nn,n(l.pval018)) p18,
292 l.pval018 d18,
293 decode(l.pval019,cn,dn,vn,dn,d(l.pval019)) p19,
294 l.pval019 d19,
295 decode(l.pval020,cn,dn,vn,dn,d(l.pval020)) p20,
296 l.pval020 d20,
297 decode(l.pval021,cn,dn,vn,dn,d(l.pval021)) p21,
298 l.pval021 d21,
299 decode(l.pval022,cn,dn,vn,dn,d(l.pval022)) p22,
300 l.pval022 d22,
301 decode(l.pval023,cn,dn,vn,dn,d(l.pval023)) p23,
302 l.pval023 d23,
303 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
304 l.pval024 d24,
305 decode(l.pval025,cn,vn,l.pval025) p25,
306 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
307 l.pval026 d26,
308 decode(l.pval027,cn,nn,vn,nn,n(l.pval027)) p27,
309 l.pval027 d27,
310 decode(l.pval028,cn,vn,vn,vn,l.pval028) p28,
311 l.pval028 d28,
312 decode(l.pval029,cn,nn,vn,nn,n(l.pval029)) p29,
313 l.pval029 d29,
314 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
315 l.pval030 d30,
316 decode(l.pval031,cn,nn,vn,nn,n(l.pval031)) p31,
317 l.pval031 d31,
318 decode(l.pval032,cn,vn,l.pval032) p32,
319 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
320 l.pval033 d33,
321 decode(l.pval034,cn,nn,vn,nn,n(l.pval034)) p34,
322 l.pval034 d34,
323 decode(l.pval035,cn,vn,vn,vn,l.pval035) p35,
324 l.pval035 d35,
325 decode(l.pval036,cn,nn,vn,nn,n(l.pval036)) p36,
326 l.pval036 d36,
327 decode(l.pval037,cn,vn,l.pval037) p37,
328 decode(l.pval038,cn,vn,l.pval038) p38,
329 decode(l.pval039,cn,vn,l.pval039) p39,
330 decode(l.pval040,cn,dn,d(l.pval040)) p40,
331 decode(l.pval041,cn,nn,n(l.pval041)) p41,
332 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
333 l.pval042 d42,
334 decode(l.pval043,cn,nn,vn,nn,n(l.pval043)) p43,
335 l.pval043 d43,
336 decode(l.pval044,cn,vn,vn,vn,l.pval044) p44,
337 l.pval044 d44,
338 decode(l.pval045,cn,nn,vn,nn,n(l.pval045)) p45,
339 l.pval045 d45,
340 decode(l.pval046,cn,vn,vn,vn,l.pval046) p46,
341 l.pval046 d46,
342 decode(l.pval047,cn,nn,vn,nn,n(l.pval047)) p47,
343 l.pval047 d47,
344 decode(l.pval048,cn,vn,vn,vn,l.pval048) p48,
345 l.pval048 d48,
346 decode(l.pval049,cn,nn,vn,nn,n(l.pval049)) p49,
347 l.pval049 d49
348 from hr_pump_batch_lines l
349 where l.batch_line_id = p_batch_line_id;
350 --
351 c cr%rowtype;
352 l_validate boolean := false;
353 L_PGM_ID number;
354 L_PL_ID number;
355 L_OPT_ID number;
356 L_LER_ID number;
357 L_ENDED_PL_ID number;
358 L_ENDED_OPT_ID number;
359 L_PERSON_ID number;
360 L_ACTY_BASE_RT_ID1 number;
361 L_ACTY_BASE_RT_ID2 number;
362 L_ACTY_BASE_RT_ID3 number;
363 L_ACTY_BASE_RT_ID4 number;
364 --
365 begin
366 hr_data_pump.entry('call');
367 open cr;
368 fetch cr into c;
369 if cr%notfound then
370 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
371 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
372 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
373 hr_utility.set_message_token('VALUE',p_batch_line_id);
374 hr_utility.raise_error;
375 end if;
376 --
377 L_PGM_ID := 
378 BEN_PUMP_GET.GET_PGM_ID
379 (P_DATA_PUMP_ALWAYS_CALL => null
380 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
381 ,P_PROGRAM => c.p26
382 ,P_PROGRAM_NUM => c.p27
383 ,P_EFFECTIVE_DATE => c.p3);
384 --
385 L_PL_ID := 
386 BEN_PUMP_GET.GET_PL_ID
387 (P_DATA_PUMP_ALWAYS_CALL => null
388 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
389 ,P_PLAN => c.p28
390 ,P_PLAN_NUM => c.p29
391 ,P_EFFECTIVE_DATE => c.p3);
392 --
393 L_OPT_ID := 
394 BEN_PUMP_GET.GET_OPT_ID
395 (P_DATA_PUMP_ALWAYS_CALL => null
396 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
397 ,P_OPTION => c.p30
398 ,P_OPTION_NUM => c.p31
399 ,P_EFFECTIVE_DATE => c.p3);
400 --
401 if c.p32 is null or
402 c.p3 is null then
403 L_LER_ID:=nn;
404 else
405 L_LER_ID := 
406 hr_pump_get.GET_LER_ID
407 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
408 ,P_LIFE_EVENT_REASON => c.p32
409 ,P_EFFECTIVE_DATE => c.p3);
410 end if;
411 --
412 L_ENDED_PL_ID := 
413 BEN_PUMP_GET.GET_ENDED_PL_ID
414 (P_DATA_PUMP_ALWAYS_CALL => null
418 ,P_EFFECTIVE_DATE => c.p3);
415 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
416 ,P_ENDED_PLAN => c.p33
417 ,P_ENDED_PLAN_NUM => c.p34
419 --
420 L_ENDED_OPT_ID := 
421 BEN_PUMP_GET.GET_ENDED_OPT_ID
422 (P_DATA_PUMP_ALWAYS_CALL => null
423 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
424 ,P_ENDED_OPTION => c.p35
425 ,P_ENDED_OPTION_NUM => c.p36
426 ,P_EFFECTIVE_DATE => c.p3);
427 --
428 L_PERSON_ID := 
429 BEN_PUMP_GET.GET_PEN_PERSON_ID
430 (P_DATA_PUMP_ALWAYS_CALL => null
431 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
432 ,P_EMPLOYEE_NUMBER => c.p37
433 ,P_NATIONAL_IDENTIFIER => c.p38
434 ,P_FULL_NAME => c.p39
435 ,P_DATE_OF_BIRTH => c.p40
436 ,P_PERSON_NUM => c.p41
437 ,P_EFFECTIVE_DATE => c.p3);
438 --
439 L_ACTY_BASE_RT_ID1 := 
440 BEN_PUMP_GET.GET_ACTY_BASE_RT_ID1
441 (P_DATA_PUMP_ALWAYS_CALL => null
442 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
443 ,P_ACTY_BASE_RATE_NAME1 => c.p42
444 ,P_ACTY_BASE_RATE_NUM1 => c.p43
445 ,P_EFFECTIVE_DATE => c.p3);
446 --
447 L_ACTY_BASE_RT_ID2 := 
448 BEN_PUMP_GET.GET_ACTY_BASE_RT_ID2
449 (P_DATA_PUMP_ALWAYS_CALL => null
450 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
451 ,P_ACTY_BASE_RATE_NAME2 => c.p44
452 ,P_ACTY_BASE_RATE_NUM2 => c.p45
453 ,P_EFFECTIVE_DATE => c.p3);
454 --
455 L_ACTY_BASE_RT_ID3 := 
456 BEN_PUMP_GET.GET_ACTY_BASE_RT_ID3
457 (P_DATA_PUMP_ALWAYS_CALL => null
458 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
459 ,P_ACTY_BASE_RATE_NAME3 => c.p46
460 ,P_ACTY_BASE_RATE_NUM3 => c.p47
461 ,P_EFFECTIVE_DATE => c.p3);
462 --
463 L_ACTY_BASE_RT_ID4 := 
464 BEN_PUMP_GET.GET_ACTY_BASE_RT_ID4
465 (P_DATA_PUMP_ALWAYS_CALL => null
466 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
467 ,P_ACTY_BASE_RATE_NAME4 => c.p48
468 ,P_ACTY_BASE_RATE_NUM4 => c.p49
469 ,P_EFFECTIVE_DATE => c.p3);
470 --
471 hr_data_pump.api_trc_on;
472 BEN_ENROLLMENT_PROCESS.CREATE_ENROLLMENT
473 (p_validate => l_validate
474 ,P_PGM_ID => L_PGM_ID
475 ,P_PL_ID => L_PL_ID
476 ,P_OPT_ID => L_OPT_ID
477 ,P_LER_ID => L_LER_ID
478 ,P_LIFE_EVENT_DATE => c.p1
479 ,P_ENDED_PL_ID => L_ENDED_PL_ID
480 ,P_ENDED_OPT_ID => L_ENDED_OPT_ID
481 ,P_ENDED_BNFT_VAL => c.p2
482 ,P_EFFECTIVE_DATE => c.p3
483 ,P_PERSON_ID => L_PERSON_ID
484 ,P_BNFT_VAL => c.p4
485 ,P_ACTY_BASE_RT_ID1 => L_ACTY_BASE_RT_ID1
486 ,P_RT_VAL1 => c.p5
487 ,P_ANN_RT_VAL1 => c.p6
488 ,P_RT_STRT_DT1 => c.p7
489 ,P_RT_END_DT1 => c.p8
490 ,P_ACTY_BASE_RT_ID2 => L_ACTY_BASE_RT_ID2
491 ,P_RT_VAL2 => c.p9
492 ,P_ANN_RT_VAL2 => c.p10
493 ,P_RT_STRT_DT2 => c.p11
494 ,P_RT_END_DT2 => c.p12
495 ,P_ACTY_BASE_RT_ID3 => L_ACTY_BASE_RT_ID3
496 ,P_RT_VAL3 => c.p13
497 ,P_ANN_RT_VAL3 => c.p14
498 ,P_RT_STRT_DT3 => c.p15
499 ,P_RT_END_DT3 => c.p16
500 ,P_ACTY_BASE_RT_ID4 => L_ACTY_BASE_RT_ID4
501 ,P_RT_VAL4 => c.p17
502 ,P_ANN_RT_VAL4 => c.p18
503 ,P_RT_STRT_DT4 => c.p19
504 ,P_RT_END_DT4 => c.p20
505 ,p_business_group_id => p_business_group_id
506 ,P_ENRT_CVG_STRT_DT => c.p21
507 ,P_ENRT_CVG_THRU_DT => c.p22
508 ,P_ORGNL_ENRT_DT => c.p23
509 ,P_PROC_CD => c.p24
510 ,P_RECORD_TYP_CD => c.p25);
511 hr_data_pump.api_trc_off;
512 
513 --
514 
515 --
516 close cr;
517 --
518 hr_data_pump.exit('call');
519 exception
520  when hr_multi_message.error_message_exist then
521    if cr%isopen then
522     close cr;
523    end if;
524    hr_pump_utils.set_multi_msg_error_flag(true);
525  when others then
526  if cr%isopen then
527   close cr;
528  end if;
529  raise;
530 end call;
531 end hrdpp_CREATE_ENROLLMENT;