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_CREATE_BEN_ASG

Source


1 package body hrdpp_CREATE_BEN_ASG as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2007/01/04 02:01:49
4  * Generated for API: BEN_ASSIGNMENT_API.CREATE_BEN_ASG
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_EVENT_MODE in boolean default null
83 ,P_EFFECTIVE_DATE in date
84 ,P_CHANGE_REASON in varchar2 default null
85 ,P_COMMENTS in varchar2 default null
86 ,P_DATE_PROBATION_END in date default null
87 ,P_EMPLOYMENT_CATEGORY in varchar2 default null
88 ,P_FREQUENCY in varchar2 default null
89 ,P_INTERNAL_ADDRESS_LINE in varchar2 default null
90 ,P_MANAGER_FLAG in varchar2 default null
91 ,P_NORMAL_HOURS in number default null
92 ,P_PERF_REVIEW_PERIOD in number default null
93 ,P_PERF_REVIEW_PERIOD_FREQUENCY in varchar2 default null
94 ,P_PROBATION_PERIOD in number default null
95 ,P_PROBATION_UNIT in varchar2 default null
96 ,P_SAL_REVIEW_PERIOD in number default null
97 ,P_SAL_REVIEW_PERIOD_FREQUENCY in varchar2 default null
98 ,P_SOURCE_TYPE in varchar2 default null
99 ,P_TIME_NORMAL_FINISH in varchar2 default null
100 ,P_TIME_NORMAL_START in varchar2 default null
101 ,P_BARGAINING_UNIT_CODE in varchar2 default null
102 ,P_LABOUR_UNION_MEMBER_FLAG in varchar2 default null
103 ,P_HOURLY_SALARIED_CODE in varchar2 default null
104 ,P_ASS_ATTRIBUTE_CATEGORY in varchar2 default null
105 ,P_ASS_ATTRIBUTE1 in varchar2 default null
106 ,P_ASS_ATTRIBUTE2 in varchar2 default null
107 ,P_ASS_ATTRIBUTE3 in varchar2 default null
108 ,P_ASS_ATTRIBUTE4 in varchar2 default null
109 ,P_ASS_ATTRIBUTE5 in varchar2 default null
110 ,P_ASS_ATTRIBUTE6 in varchar2 default null
111 ,P_ASS_ATTRIBUTE7 in varchar2 default null
112 ,P_ASS_ATTRIBUTE8 in varchar2 default null
113 ,P_ASS_ATTRIBUTE9 in varchar2 default null
114 ,P_ASS_ATTRIBUTE10 in varchar2 default null
115 ,P_ASS_ATTRIBUTE11 in varchar2 default null
116 ,P_ASS_ATTRIBUTE12 in varchar2 default null
117 ,P_ASS_ATTRIBUTE13 in varchar2 default null
118 ,P_ASS_ATTRIBUTE14 in varchar2 default null
119 ,P_ASS_ATTRIBUTE15 in varchar2 default null
120 ,P_ASS_ATTRIBUTE16 in varchar2 default null
121 ,P_ASS_ATTRIBUTE17 in varchar2 default null
122 ,P_ASS_ATTRIBUTE18 in varchar2 default null
123 ,P_ASS_ATTRIBUTE19 in varchar2 default null
124 ,P_ASS_ATTRIBUTE20 in varchar2 default null
125 ,P_ASS_ATTRIBUTE21 in varchar2 default null
126 ,P_ASS_ATTRIBUTE22 in varchar2 default null
127 ,P_ASS_ATTRIBUTE23 in varchar2 default null
128 ,P_ASS_ATTRIBUTE24 in varchar2 default null
129 ,P_ASS_ATTRIBUTE25 in varchar2 default null
130 ,P_ASS_ATTRIBUTE26 in varchar2 default null
131 ,P_ASS_ATTRIBUTE27 in varchar2 default null
132 ,P_ASS_ATTRIBUTE28 in varchar2 default null
133 ,P_ASS_ATTRIBUTE29 in varchar2 default null
134 ,P_ASS_ATTRIBUTE30 in varchar2 default null
135 ,P_TITLE in varchar2 default null
136 ,P_AGE in number default null
137 ,P_ADJUSTED_SERVICE_DATE in date default null
138 ,P_ORIGINAL_HIRE_DATE in date default null
139 ,P_SALARY in varchar2 default null
140 ,P_TERMINATION_DATE in date default null
141 ,P_TERMINATION_REASON in varchar2 default null
142 ,P_LEAVE_OF_ABSENCE_DATE in date default null
143 ,P_ABSENCE_REASON in varchar2 default null
144 ,P_DATE_OF_HIRE in date default null
145 ,P_CALLED_FROM in varchar2 default null
146 ,P_ASSIGNMENT_USER_KEY in varchar2
147 ,P_PERSON_USER_KEY in varchar2
148 ,P_ORGANIZATION_NAME in varchar2
149 ,P_LANGUAGE_CODE in varchar2
150 ,P_GRADE_NAME in varchar2 default null
151 ,P_POSITION_NAME in varchar2 default null
152 ,P_JOB_NAME in varchar2 default null
153 ,P_USER_STATUS in varchar2 default null
154 ,P_PAYROLL_NAME in varchar2 default null
155 ,P_LOCATION_CODE in varchar2 default null
156 ,P_SUPERVISOR_USER_KEY in varchar2 default null
157 ,P_SPECIAL_CEILIN_STEP_USER_KEY in varchar2 default null
158 ,P_PEOPLE_GROUP_USER_NAME in varchar2 default null
159 ,P_CON_SEG_USER_NAME in varchar2 default null
160 ,P_PAY_BASIS_NAME in varchar2 default null
161 ,P_DEFAULT_CODE_COMB_USER_KEY in varchar2 default null
162 ,P_SET_OF_BOOKS_NAME in varchar2 default null
163 ,P_USER_PERSON_TYPE in varchar2 default null
164 ,P_AAT_USER_NAME in varchar2 default null) is
165 blid number := p_data_pump_batch_line_id;
166  L_EVENT_MODE varchar2(5);
167 begin
168 if P_EVENT_MODE is null then
169  L_EVENT_MODE := null;
170 elsif P_EVENT_MODE then
171  L_EVENT_MODE := 'TRUE';
172 else 
173  L_EVENT_MODE := 'FALSE';
174 end if;
175 if blid is not null then
176 delete from hr_pump_batch_lines where batch_line_id = blid;
177 delete from hr_pump_batch_exceptions
178 where source_type = 'BATCH_LINE' and source_id = blid;
179 end if;
180 insert into hr_pump_batch_lines
181 (batch_id
182 ,batch_line_id
183 ,business_group_name
184 ,api_module_id
185 ,line_status
186 ,user_sequence
187 ,link_value
188 ,pval001
189 ,pval002
190 ,pval003
191 ,pval004
192 ,pval005
193 ,pval006
194 ,pval007
195 ,pval008
196 ,pval009
197 ,pval010
198 ,pval011
199 ,pval012
200 ,pval013
201 ,pval014
202 ,pval015
203 ,pval016
204 ,pval017
205 ,pval018
206 ,pval019
207 ,pval020
208 ,pval021
209 ,pval022
210 ,pval023
211 ,pval024
212 ,pval025
213 ,pval026
214 ,pval027
215 ,pval028
216 ,pval029
217 ,pval030
218 ,pval031
219 ,pval032
220 ,pval033
221 ,pval034
222 ,pval035
223 ,pval036
224 ,pval037
225 ,pval038
226 ,pval039
227 ,pval040
228 ,pval041
229 ,pval042
230 ,pval043
231 ,pval044
232 ,pval045
233 ,pval046
234 ,pval047
235 ,pval048
236 ,pval049
237 ,pval050
238 ,pval051
239 ,pval052
240 ,pval053
241 ,pval054
242 ,pval055
243 ,pval056
244 ,pval057
245 ,pval058
246 ,pval059
247 ,pval060
248 ,pval061
249 ,pval062
250 ,pval063
251 ,pval064
252 ,pval065
253 ,pval071
254 ,pval072
255 ,pval073
256 ,pval074
257 ,pval075
258 ,pval076
259 ,pval077
260 ,pval078
261 ,pval079
262 ,pval080
263 ,pval081
264 ,pval082
265 ,pval083
266 ,pval084
267 ,pval085
268 ,pval086
269 ,pval087
270 ,pval088)
271 values
272 (p_batch_id
273 ,nvl(blid,hr_pump_batch_lines_s.nextval)
274 ,p_data_pump_business_grp_name
275 ,1680
276 ,'U'
277 ,p_user_sequence
278 ,p_link_value
279 ,L_EVENT_MODE
280 ,dc(P_EFFECTIVE_DATE)
281 ,P_CHANGE_REASON
282 ,P_COMMENTS
283 ,dc(P_DATE_PROBATION_END)
284 ,P_EMPLOYMENT_CATEGORY
285 ,P_FREQUENCY
286 ,P_INTERNAL_ADDRESS_LINE
287 ,P_MANAGER_FLAG
288 ,P_NORMAL_HOURS
289 ,P_PERF_REVIEW_PERIOD
290 ,P_PERF_REVIEW_PERIOD_FREQUENCY
291 ,P_PROBATION_PERIOD
292 ,P_PROBATION_UNIT
293 ,P_SAL_REVIEW_PERIOD
294 ,P_SAL_REVIEW_PERIOD_FREQUENCY
295 ,P_SOURCE_TYPE
296 ,P_TIME_NORMAL_FINISH
297 ,P_TIME_NORMAL_START
298 ,P_BARGAINING_UNIT_CODE
299 ,P_LABOUR_UNION_MEMBER_FLAG
300 ,P_HOURLY_SALARIED_CODE
301 ,P_ASS_ATTRIBUTE_CATEGORY
302 ,P_ASS_ATTRIBUTE1
303 ,P_ASS_ATTRIBUTE2
304 ,P_ASS_ATTRIBUTE3
305 ,P_ASS_ATTRIBUTE4
306 ,P_ASS_ATTRIBUTE5
307 ,P_ASS_ATTRIBUTE6
308 ,P_ASS_ATTRIBUTE7
309 ,P_ASS_ATTRIBUTE8
310 ,P_ASS_ATTRIBUTE9
311 ,P_ASS_ATTRIBUTE10
312 ,P_ASS_ATTRIBUTE11
313 ,P_ASS_ATTRIBUTE12
314 ,P_ASS_ATTRIBUTE13
315 ,P_ASS_ATTRIBUTE14
316 ,P_ASS_ATTRIBUTE15
317 ,P_ASS_ATTRIBUTE16
318 ,P_ASS_ATTRIBUTE17
319 ,P_ASS_ATTRIBUTE18
320 ,P_ASS_ATTRIBUTE19
321 ,P_ASS_ATTRIBUTE20
322 ,P_ASS_ATTRIBUTE21
323 ,P_ASS_ATTRIBUTE22
324 ,P_ASS_ATTRIBUTE23
325 ,P_ASS_ATTRIBUTE24
326 ,P_ASS_ATTRIBUTE25
327 ,P_ASS_ATTRIBUTE26
328 ,P_ASS_ATTRIBUTE27
329 ,P_ASS_ATTRIBUTE28
330 ,P_ASS_ATTRIBUTE29
331 ,P_ASS_ATTRIBUTE30
332 ,P_TITLE
333 ,P_AGE
334 ,dc(P_ADJUSTED_SERVICE_DATE)
335 ,dc(P_ORIGINAL_HIRE_DATE)
336 ,P_SALARY
337 ,dc(P_TERMINATION_DATE)
338 ,P_TERMINATION_REASON
339 ,dc(P_LEAVE_OF_ABSENCE_DATE)
340 ,P_ABSENCE_REASON
341 ,dc(P_DATE_OF_HIRE)
342 ,P_CALLED_FROM
343 ,P_ASSIGNMENT_USER_KEY
344 ,P_PERSON_USER_KEY
345 ,P_ORGANIZATION_NAME
346 ,P_LANGUAGE_CODE
347 ,P_GRADE_NAME
348 ,P_POSITION_NAME
349 ,P_JOB_NAME
350 ,P_USER_STATUS
351 ,P_PAYROLL_NAME
352 ,P_LOCATION_CODE
353 ,P_SUPERVISOR_USER_KEY
354 ,P_SPECIAL_CEILIN_STEP_USER_KEY
355 ,P_PEOPLE_GROUP_USER_NAME
356 ,P_CON_SEG_USER_NAME
357 ,P_PAY_BASIS_NAME
358 ,P_DEFAULT_CODE_COMB_USER_KEY
359 ,P_SET_OF_BOOKS_NAME
360 ,P_USER_PERSON_TYPE
361 ,P_AAT_USER_NAME);
362 end insert_batch_lines;
363 --
364 procedure call
365 (p_business_group_id in number,
366 p_batch_line_id     in number) is
367 cursor cr is
368 select l.rowid myrowid,
369 decode(l.pval001,cn,vn,vn,null,l.pval001) p1,
370 l.pval001 d1,
371 decode(l.pval002,cn,dn,d(l.pval002)) p2,
372 decode(l.pval003,cn,vn,vn,vn,l.pval003) p3,
373 l.pval003 d3,
374 decode(l.pval004,cn,vn,vn,vn,l.pval004) p4,
375 l.pval004 d4,
376 decode(l.pval005,cn,dn,vn,dn,d(l.pval005)) p5,
377 l.pval005 d5,
378 decode(l.pval006,cn,vn,vn,vn,
379  hr_pump_get.gl(l.pval006,'EMP_CAT',d(l.pval002),l.pval073)) p6,
380 l.pval006 d6,
381 decode(l.pval007,cn,vn,vn,vn,
382  hr_pump_get.gl(l.pval007,'FREQUENCY',d(l.pval002),l.pval073)) p7,
383 l.pval007 d7,
384 decode(l.pval008,cn,vn,vn,vn,l.pval008) p8,
385 l.pval008 d8,
386 decode(l.pval009,cn,vn,vn,vn,
387  hr_pump_get.gl(l.pval009,'YES_NO',d(l.pval002),l.pval073)) p9,
388 l.pval009 d9,
389 decode(l.pval010,cn,nn,vn,nn,n(l.pval010)) p10,
390 l.pval010 d10,
391 decode(l.pval011,cn,nn,vn,nn,n(l.pval011)) p11,
392 l.pval011 d11,
393 decode(l.pval012,cn,vn,vn,vn,
394  hr_pump_get.gl(l.pval012,'FREQUENCY',d(l.pval002),l.pval073)) p12,
395 l.pval012 d12,
396 decode(l.pval013,cn,nn,vn,nn,n(l.pval013)) p13,
397 l.pval013 d13,
398 decode(l.pval014,cn,vn,vn,vn,
399  hr_pump_get.gl(l.pval014,'QUALIFYING_UNITS',d(l.pval002),l.pval073)) p14,
400 l.pval014 d14,
401 decode(l.pval015,cn,nn,vn,nn,n(l.pval015)) p15,
402 l.pval015 d15,
403 decode(l.pval016,cn,vn,vn,vn,
404  hr_pump_get.gl(l.pval016,'FREQUENCY',d(l.pval002),l.pval073)) p16,
405 l.pval016 d16,
406 decode(l.pval017,cn,vn,vn,vn,
407  hr_pump_get.gl(l.pval017,'REC_TYPE',d(l.pval002),l.pval073)) p17,
408 l.pval017 d17,
409 decode(l.pval018,cn,vn,vn,vn,l.pval018) p18,
410 l.pval018 d18,
411 decode(l.pval019,cn,vn,vn,vn,l.pval019) p19,
412 l.pval019 d19,
413 decode(l.pval020,cn,vn,vn,vn,
414  hr_pump_get.gl(l.pval020,'BARGAINING_UNIT_CODE',d(l.pval002),l.pval073)) p20,
415 l.pval020 d20,
416 decode(l.pval021,cn,vn,vn,vn,l.pval021) p21,
417 l.pval021 d21,
418 decode(l.pval022,cn,vn,vn,vn,
419  hr_pump_get.gl(l.pval022,'HOURLY_SALARIED_CODE',d(l.pval002),l.pval073)) p22,
420 l.pval022 d22,
421 decode(l.pval023,cn,vn,vn,vn,l.pval023) p23,
422 l.pval023 d23,
423 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
424 l.pval024 d24,
425 decode(l.pval025,cn,vn,vn,vn,l.pval025) p25,
426 l.pval025 d25,
427 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
428 l.pval026 d26,
429 decode(l.pval027,cn,vn,vn,vn,l.pval027) p27,
430 l.pval027 d27,
431 decode(l.pval028,cn,vn,vn,vn,l.pval028) p28,
432 l.pval028 d28,
433 decode(l.pval029,cn,vn,vn,vn,l.pval029) p29,
434 l.pval029 d29,
435 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
436 l.pval030 d30,
437 decode(l.pval031,cn,vn,vn,vn,l.pval031) p31,
438 l.pval031 d31,
439 decode(l.pval032,cn,vn,vn,vn,l.pval032) p32,
440 l.pval032 d32,
441 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
442 l.pval033 d33,
443 decode(l.pval034,cn,vn,vn,vn,l.pval034) p34,
444 l.pval034 d34,
445 decode(l.pval035,cn,vn,vn,vn,l.pval035) p35,
446 l.pval035 d35,
447 decode(l.pval036,cn,vn,vn,vn,l.pval036) p36,
448 l.pval036 d36,
449 decode(l.pval037,cn,vn,vn,vn,l.pval037) p37,
450 l.pval037 d37,
451 decode(l.pval038,cn,vn,vn,vn,l.pval038) p38,
452 l.pval038 d38,
453 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
454 l.pval039 d39,
455 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
456 l.pval040 d40,
457 decode(l.pval041,cn,vn,vn,vn,l.pval041) p41,
458 l.pval041 d41,
459 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
460 l.pval042 d42,
461 decode(l.pval043,cn,vn,vn,vn,l.pval043) p43,
462 l.pval043 d43,
463 decode(l.pval044,cn,vn,vn,vn,l.pval044) p44,
464 l.pval044 d44,
465 decode(l.pval045,cn,vn,vn,vn,l.pval045) p45,
466 l.pval045 d45,
467 decode(l.pval046,cn,vn,vn,vn,l.pval046) p46,
468 l.pval046 d46,
469 decode(l.pval047,cn,vn,vn,vn,l.pval047) p47,
470 l.pval047 d47,
471 decode(l.pval048,cn,vn,vn,vn,l.pval048) p48,
472 l.pval048 d48,
473 decode(l.pval049,cn,vn,vn,vn,l.pval049) p49,
474 l.pval049 d49,
475 decode(l.pval050,cn,vn,vn,vn,l.pval050) p50,
476 l.pval050 d50,
477 decode(l.pval051,cn,vn,vn,vn,l.pval051) p51,
478 l.pval051 d51,
479 decode(l.pval052,cn,vn,vn,vn,l.pval052) p52,
480 l.pval052 d52,
481 decode(l.pval053,cn,vn,vn,vn,l.pval053) p53,
482 l.pval053 d53,
483 decode(l.pval054,cn,vn,vn,vn,l.pval054) p54,
484 l.pval054 d54,
485 decode(l.pval055,cn,nn,vn,nn,n(l.pval055)) p55,
489 decode(l.pval057,cn,dn,vn,dn,d(l.pval057)) p57,
486 l.pval055 d55,
487 decode(l.pval056,cn,dn,vn,dn,d(l.pval056)) p56,
488 l.pval056 d56,
490 l.pval057 d57,
491 decode(l.pval058,cn,vn,vn,vn,l.pval058) p58,
492 l.pval058 d58,
493 decode(l.pval059,cn,dn,vn,dn,d(l.pval059)) p59,
494 l.pval059 d59,
495 decode(l.pval060,cn,vn,vn,vn,
496  hr_pump_get.gl(l.pval060,'LEAV_REAS',d(l.pval002),l.pval073)) p60,
497 l.pval060 d60,
498 decode(l.pval061,cn,dn,vn,dn,d(l.pval061)) p61,
499 l.pval061 d61,
500 decode(l.pval062,cn,vn,vn,vn,l.pval062) p62,
501 l.pval062 d62,
502 decode(l.pval063,cn,dn,vn,dn,d(l.pval063)) p63,
503 l.pval063 d63,
504 decode(l.pval064,cn,vn,vn,vn,l.pval064) p64,
505 l.pval064 d64,
506 l.pval065 p65,
507 l.pval066 p66,
508 decode(l.pval067,cn,dn,d(l.pval067)) p67,
509 decode(l.pval068,cn,dn,d(l.pval068)) p68,
510 l.pval069 p69,
511 l.pval070 p70,
512 decode(l.pval071,cn,vn,l.pval071) p71,
513 decode(l.pval072,cn,vn,l.pval072) p72,
514 decode(l.pval073,cn,vn,l.pval073) p73,
515 decode(l.pval074,cn,vn,vn,vn,l.pval074) p74,
516 l.pval074 d74,
517 decode(l.pval075,cn,vn,vn,vn,l.pval075) p75,
518 l.pval075 d75,
519 decode(l.pval076,cn,vn,vn,vn,l.pval076) p76,
520 l.pval076 d76,
521 decode(l.pval077,cn,vn,vn,vn,l.pval077) p77,
522 l.pval077 d77,
523 decode(l.pval078,cn,vn,vn,vn,l.pval078) p78,
524 l.pval078 d78,
525 decode(l.pval079,cn,vn,vn,vn,l.pval079) p79,
526 l.pval079 d79,
527 decode(l.pval080,cn,vn,vn,vn,l.pval080) p80,
528 l.pval080 d80,
529 decode(l.pval081,cn,vn,vn,vn,l.pval081) p81,
530 l.pval081 d81,
531 decode(l.pval082,cn,vn,vn,vn,l.pval082) p82,
532 l.pval082 d82,
533 decode(l.pval083,cn,vn,vn,vn,l.pval083) p83,
534 l.pval083 d83,
535 decode(l.pval084,cn,vn,vn,vn,l.pval084) p84,
536 l.pval084 d84,
537 decode(l.pval085,cn,vn,vn,vn,l.pval085) p85,
538 l.pval085 d85,
539 decode(l.pval086,cn,vn,vn,vn,l.pval086) p86,
540 l.pval086 d86,
541 decode(l.pval087,cn,vn,vn,vn,l.pval087) p87,
542 l.pval087 d87,
543 decode(l.pval088,cn,vn,vn,vn,l.pval088) p88,
544 l.pval088 d88
545 from hr_pump_batch_lines l
546 where l.batch_line_id = p_batch_line_id;
547 --
548 c cr%rowtype;
549 l_validate boolean := false;
550 L_EVENT_MODE boolean;
551 L_ASSIGNMENT_ID number;
552 L_PERSON_ID number;
553 L_ORGANIZATION_ID number;
554 L_GRADE_ID number;
555 L_POSITION_ID number;
556 L_JOB_ID number;
557 L_ASSIGNMENT_STATUS_TYPE_ID number;
558 L_PAYROLL_ID number;
559 L_LOCATION_ID number;
560 L_SUPERVISOR_ID number;
561 L_SPECIAL_CEILING_STEP_ID number;
562 L_PEOPLE_GROUP_ID number;
563 L_SOFT_CODING_KEYFLEX_ID number;
564 L_PAY_BASIS_ID number;
565 L_DEFAULT_CODE_COMB_ID number;
566 L_SET_OF_BOOKS_ID number;
567 L_ORIGINAL_PERSON_TYPE varchar2(2000);
568 L_ABSENCE_TYPE varchar2(2000);
569 --
570 begin
571 hr_data_pump.entry('call');
572 open cr;
573 fetch cr into c;
574 if cr%notfound then
575 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
576 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
577 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
578 hr_utility.set_message_token('VALUE',p_batch_line_id);
579 hr_utility.raise_error;
580 end if;
581 --
582 if upper(c.p1) = 'TRUE' then
583 L_EVENT_MODE := true;
584 elsif upper(c.p1) = 'FALSE' then
585 L_EVENT_MODE := false;
586 elsif c.p1 is not null then
587 hr_utility.set_message(800,'HR_50327_DP_TYPE_ERR');
588 hr_utility.set_message_token('TYPE','BOOLEAN');
589 hr_utility.set_message_token('PARAMETER','P_EVENT_MODE');
590 hr_utility.set_message_token('VALUE',c.p1);
591 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
592 hr_utility.raise_error;
593 end if;
594 --
595 if c.p71 is null then
596 L_PERSON_ID:=nn;
597 else
598 L_PERSON_ID := 
599 hr_pump_get.get_person_id
600 (P_PERSON_USER_KEY => c.p71);
601 end if;
602 --
603 if c.p72 is null or
604 c.p2 is null or
605 c.p73 is null then
606 L_ORGANIZATION_ID:=nn;
607 else
608 L_ORGANIZATION_ID := 
609 hr_pump_get.GET_ORGANIZATION_ID
610 (P_ORGANIZATION_NAME => c.p72
611 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
612 ,P_EFFECTIVE_DATE => c.p2
613 ,P_LANGUAGE_CODE => c.p73);
614 end if;
615 --
616 if c.p74 is null or
617 c.p2 is null then
618 L_GRADE_ID:=nn;
619 else
620 L_GRADE_ID := 
621 hr_pump_get.GET_GRADE_ID
622 (P_GRADE_NAME => c.p74
623 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
624 ,P_EFFECTIVE_DATE => c.p2);
625 end if;
626 --
627 if c.p75 is null or
628 c.p2 is null then
629 L_POSITION_ID:=nn;
630 else
631 L_POSITION_ID := 
632 hr_pump_get.GET_POSITION_ID
633 (P_POSITION_NAME => c.p75
634 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
635 ,P_EFFECTIVE_DATE => c.p2);
636 end if;
637 --
638 if c.p76 is null or
639 c.p2 is null then
640 L_JOB_ID:=nn;
641 else
642 L_JOB_ID := 
643 hr_pump_get.GET_JOB_ID
644 (P_JOB_NAME => c.p76
645 ,P_EFFECTIVE_DATE => c.p2
646 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID);
647 end if;
648 --
649 if c.p77 is null or
650 c.p73 is null then
651 L_ASSIGNMENT_STATUS_TYPE_ID:=nn;
652 else
653 L_ASSIGNMENT_STATUS_TYPE_ID := 
654 hr_pump_get.GET_ASSIGNMENT_STATUS_TYPE_ID
655 (P_USER_STATUS => c.p77
656 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
657 ,P_LANGUAGE_CODE => c.p73);
658 end if;
659 --
660 if c.p78 is null or
661 c.p2 is null then
662 L_PAYROLL_ID:=nn;
663 else
664 L_PAYROLL_ID := 
665 hr_pump_get.GET_PAYROLL_ID
666 (P_PAYROLL_NAME => c.p78
670 --
667 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
668 ,P_EFFECTIVE_DATE => c.p2);
669 end if;
671 if c.p79 is null or
672 c.p73 is null then
673 L_LOCATION_ID:=nn;
674 else
675 L_LOCATION_ID := 
676 hr_pump_get.GET_LOCATION_ID
677 (P_LOCATION_CODE => c.p79
678 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
679 ,P_LANGUAGE_CODE => c.p73);
680 end if;
681 --
682 if c.p80 is null then
683 L_SUPERVISOR_ID:=nn;
684 else
685 L_SUPERVISOR_ID := 
686 hr_pump_get.GET_SUPERVISOR_ID
687 (P_SUPERVISOR_USER_KEY => c.p80);
688 end if;
689 --
690 if c.p81 is null then
691 L_SPECIAL_CEILING_STEP_ID:=nn;
692 else
693 L_SPECIAL_CEILING_STEP_ID := 
694 hr_pump_get.GET_SPECIAL_CEILING_STEP_ID
695 (P_SPECIAL_CEILIN_STEP_USER_KEY => c.p81);
696 end if;
697 --
698 if c.p82 is null or
699 c.p2 is null then
700 L_PEOPLE_GROUP_ID:=nn;
701 else
702 L_PEOPLE_GROUP_ID := 
703 hr_pump_get.GET_PEOPLE_GROUP_ID
704 (P_PEOPLE_GROUP_USER_NAME => c.p82
705 ,P_EFFECTIVE_DATE => c.p2);
706 end if;
707 --
708 if c.p83 is null or
709 c.p2 is null then
710 L_SOFT_CODING_KEYFLEX_ID:=nn;
711 else
712 L_SOFT_CODING_KEYFLEX_ID := 
713 hr_pump_get.GET_SOFT_CODING_KEYFLEX_ID
714 (P_CON_SEG_USER_NAME => c.p83
715 ,P_EFFECTIVE_DATE => c.p2);
716 end if;
717 --
718 if c.p84 is null then
719 L_PAY_BASIS_ID:=nn;
720 else
721 L_PAY_BASIS_ID := 
722 hr_pump_get.GET_PAY_BASIS_ID
723 (P_PAY_BASIS_NAME => c.p84
724 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID);
725 end if;
726 --
727 if c.p85 is null then
728 L_DEFAULT_CODE_COMB_ID:=nn;
729 else
730 L_DEFAULT_CODE_COMB_ID := 
731 hr_pump_get.GET_DEFAULT_CODE_COMB_ID
732 (P_DEFAULT_CODE_COMB_USER_KEY => c.p85);
733 end if;
734 --
735 if c.p86 is null then
736 L_SET_OF_BOOKS_ID:=nn;
737 else
738 L_SET_OF_BOOKS_ID := 
739 hr_pump_get.GET_SET_OF_BOOKS_ID
740 (P_SET_OF_BOOKS_NAME => c.p86);
741 end if;
742 --
743 if c.p87 is null or
744 c.p73 is null then
745 L_ORIGINAL_PERSON_TYPE:=vn;
746 else
747 L_ORIGINAL_PERSON_TYPE := 
748 hr_pump_get.GET_PERSON_TYPE_ID
749 (P_USER_PERSON_TYPE => c.p87
750 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
751 ,P_LANGUAGE_CODE => c.p73);
752 end if;
753 --
754 if c.p88 is null or
755 c.p2 is null then
756 L_ABSENCE_TYPE:=vn;
757 else
758 L_ABSENCE_TYPE := 
759 hr_pump_get.GET_ABSENCE_ATTENDANCE_TYPE_ID
760 (P_AAT_USER_NAME => c.p88
761 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
762 ,P_EFFECTIVE_DATE => c.p2);
763 end if;
764 --
765 hr_data_pump.api_trc_on;
766 BEN_ASSIGNMENT_API.CREATE_BEN_ASG
767 (p_validate => l_validate
768 ,P_EVENT_MODE => L_EVENT_MODE
769 ,P_EFFECTIVE_DATE => c.p2
770 ,P_PERSON_ID => L_PERSON_ID
771 ,P_ORGANIZATION_ID => L_ORGANIZATION_ID
772 ,P_GRADE_ID => L_GRADE_ID
773 ,P_POSITION_ID => L_POSITION_ID
774 ,P_JOB_ID => L_JOB_ID
775 ,P_ASSIGNMENT_STATUS_TYPE_ID => L_ASSIGNMENT_STATUS_TYPE_ID
776 ,P_PAYROLL_ID => L_PAYROLL_ID
777 ,P_LOCATION_ID => L_LOCATION_ID
778 ,P_SUPERVISOR_ID => L_SUPERVISOR_ID
779 ,P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_STEP_ID
780 ,P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID
781 ,P_SOFT_CODING_KEYFLEX_ID => L_SOFT_CODING_KEYFLEX_ID
782 ,P_PAY_BASIS_ID => L_PAY_BASIS_ID
783 ,P_CHANGE_REASON => c.p3
784 ,P_COMMENTS => c.p4
785 ,P_DATE_PROBATION_END => c.p5
786 ,P_DEFAULT_CODE_COMB_ID => L_DEFAULT_CODE_COMB_ID
787 ,P_EMPLOYMENT_CATEGORY => c.p6
788 ,P_FREQUENCY => c.p7
789 ,P_INTERNAL_ADDRESS_LINE => c.p8
790 ,P_MANAGER_FLAG => c.p9
791 ,P_NORMAL_HOURS => c.p10
792 ,P_PERF_REVIEW_PERIOD => c.p11
793 ,P_PERF_REVIEW_PERIOD_FREQUENCY => c.p12
794 ,P_PROBATION_PERIOD => c.p13
795 ,P_PROBATION_UNIT => c.p14
796 ,P_SAL_REVIEW_PERIOD => c.p15
797 ,P_SAL_REVIEW_PERIOD_FREQUENCY => c.p16
798 ,P_SET_OF_BOOKS_ID => L_SET_OF_BOOKS_ID
799 ,P_SOURCE_TYPE => c.p17
800 ,P_TIME_NORMAL_FINISH => c.p18
801 ,P_TIME_NORMAL_START => c.p19
802 ,P_BARGAINING_UNIT_CODE => c.p20
803 ,P_LABOUR_UNION_MEMBER_FLAG => c.p21
804 ,P_HOURLY_SALARIED_CODE => c.p22
805 ,P_ASS_ATTRIBUTE_CATEGORY => c.p23
806 ,P_ASS_ATTRIBUTE1 => c.p24
807 ,P_ASS_ATTRIBUTE2 => c.p25
808 ,P_ASS_ATTRIBUTE3 => c.p26
809 ,P_ASS_ATTRIBUTE4 => c.p27
810 ,P_ASS_ATTRIBUTE5 => c.p28
811 ,P_ASS_ATTRIBUTE6 => c.p29
812 ,P_ASS_ATTRIBUTE7 => c.p30
813 ,P_ASS_ATTRIBUTE8 => c.p31
814 ,P_ASS_ATTRIBUTE9 => c.p32
815 ,P_ASS_ATTRIBUTE10 => c.p33
816 ,P_ASS_ATTRIBUTE11 => c.p34
817 ,P_ASS_ATTRIBUTE12 => c.p35
818 ,P_ASS_ATTRIBUTE13 => c.p36
819 ,P_ASS_ATTRIBUTE14 => c.p37
820 ,P_ASS_ATTRIBUTE15 => c.p38
821 ,P_ASS_ATTRIBUTE16 => c.p39
822 ,P_ASS_ATTRIBUTE17 => c.p40
823 ,P_ASS_ATTRIBUTE18 => c.p41
824 ,P_ASS_ATTRIBUTE19 => c.p42
825 ,P_ASS_ATTRIBUTE20 => c.p43
826 ,P_ASS_ATTRIBUTE21 => c.p44
827 ,P_ASS_ATTRIBUTE22 => c.p45
828 ,P_ASS_ATTRIBUTE23 => c.p46
829 ,P_ASS_ATTRIBUTE24 => c.p47
830 ,P_ASS_ATTRIBUTE25 => c.p48
831 ,P_ASS_ATTRIBUTE26 => c.p49
832 ,P_ASS_ATTRIBUTE27 => c.p50
833 ,P_ASS_ATTRIBUTE28 => c.p51
837 ,P_AGE => c.p55
834 ,P_ASS_ATTRIBUTE29 => c.p52
835 ,P_ASS_ATTRIBUTE30 => c.p53
836 ,P_TITLE => c.p54
838 ,P_ADJUSTED_SERVICE_DATE => c.p56
839 ,P_ORIGINAL_HIRE_DATE => c.p57
840 ,P_SALARY => c.p58
841 ,P_ORIGINAL_PERSON_TYPE => L_ORIGINAL_PERSON_TYPE
842 ,P_TERMINATION_DATE => c.p59
843 ,P_TERMINATION_REASON => c.p60
844 ,P_LEAVE_OF_ABSENCE_DATE => c.p61
845 ,P_ABSENCE_TYPE => L_ABSENCE_TYPE
846 ,P_ABSENCE_REASON => c.p62
847 ,P_DATE_OF_HIRE => c.p63
848 ,P_CALLED_FROM => c.p64
849 ,P_ASSIGNMENT_ID => L_ASSIGNMENT_ID
850 ,P_OBJECT_VERSION_NUMBER => c.p66
851 ,P_EFFECTIVE_START_DATE => c.p67
852 ,P_EFFECTIVE_END_DATE => c.p68
853 ,P_ASSIGNMENT_EXTRA_INFO_ID => c.p69
854 ,P_AEI_OBJECT_VERSION_NUMBER => c.p70);
855 hr_data_pump.api_trc_off;
856 --
857 iuk(p_batch_line_id,c.p65,L_ASSIGNMENT_ID);
858 --
859 update hr_pump_batch_lines l set
860 l.pval065 = decode(c.p65,null,cn,c.p65),
861 l.pval066 = decode(c.p66,null,cn,c.p66),
862 l.pval067 = decode(c.p67,null,cn,dc(c.p67)),
863 l.pval068 = decode(c.p68,null,cn,dc(c.p68)),
864 l.pval069 = decode(c.p69,null,cn,c.p69),
865 l.pval070 = decode(c.p70,null,cn,c.p70)
866 where l.rowid = c.myrowid;
867 --
868 close cr;
869 --
870 hr_data_pump.exit('call');
871 exception
872  when hr_multi_message.error_message_exist then
873    if cr%isopen then
874     close cr;
875    end if;
876    hr_pump_utils.set_multi_msg_error_flag(true);
877  when others then
878  if cr%isopen then
879   close cr;
880  end if;
881  raise;
882 end call;
883 end hrdpp_CREATE_BEN_ASG;