--------------API TO CREATE BUSINESS GROUP IN BACK END.
CREATE OR REPLACE package body APPS.test_pkg1 is
procedure create_org(errbuff out varchar2,retcode out number) is
--Cursor to fetch the information stored inside the XX_ORG TABLE
cursor org_info is
select org.org_name,bg_name,loc,dt_start,dt_end
from
xx_org org;
--Cursor to fetch the information stored inside the
--hr_all_organization_units by passing the name of the business group.
cursor org_bg(cp_bg_name varchar2) is
select organization_id,date_from from hr_all_organization_units where name=cp_bg_name;
ln_bg_id number default null;
ld_bg_stdt date default sysdate;
lv_succ_status varchar2(10) default 'SUCCESS';
lv_error_status varchar2(10) default 'ERROR';
lv_error_msg varchar2(500) default null;
lv_status varchar2(10);
ln_org_id number default 0;
ln_obj_ver_no number default 0;
lb_org_warning boolean default false;
begin
fnd_file.put_line(1,'Begin Process...');
--Opening the cursor Org_info to fetch the records into org_rec variable
for org_rec in org_info
loop
lv_error_msg:=null;
ln_org_id:=0;
ln_obj_ver_no:=0;
lb_org_warning:=false;
ln_bg_id:=null;
ld_bg_stdt:=sysdate;
lv_status:=lv_succ_status;
fnd_file.put_line(1,'Begin creating organization : '||org_rec.org_name);
--Opening the cursor org_bg by passing the business group name from the above cursor variable.
open org_bg(cp_bg_name => org_rec.bg_name);
--Fetching the records into two different variables from the above cursor Org_info
fetch org_bg into ln_bg_id,ld_bg_stdt;
-- Close the cursor org_bg
close org_bg;
fnd_file.put_line(1,' BG ID : '||ln_bg_id);
fnd_file.put_line(1,' BG start dt : '||to_char(ld_bg_stdt,'dd-mon-yyyy'));
fnd_file.put_line(1,' org start dt : '||to_char(org_rec.dt_start,'dd-mon-yyyy'));
fnd_file.put_line(1,' 1 error status : '||lv_status);
--Passing the variable Business Group ID and verifying that whether it is an existing Business Group or Not.
--If the Business Group Id is NULL then it will enter into the if condition and
--updates the error msgs in the pqr_org_load table.
if ln_bg_id is null then
lv_status:=lv_error_status;
lv_error_msg:='Business group does not exists';
end if;
fnd_file.put_line(1,' 2 error status : '||lv_status);
--Passing the variable Business Group Start Date and verifying that whether it is greater then the
--Organization start date or not.
if ld_bg_stdt > org_rec.dt_start then
lv_status:=lv_error_status;
lv_error_msg:=lv_error_msg||' Business group start date is after org start date';
end if;
fnd_file.put_line(1,' 3 error status : '||lv_status);
--If the above condition is satisfied then we will create the Organization for that particular Business Group.
if lv_status<>lv_error_status then
hr_organization_api.create_organization( p_validate => false
,p_effective_date => sysdate
,p_business_group_id => ln_bg_id
,p_date_from => org_rec.dt_start
,p_name => org_rec.org_name
,p_organization_id => ln_org_id
,p_object_version_number => ln_obj_ver_no
,p_duplicate_org_warning => lb_org_warning);
fnd_file.put_line(1,' created Organization : '||ln_org_id);
update xx_org set status=lv_succ_status,error_msg='Organization '||org_rec.org_name||' created with org id : '||ln_org_id
where org_name=org_rec.org_name;
else
update xx_org set status=lv_error_status,error_msg=lv_error_msg
where org_name=org_rec.org_name;
fnd_file.put_line(1,' Error message : '||lv_error_msg);
end if;
fnd_file.put_line(1,'Completion of org creation : '||org_rec.org_name);
end loop;
commit;
exception
when others then
fnd_file.put_line(1,'exception occured '||SQLERRM);
end create_org;
end test_pkg1;
/
CREATE OR REPLACE package body APPS.test_pkg1 is
procedure create_org(errbuff out varchar2,retcode out number) is
--Cursor to fetch the information stored inside the XX_ORG TABLE
cursor org_info is
select org.org_name,bg_name,loc,dt_start,dt_end
from
xx_org org;
--Cursor to fetch the information stored inside the
--hr_all_organization_units by passing the name of the business group.
cursor org_bg(cp_bg_name varchar2) is
select organization_id,date_from from hr_all_organization_units where name=cp_bg_name;
ln_bg_id number default null;
ld_bg_stdt date default sysdate;
lv_succ_status varchar2(10) default 'SUCCESS';
lv_error_status varchar2(10) default 'ERROR';
lv_error_msg varchar2(500) default null;
lv_status varchar2(10);
ln_org_id number default 0;
ln_obj_ver_no number default 0;
lb_org_warning boolean default false;
begin
fnd_file.put_line(1,'Begin Process...');
--Opening the cursor Org_info to fetch the records into org_rec variable
for org_rec in org_info
loop
lv_error_msg:=null;
ln_org_id:=0;
ln_obj_ver_no:=0;
lb_org_warning:=false;
ln_bg_id:=null;
ld_bg_stdt:=sysdate;
lv_status:=lv_succ_status;
fnd_file.put_line(1,'Begin creating organization : '||org_rec.org_name);
--Opening the cursor org_bg by passing the business group name from the above cursor variable.
open org_bg(cp_bg_name => org_rec.bg_name);
--Fetching the records into two different variables from the above cursor Org_info
fetch org_bg into ln_bg_id,ld_bg_stdt;
-- Close the cursor org_bg
close org_bg;
fnd_file.put_line(1,' BG ID : '||ln_bg_id);
fnd_file.put_line(1,' BG start dt : '||to_char(ld_bg_stdt,'dd-mon-yyyy'));
fnd_file.put_line(1,' org start dt : '||to_char(org_rec.dt_start,'dd-mon-yyyy'));
fnd_file.put_line(1,' 1 error status : '||lv_status);
--Passing the variable Business Group ID and verifying that whether it is an existing Business Group or Not.
--If the Business Group Id is NULL then it will enter into the if condition and
--updates the error msgs in the pqr_org_load table.
if ln_bg_id is null then
lv_status:=lv_error_status;
lv_error_msg:='Business group does not exists';
end if;
fnd_file.put_line(1,' 2 error status : '||lv_status);
--Passing the variable Business Group Start Date and verifying that whether it is greater then the
--Organization start date or not.
if ld_bg_stdt > org_rec.dt_start then
lv_status:=lv_error_status;
lv_error_msg:=lv_error_msg||' Business group start date is after org start date';
end if;
fnd_file.put_line(1,' 3 error status : '||lv_status);
--If the above condition is satisfied then we will create the Organization for that particular Business Group.
if lv_status<>lv_error_status then
hr_organization_api.create_organization( p_validate => false
,p_effective_date => sysdate
,p_business_group_id => ln_bg_id
,p_date_from => org_rec.dt_start
,p_name => org_rec.org_name
,p_organization_id => ln_org_id
,p_object_version_number => ln_obj_ver_no
,p_duplicate_org_warning => lb_org_warning);
fnd_file.put_line(1,' created Organization : '||ln_org_id);
update xx_org set status=lv_succ_status,error_msg='Organization '||org_rec.org_name||' created with org id : '||ln_org_id
where org_name=org_rec.org_name;
else
update xx_org set status=lv_error_status,error_msg=lv_error_msg
where org_name=org_rec.org_name;
fnd_file.put_line(1,' Error message : '||lv_error_msg);
end if;
fnd_file.put_line(1,'Completion of org creation : '||org_rec.org_name);
end loop;
commit;
exception
when others then
fnd_file.put_line(1,'exception occured '||SQLERRM);
end create_org;
end test_pkg1;
/
