*&---------------------------------------------------------------------* *& Report ZHR_BPM11 業務行事日歷同步 *& *&---------------------------------------------------------------------* *&創建者：caizjian 日期：20141010 *& *&---------------------------------------------------------------------*REPORT zhr_bpm11.TYPES:BEGIN OF typ_db,pernr TYPE pa0001-pernr, "員工號datum TYPE ptpsp-datum, "計劃工作時間tprog TYPE ptpsp-tprog, "工作計劃規則timestamp TYPE timestamp, "JOB同步時間END OF typ_db.TYPES:BEGIN OF typ_yg,pernr TYPE pa0001-pernr,END OF typ_yg.DATA:gt_db TYPE TABLE OF typ_db,gs_db LIKE LINE OF gt_db,gt_yg TYPE TABLE OF typ_yg,gs_yg LIKE LINE OF gt_yg.DATA:con_ora_name TYPE dbcon_name VALUE 'BPMFLOW',tp_time1(19) TYPE c, "開始插入時間tp_time2(19) TYPE c. "結束插入時間 data: exc_ref TYPE ref TO cx_sy_native_sql_error,error_text TYPE string.INITIALIZATION.IF sy-mandt EQ '800'.MOVE 'PORTAL' TO con_ora_name.ENDIF.START-OF-SELECTION.PERFORM get_data.PERFORM frm_connect. *&---------------------------------------------------------------------* *& Form GET_DATA *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM get_data .DATA:l_datum1 TYPE sy-datum,l_datum2 TYPE sy-datum,l_return TYPE bapireturn1,lt_perws TYPE TABLE OF ptpsp,ls_perws LIKE LINE OF lt_perws.*員工號SELECT pernr INTO TABLE gt_ygFROM pa0001WHERE begda <= sy-datumAND endda >= sy-datumAND persk LIKE '%3'.l_datum1 = sy-datum.l_datum2 = l_datum1 + 2.LOOP AT gt_yg INTO gs_yg.CLEAR:lt_perws,l_return.CALL FUNCTION 'ZHR_GET_DWS'EXPORTINGpernr = gs_yg-pernrbegda = l_datum1endda = l_datum2IMPORTINGreturn = l_returnTABLESperws = lt_perws.LOOP AT lt_perws INTO ls_perws.gs_db-pernr = gs_yg-pernr.gs_db-datum = ls_perws-datum.gs_db-tprog = ls_perws-tprog.APPEND gs_db TO gt_db.CLEAR:gs_db, ls_perws.ENDLOOP.ENDLOOP.ENDFORM. " GET_DATA *&---------------------------------------------------------------------* *& Form FRM_CONNECT *&---------------------------------------------------------------------* FORM frm_connect .DATA:l_emp_id TYPE char8,l_time TYPE char8,l_error TYPE c.DATA:wa_log TYPE zhrbpm_log,l_timestamp TYPE string.TRY .EXEC SQL.CONNECT TO: CON_ORA_NAME.ENDEXEC.IF sy-subrc <> 0 . * 出錯時RAISE EXCEPTION TYPE cx_sy_native_sql_error.ENDIF.EXEC SQL.OPEN dbcur forselect Emp_id from EMP_WORK_SCHEDULEENDEXEC.DO.EXEC SQL.FETCH NEXT dbcur INTO :L_Emp_idENDEXEC.IF sy-subrc <> 0.EXIT.ENDIF.ENDDO.EXEC SQL.CLOSE dbcurENDEXEC.* 獲取開始插入時間GET TIME.CONCATENATE sy-datum+0(4)sy-datum+4(2)sy-datum+6(2)INTO tp_time1SEPARATED BY '-'.CLEAR l_time.CONCATENATE sy-uzeit+0(2)sy-uzeit+2(2)sy-uzeit+4(2)INTO l_timeSEPARATED BY ':'.CONCATENATE tp_time1l_timeINTO tp_time1SEPARATED BY space.EXEC SQL.UPDATE SYNC_RECORD_TBL set START_TIME = to_date(:TP_TIME1,'YYYY-MM-DD HH24:MI:SS') WHERE SID = '13'ENDEXEC.EXEC SQL.COMMITENDEXEC.IF l_emp_id IS NOT INITIAL.EXEC SQL.DELETE FROM EMP_WORK_SCHEDULEENDEXEC.ENDIF.IF sy-subrc <> 0.l_error = 'X'.ENDIF.IF l_error IS INITIAL.LOOP AT gt_db INTO gs_db.IF gs_db-pernr IS INITIAL OR gs_db-datum IS INITIAL.CLEAR gs_db.CONTINUE.ENDIF.CALL FUNCTION 'CONVERT_INTO_TIMESTAMP' "獲取時間戳EXPORTINGi_datlo = sy-datumi_timlo = sy-uzeiti_tzone = 'UTC+8'IMPORTINGe_timestamp = gs_db-timestamp.EXEC SQL.INSERT INTO EMP_WORK_SCHEDULE(EMP_ID,WORK_DATE,WORK_RULE,CREATE_DATE)values:(:gs_db-PERNR,:gs_db-DATUM,:gs_db-TPROG,:gs_db-timestamp)ENDEXEC.IF sy-subrc <> 0.l_error = 'X'." CRQ000000003316-HR拋BPM出錯寫日志wa_log-prog = sy-repid."當前程序名wa_log-tabnam = 'EMP_WORK_SCHEDULE'."表名l_timestamp = gs_db-timestamp.CONCATENATE gs_db-pernrgs_db-datumgs_db-tprogl_timestampINTO wa_log-msgSEPARATED BY '/'.PERFORM write_bpm_log USING wa_log.EXIT.ENDIF.ENDLOOP.ENDIF.IF l_error IS INITIAL. * 獲取插入結束時間CONCATENATE sy-datum+0(4)sy-datum+4(2)sy-datum+6(2)INTO tp_time2SEPARATED BY '-'.CLEAR l_time.GET TIME.CONCATENATE sy-uzeit+0(2)sy-uzeit+2(2)sy-uzeit+4(2)INTO l_timeSEPARATED BY ':'.CONCATENATE tp_time2l_timeINTO tp_time2SEPARATED BY space.EXEC SQL.UPDATE SYNC_RECORD_TBL set END_TIME = to_date(:TP_TIME2,'YYYY-MM-DD HH24:MI:SS') WHERE SID = '13'ENDEXEC.EXEC SQL.COMMITENDEXEC.WRITE: '插入數據成功！'.ELSE.EXEC SQL.ROLLBACKENDEXEC.WRITE: '插入數據失敗！'.ENDIF.EXEC SQL.DISCONNECT :CON_ORA_NAMEENDEXEC.CATCH cx_sy_native_sql_error INTO exc_ref.error_text = exc_ref->get_text( ).EXEC SQL.ROLLBACKENDEXEC.IF sy-batch = 'X'.WRITE: '插入數據失敗！',/ error_text.ELSE.MESSAGE error_text TYPE 'I'.ENDIF.LEAVE PROGRAM.ENDTRY.ENDFORM. " FRM_CONNECT *&---------------------------------------------------------------------* *& Form WRITE_BPM_LOG *&---------------------------------------------------------------------* FORM write_bpm_log USING wa TYPE zhrbpm_log.CALL FUNCTION 'ZHR_BPM_WLOG'EXPORTINGwa_log = waEXCEPTIONSnodata = 1OTHERS = 2.IF sy-subrc <> 0.MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgnoWITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.ENDIF.ENDFORM. " WRITE_BPM_LOG

?

轉載于:https://www.cnblogs.com/caizjian/p/4027173.html

總結

以上是生活随笔為你收集整理的一个关于native sql的程序的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。