[Home] [Help]
PACKAGE BODY: APPS.HRDPP_CREATE_IN_SECONDARY_CWK_
Source
1 package body hrdpp_create_in_secondary_cwk_ as
2 /*
3 * Generated by hr_pump_meta_mapper at: 2007/01/03 23:01:50
4 * Generated for API: hr_in_assignment_api.create_in_secondary_cwk_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_ASSIGNMENT_NUMBER in varchar2
84 ,P_ASSIGNMENT_CATEGORY in varchar2 default null
85 ,P_CHANGE_REASON in varchar2 default null
86 ,P_COMMENTS in varchar2 default null
87 ,P_FREQUENCY in varchar2 default null
88 ,P_INTERNAL_ADDRESS_LINE in varchar2 default null
89 ,P_LABOUR_UNION_MEMBER_FLAG in varchar2 default null
90 ,P_MANAGER_FLAG in varchar2 default null
91 ,P_NORMAL_HOURS in number default null
92 ,P_PROJECT_TITLE in varchar2 default null
93 ,P_SOURCE_TYPE in varchar2 default null
94 ,P_TIME_NORMAL_FINISH in varchar2 default null
95 ,P_TIME_NORMAL_START in varchar2 default null
96 ,P_TITLE in varchar2 default null
97 ,P_VENDOR_ASSIGNMENT_NUMBER in varchar2 default null
98 ,P_VENDOR_EMPLOYEE_NUMBER in varchar2 default null
99 ,P_PROJECTED_ASSIGNMENT_END in date default null
100 ,P_ATTRIBUTE_CATEGORY in varchar2 default null
101 ,P_ATTRIBUTE1 in varchar2 default null
102 ,P_ATTRIBUTE2 in varchar2 default null
103 ,P_ATTRIBUTE3 in varchar2 default null
104 ,P_ATTRIBUTE4 in varchar2 default null
105 ,P_ATTRIBUTE5 in varchar2 default null
106 ,P_ATTRIBUTE6 in varchar2 default null
107 ,P_ATTRIBUTE7 in varchar2 default null
108 ,P_ATTRIBUTE8 in varchar2 default null
109 ,P_ATTRIBUTE9 in varchar2 default null
110 ,P_ATTRIBUTE10 in varchar2 default null
111 ,P_ATTRIBUTE11 in varchar2 default null
112 ,P_ATTRIBUTE12 in varchar2 default null
113 ,P_ATTRIBUTE13 in varchar2 default null
114 ,P_ATTRIBUTE14 in varchar2 default null
115 ,P_ATTRIBUTE15 in varchar2 default null
116 ,P_ATTRIBUTE16 in varchar2 default null
117 ,P_ATTRIBUTE17 in varchar2 default null
118 ,P_ATTRIBUTE18 in varchar2 default null
119 ,P_ATTRIBUTE19 in varchar2 default null
120 ,P_ATTRIBUTE20 in varchar2 default null
121 ,P_ATTRIBUTE21 in varchar2 default null
122 ,P_ATTRIBUTE22 in varchar2 default null
123 ,P_ATTRIBUTE23 in varchar2 default null
124 ,P_ATTRIBUTE24 in varchar2 default null
125 ,P_ATTRIBUTE25 in varchar2 default null
126 ,P_ATTRIBUTE26 in varchar2 default null
127 ,P_ATTRIBUTE27 in varchar2 default null
128 ,P_ATTRIBUTE28 in varchar2 default null
129 ,P_ATTRIBUTE29 in varchar2 default null
130 ,P_ATTRIBUTE30 in varchar2 default null
131 ,P_PGP_SEGMENT1 in varchar2 default null
132 ,P_PGP_SEGMENT2 in varchar2 default null
133 ,P_PGP_SEGMENT3 in varchar2 default null
134 ,P_PGP_SEGMENT4 in varchar2 default null
135 ,P_PGP_SEGMENT5 in varchar2 default null
136 ,P_PGP_SEGMENT6 in varchar2 default null
137 ,P_PGP_SEGMENT7 in varchar2 default null
138 ,P_PGP_SEGMENT8 in varchar2 default null
139 ,P_PGP_SEGMENT9 in varchar2 default null
140 ,P_PGP_SEGMENT10 in varchar2 default null
141 ,P_PGP_SEGMENT11 in varchar2 default null
142 ,P_PGP_SEGMENT12 in varchar2 default null
143 ,P_PGP_SEGMENT13 in varchar2 default null
144 ,P_PGP_SEGMENT14 in varchar2 default null
145 ,P_PGP_SEGMENT15 in varchar2 default null
146 ,P_PGP_SEGMENT16 in varchar2 default null
147 ,P_PGP_SEGMENT17 in varchar2 default null
148 ,P_PGP_SEGMENT18 in varchar2 default null
149 ,P_PGP_SEGMENT19 in varchar2 default null
150 ,P_PGP_SEGMENT20 in varchar2 default null
151 ,P_PGP_SEGMENT21 in varchar2 default null
152 ,P_PGP_SEGMENT22 in varchar2 default null
153 ,P_PGP_SEGMENT23 in varchar2 default null
154 ,P_PGP_SEGMENT24 in varchar2 default null
155 ,P_PGP_SEGMENT25 in varchar2 default null
156 ,P_PGP_SEGMENT26 in varchar2 default null
157 ,P_PGP_SEGMENT27 in varchar2 default null
158 ,P_PGP_SEGMENT28 in varchar2 default null
159 ,P_PGP_SEGMENT29 in varchar2 default null
160 ,P_PGP_SEGMENT30 in varchar2 default null
161 ,P_SCL_CONCAT_SEGMENTS in varchar2 default null
162 ,P_PGP_CONCAT_SEGMENTS in varchar2 default null
163 ,P_ASSIGNMENT_USER_KEY in varchar2
164 ,P_PERSON_USER_KEY in varchar2
165 ,P_ORGANIZATION_NAME in varchar2
166 ,P_LANGUAGE_CODE in varchar2
167 ,P_USER_STATUS in varchar2 default null
168 ,P_DEFAULT_CODE_COMB_USER_KEY in varchar2 default null
169 ,P_ESTABLISHMENT_ORG_NAME in varchar2 default null
170 ,P_JOB_NAME in varchar2 default null
171 ,P_LOCATION_CODE in varchar2 default null
172 ,P_POSITION_NAME in varchar2 default null
173 ,P_GRADE_NAME in varchar2 default null
174 ,P_SET_OF_BOOKS_NAME in varchar2 default null
175 ,P_SUPERVISOR_USER_KEY in varchar2 default null
176 ,P_VENDOR_NAME in varchar2 default null
177 ,P_VENDOR_SITE_ID in number default null
178 ,P_PO_HEADER_ID in number default null
179 ,P_PO_LINE_ID in number default null
180 ,P_SCL_CONTRACTOR_NAME in varchar2 default null
181 ,P_SVR_ASSIGNMENT_USER_KEY in varchar2 default null) is
182 blid number := p_data_pump_batch_line_id;
183 begin
184 if blid is not null then
185 delete from hr_pump_batch_lines where batch_line_id = blid;
186 delete from hr_pump_batch_exceptions
187 where source_type = 'BATCH_LINE' and source_id = blid;
188 end if;
189 insert into hr_pump_batch_lines
190 (batch_id
191 ,batch_line_id
192 ,business_group_name
193 ,api_module_id
194 ,line_status
195 ,user_sequence
196 ,link_value
197 ,pval001
198 ,pval002
199 ,pval003
200 ,pval004
201 ,pval005
202 ,pval006
203 ,pval007
204 ,pval008
205 ,pval009
206 ,pval010
207 ,pval011
208 ,pval012
209 ,pval013
210 ,pval014
211 ,pval015
212 ,pval016
213 ,pval017
214 ,pval018
215 ,pval019
216 ,pval020
217 ,pval021
218 ,pval022
219 ,pval023
220 ,pval024
221 ,pval025
222 ,pval026
223 ,pval027
224 ,pval028
225 ,pval029
226 ,pval030
227 ,pval031
228 ,pval032
229 ,pval033
230 ,pval034
231 ,pval035
232 ,pval036
233 ,pval037
234 ,pval038
235 ,pval039
236 ,pval040
237 ,pval041
238 ,pval042
239 ,pval043
240 ,pval044
241 ,pval045
242 ,pval046
243 ,pval047
244 ,pval048
245 ,pval049
246 ,pval050
247 ,pval051
248 ,pval052
249 ,pval053
250 ,pval054
251 ,pval055
252 ,pval056
253 ,pval057
254 ,pval058
255 ,pval059
256 ,pval060
257 ,pval061
258 ,pval062
259 ,pval063
260 ,pval064
261 ,pval065
262 ,pval066
263 ,pval067
264 ,pval068
265 ,pval069
266 ,pval070
267 ,pval071
268 ,pval072
269 ,pval073
270 ,pval074
271 ,pval075
272 ,pval076
273 ,pval077
274 ,pval078
275 ,pval079
276 ,pval080
277 ,pval081
278 ,pval082
279 ,pval093
280 ,pval094
281 ,pval095
282 ,pval096
283 ,pval097
284 ,pval098
285 ,pval099
286 ,pval100
287 ,pval101
288 ,pval102
289 ,pval103
290 ,pval104
291 ,pval105
292 ,pval106
293 ,pval107
294 ,pval108
295 ,pval109
296 ,pval110)
297 values
298 (p_batch_id
299 ,nvl(blid,hr_pump_batch_lines_s.nextval)
300 ,p_data_pump_business_grp_name
301 ,3215
302 ,'U'
303 ,p_user_sequence
304 ,p_link_value
305 ,dc(P_EFFECTIVE_DATE)
306 ,P_ASSIGNMENT_NUMBER
307 ,P_ASSIGNMENT_CATEGORY
308 ,P_CHANGE_REASON
309 ,P_COMMENTS
310 ,P_FREQUENCY
311 ,P_INTERNAL_ADDRESS_LINE
312 ,P_LABOUR_UNION_MEMBER_FLAG
313 ,P_MANAGER_FLAG
314 ,P_NORMAL_HOURS
315 ,P_PROJECT_TITLE
316 ,P_SOURCE_TYPE
317 ,P_TIME_NORMAL_FINISH
318 ,P_TIME_NORMAL_START
319 ,P_TITLE
320 ,P_VENDOR_ASSIGNMENT_NUMBER
321 ,P_VENDOR_EMPLOYEE_NUMBER
322 ,dc(P_PROJECTED_ASSIGNMENT_END)
323 ,P_ATTRIBUTE_CATEGORY
324 ,P_ATTRIBUTE1
325 ,P_ATTRIBUTE2
326 ,P_ATTRIBUTE3
327 ,P_ATTRIBUTE4
328 ,P_ATTRIBUTE5
329 ,P_ATTRIBUTE6
330 ,P_ATTRIBUTE7
331 ,P_ATTRIBUTE8
332 ,P_ATTRIBUTE9
333 ,P_ATTRIBUTE10
334 ,P_ATTRIBUTE11
335 ,P_ATTRIBUTE12
336 ,P_ATTRIBUTE13
337 ,P_ATTRIBUTE14
338 ,P_ATTRIBUTE15
339 ,P_ATTRIBUTE16
340 ,P_ATTRIBUTE17
341 ,P_ATTRIBUTE18
342 ,P_ATTRIBUTE19
343 ,P_ATTRIBUTE20
344 ,P_ATTRIBUTE21
345 ,P_ATTRIBUTE22
346 ,P_ATTRIBUTE23
347 ,P_ATTRIBUTE24
348 ,P_ATTRIBUTE25
349 ,P_ATTRIBUTE26
350 ,P_ATTRIBUTE27
351 ,P_ATTRIBUTE28
352 ,P_ATTRIBUTE29
353 ,P_ATTRIBUTE30
354 ,P_PGP_SEGMENT1
355 ,P_PGP_SEGMENT2
356 ,P_PGP_SEGMENT3
357 ,P_PGP_SEGMENT4
358 ,P_PGP_SEGMENT5
359 ,P_PGP_SEGMENT6
360 ,P_PGP_SEGMENT7
361 ,P_PGP_SEGMENT8
362 ,P_PGP_SEGMENT9
363 ,P_PGP_SEGMENT10
364 ,P_PGP_SEGMENT11
365 ,P_PGP_SEGMENT12
366 ,P_PGP_SEGMENT13
367 ,P_PGP_SEGMENT14
368 ,P_PGP_SEGMENT15
369 ,P_PGP_SEGMENT16
370 ,P_PGP_SEGMENT17
371 ,P_PGP_SEGMENT18
372 ,P_PGP_SEGMENT19
373 ,P_PGP_SEGMENT20
374 ,P_PGP_SEGMENT21
375 ,P_PGP_SEGMENT22
376 ,P_PGP_SEGMENT23
377 ,P_PGP_SEGMENT24
378 ,P_PGP_SEGMENT25
379 ,P_PGP_SEGMENT26
380 ,P_PGP_SEGMENT27
381 ,P_PGP_SEGMENT28
382 ,P_PGP_SEGMENT29
383 ,P_PGP_SEGMENT30
384 ,P_SCL_CONCAT_SEGMENTS
385 ,P_PGP_CONCAT_SEGMENTS
386 ,P_ASSIGNMENT_USER_KEY
387 ,P_PERSON_USER_KEY
388 ,P_ORGANIZATION_NAME
389 ,P_LANGUAGE_CODE
390 ,P_USER_STATUS
391 ,P_DEFAULT_CODE_COMB_USER_KEY
392 ,P_ESTABLISHMENT_ORG_NAME
393 ,P_JOB_NAME
394 ,P_LOCATION_CODE
395 ,P_POSITION_NAME
396 ,P_GRADE_NAME
397 ,P_SET_OF_BOOKS_NAME
398 ,P_SUPERVISOR_USER_KEY
399 ,P_VENDOR_NAME
400 ,P_VENDOR_SITE_ID
401 ,P_PO_HEADER_ID
402 ,P_PO_LINE_ID
403 ,P_SCL_CONTRACTOR_NAME
404 ,P_SVR_ASSIGNMENT_USER_KEY);
405 end insert_batch_lines;
406 --
407 procedure call
408 (p_business_group_id in number,
409 p_batch_line_id in number) is
410 cursor cr is
411 select l.rowid myrowid,
412 decode(l.pval001,cn,dn,d(l.pval001)) p1,
413 decode(l.pval002,cn,vn,l.pval002) p2,
414 decode(l.pval003,cn,vn,vn,vn,
415 hr_pump_get.gl(l.pval003,'CWK_ASG_CATEGORY',d(l.pval001),l.pval095)) p3,
416 l.pval003 d3,
417 decode(l.pval004,cn,vn,vn,vn,
418 hr_pump_get.gl(l.pval004,'CWK_ASSIGN_REASON',d(l.pval001),l.pval095)) p4,
419 l.pval004 d4,
420 decode(l.pval005,cn,vn,vn,vn,l.pval005) p5,
421 l.pval005 d5,
422 decode(l.pval006,cn,vn,vn,vn,
423 hr_pump_get.gl(l.pval006,'FREQUENCY',d(l.pval001),l.pval095)) p6,
424 l.pval006 d6,
425 decode(l.pval007,cn,vn,vn,vn,l.pval007) p7,
426 l.pval007 d7,
427 decode(l.pval008,cn,vn,vn,'N',
428 hr_pump_get.gl(l.pval008,'YES_NO',d(l.pval001),l.pval095)) p8,
429 l.pval008 d8,
430 decode(l.pval009,cn,vn,vn,vn,
434 l.pval010 d10,
431 hr_pump_get.gl(l.pval009,'YES_NO',d(l.pval001),l.pval095)) p9,
432 l.pval009 d9,
433 decode(l.pval010,cn,nn,vn,nn,n(l.pval010)) p10,
435 decode(l.pval011,cn,vn,vn,vn,l.pval011) p11,
436 l.pval011 d11,
437 decode(l.pval012,cn,vn,vn,vn,
438 hr_pump_get.gl(l.pval012,'REC_TYPE',d(l.pval001),l.pval095)) p12,
439 l.pval012 d12,
440 decode(l.pval013,cn,vn,vn,vn,l.pval013) p13,
441 l.pval013 d13,
442 decode(l.pval014,cn,vn,vn,vn,l.pval014) p14,
443 l.pval014 d14,
444 decode(l.pval015,cn,vn,vn,vn,l.pval015) p15,
445 l.pval015 d15,
446 decode(l.pval016,cn,vn,vn,vn,l.pval016) p16,
447 l.pval016 d16,
448 decode(l.pval017,cn,vn,vn,vn,l.pval017) p17,
449 l.pval017 d17,
450 decode(l.pval018,cn,dn,vn,dn,d(l.pval018)) p18,
451 l.pval018 d18,
452 decode(l.pval019,cn,vn,vn,vn,l.pval019) p19,
453 l.pval019 d19,
454 decode(l.pval020,cn,vn,vn,vn,l.pval020) p20,
455 l.pval020 d20,
456 decode(l.pval021,cn,vn,vn,vn,l.pval021) p21,
457 l.pval021 d21,
458 decode(l.pval022,cn,vn,vn,vn,l.pval022) p22,
459 l.pval022 d22,
460 decode(l.pval023,cn,vn,vn,vn,l.pval023) p23,
461 l.pval023 d23,
462 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
463 l.pval024 d24,
464 decode(l.pval025,cn,vn,vn,vn,l.pval025) p25,
465 l.pval025 d25,
466 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
467 l.pval026 d26,
468 decode(l.pval027,cn,vn,vn,vn,l.pval027) p27,
469 l.pval027 d27,
470 decode(l.pval028,cn,vn,vn,vn,l.pval028) p28,
471 l.pval028 d28,
472 decode(l.pval029,cn,vn,vn,vn,l.pval029) p29,
473 l.pval029 d29,
474 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
475 l.pval030 d30,
476 decode(l.pval031,cn,vn,vn,vn,l.pval031) p31,
477 l.pval031 d31,
478 decode(l.pval032,cn,vn,vn,vn,l.pval032) p32,
479 l.pval032 d32,
480 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
481 l.pval033 d33,
482 decode(l.pval034,cn,vn,vn,vn,l.pval034) p34,
483 l.pval034 d34,
484 decode(l.pval035,cn,vn,vn,vn,l.pval035) p35,
485 l.pval035 d35,
486 decode(l.pval036,cn,vn,vn,vn,l.pval036) p36,
487 l.pval036 d36,
488 decode(l.pval037,cn,vn,vn,vn,l.pval037) p37,
489 l.pval037 d37,
490 decode(l.pval038,cn,vn,vn,vn,l.pval038) p38,
491 l.pval038 d38,
492 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
493 l.pval039 d39,
494 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
495 l.pval040 d40,
496 decode(l.pval041,cn,vn,vn,vn,l.pval041) p41,
497 l.pval041 d41,
498 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
499 l.pval042 d42,
500 decode(l.pval043,cn,vn,vn,vn,l.pval043) p43,
501 l.pval043 d43,
502 decode(l.pval044,cn,vn,vn,vn,l.pval044) p44,
503 l.pval044 d44,
504 decode(l.pval045,cn,vn,vn,vn,l.pval045) p45,
505 l.pval045 d45,
506 decode(l.pval046,cn,vn,vn,vn,l.pval046) p46,
507 l.pval046 d46,
508 decode(l.pval047,cn,vn,vn,vn,l.pval047) p47,
509 l.pval047 d47,
510 decode(l.pval048,cn,vn,vn,vn,l.pval048) p48,
511 l.pval048 d48,
512 decode(l.pval049,cn,vn,vn,vn,l.pval049) p49,
513 l.pval049 d49,
514 decode(l.pval050,cn,vn,vn,vn,l.pval050) p50,
515 l.pval050 d50,
516 decode(l.pval051,cn,vn,vn,vn,l.pval051) p51,
517 l.pval051 d51,
518 decode(l.pval052,cn,vn,vn,vn,l.pval052) p52,
519 l.pval052 d52,
520 decode(l.pval053,cn,vn,vn,vn,l.pval053) p53,
521 l.pval053 d53,
522 decode(l.pval054,cn,vn,vn,vn,l.pval054) p54,
523 l.pval054 d54,
524 decode(l.pval055,cn,vn,vn,vn,l.pval055) p55,
525 l.pval055 d55,
526 decode(l.pval056,cn,vn,vn,vn,l.pval056) p56,
527 l.pval056 d56,
528 decode(l.pval057,cn,vn,vn,vn,l.pval057) p57,
529 l.pval057 d57,
530 decode(l.pval058,cn,vn,vn,vn,l.pval058) p58,
531 l.pval058 d58,
532 decode(l.pval059,cn,vn,vn,vn,l.pval059) p59,
533 l.pval059 d59,
534 decode(l.pval060,cn,vn,vn,vn,l.pval060) p60,
535 l.pval060 d60,
536 decode(l.pval061,cn,vn,vn,vn,l.pval061) p61,
537 l.pval061 d61,
538 decode(l.pval062,cn,vn,vn,vn,l.pval062) p62,
539 l.pval062 d62,
540 decode(l.pval063,cn,vn,vn,vn,l.pval063) p63,
541 l.pval063 d63,
542 decode(l.pval064,cn,vn,vn,vn,l.pval064) p64,
543 l.pval064 d64,
544 decode(l.pval065,cn,vn,vn,vn,l.pval065) p65,
545 l.pval065 d65,
546 decode(l.pval066,cn,vn,vn,vn,l.pval066) p66,
547 l.pval066 d66,
548 decode(l.pval067,cn,vn,vn,vn,l.pval067) p67,
549 l.pval067 d67,
550 decode(l.pval068,cn,vn,vn,vn,l.pval068) p68,
551 l.pval068 d68,
552 decode(l.pval069,cn,vn,vn,vn,l.pval069) p69,
553 l.pval069 d69,
554 decode(l.pval070,cn,vn,vn,vn,l.pval070) p70,
555 l.pval070 d70,
556 decode(l.pval071,cn,vn,vn,vn,l.pval071) p71,
557 l.pval071 d71,
558 decode(l.pval072,cn,vn,vn,vn,l.pval072) p72,
559 l.pval072 d72,
560 decode(l.pval073,cn,vn,vn,vn,l.pval073) p73,
561 l.pval073 d73,
562 decode(l.pval074,cn,vn,vn,vn,l.pval074) p74,
563 l.pval074 d74,
564 decode(l.pval075,cn,vn,vn,vn,l.pval075) p75,
565 l.pval075 d75,
566 decode(l.pval076,cn,vn,vn,vn,l.pval076) p76,
567 l.pval076 d76,
568 decode(l.pval077,cn,vn,vn,vn,l.pval077) p77,
569 l.pval077 d77,
570 decode(l.pval078,cn,vn,vn,vn,l.pval078) p78,
571 l.pval078 d78,
572 decode(l.pval079,cn,vn,vn,vn,l.pval079) p79,
573 l.pval079 d79,
574 decode(l.pval080,cn,vn,vn,vn,l.pval080) p80,
575 l.pval080 d80,
576 decode(l.pval081,cn,vn,vn,vn,l.pval081) p81,
577 l.pval081 d81,
578 l.pval082 p82,
579 l.pval083 p83,
580 decode(l.pval084,cn,dn,d(l.pval084)) p84,
581 decode(l.pval085,cn,dn,d(l.pval085)) p85,
582 l.pval086 p86,
583 l.pval087 p87,
584 l.pval088 p88,
585 l.pval089 p89,
586 l.pval090 p90,
590 decode(l.pval094,cn,vn,l.pval094) p94,
587 l.pval091 p91,
588 l.pval092 p92,
589 decode(l.pval093,cn,vn,l.pval093) p93,
591 decode(l.pval095,cn,vn,l.pval095) p95,
592 decode(l.pval096,cn,vn,vn,vn,l.pval096) p96,
593 l.pval096 d96,
594 decode(l.pval097,cn,vn,vn,vn,l.pval097) p97,
595 l.pval097 d97,
596 decode(l.pval098,cn,vn,vn,vn,l.pval098) p98,
597 l.pval098 d98,
598 decode(l.pval099,cn,vn,vn,vn,l.pval099) p99,
599 l.pval099 d99,
600 decode(l.pval100,cn,vn,vn,vn,l.pval100) p100,
601 l.pval100 d100,
602 decode(l.pval101,cn,vn,vn,vn,l.pval101) p101,
603 l.pval101 d101,
604 decode(l.pval102,cn,vn,vn,vn,l.pval102) p102,
605 l.pval102 d102,
606 decode(l.pval103,cn,vn,vn,vn,l.pval103) p103,
607 l.pval103 d103,
608 decode(l.pval104,cn,vn,vn,vn,l.pval104) p104,
609 l.pval104 d104,
610 decode(l.pval105,cn,vn,vn,vn,l.pval105) p105,
611 l.pval105 d105,
612 decode(l.pval106,cn,nn,vn,nn,n(l.pval106)) p106,
613 l.pval106 d106,
614 decode(l.pval107,cn,nn,vn,nn,n(l.pval107)) p107,
615 l.pval107 d107,
616 decode(l.pval108,cn,nn,vn,nn,n(l.pval108)) p108,
617 l.pval108 d108,
618 decode(l.pval109,cn,vn,vn,vn,l.pval109) p109,
619 l.pval109 d109,
620 decode(l.pval110,cn,vn,vn,vn,l.pval110) p110,
621 l.pval110 d110
622 from hr_pump_batch_lines l
623 where l.batch_line_id = p_batch_line_id;
624 --
625 c cr%rowtype;
626 l_validate boolean := false;
627 L_ASSIGNMENT_ID number;
628 L_OTHER_MANAGER_WARNING boolean;
629 L_HOURLY_SALARIED_WARNING boolean;
630 L_PERSON_ID number;
631 L_ORGANIZATION_ID number;
632 L_ASSIGNMENT_STATUS_TYPE_ID number;
633 L_DEFAULT_CODE_COMB_ID number;
634 L_ESTABLISHMENT_ID number;
635 L_JOB_ID number;
636 L_LOCATION_ID number;
637 L_POSITION_ID number;
638 L_GRADE_ID number;
639 L_SET_OF_BOOKS_ID number;
640 L_SUPERVISOR_ID number;
641 L_VENDOR_ID number;
642 L_VENDOR_SITE_ID number;
643 L_PO_HEADER_ID number;
644 L_PO_LINE_ID number;
645 L_SCL_CONTRACTOR_NAME varchar2(2000);
646 L_SUPERVISOR_ASSIGNMENT_ID number;
647 --
648 begin
649 hr_data_pump.entry('call');
650 open cr;
651 fetch cr into c;
652 if cr%notfound then
653 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
654 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
655 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
656 hr_utility.set_message_token('VALUE',p_batch_line_id);
657 hr_utility.raise_error;
658 end if;
659 --
660 if c.p93 is null then
661 L_PERSON_ID:=nn;
662 else
663 L_PERSON_ID :=
664 hr_pump_get.get_person_id
665 (P_PERSON_USER_KEY => c.p93);
666 end if;
667 --
668 if c.p94 is null or
669 c.p1 is null or
670 c.p95 is null then
671 L_ORGANIZATION_ID:=nn;
672 else
673 L_ORGANIZATION_ID :=
674 hr_pump_get.get_organization_id
675 (P_ORGANIZATION_NAME => c.p94
676 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
677 ,P_EFFECTIVE_DATE => c.p1
678 ,P_LANGUAGE_CODE => c.p95);
679 end if;
680 --
681 if c.p96 is null or
682 c.p95 is null then
683 L_ASSIGNMENT_STATUS_TYPE_ID:=nn;
684 else
685 L_ASSIGNMENT_STATUS_TYPE_ID :=
686 hr_pump_get.get_assignment_status_type_id
687 (P_USER_STATUS => c.p96
688 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
689 ,P_LANGUAGE_CODE => c.p95);
690 end if;
691 --
692 if c.p97 is null then
693 L_DEFAULT_CODE_COMB_ID:=nn;
694 else
695 L_DEFAULT_CODE_COMB_ID :=
696 hr_pump_get.get_default_code_comb_id
697 (P_DEFAULT_CODE_COMB_USER_KEY => c.p97);
698 end if;
699 --
700 if c.p98 is null or
701 c.p1 is null or
702 c.p95 is null then
703 L_ESTABLISHMENT_ID:=nn;
704 else
705 L_ESTABLISHMENT_ID :=
706 hr_pump_get.GET_ESTABLISHMENT_ORG_ID
707 (P_ESTABLISHMENT_ORG_NAME => c.p98
708 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
709 ,P_EFFECTIVE_DATE => c.p1
710 ,P_LANGUAGE_CODE => c.p95);
711 end if;
712 --
713 if c.p99 is null or
714 c.p1 is null then
715 L_JOB_ID:=nn;
716 else
717 L_JOB_ID :=
718 hr_pump_get.get_job_id
719 (P_JOB_NAME => c.p99
720 ,P_EFFECTIVE_DATE => c.p1
721 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID);
722 end if;
723 --
724 if c.p100 is null or
725 c.p95 is null then
726 L_LOCATION_ID:=nn;
727 else
728 L_LOCATION_ID :=
729 hr_pump_get.get_location_id
730 (P_LOCATION_CODE => c.p100
731 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
732 ,P_LANGUAGE_CODE => c.p95);
733 end if;
734 --
735 if c.p101 is null or
736 c.p1 is null then
737 L_POSITION_ID:=nn;
738 else
739 L_POSITION_ID :=
740 hr_pump_get.get_position_id
741 (P_POSITION_NAME => c.p101
742 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
743 ,P_EFFECTIVE_DATE => c.p1);
744 end if;
745 --
746 if c.p102 is null or
747 c.p1 is null then
748 L_GRADE_ID:=nn;
749 else
750 L_GRADE_ID :=
751 hr_pump_get.get_grade_id
752 (P_GRADE_NAME => c.p102
753 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
754 ,P_EFFECTIVE_DATE => c.p1);
755 end if;
756 --
757 if c.p103 is null then
758 L_SET_OF_BOOKS_ID:=nn;
759 else
760 L_SET_OF_BOOKS_ID :=
761 hr_pump_get.get_set_of_books_id
762 (P_SET_OF_BOOKS_NAME => c.p103);
763 end if;
764 --
765 if c.p104 is null then
766 L_SUPERVISOR_ID:=nn;
767 else
768 L_SUPERVISOR_ID :=
769 hr_pump_get.get_supervisor_id
770 (P_SUPERVISOR_USER_KEY => c.p104);
771 end if;
772 --
773 if c.p105 is null then
774 L_VENDOR_ID:=nn;
775 else
779 end if;
776 L_VENDOR_ID :=
777 hr_pump_get.get_vendor_id
778 (P_VENDOR_NAME => c.p105);
780 --
781 if c.p106 is null then
782 L_VENDOR_SITE_ID:=nn;
783 else
784 L_VENDOR_SITE_ID :=
785 PER_IN_DATA_PUMP.get_vendor_site_id
786 (P_VENDOR_SITE_ID => c.p106);
787 end if;
788 --
789 if c.p107 is null then
790 L_PO_HEADER_ID:=nn;
791 else
792 L_PO_HEADER_ID :=
793 PER_IN_DATA_PUMP.get_po_header_id
794 (P_PO_HEADER_ID => c.p107);
795 end if;
796 --
797 if c.p108 is null then
798 L_PO_LINE_ID:=nn;
799 else
800 L_PO_LINE_ID :=
801 PER_IN_DATA_PUMP.get_po_line_id
802 (P_PO_LINE_ID => c.p108);
803 end if;
804 --
805 if c.p109 is null then
806 L_SCL_CONTRACTOR_NAME:=vn;
807 else
808 L_SCL_CONTRACTOR_NAME :=
809 PER_IN_DATA_PUMP.GET_SCL_CONTRACTOR_ID
810 (P_SCL_CONTRACTOR_NAME => c.p109);
811 end if;
812 --
813 if c.p110 is null then
814 L_SUPERVISOR_ASSIGNMENT_ID:=nn;
815 else
816 L_SUPERVISOR_ASSIGNMENT_ID :=
817 hr_pump_get.get_supervisor_assignment_id
818 (P_SVR_ASSIGNMENT_USER_KEY => c.p110);
819 end if;
820 --
821 hr_data_pump.api_trc_on;
822 hr_in_assignment_api.create_in_secondary_cwk_asg
823 (p_validate => l_validate
824 ,P_EFFECTIVE_DATE => c.p1
825 ,p_business_group_id => p_business_group_id
826 ,P_PERSON_ID => L_PERSON_ID
827 ,P_ORGANIZATION_ID => L_ORGANIZATION_ID
828 ,P_ASSIGNMENT_NUMBER => c.p2
829 ,P_ASSIGNMENT_CATEGORY => c.p3
830 ,P_ASSIGNMENT_STATUS_TYPE_ID => L_ASSIGNMENT_STATUS_TYPE_ID
831 ,P_CHANGE_REASON => c.p4
832 ,P_COMMENTS => c.p5
833 ,P_DEFAULT_CODE_COMB_ID => L_DEFAULT_CODE_COMB_ID
834 ,P_ESTABLISHMENT_ID => L_ESTABLISHMENT_ID
835 ,P_FREQUENCY => c.p6
836 ,P_INTERNAL_ADDRESS_LINE => c.p7
837 ,P_JOB_ID => L_JOB_ID
838 ,P_LABOUR_UNION_MEMBER_FLAG => c.p8
839 ,P_LOCATION_ID => L_LOCATION_ID
840 ,P_MANAGER_FLAG => c.p9
841 ,P_NORMAL_HOURS => c.p10
842 ,P_POSITION_ID => L_POSITION_ID
843 ,P_GRADE_ID => L_GRADE_ID
844 ,P_PROJECT_TITLE => c.p11
845 ,P_SET_OF_BOOKS_ID => L_SET_OF_BOOKS_ID
846 ,P_SOURCE_TYPE => c.p12
847 ,P_SUPERVISOR_ID => L_SUPERVISOR_ID
848 ,P_TIME_NORMAL_FINISH => c.p13
849 ,P_TIME_NORMAL_START => c.p14
850 ,P_TITLE => c.p15
851 ,P_VENDOR_ASSIGNMENT_NUMBER => c.p16
852 ,P_VENDOR_EMPLOYEE_NUMBER => c.p17
853 ,P_VENDOR_ID => L_VENDOR_ID
854 ,P_VENDOR_SITE_ID => L_VENDOR_SITE_ID
855 ,P_PO_HEADER_ID => L_PO_HEADER_ID
856 ,P_PO_LINE_ID => L_PO_LINE_ID
857 ,P_PROJECTED_ASSIGNMENT_END => c.p18
858 ,P_ATTRIBUTE_CATEGORY => c.p19
859 ,P_ATTRIBUTE1 => c.p20
860 ,P_ATTRIBUTE2 => c.p21
861 ,P_ATTRIBUTE3 => c.p22
862 ,P_ATTRIBUTE4 => c.p23
863 ,P_ATTRIBUTE5 => c.p24
864 ,P_ATTRIBUTE6 => c.p25
865 ,P_ATTRIBUTE7 => c.p26
866 ,P_ATTRIBUTE8 => c.p27
867 ,P_ATTRIBUTE9 => c.p28
868 ,P_ATTRIBUTE10 => c.p29
869 ,P_ATTRIBUTE11 => c.p30
870 ,P_ATTRIBUTE12 => c.p31
871 ,P_ATTRIBUTE13 => c.p32
872 ,P_ATTRIBUTE14 => c.p33
873 ,P_ATTRIBUTE15 => c.p34
874 ,P_ATTRIBUTE16 => c.p35
875 ,P_ATTRIBUTE17 => c.p36
876 ,P_ATTRIBUTE18 => c.p37
877 ,P_ATTRIBUTE19 => c.p38
878 ,P_ATTRIBUTE20 => c.p39
879 ,P_ATTRIBUTE21 => c.p40
880 ,P_ATTRIBUTE22 => c.p41
881 ,P_ATTRIBUTE23 => c.p42
882 ,P_ATTRIBUTE24 => c.p43
883 ,P_ATTRIBUTE25 => c.p44
884 ,P_ATTRIBUTE26 => c.p45
885 ,P_ATTRIBUTE27 => c.p46
886 ,P_ATTRIBUTE28 => c.p47
887 ,P_ATTRIBUTE29 => c.p48
888 ,P_ATTRIBUTE30 => c.p49
889 ,P_PGP_SEGMENT1 => c.p50
890 ,P_PGP_SEGMENT2 => c.p51
891 ,P_PGP_SEGMENT3 => c.p52
892 ,P_PGP_SEGMENT4 => c.p53
893 ,P_PGP_SEGMENT5 => c.p54
894 ,P_PGP_SEGMENT6 => c.p55
895 ,P_PGP_SEGMENT7 => c.p56
896 ,P_PGP_SEGMENT8 => c.p57
897 ,P_PGP_SEGMENT9 => c.p58
898 ,P_PGP_SEGMENT10 => c.p59
899 ,P_PGP_SEGMENT11 => c.p60
900 ,P_PGP_SEGMENT12 => c.p61
901 ,P_PGP_SEGMENT13 => c.p62
902 ,P_PGP_SEGMENT14 => c.p63
903 ,P_PGP_SEGMENT15 => c.p64
904 ,P_PGP_SEGMENT16 => c.p65
905 ,P_PGP_SEGMENT17 => c.p66
906 ,P_PGP_SEGMENT18 => c.p67
907 ,P_PGP_SEGMENT19 => c.p68
908 ,P_PGP_SEGMENT20 => c.p69
909 ,P_PGP_SEGMENT21 => c.p70
910 ,P_PGP_SEGMENT22 => c.p71
911 ,P_PGP_SEGMENT23 => c.p72
912 ,P_PGP_SEGMENT24 => c.p73
913 ,P_PGP_SEGMENT25 => c.p74
914 ,P_PGP_SEGMENT26 => c.p75
915 ,P_PGP_SEGMENT27 => c.p76
916 ,P_PGP_SEGMENT28 => c.p77
917 ,P_PGP_SEGMENT29 => c.p78
918 ,P_PGP_SEGMENT30 => c.p79
919 ,P_SCL_CONTRACTOR_NAME => L_SCL_CONTRACTOR_NAME
920 ,P_SCL_CONCAT_SEGMENTS => c.p80
921 ,P_PGP_CONCAT_SEGMENTS => c.p81
922 ,P_SUPERVISOR_ASSIGNMENT_ID => L_SUPERVISOR_ASSIGNMENT_ID
923 ,P_ASSIGNMENT_ID => L_ASSIGNMENT_ID
924 ,P_OBJECT_VERSION_NUMBER => c.p83
925 ,P_EFFECTIVE_START_DATE => c.p84
926 ,P_EFFECTIVE_END_DATE => c.p85
927 ,P_ASSIGNMENT_SEQUENCE => c.p86
928 ,P_COMMENT_ID => c.p87
929 ,P_PEOPLE_GROUP_ID => c.p88
930 ,P_PEOPLE_GROUP_NAME => c.p89
931 ,P_OTHER_MANAGER_WARNING => L_OTHER_MANAGER_WARNING
932 ,P_HOURLY_SALARIED_WARNING => L_HOURLY_SALARIED_WARNING
933 ,P_SOFT_CODING_KEYFLEX_ID => c.p92);
934 hr_data_pump.api_trc_off;
935 --
936 iuk(p_batch_line_id,c.p82,L_ASSIGNMENT_ID);
937 --
938 if L_OTHER_MANAGER_WARNING then
939 c.p90 := 'TRUE';
940 else
941 c.p90 := 'FALSE';
942 end if;
943 --
944 if L_HOURLY_SALARIED_WARNING then
945 c.p91 := 'TRUE';
946 else
947 c.p91 := 'FALSE';
948 end if;
949 --
950 update hr_pump_batch_lines l set
951 l.pval002 = decode(c.p2,null,cn,c.p2),
952 l.pval082 = decode(c.p82,null,cn,c.p82),
956 l.pval086 = decode(c.p86,null,cn,c.p86),
953 l.pval083 = decode(c.p83,null,cn,c.p83),
954 l.pval084 = decode(c.p84,null,cn,dc(c.p84)),
955 l.pval085 = decode(c.p85,null,cn,dc(c.p85)),
957 l.pval087 = decode(c.p87,null,cn,c.p87),
958 l.pval088 = decode(c.p88,null,cn,c.p88),
959 l.pval089 = decode(c.p89,null,cn,c.p89),
960 l.pval090 = decode(c.p90,null,cn,c.p90),
961 l.pval091 = decode(c.p91,null,cn,c.p91),
962 l.pval092 = decode(c.p92,null,cn,c.p92)
963 where l.rowid = c.myrowid;
964 --
965 close cr;
966 --
967 hr_data_pump.exit('call');
968 exception
969 when hr_multi_message.error_message_exist then
970 if cr%isopen then
971 close cr;
972 end if;
973 hr_pump_utils.set_multi_msg_error_flag(true);
974 when others then
975 if cr%isopen then
976 close cr;
977 end if;
978 raise;
979 end call;
980 end hrdpp_create_in_secondary_cwk_;