View: MRPFV_PLAN_SUPPLIES

Product:	MRP - Master Scheduling/MRP
Description:
Implementation/DBA Data:	APPS.MRPFV_PLAN_SUPPLIES

View Text

SELECT DISTINCT PA2.ORGANIZATION_CODE
, ORG2.NAME
, PLAN.COMPILE_DESIGNATOR
, '_KF:INV:MSTK:SYSITEM'
, LKUP.MEANING
, REC.NEW_SCHEDULE_DATE
, REC.NEW_ORDER_QUANTITY
, 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)
, '_LA:REC.FIRM_PLANNED_TYPE:MFG_LOOKUPS:SYS_YES_NO:MEANING'
, REC.FIRM_DATE
, REC.FIRM_QUANTITY
, LINE.LINE_CODE
, PA.ORGANIZATION_CODE
, ORG.NAME
, VEN.VENDOR_NAME
, SITE.VENDOR_SITE_CODE
, MRP_GET_PROJECT.PROJECT(REC.PROJECT_ID)
, MRP_GET_PROJECT.TASK(REC.TASK_ID)
, WSG2.SCHEDULE_GROUP_NAME
, 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))
, REC.ALTERNATE_BOM_DESIGNATOR
, REC.ALTERNATE_ROUTING_DESIGNATOR
, '_LA:REC.RELEASE_STATUS:MFG_LOOKUPS:SYS_YES_NO:MEANING'
, REC.IMPLEMENT_DATE
, REC.IMPLEMENT_QUANTITY
, '_LA:REC.IMPLEMENT_AS:MFG_LOOKUPS:MRP_WORKBENCH_IMPLEMENT_AS:MEANING'
, '_LA:REC.IMPLEMENT_STATUS_CODE:MFG_LOOKUPS:WIP_JOB_STATUS:MEANING'
, REC.SCHEDULE_COMPRESSION_DAYS
, GREATEST(0
, REC.NEW_ORDER_PLACEMENT_DATE -(TRUNC(SYSDATE) ))
, REC.LAST_UNIT_START_DATE
, REC.LAST_UNIT_COMPLETION_DATE
, REC.FIRST_UNIT_START_DATE
, REC.OLD_SCHEDULE_DATE
, SYSITEM.PLANNER_CODE
, REC.NEW_PROCESSING_DAYS
, '_LA:REC.RESCHEDULED_FLAG:MFG_LOOKUPS:SYS_YES_NO:MEANING'
, REC.NEW_DOCK_DATE
, REC.NEW_ORDER_PLACEMENT_DATE
, REC.NEW_WIP_START_DATE
, REC.OLD_ORDER_QUANTITY
, REC.IMPLEMENTED_QUANTITY
, REC.QUANTITY_IN_PROCESS
, REC.PLANNING_GROUP
, REC.IMPLEMENT_BUILD_SEQUENCE
, REC.IMPLEMENT_ALTERNATE_BOM
, REC.IMPLEMENT_ALTERNATE_ROUTING
, REC.IMPLEMENT_DEMAND_CLASS
, WSG.SCHEDULE_GROUP_NAME
, '_LA:REC.IMPLEMENT_FIRM:MFG_LOOKUPS:SYS_YES_NO:MEANING'
, MRP_GET_PROJECT.PROJECT(REC.IMPLEMENT_PROJECT_ID)
, MRP_GET_PROJECT.TASK(REC.IMPLEMENT_TASK_ID)
, PA3.ORGANIZATION_CODE
, ORG3.NAME
, VEN2.VENDOR_NAME
, SITE2.VENDOR_SITE_CODE
, LINE2.LINE_CODE
, REC.IMPLEMENT_WIP_CLASS_CODE
, REC.END_ITEM_UNIT_NUMBER
, REC.IMPLEMENT_END_ITEM_UNIT_NUMBER
, REC.TRANSACTION_ID
, MRPITEM.ORGANIZATION_ID
, REC.PROJECT_ID
, REC.TASK_ID
, REC.SOURCE_ORGANIZATION_ID
, REC.SOURCE_VENDOR_ID
, REC.SOURCE_VENDOR_SITE_ID
, REC.IMPLEMENT_PROJECT_ID
, REC.IMPLEMENT_TASK_ID
, REC.IMPLEMENT_SCHEDULE_GROUP_ID
, REC.IMPLEMENT_SOURCE_ORG_ID
, REC.IMPLEMENT_VENDOR_ID
, REC.IMPLEMENT_VENDOR_SITE_ID
, REC.IMPLEMENT_LINE_ID
, REC.INVENTORY_ITEM_ID
, REC.LINE_ID
, DECODE(REC.ORDER_TYPE
, 1
, IPO.LINE_ID
, 8
, IPO.LINE_ID
, TO_NUMBER(NULL))
, DECODE(REC.ORDER_TYPE
, 11
, IPO.LINE_ID
, 12
, IPO.LINE_ID
, TO_NUMBER(NULL))
, DECODE(REC.ORDER_TYPE
, 2
, IPO.LINE_ID
, TO_NUMBER(NULL))
, DECODE(REC.ORDER_TYPE
, 3
, IWE.WIP_ENTITY_ID
, 7
, IWE.WIP_ENTITY_ID
, 14
, IWE.WIP_ENTITY_ID
, 15
, IWE.WIP_ENTITY_ID
, 27
, IWE.WIP_ENTITY_ID
, 28
, IWE.WIP_ENTITY_ID
, TO_NUMBER(NULL))
, WSG2.SCHEDULE_GROUP_ID
, REC.LAST_UPDATE_DATE
, REC.LAST_UPDATED_BY
, REC.CREATION_DATE
, REC.CREATED_BY
FROM WIP_SCHEDULE_GROUPS WSG
, WIP_SCHEDULE_GROUPS WSG2
, WIP_LINES LINE
, WIP_LINES LINE2
, PO_VENDORS VEN
, PO_VENDORS VEN2
, PO_VENDOR_SITES_ALL SITE
, PO_VENDOR_SITES_ALL SITE2
, MTL_PARAMETERS PA
, MTL_PARAMETERS PA2
, MTL_PARAMETERS PA3
, HR_ALL_ORGANIZATION_UNITS ORG
, HR_ALL_ORGANIZATION_UNITS ORG2
, HR_ALL_ORGANIZATION_UNITS ORG3
, MFG_LOOKUPS LKUP
, MRP_ITEM_WIP_ENTITIES IWE
, MRP_ITEM_PURCHASE_ORDERS IPO
, MRP_RECOMMENDATIONS REC
, MTL_SYSTEM_ITEMS SYSITEM
, MRP_SYSTEM_ITEMS MRPITEM
, MRP_PLAN_ORGANIZATIONS PLANORG
, MRP_PLANS PLAN
WHERE WSG.SCHEDULE_GROUP_ID(+) = REC.IMPLEMENT_SCHEDULE_GROUP_ID
AND WSG2.SCHEDULE_GROUP_ID(+) = IWE.SCHEDULE_GROUP_ID
AND LINE.LINE_ID(+) = REC.LINE_ID
AND LINE2.LINE_ID(+) = REC.IMPLEMENT_LINE_ID
AND VEN.VENDOR_ID(+) = REC.SOURCE_VENDOR_ID
AND VEN2.VENDOR_ID(+) = REC.IMPLEMENT_VENDOR_ID
AND SITE.VENDOR_SITE_ID(+) = REC.SOURCE_VENDOR_SITE_ID
AND SITE2.VENDOR_SITE_ID(+) = REC.IMPLEMENT_VENDOR_SITE_ID
AND PA.ORGANIZATION_ID(+) = REC.SOURCE_ORGANIZATION_ID
AND ORG.ORGANIZATION_ID(+) = REC.SOURCE_ORGANIZATION_ID
AND PA2.ORGANIZATION_ID = REC.ORGANIZATION_ID
AND ORG2.ORGANIZATION_ID(+) = REC.ORGANIZATION_ID
AND PA3.ORGANIZATION_ID(+) = REC.IMPLEMENT_SOURCE_ORG_ID
AND ORG3.ORGANIZATION_ID(+) = REC.IMPLEMENT_SOURCE_ORG_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 = MRPITEM.COMPILE_DESIGNATOR
AND REC.ORGANIZATION_ID = MRPITEM.ORGANIZATION_ID
AND REC.INVENTORY_ITEM_ID = MRPITEM.INVENTORY_ITEM_ID
AND SYSITEM.ORGANIZATION_ID = MRPITEM.ORGANIZATION_ID
AND SYSITEM.INVENTORY_ITEM_ID = MRPITEM.INVENTORY_ITEM_ID
AND LKUP.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND LKUP.LOOKUP_CODE = REC.ORDER_TYPE
AND REC.ORDER_TYPE != 4
AND MRPITEM.ORGANIZATION_ID = NVL(PLANORG.PLANNED_ORGANIZATION
, PLAN.ORGANIZATION_ID)
AND MRPITEM.COMPILE_DESIGNATOR = PLAN.COMPILE_DESIGNATOR
AND PLANORG.PLAN_LEVEL(+) = 2
AND PLANORG.COMPILE_DESIGNATOR(+) = PLAN.COMPILE_DESIGNATOR
AND PLANORG.ORGANIZATION_ID(+) = PLAN.ORGANIZATION_ID
AND '_SEC:MRPITEM.ORGANIZATION_ID' IS NOT NULL WITH READ ONLY

Columns