*&---------------------------------------------------------------------*
*& Report ZITABGA8
*& Excercise for 5th Mar,09, Sub Query
*&---------------------------------------------------------------------*
*& _______ ________
*& / / /
*& / ___ /_______/
*& / / /
*& /______/ A U R A V / A T W A R I
*& gaurav_patwari@yahoo.co.in
*& ---------------------------------------------------------------------
*& Create a report that accepts the Purchasing organization (Default ‘0001’) as input
*& and generates the list of purchase requisitions.
*& Declare an internal table with following fields from table EBAN. (Report: ZITAB??8)
*& BANFN - P.R. No.
*& ERDAT - Date last changed
*& WERKS - Plant
*& EKGRP - Purchasing group
*& BADAT - Requisition date
*& MATNR - Material no
*& MENGE - P.R. quantity
*& MEINS - Unit of measure
*& Hints:
*& 1. Purchasing group always belongs to Purchase organization.
*& 2. Table T026Z stores the assignment of Purchasing group to Purchase organization.
*& 3. Use sub query to find the Purchasing group for the given Purchase organization.
*&---------------------------------------------------------------------*
REPORT ZITABGA8.
TABLES: EBAN, T026Z.
DATA: BEGIN OF IT_EBAN OCCURS 0,
BANFN TYPE EBAN-BANFN, " P.R. No.
ERDAT TYPE EBAN-ERDAT, " Date last changed
WERKS TYPE EBAN-WERKS, " Plant
EKGRP TYPE EBAN-EKGRP, " Purchasing group
BADAT TYPE EBAN-BADAT, " Requisition date
MATNR TYPE EBAN-MATNR, " Material no
MENGE TYPE EBAN-MENGE, " P.R. quantity
MEINS TYPE EBAN-MEINS, " Unit of measure
END OF IT_EBAN.
PARAMETERS P_EKORG TYPE EBAN-EKORG DEFAULT '0001'.
START-OF-SELECTION.
SELECT BANFN
ERDAT
WERKS
EKGRP
BADAT
MATNR
MENGE
MEINS
FROM EBAN
INTO CORRESPONDING FIELDS OF TABLE IT_EBAN
WHERE EKGRP IN ( SELECT EKGRP FROM T026Z WHERE EKORG = P_EKORG ).
END-OF-SELECTION.
LOOP AT IT_EBAN.
WRITE: /
SY-VLINE, 2 IT_EBAN-BANFN, " UNDER '|P.R. No.',
13 SY-VLINE,14 IT_EBAN-ERDAT , "UNDER '|Date changed',
25 SY-VLINE,26 IT_EBAN-WERKS , "UNDER '|Plant',
30 SY-VLINE,31 IT_EBAN-EKGRP , "UNDER '|Pur. Group',
34 SY-VLINE,35 IT_EBAN-BADAT , "UNDER '|Req. date',
45 SY-VLINE,46 IT_EBAN-MATNR , "UNDER '|Mat. no',
64 SY-VLINE,65 IT_EBAN-MENGE ,"UNDER '|P.R. Qty',
81 SY-VLINE,82 IT_EBAN-MEINS , "UNDER '|UoM'.
SY-VLINE.
PERFORM DRAWLINE.
ENDLOOP.
TOP-OF-PAGE.
PERFORM GAURAVHEADER.
PERFORM TABLEHEADER.
*&---------------------------------------------------------------------*
*& Form DRAWLINE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM DRAWLINE.
NEW-LINE.
DO 86 TIMES.
WRITE : '-' NO-GAP.
ENDDO.
ENDFORM. "DRAWLINE
*&---------------------------------------------------------------------*
*& 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
*&---------------------------------------------------------------------*
*& Form TABLEHEADER
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM TABLEHEADER .
WRITE : /.
PERFORM DRAWLINE.
NEW-LINE.
WRITE: 1 '|P.R. No.',
13 '|Date changed',
25 '|Plant',
30 '|Pur. Group',
34 '|Req. date',
45 '|Mat. no',
64 '|P.R. Qty',
81 '|UoM',
86 '|'.
PERFORM DRAWLINE.
ENDFORM. " TABLEHEADER
Saturday, March 7, 2009
ABAP Sub Query Report
Friday, March 6, 2009
Open SQL Inner Join, Internal Tables and Write Output
*&---------------------------------------------------------------------*
*& Report ZITABGA7
*& Excercise for 5th Mar,09
*&---------------------------------------------------------------------*
*& ---------------------------------------------------------------------
*& 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
*& 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
Subscribe to:
Posts (Atom)
