Stop spending 3 hours on a query that should take 20 minutes. Copy-paste production SQL for PER_ALL_ASSIGNMENTS_M, headcount, compensation, absence, and turnover — with every gotcha documented.
Get the Cookbook →Generic SQL generators don't know about EFFECTIVE_LATEST_CHANGE, _M vs _F tables, or ASSIGNMENT_STATUS_TYPE filters. You get 40 rows per employee.
You can find the column names. You can't find which table to join, in what order, with which filters to avoid blowing up the result set.
Date-effective tables, future-dated rows, multiple assignment types, TL translation joins — each one is a trap unless you've been burned before.
Every query includes the full SQL, required filters, a column reference, performance notes, and common mistakes for that specific pattern.
PER_ALL_ASSIGNMENTS_M with EFFECTIVE_LATEST_CHANGE = 'Y' + ASSIGNMENT_TYPE = 'E' + active status. Zero duplicates, works on 100K+ employee orgs.
Historical snapshot using PER_ALL_ASSIGNMENTS_F date-range filtering. Correct pattern for quarterly/annual HR reports — not the same as current headcount.
Distinguishes genuine new hires from rehires using ORIGINAL_DATE_OF_HIRE vs LAST_HIRE_DATE. Most teams get this wrong and inflate headcount reports.
ACTUAL_TERMINATION_DATE + termination reason from PER_PERIODS_OF_SERVICE. Includes voluntary vs. involuntary classification and last assigned cost center.
Annualized turnover with beginning headcount denominator. Correct formula that matches what your CFO expects — not just count of terminations.
Self-join on MANAGER_ID with NULL handling. Supports orgs where managers have multiple assignment types at each level.
Full date-effective change log per employee: what changed, when, and prior value. Required for compliance and SOX audit responses.
CMP_SALARY join with assignment and grade tables. Handles currency conversion flag and salary range midpoint for compa-ratio calculation.
Date-effective salary history with previous salary, change amount, and change reason. Covers merit, promotion, and market adjustment events.
Average, median, min/max salary by job code and gender. Pay equity report-ready. Uses PERCENTILE_CONT for true median in Oracle SQL.
Vacation, sick, and PTO balances from ANC_PER_ACCRUAL_PLANS. Correct table — not the view. Handles absence plan version changes.
ANC_PER_ABSENCE_ENTRIES with absence type, start/end dates, and approved/pending status. Includes calendar days vs. working days distinction.
IRC_REQUISITIONS with status filter for open roles. Includes requisition age, hiring manager, and department cost center for budget tracking.
IRC_SUBMISSIONS with phase/state mapping. Shows funnel from Applied → Screened → Interview → Offer → Hired for each requisition.
Days from requisition open to offer accepted. Uses TRUNC(SYSDATE) anchor date, handles requisitions with multiple offers.
PAY_RUN_RESULTS + PAY_RUN_RESULT_VALUES for a given payroll period. Correct join to avoid exploding row counts on multi-element assignments.
Federal, state, and local withholding from PAY_RUN_RESULTS filtered by element classification. Year-to-date accumulation pattern included.
BEN_PRTT_ENRT_RSLT_F with plan and option joins. Shows medical, dental, vision, and 401k elections for open enrollment audits.
HRA_OBJECTIVES for performance goals. Completion percentage, due dates, and manager-assigned vs. self-assigned goal breakdown.
HRA_EVALUATIONS with rating scale mapping. Shows distribution by department and level — the query your CHRO asks for every review cycle.
WLF_LEARNER_ATTEMPTS with completion status. Compliance training audit — shows who completed, who's overdue, and completion date for each mandatory course.
Headcount by gender, ethnicity (EEO categories), age band, and disability status. EEO-1 report prep. Handles null demographic values correctly.
Manager headcount with organizational level depth. Identifies managers with 1 direct report and managers with 20+ for org design analysis.
Employees by work location including remote/hybrid flag. Uses PER_LOCATIONS join with HR_LOCATIONS_V for address details.
Pending actions from HRA_DOCUMENT_CATEGORIES and approval workflow tables. Shows what's stuck in approval and who needs to act.
Before/after comparison using LAST_UPDATE_DATE filter. Quickly confirms which rows were touched by a specific HDL batch load.
PER_ALL_PEOPLE_F ATTRIBUTE columns with FND_DESCRIPTIVE_FLEXS lookup for meaningful names. Common ask: "show me the custom field we added in implementation."
FND_LOOKUP_VALUES with language filter. Returns all valid values for any HCM lookup type — essential when building data validation reports.
ASC_ROLE_ASSIGNMENTS + FND_USERS + FND_MENUS. Shows who has what Oracle HCM security role — standard ask during SOX or access reviews.
Eight data quality checks in one query: employees without jobs, assignments without cost centers, missing termination dates, future-dated rows, and more. Run before every audit.
Every query is fully commented, with filters explained and gotchas called out in-line.
-- Headcount: one row per active employee, guaranteed. -- _M table + EFFECTIVE_LATEST_CHANGE handles future-dated rows automatically. SELECT p.full_name, a.assignment_number, a.department_id, a.location_id, a.job_id, a.grade_id FROM per_all_assignments_m a JOIN per_all_people_f p ON p.person_id = a.person_id AND p.effective_start_date <= SYSDATE AND p.effective_end_date >= SYSDATE WHERE a.effective_latest_change = 'Y' -- one row per assignment AND a.assignment_type = 'E' -- employees only (not CWK) AND a.assignment_status_type = 'ACTIVE_ASSIGN' AND a.primary_flag = 'Y' -- primary assignment only ORDER BY p.full_name;
Not just the SQL — the context that makes it work in your environment.
30 production SQL files — one per query, named and organized by module
Required filters documented — for every query, which columns you must include to avoid garbage results
Column reference tables — the 10–15 most useful columns per query, with data types and notes
Gotchas documented — the traps that cost you hours the first time you hit them
_M vs _F decision guide — which table to use and when, with date-effective pattern comparisons
Performance notes — which queries need hints, which indexes to create, what blows up on large orgs
OTBI Logical SQL equivalents — for 10 key queries, the OTBI version that runs in Reports and Analytics
Lifetime updates — new queries added as Oracle releases new tables and schema changes (24B, 25A, etc.)
Oracle HCM consultants bill $150–$350/hr. These queries pay for themselves in the first use.
All queries target Oracle Fusion Cloud HCM 23D and later. Where a query differs between 23D and earlier releases, both versions are included. The PER_ALL_ASSIGNMENTS_M queries specifically cover the 24A/24B schema changes.
Yes — these are direct SQL queries against the Oracle HCM schema. You need either SQL Developer or access to Reports and Analytics (BIP/OTBI) with the appropriate HCM security roles. Ask your Oracle DBA for read access to the HCM schema if you don't have it.
10 of the 30 queries include the OTBI Logical SQL equivalent. The remaining 20 are direct SQL that run via BI Publisher or SQL Developer — OTBI has limitations that prevent some complex joins and window functions.
Oracle HCM schemas vary by implementation, and some tables may have been customized or renamed. If a query doesn't run as-is, email me the error and I'll help you adapt it — that's covered by the 30-day guarantee.
You bring 1–2 specific reporting problems or queries you're stuck on. We walk through them together, I help adapt the cookbook patterns to your specific setup, and the session is recorded so you can reference it later.
The OTBI Template Pack ($97) focuses on 15 OTBI report patterns with Logical SQL syntax. The HCM Query Cookbook ($197) is broader — 30 queries, SQL + OTBI coverage, and every major HCM module including payroll, benefits, and performance. They're complementary, not duplicates.
30 queries that work on the first run. The alternative is another 3-hour debug session at 10pm before a stakeholder presentation.
Get the Cookbook →