VIEW: APPS.CS_CP_AUDIT_MAINT_V

Source

View Text - Preformatted

SELECT cpa.ROWID ROW_ID , cpa.CUSTOMER_PRODUCT_ID , cpa.LAST_UPDATE_DATE , cpa.LAST_UPDATED_BY , cpa.CREATION_DATE , cpa.CREATED_BY , cpa.LAST_UPDATE_LOGIN , cpa.OLD_CUSTOMER_ID , cpa.CURRENT_CUSTOMER_ID , cpa.OLD_SYSTEM_ID , cpa.CURRENT_SYSTEM_ID , cpa.OLD_PRODUCT_AGREEMENT_ID , cpa.CURRENT_PRODUCT_AGREEMENT_ID , cpa.OLD_PLATFORM_VERSION_ID , cpa.CURRENT_PLATFORM_VERSION_ID , cpa.ATTRIBUTE1 , cpa.ATTRIBUTE2 , cpa.ATTRIBUTE3 , cpa.ATTRIBUTE4 , cpa.ATTRIBUTE5 , cpa.ATTRIBUTE6 , cpa.ATTRIBUTE7 , cpa.ATTRIBUTE8 , cpa.ATTRIBUTE9 , cpa.ATTRIBUTE10 , cpa.CONTEXT , cpa.ATTRIBUTE11 , cpa.ATTRIBUTE12 , cpa.ATTRIBUTE13 , cpa.ATTRIBUTE14 , cpa.ATTRIBUTE15 , cpa.COMMENTS , cpa.OLD_CP_STATUS_ID , cpa.CURRENT_CP_STATUS_ID , cpa.OLD_TYPE_CODE , cpa.CURRENT_TYPE_CODE , fnd.user_name UPDATED_BY , hzp1.party_name OLD_CUSTOMER , hzp2.party_name NEW_CUSTOMER , cs1.name OLD_SYSTEM , cs2.name NEW_SYSTEM , pag1.name OLD_PRODUCT_AGREEMENT , pag2.name NEW_PRODUCT_AGREEMENT , NULL OLD_PLATFORM_NAME , NULL NEW_PLATFORM_NAME , cps1.name OLD_CP_STATUS , cps2.name NEW_CP_STATUS , lu1.meaning OLD_TYPE , lu2.meaning NEW_TYPE , cpa.split_cp_id , ccp.reference_number SPLIT_CP_REFERENCE_NUMBER , cpa.old_cp_quantity , cpa.current_cp_quantity , cpa.cp_split_reason_code , lu.meaning CP_SPLIT_REASON FROM CS_CP_AUDIT CPA , HZ_CUST_ACCOUNTS CUST1 , HZ_CUST_ACCOUNTS CUST2 , HZ_PARTIES HZP1 , HZ_PARTIES HZP2 , CS_SYSTEMS CS1 , CS_SYSTEMS CS2 , OE_AGREEMENTS PAG1 , OE_AGREEMENTS PAG2 , FND_USER FND , CS_CUSTOMER_PRODUCT_STATUSES CPS1 , CS_CUSTOMER_PRODUCT_STATUSES CPS2 , CS_CUSTOMER_PRODUCTS_ALL CCP , CS_LOOKUPS LU1 , CS_LOOKUPS LU2 , CS_LOOKUPS LU WHERE fnd.user_id = cpa.created_by AND cpa.old_customer_id = cust1.cust_account_id(+) AND cust1.party_id = hzp1.party_id AND cpa.current_customer_id = cust2.cust_account_id(+) AND cust2.party_id = hzp2.party_id AND cpa.old_system_id = cs1.system_id(+) AND cpa.current_system_id = cs2.system_id(+) AND cpa.old_product_agreement_id = pag1.agreement_id(+) AND cpa.current_product_agreement_id = pag2.agreement_id(+) AND cpa.old_cp_status_id = cps1.customer_product_status_id(+) AND cpa.current_cp_status_id = cps2.customer_product_status_id(+) AND cpa.split_cp_id = ccp.customer_product_id(+) AND cpa.old_type_code = lu1.lookup_code(+) AND lu1.lookup_type(+) = 'CUSTOMER_PRODUCT_TYPE' AND cpa.current_type_code = lu2.lookup_code(+) AND lu2.lookup_type(+) = 'CUSTOMER_PRODUCT_TYPE' AND cpa.cp_split_reason_code = lu.lookup_code(+) AND lu.lookup_type(+) = 'CS_SPLIT_CP_REASON'

View Text - HTML Formatted

SELECT CPA.ROWID ROW_ID
, CPA.CUSTOMER_PRODUCT_ID
, CPA.LAST_UPDATE_DATE
, CPA.LAST_UPDATED_BY
, CPA.CREATION_DATE
, CPA.CREATED_BY
, CPA.LAST_UPDATE_LOGIN
, CPA.OLD_CUSTOMER_ID
, CPA.CURRENT_CUSTOMER_ID
, CPA.OLD_SYSTEM_ID
, CPA.CURRENT_SYSTEM_ID
, CPA.OLD_PRODUCT_AGREEMENT_ID
, CPA.CURRENT_PRODUCT_AGREEMENT_ID
, CPA.OLD_PLATFORM_VERSION_ID
, CPA.CURRENT_PLATFORM_VERSION_ID
, CPA.ATTRIBUTE1
, CPA.ATTRIBUTE2
, CPA.ATTRIBUTE3
, CPA.ATTRIBUTE4
, CPA.ATTRIBUTE5
, CPA.ATTRIBUTE6
, CPA.ATTRIBUTE7
, CPA.ATTRIBUTE8
, CPA.ATTRIBUTE9
, CPA.ATTRIBUTE10
, CPA.CONTEXT
, CPA.ATTRIBUTE11
, CPA.ATTRIBUTE12
, CPA.ATTRIBUTE13
, CPA.ATTRIBUTE14
, CPA.ATTRIBUTE15
, CPA.COMMENTS
, CPA.OLD_CP_STATUS_ID
, CPA.CURRENT_CP_STATUS_ID
, CPA.OLD_TYPE_CODE
, CPA.CURRENT_TYPE_CODE
, FND.USER_NAME UPDATED_BY
, HZP1.PARTY_NAME OLD_CUSTOMER
, HZP2.PARTY_NAME NEW_CUSTOMER
, CS1.NAME OLD_SYSTEM
, CS2.NAME NEW_SYSTEM
, PAG1.NAME OLD_PRODUCT_AGREEMENT
, PAG2.NAME NEW_PRODUCT_AGREEMENT
, NULL OLD_PLATFORM_NAME
, NULL NEW_PLATFORM_NAME
, CPS1.NAME OLD_CP_STATUS
, CPS2.NAME NEW_CP_STATUS
, LU1.MEANING OLD_TYPE
, LU2.MEANING NEW_TYPE
, CPA.SPLIT_CP_ID
, CCP.REFERENCE_NUMBER SPLIT_CP_REFERENCE_NUMBER
, CPA.OLD_CP_QUANTITY
, CPA.CURRENT_CP_QUANTITY
, CPA.CP_SPLIT_REASON_CODE
, LU.MEANING CP_SPLIT_REASON
FROM CS_CP_AUDIT CPA
, HZ_CUST_ACCOUNTS CUST1
, HZ_CUST_ACCOUNTS CUST2
, HZ_PARTIES HZP1
, HZ_PARTIES HZP2
, CS_SYSTEMS CS1
, CS_SYSTEMS CS2
, OE_AGREEMENTS PAG1
, OE_AGREEMENTS PAG2
, FND_USER FND
, CS_CUSTOMER_PRODUCT_STATUSES CPS1
, CS_CUSTOMER_PRODUCT_STATUSES CPS2
, CS_CUSTOMER_PRODUCTS_ALL CCP
, CS_LOOKUPS LU1
, CS_LOOKUPS LU2
, CS_LOOKUPS LU
WHERE FND.USER_ID = CPA.CREATED_BY
AND CPA.OLD_CUSTOMER_ID = CUST1.CUST_ACCOUNT_ID(+)
AND CUST1.PARTY_ID = HZP1.PARTY_ID
AND CPA.CURRENT_CUSTOMER_ID = CUST2.CUST_ACCOUNT_ID(+)
AND CUST2.PARTY_ID = HZP2.PARTY_ID
AND CPA.OLD_SYSTEM_ID = CS1.SYSTEM_ID(+)
AND CPA.CURRENT_SYSTEM_ID = CS2.SYSTEM_ID(+)
AND CPA.OLD_PRODUCT_AGREEMENT_ID = PAG1.AGREEMENT_ID(+)
AND CPA.CURRENT_PRODUCT_AGREEMENT_ID = PAG2.AGREEMENT_ID(+)
AND CPA.OLD_CP_STATUS_ID = CPS1.CUSTOMER_PRODUCT_STATUS_ID(+)
AND CPA.CURRENT_CP_STATUS_ID = CPS2.CUSTOMER_PRODUCT_STATUS_ID(+)
AND CPA.SPLIT_CP_ID = CCP.CUSTOMER_PRODUCT_ID(+)
AND CPA.OLD_TYPE_CODE = LU1.LOOKUP_CODE(+)
AND LU1.LOOKUP_TYPE(+) = 'CUSTOMER_PRODUCT_TYPE'
AND CPA.CURRENT_TYPE_CODE = LU2.LOOKUP_CODE(+)
AND LU2.LOOKUP_TYPE(+) = 'CUSTOMER_PRODUCT_TYPE'
AND CPA.CP_SPLIT_REASON_CODE = LU.LOOKUP_CODE(+)
AND LU.LOOKUP_TYPE(+) = 'CS_SPLIT_CP_REASON'