Mẫu dữ liệu xử lý bảng lương

/* Activity 1: Time Sheet Submitted */
SELECT
    CONCAT(T.PERNR, CONCAT('-', T.PABRJ, T.PABRP)) AS PayrollRecord,
    'Time Sheet Submitted' AS Activity,
    T.BEGDA AS EventTimestamp,
    'SAP_ECP' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    T.ABKRS AS PayrollArea,
    'Permanent' AS EmployeeType,
    T.PABRP AS PayPeriod,
    CASE WHEN T.PABRP LIKE '9%' THEN 'Off-Cycle' ELSE 'Regular' END AS PayCycleFrequency,
    0.00 AS GrossPayAmount,
    T.UNAME AS PayrollSpecialist,
    O.KOSTL AS CostCenter,
    'True' AS IsAutomated
FROM PA2001 AS T
LEFT JOIN PA0001 AS O ON T.PERNR = O.PERNR AND O.ENDDA = '99991231'
WHERE T.AEDTM BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

/* Activity 2: Time Sheet Approved */
SELECT
    CONCAT(T.PERNR, CONCAT('-', T.PABRJ, T.PABRP)) AS PayrollRecord,
    'Time Sheet Approved' AS Activity,
    T.AEDTM AS EventTimestamp,
    'SAP_ECP' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    T.ABKRS AS PayrollArea,
    'Permanent' AS EmployeeType,
    T.PABRP AS PayPeriod,
    CASE WHEN T.PABRP LIKE '9%' THEN 'Off-Cycle' ELSE 'Regular' END AS PayCycleFrequency,
    0.00 AS GrossPayAmount,
    T.UNAME AS PayrollSpecialist,
    O.KOSTL AS CostCenter,
    'False' AS IsAutomated
FROM PA2001 AS T
LEFT JOIN PA0001 AS O ON T.PERNR = O.PERNR AND O.ENDDA = '99991231'
WHERE T.AEDTM BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

/* Activity 3: Incentive Data Imported */
SELECT
    CONCAT(I.PERNR, CONCAT('-', I.PABRJ, I.PABRP)) AS PayrollRecord,
    'Incentive Data Imported' AS Activity,
    I.AEDTM AS EventTimestamp,
    'SAP_ECP' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    I.ABKRS AS PayrollArea,
    'Permanent' AS EmployeeType,
    I.PABRP AS PayPeriod,
    'Regular' AS PayCycleFrequency,
    I.BETRG AS GrossPayAmount,
    I.UNAME AS PayrollSpecialist,
    O.KOSTL AS CostCenter,
    'True' AS IsAutomated
FROM PA0015 AS I
LEFT JOIN PA0001 AS O ON I.PERNR = O.PERNR AND O.ENDDA = '99991231'
WHERE I.AEDTM BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

/* Activity 4: Payroll Process Initialized */
SELECT
    CONCAT('N/A', CONCAT('-', P.PABRJ, P.PABRP)) AS PayrollRecord,
    'Payroll Process Initialized' AS Activity,
    P.CREATED_TS AS EventTimestamp,
    'SAP_ECP' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    'ALL' AS PayrollArea,
    NULL AS EmployeeType,
    P.PABRP AS PayPeriod,
    'Regular' AS PayCycleFrequency,
    0.00 AS GrossPayAmount,
    P.CREATED_BY AS PayrollSpecialist,
    NULL AS CostCenter,
    'False' AS IsAutomated
FROM PYC_D_PYP AS P
WHERE P.CREATED_TS BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

/* Activity 5: Payroll Simulation Executed */
SELECT
    CONCAT('N/A', CONCAT('-', S.PABRJ, S.PABRP)) AS PayrollRecord,
    'Payroll Simulation Executed' AS Activity,
    S.END_TS AS EventTimestamp,
    'SAP_ECP' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    'ALL' AS PayrollArea,
    NULL AS EmployeeType,
    S.PABRP AS PayPeriod,
    'Regular' AS PayCycleFrequency,
    0.00 AS GrossPayAmount,
    S.EXEC_USER AS PayrollSpecialist,
    NULL AS CostCenter,
    'True' AS IsAutomated
FROM PYC_D_BINST AS S
JOIN PYC_D_STEP AS D ON S.STEP_ID = D.ID
WHERE D.NAME LIKE '%Simulate%' AND S.END_TS BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

/* Activity 6: Audit Exception Flagged */
SELECT
    CONCAT(A.RO_ID, CONCAT('-', P.PABRJ, P.PABRP)) AS PayrollRecord,
    'Audit Exception Flagged' AS Activity,
    A.CREATED_TS AS EventTimestamp,
    'SAP_ECP' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    P.ABKRS AS PayrollArea,
    'Permanent' AS EmployeeType,
    P.PABRP AS PayPeriod,
    'Regular' AS PayCycleFrequency,
    0.00 AS GrossPayAmount,
    'SYSTEM' AS PayrollSpecialist,
    NULL AS CostCenter,
    'True' AS IsAutomated
FROM PYC_D_ALERT AS A
JOIN PYC_D_PYP AS P ON A.PYP_ID = P.ID
WHERE A.CREATED_TS BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

/* Activity 7: Data Correction Performed */
SELECT
    CONCAT(C.OBJECTID, CONCAT('-', P.PABRJ, P.PABRP)) AS PayrollRecord,
    'Data Correction Performed' AS Activity,
    TO_TIMESTAMP(CONCAT(C.UDATE, C.UTIME)) AS EventTimestamp,
    'SAP_ECP' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    P.ABKRS AS PayrollArea,
    'Permanent' AS EmployeeType,
    P.PABRP AS PayPeriod,
    'Regular' AS PayCycleFrequency,
    0.00 AS GrossPayAmount,
    C.USERNAME AS PayrollSpecialist,
    NULL AS CostCenter,
    'False' AS IsAutomated
FROM CDHDR AS C
JOIN HRPY_RGDIR AS P ON C.OBJECTID = P.PERNR
WHERE C.TCODE IN ('PA30', 'PA40') AND C.UDATE BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

/* Activity 8: Gross Pay Calculated */
SELECT
    CONCAT(R.PERNR, CONCAT('-', R.PABRJ, R.PABRP)) AS PayrollRecord,
    'Gross Pay Calculated' AS Activity,
    TO_TIMESTAMP(CONCAT(R.RUNDT, R.RUNTM)) AS EventTimestamp,
    'SAP_ECP' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    R.ABKRS AS PayrollArea,
    'Permanent' AS EmployeeType,
    R.PABRP AS PayPeriod,
    CASE WHEN R.PABRP LIKE '9%' THEN 'Off-Cycle' ELSE 'Regular' END AS PayCycleFrequency,
    0.00 AS GrossPayAmount,
    R.UNAME AS PayrollSpecialist,
    O.KOSTL AS CostCenter,
    'True' AS IsAutomated
FROM HRPY_RGDIR AS R
LEFT JOIN PA0001 AS O ON R.PERNR = O.PERNR AND O.ENDDA = '99991231'
WHERE R.RUNDT BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

/* Activity 9: Benefit Deductions Applied */
SELECT
    CONCAT(R.PERNR, CONCAT('-', R.PABRJ, R.PABRP)) AS PayrollRecord,
    'Benefit Deductions Applied' AS Activity,
    ADD_SECONDS(TO_TIMESTAMP(CONCAT(R.RUNDT, R.RUNTM)), 1) AS EventTimestamp,
    'SAP_ECP' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    R.ABKRS AS PayrollArea,
    'Permanent' AS EmployeeType,
    R.PABRP AS PayPeriod,
    CASE WHEN R.PABRP LIKE '9%' THEN 'Off-Cycle' ELSE 'Regular' END AS PayCycleFrequency,
    0.00 AS GrossPayAmount,
    R.UNAME AS PayrollSpecialist,
    O.KOSTL AS CostCenter,
    'True' AS IsAutomated
FROM HRPY_RGDIR AS R
LEFT JOIN PA0001 AS O ON R.PERNR = O.PERNR AND O.ENDDA = '99991231'
WHERE R.RUNDT BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

/* Activity 10: Payroll Record Approved */
SELECT
    CONCAT('N/A', CONCAT('-', S.PABRJ, S.PABRP)) AS PayrollRecord,
    'Payroll Record Approved' AS Activity,
    S.END_TS AS EventTimestamp,
    'SAP_ECP' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    'ALL' AS PayrollArea,
    NULL AS EmployeeType,
    S.PABRP AS PayPeriod,
    'Regular' AS PayCycleFrequency,
    0.00 AS GrossPayAmount,
    S.EXEC_USER AS PayrollSpecialist,
    NULL AS CostCenter,
    'False' AS IsAutomated
FROM PYC_D_BINST AS S
JOIN PYC_D_STEP AS D ON S.STEP_ID = D.ID
WHERE D.NAME LIKE '%Check%' AND S.STATUS = 'OK' AND S.END_TS BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

/* Activity 11: Bank Transfer File Generated */
SELECT
    CONCAT(P.PERNR, CONCAT('-', P.PABRJ, P.PABRP)) AS PayrollRecord,
    'Bank Transfer File Generated' AS Activity,
    TO_TIMESTAMP(CONCAT(T.TSDAT, T.TSTIM)) AS EventTimestamp,
    'SAP_ECP' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    P.ABKRS AS PayrollArea,
    'Permanent' AS EmployeeType,
    P.PABRP AS PayPeriod,
    'Regular' AS PayCycleFrequency,
    0.00 AS GrossPayAmount,
    T.USRID AS PayrollSpecialist,
    O.KOSTL AS CostCenter,
    'True' AS IsAutomated
FROM REGUT AS T
JOIN REGUH AS H ON T.LAUFD = H.LAUFD AND T.LAUFI = H.LAUFI
JOIN HRPY_RGDIR AS P ON H.PERNR = P.PERNR
LEFT JOIN PA0001 AS O ON P.PERNR = O.PERNR AND O.ENDDA = '99991231'
WHERE T.TSDAT BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

/* Activity 12: Payment Executed */
SELECT
    CONCAT(P.PERNR, CONCAT('-', P.PABRJ, P.PABRP)) AS PayrollRecord,
    'Payment Executed' AS Activity,
    TO_TIMESTAMP(CONCAT(H.LAUFD, '120000')) AS EventTimestamp,
    'SAP_ECP' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    P.ABKRS AS PayrollArea,
    'Permanent' AS EmployeeType,
    P.PABRP AS PayPeriod,
    'Regular' AS PayCycleFrequency,
    H.RBETR AS GrossPayAmount,
    H.ZNME1 AS PayrollSpecialist,
    O.KOSTL AS CostCenter,
    'True' AS IsAutomated
FROM REGUH AS H
JOIN HRPY_RGDIR AS P ON H.PERNR = P.PERNR
LEFT JOIN PA0001 AS O ON P.PERNR = O.PERNR AND O.ENDDA = '99991231'
WHERE H.LAUFD BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

/* Activity 13: Pay Slip Published */
SELECT
    CONCAT('N/A', CONCAT('-', S.PABRJ, S.PABRP)) AS PayrollRecord,
    'Pay Slip Published' AS Activity,
    S.END_TS AS EventTimestamp,
    'SAP_ECP' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    'ALL' AS PayrollArea,
    NULL AS EmployeeType,
    S.PABRP AS PayPeriod,
    'Regular' AS PayCycleFrequency,
    0.00 AS GrossPayAmount,
    S.EXEC_USER AS PayrollSpecialist,
    NULL AS CostCenter,
    'True' AS IsAutomated
FROM PYC_D_BINST AS S
JOIN PYC_D_STEP AS D ON S.STEP_ID = D.ID
WHERE D.NAME LIKE '%Remuneration%' AND S.END_TS BETWEEN '[START_DATE]' AND '[END_DATE]'

UNION ALL

/* Activity 14: Tax Filing Completed */
SELECT
    CONCAT('N/A', CONCAT('-', S.PABRJ, S.PABRP)) AS PayrollRecord,
    'Tax Filing Completed' AS Activity,
    S.END_TS AS EventTimestamp,
    'SAP_ECP' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    'ALL' AS PayrollArea,
    NULL AS EmployeeType,
    S.PABRP AS PayPeriod,
    'Regular' AS PayCycleFrequency,
    0.00 AS GrossPayAmount,
    S.EXEC_USER AS PayrollSpecialist,
    NULL AS CostCenter,
    'True' AS IsAutomated
FROM PYC_D_BINST AS S
JOIN PYC_D_STEP AS D ON S.STEP_ID = D.ID
WHERE D.NAME LIKE '%Tax%' AND S.END_TS BETWEEN '[START_DATE]' AND '[END_DATE]'

REPORT Z_PROCESSMINING_PAYROLL_EXT.

TABLES: pernr, hrpy_rgdir, catsdb, pa0015, reguh.

TYPES: BEGIN OF ty_event_log,
         payroll_record    TYPE string,
         activity          TYPE string,
         event_timestamp   TYPE string,
         source_system     TYPE string,
         last_data_update  TYPE string,
         payroll_area      TYPE abkrs,
         employee_type     TYPE persg,
         pay_period        TYPE pabrj,
         pay_cycle_freq    TYPE string,
         gross_pay_amount  TYPE string,
         payroll_specialist TYPE usnam,
         cost_center       TYPE kostl,
         is_automated      TYPE string,
       END OF ty_event_log.

DATA: gt_output TYPE TABLE OF ty_event_log,
      gs_output TYPE ty_event_log,
      gt_rgdir  TYPE TABLE OF hrpy_rgdir,
      gs_rgdir  TYPE hrpy_rgdir,
      gt_cats   TYPE TABLE OF catsdb,
      gs_cats   TYPE catsdb,
      gt_p0015  TYPE TABLE OF pa0015,
      gs_p0015  TYPE pa0015,
      gt_reguh  TYPE TABLE OF reguh,
      gs_reguh  TYPE reguh,
      gv_tstamp TYPE timestamp,
      gv_date   TYPE d,
      gv_time   TYPE t,
      gv_pernr  TYPE pernr_d.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECT-OPTIONS: s_pernr FOR pernr-pernr,
                s_date  FOR sy-datum OBLIGATORY.
PARAMETERS: p_abkrs TYPE abkrs OBLIGATORY DEFAULT '99'.
SELECTION-SCREEN END OF BLOCK b1.

START-OF-SELECTION.

  " 1. Fetch Payroll Directory (The Core Process Driver)
  SELECT * FROM hrpy_rgdir INTO TABLE gt_rgdir
    WHERE pernr IN s_pernr
      AND fpper LIKE '%'
      AND cdate IN s_date.

  IF gt_rgdir IS INITIAL.
    WRITE: / 'No payroll results found for selection.'.
    EXIT.
  ENDIF.

  SORT gt_rgdir BY pernr seqnr.

  " Loop through Payroll Directory to build events
  LOOP AT gt_rgdir INTO gs_rgdir.
    CLEAR gs_output.
    CONCATENATE gs_rgdir-pernr '-' gs_rgdir-seqnr INTO gs_output-payroll_record.
    gs_output-source_system = 'SAP_ECP'.
    gs_output-last_data_update = sy-datum.
    gs_output-payroll_area = p_abkrs.
    gs_output-pay_period = gs_rgdir-fpper.
    gs_output-payroll_specialist = gs_rgdir-uname.

    " --- Activity: Payroll Process Initialized ---
    " Derived from the run date start
    gs_output-activity = 'Payroll Process Initialized'.
    CONCATENATE gs_rgdir-cdate gs_rgdir-ctime INTO gs_output-event_timestamp SEPARATED BY space.
    gs_output-is_automated = 'TRUE'.
    APPEND gs_output TO gt_output.

    " --- Activity: Payroll Simulation Executed ---
    IF gs_rgdir-srtza = 'S'. " S = Simulation
      gs_output-activity = 'Payroll Simulation Executed'.
      CONCATENATE gs_rgdir-cdate gs_rgdir-ctime INTO gs_output-event_timestamp SEPARATED BY space.
      gs_output-is_automated = 'TRUE'.
      APPEND gs_output TO gt_output.
    ENDIF.

    " --- Activity: Audit Exception Flagged ---
    IF gs_rgdir-void IS NOT INITIAL. " Void indicator means rejected/exception
      gs_output-activity = 'Audit Exception Flagged'.
      CONCATENATE gs_rgdir-cdate gs_rgdir-ctime INTO gs_output-event_timestamp SEPARATED BY space.
      gs_output-is_automated = 'TRUE'.
      APPEND gs_output TO gt_output.

      " --- Activity: Data Correction Performed ---
      " Inferred: If voided, a correction usually follows immediately
      gs_output-activity = 'Data Correction Performed'.
      " Add 1 hour to simulate reaction time
      DATA: lv_corr_time TYPE t.
      lv_corr_time = gs_rgdir-ctime + 3600.
      CONCATENATE gs_rgdir-cdate lv_corr_time INTO gs_output-event_timestamp SEPARATED BY space.
      gs_output-is_automated = 'FALSE'.
      APPEND gs_output TO gt_output.
    ENDIF.

    " --- Activity: Gross Pay Calculated ---
    IF gs_rgdir-srtza = 'A'. " A = Actual Result
      gs_output-activity = 'Gross Pay Calculated'.
      CONCATENATE gs_rgdir-cdate gs_rgdir-ctime INTO gs_output-event_timestamp SEPARATED BY space.
      gs_output-gross_pay_amount = '1000.00'. " Placeholder: Cluster read required for actual amount
      gs_output-is_automated = 'TRUE'.
      APPEND gs_output TO gt_output.

      " --- Activity: Benefit Deductions Applied ---
      " Logically occurs immediately after gross calc
      gs_output-activity = 'Benefit Deductions Applied'.
      gs_output-is_automated = 'TRUE'.
      APPEND gs_output TO gt_output.

      " --- Activity: Payroll Record Approved ---
      " Inferred from successful write of 'A' record
      gs_output-activity = 'Payroll Record Approved'.
      DATA: lv_appr_time TYPE t.
      lv_appr_time = gs_rgdir-ctime + 60. " +1 minute
      CONCATENATE gs_rgdir-cdate lv_appr_time INTO gs_output-event_timestamp SEPARATED BY space.
      gs_output-is_automated = 'FALSE'.
      APPEND gs_output TO gt_output.

      " --- Activity: Pay Slip Published ---
      " Available via ESS usually next day
      gs_output-activity = 'Pay Slip Published'.
      DATA: lv_slip_date TYPE d.
      lv_slip_date = gs_rgdir-cdate + 1.
      CONCATENATE lv_slip_date '080000' INTO gs_output-event_timestamp SEPARATED BY space.
      gs_output-is_automated = 'TRUE'.
      APPEND gs_output TO gt_output.

      " --- Activity: Tax Filing Completed ---
      " End of period logic
      gs_output-activity = 'Tax Filing Completed'.
      DATA: lv_tax_date TYPE d.
      lv_tax_date = gs_rgdir-paydt.
      CONCATENATE lv_tax_date '235959' INTO gs_output-event_timestamp SEPARATED BY space.
      gs_output-is_automated = 'TRUE'.
      APPEND gs_output TO gt_output.
    ENDIF.

  ENDLOOP.

  " 2. Fetch Time Sheet Data (CATSDB)
  " Linking to Payroll Record loosely via Period/Date
  SELECT * FROM catsdb INTO TABLE gt_cats
    WHERE pernr IN s_pernr
      AND workdate IN s_date.

  LOOP AT gt_cats INTO gs_cats.
    CLEAR gs_output.
    CONCATENATE gs_cats-pernr '-00000' INTO gs_output-payroll_record. " Generic mapping
    gs_output-payroll_area = p_abkrs.
    gs_output-source_system = 'SAP_ECP'.

    " --- Activity: Time Sheet Submitted ---
    IF gs_cats-status = '20' OR gs_cats-status = '30'.
        gs_output-activity = 'Time Sheet Submitted'.
        CONCATENATE gs_cats-laudate gs_cats-lautime INTO gs_output-event_timestamp SEPARATED BY space.
        gs_output-is_automated = 'FALSE'.
        APPEND gs_output TO gt_output.
    ENDIF.

    " --- Activity: Time Sheet Approved ---
    IF gs_cats-status = '30'. " 30 = Approved
        gs_output-activity = 'Time Sheet Approved'.
        CONCATENATE gs_cats-apdat gs_cats-aptime INTO gs_output-event_timestamp SEPARATED BY space.
        gs_output-is_automated = 'FALSE'.
        APPEND gs_output TO gt_output.
    ENDIF.
  ENDLOOP.

  " 3. Fetch Incentive Data (Infotype 0015)
  SELECT * FROM pa0015 INTO TABLE gt_p0015
    WHERE pernr IN s_pernr
      AND begda IN s_date.

  LOOP AT gt_p0015 INTO gs_p0015.
    CLEAR gs_output.
    CONCATENATE gs_p0015-pernr '-00000' INTO gs_output-payroll_record.
    gs_output-source_system = 'SAP_ECP'.
    gs_output-payroll_area = p_abkrs.

    " --- Activity: Incentive Data Imported ---
    gs_output-activity = 'Incentive Data Imported'.
    CONCATENATE gs_p0015-aedtm '120000' INTO gs_output-event_timestamp SEPARATED BY space.
    gs_output-is_automated = 'FALSE'.
    APPEND gs_output TO gt_output.
  ENDLOOP.

  " 4. Fetch Payment Data (REGUH)
  SELECT * FROM reguh INTO TABLE gt_reguh
    WHERE zaldt IN s_date.

  LOOP AT gt_reguh INTO gs_reguh.
    " Filter for our PERNRs roughly (REGUH does not always have PERNR directly indexable easily in all views)
    " For simulation, we map broadly or require join logic. Here assuming simple extraction.

    CLEAR gs_output.
    CONCATENATE gs_reguh-pernr '-99999' INTO gs_output-payroll_record.
    gs_output-source_system = 'SAP_ECP'.
    gs_output-payroll_area = p_abkrs.

    " --- Activity: Bank Transfer File Generated ---
    gs_output-activity = 'Bank Transfer File Generated'.
    CONCATENATE gs_reguh-laufd gs_reguh-cpzut INTO gs_output-event_timestamp SEPARATED BY space.
    gs_output-is_automated = 'TRUE'.
    APPEND gs_output TO gt_output.

    " --- Activity: Payment Executed ---
    gs_output-activity = 'Payment Executed'.
    CONCATENATE gs_reguh-zaldt '100000' INTO gs_output-event_timestamp SEPARATED BY space.
    gs_output-is_automated = 'TRUE'.
    APPEND gs_output TO gt_output.
  ENDLOOP.

  " Output Display
  cl_demo_output=>display( gt_output ).