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
