Your Payroll Processing Data Template
Your Payroll Processing Data Template
- Optimized data fields for payroll analysis
- Comprehensive activity tracking for lifecycle mapping
- Detailed extraction guidance for Oracle HCM Cloud Payroll
Payroll Processing Attributes
| Name | Description | ||
|---|---|---|---|
| Activity Name ActivityName | The specific step or event performed in the payroll cycle. | ||
| Description This attribute captures the name of the event occurring within the payroll process. Examples include 'Time Card Submitted', 'Payroll Calculation Started', and 'Payment Executed'. It is the primary dimension for process discovery and variant analysis. In Oracle HCM Cloud, this is often derived from the Action Type or Action Status within the payroll action tables. Why it matters This defines the 'what' of the process, allowing the process map to be reconstructed. Where to get Derived from ACTION_TYPE code in PAY_PAYROLL_ACTIONS or status changes in audit trails. Examples Payroll Calculation StartedTime Card ApprovedBank Transfer GeneratedAudit Exception Flagged | |||
| Event Timestamp EventTimestamp | The exact date and time when the activity occurred. | ||
| Description Records the specific moment an activity took place. This is critical for calculating durations, lead times, and identifying bottlenecks. For automated steps, this is the system execution time. For manual steps like approvals, it is the timestamp of the user action recorded in the transaction log. Why it matters Essential for ordering events chronologically and calculating all time-based KPIs. Where to get CREATION_DATE or ACTION_DATE columns in relevant transaction tables like PAY_ACTION_INFORMATION. Examples 2023-10-01T08:30:00Z2023-10-01T14:15:22Z2023-10-05T09:00:00Z | |||
| Last Data Update LastDataUpdate | The timestamp when the record was last modified in the source database. | ||
| Description Indicates the freshness of the data used for analysis. This is distinct from the Event Timestamp; it reflects when the row in the database was last touched by an ETL process or system update. It is used to verify data currency and debug extraction latency issues. Why it matters Ensures analysts know if they are looking at real-time or stale data. Where to get LAST_UPDATE_DATE column common in almost all Oracle HCM tables. Examples 2023-10-02T12:00:00Z2023-10-06T01:00:00Z | |||
| Payroll Record PayrollRecord | Unique identifier representing an employee within a specific pay period. | ||
| Description The Payroll Record acts as the central case identifier for the process mining analysis. It is a concatenation or unique key that combines the Employee Assignment Identifier and the Payroll Period Identifier. This attribute groups all activities related to paying a single employee for a specific cycle, from the initial time card submission through to the final bank transfer and tax filing. It ensures that analysis can distinguish between the same employee being paid in January versus February. Why it matters It serves as the Case ID, binding all disparate events into a single process instance for analysis. Where to get Constructed from PAY_ASSIGNMENT_ACTIONS or a combination of Person Number and Payroll Period Name in Oracle HCM. Examples EMP1001-2023-M01EMP5992-2023-W42300000018273645US-NY-A123-JAN23 | |||
| Source System SourceSystem | The name of the system where the event originated. | ||
| Description Identifies the software or module that generated the data point. For this process, the primary value is 'Oracle HCM Cloud Payroll'. However, in complex landscapes, this might distinguish between the core payroll engine, the time and labor module, or an external banking interface. Why it matters Provides lineage context, especially when integrating data from timekeeping or banking subsystems. Where to get Hardcoded during extraction or derived from interface IDs. Examples Oracle HCM Cloud PayrollOracle Time and LaborExternal Banking Interface | |||
| Department Name DepartmentName | The organizational unit where the employee works. | ||
| Description Identifies the department responsible for the employee associated with the payroll record. This is crucial for the 'Timesheet Approval Efficiency' dashboard. By segmenting data by department, analysts can identify which business units are consistently late in approving time cards, delaying the overall payroll run. Why it matters Allows for root cause analysis of delays related to management approvals. Where to get Derived from the employee's assignment record effective at the time of the payroll run. Examples Sales North AmericaEngineeringHuman ResourcesLogistics | |||
| Gross Pay Amount GrossPayAmount | The total calculated gross pay for the period. | ||
| Description Represents the monetary value of the gross pay calculated during the cycle. This is used in the 'Tax and Benefit Calculation Accuracy' and 'Incentive Integration' analysis. It allows analysts to correlate high-value payments with processing time or error rates. Why it matters Provides financial context to the process mining analysis. Where to get PAY_RUN_RESULT_VALUES table, aggregated for Gross Pay balance. Examples 5000.002350.5010000.00 | |||
| Is Manual Correction IsManualCorrection | Flag indicating if the activity involved manual intervention. | ||
| Description A boolean flag that is set to true for activities like 'Data Correction Performed' or 'Payroll Record Corrected'. It is used to calculate the 'Manual Data Correction Rate' and 'First-Pass Payroll Accuracy Rate' KPIs. Why it matters Differentiates between straight-through processing and manual rework. Where to get Derived from Activity Name or specific Action Types indicating adjustments (e.g., QuickPay, Balance Adjustment). Examples truefalse | |||
| Legislative Data Group LegislativeDataGroup | Partitions payroll data by country or regulatory environment. | ||
| Description In Oracle HCM Cloud, the Legislative Data Group (LDG) partitions payroll and related data. It typically corresponds to a country or a specific territory's legislation. This attribute is vital for filtering the process analysis by country (e.g., US Payroll vs. UK Payroll) and is often mapped to the Country generic attribute. Why it matters Compliance rules and process flows often vary significantly by jurisdiction. Where to get PAY_ALL_PAYROLLS_F table joined with Legislative Data Group definitions. Examples US Legislative Data GroupUK LDGFrance Payroll | |||
| Pay Group PayGroup | Logical grouping of employees for payroll processing. | ||
| Description Represents the specific payroll definition the employee belongs to (e.g., 'Weekly Manufacturing', 'Monthly Corporate'). This attribute is central to almost all dashboards, allowing performance comparison across different payroll cycles and frequencies. Why it matters The primary configuration object that drives payroll scheduling. Where to get PAY_ALL_PAYROLLS_F table (Payroll Name). Examples US Semi-MonthlyUK MonthlyWeekly Union | |||
| Payroll Period Name PayrollPeriodName | The specific name of the time interval for the payroll run. | ||
| Description Identifies the specific cycle, such as 'Monthly January 2024' or 'Weekly Period 42'. This is essential for the 'SLA Deadline Compliance Monitor'. It helps group individual employee cases into their respective batch runs for aggregate reporting. Why it matters Fundamental for batch-level analysis and SLA tracking. Where to get PAY_TIME_PERIODS table or Time Period Name on the Payroll Definition. Examples 2023 Monthly 102023 Weekly 42Dec 2023 Bonus Run | |||
| Process Duration Days ProcessDurationDays | Total time from initialization to payment. | ||
| Description The calculated duration between the first activity and the 'Payment Executed' activity. Used for the 'End To End Payroll Cycle Time' dashboard. This metric serves as the high-level health check for the speed of the payroll function. Why it matters High-level KPI for process efficiency. Where to get Calculated: Timestamp(Payment Executed) - Timestamp(Start). Examples 3.55.00.5 | |||
| Processing User ProcessingUser | The user ID or name of the person performing the activity. | ||
| Description Captures the identity of the payroll specialist, manager, or system account that triggered the activity. This supports the 'Payroll Specialist Workload Distribution' dashboard. It helps in identifying resource bottlenecks and training needs among the payroll team. Why it matters Enables resource productivity analysis and workload balancing. Where to get CREATED_BY or LAST_UPDATED_BY columns in transaction tables. Examples sysadminj.smithpayroll_batch_userm.doe | |||
| SLA Processing Deadline SlaProcessingDeadline | The target date/time by which payment must be executed. | ||
| Description Stores the contractual or legal deadline for the payroll disbursement. This attribute is compared against the 'Payment Executed' timestamp. It is used to calculate the 'SLA Processing Deadline Adherence' KPI and identify at-risk pay groups. Why it matters The primary benchmark for process success or failure regarding timeliness. Where to get Usually a configured field (DFF) on the Payroll Definition or derived from the Check Date minus X days. Examples 2023-10-28T17:00:00Z2023-11-30T17:00:00Z | |||
| Audit Exception Type AuditExceptionType | The category of error or warning flagged during audit. | ||
| Description Categorizes the specific issue found during the 'Audit Exception Flagged' activity (e.g., 'Negative Net Pay', 'Missing Tax ID'). This attribute supports the 'Audit Exception and Correction Analysis' dashboard by pinpointing common data quality issues. Why it matters Critical for root cause analysis of rework loops. Where to get Message or Error Code columns in payroll process logs or exception reports. Examples Negative Net PayMissing SSNInvalid Bank DetailsOvertime Limit Exceeded | |||
| Employee Type EmployeeType | Categorization of the employee (e.g., Salaried, Hourly, Contractor). | ||
| Description Classifies the employee record. This is vital for the 'Digital Pay Slip Publishing Lag' dashboard, as different employee types may have different processing rules or urgencies. It helps filter analysis to compare process performance between different workforce segments. Why it matters Different employee types often follow different process variants. Where to get Assignment category or employment terms in PER_ALL_ASSIGNMENTS_M. Examples Full-Time SalariedPart-Time HourlyContractorExecutive | |||
| Preview Iteration Count PreviewIterationCount | Number of times results were previewed before finalization. | ||
| Description A counter that increments each time a 'Payroll Result Previewed' activity occurs for the same case. This is the direct measure for the 'Payroll Preview Iteration Tracker' dashboard, indicating the amount of rework or hesitation before approval. Why it matters Quantifies the efficiency of the validation phase. Where to get Calculated during data transformation by counting specific activity occurrences per Case ID. Examples 1350 | |||
| Retroactive Change Flag RetroactiveChangeFlag | Indicates if the payroll run includes retroactive payments. | ||
| Description A boolean indicator that is true if 'Retropay' elements are processed in this record. This helps explain longer processing times or audit exceptions in the 'Process Path Variant Comparison'. Retroactive changes often trigger complex recalculations spanning previous periods. Why it matters Identifies complex cases that naturally require more processing time. Where to get Presence of 'Retropay' element entries in PAY_ELEMENT_ENTRIES. Examples truefalse | |||
| SLA Status SlaStatus | Categorical status of SLA adherence (Met/Missed). | ||
| Description Derived by comparing the 'Payment Executed' timestamp with the 'SLA Processing Deadline'. Values might include 'On Time', 'At Risk', or 'Breached'. This simplifies reporting for the 'SLA Deadline Compliance Monitor' by grouping cases into performance buckets. Why it matters Simplifies complex time comparisons into actionable categories. Where to get Calculated logic comparing Event Timestamp vs SLA Deadline. Examples MetBreachedNear Breach | |||
| Tax Jurisdiction TaxJurisdiction | The specific state, province, or locality for tax filing. | ||
| Description Indicates the primary tax authority related to the payroll record (e.g., 'CA' for California, 'NY' for New York). This supports the 'Tax Compliance Processing Duration' dashboard. It helps identify if specific regions have more complex or slower tax calculation and filing processes. Why it matters Segments performance by regulatory complexity. Where to get Derived from the employee's tax card or work location addresses. Examples CA-CaliforniaNY-New YorkTX-TexasFederal-US | |||
Payroll Processing Activities
| Activity | Description | ||
|---|---|---|---|
| Bank Transfer Generated | The generation of the EFT (Electronic Funds Transfer) file or similar payment output. This is the technical step of creating the instruction file for the bank. | ||
| Why it matters Measures the Payment File Generation Cycle Time. Delays here can cause missed banking cut-off times. Where to get Query PAY_PAYROLL_ACTIONS where ACTION_TYPE is M (Magnetic Tape) or E (EFT) or C (Check Writer). Capture Logged when EFT/Check action completes Event type explicit | |||
| Payment Executed | The final confirmation that payment has been processed. In Oracle, this correlates to the completion of the payment flow or reconciliation in Cash Management. | ||
| Why it matters The primary timestamp for SLA Compliance. Used to determine if employees were paid on time. Where to get Can be inferred from the Effective Date of the Bank Transfer action, or explicitly via Cash Management reconciliation logs (CE_STATEMENT_LINES). Capture Logged via effective date of payment action or reconciliation event Event type explicit | |||
| Payroll Calculation Executed | The successful completion of the Calculate Payroll process, including gross-to-net calculations. This event indicates that earnings, deductions, and taxes have been computed for the assignment. | ||
| Why it matters A major milestone indicating that data is ready for validation. High durations here may indicate system performance issues or complex fast formulas. Where to get Query PAY_PAYROLL_REL_ACTIONS joining to PAY_PAYROLL_ACTIONS where ACTION_TYPE is R or Q and ACTION_STATUS changes to C (Complete). Capture Logged when Payroll Run action status updates to Complete Event type explicit | |||
| Payroll Calculation Started | The initiation of the main payroll run process for a specific payroll definition and period. This marks the transition from data collection to processing. | ||
| Why it matters Establishes the start of the heavy processing window. Used to calculate the total cycle time for the technical payroll execution. Where to get Query PAY_PAYROLL_ACTIONS table where ACTION_TYPE is R (Run) or Q (QuickPay) and ACTION_STATUS is marked as started (or inferred from creation date). Capture Logged when PAY_PAYROLL_ACTIONS record is inserted Event type explicit | |||
| Prepayments Calculated | The execution of the Prepayments process which distributes net pay to the employee's chosen payment methods (Check, EFT, etc.). This validates that the calculated amounts can actually be paid. | ||
| Why it matters The bridge between calculation and disbursement. Failures here usually indicate missing banking details or invalid payment methods. Where to get Query PAY_PAYROLL_ACTIONS where ACTION_TYPE is P (Prepayments) and status is Complete. Capture Logged when Prepayments action completes Event type explicit | |||
| Archive Results Generated | The completion of the Archive Periodic Payroll Results process. This snapshot effectively locks the data for reporting and payslip generation. | ||
| Why it matters A technical prerequisite for generating payslips and statutory reports. It confirms the data is finalized. Where to get Query PAY_PAYROLL_ACTIONS where ACTION_TYPE is X (Archive) and status is Complete. Capture Logged when Archive action completes Event type explicit | |||
| Audit Exception Flagged | The recording of an error or warning message during the payroll calculation or validation phase. This captures specific validation failures that require human intervention. | ||
| Why it matters Essential for identifying data quality issues. A high volume of exceptions increases manual effort and risk of SLA breach. Where to get Query PAY_MESSAGE_LINES table linked to the specific PAY_PAYROLL_REL_ACTION_ID. This table stores errors and warnings generated during runs. Capture Logged when a record is inserted into PAY_MESSAGE_LINES Event type explicit | |||
| Costing Transferred | The transfer of payroll costing results to the General Ledger. This represents the financial accounting closure of the payroll cycle. | ||
| Why it matters Ensures financial compliance and accurate booking of labor costs. Delays prevent Finance from closing books. Where to get Query PAY_PAYROLL_ACTIONS where ACTION_TYPE is T (Transfer to GL) and status is Complete. Capture Logged when Transfer to GL process completes Event type explicit | |||
| Incentive Data Imported | The creation of element entries for variable pay such as bonuses or commissions via batch loader or HDL import. This represents the integration of external compensation data into the payroll run. | ||
| Why it matters Monitors the integration speed of variable pay. Late imports often force supplemental payroll runs or manual corrections. Where to get Query PAY_BATCH_HEADERS for import completion or track creation of PAY_ELEMENT_ENTRIES_F with a specific source system reference. Capture Logged when batch element entry process completes successfully Event type explicit | |||
| Pay Slip Published | The point at which the payslip document becomes visible to the employee in Self Service. This follows the Archiver and potentially a specific 'Generate Payslip' process. | ||
| Why it matters Impacts employee satisfaction. Long lags between payment and payslip availability generate support tickets. Where to get Query the Documents of Record table (HR_DOCUMENTS_OF_RECORD) or the completion of the Generate Payslip process in PAY_PAYROLL_ACTIONS. Capture Logged when Payslip PDF generation process completes Event type explicit | |||
| Payroll Record Corrected | Manual updates applied to element entries or assignment data after an initial payroll run but before finalization. This activity represents rework required to fix errors or exceptions. | ||
| Why it matters Indicates process inefficiency and data quality problems. Reducing this activity is key to improving the First-Pass Payroll Accuracy Rate. Where to get Inferred by identifying updates to PAY_ELEMENT_ENTRIES_F where the Last Update Date is between the initial Payroll Run date and the Prepayments date. Capture Compare element entry update timestamps to payroll run timestamps Event type inferred | |||
| Payroll Run Approved | The formal approval of the payroll register or flow within the Payroll Dashboard. This typically happens after validation reports are reviewed and before bank files are generated. | ||
| Why it matters Represents the managerial sign-off. The time between calculation and approval represents the verification window. Where to get Query PAY_REQ_FLOW_INSTANCES for status changes or specific task completion within a defined Payroll Flow Pattern. Capture Logged when Flow Instance status changes to Completed/Approved Event type explicit | |||
| QuickPay Executed | The execution of a single-person payroll run (QuickPay) generally used for verifying corrections. This serves as a proxy for the Payroll Result Previewed activity. | ||
| Why it matters Frequent QuickPays per person indicate a trial-and-error approach to payroll processing rather than a Where to get Query PAY_PAYROLL_ACTIONS where ACTION_TYPE is Q (QuickPay). Count instances per payroll period per employee. Capture Logged when a QuickPay action is recorded in PAY_PAYROLL_ACTIONS Event type explicit | |||
| Tax Filing Completed | The generation of statutory tax files (e.g., Third Party Quarterly Tax File). This ensures regulatory compliance for the pay period. | ||
| Why it matters Critical for the Tax Compliance Processing Duration KPI. Failure here leads to penalties. Where to get Query PAY_PAYROLL_ACTIONS for specific legislative data group actions related to tax reporting (e.g., US Third Party Tax Filing). Capture Logged when specific tax reporting flow completes Event type explicit | |||
| Time Card Approved | The confirmation that submitted time data has been reviewed and authorized by a manager. This status change triggers the transfer of data to the payroll element entries. | ||
| Why it matters Critical for measuring the Manager Approval Lead Time. Delays here are a primary cause of retro-pay adjustments in subsequent periods. Where to get Query the HWM_TM_REC_GRP_DTLS table or HXT_TIMECARDS view where the status changes to APPROVED. Compare approval timestamp to submission timestamp. Capture Logged when status changes to APPROVED in Time and Labor Event type explicit | |||
| Time Card Submitted | The initial event where an employee or manager submits time and labor data for processing. In Oracle HCM, this is captured when the time card status changes to Submitted in the Time and Labor module. | ||
| Why it matters Marks the entry of raw data into the potential payroll pipeline. Delays here ripple downstream, shortening the window for payroll specialists to validate calculations. Where to get Query the HWM_TM_REC_GRP_DTLS table or HXT_TIMECARDS view where the status changes to SUBMITTED. Use the submission timestamp. Capture Logged when status changes to SUBMITTED in Time and Labor Event type explicit | |||
Extraction Guides
Steps
Access Data Exchange: Log in to Oracle HCM Cloud with a user that has the 'Human Capital Management Integration Specialist' role. Navigate to My Client Groups > Data Exchange > Extract Definitions.
Create New Extract: Click the '+' icon to create a new extract. Name it 'ProcessMind_Payroll_Extract'. Select 'Payroll' as the consumer and 'HR Archive' as the category to ensure access to historical payroll action data.
Define Data Groups: Create a Root Data Group based on the User Entity
PAY_PAYROLL_REL_UE(Payroll Relationship). This anchors the extraction to the employee's payroll relationship. Create child data groups forPAY_ACTION_INFORMATION_UE,PAY_ELEMENT_ENTRY_UE, andHWM_MEASURE_DAY_V_UE(for time and labor).Configure Extract Records: Within the data groups, define records to flatten the hierarchical data. You must create specific records for each activity type (e.g., a record filtering for 'Time Card' events, another for 'Run Results').
Implement SQL Logic via FastFormula or BIP: Due to the complexity of transforming hierarchical payroll data into a linear event log, the most robust approach is to use the 'Extract Delivery Option' to call a BI Publisher Report. This allows you to use the SQL provided in the 'Query' section below as the Data Model. This is superior to native GUI filtering for creating a unified event log.
Create BI Publisher Data Model: Navigate to Tools > Reports and Analytics > Browse Catalog. Create a new Data Model. Paste the SQL query provided below. Define a Parameter for
bind_start_dateandbind_end_date.Link Extract to BIP: In the Extract Definition 'Delivery' tab, create a delivery option. Select 'PDF' or 'XML' as the output type (ProcessMind prefers CSV, but you will convert the BIP output). Select the BI Publisher report you created in the previous step.
Define Parameters: In the Extract Execution Tree, add parameters for the Start Date and End Date to pass to the BI Publisher query dynamically.
Validate and Submit: Validate the extract definition to check for errors. Click 'Submit Extract'. Enter the parameters (e.g., the last 90 days).
Monitor Process: Go to 'View Extract Results'. Wait for the status to change to 'Succeeded'.
Download Output: Download the generated XML/CSV file. Open it to verify the structure contains the required columns: PayrollRecord, ActivityName, EventTimestamp, etc.
Format for ProcessMind: If the output is XML, convert it to a flat CSV file ensuring the headers match the 'Attributes to include' requirements. Upload the resulting file to ProcessMind.
Configuration
- Extraction Mode: Changes Only vs. Full Extract. For the initial load, use Full Extract. For daily updates, configure 'Changes Only' based on the Last Update Date.
- Legislative Data Group (LDG): It is highly recommended to filter by LDG if your instance supports multiple countries, as payroll definitions vary by legislation.
- Date Range: Set the
bind_start_dateandbind_end_dateto cover at least 3-6 months for process mining to capture full cycle times. - Chunk Size: For high-volume payrolls (10k+ employees), set the chunk size in the Extract Definition to 2000 to prevent timeout errors.
- Effective Dating: Payroll tables are date-effective. The query logic explicitly handles
effective_start_dateandeffective_end_dateto ensure the correct historical version of the record is retrieved. - Security: The executing user must have data security profiles that allow viewing all payrolls and departments intended for analysis.
a Sample Query config
/* Oracle HCM Cloud Payroll Extraction for ProcessMind */
/* Aggregating 16 specific activities into a unified Event Log */
SELECT
/* Attributes */
rel.person_number || '-' || ppa.payroll_action_id AS PayrollRecord,
'Time Card Submitted' AS ActivityName,
TO_CHAR(htr.creation_date, 'YYYY-MM-DD HH24:MI:SS') AS EventTimestamp,
'Oracle HCM Time and Labor' AS SourceSystem,
TO_CHAR(htr.last_update_date, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
pap.name AS PayrollPeriodName,
org.name AS DepartmentName,
NULL AS GrossPayAmount
FROM hwm_tm_rec htr
JOIN hwm_tm_rec_grp htrg ON htr.tm_rec_grp_id = htrg.tm_rec_grp_id
JOIN per_all_people_f rel ON htrg.person_id = rel.person_id
LEFT JOIN pay_all_payrolls_f pap ON htrg.payroll_id = pap.payroll_id
LEFT JOIN per_departments org ON rel.attribute1 = org.organization_id /* Adjust mapping */
WHERE htr.latest_version_flag = 'Y'
AND htr.tm_rec_status = 'SUBMITTED'
AND htr.creation_date BETWEEN :bind_start_date AND :bind_end_date
UNION ALL
SELECT
rel.person_number || '-' || ppa.payroll_action_id,
'Time Card Approved',
TO_CHAR(htr.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
'Oracle HCM Time and Labor',
TO_CHAR(htr.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
pap.name,
org.name,
NULL
FROM hwm_tm_rec htr
JOIN hwm_tm_rec_grp htrg ON htr.tm_rec_grp_id = htrg.tm_rec_grp_id
JOIN per_all_people_f rel ON htrg.person_id = rel.person_id
LEFT JOIN pay_all_payrolls_f pap ON htrg.payroll_id = pap.payroll_id
LEFT JOIN per_departments org ON rel.attribute1 = org.organization_id
WHERE htr.latest_version_flag = 'Y'
AND htr.tm_rec_status = 'APPROVED'
AND htr.last_update_date BETWEEN :bind_start_date AND :bind_end_date
UNION ALL
SELECT
rel.person_number || '-' || pee.element_entry_id,
'Incentive Data Imported',
TO_CHAR(pee.creation_date, 'YYYY-MM-DD HH24:MI:SS'),
'Oracle HCM Payroll',
TO_CHAR(pee.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
NULL,
org.name,
NULL
FROM pay_element_entries_f pee
JOIN pay_element_types_f pet ON pee.element_type_id = pet.element_type_id
JOIN per_all_assignments_m asg ON pee.assignment_id = asg.assignment_id
JOIN per_all_people_f rel ON asg.person_id = rel.person_id
LEFT JOIN per_departments org ON asg.organization_id = org.organization_id
WHERE pet.classification_name IN ('Supplemental Earnings', 'Voluntary Deductions')
AND pee.creator_type IN ('H', 'F') /* HDL or Flat File */
AND pee.creation_date BETWEEN :bind_start_date AND :bind_end_date
UNION ALL
SELECT
rel.person_number || '-' || ppa.payroll_action_id,
'Payroll Calculation Started',
TO_CHAR(ppa.creation_date, 'YYYY-MM-DD HH24:MI:SS'),
'Oracle HCM Payroll',
TO_CHAR(ppa.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
pap.name,
org.name,
NULL
FROM pay_payroll_actions ppa
JOIN pay_all_payrolls_f pap ON ppa.payroll_id = pap.payroll_id
JOIN pay_payroll_rel_actions pra ON ppa.payroll_action_id = pra.payroll_action_id
JOIN per_all_people_f rel ON pra.payroll_relationship_id = rel.person_id /* Simplified Join */
LEFT JOIN per_departments org ON rel.attribute1 = org.organization_id
WHERE ppa.action_type IN ('R', 'Q') /* Run or QuickPay */
AND ppa.creation_date BETWEEN :bind_start_date AND :bind_end_date
UNION ALL
SELECT
rel.person_number || '-' || ppa.payroll_action_id,
'Payroll Calculation Executed',
TO_CHAR(pra.action_sequence_date, 'YYYY-MM-DD HH24:MI:SS'),
'Oracle HCM Payroll',
TO_CHAR(pra.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
pap.name,
org.name,
(SELECT SUM(prrv.result_value) FROM pay_run_result_values prrv JOIN pay_run_results prr ON prrv.run_result_id = prr.run_result_id WHERE prr.payroll_rel_action_id = pra.payroll_rel_action_id) AS GrossPayAmount
FROM pay_payroll_rel_actions pra
JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id
JOIN pay_all_payrolls_f pap ON ppa.payroll_id = pap.payroll_id
JOIN per_all_people_f rel ON pra.payroll_relationship_id = rel.person_id
LEFT JOIN per_departments org ON rel.attribute1 = org.organization_id
WHERE ppa.action_type IN ('R', 'Q')
AND pra.action_status = 'C'
AND pra.action_sequence_date BETWEEN :bind_start_date AND :bind_end_date
UNION ALL
SELECT
rel.person_number || '-' || ppa.payroll_action_id,
'Audit Exception Flagged',
TO_CHAR(pml.creation_date, 'YYYY-MM-DD HH24:MI:SS'),
'Oracle HCM Payroll',
TO_CHAR(pml.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
pap.name,
org.name,
NULL
FROM pay_message_lines pml
JOIN pay_payroll_rel_actions pra ON pml.source_id = pra.payroll_rel_action_id
JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id
JOIN pay_all_payrolls_f pap ON ppa.payroll_id = pap.payroll_id
JOIN per_all_people_f rel ON pra.payroll_relationship_id = rel.person_id
LEFT JOIN per_departments org ON rel.attribute1 = org.organization_id
WHERE pml.message_level IN ('F', 'E') /* Fatal or Error */
AND pml.creation_date BETWEEN :bind_start_date AND :bind_end_date
UNION ALL
SELECT
rel.person_number || '-' || ppa.payroll_action_id,
'Payroll Record Corrected',
TO_CHAR(pee.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
'Oracle HCM Payroll',
TO_CHAR(pee.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
pap.name,
org.name,
NULL
FROM pay_element_entries_f pee
JOIN pay_payroll_rel_actions pra ON pee.creator_id = pra.payroll_rel_action_id
JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id
JOIN pay_all_payrolls_f pap ON ppa.payroll_id = pap.payroll_id
JOIN per_all_people_f rel ON pra.payroll_relationship_id = rel.person_id
LEFT JOIN per_departments org ON rel.attribute1 = org.organization_id
WHERE pee.last_update_date > pee.creation_date
AND pee.last_update_date BETWEEN :bind_start_date AND :bind_end_date
UNION ALL
SELECT
rel.person_number || '-' || ppa.payroll_action_id,
'QuickPay Executed',
TO_CHAR(pra.action_sequence_date, 'YYYY-MM-DD HH24:MI:SS'),
'Oracle HCM Payroll',
TO_CHAR(pra.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
pap.name,
org.name,
NULL
FROM pay_payroll_rel_actions pra
JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id
JOIN pay_all_payrolls_f pap ON ppa.payroll_id = pap.payroll_id
JOIN per_all_people_f rel ON pra.payroll_relationship_id = rel.person_id
LEFT JOIN per_departments org ON rel.attribute1 = org.organization_id
WHERE ppa.action_type = 'Q'
AND pra.action_status = 'C'
AND pra.action_sequence_date BETWEEN :bind_start_date AND :bind_end_date
UNION ALL
SELECT
rel.person_number || '-' || ppa.payroll_action_id,
'Prepayments Calculated',
TO_CHAR(pra.action_sequence_date, 'YYYY-MM-DD HH24:MI:SS'),
'Oracle HCM Payroll',
TO_CHAR(pra.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
pap.name,
org.name,
NULL
FROM pay_payroll_rel_actions pra
JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id
JOIN pay_all_payrolls_f pap ON ppa.payroll_id = pap.payroll_id
JOIN per_all_people_f rel ON pra.payroll_relationship_id = rel.person_id
LEFT JOIN per_departments org ON rel.attribute1 = org.organization_id
WHERE ppa.action_type = 'P' /* Distribution */
AND pra.action_status = 'C'
AND pra.action_sequence_date BETWEEN :bind_start_date AND :bind_end_date
UNION ALL
SELECT
rel.person_number || '-' || ppa.payroll_action_id,
'Payroll Run Approved',
TO_CHAR(pfi.action_date, 'YYYY-MM-DD HH24:MI:SS'),
'Oracle HCM Payroll',
TO_CHAR(pfi.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
pap.name,
org.name,
NULL
FROM pay_flow_instances pfi
JOIN pay_payroll_actions ppa ON pfi.payroll_action_id = ppa.payroll_action_id
JOIN pay_all_payrolls_f pap ON ppa.payroll_id = pap.payroll_id
JOIN pay_payroll_rel_actions pra ON ppa.payroll_action_id = pra.payroll_action_id
JOIN per_all_people_f rel ON pra.payroll_relationship_id = rel.person_id
LEFT JOIN per_departments org ON rel.attribute1 = org.organization_id
WHERE pfi.status = 'COMPLETED'
AND pfi.instance_name LIKE '%Approval%'
AND pfi.action_date BETWEEN :bind_start_date AND :bind_end_date
UNION ALL
SELECT
rel.person_number || '-' || ppa.payroll_action_id,
'Archive Results Generated',
TO_CHAR(pra.action_sequence_date, 'YYYY-MM-DD HH24:MI:SS'),
'Oracle HCM Payroll',
TO_CHAR(pra.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
pap.name,
org.name,
NULL
FROM pay_payroll_rel_actions pra
JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id
JOIN pay_all_payrolls_f pap ON ppa.payroll_id = pap.payroll_id
JOIN per_all_people_f rel ON pra.payroll_relationship_id = rel.person_id
LEFT JOIN per_departments org ON rel.attribute1 = org.organization_id
WHERE ppa.action_type = 'X' /* Archive */
AND pra.action_status = 'C'
AND pra.action_sequence_date BETWEEN :bind_start_date AND :bind_end_date
UNION ALL
SELECT
rel.person_number || '-' || ppa.payroll_action_id,
'Bank Transfer Generated',
TO_CHAR(pra.action_sequence_date, 'YYYY-MM-DD HH24:MI:SS'),
'Oracle HCM Payroll',
TO_CHAR(pra.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
pap.name,
org.name,
(SELECT SUM(ppp.value) FROM pay_pre_payments ppp WHERE ppp.payroll_action_id = ppa.payroll_action_id) AS GrossPayAmount
FROM pay_payroll_rel_actions pra
JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id
JOIN pay_all_payrolls_f pap ON ppa.payroll_id = pap.payroll_id
JOIN per_all_people_f rel ON pra.payroll_relationship_id = rel.person_id
LEFT JOIN per_departments org ON rel.attribute1 = org.organization_id
WHERE ppa.action_type IN ('M', 'E') /* Mag Tape or EFT */
AND pra.action_status = 'C'
AND pra.action_sequence_date BETWEEN :bind_start_date AND :bind_end_date
UNION ALL
SELECT
rel.person_number || '-' || ppa.payroll_action_id,
'Payment Executed',
TO_CHAR(ppp.base_currency_value, 'YYYY-MM-DD HH24:MI:SS'),
'Oracle HCM Payroll',
TO_CHAR(ppa.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
pap.name,
org.name,
ppp.value AS GrossPayAmount
FROM pay_pre_payments ppp
JOIN pay_payroll_actions ppa ON ppp.payroll_action_id = ppa.payroll_action_id
JOIN pay_all_payrolls_f pap ON ppa.payroll_id = pap.payroll_id
JOIN pay_payroll_rel_actions pra ON ppa.payroll_action_id = pra.payroll_action_id
JOIN per_all_people_f rel ON pra.payroll_relationship_id = rel.person_id
LEFT JOIN per_departments org ON rel.attribute1 = org.organization_id
WHERE ppa.action_type IN ('H', 'E', 'M') /* Check or EFT */
AND ppa.effective_date BETWEEN :bind_start_date AND :bind_end_date
UNION ALL
SELECT
rel.person_number || '-' || ppa.payroll_action_id,
'Pay Slip Published',
TO_CHAR(pra.action_sequence_date, 'YYYY-MM-DD HH24:MI:SS'),
'Oracle HCM Payroll',
TO_CHAR(pra.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
pap.name,
org.name,
NULL
FROM pay_payroll_rel_actions pra
JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id
JOIN pay_all_payrolls_f pap ON ppa.payroll_id = pap.payroll_id
JOIN per_all_people_f rel ON pra.payroll_relationship_id = rel.person_id
LEFT JOIN per_departments org ON rel.attribute1 = org.organization_id
WHERE ppa.action_type = 'G' /* Generate Payslip */
AND pra.action_status = 'C'
AND pra.action_sequence_date BETWEEN :bind_start_date AND :bind_end_date
UNION ALL
SELECT
rel.person_number || '-' || ppa.payroll_action_id,
'Costing Transferred',
TO_CHAR(pra.action_sequence_date, 'YYYY-MM-DD HH24:MI:SS'),
'Oracle HCM Payroll',
TO_CHAR(pra.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
pap.name,
org.name,
NULL
FROM pay_payroll_rel_actions pra
JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id
JOIN pay_all_payrolls_f pap ON ppa.payroll_id = pap.payroll_id
JOIN per_all_people_f rel ON pra.payroll_relationship_id = rel.person_id
LEFT JOIN per_departments org ON rel.attribute1 = org.organization_id
WHERE ppa.action_type = 'T' /* Transfer to GL */
AND pra.action_status = 'C'
AND pra.action_sequence_date BETWEEN :bind_start_date AND :bind_end_date
UNION ALL
SELECT
rel.person_number || '-' || ppa.payroll_action_id,
'Tax Filing Completed',
TO_CHAR(pra.action_sequence_date, 'YYYY-MM-DD HH24:MI:SS'),
'Oracle HCM Payroll',
TO_CHAR(pra.last_update_date, 'YYYY-MM-DD HH24:MI:SS'),
pap.name,
org.name,
NULL
FROM pay_payroll_rel_actions pra
JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id
JOIN pay_all_payrolls_f pap ON ppa.payroll_id = pap.payroll_id
JOIN per_all_people_f rel ON pra.payroll_relationship_id = rel.person_id
LEFT JOIN per_departments org ON rel.attribute1 = org.organization_id
WHERE ppa.report_category IN ('Tax', 'Regulatory') /* Customize based on local requirements */
AND pra.action_status = 'C'
AND pra.action_sequence_date BETWEEN :bind_start_date AND :bind_end_dateSteps
- Log in to Oracle BI Publisher: Access the Oracle Cloud environment and navigate to Tools > Reports and Analytics. Click Browse Catalog to open the BI Publisher interface.
- Create a Data Model: Click New (top left) and select Data Model. This is the container for your SQL extraction logic.
- Create a SQL Data Set: Under the Diagram tab, click the New Data Set icon and select SQL Query.
- Configure Data Source: Name the data set (e.g.,
ProcessMind_Payroll_Extract). Set the Data Source toApplicationDB_HCM(or your specific HCM Application Database connection). Leave the type as Standard SQL. - Input the Query: Copy the complete SQL script provided in the Query section below and paste it into the SQL Query text box. Ensure you do not include any markdown formatting.
- Define Parameters: The query uses bind variables
:p_start_dateand:p_end_date. In the Parameters tab of the Data Model, create these two parameters (Date type) to filter the extraction range (e.g., based onppa.EFFECTIVE_DATE). - Validate Data Structure: Click OK. Switch to the Data tab within the Data Model editor. Enter sample dates (e.g.,
2023-01-01to2023-03-31) and click View. Ensure the output contains rows withPayrollRecord,ActivityName, andEventTimestamp. - Save the Data Model: Save the model in a shared folder (e.g.,
/Shared Folders/Custom/ProcessMining). - Create Report for Export: Click Create Report, select the Data Model you just saved. Use the Report Wizard to create a simple Table layout containing all columns. Uncheck 'Show Grand Totals'.
- Export to CSV: View the report in the Report Viewer. Click the Actions menu (gear icon) > Export > Data > CSV.
- Transform: Open the CSV. Ensure date formats are ISO 8601 (
YYYY-MM-DD HH:MM:SS) if not automatically formatted. No further reshaping should be needed if the SQL was pasted correctly. - Upload: Import the resulting CSV file into ProcessMind, mapping the columns as defined in the Data Requirements.
Configuration
- Data Source Connection: Must use
ApplicationDB_HCMor the equivalent JNDI data source that has read access toPAY_andPER_schemas. - Date Range: Recommended extraction window is 3-6 months to capture full payroll cycles. Use the
:p_start_dateand:p_end_dateparameters to control this dynamic loading. - Legislative Data Group (LDG): The query extracts all LDGs by default. If you run multiple countries, consider adding a filter
AND ppa.LEGISLATIVE_DATA_GROUP_ID = [Your_LDG_ID]to partition the data. - Performance: The query hits high-volume tables (
PAY_RUN_RESULTS,PAY_ASSIGNMENT_ACTIONS). Ensure the date range is not too wide (e.g., >1 year) to prevent timeout errors in BI Publisher. - Security: The user running the report needs Data Access Set permissions for the target Payroll definitions.
a Sample Query sql
SELECT
TO_CHAR(papf.PERSON_NUMBER) || '-' || ptp.PERIOD_NAME AS PayrollRecord,
'Time Card Submitted' AS ActivityName,
peef.CREATION_DATE AS EventTimestamp,
'Oracle HCM' AS SourceSystem,
peef.LAST_UPDATE_DATE AS LastDataUpdate,
pld.NAME AS LegislativeDataGroup,
org.NAME AS DepartmentName,
peef.LAST_UPDATED_BY AS ProcessingUser,
NULL AS GrossPayAmount,
ptp.PERIOD_NAME AS PayrollPeriodName,
NULL AS SlaProcessingDeadline,
'N' AS IsManualCorrection,
pg.PAYROLL_NAME AS PayGroup,
NULL AS ProcessDurationDays
FROM PAY_ELEMENT_ENTRIES_F peef
JOIN PER_ALL_ASSIGNMENTS_M paam ON peef.ASSIGNMENT_ID = paam.ASSIGNMENT_ID
AND peef.EFFECTIVE_START_DATE BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE
JOIN PER_ALL_PEOPLE_F papf ON paam.PERSON_ID = papf.PERSON_ID
AND peef.EFFECTIVE_START_DATE BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
JOIN PAY_PAY_RELATIONSHIPS_DN pprd ON paam.PAYROLL_RELATIONSHIP_ID = pprd.PAYROLL_RELATIONSHIP_ID
JOIN PER_TIME_PERIODS ptp ON pprd.PAYROLL_ID = ptp.PAYROLL_ID
AND peef.EFFECTIVE_START_DATE BETWEEN ptp.START_DATE AND ptp.END_DATE
LEFT JOIN PER_LEGISLATIVE_DATA_GROUPS_VL pld ON pprd.LEGISLATIVE_DATA_GROUP_ID = pld.LEGISLATIVE_DATA_GROUP_ID
LEFT JOIN HR_ORGANIZATION_UNITS_F_TL org ON paam.ORGANIZATION_ID = org.ORGANIZATION_ID AND org.LANGUAGE = USERENV('LANG')
LEFT JOIN PAY_ALL_PAYROLLS_F pg ON pprd.PAYROLL_ID = pg.PAYROLL_ID
WHERE peef.CREATOR_TYPE = 'H'
AND peef.CREATION_DATE BETWEEN :p_start_date AND :p_end_date
UNION ALL
SELECT
TO_CHAR(papf.PERSON_NUMBER) || '-' || ptp.PERIOD_NAME AS PayrollRecord,
'Time Card Approved' AS ActivityName,
peef.EFFECTIVE_START_DATE AS EventTimestamp,
'Oracle HCM' AS SourceSystem,
peef.LAST_UPDATE_DATE AS LastDataUpdate,
pld.NAME,
org.NAME,
peef.LAST_UPDATED_BY,
NULL,
ptp.PERIOD_NAME,
NULL,
'N',
pg.PAYROLL_NAME,
NULL
FROM PAY_ELEMENT_ENTRIES_F peef
JOIN PER_ALL_ASSIGNMENTS_M paam ON peef.ASSIGNMENT_ID = paam.ASSIGNMENT_ID
AND peef.EFFECTIVE_START_DATE BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE
JOIN PER_ALL_PEOPLE_F papf ON paam.PERSON_ID = papf.PERSON_ID
AND peef.EFFECTIVE_START_DATE BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
JOIN PAY_PAY_RELATIONSHIPS_DN pprd ON paam.PAYROLL_RELATIONSHIP_ID = pprd.PAYROLL_RELATIONSHIP_ID
JOIN PER_TIME_PERIODS ptp ON pprd.PAYROLL_ID = ptp.PAYROLL_ID
AND peef.EFFECTIVE_START_DATE BETWEEN ptp.START_DATE AND ptp.END_DATE
LEFT JOIN PER_LEGISLATIVE_DATA_GROUPS_VL pld ON pprd.LEGISLATIVE_DATA_GROUP_ID = pld.LEGISLATIVE_DATA_GROUP_ID
LEFT JOIN HR_ORGANIZATION_UNITS_F_TL org ON paam.ORGANIZATION_ID = org.ORGANIZATION_ID AND org.LANGUAGE = USERENV('LANG')
LEFT JOIN PAY_ALL_PAYROLLS_F pg ON pprd.PAYROLL_ID = pg.PAYROLL_ID
WHERE peef.CREATOR_TYPE = 'H'
AND peef.EFFECTIVE_START_DATE BETWEEN :p_start_date AND :p_end_date
UNION ALL
SELECT
TO_CHAR(papf.PERSON_NUMBER) || '-' || ptp.PERIOD_NAME AS PayrollRecord,
'Incentive Data Imported' AS ActivityName,
peef.CREATION_DATE AS EventTimestamp,
'Oracle HCM' AS SourceSystem,
peef.LAST_UPDATE_DATE AS LastDataUpdate,
pld.NAME,
org.NAME,
peef.LAST_UPDATED_BY,
NULL,
ptp.PERIOD_NAME,
NULL,
'N',
pg.PAYROLL_NAME,
NULL
FROM PAY_ELEMENT_ENTRIES_F peef
JOIN PER_ALL_ASSIGNMENTS_M paam ON peef.ASSIGNMENT_ID = paam.ASSIGNMENT_ID
AND peef.EFFECTIVE_START_DATE BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE
JOIN PER_ALL_PEOPLE_F papf ON paam.PERSON_ID = papf.PERSON_ID
AND peef.EFFECTIVE_START_DATE BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
JOIN PAY_PAY_RELATIONSHIPS_DN pprd ON paam.PAYROLL_RELATIONSHIP_ID = pprd.PAYROLL_RELATIONSHIP_ID
JOIN PER_TIME_PERIODS ptp ON pprd.PAYROLL_ID = ptp.PAYROLL_ID
AND peef.EFFECTIVE_START_DATE BETWEEN ptp.START_DATE AND ptp.END_DATE
LEFT JOIN PER_LEGISLATIVE_DATA_GROUPS_VL pld ON pprd.LEGISLATIVE_DATA_GROUP_ID = pld.LEGISLATIVE_DATA_GROUP_ID
LEFT JOIN HR_ORGANIZATION_UNITS_F_TL org ON paam.ORGANIZATION_ID = org.ORGANIZATION_ID AND org.LANGUAGE = USERENV('LANG')
LEFT JOIN PAY_ALL_PAYROLLS_F pg ON pprd.PAYROLL_ID = pg.PAYROLL_ID
WHERE peef.CREATOR_TYPE IN ('B', 'F')
AND peef.CREATION_DATE BETWEEN :p_start_date AND :p_end_date
UNION ALL
SELECT
TO_CHAR(papf.PERSON_NUMBER) || '-' || ptp.PERIOD_NAME AS PayrollRecord,
CASE
WHEN ppa.ACTION_TYPE = 'R' AND paa.ACTION_STATUS = 'C' THEN 'Payroll Calculation Executed'
WHEN ppa.ACTION_TYPE = 'R' THEN 'Payroll Calculation Started'
WHEN ppa.ACTION_TYPE = 'Q' THEN 'QuickPay Executed'
WHEN ppa.ACTION_TYPE IN ('P', 'U') THEN 'Prepayments Calculated'
WHEN ppa.ACTION_TYPE = 'X' THEN 'Archive Results Generated'
WHEN ppa.ACTION_TYPE = 'M' THEN 'Bank Transfer Generated'
WHEN ppa.ACTION_TYPE IN ('Z', 'E') THEN 'Payment Executed'
WHEN ppa.ACTION_TYPE = 'T' THEN 'Costing Transferred'
ELSE 'Payroll Process Action'
END AS ActivityName,
ppa.CREATION_DATE AS EventTimestamp,
'Oracle HCM' AS SourceSystem,
paa.LAST_UPDATE_DATE AS LastDataUpdate,
pld.NAME,
org.NAME,
ppa.LAST_UPDATED_BY,
NULL,
ptp.PERIOD_NAME,
ptp.REGULAR_PAYMENT_DATE AS SlaProcessingDeadline,
'N',
pg.PAYROLL_NAME,
NULL
FROM PAY_PAYROLL_ACTIONS ppa
JOIN PAY_ASSIGNMENT_ACTIONS paa ON ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
JOIN PER_ALL_ASSIGNMENTS_M paam ON paa.ASSIGNMENT_ID = paam.ASSIGNMENT_ID
AND ppa.EFFECTIVE_DATE BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE
JOIN PER_ALL_PEOPLE_F papf ON paam.PERSON_ID = papf.PERSON_ID
AND ppa.EFFECTIVE_DATE BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
JOIN PER_TIME_PERIODS ptp ON ppa.PAYROLL_ID = ptp.PAYROLL_ID
AND ppa.DATE_EARNED BETWEEN ptp.START_DATE AND ptp.END_DATE
LEFT JOIN PER_LEGISLATIVE_DATA_GROUPS_VL pld ON ppa.LEGISLATIVE_DATA_GROUP_ID = pld.LEGISLATIVE_DATA_GROUP_ID
LEFT JOIN HR_ORGANIZATION_UNITS_F_TL org ON paam.ORGANIZATION_ID = org.ORGANIZATION_ID AND org.LANGUAGE = USERENV('LANG')
LEFT JOIN PAY_ALL_PAYROLLS_F pg ON ppa.PAYROLL_ID = pg.PAYROLL_ID
WHERE ppa.ACTION_TYPE IN ('R', 'Q', 'P', 'U', 'X', 'M', 'Z', 'E', 'T')
AND ppa.EFFECTIVE_DATE BETWEEN :p_start_date AND :p_end_date
UNION ALL
SELECT
TO_CHAR(papf.PERSON_NUMBER) || '-' || ptp.PERIOD_NAME AS PayrollRecord,
'Audit Exception Flagged' AS ActivityName,
pml.CREATION_DATE AS EventTimestamp,
'Oracle HCM' AS SourceSystem,
pml.LAST_UPDATE_DATE AS LastDataUpdate,
pld.NAME,
org.NAME,
pml.LAST_UPDATED_BY,
NULL,
ptp.PERIOD_NAME,
NULL,
'Y',
pg.PAYROLL_NAME,
NULL
FROM PAY_MESSAGE_LINES pml
JOIN PAY_PAYROLL_ACTIONS ppa ON pml.PAYROLL_ACTION_ID = ppa.PAYROLL_ACTION_ID
LEFT JOIN PAY_ASSIGNMENT_ACTIONS paa ON ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
JOIN PER_ALL_ASSIGNMENTS_M paam ON paa.ASSIGNMENT_ID = paam.ASSIGNMENT_ID
AND ppa.EFFECTIVE_DATE BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE
JOIN PER_ALL_PEOPLE_F papf ON paam.PERSON_ID = papf.PERSON_ID
AND ppa.EFFECTIVE_DATE BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
JOIN PER_TIME_PERIODS ptp ON ppa.PAYROLL_ID = ptp.PAYROLL_ID
AND ppa.DATE_EARNED BETWEEN ptp.START_DATE AND ptp.END_DATE
LEFT JOIN PER_LEGISLATIVE_DATA_GROUPS_VL pld ON ppa.LEGISLATIVE_DATA_GROUP_ID = pld.LEGISLATIVE_DATA_GROUP_ID
LEFT JOIN HR_ORGANIZATION_UNITS_F_TL org ON paam.ORGANIZATION_ID = org.ORGANIZATION_ID AND org.LANGUAGE = USERENV('LANG')
LEFT JOIN PAY_ALL_PAYROLLS_F pg ON ppa.PAYROLL_ID = pg.PAYROLL_ID
WHERE pml.MESSAGE_LEVEL IN ('E', 'W')
AND pml.CREATION_DATE BETWEEN :p_start_date AND :p_end_date
UNION ALL
SELECT
TO_CHAR(papf.PERSON_NUMBER) || '-' || ptp.PERIOD_NAME AS PayrollRecord,
'Payroll Record Corrected' AS ActivityName,
peef.LAST_UPDATE_DATE AS EventTimestamp,
'Oracle HCM' AS SourceSystem,
peef.LAST_UPDATE_DATE AS LastDataUpdate,
pld.NAME,
org.NAME,
peef.LAST_UPDATED_BY,
NULL,
ptp.PERIOD_NAME,
NULL,
'Y',
pg.PAYROLL_NAME,
NULL
FROM PAY_ELEMENT_ENTRIES_F peef
JOIN PER_ALL_ASSIGNMENTS_M paam ON peef.ASSIGNMENT_ID = paam.ASSIGNMENT_ID
AND peef.EFFECTIVE_START_DATE BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE
JOIN PER_ALL_PEOPLE_F papf ON paam.PERSON_ID = papf.PERSON_ID
AND peef.EFFECTIVE_START_DATE BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
JOIN PAY_PAY_RELATIONSHIPS_DN pprd ON paam.PAYROLL_RELATIONSHIP_ID = pprd.PAYROLL_RELATIONSHIP_ID
JOIN PER_TIME_PERIODS ptp ON pprd.PAYROLL_ID = ptp.PAYROLL_ID
AND peef.EFFECTIVE_START_DATE BETWEEN ptp.START_DATE AND ptp.END_DATE
LEFT JOIN PER_LEGISLATIVE_DATA_GROUPS_VL pld ON pprd.LEGISLATIVE_DATA_GROUP_ID = pld.LEGISLATIVE_DATA_GROUP_ID
LEFT JOIN HR_ORGANIZATION_UNITS_F_TL org ON paam.ORGANIZATION_ID = org.ORGANIZATION_ID AND org.LANGUAGE = USERENV('LANG')
LEFT JOIN PAY_ALL_PAYROLLS_F pg ON pprd.PAYROLL_ID = pg.PAYROLL_ID
WHERE peef.LAST_UPDATE_DATE > peef.CREATION_DATE
AND peef.LAST_UPDATE_DATE BETWEEN :p_start_date AND :p_end_date
UNION ALL
SELECT
TO_CHAR(papf.PERSON_NUMBER) || '-' || ptp.PERIOD_NAME AS PayrollRecord,
'Payroll Run Approved' AS ActivityName,
ppa.LAST_UPDATE_DATE AS EventTimestamp,
'Oracle HCM' AS SourceSystem,
ppa.LAST_UPDATE_DATE AS LastDataUpdate,
pld.NAME,
org.NAME,
ppa.LAST_UPDATED_BY,
NULL,
ptp.PERIOD_NAME,
NULL,
'N',
pg.PAYROLL_NAME,
NULL
FROM PAY_PAYROLL_ACTIONS ppa
JOIN PAY_ASSIGNMENT_ACTIONS paa ON ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
JOIN PER_ALL_ASSIGNMENTS_M paam ON paa.ASSIGNMENT_ID = paam.ASSIGNMENT_ID
AND ppa.EFFECTIVE_DATE BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE
JOIN PER_ALL_PEOPLE_F papf ON paam.PERSON_ID = papf.PERSON_ID
AND ppa.EFFECTIVE_DATE BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
JOIN PER_TIME_PERIODS ptp ON ppa.PAYROLL_ID = ptp.PAYROLL_ID
AND ppa.DATE_EARNED BETWEEN ptp.START_DATE AND ptp.END_DATE
LEFT JOIN PER_LEGISLATIVE_DATA_GROUPS_VL pld ON ppa.LEGISLATIVE_DATA_GROUP_ID = pld.LEGISLATIVE_DATA_GROUP_ID
LEFT JOIN HR_ORGANIZATION_UNITS_F_TL org ON paam.ORGANIZATION_ID = org.ORGANIZATION_ID AND org.LANGUAGE = USERENV('LANG')
LEFT JOIN PAY_ALL_PAYROLLS_F pg ON ppa.PAYROLL_ID = pg.PAYROLL_ID
WHERE ppa.ACTION_TYPE = 'R'
AND paa.ACTION_STATUS = 'C'
AND ppa.EFFECTIVE_DATE BETWEEN :p_start_date AND :p_end_date
UNION ALL
SELECT
TO_CHAR(papf.PERSON_NUMBER) || '-' || ptp.PERIOD_NAME AS PayrollRecord,
'Pay Slip Published' AS ActivityName,
(ppa.CREATION_DATE + 1) AS EventTimestamp,
'Oracle HCM' AS SourceSystem,
ppa.LAST_UPDATE_DATE AS LastDataUpdate,
pld.NAME,
org.NAME,
ppa.LAST_UPDATED_BY,
NULL,
ptp.PERIOD_NAME,
NULL,
'N',
pg.PAYROLL_NAME,
NULL
FROM PAY_PAYROLL_ACTIONS ppa
JOIN PAY_ASSIGNMENT_ACTIONS paa ON ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
JOIN PER_ALL_ASSIGNMENTS_M paam ON paa.ASSIGNMENT_ID = paam.ASSIGNMENT_ID
AND ppa.EFFECTIVE_DATE BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE
JOIN PER_ALL_PEOPLE_F papf ON paam.PERSON_ID = papf.PERSON_ID
AND ppa.EFFECTIVE_DATE BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
JOIN PER_TIME_PERIODS ptp ON ppa.PAYROLL_ID = ptp.PAYROLL_ID
AND ppa.DATE_EARNED BETWEEN ptp.START_DATE AND ptp.END_DATE
LEFT JOIN PER_LEGISLATIVE_DATA_GROUPS_VL pld ON ppa.LEGISLATIVE_DATA_GROUP_ID = pld.LEGISLATIVE_DATA_GROUP_ID
LEFT JOIN HR_ORGANIZATION_UNITS_F_TL org ON paam.ORGANIZATION_ID = org.ORGANIZATION_ID AND org.LANGUAGE = USERENV('LANG')
LEFT JOIN PAY_ALL_PAYROLLS_F pg ON ppa.PAYROLL_ID = pg.PAYROLL_ID
WHERE ppa.ACTION_TYPE = 'X'
AND ppa.EFFECTIVE_DATE BETWEEN :p_start_date AND :p_end_date
UNION ALL
SELECT
TO_CHAR(papf.PERSON_NUMBER) || '-' || ptp.PERIOD_NAME AS PayrollRecord,
'Tax Filing Completed' AS ActivityName,
ppa.CREATION_DATE AS EventTimestamp,
'Oracle HCM' AS SourceSystem,
ppa.LAST_UPDATE_DATE AS LastDataUpdate,
pld.NAME,
org.NAME,
ppa.LAST_UPDATED_BY,
NULL,
ptp.PERIOD_NAME,
NULL,
'N',
pg.PAYROLL_NAME,
NULL
FROM PAY_PAYROLL_ACTIONS ppa
JOIN PAY_ASSIGNMENT_ACTIONS paa ON ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
JOIN PER_ALL_ASSIGNMENTS_M paam ON paa.ASSIGNMENT_ID = paam.ASSIGNMENT_ID
AND ppa.EFFECTIVE_DATE BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE
JOIN PER_ALL_PEOPLE_F papf ON paam.PERSON_ID = papf.PERSON_ID
AND ppa.EFFECTIVE_DATE BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
JOIN PER_TIME_PERIODS ptp ON ppa.PAYROLL_ID = ptp.PAYROLL_ID
AND ppa.DATE_EARNED BETWEEN ptp.START_DATE AND ptp.END_DATE
LEFT JOIN PER_LEGISLATIVE_DATA_GROUPS_VL pld ON ppa.LEGISLATIVE_DATA_GROUP_ID = pld.LEGISLATIVE_DATA_GROUP_ID
LEFT JOIN HR_ORGANIZATION_UNITS_F_TL org ON paam.ORGANIZATION_ID = org.ORGANIZATION_ID AND org.LANGUAGE = USERENV('LANG')
LEFT JOIN PAY_ALL_PAYROLLS_F pg ON ppa.PAYROLL_ID = pg.PAYROLL_ID
WHERE ppa.ACTION_TYPE = 'Z'
AND ppa.CREATION_DATE BETWEEN :p_start_date AND :p_end_date