VIEW: APPS.CSI_CP_FORM_GRID_V

Source

View Text - Preformatted

SELECT CII.ROWID ROW_ID , HZA.ACCOUNT_NUMBER ACCOUNT_NUMBER, HZP.PARTY_NAME PRINCIPAL_PARTY_NAME, 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, CIA.PARTY_ACCOUNT_ID CUSTOMER_ID , CII.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, CII.MANUALLY_CREATED_FLAG MANUALLY_CREATED_FLAG, 'Y' MOST_RECENT_FLAG , CII.INVENTORY_REVISION CURRENT_CP_REVISION_ID , CII.SERIAL_NUMBER CURRENT_SERIAL_NUMBER , CII.INSTANCE_TYPE_CODE TYPE_CODE , CII.SYSTEM_ID SYSTEM_ID, CII.INSTALL_DATE INSTALLATION_DATE , CII.LAST_OE_ORDER_LINE_ID ORIGINAL_ORDER_LINE_ID, OOH.ORDERED_DATE ORIGINAL_ORDER_DATE, OOH.ORDER_NUMBER ORIGINAL_ORDER_NUMBER, OOL.LINE_NUMBER ORIGINAL_ORDER_LINE_NUMBER, OOL.LINK_TO_LINE_ID ORIGINAL_LINK_TO_ORDER_LINE_ID, NULL ORIGINAL_LINE_SERV_DETAIL_ID , OOL.UNIT_SELLING_PRICE ORIGINAL_NET_AMOUNT, OOH.PRICE_LIST_ID PRICE_LIST_ID, CIA.SHIP_TO_ADDRESS SHIP_TO_SITE_USE_ID , CIA.BILL_TO_ADDRESS BILL_TO_SITE_USE_ID , CII.INSTALL_LOCATION_ID INSTALL_SITE_USE_ID , CII.QUANTITY QUANTITY, NULL SHIPPED_FLAG , NULL DELIVERED_FLAG , CII.UNIT_OF_MEASURE UNIT_OF_MEASURE_CODE, NULL PARENT_CP_ID, CII.ACTIVE_START_DATE START_DATE_ACTIVE, CII.ACTIVE_END_DATE END_DATE_ACTIVE, 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.CONTEXT CONTEXT, CII.INSTANCE_STATUS_ID CUSTOMER_PRODUCT_STATUS_ID , NULL SHIPPED_DATE, CIOA.OPERATING_UNIT_ID ORG_ID , CII.INSTANCE_NUMBER REFERENCE_NUMBER , OOH.CUST_PO_NUMBER PURCHASE_ORDER_NUM, CII.LOT_NUMBER CP_LOT_NUMBER, NULL SPLIT_FLAG, CIS.NAME CP_STATUS , NULL CANCELLED_FLAG , CIS.TERMINATED_FLAG TERMINATED_FLAG, CIS.STATUS_CHANGE_ALLOWED_FLAG STATUS_CHANGE_ALLOWED_FLAG, CIS.INCIDENT_ALLOWED_FLAG INCIDENT_ALLOWED_FLAG, CIS.SERVICE_ORDER_ALLOWED_FLAG SERVICE_ORDER_ALLOWED_FLAG, CSV.NAME SYSTEM , CL.MEANING CP_TYPE, A1.NAME PRODUCT_AGREEMENT, CII.LAST_OE_AGREEMENT_ID PRODUCT_AGREEMENT_ID, CII.INVENTORY_REVISION REVISION, SI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE, SI.DESCRIPTION PRODUCT_DESCRIPTION , NULL SERVICED_STATUS , NULL SERVICED_STATUS_FLAG , SI.BOM_ITEM_TYPE BOM_ITEM_TYPE, CII.RETURN_BY_DATE RETURN_BY_DATE, CII.ACTUAL_RETURN_DATE ACTUAL_RETURNED_DATE, 'Y' CONFIG_ENABLED_FLAG , CIR.ACTIVE_START_DATE CONFIG_START_DATE, CIR.ACTIVE_END_DATE CONFIG_END_DATE, NULL CONFIG_ROOT_ID , CIR.OBJECT_ID CONFIG_PARENT_ID , CIR.RELATIONSHIP_TYPE_CODE CONFIG_TYPE, CL3.MEANING CP_CONFIG_TYPE, NULL RMA_LINE_ID, CII.PA_PROJECT_ID PROJECT_ID, CII.PA_PROJECT_TASK_ID TASK_ID , NULL PLATFORM_VERSION_ID, NULL PLATFORM_VERSION, KFV.CONCATENATED_SEGMENTS 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, OPER.NAME ORGANIZATION_NAME, CII.MERCHANT_VIEW_FLAG MERCHANT_VIEW_FLAG, CII.CUSTOMER_VIEW_FLAG CUSTOMER_VIEW_FLAG, CII.EXTERNAL_REFERENCE EXTERNAL_REFERENCE FROM OE_AGREEMENTS A1, HZ_CUST_ACCOUNTS HZA, HZ_PARTIES HZP, CSI_SYSTEMS_VL CSV, CSI_ITEM_INSTANCES CII, CSI_II_RELATIONSHIPS CIR, CSI_I_ORG_ASSIGNMENTS CIOA, CSI_IP_ACCOUNTS CIA, CSI_INSTANCE_STATUSES CIS, CSI_I_PARTIES CIP, CSI_LOOKUPS CL, CSI_LOOKUPS CL3, MTL_SYSTEM_ITEMS SI, MTL_SYSTEM_ITEMS_KFV KFV, OE_ORDER_HEADERS_ALL OOH, OE_ORDER_LINES_ALL OOL, HR_OPERATING_UNITS OPER WHERE 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 CII.SYSTEM_ID = CSV.SYSTEM_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 CII.OWNER_PARTY_ACCOUNT_ID = HZA.CUST_ACCOUNT_ID AND HZA.PARTY_ID = HZP.PARTY_ID AND CIOA.OPERATING_UNIT_ID = OPER.ORGANIZATION_ID(+) AND CII.INSTANCE_ID = CIOA.INSTANCE_ID (+) AND CIOA.RELATIONSHIP_TYPE_CODE (+) = 'SOLD_FROM' AND CII.INSTANCE_ID = CIP.INSTANCE_ID AND CIS.INSTANCE_STATUS_ID = CII.INSTANCE_STATUS_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 = 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 )

View Text - HTML Formatted

SELECT CII.ROWID ROW_ID
, HZA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, HZP.PARTY_NAME PRINCIPAL_PARTY_NAME
, 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
, CIA.PARTY_ACCOUNT_ID CUSTOMER_ID
, CII.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, CII.MANUALLY_CREATED_FLAG MANUALLY_CREATED_FLAG
, 'Y' MOST_RECENT_FLAG
, CII.INVENTORY_REVISION CURRENT_CP_REVISION_ID
, CII.SERIAL_NUMBER CURRENT_SERIAL_NUMBER
, CII.INSTANCE_TYPE_CODE TYPE_CODE
, CII.SYSTEM_ID SYSTEM_ID
, CII.INSTALL_DATE INSTALLATION_DATE
, CII.LAST_OE_ORDER_LINE_ID ORIGINAL_ORDER_LINE_ID
, OOH.ORDERED_DATE ORIGINAL_ORDER_DATE
, OOH.ORDER_NUMBER ORIGINAL_ORDER_NUMBER
, OOL.LINE_NUMBER ORIGINAL_ORDER_LINE_NUMBER
, OOL.LINK_TO_LINE_ID ORIGINAL_LINK_TO_ORDER_LINE_ID
, NULL ORIGINAL_LINE_SERV_DETAIL_ID
, OOL.UNIT_SELLING_PRICE ORIGINAL_NET_AMOUNT
, OOH.PRICE_LIST_ID PRICE_LIST_ID
, CIA.SHIP_TO_ADDRESS SHIP_TO_SITE_USE_ID
, CIA.BILL_TO_ADDRESS BILL_TO_SITE_USE_ID
, CII.INSTALL_LOCATION_ID INSTALL_SITE_USE_ID
, CII.QUANTITY QUANTITY
, NULL SHIPPED_FLAG
, NULL DELIVERED_FLAG
, CII.UNIT_OF_MEASURE UNIT_OF_MEASURE_CODE
, NULL PARENT_CP_ID
, CII.ACTIVE_START_DATE START_DATE_ACTIVE
, CII.ACTIVE_END_DATE END_DATE_ACTIVE
, 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.CONTEXT CONTEXT
, CII.INSTANCE_STATUS_ID CUSTOMER_PRODUCT_STATUS_ID
, NULL SHIPPED_DATE
, CIOA.OPERATING_UNIT_ID ORG_ID
, CII.INSTANCE_NUMBER REFERENCE_NUMBER
, OOH.CUST_PO_NUMBER PURCHASE_ORDER_NUM
, CII.LOT_NUMBER CP_LOT_NUMBER
, NULL SPLIT_FLAG
, CIS.NAME CP_STATUS
, NULL CANCELLED_FLAG
, CIS.TERMINATED_FLAG TERMINATED_FLAG
, CIS.STATUS_CHANGE_ALLOWED_FLAG STATUS_CHANGE_ALLOWED_FLAG
, CIS.INCIDENT_ALLOWED_FLAG INCIDENT_ALLOWED_FLAG
, CIS.SERVICE_ORDER_ALLOWED_FLAG SERVICE_ORDER_ALLOWED_FLAG
, CSV.NAME SYSTEM
, CL.MEANING CP_TYPE
, A1.NAME PRODUCT_AGREEMENT
, CII.LAST_OE_AGREEMENT_ID PRODUCT_AGREEMENT_ID
, CII.INVENTORY_REVISION REVISION
, SI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, SI.DESCRIPTION PRODUCT_DESCRIPTION
, NULL SERVICED_STATUS
, NULL SERVICED_STATUS_FLAG
, SI.BOM_ITEM_TYPE BOM_ITEM_TYPE
, CII.RETURN_BY_DATE RETURN_BY_DATE
, CII.ACTUAL_RETURN_DATE ACTUAL_RETURNED_DATE
, 'Y' CONFIG_ENABLED_FLAG
, CIR.ACTIVE_START_DATE CONFIG_START_DATE
, CIR.ACTIVE_END_DATE CONFIG_END_DATE
, NULL CONFIG_ROOT_ID
, CIR.OBJECT_ID CONFIG_PARENT_ID
, CIR.RELATIONSHIP_TYPE_CODE CONFIG_TYPE
, CL3.MEANING CP_CONFIG_TYPE
, NULL RMA_LINE_ID
, CII.PA_PROJECT_ID PROJECT_ID
, CII.PA_PROJECT_TASK_ID TASK_ID
, NULL PLATFORM_VERSION_ID
, NULL PLATFORM_VERSION
, KFV.CONCATENATED_SEGMENTS 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
, OPER.NAME ORGANIZATION_NAME
, CII.MERCHANT_VIEW_FLAG MERCHANT_VIEW_FLAG
, CII.CUSTOMER_VIEW_FLAG CUSTOMER_VIEW_FLAG
, CII.EXTERNAL_REFERENCE EXTERNAL_REFERENCE
FROM OE_AGREEMENTS A1
, HZ_CUST_ACCOUNTS HZA
, HZ_PARTIES HZP
, CSI_SYSTEMS_VL CSV
, CSI_ITEM_INSTANCES CII
, CSI_II_RELATIONSHIPS CIR
, CSI_I_ORG_ASSIGNMENTS CIOA
, CSI_IP_ACCOUNTS CIA
, CSI_INSTANCE_STATUSES CIS
, CSI_I_PARTIES CIP
, CSI_LOOKUPS CL
, CSI_LOOKUPS CL3
, MTL_SYSTEM_ITEMS SI
, MTL_SYSTEM_ITEMS_KFV KFV
, OE_ORDER_HEADERS_ALL OOH
, OE_ORDER_LINES_ALL OOL
, HR_OPERATING_UNITS OPER
WHERE 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 CII.SYSTEM_ID = CSV.SYSTEM_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 CII.OWNER_PARTY_ACCOUNT_ID = HZA.CUST_ACCOUNT_ID
AND HZA.PARTY_ID = HZP.PARTY_ID
AND CIOA.OPERATING_UNIT_ID = OPER.ORGANIZATION_ID(+)
AND CII.INSTANCE_ID = CIOA.INSTANCE_ID (+)
AND CIOA.RELATIONSHIP_TYPE_CODE (+) = 'SOLD_FROM'
AND CII.INSTANCE_ID = CIP.INSTANCE_ID
AND CIS.INSTANCE_STATUS_ID = CII.INSTANCE_STATUS_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 = 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 )