[Home] [Help]
PACKAGE: APPS.PER_EMPDIR_SS
Source
1 PACKAGE PER_EMPDIR_SS AUTHID CURRENT_USER AS
2 /* $Header: peredrcp.pkh 120.2 2006/05/04 00:56 sspratur noship $ */
3
4 -- Global Variables
5 g_package CONSTANT VARCHAR2(30):='PER_EMPDIR_SS';
6 g_commit_size CONSTANT NUMBER:= 5000;
7 g_debug Boolean:= false;
8 g_trace Boolean:= false;
9 g_lang VARCHAR2(10):= userenv('LANG');
10 g_request_id NUMBER := nvl(fnd_global.conc_request_id,-1);
11 g_prog_appl_id NUMBER := nvl(fnd_global.prog_appl_id,-1);
12 g_prog_id NUMBER := nvl(fnd_global.conc_program_id,-1);
13 g_user_id NUMBER := nvl(fnd_global.user_id,-1);
14 g_login_id NUMBER := nvl(fnd_global.login_id, -1);
15 g_date DATE := trunc(SYSDATE);
16
17 -- Memory structures
18
19 TYPE RowIdTblType IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
20 TYPE NumberTblType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
21 TYPE VarChar10TblType IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
22 TYPE VarChar30TblType IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
23 TYPE VarChar45TblType IS TABLE OF VARCHAR2(45) INDEX BY BINARY_INTEGER;
24 TYPE VarChar60TblType IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;
25 TYPE VarChar80TblType IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
26 TYPE VarChar150TblType IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
27 TYPE VarChar240TblType IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
28 TYPE VarChar700TblType IS TABLE OF VARCHAR2(700) INDEX BY BINARY_INTEGER;
29 TYPE VarChar2000TblType IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
30 TYPE DateTblType IS TABLE OF DATE INDEX BY BINARY_INTEGER;
31
32 -- INDEX BY VARCHAR2 has 9i dependency
33
34 -- Record type definitions
35
36 TYPE PosTblType IS RECORD
37 (orig_system VarChar30TblType
38 ,orig_system_id NumberTblType
39 ,business_group_id NumberTblType
40 ,legislation_code VarChar30TblType
41 ,job_id NumberTblType
42 ,location_id NumberTblType
43 ,organization_id NumberTblType
44 ,position_definition_id NumberTblType
45 ,name VarChar240TblType
46 ,language VarChar30TblType
47 ,source_language VarChar30TblType
48 ,partition_id NumberTblType
49 ,object_version_number NumberTblType
50 ,attribute_category VarChar30TblType
51 ,attribute1 VarChar150TblType
52 ,attribute2 VarChar150TblType
53 ,attribute3 VarChar150TblType
54 ,attribute4 VarChar150TblType
55 ,attribute5 VarChar150TblType
56 ,attribute6 VarChar150TblType
57 ,attribute7 VarChar150TblType
58 ,attribute8 VarChar150TblType
59 ,attribute9 VarChar150TblType
60 ,attribute10 VarChar150TblType
61 ,attribute11 VarChar150TblType
62 ,attribute12 VarChar150TblType
63 ,attribute13 VarChar150TblType
64 ,attribute14 VarChar150TblType
65 ,attribute15 VarChar150TblType
66 ,attribute16 VarChar150TblType
67 ,attribute17 VarChar150TblType
68 ,attribute18 VarChar150TblType
69 ,attribute19 VarChar150TblType
70 ,attribute20 VarChar150TblType
71 ,attribute21 VarChar150TblType
72 ,attribute22 VarChar150TblType
73 ,attribute23 VarChar150TblType
74 ,attribute24 VarChar150TblType
75 ,attribute25 VarChar150TblType
76 ,attribute26 VarChar150TblType
77 ,attribute27 VarChar150TblType
78 ,attribute28 VarChar150TblType
79 ,attribute29 VarChar150TblType
80 ,attribute30 VarChar150TblType
81 ,information_category VarChar30TblType
82 ,information1 VarChar150TblType
83 ,information2 VarChar150TblType
84 ,information3 VarChar150TblType
85 ,information4 VarChar150TblType
86 ,information5 VarChar150TblType
87 ,information6 VarChar150TblType
88 ,information7 VarChar150TblType
89 ,information8 VarChar150TblType
90 ,information9 VarChar150TblType
91 ,information10 VarChar150TblType
92 ,information11 VarChar150TblType
93 ,information12 VarChar150TblType
94 ,information13 VarChar150TblType
95 ,information14 VarChar150TblType
96 ,information15 VarChar150TblType
97 ,information16 VarChar150TblType
98 ,information17 VarChar150TblType
99 ,information18 VarChar150TblType
100 ,information19 VarChar150TblType
101 ,information20 VarChar150TblType
102 ,information21 VarChar150TblType
103 ,information22 VarChar150TblType
104 ,information23 VarChar150TblType
105 ,information24 VarChar150TblType
106 ,information25 VarChar150TblType
107 ,information26 VarChar150TblType
108 ,information27 VarChar150TblType
109 ,information28 VarChar150TblType
110 ,information29 VarChar150TblType
111 ,information30 VarChar150TblType
112 );
113
114 posTbl PosTblType;
115
116 TYPE JobTblType IS RECORD
117 (orig_system VarChar30TblType
118 ,orig_system_id NumberTblType
119 ,business_group_id NumberTblType
120 ,legislation_code VarChar30TblType
121 ,job_definition_id NumberTblType
122 ,name VarChar700TblType
123 ,display_name VarChar240TblType
124 ,language VarChar30TblType
125 ,source_language VarChar30TblType
126 ,partition_id NumberTblType
127 ,object_version_number NumberTblType
128 ,attribute_category VarChar30TblType
129 ,attribute1 VarChar150TblType
130 ,attribute2 VarChar150TblType
131 ,attribute3 VarChar150TblType
132 ,attribute4 VarChar150TblType
133 ,attribute5 VarChar150TblType
134 ,attribute6 VarChar150TblType
135 ,attribute7 VarChar150TblType
136 ,attribute8 VarChar150TblType
137 ,attribute9 VarChar150TblType
138 ,attribute10 VarChar150TblType
139 ,attribute11 VarChar150TblType
140 ,attribute12 VarChar150TblType
141 ,attribute13 VarChar150TblType
142 ,attribute14 VarChar150TblType
143 ,attribute15 VarChar150TblType
144 ,attribute16 VarChar150TblType
145 ,attribute17 VarChar150TblType
146 ,attribute18 VarChar150TblType
147 ,attribute19 VarChar150TblType
148 ,attribute20 VarChar150TblType
149 ,job_information_category VarChar30TblType
150 ,job_information1 VarChar150TblType
151 ,job_information2 VarChar150TblType
152 ,job_information VarChar150TblType
153 ,job_information4 VarChar150TblType
154 ,job_information5 VarChar150TblType
155 ,job_information6 VarChar150TblType
156 ,job_information7 VarChar150TblType
157 ,job_information8 VarChar150TblType
158 ,job_information9 VarChar150TblType
159 ,job_information10 VarChar150TblType
160 ,job_information11 VarChar150TblType
161 ,job_information12 VarChar150TblType
162 ,job_information13 VarChar150TblType
163 ,job_information14 VarChar150TblType
164 ,job_information15 VarChar150TblType
165 ,job_information16 VarChar150TblType
166 ,job_information17 VarChar150TblType
167 ,job_information18 VarChar150TblType
168 ,job_information19 VarChar150TblType
169 ,job_information20 VarChar150TblType
170 );
171
172 jobTbl JobTblType;
173
174 TYPE CntTblType IS RECORD
175 (row_id RowIdTblType
176 ,orig_system VarChar30TblType
177 ,orig_system_id NumberTblType
178 ,cnt NumberTblType
179 );
180
181 cntTbl CntTblType;
182
183 TYPE OrgTblType IS RECORD
184 (orig_system VarChar30TblType
185 ,orig_system_id NumberTblType
186 ,business_group_id NumberTblType
187 ,legislation_code VarChar30TblType
188 ,location_id NumberTblType
189 ,representative1_id NumberTblType
190 ,representative2_id NumberTblType
191 ,representative3_id NumberTblType
192 ,representative4_id NumberTblType
193 ,name VarChar240TblType
194 ,language VarChar10TblType
195 ,source_lang VarChar10TblType
196 ,object_version_number NumberTblType
197 ,partition_id NumberTblType
198 ,attribute_category VarChar30TblType
199 ,attribute1 VarChar150TblType
200 ,attribute2 VarChar150TblType
201 ,attribute3 VarChar150TblType
202 ,attribute4 VarChar150TblType
203 ,attribute5 VarChar150TblType
204 ,attribute6 VarChar150TblType
205 ,attribute7 VarChar150TblType
206 ,attribute8 VarChar150TblType
207 ,attribute9 VarChar150TblType
208 ,attribute10 VarChar150TblType
209 ,attribute11 VarChar150TblType
210 ,attribute12 VarChar150TblType
211 ,attribute13 VarChar150TblType
212 ,attribute14 VarChar150TblType
213 ,attribute15 VarChar150TblType
214 ,attribute16 VarChar150TblType
215 ,attribute17 VarChar150TblType
216 ,attribute18 VarChar150TblType
217 ,attribute19 VarChar150TblType
218 ,attribute20 VarChar150TblType);
219
220 orgTbl OrgTblType;
221
222 TYPE LocTblType IS RECORD
223 (orig_system VarChar30TblType
224 ,orig_system_id NumberTblType
225 ,business_group_id NumberTblType
226 ,derived_locale VarChar240TblType
227 ,tax_name VarChar30TblType -- 15
228 ,country VarChar60TblType
229 ,style VarChar30TblType -- 7
230 ,address VarChar2000TblType
231 ,address_line_1 VarChar240TblType
232 ,address_line_2 VarChar240TblType
233 ,address_line_3 VarChar240TblType
234 ,town_or_city VarChar30TblType
235 ,region_1 VarChar150TblType -- 120
236 ,region_2 VarChar150TblType -- 120
237 ,region_3 VarChar150TblType -- 120
238 ,postal_code VarChar30TblType
239 ,inactive_date DateTblType
240 ,office_site_flag VarChar30TblType
241 ,receiving_site_flag VarChar30TblType
242 ,telephone_number_1 VarChar60TblType
243 ,telephone_number_2 VarChar60TblType
244 ,telephone_number_3 VarChar60TblType
245 ,timezone_id NumberTblType
246 ,timezone_code VarChar60TblType -- 50
247 ,object_version_number NumberTblType
248 ,partition_id NumberTblType
249 );
250
251 locationTbl LocTblType;
252
253 TYPE AsgTblType IS RECORD
254 (orig_system VarChar30TblType
255 ,orig_system_id NumberTblType
256 ,business_group_id NumberTblType
257 ,legislation_code VarChar30TblType
258 ,position_id NumberTblType
259 ,job_id NumberTblType
260 ,location_id NumberTblType
261 ,supervisor_id NumberTblType
262 ,supervisor_assignment_id NumberTblType
263 ,person_id NumberTblType
264 ,organization_id NumberTblType
265 ,primary_flag VarChar30TblType
266 ,active VarChar30TblType
267 ,assignment_number VarChar30TblType
268 ,discretionary_title VarChar240TblType
269 ,employee_category VarChar30TblType
270 ,employment_category VarChar30TblType
271 ,assignment_category VarChar30TblType
272 ,work_at_home VarChar30TblType
273 ,object_version_number NumberTblType
274 ,partition_id NumberTblType
275 ,ass_attribute_category VarChar30TblType
276 ,ass_attribute1 VarChar150TblType
277 ,ass_attribute2 VarChar150TblType
278 ,ass_attribute3 VarChar150TblType
279 ,ass_attribute4 VarChar150TblType
280 ,ass_attribute5 VarChar150TblType
281 ,ass_attribute6 VarChar150TblType
282 ,ass_attribute7 VarChar150TblType
283 ,ass_attribute8 VarChar150TblType
284 ,ass_attribute9 VarChar150TblType
285 ,ass_attribute10 VarChar150TblType
286 ,ass_attribute11 VarChar150TblType
287 ,ass_attribute12 VarChar150TblType
288 ,ass_attribute13 VarChar150TblType
289 ,ass_attribute14 VarChar150TblType
290 ,ass_attribute15 VarChar150TblType
291 ,ass_attribute16 VarChar150TblType
292 ,ass_attribute17 VarChar150TblType
293 ,ass_attribute18 VarChar150TblType
294 ,ass_attribute19 VarChar150TblType
295 ,ass_attribute20 VarChar150TblType
296 ,ass_attribute21 VarChar150TblType
297 ,ass_attribute22 VarChar150TblType
298 ,ass_attribute23 VarChar150TblType
299 ,ass_attribute24 VarChar150TblType
300 ,ass_attribute25 VarChar150TblType
301 ,ass_attribute26 VarChar150TblType
302 ,ass_attribute27 VarChar150TblType
303 ,ass_attribute28 VarChar150TblType
304 ,ass_attribute29 VarChar150TblType
305 ,ass_attribute30 VarChar150TblType);
306
307 asgTbl AsgTblType;
308
309 TYPE PersonTblType IS RECORD
310 (row_id RowIdTblType
311 ,person_key VarChar2000TblType
312 ,orig_system VarChar30TblType
313 ,orig_sytem_id NumberTblType
314 ,business_group_id NumberTblType
315 ,legislation_code VarChar30TblType
319 ,last_name VarChar150TblType
316 ,display_name VarChar240TblType
317 ,full_name VarChar240TblType
318 ,full_name_alternate VarChar240TblType
320 ,first_name VarChar150TblType
321 ,last_name_alternate VarChar150TblType
322 ,first_name_alternate VarChar150TblType
323 ,pre_name_adjunct VarChar30TblType
324 ,person_type VarChar10TblType
325 ,user_name VarChar60TblType
326 ,active VarChar30TblType
327 ,employee_number VarChar30TblType
328 ,known_as VarChar80TblType
329 ,middle_names VarChar60TblType
330 ,previous_last_name VarChar150TblType
331 ,start_date DateTblType
332 ,original_DOH DateTblType
333 ,email_address VarChar240TblType
334 ,work_telephone VarChar60TblType
335 ,mailstop VarChar45TblType
336 ,office_number VarChar45TblType
337 ,order_name VarChar240TblType
338 ,partition_id NumberTblType
339 ,object_version_number NumberTblType
340 ,global_person_id VarChar30TblType
341 ,party_id NumberTblType
342 ,attribute_category VarChar30TblType
343 ,attribute1 VarChar150TblType
344 ,attribute2 VarChar150TblType
345 ,attribute3 VarChar150TblType
346 ,attribute4 VarChar150TblType
347 ,attribute5 VarChar150TblType
348 ,attribute6 VarChar150TblType
349 ,attribute7 VarChar150TblType
350 ,attribute8 VarChar150TblType
351 ,attribute9 VarChar150TblType
352 ,attribute10 VarChar150TblType
353 ,attribute11 VarChar150TblType
354 ,attribute12 VarChar150TblType
355 ,attribute13 VarChar150TblType
356 ,attribute14 VarChar150TblType
357 ,attribute15 VarChar150TblType
358 ,attribute16 VarChar150TblType
359 ,attribute17 VarChar150TblType
360 ,attribute18 VarChar150TblType
361 ,attribute19 VarChar150TblType
362 ,attribute20 VarChar150TblType
363 ,attribute21 VarChar150TblType
364 ,attribute22 VarChar150TblType
365 ,attribute23 VarChar150TblType
366 ,attribute24 VarChar150TblType
367 ,attribute25 VarChar150TblType
368 ,attribute26 VarChar150TblType
369 ,attribute27 VarChar150TblType
370 ,attribute28 VarChar150TblType
371 ,attribute29 VarChar150TblType
372 ,attribute30 VarChar150TblType
373 ,per_information_category VarChar30TblType
374 ,per_information1 VarChar150TblType
375 ,per_information2 VarChar150TblType
376 ,per_information3 VarChar150TblType
377 ,per_information4 VarChar150TblType
378 ,per_information5 VarChar150TblType
379 ,per_information6 VarChar150TblType
380 ,per_information7 VarChar150TblType
381 ,per_information8 VarChar150TblType
382 ,per_information9 VarChar150TblType
383 ,per_information10 VarChar150TblType
384 ,per_information11 VarChar150TblType
385 ,per_information12 VarChar150TblType
386 ,per_information13 VarChar150TblType
387 ,per_information14 VarChar150TblType
388 ,per_information15 VarChar150TblType
389 ,per_information16 VarChar150TblType
390 ,per_information17 VarChar150TblType
391 ,per_information18 VarChar150TblType
392 ,per_information19 VarChar150TblType
393 ,per_information20 VarChar150TblType
394 ,per_information21 VarChar150TblType
395 ,per_information22 VarChar150TblType
396 ,per_information23 VarChar150TblType
397 ,per_information24 VarChar150TblType
398 ,per_information25 VarChar150TblType
399 ,per_information26 VarChar150TblType
400 ,per_information27 VarChar150TblType
401 ,per_information28 VarChar150TblType
402 ,per_information29 VarChar150TblType
403 ,per_information30 VarChar150TblType
404 ,direct_reports NumberTblType
405 ,total_reports NumberTblType
406 );
407
408 personTbl PersonTblType;
409
410 -- ---------------------------------------------------------------------------
411 -- ---------------------------- < swap > -------------------------------------
412 -- ---------------------------------------------------------------------------
413 -- Purpose: This is a utility procedure for swaping two varchar2 data elements
414 -- ---------------------------------------------------------------------------
415
416 PROCEDURE swap(
417 value1 IN OUT NOCOPY VARCHAR2
418 ,value2 IN OUT NOCOPY VARCHAR2);
419
420 -- ---------------------------------------------------------------------------
421 -- ---------------------------- < main > -------------------------------------
422 -- ---------------------------------------------------------------------------
423 -- Purpose: This procedure is invoked by PEREMPDIRREFRESH conc. prg.
424 -- p_mode: {COMPLETE, INCREMENTAL}
425 -- p_eff_date: Refresh date
426 -- p_soruce_system: Source system identifier
427 -- p_multi_asg: Process multiple assignments
428 -- p_refresh_images: Process images
429 -- ---------------------------------------------------------------------------
430
431 PROCEDURE main(
432 errbuf OUT NOCOPY VARCHAR2
433 ,retcode OUT NOCOPY VARCHAR2
434 ,p_mode IN VARCHAR2
435 ,p_eff_date IN VARCHAR2
436 ,p_source_system IN VARCHAR2
437 ,p_multi_asg IN VARCHAR2 DEFAULT 'N'
438 ,p_image_refresh IN VARCHAR2 DEFAULT 'N'
439 );
440
441 -- ---------------------------------------------------------------------------
442 -- ---------------------------- < write_log > --------------------------------
443 -- ---------------------------------------------------------------------------
444 -- Purpose: Utility procedure for writting conc. prg. log
445 -- ---------------------------------------------------------------------------
446
447 PROCEDURE write_log(
448 p_fpt IN NUMBER
449 ,p_msg IN VARCHAR2);
450
451 -- ---------------------------------------------------------------------------
452 -- ---------------------------- < compute_reports > --------------------------
453 -- ---------------------------------------------------------------------------
454 -- Purpose: This procedure is invoked by PEREMPDIRCR conc. prg.
455 -- p_source_system: Source system identifier
456 -- ---------------------------------------------------------------------------
457
458 PROCEDURE compute_reports(
459 errbuf OUT NOCOPY VARCHAR2
460 ,retcode OUT NOCOPY VARCHAR2
461 ,p_source_system IN VARCHAR2
462 );
463
464 -- ---------------------------------------------------------------------------
465 -- ---------------------------- < get_timezone_id > --------------------------
466 -- ---------------------------------------------------------------------------
467 -- Purpose: This function returns the timeZoneID given address location
468 -- ---------------------------------------------------------------------------
469
470 FUNCTION get_timezone_id(
471 p_postal_code IN VARCHAR2,
472 p_city IN VARCHAR2,
473 p_state IN VARCHAR2,
474 p_country IN VARCHAR2
475 ) RETURN NUMBER;
476
477 -- ---------------------------------------------------------------------------
478 -- ---------------------------- < get_timezone_id > --------------------------
479 -- ---------------------------------------------------------------------------
480 -- Purpose: This function returns the timeZone Code based on the timeZoneId
481 -- derived for given address location
482 -- ---------------------------------------------------------------------------
483
484 FUNCTION get_timezone_code(
485 p_postal_code IN VARCHAR2,
486 p_city IN VARCHAR2,
487 p_state IN VARCHAR2,
488 p_country IN VARCHAR2
489 ) RETURN VARCHAR2;
490
491
492 -- ---------------------------------------------------------------------------
493 -- ---------------------------- < get_time > ---------------------------------
494 -- ---------------------------------------------------------------------------
495 -- Purpose: This procedure is invoked by the Online code for deriving the
496 -- local time give server and client timeZoneID's
497 -- ---------------------------------------------------------------------------
498
499 FUNCTION get_time (
500 p_source_tz_id IN NUMBER,
501 p_dest_tz_id IN NUMBER,
502 p_source_day_time IN DATE
503 ) RETURN VARCHAR2;
504
505 -- ---------------------------------------------------------------------------
506 -- ---------------------------- < get_time > ---------------------------------
507 -- ---------------------------------------------------------------------------
508 -- Purpose: This procedure is invoked by the Online code for deriving the
509 -- local time given the client timeZoneID's. (server timezone is derived
510 -- using FND_TIMEZONES package.
511 -- ---------------------------------------------------------------------------
512 function get_time (
513 p_to_tz in varchar2
514 ) return varchar2;
515
516 END PER_EMPDIR_SS;