MRP Supply Chain Late Order

Description
Categories: BI Publisher, Manufacturing
Application: Master Scheduling/MRP
Source: Supply Chain Late Order Report (XML)
Short Name: MRPRPLOS_XML
DB package: MRP_MRPRPLOR_XMLP_PKG
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
Parameter Name SQL text Validation
Quantity Precision
 
Number
Category Structure
 
Number
Org id
 
Number
ABC Class
 
LOV Oracle
ABC Assignment Group
 
LOV Oracle
To
 
Categories From
 
Category Set
 
LOV Oracle
To
 
Items From
 
Buyer
 
LOV Oracle
Planner
 
LOV Oracle
Sort
 
LOV Oracle
Advance Notice Date
 
LOV Oracle
Organization Selection
 
LOV Oracle
Plan Name
 
LOV Oracle