05-07-2013 7:32 AM
i am creating a report in that i have to fetch gross and net weight of all the items of a delivery order..
for e.g if a DO has single item then there is no issue.but if have a DO number say 10 and if in this DO, 10 items associated with it
Than in report i have to show total weight of all the items of this DO.
i am applying
sELECT vbeln ntgew brgew FROM lips INTO TABLE it_lips
FOR ALL ENTRIES IN it_likp
WHERE vbeln = it_likp-vbeln.
by this i am getting weight of only first item...
how can i get weight of all the items??
Please suggest
05-07-2013 7:58 AM
SELECT vbeln
sum( ntgew )
sum( brgew )
FROM lips INTO TABLE it_lips
* FOR ALL ENTRIES IN it_likp
* WHERE vbeln = it_likp-vbeln
group by vbeln ntgew brgew.
this will work if u use join and will not work for all entries.
if u want to use for all entries then loop at internal table and do subtotal using COLLECT or
do sum internally
05-07-2013 8:01 AM
Hi Hemant,
Please change your select query as:
sELECT vbeln POSNR ntgew brgew FROM lips INTO TABLE it_lips
FOR ALL ENTRIES IN it_likp
WHERE vbeln = it_likp-vbeln.
I hope above should solve your problem.
Regards,
Deepak.
05-07-2013 8:07 AM
Hi,
keep the select statement as it is. But u need to collect the weights in loop on line item.
Try it.
Thanks-
Makarand
05-07-2013 8:31 AM
Hi makarand would you please write ....what to write in loop statement.
thanks
05-07-2013 10:53 AM
Hi,
Have you appended the entry? Have you added the field in fieldcatalog? I know this is very basic thing. But this is what i understood from your reply. Do it if not done yet.
Thanks-
Makarand
05-07-2013 8:48 AM
Hi,
You can try like below code...
DATA : LV_NTGEW TYPE NTGEW_15,
LV_BRGEW TYPE BRGEW_15.
LOOP AT it_lips INTO wa_lips.
LV_NTGEW = wa_lips-ntgew + LV_NTGEW.
LV_BRGEW = wa_lips-brgew + LV_BRGEW.
*LV_NTGEW - Use Total Net weight
*LV_NTGEW - Use Total Gross weight
AT END OF VBELN.
CLEAR : LV_NTGEW,LV_BRGEW.
ENDAT.
ENDLOOP.
Regards,
Shaiksha Vali.
05-07-2013 9:40 AM
Dear Shaiksha
i applied ur code and in debugging i am getting gross and net wgt of all the items
but not in alv layout..
please suggest
05-07-2013 9:52 AM
Hi,
How does it come in the layout?
You are adding up and showing it as single record or you are showing all items seperately without adding?
Regards,
Swarna
05-07-2013 10:05 AM
Hi
i am showing as a single record...but not getting...
thanks
Hemant
05-07-2013 10:12 AM
Ok. What is not getting? The record itself is not coming or summation is not coming correctly?
Complete the logic
LOOP AT it_lips INTO wa_lips.
LV_NTGEW = wa_lips-ntgew + LV_NTGEW.
LV_BRGEW = wa_lips-brgew + LV_BRGEW.
*LV_NTGEW - Use Total Net weight
*LV_NTGEW - Use Total Gross weight
AT END OF VBELN.
MOVE WA_LIPS TO WA_LIPS_FINAL.
WA_LIPS_FINAL-NTGEW = LV_NTGEW.
WA_LIPS_FINAL-BRGEW = LV_BRGEW.
CLEAR : LV_NTGEW,LV_BRGEW.
APPEND WA_LIPS_FINAL TO IT_LIPS_FINAL.
ENDAT.
ENDLOOP.
Now populate it_lips_final.
05-07-2013 10:39 AM
Hi Hemant.
Have a look in the below code.
LOOP AT it_lips INTO wa_lips.
LV_NTGEW = wa_lips-ntgew + LV_NTGEW.
LV_BRGEW = wa_lips-brgew + LV_BRGEW.
*LV_NTGEW - Use Total Net weight
*LV_NTGEW - Use Total Gross weight
AT END OF VBELN.
READ TABLE IT_LIPS_FINAL INTO WA_LIPS_FINAL WITH KEY <REQUIRED FIELDS>.
IF SY-SURC = 0.
MOVE WA_LIPS TO WA_LIPS_FINAL.
WA_LIPS_FINAL-NTGEW = LV_NTGEW.
WA_LIPS_FINAL-BRGEW = LV_BRGEW.
APPEND WA_LIPS_FINAL TO IT_LIPS_FINAL.
ENDIF.
CLEAR : LV_NTGEW,LV_BRGEW.
ENDAT.
ENDLOOP.
Regards,
Shaiskha Vali.
05-07-2013 10:52 AM
i tried........
in debugging it is showing weight of all items but in alv
it is showing weight of only first item.
it is not summing all weight of all the items..
thanks
05-07-2013 10:52 AM
i tried........
in debugging it is showing weight of all items but in alv
it is showing weight of only first item.
it is not summing all weight of all the items..
thanks
05-07-2013 11:47 AM
hi shaiksha
i see in debugging it is all the weight is getting added but in alv layout
it is showing weight of only first item.
thanks
05-07-2013 1:17 PM
05-08-2013 5:03 AM
REPORT z_weight_comp_report.
TABLES : leci_event_data, z23_leci_del, z23_leci_chkin, likp, lips.
TYPES : BEGIN OF ty_data,
vehicle_reg_numb TYPE leci_tra_dyn-vehicle_reg_numb,
trailer_reg_numb TYPE leci_tra_dyn-trailer_reg_numb,
* PASS_DATE TYPE LECI_TRA_DYN-PASS_DATE,
END OF ty_data.
TYPES : BEGIN OF ty_del,
* guid_event TYPE z23_leci_del-guid_event, "Key for Check-in/Check-out Process That Is Unique Worldwide
vbeln TYPE z23_leci_del-vbeln, " doc no
pass_numb TYPE z23_leci_del-pass_numb, "seq numb
name1 TYPE z23_leci_del-name1, "cust name
kunnr TYPE z23_leci_del-kunnr, " cust code
* erdat TYPE Z23_LECI_DEL-erdat,
END OF ty_del.
TYPES : BEGIN OF ty_wgt,
pass_numb TYPE z23_leci_chkin-pass_numb,
laden TYPE z23_leci_chkin-laden,
unladen TYPE z23_leci_chkin-unladen,
* chk_date TYPe Z23_LECI_CHKIN-chk_date,
END OF ty_wgt.
TYPES : BEGIN OF ty_likp,
vbeln TYPE likp-vbeln,
ntgew TYPE likp-ntgew,
btgew TYPE likp-btgew,
END OF ty_likp.
***********************************
TYPES : BEGIN OF ty_lips,
vbeln TYPE lips-vbeln,
ntgew TYPE lips-ntgew,
brgew TYPE lips-brgew,
matnr TYPE lips-matnr,
posnr TYPE lips-posnr,
END OF ty_lips.
*************************************
TYPES : BEGIN OF ty_final,
vbeln TYPE z23_leci_del-vbeln,
name1 TYPE z23_leci_del-name1,
pass_numb TYPE z23_leci_del-pass_numb,
guid_event TYPE z23_leci_del-guid_event,
guid_event_data TYPE leci_event_data-guid_event_data,
kunnr TYPE z23_leci_del-kunnr,
laden TYPE z23_leci_chkin-laden,
unladen TYPE z23_leci_chkin-unladen,
*************** ntgew TYPE likp-ntgew,
btgew TYPE likp-btgew,
brgew_c TYPE lips-brgew,
vehicle_reg_numb TYPE leci_tra_dyn-vehicle_reg_numb,
trailer_reg_numb TYPE leci_tra_dyn-trailer_reg_numb,
brgew TYPE lips-brgew,
ntgew TYPE lips-ntgew,
matnr TYPE lips-matnr,
posnr TYPE lips-posnr,
END OF ty_final.
DATA : it_data TYPE STANDARD TABLE OF ty_data ,
wa_data TYPE ty_data.
DATA : it_del TYPE STANDARD TABLE OF ty_del,
wa_del TYPE ty_del.
DATA : it_wgt TYPE STANDARD TABLE OF ty_wgt,
wa_wgt TYPE ty_wgt.
DATA : it_likp TYPE STANDARD TABLE OF ty_likp,
wa_likp TYPE ty_likp.
DATA : it_lips TYPE STANDARD TABLE OF ty_lips,
wa_lips TYPE ty_lips.
DATA : it_final TYPE STANDARD TABLE OF ty_final ,
wa_final TYPE ty_final.
DATA : diff(20) TYPE c.
DATA : z_pass TYPE z23_leci_del-pass_numb.
DATA : l_laden TYPE z23_leci_chkin-laden.
DATA : un_laden TYPE z23_leci_chkin-unladen.
DATA: p_id LIKE sy-repid,
it_fcat TYPE slis_t_fieldcat_alv,
wa_fcat TYPE slis_fieldcat_alv,
it_sort TYPE slis_t_sortinfo_alv,
wa_sort TYPE slis_sortinfo_alv,
it_layout TYPE slis_layout_alv.
*DATA : var1 TYPE likp-btgew,
* var2 TYPE likp-btgew.
*
*DATA: BEGIN OF it_vat OCCURS 0,
** var1 TYPE likp-btgew,
** var2 TYPE likp-btgew,
* var3 TYPE likp-btgew,
* var4 TYPE likp-btgew,
*
* END OF it_vat.
DATA: var4 TYPE likp-btgew,
var3 TYPE likp-btgew,
var5 TYPE likp-btgew.
DATA : V_NTGEW TYPE LIPS-NTGEW,
V_BRGEW TYPE LIPS-BRGEW,
v_vbeln TYPE z23_leci_del-vbeln.
SELECTION-SCREEN BEGIN OF BLOCK bl WITH FRAME TITLE text-001.
SELECT-OPTIONS: s_vbeln FOR z23_leci_del-vbeln,
s_pass FOR z23_leci_del-pass_numb OBLIGATORY.
* s_trail FOR leci_event_data-trailer_reg_numb.
SELECTION-SCREEN END OF BLOCK bl.
INITIALIZATION.
p_id = sy-repid.
START-OF-SELECTION.
PERFORM get_data.
PERFORM buidl_fcat.
PERFORM display_data.
PERFORM sub_sort CHANGING it_sort.
*&---------------------------------------------------------------------*
*& Form GET_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM get_data .
SELECT vbeln name1 kunnr pass_numb FROM z23_leci_del INTO CORRESPONDING FIELDS OF TABLE it_del
WHERE vbeln IN s_vbeln AND pass_numb IN s_pass .
IF it_del[] IS NOT INITIAL.
SELECT pass_numb laden unladen FROM z23_leci_chkin INTO CORRESPONDING FIELDS OF TABLE it_wgt
FOR ALL ENTRIES IN it_del
WHERE pass_numb = it_del-pass_numb.
********
******** SELECT vbeln ntgew btgew FROM likp INTO CORRESPONDING FIELDS OF TABLE it_likp
******** FOR ALL ENTRIES IN it_del
******** WHERE vbeln = it_del-vbeln.
SELECT vbeln posnr ntgew brgew FROM lips INTO CORRESPONDING FIELDS OF TABLE it_lips
FOR ALL ENTRIES IN it_del
WHERE vbeln = it_del-vbeln.
ENDIF.
LOOP AT it_del INTO wa_del.
wa_final-vbeln = wa_del-vbeln.
wa_final-name1 = wa_del-name1.
wa_final-kunnr = wa_del-kunnr.
wa_final-pass_numb = wa_del-pass_numb .
READ TABLE it_wgt INTO wa_wgt WITH KEY pass_numb = wa_del-pass_numb.
wa_final-laden = wa_wgt-laden.
wa_final-unladen = wa_wgt-unladen.
********* READ TABLE it_likp INTO wa_likp WITH KEY vbeln = wa_del-vbeln.
********* wa_final-ntgew = wa_likp-ntgew.
********* wa_final-btgew = wa_likp-btgew.
READ TABLE it_lips INTO wa_lips WITH KEY vbeln = wa_del-vbeln.
wa_final-ntgew = wa_lips-ntgew.
wa_final-brgew = wa_lips-brgew.
wa_final-matnr = wa_lips-matnr.
wa_final-posnr = wa_lips-posnr.
******************************************************************************
*********************************************************************************
CALL FUNCTION 'Z_MPH_LECI_DATA_FETCH'
EXPORTING
vbeln = wa_del-vbeln
pass_numb = ' '
IMPORTING
vehicle_reg_numb = wa_final-vehicle_reg_numb
trailer_reg_numb = wa_final-trailer_reg_numb
* NAME_DRVR =
* FST_NAME_DRVR =
* VEND_NUMB = trawa-vend_no
* REPORTIN_DATE = WA_FINAL-PASS_DATE
* CHECKIN_DATE =
* CHECKOUT_DATE =
* REPORTIN_TIME =
* CHECKIN_TIME =
* CHECKOUT_TIME =
* UNLADEN_WT =
* LADEN_WT =
* TRANSPORTER_NOTE_TEXT = trawa-tra_name
EXCEPTIONS
no_data_found = 1
OTHERS = 2.
APPEND wa_final TO it_final.
CLEAR : wa_del, wa_data, wa_final , wa_wgt, wa_likp, wa_lips.
ENDLOOP.
******* LOOP AT it_del INTO wa_del.
******* LOOP AT it_final INTO wa_final WHERE pass_numb = wa_del-pass_numb.
*******
******* IF wa_final-btgew IS NOT INITIAL.
******* wa_final-brgew = wa_final-btgew.
******* MOVE wa_final-brgew TO var3.
******* ENDIF.
*******
******* IF wa_final-btgew IS NOT INITIAL .
******* wa_final-brgew = var3 + var4 .
******* MOVE wa_final-brgew TO var4.
*******
******* IF wa_final-brgew IS NOT INITIAL.
******* wa_final-brgew_c = ( wa_final-laden - var4 ).
******* ENDIF.
*******
******* CLEAR : var3, wa_final-btgew.
******* ENDIF.
******* MODIFY it_final FROM wa_final TRANSPORTING brgew brgew_c.
*******
******* ENDLOOP.
******* CLEAR: wa_final, var4, wa_final-brgew_c .
*******
******* ENDLOOP.
sort it_lips by vbeln.
BREAK edp21.
LOOP AT it_lips INTO wa_lips.
V_NTGEW = wa_lips-ntgew + V_NTGEW.
V_BRGEW = wa_lips-brgew + V_BRGEW.
AT END OF VBELN.
wa_final-ntgew = v_ntgew.
wa_final-brgew = v_brgew.
APPEND wa_final to it_final.
*endif.
CLEAR : V_NTGEW, V_BRGEW, wa_final.
ENDAT.
ENDLOOP.
ENDFORM. " GET_DATA
*&---------------------------------------------------------------------*
*& Form BUIDL_FCAT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM buidl_fcat .
wa_fcat-fieldname = 'VBELN'.
wa_fcat-seltext_m = 'Del Numb'.
wa_fcat-col_pos = 0.
APPEND wa_fcat TO it_fcat.
CLEAR wa_fcat.
*********************
wa_fcat-fieldname = 'MATNR'.
wa_fcat-seltext_m = 'Mat Numb'.
wa_fcat-col_pos = 1.
APPEND wa_fcat TO it_fcat.
CLEAR wa_fcat.
**************************
wa_fcat-fieldname = 'NAME1'.
wa_fcat-seltext_m = 'Cust Name'.
wa_fcat-col_pos = 2.
APPEND wa_fcat TO it_fcat.
CLEAR wa_fcat.
wa_fcat-fieldname = 'KUNNR'.
wa_fcat-seltext_m = 'Cust Code'.
wa_fcat-col_pos = 3.
APPEND wa_fcat TO it_fcat.
CLEAR wa_fcat.
wa_fcat-fieldname = 'VEHICLE_REG_NUMB'.
wa_fcat-seltext_m = 'Vehcle No'.
wa_fcat-col_pos = 4.
APPEND wa_fcat TO it_fcat.
CLEAR wa_fcat.
wa_fcat-fieldname = 'TRAILER_REG_NUMB'.
wa_fcat-seltext_m = 'LR No'.
wa_fcat-col_pos = 5.
APPEND wa_fcat TO it_fcat.
CLEAR wa_fcat.
wa_fcat-fieldname = 'PASS_NUMB'.
wa_fcat-seltext_m = 'Seq Numb'.
wa_fcat-col_pos = 6.
APPEND wa_fcat TO it_fcat.
CLEAR wa_fcat.
wa_fcat-fieldname = 'UNLADEN'.
wa_fcat-seltext_m = 'Unladen Wt'.
wa_fcat-col_pos = 7.
APPEND wa_fcat TO it_fcat.
CLEAR wa_fcat.
wa_fcat-fieldname = 'LADEN'.
wa_fcat-seltext_m = 'Laden Wt'.
wa_fcat-col_pos = 8.
APPEND wa_fcat TO it_fcat.
CLEAR wa_fcat.
wa_fcat-fieldname = 'NTGEW'.
wa_fcat-seltext_m = 'Net Wt'.
wa_fcat-col_pos = 9.
APPEND wa_fcat TO it_fcat.
CLEAR wa_fcat.
wa_fcat-fieldname = 'BRGEW'.
wa_fcat-seltext_m = 'Gross Wt'.
wa_fcat-col_pos = 10.
* wa_fcat-do_sum = 'X'.
wa_fcat-inttype = 'P'.
APPEND wa_fcat TO it_fcat.
CLEAR wa_fcat.
wa_fcat-fieldname = 'POSNR'.
wa_fcat-seltext_m = 'Item'.
wa_fcat-col_pos = 12.
APPEND wa_fcat TO it_fcat.
CLEAR wa_fcat.
ENDFORM. " BUIDL_FCAT
*&---------------------------------------------------------------------*
*& Form DISPLAY_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM display_data .
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
* I_INTERFACE_CHECK = ' '
* I_BYPASSING_BUFFER = ' '
* I_BUFFER_ACTIVE = ' '
i_callback_program = p_id
* I_CALLBACK_PF_STATUS_SET = ' '
* I_CALLBACK_USER_COMMAND = ' '
* I_CALLBACK_TOP_OF_PAGE = ' '
* I_CALLBACK_HTML_TOP_OF_PAGE = ' '
* I_CALLBACK_HTML_END_OF_LIST = ' '
* I_STRUCTURE_NAME = I_STRUCTURE_NAME
* I_BACKGROUND_ID = ' '
* I_GRID_TITLE = I_GRID_TITLE
* I_GRID_SETTINGS = I_GRID_SETTINGS
is_layout = it_layout
it_fieldcat = it_fcat
* IT_EXCLUDING = IT_EXCLUDING
* IT_SPECIAL_GROUPS = IT_SPECIAL_GROUPS
it_sort = it_sort
* IT_FILTER = IT_FILTER
* IS_SEL_HIDE = IS_SEL_HIDE
* I_DEFAULT = 'X'
i_save = 'A'
* IS_VARIANT = IS_VARIANT
* IT_EVENTS = IT_EVENTS
* IT_EVENT_EXIT = IT_EVENT_EXIT
* IS_PRINT = IS_PRINT
* IS_REPREP_ID = IS_REPREP_ID
* I_SCREEN_START_COLUMN = 0
* I_SCREEN_START_LINE = 0
* I_SCREEN_END_COLUMN = 0
* I_SCREEN_END_LINE = 0
* I_HTML_HEIGHT_TOP = 0
* I_HTML_HEIGHT_END = 0
* IT_ALV_GRAPHICS = IT_ALV_GRAPHICS
* IT_HYPERLINK = IT_HYPERLINK
* IT_ADD_FIELDCAT = IT_ADD_FIELDCAT
* IT_EXCEPT_QINFO = IT_EXCEPT_QINFO
* IR_SALV_FULLSCREEN_ADAPTER = IR_SALV_FULLSCREEN_ADAPTER
* IMPORTING
* E_EXIT_CAUSED_BY_CALLER = E_EXIT_CAUSED_BY_CALLER
* ES_EXIT_CAUSED_BY_USER = ES_EXIT_CAUSED_BY_USER
TABLES
t_outtab = it_final
* EXCEPTIONS
* PROGRAM_ERROR = 1
.
ENDFORM. " DISPLAY_DATA
*&---------------------------------------------------------------------*
*& Form SUB_SORT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_IT_SORT text
*----------------------------------------------------------------------*
FORM sub_sort CHANGING p_it_sort.
* wa_sort-spos = 5.
* wa_sort-fieldname = 'PASS_NUMB'. "sort depending on which field
* wa_sort-group = 'UL'.
* wa_sort-up = 'X'. "ascending sequence
* wa_sort-subtot = 'X'.
* APPEND wa_sort TO it_sort.
** CLEAR wa_sort.
* wa_sort-spos = 7.
* wa_sort-fieldname = 'LADEN'. "sort depending on which field
** wa_sort-group = 'UL'.
* wa_sort-up = 'X'. "ascending sequence
* wa_sort-subtot = 'X'.
* APPEND wa_sort TO it_sort.
* CLEAR wa_sort.
ENDFORM. " SUB_SORT
05-08-2013 6:36 AM
Hi Hemant,
I couldnt find any flaw in the logic, infact I have implemented your loop with addition logic to make sure that it works correctly. I have one doubt though, you are appending your I_FINAL table twice. I think your summation of weights logic should occur in a loop on I_FINAL table and then you should collect this data in a new table I_FINAL2.
just try the above suggested change and pass this table to your ALV display FM. Please check in debugging that after creation of I_FINAL from IT_DEL table, you have the correct data in I_FINAL. similarly check I_FINAL2 has summed up correctly all the line item for all the deliveries.
Kindly revert after testing.
Regards,
Deepak
05-08-2013 6:59 AM
Hi,
Do a small change in the program. shuffle the loops.
after select statements:
loop at it_lips...
atend of vbeln.
....
....
append wa_lips to it_final_wgt. (use a new internal table it_final_wgt instead of it_final.
endat.
endloop.
Inside it_del loop. read it_final_wgt instead of reading it_lips.
loop at it_del.....
........
.......
READ TABLE it_final_wgt INTO wa_final_wgt WITH KEY vbeln = wa_del-vbeln.
wa_final-ntgew = wa_final_wgt-ntgew.
wa_final-brgew = wa_final_wgt-brgew.
wa_final-matnr = wa_final_wgt-matnr.
wa_final-posnr = wa_final_wgt-posnr.
...
...
append wa_final to it_final.
endloop.
Regards,
Swarna
05-08-2013 10:33 AM
05-07-2013 9:44 AM
Hi,
Check the point mentioned in documentation of FOR ALL ENTRIES:
Doc link:
http://help.sap.com/abapdocu_702/en/abenwhere_logexp_itab.htm
"With duplicated rows in the resulting set, the addition FOR ALL ENTRIES has the same effect as when the addition DISTINCT is specified in the definition of the selection set. Unlike DISTINCT, the rows are not always deleted from the database system but instead are sometimes first deleted from the result set on the application server. The duplicate rows are then removed from the database system if the SELECT statement can be passed to the database system as a single SQL statement. If the SELECT statement has to be distributed to multiple SQL statements, the aggregation takes place on the application server"
So include POSNR to make the resulting set unique then you will get all the entries.
Cheers,
Arindam
05-08-2013 7:14 AM
Dear Hemant,
As Deepak Nagar Worte, It is correct solution for this because For All Entries Delete all duplicates from Internal table. So User POSNR with VBELN.
Thanks!.
Ram Shanker