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_SIT

Source


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