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