KI (All) - PO - Authorization limits can be bypassed if you convert a PO Quote to an Order.

Issue

User Story

When Purchase Order Authorizations are turned on, I expect Authorizations/credit limits to be respected by all processes.

Problem

Currently, there does not seem to be an authorization limit applied to orders that are raised as Quotes and then Converted to Purchase Orders.

Expected Result

When Purchase Order Authorizations are on, all workflows should have the limits enforced on them. Converting Quotes should not circumvent PO Authorization procedures.

Workaround

Apply workaround trigger to Purchase Order Header table.

SQL trigger code for Purchase Order Authorisation fix

CREATE TRIGGER [dbo].[X_SKUNKWORKS_PURCHORD_HDR_AUTHORIZATIONS]
ON [dbo].[PURCHORD_HDR]
FOR UPDATE
AS
/** CUSTOM - Exo Skunkworks
PURPOSE 1 - Workaround for SR 173910106781 - PO Quote conversion ignores auth limit.

Trigger version Created: Exo 2019.4**/
BEGIN
SET NOCOUNT ON
IF TRIGGER_NESTLEVEL( ( SELECT object_id FROM sys.triggers
WHERE name = 'X_SKUNKWORKS_PURCHORD_HDR_AUTHORIZATIONS' )) > 1 /*this update is not coming from some other trigger*/ RETURN
--Only run for single line updates. Otherwise abort trigger.
IF (select COUNT(*) from inserted)>1
RETURN

--Check Profile values
DECLARE @AUTHORISEPURCHORDERS CHAR(1)
EXECUTE GET_PROFILE_VALUE 'AUTHORISEPURCHORDERS', @AUTHORISEPURCHORDERS OUTPUT
DECLARE @EXTRAPURCHORDERSTATUS CHAR(1)
EXECUTE GET_PROFILE_VALUE 'EXTRAPURCHORDERSTATUS', @EXTRAPURCHORDERSTATUS OUTPUT
--Only proceed if BOTH these profiles are on. (Required for PO Authorizations to apply to a DB)
IF (@AUTHORISEPURCHORDERS='Y' AND @EXTRAPURCHORDERSTATUS='Y')
BEGIN
--Only apply if Order is being converted from QUOTE (3) to NOT PROCESSED(0)
IF EXISTS (select 1 from inserted as i
inner join deleted as d
on d.seqno=i.seqno
where i.status=0
and d.status=3)
BEGIN
--Establish StaffNo
DECLARE @STAFFNO INT
Select @STAFFNO=DBO.FN_GET_STAFF_NO()
--Confirm how much the Staff member can Authorize.
DECLARE @StkAuth FLOAT,@LookupAuth FLOAT
Select @StkAuth=STOCK_AUTH_AMT, @LookupAuth=NON_STOCK_AUTH_AMT
FROM STAFF where staffno=@STAFFNO
SELECT @StkAuth=ISNULL(@StkAuth,0), @LookupAuth=ISNULL(@LookupAuth,0)
--Confirm amounts of order.
DECLARE @StkAmt FLOAT,@LookupAmt FLOAT
select @StkAmt=SUM(CASE WHEN Codetype ='S' THEN linetotal ELSE 0 END)
,@LookupAmt=SUM(CASE WHEN Codetype <>'S' THEN linetotal ELSE 0 END)
from PURCHORD_LINES AS L
INNER JOIN inserted as i on i.seqno=L.HDR_SEQNO
--If conversion is higher than authorize limit, rollback to Stage 4 (Awaiting Authorization)
IF ( @StkAmt>@StkAuth OR @LookupAmt>@LookupAuth )
BEGIN
UPDATE P
set STATUS=4
FROM PURCHORD_HDR AS P
INNER JOIN inserted as i on i.seqno=P.SEQNO
END
END
END
SET NOCOUNT OFF
END

Resolution

Attached to Problem ticket EXB-5444 for resolution in a future release.