Assign Responsibility using database

Requirement – To assign any responsibility to a fnd user using database. This is useful if we does not have sysadmin responsibility.

Solution  - Following API can be used to assign any responsibility to a user

  fnd_user_resp_groups_api.Insert_Assignment

                                    ( user_id                                   =>ln_user_id,

                                    responsibility_id                      => c1.responsibility_id,

                                    responsibility_application_id =>c1.responsibility_application_id,

                                    start_date                               =>SYSDATE,

                                    end_date                                 =>SYSDATE+1,

                                    description                              =>'null');

Example – Assign system administrator responsibility to user sandeep

DECLARE
  ln_user_id      NUMBER;

  CURSOR curr_add_resp
  is
    SELECT distinct responsibility_id,
      responsibility_application_id,
      security_group_id
    from fnd_user_resp_groups
    WHERE responsibility_id IN
      (SELECT responsibility_id
      from fnd_responsibility_vl
      WHERE responsibility_name IN ('System Administrator','Functional Administrator')
      )  
  ;
BEGIN  
              /* Fetch user id for fnd user */
              SELECT user_id
              INTO ln_user_id
              FROM fnd_user
              WHERE user_name='SKP';
             
              /*Responsibility Assignments */
              FOR c1 IN  curr_add_resp LOOP
                        BEGIN
                          fnd_user_resp_groups_api.Insert_Assignment
                                                            ( user_id =>ln_user_id,
                                                            responsibility_id => c1.responsibility_id,
                                                            responsibility_application_id =>c1.responsibility_application_id,
                                                            start_date =>sysdate,
                                                            end_date =>SYSDATE+100,
                                                            description =>'null');
                        EXCEPTION
                        WHEN OTHERS THEN
                          dbms_output.put_line('Error in Responsibility Assignment'||SQLERRM);
                        END;
               END LOOP;
EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Error in Responsibility Assignment'||SQLERRM);
END;   

Create FNDUSER From database

Requirement – To create FNDUSER without having access to application.

Solution  - Following API can be used to create a FND USER.

fnd_user_pkg.createuser

                                    ( x_user_name =>lv_user_name,

                                      x_owner        =>lv_owner,

                                      x_unencrypted_password     =>lv_password );

Example – create FNDUSER “Sandeep” using database.

DECLARE

  lv_user_name    VARCHAR2(100):= 'SANDEEP';

  lv_owner        VARCHAR2(10) := 'CUST';

  lv_password     VARCHAR2(20) := 'sandeep'; 
 ln_user_id         NUMBER;

BEGIN

  /*Create FND User*/ 

    fnd_user_pkg.createuser

                                    ( x_user_name                                                =>lv_user_name,

                                      x_owner                                                        =>lv_owner,

                                      x_unencrypted_password                             =>lv_password

                        );

    BEGIN

                          SELECT user_id

                          INTO ln_user_id

                          FROM fnd_user

                          WHERE user_name=lv_user_name;  

    EXCEPTION

                        WHEN no_data_found THEN

                          dbms_output.put_line('Did not create user('||lv_user_name||') : '||sqlerrm);

    END;

    IF ln_user_id IS NOT NULL THEN

      dbms_output.put_line('Successfully created user : '||lv_user_name);

      dbms_output.put_line('user id = '||ln_user_id);

    END IF;

EXCEPTION

    WHEN OTHERS THEN

    dbms_output.put_line('Error in  Create user :'||lv_user_name||'>>'||SQLERRM);

END;

Upload/Download Workflow (wft) Files

Requirement -  Upload wft files to database or download the latest wft file from database

Solution -  Workflow files (with extension .wft) are being stored in database and it can be upload (to) or download (from) database using below 2 methods.

1)      Standard Concurrent Program  

2)      WFLOAD Utility

Standard Concurrent Program – Run the program Workflow Definitions Loader using sysadmin responsibility (you can add this program to any of responsibility and run from there).

This program has 3 parameters as below

1)      Mode – Upload/Download

2)      File - /staging/sandeep/Project/PAPROWF.wft 

                  (wft file name with Full Path. Make sure file is available at given path for UPLOAD Mode)

3)      Item Type – Item type Name for above file (This can be get from workflow builder. Open your wft file in workflow builder and create any process. The first attribute will be Item Type Name)

4)   
    

WFLOAD Utility – similar to FNDLOAD utility used for LDT files, oracle has provided WFLOAD  for workflow files. Below are the available options -

To upgrade      - WFLOAD apps/pwd 0 Y UPGRADE filename.wft

To upload        - WFLOAD apps/pwd 0 Y UPLOAD filename.wft

To force           - WFLOAD apps/pwd 0 Y FORCE filename.wft

To download   - WFLOAD apps/pwd 0 Y DOWNLOAD filename.wft ITEMTYPE1 [ITEMTYPE2 ... ITEMTYPEN]

---

Add Concurrent Program to Request Group Using Database

Requirement – Need to register custom concurrent program to request group without having system administrator responsibility

Solution – We can use below script to add custom program to respective request group  

DECLARE

BEGIN

FND_PROGRAM.add_to_group

(

 PROGRAM_SHORT_NAME =>'XX_MANAGERS'       

,PROGRAM_APPLICATION =>'AR'                             

,REQUEST_GROUP => 'Receivables All'

,GROUP_APPLICATION =>'AR'

) ;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Object already exists');

END ;

/