VIEW: APPS.CSC_CUSTOMER_RESPONSE_V

Source

View Text - Preformatted

SELECT PARTY.ROWID , PARTY.PARTY_ID , PARTY.PARTY_NUMBER , PARTY.PARTY_TYPE , PARTY.PARTY_NAME , PARTY_REL.SUBJECT_ID , PARTY_REL.OBJECT_ID , PARTY.LAST_UPDATE_DATE , PARTY.LAST_UPDATED_BY , PARTY.LAST_UPDATE_LOGIN , PARTY.CREATION_DATE , PARTY.CREATED_BY , OBJ_PARTY.PARTY_TYPE , OBJ_PARTY.PARTY_NAME , OBJ_PARTY.PARTY_NAME , OBJ_PARTY.GROUP_TYPE , OBJ_PARTY.PERSON_FIRST_NAME , OBJ_PARTY.PERSON_MIDDLE_NAME , OBJ_PARTY.PERSON_LAST_NAME , OBJ_PARTY.PERSON_NAME_SUFFIX , OBJ_PARTY.PERSON_TITLE , OBJ_PARTY.KNOWN_AS , OBJ_PARTY.SIC_CODE , ORG_PROFILE.ANALYSIS_FY , ORG_PROFILE.FISCAL_YEAREND_MONTH , ORG_PROFILE.EMPLOYEES_TOTAL , ORG_PROFILE.CURR_FY_POTENTIAL_REVENUE , ORG_PROFILE.NEXT_FY_POTENTIAL_REVENUE , ORG_PROFILE.TAX_REFERENCE , ORG_PROFILE.YEAR_ESTABLISHED , ORG_PROFILE.INTERNAL_FLAG , ORG_PROFILE.PUBLIC_PRIVATE_OWNERSHIP_FLAG , ORG_PROFILE.DUNS_NUMBER , ORG_PROFILE.JGZZ_FISCAL_CODE , SUB_PARTY.PARTY_TYPE , SUB_PARTY.PERSON_FIRST_NAME , SUB_PARTY.PERSON_MIDDLE_NAME , SUB_PARTY.PERSON_LAST_NAME , SUB_PARTY.PERSON_NAME_SUFFIX , SUB_PARTY.PERSON_TITLE , PERSON_PROFILE_SUB.KNOWN_AS , PERSON_PROFILE_SUB.PERSON_NAME_PHONETIC , ORG_CONT.JOB_TITLE , ORG_CONT.DECISION_MAKER_FLAG , ORG_CONT.JOB_TITLE_CODE , ORG_CONT.MANAGED_BY , ORG_CONT.NATIVE_LANGUAGE , ORG_CONT.REFERENCE_USE_FLAG , ORG_CONT.OTHER_LANGUAGE_1 , ORG_CONT.OTHER_LANGUAGE_2 , ORG_CONT.RANK , SUB_PARTY.PERSON_PRE_NAME_ADJUNCT , ORG_CONT.DEPARTMENT_CODE , ORG_CONT.DEPARTMENT , PARTY_REL.RELATIONSHIP_CODE , PARTY.ADDRESS1 || DECODE(PARTY.ADDRESS1,',',DECODE(PARTY.ADDRESS2,',',',')) || PARTY.ADDRESS2 || PARTY.ADDRESS3 || DECODE(PARTY.ADDRESS3,',',DECODE(PARTY.ADDRESS4,',',',')) || PARTY.ADDRESS4 , PARTY.CITY , PARTY.STATE , PARTY.PROVINCE , PARTY.POSTAL_CODE , PARTY.COUNTRY , PARTY.ATTRIBUTE_CATEGORY , PARTY.ATTRIBUTE1 , PARTY.ATTRIBUTE2 , PARTY.ATTRIBUTE3 , PARTY.ATTRIBUTE4 , PARTY.ATTRIBUTE5 , PARTY.ATTRIBUTE6 , PARTY.ATTRIBUTE7 , PARTY.ATTRIBUTE8 , PARTY.ATTRIBUTE9 , PARTY.ATTRIBUTE10 , PARTY.ATTRIBUTE11 , PARTY.ATTRIBUTE12 , PARTY.ATTRIBUTE13 , PARTY.ATTRIBUTE14 , PARTY.ATTRIBUTE15 , PARTY.GLOBAL_ATTRIBUTE_CATEGORY , PARTY.GLOBAL_ATTRIBUTE1 , PARTY.GLOBAL_ATTRIBUTE2 , PARTY.GLOBAL_ATTRIBUTE3 , PARTY.GLOBAL_ATTRIBUTE4 , PARTY.GLOBAL_ATTRIBUTE5 , PARTY.GLOBAL_ATTRIBUTE6 , PARTY.GLOBAL_ATTRIBUTE7 , PARTY.GLOBAL_ATTRIBUTE8 , PARTY.GLOBAL_ATTRIBUTE9 , PARTY.GLOBAL_ATTRIBUTE10 , PARTY.GLOBAL_ATTRIBUTE11 , PARTY.GLOBAL_ATTRIBUTE12 , PARTY.GLOBAL_ATTRIBUTE13 , PARTY.GLOBAL_ATTRIBUTE14 , PARTY.GLOBAL_ATTRIBUTE15 , PARTY.GLOBAL_ATTRIBUTE16 , PARTY.GLOBAL_ATTRIBUTE17 , PARTY.GLOBAL_ATTRIBUTE18 , PARTY.GLOBAL_ATTRIBUTE19 , PARTY.GLOBAL_ATTRIBUTE20 FROM HZ_PARTIES PARTY, HZ_PARTIES SUB_PARTY, HZ_PARTIES OBJ_PARTY, HZ_RELATIONSHIPS PARTY_REL, HZ_ORGANIZATION_PROFILES ORG_PROFILE, HZ_PERSON_PROFILES PERSON_PROFILE_SUB, HZ_PERSON_PROFILES PERSON_PROFILE_OBJ, HZ_ORG_CONTACTS ORG_CONT WHERE PARTY.PARTY_ID = PARTY_REL.PARTY_ID AND PARTY_REL.SUBJECT_ID = SUB_PARTY.PARTY_ID AND PARTY_REL.OBJECT_ID = OBJ_PARTY.PARTY_ID AND OBJ_PARTY.PARTY_ID = ORG_PROFILE.PARTY_ID(+) AND OBJ_PARTY.PARTY_ID = PERSON_PROFILE_OBJ.PARTY_ID(+) AND SUB_PARTY.PARTY_ID = PERSON_PROFILE_SUB.PARTY_ID(+) AND ORG_CONT.PARTY_RELATIONSHIP_ID(+) = PARTY_REL.RELATIONSHIP_ID AND party_rel.subject_table_name = 'HZ_PARTIES' AND party_rel.object_table_name = 'HZ_PARTIES' AND party_rel.directional_flag = 'F' UNION SELECT PARTY.ROWID , PARTY.PARTY_ID , PARTY.PARTY_NUMBER , PARTY.PARTY_TYPE , PARTY.PARTY_NAME , PARTY.PARTY_ID , PARTY.PARTY_ID , PARTY.LAST_UPDATE_DATE , PARTY.LAST_UPDATED_BY , PARTY.LAST_UPDATE_LOGIN , PARTY.CREATION_DATE , PARTY.CREATED_BY , PARTY.PARTY_TYPE , NULL PARTY_NAME , NULL PARTY_NAME , NULL GROUP_TYPE , PARTY.PERSON_FIRST_NAME , PARTY.PERSON_MIDDLE_NAME , PARTY.PERSON_LAST_NAME , PARTY.PERSON_NAME_SUFFIX , PARTY.PERSON_TITLE , NULL ALIAS , NULL SIC_CODE , NULL ANALYSIS_FY , NULL ,TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , NULL TAX_REFERENCE , TO_NUMBER(NULL) , NULL INTERNAL_FLAG , NULL , TO_NUMBER(NULL) DUNS_NUMBER , NULL JGZZ_FISCAL_CODE , PARTY.PARTY_TYPE , PARTY.PERSON_FIRST_NAME , PARTY.PERSON_MIDDLE_NAME , PARTY.PERSON_LAST_NAME , PARTY.PERSON_NAME_SUFFIX , PARTY.PERSON_TITLE , NULL KNOWN_AS , NULL PERSON_NAME_PHONETIC , NULL JOB_TITLE , NULL DECISION_MAKER_FLAG , NULL JOB_TITLE_CODE , TO_NUMBER(NULL) MANAGED_BY , NULL NATIVE_LANGUAGE , NULL REFERENCE_USE_FLAG , NULL OTHER_LANGUAGE_1 , NULL OTHER_LANGUAGE_2 , NULL RANK , NULL PERSON_PRE_NAME_ADJUNCT , NULL DEPARTMENT_CODE , NULL DEPARTMENT , NULL PARTY_RELATIONSHIP_TYPE , PARTY.ADDRESS1 || DECODE(PARTY.ADDRESS1,',',DECODE(PARTY.ADDRESS2,',',',')) || PARTY.ADDRESS2 || PARTY.ADDRESS3 || DECODE(PARTY.ADDRESS3,',',DECODE(PARTY.ADDRESS4,',',',')) || PARTY.ADDRESS4 ADDRESS , PARTY.CITY , PARTY.STATE , PARTY.PROVINCE , PARTY.POSTAL_CODE , PARTY.COUNTRY , PARTY.ATTRIBUTE_CATEGORY , PARTY.ATTRIBUTE1 , PARTY.ATTRIBUTE2 , PARTY.ATTRIBUTE3 , PARTY.ATTRIBUTE4 , PARTY.ATTRIBUTE5 , PARTY.ATTRIBUTE6 , PARTY.ATTRIBUTE7 , PARTY.ATTRIBUTE8 , PARTY.ATTRIBUTE9 , PARTY.ATTRIBUTE10 , PARTY.ATTRIBUTE11 , PARTY.ATTRIBUTE12 , PARTY.ATTRIBUTE13 , PARTY.ATTRIBUTE14 , PARTY.ATTRIBUTE15 , PARTY.GLOBAL_ATTRIBUTE_CATEGORY , PARTY.GLOBAL_ATTRIBUTE1 , PARTY.GLOBAL_ATTRIBUTE2 , PARTY.GLOBAL_ATTRIBUTE3 , PARTY.GLOBAL_ATTRIBUTE4 , PARTY.GLOBAL_ATTRIBUTE5 , PARTY.GLOBAL_ATTRIBUTE6 , PARTY.GLOBAL_ATTRIBUTE7 , PARTY.GLOBAL_ATTRIBUTE8 , PARTY.GLOBAL_ATTRIBUTE9 , PARTY.GLOBAL_ATTRIBUTE10 , PARTY.GLOBAL_ATTRIBUTE11 , PARTY.GLOBAL_ATTRIBUTE12 , PARTY.GLOBAL_ATTRIBUTE13 , PARTY.GLOBAL_ATTRIBUTE14 , PARTY.GLOBAL_ATTRIBUTE15 , PARTY.GLOBAL_ATTRIBUTE16 , PARTY.GLOBAL_ATTRIBUTE17 , PARTY.GLOBAL_ATTRIBUTE18 , PARTY.GLOBAL_ATTRIBUTE19 , PARTY.GLOBAL_ATTRIBUTE20 FROM HZ_PARTIES PARTY WHERE NOT EXISTS (SELECT 'x' FROM HZ_RELATIONSHIPS PARTY_REL WHERE PARTY.PARTY_ID = PARTY_REL.OBJECT_ID AND party_rel.subject_table_name = 'HZ_PARTIES' AND party_rel.object_table_name = 'HZ_PARTIES' AND party_rel.directional_flag = 'F') AND PARTY.PARTY_TYPE = 'PERSON'

View Text - HTML Formatted

SELECT PARTY.ROWID
, PARTY.PARTY_ID
, PARTY.PARTY_NUMBER
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, PARTY_REL.SUBJECT_ID
, PARTY_REL.OBJECT_ID
, PARTY.LAST_UPDATE_DATE
, PARTY.LAST_UPDATED_BY
, PARTY.LAST_UPDATE_LOGIN
, PARTY.CREATION_DATE
, PARTY.CREATED_BY
, OBJ_PARTY.PARTY_TYPE
, OBJ_PARTY.PARTY_NAME
, OBJ_PARTY.PARTY_NAME
, OBJ_PARTY.GROUP_TYPE
, OBJ_PARTY.PERSON_FIRST_NAME
, OBJ_PARTY.PERSON_MIDDLE_NAME
, OBJ_PARTY.PERSON_LAST_NAME
, OBJ_PARTY.PERSON_NAME_SUFFIX
, OBJ_PARTY.PERSON_TITLE
, OBJ_PARTY.KNOWN_AS
, OBJ_PARTY.SIC_CODE
, ORG_PROFILE.ANALYSIS_FY
, ORG_PROFILE.FISCAL_YEAREND_MONTH
, ORG_PROFILE.EMPLOYEES_TOTAL
, ORG_PROFILE.CURR_FY_POTENTIAL_REVENUE
, ORG_PROFILE.NEXT_FY_POTENTIAL_REVENUE
, ORG_PROFILE.TAX_REFERENCE
, ORG_PROFILE.YEAR_ESTABLISHED
, ORG_PROFILE.INTERNAL_FLAG
, ORG_PROFILE.PUBLIC_PRIVATE_OWNERSHIP_FLAG
, ORG_PROFILE.DUNS_NUMBER
, ORG_PROFILE.JGZZ_FISCAL_CODE
, SUB_PARTY.PARTY_TYPE
, SUB_PARTY.PERSON_FIRST_NAME
, SUB_PARTY.PERSON_MIDDLE_NAME
, SUB_PARTY.PERSON_LAST_NAME
, SUB_PARTY.PERSON_NAME_SUFFIX
, SUB_PARTY.PERSON_TITLE
, PERSON_PROFILE_SUB.KNOWN_AS
, PERSON_PROFILE_SUB.PERSON_NAME_PHONETIC
, ORG_CONT.JOB_TITLE
, ORG_CONT.DECISION_MAKER_FLAG
, ORG_CONT.JOB_TITLE_CODE
, ORG_CONT.MANAGED_BY
, ORG_CONT.NATIVE_LANGUAGE
, ORG_CONT.REFERENCE_USE_FLAG
, ORG_CONT.OTHER_LANGUAGE_1
, ORG_CONT.OTHER_LANGUAGE_2
, ORG_CONT.RANK
, SUB_PARTY.PERSON_PRE_NAME_ADJUNCT
, ORG_CONT.DEPARTMENT_CODE
, ORG_CONT.DEPARTMENT
, PARTY_REL.RELATIONSHIP_CODE
, PARTY.ADDRESS1 || DECODE(PARTY.ADDRESS1
, '
, '
, DECODE(PARTY.ADDRESS2
, '
, '
, '
, ')) || PARTY.ADDRESS2 || PARTY.ADDRESS3 || DECODE(PARTY.ADDRESS3
, '
, '
, DECODE(PARTY.ADDRESS4
, '
, '
, '
, ')) || PARTY.ADDRESS4
, PARTY.CITY
, PARTY.STATE
, PARTY.PROVINCE
, PARTY.POSTAL_CODE
, PARTY.COUNTRY
, PARTY.ATTRIBUTE_CATEGORY
, PARTY.ATTRIBUTE1
, PARTY.ATTRIBUTE2
, PARTY.ATTRIBUTE3
, PARTY.ATTRIBUTE4
, PARTY.ATTRIBUTE5
, PARTY.ATTRIBUTE6
, PARTY.ATTRIBUTE7
, PARTY.ATTRIBUTE8
, PARTY.ATTRIBUTE9
, PARTY.ATTRIBUTE10
, PARTY.ATTRIBUTE11
, PARTY.ATTRIBUTE12
, PARTY.ATTRIBUTE13
, PARTY.ATTRIBUTE14
, PARTY.ATTRIBUTE15
, PARTY.GLOBAL_ATTRIBUTE_CATEGORY
, PARTY.GLOBAL_ATTRIBUTE1
, PARTY.GLOBAL_ATTRIBUTE2
, PARTY.GLOBAL_ATTRIBUTE3
, PARTY.GLOBAL_ATTRIBUTE4
, PARTY.GLOBAL_ATTRIBUTE5
, PARTY.GLOBAL_ATTRIBUTE6
, PARTY.GLOBAL_ATTRIBUTE7
, PARTY.GLOBAL_ATTRIBUTE8
, PARTY.GLOBAL_ATTRIBUTE9
, PARTY.GLOBAL_ATTRIBUTE10
, PARTY.GLOBAL_ATTRIBUTE11
, PARTY.GLOBAL_ATTRIBUTE12
, PARTY.GLOBAL_ATTRIBUTE13
, PARTY.GLOBAL_ATTRIBUTE14
, PARTY.GLOBAL_ATTRIBUTE15
, PARTY.GLOBAL_ATTRIBUTE16
, PARTY.GLOBAL_ATTRIBUTE17
, PARTY.GLOBAL_ATTRIBUTE18
, PARTY.GLOBAL_ATTRIBUTE19
, PARTY.GLOBAL_ATTRIBUTE20
FROM HZ_PARTIES PARTY
, HZ_PARTIES SUB_PARTY
, HZ_PARTIES OBJ_PARTY
, HZ_RELATIONSHIPS PARTY_REL
, HZ_ORGANIZATION_PROFILES ORG_PROFILE
, HZ_PERSON_PROFILES PERSON_PROFILE_SUB
, HZ_PERSON_PROFILES PERSON_PROFILE_OBJ
, HZ_ORG_CONTACTS ORG_CONT
WHERE PARTY.PARTY_ID = PARTY_REL.PARTY_ID
AND PARTY_REL.SUBJECT_ID = SUB_PARTY.PARTY_ID
AND PARTY_REL.OBJECT_ID = OBJ_PARTY.PARTY_ID
AND OBJ_PARTY.PARTY_ID = ORG_PROFILE.PARTY_ID(+)
AND OBJ_PARTY.PARTY_ID = PERSON_PROFILE_OBJ.PARTY_ID(+)
AND SUB_PARTY.PARTY_ID = PERSON_PROFILE_SUB.PARTY_ID(+)
AND ORG_CONT.PARTY_RELATIONSHIP_ID(+) = PARTY_REL.RELATIONSHIP_ID
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F' UNION SELECT PARTY.ROWID
, PARTY.PARTY_ID
, PARTY.PARTY_NUMBER
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, PARTY.PARTY_ID
, PARTY.PARTY_ID
, PARTY.LAST_UPDATE_DATE
, PARTY.LAST_UPDATED_BY
, PARTY.LAST_UPDATE_LOGIN
, PARTY.CREATION_DATE
, PARTY.CREATED_BY
, PARTY.PARTY_TYPE
, NULL PARTY_NAME
, NULL PARTY_NAME
, NULL GROUP_TYPE
, PARTY.PERSON_FIRST_NAME
, PARTY.PERSON_MIDDLE_NAME
, PARTY.PERSON_LAST_NAME
, PARTY.PERSON_NAME_SUFFIX
, PARTY.PERSON_TITLE
, NULL ALIAS
, NULL SIC_CODE
, NULL ANALYSIS_FY
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL TAX_REFERENCE
, TO_NUMBER(NULL)
, NULL INTERNAL_FLAG
, NULL
, TO_NUMBER(NULL) DUNS_NUMBER
, NULL JGZZ_FISCAL_CODE
, PARTY.PARTY_TYPE
, PARTY.PERSON_FIRST_NAME
, PARTY.PERSON_MIDDLE_NAME
, PARTY.PERSON_LAST_NAME
, PARTY.PERSON_NAME_SUFFIX
, PARTY.PERSON_TITLE
, NULL KNOWN_AS
, NULL PERSON_NAME_PHONETIC
, NULL JOB_TITLE
, NULL DECISION_MAKER_FLAG
, NULL JOB_TITLE_CODE
, TO_NUMBER(NULL) MANAGED_BY
, NULL NATIVE_LANGUAGE
, NULL REFERENCE_USE_FLAG
, NULL OTHER_LANGUAGE_1
, NULL OTHER_LANGUAGE_2
, NULL RANK
, NULL PERSON_PRE_NAME_ADJUNCT
, NULL DEPARTMENT_CODE
, NULL DEPARTMENT
, NULL PARTY_RELATIONSHIP_TYPE
, PARTY.ADDRESS1 || DECODE(PARTY.ADDRESS1
, '
, '
, DECODE(PARTY.ADDRESS2
, '
, '
, '
, ')) || PARTY.ADDRESS2 || PARTY.ADDRESS3 || DECODE(PARTY.ADDRESS3
, '
, '
, DECODE(PARTY.ADDRESS4
, '
, '
, '
, ')) || PARTY.ADDRESS4 ADDRESS
, PARTY.CITY
, PARTY.STATE
, PARTY.PROVINCE
, PARTY.POSTAL_CODE
, PARTY.COUNTRY
, PARTY.ATTRIBUTE_CATEGORY
, PARTY.ATTRIBUTE1
, PARTY.ATTRIBUTE2
, PARTY.ATTRIBUTE3
, PARTY.ATTRIBUTE4
, PARTY.ATTRIBUTE5
, PARTY.ATTRIBUTE6
, PARTY.ATTRIBUTE7
, PARTY.ATTRIBUTE8
, PARTY.ATTRIBUTE9
, PARTY.ATTRIBUTE10
, PARTY.ATTRIBUTE11
, PARTY.ATTRIBUTE12
, PARTY.ATTRIBUTE13
, PARTY.ATTRIBUTE14
, PARTY.ATTRIBUTE15
, PARTY.GLOBAL_ATTRIBUTE_CATEGORY
, PARTY.GLOBAL_ATTRIBUTE1
, PARTY.GLOBAL_ATTRIBUTE2
, PARTY.GLOBAL_ATTRIBUTE3
, PARTY.GLOBAL_ATTRIBUTE4
, PARTY.GLOBAL_ATTRIBUTE5
, PARTY.GLOBAL_ATTRIBUTE6
, PARTY.GLOBAL_ATTRIBUTE7
, PARTY.GLOBAL_ATTRIBUTE8
, PARTY.GLOBAL_ATTRIBUTE9
, PARTY.GLOBAL_ATTRIBUTE10
, PARTY.GLOBAL_ATTRIBUTE11
, PARTY.GLOBAL_ATTRIBUTE12
, PARTY.GLOBAL_ATTRIBUTE13
, PARTY.GLOBAL_ATTRIBUTE14
, PARTY.GLOBAL_ATTRIBUTE15
, PARTY.GLOBAL_ATTRIBUTE16
, PARTY.GLOBAL_ATTRIBUTE17
, PARTY.GLOBAL_ATTRIBUTE18
, PARTY.GLOBAL_ATTRIBUTE19
, PARTY.GLOBAL_ATTRIBUTE20
FROM HZ_PARTIES PARTY
WHERE NOT EXISTS (SELECT 'X'
FROM HZ_RELATIONSHIPS PARTY_REL
WHERE PARTY.PARTY_ID = PARTY_REL.OBJECT_ID
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F')
AND PARTY.PARTY_TYPE = 'PERSON'