*&---------------------------------------------------------------------* *& 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_yg FROM pa0001 WHERE begda <= sy-datum AND endda >= sy-datum AND 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' EXPORTING pernr = gs_yg-pernr begda = l_datum1 endda = l_datum2 IMPORTING return = l_return TABLES perws = 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 for select Emp_id from EMP_WORK_SCHEDULE ENDEXEC. DO. EXEC SQL. FETCH NEXT dbcur INTO :L_Emp_id ENDEXEC. IF sy-subrc <> 0. EXIT. ENDIF. ENDDO. EXEC SQL. CLOSE dbcur ENDEXEC. * 获取开始插入时间 GET TIME. CONCATENATE sy-datum+0(4) sy-datum+4(2) sy-datum+6(2) INTO tp_time1 SEPARATED BY '-'. CLEAR l_time. CONCATENATE sy-uzeit+0(2) sy-uzeit+2(2) sy-uzeit+4(2) INTO l_time SEPARATED BY ':'. CONCATENATE tp_time1 l_time INTO tp_time1 SEPARATED 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. COMMIT ENDEXEC. IF l_emp_id IS NOT INITIAL. EXEC SQL. DELETE FROM EMP_WORK_SCHEDULE ENDEXEC. 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' "获取时间戳 EXPORTING i_datlo = sy-datum i_timlo = sy-uzeit i_tzone = 'UTC+8' IMPORTING e_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-pernr gs_db-datum gs_db-tprog l_timestamp INTO wa_log-msg SEPARATED 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_time2 SEPARATED BY '-'. CLEAR l_time. GET TIME. CONCATENATE sy-uzeit+0(2) sy-uzeit+2(2) sy-uzeit+4(2) INTO l_time SEPARATED BY ':'. CONCATENATE tp_time2 l_time INTO tp_time2 SEPARATED 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. COMMIT ENDEXEC. WRITE: '插入数据成功！'. ELSE. EXEC SQL. ROLLBACK ENDEXEC. WRITE: '插入数据失败！'. ENDIF. EXEC SQL. DISCONNECT :CON_ORA_NAME ENDEXEC. CATCH cx_sy_native_sql_error INTO exc_ref. error_text = exc_ref->get_text( ). EXEC SQL. ROLLBACK ENDEXEC. 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' EXPORTING wa_log = wa EXCEPTIONS nodata = 1 OTHERS = 2. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. ENDFORM. " WRITE_BPM_LOG