金蝶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')

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Ken的杂谈

【系统设置】CentOS 修改机器名

18030
来自专栏腾讯大讲堂的专栏

白底黑字or黑底白字,眼睛更喜欢哪一个?

12310
来自专栏钱塘大数据

中国互联网协会发布:《2018中国互联网发展报告》

在2018中国互联网大会闭幕论坛上,中国互联网协会正式发布《中国互联网发展报告2018》(以下简称《报告》)。《中国互联网发展报告》是由中国互联网协会与中国互联...

13550
来自专栏钱塘大数据

理工男图解零维到十维空间,烧脑已过度,受不了啦!

让我们从一个点开始,和我们几何意义上的点一样,它没有大小、没有维度。它只是被想象出来的、作为标志一个位置的点。它什么也没有,空间、时间通通不存在,这就是零维度。

33430
来自专栏FSociety

SQL中GROUP BY用法示例

GROUP BY我们可以先从字面上来理解,GROUP表示分组,BY后面写字段名,就表示根据哪个字段进行分组,如果有用Excel比较多的话,GROUP BY比较类...

5.2K20
来自专栏怀英的自我修炼

考研英语-1-导学

英二图表作文要重视。总体而言,英语一会比英语二难点。不过就写作而言,英语二会比英语一有难度,毕竟图表作文并不好写。

11910
来自专栏前端桃园

知识体系解决迷茫的你

最近在星球里群里都有小伙伴说道自己对未来的路比较迷茫,一旦闲下来就不知道自己改干啥,今天我这篇文章就是让你觉得一天给你 25 个小时你都不够用,觉得睡觉都是浪费...

21640
来自专栏微信公众号:小白课代表

不只是软件,在线也可以免费下载百度文库了。

不管是学生,还是职场员工,下载各种文档几乎是不可避免的,各种XXX.docx,XXX.pptx更是家常便饭,人们最常用的就是百度文库,豆丁文库,道客巴巴这些下载...

44630
来自专栏haifeiWu与他朋友们的专栏

复杂业务下向Mysql导入30万条数据代码优化的踩坑记录

从毕业到现在第一次接触到超过30万条数据导入MySQL的场景(有点low),就是在顺丰公司接入我司EMM产品时需要将AD中的员工数据导入MySQL中,因此楼主负...

29740
来自专栏腾讯社交用户体验设计

ISUX Xcube智能一键生成H5

51220

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励