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

Add to BizTalkMGMTDB
BASED ON AGREEMENT NAME
 
STORED PROC : For SINGLE X12 and SINGLE AS2 Per Party->Party

— =============================================
— Author: <Mark Rowe>
— Create date: <September 17, 2010>
— Description: <EDIGUIDANCE Addin>
— =============================================

CREATE

PROCEDURE [GetAS2AndEDIInformationByAgreementName]

(

@AgreementName

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

agreement

.Name = @AgreementName and

businessIdentityReceiver

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

 

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

 

WHERE

agreement

.Name = @AgreementName and

businessIdentityReceiver

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

 

FOR XML EXPLICIT

 

END
GO

 
 
 
TEST:
 

USE

[BizTalkMgmtDb]

GO
DECLARE

@return_value int

EXEC

@return_value = [dbo].[GetAS2AndEDIInformationByAgreementName]

 

@AgreementName = N’Acme To EDIGUIDANCE’

SELECT

‘Return Value’ = @return_value

GO
 
RESULT

<

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

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

USE

[BizTalkMgmtDb]

GO

/****** Object: StoredProcedure [dbo].[GetAS2AndEDIInformationByAgreementName] Script Date: 09/18/2010 01:14:01 ******/

SET

ANSI_NULLS ON

GO
SET

QUOTED_IDENTIFIER ON

GO
 

— =============================================
— Author: <Mark Rowe>
— Create date: <September 17, 2010>
— Description: <EDIGUIDANCE Addin>
— =============================================

ALTER

PROCEDURE [dbo].[GetEDIInformationByAgreementName]

(

@AgreementName

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

agreement

.Name = @AgreementName and

agreement

.Protocol = ‘x12’ and businessIdentityReceiver.Qualifier <> ‘AS2Identity’ and businessIdentitySender.Qualifier <>‘AS2Identity’

 
 

FOR XML EXPLICIT

 

END
 
GO
OUTPUT:

<

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

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

USE

[BizTalkMgmtDb]

GO

/****** Object: StoredProcedure [dbo].[GetAS2AndEDIInformationByAgreementName] Script Date: 09/18/2010 01:14:01 ******/

SET

ANSI_NULLS ON

GO
SET

QUOTED_IDENTIFIER ON

GO
 

— =============================================
— Author: <Mark Rowe>
— Create date: <September 17, 2010>
— Description: <EDIGUIDANCE Addin>
— =============================================

ALTER

PROCEDURE [dbo].[GetAS2InformationByAgreementName]

(

@AgreementName

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

agreement

.Name = @AgreementName 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" />

 

Categories: Biztalk-WorkFlow

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: