VIEW: APPS.CSI_CUST_PROD_MAINT_V

Source

View Text - Preformatted

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 )

View Text - HTML Formatted

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 )