price_api/new_targets/procs/single_price_call_func.ms.sql

CREATE OR ALTER FUNCTION pricing.single_price_call_fn (
    @bill VARCHAR(100),
    @ship VARCHAR(100),
    @part VARCHAR(100),
    @stlc VARCHAR(100),
    @v1ds VARCHAR(100),
    @vol  NUMERIC(18, 6)
)
RETURNS TABLE
AS
RETURN (
    WITH queue AS (
        SELECT
            @bill AS bill,
            @ship AS ship,
            @part AS part,
            @stlc AS stlc,
            @v1ds AS v1ds,
            @vol  AS vol
    ),
    enriched AS (
        SELECT
            q.*,
            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 ISNULL(sc.tier, bc.tier)
            END AS tier,
            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 AS cust,
            i.mpck AS pltq,
            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 AS plevel
        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
    ),
    hist AS (
        SELECT TOP 1
            e.part,
            j.qty,
            j.price AS last_price,
            j.odate AS last_date,
            j.ordnum AS last_order,
            j.quoten AS last_quote,
            JSON_QUERY(lp.part_stats) AS full_history
        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
        INNER JOIN enriched e ON
            lp.customer = e.cust AND
            lp.mold = SUBSTRING(e.part, 1, 8)
        WHERE
            p.[key] COLLATE SQL_Latin1_General_CP1_CI_AS = e.part
        ORDER BY j.odate DESC
    ),
    list_price_cte AS (
        SELECT *,
            ROW_NUMBER() OVER (PARTITION BY e.part ORDER BY p.price ASC) AS rn
        FROM enriched e
        INNER JOIN CMSInterfaceIn."CMS.CUSLG".IPRCBHC i
            ON TRIM(i.jbplvl) = TRIM(e.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 = e.part
            AND e.vol >= p.vb_from
            AND (p.vb_to IS NULL OR e.vol < p.vb_to)
    ),
    FINAL AS (
        SELECT
            e.part,
            tp.price,
            e.plevel,
            h.full_history,
            h.last_price,
            h.last_date,
            h.last_order,
            h.last_quote,
            lp.price AS list_price,
            lp.jcplcd AS list_code,
            g.guidance_price,
            g.guidance_reason,
            (
                SELECT
                    'target price' AS source,
                    tp.price AS target_price,
                    h.last_price,
                    FORMAT(h.last_date, 'yyyy-MM-dd') AS last_date,
                    h.last_order,
                    h.last_quote,
                    lp.price AS list_price,
                    lp.jcplcd AS list_code,
                    FLOOR(e.vol / NULLIF(e.pltq, 0)) AS calculated_pallets,
                    CAST(e.vol / NULLIF(e.pltq, 0) AS NUMERIC(20,5)) AS exact_pallets,
                    CONCAT(tp.lower_bound, '-', ISNULL(CAST(tp.upper_bound AS VARCHAR), '∞')) AS [volume range],
                    e.cust AS customer,
                    e.chan AS channel,
                    RTRIM(e.tier) AS tier,
                    JSON_QUERY(tp.math) AS [target math],
--                    JSON_QUERY(h.full_history) AS [price history],
                    g.guidance_price,
                    g.guidance_reason
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            ) AS expl
        FROM enriched e
        INNER JOIN pricing.target_prices tp ON
            e.stlc = tp.stlc AND
            e.v1ds = tp.ds AND
            e.chan = tp.chan AND
            RTRIM(e.tier) = RTRIM(tp.tier) AND
            FLOOR(e.vol / NULLIF(e.pltq, 0)) >= tp.lower_bound AND
            (tp.upper_bound IS NULL OR FLOOR(e.vol / NULLIF(e.pltq, 0)) < tp.upper_bound)
        LEFT JOIN hist h ON h.part = e.part
        LEFT JOIN list_price_cte lp ON lp.part = e.part AND lp.rn = 1
        CROSS APPLY pricing.guidance_logic(
            tp.price,
            h.last_price,
            lp.price
        ) g
    )
    SELECT
        part,
        price AS target_price,
        plevel,
        last_price,
        last_date,
        last_order,
        last_quote,
        list_price,
        list_code,
        guidance_price,
        guidance_reason,
        expl
    FROM FINAL
);