Oracle Applications

Oracle for Beginners

Posts Tagged ‘fetch organization detail’

Function to fetch Organization Info

Posted by shameemakhtar on June 29, 2010



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

Advertisements

Posted in Inventory, Pl/Sql Functions, Procurement / Supply Chain, Scripts, Supply Chain Management, System Administration, System Administrator, Technical | Tagged: , , , , | 1 Comment »