Stop reverse-engineering Oracle's data model every time you build a report. Copy-paste SQL that actually works — with EFFECTIVE_LATEST_CHANGE, _TL joins, and assignment type filters already handled.
Get the Template Pack →Date-effective history multiplies your results unless you know the exact deduplication filters.
Standard SQL generators write queries that fail in Oracle's logical SQL layer — OTBI has its own syntax rules.
Oracle's OTBI documentation tells you what's available. It doesn't tell you what the gotchas are.
Each template includes the query, the required filters, a table of relevant columns, and notes on common mistakes for that pattern.
Current headcount with department, location, and grade. Uses EFFECTIVE_LATEST_CHANGE + ASSIGNMENT_TYPE = 'E' + ASSIGNMENT_STATUS_TYPE = 'ACTIVE_ASSIGN'. One row per active employee guaranteed.
Historical headcount snapshot as of any date using date-range filtering on PER_ALL_ASSIGNMENTS_F. Correct pattern for quarterly or year-end reporting.
Self-join on MANAGER_ID to build a 3-level reporting hierarchy. Handles NULL managers and multiple assignment types at each level.
Identifies genuine new hires vs. rehires vs. internal transfers. Filters on ORIGINAL_DATE_OF_HIRE vs LAST_HIRE_DATE. Common mistake: using EFFECTIVE_START_DATE on the assignment table instead.
Employees terminated in a date range with ACTUAL_TERMINATION_DATE, termination reason code, and final department. Joins PER_PERIODS_OF_SERVICE for accurate dates.
Full date-effective assignment change history per employee. Shows what changed, when, and what the previous value was. Useful for audit trails and compliance reports.
Current base salary, salary grade, and compa-ratio. Joins CMP_SALARY (or PER_PAY_PROPOSALS for older schemas) with assignment and grade tables. Handles currency conversion flag.
Current absence plan balances with plan name, accrued, taken, and remaining days. Joins ANC_PER_ABSENCE_ENTRIES_F and ANC_PER_ACCRUAL_BALANCES. Requires language filter on plan name TL table.
Open requisitions with hiring manager, department, target hire date, and candidate counts. Joins IRC_REQUISITIONS_B with IRC_SUBMISSIONS to count active candidates per requisition.
Recruiting funnel: offers extended vs. offers accepted vs. offers declined, by department and time period. OTBI logical SQL using Recruiting - Real Time subject area with correct date grain.
Employee work email, personal email, and phone. Joins PER_EMAIL_ADDRESSES and PER_PHONES with correct EMAIL_TYPE and PHONE_TYPE filters (W1=work primary, H1=home primary, M=mobile).
Resolves GRADE_ID, JOB_ID, and POSITION_ID to their display names via the corresponding _F and _TL tables. Includes language filter pattern and effective date handling.
Identifies which Fast Formula is assigned to which element link, the formula text, and last-modified date. Essential for payroll troubleshooting — avoids navigating through UI breadcrumbs.
Payroll run results by element for a pay period: base pay, overtime, deductions, and employer contributions. Joins PAY_RUN_RESULTS to PAY_ELEMENT_TYPES_F with correct assignment action filter.
The correct way to write subqueries in OTBI logical SQL (Oracle BI EE dialect). Standard SQL subqueries fail — this shows the FILTER() function and correlated measure pattern that actually works in OTBI.
Every template includes the query, required filters, a gotchas section, and the relevant table reference.
-- TEMPLATE 01: Active Employee Headcount -- Returns one row per active employee -- Required filters: EFFECTIVE_LATEST_CHANGE, ASSIGNMENT_TYPE, ASSIGNMENT_STATUS_TYPE -- Gotcha: Missing EFFECTIVE_LATEST_CHANGE = 'Y' returns all historical rows SELECT p.PERSON_NUMBER, p.FULL_NAME, a.ASSIGNMENT_NUMBER, a.GRADE_ID, a.DEPARTMENT_ID, a.JOB_ID, a.LOCATION_ID, a.MANAGER_ID, a.EFFECTIVE_START_DATE FROM PER_ALL_PEOPLE_F p JOIN PER_ALL_ASSIGNMENTS_M a ON p.PERSON_ID = a.PERSON_ID WHERE TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE AND a.EFFECTIVE_LATEST_CHANGE = 'Y' AND a.ASSIGNMENT_TYPE = 'E' AND a.ASSIGNMENT_STATUS_TYPE = 'ACTIVE_ASSIGN';
15 SQL/OTBI templates covering all major HCM modules
Required filter notes for every query — no guessing which columns prevent duplicates
Common mistakes section per template — the errors that waste hours the first time
OTBI subject area mapping — which subject area each pattern belongs to
Table reference links — jump directly to each table's full column list
Updated for Oracle 24B — uses _M tables and EFFECTIVE_LATEST_CHANGE where appropriate
The 5 most critical OTBI filter patterns — the exact clauses that prevent duplicate rows, handle date-effective tables, and avoid the most common OTBI errors. Free, instant download.
Templates are written against Oracle Fusion HCM Release 13 (the current cloud version) and tested through Oracle 24B. The headcount and assignment templates specifically use the 24B-recommended PER_ALL_ASSIGNMENTS_M pattern.
Both. Each template is labeled SQL (for direct database or BIP queries) or OTBI (logical SQL using subject areas). The OTBI templates use Oracle BI EE dialect — not standard SQL — and include the FILTER() function pattern that OTBI requires for subqueries.
For the SQL templates, yes — you need BI Publisher, a custom report with SQL data source, or direct database access. For the OTBI templates, you only need an Oracle Analytics account and access to the relevant subject areas.
A single PDF document with all 15 templates, plus a .sql file with the raw queries for easy copy-paste. No account or app required.
Email us with your Oracle version and the error you're seeing. We'll help you adapt it. 30-day money-back guarantee if we can't get it working for your use case.