WebADI : Custom Integrators not appearing in the 'Create Document' Integrator LOV in R12

If your custom Integrator is not appearing in Integrator list of values in the 'Desktop Integration --> Create Document' function, check the below settings.

Navigation: Desktop Integration Manager --> Manage Integrators 
Search for your custom Integrator

Click on the update icon

Make sure 'Display in Create Document Page' is selected and the function BME_ADI_CREATE_DOCUEMENT is added as a function under the Security Rules section.

You could also override the profile option  'BNE Allow No Security Rule' to ‘Yes’, but the recommended Value is ‘No’

Helpful Links:
New Desktop Integrator Responsibility Missing Integrators (Doc ID 472160.1)



Feel free to point out if anything is wrong/missing in this post.

Oracle Apps: Apply Credit Memo against Debit Memo using API

This is some sample code which we have used in Release 12.1.3 instance to apply Credit Memo against Debit Memo in Bulk.

This code expects you already have the Customer Account Number, Credit Memo and Debit Memo details. You just need to replace the SELECT from DUAL section in the cursor and replace with your values.

DECLARE
  CURSOR c_get_trx IS
    SELECT t.account_number   account_number
          ,TO_NUMBER(t.amt)   apply_amount
          ,t.dm_number           dm_trx_number
          ,t.dm_date dm_trx_date
          ,psd.customer_trx_id dm_trx_id
          ,psd.payment_schedule_id dm_ps_id
          ,psd.amount_due_remaining dm_amt_remaining
          ,psd.status dm_status
          ,t.cm_number cm_trx_number
          ,t.cm_date cm_trx_date
          ,psc.customer_trx_id cm_trx_id
          ,psc.payment_schedule_id cm_ps_id
          ,psc.amount_due_remaining cm_amt_remaining
          ,psc.status cm_status
    FROM   (SELECT '2412315'                             "ACCOUNT_NUMBER"
                   ,'100.50'                             "AMT"
                   ,'95000'                              "DM_NUMBER"
                   ,TO_DATE('01-Jan-2018','DD-Mon-YYYY') "DM_DATE"
                   ,'130000'                             "CM_NUMBER"
                   ,TO_DATE('05-Jan-2018','DD-Mon-YYYY') "CM_DATE"
            FROM   dual) t
           ,hz_cust_accounts ca
           ,ar_payment_schedules psd
           ,ar_payment_schedules psc
    WHERE  ca.account_number = t.account_number
    AND    psd.customer_id = ca.cust_account_id
    AND    psd.class = 'DM'
    AND    psd.trx_number = t.dm_number
    AND    TRUNC(psd.trx_date) = t.dm_date
    AND    psc.customer_id = ca.cust_account_id
    AND    psc.class = 'CM'
    AND    psc.trx_number = t.cm_number
    AND    TRUNC(psc.trx_date) = t.cm_date ;
 
  x_application_id      NUMBER;
  x_amount_applied_from NUMBER;
  x_amount_applied_to   NUMBER;
 
BEGIN
  -- Apps Initialize
  mo_global.set_policy_context('S'
                              ,0);
  --Initilize with the Receivables Responsibility
  fnd_global.apps_initialize(1000
                            ,50000
                            ,222);
 
  FOR r_rec IN c_get_trx
  LOOP
    DBMS_OUTPUT.put_line('Account Number ' || r_rec.account_number);
    DBMS_OUTPUT.put_line('Applying Credit Memo: ' || r_rec.cm_trx_number || ' to Debit Memo : ' || r_rec.dm_trx_number);
 
    IF NVL(r_rec.dm_status
          ,'X') != 'OP'
    THEN
      DBMS_OUTPUT.put_line('*****ERROR: Debit Memo is not Open. This cannot be applied.');
      CONTINUE;
    END IF;
 
    IF r_rec.dm_amt_remaining < r_rec.apply_amount
    THEN
      DBMS_OUTPUT.put_line('*****ERROR: Amount to be applied > Remaining amount on Debit Memo.');
      CONTINUE;
    END IF;
 
    BEGIN
      arp_process_application.cm_application(p_cm_ps_id                  => r_rec.cm_ps_id
                                            ,p_invoice_ps_id             => r_rec.dm_ps_id
                                            ,p_amount_applied            => r_rec.apply_amount
                                            ,p_apply_date                => SYSDATE
                                            ,p_gl_date                   => SYSDATE
                                            ,p_out_rec_application_id    => x_application_id
                                            ,p_acctd_amount_applied_from => x_amount_applied_from
                                            ,p_acctd_amount_applied_to   => x_amount_applied_to
                                            ,p_ussgl_transaction_code    => NULL
                                            ,p_attribute_category        => NULL
                                            ,p_attribute1                => NULL
                                            ,p_attribute2                => NULL
                                            ,p_attribute3                => NULL
                                            ,p_attribute4                => NULL
                                            ,p_attribute5                => NULL
                                            ,p_attribute6                => NULL
                                            ,p_attribute7                => NULL
                                            ,p_attribute8                => NULL
                                            ,p_attribute9                => NULL
                                            ,p_attribute10               => NULL
                                            ,p_attribute11               => NULL
                                            ,p_attribute12               => NULL
                                            ,p_attribute13               => NULL
                                            ,p_attribute14               => NULL
                                            ,p_attribute15               => NULL
                                            ,p_global_attribute_category => NULL
                                            ,p_global_attribute1         => NULL
                                            ,p_global_attribute2         => NULL
                                            ,p_global_attribute3         => NULL
                                            ,p_global_attribute4         => NULL
                                            ,p_global_attribute5         => NULL
                                            ,p_global_attribute6         => NULL
                                            ,p_global_attribute7         => NULL
                                            ,p_global_attribute8         => NULL
                                            ,p_global_attribute9         => NULL
                                            ,p_global_attribute10        => NULL
                                            ,p_global_attribute11        => NULL
                                            ,p_global_attribute12        => NULL
                                            ,p_global_attribute13        => NULL
                                            ,p_global_attribute14        => NULL
                                            ,p_global_attribute15        => NULL
                                            ,p_global_attribute16        => NULL
                                            ,p_global_attribute17        => NULL
                                            ,p_global_attribute18        => NULL
                                            ,p_global_attribute19        => NULL
                                            ,p_global_attribute20        => NULL
                                            ,p_customer_trx_line_id      => NULL
                                            ,p_module_name               => 'RAPI'
                                            ,p_module_version            => 1.0);
 
      DBMS_OUTPUT.put_line('Successfully applied!!!');
 
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('*****ERROR: Unable to apply credit memo ' || r_rec.cm_trx_number || ': ' ||
                             app_exception.get_type || ':' || app_exception.get_code || ':' || app_exception.get_text);
    END;
  END LOOP;
  COMMIT;
END;

Feel free to point out if anything is wrong/missing in this post.

PL/SQL: Sample code to call a Procedure with OUT parameter using execute immediate

One of my collegue asked me how to call a PLSQL package procedure dynamically. He has done a lot with dynamic sql, but all of those were calling sql statements. he was not sure how to call a procedure which has an OUT variable as a parameter. This post is for him :)

This is just a quick example to show how can we get the value of an OUT variable when we call a procedure using execute immediate.

Sample Procedure

CREATE OR REPLACE PROCEDURE xxaj_test_proc (id      NUMBER
                                           ,msg OUT VARCHAR2)
IS
BEGIN
 
  DBMS_OUTPUT.put_line('    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
  DBMS_OUTPUT.put_line('    id:'||id);
  msg := 'Return from xxaj_proc for id ' || id ;
  DBMS_OUTPUT.put_line('    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
 
END xxaj_test_proc;
/

Sample code to call

DECLARE
  v_sql VARCHAR2(1000);
  v_msg VARCHAR2(100);
  v_id  NUMBER := 100;
BEGIN
 
  v_sql := 'begin xxaj_test_proc(:1,:2); end;';
 
  v_id := 100;
  DBMS_OUTPUT.put_line('Going to Call xxaj_test_proc with id: '|| v_id);
  --- Map the variables for the bind parameter and specify OUT for out variables  
  EXECUTE IMMEDIATE  v_sql using v_id,OUT v_msg; 
  DBMS_OUTPUT.put_line('v_msg: '|| v_msg); 
 
  DBMS_OUTPUT.put_line(''); 
  v_id := 101;
  DBMS_OUTPUT.put_line('Going to Call xxaj_test_proc with id: '|| v_id);
  --- Map the variables for the bind parameter and specify OUT for out variables  
  EXECUTE IMMEDIATE  v_sql using v_id,OUT v_msg; 
  DBMS_OUTPUT.put_line('v_msg: '|| v_msg); 
 
END;

Output

Going to Call xxaj_test_proc with id: 100
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    id:100
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
v_msg: Return from xxaj_proc for id 100

Going to Call xxaj_test_proc with id: 101
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    id:101
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
v_msg: Return from xxaj_proc for id 101

Feel free to point out if anything is wrong/missing in this post.

Oracle Apps: How to submit a Concurrent Program from back-end and wait for that to complete

In my previous post I have explained about how can we make the parent concurrent program wait for all the child programs to complete. In this one I thought of giving an example of how to submit a concurrent program from the back-end and wait for the completion of that.

DECLARE
 
   v_request_id        NUMBER;
   v_req_return_status BOOLEAN;
   v_phase             VARCHAR2(100);
   v_status            VARCHAR2(100);
   v_dev_phase         VARCHAR2(100);
   v_dev_status        VARCHAR2(100);
   v_message           VARCHAR2(100);
 
BEGIN
   --Initialize the session with appropirate values
   fnd_global.apps_initialize (user_id=>100
                              ,resp_id=>100
                              ,resp_appl_id=>100);
 
   --Submit the Request
   v_request_id := fnd_request.submit_request ( application => 'XXAJ'
                                              , program     => 'XXAJ_CHILD'
                                              , start_time  => SYSDATE
                                              , sub_request => FALSE);
   COMMIT;
 
   IF v_request_id = 0 THEN
      DBMS_OUTPUT.put_line('Request not submitted: '|| fnd_message.get);
   ELSE
      DBMS_OUTPUT.put_line('Request submitted successfully. Request id: ' || v_request_id);
 
      v_req_return_status := fnd_concurrent.wait_for_request 
                                            (request_id      => v_request_id
                                            ,INTERVAL        => 2 --interval Number of seconds to wait between checks
                                            -- ,max_wait     => 5 --Maximum number of seconds to wait for the request completion
                                             -- out arguments
                                            ,phase           => v_phase
                                            ,status          => v_status
                                            ,dev_phase       => v_dev_phase
                                            ,dev_status      => v_dev_status
                                            ,message         => v_message
                                            );   
      DBMS_OUTPUT.put_line('Request status: ' || v_status); 
   END IF;
 
EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.put_line('Exception: ' || SQLERRM);   
END;
 

Note:- If you want to wait only for x seconds, specify the value for the parameter max_wait.

Feel free to point out if anything is wrong/missing in this blog.

Oracle Apps: How make the Parent Concurrent Program to wait till all the Child Concurrent Program Completes

This note gives a sample code in which a parent program submits multiple child programs waits for all the child programs to complete, without consuming any resources.

Ref: Oracle E-Business Suite Developer's Guide: PL/SQL APIs for Concurrent Processing

Sample Code for the Parent Program and the Child Program. In the sample code, the parent program submits 5 child programs which just wait for 10 seconds and the parent program waits for all the child programs to finish.

Note:- Once the child program completes the parent program gets restarted. Please have a closer look at the first few lines in the Parent Program code.

Package Spec

CREATE OR REPLACE PACKAGE xxaj_master_child_cp  
 AS  
  PROCEDURE parent_prg(p_errbuf OUT VARCHAR2  
            ,p_retcode OUT VARCHAR2);  
 
  PROCEDURE child_prg(p_errbuf OUT VARCHAR2  
            ,p_retcode OUT VARCHAR2);  
 
 END xxaj_master_child_cp;  
 /  

Package Body

CREATE OR REPLACE PACKAGE BODY xxaj_master_child_cp
AS
  PROCEDURE parent_prg(p_errbuf OUT VARCHAR2
                      ,p_retcode OUT VARCHAR2)
  IS
    v_req_data   VARCHAR2(30);
    v_request_id NUMBER;
 
  BEGIN
 
    -- Read the value from REQUEST_DATA.  
    --If this is the first run of the program, then this value will be null.
    -- Otherwise, this will be the value that we passed to SET_REQ_GLOBALS on the previous run.
    v_req_data   := fnd_conc_global.request_data;
    fnd_file.put_line(fnd_file.LOG,'v_req_data : '||v_req_data);
 
    -- If equals to 'EXIT_AJ_PARENT', exit the program. 
    IF v_req_data ='EXIT_XXAJ_PARENT' THEN
       RETURN;
    END IF;
 
    FOR i IN 1..5
    LOOP
      -- Submit child program   
      v_request_id   := fnd_request.submit_request(application => 'XXAJ'
                                                  ,program     => 'XXAJ_CHILD'
                                                 ,sub_request => TRUE); --This is the parameter which treats this request as the child request   
 
      fnd_file.put_line(fnd_file.LOG,'Child Request Id '||(i)||':' || v_request_id);
    END LOOP;
 
    -- Here we set the globals to put the program into the PAUSED status on exit
    -- and to save the state in request_data. 
    fnd_conc_global.set_req_globals(conc_status => 'PAUSED'
                                  , request_data => 'EXIT_XXAJ_PARENT');
 
 
  EXCEPTION
    WHEN OTHERS THEN
      fnd_file.put_line(fnd_file.LOG,'Exception: ' || SQLERRM);
  END parent_prg;
 
  PROCEDURE child_prg(p_errbuf OUT VARCHAR2
                     ,p_retcode OUT VARCHAR2)
  IS
  BEGIN
 
    fnd_file.put_line(fnd_file.LOG,'Start');
    DBMS_LOCK.sleep(10);  -- Wait for some time to see the behaviour :)
    fnd_file.put_line(fnd_file.LOG,'End!!!!');
 
  EXCEPTION
    WHEN OTHERS THEN
      fnd_file.put_line(fnd_file.LOG,'Exception: ' || SQLERRM);
  END child_prg; 
 
END  xxaj_master_child_cp;
/

This is how the Find Requests window looks like when the program is submitted.

You can see that the Parent program is in the Paused status.

Feel free to point out if anything is wrong/missing in this blog.

Oracle Apps: E-Business Suite Applications Technology Stack Component Versions

This post helps to find the vesrions of some of the Technology components in R12.2.
Inorder to get the Technology Stack Components Version on E-Business Suite, please refer to the below Metalink Note.

E-Business Suite Applications Technology Stack Utility / Script To Check The Techstack Component Versions (Forms, Http Server, JDK, Framework, Database, etc) (Doc ID 601736.1)

The script from the above Note will give you an html report with all the Technology stack components versions.

Note:- The below scripts  have been verified only on a 12.2.7 instance

Scripts to run on middle-tier (ex:- use putty)

#1. Forms Version
   $ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version
   
#2. Reports Version
   $ORACLE_HOME/bin/rwrun | grep Release
   
#3. PL/SQL Version
   $ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version

#4. Weblogic Version
   cat $FMW_HOME/wlserver_10.3/.product.properties | grep WLS_PRODUCT_VERSION
   
#5. Jinitiator Version
   grep jinit_ver_comma $CONTEXT_FILE

#6. Application Server Version
   cat $ORACLE_HOME/config/ias.properties | grep Version


Scripts to run on database

#1. EBS Version
   select release_name from apps.fnd_product_groups;
   
#2. Database Version
   select * from v$version;
   
#3. Workflow Version
   select text version from wf_resources where name = 'WF_VERSION';

Feel free to point out if anything is missing/wrong in this blog.

Oracle Apps: List of Responsibilities attached with a Concurrent Program / Request Set

Some helpful queries to find the list of responsibilities to which a concurrent program  or a request set is attached. This helps to easily find out which responsibility can be used to submit a Concurrent Program / request Set.


#1. Query to find the list of responsibilities to which a Concurrent Program is attached.

 SELECT d.responsibility_name          responsibility  
       ,c.request_group_name           request_group  
       ,a.user_concurrent_program_name concurrent_program  
 FROM  apps.fnd_concurrent_programs_vl a  
      ,apps.fnd_request_group_units    b  
      ,apps.fnd_request_groups         c  
      ,apps.fnd_responsibility_vl      d  
 WHERE b.request_unit_id  = a.concurrent_program_id  
 AND   c.request_group_id = b.request_group_id  
 AND   d.request_group_id = b.request_group_id  
 AND   (a.user_concurrent_program_name LIKE 'User Concurrent Program Name'   
        OR  
        a.concurrent_program_name LIKE 'CONCURRENT_PROGRAM_NAME')  
 ORDER BY 1,2,3;

#2. Query to find the list of responsibilities to which a Request Set is attached.

 SELECT d.responsibility_name   responsibility  
       ,c.request_group_name    request_group  
       ,a.user_request_set_name request_set  
 FROM  apps.fnd_request_sets_vl     a  
      ,apps.fnd_request_group_units b  
      ,apps.fnd_request_groups      c  
      ,apps.fnd_responsibility_vl   d  
 WHERE b.request_unit_id  = a.request_set_id  
 AND   c.request_group_id = b.request_group_id  
 AND   d.request_group_id = b.request_group_id  
 AND   a.user_request_set_name LIKE 'Request Set Name'  
 ORDER BY 1,2,3;  

Feel free to point out if anything is missing/wrong in this blog.