Messages in Oracle Application


What is a message in Oracle Application?

Oracle Application uses the Message Dictionary to store translatable Error and Warning messages that can be used by any programs written in Forms, Reports, Java, or PL/SQL.

These messages mainly provide information about business rule errors, such as missing or incorrect data, and how to resolve them, warn about the consequences of intended actions, inform about the status of an application, pages, or business objects, and indicate that processes and actions are performing or are completed.

By using the messages in the Message Dictionary, you can define standard messages that you can use in all your applications(Oracle Form, Reports, OAF and ADF), provide consistency for messages within and across all your applications, define flexible messages, and change or translate the text of your messages without regenerating or recompiling your application code.

How will you create a Message?

In Application Developer Responsibility, navigate to (N) Application | Messages.

Using that screen, the new messages can be created by entering records. The contents entered in field message text become visible to the end user. The content of the message can optionally have tokens, which act as placeholders for dynamically substituted values at runtime.

You can also create messages from OAF Page. For that-

1] Login to Functional Administrator responsibility – then choose Home.

2] Choose the ‘Core Services’ Tab – then the “Messages” Sub-Menu.

The components of a Message:

Component Name Description
Name Every message must have a unique name. You should include a unique prefix that makes it easier to find your custom messages and that helps to avoid name conflicts with non-custom messages.
Language Select the language that your message is written in.
Application Select the application that the message belongs, this will usually be the custom application.
Current Message Text Message text is required. This is a brief statement of the operation attempted and the problem that occurred as a result, or information that the user needs to know. The maximum field size for messages stored in the Message Dictionary is 240 characters.
Number A unique and persistent message number can be included with each message. When displayed, the number takes the format of (Application ShortnameNumber). If the message does not have a message number, the formatted number is not displayed.
Type The message type indicates which message components are applicable, determines whether implicit logging and incident creation occurs, and determines the logging level if the message is logged.
Maximum Length Maximum number of display characters the translators can use to translate the message.
Description Description of the Message.
Alert Category This will allow user interfaces and other programs to filter exception messages based on category. The types are Product, System, Security and User.
Alert Severity This will allow user interfaces and other programs to filter exception messages based on severity. The types can be: Critical, Error or Warning.
Log Severity This group indicates the Log severity levels like: Unexpected, Error, Exception, Event, Procedure, Statement or Off.

About Tokens:

Tokens are identified in the message text by their use of & and all uppercase letters. The token values are supplied at runtime by the code that raises the message. For example, the following token &FIELD_NAME is replaced by a field when the user receives the error message on their screen:

“A Value must be entered for &FIELD_NAME.

Becomes: “A Value must be entered for PO Number.”.

Table Used by Messages Dictionary:

FND_NEW_MESSAGES stores application messages for Message Dictionary. Each row includes the application to which the message belongs, the language the message is in, the message name, the message text, and the message number. You need one row for each application message in each of the language.

APIs to Set, Retrieve, Clear the messages:

Use the Message Dictionary APIs to retrieve a Message Dictionary message. The PL/SQL methods are in the FND_MESSAGE package and the Java methods are in the messageService package. There are many functions and procedures in the package- FND_MESSAGE. However I have given the details of three most used procedures below.

procedure SET_NAME(APPLICATION in varchar2, NAME in varchar2)

In Database Server, this Sets a message name in the global area without actually retrieving the message from Message Dictionary.

procedure SET_TOKEN(TOKEN     in varchar2,

                        VALUE     in varchar2,

                        TRANSLATE in boolean default false)

This procedure defines a message token with a value. In Database Server, SET_TOKEN adds a token/value pair to the global area without actually doing the substitution. Call FND_MESSAGE.SET_TOKEN once for each token/value pair in a message.

function GET return varchar2

This function gets a translated and token substituted message from the message dictionary database. It returns NULL if the message cannot be found. If this function is called from a stored procedure on the database server side, the message is retrieved from the Message Dictionary table. If the function is called from a form or forms library, the message is retrieved from the messages file on the forms server.

How to use message in PL/SQL Concurrent Program:

fnd_message.clear;
fnd_message.set_name ('XXSCM', 'XXSCM_MANDATORY_FIELD');
fnd_message.set_token('FIELD_NAME', 'PO Number');
--Now get the final string
l_message := fnd_message.get;
--Display the message text in output of concurrent program
fnd_file.put_line(fnd_file.OUTPUT, l_message);

How to use message in Oracle Forms:

fnd_message.set_name('XXSCM', 'XXSCM_MANDATORY_FIELD');
fnd_message.set_token('FIELD_NAME', 'PO Number');
fnd_message.show;

How to use message in OA Framework Controller:

String sReturnMsg = oapagecontext.getMessage("XXSCM", " XXSCM_MANDATORY_FIELD ", new MessageToken[] {new MessageToken(" FIELD_NAME ", "PO Number") });

Example of retrieving message from the Stack:

-- setting INVALID_USER as the current message
fnd_message.set_name('FND', 'INVALID_USER');
-- setting value for token NAME for INVALID_USER message
fnd_message.set_token('NAME', 'TESTUSER');
-- saving the current message onto stack
fnd_message.push;
-- setting LOGIN_FAILED as the current message
fnd_message.set_name('FND', 'LOGIN_FAILED');
-- saving the current message onto stack
fnd_message.push;
-- poping one message out of stack and set it as the current message
fnd_message.pop;
-- get the translated and token subsituted LOGIN_FAILED message
-- then clear the current message
msg := fnd_message.get;
-- poping one message out of stack and set it as the current message
fnd_message.pop;
-- get the translated and token subsituted INVALID_USER message
-- then clear the message
msg := fnd_message.get;

FNDLOAD for Messages:

Download:

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXSCM_MANDATORY_FIELD_MSG.ldt  FND_NEW_MESSAGES APPLICATION_SHORT_NAME=’XXSCM’ MESSAGE_NAME=”XXSCM_MANDATORY_FIELD”

Upload:

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXSCM_MANDATORY_FIELD_MSG.ldt

SQL related to Oracle Application Messages:

SELECT m.message_name,
  m.message_text,
  m.message_number,
  a.application_short_name
FROM FND_NEW_MESSAGES M,
  FND_APPLICATION a
WHERE upper(m.message_text) LIKE upper('%&Enter_Message_Text%')
AND m.language_code  = 'US'
AND M.APPLICATION_ID = a.APPLICATION_ID;

SELECT m.message_name,
  m.message_text,
  m.message_number,
  a.application_short_name
FROM FND_NEW_MESSAGES M,
  FND_APPLICATION a
WHERE m.message_name LIKE '%&Enter_Message_Name%'
AND m.language_code  = 'US'
AND M.APPLICATION_ID = a.APPLICATION_ID;
Advertisements

How to Use Global Variables in Form Personalization?


Here let’s say the requirement is to run a concurrent program through a custom menu from an oracle form and in that concurrent program parameters, we need to fetch some values from the oracle form itself. In that case we need to first assign the form values to some Global Variables and then use them in the concurrent program parameters. Here are the Steps:

1] Go to Form Personalization for that Form (Use: Help > Diagnostics > Custom Code > Personalize)

2] Create one custom menu (ex. SPECIAL15) on the triggering event: WHEN-NEW-FORM-INSTANCE.

3] On the triggering event: SPECIAL15, do the following actions:

  • Select Type as ‘Property’ and select Object Type as ‘Global Variable’.
  • Give a name to the global variable in the ‘Target Object’ tab. (Ex. G_ITEM_NAME, G_ORG_CODE)
  • Select the Property Name as ‘VALUE’.
  • In the value tab- Give ‘=:Block_name.Field_name’ (Use: Help > Diagnostics > Examine). This is the value which you want to put in the global variable.

4] Select Type as ‘Builtin’ and Select Builtin Type as ‘Launch SRS Form’.

5] Select your Concurrent Program in the ‘Program Name’ tab. Please note that you need to assign the Function – ‘Requests: Submit’ to the Main Menu of the responsibility to which your concurrent program is attached.

  • Use: System Administrator > Security > Responsibility > Define to find the Menu Name.
  • Go to System Administrator > Application > Menu and add the Function – ‘Requests: Submit’ at the end and Save.
  • Also don’t forget to attach the concurrent program to proper Request Group.

6] Go to the concurrent program parameters. Select the Default Type as SQL Statement in the Validation tab and give the default values as

  • select :GLOBAL.G_ITEM_NAME from dual
  • select :GLOBAL.G_ORG_CODE from dual

7] Save and Test the functionality.

An Alternate Way:

Create a PL/SQL function in the database that calls fnd_request.submit_request and commits in an AUTONOMOUS TRANSACTION. The function returns a message to the user, with the request_id.

1] Go to Form Personalization for that Form (Use: Help > Diagnostics > Custom Code > Personalize)

2] Create one custom menu (ex. SPECIAL15) on the triggering event: WHEN-NEW-FORM-INSTANCE.

3] On the triggering event: SPECIAL15, do the following actions:

  • Define a global variable for the message (Ex. XX_CONC_PROG_RESULT)
  • Assign the above global variable the following value: =SELECT <Your Custom PL/SQL Function> from dual. You can pass parameters to the function as: Block_name.Field_name.
  • Define a message to show as =:GLOBAL.XX_CONC_PROG_RESULT

Query to find the Request Group of a Concurrent Program


Many a times we need to find out the Request Group of a Concurrent Program. In such cases the below query will be a useful one.

SELECT
  RG.APPLICATION_ID "Request Group Application ID",
  RG.REQUEST_GROUP_ID "Request Group - Group ID",
  RG.REQUEST_GROUP_NAME,
  RG.DESCRIPTION,
  rgu.unit_application_id,
  rgu.request_group_id "Request Group Unit - Group ID",
  rgu.request_unit_id,cp.concurrent_program_id,
  cp.concurrent_program_name,
  cpt.user_concurrent_program_name,
  DECODE(rgu.request_unit_type,'P','Program','S','Set',rgu.request_unit_type) "Unit Type"
FROM
  fnd_request_groups rg,
  fnd_request_group_units rgu,
  fnd_concurrent_programs cp,
  FND_CONCURRENT_PROGRAMS_TL CPT
WHERE rg.request_group_id = rgu.request_group_id
  AND rgu.request_unit_id = cp.concurrent_program_id
  AND cp.concurrent_program_id = cpt.concurrent_program_id
  AND cpt.user_concurrent_program_name =’<Your_Concurrent_Program_Name>’;

Finding SQL Command on Java Page


Here are the steps to view the SQL Statements from Java User Interface (UI) Pages. For the purpose of this exercise, we will look at the query used to retrieve Bank Accounts attached to a Customer Site.

1. Update the following Profile Option Values to YES at user level

  • FND: Diagnostics
  • Personalize Self-Service Defn

2. Go to the Java UI Page where the SQL Query executes. In this example we go to Customers > Customer Sites.

3. Click on the link ‘About this Page’ in the bottom Left Hand Side of the page.

4. Navigate to the Page Tab on the Top Left Hand Side of the page.

5. In the Business Component References Details Section (You may need to expand this section), Go to the View Objects Sub Section.

6. Find the Object that describes the piece of information that you want to find the query for. In this case it’s Bank Accounts. The relevant Object has been highlighted (I searched the HTML for bank to find this).

7. Click on the link.

The full Query used can be cut and pasted into a SQL Editor and the query run.

Note: You will have to find the Bind Variables passed to the query in order to do this!! This may be found by looking up one of the previous queries.

Queries for Value Sets


Queries for Value Sets

Value Sets based on table:
This Query gives details of value sets that are based on a oracle application tables.
select ffvs.flex_value_set_id ,
    ffvs.flex_value_set_name ,
    ffvs.description set_description ,
    ffvs.validation_type,
    ffvt.value_column_name ,
    ffvt.meaning_column_name ,
    ffvt.id_column_name ,
    ffvt.application_table_name ,
    ffvt.additional_where_clause
FROM fnd_flex_value_sets ffvs ,
    fnd_flex_validation_tables ffvt
WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id;
Independent Value set Details:
This query gives details of independent FND Value sets i.e. Values are static and these are not derived from any application table.
SELECT ffvs.flex_value_set_id ,
    ffvs.flex_value_set_name ,
    ffvs.description set_description ,
    ffvs.validation_type,
    ffv.flex_value_id ,
    ffv.flex_value ,
    ffvt.flex_value_meaning ,
    ffvt.description value_description
FROM fnd_flex_value_sets ffvs ,
    fnd_flex_values ffv ,
    fnd_flex_values_tl ffvt
WHERE
    ffvs.flex_value_set_id     = ffv.flex_value_set_id
    and ffv.flex_value_id      = ffvt.flex_value_id
    AND ffvt.language          = USERENV('LANG');
Thanks
Dibyajyoti Koch
Have a nice Day!

Know your Concurrent Program’s Performance


Know your Concurrent Program’s Performance

The below query will give you the time taken to execute the concurrent Programs with the latest concurrent programs with least execution time comes first.

select
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' HOURS ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' MINUTES ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' SECS ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
      f.status_code
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      AND pt.language = USERENV('Lang')
      and f.actual_start_date is not null
order by
      f.actual_start_date desc,
      f.actual_completion_date-f.actual_start_date ;

API’s to Create User,Reset Password and Add Responsibility


API’s to Create User,Reset Password and Add Responsibility

I have created few queries using Oracle provided package:’FND_USER_PKG’. These queries might be very useful when you donot have the Oracle Apps front end access or you like to get in done through backend.

Using the below query, you can create a User in Oracle application.Just pass username, password and email id as parameters and it will create a user.

declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_password varchar2(30):='&Enter_Password';
v_session_id integer := userenv('sessionid');
v_email varchar2(30):=upper('&Enter_Email_Id');
begin
  fnd_user_pkg.createuser (
  x_user_name => v_user_name,
  x_owner => null,
  x_unencrypted_password => v_password,
  x_session_number => v_session_id,
  x_start_date => sysdate,
  x_end_date => null,
  x_email_address => v_email
  );
  commit;
  DBMS_OUTPUT.put_line ('User:'||v_user_name||'Created Successfully');
EXCEPTION
when others then
  DBMS_OUTPUT.put_line ('Unable to create User due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  ROLLBACK;
end;

May a times we forgot the apps password. Then you can use the below query to resent the password just in few seconds.

declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_new_password varchar2(30):='&Enter_New_Password';
v_status boolean;
begin
 v_status:= fnd_user_pkg.ChangePassword (
    username => v_user_name,
    newpassword => v_new_password
  );
  if v_status =true then
  dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
  commit;
  else
  DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  rollback;
  END if;
end;

Use the below query to add a responsibility to a user. The advantage here is that you donot require system administrator responsibility access to add a responsibility.

declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_resp varchar2(30):='&Enter_Responsibility';
v_resp_key varchar2(30);
v_app_short_name varchar2(50);
begin
  select
    r.responsibility_key ,
    a.application_short_name
  into v_resp_key,v_app_short_name
  from fnd_responsibility_vl r,
    fnd_application_vl a
  where
    r.application_id =a.application_id
    and upper(r.responsibility_name) = upper(v_resp);

  fnd_user_pkg.AddResp (
  username => v_user_name,
  resp_app => v_app_short_name,
  resp_key => v_resp_key,
  security_group => 'STANDARD',
  description => null,
  start_date => sysdate,
  end_date => null
  );
  commit;
  DBMS_OUTPUT.put_line ('Responsibility:'||v_resp||' '||'is added to the User:'||v_user_name);
EXCEPTION
when others then
  DBMS_OUTPUT.put_line ('Unable to add the responsibility due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  rollback;
end;

Alerts in Oracle Application


Alerts in Oracle Application

Oracle Alert facilitates the flow of information within your organization by letting you create entities called alerts to monitor your business information and to notify you of the information you want. You can define one of two types of alerts: an event alert or a periodic alert.

Event Alert:

An event alert immediately notifies you of activity in your database as it occurs. When you create an event alert, you specify the following:

• A database event that you want to monitor, that is, an insert and/or an update to a specific database table.

• A SQL Select statement that retrieves specific database information as a result of the database event.

• Actions that you want Oracle Alert to perform as a result of the database event. An action can entail sending someone an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set.

Periodic Alert:

A periodic alert, on the other hand, checks the database for information according to a schedule you define. In a periodic alert specify the following:

• A SQL Select statement that retrieves specific database information.

• The frequency that you want the periodic alert to run the SQL statement.

• Actions that  Oracle Alert to perform once it runs the SQL statement. An action can entail sending the retrieved information to someone in an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. We include all the actions we want Oracle Alert to perform, in an action set.

Navigation in Oracle Apps to define an alert:

Go to “Alert Manager” Responsibility
Alert >> Define

Transfer Alert from one instance/database to other:

Go to “Alert Manager” Responsibility
Alert >> Define
Go to “Tools” Menu on top
Click on “Transfer Alert”
Enter source and destination fields and click Transfer.

How to define an periodic alert:

  1. Go to Alert Manager > Alert > Define.
  2. Select the ‘Periodic’ Tab.
  3. Enter the name of the application that owns the alert in the Application field.
  4. Name the alert (up to 50 characters), and give it a meaningful description (up to 240 characters).
  5. Check Enabled to enable your periodic alert.
  6. Set the frequency for the periodic alert to any of the following:
  • On Demand
  • On Day of the Month
  • On Day of the Week
  • Every N Calendar Days
  • Every Day
  • Every Other Day
  • Every N Business Days
  • Every Business Day
  • Every Other Business Day

Enter a SQL Select statement that retrieves all the data your alert needs to perform the actions you plan to define. Your periodic alert Select statement must include an INTO clause that contains one output for each column selected by your Select statement.

Here is an example of a periodic alert Select statement that looks for users who have not changed their passwords within the number of days specified by the value in :THRESHOLD_DAYS.:

SELECT user_name,
password_date,
:THRESHOLD_DAYS
INTO &USER,
&LASTDATE,
&NUMDAYS
FROM fnd_user
WHERE sysdate = NVL(password_date,
sysdate) + :THRESHOLD_DAYS
ORDER BY user_name

Although Oracle Alert does not support PL/SQL statements as the alert SQL statement definition, you can create a PL/SQL packaged function that contains PL/SQL logic and enter a SQL Select statement that calls that packaged function.

You can verify the accuracy and effectiveness of your Select statement. Choose Verify to parse your Select statement and display the result in a Note window.

Choose Run to execute the Select statement in one of your application’s Oracle IDs, and display the number of rows returned in a Note window.

Once you are satisfied with the SQL statement, save your work.

Specifying Alert Details:

Once you define an event or periodic alert in the Alerts window, you need to display to the Alert Details window to complete the alert definition. The Alert Details window includes information such as which Application installations you want the alert to run against, what default values you want your inputs variables to use, and what additional characteristics you want your output variables to have.

Creating Alert Actions:

After you define your alert you need to create the actions you want your alert to perform. There are four types of actions you can create:

• message actions

• concurrent program actions

• operating script actions

• SQL statement script actions

Choose Actions

Enter a name (up to 80 characters) and description (up to 240 characters) for your alert action.

Select a level for your action: Detail, Summary, or No Exception.

Choose Action Details to display the Action Details window.

Select the type of action you want to create in the Action Type field

Creating an Event Alert:

Specify the name of the application and the database table that you want Oracle Alert to monitor.

Note: You cannot use a view as the event table for your alert.

Check After Insert and/or After Update if you want to run your event alert when an application user inserts and/or updates a row in the database table.

Specify a value in the Keep _ Days field to indicate the number of days of exceptions, actions, and response actions history you want to keep for this alert.

Specify a value in the End Date field if you want to disable your alert by a certain date.

Important Alert Tables:

  • ALR_ALERTS
  • ALR_ACTIONS
  • ALR_ACTION_SETS
  • ALR_ACTION_SET_INPUTS
  • ALR_ACTION_SET_OUTPUTS
  • ALR_ACTION_SET_MEMBERS
  • ALR_ALERT_CHECKS
  • ALR_ALERT_INPUTS
  • ALR_ALERT_OUTPUTS
  • ALR_ACTION_SET_CHECKS
  • ALR_RESPONSE_SETS
  • ALR_RESPONSE_ACTIONS
  • ALR_VALID_RESONSES

Oracle Alert uses the following internal views:

  •  ALR_ALERT_ACTIONS_VIEW
  •  ALR_ALERT_HISTORY_VIEW
  •  ALR_CHECK_ACTION_HISTORY_VIEW
  •  ALR_INSTALLATIONS_VIEW
  •  ALR_PERIODIC_ALERTS_VIEW
  •  ALR_RESPONSE_ACTIONS_VIEW
  •  ALR_SCHEDULED_PROGRAMS
  •  ALR_VARIABLES_AND_OUTPUTS

For complete details you can refer Oracle Alert User’s Guide

KEY FLEX FIELDS (KFF)


KEY FLEX FIELDS (KFF)

KFF are used to capture mandatory or Key Business information of the Organization. Each Key Flex Field is having its won base Table.

A key flexfield is a field made up of segments, each of which has both a value and a meaning. You can think of a key flexfield as an “intelligent” field that your business can use to store information represented as codes.

Most organizations use codes to identify general ledger accounts, part numbers, and other business entities. Each segment in the code represents a characteristic of the entity. A combination of segment values, also known as a key flexfield code combination, uniquely describes a business entity stored in a key flexfield.

The organization decides the following four basic information for each key flexfield:

  1. How many segments an entity has?
  2. What each segment means?
  3. What values each segment can have?
  4. What each segment value means?

Few Key Flexfields in different oracle modules are:

Module: GL

  1. Accounting

Module: HRMS

  1. Job
  2. Position
  3. Grade
  4. Personal Analysis   

Module: Inventory

  1. Account Aliases
  2. Item Catalogs
  3. Item Categories
  4. Sales Orders
  5. Stock Locations
  6. System Items

Module: Fixed Assets

  1. Asset
  2. Category
  3. Location

Module: AR

  1. Sales Tax Location
  2. Territory

Note:

Flexfields consists of Structures > Structures consists of Segments > Segments consists of Value Set >Value Set consists of Parameters.

Main Tables:

FND_ID_FLEXS:

This table captures the information of all the Key FlexFields. The main columns in this table are:

  • APPLICATION_ID ‐ Column consists of Application ID
  • ID_FLEX_CODE ‐ Column KFF Code (like ‘GL#’, ‘AR#’ etc.)
  • ID_FLEX_NAME  –  KFF Name (like ‘Accounting Flexfield’, ‘Category Flexfield’..etc.)
  • APPLICATION_TABLE_NAME – Name of combination table (like ‘GL_CODE_COMBINATIONS’ , ‘FA_LOCATIONS’ etc.)

FND_ID_FLEX_STRUCTURES:

This table stores structure information about key Flexfields. Each Structure is uniquely identified by

  • APPLICATION_ID – Module Code
  • ID_FLEX_CODE  – Code of KFF
  • ID_FLEX_NUM – Number of a Structure

FND_ID_FLEX_SEGMENTS:

It captures the information of Segments. Each Segment is Uniquely identified by

  • APPLICATION_ID – Module Code
  • ID_FLEX_CODE – Key Flexfield code
  • ID_FLEX_NUM – Key flexfield structure number
  • SEG_NUM – Segment number
  • FLEX_VALUE_SET_ID – Flexfield value set identifier

FND_FLEX_VALUE_SETS:

This table captures the information of each Segment’s Value Set. Each Value Set is Uniquely identified by FLEX_VALUE_SET_ID as Foreign Key of FND_ID_FLEX_SEGMENTS Table.

FND_FLEX_VALUES:

This table captures the information each Value codes of a Value Set of a Segment. Each Value Code is uniquely identified by

  • FLEX_VALUE_SET_ID
  • FLEX_VALUE_ID

FND_FLEX_VALUES_TL:

This table captures the information of each Value Description of a Value Set of a Segment. Each Value Description is uniquely identified by FLEX_VALUE_ID.

Query:

select  FIF.APPLICATION_ID  ,
        FIF.ID_FLEX_CODE    ,
        FIF.ID_FLEX_NAME    ,
        FIF.APPLICATION_TABLE_NAME ,
        FIF.DESCRIPTION     ,
        FIFS.ID_FLEX_NUM    ,
        FIFS.ID_FLEX_STRUCTURE_CODE  ,
        FIFSE.SEGMENT_NAME,
        FIFSE.SEGMENT_NUM,
        FIFSE.FLEX_VALUE_SET_ID
from    FND_ID_FLEXS FIF    ,
        FND_ID_FLEX_STRUCTURES FIFS ,
        FND_ID_FLEX_SEGMENTS FIFSE
where   FIF.APPLICATION_ID = FIFS.APPLICATION_ID
and     FIF.ID_FLEX_CODE   = FIFS.ID_FLEX_CODE
and     FIFSE.APPLICATION_ID = FIF.APPLICATION_ID
and     FIFSE.ID_FLEX_CODE = FIF.ID_FLEX_CODE
and     FIFSE.ID_FLEX_NUM = FIFS.ID_FLEX_NUM
and     FIF.ID_FLEX_CODE LIKE 'GL#'
and     FIF.ID_FLEX_NAME LIKE 'Accounting Flexfield';

Steps required to register a New Key Flexfield (KFF):

1] Create a KFF Table in Module Specific Schema.

2] Create a Public Synonym in APPS Schema.

3] Register the Table with AOL Module.

Go to Application Developer > Functions > Application > Database > Table

We can also register a Table using Application DBA Data Definitions (AD_DD) Package from the Back End.

procedure register_table
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_tab_type in varchar2,
p_next_extent in number default 512,
p_pct_free in number default 10,
p_pct_used in number default 70);

procedure register_column
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2,
p_col_seq in number,
p_col_type in varchar2,
p_col_width in number,
p_nullable in varchar2,
p_translate in varchar2,
p_precision in number default null,
p_scale in number default null);

You can also use the AD_DD API to delete the registrations of tables and columns from Oracle Application Object Library tables.

4] Register the KFF with AOL Module.

Go to Application Developer > FlexField > Key > Register

FNDLOAD Examples


FNDLOAD Examples

Here are few examples of FNDLOAD.

Concurrent Programs:

Source:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCPNAME.ldt PROGRAM APPLICATION_SHORT_NAME=”XXCUST” CONCURRENT_PROGRAM_NAME=”XXCPNAME”

Target:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCPNAME.ldt

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpprog.lct XXCPNAME.ldt PROGRAM CONCURRENT_PROGRAM_NAME=”XXCPNAME” APPLICATION_SHORT_NAME=”XXCUST”

Responsibilities:

Source:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt FND_RESPONSIBILITY RESP_KEY=”XXRESNAME”

Target:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt FND_RESPONSIBILITY RESP_KEY=”XXRESNAME” APPLICATION_SHORT_NAME=”XXCUST”

Request Groups:

Source:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”XXRQGNAME” APPLICATION_SHORT_NAME=”XXCUST”

Target:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”XXRQGNAME” APPLICATION_SHORT_NAME=”XXCUST”

Request Sets:

Source:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSNAME.ldt REQ_SET REQUEST_SET_NAME=”XXRQSNAME”

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSLNAME.ldt REQ_SET_LINKS REQUEST_SET_NAME=”XXRQSNAME”

Target:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSNAME.ldt

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSLNAME.ldt

Forms:

Source:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXFRMNAME.ldt FORM APPLICATION_SHORT_NAME=”XXCUST” FORM_NAME=”XXFRMNAME”

Target:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXFRMNAME.ldt

Functions:

Source:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXFUNNAME.ldt FUNCTION FUNC_APP_SHORT_NAME=”XXCUST” FUNCTION_NAME=”XXFUNNAME”

Target:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXFUNNAME.ldt

Menus:

Source:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXMNNAME.ldt MENU MENU_NAME=”XXMNNAME”

Target:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXMNNAME.ldt

Profile Options:

Source:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt PROFILE PROFILE_NAME=”XXPRNAME” APPLICATION_SHORT_NAME=”XXCUST”

Target:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt PROFILE PROFILE_NAME=” XXPRNAME” APPLICATION_SHORT_NAME=”XXCUST”

Lookups:

Source:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXLKPNAME.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=”XXCUST” LOOKUP_TYPE=”XXLKPNAME”

Target:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXLKPNAME.ldt

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/aflvmlu.lct XXLKPNAME.ldt FND_LOOKUP_TYPE LOOKUP_TYPE=”XXLKPNAME” APPLICATION_SHORT_NAME=”XXCUST”

Value Sets:

Source:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXVALSNAME.ldt VALUE_SET FLEX_VALUE_SET_NAME=”XXVALSNAME”

Target:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XXVALSNAME.ldt

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afffload.lct XXVALSNAME.ldt VALUE_SET FLEX_VALUE_SET_NAME=”XXVALSNAME” APPLICATION_SHORT_NAME=”XXCUST”

Descriptive Flex-fields:

Source:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXDFFNAME.ldt DESC_FLEX P_LEVEL=’COL_ALL:REF_ALL:CTX_ONE:SEG_ALL’ APPLICATION_SHORT_NAME=”PN” DESCRIPTIVE_FLEXFIELD_NAME=”PN_LEASE_DETAILS” P_CONTEXT_CODE=”Global Data Elements”

Target:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XXDFFNAME.ldt

Key Flex-fields:

Source:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXKFFNAME.ldt KEY_FLEX P_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’ APPLICATION_SHORT_NAME=”FND” ID_FLEX_CODE=”key flex code” P_STRUCTURE_CODE=”structure name”

Target:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XXKFFNAME.ldt

Form Personalization:

Source:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XXFPNAME.ldt FND_FORM_CUSTOM_RULES function_name=”XXFPNAME”

Target:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XXFPNAME.ldt

FND Users:

Source:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XXUSERNAME.ldt FND_USER USER_NAME=’XXUSERNAME’

Target:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XXUSERNAME.ldt

Printer Styles:

Source:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct XXPRSTYLE.ldt STYLE PRINTER_STYLE_NAME=”XXPRSTYLE”

Target:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y UPLOAD $FND_TOP/patch/115/import/afcppstl.lct XXPRSTYLE.ldt

Data Definitions for XML Publisher Report Template:

$FND_TOP/bin/FNDLOAD username/password@database 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXBRPRPOPRINT_XML_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=’XXCUST’ DATA_SOURCE_CODE=XXBRPRPOPRINT_XML’

$FND_TOP/bin/FNDLOAD username/password@database  0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXBRPRPOPRINT_XML_DD.ldt