ABAP QUERY

        Introduce you about ABAP Query and explain how to create an ABAP Query based on a case scenario.

What Is ABAP Query

• A tool to generate report without ABAP coding.
  All you need is to join the table and drag and drop field that you wish to occur in the report.
• Type of reports provided by ABAP Query :
  1. Basic List
      Simple Report.
  2. Statistics
      Report containing statistical functions like Average & Percentage.
  3. Ranked List
      Report written for analytical purpose.

A.  Case Scenario 

There is a request to generate a report display who should release a
PO and message status of the PO.
The selections are based on :

• Date on which the record was created 
• Purchasing Organization 
• Purchasing Group 
• Release Code 
• Vendor’s Account Number 
• Purchasing Document Type

For example : 

We would like to display all PO which was created on a certain date.
With information about who should release the PO or who is the latest
releaser (for PO that has been full release) and the message type of
the PO (has been print, mail, fax). 

Creating ABAP Query

Now, we come to step in creating ABAP Query. There are 3 steps :

1. Create User Group (SQ03)                                                                                                        Specify the users who should be authorized to run the query.
2. Create Infoset (SQ02)                                                                                                          Indicates from which part of the SAP database the data is going to be retrieved and how the data is to be retrieved by the query.
3. Create Queries (SQ01)                                                                                                            Create the query and arrange the layout.

Here are the detail explanation of each step :

• Create User Group (SQ03)

• Go to SQ03 to create User Group 

• Clik Assign users and Infosets Assign user to this user group.

• Create Infoset                                                                                                         This report need information about PO, PO releaser and PO Message Status. So that the table relation could be describe as below :

• Go To SQ02 to create Infoset                                                                                Give the title of the Infoset.                                                                                     On Table join using basis table enter the basic table that u want to join (for this case example we want to join several table with basic table is T16FV, because the selection based on release code and T16FV is a Release Prerequisites table).

• Join The Table                                                                                                      Give a relation between the tables that you are going to join.

• Choose The Field For Display                                                                              Choose field (from tables you have joined before) that you would like to display.

• Create Additional Fields                                                                                          Create additional fields if you need them.

• Create Coding                                                                                                                               You can add some coding in some section. Click the button inside the red circle.

On this case I add coding at code section:

•

DATA

TABLES : T16FS, NAST.

DATA : condition(5) TYPE c,

ld_frgc1 LIKE t16fs-frgc1,

ld_kschl LIKE nast-kschl.

* ctrneu TYPE i,

* ctrneu2 TYPE i,

* ctrneu3 TYPE i.

•

Record Processing

CLEAR : NEU, NEU1, NEU2.

SELECT kschl INTO ld_kschl FROM nast

WHERE kappl = 'EF' AND objky = ekko-ebeln

AND ( kschl = 'NEU' OR kschl = 'NEU1' OR kschl = 'NEU2' )

AND spras = 'E' AND vstat = '1'.

IF sy-subrc = 0.

IF ld_kschl = 'NEU'.

NEU = 'X'.

ELSEIF ld_kschl = 'NEU1'.

NEU1 = 'X'.

ELSEIF ld_kschl = 'NEU2'.

NEU2 = 'X'.

ENDIF.

ENDIF.

ENDSELECT.

* ctrneu = ctrneu2 = ctrneu3 = 0.

* CLEAR : NEU, NEU2, NEU3.

IF T16FV-frggr IS NOT INITIAL AND T16FV-frgsx IS NOT INITIAL.

CASE EKKO-frgzu.

WHEN ' '.

condition = 'frgc1'.

WHEN 'X'.

condition = 'frgc2'.

WHEN 'XX'.

condition = 'frgc3'.

WHEN 'XXX'.

condition = 'frgc4'.

WHEN 'XXXX'.

condition = 'frgc5'.

WHEN 'XXXXX'.

condition = 'frgc6'.

WHEN 'XXXXXX'.

condition = 'frgc7'.

WHEN 'XXXXXXX'.

condition = 'frgc8'.

WHEN 'XXXXXXXX'.

condition = 'frgc8'.

ENDCASE.

SELECT SINGLE (condition) INTO ld_frgc1

FROM t16fs

WHERE frggr = T16FV-frggr AND frgsx = T16FV-frgsx.

IF ld_frgc1 IS INITIAL.

CASE EKKO-frgzu.

WHEN 'X'.

condition = 'frgc1'.

WHEN 'XX'.

condition = 'frgc2'.

WHEN 'XXX'.

condition = 'frgc3'.

WHEN 'XXXX'.

condition = 'frgc4'.

WHEN 'XXXXX'.

condition = 'frgc5'.

WHEN 'XXXXXX'.

condition = 'frgc6'.

WHEN 'XXXXXXX'.

condition = 'frgc7'.

WHEN 'XXXXXXXX'.

condition = 'frgc8'.

ENDCASE.

SELECT SINGLE (condition) INTO ld_frgc1

FROM t16fs

WHERE frggr = T16FV-frggr AND frgsx = T16FV-frgsx.

ENDIF.

IF T16FV-frgco <> ld_frgc1.

* CLEAR : EKKO, T16FV.

DELETE : EKKO, T16FV.

ELSE.

MODIFY EKKO.

ENDIF.

ENDIF.

• Create Queries

• Go to SQ01 to create Queries Firstly,                                                                      choose the Infoset that you are going to use for this query.

Then give a title to this Query.

• Select Field that would be display by this Query.                                                        Started by choosing the Field Group.

Thus select The field.

• Field For Selection                                                                                                                           Select field that will be used for the selection screen. Furthermore, you can define the sequence of these fields.

• Basic List Display                                                                                                  Arrange how you wish you report to be displayed.

Result  of ABAP QUERY 

Report Layout 

 

Simple ALV Display

Create Simple ALV Display 

      1.  Goto Tcode SE38

           

       2.     Input Program Name ZDEMO and Click Create  Button

 

      3. 1.       Fill like the Example and Save - Local

4.   In Abap Editor, Type like Below :

*&---------------------------------------------------------------------*
*& Report ZDEMO
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT ZDEMO.

TABLES ZDEMO.
TYPE-POOLS: slis.

TYPES : BEGIN OF TY_DEMO,
ZPERNR TYPE ZDEMO-ZPERNR,
BEGDA TYPE ZDEMO-BEGDA,
NACHN TYPE ZDEMO-NACHN,
STRAS TYPE ZDEMO-STRAS,
ORT01 TYPE ZDEMO-ORT01,
PSTLZ TYPE ZDEMO-PSTLZ,
TELNR TYPE ZDEMO-TELNR,
END OF TY_DEMO.

DATA : IT_DEMO TYPE TABLE OF TY_DEMO,
WA_DEMO TYPE TY_DEMO.

DATA: fieldcatalog type slis_t_fieldcat_alv with header line,
gd_repid like sy-repid,
gd_layout type slis_layout_alv.

START-OF-SELECTION.

SELECT * FROM ZDEMO INTO CORRESPONDING FIELDS OF TABLE IT_DEMO.

END-OF-SELECTION.


fieldcatalog-fieldname = 'ZPERNR'.
fieldcatalog-seltext_m = 'Personal No'.
fieldcatalog-col_pos = 0.
fieldcatalog-outputlen = 10.
append fieldcatalog to fieldcatalog.
clear fieldcatalog.

fieldcatalog-fieldname = 'BEGDA'.
fieldcatalog-seltext_m = 'Start Date'.
fieldcatalog-col_pos = 1.
fieldcatalog-outputlen = 10.
append fieldcatalog to fieldcatalog.
clear fieldcatalog.

fieldcatalog-fieldname = 'NACHN'.
fieldcatalog-seltext_m = 'Last Name'.
fieldcatalog-col_pos = 2.
fieldcatalog-outputlen = 10.
append fieldcatalog to fieldcatalog.
clear fieldcatalog.

fieldcatalog-fieldname = 'STRAS'.
fieldcatalog-seltext_m = 'House number and street'.
fieldcatalog-col_pos = 3.
fieldcatalog-outputlen = 10.
append fieldcatalog to fieldcatalog.
clear fieldcatalog.

fieldcatalog-fieldname = 'ORT01'.
fieldcatalog-seltext_m = 'City'.
fieldcatalog-col_pos = 4.
fieldcatalog-outputlen = 10.
append fieldcatalog to fieldcatalog.
clear fieldcatalog.


fieldcatalog-fieldname = 'PSTLZ'.
fieldcatalog-seltext_m = 'Postal Code'.
fieldcatalog-col_pos = 5.
fieldcatalog-outputlen = 10.
append fieldcatalog to fieldcatalog.
clear fieldcatalog.

fieldcatalog-fieldname = 'TELNR'.
fieldcatalog-seltext_m = 'Telephone Number'.
fieldcatalog-col_pos = 6.
fieldcatalog-outputlen = 10.
append fieldcatalog to fieldcatalog.
clear fieldcatalog.


gd_repid = sy-repid.
call function 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
i_callback_program = gd_repid
* i_callback_top_of_page = 'TOP-OF-PAGE' "see FORM
is_layout = gd_layout
it_fieldcat = fieldcatalog[]
* it_sort = it_sort[]
i_save = 'X'
TABLES
t_outtab = it_DEMO
EXCEPTIONS
program_error = 1
others = 2.

if sy-subrc <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
endif.



5. Save and Activate.

6. Run

Welcome First Logon SAP

       Create Welcome in First Logon Using function exit EXIT_SAPLSUSF_001 :

 Enhancement   : SUSR0001 User Exit after logon to SAP system.

When the User logs in the system ,this exit is called each and every time for every user after logon to the R/3 system.

Function Exit : EXIT_SAPLSUSF_001 .

  Every dialog user passes thrugh this function module after logon,It can be used to execute individual customer checks and send mesages to the user.
TABLE USR02 CONTAINS LOGON DATA  and can be used in this exit to get the user logon data and take necessary actions as required by the customer requirement and even LOG_OFF (not recommended by SAP).

STEPS REQUIRED FOR IMPLEMETING THE EXIT

1.   Open CMOD(Project maintenance) Transaction.Enter a project name starting with Z.Press  enter.
2.  Goto Enhancement window by clicking the enhancement button in the application tool bar. 
3.  Write SUSR0001 in the enhancement column and press enter.
4.  Goto Components window(It will show al the exits included in this enhancement), in our case  only one Function Exit will be shown.
5.   Double click on the Function exit EXIT_SAPLSUSF_001.  

     The following function source code will be displayed in the function editor

       FUNCTION EXIT_SAPLSUSF_001.
     *"----------------------------------------------------------------------
     *"*"Lokale Schnittstelle:
     *"----------------------------------------------------------------------

       INCLUDE ZXUSRU01.

       ENDFUNCTION     

     

     6.  Double click on the include ZXUSRU01 .If it  will ask to create the include say yes . 

     7.  Write the following code in the include.

          DATA W_TEXT(30)  TYPE C.
          DATA W_DATE(10)  TYPE C.
          WRITE sy-datum TO W_DATE DD/MM/YYYY.
          CONCATENATE  ' Date is '  W_DATE  INTO W_TEXT.

          CALL FUNCTION 'POPUP_TO_INFORM'
          EXPORTING
              titel           = 'Welcome to Paradise !'
              txt1           = 'Have A Nice Day' 
              txt2           =  W_TEXT
      *   TXT3           = ' '
       *   TXT4          = ' '

     8.  Save the include and activate it .Activate the Project also by going to the menu in CMOD  

          transaction.Now The user exit SUSR0001 has been implemented and is ready to use.

     9.  Logon to the R/3 system . A dialog box will appear with the message

          Welcome to  Paradise ! with date .

NOTE: BAPIs like BAPI_USER_GET_DETAIL can also be used to get user info or directly read from tables USR02 etc.