Oracle Applications

Oracle for Beginners

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
    p_org_name varchar2(250);
    p_short_code varchar2(10);

    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;
        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;
            return null;
    end case;
when no_data_found then
    return null;
when others then
    return nvl(sqlerrm, null);

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:

Use the following to test your function:

select ztd_get_org_info_fn('967', 'CODE')
from dual;

Shameem Bauccha
28 June 2010


One Response to “Function to fetch Organization Info”

  1. Revan said

    Hi Shameem,

    Thanks a lot for sharing you valuable knowledge with us. It has really helped us in setting organisation in our project.

    Thanks and Regards,
    Revan Chavan

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s