VIEW: APPS.CSC_ALL_HZ_PARTIES_REL_V

Source

View Text - Preformatted

SELECT OBJ.ROWID , OBJ.JGZZ_FISCAL_CODE OBJ_JGZZ_FISCAL_CODE , REL.PARTY_ID REL_PARTY_ID , OBJ.PARTY_ID , OBJ.PARTY_NUMBER , OBJ.PARTY_NAME , OBJ.PARTY_TYPE , OBJ.OBJECT_VERSION_NUMBER, OBJ.ATTRIBUTE_CATEGORY , OBJ.ATTRIBUTE1 , OBJ.ATTRIBUTE2 OBJ_ATTRIBUTE2, OBJ.ATTRIBUTE3 OBJ_ATTRIBUTE3, OBJ.ATTRIBUTE4 OBJ_ATTRIBUTE4, OBJ.ATTRIBUTE5 OBJ_ATTRIBUTE5, OBJ.ATTRIBUTE6 OBJ_ATTRIBUTE6, OBJ.ATTRIBUTE7 OBJ_ATTRIBUTE7, OBJ.ATTRIBUTE8 OBJ_ATTRIBUTE8, OBJ.ATTRIBUTE9 OBJ_ATTRIBUTE9, OBJ.ATTRIBUTE10 OBJ_ATTRIBUTE10, OBJ.ATTRIBUTE11 OBJ_ATTRIBUTE11, OBJ.ATTRIBUTE12 OBJ_ATTRIBUTE12, OBJ.ATTRIBUTE13 OBJ_ATTRIBUTE13, OBJ.ATTRIBUTE14 OBJ_ATTRIBUTE14, OBJ.ATTRIBUTE15 OBJ_ATTRIBUTE15, OBJ.ATTRIBUTE16 OBJ_ATTRIBUTE16, OBJ.ATTRIBUTE17 OBJ_ATTRIBUTE17, OBJ.ATTRIBUTE18 OBJ_ATTRIBUTE18, OBJ.ATTRIBUTE19 OBJ_ATTRIBUTE19, OBJ.ATTRIBUTE20 OBJ_ATTRIBUTE20, OBJ.ATTRIBUTE21 OBJ_ATTRIBUTE21, OBJ.ATTRIBUTE22 OBJ_ATTRIBUTE22, OBJ.ATTRIBUTE23 OBJ_ATTRIBUTE23, OBJ.ATTRIBUTE24 OBJ_ATTRIBUTE24, OBJ.PERSON_PRE_NAME_ADJUNCT OBJ_PERSON_PRE_NAME_ADJUNCT , OBJ.PERSON_FIRST_NAME OBJ_PERSON_FIRST_NAME , OBJ.PERSON_MIDDLE_NAME OBJ_PERSON_MIDDLE_NAME, OBJ.PERSON_LAST_NAME OBJ_PERSON_LAST_NAME , OBJ.PERSON_NAME_SUFFIX OBJ_PERSON_NAME_SUFFIX , OBJ.PERSON_TITLE OBJ_PERSON_TITLE , OBJ.ADDRESS1||' '||OBJ.ADDRESS2||' '||OBJ.ADDRESS3||' '||OBJ.ADDRESS4 OBJ_ADDRESS , OBJ.ADDRESS1 OBJ_ADDRESS1 , OBJ.ADDRESS2 OBJ_ADDRESS2, OBJ.ADDRESS3 OBJ_ADDRESS3, OBJ.ADDRESS4 OBJ_ADDRESS4, OBJ.CITY OBJ_CITY, OBJ.STATE OBJ_STATE , OBJ.PROVINCE OBJ_PROVINCE, OBJ.POSTAL_CODE OBJ_POSTAL_CODE , OBJ.COUNTRY OBJ_COUNTRY , OBJ.STATUS OBJ_STATUS, OBJ.COUNTY OBJ_COUNTY, OBJ.URL OBJ_URL , OBJ.EMAIL_ADDRESS OBJ_EMAIL_ADDRESS, OBJ.ORGANIZATION_NAME_PHONETIC OBJ_ORGANIZATION_NAME_PHONETIC, OBJ.PERSON_FIRST_NAME_PHONETIC OBJ_PERSON_FIRST_NAME_PHONETIC , OBJ.PERSON_LAST_NAME_PHONETIC OBJ_PERSON_LAST_NAME_PHONETIC , SUB.PARTY_ID SUB_PARTY_ID , SUB.PARTY_NUMBER SUB_PARTY_NUMBER, SUB.PARTY_NAME SUB_PARTY_NAME , SUB.PARTY_TYPE SUB_PARTY_TYPE , SUB.OBJECT_VERSION_NUMBER SUB_OBJ_VER_NUMBER, SUB.ATTRIBUTE_CATEGORY SUB_ATTRIBUTE_CATEGORY , SUB.ATTRIBUTE1 SUB_ATTRIBUTE1, SUB.ATTRIBUTE2 SUB_ATTRIBUTE2 , SUB.ATTRIBUTE3 SUB_ATTRIBUTE3, SUB.ATTRIBUTE4 SUB_ATTRIBUTE4, SUB.ATTRIBUTE5 SUB_ATTRIBUTE5, SUB.ATTRIBUTE6 SUB_ATTRIBUTE6, SUB.ATTRIBUTE7 SUB_ATTRIBUTE7, SUB.ATTRIBUTE8 SUB_ATTRIBUTE8, SUB.ATTRIBUTE9 SUB_ATTRIBUTE9, SUB.ATTRIBUTE10 SUB_ATTRIBUTE10, SUB.ATTRIBUTE11 SUB_ATTRIBUTE11, SUB.ATTRIBUTE12 SUB_ATTRIBUTE12, SUB.ATTRIBUTE13 SUB_ATTRIBUTE13, SUB.ATTRIBUTE14 SUB_ATTRIBUTE14, SUB.ATTRIBUTE15 SUB_ATTRIBUTE15, SUB.ATTRIBUTE16 SUB_ATTRIBUTE16, SUB.ATTRIBUTE17 SUB_ATTRIBUTE17, SUB.ATTRIBUTE18 SUB_ATTRIBUTE18, SUB.ATTRIBUTE19 SUB_ATTRIBUTE19, SUB.ATTRIBUTE20 SUB_ATTRIBUTE20, SUB.ATTRIBUTE21 SUB_ATTRIBUTE21, SUB.ATTRIBUTE22 SUB_ATTRIBUTE22, SUB.ATTRIBUTE23 SUB_ATTRIBUTE23, SUB.ATTRIBUTE24 SUB_ATTRIBUTE24, SUB.PERSON_PRE_NAME_ADJUNCT SUB_PERSON_PRE_NAME_ADJUNCT, SUB.PERSON_FIRST_NAME SUB_PERSON_FIRST_NAME, SUB.PERSON_MIDDLE_NAME SUB_PERSON_MIDDLE_NAME , SUB.PERSON_LAST_NAME SUB_PERSON_LAST_NAME , SUB.PERSON_NAME_SUFFIX SUB_PERSON_NAME_SUFFIX, SUB.PERSON_TITLE SUB_PERSON_TITLE , SUB.ADDRESS1||' '||SUB.ADDRESS2||' '||SUB.ADDRESS3||' '||SUB.ADDRESS4 SUB_ADDRESS , SUB.ADDRESS1 SUB_ADDRESS1 , SUB.ADDRESS2 SUB_ADDRESS2, SUB.ADDRESS3 SUB_ADDRESS3, SUB.ADDRESS4 SUB_ADDRESS4, SUB.CITY SUB_CITY , SUB.STATE SUB_STATE , SUB.PROVINCE SUB_PROVINCE , SUB.POSTAL_CODE SUB_POSTAL_CODE , SUB.COUNTRY SUB_COUNTRY , SUB.STATUS SUB_STATUS , SUB.COUNTY SUB_COUNTY , SUB.URL SUB_URL , SUB.EMAIL_ADDRESS SUB_EMAIL_ADDRESS, SUB.ORGANIZATION_NAME_PHONETIC SUB_ORGANIZATION_NAME_PHONETIC , SUB.PERSON_FIRST_NAME_PHONETIC SUB_PERSON_FIRST_NAME_PHONETIC, SUB.PERSON_LAST_NAME_PHONETIC SUB_PERSON_LAST_NAME_PHONETIC , REL.PARTY_NUMBER REL_PARTY_NUMBER, REL.PARTY_NAME REL_PARTY_NAME , REL.PARTY_TYPE REL_PARTY_TYPE , REL.OBJECT_VERSION_NUMBER REL_OBJ_VER_NUMBER , REL.ATTRIBUTE_CATEGORY REL_ATTRIBUTE_CATEGORY , REL.ATTRIBUTE1 REL_ATTRIBUTE1 , REL.ATTRIBUTE2 REL_ATTRIBUTE2, REL.ATTRIBUTE3 REL_ATTRIBUTE3, REL.ATTRIBUTE4 REL_ATTRIBUTE4, REL.ATTRIBUTE5 REL_ATTRIBUTE5, REL.ATTRIBUTE6 REL_ATTRIBUTE6, REL.ATTRIBUTE7 REL_ATTRIBUTE7, REL.ATTRIBUTE8 REL_ATTRIBUTE8, REL.ATTRIBUTE9 REL_ATTRIBUTE9, REL.ATTRIBUTE10 REL_ATTRIBUTE10, REL.ATTRIBUTE11 REL_ATTRIBUTE11, REL.ATTRIBUTE12 REL_ATTRIBUTE12, REL.ATTRIBUTE13 REL_ATTRIBUTE13, REL.ATTRIBUTE14 REL_ATTRIBUTE14, REL.ATTRIBUTE15 REL_ATTRIBUTE15, REL.ATTRIBUTE16 REL_ATTRIBUTE16, REL.ATTRIBUTE17 REL_ATTRIBUTE17, REL.ATTRIBUTE18 REL_ATTRIBUTE18, REL.ATTRIBUTE19 REL_ATTRIBUTE19, REL.ATTRIBUTE20 REL_ATTRIBUTE20, REL.ATTRIBUTE21 REL_ATTRIBUTE21, REL.ATTRIBUTE22 REL_ATTRIBUTE22, REL.ATTRIBUTE23 REL_ATTRIBUTE23, REL.ATTRIBUTE24 REL_ATTRIBUTE24, REL.PERSON_PRE_NAME_ADJUNCT REL_PERSON_PRE_NAME_ADJUNCT, REL.PERSON_FIRST_NAME REL_PERSON_FIRST_NAME , REL.PERSON_MIDDLE_NAME REL_PERSON_MIDDLE_NAME , REL.PERSON_LAST_NAME REL_PERSON_LAST_NAME , REL.PERSON_NAME_SUFFIX REL_PERSON_NAME_SUFFIX , REL.PERSON_TITLE REL_PERSON_TITLE, LOC.ADDRESS1||' '|| LOC.ADDRESS2||' '|| LOC.ADDRESS3||' '|| LOC.ADDRESS4 REL_ADDRESS , LOC.ADDRESS1 REL_ADDRESS1, LOC.ADDRESS2 REL_ADDRESS2, LOC.ADDRESS3 REL_ADDRESS3, LOC.ADDRESS4 REL_ADDRESS4, LOC.CITY REL_CITY, LOC.STATE REL_STATE, LOC.PROVINCE REL_PROVINCE, LOC.POSTAL_CODE REL_POSTAL_CODE, LOC.COUNTRY REL_COUNTRY, REL.STATUS REL_STATUS, LOC.COUNTY REL_COUNTY , REL.URL REL_URL , REL.EMAIL_ADDRESS REL_EMAIL_ADDRESS, REL.ORGANIZATION_NAME_PHONETIC REL_ORGANIZATION_NAME_PHONETIC, REL.PERSON_FIRST_NAME_PHONETIC REL_PERSON_FIRST_NAME_PHONETIC , REL.PERSON_LAST_NAME_PHONETIC REL_PERSON_LAST_NAME_PHONETIC, PARTY_REL.RELATIONSHIP_ID PARTY_REL_RELATIONSHIP_ID, PARTY_REL.PARTY_ID PARTY_REL_PARTY_ID, PARTY_REL.SUBJECT_ID PARTY_REL_SUBJECT_ID , PARTY_REL.SUBJECT_TYPE PARTY_REL_SUBJECT_TYPE, PARTY_REL.SUBJECT_TABLE_NAME PARTY_REL_SUBJECT_TABLE_NAME, PARTY_REL.OBJECT_ID PARTY_REL_OBJECT_ID, PARTY_REL.OBJECT_TYPE PARTY_REL_OBJECT_TYPE, PARTY_REL.OBJECT_TABLE_NAME PARTY_REL_OBJECT_TABLE_NAME, PARTY_REL.RELATIONSHIP_CODE PARTY_REL_RELATIONSHIP_CODE, PARTY_REL.DIRECTIONAL_FLAG PARTY_REL_DIRECTIONAL_FLAG , PARTY_REL.START_DATE PARTY_REL_START_DATE, PARTY_REL.END_DATE PARTY_REL_END_DATE, PARTY_REL.STATUS PARTY_REL_STATUS , PARTY_REL.RELATIONSHIP_TYPE PARTY_REL_RELATIONSHIP_TYPE, PARTY_REL.OBJECT_VERSION_NUMBER, LKUP.MEANING LKUP_MEANING , TERI.TERRITORY_SHORT_NAME TERI_TERRITORY_SHORT_NAME , OBJ_TERI.TERRITORY_SHORT_NAME OBJ_TERI_TERRITORY_SHORT_NAME, loc.location_id rel_location_id, SITES.STATUS SITE_STATUS FROM HZ_PARTIES OBJ, HZ_PARTIES SUB, HZ_PARTIES REL, HZ_RELATIONSHIPS PARTY_REL, AR_LOOKUPS LKUP, FND_TERRITORIES_TL TERI, FND_TERRITORIES_TL OBJ_TERI,HZ_LOCATIONS LOC, HZ_PARTY_SITES SITES WHERE SUB.Party_Type = 'PERSON' AND OBJ.Party_Type in ('PERSON','ORGANIZATION') AND SUB.party_id = PARTY_REL.subject_id AND OBJ.party_id = PARTY_REL.object_id AND REL.party_id = PARTY_REL.party_id AND LOC.LOCATION_ID=SITES.LOCATION_ID AND SITES.PARTY_ID=REL.PARTY_ID AND LOC.ADDRESS_EXPIRATION_DATE IS NULL AND LOC.COUNTRY = TERI.TERRITORY_CODE(+) AND OBJ.COUNTRY = OBJ_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 OBJ.STATUS NOT IN ('M', 'D') AND REL.STATUS NOT IN ('M', 'D') AND SUB.STATUS NOT IN ('M', 'D') AND TERI.LANGUAGE(+) = USERENV('LANG') AND OBJ_TERI.LANGUAGE(+) = USERENV('LANG') AND NVL(SITES.CREATED_BY_MODULE,'XXX') <> 'SR_ONETIME' UNION ALL SELECT PRTY.ROWID , PRTY.jgzz_fiscal_code, PRTY.PARTY_ID, 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 , PRTY.ADDRESS1||' '||PRTY.ADDRESS2||' '||PRTY.ADDRESS3||' '||PRTY.ADDRESS4 ADDRESS ,PRTY.ADDRESS1 , PRTY.ADDRESS2 ,PRTY.ADDRESS3 ,PRTY.ADDRESS4 ,PRTY.CITY ,PRTY.STATE ,PRTY.PROVINCE , PRTY.POSTAL_CODE ,PRTY.COUNTRY ,PRTY.STATUS ,PRTY.COUNTY ,PRTY.URL ,PRTY.EMAIL_ADDRESS , PRTY.ORGANIZATION_NAME_PHONETIC ,PRTY.PERSON_FIRST_NAME_PHONETIC ,PRTY.PERSON_LAST_NAME_PHONETIC , 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_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 ,TERI.TERRITORY_SHORT_NAME ,loc.location_id rel_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 NVL(SITES.CREATED_BY_MODULE,'XXX') <> 'SR_ONETIME' AND LOC.ADDRESS_EXPIRATION_DATE IS NULL AND LOC.COUNTRY = TERI.TERRITORY_CODE(+) AND TERI.LANGUAGE(+) = USERENV ('LANG') AND PRTY.STATUS NOT IN ('M', 'D')

View Text - HTML Formatted

SELECT OBJ.ROWID
, OBJ.JGZZ_FISCAL_CODE OBJ_JGZZ_FISCAL_CODE
, REL.PARTY_ID REL_PARTY_ID
, OBJ.PARTY_ID
, OBJ.PARTY_NUMBER
, OBJ.PARTY_NAME
, OBJ.PARTY_TYPE
, OBJ.OBJECT_VERSION_NUMBER
, OBJ.ATTRIBUTE_CATEGORY
, OBJ.ATTRIBUTE1
, OBJ.ATTRIBUTE2 OBJ_ATTRIBUTE2
, OBJ.ATTRIBUTE3 OBJ_ATTRIBUTE3
, OBJ.ATTRIBUTE4 OBJ_ATTRIBUTE4
, OBJ.ATTRIBUTE5 OBJ_ATTRIBUTE5
, OBJ.ATTRIBUTE6 OBJ_ATTRIBUTE6
, OBJ.ATTRIBUTE7 OBJ_ATTRIBUTE7
, OBJ.ATTRIBUTE8 OBJ_ATTRIBUTE8
, OBJ.ATTRIBUTE9 OBJ_ATTRIBUTE9
, OBJ.ATTRIBUTE10 OBJ_ATTRIBUTE10
, OBJ.ATTRIBUTE11 OBJ_ATTRIBUTE11
, OBJ.ATTRIBUTE12 OBJ_ATTRIBUTE12
, OBJ.ATTRIBUTE13 OBJ_ATTRIBUTE13
, OBJ.ATTRIBUTE14 OBJ_ATTRIBUTE14
, OBJ.ATTRIBUTE15 OBJ_ATTRIBUTE15
, OBJ.ATTRIBUTE16 OBJ_ATTRIBUTE16
, OBJ.ATTRIBUTE17 OBJ_ATTRIBUTE17
, OBJ.ATTRIBUTE18 OBJ_ATTRIBUTE18
, OBJ.ATTRIBUTE19 OBJ_ATTRIBUTE19
, OBJ.ATTRIBUTE20 OBJ_ATTRIBUTE20
, OBJ.ATTRIBUTE21 OBJ_ATTRIBUTE21
, OBJ.ATTRIBUTE22 OBJ_ATTRIBUTE22
, OBJ.ATTRIBUTE23 OBJ_ATTRIBUTE23
, OBJ.ATTRIBUTE24 OBJ_ATTRIBUTE24
, OBJ.PERSON_PRE_NAME_ADJUNCT OBJ_PERSON_PRE_NAME_ADJUNCT
, OBJ.PERSON_FIRST_NAME OBJ_PERSON_FIRST_NAME
, OBJ.PERSON_MIDDLE_NAME OBJ_PERSON_MIDDLE_NAME
, OBJ.PERSON_LAST_NAME OBJ_PERSON_LAST_NAME
, OBJ.PERSON_NAME_SUFFIX OBJ_PERSON_NAME_SUFFIX
, OBJ.PERSON_TITLE OBJ_PERSON_TITLE
, OBJ.ADDRESS1||' '||OBJ.ADDRESS2||' '||OBJ.ADDRESS3||' '||OBJ.ADDRESS4 OBJ_ADDRESS
, OBJ.ADDRESS1 OBJ_ADDRESS1
, OBJ.ADDRESS2 OBJ_ADDRESS2
, OBJ.ADDRESS3 OBJ_ADDRESS3
, OBJ.ADDRESS4 OBJ_ADDRESS4
, OBJ.CITY OBJ_CITY
, OBJ.STATE OBJ_STATE
, OBJ.PROVINCE OBJ_PROVINCE
, OBJ.POSTAL_CODE OBJ_POSTAL_CODE
, OBJ.COUNTRY OBJ_COUNTRY
, OBJ.STATUS OBJ_STATUS
, OBJ.COUNTY OBJ_COUNTY
, OBJ.URL OBJ_URL
, OBJ.EMAIL_ADDRESS OBJ_EMAIL_ADDRESS
, OBJ.ORGANIZATION_NAME_PHONETIC OBJ_ORGANIZATION_NAME_PHONETIC
, OBJ.PERSON_FIRST_NAME_PHONETIC OBJ_PERSON_FIRST_NAME_PHONETIC
, OBJ.PERSON_LAST_NAME_PHONETIC OBJ_PERSON_LAST_NAME_PHONETIC
, SUB.PARTY_ID SUB_PARTY_ID
, SUB.PARTY_NUMBER SUB_PARTY_NUMBER
, SUB.PARTY_NAME SUB_PARTY_NAME
, SUB.PARTY_TYPE SUB_PARTY_TYPE
, SUB.OBJECT_VERSION_NUMBER SUB_OBJ_VER_NUMBER
, SUB.ATTRIBUTE_CATEGORY SUB_ATTRIBUTE_CATEGORY
, SUB.ATTRIBUTE1 SUB_ATTRIBUTE1
, SUB.ATTRIBUTE2 SUB_ATTRIBUTE2
, SUB.ATTRIBUTE3 SUB_ATTRIBUTE3
, SUB.ATTRIBUTE4 SUB_ATTRIBUTE4
, SUB.ATTRIBUTE5 SUB_ATTRIBUTE5
, SUB.ATTRIBUTE6 SUB_ATTRIBUTE6
, SUB.ATTRIBUTE7 SUB_ATTRIBUTE7
, SUB.ATTRIBUTE8 SUB_ATTRIBUTE8
, SUB.ATTRIBUTE9 SUB_ATTRIBUTE9
, SUB.ATTRIBUTE10 SUB_ATTRIBUTE10
, SUB.ATTRIBUTE11 SUB_ATTRIBUTE11
, SUB.ATTRIBUTE12 SUB_ATTRIBUTE12
, SUB.ATTRIBUTE13 SUB_ATTRIBUTE13
, SUB.ATTRIBUTE14 SUB_ATTRIBUTE14
, SUB.ATTRIBUTE15 SUB_ATTRIBUTE15
, SUB.ATTRIBUTE16 SUB_ATTRIBUTE16
, SUB.ATTRIBUTE17 SUB_ATTRIBUTE17
, SUB.ATTRIBUTE18 SUB_ATTRIBUTE18
, SUB.ATTRIBUTE19 SUB_ATTRIBUTE19
, SUB.ATTRIBUTE20 SUB_ATTRIBUTE20
, SUB.ATTRIBUTE21 SUB_ATTRIBUTE21
, SUB.ATTRIBUTE22 SUB_ATTRIBUTE22
, SUB.ATTRIBUTE23 SUB_ATTRIBUTE23
, SUB.ATTRIBUTE24 SUB_ATTRIBUTE24
, SUB.PERSON_PRE_NAME_ADJUNCT SUB_PERSON_PRE_NAME_ADJUNCT
, SUB.PERSON_FIRST_NAME SUB_PERSON_FIRST_NAME
, SUB.PERSON_MIDDLE_NAME SUB_PERSON_MIDDLE_NAME
, SUB.PERSON_LAST_NAME SUB_PERSON_LAST_NAME
, SUB.PERSON_NAME_SUFFIX SUB_PERSON_NAME_SUFFIX
, SUB.PERSON_TITLE SUB_PERSON_TITLE
, SUB.ADDRESS1||' '||SUB.ADDRESS2||' '||SUB.ADDRESS3||' '||SUB.ADDRESS4 SUB_ADDRESS
, SUB.ADDRESS1 SUB_ADDRESS1
, SUB.ADDRESS2 SUB_ADDRESS2
, SUB.ADDRESS3 SUB_ADDRESS3
, SUB.ADDRESS4 SUB_ADDRESS4
, SUB.CITY SUB_CITY
, SUB.STATE SUB_STATE
, SUB.PROVINCE SUB_PROVINCE
, SUB.POSTAL_CODE SUB_POSTAL_CODE
, SUB.COUNTRY SUB_COUNTRY
, SUB.STATUS SUB_STATUS
, SUB.COUNTY SUB_COUNTY
, SUB.URL SUB_URL
, SUB.EMAIL_ADDRESS SUB_EMAIL_ADDRESS
, SUB.ORGANIZATION_NAME_PHONETIC SUB_ORGANIZATION_NAME_PHONETIC
, SUB.PERSON_FIRST_NAME_PHONETIC SUB_PERSON_FIRST_NAME_PHONETIC
, SUB.PERSON_LAST_NAME_PHONETIC SUB_PERSON_LAST_NAME_PHONETIC
, REL.PARTY_NUMBER REL_PARTY_NUMBER
, REL.PARTY_NAME REL_PARTY_NAME
, REL.PARTY_TYPE REL_PARTY_TYPE
, REL.OBJECT_VERSION_NUMBER REL_OBJ_VER_NUMBER
, REL.ATTRIBUTE_CATEGORY REL_ATTRIBUTE_CATEGORY
, REL.ATTRIBUTE1 REL_ATTRIBUTE1
, REL.ATTRIBUTE2 REL_ATTRIBUTE2
, REL.ATTRIBUTE3 REL_ATTRIBUTE3
, REL.ATTRIBUTE4 REL_ATTRIBUTE4
, REL.ATTRIBUTE5 REL_ATTRIBUTE5
, REL.ATTRIBUTE6 REL_ATTRIBUTE6
, REL.ATTRIBUTE7 REL_ATTRIBUTE7
, REL.ATTRIBUTE8 REL_ATTRIBUTE8
, REL.ATTRIBUTE9 REL_ATTRIBUTE9
, REL.ATTRIBUTE10 REL_ATTRIBUTE10
, REL.ATTRIBUTE11 REL_ATTRIBUTE11
, REL.ATTRIBUTE12 REL_ATTRIBUTE12
, REL.ATTRIBUTE13 REL_ATTRIBUTE13
, REL.ATTRIBUTE14 REL_ATTRIBUTE14
, REL.ATTRIBUTE15 REL_ATTRIBUTE15
, REL.ATTRIBUTE16 REL_ATTRIBUTE16
, REL.ATTRIBUTE17 REL_ATTRIBUTE17
, REL.ATTRIBUTE18 REL_ATTRIBUTE18
, REL.ATTRIBUTE19 REL_ATTRIBUTE19
, REL.ATTRIBUTE20 REL_ATTRIBUTE20
, REL.ATTRIBUTE21 REL_ATTRIBUTE21
, REL.ATTRIBUTE22 REL_ATTRIBUTE22
, REL.ATTRIBUTE23 REL_ATTRIBUTE23
, REL.ATTRIBUTE24 REL_ATTRIBUTE24
, REL.PERSON_PRE_NAME_ADJUNCT REL_PERSON_PRE_NAME_ADJUNCT
, REL.PERSON_FIRST_NAME REL_PERSON_FIRST_NAME
, REL.PERSON_MIDDLE_NAME REL_PERSON_MIDDLE_NAME
, REL.PERSON_LAST_NAME REL_PERSON_LAST_NAME
, REL.PERSON_NAME_SUFFIX REL_PERSON_NAME_SUFFIX
, REL.PERSON_TITLE REL_PERSON_TITLE
, LOC.ADDRESS1||' '|| LOC.ADDRESS2||' '|| LOC.ADDRESS3||' '|| LOC.ADDRESS4 REL_ADDRESS
, LOC.ADDRESS1 REL_ADDRESS1
, LOC.ADDRESS2 REL_ADDRESS2
, LOC.ADDRESS3 REL_ADDRESS3
, LOC.ADDRESS4 REL_ADDRESS4
, LOC.CITY REL_CITY
, LOC.STATE REL_STATE
, LOC.PROVINCE REL_PROVINCE
, LOC.POSTAL_CODE REL_POSTAL_CODE
, LOC.COUNTRY REL_COUNTRY
, REL.STATUS REL_STATUS
, LOC.COUNTY REL_COUNTY
, REL.URL REL_URL
, REL.EMAIL_ADDRESS REL_EMAIL_ADDRESS
, REL.ORGANIZATION_NAME_PHONETIC REL_ORGANIZATION_NAME_PHONETIC
, REL.PERSON_FIRST_NAME_PHONETIC REL_PERSON_FIRST_NAME_PHONETIC
, REL.PERSON_LAST_NAME_PHONETIC REL_PERSON_LAST_NAME_PHONETIC
, PARTY_REL.RELATIONSHIP_ID PARTY_REL_RELATIONSHIP_ID
, PARTY_REL.PARTY_ID PARTY_REL_PARTY_ID
, PARTY_REL.SUBJECT_ID PARTY_REL_SUBJECT_ID
, PARTY_REL.SUBJECT_TYPE PARTY_REL_SUBJECT_TYPE
, PARTY_REL.SUBJECT_TABLE_NAME PARTY_REL_SUBJECT_TABLE_NAME
, PARTY_REL.OBJECT_ID PARTY_REL_OBJECT_ID
, PARTY_REL.OBJECT_TYPE PARTY_REL_OBJECT_TYPE
, PARTY_REL.OBJECT_TABLE_NAME PARTY_REL_OBJECT_TABLE_NAME
, PARTY_REL.RELATIONSHIP_CODE PARTY_REL_RELATIONSHIP_CODE
, PARTY_REL.DIRECTIONAL_FLAG PARTY_REL_DIRECTIONAL_FLAG
, PARTY_REL.START_DATE PARTY_REL_START_DATE
, PARTY_REL.END_DATE PARTY_REL_END_DATE
, PARTY_REL.STATUS PARTY_REL_STATUS
, PARTY_REL.RELATIONSHIP_TYPE PARTY_REL_RELATIONSHIP_TYPE
, PARTY_REL.OBJECT_VERSION_NUMBER
, LKUP.MEANING LKUP_MEANING
, TERI.TERRITORY_SHORT_NAME TERI_TERRITORY_SHORT_NAME
, OBJ_TERI.TERRITORY_SHORT_NAME OBJ_TERI_TERRITORY_SHORT_NAME
, LOC.LOCATION_ID REL_LOCATION_ID
, SITES.STATUS SITE_STATUS
FROM HZ_PARTIES OBJ
, HZ_PARTIES SUB
, HZ_PARTIES REL
, HZ_RELATIONSHIPS PARTY_REL
, AR_LOOKUPS LKUP
, FND_TERRITORIES_TL TERI
, FND_TERRITORIES_TL OBJ_TERI
, HZ_LOCATIONS LOC
, HZ_PARTY_SITES SITES
WHERE SUB.PARTY_TYPE = 'PERSON'
AND OBJ.PARTY_TYPE IN ('PERSON'
, 'ORGANIZATION')
AND SUB.PARTY_ID = PARTY_REL.SUBJECT_ID
AND OBJ.PARTY_ID = PARTY_REL.OBJECT_ID
AND REL.PARTY_ID = PARTY_REL.PARTY_ID
AND LOC.LOCATION_ID=SITES.LOCATION_ID
AND SITES.PARTY_ID=REL.PARTY_ID
AND LOC.ADDRESS_EXPIRATION_DATE IS NULL
AND LOC.COUNTRY = TERI.TERRITORY_CODE(+)
AND OBJ.COUNTRY = OBJ_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 OBJ.STATUS NOT IN ('M'
, 'D')
AND REL.STATUS NOT IN ('M'
, 'D')
AND SUB.STATUS NOT IN ('M'
, 'D')
AND TERI.LANGUAGE(+) = USERENV('LANG')
AND OBJ_TERI.LANGUAGE(+) = USERENV('LANG')
AND NVL(SITES.CREATED_BY_MODULE
, 'XXX') <> 'SR_ONETIME' UNION ALL SELECT PRTY.ROWID
, PRTY.JGZZ_FISCAL_CODE
, PRTY.PARTY_ID
, 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
, PRTY.ADDRESS1||' '||PRTY.ADDRESS2||' '||PRTY.ADDRESS3||' '||PRTY.ADDRESS4 ADDRESS
, PRTY.ADDRESS1
, PRTY.ADDRESS2
, PRTY.ADDRESS3
, PRTY.ADDRESS4
, PRTY.CITY
, PRTY.STATE
, PRTY.PROVINCE
, PRTY.POSTAL_CODE
, PRTY.COUNTRY
, PRTY.STATUS
, PRTY.COUNTY
, PRTY.URL
, PRTY.EMAIL_ADDRESS
, PRTY.ORGANIZATION_NAME_PHONETIC
, PRTY.PERSON_FIRST_NAME_PHONETIC
, PRTY.PERSON_LAST_NAME_PHONETIC
, 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_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
, TERI.TERRITORY_SHORT_NAME
, LOC.LOCATION_ID REL_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 NVL(SITES.CREATED_BY_MODULE
, 'XXX') <> 'SR_ONETIME'
AND LOC.ADDRESS_EXPIRATION_DATE IS NULL
AND LOC.COUNTRY = TERI.TERRITORY_CODE(+)
AND TERI.LANGUAGE(+) = USERENV ('LANG')
AND PRTY.STATUS NOT IN ('M'
, 'D')