10-14-2007 5:22 PM
Hi All,
I am trying to build a report, where i want the output to be as follows:
Plant | Mtl | Mtl Descpn | Moving avg price | Fixed Vendor Indicator | Vendor No. | Vendor Name | Net Price
The data for Plant & Mtl (MARC), Descpn (MAKT), Mvg Avg price (MBEW), Fixed Vendor Indicator & Vendor no. (EORD), Vendor name (LFA1), net price (EINE).
I tried joining the tables in SQVI, but did not get the output as desired, i believe this can be done with some ABAp lines in infoset & use this infoset for my SQVI. The logic i am looking for is, if the Fixed vendor indicator is ticked in EORD, i want to display the vendor no. & the net price, if it is not, then i want the fields to remain blank. Hope my problem is clear, await inputs.
Vivek
10-15-2007 10:00 AM
hi, copy and paste this code in se38 and press f8 and see the o/p.
Report ztest.
data: begin of itab occurs 0,
matnr like marc- matnr "material number
werks like marc-werks, "plant
maktx like makt-maktx, "description
vmver like mbew-vmver , "moving average price
end of itab.
final internal table
data: begin of t_vendor occurs 0,
<b>include itab.</b> "including data from the above intenal table
data:
flifn like eord-flifn, "fixed vendor indicator
name1 like lfa1-name1, "name
netpr like eine-netpr, "net price
end of t_vendor.
*********selecting data from marc, makt and mbew into itab
select awerks amatnr bmaktx cvmver from marc as a inner join makt as b inner join mbew as c on amatnr = bmatnr = c~matnr into corresponding fields of table itab.
*******moving data from itab to the final intenal table also selecting the remaining three fields flifn,name1 and netpr
loop at itab.
t_vendor-matnr = itab-matnr.
t_vendor-werks = itab-werks.
t_vendor-maktx = itab-maktx.
t_vendor-vmver = itab-vmver.
select single flifn from eord into t_vendor-flifn where werks = itab-werks.
select single name1 from lfa1 into t_vendor-name where werks = itab-werks.
select single netpr from eine into t_vendor-netpr where werks = itab-werks.
append t_vendor.
endloop.
***********display data
loop at t_vendor.
write 😕 t_vendor-matnr,t_vendor-werks, t_vendor-maktx,t_vendor-vmver,t_vendor-flifn,t_vendor-name1,t_vendor-netpr.
endloop.
10-14-2007 6:48 PM
Please use a meaningful subject if you expect people to help you.
Thank you.
Regards, Clemens
10-15-2007 5:02 AM
Mr. Clemens,
Thanks for the advice, but would have been nice, if you could have given me some hints or advice on how i should go about it, along with your comment, as i am new to this field.
Vivek
10-15-2007 6:53 AM
Any ABAper who can help me with this small coding??? It would be kind if you can let me know how i can extract only those info records of materials, which have a fixed source.
Await inputs
Vivek
10-15-2007 7:31 AM
Hi ,,
try this code and see.. and do respective modifications
REPORT ZND_test line-size 300.
*------------**********************--------------------*
Tables : mara,
marc,
werks,
eord,
eine.
data: begin of t_final occurs 0,
matnr like marc-matnr,
werks like marc-werks,
maktx like makt-maktx,
VERPR like mbew-VERPR,
FLIFN like eord-FLIFN,
LIFNR like eord-LIFNR,
NAME1 like lfa1-NAME1,
NETPR like eine-NETPR,
end of t_final.
data: begin of t_marc occurs 0,
matnr like marc-matnr,
werks like marc-werks,
end of t_marc.
data: begin of t_makt occurs 0,
matnr like marc-matnr,
spras like makt-maktx,
maktx like makt-maktx,
end of t_makt.
data: begin of t_mbew occurs 0,
matnr like marc-matnr,
bwkey like mbew-bwkey,
VERPR like mbew-VERPR,
end of t_mbew.
data: begin of t_eord occurs 0,
matnr like marc-werks,
werks like marc-werks,
lifnr like eord-lifnr,
FLIFN like eord-flifn,
end of t_eord.
data: begin of t_lfa1 occurs 0,
lifnr like lfa1-lifnr,
name1 like lfa1-name1,
end of t_lfa1.
data: begin of t_eine occurs 0,
werks like marc-werks,
NETPR like eine-netpr,
end of t_eine.
start-of-selection.
selection-screen : begin of block 1 with frame title text-001.
select-options: s_matnr for marc-matnr no-extension.
select-options: s_werks for marc-werks no-extension.
selection-screen: end of block 1.
select matnr werks
from marc
into table t_marc
where matnr in s_matnr and
werks in s_werks.
sort t_marc by matnr werks.
if not t_marc[] is initial.
select matnr spras maktx
from makt
into table t_makt
for all entries in t_marc
where matnr = t_marc-matnr and
spras = sy-langu.
sort t_makt by matnr.
select matnr bwkey verpr
from mbew
into table t_mbew
for all entries in t_marc
where matnr = t_marc-matnr and
bwkey = t_marc-werks.
sort t_mbew by matnr bwkey.
select matnr werks lifnr FLIFN
from eord
into table t_eord
for all entries in t_marc
where matnr = t_marc-matnr and
werks = t_marc-werks.
sort t_eord by matnr werks.
select werks netpr
from eine
into table t_eine
for all entries in t_marc
where werks = t_marc-werks.
endif.
if not t_lfa1[] is initial.
select lifnr name1
from lfa1
into table t_lfa1
for all entries in t_eord
where lifnr = t_eord-lifnr.
sort t_lfa1 by lifnr name1.
endif.
loop at t_marc.
read table t_makt binary search with key
matnr = t_marc-matnr.
if sy-subrc = 0.
t_final-matnr = t_marc-matnr.
t_final-werks = t_marc-werks.
t_final-maktx = t_makt-maktx.
append t_final.
endif.
endloop.
*-------------mbew
loop at t_final.
read table t_mbew binary search with key
matnr = t_final-matnr
bwkey = t_final-werks.
if sy-subrc = 0.
t_final-verpr = t_mbew-verpr.
modify t_final transporting verpr
where matnr = t_final-matnr and
werks = t_final-werks.
endif.
endloop.
*-----------eord
loop at t_final.
read table t_eord binary search with key
matnr = t_final-matnr
werks = t_final-werks.
if sy-subrc = 0.
t_final-lifnr = t_eord-lifnr.
t_final-flifn = t_eord-flifn.
modify t_final transporting lifnr FLIFN
where matnr = t_final-matnr and
werks = t_final-werks.
endif.
endloop.
*------------eine
loop at t_final.
read table t_eine binary search with key
werks = t_final-werks.
if sy-subrc = 0.
t_final-netpr = t_eine-netpr.
modify t_final transporting netpr
where matnr = t_final-matnr and
werks = t_final-werks.
endif.
endloop.
*-------------lfa1
loop at t_final.
read table t_lfa1 binary search with key
lifnr = t_final-lifnr.
if sy-subrc = 0.
t_final-name1 = t_lfa1-name1.
modify t_final transporting name1
where matnr = t_final-matnr and
werks = t_final-werks and
lifnr = t_final-lifnr.
endif.
endloop.
write :/'Plant'(002),
5 'Mtl Descn'(003),
40 'Mvg avg price'(004),
60 'Fixed vendor Indicator'(005),
80 'Vendor no'(006),
100 'Vendor Name'(007),
120 'net price'(008).
loop at t_final.
write:/ t_final-werks under text-002,
t_final-verpr under text-003,
t_final-maktx under text-004.
<b> if t_final-flifn = 'X'.
write: t_final-flifn under text-005,
t_final-lifnr under text-006,
t_final-name1 under text-007,
t_final-netpr under text-008.
endif.
</b>
endloop.
10-15-2007 8:59 AM
Hi Naveena,
Thanks a lot for the piece of code which u have posted. I am not an ABAper, but this is what i have understood, let me know if what i have understood is right
1. I need to join the tables (MARA, MARC, WERKS, EORD, EINE) in Infoset (SQ02) using Table Join option.
2. I enter the Data portion of your code in the DATA section of the coding.
3. I enter the code from start-of-selection portion till the end in SELECTION portion of the infoset.
4. The matl description lang which will be picked is dependent on what i have set as my default lang for my login
5. I then use this infoset as my data source in SQVI
6. The code for output does not show material number i.e. in the Write section, how should i modify the same?
Clarification required:
1. If i need to add additional fields from MARC to be displayed, say eg: PLIFZ or WEBAZ, can you let me know where all shoudl i modify
2. If i need to add one more selection criteria coming from MARC, where all should i modify?
Vivek
10-15-2007 9:04 AM
Hi
Vivek Sq01 and all other will be benefitable if u use for small tables so try to use Join condition in your report check this sample report which resembles your requirement.
REPORT zmmpavan.
******************************
* tables
******************************
TABLES: ekko,ekbe,ekpo,ekkn,konh,konv,esll,ekbz,rseg,lfa1,j_1ipart1,
j_1igrxref.
TYPE-POOLS: slis.
******************
*data definituon
*******************
DATA:
wa_ekko LIKE ekko,
wa_ekbe LIKE ekbe,
wa_ekkn LIKE ekkn,
wa_konp LIKE konp,
wa_ekpo LIKE ekpo,
wa_rbkp LIKE rbkp,
wa_j_1ipart1 LIKE j_1ipart1,
wa_j_1igrxref LIKE j_1igrxref,
wa_rseg LIKE rseg OCCURS 0 WITH HEADER LINE.
DATA: ws_vakey LIKE konh-vakey,
ws_kbetr LIKE konp-kbetr,
ws_kbetr1 LIKE konp-kbetr,
ws_kbetr2 LIKE konp-kbetr,
ws_ecs LIKE j_1igrxref-ecs,
ws_ebelp LIKE ekbz-ebelp,
ws_ebeln LIKE ekko-ebeln,
ws_belnr LIKE ekbe-belnr,
ws_beznk LIKE rbkp-beznk,
ws_wrbtr_f LIKE ekbz-wrbtr,
ws_rmwwr LIKE rbkp-rmwwr,
ws_rbkp_benz.
**********************************
*internal tables
*********************************
DATA: BEGIN OF int_outtab OCCURS 0,
lifnr LIKE ekko-lifnr, " vend no
name1 LIKE lfa1-name1, " vend name
werks LIKE ekpo-werks, " plant
ebeln LIKE ekko-ebeln, " po no
knumv LIKE ekko-knumv,
ebelp LIKE ekbe-ebelp,
matnr LIKE ekpo-matnr, " mat no
txz01 LIKE ekpo-txz01, " mat desceiption
kostl LIKE ekkn-kostl, " cost center
ps_psp_pnr LIKE ekkn-ps_psp_pnr, " WBS element
netwr LIKE ekpo-netwr,
" basic value (po)
kbetr LIKE konp-kbetr,
" excise value (po)
kwert_i LIKE konv-kwert,
" insurance value (po)
kwert_f LIKE konv-kwert,
" frieght value (po)
kwert_t LIKE konv-kwert,
" vat/tax value (po)
kwert_o LIKE konv-kwert,
" others (po)
kwert_total LIKE konv-kwert,
" total (po)
ecs LIKE j_1igrxref-ecs,
" excise (ap)
wmwst1 LIKE rbkp-wmwst1,
" tax amount (ap)
wrbtr_f TYPE ekbz-wrbtr,
" freight (ap)
wrbtr TYPE ekbz-wrbtr,
beznk LIKE rbkp-beznk,
" Unplanned cost (ap)
refwr_g TYPE ekbe-refwr,
" gross (ap)
rmwwr LIKE rbkp-rmwwr,
" Actual Invoice Total (ap)
buzei LIKE ekbz-buzei, "item for material doc
gjahr LIKE ekbe-gjahr,
tax LIKE ekbe-wrbtr, " tax (ap)
actual_total LIKE ekbe-wrbtr,
* belnr LIKE rseg-belnr,
belnr LIKE ekbe-belnr,
vakey LIKE konh-vakey,
cpudt LIKE ekbe-cpudt,
budat LIKE ekbz-budat,
belnr_ekbz LIKE ekbz-belnr, "to get excise value
* aedat LIKE ekpo-aedat,
bedat LIKE ekko-bedat,
waers LIKE lfm1-waers, " currency
END OF int_outtab.
DATA: int_konv LIKE konv OCCURS 0 WITH HEADER LINE,
* int_ekbe LIKE ekbe OCCURS 0 WITH HEADER LINE,
int_ekbz LIKE ekbz OCCURS 0 WITH HEADER LINE.
DATA: BEGIN OF int_konh OCCURS 0,
knumh LIKE konh-knumh,
kschl LIKE konh-kschl,
vakey LIKE konh-vakey,
END OF int_konh.
DATA: BEGIN OF int_konp OCCURS 0,
knumh LIKE konp-knumh,
kschl LIKE konp-kschl,
kbetr LIKE konp-kbetr,
END OF int_konp.
DATA: BEGIN OF int_ekkn OCCURS 0,
ebeln LIKE ekkn-ebeln,
ebelp LIKE ekkn-ebelp,
kostl LIKE ekkn-kostl,
ps_psp_pnr LIKE ekkn-ps_psp_pnr,
END OF int_ekkn.
DATA: BEGIN OF int_outtab1 OCCURS 0,
exbed LIKE j_1igrxref-exbed,
ecs LIKE j_1igrxref-ecs,
END OF int_outtab1.
DATA: BEGIN OF int_ekbe OCCURS 0,
ebelp LIKE ekbe-ebelp,
refwr LIKE ekbe-refwr,
wrbtr LIKE ekbe-wrbtr,
shkzg LIKE ekbe-shkzg,
END OF int_ekbe.
DATA: BEGIN OF int_total OCCURS 0,
ebeln LIKE ekko-ebeln,
ebelp LIKE ekbe-ebelp,
actual_total LIKE ekbe-wrbtr,
END OF int_total.
DATA: BEGIN OF int_excise OCCURS 0,
ebeln LIKE ekko-ebeln,
kbetr LIKE konp-kbetr,
END OF int_excise.
DATA: BEGIN OF int_rseg OCCURS 0,
ebeln LIKE rseg-ebeln,
ebelp LIKE rseg-ebelp,
lfbnr LIKE rseg-lfbnr,
matnr LIKE rseg-matnr,
lfgja LIKE rseg-lfgja,
belnr LIKE rseg-belnr,
ecs LIKE j_1igrxref-ecs,
beznk LIKE rbkp-beznk,
wrbtr_f LIKE ekbz-wrbtr,
rmwwr LIKE rbkp-rmwwr,
END OF int_rseg.
DATA: BEGIN OF int_rbkp OCCURS 0,
belnr LIKE rbkp-belnr,
beznk LIKE rbkp-beznk,
rmwwr LIKE rbkp-rmwwr,
tcode LIKE rbkp-tcode,
END OF int_rbkp.
**************************
****ALV list definintion
*************************
DATA: ws_cat TYPE slis_t_fieldcat_alv ,
int_cat TYPE slis_t_fieldcat_alv WITH HEADER LINE.
DATA: g_container TYPE scrfname VALUE 'BCALV_GRID_DEMO_0100_CONT1',
g_custom_container TYPE REF TO cl_gui_custom_container.
*****************
*selection-screen
******************
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECTION-SCREEN SKIP 2.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 19(23) text-002.
SELECT-OPTIONS: s_lifnr FOR ekko-lifnr obligatory.
*PARAMETERS:p_lifnr LIKE ekko-lifnr .
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 25(17) text-006.
*PARAMETERS:p_ebeln LIKE ekko-ebeln obligatory.
SELECT-OPTIONS:s_ebeln FOR ekko-ebeln .
SELECTION-SCREEN END OF LINE.
*SELECTION-SCREEN BEGIN OF LINE.
*SELECTION-SCREEN COMMENT 25(23) text-003.
*PARAMETERS:p_ekorg LIKE ekko-ekorg.
*SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN BEGIN OF LINE.
*SELECTION-SCREEN COMMENT 25(20) text-004.
*PARAMETERS:p_werks LIKE ekpo-werks obligatory.
*SELECT-OPTIONS: S_werks FOR ekpo-werks obligatory.
SELECTION-SCREEN END OF LINE.
*SELECTION-SCREEN BEGIN OF LINE.
*SELECTION-SCREEN COMMENT 25(23) text-005.
*SELECT-OPTIONS:s_bedat FOR ekko-bedat.
*SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK b1.
START-OF-SELECTION.
PERFORM get_data.
PERFORM field_catalog.
PERFORM display_data.
END-OF-SELECTION.
*&---------------------------------------------------------------------*
*& Form get_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM get_data.
*************************
* start of Vender details
*************************
***vender no, vender name, po no,service/mat no, service/mat description
SELECT ekko~lifnr ekko~ebeln ekko~knumv ekko~bedat
ekpo~werks ekpo~matnr ekpo~txz01 ekpo~werks
ekpo~netwr
* ekpo~aedat
ekbe~ebelp ekbe~belnr
lfa1~name1
INTO CORRESPONDING FIELDS OF TABLE int_outtab
FROM ( ( ( ekko
JOIN ekbe ON ekbe~ebeln = ekko~ebeln AND
ekbe~vgabe = '2' )
JOIN ekpo ON ekpo~ebeln = ekko~ebeln AND
ekpo~ebelp = ekbe~ebelp )
JOIN lfa1 ON lfa1~lifnr = ekko~lifnr )
WHERE
* ekko~lifnr = P_lifnr AND
* ekko~ebeln IN S_ebeln AND
** ekpo~werks IN S_werks AND
ekbe~vgabe = '2'.
***WBS/Cost center
SELECT ebeln ebelp kostl ps_psp_pnr FROM ekkn INTO int_ekkn
FOR ALL ENTRIES IN int_outtab
WHERE ebeln = int_outtab-ebeln AND
ebelp = int_outtab-ebelp.
APPEND int_ekkn.
ENDSELECT.
LOOP AT int_outtab.
ON CHANGE OF int_outtab-ebeln OR int_outtab-ebelp.
LOOP AT int_ekkn WHERE ebeln = int_outtab-ebeln AND
ebelp = int_outtab-ebelp.
IF int_ekkn-kostl <> ' '.
MOVE int_ekkn-kostl TO int_outtab-kostl.
ELSE.
MOVE int_ekkn-ps_psp_pnr TO int_outtab-kostl.
" int_outtab-ps_psp_pnr.
ENDIF.
ENDLOOP.
MODIFY int_outtab.
ENDON.
ENDLOOP.
***************************
*end of Vender details*****
***************************
*********************************
*******Start of calculation of PO
*********************************
*****Excise calculation of po
LOOP AT int_outtab.
CONCATENATE int_outtab-werks int_outtab-lifnr int_outtab-matnr INTO
ws_vakey.
MOVE ws_vakey TO int_outtab-vakey.
MODIFY int_outtab.
ENDLOOP.
LOOP AT int_outtab.
* ON CHANGE OF int_outtab-ebeln OR int_outtab-vakey.
ON CHANGE OF int_outtab-ebelp.
SELECT knumh kschl vakey FROM konh INTO int_konh
* FOR ALL ENTRIES IN int_outtab
WHERE vakey = int_outtab-vakey AND
datab <= int_outtab-bedat AND
datbi > int_outtab-bedat AND
( kschl = 'JMOP' OR kschl = 'JEC1' ).
SELECT knumh kschl kbetr FROM konp INTO int_konp
WHERE knumh = int_konh-knumh .
IF int_konp-kschl = 'JMOP'.
ws_kbetr = int_konp-kbetr / 1000 * int_outtab-netwr.
ws_kbetr1 = ws_kbetr.
ENDIF.
IF int_konp-kschl = 'JEC1'.
ws_kbetr2 = int_konp-kbetr / 1000 * ws_kbetr1.
ENDIF.
ws_kbetr = ws_kbetr + ws_kbetr2.
ENDSELECT.
ENDSELECT.
ENDON.
* ENDON.
MOVE ws_kbetr TO int_outtab-kbetr.
MODIFY int_outtab.
CLEAR: ws_kbetr, ws_kbetr1, ws_kbetr2.
ENDLOOP.
LOOP AT int_outtab.
* ON CHANGE OF int_outtab-ebeln OR int_outtab-vakey.
int_excise-kbetr = int_outtab-kbetr.
int_excise-ebeln = int_outtab-ebeln.
APPEND int_excise.
* ENDON.
ENDLOOP.
LOOP AT int_outtab.
ON CHANGE OF int_outtab-ebeln.
LOOP AT int_excise WHERE ebeln = int_outtab-ebeln.
ws_kbetr = ws_kbetr + int_excise-kbetr.
ENDLOOP.
ENDON.
int_outtab-kbetr = ws_kbetr.
MODIFY int_outtab.
CLEAR ws_kbetr.
ENDLOOP.
***** Insurance, Freight,Vat/ST, Other, Total***********
LOOP AT int_outtab.
ON CHANGE OF int_outtab-ebelp OR int_outtab-ebeln.
CLEAR int_konv.
SELECT * FROM konv INTO CORRESPONDING FIELDS OF int_konv
WHERE knumv = int_outtab-knumv AND
kposn = int_outtab-ebelp AND
( ( kschl <> 'RA00' )
AND ( kschl <> 'RA01' ) AND ( kschl <> 'RB00' )
AND ( kschl <> 'PBXX' ) AND ( kschl <> 'PB00' )
AND ( kschl <> 'RC00' ) AND ( kschl <> 'RL01' )
AND ( kschl <> 'ZC00' ) AND ( kschl <> 'ZA00' )
AND ( kschl <> 'ZA01' ) AND ( kschl <> 'HB01' )
AND ( kschl <> 'ZBED' ) AND ( kschl <> 'ZAED' )
AND ( kschl <> 'ZSED' ) AND ( kschl <> 'ZAE1' )
AND ( kschl <> 'ZSE1' ) AND ( kschl <> 'ZCEX' )
AND ( kschl <> 'ZPRO' ) AND ( kschl <> 'ZTRD' )
AND ( kschl <> 'ZPK4' ) AND ( kschl <> 'ZSTX' )
AND ( kschl <> 'ZASS' ) AND ( kschl <> 'ZJCD' )
AND ( kschl <> 'HB00') ).
APPEND int_konv.
ENDSELECT.
ENDON.
ENDLOOP.
LOOP AT int_outtab.
ON CHANGE OF int_outtab-ebelp OR int_outtab-knumv.
LOOP AT int_konv WHERE kposn = int_outtab-ebelp AND
knumv = int_outtab-knumv.
* lifnr = int_outtab-lifnr.
***** tax
IF ( ( int_konv-kschl = 'NAVS' ) OR ( int_konv-kschl = 'NAVM' )
).
int_outtab-kwert_t = int_outtab-kwert_t + int_konv-kwert.
ELSE.
*****freight
IF ( ( int_konv-kschl = 'FRA1' ) OR ( int_konv-kschl = 'FRB1'
)
OR ( int_konv-kschl = 'FRC1'
) ).
int_outtab-kwert_f = int_outtab-kwert_f + int_konv-kwert.
ELSE.
*****insurance
IF ( ( int_konv-kschl = 'ZGIN' ) OR ( int_konv-kschl =
'ZIN2' ) ).
int_outtab-kwert_i = int_outtab-kwert_i + int_konv-kwert.
ELSE.
****others
IF NOT ( ( int_konv-kschl = 'NAVS' ) OR (
int_konv-kschl = 'NAVM' )
OR ( int_konv-kschl = 'FRA1' ) OR (
int_konv-kschl = 'FRB1' )
OR ( int_konv-kschl = 'FRC1' ) OR (
int_konv-kschl = 'ZGIN' )
OR ( int_konv-kschl = 'ZIN2' ) OR (
int_konv-kschl = 'RA00' )
OR ( int_konv-kschl = 'RA01' ) OR (
int_konv-kschl = 'RB00' )
OR ( int_konv-kschl = 'PBXX' ) OR (
int_konv-kschl = 'PB00' )
OR ( int_konv-kschl = 'RC00' ) OR (
int_konv-kschl = 'RL01' ) ).
int_outtab-kwert_o = int_outtab-kwert_o +
int_konv-kwert.
ENDIF.
ENDIF.
ENDIF.
ENDIF.
ENDLOOP.
****total
int_outtab-kwert_total = int_outtab-kwert_t
+ int_outtab-kwert_f
+ int_outtab-kwert_i
+ int_outtab-kwert_o
+ int_outtab-netwr.
ENDON.
MODIFY int_outtab.
ENDLOOP.
*********************************
******End of calculation of PO
*********************************
*********************************************
*start of calculation for actual payment
*********************************************
*****Excise
sort int_outtab by ebelp ebeln.
LOOP AT int_outtab.
ON CHANGE OF int_outtab-ebelp OR int_outtab-ebeln.
SELECT * FROM rseg INTO CORRESPONDING FIELDS OF int_rseg
* FOR ALL ENTRIES IN int_outtab
WHERE ebeln = int_outtab-ebeln AND
ebelp = int_outtab-ebelp.
APPEND int_rseg.
ENDSELECT.
ENDON.
ENDLOOP.
sort int_rseg by ebelp lfbnr matnr.
LOOP AT int_rseg.
ON CHANGE OF int_outtab-ebelp OR int_rseg-lfbnr OR int_rseg-matnr OR
int_rseg-lfgja.
SELECT * FROM j_1ipart1 INTO wa_j_1ipart1
WHERE mblnr = int_rseg-lfbnr AND
mjahr = int_rseg-lfgja AND
matnr = int_rseg-matnr.
ON CHANGE OF wa_j_1ipart1-mblnr OR wa_j_1ipart1-zeile.
SELECT * FROM j_1igrxref INTO wa_j_1igrxref
WHERE mblnr = wa_j_1ipart1-mblnr AND
zeile = wa_j_1ipart1-zeile.
IF sy-subrc EQ 0.
ws_ecs = ws_ecs + wa_j_1igrxref-exbed + wa_j_1igrxref-ecs.
ENDIF.
ENDSELECT.
ENDON.
ENDSELECT.
ENDON.
MOVE ws_ecs TO int_rseg-ecs.
MODIFY int_rseg TRANSPORTING ecs.
CLEAR: ws_ecs.
ENDLOOP.
LOOP AT int_outtab.
ON CHANGE OF int_outtab-ebeln.
LOOP AT int_rseg WHERE ebeln = int_outtab-ebeln.
ws_ecs = ws_ecs + int_rseg-ecs.
ENDLOOP.
ENDON.
MOVE ws_ecs TO int_outtab-ecs.
MODIFY int_outtab TRANSPORTING ecs.
CLEAR ws_ecs.
ENDLOOP.
******unplanned costs and actual_invoice_total
SORT int_rseg BY belnr.
LOOP AT int_rseg .
ON CHANGE OF int_rseg-belnr.
SELECT belnr beznk rmwwr tcode FROM rbkp INTO int_rbkp
WHERE belnr = int_rseg-belnr .
APPEND int_rbkp.
ENDSELECT.
ENDON.
ENDLOOP.
SORT int_rseg BY belnr.
SORT int_rbkp BY belnr.
LOOP AT int_rseg.
ON CHANGE OF int_rseg-ebeln OR int_rseg-belnr.
LOOP AT int_rbkp WHERE belnr = int_rseg-belnr.
IF int_rbkp-tcode = 'MR8M'.
int_rbkp-rmwwr = int_rbkp-rmwwr * ( - 1 ).
int_rbkp-beznk = int_rbkp-beznk * ( - 1 ).
ENDIF.
ws_beznk = ws_beznk + int_rbkp-beznk.
ws_rmwwr = ws_rmwwr + int_rbkp-rmwwr.
ENDLOOP.
ENDON.
MOVE ws_beznk TO int_rseg-beznk.
MOVE ws_rmwwr TO int_rseg-rmwwr.
MODIFY int_rseg.
CLEAR: ws_rmwwr, ws_beznk.
ENDLOOP.
LOOP AT int_outtab.
ON CHANGE OF int_outtab-ebeln.
LOOP AT int_rseg WHERE ebeln = int_rseg-ebeln.
ws_beznk = ws_beznk + int_rseg-beznk.
ws_rmwwr = ws_rmwwr + int_rseg-rmwwr.
ENDLOOP.
ENDON.
MOVE ws_beznk TO int_outtab-beznk.
MOVE ws_rmwwr TO int_outtab-rmwwr.
MODIFY int_outtab.
CLEAR: ws_rmwwr, ws_beznk.
ENDLOOP.
*freight of actual payment
sort int_rseg by belnr ebelp.
LOOP AT int_rseg.
ON CHANGE OF int_rseg-belnr or int_rseg-ebelp..
SELECT * FROM ekbz INTO CORRESPONDING FIELDS OF int_ekbz
WHERE ebeln = int_rseg-ebeln AND
belnr = int_rseg-belnr AND
ebelp = int_rseg-ebelp AND
vgabe = '2' .
IF int_ekbz-shkzg = 'H'.
int_ekbz-wrbtr = int_ekbz-wrbtr * ( - 1 ).
ENDIF.
int_rseg-wrbtr_f = int_rseg-wrbtr_f + int_ekbz-wrbtr.
ENDSELECT.
ENDON.
MODIFY int_rseg.
CLEAR int_rseg-wrbtr_f.
ENDLOOP.
sort int_rseg by ebelp.
LOOP AT int_outtab.
ON CHANGE OF int_outtab-ebeln OR int_outtab-ebelp.
LOOP AT int_rseg WHERE ebeln = int_outtab-ebeln AND
ebelp = int_outtab-ebelp.
ws_wrbtr_f = ws_wrbtr_f + int_rseg-wrbtr_f.
ENDLOOP.
ENDON.
MOVE ws_wrbtr_f TO int_outtab-wrbtr_f.
MODIFY int_outtab TRANSPORTING wrbtr_f.
CLEAR ws_wrbtr_f.
ENDLOOP.
**tax and gross amt of actual payment
LOOP AT int_outtab.
ON CHANGE OF int_outtab-ebelp OR int_outtab-ebeln.
" or int_outtab-belnr.
SELECT ebelp refwr wrbtr shkzg FROM ekbe INTO int_ekbe
WHERE ebelp = int_outtab-ebelp AND
ebeln = int_outtab-ebeln AND
vgabe = '2' .
IF int_ekbe-shkzg = 'H'.
int_ekbe-refwr = int_ekbe-refwr * ( - 1 ).
int_ekbe-wrbtr = int_ekbe-wrbtr * ( - 1 ).
ENDIF.
int_outtab-refwr_g = int_outtab-refwr_g + int_ekbe-refwr.
int_outtab-tax = int_outtab-tax + ( int_ekbe-wrbtr -
int_ekbe-refwr ).
ENDSELECT.
ENDON.
MODIFY int_outtab.
CLEAR int_outtab-refwr_g.
CLEAR int_outtab-tax.
ENDLOOP.
****total
LOOP AT int_outtab.
ON CHANGE OF int_outtab-ebelp OR int_outtab-ebeln.
int_outtab-actual_total = int_outtab-actual_total
+ int_outtab-ecs
+ int_outtab-wrbtr_f
+ int_outtab-tax
+ int_outtab-beznk
+ int_outtab-refwr_g.
int_total-actual_total = int_outtab-actual_total.
int_total-ebelp = int_outtab-ebelp.
int_total-ebeln = int_outtab-ebeln.
APPEND int_total.
ENDON.
ENDLOOP.
***************************************
*end of calculation for actaul payment
****************************************
*************Modification in internal table*****
LOOP AT int_outtab.
IF int_outtab-ebelp = ws_ebelp AND
int_outtab-ebeln = ws_ebeln.
int_outtab-netwr = 0.
MODIFY int_outtab.
ENDIF.
ws_ebelp = int_outtab-ebelp.
ws_ebeln = int_outtab-ebeln.
ENDLOOP.
LOOP AT int_outtab.
IF int_outtab-netwr IS INITIAL.
DELETE int_outtab INDEX sy-tabix.
ENDIF.
ENDLOOP.
LOOP AT int_outtab.
ON CHANGE OF int_outtab-ebeln.
LOOP AT int_total WHERE ebeln = int_outtab-ebeln.
int_outtab-actual_total = int_outtab-actual_total
+ int_total-actual_total.
ENDLOOP.
ENDON.
MODIFY int_outtab.
ENDLOOP.
LOOP AT int_outtab.
SELECT SINGLE * FROM lfm1 INTO CORRESPONDING FIELDS OF int_outtab
WHERE lifnr = int_outtab-lifnr.
MODIFY int_outtab.
ENDLOOP.
********** end of modification*********
ENDFORM. "get_data
*&---------------------------------------------------------------------*
*& Form field_catalog
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM field_catalog.
***vender no
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'LIFNR'.
int_cat-reptext_ddic = 'Vender No'.
APPEND int_cat TO ws_cat.
*vender name
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'NAME1'.
int_cat-reptext_ddic = 'Vender Name'.
APPEND int_cat TO ws_cat.
** PO No
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'EBELN'.
int_cat-reptext_ddic = 'PO No'.
APPEND int_cat TO ws_cat.
** Mat No
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'MATNR'.
int_cat-reptext_ddic = 'Mat No'.
APPEND int_cat TO ws_cat.
** Matdescription
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'TXZ01'.
int_cat-reptext_ddic = 'Mat description'.
APPEND int_cat TO ws_cat.
**WBS/Cost center
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'KOSTL'.
int_cat-reptext_ddic = 'WBS/Cost center'.
APPEND int_cat TO ws_cat.
**Currency
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'WAERS'.
int_cat-reptext_ddic = 'Currency'.
APPEND int_cat TO ws_cat.
**Basic
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'NETWR'.
int_cat-reptext_ddic = 'Basic'.
APPEND int_cat TO ws_cat.
**Excise
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'KBETR'.
int_cat-reptext_ddic = 'Excise'.
APPEND int_cat TO ws_cat.
**Insurance
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'KWERT_I'.
int_cat-reptext_ddic = 'Insurance'.
APPEND int_cat TO ws_cat.
**Frieght
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'KWERT_F'.
int_cat-reptext_ddic = 'Frieght'.
APPEND int_cat TO ws_cat.
**VAT/ST
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'KWERT_T'.
int_cat-reptext_ddic = 'VAT/ST'.
APPEND int_cat TO ws_cat.
**Other
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'KWERT_O'.
int_cat-reptext_ddic = 'Others'.
APPEND int_cat TO ws_cat.
**Total
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'KWERT_TOTAL'.
int_cat-reptext_ddic = 'Total'.
APPEND int_cat TO ws_cat.
***Excise
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'ECS'.
int_cat-reptext_ddic = 'Excise'.
APPEND int_cat TO ws_cat.
*****Freight
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'WRBTR_F'.
int_cat-reptext_ddic = 'Freight'.
APPEND int_cat TO ws_cat.
*****Tax
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'TAX'.
int_cat-reptext_ddic = 'Tax'.
APPEND int_cat TO ws_cat.
***Unplanned Del Cost
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'BEZNK'.
int_cat-reptext_ddic = 'Unplanned Del Cost'.
APPEND int_cat TO ws_cat.
****Gross
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'REFWR_G'.
int_cat-reptext_ddic = 'Gross'.
APPEND int_cat TO ws_cat.
**Total
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'ACTUAL_TOTAL'.
int_cat-reptext_ddic = 'Total'.
APPEND int_cat TO ws_cat.
**Actual Invoice Total
int_cat-tabname = 'INT_OUTTAB'.
int_cat-fieldname = 'RMWWR'.
int_cat-reptext_ddic = 'Actual Invoice Total'.
APPEND int_cat TO ws_cat.
ENDFORM. "field_catalog
*&---------------------------------------------------------------------*
*& Form display_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM display_data.
CALL FUNCTION 'REUSE_ALV_LIST_DISPLAY'
EXPORTING
i_callback_program = 'Z_MM_TEST1'
it_fieldcat = ws_cat[]
TABLES
t_outtab = int_outtab
EXCEPTIONS
program_error = 1
OTHERS = 2.
ENDFORM. "display_data
Regards
Pavan
10-15-2007 9:20 AM
Hi Pavan,
Thanks for the code which you posted. To be frank, i am not an ABAPer, so it is very hard for me to understand all the steps in the code. I will restate what i am actually trying to do, maybe then you can give me a simple solution to overcome the problem.
I need a report, which gives the matl no., descpn & few more details of the material which is coming from MARC, also the MAP value from MBEW. For each material i need to know if there is a fixed source & if there is, i want to extract the vendor no., descpn & info record price.
I am using SQVI as i am a novice in ABAP. I have joined MARC-MBEW-MAKT-MARA & able to extract most of the data. But the problem i am facing is when i am trying to join EORD, EINA, EINE & LFA1 to the above tables. So i just wanted a few lines which i could add to my query, where by it checks for the material if a fixed source exists & if there is, then extract info record price & vendor descpn. I hope i was able to put across my problem clearly to you.
So if there is any simple code which i can use for this, kindly let me know.
Vivek
10-15-2007 9:36 AM
Hi Vivek
If you are not an abap er why you are taking risk. You just give the required fields and everything to your abaper he will perform that with his coding. It will be a little bit difficult by using this quereies. Ok you can perform the task by joining but you cant get the required output
Ok i'll try from my end. To give u output as simple as possible
Regards
Pavan
10-15-2007 10:34 AM
Hi Pavan,
Thanks for the inputs. The reason am trying to see if i can manage this with a simple table join is because i need this information ASAP & the ABAPer at the moment is having a lot of reports to prepare. But if my problem cannot be resolved with a simple join & a few lines of code, then i will have to wait for my ABAPer, no way out.
Let me know if you can manage something like this.... the output already has the material no. & the other details... so lemme know if any code can be added just to read this material no, read from EORD table if fixed indicator is ticked, if yes display the vendor no, descpn (LFA1) & price from info record (EINE). If this part can be managed, then i can get the information i urgently need.
Vivek
10-15-2007 10:00 AM
hi, copy and paste this code in se38 and press f8 and see the o/p.
Report ztest.
data: begin of itab occurs 0,
matnr like marc- matnr "material number
werks like marc-werks, "plant
maktx like makt-maktx, "description
vmver like mbew-vmver , "moving average price
end of itab.
final internal table
data: begin of t_vendor occurs 0,
<b>include itab.</b> "including data from the above intenal table
data:
flifn like eord-flifn, "fixed vendor indicator
name1 like lfa1-name1, "name
netpr like eine-netpr, "net price
end of t_vendor.
*********selecting data from marc, makt and mbew into itab
select awerks amatnr bmaktx cvmver from marc as a inner join makt as b inner join mbew as c on amatnr = bmatnr = c~matnr into corresponding fields of table itab.
*******moving data from itab to the final intenal table also selecting the remaining three fields flifn,name1 and netpr
loop at itab.
t_vendor-matnr = itab-matnr.
t_vendor-werks = itab-werks.
t_vendor-maktx = itab-maktx.
t_vendor-vmver = itab-vmver.
select single flifn from eord into t_vendor-flifn where werks = itab-werks.
select single name1 from lfa1 into t_vendor-name where werks = itab-werks.
select single netpr from eine into t_vendor-netpr where werks = itab-werks.
append t_vendor.
endloop.
***********display data
loop at t_vendor.
write 😕 t_vendor-matnr,t_vendor-werks, t_vendor-maktx,t_vendor-vmver,t_vendor-flifn,t_vendor-name1,t_vendor-netpr.
endloop.
10-15-2007 10:37 AM
Hi Oruga,
I will check the code which you have posted in SE38 & let you know if it solves my problem. Thanks for your help.
Vivek
10-15-2007 11:08 AM
Hi Vivek
Do one thing yar. Do u know anything about Creating Views??
If not try with that it may solve your problem
The main aim of a view is to join two or more tables. It will be just like a table output.
For the procedure try like this
Goto Tcode SE11
Click on the Radio button <i><b>View</b></i> Give your view name starting with Z
Then press F5 or click on the create button
It will ask for four options
1)DataBase view
2)Projection View
3)Maintanence View
4)Help view
Database Views Locate the document in its SAP Library structure
Data about an application object is often distributed on several database tables. A database view provides an application-specific view on such distributed data.
Database views are defined in the ABAP Dictionary. A database view is automatically created in the underlying database when it is activated.
Application programs can access the data of a database view using the database interface. You can access the data in ABAP programs with both OPEN SQL and NATIVE SQL. However, the data is actually selected in the database. Since the join operation is executed in the database in this case, you can minimize the number of database accesses in this way. Database views implement an inner join
f the database view only contains a single table, the maintenance status can be used to determine if data records can also be inserted with the view. If the database view contains more than one table, you can only read the data.
Database views should be created if want to select logically connected data from different tables simultaneously. Selection with a database view is generally faster than access to individual tables. When selecting with views, you should also ensure that there are suitable indexes on the tables contained in the view.
Since a database view is implemented in the database, a database view may only contain transparent tables.
The technical settings of a database view control whether the view data should be buffered.
<b>Check this link for clear information on different types of views</b>
http://help.sap.com/saphelp_nw04/helpdata/en/cf/21ec5d446011d189700000e8322d00/content.htm
For your present instance select <i><b>Database view</b></i>
1. Enter an explanatory short text in the field Short text.
You can for example find the view at a later time using this short text.
2. Define the tables to be included in the view in the Tables field of the Tables/Join conditions tab page.
Keep in mind that you can only include transparent tables in a database view.
3. Link the tables with
join conditions.
If there are suitable foreign keys between the tables, you should copy the join conditions from these foreign keys
Place the cursor on a table name and choose Relationships. All foreign keys to other tables defined for this table are displayed. Select the foreign keys and choose <i>Copy</i> . The join condition is now derived from the definitions in the foreign key.
If you only want to see the foreign key relationship existing between two tables, you must first select these two tables (click on the first column of the input area Tables) and then choose Relationships.
4. On the View fields tab page, select the fields that you want to copy to the view.
Choose Table fields. All the tables contained in the view are displayed in a dialog box. Select a table. All the fields contained in this table are displayed. You can copy fields by selecting them in the first column and choosing <i>Copy</i>
You can also include an entire table in the view (see Includes in Database Views).
5. On the Selection conditions tab page, you can (optionally) formulate restrictions for the data records to be displayed with the view (see
Maintaining Selection Conditions for Views).
The selection conditions define the data records that can be selected with the view.
6. With Goto --> Technical settings, you can (optionally) maintain the technical settings of the database view.
You can define whether and how the database view should be buffered here. Proceed as for the technical settings of a table (see Structure link Maintaining Technical Settings). Note that only the settings for buffering can be maintained for database views.
7. On the Maintenance status tab page, select the
maintenance status of the database view.
If the view contains more than one table, the maintenance status read only cannot be altered.
8. Save your entries. You are asked to assign the view a development class.
You can change this development class later with Goto --> Object directory entry.
9. Choose <i>Activate</i>
Hope it may solve your problem
Regards
Pavan
Message was edited by:
Pavan praveen
10-15-2007 12:42 PM
Hi Pavan,
Thanks a lot for all your support & guidance. I will try creating a view as you have mentioned, if i succeed with it will be able to extract the data i need, if not will have to wait for my ABAPer. Once again a heartfelt thanks to you for having shared your knowledge & for all the effort you have put-in to explain things to me. Keep the great work going. Will keep you updated about my progress.
Also thanks to others who have put in their effort & time to help me.
Thanks & Regards,
Vivek