MRP Late Order

SELECT 
         null              C_wip2_item
,        sys.description               C_wip2_description
,        par.organization_code              C_wip2_organization
,        wip.wip_entity_name           C_wip2_job_number
,        round(wip.job_quantity,:P_QTY_PRECISION)              C_wip2_quantity
,        bom2.calendar_date            C_wip2_order_date
,        wip.start_date                C_wip2_start_date
,        wip.schedule_completion_date  C_wip2_due_date
,        lu1.meaning                   C_wip2_firm
,        lu2.meaning                   C_wip2_job_status
         &C_SORT_COLUMN, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_wip2_flex_item', 'INV', 'MSTK', 101, sys.ORGANIZATION_ID, sys.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_wip2_FLEX_ITEM
FROM     mrp_item_wip_entities  wip
,        mrp_system_items       req
,        mtl_system_items       sys
,        mtl_item_categories    ic
,        mfg_lookups            lu1
,        mfg_lookups            lu2
,        bom_calendar_dates     bom1
,        bom_calendar_dates     bom2
,        mrp_plan_organizations_v      org_v
,        mtl_parameters              par
         &C_CATEGORY_FROM                                                    
         &C_ABC_FROM
WHERE    lu1.lookup_type              = 'SYS_YES_NO'
AND      lu1.lookup_code              = wip.firm_planned_status_type
AND      lu2.lookup_type              = 'WIP_JOB_STATUS'
AND      lu2.lookup_code              = wip.status_code
         &C_PLANNER_RANGE                                                    
         &C_BUYER_RANGE                                                      
         &C_ABC_RANGE                                                        
         &C_CATEGORY_WHERE                                                   
         &C_ABC_WHERE                                                        
AND      &P_ITEM_RANGE                                                       
AND      &P_CAT_RANGE
AND      req.organization_id          = wip.organization_id  
AND      req.inventory_item_id        = wip.inventory_item_id
AND      sys.inventory_item_id        = wip.inventory_item_id   
AND      sys.organization_id          = wip.organization_id   
AND      bom2.calendar_code           = bom1.calendar_code
AND      bom2.exception_set_id        = bom1.exception_set_id   
AND      bom2.seq_num                 = bom1.prior_seq_num 
                                       - NVL(req.preprocessing_lead_time,0)
AND      bom1.calendar_date           = wip.start_date                     
AND      bom1.calendar_code           = :P_CAL_CODE
AND      bom1.exception_set_id        = :P_CAL_EXCEPTION_SET_ID
AND      ic.inventory_item_id         = req.inventory_item_id
AND      ic.organization_id           = req.organization_id
AND      ic.category_set_id           = :P_CAT_SET
AND      wip.job_quantity             > 0
AND      wip.compile_designator       = req.compile_designator
AND      org_v.compile_designator       = :P_PLAN_NAME
AND      wip.organization_id          =  org_v.planned_organization
AND      par.organization_id          =  wip.organization_id
AND      wip.compile_designator         = org_v.compile_designator
AND      DECODE(:P_ORG_TYPE, 1, org_v.planned_organization, org_v.organization_id)                = :P_ORG_ID
AND      wip.status_code              IN (3, 6)
--AND      wip.start_date                < :P_ADVANCE_NOTICE_DATE
--AND      wip.schedule_completion_date >= :P_ADVANCE_NOTICE_DATE
AND      wip.start_date                < :P_ADVANCE_NOTICE_DATE
AND      wip.schedule_completion_date >= :P_ADVANCE_NOTICE_DATE
ORDER BY sys.SEGMENT1 &C_ORDER_BY wip.schedule_completion_date,sys.SEGMENT1, 
wip.job_quantity, bom2.calendar_date, wip.start_date,        wip.schedule_completion_date, 
wip.wip_entity_id