[rdld].[UpdatePaperworkRecd]
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'
);
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;
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