> 持续流程改进 > 薪资处理 > ADP Workforce Now > 数据模板

您的薪资处理数据模板

ADP Workforce Now

您的薪资处理数据模板

此模板为您在ADP Workforce Now中梳理薪资周期提供完整框架。其中列出了构建可靠事件日志所需的关键属性和活动，帮助您识别瓶颈，并确保整个组织符合合规要求。按照这些指南操作，您可以将原始系统数据转化为实际业务运营的清晰可视化结果。

用于详细分析的推荐属性
关键流程步骤和里程碑
ADP Workforce Now数据提取最佳实践

刚接触事件日志？了解如何创建流程挖掘事件日志.

下载模板

薪资处理属性

以下推荐数据字段可为事件日志提供必要背景，支持您深入分析薪资周期。

5 必需 7 建议 5 可选

	名称	说明
	事件时间戳 EventDateTime	活动发生的具体日期和时间。
说明此属性记录活动在系统中登记的准确时间，用于按时间顺序排列事件并计算步骤之间的时长。精确的时间戳对于识别瓶颈、衡量周期时间，例如审批耗时，以及确保工资处理符合服务级别协议至关重要。为什么重要它为所有周期时间和绩效分析提供必要的时间维度。获取位置与交易日志关联的系统时间戳。示例 2023-10-01T08:30:00Z2023-10-01T14:45:12Z2023-10-02T09:15:00Z
	工资记录 PayrollRecordId	特定员工与工资周期组合的唯一标识符。
说明工资记录是该流程的核心案例标识符，代表某名员工在特定周期内获得薪酬的一次完整记录，例如员工X的第12个工资周期。在分析中，此属性会将从最初提交工时表到最终完成税务申报的所有分散事件，归并为一条完整的端到端流程。分析人员可以据此跟踪每条记录的支付速度和准确性。为什么重要这是用于绑定所有事件数据、重建流程的必填案例键。获取位置请查阅ADP Workforce Now文档示例 PR-2023-10-A-00123PR-2023-10-A-00456PR-2023-10-B-00789
	活动 ActivityName	已执行的流程步骤或事件名称。
说明此属性记录工资处理工作流中的具体操作，例如“工时表已提交”“已计算应发工资”或“已生成银行转账文件”。它是流程图的基础，用于定义图中的节点并展示工作流顺序。活动序列的差异可以揭示返工循环以及偏离标准操作流程的情况。为什么重要它定义流程中的“内容”，从而实现流程顺序和流程变体的可视化。获取位置 ADP Workforce Now中的审计轨迹或交易历史日志。示例已提交工时表工资记录已批准已完成数据更正
	最后数据更新时间 LastDataUpdate	表示源系统中记录最后修改时间的时间戳。
说明反映ADP Workforce Now中数据记录最近一次修改的时间。该字段用于判断数据新鲜度并管理增量数据加载，确保流程分析反映工资记录的当前状态。为什么重要增量数据提取以及确保分析基于当前数据的关键字段。获取位置请查阅ADP Workforce Now文档示例 2023-10-05T17:00:00Z2023-10-06T08:00:00Z
	源系统 SourceSystem	事件产生所在的记录系统。
说明用于标识数据提取来源的软件环境，本例中为“ADP Workforce Now”。在数据可能与其他HRIS或银行系统合并的复杂系统环境中，该字段可确保数据血缘清晰，并帮助定位特定系统的集成问题。为什么重要它可确保数据的可追溯性和上下文信息，尤其适用于多系统流程挖掘环境。获取位置在ETL过程中硬编码。示例 ADP Workforce NowADP WFNADP_PROD
	工资专员 PayrollSpecialistName	负责执行活动的用户或系统代理。
说明用于标识执行活动的个人，例如工资专员、经理，或自动化系统账户。此属性支持“专员吞吐绩效”仪表板，帮助平衡工作负载，并识别培训需求或由资源限制造成的瓶颈。为什么重要它支持资源分析、工作负载平衡和职责分离审计。获取位置审计日志中的用户ID或执行者字段。示例 Jane Doe系统管理员John Smith
	工资组 PayGroupCode	用于标识特定的工资周期或群组，例如美国双周发薪组。
说明根据发薪频率和员工类型将员工划分为不同处理组，例如每周、双周或高管组。该字段用于筛选特定周期的分析，也是“手动数据更正频率”仪表板的关键字段，可用于判断某些群组是否更容易出错。为什么重要支持按发薪频率和员工类型对流程进行分组分析。获取位置工资组配置或工资表头表。示例 US_双周CA_月度US_高管
	应发工资总额 GrossPayAmount	扣除各项款项前，该记录计算得出的应发工资总额。
说明特定工资周期内为员工计算的总薪酬金额。虽然它不一定用于流程顺序分析，但可以为“ActivityAmount”提供背景信息，并帮助您在分析审计例外或追溯调整时优先处理高价值记录。为什么重要为工资记录提供财务背景和金额规模信息。获取位置工资结果表。示例 2500.005430.501200.00
	是否为追溯记录 IsRetrospective	用于标记该工资记录是否为过去周期的调整。
说明用于识别当前周期处理，还是对已结算周期进行更正，即追溯补发工资。该字段支持“追溯调整量”仪表板，用于展示工资流程的稳定性以及发布后错误的发生频率。为什么重要区分标准处理与返工或更正周期。获取位置根据Pay Run Type，或比较Pay Period与Current Date派生。示例 truefalse
	是否需要人工干预 IsManualIntervention	用于标记活动是否涉及手动数据录入或更正。
说明根据活动名称或交易来源派生的布尔属性，例如活动为“已执行数据更正”时取true。该字段驱动“手动更正率”KPI，帮助管理层识别自动化失效环节以及人工投入高度集中的区域。为什么重要直接支持自动化率和流程效率分析。获取位置根据ActivityName或特定交易代码派生。示例 truefalse
	税务管辖区 TaxJurisdictionCode	用于税务申报的具体地区或州代码。
说明表示与工资记录相关的主要税务机关或地区，例如CA、NY或联邦。 “税务申报完成基准”仪表板需要使用该字段识别法定申报延迟的地区，并确保符合当地法规。为什么重要合规监控和地区绩效分析的关键字段。获取位置税务档案或工资税表。示例 US-CAUS-NYUS-TX
	部门 DepartmentCode	与员工关联的部门或成本中心。
说明表示收款员工所属的组织单元。在ADP中，通常对应“归属部门”。此属性对于“部门流程差异”分析至关重要，可帮助组织比较不同业务单元的审批时间和错误率。为什么重要用于对不同组织单元的绩效进行基准比较。获取位置员工主数据或成本中心字段。示例 Sales-101IT-Dev-200HR-Admin-001
	员工ID EmployeeId	收款员工的唯一标识符。
说明与工资记录关联的具体员工ID。 CaseId用于跟踪记录，而Employee ID支持针对特定员工的纵向分析，例如识别经常迟交工时表或频繁需要手动更正的员工。为什么重要支持在员工层面分析行为模式。获取位置 ADP中的AssociateOID或File Number。示例 A102030000123456
	审计例外类型 AuditExceptionType	对标记出的合规或数据警告进行分类。
说明明确验证过程中发现的问题类型，例如“净工资为负”或“缺少税号”。该字段用于“审计例外解决监控”，分析哪些错误导致最长延迟，并指导上游数据质量改进的根因分析。为什么重要详细说明流程被暂停或标记的原因。获取位置请查阅ADP Workforce Now文档示例缺少SSN净工资为负福利不匹配
	工资周期结束日期 PayPeriodEndDate	工资周期的截止日期。
说明标记员工获得薪酬所对应周期的结束时间。结合事件时间戳使用，可判断处理是否相对于工资周期结束时间按计划完成。为什么重要用于计算计划遵循情况和延迟程度的参考点。获取位置工资周期配置。示例 2023-10-152023-10-31
	成本中心 CostCenterId	计入工资费用的财务成本中心代码。
说明表示与工资费用关联的具体GL单元或成本中心。此属性提供流程的财务视角，使组织能够将流程低效，例如返工成本，与具体业务单元关联起来。为什么重要使流程分析与财务报告结构保持一致。获取位置人工分配字段。示例 CC-500CC-900CC-120
	银行文件ID BankTransferFileId	发送至银行的已生成付款文件标识符。
说明 “已生成银行转账文件”活动生成的ACH或电汇文件唯一参考编号。该字段有助于将单条工资记录关联到批量付款执行步骤，支持“发放与发布速度”分析。为什么重要将单个案例关联到批处理事件。获取位置请查阅ADP Workforce Now文档示例 ACH-20231005-01ACH-20231005-02

必需建议可选

薪资处理活动

以下关键流程步骤和里程碑涵盖薪资运行从初始工时录入到最终付款的完整生命周期。

9 建议 6 可选

	活动	说明
	工时表已批准	表示经理批准工时卡，授权将工时用于工资处理。这是关键的把关步骤，可将数据从T&A模块转入Payroll模块。
为什么重要用于“Timesheet Approval Cycle Times”仪表板。识别可能危及处理时间线的管理瓶颈。获取位置 ADP Time & Attendance审计轨迹；筛选“Status Change”变为“Approved”的记录。采集在交易“Approve Timecard”执行时记录事件类型 explicit
	工资记录已批准	正式接受工资批次，锁定批次以防止进一步变更，并授权资金转移。这是ADP WFN中的“Finalize”步骤。
为什么重要标志着处理阶段结束并开始付款阶段的最终里程碑。获取位置 Payroll流程工作流；批次状态变为“Finalized”或“Committed”。采集在交易“Finalize Payroll”执行时记录事件类型 explicit
	已初始化工资记录	工资批次为当前周期创建或打开时发生，实质上为员工创建工资处理案例。该事件可根据活动批次中Paydata记录的创建时间戳推断。
为什么重要确定具体工资计算窗口的开始时间。与数据导入时间戳对比时，用于衡量“Incentive Data Integration Lag”。获取位置 Payroll模块；Paydata批次标题的创建时间戳，或Paydata Grid中的首条记录。采集比较批次创建前后的状态字段事件类型 inferred
	已发布工资单	员工通过自助服务门户（iPay）获取数字工资单。通常根据Payment Date安排。
为什么重要支持“Pay Slip Publication Lead Time”和员工满意度分析。获取位置根据应用于Check Date的系统设置（Pay Statement Availability Rule）推断。采集通过比较Check Date字段与Publication Rule推导事件类型 inferred
	已执行付款	资金实际发放给员工的日期。该事件通常根据工资批次中定义的Check Date计算得出。
为什么重要流程的主要结果。对于计算“Tax Filing Compliance Lead Time”至关重要。获取位置 Payroll历史记录；“Check Date”字段。采集通过比较Check Date字段与System Date推导事件类型 calculated
	已提交工时表	表示员工或经理为发薪周期提交工时和出勤数据的时间点。该事件明确记录于ADP Workforce Now的Time & Attendance模块审计日志中。
为什么重要启动工资周期计时。此处的延误会向下游传导，压缩薪资专员验证数据的可用时间。获取位置 ADP Time & Attendance审计轨迹；筛选Timecard对象中“Status Change”变为“Submitted”的记录。采集在交易“Submit Timecard”执行时记录事件类型 explicit
	已标记审计异常	表示系统在计算期间检测到违反验证规则的情况，例如实发工资为负或缺少税务辖区。该事件记录于计算输出中生成错误消息时。
为什么重要 “Audit Exception Resolution Monitor”的驱动指标。数量较高通常表示上游数据质量问题。获取位置 Payroll处理错误日志或“Verify Payroll”输出报告。采集系统生成验证错误时记录事件类型 explicit
	已生成银行转账文件	为直接存款技术生成NACHA/ACH文件。通常在“Finalize”步骤完成后立即触发。
为什么重要监控“Time to Bank Transfer File Generation”，确保技术交接不会延误付款。获取位置输出/报告日志；“ACH File”或“Direct Deposit File”生成的时间戳。采集输出文件创建时记录事件类型 explicit
	已计算应发工资	系统执行将工时乘以工资率并加上补充收入的逻辑。该事件可根据“Calculate Payroll”批处理完成时间戳推断。
为什么重要核心处理步骤。多次发生通常表示返工循环，可在“Payroll Preview Loop Analysis”中进行可视化。获取位置 Payroll审计轨迹；特定Pay Group批次中“Calculate”操作的完成时间。采集根据计算作业的完成时间戳推导事件类型 inferred
	已入账追溯调整	对已关闭的工资记录应用历史更正时发生，通常会影响下一个周期。该事件用于识别流程正式结束后发现的错误。
为什么重要对于“Retrospective Adjustment Volume”分析至关重要，表明上游质量检查未能发现问题。获取位置 Payroll历史记录；新增交易的“Effective Date”早于当前日期。采集在交易“Post Adjustment”执行时记录事件类型 explicit
	已完成数据更正	薪资专员为解决错误或调整金额而对Paydata Grid执行的手动修改。通过字段级审计轨迹记录。
为什么重要 “Manual Data Correction Frequency”的关键指标。更正率较高通常意味着自动化失败。获取位置 Paydata Grid审计轨迹；用户对工资明细行执行的“Update”操作。采集在交易“Update Paydata”执行时记录事件类型 explicit
	已完成税务申报	标志着该发薪周期法定税务申报完成。对于ADP Tax Service客户，这是ADP确认申报包已关闭的时间点。
为什么重要跟踪合规风险。此处延误可能导致处罚。获取位置 Tax模块；季度末或周期末申报状态更新。采集需要进行系统分析事件类型 inferred
	已导入激励数据	记录通过ADP Import工具导入佣金或奖金等外部薪酬数据的过程。该事件跟踪次要收入来源附加到工资记录的时间。
为什么重要对于识别集成延迟至关重要。导入延迟通常会导致重新计算和返工循环。获取位置 ADP Integration/Import日志；“Paydata Import”作业的成功时间戳。采集在交易“Import Paydata”执行时记录事件类型 explicit
	已应用福利扣除	将福利规则（医疗、401k）应用于应发工资。该步骤通常与计算同时发生，但出于审计目的在逻辑上独立。
为什么重要与“Audit Exception Flagged”关联，用于判断福利逻辑是否触发合规错误。获取位置根据计算事件后Paydata Grid中扣款明细行的创建或更新时间推断。采集比较计算前后的扣款字段事件类型 inferred
	已预览工资结果	生成“Preview Payroll”登记表或报告，供人工核验。该步骤用于在最终确定前验证计算结果。
为什么重要用于“Payroll Preview Loop Analysis”。预览次数过多通常说明对数据缺乏信心。获取位置系统日志；“Preview Payroll”报告生成的时间戳。采集在交易“Generate Preview”执行时记录事件类型 explicit

建议可选

提取指南

如何从ADP Workforce Now获取数据

步骤

使用具有Administrator或Payroll Practitioner访问权限的账户登录ADP Workforce Now。
从仪表板菜单进入Reports & Analytics，然后选择Custom Reports。
点击New Custom Report，选择Payroll数据域。根据版本不同，该选项可能标记为Pay Data或Payroll Register。
在Field Selection步骤中，找到并添加Case的唯一标识符。选择File Number，或将Associate ID与Pay Frequency和Pay Period Number组合，构建PayrollRecordId。
选择映射活动所需的全部日期字段。必须包含Pay Period Start Date、Pay Period End Date、Check Date、Period End Date和Calculation Date。请注意，部分审计字段，例如Timecard Approval Date，可能需要从Time & Attendance模块关联获取，前提是主视图中不存在该字段。
添加Gross Pay、Net Pay、Department、Cost Center和Tax Jurisdiction等财务及维度字段。
如果可用，将Audit Trail或Field Changes模块添加到报表布局中。这对于捕获Data Correction Performed等人工事件至关重要。如果无法在同一报表中完成关联，则必须创建单独的Audit Report，再通过Employee ID和Pay Period在外部关联。
配置筛选条件，排除作废支票，并将日期范围限制在所需分析窗口内，例如最近12个月。
进入Sort & Total步骤，先按Pay Period Start Date排序，再按Associate ID排序，确保数据顺序合理。
进入Run/Schedule步骤。选择Run Now进行测试，然后设置定期Schedule，将文件导出为CSV或Excel格式。
生成原始宽表报表后，使用ETL工具或脚本转换数据。必须将日期列反透视，使每个日期字段在事件日志中成为单独一行的Activity。
上传至ProcessMind前，确认最终CSV包含PayrollRecordId、ActivityName、EventDateTime和SourceSystem等必需列。

配置

报表类型：Standard Custom Report
数据域：Payroll / Pay Data Dictionary
输出格式：逗号分隔值（CSV）
建议日期范围：滚动12至24个月，用于趋势分析
关键筛选条件：Pay Group Code（选择相关组）、Company Code（适用于多实体环境）、Status为Active或Leave
粒度：原始数据为每位Associate每个Pay Period一行，转换后为每位Associate多行
所需权限：View Payroll Data、View Audit Trails、Create Custom Reports
本地化：在用户偏好设置中将日期格式设为ISO 8601（YYYY-MM-DD HH:MM:SS），避免格式歧义

a 示例查询 sql

/* 
ADP Workforce Now does not accept SQL queries from end-users. 
The following configuration block represents the Transformation Logic required to convert the ADP Custom Report export (Wide Format) into the Process Mining Event Log (Long Format).

Input Source: ADP_Custom_Payroll_Export.csv
Logic Language: Pseudo-ETL/Config
*/

TRANSFORMATION_CONFIG {

  GLOBAL_SETTINGS {
    SOURCE_SYSTEM: "ADP Workforce Now"
    CASE_ID_FORMULA: CONCATENATE(Field_AssociateID, "-", Field_PayPeriodNumber, "-", Field_Year)
    LAST_DATA_UPDATE: SYSTEM_TIMESTAMP()
  }

  ACTIVITY_MAPPING_RULES {

    /* 1. Time Sheet Submitted */
    RULE "Time Sheet Submitted" {
      IF Field_Timecard_Submit_Date IS NOT NULL THEN
        CREATE_EVENT(
          ACTIVITY: "Time Sheet Submitted",
          TIMESTAMP: Field_Timecard_Submit_Date,
          ATTRIBUTES: [PayrollSpecialistName: Field_Timecard_User, IsManualIntervention: "False"]
        )
    }

    /* 2. Time Sheet Approved */
    RULE "Time Sheet Approved" {
      IF Field_Timecard_Approval_Date IS NOT NULL THEN
        CREATE_EVENT(
          ACTIVITY: "Time Sheet Approved",
          TIMESTAMP: Field_Timecard_Approval_Date,
          ATTRIBUTES: [PayrollSpecialistName: Field_Manager_Name, IsManualIntervention: "True"]
        )
    }

    /* 3. Payroll Record Initialized */
    RULE "Payroll Record Initialized" {
      IF Field_Pay_Period_Start_Date IS NOT NULL THEN
        CREATE_EVENT(
          ACTIVITY: "Payroll Record Initialized",
          TIMESTAMP: Field_Pay_Period_Start_Date,
          ATTRIBUTES: [PayGroupCode: Field_PayGroup, IsRetrospective: "False"]
        )
    }

    /* 4. Incentive Data Imported */
    RULE "Incentive Data Imported" {
      IF Field_Import_Batch_Date IS NOT NULL AND Field_Batch_Type == "Import" THEN
        CREATE_EVENT(
          ACTIVITY: "Incentive Data Imported",
          TIMESTAMP: Field_Import_Batch_Date,
          ATTRIBUTES: [IsManualIntervention: "False"]
        )
    }

    /* 5. Gross Pay Calculated */
    RULE "Gross Pay Calculated" {
      IF Field_Calc_DateTime IS NOT NULL THEN
        CREATE_EVENT(
          ACTIVITY: "Gross Pay Calculated",
          TIMESTAMP: Field_Calc_DateTime,
          ATTRIBUTES: [GrossPayAmount: Field_Gross_Pay, DepartmentCode: Field_Home_Dept]
        )
    }

    /* 6. Benefits Deductions Applied */
    RULE "Benefits Deductions Applied" {
      /* Often occurs simultaneous to Calc, adding 1 second for logical sort */
      IF Field_Calc_DateTime IS NOT NULL AND Field_Total_Deductions > 0 THEN
        CREATE_EVENT(
          ACTIVITY: "Benefits Deductions Applied",
          TIMESTAMP: DATE_ADD(Field_Calc_DateTime, 1, "SECOND"),
          ATTRIBUTES: [GrossPayAmount: Field_Gross_Pay]
        )
    }

    /* 7. Audit Exception Flagged */
    RULE "Audit Exception Flagged" {
      IF Field_Error_Message_Timestamp IS NOT NULL THEN
        CREATE_EVENT(
          ACTIVITY: "Audit Exception Flagged",
          TIMESTAMP: Field_Error_Message_Timestamp,
          ATTRIBUTES: [IsManualIntervention: "False"]
        )
    }

    /* 8. Data Correction Performed */
    RULE "Data Correction Performed" {
      IF Field_Audit_Change_Date IS NOT NULL THEN
        CREATE_EVENT(
          ACTIVITY: "Data Correction Performed",
          TIMESTAMP: Field_Audit_Change_Date,
          ATTRIBUTES: [PayrollSpecialistName: Field_Audit_User, IsManualIntervention: "True"]
        )
    }

    /* 9. Payroll Result Previewed */
    RULE "Payroll Result Previewed" {
      IF Field_Preview_Run_DateTime IS NOT NULL THEN
        CREATE_EVENT(
          ACTIVITY: "Payroll Result Previewed",
          TIMESTAMP: Field_Preview_Run_DateTime,
          ATTRIBUTES: [IsManualIntervention: "True"]
        )
    }

    /* 10. Payroll Record Approved */
    RULE "Payroll Record Approved" {
      IF Field_Finalize_DateTime IS NOT NULL THEN
        CREATE_EVENT(
          ACTIVITY: "Payroll Record Approved",
          TIMESTAMP: Field_Finalize_DateTime,
          ATTRIBUTES: [PayrollSpecialistName: Field_Finalizer_User, IsManualIntervention: "True"]
        )
    }

    /* 11. Bank Transfer File Generated */
    RULE "Bank Transfer File Generated" {
      /* Logic: Usually occurs 2 hours after Finalize or at specific cutoff */
      IF Field_ACH_File_Date IS NOT NULL THEN
        CREATE_EVENT(
          ACTIVITY: "Bank Transfer File Generated",
          TIMESTAMP: Field_ACH_File_Date,
          ATTRIBUTES: [IsManualIntervention: "False"]
        )
    }

    /* 12. Payment Executed */
    RULE "Payment Executed" {
      IF Field_Check_Date IS NOT NULL THEN
        CREATE_EVENT(
          ACTIVITY: "Payment Executed",
          TIMESTAMP: Field_Check_Date,
          ATTRIBUTES: [GrossPayAmount: Field_Gross_Pay, IsRetrospective: "False"]
        )
    }

    /* 13. Pay Slip Published */
    RULE "Pay Slip Published" {
      IF Field_Advice_Available_Date IS NOT NULL THEN
        CREATE_EVENT(
          ACTIVITY: "Pay Slip Published",
          TIMESTAMP: Field_Advice_Available_Date,
          ATTRIBUTES: [IsManualIntervention: "False"]
        )
    }

    /* 14. Tax Filing Completed */
    RULE "Tax Filing Completed" {
      IF Field_Tax_Filing_Confirmation_Date IS NOT NULL THEN
        CREATE_EVENT(
          ACTIVITY: "Tax Filing Completed",
          TIMESTAMP: Field_Tax_Filing_Confirmation_Date,
          ATTRIBUTES: [TaxJurisdictionCode: Field_Tax_State, IsManualIntervention: "False"]
        )
    }

    /* 15. Retrospective Adjustment Posted */
    RULE "Retrospective Adjustment Posted" {
      IF Field_Adjustment_Run_Date IS NOT NULL AND Field_Run_Type == "Adjustment" THEN
        CREATE_EVENT(
          ACTIVITY: "Retrospective Adjustment Posted",
          TIMESTAMP: Field_Adjustment_Run_Date,
          ATTRIBUTES: [IsRetrospective: "True", IsManualIntervention: "True"]
        )
    }
  }
}

步骤

获取ADP Marketplace API凭据，包括Client ID、Client Secret以及与应用关联的SSL Client Certificate（PEM和Key文件）。
确认应用已启用必要的范围权限：Payroll（读取）、Worker（读取）和Time（读取）。
使用pip安装所需Python库：requests、pandas和pyOpenSSL，后者通常用于双向TLS处理。
使用您专属的ADP API端点和凭据文件路径，配置下方提供的Python脚本。
脚本通过双向TLS建立安全会话，并向ADP Token Endpoint完成身份验证。
首先查询Time and Labor Management API，获取目标期间的工时卡状态变更。
随后查询Payroll Output API（payroll-v1），获取每位员工的详细Pay Statement JSON。
脚本遍历复杂的嵌套JSON结构，识别计算、审批和文件生成等特定事件的时间戳。
脚本通过检查响应中的通知/警告数组，应用条件逻辑推断Audit Exception Flagged等活动。
根据AssociateOID（ADP中的员工唯一标识符）合并Time和Payroll数据。
将最终事件列表转换为Pandas DataFrame，并格式化为符合ProcessMind事件日志架构的数据。
运行脚本生成输出CSV文件，并在上传至ProcessMind前确认列标题符合要求。

配置

Client ID和Secret：从ADP Developer Portal获取特定应用的凭据。
SSL Certificates：填写client.pem和client.key文件的路径。ADP要求API通信使用双向TLS（mTLS）。
API Base URL：通常为https://api.adp.com，但特定环境可能不同，例如wfn-api.adp.com。
日期范围：建议每次提取3至6个月的数据，以符合API分页和超时限制。
Page Size：将$top参数（限制）设为每次请求50或100条记录，在避免超时的同时优化吞吐量。
Company Code：如果ADP实例托管多个法人实体，请按Organization Unit或Company Code筛选。
AssociateOID：这是关联Time和Payroll数据的主键，与可见的Employee ID不同。

a 示例查询 python

import requests
import pandas as pd
import json
from datetime import datetime
import uuid

class=class="hl-string">"hl-comment"># === CONFIGURATION ===
class=class="hl-string">"hl-comment"># Replace these placeholders with your actual ADP credentials and paths
ADP_CLIENT_ID = class="hl-string">"[Your ADP Client ID]"
ADP_CLIENT_SECRET = class="hl-string">"[Your ADP Client Secret]"
CERT_PATH = class="hl-string">"path/to/your/client_certificate.pem"
KEY_PATH = class="hl-string">"path/to/your/client_private_key.key"
TOKEN_URL = class="hl-string">"https://api.adp.com/auth/oauth/v2/token"
PAYROLL_API_URL = class="hl-string">"https://api.adp.com/payroll/v1/payroll-output"
TIME_API_URL = class="hl-string">"https://api.adp.com/time/v1/time-cards"

class=class="hl-string">"hl-comment"># Output File
OUTPUT_FILENAME = class="hl-string">"ADP_Payroll_Event_Log.csv"

class=class="hl-string">"hl-comment"># === AUTHENTICATION ===
def get_access_token():
    class=class="hl-string">"hl-comment"># ADP requires mutual TLS (mTLS) for authentication
    payload = {
        class="hl-string">'client_id': ADP_CLIENT_ID,
        class="hl-string">'client_secret': ADP_CLIENT_SECRET,
        class="hl-string">'grant_type': class="hl-string">'client_credentials'
    }
    try:
        response = requests.post(
            TOKEN_URL,
            data=payload,
            cert=(CERT_PATH, KEY_PATH),
            verify=True
        )
        response.raise_for_status()
        return response.json()[class="hl-string">'access_token']
    except Exception as e:
        print(fclass="hl-string">"Authentication failed: {str(e)}")
        return None

class=class="hl-string">"hl-comment"># === DATA EXTRACTION ===
def fetch_adp_data(url, token, params=None):
    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {token}',
        class="hl-string">'Accept': class="hl-string">'application/json'
    }
    all_records = []
    
    class=class="hl-string">"hl-comment"># Simple pagination loop (ADP uses class="hl-string">'skip' and class="hl-string">'top' or class="hl-string">'next' links)
    class=class="hl-string">"hl-comment"># This is a simplified implementation assuming standard offset pagination
    skip = 0
    limit = 50
    fetching = True
    
    while fetching:
        current_params = params.copy() if params else {}
        current_params[class="hl-string">'$skip'] = skip
        current_params[class="hl-string">'$top'] = limit
        
        try:
            response = requests.get(
                url, 
                headers=headers, 
                params=current_params, 
                cert=(CERT_PATH, KEY_PATH)
            )
            if response.status_code != 200:
                break
                
            data = response.json()
            class=class="hl-string">"hl-comment"># Adjust key based on actual API response structure (e.g., class="hl-string">'payrollOutputs', class="hl-string">'timeCards')
            class=class="hl-string">"hl-comment"># Using generic list detection for the script
            key = next((k for k in data.keys() if isinstance(data[k], list)), None)
            
            if not key or len(data[key]) == 0:
                fetching = False
            else:
                all_records.extend(data[key])
                skip += limit
                
        except Exception:
            fetching = False
            
    return all_records

class=class="hl-string">"hl-comment"># === EVENT LOG GENERATION ===
def generate_event_log(payroll_data, time_data):
    event_log = []
    
    class=class="hl-string">"hl-comment"># Index time data by AssociateOID for quick lookup
    time_map = {item.get(class="hl-string">'associateOID'): item for item in time_data}

    for pay_record in payroll_data:
        class=class="hl-string">"hl-comment"># Basic Attributes
        associate_id = pay_record.get(class="hl-string">'associateOID', class="hl-string">'Unknown')
        pay_period_start = pay_record.get(class="hl-string">'payPeriodStartDate', class="hl-string">'')
        class=class="hl-string">"hl-comment"># Create a unique Case ID using AssociateID and Pay Period
        case_id = fclass="hl-string">"{associate_id}-{pay_period_start}"
        
        dept_code = pay_record.get(class="hl-string">'worker', {}).get(class="hl-string">'homeOrganizationalUnits', [{}])[0].get(class="hl-string">'nameCode', {}).get(class="hl-string">'code', class="hl-string">'Unknown')
        pay_group = pay_record.get(class="hl-string">'payrollGroupCode', class="hl-string">'Main')
        gross_pay = pay_record.get(class="hl-string">'grossPayAmount', 0.0)
        tax_jur = pay_record.get(class="hl-string">'taxJurisdiction', {}).get(class="hl-string">'code', class="hl-string">'US')
        last_update = datetime.now().isoformat() class=class="hl-string">"hl-comment"># Using extraction time as fallback if record update time missing

        class=class="hl-string">"hl-comment"># Helper to append event
        def add_event(activity, timestamp, specialist=None, manual=False, retro=False):
            if timestamp:
                event_log.append({
                    class="hl-string">'PayrollRecordId': case_id,
                    class="hl-string">'ActivityName': activity,
                    class="hl-string">'EventDateTime': timestamp,
                    class="hl-string">'SourceSystem': class="hl-string">'ADP Workforce Now',
                    class="hl-string">'LastDataUpdate': last_update,
                    class="hl-string">'PayrollSpecialistName': specialist if specialist else class="hl-string">'System Agent',
                    class="hl-string">'DepartmentCode': dept_code,
                    class="hl-string">'PayGroupCode': pay_group,
                    class="hl-string">'GrossPayAmount': gross_pay,
                    class="hl-string">'TaxJurisdictionCode': tax_jur,
                    class="hl-string">'IsManualIntervention': manual,
                    class="hl-string">'IsRetrospective': retro
                })

        class=class="hl-string">"hl-comment"># --- ACTIVITY 1: Time Sheet Submitted ---
        class=class="hl-string">"hl-comment"># Derived from Time API
        t_card = time_map.get(associate_id)
        if t_card and t_card.get(class="hl-string">'submissionDateTime'):
            add_event(class="hl-string">"Time Sheet Submitted", t_card[class="hl-string">'submissionDateTime'], specialist=t_card.get(class="hl-string">'submittedBy'))

        class=class="hl-string">"hl-comment"># --- ACTIVITY 2: Time Sheet Approved ---
        class=class="hl-string">"hl-comment"># Derived from Time API
        if t_card and t_card.get(class="hl-string">'approvalDateTime'):
            add_event(class="hl-string">"Time Sheet Approved", t_card[class="hl-string">'approvalDateTime'], specialist=t_card.get(class="hl-string">'approvedBy'))

        class=class="hl-string">"hl-comment"># --- ACTIVITY 3: Payroll Record Initialized ---
        class=class="hl-string">"hl-comment"># Creation of the pay data record
        create_time = pay_record.get(class="hl-string">'creationDateTime')
        add_event(class="hl-string">"Payroll Record Initialized", create_time)

        class=class="hl-string">"hl-comment"># --- ACTIVITY 4: Incentive Data Imported ---
        class=class="hl-string">"hl-comment"># Check for specific earnings codes related to incentives
        earnings = pay_record.get(class="hl-string">'earnings', [])
        for earn in earnings:
            if earn.get(class="hl-string">'typeCode') in [class="hl-string">'BONUS', class="hl-string">'COMMISSION']:
                class=class="hl-string">"hl-comment"># Use entry time of the specific line item
                add_event(class="hl-string">"Incentive Data Imported", earn.get(class="hl-string">'entryDateTime', create_time))
                break class=class="hl-string">"hl-comment"># Capture once per case to avoid duplicates or refine logic for multiple

        class=class="hl-string">"hl-comment"># --- ACTIVITY 5: Gross Pay Calculated ---
        class=class="hl-string">"hl-comment"># When the calculation engine finished
        calc_time = pay_record.get(class="hl-string">'calculationDateTime')
        add_event(class="hl-string">"Gross Pay Calculated", calc_time)

        class=class="hl-string">"hl-comment"># --- ACTIVITY 6: Benefits Deductions Applied ---
        class=class="hl-string">"hl-comment"># Usually happens same time as calc, but logically distinct
        class=class="hl-string">"hl-comment"># We look for deduction processing timestamp
        deductions = pay_record.get(class="hl-string">'deductions', [])
        if deductions and calc_time:
             add_event(class="hl-string">"Benefits Deductions Applied", calc_time)

        class=class="hl-string">"hl-comment"># --- ACTIVITY 7: Audit Exception Flagged ---
        class=class="hl-string">"hl-comment"># Check notification messages
        msgs = pay_record.get(class="hl-string">'notificationMessages', [])
        if msgs:
            class=class="hl-string">"hl-comment"># Take the timestamp of the first warning
            add_event(class="hl-string">"Audit Exception Flagged", msgs[0].get(class="hl-string">'generationDateTime', calc_time))

        class=class="hl-string">"hl-comment"># --- ACTIVITY 8: Data Correction Performed ---
        class=class="hl-string">"hl-comment"># Compare modification time vs creation time. If significantly different, assume correction.
        mod_time = pay_record.get(class="hl-string">'modificationDateTime')
        if mod_time and create_time and mod_time > create_time:
            add_event(class="hl-string">"Data Correction Performed", mod_time, manual=True)

        class=class="hl-string">"hl-comment"># --- ACTIVITY 9: Payroll Result Previewed ---
        class=class="hl-string">"hl-comment"># Report generation time
        report_time = pay_record.get(class="hl-string">'reportGenerationDateTime')
        add_event(class="hl-string">"Payroll Result Previewed", report_time)

        class=class="hl-string">"hl-comment"># --- ACTIVITY 10: Payroll Record Approved ---
        class=class="hl-string">"hl-comment"># Final approval timestamp
        approve_time = pay_record.get(class="hl-string">'approvalDateTime')
        add_event(class="hl-string">"Payroll Record Approved", approve_time, specialist=class="hl-string">"Payroll Manager")

        class=class="hl-string">"hl-comment"># --- ACTIVITY 11: Bank Transfer File Generated ---
        class=class="hl-string">"hl-comment"># Distribution file generation
        dist_time = pay_record.get(class="hl-string">'distribution', {}).get(class="hl-string">'fileGenerationDateTime')
        add_event(class="hl-string">"Bank Transfer File Generated", dist_time)

        class=class="hl-string">"hl-comment"># --- ACTIVITY 12: Payment Executed ---
        class=class="hl-string">"hl-comment"># The actual check date or payment date
        pay_date = pay_record.get(class="hl-string">'paymentDate')
        class=class="hl-string">"hl-comment"># Convert date-only string to datetime (assuming 9 AM local)
        if pay_date:
            pay_dt_time = fclass="hl-string">"{pay_date}T09:00:00"
            add_event(class="hl-string">"Payment Executed", pay_dt_time)

        class=class="hl-string">"hl-comment"># --- ACTIVITY 13: Pay Slip Published ---
        class=class="hl-string">"hl-comment"># Usually available on Check Date
        class=class="hl-string">"hl-comment"># Or specific class="hl-string">'publicationDate' if available
        pub_date = pay_record.get(class="hl-string">'payStatementAvailableDateTime')
        if not pub_date and pay_date:
             pub_date = fclass="hl-string">"{pay_date}T08:00:00"
        add_event(class="hl-string">"Pay Slip Published", pub_date)

        class=class="hl-string">"hl-comment"># --- ACTIVITY 14: Tax Filing Completed ---
        class=class="hl-string">"hl-comment"># Tax filing status date
        tax_file_time = pay_record.get(class="hl-string">'taxFiling', {}).get(class="hl-string">'completionDateTime')
        add_event(class="hl-string">"Tax Filing Completed", tax_file_time)

        class=class="hl-string">"hl-comment"># --- ACTIVITY 15: Retrospective Adjustment Posted ---
        class=class="hl-string">"hl-comment"># Check if this record is marked as a retro adjustment
        is_retro = pay_record.get(class="hl-string">'isRetroactive', False)
        if is_retro:
            retro_time = pay_record.get(class="hl-string">'postingDateTime', calc_time)
            add_event(class="hl-string">"Retrospective Adjustment Posted", retro_time, retro=True)

    return pd.DataFrame(event_log)

class=class="hl-string">"hl-comment"># === EXECUTION ===
if __name__ == class="hl-string">"__main__":
    class=class="hl-string">"hl-comment"># 1. Authenticate
    token = get_access_token()
    
    if token:
        class=class="hl-string">"hl-comment"># 2. Fetch Data
        print(class="hl-string">"Fetching Payroll Data...")
        class=class="hl-string">"hl-comment"># Filter for last 90 days - example
        payroll_raw = fetch_adp_data(PAYROLL_API_URL, token)
        
        print(class="hl-string">"Fetching Time Data...")
        time_raw = fetch_adp_data(TIME_API_URL, token)
        
        class=class="hl-string">"hl-comment"># 3. Transform
        print(class="hl-string">"Generating Event Log...")
        df_events = generate_event_log(payroll_raw, time_raw)
        
        class=class="hl-string">"hl-comment"># 4. Export
        if not df_events.empty:
            class=class="hl-string">"hl-comment"># Ensure timestamp format
            df_events[class="hl-string">'EventDateTime'] = pd.to_datetime(df_events[class="hl-string">'EventDateTime']).dt.strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
            df_events.to_csv(OUTPUT_FILENAME, index=False)
            print(fclass="hl-string">"Success! Event log written to {OUTPUT_FILENAME} with {len(df_events)} rows.")
        else:
            print(class="hl-string">"No events generated.")

步骤

确认目标环境已启用ADP Workforce Now General Ledger Interface、Payroll、Time & Attendance、审计、导入、报表及适用的税务服务能力。同时确认已具备所需的审计和薪资报表授权。菜单名称和交易标识符因配置而异，请使用[Configure based on your system]记录适用路径。
为所需公司、薪资组、收入、扣款、税费、部门和科目代码配置General Ledger Interface映射。保留生成的接口输出及其处理元数据。GL文件是薪资金额和会计维度的主要来源，辅助源提取则提供事件时间戳和活动证据。
从[Your table name]或相应的ADP Workforce Now导出文件中，提取所选期间的源数据集。所需源类别包括Time & Attendance审计事件、Paydata记录、激励导入记录、薪资计算结果、扣款结果、验证消息、字段级审计记录、预览登记表、最终化记录、银行文件记录、支付记录、工资单发布记录、税务申报确认、追溯调整记录和GL接口输出。
将查询中的每个方括号源引用替换为环境中实际可用的表、视图、报表或暂存导出，并将源字段映射到查询中显示的逻辑字段。对于推断出的时间戳，除非记录推断规则，否则不得将其视为直接审计证据。
将所有源时间戳统一为一个时区，最好使用UTC，并将源标识符统一转换为文本值。严格按照查询中列出的名称规范化活动名称。将金额字段转换为统一数值类型，并将布尔指标规范化为TRUE或FALSE。
针对受控日期范围执行查询，通常为三至六个月。使用薪资记录标识符作为Case键。同一Case中的同一活动如果发生多次，应保留多行，因为ProcessMind会按原样读取事件日志，不会推断额外事件。
检查重复记录处理、时间戳顺序和源优先级。查询有意使用UNION ALL，以确保每个明确提取的活动都保留为事件行。对于没有直接源事件的活动，仅当已配置源支持该推断时，才保留有文档记录的推断源时间戳。
验证全部15个活动名称均已存在，源数据支持的必需属性均已填充，并确认事件日期范围与提取参数一致。将薪资总额和会计维度与General Ledger Interface输出及适用的薪资登记表进行核对。
将结果导出为扁平化UTF-8 CSV或其他ProcessMind支持的事件日志格式。包含PayrollRecordId、ActivityName、EventDateTime、SourceSystem、LastDataUpdate、PayrollSpecialistName、DepartmentCode、PayGroupCode、GrossPayAmount、TaxJurisdictionCode、IsManualIntervention和IsRetrospective列。将文件上传至ProcessMind，并将PayrollRecordId配置为Case标识符、ActivityName配置为活动、EventDateTime配置为事件时间戳。

配置

日期范围：从已完成的三至六个月薪资周期开始。历史数据应覆盖常规薪资、调整、激励、税务活动和追溯更正。增量提取使用[Start date parameter]和[End date parameter]。
公司和薪资组筛选：将提取范围限制为所需的Company Code、Pay Group Code、法人实体和薪资日历。环境包含多家公司时使用[Company filter]。
源范围：包含General Ledger Interface输出，以及薪资、Time & Attendance、导入、审计、计算、支付、工资单、税务和调整源。仅凭GL文件通常无法证明完整的生命周期活动。
活动筛选：使用查询中的15个ActivityName精确值。不要将活动合并为更宽泛的类别，因为ProcessMind不会推导缺失事件。
时间戳策略：定义源时区，并统一转换所有EventDateTime和LastDataUpdate值。如需满足可审计性要求，应在暂存层保留原始源时间戳。
金额和会计配置：确认GL Interface配置中的收入、扣款、税费、部门、成本中心和科目映射。将GrossPayAmount与薪资计算或登记表源进行核对，不要假设每条GL行都是应发工资。
审计保留：启用或获取Time & Attendance审计日志、字段级Paydata审计记录、计算消息、最终化记录、支付记录和税务申报确认的保留能力。
性能：关联源数据前，先按薪资周期、公司和薪资组筛选。在支持的情况下，按PayrollRecordId、薪资周期、事件时间戳和批次标识为暂存数据建立索引或分区。大范围数据应增量处理。
授权：获取访问薪资、Time & Attendance、General Ledger Interface、审计、导入、支付、工资单和税务服务数据的权限。具体角色、许可证、菜单路径和交易标识符取决于ADP Workforce Now配置。
推断控制：在技术文档中明确标记Payroll Record Initialized、Gross Pay Calculated、Benefits Deductions Applied、Payment Executed及其他推断事件。如果源数据不支持某项推断，应排除该行，不得虚构时间戳。
文件处理：保留原始GL接口文件、源提取文件、提取参数、查询版本和执行时间戳，以便进行核对和审计。

a 示例查询 sql

WITH
params AS (
    SELECT
        CAST('[Start date parameter]' AS TIMESTAMP) AS start_datetime,
        CAST('[End date parameter]' AS TIMESTAMP) AS end_datetime
),

time_sheet_submitted AS (
    SELECT
        CAST(t.PayrollRecordId AS VARCHAR(255)) AS PayrollRecordId,
        'Time Sheet Submitted' AS ActivityName,
        CAST(t.SubmittedDateTime AS TIMESTAMP) AS EventDateTime,
        'ADP Workforce Now' AS SourceSystem,
        CAST(t.LastDataUpdate AS TIMESTAMP) AS LastDataUpdate,
        CAST(t.PayrollSpecialistName AS VARCHAR(255)) AS PayrollSpecialistName,
        CAST(t.DepartmentCode AS VARCHAR(100)) AS DepartmentCode,
        CAST(t.PayGroupCode AS VARCHAR(100)) AS PayGroupCode,
        CAST(t.GrossPayAmount AS DECIMAL(18,2)) AS GrossPayAmount,
        CAST(t.TaxJurisdictionCode AS VARCHAR(100)) AS TaxJurisdictionCode,
        CAST(COALESCE(t.IsManualIntervention, FALSE) AS BOOLEAN) AS IsManualIntervention,
        CAST(COALESCE(t.IsRetrospective, FALSE) AS BOOLEAN) AS IsRetrospective
    FROM [Your time and attendance audit table] t
    CROSS JOIN params p
    WHERE t.SubmittedDateTime >= p.start_datetime
      AND t.SubmittedDateTime < p.end_datetime
      AND t.EventType = 'Time Sheet Submitted'
),

time_sheet_approved AS (
    SELECT
        CAST(t.PayrollRecordId AS VARCHAR(255)),
        'Time Sheet Approved',
        CAST(t.ApprovedDateTime AS TIMESTAMP),
        'ADP Workforce Now',
        CAST(t.LastDataUpdate AS TIMESTAMP),
        CAST(t.PayrollSpecialistName AS VARCHAR(255)),
        CAST(t.DepartmentCode AS VARCHAR(100)),
        CAST(t.PayGroupCode AS VARCHAR(100)),
        CAST(t.GrossPayAmount AS DECIMAL(18,2)),
        CAST(t.TaxJurisdictionCode AS VARCHAR(100)),
        CAST(COALESCE(t.IsManualIntervention, FALSE) AS BOOLEAN),
        CAST(COALESCE(t.IsRetrospective, FALSE) AS BOOLEAN)
    FROM [Your time and attendance approval table] t
    CROSS JOIN params p
    WHERE t.ApprovedDateTime >= p.start_datetime
      AND t.ApprovedDateTime < p.end_datetime
      AND t.EventType = 'Time Sheet Approved'
),

payroll_record_initialized AS (
    SELECT
        CAST(t.PayrollRecordId AS VARCHAR(255)),
        'Payroll Record Initialized',
        CAST(t.PaydataCreatedDateTime AS TIMESTAMP),
        'ADP Workforce Now',
        CAST(t.LastDataUpdate AS TIMESTAMP),
        CAST(t.PayrollSpecialistName AS VARCHAR(255)),
        CAST(t.DepartmentCode AS VARCHAR(100)),
        CAST(t.PayGroupCode AS VARCHAR(100)),
        CAST(t.GrossPayAmount AS DECIMAL(18,2)),
        CAST(t.TaxJurisdictionCode AS VARCHAR(100)),
        CAST(COALESCE(t.IsManualIntervention, FALSE) AS BOOLEAN),
        CAST(COALESCE(t.IsRetrospective, FALSE) AS BOOLEAN)
    FROM [Your Paydata table] t
    CROSS JOIN params p
    WHERE t.PaydataCreatedDateTime >= p.start_datetime
      AND t.PaydataCreatedDateTime < p.end_datetime
),

incentive_data_imported AS (
    SELECT
        CAST(t.PayrollRecordId AS VARCHAR(255)),
        'Incentive Data Imported',
        CAST(t.ImportedDateTime AS TIMESTAMP),
        'ADP Workforce Now',
        CAST(t.LastDataUpdate AS TIMESTAMP),
        CAST(t.PayrollSpecialistName AS VARCHAR(255)),
        CAST(t.DepartmentCode AS VARCHAR(100)),
        CAST(t.PayGroupCode AS VARCHAR(100)),
        CAST(t.GrossPayAmount AS DECIMAL(18,2)),
        CAST(t.TaxJurisdictionCode AS VARCHAR(100)),
        CAST(COALESCE(t.IsManualIntervention, FALSE) AS BOOLEAN),
        CAST(COALESCE(t.IsRetrospective, FALSE) AS BOOLEAN)
    FROM [Your incentive import table] t
    CROSS JOIN params p
    WHERE t.ImportedDateTime >= p.start_datetime
      AND t.ImportedDateTime < p.end_datetime
),

gross_pay_calculated AS (
    SELECT
        CAST(t.PayrollRecordId AS VARCHAR(255)),
        'Gross Pay Calculated',
        CAST(t.CalculationCompletedDateTime AS TIMESTAMP),
        'ADP Workforce Now',
        CAST(t.LastDataUpdate AS TIMESTAMP),
        CAST(t.PayrollSpecialistName AS VARCHAR(255)),
        CAST(t.DepartmentCode AS VARCHAR(100)),
        CAST(t.PayGroupCode AS VARCHAR(100)),
        CAST(t.GrossPayAmount AS DECIMAL(18,2)),
        CAST(t.TaxJurisdictionCode AS VARCHAR(100)),
        CAST(COALESCE(t.IsManualIntervention, FALSE) AS BOOLEAN),
        CAST(COALESCE(t.IsRetrospective, FALSE) AS BOOLEAN)
    FROM [Your payroll calculation result table] t
    CROSS JOIN params p
    WHERE t.CalculationCompletedDateTime >= p.start_datetime
      AND t.CalculationCompletedDateTime < p.end_datetime
      AND t.ProcessName = 'Calculate Payroll'
),

benefits_deductions_applied AS (
    SELECT
        CAST(t.PayrollRecordId AS VARCHAR(255)),
        'Benefits Deductions Applied',
        CAST(t.CalculationCompletedDateTime AS TIMESTAMP),
        'ADP Workforce Now',
        CAST(t.LastDataUpdate AS TIMESTAMP),
        CAST(t.PayrollSpecialistName AS VARCHAR(255)),
        CAST(t.DepartmentCode AS VARCHAR(100)),
        CAST(t.PayGroupCode AS VARCHAR(100)),
        CAST(t.GrossPayAmount AS DECIMAL(18,2)),
        CAST(t.TaxJurisdictionCode AS VARCHAR(100)),
        CAST(COALESCE(t.IsManualIntervention, FALSE) AS BOOLEAN),
        CAST(COALESCE(t.IsRetrospective, FALSE) AS BOOLEAN)
    FROM [Your payroll deduction result table] t
    CROSS JOIN params p
    WHERE t.CalculationCompletedDateTime >= p.start_datetime
      AND t.CalculationCompletedDateTime < p.end_datetime
      AND t.DeductionCategory IN ('Medical', '401k')
),

audit_exception_flagged AS (
    SELECT
        CAST(t.PayrollRecordId AS VARCHAR(255)),
        'Audit Exception Flagged',
        CAST(t.ErrorGeneratedDateTime AS TIMESTAMP),
        'ADP Workforce Now',
        CAST(t.LastDataUpdate AS TIMESTAMP),
        CAST(t.PayrollSpecialistName AS VARCHAR(255)),
        CAST(t.DepartmentCode AS VARCHAR(100)),
        CAST(t.PayGroupCode AS VARCHAR(100)),
        CAST(t.GrossPayAmount AS DECIMAL(18,2)),
        CAST(t.TaxJurisdictionCode AS VARCHAR(100)),
        CAST(COALESCE(t.IsManualIntervention, FALSE) AS BOOLEAN),
        CAST(COALESCE(t.IsRetrospective, FALSE) AS BOOLEAN)
    FROM [Your payroll calculation error table] t
    CROSS JOIN params p
    WHERE t.ErrorGeneratedDateTime >= p.start_datetime
      AND t.ErrorGeneratedDateTime < p.end_datetime
),

data_correction_performed AS (
    SELECT
        CAST(t.PayrollRecordId AS VARCHAR(255)),
        'Data Correction Performed',
        CAST(t.ChangedDateTime AS TIMESTAMP),
        'ADP Workforce Now',
        CAST(t.LastDataUpdate AS TIMESTAMP),
        CAST(t.PayrollSpecialistName AS VARCHAR(255)),
        CAST(t.DepartmentCode AS VARCHAR(100)),
        CAST(t.PayGroupCode AS VARCHAR(100)),
        CAST(t.GrossPayAmount AS DECIMAL(18,2)),
        CAST(t.TaxJurisdictionCode AS VARCHAR(100)),
        TRUE,
        CAST(COALESCE(t.IsRetrospective, FALSE) AS BOOLEAN)
    FROM [Your Paydata field level audit table] t
    CROSS JOIN params p
    WHERE t.ChangedDateTime >= p.start_datetime
      AND t.ChangedDateTime < p.end_datetime
      AND t.ChangeSource = 'Paydata Grid'
),

payroll_result_previewed AS (
    SELECT
        CAST(t.PayrollRecordId AS VARCHAR(255)),
        'Payroll Result Previewed',
        CAST(t.PreviewGeneratedDateTime AS TIMESTAMP),
        'ADP Workforce Now',
        CAST(t.LastDataUpdate AS TIMESTAMP),
        CAST(t.PayrollSpecialistName AS VARCHAR(255)),
        CAST(t.DepartmentCode AS VARCHAR(100)),
        CAST(t.PayGroupCode AS VARCHAR(100)),
        CAST(t.GrossPayAmount AS DECIMAL(18,2)),
        CAST(t.TaxJurisdictionCode AS VARCHAR(100)),
        CAST(COALESCE(t.IsManualIntervention, FALSE) AS BOOLEAN),
        CAST(COALESCE(t.IsRetrospective, FALSE) AS BOOLEAN)
    FROM [Your payroll preview register table] t
    CROSS JOIN params p
    WHERE t.PreviewGeneratedDateTime >= p.start_datetime
      AND t.PreviewGeneratedDateTime < p.end_datetime
),

payroll_record_approved AS (
    SELECT
        CAST(t.PayrollRecordId AS VARCHAR(255)),
        'Payroll Record Approved',
        CAST(t.FinalizedDateTime AS TIMESTAMP),
        'ADP Workforce Now',
        CAST(t.LastDataUpdate AS TIMESTAMP),
        CAST(t.PayrollSpecialistName AS VARCHAR(255)),
        CAST(t.DepartmentCode AS VARCHAR(100)),
        CAST(t.PayGroupCode AS VARCHAR(100)),
        CAST(t.GrossPayAmount AS DECIMAL(18,2)),
        CAST(t.TaxJurisdictionCode AS VARCHAR(100)),
        CAST(COALESCE(t.IsManualIntervention, FALSE) AS BOOLEAN),
        CAST(COALESCE(t.IsRetrospective, FALSE) AS BOOLEAN)
    FROM [Your payroll finalization table] t
    CROSS JOIN params p
    WHERE t.FinalizedDateTime >= p.start_datetime
      AND t.FinalizedDateTime < p.end_datetime
),

bank_transfer_file_generated AS (
    SELECT
        CAST(t.PayrollRecordId AS VARCHAR(255)),
        'Bank Transfer File Generated',
        CAST(t.FileGeneratedDateTime AS TIMESTAMP),
        'ADP Workforce Now',
        CAST(t.LastDataUpdate AS TIMESTAMP),
        CAST(t.PayrollSpecialistName AS VARCHAR(255)),
        CAST(t.DepartmentCode AS VARCHAR(100)),
        CAST(t.PayGroupCode AS VARCHAR(100)),
        CAST(t.GrossPayAmount AS DECIMAL(18,2)),
        CAST(t.TaxJurisdictionCode AS VARCHAR(100)),
        CAST(COALESCE(t.IsManualIntervention, FALSE) AS BOOLEAN),
        CAST(COALESCE(t.IsRetrospective, FALSE) AS BOOLEAN)
    FROM [Your bank transfer file table] t
    CROSS JOIN params p
    WHERE t.FileGeneratedDateTime >= p.start_datetime
      AND t.FileGeneratedDateTime < p.end_datetime
),

payment_executed AS (
    SELECT
        CAST(t.PayrollRecordId AS VARCHAR(255)),
        'Payment Executed',
        CAST(t.PaymentDateTime AS TIMESTAMP),
        'ADP Workforce Now',
        CAST(t.LastDataUpdate AS TIMESTAMP),
        CAST(t.PayrollSpecialistName AS VARCHAR(255)),
        CAST(t.DepartmentCode AS VARCHAR(100)),
        CAST(t.PayGroupCode AS VARCHAR(100)),
        CAST(t.GrossPayAmount AS DECIMAL(18,2)),
        CAST(t.TaxJurisdictionCode AS VARCHAR(100)),
        CAST(COALESCE(t.IsManualIntervention, FALSE) AS BOOLEAN),
        CAST(COALESCE(t.IsRetrospective, FALSE) AS BOOLEAN)
    FROM [Your payment execution table] t
    CROSS JOIN params p
    WHERE t.PaymentDateTime >= p.start_datetime
      AND t.PaymentDateTime < p.end_datetime
),

pay_slip_published AS (
    SELECT
        CAST(t.PayrollRecordId AS VARCHAR(255)),
        'Pay Slip Published',
        CAST(t.PublishedDateTime AS TIMESTAMP),
        'ADP Workforce Now',
        CAST(t.LastDataUpdate AS TIMESTAMP),
        CAST(t.PayrollSpecialistName AS VARCHAR(255)),
        CAST(t.DepartmentCode AS VARCHAR(100)),
        CAST(t.PayGroupCode AS VARCHAR(100)),
        CAST(t.GrossPayAmount AS DECIMAL(18,2)),
        CAST(t.TaxJurisdictionCode AS VARCHAR(100)),
        CAST(COALESCE(t.IsManualIntervention, FALSE) AS BOOLEAN),
        CAST(COALESCE(t.IsRetrospective, FALSE) AS BOOLEAN)
    FROM [Your pay statement publication table] t
    CROSS JOIN params p
    WHERE t.PublishedDateTime >= p.start_datetime
      AND t.PublishedDateTime < p.end_datetime
),

tax_filing_completed AS (
    SELECT
        CAST(t.PayrollRecordId AS VARCHAR(255)),
        'Tax Filing Completed',
        CAST(t.FilingCompletedDateTime AS TIMESTAMP),
        'ADP Workforce Now',
        CAST(t.LastDataUpdate AS TIMESTAMP),
        CAST(t.PayrollSpecialistName AS VARCHAR(255)),
        CAST(t.DepartmentCode AS VARCHAR(100)),
        CAST(t.PayGroupCode AS VARCHAR(100)),
        CAST(t.GrossPayAmount AS DECIMAL(18,2)),
        CAST(t.TaxJurisdictionCode AS VARCHAR(100)),
        CAST(COALESCE(t.IsManualIntervention, FALSE) AS BOOLEAN),
        CAST(COALESCE(t.IsRetrospective, FALSE) AS BOOLEAN)
    FROM [Your tax filing confirmation table] t
    CROSS JOIN params p
    WHERE t.FilingCompletedDateTime >= p.start_datetime
      AND t.FilingCompletedDateTime < p.end_datetime
),

retrospective_adjustment_posted AS (
    SELECT
        CAST(t.PayrollRecordId AS VARCHAR(255)),
        'Retrospective Adjustment Posted',
        CAST(t.AdjustmentPostedDateTime AS TIMESTAMP),
        'ADP Workforce Now',
        CAST(t.LastDataUpdate AS TIMESTAMP),
        CAST(t.PayrollSpecialistName AS VARCHAR(255)),
        CAST(t.DepartmentCode AS VARCHAR(100)),
        CAST(t.PayGroupCode AS VARCHAR(100)),
        CAST(t.GrossPayAmount AS DECIMAL(18,2)),
        CAST(t.TaxJurisdictionCode AS VARCHAR(100)),
        CAST(COALESCE(t.IsManualIntervention, FALSE) AS BOOLEAN),
        TRUE
    FROM [Your retrospective adjustment table] t
    CROSS JOIN params p
    WHERE t.AdjustmentPostedDateTime >= p.start_datetime
      AND t.AdjustmentPostedDateTime < p.end_datetime
)

SELECT PayrollRecordId, ActivityName, EventDateTime, SourceSystem, LastDataUpdate, PayrollSpecialistName, DepartmentCode, PayGroupCode, GrossPayAmount, TaxJurisdictionCode, IsManualIntervention, IsRetrospective FROM time_sheet_submitted
UNION ALL
SELECT PayrollRecordId, ActivityName, EventDateTime, SourceSystem, LastDataUpdate, PayrollSpecialistName, DepartmentCode, PayGroupCode, GrossPayAmount, TaxJurisdictionCode, IsManualIntervention, IsRetrospective FROM time_sheet_approved
UNION ALL
SELECT PayrollRecordId, ActivityName, EventDateTime, SourceSystem, LastDataUpdate, PayrollSpecialistName, DepartmentCode, PayGroupCode, GrossPayAmount, TaxJurisdictionCode, IsManualIntervention, IsRetrospective FROM payroll_record_initialized
UNION ALL
SELECT PayrollRecordId, ActivityName, EventDateTime, SourceSystem, LastDataUpdate, PayrollSpecialistName, DepartmentCode, PayGroupCode, GrossPayAmount, TaxJurisdictionCode, IsManualIntervention, IsRetrospective FROM incentive_data_imported
UNION ALL
SELECT PayrollRecordId, ActivityName, EventDateTime, SourceSystem, LastDataUpdate, PayrollSpecialistName, DepartmentCode, PayGroupCode, GrossPayAmount, TaxJurisdictionCode, IsManualIntervention, IsRetrospective FROM gross_pay_calculated
UNION ALL
SELECT PayrollRecordId, ActivityName, EventDateTime, SourceSystem, LastDataUpdate, PayrollSpecialistName, DepartmentCode, PayGroupCode, GrossPayAmount, TaxJurisdictionCode, IsManualIntervention, IsRetrospective FROM benefits_deductions_applied
UNION ALL
SELECT PayrollRecordId, ActivityName, EventDateTime, SourceSystem, LastDataUpdate, PayrollSpecialistName, DepartmentCode, PayGroupCode, GrossPayAmount, TaxJurisdictionCode, IsManualIntervention, IsRetrospective FROM audit_exception_flagged
UNION ALL
SELECT PayrollRecordId, ActivityName, EventDateTime, SourceSystem, LastDataUpdate, PayrollSpecialistName, DepartmentCode, PayGroupCode, GrossPayAmount, TaxJurisdictionCode, IsManualIntervention, IsRetrospective FROM data_correction_performed
UNION ALL
SELECT PayrollRecordId, ActivityName, EventDateTime, SourceSystem, LastDataUpdate, PayrollSpecialistName, DepartmentCode, PayGroupCode, GrossPayAmount, TaxJurisdictionCode, IsManualIntervention, IsRetrospective FROM payroll_result_previewed
UNION ALL
SELECT PayrollRecordId, ActivityName, EventDateTime, SourceSystem, LastDataUpdate, PayrollSpecialistName, DepartmentCode, PayGroupCode, GrossPayAmount, TaxJurisdictionCode, IsManualIntervention, IsRetrospective FROM payroll_record_approved
UNION ALL
SELECT PayrollRecordId, ActivityName, EventDateTime, SourceSystem, LastDataUpdate, PayrollSpecialistName, DepartmentCode, PayGroupCode, GrossPayAmount, TaxJurisdictionCode, IsManualIntervention, IsRetrospective FROM bank_transfer_file_generated
UNION ALL
SELECT PayrollRecordId, ActivityName, EventDateTime, SourceSystem, LastDataUpdate, PayrollSpecialistName, DepartmentCode, PayGroupCode, GrossPayAmount, TaxJurisdictionCode, IsManualIntervention, IsRetrospective FROM payment_executed
UNION ALL
SELECT PayrollRecordId, ActivityName, EventDateTime, SourceSystem, LastDataUpdate, PayrollSpecialistName, DepartmentCode, PayGroupCode, GrossPayAmount, TaxJurisdictionCode, IsManualIntervention, IsRetrospective FROM pay_slip_published
UNION ALL
SELECT PayrollRecordId, ActivityName, EventDateTime, SourceSystem, LastDataUpdate, PayrollSpecialistName, DepartmentCode, PayGroupCode, GrossPayAmount, TaxJurisdictionCode, IsManualIntervention, IsRetrospective FROM tax_filing_completed
UNION ALL
SELECT PayrollRecordId, ActivityName, EventDateTime, SourceSystem, LastDataUpdate, PayrollSpecialistName, DepartmentCode, PayGroupCode, GrossPayAmount, TaxJurisdictionCode, IsManualIntervention, IsRetrospective FROM retrospective_adjustment_posted
ORDER BY PayrollRecordId, EventDateTime, ActivityName;

准备开始了吗？

立即下载模板，将薪资数据转化为实际业务运营的可视化流程图。

返回改进指南下载模板

立即解决ADP Workforce Now中的薪资处理延迟

将周期时间缩短30%，停止人工数据修正

开始免费试用

无需信用卡，几分钟即可完成设置。

您的薪资处理数据模板

您的薪资处理数据模板

薪资处理属性

薪资处理活动

提取指南

ADP Workforce Now自定义报表导出

步骤

配置

a 示例查询 sql

ADP Workforce Now薪资API Python脚本

步骤

配置

a 示例查询 python

总账接口文件提取

步骤

配置

a 示例查询 sql

准备开始了吗？

立即解决ADP Workforce Now中的薪资处理延迟

您的薪资处理数据模板

您的薪资处理数据模板

薪资处理属性

薪资处理活动

提取指南

ADP Workforce Now自定义报表导出

步骤

配置

a 示例查询 sql

ADP Workforce Now薪资API Python脚本

步骤

配置

a 示例查询 python

总账接口文件提取

步骤

配置

a 示例查询 sql

准备开始了吗？

立即解决ADP Workforce Now中的薪资处理延迟

我们重视您的隐私