Creating Ad Hoc query (HCM related infoset query)

The InfoSet Query is a useful tool for maintaining queries within the SAP Query and is suitable for developing queries and for ad-hoc reporting.

InfoSet Query is suitable for reporting in all areas of the SAP R/3 system. A special feature is the Human Resources (HR) component. When InfoSet Query is used in HR for ad-hoc reporting, the name Ad-Hoc Query is used instead of InfoSet Query.

Ad-Hoc query provides three different kinds of reports:

• Basic List - Simple reports

• Statistics - reports with statistical functions such as Averages, percentages etc

• Ranked List - for analytical reports

The first step in the process of creating an Ad-Hoc query is to create a Functional Area. Function area provides special views of logical databases. They determine which fields of a logical database can be evaluated in queries.

Go to tcode SQ03.

Select Query areas

Menu->Environment->Query areas

Select the radio button ‘Global area’.

Click on ‘Choose’

Create user group

Here you specify the users who should be authorized to run the query. A user group is always associated with a Functional Area.
- Go to the menu path Environment => User Group or SQ03 à Give a user group name in the screen. Specify the sap logins of the users whom you want to authorize for using the functional area and click on the Create button.

Select User group as ‘zdemo_user’ (Create Z user group)

Click on ‘CREATE’ button.

Pass short description click SAVE

Go to Environment menu select INFOSET or go for tcode (SQ02)

Create Infoset with Z like ‘zdemo_infoset’

Click on create button

Name as ‘Shortdesc’.. (demo)

Select radio button Logical database ‘PNP or PNPC’

Click on Continue button

Open ‘Basic personal data’

Select infotype which u need (like above screen short)

Click on continue button

Click on save and Click on generate button (red button)

Go back to main screen

Click on ‘Role/User group assignment’ tab

Select assigned table check box with User group which u already created.(zdem_user)

Save it and back...

Go to tcode for SQ01..

Query ‘zdemo_01’ (Create zname)

Click on ‘create’ button

Select ‘infoset’ click on continue....

Pass title as Shortdesc..(demo for pa infotype)

Save......

Back....

Click on ‘Infoset Query’ button

Select selection screen field and Output fields...(the fields you required as Selection screen fields and the fields you need as Output fields)

Select output check box with right click –select output-only value..

Select fields from which u already given info types

Finally click on save button

Click on continue....

Back...

Click on Execute button (F8)..

Click on continue ready for you REPORT

Developing SAP Query for Task List Data Extraction

Purpose: To create an SAP query to extract task list data from system.

Scope: All task lists pertaining to Production and Planning; Routing (N), Master Recipe (2), Rate Routing (R)

Procedure:

Follow the procedure step by step:

Transaction Code	SQVI
Menu path	Tools>Utilities>SQVI - QuickViewer

Create new Quick view

Write the appropriate name of query

After you click on create button, system will populate the following window, complete the details with right information

Once you have done with above steps you will get “Choose Data Source” screen. Here in the following step you need to connect different tables properly in order to get task list data.

We are starting with MAPL (Assignment of Task Lists to Materials) table. Similarly, we need to take following tables in order to get the appropriate information.

PLKO (Task list – header)

PLPO (Task list - operation/activity)

PLAS (Task list - selection of operations/activities)

CRHD (Work Center Header)

CRTX (Text for the Work Center or Production Resource/Tool)

MARA (General Material Data)

MAKT (Material Descriptions)

Between PLPO and CRHD, the default link is with field OBJTY. You need to delete this link and create with PLPO-ARBID and CRHD-OBJID. For this click on ARBID in PLPO table and drag the cursor towards OBJID in CRHD table. Afterwards it should like

After you finish with above activities the screen will look like

Following tables explain the link details between different tables used above:

Table 1 MAPL	Table2 PLKO	Table 3 MARA
PLNTY	PLNTY
PLNNR	PLNNR
PLNAL	PLNAL
MATNR		MATNR
PLKO	PLAS
PLNTY	PLNTY
PLNNR	PLNNR
PLNAL	PLNAL
PLAS	PLPO
PLNTY	PLNTY
PLNNR	PLNNR
PLNKN	PLNKN
PLPO	CRHD
ARBID	OBJID
CRHD	CRTX
OBJID	OBJID
MARA	MAKT
MATNR	MATNR

In the following steps, mainly you need to select which fields you want to see in selection screen and which one in output.

Similarly select all those fields from table enlisted which you want to see in Selection and Display screen.

For example, you want to see following fields in Selection Screen

• Material
• Plant
• Task List Type

And following fields in output

• Material
• Material Description
• Plant
• Group
• Group Counter
• Task List Description
• Usage
• Status
• From Lot Size
• UoM
• To Lot Size
• UoM
• Operation/Activity
• Work center
• Work center description
• Operation short Text
• Operation Base Quantity
• UoM
• Standard value1
• UoM for Std value1
• Standard value2
• UoM for Std value2
• Standard value3
• UoM for Std value3
• Setup Group Key
• Setup Group Category

Note: As per your requirement, you can select/deselect additional fields in Quick viewer: Initial screen

It’s better to arrange Sort Sequence in order to get proper operation number in output for a combination of Group, Group Counter, Material, and Plant.

Check and Save the Query.

Now it’s the time to see the result of efforts that you have put in to develop this query.

So execute the transaction.

and the Result of your efforts is

Transport of SAP Query objects

ABAP Queries can be transported to different clients by the following two ways:

1. By using the standard program ‘RSAQR3TR’

2. By using the transport option available on the application bar in Transactions SQ02 or SQ03.

From Transaction SQ02/SQ03->Environment->Transports:

On the next screen, all the options come up for transporting the queries. The transport action is performed based on the radio button selected on the screen.

The transports are to be made in the following order:

1. User Group transport
2. Infoset transport
3. Infoset and Query transport
4. Queries transport

The transports are to be made in the specified order. The User Group is to be transported first, followed by the Infoset and then the Queries.

By using the standard program ‘RSAQR3TR’:

Execute the standard program RSAQR3TR On the next screen, all the options come up for transporting the queries. The transport action is performed based on the radio button selected on the screen.

Once the query is transported or uploaded in the destination system in the method specified above, the same can be used there for displaying the data fetched from the database.

Setting the Expiry date in SAP Query

Business Requirement: The client wants to maintain expiry dates to each and every SAP queries that are running in production. The expired query will not be allowed to run in production.

Design Logic:

1. Maintain a custom table with two fields – query name and query expiry date.

2. Design a custom function module which will collect the current SAP query name at run time (i.e. during query execution); check the expiry date of that query from the custom table and generate an error message if the query is expired or work as per the code in the infoset if it’s not expired.

3. Put that custom function module in the infosets of all the queries. Keep all the executable codes of the infosets in the ‘ELSE’ part of the sy-subrc check.

Explanations:

Step 1: Create a Z-table ZQUERY_EXPIRE along with a table maintenance generator.

For more details on user group, infoset and query name, refer to table AQLQCAT.

Step 2: Maintain query names along with their expiry dates in the Z-table ZQUERY_EXPIRE via SM30.

Step 3: Design a custom function module ZQUERY_EXP_CHECK with an exception QUERY_EXPIRED (Query has expired).

FUNCTION zquery_exp_check.
*"---------------------------------------------------------------
*"*"Local Interface:
*"  EXCEPTIONS
*"      QUERY_EXPIRED
*"---------------------------------------------------------------

  TABLES: zquery_expire.

  CONSTANTS: c_fill TYPE c VALUE '='.
 DATA: v_sycprog LIKE sy-cprog,
       v_user_grp1(30) TYPE c,
       v_user_grp2(14) TYPE c,
       v_query_name1(14) TYPE c,
       v_rest(14) TYPE c,
       v_query_name TYPE aqs_quname.

  TYPES: BEGIN OF ty_query,
          qnum     TYPE aqs_quname,
          exp_date TYPE datum,
        END OF ty_query.

  DATA: wa_query TYPE ty_query,
       i_query TYPE STANDARD TABLE OF ty_query INITIAL SIZE 0.

  v_sycprog = sy-cprog.
 v_user_grp1 = v_sycprog+4(26).
 v_user_grp2 = v_user_grp1+12(14).
 SPLIT v_user_grp2 AT c_fill INTO v_query_name1 v_rest.
 v_query_name = v_query_name1.

  SELECT qnum
        exp_date
 FROM zquery_expire
 INTO TABLE i_query
 WHERE qnum EQ v_query_name.

  LOOP AT i_query INTO wa_query.

* If the query expires, generate the error message
   IF wa_query-exp_date LT sy-datum.
     MESSAGE e000(zbasis_msg) RAISING query_expired.

* If the expiary date is today or yet to come, don't do anything
   ELSE.
     MESSAGE i002(zbasis_msg).
   ENDIF.
 ENDLOOP.
ENDFUNCTION.

Step 4: Call this custom function module from the START-OF-SELECTION code sections of all the infosets of the queries that are maintained in the custom table ZQUERY_EXPIRE.  

Here we’ve added the following code in all the infosets of the 3 queries maintained in the custom table.

CALL FUNCTION 'ZQUERY_EXP_CHECK'
      EXCEPTIONS
        query_expired = 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.
    ELSE.
      WRITE: /5 'Hello1'.
    ENDIF.

Test: Suppose today’s date is 20/03/2009. The expiry date of the query BEJ_QRY_TEST1 was 18.03.2009 (as maintained in the Z-table).         

The expiry date of the query QRY_TEST3 is today (20/03/2009).

Scenario 1: Execute the query BEJ_QRY_TEST1 (expiry date is less than the current date) from SQ01.

Execute; the following error message will be generated.

Scenario 2: Execute the query QRY_TEST3 (expiry date is equal to the current date) from SQ01.

Execute; the actual code of the infoset will be executed.