[Home] [Help]
PACKAGE BODY: APPS.HR_NMF_SYNC
Source
1 Package Body HR_NMF_SYNC as
2 /* $Header: hrnmfsyn.pkb 120.7 2006/11/03 12:29:14 rvarshne noship $ */
3
4 --
5 g_package CONSTANT varchar2(30) := 'hr_nmf_sync.';
6 g_FULL_NAME CONSTANT hr_name_formats.format_name%TYPE := 'FULL_NAME';
7 g_ORDER_NAME CONSTANT hr_name_formats.format_name%TYPE := 'ORDER_NAME';
8 g_LIST_NAME CONSTANT hr_name_formats.format_name%TYPE := 'LIST_NAME';
9 --
10 -- Write_Log: writes messages to the LOG file generated by Concurrent Mg.
11 -- ---------------------------------------------------------------------------+
12 PROCEDURE Write_Log
13 ( p_message varchar2 ) IS
14 --
15 c_proc_name CONSTANT varchar2(100) := g_package||'.write_log';
16 BEGIN
17 FND_FILE.PUT_LINE(FND_FILE.LOG,p_message);
18
19 EXCEPTION
20 WHEN OTHERS THEN
21 --
22 -- Exception raised whilst writing the log file...
23 --
24 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
25 RAISE;
26 END Write_Log;
27 --
28 /* =====================================================================
29 Name : cache_mask_info
30 =====================================================================
31 Purpose : Populates the PL/SQL table with the given mask information.
32 If the table is already cached, the mask is added.
33 Returns : Nothing.
34 ---------------------------------------------------------------------*/
35 PROCEDURE cache_mask_info(p_legislation_code in varchar2
36 ,p_format_name in varchar2
37 ,p_scope in varchar2) IS
38 --
39 cursor csr_get_masks(cp_legCode varchar2
40 ,cp_formatName varchar2
41 ,cp_scope varchar2) is
42 select nmf.legislation_code
43 ,nmf.format_name
44 ,nmf.user_format_choice
45 ,nmf.format_mask
46 from HR_NAME_FORMATS nmf
47 where ((cp_legCode is not null and nmf.legislation_code = cp_legCode)
48 OR ( nmf.legislation_code is null))
49 and ((cp_formatName is not null and nmf.format_name = cp_formatName)
50 OR (cp_formatName is null))
51 and ((cp_scope is not null and nmf.user_format_choice = cp_scope)
52 OR (cp_scope is null));
53 --
54 cursor csr_get_all_leg(cp_formatName varchar2
55 ,cp_scope varchar2) is
56 select nmf.legislation_code
57 ,nmf.format_name
58 ,nmf.user_format_choice
59 ,nmf.format_mask
60 from HR_NAME_FORMATS nmf
61 where nmf.legislation_code in
62 (select distinct legislation_code from per_business_groups_perf)
63 and ((cp_formatName is not null and nmf.format_name = cp_formatName)
64 OR (cp_formatName is null))
65 and ((cp_scope is not null and nmf.user_format_choice = cp_scope)
66 OR (cp_scope is null));
67 --
68 BEGIN
69 --
70 -- Cache the format masks based on parameters being passed
71 --
72 if g_masks_cached = FALSE then
73 g_leg_masks_cache.sz := 0;
74 end if;
75 --
76 for masks_rec in csr_get_masks(p_legislation_code
77 ,p_format_name
78 ,p_scope) loop
79 --
80 g_leg_masks_cache.sz := g_leg_masks_cache.sz + 1;
81 g_leg_masks_cache.legislation_code(g_leg_masks_cache.sz) := masks_rec.legislation_code;
82 g_leg_masks_cache.format_name(g_leg_masks_cache.sz) := masks_rec.format_name;
83 g_leg_masks_cache.user_format_choice(g_leg_masks_cache.sz) := masks_rec.user_format_choice;
84 g_leg_masks_cache.format_mask(g_leg_masks_cache.sz) := masks_rec.format_mask;
85 --
86 end loop;
87 --
88 if p_legislation_code is null then
89 -- cache all legislations
90 for masks_rec in csr_get_all_leg(p_format_name, p_scope) loop
91 --
92 g_leg_masks_cache.sz := g_leg_masks_cache.sz + 1;
93 g_leg_masks_cache.legislation_code(g_leg_masks_cache.sz) := masks_rec.legislation_code;
94 g_leg_masks_cache.format_name(g_leg_masks_cache.sz) := masks_rec.format_name;
95 g_leg_masks_cache.user_format_choice(g_leg_masks_cache.sz) := masks_rec.user_format_choice;
96 g_leg_masks_cache.format_mask(g_leg_masks_cache.sz) := masks_rec.format_mask;
97 --
98 end loop;
99 end if;
100 --
101 g_masks_cached := TRUE;
102 --
103 END cache_mask_info;
104 --
105 /* =====================================================================
106 Name : get_cache_mask
107 /* =====================================================================
108 Purpose : Gets the cached mask from a cached pl/sql table to prevent
109 same reads of tables for each person in the sync process.
110 Returns : format_mask if found, otherwise NULL.
111 ---------------------------------------------------------------------*/
112 function get_cache_mask (p_legislation_code in varchar2
113 ,p_format_name in varchar2
114 ,p_scope in varchar2 )
115 return varchar2 is
116
117 l_mask_rec number;
118 l_format_mask hr_name_formats.format_mask%TYPE;
119
120 begin
121 --
122 l_format_mask := null;
123 --
124 for l_mask_rec in 1..g_leg_masks_cache.sz loop
125
126 if (nvl(g_leg_masks_cache.legislation_code(l_mask_rec),'NULL') = nvl(p_legislation_code,'NULL'))
127 and (g_leg_masks_cache.format_name(l_mask_rec) = p_format_name)
128 and (g_leg_masks_cache.user_format_choice(l_mask_rec) = p_scope)
129 then
130 l_format_mask := g_leg_masks_cache.format_mask(l_mask_rec);
131 exit;
132 end if;
133
134 end loop;
135
136 return l_format_mask;
137 -- This will be null if the mask is not in the cached mask table
138 --
139 end get_cache_mask;
140 --
141 /* ========================================================================
142 Name : get_mask
143 ========================================================================
144 Purpose : Gets the mask information from a cached pl/sql table to prevent
145 same table scans on name formats table for each person in the
146 sync process.
147 Returns : format mask if found, otherwise null.
148 -----------------------------------------------------------------------*/
149 function get_mask(p_legislation_code in varchar2
150 ,p_format_name in varchar2
151 ,p_scope in varchar2 )
152 return varchar2 is
153 --
154 l_format_mask hr_name_formats.format_mask%TYPE;
155 --
156 begin
157 --
158 if NOT g_masks_cached then
159 cache_mask_info(p_legislation_code => p_legislation_code
160 ,p_format_name => p_format_name
161 ,p_scope => p_scope);
162 end if;
163
164 l_format_mask := get_cache_mask
165 (p_legislation_code => p_legislation_code
166 ,p_format_name => p_format_name
167 ,p_scope => p_scope);
168
169 return l_format_mask;
170 -- This will be null if mask does not exist
171 --
172 end get_mask;
173 --
174 /* =====================================================================
175 Name : cache_bg_info
176 =====================================================================
177 Purpose : Populates the PL/SQL table with the given business group
178 information. If the table is already cached, the bg is added.
179 Returns : Nothing.
180 ---------------------------------------------------------------------*/
181 PROCEDURE cache_bg_info(p_legislation_code in varchar2) IS
182 --
183 cursor csr_get_bgs(cp_legCode varchar2) is
184 select legislation_code, business_group_id, name
185 from PER_BUSINESS_GROUPS_PERF bg
186 where ((cp_legCode is not null and bg.legislation_code = cp_legCode)
187 or (cp_legCode is null));
188 --
189 BEGIN
190 --
191 if g_bg_cached = FALSE then
192 g_business_groups_cache.sz := 0;
193 end if;
194 --
195 for bgs_rec in csr_get_bgs(p_legislation_code) loop
196 --
197 g_business_groups_cache.sz := g_business_groups_cache.sz + 1;
198 g_business_groups_cache.legislation_code(g_business_groups_cache.sz) := bgs_rec.legislation_code;
199 g_business_groups_cache.bg_name(g_business_groups_cache.sz) := bgs_rec.name;
200 g_business_groups_cache.business_group_id(g_business_groups_cache.sz) := bgs_rec.business_group_id;
201 --
202 end loop;
203 --
204 g_bg_cached := TRUE;
205 --
206 END cache_bg_info;
207 --
208 --
209 /* =====================================================================
210 Name : get_cache_bg_leg_code
211 /* =====================================================================
212 Purpose : retrieves the legislation code associated with business
213 group parameter
214 Returns : legislation code if found, otherwise null
215 ---------------------------------------------------------------------*/
216 function get_cache_bg_leg_code(p_business_group_id number)
217 return varchar2 is
218 --
219 l_legislation_code varchar2(80);
220 begin
221 l_legislation_code := null;
222 for bg_rec in 1.. g_business_groups_cache.sz loop
223 if g_business_groups_cache.business_group_id(bg_rec) = p_business_group_id then
224 l_legislation_code := g_business_groups_cache.legislation_code(bg_rec);
225 exit;
226 end if;
227 end loop;
228 return l_legislation_code;
229 end get_cache_bg_leg_code;
230 --
231 --
232 /* =====================================================================
233 Name : cache_format_names
234 /* =====================================================================
235 Purpose : Caches the format names into a pl/sql table for later
236 processing.
237 Returns : nothing
238 ---------------------------------------------------------------------*/
239 procedure cache_format_names is
240 begin
241 g_format_names_tbl(1) := g_FULL_NAME;
242 g_format_names_tbl(2) := g_ORDER_NAME;
243 g_format_names_tbl(3) := g_LIST_NAME;
244 end;
245 --
246 --
247 /* =====================================================================
248 Name : get_cache_seeded_mask
249 /* =====================================================================
250 Purpose : Gets the cached seeded mask from a cached pl/sql table to
251 preventsame reads of tables for each person in the sync
252 process.
253 Returns : format_mask if found, otherwise NULL.
254 ---------------------------------------------------------------------*/
255 function get_cache_seeded_mask
256 (p_format_name in varchar2
257 ,p_scope in varchar2 ) return varchar2 is
258
259 l_mask_rec number;
260 l_format_mask hr_name_formats.format_mask%TYPE;
261
262 begin
263 --
264 l_format_mask := null;
265 --
266 for l_mask_rec in 1..g_leg_masks_cache.sz loop
267
268 if (g_leg_masks_cache.legislation_code(l_mask_rec) is null)
269 and (g_leg_masks_cache.format_name(l_mask_rec) = p_format_name)
270 and (g_leg_masks_cache.user_format_choice(l_mask_rec) = p_scope)
271 then
272 l_format_mask := g_leg_masks_cache.format_mask(l_mask_rec);
273 exit;
274 end if;
275 end loop;
276 return l_format_mask;
277 -- This will be null if the mask is not in the cached mask table
278 --
279 end get_cache_seeded_mask;
280 --
281 /* =====================================================================
282 Name : cache_seeded_pkg
283 =====================================================================
284 Purpose : Populates the PL/SQL table with the given seeded proc
285 information.
286 Returns : Nothing.
287 ---------------------------------------------------------------------*/
288 PROCEDURE cache_seeded_pkg IS
289 --
290 l_format_name hr_name_formats.format_name%TYPE;
291 --
292 cursor csr_get_legislations is
293 select leg.territory_code legislation_code
294 from fnd_territories_vl leg
295 where leg.territory_code in
296 (select distinct legislation_code from per_business_groups_perf);
297 --
298 BEGIN
299 --
300 if g_seeded_pkg_cached = FALSE then
301 g_leg_seeded_pkg_cache.sz := 0;
302 end if;
303 --
304 for leg_rec in csr_get_legislations loop
305 --
306 l_format_name := g_FULL_NAME;
307 g_leg_seeded_pkg_cache.sz := g_leg_seeded_pkg_cache.sz + 1;
308 g_leg_seeded_pkg_cache.legislation_code(g_leg_seeded_pkg_cache.sz) := leg_rec.legislation_code;
309 g_leg_seeded_pkg_cache.format_name(g_leg_seeded_pkg_cache.sz) := l_format_name;
310 hr_person_name.get_seeded_procedure_name
311 (p_format_name => l_format_name
312 ,p_legislation_code => leg_rec.legislation_code
313 ,p_package_name =>
314 g_leg_seeded_pkg_cache.seeded_package_name(g_leg_seeded_pkg_cache.sz)
315 ,p_procedure_name =>
316 g_leg_seeded_pkg_cache.seeded_procedure_name(g_leg_seeded_pkg_cache.sz)
317 );
318 --
319 l_format_name := g_ORDER_NAME;
320 g_leg_seeded_pkg_cache.sz := g_leg_seeded_pkg_cache.sz + 1;
321 g_leg_seeded_pkg_cache.legislation_code(g_leg_seeded_pkg_cache.sz) := leg_rec.legislation_code;
322 g_leg_seeded_pkg_cache.format_name(g_leg_seeded_pkg_cache.sz) := l_format_name;
323 hr_person_name.get_seeded_procedure_name
324 (p_format_name => l_format_name
325 ,p_legislation_code => leg_rec.legislation_code
326 ,p_package_name =>
327 g_leg_seeded_pkg_cache.seeded_package_name(g_leg_seeded_pkg_cache.sz)
328 ,p_procedure_name =>
329 g_leg_seeded_pkg_cache.seeded_procedure_name(g_leg_seeded_pkg_cache.sz)
330 );
331 --
332 end loop;
333 --
334 g_seeded_pkg_cached := TRUE;
335 --
336 END cache_seeded_pkg;
337 --
338 /* =====================================================================
339 Name : get_cache_seeded_pkg
340 /* =====================================================================
341 Purpose : Gets the cached seeded package from a cached pl/sql table
342 to prevent same reads of tables for each person in the
343 sync process.
344 Returns : seeded package name if found, otherwise NULL.
345 ---------------------------------------------------------------------*/
346 procedure get_cache_seeded_pkg
347 (p_format_name in varchar2
348 ,p_legislation_code in varchar2
349 ,p_pkg_name out NOCOPY varchar2
350 ,p_procedure_name out NOCOPY varchar2 ) is
351 --
352 l_pkg_rec number;
353 l_pkg_name varchar2(80);
354 l_proc_name varchar2(80);
355 --
356 begin
357 --
358 l_pkg_name := null;
359 l_proc_name := null;
360 for l_pkg_rec in 1..g_leg_seeded_pkg_cache.sz loop
361 if (g_leg_seeded_pkg_cache.legislation_code(l_pkg_rec) = p_legislation_code)
362 and (g_leg_seeded_pkg_cache.format_name(l_pkg_rec) = p_format_name)
363 then
364 l_pkg_name := g_leg_seeded_pkg_cache.seeded_package_name(l_pkg_rec);
365 l_proc_name := g_leg_seeded_pkg_cache.seeded_procedure_name(l_pkg_rec);
366 exit;
367 end if;
368
369 end loop;
370 p_pkg_name := l_pkg_name;
371 p_procedure_name := l_proc_name;
372 --
373 end get_cache_seeded_pkg;
374 --
375 /* ========================================================================
376 Name : get_seeded_pkg
377 ========================================================================
378 Purpose : Gets the seeded pkg information from a cached pl/sql table
379 to prevent same table scans for each person in the
380 sync process.
381 Returns : pkg and procedure names if found, otherwise null.
382 -----------------------------------------------------------------------*/
383 procedure get_seeded_pkg
384 (p_format_name in varchar2
385 ,p_legislation_code in varchar2
386 ,p_pkg_name out NOCOPY varchar2
387 ,p_procedure_name out NOCOPY varchar2 ) is
388 --
389 begin
390 --
391 -- will assume value is always cached
392 --
393 get_cache_seeded_pkg
394 (p_format_name => p_format_name
395 ,p_legislation_code => p_legislation_code
396 ,p_pkg_name => p_pkg_name
397 ,p_procedure_name => p_procedure_name);
398 --
399 end get_seeded_pkg;
400 --
401 /* ========================================================================
402 Name : sync_person_names
403 ========================================================================
404 Purpose : rebuilds the person names in per_all_people_f depending on
405 parameters being passed.
406 Returns : nothing, it updates the table with newly constructed names.
407 -----------------------------------------------------------------------*/
408 procedure sync_person_names
409 (p_rowid in rowid
410 ,p_person_id in number
411 ,p_person_rec in hr_person_name.t_nameColumns_Rec
412 ,p_legislation_code in varchar2
413 ,p_format_name in varchar2
414 ,p_user_format_choice in varchar2
415 ,p_num_rows_processed out nocopy number) IS
416 --
417 l_format_mask hr_name_formats.format_mask%TYPE;
418 l_seeded_format_mask hr_name_formats.format_mask%TYPE;
419 l_pkg_name varchar2(80);
420 l_proc_name varchar2(80);
421
422
423 l_new_name per_all_people_f.full_name%TYPE;
424 l_full_name per_all_people_f.full_name%TYPE;
425 l_order_name per_all_people_f.order_name%TYPE;
426 l_local_name per_all_people_f.local_name%TYPE;
427 l_global_name per_all_people_f.global_name%TYPE;
428 l_name_changed boolean;
429 --
430 --
431 begin
432 --
433 l_name_changed := FALSE;
434 l_full_name := p_person_rec.full_name;
435 l_order_name := p_person_rec.order_name;
436 l_local_name := p_person_rec.local_name;
437 l_global_name := p_person_rec.global_name;
438 l_pkg_name := null;
439 l_proc_name := null;
440 --
441 if p_format_name is not null and p_format_name in (g_FULL_NAME,g_ORDER_NAME) then
442 get_seeded_pkg
443 (p_format_name => p_format_name
444 ,p_legislation_code => p_legislation_code
445 ,p_pkg_name => l_pkg_name
446 ,p_procedure_name => l_proc_name);
447 end if;
448 --
449 if p_format_name is not null then
450 if p_user_format_choice is not null then
451 --
452 l_format_mask := get_mask(p_legislation_code => p_legislation_code
453 ,p_format_name => p_format_name
454 ,p_scope => p_user_format_choice);
455 l_seeded_format_mask := get_mask(p_legislation_code => NULL
456 ,p_format_name => p_format_name
457 ,p_scope => p_user_format_choice);
458 l_new_name := hr_person_name.derive_formatted_name
459 (p_person_names_rec => p_person_rec
460 ,p_format_name => p_format_name
461 ,p_legislation_code => p_legislation_code
462 ,p_format_mask => l_format_mask
463 ,p_seeded_pkg => l_pkg_name
464 ,p_seeded_procedure => l_proc_name
465 ,p_seeded_format_mask => l_seeded_format_mask);
466 if p_format_name = g_FULL_NAME then
467 l_full_name := l_new_name;
468 elsif p_format_name = g_ORDER_NAME then
469 l_order_name := l_new_name;
470 elsif p_format_name = g_LIST_NAME and p_user_format_choice = 'L' then
471 l_local_name := l_new_name;
472 elsif p_format_name = g_LIST_NAME and p_user_format_choice = 'G' then
473 l_global_name := l_new_name;
474 end if;
475 else
476 -- ------------------------------------------------------------------
477 -- User format choice is null; therefore, generate for all types
478 -- ------------------------------------------------------------------
479 if p_format_name in (g_FULL_NAME,g_ORDER_NAME) then
480 l_format_mask := get_mask(p_legislation_code => p_legislation_code
481 ,p_format_name => p_format_name
482 ,p_scope => 'L');
483 l_seeded_format_mask := get_mask(p_legislation_code => NULL
484 ,p_format_name => p_format_name
485 ,p_scope => 'L');
486 l_new_name := hr_person_name.derive_formatted_name
487 (p_person_names_rec => p_person_rec
488 ,p_format_name => p_format_name
489 ,p_legislation_code => p_legislation_code
490 ,p_format_mask => l_format_mask
491 ,p_seeded_pkg => l_pkg_name
492 ,p_seeded_procedure => l_proc_name
493 ,p_seeded_format_mask => l_seeded_format_mask);
494 --
495 if p_format_name = g_FULL_NAME then
496 l_full_name := l_new_name;
497 elsif p_format_name = g_ORDER_NAME then
498 l_order_name := l_new_name;
499 end if;
500 --
501 else
502 l_format_mask := get_mask(p_legislation_code => p_legislation_code
503 ,p_format_name => p_format_name
504 ,p_scope => 'L');
505
506 l_seeded_format_mask := get_mask(p_legislation_code => NULL
507 ,p_format_name => p_format_name
508 ,p_scope => 'L');
509
510 l_local_name := hr_person_name.derive_formatted_name
511 (p_person_names_rec => p_person_rec
512 ,p_format_name => p_format_name
513 ,p_legislation_code => p_legislation_code
514 ,p_format_mask => l_format_mask
515 ,p_seeded_pkg => NULL
516 ,p_seeded_procedure => NULL
517 ,p_seeded_format_mask => l_seeded_format_mask);
518 --
519 l_format_mask := get_mask(p_legislation_code => p_legislation_code
520 ,p_format_name => p_format_name
521 ,p_scope => 'G');
522
523 l_seeded_format_mask := get_mask(p_legislation_code => NULL
524 ,p_format_name => p_format_name
525 ,p_scope => 'G');
526
527 l_global_name := hr_person_name.derive_formatted_name
528 (p_person_names_rec => p_person_rec
529 ,p_format_name => p_format_name
530 ,p_legislation_code => p_legislation_code
531 ,p_format_mask => l_format_mask
532 ,p_seeded_pkg => NULL
533 ,p_seeded_procedure => NULL
534 ,p_seeded_format_mask => l_seeded_format_mask);
535 --
536 end if; -- format_name in Full_name / Order Name
537 end if; -- user format choice is null
538 else -- format name is null
539 -- ------------------------------------------------------------------
540 -- Sync all the stored names
541 -- ------------------------------------------------------------------
542 l_pkg_name := null;
543 l_proc_name := null;
544 l_format_mask := null;
545 l_seeded_format_mask := null;
546 if p_user_format_choice is not null then
547 --
548 for i in g_format_names_tbl.first .. g_format_names_tbl.last loop
549 l_format_mask := get_mask(p_legislation_code => p_legislation_code
550 ,p_format_name => g_format_names_tbl(i)
551 ,p_scope => p_user_format_choice);
552 l_seeded_format_mask := get_mask(p_legislation_code => NULL
553 ,p_format_name => g_format_names_tbl(i)
554 ,p_scope => p_user_format_choice);
555 if g_format_names_tbl(i) in (g_FULL_NAME,g_ORDER_NAME) then
556 get_seeded_pkg
557 (p_format_name => g_format_names_tbl(i)
558 ,p_legislation_code => p_legislation_code
559 ,p_pkg_name => l_pkg_name
560 ,p_procedure_name => l_proc_name);
561 else
562 l_pkg_name := null;
563 l_proc_name := null;
564 end if;
565 l_new_name := hr_person_name.derive_formatted_name
566 (p_person_names_rec => p_person_rec
567 ,p_format_name => g_format_names_tbl(i)
568 ,p_legislation_code => p_legislation_code
569 ,p_format_mask => l_format_mask
570 ,p_seeded_pkg => l_pkg_name
571 ,p_seeded_procedure => l_proc_name
572 ,p_seeded_format_mask => l_seeded_format_mask);
573 --
574 if p_format_name = g_FULL_NAME then
575 l_full_name := l_new_name;
576 elsif p_format_name = g_ORDER_NAME then
577 l_order_name := l_new_name;
578 elsif p_format_name = g_LIST_NAME and p_user_format_choice = 'L' then
579 l_local_name := l_new_name;
580 elsif p_format_name = g_LIST_NAME and p_user_format_choice = 'G' then
581 l_global_name := l_new_name;
582 end if;
583 end loop;
584 else
585 -- ------------------------------------------------------------------
586 -- User format choice is null; therefore, generate for all stored
587 -- names and all user format choices
588 -- ------------------------------------------------------------------
589 l_pkg_name := null;
590 l_proc_name := null;
591 l_format_mask := null;
592
593 for i in g_format_names_tbl.first .. g_format_names_tbl.last loop
594 if g_format_names_tbl(i) in (g_FULL_NAME,g_ORDER_NAME) then
595 l_format_mask := get_mask(p_legislation_code => p_legislation_code
596 ,p_format_name => g_format_names_tbl(i)
597 ,p_scope => 'L');
598 l_seeded_format_mask := get_mask(p_legislation_code => NULL
599 ,p_format_name => g_format_names_tbl(i)
600 ,p_scope => 'L');
601 get_seeded_pkg
602 (p_format_name => g_format_names_tbl(i)
603 ,p_legislation_code => p_legislation_code
604 ,p_pkg_name => l_pkg_name
605 ,p_procedure_name => l_proc_name);
606 l_new_name := hr_person_name.derive_formatted_name
607 (p_person_names_rec => p_person_rec
608 ,p_format_name => g_format_names_tbl(i)
609 ,p_legislation_code => p_legislation_code
610 ,p_format_mask => l_format_mask
611 ,p_seeded_pkg => l_pkg_name
612 ,p_seeded_procedure => l_proc_name
613 ,p_seeded_format_mask => l_seeded_format_mask);
614 if g_format_names_tbl(i) = g_FULL_NAME then
615 l_full_name := l_new_name;
616 else
617 l_order_name := l_new_name;
618 end if;
619 else
620 l_format_mask := get_mask(p_legislation_code => p_legislation_code
621 ,p_format_name => g_format_names_tbl(i)
622 ,p_scope => 'L');
623 l_seeded_format_mask := get_mask(p_legislation_code => NULL
624 ,p_format_name => g_format_names_tbl(i)
625 ,p_scope => 'L');
626
627 l_local_name := hr_person_name.derive_formatted_name
628 (p_person_names_rec => p_person_rec
629 ,p_format_name => g_format_names_tbl(i)
630 ,p_legislation_code => p_legislation_code
631 ,p_format_mask => l_format_mask
632 ,p_seeded_pkg => NULL
633 ,p_seeded_procedure => NULL
634 ,p_seeded_format_mask => l_seeded_format_mask);
635
636 l_format_mask := get_mask(p_legislation_code => p_legislation_code
637 ,p_format_name => g_format_names_tbl(i)
638 ,p_scope => 'G');
639 l_seeded_format_mask := get_mask(p_legislation_code => NULL
640 ,p_format_name => g_format_names_tbl(i)
641 ,p_scope => 'G');
642
643 l_global_name := hr_person_name.derive_formatted_name
644 (p_person_names_rec => p_person_rec
645 ,p_format_name => g_format_names_tbl(i)
646 ,p_legislation_code => p_legislation_code
647 ,p_format_mask => l_format_mask
648 ,p_seeded_pkg => NULL
649 ,p_seeded_procedure => NULL
650 ,p_seeded_format_mask => l_seeded_format_mask);
651
652 end if;
653 end loop;
654 end if;
655 end if; -- all stored names, all user format choice options
656 -- ---------------------------------------------------------------------
657 -- Update per_all_people_f table with new names
658 -- ---------------------------------------------------------------------
659 if nvl(l_full_name,'NULL') <> nvl(p_person_rec.full_name,'NULL') then
660 l_name_changed := TRUE;
661 elsif nvl(l_order_name,'NULL') <> nvl(p_person_rec.order_name,'NULL') then
662 l_name_changed := TRUE;
663 elsif nvl(l_local_name,'NULL') <> nvl(p_person_rec.local_name,'NULL') then
664 l_name_changed := TRUE;
665 elsif nvl(l_global_name,'NULL') <> nvl(p_person_rec.global_name,'NULL') then
666 l_name_changed := TRUE;
667 end if;
668 if l_name_changed then
669 update per_all_people_f
670 set full_name = l_full_name
671 ,order_name = l_order_name
672 ,local_name = l_local_name
673 ,global_name = l_global_name
674 where rowid = p_rowid;
675 p_num_rows_processed := SQL%ROWCOUNT;
676 end if;
677 --
678 end sync_person_names;
679 --
680 --
681 PROCEDURE alter_who_trigger(p_action in varchar2
682 ,p_error out nocopy varchar2) is
683 --
684 trigger_not_exists EXCEPTION;
685 PRAGMA EXCEPTION_INIT(trigger_not_exists,-04080);
686 --
687 l_owner all_triggers.owner%TYPE;
688 l_cmd varchar2(1000);
689 --
690 cursor csr_get_trigger_owner is
691 select table_owner from user_triggers
692 where trigger_name = 'PER_ALL_PEOPLE_F_WHO';
693 BEGIN
694 p_error := null;
695 open csr_get_trigger_owner;
696 fetch csr_get_trigger_owner into l_owner;
697 close csr_get_trigger_owner;
698 if l_owner is not null then
699 l_cmd := 'ALTER TRIGGER PER_ALL_PEOPLE_F_WHO ';
700 if p_action = 'DISABLE' then
701 l_cmd := l_cmd || 'DISABLE';
702 EXECUTE IMMEDIATE l_cmd;
703 elsif p_action = 'ENABLE' then
704 l_cmd := l_cmd || 'ENABLE';
705 EXECUTE IMMEDIATE l_cmd;
706 end if;
707 end if;
708 EXCEPTION
709 when trigger_not_exists then NULL;
710 when others then p_error := substr(SQLERRM,1,512);
711 END alter_who_trigger;
712 --
713 -- ----------------------------------------------------------------------------
714 -- |--------------------------< range_cursor >--------------------------------|
715 -- ----------------------------------------------------------------------------
716 --
717 PROCEDURE range_cursor (pactid in number,
718 sqlstr out NOCOPY varchar2) is
719
720 l_proc CONSTANT varchar2(100) := g_package||'range_curosr';
721 l_leg_code varchar2(30);
722 l_bg_list varchar2(4000);
723 --
724 cursor csr_get_bgs(cp_legCode varchar2) is
725 select business_group_id
726 from PER_BUSINESS_GROUPS_PERF bg
727 where bg.legislation_code = cp_legCode;
728 --
729 BEGIN
730 select pay_core_utils.get_parameter('LEGISLATION_CODE',
731 ppa.legislative_parameters)
732 into l_leg_code
733 from pay_payroll_actions ppa
734 where ppa.payroll_action_id = pactid;
735 /*
736 ** Define the SQL statement to get the people we want to process. Provide
737 ** initial filtering based on business group if appropriate.
738 */
739 if l_leg_code is null then
740 /*
741 **scope is ALL_BUS_GRP
742 */
743 sqlstr := 'select distinct per.person_id
744 from per_all_people_f per
745 where :payroll_action_id > 0
746 order by per.person_id';
747 else
748 sqlstr := 'select distinct per.person_id
749 from per_all_people_f per
750 ,per_business_groups_perf bg
751 where :payroll_action_id > 0
752 and bg.business_group_id = per.business_group_id
753 and bg.legislation_code = '||''''||l_leg_code||''''||
754 ' order by per.person_id';
755 end if;
756
757 END range_cursor;
758 --
759 -- ----------------------------------------------------------------------------
760 -- |--------------------------< action_creation >-----------------------------|
761 -- ----------------------------------------------------------------------------
762 --
763 -- Purpose : This routine creates assignment actions for a specific chunk.
764 -- Only one action is created for a single person ID.
765 --
766 -- Notes :
767 --
768 PROCEDURE action_creation (pactid in number,
769 stperson in number,
770 endperson in number,
771 chunk in number) is
772 --
773 l_temp_person_id per_all_people_f.person_id%TYPE;
774 l_lockingactid pay_assignment_actions.assignment_action_id%TYPE;
775 l_business_group_id number;
776 l_legislation_code per_business_groups_perf.legislation_code%TYPE;
777 --
778 cursor csr_actions(cp_pactid number
779 ,cp_stperson number
780 ,cp_endperson number
781 ,cp_leg_code varchar2) is -- #4571033
782 select distinct ppf.person_id
783 from per_all_people_f ppf
784 ,per_business_groups_perf bg
785 ,pay_payroll_actions ppa
786 where ppf.person_id between cp_stperson and cp_endperson
787 and ppa.payroll_action_id = cp_pactid
788 and ppf.business_group_id = bg.business_group_id
789 and bg.legislation_code = cp_leg_code;
790 --
791 cursor csr_actions_no_leg(cp_pactid number
792 ,cp_stperson number
793 ,cp_endperson number) is
794 select distinct ppf.person_id
795 from per_all_people_f ppf
796 ,pay_payroll_actions ppa
797 where ppf.person_id between cp_stperson and cp_endperson
798 and ppa.payroll_action_id = cp_pactid;
799 --
800 BEGIN
801 select pay_core_utils.get_parameter('LEGISLATION_CODE',
802 ppa.legislative_parameters)
803 into l_legislation_code
804 from pay_payroll_actions ppa
805 where ppa.payroll_action_id = pactid;
806 if l_legislation_code is null then
807 --
808 for people_rec in csr_actions_no_leg(pactid, stperson, endperson) loop
809 select pay_assignment_actions_s.nextval into l_lockingactid
810 from dual;
811 -- Passed person_id/PER_ALL_PEOPLE_F as object_id/object_type, procedure
812 -- archive_data needs it to process a person.
813 hr_nonrun_asact.insact(lockingactid => l_lockingactid,
814 assignid => -1,
815 pactid => pactid,
816 chunk => chunk,
817 greid => null,
818 object_id => people_rec.person_id,
819 object_type => 'PER_ALL_PEOPLE_F');
820 end loop;
821 else
822 -- only process those belonging to a specific legislation
823 --
824 for people_rec in csr_actions(pactid, stperson, endperson, l_legislation_code) loop
825 select pay_assignment_actions_s.nextval into l_lockingactid
826 from dual;
827 -- Passed person_id/PER_ALL_PEOPLE_F as object_id/object_type, procedure
828 -- archive_data needs it to process a person.
829 hr_nonrun_asact.insact(lockingactid => l_lockingactid,
830 assignid => -1,
831 pactid => pactid,
832 chunk => chunk,
833 greid => null,
834 object_id => people_rec.person_id,
835 object_type => 'PER_ALL_PEOPLE_F');
836 end loop;
837 end if;
838 --
839 END action_creation;
840 -- ----------------------------------------------------------------------------
841 -- |---------------------------< initialization >-----------------------------|
842 -- ----------------------------------------------------------------------------
843 --
844 -- Purpose : This process is called for each slave process to perform
845 -- standard initialization.
846 --
847 -- Notes :
848 --
849 PROCEDURE initialization(pactid in number) IS
850 --
851 p_format_name_choice varchar2(80);
852 BEGIN
853 --Added this for bug 5641860
854 hr_general.g_data_migrator_mode := 'Y';
855 --
856 -- get parameters and initialize global variables
857 --
858 select pay_core_utils.get_parameter('LEGISLATION_CODE',
859 ppa.legislative_parameters)
860 ,pay_core_utils.get_parameter('FORMAT_NAME_CHOICE',
861 ppa.legislative_parameters)
862 into g_prm_legislation_code, p_format_name_choice
863 from pay_payroll_actions ppa
864 where ppa.payroll_action_id = pactid;
865
866 if p_format_name_choice = 'A' then
867 g_prm_format_name := null;
868 g_prm_scope := null;
869 elsif p_format_name_choice = 'O' then
870 g_prm_format_name := g_ORDER_NAME;
871 g_prm_scope := 'L';
872 elsif p_format_name_choice = 'F' then
873 g_prm_format_name := g_FULL_NAME;
874 g_prm_scope := 'L';
875 elsif p_format_name_choice = 'S' then
876 g_prm_format_name := g_LIST_NAME;
877 g_prm_scope := 'L';
878 elsif p_format_name_choice = 'U' then
879 g_prm_format_name := g_LIST_NAME;
880 g_prm_scope := 'G';
881 elsif p_format_name_choice = 'V' then
882 g_prm_format_name := g_LIST_NAME;
883 g_prm_scope := null;
884 end if;
885
886 write_log('Parameters: - Initialization Phase - ');
887 write_log('p_format_name_choice= '||p_format_name_choice);
888 write_log('p_legislation_code= '||g_prm_legislation_code);
889 write_log('format_name= '||g_prm_format_name);
890 write_log('user_format_choice= '||g_prm_scope);
891 --
892 cache_mask_info(p_legislation_code => g_prm_legislation_code
893 ,p_format_name => g_prm_format_name
894 ,p_scope => g_prm_scope);
895 --
896 cache_bg_info(p_legislation_code => g_prm_legislation_code);
897 --
898 if p_format_name_choice not in ('S','U') then
899 cache_seeded_pkg;
900 end if;
901 --
902 cache_format_names;
903 --
904 END initialization;
905 --
906 -- ----------------------------------------------------------------------------
907 -- |----------------------------< archive_data >------------------------------|
908 -- ----------------------------------------------------------------------------
909 --
910 -- Purpose : This process is called for each assignment action and performs the
911 -- processing required for each individual person.
912 --
913 -- Notes :
914 --
915 PROCEDURE archive_data(p_assactid in number,
916 p_effective_date in date) is
917 --
918 l_person_id per_all_people_f.person_id%TYPE;
919 l_legislation_code varchar2(80);
920 l_num_rows_processed number;
921 --
922 cursor csr_person is
923 select ass.object_id
924 from pay_assignment_actions ass
925 where ass.assignment_action_id = p_assactid;
926 --
927 cursor csr_get_person(cp_person_id number) is
928 select rowid row_id
929 ,FIRST_NAME
930 ,MIDDLE_NAMES
931 ,LAST_NAME
932 ,SUFFIX
933 ,PRE_NAME_ADJUNCT
934 ,TITLE
935 ,KNOWN_AS
936 ,EMAIL_ADDRESS
937 ,EMPLOYEE_NUMBER
938 ,APPLICANT_NUMBER
939 ,NPW_NUMBER
940 ,PREVIOUS_LAST_NAME
941 ,PER_INFORMATION1
942 ,PER_INFORMATION2
943 ,PER_INFORMATION3
944 ,PER_INFORMATION4
945 ,PER_INFORMATION5
946 ,PER_INFORMATION6
947 ,PER_INFORMATION7
948 ,PER_INFORMATION8
949 ,PER_INFORMATION9
950 ,PER_INFORMATION10
951 ,PER_INFORMATION11
952 ,PER_INFORMATION12
953 ,PER_INFORMATION13
954 ,PER_INFORMATION14
955 ,PER_INFORMATION15
956 ,PER_INFORMATION16
957 ,PER_INFORMATION17
958 ,PER_INFORMATION18
959 ,PER_INFORMATION19
960 ,PER_INFORMATION20
961 ,PER_INFORMATION21
962 ,PER_INFORMATION22
963 ,PER_INFORMATION23
964 ,PER_INFORMATION24
965 ,PER_INFORMATION25
966 ,PER_INFORMATION26
967 ,PER_INFORMATION27
968 ,PER_INFORMATION28
969 ,PER_INFORMATION29
970 ,PER_INFORMATION30
971 ,ATTRIBUTE1
972 ,ATTRIBUTE2
973 ,ATTRIBUTE3
974 ,ATTRIBUTE4
975 ,ATTRIBUTE5
976 ,ATTRIBUTE6
977 ,ATTRIBUTE7
978 ,ATTRIBUTE8
979 ,ATTRIBUTE9
980 ,ATTRIBUTE10
981 ,ATTRIBUTE11
982 ,ATTRIBUTE12
983 ,ATTRIBUTE13
984 ,ATTRIBUTE14
985 ,ATTRIBUTE15
986 ,ATTRIBUTE16
987 ,ATTRIBUTE17
988 ,ATTRIBUTE18
989 ,ATTRIBUTE19
990 ,ATTRIBUTE20
991 ,ATTRIBUTE21
992 ,ATTRIBUTE22
993 ,ATTRIBUTE23
994 ,ATTRIBUTE24
995 ,ATTRIBUTE25
996 ,ATTRIBUTE26
997 ,ATTRIBUTE27
998 ,ATTRIBUTE28
999 ,ATTRIBUTE29
1000 ,ATTRIBUTE30
1001 ,FULL_NAME
1002 ,ORDER_NAME
1003 ,LOCAL_NAME
1004 ,GLOBAL_NAME
1005 ,BUSINESS_GROUP_ID
1006 from per_all_people_f
1007 where person_id = cp_person_id
1008 for update nowait;
1009
1010 BEGIN
1011 l_num_rows_processed := 0;
1012 l_legislation_code := g_prm_legislation_code;
1013 --
1014 open csr_person;
1015 fetch csr_person into l_person_id;
1016 close csr_person;
1017 --
1018 for people_rec in csr_get_person(l_person_id) loop
1019 if l_legislation_code is null then
1020 l_legislation_code := get_cache_bg_leg_code(people_rec.business_group_id);
1021 end if;
1022 --
1023 sync_person_names
1024 (p_rowid => people_rec.row_id
1025 ,p_person_id => l_person_id
1026 ,p_person_rec => people_rec
1027 ,p_legislation_code => l_legislation_code
1028 ,p_format_name => g_prm_format_name
1029 ,p_user_format_choice => g_prm_scope
1030 ,p_num_rows_processed => l_num_rows_processed);
1031 end loop;
1032 --
1033 END archive_data;
1034 --
1035 --
1036 -- ----------------------------------------------------------------------------+
1037 -- |----------------------------< sync_list_name >-----------------------------|
1038 -- ----------------------------------------------------------------------------+
1039 --
1040 -- Purpose : This process will update the global_name and local_name columns
1041 -- for those records having null values in these columns.
1042 -- This runs single-threaded and therefore, can be used for Shared
1043 -- HR installations only.
1044 --
1045 -- Notes :
1046 --
1047 PROCEDURE sync_list_name(errbuf out NOCOPY varchar2
1048 ,retcode out NOCOPY number) is
1049 --
1050 l_person_id per_all_people_f.person_id%TYPE;
1051 l_legislation_code varchar2(80);
1052 l_limit natural := 100; -- Limiting the bulk collect, if not limited then bulk collect
1053 -- returns entire rows for the condition, it may affect memory
1054 l_prev_collect number := 0; -- Cumulative record count till previous fetch
1055 l_curr_collect number := 0; -- Cumulative record count including the current fetch
1056 l_diff_collect number := 0; -- To check that, whether the last fetch retrived any new
1057 -- records, if not then to exit from the loop
1058 Type t_rowids is table of ROWID
1059 index by binary_integer;
1060
1061 Type t_leg_code is table of varchar2(80) index by binary_integer;
1062 --
1063 l_rowids_tab t_rowids;
1064 l_leg_code_tab t_leg_code;
1065 l_num_rows_processed number;
1066 l_total_rows number;
1067 l_commit number;
1068 --
1069 cursor csr_get_person(cp_row_id rowid) is
1070 select rowid row_id
1071 ,FIRST_NAME
1072 ,MIDDLE_NAMES
1073 ,LAST_NAME
1074 ,SUFFIX
1075 ,PRE_NAME_ADJUNCT
1076 ,TITLE
1077 ,KNOWN_AS
1078 ,EMAIL_ADDRESS
1079 ,EMPLOYEE_NUMBER
1080 ,APPLICANT_NUMBER
1081 ,NPW_NUMBER
1082 ,PREVIOUS_LAST_NAME
1083 ,PER_INFORMATION1
1084 ,PER_INFORMATION2
1085 ,PER_INFORMATION3
1086 ,PER_INFORMATION4
1087 ,PER_INFORMATION5
1088 ,PER_INFORMATION6
1089 ,PER_INFORMATION7
1090 ,PER_INFORMATION8
1091 ,PER_INFORMATION9
1092 ,PER_INFORMATION10
1093 ,PER_INFORMATION11
1094 ,PER_INFORMATION12
1095 ,PER_INFORMATION13
1096 ,PER_INFORMATION14
1097 ,PER_INFORMATION15
1098 ,PER_INFORMATION16
1099 ,PER_INFORMATION17
1100 ,PER_INFORMATION18
1101 ,PER_INFORMATION19
1102 ,PER_INFORMATION20
1103 ,PER_INFORMATION21
1104 ,PER_INFORMATION22
1105 ,PER_INFORMATION23
1106 ,PER_INFORMATION24
1107 ,PER_INFORMATION25
1108 ,PER_INFORMATION26
1109 ,PER_INFORMATION27
1110 ,PER_INFORMATION28
1111 ,PER_INFORMATION29
1112 ,PER_INFORMATION30
1113 ,ATTRIBUTE1
1114 ,ATTRIBUTE2
1115 ,ATTRIBUTE3
1116 ,ATTRIBUTE4
1117 ,ATTRIBUTE5
1118 ,ATTRIBUTE6
1119 ,ATTRIBUTE7
1120 ,ATTRIBUTE8
1121 ,ATTRIBUTE9
1122 ,ATTRIBUTE10
1123 ,ATTRIBUTE11
1124 ,ATTRIBUTE12
1125 ,ATTRIBUTE13
1126 ,ATTRIBUTE14
1127 ,ATTRIBUTE15
1128 ,ATTRIBUTE16
1129 ,ATTRIBUTE17
1130 ,ATTRIBUTE18
1131 ,ATTRIBUTE19
1132 ,ATTRIBUTE20
1133 ,ATTRIBUTE21
1134 ,ATTRIBUTE22
1135 ,ATTRIBUTE23
1136 ,ATTRIBUTE24
1137 ,ATTRIBUTE25
1138 ,ATTRIBUTE26
1139 ,ATTRIBUTE27
1140 ,ATTRIBUTE28
1141 ,ATTRIBUTE29
1142 ,ATTRIBUTE30
1143 ,FULL_NAME
1144 ,ORDER_NAME
1145 ,LOCAL_NAME
1146 ,GLOBAL_NAME
1147 ,BUSINESS_GROUP_ID
1148 from per_all_people_f
1149 where rowid = cp_row_id
1150 for update nowait;
1151 --
1152 cursor csr_get_rows_toUpd is
1153 select peo.rowid, bg.legislation_code
1154 from per_all_people_f peo
1155 ,per_business_groups_perf bg
1156 where (peo.global_name is null or peo.local_name is null)
1157 and peo.business_group_id = bg.business_group_id;
1158 --
1159 BEGIN
1160 l_num_rows_processed := 0;
1161 l_total_rows := 0;
1162 l_commit := 100;
1163 --
1164 -- initialize mask information
1165 --
1166 g_prm_legislation_code := NULL;
1167 g_prm_format_name := NULL;
1168 g_prm_scope := null;
1169 cache_mask_info(p_legislation_code => g_prm_legislation_code
1170 ,p_format_name => g_prm_format_name
1171 ,p_scope => g_prm_scope);
1172 --
1173 open csr_get_rows_toUpd;
1174 --
1175 loop
1176 --
1177 fetch csr_get_rows_toUpd bulk collect into
1178 l_rowids_tab, l_leg_code_tab limit l_limit;
1179 l_prev_collect := l_curr_collect;
1180 l_curr_collect := csr_get_rows_toUpd%rowcount;
1181 l_diff_collect := l_curr_collect - l_prev_collect;
1182 --
1183 if l_diff_collect > 0 then
1184 --
1185 for i in l_rowids_tab.first..l_rowids_tab.last loop
1186 --
1187 -- synchronize names in table
1188 --
1189 for people_rec in csr_get_person(l_rowids_tab(i)) loop
1190 --
1191 sync_person_names
1192 (p_rowid => people_rec.row_id
1193 ,p_person_id => null
1194 ,p_person_rec => people_rec
1195 ,p_legislation_code => l_leg_code_tab(i)
1196 ,p_format_name => g_LIST_NAME
1197 ,p_user_format_choice => NULL -- this will update local and global
1198 ,p_num_rows_processed => l_num_rows_processed);
1199 l_total_rows := l_total_rows + l_num_rows_processed;
1200 end loop;
1201 if l_total_rows >= l_commit then
1202 l_total_rows := 0;
1203 commit;
1204 end if;
1205 end loop;
1206 --
1207 end if;
1208 --
1209 -- Exiting, if the present fetch is NOT returning any new rows
1210 exit when (l_diff_collect = 0);
1211 --
1212 --
1213 end loop;
1214 --
1215 close csr_get_rows_toUpd;
1216 --
1217 retcode := 0;
1218 errbuf := NULL;
1219 EXCEPTION when others then
1220 retcode := 2;
1221 errbuf := substr(SQLERRM,1,512);
1222 --
1223 END sync_list_name;
1224 --
1225 -- ----------------------------------------------------------------------------
1226 -- |-----------------------< submit_sync_names >------------------------------|
1227 -- ----------------------------------------------------------------------------
1228 --
1229 procedure submit_sync_names
1230 (errbuf out NOCOPY varchar2
1231 ,retcode out NOCOPY number
1232 ,p_effective_date varchar2
1233 ,p_action_parameter_group_id varchar2
1234 ,p_format_name_choice varchar2
1235 ,p_legislation_code varchar2
1236 ,p_format_name varchar2
1237 ,p_user_format_choice varchar2
1238 ,p_disable_who_triggers varchar2
1239 ) is
1240
1241 l_proc CONSTANT varchar2(100) := g_package||'submit_sync_names';
1242 l_business_group_id number;
1243 l_action_parameter_group_id number;
1244 l_logging pay_action_parameters.parameter_value%TYPE;
1245 l_request_id number;
1246 l_effective_date varchar2(50);
1247 l_update_date date;
1248 l_debug boolean;
1249 l_success boolean;
1250 l_status varchar2(100);
1251 l_phase varchar2(100);
1252 l_dev_status varchar2(100);
1253 l_dev_phase varchar2(100);
1254 l_message varchar2(100);
1255 l_request_data varchar2(100);
1256 l_format_name hr_name_formats.format_name%TYPE;
1257 l_user_format_choice hr_name_formats.user_format_choice%TYPE;
1258 l_call_status boolean;
1259 l_trigger_error varchar2(512);
1260 l_data_migrator_mode varchar2(30); -- For Bug 5581236
1261 --
1262 begin
1263 l_debug := FALSE;
1264 l_effective_date := p_effective_date; --fnd_date.date_to_canonical(p_effective_date);
1265 l_action_parameter_group_id := to_number(p_action_parameter_group_id);
1266 l_update_date := trunc(sysdate);
1267 l_trigger_error := null;
1268 -- For Bug 5581236
1269 l_data_migrator_mode := hr_general.g_data_migrator_mode;
1270 hr_general.g_data_migrator_mode := 'Y';
1271 --
1272 -- Perform restart checking....
1273 --
1274 l_request_data := fnd_conc_global.request_data;
1275 if l_request_data is not null then
1276 l_call_status := fnd_concurrent.get_request_status(
1277 request_id => l_request_data,
1278 phase => l_phase,
1279 status => l_status,
1280 dev_phase => l_dev_phase,
1281 dev_status => l_dev_status,
1282 message => l_message);
1283
1284
1285 if l_dev_phase = 'COMPLETE' and l_dev_status = 'ERROR' then
1286 errbuf := l_message;
1287 retcode := 2;
1288 else
1289 l_trigger_error := null;
1290 if p_disable_who_triggers = 'Y' then
1291 alter_who_trigger('ENABLE', l_trigger_error);
1292 end if;
1293 if l_trigger_error is not null then
1294 errbuf := l_trigger_error;
1295 retcode := 2;
1296 else
1297 retcode := 0;
1298 end if;
1299 end if;
1300 return;
1301 end if;
1302 --
1303 -- verify "disable who trigger" parameter
1304 --
1305 if p_disable_who_triggers = 'Y' then
1306 alter_who_trigger('DISABLE', l_trigger_error);
1307 end if;
1308 if l_trigger_error is not null then
1309 errbuf := l_trigger_error;
1310 retcode := 2;
1311 else
1312 --
1313 if hr_general.chk_product_installed(800) = 'TRUE' then
1314
1315
1316 l_request_id := fnd_request.submit_request
1317 (application => 'PER',
1318 program => 'PERNMFT_INT',
1319 sub_request => TRUE,
1320 argument1 => 'ARCHIVE',
1321 argument2 => 'PERNMFT', -- report type
1322 argument3 => 'PERNMFT', -- report qualifier
1323 argument4 => l_effective_date, -- start date
1324 argument5 => l_effective_date, -- effective date
1325 argument6 => 'PROCESS', -- report category
1326 argument7 => fnd_profile.value('PER_BUSINESS_GROUP_ID'),
1327 argument8 => null, -- magnetic filename
1328 argument9 => null, -- report filename
1329 argument10 => l_action_parameter_group_id,
1330 argument11 => 'FORMAT_NAME_CHOICE='||p_format_name_choice,
1331 argument12 => 'LEGISLATION_CODE='||p_legislation_code,
1332 argument13 => 'FORMAT_NAME='||p_format_name,
1333 argument14 => 'USER_FORMAT_CHOICE='||p_user_format_choice);
1334 --
1335 -- Set the status of the process and then exit until the sub-requests
1336 -- have completed.
1337 --
1338 if l_request_id = 0 then
1339 if p_disable_who_triggers = 'Y' then
1340 alter_who_trigger('ENABLE', l_trigger_error);
1341 end if;
1342 errbuf := fnd_message.get;
1343 retcode := 2;
1344 else
1345 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
1346 request_data => l_request_id );
1347 retcode := 0;
1348 end if;
1349 else
1350 --
1351 -- this is a SHARED HR installation
1352 --
1353 if p_format_name = 'V' then -- LIST NAME format (local and global)
1354 sync_list_name(errbuf, retcode);
1355 if p_disable_who_triggers = 'Y' then
1356 alter_who_trigger('ENABLE', l_trigger_error);
1357 end if; -- prm setting
1358 end if; -- format = List Name?
1359 --
1360 end if; -- prod installed
1361 end if; -- trigger error
1362 -- For Bug 5581236
1363 hr_general.g_data_migrator_mode := l_data_migrator_mode;
1364 end submit_sync_names;
1365 --
1366 -- --------------------------------------------------------------------------+
1367 -- ValidateRun
1368 -- --------------------------------------------------------------------------+
1369 --
1370 PROCEDURE ValidateRun(p_result OUT nocopy varchar2) IS
1371 l_result varchar2(10);
1372 BEGIN
1373 l_result := hr_update_utility.isUpdateComplete
1374 (p_app_shortname => 'PER'
1375 ,p_function_name => null
1376 ,p_business_group_id => null
1377 ,p_update_name => 'PERNMFT');
1378 --
1379 if l_result = 'FALSE' then
1380 p_result := 'TRUE';
1381 else
1382 p_result := 'FALSE';
1383 end if;
1384 --
1385 END ValidateRun;
1386 --
1387 --
1388 END HR_NMF_SYNC;