if exists(select *from sysobjects where name='jf_SafetyStock')
drop procedure jf_SafetyStock
go
create procedure jf_SafetyStock
as
begin
if exists(
SELECT MB001,MB002,MB003,CAST((ISNULL(PURTB.TB009,0)+ISNULL(PURTD.TD008,0)+ISNULL(PURTD2.TD008,0)-ISNULL(MOCTA.TA015,0)-ISNULL(MOCTB.TB004,0)-ISNULL(COPTD.TD008,0)+ISNULL(INVMC.MC007,0)) AS INT) AS QTY1,CAST((INVMC2.MC004)AS INT) AS QTY2,((ISNULL(PURTB.TB009,0)+ISNULL(PURTD.TD008,0)+ISNULL(PURTD2.TD008,0)-ISNULL(MOCTA.TA015,0)-ISNULL(MOCTB.TB004,0)-ISNULL(COPTD.TD008,0)+ISNULL(INVMC.MC007,0))/INVMC2.MC004) AS RATE FROM INVMB
LEFT JOIN (SELECT TB004,SUM(TB009) AS TB009 FROM PURTB WHERE TB025='Y' AND TB039='N' GROUP BY TB004) AS PURTB ON INVMB.MB001=PURTB.TB004
LEFT JOIN (SELECT TD004,SUM(TD008) AS TD008 FROM PURTD WHERE TD018='N' GROUP BY TD004) AS PURTD ON INVMB.MB001=PURTD.TD004
drop procedure jf_SafetyStock
go
create procedure jf_SafetyStock
as
begin
if exists(
SELECT MB001,MB002,MB003,CAST((ISNULL(PURTB.TB009,0)+ISNULL(PURTD.TD008,0)+ISNULL(PURTD2.TD008,0)-ISNULL(MOCTA.TA015,0)-ISNULL(MOCTB.TB004,0)-ISNULL(COPTD.TD008,0)+ISNULL(INVMC.MC007,0)) AS INT) AS QTY1,CAST((INVMC2.MC004)AS INT) AS QTY2,((ISNULL(PURTB.TB009,0)+ISNULL(PURTD.TD008,0)+ISNULL(PURTD2.TD008,0)-ISNULL(MOCTA.TA015,0)-ISNULL(MOCTB.TB004,0)-ISNULL(COPTD.TD008,0)+ISNULL(INVMC.MC007,0))/INVMC2.MC004) AS RATE FROM INVMB
LEFT JOIN (SELECT TB004,SUM(TB009) AS TB009 FROM PURTB WHERE TB025='Y' AND TB039='N' GROUP BY TB004) AS PURTB ON INVMB.MB001=PURTB.TB004
LEFT JOIN (SELECT TD004,SUM(TD008) AS TD008 FROM PURTD WHERE TD018='N' GROUP BY TD004) AS PURTD ON INVMB.MB001=PURTD.TD004