, , , ,

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 – Register, Attach, Submit, Delete Concurrent program using API

The scripts in this blog can be used to:
1)    Register the executable and Program
2)    Attach Concurrent program to a Request Group
3)    Submit Concurrent program

1)    Registering the Executable from back end
          Usually we create executable in the front-end, but this can be done from the database tier i.e. back-end too.
          Below is the PL/SQL code to create an executable from back-end.
         BEGIN
              FND_PROGRAM.executable(‘XXMZ_EMPLOYEE’ — executable
                                                              , ‘XXMZ Custom’ — application
                                                              , ‘XXMZ_EMPLOYEE’ — short_name
                                                              , ‘Executable for Employee INFORMATION’ — description
                                                              , ‘PL/SQL Stored Procedure’ — execution_method
                                                              , ‘XXMZ_EMPLOYEE’ — execution_file_name
                                                              , ” — subroutine_name
                                                              , ” — Execution File Path
                                                              , ‘US’ — language_code
                                                              , ”);
             COMMIT;
         END;
       Query in the front-end to see whether your executable is created.

2)    Registering the Concurrent program from back end
            Usually we create Concurrent program in the front-end, but this can be done from the database tier too.
            Below is the program to create a Concurrent program from back-end.
            BEGIN
                     FND_PROGRAM.register(‘Concurrent program for Employee Information’ — program
                                                                , ‘XXMZ Custom’ — application
                                                                , ‘Y’ — enabled
                                                                , ‘XXMZ_EMPLOYEE’ — short_name
                                                                , ‘ Employee Information’ — description
                                                                , ‘XXMZ_EMPLOYEE’ — executable_short_name
                                                                , ‘XXMZ Custom’ — executable_application
                                                                , ” — execution_options
                                                                , ” — priority
                                                                , ‘Y’ — save_output
                                                                , ‘Y’ — print
                                                                , ” — cols
                                                                , ” — rows
                                                                , ” — style
                                                                , ‘N’ — style_required
                                                                , ” — printer
                                                                , ” — request_type
                                                                , ” — request_type_application
                                                                , ‘Y’ — use_in_srs
                                                                , ‘N’ — allow_disabled_values
                                                                , ‘N’ — run_alone
                                                                , ‘TEXT’ – output_type
                                                                , ‘N’ — enable_trace
                                                                , ‘Y’ — restart
                                                                , ‘Y’ — nls_compliant
                                                                , ” — icon_name
                                                                , ‘US’); — language_code
                        COMMIT;
            END;

 Query in the front-end to see whether your Concurrent program is created .

3)    Attaching the concurrent program to the request group
               Usually we Attach Concurrent program to the request group in the front-end, but this can be done from database tier too.
               Below is the program to Attach Concurrent program to the request group from back-end.
               BEGIN
                        FND_PROGRAM.add_to_group(‘XXMZ_EMPLOYEE’ — program_short_name
                                                                             , ‘XXMZ Custom’ — application
                                                                             , ‘xxmz Request Group’ — Report Group Name
                                                                             , ‘XXMZ’); — Report Group Application
                        COMMIT;
              END;

  Query in the front-end to see whether your Concurrent program is Attached to Request Group.

4)    Submitting Concurrent Program from Back-end
We first need to initialize oracle applications session using
                        fnd_global.apps_initialize(user_id,responsibility_id,application_responsibility_id)
and then run fnd_request.submit_request
                         DECLARE
                               l_request_id NUMBER(30);
                         begin
                                  FND_GLOBAL.APPS_INITIALIZE (user_id => 1318, resp_id => 59966, resp_appl_id => 20064);
                                  l_request_id:= FND_REQUEST.SUBMIT_REQUEST (‘XXMZ’ –Application Short name,
‘VENDOR_FORM’– Concurrent Program Short Name );
                                  DBMS_OUTPUT.PUT_LINE(l_request_id);
                                  commit;
                         end;

Once the concurrent program is submitted from back-end, status of the concurrent program can be checked using below query.

SELECT * FROM FND_CONCURRENT_REQUESTS WHERE   REQUEST_ID= l_request_id;

You can use following code to wait for the request. It will return Boolean value.

FND_CONCURRENT.WAIT_FOR_REQUEST
                                   (request_id IN number default NULL,
                                    interval IN number default 60,
                                    max_wait IN number default 0,
                                    phase OUT varchar2,
                                    status OUT varchar2,
                                    dev_phase OUT varchar2,
                                    dev_status OUT varchar2,
                                    message OUT varchar2);

5) Delete a concurrent program from back-end

BEGIN   apps.fnd_program.remove_from_group (      program_short_name    => ‘CTAR_AR_TRX_LINE_CONVERSION’,      program_application   => ‘CTAR’,      request_group         => ‘Receivables All’,      group_application     => ‘Receivables’);   fnd_program.delete_program (‘CTAR_AR_ACCTD_AMT_DUE_RMNG’, ‘CTAR’);   fnd_program.delete_executable (‘CTAR_AR_ACCTD_AMT_DUE_RMNG’, ‘CTAR’);   COMMIT;END;

/by