VIEW: APPS.CSI_CUSTOMER_PRODUCTS_ALL_V

Source

View Text - Preformatted

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 )

View Text - HTML Formatted

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 )