Supply Chain Hub - MRP Release Diagnostics

  select
  msib.bom_item_type,
  msib.release_time_fence_code,
  (select 'Y' from mrp_cust_sup_org_v mcsov where mr.organization_id=mcsov.organization_id and rownum=1) cust_supply_org,
  (select bor.cfm_routing_flag from bom_operational_routings bor where mr.inventory_item_id=bor.assembly_item_id and mr.organization_id=bor.organization_id and nvl(mr.alternate_routing_designator,'-23453')=nvl(bor.alternate_routing_designator,'-23453')) cfm_routing_flag,
  nvl(mwdo.orders_default_job_status,1) status_type,
  nvl(mr.implement_date,mrp_calendar.next_work_day(mr.organization_id,1,greatest(nvl(mr.firm_date,mr.new_schedule_date),trunc(sysdate)))) last_unit_completion_date,
  coalesce(mr.implement_wip_class_code, mwdo.job_class_code, xxen_schub.default_acc_class(mr.organization_id,mr.inventory_item_id,nvl(mr.implement_project_id,mr.project_id))) class_code,
  coalesce(mr.implement_job_name,fnd_profile.value('WIP_JOB_PREFIX')||wip_job_number_s.nextval) job_name,
  greatest(nvl(mr.firm_quantity,mr.new_order_quantity)-nvl(mr.quantity_in_process,0)-nvl(mr.implemented_quantity,0),0) quantity,
  nvl(mr.implement_demand_class,msd.demand_class) demand_class,
  nvl(mr.implement_project_id,mr.project_id) project_id,
  nvl(mr.implement_task_id,mr.task_id) task_id,
  coalesce(mr.implement_schedule_group_id,(select wsg.schedule_group_id from wip_schedule_groups wsg where msib.planner_code=wsg.schedule_group_name and mr.organization_id=wsg.organization_id)) schedule_group_id,
  mr.transaction_id,
  mr.source_vendor_id,
  mr.inventory_item_id,
  mr.organization_id,
  mr.source_organization_id,
  mr.order_type,
  mr.implement_build_sequence,
  nvl(mr.implement_alternate_bom,mr.alternate_bom_designator) alternate_bom_designator,
  nvl(mr.implement_alternate_routing,mr.alternate_routing_designator) alternate_routing_designator,
  nvl(mr.implement_end_item_unit_number,mr.end_item_unit_number) end_item_unit_number,
  mr.release_errors
  from
  mrp_recommendations mr,
  mrp_schedule_designators msd,
  mtl_system_items_b msib,
  (
  select
  mwdo.*
  from
  (select fnd_global.user_id user_id from dual) x,
  mrp_workbench_display_options mwdo
  where
  x.user_id=mwdo.user_id(+) and
  rownum=1
  ) mwdo
  where
  mr.transaction_id=:p_transaction_id and
  (mr.inventory_item_id, mr.organization_id, mr.compile_designator) in (select msi.inventory_item_id, msi.organization_id, msi.compile_designator from mrp_system_items msi) and
  mr.compile_designator=msd.schedule_designator(+) and
  mr.organization_id=msd.organization_id(+) and
  mr.organization_id=msib.organization_id and
  mr.inventory_item_id=msib.inventory_item_id