USE [AIS20160101004121]
GO
/****** Object: StoredProcedure [dbo].[StandingBook] Script Date: 2018/7/26 20:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****************************************************************************************************************
# Name:[StandingBook]
# Function:台账报表
# Author: zhanglq
# Date:2018-05-20
# Parameters:
# Ex:
--v1.FDate 订单日期,v1.FBillNo 订单编号,t12.FName 职员,t12.fnumber 职员代码,t4.FName 客户名称,t4.fnumber 客户代码,t11.FName 部门,t16.Fname 物料名称,t16.fnumber 物料代码
--t16.Fmodel 规格型号,u1.Fauxprice 订单单价,
--u1.Fauxqty 订单数量,u1.FAllStdAmount 订单金额,
--v2.Fdate 出库单日期,v2.fheadselfb0163 签收日期,u2.Fauxqty 出库单数量,u2.FConsignAmount 出库单金额,
--v3.fdate 发票日期,v3.fBillno as 发票号码,
--v4.FDate 收款单日期,v4.FAmount 收款单金额
****************************************************************************************************************/
CREATE PROCEDURE [dbo].[StandingBook]
(
@StartFdate varchar(20),
@EndFdate varchar(20),
@CKStartFdate varchar(20),
@CKEndFdate varchar(20),
@DDStrNumber varchar(50),
@DDEndNumber varchar(50),
@WLStrCode varchar(80),
@WLEndCode varchar(80)
)As
set nocount on
create table #tempTable
(
FentryID int, --订单id
FDate datetime, --订单日期
FBillNo nvarchar(255), --订单编号
ZYFName varchar(50), --职员
ZYCode varchar(50), --职员代码
KHFName varchar(255), --客户
KHCode varchar (100), --客户代码
BMFName varchar(255), --部门名称
WLFname varchar(100), --物料
WLCode varchar(100), --物料代码
Fmodel varchar(100),
Fauxprice decimal(28,10),
Fauxqty decimal(28,10),
Famount decimal(28,10),
CKFdate varchar(max),
QSFdate varchar(max),
CKFauxqty decimal(28,10),
FConsignAmount decimal(28,10),
FPfdate varchar(max),
FPFBillno varchar(max),
SKFDate varchar(800),
SKFAmount decimal(28,10)
)
CREATE NONCLUSTERED INDEX [[IX_t_mid_JSInvoice_FentryID] ON #tempTable
(
FBillNo ASC,
FentryID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
--导入销售订单数据
insert into #tempTable(FentryID,FDate,FBillNo,ZYFName,ZYCode,KHFName,KHCode,BMFName,WLFname,WLCode,Fmodel,Fauxprice,Fauxqty,Famount)
Select distinct u1.FentryID,v1.FDate 订单日期,v1.FBillNo 订单编号,t12.FName 职员,t12.fnumber 职员代码,t4.FName 客户名称,t4.fnumber 客户代码,t11.FName 部门,t16.Fname 物料名称,t16.fnumber 物料代码,t16.Fmodel 规格型号,u1.Fauxprice 订单单价,u1.Fauxqty 订单数量,u1.FAllStdAmount 订单金额
from SEOrder v1 INNER JOIN SEOrderEntry u1 ON v1.FInterID = u1.FInterID AND u1.FInterID <>0
LEFT OUTER JOIN t_Organization t4 ON