存储过程_采购入库购货发票、费用发票跟踪表

 

CREATE PROCEDURE [FuRuiBuy]
@Bdate as datetime,
@Edate as datetime,
@SuppName as nvarchar(100), --客户名称
@EmpName as nvarchar(100), --业务员
@ItemName as nvarchar(100), -- 商品名称
@ZhangTao as nvarchar(100), --帐套0
@DanjuLeixing as nvarchar(100), --单据类型0
@StockName as nvarchar(100)----仓库名称

AS

set nocount on

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#AllMySaleReport]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#AllMySaleReport]

 

/***公司名称开始***/

 

select

u2.FName as  发货仓库,
u4.FNumber as 商品代码,
u4.FName as 商品名称,
u4.FModel as 规格型号,
u5.FName as 单位,
u6.FName as 制单人,
u9.FName as 业务员,
u10.FName as 发货人,
u13.FName as 客户供应商,
'公司名称' as 帐套,
v1.FBillNo as 编号,
v1.FCheckDate as 审核日期,
v1.Fdate as 日期,
v1.FExplanation as 摘要,
u1.FAllHookQTY as 已钩稽数量,
u1.Famount as 成本,
u1.Fauxprice as 单位成本,
u1.Fauxqty as 实际数量,
u1.FAuxQtyInvoice as 开票数量,
u1.FBatchNo as 批号,
u1.Fnote as 备注,
u1.FQty as 基本单位实发数量,
u1.FQtyInvoice as 基本单位开票数量,
u16.FName as 审核人,
(case
when v1.FTranType='29' then '其他出库单'
when v1.FTranType='1' then '外购入库单'
when v1.FTranType='10' then '其他入库单'
when v1.FTranType='21' then '销售出库单'
else '未定义类型' end ) as 单据类型,
u18.数量,
u18.不含税金额,
u18.税额,
u18.应计成本费用,
u18.运费税金,
u18.最小发票日期,
u18.最大发票日期
  into  #AllMySaleReport

from AIS20090905162212.dbo.ICStockBill v1
inner join AIS20090905162212.dbo.ICStockBillEntry u1 on v1.FInterID=u1.FInterID and v1.FTranType in (29,10,1,21)
left outer join AIS20090905162212.dbo.t_stock u2 on u1.FDCstockID=u2.FItemID --发货仓库
left outer join AIS20090905162212.dbo.t_stockPlace u3 on u1.FDCSPID=u3.FSPID --仓位
Left outer join AIS20090905162212.dbo.t_icitem u4 on u1.Fitemid=u4.Fitemid --物料商品
left outer join AIS20090905162212.dbo.t_measureUnit u5 on u1.FUnitID=u5.FItemID --单位
left outer join AIS20090905162212.dbo.T_user u6 on v1.FBillerID=u6.FUserID --制单人
left outer join AIS20090905162212.dbo.t_SonCompany u7 on v1.FBrID=u7.FItemID --单位
left outer join AIS20090905162212.dbo.t_department u8 on v1.FDeptID=u8.FItemID --部门
left outer join AIS20090905162212.dbo.t_Emp u9 on v1.FEmpID=u9.FItemID --业务员
left outer join AIS20090905162212.dbo.t_Emp u10 on v1.FFManagerID=u10.FItemID --发货人
left outer join AIS20090905162212.dbo.t_Emp u11 on v1.FManagerID=u11.FItemID --主管
left outer join AIS20090905162212.dbo.t_Emp u12 on v1.FManagerID=u12.FItemID --保管
left outer join AIS20090905162212.dbo.t_item u13 on v1.FSupplyID=u13.FItemID --购货单位
left outer join AIS20090905162212.dbo.t_item u14 on v1.FHeadSelfB0147=u14.FItemID --公司名称子单位
Left outer join AIS20090905162212.dbo.t_SubMessage u15 on v1.FSaleStyle=u15.FInterID---销售方式v1.FSaleStyle
left outer join AIS20090905162212.dbo.T_user u16 on v1.Fcheckerid=u16.FUserID --审核人
Left outer join AIS20090905162212.dbo.t_SubMessage u17 on v1.FMarketingStyle=u17.FInterID---v1.FMarketingStyle销售类型
left outer join
(
select
sum(FQTY) as 数量,
sum(FAmount) as 不含税金额,
sum(FTaxAmount) as 税额,
sum(FAmountMust) as 应计成本费用,
sum(FDeductTax) as 运费税金,
min(fdate) as 最小发票日期,
max(fdate) as 最大发票日期,
FSourceEntryID,
FSourceTranType,
FSourceInterid,
FSourceBillNo,
FClassID_SRC

from
AIS20090905162212.dbo.ICPurchase t1
inner join AIS20090905162212.dbo.ICPurchaseEntry u11 on u11.FInterID=t1.FInterID

group by
FSourceEntryID,
FSourceTranType,
FSourceInterid,
FSourceBillNo,
FClassID_SRC
) u18 on u18.FSourceInterid=v1.FInterID and u18.FSourceEntryID=u1.FEntryID

where v1.Fdate between @Bdate and @Edate 
and u13.FName like @SuppName
and isnull(u9.FName,' ') like @EmpName
and u4.FName like @ItemName
and u2.FName like @StockName

/**公司名称结束***/

/**多账查询账套开始***/
insert  into  #AllMySaleReport
select

u2.FName as  发货仓库,
u4.FNumber as 商品代码,
u4.FName as 商品名称,
u4.FModel as 规格型号,
u5.FName as 单位,
u6.FName as 制单人,
u9.FName as 业务员,
u10.FName as 发货人,
u13.FName as 客户供应商,
'公司名称' as 帐套,
v1.FBillNo as 编号,
v1.FCheckDate as 审核日期,
v1.Fdate as 日期,
v1.FExplanation as 摘要,
u1.FAllHookQTY as 已钩稽数量,
u1.Famount as 成本,
u1.Fauxprice as 单位成本,
u1.Fauxqty as 实际数量,
u1.FAuxQtyInvoice as 开票数量,
u1.FBatchNo as 批号,
u1.Fnote as 备注,
u1.FQty as 基本单位实发数量,
u1.FQtyInvoice as 基本单位开票数量,
u16.FName as 审核人,
(case
when v1.FTranType='29' then '其他出库单'
when v1.FTranType='1' then '外购入库单'
when v1.FTranType='10' then '其他入库单'
when v1.FTranType='21' then '销售出库单'
else '未定义类型' end ) as 单据类型,
u18.数量,
u18.不含税金额,
u18.税额,
u18.应计成本费用,
u18.运费税金,
u18.最小发票日期,
u18.最大发票日期

from AIS20080115104106.dbo.ICStockBill v1
inner join AIS20080115104106.dbo.ICStockBillEntry u1 on v1.FInterID=u1.FInterID and v1.FTranType in (29,10,1,21)
left outer join AIS20080115104106.dbo.t_stock u2 on u1.FDCstockID=u2.FItemID --发货仓库
left outer join AIS20080115104106.dbo.t_stockPlace u3 on u1.FDCSPID=u3.FSPID --仓位
Left outer join AIS20080115104106.dbo.t_icitem u4 on u1.Fitemid=u4.Fitemid --物料商品
left outer join AIS20080115104106.dbo.t_measureUnit u5 on u1.FUnitID=u5.FItemID --单位
left outer join AIS20080115104106.dbo.T_user u6 on v1.FBillerID=u6.FUserID --制单人
left outer join AIS20080115104106.dbo.t_SonCompany u7 on v1.FBrID=u7.FItemID --单位
left outer join AIS20080115104106.dbo.t_department u8 on v1.FDeptID=u8.FItemID --部门
left outer join AIS20080115104106.dbo.t_Emp u9 on v1.FEmpID=u9.FItemID --业务员
left outer join AIS20080115104106.dbo.t_Emp u10 on v1.FFManagerID=u10.FItemID --发货人
left outer join AIS20080115104106.dbo.t_Emp u11 on v1.FManagerID=u11.FItemID --主管
left outer join AIS20080115104106.dbo.t_Emp u12 on v1.FManagerID=u12.FItemID --保管
left outer join AIS20080115104106.dbo.t_item u13 on v1.FSupplyID=u13.FItemID --购货单位
left outer join AIS20080115104106.dbo.t_item u14 on v1.FHeadSelfB0147=u14.FItemID --公司名称子单位
Left outer join AIS20080115104106.dbo.t_SubMessage u15 on v1.FSaleStyle=u15.FInterID---销售方式v1.FSaleStyle
left outer join AIS20080115104106.dbo.T_user u16 on v1.Fcheckerid=u16.FUserID --审核人
Left outer join AIS20080115104106.dbo.t_SubMessage u17 on v1.FMarketingStyle=u17.FInterID---v1.FMarketingStyle销售类型
left outer join
(
select
sum(FQTY) as 数量,
sum(FAmount) as 不含税金额,
sum(FTaxAmount) as 税额,
sum(FAmountMust) as 应计成本费用,
sum(FDeductTax) as 运费税金,
min(fdate) as 最小发票日期,
max(fdate) as 最大发票日期,
FSourceEntryID,
FSourceTranType,
FSourceInterid,
FSourceBillNo,
FClassID_SRC

from
AIS20080115104106.dbo.ICPurchase t1
inner join AIS20080115104106.dbo.ICPurchaseEntry u11 on u11.FInterID=t1.FInterID

group by
FSourceEntryID,
FSourceTranType,
FSourceInterid,
FSourceBillNo,
FClassID_SRC
) u18 on u18.FSourceInterid=v1.FInterID and u18.FSourceEntryID=u1.FEntryID

where v1.Fdate between @Bdate and @Edate 
and u13.FName like @SuppName
and isnull(u9.FName,' ') like @EmpName
and u4.FName like @ItemName
and u2.FName like @StockName

/**多账查询账套结束***/

select * from  #AllMySaleReport
where 帐套 like @ZhangTao
and 单据类型 like @DanjuLeixing
drop table [dbo].[#AllMySaleReport]
set nocount off
GO