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