[產值分析]生產部KPI考核之產值分析

接到新任務：設計統計電子和磁電公司生產部產值分析報表。

目前狀況：

1.電子公司：取最新單價*入庫數量

2.磁電公司：取最低價格*入庫數量（實際取價的時候又沒有取到最低價）

如果計算出來的結果和財務提供給生產部的報表有差異怎麼辦？給多 給少都不合適。

兩個原則：1.合理原則 2.如果上級不同意，按領導意思辦

按照當時的客戶訂單的不含稅單價是最合理的，是多少就是多少

1.電子公司去客戶訂單的不含稅單價

-- =============================================   -- Author: <David Gong>   -- Create date: <2014-06-17>   -- Description: <生產部KPI考核產值分析匯總>   -- ============================================= ALTER Procedure [dbo].[UP_Production](@yearmonth as nvarchar(6))asbeginselect isnull(b.類別,'合計') as 類別 ,入庫數量, 產值 from(select a.類別,cast(SUM(入庫數量) as decimal(10,2)) as 入庫數量,cast(SUM(入庫數量*售價) as decimal(10,2)) as 產值 from (Select TG004 品號,TG005 品名,TG006 規格 ,TG011 入庫數量,TC008 as 幣種,TC009 AS 匯率,MA101 稅率,case when LEFT(TG004,3)<>'203'  THEN ISNULL(TD011/(1+MA101)*TC009,0)else (MB057+MB058+MB059+MB060)*1.2 END AS 售價,case when LEFT(TG004,3)='102' then 'Relay' when LEFT(TG004,3)='103' then 'PCB' when LEFT(TG004,3) in ('104','106','203') then 'BL' when LEFT(TG004,3)='101' and CHARINDEX('ATM',TG006)>0 then 'TFT' when LEFT(TG004,3)='101' and CHARINDEX('AQM',TG006)>0 then 'COG' ELSE 'COB' END 類別        from MOCTG INNER JOIN MOCTF ON TF001=TG001 AND TF002=TG002              left join MOCTA ON TG014=TA001 AND TG015=TA002             left join COPTD ON TA026=TD001 AND TA027=TD002 AND TA028=TD003             left JOIN COPTC ON TC001=TD001 AND TC002=TD002             left join COPMA on TC004=MA001             left join INVMB ON TG004=MB001        where left(TF003,6)=@yearmonth ) a        group by 類別,        類別 WITH ROLLUP) b/*;WITH cte as(SELECT ROW_NUMBER() OVER (PARTITION BY MB002 ORDER BY MB017 DESC) cnt,MB002 As 品號,MB004 幣種,MB008 單價 FROM COPMB)select c.品號, c.品名, c.規格 ,c.入庫數量,a.單價,b.幣種,b.匯率,case when LEFT(c.品號,3)<>'203' and b.幣種<>'RMB' THEN a.單價*b.匯率     when LEFT(c.品號,3)<>'203' and b.幣種='RMB'  THEN a.單價/1.17else (MB057+MB058+MB059+MB060)*1.2 END AS 售價,case when LEFT(c.品號,3)='102' then 'Relay' when LEFT(c.品號,3)='103' then 'PCB' when LEFT(c.品號,3) in ('104','106','203') then 'BL' when LEFT(c.品號,3)='101' and CHARINDEX('ATM',c.規格)>0 then 'TFT' when LEFT(c.品號,3)='101' and CHARINDEX('AQM',c.規格)>0 then 'COG' ELSE 'COB' END 分類 into #Productionfrom (Select TG004 品號,TG005 品名,TG006 規格 ,SUM(TG011) 入庫數量        from MOCTG INNER JOIN MOCTF ON TF001=TG001 AND TF002=TG002         where left(TF003,6)=@yearmonth        GROUP BY TG004,TG005,TG006 ) c           left join (SELECT * FROM cte  WHERE cnt=1) a on a.品號=c.品號           left join (SELECT MG001 as 幣種, MG004 as 匯率 FROM CMSMG  WHERE left(MG002,6)=@yearmonth                      UNION ALL                       Select 'RMB',1.0 ) b ON a.幣種=b.幣種                      left join INVMB on MB001=c.品號              select isnull(e.分類,'合計') as 類別,e.入庫數量,e.產值 from (          select 分類,cast(SUM(入庫數量) as decimal(10,2)) as 入庫數量, cast(SUM(入庫數量*售價) as decimal(10,2)) as 產值 from   #Production   group by 分類,   分類 WITH ROLLUP) edrop table #Production*/end

-- =============================================      -- Author: <David Gong>      -- Create date: <2014-06-16>      -- Description: <取最小价格>      -- ============================================= ALTER function [dbo].[GetMinPrice](@ITEM AS VARCHAR(20),@YEARMONTH AS VARCHAR(6))returns decimal(15,2)as begindeclare  @Price as decimal(10,4)select @Price=min(c.售價) from (select MB002 As 品號,MB004 幣種,MB008 單價 ,b.匯率,case when MB004<>'RMB' THEN MB008*b.匯率    else   MB008/1.17 end as 售價FROM COPMBleft join (SELECT MG001 as 幣種, MG004 as 匯率 FROM CMSMG  WHERE left(MG002,6)=@YEARMONTH                      UNION ALL                       Select 'RMB',1.0 ) b ON MB004=b.幣種                        where    MB002=@ITEM )creturn @Priceend

-- =============================================   -- Author: <David Gong>   -- Create date: <2014-06-17>   -- Description: <生產部KPI考核產值分析匯總>   -- ============================================= ALTER Procedure [dbo].[UP_Production](@yearmonth as nvarchar(6))asbeginselect isnull(b.類別,'合計') as 類別 ,入庫數量, 產值 from(select a.類別,cast(SUM(入庫數量) as decimal(10,2)) as 入庫數量,cast(SUM(入庫數量*售價) as decimal(10,2)) as 產值 from (Select TG004 品號,TG005 品名,TG006 規格 ,TG011 入庫數量,TC008 as 幣種,TC009 AS 匯率,MA101 稅率,ZM.[dbo].[GetMinPrice](TG004,@yearmonth) 售價, case when CHARINDEX('，生產100%檢驗',MOCTG.UDF01)>0 THEN '外協' else REPLACE(MOCTG.UDF01,'，生產100%檢驗','') end as 類別        from MOCTG INNER JOIN MOCTF ON TF001=TG001 AND TF002=TG002              left join MOCTA ON TG014=TA001 AND TG015=TA002             left join COPTD ON TA026=TD001 AND TA027=TD002 AND TA028=TD003             left JOIN COPTC ON TC001=TD001 AND TC002=TD002             left join COPMA on TC004=MA001        where left(TF003,6)=@yearmonth ) a        group by 類別,        類別 WITH ROLLUP) b end

20W*0.17=3.4W

少了3.4W的獎金，估計他們也會對我不滿了。。。