110 lines
4.0 KiB
Transact-SQL
110 lines
4.0 KiB
Transact-SQL
CREATE OR ALTER PROCEDURE pricing.process_queue
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
--------------------------------------------------------------------------------
|
|
-- Step 1: Insert input row into real queue table
|
|
--------------------------------------------------------------------------------
|
|
|
|
--------------------------------------------------------------------------------
|
|
-- Step 2: Enrich the row with chan, tier, cust, pltq
|
|
--------------------------------------------------------------------------------
|
|
UPDATE q
|
|
SET
|
|
q.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,
|
|
q.tier =
|
|
CASE SUBSTRING(bc.cclass, 2, 3)
|
|
WHEN 'DIR' THEN bc.tier
|
|
ELSE ISNULL(sc.tier, bc.tier)
|
|
END,
|
|
q.cust =
|
|
CASE SUBSTRING(bc.cclass, 2, 3)
|
|
WHEN 'DIS' THEN
|
|
CASE SUBSTRING(sc.cclass, 2, 3)
|
|
WHEN 'DIS' THEN q.ship
|
|
ELSE q.ship
|
|
END
|
|
ELSE q.bill
|
|
END,
|
|
q.pltq = i.mpck
|
|
FROM
|
|
pricing.price_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 pricing from target_prices
|
|
--------------------------------------------------------------------------------
|
|
|
|
DECLARE @updated TABLE (
|
|
id BIGINT,
|
|
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)
|
|
);
|
|
|
|
UPDATE q
|
|
SET
|
|
q.price = tp.price,
|
|
q.expl = (
|
|
SELECT
|
|
'target price' AS [source],
|
|
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] -- important if math is JSON
|
|
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
|
|
)
|
|
OUTPUT
|
|
inserted.id,
|
|
inserted.bill,
|
|
inserted.ship,
|
|
inserted.part,
|
|
inserted.stlc,
|
|
inserted.v1ds,
|
|
inserted.vol,
|
|
inserted.chan,
|
|
inserted.cust,
|
|
inserted.tier,
|
|
inserted.pltq,
|
|
inserted.price,
|
|
inserted.expl
|
|
INTO @updated
|
|
FROM pricing.price_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: Return just the enriched row
|
|
--------------------------------------------------------------------------------
|
|
SELECT * FROM @updated;
|
|
END;
|