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_ELIG_CVRD_DPNT

Source


1 package body hrdpp_UPDATE_ELIG_CVRD_DPNT as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2007/01/04 02:01:43
4  * Generated for API: ben_elig_cvrd_dpnt_api.UPDATE_ELIG_CVRD_DPNT
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_CVG_STRT_DT in date default null
83 ,I_CVG_STRT_DT in varchar2 default 'N'
84 ,P_CVG_THRU_DT in date default null
85 ,I_CVG_THRU_DT in varchar2 default 'N'
86 ,P_CVG_PNDG_FLAG in varchar2 default null
87 ,P_PDP_ATTRIBUTE_CATEGORY in varchar2 default null
88 ,P_PDP_ATTRIBUTE1 in varchar2 default null
89 ,P_PDP_ATTRIBUTE2 in varchar2 default null
90 ,P_PDP_ATTRIBUTE3 in varchar2 default null
91 ,P_PDP_ATTRIBUTE4 in varchar2 default null
92 ,P_PDP_ATTRIBUTE5 in varchar2 default null
93 ,P_PDP_ATTRIBUTE6 in varchar2 default null
94 ,P_PDP_ATTRIBUTE7 in varchar2 default null
95 ,P_PDP_ATTRIBUTE8 in varchar2 default null
96 ,P_PDP_ATTRIBUTE9 in varchar2 default null
97 ,P_PDP_ATTRIBUTE10 in varchar2 default null
98 ,P_PDP_ATTRIBUTE11 in varchar2 default null
99 ,P_PDP_ATTRIBUTE12 in varchar2 default null
100 ,P_PDP_ATTRIBUTE13 in varchar2 default null
101 ,P_PDP_ATTRIBUTE14 in varchar2 default null
102 ,P_PDP_ATTRIBUTE15 in varchar2 default null
103 ,P_PDP_ATTRIBUTE16 in varchar2 default null
104 ,P_PDP_ATTRIBUTE17 in varchar2 default null
105 ,P_PDP_ATTRIBUTE18 in varchar2 default null
106 ,P_PDP_ATTRIBUTE19 in varchar2 default null
107 ,P_PDP_ATTRIBUTE20 in varchar2 default null
108 ,P_PDP_ATTRIBUTE21 in varchar2 default null
109 ,P_PDP_ATTRIBUTE22 in varchar2 default null
110 ,P_PDP_ATTRIBUTE23 in varchar2 default null
111 ,P_PDP_ATTRIBUTE24 in varchar2 default null
112 ,P_PDP_ATTRIBUTE25 in varchar2 default null
113 ,P_PDP_ATTRIBUTE26 in varchar2 default null
114 ,P_PDP_ATTRIBUTE27 in varchar2 default null
115 ,P_PDP_ATTRIBUTE28 in varchar2 default null
116 ,P_PDP_ATTRIBUTE29 in varchar2 default null
117 ,P_PDP_ATTRIBUTE30 in varchar2 default null
118 ,P_PROGRAM_UPDATE_DATE in date default null
119 ,I_PROGRAM_UPDATE_DATE in varchar2 default 'N'
120 ,P_OVRDN_FLAG in varchar2 default null
121 ,P_OVRDN_THRU_DT in date default null
122 ,I_OVRDN_THRU_DT in varchar2 default 'N'
123 ,P_EFFECTIVE_DATE in date
124 ,P_DATETRACK_MODE in varchar2
125 ,P_MULTI_ROW_ACTN in boolean default null
126 ,P_ELIG_CVRD_DPNT_USER_KEY in varchar2
127 ,P_PRTT_ENRT_RSLT_USER_KEY in varchar2 default null
128 ,P_DPNT_PERSON_USER_KEY in varchar2 default null
129 ,P_PER_IN_LER_USER_KEY in varchar2 default null) is
130 blid number := p_data_pump_batch_line_id;
131  L_MULTI_ROW_ACTN varchar2(5);
132 begin
133 if P_MULTI_ROW_ACTN is null then
134  L_MULTI_ROW_ACTN := null;
135 elsif P_MULTI_ROW_ACTN then
136  L_MULTI_ROW_ACTN := 'TRUE';
137 else 
138  L_MULTI_ROW_ACTN := 'FALSE';
139 end if;
140 if blid is not null then
141 delete from hr_pump_batch_lines where batch_line_id = blid;
142 delete from hr_pump_batch_exceptions
143 where source_type = 'BATCH_LINE' and source_id = blid;
144 end if;
145 insert into hr_pump_batch_lines
146 (batch_id
147 ,batch_line_id
148 ,business_group_name
149 ,api_module_id
150 ,line_status
151 ,user_sequence
152 ,link_value
153 ,pval003
154 ,pval004
155 ,pval005
156 ,pval006
157 ,pval007
158 ,pval008
159 ,pval009
160 ,pval010
161 ,pval011
162 ,pval012
163 ,pval013
164 ,pval014
165 ,pval015
166 ,pval016
167 ,pval017
168 ,pval018
169 ,pval019
170 ,pval020
171 ,pval021
172 ,pval022
173 ,pval023
174 ,pval024
175 ,pval025
176 ,pval026
177 ,pval027
178 ,pval028
179 ,pval029
180 ,pval030
181 ,pval031
182 ,pval032
183 ,pval033
184 ,pval034
185 ,pval035
186 ,pval036
187 ,pval037
188 ,pval038
189 ,pval039
190 ,pval040
191 ,pval041
192 ,pval042
193 ,pval043
194 ,pval044
195 ,pval045
196 ,pval046)
197 values
198 (p_batch_id
199 ,nvl(blid,hr_pump_batch_lines_s.nextval)
200 ,p_data_pump_business_grp_name
201 ,607
202 ,'U'
203 ,p_user_sequence
204 ,p_link_value
205 ,dd(P_CVG_STRT_DT,I_CVG_STRT_DT)
206 ,dd(P_CVG_THRU_DT,I_CVG_THRU_DT)
207 ,P_CVG_PNDG_FLAG
208 ,P_PDP_ATTRIBUTE_CATEGORY
209 ,P_PDP_ATTRIBUTE1
210 ,P_PDP_ATTRIBUTE2
211 ,P_PDP_ATTRIBUTE3
212 ,P_PDP_ATTRIBUTE4
213 ,P_PDP_ATTRIBUTE5
214 ,P_PDP_ATTRIBUTE6
215 ,P_PDP_ATTRIBUTE7
216 ,P_PDP_ATTRIBUTE8
217 ,P_PDP_ATTRIBUTE9
218 ,P_PDP_ATTRIBUTE10
219 ,P_PDP_ATTRIBUTE11
220 ,P_PDP_ATTRIBUTE12
221 ,P_PDP_ATTRIBUTE13
222 ,P_PDP_ATTRIBUTE14
223 ,P_PDP_ATTRIBUTE15
224 ,P_PDP_ATTRIBUTE16
225 ,P_PDP_ATTRIBUTE17
226 ,P_PDP_ATTRIBUTE18
227 ,P_PDP_ATTRIBUTE19
228 ,P_PDP_ATTRIBUTE20
229 ,P_PDP_ATTRIBUTE21
230 ,P_PDP_ATTRIBUTE22
231 ,P_PDP_ATTRIBUTE23
232 ,P_PDP_ATTRIBUTE24
233 ,P_PDP_ATTRIBUTE25
234 ,P_PDP_ATTRIBUTE26
235 ,P_PDP_ATTRIBUTE27
236 ,P_PDP_ATTRIBUTE28
237 ,P_PDP_ATTRIBUTE29
238 ,P_PDP_ATTRIBUTE30
239 ,dd(P_PROGRAM_UPDATE_DATE,I_PROGRAM_UPDATE_DATE)
240 ,P_OVRDN_FLAG
241 ,dd(P_OVRDN_THRU_DT,I_OVRDN_THRU_DT)
242 ,dc(P_EFFECTIVE_DATE)
243 ,P_DATETRACK_MODE
244 ,L_MULTI_ROW_ACTN
245 ,P_ELIG_CVRD_DPNT_USER_KEY
246 ,P_PRTT_ENRT_RSLT_USER_KEY
247 ,P_DPNT_PERSON_USER_KEY
248 ,P_PER_IN_LER_USER_KEY);
249 end insert_batch_lines;
250 --
251 procedure call
252 (p_business_group_id in number,
253 p_batch_line_id     in number) is
254 cursor cr is
255 select l.rowid myrowid,
256 decode(l.pval001,cn,dn,d(l.pval001)) p1,
257 decode(l.pval002,cn,dn,d(l.pval002)) p2,
258 decode(l.pval003,cn,dn,vn,dh,d(l.pval003)) p3,
259 l.pval003 d3,
260 decode(l.pval004,cn,dn,vn,dh,d(l.pval004)) p4,
261 l.pval004 d4,
262 decode(l.pval005,cn,vn,vn,vh,
263  hr_pump_get.gl(l.pval005,'YES_NO',d(l.pval040),vn)) p5,
264 l.pval005 d5,
265 decode(l.pval006,cn,vn,vn,vh,l.pval006) p6,
266 l.pval006 d6,
267 decode(l.pval007,cn,vn,vn,vh,l.pval007) p7,
268 l.pval007 d7,
269 decode(l.pval008,cn,vn,vn,vh,l.pval008) p8,
270 l.pval008 d8,
271 decode(l.pval009,cn,vn,vn,vh,l.pval009) p9,
272 l.pval009 d9,
273 decode(l.pval010,cn,vn,vn,vh,l.pval010) p10,
274 l.pval010 d10,
275 decode(l.pval011,cn,vn,vn,vh,l.pval011) p11,
276 l.pval011 d11,
277 decode(l.pval012,cn,vn,vn,vh,l.pval012) p12,
278 l.pval012 d12,
279 decode(l.pval013,cn,vn,vn,vh,l.pval013) p13,
280 l.pval013 d13,
281 decode(l.pval014,cn,vn,vn,vh,l.pval014) p14,
282 l.pval014 d14,
283 decode(l.pval015,cn,vn,vn,vh,l.pval015) p15,
284 l.pval015 d15,
285 decode(l.pval016,cn,vn,vn,vh,l.pval016) p16,
286 l.pval016 d16,
287 decode(l.pval017,cn,vn,vn,vh,l.pval017) p17,
288 l.pval017 d17,
289 decode(l.pval018,cn,vn,vn,vh,l.pval018) p18,
290 l.pval018 d18,
291 decode(l.pval019,cn,vn,vn,vh,l.pval019) p19,
292 l.pval019 d19,
293 decode(l.pval020,cn,vn,vn,vh,l.pval020) p20,
294 l.pval020 d20,
295 decode(l.pval021,cn,vn,vn,vh,l.pval021) p21,
296 l.pval021 d21,
297 decode(l.pval022,cn,vn,vn,vh,l.pval022) p22,
298 l.pval022 d22,
299 decode(l.pval023,cn,vn,vn,vh,l.pval023) p23,
300 l.pval023 d23,
301 decode(l.pval024,cn,vn,vn,vh,l.pval024) p24,
302 l.pval024 d24,
303 decode(l.pval025,cn,vn,vn,vh,l.pval025) p25,
304 l.pval025 d25,
305 decode(l.pval026,cn,vn,vn,vh,l.pval026) p26,
306 l.pval026 d26,
307 decode(l.pval027,cn,vn,vn,vh,l.pval027) p27,
308 l.pval027 d27,
309 decode(l.pval028,cn,vn,vn,vh,l.pval028) p28,
310 l.pval028 d28,
311 decode(l.pval029,cn,vn,vn,vh,l.pval029) p29,
312 l.pval029 d29,
313 decode(l.pval030,cn,vn,vn,vh,l.pval030) p30,
314 l.pval030 d30,
315 decode(l.pval031,cn,vn,vn,vh,l.pval031) p31,
316 l.pval031 d31,
317 decode(l.pval032,cn,vn,vn,vh,l.pval032) p32,
318 l.pval032 d32,
319 decode(l.pval033,cn,vn,vn,vh,l.pval033) p33,
320 l.pval033 d33,
321 decode(l.pval034,cn,vn,vn,vh,l.pval034) p34,
322 l.pval034 d34,
323 decode(l.pval035,cn,vn,vn,vh,l.pval035) p35,
324 l.pval035 d35,
325 decode(l.pval036,cn,vn,vn,vh,l.pval036) p36,
326 l.pval036 d36,
327 decode(l.pval037,cn,dn,vn,dh,d(l.pval037)) p37,
328 l.pval037 d37,
329 decode(l.pval038,cn,vn,vn,vh,
330  hr_pump_get.gl(l.pval038,'YES_NO',d(l.pval040),vn)) p38,
331 l.pval038 d38,
332 decode(l.pval039,cn,dn,vn,dh,d(l.pval039)) p39,
333 l.pval039 d39,
334 decode(l.pval040,cn,dn,d(l.pval040)) p40,
335 decode(l.pval041,cn,vn,l.pval041) p41,
336 decode(l.pval042,cn,vn,vn,null,l.pval042) p42,
337 l.pval042 d42,
338 decode(l.pval043,cn,vn,l.pval043) p43,
339 decode(l.pval044,cn,vn,vn,vn,l.pval044) p44,
340 l.pval044 d44,
341 decode(l.pval045,cn,vn,vn,vn,l.pval045) p45,
342 l.pval045 d45,
343 decode(l.pval046,cn,vn,vn,vn,l.pval046) p46,
344 l.pval046 d46
345 from hr_pump_batch_lines l
346 where l.batch_line_id = p_batch_line_id;
347 --
348 c cr%rowtype;
349 l_validate boolean := false;
350 L_MULTI_ROW_ACTN boolean;
351 L_ELIG_CVRD_DPNT_ID number;
352 L_PRTT_ENRT_RSLT_ID number;
353 L_DPNT_PERSON_ID number;
354 L_REQUEST_ID number;
355 L_PROGRAM_APPLICATION_ID number;
356 L_PROGRAM_ID number;
357 L_OBJECT_VERSION_NUMBER number;
358 L_PER_IN_LER_ID number;
359 --
360 begin
361 hr_data_pump.entry('call');
362 open cr;
363 fetch cr into c;
364 if cr%notfound then
365 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
366 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
367 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
368 hr_utility.set_message_token('VALUE',p_batch_line_id);
369 hr_utility.raise_error;
370 end if;
371 --
372 if upper(c.p42) = 'TRUE' then
373 L_MULTI_ROW_ACTN := true;
374 elsif upper(c.p42) = 'FALSE' then
375 L_MULTI_ROW_ACTN := false;
376 elsif c.p42 is not null then
377 hr_utility.set_message(800,'HR_50327_DP_TYPE_ERR');
378 hr_utility.set_message_token('TYPE','BOOLEAN');
379 hr_utility.set_message_token('PARAMETER','P_MULTI_ROW_ACTN');
380 hr_utility.set_message_token('VALUE',c.p42);
381 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
382 hr_utility.raise_error;
383 end if;
384 --
385 if c.p43 is null then
386 L_ELIG_CVRD_DPNT_ID:=nn;
387 else
388 L_ELIG_CVRD_DPNT_ID := 
389 hr_pump_get.get_elig_cvrd_dpnt_id
390 (P_ELIG_CVRD_DPNT_USER_KEY => c.p43);
391 end if;
392 --
393 if c.d44=cn then
394 L_PRTT_ENRT_RSLT_ID:=nn;
395 elsif c.d44 is null then 
396 L_PRTT_ENRT_RSLT_ID:=nh;
397 else
398 L_PRTT_ENRT_RSLT_ID := 
399 hr_pump_get.get_prtt_enrt_rslt_id
400 (P_PRTT_ENRT_RSLT_USER_KEY => c.p44);
401 end if;
402 --
403 if c.d45=cn then
404 L_DPNT_PERSON_ID:=nn;
405 elsif c.d45 is null then 
406 L_DPNT_PERSON_ID:=nh;
407 else
408 L_DPNT_PERSON_ID := 
409 hr_pump_get.get_dpnt_person_id
410 (P_DPNT_PERSON_USER_KEY => c.p45);
411 end if;
412 --
413 L_REQUEST_ID := 
414 hr_pump_get.get_request_id;
415 --
416 L_PROGRAM_APPLICATION_ID := 
417 hr_pump_get.get_program_application_id;
418 --
419 L_PROGRAM_ID := 
420 hr_pump_get.get_program_id;
421 --
422 if c.p43 is null or
423 c.p40 is null then
424 L_OBJECT_VERSION_NUMBER:=nn;
425 else
426 L_OBJECT_VERSION_NUMBER := 
427 hr_pump_get.GET_ELIG_CVRD_DPNT_OVN
428 (P_ELIG_CVRD_DPNT_USER_KEY => c.p43
429 ,P_EFFECTIVE_DATE => c.p40);
430 end if;
431 --
432 if c.d46=cn then
433 L_PER_IN_LER_ID:=nn;
434 elsif c.d46 is null then 
435 L_PER_IN_LER_ID:=nh;
436 else
437 L_PER_IN_LER_ID := 
438 hr_pump_get.get_per_in_ler_id
439 (P_PER_IN_LER_USER_KEY => c.p46);
440 end if;
441 --
442 hr_data_pump.api_trc_on;
443 ben_elig_cvrd_dpnt_api.UPDATE_ELIG_CVRD_DPNT
444 (p_validate => l_validate
445 ,P_ELIG_CVRD_DPNT_ID => L_ELIG_CVRD_DPNT_ID
446 ,P_EFFECTIVE_START_DATE => c.p1
447 ,P_EFFECTIVE_END_DATE => c.p2
448 ,p_business_group_id => p_business_group_id
449 ,P_PRTT_ENRT_RSLT_ID => L_PRTT_ENRT_RSLT_ID
450 ,P_DPNT_PERSON_ID => L_DPNT_PERSON_ID
451 ,P_CVG_STRT_DT => c.p3
452 ,P_CVG_THRU_DT => c.p4
453 ,P_CVG_PNDG_FLAG => c.p5
457 ,P_PDP_ATTRIBUTE3 => c.p9
454 ,P_PDP_ATTRIBUTE_CATEGORY => c.p6
455 ,P_PDP_ATTRIBUTE1 => c.p7
456 ,P_PDP_ATTRIBUTE2 => c.p8
458 ,P_PDP_ATTRIBUTE4 => c.p10
459 ,P_PDP_ATTRIBUTE5 => c.p11
460 ,P_PDP_ATTRIBUTE6 => c.p12
461 ,P_PDP_ATTRIBUTE7 => c.p13
462 ,P_PDP_ATTRIBUTE8 => c.p14
463 ,P_PDP_ATTRIBUTE9 => c.p15
464 ,P_PDP_ATTRIBUTE10 => c.p16
465 ,P_PDP_ATTRIBUTE11 => c.p17
466 ,P_PDP_ATTRIBUTE12 => c.p18
467 ,P_PDP_ATTRIBUTE13 => c.p19
468 ,P_PDP_ATTRIBUTE14 => c.p20
469 ,P_PDP_ATTRIBUTE15 => c.p21
470 ,P_PDP_ATTRIBUTE16 => c.p22
471 ,P_PDP_ATTRIBUTE17 => c.p23
472 ,P_PDP_ATTRIBUTE18 => c.p24
473 ,P_PDP_ATTRIBUTE19 => c.p25
474 ,P_PDP_ATTRIBUTE20 => c.p26
475 ,P_PDP_ATTRIBUTE21 => c.p27
476 ,P_PDP_ATTRIBUTE22 => c.p28
477 ,P_PDP_ATTRIBUTE23 => c.p29
478 ,P_PDP_ATTRIBUTE24 => c.p30
479 ,P_PDP_ATTRIBUTE25 => c.p31
480 ,P_PDP_ATTRIBUTE26 => c.p32
481 ,P_PDP_ATTRIBUTE27 => c.p33
482 ,P_PDP_ATTRIBUTE28 => c.p34
483 ,P_PDP_ATTRIBUTE29 => c.p35
484 ,P_PDP_ATTRIBUTE30 => c.p36
485 ,P_REQUEST_ID => L_REQUEST_ID
486 ,P_PROGRAM_APPLICATION_ID => L_PROGRAM_APPLICATION_ID
487 ,P_PROGRAM_ID => L_PROGRAM_ID
488 ,P_PROGRAM_UPDATE_DATE => c.p37
489 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER
490 ,P_OVRDN_FLAG => c.p38
491 ,P_PER_IN_LER_ID => L_PER_IN_LER_ID
492 ,P_OVRDN_THRU_DT => c.p39
493 ,P_EFFECTIVE_DATE => c.p40
494 ,P_DATETRACK_MODE => c.p41
495 ,P_MULTI_ROW_ACTN => L_MULTI_ROW_ACTN);
496 hr_data_pump.api_trc_off;
497 
498 --
499 update hr_pump_batch_lines l set
500 l.pval001 = decode(c.p1,null,cn,dc(c.p1)),
501 l.pval002 = decode(c.p2,null,cn,dc(c.p2))
502 where l.rowid = c.myrowid;
503 --
504 close cr;
505 --
506 hr_data_pump.exit('call');
507 exception
508  when hr_multi_message.error_message_exist then
509    if cr%isopen then
510     close cr;
511    end if;
512    hr_pump_utils.set_multi_msg_error_flag(true);
513  when others then
514  if cr%isopen then
515   close cr;
516  end if;
517  raise;
518 end call;
519 end hrdpp_UPDATE_ELIG_CVRD_DPNT;