Product: | CSI - Install Base |
---|---|
Description: | Backward Compatible : Customer Products (Table View) |
Implementation/DBA Data: | APPS.CSI_CUSTOMER_PRODUCTS_ALL_V |
SELECT CII.ROWID ROW_ID
, CII.INSTANCE_ID CUSTOMER_PRODUCT_ID
, CII.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CII.LAST_UPDATED_BY LAST_UPDATED_BY
, CII.CREATION_DATE CREATION_DATE
, CII.CREATED_BY CREATED_BY
, CII.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CII.OWNER_PARTY_ACCOUNT_ID PARTY_ACCOUNT_ID
, CII.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, CII.MANUALLY_CREATED_FLAG
, 'Y' MOST_RECENT_FLAG
, CII.INVENTORY_REVISION
, CII.SERIAL_NUMBER
, CII.INSTANCE_TYPE_CODE
, CII.SYSTEM_ID
, CII.LAST_OE_AGREEMENT_ID
, CII.INSTALL_DATE
, CII.LAST_OE_ORDER_LINE_ID
, NULL ORIGINAL_LINE_SERV_DETAIL_ID
, OOH.TRANSACTIONAL_CURR_CODE ORIGINAL_ORDER_CURRENCY_CODE
, OOH.ORDER_NUMBER ORIGINAL_ORDER_NUMBER
, OOL.CREATION_DATE ORIGINAL_ORDER_DATE
, OOL.UNIT_SELLING_PRICE ORIGINAL_NET_AMOUNT
, CIA.SHIP_TO_ADDRESS
, CIA.BILL_TO_ADDRESS
, CII.INSTALL_LOCATION_ID
, CII.QUANTITY
, DECODE(OOL.ACTUAL_SHIPMENT_DATE
, NULL
, 'N'
, 'Y') SHIPPED_FLAG
, NULL DELIVERED_FLAG
, CII.UNIT_OF_MEASURE
, NULL PARENT_CP_ID
, 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.CONTEXT
, CII.INSTANCE_STATUS_ID
, CIPA.PRICING_ATTRIBUTE1
, CIPA.PRICING_ATTRIBUTE2
, CIPA.PRICING_ATTRIBUTE3
, CIPA.PRICING_ATTRIBUTE4
, CIPA.PRICING_ATTRIBUTE5
, CIPA.PRICING_ATTRIBUTE6
, CIPA.PRICING_ATTRIBUTE7
, CIPA.PRICING_ATTRIBUTE8
, CIPA.PRICING_ATTRIBUTE9
, CIPA.PRICING_ATTRIBUTE10
, CIPA.PRICING_ATTRIBUTE11
, CIPA.PRICING_ATTRIBUTE12
, CIPA.PRICING_ATTRIBUTE13
, CIPA.PRICING_ATTRIBUTE14
, CIPA.PRICING_ATTRIBUTE15
, CIPA.PRICING_CONTEXT
, CII.ATTRIBUTE11
, CII.ATTRIBUTE12
, CII.ATTRIBUTE13
, CII.ATTRIBUTE14
, CII.ATTRIBUTE15
, OOL.ACTUAL_SHIPMENT_DATE SHIPPED_DATE
, CIOA.OPERATING_UNIT_ID
, CII.LOT_NUMBER
, CII.INSTANCE_NUMBER
, CII.LAST_OE_PO_NUMBER
, CII.RETURN_BY_DATE
, CII.ACTUAL_RETURN_DATE
, NULL RMA_LINE_ID
, NULL SPLIT_FLAG
, CII.PA_PROJECT_ID
, CII.PA_PROJECT_TASK_ID
, 'Y' CONFIG_ENABLED_FLAG
, CIIR.ACTIVE_START_DATE
, CIIR.ACTIVE_END_DATE
, CSICUMPI_PUB.GET_ROOT_ID(CII.INSTANCE_ID)
, CIIR.OBJECT_ID
, CIIR.RELATIONSHIP_TYPE_CODE
, NULL PLATFORM_VERSION_ID
, CII.OBJECT_VERSION_NUMBER
, CII.MERCHANT_VIEW_FLAG
, CII.CUSTOMER_VIEW_FLAG
, CIPA.PRICING_ATTRIBUTE16
, CIPA.PRICING_ATTRIBUTE17
, CIPA.PRICING_ATTRIBUTE18
, CIPA.PRICING_ATTRIBUTE19
, CIPA.PRICING_ATTRIBUTE20
, CIPA.PRICING_ATTRIBUTE21
, CIPA.PRICING_ATTRIBUTE22
, CIPA.PRICING_ATTRIBUTE23
, CIPA.PRICING_ATTRIBUTE24
, CIPA.PRICING_ATTRIBUTE25
, CIPA.PRICING_ATTRIBUTE26
, CIPA.PRICING_ATTRIBUTE27
, CIPA.PRICING_ATTRIBUTE28
, CIPA.PRICING_ATTRIBUTE29
, CIPA.PRICING_ATTRIBUTE30
, CIPA.PRICING_ATTRIBUTE31
, CIPA.PRICING_ATTRIBUTE32
, CIPA.PRICING_ATTRIBUTE33
, CIPA.PRICING_ATTRIBUTE34
, CIPA.PRICING_ATTRIBUTE35
, CIPA.PRICING_ATTRIBUTE36
, CIPA.PRICING_ATTRIBUTE37
, CIPA.PRICING_ATTRIBUTE38
, CIPA.PRICING_ATTRIBUTE39
, CIPA.PRICING_ATTRIBUTE40
, CIPA.PRICING_ATTRIBUTE41
, CIPA.PRICING_ATTRIBUTE42
, CIPA.PRICING_ATTRIBUTE43
, CIPA.PRICING_ATTRIBUTE44
, CIPA.PRICING_ATTRIBUTE45
, CIPA.PRICING_ATTRIBUTE46
, CIPA.PRICING_ATTRIBUTE47
, CIPA.PRICING_ATTRIBUTE48
, CIPA.PRICING_ATTRIBUTE49
, CIPA.PRICING_ATTRIBUTE50
, CIPA.PRICING_ATTRIBUTE51
, CIPA.PRICING_ATTRIBUTE52
, CIPA.PRICING_ATTRIBUTE53
, CIPA.PRICING_ATTRIBUTE54
, CIPA.PRICING_ATTRIBUTE55
, CIPA.PRICING_ATTRIBUTE56
, CIPA.PRICING_ATTRIBUTE57
, CIPA.PRICING_ATTRIBUTE58
, CIPA.PRICING_ATTRIBUTE59
, CIPA.PRICING_ATTRIBUTE61
, CIPA.PRICING_ATTRIBUTE62
, CIPA.PRICING_ATTRIBUTE63
, CIPA.PRICING_ATTRIBUTE64
, CIPA.PRICING_ATTRIBUTE65
, CIPA.PRICING_ATTRIBUTE66
, CIPA.PRICING_ATTRIBUTE67
, CIPA.PRICING_ATTRIBUTE68
, CIPA.PRICING_ATTRIBUTE69
, CIPA.PRICING_ATTRIBUTE70
, CIPA.PRICING_ATTRIBUTE71
, CIPA.PRICING_ATTRIBUTE72
, CIPA.PRICING_ATTRIBUTE73
, CIPA.PRICING_ATTRIBUTE74
, CIPA.PRICING_ATTRIBUTE75
, CIPA.PRICING_ATTRIBUTE76
, CIPA.PRICING_ATTRIBUTE77
, CIPA.PRICING_ATTRIBUTE78
, CIPA.PRICING_ATTRIBUTE79
, CIPA.PRICING_ATTRIBUTE80
, CIPA.PRICING_ATTRIBUTE81
, CIPA.PRICING_ATTRIBUTE82
, CIPA.PRICING_ATTRIBUTE83
, CIPA.PRICING_ATTRIBUTE84
, CIPA.PRICING_ATTRIBUTE85
, CIPA.PRICING_ATTRIBUTE86
, CIPA.PRICING_ATTRIBUTE87
, CIPA.PRICING_ATTRIBUTE88
, CIPA.PRICING_ATTRIBUTE89
, CIPA.PRICING_ATTRIBUTE90
, CIPA.PRICING_ATTRIBUTE91
, CIPA.PRICING_ATTRIBUTE92
, CIPA.PRICING_ATTRIBUTE93
, CIPA.PRICING_ATTRIBUTE94
, CIPA.PRICING_ATTRIBUTE95
, CIPA.PRICING_ATTRIBUTE96
, CIPA.PRICING_ATTRIBUTE97
, CIPA.PRICING_ATTRIBUTE98
, CIPA.PRICING_ATTRIBUTE99
, CIPA.PRICING_ATTRIBUTE100
, CIPA.PRICING_ATTRIBUTE60
, NULL UPGRADED_STATUS_FLAG
, NULL REQUEST_ID
, NULL PROGRAM_APPLICATION_ID
, NULL PROGRAM_ID
, NULL PROGRAM_UPDATE_DATE
, DECODE(CIS.NAME
, 'EXPIRED'
, 'Y'
, 'N')
, NULL RETURNED_QUANTITY
FROM CSI_ITEM_INSTANCES CII
, CSI_INSTANCE_STATUSES CIS
, CSI_I_PRICING_ATTRIBS CIPA
, CSI_I_ORG_ASSIGNMENTS CIOA
, CSI_II_RELATIONSHIPS CIIR
, CSI_I_PARTIES CIP
, CSI_IP_ACCOUNTS CIA
, OE_ORDER_LINES_ALL OOL
, OE_ORDER_HEADERS_ALL OOH
WHERE CII.INSTANCE_ID = CIOA.INSTANCE_ID(+) AND CIOA.RELATIONSHIP_TYPE_CODE (+) = 'SOLD_FROM' AND CIOA.ACTIVE_END_DATE IS NULL AND CII.INSTANCE_ID = CIPA.INSTANCE_ID (+) AND CII.INSTANCE_ID = CIP.INSTANCE_ID AND CII.LAST_OE_ORDER_LINE_ID = OOL.LINE_ID(+) AND OOL.HEADER_ID = OOH.HEADER_ID(+) AND CIP.RELATIONSHIP_TYPE_CODE = 'OWNER' AND CIP.PARTY_SOURCE_TABLE = 'HZ_PARTIES' AND CIA.RELATIONSHIP_TYPE_CODE(+) = 'OWNER' AND CIA.ACTIVE_END_DATE (+) IS NULL AND CIP.INSTANCE_PARTY_ID = CIA.INSTANCE_PARTY_ID(+) AND CIS.INSTANCE_STATUS_ID = CII.INSTANCE_STATUS_ID AND CII.INSTANCE_ID = CIIR.SUBJECT_ID(+) AND CIIR.RELATIONSHIP_TYPE_CODE (+) = 'COMPONENT-OF' AND CIIR.ACTIVE_END_DATE (+) IS NULL AND NOT EXISTS ( SELECT 1 FROM CSI_INSTALL_PARAMETERS CIPM WHERE CIPM.INTERNAL_PARTY_ID = CIP.PARTY_ID )