none
Processing Inbound 997 Acks RRS feed

  • Question

  • Hi,

     

    I am still having problems processing inbound 997 Acks for my outbound EDI transactions.  I am using the new EDI features in R2.  Here are my questions.

    1.  Does Biztalk automatically correlate the inbound 997s to the EDI transactions that were sent?

    2.  How does the Interchange Status field from the "EDI Interchange and Correlated Ack Status" report from the Group Hub page get updated?

    3.  When the inbound 997 Acks are received in the EDIReceive pipeline two messages are created, http://schemas.microsoft.com/Edi/X12#X12_997_Root and http://schemas.microsoft.com/Edi/X12#X12_TA1_Root Why are both messages created?

     

    Thanks,

    Mike

    Wednesday, September 5, 2007 5:53 AM

Answers

  • "Ack Expected" field will show up if you check the box "ISA14 (Technical Ack Required)" under "Party as Interchange Receiver/ISA Segment Definition" for the party who is sending the doc (in this case Party A). This is relating to a TA1 being requested back by the sender (which is different from a 997 or a functional ACK). If you uncheck this box, you will see that the field says "Ack not Expected".

     

    To answer your previous question, "Ack Expected" field will not get updated since that is just an indication of whether you require an TA1 to be generated.

     

    As far as correlating the ACK goes, PartyB receives the 850 and sends back the 997 and that is correlated if you check the control ID of the fields.

     

    Also the 2 messages that you mentioned are TA1 and 997. If you like more information on these 2 messages, please let me know and I will be more than happy to help you.

     

    Hope this helps

    Mohsin

    Thursday, September 20, 2007 1:21 AM
  • Hi,

     

    I think I have created the Outbound EDI acknowlege report I need.  It queries the BAM EDI interchange tables.  Here's the SQL if anyone else is looking for it. I haven't tried it with Batch EDI submissions yet.

     

    Code Block

     

    SELECT tsa.InterchangeControlNo AS OutICN, tsa.ReceiverID AS OutReceiverID, tsa.SenderID AS OutSenderID, tsa.ReceiverQ AS OutReceiverQ,

    tsa.SenderQ AS OutSenderQ, tsa.InterchangeDateTime AS OutSent, tsa.Direction AS OutDir, tsa.GroupControlNo AS OutGCN,

    tsa.TransactionSetId AS OutTransactionSetID, tsa.TransactionSetControlNo AS OutTCN,

    faa.ActivityID AS InActivityID, faa.GroupControlNo AS InGCN, faa.ReceiverID AS InReceiverID,

    faa.SenderID AS InSenderID, faa.ReceiverQ AS InReceiverQ, faa.SenderQ AS InSenderQ, faa.Direction AS InDirection, faa.AcceptedTSCount,

    faa.TimeCreated AS InTimeCreated

    FROM bam_TransactionSetActivity_AllInstances AS tsa LEFT OUTER JOIN

    bam_FunctionalAckActivity_AllInstances AS faa ON tsa.GroupControlNo = faa.GroupControlNo AND tsa.ReceiverQ = faa.SenderQ AND

    tsa.SenderQ = faa.ReceiverQ AND tsa.SenderID = faa.ReceiverID AND tsa.ReceiverID = faa.SenderID

    WHERE (tsa.Direction = 2) AND (faa.Direction = 1 or faa.Direction IS NULL)

     

     

     

    Thanks,

    Mike

     

     

    Thursday, October 18, 2007 8:06 PM

All replies

  • Hi,

     

    I have been working on this for a while.  I think I have gotten somewhere but I have not gotten the Interchange Status field to update.

     

    I created 2 parties: PartyA creates and sends an EDI 850.  PartyB accepts the EDI850 and sends back a 997.  When I look at the EDI Interchange report the status for the EDI 850 sent is "Ack Expected".  However if I look at the details I see that there is no Interchange Ack record, but the 997 from PartyB shows up as a Functional Ack.

     

    How can the Interchange Ack be updated?  I'm thinking that I need an orchestration using either the 997 message or the TA1 message to do something but I'm not sure what.

     

    So I have answered questions 1 and 3, but I'm still stuck on question 2.

     

    Thanks,

    Mike

     

     

    Wednesday, September 5, 2007 11:00 PM
  • "Ack Expected" field will show up if you check the box "ISA14 (Technical Ack Required)" under "Party as Interchange Receiver/ISA Segment Definition" for the party who is sending the doc (in this case Party A). This is relating to a TA1 being requested back by the sender (which is different from a 997 or a functional ACK). If you uncheck this box, you will see that the field says "Ack not Expected".

     

    To answer your previous question, "Ack Expected" field will not get updated since that is just an indication of whether you require an TA1 to be generated.

     

    As far as correlating the ACK goes, PartyB receives the 850 and sends back the 997 and that is correlated if you check the control ID of the fields.

     

    Also the 2 messages that you mentioned are TA1 and 997. If you like more information on these 2 messages, please let me know and I will be more than happy to help you.

     

    Hope this helps

    Mohsin

    Thursday, September 20, 2007 1:21 AM
  • Hi,

     

    I am back working on the Inbound 997s again.

     

    According to the  MSDN "EDI Interchange and Correlated ACK Status Report" report documentation http://msdn2.microsoft.com/en-us/library/bb743627.aspx 

    Note
    The status of a functional acknowledgment will be set to Ack Expected if the "Functional ACK (integrated with ACK Reporting)" property is set in the ACK Processing and Validation Settings page for the party as interchange receiver. The status will be changed to Accepted when the acknowledgment has been received and validated.

     

     

    I think there is a problem when the 997s come in.  The party as interchange sender has the Inbound batch processing option set to "Split Interchange as transaction sets".  So the 997s come in and are correlated as Functional Acks but they are not correlated as Interchange Acks and at the Interchange Status level.

     

    When I look at the records in bam_InterchangeAckActivity_AllInstances I can see the inbound 997s but the AckIcn field is NULL.

     

    How can I correlate the inbound 997s to the original outbound EDI documents?

     

    Thanks,

    Mike

    Tuesday, October 16, 2007 8:37 PM
  • Hi,

     

    I think I have created the Outbound EDI acknowlege report I need.  It queries the BAM EDI interchange tables.  Here's the SQL if anyone else is looking for it. I haven't tried it with Batch EDI submissions yet.

     

    Code Block

     

    SELECT tsa.InterchangeControlNo AS OutICN, tsa.ReceiverID AS OutReceiverID, tsa.SenderID AS OutSenderID, tsa.ReceiverQ AS OutReceiverQ,

    tsa.SenderQ AS OutSenderQ, tsa.InterchangeDateTime AS OutSent, tsa.Direction AS OutDir, tsa.GroupControlNo AS OutGCN,

    tsa.TransactionSetId AS OutTransactionSetID, tsa.TransactionSetControlNo AS OutTCN,

    faa.ActivityID AS InActivityID, faa.GroupControlNo AS InGCN, faa.ReceiverID AS InReceiverID,

    faa.SenderID AS InSenderID, faa.ReceiverQ AS InReceiverQ, faa.SenderQ AS InSenderQ, faa.Direction AS InDirection, faa.AcceptedTSCount,

    faa.TimeCreated AS InTimeCreated

    FROM bam_TransactionSetActivity_AllInstances AS tsa LEFT OUTER JOIN

    bam_FunctionalAckActivity_AllInstances AS faa ON tsa.GroupControlNo = faa.GroupControlNo AND tsa.ReceiverQ = faa.SenderQ AND

    tsa.SenderQ = faa.ReceiverQ AND tsa.SenderID = faa.ReceiverID AND tsa.ReceiverID = faa.SenderID

    WHERE (tsa.Direction = 2) AND (faa.Direction = 1 or faa.Direction IS NULL)

     

     

     

    Thanks,

    Mike

     

     

    Thursday, October 18, 2007 8:06 PM
  • Mike:

     

    Thanks for the post.  I found the information in the BAM Primary Import tables as you did.

     

    How are you going to "present" the information to the business users?

     

    Is there a way to add to the Standard EDI Reports?

     

    ... or ...

     

    Should all of this kind of thing be done using BAM and then displayed using the BAM Portal?

     

    I am trying to get BAM / BAM tools configured so I can prototype.

     

    Curt

     

    Friday, December 14, 2007 1:38 PM
  •  

    Mike:

     

    I tried to Execute your SQL using SQL Management Studio.

     

    I get the following message:  Invalid object name 'bam_TransactionSetActivity_AllInstances'

     

    I looked for the table but could not find it. 

     

    I could not find any tables with "_AllInstances".

     

    I changed to "_Completed" as these seemed to be the tables with data but got the same message:  Invalid object name 'bam_TransactionSetActivity_Completed'. 

     

    Any ideas?

     

    Curt

     

     

    Friday, December 14, 2007 3:18 PM
  • Mike:

     

    I realized that you were using views rather than tables for your SQL. 

     

    I talked to our DBA and he said I need to include the "use" statement ...

     

    use [BAMPRIMARYIMPORT]

    SELECT tsa.InterchangeControlNo AS OutICN, tsa.ReceiverID AS OutReceiverID, tsa.SenderID AS OutSenderID, tsa.ReceiverQ AS OutReceiverQ,

    tsa.SenderQ AS OutSenderQ, tsa.InterchangeDateTime AS OutSent, tsa.Direction AS OutDir, tsa.GroupControlNo AS OutGCN,

    tsa.TransactionSetId AS OutTransactionSetID, tsa.TransactionSetControlNo AS OutTCN,

    faa.ActivityID AS InActivityID, faa.GroupControlNo AS InGCN, faa.ReceiverID AS InReceiverID,

    faa.SenderID AS InSenderID, faa.ReceiverQ AS InReceiverQ, faa.SenderQ AS InSenderQ, faa.Direction AS InDirection, faa.AcceptedTSCount,

    faa.TimeCreated AS InTimeCreated

    FROM bam_TransactionSetActivity_AllInstances AS tsa LEFT OUTER JOIN

    bam_FunctionalAckActivity_AllInstances AS faa ON tsa.GroupControlNo = faa.GroupControlNo AND tsa.ReceiverQ = faa.SenderQ AND

    tsa.SenderQ = faa.ReceiverQ AND tsa.SenderID = faa.ReceiverID AND tsa.ReceiverID = faa.SenderID

    WHERE (tsa.Direction = 2) AND (faa.Direction = 1 or faa.Direction IS NULL)

     

    ... and now I got it working.

     

    If I get something working with the BAM Portal I will try to post it.

     

    Thanks!

    Friday, December 14, 2007 6:43 PM