金蝶K/3 跟踪语句_业务单据
跟踪语句_业务单据_BOM
select * from t_TableDescription where Ftablename like '%ICBOM%' select * from t_FieldDescription where FTableID=250000 order by FFieldName select * from t_FieldDescription where FTableID=250001 order by FFieldName SELECT FErpClsID FROM t_ICItem WHERE FItemID= 39420 SELECT * FROM ICBomGroup WHERE FInterID=1052 declare @p2 int set @p2=1148 exec GetICMaxNum 'ICBom',@p2 output,1,16394 select @p2 INSERT INTO ICBomChild (FInterID,FEntryID,FBrNo,FItemID,FAuxPropID, FUnitID,FMaterielType,FMarshalType,FQty,FAuxQty, FBeginDay,FEndDay,FPercent,FScrap,FPositionNo, FItemSize,FItemSuite,FOperSN,FOperID,FMachinePos, FOffSetDay,FBackFlush,FStockID,FSPID,FNote, FNote1,FNote2,FNote3,FPDMImportDate,FDetailID,FCostPercentage) SELECT 1148,1,'0',42494,0, 39265,371,385,1,1, '1900-01-01','2100-01-01',100,0,'', '','',0,0,'', 0,1059,0,0,'', '','','','','{D4C30E3E-F017-4764-8C4F-7E92CE04E049}',0 INSERT INTO ICBom (FInterID,FBomNumber,FBrNo,FTranType,FCancellation, FStatus,FVersion,FUseStatus,FItemID,FUnitID, FAuxPropID,FAuxQty,FYield,FNote,FCheckID, FCheckDate,FOperatorID,FEntertime,FRoutingID,FBomType, FCustID,FParentID,FAudDate,FImpMode,FPDMImportDate, FBOMSkip,FUseDate,FPrintCount) SELECT 1148,'BOM000010','0',50,0, 0,'','1073',39420,'39249', 0,1,100,'',16394, '2018-05-02',16394,'2018-05-02',0,0, 0,1052,Null,0,Null, '1059',Null,0 UPDATE ICBom SET FOperatorID=16394,FEnterTime=Convert(Varchar(10),Getdate(),120) WHERE FInterID=1148 Update t1 set t1.FQty=cast(t1.FAuxQty as decimal(28,15)) * cast( isnull(t3.FCoefficient,1) + cast(isnull(t3.FScale,0) as float) as decimal(28,15) ) from ICBOMChild t1,t_MeasureUnit t3 Where t3.FItemID = t1.FUnitID and t1.FInterID=1148 IF EXISTS (SELECT 1 FROM ICBOM WHERE FInterId = 1148 AND FBomtype <> 0) UPDATE ICBomChild SET FBeginDay = '1900-01-01', FEndDay = '2100-01-01' WHERE FInterID = 1148 UPDATE d SET FHasChar=CASE WHEN LEN(ISNULL(CONVERT(NVARCHAR(128),s.FDetailID),''))=0 THEN 0 ELSE 1 END FROM ICBOMChild d LEFT JOIN ICPlan_BOMChildChar s ON s.FDetailID=d.FDetailID WHERE d.FInterID=1148 UPDATE ICBOMChild SET FPDMImportDate = NULL FROM ICBOM WHERE ICBOM.FInterID=ICBOMChild.FInterID AND ICBOM.FBomNumber='BOM000010' AND ICBOMChild.FPDMImportDate = ''
跟踪语句_业务单据_BOM类别
select * from t_TableDescription where FTableName='ICBOMGroup' select * from t_FieldDescription where FTableID=250002 SELECT FMaxNum FROM ICMaxNum WHERE FTableName='ICBOMGroup' INSERT INTO ICBomGroup (FInterID,FNumber,FName,FParentID,FBootID) VALUES (1039,'2','2',0,1039) UPDATE ICMaxNum SET FMaxNum=1039 WHERE FTableName='ICBOMGroup'
跟踪语句_业务单据_价格政策
select * from t_TableDescription where FDescription like '%价格%' select * from t_TableDescription where ftablename = 'ICDisPlyEntry' select * from t_TableDescription where ftablename = 'ICPrcPlyEntry ' select * from t_FieldDescription where FTableID=230036 --价格政策方案 或 价格政策表头 select FInterID From IcPrcPly Where FPlyType='PrcAsm1' And FPri=0 And FInterID<>'2' INSERT INTO IcPrcPlyEntry (FIndex,FItemID,FRelatedID,FAuxPropID,FInterID, FUnitID,FBegQty,FEndQty,FCuryID,FPriceType, FPrice,FBegDate,FEndDate,FLeadTime,FMainterID, FMaintDate,FNote,FCheckerID,FCheckDate,Fchecked,FFlagSave) Values (1,39414,42797,0,2, 39275,1,100,1,20004, 100,'2018-05-02','2100-01-01',5,16394, '2018-05-02','备注字段',16394,'2018-05-02',0,'{432EA0D1-00BB-419C-B058-1C415CB26A0E}') Update IcPrcPlyEntry Set FChecked=1 Where FFlagSave='{432EA0D1-00BB-419C-B058-1C415CB26A0E}'
跟踪语句_业务单据_外购入库
--新增 --增加非跟踪逻辑：判断是否存在上游单据，且上游单据未下推其他数据。 --如没有上游单据，则保存失败，日志预警；如已下推，则保存失败，日志预警 select * from t_TableDescription where ftablename='icstockbill' select * from t_FieldDescription where ftableid=210008 order by FFieldName declare @p2 int set @p2=1784 exec GetICMaxNum 'ICStockBill',@p2 output,1,16394 select @p2 INSERT INTO ICStockBillEntry (FInterID,FEntryID,FBrNo,FMapNumber,FMapName, FItemID,FAuxPropID,FBatchNo,FQtyMust,FQty, FUnitID,FAuxQtyMust,Fauxqty,FSecCoefficient,FSecQty, FAuxPlanPrice,FPurchasePrice,FPlanAmount,Fauxprice,FDiscountRate, FDiscountAmount,Famount,Fnote,FPurchaseAmount,FKFDate, FKFPeriod,FPeriodDate,FDCStockID,FDCSPID,FOrgBillEntryID, FSNListID,FSourceBillNo,FSourceTranType,FSourceInterId,FSourceEntryID, FContractBillNo,FContractInterID,FContractEntryID,FOrderBillNo,FOrderInterID, FOrderEntryID,FAllHookQTY,FAllHookAmount,FCurrentHookQTY,FCurrentHookAmount, FPlanMode,FMTONo,FChkPassItem,FDeliveryNoticeFID,FDeliveryNoticeEntryID, FCheckAmount,FOutSourceInterID,FOutSourceEntryID,FOutSourceTranType) SELECT 1784,1,'0','','', 42587,0,'',1,1, 39265,1,1,0,0, 0,1,0,1,0, 0,1,'',1,Null, 0,Null,42937,0,0, 0,'POORDN000003',71,1171,1, '',0,0,'POORDN000003',1171, 1,0,0,0,0, 14036,'',1058,0,0, 0,0,0,0 EXEC p_UpdateBillRelateData 1,1784,'ICStockBill','ICStockBillEntry' INSERT INTO ICStockBill (FInterID,FBillNo,FBrNo,FTranType,FCancellation, FStatus,FUpStockWhenSave,FROB,FHookStatus,Fdate, FSupplyID,FCheckDate,FFManagerID,FSManagerID,FBillerID, FPOStyle,FMultiCheckDate1,FMultiCheckDate2,FMultiCheckDate3,FMultiCheckDate4, FMultiCheckDate5,FMultiCheckDate6,FRelateBrID,FPOOrdBillNo,FOrgBillInterID, FSelTranType,FBrID,FExplanation,FDeptID,FManagerID, FEmpID,FCussentAcctID,FManageType,FPOMode,FSettleDate, FPrintCount,FPayCondition,FEnterpriseID,FSendStatus,FISUpLoad) SELECT 1784,'WIN000002','0',1,0, 0,0,1,0,'2018-04-07', 42755,Null,42842,42842,16394, 252,Null,Null,Null,Null, Null,Null,0,'',0, 71,0,'',35633,0, 42842,1001,0,36680,'2018-04-07', 0,0,0,0,1059 UPDATE ICStockBill SET FUUID=NEWID() WHERE FInterID=1784 Update t Set t.FStatus =Case When (SELECT COUNT(1) FROM POOrderEntry WHERE (FCommitQty>0 OR (ISNULL(FMRPClosed,0)=1 AND ISNULL(FMRPAutoClosed,1)=0)) AND FInterID IN(1171))=0 Then 1 When (SELECT COUNT(1) FROM POOrderEntry te WHERE (ISNULL(FMRPClosed,0)=1 OR FCommitQty >= FQty ) AND FInterID IN(1171))<(SELECT COUNT(1) FROM POOrderEntry WHERE FInterID IN(1171)) Then 2 Else 3 End ,t.FClosed =Case WHEN (SELECT COUNT(1) FROM POOrderEntry te WHERE ( FCommitQty >= FQty OR (ISNULL(te.FMRPAutoClosed,1)=0 AND ISNULL(FMRPClosed,0)=1)) AND te.FInterID IN(1171))=(SELECT COUNT(1) FROM POOrderEntry te WHERE te.FInterID IN(1171)) Then 1 Else 0 End From POOrder t WHERE t.FInterID IN(1171) Update v1 SET v1.FStatus= (CASE WHEN u1.sumqty>0 THEN (CASE WHEN u1.qty <= u1.sumqty THEN 3 ELSE 1 END) ELSE v1.FStatus END),FChildren=(CASE WHEN u1.sumqty>0 THEN 1 ELSE 0 END) From POInStock v1 inner join (select t2.FInterID,SUM(t2.fqty) AS qty, SUM(t2.fconcommitqty + t2.fcommitqty+t2.FSampleBreakQty) AS sumqty from POInStockEntry t2 inner join ICStockBillEntry t3 on t2.FInterID = t3.fsourceinterid where t3.fsourcetrantype=702 AND t3.FInterID = 1784 group by t2.FInterID) u1 on v1.FInterID = u1.FInterID IF EXISTS (SELECT 1 FROM ICBillRelations_Sale WHERE FBillType = 1 AND FBillID=1784) BEGIN UPDATE t1 SET t1.FChildren=t1.FChildren+1 FROM POOrder t1 INNER JOIN POOrderEntry t2 ON t1.FInterID=t2.FInterID INNER JOIN ICBillRelations_Sale t3 ON t3.FMultiEntryID=t2.FEntryID AND t3.FMultiInterID=t2.FInterID WHERE t3.FBillType=1 AND t3.FBillID=1784 END ELSE BEGIN UPDATE t3 SET t3.FChildren=t3.FChildren+1 FROM ICStockBill t1 INNER JOIN ICStockBillEntry t2 ON t1.FInterID=t2.FInterID INNER JOIN POOrder t3 ON t3.FTranType=t2.FSourceTranType AND t3.FInterID=t2.FSourceInterID WHERE t1.FTranType=1 AND t1.FInterID=1784 AND t2.FSourceInterID>0 END --审核 DROP TABLE #TempBill SET NOCOUNT ON CREATE TABLE #TempBill (FID INT IDENTITY (1,1),FBrNo VARCHAR(10) NOT NULL DEFAULT(''), FInterID INT NOT NULL DEFAULT(0), FEntryID INT NOT NULL DEFAULT(0), FTranType INT NOT NULL DEFAULT(0), FItemID INT NOT NULL DEFAULT(0), FBatchNo NVARCHAR(255) NOT NULL DEFAULT(''), FMTONo NVARCHAR(255) NOT NULL DEFAULT(''), FAuxPropID INT NOT NULL DEFAULT(0), FStockID INT NOT NULL DEFAULT(0), FStockPlaceID INT NOT NULL DEFAULT(0), FKFPeriod INT NOT NULL DEFAULT(0), FKFDate VARCHAR(20) NOT NULL DEFAULT(''), FSupplyID INT NOT NULL DEFAULT(0), FQty DECIMAL(28,10) NOT NULL DEFAULT(0), FSecQty DECIMAL(28,10) NOT NULL DEFAULT(0), FAmount DECIMAL(28,2) NOT NULL DEFAULT(0) ) INSERT INTO #TempBill(FBrNo,FInterID,FEntryID,FTranType,FItemID,FBatchNo,FMTONo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FSupplyID,FQty,FSecQty,FAmount) SELECT '',u1.FInterID,u1.FEntryID,1 AS FTranType,u1.FItemID,ISNULL(u1.FBatchNo,'') AS FBatchNo,ISNULL(u1.FMTONo,'') AS FMTONo, u1.FAuxPropID,ISNULL(u1.FDCStockID,0) AS FDCStockID,ISNULL(u1.FDCSPID,0) AS FDCSPID,ISNULL(u1.FKFPeriod,0) AS FKFPeriod, LEFT(ISNULL(CONVERT(VARCHAR(20),u1.FKFdate ,120),''),10) AS FKFDate,FEntrySupply, 1*u1.FQty AS FQty,1*u1.FSecQty AS FSecQty,1*u1.FAmount FROM ICStockBillEntry u1 WHERE u1.FInterID=1784 order by u1.FEntryID DROP TABLE #TempPOBill UPDATE ICStockBill SET FOrderAffirm=0 WHERE FInterID=1784 Update ICStockBill Set FCheckerID=16394,FStatus=1,FCheckDate='2018-04-07' WHERE FInterID=1784 IF EXISTS(SELECT FOrderInterID FROM ICStockBillEntry WHERE FOrderInterID>0 AND FInterID=1784) UPDATE u1 SET u1.FStockQty=u1.FStockQty+1* CAST(u2.FStockQty AS FLOAT) ,u1.FSecStockQty=u1.FSecStockQty+1* CAST(u2.FSecStockQty AS FLOAT) ,u1.FAuxStockQty=ROUND((u1.FStockQty+1* CAST(u2.FStockQty AS FLOAT))/ CAST(t3.FCoefficient AS FLOAT),t1.FQtyDecimal) FROM POOrderEntry u1 INNER JOIN (SELECT FOrderInterID,FOrderEntryID,FItemID,SUM(FQty)AS FStockQty,SUM(FSecQty)AS FSecStockQty,SUM(FAuxQty) AS FAuxStockQty FROM ICStockBillEntry WHERE FInterID=1784 GROUP BY FOrderInterID,FOrderEntryID,FItemID) u2 ON u1.FInterID=u2.FOrderInterID AND u1.FEntryID=u2.FOrderEntryID AND u1.FItemID=u2.FItemID INNER JOIN t_ICItem t1 ON u1.FItemID=t1.FItemID INNER JOIN t_MeasureUnit t3 ON u1.FUnitID=t3.FItemID --PMC这部分也不要 IF OBJECT_ID('tempdb..#tmpPMCPOOrder') IS NOT NULL EXEC('DROP TABLE #tmpPMCPOOrder') CREATE TABLE #tmpPMCPOOrder( FPMCIndex INT not null default(0) ,FOrderInterID INT not null default(0) ,FOrderEntryID INT not null default(0) ,FItemID INT not null default(0) ) INSERT INTO #tmpPMCPOOrder(FOrderInterID,FOrderEntryID,FItemID) SELECT DISTINCT FOrderInterID,FOrderEntryID,FItemID From ICStockBillEntry Where FOrderInterID > 0 And FInterID =1784 ORDER BY FOrderInterID,FOrderEntryID IF @@ROWCOUNT>0 BEGIN CREATE INDEX idx_#tmpPMCPOOrder ON #tmpPMCPOOrder(FOrderInterID,FOrderEntryID,FItemID) Update t1 Set t1.FPMCIndex = t2.FIndex FROm #tmpPMCPOOrder t1 INNER join ICPlan_PMCDetail t2 On t2.fRelTrantype=71 AND t2.FRelInterID=t1.FOrderInterID AND t1.FOrderEntryID=t2.FRelEntryID CREATE INDEX idx_#tmpPMCPOOrder_FPMCIndex ON #tmpPMCPOOrder(FPMCIndex) Update u1 Set u1.FWillInQty = u2.FQty-u2.FStockQty FROM ICPlan_PMCDetail u1 INNER JOIN POOrderEntry u2 ON U1.fRelTrantype=71 AND u1.FRelInterID=u2.FInterID AND u1.FRelEntryID=u2.FEntryID WHERE exists(select 1 from #tmpPMCPOOrder where FPMCIndex=u1.FIndex) CREATE TABLE #tmpPMCPODeliveryPlan(FWillInQty DECIMAL(23,10),FReplyQty DECIMAL(23,10),FReplyDate DATETIME,FIndex INT,FRelEntryID INT) INSERT INTO #tmpPMCPODeliveryPlan(FWillInQty,FReplyQty,FReplyDate,FIndex,FRelEntryID) SELECT PMC.FWillInQty,DP.FReplyQty,DP.FReplyDate,DP.FIndex,PMC.FRelEntryID FROM ICPlan_PMCDetail PMC INNER JOIN #tmpPMCPOOrder u3 ON PMC.fRelTrantype=71 AND PMC.FRelInterID=u3.FOrderInterID AND PMC.FRelEntryID=u3.FOrderEntryID INNER JOIN ICPlan_PMCPODeliveryPlan DP ON DP.FRelInterID=PMC.FRelInterID AND DP.FRelEntryID=PMC.FRelEntryID AND DP.FRelTranType=PMC.FRelTranType ORDER BY PMC.FRelEntryID,DP.FReplyDate DESC DECLARE @FReplyQty AS DECIMAL(23,10) DECLARE @FRelEntryID AS INT SET @FReplyQty=0 SET @FRelEntryID=0 UPDATE #tmpPMCPODeliveryPlan SET @FReplyQty=CASE WHEN @FRelEntryID <> FRelEntryID THEN FWillInQty-FReplyQty ELSE @FReplyQty-FReplyQty END, FReplyQty=CASE WHEN FReplyDate=(SELECT MIN(P.FReplyDate) FROM #tmpPMCPODeliveryPlan P WHERE P.FRelEntryID=FRelEntryID) THEN FReplyQty+@FReplyQty WHEN @FReplyQty>0 THEN FReplyQty ELSE FReplyQty+@FReplyQty END ,@FRelEntryID = FRelEntryID Delete ICPlan_PMCPODeliveryPlan WHERE FIndex IN(SELECT DISTINCT FIndex FROM #tmpPMCPODeliveryPlan WHERE FReplyQty<=0) UPDATE DP SET DP.FReplyQty=TMP.FReplyQty FROM #tmpPMCPODeliveryPlan TMP INNER JOIN ICPlan_PMCPODeliveryPlan DP ON TMP.FReplyQty>0 AND TMP.FIndex=DP.FIndex DROP TABLE #tmpPMCPODeliveryPlan DELETE u1 FROM ICPlan_PMCDetail u1 INNER JOIN POOrderEntry u2 ON U1.FRelTrantype=71 AND u1.FRelInterID=u2.FInterID AND u1.FRelEntryID=u2.FEntryID Where u2.FQty <= u2.FStockQty AND exists(select 1 from #tmpPMCPOOrder where FPMCIndex=u1.FIndex) INSERT INTO ICPlan_PMCDetail(FItemID,FUnitID,FNeedDate,FSrcTranType,FSrcInterID,FSrcEntryID,FRelTranType,FRelInterID,FRelEntryID, FParentTranType,FParentInterID,FParentEntryID, FNeedQty,FWillInQty,FBillType,FAuxPropID,FPlanCategory ) SELECT v1.FItemID,v2.FUnitID,v1.FDate,0,0,0,71,v1.FInterID,v1.FEntryID,71,v1.FInterID,v1.FEntryID ,v1.FQty AS FNeedQty,(case when v1.FQty > v1.FStockQty then v1.FQty - v1.FStockQty else 0 end ) FWillQty ,5 AS FBillType,v1.FAuxPropID,v4.FPlanCategory From POOrderEntry v1 INNER JOIN t_ICItemBase v2 ON v1.FItemID=v2.FItemID INNER JOIN #tmpPMCPOOrder v3 ON v1.FInterID=v3.FOrderInterID AND v1.FEntryID=v3.FOrderEntryID AND v1.FItemID=v3.FItemID INNER JOIN POOrder v4 ON v1.FInterID=v4.FInterID Where v1.FQty > v1.FStockQty AND NOT EXISTS(SELECT 1 FROM ICPlan_PMCDetail WITH(NOLOCK) WHERE FRelTranType=71 AND FRelInterID=v1.FInterID AND FRelEntryID=v1.FEntryID) END DROP TABLE #tmpPMCPOOrder UPDATE p1 SET p1.FMrpClosed=CASE WHEN ISNULL(p1.FMRPAutoClosed,1)=1 THEN (CASE WHEN p1.FStockQty<p1.FQty THEN 0 ELSE 1 END) ELSE p1.FMrpClosed END FROM POOrderEntry p1 INNER JOIN ICStockBillEntry u1 ON u1.FOrderInterID=p1.FInterID AND u1.FOrderEntryID=p1.FEntryID WHERE u1.FInterID=1784 Update t Set t.FStatus =Case When (SELECT COUNT(1) FROM POOrderEntry WHERE (FCommitQty>0 OR (ISNULL(FMRPClosed,0)=1 AND ISNULL(FMRPAutoClosed,1)=0)) AND FInterID IN(1171))=0 Then 1 When (SELECT COUNT(1) FROM POOrderEntry te WHERE (ISNULL(FMRPClosed,0)=1 OR FCommitQty >= FQty ) AND FInterID IN(1171))<(SELECT COUNT(1) FROM POOrderEntry WHERE FInterID IN(1171)) Then 2 Else 3 End ,t.FClosed =Case WHEN (SELECT COUNT(1) FROM POOrderEntry te WHERE ( FCommitQty >= FQty OR (ISNULL(te.FMRPAutoClosed,1)=0 AND ISNULL(FMRPClosed,0)=1)) AND te.FInterID IN(1171))=(SELECT COUNT(1) FROM POOrderEntry te WHERE te.FInterID IN(1171)) Then 1 Else 0 End From POOrder t WHERE t.FInterID IN(1171) --供应商协同 DELETE FROM SRM_DistributeDataInfo WHERE FInterID =1171 AND FTableName ='v_ic_Poorder71' INSERT SRM_DistributeDataInfo(FInterID,FTableName,FSynDataWay) VALUES(1171,'v_ic_Poorder71',1) UPDATE p1 SET p1.FCloseEntryUser=CASE WHEN ISNULL(p1.FMRPAutoClosed,1)=1 THEN (CASE WHEN p1.FMrpClosed=1 THEN 16394 ELSE 0 END) ELSE 0 END, p1.FCloseEntryDate=CASE WHEN ISNULL(p1.FMRPAutoClosed,1)=1 THEN (CASE WHEN p1.FMrpClosed=1 THEN GETDATE() ELSE NULL END) ELSE NULL END, p1.FCloseEntryCauses=CASE WHEN ISNULL(p1.FMRPAutoClosed,1)=1 THEN (CASE WHEN p1.FMrpClosed=1 THEN '系统自动关闭' ELSE '' END) ELSE '' END FROM POOrderEntry p1 INNER JOIN ICStockBillEntry u1 ON u1.FOrderInterID=p1.FInterID AND u1.FOrderEntryID=p1.FEntryID WHERE u1.FInterID=1784 --供应商协同 DECLARE @TempOrder TABLE(FInterID INT) INSERT INTO @TempOrder(FInterID) SELECT DISTINCT t0.FInterID FROM POOrder t0 INNER JOIN ICStockBillEntry t1 ON t0.FInterID = t1.FOrderInterID and t1.FSourceTranType=71 INNER JOIN ICStockBill t2 ON t1.FInterID = t2.FInterID INNER JOIN t_Supplier t3 ON t0.FSupplyID = t3.FItemID WHERE t0.FTranType = 71 AND t0.FClassTypeID = 0 AND ISNULL(t3.FSupplierCoroutineFlag,0) = 1 AND t2.FInterID =1784 DELETE t1 FROM SRM_DistributeDataInfo t1 INNER JOIN @TempOrder t2 ON t1.FInterID = t2.FInterID WHERE FTableName = 'v_IC_POOrder71' INSERT INTO SRM_DistributeDataInfo(FInterID,FTableName,FSynDataWay) SELECT DISTINCT FInterID ,'v_IC_POOrder71',1 FROM @TempOrder DELETE @TempOrder INSERT INTO @TempOrder(FInterID) SELECT DISTINCT t0.FInterID FROM ICSubContract t0 INNER JOIN ICStockBillEntry t1 ON t0.FInterID = t1.FOrderInterID and t1.FSourceTranType=1007105 INNER JOIN ICStockBill t2 ON t1.FInterID = t2.FInterID INNER JOIN t_Supplier t3 ON t0.FSupplyID = t3.FItemID WHERE ISNULL(t3.FSupplierCoroutineFlag,0) = 1 AND t2.FInterID =1784 DELETE t1 FROM SRM_DistributeDataInfo t1 INNER JOIN @TempOrder t2 ON t1.FInterID = t2.FInterID WHERE FTableName = 'v_IC_SubContract1007105' INSERT INTO SRM_DistributeDataInfo(FInterID,FTableName,FSynDataWay) SELECT DISTINCT FInterID ,'v_IC_SubContract1007105',1 FROM @TempOrder select * from t_FieldDescription where FTableID=9 select FProperty,* from t_stock
跟踪语句_业务单据_生产任务
SELECT * FROM t_TableDescription where FDescription like '%生产任务%' select * from t_TableDescription where ftablename='T_MutiWorkCal' select * from t_FieldDescription where FTableID=470000 order by FFieldName SELECT FCharSourceItemID,* FROM t_ICItem WHERE FItemID=39420 declare @p2 int set @p2=1012 exec GetICMaxNum 'ICMO',@p2 output,1,16394 select @p2 INSERT INTO ICMO (FInterID,FBillNo,FBrNo,FTranType,FCancellation, FCheckDate,Fstatus,FMRP,FItemID,FCostObjID, FBomInterID,FRoutingID,FWorkShop,FSupplyID,FWorktypeID, FUnitID,Fauxqty,FPlanCommitDate,FPlanFinishDate,Fnote, FCommitDate,FBillerID,FOrderInterID,FParentInterID,FPPOrderInterID, FType,FSourceEntryID,FProcessPrice,FProcessFee,FPlanOrderInterID, FScheduleID,FCustID,FMultiCheckDate1,FMultiCheckDate2,FMultiCheckDate3, FMultiCheckDate4,FMultiCheckDate5,FMultiCheckDate6,FConfirmDate,FInHighLimit, FAuxInHighLimitQty,FInLowLimit,FAuxInLowLimitQty,FGMPBatchNo,FChangeTimes, FMrpLockFlag,FCloseDate,FPlanMode,FMtoNo,FPlanConfirmed, FPrintCount,FCardClosed,FHRReadyTime,FFinClosed,FFinCloseer, FFinClosedate,FStockFlag,FPlanCategory,FBomCategory,FSourceTranType, FSourceInterId,FSourceBillNo,FAddInterID,FAPSImported,FAuxPropID,FOrderBOMEntryID) SELECT 1012,'WORK000012','0',85,0, '2018-05-02',0,1052,39420,'39422', 1478,0,35635,0,55, 39249,100,'2018-05-02','2018-05-16','备注', Null,16394,0,0,0, 1054,0,0,0,0, 0,0,Null,Null,Null, Null,Null,Null,Null,0, 100,0,100,'',0, 0,Null,14036,'',0, 0,1059,0,0,0, Null,14215,'1','36820',0, 0,'',0,'0',0,0 DECLARE @FPlanOrderInterID AS INT DECLARE @FMRP AS INT SELECT @FPlanOrderInterID=ISNULL(FPlanOrderInterID,0) ,@FMRP=ISNULL(FMRP,0) FROM ICMO WHERE FInterid=1012 IF @FMRP=14094 AND ISNULL(@FPlanOrderInterID ,0)>0 BEGIN Update t_APS_ProcessTaskRelation SET FIcomNumber='WORK000012',FICMOID=1012 WHERE FMpsOrderId=@FPlanOrderInterID END update ICMO set FSourceTranType = 0, FSourceInterID = 0, FSourceBillNo = '' where FSourceTranType = 1201006 AND FMRP = 1052 AND FInterID=1012
跟踪语句_业务单据_采购发票
select * from t_TableDescription where FTableName= 'ICCreditBill' select * from t_TableDescription where fdescription like '%采购发票%' select * from t_FieldDescription where FTableID=60038 order by FFieldName select * from t_TableDescription where fdescription like '%入库%' select * from t_FieldDescription where FTableID=210009 order by FFieldName select * from t_TableDescription where ftablename like '%t_rpcontract%' --合同表（应付/应收） select * from t_FieldDescription where FTableID=50030 order by FFieldName select * from ICPurchase --专票 select FPeriod,FYear from T_PeriodDate where '2018-04-10'>=FStartDate and '2018-04-10'<=FEndDate declare @p2 int set @p2=1204 exec GetICMaxNum 'ICPurchase',@p2 output,1,16394 select @p2 INSERT INTO ICPurchaseEntry (FInterID,FEntryID,FBrNo,FMapNumber,FMapName, FItemID,FAuxPropID,FQty,FUnitID,Fauxqty, FSecCoefficient,FSecQty,Fauxprice,FAuxTaxPrice,FDiscountRate, FAuxPriceDiscount,Famount,FStdAmount,FAmtDiscount,FStdAmtDiscount, FTaxRate,FTaxAmount,FStdTaxAmount,FAmountMust,FAmountMustOld, FNoMust,FNoMustOld,FDeductTax,FDeductTaxOld,FOrgBillEntryID, FOrderPrice,FAuxOrderPrice,FClassID_SRC,FEntryID_SRC,FSourceBillNo, FSourceTranType,FSourceInterId,FSourceEntryID,FContractBillNo,FContractInterID, FContractEntryID,FOrderBillNo,FOrderInterID,FOrderEntryID,FAllHookQTY, FStdAllHookAmount,FCurrentHookQTY,FStdCurrentHookAmount,FPlanMode,FMTONo, FOrderType,FBatchNo,FItemStatementBillNO,FItemStatementEntryID,FItemStatementInterID) SELECT 1204,1,'0','','',604,0,3418,161,3418,0,0,36.504274,42.71,0,42.71,124771.61,124771.61,0,0,17,21211.17,21211.17,0,0,0,0,0,0,0,42.71,42.71,0,0,'WIN000085',1,2199,1,'',0,0,'POORD000080',1227,1,0,0,0,0,14036,'',71,'','',0,0 union all SELECT 1204,2,'0','','',609,0,890,161,890,0,0,32.435897,37.95,0,37.95,28867.95,28867.95,0,0,17,4907.55,4907.55,0,0,0,0,0,0,0,37.95,37.95,0,0,'WIN000085',1,2199,2,'',0,0,'POORD000080',1227,2,0,0,0,0,14036,'',71,'','',0,0 union all SELECT 1204,3,'0','','',609,0,140,161,140,0,0,32.435897,37.95,0,37.95,4541.03,4541.03,0,0,17,771.97,771.97,0,0,0,0,0,0,0,37.95,37.95,0,0,'WIN000085',1,2199,3,'',0,0,'POORD000080',1227,3,0,0,0,0,14036,'',71,'','',0,0 union all SELECT 1204,4,'0','','',611,0,1670,161,1670,0,0,30.820513,36.06,0,36.06,51470.26,51470.26,0,0,17,8749.94,8749.94,0,0,0,0,0,0,0,36.06,36.06,0,0,'WIN000085',1,2199,4,'',0,0,'POORD000080',1227,4,0,0,0,0,14036,'',71,'','',0,0 union all SELECT 1204,5,'0','','',611,0,468,161,468,0,0,30.820513,36.06,0,36.06,14424,14424,0,0,17,2452.08,2452.08,0,0,0,0,0,0,0,36.06,36.06,0,0,'WIN000085',1,2199,5,'',0,0,'POORD000080',1227,5,0,0,0,0,14036,'',71,'','',0,0 EXEC p_UpdateBillRelateData 75,1204,'ICPurchase','ICPurchaseEntry' INSERT INTO ICPurchase (FInterID,FBillNo,FBrNo,FTranType,FCancellation, FStatus,FROB,FClassTypeID,FSubSystemID,FYear, FPeriod,FItemClassID,FFincDate,FHookStatus,FTotalCostFor, FTotalCost,Fdate,FSupplyID,FTaxNum,FCheckDate, FDeptID,FEmpID,FBillerName,FCurrencyID,FInvStyle, FExchangeRateType,FExchangeRate,FCompactNo,Fnote,FBillerID, FPOStyle,FYearPeriod,FMultiCheckDate1,FMultiCheckDate2,FPOOrdBillNo, FMultiCheckDate3,FMultiCheckDate4,FMultiCheckDate5,FMultiCheckDate6,FPrintCount, FYtdIntRate,FAcctID,FOrgBillInterID,FHookerID,FSelTranType, FBrID,FManagerID,FCussentAcctID,FPayCondition,FSettleDate,FSysStatus) SELECT 1204,'ZPOFP000095','0',75,0, 0,1,1000004,0,2018, 4,8,'2018-04-10',0,262167.56, 262167.56,'2018-04-10',412,'',Null, 241,2188,'',1,12510, 1,1,'','',16394, 252,'',Null,Null,'', Null,Null,Null,Null,0, 0,0,0,0,1, 0,0,0,1006,'2018-05-08',0 UPDATE ICPurchase SET FSysStatus = 2 WHERE FInterID = 1204 UPDATE ICPurchase SET FUUID=NEWID() WHERE FInterID=1204 --更新采购入库单的开票数量 set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(src.fqtyinvoice,0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.fqty, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (abs(src.fqty)>abs(@fsrccommitfield_prevalue) or abs(src.fqty)>abs(@fsrccommitfield_endvalue)) then @fcheck_fail else -1 end) end, src.fqtyinvoice=@fsrccommitfield_endvalue, --基本单位开票数量 src.fauxqtyinvoice=@fsrccommitfield_endvalue/cast(t1.fcoefficient as float) --开票数量 from icstockbillentry src inner join icstockbill srchead on src.finterid=srchead.finterid inner join (select u1.fsourceinterid as fsourceinterid,u1.fsourceentryid,u1.fitemid,sum(u1.fqty) as fqty from icpurchaseentry u1 where u1.finterid=1204 group by u1.fsourceinterid,u1.fsourceentryid,u1.fitemid) dest on dest.fsourceinterid = src.finterid and dest.fitemid = src.fitemid and src.fentryid = dest.fsourceentryid inner join t_measureunit t1 on src.funitid=t1.fitemid if (isnull(@fcheck_fail,0)=-1) raiserror('可能的原因是： 1、所选单据已被其他单据关联 2、所选单据已被反审核 3、当前单据和所选单据的关联数量超过了所选单据的数量 4、所选单据已经关闭',18,18) else if exists (select 1 from icstockbill src right join (select u1.fsourceinterid as fsourceinterid,u1.fsourceentryid,u1.fitemid,sum(u1.fqty) as fqty from icpurchaseentry u1 where u1.finterid=1204 group by u1.fsourceinterid,u1.fsourceentryid,u1.fitemid) dest on dest.fsourceinterid = src.finterid where dest.fsourceinterid>0 and src.finterid is null) raiserror('所选单据已被删除',18,18) --更新采购订单的开票数量 set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(src.fqtyinvoice,0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.fqty, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.fqtyinvoice=@fsrccommitfield_endvalue, src.fauxqtyinvoice=@fsrccommitfield_endvalue/cast(t1.fcoefficient as float) from poorderentry src inner join poorder srchead on src.finterid=srchead.finterid inner join (select u1.forderinterid as fsourceinterid,u1.forderentryid,u1.fitemid,sum(u1.fqty) as fqty from icpurchaseentry u1 where u1.finterid=1204 group by u1.forderinterid,u1.forderentryid,u1.fitemid) dest on dest.fsourceinterid = src.finterid and dest.fitemid = src.fitemid and src.fentryid = dest.forderentryid inner join t_measureunit t1 on src.funitid=t1.fitemid --更新合同（应付）的开票数量 set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(abs(src.finvoiceqty_relative_base),0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.fqty, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.finvoiceqty_relative_base= case when src.fquantity_base < 0 then -1 * @fsrccommitfield_endvalue else @fsrccommitfield_endvalue end, src.finvoiceqty_relative= case when src.fquantity_base < 0 then -1 * (@fsrccommitfield_endvalue/cast(t1.fcoefficient as float)) else @fsrccommitfield_endvalue/cast(t1.fcoefficient as float) end from t_rpcontractentry src inner join t_rpcontract srchead on src.fcontractid=srchead.fcontractid inner join (select u1.fcontractinterid as fsourceinterid,u1.fcontractentryid,u1.fitemid,sum(u1.fqty) as fqty from icpurchaseentry u1 where u1.finterid=1204 group by u1.fcontractinterid,u1.fcontractentryid,u1.fitemid) dest on dest.fsourceinterid = src.fcontractid and dest.fitemid = src.fproductid and src.fentryid = dest.fcontractentryid inner join t_measureunit t1 on src.funitid=t1.fitemid where (select fclasstypeid from t_rpcontract where fcontractid=src.fcontractid)=1000020 --更新合同（应付）的发票关联金额（本位币） set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(abs(src.finvoiceamt_relative),0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.fstdamountincludetax, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.finvoiceamt_relative= case when src.famountincludetax < 0 then -1 * @fsrccommitfield_endvalue else @fsrccommitfield_endvalue end from t_rpcontractentry src inner join t_rpcontract srchead on src.fcontractid=srchead.fcontractid inner join (select u1.fcontractinterid as fsourceinterid,u1.fcontractentryid,u1.fitemid,sum(u1.fstdamountincludetax) as fstdamountincludetax from icpurchaseentry u1 where u1.finterid=1204 and not exists (select 1 from icbillrelations_purchase tr where u1.finterid = tr.fbillid and u1.fentryid=tr.fdestentryid) and not exists (select 1 from icbillrelations_purchaser tr where u1.finterid = tr.fbillid and u1.fentryid=tr.fdestentryid) group by u1.fcontractinterid,u1.fcontractentryid,u1.fitemid) dest on dest.fsourceinterid = src.fcontractid and dest.fitemid = src.fproductid and src.fentryid = dest.fcontractentryid where (select fclasstypeid from t_rpcontract where fcontractid=src.fcontractid)=1000020 --更新合同（应付）的发票关联金额 set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(abs(src.finvoiceamtfor_relative),0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.famountincludetax, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.finvoiceamtfor_relative= case when src.famountincludetax < 0 then -1 * @fsrccommitfield_endvalue else @fsrccommitfield_endvalue end from t_rpcontractentry src inner join t_rpcontract srchead on src.fcontractid=srchead.fcontractid inner join (select u1.fcontractinterid as fsourceinterid,u1.fcontractentryid,u1.fitemid,sum(u1.famountincludetax) as famountincludetax from icpurchaseentry u1 where u1.finterid=1204 and not exists (select 1 from icbillrelations_purchase tr where u1.finterid = tr.fbillid and u1.fentryid=tr.fdestentryid) and not exists (select 1 from icbillrelations_purchaser tr where u1.finterid = tr.fbillid and u1.fentryid=tr.fdestentryid) group by u1.fcontractinterid,u1.fcontractentryid,u1.fitemid) dest on dest.fsourceinterid = src.fcontractid and dest.fitemid = src.fproductid and src.fentryid = dest.fcontractentryid where (select fclasstypeid from t_rpcontract where fcontractid=src.fcontractid)=1000020 --更新合同（应付）的发票关联数量、发票关联基本数量 set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(abs(src.finvoiceqty_relative_base),0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.fqty, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.finvoiceqty_relative_base= case when src.fquantity_base < 0 then -1 * @fsrccommitfield_endvalue else @fsrccommitfield_endvalue end, src.finvoiceqty_relative= case when src.fquantity_base < 0 then -1 * (@fsrccommitfield_endvalue/cast(t1.fcoefficient as float)) else @fsrccommitfield_endvalue/cast(t1.fcoefficient as float) end from t_rpcontractentry src inner join t_rpcontract srchead on src.fcontractid=srchead.fcontractid inner join (select u1.fcontractinterid as fsourceinterid,u1.fcontractentryid,u1.fitemid,sum(u1.fqty) as fqty from icpurchaseentry u1 where u1.finterid=1204 group by u1.fcontractinterid,u1.fcontractentryid,u1.fitemid) dest on dest.fsourceinterid = src.fcontractid and dest.fitemid = src.fproductid and src.fentryid = dest.fcontractentryid inner join t_measureunit t1 on src.funitid=t1.fitemid where (select fclasstypeid from t_rpcontract where fcontractid=src.fcontractid)=1000551 --更新初始化合同（应付）的发票关联金额 set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(abs(src.finvoiceamt_relative),0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.fstdamountincludetax, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.finvoiceamt_relative= case when src.famountincludetax < 0 then -1 * @fsrccommitfield_endvalue else @fsrccommitfield_endvalue end from t_rpcontractentry src inner join t_rpcontract srchead on src.fcontractid=srchead.fcontractid inner join (select u1.fcontractinterid as fsourceinterid,u1.fcontractentryid,u1.fitemid,sum(u1.fstdamountincludetax) as fstdamountincludetax from icpurchaseentry u1 where u1.finterid=1204 and not exists (select 1 from icbillrelations_purchase tr where u1.finterid = tr.fbillid and u1.fentryid=tr.fdestentryid) and not exists (select 1 from icbillrelations_purchaser tr where u1.finterid = tr.fbillid and u1.fentryid=tr.fdestentryid) group by u1.fcontractinterid,u1.fcontractentryid,u1.fitemid) dest on dest.fsourceinterid = src.fcontractid and dest.fitemid = src.fproductid and src.fentryid = dest.fcontractentryid where (select fclasstypeid from t_rpcontract where fcontractid=src.fcontractid)=1000551 --更新初始化合同（应付）的发票关联金额（本位币） set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(abs(src.finvoiceamtfor_relative),0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.famountincludetax, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.finvoiceamtfor_relative= case when src.famountincludetax < 0 then -1 * @fsrccommitfield_endvalue else @fsrccommitfield_endvalue end from t_rpcontractentry src inner join t_rpcontract srchead on src.fcontractid=srchead.fcontractid inner join (select u1.fcontractinterid as fsourceinterid,u1.fcontractentryid,u1.fitemid,sum(u1.famountincludetax) as famountincludetax from icpurchaseentry u1 where u1.finterid=1204 and not exists (select 1 from icbillrelations_purchase tr where u1.finterid = tr.fbillid and u1.fentryid=tr.fdestentryid) and not exists (select 1 from icbillrelations_purchaser tr where u1.finterid = tr.fbillid and u1.fentryid=tr.fdestentryid) group by u1.fcontractinterid,u1.fcontractentryid,u1.fitemid) dest on dest.fsourceinterid = src.fcontractid and dest.fitemid = src.fproductid and src.fentryid = dest.fcontractentryid where (select fclasstypeid from t_rpcontract where fcontractid=src.fcontractid)=1000551 --更新合同（应付）的收款关联金额 （本位币） set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(abs(src.freceiveamount),0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.fstdamountincludetax, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.freceiveamount= case when src.famountincludetax < 0 then -1 * @fsrccommitfield_endvalue else @fsrccommitfield_endvalue end from t_rpcontractentry src inner join t_rpcontract srchead on src.fcontractid=srchead.fcontractid inner join (select u1.fcontractinterid as fsourceinterid,u1.fcontractentryid,u1.fitemid,sum(u1.fstdamountincludetax) as fstdamountincludetax from icpurchaseentry u1 where u1.finterid=1204 and not exists (select 1 from icbillrelations_purchase tr where u1.finterid = tr.fbillid and u1.fentryid=tr.fdestentryid) and not exists (select 1 from icbillrelations_purchaser tr where u1.finterid = tr.fbillid and u1.fentryid=tr.fdestentryid) and (select fpostyle from icpurchase where finterid=u1.finterid)= 251 and (select fclasstypeid from t_rpcontract where fcontractid=u1.fcontractinterid)=1000020 group by u1.fcontractinterid,u1.fcontractentryid,u1.fitemid) dest on dest.fsourceinterid = src.fcontractid and dest.fitemid = src.fproductid and src.fentryid = dest.fcontractentryid --更新合同（应付）的发票关联金额 set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(abs(src.freceiveamountfor),0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.famountincludetax, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.freceiveamountfor= case when src.famountincludetax < 0 then -1 * @fsrccommitfield_endvalue else @fsrccommitfield_endvalue end from t_rpcontractentry src inner join t_rpcontract srchead on src.fcontractid=srchead.fcontractid inner join (select u1.fcontractinterid as fsourceinterid,u1.fcontractentryid,u1.fitemid,sum(u1.famountincludetax) as famountincludetax from icpurchaseentry u1 where u1.finterid=1204 and not exists (select 1 from icbillrelations_purchase tr where u1.finterid = tr.fbillid and u1.fentryid=tr.fdestentryid) and not exists (select 1 from icbillrelations_purchaser tr where u1.finterid = tr.fbillid and u1.fentryid=tr.fdestentryid) and (select fpostyle from icpurchase where finterid=u1.finterid)= 251 and (select fclasstypeid from t_rpcontract where fcontractid=u1.fcontractinterid)=1000020 group by u1.fcontractinterid,u1.fcontractentryid,u1.fitemid) dest on dest.fsourceinterid = src.fcontractid and dest.fitemid = src.fproductid and src.fentryid = dest.fcontractentryid --更新合同（应付）的收款执行金额（本位币） set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(abs(src.fbillamt_commit),0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.fstdamountincludetax, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.fbillamt_commit= case when src.famountincludetax < 0 then -1 * @fsrccommitfield_endvalue else @fsrccommitfield_endvalue end from t_rpcontractentry src inner join t_rpcontract srchead on src.fcontractid=srchead.fcontractid inner join (select u1.fcontractinterid as fsourceinterid,u1.fcontractentryid,u1.fitemid,sum(u1.fstdamountincludetax) as fstdamountincludetax from icpurchaseentry u1 where u1.finterid=1204 and not exists (select 1 from icbillrelations_purchase tr where u1.finterid = tr.fbillid and u1.fentryid=tr.fdestentryid) and not exists (select 1 from icbillrelations_purchaser tr where u1.finterid = tr.fbillid and u1.fentryid=tr.fdestentryid) and (select fpostyle from icpurchase where finterid=u1.finterid)= 251 and (select fclasstypeid from t_rpcontract where fcontractid=u1.fcontractinterid)=1000551 group by u1.fcontractinterid,u1.fcontractentryid,u1.fitemid) dest on dest.fsourceinterid = src.fcontractid and dest.fitemid = src.fproductid and src.fentryid = dest.fcontractentryid --更新合同（应付） set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(abs(src.fbillamtfor_commit),0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.famountincludetax, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.fbillamtfor_commit= case when src.famountincludetax < 0 then -1 * @fsrccommitfield_endvalue else @fsrccommitfield_endvalue end from t_rpcontractentry src inner join t_rpcontract srchead on src.fcontractid=srchead.fcontractid inner join (select u1.fcontractinterid as fsourceinterid,u1.fcontractentryid,u1.fitemid,sum(u1.famountincludetax) as famountincludetax from icpurchaseentry u1 where u1.finterid=1204 and not exists (select 1 from icbillrelations_purchase tr where u1.finterid = tr.fbillid and u1.fentryid=tr.fdestentryid) and not exists (select 1 from icbillrelations_purchaser tr where u1.finterid = tr.fbillid and u1.fentryid=tr.fdestentryid) and (select fpostyle from icpurchase where finterid=u1.finterid)= 251 and (select fclasstypeid from t_rpcontract where fcontractid=u1.fcontractinterid)=1000551 group by u1.fcontractinterid,u1.fcontractentryid,u1.fitemid) dest on dest.fsourceinterid = src.fcontractid and dest.fitemid = src.fproductid and src.fentryid = dest.fcontractentryid --更新外购入库的辅助单位开票数量 set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(src.fsecinvoiceqty,0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.fsecqty, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.fsecinvoiceqty=@fsrccommitfield_endvalue from icstockbillentry src inner join icstockbill srchead on src.finterid=srchead.finterid inner join (select u1.fsourceinterid as fsourceinterid,u1.fsourceentryid,u1.fitemid,sum(u1.fsecqty) as fsecqty from icpurchaseentry u1 where u1.finterid=1204 group by u1.fsourceinterid,u1.fsourceentryid,u1.fitemid) dest on dest.fsourceinterid = src.finterid and dest.fitemid = src.fitemid and src.fentryid = dest.fsourceentryid --更新采购订单的辅助单位开票数量 set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(src.fsecinvoiceqty,0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.fsecqty, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.fsecinvoiceqty=@fsrccommitfield_endvalue from poorderentry src inner join poorder srchead on src.finterid=srchead.finterid inner join (select u1.forderinterid as fsourceinterid,u1.forderentryid,u1.fitemid,sum(u1.fsecqty) as fsecqty from icpurchaseentry u1 where u1.finterid=1204 group by u1.forderinterid,u1.forderentryid,u1.fitemid) dest on dest.fsourceinterid = src.finterid and dest.fitemid = src.fitemid and src.fentryid = dest.forderentryid --更新合同（应付） set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(abs(src.fsecinvoicecommitqty),0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.fsecqty, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.fsecinvoicecommitqty= case when src.fsecqty < 0 then -1 * @fsrccommitfield_endvalue else @fsrccommitfield_endvalue end from t_rpcontractentry src inner join t_rpcontract srchead on src.fcontractid=srchead.fcontractid inner join (select u1.fcontractinterid as fsourceinterid,u1.fcontractentryid,u1.fitemid,sum(u1.fsecqty) as fsecqty from icpurchaseentry u1 where u1.finterid=1204 group by u1.fcontractinterid,u1.fcontractentryid,u1.fitemid) dest on dest.fsourceinterid = src.fcontractid and dest.fitemid = src.fproductid and src.fentryid = dest.fcontractentryid where (select fclasstypeid from t_rpcontract where fcontractid=src.fcontractid)=1000020 --更新合同（应付） set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(abs(src.fsecinvoicecommitqty),0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.fsecqty, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.fsecinvoicecommitqty= case when src.fsecqty < 0 then -1 * @fsrccommitfield_endvalue else @fsrccommitfield_endvalue end from t_rpcontractentry src inner join t_rpcontract srchead on src.fcontractid=srchead.fcontractid inner join (select u1.fcontractinterid as fsourceinterid,u1.fcontractentryid,u1.fitemid,sum(u1.fsecqty) as fsecqty from icpurchaseentry u1 where u1.finterid=1204 group by u1.fcontractinterid,u1.fcontractentryid,u1.fitemid) dest on dest.fsourceinterid = src.fcontractid and dest.fitemid = src.fproductid and src.fentryid = dest.fcontractentryid where (select fclasstypeid from t_rpcontract where fcontractid=src.fcontractid)=1000551 --更新VMI物料消耗结算单表体汇总 set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(src.fqtyinvoice,0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.fqty, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (abs(src.fqty)>=abs(@fsrccommitfield_endvalue)) then @fcheck_fail else -1 end) end, src.fqtyinvoice=@fsrccommitfield_endvalue, src.fauxqtyinvoice=@fsrccommitfield_endvalue/cast(t1.fcoefficient as float) from icitemaccountcheckentrysum src inner join icitemaccountcheck srchead on src.fid=srchead.fid inner join (select u1.fitemstatementinterid as fsourceinterid,u1.fitemstatemententryid,u1.fitemid,sum(u1.fqty) as fqty from icpurchaseentry u1 where u1.finterid=1204 group by u1.fitemstatementinterid,u1.fitemstatemententryid,u1.fitemid) dest on dest.fsourceinterid = src.fid and dest.fitemid = src.fitemid and src.fentryid = dest.fitemstatemententryid inner join t_measureunit t1 on src.funitid=t1.fitemid if (isnull(@fcheck_fail,0)=-1) raiserror('可能的原因是： 1、所选单据已被其他单据关联 2、所选单据已被反审核 3、当前单据和所选单据的关联数量超过了所选单据的数量 4、所选单据已经关闭',18,18) else if exists (select 1 from icitemaccountcheck src right join (select u1.fitemstatementinterid as fsourceinterid,u1.fitemstatemententryid,u1.fitemid,sum(u1.fqty) as fqty from icpurchaseentry u1 where u1.finterid=1204 group by u1.fitemstatementinterid,u1.fitemstatemententryid,u1.fitemid) dest on dest.fsourceinterid = src.fid where dest.fsourceinterid>0 and src.fid is null) raiserror('所选单据已被删除',18,18) --更新外购入库的 set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(src.fcommitamt,0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.famount, @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.fcommitamt=@fsrccommitfield_endvalue from icstockbillentry src inner join icstockbill srchead on src.finterid=srchead.finterid inner join (select u1.fsourceinterid as fsourceinterid,u1.fsourceentryid,u1.fitemid,sum(u1.famount) as famount from icpurchaseentry u1 where u1.finterid=1204 group by u1.fsourceinterid,u1.fsourceentryid,u1.fitemid) dest on dest.fsourceinterid = src.finterid and dest.fitemid = src.fitemid and src.fentryid = dest.fsourceentryid IF EXISTS (SELECT 1 FROM ICBillRelations_Purchase WHERE FBillType = 75 AND FBillID=1204) BEGIN UPDATE t1 SET t1.FChildren=t1.FChildren+1 FROM ICStockBill t1 INNER JOIN ICStockBillEntry t2 ON t1.FInterID=t2.FInterID INNER JOIN ICBillRelations_Purchase t3 ON t3.FMultiEntryID=t2.FEntryID AND t3.FMultiInterID=t2.FInterID WHERE t3.FBillType=75 AND t3.FBillID=1204 END ELSE BEGIN UPDATE t3 SET t3.FChildren=t3.FChildren+1 FROM ICPurchase t1 INNER JOIN ICPurchaseEntry t2 ON t1.FInterID=t2.FInterID INNER JOIN ICStockBill t3 ON t3.FTranType=t2.FSourceTranType AND t3.FInterID=t2.FSourceInterID WHERE t1.FTranType=75 AND t1.FInterID=1204 AND t2.FSourceInterID>0 END --更新合同（应付） UPDATE t_rpContractEntry SET FInvoiceAmtFor_Relative = round((u.FInvoiceAmtFor_Relative +(1)*t1.FQty*t4.FPriceDiscount),isnull(t6.FScale,2)), FInvoiceAmt_Relative = round((u.FInvoiceAmt_Relative +(1)* t1.FQty * t4.FPriceDiscount * t3.FExchangeRate),isnull(t6.FScale,2)), FReceiveAmountFor =CASE WHEN t3.FPOStyle=251 THEN round((u.FReceiveAmountFor +(1)* t1.FQty*t4.FPriceDiscount),isnull(t6.FScale,2)) ELSE u.FReceiveAmountFor END, FReceiveAmount = CASE WHEN t3.FPOStyle=251 THEN round((u.FReceiveAmount +(1)* t1.FQty * t4.FPriceDiscount * t3.FExchangeRate),isnull(t6.FScale,2)) ELSE u.FReceiveAmount END FROM t_rpContractEntry u join t_rpContract v on v.FContractID = u.FContractID join ICStockBillEntry t2 on u.FEntryID = t2.FContractEntryID join ICBillRelations_Purchase t1 on t1.FMultiInterID = t2.FInterID and t1.FMultiEntryID = t2.FEntryID join ICPurchase t3 on t1.FBillID=t3.FInterID join ICPurchaseEntry t4 on t1.FBillID=t4.FInterID and t1.FDestEntryID=t4.FEntryID LEFT JOIN t_Currency t6 ON v.FCurrencyID=t6.FCurrencyID WHERE t1.FBillID =1204 --更新合同（应付） UPDATE t_rpContractEntry SET FInvoiceAmtFor_Relative = round((u.FInvoiceAmtFor_Relative -(1)* t1.FQty*t4.FPriceDiscount),isnull(t6.FScale,2)), FInvoiceAmt_Relative = round((u.FInvoiceAmt_Relative -(1)* t1.FQty * t4.FPriceDiscount * t3.FExchangeRate),isnull(t6.FScale,2)), FInvoiceQty_Relative = round((u.FInvoiceQty_Relative -(1)* t1.FQty),isnull(t7.FQtyDecimal,4)), FInvoiceQty_Relative_Base = round((u.FInvoiceQty_Relative_Base -(1)* t1.FQty*tu.FCoefficient),isnull(t7.FQtyDecimal,4)), FSecInvoiceCommitQty=round((u.FSecInvoiceCommitQty-(1)*t1.FQty/t4.FSecCoefficient),isnull(t7.FQtyDecimal,4)), FReceiveAmountFor =CASE WHEN t3.FPOStyle=251 THEN round((u.FReceiveAmountFor -(1)* t1.FQty*t4.FPriceDiscount),isnull(t6.FScale,2)) ELSE u.FReceiveAmountFor END, FReceiveAmount = CASE WHEN t3.FPOStyle=251 THEN round((u.FReceiveAmount -(1)* t1.FQty * t4.FPriceDiscount * t3.FExchangeRate),isnull(t6.FScale,2)) ELSE u.FReceiveAmount END FROM t_rpContractEntry u join t_rpContract v on v.FContractID = u.FContractID join ICStockBillEntry t2 on u.FEntryID = t2.FContractEntryID join ICBillRelations_PurchaseR t1 on t1.FMultiInterID = t2.FInterID and t1.FMultiEntryID = t2.FEntryID and t1.FSourceTranType in (1,5) join ICPurchase t3 on t1.FBillID=t3.FInterID join ICPurchaseEntry t4 on t1.FBillID=t4.FInterID and t1.FDestEntryID=t4.FEntryID left join t_MeasureUnit tu on t4.FUnitID=tu.FMeasureUnitID LEFT JOIN t_Currency t6 ON v.FCurrencyID=t6.FCurrencyID LEFT JOIN t_ICItem t7 ON u.FProductID=t7.FItemID WHERE t1.FBillID =1204 --更新采购订单 Update Src Set Src.FReceiveAmountFor_Commit=Src.FReceiveAmountFor_Commit + (IsNull(Dest.FCommitAmtFor, 0) * IsNull(Dest.FExchangeRate, 1) / IsNull(Head.FExchangeRate, 1)), Src.FReceiveAmount_Commit=Src.FReceiveAmount_Commit+ISNull(Dest.FCommitAmt,0) From POOrderEntry Src Inner Join POOrder Head On Src.FInterID=Head.FInterID Inner Join ( Select isnull(ti.FOrderInterID,t1.FOrderInterID) as FOrderInterID,isnull(ti.FOrderEntryID,t1.FOrderEntryID) as FOrderEntryID,t1.Fitemid, sum(case when ti.FOrderInterID is null then (Case t2.FTranType When 75 Then t1.FAmountIncludeTax When 76 Then t1.FAmount Else 0 End) else ti.FQty*(Case t2.FTranType When 75 Then t1.FAmountIncludeTax When 76 Then t1.FAmount Else 0 End)/t1.FQty end) as FCommitAmtFor, sum(case when ti.FOrderInterID is null then (Case t2.FTranType When 75 Then t1.FStdAmountIncludeTax When 76 Then t1.FStdAmount Else 0 End) else ti.FQty*(Case t2.FTranType When 75 Then t1.FStdAmountIncludeTax When 76 Then t1.FStdAmount Else 0 End)/t1.FQty end) as FCommitAmt, t2.FExchangeRate From ICPurchaseEntry t1 left Join ICPurchase t2 On t1.FInterID=t2.FInterID left join (select a.fbillid,a.FDestEntryID,b.FOrderInterID,b.FOrderEntryID,a.fqty from ICBillRelations_Purchase a join ICStockBillEntry b on a.FMultiInterID=b.FInterID and a.FMultiEntryID=b.fentryid ) ti on t1.FInterID=ti.FBillID and t1.FEntryID=ti.FDestEntryID Where t2.FTranType in (75,76) and t2.FPOStyle=251 and t1.FOrderInterID>0 and t2.FInterID= 1204 Group by isnull(ti.FOrderInterID,t1.FOrderInterID),isnull(ti.FOrderEntryID,t1.FOrderEntryID), t1.FItemID,t2.FTranType,t2.FExchangeRate ) Dest on Dest.FOrderInterID = Src.FInterID and Dest.Fitemid = Src.Fitemid and Src.Fentryid = Dest.FOrderEntryID --更新合同收款计划明细 update t_rpContractScheme set FReceiveAmountFor = t1.FReceiveAmountFor - t2.FReceiveAmountFor From t_rpContractScheme t1, (select FContractID,FSchemeEntryID,sum(FAmountFor) FReceiveAmountFor from t_rp_ContractPlanReceive where FBillID=1204 and FBillType = 4 group by FContractID,FSchemeEntryID ) t2 where t1.FContractID=t2.FContractID and t1.FEntryID=t2.FSchemeEntryID delete t_rp_ContractPlanReceive where FBillID = 1204 and FBillType = 4 update ICPurchase set FFincDate=FDate, FYear=2018, FPeriod=4 where FinterID=1204 update ICPurchase set FFincDate=FDate, FYear=2018, FPeriod=4 where FinterID=1204Update e SET e.FRemainAmount=(case h.FClassTypeID when 1000003 then e.FStdAmount else e.FStdAmountincludetax end), e.FRemainAmountFor=(case h.FClassTypeID when 1000003 then e.FAmount else e.FAmountincludetax end), e.FRemainQty=e.FAuxQty,e.FClassID_SRC=e.FSourceTranType from ICPurchaseEntry e join ICPurchase h on e.FinterID=h.FinterID Where e.FinterID = 1204 Update t1 set t1.FAdjustExchangeRate=case when t2.FOperator='/' then 1/t1.FExchangerate else t1.FExchangerate end, t1.FCheckStatus=(case t1.FPoStyle when 251 then 1 else 0 end),t1.FArapStatus=(case t1.FPoStyle when 251 then 4 else 0 end) from ICPurchase t1 inner join t_Currency t2 on t1.FCurrencyID=t2.FCurrencyID where t1.FinterID = 1204 --新增付款计划 insert into t_rp_plan_ap(FOrgID,FDate,FAmount,FAmountFor, FRemainAmount,FRemainAmountFor,FRP,FinterID) values(0,'2018-05-08',262167.56,262167.56,262167.56,262167.56,0,1204) --应收、应付往来表 exec sp_executesql N'INSERT INTO t_RP_Contact (FYear,FPeriod,FRP,FType,FDate,FFincDate,FNumber,FCustomer,FDepartment,FEmployee,FCurrencyID,FExchangeRate,FAmount,FAmountFor,FRemainAmount,FRemainAmountFor,FInvoiceID,FRPDate,FK3Import,FInterestRate,FBillType,finvoicetype,FItemClassID,FExplanation,FPreparer) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23,@P24,@P25)',N'@P1 int,@P2 int,@P3 smallint,@P4 smallint,@P5 datetime,@P6 datetime,@P7 varchar(255),@P8 int,@P9 int,@P10 int,@P11 int,@P12 float,@P13 money,@P14 money,@P15 money,@P16 money,@P17 int,@P18 datetime,@P19 smallint,@P20 float,@P21 int,@P22 smallint,@P23 int,@P24 varchar(255),@P25 int',2018,4,0,4,'2018-04-10 00:00:00','2018-04-10 00:00:00','ZPOFP000095',412,241,2188,1,1,$262167.5600,$262167.5600,$262167.5600,$262167.5600,1204,'2018-04-10 00:00:00',1,0,1,2,8,'',16394 update t_rp_plan_ap set FOrgID=338 where FIsinit=0 and FinterID=1204 and FRP=0 --最后交易金额 update t_supplier set FLastTradeDate='2018-04-10', FLastTradeAmount=262167.56 where FItemID=412 Update ICPurchase Set FSubSystemID=0 Where Ftrantype in(75,76) and FInterID=1204 Update v1 set v1.FOrderPrice=round(u1.FPrice *w1.FExchangeRate*(100-u1.FDiscountRate)/100,t1.FPriceDecimal) From t_ICItemCore v1 Right Join ICPurchaseEntry u1 On v1.FItemID=u1.FItemID Left Join ICPurchase w1 On u1.FInterID=w1.FInterID Left Join t_ICItem t1 On t1.FItemID =u1.FItemID Where u1.finterid=1204 And u1.FEntryID=(Select Max(FEntryid) From ICPurchaseEntry Where FInterid=u1.FInterid and FItemid=u1.FItemid) UPDATE ICPurchaseEntry SET FAmountMustOld=FAmountMust,FNoMustOld=FNoMust,FDeductTaxOld=FDeductTax WHERE FInterID=1204 update ICStockBill set FRelateInvoiceID=1204 where FInterID=2199 INSERT INTO t_Log (FDate,FUserID,FFunctionID,FStatement,FDescription,FMachineName,FIPAddress) VALUES (getdate(),16394,'K000301',3,'编号为ZPOFP000095的单据保存成功','WIN-5579AATH4RN','192.168.6.149') update icpurchase set FCheckerID=16394 ,FArApStatus=FArApStatus | 1 ,fcheckdate=getdate() where FInterID=1204; update t_RP_Contact set FStatus=FStatus | 1,FToBal=1 where FInvoiceID=1204 and FType = 4 and FK3Import=1 UPDATE ICPurchase SET FCheckerID=16394,FStatus=1 ,FCheckDate='2018-04-10' WHERE FInterID=1204
跟踪语句_业务单据_采购订单
select * from t_TableDescription where FtableName='POOrder' select * from t_FieldDescription where FTableID=200004 order by ffieldname select * from t_TableDescription where FtableName='POOrderEntry' select * from t_FieldDescription where FTableID=200005 order by ffieldname declare @p2 int set @p2=1142 exec GetICMaxNum 'POOrder',@p2 output,1,16394 select @p2 INSERT INTO POOrderEntry (FInterID,FEntryID,FBrNo,FMapNumber,FMapName, FItemID,FAuxPropID,FQty,FUnitID,FAuxQty, FSecCoefficient,FSecQty,Fauxprice,FAuxTaxPrice,FAmount, FTaxRate,FAuxPriceDiscount,FDescount,FCess,FTaxAmount, FAllAmount,Fdate,Fnote,FSourceBillNo,FSourceTranType, FSourceInterId,FSourceEntryID,FContractBillNo,FContractInterID,FContractEntryID, FMrpLockFlag,FReceiveAmountFor_Commit,FPlanMode,FMTONo,FSupConfirm, FSupConDate,FSupConMem,FSupConFetchDate,FSupConfirmor,FPRInterID, FPREntryID,FEntryAccessoryCount,FCheckMethod,FIsCheck,FCloseEntryDate, FCloseEntryUser,FCloseEntryCauses,FOutSourceInterID,FOutSourceEntryID,FOutSourceTranType) SELECT 1142,1,'0','','', 42571,0,100000,39272,100, 0,0,85.47,100,8547.01, 0,100,0,17,1452.99, 10000,'2018-04-07','','',0, 0,0,'',0,0, 0,0,14036,'','', Null,'',Null,0,0, 0,0,352,'0',Null, 0,'',0,0,0 union all SELECT 1142,2,'0','','', 42573,0,100000,39272,100, 0,0,85.47,100,8547.01, 0,100,0,17,1452.99, 10000,'2018-04-07','','',0, 0,0,'',0,0, 0,0,14036,'','', Null,'',Null,0,0, 0,0,352,'0',Null, 0,'',0,0,0 EXEC p_UpdateBillRelateData 71,1142,'POOrder','POOrderEntry' INSERT INTO POOrder (FInterID,FBillNo,FBrNo,FTranType,FCancellation, FStatus,FSupplyID,Fdate,FCurrencyID,FCheckDate, FMangerID,FDeptID,FEmpID,FBillerID,FExchangeRateType, FExchangeRate,FPOStyle,FRelateBrID,FMultiCheckDate1,FMultiCheckDate2, FMultiCheckDate3,FMultiCheckDate4,FMultiCheckDate5,FMultiCheckDate6,FSelTranType, FBrID,FExplanation,FSettleID,FSettleDate,FAreaPS, FPOOrdBillNo,FManageType,FSysStatus,FValidaterName,FConsignee, FHeadSelfP0246,FVersionNo,FChangeDate,FChangeUser,FChangeCauses, FChangeMark,FPrintCount,FDeliveryPlace,FPOMode,FAccessoryCount, FLastAlterBillNo,FPlanCategory,FCloseDate,FCloseUser,FCloseCauses, FEnterpriseID,FSendStatus) SELECT 1142,'POORD000001','0',71,0, 0,42755,'2018-04-07',1,Null, 0,35637,42657,16394,1, 1,252,0,Null,Null, Null,Null,Null,Null,0, 0,'',0,'2018-04-07',20302, '',0,0,'',0, 0,'000',Null,0,'', '',0,'',36680,0, '','1',Null,0,'', 0,0 --FSysStatus系统设置 UPDATE POOrder SET FSysStatus = 2 WHERE FInterID = 1142 --附件表 DELETE t_Accessory WHERE FTypeID=200071 AND FItemID=1142 AND FEntryID=0 insert into t_Accessory(FTypeID,FItemID,FDesc,FFileName,FFile,FFileSize,FUpLoader,FUpLoadTime,FChecker,FIsPIC,FData,FVersion,FSaveMode,FPage,FEntryID) select 200071,1142,M.Fdesc,M.FFileName,M.FFile,M.FFileSize,M.FUpLoader,M.FUpLoadTime,M.FChecker,M.FIsPIC,M.FData,M.FVersion,M.FSaveMode,M.FPage,0 from t_Accessory M INNER JOIN (select Distinct(B.FID) from POOrderEntry A INNER JOIN ICSampleReqPetitionEntry B ON A.FSourceInterID=B.FID AND A.FSourceEntryID=B.FEntryID WHERE A.FInterID=1142 ) N ON M.FItemID=N.FID AND M.FTypeID=2580015 AND M.FEntryID=0 DELETE t_Accessory WHERE FTypeID=200071 AND FItemID=1142AND FEntryID<>0 insert into t_Accessory(FTypeID,FItemID,FDesc,FFileName,FFile,FFileSize,FUpLoader,FUpLoadTime,FChecker,FIsPIC,FData,FVersion,FSaveMode,FPage,FEntryID) select 200071,1142,M.Fdesc,M.FFileName,M.FFile,M.FFileSize,M.FUpLoader,M.FUpLoadTime,M.FChecker,M.FIsPIC,M.FData,M.FVersion,M.FSaveMode,M.FPage,P.FEntryID from t_Accessory M INNER JOIN ICSampleReqPetitionEntry N ON M.FItemID=N.FID AND M.FEntryID=N.FEntryID INNER JOIN POOrderEntry P ON N.FID=P.FSourceInterID AND N.FEntryID=P.FSourceEntryID WHERE P.FInterID=1142 AND M.FTypeID=2580015 AND P.FSourceTranType=1007315 AND M.FEntryID<>0 UPDATE A SET A.FAutoClosed=CASE WHEN A.FReqCommitQty>=A.FQty THEN 1 ELSE 0 END,A.FClosed=CASE WHEN (A.FClosed =1 AND A.FAutoClosed= 0) THEN 1 ELSE CASE WHEN A.FReqCommitQty>=A.FQty THEN 1 ELSE 0 END END FROM ICSampleReqPetitionEntry A INNER JOIN POOrderEntry B ON A.FID=B.FSourceInterID AND A.FEntryID=B.FSourceEntryID WHERE B.FInterID=1142 INSERT INTO t_Log (FDate,FUserID,FFunctionID,FStatement,FDescription,FMachineName,FIPAddress) VALUES (getdate(),16394,'K000101',3,'编号为POORD000001的单据保存成功','WIN-5579AATH4RN','192.168.6.149')
跟踪语句_业务单据_销售出库
select * from t_TableDescription where ftablename like '%poorder%' select * from t_FieldDescription where FTableID=200005 order by FFieldName declare @p2 int set @p2=1816 exec GetICMaxNum 'ICStockBill',@p2 output,1,16394 select @p2 INSERT INTO ICStockBillEntry (FInterID,FEntryID,FBrNo,FMapNumber,FMapName, FItemID,FOLOrderBillNo,FAuxPropID,FBatchNo,FQty, FUnitID,FAuxQtyMust,Fauxqty,FSecCoefficient,FSecQty, FAuxPlanPrice,FPlanAmount,Fauxprice,Famount,Fnote, FKFDate,FKFPeriod,FPeriodDate,FIsVMI,FEntrySupply, FDCStockID,FDCSPID,FConsignPrice,FDiscountRate,FConsignAmount, FDiscountAmount,FOrgBillEntryID,FSNListID,FSourceBillNo,FSourceTranType, FSourceInterId,FSourceEntryID,FContractBillNo,FContractInterID,FContractEntryID, FOrderBillNo,FOrderInterID,FOrderEntryID,FAllHookQTY,FCurrentHookQTY, FQtyMust,FSepcialSaleId,FPlanMode,FMTONo,FClientOrderNo, FConfirmMemEntry,FClientEntryID,FChkPassItem,FSEOutBillNo,FSEOutEntryID, FSEOutInterID,FReturnNoticeBillNo,FReturnNoticeEntryID,FReturnNoticeInterID,FProductFileQty, FPostFee,FOutSourceInterID,FOutSourceEntryID,FOutSourceTranType,FShopName) SELECT 1816,1,'0','','', 41002,'',0,'',2, 39239,2,2,0,0, 0,0,0,0,'', Null,0,Null,0,0, 42954,0,.47,0,.94, 0,0,0,'SEORD000086',81, 1150,1,'',0,0, 'SEORD000086',1150,1,0,0, 2,0,14036,'','', '','0',1058,'',0, 0,'',0,0,0, 0,0,0,0,'' INSERT INTO ICStockBill (FInterID,FBillNo,FBrNo,FTranType,FCancellation, FStatus,FUpStockWhenSave,FROB,FHookStatus,Fdate, FSupplyID,FSaleStyle,FConfirmDate,FCheckDate,FFManagerID, FSManagerID,FBillerID,FConfirmer,FMultiCheckDate1,FMultiCheckDate2, FMultiCheckDate3,FMultiCheckDate4,FMultiCheckDate5,FPOOrdBillNo,FMultiCheckDate6, FRelateBrID,FOrgBillInterID,FMarketingStyle,FPrintCount,FSelTranType, FBrID,FFetchAdd,FExplanation,FConfirmMem,FDeptID, FEmpID,FManagerID,FVIPCardID,FVIPScore,FReceiver, FHolisticDiscountRate,FPOSName,FWorkShiftId,FLSSrcInterID,FManageType, FPayCondition,FSettleDate,FConsignee,FInvoiceStatus,FReceiveMan, FConsigneeAdd,FCod,FReceiverMobile,FEnterpriseID,FSendStatus) SELECT 1816,'XOUT000003','0',21,0, 0,0,1,0,'2018-05-07', 42797,101,Null,Null,42842, 42842,16394,0,Null,Null, Null,Null,Null,'',Null, 0,0,12530,0,81, 0,'','','',35633, 42842,0,0,0,'', 0,'',0,0,0, 0,'2018-05-07',0,'','', '','','',0,0 UPDATE ICStockBill SET FUUID=NEWID() WHERE FInterID=1816 --更新销售订单：发货数量，辅助发货数量 set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(src.fcommitqty,0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.fqty, @maxorder=(select fvalue from t_systemprofile where fcategory='ic' and fkey='cqtylargerseqty'), @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (abs(src.fqty)>abs(@fsrccommitfield_prevalue) or abs(src.fqty)>abs(@fsrccommitfield_endvalue)) then @fcheck_fail else -1 end) end, src.fcommitqty=@fsrccommitfield_endvalue, src.fauxcommitqty=@fsrccommitfield_endvalue/cast(t1.fcoefficient as float) from seorderentry src inner join seorder srchead on src.finterid=srchead.finterid inner join (select u1.fsourceinterid as fsourceinterid,u1.fsourceentryid,u1.fitemid,sum(u1.fqty) as fqty from icstockbillentry u1 where u1.finterid=1816 group by u1.fsourceinterid,u1.fsourceentryid,u1.fitemid) dest on dest.fsourceinterid = src.finterid and dest.fitemid = src.fitemid and src.fentryid = dest.fsourceentryid inner join t_measureunit t1 on src.funitid=t1.fitemid if (isnull(@fcheck_fail,0)=-1) raiserror('可能的原因是： 1、所选单据已被其他单据关联 2、所选单据已被反审核 3、当前单据和所选单据的关联数量超过了所选单据的数量 4、所选单据已经关闭',18,18) else if exists (select 1 from seorder src right join (select u1.fsourceinterid as fsourceinterid,u1.fsourceentryid,u1.fitemid,sum(u1.fqty) as fqty from icstockbillentry u1 where u1.finterid=1816 group by u1.fsourceinterid,u1.fsourceentryid,u1.fitemid) dest on dest.fsourceinterid = src.finterid where dest.fsourceinterid>0 and src.finterid is null) raiserror('所选单据已被删除',18,18) --更新销售订单：审核状态、关闭状态 Update t Set t.FStatus =Case When (SELECT COUNT(1) FROM SEOrderEntry WHERE (FCommitQty>0 OR (ISNULL(FMRPClosed,0)=1 AND ISNULL(FMRPAutoClosed,1)=0)) AND FInterID IN(1150))=0 Then 1 When (SELECT COUNT(1) FROM SEOrderEntry te WHERE (ISNULL(FMRPClosed,0)=1 OR FCommitQty >= FQty ) AND FInterID IN(1150))<(SELECT COUNT(1) FROM SEOrderEntry WHERE FInterID IN(1150)) Then 2 Else 3 End ,t.FClosed =Case WHEN (SELECT COUNT(1) FROM SEOrderEntry te WHERE ( FCommitQty >= FQty OR (ISNULL(te.FMRPAutoClosed,1)=0 AND ISNULL(FMRPClosed,0)=1)) AND te.FInterID IN(1150))=(SELECT COUNT(1) FROM SEOrderEntry te WHERE te.FInterID IN(1150)) Then 1 Else 0 End From SEOrder t WHERE t.FInterID IN(1150) --更新销售订单：辅助执行数量 set nocount on declare @fcheck_fail int declare @fsrccommitfield_prevalue decimal(28,13) declare @fsrccommitfield_endvalue decimal(28,10) declare @maxorder int update src set @fsrccommitfield_prevalue= isnull(src.fseccommitqty,0), @fsrccommitfield_endvalue=@fsrccommitfield_prevalue+dest.fsecqty, @maxorder=(select fvalue from t_systemprofile where fcategory='ic' and fkey='cqtylargerseqty'), @fcheck_fail=case isnull(@maxorder,0) when 1 then 0 else (case when (1=1) then @fcheck_fail else -1 end) end, src.fseccommitqty=@fsrccommitfield_endvalue from seorderentry src inner join seorder srchead on src.finterid=srchead.finterid inner join (select u1.fsourceinterid as fsourceinterid,u1.fsourceentryid,u1.fitemid,sum(u1.fsecqty) as fsecqty from icstockbillentry u1 where u1.finterid=1816 group by u1.fsourceinterid,u1.fsourceentryid,u1.fitemid) dest on dest.fsourceinterid = src.finterid and dest.fitemid = src.fitemid and src.fentryid = dest.fsourceentryid IF EXISTS (SELECT 1 FROM ICBillRelations_Sale WHERE FBillType = 21 AND FBillID=1816) BEGIN UPDATE t1 SET t1.FChildren=t1.FChildren+1 FROM SEOrder t1 INNER JOIN SEOrderEntry t2 ON t1.FInterID=t2.FInterID INNER JOIN ICBillRelations_Sale t3 ON t3.FMultiEntryID=t2.FEntryID AND t3.FMultiInterID=t2.FInterID WHERE t3.FBillType=21 AND t3.FBillID=1816 END ELSE BEGIN UPDATE t3 SET t3.FChildren=t3.FChildren+1 FROM ICStockBill t1 INNER JOIN ICStockBillEntry t2 ON t1.FInterID=t2.FInterID INNER JOIN SEOrder t3 ON t3.FTranType=t2.FSourceTranType AND t3.FInterID=t2.FSourceInterID WHERE t1.FTranType=21 AND t1.FInterID=1816 AND t2.FSourceInterID>0 END --出运商品明细 update t1 set FcmtQty_O=FcmtQty_O from ExpOutReqEntry t1 inner join ( select sum(t1.FQty) FQty,t3.fdetailid from ICStockBillEntry t1 inner join ExpOutReqEntry t2 on t2.fdetailid=t1.fsourceEntryid inner join ExpOutReqEntry t3 on t3.fdetailid=t2.fentryid_src where fsourceinterid>0 and fsourcebillno<>'' and fsourcetrantype=1007131 and t1.finterid=1816 group by t3.fdetailid) t2 on t1.fdetailid=t2.fdetailid update ICStockBill set FVIPScore=ABS(FVIPScore)*(-1) WHERE FROB=-1 AND FInterID=1816 Update RTL_VIP SET FVIPScore=FVIPScore + 0 WHERE FID=0 --更新销售出库单：提交数量，辅助提交数量 UPDATE A SET A.FCommitQty=A.FCommitQty-D.FQty,A.FAuxCommitQty=A.FAuxCommitQty-(D.FQty/T.FCoefficient), A.FSecCommitQty=A.FSecCommitQty -D.FSecQty FROM ICStockBillEntry A INNER JOIN ICWebReturnEntry B ON B.FID_SRC=A.FInterID AND B.FEntryID_SRC=A.FDetailID AND B.FClassID_SRC=1007572 INNER JOIN SEOutStockEntry C ON C.FSourceInterId=B.FID AND C.FSourceEntryID=B.FEntryID INNER JOIN ICStockBillEntry D ON D.FSourceInterId =C.FInterID AND D.FSourceEntryID =C.FEntryID AND D.FSourceTranType =82 LEFT JOIN t_MeasureUnit T ON A.FUnitID=T.FMeasureUnitID WHERE D.FInterID=1816 DROP TABLE #TempBill SET NOCOUNT ON CREATE TABLE #TempBill (FID INT IDENTITY (1,1),FBrNo VARCHAR(10) NOT NULL DEFAULT(''), FInterID INT NOT NULL DEFAULT(0), FEntryID INT NOT NULL DEFAULT(0), FTranType INT NOT NULL DEFAULT(0), FItemID INT NOT NULL DEFAULT(0), FBatchNo NVARCHAR(255) NOT NULL DEFAULT(''), FMTONo NVARCHAR(255) NOT NULL DEFAULT(''), FAuxPropID INT NOT NULL DEFAULT(0), FStockID INT NOT NULL DEFAULT(0), FStockPlaceID INT NOT NULL DEFAULT(0), FKFPeriod INT NOT NULL DEFAULT(0), FKFDate VARCHAR(20) NOT NULL DEFAULT(''), FSupplyID INT NOT NULL DEFAULT(0), FQty DECIMAL(28,10) NOT NULL DEFAULT(0), FSecQty DECIMAL(28,10) NOT NULL DEFAULT(0), FAmount DECIMAL(28,2) NOT NULL DEFAULT(0) ) INSERT INTO #TempBill(FBrNo,FInterID,FEntryID,FTranType,FItemID,FBatchNo,FMTONo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FSupplyID,FQty,FSecQty,FAmount) SELECT '',u1.FInterID,u1.FEntryID,21 AS FTranType,u1.FItemID,ISNULL(u1.FBatchNo,'') AS FBatchNo,ISNULL(u1.FMTONo,'') AS FMTONo, u1.FAuxPropID,ISNULL(u1.FDCStockID,0) AS FDCStockID,ISNULL(u1.FDCSPID,0) AS FDCSPID,ISNULL(u1.FKFPeriod,0) AS FKFPeriod, LEFT(ISNULL(CONVERT(VARCHAR(20),u1.FKFdate ,120),''),10) AS FKFDate,FEntrySupply, -1*u1.FQty AS FQty,-1*u1.FSecQty AS FSecQty,-1*u1.FAmount FROM ICStockBillEntry u1 WHERE u1.FInterID=1816 order by u1.FEntryID SELECT * INTO #TempBill2 FROM #TempBill UPDATE t1 SET t1.FQty=t1.FQty+(u1.FQty), t1.FSecQty=t1.FSecQty+(u1.FSecQty) FROM ICInventory t1 INNER JOIN (SELECT FItemID,FBatchNo,FMTONo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FSupplyID ,SUM(FQty) AS FQty,SUM(FSecQty) AS FSecQty FROM #TempBill2 GROUP BY FItemID,FBatchNo,FMTONo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FSupplyID ) u1 ON t1.FItemID=u1.FItemID AND t1.FBatchNo=u1.FBatchNo AND t1.FMTONo=u1.FMTONo AND t1.FAuxPropID=u1.FAuxPropID AND t1.FStockID=u1.FStockID AND t1.FStockPlaceID=u1.FStockPlaceID AND t1.FKFPeriod=u1.FKFPeriod AND t1.FKFDate=u1.FKFDate AND t1.FSupplyID=u1.FSupplyID DELETE u1 FROM ICInventory t1 INNER JOIN #TempBill2 u1 ON t1.FItemID=u1.FItemID AND t1.FBatchNo=u1.FBatchNo AND t1.FMTONo=u1.FMTONo AND t1.FAuxPropID=u1.FAuxPropID AND t1.FStockID=u1.FStockID AND t1.FStockPlaceID=u1.FStockPlaceID AND t1.FKFPeriod=u1.FKFPeriod AND t1.FKFDate=u1.FKFDate AND t1.FSupplyID=u1.FSupplyID INSERT INTO ICInventory(FBrNo,FItemID,FBatchNo,FMTONo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FSupplyID,FQty,FSecQty) SELECT '',FItemID,FBatchNo,FMTONo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FSupplyID, SUM(FQty) AS FQty,SUM(FSecQty) AS FSecQty FROM #TempBill2 GROUP BY FItemID,FBatchNo,FMTONo,FAuxPropID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FSupplyID DROP TABLE #TempBill2 UPDATE P1 SET P1.FLockFlag=(CASE WHEN ISNULL(t1.FQty,0)<=0 THEN 0 ELSE 1 END) FROM SEOrderEntry P1 INNER JOIN ICStockBillEntry u1 ON u1.FOrderInterID=P1.FInterID AND u1.FOrderEntryID=P1.FEntryID AND u1.FItemID=P1.FItemID INNER JOIN (SELECT FInterID,FEntryID,SUM(FQty) AS FQty FROM t_LockStock WHERE FTranType=81 GROUP BY FInterID,FEntryID) t1 ON P1.FInterID=t1.FInterID AND P1.FEntryID=t1.FEntryID WHERE u1.FInterID=1816 DROP TABLE #TempBill UPDATE ICStockBill SET FOrderAffirm=0 WHERE FInterID=1816 Update ICStockBill Set FCheckerID=16394,FStatus=1,FCheckDate='2018-05-07' WHERE FInterID=1816 IF EXISTS(SELECT FOrderInterID FROM ICStockBillEntry WHERE FOrderInterID>0 AND FInterID=1816) BEGIN UPDATE u1 SET u1.FStockQty=u1.FStockQty+1*Cast(u2.FStockQty as Float) ,u1.FSecStockQty=u1.FSecStockQty+1*Cast(u2.FSecStockQty as Float) ,u1.FAuxStockQty=ROUND((u1.FStockQty+1*Cast(u2.FStockQty as Float))/Cast(t3.FCoefficient as Float),t1.FQtyDecimal) FROM SEOrderEntry u1 INNER JOIN (SELECT FOrderInterID,FOrderEntryID,FItemID,SUM(FQty)AS FStockQty,SUM(FAuxQty) AS FAuxStockQty,SUM(FSecQty) AS FSecStockQty FROM ICStockBillEntry WHERE FInterID=1816 GROUP BY FOrderInterID,FOrderEntryID,FItemID) u2 ON u1.FInterID=u2.FOrderInterID AND u1.FEntryID=u2.FOrderEntryID AND u1.FItemID=u2.FItemID INNER JOIN t_ICItemBase t1 ON u1.FItemID=t1.FItemID INNER JOIN t_MeasureUnit t3 ON u1.FUnitID=t3.FItemID --供给汇总数据 IF OBJECT_ID('tempdb..#tmpPMCIndex','U') IS NOT NULL DROP TABLE #tmpPMCIndex SELECT u0.FIndex INTO #tmpPMCIndex FROM ICPlan_PMCdetail u0 INNER JOIN SEOrderEntry u1 ON u0.FRelTranType=81 AND u0.FRelInterID=u1.FInterID AND u0.FRelEntryID=u1.FEntryID AND u0.FBillType In(22,25) INNER JOIN ( SELECT DISTINCT FOrderInterID,FOrderEntryID,FItemID FROM ICStockBillEntry WHERE FOrderInterID>0 AND FInterID=1816 ) u2 ON u1.FInterID=u2.FOrderInterID AND u1.FEntryID=u2.FOrderEntryID AND u1.FItemID=u2.FItemID CREATE CLUSTERED INDEX PK_#tmpPMCIndex ON #tmpPMCIndex(FIndex) UPDATE u0 SET u0.FWillOutQty=CASE WHEN u1.FQty>u1.FStockQty THEN u1.FQty-u1.FStockQty ELSE 0 END FROM ICPlan_PMCdetail u0 INNER JOIN SEOrderEntry u1 ON u0.FRelTranType=81 AND u0.FRelInterID=u1.FInterID AND u0.FRelEntryID=u1.FEntryID AND u0.FBillType In(22,25) WHERE exists(select 1 from #tmpPMCIndex where FIndex=u0.FIndex) DROP TABLE #tmpPMCIndex END --更新销售订单 IF EXISTS(SELECT FOrderInterID FROM ICStockBillEntry WHERE FOrderInterID>0 AND FInterID=1816) BEGIN UPDATE u1 SET u1.FStockQty=u1.FStockQty+1*Cast(u2.FStockQty as Float) ,u1.FSecStockQty=u1.FSecStockQty+1*Cast(u2.FSecStockQty as Float) ,u1.FAuxStockQty=ROUND((u1.FStockQty+1*Cast(u2.FStockQty as Float))/Cast(t3.FCoefficient as Float),t1.FQtyDecimal) FROM SEOrderEntry u1 INNER JOIN (SELECT FOrderInterID,FOrderEntryID,FItemID,SUM(FQty)AS FStockQty,SUM(FAuxQty) AS FAuxStockQty,SUM(FSecQty) AS FSecStockQty FROM ICStockBillEntry WHERE FInterID=1816 GROUP BY FOrderInterID,FOrderEntryID,FItemID) u2 ON u1.FInterID=u2.FOrderInterID AND u1.FEntryID=u2.FOrderEntryID AND u1.FItemID=u2.FItemID INNER JOIN t_ICItemBase t1 ON u1.FItemID=t1.FItemID INNER JOIN t_MeasureUnit t3 ON u1.FUnitID=t3.FItemID IF OBJECT_ID('tempdb..#tmpPMCIndex','U') IS NOT NULL DROP TABLE #tmpPMCIndex SELECT u0.FIndex INTO #tmpPMCIndex FROM ICPlan_PMCdetail u0 INNER JOIN SEOrderEntry u1 ON u0.FRelTranType=81 AND u0.FRelInterID=u1.FInterID AND u0.FRelEntryID=u1.FEntryID AND u0.FBillType In(22,25) INNER JOIN ( SELECT DISTINCT FOrderInterID,FOrderEntryID,FItemID FROM ICStockBillEntry WHERE FOrderInterID>0 AND FInterID=1816 ) u2 ON u1.FInterID=u2.FOrderInterID AND u1.FEntryID=u2.FOrderEntryID AND u1.FItemID=u2.FItemID CREATE CLUSTERED INDEX PK_#tmpPMCIndex ON #tmpPMCIndex(FIndex) UPDATE u0 SET u0.FWillOutQty=CASE WHEN u1.FQty>u1.FStockQty THEN u1.FQty-u1.FStockQty ELSE 0 END FROM ICPlan_PMCdetail u0 INNER JOIN SEOrderEntry u1 ON u0.FRelTranType=81 AND u0.FRelInterID=u1.FInterID AND u0.FRelEntryID=u1.FEntryID AND u0.FBillType In(22,25) WHERE exists(select 1 from #tmpPMCIndex where FIndex=u0.FIndex) DROP TABLE #tmpPMCIndex END UPDATE p1 SET p1.FMrpClosed=CASE WHEN ISNULL(p1.FMRPAutoClosed,1)=1 THEN (CASE WHEN p1.FStockQty<p1.FQty THEN 0 ELSE 1 END) ELSE p1.FMrpClosed END FROM SEOrderEntry p1 INNER JOIN ICStockBillEntry u1 ON u1.FOrderInterID=p1.FInterID AND u1.FOrderEntryID=p1.FEntryID WHERE u1.FInterID=1816 Update t Set t.FStatus =Case When (SELECT COUNT(1) FROM SEOrderEntry WHERE (FCommitQty>0 OR (ISNULL(FMRPClosed,0)=1 AND ISNULL(FMRPAutoClosed,1)=0)) AND FInterID IN(1150))=0 Then 1 When (SELECT COUNT(1) FROM SEOrderEntry te WHERE (ISNULL(FMRPClosed,0)=1 OR FCommitQty >= FQty ) AND FInterID IN(1150))<(SELECT COUNT(1) FROM SEOrderEntry WHERE FInterID IN(1150)) Then 2 Else 3 End ,t.FClosed =Case WHEN (SELECT COUNT(1) FROM SEOrderEntry te WHERE ( FCommitQty >= FQty OR (ISNULL(te.FMRPAutoClosed,1)=0 AND ISNULL(FMRPClosed,0)=1)) AND te.FInterID IN(1150))=(SELECT COUNT(1) FROM SEOrderEntry te WHERE te.FInterID IN(1150)) Then 1 Else 0 End From SEOrder t WHERE t.FInterID IN(1150) IF EXISTS(SELECT FOrderInterID FROM ICStockBillEntry WHERE FSEOutInterID>0 AND FInterID=1816) UPDATE u1 SET u1.FStockQty=u1.FStockQty+1*Cast(u2.FStockQty as Float) ,u1.FSecStockQty=u1.FSecStockQty+1*Cast(u2.FSecStockQty as Float) ,u1.FAuxStockQty=ROUND((u1.FStockQty+1*Cast(u2.FStockQty as Float))/Cast(t3.FCoefficient as Float),t1.FQtyDecimal) FROM SEOutStockEntry u1 INNER JOIN (SELECT FSEOutInterID,FSEOutEntryID,FItemID,SUM(FQty)AS FStockQty,SUM(FAuxQty) AS FAuxStockQty,SUM(FSecQty) AS FSecStockQty FROM ICStockBillEntry WHERE FInterID=1816 GROUP BY FSEOutInterID,FSEOutEntryID,FItemID) u2 ON u1.FInterID=u2.FSEOutInterID AND u1.FEntryID=u2.FSEOutEntryID AND u1.FItemID=u2.FItemID INNER JOIN t_ICItemBase t1 ON u1.FItemID=t1.FItemID INNER JOIN t_MeasureUnit t3 ON u1.FUnitID=t3.FItemID
跟踪语句_业务单据_销售订单
select * from t_TableDescription where FTableName='seorder' select * from t_FieldDescription where FTableID=230004 order by FFieldName select * from t_TableDescription where FTableName='ICPrcPlyEntry' select * from t_FieldDescription where FTableID=230005 order by FFieldName declare @p2 int set @p2=1219 exec GetICMaxNum 'SEOrder',@p2 output,1,16394 select @p2 INSERT INTO SEOrderEntry (FInterID,FEntryID,FBrNo,FMapNumber,FMapName, FItemID,FAuxPropID,FQty,FUnitID,Fauxqty, FSecCoefficient,FSecQty,Fauxprice,FAuxTaxPrice,Famount, FCess,FTaxRate,FUniDiscount,FTaxAmount,FAuxPriceDiscount, FTaxAmt,FAllAmount,FTranLeadTime,FInForecast,FDate, Fnote,FPlanMode,FMTONo,FBOMCategory,FBomInterID, FOrderBOMStatus,FCostObjectID,FAdviceConsignDate,FATPDeduct,FLockFlag, FSourceBillNo,FSourceTranType,FSourceInterId,FSourceEntryID,FContractBillNo, FContractInterID,FContractEntryID,FSecCommitInstall,FCommitInstall,FAuxCommitInstall, FAllStdAmount,FMrpLockFlag,FHaveMrp,FReceiveAmountFor_Commit,FOrderBOMInterID, FOrderBillNo,FOrderEntryID,FOutSourceInterID,FOutSourceEntryID,FOutSourceTranType) SELECT 1219,1,'0','','', 1688,0,1,186,1, 0,0,1,1.17,1, 17,0,0,0,1.17, .17,1.17,'',0,'2018-04-11', '',14036,'',0,0, 0,'0','2018-04-11',0,0, '',0,0,0,'', 0,0,0,0,0, 1.17,0,0,0,0, '','',0,0,0 EXEC p_UpdateBillRelateData 81,1219,'SEOrder','SEOrderEntry' INSERT INTO SEOrder (FInterID,FBillNo,FBrNo,FTranType,FCancellation, FStatus,FDiscountType,Fdate,FCustAddress,FSaleStyle, FFetchStyle,FCurrencyID,FCustID,FFetchAdd,FCheckDate, FMangerID,FDeptID,FEmpID,FBillerID,FSettleID, FExchangeRateType,FExchangeRate,FMultiCheckLevel1,FMultiCheckDate1,FMultiCheckLevel2, FMultiCheckDate2,FMultiCheckLevel3,FMultiCheckDate3,FMultiCheckLevel4,FMultiCheckDate4, FMultiCheckLevel5,FMultiCheckDate5,FMultiCheckLevel6,FMultiCheckDate6,FPOOrdBillNo, FRelateBrID,FTransitAheadTime,FImport,FSelTranType,FBrID, FSettleDate,FExplanation,FAreaPS,FManageType,FSysStatus, FValidaterName,FConsignee,FVersionNo,FChangeDate,FChangeUser, FChangeCauses,FChangeMark,FPrintCount,FPlanCategory,FEnterpriseID, FSendStatus) SELECT 1219,'SEORD000086','0',81,0, 0,0,'2018-04-11',0,101, '',1,2656,'',Null, 0,237,263,16394,0, 1,1,Null,Null,Null, Null,Null,Null,Null,Null, Null,Null,Null,Null,'', 0,'0',0,1000019,0, '2018-04-11','',20302,0,0, '',0,'000',Null,0, '','',0,'1',0, 0 UPDATE SEOrder SET FSysStatus = 2 WHERE FInterID = 1219 UPDATE SEOrder SET FUUID=NEWID() WHERE FInterID=1219 --成本对象 SET NOCOUNT ON SELECT v1.FBillNo,u1.FCostObjectID INTO #OldCostObject FROM SEOrder v1 INNER JOIN SEOrderEntry u1 ON v1.FInterID=u1.FInterID WHERE v1.FInterID=1219 SELECT v1.FBillNo,u1.FCostObjectID INTO #NewCostObject FROM SEOrder v1 INNER JOIN SEOrderEntry u1 ON v1.FInterID=u1.FInterID WHERE v1.FInterID=1219 AND u1.FCostObjectID>0 Update t1 Set FSBillNo='' FROM CBCostobj t1 INNER JOIN #OldCostObject t2 on t1.FSBillNo=t2.FBillNo Update t1 Set FSBillNo=t2.FBillNo FROM CBCostobj t1 INNER JOIN #NewCostObject t2 on t2.FCostObjectID=t1.FItemID DROP TABLE #OldCostObject DROP TABLE #NewCostObject --辅助属性 UPDATE obc SET obc.FItemPropID=oo.FAuxPropID FROM PPOrderEntry oo INNER JOIN ICOrderBOMChild obc ON oo.FInterID=1219 AND oo.FOrderBOMInterID=obc.FInterID AND obc.FParentID=0 UPDATE obc SET obc.FItemPropID=oo.FAuxPropID FROM PPOrderEntry oo INNER JOIN ICOrderBOM obc ON oo.FInterID=1219 AND oo.FOrderBOMInterID=obc.FInterID UPDATE obc SET obc.FItemPropID=oo.FAuxPropID FROM SEOrderEntry oo INNER JOIN ICOrderBOMChild obc ON oo.FInterID=1219 AND oo.FOrderBOMInterID=obc.FInterID AND obc.FParentID=0 UPDATE obc SET obc.FItemPropID=oo.FAuxPropID FROM SEOrderEntry oo INNER JOIN ICOrderBOM obc ON oo.FInterID=1219 AND oo.FOrderBOMInterID=obc.FInterID --网上订货单 UPDATE v1 SET v1.FExecStatus =1 FROM ICCustNetOrder v1 INNER JOIN SEOrderEntry u2 ON v1.FID = u2.FSourceInterID AND u2.FSourceTranType = 1007553 AND u2.FInterID =1219 --价格政策 Create Table #tmpPrcPly(FRowNo int, FItemID int, FAuxPropID int,FUnitID int, FCuryID int,FDisType int, FPrice Decimal(28,10) Not Null default 0, FAppScale int, FQty Decimal(28,10), FBaseQty Decimal(28,10), FDiscount Decimal(28,10) Not Null default 0, FPrcInterID int,FPrcEntryID int, FDisInterID int,FDisEntryID int, FMainterID int,FMaintDate datetime, FCheckerID int,FCheckDate datetime) Insert Into #tmpPrcPly (FRowNo, FItemID, FAuxPropId, FUnitID, FCuryID, FDisType, FPrice, FAppScale, FQty,FBaseQty, FDisCount,FPrcInterID,FPrcEntryID,FDisInterID,FDisEntryID, FMainterID,FMaintDate,FCheckerID,FCheckDate) Values (1,1688,0,186,1, 0,1.17,0,1,1, 0,0,0,0,0, 16394,CONVERT(VARCHAR(10),GETDATE(),120),null,null) Update t1 Set FUnitID=IsNull(t2.FUnitID,0), FPrice=IsNull(FPrice/tm.FCoefficient,0) From #tmpPrcPly t1 Left Join t_ICItem t2 On t1.FItemID=t2.FItemID Left Join t_MeasureUnit tm On t1.FUnitID=tm.FMeasureUnitID Select t1.FPri,t1.FInterID,t2.FEntryID,t3.FPrice,t3.FItemID,t3.FAuxPropID,t3.FUnitID,t3.FQty, case when t1.FPlyType ='PrcAsm1' then '2656' when t1.FPlyType ='PrcAsm2' then (select top 1 ftypeid from t_Organization where FItemID = '2656') when t1.FPlyType ='PrcAsm3' then '263' when t1.FPlyType ='PrcAsm4' then (select top 1 Fempgroup from t_Emp where FItemID = '263') end as FRelatedID Into #tmpPri From IcPrcPly t1 Inner Join IcPrcPlyEntry t2 On t1.FInterId=t2.FInterID Inner Join #tmpPrcPly t3 On t2.FCuryID=t3.FCuryID And t3.FItemID=t2.FItemID And t3.FAuxPropID=t2.FAuxPropID And t3.FUnitID=t2.FUnitID Inner Join (Select Max(FRowNo) FRowNo From #tmpPrcPly Group By FItemID,FAuxPropID,FUnitID) t4 On t3.FRowNo=t4.FRowNo Where t1.FPlyType=(select TOP 1 FKey from ICPrcOpt Where FCategory='ICPrcPlyType' and FValue = 1 order by FSort) And (FPeriodType = 0 Or (FPeriodType=1 And FCycBegTime<='18:39:11' And FCycEndTime>='18:39:11') Or (FPeriodType=2 And FCycBegTime<='18:39:11' And FCycEndTime>='18:39:11' And CharIndex('3',FWeek)>0) or (FPeriodType=3 And FCycBegTime<='18:39:11' And FCycEndTime>='18:39:11' And CharIndex('04' ,FMonth)>0 And (FDayPerMonth='11' Or (FSerialWeekPerMonth='2' And FWeekDayPerMonth='3')))) And t2.FBegDate<='2018-04-11' And t2.FEndDate>='2018-04-11' And t2.FChecked=0 And t3.FPrice>0 And ((t2.FBegQty<=t3.FBaseQty And t2.FEndQty>=t3.FBaseQty) Or (t2.FBegQty=0 And t2.FEndQty=0)) UpDate t1 Set FPrice=t2.FPrice, FMainterID = 16394, FMaintDate = CONVERT(VARCHAR(10), GETDATE(), 120), FCheckerID = (CASE WHEN FChecked = 1 THEN 16394 ELSE FCheckerID END), FCheckDate = (CASE WHEN FChecked = 1 THEN CONVERT(VARCHAR(10), GETDATE(), 120) ELSE FCheckDate END) From ICPrcPlyEntry t1 Inner Join #tmpPri t2 on t1.FInterID=t2.FInterID And t1.FEntryID=t2.FEntryID AND t1.FRelatedID=t2.FRelatedID Inner Join (Select Min(Fpri) as FPri,FItemID,FAuxPropID,FUnitID,FQty From #tmpPri Group By FItemID,FAuxPropID,FUnitID,FQty) t3 On t2.Fpri=t3.FPri And t2.FItemID=t3.FItemID And t2.FAuxPropID= t3.FAuxPropID And t2.FUnitID=t3.FUnitID And t2.FQty=t3.FQty Drop Table #tmpPri Select t1.FPri,t1.FInterID,t2.FEntryID,t3.FDiscount ,t3.FItemID,t3.FAuxPropID,t3.FUnitID,t3.FQty Into #tmpDis From IcDisPly t1 Inner Join IcDisPlyEntry t2 On t1.FInterId=t2.FInterID Inner Join #tmpPrcPly t3 On t3.FItemID=t2.FItemID And t3.FAuxPropID=t2.FAuxPropID And t3.FUnitID=t2.FUnitID Inner Join (Select Max(FRowNo) FRowNo From #tmpPrcPly Group By FItemID,FAuxPropID,FUnitID) t4 On t3.FRowNo=t4.FRowNo Where t1.FPlyType='DisAsm1' And (FPeriodType = 0 Or (FPeriodType=1 And FCycBegTime<='18:39:11' And FCycEndTime>='18:39:11') Or (FPeriodType=2 And FCycBegTime<='18:39:11' And FCycEndTime>='18:39:11' And CharIndex('3',FWeek)>0) or (FPeriodType=3 And FCycBegTime<='18:39:11' And FCycEndTime>='18:39:11' And CharIndex('04' ,FMonth)>0 And (FDayPerMonth='11' Or (FSerialWeekPerMonth='2' And FWeekDayPerMonth='3')))) And t2.FBegDate<='2018-04-11' And t2.FEndDate>='2018-04-11' And t2.FChecked=0 And t3.FDiscount<>0 And t3.FAppScale=0 And t2.FAppScale=t3.FAppScale And t2.FDisType=t3.FDisType And ((t2.FBegQty<=t3.FBaseQty And t2.FEndQty>=t3.FBaseQty) Or (t2.FBegQty=0 And t2.FEndQty=0)) UpDate t1 Set FValue=t2.FDiscount, FMainterID = 16394, FMaintDate = CONVERT(VARCHAR(10), GETDATE(), 120), FCheckerID = (CASE WHEN FChecked = 1 THEN 16394 ELSE FCheckerID END), FCheckDate = (CASE WHEN FChecked = 1 THEN CONVERT(VARCHAR(10), GETDATE(), 120) ELSE FCheckDate END) From IcDisPlyEntry t1 Inner Join #tmpDis t2 on t1.FInterID=t2.FInterID And t1.FEntryID=t2.FEntryID Inner Join (Select Min(Fpri) as FPri,FItemID,FAuxPropID,FUnitID,FQty From #tmpDis Group By FItemID,FAuxPropID,FUnitID,FQty) t3 On t2.Fpri=t3.FPri And t2.FItemID=t3.FItemID And t2.FAuxPropID= t3.FAuxPropID And t2.FUnitID=t3.FUnitID And t2.FQty=t3.FQty where t1.frelatedid=2656 Drop Table #tmpDis Insert Into ICPrcPlyEntry(FInterID,FItemID,FRelatedID,FUnitID,FAuxPropID,FBegQty,FEndQty,FCuryID,FPriceType,FPrice,FBegDate,FEndDate,FChecked,FMainterID,FMaintDate,FCheckerID,FCheckDate) Select t3.FInterID As FInterID,t1.FItemID, t3.FRelatedID as FRelatedID ,t1.FUnitID,t1.FAuxPropID,0,0,t1.FCuryID,0,t1.FPrice,'2018-04-11','2100-01-01',0,t1.FMainterID,t1.FMaintDate,t1.FCheckerID,t1.FCheckDate From #tmpPrcPly t1 Inner Join (Select Max(FRowNo) FRowNo From #tmpPrcPly Group By FItemID,FAuxPropID,FUnitID) t2 On t1.FRowNo=t2.FRowNo Cross Join (Select tt1.FPlyType,tt1.FInterid,case when tt1.FPlyType ='PrcAsm1' then '2656' when tt1.FPlyType ='PrcAsm2' then (select top 1 ftypeid from t_Organization where FItemID = '2656') when tt1.FPlyType ='PrcAsm3' then '263' when tt1.FPlyType ='PrcAsm4' then (select top 1 Fempgroup from t_Emp where FItemID = '263') end AS FRelatedID From IcprcPly tt1 Where tt1.FPlyType in (select TOP 1 FKey from ICPrcOpt Where FCategory='ICPrcPlyType' and FValue = 1 order by FSort) and tt1.FInterID = (Select TOP 1 FInterid From IcprcPly tt2 Where tt1.FPlyType= tt2.FPlyType order by FPri)) t3 Where t1.FPrice>0 AND t3.FRelatedID > 0 And Not Exists (Select 1 From ICPrcPly t100 , ICPrcPlyEntry t101 Where t100.FInterID=t101.FInterID And t101.FChecked=0 And t101.FRelatedID=t3.FRelatedID And t101.FItemID=t1.FItemID And t101.FAuxPropID=t1.FAuxPropID And t101.FUnitID=t1.FUnitID And t101.FCuryID=t1.FCuryID And ((t101.FBegQty<=t1.FBaseQty And t101.FEndQty>=t1.FBaseQty) or (t101.FBegQty=0 And t101.FEndQty=0)) And t100.FPlyType=t3.FPlyType And (FPeriodType = 0 Or (FPeriodType=1 And FCycBegTime<='18:39:11' And FCycEndTime>='18:39:11') Or (FPeriodType=2 And FCycBegTime<='18:39:11' And FCycEndTime>='18:39:11' And CharIndex('3',FWeek)>0) or (FPeriodType=3 And FCycBegTime<='18:39:11' And FCycEndTime>='18:39:11' And CharIndex('04' ,FMonth)>0 And (FDayPerMonth='11' Or (FSerialWeekPerMonth='2' And FWeekDayPerMonth='3')))) And t101.FBegDate<='2018-04-11' And t101.FEndDate>='2018-04-11') Insert Into ICDisPlyEntry(FInterID,FItemID,FRelatedID,FUnitID,FAuxPropID,FBegQty,FEndQty,FBegAmt,FEndAmt,FCuryID, FDisType,FAppScale,FValue,FBegDate,FEndDate,FChecked,FMainterID,FMaintDate,FCheckerID,FCheckDate) Select (Select top 1 FInterid From IcDisPly Where FPlyType='DisAsm1' Order by FPri) As FInterID,t1.FItemID,2656,t1.FUnitID,FAuxPropID,0,0,0,0,0, t1.FDisType,0,t1.FDiscount,'2018-04-11','2100-01-01',0,t1.FMainterID,t1.FMaintDate,t1.FCheckerID,t1.FCheckDate From #tmpPrcPly t1 Inner Join (Select Max(FRowNo) FRowNo From #tmpPrcPly Group By FItemID,FAuxPropID,FUnitID) t2 On t1.FRowNo=t2.FRowNo Where t1.FDiscount<>0 And Not Exists (Select 1 From ICDisPly t100, ICDisPlyEntry t101 Where t100.FInterID=t101.FInterID And t101.FAppScale=0 And t101.FChecked=0 And t101.FRelatedID=2656 And t101.FItemID=t1.FItemID And t101.FAuxPropID=t1.FAuxPropID And t101.FUnitID=t1.FUnitID And ((t101.FBegQty<=t1.FBaseQty And t101.FEndQty>=t1.FBaseQty) or (t101.FBegQty=0 And t101.FEndQty=0)) And t100.FPlyType='DisAsm1' And (FPeriodType = 0 Or (FPeriodType=1 And FCycBegTime<='18:39:11' And FCycEndTime>='18:39:11') Or (FPeriodType=2 And FCycBegTime<='18:39:11' And FCycEndTime>='18:39:11' And CharIndex('3',FWeek)>0) or (FPeriodType=3 And FCycBegTime<='18:39:11' And FCycEndTime>='18:39:11' And CharIndex('04' ,FMonth)>0 And (FDayPerMonth='11' Or (FSerialWeekPerMonth='2' And FWeekDayPerMonth='3')))) And t101.FBegDate<='2018-04-11' And t101.FEndDate>='2018-04-11') And Exists (Select 1 From IcDisPly Where FPlyType='DisAsm1') Drop table #tmpPrcPly INSERT INTO t_Log (FDate,FUserID,FFunctionID,FStatement,FDescription,FMachineName,FIPAddress) VALUES (getdate(),16394,'K030101',3,'编号为SEORD000086的单据保存成功','WIN-5579AATH4RN','192.168.6.149')
网名：浩秦;
邮箱：root#landv.pw;
博客：landv.cnblogs.com;
只要我能控制一個國家的貨幣發行，我不在乎誰制定法律。金錢一旦作響，壞話隨之戛然而止。
