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_PROCESS_BENEFICIARY

Source


1 package body hrdpp_PROCESS_BENEFICIARY as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2009/03/30 04:03:27
4  * Generated for API: BEN_ENROLLMENT_PROCESS.PROCESS_BENEFICIARY
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_BNFT_VAL in number default null
83 ,I_BNFT_VAL in varchar2 default 'N'
84 ,P_LIFE_EVENT_DATE in date
85 ,P_EFFECTIVE_DATE in date
86 ,P_DSGN_STRT_DT in date default null
87 ,I_DSGN_STRT_DT in varchar2 default 'N'
88 ,P_DSGN_THRU_DT in date default null
89 ,I_DSGN_THRU_DT in varchar2 default 'N'
90 ,P_PRMRY_CNTNGNT_CD in varchar2
91 ,P_PCT_DSGD_NUM in number
92 ,P_AMT_DSGD_VAL in number default null
93 ,I_AMT_DSGD_VAL in varchar2 default 'N'
94 ,P_AMT_DSGD_UOM in varchar2 default null
95 ,P_ADDL_INSTRN_TXT in varchar2 default null
96 ,P_MULTI_ROW_ACTN in boolean default null
97 ,P_RECORD_TYP_CD in varchar2
98 ,P_EMPLOYEE_NUMBER in varchar2
99 ,P_NATIONAL_IDENTIFIER in varchar2
100 ,P_FULL_NAME in varchar2
101 ,P_DATE_OF_BIRTH in date
102 ,P_PERSON_NUM in number
103 ,P_PROGRAM in varchar2 default null
104 ,P_PROGRAM_NUM in number default null
105 ,I_PROGRAM_NUM in varchar2 default 'N'
106 ,P_PLAN in varchar2 default null
107 ,P_PLAN_NUM in number default null
108 ,I_PLAN_NUM in varchar2 default 'N'
109 ,P_OPTION in varchar2 default null
110 ,P_OPTION_NUM in number default null
111 ,I_OPTION_NUM in varchar2 default 'N'
112 ,P_LIFE_EVENT_REASON in varchar2
113 ,P_CON_EMPLOYEE_NUMBER in varchar2
114 ,P_CON_NATIONAL_IDENTIFIER in varchar2
115 ,P_CON_FULL_NAME in varchar2
116 ,P_CON_DATE_OF_BIRTH in date
117 ,P_CON_PERSON_NUM in number
118 ,P_ORGANIZATION_NAME in varchar2 default null
119 ,P_LANGUAGE_CODE in varchar2 default null
120 ,P_TTEE_PERSON_USER_KEY in varchar2 default null) is
121 blid number := p_data_pump_batch_line_id;
122  L_MULTI_ROW_ACTN varchar2(5);
123 begin
124 if P_MULTI_ROW_ACTN is null then
125  L_MULTI_ROW_ACTN := null;
126 elsif P_MULTI_ROW_ACTN then
127  L_MULTI_ROW_ACTN := 'TRUE';
128 else 
129  L_MULTI_ROW_ACTN := 'FALSE';
130 end if;
131 if blid is not null then
132 delete from hr_pump_batch_lines where batch_line_id = blid;
133 delete from hr_pump_batch_exceptions
134 where source_type = 'BATCH_LINE' and source_id = blid;
135 end if;
136 insert into hr_pump_batch_lines
137 (batch_id
138 ,batch_line_id
139 ,business_group_name
140 ,api_module_id
141 ,line_status
142 ,user_sequence
143 ,link_value
144 ,pval001
145 ,pval002
146 ,pval003
147 ,pval004
148 ,pval005
149 ,pval006
150 ,pval007
151 ,pval008
152 ,pval009
153 ,pval010
154 ,pval011
155 ,pval012
156 ,pval013
157 ,pval014
158 ,pval015
159 ,pval016
160 ,pval017
161 ,pval018
162 ,pval019
163 ,pval020
164 ,pval021
165 ,pval022
166 ,pval023
167 ,pval024
168 ,pval025
169 ,pval026
170 ,pval027
171 ,pval028
172 ,pval029
173 ,pval030
174 ,pval031
175 ,pval032)
176 values
177 (p_batch_id
178 ,nvl(blid,hr_pump_batch_lines_s.nextval)
179 ,p_data_pump_business_grp_name
180 ,3847
181 ,'U'
182 ,p_user_sequence
183 ,p_link_value
184 ,nd(P_BNFT_VAL,I_BNFT_VAL)
185 ,dc(P_LIFE_EVENT_DATE)
186 ,dc(P_EFFECTIVE_DATE)
187 ,dd(P_DSGN_STRT_DT,I_DSGN_STRT_DT)
188 ,dd(P_DSGN_THRU_DT,I_DSGN_THRU_DT)
189 ,P_PRMRY_CNTNGNT_CD
190 ,P_PCT_DSGD_NUM
191 ,nd(P_AMT_DSGD_VAL,I_AMT_DSGD_VAL)
192 ,P_AMT_DSGD_UOM
193 ,P_ADDL_INSTRN_TXT
194 ,L_MULTI_ROW_ACTN
195 ,P_RECORD_TYP_CD
196 ,P_EMPLOYEE_NUMBER
197 ,P_NATIONAL_IDENTIFIER
198 ,P_FULL_NAME
199 ,dc(P_DATE_OF_BIRTH)
200 ,P_PERSON_NUM
201 ,P_PROGRAM
202 ,nd(P_PROGRAM_NUM,I_PROGRAM_NUM)
203 ,P_PLAN
204 ,nd(P_PLAN_NUM,I_PLAN_NUM)
205 ,P_OPTION
206 ,nd(P_OPTION_NUM,I_OPTION_NUM)
207 ,P_LIFE_EVENT_REASON
208 ,P_CON_EMPLOYEE_NUMBER
209 ,P_CON_NATIONAL_IDENTIFIER
210 ,P_CON_FULL_NAME
211 ,dc(P_CON_DATE_OF_BIRTH)
212 ,P_CON_PERSON_NUM
213 ,P_ORGANIZATION_NAME
214 ,P_LANGUAGE_CODE
215 ,P_TTEE_PERSON_USER_KEY);
216 end insert_batch_lines;
217 --
218 procedure call
219 (p_business_group_id in number,
220 p_batch_line_id     in number) is
221 cursor cr is
222 select l.rowid myrowid,
223 decode(l.pval001,cn,nn,vn,nh,n(l.pval001)) p1,
224 l.pval001 d1,
225 decode(l.pval002,cn,dn,d(l.pval002)) p2,
226 decode(l.pval003,cn,dn,d(l.pval003)) p3,
227 decode(l.pval004,cn,dn,vn,dh,d(l.pval004)) p4,
228 l.pval004 d4,
229 decode(l.pval005,cn,dn,vn,dh,d(l.pval005)) p5,
230 l.pval005 d5,
231 decode(l.pval006,cn,vn,
232  hr_pump_get.gl(l.pval006,'BEN_PRMRY_CNTNGNT',d(l.pval003),l.pval031)) p6,
233 decode(l.pval007,cn,nn,n(l.pval007)) p7,
234 decode(l.pval008,cn,nn,vn,nh,n(l.pval008)) p8,
235 l.pval008 d8,
236 decode(l.pval009,cn,vn,vn,vh,l.pval009) p9,
237 l.pval009 d9,
238 decode(l.pval010,cn,vn,vn,vh,l.pval010) p10,
239 l.pval010 d10,
240 decode(l.pval011,cn,vn,vn,null,l.pval011) p11,
241 l.pval011 d11,
242 decode(l.pval012,cn,vn,l.pval012) p12,
243 decode(l.pval013,cn,vn,l.pval013) p13,
244 decode(l.pval014,cn,vn,l.pval014) p14,
245 decode(l.pval015,cn,vn,l.pval015) p15,
246 decode(l.pval016,cn,dn,d(l.pval016)) p16,
247 decode(l.pval017,cn,nn,n(l.pval017)) p17,
248 decode(l.pval018,cn,vn,vn,vh,l.pval018) p18,
249 l.pval018 d18,
250 decode(l.pval019,cn,nn,vn,nh,n(l.pval019)) p19,
251 l.pval019 d19,
252 decode(l.pval020,cn,vn,vn,vh,l.pval020) p20,
253 l.pval020 d20,
254 decode(l.pval021,cn,nn,vn,nh,n(l.pval021)) p21,
255 l.pval021 d21,
256 decode(l.pval022,cn,vn,vn,vh,l.pval022) p22,
257 l.pval022 d22,
258 decode(l.pval023,cn,nn,vn,nh,n(l.pval023)) p23,
259 l.pval023 d23,
260 decode(l.pval024,cn,vn,l.pval024) p24,
261 decode(l.pval025,cn,vn,l.pval025) p25,
262 decode(l.pval026,cn,vn,l.pval026) p26,
263 decode(l.pval027,cn,vn,l.pval027) p27,
264 decode(l.pval028,cn,dn,d(l.pval028)) p28,
265 decode(l.pval029,cn,nn,n(l.pval029)) p29,
266 decode(l.pval030,cn,vn,vn,vh,l.pval030) p30,
267 l.pval030 d30,
268 decode(l.pval031,cn,vn,vn,vh,l.pval031) p31,
269 l.pval031 d31,
270 decode(l.pval032,cn,vn,vn,vn,l.pval032) p32,
271 l.pval032 d32
272 from hr_pump_batch_lines l
273 where l.batch_line_id = p_batch_line_id;
274 --
275 c cr%rowtype;
276 l_validate boolean := false;
277 L_MULTI_ROW_ACTN boolean;
278 L_PERSON_ID number;
279 L_PGM_ID number;
280 L_PL_ID number;
281 L_OPT_ID number;
282 L_LER_ID number;
283 L_BNF_PERSON_ID number;
284 L_ORGANIZATION_ID number;
285 L_TTEE_PERSON_ID number;
286 --
287 begin
288 hr_data_pump.entry('call');
289 open cr;
290 fetch cr into c;
291 if cr%notfound then
292 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
293 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
294 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
295 hr_utility.set_message_token('VALUE',p_batch_line_id);
296 hr_utility.raise_error;
297 end if;
298 --
299 if upper(c.p11) = 'TRUE' then
300 L_MULTI_ROW_ACTN := true;
301 elsif upper(c.p11) = 'FALSE' then
302 L_MULTI_ROW_ACTN := false;
303 elsif c.p11 is not null then
304 hr_utility.set_message(800,'HR_50327_DP_TYPE_ERR');
305 hr_utility.set_message_token('TYPE','BOOLEAN');
306 hr_utility.set_message_token('PARAMETER','P_MULTI_ROW_ACTN');
307 hr_utility.set_message_token('VALUE',c.p11);
308 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
309 hr_utility.raise_error;
310 end if;
311 --
312 L_PERSON_ID := 
313 BEN_PUMP_GET.GET_PEN_PERSON_ID
314 (P_DATA_PUMP_ALWAYS_CALL => null
315 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
316 ,P_EMPLOYEE_NUMBER => c.p13
317 ,P_NATIONAL_IDENTIFIER => c.p14
318 ,P_FULL_NAME => c.p15
319 ,P_DATE_OF_BIRTH => c.p16
320 ,P_PERSON_NUM => c.p17
321 ,P_EFFECTIVE_DATE => c.p3);
322 --
323 L_PGM_ID := 
324 BEN_PUMP_GET.GET_PGM_ID
325 (P_DATA_PUMP_ALWAYS_CALL => null
326 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
327 ,P_PROGRAM => c.p18
328 ,P_PROGRAM_NUM => c.p19
329 ,P_EFFECTIVE_DATE => c.p3);
330 --
331 L_PL_ID := 
332 BEN_PUMP_GET.GET_PL_ID
333 (P_DATA_PUMP_ALWAYS_CALL => null
334 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
335 ,P_PLAN => c.p20
336 ,P_PLAN_NUM => c.p21
337 ,P_EFFECTIVE_DATE => c.p3);
338 --
339 L_OPT_ID := 
340 BEN_PUMP_GET.GET_OPT_ID
341 (P_DATA_PUMP_ALWAYS_CALL => null
342 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
343 ,P_OPTION => c.p22
344 ,P_OPTION_NUM => c.p23
345 ,P_EFFECTIVE_DATE => c.p3);
346 --
347 if c.p24 is null or
348 c.p3 is null then
349 L_LER_ID:=nn;
350 else
351 L_LER_ID := 
352 hr_pump_get.GET_LER_ID
353 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
354 ,P_LIFE_EVENT_REASON => c.p24
355 ,P_EFFECTIVE_DATE => c.p3);
356 end if;
357 --
358 L_BNF_PERSON_ID := 
359 BEN_PUMP_GET.GET_CON_PERSON_ID
360 (P_DATA_PUMP_ALWAYS_CALL => null
361 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
362 ,P_CON_EMPLOYEE_NUMBER => c.p25
363 ,P_CON_NATIONAL_IDENTIFIER => c.p26
364 ,P_CON_FULL_NAME => c.p27
365 ,P_CON_DATE_OF_BIRTH => c.p28
366 ,P_CON_PERSON_NUM => c.p29
367 ,P_EFFECTIVE_DATE => c.p3);
368 --
369 if c.d30=cn or
370 c.p3 is null or
371 c.d31=cn then
372 L_ORGANIZATION_ID:=nn;
373 elsif c.d30 is null or
374 c.d31 is null then 
375 L_ORGANIZATION_ID:=nh;
376 else
377 L_ORGANIZATION_ID := 
378 hr_pump_get.GET_ORGANIZATION_ID
379 (P_ORGANIZATION_NAME => c.p30
380 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
381 ,P_EFFECTIVE_DATE => c.p3
382 ,P_LANGUAGE_CODE => c.p31);
383 end if;
384 --
385 if c.d32=cn then
386 L_TTEE_PERSON_ID:=nn;
387 elsif c.d32 is null then 
388 L_TTEE_PERSON_ID:=nh;
389 else
390 L_TTEE_PERSON_ID := 
391 hr_pump_get.get_ttee_person_id
392 (P_TTEE_PERSON_USER_KEY => c.p32);
393 end if;
394 --
395 hr_data_pump.api_trc_on;
396 BEN_ENROLLMENT_PROCESS.PROCESS_BENEFICIARY
397 (p_validate => l_validate
398 ,P_PERSON_ID => L_PERSON_ID
399 ,P_PGM_ID => L_PGM_ID
400 ,P_PL_ID => L_PL_ID
401 ,P_OPT_ID => L_OPT_ID
402 ,P_BNFT_VAL => c.p1
403 ,P_LER_ID => L_LER_ID
404 ,P_LIFE_EVENT_DATE => c.p2
405 ,P_EFFECTIVE_DATE => c.p3
406 ,P_BNF_PERSON_ID => L_BNF_PERSON_ID
407 ,p_business_group_id => p_business_group_id
408 ,P_DSGN_STRT_DT => c.p4
409 ,P_DSGN_THRU_DT => c.p5
410 ,P_PRMRY_CNTNGNT_CD => c.p6
411 ,P_PCT_DSGD_NUM => c.p7
412 ,P_AMT_DSGD_VAL => c.p8
413 ,P_AMT_DSGD_UOM => c.p9
414 ,P_ADDL_INSTRN_TXT => c.p10
415 ,P_MULTI_ROW_ACTN => L_MULTI_ROW_ACTN
416 ,P_ORGANIZATION_ID => L_ORGANIZATION_ID
417 ,P_TTEE_PERSON_ID => L_TTEE_PERSON_ID
418 ,P_RECORD_TYP_CD => c.p12);
419 hr_data_pump.api_trc_off;
420 
421 --
422 
423 --
424 close cr;
425 --
426 hr_data_pump.exit('call');
427 exception
428  when hr_multi_message.error_message_exist then
429    if cr%isopen then
430     close cr;
431    end if;
432    hr_pump_utils.set_multi_msg_error_flag(true);
433  when others then
434  if cr%isopen then
435   close cr;
436  end if;
437  raise;
438 end call;
439 end hrdpp_PROCESS_BENEFICIARY;