Skip to Content
avatar image
Former Member

SAP B1 Alert Query when an international sales order is added

Our transport department has asked if it's possible to receive some form of alert when a sales order going outside of the UK is added onto SAP B1.

Looking back at the previous international sales order in B1, the only way to distinguish them is the currency is something other than GBP.

I have made a simply query which I have assigned to an alert which flags up all sales orders where the currency is NOT gbp, but this flags up every time I log into SAP showing all historical orders which are not gbp, I only really want to see new ones which have been added.

Is there a better way to do this?

SELECT T0.[DocCur], T0.[DocNum], T0.[DocType], T0.[CardCode], T0.[CardName], T0.[DocDate]

FROM ORDR T0

WHERE T0.[DocCur] NOT Like '%%gbp%%'
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Nov 08, 2016 at 02:45 AM

    You may try the below, this query will pull based on the server current and the create date(client pc's system date).

    select DocCur,DocNum,DocType,CardCode,CardName,DocDate From ORDR
    Where CreateDate=convert(varchar(15),GetDate(),110) and DocCur!='GBP'

    While creating alert may be you can enable save history, if required for the users.

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 08, 2016 at 06:49 AM

    Hi Nathan,

    Try This way ,

    select T0.DocCur,T0.DocNum,T0.DocType,T0.CardCode,T0.CardName,T0.DocDate From ORDR T0
    Where T0.CreateDate=getdate() and  T0.[DocCur]  NOT Like '%%gbp%%'
    
    
    
    Add comment
    10|10000 characters needed characters exceeded

  • Nov 08, 2016 at 12:11 PM

    Try configuring first sql mail and then combine with transaction notification

    Every time a new order is addedd when currency<>'Gbp' notifies in the email this creation

    if @object_type='17' and @transaction_type='A' 
    begin
    DECLARE @tableHTML NVARCHAR(MAX) ;
    DECLARE @ccmail NVARCHAR(MAX)
    --your mail id
    set @ccmail= 'your mail'
    SET @tableHTML =
    N'<H1>International Order</H1>' +
    N'<table border="1">' +
    N'<tr><th>Nº Docto:</th>'+
    N'<th>Date</th>' +
    N'<th>Customer</th>'+
    N'<th>Ref</th>'+
    N'<th>Currency</th>'+
    CAST ( ( SELECT td = T0.[DocNum], '',
    td = CONVERT(VARCHAR(10),T0.[DocDate],105), '',
    td = T0.[CardName], '',
    td = ISNULL(T0.[NumAtCard],'-'), '',
    td = T0.[DocCur], ''
    FROM [YourDataBase].dbo.ORDR T0 where T0.DocEntry = + @list_of_cols_val_tab_del and t0.doccur<>'Gbp'  
    FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;
    EXEC [msdb].[dbo].[sp_send_dbmail]
    @recipients = 'transportdepartmentmail', 
    @copy_recipients = 'transportdepartmentmail',
    @blind_copy_recipients='transportdepartmentmail',
    @subject = 'New International Order',
    @profile_name = 'SQLMailProfile',
    @body = @tableHTML,
    @body_format = 'HTML' ;
    end
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 09, 2016 at 01:08 PM

    Hi,

    In your query check the document status along with date. That will show only open sales orders thus not listing old historical sales orders.

    Regards

    Pradnya

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 09, 2016 at 01:31 PM
    Looking back at the previous international sales order in B1, the only way to distinguish them is the currency is something other than GBP.

    Why not Ship To Region ? If this is outside UK, you can very well conclude that the material is being shipped outside UK. May be in your business process, currency is different but this is predominently not the case in many projects.

    Add comment
    10|10000 characters needed characters exceeded