View: CSI_INSTALLED_BASE_V

Product:	CSI - Install Base
Description:	View for the grid in E-Business Center.
Implementation/DBA Data:	APPS.CSI_INSTALLED_BASE_V

View Text

SELECT CP.INSTANCE_ID INSTANCE_ID
, CP.INSTANCE_NUMBER INSTANCE_NUMBER
, CP.INSTANCE_DESCRIPTION INSTANCE_NAME
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, CP.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, CP.SERIAL_NUMBER SERIAL_NUMBER
, CP.LOT_NUMBER LOT_NUMBER
, CP.QUANTITY QUANTITY
, CP.ACTIVE_START_DATE ACTIVE_START_DATE
, CP.ACTIVE_END_DATE ACTIVE_END_DATE
, CP.EXTERNAL_REFERENCE EXTERNAL_REFERENCE
, L.MEANING CP_TYPE
, CP.INSTALL_DATE INSTALL_DATE
, CP.INSTANCE_USAGE_CODE INSTANCE_USAGE_CODE
, CPS.NAME INSTANCE_STATUS
, CP.INSTANCE_STATUS_ID INSTANCE_STATUS_ID
, CP.INSTANCE_TYPE_CODE INSTANCE_TYPE_CODE
, CP.LAST_OE_ORDER_LINE_ID ORDER_LINE_ID
, DECODE(CP.OWNER_PARTY_SOURCE_TABLE
, 'HZ_PARTIES'
, (SELECT PARTY_NAME
FROM HZ_PARTIES HZ
WHERE HZ.PARTY_ID = CP.OWNER_PARTY_ID)
, 'PO_VENDORS'
, (SELECT VENDOR_NAME
FROM PO_VENDORS PV
WHERE PV.VENDOR_ID = CP.OWNER_PARTY_ID)
, 'EMPLOYEE'
, (SELECT FULL_NAME
FROM PER_ALL_PEOPLE_F EM
WHERE EM.PERSON_ID = CP.OWNER_PARTY_ID)
, 'TEAM'
, (SELECT TEAM_NAME
FROM JTF_RS_TEAMS_VL TM
WHERE TM.TEAM_ID = CP.OWNER_PARTY_ID)
, 'GROUP'
, (SELECT GROUP_NAME
FROM JTF_RS_GROUPS_VL GR
WHERE GR.GROUP_ID = CP.OWNER_PARTY_ID) )OWNER_PARTY
, CP.OWNER_PARTY_ID OWNER_PARTY_ID
, CP.OWNER_PARTY_ACCOUNT_ID OWNER_PARTY_ACCOUNT_ID
, CP.LOCATION_TYPE_CODE LOCATION_TYPE_CODE
, DECODE(CP.LOCATION_TYPE_CODE
, 'HZ_PARTY_SITES'
, (SELECT ADDRESS1
FROM HZ_LOCATIONS HZ
, HZ_PARTY_SITES HPS
WHERE HZ.LOCATION_ID = HPS.LOCATION_ID
AND HPS.PARTY_SITE_ID = CP.LOCATION_ID)
, 'HZ_LOCATIONS'
, (SELECT ADDRESS1
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CP.LOCATION_ID)
, 'VENDOR_SITE'
, (SELECT ADDRESS_LINE1
FROM PO_VENDOR_SITES_ALL PVS
WHERE PVS.VENDOR_SITE_ID = CP.LOCATION_ID)
, 'INVENTORY'
, (SELECT ADDRESS_LINE_1
FROM HR_LOCATIONS_ALL HRL
WHERE HRL.LOCATION_ID = CP.LOCATION_ID)
, 'INTERNAL_SITE'
, (SELECT ADDRESS_LINE_1
FROM HR_LOCATIONS_ALL HRL
WHERE HRL.LOCATION_ID = CP.LOCATION_ID)
, 'WIP'
, (SELECT ADDRESS1
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CP.LOCATION_ID)
, 'PROJECT'
, (SELECT ADDRESS1
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CP.LOCATION_ID)
, 'IN_TRANSIT'
, (SELECT ADDRESS1
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CP.LOCATION_ID)
, 'PO'
, (SELECT ADDRESS1
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CP.LOCATION_ID) )CURRENT_LOCATION
, CP.LOCATION_ID LOCATION_ID
, CP.INSTALL_LOCATION_TYPE_CODE INSTALL_LOCATION_TYPE_CODE
, DECODE(CP.INSTALL_LOCATION_TYPE_CODE
, 'HZ_PARTY_SITES'
, (SELECT ADDRESS1
FROM HZ_LOCATIONS HZ
, HZ_PARTY_SITES HPS
WHERE HZ.LOCATION_ID = HPS.LOCATION_ID
AND HPS.PARTY_SITE_ID = CP.LOCATION_ID)
, 'HZ_LOCATIONS'
, (SELECT ADDRESS1
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CP.LOCATION_ID)
, 'VENDOR_SITE'
, (SELECT ADDRESS_LINE1
FROM PO_VENDOR_SITES_ALL PVS
WHERE PVS.VENDOR_SITE_ID = CP.LOCATION_ID)
, 'INVENTORY'
, (SELECT ADDRESS_LINE_1
FROM HR_LOCATIONS_ALL HRL
WHERE HRL.LOCATION_ID = CP.LOCATION_ID)
, 'INTERNAL_SITE'
, (SELECT ADDRESS_LINE_1
FROM HR_LOCATIONS_ALL HRL
WHERE HRL.LOCATION_ID = CP.LOCATION_ID)
, 'WIP'
, (SELECT ADDRESS1
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CP.LOCATION_ID)
, 'PROJECT'
, (SELECT ADDRESS1
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CP.LOCATION_ID)
, 'IN_TRANSIT'
, (SELECT ADDRESS1
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CP.LOCATION_ID)
, 'PO'
, (SELECT ADDRESS1
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CP.LOCATION_ID) )INSTALLED_LOCATION
, CP.INSTALL_LOCATION_ID INSTALL_LOCATION_ID
, S.NAME SYSTEM
, CP.SYSTEM_ID SYSTEM_ID
, OOH.ORDER_NUMBER ORDER_NUMBER
, OOH.CUST_PO_NUMBER CUST_PO_NUMBER
, OOL.LINE_NUMBER LINE_NUMBER
, OOL.LINK_TO_LINE_ID LINK_TO_LINE_ID
, OOH.HEADER_ID ORDER_HEADER_ID
, OOH.ORDERED_DATE ORDERED_DATE
, OOL.ACTUAL_SHIPMENT_DATE SHIPPED_ON_DATE
, CP.ACTUAL_RETURN_DATE ACTUAL_RETURN_DATE
, CP.INV_MASTER_ORGANIZATION_ID MASTER_ORGANIZATION_ID
, CP.CONFIG_INST_HDR_ID CONFIG_INST_HDR_ID
, CP.CONFIG_INST_REV_NUM CONFIG_INST_REV_NUM
, CP.CONFIG_INST_ITEM_ID CONFIG_INST_ITEM_ID
, OOH.SOLD_TO_ORG_ID SOLD_TO_ORG_ID
FROM CSI_ITEM_INSTANCES CP
, CSI_INSTANCE_STATUSES CPS
, OE_ORDER_LINES_ALL OOL
, OE_ORDER_HEADERS_ALL OOH
, CSI_SYSTEMS_VL S
, CSI_LOOKUPS L
, MTL_SYSTEM_ITEMS_KFV MSI
WHERE CP.SYSTEM_ID = S.SYSTEM_ID(+)
AND MSI.INVENTORY_ITEM_ID = CP.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CP.LAST_VLD_ORGANIZATION_ID
AND CP.INSTANCE_TYPE_CODE = L.LOOKUP_CODE(+)
AND L.LOOKUP_TYPE(+) = 'CUSTOMER_PRODUCT_TYPE'
AND CP.INSTANCE_STATUS_ID = CPS.INSTANCE_STATUS_ID
AND CP.LAST_OE_ORDER_LINE_ID = OOL.LINE_ID(+)
AND OOL.HEADER_ID = OOH.HEADER_ID(+)

Columns