CREATE TRIGGER [PORequestToPOOrder] ON [dbo].[PORequest]
FOR UPDATE
AS
--采购申请单自动转至采购订单
BEGIN
DECLARE @FCurrencyID INT,
@FInterID INT,
@FEntryID INT,
@ROwID INT,
@FBillno VARCHAR(50),
@FSupplyID INT,
@FStatus INT,
@FNumber VARCHAR(50),
@FLength INT,
@FBrNO INT,
@FZero DECIMAL(28,10)
DECLARE @FMaxNum INT,
@FCustID INT,
@FSaleStyle INT,
@FDeptID INT,
@FEmpID INT,
@FBillerID INT,
@FMangerID INT,
@FBillPOONo VARCHAR(50),
@FBillCurNo INT,
@FBillCurChar VARCHAR(50),
@FCheckerID INT
SELECT @FCustID = 4017,@FBrNO = 0,@FZero = 0.000,@FDeptID = 112,
@FSupplyID = 71471,@FEmpID = 432,@FBillerID = 16394,
@FMangerID = 73751,@FCheckerID = 16531
SELECT @FBillNo = FBillNo,@FStatus = FStatus,@FInterid = FInterid
FROM inserted
IF (@FStatus=1 AND Update(FStatus))
BEGIN
--1. 采购申请单中有吉利发物料
--2. 采购订单没有吉利发此笔申请单物料
IF Exists( Select 1 From PORequestEntry Where FInterID = @FInterID AND FSupplyID=@FSupplyID) AND NOT Exists(SELECT 1 FROM POOrder a1 LEFT JOIN POOrderEntry b1 ON a1.FInterID=b1.FInterID WHERE a1.FSupplyID=@FSupplyID AND b1.FSourceBillNo=@FBillNo)
BEGIN
--获取采购订单FInterID 与FBillNo编号
select @FMaxNum=FMaxNum+1 from ICMaxNum where FTableName='POOrder'
update ICMaxNum set FMaxNum=@FMaxNum where FTableName='POOrder'
select @FBillCurNo=FCurNo from ICBillNo where FBillID=71
update ICBillNo set FCurNo=FCurNo+1 where FBillID=71
update t_billcoderule set FProjectVal=@FBillCurNo+1 where fbilltypeid=71 and fclassindex=2
select @FBillPOONo=FProjectVal from t_billcoderule where fbilltypeid=71 and fclassindex=1
select @FLength=FLength from t_billcoderule where fbilltypeid=71 and fclassindex=2
select @FBillCurChar=right(cast(power(10,4) as varchar)+@FBillCurNo,@FLength)
select @FBillPOONo=@FBillPOONo+@FBillCurChar
--采购订单主表
INSERT INTO POOrder ( FInterID,FBillNo,FBrNo,FTranType,FCancellation,
FStatus,FSupplyID,Fdate,FCurrencyID,FCheckDate,
FMangerID,FDeptID,FEmpID,FBillerID,FExchangeRate,
FPOStyle,FRelateBrID,FMultiCheckLevel1,
FMultiCheckDate1,FMultiCheckLevel2,
FMultiCheckDate2,FMultiCheckLevel3,
FMultiCheckDate3,FMultiCheckLevel4,
FMultiCheckDate4,FMultiCheckLevel5,
FMultiCheckDate5,FMultiCheckLevel6,
FMultiCheckDate6,FSelTranType,FBrID,FExplanation,
FSettleID,FSettleDate,FAreaPS,FPOOrdBillNo,
FHeadSelfP0224,FHeadSelfP0225,FHeadSelfP0228 )
VALUES ( @FMaxNum,@FBillPOONo,'0',71,0,0,@FSupplyID,GETDATE(),1,NULL,
@FMangerID,@FDeptID,@FEmpID,@FBillerID,1,252,0,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,70,0,'',0,
GETDATE(),20302,'',NULL,'','' )
--采购订单子表
INSERT INTO POOrderEntry ( FInterID,FEntryID,FBrNo,FItemID,FAuxPropID,
FQty,FUnitID,FAuxQty,FSecCoefficient,Fdate,
FSecQty,FAuxTaxPrice,FEntrySelfP0247,
Fauxprice,FAmount,FCess,Fnote,FMapName,
FMapNumber,FTaxRate,FAuxPriceDiscount,
FTaxAmount,FAllAmount,FEntrySelfP0250,
FEntrySelfP0251,FSourceBillNo,
FSourceTranType,FSourceInterId,
FSourceEntryID,FContractBillNo,
FContractInterID,FContractEntryID,
FAuxQtyInvoice,FQtyInvoice,FMrpLockFlag )
SELECT @FMaxNum,FEntryID,'0',FItemID,0,FQty,FUnitID,FQty,0,
FFetchTime,0,0,0,0,0,17,FUse,'','',0,0,0,0,'','',
@FBillNo,70,32971,13,'',0,0,0,0,0
FROM PORequestEntry
WHERE FInterID = @FInterID AND FSupplyID = @FSupplyID
--记录数与最大行号不一致, 行号重新排序
SELECT @ROwID=COUNT(*) FROM POOrderEntry Where FInterID = @FMaxNum
SELECT @FEntryID=MAX(FEntryID) FROM POOrderEntry Where FInterID = @FMaxNum
IF (@ROwID<>@FEntryID)
BEGIN
SELECT @ROwID=1
DECLARE POOrderEntryCursor CURSOR
FOR
SELECT FEntryID
FROM POOrderEntry
WHERE FInterID = @FMaxNum
ORDER BY FEntryID
OPEN POOrderEntryCursor
FETCH NEXT FROM POOrderEntryCursor INTO @FEntryID
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE POOrderEntry
SET FEntryID = @ROwID
WHERE FInterID = @FMaxNum AND FEntryID = @FEntryID
FETCH NEXT FROM POOrderEntryCursor INTO @FEntryID
SELECT @ROwID = @ROwID + 1
END
CLOSE POOrderEntryCursor
deallocate POOrderEntryCursor
END
--采购订单取价