FLM Flow Schedule

(select wl.line_code,
       wfs.scheduled_completion_date completion_date,
       wfs.build_sequence build_seq,
       &P_ASSY_FLEX C_ASSY_FLEX,
	   msi1.primary_uom_code assembly_uom,
       wfs.planned_quantity-wfs.quantity_completed assembly_qty,
       wsg.schedule_group_name sch_group,
       wfs.schedule_number sch_number,
       decode(NVL(wfs.demand_source_line,-1),-1,NULL,&P_SO_FLEX) C_SO_FLEX,
	   so_lines.line_number so_line,
       so_lines.request_date so_date,
       wfs.bom_revision bom_rev,
       be.operation_seq_num operation_seq,
       be.component_item_id component_id,
       &P_COMP_FLEX C_COMP_FLEX,
	   be.primary_uom_code component_uom,
       be.extended_quantity component_qty,
       substr(flm_schedule_report.get_revision(be.organization_id,
       be.component_item_id,be.effectivity_date),0,3) rev,
       bic.change_notice ECO,
       be.optional optional_id,
       ml.meaning optional,
       bic.supply_subinventory subinventory,
       decode(NVL(bic.supply_locator_id,-1),-1,NULL,&P_LOC_FLEX) C_LOC_FLEX,
	   fnd_flex_xml_publisher_apis.process_kff_combination_1('assembly_name', 'INV', 'MSTK', 101, MSI1.ORGANIZATION_ID, MSI1.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') assembly_name,
	   fnd_flex_xml_publisher_apis.process_kff_combination_1('component_name', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') component_name,
	   fnd_flex_xml_publisher_apis.process_kff_combination_1('so_name', 'INV', 'MKTS', 101, NULL, MSO.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE') so_name,
	   fnd_flex_xml_publisher_apis.process_kff_combination_1('locator_name', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') locator_name
from wip_flow_schedules wfs, wip_lines wl, wip_schedule_groups wsg, oe_order_lines_all so_lines,
        mtl_system_items msi1, bom_explosions be, mtl_sales_orders mso,
        mtl_system_items msi2, bom_inventory_components bic, mfg_lookups ml, mtl_item_locations mil
where :P_DISPLAY_BOM = 1
    and wfs.organization_id = :P_ORG_ID
    and trunc(wfs.scheduled_completion_date) between :CP_DATE_FROM and :CP_DATE_TO
    and (wfs.planned_quantity-wfs.quantity_completed > 0)
    and wfs.primary_item_id = msi1.inventory_item_id
    and wfs.organization_id = msi1.organization_id
    and wfs.demand_source_header_id  = mso.sales_order_id (+)
    and wfs.demand_source_line  = so_lines.line_id (+)
    and wfs.line_id = wl.line_id
    and wfs.organization_id = wl.organization_id
    and wfs.schedule_group_id = wsg.schedule_group_id (+)
    and wfs.organization_id = wsg.organization_id (+)
    and wfs.primary_item_id = be.top_item_id
    and wfs.organization_id = be.organization_id
    and be.component_item_id = msi2.inventory_item_id
    and be.organization_id = msi2.organization_id
    and be.component_sequence_id = bic.component_sequence_id
    and ((be.plan_level = 1 and msi2.item_type != 'PH') or
            (be.plan_level > 1 and (flm_schedule_report.display_item(1,be.sort_order,be.top_bill_sequence_id,
             be.organization_id))=1))
    and ml.lookup_type = 'SYS_YES_NO'
    and ml.lookup_code = be.optional
    and mil.inventory_location_id (+) = bic.supply_locator_id
    and mil.organization_id (+) = :P_ORG_ID
&P_LIMIT_LINE
&P_LIMIT_SCH_GROUP)
union
(select wl.line_code,
       wfs.scheduled_completion_date completion_date,
       wfs.build_sequence build_seq,
       &P_ASSY_FLEX C_ASSY_FLEX,
	   msi1.primary_uom_code assembly_uom,
       wfs.planned_quantity-wfs.quantity_completed assembly_qty,
       wsg.schedule_group_name sch_group,
       wfs.schedule_number sch_number,
       decode(NVL(wfs.demand_source_line,-1),-1,NULL,&P_SO_FLEX) C_SO_FLEX,
	   so_lines.line_number so_line,
       so_lines.request_date so_date,
       wfs.bom_revision bom_rev,
       0 operation_seq,
       0 component_id,
       NULL C_COMP_FLEX,
       NULL component_uom,
       0 component_qty,
       NULL rev,
       NULL ECO,
       0 optional_id,
       NULL optional,
       NULL subinventory,
       NULL C_LOC_FLEX,
       null assembly_name,
	   null component_name,
	  null so_name,
	   null locator_name
from wip_flow_schedules wfs, wip_lines wl, wip_schedule_groups wsg, oe_order_lines_all so_lines,
        mtl_system_items msi1, mtl_sales_orders mso
where (:P_DISPLAY_BOM = 2 OR 
    not exists (select top_item_id
                 from bom_explosions be 
                 where be.top_item_id = wfs.primary_item_id
                     and be.organization_id = :P_ORG_ID))
    and wfs.organization_id = :P_ORG_ID
    and trunc(wfs.scheduled_completion_date) between :CP_DATE_FROM and :CP_DATE_TO
    and (wfs.planned_quantity-wfs.quantity_completed > 0)
    and wfs.primary_item_id = msi1.inventory_item_id
    and wfs.organization_id = msi1.organization_id
    and wfs.demand_source_header_id  = mso.sales_order_id (+)
    and wfs.demand_source_line  = so_lines.line_id (+)
    and wfs.line_id = wl.line_id
    and wfs.organization_id = wl.organization_id
    and wfs.schedule_group_id = wsg.schedule_group_id (+)
    and wfs.organization_id = wsg.organization_id (+)
&P_LIMIT_LINE
&P_LIMIT_SCH_GROUP)
ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC,5 ASC,6 ASC,7 ASC,8 ASC,9 ASC,10 ASC,11 ASC,12 ASC , line_code , completion_date , build_seq , operation_seq