DROP TABLE pricing.cost_v1ds
DROP TABLE pricing.cost_v0ds

-- Final tables (one-time create)
CREATE TABLE pricing.cost_v1ds (
  stlc      varchar(50)  NOT NULL,
  v1ds      varchar(50)  NOT NULL,
  curstdus  decimal(19,6) NULL,
  futstdus  decimal(19,6) NULL,
  CONSTRAINT PK_cost_v1ds PRIMARY KEY (stlc, v1ds)
);

CREATE INDEX IX_cost_v1ds_cur ON pricing.cost_v1ds(stlc, v1ds, curstdus);

CREATE TABLE pricing.cost_v0ds (
  stlc      varchar(50)  NOT NULL,
  v0ds      varchar(50)  NOT NULL,
  curstdus  decimal(19,6) NULL,
  futstdus  decimal(19,6) NULL,
  CONSTRAINT PK_cost_v0ds PRIMARY KEY (stlc, v0ds)
);

CREATE INDEX IX_cost_v0ds_cur ON pricing.cost_v0ds(stlc, v0ds, curstdus);

CREATE OR ALTER PROCEDURE pricing.refresh_cost_rollups
AS
BEGIN
	DELETE FROM pricing.cost_v1ds;

	INSERT INTO 
		pricing.cost_v1ds
	SELECT 
	    trim(stlc) stlc 
	    ,trim(v1ds) v1ds
	    ,avg(curstdus) curstdus
	    ,avg(futstdus) futstdus
	FROM 
	    CMSInterfaceIN.[CMS.CUSLG].ITEMM i
	    LEFT OUTER JOIN pricing.arcstx a ON 
	        a.part = i.item 
	        AND a.plnt = i.dplt
	    LEFT OUTER JOIN pricing.plpr p ON
	        p.plnt = i.dplt
	    LEFT OUTER JOIN pricing.ffcret x ON
	        x.fcur = p.curr
	        AND x.tcur = 'US'
	        AND x.perd = p.ic 
	        AND x.rtyp = 'ME'
	WHERE 
	    aplnt <> 'I'
	    AND stlc <> ''
	    AND substring(glec,1,1) <= '1'
	GROUP BY
	    trim(stlc) 
	    ,trim(v1ds);
	
	DELETE FROM pricing.cost_v0ds 
	
	INSERT INTO 
		pricing.cost_v0ds
	SELECT 
	    trim(stlc) stlc 
	    ,trim(colgrp)+trim(substring(branding,1,1)) v0ds
	    ,avg(curstdus) curstdus
	    ,avg(futstdus) futstdus
	FROM 
	    CMSInterfaceIN.[CMS.CUSLG].ITEMM i
	    LEFT OUTER JOIN pricing.arcstx a ON 
	        a.part = i.item 
	        AND a.plnt = i.dplt
	    LEFT OUTER JOIN pricing.plpr p ON
	        p.plnt = i.dplt
	    LEFT OUTER JOIN pricing.ffcret x ON
	        x.fcur = p.curr
	        AND x.tcur = 'US'
	        AND x.perd = p.ic 
	        AND x.rtyp = 'ME'
	WHERE 
	    aplnt <> 'I'
	    AND stlc <> ''
	    AND substring(glec,1,1) <= '1'
	GROUP BY
	    trim(stlc) 
	    ,trim(colgrp)+trim(substring(branding,1,1));
END