SAP has a very interesting feature in S/4 HANA (cloud and on premise both) – E-Mail Templates.
In this blog post, we will learn how to embed table with multiple records in SE80 email template.
Example:
◉ Requirement is to send an email by end of the month to all employees who has pending hours in his/her time sheet. Data is taken from Std HR tables and pending hours is calculated using formula for each employee for project on which he is assigned. Pending hours = Planned hours – (Approved + Submitted) hours.
◉ A background job determines list of employees with project details for pending hours for each month and the program will trigger email to employees with a table embedded in email template having project details and the pending hours information for each project.
Topic covered in Blog Post:
◉ In current Blog Post we will only cover how to pass the table with multiple records in email template.
◉ Always use Visual Studio Code to edit the html code and test the html until satisfied and then copy in SE80 email template.
Settings in SE80 for Email Template:
SE80-Email Template Settings
SE80 Email Template Settings for (CDS view)
◉ TodaysDate, EmployeeName : Variables which will be populated dynamically from CDS view ZBASIS_GET_PENDING_TIMESHEETS on run time.
html generated table template
Html Code: I have added only Title and Content part of the html code
Title:
<!-- / Title -->
<table class="container title-block" border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td align="center" valign="top">
<table class="container" border="0" cellpadding="0" cellspacing="0" width="90%" style="">
<tr>
<td style="border-bottom: solid 1px #eeeeee; padding: 0 0 10px 0; font-size: 18px; color:#F16523" align="left">Employee Pending Time Sheets - ({{EmployeeName}})</td>
</tr>
</table>
</td>
</tr>
</table>
<!-- /// Title -->
Content: <!–///EmpContent–> in html code is the placeholder ,we will add table in html format. Please refer ABAP code for <!–///EmpContent–> for more details.
<!-- / Content -->
<table class="container paragraph-block" border="0" cellpadding="0" cellspacing="0" width="100%" >
<tr>
<td align="center" valign="top">
<table class="container" border="0" cellpadding="0" cellspacing="0" width="90%" >
<tr>
<td class="paragraph-block__content" style="padding: 15px 0 10px 0; font-size: 15px; line-height: 22px; color: #323232;" align="left">
Dear {{EmployeeName}},
<br/><br/>
Please note that you have remaining planned hour balances on the below listed projects that you may need to submit timesheets for.
</td>
</tr>
</table>
<table border="0" cellpadding="0" cellspacing="0" width="90%" style="border-collapse: collapse; border-spacing: 0; ">
<tbody class="list" style="text-align: left;"><tr><td>
<table border="1" cellpadding="3" style="border-collapse:collapse;font-size:14px" width="100%"> <tbody>
<tr class="attr-row header-row">
<th class="attr-col attr-row-header" style="width:400ppx;text-align: left;">EmpID</th>
<th class="attr-col attr-row-header" style="width:400ppx;text-align: left;">Employee</th>
<th class="attr-col attr-row-header" style="width:400ppx;text-align: left;">ProjectID </th>
<th class="attr-col attr-row-header" style="width:400ppx;text-align: left;">Project Name</th>
<th class="attr-col attr-row-header" style="width:200ppx;text-align: left;">Planned Hrs</th >
<th class="attr-col attr-row-header" style="width:200ppx;text-align: left;">Approved Hrs</th>
<th class="attr-col attr-row-header" style="width:200ppx;text-align: left;">Submitted Hrs</th>
<th class="attr-col attr-row-header" style="width:200ppx;text-align: left;">Remaining Hrs</th>
</tr> <!--///EmpContent--> “Placeholder for incorporating multiple records from internal table in email body”
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
</table>
<!-- /// Content -->
ABAP Code:
◉ We don’t want to send multiple email for an employee for each project instead we want to send only one email per employee adding all project details in the email body as shown in the final output of the email.
◉ i_output table has the records for the employee for all project details.
◉ lt_emp will have unique list of employees. We loop through each employee and generate the list of all project details in lv_content as included in ABAP code. I have added comments in abap code to help understand all steps.
" Get Distinct Projects Lists
DATA(lt_emp) = i_output.
SORT lt_emp BY PERNR ASCENDING.
DELETE ADJACENT DUPLICATES FROM lt_emp COMPARING PERNR.
WRITE / |Start of Processing Emails for { lines( lt_emp ) } |.
" Send one Email per employee for having pending time sheets
LOOP AT lt_emp INTO DATA(ls_emp).
CLEAR ls_timsh.
ls_timsh-pernr = ls_emp-pernr.
ls_timsh-prj_code = ls_emp-prj_code.
ls_timsh-pernr_name = ls_emp-pernr_name.
ls_timsh-prj_name = ls_emp-prj_name.
ls_timsh-WORKD = ls_emp-WORKD.
ls_timsh-CAT_APPROVED = ls_emp-CAT_APPROVED.
ls_timsh-CAT_PENDING = ls_emp-CAT_PENDING.
ls_timsh-cat_remaining = ls_emp-cat_remaining.
ls_timsh-pmemail = ls_emp-pmemail.
CLEAR lv_content.
" Build employee table for all projects
LOOP AT i_output INTO DATA(ls_output) WHERE pernr = ls_emp-pernr.
lv_content = |{ lv_content }<tr>|.
lv_content = |{ lv_content }<td style="width:400ppx">{ ls_output-pernr }</td>|.
lv_content = |{ lv_content }<td style="width:400ppx">{ ls_output-pernr_name }</td>|.
lv_content = |{ lv_content }<td style="width:400ppx">{ ls_output-prj_code }</td>|.
lv_content = |{ lv_content }<td style="width:400ppx">{ ls_output-prj_name }</td>|.
lv_content = |{ lv_content }<td style="width:200ppx">{ ls_output-WORKD }</td>|. "Planned hours
lv_content = |{ lv_content }<td style="width:200ppx">{ ls_output-CAT_APPROVED }</td>|. "Approved hours
lv_content = |{ lv_content }<td style="width:200ppx">{ ls_output-CAT_PENDING }</td>|. "Submitted hours
lv_content = |{ lv_content }<td style="width:200ppx">{ ls_output-cat_remaining }<td>|. "Remaining hours
lv_content = |{ lv_content }</tr>|.
ENDLOOP.
ls_timsh-emp_content = lv_content.
APPEND ls_timsh TO lt_timsh.
ENDLOOP.
“Pass the lv_content of all projects for each employee in the html format to the email body template
LOOP AT lt_timsh INTO ls_timsh.
WRITE / |Processing { ls_timsh-pernr }|.
CLEAR lv_alt_recp.
IF ls_timsh-pmemail IS INITIAL.
WRITE / | >> Error Sending - No Email |.
CONTINUE.
ELSEIF P_RECP_1 IS INITIAL.
lv_alt_recp = abap_true.
ENDIF.
TRY.
"c_template_id is ZBASIS_PENDING_TIME_SHEETS, refer to screen shots from SE80
DATA(oref_email_api) = cl_smtg_email_api=>get_instance( iv_template_id = c_template_id ).
TRY.
DATA(oref_bcs) = cl_bcs=>create_persistent( ).
DATA(i_cds_key) = VALUE x_data_key( ( name = 'EMPLOYEE' value = ls_timsh-pernr ) ).
TRY.
" Get the RawHTML Content and Replace the palceholder with employee and projects records in the email
oref_email_api->render( EXPORTING iv_language = c_english it_data_key = i_cds_key IMPORTING ev_body_html = DATA(body_html) ).
REPLACE '<!--///EmpContent-->' WITH ls_timsh-emp_content INTO body_html.
" Build HTML for Sending
DATA(body_html_soli) = cl_bcs_convert=>string_to_soli( body_html ).
DATA(multipart) = NEW cl_gbt_multirelated_service( ).
multipart->set_main_html(
EXPORTING
content = body_html_soli
description = 'EmployeeTimeSheetHtml' ).
" Set the Email Subject
lv_subject = |Employee Pending Time Sheets - ({ ls_timsh-pernr_name }) |.
" Create & Set the Email Document
DATA(doc_bcs) = cl_document_bcs=>create_from_multirelated(
EXPORTING
i_subject = lv_subject
i_multirel_service = multipart ).
oref_bcs->set_document( doc_bcs ).
TRY.
" Set Email Sender (SAP Workflow) - can be changed to any other user with email sending access.
DATA(oref_sender) = cl_sapuser_bcs=>create( 'SAP_WFRT' ).
TRY.
oref_bcs->set_sender( i_sender = oref_sender ).
" Set Email Receiver(s)
IF lv_alt_recp = abap_true.
DATA(oref_recipient) = cl_cam_address_bcs=>create_internet_address( ls_timsh-pmemail ).
oref_bcs->add_recipient( EXPORTING i_recipient = oref_recipient ).
ELSE.
oref_recipient = cl_cam_address_bcs=>create_internet_address( P_RECP_1 ).
oref_bcs->add_recipient( EXPORTING i_recipient = oref_recipient ).
ENDIF.
TRY.
" Send Email
IF c_test = abap_false.
oref_bcs->send( ).
ELSE.
WRITE | >> Test Mode (No Email Set)|.
ENDIF.
CATCH cx_send_req_bcs.
wa_message = VALUE #( msgid = sy-msgid msgno = sy-msgno attr1 = sy-msgv1 attr2 = sy-msgv2 attr3 = sy-msgv3 attr4 = sy-msgv4 ).
ENDTRY.
COMMIT WORK AND WAIT.
IF sy-subrc = 0 AND c_test = abap_false
WRITE | >> Succesfully Sent|.
ENDIF.
CATCH cx_send_req_bcs.
wa_message = VALUE #( msgid = sy-msgid msgno = sy-msgno attr1 = sy-msgv1 attr2 = sy-msgv2 attr3 = sy-msgv3 attr4 = sy-msgv4 ).
ENDTRY.
CATCH cx_address_bcs.
wa_message = VALUE #( msgid = sy-msgid msgno = sy-msgno attr1 = sy-msgv1 attr2 = sy-msgv2 attr3 = sy-msgv3 attr4 = sy-msgv4 ).
ENDTRY.
CATCH cx_smtg_email_common.
wa_message = VALUE #( msgid = sy-msgid msgno = sy-msgno attr1 = sy-msgv1 attr2 = sy-msgv2 attr3 = sy-msgv3 attr4 = sy-msgv4 ).
ENDTRY.
CATCH cx_send_req_bcs.
wa_message = VALUE #( msgid = sy-msgid msgno = sy-msgno attr1 = sy-msgv1 attr2 = sy-msgv2 attr3 = sy-msgv3 attr4 = sy-msgv4 ).
ENDTRY.
CATCH cx_smtg_email_common.
wa_message = VALUE #( msgid = sy-msgid msgno = sy-msgno attr1 = sy-msgv1 attr2 = sy-msgv2 attr3 = sy-msgv3 attr4 = sy-msgv4 ).
ENDTRY.
IF wa_message IS NOT INITIAL.
WRITE / | >> Error Sending - { wa_message-msgid } { wa_message-msgno } { wa_message-attr1 }|.
ENDIF.
CLEAR wa_message.
ENDLOOP.
Email Inbox Output:
Final Output: Screen shot of the email generated
No comments:
Post a Comment