View: AHL_OSP_ORDER_LINES_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	This View queries all the inforamtion related to ORDER LINES. The information is retrieved from the table AHL_OSP_ORDER_LINES and the view AHL_WORKORDERS_V.
Implementation/DBA Data:	APPS.AHL_OSP_ORDER_LINES_V

View Text

SELECT LI.OSP_ORDER_LINE_ID
, LI.OBJECT_VERSION_NUMBER
, LI.LAST_UPDATE_DATE
, LI.LAST_UPDATED_BY
, LI.CREATION_DATE
, LI.CREATED_BY
, LI.LAST_UPDATE_LOGIN
, LI.OSP_ORDER_ID
, LI.OSP_LINE_NUMBER
, LI.STATUS_CODE
, (SELECT MEANING
FROM FND_LOOKUP_VALUES_VL FND
WHERE FND.LOOKUP_TYPE = 'AHL_OSP_LINE_STATUS_TYPE'
AND FND.LOOKUP_CODE = LI.STATUS_CODE) STATUS
, LI.PO_LINE_TYPE_ID
, (SELECT PO.LINE_TYPE
FROM PO_LINE_TYPES PO
WHERE LI.PO_LINE_TYPE_ID = PO.LINE_TYPE_ID) PO_LINE_TYPE
, LI.SERVICE_ITEM_ID
, LI.SERVICE_ITEM_DESCRIPTION
, LI.SERVICE_ITEM_UOM_CODE
, LI.NEED_BY_DATE
, LI.SHIP_BY_DATE
, LI.PO_LINE_ID
, LI.OE_SHIP_LINE_ID
, LI.OE_RETURN_LINE_ID
, LI.WORKORDER_ID
, LI.OPERATION_ID
, LI.SECURITY_GROUP_ID
, LI.ATTRIBUTE_CATEGORY
, LI.ATTRIBUTE1
, LI.ATTRIBUTE2
, LI.ATTRIBUTE3
, LI.ATTRIBUTE4
, LI.ATTRIBUTE5
, LI.ATTRIBUTE6
, LI.ATTRIBUTE7
, LI.ATTRIBUTE8
, LI.ATTRIBUTE9
, LI.ATTRIBUTE10
, LI.ATTRIBUTE11
, LI.ATTRIBUTE12
, LI.ATTRIBUTE13
, LI.ATTRIBUTE14
, LI.ATTRIBUTE15
, WO.JOB_NUMBER
, (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = LI.SERVICE_ITEM_ID
AND ORGANIZATION_ID = WO.ORGANIZATION_ID) SERVICE_ITEM_NUMBER
, LI.QUANTITY
, WO.WO_PART_NUMBER
, WO.SERIAL_NUMBER
, WO.ITEM_INSTANCE_ID
, WO.ITEM_INSTANCE_NUMBER
, WO.PROJECT_ID
, WO.PROJECT_NAME
, WO.PROJECT_TASK_ID
, WO.PROJECT_TASK_NAME
, WO.SERVICE_ITEM_ID
, LI.EXCHANGE_INSTANCE_ID
, CSI.INSTANCE_NUMBER
, CSI.INVENTORY_ITEM_ID
, MTLI.CONCATENATED_SEGMENTS
, CSI.SERIAL_NUMBER
FROM AHL_OSP_ORDER_LINES LI
, AHL_WORKORDERS_OSP_V WO
, CSI_ITEM_INSTANCES CSI
, MTL_SYSTEM_ITEMS_KFV MTLI
WHERE LI.WORKORDER_ID = WO.WORKORDER_ID
AND (LI.PO_LINE_ID IS NULL OR NVL(LI.STATUS_CODE
, 'ENTERED') = 'PO_DELETED')
AND CSI.INSTANCE_ID (+) = LI.EXCHANGE_INSTANCE_ID
AND CSI.INVENTORY_ITEM_ID = MTLI.INVENTORY_ITEM_ID (+)
AND CSI.INV_MASTER_ORGANIZATION_ID = MTLI.ORGANIZATION_ID (+) UNION ALL SELECT LI.OSP_ORDER_LINE_ID
, LI.OBJECT_VERSION_NUMBER
, LI.LAST_UPDATE_DATE
, LI.LAST_UPDATED_BY
, LI.CREATION_DATE
, LI.CREATED_BY
, LI.LAST_UPDATE_LOGIN
, LI.OSP_ORDER_ID
, LI.OSP_LINE_NUMBER
, LI.STATUS_CODE
, (SELECT MEANING
FROM FND_LOOKUP_VALUES_VL FND
WHERE FND.LOOKUP_TYPE = 'AHL_OSP_LINE_STATUS_TYPE'
AND FND.LOOKUP_CODE = LI.STATUS_CODE) STATUS
, LI.PO_LINE_TYPE_ID
, (SELECT PO.LINE_TYPE
FROM PO_LINE_TYPES PO
WHERE LI.PO_LINE_TYPE_ID = PO.LINE_TYPE_ID) PO_LINE_TYPE
, LI.SERVICE_ITEM_ID
, LI.SERVICE_ITEM_DESCRIPTION
, MTL_UOM.UOM_CODE
, LI.NEED_BY_DATE
, LI.SHIP_BY_DATE
, LI.PO_LINE_ID
, LI.OE_SHIP_LINE_ID
, LI.OE_RETURN_LINE_ID
, LI.WORKORDER_ID
, LI.OPERATION_ID
, LI.SECURITY_GROUP_ID
, LI.ATTRIBUTE_CATEGORY
, LI.ATTRIBUTE1
, LI.ATTRIBUTE2
, LI.ATTRIBUTE3
, LI.ATTRIBUTE4
, LI.ATTRIBUTE5
, LI.ATTRIBUTE6
, LI.ATTRIBUTE7
, LI.ATTRIBUTE8
, LI.ATTRIBUTE9
, LI.ATTRIBUTE10
, LI.ATTRIBUTE11
, LI.ATTRIBUTE12
, LI.ATTRIBUTE13
, LI.ATTRIBUTE14
, LI.ATTRIBUTE15
, WO.JOB_NUMBER
, (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = LI.SERVICE_ITEM_ID
AND ORGANIZATION_ID = WO.ORGANIZATION_ID) SERVICE_ITEM_NUMBER
, PL.QUANTITY
, WO.WO_PART_NUMBER
, WO.SERIAL_NUMBER
, WO.ITEM_INSTANCE_ID
, WO.ITEM_INSTANCE_NUMBER
, WO.PROJECT_ID
, WO.PROJECT_NAME
, WO.PROJECT_TASK_ID
, WO.PROJECT_TASK_NAME
, WO.SERVICE_ITEM_ID
, LI.EXCHANGE_INSTANCE_ID
, CSI.INSTANCE_NUMBER
, CSI.INVENTORY_ITEM_ID
, MTLI.CONCATENATED_SEGMENTS
, CSI.SERIAL_NUMBER
FROM AHL_OSP_ORDER_LINES LI
, AHL_WORKORDERS_OSP_V WO
, PO_LINES_ALL PL
, MTL_UNITS_OF_MEASURE_VL MTL_UOM
, CSI_ITEM_INSTANCES CSI
, MTL_SYSTEM_ITEMS_KFV MTLI
WHERE LI.WORKORDER_ID = WO.WORKORDER_ID
AND LI.PO_LINE_ID IS NOT NULL
AND NVL(LI.STATUS_CODE
, 'ENTERED') <> 'PO_DELETED'
AND LI.PO_LINE_ID = PL.PO_LINE_ID
AND PL.UNIT_MEAS_LOOKUP_CODE = MTL_UOM.UNIT_OF_MEASURE
AND CSI.INSTANCE_ID (+) = LI.EXCHANGE_INSTANCE_ID
AND CSI.INVENTORY_ITEM_ID = MTLI.INVENTORY_ITEM_ID (+)
AND CSI.INV_MASTER_ORGANIZATION_ID = MTLI.ORGANIZATION_ID (+)

Columns