eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.HRDPP_CREATE_RU_CWK

Source


1 package body hrdpp_CREATE_RU_CWK as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2007/01/03 23:01:24
4  * Generated for API: HR_RU_CONTINGENT_WORKER_API.CREATE_RU_CWK
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_START_DATE in date
83 ,P_LAST_NAME in varchar2
84 ,P_NPW_NUMBER in varchar2
85 ,P_BACKGROUND_CHECK_STATUS in varchar2 default null
86 ,P_BACKGROUND_DATE_CHECK in date default null
87 ,P_BLOOD_TYPE in varchar2 default null
88 ,P_COMMENTS in varchar2 default null
89 ,P_COUNTRY_OF_BIRTH in varchar2 default null
90 ,P_DATE_OF_BIRTH in date default null
91 ,P_DATE_OF_DEATH in date default null
92 ,P_DPDNT_ADOPTION_DATE in date default null
93 ,P_DPDNT_VLNTRY_SVCE_FLAG in varchar2 default null
94 ,P_EMAIL_ADDRESS in varchar2 default null
95 ,P_FIRST_NAME in varchar2
96 ,P_FTE_CAPACITY in number default null
97 ,P_HONORS in varchar2 default null
98 ,P_INTERNAL_LOCATION in varchar2 default null
99 ,P_KNOWN_AS in varchar2 default null
100 ,P_LAST_MEDICAL_TEST_BY in varchar2 default null
101 ,P_LAST_MEDICAL_TEST_DATE in date default null
102 ,P_MAILSTOP in varchar2 default null
103 ,P_MARITAL_STATUS in varchar2 default null
104 ,P_MIDDLE_NAMES in varchar2 default null
105 ,P_INN in varchar2 default null
106 ,P_NATIONALITY in varchar2 default null
107 ,P_OFFICE_NUMBER in varchar2 default null
108 ,P_ON_MILITARY_SERVICE in varchar2 default null
109 ,P_PARTY_ID in number default null
110 ,P_GENITIVE_LAST_NAME in varchar2 default null
111 ,P_PREVIOUS_LAST_NAME in varchar2 default null
112 ,P_PROJECTED_PLACEMENT_END in date default null
113 ,P_RECEIPT_OF_DEATH_CERT_DATE in date default null
114 ,P_REGION_OF_BIRTH in varchar2 default null
115 ,P_REGISTERED_DISABLED_FLAG in varchar2 default null
116 ,P_RESUME_EXISTS in varchar2 default null
117 ,P_RESUME_LAST_UPDATED in date default null
118 ,P_SECOND_PASSPORT_EXISTS in varchar2 default null
119 ,P_SEX in varchar2 default null
120 ,P_STUDENT_STATUS in varchar2 default null
121 ,P_SUFFIX in varchar2 default null
122 ,P_TITLE in varchar2 default null
123 ,P_TOWN_OF_BIRTH in varchar2 default null
124 ,P_USES_TOBACCO_FLAG in varchar2 default null
125 ,P_WORK_SCHEDULE in varchar2 default null
126 ,P_WORK_TELEPHONE in varchar2 default null
127 ,P_EXP_CHECK_SEND_TO_ADDRESS in varchar2 default null
128 ,P_HOLD_APPLICANT_DATE_UNTIL in date default null
129 ,P_DATE_EMPLOYEE_DATA_VERIFIED in date default null
130 ,P_COORD_BEN_MED_PLN_NO in varchar2 default null
131 ,P_COORD_BEN_NO_CVG_FLAG in varchar2 default null
132 ,P_ORIGINAL_DATE_OF_HIRE in date default null
133 ,P_ATTRIBUTE_CATEGORY in varchar2 default null
134 ,P_ATTRIBUTE1 in varchar2 default null
135 ,P_ATTRIBUTE2 in varchar2 default null
136 ,P_ATTRIBUTE3 in varchar2 default null
137 ,P_ATTRIBUTE4 in varchar2 default null
138 ,P_ATTRIBUTE5 in varchar2 default null
139 ,P_ATTRIBUTE6 in varchar2 default null
140 ,P_ATTRIBUTE7 in varchar2 default null
141 ,P_ATTRIBUTE8 in varchar2 default null
142 ,P_ATTRIBUTE9 in varchar2 default null
143 ,P_ATTRIBUTE10 in varchar2 default null
144 ,P_ATTRIBUTE11 in varchar2 default null
145 ,P_ATTRIBUTE12 in varchar2 default null
146 ,P_ATTRIBUTE13 in varchar2 default null
147 ,P_ATTRIBUTE14 in varchar2 default null
148 ,P_ATTRIBUTE15 in varchar2 default null
149 ,P_ATTRIBUTE16 in varchar2 default null
150 ,P_ATTRIBUTE17 in varchar2 default null
151 ,P_ATTRIBUTE18 in varchar2 default null
152 ,P_ATTRIBUTE19 in varchar2 default null
153 ,P_ATTRIBUTE20 in varchar2 default null
154 ,P_ATTRIBUTE21 in varchar2 default null
155 ,P_ATTRIBUTE22 in varchar2 default null
156 ,P_ATTRIBUTE23 in varchar2 default null
157 ,P_ATTRIBUTE24 in varchar2 default null
158 ,P_ATTRIBUTE25 in varchar2 default null
159 ,P_ATTRIBUTE26 in varchar2 default null
160 ,P_ATTRIBUTE27 in varchar2 default null
161 ,P_ATTRIBUTE28 in varchar2 default null
162 ,P_ATTRIBUTE29 in varchar2 default null
163 ,P_ATTRIBUTE30 in varchar2 default null
164 ,P_PLACE_OF_BIRTH in varchar2 default null
165 ,P_REFERENCES in varchar2 default null
166 ,P_LOCAL_COEFFICIENT in varchar2 default null
167 ,P_CITIZENSHIP in varchar2
168 ,P_MILITARY_DOC in varchar2 default null
169 ,P_RESERVE_CATEGORY in varchar2 default null
170 ,P_MILITARY_RANK in varchar2 default null
171 ,P_MILITARY_PROFILE in varchar2 default null
172 ,P_MILITARY_REG_CODE in varchar2 default null
173 ,P_MIL_SRVC_READINESS_CATEGORY in varchar2 default null
174 ,P_MILITARY_COMMISSARIAT in varchar2 default null
175 ,P_QUITTING_MARK in varchar2 default null
176 ,P_MILITARY_UNIT_NUMBER in varchar2 default null
177 ,P_MILITARY_REG_TYPE in varchar2 default null
178 ,P_MILITARY_REG_DETAILS in varchar2 default null
179 ,P_PENSION_FUND_NUMBER in varchar2 default null
180 ,P_PERSON_USER_KEY in varchar2
181 ,P_ASSIGNMENT_USER_KEY in varchar2
182 ,P_USER_PERSON_TYPE in varchar2 default null
183 ,P_LANGUAGE_CODE in varchar2 default null
184 ,P_CORRESPONDENCE_LANGUAGE in varchar2 default null
185 ,P_VENDOR_NAME in varchar2 default null
186 ,P_BENEFIT_GROUP in varchar2 default null) is
187 blid number := p_data_pump_batch_line_id;
188 begin
189 if blid is not null then
190 delete from hr_pump_batch_lines where batch_line_id = blid;
191 delete from hr_pump_batch_exceptions
192 where source_type = 'BATCH_LINE' and source_id = blid;
193 end if;
194 insert into hr_pump_batch_lines
195 (batch_id
196 ,batch_line_id
197 ,business_group_name
198 ,api_module_id
199 ,line_status
200 ,user_sequence
201 ,link_value
202 ,pval001
203 ,pval002
204 ,pval003
205 ,pval004
206 ,pval005
207 ,pval006
208 ,pval007
209 ,pval008
210 ,pval009
211 ,pval010
212 ,pval011
213 ,pval012
214 ,pval013
215 ,pval014
216 ,pval015
217 ,pval016
218 ,pval017
219 ,pval018
220 ,pval019
221 ,pval020
222 ,pval021
223 ,pval022
224 ,pval023
225 ,pval024
226 ,pval025
227 ,pval026
228 ,pval027
229 ,pval028
230 ,pval029
231 ,pval030
232 ,pval031
233 ,pval032
234 ,pval033
235 ,pval034
236 ,pval035
237 ,pval036
238 ,pval037
239 ,pval038
240 ,pval039
241 ,pval040
242 ,pval041
243 ,pval042
244 ,pval043
245 ,pval044
246 ,pval045
247 ,pval046
248 ,pval047
249 ,pval048
250 ,pval049
251 ,pval050
252 ,pval051
253 ,pval052
254 ,pval053
255 ,pval054
256 ,pval055
257 ,pval056
258 ,pval057
259 ,pval058
260 ,pval059
261 ,pval060
262 ,pval061
263 ,pval062
264 ,pval063
265 ,pval064
266 ,pval065
267 ,pval066
268 ,pval067
269 ,pval068
270 ,pval069
271 ,pval070
272 ,pval071
273 ,pval072
274 ,pval073
275 ,pval074
276 ,pval075
277 ,pval076
278 ,pval077
279 ,pval078
280 ,pval079
281 ,pval080
282 ,pval081
283 ,pval082
284 ,pval083
285 ,pval084
286 ,pval085
287 ,pval086
288 ,pval087
289 ,pval088
290 ,pval089
291 ,pval090
292 ,pval091
293 ,pval092
294 ,pval093
295 ,pval094
296 ,pval095
297 ,pval096
298 ,pval097
299 ,pval098
300 ,pval099
301 ,pval106
302 ,pval111
303 ,pval112
304 ,pval113
305 ,pval114
306 ,pval115)
307 values
308 (p_batch_id
309 ,nvl(blid,hr_pump_batch_lines_s.nextval)
310 ,p_data_pump_business_grp_name
311 ,3873
312 ,'U'
313 ,p_user_sequence
314 ,p_link_value
315 ,dc(P_START_DATE)
316 ,P_LAST_NAME
317 ,P_NPW_NUMBER
318 ,P_BACKGROUND_CHECK_STATUS
319 ,dc(P_BACKGROUND_DATE_CHECK)
320 ,P_BLOOD_TYPE
321 ,P_COMMENTS
322 ,P_COUNTRY_OF_BIRTH
323 ,dc(P_DATE_OF_BIRTH)
324 ,dc(P_DATE_OF_DEATH)
325 ,dc(P_DPDNT_ADOPTION_DATE)
326 ,P_DPDNT_VLNTRY_SVCE_FLAG
327 ,P_EMAIL_ADDRESS
328 ,P_FIRST_NAME
329 ,P_FTE_CAPACITY
330 ,P_HONORS
331 ,P_INTERNAL_LOCATION
332 ,P_KNOWN_AS
333 ,P_LAST_MEDICAL_TEST_BY
334 ,dc(P_LAST_MEDICAL_TEST_DATE)
335 ,P_MAILSTOP
336 ,P_MARITAL_STATUS
337 ,P_MIDDLE_NAMES
338 ,P_INN
339 ,P_NATIONALITY
340 ,P_OFFICE_NUMBER
341 ,P_ON_MILITARY_SERVICE
342 ,P_PARTY_ID
343 ,P_GENITIVE_LAST_NAME
344 ,P_PREVIOUS_LAST_NAME
345 ,dc(P_PROJECTED_PLACEMENT_END)
346 ,dc(P_RECEIPT_OF_DEATH_CERT_DATE)
347 ,P_REGION_OF_BIRTH
348 ,P_REGISTERED_DISABLED_FLAG
349 ,P_RESUME_EXISTS
350 ,dc(P_RESUME_LAST_UPDATED)
351 ,P_SECOND_PASSPORT_EXISTS
352 ,P_SEX
353 ,P_STUDENT_STATUS
354 ,P_SUFFIX
355 ,P_TITLE
356 ,P_TOWN_OF_BIRTH
357 ,P_USES_TOBACCO_FLAG
358 ,P_WORK_SCHEDULE
359 ,P_WORK_TELEPHONE
360 ,P_EXP_CHECK_SEND_TO_ADDRESS
361 ,dc(P_HOLD_APPLICANT_DATE_UNTIL)
362 ,dc(P_DATE_EMPLOYEE_DATA_VERIFIED)
363 ,P_COORD_BEN_MED_PLN_NO
364 ,P_COORD_BEN_NO_CVG_FLAG
365 ,dc(P_ORIGINAL_DATE_OF_HIRE)
366 ,P_ATTRIBUTE_CATEGORY
367 ,P_ATTRIBUTE1
368 ,P_ATTRIBUTE2
369 ,P_ATTRIBUTE3
370 ,P_ATTRIBUTE4
371 ,P_ATTRIBUTE5
372 ,P_ATTRIBUTE6
373 ,P_ATTRIBUTE7
374 ,P_ATTRIBUTE8
375 ,P_ATTRIBUTE9
376 ,P_ATTRIBUTE10
377 ,P_ATTRIBUTE11
378 ,P_ATTRIBUTE12
379 ,P_ATTRIBUTE13
380 ,P_ATTRIBUTE14
381 ,P_ATTRIBUTE15
382 ,P_ATTRIBUTE16
383 ,P_ATTRIBUTE17
384 ,P_ATTRIBUTE18
385 ,P_ATTRIBUTE19
386 ,P_ATTRIBUTE20
387 ,P_ATTRIBUTE21
388 ,P_ATTRIBUTE22
389 ,P_ATTRIBUTE23
390 ,P_ATTRIBUTE24
391 ,P_ATTRIBUTE25
392 ,P_ATTRIBUTE26
393 ,P_ATTRIBUTE27
394 ,P_ATTRIBUTE28
395 ,P_ATTRIBUTE29
396 ,P_ATTRIBUTE30
397 ,P_PLACE_OF_BIRTH
398 ,P_REFERENCES
399 ,P_LOCAL_COEFFICIENT
400 ,P_CITIZENSHIP
401 ,P_MILITARY_DOC
402 ,P_RESERVE_CATEGORY
403 ,P_MILITARY_RANK
404 ,P_MILITARY_PROFILE
405 ,P_MILITARY_REG_CODE
406 ,P_MIL_SRVC_READINESS_CATEGORY
407 ,P_MILITARY_COMMISSARIAT
408 ,P_QUITTING_MARK
409 ,P_MILITARY_UNIT_NUMBER
410 ,P_MILITARY_REG_TYPE
411 ,P_MILITARY_REG_DETAILS
412 ,P_PENSION_FUND_NUMBER
413 ,P_PERSON_USER_KEY
414 ,P_ASSIGNMENT_USER_KEY
415 ,P_USER_PERSON_TYPE
416 ,P_LANGUAGE_CODE
417 ,P_CORRESPONDENCE_LANGUAGE
418 ,P_VENDOR_NAME
419 ,P_BENEFIT_GROUP);
420 end insert_batch_lines;
421 --
422 procedure call
423 (p_business_group_id in number,
424 p_batch_line_id     in number) is
425 cursor cr is
426 select l.rowid myrowid,
427 decode(l.pval001,cn,dn,d(l.pval001)) p1,
428 decode(l.pval002,cn,vn,l.pval002) p2,
429 decode(l.pval003,cn,vn,l.pval003) p3,
430 decode(l.pval004,cn,vn,vn,vn,
431  hr_pump_get.gl(l.pval004,'YES_NO',d(l.pval001),l.pval112)) p4,
432 l.pval004 d4,
433 decode(l.pval005,cn,dn,vn,dn,d(l.pval005)) p5,
434 l.pval005 d5,
435 decode(l.pval006,cn,vn,vn,vn,l.pval006) p6,
436 l.pval006 d6,
437 decode(l.pval007,cn,vn,vn,vn,l.pval007) p7,
438 l.pval007 d7,
439 decode(l.pval008,cn,vn,vn,vn,l.pval008) p8,
440 l.pval008 d8,
444 l.pval010 d10,
441 decode(l.pval009,cn,dn,vn,dn,d(l.pval009)) p9,
442 l.pval009 d9,
443 decode(l.pval010,cn,dn,vn,dn,d(l.pval010)) p10,
445 decode(l.pval011,cn,dn,vn,dn,d(l.pval011)) p11,
446 l.pval011 d11,
447 decode(l.pval012,cn,vn,vn,'N',
448  hr_pump_get.gl(l.pval012,'YES_NO',d(l.pval001),l.pval112)) p12,
449 l.pval012 d12,
450 decode(l.pval013,cn,vn,vn,vn,l.pval013) p13,
451 l.pval013 d13,
452 decode(l.pval014,cn,vn,l.pval014) p14,
453 decode(l.pval015,cn,nn,vn,nn,n(l.pval015)) p15,
454 l.pval015 d15,
455 decode(l.pval016,cn,vn,vn,vn,l.pval016) p16,
456 l.pval016 d16,
457 decode(l.pval017,cn,vn,vn,vn,l.pval017) p17,
458 l.pval017 d17,
459 decode(l.pval018,cn,vn,vn,vn,l.pval018) p18,
460 l.pval018 d18,
461 decode(l.pval019,cn,vn,vn,vn,l.pval019) p19,
462 l.pval019 d19,
463 decode(l.pval020,cn,dn,vn,dn,d(l.pval020)) p20,
464 l.pval020 d20,
465 decode(l.pval021,cn,vn,vn,vn,l.pval021) p21,
466 l.pval021 d21,
467 decode(l.pval022,cn,vn,vn,vn,
468  hr_pump_get.gl(l.pval022,'MAR_STATUS',d(l.pval001),l.pval112)) p22,
469 l.pval022 d22,
470 decode(l.pval023,cn,vn,vn,vn,l.pval023) p23,
471 l.pval023 d23,
472 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
473 l.pval024 d24,
474 decode(l.pval025,cn,vn,vn,vn,
475  hr_pump_get.gl(l.pval025,'NATIONALITY',d(l.pval001),l.pval112)) p25,
476 l.pval025 d25,
477 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
478 l.pval026 d26,
479 decode(l.pval027,cn,vn,vn,vn,l.pval027) p27,
480 l.pval027 d27,
481 decode(l.pval028,cn,nn,vn,nn,n(l.pval028)) p28,
482 l.pval028 d28,
483 decode(l.pval029,cn,vn,vn,vn,l.pval029) p29,
484 l.pval029 d29,
485 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
486 l.pval030 d30,
487 decode(l.pval031,cn,dn,vn,dn,d(l.pval031)) p31,
488 l.pval031 d31,
489 decode(l.pval032,cn,dn,vn,dn,d(l.pval032)) p32,
490 l.pval032 d32,
491 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
492 l.pval033 d33,
493 decode(l.pval034,cn,vn,vn,vn,
494  hr_pump_get.gl(l.pval034,'YES_NO',d(l.pval001),l.pval112)) p34,
495 l.pval034 d34,
496 decode(l.pval035,cn,vn,vn,vn,
497  hr_pump_get.gl(l.pval035,'YES_NO',d(l.pval001),l.pval112)) p35,
498 l.pval035 d35,
499 decode(l.pval036,cn,dn,vn,dn,d(l.pval036)) p36,
500 l.pval036 d36,
501 decode(l.pval037,cn,vn,vn,vn,l.pval037) p37,
502 l.pval037 d37,
503 decode(l.pval038,cn,vn,vn,vn,
504  hr_pump_get.gl(l.pval038,'SEX',d(l.pval001),l.pval112)) p38,
505 l.pval038 d38,
506 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
507 l.pval039 d39,
508 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
509 l.pval040 d40,
510 decode(l.pval041,cn,vn,vn,vn,
511  hr_pump_get.gl(l.pval041,'TITLE',d(l.pval001),l.pval112)) p41,
512 l.pval041 d41,
513 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
514 l.pval042 d42,
515 decode(l.pval043,cn,vn,vn,vn,l.pval043) p43,
516 l.pval043 d43,
517 decode(l.pval044,cn,vn,vn,vn,
518  hr_pump_get.gl(l.pval044,'WORK_SCHEDULE',d(l.pval001),l.pval112)) p44,
519 l.pval044 d44,
520 decode(l.pval045,cn,vn,vn,vn,l.pval045) p45,
521 l.pval045 d45,
522 decode(l.pval046,cn,vn,vn,vn,l.pval046) p46,
523 l.pval046 d46,
524 decode(l.pval047,cn,dn,vn,dn,d(l.pval047)) p47,
525 l.pval047 d47,
526 decode(l.pval048,cn,dn,vn,dn,d(l.pval048)) p48,
527 l.pval048 d48,
528 decode(l.pval049,cn,vn,vn,vn,l.pval049) p49,
529 l.pval049 d49,
530 decode(l.pval050,cn,vn,vn,'N',
531  hr_pump_get.gl(l.pval050,'YES_NO',d(l.pval001),l.pval112)) p50,
532 l.pval050 d50,
533 decode(l.pval051,cn,dn,vn,dn,d(l.pval051)) p51,
534 l.pval051 d51,
535 decode(l.pval052,cn,vn,vn,vn,l.pval052) p52,
536 l.pval052 d52,
537 decode(l.pval053,cn,vn,vn,vn,l.pval053) p53,
538 l.pval053 d53,
539 decode(l.pval054,cn,vn,vn,vn,l.pval054) p54,
540 l.pval054 d54,
541 decode(l.pval055,cn,vn,vn,vn,l.pval055) p55,
542 l.pval055 d55,
543 decode(l.pval056,cn,vn,vn,vn,l.pval056) p56,
544 l.pval056 d56,
545 decode(l.pval057,cn,vn,vn,vn,l.pval057) p57,
546 l.pval057 d57,
547 decode(l.pval058,cn,vn,vn,vn,l.pval058) p58,
548 l.pval058 d58,
549 decode(l.pval059,cn,vn,vn,vn,l.pval059) p59,
550 l.pval059 d59,
551 decode(l.pval060,cn,vn,vn,vn,l.pval060) p60,
552 l.pval060 d60,
553 decode(l.pval061,cn,vn,vn,vn,l.pval061) p61,
554 l.pval061 d61,
555 decode(l.pval062,cn,vn,vn,vn,l.pval062) p62,
556 l.pval062 d62,
557 decode(l.pval063,cn,vn,vn,vn,l.pval063) p63,
558 l.pval063 d63,
559 decode(l.pval064,cn,vn,vn,vn,l.pval064) p64,
560 l.pval064 d64,
561 decode(l.pval065,cn,vn,vn,vn,l.pval065) p65,
562 l.pval065 d65,
563 decode(l.pval066,cn,vn,vn,vn,l.pval066) p66,
564 l.pval066 d66,
565 decode(l.pval067,cn,vn,vn,vn,l.pval067) p67,
566 l.pval067 d67,
567 decode(l.pval068,cn,vn,vn,vn,l.pval068) p68,
568 l.pval068 d68,
569 decode(l.pval069,cn,vn,vn,vn,l.pval069) p69,
570 l.pval069 d69,
571 decode(l.pval070,cn,vn,vn,vn,l.pval070) p70,
572 l.pval070 d70,
573 decode(l.pval071,cn,vn,vn,vn,l.pval071) p71,
574 l.pval071 d71,
575 decode(l.pval072,cn,vn,vn,vn,l.pval072) p72,
576 l.pval072 d72,
577 decode(l.pval073,cn,vn,vn,vn,l.pval073) p73,
578 l.pval073 d73,
579 decode(l.pval074,cn,vn,vn,vn,l.pval074) p74,
580 l.pval074 d74,
581 decode(l.pval075,cn,vn,vn,vn,l.pval075) p75,
582 l.pval075 d75,
583 decode(l.pval076,cn,vn,vn,vn,l.pval076) p76,
584 l.pval076 d76,
585 decode(l.pval077,cn,vn,vn,vn,l.pval077) p77,
586 l.pval077 d77,
587 decode(l.pval078,cn,vn,vn,vn,l.pval078) p78,
588 l.pval078 d78,
589 decode(l.pval079,cn,vn,vn,vn,l.pval079) p79,
590 l.pval079 d79,
591 decode(l.pval080,cn,vn,vn,vn,l.pval080) p80,
592 l.pval080 d80,
593 decode(l.pval081,cn,vn,vn,vn,l.pval081) p81,
594 l.pval081 d81,
595 decode(l.pval082,cn,vn,vn,vn,l.pval082) p82,
596 l.pval082 d82,
600 decode(l.pval084,cn,vn,vn,vn,l.pval084) p84,
597 decode(l.pval083,cn,vn,vn,vn,
598  hr_pump_get.gl(l.pval083,'RU_OKATO',d(l.pval001),l.pval112)) p83,
599 l.pval083 d83,
601 l.pval084 d84,
602 decode(l.pval085,cn,vn,vn,vn,l.pval085) p85,
603 l.pval085 d85,
604 decode(l.pval086,cn,vn,
605  hr_pump_get.gl(l.pval086,'RU_CITIZENSHIP',d(l.pval001),l.pval112)) p86,
606 decode(l.pval087,cn,vn,vn,vn,
607  hr_pump_get.gl(l.pval087,'RU_MILITARY_DOC_TYPE',d(l.pval001),l.pval112)) p87,
608 l.pval087 d87,
609 decode(l.pval088,cn,vn,vn,vn,
610  hr_pump_get.gl(l.pval088,'RU_RESERVE_CATEGORY',d(l.pval001),l.pval112)) p88,
611 l.pval088 d88,
612 decode(l.pval089,cn,vn,vn,vn,
613  hr_pump_get.gl(l.pval089,'RU_MILITARY_RANK',d(l.pval001),l.pval112)) p89,
614 l.pval089 d89,
615 decode(l.pval090,cn,vn,vn,vn,
616  hr_pump_get.gl(l.pval090,'RU_MILITARY_PROFILE',d(l.pval001),l.pval112)) p90,
617 l.pval090 d90,
618 decode(l.pval091,cn,vn,vn,vn,l.pval091) p91,
619 l.pval091 d91,
620 decode(l.pval092,cn,vn,vn,vn,
621  hr_pump_get.gl(l.pval092,'RU_MILITARY_SERVICE_READINESS_CATEGORY',d(l.pval001),l.pval112)) p92,
622 l.pval092 d92,
623 decode(l.pval093,cn,vn,vn,vn,l.pval093) p93,
624 l.pval093 d93,
625 decode(l.pval094,cn,vn,vn,vn,
626  hr_pump_get.gl(l.pval094,'RU_QUITTING_MARK',d(l.pval001),l.pval112)) p94,
627 l.pval094 d94,
628 decode(l.pval095,cn,vn,vn,vn,l.pval095) p95,
629 l.pval095 d95,
630 decode(l.pval096,cn,vn,vn,vn,l.pval096) p96,
631 l.pval096 d96,
632 decode(l.pval097,cn,vn,vn,vn,l.pval097) p97,
633 l.pval097 d97,
634 decode(l.pval098,cn,vn,vn,vn,l.pval098) p98,
635 l.pval098 d98,
636 l.pval099 p99,
637 l.pval100 p100,
638 decode(l.pval101,cn,dn,d(l.pval101)) p101,
639 decode(l.pval102,cn,dn,d(l.pval102)) p102,
640 l.pval103 p103,
641 l.pval104 p104,
642 l.pval105 p105,
643 l.pval106 p106,
644 l.pval107 p107,
645 l.pval108 p108,
646 l.pval109 p109,
647 l.pval110 p110,
648 decode(l.pval111,cn,vn,vn,vn,l.pval111) p111,
649 l.pval111 d111,
650 decode(l.pval112,cn,vn,vn,vn,l.pval112) p112,
651 l.pval112 d112,
652 decode(l.pval113,cn,vn,vn,vn,l.pval113) p113,
653 l.pval113 d113,
654 decode(l.pval114,cn,vn,vn,vn,l.pval114) p114,
655 l.pval114 d114,
656 decode(l.pval115,cn,vn,vn,vn,l.pval115) p115,
657 l.pval115 d115
658 from hr_pump_batch_lines l
659 where l.batch_line_id = p_batch_line_id;
660 --
661 c cr%rowtype;
662 l_validate boolean := false;
663 L_PERSON_ID number;
664 L_ASSIGNMENT_ID number;
665 L_NAME_COMBINATION_WARNING boolean;
666 L_PERSON_TYPE_ID number;
667 L_CORRESPONDENCE_LANGUAGE varchar2(2000);
668 L_VENDOR_ID number;
669 L_BENEFIT_GROUP_ID number;
670 --
671 begin
672 hr_data_pump.entry('call');
673 open cr;
674 fetch cr into c;
675 if cr%notfound then
676 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
677 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
678 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
679 hr_utility.set_message_token('VALUE',p_batch_line_id);
680 hr_utility.raise_error;
681 end if;
682 --
683 if c.p111 is null or
684 c.p112 is null then
685 L_PERSON_TYPE_ID:=nn;
686 else
687 L_PERSON_TYPE_ID := 
688 hr_pump_get.get_person_type_id
689 (P_USER_PERSON_TYPE => c.p111
690 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
691 ,P_LANGUAGE_CODE => c.p112);
692 end if;
693 --
694 if c.p113 is null then
695 L_CORRESPONDENCE_LANGUAGE:=vn;
696 else
697 L_CORRESPONDENCE_LANGUAGE := 
698 hr_pump_get.GET_CORRESPONDENCE_LANGUAGE
699 (P_CORRESPONDENCE_LANGUAGE => c.p113);
700 end if;
701 --
702 if c.p114 is null then
703 L_VENDOR_ID:=nn;
704 else
705 L_VENDOR_ID := 
706 hr_pump_get.get_vendor_id
707 (P_VENDOR_NAME => c.p114);
708 end if;
709 --
710 if c.p115 is null then
711 L_BENEFIT_GROUP_ID:=nn;
712 else
713 L_BENEFIT_GROUP_ID := 
714 hr_pump_get.get_benefit_group_id
715 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
716 ,P_BENEFIT_GROUP => c.p115);
717 end if;
718 --
719 hr_data_pump.api_trc_on;
720 HR_RU_CONTINGENT_WORKER_API.CREATE_RU_CWK
721 (p_validate => l_validate
722 ,P_START_DATE => c.p1
723 ,p_business_group_id => p_business_group_id
724 ,P_LAST_NAME => c.p2
725 ,P_PERSON_TYPE_ID => L_PERSON_TYPE_ID
726 ,P_NPW_NUMBER => c.p3
727 ,P_BACKGROUND_CHECK_STATUS => c.p4
728 ,P_BACKGROUND_DATE_CHECK => c.p5
729 ,P_BLOOD_TYPE => c.p6
730 ,P_COMMENTS => c.p7
731 ,P_CORRESPONDENCE_LANGUAGE => L_CORRESPONDENCE_LANGUAGE
732 ,P_COUNTRY_OF_BIRTH => c.p8
733 ,P_DATE_OF_BIRTH => c.p9
734 ,P_DATE_OF_DEATH => c.p10
735 ,P_DPDNT_ADOPTION_DATE => c.p11
736 ,P_DPDNT_VLNTRY_SVCE_FLAG => c.p12
737 ,P_EMAIL_ADDRESS => c.p13
738 ,P_FIRST_NAME => c.p14
739 ,P_FTE_CAPACITY => c.p15
740 ,P_HONORS => c.p16
741 ,P_INTERNAL_LOCATION => c.p17
742 ,P_KNOWN_AS => c.p18
743 ,P_LAST_MEDICAL_TEST_BY => c.p19
744 ,P_LAST_MEDICAL_TEST_DATE => c.p20
745 ,P_MAILSTOP => c.p21
746 ,P_MARITAL_STATUS => c.p22
747 ,P_MIDDLE_NAMES => c.p23
748 ,P_INN => c.p24
749 ,P_NATIONALITY => c.p25
750 ,P_OFFICE_NUMBER => c.p26
751 ,P_ON_MILITARY_SERVICE => c.p27
752 ,P_PARTY_ID => c.p28
753 ,P_GENITIVE_LAST_NAME => c.p29
754 ,P_PREVIOUS_LAST_NAME => c.p30
755 ,P_PROJECTED_PLACEMENT_END => c.p31
756 ,P_RECEIPT_OF_DEATH_CERT_DATE => c.p32
757 ,P_REGION_OF_BIRTH => c.p33
758 ,P_REGISTERED_DISABLED_FLAG => c.p34
759 ,P_RESUME_EXISTS => c.p35
760 ,P_RESUME_LAST_UPDATED => c.p36
761 ,P_SECOND_PASSPORT_EXISTS => c.p37
762 ,P_SEX => c.p38
763 ,P_STUDENT_STATUS => c.p39
764 ,P_SUFFIX => c.p40
765 ,P_TITLE => c.p41
766 ,P_TOWN_OF_BIRTH => c.p42
770 ,P_WORK_TELEPHONE => c.p45
767 ,P_USES_TOBACCO_FLAG => c.p43
768 ,P_VENDOR_ID => L_VENDOR_ID
769 ,P_WORK_SCHEDULE => c.p44
771 ,P_EXP_CHECK_SEND_TO_ADDRESS => c.p46
772 ,P_HOLD_APPLICANT_DATE_UNTIL => c.p47
773 ,P_DATE_EMPLOYEE_DATA_VERIFIED => c.p48
774 ,P_BENEFIT_GROUP_ID => L_BENEFIT_GROUP_ID
775 ,P_COORD_BEN_MED_PLN_NO => c.p49
776 ,P_COORD_BEN_NO_CVG_FLAG => c.p50
777 ,P_ORIGINAL_DATE_OF_HIRE => c.p51
778 ,P_ATTRIBUTE_CATEGORY => c.p52
779 ,P_ATTRIBUTE1 => c.p53
780 ,P_ATTRIBUTE2 => c.p54
781 ,P_ATTRIBUTE3 => c.p55
782 ,P_ATTRIBUTE4 => c.p56
783 ,P_ATTRIBUTE5 => c.p57
784 ,P_ATTRIBUTE6 => c.p58
785 ,P_ATTRIBUTE7 => c.p59
786 ,P_ATTRIBUTE8 => c.p60
787 ,P_ATTRIBUTE9 => c.p61
788 ,P_ATTRIBUTE10 => c.p62
789 ,P_ATTRIBUTE11 => c.p63
790 ,P_ATTRIBUTE12 => c.p64
791 ,P_ATTRIBUTE13 => c.p65
792 ,P_ATTRIBUTE14 => c.p66
793 ,P_ATTRIBUTE15 => c.p67
794 ,P_ATTRIBUTE16 => c.p68
795 ,P_ATTRIBUTE17 => c.p69
796 ,P_ATTRIBUTE18 => c.p70
797 ,P_ATTRIBUTE19 => c.p71
798 ,P_ATTRIBUTE20 => c.p72
799 ,P_ATTRIBUTE21 => c.p73
800 ,P_ATTRIBUTE22 => c.p74
801 ,P_ATTRIBUTE23 => c.p75
802 ,P_ATTRIBUTE24 => c.p76
803 ,P_ATTRIBUTE25 => c.p77
804 ,P_ATTRIBUTE26 => c.p78
805 ,P_ATTRIBUTE27 => c.p79
806 ,P_ATTRIBUTE28 => c.p80
807 ,P_ATTRIBUTE29 => c.p81
808 ,P_ATTRIBUTE30 => c.p82
809 ,P_PLACE_OF_BIRTH => c.p83
810 ,P_REFERENCES => c.p84
811 ,P_LOCAL_COEFFICIENT => c.p85
812 ,P_CITIZENSHIP => c.p86
813 ,P_MILITARY_DOC => c.p87
814 ,P_RESERVE_CATEGORY => c.p88
815 ,P_MILITARY_RANK => c.p89
816 ,P_MILITARY_PROFILE => c.p90
817 ,P_MILITARY_REG_CODE => c.p91
818 ,P_MIL_SRVC_READINESS_CATEGORY => c.p92
819 ,P_MILITARY_COMMISSARIAT => c.p93
820 ,P_QUITTING_MARK => c.p94
821 ,P_MILITARY_UNIT_NUMBER => c.p95
822 ,P_MILITARY_REG_TYPE => c.p96
823 ,P_MILITARY_REG_DETAILS => c.p97
824 ,P_PENSION_FUND_NUMBER => c.p98
825 ,P_PERSON_ID => L_PERSON_ID
826 ,P_PER_OBJECT_VERSION_NUMBER => c.p100
827 ,P_PER_EFFECTIVE_START_DATE => c.p101
828 ,P_PER_EFFECTIVE_END_DATE => c.p102
829 ,P_PDP_OBJECT_VERSION_NUMBER => c.p103
830 ,P_FULL_NAME => c.p104
831 ,P_COMMENT_ID => c.p105
832 ,P_ASSIGNMENT_ID => L_ASSIGNMENT_ID
833 ,P_ASG_OBJECT_VERSION_NUMBER => c.p107
834 ,P_ASSIGNMENT_SEQUENCE => c.p108
835 ,P_ASSIGNMENT_NUMBER => c.p109
836 ,P_NAME_COMBINATION_WARNING => L_NAME_COMBINATION_WARNING);
837 hr_data_pump.api_trc_off;
838 --
839 iuk(p_batch_line_id,c.p99,L_PERSON_ID);
840 --
841 iuk(p_batch_line_id,c.p106,L_ASSIGNMENT_ID);
842 --
843 if L_NAME_COMBINATION_WARNING then
844 c.p110 := 'TRUE';
845 else
846 c.p110 := 'FALSE';
847 end if;
848 --
849 update hr_pump_batch_lines l set
850 l.pval003 = decode(c.p3,null,cn,c.p3),
851 l.pval099 = decode(c.p99,null,cn,c.p99),
852 l.pval100 = decode(c.p100,null,cn,c.p100),
853 l.pval101 = decode(c.p101,null,cn,dc(c.p101)),
854 l.pval102 = decode(c.p102,null,cn,dc(c.p102)),
855 l.pval103 = decode(c.p103,null,cn,c.p103),
856 l.pval104 = decode(c.p104,null,cn,c.p104),
857 l.pval105 = decode(c.p105,null,cn,c.p105),
858 l.pval106 = decode(c.p106,null,cn,c.p106),
859 l.pval107 = decode(c.p107,null,cn,c.p107),
860 l.pval108 = decode(c.p108,null,cn,c.p108),
861 l.pval109 = decode(c.p109,null,cn,c.p109),
862 l.pval110 = decode(c.p110,null,cn,c.p110)
863 where l.rowid = c.myrowid;
864 --
865 close cr;
866 --
867 hr_data_pump.exit('call');
868 exception
869  when hr_multi_message.error_message_exist then
870    if cr%isopen then
871     close cr;
872    end if;
873    hr_pump_utils.set_multi_msg_error_flag(true);
874  when others then
875  if cr%isopen then
876   close cr;
877  end if;
878  raise;
879 end call;
880 end hrdpp_CREATE_RU_CWK;