跟踪语句_业务单据_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')