K3 ERP 触发器 采购申请单转采购订单

arook

  

(2010-01-12 14:05:16)
转载

var $tag='k3,ERP,触发器,采购申请单,采购订单,it'; var $tag_code='5e86b6a34a3ebc9d5186fb4005f39d8b'; var $r_quote_bligid='4d73c2c20100hliw'; var $worldcup='0'; var $worldcupball='0'; 标签： k3 ERP 触发器 采购申请单 采购订单 it

分类： SoftWareAPP

CREATE TRIGGER [PORequestToPOOrder] ON [dbo].[PORequest]
FOR UPDATE
AS
--吉利发采购申请单自动转至采购订单
--hsyou 2010/01/07
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
--采购订单取价更新
--1.取最低单价
SELECT MIN(b1.FPrice) AS FPrice, b1.FItemID,b1.FDiscount INTO #minFprice FROM t_SupplyEntry b1
RIGHT JOIN POOrderEntry a1 ON a1.FItemID=b1.FItemID
WHERE a1.FInterID=@FMaxNum AND b1.FUsed =1
GROUP BY b1.FItemID,b1.FDiscount
--2 更新采购订单单价
UPDATE a1 SET
a1.FPrice=b1.FPrice/(1+a1.FCess/100),
a1.FAuxPrice = b1.FPrice/(1+a1.FCess/100), a1.FAmount = b1.FPrice/(1+a1.FCess/100)*a1.FQty,
a1.FAuxTaxPrice = b1.FPrice, a1.FAllamount = b1.FPrice*a1.FQty, a1.FTaxAmount = b1.FPrice/(1+a1.FCess/100)*a1.FCess/100*a1.FQty,
a1.FAuxPriceDiscount = b1.FPrice*(1- b1.FDiscount/100), a1.FPriceDiscount = b1.FPrice*(1- b1.FDiscount/100), a1.FTaxPrice = b1.FPrice,
a1.FAmtDiscount = b1.FPrice/(1+a1.FCess/100)*a1.FQty* b1.FDiscount/100,
a1.FEntrySelfP0247= b1.FPrice*a1.FQty
FROM POOrderEntry a1
RIGHT JOIN #minFPrice b1 ON a1.FItemID=b1.FItemID
WHERE a1.FInterID=@FMaxNum --AND b1.FUsed =1
--3. 删除临时表
DROP TABLE #minFPrice
-- 审核采购订单
UPDATE POOrder SET FStatus=1,FCheckerID= @FCheckerID WHERE FInterID=@FMaxNum
END
END
END

iou198298

有意思，楼主要多来点这些分享，让我们这些新鸟长进长进

iou198298

此贴值得推荐

tzk

非常好的贴子，收藏使用。