Product: | CSI - Install Base |
---|---|
Description: | Backward Compatible : Customer Products Details View. |
Implementation/DBA Data: | APPS.CSI_CUST_PROD_MAINT_V |
SELECT CII.ROWID ROW_ID
, HZA.ACCOUNT_NUMBER
, HZP.PARTY_NAME
, CII.INSTANCE_ID
, CII.LAST_UPDATE_DATE
, CII.LAST_UPDATED_BY
, CII.CREATION_DATE
, CII.CREATED_BY
, CII.LAST_UPDATE_LOGIN
, CIA.PARTY_ACCOUNT_ID
, CII.INVENTORY_ITEM_ID
, CII.MANUALLY_CREATED_FLAG
, NULL
, CII.INVENTORY_REVISION
, CII.SERIAL_NUMBER
, CII.INSTANCE_TYPE_CODE
, CII.SYSTEM_ID
, CII.INSTALL_DATE
, CII.LAST_OE_ORDER_LINE_ID
, OOH.ORDERED_DATE
, OOH.ORDER_NUMBER
, OOL.LINE_NUMBER
, OOL.LINK_TO_LINE_ID
, NULL
, OOL.UNIT_SELLING_PRICE
, OOH.PRICE_LIST_ID
, CIA.SHIP_TO_ADDRESS
, CIA.BILL_TO_ADDRESS
, CII.INSTALL_LOCATION_ID
, CII.QUANTITY
, NULL
, NULL
, CII.UNIT_OF_MEASURE
, NULL
, CII.ACTIVE_START_DATE
, CII.ACTIVE_END_DATE
, CII.ATTRIBUTE1
, CII.ATTRIBUTE2
, CII.ATTRIBUTE3
, CII.ATTRIBUTE4
, CII.ATTRIBUTE5
, CII.ATTRIBUTE6
, CII.ATTRIBUTE7
, CII.ATTRIBUTE8
, CII.ATTRIBUTE9
, CII.ATTRIBUTE10
, CII.ATTRIBUTE11
, CII.ATTRIBUTE12
, CII.ATTRIBUTE13
, CII.ATTRIBUTE14
, CII.ATTRIBUTE15
, CII.CONTEXT
, CII.INSTANCE_STATUS_ID
, NULL
, CIOA.OPERATING_UNIT_ID
, CII.INSTANCE_NUMBER
, OOH.CUST_PO_NUMBER
, CII.LOT_NUMBER CP_LOT_NUMBER
, NULL
, CIS.NAME CP_STATUS
, NULL
, CIS.TERMINATED_FLAG
, CIS.STATUS_CHANGE_ALLOWED_FLAG
, CIS.INCIDENT_ALLOWED_FLAG
, CIS.SERVICE_ORDER_ALLOWED_FLAG
, CSV.NAME SYSTEM
, CL.MEANING CP_TYPE
, A1.NAME PRODUCT_AGREEMENT
, CII.LAST_OE_AGREEMENT_ID
, CII.INVENTORY_REVISION
, SI.SERIAL_NUMBER_CONTROL_CODE
, SI.DESCRIPTION PRODUCT_DESCRIPTION
, SI.BOM_ITEM_TYPE
, CII.RETURN_BY_DATE
, CII.ACTUAL_RETURN_DATE
, 'Y'
, CIR.ACTIVE_START_DATE
, CIR.ACTIVE_END_DATE
, NULL
, CIR.OBJECT_ID
, CIR.RELATIONSHIP_TYPE_CODE
, CL3.MEANING
, NULL
, CII.PA_PROJECT_ID
, CII.PA_PROJECT_TASK_ID
, NULL
, KFV.CONCATENATED_SEGMENTS
, CSICUMPI_PUB.GET_PART_INFORMATION(CIR.OBJECT_ID) CONFIG_PARENT_INFO
, CSICUMPI_PUB.GET_ROOT_INFORMATION(CII.INSTANCE_ID) CONFIG_ROOT_INFO
, CSICUMPI_PUB.GET_CHILDREN_FLAG(CII.INSTANCE_ID) CONFIG_CHILDREN_FLAG
, CII.MERCHANT_VIEW_FLAG
, CII.CUSTOMER_VIEW_FLAG
FROM CSI_ITEM_INSTANCES CII
, CSI_IP_ACCOUNTS CIA
, CSI_I_ORG_ASSIGNMENTS CIOA
, CSI_I_PARTIES CIP
, CSI_II_RELATIONSHIPS CIR
, CSI_INSTANCE_STATUSES CIS
, CSI_LOOKUPS CL
, CSI_LOOKUPS CL3
, CSI_SYSTEMS_VL CSV
, MTL_SYSTEM_ITEMS SI
, MTL_SYSTEM_ITEMS_KFV KFV
, OE_AGREEMENTS A1
, ASO_I_OE_ORDER_HEADERS_V OOH
, ASO_I_OE_ORDER_LINES_V OOL
, HZ_CUST_ACCOUNTS HZA
, HZ_PARTIES HZP
WHERE CII.SYSTEM_ID = CSV.SYSTEM_ID(+) AND CII.INSTANCE_TYPE_CODE = CL.LOOKUP_CODE(+) AND CL.LOOKUP_TYPE(+) = 'CSI_INST_TYPE_CODE' AND CII.LAST_OE_AGREEMENT_ID = A1.AGREEMENT_ID(+) AND CII.INSTANCE_STATUS_ID = CIS.INSTANCE_STATUS_ID AND CII.INVENTORY_ITEM_ID = SI.INVENTORY_ITEM_ID AND SI.ORGANIZATION_ID = CII.INV_MASTER_ORGANIZATION_ID AND CII.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID AND KFV.ORGANIZATION_ID = CII.INV_MASTER_ORGANIZATION_ID AND CIR.RELATIONSHIP_TYPE_CODE = CL3.LOOKUP_CODE(+) AND CL3.LOOKUP_TYPE(+) = 'CSI_INSTANCE_VERSION_LABELS' AND CII.LAST_OE_ORDER_LINE_ID = OOL.LINE_ID(+) AND OOL.HEADER_ID = OOH.HEADER_ID(+) AND HZA.CUST_ACCOUNT_ID = CIA.PARTY_ACCOUNT_ID AND HZA.PARTY_ID = HZP.PARTY_ID AND CII.INSTANCE_ID = CIR.SUBJECT_ID(+) AND CIR.RELATIONSHIP_TYPE_CODE (+) = 'COMPONENT-OF' AND CIR.ACTIVE_END_DATE (+) IS NULL AND CII.INSTANCE_ID = CIOA.INSTANCE_ID (+) AND CIOA.RELATIONSHIP_TYPE_CODE (+) = 'SOLD_FROM' AND CII.INSTANCE_ID = CIP.INSTANCE_ID AND CIP.RELATIONSHIP_TYPE_CODE = 'OWNER' AND CIP.PARTY_SOURCE_TABLE = 'HZ_PARTIES' AND CIP.INSTANCE_PARTY_ID = CIA.INSTANCE_PARTY_ID (+) AND CIA.RELATIONSHIP_TYPE_CODE(+) = 'OWNER' AND CIA.ACTIVE_END_DATE (+) IS NULL AND NOT EXISTS ( SELECT 1 FROM CSI_INSTALL_PARAMETERS CIPM WHERE CIPM.INTERNAL_PARTY_ID = CIP.PARTY_ID )