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" />