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