*&---------------------------------------------------------------------* *& *&---------------------------------------------------------------------* *& *& *&---------------------------------------------------------------------* REPORT ZMMF016. TABLES:EINE,EINA,SSCRFIELDS,A017. INCLUDE OLE2INCL. TYPES:BEGIN OF TY_EXCEL, SEL(1), ICON(4), MESSAGE(100), LOHNB(1),"信息记录类型(S:标准；W：委外) INFNR TYPE EINA-INFNR, LIFNR TYPE LFA1-LIFNR, MATNR TYPE MARA-MATNR, EKORG TYPE EINE-EKORG, WERKS TYPE EINE-WERKS, EKGRP TYPE EINE-EKGRP, MWSKZ TYPE EINE-MWSKZ, APLFZ(3) TYPE C, " EINE-APLFZ, WAERS TYPE EINE-WAERS, MEINS TYPE EINA-MEINS, UMREZ(5) TYPE C, " TYPE EINA-UMREZ, UMREN(5) TYPE C, " TYPE EINA-UMREN, NETPR(11) TYPE C, "EINE-NETPR, LIFAB TYPE EINA-LIFAB, LIFBI TYPE EINA-LIFBI, KPEIN(5) TYPE C, "KONP-KPEIN, * NORBM TYPE EINE-NORBM, END OF TY_EXCEL. TYPES:BEGIN OF TY_CHECK, INFNR TYPE EINA-INFNR, LIFNR TYPE LFA1-LIFNR, MATNR TYPE MARA-MATNR, LIFAB TYPE EINA-LIFAB, EKORG TYPE EINE-EKORG, WERKS TYPE EINE-WERKS, ESOKZ TYPE EINE-ESOKZ, END OF TY_CHECK. DATA:GT_EXCEL TYPE TABLE OF TY_EXCEL, GW_EXCEL LIKE LINE OF GT_EXCEL, GT_CHECK TYPE TABLE OF TY_CHECK, GW_CHECK LIKE LINE OF GT_CHECK. DATA:GT_FIELDCAT TYPE LVC_T_FCAT. DATA GS_FIELDCAT LIKE LINE OF GT_FIELDCAT. DATA LS_LAYOUT TYPE LVC_S_LAYO. DATA : GT_EXCEL_T TYPE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE, GS_EXCEL_T LIKE LINE OF GT_EXCEL_T. DATA: BDCDATA LIKE BDCDATA OCCURS 0 WITH HEADER LINE. DATA: MESSTAB LIKE BDCMSGCOLL OCCURS 0 WITH HEADER LINE. DATA: GS_CTU_PARAMS TYPE CTU_PARAMS. DATA: GS_EXCEL TYPE OLE2_OBJECT, * gs_wbooklist TYPE ole2_object, * gs_application TYPE ole2_object, GS_WORKBOOK TYPE OLE2_OBJECT. DEFINE APPEND_FIELDCAT . CLEAR GS_FIELDCAT. GS_FIELDCAT-FIELDNAME = &1. GS_FIELDCAT-SCRTEXT_L = &2. GS_FIELDCAT-OUTPUTLEN = &3. GS_FIELDCAT-NO_ZERO = &4. * IF &1 = 'MEINS'. * GS_FIELDCAT-CONVEXIT = 'ZCONV'. * ENDIF. APPEND GS_FIELDCAT TO GT_FIELDCAT. END-OF-DEFINITION. SELECTION-SCREEN:BEGIN OF BLOCK BLK01 WITH FRAME TITLE TEXT-001. PARAMETERS:P_FILE LIKE RLGRAP-FILENAME. SELECTION-SCREEN END OF BLOCK BLK01. SELECTION-SCREEN FUNCTION KEY 1. INITIALIZATION. MOVE '@49@下载模板' TO SSCRFIELDS-FUNCTXT_01. AT SELECTION-SCREEN. IF SSCRFIELDS-UCOMM = 'FC01'. PERFORM PRM_SAVE_FILE. ENDIF. AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE. PERFORM FRM_GET_FILEPATH. START-OF-SELECTION. CHECK P_FILE IS NOT INITIAL. PERFORM PRM_GET_EXCEL. PERFORM PRM_SET_FIELD. PERFORM PRM_CHECK_DATA. PERFORM PRM_SHOW. *&---------------------------------------------------------------------* *& Form FRM_GET_FILEPATH *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM FRM_GET_FILEPATH . CALL FUNCTION 'WS_FILENAME_GET' EXPORTING MASK = ',Excel(*.xls),*.XLS,*.XLSX,' TITLE = '选择文件'(100) IMPORTING FILENAME = P_FILE EXCEPTIONS INV_WINSYS = 1 NO_BATCH = 2 SELECTION_CANCEL = 3 SELECTION_ERROR = 4 OTHERS = 5. IF SY-SUBRC <> 0. MESSAGE '选择文件出错！' TYPE 'S' DISPLAY LIKE 'E'. LEAVE LIST-PROCESSING. ENDIF. ENDFORM. *&---------------------------------------------------------------------* *& Form PRM_GET_EXCEL *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM PRM_GET_EXCEL . IF P_FILE IS NOT INITIAL. *将excel数据传入内表 CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE' EXPORTING FILENAME = P_FILE I_BEGIN_COL = '1' I_BEGIN_ROW = '2' I_END_COL = '20' I_END_ROW = '5000' TABLES INTERN = GT_EXCEL_T. ENDIF. IF GT_EXCEL_T[] IS INITIAL. MESSAGE '文件内容为空！' TYPE 'S' DISPLAY LIKE 'E'. LEAVE LIST-PROCESSING. ENDIF. LOOP AT GT_EXCEL_T INTO GS_EXCEL_T. AT NEW ROW. CLEAR:GW_EXCEL. ENDAT. CASE GS_EXCEL_T-COL. WHEN 1."信息记录类型 GW_EXCEL-LOHNB = GS_EXCEL_T-VALUE. WHEN 2."供应商 GW_EXCEL-LIFNR = GS_EXCEL_T-VALUE. CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT' EXPORTING INPUT = GW_EXCEL-LIFNR IMPORTING OUTPUT = GW_EXCEL-LIFNR. WHEN 3."物料 GW_EXCEL-MATNR = GS_EXCEL_T-VALUE. CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT' EXPORTING INPUT = GW_EXCEL-MATNR IMPORTING OUTPUT = GW_EXCEL-MATNR. WHEN 4."采购组织 GW_EXCEL-EKORG = GS_EXCEL_T-VALUE. WHEN 5. GW_EXCEL-WERKS = GS_EXCEL_T-VALUE. WHEN 6. GW_EXCEL-EKGRP = GS_EXCEL_T-VALUE. WHEN 7. GW_EXCEL-APLFZ = GS_EXCEL_T-VALUE. WHEN 8. GW_EXCEL-NETPR = GS_EXCEL_T-VALUE. WHEN 9. GW_EXCEL-WAERS = GS_EXCEL_T-VALUE. WHEN 10. GW_EXCEL-KPEIN = GS_EXCEL_T-VALUE. WHEN 11. GW_EXCEL-MEINS = GS_EXCEL_T-VALUE. CALL FUNCTION 'CONVERSION_EXIT_ZCONV_OUTPUT' EXPORTING INPUT = GW_EXCEL-MEINS IMPORTING OUTPUT = GW_EXCEL-MEINS EXCEPTIONS UNIT_NOT_FOUND = 1. WHEN 12. GW_EXCEL-UMREZ = GS_EXCEL_T-VALUE. WHEN 13. GW_EXCEL-UMREN = GS_EXCEL_T-VALUE. WHEN 14. GW_EXCEL-LIFAB = GS_EXCEL_T-VALUE. WHEN 15. GW_EXCEL-LIFBI = GS_EXCEL_T-VALUE. WHEN 16. GW_EXCEL-MWSKZ = GS_EXCEL_T-VALUE. WHEN OTHERS. ENDCASE. AT END OF ROW. APPEND GW_EXCEL TO GT_EXCEL. ENDAT. ENDLOOP. ENDFORM. *&---------------------------------------------------------------------* *& Form PRM_CHECK_DATA *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM PRM_CHECK_DATA . SELECT EINA~INFNR" TYPE LFA1-LIFNR, EINA~LIFNR" TYPE LFA1-LIFNR, EINA~MATNR" TYPE MARA-MATNR, EINA~LIFAB" TYPE MARA-MATNR, EINE~EKORG" TYPE EINE-EKORG, EINE~WERKS" TYPE EINE-WERKS, EINE~ESOKZ" TYPE EINE-WERKS, INTO TABLE GT_CHECK FROM EINA INNER JOIN EINE ON EINA~INFNR = EINE~INFNR FOR ALL ENTRIES IN GT_EXCEL WHERE EINA~LIFNR = GT_EXCEL-LIFNR AND EINA~MATNR = GT_EXCEL-MATNR AND EINE~EKORG = GT_EXCEL-EKORG AND EINE~WERKS = GT_EXCEL-WERKS AND EINA~LOEKZ = '' AND EINE~LOEKZ = ''. LOOP AT GT_EXCEL INTO GW_EXCEL. READ TABLE GT_CHECK INTO GW_CHECK WITH KEY LIFNR = GW_EXCEL-LIFNR MATNR = GW_EXCEL-MATNR EKORG = GW_EXCEL-EKORG WERKS = GW_EXCEL-WERKS. IF SY-SUBRC <> 0. GW_EXCEL-MESSAGE = '数据未建立，请稍后检查！'. GW_EXCEL-ICON = '@0A@'. ELSE. GW_EXCEL-INFNR = GW_CHECK-INFNR. ENDIF. CLEAR:A017. SELECT SINGLE * FROM A017 WHERE LIFNR = GW_EXCEL-LIFNR AND MATNR = GW_EXCEL-MATNR AND EKORG = GW_EXCEL-EKORG AND WERKS = GW_EXCEL-WERKS AND DATAB >= GW_EXCEL-LIFAB. IF A017 IS NOT INITIAL. GW_EXCEL-MESSAGE = '有效开始日期不能小于等于原有日期！'. GW_EXCEL-ICON = '@0A@'. ENDIF. IF GW_EXCEL-LIFAB IS INITIAL OR GW_EXCEL-LIFBI IS INITIAL. GW_EXCEL-MESSAGE = '新建日期范围必输！'. GW_EXCEL-ICON = '@0A@'. ENDIF. IF GW_EXCEL-LOHNB <> 'S' AND GW_EXCEL-LOHNB <> 'W'. GW_EXCEL-MESSAGE = '信息类型错误！'. GW_EXCEL-ICON = '@0A@'. ELSEIF GW_EXCEL-LOHNB = 'S'. IF GW_CHECK-ESOKZ <> '0'. GW_EXCEL-MESSAGE = '信息类型不匹配！'. GW_EXCEL-ICON = '@0A@'. ENDIF. ELSEIF GW_EXCEL-LOHNB = 'W'. IF GW_CHECK-ESOKZ <> '3'. GW_EXCEL-MESSAGE = '信息类型不匹配！'. GW_EXCEL-ICON = '@0A@'. ENDIF. ENDIF. * * IF GW_EXCEL-LIFAB <= GW_CHECK-LIFAB. * GW_EXCEL-MESSAGE = '有效开始日期不能小于等于原有日期！'. * GW_EXCEL-ICON = '@0A@'. * ENDIF. MODIFY GT_EXCEL FROM GW_EXCEL. CLEAR:GW_EXCEL-MESSAGE. ENDLOOP. ENDFORM. *&---------------------------------------------------------------------* *& Form PRM_SET_FIELD *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM PRM_SET_FIELD . REFRESH GT_FIELDCAT. LS_LAYOUT-CWIDTH_OPT = 'X'. "优化列宽选项是否设置 LS_LAYOUT-ZEBRA = 'X'. LS_LAYOUT-BOX_FNAME = 'SEL'. APPEND_FIELDCAT:'ICON' '标记' 1 '', 'MESSAGE' '消息' 100 '', 'LOHNB' '信息记录类型' 1 '', 'INFNR' '信息记录编号' 10 'X', 'LIFNR' '供应商编码' 10 'X', 'MATNR' '物料编码' 18 'X', 'EKORG' '采购组织' 4 '', 'EKGRP' '采购组' 3 '', 'MEINS' '订单价格单位' 5 '', 'WERKS' '工厂' 4 '', 'APLFZ' '计划交货期限' 3 '', 'WAERS' '货币码' 5 '', 'NETPR' '价格' 16 '', 'MWSKZ' '税码' 2 '', 'KPEIN' '价格倍数' 6 '', 'UMREZ' '转化分子' 5 '', 'UMREN' '转化分母' 5 '', 'LIFAB' '开始日期' 10 '', 'LIFBI' '截止日期' 10 ''. ENDFORM. *&---------------------------------------------------------------------* *& Form PRM_SHOW *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM PRM_SHOW . CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC' EXPORTING I_CALLBACK_PROGRAM = SY-REPID IS_LAYOUT_LVC = LS_LAYOUT IT_FIELDCAT_LVC = GT_FIELDCAT[] I_CALLBACK_PF_STATUS_SET = 'PRM_SET_STATUS' I_CALLBACK_USER_COMMAND = 'PRM_USER_COMMAND' * i_grid_title = lv_title I_DEFAULT = 'X' I_SAVE = 'A' TABLES T_OUTTAB = GT_EXCEL EXCEPTIONS PROGRAM_ERROR = 1 OTHERS = 2. IF SY-SUBRC <> 0. ENDIF. ENDFORM. FORM PRM_SET_STATUS USING RT_EXTAB TYPE SLIS_T_EXTAB.. SET PF-STATUS 'ZSTAT'. ENDFORM. FORM PRM_USER_COMMAND USING P_UCOMM LIKE SY-UCOMM P_SELFIELD TYPE SLIS_SELFIELD. DATA:GT_SELECT TYPE TABLE OF TY_EXCEL. DATA:GW_SELECT LIKE LINE OF GT_EXCEL. DATA:LR_GRID TYPE REF TO CL_GUI_ALV_GRID. DATA:LV_LINE TYPE I. CHECK P_UCOMM = 'GENR'. APPEND LINES OF GT_EXCEL TO GT_SELECT. DELETE GT_SELECT WHERE SEL = ''. DELETE GT_SELECT WHERE MESSAGE <> ''. CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR' IMPORTING E_GRID = LR_GRID. LOOP AT GT_SELECT INTO GW_EXCEL. CLEAR BDCDATA[]. PERFORM BDC_DYNPRO USING 'SAPMM06I' '0100'. PERFORM BDC_FIELD USING 'BDC_CURSOR' 'EINE-WERKS'. PERFORM BDC_FIELD USING 'BDC_OKCODE' '/00'. PERFORM BDC_FIELD USING 'EINA-LIFNR' GW_EXCEL-LIFNR. PERFORM BDC_FIELD USING 'EINA-MATNR' GW_EXCEL-MATNR. PERFORM BDC_FIELD USING 'EINE-EKORG' GW_EXCEL-EKORG. PERFORM BDC_FIELD USING 'EINE-WERKS' GW_EXCEL-WERKS. IF GW_EXCEL-LOHNB = 'S'. PERFORM BDC_FIELD USING 'RM06I-NORMB' 'X'. ELSEIF GW_EXCEL-LOHNB = 'L'. PERFORM BDC_FIELD USING 'RM06I-LOHNB' 'X'. ENDIF. PERFORM BDC_DYNPRO USING 'SAPMM06I' '0101'. PERFORM BDC_FIELD USING 'BDC_OKCODE' '=EINE'. IF GW_EXCEL-MEINS IS NOT INITIAL. PERFORM BDC_FIELD USING 'EINA-MEINS' GW_EXCEL-MEINS. ENDIF. IF GW_EXCEL-UMREZ <> 0. PERFORM BDC_FIELD USING 'EINA-UMREZ' GW_EXCEL-UMREZ. ENDIF. IF GW_EXCEL-UMREN <> 0. PERFORM BDC_FIELD USING 'EINA-UMREN' GW_EXCEL-UMREN. ENDIF. PERFORM BDC_DYNPRO USING 'SAPMM06I' '0102'. PERFORM BDC_FIELD USING 'BDC_CURSOR' 'EINE-MWSKZ'. PERFORM BDC_FIELD USING 'BDC_OKCODE' '/00'. IF GW_EXCEL-APLFZ > 0. PERFORM BDC_FIELD USING 'EINE-APLFZ' GW_EXCEL-APLFZ. ENDIF. IF GW_EXCEL-EKGRP IS NOT INITIAL. PERFORM BDC_FIELD USING 'EINE-EKGRP' GW_EXCEL-EKGRP. ENDIF. * PERFORM BDC_FIELD USING 'EINE-NORBM' * RECORD-NORBM_011. * PERFORM BDC_FIELD USING 'EINE-WEBRE' * RECORD-WEBRE_012. IF GW_EXCEL-MWSKZ IS NOT INITIAL. PERFORM BDC_FIELD USING 'EINE-MWSKZ' GW_EXCEL-MWSKZ. ENDIF. * PERFORM BDC_FIELD USING 'EINE-IPRKZ' * RECORD-IPRKZ_014. PERFORM BDC_DYNPRO USING 'SAPMM06I' '0105'. PERFORM BDC_FIELD USING 'BDC_CURSOR' 'EINE-ANGNR'. PERFORM BDC_FIELD USING 'BDC_OKCODE' '=KO'. PERFORM BDC_DYNPRO USING 'SAPLV14A' '0102'. PERFORM BDC_FIELD USING 'BDC_CURSOR' 'VAKE-DATAB(01)'. PERFORM BDC_FIELD USING 'BDC_OKCODE' '=NEWD'. PERFORM BDC_DYNPRO USING 'SAPMV13A' '0201'. PERFORM BDC_FIELD USING 'BDC_CURSOR' 'KONP-KBETR(01)'. PERFORM BDC_FIELD USING 'BDC_OKCODE' '=SICH'. PERFORM BDC_FIELD USING 'RV13A-DATAB' GW_EXCEL-LIFAB. PERFORM BDC_FIELD USING 'RV13A-DATBI' GW_EXCEL-LIFBI. PERFORM BDC_FIELD USING 'KONP-KBETR(01)' GW_EXCEL-NETPR. IF GW_EXCEL-WAERS IS NOT INITIAL. PERFORM BDC_FIELD USING 'KONP-KONWA(01)' GW_EXCEL-WAERS. ENDIF. IF GW_EXCEL-KPEIN IS NOT INITIAL. PERFORM BDC_FIELD USING 'KONP-KPEIN(01)' GW_EXCEL-KPEIN. ENDIF. * PERFORM BDC_TRANSACTION USING 'ME12'. CALL TRANSACTION 'ME12' USING BDCDATA MODE 'E' UPDATE 'S' MESSAGES INTO MESSTAB. READ TABLE MESSTAB WITH KEY MSGTYP = 'E'. IF SY-SUBRC <> 0. GW_EXCEL-MESSAGE = '修改成功！'. GW_EXCEL-ICON = '@08@'. ELSE. CONDENSE MESSTAB-MSGV1 NO-GAPS. CONDENSE MESSTAB-MSGV2 NO-GAPS. CONDENSE MESSTAB-MSGV3 NO-GAPS. CONDENSE MESSTAB-MSGV4 NO-GAPS. CONCATENATE '修改失败:' MESSTAB-MSGV1 MESSTAB-MSGV2 MESSTAB-MSGV3 MESSTAB-MSGV4 INTO GW_EXCEL-MESSAGE. GW_EXCEL-ICON = '@0A@'. ENDIF. MODIFY GT_SELECT FROM GW_EXCEL. refresh:MESSTAB. ENDLOOP. LOOP AT GT_EXCEL INTO GW_EXCEL. LV_LINE = SY-TABIX. READ TABLE GT_SELECT INTO GW_SELECT WITH KEY INFNR = GW_EXCEL-INFNR. IF SY-SUBRC = 0. GW_EXCEL-MESSAGE = GW_SELECT-MESSAGE. GW_EXCEL-ICON = GW_SELECT-ICON. MODIFY GT_EXCEL FROM GW_EXCEL INDEX LV_LINE TRANSPORTING MESSAGE ICON. ENDIF. ENDLOOP. CALL METHOD LR_GRID->REFRESH_TABLE_DISPLAY. ENDFORM. *&---------------------------------------------------------------------* *& Form BDC_DYNPRO *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * -->P_0774 text * -->P_0775 text *----------------------------------------------------------------------* FORM BDC_DYNPRO USING PROGRAM DYNPRO. CLEAR BDCDATA. BDCDATA-PROGRAM = PROGRAM. BDCDATA-DYNPRO = DYNPRO. BDCDATA-DYNBEGIN = 'X'. APPEND BDCDATA. ENDFORM. *----------------------------------------------------------------------* * Insert field * *----------------------------------------------------------------------* FORM BDC_FIELD USING FNAM FVAL. CLEAR BDCDATA. BDCDATA-FNAM = FNAM. BDCDATA-FVAL = FVAL. APPEND BDCDATA. ENDFORM. *&---------------------------------------------------------------------* *& Form PRM_SAVE_FILE *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM PRM_SAVE_FILE . DATA:GV_FILE1 TYPE STRING. DATA:GV_FILE2 TYPE STRING. DATA:GV_FILE3 TYPE STRING. DATA:LS_KEY TYPE WWWDATATAB, LV_DESTINAT LIKE RLGRAP-FILENAME, LV_RC TYPE I. CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG EXPORTING WINDOW_TITLE = '保存文件到' CHANGING FILENAME = GV_FILE1 PATH = GV_FILE2 FULLPATH = GV_FILE3 EXCEPTIONS CNTL_ERROR = 1 ERROR_NO_GUI = 2 NOT_SUPPORTED_BY_GUI = 3 INVALID_DEFAULT_FILE_NAME = 4 OTHERS = 5. IF SY-SUBRC <> 0. ENDIF. IF GV_FILE3 IS NOT INITIAL. LS_KEY-RELID = 'MI'. LS_KEY-OBJID = 'ZMMF016'. LV_DESTINAT = GV_FILE3. "下载模板 CALL FUNCTION 'DOWNLOAD_WEB_OBJECT' EXPORTING KEY = LS_KEY DESTINATION = LV_DESTINAT IMPORTING RC = LV_RC. ENDIF. ENDFORM.
模板：
必填的为黄色部分
截止日期默认：9991231
注：本程序只是在原有的采购信息记录上，新增一条新的有效期间为有效开始日期-有效截止日期的记录