销售合同执行一览表-存储过程版

转自：救火论坛

CREATE PROCEDURE [MyContractSale]
@Bdate as datetime,
@Edate as datetime,
@CustName as nvarchar(100)
AS
功能:销售合同执行汇总表,一览方式查询
制作:救火
日期:2009-07-23
修改:
版本:0.0

set nocount off
select * ,v.合同金额-isnull(d.收款金额,0) as  未收款
from
(
select
t.FContractID,t.FContractNo as  合同号,t.FContractName as 合同名称,t.Fdate as 合同日期,c.Fname as 客户,
d.Fname as 部门,t.FTotalAmount as 合同金额
FROM  t_RPContract  t
LEFT JOIN t_rpContractEntry u ON t.FContractID=u.FContractID 
LEFT  JOIN t_ITem c ON t.FCustomer=c.FItemID AND c.FItemID<>0
LEFT  JOIN t_Department d ON t.FDepartment=d.FItemID AND d.FItemID<>0
where t.FClassTypeID=1000019 and(t.Fdate between @Bdate and @Edate ) and c.Fname like @CustName
) v

left join (
Select u1.FContractInterid,min(v1.fdate) as 订单日期,sum(u1.FQty) as 订单数量,sum(u1.Famount) as 订单金额,sum(u1.FAllAmount) as 订单总金额
from SEOrder v1
INNER JOIN SEOrderEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0
where 1=1 AND  (v1.FChangeMark=0 AND ( Isnull(v1.FClassTypeID,0)<>1007100) AND (v1.FCancellation = 0))
group by u1.FContractInterid
) a on v.FContractID=a.FContractInterid

left join (

SELECT  u1.FSourceInterID,min(t.fdate)as 发票日期,sum(u1.FQty) as 发票数量 ,sum(u1.FAmount) as 发票金额
FROM  icsale t
left outer join ICSaleEntry u1 ON t.FInterID=u1.FInterID and u1.FClassID_SRC='1000019'
group by u1.FSourceInterID

) b on b.FSourceInterID=v.FContractID

left join(

select u1.FContractInterID,min(v1.fdate) as 出库日期,sum(u1.FQty) as 出库数量,Sum(u1.FAmount) as 出库金额, Sum(u1.FAmount)/sum(u1.FQty) as 出库单价
from ICStockBill v1 INNER JOIN ICStockBillEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0
where 1=1 AND  (v1.FTranType=21 AND (v1.FROB=1 AND  v1.FCancellation = 0))
group by u1.FContractInterID
) c on c.FContractInterID=v.FContractID

left join(

SELECT
u.FID_SRC,min(t.fdate) as  收款日期,sum(u.FReceiveAmount) as 收款金额

 

FROM  t_RP_NewReceiveBill t LEFT JOIN t_rp_ARBillOfSH u ON t.FBillID=u.FBillID
where t.FClassTypeID='1000005'
group by u.FID_SRC

) d on d.FID_SRC=v.FContractID

set nocount on
GO