Oracle Cloud Payroll Costing: SQL Queries to Identify Missing Assignment and Department Costing
Introduction
Payroll costing is one of the most critical configuration areas in Oracle Cloud Payroll. If costing is missing or incomplete, payroll may still calculate successfully, but downstream accounting, costing, and General Ledger transfer can become challenging.
In real implementations, payroll and finance teams often ask questions such as:
- Which employees do not have assignment-level costing?
- Which departments do not have department-level costing?
- Which employees are assigned to departments where costing has not been configured?
These are simple questions from a business perspective, but they are not always easy to answer from the UI, especially when the population is large.
This blog shares three useful SQL queries that can help implementation and support teams identify gaps in payroll costing setup.
Why This Matters
Missing costing setup can cause issues such as:
- Payroll costs going to suspense accounts
- Incorrect cost center allocation
- Payroll costing transfer failures
- Reconciliation issues between Payroll and GL
- Manual cleanup during payroll close
- Delays in payroll accounting validation
During implementation, parallel payroll, or post-production support, these queries can be very helpful for proactive validation.
Query 1: List Employees Without Assignment Costing
Business Requirement
The first requirement is to identify active employees who do not have assignment-level costing configured.
Assignment costing is often used when payroll costs should be charged directly based on the employee assignment rather than defaulting from department, position, organization, or other costing hierarchy levels.
SQL Query
SELECT
papf.person_number,
paam.assignment_number,
paam.assignment_name,
cost_acc.*
FROM
(
SELECT
TO_CHAR(pcaf.effective_start_date, 'YYYY/MM/DD') pcaf_effective_start_date,
TO_CHAR(pcaf.effective_end_date, 'YYYY/MM/DD') pcaf_effective_end_date,
pcaf.payroll_relationship_id,
TO_CHAR(pcaa.cost_alloc_account_id) cost_alloc_account_id,
TO_CHAR(pcaa.cost_allocation_record_id) cost_allocation_record_id,
pcaa.id_flex_num,
TO_CHAR(pcaa.cost_allocation_keyflex_id) cost_allocation_keyflex_id,
pcaa.proportion,
pcaa.source_sub_type,
pcaa.segment1,
pcaa.segment2 seg2_location,
pcaa.segment3 seg3_division,
pcaa.segment4,
pcaa.segment5,
pcaa.segment6,
pcaa.segment7,
pcaa.segment8,
pcaa.segment9,
pcaa.segment10,
pcaa.created_by,
pcaa.creation_date,
pcaa.last_update_date,
pcaa.last_update_login,
pcaa.last_updated_by
FROM
pay_cost_allocations_f pcaf,
pay_cost_alloc_accounts pcaa
WHERE
pcaf.cost_allocation_record_id(+) = pcaa.cost_allocation_record_id
AND pcaf.source_type(+) = 'ASG'
AND pcaf.payroll_relationship_id IN
(
SELECT payroll_relationship_id
FROM pay_pay_relationships_dn
WHERE person_id IN
(
SELECT DISTINCT person_id
FROM per_all_people_f
)
)
) cost_acc,
per_all_assignments_m paam,
per_all_people_f papf,
pay_pay_relationships_dn pprd
WHERE
cost_acc.payroll_relationship_id(+) = pprd.payroll_relationship_id
AND papf.person_id = paam.person_id
AND papf.person_id = pprd.person_id
AND paam.assignment_type NOT LIKE '%Termi%'
AND paam.assignment_type = 'E'
AND paam.assignment_status_type = 'ACTIVE'
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date;
How to Use the Output
This query returns active employee assignments along with assignment costing details if they exist.
To identify employees missing assignment costing, review records where costing segments or costing account information are blank.
Depending on your reporting requirement, you may further add a filter such as:
AND cost_acc.cost_allocation_record_id IS NULL
or check a specific costing segment, for example:
AND cost_acc.segment4 IS NULL
Practical Use Case
This query is useful during:
- Payroll implementation validation
- Pre-parallel payroll checks
- Assignment costing cleanup
- Payroll costing troubleshooting
- Post-go-live support (every pre-payroll validation report before every payroll run)
Query 2: List Departments Without Costing Information
Business Requirement
The second requirement is to identify departments where costing has not been configured.
Department costing is commonly used as a default costing level. If employee assignment costing is not available, payroll may derive costing from the department or organization level depending on the costing hierarchy.
SQL Query
SELECT
hov.name department_name,
cost_acc.*
FROM
(
SELECT
TO_CHAR(pcaf.effective_start_date, 'YYYY/MM/DD') costing_effective_start_date,
TO_CHAR(pcaf.effective_end_date, 'YYYY/MM/DD') costing_effective_end_date,
pcaf.source_id,
TO_CHAR(pcaa.cost_alloc_account_id) cost_alloc_account_id,
TO_CHAR(pcaa.cost_allocation_record_id) cost_allocation_record_id,
pcaa.id_flex_num,
TO_CHAR(pcaa.cost_allocation_keyflex_id) cost_allocation_keyflex_id,
pcaa.proportion,
pcaa.source_sub_type,
pcaa.segment1,
pcaa.segment2 seg2_location,
pcaa.segment3 seg3_division,
pcaa.segment4 seg4_cost_center,
pcaa.segment5,
pcaa.segment6,
pcaa.segment7,
pcaa.segment8,
pcaa.segment9,
pcaa.segment10,
pcaa.created_by,
pcaa.creation_date,
pcaa.last_update_date,
pcaa.last_update_login,
pcaa.last_updated_by
FROM
pay_cost_alloc_accounts pcaa,
pay_cost_allocations_f pcaf
WHERE
pcaa.source_sub_type = 'COST'
AND pcaf.source_type(+) = 'ORG'
AND pcaa.cost_allocation_record_id = pcaf.cost_allocation_record_id(+)
) cost_acc,
hr_organization_v hov
WHERE
cost_acc.source_id(+) = hov.organization_id
AND hov.classification_code = 'DEPARTMENT'
AND TRUNC(SYSDATE) BETWEEN hov.effective_start_date AND hov.effective_end_date;
How to Use the Output
This query lists departments and any associated organization-level costing details.
To identify only departments missing costing, you can add a filter such as:
AND cost_acc.cost_allocation_record_id IS NULL
or if Cost Center is stored in Segment 4:
AND cost_acc.seg4_cost_center IS NULL
Practical Use Case
This query is useful when Finance or Payroll wants to validate whether every active department has costing configured before payroll costing is transferred to GL.
Query 3: List Employees Assigned to Departments Without Costing
Business Requirement
The third requirement combines employee assignment data with department costing data.
This is especially useful because a department may be missing costing, but the real operational impact depends on whether active employees are assigned to that department.
SQL Query
SELECT
papf.person_number,
paam.assignment_number,
pd.name department_name,
cost_acc.*
FROM
per_all_people_f papf,
per_all_assignments_m paam,
per_periods_of_service ppos,
per_departments pd,
(
SELECT
TO_CHAR(pcaf.effective_start_date, 'YYYY/MM/DD') pcaf_effective_start_date,
TO_CHAR(pcaf.effective_end_date, 'YYYY/MM/DD') pcaf_effective_end_date,
pcaf.source_id,
pcaf.source_type,
pcaf.payroll_relationship_id,
TO_CHAR(pcaa.cost_alloc_account_id) cost_alloc_account_id,
TO_CHAR(pcaa.cost_allocation_record_id) cost_allocation_record_id,
pcaa.id_flex_num,
TO_CHAR(pcaa.cost_allocation_keyflex_id) cost_allocation_keyflex_id,
pcaa.proportion,
pcaa.source_sub_type,
pcaa.segment1,
pcaa.segment2 seg2_location,
pcaa.segment3 seg3_division,
pcaa.segment4 seg4_cost_center,
pcaa.segment5,
pcaa.segment6,
pcaa.segment7,
pcaa.segment8,
pcaa.segment9,
pcaa.segment10,
pcaa.created_by,
pcaa.creation_date,
pcaa.last_update_date,
pcaa.last_update_login,
pcaa.last_updated_by
FROM
pay_cost_alloc_accounts pcaa,
pay_cost_allocations_f pcaf
WHERE
pcaa.source_sub_type = 'COST'
AND pcaf.source_type(+) = 'ORG'
AND pcaa.cost_allocation_record_id = pcaf.cost_allocation_record_id(+)
) cost_acc
WHERE
papf.person_id = paam.person_id
AND ppos.period_of_service_id = paam.period_of_service_id
AND paam.organization_id = pd.organization_id(+)
AND cost_acc.source_id(+) = pd.organization_id
AND paam.assignment_type NOT LIKE '%Term%'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pd.effective_start_date(+) AND pd.effective_end_date(+)
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.primary_flag = 'Y'
AND paam.assignment_type NOT LIKE '%Term%'
AND paam.assignment_status_type = 'ACTIVE'
AND paam.assignment_type = 'E'
AND ppos.date_start =
(
SELECT MAX(date_start)
FROM per_periods_of_service
WHERE person_id = paam.person_id
AND period_type = paam.assignment_type
)
AND
(
pd.name IS NULL
OR cost_acc.seg4_cost_center IS NULL
);
How to Use the Output
This query identifies active employees where:
- No department is assigned
- The assigned department does not have costing information
This is one of the most useful payroll costing audit queries because it directly shows impacted employees.
Practical Use Case
Use this query before:
- Payroll costing process
- Transfer to Subledger Accounting
- Transfer to General Ledger
- Parallel payroll validation
- Department costing conversion signoff
Important Notes
1. Segment Names Are Client Specific
In the sample queries:
SEGMENT2 = Location SEGMENT3 = Division SEGMENT4 = Cost Center
However, your client’s costing key flexfield may be different.
Always confirm the costing segment structure before using the query in a production environment.
2. Costing Hierarchy Matters
Oracle Payroll costing can be derived from different levels, such as:
- Element entry costing
- Assignment costing
- Department costing
- Position costing
- Organization costing
- Element eligibility costing
- Payroll relationship costing
These queries focus mainly on assignment and department-level costing.
If your client relies on other costing levels, additional queries may be required.
3. Date Effectivity Is Critical
Payroll costing is date-effective.
Always validate costing as of the correct date:
TRUNC(SYSDATE)
This may be fine for current-state validation, but for payroll processing you may need to replace it with:
:PAYROLL_PERIOD_END_DATE
or another parameterized effective date.
4. Outer Join Usage
These queries intentionally use outer joins to identify missing costing records.
That is why you see syntax such as:
cost_acc.source_id(+) = pd.organization_id
This allows departments or assignments to appear even when costing is missing.
Recommended Enhancements
For production reporting, I recommend enhancing these queries with parameters such as:
- Effective date
- Legislative Data Group
- Payroll name
- Legal employer
- Department name
- Person number
- Assignment number
- Cost center
- Business unit
This makes the report more flexible for payroll and finance users.
When to Run These Queries
These queries are useful during:
- Payroll implementation
- Data conversion validation
- Parallel payroll
- Payroll costing testing
- Pre-go-live readiness checks
- Post-production audits
- Payroll close activities
- Post-go-live support (every pre-payroll validation report before every payroll run)
Final Thoughts
Payroll costing is often treated as a setup activity, but in practice it needs continuous validation.
Missing assignment or department costing can create downstream payroll accounting issues that are harder to fix after payroll has been processed.
These three SQL queries provide a practical way to identify:
- Employees without assignment costing
- Departments without costing
- Employees assigned to departments without costing
For Oracle Cloud Payroll implementation teams, these reports can become part of a standard payroll costing readiness checklist before each major payroll milestone.
Used proactively, they can reduce payroll accounting errors, improve reconciliation, and help payroll and finance teams close payroll with more confidence.
