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