How to retrieve EDI Partner Information for context based EDI routing Part 1

 
Based on Sender and Receiver Party Names
 
Retrieve ISA SEGMENTS for EDI Context Based routing.
Something we needed for EDIGUIDANCE.   
 
 
NOTE: THIS WILL BE CHANGING AS I LEARN TO SEPERATE OUT THE EDI FROM EDIFACT.  IF YOU HAVE NO EDIFACT THIS SHOULD WORK FINE.
 
I dug around the databases after creating a party.
 
 
How to grab the AS2 and EDI based on Party to and From Names

STORED PROC : For Single X12 and AS2 Agreements from Party->Party

— USE

[BizTalkMgmtDb]

GO

/****** Object: StoredProcedure [dbo].[GetAS2AndEDIInformationBySenderAndReceiverName] Script Date: 09/18/2010 01:07:29 ******/

SET

ANSI_NULLS ON

GO

SET

QUOTED_IDENTIFIER ON

GO

— =============================================

— Author: <Mark Rowe>

— Create date: <September 17, 2010>

— Description: <EDIGUIDANCE Addin>

— =============================================

CREATE PROCEDURE [dbo].[GetAS2AndEDIInformationBySenderAndReceiverName]

(

@Sender

varchar(255),

@Receiver

varchar(255)

)

AS

BEGIN

select 1 as tag,

null as parent,

IsNull(agreement.Name,) as ‘EDIHEADER!1!AgreementName’,

IsNull(sender.Name,) as ‘EDIHEADER!1!SenderName’, IsNull(receiver.Name,) as ‘EDIHEADER!1!ReceiverName’,

IsNull(businessIdentitySender.Qualifier,) as ‘EDIHEADER!1!ISA5’,IsNull(businessIdentitySender.Value,) as ‘EDIHEADER!1!ISA6’,

IsNull(businessIdentityReceiver.Qualifier,) as ‘EDIHEADER!1!ISA7’, IsNull(businessIdentityReceiver.Value,) as ‘EDIHEADER!1!ISA8’,

IsNull(AS2.AS2From,) as ‘EDIHEADER!1!AS2From’, IsNull(AS2.AS2To,) as ‘EDIHEADER!1!AS2To’

from

[BizTalkMgmtDb]

.[tpm].[Partner] sender inner join

[BizTalkMgmtDb]

.[tpm].[Agreement] agreement on sender.PartnerId = agreement.SenderProfileId inner join

[BizTalkMgmtDb]

.[tpm].[BusinessIdentity] businessIdentitySender on businessIdentitySender.ProfileId = sender.PartnerId inner join

[BizTalkMgmtDb]

.[tpm].[Partner] receiver on receiver.PartnerId = agreement.ReceiverProfileId inner join

[BizTalkMgmtDb]

.[tpm].[BusinessIdentity] businessIdentityReceiver on businessIdentityReceiver.ProfileId = receiver.PartnerId left outer join

(

select

businessIdentitySender

.Value as AS2From,

businessIdentityReceiver

.Value as AS2To,

sender

.Name as SenderName, receiver.Name as ReceiverName

from

[BizTalkMgmtDb]

.[tpm].[Partner] sender inner join

[BizTalkMgmtDb]

.[tpm].[Agreement] agreement on sender.PartnerId = agreement.SenderProfileId inner join

[BizTalkMgmtDb]

.[tpm].[BusinessIdentity] businessIdentitySender on businessIdentitySender.ProfileId = sender.PartnerId inner join

[BizTalkMgmtDb]

.[tpm].[Partner] receiver on receiver.PartnerId = agreement.ReceiverProfileId inner join

[BizTalkMgmtDb]

.[tpm].[BusinessIdentity] businessIdentityReceiver on businessIdentityReceiver.ProfileId = receiver.PartnerId

WHERE

sender

.Name = @Sender and receiver.Name = @Receiver and

agreement

.Protocol = ‘as2’ and

businessIdentityReceiver

.Qualifier = ‘AS2Identity’ and businessIdentitySender.Qualifier = ‘AS2Identity’

) as AS2 on AS2.SenderName = sender.Name and AS2.ReceiverName = receiver.Name

WHERE

sender

.Name = @Sender and receiver.Name = @Receiver and

agreement

.Protocol = ‘x12’

and businessIdentityReceiver.Qualifier <> ‘AS2Identity’ and businessIdentitySender.Qualifier <>‘AS2Identity’

FOR XML EXPLICIT

END

GO

__________________________________

The Test:

— USE

[BizTalkMgmtDb]

GO

DECLARE

@return_value int

EXEC

@return_value = [dbo].[GetAS2AndEDIInformationBySenderAndReceiverName]

@Sender = N’ACME CORPORATION’,

@Receiver

= N’EDIGUIDANCE’

SELECT

‘Return Value’ = @return_value

GO

OUTPUT

<EDIHEADER AgreementName="Acme To EDIGUIDANCE" SenderName="ACME CORPORATION" ReceiverName="EDIGUIDANCE" ISA5="ZZ" ISA6="ACME" ISA7="ZZ" ISA8="EDIGUIDANCE" AS2From="ACME" AS2To="EDIGUIDANCE" />

 

 

STORED PROC : For Multiple X12 Agreements from Party->Party

— USE [BizTalkMgmtDb]

GO

/****** Object: StoredProcedure [dbo].[GetEDIInformationBySenderAndReceiverName] Script Date: 09/18/2010 01:51:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

— =============================================

— Author: <Mark Rowe>

— Create date: <September 17, 2010>

— Description: <EDIGUIDANCE Addin>

— =============================================

CREATE PROCEDURE [dbo].[GetEDIInformationBySenderAndReceiverName]

(

@Sender varchar(255),

@Receiver varchar(255)

)

AS

BEGIN

select 1 as tag,

null as parent,

IsNull(agreement.Name,) as ‘EDIHEADER!1!AgreementName’,

IsNull(sender.Name,) as ‘EDIHEADER!1!SenderName’, IsNull(receiver.Name,) as ‘EDIHEADER!1!ReceiverName’,

IsNull(businessIdentitySender.Qualifier,) as ‘EDIHEADER!1!ISA5’,IsNull(businessIdentitySender.Value,) as ‘EDIHEADER!1!ISA6’,

IsNull(businessIdentityReceiver.Qualifier,) as ‘EDIHEADER!1!ISA7’, IsNull(businessIdentityReceiver.Value,) as ‘EDIHEADER!1!ISA8’

from

[BizTalkMgmtDb].[tpm].[Partner] sender inner join

[BizTalkMgmtDb].[tpm].[Agreement] agreement on sender.PartnerId = agreement.SenderProfileId inner join

[BizTalkMgmtDb].[tpm].[BusinessIdentity] businessIdentitySender on businessIdentitySender.ProfileId = sender.PartnerId inner join

[BizTalkMgmtDb].[tpm].[Partner] receiver on receiver.PartnerId = agreement.ReceiverProfileId inner join

[BizTalkMgmtDb].[tpm].[BusinessIdentity] businessIdentityReceiver on businessIdentityReceiver.ProfileId = receiver.PartnerId

WHERE

sender.Name = @Sender and receiver.Name = @Receiver and

agreement.Protocol = ‘x12’

and businessIdentityReceiver.Qualifier <> ‘AS2Identity’ and businessIdentitySender.Qualifier <>‘AS2Identity’

FOR XML EXPLICIT

END

GO

OUTPUT

<EDIHEADER AgreementName="ACME to EDIGUIDANCE AS2" SenderName="ACME CORPORATION" ReceiverName="EDIGUIDANCE" AS2From="ACME" AS2To="EDIGUIDANCE" />

 

STORED PROC : For Multiple AS2 Agreements from Party->Party based on party names

 

— USE [BizTalkMgmtDb]

GO

/****** Object: StoredProcedure [dbo].[GetAS2InformationBySenderAndReceiverName] Script Date: 09/18/2010 01:52:13 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

— =============================================

— Author: <Mark Rowe>

— Create date: <September 17, 2010>

— Description: <EDIGUIDANCE Addin>

— =============================================

CREATE PROCEDURE [dbo].[GetAS2InformationBySenderAndReceiverName]

(

@Sender varchar(255),

@Receiver varchar(255)

)

AS

BEGIN

select 1 as tag,

null as parent,

agreement.Name ‘EDIHEADER!1!AgreementName’,

sender.Name as ‘EDIHEADER!1!SenderName’, receiver.Name as ‘EDIHEADER!1!ReceiverName’,

businessIdentitySender.Value as ‘EDIHEADER!1!AS2From’,

businessIdentityReceiver.Value as‘EDIHEADER!1!AS2To’

from

[BizTalkMgmtDb].[tpm].[Partner] sender inner join

[BizTalkMgmtDb].[tpm].[Agreement] agreement on sender.PartnerId = agreement.SenderProfileId inner join

[BizTalkMgmtDb].[tpm].[BusinessIdentity] businessIdentitySender on businessIdentitySender.ProfileId = sender.PartnerId inner join

[BizTalkMgmtDb].[tpm].[Partner] receiver on receiver.PartnerId = agreement.ReceiverProfileId inner join

[BizTalkMgmtDb].[tpm].[BusinessIdentity] businessIdentityReceiver on businessIdentityReceiver.ProfileId = receiver.PartnerId

WHERE

sender.Name = @Sender and receiver.Name = @Receiver and

agreement.Protocol = ‘as2’ and

businessIdentityReceiver.Qualifier = ‘AS2Identity’ and businessIdentitySender.Qualifier = ‘AS2Identity’

FOR XML EXPLICIT

END

GO

OUTPUT

<EDIHEADER AgreementName="ACME to EDIGUIDANCE AS2" SenderName="ACME CORPORATION" ReceiverName="EDIGUIDANCE" AS2From="ACME" AS2To="EDIGUIDANCE" />

 

Share on Facebook

Leave a Reply

Your email address will not be published. Required fields are marked *