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;

Get Concurrent Program Request Status and Wait For Request completion

FND_CONCURRENT.GET_REQUEST_STATUS

This API Returns the Status of a concurrent request. It also returns the completion text if the request is already completed. The return type is Boolean (Returns TRUE on successful retrieval of the information, FALSE otherwise).

function get_request_status(request_id     IN OUT NOCOPY number,                     appl_shortname IN varchar2 default NULL,                     program        IN varchar2 default NULL,                     phase      OUT NOCOPY varchar2,                     status     OUT NOCOPY varchar2,                     dev_phase  OUT NOCOPY varchar2,                     dev_status OUT NOCOPY varchar2,                     message    OUT NOCOPY varchar2) return boolean;

The parameters are:

• REQUEST_ID: Request ID of the program to be checked.
• APPL_SHORTNAME: Short name of the application associated with the program. The default is NULL.
• PROGRAM: Short name of the concurrent program. The default is NULL.
• PHASE: Request phase.
• STATUS: Request status.
• DEV_PHASE: Request phase as a string constant.
• DEV_STATUS: Request status as a string constant.
• MESSAGE: Request completion message.

FND_CONCURRENT.WAIT_FOR_REQUEST

This API waits for the request completion, then returns the request phase/status and completion message to the caller. It goes to sleep between checks for the request completion. The return type is Boolean (Returns TRUE on successful retrieval of the information, FALSE otherwise).

function wait_for_request(request_id IN number default NULL,         interval   IN  number default 60,         max_wait   IN  number default 0,         phase      OUT NOCOPY varchar2,         status     OUT NOCOPY varchar2,         dev_phase  OUT NOCOPY varchar2,         dev_status OUT NOCOPY varchar2,         message    OUT NOCOPY varchar2) return  boolean;

The parameters are:

• REQUEST_ID: Request ID of the request to wait on. The default is NULL.
• INTERVAL: Number of seconds to wait between checks. The default is 60 seconds.
• MAX_WAIT: Maximum number of seconds to wait for the request completion. The default is 00 seconds.
• PHASE: User-friendly Request phase.
• STATUS: User-friendly Request status.
• DEV_PHASE: Request phase as a constant string.
• DEV_STATUS: Request status as a constant string.
• MESSAGE: Request completion message.

There are few other useful apis too.

• FND_CONCURRENT.SET_COMPLETION_STATUS: Called from a concurrent request to set its completion status and message.
• FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS: Returns the print options for a concurrent request.
• FND_CONCURRENT.GET_SUB_REQUESTS: Get all sub-requests for a given request id. For each sub-request it provides request_id, phase,status, developer phase , developer status and completion text.
• FND_CONCURRENT.Cancel_Request: It cancels a given Concurrent Request.

For Detailed information of the above requests you can open the package FND_CONCURRENT in your instance.