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

您的工资处理数据模板

ADP Workforce Now

← 返回改进指南

工资处理属性(17) 工资处理活动(15) 数据提取指南(3)

您的工资处理数据模板

此模板为您梳理ADP Workforce Now中的工资周期提供了完整框架，列出构建事件日志所需的关键属性和活动，帮助您发现瓶颈并确保整个组织符合合规要求。按照这些指引，您可以将原始系统数据转化为清晰的业务实际运行流程图。

建议用于深入分析的属性
关键流程步骤与里程碑
ADP Workforce Now数据提取最佳实践

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

下载模板

工资处理属性

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

5 必填 7 建议填写 5 选填

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

必填建议填写选填

工资处理活动

这些关键流程步骤和里程碑涵盖工资运行的完整周期，从最初录入工时到最终付款。

9 建议填写 6 选填

	活动	说明
	审计异常已标记	表示系统在计算过程中检测到违反校验规则的情况，例如实发工资为负或缺少税务管辖区信息。可从计算输出中生成的错误消息捕获此事件。
重要性用于驱动“审计异常解决情况监控”。异常数量较多通常表明上游数据质量存在问题。获取途径 Payroll Processing Error Log或“Verify Payroll”输出报告。采集系统生成校验错误时记录事件类型 explicit
	工时表已批准	表示经理已批准工时卡，授权将工时用于薪资处理。这是关键的把关步骤，可将数据从时间与出勤模块转入薪资模块。
重要性这是“工时表审批周期时间”仪表板的关键数据，可找出影响处理进度的管理瓶颈。获取途径 ADP时间与出勤审计记录；筛选状态变更为“已批准”的记录。采集执行“批准工时卡”交易时记录事件类型 explicit
	工时表已提交	表示员工或经理提交该薪资周期工时和出勤数据的时间。数据明确取自ADP Workforce Now中时间与出勤模块的审计日志。
重要性薪资周期时间从此时开始计算。此处的延误会传导至后续环节，压缩薪资专员验证数据的时间。获取途径 ADP时间与出勤审计记录；筛选Timecard对象中状态变更为“已提交”的记录。采集执行“提交工时卡”交易时记录事件类型 explicit
	工资单已发布	员工可通过自助服务门户（iPay）查看电子工资单。通常根据付款日期安排发布时间。
重要性用于分析“工资单发布周期时间”和员工满意度。获取途径根据应用于Check Date的系统设置（工资单可用规则）推断。采集比较Check Date字段与发布规则得出事件类型 inferred
	已执行付款	资金发放给员工的生效日期。通常根据薪资批次中设定的支票日期计算得出。
重要性这是流程的主要结果，也是计算“税务申报合规周期时间”的必要依据。获取途径薪资历史记录；“Check Date”字段。采集比较Check Date字段与System Date得出事件类型 calculated
	已生成银行转账文件	为直接存款创建NACHA/ACH文件。通常在“Finalize”步骤完成后立即触发。
重要性监控“银行转账文件生成耗时”，确保技术交接不会延误付款。获取途径输出/报告日志；“ACH File”或“Direct Deposit File”的生成时间戳。采集创建输出文件时记录事件类型 explicit
	税前工资已计算	系统按工时和工资率计算薪酬，并加上补充收入。根据“计算薪资”批处理流程的完成时间戳推断。
重要性核心处理步骤。多次出现通常表示存在返工路径，可在“薪资预览循环分析”中查看。获取途径薪资审计记录；特定Pay Group批次中“计算”操作的完成时间。采集根据计算作业的完成时间戳推断事件类型 inferred
	薪资记录已初始化	薪资批次在该周期创建或打开时触发，表示已为员工建立薪资案例。根据当前批次中Paydata记录的创建时间戳推断。
重要性确定该薪资计算时段的起点。与数据导入时间戳对比，可衡量“激励数据集成延迟”。获取途径薪资模块；Paydata批次标题的创建时间戳，或Paydata网格中的首条记录。采集比较批次创建前后的状态字段事件类型 inferred
	薪资记录已批准	正式确认薪资批次，锁定批次以防进一步修改，并授权资金转账。这是ADP WFN中的“Finalize”步骤。
重要性这是处理阶段结束、付款阶段开始的关键里程碑。获取途径薪资处理工作流；批次状态变更为“Finalized”或“Committed”。采集执行“Finalize Payroll”事务时记录事件类型 explicit
	已执行数据更正	薪资专员在Paydata Grid中手动修改数据，以解决错误或调整金额。可通过字段级审计跟踪捕获此操作。
重要性 “手动数据更正频率”的关键指标。更正率偏高可能表明自动化未能正常运行。获取途径 Paydata Grid审计跟踪；用户对薪资明细行执行的“Update”操作。采集执行“Update Paydata”事务时记录事件类型 explicit
	已提交追溯调整	对已关闭的薪资记录进行历史更正时触发，通常会影响下一个周期。此事件可识别流程正式结束后发现的错误。
重要性这是分析“追溯调整数量”的关键指标，可反映上游质量检查未能发现问题。获取途径薪资历史记录；新事务的“Effective Date”早于当前日期。采集执行“Post Adjustment”事务时记录事件类型 explicit
	已预览薪资结果	生成“Preview Payroll”工资清单或报告，供人工核验。此步骤用于在最终确认前验证计算结果。
重要性用于“薪资预览循环分析”。预览次数过多可能表明对数据缺乏把握。获取途径系统日志；“Preview Payroll”报告的生成时间戳。采集执行“Generate Preview”事务时记录事件类型 explicit
	激励数据已导入	记录通过ADP导入工具导入佣金或奖金等外部薪资数据的情况。此事件跟踪其他收入来源关联到薪资记录的时间。
重要性对于发现集成延迟至关重要。迟交数据往往会导致重新计算和返工。获取途径 ADP集成/导入日志；“Paydata Import”作业成功完成的时间戳。采集执行“导入Paydata”交易时记录事件类型 explicit
	福利扣款已应用	将福利规则（医疗、401k）应用于税前工资。通常与工资计算同时进行，但出于审计目的，在逻辑上属于独立步骤。
重要性与“审计异常已标记”关联，用于判断福利计算逻辑是否导致合规错误。获取途径根据计算事件后Paydata Grid中扣款明细行的创建或更新情况推断。采集比较计算前后的扣款字段事件类型 inferred
	税务申报已完成	标记该工资周期的法定税务申报已完成。对于使用ADP Tax Service的客户，此事件表示ADP已确认申报材料处理完毕。
重要性用于跟踪合规风险。延误可能导致罚款。获取途径税务模块；季度末或期间末申报状态更新。采集需要进行系统分析事件类型 inferred

建议填写选填

数据提取指南

如何从ADP Workforce Now获取数据

步骤

使用管理员或工资处理专员权限登录ADP Workforce Now。
进入“报表与分析”，从仪表板菜单中选择“自定义报表”。
点击“新建自定义报表”，选择工资数据域。根据版本不同，该选项可能标为“工资数据”或“工资登记表”。
在“字段选择”步骤中，找到并添加案例的唯一标识符。选择文件编号或员工ID，并结合发薪频率和工资周期编号构建PayrollRecordId。
选择映射活动所需的所有相关日期字段。必须包含工资周期开始日期、工资周期结束日期、支票日期、周期结束日期和计算日期。如果主视图中没有工时卡审批日期等审计字段，可能需要从“工时与考勤”模块关联获取。
添加财务和维度字段，包括税前工资、税后工资、部门、成本中心和税务管辖区。
如果可用，请将“审计跟踪”或“字段变更”区块添加到报表布局中。这对于记录“已执行数据更正”等人工操作至关重要。如果无法在一份报表中关联这些数据，则需另建审计报表，并在外部按员工ID和工资周期进行关联。
设置筛选条件，排除已作废支票，并将日期范围限定为所需的分析时段（例如最近12个月）。
进入“排序与汇总”步骤，先按工资周期开始日期排序，再按员工ID排序，确保数据顺序合理。
进入“运行/计划”步骤。先选择“立即运行”进行测试，再设置定期计划，将文件导出为CSV或Excel格式。
生成原始宽表报表后，使用ETL工具或脚本转换数据。必须对日期列进行逆透视，将每个日期字段转换为事件日志（活动）中的单独一行。
确认最终CSV包含所需列：PayrollRecordId、ActivityName、EventDateTime和SourceSystem，然后上传至ProcessMind。

配置

报表类型：标准自定义报表
数据域：工资/工资数据字典
输出格式：逗号分隔值（CSV）
建议日期范围：滚动选择12至24个月，用于趋势分析
关键筛选条件：工资组代码（选择相关组）、公司代码（适用于多实体组织）、状态为活跃或休假
数据粒度：原始数据按每位员工每个工资周期一行，转换后按每位员工生成多行
所需权限：查看工资数据、查看审计跟踪、创建自定义报表
本地化设置：在用户偏好设置中将日期格式设为ISO 8601（YYYY-MM-DD HH:MM:SS），避免格式歧义

查询示例 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客户端证书（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结构，查找计算、审批和文件生成等特定事件的时间戳。
脚本通过检查响应中的通知/警告数组，应用条件逻辑推断“审计异常已标记”等活动。
根据AssociateOID（ADP中的员工唯一标识符）合并工时和工资数据。
将最终事件列表转换为Pandas DataFrame，并按ProcessMind事件日志架构进行格式化。
运行脚本生成输出CSV文件，确认列标题符合要求后再上传至ProcessMind。

配置

Client ID和Secret：从ADP Developer Portal获取特定应用的凭据。
SSL证书：提供client.pem和client.key文件的路径。ADP API通信要求使用双向TLS（mTLS）。
API基础URL：通常为https://api.adp.com，特定环境可能使用不同地址（例如wfn-api.adp.com）。
日期范围：建议每次提取3至6个月的数据，以符合API分页和超时限制。
页面大小：将$top参数（上限）设为每次请求50或100条记录，以提高吞吐量并避免超时。
公司代码：如果ADP实例托管多个法人实体，请按组织单元或公司代码筛选。
AssociateOID：这是关联工时和工资数据的主键，与界面显示的员工ID不同。

查询示例 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总账接口、工资、工时与考勤、审计、导入、报表及适用的税务服务功能。确认具备所需的审计和工资报表授权。菜单名称和交易标识符因配置而异，请根据您的系统记录相应路径。
配置总账接口映射，涵盖所需的公司、工资组、收入、扣款、税项、部门和科目代码。保留生成的接口输出及其处理元数据。总账文件是工资金额和会计维度的主要来源，辅助数据提取则提供事件时间戳和活动证据。
从[您的表名]或相应的ADP Workforce Now导出文件中提取所选期间的数据集。所需数据类别包括工时与考勤审计事件、工资数据记录、激励导入记录、工资计算结果、扣款结果、验证消息、字段级审计记录、预览工资登记表、最终确认记录、银行文件记录、付款记录、工资单发布记录、税务申报确认、追溯调整记录和总账接口输出。
将查询中每个方括号内的来源引用替换为环境中实际可用的表、视图、报表或暂存导出数据。将来源字段映射到查询中列出的逻辑字段。未经记录推断规则，不要将推断出的时间戳视为直接审计证据。
将所有来源时间戳统一为同一时区，建议使用UTC，并将来源标识符统一转换为文本值。活动名称须与查询中列出的名称完全一致。将金额字段转换为统一数值类型，并将布尔指标规范为TRUE或FALSE。
在受控日期范围内执行查询，通常为三至六个月。使用工资记录标识符作为案例键。同一案例中的同一活动可能发生多次，请保留多条记录，因为ProcessMind会按原样读取事件日志，不会推断其他事件。
检查重复记录处理、时间戳排序和来源优先级。查询有意使用UNION ALL，以确保每项明确提取的活动都保留为事件行。如果某项活动没有直接来源事件，仅当配置的来源支持该推断时，才保留有记录的推断来源时间戳。
验证15个活动名称均已包含；来源支持的必填属性均已填写；事件日期范围与提取参数一致。将工资总额和会计维度与总账接口输出及适用的工资登记表进行核对。
将结果导出为扁平的UTF-8 CSV或其他ProcessMind支持的事件日志格式。包含以下列：PayrollRecordId、ActivityName、EventDateTime、SourceSystem、LastDataUpdate、PayrollSpecialistName、DepartmentCode、PayGroupCode、GrossPayAmount、TaxJurisdictionCode、IsManualIntervention和IsRetrospective。将文件上传至ProcessMind，并将PayrollRecordId设为案例标识符、ActivityName设为活动、EventDateTime设为事件时间戳。

配置

日期范围：从已完成的三至六个月工资周期开始。纳入足够的历史数据，涵盖常规工资发放、调整、激励、税务活动和追溯更正。使用[开始日期参数]和[结束日期参数]进行增量提取。
公司和工资组筛选条件：将提取范围限定为所需的公司代码、工资组代码、法人实体和工资日历。环境中包含多家公司时，使用[公司筛选条件]。
来源范围：纳入总账接口输出，以及工资、工时与考勤、导入、审计、计算、付款、工资单、税务和调整相关来源。通常仅凭总账文件无法证明完整生命周期中的每项活动。
活动筛选：使用查询中的15个ActivityName值，保持名称完全一致。不要将活动合并为更宽泛的类别，因为ProcessMind不会补充缺失事件。
时间戳策略：明确来源时区，并统一转换所有EventDateTime和LastDataUpdate值。如需完整审计能力，请在暂存层保留原始来源时间戳。
金额和会计配置：确认总账接口配置中的收入、扣款、税项、部门、成本中心和科目映射。将GrossPayAmount与工资计算或工资登记表来源核对，不要假定每条总账记录都是税前工资。
审计数据保留：启用或获取工时与考勤审计日志、工资数据字段级审计记录、计算消息、最终确认记录、付款记录和税务申报确认的保留设置。
性能：关联数据源前，先按工资周期、公司和工资组筛选。条件允许时，按PayrollRecordId、工资周期、事件时间戳和批次标识符为暂存数据建立索引或分区。大范围数据应分批增量处理。
授权：获取工资、工时与考勤、总账接口、审计、导入、付款、工资单和税务服务数据的访问权限。具体角色、许可证、菜单路径和交易标识符取决于ADP Workforce Now配置。
推断控制：在技术文档中明确标注“工资记录初始化”“税前工资计算”“已应用福利扣款”“已执行付款”等推断事件。如果来源不支持某项推断，应排除该行，不要编造时间戳。
文件处理：保留原始总账接口文件、来源数据提取文件、提取参数、查询版本和执行时间戳，以便核对和审计。

查询示例 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自定义报表导出

步骤

配置

查询示例 sql

ADP Workforce Now工资API Python脚本

步骤

配置

查询示例 python

总账接口文件提取

步骤

配置

查询示例 sql

准备好开始了吗？

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