*---------------------------------------------------------------------* * PROGRAM ID : ZSDR064 * * APPLICATION NAME : SD * * AUTHOR : liugx * * TRANSACTION : ZSDR064 * * PROGRAM TYPE : REPORT PROGRAM * * INPUT FILES : N/A * * OUTPUT FILES : N/A * * Development class : ZDEV * * Message class : * * DESCRIPTION : 客户导入 * *---------------------------------------------------------------------* *=====================================================================* * 000 2017/11/03 NEW PROGRAM liugx * REPORT zsdr064. INCLUDE zsdr064top. INCLUDE zsdr064screen. INCLUDE zsdr064sub. INITIALIZATION. texta = '参数选择'. text_a1 = '导入文件'. AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file. CALL FUNCTION 'F4_FILENAME' EXPORTING field_name = 'P_FILE' IMPORTING file_name = p_file. START-OF-SELECTION. IF p_file IS INITIAL. MESSAGE '文件路径不能为空！' TYPE 'E'. ELSE. PERFORM frm_import_data. ENDIF. *&---------------------------------------------------------------------* *& 包含 ZSDR064TOP *&---------------------------------------------------------------------* DATA: wa_header TYPE cmds_ei_header, wa_central_data TYPE cmds_ei_vmd_central_data, wa_central_datax TYPE cmds_ei_vmd_central_data_xflag, wa_address TYPE bapiad1vl, wa_addressx TYPE bapiad1vlx, wa_tax_ind_st TYPE cmds_ei_tax_ind, wa_tax_ind TYPE cmds_ei_cmd_tax_ind, wa_bankdetail_st TYPE cvis_ei_cvi_bankdetail, wa_bankdetail TYPE cvis_ei_bankdetail, wa_company_code_st TYPE cmds_ei_company, wa_company_code TYPE cmds_ei_cmd_company, wa_functions_st TYPE cmds_ei_functions, wa_functions_t TYPE cmds_ei_functions_t, wa_functions TYPE cmds_ei_cmd_functions, wa_sales_data_st TYPE cmds_ei_sales, wa_sales_data TYPE cmds_ei_cmd_sales, wa_customer TYPE cmds_ei_extern, wa_customers TYPE cmds_ei_main, wa_correct TYPE cmds_ei_main, wa_defective TYPE cmds_ei_main, wa_mes_correct TYPE cvis_message, wa_mes_error TYPE cvis_message. CONSTANTS: c_update TYPE c VALUE 'M', c_insert TYPE c VALUE 'I'. *定义OLE变量 DATA:g_excel TYPE ole2_object, g_applica TYPE ole2_object, g_sheet TYPE ole2_object, g_cell TYPE ole2_object, g_workbook TYPE ole2_object. DATA:p_filepath TYPE string. DATA: it_raw TYPE truxs_t_text_data, BEGIN OF record OCCURS 0, vkorg TYPE vkorg,"销售组织 name1 TYPE ad_name1,"名称 region TYPE regio,"省份 street TYPE ad_street, "街道 收货地址 house_num2 TYPE ad_hsnm2,"联系人 补充 str_suppl3 TYPE ad_strspp3,"电话 街道4 stkzu TYPE stkzu,"增票，普票 stceg TYPE stceg,"增值税号 location TYPE ad_lctn,"开票地址 街道5 city2 TYPE ad_city2,"开票电话 区域 banka TYPE banka,"开户银行及帐号 END OF record. DATA:it005u LIKE TABLE OF t005u WITH HEADER LINE, itvkwz LIKE TABLE OF tvkwz WITH HEADER LINE. *&---------------------------------------------------------------------* *& 包含 ZSDR064SCREEN *&---------------------------------------------------------------------* SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE texta . SELECTION-SCREEN BEGIN OF LINE. SELECTION-SCREEN COMMENT (10) text_a1. SELECTION-SCREEN POSITION 18. PARAMETERS: p_file TYPE rlgrap-filename. SELECTION-SCREEN END OF LINE. SELECTION-SCREEN END OF BLOCK b1. *&---------------------------------------------------------------------* *& 包含 ZSDR064SUB *&---------------------------------------------------------------------* *&---------------------------------------------------------------------* *& Form FRM_IMPORT_DATA *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM frm_import_data . DATA: it_raw TYPE truxs_t_text_data. DATA:messtab LIKE TABLE OF bdcmsgcoll WITH HEADER LINE, subrc TYPE syst-subrc. CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP' EXPORTING * I_FIELD_SEPERATOR = i_line_header = 'X' i_tab_raw_data = it_raw i_filename = p_file TABLES i_tab_converted_data = record[] * EXCEPTIONS * CONVERSION_FAILED = 1 * OTHERS = 2 . IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. SELECT * INTO CORRESPONDING FIELDS OF TABLE it005u FROM t005u WHERE spras = 1 AND land1 = 'CN'. * A "customer" is made up of 4 Main Sections * 1. HEADER * 2. CENTRAL_DATA * 3. COMPANY_DATA * 4 SALES_DATA DATA:bapiret2 LIKE TABLE OF bapiret2 WITH HEADER LINE. CLEAR bapiret2[]. DATA:kunnr TYPE kunnr. DATA:bapiret2_t TYPE bapiret2_t. LOOP AT record. CONDENSE record-name1 NO-GAPS. CONDENSE record-region NO-GAPS. CONDENSE record-house_num2 NO-GAPS. CONDENSE record-str_suppl3 NO-GAPS. CONDENSE record-street NO-GAPS. CONDENSE record-vkorg NO-GAPS. CONDENSE record-stkzu NO-GAPS. CONDENSE record-stceg NO-GAPS. CONDENSE record-location NO-GAPS. CONDENSE record-city2 NO-GAPS. CONDENSE record-banka NO-GAPS. CLEAR: wa_customer,wa_customers, wa_company_code_st, wa_sales_data_st. "判断客户是否存在，如果存在，则不扩充 SELECT SINGLE kunnr INTO kunnr FROM kna1 WHERE name1 = record-name1. IF sy-subrc = 0. CLEAR bapiret2. bapiret2-type = 'A'. CONCATENATE '客户已经存在！编号为：' kunnr INTO bapiret2-message. APPEND bapiret2. CONTINUE. ENDIF. PERFORM import_center_data . PERFORM import_company_data. PERFORM import_sales_data. * wa_customer-header-object_instance-kunnr = ''. wa_customer-header-object_task = c_insert. CLEAR:wa_customer-company_data,wa_customer-sales_data. wa_customer-company_data = wa_company_code. wa_customer-sales_data = wa_sales_data. APPEND wa_customer TO wa_customers-customers. CALL METHOD cmd_ei_api=>maintain_bapi EXPORTING iv_collect_messages = 'X' is_master_data = wa_customers IMPORTING es_master_data_correct = wa_correct es_message_correct = wa_mes_correct es_master_data_defective = wa_defective es_message_defective = wa_mes_error. IF wa_mes_error-is_error IS INITIAL. CALL FUNCTION 'BAPI_TRANSACTION_COMMIT' EXPORTING wait = abap_true. LOOP AT wa_correct-customers INTO wa_customer. wa_header = wa_customer-header. CLEAR bapiret2. bapiret2-type = 'S'. CONCATENATE '导入成功，客户编码：'wa_header-object_instance INTO bapiret2-message. APPEND bapiret2. ENDLOOP. ELSE. CALL FUNCTION 'BAPI_TRANSACTION_ROLLBACK'. LOOP AT wa_mes_error-messages INTO bapiret2 . APPEND bapiret2. ENDLOOP. ENDIF. ENDLOOP. CALL FUNCTION 'Z_DISP_BAPILOG' EXPORTING p_title = '导入结果查看' TABLES return = bapiret2. ENDFORM. " FRM_IMPORT_DATA *&---------------------------------------------------------------------* *& Form FRM_BANK_CREATE *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM frm_bank_create CHANGING bank_key TYPE bapi1011_key-bank_key. DATA:address TYPE bapi1011_address . address-bank_name = record-banka. "从bnka里取最大的银行代码 BANKL 100000001 SELECT MAX( bankl ) INTO bank_key FROM bnka WHERE banks = 'CN' AND bankl BETWEEN 'I100000001' AND 'I199999999'. IF bank_key IS INITIAL . bank_key = 'I100000001'. ENDIF. DATA:i_key TYPE i. i_key = bank_key+1(10) + 1."加1 bank_key = i_key. CONCATENATE 'I' bank_key INTO bank_key. CONDENSE bank_key NO-GAPS. CALL FUNCTION 'BAPI_BANK_CREATE' EXPORTING bank_ctry = 'CN' bank_key = bank_key bank_address = address * BANK_METHOD = * BANK_FORMATTING = * BANK_ADDRESS1 = * I_XUPDATE = 'X' * I_CHECK_BEFORE_SAVE = * IMPORTING * RETURN = * BANKCOUNTRY = * BANKKEY = . IF sy-subrc = 0. CALL FUNCTION 'BAPI_TRANSACTION_COMMIT' EXPORTING wait = abap_true. ENDIF. ENDFORM. " FRM_BANK_CREATE *&---------------------------------------------------------------------* *& Form IMPORT_CENTER_DATA *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM import_center_data . wa_address-title = '0003'. wa_address-name = record-name1. wa_address-city = record-region. "城市 wa_address-postl_cod1 = '999999'. wa_address-street = record-street."街道 wa_address-house_no2 = record-house_num2."收货人 wa_address-str_suppl3 = record-str_suppl3."电话 wa_address-district = record-city2."开票电话 wa_address-location = record-location."开票地址 wa_address-country = 'CN'. wa_address-langu = 'ZH'. READ TABLE it005u WITH KEY bezei = record-region. IF sy-subrc = 0. wa_address-region = it005u-bland. ENDIF. "wa_address-region = '010'."地区 wa_address-sort1 = record-name1."搜素 * wa_address-sort2 = ''. * wa_address-taxjurcode = 'FL0000000'. wa_address-time_zone = 'UTC+8'. * Assign address data directly wa_customer-central_data-address-postal-data = wa_address. wa_customer-central_data-address-postal-datax = wa_addressx. * Account account group directly wa_customer-central_data-central-data-ktokd = '1000'."客户组 IF record-stkzu = '增'. wa_customer-central_data-central-data-stkzu = 'X'."增值税标识 ENDIF. wa_customer-central_data-central-data-stceg = record-stceg."增值税号 * wa_customer-central_data-central-datax-ktokd = 'X'. DATA:bankl TYPE bankl. PERFORM frm_bank_create CHANGING bankl. ***银行代码必须存在，用BAPI_BANK_CREATE 创建 CLEAR:wa_customer-central_data-bankdetail,wa_bankdetail-bankdetails. wa_bankdetail_st-task = c_insert. wa_bankdetail_st-data_key-banks = 'CN'."国家 wa_bankdetail_st-data_key-bankl = bankl."银行代码 wa_bankdetail_st-data_key-bankn = record-banka."银行 APPEND wa_bankdetail_st TO wa_bankdetail-bankdetails. wa_customer-central_data-bankdetail = wa_bankdetail. ***税 CLEAR wa_tax_ind-tax_ind. wa_tax_ind_st-task = c_insert. wa_tax_ind_st-data_key-aland = 'CN'. wa_tax_ind_st-data_key-tatyp = 'MWST'. wa_tax_ind_st-data-taxkd = '1'."必须上税 APPEND wa_tax_ind_st TO wa_tax_ind-tax_ind. wa_customer-central_data-tax_ind = wa_tax_ind. ENDFORM. " IMPORT_CENTER_DATA *&---------------------------------------------------------------------* *& Form IMPORT_COMPANY_DATA *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM import_company_data . CLEAR wa_company_code-company. wa_company_code_st-task = c_insert. "wa_company_code_st-data_key-bukrs = record-vkorg. SELECT SINGLE bukrs INTO wa_company_code_st-data_key-bukrs FROM tvko WHERE vkorg = record-vkorg. "wa_company_code_st-data-zuawa = '009'."排序码 wa_company_code_st-data-akont = '1122020001'. * wa_company_code_st-data-zwels = 'ACDETY'. wa_company_code_st-data-zterm = '0004'. * wa_company_code_st-data-zgrup = '02'. * wa_company_code_st-data-xausz = '2'. * wa_company_code_st-data-xzver = 'X'. APPEND wa_company_code_st TO wa_company_code-company. ENDFORM. " IMPORT_COMPANY_DATA *&---------------------------------------------------------------------* *& Form IMPORT_SALES_DATA *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM import_sales_data . CLEAR wa_sales_data-sales. wa_sales_data_st-task = c_insert. wa_sales_data_st-data_key-vkorg = record-vkorg. wa_sales_data_st-data_key-vtweg = '99'. wa_sales_data_st-data_key-spart = '99'. wa_sales_data_st-data-kalks = '1'."客户定价过程 wa_sales_data_st-data-awahr = '100'."订单可能性 wa_sales_data_st-data-inco1 = 'FOB'."国际贸易条 wa_sales_data_st-data-inco2 = '北京'. wa_sales_data_st-data-antlf = '9'."最大交货 wa_sales_data_st-data-kzazu = 'X'. wa_sales_data_st-data-vsbed = '10'."装运条件 wa_sales_data_st-data-waers = 'RMB'. wa_sales_data_st-data-ktgrd = '02'."帐户分配组 wa_sales_data_st-data-zterm = '0004'."付款条件 wa_sales_data_st-data-versg = 1."客户统计组 wa_sales_data_st-data-kdgrp = '23'."客户组 SELECT SINGLE bzirk INTO wa_sales_data_st-data-bzirk FROM zxd01_regio WHERE regio = wa_address-region. "wa_sales_data_st-data-bzirk = '1400'."销售地区 wa_sales_data_st-data-konda = '10'."价格组 SELECT SINGLE werks INTO wa_sales_data_st-data-vwerk FROM tvkwz WHERE vtweg = '99' AND vkorg = record-vkorg. * wa_sales_data_st-data-vwerk = 8800."交货工厂 ***合作伙伴 CLEAR:wa_functions_t,wa_functions-functions. wa_functions_st-task = c_insert. wa_functions_st-data_key-parvw = 'AG'. wa_functions_st-data_key-parza = '000'. APPEND wa_functions_st TO wa_functions_t. wa_functions-functions = wa_functions_t. wa_functions_st-task = c_insert. wa_functions_st-data_key-parvw = 'RE'. wa_functions_st-data_key-parza = '000'. APPEND wa_functions_st TO wa_functions_t. wa_functions-functions = wa_functions_t. wa_functions_st-task = c_insert. wa_functions_st-data_key-parvw = 'RG'. wa_functions_st-data_key-parza = '000'. APPEND wa_functions_st TO wa_functions_t. wa_functions-functions = wa_functions_t. wa_functions_st-task = c_insert. wa_functions_st-data_key-parvw = 'WE'. wa_functions_st-data_key-parza = '000'. APPEND wa_functions_st TO wa_functions_t. wa_functions-functions = wa_functions_t. wa_sales_data_st-functions = wa_functions. APPEND wa_sales_data_st TO wa_sales_data-sales. ENDFORM. " IMPORT_SALES_DATA