86 lines
2.1 KiB
SQL
86 lines
2.1 KiB
SQL
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
|