SD 销售OTTR报告

selection text

 

AUART 订单类别
AUDAT 下单日期
DISPO MRP控制者
KUNNR 客户编码
LABGRU 排除拒绝发货
MATNR 物料编码
PEDATU 计划行日期(约定交货日)
PVDATU 请求交货日期
RAD_1 总览
RAD_2 明细
VBELN 订单号
VKORG 销售组织
WQ 只显示未清数量大于0的

 

*& ----------------------------------------------------
*& 程序描述：销售OTTR报表
*& 编 写 者：CHENJR
*& 编写日期：20080911
*& 业务需求：
*& 报表开发需求文档：HL2008082803
*&---------------------------------------------------------------------*
*& LOG#       DATE         AUTHOR               DESCRIPTION            *
*& ----       ----          ------               -----------           *
*&  001      2009/06/03    kevin             add  PEDATU               *
*& 修改日期：2010.06.03
*& 修 改 人：zhouxu V1.0
*& 修改原因：1 优化程序速度
*& 修改日期：2010.08.05
*& 修 改 人：zhouxu V1.1
*& 修改原因：1 订单数量取值更改
*& 修改日期：2010.08.12
*& 修 改 人：zhouxu V1.2
*& 修改原因：1 解决输入订单号无法查询数据问题。
REPORT  ysdr0004.

"LINE-SIZE 130 LINE-COUNT 50(2) NO STANDARD PAGE HEADING.
*&--------------------------------------------------------------------*
* Global Data  Declarations:                                          *
*&--------------------------------------------------------------------*
INCLUDE y_alv_func.
TABLES:
       vbak,       "销售凭证抬头
       vbap,       "销售凭证项目
       kna1,       "客户主文件的一般数据
       makt,       "物料描述
       vbep,       "销售计划行数据
       knvv,       "客户主记录销售数据
       lips,       "交货项目数据
       likp,       "交货抬头数据
       marc        "物料的工厂数据
       .

*TYPE-POOLS: slis.

*DATA: gt_list_top_of_page TYPE slis_t_listheader.
*DATA: gc_formname_top_of_page TYPE slis_formname
*                             VALUE 'ALV_TOP_OF_PAGE'.
*DATA : ps_layout TYPE slis_layout_alv.
*&--------------------------------------------------------------------*
*  internal tables
*&--------------------------------------------------------------------*
DATA:BEGIN OF itab OCCURS 100,
     vbeln LIKE vbak-vbeln,              "订单号
     auart LIKE vbak-auart,                                 "订单类别ysd17
     bezei LIKE tvakt-bezei,             "订单类别中文描述
     audat LIKE vbak-audat,              "下单日期
     kunnr LIKE vbak-kunnr,              "客户编码
     name1 LIKE kna1-name1,              "客户名称
     matnr LIKE vbap-matnr,              "物料号
     posnr LIKE vbap-posnr,              "项目号
     lbeln LIKE lips-vbeln,              "发货单号-LIPS
*     losnr LIKE lips-posnr,              "发货项目号-LIPS
     maktx LIKE makt-maktx,              "物料描述
     meins LIKE vbap-meins,              "基本计量单位
     wmeng LIKE vbep-wmeng,              "订单数量
     vdatu LIKE vbak-vdatu,              "请求交货日期 add by kevin 2009/6/03
     edatu LIKE vbep-edatu,              "计划行日期"首个交货日期 add by kaijunli 2008/12/01
     lfimg LIKE lips-lfimg,              "实际发货量
     wadat_ist LIKE likp-wadat_ist,      "实际发货日期
     bldat LIKE likp-bldat,              "交货单凭证日期 add by kevin 2009/06/05
     abgru LIKE vbap-abgru,              "拒绝交货原因 add by kevin 2009/06/22
     wqsl LIKE lips-lfimg,               "未清数量
     rqch TYPE i,                        "日期差
     ottr TYPE i,                        "OTTR标识
     jhzq TYPE i,                        "交货周期 ADD V1.0
     yy(2) TYPE c,                       "原因 ADD V1.0
     dispo LIKE marc-dispo ,"mrp控制者
     erdat LIKE vbak-erdat ,"DATS 8 0 记录的创建日期
     erzet LIKE  vbak-erzet ," TIMS 6 0 输入时间
     ernam LIKE vbak-ernam,
*     namstr type string,
     END OF itab.

DATA:BEGIN OF itab2 OCCURS 100,
     vbeln LIKE vbak-vbeln,              "订单号
     auart LIKE vbak-auart,              "订单类别
     bezei LIKE tvakt-bezei,             "订单类别中文描述
     ottr TYPE i,                        "OTTR标识
     audat LIKE vbak-audat,              "下单日期
     kunnr LIKE vbak-kunnr,              "客户编码
     name1 LIKE kna1-name1,              "客户名称
END OF itab2.
*------------------Begin V1.0---------------------------------
TYPES:BEGIN OF t_lips,
  vgbel  LIKE lips-vgbel, "销售凭证号
  vgpos LIKE lips-vgpos, "销售凭证项目
  lfimg LIKE lips-lfimg,
END OF t_lips.
TYPES:BEGIN OF t_likp,
  vgbel  LIKE lips-vgbel, "销售凭证号
  vgpos LIKE lips-vgpos, "销售凭证项目
    bldat LIKE likp-bldat,
  wadat_ist LIKE likp-wadat_ist,
END OF t_likp.
*DATA: it_lips TYPE TABLE OF t_lips  WITH HEADER LINE."保存每个凭证的中发货数量
DATA: it_lips TYPE  HASHED TABLE OF t_lips  WITH UNIQUE KEY vgbel  vgpos."保存每个凭证的中发货数量
DATA: it_likp TYPE HASHED TABLE OF t_likp WITH UNIQUE KEY vgbel  vgpos   ."保存首次发货日期
DATA:wa_lips TYPE t_lips .
DATA:wa_likp TYPE t_likp.
*------------------End V1.0--------------------------------------
DATA num TYPE i VALUE 0 .  " 假期天数
DATA sdqty LIKE lips-lfimg.
*&--------------------------------------------------------------------*
* Selection Screen:
*&--------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE t1.

SELECT-OPTIONS: kunnr FOR vbak-kunnr. " OBLIGATORY .     "客户编码
SELECT-OPTIONS: vkorg FOR vbak-vkorg. " OBLIGATORY.      "销售组织
SELECT-OPTIONS: vbeln FOR vbak-vbeln.                    "订单号
SELECT-OPTIONS: matnr FOR vbap-matnr.                    "物料编码
SELECT-OPTIONS: dispo FOR marc-dispo.                    "控制者
SELECT-OPTIONS: audat FOR vbak-audat.                    "订单下达时间
SELECT-OPTIONS: auart FOR vbak-auart.                    "订单类别"销售订单类型
SELECT-OPTIONS: pvdatu FOR vbak-vdatu.                  " 请求交货日期
SELECT-OPTIONS: pedatu FOR vbep-edatu.                  " 首个交货日期
SELECT-OPTIONS:vgbel FOR lips-vgbel NO-DISPLAY. " 交货凭证
PARAMETERS: wq TYPE c AS CHECKBOX ."只显示未清数大于0 的。
SELECTION-SCREEN END OF BLOCK b1.

SELECTION-SCREEN : BEGIN OF BLOCK b2 WITH FRAME TITLE t2.
PARAMETERS: rad_1 RADIOBUTTON GROUP 1.                             "总览
PARAMETERS: rad_2 RADIOBUTTON GROUP 1 DEFAULT 'X'.                 "明细

PARAMETERS:  labgru AS CHECKBOX DEFAULT 'X'.

SELECTION-SCREEN END OF BLOCK b2.

INITIALIZATION.
  AUTHORITY-CHECK OBJECT 'YREPORT'
    ID 'BUKRS' FIELD 'HL01'
    ID 'WERKS' FIELD 'LN01'.
  IF sy-subrc NE 0.
    MESSAGE e005(ymess) WITH 'No Authorization!'.
  ENDIF.
  t1 = '选择条件'.
  t2 = '选择类型'.

START-OF-SELECTION.
  IF rad_1 = 'X'.
    PERFORM sub_get_data.
    PERFORM modify_itab.
    PERFORM sub_get_data1.
    PERFORM show_data1.
  ELSEIF rad_2 = 'X'.
    PERFORM sub_get_data.
    PERFORM modify_itab.
    PERFORM show_data.
  ENDIF.

*&--------------------------------------------------------------------*
*&      Form  sub_get_data
*&--------------------------------------------------------------------*
FORM sub_get_data.

*  IF dispo IS NOT INITIAL.        "MRP控制者筛选条件不为空时
  SELECT vbak~vbeln  vbak~erdat vbak~erzet vbak~ernam  vbak~audat vbak~kunnr vbap~posnr vbap~matnr
  vbap~meins vbak~auart  vbak~vdatu vbap~abgru
  vbap~kwmeng AS wmeng "v1.1 mod  原来的值 vbep~wmeng
  vbep~edatu marc~dispo
  FROM vbak   INNER JOIN vbap    ON  vbak~mandt = vbap~mandt AND  vbak~vbeln = vbap~vbeln
  INNER JOIN vbep ON vbep~mandt = vbap~mandt AND vbep~posnr = vbap~posnr AND vbep~vbeln = vbap~vbeln
  INNER JOIN marc    ON vbap~mandt = marc~mandt AND vbap~matnr = marc~matnr
  INTO CORRESPONDING FIELDS OF TABLE itab
  WHERE vbak~kunnr IN kunnr
  AND   vbak~vkorg IN vkorg
  AND   vbak~vbeln IN vbeln
  AND   vbap~matnr IN matnr
  AND   marc~dispo IN dispo
  AND   vbak~audat IN audat
  AND   vbak~auart IN auart
  AND vbep~etenr = '0001'
  AND vbep~edatu IN pedatu
  AND vbak~vdatu IN pvdatu
  .

  IF itab[] IS INITIAL.
    MESSAGE i888(sabapdocu) WITH '没有符合条件的数据'.
    STOP.
  ENDIF.
  IF labgru = 'X'.
    DELETE itab WHERE abgru NE ''.
  ENDIF.
*Begin V1.0 按凭证号和项目号汇总交货单数量into lips并查询发货日期 into likp
  SORT itab BY vbeln.
  DATA:temp_likp TYPE t_likp OCCURS 0.
  DATA: b TYPE i VALUE 0, i TYPE i VALUE 0.
  LOOP AT itab.
    AT NEW  vbeln.
      i  = i + 1.
      vgbel-sign = 'I'.
      vgbel-option = 'EQ'.
      vgbel-low = itab-vbeln.
      APPEND vgbel.
      IF i = 5000 .
        b = b + 1 .
        SELECT  vgbel vgpos SUM( lfimg ) AS lfimg  FROM lips
        APPENDING CORRESPONDING FIELDS OF  TABLE  it_lips
        WHERE vgbel IN vgbel
        GROUP BY vgbel vgpos.

        SELECT lips~vgbel lips~vgpos MIN( likp~wadat_ist ) AS wadat_ist
        APPENDING CORRESPONDING FIELDS OF  TABLE  temp_likp
        FROM likp INNER JOIN lips
        ON likp~mandt = lips~mandt AND likp~vbeln = lips~vbeln
        WHERE lips~vgbel  IN vgbel
        AND wadat_ist > '00000000'
        GROUP BY lips~vgbel lips~vgpos.

        i = 0.
        CLEAR:vgbel, vgbel[].
      ENDIF.

    ENDAT.

  ENDLOOP.
*  RETURN.
  IF b = 0.                                                 "少于5000条
    SELECT  vgbel vgpos SUM( lfimg ) AS lfimg  FROM lips
    INTO CORRESPONDING FIELDS OF  TABLE  it_lips
    WHERE vgbel IN vgbel
    GROUP BY vgbel vgpos    .

    SELECT lips~vgbel lips~vgpos MIN( likp~wadat_ist ) AS wadat_ist
        APPENDING CORRESPONDING FIELDS OF  TABLE  temp_likp
        FROM likp INNER JOIN lips
        ON likp~mandt = lips~mandt AND likp~vbeln = lips~vbeln
        WHERE lips~vgbel  IN vgbel
        AND wadat_ist > '00000000'
        GROUP BY lips~vgbel lips~vgpos.

  ENDIF.
  IF b > 0 AND i < 5000.                                    "大于五千条的余数部分5000条
    SELECT  vgbel vgpos SUM( lfimg ) AS lfimg  FROM lips
    APPENDING  CORRESPONDING FIELDS OF  TABLE  it_lips
    WHERE vgbel IN vgbel
    GROUP BY vgbel vgpos    .

    SELECT lips~vgbel lips~vgpos MIN( likp~wadat_ist ) AS wadat_ist
          APPENDING CORRESPONDING FIELDS OF  TABLE  temp_likp
          FROM likp INNER JOIN lips
          ON likp~mandt = lips~mandt AND likp~vbeln = lips~vbeln
          WHERE lips~vgbel  IN vgbel
          AND wadat_ist > '00000000'
          GROUP BY lips~vgbel lips~vgpos.

  ENDIF.
*del v1.2
*  IF  it_lips[] IS INITIAL.
*    MESSAGE i888(sabapdocu) WITH '没有符合条件的交货数据'.
*    STOP.
*  ENDIF.

*  SELECT lips~vgbel lips~vgpos likp~wadat_ist   FROM likp
*  INNER JOIN lips ON likp~mandt = lips~mandt AND likp~vbeln = lips~vbeln
*  INTO CORRESPONDING FIELDS OF  TABLE  it_likp
*  FOR ALL ENTRIES IN  it_lips
*  WHERE lips~vgbel =  it_lips-vgbel     .
*  SORT temp_likp BY vgbel vgpos wadat_ist DESCENDING.

  INSERT LINES OF temp_likp INTO TABLE it_likp.

*  DELETE ADJACENT DUPLICATES FROM it_likp COMPARING vgbel vgpos.
*------------Eed  V1.0 按凭证号和项目号汇总交货单数量----------------------

ENDFORM.                    "sub_get_data

*&--------------------------------------------------------------------*
*&      Form  MODIFY_ITAB
*&--------------------------------------------------------------------*
FORM modify_itab.

  LOOP AT itab.

    CLEAR :itab-lfimg ,wa_lips.
    READ TABLE it_lips  WITH TABLE KEY vgbel = itab-vbeln  vgpos = itab-posnr INTO wa_lips.
    IF sy-subrc =  0.
      itab-lfimg = wa_lips-lfimg.
    ENDIF.
    itab-jhzq = itab-edatu - itab-audat.
    itab-wqsl = itab-wmeng - itab-lfimg  .  "未清数量
    IF wq IS NOT INITIAL AND  itab-wqsl <= 0.
      DELETE itab.
      CONTINUE.
    ENDIF.

 

    CLEAR :itab-bldat,itab-wadat_ist,wa_likp.

*
    READ TABLE it_likp  WITH TABLE KEY vgbel = itab-vbeln  vgpos = itab-posnr  INTO wa_likp.
    IF sy-subrc =  0.
      itab-bldat = wa_likp-bldat.
      itab-wadat_ist = wa_likp-wadat_ist.
    ENDIF.

    SELECT SINGLE bezei FROM tvakt
    INTO itab-bezei
    WHERE auart EQ itab-auart
    AND spras EQ '1'.

    SELECT SINGLE name1 FROM kna1
     INTO  itab-name1
     WHERE kunnr EQ itab-kunnr.

    SELECT SINGLE maktx FROM makt
    INTO   itab-maktx
    WHERE matnr EQ itab-matnr.

 

* 开始计算OTTR

*    IF itab-wadat_ist NE '00000000'.
    IF itab-wadat_ist NE '00000000'.
      itab-rqch = itab-wadat_ist - itab-edatu.  "日期差
*      itab-rqch = itab-wadat_ist - itab-edatu.  "日期差
    ELSE.
      itab-rqch = 10000.
*      itab-wadat_ist =  sy-datum - itab-edatu  .
    ENDIF.
*************************日期差中抽出假期天数*********************    kevin 2009/6/3
    num = 0.
    IF itab-rqch > 0 AND itab-wadat_ist NE '00000000'.
*    if num > 10.
      CALL FUNCTION 'YHOLIDAY_GET_LOCAL'
        EXPORTING
          date_from = itab-edatu
          date_to   = itab-wadat_ist
        IMPORTING
          num       = num.
    ENDIF.
    IF num > 0 .
      itab-rqch = itab-rqch - num.
    ENDIF.
*****************************************************************
    IF itab-rqch <= 1                           "OTTR为1的条件为日期差在±1之间,且未清数量为0
*       AND itab-rqch >= -1                      "  提前交货不做限制   kevin 2009/6/3
       AND itab-wqsl = 0.
      itab-ottr = 1.
    ELSE.
      itab-ottr = 0.
    ENDIF.

    IF itab-rqch = 10000.
      itab-rqch = 0.
    ENDIF.

    SHIFT itab-vbeln LEFT DELETING LEADING '0'.             "删除订单号前导0
    SHIFT itab-matnr LEFT DELETING LEADING '0'.             "删除物料号前导0
    SHIFT itab-kunnr LEFT DELETING LEADING '0'.             "删除客户号前导0

    MODIFY itab.
    CLEAR  itab.

  ENDLOOP.

ENDFORM.                    "MODIFY_ITAB
*&--------------------------------------------------------------------*
*&      Form  sub_get_data1
*&--------------------------------------------------------------------*
FORM sub_get_data1.
  DATA:ottrt TYPE i.
  ottrt = 1.

  LOOP AT itab.
    itab2-vbeln = itab-vbeln.              "销售单号
    itab2-bezei = itab-bezei.              "销售订单类别
    itab2-audat = itab-audat.              "下单日期
    itab2-kunnr = itab-kunnr.              "客户编码
    itab2-name1 = itab-name1.              "客户名称
    itab2-ottr = itab-ottr * ottrt.        "统计订单号内行项目的OTTR,全1总OTTR为1,否则为0
    ottrt = itab2-ottr.                    "临时变量,存储这一循环的行项目OTTR用于下一循环
    AT END OF vbeln.
      ottrt = 1.                           "重置临时变量
      APPEND itab2.
      CLEAR itab2.
    ENDAT.
  ENDLOOP.

ENDFORM.                    "sub_get_data1

 

*&---------------------------------------------------------------------*
*&      Form  user_command
*&---------------------------------------------------------------------*
*       text  处理双击事件
*----------------------------------------------------------------------*
*      -->R_UCOMM      text
*      -->RS_SELFIELD  text
*----------------------------------------------------------------------*
FORM user_command  USING r_ucomm LIKE sy-ucomm
                           rs_selfield TYPE slis_selfield.

  CASE r_ucomm.
    WHEN '&IC1'.
      CASE rs_selfield-sel_tab_field.
        WHEN  'ITAB-VBELN'.
          SET PARAMETER ID 'AUN' FIELD rs_selfield-value.
          CALL TRANSACTION 'VA03' AND SKIP FIRST SCREEN.
        WHEN  'ITAB2-VBELN'.
          SET PARAMETER ID 'AUN' FIELD rs_selfield-value.
          CALL TRANSACTION 'VA03' AND SKIP FIRST SCREEN.
      ENDCASE.
    WHEN OTHERS.
  ENDCASE.

ENDFORM.                    "user_command
*&---------------------------------------------------------------------*
*&      Form  SHOW_DATA1
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM show_data1 .

  SORT itab2 BY vbeln.
  alv_clear_data.
  alv_add_data 'ITAB2'  '' ' ' '' 'X' 'CHAR' 'VBELN' '订单号' .
  alv_add_data 'ITAB2'  '' '' '' '' 'CHAR' 'BEZEI' '订单类别' .
  alv_add_data 'ITAB2' '' ' ' '' '' 'CHAR'  'AUDAT' '下单日期'  .
  alv_add_data 'ITAB2'  '' ' ' '' '' 'CHAR' 'KUNNR' '客户编码'  .
  alv_add_data 'ITAB2' '' ' ' '' '' 'CHAR' 'NAME1'  '客户名称' .
  alv_add_data 'ITAB2' '' ' ' '' '' 'CHAR' 'OTTR'    'OTTR' .
  alv_show_data itab2.
ENDFORM.                    " SHOW_DATA1
*&---------------------------------------------------------------------*
*&      Form  SHOW_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM show_data .
  SORT itab BY vbeln posnr.
  alv_clear_data.
  alv_add_data 'ITAB' ' ' ' ' '' '' 'CHAR' 'DISPO' 'MRP控制者'.
  alv_add_data 'ITAB' ' ' ' ' '' 'X' 'CHAR' 'VBELN' '订单号' .
  alv_add_data 'ITAB' ' ' ' ' '' '' 'CHAR' 'POSNR' '行项目'.
  alv_add_data 'ITAB' ' ' ' ' '' '' 'CHAR' 'BEZEI' '订单类别'.
  alv_add_data 'ITAB' ' ' ' ' '' '' 'CHAR'  'ERDAT' '订单创建日期' .
  alv_add_data 'ITAB' ' ' ' ' '' '' 'CHAR'  'ERNAM' '创建人' .
  alv_add_data 'ITAB' ' ' ' ' '' '' 'CHAR'  'AUDAT' '下单日期' .
  alv_add_data 'ITAB' ' ' ' ' '' '' 'CHAR' 'KUNNR' '客户编码' .
  alv_add_data 'ITAB' ' ' ' ' '' '' 'CHAR' 'NAME1'  '客户名称' .
  alv_add_data 'ITAB' ' ' ' ' '' '' 'CHAR'  'MATNR' '物料号' .
  alv_add_data 'ITAB' ' ' ' ' '' '' 'CHAR' 'MAKTX'   '物料描述'.
  alv_add_data 'ITAB'  ' ' ' ' '' '' 'CHAR'  'MEINS' '基本计量单位' .
  alv_add_data 'ITAB'  ' ' ' ' '' '' 'CHAR'   'VDATU' '请求交货日期' .
  alv_add_data 'ITAB'  ' ' ' ' '' '' 'CHAR' 'EDATU'  '约定交货日'  .
*  alv_add_data 'ITAB'  ' ' ' ' '' '' 'CHAR' 'BLDAT'  '发货凭证日期'  .
  alv_add_data 'ITAB'  ' ' ' ' '' '' 'CHAR' 'WADAT_IST'  '实际发货日期'  .
  alv_add_data 'ITAB'  ' ' ' ' '' '' 'CHAR'  'JHZQ'   '交货周期' .
  alv_add_data 'ITAB'  'X' ' ' '' '' 'CHAR' 'WMENG' '订单数量'   .
  alv_add_data 'ITAB'  'X' ' ' '' '' 'CHAR'  'LFIMG'  '实际发货量' .
  alv_add_data 'ITAB'  'X' ' ' '' '' 'CHAR' 'WQSL'    '未清数量'  .
  alv_add_data 'ITAB'  ' ' ' ' '' '' 'CHAR'  'RQCH'     '日期差'   .
  alv_add_data 'ITAB'  ' ' ' ' '' '' 'CHAR' 'OTTR'    'OTTR' .
  alv_add_data 'ITAB'  ' ' 'X' '' '' 'CHAR' 'YY'    '原因'  .

  alv_show_data itab.

ENDFORM.                    " SHOW_DATA

*
FORM set_pf_status USING rt_extab TYPE slis_t_extab.
  SET PF-STATUS 'ZSTANDARD' .
ENDFORM.                    "set_pf_status