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

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

Dominick said...
March 8, 2021 at 7:49 PM

One of the significant benefits of the internet shop is it enables players to try out various video games translation proofreading without leaving their house. One more benefit is that when they paid for the video games they got over the web, the things will certainly be supplied straight to their house address and also many of the time it gets here within one week from the day they have actually acquired it.


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.