70 lines
2.1 KiB
PL/PgSQL
70 lines
2.1 KiB
PL/PgSQL
CREATE OR REPLACE PROCEDURE pricequote.process_queue_proc()
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
|
|
--------------------------------------------------------------------------------
|
|
-- Step 1: Set channel, tier, and pallet quantity
|
|
--------------------------------------------------------------------------------
|
|
UPDATE pricequote.price_queue s
|
|
SET
|
|
chan = cr.chan,
|
|
tier = cr.tier,
|
|
pltq = cr.mpck,
|
|
cust = cr.cust
|
|
FROM (
|
|
SELECT
|
|
q.bill,
|
|
q.ship,
|
|
q.part,
|
|
i.mpck,
|
|
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 AS chan,
|
|
CASE SUBSTRING(bc.cclass, 2, 3)
|
|
WHEN 'DIR' THEN bc.tier
|
|
ELSE COALESCE(sc.tier, bc.tier)
|
|
END AS tier,
|
|
CASE SUBSTRING(bc.cclass, 2, 3)
|
|
WHEN 'DIR' THEN bc.dba
|
|
ELSE COALESCE(sc.dba, bc.dba)
|
|
END AS cust
|
|
FROM pricequote.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 "CMS.CUSLG".itemm i ON i.item = q.part
|
|
) cr
|
|
WHERE
|
|
cr.bill = s.bill AND
|
|
COALESCE(cr.ship, '') = COALESCE(s.ship, '') AND
|
|
cr.part = s.part;
|
|
|
|
--------------------------------------------------------------------------------
|
|
-- Step 2: Set price and explanation from target_prices
|
|
--------------------------------------------------------------------------------
|
|
UPDATE pricequote.price_queue q
|
|
SET
|
|
price = tp.price,
|
|
expl = jsonb_build_object(
|
|
'source', 'target price',
|
|
'calculated_pallets', FLOOR(q.vol / NULLIF(q.pltq, 0))::INT,
|
|
'exact_pallets', ROUND(q.vol / NULLIF(q.pltq, 0), 5),
|
|
'volume range', tp.vol
|
|
)
|
|
FROM pricequote.target_prices tp
|
|
WHERE
|
|
q.stlc = tp.stlc
|
|
AND q.v1ds = tp.ds
|
|
AND q.chan = tp.chan
|
|
AND q.tier = tp.tier
|
|
AND tp.vol @> FLOOR(q.vol / NULLIF(q.pltq, 0))::INT;
|
|
|
|
END;
|
|
$$;
|
|
|