/****** Object:  StoredProcedure [rdld].[UpdateXmlOrderLink]  ******/
-- =============================================
-- Author:     Jeff Spindler
-- Date: 4/2023
-- Description:   
-- =============================================
CREATE PROCEDURE [rdld].[UpdateXmlOrderLink]
  @orderNo AS     NVARCHAR(64),
  @externalKey AS VARCHAR(60) -- ShipmentID
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @integratorKeyId AS NVARCHAR(4)= 'rdld';
   DECLARE @theVendorConnectXmlSystemID int = dbo.GetVendorConnectXmlSystemID(@integratorKeyId);
   DECLARE @theXmlOrderLinkNewRows int;

   DECLARE @ExistingOrdersPriKey INT = (
      SELECT TOP 1 OrderFID
      FROM
         XmlOrderLink
      WHERE
         XmlOrderLink.XmlSystemFID = @theVendorConnectXmlSystemID
         AND XmlOrderLink.ExternalKey = @externalKey
      ORDER BY
         OrderFID DESC
   );

   IF @ExistingOrdersPriKey IS NOT NULL
   BEGIN
      SELECT
         13004 AS ErrorCode,
         'ExternalKey ' + @externalKey + ' already exists for OrderFID = '
               + CAST(@ExistingOrdersPriKey AS VARCHAR(20))
               + '. Continuing is either unnecessary or will cause multiple records with same ExternalKey.'
            AS ErrorMessage FOR XML RAW;
      RETURN;
   END;

   DECLARE @OrdersPriKey INT=
   (
       SELECT TOP 1 PriKey
       FROM
         Orders
       WHERE
         OrderNo = @orderNo
   );

   IF @OrdersPriKey IS NULL
   BEGIN
      SELECT 13001 AS ErrorCode,
             'OrderNo ' + @OrderNo + ' not found' AS ErrorMessage FOR XML RAW;
      RETURN;
   END;

   INSERT INTO XmlOrderLink
   (
      xmlsystemfid,
      OrderFID,
      ExternalKey,
      SecondaryExternalKey
   )
   SELECT
      xmlsystemfid = @theVendorConnectXmlSystemID,
      OrderFID = @OrdersPriKey,
      ExternalKey = @externalKey,
      SecondaryExternalKey = NULL
   WHERE not exists(
      select top 1 1
      from XmlOrderLink
      where xmlsystemfid = @theVendorConnectXmlSystemID and
         OrderFID = @OrdersPriKey
   );

   SET @theXmlOrderLinkNewRows = @@ROWCOUNT;

   if( @theXmlOrderLinkNewRows = 0 )
   BEGIN
      UPDATE XmlOrderLink SET
         ExternalKey = @externalKey,
         SecondaryExternalKey = NULL
      WHERE xmlsystemfid = @theVendorConnectXmlSystemID and
         OrderFID = @OrdersPriKey;
   END;

   DECLARE @AppSysUser INT =
   (
       SELECT SysUserID
       FROM
            SysUser
       WHERE  LastName = 'Application'
              AND FirstName = 'Roadload'
   );

   UPDATE Orders
     SET  CreatedBy = @AppSysUser
   WHERE  PriKey = @OrdersPriKey;

   SELECT 0 AS ErrorCode,
             'OrderNo' + @OrderNo + ' link changed.' AS ErrorMessage FOR XML RAW;
   RETURN 0;
END;
GO
GRANT EXECUTE ON  [rdld].[UpdateXmlOrderLink] TO [MssExec]
GO