how do I get the MAKTX, BANFN data by using the modify statement ?

[gowthamigunji2000]

Report program1.

TYPES : begin of ty_sales,

VBELN type vbak-vbeln,

KUNNR type vbak-kunnr,

BSTNK type vbak-bstnk,

ERDAT type vbak-erdat,

WERKS type vbap-werks,

MATNR type VBAP-matnr,

fixmg type vbap-fixmg,

BANFN type ekpo-banfn,

MAKTX type makt-maktx,

END OF ty_sales.

data : itab type table of ty_sales,
wa type ty_sales.

PARAMETERS : p_VBELN type vbak-vbeln,

KUNNR type vbak-kunnr,

BSTNK type vbak-bstnk,

ERDAT type vbak-erdat,

WERKS type vbap-werks,

p_MATNR type mara-matnr.


Select vbak~VBELN vbak~KUNNR vbak~BSTNK vbak~ERDAT vbap~WERKS VBAP~MATNR vbap~fixmg
from vbak inner join vbap
on vbak~vbeln = vbap~vbeln
into table itab
where vbap~vbeln = p_vbeln.


if sy-subrc eq 0.

loop at itab into wa.

AT new vbeln.
write : 'sales ordre number :' COLOR 1,wa-vbeln.
ENDAT.

at new bstnk.
write: / 'customer purchase order number :' COLOR 2,wa-bstnk.
endat.

skip 1.

write : / 'material number :' color 3,wa-matnr.

write : / 'purchase order item delivery date :' color 4,wa-fixmg.

write: / 'material description :' COLOR 5,wa-maktx.
write: / 'purchase order requestion :' COLOR 6,wa-banfn.

uline.

ENDLOOP.

endif.

[anujawani2426]

Hi,

Please try below query.

You can print maktx by joining MAKT and VBAP tables on MATNR.

To print BANFN you can not directly join VBAK and EKPO. For that first you have join VBAK and VBFA tables on VBELN then join VBFA and EKPO tables on VBELV from VBFA and EBELN from EKPO.

Select vbak~VBELN vbak~KUNNR vbak~BSTNK vbak~ERDAT vbap~WERKS 
  VBAP~MATNR vbap~fixmg
  ekpo~banfn makt~maktx
from vbak inner join vbap
on vbak~vbeln = vbap~vbeln
INNER JOIN makt
  on makt~matnr = vbap~matnr
  INNER JOIN vbfa
  on vbfa~vbeln = vbak~vbeln
  INNER JOIN ekpo
  on vbfa~vbelv = ekpo~ebeln
  and vbfa~vbtyp_v = 'V'
into table itab
where vbap~vbeln = p_vbeln.

Regards,

Anuja Kawadiwale

[former_member640214]

SELECT vbak~vbeln vbak~kunnr vbak~bstnk vbak~erdat vbap~werks
  vbap~matnr vbap~fixmg
  ekpo~banfn makt~maktx
FROM vbak INNER JOIN vbap
ON vbak~vbeln = vbap~vbeln
LEFT OUTER JOIN makt
  ON makt~matnr = vbap~matnr
  AND spras = sy-langue
  INNER JOIN vbfa
  ON vbfa~vbeln = vbak~vbeln
  AND vbfa~vbtyp_v = 'V'
  INNER JOIN ekpo
  ON ekpo~ebeln = vbfa~vbelv
INTO TABLE itab
WHERE vbap~vbeln = p_vbeln.

[gowthamigunji2000]

TYPES : begin of ty_sales,

VBELN type vbak-vbeln,

KUNNR type vbak-kunnr,

BSTNK type vbak-bstnk,

ERDAT type vbak-erdat,

WERKS type vbap-werks,

MATNR type VBAP-matnr,

fixmg type ekpo-fixmg,

BANFN type ekpo-banfn,

MAKTX type makt-maktx,

END OF ty_sales.

data : itab type table of ty_sales,
wa type ty_sales.



PARAMETERS : p_VBELN type vbak-vbeln,
*
* KUNNR type vbak-kunnr,
*
* BSTNK type vbak-bstnk,
*
* ERDAT type vbak-erdat,
*
* WERKS type vbap-werks,

p_MATNR type mara-matnr.


*Select vbak~VBELN, vbak~kunnr, vbak~BSTNK,vbak~erdat, vbap~werks, VBAP~MATNR, ekpo~fixmg, ekpo~banfn, makt~maktx
*from vbak
*inner join vbap
* on vbak~vbeln = vbap~vbeln
*INNER JOIN makt
* on makt~matnr = vbap~matnr
*INNER JOIN ekpo
* on ekpo~matnr = makt~matnr
*into table @itab
*where vbak~vbeln = @p_vbeln.

* Select vbak~VBELN, vbak~KUNNR, vbak~BSTNK, vbak~ERDAT, vbap~WERKS,
* VBAP~MATNR, vbap~fixmg,
* ekpo~banfn, makt~maktx
*from vbak inner join vbap
*on vbak~vbeln = vbap~vbeln
*INNER JOIN makt
* on makt~matnr = vbap~matnr
* INNER JOIN vbfa
* on vbfa~vbeln = vbak~vbeln
* INNER JOIN ekpo
* on vbfa~vbelv = ekpo~ebeln
* and vbfa~vbtyp_v = 'V'
*into table @itab
*where vbap~vbeln = @p_vbeln.

SELECT vbak~vbeln vbak~kunnr vbak~bstnk vbak~erdat vbap~werks
vbap~matnr vbap~fixmg
ekpo~banfn makt~maktx
FROM vbak INNER JOIN vbap
ON vbak~vbeln = vbap~vbeln
LEFT OUTER JOIN makt
ON makt~matnr = vbap~matnr
AND spras = sy-langue
INNER JOIN vbfa
ON vbfa~vbeln = vbak~vbeln
AND vbfa~vbtyp_v = 'V'
INNER JOIN ekpo
ON ekpo~ebeln = vbfa~vbelv
INTO TABLE itab
WHERE vbap~vbeln = p_vbeln.


if sy-subrc eq 0.

loop at itab into wa.

*AT new vbeln.
write : 'sales ordre number :' COLOR 1,wa-vbeln.
*ENDAT.

*at new bstnk.
write: / 'customer purchase order number :' COLOR 2,wa-bstnk.
*endat.

skip 1.

*at new matnr.
write : / 'material number :' color 3,wa-matnr.
*ENDAT.

write: / 'material description :' COLOR 5,wa-maktx.

write : / 'purchase order item delivery date :' color 4,wa-fixmg.


write: / 'purchase order requesition :' COLOR 6,wa-banfn.

uline.

ENDLOOP.
endif.

this is not executing.

[gowthamigunji2000]

TYPES : begin of ty_sales,

VBELN type vbak-vbeln,

KUNNR type vbak-kunnr,

BSTNK type vbak-bstnk,

ERDAT type vbak-erdat,

WERKS type vbap-werks,

MATNR type VBAP-matnr,

fixmg type ekpo-fixmg,

BANFN type ekpo-banfn,

MAKTX type makt-maktx,

END OF ty_sales.

data : itab type table of ty_sales,
wa type ty_sales.



PARAMETERS : p_VBELN type vbak-vbeln,
*
* KUNNR type vbak-kunnr,
*
* BSTNK type vbak-bstnk,
*
* ERDAT type vbak-erdat,
*
* WERKS type vbap-werks,

p_MATNR type mara-matnr.


*Select vbak~VBELN, vbak~kunnr, vbak~BSTNK,vbak~erdat, vbap~werks, VBAP~MATNR, ekpo~fixmg, ekpo~banfn, makt~maktx
*from vbak
*inner join vbap
* on vbak~vbeln = vbap~vbeln
*INNER JOIN makt
* on makt~matnr = vbap~matnr
*INNER JOIN ekpo
* on ekpo~matnr = makt~matnr
*into table @itab
*where vbak~vbeln = @p_vbeln.

* Select vbak~VBELN, vbak~KUNNR, vbak~BSTNK, vbak~ERDAT, vbap~WERKS,
* VBAP~MATNR, vbap~fixmg,
* ekpo~banfn, makt~maktx
*from vbak inner join vbap
*on vbak~vbeln = vbap~vbeln
*INNER JOIN makt
* on makt~matnr = vbap~matnr
* INNER JOIN vbfa
* on vbfa~vbeln = vbak~vbeln
* INNER JOIN ekpo
* on vbfa~vbelv = ekpo~ebeln
* and vbfa~vbtyp_v = 'V'
*into table @itab
*where vbap~vbeln = @p_vbeln.

SELECT vbak~vbeln vbak~kunnr vbak~bstnk vbak~erdat vbap~werks
vbap~matnr vbap~fixmg
ekpo~banfn makt~maktx
FROM vbak INNER JOIN vbap
ON vbak~vbeln = vbap~vbeln
LEFT OUTER JOIN makt
ON makt~matnr = vbap~matnr
AND spras = sy-langue
INNER JOIN vbfa
ON vbfa~vbeln = vbak~vbeln
AND vbfa~vbtyp_v = 'V'
INNER JOIN ekpo
ON ekpo~ebeln = vbfa~vbelv
INTO TABLE itab
WHERE vbap~vbeln = p_vbeln.


if sy-subrc eq 0.

loop at itab into wa.

*AT new vbeln.
write : 'sales ordre number :' COLOR 1,wa-vbeln.
*ENDAT.

*at new bstnk.
write: / 'customer purchase order number :' COLOR 2,wa-bstnk.
*endat.

skip 1.

*at new matnr.
write : / 'material number :' color 3,wa-matnr.
*ENDAT.

write: / 'material description :' COLOR 5,wa-maktx.

write : / 'purchase order item delivery date :' color 4,wa-fixmg.


write: / 'purchase order requesition :' COLOR 6,wa-banfn.

uline.

ENDLOOP.
endif.

this is not executing.

[venkateswaran_k]

Hi

You can get the Banfn ( from eban table as below )

Relate VBAP vbeln + item number to VBEP vbeln + item number

VBAP-VBELN. = VBEP-VBELN

then   VBEP-BANFN will give you EBAN-BANFN

[anujawani2426]

Hi,

Please try below code. It's working.

I selected banfn field from vbep table and joined that table with vbak table.

TYPES : begin of ty_sales,
VBELN type vbak-vbeln,
KUNNR type vbak-kunnr,
BSTNK type vbak-bstnk,
ERDAT type vbak-erdat,
WERKS type vbap-werks,
MATNR type VBAP-matnr,
fixmg type ekpo-fixmg,
BANFN type vbep-banfn,
MAKTX type makt-maktx,
END OF ty_sales.
data : itab type table of ty_sales,
wa type ty_sales.
parameters : p_VBELN type vbak-vbeln,
*
* KUNNR type vbak-kunnr,
*
* BSTNK type vbak-bstnk,
*
* ERDAT type vbak-erdat,
*
* WERKS type vbap-werks,
p_MATNR type mara-matnr.
*Select vbak~VBELN, vbak~kunnr, vbak~BSTNK,vbak~erdat, vbap~werks, VBAP~MATNR, ekpo~fixmg, ekpo~banfn, makt~maktx
*from vbak
*inner join vbap
* on vbak~vbeln = vbap~vbeln
*INNER JOIN makt
* on makt~matnr = vbap~matnr
*INNER JOIN ekpo
* on ekpo~matnr = makt~matnr
*into table @itab
*where vbak~vbeln = @p_vbeln.
* Select vbak~VBELN, vbak~KUNNR, vbak~BSTNK, vbak~ERDAT, vbap~WERKS,
* VBAP~MATNR, vbap~fixmg,
* ekpo~banfn, makt~maktx
*from vbak inner join vbap
*on vbak~vbeln = vbap~vbeln
*INNER JOIN makt
* on makt~matnr = vbap~matnr
* INNER JOIN vbfa
* on vbfa~vbeln = vbak~vbeln
* INNER JOIN ekpo
* on vbfa~vbelv = ekpo~ebeln
* and vbfa~vbtyp_v = 'V'
*into table @itab
*where vbap~vbeln = @p_vbeln.
SELECT distinct vbak~vbeln vbak~kunnr vbak~bstnk vbak~erdat vbap~werks
vbap~matnr vbap~fixmg
vbep~banfn makt~maktx
FROM vbak INNER JOIN vbap
ON vbak~vbeln = vbap~vbeln
INNER JOIN makt
ON makt~matnr = vbap~matnr
AND spras = sy-langu
INNER JOIN vbep
on vbak~vbeln = vbep~vbeln
INTO TABLE itab
WHERE vbap~vbeln = p_vbeln.
if sy-subrc eq 0.
loop at itab into wa.
*AT new vbeln.
write : 'sales ordre number :' COLOR 1,wa-vbeln.
*ENDAT.
*at new bstnk.
write: / 'customer purchase order number :' COLOR 2,wa-bstnk.
*endat.
skip 1.
*at new matnr.
write : / 'material number :' color 3,wa-matnr.
*ENDAT.
write: / 'material description :' COLOR 5,wa-maktx.
write : / 'purchase order item delivery date :' color 4,wa-fixmg.
write: / 'purchase order requesition :' COLOR 6,wa-banfn.
uline.
ENDLOOP.
endif.

[gowthamigunji2000]

thank you.

instead of select, I have to use select distinct.

but, here I didn't get the o/p of two fields that are

purchase order item delivery date(FIXMG) & purchase order requisition(BANFN)