Report (Urgent)

[Former Member]

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

[Clemenss]

Please use a meaningful subject if you expect people to help you.

Thank you.

Regards, Clemens

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[hymavathi_oruganti]

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 a_{werks a}matnr b_{maktx c}vmver from marc as a inner join makt as b inner join mbew as c on a_{matnr = b}matnr = 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.

[Former Member]

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

[Former Member]

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

[Former Member]

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