BOM Department Upload – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Creates and updates departments with their resources and resource shifts - the Departments form (Bills of Material > Routings > Departments).

Each row holds one department, optionally one of its resources and one shift of that resource. The department and resource columns repeat on every row, and a change in any of these rows is applied. Departments cannot be deleted; enter an Inactive On date instead.

A resource with an Owning Department is borrowed from that department: its availability, units, Check CTP, ATP rule, utilization and efficiency are copied from the owning department’s resource, which must have Share set to Yes. Changing Available 24 Hours of a shared resource updates the departments borrowing it, as the form does.

A resource that is not available 24 hours needs at least one shift. Deleting its last shift makes it available 24 hours. To remove a resource from a department, delete its shifts first, or enter Yes in Delete Shift and Delete Resource on the same row.

Utilization % and Efficiency % are entered as percentages. Cost Category is available in eAM organizations only, Project Expenditure Org in organizations with project cost collection, and the scrap accounts in organizations with network manufacturing (WSM).

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Upload Mode, Organization Code, Department, Department Class, Resource, Active only

Oracle EBS Tables Used

bom_departments, bom_department_resources, bom_calendar_shifts, bom_resource_shifts, mtl_parameters, bom_department_classes, hr_locations_all, hr_all_organization_units_vl, gl_code_combinations_kfv, bom_resources, mtl_atp_rules, org_access_view

Report Categories

Enginatics, R12 only, Upload

CAC WIP Resource Efficiency, GL Account Distribution Analysis, GL Account Analysis, CAC WIP Account Detail, CAC ICP PII WIP Material Usage Variance, CAC WIP Account Summary, WIP Value Summary, GL Account Analysis (Drilldown) (with inventory and WIP), CAC Manufacturing Variance

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output Example
Blitz Report™ XML Import BOM_Department_Upload.xml
SQL Source BOM Department Upload.sql
Full Report on Enginatics www.enginatics.com/reports/bom-department-upload/

© Enginatics GmbH