收入成本报表 – 中文栏目(存储过程语句)

alter Proc Proc_Rs_RevenueCost
@cInvCode Nvarchar(200)=”,
@cInvStd Nvarchar(200)=”,
@cDepCode Nvarchar(200)=”,
@sDate Nvarchar(200)=”,
@eDate Nvarchar(200)=”,
@sRdDate Nvarchar(200)=”,
@eRdDate Nvarchar(200)=”,
@cCusCode Nvarchar(200)=”
as
Declare @sqlstr Nvarchar(4000)
Set @sqlstr=’
select
”” as 收入类别,–留空
dep01.cDepName as 地区,
So01.cdefine1 as 运营订单号,
CONVERT(VARCHAR(10),so01.dDate, 23) as 销售订单日期,
so01.cSOCode as 销售订单号,
”” as 合同号,– 留空
”” as 销售模式,– 留空
”” as 关联关系,– 留空
so01.cCusName as 客户名称,
”” as 终端客户名称,— 留空
inv01.cInvCode as 物料编码,
inv01.cInvName as 物料名称,
inv01.cInvStd as 规格,
com01.cComunitCode as 计价单位,
so02.iquantity as 计价数量,
so02.iTaxUnitPrice as 含税单价,
so02.iSum as 价税合计,
so02.iTaxRate as 税率,
rd02.dDate as 发货日期,
rd02.cCode as 出库单号,
rd02.cdefine7 as 物流费用,– 留空
rd02.cdefine12 as 货品物流单号,
DATEADD(DAY,3,Dis02.dDate) as 验收日期签收日期,
MONTH(Dis02.dDate) as 确认收入月份,
isnull(so02.iSum,0) as 确认收入金额,
isnull(rd01.iUnitCost,0) as 产品成本单价,
isnull(rd01.iPrice,0) as 总成本,
(isnull(so02.isum,0)-isnull(rd01.iPrice,0)) as 毛利,
case when so02.isum=0 then 0 else (isnull(so02.isum,0)-isnull(rd01.iPrice,0))*100/so02.isum end as 毛利率,
sb02.cSBVCode as 发票号码
from SO_SOMain So01
inner join SO_SODetails So02 on So01.ID=So02.ID
inner join Inventory inv01 on inv01.cInvcode=So02.cInvcode
left join ComputationUnit com01 on inv01.cComUnitCode=com01.cComUnitCode
left join Department dep01 on dep01.cDepCode=So01.cDepCode
left join DispatchLists Dis01 on Dis01.iSOsID=So02.iSOsID
left join DispatchList Dis02 on Dis01.DLID=Dis02.DLID
left join rdrecords32 rd01 on rd01.iDLsID=Dis01.iDLsID
left join rdrecord32 rd02 on rd01.id=rd02.id
left join SaleBillVouchs sb01 on sb01.iDLsID=Dis01.iDLsID
left join SaleBillVouch sb02 on sb01.SBVID=sb02.SBVID Where 1=1′
If @cInvCode<>”
Begin
Set @sqlstr=@sqlstr+’ And inv01.cInvcode=”’+@cInvCode+””
End
If @cInvStd<>”
Begin
Set @sqlstr=@sqlstr+’ And inv01.cInvStd like ”%’+@cInvStd+’%”’
End
If @cDepCode<>”
Begin
Set @sqlstr=@sqlstr+’ And So01.cDepCode=”’+@cDepCode+””
End
If @cCusCode<>”
Begin
Set @sqlstr=@sqlstr+’ And So01.cCusCode=”’+@cCusCode+””
End
If @sDate<>”
Begin
Set @sqlstr=@sqlstr+’ And So01.dDate>=”’+@sDate+””
End
If @eDate<>”
Begin
Set @sqlstr=@sqlstr+’ And So01.dDate<=”’+@eDate+””
End
If @sRdDate<>”
Begin
Set @sqlstr=@sqlstr+’ And rd02.dDate>=”’+@sRdDate+””
End
If @eRdDate<>”
Begin
Set @sqlstr=@sqlstr+’ And rd02.dDate<=”’+@eRdDate+””
End
Exec(@sqlstr)