Blocking Sales Order Quanity SP Notification

[geraldhans]

Hi All

I attempt to block Sales Order from being generated supposedly Quantity in Sales Order is greater then allocation quantity in UDT I created. Lets name the UDT as GH_Item_Allocation. You can see the UDT illustration as follows

Now when I create a Sales Order, should the quantity in Order exceeds the quantity in Allocation Quantity minus the total sales order generated for specific customers and item code has reach its allocation quantity, Sales Order cannot be generated. To explain more, below is illustrations

Sept 11 - Customer DXY Inc. buy Instant Noodles - Quantity 100 -> Sales Order Generated
Sept 12 - Customer DXY Inc. want to buy Instant Noodles - Quantity 100 -> Sales Order cannot be generated, because Customer DXY Inc. has recently purchased 100 instant noodles on 11th Sept, and they allocation quantity is only 100 and they have ordered 100 instant noodles the day before

I attempt to wrote this query, but the query is not working. Appreciate your help

IF @object_type = '17' AND (@transaction_type = 'A' OR @transaction_type = 'U')
BEGIN
    IF EXISTS (
        SELECT 1
        FROM ORDR T0
        INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
        INNER JOIN [dbo].[@GH_ITEM_ALLOCATION] T2 ON T2.U_Item_Code = T1.ItemCode AND T2.U_BP_Code = T0.CardCode
        WHERE T0.DocEntry = @list_of_cols_val_tab_del
        AND T1.Quantity >= T2.U_Alloc_Qty - 
		(
            SELECT SUM(T4.Quantity)
            FROM ORDR T3
            INNER JOIN RDR1 T4 ON T3.DocEntry = T4.DocEntry
            WHERE T3.CardCode = T0.CardCode AND T3.CardCode = T2.U_BP_Code
            AND T4.ItemCode = T2.U_Item_Code AND T1.ItemCode = T2.U_Item_Code
            AND T3.DocDate BETWEEN '2023-08-01' AND '2023-12-31'
        )
        AND T1.ItemCode <> 'NO-N-00-00000-0000' -- Exclude this specific item
    )
    BEGIN
        SET @error = '15002'
        SET @error_message = 'You Cannot Create Sales Order - Quantity in Sales Order exceeds the allocated quantity for this customer.'
    END
END

Thank YouRegardsGerald