View: EAM_DIRECT_ITEMS_RECS_LITE_V

Product:	EAM - Enterprise Asset Management
Description:	This is a light weight version og EAM_DIRECT_ITEMS_RECS_V
Implementation/DBA Data:	APPS.EAM_DIRECT_ITEMS_RECS_LITE_V

View Text

SELECT EWODI.DIRECT_ITEM_TYPE
, EWODI.DIRECT_ITEM_TYPE_ID
, EWODI.OPERATION_SEQ_NUM
, MC.CONCATENATED_SEGMENTS
, EWODI.CATEGORY_ID
, EWODI.ITEM_NAME
, EWODI.ITEM_DESCRIPTION
, EWODI.UOM_CODE
, EWODI.WIP_ENTITY_ID
, EWODI.ORGANIZATION_ID
, NVL(EWODI.UNIT_PRICE
, EWODI.AMOUNT) AS UNIT_PRICE
, EWODI.SUPPLIER_NAME
, NVL(EWODI.QUANTITY_REQUIRED
, 0) REQUIRED_QUANTITY
, EWODI.NEED_BY_DATE
, EWODI.AUTO_REQUEST_MATERIAL
, EWODI.ITEM_ID
, EWODI.DIRECT_ITEM_SEQUENCE_ID
, NVL(EWODI.QUANTITY_RECEIVED
, 0) QUANTITY_RECEIVED
, EWODI.DEPARTMENT_ID
, NVL(EWODI.RQL_QUANTITY_ORDERED
, 0)-NVL(EWODI.RQL_QUANTITY_CANCELLED
, 0) RQL_QUANTITY_ORDERED
, NVL(EWODI.PO_QUANTITY_ORDERED
, 0) -NVL(EWODI.PO_QUANTITY_CANCELLED
, 0) PO_QUANTITY_ORDERED
, NVL(EWODI.PO_QUANTITY_CANCELLED
, 0) PO_QUANTITY_CANCELLED
, EWODI.EFFECTIVITY_CONTROL
, EWODI.EAM_ITEM_TYPE
, EWODI.ORDER_TYPE_LOOKUP_CODE
, EWODI.AMOUNT
, EWODI.AMOUNT_DELIVERED
, EWODI.RQL_AMOUNT_ORDERED
, EWODI.PO_AMOUNT_ORDERED
, DECODE(EWODI.ORDER_TYPE_LOOKUP_CODE
, 'FIXED PRICE'
, 2
, 1) SERVICE_LINE_TYPE_ID
, ML.MEANING
, EWODI.ATTRIBUTE_CATEGORY
, EWODI.ATTRIBUTE1
, EWODI.ATTRIBUTE2
, EWODI.ATTRIBUTE3
, EWODI.ATTRIBUTE4
, EWODI.ATTRIBUTE5
, EWODI.ATTRIBUTE6
, EWODI.ATTRIBUTE7
, EWODI.ATTRIBUTE8
, EWODI.ATTRIBUTE9
, EWODI.ATTRIBUTE10
, EWODI.ATTRIBUTE11
, EWODI.ATTRIBUTE12
, EWODI.ATTRIBUTE13
, EWODI.ATTRIBUTE14
, EWODI.ATTRIBUTE15
FROM EAM_WO_DIRECT_ITEMS_LITE_V EWODI
, MFG_LOOKUPS ML
, MTL_CATEGORIES_KFV MC
WHERE EWODI.CATEGORY_ID = MC.CATEGORY_ID
AND DECODE(EWODI.ORDER_TYPE_LOOKUP_CODE
, 'FIXED PRICE'
, 2
, 1) = ML.LOOKUP_CODE(+)
AND ML.LOOKUP_TYPE(+) = 'EAM_SERVICE_LINE_TYPE' UNION ALL SELECT ML.MEANING
, DECODE(EWORP.ITEM_ID
, NULL
, 1
, 2)
, EWORP.OPERATION_SEQ_NUM
, MC.CONCATENATED_SEGMENTS
, EWORP.CATEGORY_ID
, MSIK.CONCATENATED_SEGMENTS
, DECODE(EWORP.ITEM_ID
, NULL
, EWORP.ITEM_DESCRIPTION
, MSIK.DESCRIPTION)
, EWORP.UOM_CODE
, EWORP.WIP_ENTITY_ID
, EWORP.ORGANIZATION_ID
, NVL(EWORP.UNIT_PRICE
, EWORP.AMOUNT) AS UNIT_PRICE
, EWORP.VENDOR_NAME
, NVL( NVL(EWORP.PO_QUANTITY_ORDERED
, EWORP.RQL_QUANTITY_ORDERED)
, 0)
, EWORP.NEED_BY_DATE
, 'N'
, EWORP.ITEM_ID
, EWORP.DIRECT_ITEM_SEQUENCE_ID AS DIRECT_ITEM_SEQUENCE_ID
, NVL(EWORP.QUANTITY_RECEIVED
, 0)
, TO_NUMBER(NULL)
, NVL(EWORP.RQL_QUANTITY_ORDERED
, 0)-NVL(EWORP.RQL_QUANTITY_CANCELLED
, 0) RQL_QUANTITY_ORDERED
, NVL(EWORP.PO_QUANTITY_ORDERED
, 0)-NVL(EWORP.PO_QUANTITY_CANCELLED
, 0) PO_QUANTITY_ORDERED
, NVL(EWORP.PO_QUANTITY_CANCELLED
, 0)
, MSIK.EFFECTIVITY_CONTROL
, MSIK.EAM_ITEM_TYPE
, EWORP.ORDER_TYPE_LOOKUP_CODE
, EWORP.AMOUNT
, EWORP.AMOUNT_DELIVERED
, EWORP.RQL_AMOUNT_ORDERED
, EWORP.PO_AMOUNT_ORDERED
, DECODE(EWORP.ORDER_TYPE_LOOKUP_CODE
, 'FIXED PRICE'
, 2
, 1)
, ML1.MEANING
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM EAM_WO_REQ_PO_LITE_V EWORP
, MFG_LOOKUPS ML1
, MTL_SYSTEM_ITEMS_B_KFV MSIK
, MTL_CATEGORIES_KFV MC
, MFG_LOOKUPS ML
WHERE EWORP.DIRECT_ITEM_SEQUENCE_ID IS NULL
AND DECODE(EWORP.ORDER_TYPE_LOOKUP_CODE
, 'FIXED PRICE'
, 2
, 1) = ML1.LOOKUP_CODE(+)
AND ML1.LOOKUP_TYPE(+) = 'EAM_SERVICE_LINE_TYPE'
AND NVL(EWORP.QUANTITY_RECEIVED
, -1) <= 0
AND NVL(EWORP.AMOUNT_DELIVERED
, -1) <= 0
AND ( EWORP.ITEM_ID IS NULL OR NOT EXISTS (SELECT 1
FROM WIP_REQUIREMENT_OPERATIONS WRO
WHERE WRO.WIP_ENTITY_ID = EWORP.WIP_ENTITY_ID
AND WRO.OPERATION_SEQ_NUM = EWORP.OPERATION_SEQ_NUM
AND WRO.INVENTORY_ITEM_ID = EWORP.ITEM_ID ) )
AND EWORP.ITEM_ID = MSIK.INVENTORY_ITEM_ID (+)
AND EWORP.ORGANIZATION_ID = MSIK.ORGANIZATION_ID (+)
AND EWORP.CATEGORY_ID = MC.CATEGORY_ID
AND ML.LOOKUP_TYPE ='EAM_DIRECT_ITEM_TYPE'
AND ML.LOOKUP_CODE =DECODE(EWORP.ITEM_ID
, NULL
, 1
, 2)

Columns