Friday, March 6, 2009

Open SQL Inner Join, Internal Tables and Write Output

*&---------------------------------------------------------------------*
*& Report ZITABGA7
*& Excercise for 5th Mar,09
*&---------------------------------------------------------------------*

*& _______ ________
*& / / /
*& / ___ /_______/
*& / / /
*& /______/ A U R A V / A T W A R I
*& gaurav_patwari@yahoo.co.in

*& ---------------------------------------------------------------------
*& Declare two internal tables with following structure: (Report: ZITAB??7)
*& First:
*& EKKO-EBELN
*& EKKO-AEDAT
*& EKKO-BSTYP
*& EKKO-BUKRS
*& EKKO-EKORG
*& EKKO-LIFNR
*&
*& Second:
*& EKPO-EBELN
*& EKPO-EBELP
*& EKPO-WERKS
*& EKPO-MATNR
*& MAKT-MAKTX
*& EKPO-MENGE
*& EKPO-MEINS
*&
*& Selection screen Parameters:
*& Purchasing organization.
*& Fill the First internal table based on user input using array fetch.
*& Fill the second internal table with PO Items for all the purchase orders
*& existing in the first internal table. (Use FOR ALL ENTRIES and INNER JOIN)
*& Display both the internal tables.
*&---------------------------------------------------------------------*

REPORT ZITABGA7.

DATA: BEGIN OF IT_EKKO OCCURS 0,
EBELN TYPE EKKO-EBELN,
AEDAT TYPE EKKO-AEDAT,
BSTYP TYPE EKKO-BSTYP,
BUKRS TYPE EKKO-BUKRS,
EKORG TYPE EKKO-EKORG,
LIFNR TYPE EKKO-LIFNR,
END OF IT_EKKO.

DATA: BEGIN OF IT_EKPO OCCURS 0,
EBELN TYPE EKPO-EBELN,
EBELP TYPE EKPO-EBELP,
WERKS TYPE EKPO-WERKS,
MATNR TYPE EKPO-MATNR,
MAKTX TYPE MAKT-MAKTX,
MENGE TYPE EKPO-MENGE,
MEINS TYPE EKPO-MEINS,
END OF IT_EKPO.

PARAMETERS: P_EKORG TYPE EKKO-EKORG OBLIGATORY.

START-OF-SELECTION.
SELECT EBELN
AEDAT
BSTYP
BUKRS
EKORG
LIFNR
FROM EKKO
INTO CORRESPONDING FIELDS OF TABLE IT_EKKO
WHERE EKORG = P_EKORG.

SELECT EKPO~EBELN
EKPO~EBELP
EKPO~WERKS
EKPO~MATNR
MAKT~MAKTX
EKPO~MENGE
EKPO~MEINS
INTO CORRESPONDING FIELDS OF TABLE IT_EKPO
FROM EKPO INNER JOIN MAKT ON MAKT~MATNR = EKPO~MATNR
FOR ALL ENTRIES IN IT_EKKO WHERE EBELN = IT_EKKO-EBELN.

END-OF-SELECTION.
* DISPLAY THE INTERNAL TABLE 1 -------------------------------------
WRITE: 'INTERNAL TABLE 1 - FOR PURCHASE ORGANIZATION ',P_EKORG.
PERFORM DRAWLINE.
* PRINTING HEADER OF THE DATA ---------------------------------------
WRITE: / SY-VLINE,
2 'PUR DOC NO.',
13 SY-VLINE,
14 'DATE CREATED',
24 SY-VLINE,
25 'CAT',
28 SY-VLINE,
29 'COCd',
33 SY-VLINE,
34 'VENDOR NO.',
53 SY-VLINE.
PERFORM DRAWLINE.
LOOP AT IT_EKKO.
WRITE: / SY-VLINE,
2 IT_EKKO-EBELN,
13 SY-VLINE,
14 IT_EKKO-AEDAT,
24 SY-VLINE,
25 IT_EKKO-BSTYP,
28 SY-VLINE,
29 IT_EKKO-BUKRS,
33 SY-VLINE,
34 IT_EKKO-LIFNR,
53 SY-VLINE.
PERFORM DRAWLINE.
ENDLOOP.


* DISPLAY THE INTERNAL TABLE 2 -------------------------------------
NEW-LINE.
WRITE: 'INTERNAL TABLE 2 '.
PERFORM DRAWLINE2.
* PRINTING HEADER OF THE DATA ----------------------------------------
WRITE: /
SY-VLINE,
2 'PUR DOC',
13 SY-VLINE,
14 'ITMNO',
19 SY-VLINE,
29 'PLANT',
33 SY-VLINE,
34 'MAT NO.',
52 SY-VLINE,
53 'MAT. NAME',
93 SY-VLINE,
94 'QUAN.',
111 SY-VLINE,
112 'UoM',
115 SY-VLINE.
PERFORM DRAWLINE2.
LOOP AT IT_EKPO.
WRITE: /
SY-VLINE,
2 IT_EKPO-EBELN,
13 SY-VLINE,
14 IT_EKPO-EBELP,
19 SY-VLINE,
29 IT_EKPO-WERKS,
33 SY-VLINE,
34 IT_EKPO-MATNR,
52 SY-VLINE,
53 IT_EKPO-MAKTX,
93 SY-VLINE,
94 IT_EKPO-MENGE,
111 SY-VLINE,
112 IT_EKPO-MEINS,
115 SY-VLINE.
PERFORM DRAWLINE2.
ENDLOOP.

TOP-OF-PAGE.
PERFORM GAURAVHEADER.


*&---------------------------------------------------------------------*
*& Form DRAWLINE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM DRAWLINE.
NEW-LINE.
DO 53 TIMES.
WRITE : '-' NO-GAP.
ENDDO.
ENDFORM. "DRAWLINE

*&---------------------------------------------------------------------*
*& Form DRAWLINE2
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM DRAWLINE2.
NEW-LINE.
DO 115 TIMES.
WRITE : '-' NO-GAP.
ENDDO.
ENDFORM. "DRAWLINE2

*&---------------------------------------------------------------------*
*& Form GAURAVHEADER
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM GAURAVHEADER .
WRITE:/' _______ ________'.
WRITE:/' / / /'.
WRITE:/' / ___ /_______/'.
WRITE:/' / / /'.
WRITE:/' /______/ A U R A V / A T W A R I'.
WRITE:/' gaurav_patwari@yahoo.co.in '.
WRITE:/ SY-ULINE.
ENDFORM. " GAURAVHEADER

1 comment:

  1. thanks for sharing
    it helped a lot.....

    ReplyDelete