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_CBR_QUALD_BNF

Source


1 package body hrdpp_UPDATE_CBR_QUALD_BNF as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2007/01/04 02:01:24
4  * Generated for API: ben_cbr_quald_bnf_api.UPDATE_CBR_QUALD_BNF
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_QUALD_BNF_FLAG in varchar2 default null
83 ,P_CBR_ELIG_PERD_STRT_DT in date default null
84 ,I_CBR_ELIG_PERD_STRT_DT in varchar2 default 'N'
85 ,P_CBR_ELIG_PERD_END_DT in date default null
86 ,I_CBR_ELIG_PERD_END_DT in varchar2 default 'N'
87 ,P_CBR_INELG_RSN_CD in varchar2 default null
88 ,P_CQB_ATTRIBUTE_CATEGORY in varchar2 default null
89 ,P_CQB_ATTRIBUTE1 in varchar2 default null
90 ,P_CQB_ATTRIBUTE2 in varchar2 default null
91 ,P_CQB_ATTRIBUTE3 in varchar2 default null
92 ,P_CQB_ATTRIBUTE4 in varchar2 default null
93 ,P_CQB_ATTRIBUTE5 in varchar2 default null
94 ,P_CQB_ATTRIBUTE6 in varchar2 default null
95 ,P_CQB_ATTRIBUTE7 in varchar2 default null
96 ,P_CQB_ATTRIBUTE8 in varchar2 default null
97 ,P_CQB_ATTRIBUTE9 in varchar2 default null
98 ,P_CQB_ATTRIBUTE10 in varchar2 default null
99 ,P_CQB_ATTRIBUTE11 in varchar2 default null
100 ,P_CQB_ATTRIBUTE12 in varchar2 default null
101 ,P_CQB_ATTRIBUTE13 in varchar2 default null
102 ,P_CQB_ATTRIBUTE14 in varchar2 default null
103 ,P_CQB_ATTRIBUTE15 in varchar2 default null
104 ,P_CQB_ATTRIBUTE16 in varchar2 default null
105 ,P_CQB_ATTRIBUTE17 in varchar2 default null
106 ,P_CQB_ATTRIBUTE18 in varchar2 default null
107 ,P_CQB_ATTRIBUTE19 in varchar2 default null
108 ,P_CQB_ATTRIBUTE20 in varchar2 default null
109 ,P_CQB_ATTRIBUTE21 in varchar2 default null
110 ,P_CQB_ATTRIBUTE22 in varchar2 default null
111 ,P_CQB_ATTRIBUTE23 in varchar2 default null
112 ,P_CQB_ATTRIBUTE24 in varchar2 default null
113 ,P_CQB_ATTRIBUTE25 in varchar2 default null
114 ,P_CQB_ATTRIBUTE26 in varchar2 default null
115 ,P_CQB_ATTRIBUTE27 in varchar2 default null
116 ,P_CQB_ATTRIBUTE28 in varchar2 default null
117 ,P_CQB_ATTRIBUTE29 in varchar2 default null
118 ,P_CQB_ATTRIBUTE30 in varchar2 default null
119 ,P_EFFECTIVE_DATE in date
120 ,P_CBR_QUALD_BNF_USER_KEY in varchar2
121 ,P_QUALD_BNF_PERSON_USER_KEY in varchar2 default null
122 ,P_PROGRAM in varchar2 default null
123 ,P_PTIP_USER_KEY in varchar2 default null
124 ,P_PLAN_TYPE in varchar2 default null
125 ,P_CVRD_EMP_PERSON_USER_KEY in varchar2 default null) is
126 blid number := p_data_pump_batch_line_id;
127 begin
128 if blid is not null then
129 delete from hr_pump_batch_lines where batch_line_id = blid;
130 delete from hr_pump_batch_exceptions
131 where source_type = 'BATCH_LINE' and source_id = blid;
132 end if;
133 insert into hr_pump_batch_lines
134 (batch_id
135 ,batch_line_id
136 ,business_group_name
137 ,api_module_id
138 ,line_status
139 ,user_sequence
140 ,link_value
141 ,pval001
142 ,pval002
143 ,pval003
144 ,pval004
145 ,pval005
146 ,pval006
147 ,pval007
148 ,pval008
149 ,pval009
150 ,pval010
151 ,pval011
152 ,pval012
153 ,pval013
154 ,pval014
155 ,pval015
156 ,pval016
157 ,pval017
158 ,pval018
159 ,pval019
160 ,pval020
161 ,pval021
162 ,pval022
163 ,pval023
164 ,pval024
165 ,pval025
166 ,pval026
167 ,pval027
168 ,pval028
169 ,pval029
170 ,pval030
171 ,pval031
172 ,pval032
173 ,pval033
174 ,pval034
175 ,pval035
176 ,pval036
177 ,pval037
178 ,pval038
179 ,pval039
180 ,pval040
181 ,pval041
182 ,pval042)
183 values
184 (p_batch_id
185 ,nvl(blid,hr_pump_batch_lines_s.nextval)
186 ,p_data_pump_business_grp_name
187 ,26
188 ,'U'
189 ,p_user_sequence
190 ,p_link_value
191 ,P_QUALD_BNF_FLAG
192 ,dd(P_CBR_ELIG_PERD_STRT_DT,I_CBR_ELIG_PERD_STRT_DT)
193 ,dd(P_CBR_ELIG_PERD_END_DT,I_CBR_ELIG_PERD_END_DT)
194 ,P_CBR_INELG_RSN_CD
195 ,P_CQB_ATTRIBUTE_CATEGORY
196 ,P_CQB_ATTRIBUTE1
197 ,P_CQB_ATTRIBUTE2
198 ,P_CQB_ATTRIBUTE3
199 ,P_CQB_ATTRIBUTE4
200 ,P_CQB_ATTRIBUTE5
201 ,P_CQB_ATTRIBUTE6
202 ,P_CQB_ATTRIBUTE7
203 ,P_CQB_ATTRIBUTE8
204 ,P_CQB_ATTRIBUTE9
205 ,P_CQB_ATTRIBUTE10
206 ,P_CQB_ATTRIBUTE11
207 ,P_CQB_ATTRIBUTE12
208 ,P_CQB_ATTRIBUTE13
209 ,P_CQB_ATTRIBUTE14
210 ,P_CQB_ATTRIBUTE15
211 ,P_CQB_ATTRIBUTE16
212 ,P_CQB_ATTRIBUTE17
213 ,P_CQB_ATTRIBUTE18
214 ,P_CQB_ATTRIBUTE19
215 ,P_CQB_ATTRIBUTE20
216 ,P_CQB_ATTRIBUTE21
217 ,P_CQB_ATTRIBUTE22
218 ,P_CQB_ATTRIBUTE23
219 ,P_CQB_ATTRIBUTE24
220 ,P_CQB_ATTRIBUTE25
221 ,P_CQB_ATTRIBUTE26
222 ,P_CQB_ATTRIBUTE27
223 ,P_CQB_ATTRIBUTE28
224 ,P_CQB_ATTRIBUTE29
225 ,P_CQB_ATTRIBUTE30
226 ,dc(P_EFFECTIVE_DATE)
227 ,P_CBR_QUALD_BNF_USER_KEY
228 ,P_QUALD_BNF_PERSON_USER_KEY
229 ,P_PROGRAM
230 ,P_PTIP_USER_KEY
231 ,P_PLAN_TYPE
232 ,P_CVRD_EMP_PERSON_USER_KEY);
233 end insert_batch_lines;
234 --
235 procedure call
236 (p_business_group_id in number,
237 p_batch_line_id     in number) is
238 cursor cr is
239 select l.rowid myrowid,
240 decode(l.pval001,cn,vn,vn,vh,
241  hr_pump_get.gl(l.pval001,'YES_NO',d(l.pval036),vn)) p1,
242 l.pval001 d1,
243 decode(l.pval002,cn,dn,vn,dh,d(l.pval002)) p2,
244 l.pval002 d2,
245 decode(l.pval003,cn,dn,vn,dh,d(l.pval003)) p3,
246 l.pval003 d3,
247 decode(l.pval004,cn,vn,vn,vh,
248  hr_pump_get.gl(l.pval004,'BEN_CBR_INELG_RSN',d(l.pval036),vn)) p4,
249 l.pval004 d4,
250 decode(l.pval005,cn,vn,vn,vh,l.pval005) p5,
251 l.pval005 d5,
252 decode(l.pval006,cn,vn,vn,vh,l.pval006) p6,
253 l.pval006 d6,
254 decode(l.pval007,cn,vn,vn,vh,l.pval007) p7,
255 l.pval007 d7,
256 decode(l.pval008,cn,vn,vn,vh,l.pval008) p8,
257 l.pval008 d8,
258 decode(l.pval009,cn,vn,vn,vh,l.pval009) p9,
259 l.pval009 d9,
260 decode(l.pval010,cn,vn,vn,vh,l.pval010) p10,
261 l.pval010 d10,
262 decode(l.pval011,cn,vn,vn,vh,l.pval011) p11,
263 l.pval011 d11,
264 decode(l.pval012,cn,vn,vn,vh,l.pval012) p12,
265 l.pval012 d12,
266 decode(l.pval013,cn,vn,vn,vh,l.pval013) p13,
267 l.pval013 d13,
268 decode(l.pval014,cn,vn,vn,vh,l.pval014) p14,
269 l.pval014 d14,
270 decode(l.pval015,cn,vn,vn,vh,l.pval015) p15,
271 l.pval015 d15,
272 decode(l.pval016,cn,vn,vn,vh,l.pval016) p16,
273 l.pval016 d16,
274 decode(l.pval017,cn,vn,vn,vh,l.pval017) p17,
275 l.pval017 d17,
276 decode(l.pval018,cn,vn,vn,vh,l.pval018) p18,
277 l.pval018 d18,
278 decode(l.pval019,cn,vn,vn,vh,l.pval019) p19,
279 l.pval019 d19,
280 decode(l.pval020,cn,vn,vn,vh,l.pval020) p20,
281 l.pval020 d20,
282 decode(l.pval021,cn,vn,vn,vh,l.pval021) p21,
283 l.pval021 d21,
284 decode(l.pval022,cn,vn,vn,vh,l.pval022) p22,
285 l.pval022 d22,
286 decode(l.pval023,cn,vn,vn,vh,l.pval023) p23,
287 l.pval023 d23,
288 decode(l.pval024,cn,vn,vn,vh,l.pval024) p24,
289 l.pval024 d24,
290 decode(l.pval025,cn,vn,vn,vh,l.pval025) p25,
291 l.pval025 d25,
292 decode(l.pval026,cn,vn,vn,vh,l.pval026) p26,
293 l.pval026 d26,
294 decode(l.pval027,cn,vn,vn,vh,l.pval027) p27,
295 l.pval027 d27,
296 decode(l.pval028,cn,vn,vn,vh,l.pval028) p28,
297 l.pval028 d28,
298 decode(l.pval029,cn,vn,vn,vh,l.pval029) p29,
299 l.pval029 d29,
300 decode(l.pval030,cn,vn,vn,vh,l.pval030) p30,
301 l.pval030 d30,
302 decode(l.pval031,cn,vn,vn,vh,l.pval031) p31,
303 l.pval031 d31,
304 decode(l.pval032,cn,vn,vn,vh,l.pval032) p32,
305 l.pval032 d32,
306 decode(l.pval033,cn,vn,vn,vh,l.pval033) p33,
307 l.pval033 d33,
308 decode(l.pval034,cn,vn,vn,vh,l.pval034) p34,
309 l.pval034 d34,
310 decode(l.pval035,cn,vn,vn,vh,l.pval035) p35,
311 l.pval035 d35,
312 decode(l.pval036,cn,dn,d(l.pval036)) p36,
313 decode(l.pval037,cn,vn,l.pval037) p37,
314 decode(l.pval038,cn,vn,vn,vn,l.pval038) p38,
315 l.pval038 d38,
316 decode(l.pval039,cn,vn,vn,vh,l.pval039) p39,
317 l.pval039 d39,
318 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
319 l.pval040 d40,
320 decode(l.pval041,cn,vn,vn,vh,l.pval041) p41,
321 l.pval041 d41,
322 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
323 l.pval042 d42
324 from hr_pump_batch_lines l
325 where l.batch_line_id = p_batch_line_id;
326 --
327 c cr%rowtype;
328 l_validate boolean := false;
329 L_CBR_QUALD_BNF_ID number;
330 L_QUALD_BNF_PERSON_ID number;
331 L_PGM_ID number;
332 L_PTIP_ID number;
333 L_PL_TYP_ID number;
334 L_CVRD_EMP_PERSON_ID number;
335 L_OBJECT_VERSION_NUMBER number;
336 --
337 begin
338 hr_data_pump.entry('call');
339 open cr;
340 fetch cr into c;
341 if cr%notfound then
342 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
343 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
344 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
345 hr_utility.set_message_token('VALUE',p_batch_line_id);
346 hr_utility.raise_error;
347 end if;
348 --
349 if c.p37 is null then
350 L_CBR_QUALD_BNF_ID:=nn;
351 else
352 L_CBR_QUALD_BNF_ID := 
353 hr_pump_get.get_cbr_quald_bnf_id
354 (P_CBR_QUALD_BNF_USER_KEY => c.p37);
355 end if;
356 --
357 if c.d38=cn then
358 L_QUALD_BNF_PERSON_ID:=nn;
359 elsif c.d38 is null then 
360 L_QUALD_BNF_PERSON_ID:=nh;
361 else
362 L_QUALD_BNF_PERSON_ID := 
366 --
363 hr_pump_get.get_quald_bnf_person_id
364 (P_QUALD_BNF_PERSON_USER_KEY => c.p38);
365 end if;
367 if c.d39=cn or
368 c.p36 is null then
369 L_PGM_ID:=nn;
370 elsif c.d39 is null then 
371 L_PGM_ID:=nh;
372 else
373 L_PGM_ID := 
374 hr_pump_get.get_pgm_id
375 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
376 ,P_PROGRAM => c.p39
377 ,P_EFFECTIVE_DATE => c.p36);
378 end if;
379 --
380 if c.d40=cn then
381 L_PTIP_ID:=nn;
382 elsif c.d40 is null then 
383 L_PTIP_ID:=nh;
384 else
385 L_PTIP_ID := 
386 hr_pump_get.get_ptip_id
387 (P_PTIP_USER_KEY => c.p40);
388 end if;
389 --
390 if c.d41=cn or
391 c.p36 is null then
392 L_PL_TYP_ID:=nn;
393 elsif c.d41 is null then 
394 L_PL_TYP_ID:=nh;
395 else
396 L_PL_TYP_ID := 
397 hr_pump_get.get_pl_typ_id
398 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
399 ,P_PLAN_TYPE => c.p41
400 ,P_EFFECTIVE_DATE => c.p36);
401 end if;
402 --
403 if c.d42=cn then
404 L_CVRD_EMP_PERSON_ID:=nn;
405 elsif c.d42 is null then 
406 L_CVRD_EMP_PERSON_ID:=nh;
407 else
408 L_CVRD_EMP_PERSON_ID := 
409 hr_pump_get.get_cvrd_emp_person_id
410 (P_CVRD_EMP_PERSON_USER_KEY => c.p42);
411 end if;
412 --
413 if c.p37 is null then
414 L_OBJECT_VERSION_NUMBER:=nn;
415 else
416 L_OBJECT_VERSION_NUMBER := 
417 hr_pump_get.GET_CBR_QUALD_BNF_OVN
418 (P_CBR_QUALD_BNF_USER_KEY => c.p37);
419 end if;
420 --
421 hr_data_pump.api_trc_on;
422 ben_cbr_quald_bnf_api.UPDATE_CBR_QUALD_BNF
423 (p_validate => l_validate
424 ,P_CBR_QUALD_BNF_ID => L_CBR_QUALD_BNF_ID
425 ,P_QUALD_BNF_FLAG => c.p1
426 ,P_CBR_ELIG_PERD_STRT_DT => c.p2
427 ,P_CBR_ELIG_PERD_END_DT => c.p3
428 ,P_QUALD_BNF_PERSON_ID => L_QUALD_BNF_PERSON_ID
429 ,P_PGM_ID => L_PGM_ID
430 ,P_PTIP_ID => L_PTIP_ID
431 ,P_PL_TYP_ID => L_PL_TYP_ID
432 ,P_CVRD_EMP_PERSON_ID => L_CVRD_EMP_PERSON_ID
433 ,P_CBR_INELG_RSN_CD => c.p4
434 ,p_business_group_id => p_business_group_id
435 ,P_CQB_ATTRIBUTE_CATEGORY => c.p5
436 ,P_CQB_ATTRIBUTE1 => c.p6
437 ,P_CQB_ATTRIBUTE2 => c.p7
438 ,P_CQB_ATTRIBUTE3 => c.p8
439 ,P_CQB_ATTRIBUTE4 => c.p9
440 ,P_CQB_ATTRIBUTE5 => c.p10
441 ,P_CQB_ATTRIBUTE6 => c.p11
442 ,P_CQB_ATTRIBUTE7 => c.p12
443 ,P_CQB_ATTRIBUTE8 => c.p13
444 ,P_CQB_ATTRIBUTE9 => c.p14
445 ,P_CQB_ATTRIBUTE10 => c.p15
446 ,P_CQB_ATTRIBUTE11 => c.p16
447 ,P_CQB_ATTRIBUTE12 => c.p17
448 ,P_CQB_ATTRIBUTE13 => c.p18
449 ,P_CQB_ATTRIBUTE14 => c.p19
450 ,P_CQB_ATTRIBUTE15 => c.p20
451 ,P_CQB_ATTRIBUTE16 => c.p21
452 ,P_CQB_ATTRIBUTE17 => c.p22
453 ,P_CQB_ATTRIBUTE18 => c.p23
454 ,P_CQB_ATTRIBUTE19 => c.p24
455 ,P_CQB_ATTRIBUTE20 => c.p25
456 ,P_CQB_ATTRIBUTE21 => c.p26
457 ,P_CQB_ATTRIBUTE22 => c.p27
458 ,P_CQB_ATTRIBUTE23 => c.p28
459 ,P_CQB_ATTRIBUTE24 => c.p29
460 ,P_CQB_ATTRIBUTE25 => c.p30
461 ,P_CQB_ATTRIBUTE26 => c.p31
462 ,P_CQB_ATTRIBUTE27 => c.p32
463 ,P_CQB_ATTRIBUTE28 => c.p33
464 ,P_CQB_ATTRIBUTE29 => c.p34
465 ,P_CQB_ATTRIBUTE30 => c.p35
466 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER
467 ,P_EFFECTIVE_DATE => c.p36);
468 hr_data_pump.api_trc_off;
469 
470 --
471 
472 --
473 close cr;
474 --
475 hr_data_pump.exit('call');
476 exception
477  when hr_multi_message.error_message_exist then
478    if cr%isopen then
479     close cr;
480    end if;
481    hr_pump_utils.set_multi_msg_error_flag(true);
482  when others then
483  if cr%isopen then
484   close cr;
485  end if;
486  raise;
487 end call;
488 end hrdpp_UPDATE_CBR_QUALD_BNF;