Query to get Product details using ingredient in ASCP

SELECT
mtp1.organization_code
,(
    SELECT
      item_name
    FROM
      msc_system_items
    WHERE
      inventory_item_id= mbc.using_assembly_id
    and organization_id=mbc.organization_id
      and plan_id=msib.plan_id
  )
  product
,(
    SELECT
      description
    FROM
      msc_system_items
    WHERE
      inventory_item_id= mbc.using_assembly_id
      and organization_id=mbc.organization_id
      and plan_id=msib.plan_id
      )
  product_description
,(
    SELECT
      uom_code
    FROM
      msc_system_items
    WHERE
      inventory_item_id= mbc.using_assembly_id
     and organization_id=mbc.organization_id
      and plan_id=msib.plan_id
  )
product_uom
, md.USING_REQUIREMENT_QUANTITY fg_forecast
,md.USING_ASSEMBLY_DEMAND_DATE fg_forecast_due_date
,msib.item_name ingredient
,msib.description ingredient_desc
,msib.uom_code  ingredient_uom
,mbc.USAGE_QUANTITY
,(md.USING_REQUIREMENT_QUANTITY*mbc.USAGE_QUANTITY ) total_ingredient_requirement
FROM
  MSC_BOM_COMPONENTS mbc
, msc_system_items msib
, msc_trading_partners mtp
,MSC_DEMANDS md
, msc_trading_partners mtp1
WHERE
  mbc.inventory_item_id   =:p_item_id
AND mbc.plan_id           =msib.plan_id
AND msib.plan_id          =:p_plan_id
AND mtp.sr_tp_id          =msib.organization_id
AND mbc.organization_id   =msib.organization_id
AND mbc.inventory_item_id = msib.inventory_item_id
AND mbc.organization_id   =3095
AND md.ORGANIZATION_ID    =75
AND md.Plan_id            =:p_plan_id
AND md.ORIGINATION_TYPE   =29
AND mbc.using_assembly_id =md.INVENTORY_ITEM_ID
AND mtp1.sr_tp_id          =md.organization_id
order by 6 asc,2 asc
 ;