, , , ,

R12 – How to extract Profile Option Values using Query

In Oracle E-Business Suite profile options can be set on several levels:
  • Site
  • Application
  • Responsibility
  • Server
  • Server with Responsibility
  • Organization
  • User

When needed one and the same profile option can be assigned to different levels. For example, when you implement Global Security Profiles to create access control on Operation Units – every responsibility for an Operating Unit may need a setting for profile options MO: Security Profile and HR: Security Profile. In the form which is used to set profile options all those different responsibilities can’t be seen at once.

In that case I use the SQL statement below to quickly provide me a list of the values of a profile option for all levels.  

The profile option name (column profile_option_name from table applsys.fnd_profile_options) can be found within the definition of the profile itself through responsibility Application Developer – menu Profile.


Here’s the SQL to provide you the values on all levels of a specific profile.

SELECT
    SUBSTR(e.profile_option_name,1,25) INTERNAL_NAME,
    SUBSTR(pot.user_profile_option_name,1,60) NAME_IN_FORMS,
    DECODE(a.level_id,10001,’Site’,10002,’Application’,10003,’Resp’,
    10004,’User’,10005,’Server’,10007,’Server+Resp’,a.level_id) LEVELl,
    DECODE(a.level_id,10001,’Site’,10002,c.application_short_name,
    10003,b.responsibility_name,10004,d.user_name,10005,n.node_name,
    10007,m.node_name||’ + ‘||b.responsibility_name,a.level_id) LEVEL_VALUE,
    NVL(a.profile_option_value,’Is Null’) VALUE,
    to_char(a.last_update_date, ‘DD-MON-YYYY HH24:MI’) LAST_UPDATE_DATE,
    dd.USER_NAME LAST_UPDATE_USER
FROM
    applsys.fnd_profile_option_values a,
    applsys.fnd_responsibility_tl b,
    applsys.fnd_application c,
    applsys.fnd_user d,
    applsys.fnd_profile_options e,
    applsys.fnd_nodes n,
    applsys.fnd_nodes m,
    applsys.fnd_responsibility_tl x,
    applsys.fnd_user dd,
    applsys.fnd_profile_options_tl pot
WHERE
    e.profile_option_name = ‘XLA_MO_SECURITY_PROFILE_LEVEL’    AND e.PROFILE_OPTION_NAME = pot.profile_option_name (+)
    AND e.profile_option_id = a.profile_option_id (+)
    AND a.level_value = b.responsibility_id (+)
    AND a.level_value = c.application_id (+)
    AND a.level_value = d.user_id (+)
    AND a.level_value = n.node_id (+)
    AND a.LEVEL_VALUE_APPLICATION_ID = x.responsibility_id (+)
    AND a.level_value2 = m.node_id (+)
    AND a.LAST_UPDATED_BY = dd.USER_ID (+)
    AND pot.LANGUAGE = ‘US’
ORDER BY
    e.profile_option_name

/by
, ,

R12 – How to Create Category and Category Set in Oracle Inventory

Categories are the method by which the items in inventory can be separated logically and functionally for planning, purchasing and other activities.You can use categories and category sets to group your items for various reports and programs. A category is a logical classification of items that have similar characteristics. A category set is a distinct grouping scheme and consists of categories. The flexibility of category sets allows you to report and inquire on items in a way that best suits your needs. This article will describe how to create categories and category set in oracle inventory.
Suppose we need a category called ‘INV_COLORS’. We can define multiple colors in this category and then assign this category to an item.
Example:
  1. Item1 —- Black
  2. Item2 —- Red
  3. Item3 —- Green
  4. Item4 —- Orange
1] First we need to create a value set to hold these colors.
Navigation > Setup: Flexfields: Validation: Sets
Validation type Select: Independent
2] Next we need to enter our values in the INVENTORY_COLOR valueset
RED, GREEN, BLUE, BLACK, and ORANGE
Navigation -> Setup: Flexfields: Validation: Values
Save and close the Screen.
3] Now we need to create a KFF Structure
Navigation Setup: Flexfields: Key: Segments
Create the structure name: In the “Code” field enter INV_COLORS
4] Click on the “Segments” button.
  • Enter the “Number” field: 10
  • Enter the Name field: Color
  • Enter the “Window Prompt”: Color (This value will appear on the screen)
  • Enter the “Column” field: Segment1 (you can choose any column)
Save and exit the form.
5] Check the Freeze flex field Definition, the following warning will appear.
Click OK.
6] The “Compile” button is now available to be selected. Click on the compile button.
Click Ok
Close the form.
7] Go to View -> Request and Verify that the new Category flexfield compiled successfully.
8] The new structure is ready for use. Now let’s create a category.
Navigation : Setup: Items: Categories: Category Codes
  • Enter the structure name: INV_COLORES
  • Enter the category: BLACK
  • (Note the form does not provide an LOV for the categories. You will need to use edit symbol at the top of the page or “ e “ to bring up the lov)
  • Enter the description.
9] Next we create our category set.
Navigation Setup: Items: Categories: Category Sets
  • Fill in the category set Name: INV_COLORS_SET
  • The description: Inventory color set
  • The Flex Structure: INV_COLORS
  • The Controlled: Org Level
  • Default Category: BLACK
10] After creating the category set, we can assign it to any items.
/by
, , , , , ,

How to Create and Register a Concurrent Program using SQL*Loader

When building customization’s for the Oracle E-Business Suite it might be needed to load data from external sources into specific tables. Tables might be seeded tables, i.e. Open Interface tables, or custom tables which you’ve added to a special customization’s database schema.

To load data from external sources into Oracle eBS you might consider using SQL*Loader to accomplish this. SQL*Loader is using comma seperated (csv) files with data and a so called control file (ctl). The control file tells the system how to import the csv file with data. The control file describes the table and columns to be loaded, what the seperator is of the incoming file etc. Next the SQL*Loader program exports a log file to give an overview of the process, a bad file of records that caused errors in the process, and a discard file for records that were not selected during the load.


Starting a SQL*Loader load can be done by command line by executing the below:

sqlldr db_user/db_password@host:port/sid control=control_file_name.ctl log=log_file_name.log

SQL*Loader can also be executed by starting a special concurrent program which you can create in Oracle E-Business Suite. Below the steps how to do this.

1) First of all you need to have a csv file with data – the csv can also contain header information. The header in a csv file can be skipped by adding a special parameter in the control file. Take note of the columns in the csv file.

2) You need a control file – for example see the below control file. There are a big number of commands you can use in the control file to completely have control on how data gets loaded into tables. If you want to know more than contact me on this topic.

SKIP = 1
LOAD DATA
INFILE ‘data_file_name.csv’
BADFILE ‘data_file_name.bad’
REPLACE INTO TABLE XXX.YOUR_CUSTOM_TABLE
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘
TRAILING NULLCOLS
(
COLUMN1,COLUMN2,COLUMN3,…
)

SKIP=1
Tells SQL*Loader to exclude row 1 in the data file (to exclude the headers)

INFILE
Specifies the name of the incoming data csv file

BADFILE
Determines the bad file generated for any errors occured during the load

REPLACE
This command will first truncate the table and than add the records. If you change this in an APPEND command the records will be added only to the table specified without truncating first

FIELDS TERMINATED BY
Determines the seperator used in the data csv file

OPTIONALLY ENCLOSED BY
Determines an additional enclosing character like for example ” if you’re adding data which contains the column seperator used

TRAILING NULLCOLS
Is used to treat any missing data in the csv file as NULL for the table to be loaded

COLUMN1, COLUMN2, COLUMN3, …
Gives the column names to be loaded

3) Create the SQL*Loader Executable in Oracle E-Business Suite. Go to responsibility System Administrator – Concurrent – Program – Executable.


Give you executable a name, shortname and assign it to your customizations application. A description is optionally. Select Execution Method SQL*Loader to let eBS know SQL*Loader needs to be started. The Execution File Name holds the name of the control file you want to start (exclude the extension ctl here). The control file needs to be located in the bin directory of your customization application.

4) Create the concurrent program in Oracle E-Business Suite. Go to responsibility System Administrator – Concurrent – Program – Define.


Give your concurrent program a name, a short name, assign it to your customization’s application and optionally provide a description. Assign your created executable to the concurrent program.

5) Add parameters to dynamically provide the incoming data csv file. Click on Parameters.


Add Sequence 10 and give the parameter a name. In this we want to provide a full path to the incoming data csv file so we use seeded Value Set 100 Characters to hold the path. Optionally add a default value.

6) When done creating the concurrent program add it to a Concurrent Request Group and start loading data in your tables.

/by
, , ,

R12 – FND – Script to add responsibility using fnd_user_pkg with validation

Some times we don’t have the access to add the responsibility to the user using the the Create User form. So for this Oracle is having one API fnd_user_pkg.addresp which can do the job without using the Create User Form.

R12 – FND – Script to add responsibility using fnd_user_pkg with validation

DECLARE
v_user_name       VARCHAR2 (10) := ‘Enter_User_Name’;
v_resp_name       VARCHAR2 (50) := ‘Enter_Existing_Responsibility_Name’;
v_req_resp_name   VARCHAR2 (50) := ‘Enter_required_Responsibility_Name’;
v_user_id         NUMBER (10);
v_resp_id         NUMBER (10);
v_appl_id         NUMBER (10);
v_count           NUMBER (10);
v_resp_app        VARCHAR2 (50);
v_resp_key        VARCHAR2 (50);
v_description     VARCHAR2 (100);
RESULT            BOOLEAN;
BEGIN
SELECT fu.user_id, frt.responsibility_id, frt.application_id
INTO v_user_id, v_resp_id, v_appl_id
FROM fnd_user fu,
fnd_responsibility_tl frt,
fnd_user_resp_groups_direct furgd
WHERE     fu.user_id = furgd.user_id
AND frt.responsibility_id = furgd.responsibility_id
AND frt.LANGUAGE = ‘US’
AND fu.user_name = v_user_name
AND frt.responsibility_name = v_resp_name;

fnd_global.apps_initialize (v_user_id, v_resp_id, v_appl_id);

SELECT COUNT (*)
INTO v_count
FROM fnd_user fu,
fnd_responsibility_tl frt,
fnd_user_resp_groups_direct furgd
WHERE     fu.user_id = furgd.user_id
AND frt.responsibility_id = furgd.responsibility_id
AND frt.LANGUAGE = ‘US’
AND fu.user_name = v_user_name
AND frt.responsibility_name = v_req_resp_name;

IF v_count = 0
THEN
SELECT fa.application_short_name,
frv.responsibility_key,
frv.description
INTO v_resp_app, v_resp_key, v_description
FROM fnd_responsibility_vl frv, fnd_application fa
WHERE frv.application_id = fa.application_id
AND frv.responsibility_name = v_req_resp_name;

fnd_user_pkg.addresp (username         => v_user_name,
resp_app         => v_resp_app,
resp_key         => v_resp_key,
security_group   => ‘STANDARD’,
description      => v_description,
start_date       => SYSDATE – 1,
end_date         => NULL);

RESULT :=
fnd_profile.SAVE (x_name          => ‘APPS_SSO_LOCAL_LOGIN’,
x_value         => ‘BOTH’,
x_level_name    => ‘USER’,
x_level_value   => v_user_id);

RESULT :=
fnd_profile.SAVE (x_name          => ‘FND_CUSTOM_OA_DEFINTION’,
x_value         => ‘Y’,
x_level_name    => ‘USER’,
x_level_value   => v_user_id);

RESULT :=
fnd_profile.SAVE (x_name          => ‘FND_DIAGNOSTICS’,
x_value         => ‘Y’,
x_level_name    => ‘USER’,
x_level_value   => v_user_id);

RESULT :=
fnd_profile.SAVE (x_name          => ‘DIAGNOSTICS’,
x_value         => ‘Y’,
x_level_name    => ‘USER’,
x_level_value   => v_user_id);

RESULT :=
fnd_profile.SAVE (x_name          => ‘FND_HIDE_DIAGNOSTICS’,
x_value         => ‘N’,
x_level_name    => ‘USER’,
x_level_value   => v_user_id);

DBMS_OUTPUT.put_line (
‘The responsibility added to the user ‘
|| v_user_name
|| ‘ is ‘
|| v_req_resp_name);

COMMIT;
ELSE
DBMS_OUTPUT.put_line (
‘The responsibility has already been added to the user’);
END IF;
END;

/by
, , ,

R12 – Adding Descriptive Flex Field (DFF) through OAF Personalization

This article will explain how to add a DFF to a existing OAF page through personalization.I am using Supplier Quick Update Page ( /oracle/apps/pos/supplier/webui/SuppSummPG ).
If you want to see how to create DFF please click here.
In this scenario I am using a custom DFF. Following are the details.
Application -> Payables ( Code: SQLAP )
Name -> XXCUST_SUPPLIER_DFF
Title -> XXCUST – Supplier DFF
Table Name -> AP_SUPPLIERS
DFV View name -> XXCUST_SUPPLIER_DFV
Reference Fields -> ATTRIBUTE_CATEGORY
Following are the Context Field Details.
Prompt -> Supplier Type
Value Set -> XXCUST_SUP_TYPE ( Values : External and Internal )
Reference Field -> ATTRIBUTE_CATEGORY
Below table shows the segment details of XXCUST_SUPPLIER_DFF.
Code
Segments
Column
Value Set
Global Data Elements
Identification Number
ATTRIBUTE1
15 Characters
External
Type
ATTRIBUTE2
XXCUST_EXT_SUP_TYPE
Values
         Domestic 
          International
Internal
Department
ATTRIBUTE2
15 Characters
Following steps you need to perform to create flex item in the Quick Update page.
1) Click on Personalize Page.In the Personalize Page click on Complete View.
2) Click on Create Item.( Based on where you want to place the DFF choose appropriate layout).
3) Create flex item with following details.
4) If you want to arrange the item in the page click on Reorder.
Following is the output.
/by