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