DBA Data[Home] [Help]

PACKAGE BODY: APPS.HRDPP_UPDATE_BENEFITS_GROUP

Source


1 package body hrdpp_UPDATE_BENEFITS_GROUP as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2007/01/04 02:01:16
4  * Generated for API: ben_benefits_group_api.UPDATE_BENEFITS_GROUP
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_NAME in varchar2 default null
83 ,P_BNG_DESC in varchar2 default null
84 ,P_BNG_ATTRIBUTE_CATEGORY in varchar2 default null
85 ,P_BNG_ATTRIBUTE1 in varchar2 default null
86 ,P_BNG_ATTRIBUTE2 in varchar2 default null
87 ,P_BNG_ATTRIBUTE3 in varchar2 default null
88 ,P_BNG_ATTRIBUTE4 in varchar2 default null
89 ,P_BNG_ATTRIBUTE5 in varchar2 default null
90 ,P_BNG_ATTRIBUTE6 in varchar2 default null
91 ,P_BNG_ATTRIBUTE7 in varchar2 default null
92 ,P_BNG_ATTRIBUTE8 in varchar2 default null
93 ,P_BNG_ATTRIBUTE9 in varchar2 default null
94 ,P_BNG_ATTRIBUTE10 in varchar2 default null
95 ,P_BNG_ATTRIBUTE11 in varchar2 default null
96 ,P_BNG_ATTRIBUTE12 in varchar2 default null
97 ,P_BNG_ATTRIBUTE13 in varchar2 default null
98 ,P_BNG_ATTRIBUTE14 in varchar2 default null
99 ,P_BNG_ATTRIBUTE15 in varchar2 default null
100 ,P_BNG_ATTRIBUTE16 in varchar2 default null
101 ,P_BNG_ATTRIBUTE17 in varchar2 default null
102 ,P_BNG_ATTRIBUTE18 in varchar2 default null
103 ,P_BNG_ATTRIBUTE19 in varchar2 default null
104 ,P_BNG_ATTRIBUTE20 in varchar2 default null
105 ,P_BNG_ATTRIBUTE21 in varchar2 default null
106 ,P_BNG_ATTRIBUTE22 in varchar2 default null
107 ,P_BNG_ATTRIBUTE23 in varchar2 default null
108 ,P_BNG_ATTRIBUTE24 in varchar2 default null
109 ,P_BNG_ATTRIBUTE25 in varchar2 default null
110 ,P_BNG_ATTRIBUTE26 in varchar2 default null
111 ,P_BNG_ATTRIBUTE27 in varchar2 default null
112 ,P_BNG_ATTRIBUTE28 in varchar2 default null
113 ,P_BNG_ATTRIBUTE29 in varchar2 default null
114 ,P_BNG_ATTRIBUTE30 in varchar2 default null
115 ,P_BENEFITS_GROUP in varchar2) is
116 blid number := p_data_pump_batch_line_id;
117 begin
118 if blid is not null then
119 delete from hr_pump_batch_lines where batch_line_id = blid;
120 delete from hr_pump_batch_exceptions
121 where source_type = 'BATCH_LINE' and source_id = blid;
122 end if;
123 insert into hr_pump_batch_lines
124 (batch_id
125 ,batch_line_id
126 ,business_group_name
127 ,api_module_id
128 ,line_status
129 ,user_sequence
130 ,link_value
131 ,pval001
132 ,pval002
133 ,pval003
134 ,pval004
135 ,pval005
136 ,pval006
137 ,pval007
138 ,pval008
139 ,pval009
140 ,pval010
141 ,pval011
142 ,pval012
143 ,pval013
144 ,pval014
145 ,pval015
146 ,pval016
147 ,pval017
148 ,pval018
149 ,pval019
150 ,pval020
151 ,pval021
152 ,pval022
153 ,pval023
154 ,pval024
155 ,pval025
156 ,pval026
157 ,pval027
158 ,pval028
159 ,pval029
160 ,pval030
161 ,pval031
162 ,pval032
163 ,pval033
164 ,pval034)
165 values
166 (p_batch_id
167 ,nvl(blid,hr_pump_batch_lines_s.nextval)
168 ,p_data_pump_business_grp_name
169 ,104
170 ,'U'
171 ,p_user_sequence
172 ,p_link_value
173 ,P_NAME
174 ,P_BNG_DESC
175 ,P_BNG_ATTRIBUTE_CATEGORY
176 ,P_BNG_ATTRIBUTE1
177 ,P_BNG_ATTRIBUTE2
178 ,P_BNG_ATTRIBUTE3
179 ,P_BNG_ATTRIBUTE4
180 ,P_BNG_ATTRIBUTE5
181 ,P_BNG_ATTRIBUTE6
182 ,P_BNG_ATTRIBUTE7
183 ,P_BNG_ATTRIBUTE8
184 ,P_BNG_ATTRIBUTE9
185 ,P_BNG_ATTRIBUTE10
186 ,P_BNG_ATTRIBUTE11
187 ,P_BNG_ATTRIBUTE12
188 ,P_BNG_ATTRIBUTE13
189 ,P_BNG_ATTRIBUTE14
190 ,P_BNG_ATTRIBUTE15
191 ,P_BNG_ATTRIBUTE16
192 ,P_BNG_ATTRIBUTE17
193 ,P_BNG_ATTRIBUTE18
194 ,P_BNG_ATTRIBUTE19
195 ,P_BNG_ATTRIBUTE20
196 ,P_BNG_ATTRIBUTE21
197 ,P_BNG_ATTRIBUTE22
198 ,P_BNG_ATTRIBUTE23
199 ,P_BNG_ATTRIBUTE24
200 ,P_BNG_ATTRIBUTE25
201 ,P_BNG_ATTRIBUTE26
202 ,P_BNG_ATTRIBUTE27
203 ,P_BNG_ATTRIBUTE28
204 ,P_BNG_ATTRIBUTE29
205 ,P_BNG_ATTRIBUTE30
206 ,P_BENEFITS_GROUP);
207 end insert_batch_lines;
208 --
209 procedure call
210 (p_business_group_id in number,
211 p_batch_line_id     in number) is
212 cursor cr is
213 select l.rowid myrowid,
214 decode(l.pval001,cn,vn,vn,vh,l.pval001) p1,
215 l.pval001 d1,
216 decode(l.pval002,cn,vn,vn,vh,l.pval002) p2,
217 l.pval002 d2,
218 decode(l.pval003,cn,vn,vn,vh,l.pval003) p3,
219 l.pval003 d3,
220 decode(l.pval004,cn,vn,vn,vh,l.pval004) p4,
221 l.pval004 d4,
222 decode(l.pval005,cn,vn,vn,vh,l.pval005) p5,
223 l.pval005 d5,
224 decode(l.pval006,cn,vn,vn,vh,l.pval006) p6,
225 l.pval006 d6,
226 decode(l.pval007,cn,vn,vn,vh,l.pval007) p7,
227 l.pval007 d7,
228 decode(l.pval008,cn,vn,vn,vh,l.pval008) p8,
229 l.pval008 d8,
230 decode(l.pval009,cn,vn,vn,vh,l.pval009) p9,
231 l.pval009 d9,
232 decode(l.pval010,cn,vn,vn,vh,l.pval010) p10,
233 l.pval010 d10,
234 decode(l.pval011,cn,vn,vn,vh,l.pval011) p11,
235 l.pval011 d11,
236 decode(l.pval012,cn,vn,vn,vh,l.pval012) p12,
237 l.pval012 d12,
238 decode(l.pval013,cn,vn,vn,vh,l.pval013) p13,
239 l.pval013 d13,
240 decode(l.pval014,cn,vn,vn,vh,l.pval014) p14,
241 l.pval014 d14,
242 decode(l.pval015,cn,vn,vn,vh,l.pval015) p15,
243 l.pval015 d15,
244 decode(l.pval016,cn,vn,vn,vh,l.pval016) p16,
245 l.pval016 d16,
246 decode(l.pval017,cn,vn,vn,vh,l.pval017) p17,
247 l.pval017 d17,
248 decode(l.pval018,cn,vn,vn,vh,l.pval018) p18,
249 l.pval018 d18,
250 decode(l.pval019,cn,vn,vn,vh,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,vn,vn,vh,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,vn,vn,vh,l.pval023) p23,
259 l.pval023 d23,
260 decode(l.pval024,cn,vn,vn,vh,l.pval024) p24,
261 l.pval024 d24,
262 decode(l.pval025,cn,vn,vn,vh,l.pval025) p25,
263 l.pval025 d25,
264 decode(l.pval026,cn,vn,vn,vh,l.pval026) p26,
265 l.pval026 d26,
266 decode(l.pval027,cn,vn,vn,vh,l.pval027) p27,
267 l.pval027 d27,
268 decode(l.pval028,cn,vn,vn,vh,l.pval028) p28,
269 l.pval028 d28,
270 decode(l.pval029,cn,vn,vn,vh,l.pval029) p29,
271 l.pval029 d29,
272 decode(l.pval030,cn,vn,vn,vh,l.pval030) p30,
273 l.pval030 d30,
274 decode(l.pval031,cn,vn,vn,vh,l.pval031) p31,
275 l.pval031 d31,
276 decode(l.pval032,cn,vn,vn,vh,l.pval032) p32,
277 l.pval032 d32,
278 decode(l.pval033,cn,vn,vn,vh,l.pval033) p33,
279 l.pval033 d33,
280 decode(l.pval034,cn,vn,l.pval034) p34
281 from hr_pump_batch_lines l
282 where l.batch_line_id = p_batch_line_id;
283 --
284 c cr%rowtype;
285 l_validate boolean := false;
286 L_BENFTS_GRP_ID number;
287 L_OBJECT_VERSION_NUMBER number;
288 --
289 begin
290 hr_data_pump.entry('call');
291 open cr;
292 fetch cr into c;
293 if cr%notfound then
294 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
295 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
296 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
297 hr_utility.set_message_token('VALUE',p_batch_line_id);
298 hr_utility.raise_error;
299 end if;
300 --
301 if c.p34 is null then
302 L_BENFTS_GRP_ID:=nn;
303 else
304 L_BENFTS_GRP_ID := 
305 hr_pump_get.GET_BENFTS_GRP_ID
306 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
307 ,P_BENEFITS_GROUP => c.p34);
308 end if;
309 --
310 if c.p34 is null then
311 L_OBJECT_VERSION_NUMBER:=nn;
312 else
313 L_OBJECT_VERSION_NUMBER := 
314 hr_pump_get.GET_BENFTS_GRP_OVN
315 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
316 ,P_BENEFITS_GROUP => c.p34);
317 end if;
318 --
319 hr_data_pump.api_trc_on;
320 ben_benefits_group_api.UPDATE_BENEFITS_GROUP
321 (p_validate => l_validate
322 ,P_BENFTS_GRP_ID => L_BENFTS_GRP_ID
323 ,p_business_group_id => p_business_group_id
324 ,P_NAME => c.p1
325 ,P_BNG_DESC => c.p2
326 ,P_BNG_ATTRIBUTE_CATEGORY => c.p3
327 ,P_BNG_ATTRIBUTE1 => c.p4
328 ,P_BNG_ATTRIBUTE2 => c.p5
329 ,P_BNG_ATTRIBUTE3 => c.p6
330 ,P_BNG_ATTRIBUTE4 => c.p7
331 ,P_BNG_ATTRIBUTE5 => c.p8
332 ,P_BNG_ATTRIBUTE6 => c.p9
333 ,P_BNG_ATTRIBUTE7 => c.p10
334 ,P_BNG_ATTRIBUTE8 => c.p11
335 ,P_BNG_ATTRIBUTE9 => c.p12
336 ,P_BNG_ATTRIBUTE10 => c.p13
337 ,P_BNG_ATTRIBUTE11 => c.p14
338 ,P_BNG_ATTRIBUTE12 => c.p15
339 ,P_BNG_ATTRIBUTE13 => c.p16
340 ,P_BNG_ATTRIBUTE14 => c.p17
341 ,P_BNG_ATTRIBUTE15 => c.p18
342 ,P_BNG_ATTRIBUTE16 => c.p19
343 ,P_BNG_ATTRIBUTE17 => c.p20
344 ,P_BNG_ATTRIBUTE18 => c.p21
345 ,P_BNG_ATTRIBUTE19 => c.p22
346 ,P_BNG_ATTRIBUTE20 => c.p23
347 ,P_BNG_ATTRIBUTE21 => c.p24
348 ,P_BNG_ATTRIBUTE22 => c.p25
349 ,P_BNG_ATTRIBUTE23 => c.p26
350 ,P_BNG_ATTRIBUTE24 => c.p27
351 ,P_BNG_ATTRIBUTE25 => c.p28
352 ,P_BNG_ATTRIBUTE26 => c.p29
353 ,P_BNG_ATTRIBUTE27 => c.p30
354 ,P_BNG_ATTRIBUTE28 => c.p31
355 ,P_BNG_ATTRIBUTE29 => c.p32
356 ,P_BNG_ATTRIBUTE30 => c.p33
357 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
358 hr_data_pump.api_trc_off;
359 
360 --
361 
362 --
363 close cr;
364 --
365 hr_data_pump.exit('call');
366 exception
367  when hr_multi_message.error_message_exist then
368    if cr%isopen then
369     close cr;
370    end if;
371    hr_pump_utils.set_multi_msg_error_flag(true);
372  when others then
373  if cr%isopen then
374   close cr;
375  end if;
376  raise;
377 end call;
378 end hrdpp_UPDATE_BENEFITS_GROUP;