Blitz Report™ Library

This library is a free open source SQL knowledge base for Oracle E-Business Suite customers and IT professionals. It contains SQLs that we created (category: Enginatics) and SQLs imported from Oracle’s BI Publisher reports (category: BI Publisher).

If you have Bitz Report installed, you can download a single or multiple reports as XML file from this library and automatically load them into your environment, including parameters, LOVs and category assignments.

If you would like to try one of these SQLs without having Blitz Report installed, as SQLs are composed dynamically at runtime, it is easiest to copy the query from the second tab of the example Excel sheet instead from the report’s detail page. Note that some of the reports require functions from utility package xxen_util.

To facilitate development work and help learning the EBS table structure, there are hyperlinks on each table to navigate directly to the corresponding Oracle ETRM record.

We also encourage you to contribute by adding your own SQL reports or suggesting enhancements and corrections to existing SQLs.
To add new reports, simply log in to our online demo environment, create a Blitz Report and it will automatically show up in this library (creation of a Blitz Report is shown here).

22 reports

DescriptionDownload
PER Employee Assignments
Master data report showing the employee demographic information including name, gender SSN, birthdate, age, nationality, effective start date, and additional profile information. Detail Level Costing adds one row per assignment cost allocation valid at the Effective Date, with the proportion, the total proportion of the assignment and the cost allocation flexfield. The individual cost segment columns are shown when a Business Group is selected. Assignments without assignment-level costing show blank costing columns. Detail Level Extra Information lists one row per assignment extra…
full description
Human Resources Enginatics XLS XML
PER Employee Grade Changes
List of employees with their grade changes.
Human Resources Enginatics XLS XML
PER Information Type Security Human Resources Enginatics XLS XML
PER Jobs and Positions
Positions valid at the Effective Date with their job, organization, status, valid grades, FTE, maximum persons, headcount and incumbent FTE, plus the jobs that have no position valid at the Effective Date, for enterprises that use jobs only. Headcount counts the active and suspended employee and contingent worker assignments of the position, or for a job without positions, the assignments of the job without a position. Incumbent FTE sums their FTE assignment budget values. Vacancies is the maximum persons of the position, one if blank, minus its headcount. Detail Level Incumbents adds one…
full description
Human Resources Enginatics XLS XML
PER Organization Hierarchy
Master data report showing hierarchical list of human resource organization structures including subordinate orgs.
Human Resources Enginatics XLS XML
PER Organizations
Master data report showing organizations, classifications, address details and additional information attributes.
Human Resources Enginatics XLS XML
PER Position Hierarchy
Position hierarchy showing the reporting structure of positions with hierarchy level, organization, job, grade, location, budgeted vs actual headcount, vacancy count, and current incumbent names. Useful for position control, staffing analysis, and org chart construction.
Human Resources BI Publisher, Enginatics XLS XML
PER Security Profiles
Security profiles and operating units that they give access to.
Human Resources Enginatics, R12 only XLS XML
PER Employee Absences
Employee absence and attendance records showing absence type, category, dates, duration in days and hours, reason, authorizing person, replacement person, and approval status. Useful for leave management, absence tracking, and compliance reporting.
Human Resources Enginatics XML
PER Employee Contacts and Dependents
Employee contact relationships including dependents, beneficiaries, and emergency contacts. Shows contact person details (name, phone, email, address), relationship type, and flags for dependent, beneficiary, emergency contact, and third party payment. Useful for benefits administration, emergency preparedness, and employee records management.
Human Resources Enginatics XML
PER Employee Length of Service
Active employees with calculated length of service in years and months, based on the adjusted service date (or hire date if not set). Shows milestone years (multiples of 5), next service anniversary, age, and current assignment details. Useful for recognition programs, benefits eligibility, and workforce tenure analysis.
Human Resources Enginatics XML
PER Employee Salary Change
Query to get the last Salary change details for employees. Can add: whether to look at just the recent salary change? or salary change history.... If yes, then the last condition in where clause to be dynamically added/removed
Human Resources Enginatics XML
PER Headcount by Organization
Active employee and contingent worker headcount summarized by business group, organization, location, job, grade, and employment category. Includes gender breakdown and average service years and age. Use Excel pivot tables for flexible grouping and analysis.
Human Resources Enginatics XML
PER Location Upload
Upload to create, update, and delete HR Locations. Supports all country-specific address styles with address field validation. Includes site flags (Ship-To, Bill-To, Receiving, Office, Internal) with autocorrection for Ship-To and Receiving flags based on the Ship-To Location relationship. Includes the Descriptive Flexfield attributes with validation. Business group security restricts downloaded locations to the user's current business group plus global locations. New locations can optionally be made local by assigning the location to the user's current business group. If not assigned to…
full description
Human Resources Enginatics, Upload XML
PER New Hires
Employees hired within a specified date range, showing hire date, organization, job, position, grade, location, supervisor, employment category, and probation details. Useful for onboarding tracking and new hire analysis.
Human Resources Enginatics XML
PER Organization Upload
Upload to create and update organizations, organization classifications, and organization information types within Oracle HRMS. Scope: - Create new organizations, assign classifications and information types, and specify the information type attributes - Update existing organization data, classifications (enable/disable), and the information type attributes - Supports all organization classification types: Business Groups (excluding creation of new Business Groups), HR Organization, Operating Unit, GRE/Legal Entity, Company Cost Center, Inventory Organization, etc. - Supports creation/update…
full description
Human Resources Enginatics, Upload XML
PER Payroll Element Entry Upload
PER Payroll Element Entry Upload ================== Use this upload to upload new or update existing PER Payroll Element Entries, or to enter adjustment entries.
Human Resources Enginatics, Upload XML
PER Person Assignment Upload
Upload to create and update employee assignments as in the Assignment window of the People form: organization, job, position, grade, payroll, location, status, categories, supervisor, probation and notice periods, standard conditions, statutory information, salary basis, people group and the Additional Assignment Details flexfield. Changes are made as of the Effective Date. When Datetrack Mode is blank, the assignment is corrected if the Effective Date is its start date, and otherwise a change is inserted from the Effective Date (Update, or Update - Insert when later changes exist). A new…
full description
Human Resources Enginatics, R12 only, Upload XML
PER Person Extra Information Upload
Upload to create and update Person Extra Information Types (EITs) within Oracle HRMS - the People form > Others > Extra Information > Extra Person Information window. Scope: - Generic upload covering all person extra information types. The Information Type parameter/column lists every enabled context of the Extra Person Info DDF flexfield (titled Extra Person Information), which holds the registered EIT types. - The segment values are loaded as raw Information 1..30 columns (pei_information1..30), matching the order of the information type's flexfield segments. - Create new person extra…
full description
Human Resources Enginatics, Upload XML
PER Person Extra Information Upload 11i
Upload to create and update Person Extra Information Types (EITs) within Oracle HRMS - the People form > Others > Extra Information > Extra Person Information window. Scope: - Generic upload covering all person extra information types. The Information Type parameter/column lists every enabled context of the Extra Person Info DDF flexfield (titled Extra Person Information), which holds the registered EIT types. - The segment values are loaded as raw Information 1..30 columns (pei_information1..30), matching the order of the information type's flexfield segments. - Create new person extra…
full description
Human Resources Enginatics, Upload XML
PER Person Upload
Upload to create and update people as in the People form (Combined Person and Assignment form, People window): employees, contingent workers and applicants, their personal details, Further Person Information and Additional Personal Details flexfields. A new person is created with hr_employee_api, hr_contingent_worker_api or hr_applicant_api depending on the system type of the Person Type. The Latest Start Date is the hire, placement or application date, and the API creates the default primary assignment in the business group. Use the PER Person Assignment Upload for the assignment…
full description
Human Resources Enginatics, R12 only, Upload XML
PER Terminations and Separations
Terminated employees within a specified date range, showing termination date, leaving reason, length of service, last organization, job, position, grade, and supervisor. Useful for turnover analysis and exit tracking.
Human Resources Enginatics XML