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_UPDATE_ELIGIBLE_PERSON

Source


1 package body hrdpp_UPDATE_ELIGIBLE_PERSON as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2012/11/27 04:11:10
4  * Generated for API: ben_eligible_person_api.UPDATE_ELIGIBLE_PERSON
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_PER_IN_LER_ID in varchar2 default null
83 ,P_DPNT_OTHR_PL_CVRD_RL_FLAG in varchar2 default null
84 ,P_PRTN_OVRIDN_THRU_DT in date default null
85 ,I_PRTN_OVRIDN_THRU_DT in varchar2 default 'N'
86 ,P_PL_KEY_EE_FLAG in varchar2 default null
87 ,P_PL_HGHLY_COMPD_FLAG in varchar2 default null
88 ,P_ELIG_FLAG in varchar2 default null
89 ,P_COMP_REF_AMT in number default null
90 ,I_COMP_REF_AMT in varchar2 default 'N'
91 ,P_CMBN_AGE_N_LOS_VAL in number default null
92 ,I_CMBN_AGE_N_LOS_VAL in varchar2 default 'N'
93 ,P_AGE_VAL in number default null
94 ,I_AGE_VAL in varchar2 default 'N'
95 ,P_LOS_VAL in number default null
96 ,I_LOS_VAL in varchar2 default 'N'
97 ,P_PRTN_END_DT in date default null
98 ,I_PRTN_END_DT in varchar2 default 'N'
99 ,P_PRTN_STRT_DT in date default null
100 ,I_PRTN_STRT_DT in varchar2 default 'N'
101 ,P_WAIT_PERD_CMPLTN_DT in date default null
102 ,I_WAIT_PERD_CMPLTN_DT in varchar2 default 'N'
103 ,P_WAIT_PERD_STRT_DT in date default null
104 ,I_WAIT_PERD_STRT_DT in varchar2 default 'N'
105 ,P_WV_CTFN_TYP_CD in varchar2 default null
106 ,P_HRS_WKD_VAL in number default null
107 ,I_HRS_WKD_VAL in varchar2 default 'N'
108 ,P_HRS_WKD_BNDRY_PERD_CD in varchar2 default null
109 ,P_PRTN_OVRIDN_FLAG in varchar2 default null
110 ,P_NO_MX_PRTN_OVRID_THRU_FLAG in varchar2 default null
111 ,P_PRTN_OVRIDN_RSN_CD in varchar2 default null
112 ,P_AGE_UOM in varchar2 default null
113 ,P_LOS_UOM in varchar2 default null
114 ,P_OVRID_SVC_DT in date default null
115 ,I_OVRID_SVC_DT in varchar2 default 'N'
116 ,P_INELG_RSN_CD in varchar2 default null
117 ,P_FRZ_LOS_FLAG in varchar2 default null
118 ,P_FRZ_AGE_FLAG in varchar2 default null
119 ,P_FRZ_CMP_LVL_FLAG in varchar2 default null
120 ,P_FRZ_PCT_FL_TM_FLAG in varchar2 default null
121 ,P_FRZ_HRS_WKD_FLAG in varchar2 default null
122 ,P_FRZ_COMB_AGE_AND_LOS_FLAG in varchar2 default null
123 ,P_DSTR_RSTCN_FLAG in varchar2 default null
124 ,P_PCT_FL_TM_VAL in number default null
125 ,I_PCT_FL_TM_VAL in varchar2 default 'N'
126 ,P_WV_PRTN_RSN_CD in varchar2 default null
127 ,P_PL_WVD_FLAG in varchar2 default null
128 ,P_RT_COMP_REF_AMT in number default null
129 ,I_RT_COMP_REF_AMT in varchar2 default 'N'
130 ,P_RT_CMBN_AGE_N_LOS_VAL in number default null
131 ,I_RT_CMBN_AGE_N_LOS_VAL in varchar2 default 'N'
132 ,P_RT_AGE_VAL in number default null
133 ,I_RT_AGE_VAL in varchar2 default 'N'
134 ,P_RT_LOS_VAL in number default null
135 ,I_RT_LOS_VAL in varchar2 default 'N'
136 ,P_RT_HRS_WKD_VAL in number default null
137 ,I_RT_HRS_WKD_VAL in varchar2 default 'N'
141 ,P_RT_PCT_FL_TM_VAL in number default null
138 ,P_RT_HRS_WKD_BNDRY_PERD_CD in varchar2 default null
139 ,P_RT_AGE_UOM in varchar2 default null
140 ,P_RT_LOS_UOM in varchar2 default null
142 ,I_RT_PCT_FL_TM_VAL in varchar2 default 'N'
143 ,P_RT_FRZ_LOS_FLAG in varchar2 default null
144 ,P_RT_FRZ_AGE_FLAG in varchar2 default null
145 ,P_RT_FRZ_CMP_LVL_FLAG in varchar2 default null
146 ,P_RT_FRZ_PCT_FL_TM_FLAG in varchar2 default null
147 ,P_RT_FRZ_HRS_WKD_FLAG in varchar2 default null
148 ,P_RT_FRZ_COMB_AGE_AND_LOS_FLAG in varchar2 default null
149 ,P_ONCE_R_CNTUG_CD in varchar2 default null
150 ,P_PL_ORDR_NUM in number default null
151 ,I_PL_ORDR_NUM in varchar2 default 'N'
152 ,P_PLIP_ORDR_NUM in number default null
153 ,I_PLIP_ORDR_NUM in varchar2 default 'N'
154 ,P_PTIP_ORDR_NUM in number default null
155 ,I_PTIP_ORDR_NUM in varchar2 default 'N'
156 ,P_PEP_ATTRIBUTE_CATEGORY in varchar2 default null
157 ,P_PEP_ATTRIBUTE1 in varchar2 default null
158 ,P_PEP_ATTRIBUTE2 in varchar2 default null
159 ,P_PEP_ATTRIBUTE3 in varchar2 default null
160 ,P_PEP_ATTRIBUTE4 in varchar2 default null
161 ,P_PEP_ATTRIBUTE5 in varchar2 default null
162 ,P_PEP_ATTRIBUTE6 in varchar2 default null
163 ,P_PEP_ATTRIBUTE7 in varchar2 default null
164 ,P_PEP_ATTRIBUTE8 in varchar2 default null
165 ,P_PEP_ATTRIBUTE9 in varchar2 default null
166 ,P_PEP_ATTRIBUTE10 in varchar2 default null
167 ,P_PEP_ATTRIBUTE11 in varchar2 default null
168 ,P_PEP_ATTRIBUTE12 in varchar2 default null
169 ,P_PEP_ATTRIBUTE13 in varchar2 default null
170 ,P_PEP_ATTRIBUTE14 in varchar2 default null
171 ,P_PEP_ATTRIBUTE15 in varchar2 default null
172 ,P_PEP_ATTRIBUTE16 in varchar2 default null
173 ,P_PEP_ATTRIBUTE17 in varchar2 default null
174 ,P_PEP_ATTRIBUTE18 in varchar2 default null
175 ,P_PEP_ATTRIBUTE19 in varchar2 default null
176 ,P_PEP_ATTRIBUTE20 in varchar2 default null
177 ,P_PEP_ATTRIBUTE21 in varchar2 default null
178 ,P_PEP_ATTRIBUTE22 in varchar2 default null
179 ,P_PEP_ATTRIBUTE23 in varchar2 default null
180 ,P_PEP_ATTRIBUTE24 in varchar2 default null
181 ,P_PEP_ATTRIBUTE25 in varchar2 default null
182 ,P_PEP_ATTRIBUTE26 in varchar2 default null
183 ,P_PEP_ATTRIBUTE27 in varchar2 default null
184 ,P_PEP_ATTRIBUTE28 in varchar2 default null
185 ,P_PEP_ATTRIBUTE29 in varchar2 default null
186 ,P_PEP_ATTRIBUTE30 in varchar2 default null
187 ,P_PROGRAM_UPDATE_DATE in date default null
188 ,I_PROGRAM_UPDATE_DATE in varchar2 default 'N'
189 ,P_EFFECTIVE_DATE in date
190 ,P_DATETRACK_MODE in varchar2
191 ,P_ELIG_PER_USER_KEY in varchar2
192 ,P_PLAN in varchar2 default null
193 ,P_PROGRAM in varchar2 default null
194 ,P_PLIP_USER_KEY in varchar2 default null
195 ,P_PTIP_USER_KEY in varchar2 default null
196 ,P_LIFE_EVENT_REASON in varchar2 default null
197 ,P_PERSON_USER_KEY in varchar2 default null
198 ,P_COMP_REF_UOM in varchar2 default null
199 ,P_RT_COMP_REF_UOM in varchar2 default null) is
200 blid number := p_data_pump_batch_line_id;
201 begin
202 if blid is not null then
203 delete from hr_pump_batch_lines where batch_line_id = blid;
204 delete from hr_pump_batch_exceptions
205 where source_type = 'BATCH_LINE' and source_id = blid;
206 end if;
207 insert into hr_pump_batch_lines
208 (batch_id
209 ,batch_line_id
210 ,business_group_name
211 ,api_module_id
212 ,line_status
213 ,user_sequence
214 ,link_value
215 ,pval003
216 ,pval004
217 ,pval005
218 ,pval006
219 ,pval007
220 ,pval008
221 ,pval009
222 ,pval010
223 ,pval011
224 ,pval012
225 ,pval013
226 ,pval014
227 ,pval015
228 ,pval016
229 ,pval017
230 ,pval018
231 ,pval019
232 ,pval020
233 ,pval021
234 ,pval022
235 ,pval023
236 ,pval024
237 ,pval025
238 ,pval026
239 ,pval027
240 ,pval028
241 ,pval029
242 ,pval030
243 ,pval031
244 ,pval032
245 ,pval033
246 ,pval034
247 ,pval035
248 ,pval036
249 ,pval037
250 ,pval038
251 ,pval039
252 ,pval040
253 ,pval041
254 ,pval042
255 ,pval043
256 ,pval044
257 ,pval045
258 ,pval046
259 ,pval047
260 ,pval048
261 ,pval049
262 ,pval050
263 ,pval051
264 ,pval052
265 ,pval053
266 ,pval054
267 ,pval055
268 ,pval056
269 ,pval057
270 ,pval058
271 ,pval059
272 ,pval060
273 ,pval061
274 ,pval062
275 ,pval063
276 ,pval064
277 ,pval065
278 ,pval066
279 ,pval067
280 ,pval068
281 ,pval069
282 ,pval070
283 ,pval071
284 ,pval072
285 ,pval073
286 ,pval074
287 ,pval075
288 ,pval076
289 ,pval077
290 ,pval078
291 ,pval079
292 ,pval080
293 ,pval081
294 ,pval082
295 ,pval083
296 ,pval084
297 ,pval085
298 ,pval086
299 ,pval087
300 ,pval088
301 ,pval089
302 ,pval090
303 ,pval091
304 ,pval092
305 ,pval093
306 ,pval094
307 ,pval095
308 ,pval096
309 ,pval097
310 ,pval098)
311 values
312 (p_batch_id
313 ,nvl(blid,hr_pump_batch_lines_s.nextval)
314 ,p_data_pump_business_grp_name
315 ,626
316 ,'U'
317 ,p_user_sequence
318 ,p_link_value
319 ,P_PER_IN_LER_ID
320 ,P_DPNT_OTHR_PL_CVRD_RL_FLAG
321 ,dd(P_PRTN_OVRIDN_THRU_DT,I_PRTN_OVRIDN_THRU_DT)
322 ,P_PL_KEY_EE_FLAG
323 ,P_PL_HGHLY_COMPD_FLAG
324 ,P_ELIG_FLAG
325 ,nd(P_COMP_REF_AMT,I_COMP_REF_AMT)
326 ,nd(P_CMBN_AGE_N_LOS_VAL,I_CMBN_AGE_N_LOS_VAL)
327 ,nd(P_AGE_VAL,I_AGE_VAL)
328 ,nd(P_LOS_VAL,I_LOS_VAL)
329 ,dd(P_PRTN_END_DT,I_PRTN_END_DT)
330 ,dd(P_PRTN_STRT_DT,I_PRTN_STRT_DT)
334 ,nd(P_HRS_WKD_VAL,I_HRS_WKD_VAL)
331 ,dd(P_WAIT_PERD_CMPLTN_DT,I_WAIT_PERD_CMPLTN_DT)
332 ,dd(P_WAIT_PERD_STRT_DT,I_WAIT_PERD_STRT_DT)
333 ,P_WV_CTFN_TYP_CD
335 ,P_HRS_WKD_BNDRY_PERD_CD
336 ,P_PRTN_OVRIDN_FLAG
337 ,P_NO_MX_PRTN_OVRID_THRU_FLAG
338 ,P_PRTN_OVRIDN_RSN_CD
339 ,P_AGE_UOM
340 ,P_LOS_UOM
341 ,dd(P_OVRID_SVC_DT,I_OVRID_SVC_DT)
342 ,P_INELG_RSN_CD
343 ,P_FRZ_LOS_FLAG
344 ,P_FRZ_AGE_FLAG
345 ,P_FRZ_CMP_LVL_FLAG
346 ,P_FRZ_PCT_FL_TM_FLAG
347 ,P_FRZ_HRS_WKD_FLAG
348 ,P_FRZ_COMB_AGE_AND_LOS_FLAG
349 ,P_DSTR_RSTCN_FLAG
350 ,nd(P_PCT_FL_TM_VAL,I_PCT_FL_TM_VAL)
351 ,P_WV_PRTN_RSN_CD
352 ,P_PL_WVD_FLAG
353 ,nd(P_RT_COMP_REF_AMT,I_RT_COMP_REF_AMT)
354 ,nd(P_RT_CMBN_AGE_N_LOS_VAL,I_RT_CMBN_AGE_N_LOS_VAL)
355 ,nd(P_RT_AGE_VAL,I_RT_AGE_VAL)
356 ,nd(P_RT_LOS_VAL,I_RT_LOS_VAL)
357 ,nd(P_RT_HRS_WKD_VAL,I_RT_HRS_WKD_VAL)
358 ,P_RT_HRS_WKD_BNDRY_PERD_CD
359 ,P_RT_AGE_UOM
360 ,P_RT_LOS_UOM
361 ,nd(P_RT_PCT_FL_TM_VAL,I_RT_PCT_FL_TM_VAL)
362 ,P_RT_FRZ_LOS_FLAG
363 ,P_RT_FRZ_AGE_FLAG
364 ,P_RT_FRZ_CMP_LVL_FLAG
365 ,P_RT_FRZ_PCT_FL_TM_FLAG
366 ,P_RT_FRZ_HRS_WKD_FLAG
367 ,P_RT_FRZ_COMB_AGE_AND_LOS_FLAG
368 ,P_ONCE_R_CNTUG_CD
369 ,nd(P_PL_ORDR_NUM,I_PL_ORDR_NUM)
370 ,nd(P_PLIP_ORDR_NUM,I_PLIP_ORDR_NUM)
371 ,nd(P_PTIP_ORDR_NUM,I_PTIP_ORDR_NUM)
372 ,P_PEP_ATTRIBUTE_CATEGORY
373 ,P_PEP_ATTRIBUTE1
374 ,P_PEP_ATTRIBUTE2
375 ,P_PEP_ATTRIBUTE3
376 ,P_PEP_ATTRIBUTE4
377 ,P_PEP_ATTRIBUTE5
378 ,P_PEP_ATTRIBUTE6
379 ,P_PEP_ATTRIBUTE7
380 ,P_PEP_ATTRIBUTE8
381 ,P_PEP_ATTRIBUTE9
382 ,P_PEP_ATTRIBUTE10
383 ,P_PEP_ATTRIBUTE11
384 ,P_PEP_ATTRIBUTE12
385 ,P_PEP_ATTRIBUTE13
386 ,P_PEP_ATTRIBUTE14
387 ,P_PEP_ATTRIBUTE15
388 ,P_PEP_ATTRIBUTE16
389 ,P_PEP_ATTRIBUTE17
390 ,P_PEP_ATTRIBUTE18
391 ,P_PEP_ATTRIBUTE19
392 ,P_PEP_ATTRIBUTE20
393 ,P_PEP_ATTRIBUTE21
394 ,P_PEP_ATTRIBUTE22
395 ,P_PEP_ATTRIBUTE23
396 ,P_PEP_ATTRIBUTE24
397 ,P_PEP_ATTRIBUTE25
398 ,P_PEP_ATTRIBUTE26
399 ,P_PEP_ATTRIBUTE27
400 ,P_PEP_ATTRIBUTE28
401 ,P_PEP_ATTRIBUTE29
402 ,P_PEP_ATTRIBUTE30
403 ,dd(P_PROGRAM_UPDATE_DATE,I_PROGRAM_UPDATE_DATE)
404 ,dc(P_EFFECTIVE_DATE)
405 ,P_DATETRACK_MODE
406 ,P_ELIG_PER_USER_KEY
407 ,P_PLAN
408 ,P_PROGRAM
409 ,P_PLIP_USER_KEY
410 ,P_PTIP_USER_KEY
411 ,P_LIFE_EVENT_REASON
412 ,P_PERSON_USER_KEY
413 ,P_COMP_REF_UOM
414 ,P_RT_COMP_REF_UOM);
415 end insert_batch_lines;
416 --
417 procedure call
418 (p_business_group_id in number,
419 p_batch_line_id     in number) is
420 cursor cr is
421 select l.rowid myrowid,
422 decode(l.pval001,cn,dn,d(l.pval001)) p1,
423 decode(l.pval002,cn,dn,d(l.pval002)) p2,
424 decode(l.pval003,cn,nn,vn,vn,n(l.pval003)) p3,
425 l.pval003 d3,
426 decode(l.pval004,cn,vn,vn,vh,
427  hr_pump_get.gl(l.pval004,'YES_NO',d(l.pval088),vn)) p4,
428 l.pval004 d4,
429 decode(l.pval005,cn,dn,vn,dh,d(l.pval005)) p5,
430 l.pval005 d5,
431 decode(l.pval006,cn,vn,vn,vh,
432  hr_pump_get.gl(l.pval006,'YES_NO',d(l.pval088),vn)) p6,
433 l.pval006 d6,
434 decode(l.pval007,cn,vn,vn,vh,
435  hr_pump_get.gl(l.pval007,'YES_NO',d(l.pval088),vn)) p7,
436 l.pval007 d7,
437 decode(l.pval008,cn,vn,vn,vh,
438  hr_pump_get.gl(l.pval008,'YES_NO',d(l.pval088),vn)) p8,
439 l.pval008 d8,
440 decode(l.pval009,cn,nn,vn,nh,n(l.pval009)) p9,
441 l.pval009 d9,
442 decode(l.pval010,cn,nn,vn,nh,n(l.pval010)) p10,
443 l.pval010 d10,
444 decode(l.pval011,cn,nn,vn,nh,n(l.pval011)) p11,
445 l.pval011 d11,
446 decode(l.pval012,cn,nn,vn,nh,n(l.pval012)) p12,
447 l.pval012 d12,
448 decode(l.pval013,cn,dn,vn,dh,d(l.pval013)) p13,
449 l.pval013 d13,
450 decode(l.pval014,cn,dn,vn,dh,d(l.pval014)) p14,
451 l.pval014 d14,
452 decode(l.pval015,cn,dn,vn,dh,d(l.pval015)) p15,
453 l.pval015 d15,
454 decode(l.pval016,cn,dn,vn,dh,d(l.pval016)) p16,
455 l.pval016 d16,
456 decode(l.pval017,cn,vn,vn,vh,
457  hr_pump_get.gl(l.pval017,'BEN_WV_PRTN_CTFN_TYP',d(l.pval088),vn)) p17,
458 l.pval017 d17,
459 decode(l.pval018,cn,nn,vn,nh,n(l.pval018)) p18,
460 l.pval018 d18,
461 decode(l.pval019,cn,vn,vn,vh,
462  hr_pump_get.gl(l.pval019,'BEN_BNDRY_PERD',d(l.pval088),vn)) p19,
463 l.pval019 d19,
464 decode(l.pval020,cn,vn,vn,vh,
465  hr_pump_get.gl(l.pval020,'YES_NO',d(l.pval088),vn)) p20,
466 l.pval020 d20,
467 decode(l.pval021,cn,vn,vn,vh,
468  hr_pump_get.gl(l.pval021,'YES_NO',d(l.pval088),vn)) p21,
469 l.pval021 d21,
470 decode(l.pval022,cn,vn,vn,vh,
471  hr_pump_get.gl(l.pval022,'BEN_OVRID_RSN',d(l.pval088),vn)) p22,
472 l.pval022 d22,
473 decode(l.pval023,cn,vn,vn,vh,
474  hr_pump_get.gl(l.pval023,'BEN_TM_UOM',d(l.pval088),vn)) p23,
475 l.pval023 d23,
476 decode(l.pval024,cn,vn,vn,vh,
477  hr_pump_get.gl(l.pval024,'BEN_TM_UOM',d(l.pval088),vn)) p24,
478 l.pval024 d24,
479 decode(l.pval025,cn,dn,vn,dh,d(l.pval025)) p25,
480 l.pval025 d25,
481 decode(l.pval026,cn,vn,vn,vh,
482  hr_pump_get.gl(l.pval026,'BEN_INELG_RSN',d(l.pval088),vn)) p26,
483 l.pval026 d26,
484 decode(l.pval027,cn,vn,vn,vh,
485  hr_pump_get.gl(l.pval027,'YES_NO',d(l.pval088),vn)) p27,
486 l.pval027 d27,
487 decode(l.pval028,cn,vn,vn,vh,
488  hr_pump_get.gl(l.pval028,'YES_NO',d(l.pval088),vn)) p28,
489 l.pval028 d28,
490 decode(l.pval029,cn,vn,vn,vh,
491  hr_pump_get.gl(l.pval029,'YES_NO',d(l.pval088),vn)) p29,
492 l.pval029 d29,
493 decode(l.pval030,cn,vn,vn,vh,
494  hr_pump_get.gl(l.pval030,'YES_NO',d(l.pval088),vn)) p30,
498 l.pval031 d31,
495 l.pval030 d30,
496 decode(l.pval031,cn,vn,vn,vh,
497  hr_pump_get.gl(l.pval031,'YES_NO',d(l.pval088),vn)) p31,
499 decode(l.pval032,cn,vn,vn,vh,
500  hr_pump_get.gl(l.pval032,'YES_NO',d(l.pval088),vn)) p32,
501 l.pval032 d32,
502 decode(l.pval033,cn,vn,vn,vh,
503  hr_pump_get.gl(l.pval033,'YES_NO',d(l.pval088),vn)) p33,
504 l.pval033 d33,
505 decode(l.pval034,cn,nn,vn,nh,n(l.pval034)) p34,
506 l.pval034 d34,
507 decode(l.pval035,cn,vn,vn,vh,
508  hr_pump_get.gl(l.pval035,'BEN_WV_PRTN_RSN',d(l.pval088),vn)) p35,
509 l.pval035 d35,
510 decode(l.pval036,cn,vn,vn,vh,
511  hr_pump_get.gl(l.pval036,'YES_NO',d(l.pval088),vn)) p36,
512 l.pval036 d36,
513 decode(l.pval037,cn,nn,vn,nh,n(l.pval037)) p37,
514 l.pval037 d37,
515 decode(l.pval038,cn,nn,vn,nh,n(l.pval038)) p38,
516 l.pval038 d38,
517 decode(l.pval039,cn,nn,vn,nh,n(l.pval039)) p39,
518 l.pval039 d39,
519 decode(l.pval040,cn,nn,vn,nh,n(l.pval040)) p40,
520 l.pval040 d40,
521 decode(l.pval041,cn,nn,vn,nh,n(l.pval041)) p41,
522 l.pval041 d41,
523 decode(l.pval042,cn,vn,vn,vh,
524  hr_pump_get.gl(l.pval042,'BEN_BNDRY_PERD',d(l.pval088),vn)) p42,
525 l.pval042 d42,
526 decode(l.pval043,cn,vn,vn,vh,
527  hr_pump_get.gl(l.pval043,'BEN_TM_UOM',d(l.pval088),vn)) p43,
528 l.pval043 d43,
529 decode(l.pval044,cn,vn,vn,vh,
530  hr_pump_get.gl(l.pval044,'BEN_TM_UOM',d(l.pval088),vn)) p44,
531 l.pval044 d44,
532 decode(l.pval045,cn,nn,vn,nh,n(l.pval045)) p45,
533 l.pval045 d45,
534 decode(l.pval046,cn,vn,vn,vh,
535  hr_pump_get.gl(l.pval046,'YES_NO',d(l.pval088),vn)) p46,
536 l.pval046 d46,
537 decode(l.pval047,cn,vn,vn,vh,
538  hr_pump_get.gl(l.pval047,'YES_NO',d(l.pval088),vn)) p47,
539 l.pval047 d47,
540 decode(l.pval048,cn,vn,vn,vh,
541  hr_pump_get.gl(l.pval048,'YES_NO',d(l.pval088),vn)) p48,
542 l.pval048 d48,
543 decode(l.pval049,cn,vn,vn,vh,
544  hr_pump_get.gl(l.pval049,'YES_NO',d(l.pval088),vn)) p49,
545 l.pval049 d49,
546 decode(l.pval050,cn,vn,vn,vh,
547  hr_pump_get.gl(l.pval050,'YES_NO',d(l.pval088),vn)) p50,
548 l.pval050 d50,
549 decode(l.pval051,cn,vn,vn,vh,
550  hr_pump_get.gl(l.pval051,'YES_NO',d(l.pval088),vn)) p51,
551 l.pval051 d51,
552 decode(l.pval052,cn,vn,vn,vh,
553  hr_pump_get.gl(l.pval052,'BEN_ONCE_R_CNTUG',d(l.pval088),vn)) p52,
554 l.pval052 d52,
555 decode(l.pval053,cn,nn,vn,nh,n(l.pval053)) p53,
556 l.pval053 d53,
557 decode(l.pval054,cn,nn,vn,nh,n(l.pval054)) p54,
558 l.pval054 d54,
559 decode(l.pval055,cn,nn,vn,nh,n(l.pval055)) p55,
560 l.pval055 d55,
561 decode(l.pval056,cn,vn,vn,vh,l.pval056) p56,
562 l.pval056 d56,
563 decode(l.pval057,cn,vn,vn,vh,l.pval057) p57,
564 l.pval057 d57,
565 decode(l.pval058,cn,vn,vn,vh,l.pval058) p58,
566 l.pval058 d58,
567 decode(l.pval059,cn,vn,vn,vh,l.pval059) p59,
568 l.pval059 d59,
569 decode(l.pval060,cn,vn,vn,vh,l.pval060) p60,
570 l.pval060 d60,
571 decode(l.pval061,cn,vn,vn,vh,l.pval061) p61,
572 l.pval061 d61,
573 decode(l.pval062,cn,vn,vn,vh,l.pval062) p62,
574 l.pval062 d62,
575 decode(l.pval063,cn,vn,vn,vh,l.pval063) p63,
576 l.pval063 d63,
577 decode(l.pval064,cn,vn,vn,vh,l.pval064) p64,
578 l.pval064 d64,
579 decode(l.pval065,cn,vn,vn,vh,l.pval065) p65,
580 l.pval065 d65,
581 decode(l.pval066,cn,vn,vn,vh,l.pval066) p66,
582 l.pval066 d66,
583 decode(l.pval067,cn,vn,vn,vh,l.pval067) p67,
584 l.pval067 d67,
585 decode(l.pval068,cn,vn,vn,vh,l.pval068) p68,
586 l.pval068 d68,
587 decode(l.pval069,cn,vn,vn,vh,l.pval069) p69,
588 l.pval069 d69,
589 decode(l.pval070,cn,vn,vn,vh,l.pval070) p70,
590 l.pval070 d70,
591 decode(l.pval071,cn,vn,vn,vh,l.pval071) p71,
592 l.pval071 d71,
593 decode(l.pval072,cn,vn,vn,vh,l.pval072) p72,
594 l.pval072 d72,
595 decode(l.pval073,cn,vn,vn,vh,l.pval073) p73,
596 l.pval073 d73,
597 decode(l.pval074,cn,vn,vn,vh,l.pval074) p74,
598 l.pval074 d74,
599 decode(l.pval075,cn,vn,vn,vh,l.pval075) p75,
600 l.pval075 d75,
601 decode(l.pval076,cn,vn,vn,vh,l.pval076) p76,
602 l.pval076 d76,
603 decode(l.pval077,cn,vn,vn,vh,l.pval077) p77,
604 l.pval077 d77,
605 decode(l.pval078,cn,vn,vn,vh,l.pval078) p78,
606 l.pval078 d78,
607 decode(l.pval079,cn,vn,vn,vh,l.pval079) p79,
608 l.pval079 d79,
609 decode(l.pval080,cn,vn,vn,vh,l.pval080) p80,
610 l.pval080 d80,
611 decode(l.pval081,cn,vn,vn,vh,l.pval081) p81,
612 l.pval081 d81,
613 decode(l.pval082,cn,vn,vn,vh,l.pval082) p82,
614 l.pval082 d82,
615 decode(l.pval083,cn,vn,vn,vh,l.pval083) p83,
616 l.pval083 d83,
617 decode(l.pval084,cn,vn,vn,vh,l.pval084) p84,
618 l.pval084 d84,
619 decode(l.pval085,cn,vn,vn,vh,l.pval085) p85,
620 l.pval085 d85,
621 decode(l.pval086,cn,vn,vn,vh,l.pval086) p86,
622 l.pval086 d86,
623 decode(l.pval087,cn,dn,vn,dh,d(l.pval087)) p87,
624 l.pval087 d87,
625 decode(l.pval088,cn,dn,d(l.pval088)) p88,
626 decode(l.pval089,cn,vn,l.pval089) p89,
627 decode(l.pval090,cn,vn,l.pval090) p90,
628 decode(l.pval091,cn,vn,vn,vh,l.pval091) p91,
629 l.pval091 d91,
630 decode(l.pval092,cn,vn,vn,vh,l.pval092) p92,
631 l.pval092 d92,
632 decode(l.pval093,cn,vn,vn,vn,l.pval093) p93,
633 l.pval093 d93,
634 decode(l.pval094,cn,vn,vn,vn,l.pval094) p94,
635 l.pval094 d94,
636 decode(l.pval095,cn,vn,vn,vh,l.pval095) p95,
637 l.pval095 d95,
638 decode(l.pval096,cn,vn,vn,vn,l.pval096) p96,
639 l.pval096 d96,
640 decode(l.pval097,cn,vn,vn,vh,l.pval097) p97,
641 l.pval097 d97,
642 decode(l.pval098,cn,vn,vn,vh,l.pval098) p98,
643 l.pval098 d98
644 from hr_pump_batch_lines l
648 l_validate boolean := false;
645 where l.batch_line_id = p_batch_line_id;
646 --
647 c cr%rowtype;
649 L_ELIG_PER_ID number;
650 L_PL_ID number;
651 L_PGM_ID number;
652 L_PLIP_ID number;
653 L_PTIP_ID number;
654 L_LER_ID number;
655 L_PERSON_ID number;
656 L_COMP_REF_UOM varchar2(2000);
657 L_RT_COMP_REF_UOM varchar2(2000);
658 L_REQUEST_ID number;
659 L_PROGRAM_APPLICATION_ID number;
660 L_PROGRAM_ID number;
661 L_OBJECT_VERSION_NUMBER number;
662 --
663 begin
664 hr_data_pump.entry('call');
665 open cr;
666 fetch cr into c;
667 if cr%notfound then
668 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
669 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
670 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
671 hr_utility.set_message_token('VALUE',p_batch_line_id);
672 hr_utility.raise_error;
673 end if;
674 --
675 if c.p90 is null then
676 L_ELIG_PER_ID:=nn;
677 else
678 L_ELIG_PER_ID := 
679 hr_pump_get.get_elig_per_id
680 (P_ELIG_PER_USER_KEY => c.p90);
681 end if;
682 --
683 if c.d91=cn or
684 c.p88 is null then
685 L_PL_ID:=nn;
686 elsif c.d91 is null then 
687 L_PL_ID:=nh;
688 else
689 L_PL_ID := 
690 hr_pump_get.GET_PL_ID
691 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
692 ,P_PLAN => c.p91
693 ,P_EFFECTIVE_DATE => c.p88);
694 end if;
695 --
696 if c.d92=cn or
697 c.p88 is null then
698 L_PGM_ID:=nn;
699 elsif c.d92 is null then 
700 L_PGM_ID:=nh;
701 else
702 L_PGM_ID := 
703 hr_pump_get.GET_PGM_ID
704 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
705 ,P_PROGRAM => c.p92
706 ,P_EFFECTIVE_DATE => c.p88);
707 end if;
708 --
709 if c.d93=cn then
710 L_PLIP_ID:=nn;
711 elsif c.d93 is null then 
712 L_PLIP_ID:=nh;
713 else
714 L_PLIP_ID := 
715 hr_pump_get.get_plip_id
716 (P_PLIP_USER_KEY => c.p93);
717 end if;
718 --
719 if c.d94=cn then
720 L_PTIP_ID:=nn;
721 elsif c.d94 is null then 
722 L_PTIP_ID:=nh;
723 else
724 L_PTIP_ID := 
725 hr_pump_get.GET_PTIP_ID
726 (P_PTIP_USER_KEY => c.p94);
727 end if;
728 --
729 if c.d95=cn or
730 c.p88 is null then
731 L_LER_ID:=nn;
732 elsif c.d95 is null then 
733 L_LER_ID:=nh;
734 else
735 L_LER_ID := 
736 hr_pump_get.GET_LER_ID
737 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
738 ,P_LIFE_EVENT_REASON => c.p95
739 ,P_EFFECTIVE_DATE => c.p88);
740 end if;
741 --
742 if c.d96=cn then
743 L_PERSON_ID:=nn;
744 elsif c.d96 is null then 
745 L_PERSON_ID:=nh;
746 else
747 L_PERSON_ID := 
748 hr_pump_get.get_person_id
749 (P_PERSON_USER_KEY => c.p96);
750 end if;
751 --
752 if c.d97=cn or
753 c.p88 is null then
754 L_COMP_REF_UOM:=vn;
755 elsif c.d97 is null then 
756 L_COMP_REF_UOM:=vh;
757 else
758 L_COMP_REF_UOM := 
759 hr_pump_get.GET_COMP_REF_UOM_CODE
760 (P_COMP_REF_UOM => c.p97
761 ,P_EFFECTIVE_DATE => c.p88);
762 end if;
763 --
764 if c.d98=cn or
765 c.p88 is null then
766 L_RT_COMP_REF_UOM:=vn;
767 elsif c.d98 is null then 
768 L_RT_COMP_REF_UOM:=vh;
769 else
770 L_RT_COMP_REF_UOM := 
771 hr_pump_get.GET_RT_COMP_REF_UOM_CODE
772 (P_RT_COMP_REF_UOM => c.p98
773 ,P_EFFECTIVE_DATE => c.p88);
774 end if;
775 --
776 L_REQUEST_ID := 
777 hr_pump_get.get_request_id;
778 --
779 L_PROGRAM_APPLICATION_ID := 
780 hr_pump_get.get_program_application_id;
781 --
782 L_PROGRAM_ID := 
783 hr_pump_get.get_program_id;
784 --
785 if c.p90 is null or
786 c.p88 is null then
787 L_OBJECT_VERSION_NUMBER:=nn;
788 else
789 L_OBJECT_VERSION_NUMBER := 
790 hr_pump_get.GET_ELIG_PER_OVN
791 (P_ELIG_PER_USER_KEY => c.p90
792 ,P_EFFECTIVE_DATE => c.p88);
793 end if;
794 --
795 hr_data_pump.api_trc_on;
796 ben_eligible_person_api.UPDATE_ELIGIBLE_PERSON
797 (p_validate => l_validate
798 ,P_ELIG_PER_ID => L_ELIG_PER_ID
799 ,P_EFFECTIVE_START_DATE => c.p1
800 ,P_EFFECTIVE_END_DATE => c.p2
801 ,p_business_group_id => p_business_group_id
802 ,P_PL_ID => L_PL_ID
803 ,P_PGM_ID => L_PGM_ID
804 ,P_PLIP_ID => L_PLIP_ID
805 ,P_PTIP_ID => L_PTIP_ID
806 ,P_LER_ID => L_LER_ID
807 ,P_PERSON_ID => L_PERSON_ID
808 ,P_PER_IN_LER_ID => c.p3
809 ,P_DPNT_OTHR_PL_CVRD_RL_FLAG => c.p4
810 ,P_PRTN_OVRIDN_THRU_DT => c.p5
811 ,P_PL_KEY_EE_FLAG => c.p6
812 ,P_PL_HGHLY_COMPD_FLAG => c.p7
813 ,P_ELIG_FLAG => c.p8
814 ,P_COMP_REF_AMT => c.p9
815 ,P_CMBN_AGE_N_LOS_VAL => c.p10
816 ,P_COMP_REF_UOM => L_COMP_REF_UOM
817 ,P_AGE_VAL => c.p11
818 ,P_LOS_VAL => c.p12
819 ,P_PRTN_END_DT => c.p13
820 ,P_PRTN_STRT_DT => c.p14
821 ,P_WAIT_PERD_CMPLTN_DT => c.p15
822 ,P_WAIT_PERD_STRT_DT => c.p16
823 ,P_WV_CTFN_TYP_CD => c.p17
824 ,P_HRS_WKD_VAL => c.p18
825 ,P_HRS_WKD_BNDRY_PERD_CD => c.p19
826 ,P_PRTN_OVRIDN_FLAG => c.p20
827 ,P_NO_MX_PRTN_OVRID_THRU_FLAG => c.p21
828 ,P_PRTN_OVRIDN_RSN_CD => c.p22
829 ,P_AGE_UOM => c.p23
830 ,P_LOS_UOM => c.p24
831 ,P_OVRID_SVC_DT => c.p25
832 ,P_INELG_RSN_CD => c.p26
833 ,P_FRZ_LOS_FLAG => c.p27
834 ,P_FRZ_AGE_FLAG => c.p28
835 ,P_FRZ_CMP_LVL_FLAG => c.p29
836 ,P_FRZ_PCT_FL_TM_FLAG => c.p30
837 ,P_FRZ_HRS_WKD_FLAG => c.p31
838 ,P_FRZ_COMB_AGE_AND_LOS_FLAG => c.p32
839 ,P_DSTR_RSTCN_FLAG => c.p33
840 ,P_PCT_FL_TM_VAL => c.p34
841 ,P_WV_PRTN_RSN_CD => c.p35
842 ,P_PL_WVD_FLAG => c.p36
843 ,P_RT_COMP_REF_AMT => c.p37
844 ,P_RT_CMBN_AGE_N_LOS_VAL => c.p38
845 ,P_RT_COMP_REF_UOM => L_RT_COMP_REF_UOM
846 ,P_RT_AGE_VAL => c.p39
847 ,P_RT_LOS_VAL => c.p40
848 ,P_RT_HRS_WKD_VAL => c.p41
849 ,P_RT_HRS_WKD_BNDRY_PERD_CD => c.p42
850 ,P_RT_AGE_UOM => c.p43
851 ,P_RT_LOS_UOM => c.p44
852 ,P_RT_PCT_FL_TM_VAL => c.p45
853 ,P_RT_FRZ_LOS_FLAG => c.p46
854 ,P_RT_FRZ_AGE_FLAG => c.p47
855 ,P_RT_FRZ_CMP_LVL_FLAG => c.p48
856 ,P_RT_FRZ_PCT_FL_TM_FLAG => c.p49
857 ,P_RT_FRZ_HRS_WKD_FLAG => c.p50
858 ,P_RT_FRZ_COMB_AGE_AND_LOS_FLAG => c.p51
859 ,P_ONCE_R_CNTUG_CD => c.p52
860 ,P_PL_ORDR_NUM => c.p53
861 ,P_PLIP_ORDR_NUM => c.p54
862 ,P_PTIP_ORDR_NUM => c.p55
863 ,P_PEP_ATTRIBUTE_CATEGORY => c.p56
864 ,P_PEP_ATTRIBUTE1 => c.p57
865 ,P_PEP_ATTRIBUTE2 => c.p58
866 ,P_PEP_ATTRIBUTE3 => c.p59
867 ,P_PEP_ATTRIBUTE4 => c.p60
868 ,P_PEP_ATTRIBUTE5 => c.p61
869 ,P_PEP_ATTRIBUTE6 => c.p62
870 ,P_PEP_ATTRIBUTE7 => c.p63
871 ,P_PEP_ATTRIBUTE8 => c.p64
872 ,P_PEP_ATTRIBUTE9 => c.p65
873 ,P_PEP_ATTRIBUTE10 => c.p66
874 ,P_PEP_ATTRIBUTE11 => c.p67
875 ,P_PEP_ATTRIBUTE12 => c.p68
876 ,P_PEP_ATTRIBUTE13 => c.p69
877 ,P_PEP_ATTRIBUTE14 => c.p70
878 ,P_PEP_ATTRIBUTE15 => c.p71
879 ,P_PEP_ATTRIBUTE16 => c.p72
880 ,P_PEP_ATTRIBUTE17 => c.p73
881 ,P_PEP_ATTRIBUTE18 => c.p74
882 ,P_PEP_ATTRIBUTE19 => c.p75
883 ,P_PEP_ATTRIBUTE20 => c.p76
884 ,P_PEP_ATTRIBUTE21 => c.p77
885 ,P_PEP_ATTRIBUTE22 => c.p78
886 ,P_PEP_ATTRIBUTE23 => c.p79
887 ,P_PEP_ATTRIBUTE24 => c.p80
888 ,P_PEP_ATTRIBUTE25 => c.p81
889 ,P_PEP_ATTRIBUTE26 => c.p82
890 ,P_PEP_ATTRIBUTE27 => c.p83
891 ,P_PEP_ATTRIBUTE28 => c.p84
892 ,P_PEP_ATTRIBUTE29 => c.p85
893 ,P_PEP_ATTRIBUTE30 => c.p86
894 ,P_REQUEST_ID => L_REQUEST_ID
895 ,P_PROGRAM_APPLICATION_ID => L_PROGRAM_APPLICATION_ID
896 ,P_PROGRAM_ID => L_PROGRAM_ID
897 ,P_PROGRAM_UPDATE_DATE => c.p87
898 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER
899 ,P_EFFECTIVE_DATE => c.p88
900 ,P_DATETRACK_MODE => c.p89);
901 hr_data_pump.api_trc_off;
902 
903 --
904 update hr_pump_batch_lines l set
905 l.pval001 = decode(c.p1,null,cn,dc(c.p1)),
906 l.pval002 = decode(c.p2,null,cn,dc(c.p2))
907 where l.rowid = c.myrowid;
908 --
909 close cr;
910 --
911 hr_data_pump.exit('call');
912 exception
913  when hr_multi_message.error_message_exist then
914    if cr%isopen then
915     close cr;
916    end if;
917    hr_pump_utils.set_multi_msg_error_flag(true);
918  when others then
919  if cr%isopen then
920   close cr;
921  end if;
922  raise;
923 end call;
924 end hrdpp_UPDATE_ELIGIBLE_PERSON;