Debugging Concurrent Programs

Concurrent Programs

For concurrent programs you can place in Log file / output file / fnd_log_messages table


For Log File
FND_FILE.PUT_LINE(FND_FILE.LOG,'your log statement');


For Output File
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'your output statement');


For Tracing it in a table FND_LOG_MESSAGES


fnd_log.string(log_level => fnd_log.level_statement
,module => 'custom module'
,message => 'you log message here');

Workflow Mailer Debugging Script for Debugging Emails issues and event subscriptions

Workflow Mailer Debugging Script for Debugging Emails issues and event subscriptions
This article containts various Workflow and Business Event debugging scripts.


--Checking workflow Components status wheather are they running or stopped.
select component_type, component_name, Component_status,COMPONENT_STATUS_INFO Error
from fnd_svc_components
where component_type like 'WF%'
order by 1 desc,2,3;


--Query to get the log file of active workflow mailer and workflow agent listener Container
--Note All Workflow Agent Components logs will stored in single file ie. container log file.
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;



--Linux Shell script Command to get outbound error in Mailer
grep -i '^\[[A-Za-z].*\(in\|out\).*boundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' | tail -10 ;
--Note: All Mailer log files starts with name FNDCPGSC prefix

--Linux Shell script Command to get inbound processing error in Mailer
grep -i '^\[[A-Za-z].*.*inboundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' | tail -10 ;


---Query to Check Workflow Mailer Backlog
--State=Ready implies that emails are not being sent & Waiting mailer to sent emails
select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by tab.msg_state ;


--Check any particular Alert Message email has be pending by Mailer
select decode(wno.state,
0, '0 = Pending in mailer queue',
1, '1 = Pending in mailer queue',
2, '2 = Sent by mailer on '||to_char(DEQ_TIME),
3, '3 = Exception', 4,'4 = Wait', to_char(state)) State,
to_char(DEQ_TIME),
wno.user_data.TEXT_VC
from wf_notification_out wno
where corrid='APPS:ALR'
and upper(wno.user_data.TEXT_VC) like '%%';


--Check The Workflow notification has ben sent or not
select mail_status, status from wf_notifications where notification_id=
--If mail_status is MAIL , it means the email delivery is pending for workflow mailer to send the notification
--If mail_status is SENT, its means mailer has sent email
--If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is "Don't send email"
--Notification preference of user can be set by user by loggin in application + click on preference + the notification preference


--Check Wheather workflow background Engine is workfing for given workflow or not in last 2 days
-- Note: Workflow Deferred activities are run by workflow background engine.
select a.argument1,a.phase_code, a.status_code ,a.actual_start_date,a.* from fnd_concurrent_requests a
where CONCURRENT_PROGRAM_ID =
(select concurrent_program_id from fnd_concurrent_programs where
CONCURRENT_PROGRAM_NAME='FNDWFBG')
and last_update_Date>sysdate-2 and argument1=''
order by last_update_date desc

-- Check wheather any business event is pending to process
-- ie. Query to get event status & parameters value of particular event in wf_deferred table.
select wd.user_Data.event_name,wd.user_Data.event_key,
rank() over ( partition by wd.user_Data.event_name, wd.user_Data.event_key order by n.name) as serial_no,
n.NAME Parameter_name, N.value Parameter_value ,
decode(state, 0, '0 = Ready', 1, '1 = Delayed', 2, '2 = Retained',
3, '3 = Exception', 4,'4 = Wait', to_char(state)) state,
wd.user_Data.SEND_DATE,
wd.user_Data.ERROR_MESSAGE,
wd.user_Data.ERROR_STACK,
wd.msgid,wd.delay
from WF_DEFERRED wd , TABLE(wd.user_Data.PARAMETER_LIST) n
where lower(wd.user_data.event_name)=''
order by wd.user_Data.event_name, wd.user_Data.event_key, n.name

Debugging OA pages

Debugging OA pages


a. Enable the profile option : FND: Debug Log Enabled -- Set to Yes
b. Enable the profile option : FND: Debug Log Level -- Set to Statement level
c. Add the below piece of code in your OA page
boolean isLoggingEnabled = pageContext.isLoggingEnabled(OAFwkConstants.STATEMENT);
if (isLoggingEnabled)
{
pageContext.writeDiagnostics(this, "your log statement", OAFwkConstants.STATEMENT);
}
To see log stmt on browser append below string to browser url and click on enter


&aflog_level=statement

R12 Suppliers Query

SELECT DISTINCT asp.vendor_id ,
asp.segment1 "Supplier Num" ,
ASp.vendor_name "Supplier Name" ,
ASP.ATTRIBUTE15 "HDRS LEGACY SUPP NO" ,
ass.vendor_site_code "SITE NAME" ,
hou.NAME "Operating Unit Name" ,
ass.address_line1 ,
ass.city ,
ass.state ,
ass.zip ,
ass.country ,
ass.phone ,
ASS.ATTRIBUTE15 "SITES LEGACY SUPP NO" ,
person.person_first_name ,
person.person_last_name ,
pty_rel.primary_phone_number ,
pty_rel.EMAIL_ADDRESS
FROM ap_suppliers asp ,
ap_supplier_sites_all ass ,
ap_supplier_contacts apsc ,
hz_parties person ,
hz_parties pty_rel,
hr_operating_units hou
WHERE 1 = 1
--AND ASP.VENDOR_NAME = PERSON.PARTY_NAME
AND ass.vendor_id = asp.vendor_id
AND apsc.per_party_id = person.party_id
AND apsc.rel_party_id = pty_rel.party_id
AND ass.org_id = hou.organization_id
AND apsc.ORG_PARTY_SITE_ID = ASS.PARTY_SITE_ID
AND ASP.ATTRIBUTE15 IN ('1240')
ORDER BY hou.NAME