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_PTNL_LER_FOR_PER

Source


1 package body hrdpp_UPDATE_PTNL_LER_FOR_PER as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2009/03/30 04:03:36
4  * Generated for API: ben_ptnl_ler_for_per_api.UPDATE_PTNL_LER_FOR_PER
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_LF_EVT_OCRD_DT in date default null
83 ,I_LF_EVT_OCRD_DT in varchar2 default 'N'
84 ,P_PTNL_LER_FOR_PER_STAT_CD in varchar2 default null
85 ,P_PTNL_LER_FOR_PER_SRC_CD in varchar2 default null
86 ,P_MNL_DT in date default null
87 ,I_MNL_DT in varchar2 default 'N'
88 ,P_DTCTD_DT in date default null
89 ,I_DTCTD_DT in varchar2 default 'N'
90 ,P_PROCD_DT in date default null
91 ,I_PROCD_DT in varchar2 default 'N'
92 ,P_UNPROCD_DT in date default null
93 ,I_UNPROCD_DT in varchar2 default 'N'
94 ,P_VOIDD_DT in date default null
95 ,I_VOIDD_DT in varchar2 default 'N'
96 ,P_MNLO_DT in date default null
97 ,I_MNLO_DT in varchar2 default 'N'
98 ,P_NTFN_DT in date default null
99 ,I_NTFN_DT in varchar2 default 'N'
100 ,P_PROGRAM_UPDATE_DATE in date default null
101 ,I_PROGRAM_UPDATE_DATE in varchar2 default 'N'
102 ,P_EFFECTIVE_DATE in date
103 ,P_PTNL_LER_FOR_PER_USER_KEY in varchar2
104 ,P_CSD_BY_PPL_USER_KEY in varchar2 default null
105 ,P_TRGR_TABLE_PK_USER_KEY in varchar2 default null
106 ,P_ENRT_PERD_USER_KEY in varchar2 default null
107 ,P_LIFE_EVENT_REASON in varchar2 default null
108 ,P_PERSON_USER_KEY in varchar2 default null) is
109 blid number := p_data_pump_batch_line_id;
110 begin
111 if blid is not null then
112 delete from hr_pump_batch_lines where batch_line_id = blid;
113 delete from hr_pump_batch_exceptions
114 where source_type = 'BATCH_LINE' and source_id = blid;
115 end if;
116 insert into hr_pump_batch_lines
117 (batch_id
118 ,batch_line_id
119 ,business_group_name
120 ,api_module_id
121 ,line_status
122 ,user_sequence
123 ,link_value
124 ,pval001
125 ,pval002
126 ,pval003
127 ,pval004
128 ,pval005
129 ,pval006
130 ,pval007
131 ,pval008
132 ,pval009
133 ,pval010
134 ,pval011
135 ,pval012
136 ,pval013
137 ,pval014
138 ,pval015
139 ,pval016
140 ,pval017
141 ,pval018)
142 values
143 (p_batch_id
144 ,nvl(blid,hr_pump_batch_lines_s.nextval)
145 ,p_data_pump_business_grp_name
146 ,662
147 ,'U'
148 ,p_user_sequence
149 ,p_link_value
150 ,dd(P_LF_EVT_OCRD_DT,I_LF_EVT_OCRD_DT)
151 ,P_PTNL_LER_FOR_PER_STAT_CD
152 ,P_PTNL_LER_FOR_PER_SRC_CD
153 ,dd(P_MNL_DT,I_MNL_DT)
154 ,dd(P_DTCTD_DT,I_DTCTD_DT)
155 ,dd(P_PROCD_DT,I_PROCD_DT)
156 ,dd(P_UNPROCD_DT,I_UNPROCD_DT)
157 ,dd(P_VOIDD_DT,I_VOIDD_DT)
158 ,dd(P_MNLO_DT,I_MNLO_DT)
159 ,dd(P_NTFN_DT,I_NTFN_DT)
160 ,dd(P_PROGRAM_UPDATE_DATE,I_PROGRAM_UPDATE_DATE)
161 ,dc(P_EFFECTIVE_DATE)
162 ,P_PTNL_LER_FOR_PER_USER_KEY
163 ,P_CSD_BY_PPL_USER_KEY
164 ,P_TRGR_TABLE_PK_USER_KEY
165 ,P_ENRT_PERD_USER_KEY
166 ,P_LIFE_EVENT_REASON
167 ,P_PERSON_USER_KEY);
168 end insert_batch_lines;
169 --
170 procedure call
171 (p_business_group_id in number,
172 p_batch_line_id     in number) is
173 cursor cr is
174 select l.rowid myrowid,
175 decode(l.pval001,cn,dn,vn,dh,d(l.pval001)) p1,
176 l.pval001 d1,
177 decode(l.pval002,cn,vn,vn,vh,
178  hr_pump_get.gl(l.pval002,'BEN_PTNL_LER_FOR_PER_STAT',d(l.pval012),vn)) p2,
179 l.pval002 d2,
180 decode(l.pval003,cn,vn,vn,vh,
181  hr_pump_get.gl(l.pval003,'BEN_PTNL_LER_FOR_PER_SRC',d(l.pval012),vn)) p3,
182 l.pval003 d3,
183 decode(l.pval004,cn,dn,vn,dh,d(l.pval004)) p4,
184 l.pval004 d4,
185 decode(l.pval005,cn,dn,vn,dh,d(l.pval005)) p5,
186 l.pval005 d5,
187 decode(l.pval006,cn,dn,vn,dh,d(l.pval006)) p6,
188 l.pval006 d6,
189 decode(l.pval007,cn,dn,vn,dh,d(l.pval007)) p7,
190 l.pval007 d7,
191 decode(l.pval008,cn,dn,vn,dh,d(l.pval008)) p8,
192 l.pval008 d8,
193 decode(l.pval009,cn,dn,vn,dh,d(l.pval009)) p9,
194 l.pval009 d9,
195 decode(l.pval010,cn,dn,vn,dh,d(l.pval010)) p10,
196 l.pval010 d10,
197 decode(l.pval011,cn,dn,vn,dh,d(l.pval011)) p11,
198 l.pval011 d11,
199 decode(l.pval012,cn,dn,d(l.pval012)) p12,
200 decode(l.pval013,cn,vn,l.pval013) p13,
201 decode(l.pval014,cn,vn,vn,vn,l.pval014) p14,
202 l.pval014 d14,
203 decode(l.pval015,cn,vn,vn,vn,l.pval015) p15,
204 l.pval015 d15,
205 decode(l.pval016,cn,vn,vn,vn,l.pval016) p16,
206 l.pval016 d16,
207 decode(l.pval017,cn,vn,vn,vh,l.pval017) p17,
208 l.pval017 d17,
209 decode(l.pval018,cn,vn,vn,vn,l.pval018) p18,
210 l.pval018 d18
211 from hr_pump_batch_lines l
212 where l.batch_line_id = p_batch_line_id;
213 --
214 c cr%rowtype;
215 l_validate boolean := false;
216 L_PTNL_LER_FOR_PER_ID number;
217 L_CSD_BY_PTNL_LER_FOR_PER_ID number;
218 L_TRGR_TABLE_PK_ID number;
219 L_ENRT_PERD_ID number;
220 L_LER_ID number;
221 L_PERSON_ID number;
222 L_REQUEST_ID number;
223 L_PROGRAM_APPLICATION_ID number;
224 L_PROGRAM_ID number;
225 L_OBJECT_VERSION_NUMBER number;
226 --
227 begin
228 hr_data_pump.entry('call');
229 open cr;
230 fetch cr into c;
231 if cr%notfound then
232 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
233 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
234 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
235 hr_utility.set_message_token('VALUE',p_batch_line_id);
236 hr_utility.raise_error;
237 end if;
238 --
239 if c.p13 is null then
240 L_PTNL_LER_FOR_PER_ID:=nn;
241 else
242 L_PTNL_LER_FOR_PER_ID := 
243 hr_pump_get.get_ptnl_ler_for_per_id
244 (P_PTNL_LER_FOR_PER_USER_KEY => c.p13);
245 end if;
246 --
247 if c.d14=cn then
248 L_CSD_BY_PTNL_LER_FOR_PER_ID:=nn;
249 elsif c.d14 is null then 
250 L_CSD_BY_PTNL_LER_FOR_PER_ID:=nh;
251 else
252 L_CSD_BY_PTNL_LER_FOR_PER_ID := 
253 hr_pump_get.get_csd_by_ptnl_ler_for_per_id
254 (P_CSD_BY_PPL_USER_KEY => c.p14);
255 end if;
256 --
257 if c.d15=cn then
258 L_TRGR_TABLE_PK_ID:=nn;
259 elsif c.d15 is null then 
260 L_TRGR_TABLE_PK_ID:=nh;
261 else
262 L_TRGR_TABLE_PK_ID := 
263 hr_pump_get.get_trgr_table_pk_id
264 (P_TRGR_TABLE_PK_USER_KEY => c.p15);
265 end if;
266 --
267 if c.d16=cn then
268 L_ENRT_PERD_ID:=nn;
269 elsif c.d16 is null then 
270 L_ENRT_PERD_ID:=nh;
271 else
272 L_ENRT_PERD_ID := 
273 hr_pump_get.get_enrt_perd_id
274 (P_ENRT_PERD_USER_KEY => c.p16);
275 end if;
276 --
277 if c.d17=cn or
278 c.p12 is null then
279 L_LER_ID:=nn;
280 elsif c.d17 is null then 
281 L_LER_ID:=nh;
282 else
283 L_LER_ID := 
284 hr_pump_get.get_ler_id
285 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
286 ,P_LIFE_EVENT_REASON => c.p17
287 ,P_EFFECTIVE_DATE => c.p12);
288 end if;
289 --
290 if c.d18=cn then
291 L_PERSON_ID:=nn;
292 elsif c.d18 is null then 
293 L_PERSON_ID:=nh;
294 else
295 L_PERSON_ID := 
296 hr_pump_get.get_person_id
297 (P_PERSON_USER_KEY => c.p18);
298 end if;
299 --
300 L_REQUEST_ID := 
301 hr_pump_get.get_request_id;
302 --
303 L_PROGRAM_APPLICATION_ID := 
304 hr_pump_get.get_program_application_id;
305 --
306 L_PROGRAM_ID := 
307 hr_pump_get.get_program_id;
308 --
309 if c.p13 is null then
310 L_OBJECT_VERSION_NUMBER:=nn;
311 else
312 L_OBJECT_VERSION_NUMBER := 
313 hr_pump_get.GET_PTNL_LER_FOR_PER_OVN
314 (P_PTNL_LER_FOR_PER_USER_KEY => c.p13);
315 end if;
316 --
317 hr_data_pump.api_trc_on;
318 ben_ptnl_ler_for_per_api.UPDATE_PTNL_LER_FOR_PER
319 (p_validate => l_validate
320 ,P_PTNL_LER_FOR_PER_ID => L_PTNL_LER_FOR_PER_ID
321 ,P_CSD_BY_PTNL_LER_FOR_PER_ID => L_CSD_BY_PTNL_LER_FOR_PER_ID
322 ,P_LF_EVT_OCRD_DT => c.p1
323 ,P_TRGR_TABLE_PK_ID => L_TRGR_TABLE_PK_ID
324 ,P_PTNL_LER_FOR_PER_STAT_CD => c.p2
325 ,P_PTNL_LER_FOR_PER_SRC_CD => c.p3
326 ,P_MNL_DT => c.p4
327 ,P_ENRT_PERD_ID => L_ENRT_PERD_ID
328 ,P_LER_ID => L_LER_ID
329 ,P_PERSON_ID => L_PERSON_ID
330 ,p_business_group_id => p_business_group_id
331 ,P_DTCTD_DT => c.p5
332 ,P_PROCD_DT => c.p6
333 ,P_UNPROCD_DT => c.p7
334 ,P_VOIDD_DT => c.p8
335 ,P_MNLO_DT => c.p9
336 ,P_NTFN_DT => c.p10
337 ,P_REQUEST_ID => L_REQUEST_ID
338 ,P_PROGRAM_APPLICATION_ID => L_PROGRAM_APPLICATION_ID
339 ,P_PROGRAM_ID => L_PROGRAM_ID
340 ,P_PROGRAM_UPDATE_DATE => c.p11
341 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER
342 ,P_EFFECTIVE_DATE => c.p12);
343 hr_data_pump.api_trc_off;
344 
345 --
346 
347 --
348 close cr;
349 --
350 hr_data_pump.exit('call');
351 exception
352  when hr_multi_message.error_message_exist then
353    if cr%isopen then
354     close cr;
355    end if;
356    hr_pump_utils.set_multi_msg_error_flag(true);
357  when others then
358  if cr%isopen then
359   close cr;
360  end if;
361  raise;
362 end call;
363 end hrdpp_UPDATE_PTNL_LER_FOR_PER;