PROCEDURE [GetAS2AndEDIInformationByAgreementName]

varchar(255)

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

.[tpm].[Partner] sender inner join

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

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

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

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

(

select

.Value as AS2From,

.Value as AS2To,

.Name as SenderName, receiver.Name as ReceiverName

from

.[tpm].[Partner] sender inner join

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

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

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

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

WHERE

.Name = @AgreementName and

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

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

WHERE

.Name = @AgreementName and

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

FOR XML EXPLICIT

[BizTalkMgmtDb]

@return_value int

@return_value = [dbo].[GetAS2AndEDIInformationByAgreementName]

@AgreementName = N’Acme To EDIGUIDANCE’

‘Return Value’ = @return_value