Open Forum

Email Notification When Time Card is Rejected from Web

  • 1.  Email Notification When Time Card is Rejected from Web

    BRONZE CONTRIBUTOR
    Posted Jan 08, 2020 11:56 AM
    I need to customize the web apps functionality of rejecting a time card such that our payroll administrator is emailed when such a rejection occurs. It would also be nice to cc the rejected employee as well. We are not running Communicator so I am thinking about adding the email function to the SQL stored procedure that handles this method. Does anyone happen to know the stored procedure for processing time card approvals/rejections?
    I ran a SQL profile and couldn't find it.

    Thanks in advance!

    ------------------------------
    Susan Jeffries
    UNAVCO
    Boulder CO
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Email Notification When Time Card is Rejected from Web

    SILVER CONTRIBUTOR
    Posted Jan 08, 2020 01:15 PM
      |   view attached
    When a TC is rejected, it is an SQL communicator trigger that is implemented when configuring SQL for those emails.  When a record is inserted into the PJCOMMUN table, an email gets sent by SQL.  You don't necessarily have to have the SL Communicator program running in order to do this.  Just need to follow the steps in setting up SQL to send emails.  See attached zip file for the SQL trigger, and how to configure SQL for sending emails.

    I have created custom SQL triggers that send out other notifications (like when a payment is applied to an Invoice linked to a Project, the PM gets an email).  I imagine you could do something similar to create another record in the PJCOMMUN table that will send out an email to the PR Admin.  If you configure it correctly, it can work.  If you implement the SQL trigger, the notifications to EE's for rejected TC's will happen by design.

    ------------------------------
    Mark Ehleben
    President
    QBI Consulting, LLC
    Warminster PA
    ------------------------------

    Attachment(s)

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Email Notification When Time Card is Rejected from Web

    GOLD CONTRIBUTOR
    Posted Jan 08, 2020 02:18 PM
      |   view attached
    I agree with Mark, you can use Database Mail for this - you can either add as a trigger to the PJLABHDR table or you can actually execute the stored procedure from web apps directly during the rejection process.  To do this, you would go into the ProjectApprovalController and edit the PostCommitActions function.  You could have web apps execute a stored procedure that sends DB mail to the employee and the HR person and even include the reject reason in the email.

    I tweaked our web apps to remove the 'reject all' button so users have to click reject on the timecard itself.  Then, when the reject button is clicked, the comment box is automatically opened.  If they don't supply a comment, the status is set back to 'No Action' which forces them to enter a reject reason.

    Marc

    ------------------------------
    Marc DeLozier
    Finance SysAdmin
    David Evans and Associates Inc.
    Portland OR
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Email Notification When Time Card is Rejected from Web

    BRONZE CONTRIBUTOR
    Posted Jan 08, 2020 03:04 PM
    I'm not sure inserts are happening in my PJCOMMUN table. It only has 2 new records since we installed web apps and I know I have been done a lot of approvals the past few days. Perhaps you need to turn on COMMUNICATOR somewhere within the app?

    Anyhow, adding a trigger to PJLABHDR or altering the web service method are both great options.

    Thanks for help!

    ------------------------------
    Susan Jeffries
    UNAVCO
    Boulder CO
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Email Notification When Time Card is Rejected from Web

    SILVER CONTRIBUTOR
    Posted Jan 10, 2020 08:37 AM
    Regarding the PJCOMMUN table, there is nothing to turn on or enable.  If your user community has been advised to view and delete any messages within WebApps, then those records get removed from PJCOMMUN when they delete the messages.

    Using the Mail trigger along with the SQL Mail configuration will send out the emails for any new PJCOMMUN records entered into the table.  This will include any custom records you create that insert into that table.

    Whatever method you choose to insert new records into PJCOMMUN will send out the emails when inserted.

    ------------------------------
    Mark Ehleben
    President
    QBI Consulting, LLC
    Warminster PA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Email Notification When Time Card is Rejected from Web

    BRONZE CONTRIBUTOR
    Posted Jan 08, 2020 05:41 PM
    Also, how do I give the web apps permissions to execute sql send mail? What user needs this permission in Dynamics 2018?

    ------------------------------
    Susan Jeffries
    UNAVCO
    Boulder CO
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: Email Notification When Time Card is Rejected from Web

    GOLD CONTRIBUTOR
    Posted Jan 08, 2020 06:20 PM

    I would create a stored procedure that does the heavy lifting to send the DB Mail - probably with '@docnbr' as parameter.  The stored procedure would ​gather the necessary info (recipients, body of message, etc) then executes msdb.dbo.sp_send_dbmail

    To have web apps execute this new procedure, you will need to insert records into 2 tables in the SYS DB (WebServiceLookup & WebServiceLookupIt)  - that should be all the access web apps needs to execute.  They are simple inserts with 3 and 2 columns.

    To execute the SP from web apps controller, you can use lookuphelper as such:
         Dim lh As LookupHelper = New LookupHelper
         Dim r As DataRowCollection = lh.GetLookupExtended("zsp_DuplicateTimecard", {"@docnbr"}, {docnbr}, UIHelper.LoginSession, False)

    You can add error handling as needed: If r IsNot Nothing AndAlso r.Count = 1 Then....Else.....

    The above will execute a stored proc name zsp_DuplicateTimecard which expects '@docnbr' as parameter, passing local variable docnbr as the parameter value; the UIHelper.LoginSession is just something that is needed and False means don't prepend stored proc with paging info.

    Thanks,

    Marc



    ------------------------------
    Marc DeLozier
    Finance SysAdmin
    David Evans and Associates Inc.
    Portland OR
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: Email Notification When Time Card is Rejected from Web

    GOLD CONTRIBUTOR
    Posted Jan 09, 2020 08:35 AM
    Marc,

    In editing the web apps controller - would it be feasible to add an external web service call or library?  Instead of relying on database mail to handle the notification, using an email API/service such as MailGun gives you a lot more flexibility.
    https://documentation.mailgun.com/en/latest/libraries.html#libraries

    As our volume of email notifications grew, I realized I wanted my SQL server focused on DB transactions.  There's only a handful of times I'd rather use DB mail than an email service.


    ------------------------------
    Drew Skwiers-Koballa
    Director of IT
    Inside Edge CIS
    Eagan MN
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: Email Notification When Time Card is Rejected from Web

    GOLD CONTRIBUTOR
    Posted Jan 09, 2020 02:05 PM

    Hi Drew,
    Our volume hasn't warranted this, so I haven't tried that approach, but it could work.
    Maybe someone else has done this and could confirm?

    Thanks,
    Marc



    ------------------------------
    Marc DeLozier
    Finance SysAdmin
    David Evans and Associates Inc.
    Portland OR
    ------------------------------

    Academy - Online Interactive Learning from Experts