How to Resolve ORA-04061: Existing State Of Package Body has been invalidated

In Oracle application Workflow related errors are very common specially related to PO Approval or Requisition approval. Recently we got ORA-04061: Existing State Of Package Body has been invalidated for APPS.PO_GA_PVT and APPS.PO_WF_PO_NOTIFICATION. All PO related Workflow notifications were erroring out and users were not able to approve PO from emails and from the system.

Error Message:

[WF_ERROR] ERROR_MESSAGE=3835: Error '-20002 - ORA-20002: 2018: Unable to generate the notification XML. Caused by: 2020: Error when getting notification content. Caused by: ORA-04061: existing state of has been invalidated ORA-04061: existing state of package body "APPS.XXUS_PO_WF_PO_NOTIFICATION" has been invalidated ORA-04065: not executed, altered or dropped package body "APPS.XXUS_PO_WF_PO_NOTIFICATION" ORA-06508: PL/SQL: could not find program unit being called: "APPS.XXUS_PO_WF_PO_NOTIFICATION" wf_notification.GetAttrDoc2(3618679, PO_LINES_DE' encountered during execution of Generate function 'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'. ERROR_STACK= WF_MAIL.GetLOBMessage3(3618679, WFMAIL, 2020: Error when getting notification content. Caused by: ORA-04061: existing state of has been invalidated ORA-04061: existing state of package body "APPS.XXUS_PO_WF_PO_NOTIFICATION" has been invalidated ORA-04065: not executed, altered or dropped package body "APPS.XXUS_PO_WF_PO_NOTIFICATION" ORA-06508: PL/SQL: could not find program unit being called: "APPS.XXUS_PO_WF_PO_NOTIFICATION" wf_notification.GetAttrDoc2(3618679, PO_LINES_DETAILS, text/plain) Wf_Notification.GetAttrDoc(3618679, PO_LINES_DETAILS, text/plain) Wf_Notification.GetText(3618679, text/plain) Wf_Notification.GetBody(3618679, text/plain) WF_NOTIFICATION.GetFullBody(nid => 3618679, disptype => text/plain) WF_MAIL.GetLOBMessage3(nid => 3618679, r_ntf_pref => MAILATTH), Step -> Getting text/plain body) WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 3618679) WF_XML.Generate(oracle.apps.wf.notification.send, 3618679) WF_XML.Generate(oracle.apps.wf.notification.send, 3618679) Wf_Event.setMessage(oracle.apps.wf.notification.send, 3618679, WF_XML.Generate) Wf_Event.dispatch_internal()

In the database both the package and body were in valid state. We completed following steps as per metalink note to resolve this issue but couldn't resolve this issue.

1. Shutdown the Notification Mailer and agent listener
2. Recompile the APPS schema through ADADMIN (even when there were no invalids)
3. Start the Notification Mailer and agent listener

Now to resolve this issue we need to do following steps in addition to the above steps.

1. Shutdown the Notification Mailer and agent listener
2. Recompile the APPS schema through ADADMIN
3. connect / as sysdba

SQL>alter system set aq_tm_processes=0 scope=memory;

You need to wait until the q00* and qmnc* processeses are no longer running. You can check via

ps -ef grep q00 and ps -ef grep qmn

SQL> alter system flush shared_pool;

SQL> alter system set aq_tm_processes=1 scope=memory;

4. Start the Notification Mailer and agent listener

Once we did all the above steps users were able to receive mails related to PO and Requisition and they were also able to approve PO from Workflow Notification Emails.