Table joining between ekko ekpo essr

[Former Member]

Hi Experts,

As i have developed a report but in table joining between ekko ekpo and essr i am facing problen as in my selection screen

ENTRY SHEET NUMBER and also in the ouput also i need ENTRY SHEET NUMBER with other field which i have mention i the IT_FINAL table.Below is my code.How to display all the field which is present in my final table it_final.

types: begin of ty_gtab1,

lblni type lblni,

lblne type lblne,

erdat type erdat,

ebeln type ebeln,

netwr type netwr,

banfn type banfn,

warpl type warpl,

fknum type fknum,

spec_no type spec_no,

end of ty_gtab1.

data: it_gtab1 type STANDARD TABLE OF ty_gtab1.

data: wa_gtab1 type ty_gtab1.

Types: Begin of ty_output,

ebeln type ekko-ebeln,

bsart type ekko-bsart, " esart,

lifnr type ekko-lifnr, "ELIFN,

ekorg type ekko-ekorg,

ekgrp type ekko-ekgrp, "BKGRP,

waers type ekko-waers,

bedat type ekko-bedat, "EBDAT,

frggr type ekko-frggr,

frgsx type ekko-frgsx,

frgke type ekko-frgke,

frgzu type ekko-frgzu,

txz01 type ekpo-txz01,

werks type ekpo-werks, "EWERK,

matkl type ekpo-matkl,

end of ty_output.

types: begin of ty_final,

ebeln type ekko-ebeln,

bsart type ekko-bsart,

lifnr type ekko-lifnr,

ekorg type ekko-ekorg,

ekgrp type ekko-ekgrp,

waers type ekko-waers,

bedat type ekko-bedat,

frggr type ekko-frggr,

frgsx type ekko-frgsx,

frgke type ekko-frgke,

frgzu type ekko-frgzu,

txz01 type ekpo-txz01,

werks type ekpo-werks,

matkl type ekpo-matkl,

lblni type essr-lblni,

erdat type essr-erdat,

netwr type essr-netwr,

end of ty_final.

data: gt_output type STANDARD TABLE OF ty_output.

data: wt_output type ty_output.

data: gt_final type STANDARD TABLE OF ty_final.

data: wt_final type ty_final.

*selection-screen begin of block b1 with frame title text-001.

*

• SELECT-OPTIONS: S_FRGCO FOR T16FC-FRGCO NO-EXTENSION no INTERVALS,

• S_FRGGR FOR T16FC-FRGGR.

*

• selection-screen end of block b1.

selection-screen begin of block b2 with frame TITLE text-002.

select-OPTIONS: S_EBELN FOR EKKO-EBELN,

S_BEDAT FOR EKKO-BEDAT,

S_BSART FOR EKKO-BSART,

S_LIFNR FOR EKKO-LIFNR,

S_EKORG FOR EKKO-EKORG,

S_EKGRP FOR EKKO-EKGRP,

S_WERKS FOR EKPO-WERKS,

S_MATKL FOR EKPO-MATKL,

S_FRGCO FOR T16FC-FRGCO NO-EXTENSION no INTERVALS,

S_FRGGR FOR T16FC-FRGGR.

SELECTION-SCREEN END OF BLOCK B2.

selection-screen begin of block b3 with frame title text-004.

parameters: p_ch1 as checkbox.

parameters: p_ch2 as checkbox .

parameters: p_ch3 as checkbox.

selection-screen end of block b3.

selection-screen begin of block b4 with frame TITLE text-003.

select-options: S_LBLNI FOR ESSR-LBLNI,

S_LBLNE FOR ESSR-LBLNE,

S_ErDAT FOR ESSR-ERDAT,

S_SPEC FOR ESSR-SPEC_NO,

S_BANFN FOR ESSR-BANFN,

S_WARPL FOR ESSR-WARPL,

S_FKNUM FOR ESSR-FKNUM.

SELECTION-SCREEN END OF BLOCK B4.

• fetching data**************************

select lblni

lblne

erdat

ebeln

netwr

banfn

warpl

fknum

spec_no

from essr into table it_gtab1

where lblni in s_lblni

and lblne in s_lblne

and erdat in s_erdat

and spec_no in s_spec

and banfn in s_banfn

and warpl in s_warpl

and fknum in s_fknum.

select t1~ebeln " ekko

t1~bsart

t1~lifnr

t1~ekorg

t1~ekgrp

t1~waers

t1~bedat

t1~frggr

t1~frgsx

t1~frgke

t1~frgzu

t2~txz01 "ekpo

t2~werks

t2~matkl

into corresponding fields of table gt_output

from ekko as t1 inner join ekpo as t2 on

t1_{ebeln = t2}ebeln

where t1~ebeln in s_ebeln

and t1~bsart in s_bsart

and t1~lifnr in s_lifnr

and t1~ekorg in s_ekorg

and t1~ekgrp in s_ekgrp

and t1~bedat in s_bedat

and t1~frggr in s_frggr

and t2~werks in s_werks

and t2~matkl in s_matkl.

• and t1~ebeln = wa_gtab1-lblni.

loop at gt_output into wt_output.

endloop.

Please any one can through some light.

Regards,

Am

[Former Member]

Hi Anitha,

as per my understanding you can create one common structure with all the fields that you need to display on the list instead of creating 3 diff structure you can write 1 select query and fetch the data from all the 3 tables and put it in your output internal table.

for the display of output if you need to display on List screen then use WRITE statement.

loop at gt_output into wt_output.

write: / wt_output-ebeln,

10 wt_output-bsart

15 wt_output-lifnr

20 wt_output-ekorg,

......all the fields.

this will print all the fileds at max length of 1024 chars on the screen

endloop.

or if you need to display in the ALV table pass this internal table and field catalog table to the FM REUSE_ALV_HIERSEQ_LIST_DISPLAY or REUSE_ALV_LIST_DISPLAY

u can check for post on how to display the data in an ALV table.

Thanks & Regards,

Ravi Aswani

[Former Member]

Hi,

As far as i have understood your problem.The solution is as below:

First of all instead of writing two select queries you can write just one query by using join statement on the three tables ie ESSR,EKKO and EKPO.The join statement can be like this:

SELECT a_{lblni a}lblne a_{erdat a}ebeln a_{netwr a}banfn a_{warpl a}fknum a~spec_no

t1_{ebeln t1} bsart t1_{lifnr t1}ekorg t1_{ekgrp t1}waers t1_{bedat t1}frggr

t1_{frgsx t1}frgke t1~frgzu

t2_{txz01 t2}werks t2~matkl

INTO CORRESPONDING FIELDS OF TABLE i_final

FROM ( ( essr AS a

INNER JOIN ekko AS t1 ON a_{ebeln EQ t1}ebeln )

INNER JOIN ekpo AS t2 ON t1_{ebeln = t2}ebeln )

WHERE a~lblni IN s_lblni

AND a~lblne IN s_lblne

AND a~erdat IN s_erdat

AND a~spec_no IN s_spec

AND a~banfn IN s_banfn

AND a~warpl IN s_warpl

AND a~fknum IN s_fknum

AND t1~ebeln IN s_ebeln

AND t1~bsart IN s_bsart

AND t1~lifnr IN s_lifnr

AND t1~ekorg IN s_ekorg

AND t1~ekgrp IN s_ekgrp

AND t1~bedat IN s_bedat

AND t1~frggr IN s_frggr

AND t2~werks IN s_werks

AND t2~matkl IN s_matkl.

This way we can avoid loops while populating the final internal table that will be used for display of the output.

Then you can loop on the final table and pass the corresponding values to the output display.

So your code can be as below:

TABLES: ekko,ekpo,t16fc,essr.

TYPES: BEGIN OF t_final,

lblni TYPE lblni,

lblne TYPE lblne,

erdat TYPE erdat,

ebeln TYPE ebeln,

netwr TYPE netwr,

banfn TYPE banfn,

warpl TYPE warpl,

fknum TYPE fknum,

spec_no TYPE spec_no,

*ebeln TYPE ekko-ebeln,

bsart TYPE ekko-bsart, " esart,

lifnr TYPE ekko-lifnr, "ELIFN,

ekorg TYPE ekko-ekorg,

ekgrp TYPE ekko-ekgrp, "BKGRP,

waers TYPE ekko-waers,

bedat TYPE ekko-bedat, "EBDAT,

frggr TYPE ekko-frggr,

frgsx TYPE ekko-frgsx,

frgke TYPE ekko-frgke,

frgzu TYPE ekko-frgzu,

txz01 TYPE ekpo-txz01,

werks TYPE ekpo-werks, "EWERK,

matkl TYPE ekpo-matkl,

END OF t_final.

DATA: i_final TYPE TABLE OF t_final.

REFRESH: i_final.

SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE text-002.

SELECT-OPTIONS: s_ebeln FOR ekko-ebeln,

s_bedat FOR ekko-bedat,

s_bsart FOR ekko-bsart,

s_lifnr FOR ekko-lifnr,

s_ekorg FOR ekko-ekorg,

s_ekgrp FOR ekko-ekgrp,

s_werks FOR ekpo-werks,

s_matkl FOR ekpo-matkl,

s_frgco FOR t16fc-frgco NO-EXTENSION NO INTERVALS,

s_frggr FOR t16fc-frggr.

SELECTION-SCREEN END OF BLOCK b2.

SELECTION-SCREEN BEGIN OF BLOCK b3 WITH FRAME TITLE text-004.

PARAMETERS: p_ch1 AS CHECKBOX.

PARAMETERS: p_ch2 AS CHECKBOX .

PARAMETERS: p_ch3 AS CHECKBOX.

SELECTION-SCREEN END OF BLOCK b3.

SELECTION-SCREEN BEGIN OF BLOCK b4 WITH FRAME TITLE text-003.

SELECT-OPTIONS: s_lblni FOR essr-lblni,

s_lblne FOR essr-lblne,

s_erdat FOR essr-erdat,

s_spec FOR essr-spec_no,

s_banfn FOR essr-banfn,

s_warpl FOR essr-warpl,

s_fknum FOR essr-fknum.

SELECTION-SCREEN END OF BLOCK b4.

SELECT a_{lblni a}lblne a_{erdat a}ebeln a_{netwr a}banfn a_{warpl a}fknum a~spec_no

t1_{ebeln t1} bsart t1_{lifnr t1}ekorg t1_{ekgrp t1}waers t1_{bedat t1}frggr

t1_{frgsx t1}frgke t1~frgzu

t2_{txz01 t2}werks t2~matkl

INTO CORRESPONDING FIELDS OF TABLE i_final

FROM ( ( essr AS a

INNER JOIN ekko AS t1 ON a_{ebeln EQ t1}ebeln )

INNER JOIN ekpo AS t2 ON t1_{ebeln = t2}ebeln )

WHERE a~lblni IN s_lblni

AND a~lblne IN s_lblne

AND a~erdat IN s_erdat

AND a~spec_no IN s_spec

AND a~banfn IN s_banfn

AND a~warpl IN s_warpl

AND a~fknum IN s_fknum

AND t1~ebeln IN s_ebeln

AND t1~bsart IN s_bsart

AND t1~lifnr IN s_lifnr

AND t1~ekorg IN s_ekorg

AND t1~ekgrp IN s_ekgrp

AND t1~bedat IN s_bedat

AND t1~frggr IN s_frggr

AND t2~werks IN s_werks

AND t2~matkl IN s_matkl.

IF sy-subrc EQ 0.

clear: w_final.

loop at i_final to w_final.

"move whichever fields you want in the output to be displayed to the final display internal table.

ENDIF.

<<point-begging removed and points unassigned for ignoring moderator warnings>>

Edited by: kishan P on Jan 9, 2012 1:39 PM

[Former Member]

Hi Prasad,

Thank's for u r answer .In my report i have to get in the output purchase order corresponding to entry sheet number as u can see in the t.code ml81.Can i get from u r qury entry sheet number also in my output corresponding to that purchase order.

Regards,

Am

Edited by: anitha.mava on Jan 10, 2012 6:40 AM

[former_member320332]

Hi Anitha,

Are you looing for relationship between these three tables than plase refer as below

EKKO-EBELN = EKPO-EBELN = ESSR-EBELN

EKPO-EBELP = ESSR-EBELN.

Go to SQVI and create Table join you will get the relationship between the tables.

Thanks,

Pawan