CREATE OR ALTER PROCEDURE pricing.single_price_call @bill VARCHAR(100), @ship VARCHAR(100), @part VARCHAR(100), @v1ds VARCHAR(100), @vol NUMERIC(18,6) AS BEGIN SET NOCOUNT ON; -- Working table for enriched pricing request 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), volume_range TEXT, plevel NVARCHAR(20), listprice NUMERIC(20,5), listcode VARCHAR(10), hist NVARCHAR(MAX), last_price NUMERIC(20,5), last_date DATE, last_order NVARCHAR(10), last_quote NVARCHAR(10), tprice NUMERIC(20,5), guidance_price NUMERIC(20,5), guidance_reason NVARCHAR(MAX), expl NVARCHAR(MAX), expl_pretty NVARCHAR(MAX) ); -------------------------------------------------------------------------------- -- Step 1: Seed the queue with input row -------------------------------------------------------------------------------- INSERT INTO @queue (bill, ship, part, v1ds, vol, expl) VALUES (@bill, @ship, @part, @v1ds, @vol, '{}'); -------------------------------------------------------------------------------- -- Step 2: Enrich with channel, tier, customer, pack quantity, and price level -------------------------------------------------------------------------------- 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, plevel = CASE SUBSTRING(bc.cclass, 2, 3) WHEN 'DIS' THEN CASE SUBSTRING(sc.cclass, 2, 3) WHEN 'DIS' THEN sc.plevel ELSE bc.plevel END ELSE bc.plevel END, stlc = substring(q.part,1,8) 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: Apply target price and embed metadata as JSON -------------------------------------------------------------------------------- UPDATE q SET tprice = tp.price, expl = ( SELECT 'target price' AS [source], tp.price AS [target_price], FLOOR(q.vol / NULLIF(q.pltq, 0)) AS [calculated_pallets], CAST(ROUND(q.vol / NULLIF(q.pltq, 0), 5) AS NUMERIC(20,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], TRIM(q.tier) AS [tier], JSON_QUERY(tp.math) AS [target_math] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER ), volume_range = CONCAT(tp.lower_bound, '-', ISNULL(CAST(tp.upper_bound AS VARCHAR), '∞')) 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 4: Pull last sale data and embed in columns and JSON -------------------------------------------------------------------------------- UPDATE q SET hist = JSON_MODIFY('{}', '$.full_history', JSON_QUERY(lp.part_stats)), last_price = j.price, last_date = j.odate, last_order = j.ordnum, last_quote = j.quoten, expl = JSON_MODIFY( JSON_MODIFY( JSON_MODIFY( JSON_MODIFY( ISNULL(q.expl, '{}'), '$.last_price', j.price ), '$.last_date', CONVERT(NVARCHAR(10), j.odate, 23) ), '$.last_order', j.ordnum ), '$.last_quote', j.quoten ) FROM @queue q JOIN pricing.lastprice lp ON lp.customer = q.cust AND lp.mold = SUBSTRING(q.part, 1, 8) OUTER APPLY ( SELECT TOP 1 * FROM 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 p.[key] COLLATE SQL_Latin1_General_CP1_CI_AS = q.part ORDER BY j.odate DESC ) AS j; -------------------------------------------------------------------------------- -- Step 5: Add list price info from external pricelist -------------------------------------------------------------------------------- WITH ranked_prices AS ( SELECT q.bill, q.ship, q.part, q.stlc, q.v1ds, q.vol, CAST(p.price AS NUMERIC(20,5)) AS price, p.jcplcd, ROW_NUMBER() OVER ( PARTITION BY q.bill, q.ship, q.part, q.stlc, q.v1ds, q.vol ORDER BY p.price ASC ) AS rn FROM @queue q INNER JOIN CMSInterfaceIn."CMS.CUSLG".IPRCBHC i ON TRIM(i.jbplvl) = TRIM(q.plevel) AND CAST(GETDATE() AS DATE) BETWEEN i.jbfdat AND i.jbtdat INNER JOIN pricing.pricelist_ranged p ON p.jcplcd = TRIM(i.jbplcd) AND p.jcpart = q.part AND q.vol >= p.vb_from AND (p.vb_to IS NULL OR q.vol < p.vb_to) ) UPDATE q SET expl = JSON_MODIFY( JSON_MODIFY( ISNULL(q.expl, '{}'), '$.list_price', rp.price ), '$.list_code', rp.jcplcd ) ,listcode = rp.jcplcd ,listprice = rp.price FROM @queue q JOIN ranked_prices rp ON q.bill = rp.bill AND q.ship = rp.ship AND q.part = rp.part AND q.stlc = rp.stlc AND q.v1ds = rp.v1ds AND q.vol = rp.vol AND rp.rn = 1; -------------------------------------------------------------------------------- -- Step 6: Compute guidance price and logic, and embed in JSON -------------------------------------------------------------------------------- UPDATE q SET guidance_price = g.guidance_price, guidance_reason = g.guidance_reason, expl = JSON_MODIFY( JSON_MODIFY( ISNULL(q.expl, '{}'), '$.guidance_reason', g.guidance_reason ), '$.guidance_price', g.guidance_price ) FROM @queue q CROSS APPLY pricing.guidance_logic( CAST(JSON_VALUE(q.expl, '$.target_price') AS NUMERIC(20,5)), CAST(JSON_VALUE(q.expl, '$.last_price') AS NUMERIC(20,5)), CAST(JSON_VALUE(q.expl, '$.list_price') AS NUMERIC(20,5)), CAST(JSON_VALUE(q.expl, '$.last_date') AS DATE) ) g; -------------------------------------------------------------------------------- -- Step 7: Clean up for UI -------------------------------------------------------------------------------- UPDATE q SET expl_pretty = ( SELECT 'Target Price' AS [target_price.label], q.tprice AS [target_price.value], 'Volume Range' AS [volume_range.label], q.volume_range AS [volume_range.value], 'Channel' AS [channel.label], q.chan AS [channel.value], 'Tier' AS [tier.label], q.tier AS [tier.value], 'Last Price' AS [last_price.label], q.last_price AS [last_price.value], 'Last Date' AS [last_date.label], q.last_date AS [last_date.value], 'Last Order' AS [last_order.label], q.last_order AS [last_order.value], 'List Price' AS [list_price.label], q.listprice AS [list_price.value], 'List Code' AS [list_code.label], q.listcode AS [list_code.value], 'Guidance Reason' AS [guidance_reason.label], q.guidance_reason AS [guidance_reason.value], 'Guidance Price' AS [guidance_price.label], q.guidance_price AS [guidance_price.value] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER ) FROM @queue q; -------------------------------------------------------------------------------- -- Final: Return the enriched result row -------------------------------------------------------------------------------- SELECT * FROM @queue; END;