[eluser]The Casual Bot[/eluser]
im useing this as part of my ci project to intergrate with a mssql database i have a trigger on a sql table to excute but for some reason its returning null and i dont know why
Code:
CREATE TRIGGER CPRD_ABSORPTION_ACCOUNTING
ON ord_detail
FOR INSERT
AS
-------------------------------------------------------------------- CHECKS TO ENSURE THIS IS A CPRD SALE ---------------------------------------------------
DECLARE @NETT FLOAT
DECLARE @TYPE CHAR
DECLARE @ENTRY CHAR
DECLARE @COSTPRICE CHAR
SELECT @NETT = (select OD_NETT from inserted)
SELECT @TYPE = (select OD_TYPE from inserted)
SELECT @ENTRY = (select OD_ENTRY_TYPE from inserted)
SELECT @COSTPRICE = (select OD_COSTPRICE from inserted)
IF @NETT = 0
BEGIN
IF @TYPE = 'O'
BEGIN
IF @ENTRY = 'S'
BEGIN
IF @COSTPRICE <> 0
BEGIN
----------------------------------------------- DEFINEING VARIABLES AND GET DATA NEED FROM THE VIRTUAL INSERTED TABLE ---------------------------------
DECLARE @COST float
DECLARE @QTY float
DECLARE @ORDER float
DECLARE @DUTY float
DECLARE @FREIGHT float
DECLARE @TOTAL float
DECLARE @NUMBER INT
DECLARE @CONTROLACC1 float
DECLARE @CONTROLACC2 float
DECLARE @CONTROLACC3 float
--GET DATA NEED FROM THE VIRTUAL INSERTED TABLE
SELECT @COST = (select STK_COSTPRICE from inserted inner join STK_STOCK on inserted.OD_STOCK_CODE = STK_STOCK.STKCODE)
SELECT @QTY = (select OD_QTYORD from inserted)
SELECT @ORDER = (select OD_ORDER_NUMBER from inserted)
SELECT @DUTY = (select loc_landed_value1 from inserted INNER JOIN dbo.STK_LOCATION2 ON LOC_CODE2 = OD_LOCATN AND LOC_STOCKCODE2 = OD_STOCK_CODE )
SELECT @FREIGHT = (select loc_landed_value2 from inserted INNER JOIN dbo.STK_LOCATION2 ON LOC_CODE2 = OD_LOCATN AND LOC_STOCKCODE2 = OD_STOCK_CODE )
SELECT @CONTROLACC1 = (select SANOMINALCR from inserted inner join SL_ANALYSIS on od_analysis = sacode )
-------------------------------------------------------------------- TOTALS CALCULATIONS --------------------------------------------------------------------
--CALCULATE TOTAL COST
SELECT @COST = (@COST * @QTY)
--CALCULATE TOTAL DUTY
select @DUTY = (@COST / 100 * @DUTY)
select @DUTY = (@DUTY * @QTY)
--CALCULATE TOTAL FREIGTH
SELECT @FREIGHT = (@FREIGHT * @QTY)
--CALCULATE TOTAL COST
SELECT @TOTAL = (@COST + @DUTY + @FREIGHT)
--CALCULATE STOCK CONTROL ACCOUNT BASED ON NOMINAL WE ARE TRYING TO CALUCLATE
SELECT @CONTROLACC2 = (@CONTROLACC1 + 120000)
--CALCUATE COGS ACCOUNT BASED ON NOMINAL WE ARE TRYING TO CREDIT
SELECT @CONTROLACC3 = (@CONTROLACC1 + 130000)
-------------------------------------------------------------------- DIMENSIONS API --------------------------------------------------------------------
--GET THE NEXT JNL SEQUENCE NUMBER USING AD API
EXEC AA_GET_SEQUENCE_NO_S @PS_Sequence_Field = 'NL_TRAN_ID', @PS_Increment = 1, @PS_Sequence_No = @NUMBER OUTPUT
--INSERTING INTO DIMENSIONS API TEMP TABLE READY FOR EXCUTEING JNL
insert into nl_trn_temp (NL_DO_NOT_BATCH, NL_TRAN_ID, NL_DETAIL_LINE_NO, NL_STATUS_TEMP, NL_USER_ID, NL_MODULE, NL_TRANSACTION_TYPE, NL_CREDIT_DEBIT_TRANSACTION, NL_JOURNAL_ACCOUNT , NL_HOME_TRANSACTION_VALUE, NL_DESCRIPTION )
VALUES (1, @NUMBER, 1, 2, 'SA', 'NL', 'JNL', 'DR', @CONTROLACC3, @TOTAL, @ORDER )
insert into nl_trn_temp (NL_DO_NOT_BATCH, NL_TRAN_ID, NL_DETAIL_LINE_NO, NL_STATUS_TEMP, NL_USER_ID, NL_MODULE, NL_TRANSACTION_TYPE, NL_CREDIT_DEBIT_TRANSACTION, NL_JOURNAL_ACCOUNT , NL_HOME_TRANSACTION_VALUE, NL_DESCRIPTION )
VALUES (1, @NUMBER, 2, 2, 'SA', 'NL', 'JNL', 'CR', @CONTROLACC2, @TOTAL, @ORDER )
--now exec and post JNL
EXEC AA_PROCESS_NL_TRN_TEMP_S @PS_TRN_TEMP_ID = @NUMBER, @PS_USER_ID ='SA'
-------------------------------------------------------------------- END OF CHECK IF STATMENTS --------------------------------------------------------
END
END
END
END
iv narrowed the problem down to the following line
Code:
--CALCULATE TOTAL COST
SELECT @TOTAL = (@COST + @DUTY + @FREIGHT)
which when it runs they should be set as follows
Code:
@total = null
@cost = 10
@duty = 0
@freight = 0
iv triple checked the inputs
and when i change this line to
Code:
--CALCULATE TOTAL COST
SELECT @TOTAL = (@COST)
it works
please please help
