REPORT ZTY_MM_0018 MESSAGE-ID zwwerrors.
*程序名称：包材分配表(ZTY_MM_0018)
*功能描述：自动分配包装材料库存
*程序建立日期:2004-09-14
*最后修改日期日期
perform print-header.
tables: makt, "物料管理
mkol, "供应商的特殊库存
mard, "物料的仓储位置数据
mara,
ekpo, "采购订单行项目
ekko, "采购订单表头
t023t, "物料组描述
eket, "计划协议计划行
equk, "配额文件: 标题
equp, "配额文件：项目
resb, "预定/相关需求
jest, "对象状态
aufk, "订单主数据
afpo, "订单项
lfa1. "供应商主数据
data: begin of item occurs 0,
matkl like ekpo-matkl, "物料组
WGBEZ like t023t-WGBEZ, "物料组描述
matnr like makt-matnr, "物料编码
maktx like makt-maktx, "物料描述
fullname(50) type c, "供应商组合
wponum like ekpo-menge, "供应商未清订单
wmenge like ekpo-menge, "供应商寄售库存
SLABS like mkol-SLABS, "包材库存
labst like mard-labst, "包料在线库存
lifnr like lfa1-lifnr, "供应商编码
groes like mara-groes, "大小/量纲
wqyh like ekpo-menge, "未清要货计划
fpjh like ekpo-menge, "分配计划
end of item.
data:struc like item.
data:itemc like item occurs 0 with header line.
data:begin of tekpo occurs 0,
ebeln like ekpo-ebeln, "采购订单号
ebelp like ekpo-ebelp, "行项目号
matnr like ekpo-matnr, "物料编码
menge like ekpo-menge, "订单数量
end of tekpo.
data:begin of tekko occurs 0,
ebeln like ekko-ebeln, "采购订单号
lifnr like ekko-lifnr, "供应商编号
end of tekko.
data flag type i value 0.
data:begin of teket occurs 0,
ebeln like eket-ebeln, "采购订单号
ebelp like eket-ebelp, "行项目号
wemng like eket-wemng, "数量
end of teket.
data:begin of twpo occurs 0,
ebeln like ekpo-ebeln, "采购订单号
ebelp like ekpo-ebelp, "行项目号
matnr like ekpo-matnr, "物料编码
menge like ekpo-menge, "订单数量
lifnr like ekko-lifnr, "供应商
end of twpo.
data:begin of twpo2 occurs 0,
matnr like ekpo-matnr, "物料编码
menge like ekpo-menge, "未清数量
lifnr like ekko-lifnr, "供应商编码
end of twpo2.
data:begin of titemp occurs 0,
matnr like ekpo-matnr, "物料编码
lifnr like ekko-lifnr, "供应商编码
wponum like ekpo-menge, "供应商未清订单
wmenge like ekpo-menge, "供应商寄售库存
slabs like mkol-slabs, "包材库存
labst like mard-labst, "包材在线库存
wqyh like ekpo-menge, "未清要货计划
fpjh like ekpo-menge, "分配计划
end of titemp.
constants matnrlow(18) type c value '000000000002300000'.
constants matnrhigh(18) type c value '000000000002399999'.
data year like sy-datum.
data cyear(4) type c.
data: begin of tmara occurs 0,
matnr like mara-matnr, "物料编码
matkl like mara-matkl, "物料组
groes like mara-groes, "大小/量纲
end of tmara.
SELECTION-SCREEN: BEGIN OF BLOCK FRAME01
WITH FRAME TITLE Text-001.
select-options s_lifnr for lfa1-lifnr.
select-options: s_matnr for makt-matnr.
select-options: s_matkl for t023t-matkl.
parameters p_werks like vbap-werks obligatory default '8000'.
select-options:s_SPART for mara-spart.
SELECTION-SCREEN: END OF BLOCK FRAME01.
SELECTION-SCREEN: BEGIN OF BLOCK FRAME02
WITH FRAME TITLE TEXT-002.
SELECTION-SCREEN COMMENT /5(68) TEXT-003.
SELECTION-SCREEN: END OF BLOCK FRAME02.
SELECTION-SCREEN: BEGIN OF BLOCK FRAME03
WITH FRAME TITLE TEXT-004.
SELECTION-SCREEN COMMENT /5(68) TEXT-005.
SELECTION-SCREEN: END OF BLOCK FRAME03.
start-of-selection.
year = sy-datum.
cyear = year+0(4).
*获取符合条件的记录
select * into corresponding fields of table tmara from mara
where mtart = 'ZVER' and matkl in s_matkl
and matnr in s_matnr and LVORM <> 'X' and spart in s_spart.
select * into corresponding fields of table tekpo from ekpo
where matnr in s_matnr and matkl in s_matkl
and loekz <> 'L' and matnr between matnrlow and matnrhigh
and werks = p_werks.
sort tekpo by ebeln ebelp.
select * into corresponding fields of table tekko from ekko
where loekz <> 'L' and lifnr in s_lifnr.
sort tekko by ebeln.
if not s_lifnr is initial.
flag = 0.
loop at tekpo.
loop at tekko where ebeln = tekpo-ebeln.
flag = 1.
exit.
endloop.
if flag = 0.
delete tekpo.
else.
flag = 0.
endif.
endloop.
endif.
*减去已清PO
loop at tekpo.
select * appending corresponding fields of table teket from eket
where ebeln = tekpo-ebeln and ebelp = tekpo-ebelp.
endloop.
sort teket by ebeln ebelp.
loop at tekpo.
loop at teket where ebeln = tekpo-ebeln and ebelp = tekpo-ebelp.
tekpo-menge = tekpo-menge - teket-wemng.
modify tekpo.
endloop.
endloop.
data: begin of tlif occurs 0,
lifnr like lfa1-lifnr, "供应商编号
end of tlif.
loop at tekpo.
twpo-ebeln = tekpo-ebeln.
twpo-ebelp = tekpo-ebelp.
twpo-matnr = tekpo-matnr.
twpo-menge = tekpo-menge.
loop at tekko where ebeln = tekpo-ebeln.
twpo-lifnr = tekko-lifnr.
exit.
endloop.
append twpo.
endloop.
*按物料,供应商编码合并记录
sort twpo by matnr lifnr.
read table twpo index 1.
twpo2-matnr = twpo-matnr.
twpo2-menge = 0.
twpo2-lifnr = twpo-lifnr.
loop at twpo.
if twpo-matnr = twpo2-matnr and twpo-lifnr = twpo2-lifnr.
twpo2-menge = twpo2-menge + twpo-menge.
else.
append twpo2.
twpo2-matnr = twpo-matnr.
twpo2-menge = twpo-menge.
twpo2-lifnr = twpo-lifnr.
endif.
endloop.
append twpo2.
sort tekko by lifnr.
read table tekko index 1.
tlif-lifnr = tekko-lifnr.
loop at tekko.
if tlif-lifnr <> tekko-lifnr.
append tlif.
tlif-lifnr = tekko-lifnr.
endif.
endloop.
append tlif.
sort tlif by lifnr.
*供应商寄售库存
data:begin of tmkol occurs 0,
matnr like mkol-matnr,
lifnr like mkol-lifnr,
slabs like mkol-slabs,
end of tmkol.
data:tmkol1 like tmkol occurs 0 with header line.
loop at tlif.
select * appending corresponding fields of table tmkol from mkol
where lifnr = tlif-lifnr
and lvorm <> 'X' and werks = p_werks.
endloop.
loop at tmkol.
tmkol1-matnr = tmkol-matnr.
tmkol1-lifnr = tmkol-lifnr.
tmkol1-slabs = tmkol-slabs.
collect tmkol1.
endloop.
loop at twpo2.
titemp-matnr = twpo2-matnr.
titemp-lifnr = twpo2-lifnr.
titemp-wponum = twpo2-menge.
append titemp.
endloop.
flag = 0.
loop at tmkol1.
loop at titemp where matnr = tmkol1-matnr and lifnr = tmkol1-lifnr.
titemp-wmenge = tmkol1-slabs.
modify titemp.
flag = 1.
endloop.
if flag = 1.
delete tmkol1.
flag = 0.
endif.
endloop.
if not tmkol1[] is initial.
loop at tmkol1.
titemp-matnr = tmkol1-matnr.
titemp-lifnr = tmkol1-lifnr.
titemp-wmenge = tmkol1-slabs.
append titemp.
endloop.
endif.
data:begin of tmard occurs 0,
matnr like mard-matnr, "物料编码
labst like mard-labst, "非限制库存
end of tmard.
data:begin of mainmard occurs 0,
matnr like mard-matnr,
bckc like mard-labst,
zxkc like mard-labst,
end of mainmard.
data:finmard like mainmard occurs 0 with header line.
data:onlinemard like tmard occurs 0 with header line.
select * into corresponding fields of table tmard from mard
where matnr between matnrlow and matnrhigh
and ( lgort = '0040' or lgort = '6040' )
and labst <> 0 and werks = p_werks.
select * into corresponding fields of table onlinemard from mard
where matnr between matnrlow and matnrhigh
and ( lgort = '0041' or lgort = '6041' )
and labst <> 0 and werks = p_werks.
loop at tmard.
mainmard-matnr = tmard-matnr.
mainmard-bckc = tmard-labst.
mainmard-zxkc = 0.
append mainmard.
endloop.
loop at onlinemard.
mainmard-matnr = onlinemard-matnr.
mainmard-bckc = 0.
mainmard-zxkc = onlinemard-labst.
append mainmard.
endloop.
loop at mainmard.
finmard-matnr = mainmard-matnr.
finmard-bckc = mainmard-bckc.
finmard-zxkc = mainmard-zxkc.
collect finmard.
endloop.
if not finmard[] is initial.
flag = 0.
loop at finmard.
loop at titemp where matnr = titemp-matnr.
titemp-slabs = finmard-bckc.
titemp-labst = finmard-zxkc.
modify titemp.
flag = 1.
endloop.
if flag = 1.
delete finmard.
flag = 0.
endif.
endloop.
endif.
if not finmard[] is initial.
loop at finmard.
titemp-matnr = finmard-matnr.
titemp-slabs = finmard-bckc.
titemp-labst = finmard-zxkc.
append titemp.
endloop.
endif.
*未清要货计划
data:begin of tekes occurs 0,
ebeln like ekes-ebeln,
ebelp like ekes-ebelp,
menge like ekes-menge,
dabmg like ekes-dabmg,
end of tekes.
data:begin of twqsl occurs 0,
matnr like ekpo-matnr,
lifnr like ekko-lifnr,
wqs like ekes-menge,
end of twqsl.
select * into corresponding fields of table tekes from ekes
where eindt = sy-datum.
loop at tekes.
select single matnr from ekpo into twqsl-matnr where
ebeln = tekes-ebeln and ebelp = tekes-ebelp.
select single lifnr from ekko into twqsl-lifnr where
ebeln = tekes-ebeln.
twqsl-wqs = tekes-menge - tekes-dabmg.
collect twqsl.
endloop.
flag = 0.
loop at twqsl.
loop at titemp where matnr = twqsl-matnr and lifnr = twqsl-lifnr.
titemp-wqyh = twqsl-wqs.
modify titemp.
flag = 1.
endloop.
if flag = 1.
delete twqsl.
flag = 0.
endif.
endloop.
if not twqsl[] is initial.
titemp-matnr = twqsl-matnr.
titemp-lifnr = twqsl-lifnr.
titemp-wqyh = twqsl-wqs.
append titemp.
endif.
*包材分配计划
data:begin of tresb occurs 0,
matnr like resb-matnr,
bdmng like resb-bdmng,
aufnr like resb-aufnr,
end of tresb.
select * into corresponding fields of table tresb from resb
where kzear <> 'X' and xloek <> 'X' and bdart = 'AR'
and matnr between matnrlow and matnrhigh
and bdter = sy-datum.
* tj02t表,汉字描述
data:begin of taufk occurs 0,
aufnr like aufk-aufnr,
objnr like aufk-objnr,
stat like jest-stat,
end of taufk.
loop at tresb.
select single aufnr objnr from aufk into (taufk-aufnr,taufk-objnr)
where aufnr = tresb-aufnr.
select single stat from jest into taufk-stat
where objnr = taufk-objnr and inact <> 'X'.
append taufk.
endloop.
loop at taufk.
if taufk-stat = 'I0009'.
delete taufk.
endif.
endloop.
flag = 0.
loop at tresb.
loop at taufk where aufnr = tresb-aufnr.
flag = 1.
exit.
endloop.
if flag = 0.
delete tresb.
else.
flag = 0.
endif.
endloop.
data:begin of tequk occurs 0,
matnr like equk-matnr,
qunum like equk-qunum,
end of tequk.
select * into corresponding fields of table tequk from equk
where matnr between matnrlow and matnrhigh
and vdatu <= sy-datum and bdatu >= sy-datum.
data:begin of tequp occurs 0,
qunum like equp-qunum,
lifnr like equp-lifnr,
quote like equp-quote,
end of tequp.
loop at tequk.
select * appending corresponding fields of table tequp from equp
where qunum = tequk-qunum.
endloop.
data:begin of tpe occurs 0,
matnr like equk-matnr,
lifnr like equp-lifnr,
quote like equp-quote,
end of tpe.
loop at tequp.
tpe-lifnr = tequp-lifnr.
tpe-quote = tequp-quote.
loop at tequk where qunum = tequp-qunum.
tpe-matnr = tequk-matnr.
exit.
endloop.
append tpe.
endloop.
data:begin of tfp occurs 0,
matnr like makt-matnr,
lifnr like ekko-lifnr,
menge like resb-bdmng,
end of tfp.
loop at tpe.
tfp-matnr = tpe-matnr.
tfp-lifnr = tpe-lifnr.
loop at tresb where matnr = tpe-matnr.
tfp-menge = tresb-bdmng * tpe-quote / 100.
endloop.
append tfp.
endloop.
loop at titemp.
loop at tfp where matnr = titemp-matnr and lifnr = titemp-lifnr.
titemp-fpjh = tfp-menge.
modify titemp.
exit.
endloop.
endloop.
flag = 0.
loop at titemp.
loop at tmara where matnr = titemp-matnr.
flag = 1.
exit.
endloop.
if flag = 0.
delete titemp.
else.
flag = 0.
endif.
endloop.
data:name111 like lfa1-name1.
loop at titemp.
item-matnr = titemp-matnr.
select single maktx from makt into item-maktx
where matnr = titemp-matnr and spras = '1'.
loop at tmara where matnr = titemp-matnr.
item-matkl = tmara-matkl.
item-groes = tmara-groes.
exit.
endloop.
select single WGBEZ from t023t into item-wgbez
where SPRAS = '1' and matkl = item-matkl.
item-lifnr = titemp-lifnr.
select single name1 from lfa1 into name111
where lifnr = item-lifnr.
SHIFT item-lifnr LEFT DELETING LEADING '0'.
concatenate name111 '(' item-lifnr ')' into item-fullname.
item-wponum = titemp-wponum.
item-wmenge = titemp-wmenge.
item-slabs = titemp-slabs.
item-labst = titemp-labst.
item-wqyh = titemp-wqyh.
item-fpjh = titemp-fpjh.
append item.
endloop.
if item[] is initial.
message E001.
endif.
sort item by matnr lifnr.
read table item index 1.
struc-matkl = item-matkl.
struc-wgbez = item-wgbez.
struc-matnr = item-matnr.
struc-maktx = item-maktx.
struc-fullname = item-fullname.
struc-lifnr = item-lifnr.
struc-wponum = 0.
struc-wmenge = 0.
struc-slabs = 0.
struc-labst = 0.
struc-lifnr = item-lifnr.
struc-groes = item-groes.
struc-wqyh = 0.
struc-fpjh = 0.
loop at item.
if struc-matnr = item-matnr.
itemc-matkl = item-matkl.
itemc-wgbez = item-wgbez.
itemc-matnr = item-matnr.
itemc-maktx = item-maktx.
itemc-fullname = item-fullname.
itemc-lifnr = item-lifnr.
itemc-wponum = item-wponum.
itemc-wmenge = item-wmenge.
itemc-slabs = item-slabs.
itemc-labst = item-labst.
itemc-lifnr = item-lifnr.
itemc-groes = item-groes.
itemc-wqyh = item-wqyh.
itemc-fpjh = item-fpjh.
append itemc.
struc-matkl = item-matkl.
struc-wgbez = '小计'.
struc-matnr = item-matnr.
struc-maktx = item-maktx.
struc-wponum = struc-wponum + item-wponum.
struc-wmenge = struc-wmenge + item-wmenge.
struc-slabs = struc-slabs + item-slabs.
struc-labst = struc-labst + item-labst.
struc-groes = item-groes.
struc-wqyh = struc-wqyh + item-wqyh.
struc-fpjh = struc-fpjh + item-fpjh.
else.
append struc to itemc.
itemc-matkl = item-matkl.
itemc-wgbez = item-wgbez.
itemc-matnr = item-matnr.
itemc-maktx = item-maktx.
itemc-fullname = item-fullname.
itemc-lifnr = item-lifnr.
itemc-wponum = item-wponum.
itemc-wmenge = item-wmenge.
itemc-slabs = item-slabs.
itemc-labst = item-labst.
itemc-lifnr = item-lifnr.
itemc-groes = item-groes.
itemc-wqyh = item-wqyh.
itemc-fpjh = item-fpjh.
append itemc.
struc-matkl = item-matkl.
struc-wgbez = item-wgbez.
struc-matnr = item-matnr.
struc-maktx = item-maktx.
struc-wponum = item-wponum.
struc-wmenge = item-wmenge.
struc-slabs = item-slabs.
struc-labst = item-labst.
struc-groes = item-groes.
struc-wqyh = item-wqyh.
struc-fpjh = item-fpjh.
endif.
endloop.
append struc to itemc.
loop at itemc where wgbez = '小计'.
itemc-matkl = ''.
itemc-fullname = ''.
itemc-lifnr = ''.
modify itemc.
endloop.
if itemc[] is initial.
message E001.
endif.
end-of-selection.
PERFORM ole_excel
TABLES itemc
USING 'd:\sapdata\ty_mm_0018\ty_mm_0018.xls'.
FORM ole_excel
TABLES itab
USING appl_file_name TYPE string.
INCLUDE ole2incl.
DATA: excel TYPE ole2_object.
DATA: books TYPE ole2_object.
DATA: range TYPE ole2_object.
DATA: book TYPE ole2_object.
DATA: filename LIKE rcgfiletr-ftfront.
DATA: appl_file_path TYPE string.
DATA: appl_filename LIKE rcgfiletr-ftfront.
filename = 'd:\sapdata\ty_mm_0018\ty_mm_0018.xls'.
appl_file_path = 'd:\sapdata\ty_mm_0018\'.
CALL FUNCTION 'WS_DOWNLOAD'
EXPORTING
filename = 'd:\sapdata\ty_mm_0018\itab.dat'
filetype = 'DAT'
TABLES
data_tab = itab.
FREE itab.
CONCATENATE appl_file_path appl_file_name INTO appl_filename.
CALL FUNCTION 'C13Z_FILE_DOWNLOAD_BINARY'
EXPORTING
i_file_front_end = filename
i_file_appl = appl_filename
i_file_overwrite = 'X'.
CALL FUNCTION 'WS_EXECUTE'
EXPORTING
document = 'X'
COMMANDLINE = filename
PROGRAM = 'EXCEL'.
ENDFORM.
FORM print-header.
WRITE: / 'Program started by', SY-UNAME,
/ 'on host', SY-HOST,
'date:', SY-DATUM, 'time:', SY-UZEIT,
/ '程序名称：包材分配表'.
ULINE.
ENDFORM.
