Friday, July 24, 2020

Query to Fetch Purchase Order XML delivery details (ECX_DOCLOGS , ECX_OUTBOUND_LOGS, ECX_OXTA_LOGMSG)










Sending Purchase Order XML details to Supplier is a standard Oracle Feature. Its managed by e-Commerce gateway (ECX). Here we have given a query which fetches the basic details about delivery of a PO XML.

Query:

SELECT hou.short_code
      ,pha.segment1
      ,pha.org_id
      ,aps.vendor_name
	  ,aps.segment1 vendor_number
      ,assa.vendor_site_code
      ,edoc.document_number
      ,eol.completed_date
      ,DECODE(edoc.transaction_type,
	          'PROCESS','NEW',
			  'CHANGE'
			 ) transaction_type
      ,edoc.protocol_type
      ,edoc.protocol_address
      ,edoc.payload po_xml
      ,edoc.item_type
      ,edoc.item_key
      ,decode(eol.result_code, 
	          1000, 'Success', 
			'Failure'
	      ) AS status
      ,eol.result_text
      ,eol.exception_text
 FROM ecx_oxta_logmsg eol
     ,ecx_doclogs edoc
     ,ecx_outbound_logs eog
     ,po_headers_all pha
     ,hr_operating_units hou
     ,ap_suppliers aps
     ,ap_supplier_sites_all assa
WHERE edoc.msgid          = eol.sender_message_id
  AND eog.out_msgid       = edoc.msgid
  AND pha.segment1        = SUBSTR(edoc.document_number,
                                   1,INSTR(edoc.document_number,':',1) - 1
								  )
  AND pha.org_id          = hou.organization_id
  AND pha.supplier_notif_method = 'XML'
  AND pha.vendor_id       = aps.vendor_id
  AND aps.vendor_id       = assa.vendor_id
  AND assa.vendor_site_id = pha.vendor_site_id
ORDER BY edoc.document_number

0 Responses to “Query to Fetch Purchase Order XML delivery details (ECX_DOCLOGS , ECX_OUTBOUND_LOGS, ECX_OXTA_LOGMSG)”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.