https://pastein.ru/t/MfN
Загрузка данных
*---------------------------------------------------------------------*
* Main include
*---------------------------------------------------------------------*
TYPE-POOLS: slis.
*---------------------------------------------------------------------*
* TYPES
*---------------------------------------------------------------------*
TYPES:
BEGIN OF ty_excel,
stcd1 TYPE stcd1,
stcd3 TYPE stcd3,
END OF ty_excel.
TYPES:
BEGIN OF ty_result,
stcd1 TYPE stcd1,
stcd3 TYPE stcd3,
name1 TYPE kna1-name1,
sortl TYPE kna1-sortl,
kunnr TYPE kunnr,
status TYPE char10,
message TYPE char255,
END OF ty_result.
TYPES:
BEGIN OF ty_config,
begru TYPE begru,
vkbur TYPE vkbur,
vwerk TYPE werks_d,
zf_ktonr TYPE kunnr,
END OF ty_config.
*---------------------------------------------------------------------*
* DATA - EXCEL
*---------------------------------------------------------------------*
DATA:
gt_excel TYPE STANDARD TABLE OF ty_excel,
gs_excel TYPE ty_excel.
DATA:
gt_excel_raw TYPE STANDARD TABLE OF alsmex_tabline,
gs_excel_raw TYPE alsmex_tabline.
*---------------------------------------------------------------------*
* DATA - RESULT
*---------------------------------------------------------------------*
DATA:
gt_result TYPE STANDARD TABLE OF ty_result,
gs_result TYPE ty_result.
*---------------------------------------------------------------------*
* DATA - CONFIG
*---------------------------------------------------------------------*
DATA:
gs_config TYPE ty_config.
*---------------------------------------------------------------------*
* DATA - BAPI
*---------------------------------------------------------------------*
DATA:
gs_personaldata TYPE bapikna101_1,
gs_opt_personaldata TYPE bapikna105,
gs_companydata TYPE bapikna106,
gs_opt_companydata TYPE bapikna105,
gs_copyreference TYPE bapikna102,
gv_consumeren TYPE bapikna107-consumer_flag,
gv_credit_control TYPE bapikna108-x_flag,
gv_customer TYPE kunnr,
gs_bapi_return TYPE bapireturn1.
*---------------------------------------------------------------------*
* DATA - CUSTOMER MASTER
*---------------------------------------------------------------------*
DATA:
gs_kna1 TYPE kna1,
gs_knb1 TYPE knb1,
gs_knvv TYPE knvv.
*---------------------------------------------------------------------*
* DATA - TAX / PARTNERS
*---------------------------------------------------------------------*
DATA:
gs_xknvi TYPE fknvi,
gs_xknvp TYPE fknvp,
gt_xknvi TYPE STANDARD TABLE OF fknvi,
gt_xknvp TYPE STANDARD TABLE OF fknvp.
*---------------------------------------------------------------------*
* DATA - NAME / SORT
*---------------------------------------------------------------------*
DATA:
gv_name1 TYPE kna1-name1,
gv_sortl TYPE kna1-sortl,
gv_ok TYPE c,
gv_msg TYPE char255.
*---------------------------------------------------------------------*
* ALV
*---------------------------------------------------------------------*
DATA:
gt_fieldcat TYPE slis_t_fieldcat_alv,
gs_fieldcat TYPE slis_fieldcat_alv.
*---------------------------------------------------------------------*
* CONSTANTS
*---------------------------------------------------------------------*
CONSTANTS:
gc_ktokd TYPE ktokd VALUE 'DMCU',
gc_land1 TYPE land1 VALUE 'RU',
gc_spras TYPE spras VALUE 'R',
gc_lzone TYPE lzone VALUE 'ZSD019',
gc_akont TYPE akont VALUE '62.01.192',
gc_fdgrv TYPE fdgrv VALUE 'ZZZTECH602',
gc_zterm TYPE dzterm VALUE 'N000',
gc_waers TYPE waers VALUE 'RUB',
gc_kalks TYPE kalks VALUE '1',
gc_versg TYPE versg VALUE '1',
gc_kzazu TYPE kzazu VALUE 'X',
gc_vsbed TYPE vsbed VALUE '23',
gc_vtweg TYPE vtweg VALUE '45',
gc_spart TYPE spart VALUE '00',
gc_antlf TYPE antlf VALUE '9',
gc_ktgrd TYPE ktgrd VALUE '45',
gc_taxkd TYPE taxkd VALUE '1',
gc_kvgr1 TYPE kvgr1 VALUE 'NEV',
gc_katr1 TYPE katr1 VALUE 'MX',
gc_parvw_zp TYPE parvw VALUE 'ZP',
gc_parvw_zf TYPE parvw VALUE 'ZF',
gc_ref_customer TYPE kunnr VALUE '0000000001'.
*---------------------------------------------------------------------*
* EVENTS
*---------------------------------------------------------------------*
AT SELECTION-SCREEN.
PERFORM validate_selection.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
PERFORM get_file_name.
START-OF-SELECTION.
PERFORM get_configuration.
PERFORM upload_excel.
PERFORM process_excel.
PERFORM display_result.
*---------------------------------------------------------------------*
* FORM VALIDATE_SELECTION
*---------------------------------------------------------------------*
FORM validate_selection.
IF p_bukrs IS INITIAL.
MESSAGE 'Введите BUKRS' TYPE 'E'.
ENDIF.
IF p_vkorg IS INITIAL.
MESSAGE 'Введите VKORG' TYPE 'E'.
ENDIF.
IF p_ktonr IS INITIAL.
MESSAGE 'Введите KTONR' TYPE 'E'.
ENDIF.
IF p_vkgrp IS INITIAL.
MESSAGE 'Введите VKGRP' TYPE 'E'.
ENDIF.
IF p_file IS INITIAL.
MESSAGE 'Выберите Excel-файл' TYPE 'E'.
ENDIF.
ENDFORM.
*---------------------------------------------------------------------*
* FORM GET_FILE_NAME
*---------------------------------------------------------------------*
FORM get_file_name.
CALL FUNCTION 'F4_FILENAME'
EXPORTING
program_name = sy-cprog
dynpro_number = sy-dynnr
IMPORTING
file_name = p_file.
ENDFORM.
*---------------------------------------------------------------------*
* FORM GET_CONFIGURATION
*---------------------------------------------------------------------*
FORM get_configuration.
CLEAR gs_config.
CASE p_bukrs.
WHEN '1000'.
IF p_vkorg = '1000'.
gs_config-begru = '1MCU'.
gs_config-vkbur = '0002'.
gs_config-vwerk = '1000'.
gs_config-zf_ktonr = 'BEZANT-1'.
ELSE.
MESSAGE 'Для BUKRS 1000 допустим VKORG 1000' TYPE 'E'.
ENDIF.
WHEN '4000'.
IF p_vkorg = '4000'.
gs_config-begru = '4MCU'.
gs_config-vkbur = '0003'.
gs_config-vwerk = '4000'.
gs_config-zf_ktonr = 'BEZANT-SIB'.
ELSE.
MESSAGE 'Для BUKRS 4000 допустим VKORG 4000' TYPE 'E'.
ENDIF.
WHEN OTHERS.
MESSAGE 'Недопустимый BUKRS' TYPE 'E'.
ENDCASE.
ENDFORM.
*---------------------------------------------------------------------*
* FORM UPLOAD_EXCEL
*---------------------------------------------------------------------*
FORM upload_excel.
DATA:
lv_row TYPE i,
lv_max_row TYPE i,
lv_inn TYPE char50,
lv_kpp TYPE char50.
CLEAR:
gt_excel,
gt_excel_raw.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = p_file
i_begin_col = 1
i_begin_row = 1
i_end_col = 2
i_end_row = 65536
TABLES
intern = gt_excel_raw
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc <> 0.
MESSAGE 'Ошибка загрузки Excel' TYPE 'E'.
ENDIF.
IF gt_excel_raw IS INITIAL.
MESSAGE 'Excel-файл пустой' TYPE 'E'.
ENDIF.
SORT gt_excel_raw BY row col.
CLEAR lv_max_row.
LOOP AT gt_excel_raw INTO gs_excel_raw.
IF gs_excel_raw-row > lv_max_row.
lv_max_row = gs_excel_raw-row.
ENDIF.
ENDLOOP.
DO lv_max_row TIMES.
lv_row = sy-index.
" Первая строка - заголовки
IF lv_row = 1.
CONTINUE.
ENDIF.
CLEAR:
gs_excel,
lv_inn,
lv_kpp.
READ TABLE gt_excel_raw INTO gs_excel_raw
WITH KEY
row = lv_row
col = 1.
IF sy-subrc = 0.
lv_inn = gs_excel_raw-value.
ENDIF.
READ TABLE gt_excel_raw INTO gs_excel_raw
WITH KEY
row = lv_row
col = 2.
IF sy-subrc = 0.
lv_kpp = gs_excel_raw-value.
ENDIF.
CONDENSE lv_inn NO-GAPS.
CONDENSE lv_kpp NO-GAPS.
IF lv_inn IS INITIAL
AND lv_kpp IS INITIAL.
CONTINUE.
ENDIF.
gs_excel-stcd1 = lv_inn.
gs_excel-stcd3 = lv_kpp.
APPEND gs_excel TO gt_excel.
ENDDO.
IF gt_excel IS INITIAL.
MESSAGE 'В Excel нет данных для обработки' TYPE 'E'.
ENDIF.
ENDFORM.
*---------------------------------------------------------------------*
* FORM PROCESS_EXCEL
*---------------------------------------------------------------------*
FORM process_excel.
CLEAR gt_result.
LOOP AT gt_excel INTO gs_excel.
CLEAR:
gs_result,
gv_name1,
gv_sortl,
gv_ok,
gv_msg,
gv_customer.
gs_result-stcd1 = gs_excel-stcd1.
gs_result-stcd3 = gs_excel-stcd3.
"---------------------------------------------------------------*
" Получаем NAME1 / SORTL по ИНН / КПП
"---------------------------------------------------------------*
PERFORM get_name_sortl
USING
gs_excel-stcd1
gs_excel-stcd3
CHANGING
gv_name1
gv_sortl
gv_ok
gv_msg.
IF gv_ok IS INITIAL.
gs_result-status = 'ERROR'.
gs_result-message = gv_msg.
APPEND gs_result TO gt_result.
CONTINUE.
ENDIF.
gs_result-name1 = gv_name1.
gs_result-sortl = gv_sortl.
"---------------------------------------------------------------*
" Создание клиента
"---------------------------------------------------------------*
PERFORM create_customer
USING
gs_excel-stcd1
gs_excel-stcd3
gv_name1
gv_sortl
CHANGING
gv_customer
gv_ok
gv_msg.
IF gv_ok IS INITIAL.
gs_result-status = 'ERROR'.
gs_result-message = gv_msg.
ELSE.
gs_result-kunnr = gv_customer.
gs_result-status = 'OK'.
gs_result-message = 'Клиент создан'.
ENDIF.
APPEND gs_result TO gt_result.
ENDLOOP.
ENDFORM.
*---------------------------------------------------------------------*
* FORM GET_NAME_SORTL
*---------------------------------------------------------------------*
* Здесь будет вызов твоего API по ИНН/КПП.
*---------------------------------------------------------------------*
FORM get_name_sortl
USING
iv_inn TYPE stcd1
iv_kpp TYPE stcd3
CHANGING
cv_name1 TYPE kna1-name1
cv_sortl TYPE kna1-sortl
cv_ok TYPE c
cv_msg TYPE char255.
CLEAR:
cv_name1,
cv_sortl,
cv_ok,
cv_msg.
"===============================================================*
" TODO:
" Здесь вставляется твой отдельный вызов API.
"
" Пример:
"
" CALL FUNCTION 'Z_GET_ORG_DATA'
" EXPORTING
" iv_inn = iv_inn
" iv_kpp = iv_kpp
" IMPORTING
" ev_name1 = cv_name1
" ev_sortl = cv_sortl.
"
"===============================================================*
cv_msg = 'Не реализовано получение NAME1/SORTL'.
ENDFORM.
*---------------------------------------------------------------------*
* FORM CREATE_CUSTOMER
*---------------------------------------------------------------------*
FORM create_customer
USING
iv_stcd1 TYPE stcd1
iv_stcd3 TYPE stcd3
iv_name1 TYPE kna1-name1
iv_sortl TYPE kna1-sortl
CHANGING
cv_customer TYPE kunnr
cv_ok TYPE c
cv_msg TYPE char255.
CLEAR:
cv_customer,
cv_ok,
cv_msg.
*---------------------------------------------------------------------*
* CLEAR BAPI
*---------------------------------------------------------------------*
CLEAR:
gs_personaldata,
gs_opt_personaldata,
gs_companydata,
gs_opt_companydata,
gs_copyreference,
gs_bapi_return.
CLEAR:
gv_consumeren,
gv_credit_control.
*---------------------------------------------------------------------*
* BAPI PERSONAL DATA
*---------------------------------------------------------------------*
gs_personaldata-country = gc_land1.
gs_personaldata-countryiso = 'RU'.
gs_personaldata-langu_p = gc_spras.
gs_personaldata-langup_iso = 'RU'.
gs_personaldata-currency = gc_waers.
gs_personaldata-currency_iso = 'RUB'.
*---------------------------------------------------------------------*
* BAPI OPTIONAL PERSONAL DATA
*---------------------------------------------------------------------*
gs_opt_personaldata-transpzone = gc_lzone.
gs_opt_personaldata-control_account = gc_akont.
gs_opt_personaldata-pmnttrms = gc_zterm.
gs_opt_personaldata-ship_cond = gc_vsbed.
gs_opt_personaldata-delyg_plnt = gs_config-vwerk.
*---------------------------------------------------------------------*
* BAPI COMPANY DATA
*---------------------------------------------------------------------*
gs_companydata-country = gc_land1.
gs_companydata-countryiso = 'RU'.
gs_companydata-langu = gc_spras.
gs_companydata-langu_iso = 'RU'.
gs_companydata-currency = gc_waers.
gs_companydata-currency_iso = 'RUB'.
*---------------------------------------------------------------------*
* REFERENCE CUSTOMER
*---------------------------------------------------------------------*
gs_copyreference-salesorg = p_vkorg.
gs_copyreference-distr_chan = gc_vtweg.
gs_copyreference-division = gc_spart.
gs_copyreference-ref_custmr = gc_ref_customer.
gv_consumeren = space.
gv_credit_control = space.
*---------------------------------------------------------------------*
* CREATE BASE CUSTOMER
*---------------------------------------------------------------------*
CALL FUNCTION 'BAPI_CUSTOMER_CREATEFROMDATA1'
EXPORTING
pi_personaldata = gs_personaldata
pi_opt_personaldata = gs_opt_personaldata
pi_companydata = gs_companydata
pi_opt_companydata = gs_opt_companydata
pi_consumeren = gv_consumeren
pi_copyreference = gs_copyreference
pi_credit_control_flag = gv_credit_control
IMPORTING
customerno = cv_customer
return = gs_bapi_return.
IF gs_bapi_return-type = 'E'
OR gs_bapi_return-type = 'A'.
cv_msg = gs_bapi_return-message.
ROLLBACK WORK.
CLEAR cv_customer.
RETURN.
ENDIF.
IF cv_customer IS INITIAL.
cv_msg = 'BAPI не вернул номер клиента'.
ROLLBACK WORK.
RETURN.
ENDIF.
*---------------------------------------------------------------------*
* KNA1
*---------------------------------------------------------------------*
CLEAR gs_kna1.
gs_kna1-kunnr = cv_customer.
gs_kna1-ktokd = gc_ktokd.
gs_kna1-name1 = iv_name1.
gs_kna1-sortl = iv_sortl.
gs_kna1-land1 = gc_land1.
gs_kna1-spras = gc_spras.
gs_kna1-stcd1 = iv_stcd1.
gs_kna1-stcd3 = iv_stcd3.
gs_kna1-lzone = gc_lzone.
gs_kna1-katr1 = gc_katr1.
*---------------------------------------------------------------------*
* KNB1
*---------------------------------------------------------------------*
CLEAR gs_knb1.
gs_knb1-kunnr = cv_customer.
gs_knb1-bukrs = p_bukrs.
gs_knb1-akont = gc_akont.
gs_knb1-begru = gs_config-begru.
gs_knb1-fdgrv = gc_fdgrv.
gs_knb1-zterm = gc_zterm.
gs_knb1-kverm = iv_name1.
*---------------------------------------------------------------------*
* KNVV
*---------------------------------------------------------------------*
CLEAR gs_knvv.
gs_knvv-kunnr = cv_customer.
gs_knvv-vkorg = p_vkorg.
gs_knvv-vtweg = gc_vtweg.
gs_knvv-spart = gc_spart.
gs_knvv-vkbur = gs_config-vkbur.
gs_knvv-vkgrp = p_vkgrp.
gs_knvv-waers = gc_waers.
gs_knvv-kalks = gc_kalks.
gs_knvv-versg = gc_versg.
gs_knvv-kzazu = gc_kzazu.
gs_knvv-vsbed = gc_vsbed.
gs_knvv-vwerk = gs_config-vwerk.
gs_knvv-antlf = gc_antlf.
gs_knvv-zterm = gc_zterm.
gs_knvv-ktgrd = gc_ktgrd.
gs_knvv-kvgr1 = gc_kvgr1.
*---------------------------------------------------------------------*
* KNVI
*---------------------------------------------------------------------*
CLEAR:
gs_xknvi,
gt_xknvi.
gs_xknvi-kunnr = cv_customer.
gs_xknvi-aland = gc_land1.
gs_xknvi-taxkd = gc_taxkd.
APPEND gs_xknvi TO gt_xknvi.
*---------------------------------------------------------------------*
* KNVP - ZP
*---------------------------------------------------------------------*
CLEAR:
gs_xknvp,
gt_xknvp.
gs_xknvp-kunnr = cv_customer.
gs_xknvp-vkorg = p_vkorg.
gs_xknvp-vtweg = gc_vtweg.
gs_xknvp-spart = gc_spart.
gs_xknvp-parvw = gc_parvw_zp.
gs_xknvp-kunn2 = p_ktonr.
APPEND gs_xknvp TO gt_xknvp.
*---------------------------------------------------------------------*
* KNVP - ZF
*---------------------------------------------------------------------*
CLEAR gs_xknvp.
gs_xknvp-kunnr = cv_customer.
gs_xknvp-vkorg = p_vkorg.
gs_xknvp-vtweg = gc_vtweg.
gs_xknvp-spart = gc_spart.
gs_xknvp-parvw = gc_parvw_zf.
gs_xknvp-kunn2 = gs_config-zf_ktonr.
APPEND gs_xknvp TO gt_xknvp.
*---------------------------------------------------------------------*
* UPDATE CUSTOMER MASTER
*---------------------------------------------------------------------*
CALL FUNCTION 'SD_CUSTOMER_MAINTAIN_ALL'
EXPORTING
i_kna1 = gs_kna1
i_knb1 = gs_knb1
i_knvv = gs_knvv
i_maintain_address_by_kna1 = 'X'
pi_postflag = 'X'
IMPORTING
e_kunnr = cv_customer
o_kna1 = gs_kna1
TABLES
t_xknvi = gt_xknvi
t_xknvp = gt_xknvp
EXCEPTIONS
client_error = 1
kna1_incomplete = 2
knb1_incomplete = 3
knb5_incomplete = 4
knvv_incomplete = 5
kunnr_not_unique = 6
sales_area_not_unique = 7
sales_area_not_valid = 8
insert_update_conflict = 9
number_assignment_error = 10
number_not_in_range = 11
number_range_not_extern = 12
number_range_not_intern = 13
account_group_not_valid = 14
parnr_invalid = 15
bank_address_invalid = 16
tax_data_not_valid = 17
no_authority = 18
company_code_not_unique = 19
dunning_data_not_valid = 20
knb1_reference_invalid = 21
cam_error = 22
OTHERS = 23.
IF sy-subrc <> 0.
CASE sy-subrc.
WHEN 1.
cv_msg = 'CLIENT_ERROR'.
WHEN 2.
cv_msg = 'KNA1_INCOMPLETE'.
WHEN 3.
cv_msg = 'KNB1_INCOMPLETE'.
WHEN 4.
cv_msg = 'KNB5_INCOMPLETE'.
WHEN 5.
cv_msg = 'KNVV_INCOMPLETE'.
WHEN 6.
cv_msg = 'KUNNR_NOT_UNIQUE'.
WHEN 7.
cv_msg = 'SALES_AREA_NOT_UNIQUE'.
WHEN 8.
cv_msg = 'SALES_AREA_NOT_VALID'.
WHEN 9.
cv_msg = 'INSERT_UPDATE_CONFLICT'.
WHEN 10.
cv_msg = 'NUMBER_ASSIGNMENT_ERROR'.
WHEN 11.
cv_msg = 'NUMBER_NOT_IN_RANGE'.
WHEN 12.
cv_msg = 'NUMBER_RANGE_NOT_EXTERN'.
WHEN 13.
cv_msg = 'NUMBER_RANGE_NOT_INTERN'.
WHEN 14.
cv_msg = 'ACCOUNT_GROUP_NOT_VALID'.
WHEN 15.
cv_msg = 'PARNR_INVALID'.
WHEN 16.
cv_msg = 'BANK_ADDRESS_INVALID'.
WHEN 17.
cv_msg = 'TAX_DATA_NOT_VALID'.
WHEN 18.
cv_msg = 'NO_AUTHORITY'.
WHEN 19.
cv_msg = 'COMPANY_CODE_NOT_UNIQUE'.
WHEN 20.
cv_msg = 'DUNNING_DATA_NOT_VALID'.
WHEN 21.
cv_msg = 'KNB1_REFERENCE_INVALID'.
WHEN 22.
cv_msg = 'CAM_ERROR'.
WHEN OTHERS.
cv_msg = 'Ошибка SD_CUSTOMER_MAINTAIN_ALL'.
ENDCASE.
ROLLBACK WORK.
CLEAR cv_customer.
RETURN.
ENDIF.
*---------------------------------------------------------------------*
* COMMIT
*---------------------------------------------------------------------*
CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'
EXPORTING
wait = 'X'.
cv_ok = 'X'.
ENDFORM.
*---------------------------------------------------------------------*
* FORM DISPLAY_RESULT
*---------------------------------------------------------------------*
FORM display_result.
CLEAR gt_fieldcat.
PERFORM add_fieldcat USING 'STCD1' 'ИНН'.
PERFORM add_fieldcat USING 'STCD3' 'КПП'.
PERFORM add_fieldcat USING 'NAME1' 'Наименование'.
PERFORM add_fieldcat USING 'SORTL' 'Поиск'.
PERFORM add_fieldcat USING 'KUNNR' 'Клиент'.
PERFORM add_fieldcat USING 'STATUS' 'Статус'.
PERFORM add_fieldcat USING 'MESSAGE' 'Сообщение'.
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
i_callback_program = sy-repid
it_fieldcat = gt_fieldcat
TABLES
t_outtab = gt_result
EXCEPTIONS
program_error = 1
OTHERS = 2.
ENDFORM.
*---------------------------------------------------------------------*
* FORM ADD_FIELDCAT
*---------------------------------------------------------------------*
FORM add_fieldcat
USING
iv_fieldname TYPE slis_fieldname
iv_text TYPE char40.
CLEAR gs_fieldcat.
gs_fieldcat-fieldname = iv_fieldname.
gs_fieldcat-seltext_m = iv_text.
APPEND gs_fieldcat TO gt_fieldcat.
ENDFORM.