[Home] [Help]
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);
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;
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:
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);
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
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');
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;
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:
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:
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)'||
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;
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)' ||
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:
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
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:
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;
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);
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: );
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: );
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
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;
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:
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';
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');
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:
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: );
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: );
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
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);
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
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;
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:
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;
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;
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);
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:
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:
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
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;
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;
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
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;
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;
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
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;
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;
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
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;
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;
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
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;
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:
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
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;
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;
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'||
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:
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
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;
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:
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
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);
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;
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:
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: );
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);
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
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
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
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
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;
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;
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;
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);
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
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
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:
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:
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
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;
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;
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
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;
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;
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
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;
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;
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
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;
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:
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
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;
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
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;
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:
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
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;
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
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;
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:
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
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;
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);
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
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
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:
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);
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
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
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');
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;
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;
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);
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
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:
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
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');
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;
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;
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);
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
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);
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||')');
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:
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
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');
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;
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;
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
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);
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
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');
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;
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;
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
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;
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:
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
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;
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
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;
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
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;
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;
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);
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
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
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'))
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'
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');
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');
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
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;
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:
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||
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:
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
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);
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
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);
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: );
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:
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
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
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:
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
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);
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
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:
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
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
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
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.
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
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
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: );
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;
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;
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);
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
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;
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;
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.
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;
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,
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;
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
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);
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
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;
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
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
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');
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
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||
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'||
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'||
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:
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
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;
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: --
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: --
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
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
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: --
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: --
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);
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
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
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
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);
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
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;
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
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;
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;
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);
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
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
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');
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
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
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'||
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'||
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:
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
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
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;
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
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||
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
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:
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
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;
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:
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;
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:
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
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
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'||
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:
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'||
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'||
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:
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
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;
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:
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;
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:
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');
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');
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
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;
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:
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');
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');
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' ||
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
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;
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;
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
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:
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;
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
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;
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:
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);
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
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');
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
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:
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
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;
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);
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:
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'||
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);
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
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
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);
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);
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.
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);
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
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;
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;
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;
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;
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.
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
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:
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;
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
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;
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:
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);
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
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'||
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'||
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:
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;
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);
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
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:
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
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;
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:
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;
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:
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.
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
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
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
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
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;
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.
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
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
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;
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;
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;
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.
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;
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:
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;
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:
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');
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;
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:
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;
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:
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;
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:
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:
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
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;
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;
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
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;
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:
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
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;
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:
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;
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:
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;
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:
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;
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;
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);
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
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;
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:
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;
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);
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;
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:
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;
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:
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
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;
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;
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;
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:
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;
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:
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;
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:
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;
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;
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;
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:
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);
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
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);
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
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');
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');
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
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: );
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:
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
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);
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
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
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
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;
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
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;
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;
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:
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);
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,
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
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:
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
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;
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: