ZPPR029: Excess report

*&---------------------------------------------------------------------*
*& Progarm      :  ZPPR029                         Author : Jimmy
*& Created      :  2012-11-15                      App    : PP
*& Title        :  Excess report
*& Description  :  Excess report
*&---------------------------------------------------------------------*
*&  Version       Author      Date        description
*&                Jimmy       15 Nov 2012 the first version
*&  the last update time  2012.11.15 15:00
*&---------------------------------------------------------------------*

report  zppr029 no standard page heading.
*&---------------------------------------------------------------------*
* database table
*&---------------------------------------------------------------------*
tables :marc,prps.
data:begin of it_matnr occurs 0,
     werks like marc-werks,
     matnr like marc-matnr,
     ps_psp_pnr like prps-pspnr,
    end of it_matnr.
data:it_matnr1 like it_matnr occurs 0 with header line.
data:it_kunnr like zcust_prodlot occurs 0 with header line.
data: begin of it_stpo occurs 0 .
        include structure stpox.
data: end of  it_stpo.
data: begin of it_matcat occurs 0 .
        include structure cscmat.
data: end of it_matcat.
data: begin of it_sub_matnr occurs 0,
      m_matnr like mara-matnr,
      matnr   like mara-matnr,
      werks   like marc-werks,
    end of it_sub_matnr.
data:it_sub_matnr1 like it_sub_matnr  occurs 0 with header line.
data: begin of itab occurs 0,
      m_matnr like mara-matnr,
      matnr   like mara-matnr,
      werks   like marc-werks,
      kdmat   like knmt-kdmat,  "Customer Material
      maktx   like makt-maktx,
      bstmi   like marc-bstmi,  "Minimum Lot Size
      bstrf   like marc-bstrf,  "Rounding value
      plifz   like marc-plifz,  "Planned Deliv. Time
      verpr   like mbew-verpr,  "net price
      peinh   like mbew-peinh,  "per
      clabs   like mchb-clabs,  "On Hand qty
      meins   like mara-meins,
      menge   like ekpo-menge,  "PO qty
      reqqty  like ekpo-menge,  "req  qty
      balqty  like ekpo-menge,  "
      balamt  like ekpo-netwr,  " amount
      pobalqty like ekpo-menge,
      pobalamt like ekpo-netwr,  " PO amount
    end of itab.
data:begin of it_makt occurs 0,
    matnr like makt-matnr,
    maktx like makt-maktx,
    end of it_makt.
data:begin of it_knmt occurs 0,
      kunnr like knmt-kunnr,
      matnr like mara-matnr,
      vkorg like knmt-vkorg,
      kdmat like knmt-kdmat ,
     end of it_knmt.
data:begin of it_marc occurs 0,
      matnr   like mara-matnr,
      werks   like marc-werks,
      bstmi   like marc-bstmi,  "Minimum Lot Size
      bstrf   like marc-bstrf,  "Rounding value
      plifz   like marc-plifz,  "Planned Deliv. Time
    end of it_marc.
data:begin of it_mbew occurs 0,
    matnr  like mbew-matnr,
    bwkey  like mbew-bwkey,
    verpr  like mbew-verpr,  "net price
    peinh  like mbew-peinh,  "per
     end of it_mbew.
data:begin of it_mchb1 occurs 0,
      matnr like mara-matnr,
      werks   like marc-werks,
      lgort   like mchb-lgort ,
      charg   like mchb-lgort ,
      clabs   like mchb-clabs,
     end of it_mchb1.
data:it_mchb like it_mchb1 occurs 0 with header line.
data:it_mara like mara occurs 0 with header line.
ranges:ms_werks for marc-werks.
data: begin of xtab occurs 0,
      werks like ekpo-werks,
      lgort like ekpo-lgort,
      matnr like ekpo-matnr,
      menge like ekpo-menge,
      mengk like ekpo-menge,
      end of xtab.
data: it_mrp_list like bapi_mrp_ind_lines occurs 0 with header line.
data:lv_total_line type i,
     lv_line type p decimals 3.
type-pools: slis.
data: fc_hier type slis_t_fieldcat_alv,
      wa_hier type slis_fieldcat_alv .
*----------------------------------------------------------------------*
*  Parameter & Select-Options                                          *
*----------------------------------------------------------------------*
selection-screen begin of block b1 with frame title text-001.
parameters :      p_werks like marc-werks default '8101' obligatory.
select-options :  s_pspnr  for prps-pspnr,
                  s_matnr  for marc-matnr.
selection-screen end of block b1.
*&---------------------------------------------------------------------*
* start of process
*&---------------------------------------------------------------------*
start-of-selection.
  perform get_matnr.
  if it_matnr[] is initial.
    message 'No Record Found' type 'I'.
    exit.
  endif .
  perform get_data.
  if it_sub_matnr[] is initial.
    message 'No Record Found' type 'I'.
    exit.
  endif .
  perform process_data.
  perform dispaly_data.
*&---------------------------------------------------------------------*
*&      Form  GET_MATNR
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form get_matnr .
  select distinct werks matnr ps_psp_pnr
    into corresponding fields of table it_matnr1
    from zmm_prodlot
    where werks = p_werks
      and ps_psp_pnr in s_pspnr
      and matnr in s_matnr.
  if it_matnr1[] is not initial.
    select * into table it_kunnr
      from zcust_prodlot
      for all entries in it_matnr1
      where vkorg = it_matnr1-werks
        and ps_psp_pnr = it_matnr1-ps_psp_pnr.
    sort it_kunnr by vkorg ps_psp_pnr.
    sort it_matnr1 by matnr werks.
    loop at it_matnr1.
      it_matnr-matnr = it_matnr1-matnr.
      it_matnr-werks = it_matnr1-werks.
      collect it_matnr.
      clear:it_matnr.
    endloop.
  endif.
endform.                    " GET_MATNR
*&---------------------------------------------------------------------*
*&      Form  GET_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form get_data .
  describe table  it_matnr lines lv_total_line.
  loop at it_matnr.
    lv_line =  sy-tabix  / lv_total_line * 100.
    call function 'SAPGUI_PROGRESS_INDICATOR'
      exporting
        percentage = lv_line
        text       = 'Get Data........'.

    call function 'CS_BOM_EXPL_MAT_V2'
      exporting
        capid                 = 'PP01'
        datuv                 = sy-datum
        emeng                 = 1
        mtnrv                 = it_matnr-matnr
        mehrs                 = 'X'
        mmory                 = 'X'
        werks                 = it_matnr-werks
        aumgb                 = 'X'
      tables
        stb                   = it_stpo
        matcat                = it_matcat
      exceptions
        material_not_found    = 4
        no_plant_data         = 8
        no_bom_found          = 12
        no_suitable_bom_found = 16
        alt_not_found         = 24
        missing_authorization = 28
        conversion_error      = 36.
    if sy-subrc eq 0 .
      read table it_stpo with key postp = 'L'.
      if sy-subrc eq 0.
        loop at it_stpo.
          check it_stpo-postp = 'L'.
          it_sub_matnr-matnr = it_stpo-idnrk.
          it_sub_matnr-werks = it_matnr-werks.
          append it_sub_matnr.
          clear:it_sub_matnr.
          it_sub_matnr1-m_matnr = it_matnr-matnr.
          it_sub_matnr1-matnr = it_stpo-idnrk.
          it_sub_matnr1-werks = it_matnr-werks.
          append it_sub_matnr1.
          clear:it_sub_matnr1.
        endloop.
      endif.
    endif.
    clear:it_stpo,it_stpo[],it_matcat,it_matcat[]
          .
  endloop.
endform.                    " GET_DATA
*&---------------------------------------------------------------------*
*&      Form  PROCESS_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form process_data .
  select matnr maktx
      into corresponding fields of table it_makt
      from makt
      for all entries in it_sub_matnr
      where matnr = it_sub_matnr-matnr.
  sort it_sub_matnr1 by matnr werks.
  sort it_makt by matnr.

  select kunnr matnr vkorg kdmat
     into corresponding fields of table it_knmt
     from knmt
     for all entries in it_sub_matnr
     where matnr =  it_sub_matnr-matnr
       and vkorg = it_sub_matnr-werks.
  sort it_knmt by kunnr matnr vkorg.

  select matnr werks bstmi bstrf plifz
      into corresponding fields of table it_marc
      from marc
      for all entries in it_sub_matnr
      where matnr = it_sub_matnr-matnr
        and werks = it_sub_matnr-werks .
  sort it_marc by  matnr werks.

  select matnr  bwkey verpr  peinh
     into corresponding fields of table it_mbew
      from mbew
      for all entries in it_sub_matnr
      where matnr = it_sub_matnr-matnr
        and bwkey = it_sub_matnr-werks .
  sort it_mbew by matnr bwkey.

  select matnr werks lgort charg clabs
      into corresponding fields of table it_mchb1
      from mchb
      for all entries in it_sub_matnr
      where matnr = it_sub_matnr-matnr
        and werks = it_sub_matnr-werks .
  loop at it_mchb1.
    it_mchb-matnr = it_mchb1-matnr.
    it_mchb-werks = it_mchb1-werks.
    it_mchb-clabs = it_mchb1-clabs.
    collect it_mchb.
    clear :it_mchb.
  endloop.
  sort  it_mchb by matnr werks.

  select * into table it_mara
    from mara
    for all entries in it_sub_matnr
    where matnr = it_sub_matnr-matnr.
  sort it_mara by matnr.

  describe table  it_sub_matnr lines lv_total_line.
  loop at it_sub_matnr ."where matnr = '10008077001R000'.
    lv_line =  sy-tabix  / lv_total_line * 100.
    call function 'SAPGUI_PROGRESS_INDICATOR'
      exporting
        percentage = lv_line
        text       = 'Process Data........'.
    move-corresponding it_sub_matnr to itab.
    "Desc
    read table it_makt with key matnr = it_sub_matnr-matnr binary search.
    if sy-subrc eq 0.
      itab-maktx = it_makt-maktx.
    endif.
    "customer material
    read table it_sub_matnr1 with  key matnr = itab-matnr werks = itab-werks binary search.
    if sy-subrc eq 0.
      itab-m_matnr = it_sub_matnr1-m_matnr.
      read table it_matnr1 with key matnr = itab-m_matnr werks = itab-werks binary search.
      if sy-subrc eq 0.
        read table it_kunnr with key  vkorg = itab-werks  ps_psp_pnr = it_matnr1-ps_psp_pnr binary search.
        if sy-subrc eq 0.
          loop at it_kunnr where vkorg = itab-werks  and ps_psp_pnr = it_matnr1-ps_psp_pnr.
            read table it_knmt with  key kunnr = it_kunnr-kunnr matnr = itab-matnr vkorg = itab-werks binary search.
            if sy-subrc eq 0.
              itab-kdmat = it_knmt-kdmat.
              exit.
            endif.
          endloop.
        else.
          read table it_knmt with  key  matnr = itab-matnr vkorg = itab-werks .
          if sy-subrc eq 0.
            itab-kdmat = it_knmt-kdmat.
          endif.
        endif.
      endif.
    endif.
    "Minimum Lot Size
    read table it_marc with  key matnr = itab-matnr werks = itab-werks binary search.
    if sy-subrc eq 0.
      itab-bstmi = it_marc-bstmi.
      itab-bstrf = it_marc-bstrf.
      itab-plifz = it_marc-plifz.
    endif.
    "net price
    read table it_mbew with  key matnr = itab-matnr bwkey = itab-werks binary search.
    if sy-subrc eq 0.
      itab-peinh = it_mbew-peinh .
      itab-verpr = it_mbew-verpr  .
    else.
      itab-verpr = 0.
      itab-peinh = 0.
    endif.
    "Unrestricted use qty
    read table it_mchb with  key matnr = itab-matnr werks = itab-werks binary search.
    if sy-subrc eq 0.
      itab-clabs = it_mchb-clabs.
    endif.
    "unit
    read table it_mara with key  matnr = itab-matnr  binary search.
    if sy-subrc eq 0.
      itab-meins = it_mara-meins.
    endif.

    "PO Qty
    call function 'MB_ADD_PURCHASE_ORDER_QUANTITY'
      exporting
        x_matnr = itab-matnr
        x_meins = itab-meins
        x_elikz = space
        x_loekz = space
      tables
        xtab    = xtab
        xwerks  = ms_werks.
    if not xtab[] is initial.
      loop at xtab where werks = itab-werks.
        itab-menge = itab-menge + xtab-menge.
      endloop.
    endif.
    "req Qty
    call function 'BAPI_MATERIAL_MRP_LIST'
      exporting
        material      = itab-matnr
        plant         = itab-werks
      tables
        mrp_ind_lines = it_mrp_list.
    loop at it_mrp_list where rec_reqd_qty < 0 .
      itab-reqqty = itab-reqqty + it_mrp_list-rec_reqd_qty.
    endloop.
    itab-reqqty = 0 - itab-reqqty.

    itab-balqty = itab-clabs - itab-reqqty.

    if itab-balqty > 0.
      if itab-peinh ne 0.
        itab-balamt = itab-balqty * itab-verpr / itab-peinh.
      endif.
      itab-pobalqty =  itab-menge.
    else.
      itab-balamt  = 0.
      itab-pobalqty = itab-balqty + itab-menge.
    endif.
    if itab-peinh ne 0.
      itab-pobalamt = itab-pobalqty  * itab-verpr / itab-peinh..
    endif.
    append itab.
    clear:itab,xtab,xtab[],ms_werks,ms_werks[],it_mrp_list,it_mrp_list[].
  endloop.
  free:it_mara,it_mchb ,it_mbew ,it_marc,it_knmt,it_kunnr,it_matnr1,it_sub_matnr1,it_makt,it_matnr,it_sub_matnr .
endform.                    " PROCESS_DATA
*&---------------------------------------------------------------------*
*&      Form  DISPALY_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form dispaly_data .
  define alv_field.
    clear:wa_hier.
    wa_hier-fieldname = &1.
    wa_hier-seltext_m = &2.
    wa_hier-seltext_l = &3.
    wa_hier-outputlen = &4.
    wa_hier-just = &5.
    wa_hier-do_sum = &6.
    wa_hier-ref_tabname = &7 .
    wa_hier-ref_fieldname = &8 .
    append wa_hier to fc_hier.
  end-of-definition.

  sort itab by werks matnr.
  refresh: fc_hier.
  alv_field  'WERKS' 'Plant' '' '6' 'L' '' '' ''.
  alv_field  'MATNR' 'Material' '' '18' 'L' '' 'MARA' 'MATNR'.
  alv_field  'MAKTX' '' 'Material Desc' '20' 'L' '' '' ''.
  alv_field  'KDMAT' '' 'Cust Material' '20' 'L' '' '' ''.
  alv_field  'VERPR' 'Net Price' '' '' 'R' '' '' ''.
  alv_field  'PEINH' 'Price Unit' '' '' 'R' '' '' ''.
  alv_field  'CLABS' '' 'Unrestricted use qty' '15' 'R' 'X' '' ''.
  alv_field  'REQQTY' '' 'Request Qty' '15' 'R' 'X' '' ''.
  alv_field  'MENGE' '' 'PO Qty' '15' 'R' 'X' '' ''.
  alv_field  'BALQTY' '' 'Excess Qty' '15' 'R' 'X' '' ''.
  alv_field  'BALAMT' '' 'Excess value' '15' 'R' 'X' '' ''.
  alv_field  'POBALQTY' '' 'Excess PO Qty' '15' 'R' 'X' '' ''.
  alv_field  'POBALAMT' '' 'Excess PO value' '15' 'R' 'X' '' ''.
  alv_field  'BSTMI' '' 'Minimum Lot Size' '' 'R' '' '' ''.
  alv_field  'BSTRF' '' 'Rounding value' '' 'R' '' '' ''.
  alv_field  'PLIFZ' '' 'Planned Deliv. Time' '' 'L' '' '' ''.
  alv_field  'M_MATNR' '' 'Where Used' '20' 'L' '' '' ''.

  call function 'REUSE_ALV_GRID_DISPLAY'
    exporting
      it_fieldcat        = fc_hier[]
      i_save             = 'A'
      i_callback_program = sy-repid
    tables
      t_outtab           = itab[]
    exceptions
      program_error      = 1
      others             = 2.
endform.                    " DISPALY_DATA