VIEW: APPS.MTL_SHORT_SUMMARY_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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