In my previous articles we have seen some basic reports in SAP and some basic operations that we can perform on the SAP tables and reports. In this article we will see how can we show data of header database table in SAP and send alert emails using ALV Grid Report. Below is the Program that can be used to show data of header database table and to send email alerts in SAP.
Program to show data of database table
REPORT ZBILL_ALV_REPORT.
*&---------------------------------------------------------------------*
*& Report ZBILL_ALV_GRID_REPORT
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
TABLES: ZBILL.
TYPE-POOLS SLIS.
" ---- WORK-AREA AND INTERNAL TABLE DECLARATION ----
DATA: WA_BILL TYPE ZBILL,
IT_BILL TYPE ZBILL OCCURS 0.
DATA: REP_ID TYPE SY-REPID VALUE SY-REPID.
DATA:
*-----DECLARING THE FIELD CATALOG WORK AREA & INTERNAL TABLE-----------*
WA_FCAT TYPE SLIS_FIELDCAT_ALV,
IT_FCAT TYPE SLIS_T_FIELDCAT_ALV,
*-----DECLARING THE WORK AREA OF ALV LAYOUT----------------------------*
WA_LAYOUT TYPE SLIS_LAYOUT_ALV,
LS_VARIANT type DISVARIANT,
*-----DECLARING THE WORK AREA & INTERNAL TABLE FOR TOP OF PAGE---------*
WA_TOP TYPE SLIS_LISTHEADER,
IT_TOP TYPE SLIS_T_LISTHEADER.
START-OF-SELECTION. " for selection of data from database.
* SET PF-STATUS 'SAVESTS'.
PERFORM GET_BILLS.
END-OF-SELECTION.
PERFORM ALV_LAYOUT.
PERFORM ALV_FIELD_CATALOG.
PERFORM ALV_GRID_DISPLAY.
TOP-OF-PAGE.
PERFORM TOP_OF_BILL.
*&---------------------------------------------------------------------*
*& Form GET_BILLS
*&---------------------------------------------------------------------*
* Selection of BILL table data
*----------------------------------------------------------------------*
FORM GET_BILLS.
SELECT * INTO CORRESPONDING FIELDS OF TABLE IT_BILL FROM ZBILL.
* SELECT * INTO CORRESPONDING FIELDS OF TABLE IT_BILL FROM ZBILL WHERE DOC_ID IN P_DOC_ID.
IF SY-SUBRC = 0.
SORT IT_BILL.
ELSE.
MESSAGE 'Bill Records doesn''t exist' TYPE 'I'.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form alv_field_catalog
*&---------------------------------------------------------------------*
* Preparing ALV field catalog
*----------------------------------------------------------------------*
FORM ALV_FIELD_CATALOG.
DATA LV_COL TYPE I VALUE 0.
IF NOT IT_BILL IS INITIAL.
LV_COL = 1 + LV_COL. "INCREMENTING POSITION OF COLUMN
WA_FCAT-COL_POS = LV_COL. "COLUMN POSITION
WA_FCAT-FIELDNAME = 'DOC_ID'. "TECHNICAL FIELD NAME
WA_FCAT-TABNAME = 'IT_BILL'. "OUTPUT TABLE NAME
WA_FCAT-SELTEXT_L = 'Document ID'. "FIELD TEXT
WA_FCAT-EMPHASIZE = 'C10'. "FOR COLUMN COLOR
* WA_LAYOUT-COLTAB_FIELDNAME = 'CELLCOLOR'.
APPEND WA_FCAT TO IT_FCAT. "PREPARING THE FIELDCAT TABLE
CLEAR WA_FCAT.
LV_COL = 1 + LV_COL.
WA_FCAT-COL_POS = LV_COL. "COLUMN POSITION
WA_FCAT-FIELDNAME = 'BILNO'. "TECHNICAL FIELD NAME
WA_FCAT-TABNAME = 'IT_BILL'. "OUTPUT TABLE NAME
WA_FCAT-SELTEXT_L = 'Bill Number'. "FIELD TEXT
WA_FCAT-EMPHASIZE = 'C21'.
APPEND WA_FCAT TO IT_FCAT. "PREPARING THE FIELDCAT TABLE
CLEAR WA_FCAT.
LV_COL = 1 + LV_COL.
WA_FCAT-COL_POS = LV_COL. "COLUMN POSITION
WA_FCAT-FIELDNAME = 'BILDESC'. "TECHNICAL FIELD NAME
WA_FCAT-TABNAME = 'IT_BILL'. "OUTPUT TABLE NAME
WA_FCAT-SELTEXT_L = 'Description'. "FIELD TEXT
WA_FCAT-EMPHASIZE = 'C21'.
APPEND WA_FCAT TO IT_FCAT. "PREPARING THE FIELDCAT TABLE
CLEAR WA_FCAT.
LV_COL = 1 + LV_COL.
WA_FCAT-COL_POS = LV_COL. "COLUMN POSITION
WA_FCAT-FIELDNAME = 'QUANTITY'. "TECHNICAL FIELD NAME
WA_FCAT-TABNAME = 'IT_BILL'. "OUTPUT TABLE NAME
WA_FCAT-SELTEXT_L = 'Total Quantity'. "FIELD TEXT
WA_FCAT-EMPHASIZE = 'C21'.
APPEND WA_FCAT TO IT_FCAT. "PREPARING THE FIELDCAT TABLE
CLEAR WA_FCAT.
LV_COL = 1 + LV_COL.
WA_FCAT-COL_POS = LV_COL. "COLUMN POSITION
WA_FCAT-FIELDNAME = 'AMOUNT'. "TECHNICAL FIELD NAME
WA_FCAT-TABNAME = 'IT_BILL'. "OUTPUT TABLE NAME
WA_FCAT-SELTEXT_L = 'AMOUNT'. "FIELD TEXT
WA_FCAT-EMPHASIZE = 'C30'.
* WA_FCAT-EDIT = 'X'.
WA_FCAT-DO_SUM = 'X'.
APPEND WA_FCAT TO IT_FCAT. "PREPARING THE FIELDCAT TABLE
CLEAR WA_FCAT.
LV_COL = 1 + LV_COL.
WA_FCAT-COL_POS = LV_COL. "COLUMN POSITION
WA_FCAT-FIELDNAME = 'BILPMOD'. "TECHNICAL FIELD NAME
WA_FCAT-TABNAME = 'IT_BILL'. "OUTPUT TABLE NAME
WA_FCAT-SELTEXT_L = 'Payment Mode'. "FIELD TEXT
* WA_FCAT-EMPHASIZE = 'C2'.
WA_FCAT-EDIT = 'X'.
* WA_FCAT-DO_SUM = 'X'.
APPEND WA_FCAT TO IT_FCAT. "PREPARING THE FIELDCAT TABLE
CLEAR WA_FCAT.
ENDIF.
ENDFORM. " end alv_field_catalog
*&---------------------------------------------------------------------*
*& Form alv_layout
*&---------------------------------------------------------------------*
* Preparing the ALV Layout
*----------------------------------------------------------------------*
FORM ALV_LAYOUT.
WA_LAYOUT-ZEBRA = 'X'. "CALLING THE ZEBRA LAYOUT
WA_LAYOUT-COLWIDTH_OPTIMIZE = 'X'.
ENDFORM.
FORM TOP_OF_BILL.
REFRESH IT_TOP.
WA_TOP-TYP = 'H'. "HEADER TYPE
WA_TOP-INFO = 'Technocrats Club.'. "HEADER TEXT
APPEND WA_TOP TO IT_TOP.
CLEAR WA_TOP.
WA_TOP-TYP = 'S'. "NORMAL LINE TYPE
WA_TOP-KEY = 'Report:'. "NORMAL LINE TEXT
WA_TOP-INFO = SY-REPID.
* CONCATENATE WA_TOP-KEY SY-REPID INTO WA_TOP-INFO SEPARATED BY SPACE.
"CONCATENATING THE TEXT INFO WITH PROGRAM NAME
APPEND WA_TOP TO IT_TOP.
CLEAR WA_TOP.
WA_TOP-TYP = 'S'.
WA_TOP-KEY = 'User:'.
WA_TOP-INFO = SY-UNAME.
* CONCATENATE WA_TOP-INFO SY-UNAME INTO WA_TOP-INFO SEPARATED BY SPACE.
"CONCATENATING THE TEXT INFO WITH USER NAME
APPEND WA_TOP TO IT_TOP.
CLEAR WA_TOP.
DATA: DATUM type c LENGTH 10. " CONVERT DATE TO EXTERNAL.
WRITE SY-DATUM TO DATUM.
WA_TOP-TYP = 'S'.
WA_TOP-KEY = 'Date:'.
WA_TOP-INFO = DATUM.
* CONCATENATE WA_TOP-INFO SY-DATUM INTO WA_TOP-INFO SEPARATED BY SPACE.
"CONCATENATING THE TEXT INFO WITH USER NAME
APPEND WA_TOP TO IT_TOP.
CLEAR WA_TOP.
DATA : S_TIME TYPE C LENGTH 20. "TIME CONVERSION TO EXTERNAL
WRITE SY-UZEIT TO S_TIME.
WA_TOP-TYP = 'S'.
WA_TOP-KEY = 'Time:'.
WA_TOP-INFO = S_TIME.
* CONCATENATE WA_TOP-INFO SY-UZEIT INTO WA_TOP-INFO SEPARATED BY SPACE.
"CONCATENATING THE TEXT INFO WITH USER NAME
APPEND WA_TOP TO IT_TOP.
CLEAR WA_TOP.
"-CALLING FUNCTION MODULE FOR DISPLAYING TOP OF PAGE-------------------*
CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE'
EXPORTING
IT_LIST_COMMENTARY = IT_TOP "PASSING THE INTERNAL TABLE
I_LOGO = 'ENJOYSAP_LOGO'
* I_END_OF_LIST_GRID =
* I_ALV_FORM =
.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form alv_grid_display
*&---------------------------------------------------------------------*
* Preparing the final output by using Grid Display
*----------------------------------------------------------------------*
FORM ALV_GRID_DISPLAY.
IF NOT IT_BILL IS INITIAL AND NOT IT_FCAT IS INITIAL.
* CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
* CALL FUNCTION 'CL_GUI_ALV_GRID'
EXPORTING
* I_INTERFACE_CHECK = ' '
* I_BYPASSING_BUFFER = ' '
* I_BUFFER_ACTIVE = ' '
I_CALLBACK_PROGRAM = SY-REPID
I_CALLBACK_PF_STATUS_SET = 'PF'
I_CALLBACK_USER_COMMAND = 'USER_COMMAND'
I_CALLBACK_TOP_OF_PAGE = 'TOP_OF_BILL'
* I_CALLBACK_HTML_TOP_OF_PAGE = ' '
* I_CALLBACK_HTML_END_OF_LIST = ' '
* I_STRUCTURE_NAME =
* I_BACKGROUND_ID = ' '
I_GRID_TITLE = 'Bill Records'
* I_GRID_SETTINGS =
IS_LAYOUT = WA_LAYOUT
IT_FIELDCAT = IT_FCAT
* IT_EXCLUDING =
* IT_SPECIAL_GROUPS =
* IT_SORT = ITSORT
* IT_EXCLUDING =
* IT_SPECIAL_GROUPS =
* IT_FILTER =
* IS_SEL_HIDE =
* I_DEFAULT = 'X'
* I_SAVE = 'X'
* IS_VARIANT = LS_VARIANT
* IT_EVENTS = LI_EVENTS
* IT_EVENT_EXIT =
* IS_PRINT =
* IS_REPREP_ID =
* I_SCREEN_START_COLUMN = 0
* I_SCREEN_START_LINE = 0
* I_SCREEN_END_COLUMN = 0
* I_SCREEN_END_LINE = 0
* I_HTML_HEIGHT_TOP = 0
* I_HTML_HEIGHT_END = 0
* IT_ALV_GRAPHICS =
* IT_HYPERLINK =
* IT_ADD_FIELDCAT =
* IT_EXCEPT_QINFO =
* IR_SALV_FULLSCREEN_ADAPTER =
TABLES
T_OUTTAB = IT_BILL.
IF SY-SUBRC <> 0.
* Implement suitable error handling here
ENDIF.
ENDIF.
ENDFORM.
FORM USER_COMMAND USING R_UCOMM LIKE SY-UCOMM
RS_SELFIELD TYPE SLIS_SELFIELD.
CASE R_UCOMM.
WHEN 'SENDMAIL'.
IF IT_BILL IS NOT INITIAL.
SUBMIT ZBILL_ALV_REPORT_ALERT AND RETURN.
ENDIF.
ENDCASE.
ENDFORM.
FORM PF USING RT_EXTAB TYPE SLIS_T_EXTAB.
SET PF-STATUS 'SAVESTS'. ""<--PASS YOUR PF-STATUS CREATED
ENDFORM.
Output
Program of Making alert for sending mail using send mail button
REPORT ZBILL_ALV_REPORT_ALERT.
DATA: WA_BILL TYPE ZBILL,
IT_BILL TYPE ZBILL OCCURS 0.
DATA: IT_OBJBIN TYPE STANDARD TABLE OF SOLISTI1, " Attachment data
IT_OBJTXT TYPE STANDARD TABLE OF SOLISTI1, " Message body
IT_OBJPACK TYPE STANDARD TABLE OF SOPCKLSTI1, " Packing list
IT_RECLIST TYPE STANDARD TABLE OF SOMLRECI1, " Receipient list
IT_OBJHEAD TYPE STANDARD TABLE OF SOLISTI1. " Header
*-------WORK AREA FOR SENDING EMAILS--------
DATA: WA_DOCDATA TYPE SODOCCHGI1, " Document data
WA_OBJTXT TYPE SOLISTI1, " Message body
WA_OBJBIN TYPE SOLISTI1, " Attachment data
WA_OBJPACK TYPE SOPCKLSTI1, " Packing list
WA_OBJPACK2 TYPE SOPCKLSTI1, " Packing list
WA_RECLIST TYPE SOMLRECI1, " Receipient list
* W_DOCUMENT_DATA TYPE SODOCCHGI1,
OUT_CHAR(15),
W_TAB_LINES TYPE I.
DATA : IT_SMTP TYPE BAPIADSMTP OCCURS 0 WITH HEADER LINE.
DATA : IT_BAPIRET TYPE BAPIRET2 OCCURS 0 WITH HEADER LINE.
DATA : IT_MSEG LIKE MSEG OCCURS 0 WITH HEADER LINE.
SELECT * INTO CORRESPONDING FIELDS OF TABLE IT_BILL FROM ZBILL.
IF IT_BILL IS NOT INITIAL.
PERFORM CREATE_MESSAGE.
PERFORM SEND_MESSAGE.
ENDIF.
*-----------code for bill alert email---------
FORM CREATE_MESSAGE.
CLEAR : IT_OBJTXT, OUT_CHAR.
CLEAR: WA_OBJPACK.
WRITE SY-DATUM DD/MM/YYYY TO OUT_CHAR.
WA_DOCDATA-OBJ_NAME = 'Alert:For Bill Records'.
CONCATENATE 'Date:' OUT_CHAR INTO WA_DOCDATA-OBJ_DESCR SEPARATED BY SPACE.
*----Message Body In HTML----
WA_OBJTXT-LINE = '<html> <body>'.
APPEND WA_OBJTXT TO IT_OBJTXT.
WA_OBJTXT-LINE = '<div style="border:1px solid;"> <div align="center">Bill Records</div>'.
APPEND WA_OBJTXT TO IT_OBJTXT.
WA_OBJTXT-LINE = '<table align="center">'.
APPEND WA_OBJTXT TO IT_OBJTXT.
WA_OBJTXT-LINE = '<tr>'.
APPEND WA_OBJTXT TO IT_OBJTXT.
WA_OBJTXT-LINE = '<th style="background-color:#cfd5db;color:#000000;text-align:center;">Document Id</th>'.
APPEND WA_OBJTXT TO IT_OBJTXT.
WA_OBJTXT-LINE = '<th style="background-color:#cfd5db;color:#000000;text-align:center;">Bill No.</th>'.
APPEND WA_OBJTXT TO IT_OBJTXT.
WA_OBJTXT-LINE = '<th style="background-color:#cfd5db;color:#000000;text-align:center;">Bill Desc.</th>'.
APPEND WA_OBJTXT TO IT_OBJTXT.
WA_OBJTXT-LINE = '<th style="background-color:#cfd5db;color:#000000;text-align:center;">Total Qty.</th>'.
APPEND WA_OBJTXT TO IT_OBJTXT.
WA_OBJTXT-LINE = '<th style="background-color:#cfd5db;color:#000000;text-align:center;">Total Amt.</th>'.
APPEND WA_OBJTXT TO IT_OBJTXT.
WA_OBJTXT-LINE = '<th style="background-color:#cfd5db;color:#000000;text-align:center;">Date</th>'.
APPEND WA_OBJTXT TO IT_OBJTXT.
WA_OBJTXT-LINE = '</tr>'.
APPEND WA_OBJTXT TO IT_OBJTXT.
LOOP AT IT_BILL INTO WA_BILL.
WA_OBJTXT-LINE = '<tr>'.
APPEND WA_OBJTXT TO IT_OBJTXT.
CONCATENATE '<td style="background-color:#c5eaee;border:1px solid;border-color:#72ccd6;text-align:right;">' WA_BILL-DOC_ID '</td>' INTO WA_OBJTXT.
APPEND WA_OBJTXT TO IT_OBJTXT.
CONCATENATE '<td style="background-color:#f1f5fe;border:1px solid;border-color:#c4c4ff;text-align:right;">' WA_BILL-BILNO '</td>' INTO WA_OBJTXT.
APPEND WA_OBJTXT TO IT_OBJTXT.
CONCATENATE '<td style="background-color:#d5e3f2;border:1px solid;border-color:#6f9ed2;text-align:left;">' WA_BILL-BILDESC '</td>' INTO WA_OBJTXT.
APPEND WA_OBJTXT TO IT_OBJTXT.
CLEAR : OUT_CHAR.
WRITE WA_BILL-QUANTITY TO OUT_CHAR.
CONCATENATE '<td style="background-color:#d5e3f2;border:1px solid;border-color:#6f9ed2;text-align:right;">' OUT_CHAR '</td>' INTO WA_OBJTXT.
APPEND WA_OBJTXT TO IT_OBJTXT.
CLEAR : OUT_CHAR.
WRITE : WA_BILL-AMOUNT TO OUT_CHAR.
CONCATENATE '<td style="background-color:#fffdbf;border:1px solid;border-color:#fffb46;text-align:right">' OUT_CHAR '</td>' INTO WA_OBJTXT.
APPEND WA_OBJTXT TO IT_OBJTXT.
CLEAR : OUT_CHAR.
WRITE : WA_BILL-BILDATE DD/MM/YYYY TO OUT_CHAR.
CONCATENATE '<td style="background-color:#d5e3f2;border:1px solid;border-color:#6f9ed2;text-align:right">' OUT_CHAR '</td>' INTO WA_OBJTXT.
APPEND WA_OBJTXT TO IT_OBJTXT.
WA_OBJTXT-LINE = '</tr>'.
APPEND WA_OBJTXT TO IT_OBJTXT.
ENDLOOP.
WA_OBJTXT-LINE = '</table>'.
APPEND WA_OBJTXT TO IT_OBJTXT.
WA_OBJTXT-LINE = '</div>'.
APPEND WA_OBJTXT TO IT_OBJTXT.
WA_OBJTXT-LINE = '<div style="margin:30px 20px;font-size:12px;text-align:left;color:red;"> <b>Note:</b>- This is a SAP Training Test Mail, Please do not reply on this mail.</div></div>'.
APPEND WA_OBJTXT TO IT_OBJTXT.
WA_OBJTXT-LINE = '</body> </html>'.
APPEND WA_OBJTXT TO IT_OBJTXT.
DESCRIBE TABLE IT_OBJTXT LINES W_TAB_LINES.
READ TABLE IT_OBJTXT INTO WA_OBJTXT INDEX W_TAB_LINES.
WA_DOCDATA-DOC_SIZE =
( W_TAB_LINES - 1 ) * 255 + STRLEN( WA_OBJTXT ).
REFRESH IT_OBJPACK.
CLEAR WA_OBJPACK-TRANSF_BIN.
WA_OBJPACK-HEAD_START = 1.
WA_OBJPACK-HEAD_NUM = 0.
WA_OBJPACK-BODY_START = 1.
WA_OBJPACK-BODY_NUM = W_TAB_LINES.
WA_OBJPACK-DOC_TYPE = 'HTML'.
APPEND WA_OBJPACK TO IT_OBJPACK.
CLEAR :IT_RECLIST,IT_SMTP,IT_BAPIRET. " For BY passing
REFRESH :IT_RECLIST,IT_SMTP,IT_BAPIRET. " For BY passing UNAME
CALL FUNCTION 'BAPI_USER_GET_DETAIL'
EXPORTING
USERNAME = SY-UNAME
TABLES
RETURN = IT_BAPIRET
ADDSMTP = IT_SMTP.
LOOP AT IT_SMTP .
WA_RECLIST-RECEIVER = IT_SMTP-E_MAIL.
WA_RECLIST-REC_TYPE = 'U'.
APPEND WA_RECLIST TO IT_RECLIST.
CLEAR WA_RECLIST.
ENDLOOP.
* WA_RECLIST-RECEIVER = '[email protected]'.
WA_RECLIST-RECEIVER = '[email protected]'.
WA_RECLIST-REC_TYPE = 'U'.
* WA_RECLIST-BLIND_COPY = 'X'.
APPEND WA_RECLIST TO IT_RECLIST.
CLEAR WA_RECLIST.
IF SY-UNAME EQ 'NAHAR'.
CLEAR IT_RECLIST. " For BY passing
REFRESH IT_RECLIST. " For BY passing
CLEAR WA_RECLIST.
WA_RECLIST-RECEIVER = '[email protected]'.
WA_RECLIST-REC_TYPE = 'U'.
APPEND WA_RECLIST TO IT_RECLIST.
CLEAR WA_RECLIST.
ENDIF.
ENDFORM.
FORM SEND_MESSAGE.
CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'
EXPORTING
DOCUMENT_DATA = WA_DOCDATA
PUT_IN_OUTBOX = ' '
COMMIT_WORK = 'X' "used from rel.6.10
TABLES
PACKING_LIST = IT_OBJPACK
OBJECT_HEADER = IT_OBJHEAD
CONTENTS_TXT = IT_OBJTXT
CONTENTS_BIN = IT_OBJBIN
RECEIVERS = IT_RECLIST
EXCEPTIONS
TOO_MANY_RECEIVERS = 1
DOCUMENT_NOT_SENT = 2
DOCUMENT_TYPE_NOT_EXIST = 3
OPERATION_NO_AUTHORIZATION = 4
PARAMETER_ERROR = 5
X_ERROR = 6
ENQUEUE_ERROR = 7
OTHERS = 8.
IF SY-SUBRC NE 0.
MESSAGE 'Mail sending failed' TYPE 'I'.
ELSE.
SUBMIT RSCONN01 WITH MODE = 'INT' WITH OUTPUT = ' ' AND RETURN.
MESSAGE 'Mail sent successfully' TYPE 'I'.
ENDIF.
WAIT UP TO 2 SECONDS.
SUBMIT RSCONN01 WITH MODE = 'INT'
WITH OUTPUT = ' '
AND RETURN.
ENDFORM.
Output:
