Want to send out an email after submit PO approval request

[Former Member]

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

[KennedyT21 · Accepted solution]

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

[KennedyT21]

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

[KennedyT21]

HI,

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

Regards

Kennedy

[Former Member]

Hi Kennedy,

Thanks for all the details information. Our client actually want to have an email from SAP every time after the PO request for specific PO.

I can let them create 2 PO approval procedure. One is for regular request. No email needed.

The other is for the specific PO. Once someone submit the request, we want to see an email come out. Currently I can only see the request from my SAP inbox.

I suggested them use alert (add a user query to the alert. The user query can show all the special PO request). But the alert will be every X hours not real time. It will send out all the special PO on one report not every time after the PO request created.

Regards,

Yuka

[malcolm_lamour]

Hi

I know its like a year later, but we recently (2 months ago) moved onto SAP Business One and this was also one of the business requirements

I hacked together a script that I run off sqlagent every 5 minutes to email people on a distribution list with the results in html format.

It's not perfect but it does the job for now.

It basically emails whenever a particular stage is approved of the PQ.

It sort of get around the issue of not being able to email outside of the organization and also the requirement that the user must be logged into SAP to receive the Alert.

Am I allowed to post it here so that it can be improved or is it against SAP rules.

I also requires your sql server (not SBO) to be able to send emails.

[Lartey]

Hi Malcolm,

Can you please share the script you created with us? I have a similar need where email alert is required for all approval request.

Regards

John

[malcolm_lamour]

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