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_DEPENDENT

Source


1 package body hrdpp_PROCESS_DEPENDENT as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2009/03/30 04:03:00
4  * Generated for API: BEN_ENROLLMENT_PROCESS.PROCESS_DEPENDENT
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_LIFE_EVENT_DATE in date
83 ,P_EFFECTIVE_DATE in date
84 ,P_CVG_STRT_DT in date default null
85 ,I_CVG_STRT_DT in varchar2 default 'N'
86 ,P_CVG_THRU_DT in date default null
87 ,I_CVG_THRU_DT in varchar2 default 'N'
88 ,P_MULTI_ROW_ACTN in boolean default null
89 ,P_RECORD_TYP_CD in varchar2
90 ,P_EMPLOYEE_NUMBER in varchar2
91 ,P_NATIONAL_IDENTIFIER in varchar2
92 ,P_FULL_NAME in varchar2
93 ,P_DATE_OF_BIRTH in date
94 ,P_PERSON_NUM in number
95 ,P_PROGRAM in varchar2 default null
96 ,P_PROGRAM_NUM in number default null
97 ,I_PROGRAM_NUM in varchar2 default 'N'
98 ,P_PLAN in varchar2 default null
99 ,P_PLAN_NUM in number default null
100 ,I_PLAN_NUM in varchar2 default 'N'
101 ,P_OPTION in varchar2 default null
102 ,P_OPTION_NUM in number default null
103 ,I_OPTION_NUM in varchar2 default 'N'
104 ,P_LIFE_EVENT_REASON in varchar2
105 ,P_CON_EMPLOYEE_NUMBER in varchar2
106 ,P_CON_NATIONAL_IDENTIFIER in varchar2
107 ,P_CON_FULL_NAME in varchar2
108 ,P_CON_DATE_OF_BIRTH in date
109 ,P_CON_PERSON_NUM in number) is
110 blid number := p_data_pump_batch_line_id;
111  L_MULTI_ROW_ACTN varchar2(5);
112 begin
113 if P_MULTI_ROW_ACTN is null then
114  L_MULTI_ROW_ACTN := null;
115 elsif P_MULTI_ROW_ACTN then
116  L_MULTI_ROW_ACTN := 'TRUE';
117 else 
118  L_MULTI_ROW_ACTN := 'FALSE';
119 end if;
120 if blid is not null then
121 delete from hr_pump_batch_lines where batch_line_id = blid;
122 delete from hr_pump_batch_exceptions
123 where source_type = 'BATCH_LINE' and source_id = blid;
124 end if;
125 insert into hr_pump_batch_lines
126 (batch_id
127 ,batch_line_id
128 ,business_group_name
129 ,api_module_id
130 ,line_status
131 ,user_sequence
132 ,link_value
133 ,pval001
134 ,pval002
135 ,pval003
136 ,pval004
137 ,pval005
138 ,pval006
139 ,pval007
140 ,pval008
141 ,pval009
142 ,pval010
143 ,pval011
144 ,pval012
145 ,pval013
146 ,pval014
147 ,pval015
148 ,pval016
149 ,pval017
150 ,pval018
151 ,pval019
152 ,pval020
153 ,pval021
154 ,pval022
155 ,pval023)
156 values
157 (p_batch_id
158 ,nvl(blid,hr_pump_batch_lines_s.nextval)
159 ,p_data_pump_business_grp_name
160 ,3846
161 ,'U'
162 ,p_user_sequence
163 ,p_link_value
164 ,dc(P_LIFE_EVENT_DATE)
165 ,dc(P_EFFECTIVE_DATE)
166 ,dd(P_CVG_STRT_DT,I_CVG_STRT_DT)
167 ,dd(P_CVG_THRU_DT,I_CVG_THRU_DT)
168 ,L_MULTI_ROW_ACTN
169 ,P_RECORD_TYP_CD
170 ,P_EMPLOYEE_NUMBER
171 ,P_NATIONAL_IDENTIFIER
172 ,P_FULL_NAME
173 ,dc(P_DATE_OF_BIRTH)
174 ,P_PERSON_NUM
175 ,P_PROGRAM
176 ,nd(P_PROGRAM_NUM,I_PROGRAM_NUM)
177 ,P_PLAN
178 ,nd(P_PLAN_NUM,I_PLAN_NUM)
179 ,P_OPTION
180 ,nd(P_OPTION_NUM,I_OPTION_NUM)
181 ,P_LIFE_EVENT_REASON
182 ,P_CON_EMPLOYEE_NUMBER
183 ,P_CON_NATIONAL_IDENTIFIER
184 ,P_CON_FULL_NAME
185 ,dc(P_CON_DATE_OF_BIRTH)
186 ,P_CON_PERSON_NUM);
187 end insert_batch_lines;
188 --
189 procedure call
190 (p_business_group_id in number,
191 p_batch_line_id     in number) is
192 cursor cr is
193 select l.rowid myrowid,
194 decode(l.pval001,cn,dn,d(l.pval001)) p1,
195 decode(l.pval002,cn,dn,d(l.pval002)) p2,
196 decode(l.pval003,cn,dn,vn,dh,d(l.pval003)) p3,
197 l.pval003 d3,
198 decode(l.pval004,cn,dn,vn,dh,d(l.pval004)) p4,
199 l.pval004 d4,
200 decode(l.pval005,cn,vn,vn,null,l.pval005) p5,
201 l.pval005 d5,
202 decode(l.pval006,cn,vn,l.pval006) p6,
203 decode(l.pval007,cn,vn,l.pval007) p7,
204 decode(l.pval008,cn,vn,l.pval008) p8,
205 decode(l.pval009,cn,vn,l.pval009) p9,
206 decode(l.pval010,cn,dn,d(l.pval010)) p10,
207 decode(l.pval011,cn,nn,n(l.pval011)) p11,
208 decode(l.pval012,cn,vn,vn,vh,l.pval012) p12,
209 l.pval012 d12,
210 decode(l.pval013,cn,nn,vn,nh,n(l.pval013)) p13,
211 l.pval013 d13,
212 decode(l.pval014,cn,vn,vn,vh,l.pval014) p14,
213 l.pval014 d14,
214 decode(l.pval015,cn,nn,vn,nh,n(l.pval015)) p15,
215 l.pval015 d15,
216 decode(l.pval016,cn,vn,vn,vh,l.pval016) p16,
217 l.pval016 d16,
218 decode(l.pval017,cn,nn,vn,nh,n(l.pval017)) p17,
219 l.pval017 d17,
220 decode(l.pval018,cn,vn,l.pval018) p18,
221 decode(l.pval019,cn,vn,l.pval019) p19,
222 decode(l.pval020,cn,vn,l.pval020) p20,
223 decode(l.pval021,cn,vn,l.pval021) p21,
224 decode(l.pval022,cn,dn,d(l.pval022)) p22,
225 decode(l.pval023,cn,nn,n(l.pval023)) p23
226 from hr_pump_batch_lines l
227 where l.batch_line_id = p_batch_line_id;
228 --
229 c cr%rowtype;
230 l_validate boolean := false;
231 L_MULTI_ROW_ACTN boolean;
232 L_PERSON_ID number;
233 L_PGM_ID number;
234 L_PL_ID number;
235 L_OPT_ID number;
236 L_LER_ID number;
237 L_CONTACT_PERSON_ID number;
238 --
239 begin
240 hr_data_pump.entry('call');
241 open cr;
242 fetch cr into c;
243 if cr%notfound then
244 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
245 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
246 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
247 hr_utility.set_message_token('VALUE',p_batch_line_id);
248 hr_utility.raise_error;
249 end if;
250 --
251 if upper(c.p5) = 'TRUE' then
252 L_MULTI_ROW_ACTN := true;
253 elsif upper(c.p5) = 'FALSE' then
254 L_MULTI_ROW_ACTN := false;
255 elsif c.p5 is not null then
256 hr_utility.set_message(800,'HR_50327_DP_TYPE_ERR');
257 hr_utility.set_message_token('TYPE','BOOLEAN');
258 hr_utility.set_message_token('PARAMETER','P_MULTI_ROW_ACTN');
259 hr_utility.set_message_token('VALUE',c.p5);
260 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
261 hr_utility.raise_error;
262 end if;
263 --
264 L_PERSON_ID := 
265 BEN_PUMP_GET.GET_PEN_PERSON_ID
266 (P_DATA_PUMP_ALWAYS_CALL => null
267 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
268 ,P_EMPLOYEE_NUMBER => c.p7
269 ,P_NATIONAL_IDENTIFIER => c.p8
270 ,P_FULL_NAME => c.p9
271 ,P_DATE_OF_BIRTH => c.p10
272 ,P_PERSON_NUM => c.p11
273 ,P_EFFECTIVE_DATE => c.p2);
274 --
275 L_PGM_ID := 
276 BEN_PUMP_GET.GET_PGM_ID
277 (P_DATA_PUMP_ALWAYS_CALL => null
278 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
279 ,P_PROGRAM => c.p12
280 ,P_PROGRAM_NUM => c.p13
281 ,P_EFFECTIVE_DATE => c.p2);
282 --
283 L_PL_ID := 
284 BEN_PUMP_GET.GET_PL_ID
285 (P_DATA_PUMP_ALWAYS_CALL => null
286 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
287 ,P_PLAN => c.p14
288 ,P_PLAN_NUM => c.p15
289 ,P_EFFECTIVE_DATE => c.p2);
290 --
291 L_OPT_ID := 
292 BEN_PUMP_GET.GET_OPT_ID
293 (P_DATA_PUMP_ALWAYS_CALL => null
294 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
295 ,P_OPTION => c.p16
296 ,P_OPTION_NUM => c.p17
297 ,P_EFFECTIVE_DATE => c.p2);
298 --
299 if c.p18 is null or
300 c.p2 is null then
301 L_LER_ID:=nn;
302 else
303 L_LER_ID := 
304 hr_pump_get.GET_LER_ID
305 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
306 ,P_LIFE_EVENT_REASON => c.p18
307 ,P_EFFECTIVE_DATE => c.p2);
308 end if;
309 --
310 L_CONTACT_PERSON_ID := 
311 BEN_PUMP_GET.GET_CON_PERSON_ID
312 (P_DATA_PUMP_ALWAYS_CALL => null
313 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
314 ,P_CON_EMPLOYEE_NUMBER => c.p19
315 ,P_CON_NATIONAL_IDENTIFIER => c.p20
316 ,P_CON_FULL_NAME => c.p21
317 ,P_CON_DATE_OF_BIRTH => c.p22
318 ,P_CON_PERSON_NUM => c.p23
319 ,P_EFFECTIVE_DATE => c.p2);
320 --
321 hr_data_pump.api_trc_on;
322 BEN_ENROLLMENT_PROCESS.PROCESS_DEPENDENT
323 (p_validate => l_validate
324 ,P_PERSON_ID => L_PERSON_ID
325 ,P_PGM_ID => L_PGM_ID
326 ,P_PL_ID => L_PL_ID
327 ,P_OPT_ID => L_OPT_ID
328 ,P_LER_ID => L_LER_ID
329 ,P_LIFE_EVENT_DATE => c.p1
330 ,P_EFFECTIVE_DATE => c.p2
331 ,P_CONTACT_PERSON_ID => L_CONTACT_PERSON_ID
332 ,p_business_group_id => p_business_group_id
333 ,P_CVG_STRT_DT => c.p3
334 ,P_CVG_THRU_DT => c.p4
335 ,P_MULTI_ROW_ACTN => L_MULTI_ROW_ACTN
336 ,P_RECORD_TYP_CD => c.p6);
337 hr_data_pump.api_trc_off;
338 
339 --
340 
341 --
342 close cr;
343 --
344 hr_data_pump.exit('call');
345 exception
346  when hr_multi_message.error_message_exist then
347    if cr%isopen then
348     close cr;
349    end if;
350    hr_pump_utils.set_multi_msg_error_flag(true);
351  when others then
352  if cr%isopen then
353   close cr;
354  end if;
355  raise;
356 end call;
357 end hrdpp_PROCESS_DEPENDENT;