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_PRTT_PREM

Source


1 package body hrdpp_CREATE_PRTT_PREM as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2007/01/04 02:01:56
4  * Generated for API: ben_prtt_prem_api.CREATE_PRTT_PREM
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_PRTT_PREM_USER_KEY in varchar2
83 ,P_STD_PREM_VAL in number default null
84 ,P_PPE_ATTRIBUTE_CATEGORY in varchar2 default null
85 ,P_PPE_ATTRIBUTE1 in varchar2 default null
86 ,P_PPE_ATTRIBUTE2 in varchar2 default null
87 ,P_PPE_ATTRIBUTE3 in varchar2 default null
88 ,P_PPE_ATTRIBUTE4 in varchar2 default null
89 ,P_PPE_ATTRIBUTE5 in varchar2 default null
90 ,P_PPE_ATTRIBUTE6 in varchar2 default null
91 ,P_PPE_ATTRIBUTE7 in varchar2 default null
92 ,P_PPE_ATTRIBUTE8 in varchar2 default null
93 ,P_PPE_ATTRIBUTE9 in varchar2 default null
94 ,P_PPE_ATTRIBUTE10 in varchar2 default null
95 ,P_PPE_ATTRIBUTE11 in varchar2 default null
96 ,P_PPE_ATTRIBUTE12 in varchar2 default null
97 ,P_PPE_ATTRIBUTE13 in varchar2 default null
98 ,P_PPE_ATTRIBUTE14 in varchar2 default null
99 ,P_PPE_ATTRIBUTE15 in varchar2 default null
100 ,P_PPE_ATTRIBUTE16 in varchar2 default null
101 ,P_PPE_ATTRIBUTE17 in varchar2 default null
102 ,P_PPE_ATTRIBUTE18 in varchar2 default null
103 ,P_PPE_ATTRIBUTE19 in varchar2 default null
104 ,P_PPE_ATTRIBUTE20 in varchar2 default null
105 ,P_PPE_ATTRIBUTE21 in varchar2 default null
106 ,P_PPE_ATTRIBUTE22 in varchar2 default null
107 ,P_PPE_ATTRIBUTE23 in varchar2 default null
108 ,P_PPE_ATTRIBUTE24 in varchar2 default null
109 ,P_PPE_ATTRIBUTE25 in varchar2 default null
110 ,P_PPE_ATTRIBUTE26 in varchar2 default null
111 ,P_PPE_ATTRIBUTE27 in varchar2 default null
112 ,P_PPE_ATTRIBUTE28 in varchar2 default null
113 ,P_PPE_ATTRIBUTE29 in varchar2 default null
114 ,P_PPE_ATTRIBUTE30 in varchar2 default null
115 ,P_PROGRAM_UPDATE_DATE in date default null
116 ,P_EFFECTIVE_DATE in date
117 ,P_STD_PREM_UOM in varchar2 default null
118 ,P_ACTUAL_PREMIUM in varchar2 default null
119 ,P_PRTT_ENRT_RSLT_USER_KEY in varchar2 default null
120 ,P_PER_IN_LER_USER_KEY in varchar2) is
121 blid number := p_data_pump_batch_line_id;
122 begin
123 if blid is not null then
124 delete from hr_pump_batch_lines where batch_line_id = blid;
125 delete from hr_pump_batch_exceptions
126 where source_type = 'BATCH_LINE' and source_id = blid;
127 end if;
128 insert into hr_pump_batch_lines
129 (batch_id
130 ,batch_line_id
131 ,business_group_name
132 ,api_module_id
133 ,line_status
134 ,user_sequence
135 ,link_value
136 ,pval001
137 ,pval004
138 ,pval005
139 ,pval006
140 ,pval007
141 ,pval008
142 ,pval009
143 ,pval010
144 ,pval011
145 ,pval012
146 ,pval013
147 ,pval014
148 ,pval015
149 ,pval016
150 ,pval017
151 ,pval018
152 ,pval019
153 ,pval020
154 ,pval021
155 ,pval022
156 ,pval023
157 ,pval024
158 ,pval025
159 ,pval026
160 ,pval027
161 ,pval028
162 ,pval029
163 ,pval030
164 ,pval031
165 ,pval032
166 ,pval033
167 ,pval034
168 ,pval035
169 ,pval037
170 ,pval038
171 ,pval039
172 ,pval040
173 ,pval041
174 ,pval042)
175 values
176 (p_batch_id
177 ,nvl(blid,hr_pump_batch_lines_s.nextval)
178 ,p_data_pump_business_grp_name
179 ,1009
180 ,'U'
181 ,p_user_sequence
182 ,p_link_value
183 ,P_PRTT_PREM_USER_KEY
184 ,P_STD_PREM_VAL
185 ,P_PPE_ATTRIBUTE_CATEGORY
186 ,P_PPE_ATTRIBUTE1
187 ,P_PPE_ATTRIBUTE2
188 ,P_PPE_ATTRIBUTE3
189 ,P_PPE_ATTRIBUTE4
190 ,P_PPE_ATTRIBUTE5
191 ,P_PPE_ATTRIBUTE6
192 ,P_PPE_ATTRIBUTE7
193 ,P_PPE_ATTRIBUTE8
194 ,P_PPE_ATTRIBUTE9
195 ,P_PPE_ATTRIBUTE10
196 ,P_PPE_ATTRIBUTE11
197 ,P_PPE_ATTRIBUTE12
198 ,P_PPE_ATTRIBUTE13
199 ,P_PPE_ATTRIBUTE14
200 ,P_PPE_ATTRIBUTE15
201 ,P_PPE_ATTRIBUTE16
202 ,P_PPE_ATTRIBUTE17
203 ,P_PPE_ATTRIBUTE18
204 ,P_PPE_ATTRIBUTE19
205 ,P_PPE_ATTRIBUTE20
206 ,P_PPE_ATTRIBUTE21
207 ,P_PPE_ATTRIBUTE22
208 ,P_PPE_ATTRIBUTE23
209 ,P_PPE_ATTRIBUTE24
210 ,P_PPE_ATTRIBUTE25
211 ,P_PPE_ATTRIBUTE26
212 ,P_PPE_ATTRIBUTE27
213 ,P_PPE_ATTRIBUTE28
214 ,P_PPE_ATTRIBUTE29
215 ,P_PPE_ATTRIBUTE30
216 ,dc(P_PROGRAM_UPDATE_DATE)
217 ,dc(P_EFFECTIVE_DATE)
218 ,P_STD_PREM_UOM
219 ,P_ACTUAL_PREMIUM
220 ,P_PRTT_ENRT_RSLT_USER_KEY
221 ,P_PER_IN_LER_USER_KEY);
222 end insert_batch_lines;
223 --
224 procedure call
225 (p_business_group_id in number,
226 p_batch_line_id     in number) is
227 cursor cr is
228 select l.rowid myrowid,
229 l.pval001 p1,
230 decode(l.pval002,cn,dn,d(l.pval002)) p2,
231 decode(l.pval003,cn,dn,d(l.pval003)) p3,
232 decode(l.pval004,cn,nn,vn,nn,n(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,vn,dn,d(l.pval037)) p37,
298 l.pval037 d37,
299 decode(l.pval038,cn,dn,d(l.pval038)) p38,
300 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
301 l.pval039 d39,
302 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
303 l.pval040 d40,
304 decode(l.pval041,cn,vn,vn,vn,l.pval041) p41,
305 l.pval041 d41,
306 decode(l.pval042,cn,vn,l.pval042) p42
307 from hr_pump_batch_lines l
308 where l.batch_line_id = p_batch_line_id;
309 --
310 c cr%rowtype;
311 l_validate boolean := false;
312 L_PRTT_PREM_ID number;
313 L_STD_PREM_UOM varchar2(2000);
314 L_ACTL_PREM_ID number;
315 L_PRTT_ENRT_RSLT_ID number;
316 L_PER_IN_LER_ID number;
317 L_REQUEST_ID number;
318 L_PROGRAM_APPLICATION_ID number;
319 L_PROGRAM_ID number;
320 --
321 begin
322 hr_data_pump.entry('call');
323 open cr;
324 fetch cr into c;
325 if cr%notfound then
326 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
327 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
328 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
329 hr_utility.set_message_token('VALUE',p_batch_line_id);
330 hr_utility.raise_error;
331 end if;
332 --
333 if c.p39 is null or
334 c.p38 is null then
335 L_STD_PREM_UOM:=vn;
336 else
337 L_STD_PREM_UOM := 
338 hr_pump_get.GET_STD_PREM_UOM_CODE
339 (P_STD_PREM_UOM => c.p39
340 ,P_EFFECTIVE_DATE => c.p38);
341 end if;
342 --
343 if c.p40 is null or
344 c.p38 is null then
345 L_ACTL_PREM_ID:=nn;
346 else
347 L_ACTL_PREM_ID := 
348 hr_pump_get.get_actl_prem_id
349 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
350 ,P_ACTUAL_PREMIUM => c.p40
351 ,P_EFFECTIVE_DATE => c.p38);
352 end if;
353 --
354 if c.p41 is null then
355 L_PRTT_ENRT_RSLT_ID:=nn;
356 else
357 L_PRTT_ENRT_RSLT_ID := 
358 hr_pump_get.get_prtt_enrt_rslt_id
359 (P_PRTT_ENRT_RSLT_USER_KEY => c.p41);
360 end if;
361 --
362 if c.p42 is null then
363 L_PER_IN_LER_ID:=nn;
364 else
365 L_PER_IN_LER_ID := 
366 hr_pump_get.get_per_in_ler_id
370 L_REQUEST_ID := 
367 (P_PER_IN_LER_USER_KEY => c.p42);
368 end if;
369 --
371 hr_pump_get.get_request_id;
372 --
373 L_PROGRAM_APPLICATION_ID := 
374 hr_pump_get.get_program_application_id;
375 --
376 L_PROGRAM_ID := 
377 hr_pump_get.get_program_id;
378 --
379 hr_data_pump.api_trc_on;
380 ben_prtt_prem_api.CREATE_PRTT_PREM
381 (p_validate => l_validate
382 ,P_PRTT_PREM_ID => L_PRTT_PREM_ID
383 ,P_EFFECTIVE_START_DATE => c.p2
384 ,P_EFFECTIVE_END_DATE => c.p3
385 ,P_STD_PREM_UOM => L_STD_PREM_UOM
386 ,P_STD_PREM_VAL => c.p4
387 ,P_ACTL_PREM_ID => L_ACTL_PREM_ID
388 ,P_PRTT_ENRT_RSLT_ID => L_PRTT_ENRT_RSLT_ID
389 ,P_PER_IN_LER_ID => L_PER_IN_LER_ID
390 ,p_business_group_id => p_business_group_id
391 ,P_PPE_ATTRIBUTE_CATEGORY => c.p5
392 ,P_PPE_ATTRIBUTE1 => c.p6
393 ,P_PPE_ATTRIBUTE2 => c.p7
394 ,P_PPE_ATTRIBUTE3 => c.p8
395 ,P_PPE_ATTRIBUTE4 => c.p9
396 ,P_PPE_ATTRIBUTE5 => c.p10
397 ,P_PPE_ATTRIBUTE6 => c.p11
398 ,P_PPE_ATTRIBUTE7 => c.p12
399 ,P_PPE_ATTRIBUTE8 => c.p13
400 ,P_PPE_ATTRIBUTE9 => c.p14
401 ,P_PPE_ATTRIBUTE10 => c.p15
402 ,P_PPE_ATTRIBUTE11 => c.p16
403 ,P_PPE_ATTRIBUTE12 => c.p17
404 ,P_PPE_ATTRIBUTE13 => c.p18
405 ,P_PPE_ATTRIBUTE14 => c.p19
406 ,P_PPE_ATTRIBUTE15 => c.p20
407 ,P_PPE_ATTRIBUTE16 => c.p21
408 ,P_PPE_ATTRIBUTE17 => c.p22
409 ,P_PPE_ATTRIBUTE18 => c.p23
410 ,P_PPE_ATTRIBUTE19 => c.p24
411 ,P_PPE_ATTRIBUTE20 => c.p25
412 ,P_PPE_ATTRIBUTE21 => c.p26
413 ,P_PPE_ATTRIBUTE22 => c.p27
414 ,P_PPE_ATTRIBUTE23 => c.p28
415 ,P_PPE_ATTRIBUTE24 => c.p29
416 ,P_PPE_ATTRIBUTE25 => c.p30
417 ,P_PPE_ATTRIBUTE26 => c.p31
418 ,P_PPE_ATTRIBUTE27 => c.p32
419 ,P_PPE_ATTRIBUTE28 => c.p33
420 ,P_PPE_ATTRIBUTE29 => c.p34
421 ,P_PPE_ATTRIBUTE30 => c.p35
422 ,P_OBJECT_VERSION_NUMBER => c.p36
423 ,P_REQUEST_ID => L_REQUEST_ID
424 ,P_PROGRAM_APPLICATION_ID => L_PROGRAM_APPLICATION_ID
425 ,P_PROGRAM_ID => L_PROGRAM_ID
426 ,P_PROGRAM_UPDATE_DATE => c.p37
427 ,P_EFFECTIVE_DATE => c.p38);
428 hr_data_pump.api_trc_off;
429 --
430 iuk(p_batch_line_id,c.p1,L_PRTT_PREM_ID);
431 --
432 update hr_pump_batch_lines l set
433 l.pval001 = decode(c.p1,null,cn,c.p1),
434 l.pval002 = decode(c.p2,null,cn,dc(c.p2)),
435 l.pval003 = decode(c.p3,null,cn,dc(c.p3)),
436 l.pval036 = decode(c.p36,null,cn,c.p36)
437 where l.rowid = c.myrowid;
438 --
439 close cr;
440 --
441 hr_data_pump.exit('call');
442 exception
443  when hr_multi_message.error_message_exist then
444    if cr%isopen then
445     close cr;
446    end if;
447    hr_pump_utils.set_multi_msg_error_flag(true);
448  when others then
449  if cr%isopen then
450   close cr;
451  end if;
452  raise;
453 end call;
454 end hrdpp_CREATE_PRTT_PREM;