Pages

Recently Viewed

Wednesday, February 29, 2012

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;


Friday, February 17, 2012

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 ;
/