Monday, April 24, 2023

Query to List Items, Structures and Components In Oracle Fusion


Please find the query to retrieve structure and components for an item:

SELECT
item.item_number Struct_Item,
item.ORGANIZATION_ID,
structb.ALTERNATE_BOM_DESIGNATOR,
compb.item_num CompSEQ,
complist.item_number Components,
compb.EFFECTIVITY_DATE
FROM
FUSION.EGP_SYSTEM_ITEMS_B item
JOIN
FUSION.EGP_STRUCTURES_B structb
ON item.INVENTORY_ITEM_ID = structb.PK1_VALUE
JOIN
FUSION.EGP_COMPONENTS_B compb
ON structb.COMMON_BILL_SEQUENCE_ID=compb.BILL_SEQUENCE_ID
JOIN
FUSION.EGP_SYSTEM_ITEMS_B complist
ON compb.PK1_VALUE=complist.INVENTORY_ITEM_ID
and complist.ORGANIZATION_ID = item.ORGANIZATION_ID
WHERE
item.item_number='Toy Duck' -- Parent Item

No comments:

Post a Comment

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...