topic Re: Eliminate multiple line items in Application Development and Automation Discussions

Eliminate multiple line items

Former Member — Fri, 06 Mar 2009 20:50:13 GMT

Hi , I have a scenerio to extarct the billing plan items in my report. When I run the report it gives me the required output but with multiple line items . I am not sure if my sleection is bring in data from both billing plan header as well as billing plan item data . I just need to bring in the data from the item level which will resolve the issue of multiple line items showing up in the report, my selection is as follows , please comment if I am selecting form both the header as well as item level,

  IF  p_rental = 'X' .

    SELECT a~vbeln a~ktext a~erdat a~ernam a~audat a~vbtyp a~auart a~vkorg
     a~vtweg a~spart a~vkbur a~guebg a~gueen a~kunnr a~kvgr4
     b~posnr b~matnr b~charg b~matkl b~arktx b~pstyv b~zmeng
     b~zieme b~kdmat b~werks b~lgort b~vstel b~netpr b~kpein
     b~vkaus b~aufnr b~kmein b~shkzg b~oid_extbol b~oid_miscdl b~netwr
     b~oidrc b~oid_ship AS kunwe b~zzwprofid c~datbi c~datab b~route
     e~fkdat e~nfdat e~fakwr e~fksaf e~afdat
     f~fpart f~bedat f~endat f~horiz g~bezei
       INTO CORRESPONDING FIELDS OF TABLE gt_sel
       FROM vbak AS a
           INNER JOIN vbap AS b ON a~vbeln = b~vbeln
           INNER JOIN vbkd AS d ON a~vbeln = d~vbeln
           INNER JOIN fplt AS e ON d~fplnr = e~fplnr
           INNER JOIN fpla AS f ON e~fplnr = f~fplnr
           LEFT OUTER JOIN tvlvt AS g ON b~vkaus = g~abrvw
                                        AND g~spras = 'E'
           LEFT OUTER JOIN zsdsched AS c ON b~vbeln = c~vbeln
                                        AND b~posnr = c~posnr
         WHERE a~vbeln IN s_vbeln
         AND a~ktext IN s_ktext
         AND a~erdat IN s_erdat
         AND a~vbtyp = gc_g
         AND a~ernam IN s_ernam
         AND a~vkorg IN s_vkorg
         AND a~vtweg IN s_vtweg
         AND a~spart IN s_spart
         AND a~vkbur IN s_vkbur
         AND a~guebg IN s_guebg
         AND a~gueen IN s_gueen
         AND a~auart IN s_auart
         AND a~kunnr IN s_kunnr
         AND b~oid_ship IN s_kunwe
         AND b~werks IN s_werks
         AND b~lgort IN s_lgort
         AND b~vstel IN s_vstel
         AND b~route in s_route
         AND b~matnr IN s_matnr
         AND e~afdat IN s_afdat
         AND e~fksaf IN s_fksaf
         AND b~pstyv in s_pstyv.
    SORT gt_sel.
    IF NOT gt_sel[] IS INITIAL.
**   Get Bill-to Party's for Item Lines
      SELECT vbeln posnr parvw kunnr INTO CORRESPONDING FIELDS OF TABLE gt_vbpa
          FROM vbpa
          FOR ALL ENTRIES IN gt_sel
          WHERE vbeln = gt_sel-vbeln
            AND parvw = gc_re.
    ENDIF.
*   Loop through captured data for additional information
    LOOP AT gt_sel INTO gs_report.

*   Find the bill-to party
      READ TABLE gt_vbpa INTO gs_vbpa WITH KEY vbeln = gs_report-vbeln
                                               posnr = gs_report-posnr
                                               parvw = gc_re.
      IF sy-subrc <> 0.
        READ TABLE gt_vbpa INTO gs_vbpa WITH KEY vbeln = gs_report-vbeln
                                                 parvw = gc_re.
        CHECK sy-subrc = 0 AND gs_vbpa-kunnr IN s_kunre.
      ELSE.
        CHECK gs_vbpa-kunnr IN s_kunre.
      ENDIF.
      if sy-subrc = 0.
        gs_report-kunre = gs_vbpa-kunnr.
      endif.
*  Get the Customer's Name
      gs_report-kunnrt = zcl_kna1=>get_name1( itp_kunnr = gs_report-kunnr ).
      gs_report-kunwet = zcl_kna1=>get_name1( itp_kunnr = gs_report-kunwe ).
      gs_report-kunret = zcl_kna1=>get_name1( itp_kunnr = gs_report-kunre ).
      APPEND gs_report TO gt_report.
      ENDLOOP.
     DELETE ADJACENT DUPLICATES FROM  gt_report .

Thanks

Re: Eliminate multiple line items

Former Member — Fri, 06 Mar 2009 21:03:22 GMT

vbak is the header table and vbkd has the items, so it does look like you would have duplicates in the gt_sel table. I think you would want to sort the gt_sel, then do a "delete adjacent duplicates comaring...." on the gt_sel to get rid of those duplicates before you go into the rest of the program.

Winni

Re: Eliminate multiple line items

Former Member — Fri, 06 Mar 2009 21:08:53 GMT

Hi,

Try debugging the code and after the select query and sort just check the gt_sel table if it contains any duplicate entries, if it is so, then there is some problem in inner join and left outer join and left outer join takes all the entries from left hand tables and only selected entries from right hand table so if the right hand table contains 3 to 4 entries then the same records from the left hand table is repeated for 3 to 4 times due to which you feel that the records are repeated.

just check with the query and instead of using joins which is very costly statement try separating the query for each table and then using different internal tables you can collaborate the data into one single and avoid repetation.

Regards,

Ravi

Re: Eliminate multiple line items

Former Member — Fri, 06 Mar 2009 21:29:16 GMT

Hi !

Thats right I have added extra joins , but I wanted to connect either vbak or vbap tables to fplt or fpla and as I wasnt getting any connection so I put in that table . Ho will I sperate into different intenal tables the and into what , if some help can be given so that I can sort out and get only the required entries as per my selection.

Thanks

Re: Eliminate multiple line items

Former Member — Fri, 06 Mar 2009 21:34:15 GMT

You are asking the forum a question which you are obviously in a better position to answer than we are. You have a JOIN on a Z table, so there is no way that anyone could compile or test your code. The best we can do is some desk checking. You on the other hand can compile and debug yourself.

So why not give that a try?

Rob

Re: Eliminate multiple line items

Former Member — Fri, 06 Mar 2009 22:16:13 GMT

Yes I did that , I debiugged and see multiple entries but need to know a way out of this so that only entries from item level comes in.

Thanks

Re: Eliminate multiple line items

Former Member — Fri, 06 Mar 2009 22:22:05 GMT

Yes - I see that, but you are still in the best position to answer your question.

Rob

Re: Eliminate multiple line items

Former Member — Fri, 06 Mar 2009 22:36:58 GMT

Hi,

Rob Burank is right,

But if you can try to split the select query by removing all the joins I think then it becomes easier for you to combine the result and then proceed further...

Regards,

Siddarth

Re: Eliminate multiple line items

Former Member — Fri, 06 Mar 2009 22:42:11 GMT

Siddharth is correct. If you have a big whack of code that is causing problems and defies analysis, break it down into manageable pieces that can be debugged more easily.

Rob

Re: Eliminate multiple line items

Former Member — Fri, 06 Mar 2009 23:37:55 GMT

Thats right, I am trying to break into two different selecets statemenst , but I need to connect them togeteher to be able to show on one report .

Re: Eliminate multiple line items

Former Member — Fri, 06 Mar 2009 23:45:44 GMT

Hi,

take two internal tables according to select queries and keep one field as common in both the table which should logically bind the data...

then after the select query is executed merge the data of the internal tables in the third internal table which has all the fields of the first and second internal table...

Regards,

Siddarth