Skip to Content
author's profile photo Former Member
Former Member

SP Transaction Notification

Hellow, i begin in SAP Business One, i have used SP Transaction Notification for Rate in my transaction, i want to control my actuel rate, and there is my T-SQL code:

declare @docrateE decimal

declare @docrateD Decimal

declare @BorneI Decimal

Declare @BoreneS Decimal

Declare @DateDoc datetime

declare @doccur nchar(7)

declare @error int -- Result (0 for no error)

declare @error_message nvarchar (200) -- Error string to be displayed

select @error = 0

select @error_message = N'Ok'

--------------------------------------------------------------------------------------------------------------------------------

-- ADD YOUR CODE HERE

/*********************************************Table des taux de change**********************************/

/*******************************************************************************************************/

/*************************************Facture Fournisseur***********************************************/

IF @object_type='18' --Test Sur le Document

begin

set @DateDoc = ( select docdate from OPCH where DocEntry = CONVERT(int, @list_of_cols_val_tab_del))

set @docrateD= (select docrate from OPCH where DocEntry = CONVERT (int, @list_of_cols_val_tab_del))--Tau de change de la factureF

set @doccur= ( select DocCur from OPCH WHERE OPCH.DocEntry = CONVERT (int, @list_of_cols_val_tab_del)) -- La devise de la factureF

set @docrateE=(SELECT TOP 1 [Rate] FROM ORTT where Currency= @doccur and RateDate<@DateDoc ORDER BY RateDate DESC)

if(@docrateE is not null)

begin

set @BorneI= @docrateE - (@docrateE*0.25)

set @BoreneS=@docrateE + (@docrateE*0.25)

if (@BoreneS<@docrated and @docrateD <@BorneI)

begin

SELECT @error = 1, @error_message = 'Le Taux de change ne peut pas dépasser cette valeur saisie ' -- Blmoquer et Réfuser la valeur de tau

end

end

end

/****************************************Avoir Fournisseur*********************************************/

IF @object_type='19' --Test Sur le Document

begin

set @DateDoc = ( select docdate from ORPC where DocEntry = CONVERT(int, @list_of_cols_val_tab_del))

set @docrateD= (select docrate from ORPC WHERE ORPC.DocEntry = CONVERT (int, @list_of_cols_val_tab_del))--Tau de change de la Reception D'achat

set @doccur= ( select DocCur from ORPC WHERE ORPC.DocEntry = CONVERT (int, @list_of_cols_val_tab_del))

set @docrateE=(SELECT TOP 1 [Rate] FROM ORTT where Currency= @doccur and RateDate<@DateDoc ORDER BY RateDate DESC)

if(@docrateE is not null)

begin

set @BorneI= @docrateE - (@docrateE*0.3)

set @BoreneS=@docrateE + (@docrateE*0.3)

if (@BoreneS<@docrateD and @docrated <@BorneI)

begin

SELECT @error = 1, @error_message = 'Le Taux de change ne peut pas dépasser cette valeur saisie ' -- Blmoquer et Réfuser la valeur de tau

end

end

end

/****************************************Reception D'achat*********************************************/

IF @object_type='20' --Test Sur le Document

begin

set @DateDoc = ( select docdate from OPDN where DocEntry = CONVERT(int, @list_of_cols_val_tab_del))

set @docrateD= (select docrate from OPDN WHERE OPDN.DocEntry = CONVERT (int, @list_of_cols_val_tab_del))--Tau de change de la Reception D'achat

set @doccur= ( select DocCur from OPDN WHERE OPDN.DocEntry = CONVERT (int, @list_of_cols_val_tab_del)) -- La devise de la Reception D'achat

set @docrateE=(SELECT TOP 1 [Rate] FROM ORTT where Currency= @doccur and RateDate<@DateDoc ORDER BY RateDate DESC)

if(@docrateE is not null)

begin

set @BorneI= @docrateE - (@docrateE*0.3)

set @BoreneS=@docrateE + (@docrateE*0.3)

if (@BoreneS<@docrateD and @docrateD <@BorneI)

begin

SELECT @error = 1, @error_message = 'Le Taux de change ne peut pas dépasser cette valeur saisie ' -- Blmoquer et Réfuser la valeur de tau

end

end

end

/*****************************************************************************************************/

--------------------------------------------------------------------------------------------------------------------------------

-- Select the return values

--Select the return values

select @error, @error_message

end

Thank's

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Nov 25, 2012 at 07:17 PM

    Hi Amina

    You must also include the transaction type in your code as follows:

    IF @transaction_type = 'A' AND @object_type = '18'.

    A - Add

    U - Update

    Kind regards

    Peter Juby

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 26, 2012 at 01:33 AM

    Hi Amina DIF.....

    Try this

    IF @object_type='18' and @transaction_type in ( 'A' ,'U')--Test Sur le Document

    begin

    set @DateDoc = ( select docdate from OPCH where DocEntry = CONVERT(int, @list_of_cols_val_tab_del))

    set @docrateD= (select docrate from OPCH where DocEntry = CONVERT (int, @list_of_cols_val_tab_del))--Tau de change de la factureF

    set @doccur= ( select DocCur from OPCH WHERE OPCH.DocEntry = CONVERT (int, @list_of_cols_val_tab_del)) -- La devise de la factureF

    set @docrateE=(SELECT TOP 1 [Rate] FROM ORTT where Currency= @doccur and RateDate<@DateDoc ORDER BY RateDate DESC)

    if(@docrateE is not null)

    begin

    set @BorneI= @docrateE - (@docrateE*0.25)

    set @BoreneS=@docrateE + (@docrateE*0.25)

    if (@BoreneS<@docrated and @docrateD <@BorneI)

    begin

    SELECT @error = 1, @error_message = 'Le Taux de change ne peut pas dépasser cette valeur saisie ' -- Blmoquer et Réfuser la valeur de tau

    end

    end

    end

    /****************************************Avoir Fournisseur*********************************************/

    IF @object_type='19'and @transaction_type in ( 'A' ,'U') --Test Sur le Document

    begin

    set @DateDoc = ( select docdate from ORPC where DocEntry = CONVERT(int, @list_of_cols_val_tab_del))

    set @docrateD= (select docrate from ORPC WHERE ORPC.DocEntry = CONVERT (int, @list_of_cols_val_tab_del))--Tau de change de la Reception D'achat

    set @doccur= ( select DocCur from ORPC WHERE ORPC.DocEntry = CONVERT (int, @list_of_cols_val_tab_del))

    set @docrateE=(SELECT TOP 1 [Rate] FROM ORTT where Currency= @doccur and RateDate<@DateDoc ORDER BY RateDate DESC)

    if(@docrateE is not null)

    begin

    set @BorneI= @docrateE - (@docrateE*0.3)

    set @BoreneS=@docrateE + (@docrateE*0.3)

    if (@BoreneS<@docrateD and @docrated <@BorneI)

    begin

    SELECT @error = 1, @error_message = 'Le Taux de change ne peut pas dépasser cette valeur saisie ' -- Blmoquer et Réfuser la valeur de tau

    end

    end

    end

    /****************************************Reception D'achat*********************************************/

    IF @object_type='20' and @transaction_type in ( 'A' ,'U') --Test Sur le Document

    begin

    set @DateDoc = ( select docdate from OPDN where DocEntry = CONVERT(int, @list_of_cols_val_tab_del))

    set @docrateD= (select docrate from OPDN WHERE OPDN.DocEntry = CONVERT (int, @list_of_cols_val_tab_del))--Tau de change de la Reception D'achat

    set @doccur= ( select DocCur from OPDN WHERE OPDN.DocEntry = CONVERT (int, @list_of_cols_val_tab_del)) -- La devise de la Reception D'achat

    set @docrateE=(SELECT TOP 1 [Rate] FROM ORTT where Currency= @doccur and RateDate<@DateDoc ORDER BY RateDate DESC)

    if(@docrateE is not null)

    begin

    set @BorneI= @docrateE - (@docrateE*0.3)

    set @BoreneS=@docrateE + (@docrateE*0.3)

    if (@BoreneS<@docrateD and @docrateD <@BorneI)

    begin

    SELECT @error = 1, @error_message = 'Le Taux de change ne peut pas dépasser cette valeur saisie ' -- Blmoquer et Réfuser la valeur de tau

    end

    end

    end

    /*****************************************************************************************************/

    --------------------------------------------------------------------------------------------------------------------------------

    -- Select the return values

    --Select the return values

    select @error, @error_message

    end

    Regards

    Kennedy

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.