Sunday, 17 June 2018

How to create org id by using api Oracle apps R12

--------------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;
/

No comments:

Post a Comment

Query to find request set and its responsibility

  SELECT FA.application_name,        fr.responsibility_name program_attached_to,        frg.request_group_name,        fcp.request_set_name,...