[Home] [Help]
71: fnd_file.put_line(FND_FILE.log, p_text);
72: end if;
73: elsif p_error_or_debug = 'E' then
74: fnd_file.put_line(FND_FILE.log, p_text);
75: hr_utility.raise_error;
76: else
77: fnd_file.put_line(FND_FILE.log, p_text);
78: end if;
79:
76: else
77: fnd_file.put_line(FND_FILE.log, p_text);
78: end if;
79:
80: hr_utility.set_location(p_text,10);
81: exception
82: when others then
83: if SQLCODE = -20100 then
84: hr_utility.set_location(substr(p_text,1,100),20);
80: hr_utility.set_location(p_text,10);
81: exception
82: when others then
83: if SQLCODE = -20100 then
84: hr_utility.set_location(substr(p_text,1,100),20);
85: else
86: hr_utility.set_location('writelog encountered unexpected exception',30);
87: raise;
88: end if;
82: when others then
83: if SQLCODE = -20100 then
84: hr_utility.set_location(substr(p_text,1,100),20);
85: else
86: hr_utility.set_location('writelog encountered unexpected exception',30);
87: raise;
88: end if;
89: END;
90:
160: p_force IN BOOLEAN default TRUE) IS
161: l_proc varchar2(50) := g_package||'.droptable';
162: l_debug_level varchar2(80);
163: BEGIN
164: hr_utility.set_location('Entering : '||l_proc,10);
165: l_debug_level := getDebugState();
166:
167: if p_force
168: OR
167: if p_force
168: OR
169: (not p_force and
170: l_debug_level <> 'DEBUG') then
171: hr_utility.set_location(l_proc,15);
172: execute immediate 'drop table '|| p_tablename;
173: end if;
174: hr_utility.set_location('Leaving : '||l_proc,20);
175:
170: l_debug_level <> 'DEBUG') then
171: hr_utility.set_location(l_proc,15);
172: execute immediate 'drop table '|| p_tablename;
173: end if;
174: hr_utility.set_location('Leaving : '||l_proc,20);
175:
176: Exception when others then
177: writelog(sqlerrm,'D');
178: IF (sqlcode = -942) THEN
179: /*
180: ** table does not exist at time of dropping which
181: ** we can ignore.
182: */
183: hr_utility.set_location('Leaving : '||l_proc,30);
184: null;
185: ELSE
186: hr_utility.set_location('Leaving : '||l_proc,40);
187: raise;
182: */
183: hr_utility.set_location('Leaving : '||l_proc,30);
184: null;
185: ELSE
186: hr_utility.set_location('Leaving : '||l_proc,40);
187: raise;
188: END IF;
189:
190: END;
199: l_proc varchar2(150) := g_package||'.reportMissingFlex';
200: l_message varchar2(150);
201: begin
202:
203: hr_utility.set_location('Entering : '||l_proc,10);
204:
205: hr_utility.set_message(800,'PER_289604_WRN_CHRT_ACC_ID');
206: fnd_message.set_token('ID',p_chart_of_accounts_id);
207: l_message := fnd_message.get();
201: begin
202:
203: hr_utility.set_location('Entering : '||l_proc,10);
204:
205: hr_utility.set_message(800,'PER_289604_WRN_CHRT_ACC_ID');
206: fnd_message.set_token('ID',p_chart_of_accounts_id);
207: l_message := fnd_message.get();
208:
209: writelog(l_message,'W');
219: if p_cc_vs = - 1 then
220: writelog(fnd_message.get_string(800,'PER_289608_WRN_CC_VS'),'W');
221: end if;
222: l_retcode := 1;
223: hr_utility.set_location('Leaving : '||l_proc,20);
224: return l_retcode;
225:
226: end;
227:
230: l_dummy2 varchar2(2000);
231: l_schema varchar2(400);
232: l_proc varchar2(50) := g_package||'.getProductSchema';
233: BEGIN
234: hr_utility.set_location('Entering : '||l_proc,10);
235: if FND_INSTALLATION.GET_APP_INFO(p_product
236: ,l_dummy1
237: ,l_dummy2
238: ,l_schema) = false then
235: if FND_INSTALLATION.GET_APP_INFO(p_product
236: ,l_dummy1
237: ,l_dummy2
238: ,l_schema) = false then
239: hr_utility.set_location('Leaving : '||l_proc,15);
240: return null;
241: end if;
242: hr_utility.set_location('Leaving : '||l_proc,20);
243: return l_schema;
238: ,l_schema) = false then
239: hr_utility.set_location('Leaving : '||l_proc,15);
240: return null;
241: end if;
242: hr_utility.set_location('Leaving : '||l_proc,20);
243: return l_schema;
244: END;
245:
246: Procedure writeHeaderFileHeader(p_file in utl_file.file_type
249: l_bg_name per_business_groups.name%type := null;
250: l_proc varchar2(50) := g_package||'.writeHeaderFileHeader';
251: l_tab varchar2(5) := fnd_global.tab;
252: BEGIN
253: hr_utility.set_location('Entering : '||l_proc,10);
254: BEGIN
255: SELECT name INTO l_bg_name
256: from PER_BUSINESS_GROUPS
257: WHERE business_group_id = p_bgid;
272: When no_data_found then
273: writelog('Error in writeHeaderFile for business group id : '||p_bgId
274: ,'E');
275: END;
276: hr_utility.set_location('Leaving : '||l_proc,20);
277: END;
278:
279: Procedure writeDataFileHeader(p_file IN utl_file.file_type) IS
280: l_proc varchar2(50) := g_package||'.writeDataFileHeader';
279: Procedure writeDataFileHeader(p_file IN utl_file.file_type) IS
280: l_proc varchar2(50) := g_package||'.writeDataFileHeader';
281: l_tab varchar2(5) := fnd_global.tab;
282: BEGIN
283: hr_utility.set_location('Entering : '||l_proc,10);
284:
285: utl_file.put_line(P_FILE, 'Descriptor'||l_tab||'Start');
286: utl_file.put_line(P_FILE, 'API'||l_tab||'create_company_cost_center');
287: utl_file.put_line(P_FILE, 'Title'||l_tab||'create company cost center');
293: 'costcenter_id'||l_tab||'costcenter_name'||l_tab||
294: 'company_id'||l_tab||'company_name'||l_tab||
295: 'costcenter_valueset_name'||l_tab||'company_valueset_name'||
296: l_tab||'start_date'||l_tab||'language_code');
297: hr_utility.set_location('Leaving : '||l_proc,20);
298: END;
299:
300: procedure spoolToFile(p_hr_cc_reporting_temp in varchar2) is
301:
330: l_stmt VARCHAR2(3000) := null;
331: l_retcode NUMBER;
332: l_tab VARCHAR2(5) := fnd_global.tab;
333: BEGIN
334: hr_utility.set_location('Entering : '||l_proc,10);
335: l_dir := fnd_profile.value('PER_DATA_EXCHANGE_DIR');
336:
337: writelog('HR profile PER_DATA_EXCHANGE_DIR is set to : '||l_dir,'D');
338:
336:
337: writelog('HR profile PER_DATA_EXCHANGE_DIR is set to : '||l_dir,'D');
338:
339: IF (l_dir is null) THEN
340: hr_utility.set_message(801,'HR_289427_NO_EXC_DIR');
341: writelog(fnd_message.get_string('PER','HR_289427_NO_EXC_DIR'),'E');
342: END IF;
343: hr_utility.set_location(l_proc,20);
344:
339: IF (l_dir is null) THEN
340: hr_utility.set_message(801,'HR_289427_NO_EXC_DIR');
341: writelog(fnd_message.get_string('PER','HR_289427_NO_EXC_DIR'),'E');
342: END IF;
343: hr_utility.set_location(l_proc,20);
344:
345: open c_bg_list for 'select distinct rep.business_group_id,
346: per.name
347: from '||p_hr_cc_reporting_temp ||' rep,
347: from '||p_hr_cc_reporting_temp ||' rep,
348: per_business_groups per
349: where rep.business_group_id=per.business_group_id';
350: loop
351: hr_utility.set_location(l_proc,30);
352: fetch c_bg_list into l_bg_id, l_bg_name;
353: exit when c_bg_list%NOTFOUND or l_bg_id is null;
354:
355: l_header_file := 'GLCC_'||replace(substr(l_bg_name,1,30),' ','_')||
359: writelog('Header file name '||l_header_file,'D');
360: writelog('Data file name '||l_data_file,'D');
361:
362: begin
363: hr_utility.set_location(l_proc,50);
364: l_hdr_file := utl_file.fopen(l_dir,l_header_file,'w');
365: Exception
366: when others then
367: hr_utility.set_message(801,'HR_289426_INV_EXC_DIR');
363: hr_utility.set_location(l_proc,50);
364: l_hdr_file := utl_file.fopen(l_dir,l_header_file,'w');
365: Exception
366: when others then
367: hr_utility.set_message(801,'HR_289426_INV_EXC_DIR');
368: writelog(fnd_message.get_string('PER','HR_289426_INV_EXC_DIR'),'E');
369: end;
370:
371: hr_utility.set_location(l_proc,60);
367: hr_utility.set_message(801,'HR_289426_INV_EXC_DIR');
368: writelog(fnd_message.get_string('PER','HR_289426_INV_EXC_DIR'),'E');
369: end;
370:
371: hr_utility.set_location(l_proc,60);
372: writeHeaderFileHeader(l_hdr_file
373: ,l_bg_id
374: ,l_data_file);
375: utl_file.fclose(l_hdr_file);
372: writeHeaderFileHeader(l_hdr_file
373: ,l_bg_id
374: ,l_data_file);
375: utl_file.fclose(l_hdr_file);
376: hr_utility.set_location(l_proc,70);
377:
378: l_dat_file := utl_file.fopen(l_dir, l_data_file,'w');
379:
380: writeDataFileHeader(l_dat_file);
408: and reptemp.company = compname.flex_value
409: and reptemp.cost_center = ccname.flex_value ';
410:
411: open c_list for l_stmt;
412: hr_utility.set_location(l_proc,80);
413:
414: loop
415: fetch c_list
416: into l_company_vs,
441: 'US' ); -- Language_code
442:
443: l_data_line_counter := l_data_line_counter + 1;
444: end loop;
445: hr_utility.set_location(l_proc,90);
446: utl_file.put_line(l_dat_file, 'Data'||l_tab||'End');
447: utl_file.fclose(l_dat_file);
448: writelog('Spooled '||l_data_line_counter||' lines in the data file','D');
449: l_data_line_counter := 1;
448: writelog('Spooled '||l_data_line_counter||' lines in the data file','D');
449: l_data_line_counter := 1;
450: l_counter := l_counter +1;
451: close c_list;
452: hr_utility.set_location(l_proc,100);
453: END LOOP;
454: hr_utility.set_location(l_proc,110);
455: close c_bg_list;
456:
450: l_counter := l_counter +1;
451: close c_list;
452: hr_utility.set_location(l_proc,100);
453: END LOOP;
454: hr_utility.set_location(l_proc,110);
455: close c_bg_list;
456:
457: writelog('Finished Spooling to file.','D');
458: end;
465: return VARCHAR2 is
466: l_proc varchar2(50) := g_package||'.getSegmentForQualifier';
467:
468: begin
469: hr_utility.set_location('Entering : '||l_proc,10);
470: if fnd_flex_apis.get_segment_column(101
471: ,'GL#'
472: ,p_chart_of_accounts_id
473: ,p_qualifier
471: ,'GL#'
472: ,p_chart_of_accounts_id
473: ,p_qualifier
474: ,p_column_name) THEN
475: hr_utility.set_location(p_qualifier||' segment : '||p_column_name,20);
476: hr_utility.set_location('Leaving : '||l_proc,30);
477: return 0;
478: end if;
479: hr_utility.set_location('Leaving : '||l_proc,40);
472: ,p_chart_of_accounts_id
473: ,p_qualifier
474: ,p_column_name) THEN
475: hr_utility.set_location(p_qualifier||' segment : '||p_column_name,20);
476: hr_utility.set_location('Leaving : '||l_proc,30);
477: return 0;
478: end if;
479: hr_utility.set_location('Leaving : '||l_proc,40);
480: return -2;
475: hr_utility.set_location(p_qualifier||' segment : '||p_column_name,20);
476: hr_utility.set_location('Leaving : '||l_proc,30);
477: return 0;
478: end if;
479: hr_utility.set_location('Leaving : '||l_proc,40);
480: return -2;
481: END;
482:
483:
494: l_vs_name fnd_flex_value_sets.flex_value_set_name%TYPE;
495:
496: BEGIN
497: p_vs_id := -1;
498: hr_utility.set_location('Entering : '||l_proc,10);
499: hr_utility.set_location('coa ID '||p_chart_of_Accounts_id,10);
500: hr_utility.set_location('segment '||p_segment,10);
501:
502: open c_list for 'select vs.flex_value_set_id,
495:
496: BEGIN
497: p_vs_id := -1;
498: hr_utility.set_location('Entering : '||l_proc,10);
499: hr_utility.set_location('coa ID '||p_chart_of_Accounts_id,10);
500: hr_utility.set_location('segment '||p_segment,10);
501:
502: open c_list for 'select vs.flex_value_set_id,
503: vs.flex_value_set_name
496: BEGIN
497: p_vs_id := -1;
498: hr_utility.set_location('Entering : '||l_proc,10);
499: hr_utility.set_location('coa ID '||p_chart_of_Accounts_id,10);
500: hr_utility.set_location('segment '||p_segment,10);
501:
502: open c_list for 'select vs.flex_value_set_id,
503: vs.flex_value_set_name
504: from fnd_id_flex_segments_vl seg
511: and application_column_name = :2'
512: using p_chart_of_accounts_id, p_segment;
513: FETCH c_list into p_vs_id, l_vs_name;
514: close c_list;
515: hr_utility.set_location(p_segment||' valueset is '||
516: l_vs_name||'('||p_vs_id||')',20);
517: hr_utility.set_location('Leaving : '||l_proc,30);
518: return 0;
519: END;
513: FETCH c_list into p_vs_id, l_vs_name;
514: close c_list;
515: hr_utility.set_location(p_segment||' valueset is '||
516: l_vs_name||'('||p_vs_id||')',20);
517: hr_utility.set_location('Leaving : '||l_proc,30);
518: return 0;
519: END;
520:
521:
598: where organization_id = p_organization_id
599: and org_information_context = p_info_type;
600:
601: BEGIN
602: hr_utility.set_location('Entering : '||l_proc,10);
603:
604: writelog('p_bg_id = '||p_bg_id||' , p_company = '||
605: p_company||', p_cost_center = '||p_Cost_center||
606: ', p_company_vs = ' ||p_company_vs||', p_cc_vs = '||p_cc_vs,'D');
607:
608: l_org_profile := fnd_profile.value('HR_GENERATE_GL_OPTIONS');
609: writelog('HR_GENERATE_GL_OPTIONS : '||l_org_profile,'D');
610: if (l_org_profile = 'S') then
611: hr_utility.set_location(l_proc,20);
612: writelog(fnd_message.get_string('PER','HR_289509_NOT_GEN_GL_ORGS'),'W');
613: return -1;
614: end if;
615: l_class_profile := fnd_profile.value('HR_GENERATE_GL_ORGS');
618: and l_class_profile = 'N' then
619: writelog(fnd_message.get_string('PER','HR_289478_PROF_OPTS_WARNING'),'E');
620: end if;
621:
622: hr_utility.set_location(l_proc,30);
623:
624: if (p_mode = 'GL') then
625: hr_utility.set_location(l_proc,40);
626: begin
621:
622: hr_utility.set_location(l_proc,30);
623:
624: if (p_mode = 'GL') then
625: hr_utility.set_location(l_proc,40);
626: begin
627: select summary_flag
628: into l_summary_flag
629: from gl_code_combinations
629: from gl_code_combinations
630: where code_combination_id = p_ccid;
631:
632: if (upper(l_summary_flag) = 'Y') then
633: hr_utility.set_location(l_proc,50);
634: writelog('PER_289624_SUMM_FLG_CCID');
635: return -1;
636: end if;
637:
636: end if;
637:
638: exception
639: when no_data_found then
640: hr_utility.set_location(l_proc,60);
641: /*
642: ** This message has got tokens so stick all the stuff
643: ** on the stack and then pull it off to get the fully
644: ** translated and token substituted string...
642: ** This message has got tokens so stick all the stuff
643: ** on the stack and then pull it off to get the fully
644: ** translated and token substituted string...
645: */
646: hr_utility.set_message(800,'HR_289488_INV_CCID');
647: fnd_message.set_token('CCID',p_ccid);
648: l_message := fnd_message.get();
649: /*
650: ** ... having got the translated and token substituted message
650: ** ... having got the translated and token substituted message
651: ** put it all back on the stack so it is found later when the error
652: ** is raised...
653: */
654: hr_utility.set_message(800,'HR_289488_INV_CCID');
655: fnd_message.set_token('CCID',p_ccid);
656: /*
657: ** ...now pass the string to writelog and get the error raised.
658: */
656: /*
657: ** ...now pass the string to writelog and get the error raised.
658: */
659: writelog(l_message,'E');
660: hr_utility.raise_error;
661: end;
662: end if; /* p_mode = 'GL' */
663: hr_utility.set_location(l_proc,70);
664:
659: writelog(l_message,'E');
660: hr_utility.raise_error;
661: end;
662: end if; /* p_mode = 'GL' */
663: hr_utility.set_location(l_proc,70);
664:
665: /* Check if Org already exists using company and cost center valueset/value
666: ** information
667: */
684: p_cost_center;
685:
686: fetch c_list into l_organization_id,l_organization_name;
687: close c_list;
688: hr_utility.set_location(l_proc,80);
689:
690: if (l_organization_id <> -1) then
691: hr_utility.set_location(l_proc,83);
692: /*
687: close c_list;
688: hr_utility.set_location(l_proc,80);
689:
690: if (l_organization_id <> -1) then
691: hr_utility.set_location(l_proc,83);
692: /*
693: ** This message has got tokens so stick all the stuff
694: ** on the stack and then pull it off to get the fully
695: ** translated and token substituted string...
693: ** This message has got tokens so stick all the stuff
694: ** on the stack and then pull it off to get the fully
695: ** translated and token substituted string...
696: */
697: hr_utility.set_message(800,'HR_289600_ORG_ALRDY');
698: fnd_message.set_token('NAME',l_organization_name);
699: fnd_message.set_token('COMPANY',p_company);
700: fnd_message.set_token('COST',p_cost_center);
701: l_message := fnd_message.get();
723: and flex_value = :2 '
724: using p_company_vs, p_company;
725: fetch c_list into l_company_desc,l_comp_start_dt;
726: close c_list;
727: hr_utility.set_location(l_proc,86);
728:
729: if (l_company_desc is null) then
730: l_company_desc := p_company;
731: end if;
736: and flex_value = :2'
737: using p_cc_vs, p_cost_center;
738: fetch c_list into l_cc_desc,l_cc_start_dt;
739: close c_list;
740: hr_utility.set_location(l_proc,90);
741:
742: if (l_cc_desc is null) then
743: l_cc_desc := p_cost_center;
744: end if;
756:
757: /* Read the Org Name Format */
758: l_org_name_format := fnd_profile.value('HR_GL_ORG_NAME_FORMAT');
759: if (l_org_name_format IS NULL) then
760: hr_utility.set_location(l_proc,100);
761: hr_utility.set_message(801,'HR_289489_NO_NAME_FORMAT');
762: writelog(fnd_message.get_string('PER','HR_289489_NO_NAME_FORMAT'),'E');
763: end if;
764:
757: /* Read the Org Name Format */
758: l_org_name_format := fnd_profile.value('HR_GL_ORG_NAME_FORMAT');
759: if (l_org_name_format IS NULL) then
760: hr_utility.set_location(l_proc,100);
761: hr_utility.set_message(801,'HR_289489_NO_NAME_FORMAT');
762: writelog(fnd_message.get_string('PER','HR_289489_NO_NAME_FORMAT'),'E');
763: end if;
764:
765: hr_utility.set_location(l_proc,110);
761: hr_utility.set_message(801,'HR_289489_NO_NAME_FORMAT');
762: writelog(fnd_message.get_string('PER','HR_289489_NO_NAME_FORMAT'),'E');
763: end if;
764:
765: hr_utility.set_location(l_proc,110);
766: writelog('Org Format is : '||l_org_name_format,'D');
767: /*
768: ** Build the organization name by Substituting the available componenents
769: ** using the organization name format string defined.
778: '$CCN', l_cc_desc), 1, g_org_name_length)
779: into l_org_name
780: from dual;
781:
782: hr_utility.set_location(l_proc,120);
783: l_org_name := substrb(l_org_name, 1, g_org_name_length);
784: l_orig_org_name := l_org_name;
785: writelog('Org to be created is : '||l_org_name,'D');
786:
791: open csr_chk_org_name;
792: fetch csr_chk_org_name into l_organization_id;
793:
794: if (csr_chk_org_name%NOTFOUND) then
795: hr_utility.set_location(l_proc,125);
796:
797: if l_org_profile = 'SCO' then
798: /*
799: ** No organization exists with this name and we want to create missing
802: */
803: close csr_chk_org_name;
804:
805: begin
806: hr_utility.set_location(l_proc||' org name '||l_org_name,130);
807: hr_organization_api.create_organization
808: (p_effective_date => sysdate
809: ,p_business_group_id => p_bg_id
810: ,p_date_from => l_org_start_dt
813: ,p_organization_id => l_organization_id
814: ,p_object_version_number => l_org_ovn);
815: writelog('Completed create_organization, org_id = '||l_organization_id,'D');
816: l_new_org_created := TRUE;
817: hr_utility.set_location(l_proc,140);
818: exception
819: when OTHERS then
820: /* We got an error in creating the org. Write this to the log file and
821: ** stop processing this company cost center.
829: /* The HR_GENERATE_GL_ORGS contains the token indicating we should create
830: ** a Company Cost Center(Class = 'CC') classification/info type.
831: */
832: begin
833: hr_utility.set_location(l_proc,150);
834: hr_organization_api.create_company_cost_center
835: (p_effective_date => sysdate
836: ,p_organization_id=>l_organization_id
837: ,p_company_valueset_id => p_company_vs
854: writelog('Error : '|| SQLCODE ||' - '|| SQLERRM, 'W');
855: return -1;
856: end;
857: end if;
858: hr_utility.set_location(l_proc,160);
859:
860: if (l_class_profile = 'CCHR') then
861: /* The HR_GENERATE_GL_ORGS contains the token indicating we should create
862: ** an HR Organization(Class = 'HR_ORG') classification.
864: open csr_chk_org_class(l_organization_id, 'HR_ORG');
865: fetch csr_chk_org_class into l_enabled_flag, l_class_id, l_class_ovn;
866:
867: if csr_chk_org_class%NOTFOUND then
868: hr_utility.set_location(l_proc,300);
869:
870:
871: begin
872: hr_utility.set_location(l_proc,170);
868: hr_utility.set_location(l_proc,300);
869:
870:
871: begin
872: hr_utility.set_location(l_proc,170);
873: hr_organization_api.create_org_classification
874: (p_effective_date => sysdate
875: ,p_organization_id=>l_organization_id
876: ,p_org_classif_code =>'HR_ORG'
915: ** Create the Company Cost Center Org classification and info type.
916: ** Add the HR_ORG classification if the profile indicates it's required and the
917: ** organization does not have it already.
918: */
919: hr_utility.set_location(l_proc,180);
920: close csr_chk_org_name;
921:
922: open csr_chk_org_class(l_organization_id, 'CC');
923: fetch csr_chk_org_class into l_enabled_flag, l_class_id, l_class_ovn;
922: open csr_chk_org_class(l_organization_id, 'CC');
923: fetch csr_chk_org_class into l_enabled_flag, l_class_id, l_class_ovn;
924:
925: if csr_chk_org_class%FOUND then --> A
926: hr_utility.set_location(l_proc,190);
927: close csr_chk_org_class;
928:
929: open csr_chk_info_type(l_organization_id, 'Company Cost Center');
930: fetch csr_chk_info_type into l_company, l_company_vs, l_cost_center,
934: /*
935: ** The org exists with the CC class but without the info type therefore
936: ** create the Company Cost Center org information type.
937: */
938: hr_utility.set_location(l_proc,200);
939: close csr_chk_info_type;
940:
941: begin
942: if l_enabled_flag = 'N' then
948: p_org_info_type_code => 'CC',
949: p_object_version_number => l_class_ovn);
950: end if;
951:
952: hr_utility.set_location(l_proc,210);
953: hr_organization_api.create_org_information
954: (p_effective_date => sysdate
955: ,p_organization_id=>l_organization_id
956: ,p_org_info_type_code => 'Company Cost Center'
993: open csr_chk_org_class(l_organization_id, 'HR_ORG');
994: fetch csr_chk_org_class into l_enabled_flag, l_class_id, l_class_ovn;
995:
996: if csr_chk_org_class%NOTFOUND then
997: hr_utility.set_location(l_proc,310);
998:
999:
1000: begin
1001: hr_utility.set_location(l_proc,220);
997: hr_utility.set_location(l_proc,310);
998:
999:
1000: begin
1001: hr_utility.set_location(l_proc,220);
1002: hr_organization_api.create_org_classification
1003: (p_effective_date => sysdate
1004: ,p_organization_id=>l_organization_id
1005: ,p_org_classif_code =>'HR_ORG'
1027: else /* csr_chk_info_type%NOTFOUND */
1028: /*
1029: ** Check the data on the info type and proceed as appropriate.
1030: */
1031: hr_utility.set_location(l_proc,220);
1032: close csr_chk_info_type;
1033:
1034: if l_company is null
1035: and l_cost_center is null
1038: /*
1039: ** The info type is present but empty. This should never happen since
1040: ** the Company Value Set segment is mandatory.
1041: */
1042: hr_utility.set_location(l_proc,230);
1043: null;
1044: else --> D
1045: /*
1046: ** The info type is present with data but to have got to this point
1048: ** for the current cost center. We need to derive a new org name
1049: ** and create the org, the classification and the info type if we
1050: ** are creating new orgs.
1051: */
1052: hr_utility.set_location(l_proc,240);
1053:
1054: /*
1055: ** Reset the organization ID since the org we found is of no use
1056: ** to us.
1076: l_org_name := substrb(l_org_name,1,
1077: g_org_name_length-length(l_org_name_suffix))||
1078: l_org_name_suffix;
1079: loop
1080: hr_utility.set_location(l_proc,250);
1081: l_organization_id := null;
1082: open csr_chk_org_name;
1083: fetch csr_chk_org_name into l_organization_id;
1084: close csr_chk_org_name;
1105: AND l_number_of_retries <> g_max_number_of_retries) then
1106: /*
1107: ** We got an unused org name so create the org...
1108: */
1109: hr_utility.set_location(l_proc,260);
1110:
1111: begin
1112: hr_utility.set_location(l_proc||' org name '||l_org_name,130);
1113: hr_organization_api.create_organization
1108: */
1109: hr_utility.set_location(l_proc,260);
1110:
1111: begin
1112: hr_utility.set_location(l_proc||' org name '||l_org_name,130);
1113: hr_organization_api.create_organization
1114: (p_effective_date => sysdate
1115: ,p_business_group_id => p_bg_id
1116: ,p_date_from => l_org_start_dt
1119: ,p_organization_id => l_organization_id
1120: ,p_object_version_number => l_org_ovn);
1121: writelog('Completed create_organization, org_id = '||l_organization_id,'D');
1122: l_new_org_created := TRUE;
1123: hr_utility.set_location(l_proc,140);
1124: exception
1125: when OTHERS then
1126: /* We got an error in creating the org. Write this to the log file and
1127: ** stop processing this company cost center.
1135: /* The HR_GENERATE_GL_ORGS contains the token indicating we should create
1136: ** a Company Cost Center(Class = 'CC') classification/info type.
1137: */
1138: begin
1139: hr_utility.set_location(l_proc,150);
1140: hr_organization_api.create_company_cost_center
1141: (p_effective_date => sysdate
1142: ,p_organization_id=>l_organization_id
1143: ,p_company_valueset_id => p_company_vs
1160: writelog('Error : '|| SQLCODE ||' - '|| SQLERRM, 'W');
1161: return -1;
1162: end;
1163: end if; /* l_org_profile LIKE 'CC%' */
1164: hr_utility.set_location(l_proc,160);
1165:
1166: if (l_class_profile = 'CCHR') then
1167: /* The HR_GENERATE_GL_ORGS contains the token indicating we should create
1168: ** an HR Organization(Class = 'HR_ORG') classification.
1170: open csr_chk_org_class(l_organization_id, 'HR_ORG');
1171: fetch csr_chk_org_class into l_enabled_flag, l_class_id, l_class_ovn;
1172:
1173: if csr_chk_org_class%NOTFOUND then
1174: hr_utility.set_location(l_proc,315);
1175:
1176: begin
1177: hr_utility.set_location(l_proc,170);
1178: hr_organization_api.create_org_classification
1173: if csr_chk_org_class%NOTFOUND then
1174: hr_utility.set_location(l_proc,315);
1175:
1176: begin
1177: hr_utility.set_location(l_proc,170);
1178: hr_organization_api.create_org_classification
1179: (p_effective_date => sysdate
1180: ,p_organization_id=>l_organization_id
1181: ,p_org_classif_code =>'HR_ORG'
1205: ** We exited the loop above without finding a unique org name
1206: ** so raise an error to this effect.
1207: ** Need to do the same messing about as earlier as this has tokens....
1208: */
1209: hr_utility.set_message(801,'HR_289490_NO_UNIQ_NAME');
1210: fnd_message.set_token('ORG_NAME',l_orig_org_name);
1211: l_message := fnd_message.get();
1212: hr_utility.set_message(801,'HR_289490_NO_UNIQ_NAME');
1213: fnd_message.set_token('ORG_NAME',l_orig_org_name);
1208: */
1209: hr_utility.set_message(801,'HR_289490_NO_UNIQ_NAME');
1210: fnd_message.set_token('ORG_NAME',l_orig_org_name);
1211: l_message := fnd_message.get();
1212: hr_utility.set_message(801,'HR_289490_NO_UNIQ_NAME');
1213: fnd_message.set_token('ORG_NAME',l_orig_org_name);
1214: writelog(l_message,'W');
1215: return -1;
1216: end if; /* l_organization_id IS NULL */
1225: /*
1226: ** We don't have a company cost center classification for this org so
1227: ** create it together with the info type data.
1228: */
1229: hr_utility.set_location(l_proc,250);
1230: IF csr_chk_org_class%isopen then
1231: close csr_chk_org_class;
1232: end if;
1233:
1235: /* The HR_GENERATE_GL_ORGS contains the token indicating we should create
1236: ** a Company Cost Center(Class = 'CC') classification/info type.
1237: */
1238: begin
1239: hr_utility.set_location(l_proc,150);
1240: hr_organization_api.create_company_cost_center
1241: (p_effective_date => sysdate
1242: ,p_organization_id=>l_organization_id
1243: ,p_company_valueset_id => p_company_vs
1260: writelog('Error : '|| SQLCODE ||' - '|| SQLERRM, 'W');
1261: return -1;
1262: end;
1263: end if; /* l_org_profile LIKE 'CC%' */
1264: hr_utility.set_location(l_proc,160);
1265:
1266: if (l_class_profile = 'CCHR') then
1267: /* The HR_GENERATE_GL_ORGS contains the token indicating we should create
1268: ** an HR Organization(Class = 'HR_ORG') classification. Only do this if
1271: open csr_chk_org_class(l_organization_id, 'HR_ORG');
1272: fetch csr_chk_org_class into l_enabled_flag, l_class_id, l_class_ovn;
1273:
1274: if csr_chk_org_class%NOTFOUND then
1275: hr_utility.set_location(l_proc,320);
1276: begin
1277: hr_utility.set_location(l_proc,170);
1278: hr_organization_api.create_org_classification
1279: (p_effective_date => sysdate
1273:
1274: if csr_chk_org_class%NOTFOUND then
1275: hr_utility.set_location(l_proc,320);
1276: begin
1277: hr_utility.set_location(l_proc,170);
1278: hr_organization_api.create_org_classification
1279: (p_effective_date => sysdate
1280: ,p_organization_id=>l_organization_id
1281: ,p_org_classif_code =>'HR_ORG'
1307: end if; /* csr_chk_org_name%NOTFOUND */
1308:
1309: if l_organization_id > -1 then
1310: if l_new_org_created then
1311: hr_utility.set_message(800,'HR_289195_SYNCD_NEW_ORG');
1312: fnd_message.set_token('NAME',l_org_name);
1313: fnd_message.set_token('COMPANY',p_company);
1314: fnd_message.set_token('COST',p_cost_center);
1315: l_message := fnd_message.get();
1313: fnd_message.set_token('COMPANY',p_company);
1314: fnd_message.set_token('COST',p_cost_center);
1315: l_message := fnd_message.get();
1316: else
1317: hr_utility.set_message(800,'HR_289177_SYNCD_ORG');
1318: fnd_message.set_token('NAME',l_org_name);
1319: fnd_message.set_token('COMPANY',p_company);
1320: fnd_message.set_token('COST',p_cost_center);
1321: l_message := fnd_message.get();
1324: ** ...now pass the string to writelog.
1325: */
1326: writelog(l_message,'I');
1327: end if;
1328: hr_utility.set_location('Leaving : '||l_proc,180);
1329: p_org_id := l_organization_id;
1330: return 0;
1331:
1332: end create_org_and_classification;
1357: and cc.org_information4 is null
1358: and cc.org_information5 is null;
1359:
1360: begin
1361: hr_utility.set_location('Entering : '||l_proc,10);
1362:
1363: open company_bg;
1364: fetch company_bg bulk collect into l_bg_tab limit 10;
1365: close company_bg;
1363: open company_bg;
1364: fetch company_bg bulk collect into l_bg_tab limit 10;
1365: close company_bg;
1366:
1367: hr_utility.set_location(l_proc,20);
1368:
1369: if (l_bg_tab.count > 1) then -- This company exists in more than 1 BG, so error
1370: hr_utility.set_location(l_proc,30);
1371: l_bgid:=-1;
1366:
1367: hr_utility.set_location(l_proc,20);
1368:
1369: if (l_bg_tab.count > 1) then -- This company exists in more than 1 BG, so error
1370: hr_utility.set_location(l_proc,30);
1371: l_bgid:=-1;
1372: writelog(fnd_message.get_string('PER','HR_289491_CO_MULT_BG'),'E');
1373: elsif (l_bg_tab.count = 0) then -- This company does not exist in any BG so error
1374: hr_utility.set_location(l_proc,30);
1370: hr_utility.set_location(l_proc,30);
1371: l_bgid:=-1;
1372: writelog(fnd_message.get_string('PER','HR_289491_CO_MULT_BG'),'E');
1373: elsif (l_bg_tab.count = 0) then -- This company does not exist in any BG so error
1374: hr_utility.set_location(l_proc,30);
1375: l_bgid:=-1;
1376: writelog('Company : '||p_company,'D');
1377: writelog('Company VS: '||to_char(p_companyvs),'D');
1378: writelog(fnd_message.get_string('PER','HR_289601_CO_NO_BG'),'E');
1376: writelog('Company : '||p_company,'D');
1377: writelog('Company VS: '||to_char(p_companyvs),'D');
1378: writelog(fnd_message.get_string('PER','HR_289601_CO_NO_BG'),'E');
1379: elsif l_bg_tab.count = 1 then
1380: hr_utility.set_location(l_proc,40);
1381: l_bgid := l_bg_tab(1);
1382: writelog('bg id '||l_bgid,'D');
1383: hr_utility.set_location(l_proc||' BG id '||to_char(l_bgid),45);
1384: end if;
1379: elsif l_bg_tab.count = 1 then
1380: hr_utility.set_location(l_proc,40);
1381: l_bgid := l_bg_tab(1);
1382: writelog('bg id '||l_bgid,'D');
1383: hr_utility.set_location(l_proc||' BG id '||to_char(l_bgid),45);
1384: end if;
1385:
1386: hr_utility.set_location('Leaving : '||l_proc,50);
1387: return l_bgid;
1382: writelog('bg id '||l_bgid,'D');
1383: hr_utility.set_location(l_proc||' BG id '||to_char(l_bgid),45);
1384: end if;
1385:
1386: hr_utility.set_location('Leaving : '||l_proc,50);
1387: return l_bgid;
1388: END;
1389:
1390:
1450: and p_segment = flex_value;
1451:
1452: ----
1453: begin
1454: hr_utility.set_location('Entering : '||l_proc,10);
1455:
1456: begin
1457: droptable(p_hr_cc_reporting_temp,TRUE);
1458: end;
1455:
1456: begin
1457: droptable(p_hr_cc_reporting_temp,TRUE);
1458: end;
1459: hr_utility.set_location(l_proc,20);
1460:
1461: execute immediate 'create table '||p_hr_cc_reporting_temp ||'
1462: (cc_value_set number,
1463: company_value_set number,
1474: for l_coas in c_coas
1475: loop
1476: --
1477:
1478: hr_utility.set_location(l_proc||' chart of acct ID '||
1479: to_char(l_coas.chart_of_accounts_id),30);
1480:
1481: l_retcode := getSegmentForQualifier(l_coas.chart_of_accounts_id,
1482: 'GL_BALANCING',
1495: if (l_cc_segment is NOT NULL and
1496: l_company_segment IS NOT NULL and
1497: l_company_vs <> -1 and
1498: l_cc_vs <> -1) then
1499: hr_utility.set_location(l_proc,40);
1500:
1501: open c_list2 for 'SELECT /*+ORDERED USE_NL(gcc)*/
1502: distinct gcc.'||l_company_segment||' company,
1503: gcc.'||l_cc_segment ||' cost_center
1583: end loop;
1584: close c_list2;
1585: --
1586: --
1587: hr_utility.set_location(l_proc,50);
1588: /*
1589: ** We have populated the temp table with company cost centers for the
1590: ** current COA. Now check the rows in the temp table. If we don't
1591: ** have any tell the user.
1608: end if;
1609:
1610:
1611: end loop;
1612: hr_utility.set_location(l_proc,70);
1613:
1614: /*
1615: ** Delete records from the working table which are for companies which do
1616: ** not exist in the business group. Because GL CC table spans multiple
1646: and cc.org_information4 is null
1647: and cc.org_information5 is null)';
1648:
1649: execute immediate l_stmt using p_bgid;
1650: hr_utility.set_location(l_proc,80);
1651:
1652: /*
1653: ** We have populated the temp table and deleted records from it
1654: ** if no company organization has been defined in the business
1665:
1666: /* Read the Org Name Format */
1667: l_org_name_format := fnd_profile.value('HR_GL_ORG_NAME_FORMAT');
1668: if (l_org_name_format IS NULL) then
1669: hr_utility.set_location(l_proc,90);
1670: hr_utility.set_message(801,'HR_289489_NO_NAME_FORMAT');
1671: writelog(fnd_message.get_string('PER','HR_289489_NO_NAME_FORMAT'),'E');
1672: end if;
1673:
1666: /* Read the Org Name Format */
1667: l_org_name_format := fnd_profile.value('HR_GL_ORG_NAME_FORMAT');
1668: if (l_org_name_format IS NULL) then
1669: hr_utility.set_location(l_proc,90);
1670: hr_utility.set_message(801,'HR_289489_NO_NAME_FORMAT');
1671: writelog(fnd_message.get_string('PER','HR_289489_NO_NAME_FORMAT'),'E');
1672: end if;
1673:
1674: writelog('Org Format is : '||l_org_name_format,'D');
1685: :g_org_name_length)
1686: from dual)';
1687:
1688: execute immediate l_stmt using l_org_name_format, g_org_name_length;
1689: hr_utility.set_location(l_proc,80);
1690:
1691: -- Now check if Org Names are duplicated for different value sets
1692:
1693: l_stmt := 'update '||p_hr_cc_reporting_temp||' rep
1715:
1716: writelog(' Done processing. Spooling to file now.','D');
1717: spoolToFile(p_hr_cc_reporting_temp);
1718: end if;
1719: hr_utility.set_location('Leaving : '||l_proc,100);
1720: -- commit;
1721: if l_retcode= 0 then
1722: return l_retcode1;
1723: end if;
1763: c_list curType;
1764: l_rowcount number;
1765:
1766: BEGIN
1767: hr_utility.set_location('Entering : '||l_proc,10);
1768:
1769: droptable(p_hr_sync_temp, TRUE );
1770: droptable(p_hr_sync_ccid, TRUE );
1771:
1789:
1790: execute immediate 'create index '||p_hr_sync_temp||'_n1 on '||
1791: p_hr_sync_temp||' (chart_of_accounts_id)';
1792:
1793: hr_utility.set_location(l_proc,20);
1794:
1795: for l_coas in c_coas
1796: loop
1797: hr_utility.set_location(l_proc,30);
1793: hr_utility.set_location(l_proc,20);
1794:
1795: for l_coas in c_coas
1796: loop
1797: hr_utility.set_location(l_proc,30);
1798: l_retcode := getSegmentForQualifier(l_coas.chart_of_accounts_id,
1799: 'GL_BALANCING',
1800: l_company);
1801: l_retcode := getSegmentForQualifier(l_coas.chart_of_accounts_id,
1818: p_cc_segment => l_cost_center,
1819: p_company_vs => l_company_vs,
1820: p_cc_vs => l_cc_vs);
1821: else
1822: hr_utility.set_location(l_proc,50);
1823: /*
1824: ** In the following SQL - l_company and l_cost_center hold the
1825: ** name of the segment column holding the company and cost center info
1826: ** cause the data from the corresponding column in the code comb table
1858:
1859: end if;
1860:
1861: end loop;
1862: hr_utility.set_location(l_proc,60);
1863: -- commit;
1864:
1865: /*
1866: ** Remove those records for which no company organization can be found.
1882: and units.business_group_id = :1
1883: and cc.org_information4 is null
1884: and cc.org_information5 is null)';
1885: execute immediate l_stmt using p_bgid;
1886: hr_utility.set_location(l_proc,62);
1887:
1888: /*
1889: ** We have populated the temp table and excluded records from it
1890: ** if no company organization has been defined in the business
1894: open c_list for 'select count(*) from '||p_hr_sync_temp;
1895: fetch c_list into l_rowcount;
1896: close c_list;
1897: if l_rowcount = 0 then
1898: hr_utility.set_location(l_proc,64);
1899: writelog(fnd_message.get_string('PER','HR_289599_NO_CC_PROC'),'W');
1900: /*
1901: ** We've got no rows in the temp table so bail out of the processing
1902: ** now as doing any further work is just inefficient.
1914: **
1915: ** This SQL needs rewriting to use org_information1 in the search and not
1916: ** the individual segments.
1917: */
1918: hr_utility.set_location(l_proc,66);
1919:
1920: l_stmt := 'create table '||p_hr_sync_ccid||'(ccid, org_id) as
1921: (select sync.ccid,
1922: units.organization_id
1935: and cc.org_information4 = to_char(sync.cc_vs)
1936: and cc.org_information5 = sync.cost_center)';
1937:
1938: execute immediate l_stmt;
1939: hr_utility.set_location(l_proc,70);
1940:
1941: begin
1942: execute immediate 'create unique index '||p_hr_sync_ccid||'_u1 on '||
1943: p_hr_sync_ccid||'(ccid)';
1948: writelog(fnd_message.get_string('PER','HR_289492_DUP_CCIDS'),'E');
1949: end if;
1950:
1951: end;
1952: hr_utility.set_location(l_proc,80);
1953: -- commit;
1954:
1955: -- Now analyze table for avoiding view sort
1956:
1957: l_temp_table := substr(p_hr_sync_temp, instr(p_hr_sync_temp, '.') + 1,
1958: length(p_hr_sync_temp)) ;
1959:
1960: fnd_stats.gather_table_stats (errbuf,l_retcode, p_schema, l_temp_table, 10, 1);
1961: hr_utility.set_location(l_proc,90);
1962:
1963: l_temp_table := substr(p_hr_sync_ccid, instr(p_hr_sync_ccid, '.') + 1,
1964: length(p_hr_sync_ccid)) ;
1965: fnd_stats.gather_table_stats (errbuf,l_retcode, p_schema, l_temp_table,10, 1);
1964: length(p_hr_sync_ccid)) ;
1965: fnd_stats.gather_table_stats (errbuf,l_retcode, p_schema, l_temp_table,10, 1);
1966: end if;
1967:
1968: hr_utility.set_location('Leaving : '||l_proc,100);
1969:
1970: return l_retcode;
1971: end;
1972:
2031: l_last_update_login number := 0;
2032: l_message varchar2(1000);
2033:
2034: BEGIN
2035: hr_utility.set_location('Entering : '||l_proc,10);
2036: /*
2037: ** Create and populate temporary tables which
2038: ** contain the company cost centers to be processed and the GL CCID's
2039: ** which already have a matching org(classified as company cost center with
2070:
2071: execute immediate l_stmt;
2072:
2073: writelog('Updated '||sql%rowcount||' rows in GL_CODE_COMBINATIONS','D');
2074: hr_utility.set_message(800,'HR_289179_COMB_ORG_ALRDY');
2075: fnd_message.set_token('ROWCOUNT',sql%rowcount);
2076: l_message := fnd_message.get();
2077: /*
2078: ** ...now pass the string to writelog.
2091: ** Work out if we are just syncing with existing orgs based on current
2092: ** classifications.
2093: */
2094: l_org_profile := fnd_profile.value('HR_GENERATE_GL_OPTIONS');
2095: hr_utility.set_location('generate GL options '||l_org_profile,20);
2096: if (l_org_profile = 'S') then
2097: hr_utility.set_location(l_proc,25);
2098: writelog(fnd_message.get_string('PER','HR_289509_NOT_GEN_GL_ORGS'),'W');
2099: return 1;
2093: */
2094: l_org_profile := fnd_profile.value('HR_GENERATE_GL_OPTIONS');
2095: hr_utility.set_location('generate GL options '||l_org_profile,20);
2096: if (l_org_profile = 'S') then
2097: hr_utility.set_location(l_proc,25);
2098: writelog(fnd_message.get_string('PER','HR_289509_NOT_GEN_GL_ORGS'),'W');
2099: return 1;
2100: end if;
2101:
2104: ** records which have a matching cost center Org already defined
2105: ** we can remove the records from our other temp table for CCID's which
2106: ** now have a value for the org ID.
2107: */
2108: hr_utility.set_location(l_proc,30);
2109: execute immediate 'delete from '||p_hr_sync_temp ||'
2110: where ccid in
2111: (select code_combination_id
2112: from gl_code_combinations
2122: open c_list for 'select count(*) from '||p_hr_sync_temp;
2123: fetch c_list into l_rowcount;
2124: close c_list;
2125: if l_rowcount > 0 then
2126: -- hr_utility.set_message(800,'HR_289180_COMB_TO_SYNC');
2127: -- fnd_message.set_token('ROWCOUNT',l_rowcount);
2128: -- l_message := fnd_message.get();
2129: /*
2130: ** ...now pass the string to writelog.
2201: ' rows in GL Code combinations with newly created Org ids','D');
2202: if sql%rowcount = 0 then
2203: writelog(fnd_message.get_String('PER','HR_289180_SYNC_SUMMARY_ZERO'),'I');
2204: else
2205: hr_utility.set_message(800,'HR_289184_SYNC_SUMMARY');
2206: fnd_message.set_token('ROWCOUNT',sql%rowcount);
2207: l_message := fnd_message.get();
2208: writelog(l_message,'I');
2209: end if;
2232: l_incremental_enabled VARCHAR2(10) := 'N';
2233: l_autoorgs_enabled varchar2(10) := 'N';
2234:
2235: BEGIN
2236: hr_utility.set_location('Entering : '||l_proc,10);
2237: --
2238: -- >>> Code Change Start >>> (See change history for ver 115.46)
2239: --
2240: ----------------------------------------------------------------------
2259: -- l_incremental_enabled = 'N')) THEN
2260: -- return;
2261: -- END IF;
2262: --
2263: -- hr_utility.set_location(l_proc,20);
2264: --
2265: -- l_request_id := fnd_request.submit_request(
2266: -- l_application_short_name,
2267: -- 'HR_CCID_GL_COST_CENTERS',
2286: hr_gl_sync_orgs.sync_single_org(p_ccid);
2287: --
2288: -- <<< Code Change End <<<
2289: --
2290: hr_utility.set_location('Leaving : '||l_proc,10);
2291: end;
2292:
2293: function incrementalOrgs(p_mode in varchar2,
2294: p_ccid in NUMBER)
2311: l_org_id NUMBER := -1;
2312: l_bg_id NUMBER := -1;
2313:
2314: BEGIN
2315: hr_utility.set_location('Entering : '||l_proc,10);
2316: writelog('Inside incrementalOrgs for p_ccid = '||p_ccid,'D');
2317:
2318: select chart_of_accounts_id
2319: into l_chart_of_accounts_id
2356: where code_combination_id =:1'
2357: using p_ccid;
2358: fetch c_list into l_company, l_cost_center ;
2359: close c_list;
2360: hr_utility.set_location(l_proc,30);
2361:
2362: -- Now Check if an Org already exists with a Cost Center Classification
2363: -- for this Company/Cost Center
2364:
2382: and cc.org_information3 = :2
2383: and cc.org_information5 = :4 '
2384: using to_char(l_company_vs), to_char(l_cc_vs),
2385: l_company, l_cost_center;
2386: hr_utility.set_location(l_proc,31);
2387: fetch c_list into l_org_id;
2388: hr_utility.set_location(l_proc,32);
2389: close c_list;
2390: hr_utility.set_location(l_proc,35);
2384: using to_char(l_company_vs), to_char(l_cc_vs),
2385: l_company, l_cost_center;
2386: hr_utility.set_location(l_proc,31);
2387: fetch c_list into l_org_id;
2388: hr_utility.set_location(l_proc,32);
2389: close c_list;
2390: hr_utility.set_location(l_proc,35);
2391: if (l_org_id = -1) then -- create new org
2392: hr_utility.set_location(l_proc,40);
2386: hr_utility.set_location(l_proc,31);
2387: fetch c_list into l_org_id;
2388: hr_utility.set_location(l_proc,32);
2389: close c_list;
2390: hr_utility.set_location(l_proc,35);
2391: if (l_org_id = -1) then -- create new org
2392: hr_utility.set_location(l_proc,40);
2393: writelog('Org ID does not already exist ','D');
2394:
2388: hr_utility.set_location(l_proc,32);
2389: close c_list;
2390: hr_utility.set_location(l_proc,35);
2391: if (l_org_id = -1) then -- create new org
2392: hr_utility.set_location(l_proc,40);
2393: writelog('Org ID does not already exist ','D');
2394:
2395: l_bg_id := getBGID(l_company, l_company_vs);
2396:
2410: ** has beenremovd from seed115 but leaving this code in place
2411: ** just in case the 289601 error is not detected and we exit
2412: ** getBGID with l_bg_id = -1.
2413: */
2414: hr_utility.set_message(801,'HR_289493_NO_COMPANY_ORG');
2415: hr_utility.raise_error;
2416: end if;
2417: end if;
2418:
2411: ** just in case the 289601 error is not detected and we exit
2412: ** getBGID with l_bg_id = -1.
2413: */
2414: hr_utility.set_message(801,'HR_289493_NO_COMPANY_ORG');
2415: hr_utility.raise_error;
2416: end if;
2417: end if;
2418:
2419: if l_org_id <> -1 then
2416: end if;
2417: end if;
2418:
2419: if l_org_id <> -1 then
2420: hr_utility.set_location(l_proc,50);
2421: execute immediate 'update gl_code_combinations
2422: set company_cost_center_org_id=:1,
2423: last_update_date = sysdate,
2424: last_updated_by = :2
2428: writelog('Updated GL_CODE_COMBINATIONS. Set Company_cost_center_org_id = '||
2429: l_org_id ||' where code_Combination_id = '||p_ccid,'D');
2430: end if;
2431:
2432: hr_utility.set_location('Leaving : '||l_proc,60);
2433: return l_retcode;
2434: end;
2435:
2436: Procedure synch_orgs(errbuf in out nocopy VARCHAR2
2458: from user_triggers
2459: where trigger_name='HR_ALL_ORGANIZATION_UNITS_UTF8';
2460:
2461: BEGIN
2462: hr_utility.set_location('Entering : '||l_proc,10);
2463: /*
2464: ** Set the Global holding the max length of an org name based
2465: ** on the existence of the UTF8 trigger. If it's there make the
2466: ** length 60, if it's not make it 240.
2467: */
2468: open c_derive_name_length;
2469: fetch c_derive_name_length into l_dummy;
2470: if c_derive_name_length%FOUND then
2471: hr_utility.set_location(l_proc,14);
2472: g_org_name_length := 60;
2473: else
2474: hr_utility.set_location(l_proc,18);
2475: g_org_name_length := 240;
2470: if c_derive_name_length%FOUND then
2471: hr_utility.set_location(l_proc,14);
2472: g_org_name_length := 60;
2473: else
2474: hr_utility.set_location(l_proc,18);
2475: g_org_name_length := 240;
2476: end if;
2477: close c_derive_name_length;
2478:
2484: l_hr_sync_temp := l_schema||'.'|| l_hr_sync_temp;
2485: l_hr_sync_ccid := l_schema||'.'|| l_hr_sync_ccid;
2486:
2487: if (p_mode = 'GL') then
2488: hr_utility.set_location(l_proc,20);
2489: if (p_ccid is null) then
2490: hr_utility.set_message(801,'HR_289494_NO_CCID');
2491: hr_utility.raise_error;
2492: retcode := 2;
2486:
2487: if (p_mode = 'GL') then
2488: hr_utility.set_location(l_proc,20);
2489: if (p_ccid is null) then
2490: hr_utility.set_message(801,'HR_289494_NO_CCID');
2491: hr_utility.raise_error;
2492: retcode := 2;
2493: return;
2494: end if;
2487: if (p_mode = 'GL') then
2488: hr_utility.set_location(l_proc,20);
2489: if (p_ccid is null) then
2490: hr_utility.set_message(801,'HR_289494_NO_CCID');
2491: hr_utility.raise_error;
2492: retcode := 2;
2493: return;
2494: end if;
2495: fnd_file.put_names(p_mode||p_ccid||'.log'
2494: end if;
2495: fnd_file.put_names(p_mode||p_ccid||'.log'
2496: ,p_mode||p_ccid||'.out'
2497: ,l_dir);
2498: hr_utility.set_location(l_proc,30);
2499: else
2500: hr_utility.set_location(l_proc,40);
2501: if (p_business_group_id IS NULL) then
2502: writelog(fnd_message.get_string('PER','HR_289495_NO_BG'),'E');
2496: ,p_mode||p_ccid||'.out'
2497: ,l_dir);
2498: hr_utility.set_location(l_proc,30);
2499: else
2500: hr_utility.set_location(l_proc,40);
2501: if (p_business_group_id IS NULL) then
2502: writelog(fnd_message.get_string('PER','HR_289495_NO_BG'),'E');
2503: end if;
2504:
2504:
2505: fnd_file.put_names(p_mode||p_business_group_id||'.log'
2506: ,p_mode||p_business_group_id||'.out'
2507: ,l_dir);
2508: hr_utility.set_location(l_proc,50);
2509:
2510: l_hr_cc_reporting_temp := l_hr_cc_reporting_temp||'_'||p_business_group_id;
2511: l_hr_cc_reporting_comp := l_hr_cc_reporting_comp||'_'||p_business_group_id;
2512: l_hr_sync_temp := l_hr_sync_temp||'_'||p_business_group_id;
2520: writelog('CCID is : '|| p_ccid,'D');
2521:
2522: begin
2523: if (p_mode = 'REPORT') then
2524: hr_utility.set_location(l_proc,60);
2525: l_retcode := reportingMode(
2526: p_application_short_name => l_application_short_name,
2527: p_hr_cc_reporting_temp => l_hr_cc_reporting_temp,
2528: p_bgid => p_business_group_id,
2531: /* Drop tables. FALSE means only drop them if not in DEBUG mode.
2532: */
2533: droptable(l_hr_cc_reporting_temp, FALSE);
2534: elsif (p_mode = 'SYNCHRONIZE') then
2535: hr_utility.set_location(l_proc,70);
2536: l_retcode := synchronizeMode(
2537: p_mode => p_mode,
2538: p_hr_sync_temp => l_hr_sync_temp,
2539: p_hr_sync_ccid => l_hr_sync_ccid,
2540: p_start_date => l_start_date,
2541: p_bgid => p_business_group_id,
2542: p_schema => l_schema,
2543: p_coa => p_coa);
2544: hr_utility.set_location(l_proc, 72);
2545: /* Drop tables. FALSE means only drop them if not in DEBUG mode.
2546: */
2547: droptable(l_hr_sync_temp, FALSE);
2548: droptable(l_hr_sync_ccid, FALSE);
2545: /* Drop tables. FALSE means only drop them if not in DEBUG mode.
2546: */
2547: droptable(l_hr_sync_temp, FALSE);
2548: droptable(l_hr_sync_ccid, FALSE);
2549: hr_utility.set_location(l_proc, 78);
2550: else
2551: hr_utility.set_location(l_proc,80);
2552: l_retcode := incrementalOrgs(p_mode,
2553: p_ccid);
2547: droptable(l_hr_sync_temp, FALSE);
2548: droptable(l_hr_sync_ccid, FALSE);
2549: hr_utility.set_location(l_proc, 78);
2550: else
2551: hr_utility.set_location(l_proc,80);
2552: l_retcode := incrementalOrgs(p_mode,
2553: p_ccid);
2554: end if;
2555: exception
2557: /*
2558: ** I've hit an error and raised an exception so set retcode to
2559: ** indicate a failure and then continue to the exit code.
2560: */
2561: hr_utility.set_location(l_proc, 85);
2562: hr_utility.set_location(SQLERRM,86);
2563: l_retcode := 2;
2564: end;
2565:
2558: ** I've hit an error and raised an exception so set retcode to
2559: ** indicate a failure and then continue to the exit code.
2560: */
2561: hr_utility.set_location(l_proc, 85);
2562: hr_utility.set_location(SQLERRM,86);
2563: l_retcode := 2;
2564: end;
2565:
2566: retcode := l_retcode;
2563: l_retcode := 2;
2564: end;
2565:
2566: retcode := l_retcode;
2567: hr_utility.set_location(l_proc||' retcode '||to_char(retcode),90);
2568: if (retcode = 0) then
2569: writelog('Completed all operations for '||p_mode||' mode.','D');
2570: elsif retcode =1 then
2571: writelog('Completed with warnings.','D');