Stored Procedures [rdld].[UpdatePaperworkRecd]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnYes
Parameters
NameData TypeMax Length (Bytes)
@OrderNovarchar(23)23
@RoadloadShipmentIdvarchar(64)64
@PaperworkRecddatetime8
Permissions
TypeActionOwning Principal
GrantExecuteMssExec
SQL Script
/****** Object:  StoredProcedure [rdld].[UpdatePaperworkRecd]  ******/
-- =============================================
-- Author:     Jeff Spindler
-- Date: 2/26/24
-- =============================================
CREATE PROCEDURE [rdld].[UpdatePaperworkRecd]
@OrderNo            VARCHAR(23),
@RoadloadShipmentId VARCHAR(64),
@PaperworkRecd      DATETIME = NULL
AS
   BEGIN
       SET NOCOUNT ON;
      SET DEADLOCK_PRIORITY LOW;
        DECLARE @OrdersPriKey       INT;
         DECLARE @rc INT = 0;

       DECLARE @theExternalSourceName VARCHAR(64)= 'Roadload';
       DECLARE @theRoadloadIdentifier VARCHAR(32);
       SET @theRoadloadIdentifier = LTRIM(RTRIM(@RoadloadShipmentId));
       DECLARE @InvoiceRequirementType INT=
       (
           SELECT InvoiceRequirementTypeID
           FROM InvoiceRequirementType
           WHERE description = 'Required'
       );
       DECLARE @AppSysUser int =
       (
           SELECT SysUserID
           FROM SysUser
           WHERE LastName = 'Application'
               AND FirstName = 'Roadload'
       );

       -- See if we have handled this order previously
      DECLARE @theXmlSystemId INT = dbo.GetVendorConnectXmlSystemID( 'rdld' );
       SET @OrdersPriKey =
       (
           SELECT top 1 OrderFID
           FROM XmlOrderLink
           WHERE XmlSystemFID = @theXmlSystemId
               AND ExternalKey = @theRoadloadIdentifier
           order by XmlOrderLinkID
       );
      IF @OrdersPriKey IS NULL
         BEGIN
            SELECT 13001 AS ErrorCode,
               'OrderNo ' + @OrderNo + ' not found' AS ErrorMessage FOR XML RAW;
         RETURN 13001;
      END;

      -- This will give the UPDATE Orders stmt info to handle the order history logging.  Otherwise, you would see "Direct SQL"
      DECLARE @theOrderAuditInfoFID BIGINT;
       EXEC PrepOrderForAuditLog
           @inSysUserID = @AppSysUser,
           @inUpdateSource = @theExternalSourceName,
           @outOrderAuditInfoFID = @theOrderAuditInfoFID OUTPUT;

      UPDATE Orders set
         OrderAuditInfoFID = @theOrderAuditInfoFID,
         PaperworkRecd = case when @PaperworkRecd is null then PaperworkRecd else @PaperworkRecd end
          WHERE PriKey = @OrdersPriKey;
   
      select @rc as ErrorCode, 'Ok' as ErrorMessage for xml RAW;
       RETURN 0;

   END
GO
GRANT EXECUTE ON  [rdld].[UpdatePaperworkRecd] TO [MssExec]
GO
Uses