[Home] [Help]
PACKAGE BODY: APPS.HRDPP_UPDATE_MX_EMP_ASG
Source
1 package body hrdpp_UPDATE_MX_EMP_ASG as
2 /*
3 * Generated by hr_pump_meta_mapper at: 2007/01/03 23:01:34
4 * Generated for API: HR_MX_ASSIGNMENT_API.UPDATE_MX_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_GOV_EMP_SECTOR in varchar2 default null
143 ,P_SS_SALARY_TYPE in varchar2 default null
144 ,P_SCL_CONCAT_SEGMENTS in varchar2 default null
145 ,P_CONCAT_SEGMENTS in varchar2 default null
146 ,P_CAG_SEGMENT1 in varchar2 default null
147 ,P_CAG_SEGMENT2 in varchar2 default null
148 ,P_CAG_SEGMENT3 in varchar2 default null
149 ,P_CAG_SEGMENT4 in varchar2 default null
150 ,P_CAG_SEGMENT5 in varchar2 default null
151 ,P_CAG_SEGMENT6 in varchar2 default null
152 ,P_CAG_SEGMENT7 in varchar2 default null
153 ,P_CAG_SEGMENT8 in varchar2 default null
154 ,P_CAG_SEGMENT9 in varchar2 default null
155 ,P_CAG_SEGMENT10 in varchar2 default null
156 ,P_CAG_SEGMENT11 in varchar2 default null
157 ,P_CAG_SEGMENT12 in varchar2 default null
158 ,P_CAG_SEGMENT13 in varchar2 default null
159 ,P_CAG_SEGMENT14 in varchar2 default null
160 ,P_CAG_SEGMENT15 in varchar2 default null
161 ,P_CAG_SEGMENT16 in varchar2 default null
162 ,P_CAG_SEGMENT17 in varchar2 default null
163 ,P_CAG_SEGMENT18 in varchar2 default null
164 ,P_CAG_SEGMENT19 in varchar2 default null
165 ,P_CAG_SEGMENT20 in varchar2 default null
166 ,P_NOTICE_PERIOD in number default null
167 ,I_NOTICE_PERIOD in varchar2 default 'N'
168 ,P_NOTICE_PERIOD_UOM in varchar2 default null
169 ,P_EMPLOYEE_CATEGORY in varchar2 default null
170 ,P_WORK_AT_HOME in varchar2 default null
171 ,P_JOB_POST_SOURCE_NAME in varchar2 default null
172 ,P_SS_LEAVING_REASON in varchar2 default null
173 ,P_CAGR_GRADE_DEF_ID in number
174 ,P_ASSIGNMENT_USER_KEY in varchar2
175 ,P_SUPERVISOR_USER_KEY in varchar2 default null
176 ,P_USER_STATUS in varchar2 default null
177 ,P_LANGUAGE_CODE in varchar2 default null
178 ,P_DEFAULT_CODE_COMB_USER_KEY in varchar2 default null
179 ,P_SET_OF_BOOKS_NAME in varchar2 default null
180 ,P_TAX_UNIT in varchar2 default null
181 ,P_TIMECARD_APPROVER_USER_KEY in varchar2 default null
182 ,P_WORK_SCHEDULE in varchar2 default null
183 ,P_CONTRACT_USER_KEY in varchar2 default null
184 ,P_ESTABLISHMENT_ORG_NAME in varchar2 default null
185 ,P_CAGR_NAME in varchar2 default null
186 ,P_CAGR_ID_FLEX_NUM_USER_KEY in varchar2 default null
187 ,P_SVR_ASSIGNMENT_USER_KEY in varchar2 default null
188 ,P_CON_SEG_USER_NAME in varchar2) is
189 blid number := p_data_pump_batch_line_id;
190 begin
191 if blid is not null then
192 delete from hr_pump_batch_lines where batch_line_id = blid;
193 delete from hr_pump_batch_exceptions
194 where source_type = 'BATCH_LINE' and source_id = blid;
195 end if;
196 insert into hr_pump_batch_lines
197 (batch_id
198 ,batch_line_id
199 ,business_group_name
200 ,api_module_id
201 ,line_status
202 ,user_sequence
203 ,link_value
204 ,pval001
205 ,pval002
206 ,pval003
207 ,pval004
208 ,pval005
209 ,pval006
210 ,pval007
211 ,pval008
212 ,pval009
213 ,pval010
214 ,pval011
215 ,pval012
216 ,pval013
217 ,pval014
218 ,pval015
219 ,pval016
220 ,pval017
221 ,pval018
222 ,pval019
223 ,pval020
224 ,pval021
225 ,pval022
226 ,pval023
227 ,pval024
228 ,pval025
229 ,pval026
230 ,pval027
231 ,pval028
232 ,pval029
233 ,pval030
234 ,pval031
235 ,pval032
236 ,pval033
237 ,pval034
238 ,pval035
239 ,pval036
240 ,pval037
241 ,pval038
242 ,pval039
243 ,pval040
244 ,pval041
245 ,pval042
246 ,pval043
247 ,pval044
248 ,pval045
249 ,pval046
250 ,pval047
251 ,pval048
252 ,pval049
253 ,pval050
254 ,pval051
255 ,pval052
256 ,pval053
257 ,pval054
258 ,pval055
259 ,pval056
260 ,pval057
261 ,pval058
262 ,pval059
263 ,pval060
264 ,pval061
265 ,pval062
266 ,pval063
267 ,pval064
268 ,pval065
269 ,pval066
270 ,pval067
271 ,pval068
272 ,pval069
273 ,pval070
274 ,pval071
275 ,pval072
276 ,pval073
277 ,pval074
278 ,pval075
279 ,pval076
280 ,pval077
281 ,pval078
282 ,pval079
283 ,pval080
284 ,pval081
285 ,pval082
286 ,pval083
287 ,pval084
288 ,pval085
289 ,pval086
290 ,pval096
291 ,pval097
292 ,pval098
293 ,pval099
294 ,pval100
295 ,pval101
296 ,pval102
297 ,pval103
298 ,pval104
299 ,pval105
300 ,pval106
301 ,pval107
302 ,pval108
303 ,pval109
304 ,pval110)
305 values
306 (p_batch_id
307 ,nvl(blid,hr_pump_batch_lines_s.nextval)
308 ,p_data_pump_business_grp_name
309 ,3858
310 ,'U'
311 ,p_user_sequence
312 ,p_link_value
313 ,dc(P_EFFECTIVE_DATE)
314 ,P_DATETRACK_UPDATE_MODE
315 ,P_ASSIGNMENT_NUMBER
316 ,P_CHANGE_REASON
317 ,P_COMMENTS
318 ,dd(P_DATE_PROBATION_END,I_DATE_PROBATION_END)
319 ,P_FREQUENCY
320 ,P_INTERNAL_ADDRESS_LINE
321 ,P_MANAGER_FLAG
322 ,nd(P_NORMAL_HOURS,I_NORMAL_HOURS)
323 ,nd(P_PERF_REVIEW_PERIOD,I_PERF_REVIEW_PERIOD)
324 ,P_PERF_REVIEW_PERIOD_FREQUENCY
325 ,nd(P_PROBATION_PERIOD,I_PROBATION_PERIOD)
326 ,P_PROBATION_UNIT
327 ,nd(P_SAL_REVIEW_PERIOD,I_SAL_REVIEW_PERIOD)
328 ,P_SAL_REVIEW_PERIOD_FREQUENCY
329 ,P_SOURCE_TYPE
330 ,P_TIME_NORMAL_FINISH
331 ,P_TIME_NORMAL_START
332 ,P_BARGAINING_UNIT_CODE
333 ,P_LABOUR_UNION_MEMBER_FLAG
334 ,P_HOURLY_SALARIED_CODE
335 ,P_ASS_ATTRIBUTE_CATEGORY
336 ,P_ASS_ATTRIBUTE1
337 ,P_ASS_ATTRIBUTE2
338 ,P_ASS_ATTRIBUTE3
339 ,P_ASS_ATTRIBUTE4
340 ,P_ASS_ATTRIBUTE5
341 ,P_ASS_ATTRIBUTE6
342 ,P_ASS_ATTRIBUTE7
343 ,P_ASS_ATTRIBUTE8
344 ,P_ASS_ATTRIBUTE9
345 ,P_ASS_ATTRIBUTE10
346 ,P_ASS_ATTRIBUTE11
347 ,P_ASS_ATTRIBUTE12
348 ,P_ASS_ATTRIBUTE13
349 ,P_ASS_ATTRIBUTE14
350 ,P_ASS_ATTRIBUTE15
351 ,P_ASS_ATTRIBUTE16
352 ,P_ASS_ATTRIBUTE17
353 ,P_ASS_ATTRIBUTE18
354 ,P_ASS_ATTRIBUTE19
355 ,P_ASS_ATTRIBUTE20
356 ,P_ASS_ATTRIBUTE21
357 ,P_ASS_ATTRIBUTE22
358 ,P_ASS_ATTRIBUTE23
359 ,P_ASS_ATTRIBUTE24
360 ,P_ASS_ATTRIBUTE25
361 ,P_ASS_ATTRIBUTE26
362 ,P_ASS_ATTRIBUTE27
363 ,P_ASS_ATTRIBUTE28
364 ,P_ASS_ATTRIBUTE29
365 ,P_ASS_ATTRIBUTE30
366 ,P_TITLE
367 ,P_TIMECARD_REQUIRED
368 ,P_GOV_EMP_SECTOR
369 ,P_SS_SALARY_TYPE
370 ,P_SCL_CONCAT_SEGMENTS
371 ,P_CONCAT_SEGMENTS
372 ,P_CAG_SEGMENT1
373 ,P_CAG_SEGMENT2
374 ,P_CAG_SEGMENT3
375 ,P_CAG_SEGMENT4
376 ,P_CAG_SEGMENT5
377 ,P_CAG_SEGMENT6
378 ,P_CAG_SEGMENT7
379 ,P_CAG_SEGMENT8
380 ,P_CAG_SEGMENT9
381 ,P_CAG_SEGMENT10
382 ,P_CAG_SEGMENT11
383 ,P_CAG_SEGMENT12
384 ,P_CAG_SEGMENT13
385 ,P_CAG_SEGMENT14
386 ,P_CAG_SEGMENT15
387 ,P_CAG_SEGMENT16
388 ,P_CAG_SEGMENT17
389 ,P_CAG_SEGMENT18
390 ,P_CAG_SEGMENT19
391 ,P_CAG_SEGMENT20
392 ,nd(P_NOTICE_PERIOD,I_NOTICE_PERIOD)
393 ,P_NOTICE_PERIOD_UOM
394 ,P_EMPLOYEE_CATEGORY
395 ,P_WORK_AT_HOME
396 ,P_JOB_POST_SOURCE_NAME
397 ,P_SS_LEAVING_REASON
398 ,P_CAGR_GRADE_DEF_ID
399 ,P_ASSIGNMENT_USER_KEY
400 ,P_SUPERVISOR_USER_KEY
401 ,P_USER_STATUS
402 ,P_LANGUAGE_CODE
403 ,P_DEFAULT_CODE_COMB_USER_KEY
404 ,P_SET_OF_BOOKS_NAME
405 ,P_TAX_UNIT
406 ,P_TIMECARD_APPROVER_USER_KEY
410 ,P_CAGR_NAME
407 ,P_WORK_SCHEDULE
408 ,P_CONTRACT_USER_KEY
409 ,P_ESTABLISHMENT_ORG_NAME
411 ,P_CAGR_ID_FLEX_NUM_USER_KEY
412 ,P_SVR_ASSIGNMENT_USER_KEY
413 ,P_CON_SEG_USER_NAME);
414 end insert_batch_lines;
415 --
416 procedure call
417 (p_business_group_id in number,
418 p_batch_line_id in number) is
419 cursor cr is
420 select l.rowid myrowid,
421 decode(l.pval001,cn,dn,d(l.pval001)) p1,
422 decode(l.pval002,cn,vn,l.pval002) p2,
423 decode(l.pval003,cn,vn,vn,vh,l.pval003) p3,
424 l.pval003 d3,
425 decode(l.pval004,cn,vn,vn,vh,
426 hr_pump_get.gl(l.pval004,'EMP_ASSIGN_REASON',d(l.pval001),l.pval099)) p4,
427 l.pval004 d4,
428 decode(l.pval005,cn,vn,vn,vh,l.pval005) p5,
429 l.pval005 d5,
430 decode(l.pval006,cn,dn,vn,dh,d(l.pval006)) p6,
431 l.pval006 d6,
432 decode(l.pval007,cn,vn,vn,vh,
433 hr_pump_get.gl(l.pval007,'FREQUENCY',d(l.pval001),l.pval099)) p7,
434 l.pval007 d7,
435 decode(l.pval008,cn,vn,vn,vh,l.pval008) p8,
436 l.pval008 d8,
437 decode(l.pval009,cn,vn,vn,vh,
438 hr_pump_get.gl(l.pval009,'YES_NO',d(l.pval001),l.pval099)) p9,
439 l.pval009 d9,
440 decode(l.pval010,cn,nn,vn,nh,n(l.pval010)) p10,
441 l.pval010 d10,
442 decode(l.pval011,cn,nn,vn,nh,n(l.pval011)) p11,
443 l.pval011 d11,
444 decode(l.pval012,cn,vn,vn,vh,
445 hr_pump_get.gl(l.pval012,'FREQUENCY',d(l.pval001),l.pval099)) p12,
446 l.pval012 d12,
447 decode(l.pval013,cn,nn,vn,nh,n(l.pval013)) p13,
448 l.pval013 d13,
449 decode(l.pval014,cn,vn,vn,vh,
450 hr_pump_get.gl(l.pval014,'QUALIFYING_UNITS',d(l.pval001),l.pval099)) p14,
451 l.pval014 d14,
452 decode(l.pval015,cn,nn,vn,nh,n(l.pval015)) p15,
453 l.pval015 d15,
454 decode(l.pval016,cn,vn,vn,vh,
455 hr_pump_get.gl(l.pval016,'FREQUENCY',d(l.pval001),l.pval099)) p16,
456 l.pval016 d16,
457 decode(l.pval017,cn,vn,vn,vh,
458 hr_pump_get.gl(l.pval017,'REC_TYPE',d(l.pval001),l.pval099)) p17,
459 l.pval017 d17,
460 decode(l.pval018,cn,vn,vn,vh,l.pval018) p18,
461 l.pval018 d18,
462 decode(l.pval019,cn,vn,vn,vh,l.pval019) p19,
463 l.pval019 d19,
464 decode(l.pval020,cn,vn,vn,vh,
465 hr_pump_get.gl(l.pval020,'BARGAINING_UNIT_CODE',d(l.pval001),l.pval099)) p20,
466 l.pval020 d20,
467 decode(l.pval021,cn,vn,vn,vh,
468 hr_pump_get.gl(l.pval021,'YES_NO',d(l.pval001),l.pval099)) p21,
469 l.pval021 d21,
470 decode(l.pval022,cn,vn,vn,vh,
471 hr_pump_get.gl(l.pval022,'HOURLY_SALARIED_CODE',d(l.pval001),l.pval099)) p22,
472 l.pval022 d22,
473 decode(l.pval023,cn,vn,vn,vh,l.pval023) p23,
474 l.pval023 d23,
475 decode(l.pval024,cn,vn,vn,vh,l.pval024) p24,
476 l.pval024 d24,
477 decode(l.pval025,cn,vn,vn,vh,l.pval025) p25,
478 l.pval025 d25,
479 decode(l.pval026,cn,vn,vn,vh,l.pval026) p26,
480 l.pval026 d26,
481 decode(l.pval027,cn,vn,vn,vh,l.pval027) p27,
482 l.pval027 d27,
483 decode(l.pval028,cn,vn,vn,vh,l.pval028) p28,
484 l.pval028 d28,
485 decode(l.pval029,cn,vn,vn,vh,l.pval029) p29,
486 l.pval029 d29,
487 decode(l.pval030,cn,vn,vn,vh,l.pval030) p30,
488 l.pval030 d30,
489 decode(l.pval031,cn,vn,vn,vh,l.pval031) p31,
490 l.pval031 d31,
491 decode(l.pval032,cn,vn,vn,vh,l.pval032) p32,
492 l.pval032 d32,
493 decode(l.pval033,cn,vn,vn,vh,l.pval033) p33,
494 l.pval033 d33,
495 decode(l.pval034,cn,vn,vn,vh,l.pval034) p34,
496 l.pval034 d34,
497 decode(l.pval035,cn,vn,vn,vh,l.pval035) p35,
498 l.pval035 d35,
499 decode(l.pval036,cn,vn,vn,vh,l.pval036) p36,
500 l.pval036 d36,
501 decode(l.pval037,cn,vn,vn,vh,l.pval037) p37,
502 l.pval037 d37,
503 decode(l.pval038,cn,vn,vn,vh,l.pval038) p38,
504 l.pval038 d38,
505 decode(l.pval039,cn,vn,vn,vh,l.pval039) p39,
506 l.pval039 d39,
507 decode(l.pval040,cn,vn,vn,vh,l.pval040) p40,
508 l.pval040 d40,
509 decode(l.pval041,cn,vn,vn,vh,l.pval041) p41,
510 l.pval041 d41,
511 decode(l.pval042,cn,vn,vn,vh,l.pval042) p42,
512 l.pval042 d42,
513 decode(l.pval043,cn,vn,vn,vh,l.pval043) p43,
514 l.pval043 d43,
515 decode(l.pval044,cn,vn,vn,vh,l.pval044) p44,
516 l.pval044 d44,
517 decode(l.pval045,cn,vn,vn,vh,l.pval045) p45,
518 l.pval045 d45,
519 decode(l.pval046,cn,vn,vn,vh,l.pval046) p46,
520 l.pval046 d46,
521 decode(l.pval047,cn,vn,vn,vh,l.pval047) p47,
522 l.pval047 d47,
523 decode(l.pval048,cn,vn,vn,vh,l.pval048) p48,
524 l.pval048 d48,
525 decode(l.pval049,cn,vn,vn,vh,l.pval049) p49,
526 l.pval049 d49,
527 decode(l.pval050,cn,vn,vn,vh,l.pval050) p50,
528 l.pval050 d50,
529 decode(l.pval051,cn,vn,vn,vh,l.pval051) p51,
530 l.pval051 d51,
531 decode(l.pval052,cn,vn,vn,vh,l.pval052) p52,
532 l.pval052 d52,
533 decode(l.pval053,cn,vn,vn,vh,l.pval053) p53,
534 l.pval053 d53,
535 decode(l.pval054,cn,vn,vn,vh,l.pval054) p54,
536 l.pval054 d54,
537 decode(l.pval055,cn,vn,vn,vh,
538 hr_pump_get.gl(l.pval055,'YES_NO',d(l.pval001),l.pval099)) p55,
539 l.pval055 d55,
540 decode(l.pval056,cn,vn,vn,vh,
541 hr_pump_get.gl(l.pval056,'HR_MX_EMP_CAT',d(l.pval001),l.pval099)) p56,
542 l.pval056 d56,
543 decode(l.pval057,cn,vn,vn,vh,
544 hr_pump_get.gl(l.pval057,'HR_MX_SS_SALARY_TYPE',d(l.pval001),l.pval099)) p57,
545 l.pval057 d57,
546 decode(l.pval058,cn,vn,vn,vh,l.pval058) p58,
547 l.pval058 d58,
548 decode(l.pval059,cn,vn,vn,vh,l.pval059) p59,
549 l.pval059 d59,
550 decode(l.pval060,cn,vn,vn,vh,l.pval060) p60,
551 l.pval060 d60,
552 decode(l.pval061,cn,vn,vn,vh,l.pval061) p61,
553 l.pval061 d61,
554 decode(l.pval062,cn,vn,vn,vh,l.pval062) p62,
555 l.pval062 d62,
556 decode(l.pval063,cn,vn,vn,vh,l.pval063) p63,
557 l.pval063 d63,
558 decode(l.pval064,cn,vn,vn,vh,l.pval064) p64,
559 l.pval064 d64,
560 decode(l.pval065,cn,vn,vn,vh,l.pval065) p65,
561 l.pval065 d65,
562 decode(l.pval066,cn,vn,vn,vh,l.pval066) p66,
566 decode(l.pval068,cn,vn,vn,vh,l.pval068) p68,
563 l.pval066 d66,
564 decode(l.pval067,cn,vn,vn,vh,l.pval067) p67,
565 l.pval067 d67,
567 l.pval068 d68,
568 decode(l.pval069,cn,vn,vn,vh,l.pval069) p69,
569 l.pval069 d69,
570 decode(l.pval070,cn,vn,vn,vh,l.pval070) p70,
571 l.pval070 d70,
572 decode(l.pval071,cn,vn,vn,vh,l.pval071) p71,
573 l.pval071 d71,
574 decode(l.pval072,cn,vn,vn,vh,l.pval072) p72,
575 l.pval072 d72,
576 decode(l.pval073,cn,vn,vn,vh,l.pval073) p73,
577 l.pval073 d73,
578 decode(l.pval074,cn,vn,vn,vh,l.pval074) p74,
579 l.pval074 d74,
580 decode(l.pval075,cn,vn,vn,vh,l.pval075) p75,
581 l.pval075 d75,
582 decode(l.pval076,cn,vn,vn,vh,l.pval076) p76,
583 l.pval076 d76,
584 decode(l.pval077,cn,vn,vn,vh,l.pval077) p77,
585 l.pval077 d77,
586 decode(l.pval078,cn,vn,vn,vh,l.pval078) p78,
587 l.pval078 d78,
588 decode(l.pval079,cn,vn,vn,vh,l.pval079) p79,
589 l.pval079 d79,
590 decode(l.pval080,cn,nn,vn,nh,n(l.pval080)) p80,
591 l.pval080 d80,
592 decode(l.pval081,cn,vn,vn,vh,l.pval081) p81,
593 l.pval081 d81,
594 decode(l.pval082,cn,vn,vn,vh,l.pval082) p82,
595 l.pval082 d82,
596 decode(l.pval083,cn,vn,vn,vh,l.pval083) p83,
597 l.pval083 d83,
598 decode(l.pval084,cn,vn,vn,vh,l.pval084) p84,
599 l.pval084 d84,
600 decode(l.pval085,cn,vn,vn,vh,
601 hr_pump_get.gl(l.pval085,'MX_STAT_IMSS_LEAVING_REASON',d(l.pval001),l.pval099)) p85,
602 l.pval085 d85,
603 decode(l.pval086,cn,nn,n(l.pval086)) p86,
604 l.pval087 p87,
605 l.pval088 p88,
606 l.pval089 p89,
607 decode(l.pval090,cn,dn,d(l.pval090)) p90,
608 decode(l.pval091,cn,dn,d(l.pval091)) p91,
609 l.pval092 p92,
610 l.pval093 p93,
611 l.pval094 p94,
612 l.pval095 p95,
613 decode(l.pval096,cn,vn,l.pval096) p96,
614 decode(l.pval097,cn,vn,vn,vn,l.pval097) p97,
615 l.pval097 d97,
616 decode(l.pval098,cn,vn,vn,vh,l.pval098) p98,
617 l.pval098 d98,
618 decode(l.pval099,cn,vn,vn,vh,l.pval099) p99,
619 l.pval099 d99,
620 decode(l.pval100,cn,vn,vn,vn,l.pval100) p100,
621 l.pval100 d100,
622 decode(l.pval101,cn,vn,vn,vh,l.pval101) p101,
623 l.pval101 d101,
624 decode(l.pval102,cn,vn,vn,vh,l.pval102) p102,
625 l.pval102 d102,
626 decode(l.pval103,cn,vn,vn,vn,l.pval103) p103,
627 l.pval103 d103,
628 decode(l.pval104,cn,vn,vn,vh,l.pval104) p104,
629 l.pval104 d104,
630 decode(l.pval105,cn,vn,vn,vn,l.pval105) p105,
631 l.pval105 d105,
632 decode(l.pval106,cn,vn,vn,vh,l.pval106) p106,
633 l.pval106 d106,
634 decode(l.pval107,cn,vn,vn,vh,l.pval107) p107,
635 l.pval107 d107,
636 decode(l.pval108,cn,vn,vn,vn,l.pval108) p108,
637 l.pval108 d108,
638 decode(l.pval109,cn,vn,vn,vn,l.pval109) p109,
639 l.pval109 d109,
640 decode(l.pval110,cn,vn,l.pval110) p110
641 from hr_pump_batch_lines l
642 where l.batch_line_id = p_batch_line_id;
643 --
644 c cr%rowtype;
645 l_validate boolean := false;
646 L_NO_MANAGERS_WARNING boolean;
647 L_OTHER_MANAGER_WARNING boolean;
648 L_HOURLY_SALARIED_WARNING boolean;
649 L_ASSIGNMENT_ID number;
650 L_OBJECT_VERSION_NUMBER number;
651 L_SUPERVISOR_ID number;
652 L_ASSIGNMENT_STATUS_TYPE_ID number;
653 L_DEFAULT_CODE_COMB_ID number;
654 L_SET_OF_BOOKS_ID number;
655 L_TAX_UNIT varchar2(2000);
656 L_TIMECARD_APPROVER varchar2(2000);
657 L_WORK_SCHEDULE varchar2(2000);
658 L_CONTRACT_ID number;
659 L_ESTABLISHMENT_ID number;
660 L_COLLECTIVE_AGREEMENT_ID number;
661 L_CAGR_ID_FLEX_NUM number;
662 L_SUPERVISOR_ASSIGNMENT_ID number;
663 L_SOFT_CODING_KEYFLEX_ID number;
664 --
665 begin
666 hr_data_pump.entry('call');
667 open cr;
668 fetch cr into c;
669 if cr%notfound then
670 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
671 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
672 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
673 hr_utility.set_message_token('VALUE',p_batch_line_id);
674 hr_utility.raise_error;
675 end if;
676 --
677 if c.p96 is null then
678 L_ASSIGNMENT_ID:=nn;
679 else
680 L_ASSIGNMENT_ID :=
681 hr_pump_get.get_assignment_id
682 (P_ASSIGNMENT_USER_KEY => c.p96);
683 end if;
684 --
685 if c.p96 is null or
686 c.p1 is null then
687 L_OBJECT_VERSION_NUMBER:=nn;
688 else
689 L_OBJECT_VERSION_NUMBER :=
690 hr_pump_get.GET_ASG_OVN
691 (P_ASSIGNMENT_USER_KEY => c.p96
692 ,P_EFFECTIVE_DATE => c.p1);
693 end if;
694 --
695 if c.d97=cn then
696 L_SUPERVISOR_ID:=nn;
697 elsif c.d97 is null then
698 L_SUPERVISOR_ID:=nh;
699 else
700 L_SUPERVISOR_ID :=
701 hr_pump_get.get_supervisor_id
702 (P_SUPERVISOR_USER_KEY => c.p97);
703 end if;
704 --
705 if c.d98=cn or
706 c.d99=cn then
707 L_ASSIGNMENT_STATUS_TYPE_ID:=nn;
708 elsif c.d98 is null or
709 c.d99 is null then
710 L_ASSIGNMENT_STATUS_TYPE_ID:=nh;
711 else
712 L_ASSIGNMENT_STATUS_TYPE_ID :=
713 hr_pump_get.get_assignment_status_type_id
714 (P_USER_STATUS => c.p98
715 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
716 ,P_LANGUAGE_CODE => c.p99);
717 end if;
718 --
719 if c.d100=cn then
720 L_DEFAULT_CODE_COMB_ID:=nn;
721 elsif c.d100 is null then
722 L_DEFAULT_CODE_COMB_ID:=nh;
723 else
724 L_DEFAULT_CODE_COMB_ID :=
725 hr_pump_get.get_default_code_comb_id
726 (P_DEFAULT_CODE_COMB_USER_KEY => c.p100);
727 end if;
728 --
729 if c.d101=cn then
730 L_SET_OF_BOOKS_ID:=nn;
731 elsif c.d101 is null then
732 L_SET_OF_BOOKS_ID:=nh;
733 else
734 L_SET_OF_BOOKS_ID :=
735 hr_pump_get.get_set_of_books_id
736 (P_SET_OF_BOOKS_NAME => c.p101);
737 end if;
738 --
739 if c.d102=cn then
743 else
740 L_TAX_UNIT:=vn;
741 elsif c.d102 is null then
742 L_TAX_UNIT:=vh;
744 L_TAX_UNIT :=
745 PER_MX_DATA_PUMP.GET_TAX_UNIT_ID
746 (P_TAX_UNIT => c.p102
747 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID);
748 end if;
749 --
750 if c.d103=cn then
751 L_TIMECARD_APPROVER:=vn;
752 elsif c.d103 is null then
753 L_TIMECARD_APPROVER:=vh;
754 else
755 L_TIMECARD_APPROVER :=
756 hr_pump_get.GET_TIMECARD_APPROVER_ID
757 (P_TIMECARD_APPROVER_USER_KEY => c.p103);
758 end if;
759 --
760 if c.d104=cn then
761 L_WORK_SCHEDULE:=vn;
762 elsif c.d104 is null then
763 L_WORK_SCHEDULE:=vh;
764 else
765 L_WORK_SCHEDULE :=
766 PER_MX_DATA_PUMP.GET_WORK_SCHEDULE
767 (P_WORK_SCHEDULE => c.p104);
768 end if;
769 --
770 if c.d105=cn then
771 L_CONTRACT_ID:=nn;
772 elsif c.d105 is null then
773 L_CONTRACT_ID:=nh;
774 else
775 L_CONTRACT_ID :=
776 hr_pump_get.get_contract_id
777 (P_CONTRACT_USER_KEY => c.p105);
778 end if;
779 --
780 if c.d106=cn or
781 c.p1 is null or
782 c.d99=cn then
783 L_ESTABLISHMENT_ID:=nn;
784 elsif c.d106 is null or
785 c.d99 is null then
786 L_ESTABLISHMENT_ID:=nh;
787 else
788 L_ESTABLISHMENT_ID :=
789 hr_pump_get.GET_ESTABLISHMENT_ORG_ID
790 (P_ESTABLISHMENT_ORG_NAME => c.p106
791 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
792 ,P_EFFECTIVE_DATE => c.p1
793 ,P_LANGUAGE_CODE => c.p99);
794 end if;
795 --
796 if c.d107=cn or
797 c.p1 is null then
798 L_COLLECTIVE_AGREEMENT_ID:=nn;
799 elsif c.d107 is null then
800 L_COLLECTIVE_AGREEMENT_ID:=nh;
801 else
802 L_COLLECTIVE_AGREEMENT_ID :=
803 hr_pump_get.get_collective_agreement_id
804 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
805 ,P_CAGR_NAME => c.p107
806 ,P_EFFECTIVE_DATE => c.p1);
807 end if;
808 --
809 if c.d108=cn then
810 L_CAGR_ID_FLEX_NUM:=nn;
811 elsif c.d108 is null then
812 L_CAGR_ID_FLEX_NUM:=nh;
813 else
814 L_CAGR_ID_FLEX_NUM :=
815 hr_pump_get.GET_CAGR_ID_FLEX_NUM
816 (P_CAGR_ID_FLEX_NUM_USER_KEY => c.p108);
817 end if;
818 --
819 if c.d109=cn then
820 L_SUPERVISOR_ASSIGNMENT_ID:=nn;
821 elsif c.d109 is null then
822 L_SUPERVISOR_ASSIGNMENT_ID:=nh;
823 else
824 L_SUPERVISOR_ASSIGNMENT_ID :=
825 hr_pump_get.get_supervisor_assignment_id
826 (P_SVR_ASSIGNMENT_USER_KEY => c.p109);
827 end if;
828 --
829 if c.p110 is null or
830 c.p1 is null then
831 L_SOFT_CODING_KEYFLEX_ID:=nn;
832 else
833 L_SOFT_CODING_KEYFLEX_ID :=
834 hr_pump_get.get_soft_coding_keyflex_id
835 (P_CON_SEG_USER_NAME => c.p110
836 ,P_EFFECTIVE_DATE => c.p1);
837 end if;
838 --
839 hr_data_pump.api_trc_on;
840 HR_MX_ASSIGNMENT_API.UPDATE_MX_EMP_ASG
841 (p_validate => l_validate
842 ,P_EFFECTIVE_DATE => c.p1
843 ,P_DATETRACK_UPDATE_MODE => c.p2
844 ,P_ASSIGNMENT_ID => L_ASSIGNMENT_ID
845 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER
846 ,P_SUPERVISOR_ID => L_SUPERVISOR_ID
847 ,P_ASSIGNMENT_NUMBER => c.p3
848 ,P_CHANGE_REASON => c.p4
849 ,P_ASSIGNMENT_STATUS_TYPE_ID => L_ASSIGNMENT_STATUS_TYPE_ID
850 ,P_COMMENTS => c.p5
851 ,P_DATE_PROBATION_END => c.p6
852 ,P_DEFAULT_CODE_COMB_ID => L_DEFAULT_CODE_COMB_ID
853 ,P_FREQUENCY => c.p7
854 ,P_INTERNAL_ADDRESS_LINE => c.p8
855 ,P_MANAGER_FLAG => c.p9
856 ,P_NORMAL_HOURS => c.p10
857 ,P_PERF_REVIEW_PERIOD => c.p11
858 ,P_PERF_REVIEW_PERIOD_FREQUENCY => c.p12
859 ,P_PROBATION_PERIOD => c.p13
860 ,P_PROBATION_UNIT => c.p14
861 ,P_SAL_REVIEW_PERIOD => c.p15
862 ,P_SAL_REVIEW_PERIOD_FREQUENCY => c.p16
863 ,P_SET_OF_BOOKS_ID => L_SET_OF_BOOKS_ID
864 ,P_SOURCE_TYPE => c.p17
865 ,P_TIME_NORMAL_FINISH => c.p18
866 ,P_TIME_NORMAL_START => c.p19
867 ,P_BARGAINING_UNIT_CODE => c.p20
868 ,P_LABOUR_UNION_MEMBER_FLAG => c.p21
869 ,P_HOURLY_SALARIED_CODE => c.p22
870 ,P_ASS_ATTRIBUTE_CATEGORY => c.p23
871 ,P_ASS_ATTRIBUTE1 => c.p24
872 ,P_ASS_ATTRIBUTE2 => c.p25
873 ,P_ASS_ATTRIBUTE3 => c.p26
874 ,P_ASS_ATTRIBUTE4 => c.p27
875 ,P_ASS_ATTRIBUTE5 => c.p28
876 ,P_ASS_ATTRIBUTE6 => c.p29
877 ,P_ASS_ATTRIBUTE7 => c.p30
878 ,P_ASS_ATTRIBUTE8 => c.p31
879 ,P_ASS_ATTRIBUTE9 => c.p32
880 ,P_ASS_ATTRIBUTE10 => c.p33
881 ,P_ASS_ATTRIBUTE11 => c.p34
882 ,P_ASS_ATTRIBUTE12 => c.p35
883 ,P_ASS_ATTRIBUTE13 => c.p36
884 ,P_ASS_ATTRIBUTE14 => c.p37
885 ,P_ASS_ATTRIBUTE15 => c.p38
886 ,P_ASS_ATTRIBUTE16 => c.p39
887 ,P_ASS_ATTRIBUTE17 => c.p40
888 ,P_ASS_ATTRIBUTE18 => c.p41
889 ,P_ASS_ATTRIBUTE19 => c.p42
890 ,P_ASS_ATTRIBUTE20 => c.p43
891 ,P_ASS_ATTRIBUTE21 => c.p44
892 ,P_ASS_ATTRIBUTE22 => c.p45
893 ,P_ASS_ATTRIBUTE23 => c.p46
894 ,P_ASS_ATTRIBUTE24 => c.p47
895 ,P_ASS_ATTRIBUTE25 => c.p48
896 ,P_ASS_ATTRIBUTE26 => c.p49
897 ,P_ASS_ATTRIBUTE27 => c.p50
898 ,P_ASS_ATTRIBUTE28 => c.p51
899 ,P_ASS_ATTRIBUTE29 => c.p52
900 ,P_ASS_ATTRIBUTE30 => c.p53
901 ,P_TITLE => c.p54
902 ,P_TAX_UNIT => L_TAX_UNIT
903 ,P_TIMECARD_APPROVER => L_TIMECARD_APPROVER
904 ,P_TIMECARD_REQUIRED => c.p55
905 ,P_WORK_SCHEDULE => L_WORK_SCHEDULE
906 ,P_GOV_EMP_SECTOR => c.p56
907 ,P_SS_SALARY_TYPE => c.p57
908 ,P_SCL_CONCAT_SEGMENTS => c.p58
909 ,P_CONCAT_SEGMENTS => c.p59
910 ,P_CONTRACT_ID => L_CONTRACT_ID
911 ,P_ESTABLISHMENT_ID => L_ESTABLISHMENT_ID
912 ,P_COLLECTIVE_AGREEMENT_ID => L_COLLECTIVE_AGREEMENT_ID
913 ,P_CAGR_ID_FLEX_NUM => L_CAGR_ID_FLEX_NUM
914 ,P_CAG_SEGMENT1 => c.p60
915 ,P_CAG_SEGMENT2 => c.p61
916 ,P_CAG_SEGMENT3 => c.p62
917 ,P_CAG_SEGMENT4 => c.p63
918 ,P_CAG_SEGMENT5 => c.p64
919 ,P_CAG_SEGMENT6 => c.p65
920 ,P_CAG_SEGMENT7 => c.p66
924 ,P_CAG_SEGMENT11 => c.p70
921 ,P_CAG_SEGMENT8 => c.p67
922 ,P_CAG_SEGMENT9 => c.p68
923 ,P_CAG_SEGMENT10 => c.p69
925 ,P_CAG_SEGMENT12 => c.p71
926 ,P_CAG_SEGMENT13 => c.p72
927 ,P_CAG_SEGMENT14 => c.p73
928 ,P_CAG_SEGMENT15 => c.p74
929 ,P_CAG_SEGMENT16 => c.p75
930 ,P_CAG_SEGMENT17 => c.p76
931 ,P_CAG_SEGMENT18 => c.p77
932 ,P_CAG_SEGMENT19 => c.p78
933 ,P_CAG_SEGMENT20 => c.p79
934 ,P_NOTICE_PERIOD => c.p80
935 ,P_NOTICE_PERIOD_UOM => c.p81
936 ,P_EMPLOYEE_CATEGORY => c.p82
937 ,P_WORK_AT_HOME => c.p83
938 ,P_JOB_POST_SOURCE_NAME => c.p84
939 ,P_SUPERVISOR_ASSIGNMENT_ID => L_SUPERVISOR_ASSIGNMENT_ID
940 ,P_SS_LEAVING_REASON => c.p85
941 ,P_CAGR_GRADE_DEF_ID => c.p86
942 ,P_CAGR_CONCATENATED_SEGMENTS => c.p87
943 ,P_CONCATENATED_SEGMENTS => c.p88
944 ,P_SOFT_CODING_KEYFLEX_ID => L_SOFT_CODING_KEYFLEX_ID
945 ,P_COMMENT_ID => c.p89
946 ,P_EFFECTIVE_START_DATE => c.p90
947 ,P_EFFECTIVE_END_DATE => c.p91
948 ,P_NO_MANAGERS_WARNING => L_NO_MANAGERS_WARNING
949 ,P_OTHER_MANAGER_WARNING => L_OTHER_MANAGER_WARNING
950 ,P_HOURLY_SALARIED_WARNING => L_HOURLY_SALARIED_WARNING
951 ,P_GSP_POST_PROCESS_WARNING => c.p95);
952 hr_data_pump.api_trc_off;
953 --
954 if L_NO_MANAGERS_WARNING then
955 c.p92 := 'TRUE';
956 else
957 c.p92 := 'FALSE';
958 end if;
959 --
960 if L_OTHER_MANAGER_WARNING then
961 c.p93 := 'TRUE';
962 else
963 c.p93 := 'FALSE';
964 end if;
965 --
966 if L_HOURLY_SALARIED_WARNING then
967 c.p94 := 'TRUE';
968 else
969 c.p94 := 'FALSE';
970 end if;
971 --
972 update hr_pump_batch_lines l set
973 l.pval086 = decode(c.p86,null,cn,c.p86),
974 l.pval087 = decode(c.p87,null,cn,c.p87),
975 l.pval088 = decode(c.p88,null,cn,c.p88),
976 l.pval089 = decode(c.p89,null,cn,c.p89),
977 l.pval090 = decode(c.p90,null,cn,dc(c.p90)),
978 l.pval091 = decode(c.p91,null,cn,dc(c.p91)),
979 l.pval092 = decode(c.p92,null,cn,c.p92),
980 l.pval093 = decode(c.p93,null,cn,c.p93),
981 l.pval094 = decode(c.p94,null,cn,c.p94),
982 l.pval095 = decode(c.p95,null,cn,c.p95)
983 where l.rowid = c.myrowid;
984 --
985 close cr;
986 --
987 hr_data_pump.exit('call');
988 exception
989 when hr_multi_message.error_message_exist then
990 if cr%isopen then
991 close cr;
992 end if;
993 hr_pump_utils.set_multi_msg_error_flag(true);
994 when others then
995 if cr%isopen then
996 close cr;
997 end if;
998 raise;
999 end call;
1000 end hrdpp_UPDATE_MX_EMP_ASG;