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 |
