VIEW: APPS.CSI_ASSET_INTEGRATION_R12_V

Source

View Text - Preformatted

SELECT CII.INSTANCE_ID CUSTOMER_PRODUCT_ID, CII.INSTANCE_NUMBER ASSET_NUMBER, CII.INSTANCE_DESCRIPTION ASSET_DESCRIPTION, CII.SERIAL_NUMBER SERIAL_NUMBER, CII.MFG_SERIAL_NUMBER_FLAG MFG_SERIAL_NUMBER_FLAG, CII.INV_MASTER_ORGANIZATION_ID ORGANIZATION_ID, HAOU1.NAME ORGANIZATION_NAME, MP1.ORGANIZATION_CODE ORGANIZATION_CODE, CII.LAST_VLD_ORGANIZATION_ID INV_LOCATION_ORG_ID, HAOU2.NAME INV_LOCATION_ORG_NAME, MP2.ORGANIZATION_CODE INV_LOCATION_ORG_CODE, CII.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, MSI.CONCATENATED_SEGMENTS CUSTOMER_PRODUCT_NAME, MSI.DESCRIPTION CUSTOMER_PRODUCT_DESCRIPTION, CII.INVENTORY_REVISION VERSION, CII.SYSTEM_ID SYSTEM_ID, CSV.NAME SYSTEM_NAME, CSV.DESCRIPTION SYSTEM_DESCRIPTION, CSV.SYSTEM_TYPE_CODE SYSTEM_TYPE_CODE, CSV.SYSTEM_NUMBER SYSTEM_NUMBER, CII.OWNER_PARTY_SOURCE_TABLE OWNER_PARTY_SOURCE_TABLE, CII.OWNER_PARTY_ID OWNER_PARTY_ID, CII.OWNER_PARTY_ACCOUNT_ID OWNER_ACCOUNT_ID, HCA.ACCOUNT_NAME OWNER_ACCOUNT_NAME, HCA.ACCOUNT_NUMBER OWNER_ACCOUNT_NUMBER, CSV.CUSTOMER_ID CUSTOMER_ID, HCA.ACCOUNT_NAME CUSTOMER_NAME, HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER, CII.EXTERNAL_REFERENCE EXTERNAL_REFERENCE, CII.LOT_NUMBER LOT_NUMBER, CII.QUANTITY QUANTITY, CII.UNIT_OF_MEASURE UNIT_OF_MEASURE_CODE, CII.ACCOUNTING_CLASS_CODE ACCOUNTING_CLASS_CODE, CII.INSTANCE_CONDITION_ID INSTANCE_CONDITION_ID, CII.INSTANCE_USAGE_CODE INSTANCE_USAGE_CODE, CII.INSTANCE_TYPE_CODE INSTANCE_TYPE_CODE, CL.MEANING INSTANCE_TYPE_MEANING, CII.INSTANCE_STATUS_ID STATUS_ID, CIS.NAME STATUS_CODE, CIS.DESCRIPTION STAUTS_DESCRIPTION, CII.CUSTOMER_VIEW_FLAG CUSTOMER_VIEW_FLAG, CII.MERCHANT_VIEW_FLAG MERCHANT_VIEW_FLAG, CII.SELLABLE_FLAG SELLABLE_FLAG, CII.LOCATION_TYPE_CODE LOCATION_TYPE_CODE, CII.LOCATION_ID LOCATION_ID, CII.INV_ORGANIZATION_ID INV_ORGANIZATION_ID, HAOU3.NAME INV_ORGANIZATION_NAME, MP3.ORGANIZATION_CODE INV_ORGANIZATION_CODE, CII.INV_SUBINVENTORY_NAME INV_SUBINVENTORY_NAME, CII.INV_LOCATOR_ID INV_LOCATOR_ID, CII.PA_PROJECT_ID PA_PROJECT_ID, CII.PA_PROJECT_TASK_ID PA_PROJECT_TASK_ID, CII.IN_TRANSIT_ORDER_LINE_ID IN_TRANSIT_ORDER_LINE_ID, CII.WIP_JOB_ID WIP_JOB_ID, CII.PO_ORDER_LINE_ID PO_ORDER_LINE_ID, CII.LAST_OE_ORDER_LINE_ID ORIGINAL_ORDER_LINE_ID, CII.LAST_OE_RMA_LINE_ID LAST_OE_RMA_LINE_ID, CII.LAST_PO_PO_LINE_ID LAST_PO_PO_LINE_ID, CII.LAST_OE_PO_NUMBER LAST_OE_PO_NUMBER, CII.LAST_WIP_JOB_ID LAST_WIP_JOB_ID, CII.LAST_PA_PROJECT_ID LAST_PA_PROJECT_ID, CII.LAST_PA_TASK_ID LAST_PA_TASK_ID, CII.LAST_OE_AGREEMENT_ID LAST_OE_AGREEMENT_ID, CII.LAST_TXN_LINE_DETAIL_ID LAST_TXN_LINE_DETAIL_ID, CII.MANUALLY_CREATED_FLAG MANUALLY_CREATED_FLAG, CII.INSTALL_DATE INSTALL_DATE, CII.INSTALL_LOCATION_TYPE_CODE INSTALL_LOCATION_TYPE_CODE, CII.INSTALL_LOCATION_ID INSTALL_LOCATION_ID, CII.RETURN_BY_DATE RETURN_BY_DATE, CII.ACTUAL_RETURN_DATE ACTUAL_RETURN_DATE, CII.ACTIVE_START_DATE ACTIVE_START_DATE, CII.ACTIVE_END_DATE ACTIVE_END_DATE, CII.CREATION_COMPLETE_FLAG CREATION_COMPLETE_FLAG, CII.COMPLETENESS_FLAG COMPLETENESS_FLAG, CIR.OBJECT_ID PARENT_ASSET_ID, CII2.INSTANCE_NUMBER PARENT_ASSET_NUMBER, MSI2.CONCATENATED_SEGMENTS PARENT_ASSET_PRODUCT_NAME, NVL(CSI_II_RELATIONSHIPS_PVT.GET_ROOT_PARENT( CII.INSTANCE_ID, 'COMPONENT-OF',CIR.OBJECT_ID), CII.INSTANCE_ID) ROOT_ASSET_ID, CII.SECURITY_GROUP_ID SECURITY_GROUP_ID, CII.MIGRATED_FLAG MIGRATED_FLAG, CII.REQUEST_ID REQUEST_ID, CII.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID, CII.PROGRAM_ID PROGRAM_ID, CII.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE, CII.CONFIG_INST_HDR_ID CONFIG_INST_HDR_ID, CII.CONFIG_INST_REV_NUM CONFIG_INST_REV_NUM, CII.CONFIG_INST_ITEM_ID CONFIG_INST_ITEM_ID, CII.CONFIG_VALID_STATUS CONFIG_VALID_STATUS, CII.LAST_PURGE_DATE LAST_PURGE_DATE, CII.NETWORK_ASSET_FLAG NETWORK_ASSET_FLAG, CII.MAINTAINABLE_FLAG MAINTAINABLE_FLAG, CII.PN_LOCATION_ID PN_LOCATION_ID, CII.ASSET_CRITICALITY_CODE ASSET_CRITICALITY_CODE, CII.CATEGORY_ID CATEGORY_ID, CII.EQUIPMENT_GEN_OBJECT_ID EQUIPMENT_GEN_OBJECT_ID, CII.INSTANTIATION_FLAG INSTANTIATION_FLAG, CII.LINEAR_LOCATION_ID LINEAR_LOCATION_ID, CII.OPERATIONAL_LOG_FLAG OPERATIONAL_LOG_FLAG, CII.CHECKIN_STATUS CHECKIN_STATUS, CII.SUPPLIER_WARRANTY_EXP_DATE SUPPLIER_WARRANTY_EXP_DATE, CII.PURCHASE_UNIT_PRICE PURCHASE_UNIT_PRICE, CII.PURCHASE_CURRENCY_CODE PURCHASE_CURRENCY_CODE, CII.PAYABLES_UNIT_PRICE PAYABLES_UNIT_PRICE, CII.PAYABLES_CURRENCY_CODE PAYABLES_CURRENCY_CODE, CII.SALES_UNIT_PRICE SALES_UNIT_PRICE, CII.SALES_CURRENCY_CODE SALES_CURRENCY_CODE, CII.OPERATIONAL_STATUS_CODE OPERATIONAL_STATUS_CODE, CII.SOURCE_CODE SOURCE_CODE, CII.CONTEXT CONTEXT, CII.ATTRIBUTE1 ATTRIBUTE1, CII.ATTRIBUTE2 ATTRIBUTE2, CII.ATTRIBUTE3 ATTRIBUTE3, CII.ATTRIBUTE4 ATTRIBUTE4, CII.ATTRIBUTE5 ATTRIBUTE5, CII.ATTRIBUTE6 ATTRIBUTE6, CII.ATTRIBUTE7 ATTRIBUTE7, CII.ATTRIBUTE8 ATTRIBUTE8, CII.ATTRIBUTE9 ATTRIBUTE9, CII.ATTRIBUTE10 ATTRIBUTE10, CII.ATTRIBUTE11 ATTRIBUTE11, CII.ATTRIBUTE12 ATTRIBUTE12, CII.ATTRIBUTE13 ATTRIBUTE13, CII.ATTRIBUTE14 ATTRIBUTE14, CII.ATTRIBUTE15 ATTRIBUTE15, CII.ATTRIBUTE16 ATTRIBUTE16, CII.ATTRIBUTE17 ATTRIBUTE17, CII.ATTRIBUTE18 ATTRIBUTE18, CII.ATTRIBUTE19 ATTRIBUTE19, CII.ATTRIBUTE20 ATTRIBUTE20, CII.ATTRIBUTE21 ATTRIBUTE21, CII.ATTRIBUTE22 ATTRIBUTE22, CII.ATTRIBUTE23 ATTRIBUTE23, CII.ATTRIBUTE24 ATTRIBUTE24, CII.ATTRIBUTE25 ATTRIBUTE25, CII.ATTRIBUTE26 ATTRIBUTE26, CII.ATTRIBUTE27 ATTRIBUTE27, CII.ATTRIBUTE28 ATTRIBUTE28, CII.ATTRIBUTE29 ATTRIBUTE29, CII.ATTRIBUTE30 ATTRIBUTE30, CII.CREATED_BY CREATED_BY, CII.CREATION_DATE CREATION_DATE, CII.LAST_UPDATED_BY LAST_UPDATED_BY, CII.LAST_UPDATE_DATE LAST_UPDATE_DATE, CII.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, CII.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER FROM CSI_ITEM_INSTANCES CII, CSI_ITEM_INSTANCES CII2, HR_ALL_ORGANIZATION_UNITS HAOU1, HR_ALL_ORGANIZATION_UNITS HAOU2, HR_ALL_ORGANIZATION_UNITS HAOU3, MTL_PARAMETERS MP1, MTL_PARAMETERS MP2, MTL_PARAMETERS MP3, MTL_SYSTEM_ITEMS_KFV MSI, MTL_SYSTEM_ITEMS_KFV MSI2, CSI_INSTANCE_STATUSES CIS, CSI_SYSTEMS_VL CSV, HZ_CUST_ACCOUNTS HCA, CSI_II_RELATIONSHIPS CIR, CSI_LOOKUPS CL WHERE CII.INV_MASTER_ORGANIZATION_ID = HAOU1.ORGANIZATION_ID AND CII.LAST_VLD_ORGANIZATION_ID = HAOU2.ORGANIZATION_ID AND CII.INV_ORGANIZATION_ID = HAOU3.ORGANIZATION_ID(+) AND CII.INV_MASTER_ORGANIZATION_ID = MP1.ORGANIZATION_ID AND CII.LAST_VLD_ORGANIZATION_ID = MP2.ORGANIZATION_ID AND CII.INV_ORGANIZATION_ID = MP3.ORGANIZATION_ID(+) AND CII.LAST_VLD_ORGANIZATION_ID = MSI.ORGANIZATION_ID(+) AND CII.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+) AND CII.INSTANCE_TYPE_CODE = CL.LOOKUP_CODE(+) AND CII.INSTANCE_STATUS_ID = CIS.INSTANCE_STATUS_ID AND CII.SYSTEM_ID = CSV.SYSTEM_ID(+) AND CSV.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID(+) AND CII.INSTANCE_ID = CIR.SUBJECT_ID(+) AND CIR.RELATIONSHIP_TYPE_CODE(+) = 'COMPONENT-OF' AND CIR.ACTIVE_END_DATE(+) IS NULL AND CIR.OBJECT_ID = CII2.INSTANCE_ID(+) AND CII2.LAST_VLD_ORGANIZATION_ID = MSI2.ORGANIZATION_ID(+) AND CII2.INVENTORY_ITEM_ID = MSI2.INVENTORY_ITEM_ID(+)

View Text - HTML Formatted

SELECT CII.INSTANCE_ID CUSTOMER_PRODUCT_ID
, CII.INSTANCE_NUMBER ASSET_NUMBER
, CII.INSTANCE_DESCRIPTION ASSET_DESCRIPTION
, CII.SERIAL_NUMBER SERIAL_NUMBER
, CII.MFG_SERIAL_NUMBER_FLAG MFG_SERIAL_NUMBER_FLAG
, CII.INV_MASTER_ORGANIZATION_ID ORGANIZATION_ID
, HAOU1.NAME ORGANIZATION_NAME
, MP1.ORGANIZATION_CODE ORGANIZATION_CODE
, CII.LAST_VLD_ORGANIZATION_ID INV_LOCATION_ORG_ID
, HAOU2.NAME INV_LOCATION_ORG_NAME
, MP2.ORGANIZATION_CODE INV_LOCATION_ORG_CODE
, CII.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS CUSTOMER_PRODUCT_NAME
, MSI.DESCRIPTION CUSTOMER_PRODUCT_DESCRIPTION
, CII.INVENTORY_REVISION VERSION
, CII.SYSTEM_ID SYSTEM_ID
, CSV.NAME SYSTEM_NAME
, CSV.DESCRIPTION SYSTEM_DESCRIPTION
, CSV.SYSTEM_TYPE_CODE SYSTEM_TYPE_CODE
, CSV.SYSTEM_NUMBER SYSTEM_NUMBER
, CII.OWNER_PARTY_SOURCE_TABLE OWNER_PARTY_SOURCE_TABLE
, CII.OWNER_PARTY_ID OWNER_PARTY_ID
, CII.OWNER_PARTY_ACCOUNT_ID OWNER_ACCOUNT_ID
, HCA.ACCOUNT_NAME OWNER_ACCOUNT_NAME
, HCA.ACCOUNT_NUMBER OWNER_ACCOUNT_NUMBER
, CSV.CUSTOMER_ID CUSTOMER_ID
, HCA.ACCOUNT_NAME CUSTOMER_NAME
, HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, CII.EXTERNAL_REFERENCE EXTERNAL_REFERENCE
, CII.LOT_NUMBER LOT_NUMBER
, CII.QUANTITY QUANTITY
, CII.UNIT_OF_MEASURE UNIT_OF_MEASURE_CODE
, CII.ACCOUNTING_CLASS_CODE ACCOUNTING_CLASS_CODE
, CII.INSTANCE_CONDITION_ID INSTANCE_CONDITION_ID
, CII.INSTANCE_USAGE_CODE INSTANCE_USAGE_CODE
, CII.INSTANCE_TYPE_CODE INSTANCE_TYPE_CODE
, CL.MEANING INSTANCE_TYPE_MEANING
, CII.INSTANCE_STATUS_ID STATUS_ID
, CIS.NAME STATUS_CODE
, CIS.DESCRIPTION STAUTS_DESCRIPTION
, CII.CUSTOMER_VIEW_FLAG CUSTOMER_VIEW_FLAG
, CII.MERCHANT_VIEW_FLAG MERCHANT_VIEW_FLAG
, CII.SELLABLE_FLAG SELLABLE_FLAG
, CII.LOCATION_TYPE_CODE LOCATION_TYPE_CODE
, CII.LOCATION_ID LOCATION_ID
, CII.INV_ORGANIZATION_ID INV_ORGANIZATION_ID
, HAOU3.NAME INV_ORGANIZATION_NAME
, MP3.ORGANIZATION_CODE INV_ORGANIZATION_CODE
, CII.INV_SUBINVENTORY_NAME INV_SUBINVENTORY_NAME
, CII.INV_LOCATOR_ID INV_LOCATOR_ID
, CII.PA_PROJECT_ID PA_PROJECT_ID
, CII.PA_PROJECT_TASK_ID PA_PROJECT_TASK_ID
, CII.IN_TRANSIT_ORDER_LINE_ID IN_TRANSIT_ORDER_LINE_ID
, CII.WIP_JOB_ID WIP_JOB_ID
, CII.PO_ORDER_LINE_ID PO_ORDER_LINE_ID
, CII.LAST_OE_ORDER_LINE_ID ORIGINAL_ORDER_LINE_ID
, CII.LAST_OE_RMA_LINE_ID LAST_OE_RMA_LINE_ID
, CII.LAST_PO_PO_LINE_ID LAST_PO_PO_LINE_ID
, CII.LAST_OE_PO_NUMBER LAST_OE_PO_NUMBER
, CII.LAST_WIP_JOB_ID LAST_WIP_JOB_ID
, CII.LAST_PA_PROJECT_ID LAST_PA_PROJECT_ID
, CII.LAST_PA_TASK_ID LAST_PA_TASK_ID
, CII.LAST_OE_AGREEMENT_ID LAST_OE_AGREEMENT_ID
, CII.LAST_TXN_LINE_DETAIL_ID LAST_TXN_LINE_DETAIL_ID
, CII.MANUALLY_CREATED_FLAG MANUALLY_CREATED_FLAG
, CII.INSTALL_DATE INSTALL_DATE
, CII.INSTALL_LOCATION_TYPE_CODE INSTALL_LOCATION_TYPE_CODE
, CII.INSTALL_LOCATION_ID INSTALL_LOCATION_ID
, CII.RETURN_BY_DATE RETURN_BY_DATE
, CII.ACTUAL_RETURN_DATE ACTUAL_RETURN_DATE
, CII.ACTIVE_START_DATE ACTIVE_START_DATE
, CII.ACTIVE_END_DATE ACTIVE_END_DATE
, CII.CREATION_COMPLETE_FLAG CREATION_COMPLETE_FLAG
, CII.COMPLETENESS_FLAG COMPLETENESS_FLAG
, CIR.OBJECT_ID PARENT_ASSET_ID
, CII2.INSTANCE_NUMBER PARENT_ASSET_NUMBER
, MSI2.CONCATENATED_SEGMENTS PARENT_ASSET_PRODUCT_NAME
, NVL(CSI_II_RELATIONSHIPS_PVT.GET_ROOT_PARENT( CII.INSTANCE_ID
, 'COMPONENT-OF'
, CIR.OBJECT_ID)
, CII.INSTANCE_ID) ROOT_ASSET_ID
, CII.SECURITY_GROUP_ID SECURITY_GROUP_ID
, CII.MIGRATED_FLAG MIGRATED_FLAG
, CII.REQUEST_ID REQUEST_ID
, CII.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, CII.PROGRAM_ID PROGRAM_ID
, CII.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, CII.CONFIG_INST_HDR_ID CONFIG_INST_HDR_ID
, CII.CONFIG_INST_REV_NUM CONFIG_INST_REV_NUM
, CII.CONFIG_INST_ITEM_ID CONFIG_INST_ITEM_ID
, CII.CONFIG_VALID_STATUS CONFIG_VALID_STATUS
, CII.LAST_PURGE_DATE LAST_PURGE_DATE
, CII.NETWORK_ASSET_FLAG NETWORK_ASSET_FLAG
, CII.MAINTAINABLE_FLAG MAINTAINABLE_FLAG
, CII.PN_LOCATION_ID PN_LOCATION_ID
, CII.ASSET_CRITICALITY_CODE ASSET_CRITICALITY_CODE
, CII.CATEGORY_ID CATEGORY_ID
, CII.EQUIPMENT_GEN_OBJECT_ID EQUIPMENT_GEN_OBJECT_ID
, CII.INSTANTIATION_FLAG INSTANTIATION_FLAG
, CII.LINEAR_LOCATION_ID LINEAR_LOCATION_ID
, CII.OPERATIONAL_LOG_FLAG OPERATIONAL_LOG_FLAG
, CII.CHECKIN_STATUS CHECKIN_STATUS
, CII.SUPPLIER_WARRANTY_EXP_DATE SUPPLIER_WARRANTY_EXP_DATE
, CII.PURCHASE_UNIT_PRICE PURCHASE_UNIT_PRICE
, CII.PURCHASE_CURRENCY_CODE PURCHASE_CURRENCY_CODE
, CII.PAYABLES_UNIT_PRICE PAYABLES_UNIT_PRICE
, CII.PAYABLES_CURRENCY_CODE PAYABLES_CURRENCY_CODE
, CII.SALES_UNIT_PRICE SALES_UNIT_PRICE
, CII.SALES_CURRENCY_CODE SALES_CURRENCY_CODE
, CII.OPERATIONAL_STATUS_CODE OPERATIONAL_STATUS_CODE
, CII.SOURCE_CODE SOURCE_CODE
, CII.CONTEXT CONTEXT
, CII.ATTRIBUTE1 ATTRIBUTE1
, CII.ATTRIBUTE2 ATTRIBUTE2
, CII.ATTRIBUTE3 ATTRIBUTE3
, CII.ATTRIBUTE4 ATTRIBUTE4
, CII.ATTRIBUTE5 ATTRIBUTE5
, CII.ATTRIBUTE6 ATTRIBUTE6
, CII.ATTRIBUTE7 ATTRIBUTE7
, CII.ATTRIBUTE8 ATTRIBUTE8
, CII.ATTRIBUTE9 ATTRIBUTE9
, CII.ATTRIBUTE10 ATTRIBUTE10
, CII.ATTRIBUTE11 ATTRIBUTE11
, CII.ATTRIBUTE12 ATTRIBUTE12
, CII.ATTRIBUTE13 ATTRIBUTE13
, CII.ATTRIBUTE14 ATTRIBUTE14
, CII.ATTRIBUTE15 ATTRIBUTE15
, CII.ATTRIBUTE16 ATTRIBUTE16
, CII.ATTRIBUTE17 ATTRIBUTE17
, CII.ATTRIBUTE18 ATTRIBUTE18
, CII.ATTRIBUTE19 ATTRIBUTE19
, CII.ATTRIBUTE20 ATTRIBUTE20
, CII.ATTRIBUTE21 ATTRIBUTE21
, CII.ATTRIBUTE22 ATTRIBUTE22
, CII.ATTRIBUTE23 ATTRIBUTE23
, CII.ATTRIBUTE24 ATTRIBUTE24
, CII.ATTRIBUTE25 ATTRIBUTE25
, CII.ATTRIBUTE26 ATTRIBUTE26
, CII.ATTRIBUTE27 ATTRIBUTE27
, CII.ATTRIBUTE28 ATTRIBUTE28
, CII.ATTRIBUTE29 ATTRIBUTE29
, CII.ATTRIBUTE30 ATTRIBUTE30
, CII.CREATED_BY CREATED_BY
, CII.CREATION_DATE CREATION_DATE
, CII.LAST_UPDATED_BY LAST_UPDATED_BY
, CII.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CII.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CII.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
FROM CSI_ITEM_INSTANCES CII
, CSI_ITEM_INSTANCES CII2
, HR_ALL_ORGANIZATION_UNITS HAOU1
, HR_ALL_ORGANIZATION_UNITS HAOU2
, HR_ALL_ORGANIZATION_UNITS HAOU3
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, MTL_PARAMETERS MP3
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_SYSTEM_ITEMS_KFV MSI2
, CSI_INSTANCE_STATUSES CIS
, CSI_SYSTEMS_VL CSV
, HZ_CUST_ACCOUNTS HCA
, CSI_II_RELATIONSHIPS CIR
, CSI_LOOKUPS CL
WHERE CII.INV_MASTER_ORGANIZATION_ID = HAOU1.ORGANIZATION_ID
AND CII.LAST_VLD_ORGANIZATION_ID = HAOU2.ORGANIZATION_ID
AND CII.INV_ORGANIZATION_ID = HAOU3.ORGANIZATION_ID(+)
AND CII.INV_MASTER_ORGANIZATION_ID = MP1.ORGANIZATION_ID
AND CII.LAST_VLD_ORGANIZATION_ID = MP2.ORGANIZATION_ID
AND CII.INV_ORGANIZATION_ID = MP3.ORGANIZATION_ID(+)
AND CII.LAST_VLD_ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
AND CII.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND CII.INSTANCE_TYPE_CODE = CL.LOOKUP_CODE(+)
AND CII.INSTANCE_STATUS_ID = CIS.INSTANCE_STATUS_ID
AND CII.SYSTEM_ID = CSV.SYSTEM_ID(+)
AND CSV.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID(+)
AND CII.INSTANCE_ID = CIR.SUBJECT_ID(+)
AND CIR.RELATIONSHIP_TYPE_CODE(+) = 'COMPONENT-OF'
AND CIR.ACTIVE_END_DATE(+) IS NULL
AND CIR.OBJECT_ID = CII2.INSTANCE_ID(+)
AND CII2.LAST_VLD_ORGANIZATION_ID = MSI2.ORGANIZATION_ID(+)
AND CII2.INVENTORY_ITEM_ID = MSI2.INVENTORY_ITEM_ID(+)