ORA-04063 PACKAGE BODY APPS.AP_INVOICES_PKG HAS ERRORS IN PAYABLES INVOICE WORKBENCH - R12

ISSUE:

ORA-04063 PACKAGE BODY "APPS.AP_INVOICES_PKG" HAS ERRORS

After package compile getting below error

Finally found the issue with APXINWKB form.

Form compile log:

Compiling package body APXINWKB...
Compilation error on package body APXINWKB:
PL/SQL ERROR 49 at line 411, column 52
bad bind variable 'ZX_PARAMETERS.OVERRIDE_STATUS'
PL/SQL ERROR 49 at line 411, column 52
bad bind variable 'ZX_PARAMETERS.OVERRIDE_STATUS'


SOLUTION:

Apply patch 14460965 , patch will change file version to higher and issue should fix.

You can able to create payables invoices now.

THERE IS A PROBLEM WITH THIS WEBSITE'S SECURITY CERTIFICATE IN IE10

ISSUE:

Security exception error in IE10 for SSL certificate URL.

ERROR:

There is a problem with this website's security certificate.

 

This organization's certificate has been revoked.

Security certificate problems may indicate an attempt to fool you or intercept any data you send to the server. 

 

SOLUTION:

1) Go to Tools->Internet Options

2) Click Advanced Tab

3) Scroll down and under security settings we can see below two options that are checked

4) Uncheck those below two boxes

   UnCheck for "publishers certificate revocation".

   UnCheck for "server certificate revocation".

5) Please click on Apply and OK at the bottom of the Advance Tab.

6) Close the Internet explorer or restart the computer.

7) Open the internet explorer.

8) Please open URL and test.It should work.

HOW TO TAKE OUTPUT IN EXCEL FORMAT

-bash-3.2$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 01:23:09 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set markup html on;
SQL> conn apps/apps
<br>
Connected.
<br>
SQL&gt; spool invalids.xls
<br>
SQL&gt; select * from dba_objects where status='INVALID';
<br>
<p>
<table border='1' width='90%' align='center' summary='Script output'>
<tr>
<th scope="col">
ENTITY_ID
</th>
<th scope="col">
</table>
<p>

SQL&gt; spool off
<br>
SQL&gt; set markup html off;
<br>

Product Installation Status, Version and Patch Level Details

col application_id format 99990 heading "ID"
col application_name format a40 heading "Name"
col application_prefix format a6 heading "Prefix"
col application_short_name format a10 heading "Short name"
col apps format a8 heading "Product"
col install_group_num format 90 heading "Inst Grp"
col installed_flag format a9 heading "Type"
col language_code format a4 heading "Code"
col module_short_name format a8 heading "Module"
col module_version format a8 heading "Version"
col product_group_id format 990 heading "ID"
col product_group_name format a28 heading "Product Group Name"
col product_group_type format a10 heading "Type"
col product_version format a8 heading "Version"
col argument1 format a20 heading "Arguments"
col release_name format a12 heading "Release"
col updated format a11 heading "Updated"
col patchset format a20 heading "Patchset Level"
col status format a14 heading "Appl Status"

prompt --> Product Installation Status, Version ,Patch Level and Update date
select decode(nvl(a.APPLICATION_short_name,'Not Found'), 'SQLAP','AP','SQLGL','GL','OFA','FA',
'Not Found','id '||to_char(fpi.application_id), a.APPLICATION_short_name) apps,
decode(fpi.status,'I','Installed','S','Shared','N','Inactive',fpi.status) status,fpi.product_version,
nvl(fpi.patch_level,'-- Not Available --') Patchset,to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
and fpi.oracle_id = o.oracle_id(+)
order by 1,2

FIND CHILD CONCURRENT PROGRAM REQUESTS FOR PARENT REQUEST

SELECT /*+ ORDERED USE_NL(x fcr fcp fcptl)*/
fcr.request_id "Request ID",
PARENT_REQUEST_ID "Parent ID",
fcptl.user_concurrent_program_name"Program Name",
fcr.phase_code,
fcr.status_code,
-- to_char(fcr.request_date,'DD-MON-YYYY HH24:MI:SS') "Submitted",
-- (fcr.actual_start_date - fcr.request_date)*1440 "Delay",
to_char(fcr.actual_start_date,'DD-MON-YYYY HH24:MI:SS') "Start Time",
-- to_char(fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "End Time",
(fcr.actual_completion_date - fcr.actual_start_date)*1440 "Elapsed"
FROM (SELECT /*+ index (fcr1 fnd_concurrent_requests_n3) */
fcr1.request_id
FROM apps.fnd_concurrent_requests fcr1
WHERE 1=1
START WITH fcr1.request_id = &parent_request_id
CONNECT BY PRIOR fcr1.request_id = fcr1.parent_request_id) x,
apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcptl
WHERE fcr.request_id = x.request_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcp.application_id = fcptl.application_id
AND fcp.concurrent_program_id = fcptl.concurrent_program_id
AND fcptl.language = 'US'
ORDER BY 1;

DOCUMENTS PAYABLE OF SINGLE PAYMENT REQUEST COULD NOT BE INSERTED INTO DATABASE - R12

ISSUE:

Documents payable of single payment request could not be inserted into database or External Payee ID could not be derived for document based on payee context or Entering Invoice Get ORA-04062: Signature Of Package APPS.IBY_DISBURSEMENT_COMP_PUB Has Changed or External payee error message and Payables will not pick up suppliers for payment

Following error occurred when we applied the Patch 16793957:

--------------------------------------------------------------------------------

The following Oracle Forms objects did not generate successfully:

ap forms/US APXPAWKB.fmx

An error occurred while generating Oracle Forms files.

Continue as if it were successful [No] :

SOLUTION:

Below is the plan to solve this issue.

Step 1: Apply below patches provided or recommended by oracle support.

17323119

16296267

16793957

cd /u01/oracle/ebs12/apps/apps_st/appl/au/12.0.0/resource

frmcmp_batch module=IBYFDOBJ.pll userid=apps/appspwd output_file=IBYFDOBJ.plx module_type=library batch=no compile_all=special

Test the issue it should resolve.

OR

Step 1: Apply below patches provided or recommended by oracle support.

17323119

16793957

Step 2: Apply patch 16296267 for form issue provide or recommended by support and compile library.

Error occurred when we applied the Patch 16793957 ( Doc ID 1640390.1 )

Please apply patch 16296267:R12.IBY.B first followed by patch 16793957

cd /u01/oracle/ebs12/apps/apps_st/appl/au/12.0.0/resource

frmcmp_batch module=IBYFDOBJ.pll userid=apps/appspwd output_file=IBYFDOBJ.plx module_type=library batch=no compile_all=special

Test the issue it should resolve.

Oracle Database and Apps Scripts

Concurrent Managers Status:

set lines 150
set pages 200
column QUEUE format a24
column USER_CONCURRENT_QUEUE_NAME format a46
column TARGET_NODE format a15
Column concurrent_queue_id format 999999 head Q_Id
column cache_size format 999 heading "CACHE"

Select fcq.concurrent_queue_id "Q_Id" ,fcqt.USER_CONCURRENT_QUEUE_NAME,
fcq.CONCURRENT_QUEUE_NAME Queue,TARGET_NODE,MAX_PROCESSES,RUNNING_PROCESSES ,cache_size
from apps.FND_CONCURRENT_QUEUES fcq,APPS.fnd_concurrent_queues_tl fcqt
where ENABLED_FLAG='Y'
and fcq.application_id = fcqt.application_id
and fcq.concurrent_queue_id = fcqt.concurrent_queue_id
and fcqt.language = userenv('LANG')
and MAX_PROCESSES >0
order by fcq.concurrent_queue_id
--order by USER_CONCURRENT_QUEUE_NAME ,MAX_PROCESSES desc;

HOW TO FIND APPLICATIONS USER PASSWORD in R12

Connect apps

SQL> conn apps/pwd

Package:

CREATE OR REPLACE PACKAGE APPS_PKG AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2;
END APPS_PKG;

Package body:

CREATE OR REPLACE PACKAGE BODY APPS_PKG AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt
(java.lang.String,java.lang.String) return java.lang.String';
END APPS_PKG;

Query to get Username and Password:

SELECT Usr.User_Name,
Usr.Description,
APPS_PKG.Decrypt (
(SELECT (SELECT APPS_PKG.Decrypt (
Fnd_Web_Sec.Get_Guest_Username_Pwd,
Usertable.Encrypted_Foundation_Password)
FROM DUAL)
AS Apps_Password
FROM applsys.Fnd_User Usertable
WHERE Usertable.User_Name =
(SELECT SUBSTR (
Fnd_Web_Sec.Get_Guest_Username_Pwd,
1,
INSTR (Fnd_Web_Sec.Get_Guest_Username_Pwd,
'/')
- 1)
FROM DUAL)),
Usr.Encrypted_User_Password)
Password
FROM applsys.Fnd_User Usr
WHERE Usr.User_Name = '&User_Name';

ORA-03135: CONNECTION LOST CONTACT in Power center user

ISSUE:

PowerCenter server connects to Oracle and fails with the error:

ORA-03135: connection lost contact
Process ID: 10375
Session ID: 1296 Serial number: 31875

Database driver error...
Function Name : Execute
SQL Stmt : INSERT INTO W_SALES_PICK_LINE_FS

REASON:

This issue occur in below reasons:

The server unexpectedly crashed or was forced to down or instance not available.
The server timed out due to inactivity. 

SOLUTION:

Inrease the timeout threshold by using below parameters:

SQLNET.EXPIRE_TIME =10  #in minutes
SQLNET.INBOUND_CONNECT_TIMEOUT = 300 #in seconds
SQLNET.SEND_TIMEOUT = 300 #in seconds
SQLNET.RECV_TIMEOUT = 300 #in seconds

WARNING: INBOUND CONNECTION TIMED OUT (ORA-3136) AND ORA-03135: CONNECTION LOST CONTACT

ISSUE:

Alert log on Oracle server shows the following error: 

INBOUND CONNECTION TIMED OUT (ORA-3136) 

Example log:

Time: 21-SEP-2014 03:50:34
TNS-12535: TNS:operation timed out
TNS-12535: TNS:operation timed out
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=39748))
WARNING: inbound connection timed out (ORA-3136)
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=39750))
WARNING: inbound connection timed out (ORA-3136)

AND

Informatica SSOT load failed with below message

Message: Database driver error...
CMN_1022 [
Database driver error...
Function Name : Logon
ORA-03135: connection lost contact

Database driver error...
Function Name : Connect
Database Error: Failed to connect to database using user [OBAW_OBIA] and connection string [Database].]

REASON:

This issue occurs due to one of the following reasons:

Inbound connection was timed out by the server as the user authentication was not completed within the given time specified by SQLNET.INBOUND_CONNECT_TIMEOUT or its default value specified in sqlnet.ora.
There is a lag in the network or a layer between the database and the client is preventing the log on process from completing within the allowed time.

Solution:

By default, the SQLNET.INBOUND_CONNECT_TIMEOUT is set to 60 seconds. 

To resolve this issue, do the following:

Change the setting by adding the parameters SQLNET.INBOUND_CONNECT_TIMEOUT in $ORACLE_HOME/network/admin/sqlnet.ora and INBOUND_CONNECT_TIMEOUT_<listener name> to the $ORACLE_HOME/network/admin/listener.ora file on the database server.

Bounce the listener  and Verify the issue.

Example:

SQLNET.INBOUND_CONNECT_TIMEOUT = 300
INBOUND_CONNECT_TIMEOUT_PROD = 300

FNDWRR.exe getting "Authentication failed" when trying to view completed request notification

Below post explain the current issue with Workflow Notifications and completed request where the request is only available to view one time.
        
         There is a feature in Applications that allows you to send an email
notification to a user, or group of users when you submit a concurrent request.
The notification itself has the actual completed request as an attachment,
essentially allowing the recipients to view the request without having to log
into Applications.  On the Submit Request form, there is an 'Upon Completion'
section.  You then click on the 'Completion Options' button.  In the next screen
you can enter Applications user names that will get sent the email notification.
The Workflow mailer must be running at this point prior to submitting the
request.
                The problem with this feature is that the request that is attached is only
view able one time.  Even if users log into Applications or Self Service
Applications and go in to view notifications and try to view the attachment,
it just comes up "Authentication Failed" in the browser.
                The problem lies in the URL that gets generated for the call to the Web Report
Review agent (FNDWRR).  Embedded within this URL is a temp_id string that is
randomly generated.  This string is part of an encryption mechanism that does
not recognize or record the first time the request is accessed and then
regenerates the temp_id string in order to be used again.

Explanation:
                     This URL will only work once. This is a security feature.
The temp_id is stored in a table, and once it is used, it is deleted.
The security is definitely needed. 
                    Reports can contain very sensitive information, for example, HR reports.
Security is normally done with UNIX file permissions and by the use of responsibilities. However, providing a URL that allows anyone on the network to access a report would be a major security hole.

Oracle Applications shows a blank page

Issue:

Application page showing blank

Solution:

Check and verify FND_USER table users end date and do below steps if all users end dated.

Exclude SYSADMIN user from end date.

select * from fnd_user where last_update_date > sysdate-1/4

select sysdate from dual;

create table fnd_user_orignal
as
select * from fnd_user;

restore backup table and update table using below query 

select * from fnd_user where created_by<>1
and user_id<>12124
and end_date is null;

update fnd_user 
set end_date = sysdate-1 
where created_by<>1 
and user_id<>12124 
and end_date is null

commit;