Skip to Content
avatar image
Former Member

Want to send out an email after submit PO approval request

Dear All,

Our client is on SAP882 CA localization. They have a PO approval procedure. The total amount > $5000 will need a supervisor approve it.

Then if OPOR.Indicator = 'SC' (PO for a specific group), they wish SAP will send out an email automatically (use SBO Mailer) to the supervisors.

Is there anyway we can make it work? I know we can have a user query in an alert and the alert can be send by email through SBO Mailer every X mins. But they want to have SAP send out email per request after it has been submitted. Thanks.

Regards,

Yuka

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Jun 23, 2012 at 06:55 AM

    Hi Jie Jin,

    While you define the --- Approval TemplateĀ 

    In Terms Tab

    Define what criteria, if any, are to be checked before triggering an approval procedure.

    This can be Always or based upon the fulfillment of one or more conditions.

    SAP Business One processes procedures with conditions prior to those that are marked Always.

    If you select the option When the Following Applies, two tables are displayed.

    In the upper table, you can select a predefined term (variable) in the Choose column.

    For example, to trigger an approval procedure, if the customer deviates from his credit limit by more than a certain amount, select Deviation from Credit Limit. Select the ratio that corresponds to the mathematical operation to compare the selected term to a threshold value. If the comparison of the selected term and the manually entered threshold value satisfies the selected ratio, the system activates the approval procedure.

    Send Mail.

    And the Same can be used in the Alert Management.if you have defined a release procedure for entering purchasing and sales documents, the workflow for an approval procedure also utilizes these messages.

    Alerts are optional and can be modified, deactivated or removed at any time.

    The following pre-defined Alerts are available for immediate use:

    Deviation from Credit Limit: Each business partner can have a predefined discount percentage. This Alert is triggered when a quotation or other type of document varies from this Credit Limit.

    Deviation from Commitment: Alerts when the Commitment limit, as defined by the sum of the credit limit and the cheques received but not cashed yet is breached.

    Deviation from % of Gross Profit: The conditions for the Profit Percentage in the document can be defined in the Conditions tab of the Alerts Management window. This Alert is triggered when a document has a gross profit less than this value.

    Deviation from Discount (in %): Each BUSINESS PARTNER can have a predefined discount percentage. This Alert is triggered when the discount for a certain document varies from this discount.

    Deviation from Budget: Alerts when a new Purchase Order is calculated and a divergence from the budget occurs.

    Minimum Stock Deviation: Detects when the inventory for an Item falls below the minimum level, which has been defined in the Item Master Record.

    For the above predefined alerts, the user can decide which documents to check and therefore limit or expand the scope of the alert.

    Alerts just notify the appropriate parties. They do not stop a document from being posted. The notified user can reply to the alert using the messaging function and this reply like the original alert can be sent internally, or externally via e-mail, SMS message, or fax

    Regards

    Kennedy

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 26, 2012 at 10:51 AM

    HI,

    Hope now you might get solution for automated mail, Pls close the tread with correct answer.

    Regards

    Kennedy

    Add comment
    10|10000 characters needed characters exceeded

    • Hi John

      Sure

      just add the recipients:

      CREATE TABLE ##TPO_Approvals (  -- Use ## to make it a global table so that send_mail can "see" it

      [DocEntry] varchar(11),[CardCode] varchar(15), [CardName] varchar(80),[Approver] varchar(15),[Originator] varchar(15),[Created] datetime,[Approved] varchar(10),[ApprovedDate] datetime,[Timetaken] varchar(20),

      )

      INSERT INTO ##TPO_Approvals

      SELECT T1.DocEntry, isnull(T6.CardCode, '-') as CardCode, isnull(T6.CardName,'-') as CardName, T3.U_NAME as Approver, T2.U_name as Originator,

      T0.CreateDate + STUFF(t0.CreateTime,case when len(t0.CreateTime)=3 then 2 else 3 end,0,':') AS Created, t1.Status As 'Approved',

      T0.UpdateDate + STUFF(t0.UpdateTime,case when len(t0.UpdateTime)=3 then 2 else 3 end,0,':') as [ApprovedDate]

      CAST(DATEDIFF(minute,T0.CreateDate + STUFF(t0.CreateTime,case when len(t0.CreateTime)=3 then 2 else 3 end,0,':'),

      T0.UpdateDate + STUFF(t0.UpdateTime,case when len(t0.UpdateTime)=3 then 2 else 3 end,0,':'))  / 1440 AS VARCHAR(12)) + ' day(s) ' + CONVERT(CHAR(8), DATEADD(MINUTE, DATEDIFF(minute,T0.CreateDate + STUFF(t0.CreateTime,case when len(t0.CreateTime)=3 then 2 else 3 end,0,':'),

      T0.UpdateDate + STUFF(t0.UpdateTime,case when len(t0.UpdateTime)=3 then 2 else 3 end,0,':'))  % 1440, '00:00'), 108)  as 'TimeTaken'

      FROM  [WDD1] T0 

      INNER  JOIN [OWDD] T1  ON  T1.[WddCode] = T0.[WddCode]  

      INNER  JOIN [OUSR] T2  ON  T2.[USERID] = T1.[OwnerID]  

      INNER  JOIN [OUSR] T3  ON  T3.[USERID] = T0.[UserID]  

      INNER  JOIN [OWTM] T4  ON  T4.[WtmCode] = T1.[WtmCode] 

      LEFT OUTER  JOIN [OPQT] T6  ON  T1.DocEntry = T6.[DocEntry]

      Where DATEDIFF(minute, T0.UpdateDate + STUFF(t0.UpdateTime,case when len(t0.UpdateTime)=3 then 2 else 3 end,0,':'),getdate())<=9

      and t1.Status='Y' and T6.CardCode is not NULL

      Group By T1.DocEntry, T6.CardCode, T6.CardName, T3.U_NAME, T2.U_name,T0.CreateDate, T0.UpdateDate, t0.CreateTime,t0.UpdateTime,t1.Status

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

      DECLARE @xml NVARCHAR(MAX)

      DECLARE @body NVARCHAR(MAX)

      SET @xml = CAST(( SELECT [DocEntry] AS 'td','',[CardCode] AS 'td','',

             [CardName] AS 'td','', [Approver] AS 'td','', [Originator] AS 'td',

             '', [Created] AS 'td','', [Approved] AS 'td','', [ApprovedDate] AS 'td','', [Timetaken] AS 'td'

      FROM  ##TPO_Approvals 

      ORDER BY [ApprovedDate]DESC

      FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

      SET @body ='<html><body><H3>Our Company PO Approved in Last 5 minutes</H3>

      <table border = 1>

      <tr><p style="font-family:arial;color:black;font-size:11px;">

      <th> DocEntry </th> <th> CardCode </th> <th> CardName </th> <th> Approver </th> <th> Originator </th><th> Created </th>

      <th> Approved </th><th> ApprovedDate </th><th> TimeTaken</th></tr>'   

      SET @body = @body + '<p style="font-family:arial;color:black;font-size:9px;">'+ @xml+ '</p> '+'</table></body></html>'

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

      DECLARE @the_count int

      SELECT @the_count = COUNT(*) FROM ##TPO_Approvals

      IF @the_count > 0

      BEGIN

      EXEC msdb.dbo.sp_send_dbmail

      --@query = 'SELECT * FROM ##TPO_Approvals',

      @recipients =  'user1@ourcompany.com',

      @body = @body,

      @body_format ='HTML',

      @subject = 'OurCompany SAP PO Approved'

      END

      DROP TABLE ##TPO_Approvals

  • May 05, 2013 at 03:22 AM

    Jie Jin

    Have you checked the above solutions, does the issue solved or not if not pls ask your questions

    if solved pls close the thread with correct or helpful answers šŸ˜Š

    Regards,

    Kennedy

    Add comment
    10|10000 characters needed characters exceeded