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.

[Home] [Help]

VIEW: APPS.EAM_DIRECT_ITEMS_RECS_LITE_V

Source

View Text - Preformatted


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 , 0 AS REQUIRED_QUANTITY , 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) 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 OR NOT EXISTS (SELECT 1 FROM wip_eam_direct_items wed WHERE wed.wip_entity_id = eworp.wip_entity_id AND wed.operation_seq_num = eworp.operation_seq_num AND wed.direct_item_sequence_id = eworp.direct_item_sequence_id ) ) 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)

View Text - HTML Formatted

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
, 0 AS REQUIRED_QUANTITY
, 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) 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 OR NOT EXISTS (SELECT 1
FROM WIP_EAM_DIRECT_ITEMS WED
WHERE WED.WIP_ENTITY_ID = EWORP.WIP_ENTITY_ID
AND WED.OPERATION_SEQ_NUM = EWORP.OPERATION_SEQ_NUM
AND WED.DIRECT_ITEM_SEQUENCE_ID = EWORP.DIRECT_ITEM_SEQUENCE_ID ) )
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)