ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN FEADFF4ECC8C455D8B7707AF280C0B7AY select fsr.message_id, fiai.display_name interface, fsr.method operation, &isg_12_2_columns fsr.request_timestamp request_received, fsr.request_completed response_sent, round((fsr.request_completed-fsr.request_timestamp)*86400) execution_seconds, &requests_in_process trunc(fsr.request_timestamp,'mi') received_minute, xxen_util.meaning(fsr.request_status,'FND_SOA_REQUEST_STATUS',0) status, fsr.attrib6 http_status, fsr.attrib7 fault_type, fsr.attrib8 message, fsr.attrib1 http_method, xxen_util.meaning(fsr.request_pattern,'FND_SOA_MESSAGE_PATTERN',0) interaction_pattern, fsr.attrib9 authentication_type, xxen_util.user_name(fsr.user_id) user_name, frv.responsibility_name responsibility, fav.application_name application, fsr.security_group_name security_group, fsr.nls_language, fsr.ip_address client_ip_address, fsr.attrib2 content_type, fsr.attrib3 accept, fsr.attrib4 accept_language, fsr.attrib5 language, &payload_columns fsr.irep_name internal_name from fnd_soa_request fsr, fnd_irep_all_interfaces fiai, fnd_responsibility_vl frv, fnd_application_vl fav where 1=1 and fsr.irep_name=fiai.irep_name and fiai.class_type not in ('SOAPSERVICEDOC','WEBSERVICEDOC','RESTSERVICEDOC') and fsr.responsibility_id=frv.responsibility_id(+) and fsr.responsibility_application_id=frv.application_id(+) and fsr.responsibility_application_id=fav.application_id(+) order by fsr.request_timestamp desc, fsr.message_id desc New report on the Integrated SOA Gateway request log (SOA Monitor). US FND Integrated SOA Gateway Requests Integrated SOA Gateway (ISG) web service requests as logged for the SOA Monitor, one row per request, with execution time, status, HTTP and fault details. Execution seconds is the time between the gateway receiving the request and sending the response, rounded to whole seconds. Time a request spends queued before it reaches the gateway (client, load balancer, web tier) is not included: compare the client's send time with Request Received to measure it. Show Requests in Process counts, for each request, the requests in process when it was received, itself included, to show how many calls the gateway handled in parallel. Use the template Throughput per Minute to see request volume and average execution time per minute. Show Payload adds the request and response bodies, which ISG stores only for services with auditing enabled. Enginatics &isg_12_2_columns &payload_columns &requests_in_process 1=1 1 -10 &isg_12_2_columns select 'xxen_util.meaning(fsr.service_type,''FND_SOA_SERVICE_TYPE'',0) service_type, (select haouv.name from hr_all_organization_units_vl haouv where fsr.org_id=haouv.organization_id) operating_unit,' text from all_tab_columns atc where atc.owner='APPLSYS' and atc.table_name='FND_SOA_REQUEST' and atc.column_name='SERVICE_TYPE' Char 1 US Release 12.2 Columns 2 10 1=1 fsr.request_timestamp>=sysdate-:days Number 1 US Received within x Days 3 20 1=1 fsr.request_timestamp>=:received_from DateTime US Received From 4 30 1=1 fsr.request_timestamp<=:received_to DateTime US Received To 5 40 1=1 fsr.irep_name=:interface LOV custom select fiai.irep_name id, fiai.display_name value, fiai.irep_name description from fnd_irep_all_interfaces fiai where fiai.class_type not in ('SOAPSERVICEDOC','WEBSERVICEDOC','RESTSERVICEDOC') and fiai.irep_name in (select fsr.irep_name from fnd_soa_request fsr) order by fiai.display_name US Interface 6 50 1=1 fsr.method=:operation Char US Operation 7 60 1=1 fsr.request_status=:status LOV custom select flvv.lookup_code id, flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type='FND_SOA_REQUEST_STATUS' and flvv.lookup_code<>'ANY' and flvv.view_application_id=0 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.meaning US Status 8 70 1=1 fsr.user_id=xxen_util.user_id(:user_name) LOV FND User Name 8E2FF36EDE8479D2E0530100007F1FF2 select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name US User Name 9 80 1=1 fsr.request_completed>=fsr.request_timestamp+:execution_seconds_from/86400 Number US Execution Seconds From 10 90 &requests_in_process ( select count(*) from fnd_soa_request fsr2 where fsr2.request_timestamp between fsr.request_timestamp-1/24 and fsr.request_timestamp and (fsr2.request_completed>fsr.request_timestamp or fsr2.message_id=fsr.message_id) ) requests_in_process, LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Requests in Process Counts the requests of all interfaces that were in process when each request was received. Requests received more than an hour earlier are not counted. 11 100 &payload_columns (select fsbp.body from fnd_soa_body_piece fsbp where fsr.message_id=fsbp.message_id and fsbp.belongs_to='REQUEST') request_body, (select fsbp.body from fnd_soa_body_piece fsbp where fsr.message_id=fsbp.message_id and fsbp.belongs_to='RESPONSE') response_body, LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Payload DAD58A80F791434ABF6DB2D297AF8845 Throughput per Minute ENGINATICS 5 EXECUTION_SECONDS AVG 2 INTERFACE 2 4 MESSAGE_ID COUNT 3 OPERATION 3 1 RECEIVED_MINUTE -1 S Site