price_api/new_targets/basic_setup/single_price_call.ms.sql

CREATE OR ALTER PROCEDURE pricing.single_price_call
    @bill   VARCHAR(100),
    @ship   VARCHAR(100),
    @part   VARCHAR(100),
    @stlc   VARCHAR(100),
    @v1ds   VARCHAR(100),
    @vol    NUMERIC(18,6)
AS
BEGIN
    SET NOCOUNT ON;

	DECLARE @phist NVARCHAR(MAX);

    -- Declare table variable for the input row
    DECLARE @queue TABLE (
        bill    VARCHAR(100),
        ship    VARCHAR(100),
        part    VARCHAR(100),
        stlc    VARCHAR(100),
        v1ds    VARCHAR(100),
        vol     NUMERIC(18,6),
        chan    VARCHAR(50),
        cust    VARCHAR(100),
        tier    VARCHAR(50),
        pltq    NUMERIC(18,6),
        price   NUMERIC(18,6),
        expl    NVARCHAR(MAX),
        hist    NVARCHAR(MAX),
        LAST    nvarchar(max)
    );

    --------------------------------------------------------------------------------
    -- Step 1: Insert input row into queue
    --------------------------------------------------------------------------------
    INSERT INTO @queue (bill, ship, part, stlc, v1ds, vol)
    VALUES (@bill, @ship, @part, @stlc, @v1ds, @vol);

    --------------------------------------------------------------------------------
    -- Step 2: Enrich the row with chan, tier, cust, pltq
    --------------------------------------------------------------------------------
    UPDATE q
    SET
        chan = 
            CASE SUBSTRING(bc.cclass, 2, 3)
                WHEN 'DIS' THEN 
                    CASE SUBSTRING(sc.cclass, 2, 3)
                        WHEN 'DIS' THEN 'WHS'
                        ELSE 'DRP'
                    END
                ELSE 'DIR'
            END,
        tier =
            CASE SUBSTRING(bc.cclass, 2, 3)
                WHEN 'DIR' THEN bc.tier
                ELSE ISNULL(sc.tier, bc.tier)
            END,
        cust = 
            CASE SUBSTRING(bc.cclass, 2, 3)
                WHEN 'DIS' THEN 
                    CASE SUBSTRING(sc.cclass, 2, 3)
                        WHEN 'DIS' THEN bc.dba
                        ELSE sc.dba
                    END
                ELSE q.bill
            END,
        pltq = i.mpck
    FROM @queue q
    LEFT JOIN rlarp.cust bc ON bc.code = q.bill
    LEFT JOIN rlarp.cust sc ON sc.code = q.ship
    LEFT JOIN CMSInterfaceIn.[CMS.CUSLG].itemm i ON i.item = q.part;

    
	--------------------------------------------------------------------------------
	-- Step 3: Get last price info directly into @queue columns
	--------------------------------------------------------------------------------
	UPDATE q
	SET 
	    q.hist = (
	        SELECT TOP 1
	            j.qty,
	            j.price,
	            j.odate,
	            j.ordnum,
	            j.quoten
	        FROM pricing.lastprice lp
	        OUTER APPLY OPENJSON(lp.part_stats) AS p
	        OUTER APPLY OPENJSON(p.value)
	            WITH (
	                qty     NUMERIC(20,5),
	                price   NUMERIC(20,5),
	                odate   DATE,
	                ordnum  INT,
	                quoten  INT
	            ) AS j
	        WHERE 
	            lp.customer = q.cust
	            AND lp.mold = SUBSTRING(q.part,1,8)
	            AND p.[key] COLLATE SQL_Latin1_General_CP1_CI_AS = q.part
	        ORDER BY j.odate DESC
	        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
	    ),
	    q.last = (
	        SELECT TOP 1 
	            CAST(ROUND(j.price, 5) AS NVARCHAR(50))  -- must be string to store in NVARCHAR column
	        FROM pricing.lastprice lp
	        OUTER APPLY OPENJSON(lp.part_stats) AS p
	        OUTER APPLY OPENJSON(p.value)
	            WITH (
	                qty     NUMERIC(20,5),
	                price   NUMERIC(20,5),
	                odate   DATE,
	                ordnum  INT,
	                quoten  INT
	            ) AS j
	        WHERE 
	            lp.customer = q.cust
	            AND lp.mold = SUBSTRING(q.part,1,8)
	            AND p.[key] COLLATE SQL_Latin1_General_CP1_CI_AS = q.part
	        ORDER BY j.odate DESC
	    )
	FROM @queue q;

		
	
    --------------------------------------------------------------------------------
	-- Step 4: Apply pricing and embed price history + last price from queue columns
	--------------------------------------------------------------------------------
	UPDATE q
	SET 
	    price = tp.price,
	    expl = (
	        SELECT 
	            'target price'                                                              AS [source],
	            tp.price                                                                     AS [target_price],
	            CAST(q.last AS NUMERIC(20,5))                                                AS [last_price],
	            FLOOR(q.vol / NULLIF(q.pltq, 0))                                              AS [calculated_pallets],
	            ROUND(q.vol / NULLIF(q.pltq, 0), 5)                                           AS [exact_pallets],
	            CONCAT(tp.lower_bound, '-', ISNULL(CAST(tp.upper_bound AS VARCHAR), '∞'))   AS [volume range],
	            q.cust                                                                       AS [customer],
	            q.chan                                                                       AS [channel],
	            q.tier                                                                       AS [tier],
	            JSON_QUERY(tp.math)                                                          AS [target math],
	            JSON_QUERY(q.hist)                                                           AS [price history]
	        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
	    )
	FROM @queue q
	INNER JOIN pricing.target_prices tp ON
	    q.stlc = tp.stlc
	    AND q.v1ds = tp.ds
	    AND q.chan = tp.chan
	    AND q.tier = tp.tier
	    AND FLOOR(q.vol / NULLIF(q.pltq, 0)) >= tp.lower_bound
	    AND (
	        tp.upper_bound IS NULL OR FLOOR(q.vol / NULLIF(q.pltq, 0)) < tp.upper_bound
	    );


    --------------------------------------------------------------------------------
    -- Step Last: Return just the enriched row
    --------------------------------------------------------------------------------
    SELECT price, expl FROM @queue;
END;