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_CREATE_CBR_PER_IN_LER

Source


1 package body hrdpp_CREATE_CBR_PER_IN_LER as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2007/01/04 02:01:30
4  * Generated for API: ben_cbr_per_in_ler_api.CREATE_CBR_PER_IN_LER
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_CBR_PER_IN_LER_USER_KEY in varchar2
83 ,P_INIT_EVT_FLAG in varchar2 default null
84 ,P_CNT_NUM in number default null
85 ,P_PRVS_ELIG_PERD_END_DT in date default null
86 ,P_CRP_ATTRIBUTE_CATEGORY in varchar2 default null
87 ,P_CRP_ATTRIBUTE1 in varchar2 default null
88 ,P_CRP_ATTRIBUTE2 in varchar2 default null
89 ,P_CRP_ATTRIBUTE3 in varchar2 default null
90 ,P_CRP_ATTRIBUTE4 in varchar2 default null
91 ,P_CRP_ATTRIBUTE5 in varchar2 default null
92 ,P_CRP_ATTRIBUTE6 in varchar2 default null
93 ,P_CRP_ATTRIBUTE7 in varchar2 default null
94 ,P_CRP_ATTRIBUTE8 in varchar2 default null
95 ,P_CRP_ATTRIBUTE9 in varchar2 default null
96 ,P_CRP_ATTRIBUTE10 in varchar2 default null
97 ,P_CRP_ATTRIBUTE11 in varchar2 default null
98 ,P_CRP_ATTRIBUTE12 in varchar2 default null
99 ,P_CRP_ATTRIBUTE13 in varchar2 default null
100 ,P_CRP_ATTRIBUTE14 in varchar2 default null
101 ,P_CRP_ATTRIBUTE15 in varchar2 default null
102 ,P_CRP_ATTRIBUTE16 in varchar2 default null
103 ,P_CRP_ATTRIBUTE17 in varchar2 default null
104 ,P_CRP_ATTRIBUTE18 in varchar2 default null
105 ,P_CRP_ATTRIBUTE19 in varchar2 default null
106 ,P_CRP_ATTRIBUTE20 in varchar2 default null
107 ,P_CRP_ATTRIBUTE21 in varchar2 default null
108 ,P_CRP_ATTRIBUTE22 in varchar2 default null
109 ,P_CRP_ATTRIBUTE23 in varchar2 default null
110 ,P_CRP_ATTRIBUTE24 in varchar2 default null
111 ,P_CRP_ATTRIBUTE25 in varchar2 default null
112 ,P_CRP_ATTRIBUTE26 in varchar2 default null
113 ,P_CRP_ATTRIBUTE27 in varchar2 default null
114 ,P_CRP_ATTRIBUTE28 in varchar2 default null
115 ,P_CRP_ATTRIBUTE29 in varchar2 default null
116 ,P_CRP_ATTRIBUTE30 in varchar2 default null
117 ,P_EFFECTIVE_DATE in date
118 ,P_PER_IN_LER_USER_KEY in varchar2 default null
119 ,P_CBR_QUALD_BNF_USER_KEY in varchar2 default null) is
120 blid number := p_data_pump_batch_line_id;
121 begin
122 if blid is not null then
123 delete from hr_pump_batch_lines where batch_line_id = blid;
124 delete from hr_pump_batch_exceptions
125 where source_type = 'BATCH_LINE' and source_id = blid;
126 end if;
127 insert into hr_pump_batch_lines
128 (batch_id
129 ,batch_line_id
130 ,business_group_name
131 ,api_module_id
132 ,line_status
133 ,user_sequence
134 ,link_value
135 ,pval001
136 ,pval002
137 ,pval003
138 ,pval004
139 ,pval005
140 ,pval006
141 ,pval007
142 ,pval008
143 ,pval009
144 ,pval010
145 ,pval011
146 ,pval012
147 ,pval013
148 ,pval014
149 ,pval015
150 ,pval016
151 ,pval017
152 ,pval018
153 ,pval019
154 ,pval020
155 ,pval021
156 ,pval022
157 ,pval023
158 ,pval024
159 ,pval025
160 ,pval026
161 ,pval027
162 ,pval028
163 ,pval029
164 ,pval030
165 ,pval031
166 ,pval032
167 ,pval033
168 ,pval034
169 ,pval035
170 ,pval037
171 ,pval038
172 ,pval039)
173 values
174 (p_batch_id
175 ,nvl(blid,hr_pump_batch_lines_s.nextval)
176 ,p_data_pump_business_grp_name
177 ,29
178 ,'U'
179 ,p_user_sequence
180 ,p_link_value
181 ,P_CBR_PER_IN_LER_USER_KEY
182 ,P_INIT_EVT_FLAG
183 ,P_CNT_NUM
184 ,dc(P_PRVS_ELIG_PERD_END_DT)
185 ,P_CRP_ATTRIBUTE_CATEGORY
186 ,P_CRP_ATTRIBUTE1
187 ,P_CRP_ATTRIBUTE2
188 ,P_CRP_ATTRIBUTE3
189 ,P_CRP_ATTRIBUTE4
190 ,P_CRP_ATTRIBUTE5
191 ,P_CRP_ATTRIBUTE6
192 ,P_CRP_ATTRIBUTE7
193 ,P_CRP_ATTRIBUTE8
194 ,P_CRP_ATTRIBUTE9
195 ,P_CRP_ATTRIBUTE10
196 ,P_CRP_ATTRIBUTE11
197 ,P_CRP_ATTRIBUTE12
198 ,P_CRP_ATTRIBUTE13
199 ,P_CRP_ATTRIBUTE14
200 ,P_CRP_ATTRIBUTE15
201 ,P_CRP_ATTRIBUTE16
202 ,P_CRP_ATTRIBUTE17
203 ,P_CRP_ATTRIBUTE18
204 ,P_CRP_ATTRIBUTE19
205 ,P_CRP_ATTRIBUTE20
206 ,P_CRP_ATTRIBUTE21
207 ,P_CRP_ATTRIBUTE22
208 ,P_CRP_ATTRIBUTE23
209 ,P_CRP_ATTRIBUTE24
210 ,P_CRP_ATTRIBUTE25
211 ,P_CRP_ATTRIBUTE26
212 ,P_CRP_ATTRIBUTE27
213 ,P_CRP_ATTRIBUTE28
214 ,P_CRP_ATTRIBUTE29
215 ,P_CRP_ATTRIBUTE30
216 ,dc(P_EFFECTIVE_DATE)
217 ,P_PER_IN_LER_USER_KEY
218 ,P_CBR_QUALD_BNF_USER_KEY);
219 end insert_batch_lines;
220 --
221 procedure call
222 (p_business_group_id in number,
223 p_batch_line_id     in number) is
224 cursor cr is
225 select l.rowid myrowid,
226 l.pval001 p1,
227 decode(l.pval002,cn,vn,vn,vn,
228  hr_pump_get.gl(l.pval002,'YES_NO',d(l.pval037),vn)) p2,
229 l.pval002 d2,
230 decode(l.pval003,cn,nn,vn,nn,n(l.pval003)) p3,
231 l.pval003 d3,
232 decode(l.pval004,cn,dn,vn,dn,d(l.pval004)) p4,
233 l.pval004 d4,
234 decode(l.pval005,cn,vn,vn,vn,l.pval005) p5,
235 l.pval005 d5,
236 decode(l.pval006,cn,vn,vn,vn,l.pval006) p6,
237 l.pval006 d6,
238 decode(l.pval007,cn,vn,vn,vn,l.pval007) p7,
239 l.pval007 d7,
240 decode(l.pval008,cn,vn,vn,vn,l.pval008) p8,
241 l.pval008 d8,
242 decode(l.pval009,cn,vn,vn,vn,l.pval009) p9,
243 l.pval009 d9,
244 decode(l.pval010,cn,vn,vn,vn,l.pval010) p10,
245 l.pval010 d10,
246 decode(l.pval011,cn,vn,vn,vn,l.pval011) p11,
247 l.pval011 d11,
248 decode(l.pval012,cn,vn,vn,vn,l.pval012) p12,
249 l.pval012 d12,
250 decode(l.pval013,cn,vn,vn,vn,l.pval013) p13,
251 l.pval013 d13,
252 decode(l.pval014,cn,vn,vn,vn,l.pval014) p14,
253 l.pval014 d14,
254 decode(l.pval015,cn,vn,vn,vn,l.pval015) p15,
255 l.pval015 d15,
256 decode(l.pval016,cn,vn,vn,vn,l.pval016) p16,
257 l.pval016 d16,
258 decode(l.pval017,cn,vn,vn,vn,l.pval017) p17,
259 l.pval017 d17,
260 decode(l.pval018,cn,vn,vn,vn,l.pval018) p18,
261 l.pval018 d18,
262 decode(l.pval019,cn,vn,vn,vn,l.pval019) p19,
263 l.pval019 d19,
264 decode(l.pval020,cn,vn,vn,vn,l.pval020) p20,
265 l.pval020 d20,
266 decode(l.pval021,cn,vn,vn,vn,l.pval021) p21,
267 l.pval021 d21,
268 decode(l.pval022,cn,vn,vn,vn,l.pval022) p22,
269 l.pval022 d22,
270 decode(l.pval023,cn,vn,vn,vn,l.pval023) p23,
271 l.pval023 d23,
272 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
273 l.pval024 d24,
274 decode(l.pval025,cn,vn,vn,vn,l.pval025) p25,
275 l.pval025 d25,
276 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
277 l.pval026 d26,
278 decode(l.pval027,cn,vn,vn,vn,l.pval027) p27,
279 l.pval027 d27,
280 decode(l.pval028,cn,vn,vn,vn,l.pval028) p28,
281 l.pval028 d28,
282 decode(l.pval029,cn,vn,vn,vn,l.pval029) p29,
283 l.pval029 d29,
284 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
285 l.pval030 d30,
286 decode(l.pval031,cn,vn,vn,vn,l.pval031) p31,
287 l.pval031 d31,
288 decode(l.pval032,cn,vn,vn,vn,l.pval032) p32,
289 l.pval032 d32,
290 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
291 l.pval033 d33,
292 decode(l.pval034,cn,vn,vn,vn,l.pval034) p34,
293 l.pval034 d34,
294 decode(l.pval035,cn,vn,vn,vn,l.pval035) p35,
295 l.pval035 d35,
296 l.pval036 p36,
297 decode(l.pval037,cn,dn,d(l.pval037)) p37,
298 decode(l.pval038,cn,vn,vn,vn,l.pval038) p38,
299 l.pval038 d38,
300 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
301 l.pval039 d39
302 from hr_pump_batch_lines l
303 where l.batch_line_id = p_batch_line_id;
304 --
305 c cr%rowtype;
306 l_validate boolean := false;
307 L_CBR_PER_IN_LER_ID number;
308 L_PER_IN_LER_ID number;
309 L_CBR_QUALD_BNF_ID number;
310 --
311 begin
312 hr_data_pump.entry('call');
313 open cr;
314 fetch cr into c;
315 if cr%notfound then
316 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
317 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
318 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
319 hr_utility.set_message_token('VALUE',p_batch_line_id);
320 hr_utility.raise_error;
321 end if;
322 --
323 if c.p38 is null then
324 L_PER_IN_LER_ID:=nn;
325 else
326 L_PER_IN_LER_ID := 
327 hr_pump_get.get_per_in_ler_id
328 (P_PER_IN_LER_USER_KEY => c.p38);
329 end if;
330 --
331 if c.p39 is null then
332 L_CBR_QUALD_BNF_ID:=nn;
333 else
334 L_CBR_QUALD_BNF_ID := 
335 hr_pump_get.get_cbr_quald_bnf_id
336 (P_CBR_QUALD_BNF_USER_KEY => c.p39);
337 end if;
338 --
339 hr_data_pump.api_trc_on;
340 ben_cbr_per_in_ler_api.CREATE_CBR_PER_IN_LER
341 (p_validate => l_validate
342 ,P_CBR_PER_IN_LER_ID => L_CBR_PER_IN_LER_ID
343 ,P_INIT_EVT_FLAG => c.p2
344 ,P_CNT_NUM => c.p3
345 ,P_PER_IN_LER_ID => L_PER_IN_LER_ID
346 ,P_CBR_QUALD_BNF_ID => L_CBR_QUALD_BNF_ID
347 ,P_PRVS_ELIG_PERD_END_DT => c.p4
348 ,p_business_group_id => p_business_group_id
349 ,P_CRP_ATTRIBUTE_CATEGORY => c.p5
350 ,P_CRP_ATTRIBUTE1 => c.p6
351 ,P_CRP_ATTRIBUTE2 => c.p7
352 ,P_CRP_ATTRIBUTE3 => c.p8
353 ,P_CRP_ATTRIBUTE4 => c.p9
357 ,P_CRP_ATTRIBUTE8 => c.p13
354 ,P_CRP_ATTRIBUTE5 => c.p10
355 ,P_CRP_ATTRIBUTE6 => c.p11
356 ,P_CRP_ATTRIBUTE7 => c.p12
358 ,P_CRP_ATTRIBUTE9 => c.p14
359 ,P_CRP_ATTRIBUTE10 => c.p15
360 ,P_CRP_ATTRIBUTE11 => c.p16
361 ,P_CRP_ATTRIBUTE12 => c.p17
362 ,P_CRP_ATTRIBUTE13 => c.p18
363 ,P_CRP_ATTRIBUTE14 => c.p19
364 ,P_CRP_ATTRIBUTE15 => c.p20
365 ,P_CRP_ATTRIBUTE16 => c.p21
366 ,P_CRP_ATTRIBUTE17 => c.p22
367 ,P_CRP_ATTRIBUTE18 => c.p23
368 ,P_CRP_ATTRIBUTE19 => c.p24
369 ,P_CRP_ATTRIBUTE20 => c.p25
370 ,P_CRP_ATTRIBUTE21 => c.p26
371 ,P_CRP_ATTRIBUTE22 => c.p27
372 ,P_CRP_ATTRIBUTE23 => c.p28
373 ,P_CRP_ATTRIBUTE24 => c.p29
374 ,P_CRP_ATTRIBUTE25 => c.p30
375 ,P_CRP_ATTRIBUTE26 => c.p31
376 ,P_CRP_ATTRIBUTE27 => c.p32
377 ,P_CRP_ATTRIBUTE28 => c.p33
378 ,P_CRP_ATTRIBUTE29 => c.p34
379 ,P_CRP_ATTRIBUTE30 => c.p35
380 ,P_OBJECT_VERSION_NUMBER => c.p36
381 ,P_EFFECTIVE_DATE => c.p37);
382 hr_data_pump.api_trc_off;
383 --
384 iuk(p_batch_line_id,c.p1,L_CBR_PER_IN_LER_ID);
385 --
386 update hr_pump_batch_lines l set
387 l.pval001 = decode(c.p1,null,cn,c.p1),
388 l.pval036 = decode(c.p36,null,cn,c.p36)
389 where l.rowid = c.myrowid;
390 --
391 close cr;
392 --
393 hr_data_pump.exit('call');
394 exception
395  when hr_multi_message.error_message_exist then
396    if cr%isopen then
397     close cr;
398    end if;
399    hr_pump_utils.set_multi_msg_error_flag(true);
400  when others then
401  if cr%isopen then
402   close cr;
403  end if;
404  raise;
405 end call;
406 end hrdpp_CREATE_CBR_PER_IN_LER;