PACKAGE BODY: APPS.HRDPP_CREATE_BENEFITS_GROUP

Source

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