get Trace File for concurrent program in Oracle Apps

How to get Trace File for concurrent program in Oracle Apps


1.    Navigate to Profile/System and query the following profile name:
a.    Concurrent: Allow Debugging
b.    Set this profile to Yes


2.    Next, move to the appropriate responsibility from which you are running the concurrent request.
a.    If using Purchasing Super User responsibility
b.    Choose Reports/Run
c.    Enter the request name
d.    Enter any parameters required to run the request
e.    A new button will be shown - entitled 'Debug Options' click this button and a web based form will open.
f.     In the new form, choose the SQL Trace option and set this to Trace with Binds and Waits
g.    Hit the OK button
h.    A message will appear 'Debug Rule has been created successfully' Choose OK and the web form will close
i.      Submit the request  and Take note of the Concurrent Request ID


3.    In sqlplus - execute the following statement -
a.    select name, value from v$parameter where name like 'user_dump_dest';
b.    Take note of the value output - this is the location on the database server where the trace files are located. The trace file name will have the concurrent request id along with the username for the user that submitted the request.

4.    How to get the TKPROF from the trace file
a.    TKPROF Function:
TKPROF accepts the raw trace file as input, and produces a formatted output file that is most readable than raw trace file.
b.    Generate TKPROF report: At the command prompt, change your directory to the directory where the trace files are located (or, alternatively, copy the trace files to a location of your choice, and change your command prompt directory to that directory).
c.    Execute command like the following:
tkprof <trace file> <output file> explain=userid/pwd@database
Example:
tkprof file1234.trc file1234.out explain=<apps/apps>
5.    After collecting the trace file and TKPROF, please send it to Support engineer to help you identify the problem with the concurrent process.



Long Running Concurrent Requests in 11i

How to Monitor Long Running Concurrent Requests in Oracle 11i

Use following Script to monitor long running concurrent requests in Oracle 11i



------------Start of Script-------------------------------------
#!/bin/sh
# Long Running Concurrent Requests
#


SPOOL_FILE=long_runn_conc.html
FINAL_FILE=Long_Running_Concurrent.html


pchk1=`ps -ef | grep ora_smon_$ORACLE_SID | grep -v grep | wc -l`


if [ "$pchk1" -eq 0 ]; then
     echo "WARNING: Possible database shutdown problem"
     exit 0
fi


echo "Content-Type: text/html" >> ${FINAL_FILE}
sqlplus -s apps/apps EOF
set echo off
set pagesize 120
set markup html on spool on
spool ${SPOOL_FILE}


TITTLE CENTER 'Concurrent Requests running for more than 20 minutes'


SELECT gv.inst_id "Instance Number",
gv.sid "Sid",
gv.serial# "Serial#",
fcr.request_id "Request ID",
substr(fcr.program,1,40) "Program" ,
fcr.phase "Phase",
fcr.status "Status",
to_char(fcr.actual_start_date,'DD-MON-RR HH24:MI:SS') "Start",
to_char(fcr.actual_completion_date,'DD-MON-RR HH24:MI:SS') "End",
ROUND( ( NVL( fcr.actual_completion_date, sysdate ) - fcr.actual_start_date ) *60*24, 2 ) "Time(Min)",
fcqtl.user_concurrent_queue_name "Concurrent Manager",
fcr.user_name "User Name",
substr(gvw.event,1,30) "Event"
FROM apps.fnd_amp_requests_v fcr,
gv$session gv,
gv$session_wait gvw,
fnd_concurrent_queues_tl fcqtl,
fnd_concurrent_processes fcproc
WHERE Phase_code ='R'
and fcr.oracle_session_id=gv.audsid(+)
and gv.sid=gvw.sid(+)
and gv.inst_id=gvw.inst_id(+)
and fcr.controlling_manager = fcproc.concurrent_process_id
and fcproc.queue_application_id = fcqtl.application_id
and fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
and fcqtl.language='US'
and ROUND( ( NVL( fcr.actual_completion_date, sysdate ) - fcr.actual_start_date ) *60*24, 2 ) > 20
order by 9 desc;


spool off
set markup html off spool off
EOF


if [ `grep -c 'no rows selected' ${SPOOL_FILE}` -eq 1 ]
 then
   rm ${FINAL_FILE}
   rm ${SPOOL_FILE}
 exit 0
fi


cat ${SPOOL_FILE} | grep -v 'rows selected' >> ${FINAL_FILE}


(echo "Importance: High"; echo "Subject: VGOP: Long Running Concurrent Requests"; cat ${FINAL_FILE})  | /usr/sbin/sendmail -F VGOP dbadmin@activision.com


rm ${FINAL_FILE}
rm ${SPOOL_FILE}

-------------End of Script-------------------------------------


Use following SQL to get more information.


SELECT   gv.inst_id "Instance Number", gv.SID "Sid", gv.serial# "Serial#", fcr.request_id "Request ID", SUBSTR (fcr.program, 1, 40) "Program", fcr.phase "Phase", fcr.status "Status",
         TO_CHAR (NEW_TIME (fcr.actual_start_date, 'GMT', 'PDT'), 'DD-MON-RR HH24:MI:SS' ) "Start",
         TO_CHAR (NEW_TIME (fcr.actual_completion_date, 'GMT', 'PDT'), 'DD-MON-RR HH24:MI:SS' ) "End",
         ROUND (  (  NVL (fcr.actual_completion_date, SYSDATE)
                   - fcr.actual_start_date )* 60* 24,2) "Time(Min)",
         fcqtl.user_concurrent_queue_name "Concurrent Manager",
         fcr.user_name "User Name",
         (CASE
             WHEN gvw.event = 'latch free'
                THEN (SELECT vl.NAME || ': Latch Name'
                        FROM v$latch vl
                       WHERE vl.latch# = gvw.p2)
             ELSE SUBSTR (gvw.event, 1, 30)
          END
         ) "Event",
         (CASE
             WHEN (gvw.event = 'db file sequential read' or gvw.event ='gc buffer busy')
             AND dbo.object_name IS NULL
                THEN 'Rollback Segment'
             ELSE dbo.object_name
          END
         ) "Database Object",
         fcr.argument_text, gvw.p1, gvw.p2, gv.sql_id,gp.spid
    FROM apps.fnd_amp_requests_v fcr,
         gv$session gv,
         gv$process gp,
         gv$session_wait gvw,
         dba_objects dbo,
         fnd_concurrent_queues_tl fcqtl,
         fnd_concurrent_processes fcproc
   WHERE phase_code = 'R'
     AND gv.paddr    = gp.addr(+)
     and gv.inst_id=gp.inst_id(+)
     AND fcr.oracle_session_id = gv.audsid(+)
     AND gv.SID = gvw.SID(+)
     AND gv.inst_id = gvw.inst_id(+)
     AND gv.row_wait_obj# = dbo.object_id(+)
     AND fcr.controlling_manager = fcproc.concurrent_process_id
     AND fcproc.queue_application_id = fcqtl.application_id
     AND fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
     AND fcqtl.LANGUAGE = 'US'
 ORDER BY 10 DESC;




Concurrent request running from a very long time

Issue: Concurrent request running from a very long time 

 

Before confirming a concurrent Request to long running we need to conclude/observe some of the things

•Check the Statistics,History Runs of that program & Based on this timelines we need to decide the long running Request.
•Check The load on DB node to ensure that High Resource Usage is not the Main cause.i troubleshoot
•Check DB locks in The Database To Ensure that This Session is not blocked by any other session.’
•Check wether Same Iteration Of concurrent program is running on Instance. like if concurrent request is running twise/trice
If we are Happy/resolved the above things and find that we dont have any problem Then we need to Digg

long running concurrent Request

Step 1 : Get Concurrent Request ID of long running concurrent request from Application(fronted).

Navigation : Application >> System administrator>> concurrent>request>

Step 2 : Find session associated with concurrent request .i.e SID

By using the above Query we can get sid,serial#,spid of the concurrent Request..

SELECT a.request_id, d.sid, d.serial# , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = ‘R’;


In My case The Concurrent Request id is 678901

Output of above query returned

REQUEST_ID SID SERIAL# SPID
———————————————————–
678901 1973 89 1100
Here Conurrent Request id is 678901 & Asoociated Session is 1973.

SPID is the process that Running on DB node Because of This Request..

oracle@acer $ ps -ef|grep -i 1100

oracle1100 1 0 3:30:43 0:03 oracle (LOCAL=NO)

Note : before Using Oradebug make sure that spid shoud exist on DB node..

Step 3 : Enable event 10046 trace with level 12 using oradebug .

Syntax : oradebug setospid SPID

Here SPID is the process id that we are getting from step 2

Coming to My Case
SQL> oradebug setospid 1100

Oracle pid: 79, Unix process pid: 1100, image: oracle@Acer


Step 3.3 : Enable trace for 10046 event with level 12
SQL> oradebug event 10046 trace name context forever, level 12

Step 3.4 : Locate Trace file as
SQL>oradebug tracefile_name

/ORADB/acerdb/9.2.0/admin/DELL_ACER/udump/ACER_ora_1100.trc

Wait for 30 Min to get the Trace file to find the Root Cause

Step 4 : Disable trace if u r thinking that trace is enough to find the root cause..

SQL> oradebug event 10046 trace name context off

Step 5 : Convert raw trace To Understandable tracefile By using tkprof/Traceanalyzer

tkprof ‘ /ORADB/acerdb/9.2.0/admin/DELL_ACER/udump/ACER_ora_1100.trc’ ’ /ORADB/acerdb/9.2.0/admin/DELL_ACER/udump/ACER_ora_1100.txt ’ explain=apps/[apps_passwd] prsela.

Step 6 : Check TKPROF out file to find root cause of slow concurrent request.

 

********************************************************************************
.
Application version: 11.5.10.2 multi-node (4 nodes)
Database: 11g RAC


Scenario:

Business critical concurrent request is running from more than 2 hours which was supposed to complete within 1 hour.
The standard manager and the manager responsible to execute the critical concurrent request (custom manager) were overloaded with many requests. The server on which these managers run had very high server load and all CPU’s were utilized.

After diagnosis, we had to kill few requests which were running from more than 2 hours and were creating load on the server. These requests were in fact not performing anything and had gone zombie.

The server load came down and CPU’s available were set free to execute new requests/processes.

However the business critical request was still running and wasn’t moving ahead at all. The database session associated was also not executing anything.

Challenge:

Since this being a critical business request, we couldn’t terminate the request coz we had already lost few hours running it. At the same time the database session also wasn’t performing any execution and the whole request was not going anywhere even though it showed running normal status.

Trick:

Not recommended, however can be a life saver at times.

---- Forcibly change the status of the request to pending normal.
---- Kill the database session of the request.

Use the below scripts:

update applsys.fnd_concurrent_requests set phase_code = 'P',
                                           status_code = 'I',
                                           actual_start_date = null,
                                           crm_release_date = null,
                                           controlling_manager=null,
                                           logfile_name = null,
                                           logfile_node_name = null,
                                           outfile_name = null,
                                           outfile_node_name = null,
                                           crm_tstmp = null
where request_id in ('request-id');


             alter system kill session 'sid, serial#, @instance-id';


Hence a new database session will get created as soon as the old one is killed and the request starts running. Since the server on which the request is supposed to get processed also had no issues now, the request went through very fast without any issues ;)
********************************************************************************

ORA-00904

PPR Error: ORA-00904 [ID 1311855.1]


Applies to:

Oracle Payables - Version: 12.0.0 to 12.1.3 - Release: 12.0 to 12.1
Information in this document applies to any platform.
PPR Payment Batch

Purpose

The purpose of this note is to explain steps in diagnosing and troubleshooting the ORA-00904 database error in the EBS Payables module, Payment Batch(PPR).  This note attempts to step the reader through all known setup issues as well as known bugs that can cause this error to occur. 


Note:  If you are on older code versions (10.7, 11.0, 11.5.0 - 11.5.9, 12.0.0 - 12.0.5), your solution may not be included in this article.  Please expand your search to include the archived solutions using the selection method shown below


Last Review Date

March 13, 2011

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details


1) Build Payments process

#ReleasePresentation / Error Stack Root CauseFixed File (or fix)Recommended Patch
112.0The Build Payments Process fails with:

ERROR
FV_FEDERAL_PAYMENT_FIELDS_PKG.GET_PAY_INSTR_SEQ_NUM: Payment Instruction
Sequence Assignment not set for org_id = 0 and payment_reason_code.
When the Build was run in debug mode, the log file showed:
ORA-00904: "PAYEE_BANK"."BRANCH_NUMBER": invalid
Build program errors out with - IBY_EXT_BANK_ACCOUNTS does not have the column BRANCH_NUMBER. This issue is because in the payment process profile, the branch_number is provided as a sort option which is erroring out with the code for ibypymib.pls.

BUG 8291512 ORA-00904: "PAYEE_BANK"."BRANCH_NUMBER": INVALID IDENTIFIER
Resolved by application of latest version of Package - IBY_PAYINSTR_PUB

ibypymib.pls 120.74.12000000.15
R12.1.x - Patch 10630448:R12.IBY.B

R12.0.x - Patch 11872821:R12.IBY.A
212.0When the customer creates a PPR for an AR refund invoice the build program fails withe the error:


ERROR
IBYBUILD module: Build Payments
BUILD PROGRAM ERROR - CANNOT INSERT DOCUMENTS FOR PAYMENT SERVICE REQUEST
IBY_DISBURSE_SUBMIT_PUB_PKG.insert_payreq_documents: Exception occurred when attempting to insert documents for calling app id 200, calling app payment service request id PRF7_Patch_Rebate
IBY_DISBURSE_SUBMIT_PUB_PKG.insert_payreq_documents: SQLCODE: -904
IBY_DISBURSE_SUBMIT_PUB_PKG.insert_payreq_documents: SQLERRM: ORA-00904: "GLOBAL_ATTRIBUTE20": invalid identifier
IBY_DISBURSE_SUBMIT_PUB_PKG.insert_payreq_documents: EXIT
Prerequisites of the Patch 6811355 were not appliedResolved by application of latest version of Package - IBY_VALIDATIONSETS_PUB

ibyvallb.pls. 
R12.1 - Patch 11819029:R12.IBY.B

R12.0 - Patch 11806195:R12.IBY.A
312.0When creating payment process request the following error is received:

Error:
IBY_VALIDATIONSETS_PUB.applyDocumentValidationSets: ORA-01422: exact fetch
returns more than requested number of rows
Payment format error when same bank account is attached to two or more different suppliers..

Bug 9248800 AMER:PEN:R12:PYMT FORMAT FAILS SAME BANK ACCT ATTACHED TO DIFFERENT SUPPLIERS
Resolved by upgrading to the latest version of IBY_VALIDATIONSETS_PUB.
ibyvallb.pls
R12.1 - Patch 11819029:R12.IBY.B

R12.01 - Patch 11806195:R12.IBY.A

2) Format Payments process

#ReleasePresentation / Error Stack Root CauseFixed FileRecommended Patch
112.0The program Payment Format Instructions ends in error.
User is getting the following error when an invoice
selected for payment in PPR has interest and the PPR is
creating the invoice interest:

ERROR
APP-SQLAP-10000: ORA-1422: exact fetch returns more than
requested number of rows
From FND Debug Log File
IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : Params not passed to callout - completed pmts count: 1, completed docs count: 2
IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : Fatal: External app callout 'AP_PMT_CALLOUT_PKG.payments_completed', generated exception.
IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : SQL code: -20001
IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : SQL err msg: ORA-20001: APP-SQLAP-10000: ORA-01422: exact fetch returns more than requested
IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : Exception occured when marking payments complete for payment instruction 22438
IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : SQLCODE: -20001
IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : SQLERRM: ORA-20001: APP-SQLAP-10000: ORA-01422: exact fetch returns more than requested numb
IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : EXIT
The issue is that MOAC is not happening properly. While running the program, multi-org access is set but when we are querying the ap_system_parameters table to retrieve profile values, we are going to the secured synonym. As such the query is returning multiple rows. We should go to the base table and query up by the org_id of the invoice. Resolved by upgrading to the latest version of package - AP_INTEREST_INVOICE_PKG

apintinb.pls
R12.1 - Patch 9328384:R12.AP.B

R12.01 - Patch 11821019:R12.AP.A

Standard Patching Process

Please consult as well the recommended patch lists for Payables. The list includes consolidated patches that contain fixes for issues that had been critical:

For the R12.0.x codeline, see Note 557869.1
For the R12.1.x codeline, see Note 954704.1

Patch Wizard

Important: While a patch may contain several hundred files, the impact on your system is most likely far less. Patches contain hard-prerequisites that are intended to make a patch work on a very low code baseline.

The Patch Wizard Utility will give you a detailed impact analysis for the patch above, please refer to
the Note 976188.1 for more information on the utility.

APP-FND-01564,ORA-01116,ORA-01110,ORA-27041.

Click to add to Favorites


Applies to:

Oracle Application Object Library - Version 12.0.1 to 12.0.4 [Release 12]
Oracle Concurrent Processing - Version 12.0.4 to 12.1.3 [Release 12 to 12.1]
Linux x86
***Checked for relevance 20-Jun-2011***


Symptoms

When running any concurrent requests or request sets in Oracle Applications Release 12, the following error would appear:

APP-FND-01564: ORACLE error -1116 in SUBMIT: others

Cause: SUBMIT: others failed due to ORA-01116: error in opening database file {number}
ORA-01110: data file {number}: '/{directory path}/{file}.dbf'
ORA-27041: unable to open file
Linux Error: 24: Too many open files
Additional information: 3.

The SQL statement being executed at the time of the error was:
&SQLSTMT and was executed from the file &ERRFILE.

Cause

The error would appear if the output of the lsof command is greater than the value of the ulimit.
Use the following command to check the number of open files used by applmgr:
$ lsof -u applmgr | wc -l

Compare the result to the value in ulimit:

$ ulimit -n

Solution

Modifying the Number of Open File Descriptors

Open the /etc/security/limits.conf file and change the existing values for "hard" and "soft" parameters as follows. Restart the system after making changes.

Note:
If the current value for any parameter is higher than the value listed in this document, then do not change the value of that parameter.

  * hard nofile 65535
  * soft nofile 4096
  * hard nproc 16384
  * soft nproc 2047

Note:
* means all users listed in /etc/passwd would inherit the above values.

APP-FND-00798 and APP-FND-00730




Applies to:

Oracle General Ledger - Version 12.0 to 12.0 [Release 12.0]
Information in this document applies to any platform.
Standard Reports
***Checked for relevance on 7-Feb-2012***


Symptoms

When submitting the Account Analysis Report, the following errors occur:

Error
------
APP-FND-00798 : Invalid reference gl_srs_coa_id_bysob in valueset attached to segment flexfield
form.

APP-FND-00730 This flexfield has an invalid valueset

Changes

None

Cause

This is the Value for GL_SRS_LEDGER_FLEXFIELD Value set:


FND POPIDR APPL_SHORT_NAME="SQLGL" CODE="GL#"
NUM=:$FLEX$.GL_SRS_COA_ID_BYSOB REQUIRED="Y"
VALIDATE="NONE" SEG=":!VALUE" DESC=":!MEANING"
NAVIGATE="!DIR" VDATE=""


FND VALIDR APPL_SHORT_NAME="SQLGL" CODE="GL#"
NUM=:$FLEX$.GL_SRS_COA_ID_BYSOB VALIDATE="NONE"
REQUIRED="Y" DESC=":!MEANING" SEG=":!VALUE" VDATE=""
The value "GL_SRS_COA_ID_BYSOB" caused the issue.

Solution

To implement the solution, please execute the following steps:


Go to Setup > Financials >  Flexfields > Validation > Sets
Query the value set name "GL_SRS_LEDGER_FLEXFIELD" and click on 'Edit Information' button on the Value validation section.
Change the validation of GL_SRS_LEDGER_FLEXFIELD to the following value:

FND POPIDR APPL_SHORT_NAME="SQLGL" CODE="GL#"
NUM=:$FLEX$.GL_SRS_COA_UNVALIDATED REQUIRED="Y"
VALIDATE="NONE" SEG=":!VALUE" DESC=":!MEANING"
NAVIGATE="!DIR" VDATE=""


FND VALIDR APPL_SHORT_NAME="SQLGL" CODE="GL#"
NUM=:$FLEX$.GL_SRS_COA_UNVALIDATED VALIDATE="NONE"
REQUIRED="Y" DESC=":!MEANING" SEG=":!VALUE" VDATE=""
Retest the issue

RW-50004

Click to add to Favorites


Applies to:

Oracle Applications Manager - Version: 12.0.0 to 12.0.4
Microsoft Windows

Symptoms

When attempting to install Oracle Applications Release 12 on windows platform
using RapidWiz.

RapidWiz fails at 66 % with the following error:

ERROR
RW-50004: Error code received when running external process.
    Check log file for details.
    Running Database Install Driver for  instance

The log file displays the following error:

  [SETUP PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: %ORACLE_HOME\appsutil\install\%CONTEXT_NAME%
      adsvdlsn.cmd            INSTE8_SETUP       9020

File adsvdlsn.cmd will give the following error at the end:

ERRORCODE = 9020 ERRORCODE_END
.end std out.
The system cannot execute the specified program.
The system cannot execute the specified program.

Cause

The executable Wait.exe delivered with Oracle Applications have a dependency at the file MSVCR80.dll.
The cause of this issue is included in unpublished Bug
5491142 -  QREP120.9:RI CAN NOT RUN EXE PLACED IN DBOH\APPSUTIL\BIN

 It is caused by a missing dll file MSVCR80.dll

For 11i VC++ 6.0 have to be installed.
For R12 Microsoft VS 2005 (VC++ 8.0) have to be installed.
Microsoft VS 2005 (VC++ 8.0) will install the missing dll MSVCR80.dll. 

Solution

To implement the solution, please execute the following steps:

1 - Remove Microsoft VC++ 6.0
2 - Obtain and Install Microsoft VS 2005 (VC++ 8.0)
3 - Use the retry Button to restart and finish the installation.

Or use the following option to get R12 Installed without installing Microsoft VS 2005 (VC++ 8.0). This option can be used in case you are installing the dbTier only or you want to have the R12 installation completed without error:

4 - Obtain and install Microsoft Visual C++ 2005 Redistributable Package (x86)
5 - Use the retry Button to restart and finish the installation.

Keep in mind the Microsoft VS 2005 (VC++ 8.0) have to be installed to get the relink working at the Apps Tier.

ORA-01422

PPR Error: ORA-01422 Exact Fetch Returns More Than Requested Number Of Rows [ID 1305446.1]


Applies to:

Oracle Payables - Version: 12.0.0 to 12.1.3 - Release: 12.0 to 12.1
Information in this document applies to any platform.
PPR Payment Batch

Purpose

The purpose of this note is to explain steps in diagnosing and troubleshooting the ORA-01422 database error in the EBS Payables module, Payment Batch(PPR).  This note attempts to step the reader through all known setup issues as well as known bugs that can cause this error to occur. 


Note:  If you are on older code versions (10.7, 11.0, 11.5.0 - 11.5.9, 12.0.0 - 12.0.5), your solution may not be included in this article.  Please expand your search to include the archived solutions using the selection method shown below


Last Review Date

April 28, 2011

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details


The table below details scenarios that have been known to cause this symptom in the Payment Processing Request (PPR Process).

1) Build Payments process

#ReleasePresentation / Error Stack Root CauseFixed File (or fix)Recommended Patch
112.0.6
12.1.1
When attempting to execute the Build program , gets the following error:
the following error occurs:
Error:
R12 AMER : BUILD PROGRAM ERROR - CANNOT VALIDATE DOCUMENTS
From FND Debug Log
------------------
IBY_VALIDATIONSETS_PUB.applyDocumentValidationSets: Bank Acct is not present at supplier site level
IBY_VALIDATIONSETS_PUB.applyDocumentValidationSets: No Address OU level Payee Exists
IBY_VALIDATIONSETS_PUB.applyDocumentValidationSets: No Address level Payee Exists
IBY_VALIDATIONSETS_PUB.applyDocumentValidationSets: ORA-01422: exact fetch returns more than requested number of rows
Exception occured when validating documents. Document validation will be aborted and no document statuses will be committed for payment request 4606
Exception occured when validating documents. Document validation will be aborted and no document statuses will be committed for payment request 4606
SQLCODE: 1
SQLCODE: 1
SQLERRM: User-Defined Exception
SQLERRM: User-Defined Exception
Build program error: Exception occured when attempting to validate the documents payable of the provided payment service request.
Document Validation errors out while validating supplier
bank account assignment in following cases:

1) If party has more than one Address-OU or Address-level
payees and bank account which is attached to document is
not assigned to a supplier site.

2) If a bank account is attached to more than one payee
across the hierarchy.

Bug 9248800 AMER:PEN:R12:PYMT FORMAT FAILS SAME BANK ACCT ATTACHED TO DIFFERENT SUPPLIERS

BUG 9914416 - ORA-1422 IBY_VALIDATIONSETS_PUB.APPLYDOCUMENT
VALIDATIONSETS BUILD PROGRAM
iby patch/115/sql ibyvallb.pls 120.60.12010000.25 or higherR12.1.x:
Patch.9966455:R12.IBY.B
No password is required.

NOTE:
As of 4/14/2011, the latest versions of file ibyvallb.pls can be found on :

R12.0.x:
Patch 11806195:R12.IBY.A
Contact Oracle Support for password.

R12.1.x:
Patch.11819029:R12.IBY.B
Contact Oracle Support for password.

2) Format Payment Instructions process

#ReleasePresentation / Error Stack Root CauseFixed FileRecommended Patch
112.0The program Payment Format Instructions ends in error.
User is getting the following error when an invoice
selected for payment in PPR has interest and the PPR is
creating the invoice interest:

ERROR
APP-SQLAP-10000: ORA-1422: exact fetch returns more than
requested number of rows
From FND Debug Log File
IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : Params not passed to callout - completed pmts count: 1, completed docs count: 2
IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : Fatal: External app callout 'AP_PMT_CALLOUT_PKG.payments_completed', generated exception.
IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : SQL code: -20001
IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : SQL err msg: ORA-20001: APP-SQLAP-10000: ORA-01422: exact fetch returns more than requested
IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : Exception occured when marking payments complete for payment instruction 22438
IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : SQLCODE: -20001
IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : SQLERRM: ORA-20001: APP-SQLAP-10000: ORA-01422: exact fetch returns more than requested numb
IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : EXIT
The issue is that MOAC is not happening properly. While running the program, multi-org access is set but when we are querying the ap_system_parameters table to retrieve profile values, we are going to the secured synonym. As such the query is returning multiple rows. We should go to the base table and query up by the org_id of the invoice. Resolved by upgrading to the latest version of package - AP_INTEREST_INVOICE_PKG

apintinb.pls
R12.1 - Patch 9328384:R12.AP.B

R12.01 - Patch 11821019:R12.AP.A
@ Original patches reported to fix (use in a pinch if customer cannot apply latest code)


Standard patching Process


Please consult as well the recommended patch lists for Payables, the list includes consolidated patches that contain fixes for issues that had been critical.

For the 12.0.x codeline NOTE 557869.1
For the 12.1.x codeline NOTE 954704.1

Patch Wizard


Important: Whilst a patch may contain several hundred files the impact in your system is most likely far less. Patches contain hard-prerequisites that are intended to make a patch work on a very low code baseline.

The Patch Wizard Utility will give you a detailed impact analysis for the patch above, please refer to the NOTE 976188.1 for more information on the utility.


ORA-06502

R11i/R12: Payments Workbench Error: ORA-06502 PL/SQL: numeric or value error (APXPAWKB.FMB) [ID 1303443.1]

Applies to:

Oracle Financials for the Americas - Version: 11.5.10.0 and later   [Release: 11.5 and later ]
Oracle Payables - Version: 11.5.10.0 to 12.1.3   [Release: 11.5 to 12.1]
Information in this document applies to any platform.
Form:APXPAWKB.FMB - Payment Workbench

Purpose

Troubleshooting Payment Workbench Errors: Comprehensive Listing of Errors and Solutions >Note 1303443.1

The purpose of this note is to explain steps in diagnosing and troubleshooting the ORA-06502database error in the EBS Payables module, payment workbench.  This note attempts to step the reader through all known setup issues as well as known bugs that can cause this error to occur. 


Note:  If you are on older code versions (10.7, 11.0, 11.5.0 - 11.5.9, 12.0.0 - 12.0.5), your solution may not be included in this article.  Please expand your search to include the archived solutions using the selection method shown below


Last Review Date

April 11, 2011

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details


     1) Payment Entry

#ReleasePresentation / Error Stack Root CauseFixed File (or fix)Recommended Patch
112.0When trying to pay in full with a quick payment an invoice whose invoice
number has 46 characters or more the following error happens:


APP-SQLAP-10000: ORA-06502:
PL/SQL: numeric or value error occurred in
ap_calculate_interest<-AP_PAY_IN_FULL_PKG.CREATE_SINGLE_PAYMENT
<-AP_PAY_IN_FULL_PKG.AP_CREATE_PAYMENTS
<-APXPAWKB with parameters
(Invoice_id = ...Payment_num =1, ...Currency_code = CHF)
 while performing the following operation:
Get the interest Invoice Num

Bug 7612309 APXPAWKB: ORA-06502 IN AP_CALCULATE_INTEREST WHEN DOING PAY IN FULL and fixed on fileapayfulb.pls 120.16.12000000.4Patch 11772495
211.5Paying a single payment the following error is encountered:

"APP-SQLAP-10000 ORA-06502-PLSQL NUMBERIC Or VALUE ERROR
character string buffer too msall
occurred in ap-calculated_interest
ap_pay_in_full.pkg.get_singlePayment amount->
ap_pay_in_full_pkg(ap_create_payments)
Bug 4744701: Error When Try To Pay In Full Invoice With Long Invoice_Num Ora-06502 APXINWKB.fmb 115.858Apply latest patch from Note 1303110.1
312.0During payment processing , clicking on payment error message is received

ERROR:
======
APP-SQLAP-10000: ORA 06502: PL/SQL NUMERIC OR VALUE ERROR OCCURRED IN
AP_PAY_IN_FULL_PKG.AP_LOCK_INVOICES<--APXPAWKB
With parameters (P_invoice_id=...) while performing the following operation:
Get vendor and currency info for invoice_id: ...

In some cases after the original fix for Bug 7721348 the following error was then encountered in the Invoice Workbench (Tracked via Bug 8374272):

ORA-1403: no data found
FRM-40735:WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-6502

Bug 7721348 ORA-6502: PL/SQL: NUMERIC OR VALUE ERROR IN PAY-IN-FULAPXPAWKB.fmb 120.183.12000000.102 Apply latest patch from Note 1303110.1

Secondary bug is included ad a dependent fix in the latest patch for payment workbench

411.5Argentina Localizations
Create a payment and save, the following error occurs:

APP-JL-62481:
ORA 06502 : PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.JL_ZZ_AP_WITHHOLDING_PKG", line 2533
ORA-01403: no data found occured in
JL_ZZ_AP_WITHHOLDING_PKG.Get_cumulative_supp_exemp
<--JL_ZZ_AP_WITHHOLDING_PKG.Process_Withholding_Name with parameters...
The issue is caused by the following setup: Period definition.

The following script appear in the package that failed.

SELECT start_date, end_date
FROM ap_other_periods
WHERE application_id = 200
AND module = 'AWT'
AND period_type = '&AWT_Period_Type'
AND period_name = '&period_name';
Setup > Calendar > Special Calendar

The tax calculation is only performed after opening periods to this calendar.
Make sure you have an AWT period defined (AWT calendar) and the correct range of dates entered.

Note 198651.1 APXAMDOP How to Define a Special Calendar

512.0Unable to pay invoice using Actions> Pay In Full which throws the following errors:


AP_PAY_IN_FULL_PKG is Invalid.

AP_PAY_IN_FULL_PKG
PLS-00323: subprogram or cursor 'AP_LOCK_INVOICES' is declared in a package specification and must be defined in the package body

ORA-6502: PL/SQL: NUMERIC OR VALUE ERROR
Bug 7721348 ORA-6502: PL/SQL: NUMERIC OR VALUE ERROR IN PAY-IN-FULL

Package specification out of synch with package body
apayfulb.pls-120.16.12000000.7

apayfuls.pls-120.7.12000000.3
Patch 7721348
611.5When trying to pay an invoice in full for a supplier setup to charge interest, the following error happens:


APP-SQLAP-10000 ORA-1722 invalid number occured in
ap_calculate_interest<
AP_PAY_IN_FULL_PKG.GET_SINGLE_PAYMENT_AMOUNT<
AP_PAY_IN_FULL_PKG.
AP_GET_CHECK_AMOUNT .....
while performing the following operation :
Calling custom interest package

APP-SQLAP-10000 ORA-06502 AP_CALCULATE_INTEREST
Bug 6114459

Similar report in Bug 4254258 was fixed in AP.N
apcstiib.pls 115.5Patch 6238802
(Requires AP.O)
712.1When creating a single payment with payment method that have a length greater than 25 characters and attempting to pay in full, error message is received.

ERROR:
======

-> APP-SQLAP-10000: ORA-6502 : numeric or value error occured in
-> AP_PAY_IN_FULL_PKG.AP_LOCK_INVOICES<-APXPAWKB with
-> parameters (P_invoice_id_list = 21750) while performing the following
-> operation: Get vendor and currency info for invoice_id: ...
Bug 9109346 TST1212.XB5.QA:NOT ABLE TO SINGLE PAY AFTER PAYMENT METHOD NAME IS TOO LONG

Bug 9113244 (12.1 fix)
APXPAWKB.fmb 120.183.12000000.180

APXPAWKB.fmb 120.223.12010000.136
Apply latest patch from Note 1303110.1
@ Original patches reported to fix (use in a pinch if customer cannot apply latest code)

     2) Void / Refund Processing

#ReleasePresentation / Error Stack Root CauseFixed FileRecommended Patch
112.1Federal Financials:

When an interest invoice that is paid alone through a
check, and the check is void attempted with invoice action
set to CANCEL, the following unhandled database error
is thrown:


APP-SQLAP-10000: ORA-06502: PL/SQL: numeric or value
error occurred in:
AP_CANCEL_PKG.AP_CANCEL_INVOICES<-AP_VOID_PKG.AP_REVERSE_CHECK
<-APXPAWKB-> with parameters (......)
while performing the following operation:
Open invoices_cursor and do bulk fetch
Bug 11781836 ORA-06502: WHEN VOIDING PAID-ALONE INTEREST INVOICE CHECK WITH CANCEL ACTIONapicancb.pls 120.35.12010000.21Patch 11781836

     3) Invoice Selection

#ReleasePresentation / Error Stack Root CauseFixed File (or fix)Recommended Patch
111.5When attempting to add an invoice to payment batch, the following error occurs:

APP-SQLAP-10000: ORA-06502 PL/SQL numeric or value error character string too small occurred in
ap_calculate_interest<-Pay Invoice Forms<-Bat Mdfy Pdet Item.
Bug 5858773 Apxpawkb Add Invoice To Payment Batch With Error APP-SQLAP-10000: ORA-6502APXPAWKB.fmb 115.501 Apply latest patch from Note 1303110.1
211.5 - 12.0Payment workbench throws error when loading/entering large number of invoices in Enter/Adjust invoice and attempting quick payment.

ERROR
-----------------------
ORA-01403: no data found
FRM-40735: WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-06502.
Bug 8420992 and Bug 9601535 APXPAWKB FRM-40735:WHEN-VALIDATE-ITEM TRIGGER UNHANDLED EXCEPTIONQuick Payments do not support large volume invoice processing. (300+) Users are required to use PPR for payment processing when large-numbers of invoices are involved.

Can occur in 11i with as few as 100 invoices.
No Patch Available
@ Original patches reported to fix (use in a pinch if customer cannot apply latest code)

ORA-01403




Applies to:

Oracle Assets - Version 12.0.6 to 12.0.6 [Release 12]
Information in this document applies to any platform.
FAMAPT


Symptoms

The Post Mass Additions program is erroring with the following error message on certain records Life in Months:


Cause: You have a database problem.
Action: Contact your system administrator.

ORA-01403: no data found Error: function fa_cache_pkg.fazccmt returned failure Cache:
Error retrieving row from FA_METHODS table Depreciation Method: STL Life in Months: Cause: You have a database problem.
Action: Contact your system administrator.
ORA-01403: no data found Error: function fa_cache_pkg.fazccmt returned failure
Error: function fa_asset_calc_pvt.calc_deprn_info returned failure
Error: function fa_asset_calc_pvt.calc_fin_info returned failure
Error: function fa_addition_pub.do_addition returned failure Mass Addition ID: 82556 ==> ** Failed **
Error: function fa_massadd_pkg.do_mass_addition returned failure Cache:
Error retrieving row from FA_METHODS table Depreciation Method: STL Life in Months: 
Also included many times:
Please enter a Date Placed In Service that falls in the current or a prior period.

Examples
Mass Addition ID: 81058 ==> ** Failed **
Mass Addition ID: 81377 ==> ** Failed **
Mass Addition ID: 81418 ==> ** Failed **

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Note:
The depreciation methods STL and related asset lifes had been setup already.
Eg. STL 12 months or STL 36 months were in place.
Scripts to verify:
To verify the Mass Addtions' their methods and related lifes:
select FMA.mass_addition_id, FMA.asset_category_id , FMA.deprn_method_code, FMA.life_in_months, FM.method_code, FM.life_in_months, FM.stl_method_flag
from FA_MASS_ADDITIONS FMA , FA_METHODS FM 
where mass_addition_id in (82556,81058,81377,81418) -- from the FAMAPT logfile
and book_type_code = '&book_type_code''
and FMA.deprn_method_code=FM.method_code;
To verify what had been setup in Oracle Fixed Assets:
select distinct method_code, life_in_months
from FA_METHODS
where method_code = 'STL';

Cause

Relevant triggers on FA_MASS_ADDITIONS had been disabled:
JAI_FA_MA_ARIUD_T1
JAI_FA_MA_BRIUD_T1



Comments
-----------
Triggers ought to be valid in order to have the application work as expected.

Solution

To implement the solution, please execute the following steps:

1. Backup below mentioned triggers, if needed consult with your DBA for further assistance.

2. Perform the following in a test environment.

alter trigger JAI_FA_MA_ARIUD_T1 enable;
alter trigger JAI_FA_MA_BRIUD_T1 enable;

3. Retest the issue.

4. Migrate the solution as appropriate to other environments.

FRM-92101

"FRM-92101: There Was a Failure in the Forms Server During Startup" Error When Loading Forms [ID 1103744.1]


Applies to:

Oracle Applications Technology Stack - Version: 12.0.6 to 12.1.3 - Release: 12.0 to 12.1
Oracle Applications Technology Stack - Version: 12.0.6 to 12.1.3   [Release: 12.0 to 12.1]
This problem can occur on any platform.

Symptoms

Intermittently E-Business suite forms sessions are failing with the error

Error
FRM-92101 : There was a failure in the Forms Server during startup. This could happen due to invalid configuration.

Steps to Reproduce

The issue can be reproduced at will with the following steps:

1:- Log into the E-Business suite.
2:- Choose a responsibility and click a function which loads forms (any form)

Business Impact

The issue has the following business impact:
Due to this issue, forms sessions are being disconnected for up to 100 users.

Changes

Implemented a shared application tier file system (shared APPL_TOP)

Cause

The OPMN process pings the HTTP server process to check that it is still alive. If OPMN does not get a response from the HTTP server in the timeframe configured by the E-Business suite, it presumes that the HTTP server is down and restarts it, affecting all users connected to that application server.

In the $LOG_HOME/ora/10.1.3/opmn/opmn.log we can see that OPMN is restarting the HTTP server because it has become unresponsive.

10/02/16 12:08:22 [libopmnohs] Process Ping Failed:
HTTP_Server~HTTP_Server~HTTP_Server~1 (241450421:15863) [The connection
receive timed out]
10/02/16 12:09:13 [libopmnohs] Process Ping Failed:
HTTP_Server~HTTP_Server~HTTP_Server~1 (241450421:15863) [The connection
receive timed out]
10/02/16 12:09:13 [libopmnohs] Process Unreachable:
HTTP_Server~HTTP_Server~HTTP_Server~1 (241450421:15863)
10/02/16 12:09:15 [pm-process] Stopping Process:
HTTP_Server~HTTP_Server~HTTP_Server~1 (241450421:15863)
10/02/16 12:09:23 [pm-process] Starting Process:
HTTP_Server~HTTP_Server~HTTP_Server~1 (241450421:0)

The reason that OPMN is restarting the HTTP server is that a shared APPL_TOP is being used, but the recommended settings for the Network-attached storage (NAS) file system have not been implemented.

As the NAS mount gets busier, the HTTP server process becomes swamped and does not respond to OPMN. OPMN then restarts the process, causing all forms users connected to that Application server to fail with the FRM-92100 error.

Self service session users are much less likely to notice the issue as most self service application screens are stateless HTML, so no context is lost when the HTTP server is bounced.

Solution

To implement the solution, please execute the following steps:

1:- Stop ALL middle tier services
2:- Update the $CONTEXT_FILE and change "s_lock_pid_dir" to a directory on a local disk.

For example

<lock_pid_dir oa_var="s_lock_pid_dir">/d01</lock_pid_dir>

3:- Run autoconfig

4:- Ensure that the NAS device is configured with the correct mount options.

On Linux, it should be using "nolock" option.
On Solaris, it should be using "llock" option.

5:- Start ALL middle tier services
6:- Retest the issue.
7:- Migrate the solution as appropriate to other environments.

ORA-01438



Applies to:

Oracle Assets - Version 12.1.2 and later
Information in this document applies to any platform.
Checked for relevance on 14-Jun-2011


Symptoms

While uploading assets through WEBADI, the import process fails with below error message.

Error
-----
 "Cannot Execute SQL statement".
BNE Log
----------
Web ADI Upload Job 61129 ERROR BneBaseSQL.executeUpdate: Exception while running query.  Error Code: 1438, Message: ORA-01438: value larger than specified precision allowed for this column.
Web ADI Upload Job 61129 ERROR          BneBaseSQL.executeUpdate: Query: INSERT INTO FA_MASS_ADDITIONS(INVOICE_NUMBER,ALLOWED_DEPRN_LIMIT,...

Cause

The cause of this problem has been identified and verified in published Bug 9495140 : WEBADI FAILED WITH ORA-01438 VALUE LARGER THAN SPECIFIED PRECISION

The LIFE_IN_MONTHS field, in table FA_MASS_ADDITIONS, maximum allowed value is a 4 digit number whereas the entered value exceeds this maximum value.

Solution

Correct the data by changing the life of the asset so life in month does not exceed this maximum allowed value of a 4 digit number.

APP-SQLAP-97731

Invoice Error APP-SQLAP-97731 For an Imported Invoice [ID 855012.1]

Applies to:

Oracle Payables - Version: 12.0.4 and later   [Release: 12.0 and later ]
Information in this document applies to any platform.
zxdwtxcalsrvpubb.pls

Symptoms

-- Problem Statement:
User exported expense reports from iExpense to Payables through Expense Report Export.
One invoice for one supplier receives the following error when you attempt to look at distributions:
"APP-SQLAP-97731: The system cannot generate distributions because tax calculation failed due to
the following error: &TOKEN1"

Then the lines that do show up do not add up to the total of the invoice. Lines are missing.

-- Steps To Reproduce:
Run iExpense to Payables through Expense Report Export.
For one invoice some lines are missing,.
Application returned:
"APP-SQLAP-97731: The system cannot generate distributions because tax calculation failed due to
the following error: &TOKEN1"


Cause

Incorrect version of zxdwtxcalsrvpubb.pls

Issue discussed in Bug 8348107: OE INVOICE VALIDATION ISSUES - TAX CALCULATION ERRORS APP-SQLAP-97731

Issue fixed in the package zxdwtxcalsrvpubb.pls to 120.130.12000000.16

Solution

1) Please download and review the readme for Patch 7537542

2) Please apply patch in a test environment.

3) Please confirm the following file versions:
zxdwtxcalsrvpubb.pls 120.130.12000000.17

4) Please retest the issue.

5) If satisfied then migrate to other environments.

ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE

 Error:- IEX: Scoring Engine Harness Error - ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE (Doc ID 2056754.1) SYMPTOMS:- Yo...