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. 
|