1 PACKAGE BODY hr_gl_sync_orgs AS
2 -- $Header: hrglsync.pkb 120.5 2008/04/01 10:51:54 ktithy noship $
3
4 --
5 -- Due to potentially heavy memory usage by this package, restrict
6 -- package data life to current call only.
7 --
8 -- Fix for bug 4445934. Comment out the pragma serially_reusable.
9 --
10 -- PRAGMA SERIALLY_REUSABLE;
11
12 --
13 -- Package globals.
14 --
15 g_package VARCHAR2(15) := 'hr_gl_sync_orgs';
16 g_org_name_max_length NUMBER := 0;
17 g_max_retries NUMBER := 10; -- Max. tries to make ORG Name unique.
18 g_debug_level pay_action_parameters.parameter_value%TYPE := NULL;
19 g_export_dir VARCHAR2(300) := NULL;
20 g_class_profile VARCHAR2(10) := NULL;
21 g_org_name_format VARCHAR2(60) := NULL;
22 g_appl_short_name VARCHAR2(3) := 'PER';
23 g_per_schema VARCHAR2(10) := NULL;
24 g_temp_table_name VARCHAR2(50) := NULL;
25
26 --
27 -- Write to the LOG file.
28 --
29 PROCEDURE writelog( p_text IN VARCHAR2
30 , p_debug_mode IN VARCHAR2 DEFAULT 'N'
31 ) IS
32 BEGIN
33 IF p_debug_mode = 'Y' THEN
34 IF g_debug_level = 'DEBUG' THEN
35 fnd_file.put_line(fnd_file.log, p_text);
36 END IF;
37 ELSE
38 fnd_file.put_line(fnd_file.log, p_text);
39 END IF;
40
41 hr_utility.set_location('LOG Message - '||p_text, 999);
42 EXCEPTION
43 WHEN OTHERS THEN
44 IF SQLCODE = -20100 THEN
45 hr_utility.set_location(SUBSTR(p_text,1,100), 990);
46 ELSE
47 hr_utility.set_location('Procedure writelog encountered unknown exception', 995);
48 RAISE;
49 END IF;
50 END writelog;
51
52 --
53 -- Drop temporary table.
54 --
55 PROCEDURE drop_temp_table IS
56
57 -- Local variables
58 l_proc VARCHAR2(50) := g_package||'.drop_temp_table';
59
60 BEGIN
61
62 hr_utility.set_location('Entering: '|| g_temp_table_name, 10);
63
64 EXECUTE IMMEDIATE 'DROP TABLE '||g_temp_table_name;
65
66 hr_utility.set_location('Leaving: '|| l_proc, 20);
67
68 EXCEPTION
69
70 WHEN OTHERS THEN
71 IF SQLCODE = -942 THEN
72 -- Table not present for dropping. Ignore as this could
73 -- be the first run.
74 hr_utility.set_location('Leaving: '|| l_proc, 30);
75 ELSE
76 hr_utility.set_location('Leaving: '|| l_proc, 40);
77 hr_utility.set_location(SQLERRM, 45);
78 writelog(SQLERRM,'N');
79 RAISE;
80 END IF;
81
82 END drop_temp_table;
83
84 --
85 -- Create temporary table.
86 --
87 PROCEDURE create_temp_table( p_mode IN VARCHAR2
88 ) IS
89
90 -- Local variables
91 l_proc VARCHAR2(50) := g_package||'.create_temp_table';
92 l_sql_str VARCHAR2(600);
93
94 BEGIN
95
96 hr_utility.set_location('Entering: '|| l_proc, 10);
97
98 -- Drop the temporary table if it already exists
99 drop_temp_table();
100
101 hr_utility.set_location(l_proc, 20);
102
103 l_sql_str := 'CREATE TABLE '||g_temp_table_name||
104 ' ( coa_id NUMBER(15)' ||
105 ' , co VARCHAR2(240)'||
106 ' , co_vs_id NUMBER(15)' ||
107 ' , cc VARCHAR2(240)'||
108 ' , cc_vs_id NUMBER(15)';
109
110 IF p_mode = 'SYNCHRONIZE' THEN
111 hr_utility.set_location(l_proc, 30);
112 l_sql_str := l_sql_str ||
113 ' , ccid NUMBER(15)' ||
114 ' , org_id NUMBER(15)';
115 END IF;
116
117 IF p_mode = 'EXPORT' THEN
118 hr_utility.set_location(l_proc, 40);
119 l_sql_str := l_sql_str ||
120 ' , co_desc VARCHAR2(240)'||
121 ' , cc_desc VARCHAR2(240)'||
122 ' , co_vs_name VARCHAR2(60)' ||
123 ' , cc_vs_name VARCHAR2(60)' ||
124 ' , org_name VARCHAR2(240)'||
125 ' , org_start_date DATE';
126 END IF;
127
128 l_sql_str := l_sql_str || ' )';
129
130 hr_utility.set_location(l_proc, 50);
131
132 -- Create a new instance of the temporary table
133 EXECUTE IMMEDIATE l_sql_str;
134
135 hr_utility.set_location('Leaving: '|| l_proc, 60);
136
137 EXCEPTION
138
139 WHEN OTHERS THEN
140 hr_utility.set_location('Leaving: '|| l_proc, 70);
141 hr_utility.set_location(SQLERRM, 75);
142 writelog(SQLERRM,'N');
143 RAISE;
144
145 END create_temp_table;
146
147 --
148 -- Initiate LOG and OUT files.
149 --
150 FUNCTION open_logs( p_mode IN VARCHAR2
151 , p_business_group_id IN NUMBER
152 , p_ccid IN NUMBER
153 ) RETURN NUMBER IS
154
155 -- Local variables
156 l_proc VARCHAR2(50) := g_package||'.open_logs';
157 l_retcode NUMBER := 0;
158 l_dir VARCHAR2(300) := NULL;
159
160 BEGIN
161
162 hr_utility.set_location('Entering: '|| l_proc, 10);
163
164 -- Open LOG and OUT files.
165 IF p_mode = 'SINGLE_ORG' THEN
166 hr_utility.set_location(l_proc, 20);
167 fnd_file.put_names( p_mode||p_ccid||'.log'
168 , p_mode||p_ccid||'.out'
169 , l_dir
170 );
171 ELSE -- mode is not single org
172 hr_utility.set_location(l_proc, 30);
173 fnd_file.put_names( p_mode||p_business_group_id||'.log'
174 , p_mode||p_business_group_id||'.out'
175 , l_dir
176 );
177 END IF;
178
179 hr_utility.set_location('Leaving: '|| l_proc, 40);
180
181 RETURN l_retcode;
182
183 EXCEPTION
184
185 WHEN OTHERS THEN
186 hr_utility.set_location('Leaving: '||l_proc, 50);
187 hr_utility.set_location(SQLERRM, 55);
188 writelog(SQLERRM,'N');
189 l_retcode := 2;
190 RETURN l_retcode;
191
192 END open_logs;
193
194 --
195 -- Initiate export header and data files.
196 --
197 FUNCTION open_export_files( p_bg_id IN NUMBER
198 , p_bg_name IN VARCHAR2
199 , p_hdr_file IN OUT NOCOPY VARCHAR2
200 , p_dat_file IN OUT NOCOPY VARCHAR2
201 , p_hdr_file_handle IN OUT NOCOPY utl_file.file_type
202 , p_dat_file_handle IN OUT NOCOPY utl_file.file_type
203 ) RETURN NUMBER IS
204
205 -- Local variables
206 l_proc VARCHAR2(50) := g_package||'.open_export_files';
207 l_retcode NUMBER := 0;
208 l_hdr_file_handle utl_file.file_type;
209 l_dat_file_handle utl_file.file_type;
210
211 -- Local exceptions
212 e_invalid_dir EXCEPTION;
213
214 BEGIN
215
216 hr_utility.set_location('Entering: '|| l_proc, 10);
217
218 -- Build export header abd data file names
219 p_hdr_file := 'GLCC_' || REPLACE(SUBSTR(p_bg_name,1,30),' ','_') ||
220 TO_CHAR(p_bg_id) || '.hdr';
221 p_dat_file := 'GLCC_' || REPLACE(SUBSTR(p_bg_name,1,30),' ','_') ||
222 TO_CHAR(p_bg_id) || '.txt';
223 hr_utility.set_message(800,'PER_50174_HDR_FILE_NAME');
224 fnd_message.set_token('HDR_FILE_NAME',p_hdr_file);
225 fnd_message.set_token('FILE_DIR',g_export_dir);
226 writelog(fnd_message.get(),'N');
227 hr_utility.set_message(800,'PER_50175_DAT_FILE_NAME');
228 fnd_message.set_token('DAT_FILE_NAME',p_dat_file);
229 fnd_message.set_token('FILE_DIR',g_export_dir);
230 writelog(fnd_message.get(),'N');
231
232 -- Open export header file
233 BEGIN
234 hr_utility.set_location(l_proc, 20);
235 l_hdr_file_handle := utl_file.fopen( g_export_dir
236 , p_hdr_file
237 , 'w'
238 );
239 hr_utility.set_location(l_proc, 30);
240 l_dat_file_handle := utl_file.fopen( g_export_dir
241 , p_dat_file
242 , 'w'
243 );
244 hr_utility.set_location(l_proc, 40);
245 p_hdr_file_handle := l_hdr_file_handle;
246 p_dat_file_handle := l_dat_file_handle;
247 EXCEPTION
248 WHEN OTHERS THEN
249 hr_utility.set_location(l_proc, 50);
250 RAISE e_invalid_dir;
251 END; -- anonymous block
252
253 hr_utility.set_location('Leaving: '|| l_proc, 60);
254
255 RETURN l_retcode;
256
257 EXCEPTION
258
259 WHEN e_invalid_dir THEN
260 hr_utility.set_location('Leaving: '||l_proc, 70);
261 hr_utility.set_message(801,'HR_289426_INV_EXC_DIR');
262 writelog(fnd_message.get(),'N');
263 l_retcode := 2;
264 RETURN l_retcode;
265
266 WHEN OTHERS THEN
267 hr_utility.set_location('Leaving: '||l_proc, 80);
268 hr_utility.set_location(SQLERRM, 85);
269 writelog(SQLERRM,'N');
270 writelog('Error opening export files','Y');
271 l_retcode := 2;
272 RETURN l_retcode;
273
274 END open_export_files;
275
276 --
277 -- Close export header and data files.
278 --
279 FUNCTION close_export_files( p_hdr_file_handle IN utl_file.file_type
280 , p_dat_file_handle IN utl_file.file_type
281 ) RETURN NUMBER IS
282
283 -- Local variables
284 l_proc VARCHAR2(50) := g_package||'.close_export_files';
285 l_retcode NUMBER := 0;
286 l_hdr_file_handle utl_file.file_type;
287 l_dat_file_handle utl_file.file_type;
288
289 BEGIN
290
291 hr_utility.set_location('Entering: '|| l_proc, 10);
292
293 l_hdr_file_handle := p_hdr_file_handle;
294 l_dat_file_handle := p_dat_file_handle;
295 hr_utility.set_location(l_proc, 20);
296 utl_file.fclose(l_hdr_file_handle);
297 hr_utility.set_location(l_proc, 30);
298 utl_file.fclose(l_dat_file_handle);
299
300 hr_utility.set_location('Leaving: '|| l_proc, 40);
301
302 RETURN l_retcode;
303
304 EXCEPTION
305
306 WHEN OTHERS THEN
307 hr_utility.set_location('Leaving: '||l_proc, 50);
308 hr_utility.set_location(SQLERRM, 55);
309 writelog(SQLERRM,'N');
310 writelog('Error closing export files','Y');
311 l_retcode := 2;
312 RETURN l_retcode;
313
314 END close_export_files;
315
316 --
317 -- Get Product Schema Name
318 --
319 FUNCTION get_schema( p_product IN VARCHAR2
320 ) RETURN VARCHAR2 IS
321
322 -- Local Variables
323 l_proc VARCHAR2(50) := g_package||'.get_schema';
324 l_dummy1 VARCHAR2(2000);
325 l_dummy2 VARCHAR2(2000);
326 l_schema VARCHAR2(400);
327
328 BEGIN
329
330 hr_utility.set_location('Entering: '|| l_proc, 10);
331
332 IF fnd_installation.get_app_info( p_product
333 , l_dummy1
334 , l_dummy2
335 , l_schema) THEN
336 hr_utility.set_location('Leaving: '|| l_proc, 20);
337 RETURN l_schema;
338 ELSE
339 hr_utility.set_location('Leaving: '|| l_proc, 30);
340 RETURN NULL;
341 END IF;
342
343 END get_schema;
344
345 --
346 -- Get details for a value set value
347 --
348 FUNCTION get_vs_value_details( p_vs_id IN NUMBER
349 , p_code IN VARCHAR2
350 , p_description IN OUT NOCOPY VARCHAR2
351 , p_start_date_active IN OUT NOCOPY DATE
352 , p_end_date_active OUT NOCOPY DATE
353 ) RETURN NUMBER IS
354
355 -- Local Variables
356 l_proc VARCHAR2(50) := g_package||'.get_vs_value_details';
357 l_retcode NUMBER := 0;
358
359 -- Local cursor to fetch flex value set value details
360 CURSOR c_vs_value_dets( cp_vs_id IN NUMBER
361 , cp_code IN VARCHAR2
362 ) IS
363 SELECT description
364 , start_date_active
365 , end_date_active
366 FROM fnd_flex_values_vl
367 WHERE flex_value_set_id = cp_vs_id
368 AND flex_value = cp_code;
369
370 BEGIN
371
372 hr_utility.set_location('Entering: '|| l_proc, 10);
373
374 OPEN c_vs_value_dets(p_vs_id, p_code);
375 FETCH c_vs_value_dets INTO p_description, p_start_date_active, p_end_date_active;
376 IF c_vs_value_dets%FOUND THEN
377 hr_utility.set_location(l_proc, 20);
378 ELSE
379 hr_utility.set_location(l_proc, 30);
380 p_description := NULL;
381 p_start_date_active := NULL;
382 p_end_date_active := NULL;
383 END IF;
384 CLOSE c_vs_value_dets;
385
386 hr_utility.set_location('Leaving: '|| l_proc, 40);
387
388 RETURN l_retcode;
389
390 EXCEPTION
391
392 WHEN OTHERS THEN
393 hr_utility.set_location('Leaving: '|| l_proc, 50);
394 hr_utility.set_location(SQLERRM, 55);
395 writelog(SQLERRM,'N');
396 IF c_vs_value_dets%ISOPEN THEN
397 CLOSE c_vs_value_dets;
398 END IF;
399 l_retcode := 2;
400 RETURN l_retcode;
401
402 END get_vs_value_details;
403
404 --
405 -- Get value set details
406 --
407 FUNCTION get_vs_details( p_vs_id IN NUMBER
408 , p_vs_name IN OUT NOCOPY VARCHAR2
409 ) RETURN NUMBER IS
410
411 -- Local Variables
412 l_proc VARCHAR2(50) := g_package||'.get_vs_details';
413 l_retcode NUMBER := 0;
414
415 -- Local cursor to fetch flex value set name
416 CURSOR c_vs_dets( cp_vs_id IN NUMBER
417 ) IS
418 SELECT flex_value_set_name
419 FROM fnd_flex_value_sets
420 WHERE flex_value_set_id = cp_vs_id;
421
422 BEGIN
423
424 hr_utility.set_location('Entering: '|| l_proc, 10);
425
426 OPEN c_vs_dets(p_vs_id);
427 FETCH c_vs_dets INTO p_vs_name;
428 IF c_vs_dets%FOUND THEN
429 hr_utility.set_location(l_proc, 20);
430 ELSE
431 hr_utility.set_location(l_proc, 30);
432 p_vs_name := NULL;
433 END IF;
434 CLOSE c_vs_dets;
435
436 hr_utility.set_location('Leaving: '|| l_proc, 40);
437
438 RETURN l_retcode;
439
440 EXCEPTION
441
442 WHEN OTHERS THEN
443 hr_utility.set_location('Leaving: '|| l_proc, 50);
444 hr_utility.set_location(SQLERRM, 55);
445 writelog(SQLERRM,'N');
446 IF c_vs_dets%ISOPEN THEN
447 CLOSE c_vs_dets;
448 END IF;
449 l_retcode := 2;
450 RETURN l_retcode;
451
452 END get_vs_details;
453
454 --
455 -- Get value set for segment.
456 --
457 FUNCTION get_vs_for_seg( p_coa_id IN NUMBER
458 , p_segment IN VARCHAR2
459 , p_vs_id IN OUT NOCOPY NUMBER
460 ) RETURN NUMBER IS
461
462 -- Local variables
463 l_proc VARCHAR2(50) := g_package||'.get_vs_for_seg';
464 l_retcode NUMBER := 0;
465 l_vs_name fnd_flex_value_sets.flex_value_set_name%TYPE;
466
467 TYPE cur_type IS REF CURSOR;
468 c_list cur_type;
469
470 BEGIN
471
472 hr_utility.set_location('Entering: '|| l_proc, 10);
473 p_vs_id := -1;
474
475 OPEN c_list FOR ' SELECT VS.flex_value_set_id,'||
476 ' VS.flex_value_set_name'||
477 ' FROM fnd_id_flex_segments_vl SEG,'||
478 ' fnd_flex_value_sets VS'||
479 ' WHERE UPPER(SEG.id_flex_code) = ''GL#'''||
480 ' AND SEG.application_id = 101'||
481 ' AND SEG.flex_value_set_id = VS.flex_value_set_id'||
482 ' AND SEG.enabled_flag = ''Y'''||
483 ' AND id_flex_num = :1'||
484 ' AND application_column_name = :2'
485 USING p_coa_id, p_segment;
486 FETCH c_list INTO p_vs_id, l_vs_name;
487 CLOSE c_list;
488
489 hr_utility.set_location(p_segment||' valueset is '||
490 l_vs_name||'('||p_vs_id||')', 20);
491
492 hr_utility.set_location('Leaving: '|| l_proc, 30);
493
494 RETURN l_retcode;
495
496 EXCEPTION
497
498 WHEN OTHERS THEN
499 hr_utility.set_location('Leaving: '|| l_proc, 40);
500 hr_utility.set_location(SQLERRM, 45);
501 writelog(SQLERRM,'N');
502 l_retcode := 2;
503 RETURN l_retcode;
504
505 END get_vs_for_seg;
506
507 --
508 -- Get segment for qualifier.
509 --
510 FUNCTION get_seg_for_qual( p_coa_id IN NUMBER
511 , p_qualifier IN VARCHAR2
512 , p_segment IN OUT NOCOPY VARCHAR2
513 ) RETURN NUMBER IS
514
515 -- Local variables
516 l_proc VARCHAR2(50) := g_package||'.get_seg_for_qual';
517 l_retcode NUMBER := 0;
518
519 BEGIN
520
521 hr_utility.set_location('Entering: '|| l_proc, 10);
522
523 IF fnd_flex_apis.get_segment_column( 101
524 , 'GL#'
525 , p_coa_id
526 , p_qualifier
527 , p_segment
528 ) THEN
529 hr_utility.set_location(p_qualifier||' segment: '||p_segment, 20);
530 hr_utility.set_location('Leaving: '|| l_proc, 30);
531 RETURN l_retcode;
532 ELSE
533 hr_utility.set_location('Leaving: '|| l_proc, 40);
534 l_retcode := 2;
535 RETURN l_retcode;
536 END IF;
537
538 END get_seg_for_qual;
539
540 --
541 -- Derive the company and cost center value sets from the chart of
542 -- accounts identifier.
543 --
544 FUNCTION derive_value_sets( p_coa_id IN NUMBER
545 , p_co_seg IN OUT NOCOPY VARCHAR2
546 , p_cc_seg IN OUT NOCOPY VARCHAR2
547 , p_co_vs_id IN OUT NOCOPY NUMBER
548 , p_cc_vs_id IN OUT NOCOPY NUMBER
549 ) RETURN NUMBER IS
550
551 -- Local variables
552 l_proc VARCHAR2(50) := g_package||'.derive_value_sets';
553 l_retcode NUMBER := 0;
554
555 BEGIN
556
557 hr_utility.set_location('Entering: '|| l_proc, 10);
558
559 p_co_seg := NULL;
560 p_cc_seg := NULL;
561 p_co_vs_id := -1;
562 p_cc_vs_id := -1;
563
564 -- Get company segment
565 IF l_retcode = 0 THEN
566 hr_utility.set_location(l_proc, 20);
567 l_retcode := get_seg_for_qual( p_coa_id
568 , 'GL_BALANCING'
569 , p_co_seg
570 );
571 END IF;
572
573 -- Get cost center segment
574 IF l_retcode = 0 THEN
575 hr_utility.set_location(l_proc, 30);
576 l_retcode := get_seg_for_qual( p_coa_id
577 , 'FA_COST_CTR'
578 , p_cc_seg
579 );
580 END IF;
581
582 -- Get company value set
583 IF l_retcode = 0 THEN
584 hr_utility.set_location(l_proc, 40);
585 l_retcode := get_vs_for_seg( p_coa_id
586 , p_co_seg
587 , p_co_vs_id
588 );
589 END IF;
590
591 -- Get cost cente value set
592 IF l_retcode = 0 THEN
593 hr_utility.set_location(l_proc, 50);
594 l_retcode := get_vs_for_seg( p_coa_id
595 , p_cc_seg
596 , p_cc_vs_id
597 );
598 END IF;
599
600 IF p_co_seg IS NULL OR p_cc_seg IS NULL OR p_co_vs_id = -1 OR p_cc_vs_id = -1 THEN
601 hr_utility.set_location(l_proc, 60);
602
603 -- Report missing flexfield details
604 hr_utility.set_message(800,'PER_289604_WRN_CHRT_ACC_ID');
605 fnd_message.set_token('ID',p_coa_id);
606 writelog(fnd_message.get(),'N');
607
608 IF p_co_seg IS NULL THEN
609 hr_utility.set_location(l_proc, 70);
610 writelog(fnd_message.get_string(800,'PER_289605_WRN_COMP_SEG'),'N');
611 END IF;
612
613 IF p_cc_seg IS NULL THEN
614 hr_utility.set_location(l_proc, 80);
615 writelog(fnd_message.get_string(800,'PER_289606_WRN_CC_SEG'),'N');
616 END IF;
617
618 IF p_co_vs_id = -1 THEN
619 hr_utility.set_location(l_proc, 90);
620 writelog(fnd_message.get_string(800,'PER_289607_WRN_COMP_VS'),'N');
621 END IF;
622
623 IF p_cc_vs_id = -1 THEN
624 hr_utility.set_location(l_proc, 100);
625 writelog(fnd_message.get_string(800,'PER_289608_WRN_CC_VS'),'N');
626 END IF;
627
628 l_retcode := 1;
629 END IF; -- if values not fetched
630
631 hr_utility.set_location('Leaving: '|| l_proc, 110);
632
633 RETURN l_retcode;
634
635 END derive_value_sets;
636
637 --
638 -- Get the business group identifier for the given company details.
639 --
640 FUNCTION get_business_group_id( p_co_vs_id IN NUMBER
641 , p_co IN VARCHAR2
642 , p_bg_id IN OUT NOCOPY NUMBER
643 ) RETURN NUMBER IS
644
645 -- Local Variables
646 l_proc VARCHAR2(50) := g_package||'.get_business_group_id';
647 l_retcode NUMBER := 0;
648
649 TYPE number_table IS TABLE OF NUMBER;
650 lt_bg_id_tab number_table;
651
652 -- Local cursor to fetch business group id based on company details.
653 CURSOR c_bg_id( cp_co_vs_id IN NUMBER
654 , cp_co IN VARCHAR2
655 ) IS
656 SELECT DISTINCT UNITS.business_group_id
657 FROM hr_all_organization_units UNITS
658 , hr_organization_information CLASS
659 , hr_organization_information CC
660 WHERE UNITS.organization_id = CLASS.organization_id
661 AND CLASS.org_information_context = 'CLASS'
662 AND CLASS.org_information1 = 'CC'
663 AND CLASS.organization_id = CC.organization_id
664 AND CC.org_information_context = 'Company Cost Center'
665 AND CC.org_information2 = cp_co_vs_id
666 AND CC.org_information3 = cp_co
667 AND CC.org_information4 IS NULL
668 AND CC.org_information5 IS NULL;
669
670 BEGIN
671
672 hr_utility.set_location('Entering: '|| l_proc, 10);
673
674 OPEN c_bg_id(p_co_vs_id, p_co);
675 FETCH c_bg_id BULK COLLECT INTO lt_bg_id_tab LIMIT 10;
676 CLOSE c_bg_id;
677
678 hr_utility.set_location(l_proc, 20);
679
680 IF (lt_bg_id_tab.COUNT > 1) THEN
681 -- This company exists in more than one business group
682 hr_utility.set_location(l_proc, 30);
683 p_bg_id := -1;
684 l_retcode := 2;
685 writelog(fnd_message.get_string('PER','HR_289491_CO_MULT_BG'),'N');
686 ELSIF (lt_bg_id_tab.COUNT = 0) THEN
687 -- The company does not exist in any business group.
688 hr_utility.set_location(l_proc, 40);
689 p_bg_id := -1;
690 l_retcode := 2;
691 writelog(fnd_message.get_string('PER','HR_289601_CO_NO_BG'),'N');
692 ELSIF (lt_bg_id_tab.COUNT = 1) THEN
693 hr_utility.set_location(l_proc, 50);
694 p_bg_id := lt_bg_id_tab(1);
695 hr_utility.set_location('Business Group Id:'||p_bg_id, 50);
696 END IF;
697
698 hr_utility.set_location('Leaving: '|| l_proc, 60);
699
700 RETURN l_retcode;
701
702 EXCEPTION
703
704 WHEN OTHERS THEN
705 hr_utility.set_location('Leaving: '|| l_proc, 70);
706 hr_utility.set_location(SQLERRM, 75);
707 writelog(SQLERRM,'N');
708 IF c_bg_id%ISOPEN THEN
709 CLOSE c_bg_id;
710 END IF;
711 l_retcode := 2;
712 RETURN l_retcode;
713
714 END get_business_group_id;
715
716 --
717 -- Get the chart of accounts id for the code combination id
718 --
719 FUNCTION get_chart_of_accounts_id( p_ccid IN NUMBER
720 , p_coa_id IN OUT NOCOPY NUMBER
721 ) RETURN NUMBER IS
722
723 -- Local Variables
724 l_proc VARCHAR2(50) := g_package||'.get_chart_of_accounts_id';
725 l_retcode NUMBER := 0;
726
727 -- Local cursor to fetch chart of accounts id based on code combination id.
728 CURSOR c_coa_id( cp_ccid IN NUMBER
729 ) IS
730 SELECT chart_of_accounts_id
731 FROM gl_code_combinations
732 WHERE code_combination_id = cp_ccid;
733
734 BEGIN
735
736 hr_utility.set_location('Entering: '|| l_proc, 10);
737
738 OPEN c_coa_id(p_ccid);
739 FETCH c_coa_id INTO p_coa_id;
740 IF c_coa_id%FOUND THEN
741 hr_utility.set_location(l_proc, 20);
742 ELSE
743 hr_utility.set_location(l_proc, 30);
744 p_coa_id := -1;
745 l_retcode := 2;
746 END IF;
747 CLOSE c_coa_id;
748
749 hr_utility.set_location('Leaving: '|| l_proc, 40);
750
751 RETURN l_retcode;
752
753 EXCEPTION
754
755 WHEN OTHERS THEN
756 hr_utility.set_location('Leaving: '|| l_proc, 70);
757 hr_utility.set_location(SQLERRM, 75);
758 writelog(SQLERRM,'N');
759 IF c_coa_id%ISOPEN THEN
760 CLOSE c_coa_id;
761 END IF;
762 l_retcode := 2;
763 RETURN l_retcode;
764
765 END get_chart_of_accounts_id;
766
767 --
768 -- Check if company cost center org info type exists
769 --
770 FUNCTION co_cc_org_info_type_exists( p_org_id IN NUMBER
771 , p_co IN OUT NOCOPY VARCHAR2
772 , p_co_vs_id IN OUT NOCOPY NUMBER
773 , p_cc IN OUT NOCOPY VARCHAR2
774 , p_cc_vs_id IN OUT NOCOPY NUMBER
775 ) RETURN BOOLEAN IS
776
777 -- Local variables
778 l_proc VARCHAR2(50) := g_package||'.co_cc_org_info_type_exists';
779 l_exists BOOLEAN;
780
781 -- Cursor to check if co cc org info type exists
782 CURSOR c_chk_co_cc_org_info_type IS
783 SELECT org_information2
784 , org_information3
785 , org_information4
786 , org_information5
787 FROM hr_organization_information
788 WHERE organization_id = p_org_id
789 AND org_information_context = 'Company Cost Center';
790
791 BEGIN
792
793 hr_utility.set_location('Entering: '|| l_proc, 10);
794
795 OPEN c_chk_co_cc_org_info_type;
796 FETCH c_chk_co_cc_org_info_type INTO p_co_vs_id, p_co, p_cc_vs_id, p_cc;
797 IF c_chk_co_cc_org_info_type%NOTFOUND THEN
798 hr_utility.set_location(l_proc, 20);
799 l_exists := FALSE;
800 ELSE
801 hr_utility.set_location(l_proc, 30);
802 l_exists := TRUE;
803 END IF;
804 CLOSE c_chk_co_cc_org_info_type;
805
806 hr_utility.set_location('Leaving: '|| l_proc, 40);
807
808 RETURN l_exists;
809
810 EXCEPTION
811
812 WHEN OTHERS THEN
813 hr_utility.set_location('Leaving: '|| l_proc, 50);
814 IF c_chk_co_cc_org_info_type%ISOPEN THEN
815 CLOSE c_chk_co_cc_org_info_type;
816 END IF;
817 l_exists := FALSE;
818 RETURN l_exists;
819
820 END co_cc_org_info_type_exists;
821
822 --
823 -- Check if HR Organization classification exists
824 --
825 FUNCTION hr_org_class_exists( p_org_id IN NUMBER
826 ) RETURN BOOLEAN IS
827
828 -- Local variables
829 l_proc VARCHAR2(50) := g_package||'.hr_org_class_exists';
830 l_exists BOOLEAN;
831 l_dummy NUMBER;
832
833 -- Cursor to check if classification exists
834 CURSOR c_chk_hr_org_class IS
835 SELECT 1
836 FROM hr_organization_information
837 WHERE organization_id = p_org_id
838 --AND org_information_context = 'HR_ORG';
839 AND org_information_context = 'CLASS'
840 AND org_information1 = 'HR_ORG';
841
842 BEGIN
843
844 hr_utility.set_location('Entering: '|| l_proc, 10);
845
846 OPEN c_chk_hr_org_class;
847 FETCH c_chk_hr_org_class INTO l_dummy;
848 IF c_chk_hr_org_class%NOTFOUND THEN
849 hr_utility.set_location(l_proc, 20);
850 l_exists := FALSE;
851 ELSE
852 hr_utility.set_location(l_proc, 30);
853 l_exists := TRUE;
854 END IF;
855 CLOSE c_chk_hr_org_class;
856
857 hr_utility.set_location('Leaving: '|| l_proc, 40);
858
859 RETURN l_exists;
860
861 EXCEPTION
862
863 WHEN OTHERS THEN
864 hr_utility.set_location('Leaving: '|| l_proc, 50);
865 IF c_chk_hr_org_class%ISOPEN THEN
866 CLOSE c_chk_hr_org_class;
867 END IF;
868 l_exists := FALSE;
869 RETURN l_exists;
870
871 END hr_org_class_exists;
872
873 --
874 -- Check if Company Cost Center classification exists
875 --
876 FUNCTION co_cc_class_exists( p_org_id IN NUMBER
877 , p_org_info2 IN OUT NOCOPY VARCHAR2
878 , p_org_info_id IN OUT NOCOPY NUMBER
879 , p_ovn IN OUT NOCOPY NUMBER
880 ) RETURN BOOLEAN IS
881
882 -- Local variables
883 l_proc VARCHAR2(50) := g_package||'.co_cc_class_exists';
884 l_exists BOOLEAN;
885
886 -- Cursor to check if classification exists
887 CURSOR c_chk_co_cc_class IS
888 SELECT org_information2
889 ,org_information_id
890 ,object_version_number
891 FROM hr_organization_information
892 WHERE organization_id = p_org_id
893 AND org_information_context = 'CLASS'
894 AND org_information1 = 'CC';
895
896 BEGIN
897
898 hr_utility.set_location('Entering: '|| l_proc, 10);
899
900 OPEN c_chk_co_cc_class;
901 FETCH c_chk_co_cc_class INTO p_org_info2, p_org_info_id, p_ovn;
902 IF c_chk_co_cc_class%NOTFOUND THEN
903 hr_utility.set_location(l_proc, 20);
904 l_exists := FALSE;
905 ELSE
906 hr_utility.set_location(l_proc, 30);
907 l_exists := TRUE;
908 END IF;
909 CLOSE c_chk_co_cc_class;
910
911 hr_utility.set_location('Leaving: '|| l_proc, 40);
912
913 RETURN l_exists;
914
915 EXCEPTION
916
917 WHEN OTHERS THEN
918 hr_utility.set_location('Leaving: '|| l_proc, 50);
919 IF c_chk_co_cc_class%ISOPEN THEN
920 CLOSE c_chk_co_cc_class;
921 END IF;
922 l_exists := FALSE;
923 RETURN l_exists;
924
925 END co_cc_class_exists;
926
927 --
928 -- Create company cost center organization information type.
929 --
930 FUNCTION create_co_cc_org_info_type( p_class_id IN NUMBER
931 , p_class_ovn IN NUMBER
932 , p_enabled_flag IN VARCHAR2
933 , p_org_id IN NUMBER
934 , p_co IN VARCHAR2
935 , p_co_vs_id IN NUMBER
936 , p_cc IN VARCHAR2
937 , p_cc_vs_id IN NUMBER
938 , p_org_name IN VARCHAR2
939 ) RETURN NUMBER IS
940
941 -- Local variables
942 l_proc VARCHAR2(50) := g_package||'.create_co_cc_org_info_type';
943 l_retcode NUMBER := 0;
944 l_orig_info_id NUMBER := -1;
945 l_orig_ovn NUMBER := -1;
946 l_org_info_type VARCHAR2(2) := 'CC';
947 l_class_ovn NUMBER := p_class_ovn; -- to avoid expression assignment issue
948
949 BEGIN
950
951 hr_utility.set_location('Entering: '|| l_proc, 10);
952
953 -- If classification is disabled, temporarily enable it.
954 IF p_enabled_flag = 'N' THEN
955 hr_utility.set_location(l_proc, 20);
956
957 hr_organization_api.enable_org_classification
958 ( p_effective_date => SYSDATE
959 , p_org_information_id => p_class_id
960 , p_org_info_type_code => l_org_info_type
961 , p_object_version_number => l_class_ovn
962 );
963 END IF; -- classification was disabled
964
965 hr_utility.set_location(l_proc, 30);
966
967 -- Create the organization information type
968 hr_organization_api.create_org_information
969 ( p_effective_date => SYSDATE
970 , p_organization_id => p_org_id
971 , p_org_info_type_code => 'Company Cost Center'
972 , p_org_information2 => p_co_vs_id
973 , p_org_information3 => p_co
974 , p_org_information4 => p_cc_vs_id
975 , p_org_information5 => p_cc
976 , p_org_information_id => l_orig_info_id
977 , p_object_version_number => l_orig_ovn
978 );
979
980 hr_utility.set_message(800,'PER_50167_CREATED_CO_CC_INFO');
981 fnd_message.set_token('CO_CC_CLASS_INFO_ID',l_orig_info_id);
982 writelog(fnd_message.get(),'N');
983 hr_utility.set_location(l_proc, 40);
984
985 -- If classification was disabled, re-disable it.
986 IF p_enabled_flag = 'N' THEN
987 hr_utility.set_location(l_proc, 50);
988
989 hr_organization_api.disable_org_classification
990 ( p_effective_date => SYSDATE
991 , p_org_information_id => p_class_id
992 , p_org_info_type_code => l_org_info_type
993 , p_object_version_number => l_class_ovn
994 );
995 END IF; -- classification was disabled
996
997 hr_utility.set_location('Leaving: '|| l_proc, 60);
998
999 RETURN l_retcode;
1000
1001 EXCEPTION
1002
1003 WHEN OTHERS THEN
1004 writelog(SQLCODE||' - '||SQLERRM,'N');
1005 hr_utility.set_location('Leaving: '|| l_proc, 70);
1006 hr_utility.set_location(SQLERRM, 75);
1007 hr_utility.set_message(800,'PER_50168_ERR_CRE_CO_CC_INFO');
1008 fnd_message.set_token('ORG_NAME',p_org_name);
1009 writelog(fnd_message.get(),'N');
1010 l_retcode := 2;
1011 RETURN l_retcode;
1012
1013 END create_co_cc_org_info_type;
1014
1015 --
1016 -- Create HR Organization classification if required.
1017 --
1018 FUNCTION create_hr_org_class( p_org_id IN NUMBER
1019 , p_org_name IN VARCHAR2
1020 ) RETURN NUMBER IS
1021
1022 -- Local variables
1023 l_proc VARCHAR2(50) := g_package||'.create_hr_org_class';
1024 l_retcode NUMBER := 0;
1025 l_class_id NUMBER := -1;
1026 l_class_ovn NUMBER := -1;
1027
1028 BEGIN
1029
1030 hr_utility.set_location('Entering: '|| l_proc, 10);
1031
1032 -- Create HR Organization classification if allowed by profile.
1033 IF g_class_profile = 'CCHR' THEN
1034 hr_utility.set_location(l_proc, 20);
1035
1036 hr_organization_api.create_org_classification
1037 ( p_effective_date => SYSDATE
1038 , p_organization_id => p_org_id
1039 , p_org_classif_code => 'HR_ORG'
1040 , p_org_information_id => l_class_id
1041 , p_object_version_number => l_class_ovn
1042 );
1043
1044 hr_utility.set_message(800,'PER_50165_CREATED_HR_ORG');
1045 fnd_message.set_token('HR_ORG_CLASS_ID',l_class_id);
1046 writelog(fnd_message.get(),'N');
1047 END IF; -- class profile check
1048
1049 hr_utility.set_location('Leaving: '|| l_proc, 30);
1050
1051 RETURN l_retcode;
1052
1053 EXCEPTION
1054
1055 WHEN OTHERS THEN
1056 writelog(SQLCODE||' - '||SQLERRM,'N');
1057 hr_utility.set_location('Leaving: '|| l_proc, 40);
1058 hr_utility.set_location(SQLERRM, 45);
1059 hr_utility.set_message(800,'PER_50166_ERR_CRE_HR_ORG');
1060 fnd_message.set_token('ORG_NAME',p_org_name);
1061 writelog(fnd_message.get(),'N');
1062 l_retcode := 2;
1063 RETURN l_retcode;
1064
1065 END create_hr_org_class;
1066
1067 --
1068 -- Create Company Cost Center classification if required.
1069 --
1070 FUNCTION create_co_cc_class( p_org_id IN NUMBER
1071 , p_co_vs_id IN NUMBER
1072 , p_co IN VARCHAR2
1073 , p_cc_vs_id IN NUMBER
1074 , p_cc IN VARCHAR2
1075 , p_org_name IN VARCHAR2
1076 ) RETURN NUMBER IS
1077
1078 -- Local variables
1079 l_proc VARCHAR2(50) := g_package||'.create_co_cc_class';
1080 l_retcode NUMBER := 0;
1081 l_orig_info_id NUMBER := -1;
1082 l_class_info_id NUMBER := -1;
1083 l_orig_ovn NUMBER := -1;
1084 l_class_ovn NUMBER := -1;
1085
1086 BEGIN
1087
1088 hr_utility.set_location('Entering: '|| l_proc, 10);
1089
1090 -- Create Company Cost Center classification if allowed by profile.
1091 IF g_class_profile LIKE 'CC%' THEN
1092 hr_utility.set_location(l_proc, 20);
1093
1094 hr_organization_api.create_company_cost_center
1095 ( p_effective_date => SYSDATE
1096 , p_organization_id => p_org_id
1097 , p_company_valueset_id => p_co_vs_id
1098 , p_company => p_co
1099 , p_costcenter_valueset_id => p_cc_vs_id
1100 , p_costcenter => p_cc
1101 , p_ori_org_information_id => l_orig_info_id
1102 , p_ori_object_version_number => l_orig_ovn
1103 , p_org_information_id => l_class_info_id
1104 , p_object_version_number => l_class_ovn
1105 );
1106
1107 hr_utility.set_message(800,'PER_50159_COMP_CODE');
1108 fnd_message.set_token('COMP_CODE',p_co);
1109 writelog(fnd_message.get(),'N');
1110 hr_utility.set_message(800,'PER_50160_COST_CENTER_CODE');
1111 fnd_message.set_token('COST_CENTER_CODE',p_cc);
1112 writelog(fnd_message.get(),'N');
1113 hr_utility.set_message(800,'PER_50163_CREATED_CO_CC');
1114 fnd_message.set_token('CO_CC_CLASS_ID',l_orig_info_id||' ('||l_class_info_id||')');
1115 writelog(fnd_message.get(),'N');
1116 END IF; -- class profile check
1117
1118 hr_utility.set_location('Leaving: '|| l_proc, 30);
1119
1120 RETURN l_retcode;
1121
1122 EXCEPTION
1123
1124 WHEN OTHERS THEN
1125 writelog(SQLCODE||' - '||SQLERRM,'N');
1126 hr_utility.set_location('Leaving: '|| l_proc, 40);
1127 hr_utility.set_location(SQLERRM, 45);
1128 hr_utility.set_message(800,'PER_50164_ERR_CRE_CO_CC');
1129 fnd_message.set_token('ORG_NAME',p_org_name);
1130 writelog(fnd_message.get(),'N');
1131 l_retcode := 2;
1132 RETURN l_retcode;
1133
1134 END create_co_cc_class;
1135
1136 --
1137 -- Create an organization
1138 --
1139 FUNCTION create_org( p_bg_id IN VARCHAR2
1140 , p_org_start_date IN DATE
1141 , p_org_end_date IN DATE
1142 , p_org_name IN VARCHAR2
1143 , p_org_id IN OUT NOCOPY NUMBER
1144 ) RETURN NUMBER IS
1145
1146 -- Local variables
1147 l_proc VARCHAR2(50) := g_package||'.create_org';
1148 l_retcode NUMBER := 0;
1149 l_org_ovn NUMBER := -1;
1150
1151 BEGIN
1152
1153 hr_utility.set_location('Entering: '|| l_proc, 10);
1154
1155 hr_organization_api.create_organization
1156 ( p_effective_date => SYSDATE
1157 , p_business_group_id => p_bg_id
1158 , p_date_from => p_org_start_date
1159 , p_date_to => p_org_end_date
1160 , p_name => p_org_name
1161 , p_internal_external_flag => 'INT'
1162 , p_organization_id => p_org_id
1163 , p_object_version_number => l_org_ovn
1164 );
1165
1166 hr_utility.set_message(800,'PER_50161_CREATED_ORG');
1167 fnd_message.set_token('ORG_ID',p_org_id);
1168 writelog(fnd_message.get(),'N');
1169
1170 hr_utility.set_location('Leaving: '|| l_proc, 20);
1171
1172 RETURN l_retcode;
1173
1174 EXCEPTION
1175
1176 WHEN OTHERS THEN
1177 writelog(SQLCODE||' - '||SQLERRM,'N');
1178 hr_utility.set_location('Leaving: '|| l_proc, 30);
1179 hr_utility.set_location(SQLERRM, 35);
1180 hr_utility.set_message(800,'PER_50162_ERR_CRE_ORG');
1181 fnd_message.set_token('ORG_NAME',p_org_name);
1182 writelog(fnd_message.get(),'N');
1183 l_retcode := 2;
1184 RETURN l_retcode;
1185
1186 END create_org;
1187
1188 --
1189 -- Check if ORG Name already exists
1190 --
1191 FUNCTION org_name_exists( p_bg_id IN VARCHAR2
1192 , p_org_name IN VARCHAR2
1193 , p_org_id IN OUT NOCOPY NUMBER
1194 ) RETURN BOOLEAN IS
1195
1196 -- Local variables
1197 l_proc VARCHAR2(50) := g_package||'.org_name_exists';
1198 l_exists BOOLEAN;
1199
1200 -- Cursor to test if ORG with given name already exists.
1201 CURSOR c_chk_org_name IS
1202 SELECT organization_id
1203 FROM hr_all_organization_units
1204 WHERE name = p_org_name
1205 AND business_group_id = p_bg_id;
1206
1207 BEGIN
1208
1209 hr_utility.set_location('Entering: '|| l_proc, 10);
1210
1211 OPEN c_chk_org_name;
1212 FETCH c_chk_org_name INTO p_org_id;
1213 IF c_chk_org_name%NOTFOUND THEN
1214 hr_utility.set_location(l_proc, 20);
1215 l_exists := FALSE;
1216 ELSE
1217 hr_utility.set_location(l_proc, 30);
1218 l_exists := TRUE;
1219 END IF;
1220 CLOSE c_chk_org_name;
1221
1222 hr_utility.set_location('Leaving: '|| l_proc, 40);
1223
1224 RETURN l_exists;
1225
1226 EXCEPTION
1227
1228 WHEN OTHERS THEN
1229 hr_utility.set_location('Leaving: '|| l_proc, 50);
1230 IF c_chk_org_name%ISOPEN THEN
1231 CLOSE c_chk_org_name;
1232 END IF;
1233 l_exists := FALSE;
1234 RETURN l_exists;
1235
1236 END org_name_exists;
1237
1238 --
1239 -- Amend ORG Name for uniqueness
1240 --
1241 -- Make the ORG name unique by appending (n) to the ORG Name where
1242 -- n is the number of attempts to make the name unique. Quit after
1243 -- n exceeds max allowed tries.
1244 --
1245 FUNCTION make_org_name_unique( p_bg_id IN NUMBER
1246 , p_org_name IN VARCHAR2
1247 , p_new_org_name IN OUT NOCOPY VARCHAR2
1248 ) RETURN NUMBER IS
1249
1250 -- Local variables
1251 l_proc VARCHAR2(50) := g_package||'.make_org_name_unique';
1252 l_retcode NUMBER := 0;
1253 l_unique BOOLEAN := FALSE;
1254 l_retry_count NUMBER := 1;
1255 l_org_name_suffix VARCHAR2(5) := NULL;
1256 l_org_id NUMBER;
1257
1258 BEGIN
1259
1260 hr_utility.set_location('Entering: '|| l_proc, 10);
1261 p_new_org_name := p_org_name;
1262
1263 -- Loop for the configured maximum number of retries to find an ORG Name
1264 -- which is not already used. Make the ORG Name unique in a manner similar
1265 -- to FNDLOAD utility which adds suffix "(n)" to the name.
1266 WHILE (l_retry_count <= g_max_retries AND NOT l_unique) LOOP
1267 -- Coin a new name
1268 l_org_name_suffix := '('||TO_CHAR(l_retry_count)||')';
1269 p_new_org_name := SUBSTRB(p_new_org_name
1270 ,1
1271 ,g_org_name_max_length - LENGTH(l_org_name_suffix)
1272 )||l_org_name_suffix;
1273
1274 -- Check if new name already exists
1275 IF org_name_exists(p_bg_id, p_new_org_name, l_org_id) THEN
1276 l_retry_count := l_retry_count + 1;
1277 ELSE
1278 l_unique := TRUE;
1279 END IF;
1280 END LOOP;
1281
1282 IF NOT l_unique THEN
1283 -- Failed to get a unique ORG Name
1284 hr_utility.set_message(800,'PER_50171_NO_UNIQUE_NAME');
1285 fnd_message.set_token('ORG_NAME',p_org_name);
1286 writelog(fnd_message.get(),'N');
1287 p_new_org_name := NULL;
1288 l_retcode := 1;
1289 END IF;
1290
1291 hr_utility.set_location('Leaving: '|| l_proc, 30);
1292
1293 RETURN l_retcode;
1294
1295 EXCEPTION
1296
1297 WHEN OTHERS THEN
1298 hr_utility.set_location('Leaving: '|| l_proc, 40);
1299 hr_utility.set_location(SQLERRM, 45);
1300 writelog(SQLERRM,'N');
1301 p_new_org_name := NULL;
1302 l_retcode := 2;
1303 RETURN l_retcode;
1304
1305 END make_org_name_unique;
1306
1307 --
1308 -- Derive the ORG Name
1309 --
1310 -- Get the company description for the company code.
1311 -- Get the cost center description for the cost center code.
1312 -- Calculate the ORG start date.
1313 -- Phase 2a - calc the ORG end date or null (thus OUT param).
1314 -- Paste in the values to the ORG Name format.
1315 --
1316 FUNCTION derive_org_name( p_mode IN VARCHAR2
1317 , p_co IN VARCHAR2
1318 , p_co_vs_id IN NUMBER
1319 , p_cc IN VARCHAR2
1320 , p_cc_vs_id IN NUMBER
1321 , p_co_desc IN OUT NOCOPY VARCHAR2
1322 , p_cc_desc IN OUT NOCOPY VARCHAR2
1323 , p_org_name IN OUT NOCOPY VARCHAR2
1324 , p_org_start_date IN OUT NOCOPY DATE
1325 , p_org_end_date IN OUT NOCOPY DATE
1326 ) RETURN NUMBER IS
1327
1328 -- Local variables
1329 l_proc VARCHAR2(50) := g_package||'.derive_org_name';
1330 l_retcode NUMBER := 0;
1331 l_co_start_date fnd_flex_values_vl.start_date_active%TYPE;
1332 l_cc_start_date fnd_flex_values_vl.start_date_active%TYPE;
1333 l_co_end_date fnd_flex_values_vl.end_date_active%TYPE;
1334 l_cc_end_date fnd_flex_values_vl.end_date_active%TYPE;
1335
1336 BEGIN
1337
1338 hr_utility.set_location('Entering: '|| l_proc, 10);
1339
1340 -- Get company details from the company code
1341 IF l_retcode = 0 THEN
1342 hr_utility.set_location(l_proc, 20);
1343 l_retcode := get_vs_value_details( p_co_vs_id
1344 , p_co
1345 , p_co_desc
1346 , l_co_start_date
1347 , l_co_end_date
1348 );
1349 IF p_co_desc IS NULL THEN
1350 p_co_desc := p_co;
1351 END IF;
1352 END IF; -- retcode check
1353
1354 -- Get cost center details from the cost center code
1355 IF l_retcode = 0 THEN
1356 hr_utility.set_location(l_proc, 30);
1357 l_retcode := get_vs_value_details( p_cc_vs_id
1358 , p_cc
1359 , p_cc_desc
1360 , l_cc_start_date
1361 , l_cc_end_date
1362 );
1363 IF p_cc_desc IS NULL THEN
1364 p_cc_desc := p_cc;
1365 END IF;
1366 END IF; -- retcode check
1367
1368 IF l_retcode = 0 THEN
1369 hr_utility.set_location(l_proc, 40);
1370
1371 -- Set the ORG start date to be the later of company and cost center
1372 -- start dates. If these are null, set to default date '01/01/1990'.
1373 p_org_start_date := GREATEST( NVL(l_co_start_date, TO_DATE('01/01/1990','DD/MM/RRRR'))
1374 , NVL(l_cc_start_date, TO_DATE('01/01/1990','DD/MM/RRRR'))
1375 );
1376
1377 -- phase 2a: set the Org end date to earlier of co and cc VS end dates, or null
1378 IF l_co_end_date IS NOT NULL AND l_cc_end_date IS NOT NULL THEN
1379 IF l_co_end_date >= l_cc_end_date THEN
1380 p_org_end_date := l_cc_end_date; -- Bug 4402730 use minimum end_date.
1381 ELSE
1382 p_org_end_date := l_co_end_date;
1383 END IF;
1384 ELSIF l_co_end_date IS NULL AND l_cc_end_date IS NOT NULL THEN
1385 p_org_end_date := l_cc_end_date;
1386 ELSIF l_co_end_date IS NOT NULL AND l_cc_end_date IS NULL THEN
1387 p_org_end_date := l_co_end_date;
1388 END IF;
1389
1390
1391 hr_utility.set_location(l_proc, 50);
1392
1393 -- Compile the ORG name by pasting in the details
1394 SELECT SUBSTRB(REPLACE(REPLACE(REPLACE(REPLACE(g_org_name_format
1395 ,'$COC'
1396 ,p_co
1397 )
1398 ,'$CCC'
1399 ,p_cc
1400 )
1401 ,'$CON'
1402 ,p_co_desc
1403 )
1404 ,'$CCN'
1405 ,p_cc_desc
1406 )
1407 ,1
1408 ,g_org_name_max_length
1409 )
1410 INTO p_org_name
1411 FROM dual;
1412
1413 hr_utility.set_location(l_proc, 60);
1414
1415 -- Write the generated name to the LOG file
1416 IF p_mode IN ('CREATE_MAINTAIN','SINGLE_ORG') THEN
1417 hr_utility.set_message(800,'PER_50158_ORG_NAME');
1418 fnd_message.set_token('ORG_NAME',p_org_name);
1419 fnd_message.set_token('ORG_START_DATE',p_org_start_date);
1420 fnd_message.set_token('ORG_END_DATE',p_org_end_date);
1421 writelog(fnd_message.get(),'N');
1422 END IF;
1423 END IF; -- retcode check
1424
1425 hr_utility.set_location('Leaving: '|| l_proc, 70);
1426
1427 RETURN l_retcode;
1428
1429 EXCEPTION
1430
1431 WHEN OTHERS THEN
1432 hr_utility.set_location('Leaving: '|| l_proc, 80);
1433 hr_utility.set_location(SQLERRM, 85);
1434 writelog(SQLERRM,'N');
1435 l_retcode := 2;
1436 RETURN l_retcode;
1437
1438 END derive_org_name;
1439
1440 --
1441 -- Generate ORG and CO/CC Classifications as required
1442 --
1443 -- Derive an ORG Name in the correct format.
1444 -- If an ORG with this name does not exists.
1445 -- Create the ORG.
1446 -- Create the CoCC classification if allowed by profile.
1447 -- Create the HR Org classification if allowed by profile.
1448 -- Else an ORG with this name already exists.
1449 -- If COCC classification does not exist for the ORG
1450 -- Create the CoCC classification if allowed by profile.
1451 -- If HR ORG classification does not exist for the ORG.
1452 -- Create the HR Org classification if allowed by profile.
1453 -- Else COCC classification exists for the ORG
1454 -- If classified ORG does not have CoCC Info Type
1455 -- Create CoCC Info Type
1456 -- If HR ORG classification does not exist for the ORG.
1457 -- Create the HR Org classification if allowed by profile.
1458 -- Else classified ORG has CoCC Info Type
1459 -- If CoCC Info Type data exists
1460 -- The ORG we have does not appear to fully match.
1461 -- So make the ORG Name unique.
1462 -- Create an ORG.
1463 -- Create the CoCC classification if allowed by profile.
1464 -- Create the HR Org classification if allowed by profile.
1465 --
1466 FUNCTION gen_org_co_cc_class( p_mode IN VARCHAR2
1467 , p_bg_id IN NUMBER
1468 , p_coa_id IN NUMBER
1469 , p_co IN VARCHAR2
1470 , p_co_vs_id IN NUMBER
1471 , p_cc IN VARCHAR2
1472 , p_cc_vs_id IN NUMBER
1473 , p_org_id IN OUT NOCOPY NUMBER
1474 ) RETURN NUMBER IS
1475
1476 -- Local variables
1477 l_proc VARCHAR2(50) := g_package||'.gen_org_co_cc_class';
1478 l_retcode NUMBER := 0;
1479 l_org_name hr_all_organization_units.name%TYPE;
1480 l_new_org_name hr_all_organization_units.name%TYPE;
1481 l_org_start_date fnd_flex_values_vl.start_date_active%TYPE;
1482 l_org_end_date fnd_flex_values_vl.end_date_active%TYPE;
1483 l_class_enabled hr_organization_information.org_information2%TYPE;
1484 l_class_id hr_organization_information.org_information_id%TYPE;
1485 l_class_ovn hr_organization_information.object_version_number%TYPE;
1486 l_co VARCHAR2(240);
1487 l_cc VARCHAR2(240);
1488 l_co_vs_id NUMBER(15); -- Id of company value set
1489 l_cc_vs_id NUMBER(15); -- Id of cost center value set
1490 l_co_desc fnd_flex_values_vl.description%TYPE;
1491 l_cc_desc fnd_flex_values_vl.description%TYPE;
1492
1493 BEGIN
1494
1495 hr_utility.set_location('Entering: '|| l_proc, 10);
1496
1497 writelog('Processing P_BG_ID: '||p_bg_id||
1498 ', P_COA_ID: '||p_coa_id||
1499 ', P_CO: '||p_co||
1500 ', P_CO_VS_ID: '||p_co_vs_id||
1501 ', P_CC: '||p_cc||
1502 ', P_CC_VS_ID: '||p_cc_vs_id,'Y');
1503
1504 -- At this point, there either isnt an ORG and one will need to be
1505 -- created, or there is an ORG without the requisite classification
1506 -- and this needs to be added to the existing ORG.
1507
1508 -- Get the ORG Name in the correct format
1509 IF l_retcode = 0 THEN
1510 l_retcode := derive_org_name( p_mode
1511 , p_co
1512 , p_co_vs_id
1513 , p_cc
1514 , p_cc_vs_id
1515 , l_co_desc
1516 , l_cc_desc
1517 , l_org_name
1518 , l_org_start_date
1519 , l_org_end_date
1520 );
1521 END IF; -- l_retocde is 0
1522
1523 -- Check if ORG with coined named already exists in the business group
1524 IF l_retcode = 0 THEN
1525 hr_utility.set_location(l_proc, 20);
1526
1527 IF NOT org_name_exists(p_bg_id, l_org_name, p_org_id) THEN
1528 hr_utility.set_location(l_proc, 30);
1529
1530 -- ORG with derived name does not exist. Create new ORG.
1531 l_retcode := create_org( p_bg_id
1532 , l_org_start_date
1533 , l_org_end_date
1534 , l_org_name
1535 , p_org_id
1536 );
1537
1538 hr_utility.set_location(l_proc, 40);
1539
1540 -- Create Company Cost Center classification
1541 IF l_retcode = 0 THEN
1542 hr_utility.set_location(l_proc, 50);
1543
1544 l_retcode := create_co_cc_class( p_org_id
1545 , p_co_vs_id
1546 , p_co
1547 , p_cc_vs_id
1548 , p_cc
1549 , l_org_name
1550 );
1551 END IF;
1552
1553 hr_utility.set_location(l_proc, 60);
1554
1555 -- Create HR Org classification
1556 IF l_retcode = 0 THEN
1557 hr_utility.set_location(l_proc, 70);
1558
1559 l_retcode := create_hr_org_class( p_org_id
1560 , l_org_name
1561 );
1562 END IF;
1563
1564 hr_utility.set_location(l_proc, 80);
1565
1566 ELSE -- ORG with Name found
1567 hr_utility.set_location(l_proc, 90);
1568
1569 -- So the organization exists. Check if Company Cost Center
1570 -- classification exists.
1571 IF NOT co_cc_class_exists(p_org_id, l_class_enabled, l_class_id, l_class_ovn) THEN
1572 hr_utility.set_location(l_proc, 100);
1573
1574 -- Company Cost Center classification not found. Create it.
1575 l_retcode := create_co_cc_class( p_org_id
1576 , p_co_vs_id
1577 , p_co
1578 , p_cc_vs_id
1579 , p_cc
1580 , l_org_name
1581 );
1582
1583 -- Check if HR ORG classification exists.
1584 IF l_retcode = 0 THEN
1585 hr_utility.set_location(l_proc, 110);
1586
1587 IF NOT hr_org_class_exists(p_org_id) THEN
1588 hr_utility.set_location(l_proc, 120);
1589
1590 -- HR Org classification does not exist. Create it.
1591 l_retcode := create_hr_org_class( p_org_id
1592 , l_org_name
1593 );
1594 END IF; -- HROrg classification not found
1595
1596 END IF; -- retcode is 0
1597
1598 ELSE -- CoCC classification found
1599 hr_utility.set_location(l_proc, 130);
1600
1601 -- Check if classified ORG has CC Info Type.
1602 IF NOT co_cc_org_info_type_exists(p_org_id, l_co, l_co_vs_id, l_cc, l_cc_vs_id) THEN
1603 hr_utility.set_location(l_proc, 140);
1604
1605 -- CC Info Type not found. Create it.
1606 l_retcode := create_co_cc_org_info_type( l_class_id
1607 , l_class_ovn
1608 , l_class_enabled
1609 , p_org_id
1610 , p_co
1611 , p_co_vs_id
1612 , p_cc
1613 , p_cc_vs_id
1614 , l_org_name
1615 );
1616
1617 -- Check if HR ORG classification exists.
1618 IF l_retcode = 0 THEN
1619 hr_utility.set_location(l_proc, 150);
1620
1621 IF NOT hr_org_class_exists(p_org_id) THEN
1622 hr_utility.set_location(l_proc, 160);
1623
1624 -- HR Org classification does not exist. Create it.
1625 l_retcode := create_hr_org_class( p_org_id
1626 , l_org_name
1627 );
1628 END IF; -- HROrg classification not found
1629
1630 END IF; -- retcode is 0
1631
1632 ELSE -- CC Info Type exists
1633 hr_utility.set_location(l_proc, 170);
1634
1635 -- Check if info type data exists
1636 IF l_co IS NULL AND l_co_vs_id IS NULL AND
1637 l_cc IS NULL AND l_cc_vs_id IS NULL THEN
1638
1639 hr_utility.set_location(l_proc, 180);
1640 -- This means that info type data is missing. This should never
1641 -- happen as the company value set is mandatory.
1642
1643 ELSE -- Info Type data exists
1644
1645 hr_utility.set_location(l_proc, 190);
1646 -- At this point the info type exists with data but does not match
1647 -- information for our prospective organization. We will therefore
1648 -- need to create a new organization. However, the name we have
1649 -- already exists. So we must first make the name unique.
1650
1651 p_org_id := NULL;
1652 l_retcode := make_org_name_unique( p_bg_id
1653 , l_org_name
1654 , l_new_org_name
1655 );
1656
1657 -- Create the organization.
1658 IF l_retcode = 0 THEN
1659 hr_utility.set_location(l_proc, 200);
1660
1661 l_retcode := create_org( p_bg_id
1662 , l_org_start_date
1663 , l_org_end_date
1664 , l_new_org_name
1665 , p_org_id
1666 );
1667 END IF; -- retcode is 0
1668
1669 -- Create the Company Cost Center classification.
1670 IF l_retcode = 0 THEN
1671 hr_utility.set_location(l_proc, 210);
1672
1673 l_retcode := create_co_cc_class( p_org_id
1674 , p_co_vs_id
1675 , p_co
1676 , p_cc_vs_id
1677 , p_cc
1678 , l_new_org_name
1679 );
1680 END IF; -- retcode is 0
1681
1682 -- Create the HR Org classification.
1683 IF l_retcode = 0 THEN
1684 hr_utility.set_location(l_proc, 220);
1685
1686 l_retcode := create_hr_org_class( p_org_id
1687 , l_new_org_name
1688 );
1689 END IF; -- retcode is 0
1690
1691 END IF; -- Info Type data exists
1692
1693 END IF; -- CC Info Type does not exist
1694
1695 END IF; -- CoCC classification not found
1696
1697 END IF; -- ORG with Name not found
1698
1699 END IF; -- l_retocde is 0
1700
1701 hr_utility.set_location('Leaving: '|| l_proc, 230);
1702
1703 RETURN l_retcode;
1704
1705 END gen_org_co_cc_class;
1706
1707 --
1708 -- Get additional record details for reporting/exporting
1709 --
1710 FUNCTION get_details_to_report( p_mode IN VARCHAR2
1711 ) RETURN NUMBER IS
1712
1713 -- Local Variables
1714 l_proc VARCHAR2(50) := g_package||'.get_details_to_report';
1715 l_retcode NUMBER := 0;
1716 l_coa_id NUMBER;
1717 l_co VARCHAR2(240); -- Company value
1718 l_cc VARCHAR2(240); -- Cost Center value
1719 l_co_vs_id NUMBER(15); -- Id of company value set
1720 l_cc_vs_id NUMBER(15); -- Id of cost center value set
1721 l_co_desc fnd_flex_values_vl.description%TYPE;
1722 l_cc_desc fnd_flex_values_vl.description%TYPE;
1723 l_org_name hr_all_organization_units.name%TYPE;
1724 l_org_start_date fnd_flex_values_vl.start_date_active%TYPE;
1725 l_org_end_date fnd_flex_values_vl.end_date_active%TYPE;
1726 l_co_vs_name fnd_flex_value_sets.flex_value_set_name%TYPE;
1727 l_cc_vs_name fnd_flex_value_sets.flex_value_set_name%TYPE;
1728
1729 TYPE cur_type IS REF CURSOR;
1730 c_list cur_type;
1731
1732 BEGIN
1733
1734 hr_utility.set_location('Entering: '|| l_proc, 10);
1735
1736 OPEN c_list FOR 'SELECT coa_id, co, co_vs_id, cc, cc_vs_id FROM '||g_temp_table_name;
1737 LOOP
1738 FETCH c_list INTO l_coa_id, l_co, l_co_vs_id, l_cc, l_cc_vs_id;
1739 EXIT WHEN c_list%NOTFOUND;
1740
1741 l_retcode := derive_org_name( p_mode
1742 , l_co
1743 , l_co_vs_id
1744 , l_cc
1745 , l_cc_vs_id
1746 , l_co_desc
1747 , l_cc_desc
1748 , l_org_name
1749 , l_org_start_date
1750 , l_org_end_date -- avoids overload, but not used in export.
1751 );
1752
1753 -- Note: Phase 2a has added date_to (end date) population for the orgs created.
1754 -- Need to check if file format should also be changed to support export
1755 -- of this extra field's values. Not changed until notified of this requirement.
1756
1757
1758 -- Get company value set name
1759 IF l_retcode = 0 THEN
1760 l_retcode := get_vs_details( l_co_vs_id
1761 , l_co_vs_name
1762 );
1763 END IF; -- retcode check
1764
1765 -- Get cost center value set name
1766 IF l_retcode = 0 THEN
1767 l_retcode := get_vs_details( l_cc_vs_id
1768 , l_cc_vs_name
1769 );
1770 END IF; -- retcode check
1771
1772 IF l_retcode = 0 THEN
1773 EXECUTE IMMEDIATE 'UPDATE '||g_temp_table_name||
1774 ' SET co_desc = :1' ||
1775 ', cc_desc = :2' ||
1776 ', co_vs_name = :3' ||
1777 ', cc_vs_name = :4' ||
1778 ', org_name = :5' ||
1779 ', org_start_date = :6'||
1780 ' WHERE co = :7' ||
1781 ' AND co_vs_id = :8' ||
1782 ' AND cc = :9' ||
1783 ' AND cc_vs_id = :10'
1784 USING l_co_desc
1785 , l_cc_desc
1786 , l_co_vs_name
1787 , l_cc_vs_name
1788 , l_org_name
1789 , l_org_start_date
1790 , l_co
1791 , l_co_vs_id
1792 , l_cc
1793 , l_cc_vs_id;
1794 ELSE
1795 l_retcode := 0;
1796 END IF; -- retcode check
1797
1798 END LOOP; -- records in temp table
1799 CLOSE c_list;
1800
1801 -- Close transaction
1802 IF l_retcode = 0 THEN
1803 hr_utility.set_location(l_proc, 20);
1804 COMMIT;
1805 END IF;
1806
1807 hr_utility.set_location('Leaving: '|| l_proc, 30);
1808
1809 RETURN l_retcode;
1810
1811 EXCEPTION
1812
1813 WHEN OTHERS THEN
1814 hr_utility.set_location('Leaving: '|| l_proc, 40);
1815 hr_utility.set_location(SQLERRM, 45);
1816 writelog(SQLERRM,'N');
1817 IF c_list%ISOPEN THEN
1818 CLOSE c_list;
1819 END IF;
1820 l_retcode := 2;
1821 RETURN l_retcode;
1822
1823 END get_details_to_report;
1824 --
1825 -- Fix for bug 3837139 starts here.
1826 --
1827 -- ----------------------------------------------------------------------------
1828 -- |-----------------------< update_org_name_date >---------------------------|
1829 -- ----------------------------------------------------------------------------
1830 --
1831 function update_org_name_date(
1832 p_mode in varchar2
1833 ,p_sync_org_name in varchar2 default 'N'
1834 ,p_sync_org_dates in varchar2 default 'N'
1835 ) return number is
1836 --
1837 -- Local variables
1838 l_rowid rowid;
1839 l_sql_num number(3);
1840 l_updrowcount number := 0;
1841 l_retcode number := 0;
1842 l_co_vs_id number; -- Company value set id
1843 l_cc_vs_id number; -- Cost Center value set id
1844 l_org_id hr_all_organization_units.organization_id%type;
1845 l_org_name hr_all_organization_units.name%type;
1846 l_name hr_all_organization_units.name%type;
1847 l_start hr_all_organization_units.date_from%type;
1848 l_end hr_all_organization_units.date_to%type;
1849 l_cc_desc fnd_flex_values_vl.description%type;
1850 l_co_desc fnd_flex_values_vl.description%type;
1851 l_org_start_date fnd_flex_values_vl.start_date_active%type;
1852 l_org_end_date fnd_flex_values_vl.end_date_active%type;
1853 l_co varchar2(240); -- company value
1854 l_cc varchar2(240); -- cost center value
1855 l_proc varchar2(72) := g_package||'.update_org_name_date';
1856 --
1857 type cur_type is ref cursor;
1858 c_ref cur_type;
1859 --
1860 begin
1861 --
1862 hr_utility.set_location('Entering: '|| l_proc, 10);
1863 --
1864 if p_sync_org_name = 'Y' or p_sync_org_dates = 'Y' then
1865 -- choose the org update SQL based on params
1866 if p_sync_org_name = 'Y' and p_sync_org_dates = 'Y' then l_sql_num := 1;
1867 elsif p_sync_org_name = 'Y' and p_sync_org_dates = 'N' then l_sql_num := 2;
1868 elsif p_sync_org_name = 'N' and p_sync_org_dates = 'Y' then l_sql_num := 3;
1869 end if;
1870 --
1871 hr_utility.set_location(l_proc, 20);
1872 -- PHASE 2a Conditionally update existing orgs' names, start_dates
1873 -- and end_dates to ensure they match the profile option name configuration
1874 -- and the latest values from their value sets descriptions and dates.
1875 -- Update will only be done if new and existing values differ.
1876 open c_ref for ' select units.rowid, units.name, units.organization_id, units.date_from, units.date_to, '||
1877 ' t.co_vs_id, t.co, t.cc_vs_id, t.cc'||
1878 ' from '|| g_temp_table_name ||' t , '||
1879 ' hr_organization_information cc,'||
1880 ' hr_organization_information class,'||
1881 ' hr_all_organization_units units'||
1882 ' where to_char(t.co_vs_id) = cc.org_information2 '||
1883 ' and t.co = cc.org_information3 '||
1884 ' and to_char(t.cc_vs_id) = cc.org_information4 '||
1885 ' and t.cc = cc.org_information5 '||
1886 ' and units.organization_id = class.organization_id'||
1887 ' and class.org_information_context = ''CLASS'''||
1888 ' and class.org_information1 = ''CC'''||
1889 ' and class.organization_id = cc.organization_id'||
1890 ' and cc.org_information_context = ''Company Cost Center'''||
1891 ' for update of units.name nowait ';
1892 --
1893 loop
1894 --
1895 hr_utility.set_location(l_proc, 30);
1896 --
1897 fetch c_ref into l_rowid, l_name, l_org_id, l_start, l_end,
1898 l_co_vs_id, l_co, l_cc_vs_id, l_cc;
1899 exit when c_ref%notfound;
1900 --
1901 l_org_name := null;
1902 l_retcode := derive_org_name(p_mode
1903 ,l_co
1904 ,l_co_vs_id
1905 ,l_cc
1906 ,l_cc_vs_id
1907 ,l_co_desc
1908 ,l_cc_desc
1909 ,l_org_name
1910 ,l_org_start_date
1911 ,l_org_end_date);
1912 --
1913 hr_utility.set_location(l_proc, 40);
1914 --
1915 if l_retcode = 0 then
1916 --
1917 if l_sql_num = 1 then
1918 --
1919 if l_org_name <> l_name or l_org_start_date <> l_start or
1920 nvl(l_org_end_date, hr_api.g_sot) <> nvl(l_end, hr_api.g_sot) then
1921 --
1922 hr_utility.set_location(l_proc, 50);
1923 --
1924 update hr_all_organization_units units
1925 set units.name = l_org_name,
1926 units.date_from = l_org_start_date,
1927 units.date_to = l_org_end_date
1928 where units.rowid = l_rowid;
1929 --
1930 update hr_all_organization_units_tl
1931 set name= l_org_name
1932 where organization_id = l_org_id
1933 and language in (language, source_lang);
1934 --
1935 l_updrowcount := l_updrowcount + 1;
1936 --
1937 end if;
1938 --
1939 elsif l_sql_num = 2 then
1940 --
1941 if l_org_name <> l_name then
1942 --
1943 hr_utility.set_location(l_proc, 60);
1944 --
1945 update hr_all_organization_units units
1946 set units.name = l_org_name
1947 where units.rowid = l_rowid;
1948 --
1949 update hr_all_organization_units_tl
1950 set name=l_org_name
1951 where organization_id = l_org_id
1952 and language in (language, source_lang);
1953 --
1954 l_updrowcount := l_updrowcount + 1;
1955 --
1956 end if;
1957 --
1958 elsif l_sql_num = 3 then
1959 --
1960 if l_org_start_date <> l_start or
1961 nvl(l_org_end_date, hr_api.g_sot) <> nvl(l_end, hr_api.g_sot) then
1962 --
1963 hr_utility.set_location(l_proc, 70);
1964 --
1965 update hr_all_organization_units units
1966 set units.date_from = l_org_start_date,
1967 units.date_to = l_org_end_date
1968 where units.rowid = l_rowid;
1969 --
1970 l_updrowcount := l_updrowcount + 1;
1971 --
1972 end if;
1973 --
1974 end if;
1975 --
1976 end if;
1977 --
1978 end loop;
1979 --
1980 close c_ref;
1981 -- Commit the Org updates as this is atomic unit or work
1982 if l_updrowcount > 0 then
1983 --
1984 commit;
1985 writelog('Updated '||l_updrowcount||' organization details.','N');
1986 --
1987 end if;
1988 --
1989 hr_utility.set_location(l_proc, 80);
1990 --
1991 end if;
1992 --
1993 hr_utility.set_location('Leaving: '|| l_proc, 99);
1994 --
1995 return l_retcode;
1996 --
1997 exception
1998 --
1999 when others then
2000 --
2001 hr_utility.set_location('Leaving: '|| l_proc, 99);
2002 writelog(sqlerrm, 'N');
2003 rollback; -- explicitly release any lock taken by NOWAIT
2004 if c_ref%isopen then close c_ref; end if;
2005 l_retcode := 2;
2006 --
2007 end update_org_name_date;
2008 --
2009 -- ----------------------------------------------------------------------------
2010 -- |---------------------< get_co_cc_comb_into_temp >-------------------------|
2011 -- ----------------------------------------------------------------------------
2012 --
2013 function get_co_cc_comb_into_temp(
2014 p_business_group_id in number
2015 ,p_coa_id in number
2016 ,p_co in varchar2
2017 ,p_source in varchar2
2018 ,p_rowcount out nocopy number -- added for bug4346785.
2019 ) return number is
2020 --
2021 -- Local variables
2022 l_retcode number := 0;
2023 l_rowcount number := 0;
2024 l_delrowcount number := 0;
2025 l_co_vs_id number; -- Company value set id
2026 l_cc_vs_id number; -- Cost Center value set id
2027 l_co_seg varchar2(240); -- Column name holding company segment value
2028 l_cc_seg varchar2(240); -- Column name holding cost center segment value
2029 l_proc varchar2(72) := g_package||'.get_co_cc_comb_into_temp';
2030 --
2031 -- Cursor to fetch the company codes within a business group
2032 -- and within the value set
2033 cursor c_companies(cp_co in varchar2
2034 ,cp_business_group_id in number
2035 ,cp_co_vs_id in number) is
2036 select distinct cc.org_information3 company_code
2037 from hr_all_organization_units units,
2038 hr_organization_information class,
2039 hr_organization_information cc
2040 where units.organization_id = class.organization_id
2041 and class.org_information_context = 'CLASS'
2042 and class.org_information1 = 'CC'
2043 and class.organization_id = cc.organization_id
2044 and cc.org_information_context = 'Company Cost Center'
2045 and cc.org_information2 is not null
2046 and cc.org_information3 is not null
2047 and cc.org_information3 = nvl(cp_co, cc.org_information3)
2048 and cc.org_information4 is null
2049 and cc.org_information5 is null
2050 and units.business_group_id = cp_business_group_id
2051 and cc.org_information3 in (
2052 select flex_value
2053 from fnd_flex_values_vl
2054 where flex_value_set_id = cp_co_vs_id);
2055 --
2056 -- Cursor to fetch Cost Center Codes from the specified value set
2057 cursor c_cost_centers(cp_cc_vs_id in number) is
2058 select ffv.flex_value cost_center_code
2059 from fnd_flex_values_vl ffv
2060 where ffv.flex_value_set_id = cp_cc_vs_id
2061 order by ffv.flex_value;
2062 --
2063 -- Local exceptions
2064 e_no_records_to_process exception;
2065 --
2066 begin
2067 --
2068 hr_utility.set_location('Entering: '|| l_proc, 10);
2069 --
2070 -- Derive the Company value set and Cost Center value sets
2071 l_retcode := derive_value_sets(p_coa_id
2072 ,l_co_seg
2073 ,l_cc_seg
2074 ,l_co_vs_id
2075 ,l_cc_vs_id);
2076 writelog('Company Value Set Id: '||l_co_vs_id, 'Y');
2077 writelog('Cost Center Value Set Id: '||l_cc_vs_id, 'Y');
2078 --
2079 -- Get records to process into temp table
2080 if l_retcode = 0 then
2081 --
2082 hr_utility.set_location(l_proc, 20);
2083 --
2084 if p_source = 'CCVS' then
2085 -- Use company organizations within the business group and the
2086 -- cost center value set to build the company cost center
2087 -- combinations to process.
2088 hr_utility.set_location(l_proc, 30);
2089 -- Loop for company codes within the business group
2090 for cr_company in c_companies(p_co, p_business_group_id, l_co_vs_id) loop
2091 --
2092 hr_utility.set_message(800, 'PER_50159_COMP_CODE');
2093 fnd_message.set_token('COMP_CODE', cr_company.company_code);
2094 writelog(fnd_message.get(), 'Y');
2095 --
2096 for cr_cost_center in c_cost_centers(l_cc_vs_id) loop
2097 --
2098 hr_utility.set_message(800,'PER_50160_COST_CENTER_CODE');
2099 fnd_message.set_token('COST_CENTER_CODE',cr_cost_center.cost_center_code);
2100 writelog(fnd_message.get(),'Y');
2101 -- Insert record into temp table
2102 execute immediate 'insert into '||g_temp_table_name||
2103 ' ( coa_id'||
2104 ' , co'||
2105 ' , co_vs_id'||
2106 ' , cc'||
2107 ' , cc_vs_id'||
2108 ' ) values'||
2109 ' ( :1, :2, :3, :4, :5 )'
2110 using p_coa_id
2111 ,cr_company.company_code
2112 ,l_co_vs_id
2113 ,cr_cost_center.cost_center_code
2114 ,l_cc_vs_id;
2115 l_rowcount := l_rowcount + 1;
2116 --
2117 end loop; -- Cost Centers
2118 --
2119 end loop; -- Companies
2120 --
2121 if l_rowcount = 0 then
2122 raise e_no_records_to_process;
2123 else
2124 writelog('Inserted '||l_rowcount||' Company-CostCenter records into temp','Y');
2125 end if;
2126 --
2127 else -- p_source is GLCC
2128 -- Get the company cost center combinations to process from table
2129 -- GL_CODE_COMBIANTIONS.
2130 hr_utility.set_location(l_proc, 40);
2131 --
2132 execute immediate 'insert into '||g_temp_table_name||
2133 ' ( coa_id'||
2134 ' , co'||
2135 ' , co_vs_id'||
2136 ' , cc'||
2137 ' , cc_vs_id )'||
2138 ' select distinct'||
2139 ' '||p_coa_id||
2140 ', '||l_co_seg||
2141 ', '||l_co_vs_id||
2142 ', '||l_cc_seg||
2143 ', '||l_cc_vs_id||
2144 ' from gl_code_combinations'||
2145 ' where summary_flag = ''N'''||
2146 ' and chart_of_accounts_id = :1'||
2147 ' and '||l_co_seg||' = nvl(:2,'||l_co_seg||')'
2148 using p_coa_id, p_co;
2149 --
2150 l_rowcount := sql%rowcount;
2151 if l_rowcount = 0 then
2152 raise e_no_records_to_process;
2153 else
2154 writelog('Inserted '||l_rowcount||' Company-CostCenter records from GLCC into temp','Y');
2155 end if;
2156 -- Delete combinations where company code does not exist as a company organization.
2157 hr_utility.set_location(l_proc, 50);
2158 --
2159 execute immediate 'delete from '||g_temp_table_name||
2160 ' where (co,to_char(co_vs_id)) not in ('||
2161 ' select distinct cc.org_information3, cc.org_information2'||
2162 ' from hr_all_organization_units units,'||
2163 ' hr_organization_information class,'||
2164 ' hr_organization_information cc'||
2165 ' where units.organization_id = class.organization_id'||
2166 ' and class.org_information_context = ''CLASS'''||
2167 ' and class.org_information1 = ''CC'''||
2168 ' and class.organization_id = cc.organization_id'||
2169 ' and cc.org_information_context = ''Company Cost Center'''||
2170 ' and units.business_group_id = :1 '||
2171 ' and cc.org_information4 is null'||
2172 ' and cc.org_information5 is null )'
2173 using p_business_group_id;
2174
2175 --
2176 l_delrowcount := sql%rowcount;
2177 writelog('Deleted '||l_delrowcount||' missing Company-ORG records from temp','Y');
2178 if l_rowcount = l_delrowcount then
2179 raise e_no_records_to_process;
2180 end if;
2181 --
2182 end if; -- p_source is CCVS
2183 --
2184 end if; -- rectcode is 0
2185 --
2186 p_rowcount := l_rowcount; -- Added for bug 4346785.
2187 --
2188 -- Create a performance index
2189 if l_retcode = 0 then
2190 --
2191 hr_utility.set_location(l_proc, 60);
2192 --
2193 execute immediate 'create index '||g_temp_table_name||'_n1 on '||
2194 g_temp_table_name||'(co,co_vs_id,cc,cc_vs_id)';
2195
2196 -- Note that the above DDL will implicitly commit the previously executed DML
2197 end if; -- rectcode is 0
2198 --
2199 hr_utility.set_location('Leaving: '|| l_proc, 99);
2200 --
2201 return l_retcode;
2202 --
2203 exception
2204 --
2205 when e_no_records_to_process then
2206 --
2207 hr_utility.set_location('Leaving: '|| l_proc, 999);
2208 hr_utility.set_message(800, 'PER_50154_NO_CO_CC_COMBS');
2209 writelog(fnd_message.get(), 'N');
2210 l_retcode := 1;
2211 return l_retcode;
2212 --
2213 when others then
2214 --
2215 hr_utility.set_location('Leaving: '|| l_proc, 999);
2216 writelog(sqlerrm,'N');
2217 l_retcode := 2;
2218 return l_retcode;
2219 --
2220 end get_co_cc_comb_into_temp;
2221 --
2222 -- ----------------------------------------------------------------------------
2223 -- |--------------------< delete_sync_orgs_from_temp >------------------------|
2224 -- ----------------------------------------------------------------------------
2225 --
2226 function delete_sync_orgs_from_temp(
2227 p_mode in varchar2
2228 ,p_business_group_id in number
2229 ,p_coa_id in number
2230 ,p_rowcount in number -- Added for bug4346785.
2231 ) return number is
2232 --
2233 -- Local variables
2234 --
2235 l_retcode number := 0;
2236 l_co_seg varchar2(240); -- Column name holding company segment value
2237 l_cc_seg varchar2(240); -- Column name holding cost center segment value
2238 l_co_vs_id number; -- Company value set id
2239 l_cc_vs_id number; -- Cost Center value set id
2240 l_rowcount number;
2241 l_delrowcount number := 0;
2242 l_del1rowcount number := 0;
2243 l_proc varchar2(50) := g_package||'.delete_sync_orgs_from_temp';
2244 --
2245 -- Local exceptions
2246 --
2247 e_no_records_to_process EXCEPTION;
2248 --
2249 BEGIN
2250 hr_utility.set_location('Entering:'||l_proc, 10);
2251 --
2252 l_rowcount := p_rowcount; -- Added for bug4346785.
2253 --
2254 -- Derive the Company value set and Cost Center value sets
2255 --
2256 IF l_retcode = 0 THEN
2257 --
2258 hr_utility.set_location(l_proc, 30);
2259 l_retcode := derive_value_sets( p_coa_id
2260 , l_co_seg
2261 , l_cc_seg
2262 , l_co_vs_id
2263 , l_cc_vs_id
2264 );
2265 writelog('Company Value Set Id: '||l_co_vs_id,'Y');
2266 writelog('Cost Center Value Set Id: '||l_cc_vs_id,'Y');
2267 --
2268 END IF; -- retcode is 0
2269 --
2270 -- Now updates of corresponding orgs have been done,
2271 -- perform relevant delete ops to discard existing / synchroniized rows
2272 -- from g_temp_table_name
2273 --
2274 hr_utility.set_location(l_proc, 40);
2275 --
2276 IF l_retcode = 0 AND p_mode = 'CREATE_MAINTAIN' THEN
2277 --
2278 --
2279 hr_utility.set_location(l_proc, 60);
2280 --
2281 -- Note that l_co_seg and l_cc_seg hold the names of the columns
2282 -- which contain the company code value and cost center code value.
2283 --
2284 EXECUTE IMMEDIATE 'DELETE FROM '||g_temp_table_name||
2285 ' WHERE (co,cc) IN ('||
2286 ' SELECT DISTINCT '||l_co_seg||','||l_cc_seg||' FROM gl_code_combinations'||
2287 ' WHERE chart_of_accounts_id = :1 AND company_cost_center_org_id IS NOT NULL)'
2288 USING p_coa_id;
2289 --
2290 l_delrowcount := SQL%ROWCOUNT;
2291 writelog('Deleted '||l_delrowcount||' already synchronized records from temp table','Y');
2292 IF l_rowcount = l_delrowcount THEN
2293 hr_utility.set_location(l_proc, 70);
2294 RAISE e_no_records_to_process;
2295 END IF;
2296 --
2297 hr_utility.set_location(l_proc, 80);
2298 --
2299 -- Delete records from temp table where Company-CostCenter Orgs already exist
2300 -- in the system. These may not be already synchronized. This needs to be done
2301 -- only in CREATE_MAINTAIN mode but not in EXPORT mode. This routine does not
2302 -- get called for the SINGLE_ORG and SYNCHRONIZE modes.
2303 --
2304 EXECUTE IMMEDIATE 'DELETE FROM '||g_temp_table_name||
2305 ' WHERE (TO_CHAR(co_vs_id), co'||
2306 ', TO_CHAR(cc_vs_id), cc) IN ('||
2307 ' SELECT CC.org_information2, CC.org_information3,'||
2308 ' CC.org_information4, CC.org_information5'||
2309 ' FROM hr_all_organization_units UNITS,'||
2310 ' hr_organization_information CLASS,'||
2311 ' hr_organization_information CC'||
2312 ' WHERE UNITS.organization_id = CLASS.organization_id'||
2313 ' AND CLASS.org_information_context = ''CLASS'''||
2314 ' AND CLASS.org_information1 = ''CC'''||
2315 ' AND CLASS.organization_id = CC.organization_id'||
2316 ' AND CC.org_information_context = ''Company Cost Center'''||
2317 ' AND UNITS.business_group_id = :1 )'
2318 USING p_business_group_id;
2319 --
2320 l_del1rowcount := SQL%ROWCOUNT;
2321 writelog('Deleted '||l_del1rowcount||' ORG already existing records from temp table','Y');
2322 IF l_rowcount = (l_delrowcount + l_del1rowcount) THEN
2323 hr_utility.set_location(l_proc, 90);
2324 RAISE e_no_records_to_process;
2325 END IF;
2326 --
2327 END IF; -- retcode is 0 and mode is CREATE_MAINTAIN
2328 --
2329 -- Close transaction as this is an integral unit of work
2330 --
2331 hr_utility.set_location(l_proc, 100);
2332 IF l_retcode = 0 THEN
2333 hr_utility.set_location(l_proc, 110);
2334 COMMIT;
2335 END IF; -- retcode is 0
2336 --
2337 hr_utility.set_location('Leaving: '||l_proc, 120);
2338 --
2339 RETURN l_retcode;
2340 --
2341 EXCEPTION
2342 WHEN e_no_records_to_process THEN
2343 hr_utility.set_location('Leaving: '|| l_proc, 130);
2344 hr_utility.set_message(800,'PER_50154_NO_CO_CC_COMBS');
2345 writelog(fnd_message.get(),'N');
2346 l_retcode := 1;
2347 RETURN l_retcode;
2348 WHEN OTHERS THEN
2349 hr_utility.set_location('Leaving: '|| l_proc, 140);
2350 hr_utility.set_location(SQLERRM, 135);
2351 writelog(SQLERRM,'N');
2352 l_retcode := 2;
2353 RETURN l_retcode;
2354 END delete_sync_orgs_from_temp;
2355 --
2356 -- Fix for bug 3837139 ends here.
2357 --
2358 --
2359 -- Populate Co-CC combinations to process into temporary table
2360 --
2361 -- Derive company and cost center value sets for the chart of accounts.
2362 -- If source is CCVS
2363 -- Loop for company codes within the business group.
2364 -- Loop for cost center codes within the cost center value set.
2365 -- Insert Company Cost Center combinations into temp table.
2366 -- Else source is GLCC
2367 -- Fetch distinct Company Cost Center combinations from table
2368 -- GL_CODE_COMBINATIONS into temp table.
2369 -- Create a performance index on the temp table.
2370 -- Delete from temp table where CoCC combinations have been synchronized
2371 -- in GL_CODE_COMBINATIONS.
2372 -- Update existing CoCC orgs depending upon control params.
2373 -- Delete from temp table where CoCC Orgs already exist in the system.
2374 -- Close the transaction as this completes a unit of work.
2375 --
2376 FUNCTION get_co_cc_combinations( p_mode IN VARCHAR2
2377 , p_business_group_id IN NUMBER
2378 , p_coa_id IN NUMBER
2379 , p_co IN VARCHAR2
2380 , p_source IN VARCHAR2
2381 , p_sync_org_name IN VARCHAR2 DEFAULT 'N'
2382 , p_sync_org_dates IN VARCHAR2 DEFAULT 'N'
2383 ) RETURN NUMBER IS
2384
2385 -- Local variables
2386 l_proc VARCHAR2(50) := g_package||'.get_co_cc_combinations';
2387 l_retcode NUMBER := 0;
2388 l_rowcount NUMBER := 0;
2389 l_delrowcount NUMBER := 0;
2390 l_updrowcount NUMBER := 0;
2391 l_del1rowcount NUMBER := 0;
2392 l_co_seg VARCHAR2(240); -- Column name holding company segment value
2393 l_cc_seg VARCHAR2(240); -- Column name holding cost center segment value
2394 l_co_vs_id NUMBER; -- Company value set id
2395 l_cc_vs_id NUMBER; -- Cost Center value set id
2396
2397 l_rowid ROWID;
2398 l_sql_num NUMBER(3);
2399 l_org_name hr_all_organization_units.name%TYPE;
2400 l_name hr_all_organization_units.name%TYPE;
2401 l_start hr_all_organization_units.date_from%TYPE;
2402 l_end hr_all_organization_units.date_to%TYPE;
2403 l_cc VARCHAR2(240); -- Cost Center value
2404 l_cc_desc fnd_flex_values_vl.description%TYPE;
2405 l_co VARCHAR2(240); -- Company value
2406 l_co_desc fnd_flex_values_vl.description%TYPE;
2407 l_co_vs_name fnd_flex_value_sets.flex_value_set_name%TYPE;
2408 l_cc_vs_name fnd_flex_value_sets.flex_value_set_name%TYPE;
2409 l_org_start_date fnd_flex_values_vl.start_date_active%TYPE;
2410 l_org_end_date fnd_flex_values_vl.end_date_active%TYPE;
2411 l_delete_synced_orgs_flag BOOLEAN := FALSE;
2412
2413 TYPE cur_type IS REF CURSOR;
2414 c_ref cur_type;
2415
2416
2417 -- Cursor to fetch the company codes within a business group
2418 -- and within the value set
2419 CURSOR c_companies ( cp_co IN VARCHAR2
2420 , cp_business_group_id IN NUMBER
2421 , cp_co_vs_id IN NUMBER
2422 ) IS
2423 SELECT DISTINCT CC.org_information3 company_code
2424 FROM hr_all_organization_units UNITS
2425 ,hr_organization_information CLASS
2426 ,hr_organization_information CC
2427 WHERE UNITS.organization_id = CLASS.organization_id
2428 AND CLASS.org_information_context = 'CLASS'
2429 AND CLASS.org_information1 = 'CC'
2430 AND CLASS.organization_id = CC.organization_id
2431 AND CC.org_information_context = 'Company Cost Center'
2432 AND CC.org_information2 IS NOT NULL
2433 AND CC.org_information3 IS NOT NULL
2434 AND CC.org_information3 = NVL(cp_co, CC.org_information3)
2435 AND CC.org_information4 IS NULL
2436 AND CC.org_information5 IS NULL
2437 AND UNITS.business_group_id = cp_business_group_id
2438 AND CC.org_information3 IN ( SELECT flex_value
2439 FROM fnd_flex_values_vl
2440 WHERE flex_value_set_id = cp_co_vs_id
2441 );
2442
2443 -- Cursor to fetch Cost Center Codes from the specified value set
2444 CURSOR c_cost_centers(cp_cc_vs_id IN NUMBER) IS
2445 SELECT FFV.flex_value cost_center_code
2446 FROM fnd_flex_values_vl FFV
2447 WHERE FFV.flex_value_set_id = cp_cc_vs_id
2448 ORDER BY FFV.flex_value;
2449
2450 -- Local exceptions
2451 e_no_records_to_process EXCEPTION;
2452
2453 BEGIN
2454
2455 hr_utility.set_location('Entering: '|| l_proc, 10);
2456
2457 -- Derive the Company value set and Cost Center value sets
2458 IF l_retcode = 0 THEN
2459 hr_utility.set_location(l_proc, 20);
2460 l_retcode := derive_value_sets( p_coa_id
2461 , l_co_seg
2462 , l_cc_seg
2463 , l_co_vs_id
2464 , l_cc_vs_id
2465 );
2466 writelog('Company Value Set Id: '||l_co_vs_id,'Y');
2467 writelog('Cost Center Value Set Id: '||l_cc_vs_id,'Y');
2468 END IF; -- retcode is 0
2469
2470 -- Get records to process into temp table
2471 IF l_retcode = 0 THEN
2472 hr_utility.set_location(l_proc, 30);
2473
2474 IF p_source = 'CCVS' THEN
2475 -- Use company organizations within the business group and the
2476 -- cost center value set to build the company cost center
2477 -- combinations to process.
2478 hr_utility.set_location(l_proc, 40);
2479
2480 -- Loop for company codes within the business group
2481 FOR cr_company IN c_companies(p_co, p_business_group_id, l_co_vs_id) LOOP
2482 hr_utility.set_message(800,'PER_50159_COMP_CODE');
2483 fnd_message.set_token('COMP_CODE',cr_company.company_code);
2484 writelog(fnd_message.get(),'Y');
2485
2486 FOR cr_cost_center IN c_cost_centers(l_cc_vs_id) LOOP
2487 hr_utility.set_message(800,'PER_50160_COST_CENTER_CODE');
2488 fnd_message.set_token('COST_CENTER_CODE',cr_cost_center.cost_center_code);
2489 writelog(fnd_message.get(),'Y');
2490
2491 -- Insert record into temp table
2492 EXECUTE IMMEDIATE 'INSERT INTO '||g_temp_table_name||
2493 ' ( coa_id'||
2494 ' , co'||
2495 ' , co_vs_id'||
2496 ' , cc'||
2497 ' , cc_vs_id'||
2498 ' ) VALUES'||
2499 ' ( :1, :2, :3, :4, :5 )'
2500 USING p_coa_id
2501 , cr_company.company_code
2502 , l_co_vs_id
2503 , cr_cost_center.cost_center_code
2504 , l_cc_vs_id;
2505 l_rowcount := l_rowcount + 1;
2506 END LOOP; -- Cost Centers
2507 END LOOP; -- Companies
2508
2509 IF l_rowcount = 0 THEN
2510 RAISE e_no_records_to_process;
2511 ELSE
2512 writelog('Inserted '||l_rowcount||' Company-CostCenter records into temp','Y');
2513 END IF;
2514
2515 -- Trigger subsequent delete of records from temp table where records
2516 -- in GL_CODE_COMBINATIONS are already synchronized with HR Orgs
2517 -- i.e. COMPANY_COST_CENTER_ORG_ID is not null.
2518 -- Phase 2a - Note that this code is moved below, after the update of existing orgs,
2519 -- as the corresponding orgs for these rows may still need to be updated.
2520 --
2521 l_delete_synced_orgs_flag := true;
2522
2523 ELSE -- p_source is GLCC
2524 -- Get the company cost center combinations to process from table
2525 -- GL_CODE_COMBIANTIONS.
2526 hr_utility.set_location(l_proc, 60);
2527
2528 EXECUTE IMMEDIATE 'INSERT INTO '||g_temp_table_name||
2529 ' ( coa_id'||
2530 ' , co'||
2531 ' , co_vs_id'||
2532 ' , cc'||
2533 ' , cc_vs_id )'||
2534 ' SELECT DISTINCT'||
2535 ' '||p_coa_id||
2536 ', '||l_co_seg||
2537 ', '||l_co_vs_id||
2538 ', '||l_cc_seg||
2539 ', '||l_cc_vs_id||
2540 ' FROM gl_code_combinations'||
2541 ' WHERE company_cost_center_org_id IS NULL'||
2542 ' AND summary_flag = ''N'''||
2543 ' AND chart_of_accounts_id = :1'||
2544 ' AND '||l_co_seg||' = NVL(:2,'||l_co_seg||')'
2545 USING p_coa_id, p_co;
2546
2547 l_rowcount := SQL%ROWCOUNT;
2548 IF l_rowcount = 0 THEN
2549 RAISE e_no_records_to_process;
2550 ELSE
2551 writelog('Inserted '||l_rowcount||' Company-CostCenter records from GLCC into temp','Y');
2552 END IF;
2553
2554 -- Delete combinations where company code does not exist as a company organization.
2555 hr_utility.set_location(l_proc, 70);
2556
2557 EXECUTE IMMEDIATE 'DELETE FROM '||g_temp_table_name||
2558 ' WHERE (co,TO_CHAR(co_vs_id)) NOT IN ('||
2559 ' SELECT DISTINCT CC.org_information3, CC.org_information2'||
2560 ' FROM hr_all_organization_units UNITS,'||
2561 ' hr_organization_information CLASS,'||
2562 ' hr_organization_information CC'||
2563 ' WHERE UNITS.organization_id = CLASS.organization_id'||
2564 ' AND CLASS.org_information_context = ''CLASS'''||
2565 ' AND CLASS.org_information1 = ''CC'''||
2566 ' AND CLASS.organization_id = CC.organization_id'||
2567 ' AND CC.org_information_context = ''Company Cost Center'''||
2568 ' AND UNITS.business_group_id = :1 '||
2569 ' AND CC.org_information4 IS NULL'||
2570 ' AND CC.org_information5 IS NULL )'
2571 USING p_business_group_id;
2572
2573 l_delrowcount := SQL%ROWCOUNT;
2574 writelog('Deleted '||l_delrowcount||' missing Company-ORG records from temp','Y');
2575 IF l_rowcount = l_delrowcount THEN
2576 RAISE e_no_records_to_process;
2577 END IF;
2578
2579 END IF; -- p_source is CCVS
2580
2581 END IF; -- rectcode is 0
2582
2583 -- Create a performance index
2584 IF l_retcode = 0 THEN
2585 hr_utility.set_location(l_proc, 80);
2586
2587 EXECUTE IMMEDIATE 'CREATE INDEX '||g_temp_table_name||'_n1 ON '||
2588 g_temp_table_name||'(co,co_vs_id,cc,cc_vs_id)';
2589
2590 -- Note that the above DDL will implicitly commit the previously executed DML
2591 END IF; -- rectcode is 0
2592
2593
2594 -- Phase 2a: Do maintenance of corresponding orgs first running in CREATE_MAINTAIN mode,
2595 -- and then delete rows in temp table.
2596
2597 IF l_retcode = 0 AND p_mode = 'CREATE_MAINTAIN' THEN
2598 hr_utility.set_location(l_proc, 90);
2599
2600 IF p_sync_org_name = 'Y' OR p_sync_org_dates = 'Y' THEN
2601
2602 -- choose the org update SQL based on params
2603 IF p_sync_org_name = 'Y' AND p_sync_org_dates = 'Y' THEN l_sql_num := 1;
2604 ELSIF p_sync_org_name = 'Y' AND p_sync_org_dates = 'N' THEN l_sql_num := 2;
2605 ELSIF p_sync_org_name = 'N' AND p_sync_org_dates = 'Y' THEN l_sql_num := 3;
2606 END IF;
2607
2608 BEGIN
2609
2610 -- PHASE 2a Conditionally update existing orgs' names, start_dates
2611 -- and end_dates to ensure they match the profile option name configuration
2612 -- and the latest values from their value sets descriptions and dates.
2613 -- Update will only be done if new and existing values differ.
2614 OPEN c_ref FOR ' SELECT UNITS.ROWID, UNITS.name, UNITS.date_from, UNITS.date_to, '||
2615 ' t.CO_VS_ID, t.CO, t.CC_VS_ID, t.CC'||
2616 ' FROM '|| g_temp_table_name ||' t , '||
2617 ' hr_organization_information CC,'||
2618 ' hr_organization_information CLASS,'||
2619 ' hr_all_organization_units UNITS'||
2620 ' WHERE TO_CHAR(t.co_vs_id) = CC.org_information2 '||
2621 ' AND t.co = CC.org_information3 '||
2622 ' AND TO_CHAR(t.cc_vs_id) = CC.org_information4 '||
2623 ' AND t.cc = CC.org_information5 '||
2624 ' AND UNITS.organization_id = CLASS.organization_id'||
2625 ' AND CLASS.org_information_context = ''CLASS'''||
2626 ' AND CLASS.org_information1 = ''CC'''||
2627 ' AND CLASS.organization_id = CC.organization_id'||
2628 ' AND CC.org_information_context = ''Company Cost Center'''||
2629 ' FOR UPDATE OF UNITS.name NOWAIT ';
2630
2631 LOOP
2632 FETCH c_ref INTO l_rowid, l_name, l_start, l_end, l_co_vs_id, l_co, l_cc_vs_id, l_cc;
2633 EXIT WHEN c_ref%NOTFOUND;
2634
2635 l_org_name := null;
2636 l_retcode := derive_org_name( p_mode
2637 , l_co
2638 , l_co_vs_id
2639 , l_cc
2640 , l_cc_vs_id
2641 , l_co_desc
2642 , l_cc_desc
2643 , l_org_name
2644 , l_org_start_date
2645 , l_org_end_date
2646 );
2647
2648 IF l_retcode = 0 THEN
2649 IF l_sql_num = 1 THEN
2650 IF l_org_name <> l_name OR l_org_start_date <> l_start OR
2651 nvl(l_org_end_date,hr_api.g_sot) <> nvl(l_end,hr_api.g_sot) THEN
2652
2653 UPDATE hr_all_organization_units UNITS
2654 SET UNITS.name = l_org_name,
2655 UNITS.date_from = l_org_start_date,
2656 UNITS.date_to = l_org_end_date
2657 WHERE UNITS.rowid = l_rowid;
2658
2659 l_updrowcount := l_updrowcount+1;
2660 END IF;
2661 ELSIF l_sql_num = 2 THEN
2662 IF l_org_name <> l_name THEN
2663
2664 UPDATE hr_all_organization_units UNITS
2665 SET UNITS.name = l_org_name
2666 WHERE UNITS.rowid = l_rowid;
2667
2668 l_updrowcount := l_updrowcount+1;
2669 END IF;
2670 ELSIF l_sql_num = 3 THEN
2671 IF l_org_start_date <> l_start OR
2672 nvl(l_org_end_date,hr_api.g_sot) <> nvl(l_end,hr_api.g_sot) THEN
2673
2674 UPDATE hr_all_organization_units UNITS
2675 SET UNITS.date_from = l_org_start_date,
2676 UNITS.date_to = l_org_end_date
2677 WHERE UNITS.rowid = l_rowid;
2678
2679 l_updrowcount := l_updrowcount+1;
2680 END IF;
2681 END IF;
2682
2683 END IF;
2684
2685 END LOOP;
2686 CLOSE c_ref;
2687
2688 -- Commit the Org updates as this is atomic unit or work
2689 IF l_updrowcount > 0 THEN
2690 COMMIT;
2691 writelog('Updated '||l_updrowcount||' organization details.','N');
2692 ELSE
2693 writelog('No organization details were updated.','N');
2694 ROLLBACK; -- just in case
2695 END IF;
2696
2697 EXCEPTION
2698 WHEN OTHERS THEN
2699 hr_utility.set_location('Leaving: '|| l_proc, 93);
2700 hr_utility.set_location(SQLERRM, 93);
2701 writelog(SQLERRM,'N');
2702 ROLLBACK; -- explicitly release any lock taken by NOWAIT
2703 IF c_ref%ISOPEN THEN
2704 CLOSE c_ref;
2705 END IF;
2706 l_retcode := 2;
2707 END;
2708
2709 END IF; -- Phase 2a update org name and dates
2710
2711
2712 hr_utility.set_location(l_proc, 95);
2713
2714 -- Now updates of corresponding orgs have been done,
2715 -- perform relevant delete ops to discard existing / synchroniized rows
2716 -- from g_temp_table_name
2717
2718 IF l_retcode = 0 and l_delete_synced_orgs_flag THEN
2719 hr_utility.set_location(l_proc, 50);
2720
2721 -- Note that l_co_seg and l_cc_seg hold the names of the columns
2722 -- which contain the company code value and cost center code value.
2723 EXECUTE IMMEDIATE 'DELETE FROM '||g_temp_table_name||
2724 ' WHERE (co,cc) IN ('||
2725 ' SELECT DISTINCT '||l_co_seg||','||l_cc_seg||' FROM gl_code_combinations'||
2726 ' WHERE chart_of_accounts_id = :1 AND company_cost_center_org_id IS NOT NULL)'
2727 USING p_coa_id;
2728
2729 l_delrowcount := SQL%ROWCOUNT;
2730 writelog('Deleted '||l_delrowcount||' already synchronized records from temp table','Y');
2731 IF l_rowcount = l_delrowcount THEN
2732 RAISE e_no_records_to_process;
2733 END IF;
2734 END IF; -- retcode is 0
2735
2736 hr_utility.set_location(l_proc, 100);
2737
2738 -- Delete records from temp table where Company-CostCenter Orgs already exist
2739 -- in the system. These may not be already synchronized. This needs to be done
2740 -- only in CREATE_MAINTAIN mode but not in EXPORT mode. This routine does not
2741 -- get called for the SINGLE_ORG and SYNCHRONIZE modes.
2742
2743 EXECUTE IMMEDIATE 'DELETE FROM '||g_temp_table_name||
2744 ' WHERE (TO_CHAR(co_vs_id), co'||
2745 ', TO_CHAR(cc_vs_id), cc) IN ('||
2746 ' SELECT CC.org_information2, CC.org_information3,'||
2747 ' CC.org_information4, CC.org_information5'||
2748 ' FROM hr_all_organization_units UNITS,'||
2749 ' hr_organization_information CLASS,'||
2750 ' hr_organization_information CC'||
2751 ' WHERE UNITS.organization_id = CLASS.organization_id'||
2752 ' AND CLASS.org_information_context = ''CLASS'''||
2753 ' AND CLASS.org_information1 = ''CC'''||
2754 ' AND CLASS.organization_id = CC.organization_id'||
2755 ' AND CC.org_information_context = ''Company Cost Center'''||
2756 ' AND UNITS.business_group_id = :1 )'
2757 USING p_business_group_id;
2758
2759 l_del1rowcount := SQL%ROWCOUNT;
2760 writelog('Deleted '||l_del1rowcount||' ORG already existing records from temp table','Y');
2761 IF l_rowcount = (l_delrowcount + l_del1rowcount) THEN
2762 RAISE e_no_records_to_process;
2763 END IF;
2764 END IF; -- retcode is 0 and mode is CREATE_MAINTAIN
2765
2766 -- Close transaction as this is an integral unit of work
2767 IF l_retcode = 0 THEN
2768 hr_utility.set_location(l_proc, 105);
2769
2770 COMMIT;
2771 END IF; -- retcode is 0
2772
2773 hr_utility.set_location('Leaving: '|| l_proc, 110);
2774
2775 RETURN l_retcode;
2776
2777 EXCEPTION
2778
2779 WHEN e_no_records_to_process THEN
2780 hr_utility.set_location('Leaving: '|| l_proc, 120);
2781 hr_utility.set_message(800,'PER_50154_NO_CO_CC_COMBS');
2782 writelog(fnd_message.get(),'N');
2783 l_retcode := 1;
2784 RETURN l_retcode;
2785
2786 WHEN OTHERS THEN
2787 hr_utility.set_location('Leaving: '|| l_proc, 130);
2788 hr_utility.set_location(SQLERRM, 135);
2789 writelog(SQLERRM,'N');
2790 l_retcode := 2;
2791 RETURN l_retcode;
2792
2793 END get_co_cc_combinations;
2794
2795 --
2796 -- Populate Co-CC combinations to synchronize into temporary table
2797 --
2798 -- Derive company and cost center value sets for the chart of accounts.
2799 -- Get GL_CODE_COMBINATIONS records that have not yet been synchronized
2800 -- into temp table.
2801 -- Create an index on temp table.
2802 -- Delete those combinations for which a company org cannot be found.
2803 -- Close the transaction as this completes a unit of work.
2804 --
2805 FUNCTION get_gl_co_cc_to_sync( p_business_group_id IN NUMBER
2806 , p_coa_id IN NUMBER
2807 , p_co IN VARCHAR2
2808 , p_duplicate_orgs_found IN OUT NOCOPY BOOLEAN
2809 ) RETURN NUMBER IS
2810
2811 -- Local variables
2812 l_proc VARCHAR2(50) := g_package||'.get_gl_co_cc_to_sync';
2813 l_retcode NUMBER := 0;
2814 l_rowcount NUMBER := 0;
2815 l_delrowcount NUMBER := 0;
2816 l_del1rowcount NUMBER := 0;
2817 l_co_seg VARCHAR2(240); -- Col name of seg holding company value
2818 l_cc_seg VARCHAR2(240); -- Col name of seg holding cost center value
2819 l_co_vs_id NUMBER(15); -- Id of company value set
2820 l_cc_vs_id NUMBER(15); -- Id of cost center value set
2821
2822 -- Local exceptions
2823 e_no_records_to_process EXCEPTION;
2824
2825 BEGIN
2826
2827 hr_utility.set_location('Entering: '|| l_proc, 10);
2828 p_duplicate_orgs_found := FALSE;
2829
2830 -- Derive the Company value set and Cost Center value sets
2831 IF l_retcode = 0 THEN
2832 hr_utility.set_location(l_proc, 20);
2833
2834 l_retcode := derive_value_sets( p_coa_id
2835 , l_co_seg
2836 , l_cc_seg
2837 , l_co_vs_id
2838 , l_cc_vs_id
2839 );
2840
2841 writelog('Company Value Set Id: '||l_co_vs_id,'Y');
2842 writelog('Cost Center Value Set Id: '||l_cc_vs_id,'Y');
2843 END IF; -- retcode is 0
2844
2845 -- Get records from GL_CODE_COMBINATIONS for the given COA_ID and the
2846 -- account code combination is not for a summary account and the
2847 -- company cost center org id is null, i.e. the CCID has not already
2848 -- been synchronized to an ORG.
2849 IF l_retcode = 0 THEN
2850 hr_utility.set_location(l_proc, 30);
2851
2852 EXECUTE IMMEDIATE 'INSERT INTO '||g_temp_table_name||
2853 ' ( coa_id'||
2854 ' , co'||
2855 ' , co_vs_id'||
2856 ' , cc'||
2857 ' , cc_vs_id'||
2858 ' , ccid'||
2859 ' , org_id )'||
2860 ' SELECT chart_of_accounts_id'||
2861 ', '||l_co_seg||
2862 ', '||l_co_vs_id||
2863 ', '||l_cc_seg||
2864 ', '||l_cc_vs_id||
2865 ', code_combination_id'||
2866 ', NULL'||
2867 ' FROM gl_code_combinations'||
2868 ' WHERE company_cost_center_org_id IS NULL'||
2869 ' AND summary_flag = ''N'''||
2870 ' AND chart_of_accounts_id = :1'
2871 USING p_coa_id;
2872
2873 l_rowcount := SQL%ROWCOUNT;
2874 IF l_rowcount = 0 THEN
2875 RAISE e_no_records_to_process;
2876 END IF;
2877
2878 writelog('Inserted '||l_rowcount||' records to synchronize into temp table','Y');
2879
2880 END IF; -- retcode is 0
2881
2882 -- Create a performance index
2883 IF l_retcode = 0 THEN
2884 hr_utility.set_location(l_proc, 40);
2885
2886 EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX '||g_temp_table_name||'_u1 ON '||
2887 g_temp_table_name||'(ccid)';
2888
2889 -- Note that the above index creation will implicitly commit the transaction
2890 -- for populating records into the temp table.
2891 END IF; -- retcode is 0
2892
2893 -- Delete those combinations for which a company cost center organization
2894 -- cannot be found.
2895 IF l_retcode = 0 THEN
2896 hr_utility.set_location(l_proc, 50);
2897
2898 EXECUTE IMMEDIATE 'DELETE FROM '||g_temp_table_name||
2899 ' WHERE (TO_CHAR(co_vs_id), co'||
2900 ', TO_CHAR(cc_vs_id), cc) NOT IN'||
2901 ' (SELECT DISTINCT CC.org_information2'||
2902 ', CC.org_information3'||
2903 ', CC.org_information4'||
2904 ', CC.org_information5'||
2905 ' FROM hr_all_organization_units UNITS'||
2906 ', hr_organization_information CLASS'||
2907 ', hr_organization_information CC'||
2908 ' WHERE UNITS.organization_id = CLASS.organization_id'||
2909 ' AND CLASS.org_information_context = ''CLASS'''||
2910 ' AND CLASS.org_information1 = ''CC'''||
2911 ' AND CLASS.organization_id = CC.organization_id'||
2912 ' AND CC.org_information_context = ''Company Cost Center'''||
2913 ' AND CC.org_information2 IS NOT NULL'||
2914 ' AND CC.org_information3 IS NOT NULL'||
2915 ' AND CC.org_information4 IS NOT NULL'||
2916 ' AND CC.org_information5 IS NOT NULL'||
2917 ' AND UNITS.business_group_id = :1)'
2918 USING p_business_group_id;
2919
2920 l_delrowcount := SQL%ROWCOUNT;
2921 writelog('Deleted '||l_delrowcount||' missing Company Cost Center ORG records from temp table','Y');
2922 IF l_delrowcount = l_rowcount THEN
2923 RAISE e_no_records_to_process;
2924 END IF;
2925 END IF; -- retcode is 0
2926
2927 -- Delete combinations where duplicate matching Company Cost Center ORGs
2928 -- exist as these are invalid scenarios and cannot be automatically
2929 -- synchronized.
2930 IF l_retcode = 0 THEN
2931 hr_utility.set_location(l_proc, 60);
2932
2933 EXECUTE IMMEDIATE 'DELETE FROM '||g_temp_table_name||
2934 ' WHERE (TO_CHAR(co_vs_id), co'||
2935 ', TO_CHAR(cc_vs_id), cc) IN'||
2936 ' (SELECT CC.org_information2'||
2937 ', CC.org_information3'||
2938 ', CC.org_information4'||
2939 ', CC.org_information5'||
2940 ' FROM hr_all_organization_units UNITS'||
2941 ', hr_organization_information CLASS'||
2942 ', hr_organization_information CC'||
2943 ' WHERE UNITS.organization_id = CLASS.organization_id'||
2944 ' AND CLASS.org_information_context = ''CLASS'''||
2945 ' AND CLASS.org_information1 = ''CC'''||
2946 ' AND CLASS.organization_id = CC.organization_id'||
2947 ' AND CC.org_information_context = ''Company Cost Center'''||
2948 ' AND CC.org_information2 IS NOT NULL'||
2949 ' AND CC.org_information3 IS NOT NULL'||
2950 ' AND CC.org_information4 IS NOT NULL'||
2951 ' AND CC.org_information5 IS NOT NULL'||
2952 ' AND UNITS.business_group_id = :1'||
2953 ' GROUP BY CC.org_information2'||
2954 ', CC.org_information3'||
2955 ', CC.org_information4'||
2956 ', CC.org_information5'||
2957 ' HAVING COUNT(*) > 1)'
2958 USING p_business_group_id;
2959
2960 l_del1rowcount := SQL%ROWCOUNT;
2961 writelog('Deleted '||l_del1rowcount||' duplicate ORG records from temp table','Y');
2962 IF l_del1rowcount > 0 THEN
2963 p_duplicate_orgs_found := TRUE;
2964 END IF;
2965 IF (l_delrowcount + l_del1rowcount) = l_rowcount THEN
2966 RAISE e_no_records_to_process;
2967 END IF;
2968 END IF; -- retcode is 0
2969
2970 -- Close transaction as this is an integral unit of work
2971 IF l_retcode = 0 THEN
2972 hr_utility.set_location(l_proc, 70);
2973
2974 COMMIT;
2975 END IF; -- retcode is 0
2976
2977 hr_utility.set_location('Leaving: '|| l_proc, 80);
2978
2979 RETURN l_retcode;
2980
2981 EXCEPTION
2982
2983 WHEN e_no_records_to_process THEN
2984 hr_utility.set_location('Leaving: '|| l_proc, 90);
2985 hr_utility.set_message(800,'PER_50154_NO_CO_CC_COMBS');
2986 writelog(fnd_message.get(),'N');
2987 l_retcode := 1;
2988 RETURN l_retcode;
2989
2990 WHEN OTHERS THEN
2991 hr_utility.set_location('Leaving: '|| l_proc, 100);
2992 hr_utility.set_location(SQLERRM, 105);
2993 writelog(SQLERRM,'N');
2994 l_retcode := 2;
2995 RETURN l_retcode;
2996
2997 END get_gl_co_cc_to_sync;
2998
2999 --
3000 -- Write header file
3001 --
3002 FUNCTION write_hdr_file( p_hdr_file_handle IN utl_file.file_type
3003 , p_bg_name IN VARCHAR2
3004 , p_dat_file IN VARCHAR2
3005 ) RETURN NUMBER IS
3006
3007 -- Local variables
3008 l_proc VARCHAR2(50) := g_package||'.write_hdr_file';
3009 l_retcode NUMBER := 0;
3010 l_tab VARCHAR2(5) := fnd_global.tab;
3011
3012 BEGIN
3013
3014 hr_utility.set_location('Entering: '|| l_proc, 10);
3015
3016 -- Write header file header
3017 utl_file.put_line( p_hdr_file_handle
3018 , 'Header'||l_tab||'Start');
3019 utl_file.put_line( p_hdr_file_handle
3020 , 'Batch Name'||l_tab||SUBSTR(p_bg_name||'-'||TO_CHAR(SYSDATE,'YYYY/MM/DD'),1,70));
3021 utl_file.put_line( p_hdr_file_handle
3022 , 'Date'||l_tab||TO_CHAR(SYSDATE,'YYYY/MM/DD'));
3023 utl_file.put_line( p_hdr_file_handle
3024 , 'Version'||l_tab||'1.0');
3025 utl_file.put_line( p_hdr_file_handle
3026 , 'Date Format'||l_tab||'YYYY/MM/DD');
3027 utl_file.put_line( p_hdr_file_handle
3028 , 'Number Format'||l_tab||'999999999999999');
3029 utl_file.put_line( p_hdr_file_handle
3030 , 'Header'||l_tab||'End');
3031
3032 hr_utility.set_location(l_proc, 20);
3033
3034 -- Write header file data
3035 utl_file.put_line( p_hdr_file_handle
3036 , 'Files'||l_tab||'Start');
3037 utl_file.put_line( p_hdr_file_handle
3038 , 'create_company_cost_center'||l_tab||p_dat_file);
3039 utl_file.put_line( p_hdr_file_handle
3040 , 'Files'||l_tab||'End');
3041
3042 hr_utility.set_location('Leaving: '|| l_proc, 30);
3043
3044 RETURN l_retcode;
3045
3046 EXCEPTION
3047
3048 WHEN OTHERS THEN
3049 hr_utility.set_location('Leaving: '|| l_proc, 40);
3050 hr_utility.set_location(SQLERRM, 45);
3051 writelog(SQLERRM,'N');
3052 l_retcode := 2;
3053 RETURN l_retcode;
3054
3055 END write_hdr_file;
3056
3057 --
3058 -- Write data file
3059 --
3060 FUNCTION write_dat_file( p_dat_file_handle IN utl_file.file_type
3061 ) RETURN NUMBER IS
3062
3063 -- Local variables
3064 l_proc VARCHAR2(50) := g_package||'.write_dat_file';
3065 l_retcode NUMBER := 0;
3066 l_tab VARCHAR2(5) := fnd_global.tab;
3067 l_dat_rec_count NUMBER := 0;
3068 l_org_name hr_all_organization_units.name%TYPE;
3069 l_cc VARCHAR2(240); -- Cost Center value
3070 l_cc_desc fnd_flex_values_vl.description%TYPE;
3071 l_co VARCHAR2(240); -- Company value
3072 l_co_desc fnd_flex_values_vl.description%TYPE;
3073 l_co_vs_name fnd_flex_value_sets.flex_value_set_name%TYPE;
3074 l_cc_vs_name fnd_flex_value_sets.flex_value_set_name%TYPE;
3075 l_org_start_date fnd_flex_values_vl.start_date_active%TYPE;
3076
3077 TYPE cur_type IS REF CURSOR;
3078 c_list cur_type;
3079
3080 BEGIN
3081
3082 hr_utility.set_location('Entering: '|| l_proc, 10);
3083
3084 -- Write data file header
3085 utl_file.put_line( p_dat_file_handle
3086 , 'Descriptor'||l_tab||'Start');
3087 utl_file.put_line( p_dat_file_handle
3088 , 'API'||l_tab||'create_company_cost_center');
3089 utl_file.put_line( p_dat_file_handle
3090 , 'Title'||l_tab||'create company cost center');
3091 utl_file.put_line( p_dat_file_handle
3092 , 'Process Order'||l_tab||'10');
3093 utl_file.put_line( p_dat_file_handle
3094 , 'Descriptor'||l_tab||'End');
3095
3096 hr_utility.set_location(l_proc, 20);
3097
3098 -- Write data file data titles
3099 utl_file.put_line( p_dat_file_handle
3100 , 'Data'||l_tab||'Start');
3101 utl_file.put_line( p_dat_file_handle
3102 , 'ID' ||l_tab||
3103 'organization_name' ||l_tab||
3104 'costcenter_id' ||l_tab||
3105 'costcenter_name' ||l_tab||
3106 'company_id' ||l_tab||
3107 'company_name' ||l_tab||
3108 'costcenter_valueset_name'||l_tab||
3109 'company_valueset_name' ||l_tab||
3110 'start_date' ||l_tab||
3111 'language_code'
3112 );
3113
3114 hr_utility.set_location(l_proc, 30);
3115
3116 -- Write data file data
3117 OPEN c_list FOR 'SELECT org_name' ||
3118 ', cc' ||
3119 ', cc_desc' ||
3120 ', co' ||
3121 ', co_desc' ||
3122 ', cc_vs_name' ||
3123 ', co_vs_name' ||
3124 ', org_start_date' ||
3125 ' FROM '||g_temp_table_name||
3126 ' ORDER BY cc,co';
3127 LOOP
3128 FETCH c_list INTO l_org_name
3129 , l_cc
3130 , l_cc_desc
3131 , l_co
3132 , l_co_desc
3133 , l_cc_vs_name
3134 , l_co_vs_name
3135 , l_org_start_date;
3136 EXIT WHEN c_list%NOTFOUND;
3137
3138 l_dat_rec_count := l_dat_rec_count + 1;
3139
3140 utl_file.put_line( p_dat_file_handle
3141 , l_dat_rec_count ||l_tab|| -- Data Record Id
3142 l_org_name ||l_tab|| -- Org Name
3143 l_cc ||l_tab|| -- Cost Center Code
3144 l_cc_desc ||l_tab|| -- Cost Center Name
3145 l_co ||l_tab|| -- Company Code
3146 l_co_desc ||l_tab|| -- Company Name
3147 l_cc_vs_name ||l_tab|| -- Cost Cetner Valueset Name
3148 l_co_vs_name ||l_tab|| -- Company Valueset Name
3149 l_org_start_date||l_tab|| -- Org Start Date
3150 'US' -- Language Code
3151 );
3152 END LOOP;
3153 CLOSE c_list;
3154
3155 utl_file.put_line( p_dat_file_handle
3156 , 'Data'||l_tab||'End');
3157
3158 writelog('Spooled '||l_dat_rec_count||' data records to data file','Y');
3159
3160 hr_utility.set_location('Leaving: '|| l_proc, 40);
3161
3162 RETURN l_retcode;
3163
3164 EXCEPTION
3165
3166 WHEN OTHERS THEN
3167 hr_utility.set_location('Leaving: '|| l_proc, 50);
3168 hr_utility.set_location(SQLERRM, 55);
3169 writelog(SQLERRM,'N');
3170 IF c_list%ISOPEN THEN
3171 CLOSE c_list;
3172 END IF;
3173 l_retcode := 2;
3174 RETURN l_retcode;
3175
3176 END write_dat_file;
3177
3178 --
3179 -- Spool export data to file
3180 --
3181 FUNCTION spool_to_file( p_bg_id IN NUMBER
3182 , p_bg_name IN VARCHAR2
3183 ) RETURN NUMBER IS
3184
3185 -- Local variables
3186 l_proc VARCHAR2(50) := g_package||'.spool_to_file';
3187 l_retcode NUMBER := 0;
3188 l_hdr_file VARCHAR2(80) := NULL;
3189 l_dat_file VARCHAR2(80) := NULL;
3190 l_hdr_file_handle utl_file.file_type;
3191 l_dat_file_handle utl_file.file_type;
3192
3193 BEGIN
3194
3195 hr_utility.set_location('Entering: '|| l_proc, 10);
3196
3197 -- Open export header and data files
3198 l_retcode := open_export_files( p_bg_id
3199 , p_bg_name
3200 , l_hdr_file
3201 , l_dat_file
3202 , l_hdr_file_handle
3203 , l_dat_file_handle
3204 );
3205
3206 -- Write header file
3207 IF l_retcode = 0 THEN
3208 hr_utility.set_location(l_proc, 20);
3209 l_retcode := write_hdr_file( l_hdr_file_handle
3210 , p_bg_name
3211 , l_dat_file
3212 );
3213 END IF;
3214
3215 -- Write data file
3216 IF l_retcode = 0 THEN
3217 hr_utility.set_location(l_proc, 30);
3218 l_retcode := write_dat_file( l_dat_file_handle
3219 );
3220 END IF;
3221
3222 -- Close export header and data files
3223 IF l_retcode = 0 THEN
3224 hr_utility.set_location(l_proc, 40);
3225 l_retcode := close_export_files( l_hdr_file_handle
3226 , l_dat_file_handle
3227 );
3228 END IF;
3229
3230 hr_utility.set_location('Leaving: '|| l_proc, 50);
3231
3232 RETURN l_retcode;
3233
3234 EXCEPTION
3235
3236 WHEN OTHERS THEN
3237 hr_utility.set_location('Leaving: '|| l_proc, 60);
3238 hr_utility.set_location(SQLERRM, 65);
3239 writelog(SQLERRM,'N');
3240 l_retcode := 2;
3241 RETURN l_retcode;
3242
3243 END spool_to_file;
3244
3245 --
3246 -- Process Single Org Mode
3247 --
3248 FUNCTION single_org_mode( p_mode IN VARCHAR2
3249 , p_ccid IN NUMBER
3250 ) RETURN NUMBER IS
3251
3252 -- Local variables
3253 l_proc VARCHAR2(50) := g_package||'.single_org_mode';
3254 l_retcode NUMBER := 0;
3255 l_coa_id gl_code_combinations.chart_of_accounts_id%TYPE;
3256 l_co_seg VARCHAR2(240); -- Col name of seg holding company value
3257 l_cc_seg VARCHAR2(240); -- Col name of seg holding cost center value
3258 l_co_vs_id NUMBER(15); -- Id of company value set
3259 l_cc_vs_id NUMBER(15); -- Id of cost center value set
3260 l_co_desc fnd_flex_values_vl.description%TYPE;
3261 l_cc_desc fnd_flex_values_vl.description%TYPE;
3262 l_co_start_date fnd_flex_values_vl.start_date_active%TYPE;
3263 l_cc_start_date fnd_flex_values_vl.start_date_active%TYPE;
3264 l_co VARCHAR2(240);
3265 l_cc VARCHAR2(240);
3266 l_org_id hr_all_organization_units.organization_id%TYPE := -1;
3267 l_bg_id hr_all_organization_units.business_group_id%TYPE := -1;
3268
3269 TYPE cur_type IS REF CURSOR;
3270 c_list cur_type;
3271
3272 -- Local exceptions
3273 e_invalid_ccid EXCEPTION;
3274
3275 BEGIN
3276
3277 hr_utility.set_location('Entering: '|| l_proc, 10);
3278
3279 -- Get the chart of accounts identifier
3280 IF l_retcode = 0 THEN
3281 hr_utility.set_location(l_proc, 20);
3282 l_retcode := get_chart_of_accounts_id( p_ccid
3283 , l_coa_id
3284 );
3285 IF l_retcode = 0 THEN
3286 hr_utility.set_location(l_proc, 30);
3287 hr_utility.set_message(800,'PER_50172_PROC_CCID');
3288 fnd_message.set_token('CCID',p_ccid);
3289 fnd_message.set_token('COA_ID',l_coa_id);
3290 writelog(fnd_message.get(),'N');
3291 ELSE
3292 hr_utility.set_location(l_proc, 40);
3293 RAISE e_invalid_ccid;
3294 END IF;
3295 END IF; -- retocde
3296
3297 -- Get the segments and value sets for the chart of accounts
3298 IF l_retcode = 0 THEN
3299 hr_utility.set_location(l_proc, 50);
3300 l_retcode := derive_value_sets( l_coa_id
3301 , l_co_seg
3302 , l_cc_seg
3303 , l_co_vs_id
3304 , l_cc_vs_id
3305 );
3306 writelog('Company Value Set Id: '||l_co_vs_id,'Y');
3307 writelog('Cost Center Value Set Id: '||l_cc_vs_id,'Y');
3308 END IF; -- retocde
3309
3310 -- Get the actual company code and cost center code
3311 IF l_retcode = 0 THEN
3312 hr_utility.set_location(l_proc, 60);
3313 OPEN c_list FOR 'SELECT '||l_co_seg||', '||l_cc_seg||
3314 ' FROM gl_code_combinations'||
3315 ' WHERE code_combination_id = :1'
3316 USING p_ccid;
3317 FETCH c_list INTO l_co, l_cc;
3318 CLOSE c_list;
3319
3320 hr_utility.set_message(800,'PER_50159_COMP_CODE');
3321 fnd_message.set_token('COMP_CODE',l_co);
3322 writelog(fnd_message.get(),'N');
3323 hr_utility.set_message(800,'PER_50160_COST_CENTER_CODE');
3324 fnd_message.set_token('COST_CENTER_CODE',l_cc);
3325 writelog(fnd_message.get(),'N');
3326 END IF; -- retocde
3327
3328 -- Check if Organization already exists
3329 IF l_retcode = 0 THEN
3330 hr_utility.set_location(l_proc, 70);
3331 OPEN c_list FOR 'SELECT UNITS.organization_id'||
3332 ' FROM hr_all_organization_units UNITS'||
3333 ', hr_organization_information CLASS'||
3334 ', hr_organization_information CC'||
3335 ' WHERE UNITS.organization_id = CLASS.organization_id'||
3336 ' AND CLASS.org_information_context = ''CLASS'''||
3337 ' AND CLASS.org_information1 = ''CC'''||
3338 ' AND CLASS.organization_id = CC.organization_id'||
3339 ' AND CC.org_information_context = ''Company Cost Center'''||
3340 ' AND CC.org_information2 = :1'||
3341 ' AND CC.org_information3 = :2'||
3342 ' AND CC.org_information4 = :3'||
3343 ' AND CC.org_information5 = :4'
3344 USING TO_CHAR(l_co_vs_id), l_co, TO_CHAR(l_cc_vs_id), l_cc;
3345 FETCH c_list INTO l_org_id;
3346 IF c_list%NOTFOUND THEN
3347 hr_utility.set_location(l_proc, 80);
3348 writelog('No matching ORG found', 'Y');
3349 l_org_id := -1;
3350 ELSE
3351 hr_utility.set_location(l_proc, 90);
3352 writelog('Found matching ORG with Id:'||l_org_id, 'Y');
3353 END IF;
3354 CLOSE c_list;
3355 END IF; -- retocde
3356
3357 -- Get Business Group Identifier if Organization needs to be created
3358 IF l_retcode = 0 AND l_org_id = -1 THEN
3359 hr_utility.set_location(l_proc, 100);
3360 l_retcode := get_business_group_id( l_co_vs_id
3361 , l_co
3362 , l_bg_id
3363 );
3364 END IF; -- retocde
3365
3366 -- Create Organization if required
3367 IF l_retcode = 0 AND l_org_id = -1 THEN
3368 hr_utility.set_location(l_proc, 110);
3369 l_retcode := gen_org_co_cc_class( p_mode
3370 , l_bg_id
3371 , l_coa_id
3372 , l_co
3373 , l_co_vs_id
3374 , l_cc
3375 , l_cc_vs_id
3376 , l_org_id
3377 );
3378 IF l_retcode = 0 THEN
3379 hr_utility.set_location(l_proc, 120);
3380 -- Close transaction for ORG.
3381 COMMIT;
3382 ELSE -- retcode is not 0
3383 hr_utility.set_location(l_proc, 130);
3384 -- Write warning to LOG
3385 hr_utility.set_message(800,'PER_50170_ERR_PROC_CO_CC');
3386 fnd_message.set_token('COMP_CODE',l_co);
3387 fnd_message.set_token('COST_CENTER_CODE',l_cc);
3388 writelog(fnd_message.get(),'N');
3389 -- Close transaction for ORG.
3390 ROLLBACK;
3391 l_retcode := 2;
3392 l_org_id := -1;
3393 END IF;
3394 END IF; -- retocde is 0
3395
3396 -- Synchronize new organization (if created) to GL
3397 IF l_retcode = 0 AND l_org_id <> -1 THEN
3398 hr_utility.set_location(l_proc, 140);
3399 EXECUTE IMMEDIATE 'UPDATE gl_code_combinations'||
3400 ' SET company_cost_center_org_id = :1'||
3401 ', last_update_date = SYSDATE'||
3402 ', last_updated_by = :2'||
3403 ' WHERE code_combination_id = :3'
3404 USING l_org_id, fnd_global.user_id, p_ccid;
3405 writelog('Updated GL Code Combinations with Org Id:'||l_org_id||
3406 ' for CCID:'||p_ccid, 'Y');
3407 hr_utility.set_location('Updated GL Code Combinations with Org Id:'||
3408 l_org_id||' for CCID:'||p_ccid, 150);
3409 END IF; -- retocde is 0
3410
3411 hr_utility.set_location('Leaving: '|| l_proc, 160);
3412
3413 RETURN l_retcode;
3414
3415 EXCEPTION
3416
3417 WHEN e_invalid_ccid THEN
3418 hr_utility.set_location('Leaving: '|| l_proc, 170);
3419 hr_utility.set_message(800,'PER_50173_INVALID_CCID');
3420 fnd_message.set_token('CCID',p_ccid);
3421 writelog(fnd_message.get(),'N');
3422 l_retcode := 2;
3423 RETURN l_retcode;
3424
3425 WHEN OTHERS THEN
3426 hr_utility.set_location('Leaving: '|| l_proc, 180);
3427 hr_utility.set_location(SQLERRM, 185);
3428 writelog(SQLERRM,'N');
3429 IF c_list%ISOPEN THEN
3430 CLOSE c_list;
3431 END IF;
3432 l_retcode := 2;
3433 RETURN l_retcode;
3434
3435 END single_org_mode;
3436
3437 --
3438 -- Process Report Mode
3439 --
3440 FUNCTION report_mode( p_mode IN VARCHAR2
3441 , p_bg_id IN NUMBER
3442 , p_coa_id IN NUMBER
3443 , p_source IN VARCHAR2
3444 , p_bg_name IN VARCHAR2
3445 ) RETURN NUMBER IS
3446
3447 -- Local variables
3448 l_proc VARCHAR2(50) := g_package||'.report_mode';
3449 l_retcode NUMBER := 0;
3450
3451 BEGIN
3452
3453 hr_utility.set_location('Entering: '|| l_proc, 10);
3454
3455 -- Populate Company Cost Center Combinations into temp table and
3456 -- delete those which do not have company organizations in the
3457 -- current business group.
3458 IF l_retcode = 0 THEN
3459 hr_utility.set_location(l_proc, 20);
3460 l_retcode := get_co_cc_combinations( p_mode
3461 , p_bg_id
3462 , p_coa_id
3463 , NULL
3464 , p_source
3465 );
3466 END IF;
3467
3468 -- Fetch record details for reporting/exporting
3469 IF l_retcode = 0 THEN
3470 hr_utility.set_location(l_proc, 30);
3471 l_retcode := get_details_to_report( p_mode
3472 );
3473 END IF;
3474
3475 -- Spool data to output file
3476 IF l_retcode = 0 THEN
3477 hr_utility.set_location(l_proc, 40);
3478 l_retcode := spool_to_file( p_bg_id
3479 , p_bg_name
3480 );
3481 END IF;
3482
3483 hr_utility.set_location('Leaving: '|| l_proc, 50);
3484
3485 RETURN l_retcode;
3486
3487 EXCEPTION
3488
3489 WHEN OTHERS THEN
3490 hr_utility.set_location('Leaving: '|| l_proc, 60);
3491 hr_utility.set_location(SQLERRM, 65);
3492 writelog(SQLERRM,'N');
3493 l_retcode := 2;
3494 RETURN l_retcode;
3495
3496 END report_mode;
3497
3498 --
3499 -- Process Synchronize Mode
3500 --
3501 -- Fetch GL Co-CC combinations to process into temp table
3502 -- Update the temp table with Id of matching ORGs.
3503 -- Update GL_CODE_COMBINATIONS with matching Ids from temp table.
3504 -- Close transaction.
3505 --
3506 FUNCTION synchronize_mode( p_business_group_id IN NUMBER
3507 , p_coa_id IN NUMBER
3508 , p_co IN VARCHAR2
3509 ) RETURN NUMBER IS
3510
3511 -- Local variables
3512 l_proc VARCHAR2(50) := g_package||'.synchronize_mode';
3513 l_retcode NUMBER := 0;
3514 l_rowcount NUMBER := 0;
3515 l_delrowcount NUMBER := 0;
3516 l_updated_by NUMBER := 0;
3517 l_ccid NUMBER;
3518 l_co VARCHAR2(240);
3519 l_cc VARCHAR2(240);
3520 l_org_id hr_all_organization_units.organization_id%TYPE := -1;
3521 l_duplicate_orgs_found BOOLEAN;
3522
3523 -- Local exceptions
3524 e_no_records_to_process EXCEPTION;
3525
3526 TYPE cur_type IS REF CURSOR;
3527 c_list cur_type;
3528
3529 BEGIN
3530
3531 hr_utility.set_location('Entering: '|| l_proc, 10);
3532
3533 -- Populates Company Cost Center Combinations into temp table
3534 IF l_retcode = 0 THEN
3535 hr_utility.set_location(l_proc, 20);
3536 l_retcode := get_gl_co_cc_to_sync( p_business_group_id
3537 , p_coa_id
3538 , p_co
3539 , l_duplicate_orgs_found
3540 );
3541 END IF; -- retcode is 0
3542
3543 -- For each record in temp, identify matching ORG and update the Id
3544 -- into temp table.
3545 IF l_retcode = 0 THEN
3546 hr_utility.set_location(l_proc, 30);
3547
3548 EXECUTE IMMEDIATE 'UPDATE '||g_temp_table_name||' TEMP'||
3549 ' SET TEMP.org_id ='||
3550 ' (SELECT UNITS.organization_id'||
3551 ' FROM hr_all_organization_units UNITS'||
3552 ', hr_organization_information CLASS'||
3553 ', hr_organization_information CC'||
3554 ' WHERE UNITS.organization_id = CLASS.organization_id'||
3555 ' AND CLASS.org_information_context = ''CLASS'''||
3556 ' AND CLASS.org_information1 = ''CC'''||
3557 ' AND CLASS.organization_id = CC.organization_id'||
3558 ' AND CC.org_information_context = ''Company Cost Center'''||
3559 ' AND CC.org_information2 = TO_CHAR(TEMP.co_vs_id)'||
3560 ' AND CC.org_information3 = TEMP.co'||
3561 ' AND CC.org_information4 = TO_CHAR(TEMP.cc_vs_id)'||
3562 ' AND CC.org_information5 = TEMP.cc'||
3563 ' AND UNITS.business_group_id = :1)'
3564 USING p_business_group_id;
3565
3566 l_rowcount := SQL%ROWCOUNT;
3567 IF l_rowcount = 0 THEN
3568 RAISE e_no_records_to_process;
3569 END IF;
3570
3571 writelog('Updated '||SQL%ROWCOUNT||' ORGs to temp table','Y');
3572 END IF; -- retcode is 0
3573
3574 -- Update GL Code Combinations with the matching ORG Id values
3575 IF l_retcode = 0 THEN
3576 hr_utility.set_location(l_proc, 40);
3577
3578 EXECUTE IMMEDIATE 'UPDATE gl_code_combinations GCC'||
3579 ' SET ( GCC.company_cost_center_org_id'||
3580 ', GCC.last_update_date'||
3581 ', GCC.last_updated_by) ='||
3582 ' (SELECT SYNC.org_id'||
3583 ', TRUNC(SYSDATE)'||
3584 ','||l_updated_by||
3585 ' FROM '||g_temp_table_name||' SYNC'||
3586 ' WHERE SYNC.ccid = GCC.code_combination_id'||
3587 ' AND SYNC.org_id IS NOT NULL)'||
3588 ' WHERE GCC.company_cost_center_org_id IS NULL'||
3589 ' AND GCC.code_combination_id IN (SELECT ccid'||
3590 ' FROM '||g_temp_table_name||
3591 ' WHERE org_id IS NOT NULL)';
3592
3593 writelog('Updated '||SQL%ROWCOUNT||' ORGs to GL Code Combinations','Y');
3594 END IF; -- retcode is 0
3595
3596 -- Close transaction on successfull completion
3597 IF l_retcode = 0 THEN
3598 hr_utility.set_location(l_proc, 50);
3599
3600 COMMIT;
3601 END IF; -- retcode is 0
3602
3603 -- Log synchronized combinations with organizations
3604 IF l_retcode = 0 THEN
3605 hr_utility.set_location(l_proc, 60);
3606
3607 OPEN c_list FOR 'SELECT ccid, co, cc, org_id FROM '||g_temp_table_name;
3608 LOOP
3609 FETCH c_list INTO l_ccid, l_co, l_cc, l_org_id;
3610 EXIT WHEN c_list%NOTFOUND;
3611
3612 hr_utility.set_message(800,'PER_50169_SYNC_REC_DETAILS');
3613 fnd_message.set_token('CCID',l_ccid);
3614 fnd_message.set_token('COMP_CODE',l_co);
3615 fnd_message.set_token('COST_CENTER_CODE',l_cc);
3616 fnd_message.set_token('ORG_ID',l_org_id);
3617 writelog(fnd_message.get(),'N');
3618 END LOOP; -- records in temp table
3619 CLOSE c_list;
3620 END IF; -- retcode is 0
3621
3622 -- Flag whether duplicate Company Cost Center ORGs were found while
3623 -- getting records to synchronize by exiting with warnings.
3624 IF l_retcode = 0 AND l_duplicate_orgs_found THEN
3625 hr_utility.set_location(l_proc, 70);
3626 hr_utility.set_message(800,'PER_50176_DUPL_CO_CC_ORGS');
3627 writelog(fnd_message.get(),'N');
3628 l_retcode := 1;
3629 END IF; -- retcode is 0
3630
3631 hr_utility.set_location('Leaving: '|| l_proc, 80);
3632
3633 RETURN l_retcode;
3634
3635 EXCEPTION
3636
3637 WHEN e_no_records_to_process THEN
3638 hr_utility.set_location('Leaving: '|| l_proc, 90);
3639 hr_utility.set_message(800,'PER_50154_NO_CO_CC_COMBS');
3640 writelog(fnd_message.get(),'N');
3641 l_retcode := 1;
3642 RETURN l_retcode;
3643
3644 WHEN OTHERS THEN
3645 hr_utility.set_location('Leaving: '|| l_proc, 100);
3646 hr_utility.set_location(SQLERRM, 105);
3647 writelog(SQLERRM,'N');
3648 l_retcode := 2;
3649 RETURN l_retcode;
3650
3651 END synchronize_mode;
3652
3653 --
3654 -- Process Create/Maintain Mode
3655 --
3656 -- Fetch Co-CC combinations to process into temp table.
3657 -- For each record in temp table.
3658 -- Invoke gen_co_cc_class() to process the record.
3659 -- Commit record if successful else rollback.
3660 --
3661 FUNCTION create_maintain_mode( p_mode IN VARCHAR2
3662 , p_business_group_id IN NUMBER
3663 , p_coa_id IN NUMBER
3664 , p_co IN VARCHAR2
3665 , p_source IN VARCHAR2
3666 , p_sync_org_name IN VARCHAR2
3667 , p_sync_org_dates IN VARCHAR2
3668 ) RETURN NUMBER IS
3669
3670 -- Local variables
3671 l_proc VARCHAR2(50) := g_package||'.create_maintain_mode';
3672 l_retcode NUMBER := 0;
3673 l_rowcount NUMBER; -- Added for bug4346785.
3674 l_processed_recs BOOLEAN := FALSE;
3675 l_errored_recs BOOLEAN := FALSE;
3676 l_coa_id NUMBER;
3677 l_co VARCHAR2(240); -- Company value
3678 l_cc VARCHAR2(240); -- Cost Center value
3679 l_co_vs_id NUMBER(15); -- Id of company value set
3680 l_cc_vs_id NUMBER(15); -- Id of cost center value set
3681 l_org_id hr_all_organization_units.organization_id%TYPE;
3682
3683 -- Local exceptions
3684 e_no_records_to_process EXCEPTION;
3685
3686 TYPE cur_type IS REF CURSOR;
3687 c_list cur_type;
3688
3689 BEGIN
3690
3691 hr_utility.set_location('Entering: '|| l_proc, 10);
3692
3693 -- Populates Company Cost Center Combinations into temp table
3694 -- that require ORG/Classification records to be created or
3695 -- maintained.
3696 -- phase 2a - Also does maintenance of existing org names before those
3697 -- combinations are deleted.
3698 --
3699 -- Fix for bug 3837139 starts here.
3700 --
3701 /*
3702 IF l_retcode = 0 THEN
3703 hr_utility.set_location(l_proc, 20);
3704 l_retcode := get_co_cc_combinations( p_mode
3705 , p_business_group_id
3706 , p_coa_id
3707 , p_co
3708 , p_source
3709 , p_sync_org_name
3710 , p_sync_org_dates
3711 );
3712 END IF;
3713 */
3714 --
3715 IF l_retcode = 0 THEN
3716 hr_utility.set_location(l_proc, 20);
3717 l_retcode := get_co_cc_comb_into_temp(
3718 p_business_group_id
3719 ,p_coa_id
3720 ,p_co
3721 ,p_source
3722 ,l_rowcount -- Added for bug4346785.
3723 );
3724 END IF;
3725 --
3726
3727 -- Fix For Bug # 6929228 Starts ---
3728
3729 IF l_retcode = 0 THEN
3730 hr_utility.set_location(l_proc, 22);
3731 l_retcode := update_org_name_date(
3732 p_mode
3733 ,p_sync_org_name
3734 ,p_sync_org_dates
3735 );
3736 END IF;
3737
3738 -- Fix For Bug # 6929228 Ends ---
3739
3740 --
3741 IF l_retcode = 0 THEN
3742 hr_utility.set_location(l_proc, 24);
3743 l_retcode := delete_sync_orgs_from_temp(
3744 p_mode
3745 ,p_business_group_id
3746 ,p_coa_id
3747 ,l_rowcount -- Added for bug4346785.
3748 );
3749 END IF;
3750 --
3751 -- Fix for bug 3837139 ends here.
3752 --
3753 -- For each record in temp table, generate ORG, Co-Class, CC-Class as required
3754 IF l_retcode = 0 THEN
3755 hr_utility.set_location(l_proc, 30);
3756
3757 OPEN c_list FOR 'SELECT coa_id, co, co_vs_id, cc, cc_vs_id FROM '||g_temp_table_name;
3758 LOOP
3759 FETCH c_list INTO l_coa_id, l_co, l_co_vs_id, l_cc, l_cc_vs_id;
3760 EXIT WHEN c_list%NOTFOUND;
3761 l_processed_recs := TRUE;
3762
3763 -- At this point, the system does not have an organization with the same
3764 -- company and cost center classifications. However the ORG can exist but
3765 -- without the requisite classifications or the ORG does not exist at all.
3766 l_retcode := gen_org_co_cc_class( p_mode
3767 , p_business_group_id
3768 , l_coa_id
3769 , l_co
3770 , l_co_vs_id
3771 , l_cc
3772 , l_cc_vs_id
3773 , l_org_id
3774 );
3775 IF l_retcode = 0 THEN
3776 -- Close transaction for ORG.
3777 COMMIT;
3778 ELSE -- retcode is not 0
3779 l_errored_recs := TRUE;
3780 -- Skip processing this company cost center combination and proceed with next.
3781 writelog('Error processing P_BG_ID: '||p_business_group_id||
3782 ', P_COA_ID: '||l_coa_id||
3783 ', P_CO: '||l_co||
3784 ', P_CO_VS_ID: '||l_co_vs_id||
3785 ', P_CC: '||l_cc||
3786 ', P_CC_VS_ID: '||l_cc_vs_id,'Y');
3787 -- Write warning to LOG
3788 hr_utility.set_message(800,'PER_50170_ERR_PROC_CO_CC');
3789 fnd_message.set_token('COMP_CODE',l_co);
3790 fnd_message.set_token('COST_CENTER_CODE',l_cc);
3791 writelog(fnd_message.get(),'N');
3792 -- Close transaction for ORG.
3793 ROLLBACK;
3794 -- Reset return code to continue processing next ORG.
3795 l_retcode := 0;
3796 END IF;
3797 END LOOP; -- records in temp table
3798 CLOSE c_list;
3799
3800 IF NOT l_processed_recs THEN
3801 RAISE e_no_records_to_process;
3802 END IF;
3803
3804 IF l_errored_recs THEN
3805 -- Force exit with warning to prompt the user to check the process LOG.
3806 l_retcode := 1;
3807 END IF;
3808 END IF; -- retcode is 0
3809
3810 IF l_retcode = 0 THEN
3811 hr_utility.set_location(l_proc, 22);
3812 l_retcode := update_org_name_date(
3813 p_mode
3814 ,p_sync_org_name
3815 ,p_sync_org_dates
3816 );
3817 END IF;
3818
3819 hr_utility.set_location('Leaving: '|| l_proc, 40);
3820
3821 RETURN l_retcode;
3822
3823 EXCEPTION
3824
3825 WHEN e_no_records_to_process THEN
3826 hr_utility.set_location('Leaving: '|| l_proc, 50);
3827 hr_utility.set_message(800,'PER_50154_NO_CO_CC_COMBS');
3828 writelog(fnd_message.get(),'N');
3829 IF c_list%ISOPEN THEN
3830 CLOSE c_list;
3831 END IF;
3832 l_retcode := 1;
3833 RETURN l_retcode;
3834
3835 WHEN OTHERS THEN
3836 hr_utility.set_location('Leaving: '|| l_proc, 60);
3837 hr_utility.set_location(SQLERRM, 65);
3838 writelog(SQLERRM,'N');
3839 IF c_list%ISOPEN THEN
3840 CLOSE c_list;
3841 END IF;
3842 l_retcode := 2;
3843 RETURN l_retcode;
3844
3845 END create_maintain_mode;
3846
3847 --
3848 -- Initialize package globals.
3849 --
3850 -- Set length of ORG Name based on UTF8 trigger.
3851 -- Set debug level.
3852 -- Get values held in profile options.
3853 -- Get schema name for product PER.
3854 -- Coin temp table name.
3855 -- Start LOG and OUT files by invoking open_logs().
3856 -- Create temp table.
3857 --
3858 FUNCTION initialize( p_mode IN VARCHAR2
3859 , p_business_group_id IN NUMBER
3860 , p_coa_id IN NUMBER
3861 , p_ccid IN NUMBER
3862 ) RETURN NUMBER IS
3863
3864 -- Local Variables
3865 l_proc VARCHAR2(50) := g_package||'.initialize';
3866 l_dummy NUMBER := 0;
3867 l_retcode NUMBER := 0;
3868
3869 -- Cursor to test the existence of the UTF8 trigger
3870 CURSOR c_utf8_trigger IS
3871 SELECT 1
3872 FROM user_triggers
3873 WHERE trigger_name = 'HR_ALL_ORGANIZATION_UNITS_UTF8';
3874
3875 -- Cursor to get the debug level for the instance.
3876 CURSOR c_debug_level IS
3877 SELECT parameter_value
3878 FROM pay_action_parameters
3879 WHERE parameter_name = 'HR_GL_SYNC_DEBUG';
3880
3881 -- Local Exceptions
3882 e_class_profile EXCEPTION;
3883 e_org_name_format EXCEPTION;
3884 e_export_dir EXCEPTION;
3885
3886 BEGIN
3887
3888 hr_utility.set_location('Entering: '|| l_proc, 10);
3889
3890 -- Set the global holding the max length of ORG Name based on the
3891 -- existence of the UTF8 trigger. If trigger exists, set the max
3892 -- length to 60, else 240.
3893 OPEN c_utf8_trigger;
3894 FETCH c_utf8_trigger INTO l_dummy;
3895 IF c_utf8_trigger%FOUND THEN
3896 hr_utility.set_location(l_proc, 20);
3897 g_org_name_max_length := 60;
3898 ELSE
3899 hr_utility.set_location(l_proc, 30);
3900 g_org_name_max_length := 240;
3901 END IF;
3902 CLOSE c_utf8_trigger;
3903
3904 hr_utility.set_location(l_proc, 40);
3905
3906 -- Set the debug level for the instance
3907 OPEN c_debug_level;
3908 FETCH c_debug_level INTO g_debug_level;
3909 IF c_debug_level%NOTFOUND THEN
3910 hr_utility.set_location(l_proc, 50);
3911 g_debug_level := 'NORMAL';
3912 END IF;
3913 CLOSE c_debug_level;
3914
3915 hr_utility.set_location(l_proc, 60);
3916
3917 -- Get profile option values
3918 g_class_profile := fnd_profile.value('HR_GENERATE_GL_ORGS');
3919 g_org_name_format := fnd_profile.value('HR_GL_ORG_NAME_FORMAT');
3920 g_export_dir := fnd_profile.value('PER_DATA_EXCHANGE_DIR');
3921
3922 IF p_mode IN ('CREATE_MAINTAIN','SINGLE_ORG') THEN
3923 hr_utility.set_location(l_proc, 70);
3924 IF g_class_profile IS NULL OR g_class_profile = 'N' THEN
3925 hr_utility.set_location(l_proc, 80);
3926 RAISE e_class_profile;
3927 END IF;
3928 END IF; -- p_mode check
3929
3930 IF p_mode IN ('CREATE_MAINTAIN','SINGLE_ORG','EXPORT') THEN
3931 hr_utility.set_location(l_proc, 90);
3932 IF g_org_name_format IS NULL THEN
3933 hr_utility.set_location(l_proc, 100);
3934 RAISE e_org_name_format;
3935 END IF;
3936 END IF; -- p_mode check
3937
3938 IF p_mode = 'EXPORT' THEN
3939 hr_utility.set_location(l_proc, 110);
3940 IF g_export_dir IS NULL THEN
3941 hr_utility.set_location(l_proc, 120);
3942 RAISE e_export_dir;
3943 END IF;
3944 END IF; -- p_mode check
3945
3946 hr_utility.set_location(l_proc, 130);
3947
3948 -- Get the PER schema name
3949 g_per_schema := get_schema(g_appl_short_name);
3950
3951 hr_utility.set_location(l_proc, 140);
3952
3953 -- Initialize the temporary table name
3954 g_temp_table_name := g_per_schema || '.HR_CO_CC_TEMP';
3955 IF p_mode = 'SINGLE_ORG' THEN
3956 hr_utility.set_location(l_proc, 150);
3957 g_temp_table_name := g_temp_table_name || '_' || p_ccid;
3958 ELSIF p_mode IN ('CREATE_MAINTAIN','SYNCHRONIZE') THEN
3959 hr_utility.set_location(l_proc, 160);
3960 g_temp_table_name := g_temp_table_name || '_' || p_business_group_id;
3961 ELSIF p_mode = 'EXPORT' THEN
3962 hr_utility.set_location(l_proc, 170);
3963 g_temp_table_name := g_temp_table_name || '_' || p_coa_id;
3964 END IF;
3965
3966 -- Open LOG and OUT files
3967 IF l_retcode = 0 THEN
3968 hr_utility.set_location(l_proc, 180);
3969 l_retcode := open_logs( p_mode
3970 , p_business_group_id
3971 , p_ccid
3972 );
3973 END IF;
3974
3975 -- Create temporary table
3976 IF l_retcode = 0 THEN
3977 hr_utility.set_location(l_proc, 190);
3978 IF p_mode IN ('CREATE_MAINTAIN','SYNCHRONIZE','EXPORT') THEN
3979 hr_utility.set_location(l_proc, 200);
3980 create_temp_table(p_mode);
3981 END IF;
3982 END IF;
3983
3984 hr_utility.set_location('Leaving: '|| l_proc, 210);
3985
3986 RETURN l_retcode;
3987
3988 EXCEPTION
3989
3990 WHEN e_class_profile THEN
3991 hr_utility.set_location('Leaving: '|| l_proc, 220);
3992 hr_utility.set_message(800,'PER_50155_ORG_CLASS_PROF_WARN');
3993 writelog(fnd_message.get(),'N');
3994 l_retcode := 2;
3995 RETURN l_retcode;
3996
3997 WHEN e_org_name_format THEN
3998 hr_utility.set_location('Leaving: '|| l_proc, 230);
3999 hr_utility.set_message(800,'HR_289489_NO_NAME_FORMAT');
4000 writelog(fnd_message.get(),'N');
4001 l_retcode := 2;
4002 RETURN l_retcode;
4003
4004 WHEN e_export_dir THEN
4005 hr_utility.set_location('Leaving: '|| l_proc, 240);
4006 hr_utility.set_message(800,'HR_289427_NO_EXC_DIR');
4007 writelog(fnd_message.get(),'N');
4008 l_retcode := 2;
4009 RETURN l_retcode;
4010
4011 WHEN OTHERS THEN
4012 hr_utility.set_location('Leaving: '|| l_proc, 250);
4013 hr_utility.set_location(SQLERRM, 255);
4014 writelog(SQLERRM,'N');
4015 IF c_utf8_trigger%ISOPEN THEN
4016 CLOSE c_utf8_trigger;
4017 END IF;
4018 IF c_debug_level%ISOPEN THEN
4019 CLOSE c_debug_level;
4020 END IF;
4021 l_retcode := 2;
4022 RETURN l_retcode;
4023
4024 END initialize;
4025
4026 --
4027 -- Validate passed parameters.
4028 --
4029 -- Check that P_MODE is valid.
4030 -- Check that P_COA_ID is supplied.
4031 -- If mode is SINGLE_ORG
4032 -- Check that P_CCID is supplied.
4033 -- If mode is not SINGLE_ORG
4034 -- Check that P_BUSINESS_GROUP_ID is supplied.
4035 --
4036 FUNCTION validate_params( p_mode IN VARCHAR2
4037 , p_business_group_id IN NUMBER
4038 , p_coa_id IN NUMBER
4039 , p_co IN VARCHAR2
4040 , p_ccid IN NUMBER
4041 , p_source IN VARCHAR2
4042 ) RETURN NUMBER IS
4043
4044 -- Local variables
4045 l_proc VARCHAR2(50) := g_package||'.validate_params';
4046 l_retcode NUMBER := 0;
4047
4048 -- Local exceptions
4049 e_invalid_mode EXCEPTION;
4050 e_no_coaid EXCEPTION;
4051 e_no_ccid EXCEPTION;
4052 e_no_bgid EXCEPTION;
4053 e_invalid_source EXCEPTION;
4054
4055 BEGIN
4056
4057 hr_utility.set_location('Entering: '|| l_proc, 10);
4058
4059 IF p_mode IS NULL OR
4060 p_mode NOT IN ('CREATE_MAINTAIN','SYNCHRONIZE','EXPORT','SINGLE_ORG') THEN
4061 hr_utility.set_location(l_proc, 20);
4062 RAISE e_invalid_mode;
4063 END IF;
4064
4065 IF p_mode IN ('CREATE_MAINTAIN','SYNCHRONIZE','EXPORT') THEN
4066 hr_utility.set_location(l_proc, 30);
4067 IF p_coa_id IS NULL THEN
4068 hr_utility.set_location(l_proc, 40);
4069 RAISE e_no_coaid;
4070 END IF;
4071 END IF;
4072
4073 IF p_mode = 'SINGLE_ORG' THEN
4074 hr_utility.set_location(l_proc, 50);
4075 IF p_ccid IS NULL THEN
4076 hr_utility.set_location(l_proc, 60);
4077 RAISE e_no_ccid;
4078 END IF;
4079 ELSE -- mode is not single org
4080 hr_utility.set_location(l_proc, 70);
4081 IF p_business_group_id IS NULL THEN
4082 hr_utility.set_location(l_proc, 80);
4083 RAISE e_no_bgid;
4084 END IF;
4085 END IF;
4086
4087 IF p_mode IN ('CREATE_MAINTAIN','EXPORT') THEN
4088 hr_utility.set_location(l_proc, 90);
4089 IF p_source IS NULL OR p_source NOT IN ('GLCC','CCVS') THEN
4090 hr_utility.set_location(l_proc, 100);
4091 RAISE e_invalid_source;
4092 END IF;
4093 END IF;
4094
4095 hr_utility.set_location('Leaving: '|| l_proc, 110);
4096
4097 RETURN l_retcode;
4098
4099 EXCEPTION
4100
4101 WHEN e_invalid_mode THEN
4102 hr_utility.set_location('Leaving: '|| l_proc, 120);
4103 hr_utility.set_message(800,'PER_50150_INVAL_SYNC_ORG_MODE');
4104 fnd_message.set_token('MODE',p_mode);
4105 writelog(fnd_message.get(),'N');
4106 l_retcode := 2;
4107 RETURN l_retcode;
4108
4109 WHEN e_no_coaid THEN
4110 hr_utility.set_location('Leaving: '|| l_proc, 130);
4111 hr_utility.set_message(800,'PER_50151_NO_COAID');
4112 writelog(fnd_message.get(),'N');
4113 l_retcode := 2;
4114 RETURN l_retcode;
4115
4116 WHEN e_no_ccid THEN
4117 hr_utility.set_location('Leaving: '|| l_proc, 140);
4118 hr_utility.set_message(800,'PER_50152_NO_CCID');
4119 writelog(fnd_message.get(),'N');
4120 l_retcode := 2;
4121 RETURN l_retcode;
4122
4123 WHEN e_no_bgid THEN
4124 hr_utility.set_location('Leaving: '|| l_proc, 150);
4125 hr_utility.set_message(800,'PER_50153_NO_BGID');
4126 writelog(fnd_message.get(),'N');
4127 l_retcode := 2;
4128 RETURN l_retcode;
4129
4130 WHEN e_invalid_source THEN
4131 hr_utility.set_location('Leaving: '|| l_proc, 160);
4132 hr_utility.set_message(800,'PER_50156_INVAL_CO_CC_SOURCE');
4133 fnd_message.set_token('SOURCE',p_source);
4134 writelog(fnd_message.get(),'N');
4135 l_retcode := 2;
4136 RETURN l_retcode;
4137
4138 WHEN OTHERS THEN
4139 hr_utility.set_location('Leaving: '|| l_proc, 170);
4140 hr_utility.set_location(SQLERRM, 175);
4141 writelog(SQLERRM,'N');
4142 l_retcode := 2;
4143 RETURN l_retcode;
4144
4145 END validate_params;
4146
4147 --
4148 -- Main Entry Point Procedure for Concurrent Programs.
4149 --
4150 -- Validate parameter values passed in.
4151 -- Set global variables, open log and out files, create temp table.
4152 -- Log details of parameters passed and global values.
4153 -- If mode is CREATE_MAINTAIN
4154 -- Invoke create_maintain_mode().
4155 -- Else if mode is SYNCHRONIZE
4156 -- Invoke synchronize_mode().
4157 -- If mode is EXPORT
4158 -- Invoke report_mode().
4159 -- If mode is SINGLE_ORG
4160 -- Invoke single_org_mode().
4161 -- Log exit state.
4162 --
4163 PROCEDURE sync_orgs( errbuf IN OUT NOCOPY VARCHAR2
4164 , retcode IN OUT NOCOPY NUMBER
4165 , p_mode IN VARCHAR2
4166 , p_business_group_id IN NUMBER
4167 , p_coa_id IN NUMBER
4168 , p_co IN VARCHAR2
4169 , p_ccid IN NUMBER
4170 , p_source IN VARCHAR2
4171 , p_sync_org_name IN VARCHAR2 DEFAULT 'N'
4172 , p_sync_org_dates IN VARCHAR2 DEFAULT 'N'
4173 ) IS
4174
4175 -- Local variables
4176 l_proc VARCHAR2(50) := g_package||'.sync_orgs';
4177 l_bg_name hr_all_organization_units.name%TYPE;
4178
4179 -- Cursor to fetch business group name
4180 CURSOR c_bg_name IS
4181 SELECT name
4182 FROM hr_all_organization_units
4183 WHERE organization_id = p_business_group_id;
4184
4185 BEGIN
4186
4187 hr_utility.set_location('Entering: '|| l_proc, 10);
4188
4189 -- Validate Parameters
4190 IF retcode = 0 THEN
4191 hr_utility.set_location(l_proc, 20);
4192 retcode := validate_params( p_mode
4193 , p_business_group_id
4194 , p_coa_id
4195 , p_co
4196 , p_ccid
4197 , p_source
4198 );
4199 END IF; -- retcode is 0
4200
4201 -- Initialize variables, files and temp tables
4202 IF retcode = 0 THEN
4203 hr_utility.set_location(l_proc, 30);
4204 retcode := initialize( p_mode
4205 , p_business_group_id
4206 , p_coa_id
4207 , p_ccid);
4208 END IF; -- retcode is 0
4209
4210 -- Fetch the business group name
4211 IF retcode = 0 THEN
4212 hr_utility.set_location(l_proc, 40);
4213 OPEN c_bg_name;
4214 FETCH c_bg_name INTO l_bg_name;
4215 IF c_bg_name%NOTFOUND THEN
4216 hr_utility.set_location(l_proc, 50);
4217 l_bg_name := 'UNKNOWN';
4218 END IF;
4219 CLOSE c_bg_name;
4220 END IF; -- retcode is 0
4221
4222 -- Log instance state
4223 IF retcode = 0 THEN
4224 hr_utility.set_location(l_proc, 60);
4225 writelog('==========================================','Y');
4226 writelog('Starting Synchronize Organizations Program','Y');
4227 writelog('==========================================','Y');
4228 hr_utility.set_message(800,'PER_50157_BUS_GRP_NAME');
4229 fnd_message.set_token('BUS_GRP_NAME',l_bg_name);
4230 fnd_message.set_token('BUS_GRP_ID',p_business_group_id);
4231 writelog(fnd_message.get(),'N');
4232 writelog('----------','Y');
4233 writelog('Parameters','Y');
4234 writelog('----------','Y');
4235 writelog('P_MODE: '||p_mode,'Y');
4236 writelog('P_COA_ID: '||p_coa_id,'Y');
4237 writelog('P_CO: '||p_co,'Y');
4238 writelog('P_CCID: '||p_ccid,'Y');
4239 writelog('P_SOURCE: '||p_source,'Y');
4240 writelog('P_SYNC_ORG_NAME: '||p_sync_org_name,'Y');
4241 writelog('P_SYNC_ORG_DATES: '||p_sync_org_dates,'Y');
4242 writelog('-------','Y');
4243 writelog('Globals','Y');
4244 writelog('-------','Y');
4245 writelog('G_PACKAGE: '||g_package,'Y');
4246 writelog('G_ORG_NAME_MAX_LENGTH: '||g_org_name_max_length,'Y');
4247 writelog('G_DEBUG_LEVEL: '||g_debug_level,'Y');
4248 writelog('G_CLASS_PROFILE: '||g_class_profile,'Y');
4249 writelog('G_ORG_NAME_FORMAT: '||g_org_name_format,'Y');
4250 writelog('G_PER_SCHEMA: '||g_per_schema,'Y');
4251 writelog('G_TEMP_TABLE_NAME: '||g_temp_table_name,'Y');
4252 writelog('G_EXPORT_DIR: '||g_export_dir,'Y');
4253 writelog('--','Y');
4254 END IF; -- retcode is 0
4255
4256 -- Process in appropriate mode
4257 IF retcode = 0 THEN
4258 IF p_mode = 'CREATE_MAINTAIN' THEN
4259 hr_utility.set_location(l_proc, 70);
4260 retcode := create_maintain_mode( p_mode
4261 , p_business_group_id
4262 , p_coa_id
4263 , p_co
4264 , p_source
4265 , p_sync_org_name
4266 , p_sync_org_dates
4267 );
4268
4269 ELSIF p_mode = 'SYNCHRONIZE' THEN
4270 hr_utility.set_location(l_proc, 80);
4271 retcode := synchronize_mode( p_business_group_id
4272 , p_coa_id
4273 , p_co
4274 );
4275
4276 ELSIF p_mode = 'EXPORT' THEN
4277 hr_utility.set_location(l_proc, 90);
4278 retcode := report_mode( p_mode
4279 , p_business_group_id
4280 , p_coa_id
4281 , p_source
4282 , l_bg_name
4283 );
4284
4285 ELSIF p_mode = 'SINGLE_ORG' THEN
4286 hr_utility.set_location(l_proc, 100);
4287 retcode := single_org_mode( p_mode
4288 , p_ccid
4289 );
4290
4291 END IF; -- p_mode test
4292
4293 IF p_mode IN ('CREATE_MAINTAIN','SYNCHRONIZE','EXPORT') THEN
4294 hr_utility.set_location(l_proc, 110);
4295 drop_temp_table;
4296 END IF; -- p_mode test
4297
4298 END IF; -- retcode = 0
4299
4300 hr_utility.set_location(l_proc, 120);
4301
4302 -- Exit Messages
4303 IF retcode = 0 THEN
4304 hr_utility.set_location(l_proc, 130);
4305 writelog('==============================','Y');
4306 writelog('Program Completed Successfully','Y');
4307 writelog('==============================','Y');
4308 ELSIF retcode = 1 THEN
4309 hr_utility.set_location(l_proc, 140);
4310 writelog('===============================','Y');
4311 writelog('Program Completed with Warnings','Y');
4312 writelog('===============================','Y');
4313 ELSIF retcode = 2 THEN
4314 hr_utility.set_location(l_proc, 150);
4315 writelog('===========================','Y');
4316 writelog('Program Terminated in Error','Y');
4317 writelog('===========================','Y');
4318 ELSE
4319 hr_utility.set_location(l_proc, 160);
4320 writelog('========================================','Y');
4321 writelog('Program Terminated with unknown code ('||TO_CHAR(retcode)||')','Y');
4322 writelog('========================================','Y');
4323 END IF;
4324
4325 hr_utility.set_location('Leaving: '|| l_proc, 170);
4326
4327 EXCEPTION
4328
4329 WHEN OTHERS THEN
4330 hr_utility.set_location('Leaving: '|| l_proc, 180);
4331 errbuf := SQLERRM;
4332 hr_utility.set_location(errbuf, 185);
4333 writelog(errbuf,'N');
4334 retcode := 2;
4335
4336 END sync_orgs;
4337
4338 --
4339 -- Main Entry Point Procedure for GL Code Hook.
4340 --
4341 -- Validate if single ORG processing is enabled and if it is,
4342 -- spawn an instance of the create maintain concurrent process
4343 -- in single ORG mode.
4344 --
4345 PROCEDURE sync_single_org( p_ccid IN NUMBER
4346 ) IS
4347
4348 -- Local variables
4349 l_proc VARCHAR2(50) := g_package||'.sync_single_org';
4350 l_request_id NUMBER := -1;
4351 l_class_profile VARCHAR2(10);
4352 l_single_org_profile VARCHAR2(10);
4353
4354 BEGIN
4355
4356 hr_utility.set_location('Entering: '|| l_proc, 10);
4357
4358 l_class_profile := fnd_profile.value('HR_GENERATE_GL_ORGS');
4359 l_single_org_profile := fnd_profile.value('HR_SYNC_SINGLE_GL_ORG');
4360
4361 -- Check if single Org mode is enabled
4362 IF l_class_profile IS NULL OR
4363 l_class_profile NOT IN ('CC','CCHR') OR
4364 l_single_org_profile IS NULL OR
4365 l_single_org_profile NOT IN ('Y') THEN
4366 hr_utility.set_location(l_proc, 20);
4367 RETURN;
4368 END IF;
4369
4370 hr_utility.set_location(l_proc, 30);
4371
4372 -- Launch the Sync Orgs concurrent program in 'SINGLE_ORG' mode.
4373 l_request_id := fnd_request.submit_request
4374 (APPLICATION => g_appl_short_name,
4375 PROGRAM => 'HR_GL_CREATE_MAINTAIN_ORGS',
4376 DESCRIPTION => NULL,
4377 START_TIME => NULL,
4378 SUB_REQUEST => FALSE,
4379 ARGUMENT1 => 'SINGLE_ORG', -- Mode
4380 ARGUMENT2 => NULL, -- Business Group Id
4381 ARGUMENT3 => NULL, -- Chart Of Accounts Id
4382 ARGUMENT4 => NULL, -- Company Code
4383 ARGUMENT5 => p_ccid, -- Code Combination Id
4384 ARGUMENT6 => NULL, -- Source
4385 ARGUMENT7 => CHR(0),
4386 ARGUMENT8 => NULL, ARGUMENT9 => NULL, ARGUMENT10 => NULL,
4387 ARGUMENT11 => NULL, ARGUMENT12 => NULL, ARGUMENT13 => NULL,
4388 ARGUMENT14 => NULL, ARGUMENT15 => NULL, ARGUMENT16 => NULL,
4389 ARGUMENT17 => NULL, ARGUMENT18 => NULL, ARGUMENT19 => NULL,
4390 ARGUMENT20 => NULL, ARGUMENT21 => NULL, ARGUMENT22 => NULL,
4391 ARGUMENT23 => NULL, ARGUMENT24 => NULL, ARGUMENT25 => NULL,
4392 ARGUMENT26 => NULL, ARGUMENT27 => NULL, ARGUMENT28 => NULL,
4393 ARGUMENT29 => NULL, ARGUMENT30 => NULL, ARGUMENT31 => NULL,
4394 ARGUMENT32 => NULL, ARGUMENT33 => NULL, ARGUMENT34 => NULL,
4395 ARGUMENT35 => NULL, ARGUMENT36 => NULL, ARGUMENT37 => NULL,
4396 ARGUMENT38 => NULL, ARGUMENT39 => NULL, ARGUMENT40 => NULL,
4397 ARGUMENT41 => NULL, ARGUMENT42 => NULL, ARGUMENT43 => NULL,
4398 ARGUMENT44 => NULL, ARGUMENT45 => NULL, ARGUMENT46 => NULL,
4399 ARGUMENT47 => NULL, ARGUMENT48 => NULL, ARGUMENT49 => NULL,
4400 ARGUMENT50 => NULL, ARGUMENT51 => NULL, ARGUMENT52 => NULL,
4401 ARGUMENT53 => NULL, ARGUMENT54 => NULL, ARGUMENT55 => NULL,
4402 ARGUMENT56 => NULL, ARGUMENT57 => NULL, ARGUMENT58 => NULL,
4403 ARGUMENT59 => NULL, ARGUMENT60 => NULL, ARGUMENT61 => NULL,
4404 ARGUMENT62 => NULL, ARGUMENT63 => NULL, ARGUMENT64 => NULL,
4405 ARGUMENT65 => NULL, ARGUMENT66 => NULL, ARGUMENT67 => NULL,
4406 ARGUMENT68 => NULL, ARGUMENT69 => NULL, ARGUMENT70 => NULL,
4407 ARGUMENT71 => NULL, ARGUMENT72 => NULL, ARGUMENT73 => NULL,
4408 ARGUMENT74 => NULL, ARGUMENT75 => NULL, ARGUMENT76 => NULL,
4409 ARGUMENT77 => NULL, ARGUMENT78 => NULL, ARGUMENT79 => NULL,
4410 ARGUMENT80 => NULL, ARGUMENT81 => NULL, ARGUMENT82 => NULL,
4411 ARGUMENT83 => NULL, ARGUMENT84 => NULL, ARGUMENT85 => NULL,
4412 ARGUMENT86 => NULL, ARGUMENT87 => NULL, ARGUMENT88 => NULL,
4413 ARGUMENT89 => NULL, ARGUMENT90 => NULL, ARGUMENT91 => NULL,
4414 ARGUMENT92 => NULL, ARGUMENT93 => NULL, ARGUMENT94 => NULL,
4415 ARGUMENT95 => NULL, ARGUMENT96 => NULL, ARGUMENT97 => NULL,
4416 ARGUMENT98 => NULL, ARGUMENT99 => NULL, ARGUMENT100 => NULL);
4417
4418 hr_utility.set_location(l_proc, 40);
4419
4420 IF l_request_id > 0 THEN
4421 hr_utility.set_location(l_proc, 50);
4422 -- Commit the launch
4423 COMMIT;
4424 END IF;
4425
4426 hr_utility.set_location('Leaving: '|| l_proc, 60);
4427
4428 EXCEPTION
4429
4430 WHEN OTHERS THEN
4431 hr_utility.set_location('Leaving: '|| l_proc, 70);
4432 hr_utility.set_location(SQLERRM, 75);
4433 writelog(SQLERRM,'N');
4434
4435 END sync_single_org;
4436
4437 END hr_gl_sync_orgs;
[Home] [Help]