Make your own free website on Tripod.com

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