【ABAP】native sql

原本是通过PO接口退给OA系统,但后来改了,直接写入OA系统。

首先要在ST04配置链接外部数据库

*&---------------------------------------------------------------------*
*& Report ZHRR035
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zhrr035.
DATA: gw_dbs TYPE dbcon-con_name VALUE 'OA'."ST04连接名
DATA:g_obj_sqldb  TYPE REF TO cx_sy_native_sql_error,g_error_text TYPE string.
DATA:lt_item TYPE TABLE OF zhrs028_item.
DATA:ls_item TYPE  zhrs028_item.
DATA:ls_sql TYPE  zhrs028_item.
DATA:lv_gjahr TYPE gjahr,
        lv_poper TYPE month,
        num  TYPE string,
        lv_orgeh TYPE char6,
        lv_i TYPE i.
  lv_gjahr = sy-datum+0(4).
  lv_poper = sy-datum+4(2) - 1.

INITIALIZATION.
*如果是1月份
  IF sy-datum+4(2)  = '01'.
    lv_gjahr = lv_gjahr - 1.
    lv_poper = '12'.
  ENDIF.

START-OF-SELECTION.
*取工资表数据
  SELECT *
    INTO CORRESPONDING FIELDS OF TABLE lt_item
    FROM zhrt009
    WHERE gjahr = lv_gjahr AND poper = lv_poper.

END-OF-SELECTION.
  TRY.        "连接数据库
      EXEC SQL.
        CONNECT TO :GW_DBS
      ENDEXEC.

    CATCH cx_sy_native_sql_error INTO g_obj_sqldb.
      CALL METHOD g_obj_sqldb->get_text
        RECEIVING
          result = g_error_text.
  ENDTRY.

    IF g_error_text IS INITIAL.
*设置连接
    EXEC SQL.
      SET CONNECTION 'OA'
    ENDEXEC.

   LOOP AT lt_item INTO ls_item.
    TRY .
        EXEC SQL.
           SELECT *
          FROM sap_hrgz
             INTO :ls_sql
         WHERE GJAHR   = :ls_item-GJAHR
         AND ZMONTH    = :ls_item-POPER
         AND PERNR  = :ls_item-PERNR
        ENDEXEC.
*查到有存在的数据就更新,没有就插入新的

IF sy-subrc = 0.
  PERFORM frm_update.
  ELSE.
    PERFORM frm_insert.
ENDIF.
      CATCH  cx_sy_native_sql_error INTO g_obj_sqldb.
        CALL METHOD g_obj_sqldb->get_text
          RECEIVING
            result = g_error_text.
    ENDTRY.

*提交
      EXEC SQL.
        COMMIT
      ENDEXEC.
      CLEAR:lv_i,lv_orgeh.
    ENDLOOP.
  ELSE.
    MESSAGE s899(mm) WITH g_error_text.
ENDIF.

*断开
  EXEC SQL.

    DISCONNECT :gw_dbs

  ENDEXEC.

*&---------------------------------------------------------------------*
*& Form appenddata
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_update .
         lv_i =  ls_item-orgeh.
        WRITE lv_i TO lv_orgeh LEFT-JUSTIFIED NO-GROUPING.   "外部数据库要求去掉前导0
  EXEC SQL.
      UPDATE sap_hrgz
         SET GJAHR        = :ls_item-GJAHR,
                ZMONTH     = :ls_item-POPER,
                PERNR       = :ls_item-PERNR,
                ENAME      = :ls_item-ENAME,
                ABKRS       = :ls_item-ABKRS,
                ATEXT        = :ls_item-ATEXT,
                ZZGSLB      = :ls_item-ZZGSLB,
                ORGEH      = :lv_orgeh,
                ORGTX      = :ls_item-ORGTX,
                PERSG       = :ls_item-PERSG,
                PTEXT        = :ls_item-PTEXT,
                PLANS       = :ls_item-PLANS,
                PLSTX        = :ls_item-PLSTX,
                PERSK       = :ls_item-PERSK,
                ZTEXT       = :ls_item-ZTEXT,
                ZSBTS       = :ls_item-ZSBTS,
                ZJBGZ       = :ls_item-ZJBGZ,
                ZKHGZ      = :ls_item-ZKHGZ,
                ZZJBGZ     = :ls_item-ZZJBGZ,
                ZYWTC      = :ls_item-ZYWTC,
                ZCT          = :ls_item-ZCT,
                ZGLJ        = :ls_item-ZGLJ,
                ZQQJ       = :ls_item-ZQQJ,
                ZQTJL      = :ls_item-ZQTJL,
                ZGWF      = :ls_item-ZGWF,
                ZTXBT      = :ls_item-ZTXBT,
                ZZQBT     = :ls_item-ZZQBT,
                ZZFBT      = :ls_item-ZZFBT,
                ZJTBT      = :ls_item-ZJTBT,
                ZYGJSF    = :ls_item-ZYGJSF,
                ZQTFL     = :ls_item-ZQTFL,
                ZKK1      = :ls_item-ZKK1,
                ZQT1      = :ls_item-ZQT1,
                ZQT3      = :ls_item-ZQT3,
                ZQT4      = :ls_item-ZQT4,
                ZQT5      = :ls_item-ZQT5,
                ZQQKK   = :ls_item-ZQQKK,
                ZYF         = :ls_item-ZYF,
                ZYLJ        = :ls_item-ZYLJ,
                ZYLF        = :ls_item-ZYLF,
                ZSYJ        = :ls_item-ZSYJ,
                ZGJJ        = :ls_item-ZGJJ,
                ZDBHZBX  = :ls_item-ZDBHZBX,
                ZZSSDF    = :ls_item-ZZSSDF,
                ZDKS       = :ls_item-ZDKS,
                ZQT2       = :ls_item-ZQT2,
                ZCJH       = :ls_item-ZCJH,
                ZSFJE       = :ls_item-ZSFJE
       WHERE GJAHR   = :ls_item-GJAHR
         AND ZMONTH    = :ls_item-POPER
         AND PERNR  = :ls_item-PERNR
    ENDEXEC.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form appenddata
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_insert .
         lv_i =  ls_item-orgeh.
        WRITE lv_i TO lv_orgeh LEFT-JUSTIFIED NO-GROUPING.
      PERFORM frm_add_kauid CHANGING num."获取编号,外部数据库设了ID为主键,自增。事务码SNRO
    ls_item-id = num.
    EXEC SQL.
      INSERT INTO sap_hrgz
      (ID,
      GJAHR,
          ZMONTH,
          PERNR,
          ENAME,
          ABKRS,
          ATEXT,
          ZZGSLB,
          ORGEH,
          ORGTX,
          PERSG,
          PTEXT,
          PLANS,
          PLSTX,
          PERSK,
          ZTEXT,
          ZSBTS,
          ZJBGZ,
          ZKHGZ,
          ZZJBGZ,
          ZYWTC,
          ZCT,
          ZGLJ,
          ZQQJ,
          ZQTJL,
          ZGWF,
          ZTXBT,
          ZZQBT,
          ZZFBT,
          ZJTBT,
          ZYGJSF,
          ZQTFL,
          ZKK1,
          ZQT1,
          ZQT3,
          ZQT4,
          ZQT5,
          ZQQKK,
          ZYF,
          ZYLJ,
          ZYLF,
          ZSYJ,
          ZGJJ,
          ZDBHZBX,
          ZZSSDF,
          ZDKS,
          ZQT2,
          ZCJH,
          ZSFJE
                )
      VALUES
      (:ls_item-ID,
      :ls_item-GJAHR,
       :ls_item-POPER,
        :ls_item-PERNR,
        :ls_item-ENAME,
        :ls_item-ABKRS,
        :ls_item-ATEXT,
       :ls_item-ZZGSLB,
        :lv_orgeh,
        :ls_item-ORGTX,
        :ls_item-PERSG,
         :ls_item-PTEXT,
        :ls_item-PLANS,
         :ls_item-PLSTX,
        :ls_item-PERSK,
        :ls_item-ZTEXT,
        :ls_item-ZSBTS,
         :ls_item-ZJBGZ,
        :ls_item-ZKHGZ,
        :ls_item-ZZJBGZ,
        :ls_item-ZYWTC,
        :ls_item-ZCT,
         :ls_item-ZGLJ,
        :ls_item-ZQQJ,
        :ls_item-ZQTJL,
       :ls_item-ZGWF,
        :ls_item-ZTXBT,
       :ls_item-ZZQBT,
        :ls_item-ZZFBT,
        :ls_item-ZJTBT,
       :ls_item-ZYGJSF,
       :ls_item-ZQTFL,
       :ls_item-ZKK1,
       :ls_item-ZQT1,
       :ls_item-ZQT3,
       :ls_item-ZQT4,
       :ls_item-ZQT5,
       :ls_item-ZQQKK,
       :ls_item-ZYF,
        :ls_item-ZYLJ,
        :ls_item-ZYLF,
        :ls_item-ZSYJ,
        :ls_item-ZGJJ,
       :ls_item-ZDBHZBX,
       :ls_item-ZZSSDF,
        :ls_item-ZDKS,
        :ls_item-ZQT2,
        :ls_item-ZCJH,
        :ls_item-ZSFJE
      )
    ENDEXEC.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_add_kauid
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      <-- NUM
*&---------------------------------------------------------------------*
 FORM frm_add_kauid  CHANGING num.

   CALL FUNCTION 'NUMBER_RANGE_ENQUEUE'
     EXPORTING
       object           = 'ZHRWAGES' "流水号对象
     EXCEPTIONS
       foreign_lock     = 1
       object_not_found = 2
       system_failure   = 3
       OTHERS           = 4.
   IF sy-subrc EQ 0.

     CALL FUNCTION 'NUMBER_GET_NEXT'
       EXPORTING
         nr_range_nr             = '1'                "维护的间隔号
         object                  = 'ZHRWAGES'          "流水号对象
*        QUANTITY                = '1'
*        SUBOBJECT               = ' '
*        TOYEAR                  = '0000'
*        IGNORE_BUFFER           = ' '
       IMPORTING
         number                  = num         "获得的流水号
*        QUANTITY                =
*        RETURNCODE              =
       EXCEPTIONS
         interval_not_found      = 1
         number_range_not_intern = 2
         object_not_found        = 3
         quantity_is_0           = 4
         quantity_is_not_1       = 5
         interval_overflow       = 6
         buffer_overflow         = 7
         OTHERS                  = 8.

*      *  ***将号码累加
     CALL FUNCTION 'NUMBER_RANGE_DEQUEUE '
       EXPORTING
         object           = 'ZHRWAGES'
       EXCEPTIONS
         object_not_found = 1
         OTHERS           = 2.
   ELSE.
     MESSAGE '未获取到流水号' TYPE 'E'.

   ENDIF.

 ENDFORM.

给TA买糖
共{{data.count}}人
人已赞赏
ABAPSAP

【ABAP】HR开发的几个函数

2021-6-23 19:58:28

ABAPSAP

【ABAP】自定义权限对象

2021-6-29 18:36:04

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索