Thursday, September 26, 2013

SQL Query to check programs assigned to a request set

SELECT rs.user_request_set_name "Request Set"
 , rss.display_sequence Seq
 , cp.user_concurrent_program_name "Concurrent Program"
, e.EXECUTABLE_NAME
 , e.execution_file_name
 , lv.meaning file_type
 ,fat.application_name "Application Name"
-- ,get_appl_name(e.application_id) "Application Name"
FROM apps.fnd_request_sets_vl rs
 , apps.fnd_req_set_stages_form_v rss
 , applsys.fnd_request_set_programs rsp
 , apps.fnd_concurrent_programs_vl cp
 , apps.fnd_executables e
 , apps.fnd_lookup_values lv
 , apps.fnd_application_tl fat
WHERE 1=1
--and rs.application_id IN ( 20006 )
AND rs.application_id = rss.set_application_id
AND rs.request_set_id = rss.request_set_id
AND rs.user_request_set_name =-- &amp p_request_set_name
AND e.APPLICATION_ID =FAT.APPLICATION_ID
AND rss.set_application_id = rsp.set_application_id
AND rss.request_set_id = rsp.request_set_id
AND rss.request_set_stage_id = rsp.request_set_stage_id
AND rsp.program_application_id = cp.application_id
AND rsp.concurrent_program_id = cp.concurrent_program_id
AND cp.executable_id = e.executable_id
AND cp.executable_application_id = e.application_id
AND lv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND lv.lookup_code = e.execution_method_code
and lv.language='US'
and fat.language='US'
AND rs.end_date_active IS NULL
ORDER BY 1,2

Tuesday, September 17, 2013

Invoice Validation Status showing as "Selected for Validation"

To fix the invoices for which the invoice status showing as "Selected For Validation".

The validation_request_id should be removed on ap_invoices_all when the validation program completes.

Tuesday, September 3, 2013

ORA-04061: existing state of package body “XXXX” has been invalidated

[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.XX_CHANGE_REQUEST_PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "APPS.XXI_CHANGE_REQUEST_PKG"
ORA-06508: PL/SQL: could not find program unit being called: "APPS.XX_CHANGE_REQUEST_PKG"

Wf_Notification.GetAttrClob(2836675, XX_NOTIFICAT' encountered during execution of Generate function 'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'. ERROR_STACK=
WF_MAIL.GetLOBMessage3(2836675, PRDMAIL, 2020: Error when getting notification content. Caused by: ORA-04061: existing state of  has been invalidated
ORA-04061: existing state of package body "APPS.XX_CHANGE_REQUEST_PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "APPS.XX_CHANGE_REQUEST_PKG"
ORA-06508: PL/SQL: could not find program unit being called: "APPS.XX_CHANGE_REQUEST_PKG"

Wf_Notification.GetAttrClob(2836675, XX_NOTIFICATION_MESSAGE, text/html)
Wf_Notification.oldGetAttrClob(2836675, XX_NOTIFICATION_MESSAGE, text/html)
WF_NOTIFICATION.GetFullBody(nid => 2836675, disptype => text/html)
WF_MAIL.GetLOBMessage3(nid => 2836675, r_ntf_pref => MAILHTML), Step -> Getting text/html body)
WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 2836675)
WF_XML.Generate(oracle.apps.wf.notification.send, 2836675)
WF_XML.Generate(oracle.apps.wf.notification.send, 2836675)
Wf_Event.setMessage(oracle.apps.wf.notification.send, 2836675, WF_XML.Generate)

Wf_Event.dispatch_internal()

--For the above issue the below solution worked for us.

A bounce of Workflow and Mailer Services

The below reference helped