曾在一个项目中,使用B1内置的存储过程监控收款动作,如果针对于特定订单所做的收款总额符合指定条件后而进行相应的操作.我们知道在B1中,收款的ObjectType是24,因此,我们设定的条件是@ObjectType='24' AND @TransactionType='A'.
代码如下:
Code
/*****************************************************
预收款达到超过预收款比例的,自动更新为已批准。
收款达到全款,自动更新为已批准。
*****************************************************/
DECLARE @PaymentCode NVARCHAR(255)
DECLARE @OrderDocNum NVARCHAR(255)
DECLARE @OrderPaymentCount NUMERIC(19,6)
DECLARE @SubsistProportion NUMERIC(19,6)
DECLARE @OrderDocTotal NUMERIC(19,6)
IF @object_type='24' AND @transaction_type='A'
BEGIN
SET @PaymentCode=@list_of_cols_val_tab_del
SET @OrderDocNum=(SELECT CounterRef FROM ORCT WHERE DocEntry=@PaymentCode)
SET @OrderPaymentCount=(SELECT SUM(NoDocSum) AS Total FROM ORCT WHERE CounterRef=@OrderDocNum)
SET @CardCode=(SELECT CardCode FROM ORDR WHERE DocNum=@OrderDocNum)
SET @SubsistProportion=(SELECT U_SubsistProportion FROM OCRD WHERE CardCode=@CardCode)
SET @SubsistProportion=@SubsistProportion/100
SET @OrderDocTotal=(SELECT DocTotal FROM ORDR WHERE DocNum=@OrderDocNum)
SET @CardGroupCode=(SELECT GroupCode FROM OCRD WHERE CardCode=@CardCode)
-- 判断条件为预收款总金额大于订单总额*预收款比例并且客户组类型为'部分预付款'
IF @OrderPaymentCount>=@SubsistProportion*@OrderDocTotal AND @CardGroupCode='103'
BEGIN
UPDATE ORDR SET Confirmed='Y' WHERE DocNum=@OrderDocNum
--SELECT @error_message=@OrderDocNum+'->'
END
-- 判断条件为收款达到订单总额并且客户组类型为'现款现结'
IF @OrderPaymentCount>=@OrderDocTotal AND @CardGroupCode='100'
BEGIN
UPDATE ORDR SET Confirmed='Y' WHERE DocNum=@OrderDocNum
--SELECT @error_message=@OrderDocNum+'->'
END
END
但是,在升级至2007之后,我们发现,在做收款时,这段代码始终没有执行,我们使用存储过程返回的对象代码变成了30,而30则应该是日记账分录的ObjectType.但是,通过我们对存储过程的全程跟踪,我们发现:
2009-4-8 14:27:58;INFO;company is: B1Plus_Demo Message is: dbName:B1Plus_Demo oType:30 xType:A nofc:1 cols:TransId vals:5
2009-4-8 14:27:58;INFO;company is: B1Plus_Demo Message is: dbName:B1Plus_Demo oType:241 xType:A nofc:1 cols:CFTId vals:4
当用户做收款时,在2007B版本中,会同时产生两条数据,第一条30是日记账分录,第二条241是2005B中所不具备的对象,在2007B中,表示现金流行项目,而在2005B中的24收款对象,则没有再使用.为此调整了代码,尽管最终也解决了问题,但仍然对此调整感到不解.新的代码如下:
Code
/*****************************************************
预收款达到超过预收款比例的,自动更新为已批准。
收款达到全款,自动更新为已批准。
2009.04.08
由于升级至2007,收款时对象不再使用24,而是使用30(日记
账分录)和241(现金流相关行),因此调整代码.
*****************************************************/
DECLARE @JDTCode NVARCHAR(255)
DECLARE @TransType NVARCHAR(255)
DECLARE @PaymentCode NVARCHAR(255)
DECLARE @OrderDocNum NVARCHAR(255)
DECLARE @OrderPaymentCount NUMERIC(19,6)
DECLARE @SubsistProportion NUMERIC(19,6)
DECLARE @OrderDocTotal NUMERIC(19,6)
--select @error=1
select @error_message='@object_type: '+@object_type
IF @object_type='30' AND @transaction_type='A'
BEGIN
SET @JDTCode=@list_of_cols_val_tab_del
SET @TransType=(SELECT TransType FROM OJDT WHERE TransID=@JDTCode)
IF(@TransType='24')
BEGIN
SET @PaymentCode=(SELECT BaseRef FROM OJDT WHERE TransID=@JDTCode)
SET @OrderDocNum=(SELECT CounterRef FROM ORCT WHERE DocEntry=@PaymentCode)
SET @OrderPaymentCount=(SELECT SUM(NoDocSum) AS Total FROM ORCT WHERE CounterRef=@OrderDocNum)
SET @CardCode=(SELECT CardCode FROM ORDR WHERE DocNum=@OrderDocNum)
SET @SubsistProportion=(SELECT U_SubsistProportion FROM OCRD WHERE CardCode=@CardCode)
SET @SubsistProportion=@SubsistProportion/100
SET @OrderDocTotal=(SELECT DocTotal FROM ORDR WHERE DocNum=@OrderDocNum)
SET @CardGroupCode=(SELECT GroupCode FROM OCRD WHERE CardCode=@CardCode)
-- 判断条件为预收款总金额大于订单总额*预收款比例并且客户组类型为'部分预付款'
IF @OrderPaymentCount>=@SubsistProportion*@OrderDocTotal AND @CardGroupCode='103'
BEGIN
UPDATE ORDR SET Confirmed='Y' WHERE DocNum=@OrderDocNum
END
-- 判断条件为收款达到订单总额并且客户组类型为'现款现结'
IF @OrderPaymentCount>=@OrderDocTotal AND @CardGroupCode='100'
BEGIN
UPDATE ORDR SET Confirmed='Y' WHERE DocNum=@OrderDocNum
END
END
END