Oracle Install Base Query

/* Formatted on 3/6/2014 2:10:52 PM (QP5 v5.256.13226.35510) */
--INSTALLED BASE ROOT ITEM QUERY
--ENTER A PARTY NUMBER AND GET RESULTS

SELECT SYSDATE,
       cipv.instance_number AS "Instance Number",
       (SELECT hp.party_name
          FROM ar.hz_parties hp
         WHERE hp.party_id = cipv.party_id)
          AS "Owner Name",
       (SELECT hp.party_number
          FROM ar.hz_parties hp
         WHERE hp.party_id = cipv.party_id)
          AS "Owner Party",
       (SELECT hca.account_number
          FROM ar.hz_cust_accounts hca
         WHERE hca.cust_account_id = cipv.party_account_id)
          AS "Owner Account",
       cidv.concatenated_segments AS "Item",
       cidv.description AS "Item Description",
       cidv.quantity AS "Qty",
       cidv.unit_of_measure AS "UOM",
       cidv.status_name AS "ISB Status",
       cidv.external_reference AS "External Reference (Tag)",
       cidv.instance_description AS "Instance Name",
       cidv.instance_usage_code AS "ISB Usage Code",
       cidv.serial_number AS "Serial",
       cidv.lot_number AS "Lot",
       cidv.instance_type_code AS "ISB Instance Type",
       cidv.install_date "Install Date",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'ALTITUDE')
          AS "Altitude (Value and Unit)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'BACNET')
          AS "BACNET (Device, Protocol)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'CELLULAR')
          AS "Celluar (Type, Carrier, #)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'CONFIG_CODE')
          AS "Configuration Code",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'CONFIG_COMMENT')
          AS "Configuration Comment",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'ELEVATION')
          AS "Elevation (Value and Unit)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'ETHERNET')
          AS "Ethernet (Type, IP Address)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'HIGHWAY_LOCATION')
          AS "Highway Location",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'ICAO_CODE')
          AS "ICAO Code",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'LATITUDE')
          AS "Latitude (DDD MM.MMM)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'LOCAL_PARTNER')
          AS "Local Partner",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'LONGITUDE')
          AS "Longitude (DDD MM.MMM)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'MAGNETIC_DECLINATION')
          AS "Magnetic Declination (Deg)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'MAINS_TYPE')
          AS "Mains Type",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'MAST_HEIGHT')
          AS "Mast Height (m)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'MODBUS')
          AS "Modbus (Device, Protocol)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'PHONE_PSTN')
          AS "Phone/PSTN (Type, Carrier, #)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'RADIO')
          AS "Radio (Type, Freq)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'ROAD_NUMBER')
          AS "Road Number",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'SATELLITE')
          AS "Satellite (Type, Provider)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'SENSOR_PROTOCOL')
          AS "Sensor Protocol (Type)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'SERVICE_PROVIDER')
          AS "Service Provider",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'SITE_NAME')
          AS "Site Name",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'STATION_ID')
          AS "Station ID",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'XXVAI_ITEM_REV')
          AS "Vaisala Item Revision",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'WMO_CODE')
          AS "WMO Code",
       DECODE (
          cidv.location_type_code,
          'HZ_PARTY_SITES',    (SELECT DISTINCT hpsv.address1
                                  FROM apps.hz_party_sites_v hpsv
                                 WHERE hpsv.party_site_id = cidv.location_id)
                            || '--'
                            || (SELECT DISTINCT hpsv.city
                                  FROM apps.hz_party_sites_v hpsv
                                 WHERE hpsv.party_site_id = cidv.location_id)
                            || '--'
                            || (SELECT DISTINCT hpsv.state
                                  FROM apps.hz_party_sites_v hpsv
                                 WHERE hpsv.party_site_id = cidv.location_id),
          'INVENTORY',    'VAISALA -- '
                       || (SELECT ionv.organization_name
                             FROM APPS.INV_ORGANIZATION_NAME_V ionv
                            WHERE ionv.organization_id =
                                     cidv.inv_organization_id)
                       || '--'
                       || cidv.inv_subinventory_name,
          'UNKNOWN')
          "Current_Location",
       DECODE (
          cidv.install_location_type_code,
          'HZ_PARTY_SITES',    (SELECT DISTINCT hpsv.address1
                                  FROM apps.hz_party_sites_v hpsv
                                 WHERE hpsv.party_site_id =
                                          cidv.install_location_id)
                            || '--'
                            || (SELECT DISTINCT hpsv.city
                                  FROM apps.hz_party_sites_v hpsv
                                 WHERE hpsv.party_site_id =
                                          cidv.install_location_id)
                            || '--'
                            || (SELECT DISTINCT hpsv.state
                                  FROM apps.hz_party_sites_v hpsv
                                 WHERE hpsv.party_site_id =
                                          cidv.install_location_id),
          NULL, 'NULL',
          'UNKNOWN')
          "Install_Location",
       (SELECT DISTINCT
               hpsv.address1 || '--' || hpsv.city || '--' || hpsv.state
          FROM apps.hz_party_sites_v hpsv
         WHERE hpsv.party_site_id =
                  (SELECT hcasa.party_site_id
                     FROM ar.hz_cust_acct_sites_all hcasa
                    WHERE hcasa.cust_acct_site_id =
                             (SELECT hcsua.cust_acct_site_id
                                FROM ar.hz_cust_site_uses_all hcsua
                               WHERE cipv.bill_to_address = hcsua.site_use_id)))
          AS "Bill To",
       (SELECT DISTINCT
               hpsv.address1 || '--' || hpsv.city || '--' || hpsv.state
          FROM apps.hz_party_sites_v hpsv
         WHERE hpsv.party_site_id =
                  (SELECT hcasa.party_site_id
                     FROM ar.hz_cust_acct_sites_all hcasa
                    WHERE hcasa.cust_acct_site_id =
                             (SELECT hcsua.cust_acct_site_id
                                FROM ar.hz_cust_site_uses_all hcsua
                               WHERE cipv.ship_to_address = hcsua.site_use_id)))
          AS "Ship To",
       DECODE ( (SELECT COUNT (*)
                   FROM CSI.CSI_II_RELATIONSHIPS CIR
                  WHERE CIR.OBJECT_ID = cipv.INSTANCE_ID),
               0, 'No BOM Components',
               'BOM Components Exist')
          AS "BOM?"
  FROM APPS.CSI_INSTANCE_PARTY_V cipv, apps.csi_instance_details_v cidv
 WHERE     1 = 1
       AND cidv.instance_id = cipv.instance_id
       AND cipv.instance_usage_code <> 'IN_RELATIONSHIP' --ROOT INSTANCES ONLY
       AND ( (cipv.party_account_id =
                 (SELECT hca.cust_account_id
                    FROM ar.hz_cust_accounts hca
                   WHERE hca.account_number IN TO_CHAR (&AccountNumber))));