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

 

VBAK

     VBELN

      sales order no(primary key)

      ERDAT

       date on which sales order created

     KUNNR

      customer account no(foreign key)

VBAP

      POSNR

         item no(primary key)

        MATNR

         material no

      KWMENG

       Quantity(qty

       VBELN

       sales order(foreign key)

Customer Accno

Master data

KNA1

VBAK

KUNNR	NAME1
1	X
2	Y
3	Z

KUNNR

NAME1

1

X

2

Y

3

Z

+

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

VBELN

ERDAT

KUNNR

100

--/--/--

1

200

--/--/--

2

300

--/--/--

3

                                  INNER JOIN

Customer Accno

01

Customer Accno

01

Customer Accno

Sales order

Date

CUSTOMER NAME

1

100

-----

X

1

200

----

Y