销售订单利润表 已经公开 2007年10月29日 9:30
跟传统意义上的订单利润分析不同的，一般客户要求的是每张订单的成本都要不同，其实做出来就是分批次去计算成本，但是至少，在目前阶段，k3的成本模块还没办法做到尽善尽美，如果真的强行用分批认定去做，你会发现，很累很累。
调用方法：
查询分析工具，新增报表，输入：stkd_财务_销售订单利润表 '********','########'
程序如下：
CREATE proc stkd_财务_销售订单利润表
 @begdate datetime,
@enddate datetime
as
set nocount on
create table #aa(
finterid int,
fentryid int,
fbillno varchar(50),
fcust varchar(80),
fcustnumber varchar(80),
fdate datetime,
fnumber varchar(80),
fname varchar(80),
fqty decimal(18,2) default(0),
fstockqty decimal(18,2) default(0),
fcost decimal(18,2) default(0),
fsaqty decimal(18,2) default(0),
famount decimal(18,2) default(0),
flr decimal(18,2) default(0),
frate decimal(18,2) default(0)
)
insert into #aa(fdate,finterid,fentryid,fbillno,fnumber,fname,fqty,fcustnumber,fcust)
select se.fdate,se.finterid,see.fentryid,se.fbillno,ti.fnumber,ti.fname,see.fqty,tog.fnumber,tog.fname
from seorder se,seorderentry see,t_icitem ti,t_organization tog
where se.finterid=see.finterid and se.fdate>=@begdate and se.fdate<=@enddate
and ti.fitemid=see.fitemid and tog.fitemid=se.fcustid
update t1
set t1.fcost=t2.fcost,t1.famount=t2.famount,t1.fstockqty=t2.fqty,t1.fsaqty=t2.fqty1
from #aa t1,
(select ice.forderinterid,ice.forderentryid,sum(ice.fqty) as fqty,sum(ice.famount) as fcost,
sum(ics.fqty) as fqty1,sum(ics.fstdamount) as famount
from icstockbillentry ice left join icsaleentry ics
on ics.fsourceinterid=ice.finterid and ics.fsourceentryid=ice.fentryid
where ice.finterid in (select finterid from icstockbill where ftrantype=21 AND fdate>=@begdate and fdate<=@enddate)
group by ice.forderinterid,ice.forderentryid
) t2
where t1.finterid=t2.forderinterid and t1.fentryid=t2.forderentryid
insert into #aa(fdate,fbillno,fqty,fcost,famount,fstockqty,fsaqty)
select fdate,fbillno+'合计',sum(fqty),sum(fcost),sum(famount),sum(fstockqty),sum(fsaqty)
from #aa
group by fdate,fbillno
update #aa set flr=famount-fcost
update #aa set frate=flr/famount*100 where famount<>0
select fdate as 日期,fbillno as 单据号,fcustnumber as 客户编码,fcust as 客户,
fentryid as 行号,fnumber as 物料编码,fname as 物料名称,fqty as 数量,fstockqty as 出库数量,
fcost as 成本,fsaqty as 开票数量,famount as 金额,flr as 利润,frate as 毛利率
from #aa
order by fdate,fbillno,fentryid
drop table #aa
GO
图例：(点击放大） 
请登录后再发表评论以赚取更多积分
|
|
|