Oracle Applications

Oracle for Beginners

Archive for the ‘System Administrator’ Category

System Administrator

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 »

Function to fetch Employee information

Posted by shameemakhtar on June 29, 2010



CREATE OR REPLACE function ztd_get_employee_info_fn 
(
    p_emp_id in varchar2,
    p_choice in varchar2
)
return varchar2
is
    p_emp_num varchar2(250);
    p_emp_name varchar2(250);
    p_emp_email varchar2(250);
begin

    select employee_number, 
           full_name, 
           email_address
    into p_emp_num,
         p_emp_name,
         p_emp_email
    from per_people_f
    where person_id = p_emp_id;
    
    case
        when upper(p_choice) not in ('NUM', 'NAME', 'EMAIL') then
            return 'ORA-user error: Wrong Parameter';
        when upper(p_choice) = 'NUM' then
            return p_emp_num;
        when upper(p_choice) = 'NAME' then
            return p_emp_name;
        when upper(p_choice) = 'EMAIL' then
            return p_emp_email;            
    end case;
    
exception 
when no_data_found then
    return null;
when others then
    return nvl(sqlerrm, null);
end;
/
commit;

The above function takes in two parameters, namely employee_id and a parameter for choice. Based on the choice, the function will return either the employee number, his full name or his email address.

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. The additional parameter ‘p_choice’ allows me the flexibility of using the same function to return different columns from the same select statement as required. You could have added more choices to enable you to return other fields. This gives me two advantage: I do not need to build a separate function for each and it simplifies my joins in eventual select statements as I have one table less to join.
b. Instead of making p_choice a number, I have made it a character. You can argue that I could have put remarks besides each number, but as preferred varchar2 as this makes its usage more intuitive.
c. I used the standard function ‘upper’ to further allows for any combination of cases when passing parameter p_choice.
d. Before checking for actual cases, I check whether there is an error in entering parameters
e. In case of no data found, I am returning null instead of the error code. I am returning the error messages in case of other errors. You could have opted otherwise.

I am using this structure and logic to build a series of simple functions that will allow me simplifies my eventual queries in the future.

Table Used:
per_people_f (You can use per_people_x as well).

You can check your function as follows:

select ztd_get_employee_info_fn(4018, 'NUM')
from dual;

Shameem Bauccha
28 June 2010

Posted in Core HR, Human Resource Management, Pl/Sql Functions, Scripts, System Administration, System Administrator, Technical | Tagged: , , | Leave a Comment »

Function to fetch User Details

Posted by shameemakhtar on June 29, 2010


create or replace function ztd_get_user_fn
(
p_user_id in varchar2
)
return varchar2
is
p_user_name varchar2(100);
begin
select user_name
into p_user_name
from fnd_user
where user_id = p_user_id;
return p_user_name;

exception
when others then
return nvl(sqlerrm, null);
end;

commit;

This function will return the username when you pass in the user_id. In case no data is found or any other error is encountered, you will receive the appropriate error code.

Tables used:
fnd_user

You can use the following select statement to test your function:

select ztd_get_user_fn(14061)
from dual;

Shameem Bauccha
28 June 2010

Posted in Pl/Sql Functions, Scripts, System Administration, System Administrator, Technical | Tagged: , , , | Leave a Comment »

View for Chart of Accounts KFF

Posted by shameemakhtar on October 19, 2009


The script below allows you creates a view for your chart of accounts.
Modify to suit your requirements. Note that the value set name of each segment is passed as parameter.

CREATE OR REPLACE VIEW ABH_GL_ACC_CONCAT_SEGMENTS
AS
SELECT cc.code_combination_id,
 cc.chart_of_accounts_id,
 cc.detail_posting_allowed_flag post,
 cc.detail_budgeting_allowed_flag budget,
 cc.account_type,
 cc.show_account_type,
 cc.enabled_flag,
 cc.summary_flag,
 --segment: segments used in COA definition
 cc.segment1,
 cc.segment2,
 cc.segment3,
 cc.segment4,
 cc.segment5,
 cc.segment6,
 concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(segment1, '-'), segment2),'-'), segment3), '-'), segment4), '-'), segment5), '-'), segment6) account_comb,
 get_flex_vs_desc('ABH Company', cc.segment1, '') Company,
 get_flex_vs_desc('ABH Cost Center', cc.segment2, '') "Cost Center",
 get_flex_vs_desc('ABH Account', cc.segment3, '') "Account",
 get_flex_vs_desc('ABH Sub Account', cc.segment4, cc.segment3) "Sub Account",
 get_flex_vs_desc('ABH Location', cc.segment5, '') "Location",
 get_flex_vs_desc('ABH Entity-Services', cc.segment6, '') "Entity-Services"
FROM GL_CODE_COMBINATIONS_V cc

Refer to document ‘Fetching Key Flexfield Value Description‘ for get_flex_vs_desc.
Shameem Bauccha

19 October 2009

Posted in General Ledger, Oracle Apps, Scripts, System Administrator, Technical | Tagged: , , , | 3 Comments »

Fetching Flexfield Description

Posted by shameemakhtar on October 19, 2009


CREATE OR REPLACE FUNCTION APPS.GET_FLEX_VS_DESC (P_VALUE_SET_NAME VARCHAR2, P_FLEX_VALUE VARCHAR2, P_PARENT_FLEX_VALUE VARCHAR2)
RETURN VARCHAR2 IS
DESCRIP VARCHAR2(50);
BEGIN
IF P_PARENT_FLEX_VALUE IS NULL
THEN
/*** In case the value set is independent ***/
SELECT DISTINCT DESCRIPTION
INTO DESCRIP
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID =
(
SELECT FLEX_VALUE_SET_ID
FROM FND_FLEX_VALUE_SETS
WHERE FLEX_VALUE_SET_NAME = P_VALUE_SET_NAME
AND FLEX_VALUE = P_FLEX_VALUE
--AND PARENT_FLEX_VALUE_LOW = P_PARENT_FLEX_VALUE
);
ELSE
/*** If the value set is dependent on another value set ***/
SELECT DISTINCT DESCRIPTION
INTO DESCRIP
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID =
(
SELECT FLEX_VALUE_SET_ID
FROM FND_FLEX_VALUE_SETS
WHERE FLEX_VALUE_SET_NAME = P_VALUE_SET_NAME
AND FLEX_VALUE = P_FLEX_VALUE
AND PARENT_FLEX_VALUE_LOW = P_PARENT_FLEX_VALUE
);
END IF;
RETURN NVL(DESCRIP, 'ERROR');
END;
/

Shameem Bauccha

19 October 2009

Posted in Oracle Apps, Scripts, System Administration, System Administrator, Technical | Tagged: , , , | Leave a Comment »

Key Flexfields

Posted by shameemakhtar on June 23, 2009


Platform: 11.5.10, R12

Key Flexfield Architecture

Key Flexfield is a very important concept in Oracle. It allows you to define Key Structures in each Module. This gives you the flexibility to model your objects according to your requirements. Some examples of flexfields are as follow:

Accounting Flexfield in General Ledger.

Job, Position and Grade Key Flexfield in Human Resources.

Key Flexfield Architecture

Key Flexfield Architecture

A flexfield is a structure that you define for a Flexfield Title. The Flexfield Structures are registered within an application.

A structure will compose of one or more segments. Each segment will be attached to a value set which you would have defined. If required you will qualify the segment in question.

Define Key Flexfield Structure

System Administrator –> Flexfield –> Segments

Note: KFFs are accessible from other Modules as well.

Key Flexfield Segments

Key Flexfield Segments

Query for the Key Flexfield in question. As an example I have queried the ‘Training Resources’ KFF.

Training Resources KFF

Training Resources KFF

Add your structure, then click on ‘Segments’ to define the segments.

Segments Summary

Segments Summary

Click on ‘Value Set’. Define your value set. The Value Set determines what values will be available for the segment in question. Specify the ‘Value Validation’.  Save the value set, close the form and attach it to the segment as shown above.

We have various ways of validating the values.

(Detailed document to follow on Value Sets)

Define Value Sets

Define Value Sets

Define Value Sets

In this case, the validation is based on a table. Click on ‘Edit Information’ to define it.

Validation Table Information

Validation Table Information

When you are done, click on ‘Open’ on the ‘Segment Summary’ Form. Specify whether it is required. By default all segments are marked as required.

Segments

Segments

Flexfield Qualifiers

Click on ‘Flexfield Qualifiers’ to qualify the segment. This is not valid for all Key Flexfields. Accounting Flexfield has Flexfield Qualifiers and is illustrated below.

Flexfield Qualifiers

Flexfield Qualifiers

Once you are done with all the segments close the ‘Segments Summary’ Form to return to the ‘Key Flexfield Segments’ window.

Compile Key Flexfield Structure

Compile KFF

Compile KFF

To be able to use the KFF, you need to freeze the structure. To freeze the structure, check the ‘Freeze Flexfield Definition’ and click on ‘Compile’. A request is launched to compile the structure. The structure is now available in your application.

Note: The ‘Allow Dynamic Inserts’ checkbox enables KFF values to be generated while using the application. If you don’t allow dynamic insert, then you need to define all the valid value combinations for your structure before you can use these values in your application.

Shameem Bauccha

24 June 2009

Posted in Oracle Apps, System Administration, System Administrator | Tagged: , , , , , , , , | 9 Comments »

Define System Profile Values

Posted by shameemakhtar on June 22, 2009


Introduction

System profiles direct how your application behaves, what default values it takes or what restrictions are in place.

Each module has its list of profile options which you have to define for your application to work properly.

You can specify the options at various levels. It is important to identify correctly at which level to set the profile value.  See the precedence pyramid below:

System Profile Precedence Pyramid

A profile set at user level will take priority over the same profile set at responsibility level or site level for example. Be cautious when setting profile options as site level as in this may affect other people who may be working on the same instance.

Define Profile Values

System Administrator –> Profile –> System

Find System Profile Values

Find System Profile Values

The ‘Site’ and ‘Profiles with No Valuescheck boxes on the Profile page are selected by default. Therefore, when a search is performed and a profile option is selected, the system lists the values defined only at site level. To see the values defined at all the levels, uncheck these checkboxes before performing a search and selecting a profile option.

Choose the ‘Levels’, specify the ‘Profile’ you wish to set and click on ‘Find’.

System Profile Values

System Profile Values

In case the profile cannot be set at certain level, it will be greyed out even if you specify a profile option for it.

Type in or lookup up the appropriate option and save.

That’s it, your profile option value is set!

Shameem Bauccha

22 June 2009

Posted in Oracle Apps, System Administration, System Administrator | Tagged: , , , , , | 10 Comments »