PACKAGE BODY: APPS.HRDPP_CREATE_PTNL_LER_FOR_PER

Source

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