SAP从SQL SERVER 从外部数据库取数

首先要知道DBCO连接名和外部系统的表名 参考代码如下

 

*&---------------------------------------------------------------------*
*& Report ZHRPT001
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ZHRPT001.
DATA: ZK LIKE ZHR_PT_ZK OCCURS 0 WITH HEADER LINE.
DATA: I0003 LIKE P0003 OCCURS 0 WITH HEADER LINE.
DATA: RETURN LIKE SY-SUBRC.
DATA: L_PERNR LIKE P0003-PERNR.
DATA: LS_PERNR TYPE C LENGTH 50.

SELECTION-SCREEN BEGIN OF BLOCK BLK1 WITH FRAME TITLE TEXT-M01.
SELECT-OPTIONS P_DATE FOR SY-DATUM.
SELECTION-SCREEN END OF BLOCK BLK1.
*---工号表
DATA : BEGIN OF WA,
         SSN    TYPE ZHR_PT_ZK-ZZ_PERNR,
         USERID TYPE ZZ_USERID,
       END OF WA.

DATA GT_DATA LIKE TABLE OF WA.
*---考勤表
DATA : BEGIN OF WA2,
         CHECKTIME TYPE CHAR40,
         USERID    TYPE ZZ_USERID,
       END OF WA2.

DATA GT_DATA2 LIKE TABLE OF WA2.

CONSTANTS P_CONNR TYPE DBCON-CON_NAME VALUE 'HCM_ZK'.

TRY.
    EXEC SQL.
      CONNECT TO :P_CONNR
    ENDEXEC.
    EXEC SQL.
      SET CONNECTION :P_CONNR
    ENDEXEC.

    IF SY-SUBRC EQ 0.
      PERFORM UPDATE_DATA.
      PERFORM MODIFY_DATA.
      PERFORM SAVE_DATA.
      WRITE: / '连接成功！数据已保存！'.
    ELSE.
      WRITE: / '连接失败！数据未保存！'.
    ENDIF.
ENDTRY.


*&---------------------------------------------------------------------*
*& Form UPDATE_DATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM UPDATE_DATA .
*---从USERINFO中取出员工号 再从CHECKINOUT取出打卡数据
  EXEC SQL PERFORMING APPEND_DATA.

    SELECT SSN,USERID
      INTO :WA
      FROM USERINFO
      WHERE SSN IN ('BJ1061','BJ0708')

  ENDEXEC.

  EXEC SQL PERFORMING APPEND_DATA.

    SELECT CHECKTIME,USERID
      INTO :WA2
      FROM CHECKINOUT

  ENDEXEC.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form DISPLAY_DATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM MODIFY_DATA.

  SORT GT_DATA.

  DELETE ADJACENT DUPLICATES FROM GT_DATA COMPARING ALL FIELDS.
*---通过USERID匹配打卡数据和员工号
  LOOP AT GT_DATA INTO WA.

    LOOP AT GT_DATA2 INTO WA2 WHERE USERID = WA-USERID. "AND CHECKTIME+0(4) = P_YEAR.

      ZK-ZZ_PERNR = WA-SSN.
      ZK-ZZ_USERID = WA2-USERID.
      ZK-DATUM = WA2-CHECKTIME+0(4) && WA2-CHECKTIME+5(2) && WA2-CHECKTIME+8(2).
      ZK-BEGUZ = WA2-CHECKTIME+11(2) && WA2-CHECKTIME+14(2) && '00'."WA2-CHECKTIME+17(2).

      APPEND ZK.

      CLEAR ZK.
      CLEAR WA2.

    ENDLOOP.

    CLEAR WA.

  ENDLOOP.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form APPEND_DATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM APPEND_DATA .
  APPEND WA TO GT_DATA.
  APPEND WA2 TO GT_DATA2.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SAVE_DATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM SAVE_DATA.

  DATA LT_ZHR_SHUA_TO_SAP LIKE TABLE OF ZHR_SHUA_TO_SAP WITH HEADER LINE.
*---存入SAP刷卡数据表
  CLEAR:LT_ZHR_SHUA_TO_SAP.
  REFRESH:LT_ZHR_SHUA_TO_SAP.
  LOOP AT ZK WHERE DATUM IN P_DATE.

    CASE ZK-ZZ_PERNR.
      WHEN 'BJ1061'.
        LT_ZHR_SHUA_TO_SAP-PERNR = '90002611'.
      WHEN 'BJ0708'.
        LT_ZHR_SHUA_TO_SAP-PERNR = '90001052'.
    ENDCASE.

    LT_ZHR_SHUA_TO_SAP-LDAYT = ZK-DATUM && ZK-BEGUZ.
    LT_ZHR_SHUA_TO_SAP-LDATE = ZK-DATUM .
    LT_ZHR_SHUA_TO_SAP-LTIME = ZK-BEGUZ.
    LT_ZHR_SHUA_TO_SAP-TR_DATE = SY-DATUM.
    LT_ZHR_SHUA_TO_SAP-TR_TIME = SY-UZEIT.
    LT_ZHR_SHUA_TO_SAP-UNAME = SY-UNAME.

    APPEND LT_ZHR_SHUA_TO_SAP.
    CLEAR LT_ZHR_SHUA_TO_SAP.

  ENDLOOP.

  MODIFY ZHR_SHUA_TO_SAP FROM TABLE LT_ZHR_SHUA_TO_SAP.
  IF SY-SUBRC = 0.
    COMMIT WORK AND WAIT.
  ELSE.
    ROLLBACK WORK.
  ENDIF.

  LOOP AT ZK.
    CLEAR: L_PERNR,I0003,I0003[].
    L_PERNR = ZK-ZZ_PERNR.
    CALL FUNCTION 'HR_READ_INFOTYPE_AUTHC_DISABLE'.
    CALL FUNCTION 'HR_READ_INFOTYPE'
      EXPORTING
*       TCLAS           = 'A'
        PERNR           = L_PERNR
        INFTY           = '0003'
        BEGDA           = SY-DATUM
        ENDDA           = SY-DATUM
*       BYPASS_BUFFER   = ' '
*       LEGACY_MODE     = ' '
      IMPORTING
        SUBRC           = RETURN
      TABLES
        INFTY_TAB       = I0003
      EXCEPTIONS
        INFTY_NOT_FOUND = 1
        OTHERS          = 2.

    READ TABLE I0003 INDEX 1.

    IF ZK-DATUM LT I0003-BDERR.
      UPDATE PA0003 SET BDERR = ZK-DATUM WHERE PERNR = L_PERNR.
    ENDIF.

  ENDLOOP.

ENDFORM.

*Text elements
*----------------------------------------------------------
* M01 筛选条件


*Selection texts
*----------------------------------------------------------
* P_DATE         抽取时间范围