material classification,native sql

 *&material classification,native sql  test

report  zmmr0204.

data:begin of it_data occurs 0,
       matnr like marc-matnr,
       len(13),
       wide(13),
       high(13),
       weight(13),
     end   of it_data.
data: bapi1003_alloc_values_num  type table of bapi1003_alloc_values_num,
      bapi1003_alloc_values_char type table of bapi1003_alloc_values_char
      with header line,
      bapi1003_alloc_values_curr type table of bapi1003_alloc_values_curr,
      return type table of bapiret2 with header line.

data:begin of it_update_err occurs 0,
       matnr like mara-matnr,
     end   of it_update_err.

selection-screen: begin of block b1 with frame title text-001.
parameters: p_file like rlgrap-filename obligatory,
            p_dbname like  dbcon-con_name default 'CRMSEV'.
selection-screen begin of line.
selection-screen comment (79)   text-002.
selection-screen end of line.
selection-screen begin of line.
selection-screen comment (79)   text-003.
selection-screen end of line.
selection-screen:end of block b1.

************************************************************************
* AT SELECTION-SCREEN
************************************************************************
at selection-screen on value-request for p_file .
  perform sub_query_filename using '文本文件,*.txt' p_file .

start-of-selection.
*& 从txt文件上载物料清单以及特性值
  perform upload_file.
*& 增加或更改分类并修改特性值
  perform save_to_sap.
*& 同步到OSN系统
  perform save_to_osn.
*---------------------------------------------------------------------*
*       FORM SUB_QUERY_FILENAME                                       *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  PR_FILE                                                       *
*---------------------------------------------------------------------*
form sub_query_filename using    pr_mask
                        changing pr_file.

  data: tmp_filename like rlgrap-filename,
        tmp_mask     like rlgrap-filename.

*Filter for Fileselector
  concatenate ','
              pr_mask
              ',所有文件,*.*.'
              into tmp_mask.
*Pop-up a explorer screen to select the input file
  call function 'WS_FILENAME_GET'
       exporting
*           def_path         = p_file
            mask             = tmp_mask
            mode             = 'O'
       importing
            filename         = tmp_filename
       exceptions
            inv_winsys       = 01
            no_batch         = 02
            selection_cancel = 03
            selection_error  = 04.
  case sy-subrc.
    when 0.
      pr_file = tmp_filename.
    when others.
  endcase.
endform.                    "
*&---------------------------------------------------------------------*
*&      Form  UPLOAD_FILE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form upload_file .

  call function 'ZWS_UPLOAD'
    exporting
      filename                = p_file
      filetype                = 'DAT'
    tables
      data_tab                = it_data
    exceptions
      conversion_error        = 1
      invalid_table_width     = 2
      invalid_type            = 3
      no_batch                = 4
      unknown_error           = 5
      gui_refuse_filetransfer = 6
      others                  = 7.

  if sy-subrc <> 0.
    write: at /1 '文件导入错误，返回代码为：', sy-subrc.
    stop.
  endif.

endform.                    " UPLOAD_FILE
*&---------------------------------------------------------------------*
*&      Form  SAVE_TO_SAP
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form save_to_sap .
  data:
  l_matnr like bapi1003_key-object,
  l_tabix(2) type n,
  field(5).
*& 因更新时,会覆盖掉以前的特性,所以先提取,没有变化的,还用以前的.
  loop at it_data.

    refresh:return.
    l_matnr = it_data-matnr.
    call function 'BAPI_OBJCL_GETDETAIL'
      exporting
        objectkey       = l_matnr
        objecttable     = 'MARA'
        classnum        = 'MAT_CS_BASE'
        classtype       = 'M01'
      tables
        allocvaluesnum  = bapi1003_alloc_values_num
        allocvalueschar = bapi1003_alloc_values_char
        allocvaluescurr = bapi1003_alloc_values_curr
        return          = return.
*& 对于已经分类MAT_CS_BASE的,进行修改,没有的进行创建

    refresh:bapi1003_alloc_values_char.
    bapi1003_alloc_values_char-charact = 'MAT_CS_09'.
    bapi1003_alloc_values_char-value_char = it_data-len .
    append bapi1003_alloc_values_char.

    bapi1003_alloc_values_char-charact = 'MAT_CS_10'.
    bapi1003_alloc_values_char-value_char = it_data-wide .
    append bapi1003_alloc_values_char.

    bapi1003_alloc_values_char-charact = 'MAT_CS_11'.
    bapi1003_alloc_values_char-value_char = it_data-high .
    append bapi1003_alloc_values_char.

    bapi1003_alloc_values_char-charact = 'MAT_CS_12'.
    bapi1003_alloc_values_char-value_char = it_data-weight .
    append bapi1003_alloc_values_char.

    read table return with key type = 'S'.
    if sy-subrc = 0.
      perform change_character using l_matnr.
    else.
*& 分类不存在的，进行创建
      perform create_character using l_matnr.

    endif.

  endloop.

endform.                    " SAVE_TO_SAP
*&---------------------------------------------------------------------*
*&      Form  SAVE_TO_OSN
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form save_to_osn .

  data:begin of it_err occurs 0,
         matnr like mara-matnr,
       end   of it_err.

  data:erro_object type ref to cx_sy_native_sql_error,
       erro_text   type string.

  EXEC SQL.
    CONNECT TO :p_dbname
  ENDEXEC.
  if sy-subrc = 0.
    loop at it_data.
      clear:erro_object,erro_text.
      shift it_data-len left deleting leading '0'.
      shift it_data-wide left deleting leading '0'.
      shift it_data-high left deleting leading '0'.
      shift it_data-weight left deleting leading '0'.
      try.
          EXEC SQL.
            update ECC_FND.PRODUCT_MODULE_INFO set MODULE_LENGTH = :it_data-len,
                                                   MODULE_WIDTH = :it_data-wide,
                                                   MODULE_HIGH  = :it_data-high,
                                                   MODULE_WEIGHT  = :it_data-weight
        where MODULE_CODE = :it_data-matnr

          ENDEXEC.
*& 处理意外
        catch cx_sy_native_sql_error into erro_object.
          erro_text = erro_object->get_text( ).

          if erro_text is initial.
            EXEC SQL.
              commit
            ENDEXEC.
          else.
            it_err-matnr = it_data-matnr.
            collect it_err.
          endif.

      endtry.
    endloop.

  else.
    message '数据库连接出错，请联系系统管理员！' type 'E'.
  endif.

  if not it_err[] is initial.
    write:/ '以下物料同步到OSN时出错！'.
    loop at it_err.
      write:/ it_err-matnr.
    endloop.
  endif.

  if not it_update_err[] is initial.
    write:/ '以下物料创建或更改物料分类时出错，有可能此物料当时正在被其他事物处理！'.
    loop at it_update_err.
      write:/ it_update_err-matnr.
    endloop.
  endif.

  if  it_err[] is initial and
      it_update_err[] is initial.
    message '此附件的物料清单分类已经创建或更改，并同步到了OSN！' type 'S'.
  endif.

endform.                    " SAVE_TO_OSN
*&---------------------------------------------------------------------*
*&      Form  CHANGE_CHARACTER
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form change_character using p_matnr.
  refresh:return.
  call function 'BAPI_OBJCL_CHANGE'
    exporting
      objectkey          = p_matnr
      objecttable        = 'MARA'
      classnum           = 'MAT_CS_BASE'
      classtype          = 'M01'
    tables
      allocvaluesnumnew  = bapi1003_alloc_values_num
      allocvaluescharnew = bapi1003_alloc_values_char
      allocvaluescurrnew = bapi1003_alloc_values_curr
      return             = return.

  read table return with key  type = 'E'.
  if sy-subrc <> 0.
    call function 'BAPI_TRANSACTION_COMMIT'.
  else.

    call function 'BAPI_TRANSACTION_ROLLBACK'.
    it_update_err-matnr = p_matnr.
    collect it_update_err.

  endif.

endform.                    " CHANGE_CHARACTER
*&---------------------------------------------------------------------*
*&      Form  CREATE_CHARACTER
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_L_MATNR  text
*----------------------------------------------------------------------*
form create_character  using p_matnr.

  refresh:return.
  call function 'BAPI_OBJCL_CREATE'
    exporting
      objectkeynew    = p_matnr
      objecttablenew  = 'MARA'
      classnumnew     = 'MAT_CS_BASE'
      classtypenew    = 'M01'
    tables
      allocvalueschar = bapi1003_alloc_values_char
      return          = return.

  read table return with key type = 'E'.
  if sy-subrc <> 0.
    call function 'BAPI_TRANSACTION_COMMIT'.
  else.
    call function 'BAPI_TRANSACTION_ROLLBACK'.
    it_update_err-matnr = p_matnr.
    collect it_update_err.
  endif.

endform.                    " CREATE_CHARACTER