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
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.