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