Untitled

CREATE PROCEDURE sp_InsertSalesOrder
    @CustCode VARCHAR(10),
    @ProductDetails NVARCHAR(MAX)
AS
BEGIN
    DECLARE @SalesOrderNo VARCHAR(10)
    DECLARE @OrderDate DATETIME
    DECLARE @Price MONEY
    DECLARE @Payload NVARCHAR(MAX)
    DECLARE @NewID INT

    SET @OrderDate = GETDATE()

    -- Generate SalesOrderNo with format SOXXXX
    SELECT @NewID = ISNULL(MAX(CAST(SUBSTRING(SalesOrderNo, 3, 4) AS INT)), 0) + 1 FROM SalesOrder
    SET @SalesOrderNo = 'SO' + RIGHT('000' + CAST(@NewID AS VARCHAR(4)), 4)

    -- Parse JSON and insert to SalesOrder
    DECLARE @json NVARCHAR(MAX) = @ProductDetails
    DECLARE @ProductCode VARCHAR(10)
    DECLARE @Qty INT

    DECLARE @tblProductDetails TABLE (ProductCode VARCHAR(10), Qty INT)

    INSERT INTO @tblProductDetails (ProductCode, Qty)
    SELECT ProductCode, Qty
    FROM OPENJSON(@json)
    WITH (ProductCode VARCHAR(10), Qty INT)

    DECLARE cur CURSOR FOR 
        SELECT ProductCode, Qty FROM @tblProductDetails

    OPEN cur
    FETCH NEXT FROM cur INTO @ProductCode, @Qty

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Get Price based on date validation
        SELECT @Price = Price FROM Price 
        WHERE ProductCode = @ProductCode 
        AND @OrderDate BETWEEN PriceValidateFrom AND PriceValidateTo

        INSERT INTO SalesOrder (OrderDate, SalesOrderNo, CustCode, ProductCode, Qty, Price)
        VALUES (@OrderDate, @SalesOrderNo, @CustCode, @ProductCode, @Qty, @Price)

        FETCH NEXT FROM cur INTO @ProductCode, @Qty
    END

    CLOSE cur
    DEALLOCATE cur

    -- Prepare Payload for SalesOrderInterface
    SET @Payload = (SELECT @SalesOrderNo AS SalesOrderNo, @CustCode AS CustId, 
                    (SELECT ProductCode, Qty FROM @tblProductDetails FOR JSON PATH) AS OrderDetail
                    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)

    -- Insert into SalesOrderInterface
    INSERT INTO SalesOrderInterface (SalesOrderNo, Payload)
    VALUES (@SalesOrderNo, @Payload)

    -- Commit transaction
    COMMIT

    -- Return success message
    PRINT 'Data has been insert successfully'
END
GO