VIEW: APPS.AHL_OSP_ORDER_LINES_V

Source

View Text - Preformatted

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, (SELECT osp_order_number FROM ahl_osp_orders_b H WHERE H.osp_order_id = 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.NEED_BY_DATE, LI.SHIP_BY_DATE, LI.PO_LINE_ID, 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.OE_SHIP_LINE_ID, LI.OE_RETURN_LINE_ID, LI.SERVICE_ITEM_ID, LI.SERVICE_ITEM_DESCRIPTION, (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.SERVICE_ITEM_UOM_CODE, LI.QUANTITY, LI.WORKORDER_ID, LI.OPERATION_ID, WO.JOB_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, NULL sub_inventory, WO.INVENTORY_ITEM_ID, WO.ORGANIZATION_ID, WO.WO_PART_NUMBER, WO.item_description, WO.SERIAL_NUMBER, WO.LOT_NUMBER, WO.ITEM_INSTANCE_UOM, WO.QUANTITY, LI.EXCHANGE_INSTANCE_ID, CSI.INSTANCE_NUMBER, CSI.INVENTORY_ITEM_ID, mtli.CONCATENATED_SEGMENTS, CSI.SERIAL_NUMBER, LI.PO_REQ_LINE_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 FROM AHL_OSP_ORDER_LINES LI, AHL_WORKORDERS_OSP_V WO, CSI_ITEM_INSTANCES CSI, mtl_system_items_kfv mtli WHERE LI.WORKORDER_ID IS NOT NULL AND LI.WORKORDER_ID = WO.WORKORDER_ID 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, (SELECT osp_order_number FROM ahl_osp_orders_b H WHERE H.osp_order_id = 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.NEED_BY_DATE, LI.SHIP_BY_DATE, LI.PO_LINE_ID, 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.OE_SHIP_LINE_ID, LI.OE_RETURN_LINE_ID, LI.SERVICE_ITEM_ID, LI.SERVICE_ITEM_DESCRIPTION, (select concatenated_segments from mtl_system_items_kfv where inventory_item_id = LI.service_item_id and organization_id = LI.inventory_org_id) SERVICE_ITEM_NUMBER, LI.SERVICE_ITEM_UOM_CODE, LI.QUANTITY, LI.WORKORDER_ID, LI.OPERATION_ID, NULL job_number, decode(mtl.comms_nl_trackable_flag, 'Y', csi1.instance_id, null), decode(mtl.comms_nl_trackable_flag, 'Y', csi1.instance_number, null), NULL, NULL, NULL, NULL, NULL, LI.sub_inventory, LI.INVENTORY_ITEM_ID, LI.INVENTORY_ORG_ID, mtl.concatenated_segments, mtl.description, LI.SERIAL_NUMBER, LI.LOT_NUMBER, LI.INVENTORY_ITEM_UOM, LI.INVENTORY_ITEM_QUANTITY, LI.EXCHANGE_INSTANCE_ID, CSI.INSTANCE_NUMBER, CSI.INVENTORY_ITEM_ID, mtli.CONCATENATED_SEGMENTS, CSI.SERIAL_NUMBER, LI.PO_REQ_LINE_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 FROM AHL_OSP_ORDER_LINES LI, mtl_system_items_kfv mtl, CSI_ITEM_INSTANCES CSI, mtl_system_items_kfv mtli, CSI_ITEM_INSTANCES CSI1 WHERE LI.WORKORDER_ID IS NULL AND LI.INVENTORY_ITEM_ID = mtl.INVENTORY_ITEM_ID (+) AND LI.INVENTORY_ORG_ID = mtl.organization_id (+) 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 (+) AND csi1.last_vld_organization_id (+) = li.inventory_org_id AND csi1.inventory_item_id (+) = li.inventory_item_id And csi1.serial_number (+) = li.serial_number

View Text - HTML Formatted

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
, (SELECT OSP_ORDER_NUMBER
FROM AHL_OSP_ORDERS_B H
WHERE H.OSP_ORDER_ID = 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.NEED_BY_DATE
, LI.SHIP_BY_DATE
, LI.PO_LINE_ID
, 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.OE_SHIP_LINE_ID
, LI.OE_RETURN_LINE_ID
, LI.SERVICE_ITEM_ID
, LI.SERVICE_ITEM_DESCRIPTION
, (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.SERVICE_ITEM_UOM_CODE
, LI.QUANTITY
, LI.WORKORDER_ID
, LI.OPERATION_ID
, WO.JOB_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
, NULL SUB_INVENTORY
, WO.INVENTORY_ITEM_ID
, WO.ORGANIZATION_ID
, WO.WO_PART_NUMBER
, WO.ITEM_DESCRIPTION
, WO.SERIAL_NUMBER
, WO.LOT_NUMBER
, WO.ITEM_INSTANCE_UOM
, WO.QUANTITY
, LI.EXCHANGE_INSTANCE_ID
, CSI.INSTANCE_NUMBER
, CSI.INVENTORY_ITEM_ID
, MTLI.CONCATENATED_SEGMENTS
, CSI.SERIAL_NUMBER
, LI.PO_REQ_LINE_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
FROM AHL_OSP_ORDER_LINES LI
, AHL_WORKORDERS_OSP_V WO
, CSI_ITEM_INSTANCES CSI
, MTL_SYSTEM_ITEMS_KFV MTLI
WHERE LI.WORKORDER_ID IS NOT NULL
AND LI.WORKORDER_ID = WO.WORKORDER_ID
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
, (SELECT OSP_ORDER_NUMBER
FROM AHL_OSP_ORDERS_B H
WHERE H.OSP_ORDER_ID = 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.NEED_BY_DATE
, LI.SHIP_BY_DATE
, LI.PO_LINE_ID
, 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.OE_SHIP_LINE_ID
, LI.OE_RETURN_LINE_ID
, LI.SERVICE_ITEM_ID
, LI.SERVICE_ITEM_DESCRIPTION
, (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = LI.SERVICE_ITEM_ID
AND ORGANIZATION_ID = LI.INVENTORY_ORG_ID) SERVICE_ITEM_NUMBER
, LI.SERVICE_ITEM_UOM_CODE
, LI.QUANTITY
, LI.WORKORDER_ID
, LI.OPERATION_ID
, NULL JOB_NUMBER
, DECODE(MTL.COMMS_NL_TRACKABLE_FLAG
, 'Y'
, CSI1.INSTANCE_ID
, NULL)
, DECODE(MTL.COMMS_NL_TRACKABLE_FLAG
, 'Y'
, CSI1.INSTANCE_NUMBER
, NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, LI.SUB_INVENTORY
, LI.INVENTORY_ITEM_ID
, LI.INVENTORY_ORG_ID
, MTL.CONCATENATED_SEGMENTS
, MTL.DESCRIPTION
, LI.SERIAL_NUMBER
, LI.LOT_NUMBER
, LI.INVENTORY_ITEM_UOM
, LI.INVENTORY_ITEM_QUANTITY
, LI.EXCHANGE_INSTANCE_ID
, CSI.INSTANCE_NUMBER
, CSI.INVENTORY_ITEM_ID
, MTLI.CONCATENATED_SEGMENTS
, CSI.SERIAL_NUMBER
, LI.PO_REQ_LINE_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
FROM AHL_OSP_ORDER_LINES LI
, MTL_SYSTEM_ITEMS_KFV MTL
, CSI_ITEM_INSTANCES CSI
, MTL_SYSTEM_ITEMS_KFV MTLI
, CSI_ITEM_INSTANCES CSI1
WHERE LI.WORKORDER_ID IS NULL
AND LI.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID (+)
AND LI.INVENTORY_ORG_ID = MTL.ORGANIZATION_ID (+)
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 (+)
AND CSI1.LAST_VLD_ORGANIZATION_ID (+) = LI.INVENTORY_ORG_ID
AND CSI1.INVENTORY_ITEM_ID (+) = LI.INVENTORY_ITEM_ID
AND CSI1.SERIAL_NUMBER (+) = LI.SERIAL_NUMBER