VIEW: APPS.CSC_HZ_PARTIES_SRCH_REL_V

Source

View Text - Preformatted

SELECT OBJ.ROWID , REL.PARTY_ID REL_PARTY_ID , OBJ.PARTY_ID , OBJ.PARTY_NUMBER , OBJ.PARTY_NAME , OBJ.PARTY_TYPE , OBJ.OBJECT_VERSION_NUMBER , 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.STATUS OBJ_STATUS, 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 , OBJ.JGZZ_FISCAL_CODE OBJ_JGZZ_FISCAL_CODE , 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.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.STATUS SUB_STATUS , 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.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, REL.ADDRESS1||' '||REL.ADDRESS2||' '||REL.ADDRESS3||' '||REL.ADDRESS4 REL_ADDRESS , REL.ADDRESS1 REL_ADDRESS1, REL.ADDRESS2 REL_ADDRESS2, REL.ADDRESS3 REL_ADDRESS3, REL.ADDRESS4 REL_ADDRESS4, REL.CITY REL_CITY, REL.STATE REL_STATE, REL.PROVINCE REL_PROVINCE, REL.POSTAL_CODE REL_POSTAL_CODE, REL.COUNTRY REL_COUNTRY, REL.STATUS REL_STATUS, REL.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 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 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 REL.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') UNION ALL SELECT PRTY.ROWID ,PARTY_ID ,PARTY_ID ,PARTY_NUMBER ,PARTY_NAME ,PARTY_TYPE ,PRTY.OBJECT_VERSION_NUMBER ,PERSON_PRE_NAME_ADJUNCT ,PERSON_FIRST_NAME ,PERSON_MIDDLE_NAME ,PERSON_LAST_NAME ,PERSON_NAME_SUFFIX ,PERSON_TITLE ,STATUS ,ORGANIZATION_NAME_PHONETIC ,PERSON_FIRST_NAME_PHONETIC ,PERSON_LAST_NAME_PHONETIC ,JGZZ_FISCAL_CODE ,PARTY_ID ,Null ,PARTY_NAME ,Null ,PRTY.OBJECT_VERSION_NUMBER ,Null ,PERSON_FIRST_NAME ,PERSON_MIDDLE_NAME ,PERSON_LAST_NAME ,Null ,PERSON_TITLE ,STATUS ,Null ,PERSON_FIRST_NAME_PHONETIC ,PERSON_LAST_NAME_PHONETIC ,PARTY_NUMBER ,PARTY_NAME ,PARTY_TYPE ,PRTY.OBJECT_VERSION_NUMBER ,PERSON_PRE_NAME_ADJUNCT ,PERSON_FIRST_NAME ,PERSON_MIDDLE_NAME ,PERSON_LAST_NAME ,PERSON_NAME_SUFFIX ,PERSON_TITLE ,ADDRESS1||' '||ADDRESS2||' '||ADDRESS3||' '||ADDRESS4 ADDRESS ,ADDRESS1 ,ADDRESS2 ,ADDRESS3 ,ADDRESS4 ,CITY ,STATE ,PROVINCE ,POSTAL_CODE ,COUNTRY ,STATUS ,COUNTY ,URL ,EMAIL_ADDRESS ,ORGANIZATION_NAME_PHONETIC ,PERSON_FIRST_NAME_PHONETIC ,PERSON_LAST_NAME_PHONETIC ,to_number(Null) ,to_number(Null) ,to_number(Null) ,Null ,Null ,to_number(Null) ,Null ,Null ,'SELF' ,Null ,to_date(NULL) ,to_date(NULL) ,Null ,Null ,to_number(NULL) ,Null ,TERI.TERRITORY_SHORT_NAME ,TERI.TERRITORY_SHORT_NAME FROM HZ_PARTIES PRTY, FND_TERRITORIES_TL TERI WHERE party_type = 'PERSON' AND PRTY.COUNTRY = TERI.TERRITORY_CODE(+) AND STATUS NOT IN ('M', 'D') AND TERI.LANGUAGE(+) = USERENV( 'LANG') UNION ALL SELECT PRTY.ROWID ,PARTY_ID ,PARTY_ID ,PARTY_NUMBER ,PARTY_NAME ,PARTY_TYPE ,PRTY.object_version_number ,PERSON_PRE_NAME_ADJUNCT ,PERSON_FIRST_NAME ,PERSON_MIDDLE_NAME ,PERSON_LAST_NAME ,PERSON_NAME_SUFFIX ,PERSON_TITLE ,STATUS ,ORGANIZATION_NAME_PHONETIC ,PERSON_FIRST_NAME_PHONETIC ,PERSON_LAST_NAME_PHONETIC ,JGZZ_FISCAL_CODE ,PARTY_ID ,Null ,Null ,Null ,PRTY.object_version_number ,Null ,PERSON_FIRST_NAME ,PERSON_MIDDLE_NAME ,PERSON_LAST_NAME ,Null ,PERSON_TITLE ,STATUS ,Null ,Null ,Null ,Null ,Null ,PARTY_TYPE ,PRTY.object_version_number ,PERSON_PRE_NAME_ADJUNCT ,PERSON_FIRST_NAME ,PERSON_MIDDLE_NAME ,PERSON_LAST_NAME ,PERSON_NAME_SUFFIX ,PERSON_TITLE ,ADDRESS1||' '||ADDRESS2||' '||ADDRESS3||' '||ADDRESS4 ADDRESS ,ADDRESS1 ,ADDRESS2 ,ADDRESS3 ,ADDRESS4 ,CITY ,STATE ,PROVINCE ,POSTAL_CODE ,COUNTRY ,STATUS ,COUNTY ,URL ,EMAIL_ADDRESS ,ORGANIZATION_NAME_PHONETIC ,PERSON_FIRST_NAME_PHONETIC ,PERSON_LAST_NAME_PHONETIC ,to_number(Null) ,to_number(Null) ,to_number(Null) ,Null ,Null ,to_number(Null) ,Null ,Null ,Null ,Null ,to_date(NULL) ,to_date(NULL) ,Null ,Null ,to_number(NULL) ,Null ,TERI.TERRITORY_SHORT_NAME ,TERI.TERRITORY_SHORT_NAME FROM HZ_PARTIES PRTY, FND_TERRITORIES_TL TERI WHERE party_type = 'ORGANIZATION' AND PRTY.COUNTRY = TERI.TERRITORY_CODE(+) AND STATUS NOT IN ('M', 'D') AND TERI.LANGUAGE(+) = USERENV( 'LANG')

View Text - HTML Formatted

SELECT OBJ.ROWID
, REL.PARTY_ID REL_PARTY_ID
, OBJ.PARTY_ID
, OBJ.PARTY_NUMBER
, OBJ.PARTY_NAME
, OBJ.PARTY_TYPE
, OBJ.OBJECT_VERSION_NUMBER
, 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.STATUS OBJ_STATUS
, 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
, OBJ.JGZZ_FISCAL_CODE OBJ_JGZZ_FISCAL_CODE
, 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.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.STATUS SUB_STATUS
, 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.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
, REL.ADDRESS1||' '||REL.ADDRESS2||' '||REL.ADDRESS3||' '||REL.ADDRESS4 REL_ADDRESS
, REL.ADDRESS1 REL_ADDRESS1
, REL.ADDRESS2 REL_ADDRESS2
, REL.ADDRESS3 REL_ADDRESS3
, REL.ADDRESS4 REL_ADDRESS4
, REL.CITY REL_CITY
, REL.STATE REL_STATE
, REL.PROVINCE REL_PROVINCE
, REL.POSTAL_CODE REL_POSTAL_CODE
, REL.COUNTRY REL_COUNTRY
, REL.STATUS REL_STATUS
, REL.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
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
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 REL.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') UNION ALL SELECT PRTY.ROWID
, PARTY_ID
, PARTY_ID
, PARTY_NUMBER
, PARTY_NAME
, PARTY_TYPE
, PRTY.OBJECT_VERSION_NUMBER
, PERSON_PRE_NAME_ADJUNCT
, PERSON_FIRST_NAME
, PERSON_MIDDLE_NAME
, PERSON_LAST_NAME
, PERSON_NAME_SUFFIX
, PERSON_TITLE
, STATUS
, ORGANIZATION_NAME_PHONETIC
, PERSON_FIRST_NAME_PHONETIC
, PERSON_LAST_NAME_PHONETIC
, JGZZ_FISCAL_CODE
, PARTY_ID
, NULL
, PARTY_NAME
, NULL
, PRTY.OBJECT_VERSION_NUMBER
, NULL
, PERSON_FIRST_NAME
, PERSON_MIDDLE_NAME
, PERSON_LAST_NAME
, NULL
, PERSON_TITLE
, STATUS
, NULL
, PERSON_FIRST_NAME_PHONETIC
, PERSON_LAST_NAME_PHONETIC
, PARTY_NUMBER
, PARTY_NAME
, PARTY_TYPE
, PRTY.OBJECT_VERSION_NUMBER
, PERSON_PRE_NAME_ADJUNCT
, PERSON_FIRST_NAME
, PERSON_MIDDLE_NAME
, PERSON_LAST_NAME
, PERSON_NAME_SUFFIX
, PERSON_TITLE
, ADDRESS1||' '||ADDRESS2||' '||ADDRESS3||' '||ADDRESS4 ADDRESS
, ADDRESS1
, ADDRESS2
, ADDRESS3
, ADDRESS4
, CITY
, STATE
, PROVINCE
, POSTAL_CODE
, COUNTRY
, STATUS
, COUNTY
, URL
, EMAIL_ADDRESS
, ORGANIZATION_NAME_PHONETIC
, PERSON_FIRST_NAME_PHONETIC
, PERSON_LAST_NAME_PHONETIC
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, 'SELF'
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, TERI.TERRITORY_SHORT_NAME
, TERI.TERRITORY_SHORT_NAME
FROM HZ_PARTIES PRTY
, FND_TERRITORIES_TL TERI
WHERE PARTY_TYPE = 'PERSON'
AND PRTY.COUNTRY = TERI.TERRITORY_CODE(+)
AND STATUS NOT IN ('M'
, 'D')
AND TERI.LANGUAGE(+) = USERENV( 'LANG') UNION ALL SELECT PRTY.ROWID
, PARTY_ID
, PARTY_ID
, PARTY_NUMBER
, PARTY_NAME
, PARTY_TYPE
, PRTY.OBJECT_VERSION_NUMBER
, PERSON_PRE_NAME_ADJUNCT
, PERSON_FIRST_NAME
, PERSON_MIDDLE_NAME
, PERSON_LAST_NAME
, PERSON_NAME_SUFFIX
, PERSON_TITLE
, STATUS
, ORGANIZATION_NAME_PHONETIC
, PERSON_FIRST_NAME_PHONETIC
, PERSON_LAST_NAME_PHONETIC
, JGZZ_FISCAL_CODE
, PARTY_ID
, NULL
, NULL
, NULL
, PRTY.OBJECT_VERSION_NUMBER
, NULL
, PERSON_FIRST_NAME
, PERSON_MIDDLE_NAME
, PERSON_LAST_NAME
, NULL
, PERSON_TITLE
, STATUS
, NULL
, NULL
, NULL
, NULL
, NULL
, PARTY_TYPE
, PRTY.OBJECT_VERSION_NUMBER
, PERSON_PRE_NAME_ADJUNCT
, PERSON_FIRST_NAME
, PERSON_MIDDLE_NAME
, PERSON_LAST_NAME
, PERSON_NAME_SUFFIX
, PERSON_TITLE
, ADDRESS1||' '||ADDRESS2||' '||ADDRESS3||' '||ADDRESS4 ADDRESS
, ADDRESS1
, ADDRESS2
, ADDRESS3
, ADDRESS4
, CITY
, STATE
, PROVINCE
, POSTAL_CODE
, COUNTRY
, STATUS
, COUNTY
, URL
, EMAIL_ADDRESS
, ORGANIZATION_NAME_PHONETIC
, PERSON_FIRST_NAME_PHONETIC
, PERSON_LAST_NAME_PHONETIC
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, TERI.TERRITORY_SHORT_NAME
, TERI.TERRITORY_SHORT_NAME
FROM HZ_PARTIES PRTY
, FND_TERRITORIES_TL TERI
WHERE PARTY_TYPE = 'ORGANIZATION'
AND PRTY.COUNTRY = TERI.TERRITORY_CODE(+)
AND STATUS NOT IN ('M'
, 'D')
AND TERI.LANGUAGE(+) = USERENV( 'LANG')