View: MTL_SHORT_SUMMARY_V

Product:	INV - Inventory
Description:	Summary view of the material shortages temp table
Implementation/DBA Data:	APPS.MTL_SHORT_SUMMARY_V

View Text

SELECT MSCT.ROWID
, MSCT.SEQ_NUM
, MSCT.ORGANIZATION_ID
, MSCT.OBJECT_TYPE
, ML.MEANING
, MSCT.INVENTORY_ITEM_ID
, MSIK1.CONCATENATED_SEGMENTS
, MSIK1.DESCRIPTION
, MSIK1.PLANNER_CODE
, MSCT.UOM_CODE
, MSCT.QUANTITY_OPEN
, MSCT.OBJECT_ID
, WE.WIP_ENTITY_NAME
, DECODE(SIGN(MSCT.OBJECT_DETAIL_ID)
, 1
, MSCT.OBJECT_DETAIL_ID
, TO_NUMBER(NULL))
, DECODE(SIGN(MSCT.OBJECT_DETAIL_ID)
, 1
, TO_CHAR(MSCT.OBJECT_DETAIL_ID)
, NULL)
, NULL
, MSIK2.CONCATENATED_SEGMENTS
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, WDJ.STATUS_TYPE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, WE.ENTITY_TYPE
, MSCT.LAST_UPDATED_BY
, MSCT.LAST_UPDATE_LOGIN
, MSCT.LAST_UPDATE_DATE
, MSCT.CREATED_BY
, MSCT.CREATION_DATE
FROM MFG_LOOKUPS ML
, MTL_SHORT_CHK_TEMP MSCT
, MTL_SYSTEM_ITEMS_KFV MSIK1
, MTL_SYSTEM_ITEMS_KFV MSIK2
, WIP_ENTITIES WE
, WIP_DISCRETE_JOBS WDJ
WHERE MSCT.OBJECT_ID = WE.WIP_ENTITY_ID
AND MSCT.OBJECT_ID = WDJ.WIP_ENTITY_ID
AND MSIK1.ORGANIZATION_ID = MSCT.ORGANIZATION_ID
AND MSIK1.INVENTORY_ITEM_ID = MSCT.INVENTORY_ITEM_ID
AND MSIK2.ORGANIZATION_ID (+) = WE.ORGANIZATION_ID
AND MSIK2.INVENTORY_ITEM_ID (+) = WE.PRIMARY_ITEM_ID
AND ML.LOOKUP_TYPE = 'MTL_SHORTAGE_OBJECT_TYPE'
AND ML.LOOKUP_CODE = MSCT.OBJECT_TYPE
AND MSCT.OBJECT_TYPE = 1 UNION ALL SELECT MSCT.ROWID
, MSCT.SEQ_NUM
, MSCT.ORGANIZATION_ID
, MSCT.OBJECT_TYPE
, ML.MEANING
, MSCT.INVENTORY_ITEM_ID
, MSIK1.CONCATENATED_SEGMENTS
, MSIK1.DESCRIPTION
, MSIK1.PLANNER_CODE
, MSCT.UOM_CODE
, MSCT.QUANTITY_OPEN
, MSCT.OBJECT_ID
, WE.WIP_ENTITY_NAME
, DECODE(SIGN(MSCT.OBJECT_DETAIL_ID)
, 1
, MSCT.OBJECT_DETAIL_ID
, TO_NUMBER(NULL))
, DECODE(SIGN(MSCT.OBJECT_DETAIL_ID)
, 1
, TO_CHAR(MSCT.OBJECT_DETAIL_ID)
, NULL)
, WL.LINE_CODE
, MSIK2.CONCATENATED_SEGMENTS
, WRS.FIRST_UNIT_START_DATE
, WRS.LAST_UNIT_COMPLETION_DATE
, WRS.STATUS_TYPE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, WE.ENTITY_TYPE
, MSCT.LAST_UPDATED_BY
, MSCT.LAST_UPDATE_LOGIN
, MSCT.LAST_UPDATE_DATE
, MSCT.CREATED_BY
, MSCT.CREATION_DATE
FROM MFG_LOOKUPS ML
, MTL_SHORT_CHK_TEMP MSCT
, MTL_SYSTEM_ITEMS_KFV MSIK1
, MTL_SYSTEM_ITEMS_KFV MSIK2
, WIP_ENTITIES WE
, WIP_LINES WL
, WIP_REPETITIVE_SCHEDULES WRS
WHERE MSCT.OBJECT_ID = WRS.REPETITIVE_SCHEDULE_ID
AND WRS.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WRS.LINE_ID = WL.LINE_ID
AND MSIK1.ORGANIZATION_ID = MSCT.ORGANIZATION_ID
AND MSIK1.INVENTORY_ITEM_ID = MSCT.INVENTORY_ITEM_ID
AND MSIK2.ORGANIZATION_ID (+) = WE.ORGANIZATION_ID
AND MSIK2.INVENTORY_ITEM_ID (+) = WE.PRIMARY_ITEM_ID
AND ML.LOOKUP_TYPE = 'MTL_SHORTAGE_OBJECT_TYPE'
AND ML.LOOKUP_CODE = MSCT.OBJECT_TYPE
AND MSCT.OBJECT_TYPE = 2 UNION ALL SELECT MSCT.ROWID
, MSCT.SEQ_NUM
, MSCT.ORGANIZATION_ID
, MSCT.OBJECT_TYPE
, ML.MEANING
, MSCT.INVENTORY_ITEM_ID
, MSIK.CONCATENATED_SEGMENTS
, MSIK.DESCRIPTION
, MSIK.PLANNER_CODE
, MSCT.UOM_CODE
, MSCT.QUANTITY_OPEN
, MSCT.OBJECT_ID
, MSOK.CONCATENATED_SEGMENTS
, MSCT.OBJECT_DETAIL_ID
, TO_CHAR(SL.LINE_NUMBER)
, NULL
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, SH.CUSTOMER_ID
, SH.ORDER_TYPE_ID
, TO_NUMBER(NULL)
, MSCT.LAST_UPDATED_BY
, MSCT.LAST_UPDATE_LOGIN
, MSCT.LAST_UPDATE_DATE
, MSCT.CREATED_BY
, MSCT.CREATION_DATE
FROM MFG_LOOKUPS ML
, MTL_SHORT_CHK_TEMP MSCT
, MTL_SYSTEM_ITEMS_KFV MSIK
, MTL_SALES_ORDERS_KFV MSOK
, MTL_SALES_ORDERS MSO
, SO_HEADERS_ALL SH
, SO_LINES_ALL SL
WHERE OE_INSTALL.GET_ACTIVE_PRODUCT = 'OE'
AND MSCT.OBJECT_ID = SH.HEADER_ID
AND MSCT.OBJECT_DETAIL_ID = SL.LINE_ID
AND MSOK.SALES_ORDER_ID = MSO.SALES_ORDER_ID
AND MSO.SEGMENT1 = TO_CHAR(SH.ORDER_NUMBER)
AND MSIK.ORGANIZATION_ID = MSCT.ORGANIZATION_ID
AND MSIK.INVENTORY_ITEM_ID = MSCT.INVENTORY_ITEM_ID
AND ML.LOOKUP_TYPE = 'MTL_SHORTAGE_OBJECT_TYPE'
AND ML.LOOKUP_CODE = MSCT.OBJECT_TYPE
AND MSCT.OBJECT_TYPE = 4 UNION ALL SELECT MSCT.ROWID
, MSCT.SEQ_NUM
, MSCT.ORGANIZATION_ID
, MSCT.OBJECT_TYPE
, ML.MEANING
, MSCT.INVENTORY_ITEM_ID
, MSIK.CONCATENATED_SEGMENTS
, MSIK.DESCRIPTION
, MSIK.PLANNER_CODE
, MSCT.UOM_CODE
, MSCT.QUANTITY_OPEN
, MSCT.OBJECT_ID
, MSOK.CONCATENATED_SEGMENTS
, MSCT.OBJECT_DETAIL_ID
, TO_CHAR(SL.LINE_NUMBER)
, NULL
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, SH.SOLD_TO_ORG_ID
, SH.ORDER_TYPE_ID
, TO_NUMBER(NULL)
, MSCT.LAST_UPDATED_BY
, MSCT.LAST_UPDATE_LOGIN
, MSCT.LAST_UPDATE_DATE
, MSCT.CREATED_BY
, MSCT.CREATION_DATE
FROM MFG_LOOKUPS ML
, MTL_SHORT_CHK_TEMP MSCT
, MTL_SYSTEM_ITEMS_KFV MSIK
, MTL_SALES_ORDERS_KFV MSOK
, MTL_SALES_ORDERS MSO
, OE_ORDER_HEADERS_ALL SH
, OE_ORDER_LINES_ALL SL
WHERE OE_INSTALL.GET_ACTIVE_PRODUCT = 'ONT'
AND MSCT.OBJECT_ID = SH.HEADER_ID
AND MSCT.OBJECT_DETAIL_ID = SL.LINE_ID
AND MSOK.SALES_ORDER_ID = MSO.SALES_ORDER_ID
AND MSO.SEGMENT1 = TO_CHAR(SH.ORDER_NUMBER)
AND MSO.SEGMENT2 = (SELECT NAME
FROM OE_TRANSACTION_TYPES_TL
WHERE TRANSACTION_TYPE_ID = SH.ORDER_TYPE_ID
AND LANGUAGE = (SELECT LANGUAGE_CODE
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG = 'B'))
AND MSO.SEGMENT3 = FND_PROFILE.VALUE('ONT_SOURCE_CODE')
AND MSIK.ORGANIZATION_ID = MSCT.ORGANIZATION_ID
AND MSIK.INVENTORY_ITEM_ID = MSCT.INVENTORY_ITEM_ID
AND ML.LOOKUP_TYPE = 'MTL_SHORTAGE_OBJECT_TYPE'
AND ML.LOOKUP_CODE = MSCT.OBJECT_TYPE
AND MSCT.OBJECT_TYPE = 4

Columns