SELECT WRQ.message_id,fsbp.BELONGS_TO,fsbp.body, REQUEST_TIMESTAMP
AS REQDATETIME,
REQUEST_COMPLETED
AS RESDATETIME,
WRQ.IREP_NAME
AS WEB_SERVICE_NAME,
FIC.CLASS_ID
AS CLASS_ID,
METHOD
AS METHOD_NAME,
USER_NAME,
WRQ.USER_ID,
IP_ADDRESS,
REQUEST_STATUS
AS REQUEST_STATUS,
RESPONSE_STATUS
AS RESPONSE_STATUS,
fsbp.MESSAGE_ID,
REQUEST_PATTERN,
(SELECT meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'FND_SOA_RESPONSE_STATUS'
AND lookup_code = WRQ.RESPONSE_STATUS)
AS res_status,
(SELECT meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'FND_SOA_REQUEST_STATUS'
AND lookup_code = WRQ.REQUEST_STATUS)
AS req_status,
(SELECT meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'FND_SOA_MESSAGE_PATTERN'
AND lookup_code = WRQ.REQUEST_PATTERN)
AS interaction_architecture,
(SELECT DECODE (COUNT (1), 0, 'LOG_ABSENT', 'LOG_PRESENT')
FROM fnd_log_messages
WHERE transaction_context_id IN
(SELECT transaction_context_id
FROM fnd_log_transaction_context
WHERE transaction_type = 'SOA_INSTANCE'
AND transaction_id = WRQ.MESSAGE_ID
AND REGEXP_LIKE (WRQ.MESSAGE_ID,'^-?[[:digit:],.]*$')))
AS ENABLE_LOG
FROM FND_SOA_REQUEST WRQ, FND_USER FU, FND_IREP_CLASSES FIC,fnd_soa_body_piece fsbp
WHERE WRQ.USER_ID = FU.USER_ID(+)
AND FIC.IREP_NAME = WRQ.IREP_NAME
AND FIC.CLASS_TYPE NOT IN ('SOAPSERVICEDOC', 'WEBSERVICEDOC')
AND FIC.IREP_NAME='XX_PAYMENT_SERVICE'
AND TO_DATE(WRQ.REQUEST_STARTED,'DD-MM-RRRR') between TO_DATE ('20-07-2023','DD-MM-RRRR') and TO_DATE ('21-07-2023','DD-MM-RRRR')
AND WRQ.message_id = fsbp.message_id
select * from fnd_soa_body_piece
