VIEW: APPS.CSC_ALL_HZ_PARTIES_CONT_INFO_V

Source

View Text - Preformatted

SELECT SUB.ROWID , SUB.PARTY_ID , SUB.PARTY_NUMBER , SUB.PARTY_NAME , SUB.PARTY_TYPE , SUB.OBJECT_VERSION_NUMBER , SUB.ATTRIBUTE_CATEGORY , SUB.ATTRIBUTE1 , SUB.ATTRIBUTE2 , SUB.ATTRIBUTE3 , SUB.ATTRIBUTE4 , SUB.ATTRIBUTE5 , SUB.ATTRIBUTE6 , SUB.ATTRIBUTE7 , SUB.ATTRIBUTE8 , SUB.ATTRIBUTE9 , SUB.ATTRIBUTE10 , SUB.ATTRIBUTE11 , SUB.ATTRIBUTE12 , SUB.ATTRIBUTE13 , SUB.ATTRIBUTE14 , SUB.ATTRIBUTE15 , SUB.ATTRIBUTE16 , SUB.ATTRIBUTE17 , SUB.ATTRIBUTE18 , SUB.ATTRIBUTE19 , SUB.ATTRIBUTE20 , SUB.ATTRIBUTE21 , SUB.ATTRIBUTE22 , SUB.ATTRIBUTE23 , SUB.ATTRIBUTE24 , SUB.PERSON_PRE_NAME_ADJUNCT , SUB.PERSON_FIRST_NAME , SUB.PERSON_MIDDLE_NAME , SUB.PERSON_LAST_NAME , SUB.PERSON_NAME_SUFFIX , SUB.PERSON_TITLE , SUB.ADDRESS1||' '||SUB.ADDRESS2||' '||SUB.ADDRESS3||' '||SUB.ADDRESS4 SUB_ADDRESS , SUB.ADDRESS1 , SUB.ADDRESS2 , SUB.ADDRESS3 , SUB.ADDRESS4 , SUB.CITY , SUB.STATE , SUB.PROVINCE , SUB.POSTAL_CODE , SUB.COUNTRY , SUB.STATUS , SUB.COUNTY , SUB.URL , SUB.EMAIL_ADDRESS , SUB.ORGANIZATION_NAME_PHONETIC , SUB.PERSON_FIRST_NAME_PHONETIC , SUB.PERSON_LAST_NAME_PHONETIC , REL.PARTY_ID , REL.PARTY_NUMBER , REL.PARTY_NAME , REL.PARTY_TYPE , REL.OBJECT_VERSION_NUMBER , REL.ATTRIBUTE_CATEGORY , REL.ATTRIBUTE1 , REL.ATTRIBUTE2 , REL.ATTRIBUTE3 , REL.ATTRIBUTE4 , REL.ATTRIBUTE5 , REL.ATTRIBUTE6 , REL.ATTRIBUTE7 , REL.ATTRIBUTE8 , REL.ATTRIBUTE9 , REL.ATTRIBUTE10 , REL.ATTRIBUTE11 , REL.ATTRIBUTE12 , REL.ATTRIBUTE13 , REL.ATTRIBUTE14 , REL.ATTRIBUTE15 , REL.ATTRIBUTE16 , REL.ATTRIBUTE17 , REL.ATTRIBUTE18 , REL.ATTRIBUTE19 , REL.ATTRIBUTE20 , REL.ATTRIBUTE21 , REL.ATTRIBUTE22 , REL.ATTRIBUTE23 , REL.ATTRIBUTE24 , REL.PERSON_PRE_NAME_ADJUNCT , REL.PERSON_FIRST_NAME , REL.PERSON_MIDDLE_NAME , REL.PERSON_LAST_NAME , REL.PERSON_NAME_SUFFIX , REL.PERSON_TITLE , LOC.ADDRESS1||' '||LOC.ADDRESS2||' '||LOC.ADDRESS3||' '||LOC.ADDRESS4 REL_ADDRESS , LOC.ADDRESS1 , LOC.ADDRESS2 , LOC.ADDRESS3 , LOC.ADDRESS4 , LOC.CITY , LOC.STATE , LOC.PROVINCE , LOC.POSTAL_CODE , LOC.COUNTRY , REL.STATUS , LOC.COUNTY , REL.URL , REL.EMAIL_ADDRESS , REL.ORGANIZATION_NAME_PHONETIC , REL.PERSON_FIRST_NAME_PHONETIC , REL.PERSON_LAST_NAME_PHONETIC , PARTY_REL.RELATIONSHIP_ID , PARTY_REL.PARTY_ID , PARTY_REL.SUBJECT_ID , PARTY_REL.SUBJECT_TYPE , PARTY_REL.SUBJECT_TABLE_NAME , PARTY_REL.OBJECT_ID , PARTY_REL.OBJECT_TYPE , PARTY_REL.OBJECT_TABLE_NAME , PARTY_REL.RELATIONSHIP_CODE , PARTY_REL.DIRECTIONAL_FLAG , PARTY_REL.START_DATE , PARTY_REL.END_DATE , PARTY_REL.STATUS , PARTY_REL.RELATIONSHIP_TYPE , PARTY_REL.OBJECT_VERSION_NUMBER, LKUP.MEANING , TERI.TERRITORY_SHORT_NAME, loc.location_id, SITES.STATUS SITE_STATUS FROM HZ_PARTIES SUB, HZ_PARTIES REL, HZ_RELATIONSHIPS PARTY_REL, AR_LOOKUPS LKUP, FND_TERRITORIES_TL TERI,HZ_LOCATIONS LOC,HZ_PARTY_SITES SITES WHERE SUB.Party_Type = 'PERSON' AND SUB.party_id = PARTY_REL.subject_id AND REL.party_id = PARTY_REL.party_id AND TERI.LANGUAGE(+) = USERENV( 'LANG' ) AND LOC.COUNTRY = TERI.TERRITORY_CODE(+) AND LKUP.lookup_type = 'PARTY_RELATIONS_TYPE' AND PARTY_REL.relationship_code = LKUP.lookup_code AND LKUP.enabled_flag = 'Y' AND trunc(sysdate) between trunc(nvl(LKUP.start_date_active, sysdate)) AND trunc(nvl(LKUP.end_date_active, sysdate)) AND nvl(PARTY_REL.start_date, sysdate-1) < sysdate AND nvl(PARTY_REl.end_date, sysdate+1) > sysdate AND REL.STATUS NOT IN ('M', 'D') AND SUB.STATUS NOT IN ('M', 'D') AND LOC.LOCATION_ID=SITES.LOCATION_ID AND SITES.PARTY_ID=REL.PARTY_ID AND LOC.ADDRESS_EXPIRATION_DATE IS NULL AND NVL( SITES.CREATED_BY_MODULE,'XXX') <> 'SR_ONETIME' UNION ALL SELECT PRTY.ROWID , PRTY.PARTY_ID , Null , PRTY.PARTY_NAME , Null , PRTY.OBJECT_VERSION_NUMBER, Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , PRTY.PERSON_PRE_NAME_ADJUNCT , PRTY.PERSON_FIRST_NAME , PRTY.PERSON_MIDDLE_NAME , PRTY.PERSON_LAST_NAME , Null , PRTY.PERSON_TITLE , Null , Null , Null , Null , Null , Null , Null , Null , Null , Null , PRTY.STATUS , Null , Null , Null , Null , Null , Null , PRTY.PARTY_ID , PRTY.PARTY_NUMBER , PRTY.PARTY_NAME , PRTY.PARTY_TYPE , PRTY.OBJECT_VERSION_NUMBER , PRTY.ATTRIBUTE_CATEGORY , PRTY.ATTRIBUTE1 , PRTY.ATTRIBUTE2 , PRTY.ATTRIBUTE3 , PRTY.ATTRIBUTE4 , PRTY.ATTRIBUTE5 , PRTY.ATTRIBUTE6 , PRTY.ATTRIBUTE7 , PRTY.ATTRIBUTE8 , PRTY.ATTRIBUTE9 , PRTY.ATTRIBUTE10 , PRTY.ATTRIBUTE11 , PRTY.ATTRIBUTE12 , PRTY.ATTRIBUTE13 , PRTY.ATTRIBUTE14 , PRTY.ATTRIBUTE15 , PRTY.ATTRIBUTE16 , PRTY.ATTRIBUTE17 , PRTY.ATTRIBUTE18 , PRTY.ATTRIBUTE19 , PRTY.ATTRIBUTE20 , PRTY.ATTRIBUTE21 , PRTY.ATTRIBUTE22 , PRTY.ATTRIBUTE23 , PRTY.ATTRIBUTE24 , PRTY.PERSON_PRE_NAME_ADJUNCT , PRTY.PERSON_FIRST_NAME , PRTY.PERSON_MIDDLE_NAME , PRTY.PERSON_LAST_NAME , PRTY.PERSON_NAME_SUFFIX , PRTY.PERSON_TITLE , LOC.ADDRESS1||' '||LOC.ADDRESS2||' '||LOC.ADDRESS3||' '||LOC.ADDRESS4 ADDRESS , LOC.ADDRESS1 , LOC.ADDRESS2 , LOC.ADDRESS3 , LOC.ADDRESS4 , LOC.CITY , LOC.STATE , LOC.PROVINCE , LOC.POSTAL_CODE , LOC.COUNTRY , PRTY.STATUS , LOC.COUNTY , PRTY.URL , PRTY.EMAIL_ADDRESS , PRTY.ORGANIZATION_NAME_PHONETIC , PRTY.PERSON_FIRST_NAME_PHONETIC , PRTY.PERSON_LAST_NAME_PHONETIC , to_number(Null) , to_number(Null) , PRTY.PARTY_ID , Null , Null , PRTY.PARTY_ID , Null , Null , 'SELF' , Null , to_date(Null) , to_date(Null) , Null , Null , Null , Null , TERI.TERRITORY_SHORT_NAME , loc.location_id, SITES.STATUS SITE_STATUS FROM HZ_PARTIES PRTY, FND_TERRITORIES_TL TERI,HZ_LOCATIONS LOC,HZ_PARTY_SITES SITES WHERE party_type ='PERSON' AND LOC.LOCATION_ID=SITES.LOCATION_ID AND SITES.PARTY_ID=PRTY.PARTY_ID AND LOC.ADDRESS_EXPIRATION_DATE IS NULL AND TERI.LANGUAGE(+) = USERENV( 'LANG' ) AND LOC.COUNTRY = TERI.TERRITORY_CODE(+) AND PRTY.STATUS NOT IN ('M', 'D') AND NVL(SITES.CREATED_BY_MODULE,'XXX') <> 'SR_ONETIME'

View Text - HTML Formatted

SELECT SUB.ROWID
, SUB.PARTY_ID
, SUB.PARTY_NUMBER
, SUB.PARTY_NAME
, SUB.PARTY_TYPE
, SUB.OBJECT_VERSION_NUMBER
, SUB.ATTRIBUTE_CATEGORY
, SUB.ATTRIBUTE1
, SUB.ATTRIBUTE2
, SUB.ATTRIBUTE3
, SUB.ATTRIBUTE4
, SUB.ATTRIBUTE5
, SUB.ATTRIBUTE6
, SUB.ATTRIBUTE7
, SUB.ATTRIBUTE8
, SUB.ATTRIBUTE9
, SUB.ATTRIBUTE10
, SUB.ATTRIBUTE11
, SUB.ATTRIBUTE12
, SUB.ATTRIBUTE13
, SUB.ATTRIBUTE14
, SUB.ATTRIBUTE15
, SUB.ATTRIBUTE16
, SUB.ATTRIBUTE17
, SUB.ATTRIBUTE18
, SUB.ATTRIBUTE19
, SUB.ATTRIBUTE20
, SUB.ATTRIBUTE21
, SUB.ATTRIBUTE22
, SUB.ATTRIBUTE23
, SUB.ATTRIBUTE24
, SUB.PERSON_PRE_NAME_ADJUNCT
, SUB.PERSON_FIRST_NAME
, SUB.PERSON_MIDDLE_NAME
, SUB.PERSON_LAST_NAME
, SUB.PERSON_NAME_SUFFIX
, SUB.PERSON_TITLE
, SUB.ADDRESS1||' '||SUB.ADDRESS2||' '||SUB.ADDRESS3||' '||SUB.ADDRESS4 SUB_ADDRESS
, SUB.ADDRESS1
, SUB.ADDRESS2
, SUB.ADDRESS3
, SUB.ADDRESS4
, SUB.CITY
, SUB.STATE
, SUB.PROVINCE
, SUB.POSTAL_CODE
, SUB.COUNTRY
, SUB.STATUS
, SUB.COUNTY
, SUB.URL
, SUB.EMAIL_ADDRESS
, SUB.ORGANIZATION_NAME_PHONETIC
, SUB.PERSON_FIRST_NAME_PHONETIC
, SUB.PERSON_LAST_NAME_PHONETIC
, REL.PARTY_ID
, REL.PARTY_NUMBER
, REL.PARTY_NAME
, REL.PARTY_TYPE
, REL.OBJECT_VERSION_NUMBER
, REL.ATTRIBUTE_CATEGORY
, REL.ATTRIBUTE1
, REL.ATTRIBUTE2
, REL.ATTRIBUTE3
, REL.ATTRIBUTE4
, REL.ATTRIBUTE5
, REL.ATTRIBUTE6
, REL.ATTRIBUTE7
, REL.ATTRIBUTE8
, REL.ATTRIBUTE9
, REL.ATTRIBUTE10
, REL.ATTRIBUTE11
, REL.ATTRIBUTE12
, REL.ATTRIBUTE13
, REL.ATTRIBUTE14
, REL.ATTRIBUTE15
, REL.ATTRIBUTE16
, REL.ATTRIBUTE17
, REL.ATTRIBUTE18
, REL.ATTRIBUTE19
, REL.ATTRIBUTE20
, REL.ATTRIBUTE21
, REL.ATTRIBUTE22
, REL.ATTRIBUTE23
, REL.ATTRIBUTE24
, REL.PERSON_PRE_NAME_ADJUNCT
, REL.PERSON_FIRST_NAME
, REL.PERSON_MIDDLE_NAME
, REL.PERSON_LAST_NAME
, REL.PERSON_NAME_SUFFIX
, REL.PERSON_TITLE
, LOC.ADDRESS1||' '||LOC.ADDRESS2||' '||LOC.ADDRESS3||' '||LOC.ADDRESS4 REL_ADDRESS
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.STATE
, LOC.PROVINCE
, LOC.POSTAL_CODE
, LOC.COUNTRY
, REL.STATUS
, LOC.COUNTY
, REL.URL
, REL.EMAIL_ADDRESS
, REL.ORGANIZATION_NAME_PHONETIC
, REL.PERSON_FIRST_NAME_PHONETIC
, REL.PERSON_LAST_NAME_PHONETIC
, PARTY_REL.RELATIONSHIP_ID
, PARTY_REL.PARTY_ID
, PARTY_REL.SUBJECT_ID
, PARTY_REL.SUBJECT_TYPE
, PARTY_REL.SUBJECT_TABLE_NAME
, PARTY_REL.OBJECT_ID
, PARTY_REL.OBJECT_TYPE
, PARTY_REL.OBJECT_TABLE_NAME
, PARTY_REL.RELATIONSHIP_CODE
, PARTY_REL.DIRECTIONAL_FLAG
, PARTY_REL.START_DATE
, PARTY_REL.END_DATE
, PARTY_REL.STATUS
, PARTY_REL.RELATIONSHIP_TYPE
, PARTY_REL.OBJECT_VERSION_NUMBER
, LKUP.MEANING
, TERI.TERRITORY_SHORT_NAME
, LOC.LOCATION_ID
, SITES.STATUS SITE_STATUS
FROM HZ_PARTIES SUB
, HZ_PARTIES REL
, HZ_RELATIONSHIPS PARTY_REL
, AR_LOOKUPS LKUP
, FND_TERRITORIES_TL TERI
, HZ_LOCATIONS LOC
, HZ_PARTY_SITES SITES
WHERE SUB.PARTY_TYPE = 'PERSON'
AND SUB.PARTY_ID = PARTY_REL.SUBJECT_ID
AND REL.PARTY_ID = PARTY_REL.PARTY_ID
AND TERI.LANGUAGE(+) = USERENV( 'LANG' )
AND LOC.COUNTRY = TERI.TERRITORY_CODE(+)
AND LKUP.LOOKUP_TYPE = 'PARTY_RELATIONS_TYPE'
AND PARTY_REL.RELATIONSHIP_CODE = LKUP.LOOKUP_CODE
AND LKUP.ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(LKUP.START_DATE_ACTIVE
, SYSDATE))
AND TRUNC(NVL(LKUP.END_DATE_ACTIVE
, SYSDATE))
AND NVL(PARTY_REL.START_DATE
, SYSDATE-1) < SYSDATE
AND NVL(PARTY_REL.END_DATE
, SYSDATE+1) > SYSDATE
AND REL.STATUS NOT IN ('M'
, 'D')
AND SUB.STATUS NOT IN ('M'
, 'D')
AND LOC.LOCATION_ID=SITES.LOCATION_ID
AND SITES.PARTY_ID=REL.PARTY_ID
AND LOC.ADDRESS_EXPIRATION_DATE IS NULL
AND NVL( SITES.CREATED_BY_MODULE
, 'XXX') <> 'SR_ONETIME' UNION ALL SELECT PRTY.ROWID
, PRTY.PARTY_ID
, NULL
, PRTY.PARTY_NAME
, NULL
, PRTY.OBJECT_VERSION_NUMBER
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PRTY.PERSON_PRE_NAME_ADJUNCT
, PRTY.PERSON_FIRST_NAME
, PRTY.PERSON_MIDDLE_NAME
, PRTY.PERSON_LAST_NAME
, NULL
, PRTY.PERSON_TITLE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PRTY.STATUS
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PRTY.PARTY_ID
, PRTY.PARTY_NUMBER
, PRTY.PARTY_NAME
, PRTY.PARTY_TYPE
, PRTY.OBJECT_VERSION_NUMBER
, PRTY.ATTRIBUTE_CATEGORY
, PRTY.ATTRIBUTE1
, PRTY.ATTRIBUTE2
, PRTY.ATTRIBUTE3
, PRTY.ATTRIBUTE4
, PRTY.ATTRIBUTE5
, PRTY.ATTRIBUTE6
, PRTY.ATTRIBUTE7
, PRTY.ATTRIBUTE8
, PRTY.ATTRIBUTE9
, PRTY.ATTRIBUTE10
, PRTY.ATTRIBUTE11
, PRTY.ATTRIBUTE12
, PRTY.ATTRIBUTE13
, PRTY.ATTRIBUTE14
, PRTY.ATTRIBUTE15
, PRTY.ATTRIBUTE16
, PRTY.ATTRIBUTE17
, PRTY.ATTRIBUTE18
, PRTY.ATTRIBUTE19
, PRTY.ATTRIBUTE20
, PRTY.ATTRIBUTE21
, PRTY.ATTRIBUTE22
, PRTY.ATTRIBUTE23
, PRTY.ATTRIBUTE24
, PRTY.PERSON_PRE_NAME_ADJUNCT
, PRTY.PERSON_FIRST_NAME
, PRTY.PERSON_MIDDLE_NAME
, PRTY.PERSON_LAST_NAME
, PRTY.PERSON_NAME_SUFFIX
, PRTY.PERSON_TITLE
, LOC.ADDRESS1||' '||LOC.ADDRESS2||' '||LOC.ADDRESS3||' '||LOC.ADDRESS4 ADDRESS
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.STATE
, LOC.PROVINCE
, LOC.POSTAL_CODE
, LOC.COUNTRY
, PRTY.STATUS
, LOC.COUNTY
, PRTY.URL
, PRTY.EMAIL_ADDRESS
, PRTY.ORGANIZATION_NAME_PHONETIC
, PRTY.PERSON_FIRST_NAME_PHONETIC
, PRTY.PERSON_LAST_NAME_PHONETIC
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PRTY.PARTY_ID
, NULL
, NULL
, PRTY.PARTY_ID
, NULL
, NULL
, 'SELF'
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, NULL
, NULL
, NULL
, NULL
, TERI.TERRITORY_SHORT_NAME
, LOC.LOCATION_ID
, SITES.STATUS SITE_STATUS
FROM HZ_PARTIES PRTY
, FND_TERRITORIES_TL TERI
, HZ_LOCATIONS LOC
, HZ_PARTY_SITES SITES
WHERE PARTY_TYPE ='PERSON'
AND LOC.LOCATION_ID=SITES.LOCATION_ID
AND SITES.PARTY_ID=PRTY.PARTY_ID
AND LOC.ADDRESS_EXPIRATION_DATE IS NULL
AND TERI.LANGUAGE(+) = USERENV( 'LANG' )
AND LOC.COUNTRY = TERI.TERRITORY_CODE(+)
AND PRTY.STATUS NOT IN ('M'
, 'D')
AND NVL(SITES.CREATED_BY_MODULE
, 'XXX') <> 'SR_ONETIME'