PLSQL: How to restore the stats on a table

Starting with Oracle 10g, if you need to modify/restore the statistics of a table to an older date statistics, because of a change in the execution plan or to check the performance, you could use dbms_stats package to restore the stats. You could also do an export and import, but for that, you should have exported the stats before the gather stats is run.

From Oracle 10g, when statistics are gathered for a table, the old statistics are retained for a pre-defined time, and you can restore the old ones.

Inorder to check for how long the stats are retained, the below queries would help.

The below query will give the number of days the stats are retained.

SELECT DBMS_STATS.get_stats_history_retention
FROM   dual;

The below query will return the date statistics have been purged. Only the stats from the dates after this date is available.

SELECT DBMS_STATS.get_stats_history_availability
FROM   dual;

The below query will give the statistics history for a given table.

SELECT table_name
      ,stats_update_time
FROM   dba_tab_stats_history
WHERE  table_name = 'XXAJ_TEST';
 

You can use  DBMS_STATS.RESTORE_TABLE_STATS ('<schema>','<table_name>',date) to restore the table stats.

BEGIN
 
  DBMS_STATS.restore_table_stats(ownname         => 'XXAJ'
                                ,tabname         => 'XXAJ_TEST'
                                ,as_of_timestamp => '18-JUL-19 12.01.20.766591 PM +08:00');
 
END;

Ref: How to Restore Oracle Optimizer Statistics (Doc ID 452011.1)




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

Oracle Apps: Script to unapply Credit memo

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

This code expects you already have the Customer Account Number, Credit Memo and Debit Memo/Invoice 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)          amount,
          t.field5                  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, 
          adj.customer_trx_id       inv_trx_id,
          adj.trx_rec_number        inv_trx_number
   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 psc,
         ar_app_adj_v         adj
   WHERE ca.account_number  = t.account_number
   AND   psc.customer_id    = ca.cust_account_id
   AND   psc.class          = 'CM'
   AND   psc.trx_number     = t.field5
   AND   TRUNC(psc.trx_date)= t.cm_date
   AND   adj.payment_schedule_id = psc.payment_schedule_id
   AND   psc.status = 'CL';
 
  v_return_status           VARCHAR2(1);
  p_count                   NUMBER;
  v_msg_count               NUMBER;
  v_msg_data                VARCHAR2(2000);
 
  v_cm_unapp_rec            AR_CM_API_PUB.CM_UNAPP_REC_TYPE;
  v_cm_id                   NUMBER(15) DEFAULT 1605;
  v_inv_id                  NUMBER(15) DEFAULT 1604;
  v_gl_date                 DATE       DEFAULT SYSDATE;
 
  v_context                 VARCHAR2(2);
 
BEGIN
   -- Apps Initialize
   MO_GLOBAL.SET_POLICY_CONTEXT('S', 0);
   fnd_global.apps_initialize (1000,50000,222);
 
   FOR r_rec IN c_get_trx LOOP
      DBMS_OUTPUT.put_line('Processing account number ' || r_rec.account_number || ': Trying to un apply credit memo number: ' || r_rec.cm_trx_number ||' from debit memo number: ' || r_rec.dm_trx_number);
  
      BEGIN
 
     v_cm_unapp_rec.cm_customer_trx_id       := r_rec.cm_trx_id;
         v_cm_unapp_rec.inv_customer_trx_id  := r_rec.inv_trx_id;
         v_cm_unapp_rec.reversal_gl_date     := TRUNC(SYSDATE);
 
        /* Invoking credit memo unapplication api */
        ar_cm_api_pub.unapply_on_account(
                  p_api_version                => 1.0
                 ,p_init_msg_list              => FND_API.G_FALSE
                 ,p_commit                     => FND_API.G_FALSE
                 ,p_cm_unapp_rec               => v_cm_unapp_rec
                 ,x_return_status              => v_return_status
                 ,x_msg_count                  => v_msg_count
                 ,x_msg_data                   => v_msg_data);

        DBMS_OUTPUT.put_line('Message count:' || v_msg_count);
 
        IF v_msg_count = 1 THEN
          DBMS_OUTPUT.put_line('Message Data: '||v_msg_data);
        ELSIF v_msg_count > 1 THEN
          LOOP
            p_count := p_count+1;
            v_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
            IF v_msg_data IS NULL THEN
              EXIT;
            END IF;
            DBMS_OUTPUT.put_line('Message Data : ' || p_count ||' ---'||v_msg_data);
          END LOOP;
        END IF; 
 
      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.

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.