VIEW: APPS.CSI_INSTALLED_BASE_V

Source

View Text - Preformatted

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(+)

View Text - HTML Formatted

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(+)