VIEW: APPS.CSC_HZ_PARTIES_SELF_V

Source

View Text - Preformatted

SELECT PARTY.ROWID , PARTY.PARTY_ID , PARTY_REL.RELATIONSHIP_ID PARTY_RELATIONSHIP_ID , PARTY.PARTY_NUMBER , PARTY.PARTY_TYPE , PARTY.PARTY_NAME , SUB_PARTY.PARTY_ID , SUB_PARTY.PARTY_NUMBER , OBJ_PARTY.PARTY_ID , OBJ_PARTY.PARTY_NUMBER , OBJ_PARTY.PARTY_NAME , 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_PRE_NAME_ADJUNCT , OBJ_PARTY.KNOWN_AS , OBJ_PARTY.SIC_CODE , SUB_PARTY.PARTY_TYPE , SUB_PARTY.PARTY_NAME , SUB_PARTY.PERSON_FIRST_NAME , SUB_PARTY.PERSON_MIDDLE_NAME , SUB_PARTY.PERSON_LAST_NAME , SUB_PARTY.PERSON_NAME_SUFFIX , SUB_PARTY.PERSON_PRE_NAME_ADJUNCT , SUB_PARTY.PERSON_PRE_NAME_ADJUNCT , PARTY_REL.RELATIONSHIP_TYPE PARTY_RELATIONSHIP_TYPE , PARTY.ADDRESS1||DECODE(PARTY.ADDRESS2,NULL,NULL,';'||PARTY.ADDRESS2|| DECODE(PARTY.ADDRESS3,NULL,NULL,';'||PARTY.ADDRESS3|| DECODE(PARTY.ADDRESS4,NULL,NULL,';'||PARTY.ADDRESS4))) ADDRESS , PARTY.ADDRESS1 , PARTY.CITY , PARTY.STATE , PARTY.PROVINCE , PARTY.POSTAL_CODE , PARTY.COUNTRY , OBJ_PARTY.ATTRIBUTE_CATEGORY , OBJ_PARTY.ATTRIBUTE1 , OBJ_PARTY.ATTRIBUTE2 , OBJ_PARTY.ATTRIBUTE3 , OBJ_PARTY.ATTRIBUTE4 , OBJ_PARTY.ATTRIBUTE5 , OBJ_PARTY.ATTRIBUTE6 , OBJ_PARTY.ATTRIBUTE7 , OBJ_PARTY.ATTRIBUTE8 , OBJ_PARTY.ATTRIBUTE9 , OBJ_PARTY.ATTRIBUTE10 , OBJ_PARTY.ATTRIBUTE11 , OBJ_PARTY.ATTRIBUTE12 , OBJ_PARTY.ATTRIBUTE13 , OBJ_PARTY.ATTRIBUTE14 , OBJ_PARTY.ATTRIBUTE15 , OBJ_PARTY.ATTRIBUTE16 , OBJ_PARTY.ATTRIBUTE17 , OBJ_PARTY.ATTRIBUTE18 , OBJ_PARTY.ATTRIBUTE19 , OBJ_PARTY.ATTRIBUTE20 , OBJ_PARTY.ATTRIBUTE21 , OBJ_PARTY.ATTRIBUTE22 , OBJ_PARTY.ATTRIBUTE23 , OBJ_PARTY.ATTRIBUTE24 , OBJ_PARTY.GLOBAL_ATTRIBUTE_CATEGORY , OBJ_PARTY.GLOBAL_ATTRIBUTE1 , OBJ_PARTY.GLOBAL_ATTRIBUTE2 , OBJ_PARTY.GLOBAL_ATTRIBUTE3 , OBJ_PARTY.GLOBAL_ATTRIBUTE4 , OBJ_PARTY.GLOBAL_ATTRIBUTE5 , OBJ_PARTY.GLOBAL_ATTRIBUTE6 , OBJ_PARTY.GLOBAL_ATTRIBUTE7 , OBJ_PARTY.GLOBAL_ATTRIBUTE8 , OBJ_PARTY.GLOBAL_ATTRIBUTE9 , OBJ_PARTY.GLOBAL_ATTRIBUTE10 , OBJ_PARTY.GLOBAL_ATTRIBUTE11 , OBJ_PARTY.GLOBAL_ATTRIBUTE12 , OBJ_PARTY.GLOBAL_ATTRIBUTE13 , OBJ_PARTY.GLOBAL_ATTRIBUTE14 , OBJ_PARTY.GLOBAL_ATTRIBUTE15 , OBJ_PARTY.GLOBAL_ATTRIBUTE16 , OBJ_PARTY.GLOBAL_ATTRIBUTE17 , OBJ_PARTY.GLOBAL_ATTRIBUTE18 , OBJ_PARTY.GLOBAL_ATTRIBUTE19 , OBJ_PARTY.GLOBAL_ATTRIBUTE20 , SUB_PARTY.LAST_UPDATE_DATE , OBJ_PARTY.LAST_UPDATE_DATE , PARTY_REL.LAST_UPDATE_DATE , PARTY.LAST_UPDATE_DATE ,OBJ_PARTY.STATUS ,SUB_PARTY.STATUS FROM HZ_PARTIES PARTY, HZ_PARTIES SUB_PARTY, HZ_PARTIES OBJ_PARTY, HZ_RELATIONSHIPS PARTY_REL 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 nvl(party_rel.start_date, sysdate-1) < sysdate AND nvl(party_rel.end_date, sysdate+1) > sysdate AND party_rel.subject_table_name = 'HZ_PARTIES' AND party_rel.object_table_name = 'HZ_PARTIES' AND party_rel.directional_flag = 'F' UNION ALL SELECT PARTY.ROWID , PARTY.PARTY_ID , TO_NUMBER(NULL) , PARTY.PARTY_NUMBER , PARTY.PARTY_TYPE , PARTY.PARTY_NAME , DECODE(PARTY.PARTY_TYPE,'PERSON',PARTY.PARTY_ID, TO_NUMBER(NULL)) , DECODE(PARTY.PARTY_TYPE,'PERSON',PARTY.PARTY_NUMBER, NULL) , PARTY.PARTY_ID , PARTY.PARTY_NUMBER , PARTY.PARTY_NAME , PARTY.LAST_UPDATE_DATE , PARTY.LAST_UPDATED_BY , PARTY.LAST_UPDATE_LOGIN , PARTY.CREATION_DATE , PARTY.CREATED_BY , PARTY.PARTY_TYPE , PARTY.PARTY_NAME , PARTY.PARTY_NAME , PARTY.GROUP_TYPE , PARTY.PERSON_FIRST_NAME , PARTY.PERSON_MIDDLE_NAME , PARTY.PERSON_LAST_NAME , PARTY.PERSON_NAME_SUFFIX , PARTY.PERSON_PRE_NAME_ADJUNCT , PARTY.KNOWN_AS , PARTY.SIC_CODE , PARTY.PARTY_TYPE , PARTY.PARTY_NAME , PARTY.PERSON_FIRST_NAME , PARTY.PERSON_MIDDLE_NAME , PARTY.PERSON_LAST_NAME , PARTY.PERSON_NAME_SUFFIX , PARTY.PERSON_PRE_NAME_ADJUNCT , PARTY.PERSON_PRE_NAME_ADJUNCT , 'SELF' , PARTY.ADDRESS1||DECODE(PARTY.ADDRESS2,NULL,NULL,';'||PARTY.ADDRESS2|| DECODE(PARTY.ADDRESS3,NULL,NULL,';'||PARTY.ADDRESS3|| DECODE(PARTY.ADDRESS4,NULL,NULL,';'||PARTY.ADDRESS4))) ADDRESS , PARTY.ADDRESS1 , 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.ATTRIBUTE16 , PARTY.ATTRIBUTE17 , PARTY.ATTRIBUTE18 , PARTY.ATTRIBUTE19 , PARTY.ATTRIBUTE20 , PARTY.ATTRIBUTE21 , PARTY.ATTRIBUTE22 , PARTY.ATTRIBUTE23 , PARTY.ATTRIBUTE24 , 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 , PARTY.LAST_UPDATE_DATE , PARTY.LAST_UPDATE_DATE , TO_DATE(NULL) , PARTY.LAST_UPDATE_DATE ,PARTY.STATUS OBJ_STATUS ,PARTY.STATUS SUB_STATUS FROM HZ_PARTIES PARTY WHERE NOT EXISTS (SELECT 'x' FROM HZ_RELATIONSHIPS PARTY_REL WHERE PARTY.PARTY_ID = PARTY_REL.PARTY_ID AND party_rel.subject_table_name = 'HZ_PARTIES' AND party_rel.object_table_name = 'HZ_PARTIES' AND party_rel.directional_flag = 'F')

View Text - HTML Formatted

SELECT PARTY.ROWID
, PARTY.PARTY_ID
, PARTY_REL.RELATIONSHIP_ID PARTY_RELATIONSHIP_ID
, PARTY.PARTY_NUMBER
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, SUB_PARTY.PARTY_ID
, SUB_PARTY.PARTY_NUMBER
, OBJ_PARTY.PARTY_ID
, OBJ_PARTY.PARTY_NUMBER
, OBJ_PARTY.PARTY_NAME
, 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_PRE_NAME_ADJUNCT
, OBJ_PARTY.KNOWN_AS
, OBJ_PARTY.SIC_CODE
, SUB_PARTY.PARTY_TYPE
, SUB_PARTY.PARTY_NAME
, SUB_PARTY.PERSON_FIRST_NAME
, SUB_PARTY.PERSON_MIDDLE_NAME
, SUB_PARTY.PERSON_LAST_NAME
, SUB_PARTY.PERSON_NAME_SUFFIX
, SUB_PARTY.PERSON_PRE_NAME_ADJUNCT
, SUB_PARTY.PERSON_PRE_NAME_ADJUNCT
, PARTY_REL.RELATIONSHIP_TYPE PARTY_RELATIONSHIP_TYPE
, PARTY.ADDRESS1||DECODE(PARTY.ADDRESS2
, NULL
, NULL
, ';'||PARTY.ADDRESS2|| DECODE(PARTY.ADDRESS3
, NULL
, NULL
, ';'||PARTY.ADDRESS3|| DECODE(PARTY.ADDRESS4
, NULL
, NULL
, ';'||PARTY.ADDRESS4))) ADDRESS
, PARTY.ADDRESS1
, PARTY.CITY
, PARTY.STATE
, PARTY.PROVINCE
, PARTY.POSTAL_CODE
, PARTY.COUNTRY
, OBJ_PARTY.ATTRIBUTE_CATEGORY
, OBJ_PARTY.ATTRIBUTE1
, OBJ_PARTY.ATTRIBUTE2
, OBJ_PARTY.ATTRIBUTE3
, OBJ_PARTY.ATTRIBUTE4
, OBJ_PARTY.ATTRIBUTE5
, OBJ_PARTY.ATTRIBUTE6
, OBJ_PARTY.ATTRIBUTE7
, OBJ_PARTY.ATTRIBUTE8
, OBJ_PARTY.ATTRIBUTE9
, OBJ_PARTY.ATTRIBUTE10
, OBJ_PARTY.ATTRIBUTE11
, OBJ_PARTY.ATTRIBUTE12
, OBJ_PARTY.ATTRIBUTE13
, OBJ_PARTY.ATTRIBUTE14
, OBJ_PARTY.ATTRIBUTE15
, OBJ_PARTY.ATTRIBUTE16
, OBJ_PARTY.ATTRIBUTE17
, OBJ_PARTY.ATTRIBUTE18
, OBJ_PARTY.ATTRIBUTE19
, OBJ_PARTY.ATTRIBUTE20
, OBJ_PARTY.ATTRIBUTE21
, OBJ_PARTY.ATTRIBUTE22
, OBJ_PARTY.ATTRIBUTE23
, OBJ_PARTY.ATTRIBUTE24
, OBJ_PARTY.GLOBAL_ATTRIBUTE_CATEGORY
, OBJ_PARTY.GLOBAL_ATTRIBUTE1
, OBJ_PARTY.GLOBAL_ATTRIBUTE2
, OBJ_PARTY.GLOBAL_ATTRIBUTE3
, OBJ_PARTY.GLOBAL_ATTRIBUTE4
, OBJ_PARTY.GLOBAL_ATTRIBUTE5
, OBJ_PARTY.GLOBAL_ATTRIBUTE6
, OBJ_PARTY.GLOBAL_ATTRIBUTE7
, OBJ_PARTY.GLOBAL_ATTRIBUTE8
, OBJ_PARTY.GLOBAL_ATTRIBUTE9
, OBJ_PARTY.GLOBAL_ATTRIBUTE10
, OBJ_PARTY.GLOBAL_ATTRIBUTE11
, OBJ_PARTY.GLOBAL_ATTRIBUTE12
, OBJ_PARTY.GLOBAL_ATTRIBUTE13
, OBJ_PARTY.GLOBAL_ATTRIBUTE14
, OBJ_PARTY.GLOBAL_ATTRIBUTE15
, OBJ_PARTY.GLOBAL_ATTRIBUTE16
, OBJ_PARTY.GLOBAL_ATTRIBUTE17
, OBJ_PARTY.GLOBAL_ATTRIBUTE18
, OBJ_PARTY.GLOBAL_ATTRIBUTE19
, OBJ_PARTY.GLOBAL_ATTRIBUTE20
, SUB_PARTY.LAST_UPDATE_DATE
, OBJ_PARTY.LAST_UPDATE_DATE
, PARTY_REL.LAST_UPDATE_DATE
, PARTY.LAST_UPDATE_DATE
, OBJ_PARTY.STATUS
, SUB_PARTY.STATUS
FROM HZ_PARTIES PARTY
, HZ_PARTIES SUB_PARTY
, HZ_PARTIES OBJ_PARTY
, HZ_RELATIONSHIPS PARTY_REL
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 NVL(PARTY_REL.START_DATE
, SYSDATE-1) < SYSDATE
AND NVL(PARTY_REL.END_DATE
, SYSDATE+1) > SYSDATE
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F' UNION ALL SELECT PARTY.ROWID
, PARTY.PARTY_ID
, TO_NUMBER(NULL)
, PARTY.PARTY_NUMBER
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, DECODE(PARTY.PARTY_TYPE
, 'PERSON'
, PARTY.PARTY_ID
, TO_NUMBER(NULL))
, DECODE(PARTY.PARTY_TYPE
, 'PERSON'
, PARTY.PARTY_NUMBER
, NULL)
, PARTY.PARTY_ID
, PARTY.PARTY_NUMBER
, PARTY.PARTY_NAME
, PARTY.LAST_UPDATE_DATE
, PARTY.LAST_UPDATED_BY
, PARTY.LAST_UPDATE_LOGIN
, PARTY.CREATION_DATE
, PARTY.CREATED_BY
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, PARTY.GROUP_TYPE
, PARTY.PERSON_FIRST_NAME
, PARTY.PERSON_MIDDLE_NAME
, PARTY.PERSON_LAST_NAME
, PARTY.PERSON_NAME_SUFFIX
, PARTY.PERSON_PRE_NAME_ADJUNCT
, PARTY.KNOWN_AS
, PARTY.SIC_CODE
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, PARTY.PERSON_FIRST_NAME
, PARTY.PERSON_MIDDLE_NAME
, PARTY.PERSON_LAST_NAME
, PARTY.PERSON_NAME_SUFFIX
, PARTY.PERSON_PRE_NAME_ADJUNCT
, PARTY.PERSON_PRE_NAME_ADJUNCT
, 'SELF'
, PARTY.ADDRESS1||DECODE(PARTY.ADDRESS2
, NULL
, NULL
, ';'||PARTY.ADDRESS2|| DECODE(PARTY.ADDRESS3
, NULL
, NULL
, ';'||PARTY.ADDRESS3|| DECODE(PARTY.ADDRESS4
, NULL
, NULL
, ';'||PARTY.ADDRESS4))) ADDRESS
, PARTY.ADDRESS1
, 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.ATTRIBUTE16
, PARTY.ATTRIBUTE17
, PARTY.ATTRIBUTE18
, PARTY.ATTRIBUTE19
, PARTY.ATTRIBUTE20
, PARTY.ATTRIBUTE21
, PARTY.ATTRIBUTE22
, PARTY.ATTRIBUTE23
, PARTY.ATTRIBUTE24
, 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
, PARTY.LAST_UPDATE_DATE
, PARTY.LAST_UPDATE_DATE
, TO_DATE(NULL)
, PARTY.LAST_UPDATE_DATE
, PARTY.STATUS OBJ_STATUS
, PARTY.STATUS SUB_STATUS
FROM HZ_PARTIES PARTY
WHERE NOT EXISTS (SELECT 'X'
FROM HZ_RELATIONSHIPS PARTY_REL
WHERE PARTY.PARTY_ID = PARTY_REL.PARTY_ID
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F')