金蝶官方给出的外销订单通过设置Bos到处模板再导入，我觉得还要手工再Excel上填数据，跟通过界面录入的效果一样慢， 且相关下游单据还要单独想办法转移。所以就想办法自己做存储过程，通过输入外销订单号，找到相关的外销订单进行数据转移，同时要记录转移目标账套上的对应的相关数据标识，以便查数据。 还要转移对应的采购订单 和修改力邦账套对应的销售订单的客户
（1）在外销订单表SEOrder增加列 Finteridexp，来记录目标账套的数据inerID，
（2）在外销订单表SEOrder增加列FPOOldBillNO，来记录外销订单的客户订单号对应的采购订单
（3）在外销订单表SEOrder增加列FSELBOldBillNO，来记录外销订单的客户订单号对应在力邦的销售订单（改客户名）
（4）力邦账套的销售订单表要增加字段FCustID_old，记录修改前的客户ID，以便跟踪欢迎数据。
具体实现思路：
（1）移动外销订单相关的表： SEOrder，SEOrderEntry，SEOrderClause（外销订单文本条款表），SEOrderExp（外销订单扩展文本） ，ICClassCheckRecords1007100（审核文件）
（2）因为有些采购订单不是下推的手动创建的 ，为了不漏采购订单，只能通过客户订单号来找采购订单了
（3）因为外销订单客户订单号相同的情况下，会出现转移了多条采购订单，所以要用到游标
（4）外销订单和采购购订单的FChildren自动要赋值为0，来切断上下查关系，因为这个太复杂了，目标表（POOrderEntry）的FSourceBillNo, FSourceEntryID, FSourceInterId, FSourceTranType 分别对应源明细表（SEOrderEntry）的FBillNo, FInterID, FInterId, FTranType
USE [jb2023]
GO
/****** Object: StoredProcedure [dbo].[p_rp_seorder] Script Date: 11/28/2023 11:18:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
alter PROCEDURE [dbo].[p_rp_seorder]
@strFBillNo varchar(255) , --需要转移的外销订单号
@strReMsg varchar(500) output
AS
set @strReMsg ='-1';
DECLARE @FInterID_new INT,@FInterID_old INT ,@FBillNo_new varchar(255) ,@FCustOrderNo nvarchar(100)
SET NOCOUNT ON
---先判断是否存在
if not exists ( select 1 from seorder where FCancellation=0 and FBillNo=@strFBillNo )
set @strReMsg ='敬邦账套不存在该外销订单号的数据';
else if exists ( select 1 from seorder where FCancellation=0 and FBillNo=@strFBillNo and (Finteridexp is not null and Finteridexp<>'' ) )
set @strReMsg ='该数据已经转移到时美特账套，无需再转移';
else
begin
exec summit2023.dbo. GetICMaxNum 'SEOrder', @FInterID_new output
-- select * from summit2023.dbo.ICMaxNum where FTableName like'%seorder%'
select @FInterID_old=FInterID ,@FBillNo_new='PS'+FBillNo,@FCustOrderNo=(select FCustOrderNo from SEOrderExp where SEOrderExp. FInterID=seorder.FInterID ) from seorder where FCancellation=0 and FBillNo=@strFBillNo
INSERT INTO [summit2023].[dbo].[SEOrder]
([FBrNo]
,[FInterID]
,[FBillNo]
,[FCurrencyID]
,[FCustID]
,[FDate]
,[FPayStyle]
,[FPayDate]
,[FFetchStyle]
,[FFetchDate]
,[FFetchAdd]
,[FSaleStyle]
,[FDeptID]
,[FEmpID]
,[FCheckerID]
,[FBillerID]
,[FNote]
,[FClosed]
,[FTranType]
,[FInvoiceClosed]
,[FBClosed]
,[FMangerID]
,[FSettleID]
,[FExchangeRate]
,[FDiscountType]
,[FStatus]
,[FCancellation]
,[FMultiCheckLevel1]
,[FMultiCheckLevel2]
,[FMultiCheckLevel3]
,[FMultiCheckLevel4]
,[FMultiCheckLevel5]
,[FMultiCheckLevel6]
,[FMultiCheckDate1]
,[FMultiCheckDate2]
,[FMultiCheckDate3]
,[FMultiCheckDate4]
,[FMultiCheckDate5]
,[FMultiCheckDate6]
,[FCurCheckLevel]
,[FTransitAheadTime]
,[FPOOrdBillNo]
,[FRelateBrID]
,[FImport]
,[FOrderAffirm]
,[FTranStatus]
,[FUUID]
,[FSystemType]
,[FCashDiscount]
,[FCheckDate]
,[FExplanation]
,[FSettleDate]
,[FSelTranType]
,[FChildren]
,[FBrID]
,[FAreaPS]
,[FClassTypeID]
,[FManageType]
,[FSysStatus]
,[FVersionNo]
,[FChangeDate]
,[FChangeCauses]
,[FChangeMark]
,[FChangeUser]
,[FValidaterName]
,[FConsignee]
,[FDrpRelateTranType]
,[FPrintCount]
,[FExchangeRateType]
,[FCustAddress]
,[FPlanCategory]
,[FSendStatus]
,[FEnterpriseID]
,[FXSyncOrder]
,[FAccessoryCount]
,[FCloseUser]
,[FCloseDate]
,[FCloseCauses]
,[FHeadSelfS0152]
,[FHeadSelfS0158]
,[FHeadSelfS0153]
,[FHeadSelfS0154]
,[FHeadSelfS0156]
,[FHeadSelfS0157]
,[FHeadSelfS0165]
,[FHeadSelfS0155]
,[FHeadSelfS0163]
,[FHeadSelfS0164]
,[FDisCount]
,[FDisCountRate]
,[FCommitDisCount]
,[FBase]
,[FText1]
,[FText2]
,[FBase1]
,[FHeadSelfS0172]
,[FHeadSelfS0173])
select [FBrNo]
,@FInterID_new
,'PS'+[FBillNo]
,[FCurrencyID]
,(select top 1 FItemID from summit2023.dbo. t_Organization where FDeleted=0 and FNumber in ( select FNumber from jb2023.dbo. t_Organization jbt where jbt.FItemID=FCustID ) ) --FCustID
,[FDate]
,[FPayStyle]
,[FPayDate]
,[FFetchStyle]
,[FFetchDate]
,[FFetchAdd]
,[FSaleStyle]
,[FDeptID]
,[FEmpID]
,[FCheckerID]
,[FBillerID]
,[FNote]
,[FClosed]
,[FTranType]
,[FInvoiceClosed]
,[FBClosed]
,[FMangerID]
,[FSettleID]
,[FExchangeRate]
,[FDiscountType]
,[FStatus]
,[FCancellation]
,[FMultiCheckLevel1]
,[FMultiCheckLevel2]
,[FMultiCheckLevel3]
,[FMultiCheckLevel4]
,[FMultiCheckLevel5]
,[FMultiCheckLevel6]
,[FMultiCheckDate1]
,[FMultiCheckDate2]
,[FMultiCheckDate3]
,[FMultiCheckDate4]
,[FMultiCheckDate5]
,[FMultiCheckDate6]
,[FCurCheckLevel]
,[FTransitAheadTime]
,[FPOOrdBillNo]
,[FRelateBrID]
,[FImport]
,[FOrderAffirm]
,[FTranStatus]
,[FUUID]
,[FSystemType]
,[FCashDiscount]
,[FCheckDate]
,[FExplanation]
,[FSettleDate]
,[FSelTranType]
,0-- ,[FChildren]
,[FBrID]
,[FAreaPS]
,[FClassTypeID]
,[FManageType]
,[FSysStatus]
,[FVersionNo]
,[FChangeDate]
,[FChangeCauses]
,[FChangeMark]
,[FChangeUser]
,[FValidaterName]
,[FConsignee]
,[FDrpRelateTranType]
,[FPrintCount]
,[FExchangeRateType]
,[FCustAddress]
,[FPlanCategory]
,[FSendStatus]
,[FEnterpriseID]
,[FXSyncOrder]
,[FAccessoryCount]
,[FCloseUser]
,[FCloseDate]
,[FCloseCauses]
,[FHeadSelfS0152]
,[FHeadSelfS0158]
,[FHeadSelfS0153]
,[FHeadSelfS0154]
,[FHeadSelfS0156]
,[FHeadSelfS0157]
,[FHeadSelfS0165]
,[FHeadSelfS0155]
,[FHeadSelfS0163]
,[FHeadSelfS0164]
,[FDisCount]
,[FDisCountRate]
,[FCommitDisCount]
,[FBase]
,[FText1]
,[FText2]
,[FBase1]
,[FHeadSelfS0172]
,[FHeadSelfS0173]
from SEOrder where FBillNo= @strFBillNo
INSERT INTO [summit2023].[dbo].[SEOrderEntry]
([FBrNO]
,[FInterID]
,[FEntryID]
,[FItemID]
,[FQty]
,[FCommitQty]
,[FPrice]
,[FAmount]
,[FTaxRate]
,[FTaxAmount]
,[FTax]
,[FDiscount]
,[FNote]
,[FDate]
,[FDiscountAmount]
,[FInvoiceQty]
,[FBCommitQty]
,[FTranLeadTime]
,[FATPDeduct]
,[FCostObjectID]
,[FUnitID]
,[FAuxBCommitQty]
,[FAuxCommitQty]
,[FAuxInvoiceQty]
,[FAuxPrice]
,[FAuxQty]
,[FUniDiscount]
,[FFinalAmount]
,[FSourceEntryID]
,[FHaveMrp]
,[FStockQty]
,[FAuxStockQty]
,[FBatchNo]
,[FCESS]
,[FAdviceConsignDate]
,[FBomInterID]
,[FMapNumber]
,[FMapName]
,[FLockFlag]
,[FInForeCast]
,[FAllAmount]
,[FAllStdAmount]
,[FAuxPropID]
,[FAuxPriceDiscount]
,[FPriceDiscount]
,[FQtyInvoice]
,[FQtyInvoiceBase]
,[FTaxAmt]
,[FAuxTaxPrice]
,[FTaxPrice]
,[FReceiveAmountFor_Commit]
,[FReceiveAmount_Commit]
,[FSecCoefficient]
,[FSecQty]
,[FSecCommitQty]
,[FSourceTranType]
,[FSourceInterId]
,[FSourceBillNo]
,[FContractInterID]
,[FContractEntryID]
,[FContractBillNo]
,[FMRPLockFlag]
,[FMRPTrackFlag]
,[FOrderCommitQty]
,[FOrderSecCommitQty]
,[FAuxQtyInvoice]
,[FCommitInstall]
,[FAuxCommitInstall]
,[FMrpClosed]
,[FAuxInCommitQty]
,[FInCommitQty]
,[FSecInCommitQty]
,[FApplyCommitQty]
,[FAuxApplyCommitQty]
,[FSecApplyCommitQty]
,[FEvaluated]
,[FPackUnitID]
,[FPackCount]
,[FPackType]
,[FMapID]
,[FGoodsDesc]
,[FAmountAfterDiscount]
,[FInformCommitQty]
,[FAuxInformCommitQty]
,[FSecInformCommitQty]
,[FPurCommitQty]
,[FAuxPurCommitQty]
,[FSecPurCommitQty]
,[FMrpAutoClosed]
,[FSecStockQty]
,[FSecInvoiceQty]
,[FSecCommitInstall]
,[FPlanMode]
,[FMTONo]
,[FOrderBillNo]
,[FOrderEntryID]
,[FDiffQtyClosed]
,[FBOMCategory]
,[FOrderBOMStatus]
,[FOrderBOMInterID]
,[FIsAltered]
,[FAmountExceptDisCount]
,[FAllAmountExceptDisCount]
,[FEntryDisCount]
,[FCommitAmt]
,[FCommitAmtTax]
,[FCommitTax]
,[FIsAPS]
,[FMeetDelivery]
,[FOrderBOMEntryID]
,[FOutSourceInterID]
,[FOutSourceEntryID]
,[FOutSourceTranType]
,[FEntryAccessoryCount]
,[FCloseEntryUser]
,[FCloseEntryDate]
,[FCloseEntryCauses]
,[FEntrySelfS0166]
,[FEntrySelfS0167]
,[FEntrySelfS0168]
,[FEntrySelfS0169]
,[FEntrySelfS0171]
,[FEntrySelfS0173]
,[FEntrySelfS0170]
,[FEntrySelfS0172]
,[FEntrySelfS0176]
,[FEntrySelfS0174]
,[FEntrySelfS0175]
,[FEntrySelfS0178])
SELECT [FBrNO]
,@FInterID_new
,[FEntryID]
,(select top 1 a.FItemIDsm from jb2023.dbo. t_ICItem_SM_compare a where a.FDeleted=0 and a.FItemID=SEOrderEntry.FItemID) --[FItemID]
,[FQty]
,[FCommitQty]
,[FPrice]
,[FAmount]
,[FTaxRate]
,[FTaxAmount]
,[FTax]
,[FDiscount]
,[FNote]
,[FDate]
,[FDiscountAmount]
,[FInvoiceQty]
,[FBCommitQty]
,[FTranLeadTime]
,[FATPDeduct]
,[FCostObjectID]
,(select top 1 a.FSaleUnitIDSM from jb2023.dbo. t_ICItem_SM_compare a where a.FDeleted=0 and a.FItemID=SEOrderEntry.FItemID) -- ,[FUnitID]
,[FAuxBCommitQty]
,[FAuxCommitQty]
,[FAuxInvoiceQty]
,[FAuxPrice]
,[FAuxQty]
,[FUniDiscount]
,[FFinalAmount]
,[FSourceEntryID]
,[FHaveMrp]
,[FStockQty]
,[FAuxStockQty]
,[FBatchNo]
,[FCESS]
,[FAdviceConsignDate]
,[FBomInterID]
,[FMapNumber]
,[FMapName]
,[FLockFlag]
,[FInForeCast]
,[FAllAmount]
,[FAllStdAmount]
,[FAuxPropID]
,[FAuxPriceDiscount]
,[FPriceDiscount]
,[FQtyInvoice]
,[FQtyInvoiceBase]
,[FTaxAmt]
,[FAuxTaxPrice]
,[FTaxPrice]
,[FReceiveAmountFor_Commit]
,[FReceiveAmount_Commit]
,[FSecCoefficient]
,[FSecQty]
,[FSecCommitQty]
,[FSourceTranType]
,[FSourceInterId]
,[FSourceBillNo]
,[FContractInterID]
,[FContractEntryID]
,[FContractBillNo]
,[FMRPLockFlag]
,[FMRPTrackFlag]
,[FOrderCommitQty]
,[FOrderSecCommitQty]
,[FAuxQtyInvoice]
,[FCommitInstall]
,[FAuxCommitInstall]
,[FMrpClosed]
,[FAuxInCommitQty]
,[FInCommitQty]
,[FSecInCommitQty]
,[FApplyCommitQty]
,[FAuxApplyCommitQty]
,[FSecApplyCommitQty]
,[FEvaluated]
,[FPackUnitID]
,[FPackCount]
,[FPackType]
, (select top 1 smFMapID from jb2023.dbo.Itemmap where jbFMapID=FMapID) --[FMapID]--
,[FGoodsDesc]
,[FAmountAfterDiscount]
,[FInformCommitQty]
,[FAuxInformCommitQty]
,[FSecInformCommitQty]
,[FPurCommitQty]
,[FAuxPurCommitQty]
,[FSecPurCommitQty]
,[FMrpAutoClosed]
,[FSecStockQty]
,[FSecInvoiceQty]
,[FSecCommitInstall]
,[FPlanMode]
,[FMTONo]
,[FOrderBillNo]
,[FOrderEntryID]
,[FDiffQtyClosed]
,[FBOMCategory]
,[FOrderBOMStatus]
,[FOrderBOMInterID]
,[FIsAltered]
,[FAmountExceptDisCount]
,[FAllAmountExceptDisCount]
,[FEntryDisCount]
,[FCommitAmt]
,[FCommitAmtTax]
,[FCommitTax]
,[FIsAPS]
,[FMeetDelivery]
,[FOrderBOMEntryID]
,[FOutSourceInterID]
,[FOutSourceEntryID]
,[FOutSourceTranType]
,[FEntryAccessoryCount]
,[FCloseEntryUser]
,[FCloseEntryDate]
,[FCloseEntryCauses]
,[FEntrySelfS0166]
,[FEntrySelfS0167]
,[FEntrySelfS0168]
,[FEntrySelfS0169]
,[FEntrySelfS0171]
,[FEntrySelfS0173]
,[FEntrySelfS0170]
,[FEntrySelfS0172]
,[FEntrySelfS0176]
,[FEntrySelfS0174]
,[FEntrySelfS0175]
,[FEntrySelfS0178]
FROM SEOrderEntry where FInterID=@FInterID_old
--- 外销订单文本条款表
INSERT INTO [summit2023].[dbo].[SEOrderClause]
([FInterID]
,[FEntryID]
,[FClauseContent]
,[FClauseTypeName])
SELECT [FInterID]
,@FInterID_new
,[FClauseContent]
,[FClauseTypeName]
FROM SEOrderClause where FInterID=@FInterID_old
--外销订单扩展文本
INSERT INTO [summit2023].[dbo].[SEOrderExp]
([FInterID]
,[FEntryID]
,[FVersion]
,[FValidDate]
,[FAllAmount]
,[FTradeStyleID]
,[FMidCustID]
,[FInlandSupplyID]
,[FCustOrderNo]
,[FLastModyDate]
,[FTradeTermID]
,[FTradeClause]
,[FPayTerms]
,[FPayTime]
,[FPayTimeDesc]
,[FTradeCountryID]
,[FDestCountryID]
,[FTransStyleID]
,[FFreightAgentID]
,[FMoreLessRate]
,[FShipPort]
,[FTransShipPort]
,[FDestPort]
,[FIsPartial]
,[FIsTransship]
,[FShipTime]
,[FShipTimeDesc]
,[FCommRate]
,[FCommAmount]
,[FFurtCommRate]
,[FFurtCommAmount]
,[FInsureTypeID]
,[FInsurePlusRate]
,[FGoodsDesc]
,[FGoodsMark]
,[FClauseRemark]
,[FCustName]
,[FShortCustName]
,[FCustNameEn]
,[FAddress]
,[FAddressEn]
,[FPhone]
,[FFax]
,[FPostalCode]
,[FContact]
,[FEmail]
,[FCorpName]
,[FShortCorpName]
,[FCorpNameEn]
,[FCorpAddress]
,[FCorpAddressEn]
,[FCorpPhone]
,[FCorpFax]
,[FCorpPostalCode]
,[FCorpEmail]
,[FGoodsMark_Tag])
SELECT @FInterID_new
,[FEntryID]
,[FVersion]
,[FValidDate]
,[FAllAmount]
,[FTradeStyleID]
,[FMidCustID]
,[FInlandSupplyID]
,[FCustOrderNo]
,[FLastModyDate]
,[FTradeTermID]
,[FTradeClause]
,[FPayTerms]
,[FPayTime]
,[FPayTimeDesc]
,[FTradeCountryID]
,[FDestCountryID]
,[FTransStyleID]
,[FFreightAgentID]
,[FMoreLessRate]
,[FShipPort]
,[FTransShipPort]
,[FDestPort]
,[FIsPartial]
,[FIsTransship]
,[FShipTime]
,[FShipTimeDesc]
,[FCommRate]
,[FCommAmount]
,[FFurtCommRate]
,[FFurtCommAmount]
,[FInsureTypeID]
,[FInsurePlusRate]
,[FGoodsDesc]
,[FGoodsMark]
,[FClauseRemark]
,[FCustName]
,[FShortCustName]
,[FCustNameEn]
,[FAddress]
,[FAddressEn]
,[FPhone]
,[FFax]
,[FPostalCode]
,[FContact]
,[FEmail]
,[FCorpName]
,[FShortCorpName]
,[FCorpNameEn]
,[FCorpAddress]
,[FCorpAddressEn]
,[FCorpPhone]
,[FCorpFax]
,[FCorpPostalCode]
,[FCorpEmail]
,[FGoodsMark_Tag]
FROM SEOrderExp where FInterID=@FInterID_old
---审核文件
INSERT INTO [summit2023].[dbo].[ICClassCheckRecords1007100]
([FPage]
,[FBillID]
,[FBillEntryID]
,[FBillNo]
,[FBillEntryIndex]
,[FCheckLevel]
,[FCheckLevelTo]
,[FMode]
,[FCheckMan]
,[FCheckIdea]
,[FCheckDate]
,[FDescriptions])
select [FPage]
,@FInterID_new
,[FBillEntryID]
,'PS'+[FBillNo]
,[FBillEntryIndex]
,[FCheckLevel]
,[FCheckLevelTo]
,[FMode]
,[FCheckMan]
,[FCheckIdea]
,[FCheckDate]
,[FDescriptions] from ICClassCheckRecords1007100 where FBillID=@FInterID_old
--把对方账套的FInterID记录下来
update seorder set Finteridexp=@FInterID_new where FBillNo=@strFBillNo
---------------------根据客户订单号来转移采购订单--------------
/* 客户订单号来找采购订单缺点就是
1、没有了上下游单据关联（外销订单的FChildren自动要赋值为0 ）
2、出现有外销订单客户订单号相同的情况下，会出现转移了多条采购订单，会多移了采购订单数据，不过问题不大。要做限制不能重复转移，因为下一张相同客户订单号的，也会找到这些采购订单
3、最好的解决方案是能查出单据对应的下游采购订单，来转移(目前还没找到关联字段--20230705)
*/
DECLARE @FInterID_PO_new INT,@FInterID_PO_old INT,@FBillNo_PO_old varchar(255)
--定义游标
declare POOrdertemp_cursor cursor for select FInterID,FBillNo from POOrder where FCancellation=0 and FHeadSelfP0260= @FCustOrderNo and (Finteridexp is null or Finteridexp='' )
--打开游标
open POOrdertemp_cursor
--取值
fetch next from POOrdertemp_cursor into @FInterID_PO_old,@FBillNo_PO_old
while(@@FETCH_STATUS=0)
begin
exec summit2023.dbo. GetICMaxNum 'POOrder', @FInterID_PO_new output
-- 转移采购主表
INSERT INTO [summit2023].[dbo].[POOrder]
([FBrNo]
,[FTranType]
,[FInterID]
,[FBillNo]
,[FSupplyID]
,[FDate]
,[FEmpID]
,[FDeptID]
,[FCurrencyID]
,[FCheckerID]
,[FBillerID]
,[FMangerID]
,[FClosed]
,[FTranStatus]
,[FExchangeRate]
,[FStatus]
,[FCancellation]
,[FPOStyle]
,[FMultiCheckLevel1]
,[FMultiCheckLevel2]
,[FMultiCheckLevel3]
,[FMultiCheckLevel4]
,[FMultiCheckLevel5]
,[FMultiCheckLevel6]
,[FMultiCheckDate1]
,[FMultiCheckDate2]
,[FMultiCheckDate3]
,[FMultiCheckDate4]
,[FMultiCheckDate5]
,[FMultiCheckDate6]
,[FCurCheckLevel]
,[FRelateBrID]
,[FOrderAffirm]
,[FCashDiscount]
,[FCheckDate]
,[FExplanation]
,[FFetchAdd]
,[FSettleDate]
,[FSettleID]
,[FSelTranType]
,[FChildren]
,[FBrID]
,[FPOOrdBillNo]
,[FAreaPS]
,[FClassTypeID]
,[FTotalCostFor]
,[FlastModyDate]
,[FManageType]
,[FSysStatus]
,[FVersionNo]
,[FChangeDate]
,[FChangeCauses]
,[FChangeMark]
,[FChangeUser]
,[FValidaterName]
,[FConsignee]
,[FPrintCount]
,[FExchangeRateType]
,[FDeliveryPlace]
,[FAccessoryCount]
,[FPOMode]
,[FPlanCategory]
,[FLastAlterBillNo]
,[FCloseUser]
,[FCloseDate]
,[FCloseCauses]
,[FSendStatus]
,[FEnterpriseID]
,[FHeadSelfP0259]
,[FHeadSelfP0261]
,[FHeadSelfP0255]
,[FHeadSelfP0256]
,[FHeadSelfP0257]
,[FHeadSelfP0258]
,[FHeadSelfP0260]
,[FHeadSelfP0262]
,[FHeadSelfP0270]
,[FHeadSelfP0271]
,[FDisCount]
,[FDisCountRate]
,[FCommitDisCount]
,[FHeadSelfP0272]
,[FHeadSelfP0273])
SELECT [FBrNo]
,[FTranType]
,@FInterID_PO_new
,'PS'+[FBillNo]
,(select top 1 FItemID from summit2023.dbo. t_Supplier where FDeleted=0 and FNumber in ( select FNumber from jb2023.dbo. t_Supplier jbt where jbt.FItemID=FSupplyID ))--[FSupplyID]
,[FDate]
,[FEmpID]
,[FDeptID]
,[FCurrencyID]
,[FCheckerID]
,[FBillerID]
,[FMangerID]
,[FClosed]
,[FTranStatus]
,[FExchangeRate]
,[FStatus]
,[FCancellation]
,[FPOStyle]
,[FMultiCheckLevel1]
,[FMultiCheckLevel2]
,[FMultiCheckLevel3]
,[FMultiCheckLevel4]
,[FMultiCheckLevel5]
,[FMultiCheckLevel6]
,[FMultiCheckDate1]
,[FMultiCheckDate2]
,[FMultiCheckDate3]
,[FMultiCheckDate4]
,[FMultiCheckDate5]
,[FMultiCheckDate6]
,[FCurCheckLevel]
,[FRelateBrID]
,[FOrderAffirm]
,[FCashDiscount]
,[FCheckDate]
,[FExplanation]
,[FFetchAdd]
,[FSettleDate]
,[FSettleID]
,[FSelTranType]
,0 -- ,[FChildren]
,[FBrID]
,[FPOOrdBillNo]
,[FAreaPS]
,[FClassTypeID]
,[FTotalCostFor]
,[FlastModyDate]
,[FManageType]
,[FSysStatus]
,[FVersionNo]
,[FChangeDate]
,[FChangeCauses]
,[FChangeMark]
,[FChangeUser]
,[FValidaterName]
,[FConsignee]
,[FPrintCount]
,[FExchangeRateType]
,[FDeliveryPlace]
,[FAccessoryCount]
,[FPOMode]
,[FPlanCategory]
,[FLastAlterBillNo]
,[FCloseUser]
,[FCloseDate]
,[FCloseCauses]
,[FSendStatus]
,[FEnterpriseID]
,[FHeadSelfP0259]
,[FHeadSelfP0261]
,[FHeadSelfP0255]
,[FHeadSelfP0256]
,[FHeadSelfP0257]
,[FHeadSelfP0258]
,[FHeadSelfP0260]
,[FHeadSelfP0262]
,[FHeadSelfP0270]
,[FHeadSelfP0271]
,[FDisCount]
,[FDisCountRate]
,[FCommitDisCount]
,case FHeadSelfP0272 when 36751 then 36736 when 36752 then 36737 else null end --,[FHeadSelfP0272]
,[FHeadSelfP0274]
FROM [POOrder] where FCancellation=0 and FInterID= @FInterID_PO_old
--转移采购明细表
INSERT INTO [summit2023].[dbo].[POOrderEntry]
([FBrNo]
,[FInterID]
,[FEntryID]
,[FItemID]
,[FQty]
,[FCommitQty]
,[FDate]
,[FPrice]
,[FAmount]
,[FTaxRate]
,[FTax]
,[FTaxAmount]
,[FNote]
,[FUnitID]
,[FAuxCommitQty]
,[FAuxPrice]
,[FAuxQty]
,[FSourceEntryID]
,[FCess]
,[FStockQty]
,[FAuxStockQty]
,[FMapNumber]
,[FMapName]
,[FAllAmount]
,[FAuxPropID]
,[FAuxPriceDiscount]
,[FPriceDiscount]
,[FQtyInvoice]
,[FQtyInvoiceBase]
,[FAuxTaxPrice]
,[FTaxPrice]
,[FReceiveAmountFor_Commit]
,[FReceiveAmount_Commit]
,[FSecCoefficient]
,[FSecQty]
,[FSecCommitQty]
,[FSourceTranType]
,[FSourceInterId]
,[FSourceBillNo]
,[FContractInterID]
,[FContractEntryID]
,[FContractBillNo]
,[FMRPLockFlag]
,[FAuxQtyInvoice]
,[FMrpClosed]
,[FMapID]
,[FSProducingAreaID]
,[FAmtDiscount]
,[FCheckAmount]
,[FMrpAutoClosed]
,[FPayApplyAmountFor_Commit]
,[FPayApplyAmount_Commit]
,[FSecStockQty]
,[FSecInvoiceQty]
,[FPlanMode]
,[FMTONo]
,[FDescount]
,[FSupConfirm]
,[FSupConDate]
,[FSupConQty]
,[FSupConMem]
,[FSupConFetchDate]
,[FSupConfirmor]
,[FQualityRptBillID]
,[FLockByAlter]
,[FDeliveryQty]
,[FAuxDeliveryQty]
,[FSecDeliveryQty]
,[FRejectRefuseNote]
,[FRefuseNote]
,[FLockBySupplier]
,[FEntryAccessoryCount]
,[FPRInterID]
,[FPREntryID]
,[FAuxReceiptQty]
,[FReceiptQty]
,[FAuxReturnQty]
,[FReturnQty]
,[FCheckMethod]
,[FIsCheck]
,[FAmountExceptDisCount]
,[FAllAmountExceptDisCount]
,[FEntryDisCount]
,[FCommitAmt]
,[FCommitAmtTax]
,[FCommitTax]
,[FPayReqPayAmountFor]
,[FCloseEntryUser]
,[FCloseEntryDate]
,[FCloseEntryCauses]
,[FOutSourceInterID]
,[FOutSourceEntryID]
,[FOutSourceTranType]
,[FBatchNo]
,[FEntrySelfP0267]
,[FEntrySelfP0268]
,[FEntrySelfP0269])
SELECT [FBrNo]
,@FInterID_PO_new
,[FEntryID]
,[FItemID]
,[FQty]
,[FCommitQty]
,[FDate]
,[FPrice]
,[FAmount]
,[FTaxRate]
,[FTax]
,[FTaxAmount]
,[FNote]
,(select top 1 a.FItemIDsm from jb2023.dbo. t_ICItem_SM_compare a where a.FDeleted=0 and a.FItemID=POOrderEntry.FItemID)-- ,[FUnitID]
,[FAuxCommitQty]
,[FAuxPrice]
,[FAuxQty]
,0 --,[FSourceEntryID]
,[FCess]
,[FStockQty]
,[FAuxStockQty]
,[FMapNumber]
,[FMapName]
,[FAllAmount]
,[FAuxPropID]
,[FAuxPriceDiscount]
,[FPriceDiscount]
,[FQtyInvoice]
,[FQtyInvoiceBase]
,[FAuxTaxPrice]
,[FTaxPrice]
,[FReceiveAmountFor_Commit]
,[FReceiveAmount_Commit]
,[FSecCoefficient]
,[FSecQty]
,[FSecCommitQty]
,0-- ,[FSourceTranType]
,0--,[FSourceInterId]
,''--,[FSourceBillNo]
,[FContractInterID]
,[FContractEntryID]
,[FContractBillNo]
,[FMRPLockFlag]
,[FAuxQtyInvoice]
,[FMrpClosed]
,[FMapID]
,[FSProducingAreaID]
,[FAmtDiscount]
,[FCheckAmount]
,[FMrpAutoClosed]
,[FPayApplyAmountFor_Commit]
,[FPayApplyAmount_Commit]
,[FSecStockQty]
,[FSecInvoiceQty]
,[FPlanMode]
,[FMTONo]
,[FDescount]
,[FSupConfirm]
,[FSupConDate]
,[FSupConQty]
,[FSupConMem]
,[FSupConFetchDate]
,[FSupConfirmor]
,[FQualityRptBillID]
,[FLockByAlter]
,[FDeliveryQty]
,[FAuxDeliveryQty]
,[FSecDeliveryQty]
,[FRejectRefuseNote]
,[FRefuseNote]
,[FLockBySupplier]
,[FEntryAccessoryCount]
,[FPRInterID]
,[FPREntryID]
,[FAuxReceiptQty]
,[FReceiptQty]
,[FAuxReturnQty]
,[FReturnQty]
,[FCheckMethod]
,[FIsCheck]
,[FAmountExceptDisCount]
,[FAllAmountExceptDisCount]
,[FEntryDisCount]
,[FCommitAmt]
,[FCommitAmtTax]
,[FCommitTax]
,[FPayReqPayAmountFor]
,[FCloseEntryUser]
,[FCloseEntryDate]
,[FCloseEntryCauses]
,[FOutSourceInterID]
,[FOutSourceEntryID]
,[FOutSourceTranType]
,[FBatchNo]
,[FEntrySelfP0267]
,[FEntrySelfP0268]
,[FEntrySelfP0269]
FROM POOrderEntry where FInterID= @FInterID_PO_old
update POOrder set Finteridexp=@FInterID_PO_new where FInterID= @FInterID_PO_old
fetch next from POOrdertemp_cursor into @FInterID_PO_old,@FBillNo_PO_old
end
close POOrdertemp_cursor
deallocate POOrdertemp_cursor;
----------------------------根据客户订单号来修改力邦账套的销售订单的客户名称--------------------------
update seorder set FPOOldBillNO=
(select top 1 stuff(( SELECT ',' + FBillNo FROM POOrder WHERE FCancellation=0 and FHeadSelfP0260= @FCustOrderNo FOR XML path('') ), 1, 1, '')FROM POOrder WHERE FCancellation=0 and FHeadSelfP0260= @FCustOrderNo) ,
FSELBOldBillNO=
(select top 1 stuff(( SELECT ',' + FBillNo FROM AIS20230113160504.dbo.SEOrder where FBillerID=16444 and FCancellation=0 and FClosed=0 and FHeadSelfS0162=@FCustOrderNo and FCustID=294 FOR XML path('') ), 1, 1, '')FROM lbtest211214.dbo.SEOrder where FBillerID=16444 and FCancellation=0 and FClosed=0 and FHeadSelfS0162=@FCustOrderNo and FCustID=294)
where FInterID=@FInterID_old
--294 敬邦 --6754 时美特
update AIS20230113160504.dbo.SEOrder set FCustID=6754 ,FCustID_old=294 where FCancellation=0 and FClosed=0 and FHeadSelfS0162=@FCustOrderNo and FCustID=294
set @strReMsg ='操作成功，目标账套单据编号是'+@FBillNo_new;
-- 有多少张下游单据
--select FChildren from SEOrder WHERE FInterID = 1294
--select FDetailID,FInterID, FEntryID,* from SEOrderEntry where FInterID in ( select FInterID from SEOrder where FBillNo= 'PI23070124')
-- select FSourceBillNo, FSourceEntryID, FSourceInterId, FSourceTranType,* from POOrderEntry where FInterID in ( select FInterID from poOrder where FBillNo= 'POSE230736275')
-- FHeadSelfP0272 内 36751 外 36752 //FHeadSelfP0274 --验货要求
-- select selectFHeadSelfP0260, FHeadSelfP0272, * from POOrder where FBillNo='PO230736608'
-- FHeadSelfP0272 内 36736 外36737 //FHeadSelfP0273 --验货要求 FHeadSelfP0260 客户订单号
-- select FHeadSelfP0260, FHeadSelfP0272, * from summit2023.dbo.POOrder where FBillNo='POSE230736276' order by FDate desc
-- select * from t_TableDescription where FTableName='POOrderEntry' FDescription like '%采购订单%';
-- select * from t_FieldDescription where FTableID=200005 and FFNDFieldName like '%FEntrySelfP%'
--select FSourceBillNo, FSourceEntryID, FSourceInterId, FSourceTranType, * from POOrderEntry where FSourceInterId=0
/*
update jb2023.dbo.seorder set FPOOldInterID=null , FPOOldBillNO=null , Finteridexp= NULL ,FSELBOldBillNO=null
update jb2023.dbo.poOrder set Finteridexp= NULL
update summit2023.dbo. seorder set FCheckerID=0 where FBillNo like 'PS%'
delete summit2023.dbo. seorder where FBillNo like 'PS%'
update summit2023.dbo. poorder set FCheckerID=0 where FBillNo like 'PS%'
delete summit2023.dbo. poorder where FBillNo like 'PS%'
*/
end
GO
Create view Itemmap as
select a.FEntryID as jbFMapID, b.FEntryID as smFMapID ,a.FMapNumber ,a.FMapName from jb2023.dbo. ICItemMapping a
join summit2023 .dbo. ICItemMapping b on a.FMapNumber=b.FMapNumber -- and a.FItemID= b.FItemID
join jb2023.dbo. t_ICItem_SM_compare d on a.FItemID=d.FItemID and d.FItemIDsm=b.FItemID
join jb2023.dbo. Custmap c on a.FCompanyID=c.jbFItemID and b.FCompanyID=c.smFItemID
Create view Custmap as
select a.FNumber as jbFNumber, a.FItemID as jbFItemID, b.FNumber as smFNumber, b.FItemID as smFItemID from jb2023.dbo.t_Organization a join summit2023.dbo.t_Organization b on a.FNumber=b.FNumber
select * into seorder20231128 from seorder where FBillNo in('PSPI23110557','PSPI23100532','PSPI23100548','PSPI23100529','PSPI23110564')
select * into SEOrderEntry20231128 from SEOrderEntry where FInterID in (1431,1421,1430,1419,1433)
select * into SEOrderClause20231128 from SEOrderClause where FInterID in (1431,1421,1430,1419,1433)
select * into SEOrderExp20231128 from SEOrderExp where FInterID in (1431,1421,1430,1419,1433)
select * into ICClassCheckRecords100710020231128 from ICClassCheckRecords1007100 where FBillID in (1431,1421,1430,1419,1433)
select * into jb2023.dbo. seorder20231128 from jb2023.dbo. seorder where FBillNo in('PI23110557','PI23100532','PI23100548','PI23100529','PI23110564')
select * into POOrder20231128 from POOrder where FCancellation=0 and FHeadSelfP0260 in( select FCustOrderNo from SEOrderExp where SEOrderExp. FInterID in (1431,1421,1430,1419,1433))
select * into POOrderEntry20231128 from POOrderEntry where FInterID in( select FInterID from POOrder where FCancellation=0 and FHeadSelfP0260 in( select FCustOrderNo from SEOrderExp where SEOrderExp. FInterID in (1431,1421,1430,1419,1433))
)
select * from jb2023.dbo.POOrder where Finteridexp in ( select FInterID from POOrder where FCancellation=0 and FHeadSelfP0260 in( select FCustOrderNo from SEOrderExp where SEOrderExp. FInterID in (1431,1421,1430,1419,1433))
)
update seorder set FCheckerID=0 where FBillNo in('PSPI23110557','PSPI23100532','PSPI23100548','PSPI23100529','PSPI23110564')
delete from seorder where FBillNo in('PSPI23110557','PSPI23100532','PSPI23100548','PSPI23100529','PSPI23110564')
delete from SEOrderEntry where FInterID in (1431,1421,1430,1419,1433)
delete from SEOrderClause where FInterID in (1431,1421,1430,1419,1433)
delete from SEOrderExp where FInterID in (1431,1421,1430,1419,1433)
delete from ICClassCheckRecords1007100 where FBillID in (1431,1421,1430,1419,1433)
update jb2023.dbo. seorder set Finteridexp='' where FBillNo in('PI23110557','PI23100532','PI23100548','PI23100529','PI23110564')
update POOrder set FCheckerID=0 where FCancellation=0 and FHeadSelfP0260 in( select FCustOrderNo from SEOrderExp where SEOrderExp. FInterID in (1431,1421,1430,1419,1433))
delete from POOrder where FCancellation=0 and FHeadSelfP0260 in( select FCustOrderNo from SEOrderExp where SEOrderExp. FInterID in (1431,1421,1430,1419,1433))
delete from POOrderEntry where FInterID in( select FInterID from POOrder where FCancellation=0 and FHeadSelfP0260 in( select FCustOrderNo from SEOrderExp where SEOrderExp. FInterID in (1431,1421,1430,1419,1433))
)
update jb2023.dbo.POOrder set Finteridexp='' where FInterID=2518
扫一扫
4805
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
