Oracle Apps Knowledge Sharing

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