Requirement:- We would like to search the B2B logs using the SQL query on the basis of Partner Name where the direction is Inbound.
Solution:- The tables which we usually use to store the EDI related data are:-
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.