Monday 17 November 2014

SUBQUERIES EXAMPLES:


SUBQUERIES EXAMPLES:

·        SUB QUERIES are alternate for INNER JOIN. It allows to extract data from multiple tables.

System Fields:

             Can be called as system variables also
Ø SAP provided once 171 system variables.
                     
SY-SUBRC:
             system variable for Error handling if it is equals to zero. There is no error  , not equal to zero means error.

SP-ULINE:
             it draws underline
SY-VLINE:
             draw vertical line.
SAP GUI:
             heights is 80 lines
             width is 255 characters
            
DEMO ON SUBQUERIES

Ø      Select SE 38, Enter Program: zsubqua_demo
Ø      Define attributes
Ø      Maintain attributes of Subqueries
Ø      Save details

Requirements:

·        In selection-screens, entering sales order no
·        Entering sales order no i.e

Sales order

HEADER DATA




·        Output is sales order no entered ands displays item and materials having in each Sales Order
·        Entering header data
·        Displaying header data and item data
HEADER DATA                         ITEM DATA
 

SALES ORDER NO
ITEM
MATERIAL




NO STANDARD PAGE HEADING:
             Is a syntax, suppress title, defined in attributes.
Line size 80:
             Means from 255 characters width upto 80 characters width using for data display.

MESSAGE-ID:
             Means message class, this is for Error-Handling. Transaction code is se91.
Ex: message-id zemdemo
Prog:
Ø      Report zsubqua_demo NO STANDARD PAGE HEADING
      LINE-SIZE 80
       MESSAGE-ID ZEMDEMO.
Ø      Click on zemdemo to create.
·        It displays a dialogue box
Ø      Select ‘YES’ to create
Ø      Save
Ø      Select ‘MESSAGES
Ø       
·        It displays
·        Message class have message number
·        This range starts from 000 and ends with 999
·        Each number we can assign one error
·        Total 1000 messages we can assign.
Ø      Select message number 000 and assign error message:
Ex: 000                                        sales order num enter does not exists
Ø      Save and back for f3
Ø      Maintain tables work area. Which  table we are going to use VBAK ,VBAP because there is no master table
TABLES: VBAK,
                 VBAP.

Ø      Maintain selection screen logic. Am I entering one value or ranges values, we are entering one value so selection-screen logic is ‘parameters’.

PARAMETERS: SALES TYPE VBAK-VBELN.

Ø      Declare internal table as per output screen, means how many fields? three(3) fields:
·        Now I am going to introduce one more.. note down

TYPES: BEGIN OF FS,

·        FS is a field string and it is a datatype
·        How can u say it is a datatype, bcoz by seeing “TYPES’ keyword
·        We have two options two referring fields, those are: (i)Fields From Table, and (ii)Fields From Data Elements

·        If u refer field from tables means referring dependent object
Ex:
             Data: begin of I_ITAB OCCURS 0,
                      VBELN TYPE KNA1-KUNNR,
                      POSNR TYPE KNA1-KUNNR,
                      MATNR TYPE KNA1-MATNR,
           END OF I_ITAB.                                                                            

·        If u refer field from table means referring dependent object
·        If u refer data element from table means referring Independent object. Whenever independent object refer, performance can be improved.
·        Whenever Data Element referred in referring data type so type keyword or like keyword? type keyword right because referring data type. So keyword is ‘type’ keyword.
             VBELN TYPE VBELN_VA,  ”Data elements of VBELN.
             POSNR TYPE POSNR_VA,
             MATNR TYPE MATNR, “item
             END OF FS.

·        FS is Field String, FS is a Data type, and FS is to maintaining three fields.
·        whenever data element referred so keyword is TYPE
·        Declare Internal Table work area and Body with reference of Data type FS .
·        Will you declare work area and body as a data object or data type? We have to declare work area  as a Data Object because to internal table have to hold data
·        To hold data memory required
·        Data object keep memory multiple records

DATA: WA TYPE FS. “ fs is referring data object.

·        Here work area and field string are same bcoz both properties are same



Work area(WA)

=

Field string(FS)

·        Now body it holds multiple records  then it is a table format but not line format.




WORK AREA BODY

·        Body is a table format. Now data object is body.
DATA: BODY TYPE TABLE OF FS.
·        This internal table with reference of Field string.
·        Real time scenario this is only useful
Ø      SQL statement using Sub Query
Ø       
SELECT VBELN POSNR MATNR FROM VBAP
             INTO TABLE BODY[]
WHERE VBELN = SALES
AND EXISTS
( SELECT VBELN FROM VBAK ) àhere execution starts from here means “select 1000 sales order FROM Header Data table
·        Make it I entered input sales order no 1000
·        Always analyze  subquery from last two i.e. select 1000 sales order from header data table
·        If 1000 exists in header data table then extract its item data from  item data table and keep that extracted data in body where clause as per selection screen(parameter).
·        Initially system can check header data details
·        If header data exists then it can start to extract related item data
·        Now data is in Body.
Ø      If data is not in body
IF SY-SUBRC NE 0. “In case of error.

Ø      Means sales order no doesn’t exist
MESSAGE E000.
Ø       
·        MESSAGE is a syntax and E for Error. And message no 000
ELSE. “IF SALES ORDER EXISTS.

Ø      logic to maintain column heading
WRITE:/ SY-ULINE(80).

·        It keeps underline upto 80 characters.
WRITE: /1 SY-VLINE, 2 ‘SALES ORDER NO’,
               40 SY-VLINE, 41 ‘ITEM’,
               60 SY-VLINE, 61 ‘MATERIAL’,
               80 SY-VLINE.
WRITE:/ SY-ULINE(80).
·        From 255 character width we are using 80 character
·        1st character vertical line
·         
Ø      Processing logic to display output.
LOOP AT BODY[] INTO WA.
WRITE: /1 SY-VLINE, 2 WA-VBELN,
             40 SY-VLINE, 41 WA-POSNR,
             60 SY-VLINE, 61 WA-MATNR,
            80 SY-VLINE.
END LOOP.
ENDIF.
WRITE: / SY-ULINE(80).
Ø      After data printed release data from int. table
CLEAR WA.
REFRESH BODY[].
Ø      Save and activate.
·        Internal table have 3 syntax: 91)with header line (2) without headerline (3) with reference of field string(FS).



Demo: working with SD Module (inner joins)

Predefined Tables Related to SALES and Distribution Module(SD)







·        Now We are working on SD Function Module.
·        Business flow of S.D(Sales & Distribution)
Having sales dept, Ware Housing Management Department (WM Dept) & Finance Dept(FICO).



·        In ware housing dept, raw material and finished goods can e stored.


·        Ware Housing dept is to maintain Raw Materials and Finished goods.
·        In SAP, if I say material type is ROH(Raw Material), if I say FERP(finished goods), HALB(semi finished goods



Starting Sales Dept(SD)

Here Business Partner is Customer, business Partner to Sales Dept is customer














·        Sales Dept wants to do business with Customer
·        Sales dept has to maintain customer data in SAP Database.
·        We know that it have 17000 predefined tables
·        From those 17000 tables one tables is to maintain customer tables that is KNA1.
KNA1: Customer Master Data.
o       It is a predefined Table to maintain customer data that is KNA1.
o       In this KNA1 Table, fields are:









      1. KUNNR( primary key)
     Customer Account no
      2. name1                                                              
      Name of customer
      3. ORT01
      City of customer


·        Business partner can place an order to sales department for fineshed goods
·        With reference of order sales dept can create sales order
·        With ref of sales order, sales dept can do ATP(Availability To do Promise) check on Finished goods. here ATP is SAP terminology.





























·        Now ATP check OK means Finished Goods(FERT) available in Ware Housing Department.
If ATP CHECK ok then Goods can be issued to the customer.





·        In goods issue, picking, packing, delivery can be maintained.
·        After goods issued, sales dept can give bill and Invoice to the customer for payment. here bill & Invoice are almost are same.






































With reference of invoice, In finance Department Account can be created.













·        Based on Account Document, Finance Dept can collect payment from Customer. This is nothing but AR Process. (account rec).

































SALES ORDER APPLICATION FORMAT





















·        In which table data is stored in KNA1 table
·        In selection screen item entered customer account no
·        Output is from so and so date to so..and so date list of sales order created.
·        Sales order data is available is in VBAK, it is a predefined Table for Sales Order Header details
·        Fields of VBAK ARE
                     





VBAK
     VBELN
      sales order no(primary key)
      ERDAT
       date on which sales order created
     KUNNR
      customer account no(foreign key)

·        In KNA1 table KUNNR is primary key where as in VBAK table it is a foreign key.

Requirements:

·        In selection screen, sales order number, if enter any sales order no, output should be item is having in items, materials having, qty, price also
·        Here VBAP is a predefined table for sales order Item details
·        Fields of VBAP are






VBAP
      POSNR
         item no(primary key)
        MATNR
         material no
      KWMENG
       Quantity(qty
       VBELN
       sales order(foreign key)

Relation between Master Data(VBAK) and Header Data(KNA1):
·        Based on Master Data user can maintain Header data
Example:
             Sales Order can be created based on customer information
·        Based on Header Data(VBAK) user can maintain Item Data(VBAP)
Example: Items information can be maintained under sales order No.
·        Master Data(KNA1)+ Header Data(VBAK)+ Item Data (VBAP)equals to Transactional Data.
i.e. KNA1 + VBAK + VBAP = Transactional Data
·        Sales Order Application is Transaction Data application, this can be used against Order placed by customer.
Requirement:
·        In Selection Screen I can enter customer account no










Customer Accno




Master data
          
sales




·        Output is the customer account no entered, here to this customer sales order, created by sales department on which date created.


















·        That is in briefly:






























·        Predefined tables using are: (i) KNA1 (2) VBAK.
·        Here we are using two tables so use Inner Join concept


INNER JOINS


·        Using INNER JOIN concepts data can be extracted from more than one table
·        Internal Table Keywords using are
·        Internal table Body have all records, where as Internal table work area has only last record only.










CLEAR:
             Is a keyword which can refreshed memory of Internal Table work area.
REFESH:
             Is a keyword which can clears Internal Table body memory
SORT:
             SORT is akeyword which can sort output data as per required column.
INITIAL:
             Means data is not there in body, it is keyword


NOT INITIAL:


             Means data there in body, it is a keyword.














INNER JOIN FUNCTIONALITY










KNA1


VBAK









KUNNR
NAME1
1
X
2
Y
3
Z










+









VBELN
ERDAT
KUNNR
100
--/--/--
1
200
--/--/--
2
300
--/--/--
3
                                  INNER JOIN


·        Kna1 table firelds kunnr , name1 fields., vbak tables firelds vbeln, erdat, kunnr
·        First system can check master data table KNA1, system can check primary key field kunnr, now system came to know 3 customer are there in table
·        System checks how many item have customer sales order, whichever customer field have sales order that data only it will be extracted
·        Inner joins can extract common data only
Example:





INPUT IS




Customer Accno
01


OUTPUT IS:






Customer Accno


Sales order
Date
CUSTOMER NAME
1
100
-----
X
1
200
----
Y


Demo on INNER JOINS