升级路线：专业版12.0-专业版12.3-旗舰版2.0
大神们帮忙看看。
语句已终止。
违反了 PRIMARY KEY 约束 'PK_t_Rp_CheckDetail'。不能在对象 'dbo.t_rp_CheckDetail' 中插入重复键。--select * from t_RP_CheckInfo where FCheckType = 2 --预收冲应收 FCustomerID
--select * from t_RP_CheckInfo where FCheckType = 3 --应收冲应付 FCustomerID,FVendorID
--select * from t_RP_CheckInfo where FCheckType = 0 --预付冲应付 FVendorID
--select * from t_RP_CheckInfo where FCheckType = 1 --应付冲应收 FCustomerID,FVendorID
--select * from t_RP_CheckInfo where FCheckType = 4 --应收转应收 FoutCustID,FInCustID
--select * from t_RP_CheckInfo where FCheckType = 5 --应付转应付 FoutVendorID,FInVendorID
--迁移核销单记录，核销单只涉及到三张表
-- t_rp_NewCheckInfo 核销日志 t_rp_CheckEntry 存货核销表 t_rp_CheckDetail(预收付不需要写入)
DECLARE @FInterID int
Declare @FCheckType int
declare @FCustomerID int
declare @FVendorID int
declare @FOutCustID int
declare @FInCustID int
declare @FOutVendorID int
declare @FInVendorID int
DECLARE @RPCheck_cursor CURSOR
Set @RPCheck_cursor = CURSOR FOR
select FInterID,FCheckType,FCustomerID,FVendorID,FOutCustID,FInCustID,FOutVendorID,FInVendorID from Tmp_t_RP_CheckInfo
OPEN @RPCheck_cursor
FETCH @RPCheck_cursor into @FInterID,@FCheckType,@FCustomerID,@FVendorID,@FOutCustID,@FInCustID,@FOutVendorID,@FInVendorID
while @@fetch_status=0
begin
declare @MaxContactID int
if @FCheckType = 2 --预收冲应收
begin
--t_rp_NewCheckInfo
insert t_rp_NewCheckInfo (FInterID,FID,FTransfer,FTransferID,FContactID,FCheckDate,
FChecker,FCustomer,Fdepartment,FEmployee,FBillID,FCheckType,FCheckManer,
FType,
FIsinit,FExplanation,
FCheckAmount,FCheckAmountFor,FRemainAmount,FRemainAmountFor,
FDiscount,FDiscountFor,FCurrencyID,FExchangeRate,FVoucherID,
FRP,FIsBad,FBadID,FARToAR,FPreToPre,FZXID,FItemClassID,
FUnTread,FUnTreadID,FUnEndorse
)
select a.FK3InterID,a.FCheckID,1,0,a.FK3ContactID,isnull(b.FCheckDate,b.FDate),
isnull(b.FCheckerID,16394),@FCustomerID,b.FDepartID,FEmployeeID,a.FContactID,1 as FCheckType,1 as FCheckManer,
case FContactType when 1000510 then 5 --预收和初始预收
when 1000014 then 5
when 1000501 then 3 --初始发票和发票
when 80 then 3
when 86 then 3
when 1000021 then 1 --其他应收
else 0 end as FType,
a.FIsinit,'应收冲预收',
a.FCheckAmount,a.FCheckAmountFor,(a.FUnCheckAmountFor-a.FCheckAmountFor)*a.FExchangeRate,a.FUnCheckAmountFor-a.FCheckAmountFor,
0 as FDiscount,0 as FDiscountFor,a.FCurrencyID,a.FExchangeRate,b.FVoucherID,
1 as FRP,0,null,0,0,-1,1,
0,0,0
from Tmp_t_RP_CheckInfoEntry a inner join Tmp_t_RP_CheckInfo b on a.FInterID = b.FInterID
where b.finterid = @FInterID order by a.fno
--t_rp_CheckDetail(预收不需要)
insert t_rp_CheckDetail (FCheckID,FContactID,FARDate,FCheckDate,
FBegAmount,FBegAmountFor,FRemainAmount,FRemainAmountFor)
select a.FCheckID,a.FK3ContactID,a.FContactDate,isnull(b.FCheckDate,b.FDate),
a.FUnCheckAmountFor*a.FExchangeRate,FUnCheckAmountFor,(a.FUnCheckAmountFor-a.FCheckAmountFor)*a.FExchangeRate,a.FUnCheckAmountFor-a.FCheckAmountFor
from Tmp_t_RP_CheckInfoEntry a inner join Tmp_t_RP_CheckInfo b on a.FInterID = b.FInterID
where b.finterid = @FInterID and a.FContactType not in (1000510,1000014)
--计算t_rp_CheckEntry(当多对多核销时,上下分录要逐次进行核销)
delete Tmp_Cal_CheckEntry
exec p_Set_CalCheckEntryInfo @FInterID,@FCheckType
--t_rp_CheckEntry(根据生成的Tmp_Cal_CheckEntry记录插入)
--应收数据(fchecktype=1,每行一条,核销对应的billID全为自身id)
insert t_rp_CheckEntry (FType,
FInvoiceID,FEntryID,FID,
FInterID,FBillID,FContactID,FRP,FItemID,FUnitID,FAuxPropID,
FIsinit,FBillType,FRemainQuantity,FRemainAmount,FRemainAmountFor,
FPrice,FTaxPrice,FBillID_SRC,FEntryID_SRC,FType_SRC,FIsinit_SRC,
FQty_SRC,FAmount_SRC,FAmountFor_SRC,FContractNo,FCOntractEntryID,FOrderNo,FOrderEntryID,
FCheckAmount,FCheckAmountFor,FCheckQuantity )
select case a.FContactType when 1000510 then 5 --预收和初始预收
when 1000014 then 5
when 1000501 then 3 --初始发票和发票
when 80 then 3
when 86 then 3
when 1000021 then 1 --其他应收
else 0 end as FType,
case m.FcheckType when 1 then a.FContactID else m.FContactID2 end,1 as FEntryID,a.FCheckID,
a.FK3InterID,a.FContactID,a.FK3ContactID,1 as FRP,0,0,0,
a.FIsinit,0 as FBillTYpe,0,(a.FUnCheckAmountFor-a.FCheckAmountFor+m.FRemainAmountFor)*a.FExchangeRate,a.FUnCheckAmountFor-a.FCheckAmountFor+m.FRemainAmountFor,
0,0,m.FContactID2,1 as FEntryID_SRC,m.FType,m.FIsInit,
0,m.FCheckAmountFor*b.FExchangeRate,m.FCheckAmountFor,'',0,'',0,
m.FCheckAmountFor*a.FExchangeRate,m.FCheckAmountFor,0
from Tmp_Cal_CheckEntry m
inner join Tmp_t_RP_CheckInfoEntry a on m.FInterID = a.FInterID and m.FNo1 = a.FNo
inner join Tmp_t_RP_CheckInfoEntry b on m.FInterID = b.FInterID and m.FNo2 = b.FNo
where m.FInterID = @FInterID
end
else if @FCheckType = 0 --预付冲应付
begin
--t_rp_NewCheckInfo
insert t_rp_NewCheckInfo (FInterID,FID,FTransfer,FTransferID,FContactID,FCheckDate,
FChecker,FCustomer,Fdepartment,FEmployee,FBillID,FCheckType,FCheckManer,
FType,
FIsinit,FExplanation,
FCheckAmount,FCheckAmountFor,FRemainAmount,FRemainAmountFor,
FDiscount,FDiscountFor,FCurrencyID,FExchangeRate,FVoucherID,
FRP,FIsBad,FBadID,FARToAR,FPreToPre,FZXID,FItemClassID,
FUnTread,FUnTreadID,FUnEndorse
)
select a.FK3InterID,a.FCheckID,1,0,a.FK3ContactID,isnull(b.FCheckDate,b.FDate),
isnull(b.FCheckerID,16394),@FVendorID,b.FDepartID,FEmployeeID,a.FContactID,1 as FCheckType,1 as FCheckManer,
case FContactType when 1000511 then 6 --预付和初始预付
when 1000018 then 6
when 1000503 then 4 --初始发票和发票
when 75 then 4
when 76 then 4
when 94 then 4
when 1000022 then 2 --其他应付
else 0 end as FType,
a.FIsinit,'应付冲预付',
a.FCheckAmount,a.FCheckAmountFor,(a.FUnCheckAmountFor-a.FCheckAmountFor)*a.FExchangeRate,a.FUnCheckAmountFor-a.FCheckAmountFor,
0 as FDiscount,0 as FDiscountFor,a.FCurrencyID,a.FExchangeRate,b.FVoucherID,
0 as FRP,0,null,0,0,-1,8 as FItemClassID,
0,0,0
from Tmp_t_RP_CheckInfoEntry a inner join Tmp_t_RP_CheckInfo b on a.FInterID = b.FInterID
where b.finterid = @FInterID order by a.fno
--t_rp_CheckDetail(预付不需要)
insert t_rp_CheckDetail (FCheckID,FContactID,FARDate,FCheckDate,
FBegAmount,FBegAmountFor,FRemainAmount,FRemainAmountFor)
select a.FCheckID,a.FK3ContactID,a.FContactDate,isnull(b.FCheckDate,b.FDate),
a.FUnCheckAmountFor*a.FExchangeRate,FUnCheckAmountFor,(a.FUnCheckAmountFor-a.FCheckAmountFor)*a.FExchangeRate,a.FUnCheckAmountFor-a.FCheckAmountFor
from Tmp_t_RP_CheckInfoEntry a inner join Tmp_t_RP_CheckInfo b on a.FInterID = b.FInterID
where b.finterid = @FInterID and a.FContactType not in (1000511,1000018)
--计算t_rp_CheckEntry(当多对多核销时,上下分录要逐次进行核销)
delete Tmp_Cal_CheckEntry
exec p_Set_CalCheckEntryInfo @FInterID,@FCheckType
--t_rp_CheckEntry(根据生成的Tmp_Cal_CheckEntry记录插入)
--应付数据(fchecktype=1,每行一条,核销对应的billID全为自身id)
insert t_rp_CheckEntry (FType,
FInvoiceID,FEntryID,FID,
FInterID,FBillID,FContactID,FRP,FItemID,FUnitID,FAuxPropID,
FIsinit,FBillType,FRemainQuantity,FRemainAmount,FRemainAmountFor,
FPrice,FTaxPrice,FBillID_SRC,FEntryID_SRC,FType_SRC,FIsinit_SRC,
FQty_SRC,FAmount_SRC,FAmountFor_SRC,FContractNo,FCOntractEntryID,FOrderNo,FOrderEntryID,
FCheckAmount,FCheckAmountFor,FCheckQuantity )
select case a.FContactType when 1000511 then 6 --预付和初始预付
when 1000018 then 6
when 1000503 then 4 --初始发票和发票
when 75 then 4
when 76 then 4
when 94 then 4
when 1000022 then 2 --其他应付
else 0 end as FType,
case m.FcheckType when 1 then a.FContactID else m.FContactID2 end,1 as FEntryID,a.FCheckID,
a.FK3InterID,a.FContactID,a.FK3ContactID,0 as FRP,0,0,0,
a.FIsinit,0 as FBillTYpe,0,(a.FUnCheckAmountFor-a.FCheckAmountFor+m.FRemainAmountFor)*a.FExchangeRate,a.FUnCheckAmountFor-a.FCheckAmountFor+m.FRemainAmountFor,
0,0,m.FContactID2,1 as FEntryID_SRC,m.FType,m.FIsInit,
0,m.FCheckAmountFor*b.FExchangeRate,m.FCheckAmountFor,'',0,'',0,
m.FCheckAmountFor*a.FExchangeRate,m.FCheckAmountFor,0
from Tmp_Cal_CheckEntry m
inner join Tmp_t_RP_CheckInfoEntry a on m.FInterID = a.FInterID and m.FNo1 = a.FNo
inner join Tmp_t_RP_CheckInfoEntry b on m.FInterID = b.FInterID and m.FNo2 = b.FNo
where m.FInterID = @FInterID
end
else if @FCheckType = 3 --应收冲应付
begin
--应收冲应付涉及到四个表 会向t_rp_Contact表中额外增加Ftype为9的记录
set @MaxContactID = 0
select @MaxContactID = isnull(max(FID),1) from t_rp_contact
update Tmp_t_RP_CheckInfoEntry set FrowID = 0
update Tmp_t_RP_CheckInfoEntry set @MaxContactID = FRowID = @MaxContactID + 1 where FInterID= @FInterID
INSERT INTO t_rp_contact(FID,FYear,FPeriod,FRP,FType,
FDate,FFincDate,FNumber,FCustomer,FDepartment,
FEmployee,FCurrencyID,FExchangeRate,
FAmount,FAmountFor,FRemainAmount,FRemainAmountFor,
FContractNo,FInvoiceID,FRPBillID,FBillID,FBegID,
FExpenseID,FBussinessDiscount,FCashDiscount,
FRPDate,FSuperDays,FDirectSale,FSaleBackAmount,FSaleBackAmountFor,FDue,FIsBad,FBadReason,
FVoucherID,FGroupID,FAccountID,FIsInit,FStatus,FPost,FToBal,FPre,FK3Import,FInterestRate,
FCheckType,FBillType,FInvoiceType,FItemClassID,FExplanation,FSmInvID,FPreparer)
select a.FRowID,isnull(t2.FYear,0),isnull(t2.FPeriod,0),case a.FEntryType when 0 then 1 else 0 end as FRP,9 as FType,
b.FDate,b.FDate,a.FContactNo,case a.FEntryType when 0 then @FCustomerID else @FVendorID end as FCustomer,b.FDepartID,--部门和单据编号还有问题.
b.FEmployeeID,a.FCurrencyID,a.FExchangeRate,--职员还有点问题
a.FCheckAMount,a.FCheckAmountFor,0 as FRemainAmount,0 as FRemainAmountFor,
'',case a.FContactType when 80 then a.FContactID when 86 then a.FContactID when 75 then a.FContactID when 76 then a.FContactID when 94 then a.FContactID else 0 end as FInvoiceID,
case a.FContactType when 1000021 then a.FContactID when 1000022 then a.FContactID else 0 end as FRPBillID,0 as FBillID,
case a.FContactType when 1000501 then a.FContactID when 1000503 then a.FContactID else 0 end as FBegID,
0,0,0,
a.FContactDate,0,0,0,0,0,0,null,
b.FVoucherID,0,0 as FAccountID,0 as FIsInit,1 as Ftatus,1 as FPost,0,0,0,0,
0,0,0,case a.FEntryType when 0 then 1 else 8 end,'应收单冲应付单',0,-2 --备注还要修改下
from Tmp_t_RP_CheckInfoEntry a
inner join Tmp_t_RP_CheckInfo b on a.FInterID = b.FInterID
left join t_PeriodDate_All t2 on b.FDate>=t2.FStartDate and b.FDate<=t2.FEndDate
where a.FInterID = @FInterID
--t_rp_NewCheckInfo
insert t_rp_NewCheckInfo (FInterID,FID,FTransfer,FTransferID,FContactID,FCheckDate,
FChecker,FCustomer,Fdepartment,FEmployee,FBillID,FCheckType,FCheckManer,
FType,
FIsinit,FExplanation,
FCheckAmount,FCheckAmountFor,FRemainAmount,FRemainAmountFor,
FDiscount,FDiscountFor,FCurrencyID,FExchangeRate,FVoucherID,
FRP,FIsBad,FBadID,FARToAR,FPreToPre,FZXID,FItemClassID,
FUnTread,FUnTreadID,FUnEndorse
)
select a.FK3InterID,a.FCheckID,1,a.FRowID,a.FK3ContactID,isnull(b.FCheckDate,b.FDate),
isnull(b.FCheckerID,16394),case a.FEntryType when 0 then @FCustomerID else @FVendorID end,
b.FDepartID,FEmployeeID,a.FContactID,2 as FCheckType,1 as FCheckManer,--部门职员还要修改下
case FContactType when 1000510 then 5 --预收和初始预收
when 1000014 then 5
when 1000501 then 3 --初始发票和发票
when 80 then 3
when 86 then 3
when 1000021 then 1 --其他应收
when 1000511 then 6 --预付和初始预付
when 1000018 then 6
when 1000503 then 4 --初始发票和发票
when 75 then 4
when 76 then 4
when 94 then 4
when 1000022 then 2 --其他应付
else 0 end as FType,
a.FIsinit,'应收冲应付',
a.FCheckAmount,a.FCheckAmountFor,(a.FUnCheckAmountFor-a.FCheckAmountFor)*a.FExchangeRate,a.FUnCheckAmountFor-a.FCheckAmountFor,
0 as FDiscount,0 as FDiscountFor,a.FCurrencyID,a.FExchangeRate,b.FVoucherID,
1 as FRP,0,null,0,0,-1,case a.FEntryType when 0 then 1 else 8 end as FItemClassID,
0,0,0
from Tmp_t_RP_CheckInfoEntry a
inner join Tmp_t_RP_CheckInfo b on a.FInterID = b.FInterID
where b.finterid = @FInterID order by a.fno
--t_rp_CheckDetail(应收应付全部)
insert t_rp_CheckDetail (FCheckID,FContactID,FARDate,FCheckDate,
FBegAmount,FBegAmountFor,FRemainAmount,FRemainAmountFor)
select a.FCheckID,a.FK3ContactID,a.FContactDate,isnull(b.FCheckDate,b.FDate),
a.FUnCheckAmountFor*a.FExchangeRate,FUnCheckAmountFor,(a.FUnCheckAmountFor-a.FCheckAmountFor)*a.FExchangeRate,a.FUnCheckAmountFor-a.FCheckAmountFor
from Tmp_t_RP_CheckInfoEntry a inner join Tmp_t_RP_CheckInfo b on a.FInterID = b.FInterID
where b.finterid = @FInterID
--计算t_rp_CheckEntry(当多对多核销时,上下分录要逐次进行核销)
delete Tmp_Cal_CheckEntry
exec p_Set_CalCheckEntryInfo @FInterID,@FCheckType
--t_rp_CheckEntry(根据生成的Tmp_Cal_CheckEntry记录插入)
--应收数据(fchecktype=0,每行一条,核销对应的billID全为自身id)
insert t_rp_CheckEntry (FType,
FInvoiceID,FEntryID,FID,
FInterID,FBillID,FContactID,FRP,FItemID,FUnitID,FAuxPropID,
FIsinit,FBillType,FRemainQuantity,FRemainAmount,FRemainAmountFor,
FPrice,FTaxPrice,FBillID_SRC,FEntryID_SRC,FType_SRC,FIsinit_SRC,
FQty_SRC,FAmount_SRC,FAmountFor_SRC,FContractNo,FCOntractEntryID,FOrderNo,FOrderEntryID,
FCheckAmount,FCheckAmountFor,FCheckQuantity )
select case a.FContactType when 1000510 then 5 --预收和初始预收
when 1000014 then 5
when 1000501 then 3 --初始发票和发票
when 80 then 3
when 86 then 3
推荐阅读