eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MRP_ORDERS_SC_V

Source

View Text - Preformatted


SELECT 'MRP_RECOMMENDATIONS' , REC.ROWID , REC.TRANSACTION_ID , REC.LAST_UPDATE_DATE , REC.LAST_UPDATED_BY , REC.CREATION_DATE , REC.CREATED_BY , REC.LAST_UPDATE_LOGIN , MSI.INVENTORY_ITEM_ID , NVL(MPO.PLANNED_ORGANIZATION, PLANS.ORGANIZATION_ID) , MSI.ORGANIZATION_CODE , PLANS.ORGANIZATION_ID , PLANS.COMPILE_DESIGNATOR , REC.NEW_SCHEDULE_DATE , REC.OLD_SCHEDULE_DATE , REC.NEW_WIP_START_DATE , REC.DISPOSITION_ID , DECODE(REC.ORDER_TYPE, 1, IPO.PO_NUMBER, 2, IPO.PO_NUMBER, 3, IWE.WIP_ENTITY_NAME, 7, IWE.WIP_ENTITY_NAME, 8, IPO.PO_NUMBER, 11, IPO.PO_NUMBER, 12, IPO.PO_NUMBER, 14, IWE.WIP_ENTITY_NAME, 15, IWE.WIP_ENTITY_NAME, 27, IWE.WIP_ENTITY_NAME, 28, IWE.WIP_ENTITY_NAME, NULL) , REC.DISPOSITION_STATUS_TYPE , REC.ORDER_TYPE , L1.MEANING , REC.VENDOR_ID , REC.NEW_ORDER_QUANTITY , REC.OLD_ORDER_QUANTITY , REC.NEW_ORDER_PLACEMENT_DATE , REC.FIRM_PLANNED_TYPE , REC.RESCHEDULED_FLAG , REC.NEW_PROCESSING_DAYS , REC.IMPLEMENTED_QUANTITY , REC.LAST_UNIT_COMPLETION_DATE , REC.FIRST_UNIT_START_DATE , REC.LAST_UNIT_START_DATE , REC.NEW_DOCK_DATE , REC.QUANTITY_IN_PROCESS , REC.FIRM_QUANTITY , REC.FIRM_DATE , REC.LINE_ID , WL.LINE_CODE , REC.IMPLEMENT_DEMAND_CLASS , FLEX.CONCATENATED_SEGMENTS , REC.IMPLEMENT_DATE , REC.IMPLEMENT_QUANTITY , REC.IMPLEMENT_FIRM , TO_NUMBER(NULL) , REC.IMPLEMENT_WIP_CLASS_CODE , REC.IMPLEMENT_JOB_NAME , TO_NUMBER(NULL) , REC.IMPLEMENT_STATUS_CODE , REC.IMPLEMENT_LOCATION_ID , REC.RELEASE_STATUS , REC.IMPLEMENT_AS , L4.MEANING , MSI.PLANNER_CODE , TO_NUMBER(NULL) , MSI.CATEGORY_ID , MSI.MRP_PLANNING_CODE , MSI.REPETITIVE_TYPE , MSI.BUILD_IN_WIP_FLAG , MSI.PURCHASING_ENABLED_FLAG , MSI.PLANNING_MAKE_BUY_CODE , GREATEST(0, NEW_ORDER_PLACEMENT_DATE - (TRUNC(SYSDATE) )) , MSI.BOM_ITEM_TYPE , MSI.BASE_ITEM_ID , MSI.WIP_SUPPLY_TYPE , REC.SOURCE_ORGANIZATION_ID , MTL.ORGANIZATION_CODE , IWE.STATUS_CODE , TO_CHAR(NULL) , MSI.FULL_PEGGING , REC.SOURCE_SUPPLY_SCHEDULE_NAME , NVL(REC.SOURCE_VENDOR_ID,REC.VENDOR_ID) , VEN.VENDOR_NAME , NVL(REC.SOURCE_VENDOR_SITE_ID,REC.VENDOR_SITE_ID) , MRP_GET_PROJECT.VENDOR_SITE_CODE(REC.ORGANIZATION_ID, NVL(REC.SOURCE_VENDOR_SITE_ID,REC.VENDOR_SITE_ID)) , IMPLEMENT_SOURCE_ORG_ID , IMPLEMENT_VENDOR_ID , IMPLEMENT_VENDOR_SITE_ID , DECODE(MSI.IN_SOURCE_PLAN, 1, 1, 2) , rec.schedule_compression_days , rec.project_id , rec.task_id , mrp_get_project.project(rec.project_id) , mrp_get_project.task(rec.task_id) , rec.implement_project_id , rec.implement_task_id , rec.planning_group , rec.status , rec.applied , flex.release_time_fence_code , rec.implement_schedule_group_id , rec.implement_build_sequence , DECODE(REC.ORDER_TYPE, 3, IWE.BUILD_SEQUENCE, 7, IWE.BUILD_SEQUENCE, 14, IWE.BUILD_SEQUENCE, 15, IWE.BUILD_SEQUENCE, 27, IWE.BUILD_SEQUENCE, to_number( NULL)) , DECODE(REC.ORDER_TYPE, 3, IWE.SCHEDULE_GROUP_ID, 7, IWE.SCHEDULE_GROUP_ID, 14, IWE.SCHEDULE_GROUP_ID, 15, IWE.SCHEDULE_GROUP_ID, 27, IWE.SCHEDULE_GROUP_ID, to_number(NULL)) , rec.alternate_bom_designator , rec.alternate_routing_designator , rec.implement_alternate_bom , rec.implement_alternate_routing ,wsg.schedule_group_name ,rec.implement_employee_id , bor.cfm_routing_flag ,msi.buyer_name ,rec.release_errors ,rec.number1 ,rec.end_item_unit_number ,rec.implement_end_item_unit_number ,msi.description ,nvl(rec.planning_group, '0') ,nvl(msi.buyer_name, '0') ,nvl(msi.planner_code,'0') ,nvl(rec.project_id, 0) ,nvl(rec.task_id,0) , null , null ,nvl(rec.supply_avail_date,rec.new_schedule_date) ,rec.old_dock_date FROM MRP_PLANS PLANS, MRP_PLAN_ORGANIZATIONS MPO, MRP_SYSTEM_ITEMS MSI , MTL_SYSTEM_ITEMS_B_KFV FLEX, MRP_RECOMMENDATIONS REC, MFG_LOOKUPS L4, MFG_LOOKUPS L1, MTL_PARAMETERS MTL, MRP_ITEM_PURCHASE_ORDERS IPO, MRP_ITEM_WIP_ENTITIES IWE, PO_VENDORS VEN, WIP_LINES WL, WIP_SCHEDULE_GROUPS WSG, BOM_OPERATIONAL_ROUTINGS BOR WHERE BOR.ASSEMBLY_ITEM_ID (+)= REC.INVENTORY_ITEM_ID AND BOR.ORGANIZATION_ID (+)= REC.ORGANIZATION_ID AND BOR.ALTERNATE_ROUTING_DESIGNATOR (+)= REC.ALTERNATE_ROUTING_DESIGNATOR AND WSG.SCHEDULE_GROUP_ID (+)= IWE.SCHEDULE_GROUP_ID AND WL.LINE_ID (+)= REC.LINE_ID AND WL.ORGANIZATION_ID(+) = REC.ORGANIZATION_ID AND VEN.VENDOR_ID(+) = NVL(REC.SOURCE_VENDOR_ID,REC.VENDOR_ID) AND MTL.ORGANIZATION_ID(+) = REC.SOURCE_ORGANIZATION_ID AND IWE.WIP_ENTITY_ID(+) = REC.DISPOSITION_ID AND IWE.COMPILE_DESIGNATOR(+) = REC.COMPILE_DESIGNATOR AND IWE.ORGANIZATION_ID(+) = REC.ORGANIZATION_ID AND IWE.INVENTORY_ITEM_ID(+) =DECODE(REC.ORDER_TYPE, 14, REC.BY_PRODUCT_USING_ASSY_ID, 15, REC.BY_PRODUCT_USING_ASSY_ID, 28, REC.BY_PRODUCT_USING_ASSY_ID, REC.INVENTORY_ITEM_ID) AND IPO.TRANSACTION_ID(+) = REC.DISPOSITION_ID AND IPO.COMPILE_DESIGNATOR(+) = REC.COMPILE_DESIGNATOR AND IPO.ORGANIZATION_ID(+) = REC.ORGANIZATION_ID AND IPO.INVENTORY_ITEM_ID(+) = REC.INVENTORY_ITEM_ID AND REC.COMPILE_DESIGNATOR = MSI.COMPILE_DESIGNATOR AND REC.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND FLEX.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND FLEX.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND REC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND L4.LOOKUP_TYPE(+) = 'MRP_WORKBENCH_IMPLEMENT_AS' AND L4.LOOKUP_CODE(+) = REC.IMPLEMENT_AS AND L1.LOOKUP_TYPE = 'MRP_ORDER_TYPE' AND L1.LOOKUP_CODE = REC.ORDER_TYPE AND REC.ORDER_TYPE != 4 AND MSI.ORGANIZATION_ID = NVL(MPO.PLANNED_ORGANIZATION, PLANS.ORGANIZATION_ID) AND MSI.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR AND MPO.PLAN_LEVEL (+) = 2 AND MPO.COMPILE_DESIGNATOR (+) = PLANS.COMPILE_DESIGNATOR AND MPO.ORGANIZATION_ID (+) = PLANS.ORGANIZATION_ID UNION ALL SELECT 'MRP_SUGG_REP_SCHEDULES' , SCHED.ROWID , SCHED.TRANSACTION_ID , SCHED.LAST_UPDATE_DATE , SCHED.LAST_UPDATED_BY , SCHED.CREATION_DATE , SCHED.CREATED_BY , SCHED.LAST_UPDATE_LOGIN , MSI.INVENTORY_ITEM_ID , NVL(MPO.PLANNED_ORGANIZATION,PLANS.ORGANIZATION_ID), MSI.ORGANIZATION_CODE , PLANS.ORGANIZATION_ID , PLANS.COMPILE_DESIGNATOR , SCHED.FIRST_UNIT_COMPLETION_DATE , TO_DATE(NULL) , TO_DATE(NULL) , TO_NUMBER(NULL) , NULL , TO_NUMBER(NULL) , L1.LOOKUP_CODE , L1.MEANING , TO_NUMBER(NULL) , SCHED.DAILY_RATE , TO_NUMBER(NULL) , TO_DATE(NULL) , NVL(SCHED.FIRM_PLANNED_STATUS_TYPE,2) , TO_NUMBER(NULL) , SCHED.PROCESSING_DAYS , TO_NUMBER(NULL) , SCHED.LAST_UNIT_COMPLETION_DATE , SCHED.FIRST_UNIT_START_DATE , SCHED.LAST_UNIT_START_DATE, TO_DATE(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_DATE(NULL) , SCHED.REPETITIVE_LINE , WL.LINE_CODE , SCHED.IMPLEMENT_DEMAND_CLASS , FLEX.CONCATENATED_SEGMENTS, SCHED.IMPLEMENT_DATE , SCHED.IMPLEMENT_DAILY_RATE , SCHED.IMPLEMENT_FIRM , SCHED.IMPLEMENT_PROCESSING_DAYS , SCHED.IMPLEMENT_WIP_CLASS_CODE , NULL , SCHED.IMPLEMENT_LINE_ID , TO_NUMBER(NULL) , TO_NUMBER(NULL) , SCHED.RELEASE_STATUS , L2.LOOKUP_CODE , L2.MEANING , MSI.PLANNER_CODE , TO_NUMBER(NULL) , MSI.CATEGORY_ID , MSI.MRP_PLANNING_CODE , MSI.REPETITIVE_TYPE , MSI.BUILD_IN_WIP_FLAG , MSI.PURCHASING_ENABLED_FLAG , MSI.PLANNING_MAKE_BUY_CODE , GREATEST(0, SCHED.FIRST_UNIT_START_DATE - TRUNC(SYSDATE)) , MSI.BOM_ITEM_TYPE , MSI.BASE_ITEM_ID , MSI.WIP_SUPPLY_TYPE , TO_NUMBER(NULL) , NULL , TO_NUMBER(NULL) , TO_CHAR(NULL) , MSI.FULL_PEGGING , NULL, TO_NUMBER(NULL), NULL, TO_NUMBER(NULL), NULL, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), DECODE(NVL(PLANS.USE_NEW_PLANNER,2), 2, DECODE(PLANS.PLAN_TYPE, 1, DECODE(PLANS.CURR_SCHEDULE_TYPE, 2, DECODE(MSI.MRP_PLANNING_CODE, 4, 1, 2), 2), 2, DECODE(MSI.MRP_PLANNING_CODE, 3, 1, 2)), DECODE(MSI.IN_SOURCE_PLAN, 1, 1, 2)) , to_number(null) , to_number(null) , to_number(null) , to_char(null) , to_char(null) , to_number(null) , to_number(null) , to_char(null) , SCHED.STATUS , to_number(null) , to_number(null) , to_number(NULL) , to_number(NULL) , to_number(NULL) , to_number(NULL) , null , null , null , null , null , to_number(null) , to_number(null) ,msi.buyer_name ,sched.release_errors ,to_number(NULL) ,null ,null ,msi.description ,null ,nvl(msi.buyer_name, '0') ,nvl(msi.planner_code,'0') ,to_number(null) ,to_number(null) ,null ,null ,to_date(null) ,to_date(null) FROM MRP_PLANS PLANS, MRP_PLAN_ORGANIZATIONS MPO, MRP_SYSTEM_ITEMS MSI , MTL_SYSTEM_ITEMS_B_KFV FLEX, MRP_SUGG_REP_SCHEDULES SCHED, MFG_LOOKUPS L1, MFG_LOOKUPS L2, WIP_LINES WL WHERE WL.LINE_ID = SCHED.REPETITIVE_LINE AND FLEX.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND FLEX.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND SCHED.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND SCHED.COMPILE_DESIGNATOR = MSI.COMPILE_DESIGNATOR AND SCHED.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND L2.LOOKUP_TYPE (+) = 'MRP_WORKBENCH_IMPLEMENT_AS' AND L2.LOOKUP_CODE (+) = DECODE(SCHED.RELEASE_STATUS, 1, 4) AND L1.LOOKUP_TYPE = 'MRP_ORDER_TYPE' AND L1.LOOKUP_CODE = 13 AND MSI.ORGANIZATION_ID = NVL(MPO.PLANNED_ORGANIZATION,PLANS.ORGANIZATION_ID) AND MSI.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR AND MPO.PLAN_LEVEL (+) = 2 AND MPO.COMPILE_DESIGNATOR (+) = PLANS.COMPILE_DESIGNATOR AND MPO.ORGANIZATION_ID (+) = PLANS.ORGANIZATION_ID UNION ALL SELECT 'MRP_GROSS_REQUIREMENTS' , MGR.ROWID , MGR.DEMAND_ID , MGR.LAST_UPDATE_DATE , MGR.LAST_UPDATED_BY , MGR.CREATION_DATE , MGR.CREATED_BY , MGR.LAST_UPDATE_LOGIN , MSI.INVENTORY_ITEM_ID , NVL(MPO.PLANNED_ORGANIZATION, PLANS.ORGANIZATION_ID) , MSI.ORGANIZATION_CODE , PLANS.ORGANIZATION_ID , MSI.COMPILE_DESIGNATOR , MGR.USING_ASSEMBLY_DEMAND_DATE , MGR.OLD_DEMAND_DATE , TO_DATE(NULL) , MGR.DISPOSITION_ID , DECODE(MGR.ORIGINATION_TYPE, 2, WE.WIP_ENTITY_NAME, 3, WE.WIP_ENTITY_NAME, 17, WE.WIP_ENTITY_NAME, 18, IPO.PO_NUMBER, 19, IPO.PO_NUMBER, 20, IPO.PO_NUMBER, 23, IPO.PO_NUMBER, 24, IPO.PO_NUMBER, 25, WE.WIP_ENTITY_NAME, 26, WE.WIP_ENTITY_NAME, 6, SO.SEGMENT1 || ':' || SO.SEGMENT2 || ':' || SO.SEGMENT3, NULL) , TO_NUMBER(NULL) , nvl( MGR.ORIGINATION_TYPE ,0), L1.MEANING , TO_NUMBER(NULL) , - NVL(MGR.DAILY_DEMAND_RATE, MGR.USING_REQUIREMENTS_QUANTITY) , MGR.OLD_DEMAND_QUANTITY , TO_DATE(NULL) , TO_NUMBER(NVL(NULL,2)) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , MGR.ASSEMBLY_DEMAND_COMP_DATE , TO_DATE(NULL) , TO_DATE(NULL) , TO_DATE(NULL) , TO_NUMBER(NULL) , MGR.FIRM_QUANTITY , MGR.FIRM_DATE , TO_NUMBER(NULL) , NULL , NULL , FLEX.CONCATENATED_SEGMENTS, TO_DATE(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_CHAR(NULL) , NULL , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , NULL , MSI.PLANNER_CODE , MGR.USING_ASSEMBLY_ITEM_ID , MSI.CATEGORY_ID , MSI.MRP_PLANNING_CODE , MSI.REPETITIVE_TYPE , MSI.BUILD_IN_WIP_FLAG , MSI.PURCHASING_ENABLED_FLAG , MSI.PLANNING_MAKE_BUY_CODE , GREATEST(0, USING_ASSEMBLY_DEMAND_DATE - (TRUNC(SYSDATE) )) , MSI.BOM_ITEM_TYPE , MSI.BASE_ITEM_ID , MSI.WIP_SUPPLY_TYPE , MGR.SOURCE_ORGANIZATION_ID , MTL.ORGANIZATION_CODE , TO_NUMBER(NULL) , FLEX2.CONCATENATED_SEGMENTS, MSI.FULL_PEGGING , MGR.DEMAND_SCHEDULE_NAME, TO_NUMBER(NULL), NULL, TO_NUMBER(NULL), NULL, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), DECODE(MSI.IN_SOURCE_PLAN, 1, 1, 2), to_number(null) , mgr.project_id , mgr.task_id , mrp_get_project.project(mgr.project_id) , mrp_get_project.task(mgr.task_id) , to_number(null) , to_number(null) , mgr.planning_group , mgr.status , mgr.applied , to_number(null) , to_number(NULL) , to_number(NULL) , to_number(NULL) , to_number(NULL) , null , null , null , null , null , to_number(null) , to_number(null) ,msi.buyer_name , NULL , to_number(NULL) ,mgr.end_item_unit_number ,null ,msi.description ,nvl(mgr.planning_group, '0') ,nvl(msi.buyer_name, '0') ,nvl(msi.planner_code,'0') ,nvl(mgr.project_id,0) ,nvl(mgr.task_id,0) ,mrp_get_project.project(mgr.lending_project_id) ,mrp_get_project.task(mgr.lending_task_id) ,to_date(null) ,to_date(null) FROM MRP_PLANS PLANS, MRP_PLAN_ORGANIZATIONS MPO, MRP_SYSTEM_ITEMS MSI , MTL_SYSTEM_ITEMS_B_KFV FLEX, MRP_GROSS_REQUIREMENTS MGR, MTL_SYSTEM_ITEMS_B_KFV FLEX2, MTL_PARAMETERS MTL, WIP_ENTITIES WE, MRP_ITEM_PURCHASE_ORDERS IPO, MFG_LOOKUPS L1, MRP_SCHEDULE_DATES DATES, MTL_SALES_ORDERS SO WHERE IPO.PURCHASE_ORDER_ID(+) = MGR.DISPOSITION_ID AND IPO.INVENTORY_ITEM_ID(+) = MGR.INVENTORY_ITEM_ID AND IPO.COMPILE_DESIGNATOR(+) = MGR.COMPILE_DESIGNATOR AND IPO.ORGANIZATION_ID(+) = MGR.ORGANIZATION_ID AND FLEX2.ORGANIZATION_ID = MGR.ORGANIZATION_ID AND FLEX2.INVENTORY_ITEM_ID = MGR.USING_ASSEMBLY_ITEM_ID AND FLEX.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND FLEX.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND WE.ORGANIZATION_ID(+) = MGR.ORGANIZATION_ID AND WE.WIP_ENTITY_ID(+) = MGR.DISPOSITION_ID AND DATES.SOURCE_SALES_ORDER_ID = SO.SALES_ORDER_ID (+) AND DATES.SCHEDULE_LEVEL (+) = 3 AND DATES.MPS_TRANSACTION_ID (+) = MGR.DISPOSITION_ID AND MTL.ORGANIZATION_ID(+) = MGR.SOURCE_ORGANIZATION_ID AND MGR.COMPILE_DESIGNATOR = MSI.COMPILE_DESIGNATOR AND MGR.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND MGR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND ((MGR.ORIGINATION_TYPE in (1,3,25) AND L1.LOOKUP_TYPE = 'MRP_PLANNED_ORDER_DEMAND') OR (MGR.ORIGINATION_TYPE in (2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 26, 27, 28, 29, 30, 31, 42, 45) AND L1.LOOKUP_TYPE = 'MRP_DEMAND_ORIGINATION')) AND L1.LOOKUP_CODE = MGR.ORIGINATION_TYPE AND MSI.ORGANIZATION_ID = NVL(MPO.PLANNED_ORGANIZATION, PLANS.ORGANIZATION_ID) AND MSI.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR AND MPO.PLAN_LEVEL (+) = 2 AND MPO.COMPILE_DESIGNATOR (+) = PLANS.COMPILE_DESIGNATOR AND MPO.ORGANIZATION_ID (+) = PLANS.ORGANIZATION_ID

View Text - HTML Formatted

SELECT 'MRP_RECOMMENDATIONS'
, REC.ROWID
, REC.TRANSACTION_ID
, REC.LAST_UPDATE_DATE
, REC.LAST_UPDATED_BY
, REC.CREATION_DATE
, REC.CREATED_BY
, REC.LAST_UPDATE_LOGIN
, MSI.INVENTORY_ITEM_ID
, NVL(MPO.PLANNED_ORGANIZATION
, PLANS.ORGANIZATION_ID)
, MSI.ORGANIZATION_CODE
, PLANS.ORGANIZATION_ID
, PLANS.COMPILE_DESIGNATOR
, REC.NEW_SCHEDULE_DATE
, REC.OLD_SCHEDULE_DATE
, REC.NEW_WIP_START_DATE
, REC.DISPOSITION_ID
, DECODE(REC.ORDER_TYPE
, 1
, IPO.PO_NUMBER
, 2
, IPO.PO_NUMBER
, 3
, IWE.WIP_ENTITY_NAME
, 7
, IWE.WIP_ENTITY_NAME
, 8
, IPO.PO_NUMBER
, 11
, IPO.PO_NUMBER
, 12
, IPO.PO_NUMBER
, 14
, IWE.WIP_ENTITY_NAME
, 15
, IWE.WIP_ENTITY_NAME
, 27
, IWE.WIP_ENTITY_NAME
, 28
, IWE.WIP_ENTITY_NAME
, NULL)
, REC.DISPOSITION_STATUS_TYPE
, REC.ORDER_TYPE
, L1.MEANING
, REC.VENDOR_ID
, REC.NEW_ORDER_QUANTITY
, REC.OLD_ORDER_QUANTITY
, REC.NEW_ORDER_PLACEMENT_DATE
, REC.FIRM_PLANNED_TYPE
, REC.RESCHEDULED_FLAG
, REC.NEW_PROCESSING_DAYS
, REC.IMPLEMENTED_QUANTITY
, REC.LAST_UNIT_COMPLETION_DATE
, REC.FIRST_UNIT_START_DATE
, REC.LAST_UNIT_START_DATE
, REC.NEW_DOCK_DATE
, REC.QUANTITY_IN_PROCESS
, REC.FIRM_QUANTITY
, REC.FIRM_DATE
, REC.LINE_ID
, WL.LINE_CODE
, REC.IMPLEMENT_DEMAND_CLASS
, FLEX.CONCATENATED_SEGMENTS
, REC.IMPLEMENT_DATE
, REC.IMPLEMENT_QUANTITY
, REC.IMPLEMENT_FIRM
, TO_NUMBER(NULL)
, REC.IMPLEMENT_WIP_CLASS_CODE
, REC.IMPLEMENT_JOB_NAME
, TO_NUMBER(NULL)
, REC.IMPLEMENT_STATUS_CODE
, REC.IMPLEMENT_LOCATION_ID
, REC.RELEASE_STATUS
, REC.IMPLEMENT_AS
, L4.MEANING
, MSI.PLANNER_CODE
, TO_NUMBER(NULL)
, MSI.CATEGORY_ID
, MSI.MRP_PLANNING_CODE
, MSI.REPETITIVE_TYPE
, MSI.BUILD_IN_WIP_FLAG
, MSI.PURCHASING_ENABLED_FLAG
, MSI.PLANNING_MAKE_BUY_CODE
, GREATEST(0
, NEW_ORDER_PLACEMENT_DATE - (TRUNC(SYSDATE) ))
, MSI.BOM_ITEM_TYPE
, MSI.BASE_ITEM_ID
, MSI.WIP_SUPPLY_TYPE
, REC.SOURCE_ORGANIZATION_ID
, MTL.ORGANIZATION_CODE
, IWE.STATUS_CODE
, TO_CHAR(NULL)
, MSI.FULL_PEGGING
, REC.SOURCE_SUPPLY_SCHEDULE_NAME
, NVL(REC.SOURCE_VENDOR_ID
, REC.VENDOR_ID)
, VEN.VENDOR_NAME
, NVL(REC.SOURCE_VENDOR_SITE_ID
, REC.VENDOR_SITE_ID)
, MRP_GET_PROJECT.VENDOR_SITE_CODE(REC.ORGANIZATION_ID
, NVL(REC.SOURCE_VENDOR_SITE_ID
, REC.VENDOR_SITE_ID))
, IMPLEMENT_SOURCE_ORG_ID
, IMPLEMENT_VENDOR_ID
, IMPLEMENT_VENDOR_SITE_ID
, DECODE(MSI.IN_SOURCE_PLAN
, 1
, 1
, 2)
, REC.SCHEDULE_COMPRESSION_DAYS
, REC.PROJECT_ID
, REC.TASK_ID
, MRP_GET_PROJECT.PROJECT(REC.PROJECT_ID)
, MRP_GET_PROJECT.TASK(REC.TASK_ID)
, REC.IMPLEMENT_PROJECT_ID
, REC.IMPLEMENT_TASK_ID
, REC.PLANNING_GROUP
, REC.STATUS
, REC.APPLIED
, FLEX.RELEASE_TIME_FENCE_CODE
, REC.IMPLEMENT_SCHEDULE_GROUP_ID
, REC.IMPLEMENT_BUILD_SEQUENCE
, DECODE(REC.ORDER_TYPE
, 3
, IWE.BUILD_SEQUENCE
, 7
, IWE.BUILD_SEQUENCE
, 14
, IWE.BUILD_SEQUENCE
, 15
, IWE.BUILD_SEQUENCE
, 27
, IWE.BUILD_SEQUENCE
, TO_NUMBER( NULL))
, DECODE(REC.ORDER_TYPE
, 3
, IWE.SCHEDULE_GROUP_ID
, 7
, IWE.SCHEDULE_GROUP_ID
, 14
, IWE.SCHEDULE_GROUP_ID
, 15
, IWE.SCHEDULE_GROUP_ID
, 27
, IWE.SCHEDULE_GROUP_ID
, TO_NUMBER(NULL))
, REC.ALTERNATE_BOM_DESIGNATOR
, REC.ALTERNATE_ROUTING_DESIGNATOR
, REC.IMPLEMENT_ALTERNATE_BOM
, REC.IMPLEMENT_ALTERNATE_ROUTING
, WSG.SCHEDULE_GROUP_NAME
, REC.IMPLEMENT_EMPLOYEE_ID
, BOR.CFM_ROUTING_FLAG
, MSI.BUYER_NAME
, REC.RELEASE_ERRORS
, REC.NUMBER1
, REC.END_ITEM_UNIT_NUMBER
, REC.IMPLEMENT_END_ITEM_UNIT_NUMBER
, MSI.DESCRIPTION
, NVL(REC.PLANNING_GROUP
, '0')
, NVL(MSI.BUYER_NAME
, '0')
, NVL(MSI.PLANNER_CODE
, '0')
, NVL(REC.PROJECT_ID
, 0)
, NVL(REC.TASK_ID
, 0)
, NULL
, NULL
, NVL(REC.SUPPLY_AVAIL_DATE
, REC.NEW_SCHEDULE_DATE)
, REC.OLD_DOCK_DATE
FROM MRP_PLANS PLANS
, MRP_PLAN_ORGANIZATIONS MPO
, MRP_SYSTEM_ITEMS MSI
, MTL_SYSTEM_ITEMS_B_KFV FLEX
, MRP_RECOMMENDATIONS REC
, MFG_LOOKUPS L4
, MFG_LOOKUPS L1
, MTL_PARAMETERS MTL
, MRP_ITEM_PURCHASE_ORDERS IPO
, MRP_ITEM_WIP_ENTITIES IWE
, PO_VENDORS VEN
, WIP_LINES WL
, WIP_SCHEDULE_GROUPS WSG
, BOM_OPERATIONAL_ROUTINGS BOR
WHERE BOR.ASSEMBLY_ITEM_ID (+)= REC.INVENTORY_ITEM_ID
AND BOR.ORGANIZATION_ID (+)= REC.ORGANIZATION_ID
AND BOR.ALTERNATE_ROUTING_DESIGNATOR (+)= REC.ALTERNATE_ROUTING_DESIGNATOR
AND WSG.SCHEDULE_GROUP_ID (+)= IWE.SCHEDULE_GROUP_ID
AND WL.LINE_ID (+)= REC.LINE_ID
AND WL.ORGANIZATION_ID(+) = REC.ORGANIZATION_ID
AND VEN.VENDOR_ID(+) = NVL(REC.SOURCE_VENDOR_ID
, REC.VENDOR_ID)
AND MTL.ORGANIZATION_ID(+) = REC.SOURCE_ORGANIZATION_ID
AND IWE.WIP_ENTITY_ID(+) = REC.DISPOSITION_ID
AND IWE.COMPILE_DESIGNATOR(+) = REC.COMPILE_DESIGNATOR
AND IWE.ORGANIZATION_ID(+) = REC.ORGANIZATION_ID
AND IWE.INVENTORY_ITEM_ID(+) =DECODE(REC.ORDER_TYPE
, 14
, REC.BY_PRODUCT_USING_ASSY_ID
, 15
, REC.BY_PRODUCT_USING_ASSY_ID
, 28
, REC.BY_PRODUCT_USING_ASSY_ID
, REC.INVENTORY_ITEM_ID)
AND IPO.TRANSACTION_ID(+) = REC.DISPOSITION_ID
AND IPO.COMPILE_DESIGNATOR(+) = REC.COMPILE_DESIGNATOR
AND IPO.ORGANIZATION_ID(+) = REC.ORGANIZATION_ID
AND IPO.INVENTORY_ITEM_ID(+) = REC.INVENTORY_ITEM_ID
AND REC.COMPILE_DESIGNATOR = MSI.COMPILE_DESIGNATOR
AND REC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND FLEX.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND FLEX.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND REC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND L4.LOOKUP_TYPE(+) = 'MRP_WORKBENCH_IMPLEMENT_AS'
AND L4.LOOKUP_CODE(+) = REC.IMPLEMENT_AS
AND L1.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND L1.LOOKUP_CODE = REC.ORDER_TYPE
AND REC.ORDER_TYPE != 4
AND MSI.ORGANIZATION_ID = NVL(MPO.PLANNED_ORGANIZATION
, PLANS.ORGANIZATION_ID)
AND MSI.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR
AND MPO.PLAN_LEVEL (+) = 2
AND MPO.COMPILE_DESIGNATOR (+) = PLANS.COMPILE_DESIGNATOR
AND MPO.ORGANIZATION_ID (+) = PLANS.ORGANIZATION_ID UNION ALL SELECT 'MRP_SUGG_REP_SCHEDULES'
, SCHED.ROWID
, SCHED.TRANSACTION_ID
, SCHED.LAST_UPDATE_DATE
, SCHED.LAST_UPDATED_BY
, SCHED.CREATION_DATE
, SCHED.CREATED_BY
, SCHED.LAST_UPDATE_LOGIN
, MSI.INVENTORY_ITEM_ID
, NVL(MPO.PLANNED_ORGANIZATION
, PLANS.ORGANIZATION_ID)
, MSI.ORGANIZATION_CODE
, PLANS.ORGANIZATION_ID
, PLANS.COMPILE_DESIGNATOR
, SCHED.FIRST_UNIT_COMPLETION_DATE
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, L1.LOOKUP_CODE
, L1.MEANING
, TO_NUMBER(NULL)
, SCHED.DAILY_RATE
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, NVL(SCHED.FIRM_PLANNED_STATUS_TYPE
, 2)
, TO_NUMBER(NULL)
, SCHED.PROCESSING_DAYS
, TO_NUMBER(NULL)
, SCHED.LAST_UNIT_COMPLETION_DATE
, SCHED.FIRST_UNIT_START_DATE
, SCHED.LAST_UNIT_START_DATE
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, SCHED.REPETITIVE_LINE
, WL.LINE_CODE
, SCHED.IMPLEMENT_DEMAND_CLASS
, FLEX.CONCATENATED_SEGMENTS
, SCHED.IMPLEMENT_DATE
, SCHED.IMPLEMENT_DAILY_RATE
, SCHED.IMPLEMENT_FIRM
, SCHED.IMPLEMENT_PROCESSING_DAYS
, SCHED.IMPLEMENT_WIP_CLASS_CODE
, NULL
, SCHED.IMPLEMENT_LINE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, SCHED.RELEASE_STATUS
, L2.LOOKUP_CODE
, L2.MEANING
, MSI.PLANNER_CODE
, TO_NUMBER(NULL)
, MSI.CATEGORY_ID
, MSI.MRP_PLANNING_CODE
, MSI.REPETITIVE_TYPE
, MSI.BUILD_IN_WIP_FLAG
, MSI.PURCHASING_ENABLED_FLAG
, MSI.PLANNING_MAKE_BUY_CODE
, GREATEST(0
, SCHED.FIRST_UNIT_START_DATE - TRUNC(SYSDATE))
, MSI.BOM_ITEM_TYPE
, MSI.BASE_ITEM_ID
, MSI.WIP_SUPPLY_TYPE
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, MSI.FULL_PEGGING
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(NVL(PLANS.USE_NEW_PLANNER
, 2)
, 2
, DECODE(PLANS.PLAN_TYPE
, 1
, DECODE(PLANS.CURR_SCHEDULE_TYPE
, 2
, DECODE(MSI.MRP_PLANNING_CODE
, 4
, 1
, 2)
, 2)
, 2
, DECODE(MSI.MRP_PLANNING_CODE
, 3
, 1
, 2))
, DECODE(MSI.IN_SOURCE_PLAN
, 1
, 1
, 2))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, SCHED.STATUS
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MSI.BUYER_NAME
, SCHED.RELEASE_ERRORS
, TO_NUMBER(NULL)
, NULL
, NULL
, MSI.DESCRIPTION
, NULL
, NVL(MSI.BUYER_NAME
, '0')
, NVL(MSI.PLANNER_CODE
, '0')
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
FROM MRP_PLANS PLANS
, MRP_PLAN_ORGANIZATIONS MPO
, MRP_SYSTEM_ITEMS MSI
, MTL_SYSTEM_ITEMS_B_KFV FLEX
, MRP_SUGG_REP_SCHEDULES SCHED
, MFG_LOOKUPS L1
, MFG_LOOKUPS L2
, WIP_LINES WL
WHERE WL.LINE_ID = SCHED.REPETITIVE_LINE
AND FLEX.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND FLEX.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND SCHED.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND SCHED.COMPILE_DESIGNATOR = MSI.COMPILE_DESIGNATOR
AND SCHED.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND L2.LOOKUP_TYPE (+) = 'MRP_WORKBENCH_IMPLEMENT_AS'
AND L2.LOOKUP_CODE (+) = DECODE(SCHED.RELEASE_STATUS
, 1
, 4)
AND L1.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND L1.LOOKUP_CODE = 13
AND MSI.ORGANIZATION_ID = NVL(MPO.PLANNED_ORGANIZATION
, PLANS.ORGANIZATION_ID)
AND MSI.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR
AND MPO.PLAN_LEVEL (+) = 2
AND MPO.COMPILE_DESIGNATOR (+) = PLANS.COMPILE_DESIGNATOR
AND MPO.ORGANIZATION_ID (+) = PLANS.ORGANIZATION_ID UNION ALL SELECT 'MRP_GROSS_REQUIREMENTS'
, MGR.ROWID
, MGR.DEMAND_ID
, MGR.LAST_UPDATE_DATE
, MGR.LAST_UPDATED_BY
, MGR.CREATION_DATE
, MGR.CREATED_BY
, MGR.LAST_UPDATE_LOGIN
, MSI.INVENTORY_ITEM_ID
, NVL(MPO.PLANNED_ORGANIZATION
, PLANS.ORGANIZATION_ID)
, MSI.ORGANIZATION_CODE
, PLANS.ORGANIZATION_ID
, MSI.COMPILE_DESIGNATOR
, MGR.USING_ASSEMBLY_DEMAND_DATE
, MGR.OLD_DEMAND_DATE
, TO_DATE(NULL)
, MGR.DISPOSITION_ID
, DECODE(MGR.ORIGINATION_TYPE
, 2
, WE.WIP_ENTITY_NAME
, 3
, WE.WIP_ENTITY_NAME
, 17
, WE.WIP_ENTITY_NAME
, 18
, IPO.PO_NUMBER
, 19
, IPO.PO_NUMBER
, 20
, IPO.PO_NUMBER
, 23
, IPO.PO_NUMBER
, 24
, IPO.PO_NUMBER
, 25
, WE.WIP_ENTITY_NAME
, 26
, WE.WIP_ENTITY_NAME
, 6
, SO.SEGMENT1 || ':' || SO.SEGMENT2 || ':' || SO.SEGMENT3
, NULL)
, TO_NUMBER(NULL)
, NVL( MGR.ORIGINATION_TYPE
, 0)
, L1.MEANING
, TO_NUMBER(NULL)
, - NVL(MGR.DAILY_DEMAND_RATE
, MGR.USING_REQUIREMENTS_QUANTITY)
, MGR.OLD_DEMAND_QUANTITY
, TO_DATE(NULL)
, TO_NUMBER(NVL(NULL
, 2))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MGR.ASSEMBLY_DEMAND_COMP_DATE
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, MGR.FIRM_QUANTITY
, MGR.FIRM_DATE
, TO_NUMBER(NULL)
, NULL
, NULL
, FLEX.CONCATENATED_SEGMENTS
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, MSI.PLANNER_CODE
, MGR.USING_ASSEMBLY_ITEM_ID
, MSI.CATEGORY_ID
, MSI.MRP_PLANNING_CODE
, MSI.REPETITIVE_TYPE
, MSI.BUILD_IN_WIP_FLAG
, MSI.PURCHASING_ENABLED_FLAG
, MSI.PLANNING_MAKE_BUY_CODE
, GREATEST(0
, USING_ASSEMBLY_DEMAND_DATE - (TRUNC(SYSDATE) ))
, MSI.BOM_ITEM_TYPE
, MSI.BASE_ITEM_ID
, MSI.WIP_SUPPLY_TYPE
, MGR.SOURCE_ORGANIZATION_ID
, MTL.ORGANIZATION_CODE
, TO_NUMBER(NULL)
, FLEX2.CONCATENATED_SEGMENTS
, MSI.FULL_PEGGING
, MGR.DEMAND_SCHEDULE_NAME
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(MSI.IN_SOURCE_PLAN
, 1
, 1
, 2)
, TO_NUMBER(NULL)
, MGR.PROJECT_ID
, MGR.TASK_ID
, MRP_GET_PROJECT.PROJECT(MGR.PROJECT_ID)
, MRP_GET_PROJECT.TASK(MGR.TASK_ID)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MGR.PLANNING_GROUP
, MGR.STATUS
, MGR.APPLIED
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MSI.BUYER_NAME
, NULL
, TO_NUMBER(NULL)
, MGR.END_ITEM_UNIT_NUMBER
, NULL
, MSI.DESCRIPTION
, NVL(MGR.PLANNING_GROUP
, '0')
, NVL(MSI.BUYER_NAME
, '0')
, NVL(MSI.PLANNER_CODE
, '0')
, NVL(MGR.PROJECT_ID
, 0)
, NVL(MGR.TASK_ID
, 0)
, MRP_GET_PROJECT.PROJECT(MGR.LENDING_PROJECT_ID)
, MRP_GET_PROJECT.TASK(MGR.LENDING_TASK_ID)
, TO_DATE(NULL)
, TO_DATE(NULL)
FROM MRP_PLANS PLANS
, MRP_PLAN_ORGANIZATIONS MPO
, MRP_SYSTEM_ITEMS MSI
, MTL_SYSTEM_ITEMS_B_KFV FLEX
, MRP_GROSS_REQUIREMENTS MGR
, MTL_SYSTEM_ITEMS_B_KFV FLEX2
, MTL_PARAMETERS MTL
, WIP_ENTITIES WE
, MRP_ITEM_PURCHASE_ORDERS IPO
, MFG_LOOKUPS L1
, MRP_SCHEDULE_DATES DATES
, MTL_SALES_ORDERS SO
WHERE IPO.PURCHASE_ORDER_ID(+) = MGR.DISPOSITION_ID
AND IPO.INVENTORY_ITEM_ID(+) = MGR.INVENTORY_ITEM_ID
AND IPO.COMPILE_DESIGNATOR(+) = MGR.COMPILE_DESIGNATOR
AND IPO.ORGANIZATION_ID(+) = MGR.ORGANIZATION_ID
AND FLEX2.ORGANIZATION_ID = MGR.ORGANIZATION_ID
AND FLEX2.INVENTORY_ITEM_ID = MGR.USING_ASSEMBLY_ITEM_ID
AND FLEX.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND FLEX.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WE.ORGANIZATION_ID(+) = MGR.ORGANIZATION_ID
AND WE.WIP_ENTITY_ID(+) = MGR.DISPOSITION_ID
AND DATES.SOURCE_SALES_ORDER_ID = SO.SALES_ORDER_ID (+)
AND DATES.SCHEDULE_LEVEL (+) = 3
AND DATES.MPS_TRANSACTION_ID (+) = MGR.DISPOSITION_ID
AND MTL.ORGANIZATION_ID(+) = MGR.SOURCE_ORGANIZATION_ID
AND MGR.COMPILE_DESIGNATOR = MSI.COMPILE_DESIGNATOR
AND MGR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MGR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND ((MGR.ORIGINATION_TYPE IN (1
, 3
, 25)
AND L1.LOOKUP_TYPE = 'MRP_PLANNED_ORDER_DEMAND') OR (MGR.ORIGINATION_TYPE IN (2
, 4
, 5
, 6
, 7
, 8
, 9
, 10
, 11
, 12
, 13
, 14
, 15
, 16
, 17
, 18
, 19
, 20
, 21
, 22
, 23
, 24
, 26
, 27
, 28
, 29
, 30
, 31
, 42
, 45)
AND L1.LOOKUP_TYPE = 'MRP_DEMAND_ORIGINATION'))
AND L1.LOOKUP_CODE = MGR.ORIGINATION_TYPE
AND MSI.ORGANIZATION_ID = NVL(MPO.PLANNED_ORGANIZATION
, PLANS.ORGANIZATION_ID)
AND MSI.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR
AND MPO.PLAN_LEVEL (+) = 2
AND MPO.COMPILE_DESIGNATOR (+) = PLANS.COMPILE_DESIGNATOR
AND MPO.ORGANIZATION_ID (+) = PLANS.ORGANIZATION_ID