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 ;

/

Multiple Error Messages in OAF

Requirement – How to raise multiple error messages as multiline on OAF pages.

Solution –  1) Create and initialize new ArrayList object

       2) Define the error messages in array list

       3) Raise bundled eception method  

Example -

    ArrayList  errMsg = New ArrayList();                 

 

    errMsg.add(new OAException("Who is Lucifer"));
    errMsg.add(new OAException("Ra-One will kill Lucifer"));
    errMsg.add(new OAException("G-One is protector"));      
    

   OAException.raiseBundledOAException(errMsg);    

Output –

1.       Who is Lucifer

2.       Ra-One will kill Lucifer

3.       G-One is protector

------------------------------------------------------------------------------------------------------

Exception -  If above method is being implemented inside a try/catch block, It might not throw expected error message.So either implement above outside Try/catch block or use below steps to handle this inside try/catch.       

             

1)      Create array list object at global level

      ArrayList  errMsg = New ArrayList();     

            

2)      Define the messages inside the try block and raise exception with custom message

 try {
    errMsg.add(new OAException("Who is Lucifer"));
    errMsg.add(new OAException("Ra-One will kill Lucifer"));
    errMsg.add(new OAException("G-One is protector"));      

    throw new OAException("Sandeep");   

     }           

3)      Handle this implementation inside the catch block

catch(Exception e)
  {
  String a="oracle.apps.fnd.framework.OAException: Sandeep";
  if (a.equals(e.toString()))
  {   OAException.raiseBundledOAException(errMsg);    }
  else
  {   throw new OAException("Exception Has been found in code 28:" +e, OAException.INFORMATION); 

 }

Raise Exception in OAF

Requirement – How to raise and handle exceptions in OAF pages.

Solution – There are 3 types of exceptions can be thrown on OAF pages (java).

a)      Warning  – This will display as warning and show custom message  

b)      Information  - This will display as information and show custom message

c)       Error – This will display your custom message as error and stop the flow.

In java (OAF) we use try catch block to handle exceptions.
Try Block – All logic should be kept in a try block to handle exceptions
Catch Block – Once control find any exception in try block, it goes to catch block. Catch block will be used to handle the raised exception.

Example -

try{ 

                                custom code{

……………;

…………….;

}   

  

throw new OAException("Sandeep");     // Raise Exception Manually     

                      }catch(Exception e)
                     {

                 throw new OAException("Exception " +e,  OAException.WARNING);         
                // For Warning Message

                 throw new OAException("Exception " +e,  OAException.INFORMATION);   
                // For Information Message   

                 throw new OAException("Exception " +e,  OAException.ERROR);             
               // For Error Message 
     }

If exception occurs in custom code, control will come to catch block and it will print the relative exception message on the screen.
If there is no exception in custom code, control will read last line. Which is to throw exception manually and send control to catch block.
Error message in this case will be as “Exception oracle.apps.fnd.framework.OAException: Sandeep”    

Setting Org Context in R12 ( MOAC Context )

Oracle Apps tables ending with ALL holds transaction data for multiple org ( Operating Units).
In 11i we had views on these tables. It requires setting context in order to fetch data from these views.This  is for security concerns as these objects holds transaction details.

This is one of major difference in application architecture between 11i and R12.
In R12 oracle uses VPD (Virtual Private Database) to secure these transactional data.We shall discuss about VPD and MOAC concepts in other post.  

In order to retrieve data from transactional objects, set policy context first ( as below ) -

BEGIN
      MO_GLOBAL.set_policy_context ('MODE' char(1),Org_ID number);
END;

MODE - This is  either "S" - For Single Operating Unit
                                 "M" - For Multiple Operating Unit

Org_ID - Operating unit (Value from column ORG_ID in all transactional objects)
                This is mandatory for "S" mode.

Example -


BEGIN
      MO_GLOBAL.set_policy_context ('S' ,204);
END;

XML Bursting

Requirement -Split the generated report output into multiple reports based on no of suppliers.

For example - Report is having 3 invoices, 2 for one supplier and 1 for second. concurrent program will generate one file as output having  all 3 invoice detail.
Project need is to split output either on 3 files (one for each invoice) or 2 files ( for 2 supplier). 
  
XML bursting concept help us to cut down the generated output in multiple pieces based on requirement.
Also we can send output through email using this service. 

Assumption - SMTP configuration should be available

Steps for XML bursting -
1) Create and set temp folder  
   - Create one folder in unix box ( application server) and set the temp path in application
       - Go to XML administrator responsibility
       - Administrator Function
       - Give the folder path for TEMP directory

2)Create the bursting control file
    Create the control file. control file contains split logic and delivery method, which will be used by bursting engine. Sample file as below-

   -     

    <xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">

          <xapi:request select="/XXXXX/LIST_G_SUPPLIER_NO/G_SUPPLIER_NO">

  <xapi:delivery>

   <xapi:email id="123" server="smtp.host.com" port="25" from="${EMAILFROM}" reply-to="${EMAILFROM}">

   <xapi:message id="123" to="${SUPPLIERSITEEMAIL}" cc="${EMAILCC}" attachment="true" subject="XML Bursting">This program is for xml bursting check

</xapi:message> </xapi:email> </xapi:delivery>

   <xapi:document output="AttachmentName" output-type="pdf" delivery="123">

  <xapi:template type="rtf" location="xdo://APPSHORTNAME.TEMPLATECODE.en.00/?getSource=true" />

 </xapi:document>

</xapi:request></xapi:requestset>

*${XML_TAG_NAME}
Request – Describe group level, on which bursting is required. In above example bursting will be on G_SUPPLIER_NO level
Delivery – How to send the split data ( Email, Fax, Printer etc).
Document output – Subject and format of output    
Template Type – Related template information. Location can have temp directory path ( make sure your rtf file is placed at temp directory too) too  or use the above syntax only.

***en is the territory code.Either use the correct territory code or upload rtf file without territory name.

3)Place the control file –

 XML publisher responsibility >> Data Definition >> Search for your Data Definition >> Bursting Control file >> Add File

4)Invoke Bursting Concurrent Program – XDOBURSTREP is oracle provided standard concurrent program for bursting. Invoke this concurrent program in your program at the last.
   This will read the instructions given in control file and perform bursting.

l_request_id := FND_REQUEST.SUBMIT_REQUEST (
    'XDO',
    'XDOBURSTREP',
    'XML Publisher Report Bursting Program',
    '',
    FALSE,
    'N',
    fnd_global.conc_request_id, 'Y',
    chr(0), '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '');

Run Time Restriction on Concurrent Program Parameter Values

Requirement – Have one program with 2 parameters. 1) Email Options and 2) Invoice Status  

                     Email option can be Yes or No – No will be default value

                    Invoice Status can have Approved, Cancelled, Needs reapproval and Never approved options.

As per requirement  user should be able to pick any of 4 possible Invoice Status, if Email option is No but  in case if Email option is Yes

User should be restricted to select any value other than Approved.

Solution –  This can be easily achieved if we create a custom table having invoice status values along with one additional column to map Email options value.                       

-          Create one custom table using below script

CREATE TABLE O_APPROVAL_STATUS_TAB

(

  status_id                                NUMBER,

  inv_approval_status                             VARCHAR2(50),

  description                VARCHAR2(50),

  email_flag                 VARCHAR2(5)

)

/

GRANT ALL ON O_APPROVAL_STATUS_TAB TO APPS

/

insert into O_APPROVAL_STATUS_TAB values(10,'APPROVED','Validated','Y')

/

insert into O_APPROVAL_STATUS_TAB values(20,'APPROVED','Validated','N')

/

insert into O_APPROVAL_STATUS_TAB values(30,'CANCELLED','Calcelled','N')

/

insert into O_APPROVAL_STATUS_TAB values(40,'NEEDS REAPPROVAL','Needs Revalidation','N')

/

insert into O_APPROVAL_STATUS_TAB values(50,'NEVER APPROVED','Never Validated','N')

-          Create  custom value set O_APPROVAL_VS

    

   Go to Edit Information and select table name as O_APPROVAL_STATUS_TAB

-          Set Where condition as  email_flag=$:FLEX$.Yes_No

-          Create one concurrent Program and define 2 parameters

a)      Email Option              – Yes_No                                             ( Value Set )

b)      Invoice Status            – O_APPROVAL_VS                        ( Value Set )

-          Assign this program to any request group and validate the results

Output Validation – Run the program and change the Email option values Yes and No.

 

OPP Service log file and location on server

In previous post on OPP, we learnt about OPP services using application. Below mentioned SQL is to identify the log file name and location in unix server.

If concurrent program ends in warning due to any OPP service failure, run below sql by passing request id.

SELECT fcpp.concurrent_request_id req_id,
  fcp.node_name,
  fcp.logfile_name
FROM fnd_conc_pp_actions fcpp,
  fnd_concurrent_processes fcp
WHERE fcpp.processor_id        = fcp.concurrent_process_id
AND fcpp.action_type           = 6
AND fcpp.concurrent_request_id = &REQ -- 12345

Req_id      Node_name          LogFileName
12345  ebs002learn   /oracle/ebs/inst/apps/ebs_002learn/logs/appl/conc/log/FNDOPP383669.txt
 
Open putty
Login to server ebs002learn
open specified Logfile and search for your request id (12345) for detail log message

**** Above SQL is from my DBA Friend ( Jyothi )