*&---------------------------------------------------------------------*
*& REPORT YPMxxxxxxxx *
*& 表名：。。。。。。。。。统计表 *
*&---------------------------------------------------------------------*
*& AUTHOR：
*& DATE：
*&---------------------------------------------------------------------*
REPORT YPMxxxxxxxx .
TABLES: aufk,t001w,ktext, ……[微软系统1]
****定义**取数内表***及其包括的元素***
aufnr like aufk-aufnr, "工单
ktext like aufk-ktext, "工单描述
……
end of wa_qs.
data: itab type standard table of wa_qs with header line.
data: itab_gcms type standard table of wa_qs with header line.
data: itab_ygfy type standard table of wa_qs with header line.
data: itab1 type standard table of wa_qs with header line.
data: itab2 type standard table of wa_qs with header line.
。。。 。。。
***************************ALV定义**************************
* TYPE-POOLS: SLIS. “声明：ALV使用到的类库
data: ws_events type slis_t_event,
i_grid_title type lvc_title value '施工供应商结算情况统计表',
"ALV抬头名称
it_fieldcat type slis_t_fieldcat_alv,
"ALV输出内表
wa_fieldcat type line of slis_t_fieldcat_alv,
"ALV输出表结构
is_layout type slis_layout_alv.
"ALV输出方式
***********************execl 声明**********************************
type-pools ole2.
data: excel type ole2_object, "EXCEL
sheet type ole2_object, "工作薄
cell type ole2_object, "单元格
workbook type ole2_object, "工作空间
h_zl type ole2_object, " cell
h_f type ole2_object, " font
h_s type ole2_object, " SHEET
h_r type ole2_object. " RANG
*****************选择屏幕**************
selection-screen begin of block panel with frame title text-001.
select-options: w_erdat for aufk-erdat. "工单创建时间
select-options: w_budat for essr-budat obligatory."工单结算时间
select-options: w_udate for cdhdr-udate. "采购订单的下达时间
……
selection-screen end of block panel.
selection-screen begin of block b1 with frame title text-002.
selection-screen skip.
parameters out_alv radiobutton group z user-command f default 'X'.
parameters out_exe radiobutton group z.
selection-screen begin of block b3 with frame title text-003.
selection-screen skip.
parameters p_fname(128) obligatory default
'D:\WORK\施工供应商结算情况统计表.XLS' modif id bl3.
selection-screen end of block b3.
selection-screen end of block b1.
at selection-screen output.
loop at screen.
case screen-group1.
when 'BL3' .
if out_exe = 'X'. "操作时bl3选择屏幕激活
screen-active = '1'.
else.
screen-active = '0'."否则不激活
endif.
endcase.
modify screen.
endloop.
at selection-screen on value-request for p_fname.
call function 'WS_FILENAME_GET'
exporting
def_filename = ' '
def_path = ' '
mask = ',*.* ,*.*. '
mode = 'O'
title = ' '
importing
filename = p_fname
exceptions
inv_winsys = 1
no_batch = 2
selection_cancel = 3
selection_error = 4
others = 5.
start-of-selection.
if w_swerk-low is not initial.
if w_swerk-low(1) <> 5 .
message '请输入正确维护工厂' type 'S'.
stop.
endif.
endif.
if w_swerk-high is not initial.
if w_swerk-high(1) <> 5 .
message '请输入正确维护工厂' type 'S'.
stop.
endif.
endif.
*******ALV输出定义宏*********
define set_outtab.
clear wa_fieldcat.
wa_fieldcat-fieldname = &1. "字段名 'XXXX'
wa_fieldcat-seltext_l = &2. "列标题 'XXXXX'
wa_fieldcat-key = &3. "是否关键字 'X'
wa_fieldcat-checkbox = &4. "是否选择框 'X'
wa_fieldcat-edit = &5. "是否可编辑 'X'
wa_fieldcat-emphasize = &6. "列底色(如:'C611' 6-red)
append wa_fieldcat to it_fieldcat.
clear wa_fieldcat.
end-of-definition.
if out_alv = 'X'.
perform alv_put.
elseif out_exe = 'X'.
perform out_exe.
endif.
end-of-selection.
*&---------------------------------------------------------------------*
*& Form GET_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form get_data.
*****取工单号，工单描述，维护工厂,作业类型,工厂描述****
select
aufk~aufnr "工单号
aufk~ktext "工单描述
aufk~auart "工单类型
aufk~sowrk "维护工厂
afko~aufpl "订单中工序的工艺路线号
afih~ilart "作业类型
t001w~name1 "工厂描述
aufk~erdat "工单创建时间
from aufk inner join afko on aufk~aufnr = afko~aufnr
inner join afih on aufk~aufnr = afih~aufnr
inner join t001w on aufk~sowrk = t001w~werks
into corresponding fields of table itab
where aufk~werks = '5061'
and aufk~sowrk in w_swerk
and aufk~erdat in w_erdat
and aufk~loekz = ''
and afih~ilart in w_ilart.
*****判断输入条件***************
if itab[] is initial .
message '没有查到维护工厂和订单创建时间,作业类型对应的工单数据' type 'S'.
stop.
endif.
******资金来源********
loop at itab.
if itab-auart = 'PM14'.
itab-zjly = '生产费'.
elseif itab-auart = 'PM18'.
itab-zjly = '管理费'.
elseif ( itab-auart = 'PM11' or itab-auart = 'PM12' or itab-auart = 'PM13'
or itab-auart = 'PM15' or itab-auart = 'PM16' or itab-auart = 'PM17' ).
itab-zjly = '修理费'.
modify itab.
endif.
clear itab.
endloop.
****取采购申请号，采购申请行项目**********
select
afvc~banfn "采购申请号
afvc~bnfpo "采购申请行项目
afvc~aufpl "订单中工序的工艺路线号
afvc~packno "软件包编号
afvc~steus "控制码
* AFVC~PHFLG "标志：阶段
into corresponding fields of table itab1
from afvc
for all entries in itab
where afvc~aufpl = itab-aufpl
and afvc~steus = 'PM03'.
* AND AFVC~PHFLG = ''.
****************删除已删除的采购申请*********
sort itab1 by banfn.[微软系统7]
data:temp like eban-loekz.
loop at itab1.
select single loekz into temp
from eban
where eban~banfn = itab1-banfn
and eban~bnfpo = itab1-bnfpo
and eban~loekz ne ''.
if sy-subrc eq '0'.
delete itab1.
endif.
clear itab1.
endloop.
***************判断输入条件***************
if itab1[] is initial .
message '所查工单没有形成采购审请' type 'S'.
stop.
endif.
*******取预估费用*************
select
esuh~packno
esuh~commitment
into corresponding fields of table itab_ygfy
from esuh
for all entries in itab1
where esuh~packno = itab1-packno
and esuh~commitment in w_comm.
if w_comm-low is initial and w_comm is initial.
loop at itab1.
read table itab_ygfy with key packno = itab1-packno.
if sy-subrc eq '0'.
itab1-commitment = itab_ygfy-commitment.
modify itab1.
endif.
clear itab1.
endloop.
elseif w_comm-low is not initial or w_comm is not initial.
loop at itab1.
read table itab_ygfy with key packno = itab1-packno.
if sy-subrc eq '0'.
itab1-commitment = itab_ygfy-commitment.
modify itab1.
elseif sy-subrc ne '0'.
delete itab1.
endif.
clear itab1.
endloop.
endif.
***************判断输入条件***************
if itab1[] is initial .
message '没有查到预估费用对应的工单数据' type 'S'.
stop.
endif.
********取采购订单号和采购订单行项目号*********
loop at itab1.
select single ebeln ebelp into (itab1-ebeln,itab1-ebelp)
from eban inner join ebkn on eban~banfn = ebkn~banfn
and eban~bnfpo = ebkn~bnfpo
where eban~banfn = itab1-banfn
and eban~bnfpo = itab1-bnfpo
and ebkn~loekz = ''.
if sy-subrc eq '0'.
modify itab1.
elseif sy-subrc ne '0'.
delete itab1.
endif.
clear itab1.
endloop.
***************判断输入条件***************
if itab1[] is initial .
message '没有查到您所需数据' type 'S'.
stop.
endif.
*********取供应商信息********
select
ekko~lifnr
ekko~ebeln
lfa1~name1
lfa1~name2
lfa1~name3
ekpo~ean11
into corresponding fields of table itab_gysxx
from ekko inner join lfa1 on ekko~lifnr = lfa1~lifnr
inner join ekpo on ekko~ebeln = ekpo~ebeln
for all entries in itab1
where ekko~ebeln = itab1-ebeln
and ekko~lifnr in w_lifnr.
if w_lifnr-low is initial and w_lifnr-high is initial.
loop at itab1.
read table itab_gysxx with key ebeln = itab1-ebeln.
if sy-subrc eq '0'.
concatenate itab_gysxx-name1 itab_gysxx-name2 itab_gysxx-name3 into itab1-name.
itab1-lifnr = itab_gysxx-lifnr.
itab1-ean11 = itab_gysxx-ean11.
modify itab1.
endif.
clear itab1.
endloop.
elseif w_lifnr-low is not initial or w_lifnr-high is not initial.
loop at itab1.
read table itab_gysxx with key ebeln = itab1-ebeln.
if sy-subrc eq '0'.
concatenate itab_gysxx-name1 itab_gysxx-name2 itab_gysxx-name3 into itab1-name.
itab1-lifnr = itab_gysxx-lifnr.
itab1-ean11 = itab_gysxx-ean11.
modify itab1.
elseif sy-subrc ne '0'.
delete itab1.
endif.
clear itab1.
endloop.
endif.
***************判断输入条件***************
if itab1[] is initial .
message '没有查到供应商对应的工单数据' type 'S'.
stop.
endif.
**********结算金额和结算日期***********
select
essr~budat
essr~netwr
essr~ebeln
into corresponding fields of table itab_jsxx
from essr
for all entries in itab1
where essr~ebeln = itab1-ebeln
and essr~budat in w_budat.
loop at itab1.
read table itab_jsxx with key ebeln = itab1-ebeln.
if sy-subrc eq '0'.
itab1-budat1 = itab_jsxx-budat.
itab1-netwr = itab_jsxx-netwr.
modify itab1.
elseif sy-subrc ne '0'.
delete itab1.
endif.
clear itab1.
endloop.
***************判断输入条件***************
if itab1[] is initial .
message '没有查到结算日期对应的工单数据' type 'S'.
stop.
endif.
*********发票信息*************
select
rseg~ebeln
rseg~ebelp
rseg~wrbtr
rseg~shkzg
rseg~belnr
rseg~gjahr
into corresponding fields of table itab_fpxx
from rseg
for all entries in itab1
where rseg~ebeln = itab1-ebeln
and rseg~ebelp = itab1-ebelp
and rseg~bukrs = '5000'
and rseg~werks = '5061'.
select
rbkp~belnr
rbkp~gjahr
rbkp~budat
into corresponding fields of table itab_fpsj
from rbkp
for all entries in itab_fpxx
where rbkp~belnr = itab_fpxx-belnr
and rbkp~gjahr = itab_fpxx-gjahr
and rbkp~stblg = ''.
loop at itab_fpxx.
if itab_fpxx-shkzg = 'S'.
itab_fpxx-wrbtr = itab_fpxx-wrbtr.
elseif itab_fpxx-shkzg = 'H'.
itab_fpxx-wrbtr = 0 - itab_fpxx-wrbtr.
modify itab_fpxx.
endif.
read table itab_fpsj with key belnr = itab_fpxx-belnr gjahr = itab_fpxx-gjahr.
if sy-subrc eq '0'.
itab_fpxx-budat = itab_fpsj-budat.
modify itab_fpxx.
endif.
clear itab_fpxx.
endloop.
***********整理发票信息
loop at itab1.
read table itab_fpxx with key ebeln = itab1-ebeln ebelp = itab1-ebelp.
if sy-subrc eq '0'.
itab1-wrbtr = itab_fpxx-wrbtr.
itab1-budat2 = itab_fpxx-budat.
modify itab1.
endif.
clear itab1.
endloop.
***********采购定单的下达时间***********
loop at itab1.
itab1-objectid = itab1-ebeln.
modify itab1.
clear itab1.
endloop.
select
cdhdr~objectid
cdhdr~udate
cdhdr~changenr
into corresponding fields of table itab_ddxdsj
from cdhdr
for all entries in itab1
where cdhdr~objectclas = 'EINKBELEG'
and ( cdhdr~tcode = 'ME29N' or cdhdr~tcode = 'ME28N' )
and cdhdr~langu = '1'
and cdhdr~objectid = itab1-objectid
and cdhdr~udate in w_udate.
loop at itab_ddxdsj.
select single objectid into itab_ddxdsj1
from cdpos where objectclas = 'EINKBELEG'
and objectid = itab_ddxdsj-objectid
and changenr = itab_ddxdsj-changenr
and tabname = 'EKKO'
and fname = 'FRGKE'
and value_new = 'R'.
if sy-subrc ne '0'.
delete itab_ddxdsj.
endif.
clear itab_ddxdsj.
endloop.
if w_udate-low is not initial or w_udate-low is not initial.
loop at itab1.
read table itab_ddxdsj with key objectid = itab1-ebeln.
if sy-subrc eq '0'.
itab1-udate = itab_ddxdsj-udate.
modify itab1.
elseif sy-subrc ne '0'.
delete itab1.
endif.
clear itab1.
endloop.
elseif w_udate-low is initial and w_udate-low is initial.
loop at itab1.
read table itab_ddxdsj with key objectid = itab1-ebeln.
if sy-subrc eq '0'.
itab1-udate = itab_ddxdsj-udate.
modify itab1.
endif.
clear itab1.
endloop.
endif.
***************判断输入条件***************
if itab1[] is initial .
message '没有查到采购订单下达时间对应的工单数据' type 'S'.
stop.
endif.
*********合并输出***********
loop at itab1.
read table itab with key aufpl = itab1-aufpl.
if sy-subrc eq '0'.
itab1-aufnr = itab-aufnr.
itab1-ktext = itab-ktext.
itab1-sowrk = itab-sowrk.
itab1-zjly = itab-zjly.
itab1-ilart = itab-ilart.
itab1-name1 = itab-name1.
modify itab1.
endif.
clear itab1.
endloop.
sort itab1 by aufnr.
endform. " GET_DATA
*&---------------------------------------------------------------------*
*& Form ALV_PUT
*&---------------------------------------------------------------------*
* text “控制输出的内容”
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form alv_put.
set_outtab 'AUFNR' '工单' '' '' '' ''.
set_outtab 'KTEXT' '工单描述' '' '' '' ''.
set_outtab 'ILART' '作业类型' '' '' '' ''.
set_outtab 'SOWRK' '维护工厂' '' '' '' ''.
set_outtab 'NAME1' '工厂描述' '' '' '' ''.
set_outtab 'ZJLY' '资金来源' '' '' '' ''.
set_outtab 'BANFN' '采购申请' '' '' '' ''.
set_outtab 'BNFPO' '采购申请行项目' '' '' '' ''.
set_outtab 'EBELN' '采购订单' '' '' '' ''.
set_outtab 'EBELP' '采购订单行项目' '' '' '' ''.
set_outtab 'LIFNR' '供应商' '' '' '' ''.
set_outtab 'NAME' '供应商描述' '' '' '' ''.
set_outtab 'EAN11' '二级供应商' '' '' '' ''.
set_outtab 'UDATE' '采购订单审批下达时间' '' '' '' ''.
set_outtab 'COMMITMENT' '预估费用' '' '' '' ''.
set_outtab 'NETWR' '结算' '' '' '' ''.
set_outtab 'BUDAT1' '结算时间' '' '' '' ''.
set_outtab 'WRBTR' '发票校验金额' '' '' '' ''.
set_outtab 'BUDAT2' '发票校验时间' '' '' '' ''.
is_layout-zebra = 'X'. "使显示的颜色成间隔斑马纹颜色效果
call function 'REUSE_ALV_GRID_DISPLAY' "调用显示功能模块
i_callback_program = sy-repid
i_grid_title = i_grid_title
is_layout = is_layout
it_fieldcat = it_fieldcat
it_events = ws_events[]
tables
t_outtab = itab1 "要输出的内表
exceptions
program_error = 1
others = 2 .
[微软系统8] if sy-subrc <> 0.
message 'ALV 调用出错！' type 'S'.
stop.
endif.
endform. " ALV_PUT
*&---------------------------------------------------------------------*
*& Form frm_out_data “输出数据”
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form frm_out_data .
data: l_line type slis_listheader.
data hinfo type slis_t_listheader.
data fieldcat type slis_t_fieldcat_alv with header line.
data layout type slis_layout_alv .
data sortable type slis_t_sortinfo_alv with header line.
*-------Field List Table Setting--------
clear fieldcat.
fieldcat-fieldname = 'INDEX '.
fieldcat-just = 'R'.
fieldcat-seltext_l = '序号'.
fieldcat-outputlen = '6'.
append fieldcat.
clear fieldcat.
fieldcat-fieldname = 'AUFNR '.
fieldcat-just = 'L'.
fieldcat-seltext_l = '工单号'.
fieldcat-outputlen = '12'.
append fieldcat.
clear fieldcat.
fieldcat-fieldname = 'KTEXT '.
fieldcat-just = 'L'.
fieldcat-seltext_l = '工单描述'.
fieldcat-outputlen = '20'.
append fieldcat.
clear fieldcat.
fieldcat-fieldname = 'AUART '.
fieldcat-just = 'L'.
fieldcat-seltext_l = '工单类型'.
fieldcat-outputlen = '8'.
append fieldcat.
clear fieldcat.
fieldcat-fieldname = 'TXT04 '.
fieldcat-just = 'L'.
fieldcat-seltext_l = '工单状态'.
fieldcat-outputlen = '8'.
append fieldcat.
clear fieldcat.
fieldcat-fieldname = 'ILART '.
fieldcat-just = 'L'.
fieldcat-seltext_l = '作业类型'.
fieldcat-outputlen = '8'.
append fieldcat.
clear fieldcat.
fieldcat-fieldname = 'MENGE_PR '.
fieldcat-just = 'R'.
fieldcat-seltext_l = '采购申请数量'.
fieldcat-outputlen = '12'.
append fieldcat.
clear fieldcat.
fieldcat-fieldname = 'MENGE_PO '.
fieldcat-just = 'R'.
fieldcat-seltext_l = '采购订单数量'.
fieldcat-outputlen = '12'.
append fieldcat.
clear fieldcat.
fieldcat-fieldname = 'MENGE_PO_J '.
fieldcat-just = 'R'.
fieldcat-seltext_l = '采购订单结算数量'.
fieldcat-outputlen = '18'.
append fieldcat.
clear fieldcat.
fieldcat-fieldname = 'COMMITMENT '.
fieldcat-just = 'R'.
fieldcat-seltext_l = '委托金额'.
fieldcat-outputlen = '15'.
append fieldcat.
clear fieldcat.
fieldcat-fieldname = 'NETWR_PO '.
fieldcat-just = 'R'.
fieldcat-seltext_l = '采购订单金额'.
fieldcat-outputlen = '15'.
append fieldcat.
clear fieldcat.
fieldcat-fieldname = 'NETWR '.
fieldcat-just = 'R'.
fieldcat-seltext_l = '结算金额'.
fieldcat-outputlen = '15'.
append fieldcat.
clear fieldcat.
fieldcat-fieldname = 'TCOST '.
fieldcat-just = 'R'.
fieldcat-seltext_l = '工单的实际成本 '.
fieldcat-outputlen = '16'.
append fieldcat.
clear fieldcat.
fieldcat-fieldname = 'WKGBTR '.
fieldcat-just = 'R'.
fieldcat-seltext_l = '成本结转'.
fieldcat-outputlen = '15'.
append fieldcat.
clear fieldcat.
fieldcat-fieldname = 'TXT04_X '.
fieldcat-just = 'L'.
fieldcat-seltext_l = '完成状态'.
fieldcat-outputlen = '8'.
append fieldcat.
clear fieldcat.
fieldcat-fieldname = 'MSG '.
fieldcat-just = 'L'.
fieldcat-outputlen = '500'.
fieldcat-seltext_l = '提示'.
append fieldcat.
*----------------------------------------
*------------------------------------begin of layout
* layout-colwidth_optimize = 'X'.
layout-zebra = 'X'.
layout-box_fieldname = 'SEL'.
*--------------------------------------end of layout
*---------------Begin of SortTable info
*-----------------End of SortTable Info
call function 'REUSE_ALV_GRID_DISPLAY'
exporting
i_interface_check = ''
i_callback_program = sy-repid
is_layout = layout
i_callback_pf_status_set = 'SET_PF_STATUS'
i_callback_user_command = 'USER_COMMAND'
it_fieldcat = fieldcat[]
*--------这里是动态嵌入的代码，用于ALV控制头部和尾部数据
*---------------动态代码结束
tables
t_outtab = gt_data
exceptions
program_error = 1
others = 2.
endform. " frm_out_data
浙公网安备 33010602011771号