Search for B2B logs using SQL Query

Requirement:- We would like to search the B2B logs using the SQL query on the basis of Partner Name where the direction is Inbound.

Partner_Name.png

Solution:- The tables which we usually use to store the EDI related data are:-

AU_GROUPDATA
AU_INTERCHANGEDATA
AU_ROUTINGDATA
AU_EDITRANSACTIONDATA
AU_EDIFACTUNBDATA

Also, the complex query to fetch data from the tables for reference is:-

(SELECT interchange.TRANSACTIONPID as TransmissionID ,  grop.PARTNERNAME as PartnerName ,  grop.GS02 as SenderID ,  grop.GS03 as ReceiverID ,  grop.DISPLAYSTATUS as Status ,  grop.GS01 as DocumentType ,  interchange.ERROR as LogMessage ,  grop.FLOWTYPE as Direction ,  interchange.NONEDIFILE as FileIN ,  interchange.EDIFILE as FileOut, interchange.RECORDSEPARATOR as RecordSeperator, grop.ACKEXPIRYTIME as AckExpiryTime ,  interchange.AU_LOGDATE as LogDateTime ,  interchange.AU_TRANSACTIONNAME ,  interchange.AU_TRANSACTIONID ,  grop.id as tableID  ,  grop.GE01 as transactioncount  ,  grop.GS06 controlnumber, grop.ACKREQUESTED as ackrequested,ACKSTATUS as FA_STATUS FROM  AU_INTERCHANGEDATA  interchange INNER JOIN  AU_GROUPDATA  grop  ON interchange.id=grop.interchange_id  WHERE interchange.AU_LOGDATE >= '1554700206393' AND interchange.AU_LOGDATE <= '1554892874405'  AND grop.PARTNERNAME = 'PartnerA')

We can modify the value of AU_LOGDATE, PARTNERNAME as per the requirement in the above query.

Have more questions? Submit a request

0 Comments

Article is closed for comments.