sales_walk/sql/schema.pg.sql

/*
walk
sales_walk_agg
sales_walk
sales_walk_seg_agg
sales_walk_seg
*/



CREATE TABLE IF NOT EXISTS rlarp.walk_r1 (
     bill_cust  text 
    ,ship_cust  text
    ,bucket     text
    ,attainment numeric
    ,notes      text
    ,fcnotes    text
    ,PRIMARY KEY (bill_cust, ship_cust)
);

GRANT ALL ON TABLE rlarp.walk_r1 TO PUBLIC;
DROP VIEW IF EXISTS rlarp.sales_walk_r1_seg;
DROP VIEW IF EXISTS rlarp.sales_walk_r1;

------------------------------------------sales walk agg---------------------------------------------------
DROP MATERIALIZED VIEW IF EXISTS rlarp.sales_walk_r1_agg ;

CREATE MATERIALIZED VIEW rlarp.sales_walk_r1_agg AS
WITH 
act as (
SELECT 
	bill_dba  
	,ship_dba 
	,dsm 
	--,os.glec
	--,m.biggroup 
	--,os.priceg
	,COALESCE(sum(pounds) filter (WHERE sseas = 2023 AND version = 'Actual' AND SUBSTRING(smon,1,2) <= '12'),0) "Actual 2023"
	,COALESCE(sum(pounds) filter (WHERE sseas = 2024 AND version = 'Actual'),0)                                 "Actual 2024"
 	,COALESCE(sum(pounds) filter (WHERE version = 'Actual' AND ostatus LIKE 'Open%' AND rseas <= 2024),0)       "Open Ord"
	,COALESCE(sum(pounds) filter (WHERE version = 'Quotes'),0)						                            "Quotes"
FROM
	rlarp.osm_stack os
	LEFT OUTER JOIN rlarp.molds m ON
		m.stlc = substring(os.product ,1,8)
WHERE 
	(
		(
			sseas IN (2023,2024)
			AND version = 'Actual'
			AND ostatus = 'Shipped'
		)
		OR (
			version = 'Quotes'
			AND odate >= '2023-05-01'::date
		)
		OR (ostatus LIKE 'Open%')
	)
	AND calc_status <> 'CANCELED'
	AND substring(os.glec,1,1) <= '2'
    AND os.fs_line = '41010'
    AND COALESCE(ship_dba,'') <> ''
GROUP BY
	bill_dba  
	,ship_dba 
	,dsm
	--,os.glec
	--,m.biggroup 
	--,os.priceg
)
,bgt AS (
    SELECT 
         bill_dba  
        ,ship_dba 
        ,dsm 
        ,sum(sales_usd) sales
        ,sum(pounds) pounds
        --string_agg(distinct mold,', ') 
    FROM 
        rlarp.osm_stack
    WHERE   
            version = 'Budget'
        AND oseas = '2024'
    GROUP BY 
         bill_dba  
        ,ship_dba 
        ,dsm 
)
,agg AS (
    SELECT 
         COALESCE(act.bill_dba,TRIM(bgt.bill_dba))  bill_dba  
        ,COALESCE(act.ship_dba,TRIM(bgt.ship_dba))  ship_dba
        ,COALESCE(act.dsm     ,TRIM(bgt.dsm     ))  dsm       
        ,COALESCE(SUM(bgt.pounds       ),0)                     "Budget 2024"
        ,COALESCE(SUM(act."Actual 2023"),0)                     "Actual 2023" 
        ,COALESCE(SUM(act."Actual 2024"),0)                     "Actual 2024"
        ,COALESCE(SUM(act."Open Ord"   ),0)                     "Open Ord"
        ,COALESCE(SUM(act."Quotes"     ),0)                     "Quotes"
    FROM
        act
        FULL OUTER JOIN bgt ON
                TRIM(bgt.bill_dba)  =     act.bill_dba
            AND TRIM(bgt.ship_dba)  = act.ship_dba
            AND TRIM(bgt.dsm     )  = act.dsm 
    GROUP BY
         COALESCE(act.bill_dba,TRIM(bgt.bill_dba))
        ,COALESCE(act.ship_dba,TRIM(bgt.ship_dba))
        ,COALESCE(act.dsm     ,TRIM(bgt.dsm     ))
)
SELECT * FROM agg;

------------------------------------------sales walk-------------------------------------------------------
CREATE OR REPLACE VIEW rlarp.sales_walk_r1 AS
SELECT 
	bill_dba  
	,ship_dba 
	,dsm 
	--,glec
	--,biggroup 
	--,priceg
    ,"Budget 2024" budget_2024
	,"Actual 2023" shipments_2023
	,"Actual 2024" shipments_2024
 	,"Open Ord" open_orders_2024
	,"Quotes" quotes
	,CASE WHEN "Actual 2023" > 0
		THEN CASE 
				WHEN "Actual 2024" = 0 AND "Open Ord" = 0 AND "Quotes" = 0 THEN 'Not Quoted'
				WHEN "Actual 2024" = 0 AND "Open Ord" = 0 AND "Quotes" > 0 THEN 'Quoted Only'
				WHEN ("Actual 2024" + "Open Ord") < "Actual 2023" THEN 'Reduced'
				WHEN ("Actual 2024" + "Open Ord") >= "Actual 2023" THEN 'Increased'
			 END
	    ELSE
	    	CASE
	    		WHEN "Actual 2024" = 0 AND "Open Ord" = 0 AND "Quotes" > 0 THEN 'Incremental Quoted'
	    		WHEN "Actual 2024" > 0 OR "Open Ord" > 0 THEN 'Incremental Won'
				WHEN "Actual 2024" = 0 AND "Open Ord" = 0 AND "Quotes" = 0 THEN 'Not Quoted'
	    	END
	END flag
	,COALESCE(w.bucket,a.bucket,'None') bucket
    ,COALESCE(w.attainment,0) attainment
	,COALESCE(w.notes,a.notes,'-') notes
	,COALESCE(w.fcnotes,'-') fcnotes
FROM
	rlarp.sales_walk_r1_agg agg
	LEFT OUTER JOIN rlarp.walk a ON
		a.ship_cust = agg.ship_dba
	LEFT OUTER JOIN rlarp.walk_r1 w ON
		w.ship_cust = agg.ship_dba
		AND w.bill_cust = agg.bill_dba
WHERE
    "Budget 2024" <> 0
    OR "Actual 2023" <> 0 
    OR ("Actual 2024") <> 0
    OR ("Open Ord") <> 0
    OR ("Quotes") <> 0
ORDER BY
    dsm
    ,"Budget 2024" + "Actual 2024" + "Actual 2023" + "Open Ord" + "Quotes" desc;

GRANT SELECT ON rlarp.sales_walk_r1 TO PUBLIC;
    --,biggroup

------------------------------------------sales walk seg agg-----------------------------------------------
DROP MATERIALIZED VIEW IF EXISTS rlarp.sales_walk_r1_seg_agg;

CREATE MATERIALIZED VIEW rlarp.sales_walk_r1_seg_agg AS
WITH
SEG AS (
	SELECT
		x.GLEC
		,x.SEGM
	FROM
		(
			VALUES
			('1CU','Sustainable'),
			('1SU','Sustainable'),
			('1GR','Greenhouse'),
			('1NU','Nursery'),
			('1RE','Retail'),
			('2WI','Greenhouse'),
			('3BM','Other'),
			('3CO','Other'),
			('3PE','Other'),
			('3PP','Other'),
			('4CO','Other'),
			('4RA','Other'),
			('9MI','Other'),
			('9SA','Other'),
			('9TO','Other')
		) X(GLEC, SEGM)
)
,agg as (
SELECT 
	bill_dba  
	,ship_dba 
	,dsm 
    ,director
    ,nursery_region
    ,s.segm
	,m.biggroup 
	--,os.priceg
	,COALESCE(sum(pounds) filter (WHERE oseas = 2024 AND version = 'Budget'),0) "Budget 2024"
	,COALESCE(sum(pounds) filter (WHERE sseas = 2023 AND version = 'Actual' AND SUBSTRING(smon,1,2) <= '12'),0) "Actual 2023"
	,COALESCE(sum(pounds) filter (WHERE sseas = 2024 AND version = 'Actual'),0) "Actual 2024"
 	,COALESCE(sum(pounds) filter (WHERE version = 'Actual' AND ostatus LIKE 'Open%' AND rseas <= 2024),0) "Open Ord"
	,COALESCE(sum(pounds) filter (WHERE version = 'Quotes'),0)						"Quotes"
FROM
	rlarp.osm_stack os
	LEFT OUTER JOIN rlarp.molds m ON
		m.stlc = substring(os.product ,1,8)
    LEFT OUTER JOIN seg s ON 
        s.glec = os.glec
WHERE 
	(
		(
			sseas IN (2023,2024)
			AND version = 'Actual'
			AND ostatus = 'Shipped'
		)
		OR (
			version = 'Quotes'
			AND odate >= '2023-05-01'::date
		)
		OR (ostatus LIKE 'Open%')
        OR (version = 'Budget')
	)
	AND calc_status <> 'CANCELED'
	AND substring(os.glec,1,1) <= '2'
    AND os.fs_line = '41010'
    AND COALESCE(ship_dba,'') <> ''
GROUP BY
	bill_dba  
	,ship_dba 
	,dsm
    ,director
    ,nursery_region
    ,s.segm
	,m.biggroup 
	--,os.priceg
)
SELECT * FROM agg;

------------------------------------------sales walk seg---------------------------------------------------
CREATE OR REPLACE VIEW rlarp.sales_walk_r1_seg AS
SELECT 
	 agg.bill_dba  
	,agg.ship_dba 
	,agg.dsm 
    ,agg.director
    ,agg.nursery_region
    ,agg.segm
	,agg.biggroup 
	--,priceg
	,"Budget 2024" budget_2024
	,"Actual 2023" shipments_2023
	,"Actual 2024" shipments_2024
 	,"Open Ord" open_orders_2024
	,"Quotes" quotes
	,"Budget 2024" * COALESCE(w.attainment,0) available_to_win
	,CASE WHEN COALESCE("Actual 2023",0) > 0
		THEN CASE 
				WHEN "Actual 2024" = 0 AND "Open Ord" = 0 AND "Quotes" = 0 THEN 'Not Quoted'
				WHEN "Actual 2024" = 0 AND "Open Ord" = 0 AND "Quotes" > 0 THEN 'Quoted Only'
				WHEN ("Actual 2024" + "Open Ord") < "Actual 2023" THEN 'Reduced'
				WHEN ("Actual 2024" + "Open Ord") >= "Actual 2023" THEN 'Increased'
			 END
	    ELSE
	    	CASE
	    		WHEN "Actual 2024" = 0 AND "Open Ord" = 0 AND "Quotes" > 0 THEN 'Incremental Quoted'
	    		WHEN "Actual 2024" > 0 OR "Open Ord" > 0 THEN 'Incremental Won'
				WHEN "Actual 2024" = 0 AND "Open Ord" = 0 AND "Quotes" = 0 THEN 'Not Quoted'
                ELSE 'Not Quoted'
	    	END
	END flag
	,COALESCE(w.bucket,a.bucket,'None') bucket
	,COALESCE(w.attainment,0) attainment
	,COALESCE(w.notes,a.notes,'-') notes
	,COALESCE(w.fcnotes,'-') fcnotes
    ,COALESCE(sw.flag,'Not Quoted') flag_cust
FROM
	rlarp.sales_walk_r1_seg_agg agg
	LEFT OUTER JOIN rlarp.walk a ON
		a.ship_cust = agg.ship_dba
	LEFT OUTER JOIN rlarp.walk_r1 w ON
		w.ship_cust = agg.ship_dba
		AND w.bill_cust = agg.bill_dba
    LEFT OUTER JOIN rlarp.sales_walk_r1 sw ON 
        sw.bill_dba = agg.bill_dba
        AND sw.ship_dba = agg.ship_dba
        AND sw.dsm = agg.dsm
WHERE 
    "Budget 2024" <> 0 
    OR "Actual 2023" <> 0 
    OR ("Actual 2024") <> 0
    OR ("Open Ord") <> 0
    OR ("Quotes") <> 0
ORDER BY
    dsm
    ,"Budget 2024" + "Actual 2024" + "Actual 2023" + "Open Ord" + "Quotes" desc;

GRANT SELECT ON rlarp.sales_walk_r1_seg TO PUBLIC;
    --,biggroup