[易飞]销售成本分析
来源：互联网 发布：手机自动网络时间不准 编辑：程序博客网 时间：2024/05/17 04:37
抓已开票的销货单和退货单 关联INVLA抓取相关的数据
/**用途:统计销售实际 已开票抓INVLA档抓成本*作者:龚德辉*日期:2010-05--23*/CREATE Procedure UP_SALRealyCost( @year as char(4)='2010')asbegin declare @plan Table( type nvarchar(20) ,area nvarchar(20) ,sort int ,acct_month1 decimal(18,9) ,acct_month2 decimal(18,9) ,acct_month3 decimal(18,9) ,acct_month4 decimal(18,9) ,acct_month5 decimal(18,9) ,acct_month6 decimal(18,9) ,acct_month7 decimal(18,9) ,acct_month8 decimal(18,9) ,acct_month9 decimal(18,9) ,acct_month10 decimal(18,9) ,acct_month11 decimal(18,9) ,acct_month12 decimal(18,9)) declare @account as decimal(18,9),@date as nvarchar(8),@type as nvarchar(20),@area as nvarchar(20),@sort as int ,@newyear as nvarchar(6),@curryear as nvarchar(4) set @newyear=@year+substring(convert(varchar(10),getdate(),120),6,2)--当前年月 set @curryear=datepart(year,getdate()) if @year>=@curryearbegin declare Cursorpawf cursor local static for select case when left(m.MB005,1) in ('2','3') THEN '其他' else MA003 END MA003,MR003,Acount,[date], case when m.MB005 in('2','3') then 999 else m.MB005 END as sort from ( select a.CODE,a.Acount,a.[date],b.MR003 from ( select MA076 Dept,TH004 CODE,case when TA079='1' then LA013 ELSE -(LA013) END AS Acount,TA038 [date] from ACRTA left join ACRTB ON TA001=TB001 AND TA002=TB002 left join COPTH ON TB005=TH001 AND TB006=TH002 AND TB007=TH003 LEFT JOIN COPTG ON TG001=TH001 AND TG002=TH002 LEFT JOIN COPMA ON TG004=MA001 inner JOIN INVLA on LA006=TH001 AND LA007=TH002 AND LA008=TH003 where left(TA038,6)<=@newyear AND left(TA038,4)=@year and TB019>0 union all select MA076 Dept,TJ004 CODE,case when TA079='1' then LA013 ELSE -(LA013) END AS Acount,TA038 [date] from ACRTA left join ACRTB ON TA001=TB001 AND TA002=TB002 left join COPTJ ON TB005=TJ001 AND TB006=TJ002 AND TB007=TJ003 LEFT JOIN COPTI ON TI001=TJ001 AND TI002=TJ002 LEFT JOIN COPMA ON TI004=MA001 inner JOIN INVLA on LA006=TJ001 AND LA007=TJ002 AND LA008=TJ003 where left(TA038,6)<=@newyear AND left(TA038,4)=@year and TA079='1' and TB019<0 --退货 ) a left join (select * from CMSMR where MR001='2') b on a.Dept=b.MR002 ) c left join (select MA001,MB001,MB005,MA003 from INVMB LEFT JOIN INVMA ON MB005=MA002 WHERE MA001='1') as m on c.CODE=m.MB001 WHERE Acount<>0----添加发票不是销售/销退来源 龚德辉2010-10-11 增加union all select '其他' MA003, MR003,case when TA079='1' then TB019 ELSE -(TB019) END AS Acount,TA038 [date],'999' sort from ACRTA left join ACRTB ON TA001=TB001 AND TA002=TB002 LEFT JOIN COPMA ON TA004=MA001 left join (select * from CMSMR where MR001='2') b on MA076=b.MR002 where left(TA038,6)<=@newyear AND left(TA038,4)=@year and TB004 not in('1','2') Open Cursorpawf while 1=1 begin fetch Cursorpawf into @type ,@area ,@account ,@date ,@sort if @@fetch_status<>0 break if @type is not null and @area is not null and @account<>0 begin if substring(@date,5,2)='01' begin insert @plan(type,area,acct_month1,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='02' begin insert @plan(type,area,acct_month2,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='03' begin insert @plan(type,area,acct_month3,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='04' begin insert @plan(type,area,acct_month4,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='05' begin insert @plan(type,area,acct_month5,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='06' begin insert @plan(type,area,acct_month6,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='07' begin insert @plan(type,area,acct_month7,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='08' begin insert @plan(type,area,acct_month8,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='09' begin insert @plan(type,area,acct_month9,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='10' begin insert @plan(type,area,acct_month10,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='11' begin insert @plan(type,area,acct_month11,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='12' begin insert @plan(type,area,acct_month12,sort)values(@type,@area,@account,@sort) end end end close Cursorpawf deallocate Cursorpawf end else begin declare Cursorpawf cursor local static for select case when left(m.MB005,1) in ('2','3') THEN '其他' else MA003 END MA003,MR003,Acount,[date], case when m.MB005 in('2','3') then 999 else m.MB005 END as sort from ( select a.CODE,a.Acount,a.[date],b.MR003 from ( select MA076 Dept,TH004 CODE,case when TA079='1' then LA013 ELSE -(LA013) END AS Acount,TA038 [date] from ACRTA left join ACRTB ON TA001=TB001 AND TA002=TB002 left join COPTH ON TB005=TH001 AND TB006=TH002 AND TB007=TH003 LEFT JOIN COPTG ON TG001=TH001 AND TG002=TH002 LEFT JOIN COPMA ON TG004=MA001 inner JOIN INVLA on LA006=TH001 AND LA007=TH002 AND LA008=TH003 where left(TA038,4)=@year and TB019>0 union all select MA076 Dept,TJ004 CODE,case when TA079='1' then LA013 ELSE -(LA013) END AS Acount,TA038 [date] from ACRTA left join ACRTB ON TA001=TB001 AND TA002=TB002 left join COPTJ ON TB005=TJ001 AND TB006=TJ002 AND TB007=TJ003 LEFT JOIN COPTI ON TI001=TJ001 AND TI002=TJ002 LEFT JOIN COPMA ON TI004=MA001 inner JOIN INVLA on LA006=TJ001 AND LA007=TJ002 AND LA008=TJ003 where left(TA038,4)=@year and TA079='1' and TB019<0 --退货 ) a left join (select * from CMSMR where MR001='2') b on a.Dept=b.MR002 ) c left join (select MA001,MB001,MB005,MA003 from INVMB LEFT JOIN INVMA ON MB005=MA002 WHERE MA001='1') as m on c.CODE=m.MB001 WHERE Acount<>0----添加发票不是销售/销退来源 龚德辉2010-10-11 增加union all select '其他' MA003, MR003,case when TA079='1' then TB019 ELSE -(TB019) END AS Acount,TA038 [date],'999' sort from ACRTA left join ACRTB ON TA001=TB001 AND TA002=TB002 LEFT JOIN COPMA ON TA004=MA001 left join (select * from CMSMR where MR001='2') b on MA076=b.MR002 where left(TA038,4)=@year and TB004 not in('1','2')Open Cursorpawf while 1=1 begin fetch Cursorpawf into @type ,@area ,@account ,@date ,@sort if @@fetch_status<>0 break if @type is not null and @area is not null and @account<>0 begin if substring(@date,5,2)='01' begin insert @plan(type,area,acct_month1,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='02' begin insert @plan(type,area,acct_month2,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='03' begin insert @plan(type,area,acct_month3,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='04' begin insert @plan(type,area,acct_month4,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='05' begin insert @plan(type,area,acct_month5,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='06' begin insert @plan(type,area,acct_month6,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='07' begin insert @plan(type,area,acct_month7,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='08' begin insert @plan(type,area,acct_month8,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='09' begin insert @plan(type,area,acct_month9,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='10' begin insert @plan(type,area,acct_month10,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='11' begin insert @plan(type,area,acct_month11,sort)values(@type,@area,@account,@sort) end if substring(@date,5,2)='12' begin insert @plan(type,area,acct_month12,sort)values(@type,@area,@account,@sort) end end end close Cursorpawf deallocate Cursorpawf end select sort, rtrim(type) as type, area ,convert(decimal(19,2),sum(isnull(acct_month1,0))) as 一月 ,convert(decimal(19,2),sum(isnull(acct_month2,0))) as 二月 ,convert(decimal(19,2),sum(isnull(acct_month3,0))) as 三月 ,convert(decimal(19,2),sum(isnull(acct_month4,0))) as 四月 ,convert(decimal(19,2),sum(isnull(acct_month5,0))) as 五月 ,convert(decimal(19,2),sum(isnull(acct_month6,0))) as 六月 ,convert(decimal(19,2),sum(isnull(acct_month7,0))) as 七月 ,convert(decimal(19,2),sum(isnull(acct_month8,0))) as 八月 ,convert(decimal(19,2),sum(isnull(acct_month9,0))) as 九月 ,convert(decimal(19,2),sum(isnull(acct_month10,0))) as 十月 ,convert(decimal(19,2),sum(isnull(acct_month11,0))) as 十一月 ,convert(decimal(19,2),sum(isnull(acct_month12,0))) as 十二月 ,convert(decimal(19,2),sum(isnull(acct_month1,0))+sum(isnull(acct_month2,0))+sum(isnull(acct_month3,0))+sum(isnull(acct_month4,0))+sum(isnull(acct_month5,0))+sum(isnull(acct_month6,0))+sum(isnull(acct_month7,0))+sum(isnull(acct_month8,0))+sum(isnull(acct_month9,0))+sum(isnull(acct_month10,0))+sum(isnull(acct_month11,0))+sum(isnull(acct_month12,0))) 合计from @plangroup by sort,type,areaunion allselect sort, rtrim(type)+ '合计' as type, rtrim(type)+'合计' area ,convert(decimal(19,2),sum(isnull(acct_month1,0))) as 一月 ,convert(decimal(19,2),sum(isnull(acct_month2,0))) as 二月 ,convert(decimal(19,2),sum(isnull(acct_month3,0))) as 三月 ,convert(decimal(19,2),sum(isnull(acct_month4,0))) as 四月 ,convert(decimal(19,2),sum(isnull(acct_month5,0))) as 五月 ,convert(decimal(19,2),sum(isnull(acct_month6,0))) as 六月 ,convert(decimal(19,2),sum(isnull(acct_month7,0))) as 七月 ,convert(decimal(19,2),sum(isnull(acct_month8,0))) as 八月 ,convert(decimal(19,2),sum(isnull(acct_month9,0))) as 九月 ,convert(decimal(19,2),sum(isnull(acct_month10,0))) as 十月 ,convert(decimal(19,2),sum(isnull(acct_month11,0))) as 十一月 ,convert(decimal(19,2),sum(isnull(acct_month12,0))) as 十二月 ,convert(decimal(19,2),sum(isnull(acct_month1,0))+sum(isnull(acct_month2,0))+sum(isnull(acct_month3,0))+sum(isnull(acct_month4,0))+sum(isnull(acct_month5,0))+sum(isnull(acct_month6,0))+sum(isnull(acct_month7,0))+sum(isnull(acct_month8,0))+sum(isnull(acct_month9,0))+sum(isnull(acct_month10,0))+sum(isnull(acct_month11,0))+sum(isnull(acct_month12,0))) 合计from @plangroup by sort, type order by sort ,type asc------------------------------合计----------------------select --sort, rtrim(type)+ '合计' as type --rtrim(type)+'合计' area ,convert(decimal(19,2),sum(isnull(acct_month1,0))) as 一月 ,convert(decimal(19,2),sum(isnull(acct_month2,0))) as 二月 ,convert(decimal(19,2),sum(isnull(acct_month3,0))) as 三月 ,convert(decimal(19,2),sum(isnull(acct_month4,0))) as 四月 ,convert(decimal(19,2),sum(isnull(acct_month5,0))) as 五月 ,convert(decimal(19,2),sum(isnull(acct_month6,0))) as 六月 ,convert(decimal(19,2),sum(isnull(acct_month7,0))) as 七月 ,convert(decimal(19,2),sum(isnull(acct_month8,0))) as 八月 ,convert(decimal(19,2),sum(isnull(acct_month9,0))) as 九月 ,convert(decimal(19,2),sum(isnull(acct_month10,0))) as 十月 ,convert(decimal(19,2),sum(isnull(acct_month11,0))) as 十一月 ,convert(decimal(19,2),sum(isnull(acct_month12,0))) as 十二月 from @plangroup by type endGO
- [易飞]销售成本分析
- 易飞8无销售报价的BOM成本参考
- 期间成本法与销售成本法
- 配置销售开票时结转销售成本
- 配置销售开票时结转销售成本
- [易飞]采购成本差异分析
- 激活销售会计核算的成本
- 财力会计3---存货和销售成本
- 减销售成本，但不减收入
- 销售成本结转单 失去上游联系
- 生产成本和销售成本的区别
- SD销售订单输入成本中心
- [易飞]财务表之销售分析
- [易飞]同一产品原料成本明细表与成本分析表异常
- 订单成本分析
- 成本分析报表
- 销售数据分析模型
- 现代销售模式分析
- 从零实现3D图像引擎：(13)把宽高比、透视投影矩阵、屏幕变换矩阵说透
- Linux下C语言编程——关于进程
- Brief introduction into Materialized Views
- Android uses-permission大全
- wince软键盘的隐藏和显示
- [易飞]销售成本分析
- android 读取安装应用软件的应用名及包名，版本信息
- 静态库和动态库的区别(linux下)
- flex中的查询定位
- 静态列转行 算出 A部门和B部门的平均工资之差
- 鼠标放到图片上慢慢变大，移开后图片又慢慢变小
- android设置Toast样式
- Apache Common Configuration--超方便的配置文件读取解析
- 警惕你的能力缺陷
