Check Workflow components(Notification mailers, listeners etc) which are not running in Oracle Apps

Use the below query to Check Workflow components(Notification mailers, listeners etc) which are not running From back-end:-


  SELECT component_type,
         component_name,
         Component_status,
         COMPONENT_STATUS_INFO Error
    FROM fnd_svc_components
   WHERE component_type LIKE 'WF%' AND component_status <> 'RUNNING'
ORDER BY 1 DESC, 2, 3;

ORA-20002: Version of statistics table "APPLSYS"."FND_STATTAB" is too old. Please try upgrading it with dbms_stats.upgrade_stat_table ORA-06512: at "APPS.FND_STATS", line 2828 ORA-06512: at line 1

This issue could come when you upgrade your DB to 12c or higher version.

To Replicate the issue gather stats on a table, Upgrade DB to 12c, create new index on the table and try to gather stats using below piece of code.

Gather Stats :-

exec fnd_stats.GATHER_TABLE_STATS(ownname=>'XXNAME',tabname=>'XX_NAME_TBL', PERCENT=>20,degree=>32,backup_FLAG=>'BACKUP',cascade=>true,invalidate=>'Y');

OR

BEGIN fnd_stats.GATHER_TABLE_STATS(ownname=>'XXNAME',tabname=>'XX_NAME_TBL', PERCENT=>20,degree=>32,backup_FLAG=>'BACKUP',cascade=>true,invalidate=>'Y'); END;

Error :-

ORA-20002: Version of statistics table "APPLSYS"."FND_STATTAB" is too old.

Please try upgrading it with dbms_stats.upgrade_stat_table

ORA-06512: at "APPS.FND_STATS", line 2828

ORA-06512: at line 1

Solution :-

1. Connect to sqlplus / as sysdba

2. Backup FND_STATTAB table

3. run: EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(‘APPLSYS’,’FND_STATTAB’);

Now try to gather stats on your table.

error: APP-AR-11221 : you must save this transaction before attempting to make it complete

Problem:     error: APP-AR-11221 : you must save this transaction before attempting to make it complete

Error: APP-AR-11221 : you must save this transaction before attempting to make it complete, user has to exist the form, re query the manual credit memo transaction to be able to save it


Symptoms:

Create AR transaction with source Manual Credit Memo and transaction type as Credit Memo

Log files:
ar.plsql.arp_ct_pkg.lock_compare_p.begin arp_ct_pkg.lock_compare_p()+
ar.plsql.arp_ct_pkg.lock_compare_p EXCEPTION: arp_ct_pkg.lock_compare_p NO_DATA_FOUND


Solution:

Trailing spaces issue:- 

Columns detected with trailing spaces / uncompiled AR% objects in APPS schema.


Please find file afchrchk.sql under $FND_TOP/sql. Please run the
same and this will create a procedure: chk_trail_space.
After successfully running this , please execute the below commands and then this will diagnose
the problematic columns (trailing spaces) and then run subsequent update scripts
and check whether the issue is resolved or not.

Usage:

"set serveroutput on " and execute the same,one by one.
execute chk_trail_space ('RA_CUSTOMER_TRX_ALL');
execute chk_trail_space ('RA_CUSTOMER_TRX_LINES_ALL');
execute chk_trail_space ('RA_CUST_TRX_LINE_GL_DIST_ALL');
execute chk_trail_space ('RA_BATCHES_ALL');

On executing a script, if you get an output for a table, then issue the update
statement below , issuing a COMMIT then.

update <table name>
set <column name> = rtrim(<column name>);

Follow Note 416878.1 to correct the trailing spaces / Compile all AR% objects in APPS schema using adadmin


References:


Note 416878.1 to correct the trailing spaces / Compile all AR% objects in APPS schema using adadmin

Oracle Work Flow Mailer Logs Server Location

Run the below query to find out the location where Workflow mailer logs get stored in server.


SELECT fl.meaning,
         fcp.process_status_code,
         DECODE (fcq.concurrent_queue_name,
                 'WFMLRSVC', 'mailer container',
                 'WFALSNRSVC', 'listener container',
                 fcq.concurrent_queue_name),
         fcp.concurrent_process_id,
         os_process_id,
         fcp.logfile_name
    FROM fnd_concurrent_queues fcq,
         fnd_concurrent_processes fcp,
         fnd_lookups fl
   WHERE     fcq.concurrent_queue_id = fcp.concurrent_queue_id
         AND fcp.process_status_code = 'A'
         AND fl.lookup_type = 'CP_PROCESS_STATUS_CODE'
         AND fl.lookup_code = fcp.process_status_code
         AND concurrent_queue_name IN ('WFMLRSVC', 'WFALSNRSVC')
ORDER BY fcp.logfile_name;

Make Your Code GSCC Compliance as per R12.2 Standards

Making your code GSCC compliance is one of the most important exercise as per R12.2 Standards.

Que . What does that mean?

Ans. Its kind of making your code enable for online patching. As we know R12.2 gives us a feature called online patching which decreases downtime significantly. And if you dont make your code GSCC compliant your package gets compiled everytime there is online patching in your instance.

Que. What does it do.

Ans. This check tells your code is GSCC compliant or not. So basically it tells you, you have used schema direct reference for DRL or DML statements.

ie.

You have written

select * from ar.ra_customer_Trx_All

Instead of

select * from ra_customer_Trx_all


Que. What to do after finding out its not GSCC Compliance.

Ans. Simple. Make it GSCC compliant. :) .. By removing direct schema reference.


process:- run this command from unix box or putty :-

1. Place your file in home directory.

2. Run the below command.

$FND_TOP/bin/gscc.pl -f '/home/Your home dir name /*'




Note . Even if you have apps. in your code your code will still fail the GSCC check. Its recommended to Remove apps reference also from the code.

Create and drop Oracle DB Queue/ Queue Table

Follow the below steps to Create Queue :-

1. Create Queue Table :-

BEGIN
   SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
  (
    QUEUE_TABLE           =>        'XX.XX_CUSTOM_QT'
   ,QUEUE_PAYLOAD_TYPE    =>        'APPS_NE.XX_CUSTOM_OBJ'
   ,COMPATIBLE            =>        '10.0.0'
   ,STORAGE_CLAUSE        =>        'TABLESPACE OF YOUR ORG IF CUSTOM'
   ,SORT_LIST             =>        'ENQ_TIME'
   ,MULTIPLE_CONSUMERS    =>         FALSE
   ,MESSAGE_GROUPING      =>         0
   ,SECURE                =>         FALSE
   );

  DBMS_OUTPUT.PUT_LINE('Created queue table - ok.');
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('  Created queue table - '||SUBSTR(SQLERRM, 1, 210));
END;
/

2. Create Queue :- 

BEGIN
  SYS.dbms_aqadm.create_queue(queue_name   => 'XX.XX_CUSTOM_Q',
                          queue_table     => 'XX.XX_CUSTOM_QT',
                          queue_type      => dbms_aqadm.normal_queue,
                          max_retries     => 5,
                          retry_delay     => 1000,
                          retention_time  => 86400
                          );

  DBMS_OUTPUT.PUT_LINE('Created queue - ok.');
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Create queue - '||SUBSTR(SQLERRM, 1, 210));
END;
/

3. Start Queue :- 

BEGIN
  SYS.DBMS_AQADM.START_QUEUE
  (QUEUE_NAME => 'XXG.XX_CUSTOM_Q'
   ,ENQUEUE => TRUE
   ,DEQUEUE => TRUE
   );
END;
/

Grant Privilege to Other Schema :-

Grant Enqueue and Dequeue to other schema :- 

BEGIN
DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
   privilege     =>     'ENQUEUE',
   queue_name    =>     'XXG.XX_CUSTOM_Q',
   grantee       =>     'SCHEMA_NAME',
   grant_option  =>      TRUE);
END;
/

BEGIN
DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
   privilege     =>     'DEQUEUE',
   queue_name    =>     'XXG.XX_CUSTOM_Q',
   grantee       =>     'SCHEMA_NAME',
   grant_option  =>      TRUE);
END;
/

Drop Queue:- 

1. Stop Queue :-

BEGIN
  SYS.DBMS_AQADM.STOP_QUEUE ( QUEUE_NAME => 'XX.XX_CUSTOM_Q');
  SYS.DBMS_AQADM.DROP_QUEUE ( QUEUE_NAME => 'XX.XX_CUSTOM_QT');
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

2. Drop Queue Table :- 

BEGIN
  SYS.DBMS_AQADM.DROP_QUEUE_TABLE
    (QUEUE_TABLE => 'XXG.XX_CUSTOM_QT');
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

Avoid Mutating Error in a Oracle DB Table Trigger

You would have seen this error many a times while writing trigger on a table and trying to perform DQL or DML operation on the same table the trigger is written on.

Error :- ORA-04091: table name is mutating, trigger/function may not see it.

You have multiple solutions to resolve it.

1. Write your code in Before Insert/Update Trigger :- If your piece of code is independent and not using any values from the after insert/Update trigger then You can go ahead with this approach. Just move your piece of code from After Insert/Update trigger to Before Insert/Update. This will never give you mutating trigger error.

2. Use pragma AUTONOMOUS for your piece of code :-

I.e.

I am writing a code in a trigger on ra_customer_Trx_all

Code with Error :-

I am giving only pseudo code :- 

After Insert Trigger definition on ra_customer_trx_all

With Error :-

        SELECT

        cust.ATTRIBUTE5

        FROM ra_customer_Trx_all rct, hz_cust_accounts cust

        WHERE   rct.bill_to_customer_id = cust_account_id

         AND rct.customer_Trx_id = :p_customer_Trx_id;

Avoid Error :-

Write the below function somewhere and call it in your logic.

FUNCTION get_cust_acct_id (p_customer_Trx_id IN NUMBER) 

RETURN NUMBER IS

  p_trx_id NUMBER;

  PRAGMA AUTONOMOUS_TRANSACTION;

        SELECT

        bill_to_customer_id

        FROM ra_customer_Trx_all

        WHERE   rct.customer_Trx_id = :p_customer_Trx_id;

RETURN bill_to_customer_id

END;