/****** Object:  StoredProcedure [rdld].[FindBadXmlOrderLinkExternalKeys]  ******/
-- =============================================
-- Author:      Bryan Johnston
-- Date:        2024-07-17
-- Description:
-- =============================================
CREATE PROCEDURE [rdld].[FindBadXmlOrderLinkExternalKeys]
    @ToBeIgnoredExternalKeys VARCHAR(8000)
AS
BEGIN
    SET NOCOUNT ON;
    SET DEADLOCK_PRIORITY LOW;

    DECLARE @theXmlSystemId INT = dbo.GetVendorConnectXmlSystemID( 'rdld' );

    DECLARE @IgnoreExternalKeys TABLE (ExternalKey VARCHAR(10) NOT NULL);
    INSERT INTO @IgnoreExternalKeys SELECT * FROM rdld.split_string(@ToBeIgnoredExternalKeys, ',');
    SELECT
        XmlOrderLink.ExternalKey, Orders.OrderNo
    FROM
        XmlOrderLink
        INNER JOIN Orders ON Orders.PriKey = XmlOrderLink.OrderFID
    WHERE
        ExternalKey IN (
            SELECT
                U.ExternalKey
            FROM
                (
                    SELECT ExternalKey, XmlSystemFID, COUNT(*) AS theCount
                    FROM XmlOrderLink
                    WHERE XmlSystemFID = @theXmlSystemId
                            AND ExternalKey NOT IN (SELECT ExternalKey FROM @IgnoreExternalKeys)
                    GROUP BY ExternalKey, XmlSystemFID  HAVING COUNT(*) > 1
                ) U
        )
    ORDER BY ExternalKey, OrderFID
    FOR XML RAW;
END;
GO
GRANT EXECUTE ON  [rdld].[FindBadXmlOrderLinkExternalKeys] TO [MssExec]
GO