SELECT
  comps.component_item_id ,
  bill.organization_id ,
  bill.assembly_item_id ,
  comps.operation_seq_num ,
  subs.substitute_component_id ,
  substitute_item_quantity
FROM
  bom_bill_of_materials bill ,
  mrp_plan_organizations_v orgs ,
  mrp_system_items msi ,
  bom_inventory_components comps ,
  bom_substitute_components subs
WHERE
  ((((((((((bill.common_bill_sequence_id=
  decode(orgs.planned_organization,
  null ,null ,comps.bill_sequence_id)
  and bill.organization_id=orgs.planned_organization)
  and orgs.compile_designator=:b0)
  and orgs.organization_id=:b1)
  and msi.organization_id=orgs.planned_organization)
  and msi.compile_designator=orgs.compile_designator)
  and msi.inventory_item_id=comps.component_item_id)
  and TRUNC(comps.effectivity_date)<=TRUNC(sysdate ))
  and comps.implementation_date is  not null )
  and NVL(comps.disable_date,(sysdate +1))<sysdate )
  and comps.component_sequence_id=subs.component_sequence_id)
ORDER BY
  bill.organization_id,
  comps.component_item_id;
