shameemakhtar
Ajmal Budulla
CREATE OR REPLACE function ztd_get_org_info_fn
(
p_org_id in number,
p_choice in varchar2
)
return varchar2
is
p_org_name varchar2(250);
p_short_code varchar2(10);
begin
select name
into p_org_name
from hr_all_organization_units
where organization_id = p_org_id;
select organization_code
into p_short_code
from mtl_parameters
where master_organization_id = '968'
and organization_id = p_org_id;
case
when upper(p_choice) not in ('NAME', 'DESC') then
return 'ORA-user error: Wrong Parameter';
when p_choice = 'NAME' then
return p_org_name;
when p_choice = 'CODE' then
return p_short_code;
else
return null;
end case;
exception
when no_data_found then
return null;
when others then
return nvl(sqlerrm, null);
end;
/
The above function takes in two parameters, namely organization_id and a parameter for choice. Based on the choice, the function will return either the organization name or the organization short code from two different select statements.
In case no data is found, it will return null, and in case of error, it will return the error message.
Note the following:
a. I am hard-coding the Master Organization Id in this case since I am using writing this script for one of the Operating Units in a multi-Org structure. Since I can have only one Master Organization per Operating Unit, I have hard-coded this value to restrict my Organizations and to keep the code simple.
Tables used:
hr_all_organization_units
mtl_parameters
Use the following to test your function:
select ztd_get_org_info_fn('967', 'CODE')
from dual;
Shameem Bauccha
28 June 2010
