Introduction/ Issue:
Developing a custom BI Publisher (BIP) report in Oracle Fusion Supply Planning to provide visibility into component dependent demand based on the Finished Goods Forecast instead of the Finished Goods planned orders
Why we need to do / Cause of the issue:
In Oracle Fusion Supply Planning, component dependent demand is displayed based on Finished Goods planned orders. However, business users also require visibility into component demand based on the Finished Goods Forecast to understand future material requirements at the forecast stage. A custom BI Publisher (BIP) report was developed to present component demand derived from the Finished Goods Forecast, enabling planners to analyse forecast-driven component requirements, improve material visibility, and support better procurement and production planning decisions
How do we solve:
- Login to Oracle Fusion and Navigate to Reports and Analytics under Tools
- Click on Browse Catalog. This will open analytics page. Now click on Create and select Data Model
- Click on plus icon and select SQL Query
- Select the Data source as ApplicationDB_FSCM and Use the following query
SELECT DISTINCT
msi.item_name AS “Assembly Item”,
msi.description AS “Assembly Item Desc”,
msi.organization_code AS “Assembly Item Org”,
msi1.item_name AS “Component Item”,
msi1.description AS “Component Item Desc”,
msi1.organization_code AS “Component Org”,
msi1.life_cycle_phase AS “Component Status”,
bom.component_quantity AS “Component Usage Quantity”,
msi.life_cycle_phase AS “Assembly Status”,
d.total_demand AS “FG Demand”,
ROUND(d.total_demand * bom.component_quantity, 0) AS “Component Demand”,
TO_CHAR(
TO_TIMESTAMP_TZ(t.from_date, ‘YYYY-MM-DD”T”HH24:MI:SS.FF TZH:TZM’),
‘DD-MM-YYYY’
) AS “Demand Date”
FROM (
SELECT
mb.assembly_item_id,
mbc.inventory_item_id AS component_item_id,
mbc.usage_quantity AS component_quantity,
mb.organization_id AS org_id
FROM
msc_item_structures mb
JOIN msc_bom_components mbc
ON mb.bill_sequence_id = mbc.bill_sequence_id
AND mb.assembly_item_id = mbc.using_assembly_id
AND mb.assembly_type = 1
AND mb.organization_id = mbc.organization_id
AND (TRUNC(mbc.disable_date) > TRUNC(SYSDATE) OR mbc.disable_date IS NULL)
AND TRUNC(mbc.effectivity_date) <= TRUNC(SYSDATE)
AND mbc.effectivity_date IS NOT NULL
AND mbc.plan_id = -1
AND mb.primary_flag = 1
) bom
JOIN fusion.msc_system_items msi
ON bom.assembly_item_id = msi.inventory_item_id
AND bom.org_id = msi.organization_id
AND msi.plan_id = -1
JOIN fusion.msc_system_items msi1
ON bom.component_item_id = msi1.inventory_item_id
AND bom.org_id = msi1.organization_id
AND msi1.plan_id = -1
JOIN fusion.msc_data_1_dyd d
ON d.PRD_LVL_MEMBER_ID = msi.inventory_item_id
JOIN fusion.msc_dimension_tim_dyd t
ON d.tim_lvl_member_id = t.tim_lvl_member_id
AND d.ORG_LVL_MEMBER_ID = msi.organization_id
WHERE 1=1
AND NVL(d.total_demand, 0) > 0
ORDER BY
“Assembly Item”,
“Component Item”,
“Demand Date”
- Click on Data tab and view the data
- Save the data model and Create the Report as below
Conclusion:
The custom BI Publisher (BIP) report in Oracle Fusion Supply Planning successfully provides visibility into component dependent demand based on the Finished Goods Forecast, enabling planners to evaluate future material requirements before planned orders are generated. By presenting forecast-driven component demand, the report improves component visibility, supports proactive procurement and production planning, enhances material readiness analysis, and helps business users make better planning decisions aligned with forecast expectations
