Thursday 18 March 2010

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