FND User creation script

Oracle provides below API to create FND User.

/*procedure FND_USER_PKG.CreateUser (
  x_user_name                  in varchar2,
  x_owner                      in varchar2,
  x_unencrypted_password       in varchar2 default null,
  x_session_number             in number default 0,
  x_start_date                 in date default sysdate,
  x_end_date                   in date default null,
  x_last_logon_date            in date default null,
  x_description                in varchar2 default null,
  x_password_date              in date default null,
  x_password_accesses_left     in number default null,
  x_password_lifespan_accesses in number default null,
  x_password_lifespan_days     in number default null,
  x_employee_id                in number default null,
  x_email_address              in varchar2 default null,
  x_fax                        in varchar2 default null,
  x_customer_id                in number default null,
  x_supplier_id                in number default null)
*/

Below is an example of the above seeded API. You may add/remore parameters based on your requirement.  

DECLARE
   l_user_name             VARCHAR2(100)  := 'TEST_FND_USER';
   l_user_password         VARCHAR2(100)  := 'welcome1';
   l_password_date         VARCHAR2(100)  := SYSDATE;
   l_password_lifespan_days    NUMBER     := 90;
   l_email_address         VARCHAR2(100) := 'test@default.com';

BEGIN
  FND_USER_PKG.CREATEUSER
  (  x_user_name            => l_user_name,
     x_owner                => NULL,
     x_unencrypted_password => l_user_password,
     x_password_date        => l_password_date,
     x_password_lifespan_days   => l_password_lifespan_days,
     x_email_address        => l_email_address
 );

 COMMIT;

 DBMS_OUTPUT.PUT_LINE('Apps User ' || l_user_name || ' has been successfully created');

EXCEPTION
       WHEN OTHERS THEN
           ROLLBACK;
           DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Reset an Oracle Apps User Password via script

DECLARE
      l_ret_val BOOLEAN;
      l_user_name   varchar2(50) := '&USER_NAME';
      l_new_pwd     varchar2(20) := '&PASSWORD';
BEGIN
      l_ret_val :=
           fnd_user_pkg.changepassword(username=> l_user_name
                                      ,newpassword => l_new_pwd);
     IF l_ret_val
     THEN
           DBMS_OUTPUT.PUT_LINE('The password is successfully reset to '||  l_new_pwd);
           COMMIT;
     ELSE
           DBMS_OUTPUT.PUT_LINE('The password reset has failed');
     END IF;
END;

Adding Responsibility to the Oracle Apps User using SQL Script

DECLARE

   CURSOR C_RESP
   IS
      SELECT fav.application_short_name,
             fav.application_name,
             frv.responsibility_key,
             frv.responsibility_name
        FROM FND_APPLICATION_VL fav, FND_RESPONSIBILITY_VL frv
       WHERE     frv.application_id = fav.application_id
             AND frv.responsibility_name IN
                    ('System Administrator',
                     'Functional Administrator',
                     'Application Developer');


   l_user_name         VARCHAR2 (15) := '&USER_NAME';
   l_appl_short_name   fnd_application_vl.application_short_name%TYPE;
   l_resp_name         FND_RESPONSIBILITY_VL.responsibility_name%TYPE;
   l_resp_key          FND_RESPONSIBILITY_VL.responsibility_key%TYPE;

   l_description       VARCHAR2 (100)
                          := 'Adding Responsibility to the user using script';
BEGIN

  FOR resp_rec IN C_RESP
  LOOP
     l_appl_short_name := resp_rec.application_short_name;
     l_resp_key := resp_rec.responsibility_key;
     l_resp_name := resp_rec.responsibility_name;

     BEGIN
        FND_USER_PKG.ADDRESP (username         => l_user_name,
                              resp_app         => l_appl_short_name,
                              resp_key         => l_resp_key,
                              security_group   => 'STANDARD',
                              description      => l_description,
                              start_date       => SYSDATE,
                              end_date         => NULL);
        COMMIT;
        DBMS_OUTPUT.put_line (
              'The responsibility '
           || l_resp_name
           || ' is added to the user '
           || l_user_name);
     EXCEPTION
        WHEN OTHERS
        THEN
           DBMS_OUTPUT.put_line (
                 'Responsibility '
              || l_resp_name
              || ' IS NOT added to the user '
              || l_user_name
              || ' due to '
              || SQLCODE
              || '; '
              || SUBSTR (SQLERRM, 1, 250));
           DBMS_OUTPUT.put_line ('');
           ROLLBACK;
     END;
  END LOOP;
END;

How to Modify and Enable The Configurable Home Page Delivered in R12

How to Modify and Enable The Configurable Home Page Delivered Via 12.1.2 [ID 1061482.1]

How To Get Version of Programs, Files, and Objects Necessary for Oracle Applications?

Very good metalink note:


How To Get Version of Programs, Files, and Objects Necessary for Oracle Applications? [ID 121916.1]

Good Article on why mod_plsql (plsql web tollkit) is not supported in R12

Mod_plsql and Oracle E-Business Suite Release 12 [ID 726711.1]

FNDLOAD command

Syntax
The Generic Loader is a concurrent program named FNDLOAD. The concurrent executable takes the following parameters:

FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ...]
where


The APPS schema and password in the form username/password[@connect_string]. If
connect_string is omitted, it is taken in a platform-specific manner from the environment
using the name TWO_TASK.

< 0 Y >
Concurrent program flags.

mode
UPLOAD or DOWNLOAD. UPLOAD causes the datafile to be uploaded to the database. DOWNLOAD causes the loader to fetch rows and write them to the datafile.


The configuration file to use (usually with a suffix of .lct, but not enforced or supplied by the loader).


The data file to write (usually with a suffix of .ldt, but not enforced or supplied by the loader). If the data file already exists, it will be overwritten.


The entity(ies) to upload or download. When uploading, always upload all entities, so specify a "-" to upload all entities.

< [param] >
Zero or more additional parameters are used to provide bind values in the access SQL (both UPLOAD and DOWNLOAD). Each parameter is in the form NAME=VALUE. NAME should not conflict with an attribute name for the entities being loaded.

Example of download
FNDLOAD apps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \ PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name> APPLICATION_SHORT_NAME=

Example of Upload
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt - CUSTOM_MODE=FORCE undocumented parameter

Some sample examples

1 - Printer Styles

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME=”printer style name”

2 - Lookups

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=”prod” LOOKUP_TYPE=”lookup name”

3 - Descriptive Flexfield with all of specific Contexts

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME=”prod” DESCRIPTIVE_FLEXFIELD_NAME=”desc flex name” P_CONTEXT_CODE=”context name”

4 - Multiple Flexfields

Use a combination of APPLICATION_SHORT_NAME and DESCRIPTIVE_FLEXFIELD_NAME names ie. APPLICATION_SHORT_NAME=PER >> will download all PER flexfields DESCRIPTIVE_FLEXFIELD_NAME=PER_% >> will download all flexfields that start with 'PER_'.

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct
file_name.ldt DESC_FLEX DESCRIPTIVE_FLEXFIELD_NAME="PER_%"

5 - Key Flexfield Structures

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=?COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME=”prod” ID_FLEX_CODE=”key flex code” P_STRUCTURE_CODE=”structure name”

6 - Concurrent Programs

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”prod” CONCURRENT_PROGRAM_NAME=”concurrent name”

7 - Value Sets

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=”value set name”

8 - Value Sets with values

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=”value set name”

9 - Profile Options

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=”profile option” APPLICATION_SHORT_NAME=”prod”

10 - Request Group

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”request group” APPLICATION_SHORT_NAME=”prod”

11 - Request Sets

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME=”prod” REQUEST_SET_NAME=”request set”

12 - Responsibilities

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=”responsibility"

13 - Responsibilities with all Security Groups

FNDLOAD apps/ 0 Y DOWNLOAD FND_TOP/patch/115/import/afscursp.lct .ldt
FND_USER USER_NAME="" SECURITY_GROUP=% DATA_GROUP_NAME=%

14 - Menus

FNDLOAD apps/ pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=”menu_name”

15 - Forms/Functions/Personalizations: Refer to the System Administrator's Guide on dependencies

FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct
FND_FORM_CUSTOM_RULES form_name=

OR

FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt FUNCTION FUNCTION_NAME=

OR

FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt FORM FORM_NAME=

OR

FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES function_name=

16 - User/Responsibilities

FNDLOAD apps/ pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_USER

17 - Alert

FNDLOAD apps/pwd 0 Y DOWNLOAD
$ALR_TOP/patch/115/import/alr.lct my_file.ldt ALR_ALERTS
APPLICATION_SHORT_NAME=FND ALERT_NAME=Alert name to downloa

18 - Blob

With Release 12.1.1, FNDLOAD supports BLOB data (upload / download ) to better serve content-rich applications.

FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ... ]

19 - Overwrite custom definitions

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/.lct $
XX_TOP/import/.ldt CUSTOM_MODE=FORCE

20 - Load an NLS Language

FNDLOAD / 0 Y UPLOAD \
- UPLOAD_MODE=NLS CUSTOM_MODE=FORCE WARNINGS=TRUE


21 - Migrate the role registration process from one instance to another

a. Please navigate to the path: $FND_TOP /patch/115/import/US/umxrgsvc.ldt

b. The following command can be used to download:

FNDLOAD apps/@(instance name) O Y DOWNLOAD $FND_TOP/patch/115/import/umxrgsvc.lct umxrgsvc.ldt UMX_REG_SERVICES REG_SERVICE_CODE UMX

c. The following command can be used to upload:

FNDLOAD apps/@(instance name) O Y UPLOAD $FND_TOP/patch/115/import/umxrgsvc.lct umxrgsvc.ldt UMX_REG_SERVICES REG_SERVICE_CODE UMX

22 - Transfer Custom Messages to another Instance

a. Download the message from the source instance.

FNDLOAD apps/apps 0 Y DOWNLOAD @FND:patch/115/import/afmdmsg.lct password.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=FND MESSAGE_NAME=PASSWORD-INVALID-NO-SPEC-CHAR

b. Move the custom LDT file (password.ldt) over to the destination instance.

c. Upload the custom message to the destination instance.

FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afmdmsg.lct password.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=FND CUSTOM_MODE=FORCE

23 - Download UMX Roles and Role Assignment data from one instance and upload to another.

To download from one instance:

FNDLOAD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afrole.lct
umxroles.ldt WF_ROLE ORIG_SYSTEM=UMX%

To upload to another instance:

FNDLOAD 0 Y UPLOAD $FND_TOP/patch/115/import/afrole.lct
umxroles.ldt

References:

· Oracle Applications Systems Administrator Guide - Configuration

. Oracle Metalink Note#735338.1