CREATE PROCEDURE [MyContractReport2] AS
/**合同跟踪表**/
set nocount on
Create Table #t_Phase(
FPhaseName varchar(355),
FTypeID int not null default(0))
Insert into #t_Phase values('采购订单',1)
Insert into #t_Phase values('外购入库',2)
Insert into #t_Phase values('购货发票',3)
Insert into #t_Phase values('采购付款',4)
Insert into #t_Phase values('运费发票',5)
Insert into #t_Phase values('运费付款',6)
Insert into #t_Phase values('销售出库',7)
Insert into #t_Phase values('销售收款',8)
Insert into #t_Phase values('销售发票',9)
Create Table #t_Contract(
FNumber varchar(355),
FName varchar(355),
FItemNumber varchar(355),
FItemName varchar(355),
FContractType varchar(355),
FContractName varchar(355),
FContractNo varchar(355),
FEntryID varchar(355),
FCurrencyName varchar(355),
FModel varchar(355),
FUnitName varchar(355),
FTotalQtyFor Decimal(28,10),
FTotalAmountFor Decimal(28,10),
FPhaseName varchar(355),
FBillNumber varchar(355),
FDate datetime,
FQtyDecimal int default(0),
FAmountDecimal int default(0),
FBillDate datetime,
FExpanlation varchar(355),
FNewCheckID int default(0),
FAuxName varchar(355),
FDeptNo varchar(355),
FDeptName varchar(355),
FEmpNo varchar(355),
FEmpName varchar(355),
FConRate Decimal(28,10) not null default(1),
FBillRate Decimal(28,10) not null default(1),
FQtyOrder Decimal(28,10) ,
FAmountOrder Decimal(28,10) ,
FQtyInOut Decimal(28,10),
FAmountInOut Decimal(28,10),
FQtyTicket Decimal(28,10),
FAmountTicket Decimal(28,10),
FQtyAccept Decimal(28,10),
FAmountAccept Decimal(28,10),
FNQtyOrder Decimal(28,10),
FNAmountOrder Decimal(28,10),
FNQtyInOut Decimal(28,10) ,
FNAmountInOut Decimal(28,10) ,
FNQtyTicket Decimal(28,10),
FNAmountTicket Decimal(28,10),
FNQtyAccept Decimal(28,10),
FNAmountAccept Decimal(28,10),
FCCurrID int not null default(0),
FDCurrID int not null default(0),
FInterID int not null default(0),
FIndexID int not null default(0),
FUnitID int not null default(0),
FItemID int not null default(0),
FClassTypeID int default(0),
FTranType int default(0),
FTypeID int not null default(0),
FAmountFee Decimal(28,10), ---运费发票金额
FTaxRateFee Decimal(28,10), ---运费发票税率
FAuxPriceFee Decimal(28,10), ---运费单价
FAuxQtyFee Decimal(28,10), ---运费数量
FTaxAmountFee Decimal(28,10), ---运费税金
FAmountFeePay Decimal(28,10), ---运费付款
FQtySale Decimal(28,10), ---销售数量
FConsignPriceSale Decimal(28,10),---销售单价
FConsignAmountSale Decimal(28,10),---销售金额
FQtyInvoice Decimal(28,10), ---发票数量
FTaxPriceInvoice Decimal(28,10),
FAllAmountInvoice Decimal(28,10) ---销售发票金额
)
Create Table #t_Contract1(
FNumber varchar(355),
FName varchar(355),
FItemNumber varchar(355),
FItemName varchar(355),
FContractType varchar(355),
FContractName varchar(355),
FContractNo varchar(355),
FEntryID varchar(355),
FCurrencyName varchar(355),
FModel varchar(355),
FUnitName varchar(355),
FTotalQtyFor Decimal(28,10),
FTotalAmountFor Decimal(28,10),
FPhaseName varchar(355),
FBillNumber varchar(355),
FDate datetime,
FQtyDecimal int default(0),
FAmountDecimal int default(0),
FBillDate datetime,
FExpanlation varchar(355),
FNewCheckID int default(0),
FAuxName varchar(355),
FDeptNo varchar(355),
FDeptName varchar(355),
FEmpNo varchar(355),
FEmpName varchar(355),
FConRate Decimal(28,10) not null default(1),
FBillRate Decimal(28,10) not null default(1),
FQtyOrder Decimal(28,10) ,
FAmountOrder Decimal(28,10) ,
FQtyInOut Decimal(28,10),
FAmountInOut Decimal(28,10),
FQtyTicket Decimal(28,10),
FAmountTicket Decimal(28,10),
FQtyAccept Decimal(28,10),
FAmountAccept Decimal(28,10),
FNQtyOrder Decimal(28,10),
FNAmountOrder Decimal(28,10),
FNQtyInOut Decimal(28,10) ,
FNAmountInOut Decimal(28,10) ,
FNQtyTicket Decimal(28,10),
FNAmountTicket Decimal(28,10),
FNQtyAccept Decimal(28,10),
FNAmountAccept Decimal(28,10),
FCCurrID int not null default(0),
FDCurrID int not null default(0),
FInterID int not null default(0),
FIndexID int not null default(0),
FUnitID int not null default(0),
FItemID int not null default(0),
FClassTypeID int default(0),
FTranType int default(0),
FTypeID int not null default(0),
FAmountFee Decimal(28,10), ---运费发票金额
FTaxRateFee Decimal(28,10), ---运费发票税率
FAuxPriceFee Decimal(28,10), ---运费单价
FAuxQtyFee Decimal(28,10), ---运费数量
FTaxAmountFee Decimal(28,10), ---运费税金
FAmountFeePay Decimal(28,10), ---运费付款
FQtySale Decimal(28,10), ---销售数量
FConsignPriceSale Decimal(28,10),---销售单价
FConsignAmountSale Decimal(28,10),---销售金额
FQtyInvoice Decimal(28,10), ---发票数量
FTaxPriceInvoice Decimal(28,10),
FAllAmountInvoice Decimal(28,10), ---销售发票金额
FSumSort int not null default(0),
RowNum int not null default(0)
)
INSERT INTO #t_Contract(FIndexID,FNumber,FName,FContractType,FContractName,
FItemNumber,FItemName,FModel,FUnitName,FEntryID,FTotalQtyFor,FQtyDecimal,FQtyOrder,FItemID,FNewCheckID,FAuxName, FContractNo,FDeptNo,FDeptName,FEmpNo,FEmpName,FCCurrID,FDCurrID,FConRate,FBillRate,
FCurrencyName,FTotalAmountFor,FPhaseName,FBillNumber,FDate,FBillDate,FAmountOrder,FTypeID,FExpanlation,FClassTypeID,FTranType,FInterID)
select distinct v2.FEntryID,x2.FNumber,x2.FName,x4.FName,x1.FContractName,
v3.FNumber,v3.FName,v3.FModel,m1.FName,c2.FIndex,round(c2.FQuantity_Base,v3.FQtyDecimal),v3.FQtyDecimal,round(v2.FQty,v3.FQtyDecimal),isnull(v3.FItemID,0) FItemID,0 FID,c4.FName, x1.FContractNo,
x5.FNumber,x5.FName,x6.FNumber,x6.FName,
IsNull(x1.FCurrencyID,1),
IsNull(v1.FCurrencyID,1),IsNull(x1.FExchangeRate,1),IsNull(v1.FExchangeRate,1),
x3.FName,c2.FAmountIncludeTaxFor,'采购订单',v1.FBillNo,X1.FDate,v1.FDate,v2.FTaxAmount+v2.FAmount,1,v1.FExplanation ,0,v1.FTranType,v1.FInterID
from t_RPContract x1
left join t_RpContractEntry C2 on X1.FContractID = C2.FContractID
Inner join POOrderEntry v2 on C2.FEntryID=V2.FContractEntryID
Inner join POOrder v1 on V2.FInterID=V1.FInterID
inner join t_item x2 on x1.FCustomer=x2.FItemID
inner join t_currency x3 on x1.FCurrencyID=x3.FCurrencyID
inner join t_RP_SystemEnum x4 on x1.FContractTypeID=x4.FItemID
left join t_item x5 on x1.FDepartment=x5.FItemID
left join t_item x6 on x1.FEmployee=x6.FItemID
inner join t_ICItem v3 on v2.FItemID=v3.FItemID and c2.FProductID=v3.FItemID
inner join t_measureunit x7 on c2.FUnitID=x7.FMeasureUnitID
left join t_AuxItem c4 on c2.FAuxPropID=c4.FItemID
inner join t_measureunit m1 on m1.FUnitGroupID=x7.FUnitGroupID and m1.FStandard=1
where v1.FCancellation =0
And x1.frp= 0 and x1.FCurrencyID=1 and x2.FItemClassID=8 and x1.FDate>='1900-01-01' and x1.FDate<='2010-05-20'
INSERT INTO #t_Contract(FIndexID,FNumber,FName,FContractType,FContractName,
FItemNumber,FItemName,FModel,FUnitName,FEntryID,FTotalQtyFor,FQtyDecimal,FQtyInOut,FItemID,FNewCheckID,FAuxName,
FContractNo,FDeptNo,FDeptName,FEmpNo,FEmpName,FCCurrID,FDCurrID,FConRate,FBillRate,FCurrencyName,FTotalAmountFor,FPhaseName,FBillNumber,FDate,FBillDate,FAmountInOut,FTypeID,FExpanlation,FClassTypeID,FTranType,FInterID)
select distinct t4.FEntryID,x2.FNumber,x2.FName,x4.FName as ContractType,x1.FContractName,
v3.FNumber,v3.FName as ProductName,v3.FModel,m1.FName as UnitName,c2.FIndex,round(c2.FQuantity_Base,v3.FQtyDecimal),v3.FQtyDecimal,round(t4.FQty,v3.FQtyDecimal), isnull(v3.FItemID,0) FItemID,0 FID,c4.FName, x1.FContractNo,
x5.FNumber,x5.FName as DeptName,x6.FNumber,x6.FName as EmpName, IsNull(x1.FCurrencyID,1), IsNull(t1.FCurrencyID,1),IsNull(x1.FExchangeRate,1),1,
x3.FName,c2.FAmountIncludeTaxFor,'外购入库',t1.FBillNo,x1.FDate,t1.FDate,
case when x1.FExchangeRate=0 then 0 else case when x3.FOperator='/' then t4.FAmount*x1.FExchangeRate else t4.FAmount/x1.FExchangeRate end end as FAmount,
2,t1.FExplanation ,0,t1.FTranType,t1.FInterID
FROM t_RPContract x1 left Join t_RpContractEntry c2 on X1.FContractID = C2.FContractID
Left join ICStockBillEntry t4 on C2.FEntryID=t4.FContractEntryID
join ICStockBill t1 on t1.FInterID=t4.FInterID
inner join t_item x2 on x1.FCustomer=x2.FItemID inner join t_currency x3 on x1.FCurrencyID=x3.FCurrencyID inner join t_RP_SystemEnum x4 on x1.FContractTypeID=x4.FItemID left join t_item x5 on x1.FDepartment=x5.FItemID
left join t_item x6 on x1.FEmployee=x6.FItemID
inner join t_ICItem v3 on t4.FItemID=v3.FItemID and c2.FProductID=v3.FItemID
inner join t_measureunit x7 on c2.FUnitID=x7.FMeasureUnitID
left join t_AuxItem c4 on c2.FAuxPropID=c4.FItemID
inner join t_measureunit m1 on m1.FUnitGroupID=x7.FUnitGroupID and m1.FStandard=1
WHERE
t1.FCancellation =0
and t1.FTranType<>41
And x1.frp= 0 and x1.FCurrencyID=1 and x2.FItemClassID=8 and x1.FDate>='1900-01-01' and x1.FDate<='2010-05-20'
INSERT INTO #t_Contract(FNumber,FName,FContractType,FContractName,
FItemNumber,FItemName,FModel,FUnitName,FEntryID,FTotalQtyFor,FQtyDecimal,FQtyTicket,FItemID,FNewCheckID,FAuxName, FContractNo,FDeptNo,
FDeptName,FEmpNo,FEmpName,FCCurrID,FDCurrID,FConRate,FBillRate,FCurrencyName,FTotalAmountFor,FPhaseName,FBillNumber,FDate,FBillDate,FAmountTicket,FTypeID,FExpanlation,FClassTypeID,FTranType,FInterID)
select distinct x2.FNumber,x2.FName,x4.FName,x1.FContractName,
v3.FNumber,v3.FName,v3.FModel,m1.FName,c2.FIndex,round(c2.FQuantity_Base,v3.FQtyDecimal),v3.FQtyDecimal,round((v2.FCheckQty+v2.FRemainQty)*x7.FCoefficient,v3.FQtyDecimal),isnull(v3.FItemID,0) FItemID,0 FID,c4.FName, x1.FContractNo,
x5.FNumber,x5.FName,x6.FNumber,x6.FName, IsNull(x1.FCurrencyID,1), IsNull(v1.FCurrencyID,1),IsNull(x1.FExchangeRate,1),IsNull(v1.FExchangeRate,1),
x3.FName,c2.FAmountIncludeTaxFor,'购货发票',v1.FNumber,x1.FDate,v1.FDate, case when isnull(x1.FCurrencyID,1)=IsNull(v1.FCurrencyID,1) then v2.FAmountFor else case When x3.FOperator='*' then (v2.FAmount/IsNull(x1.FExchangeRate,1)) else (v2.FAmount*IsNull(x1.FExchangeRate,1)) end End as FAmount ,3,V1.FExplanation ,v1.FClassTypeID,0,v1.FBillID
from t_RPContract x1
left join t_RpContractEntry C2 on x1.FContractID=C2.FContractID
left join t_Rp_ArpBillEntry v2 on c2.FEntryID=V2.FEntryID_SRC
inner join t_Rp_ArpBill v1 on V2.FBillID=V1.FBillID
join t_item x2 on x1.FCustomer=x2.FItemID
join t_currency x3 on x1.FCurrencyID=x3.FCurrencyID
join t_RP_SystemEnum x4 on x1.FContractTypeID=x4.FItemID
left join t_item x5 on x1.FDepartment=x5.FItemID
left join t_item x6 on x1.FEmployee=x6.FItemID
left join t_ICItem v3 on c2.FProductID=v3.FItemID
left join t_measureunit x7 on c2.FUnitID=x7.FMeasureUnitID
left join t_AuxItem c4 on c2.FAuxPropID=c4.FItemID
left join t_measureunit m1 on m1.FUnitGroupID=x7.FUnitGroupID and m1.FStandard=1
where v2.FContractNo<>''
And x1.frp= 0 and x1.FCurrencyID=1 and x2.FItemClassID=8 and x1.FDate>='1900-01-01' and x1.FDate<='2010-05-20'
INSERT INTO #t_Contract(FIndexID,FNumber,FName,FContractType,FContractName,
FItemNumber,FItemName,FModel,FUnitName,FEntryID,FTotalQtyFor,FQtyDecimal,FQtyTicket,FItemID,FNewCheckID,FAuxName, FContractNo,FDeptNo,FDeptName,FEmpNo,FEmpName,FCCurrID,FDCurrID,FConRate,FBillRate,FCurrencyName,FTotalAmountFor,FPhaseName,FBillNumber,FDate,FBillDate,FAmountTicket,FTypeID,FExpanlation,FClassTypeID,FTranType,FInterID)
select distinct v2.FEntryID,x2.FNumber,x2.FName,x4.FName,x1.FContractName,
v3.FNumber,v3.FName,v3.FModel,m1.FName,c2.FIndex,
round(c2.FQuantity_Base,v3.FQtyDecimal),v3.FQtyDecimal,
round(v2.FQty,v3.FQtyDecimal),
isnull(v3.FItemID,0) FItemID,0 FID,c4.FName,
x1.FContractNo,
x5.FNumber,x5.FName,x6.FNumber,x6.FName,
IsNull(x1.FCurrencyID,1),
IsNull(v1.FCurrencyID,1),IsNull(x1.FExchangeRate,1),IsNull(v1.FExchangeRate,1),
x3.FName,c2.FAmountIncludeTaxFor,'购货发票',v1.FBillNo,x1.FDate,v1.FDate, case when v1.FTrantype=75 or v1.FTrantype=604 then case when isnull(x1.FCurrencyID,1)=IsNull(v1.FCurrencyID,1) then v2.FAmount+v2.FTaxAmount else v2.FStdAmount+v2.FStdTaxAmount end else case when isnull(x1.FCurrencyID,1)=IsNull(v1.FCurrencyID,1) then v2.FAmount else case when x3.FOperator='*' then (v2.FStdAmount/isnull(x1.FExchangeRate,1)) else ((v2.FStdAmount*isnull(x1.FExchangeRate,1)) ) end end end,3,V1.FExplanation
,v1.FClassTypeID,v1.FTranType,v1.FInterID from t_RPContract x1
left join t_rpContractEntry C2 on x1.FContractID=C2.FContractID
Left join ICPurchaseEntry v2 on V2.FContractEntryID=C2.FEntryID
Inner join ICPurchase v1 on v2.FInterID=V1.FInterID
inner join t_item x2 on x1.FCustomer=x2.FItemID
inner join t_currency x3 on x1.FCurrencyID=x3.FCurrencyID
inner join t_RP_SystemEnum x4 on x1.FContractTypeID=x4.FItemID
left join t_item x5 on x1.FDepartment=x5.FItemID
left join t_item x6 on x1.FEmployee=x6.FItemID
left join t_ICItem v3 on v2.FItemID=v3.FItemID and c2.FProductID=v3.FItemID
left join t_measureunit x7 on c2.FUnitID=x7.FMeasureUnitID
left join t_AuxItem c4 on c2.FAuxPropID=c4.FItemID
left join t_measureunit m1 on m1.FUnitGroupID=x7.FUnitGroupID and m1.FStandard=1
Where v1.FCancellation = 0
And x1.frp= 0 and x1.FCurrencyID=1 and x2.FItemClassID=8 and x1.FDate>='1900-01-01' and x1.FDate<='2010-05-20'
union
select distinct x1.FContractId,x2.FNumber,x2.FName,x4.FName,x1.FContractName,
v3.FNumber,v3.FName,v3.FModel,m1.FName,c2.FIndex,round(c2.FQuantity_Base,v3.FQtyDecimal),v3.FQtyDecimal,round(t3.FQuantity*x7.FCoefficient,v3.FQtyDecimal),isnull(v3.FItemID,0) FItemID,0 FID,c4.FName, x1.FContractNo,
x5.FNumber,x5.FName,x6.FNumber,x6.FName,
IsNull(x1.FCurrencyID,1),
IsNull(t3.FCurrencyID,1),IsNull(x1.FExchangeRate,1),IsNull(t3.FExchangeRate,1),
x3.FName,c2.FAmountIncludeTaxFor,'购货发票',t3.FBillNumber,x1.FDate,t3.FDate, case When IsNull(x1.FCurrencyID,1)=IsNull(t3.FCurrencyID,1) then t3.FRAmountFor else case when x3.FOperator='*' then t3.FRAmount/isnull(x1.FExchangeRate,1) else ( t3.FRAmount*isnull(x1.FExchangeRate,1) ) End end as FRAmountFor,3,t3.FExplanation
,t3.FClassTypeID,0,t3.FInterID from t_rpContract x1 left join t_rpContractEntry C2 on x1.FContractID=C2.FContractID
inner join t_item x2 on x1.FCustomer=x2.FItemID inner join t_currency x3 on x1.FCurrencyID=x3.FCurrencyID inner join t_RP_SystemEnum x4 on x1.FContractTypeID=x4.FItemID left join t_item x5 on x1.FDepartment=x5.FItemID
left join t_item x6 on x1.FEmployee=x6.FItemID
Left Join ( SELECT min(B.FCurrencyID) FCurrencyID, min(b.FExchangeRate) FExchangeRate, min(d.FContractNo) FContractNo, min(d.FContractID) FContractID, max(d.FQuantity) FQuantity, d.FProductID,d.FEntryID_SRC,
min(b.FComID) FCustomer,min(b.FBillNo) FBillNumber, max(b.FDate) FDate,
case when b.FClassTypeID in (1000502,1000503,1000521) then sum(d.FOccAmountFor)
when b.FClassTypeID in (1000511) then sum(d.FOccAmountFor-d.FAmountFor) else 0 end as FRAmountFor,
case when b.FClassTypeID in (1000502,1000503,1000521) then sum(d.FOccAmount)
when b.FClassTypeID in (1000511) then sum(d.FOccAmount-d.FAmount) else 0 end as FRAmount,
max(b.FExplanation) FExplanation,b.FClassTypeID,b.FInterID
FROM t_rp_begdata b
INNER join t_rp_begdataEntry d on b.FInterID = d.FInterID
where b.FClassTypeID in (1000502,1000503,1000521,1000511) and b.FRP=0
group by d.FProductID,d.FEntryID_SRC,b.FInterID ,b.FClassTypeID ) t3 on c2.FEntryID=t3.FEntryID_SRC left join t_ICItem v3 on t3.FProductID=v3.FItemID and c2.FProductID=v3.FItemID
left join t_measureunit x7 on c2.FUnitID=x7.FMeasureUnitID
left join t_AuxItem c4 on c2.FAuxPropID=c4.FItemID
left join t_measureunit m1 on m1.FUnitGroupID=x7.FUnitGroupID and m1.FStandard=1
Where t3.FRAmountFor <> 0 And x1.frp= 0 and x1.FCurrencyID=1 and x2.FItemClassID=8 and x1.FDate>='1900-01-01' and x1.FDate<='2010-05-20'
INSERT INTO #t_Contract(FNumber,FName,FContractType,FContractName,
FItemNumber,FItemName,FModel,FUnitName,FEntryID,FTotalQtyFor,FQtyDecimal,FQtyAccept,FItemID,FNewCheckID,FAuxName, FContractNo,FDeptNo,FDeptName,FEmpNo,FEmpName,FCCurrID,FDCurrID,FConRate,FBillRate,FCurrencyName,FTotalAmountFor,FPhaseName,FBillNumber,FDate,FBillDate,FAmountAccept,FTypeID,FExpanlation,FClassTypeID,FTranType,FInterID)
select distinct x2.FNumber,x2.FName,x4.FName,x1.FContractName,
v4.FNumber,v4.FName,v4.FModel,m1.FName,c2.FIndex,
round(c2.FQuantity_Base,v4.FQtyDecimal),v4.FQtyDecimal,round(v1.FCheckQuantity*m0.FCoefficient,v4.FQtyDecimal),isnull(v4.FItemID,0) FItemID ,
v5.FID,v8.FName,x1.FContractNo,x5.FNumber,x5.FName,x6.FNumber,x6.FName,
IsNull(x1.FCurrencyID,1), IsNull(v5.FCurrencyID,1),IsNull(x1.FExchangeRate,1),IsNull(v5.FExchangeRate,1), x3.FName,
c2.FAmountIncludeTaxFor,'采购付款',
case when v1.FType_SRC=9 then v1.FNumber else case when v1.FIsInit_SRC=0 then v21.FNumber else v31.FBillNo end end
as FBillNo,x1.FDate,
v5.FCheckDate as FBillDate, case when IsNull(x1.FCurrencyID,1) = IsNull(v5.FCurrencyID,1) then v1.FCheckAmountFor else case when x3.FOperator='*' then v1.FCheckAmount/isnull(x1.FExchangeRate,1) else (v1.FCheckAmount*isnull(x1.FExchangeRate,1)) end end as FCheckAmountFor,4,'',
case when v1.FIsInit_SRC=0 then v21.FClassTypeID else v31.FClassTypeID end
as FClassTypeID,0,case v1.fisinit_src when 1 then isnull(v31.FInterID,0) else isnull(v21.FBillID,0) end from t_rpContract x1
Inner join t_RPContractEntry c2 on x1.FContractID=c2.FContractID
join t_item x2 on x1.FCustomer=x2.FItemID
join t_icitem v4 on c2.FProductID=v4.FItemID
join t_AuxItem v8 on c2.FAuxPropID=v8.FItemID
left join (select b.fnumber,FInterID,FType,FType_SRC,FBillID_SRC,FEntryID_SRC,FIsinit_SRC,FContractNo,FContractEntryID,sum(FCheckAmount) FCheckAmount,sum(FCheckQuantity) FCheckQuantity,sum(FCheckAmountFor) FCheckAmountFor from (select distinct c.* from t_rp_checkentry c,t_rp_newcheckinfo d where c.fid=d.fid and c.fcontractentryid>0 ) a join (select fid,fnumber from t_rp_contact where FType in(2,4,12,14)) b on a.fcontactid=b.fid where FType in(2,4,12,14) group by b.fnumber,FType,FType_SRC,FBillID_SRC,FEntryID_SRC,FIsinit_SRC,FContractNo,FContractEntryID,FInterID) V1 on c2.FEntryID=v1.FContractEntryID
left join t_rp_newcheckInfo v5 on v5.FInterID=V1.FInterID
left join t_rp_newreceivebill v21 on v21.FBillID=v1.FBillID_SRC
left join t_rp_begdata v31 on v31.FInterID=v1.FBillID_SRC
left join t_measureunit m0 on c2.FUnitID=m0.FMeasureUnitID
inner join t_measureunit m1 on m1.FUnitGroupID=m0.FUnitGroupID and m1.FStandard=1
join t_RP_SystemEnum x4 on x1.FContractTypeID=x4.FItemID
left join t_currency x3 on v5.FCurrencyID=x3.FCurrencyID
left join t_Item x5 on x1.FDepartment= x5.FItemID
left join t_Item x6 on x1.FEmployee= x6.FItemID
Where v1.FCheckAmountFor <> 0 And x1.frp= 0 and x1.FCurrencyID=1 and x2.FItemClassID=8 and x1.FDate>='1900-01-01' and x1.FDate<='2010-05-20'
INSERT INTO #t_Contract(FIndexID,FNumber,FName,FContractType,FContractName,
FItemNumber,FItemName,FModel,FUnitName,FEntryID,FTotalQtyFor,FQtyDecimal,FQtyAccept,FItemID,FNewCheckID,FAuxName, FContractNo,FDeptNo,FDeptName,FEmpNo,FEmpName,FCCurrID,FDCurrID,FConRate,FBillRate,FCurrencyName,FTotalAmountFor,FPhaseName,FBillNumber,FDate,FBillDate,FAmountAccept,FTypeID,FExpanlation,FClassTypeID,FTranType,FInterID)
select distinct v2.FEntryID,x2.FNumber,x2.FName,x4.FName,x1.FContractName,
v3.FNumber,v3.FName,v3.FModel,m1.FName,c2.FIndex,
round(c2.FQuantity_Base,v3.FQtyDecimal),v3.FQtyDecimal,
round(v2.FQty,v3.FQtyDecimal),
isnull(v3.FItemID,0) FItemID,0 FID,c4.FName,
x1.FContractNo,
x5.FNumber,x5.FName,x6.FNumber,x6.FName,
IsNull(x1.FCurrencyID,1),
IsNull(v1.FCurrencyID,1),IsNull(x1.FExchangeRate,1),IsNull(v1.FExchangeRate,1),
x3.FName,c2.FAmountIncludeTaxFor,'采购付款',v1.FBillNo,x1.FDate,v1.FDate, case when v1.FTrantype=75 or v1.FTrantype=604 then case when IsNull(x1.FCurrencyID,1)=IsNull(v1.FCurrencyID,1) then v2.FAmount+v2.FTaxAmount else case when x3.FOperator='*' then (v2.FStdAmount+v2.FStdTaxAmount)/x1.FExchangeRate else ((v2.FStdAmount+v2.FStdTaxAmount)*x1.FExchangeRate) end end else case when IsNull(x1.FCurrencyID,1)=IsNull(v1.FCurrencyID,1) then v2.FAmount else case when x3.FOperator='*' then v2.FStdAmount/isnull(x1.FExchangeRate,1) else (v2.FStdAmount*isnull(x1.FExchangeRate,1)) end end end,4,V1.FExplanation
,v1.FClassTypeID,v1.FTranType,v1.FInterID from t_RPContract x1
left join t_rpContractEntry C2 on x1.FContractID=C2.FContractID
Left join ICPurchaseEntry v2 on V2.FContractEntryID=C2.FEntryID
Inner join ICPurchase v1 on v2.FInterID=V1.FInterID
inner join t_item x2 on x1.FCustomer=x2.FItemID
inner join t_currency x3 on x1.FCurrencyID=x3.FCurrencyID
inner join t_RP_SystemEnum x4 on x1.FContractTypeID=x4.FItemID
left join t_item x5 on x1.FDepartment=x5.FItemID
left join t_item x6 on x1.FEmployee=x6.FItemID
inner join t_ICItem v3 on v2.FItemID=v3.FItemID and c2.FProductID=v3.FItemID
inner join t_measureunit x7 on c2.FUnitID=x7.FMeasureUnitID
left join t_AuxItem c4 on c2.FAuxPropID=c4.FItemID
inner join t_measureunit m1 on m1.FUnitGroupID=x7.FUnitGroupID and m1.FStandard=1
Where v1.FCancellation = 0
And isnull(v1.FPoStyle,0)=251
And x1.frp= 0 and x1.FCurrencyID=1 and x2.FItemClassID=8 and x1.FDate>='1900-01-01' and x1.FDate<='2010-05-20'
/***运费发票**/
/***运费发票**/
INSERT INTO #t_Contract(
FContractNo,
FContractName,
FDate,
FPhaseName,
FConRate,
FBillRate,
FBillNumber,
FBillDate,
FAmountFee, ---运费发票金额
FTaxRateFee, ---运费发票税率
FAuxPriceFee, ---运费单价
FAuxQtyFee, ---运费数量
FTaxAmountFee, ---运费税金
FIndexID,
FNumber,FName,
FContractType,FItemNumber,
FItemName,
FModel,
FUnitName,
FEntryID,
FTotalQtyFor,
FQtyDecimal,
FQtyInOut,
FItemID,
FNewCheckID,
FAuxName,
FDeptNo,
FDeptName,
FEmpNo,
FEmpName,
FCCurrID,
FDCurrID,
FCurrencyName,
FTotalAmountFor,
FAmountInOut,
FExpanlation,
FClassTypeID,
FTranType,
FInterID,
FTypeID
)
select distinct
x1.FContractNo,
x1.FContractName,
x1.FDate,
'运费发票',
IsNull(x1.FExchangeRate,1),
IsNull(x1.FExchangeRate,1),
v1.FBillNo, ---运费发票单号
v1.FDate, ---运费发票日期
u1.FAmount, ---运费发票金额
u1.FTaxRate, ---运费发票税率
u1.FAuxPrice, ---运费单价
u1.FAuxQty, ---运费数量
u1.FTaxAmount, ---运费税金
'0',
x2.FNumber,x2.FName,
x4.FName as ContractType,'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'5'
FROM
ICExpenses v1 ---费用表
left JOIN ICExpensesEntry u1 ON v1.FInterID = u1.FInterID ---费用明细
inner join t_RPContract x1 on v1.FHeadselfi0639=x1.FContractNo ---合同表
---left Join t_RpContractEntry c2 on X1.FContractID = C2.FContractID
inner join t_item x2 on x1.FCustomer=x2.FItemID ---供应商
inner join t_RP_SystemEnum x4 on x1.FContractTypeID=x4.FItemID ---合同类别
/***运费付款**/
INSERT INTO #t_Contract(
FContractNo,
FContractName,
FDate,
FPhaseName,
FConRate,
FBillRate,
FBillNumber,
FBillDate,
FAmountFeePay,
FIndexID,
FNumber,FName,
FContractType,FItemNumber,
FItemName,
FModel,
FUnitName,
FEntryID,
FTotalQtyFor,
FQtyDecimal,
FQtyInOut,
FItemID,
FNewCheckID,
FAuxName,
FDeptNo,
FDeptName,
FEmpNo,
FEmpName,
FCCurrID,
FDCurrID,
FCurrencyName,
FTotalAmountFor,
FAmountInOut,
FExpanlation,
FClassTypeID,
FTranType,
FInterID,
FTypeID
)
select distinct
x1.FContractNo,
x1.FContractName,
x1.FDate,
'运费付款',
IsNull(x1.FExchangeRate,1),
IsNull(x1.FExchangeRate,1),
v1.FNumber, ---运费发票单号
v1.FDate, ---运费发票日期
v1.FAmount,
---v1.FExplanation,
'0',
x2.FNumber,x2.FName,
x4.FName as ContractType,'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'6'
FROM
t_RP_NewReceiveBill v1 ---付款单
inner join t_rp_arbillofsh v2 on v2.FBillID=v1.FBillID and (v2.FClassID_SRC<>'1000022' and v2.FClassID_SRC<>'1000020') ---付款明细
left join t_RPContract x1 on x1.FContractNo=v1.FContractNo
---left Join t_RpContractEntry c2 on X1.FContractID = C2.FContractID
inner join t_item x2 on x1.FCustomer=x2.FItemID ---供应商
inner join t_RP_SystemEnum x4 on x1.FContractTypeID=x4.FItemID ---合同类别
WHERE x1.frp= 0 and x1.FCurrencyID=1 and x1.FDate>='1900-01-01' and x1.FDate<='2010-05-20'
/**运费付款**/
/**销售出库**/
INSERT INTO #t_Contract(
FContractNo,
FContractName,
FDate,
FPhaseName,
FConRate,
FBillRate,
FBillNumber,
FBillDate,
FQtySale, ---销售数量
FConsignPriceSale, ---销售单价
FConsignAmountSale, ---销售金额
FExpanlation,
FIndexID,
FNumber,
FName,
FContractType,
FItemNumber,
FItemName,
FModel,
FUnitName,
FEntryID,
FTotalQtyFor,
FQtyDecimal,
FQtyInOut,
FItemID,
FNewCheckID,
FCCurrID,
FDCurrID,
FAmountInOut,
FClassTypeID,
FTranType,
FInterID,
FCurrencyName,
FTotalAmountFor,
FDeptNo,
FDeptName,
FEmpNo,
FEmpName,
FTypeID
)
select distinct
x1.FContractNo,
x1.FContractName,
x1.FDate,
'销售出库',
IsNull(x1.FExchangeRate,1),
IsNull(x1.FExchangeRate,1),
t1.FBillNo,
t1.FDate,
t4.FQty,
t4.FConsignPrice,
t4.FConsignAmount,
'0',
'0',
x2.FNumber,
x2.FName,
x4.FName as ContractType,
v3.FNumber,v3.FName,
v3.FModel,
m1.FName,
'1',
round(c2.FQuantity_Base,v3.FQtyDecimal),
v3.FQtyDecimal,
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
x3.FName,
c2.FAmountIncludeTaxFor,
x5.FNumber,x5.FName,x6.FNumber,x6.FName,
'7'
from
ICStockBillEntry t4
inner join ICStockBill t1 on (t1.FInterID=t4.FInterID and t1.FTranType=21)
inner join ICStockBillEntry u1 on u1.FInterID=t4.FSourceInterID
inner join t_RPContract x1 on u1.FContractBillNo=x1.FContractNo
inner join t_item x2 on x1.FCustomer=x2.FItemID
inner join t_RP_SystemEnum x4 on x1.FContractTypeID=x4.FItemID
inner join t_currency x3 on x1.FCurrencyID=x3.FCurrencyID
left join t_ICItem v3 on u1.FItemID=v3.FItemID
left join t_measureunit x7 on u1.FUnitID=x7.FMeasureUnitID
left join t_measureunit m1 on m1.FUnitGroupID=x7.FUnitGroupID and m1.FStandard=1
left Join t_RpContractEntry c2 on X1.FContractID = C2.FContractID
left join t_item x5 on x1.FDepartment=x5.FItemID
left join t_item x6 on x1.FEmployee=x6.FItemID
/**销售出库**/
/**销售发票**/
INSERT INTO #t_Contract(
FContractNo,
FContractName,
FDate,
FPhaseName,
FConRate,
FBillRate,
FBillNumber,
FBillDate,
FQtyInvoice, ---发票号
FTaxPriceInvoice,
FAllAmountInvoice, ---发票金额
FExpanlation,
FIndexID,
FNumber,
FName,
FContractType,
FItemNumber,
FItemName,
FModel,
FUnitName,
FEntryID,
FTotalQtyFor,
FQtyDecimal,
FQtyInOut,
FItemID,
FNewCheckID,
FCCurrID,
FDCurrID,
FAmountInOut,
FClassTypeID,
FTranType,
FInterID,
FCurrencyName,
FTotalAmountFor,
FDeptNo,
FDeptName,
FEmpNo,
FEmpName,
FTypeID
)
select distinct
x1.FContractNo,
x1.FContractName,
x1.FDate,
'销售发票',
IsNull(x1.FExchangeRate,1),
IsNull(x1.FExchangeRate,1),
u3.FBillNo, ---发票号
u3.FDate,
u2.FQty,
u2.FTaxPrice,
u2.FAllAmount,
'0',
'0',
x2.FNumber,
x2.FName,
x4.FName as ContractType,
v3.FNumber,v3.FName,
v3.FModel,
m1.FName,
'1',
round(c2.FQuantity_Base,v3.FQtyDecimal),
v3.FQtyDecimal,
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
x3.FName,
c2.FAmountIncludeTaxFor,
x5.FNumber,x5.FName,x6.FNumber,x6.FName,
'8'
from
ICStockBillEntry t4
inner join ICStockBill t1 on (t1.FInterID=t4.FInterID and t1.FTranType=21)
inner join ICStockBillEntry u1 on u1.FInterID=t4.FSourceInterID
inner join t_RPContract x1 on u1.FContractBillNo=x1.FContractNo
inner join t_item x2 on x1.FCustomer=x2.FItemID
inner join t_RP_SystemEnum x4 on x1.FContractTypeID=x4.FItemID
inner join ICSaleEntry u2 on u2.FSourceInterId=t1.FInterId ---销售发票单据体
inner join icsale u3 on u2.FInterID=u3.FInterID ---销售发票单据头
left join t_ICItem v3 on u1.FItemID=v3.FItemID
inner join t_currency x3 on x1.FCurrencyID=x3.FCurrencyID
left join t_measureunit x7 on u1.FUnitID=x7.FMeasureUnitID
left join t_measureunit m1 on m1.FUnitGroupID=x7.FUnitGroupID and m1.FStandard=1
left Join t_RpContractEntry c2 on X1.FContractID = C2.FContractID
left join t_item x5 on x1.FDepartment=x5.FItemID
left join t_item x6 on x1.FEmployee=x6.FItemID
/**销售发票 **/
INSERT INTO #t_Contract(FIndexID,FNumber,FName,FContractType,FContractName,
FItemNumber,FItemName,FModel,FUnitName,FEntryID,FTotalQtyFor,FQtyDecimal,FQtyTicket,FItemID,FNewCheckID,FAuxName, FContractNo,FDeptNo,FDeptName,FEmpNo,FEmpName,FCCurrID,FDCurrID,FConRate,FBillRate,FCurrencyName,FTotalAmountFor,FPhaseName,FBillNumber,FDate,FBillDate,FAmountTicket,FTypeID,FExpanlation,FClassTypeID,FTranType,FInterID)
select isnull(c2.FEntryID,0),isnull(x2.FNumber,''),isnull(x2.FName,''),isnull(x4.FName,''),isnull(x1.FContractName,''),isnull(v3.FNumber,''),isnull(v3.FName,''),isnull(v3.FModel,''),isnull(m1.FName,''),isnull(c2.FIndex,0),round(c2.FQuantity_Base,v3.FQtyDecimal),isnull(v3.FQtyDecimal,0),round(c2.FInitInvoiceQty*x7.FCoefficient,v3.FQtyDecimal),isnull(v3.FItemID,0),0 FID,isnull(c4.FName,''), isnull(x1.FContractNo,''), isnull(x5.FNumber,''),isnull(x5.FName,''),isnull(x6.FNumber,''),isnull(x6.FName,''), IsNull(x1.FCurrencyID,1), IsNull(x1.FCurrencyID,1),IsNull(x1.FExchangeRate,1),IsNull(x1.FExchangeRate,1), x3.FName,c2.FAmountIncludeTaxFor, '购货发票',x1.FContractNo,x1.FDate,x1.FDate,c2.FInitInvoiceAmtFor,3,x1.FExplanation , X1.FClassTypeID , 0, X1.FContractID from t_rpContractEntry c2 left join t_RpContract x1 on x1.FContractID=c2.FContractID left join t_item x2 on x1.FCustomer=x2.FItemID left join t_currency x3 on x1.FCurrencyID=x3.FCurrencyID left join t_RP_SystemEnum x4 on x1.FContractTypeID=x4.FItemID left join t_item x5 on x1.FDepartment=x5.FItemID left join t_item x6 on x1.FEmployee=x6.FItemID left join t_ICItem v3 on c2.FProductID=v3.FItemID
left join t_measureunit x7 on c2.FUnitID=x7.FMeasureUnitID
left join t_AuxItem c4 on c2.FAuxPropID=c4.FItemID
left join t_measureunit m1 on m1.FUnitGroupID=x7.FUnitGroupID and m1.FStandard=1
Where x1.FIsInit=1 And (c2.FInitInvoiceAmtFor>0 or c2.FInitInvoiceQty>0) And x1.frp= 0 and x1.FCurrencyID=1 and x2.FItemClassID=8 and x1.FDate>='1900-01-01' and x1.FDate<='2010-05-20'
INSERT INTO #t_Contract(FIndexID,FNumber,FName,FContractType,FContractName,
FItemNumber,FItemName,FModel,FUnitName,FEntryID,FTotalQtyFor,FQtyDecimal,FQtyAccept,FItemID,FNewCheckID,FAuxName, FContractNo,FDeptNo,FDeptName,FEmpNo,FEmpName,FCCurrID,FDCurrID,FConRate,FBillRate,FCurrencyName,FTotalAmountFor,FPhaseName,FBillNumber,FDate,FBillDate,FAmountAccept,FTypeID,FExpanlation,FClassTypeID,FTranType,FInterID)
select isnull(c2.FEntryID,0),isnull(x2.FNumber,''),isnull(x2.FName,''),isnull(x4.FName,''),isnull(x1.FContractName,''),isnull(v3.FNumber,''),isnull(v3.FName,''),isnull(v3.FModel,''),isnull(m1.FName,''),isnull(c2.FIndex,0),round(c2.FQuantity_Base,v3.FQtyDecimal),isnull(v3.FQtyDecimal,0),round(c2.FInitReceiveQty*x7.FCoefficient,v3.FQtyDecimal),isnull(v3.FItemID,0),0 FID,isnull(c4.FName,''), isnull(x1.FContractNo,''), isnull(x5.FNumber,''),isnull(x5.FName,''),isnull(x6.FNumber,''),isnull(x6.FName,''), IsNull(x1.FCurrencyID,1), IsNull(x1.FCurrencyID,1),IsNull(x1.FExchangeRate,1),IsNull(x1.FExchangeRate,1), x3.FName,c2.FAmountIncludeTaxFor, '采购付款',x1.FContractNo,x1.FDate,x1.FDate,c2.FInitReceiveAmtFor,4,x1.FExplanation , X1.FClassTypeID , 0, X1.FContractID from t_rpContractEntry c2 left join t_RpContract x1 on x1.FContractID=c2.FContractID left join t_item x2 on x1.FCustomer=x2.FItemID left join t_currency x3 on x1.FCurrencyID=x3.FCurrencyID left join t_RP_SystemEnum x4 on x1.FContractTypeID=x4.FItemID left join t_item x5 on x1.FDepartment=x5.FItemID left join t_item x6 on x1.FEmployee=x6.FItemID left join t_ICItem v3 on c2.FProductID=v3.FItemID
left join t_measureunit x7 on c2.FUnitID=x7.FMeasureUnitID
left join t_AuxItem c4 on c2.FAuxPropID=c4.FItemID
left join t_measureunit m1 on m1.FUnitGroupID=x7.FUnitGroupID and m1.FStandard=1
Where x1.FIsInit=1 And (c2.FInitReceiveAmtFor>0 or c2.FInitReceiveQty>0) And x1.frp= 0 and x1.FCurrencyID=1 and x2.FItemClassID=8 and x1.FDate>='1900-01-01' and x1.FDate<='2010-05-20'
Update t1 set t1.FAmountDecimal=IsNull(t2.FScale,0) from #t_Contract t1 inner join t_Currency t2 on t1.FCCurrID=t2.FCurrencyID
INSERT INTO #t_Contract1(FClassTypeID,FTranType,FInterID,FNumber,FName,FItemID,FItemNumber,FItemName,FModel,FAuxName,FUnitName,FEntryID,FContractType,FContractName,
FContractNo,FDeptNo,FDeptName,FEmpNo,FEmpName,FDate,FCCurrID,FDCurrID,FConRate,FBillRate,FCurrencyName,FTotalQtyFor,
FQtyDecimal,FTotalAmountFor,FAmountDecimal,FPhaseName,FBillNumber,FBillDate,FQtyOrder,FAmountOrder,
FQtyInOut,FAmountInOut,FQtyTicket,FAmountTicket,FQtyAccept,FAmountAccept,FTypeID,FNewCheckID,
FAmountFee, ---运费发票金额
FTaxRateFee, ---运费发票税率
FAuxPriceFee, ---运费单价
FAuxQtyFee, ---运费数量
FTaxAmountFee, ---运费税金
FAmountFeePay, ---运费付款
FQtySale,
FConsignPriceSale,
FConsignAmountSale,
FQtyInvoice, ---发票号数量
FTaxPriceInvoice,
FAllAmountInvoice ---销售发票金额
)
Select FClassTypeID,FTranType,FInterID,FNumber,FName,FItemID,FItemNumber,FItemName,FModel,FAuxName,FUnitName,FEntryID,FContractType,FContractName,FContractNo,FDeptNo,FDeptName,FEmpNo,FEmpName,FDate,FCCurrID,FDCurrID,FConRate,FBillRate,FCurrencyName,
max(FTotalQtyFor),max(FQtyDecimal),max(FTotalAmountFor),max(FAmountDecimal),FPhaseName,FBillNumber,FBillDate,sum(FQtyOrder),sum(FAmountOrder),
sum(FQtyInOut),sum(FAmountInOut),sum(FQtyTicket),sum(FAmountTicket),sum(FQtyAccept),sum(FAmountAccept),FTypeID,FNewCheckID,
sum(FAmountFee), ---运费发票金额
sum(FTaxRateFee), ---运费发票税率
sum(FAuxPriceFee), ---运费单价
sum(FAuxQtyFee), ---运费数量
sum(FTaxAmountFee), ---运费税金
sum(FAmountFeePay), ---运费付款
sum(FQtySale), ---销售数量
sum(FConsignPriceSale), ---销售单价
sum(FConsignAmountSale), --销售金额
sum(FQtyInvoice), ---发票数量
sum(FTaxPriceInvoice),
sum(FAllAmountInvoice) ---销售发票金额
from #t_Contract group by FClassTypeID,FTranType,FInterID,FNumber,FName,FItemID,FItemNumber,FItemName,FModel,FAuxName,FUnitName,FEntryID,FContractType,FContractName,
FContractNo,FCCurrID,FDCurrID,FConRate,FBillRate,FCurrencyName,FPhaseName,FBillNumber,FDate,FDeptNo,FDeptName,FEmpNo,FEmpName,FBillDate,FTypeID,fIndexID,FNewCheckID
INSERT INTO #t_Contract1(FNumber,FName,FItemNumber,FItemName,FModel,FAuxName,FUnitName,FEntryID,FContractType,FContractName,
FContractNo,FDeptNo,FDeptName,FEmpNo,FEmpName,FDate,FCCurrID,FDCurrID,FConRate,FBillRate,FCurrencyName,FTotalQtyFor,FQtyDecimal,FTotalAmountFor,FAmountDecimal,FPhaseName,FTypeID,FNewCheckID)
select x2.FNumber,x2.FName,x7.FNumber,x7.FName,x7.FModel,c4.FName,m1.FName,c2.FIndex,x4.FName,x1.FContractName,x1.FContractNo,
x5.FNumber,x5.FName,x6.FNumber,x6.FName,x1.Fdate, IsNull(x1.FCurrencyID,1),0,IsNull(x1.FExchangeRate,1),1,
x3.FName,round(c2.FQuantity_Base,x7.FQtyDecimal),x7.FQtyDecimal,c2.FAmountIncludeTaxFor,IsNull(x3.FScale,0),(select top 1 FPhaseName from #t_Phase order by FTypeID),
(select top 1 FTypeID from #t_Phase order by FTypeID),0 FID
from t_RPContract x1
Inner join t_RPContractEntry c2 on x1.FContractID=c2.FContractID
left join t_ICItem x7 on c2.FProductID=x7.FItemID
left join t_item x2 on x1.FCustomer=x2.FItemID
left join t_currency x3 on x1.FCurrencyID=x3.FCurrencyID
left join t_RP_SystemEnum x4 on x1.FContractTypeID=x4.FItemID
left join t_item x5 on x1.FDepartment=x5.FItemID
left join t_item x6 on x1.FEmployee=x6.FItemID
left join t_measureunit m0 on c2.FUnitID=m0.FMeasureUnitID
left join t_measureunit m1 on m1.FUnitGroupID=m0.FUnitGroupID and m1.FStandard=1
left join t_AuxItem c4 on c2.FAuxPropID=c4.FItemID
where 1=1 And x1.frp= 0 and x1.FCurrencyID=1 and x2.FItemClassID=8 and x1.FDate>='1900-01-01' and x1.FDate<='2010-05-20' and c2.FIndex not in(select distinct t1.FEntryID from #t_Contract1 t1 where t1.FTypeID in (select FTypeID from #t_Phase) and t1.FContractNo=x1.FContractNo )
/**计算合计数**/
/**交叉组合
INSERT INTO #t_Contract1(FNumber,FName,FItemNumber,FItemName,FModel,FAuxName,FDate,FDeptName,FDeptNo,FEmpNo,FEmpName,FUnitName,FEntryID,FContractType,FContractName,
FContractNo,FCCurrID,FDCurrID,FConRate,FBillRate,FCurrencyName,FTotalQtyFor,FQtyDecimal,FTotalAmountFor,FAmountDecimal,FPhaseName,FTypeID
)
select distinct t1.FNumber,t1.FName,t1.FItemNumber,t1.FItemName,t1.FModel,t1.FAuxName,t1.FDate,t1.FDeptName,t1.FDeptNo,t1.FEmpNo,t1.FEmpName,t1.FUnitName,t1.FEntryID,t1.FContractType,t1.FContractName,
t1.FContractNo,t1.FCCurrID,0,t1.FConRate,1,t1.FCurrencyName,t1.FTotalQtyFor,t1.FQtyDecimal,t1.FTotalAmountFor,t1.FAmountDecimal,t4.FPhaseName,t4.FTypeID
from #t_Contract1 t1
Cross join (select distinct t2.FPhaseName,t2.FTypeID,t3.FContractNo,t3.FEntryID from #t_Phase t2 inner join #t_Contract1 t3 on
t3.FPhaseName<>t2.FPhaseName where t3.FTypeID<>99 and t3. FTypeID in (1,2,3,4,5,6,7,8,9)) t4 where t1.FContractNo=t4.FContractNo and t1.FEntryID=t4.FEntryID AND t1.FTypeID<>99 and t1. FTypeID in (1,2,3,4,5,6,7,8,9)
AND t4.FTypeID Not IN (SELECT FTypeID From #t_Contract1 Where FContractNo=t4.FContractNo and FEntryID=t4.FEntryID)
--- 以下为计算合计数原代码
INSERT INTO #t_Contract1(FNumber,FName,FItemNumber,FItemName,FModel,FUnitName,FEntryID,FContractType,FContractName,
FContractNo,FCurrencyName,FTotalQtyFor,FQtyDecimal,FTotalAmountFor,FAmountDecimal,FPhaseName,FBillNumber,FDate,FQtyOrder,FAmountOrder,
FQtyInOut,FAmountInOut,FQtyTicket,FAmountTicket,FQtyAccept,FAmountAccept,FTypeID,FSumSort)
Select FNumber,FName,FItemNumber,FItemName,'','',FEntryID,FContractType,FContractName,
FContractNo,'',max(isnull(FTotalQtyFor,0)),max(FQtyDecimal),max(isnull(FTotalAmountFor,0)),max(FAmountDecimal),
'','',null,sum(isnull(FQtyOrder,0)),sum(isnull(FAmountOrder,0)),
sum(isnull(FQtyInOut,0)),sum(isnull(FAmountInOut,0)),sum(isnull(FQtyTicket,0)),
sum(isnull(FAmountTicket,0)),sum(isnull(FQtyAccept,0)),sum(isnull(FAmountAccept,0)),99,101
from #t_Contract1 where FTypeID in (1,2,3,4,5,6,7,8,9) group by FContractNo,FNumber,FName,FEntryID,FItemNumber,FItemName,FContractType,FContractName,FIndexID
****/
/**计算合计数**/
INSERT INTO #t_Contract1(FNumber,FName,FItemNumber,FItemName,FModel,FUnitName,FEntryID,FContractType,FContractName,
FContractNo,FCurrencyName,FTotalQtyFor,FQtyDecimal,FTotalAmountFor,FAmountDecimal,FPhaseName,FBillNumber,FDate,FQtyOrder,FAmountOrder,
FQtyInOut,FAmountInOut,FQtyTicket,FAmountTicket,FQtyAccept,FAmountAccept,FTypeID,FSumSort,
FAmountFee, ---运费发票金额
FTaxRateFee, ---运费发票税率
FAuxPriceFee, ---运费单价
FAuxQtyFee, ---运费数量
FTaxAmountFee, ---运费税金
FAmountFeePay, ---运费付款
FQtySale, ---销售数量
FConsignPriceSale,---销售单价
FConsignAmountSale,---销售金额
FQtyInvoice, ---发票数量
FTaxPriceInvoice,
FAllAmountInvoice ---销售发票金额
)
Select FNumber,FName,FItemNumber,FItemName,'','',FEntryID,FContractType,FContractName,
FContractNo,'',max(isnull(FTotalQtyFor,0)),max(FQtyDecimal),max(isnull(FTotalAmountFor,0)),max(FAmountDecimal),
'','',null,sum(isnull(FQtyOrder,0)),sum(isnull(FAmountOrder,0)),
sum(isnull(FQtyInOut,0)),sum(isnull(FAmountInOut,0)),sum(isnull(FQtyTicket,0)),
sum(isnull(FAmountTicket,0)),sum(isnull(FQtyAccept,0)),sum(isnull(FAmountAccept,0)),99,101, sum(FAmountFee), ---运费发票金额
sum(FTaxRateFee), ---运费发票税率
sum(FAuxPriceFee), ---运费单价
sum(FAuxQtyFee), ---运费数量
sum(FTaxAmountFee), ---运费税金
sum(FAmountFeePay), ---运费付款
sum(FQtySale), ---销售数量
sum(FConsignPriceSale),---销售单价
sum(FConsignAmountSale),---销售金额
sum(FQtyInvoice), ---发票数量
sum(FTaxPriceInvoice),
sum(FAllAmountInvoice) ---销售发票金额
from #t_Contract1 where FTypeID in (1,2,3,4,5,6,7,8,9) group by FContractNo,FNumber,FName,FEntryID,FItemNumber,FItemName,FContractType,FContractName,FIndexID
Update #t_Contract1 set FNQtyOrder=isnull(FTotalQtyFor,0)-isnull(FQtyOrder,0) where FtypeID=99
Update #t_Contract1 set FNQtyInOut=isnull(FTotalQtyFor,0)-isnull(FQtyInOut,0) where FtypeID=99
Update #t_Contract1 set FNQtyTicket=isnull(FTotalQtyFor,0)-isnull(FQtyTicket,0) where FtypeID=99
Update #t_Contract1 set FNQtyAccept=isnull(FTotalQtyFor,0)-isnull(FQtyAccept,0) where FtypeID=99
Update #t_Contract1 set FNAmountTicket=isnull(FTotalAmountFor,0)-isnull(FAmountTicket,0) where FtypeID=99
Update #t_Contract1 set FNAmountAccept=isnull(FTotalAmountFor,0)-isnull(FAmountAccept,0) where FtypeID=99
Update #t_Contract1 set FQtyDecimal=0 where FQtyDecimal is Null
Update #t_Contract1 set FAmountDecimal=0 where FAmountDecimal is Null
select * from #t_Contract1 where FTypeID in (1,2,3,4,5,6,7,8,9)
or FTypeID=99
---order by FNumber,FContractNo,FContractType,FEntryID,FItemNumber,FTypeID,FDate,FBillNumber
order by FNumber,FContractNo,FTypeID,FContractType,FEntryID,FItemNumber,FDate,FBillNumber
drop Table #t_Contract
drop Table #t_Contract1
drop Table #t_Phase
set nocount off
/**合同跟踪表*/
GO
扫一扫
2267
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
