Table of Contents:
REPORT ZV_MATERIAL_JOBS MESSAGE-ID zz
NO STANDARD PAGE HEADING LINE-SIZE 196.
*@----------------------------------------------------------------------
*@ Program id :
*@ Program Desc : List Material Revenue
*@ Transaction Code : ZVMATJOBS
*@ Input files :
*@
*@ Output files : none
*@
*@ Tables Updated: Table - none
*@
*@ Author : I. Jadalowen
*@ Date : April 10, 2001
*@ Requested by :
*@----------------------------------------------------------------------
*@ Algorithm:
*@----------------------------------------------------------------------
** Change History:
** Correction Mod by Date Description
** LABK929775 2001/04/11 IJADALOW
** Check billing type for credits
** LABK929779 2001/04/12 IJADALOW
** Add Quantity and Unit fields. Add Drill down
** functionality on the Billing document.
** LABK929793 2001/04/17 IJADALOW
** Added new column in report for Plant. and changed the
** quantity and units fields.
**
** LABK933879 2002/10/30 PTAM
** Add customer name as per Joyce
**
** LABK933885 2002/10/30 PTAM
** Add customer as select option as per Joyce
**
**
*@----------------------------------------------------------------------
INCLUDE zsmaster.
include .
tables: vbrk, vbrp, kna1.
types: begin of t_mats,
kvgr1 like vbrp-kvgr1, "job type
matnr like vbrp-matnr, "Material Number
maktx like makt-maktx, "Material Description
vbeln like vbrp-vbeln, "Billing Document
fkdat like vbrk-fkdat, "billing Date
kunag like vbrk-kunag, "Bill to partner
name1 like kna1-name1, "Customer name
netsal like vbrp-netwr, "Net material Sale
netinv like vbrk-netwr, "Net invoice
fklmg like vbrp-fklmg, "Billed Quantity
meins like vbrp-meins, "Base Unit of Measure
werks like vbrp-werks, "Plant
end of t_mats.
data: begin of i_material occurs 100,
matnr like vbrp-matnr, "Material Number
maktx like makt-maktx, "Material Description
end of i_material.
* LABK933879 ->
data: begin of i_customer occurs 100,
kunnr like kna1-kunnr, "Bill to partner
name1 like kna1-name1, "customer name
end of i_customer.
* <- LABK933879
data: begin of wa_items,
kvgr1 like vbrp-kvgr1, "job type
matnr like vbrp-matnr, "Material Number
vbeln like vbrp-vbeln, "Billing Document
fkdat like vbrk-fkdat, "billing Date
kunag like vbrk-kunag, "Bill to partner
netsal like vbrp-netwr, "Net material Sale
netinv like vbrk-netwr, "Net invoice
billtype like vbrk-fkart,
fklmg like vbrp-fklmg, "Billed Quantity
meins like vbrp-meins, "Base Unit of Measure
werks like vbrp-werks, "Plant
end of wa_items.
*
data: itmats TYPE t_mats occurs 100 with header line,
wa_itmats type t_mats,
it_items like wa_items occurs 100 with header line,
w_excel_filename LIKE ibipparms-path.
DATA: BEGIN OF it_title OCCURS 10,
title(15) TYPE c,
END OF it_title.
DATA: w_excel_default_path(20) TYPE c,
w_excel_default_file(20) TYPE c.
selection-screen skip 1.
selection-screen begin of block block0 with frame title text-001.
selection-screen skip 1.
select-options s_matnr for vbrp-matnr Obligatory.
"Material Numb
select-options s_date for vbrk-fkdat obligatory no-extension.
select-options s_vkorg for vbrk-vkorg default '1000'. "Sales Org
select-options s_jtype for vbrp-kvgr1. "Job Type
select-options s_kunag for vbrk-kunag. "customer
* PARAMETERS: c_dupl AS CHECKBOX DEFAULT ' '.
selection-screen end of block block0.
selection-screen begin of block block1 with frame title text-002.
PARAMETERS: c_dupl AS CHECKBOX DEFAULT ' '.
selection-screen end of block block1.
initialization.
** Populate the Material variables with default data
* mac_set_range_row s_matnr mac_c_append: '24533' '24535' .
* mac_set_range_row s_date mac_c_append: '19990817' '19991231'.
* mac_set_range_row s_jtype mac_c_append: '405' '430'.
* refresh itmats.
start-of-selection.
perform load_data.
sort itmats by kvgr1 vbeln.
end-of-selection.
SET PF-STATUS 'STATUS1'.
* SET PF-STATUS 'STATUS2'.
perform write_data.
top-of-page.
write: 'Material Revenue'.
uline (194).
perform write_col_header.
TOP-OF-PAGE DURING LINE-SELECTION.
write: 'Material Revenue'.
uline (194).
PERFORM write_col_header.
*/- Drill Down ------------------------------\*
AT LINE-SELECTION.
check not wa_itmats-vbeln is initial.
set parameter ID 'VF' field wa_itmats-vbeln.
call transaction 'VF03' and skip first screen.
clear wa_itmats-vbeln.
AT USER-COMMAND.
CASE sy-ucomm.
WHEN 'DOWNLOAD'.
CLEAR w_excel_filename.
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
def_filename = w_excel_default_file
def_path = w_excel_default_path
mask = ',*.* ,*.*.'
mode = 'S'
title = 'Select Output Excel File'
IMPORTING
filename = w_excel_filename
EXCEPTIONS
inv_winsys = 1
no_batch = 2
selection_cancel = 3
selection_error = 4
OTHERS = 5.
IF NOT w_excel_filename IS INITIAL.
PERFORM proc_download.
ENDIF.
WHEN 'SORT2'.
SORT itmats BY matnr vbeln .
SET PF-STATUS 'STATUS2'.
PERFORM write_data.
sy-lsind = 0.
WHEN 'SORT1'.
SORT itmats by kvgr1 vbeln.
SET PF-STATUS 'STATUS1'.
PERFORM write_data.
sy-lsind = 0.
WHEN 'SORT3'.
SORT itmats by vbeln kvgr1.
SET PF-STATUS 'STATUS3'.
PERFORM write_data.
sy-lsind = 0.
WHEN 'SORT4'.
SORT itmats by name1 kvgr1.
SET PF-STATUS 'STATUS4'.
PERFORM write_data.
sy-lsind = 0.
ENDCASE.
FORM load_data.
data prev_bill like wa_items-vbeln.
select vbrp~kvgr1 vbrp~matnr vbrp~vbeln vbrk~fkdat vbrk~kunag
vbrp~netwr vbrk~netwr vbrk~fkart vbrp~fklmg vbrp~meins vbrp~werks
into table it_items
from vbrp join vbrk on vbrp~vbeln = vbrk~vbeln
where vbrp~matnr in s_matnr
and vbrk~vkorg in s_vkorg
and vbrp~kvgr1 in s_jtype
and vbrk~kunag in s_kunag
and vbrk~fkdat in s_date
and ( fkart = 'F2' or
fkart = 'ZZF2' or
fkart = 'ZXF2' or
fkart = 'ZYF2' or
fkart = 'G2' or
fkart = 'ZZG2' or
fkart = 'ZXG2' or
fkart = 'ZYG2' or
fkart = 'L2' or
fkart = 'ZZL2' or
fkart = 'ZXL2' or
fkart = 'ZYL2' or
fkart = 'RE' or
fkart = 'ZZRE' or
fkart = 'ZXRE' or
fkart = 'ZYRE' or
fkart = 'S1' or
fkart = 'ZZS1' or
fkart = 'ZXS1' or
fkart = 'zyS1' or
fkart = 'S2' or
fkart = 'ZZS2' or
fkart = 'ZXS2' or
fkart = 'ZYS2' ).
*&-- Get rid of Duplicate invoice records --&*
sort it_items by vbeln.
loop at it_items.
move-corresponding it_items to wa_itmats.
if it_items-billtype = 'G2' or it_items-billtype = 'ZZG2' or
it_items-billtype = 'ZXG2' or it_items-billtype = 'ZYG2' or
it_items-billtype = 'S1' or it_items-billtype = 'ZZS1' or
it_items-billtype = 'ZXS1' or it_items-billtype = 'ZYS1'.
wa_itmats-netsal = wa_itmats-netsal * -1.
wa_itmats-netinv = wa_itmats-netinv * -1.
wa_itmats-fklmg = wa_itmats-fklmg * -1.
endif.
if c_dupl is initial.
append wa_itmats to itmats.
else.
if prev_bill <> it_items-vbeln.
append wa_itmats to itmats.
endif.
endif.
prev_bill = it_items-vbeln.
endloop.
*&-- Get Material Description --&*
select distinct matnr maktx into table i_material
from makt for all entries in itmats
where matnr = itmats-matnr
and spras = sy-langu.
* LABK933879 ->
*&-- Get Customer name --&*
select distinct kunnr name1 into table i_customer
from kna1 for all entries in itmats
where kunnr = itmats-kunag.
* <- LABK933879
free it_items.
ENDFORM.
*&----------------------------------------------------------&*
form write_col_header.
* SKIP.
FORMAT INTENSIFIED ON COLOR 2.
NEW-LINE.
WRITE 01 line_top_left_corner AS LINE.
ULINE 02(193).
WRITE: 194 line_top_right_corner AS LINE NO-GAP.
NEW-LINE.
WRITE: sy-vline NO-GAP,
02(8) text-010,
11 sy-vline NO-GAP,
12(18) text-011,
21 sy-vline NO-GAP,
22(39) text-012,
62 sy-vline NO-GAP,
63(10) text-013,
74 sy-vline NO-GAP,
75(10) text-014,
86 sy-vline NO-GAP,
87(15) text-020,
122 sy-vline NO-GAP,
123(20) text-015,
144 sy-vline NO-GAP,
145(20) text-016,
166 sy-vline NO-GAP,
167(17) text-017,
184 sy-vline NO-GAP,
185(3) text-018,
188 sy-vline NO-GAP,
189(5) text-019,
194 sy-vline NO-GAP.
NEW-LINE.
ULINE (194).
FORMAT RESET.
endform. "write_col_header
*/**************************************/
FORM write_data.
data: total_inv like wa_itmats-netinv value 0,
total_sal like wa_itmats-netsal value 0.
FORMAT INTENSIFIED OFF COLOR 2.
loop at itmats into wa_itmats.
at new matnr.
read table i_material with key matnr = wa_itmats-matnr.
endat.
* LABK933879
at new kunag.
read table i_customer with key kunnr = wa_itmats-kunag.
endat.
wa_itmats-name1 = i_customer-name1.
* <- LABK933879
wa_itmats-maktx = i_material-maktx.
modify itmats from wa_itmats transporting maktx name1.
write: / sy-vline NO-GAP,
02 wa_itmats-kvgr1,
11 sy-vline NO-GAP,
12 wa_itmats-matnr,
21 sy-vline NO-GAP,
22 wa_itmats-maktx,
62 sy-vline NO-GAP,
63 wa_itmats-vbeln,
74 sy-vline NO-GAP,
75 wa_itmats-fkdat,
86 sy-vline NO-GAP,
87 wa_itmats-name1,
122 sy-vline NO-GAP,
123 wa_itmats-netsal,
144 sy-vline NO-GAP,
145 wa_itmats-netinv,
166 sy-vline NO-GAP,
167 wa_itmats-fklmg,
184 sy-vline NO-GAP,
185 wa_itmats-meins,
188 sy-vline NO-GAP,
189 wa_itmats-werks,
194 sy-vline NO-GAP.
total_inv = total_inv + wa_itmats-netinv.
total_sal = total_sal + wa_itmats-netsal.
*/--- We need this field for the drill down ----------\*
HIDE: wa_itmats-vbeln.
clear wa_itmats-vbeln.
endloop.
if not wa_itmats is initial.
NEW-LINE.
WRITE 01 LINE_LEFT_MIDDLE_CORNER AS LINE.
ULINE 02(193).
WRITE 194 LINE_right_MIDDLE_CORNER AS LINE.
*/-- Write out the Totals -----------------------------/*
write: / sy-vline NO-GAP,
'Totals',
122 sy-vline NO-GAP,
123 total_sal,
144 sy-vline NO-GAP,
145 total_inv,
166 sy-vline NO-GAP,
194 sy-vline NO-GAP.
*/-------------------------------------------------------\*
NEW-LINE.
WRITE 01 line_bottom_left_corner AS LINE.
ULINE 02(193).
WRITE: 194 Line_bottom_right_corner AS LINE NO-GAP.
else.
write ' '.
endif.
endform. " write_data
*---------------------------------------------------------------------
* download to excel file
*---------------------------------------------------------------------
FORM proc_download.
REFRESH it_title.
DEFINE lmac_append_title.
it_title-title = &1.
append it_title.
END-OF-DEFINITION.
lmac_append_title: text-010, text-011, text-012, text-013, text-014,
text-021, text-020,
text-015, text-016, text-017, text-018, text-019.
mac_ws_download_titled_itab w_excel_filename itmats it_title.
IF sy-subrc = 0.
MESSAGE i001(zz) WITH 'Report has been downloaded to '
w_excel_filename.
ELSE.
MESSAGE i001(zz) WITH 'Unable to download to ' w_excel_filename.
ENDIF.
ENDFORM. " PROC_DOWNLOAD