FLM Flow Schedule

Description
Categories: BI Publisher, Manufacturing
Application: Flow Manufacturing
Source: Flow Schedule Report (XML)
Short Name: FLMRFSCH_XML
DB package: FLM_FLMRFSCH_XMLP_PKG
(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
Parameter Name SQL text Validation
FLM_SRS_YES_NO_DUMMY
 
Number
Organization Id
 
Number
Display Optional Items Only
 
LOV Oracle
Display BOM
 
LOV Oracle
Schedule Group
 
LOV Oracle
To Date
 
LOV Oracle
From Date
 
LOV Oracle
To Line
 
LOV Oracle
From Line
 
LOV Oracle