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_POSITION_REQUIREM

Source


1 package body hrdpp_CREATE_POSITION_REQUIREM as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2007/01/04 02:01:52
4  * Generated for API: HR_POSITION_REQUIREMENT_API.CREATE_POSITION_REQUIREMENT
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_COMMENTS in varchar2 default null
83 ,P_ESSENTIAL in varchar2 default null
84 ,P_ATTRIBUTE_CATEGORY in varchar2 default null
85 ,P_ATTRIBUTE1 in varchar2 default null
86 ,P_ATTRIBUTE2 in varchar2 default null
87 ,P_ATTRIBUTE3 in varchar2 default null
88 ,P_ATTRIBUTE4 in varchar2 default null
89 ,P_ATTRIBUTE5 in varchar2 default null
90 ,P_ATTRIBUTE6 in varchar2 default null
91 ,P_ATTRIBUTE7 in varchar2 default null
92 ,P_ATTRIBUTE8 in varchar2 default null
93 ,P_ATTRIBUTE9 in varchar2 default null
94 ,P_ATTRIBUTE10 in varchar2 default null
95 ,P_ATTRIBUTE11 in varchar2 default null
96 ,P_ATTRIBUTE12 in varchar2 default null
97 ,P_ATTRIBUTE13 in varchar2 default null
98 ,P_ATTRIBUTE14 in varchar2 default null
99 ,P_ATTRIBUTE15 in varchar2 default null
100 ,P_ATTRIBUTE16 in varchar2 default null
101 ,P_ATTRIBUTE17 in varchar2 default null
102 ,P_ATTRIBUTE18 in varchar2 default null
103 ,P_ATTRIBUTE19 in varchar2 default null
104 ,P_ATTRIBUTE20 in varchar2 default null
105 ,P_SEGMENT1 in varchar2 default null
106 ,P_SEGMENT2 in varchar2 default null
107 ,P_SEGMENT3 in varchar2 default null
108 ,P_SEGMENT4 in varchar2 default null
109 ,P_SEGMENT5 in varchar2 default null
110 ,P_SEGMENT6 in varchar2 default null
111 ,P_SEGMENT7 in varchar2 default null
112 ,P_SEGMENT8 in varchar2 default null
113 ,P_SEGMENT9 in varchar2 default null
114 ,P_SEGMENT10 in varchar2 default null
115 ,P_SEGMENT11 in varchar2 default null
116 ,P_SEGMENT12 in varchar2 default null
117 ,P_SEGMENT13 in varchar2 default null
118 ,P_SEGMENT14 in varchar2 default null
119 ,P_SEGMENT15 in varchar2 default null
120 ,P_SEGMENT16 in varchar2 default null
121 ,P_SEGMENT17 in varchar2 default null
122 ,P_SEGMENT18 in varchar2 default null
123 ,P_SEGMENT19 in varchar2 default null
124 ,P_SEGMENT20 in varchar2 default null
125 ,P_SEGMENT21 in varchar2 default null
126 ,P_SEGMENT22 in varchar2 default null
127 ,P_SEGMENT23 in varchar2 default null
128 ,P_SEGMENT24 in varchar2 default null
129 ,P_SEGMENT25 in varchar2 default null
130 ,P_SEGMENT26 in varchar2 default null
131 ,P_SEGMENT27 in varchar2 default null
132 ,P_SEGMENT28 in varchar2 default null
133 ,P_SEGMENT29 in varchar2 default null
134 ,P_SEGMENT30 in varchar2 default null
135 ,P_CONCAT_SEGMENTS in varchar2 default null
136 ,P_JOB_REQUIREMENT_USER_KEY in varchar2
137 ,P_ANALYSIS_CRITERIA_ID in number
138 ,P_ID_FLEX_NUM_USER_KEY in varchar2
139 ,P_POSITION_NAME in varchar2
140 ,P_EFFECTIVE_DATE in date) is
141 blid number := p_data_pump_batch_line_id;
142 begin
143 if blid is not null then
144 delete from hr_pump_batch_lines where batch_line_id = blid;
145 delete from hr_pump_batch_exceptions
146 where source_type = 'BATCH_LINE' and source_id = blid;
147 end if;
148 insert into hr_pump_batch_lines
149 (batch_id
150 ,batch_line_id
151 ,business_group_name
152 ,api_module_id
153 ,line_status
154 ,user_sequence
155 ,link_value
156 ,pval001
157 ,pval002
158 ,pval003
159 ,pval004
160 ,pval005
161 ,pval006
162 ,pval007
163 ,pval008
164 ,pval009
165 ,pval010
166 ,pval011
167 ,pval012
168 ,pval013
169 ,pval014
170 ,pval015
171 ,pval016
172 ,pval017
173 ,pval018
174 ,pval019
175 ,pval020
176 ,pval021
177 ,pval022
178 ,pval023
179 ,pval024
180 ,pval025
181 ,pval026
182 ,pval027
183 ,pval028
184 ,pval029
185 ,pval030
186 ,pval031
187 ,pval032
188 ,pval033
189 ,pval034
190 ,pval035
191 ,pval036
192 ,pval037
193 ,pval038
194 ,pval039
195 ,pval040
196 ,pval041
197 ,pval042
198 ,pval043
199 ,pval044
200 ,pval045
201 ,pval046
202 ,pval047
203 ,pval048
204 ,pval049
205 ,pval050
206 ,pval051
207 ,pval052
208 ,pval053
209 ,pval054
210 ,pval055
211 ,pval057
212 ,pval058
213 ,pval059
214 ,pval060)
215 values
216 (p_batch_id
217 ,nvl(blid,hr_pump_batch_lines_s.nextval)
218 ,p_data_pump_business_grp_name
219 ,1220
220 ,'U'
221 ,p_user_sequence
222 ,p_link_value
223 ,P_COMMENTS
224 ,P_ESSENTIAL
225 ,P_ATTRIBUTE_CATEGORY
226 ,P_ATTRIBUTE1
227 ,P_ATTRIBUTE2
228 ,P_ATTRIBUTE3
229 ,P_ATTRIBUTE4
230 ,P_ATTRIBUTE5
231 ,P_ATTRIBUTE6
232 ,P_ATTRIBUTE7
233 ,P_ATTRIBUTE8
234 ,P_ATTRIBUTE9
235 ,P_ATTRIBUTE10
236 ,P_ATTRIBUTE11
237 ,P_ATTRIBUTE12
238 ,P_ATTRIBUTE13
239 ,P_ATTRIBUTE14
240 ,P_ATTRIBUTE15
241 ,P_ATTRIBUTE16
242 ,P_ATTRIBUTE17
243 ,P_ATTRIBUTE18
244 ,P_ATTRIBUTE19
245 ,P_ATTRIBUTE20
246 ,P_SEGMENT1
247 ,P_SEGMENT2
248 ,P_SEGMENT3
249 ,P_SEGMENT4
250 ,P_SEGMENT5
251 ,P_SEGMENT6
252 ,P_SEGMENT7
253 ,P_SEGMENT8
254 ,P_SEGMENT9
255 ,P_SEGMENT10
256 ,P_SEGMENT11
257 ,P_SEGMENT12
258 ,P_SEGMENT13
259 ,P_SEGMENT14
260 ,P_SEGMENT15
261 ,P_SEGMENT16
262 ,P_SEGMENT17
263 ,P_SEGMENT18
264 ,P_SEGMENT19
265 ,P_SEGMENT20
266 ,P_SEGMENT21
267 ,P_SEGMENT22
268 ,P_SEGMENT23
269 ,P_SEGMENT24
270 ,P_SEGMENT25
271 ,P_SEGMENT26
272 ,P_SEGMENT27
273 ,P_SEGMENT28
274 ,P_SEGMENT29
275 ,P_SEGMENT30
276 ,P_CONCAT_SEGMENTS
277 ,P_JOB_REQUIREMENT_USER_KEY
278 ,P_ANALYSIS_CRITERIA_ID
279 ,P_ID_FLEX_NUM_USER_KEY
280 ,P_POSITION_NAME
281 ,dc(P_EFFECTIVE_DATE));
282 end insert_batch_lines;
283 --
284 procedure call
285 (p_business_group_id in number,
286 p_batch_line_id     in number) is
287 cursor cr is
288 select l.rowid myrowid,
289 decode(l.pval001,cn,vn,vn,vn,l.pval001) p1,
290 l.pval001 d1,
291 decode(l.pval002,cn,vn,vn,'N',
292  hr_pump_get.gl(l.pval002,'YES_NO',d(l.pval060),vn)) p2,
293 l.pval002 d2,
294 decode(l.pval003,cn,vn,vn,vn,l.pval003) p3,
295 l.pval003 d3,
296 decode(l.pval004,cn,vn,vn,vn,l.pval004) p4,
297 l.pval004 d4,
298 decode(l.pval005,cn,vn,vn,vn,l.pval005) p5,
299 l.pval005 d5,
300 decode(l.pval006,cn,vn,vn,vn,l.pval006) p6,
301 l.pval006 d6,
302 decode(l.pval007,cn,vn,vn,vn,l.pval007) p7,
303 l.pval007 d7,
304 decode(l.pval008,cn,vn,vn,vn,l.pval008) p8,
305 l.pval008 d8,
306 decode(l.pval009,cn,vn,vn,vn,l.pval009) p9,
307 l.pval009 d9,
308 decode(l.pval010,cn,vn,vn,vn,l.pval010) p10,
309 l.pval010 d10,
310 decode(l.pval011,cn,vn,vn,vn,l.pval011) p11,
311 l.pval011 d11,
312 decode(l.pval012,cn,vn,vn,vn,l.pval012) p12,
313 l.pval012 d12,
314 decode(l.pval013,cn,vn,vn,vn,l.pval013) p13,
315 l.pval013 d13,
316 decode(l.pval014,cn,vn,vn,vn,l.pval014) p14,
317 l.pval014 d14,
318 decode(l.pval015,cn,vn,vn,vn,l.pval015) p15,
319 l.pval015 d15,
320 decode(l.pval016,cn,vn,vn,vn,l.pval016) p16,
321 l.pval016 d16,
322 decode(l.pval017,cn,vn,vn,vn,l.pval017) p17,
323 l.pval017 d17,
324 decode(l.pval018,cn,vn,vn,vn,l.pval018) p18,
325 l.pval018 d18,
326 decode(l.pval019,cn,vn,vn,vn,l.pval019) p19,
327 l.pval019 d19,
328 decode(l.pval020,cn,vn,vn,vn,l.pval020) p20,
329 l.pval020 d20,
330 decode(l.pval021,cn,vn,vn,vn,l.pval021) p21,
331 l.pval021 d21,
332 decode(l.pval022,cn,vn,vn,vn,l.pval022) p22,
333 l.pval022 d22,
334 decode(l.pval023,cn,vn,vn,vn,l.pval023) p23,
335 l.pval023 d23,
336 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
337 l.pval024 d24,
338 decode(l.pval025,cn,vn,vn,vn,l.pval025) p25,
339 l.pval025 d25,
340 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
341 l.pval026 d26,
342 decode(l.pval027,cn,vn,vn,vn,l.pval027) p27,
343 l.pval027 d27,
344 decode(l.pval028,cn,vn,vn,vn,l.pval028) p28,
345 l.pval028 d28,
346 decode(l.pval029,cn,vn,vn,vn,l.pval029) p29,
347 l.pval029 d29,
348 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
349 l.pval030 d30,
350 decode(l.pval031,cn,vn,vn,vn,l.pval031) p31,
351 l.pval031 d31,
352 decode(l.pval032,cn,vn,vn,vn,l.pval032) p32,
353 l.pval032 d32,
354 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
355 l.pval033 d33,
356 decode(l.pval034,cn,vn,vn,vn,l.pval034) p34,
357 l.pval034 d34,
358 decode(l.pval035,cn,vn,vn,vn,l.pval035) p35,
359 l.pval035 d35,
360 decode(l.pval036,cn,vn,vn,vn,l.pval036) p36,
361 l.pval036 d36,
362 decode(l.pval037,cn,vn,vn,vn,l.pval037) p37,
363 l.pval037 d37,
364 decode(l.pval038,cn,vn,vn,vn,l.pval038) p38,
365 l.pval038 d38,
366 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
367 l.pval039 d39,
368 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
369 l.pval040 d40,
370 decode(l.pval041,cn,vn,vn,vn,l.pval041) p41,
371 l.pval041 d41,
372 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
373 l.pval042 d42,
374 decode(l.pval043,cn,vn,vn,vn,l.pval043) p43,
375 l.pval043 d43,
376 decode(l.pval044,cn,vn,vn,vn,l.pval044) p44,
377 l.pval044 d44,
378 decode(l.pval045,cn,vn,vn,vn,l.pval045) p45,
379 l.pval045 d45,
380 decode(l.pval046,cn,vn,vn,vn,l.pval046) p46,
381 l.pval046 d46,
382 decode(l.pval047,cn,vn,vn,vn,l.pval047) p47,
383 l.pval047 d47,
384 decode(l.pval048,cn,vn,vn,vn,l.pval048) p48,
385 l.pval048 d48,
386 decode(l.pval049,cn,vn,vn,vn,l.pval049) p49,
387 l.pval049 d49,
388 decode(l.pval050,cn,vn,vn,vn,l.pval050) p50,
389 l.pval050 d50,
390 decode(l.pval051,cn,vn,vn,vn,l.pval051) p51,
391 l.pval051 d51,
392 decode(l.pval052,cn,vn,vn,vn,l.pval052) p52,
393 l.pval052 d52,
394 decode(l.pval053,cn,vn,vn,vn,l.pval053) p53,
395 l.pval053 d53,
396 decode(l.pval054,cn,vn,vn,vn,l.pval054) p54,
397 l.pval054 d54,
398 l.pval055 p55,
399 l.pval056 p56,
400 decode(l.pval057,cn,nn,n(l.pval057)) p57,
401 decode(l.pval058,cn,vn,l.pval058) p58,
402 decode(l.pval059,cn,vn,l.pval059) p59,
403 decode(l.pval060,cn,dn,d(l.pval060)) p60
404 from hr_pump_batch_lines l
405 where l.batch_line_id = p_batch_line_id;
406 --
407 c cr%rowtype;
408 l_validate boolean := false;
409 L_JOB_REQUIREMENT_ID number;
410 L_ID_FLEX_NUM number;
411 L_POSITION_ID number;
412 --
413 begin
414 hr_data_pump.entry('call');
415 open cr;
416 fetch cr into c;
417 if cr%notfound then
418 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
419 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
420 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
421 hr_utility.set_message_token('VALUE',p_batch_line_id);
422 hr_utility.raise_error;
423 end if;
424 --
425 if c.p58 is null then
426 L_ID_FLEX_NUM:=nn;
427 else
428 L_ID_FLEX_NUM := 
429 hr_pump_get.GET_ID_FLEX_NUM
430 (P_ID_FLEX_NUM_USER_KEY => c.p58);
431 end if;
432 --
433 if c.p59 is null or
434 c.p60 is null then
435 L_POSITION_ID:=nn;
436 else
437 L_POSITION_ID := 
438 hr_pump_get.get_position_id
439 (P_POSITION_NAME => c.p59
440 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
441 ,P_EFFECTIVE_DATE => c.p60);
442 end if;
443 --
444 hr_data_pump.api_trc_on;
445 HR_POSITION_REQUIREMENT_API.CREATE_POSITION_REQUIREMENT
446 (p_validate => l_validate
447 ,P_ID_FLEX_NUM => L_ID_FLEX_NUM
448 ,P_POSITION_ID => L_POSITION_ID
449 ,P_COMMENTS => c.p1
450 ,P_ESSENTIAL => c.p2
451 ,P_ATTRIBUTE_CATEGORY => c.p3
452 ,P_ATTRIBUTE1 => c.p4
453 ,P_ATTRIBUTE2 => c.p5
454 ,P_ATTRIBUTE3 => c.p6
455 ,P_ATTRIBUTE4 => c.p7
456 ,P_ATTRIBUTE5 => c.p8
457 ,P_ATTRIBUTE6 => c.p9
458 ,P_ATTRIBUTE7 => c.p10
459 ,P_ATTRIBUTE8 => c.p11
460 ,P_ATTRIBUTE9 => c.p12
461 ,P_ATTRIBUTE10 => c.p13
462 ,P_ATTRIBUTE11 => c.p14
463 ,P_ATTRIBUTE12 => c.p15
464 ,P_ATTRIBUTE13 => c.p16
465 ,P_ATTRIBUTE14 => c.p17
466 ,P_ATTRIBUTE15 => c.p18
467 ,P_ATTRIBUTE16 => c.p19
468 ,P_ATTRIBUTE17 => c.p20
469 ,P_ATTRIBUTE18 => c.p21
470 ,P_ATTRIBUTE19 => c.p22
471 ,P_ATTRIBUTE20 => c.p23
472 ,P_SEGMENT1 => c.p24
473 ,P_SEGMENT2 => c.p25
474 ,P_SEGMENT3 => c.p26
475 ,P_SEGMENT4 => c.p27
476 ,P_SEGMENT5 => c.p28
477 ,P_SEGMENT6 => c.p29
478 ,P_SEGMENT7 => c.p30
479 ,P_SEGMENT8 => c.p31
480 ,P_SEGMENT9 => c.p32
481 ,P_SEGMENT10 => c.p33
482 ,P_SEGMENT11 => c.p34
483 ,P_SEGMENT12 => c.p35
484 ,P_SEGMENT13 => c.p36
485 ,P_SEGMENT14 => c.p37
486 ,P_SEGMENT15 => c.p38
487 ,P_SEGMENT16 => c.p39
488 ,P_SEGMENT17 => c.p40
489 ,P_SEGMENT18 => c.p41
490 ,P_SEGMENT19 => c.p42
491 ,P_SEGMENT20 => c.p43
492 ,P_SEGMENT21 => c.p44
493 ,P_SEGMENT22 => c.p45
497 ,P_SEGMENT26 => c.p49
494 ,P_SEGMENT23 => c.p46
495 ,P_SEGMENT24 => c.p47
496 ,P_SEGMENT25 => c.p48
498 ,P_SEGMENT27 => c.p50
499 ,P_SEGMENT28 => c.p51
500 ,P_SEGMENT29 => c.p52
501 ,P_SEGMENT30 => c.p53
502 ,P_CONCAT_SEGMENTS => c.p54
503 ,P_JOB_REQUIREMENT_ID => L_JOB_REQUIREMENT_ID
504 ,P_OBJECT_VERSION_NUMBER => c.p56
505 ,P_ANALYSIS_CRITERIA_ID => c.p57);
506 hr_data_pump.api_trc_off;
507 --
508 iuk(p_batch_line_id,c.p55,L_JOB_REQUIREMENT_ID);
509 --
510 update hr_pump_batch_lines l set
511 l.pval055 = decode(c.p55,null,cn,c.p55),
512 l.pval056 = decode(c.p56,null,cn,c.p56),
513 l.pval057 = decode(c.p57,null,cn,c.p57)
514 where l.rowid = c.myrowid;
515 --
516 close cr;
517 --
518 hr_data_pump.exit('call');
519 exception
520  when hr_multi_message.error_message_exist then
521    if cr%isopen then
522     close cr;
523    end if;
524    hr_pump_utils.set_multi_msg_error_flag(true);
525  when others then
526  if cr%isopen then
527   close cr;
528  end if;
529  raise;
530 end call;
531 end hrdpp_CREATE_POSITION_REQUIREM;