[Home] [Help]
PACKAGE BODY: APPS.HRDPP_CREATE_ELIG_PERSON_OPTIO
Source
1 package body hrdpp_CREATE_ELIG_PERSON_OPTIO as
2 /*
3 * Generated by hr_pump_meta_mapper at: 2007/01/04 02:01:36
4 * Generated for API: ben_elig_person_option_api.CREATE_ELIG_PERSON_OPTION
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_ELIG_PER_OPT_USER_KEY in varchar2
83 ,P_PRTN_OVRIDN_FLAG in varchar2 default null
84 ,P_PRTN_OVRIDN_THRU_DT in date default null
85 ,P_NO_MX_PRTN_OVRID_THRU_FLAG in varchar2 default null
86 ,P_ELIG_FLAG in varchar2 default null
87 ,P_PRTN_STRT_DT in date default null
88 ,P_PRTN_END_DT in date default null
89 ,P_WAIT_PERD_CMPLTN_DATE in date default null
90 ,P_WAIT_PERD_STRT_DT in date default null
91 ,P_PRTN_OVRIDN_RSN_CD in varchar2 default null
92 ,P_PCT_FL_TM_VAL in number default null
93 ,P_RT_COMP_REF_AMT in number default null
94 ,P_RT_CMBN_AGE_N_LOS_VAL in number default null
95 ,P_RT_AGE_VAL in number default null
96 ,P_RT_LOS_VAL in number default null
97 ,P_RT_HRS_WKD_VAL in number default null
98 ,P_RT_HRS_WKD_BNDRY_PERD_CD in varchar2 default null
99 ,P_RT_AGE_UOM in varchar2 default null
100 ,P_RT_LOS_UOM in varchar2 default null
101 ,P_RT_PCT_FL_TM_VAL in number default null
102 ,P_RT_FRZ_LOS_FLAG in varchar2 default null
103 ,P_RT_FRZ_AGE_FLAG in varchar2 default null
104 ,P_RT_FRZ_CMP_LVL_FLAG in varchar2 default null
105 ,P_RT_FRZ_PCT_FL_TM_FLAG in varchar2 default null
106 ,P_RT_FRZ_HRS_WKD_FLAG in varchar2 default null
107 ,P_RT_FRZ_COMB_AGE_AND_LOS_FLAG in varchar2 default null
108 ,P_COMP_REF_AMT in number default null
109 ,P_CMBN_AGE_N_LOS_VAL in number default null
110 ,P_AGE_VAL in number default null
111 ,P_LOS_VAL in number default null
112 ,P_HRS_WKD_VAL in number default null
113 ,P_HRS_WKD_BNDRY_PERD_CD in varchar2 default null
114 ,P_AGE_UOM in varchar2 default null
115 ,P_LOS_UOM in varchar2 default null
116 ,P_FRZ_LOS_FLAG in varchar2 default null
117 ,P_FRZ_AGE_FLAG in varchar2 default null
118 ,P_FRZ_CMP_LVL_FLAG in varchar2 default null
119 ,P_FRZ_PCT_FL_TM_FLAG in varchar2 default null
120 ,P_FRZ_HRS_WKD_FLAG in varchar2 default null
121 ,P_FRZ_COMB_AGE_AND_LOS_FLAG in varchar2 default null
122 ,P_OVRID_SVC_DT in date default null
123 ,P_INELG_RSN_CD in varchar2 default null
124 ,P_ONCE_R_CNTUG_CD in varchar2 default null
125 ,P_OIPL_ORDR_NUM in number default null
126 ,P_EPO_ATTRIBUTE_CATEGORY in varchar2 default null
127 ,P_EPO_ATTRIBUTE1 in varchar2 default null
128 ,P_EPO_ATTRIBUTE2 in varchar2 default null
129 ,P_EPO_ATTRIBUTE3 in varchar2 default null
130 ,P_EPO_ATTRIBUTE4 in varchar2 default null
131 ,P_EPO_ATTRIBUTE5 in varchar2 default null
132 ,P_EPO_ATTRIBUTE6 in varchar2 default null
133 ,P_EPO_ATTRIBUTE7 in varchar2 default null
134 ,P_EPO_ATTRIBUTE8 in varchar2 default null
135 ,P_EPO_ATTRIBUTE9 in varchar2 default null
136 ,P_EPO_ATTRIBUTE10 in varchar2 default null
137 ,P_EPO_ATTRIBUTE11 in varchar2 default null
138 ,P_EPO_ATTRIBUTE12 in varchar2 default null
139 ,P_EPO_ATTRIBUTE13 in varchar2 default null
140 ,P_EPO_ATTRIBUTE14 in varchar2 default null
141 ,P_EPO_ATTRIBUTE15 in varchar2 default null
142 ,P_EPO_ATTRIBUTE16 in varchar2 default null
143 ,P_EPO_ATTRIBUTE17 in varchar2 default null
144 ,P_EPO_ATTRIBUTE18 in varchar2 default null
145 ,P_EPO_ATTRIBUTE19 in varchar2 default null
146 ,P_EPO_ATTRIBUTE20 in varchar2 default null
147 ,P_EPO_ATTRIBUTE21 in varchar2 default null
148 ,P_EPO_ATTRIBUTE22 in varchar2 default null
149 ,P_EPO_ATTRIBUTE23 in varchar2 default null
150 ,P_EPO_ATTRIBUTE24 in varchar2 default null
151 ,P_EPO_ATTRIBUTE25 in varchar2 default null
152 ,P_EPO_ATTRIBUTE26 in varchar2 default null
153 ,P_EPO_ATTRIBUTE27 in varchar2 default null
154 ,P_EPO_ATTRIBUTE28 in varchar2 default null
155 ,P_EPO_ATTRIBUTE29 in varchar2 default null
156 ,P_EPO_ATTRIBUTE30 in varchar2 default null
157 ,P_PROGRAM_UPDATE_DATE in date default null
158 ,P_EFFECTIVE_DATE in date
159 ,P_OVERRIDE_VALIDATION in boolean default null
160 ,P_ELIG_PER_USER_KEY in varchar2 default null
161 ,P_OPTION_DEFINITION in varchar2 default null
162 ,P_PER_IN_LER_USER_KEY in varchar2 default null
163 ,P_RT_COMP_REF_UOM in varchar2 default null
164 ,P_COMP_REF_UOM in varchar2 default null) is
165 blid number := p_data_pump_batch_line_id;
166 L_OVERRIDE_VALIDATION varchar2(5);
167 begin
168 if P_OVERRIDE_VALIDATION is null then
169 L_OVERRIDE_VALIDATION := null;
170 elsif P_OVERRIDE_VALIDATION then
171 L_OVERRIDE_VALIDATION := 'TRUE';
172 else
173 L_OVERRIDE_VALIDATION := 'FALSE';
174 end if;
175 if blid is not null then
176 delete from hr_pump_batch_lines where batch_line_id = blid;
177 delete from hr_pump_batch_exceptions
178 where source_type = 'BATCH_LINE' and source_id = blid;
179 end if;
180 insert into hr_pump_batch_lines
181 (batch_id
182 ,batch_line_id
183 ,business_group_name
184 ,api_module_id
185 ,line_status
186 ,user_sequence
187 ,link_value
188 ,pval001
189 ,pval004
190 ,pval005
191 ,pval006
192 ,pval007
193 ,pval008
194 ,pval009
195 ,pval010
196 ,pval011
197 ,pval012
198 ,pval013
199 ,pval014
200 ,pval015
201 ,pval016
202 ,pval017
203 ,pval018
204 ,pval019
205 ,pval020
206 ,pval021
207 ,pval022
208 ,pval023
209 ,pval024
210 ,pval025
211 ,pval026
212 ,pval027
213 ,pval028
214 ,pval029
215 ,pval030
216 ,pval031
217 ,pval032
218 ,pval033
219 ,pval034
220 ,pval035
221 ,pval036
222 ,pval037
223 ,pval038
224 ,pval039
225 ,pval040
226 ,pval041
227 ,pval042
228 ,pval043
229 ,pval044
230 ,pval045
231 ,pval046
232 ,pval047
233 ,pval048
234 ,pval049
235 ,pval050
236 ,pval051
237 ,pval052
238 ,pval053
239 ,pval054
240 ,pval055
241 ,pval056
242 ,pval057
243 ,pval058
244 ,pval059
245 ,pval060
246 ,pval061
247 ,pval062
248 ,pval063
249 ,pval064
250 ,pval065
251 ,pval066
252 ,pval067
253 ,pval068
254 ,pval069
255 ,pval070
256 ,pval071
257 ,pval072
258 ,pval073
259 ,pval074
260 ,pval075
261 ,pval076
262 ,pval077
263 ,pval078
264 ,pval080
265 ,pval081
266 ,pval082
267 ,pval083
268 ,pval084
269 ,pval085
270 ,pval086)
271 values
272 (p_batch_id
273 ,nvl(blid,hr_pump_batch_lines_s.nextval)
274 ,p_data_pump_business_grp_name
275 ,381
276 ,'U'
277 ,p_user_sequence
278 ,p_link_value
279 ,P_ELIG_PER_OPT_USER_KEY
280 ,P_PRTN_OVRIDN_FLAG
281 ,dc(P_PRTN_OVRIDN_THRU_DT)
282 ,P_NO_MX_PRTN_OVRID_THRU_FLAG
283 ,P_ELIG_FLAG
284 ,dc(P_PRTN_STRT_DT)
285 ,dc(P_PRTN_END_DT)
286 ,dc(P_WAIT_PERD_CMPLTN_DATE)
287 ,dc(P_WAIT_PERD_STRT_DT)
288 ,P_PRTN_OVRIDN_RSN_CD
289 ,P_PCT_FL_TM_VAL
290 ,P_RT_COMP_REF_AMT
291 ,P_RT_CMBN_AGE_N_LOS_VAL
292 ,P_RT_AGE_VAL
293 ,P_RT_LOS_VAL
294 ,P_RT_HRS_WKD_VAL
295 ,P_RT_HRS_WKD_BNDRY_PERD_CD
296 ,P_RT_AGE_UOM
297 ,P_RT_LOS_UOM
298 ,P_RT_PCT_FL_TM_VAL
299 ,P_RT_FRZ_LOS_FLAG
300 ,P_RT_FRZ_AGE_FLAG
301 ,P_RT_FRZ_CMP_LVL_FLAG
302 ,P_RT_FRZ_PCT_FL_TM_FLAG
303 ,P_RT_FRZ_HRS_WKD_FLAG
304 ,P_RT_FRZ_COMB_AGE_AND_LOS_FLAG
305 ,P_COMP_REF_AMT
306 ,P_CMBN_AGE_N_LOS_VAL
307 ,P_AGE_VAL
308 ,P_LOS_VAL
309 ,P_HRS_WKD_VAL
310 ,P_HRS_WKD_BNDRY_PERD_CD
311 ,P_AGE_UOM
315 ,P_FRZ_CMP_LVL_FLAG
312 ,P_LOS_UOM
313 ,P_FRZ_LOS_FLAG
314 ,P_FRZ_AGE_FLAG
316 ,P_FRZ_PCT_FL_TM_FLAG
317 ,P_FRZ_HRS_WKD_FLAG
318 ,P_FRZ_COMB_AGE_AND_LOS_FLAG
319 ,dc(P_OVRID_SVC_DT)
320 ,P_INELG_RSN_CD
321 ,P_ONCE_R_CNTUG_CD
322 ,P_OIPL_ORDR_NUM
323 ,P_EPO_ATTRIBUTE_CATEGORY
324 ,P_EPO_ATTRIBUTE1
325 ,P_EPO_ATTRIBUTE2
326 ,P_EPO_ATTRIBUTE3
327 ,P_EPO_ATTRIBUTE4
328 ,P_EPO_ATTRIBUTE5
329 ,P_EPO_ATTRIBUTE6
330 ,P_EPO_ATTRIBUTE7
331 ,P_EPO_ATTRIBUTE8
332 ,P_EPO_ATTRIBUTE9
333 ,P_EPO_ATTRIBUTE10
334 ,P_EPO_ATTRIBUTE11
335 ,P_EPO_ATTRIBUTE12
336 ,P_EPO_ATTRIBUTE13
337 ,P_EPO_ATTRIBUTE14
338 ,P_EPO_ATTRIBUTE15
339 ,P_EPO_ATTRIBUTE16
340 ,P_EPO_ATTRIBUTE17
341 ,P_EPO_ATTRIBUTE18
342 ,P_EPO_ATTRIBUTE19
343 ,P_EPO_ATTRIBUTE20
344 ,P_EPO_ATTRIBUTE21
345 ,P_EPO_ATTRIBUTE22
346 ,P_EPO_ATTRIBUTE23
347 ,P_EPO_ATTRIBUTE24
348 ,P_EPO_ATTRIBUTE25
349 ,P_EPO_ATTRIBUTE26
350 ,P_EPO_ATTRIBUTE27
351 ,P_EPO_ATTRIBUTE28
352 ,P_EPO_ATTRIBUTE29
353 ,P_EPO_ATTRIBUTE30
354 ,dc(P_PROGRAM_UPDATE_DATE)
355 ,dc(P_EFFECTIVE_DATE)
356 ,L_OVERRIDE_VALIDATION
357 ,P_ELIG_PER_USER_KEY
358 ,P_OPTION_DEFINITION
359 ,P_PER_IN_LER_USER_KEY
360 ,P_RT_COMP_REF_UOM
361 ,P_COMP_REF_UOM);
362 end insert_batch_lines;
363 --
364 procedure call
365 (p_business_group_id in number,
366 p_batch_line_id in number) is
367 cursor cr is
368 select l.rowid myrowid,
369 l.pval001 p1,
370 decode(l.pval002,cn,dn,d(l.pval002)) p2,
371 decode(l.pval003,cn,dn,d(l.pval003)) p3,
372 decode(l.pval004,cn,vn,vn,vn,
373 hr_pump_get.gl(l.pval004,'YES_NO',d(l.pval080),vn)) p4,
374 l.pval004 d4,
375 decode(l.pval005,cn,dn,vn,dn,d(l.pval005)) p5,
376 l.pval005 d5,
377 decode(l.pval006,cn,vn,vn,vn,
378 hr_pump_get.gl(l.pval006,'YES_NO',d(l.pval080),vn)) p6,
379 l.pval006 d6,
380 decode(l.pval007,cn,vn,vn,vn,
381 hr_pump_get.gl(l.pval007,'YES_NO',d(l.pval080),vn)) p7,
382 l.pval007 d7,
383 decode(l.pval008,cn,dn,vn,dn,d(l.pval008)) p8,
384 l.pval008 d8,
385 decode(l.pval009,cn,dn,vn,dn,d(l.pval009)) p9,
386 l.pval009 d9,
387 decode(l.pval010,cn,dn,vn,dn,d(l.pval010)) p10,
388 l.pval010 d10,
389 decode(l.pval011,cn,dn,vn,dn,d(l.pval011)) p11,
390 l.pval011 d11,
391 decode(l.pval012,cn,vn,vn,vn,
392 hr_pump_get.gl(l.pval012,'BEN_PRTN_OVRRIDN_RSN',d(l.pval080),vn)) p12,
393 l.pval012 d12,
394 decode(l.pval013,cn,nn,vn,nn,n(l.pval013)) p13,
395 l.pval013 d13,
396 decode(l.pval014,cn,nn,vn,nn,n(l.pval014)) p14,
397 l.pval014 d14,
398 decode(l.pval015,cn,nn,vn,nn,n(l.pval015)) p15,
399 l.pval015 d15,
400 decode(l.pval016,cn,nn,vn,nn,n(l.pval016)) p16,
401 l.pval016 d16,
402 decode(l.pval017,cn,nn,vn,nn,n(l.pval017)) p17,
403 l.pval017 d17,
404 decode(l.pval018,cn,nn,vn,nn,n(l.pval018)) p18,
405 l.pval018 d18,
406 decode(l.pval019,cn,vn,vn,vn,
407 hr_pump_get.gl(l.pval019,'BEN_BNDRY_PERD',d(l.pval080),vn)) p19,
408 l.pval019 d19,
409 decode(l.pval020,cn,vn,vn,vn,
410 hr_pump_get.gl(l.pval020,'BEN_TM_UOM',d(l.pval080),vn)) p20,
411 l.pval020 d20,
412 decode(l.pval021,cn,vn,vn,vn,
413 hr_pump_get.gl(l.pval021,'BEN_TM_UOM',d(l.pval080),vn)) p21,
414 l.pval021 d21,
415 decode(l.pval022,cn,nn,vn,nn,n(l.pval022)) p22,
416 l.pval022 d22,
417 decode(l.pval023,cn,vn,vn,vn,
418 hr_pump_get.gl(l.pval023,'YES_NO',d(l.pval080),vn)) p23,
419 l.pval023 d23,
420 decode(l.pval024,cn,vn,vn,vn,
421 hr_pump_get.gl(l.pval024,'YES_NO',d(l.pval080),vn)) p24,
422 l.pval024 d24,
423 decode(l.pval025,cn,vn,vn,vn,
424 hr_pump_get.gl(l.pval025,'YES_NO',d(l.pval080),vn)) p25,
425 l.pval025 d25,
426 decode(l.pval026,cn,vn,vn,vn,
427 hr_pump_get.gl(l.pval026,'YES_NO',d(l.pval080),vn)) p26,
428 l.pval026 d26,
429 decode(l.pval027,cn,vn,vn,vn,
430 hr_pump_get.gl(l.pval027,'YES_NO',d(l.pval080),vn)) p27,
431 l.pval027 d27,
432 decode(l.pval028,cn,vn,vn,vn,
433 hr_pump_get.gl(l.pval028,'YES_NO',d(l.pval080),vn)) p28,
434 l.pval028 d28,
435 decode(l.pval029,cn,nn,vn,nn,n(l.pval029)) p29,
436 l.pval029 d29,
437 decode(l.pval030,cn,nn,vn,nn,n(l.pval030)) p30,
438 l.pval030 d30,
439 decode(l.pval031,cn,nn,vn,nn,n(l.pval031)) p31,
440 l.pval031 d31,
441 decode(l.pval032,cn,nn,vn,nn,n(l.pval032)) p32,
442 l.pval032 d32,
443 decode(l.pval033,cn,nn,vn,nn,n(l.pval033)) p33,
444 l.pval033 d33,
445 decode(l.pval034,cn,vn,vn,vn,
446 hr_pump_get.gl(l.pval034,'BEN_BNDRY_PERD',d(l.pval080),vn)) p34,
447 l.pval034 d34,
448 decode(l.pval035,cn,vn,vn,vn,
449 hr_pump_get.gl(l.pval035,'BEN_TM_UOM',d(l.pval080),vn)) p35,
450 l.pval035 d35,
451 decode(l.pval036,cn,vn,vn,vn,
452 hr_pump_get.gl(l.pval036,'BEN_TM_UOM',d(l.pval080),vn)) p36,
453 l.pval036 d36,
454 decode(l.pval037,cn,vn,vn,vn,
455 hr_pump_get.gl(l.pval037,'YES_NO',d(l.pval080),vn)) p37,
456 l.pval037 d37,
457 decode(l.pval038,cn,vn,vn,vn,
458 hr_pump_get.gl(l.pval038,'YES_NO',d(l.pval080),vn)) p38,
459 l.pval038 d38,
460 decode(l.pval039,cn,vn,vn,vn,
461 hr_pump_get.gl(l.pval039,'YES_NO',d(l.pval080),vn)) p39,
462 l.pval039 d39,
463 decode(l.pval040,cn,vn,vn,vn,
464 hr_pump_get.gl(l.pval040,'YES_NO',d(l.pval080),vn)) p40,
465 l.pval040 d40,
466 decode(l.pval041,cn,vn,vn,vn,
467 hr_pump_get.gl(l.pval041,'YES_NO',d(l.pval080),vn)) p41,
468 l.pval041 d41,
469 decode(l.pval042,cn,vn,vn,vn,
470 hr_pump_get.gl(l.pval042,'YES_NO',d(l.pval080),vn)) p42,
471 l.pval042 d42,
472 decode(l.pval043,cn,dn,vn,dn,d(l.pval043)) p43,
473 l.pval043 d43,
474 decode(l.pval044,cn,vn,vn,vn,
475 hr_pump_get.gl(l.pval044,'BEN_INELG_RSN',d(l.pval080),vn)) p44,
476 l.pval044 d44,
480 decode(l.pval046,cn,nn,vn,nn,n(l.pval046)) p46,
477 decode(l.pval045,cn,vn,vn,vn,
478 hr_pump_get.gl(l.pval045,'BEN_ONCE_R_CNTNG',d(l.pval080),vn)) p45,
479 l.pval045 d45,
481 l.pval046 d46,
482 decode(l.pval047,cn,vn,vn,vn,l.pval047) p47,
483 l.pval047 d47,
484 decode(l.pval048,cn,vn,vn,vn,l.pval048) p48,
485 l.pval048 d48,
486 decode(l.pval049,cn,vn,vn,vn,l.pval049) p49,
487 l.pval049 d49,
488 decode(l.pval050,cn,vn,vn,vn,l.pval050) p50,
489 l.pval050 d50,
490 decode(l.pval051,cn,vn,vn,vn,l.pval051) p51,
491 l.pval051 d51,
492 decode(l.pval052,cn,vn,vn,vn,l.pval052) p52,
493 l.pval052 d52,
494 decode(l.pval053,cn,vn,vn,vn,l.pval053) p53,
495 l.pval053 d53,
496 decode(l.pval054,cn,vn,vn,vn,l.pval054) p54,
497 l.pval054 d54,
498 decode(l.pval055,cn,vn,vn,vn,l.pval055) p55,
499 l.pval055 d55,
500 decode(l.pval056,cn,vn,vn,vn,l.pval056) p56,
501 l.pval056 d56,
502 decode(l.pval057,cn,vn,vn,vn,l.pval057) p57,
503 l.pval057 d57,
504 decode(l.pval058,cn,vn,vn,vn,l.pval058) p58,
505 l.pval058 d58,
506 decode(l.pval059,cn,vn,vn,vn,l.pval059) p59,
507 l.pval059 d59,
508 decode(l.pval060,cn,vn,vn,vn,l.pval060) p60,
509 l.pval060 d60,
510 decode(l.pval061,cn,vn,vn,vn,l.pval061) p61,
511 l.pval061 d61,
512 decode(l.pval062,cn,vn,vn,vn,l.pval062) p62,
513 l.pval062 d62,
514 decode(l.pval063,cn,vn,vn,vn,l.pval063) p63,
515 l.pval063 d63,
516 decode(l.pval064,cn,vn,vn,vn,l.pval064) p64,
517 l.pval064 d64,
518 decode(l.pval065,cn,vn,vn,vn,l.pval065) p65,
519 l.pval065 d65,
520 decode(l.pval066,cn,vn,vn,vn,l.pval066) p66,
521 l.pval066 d66,
522 decode(l.pval067,cn,vn,vn,vn,l.pval067) p67,
523 l.pval067 d67,
524 decode(l.pval068,cn,vn,vn,vn,l.pval068) p68,
525 l.pval068 d68,
526 decode(l.pval069,cn,vn,vn,vn,l.pval069) p69,
527 l.pval069 d69,
528 decode(l.pval070,cn,vn,vn,vn,l.pval070) p70,
529 l.pval070 d70,
530 decode(l.pval071,cn,vn,vn,vn,l.pval071) p71,
531 l.pval071 d71,
532 decode(l.pval072,cn,vn,vn,vn,l.pval072) p72,
533 l.pval072 d72,
534 decode(l.pval073,cn,vn,vn,vn,l.pval073) p73,
535 l.pval073 d73,
536 decode(l.pval074,cn,vn,vn,vn,l.pval074) p74,
537 l.pval074 d74,
538 decode(l.pval075,cn,vn,vn,vn,l.pval075) p75,
539 l.pval075 d75,
540 decode(l.pval076,cn,vn,vn,vn,l.pval076) p76,
541 l.pval076 d76,
542 decode(l.pval077,cn,vn,vn,vn,l.pval077) p77,
543 l.pval077 d77,
544 decode(l.pval078,cn,dn,vn,dn,d(l.pval078)) p78,
545 l.pval078 d78,
546 l.pval079 p79,
547 decode(l.pval080,cn,dn,d(l.pval080)) p80,
548 decode(l.pval081,cn,vn,vn,null,l.pval081) p81,
549 l.pval081 d81,
550 decode(l.pval082,cn,vn,vn,vn,l.pval082) p82,
551 l.pval082 d82,
552 decode(l.pval083,cn,vn,vn,vn,l.pval083) p83,
553 l.pval083 d83,
554 decode(l.pval084,cn,vn,vn,vn,l.pval084) p84,
555 l.pval084 d84,
556 decode(l.pval085,cn,vn,vn,vn,l.pval085) p85,
557 l.pval085 d85,
558 decode(l.pval086,cn,vn,vn,vn,l.pval086) p86,
559 l.pval086 d86
560 from hr_pump_batch_lines l
561 where l.batch_line_id = p_batch_line_id;
562 --
563 c cr%rowtype;
564 l_validate boolean := false;
565 L_ELIG_PER_OPT_ID number;
566 L_OVERRIDE_VALIDATION boolean;
567 L_ELIG_PER_ID number;
568 L_OPT_ID number;
569 L_PER_IN_LER_ID number;
570 L_RT_COMP_REF_UOM varchar2(2000);
571 L_COMP_REF_UOM varchar2(2000);
572 L_REQUEST_ID number;
573 L_PROGRAM_APPLICATION_ID number;
574 L_PROGRAM_ID number;
575 --
576 begin
577 hr_data_pump.entry('call');
578 open cr;
579 fetch cr into c;
580 if cr%notfound then
581 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
582 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
583 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
584 hr_utility.set_message_token('VALUE',p_batch_line_id);
585 hr_utility.raise_error;
586 end if;
587 --
588 if upper(c.p81) = 'TRUE' then
589 L_OVERRIDE_VALIDATION := true;
590 elsif upper(c.p81) = 'FALSE' then
591 L_OVERRIDE_VALIDATION := false;
592 elsif c.p81 is not null then
593 hr_utility.set_message(800,'HR_50327_DP_TYPE_ERR');
594 hr_utility.set_message_token('TYPE','BOOLEAN');
595 hr_utility.set_message_token('PARAMETER','P_OVERRIDE_VALIDATION');
596 hr_utility.set_message_token('VALUE',c.p81);
597 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
598 hr_utility.raise_error;
599 end if;
600 --
601 if c.p82 is null then
602 L_ELIG_PER_ID:=nn;
603 else
604 L_ELIG_PER_ID :=
605 hr_pump_get.get_elig_per_id
606 (P_ELIG_PER_USER_KEY => c.p82);
607 end if;
608 --
609 if c.p83 is null or
610 c.p80 is null then
611 L_OPT_ID:=nn;
612 else
613 L_OPT_ID :=
614 hr_pump_get.GET_OPT_ID
618 end if;
615 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
616 ,P_OPTION_DEFINITION => c.p83
617 ,P_EFFECTIVE_DATE => c.p80);
619 --
620 if c.p84 is null then
621 L_PER_IN_LER_ID:=nn;
622 else
623 L_PER_IN_LER_ID :=
624 hr_pump_get.get_per_in_ler_id
625 (P_PER_IN_LER_USER_KEY => c.p84);
626 end if;
627 --
628 if c.p85 is null or
629 c.p80 is null then
630 L_RT_COMP_REF_UOM:=vn;
631 else
632 L_RT_COMP_REF_UOM :=
633 hr_pump_get.GET_RT_COMP_REF_UOM_CODE
634 (P_RT_COMP_REF_UOM => c.p85
635 ,P_EFFECTIVE_DATE => c.p80);
636 end if;
637 --
638 if c.p86 is null or
639 c.p80 is null then
640 L_COMP_REF_UOM:=vn;
641 else
642 L_COMP_REF_UOM :=
643 hr_pump_get.GET_COMP_REF_UOM_CODE
644 (P_COMP_REF_UOM => c.p86
645 ,P_EFFECTIVE_DATE => c.p80);
646 end if;
647 --
648 L_REQUEST_ID :=
649 hr_pump_get.get_request_id;
650 --
651 L_PROGRAM_APPLICATION_ID :=
652 hr_pump_get.get_program_application_id;
653 --
654 L_PROGRAM_ID :=
655 hr_pump_get.get_program_id;
656 --
657 hr_data_pump.api_trc_on;
658 ben_elig_person_option_api.CREATE_ELIG_PERSON_OPTION
659 (p_validate => l_validate
660 ,P_ELIG_PER_OPT_ID => L_ELIG_PER_OPT_ID
661 ,P_ELIG_PER_ID => L_ELIG_PER_ID
662 ,P_EFFECTIVE_START_DATE => c.p2
663 ,P_EFFECTIVE_END_DATE => c.p3
664 ,P_PRTN_OVRIDN_FLAG => c.p4
665 ,P_PRTN_OVRIDN_THRU_DT => c.p5
666 ,P_NO_MX_PRTN_OVRID_THRU_FLAG => c.p6
667 ,P_ELIG_FLAG => c.p7
668 ,P_PRTN_STRT_DT => c.p8
669 ,P_PRTN_END_DT => c.p9
670 ,P_WAIT_PERD_CMPLTN_DATE => c.p10
671 ,P_WAIT_PERD_STRT_DT => c.p11
672 ,P_PRTN_OVRIDN_RSN_CD => c.p12
673 ,P_PCT_FL_TM_VAL => c.p13
674 ,P_OPT_ID => L_OPT_ID
675 ,P_PER_IN_LER_ID => L_PER_IN_LER_ID
676 ,P_RT_COMP_REF_AMT => c.p14
677 ,P_RT_CMBN_AGE_N_LOS_VAL => c.p15
678 ,P_RT_COMP_REF_UOM => L_RT_COMP_REF_UOM
679 ,P_RT_AGE_VAL => c.p16
680 ,P_RT_LOS_VAL => c.p17
681 ,P_RT_HRS_WKD_VAL => c.p18
682 ,P_RT_HRS_WKD_BNDRY_PERD_CD => c.p19
683 ,P_RT_AGE_UOM => c.p20
684 ,P_RT_LOS_UOM => c.p21
685 ,P_RT_PCT_FL_TM_VAL => c.p22
686 ,P_RT_FRZ_LOS_FLAG => c.p23
687 ,P_RT_FRZ_AGE_FLAG => c.p24
688 ,P_RT_FRZ_CMP_LVL_FLAG => c.p25
689 ,P_RT_FRZ_PCT_FL_TM_FLAG => c.p26
690 ,P_RT_FRZ_HRS_WKD_FLAG => c.p27
691 ,P_RT_FRZ_COMB_AGE_AND_LOS_FLAG => c.p28
692 ,P_COMP_REF_AMT => c.p29
693 ,P_CMBN_AGE_N_LOS_VAL => c.p30
694 ,P_COMP_REF_UOM => L_COMP_REF_UOM
695 ,P_AGE_VAL => c.p31
696 ,P_LOS_VAL => c.p32
697 ,P_HRS_WKD_VAL => c.p33
698 ,P_HRS_WKD_BNDRY_PERD_CD => c.p34
699 ,P_AGE_UOM => c.p35
700 ,P_LOS_UOM => c.p36
701 ,P_FRZ_LOS_FLAG => c.p37
702 ,P_FRZ_AGE_FLAG => c.p38
703 ,P_FRZ_CMP_LVL_FLAG => c.p39
704 ,P_FRZ_PCT_FL_TM_FLAG => c.p40
705 ,P_FRZ_HRS_WKD_FLAG => c.p41
706 ,P_FRZ_COMB_AGE_AND_LOS_FLAG => c.p42
707 ,P_OVRID_SVC_DT => c.p43
708 ,P_INELG_RSN_CD => c.p44
709 ,P_ONCE_R_CNTUG_CD => c.p45
710 ,P_OIPL_ORDR_NUM => c.p46
711 ,p_business_group_id => p_business_group_id
712 ,P_EPO_ATTRIBUTE_CATEGORY => c.p47
713 ,P_EPO_ATTRIBUTE1 => c.p48
714 ,P_EPO_ATTRIBUTE2 => c.p49
715 ,P_EPO_ATTRIBUTE3 => c.p50
716 ,P_EPO_ATTRIBUTE4 => c.p51
717 ,P_EPO_ATTRIBUTE5 => c.p52
718 ,P_EPO_ATTRIBUTE6 => c.p53
719 ,P_EPO_ATTRIBUTE7 => c.p54
720 ,P_EPO_ATTRIBUTE8 => c.p55
721 ,P_EPO_ATTRIBUTE9 => c.p56
722 ,P_EPO_ATTRIBUTE10 => c.p57
723 ,P_EPO_ATTRIBUTE11 => c.p58
724 ,P_EPO_ATTRIBUTE12 => c.p59
725 ,P_EPO_ATTRIBUTE13 => c.p60
726 ,P_EPO_ATTRIBUTE14 => c.p61
727 ,P_EPO_ATTRIBUTE15 => c.p62
728 ,P_EPO_ATTRIBUTE16 => c.p63
729 ,P_EPO_ATTRIBUTE17 => c.p64
730 ,P_EPO_ATTRIBUTE18 => c.p65
731 ,P_EPO_ATTRIBUTE19 => c.p66
732 ,P_EPO_ATTRIBUTE20 => c.p67
733 ,P_EPO_ATTRIBUTE21 => c.p68
734 ,P_EPO_ATTRIBUTE22 => c.p69
735 ,P_EPO_ATTRIBUTE23 => c.p70
736 ,P_EPO_ATTRIBUTE24 => c.p71
737 ,P_EPO_ATTRIBUTE25 => c.p72
738 ,P_EPO_ATTRIBUTE26 => c.p73
739 ,P_EPO_ATTRIBUTE27 => c.p74
740 ,P_EPO_ATTRIBUTE28 => c.p75
741 ,P_EPO_ATTRIBUTE29 => c.p76
742 ,P_EPO_ATTRIBUTE30 => c.p77
743 ,P_REQUEST_ID => L_REQUEST_ID
744 ,P_PROGRAM_APPLICATION_ID => L_PROGRAM_APPLICATION_ID
745 ,P_PROGRAM_ID => L_PROGRAM_ID
746 ,P_PROGRAM_UPDATE_DATE => c.p78
747 ,P_OBJECT_VERSION_NUMBER => c.p79
748 ,P_EFFECTIVE_DATE => c.p80
749 ,P_OVERRIDE_VALIDATION => L_OVERRIDE_VALIDATION);
750 hr_data_pump.api_trc_off;
751 --
752 iuk(p_batch_line_id,c.p1,L_ELIG_PER_OPT_ID);
753 --
754 update hr_pump_batch_lines l set
755 l.pval001 = decode(c.p1,null,cn,c.p1),
756 l.pval002 = decode(c.p2,null,cn,dc(c.p2)),
757 l.pval003 = decode(c.p3,null,cn,dc(c.p3)),
758 l.pval079 = decode(c.p79,null,cn,c.p79)
759 where l.rowid = c.myrowid;
760 --
761 close cr;
762 --
763 hr_data_pump.exit('call');
764 exception
765 when hr_multi_message.error_message_exist then
766 if cr%isopen then
767 close cr;
768 end if;
769 hr_pump_utils.set_multi_msg_error_flag(true);
770 when others then
771 if cr%isopen then
772 close cr;
773 end if;
774 raise;
775 end call;
776 end hrdpp_CREATE_ELIG_PERSON_OPTIO;