VIEW: APPS.IEX_LM_REL_EXPAND_V

Source

View Text - Preformatted

SELECT O.PARTY_NAME PARTY_NAME, O.PARTY_ID PARTY_ID, O.PARTY_NUMBER PARTY_NUMBER, to_char(null) ACCOUNT_NUMBER, to_number(null) CUST_ACCOUNT_ID, R.PARTY_ID REL_PARTY_ID, R.PARTY_NUMBER REL_PARTY_NUMBER, REL.STATUS STATUS, O.SIC_CODE SIC_CODE, O.FISCAL_YEAREND_MONTH FISCAL_YEAREND_MONTH, O.CURR_FY_POTENTIAL_REVENUE CURR_FY_POTENTIAL_REVENUE, O.TAX_REFERENCE TAX_REFERENCE, P.PARTY_ID PERSON_ID, P.PARTY_NAME PERSON_NAME, P.PERSON_FIRST_NAME PERSON_FIRST_NAME, P.PERSON_MIDDLE_NAME PERSON_MIDDLE_NAME, P.PERSON_LAST_NAME PERSON_LAST_NAME, P.PERSON_FIRST_NAME || ' ' || P.PERSON_LAST_NAME FULL_NAME, loc.ADDRESS1||loc.ADDRESS2||loc.ADDRESS3||loc.ADDRESS4 ADDRESS, loc.ADDRESS1 ADDRESS1, loc.ADDRESS2 ADDRESS2, loc.ADDRESS3 ADDRESS3, loc.ADDRESS4 ADDRESS4, loc.CITY CITY, loc.COUNTY COUNTY, loc.STATE STATE, loc.PROVINCE PROVINCE, loc.POSTAL_CODE POSTAL_CODE, loc.ADDRESS_LINES_PHONETIC ADDRESS_LINES_PHONETIC, loc.ACTUAL_CONTENT_SOURCE ACTUAL_CONTENT_SOURCE, loc.ADDRESS_EFFECTIVE_DATE ADDRESS_EFFECTIVE_DATE, loc.ADDRESS_ERROR_CODE ADDRESS_ERROR_CODE, loc.ADDRESS_EXPIRATION_DATE ADDRESS_EXPIRATION_DATE, loc.ADDRESS_KEY ADDRESS_KEY, loc.ADDRESS_STYLE ADDRESS_STYLE, loc.APARTMENT_FLAG APARTMENT_FLAG, loc.APARTMENT_NUMBER APARTMENT_NUMBER, loc.APPLICATION_ID APPLICATION_ID, loc.BUILDING BUILDING, loc.CLLI_CODE CLLI_CODE, loc.CONTENT_SOURCE_TYPE CONTENT_SOURCE_TYPE, loc.COUNTRY COUNTRY, loc.CREATED_BY CREATED_BY, loc.CREATED_BY_MODULE CREATED_BY_MODULE, loc.CREATION_DATE CREATION_DATE, loc.DELIVERY_POINT_CODE DELIVERY_POINT_CODE, loc.DESCRIPTION DESCRIPTION, loc.DODAAC DODAAC, loc.FA_LOCATION_ID FA_LOCATION_ID, loc.FLOOR FLOOR, loc.GEOMETRY_STATUS_CODE GEOMETRY_STATUS_CODE, loc.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY, loc.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1, loc.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10, loc.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11, loc.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12, loc.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13, loc.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14, loc.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15, loc.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16, loc.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17, loc.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18, loc.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19, loc.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2, loc.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20, loc.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3, loc.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4, loc.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5, loc.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6, loc.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7, loc.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8, loc.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9, loc.HOUSE_NUMBER HOUSE_NUMBER, loc.LAST_UPDATE_DATE LAST_UPDATE_DATE, loc.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, loc.LAST_UPDATED_BY LAST_UPDATED_BY, loc.LIFE_CYCLE_STATUS LIFE_CYCLE_STATUS, loc.LOC_HIERARCHY_ID LOC_HIERARCHY_ID, loc.LOCATION_DIRECTIONS LOCATION_DIRECTIONS, loc.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER, loc.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE, loc.OVERSEAS_ADDRESS_FLAG OVERSEAS_ADDRESS_FLAG, loc.PO_BOX_NUMBER PO_BOX_NUMBER, loc.POSITION POSITION, loc.POST_OFFICE POST_OFFICE, loc.POSTAL_PLUS4_CODE POSTAL_PLUS4_CODE, loc.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID, loc.PROGRAM_ID PROGRAM_ID, loc.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE, loc.REQUEST_ID REQUEST_ID, loc.ROOM ROOM, loc.RURAL_ROUTE_NUMBER RURAL_ROUTE_NUMBER, loc.RURAL_ROUTE_TYPE RURAL_ROUTE_TYPE, loc.SALES_TAX_GEOCODE SALES_TAX_GEOCODE, loc.SALES_TAX_INSIDE_CITY_LIMITS SALES_TAX_INSIDE_CITY_LIMITS, loc.SECONDARY_SUFFIX_ELEMENT SECONDARY_SUFFIX_ELEMENT, loc.SHORT_DESCRIPTION SHORT_DESCRIPTION, loc.STREET STREET, loc.STREET_NUMBER STREET_NUMBER, loc.STREET_SUFFIX STREET_SUFFIX, loc.SUITE SUITE, time.timezone_code TIME_ZONE , loc.TRAILING_DIRECTORY_CODE TRAILING_DIRECTORY_CODE, loc.VALIDATED_FLAG VALIDATED_FLAG, loc.WH_UPDATE_DATE WH_UPDATE_DATE, FTT.TERRITORY_SHORT_NAME COUNTRY_NAME, CPT2.EMAIL_ADDRESS EMAIL_ADDRESS, CNT.JOB_TITLE JOB_TITLE, ALK1.MEANING JOB_TITLE_TYPE, CNT.DEPARTMENT_CODE DEPARTMENT_CODE, CNT.DEPARTMENT DEPARTMENT, CPT.TELEPHONE_TYPE TELEPHONE_TYPE, CPT.PHONE_LINE_TYPE PHONE_LINE_TYPE, CPT.PHONE_COUNTRY_CODE PHONE_COUNTRY_CODE, CPT.PHONE_AREA_CODE PHONE_AREA_CODE, CPT.PHONE_NUMBER PHONE_NUMBER, CPT.PHONE_EXTENSION PHONE_EXTENSION, NVL(CPT.PHONE_COUNTRY_CODE, '   ')||'-'|| NVL(CPT.PHONE_AREA_CODE, ' ')||'-'|| CPT.PHONE_NUMBER||' '||CPT.PHONE_EXTENSION FULL_PHONE_NUMBER, CPT.PHONE_AREA_CODE||CPT.PHONE_NUMBER PHONE_AREA_NUMBER, ALK.MEANING RELATIONSHIP_TYPE, REL.RELATIONSHIP_ID RELATIONSHIP_ID, R.ATTRIBUTE1 ATTRIBUTE1, R.ATTRIBUTE2 ATTRIBUTE2, R.ATTRIBUTE3 ATTRIBUTE3, R.ATTRIBUTE4 ATTRIBUTE4, R.ATTRIBUTE5 ATTRIBUTE5, R.ATTRIBUTE6 ATTRIBUTE6, R.ATTRIBUTE7 ATTRIBUTE7, R.ATTRIBUTE8 ATTRIBUTE8, R.ATTRIBUTE9 ATTRIBUTE9, R.ATTRIBUTE10 ATTRIBUTE10, R.ATTRIBUTE11 ATTRIBUTE11, R.ATTRIBUTE12 ATTRIBUTE12, R.ATTRIBUTE13 ATTRIBUTE13, R.ATTRIBUTE14 ATTRIBUTE14, R.ATTRIBUTE15 ATTRIBUTE15, R.ATTRIBUTE16 ATTRIBUTE16, R.ATTRIBUTE17 ATTRIBUTE17, R.ATTRIBUTE18 ATTRIBUTE18, R.ATTRIBUTE19 ATTRIBUTE19, R.ATTRIBUTE20 ATTRIBUTE20, CPT.PRIMARY_FLAG CP_PRIMARY_FLAG FROM HZ_RELATIONSHIPS REL , HZ_PARTIES P , HZ_PARTIES O , HZ_PARTIES R , HZ_CONTACT_POINTS CPT , HZ_CONTACT_POINTS CPT2 , HZ_ORG_CONTACTS CNT , AR_LOOKUPS ALK , AR_LOOKUPS ALK1 , FND_TERRITORIES_TL FTT , HZ_PARTY_SITES SITE , HZ_LOCATIONS LOC , fnd_TIMEZONES_TL TIME, fnd_timezones_b time_b WHERE SITE.LOCATION_ID = LOC.LOCATION_ID(+) AND TIME_B.upgrade_tz_id(+)= LOC.TIMEZONE_ID AND time_b.timezone_code = time.timezone_code(+) AND TIME.LANGUAGE(+) = userenv('LANG') AND r.party_id = site.party_id(+) and site.IDENTIFYING_ADDRESS_FLAG(+) = 'Y' and O.PARTY_TYPE = 'ORGANIZATION' AND O.PARTY_ID = REL.OBJECT_ID AND REL.OBJECT_TYPE = O.PARTY_TYPE AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.SUBJECT_ID = P.PARTY_ID AND REL.SUBJECT_TYPE = P.PARTY_TYPE AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.RELATIONSHIP_ID = CNT.PARTY_RELATIONSHIP_ID AND R.PARTY_ID = REL.PARTY_ID AND R.PARTY_TYPE = 'PARTY_RELATIONSHIP' AND CPT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND CPT.OWNER_TABLE_ID(+) = REL.PARTY_ID AND CPT.CONTACT_POINT_TYPE(+) = 'PHONE' AND CPT2.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND CPT2.OWNER_TABLE_ID(+) = REL.PARTY_ID AND CPT2.CONTACT_POINT_TYPE(+) = 'EMAIL' AND REL.RELATIONSHIP_CODE = ALK.LOOKUP_CODE (+) AND ALK.LOOKUP_TYPE (+) = 'PARTY_RELATIONS_TYPE' AND ALK.ENABLED_FLAG (+) = 'Y' AND loc.COUNTRY = FTT.TERRITORY_CODE(+) AND FTT.LANGUAGE(+) = userenv('LANG') AND FTT.SOURCE_LANG(+) = userenv('LANG') AND ALK1.LOOKUP_TYPE(+) = 'RESPONSIBILITY' AND ALK1.ENABLED_FLAG(+) = 'Y' AND ALK1.LOOKUP_CODE(+) = CNT.JOB_TITLE_CODE

View Text - HTML Formatted

SELECT O.PARTY_NAME PARTY_NAME
, O.PARTY_ID PARTY_ID
, O.PARTY_NUMBER PARTY_NUMBER
, TO_CHAR(NULL) ACCOUNT_NUMBER
, TO_NUMBER(NULL) CUST_ACCOUNT_ID
, R.PARTY_ID REL_PARTY_ID
, R.PARTY_NUMBER REL_PARTY_NUMBER
, REL.STATUS STATUS
, O.SIC_CODE SIC_CODE
, O.FISCAL_YEAREND_MONTH FISCAL_YEAREND_MONTH
, O.CURR_FY_POTENTIAL_REVENUE CURR_FY_POTENTIAL_REVENUE
, O.TAX_REFERENCE TAX_REFERENCE
, P.PARTY_ID PERSON_ID
, P.PARTY_NAME PERSON_NAME
, P.PERSON_FIRST_NAME PERSON_FIRST_NAME
, P.PERSON_MIDDLE_NAME PERSON_MIDDLE_NAME
, P.PERSON_LAST_NAME PERSON_LAST_NAME
, P.PERSON_FIRST_NAME || ' ' || P.PERSON_LAST_NAME FULL_NAME
, LOC.ADDRESS1||LOC.ADDRESS2||LOC.ADDRESS3||LOC.ADDRESS4 ADDRESS
, LOC.ADDRESS1 ADDRESS1
, LOC.ADDRESS2 ADDRESS2
, LOC.ADDRESS3 ADDRESS3
, LOC.ADDRESS4 ADDRESS4
, LOC.CITY CITY
, LOC.COUNTY COUNTY
, LOC.STATE STATE
, LOC.PROVINCE PROVINCE
, LOC.POSTAL_CODE POSTAL_CODE
, LOC.ADDRESS_LINES_PHONETIC ADDRESS_LINES_PHONETIC
, LOC.ACTUAL_CONTENT_SOURCE ACTUAL_CONTENT_SOURCE
, LOC.ADDRESS_EFFECTIVE_DATE ADDRESS_EFFECTIVE_DATE
, LOC.ADDRESS_ERROR_CODE ADDRESS_ERROR_CODE
, LOC.ADDRESS_EXPIRATION_DATE ADDRESS_EXPIRATION_DATE
, LOC.ADDRESS_KEY ADDRESS_KEY
, LOC.ADDRESS_STYLE ADDRESS_STYLE
, LOC.APARTMENT_FLAG APARTMENT_FLAG
, LOC.APARTMENT_NUMBER APARTMENT_NUMBER
, LOC.APPLICATION_ID APPLICATION_ID
, LOC.BUILDING BUILDING
, LOC.CLLI_CODE CLLI_CODE
, LOC.CONTENT_SOURCE_TYPE CONTENT_SOURCE_TYPE
, LOC.COUNTRY COUNTRY
, LOC.CREATED_BY CREATED_BY
, LOC.CREATED_BY_MODULE CREATED_BY_MODULE
, LOC.CREATION_DATE CREATION_DATE
, LOC.DELIVERY_POINT_CODE DELIVERY_POINT_CODE
, LOC.DESCRIPTION DESCRIPTION
, LOC.DODAAC DODAAC
, LOC.FA_LOCATION_ID FA_LOCATION_ID
, LOC.FLOOR FLOOR
, LOC.GEOMETRY_STATUS_CODE GEOMETRY_STATUS_CODE
, LOC.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY
, LOC.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1
, LOC.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10
, LOC.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11
, LOC.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12
, LOC.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13
, LOC.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14
, LOC.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15
, LOC.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16
, LOC.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17
, LOC.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18
, LOC.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19
, LOC.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2
, LOC.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20
, LOC.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3
, LOC.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4
, LOC.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5
, LOC.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6
, LOC.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7
, LOC.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8
, LOC.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9
, LOC.HOUSE_NUMBER HOUSE_NUMBER
, LOC.LAST_UPDATE_DATE LAST_UPDATE_DATE
, LOC.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, LOC.LAST_UPDATED_BY LAST_UPDATED_BY
, LOC.LIFE_CYCLE_STATUS LIFE_CYCLE_STATUS
, LOC.LOC_HIERARCHY_ID LOC_HIERARCHY_ID
, LOC.LOCATION_DIRECTIONS LOCATION_DIRECTIONS
, LOC.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, LOC.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE
, LOC.OVERSEAS_ADDRESS_FLAG OVERSEAS_ADDRESS_FLAG
, LOC.PO_BOX_NUMBER PO_BOX_NUMBER
, LOC.POSITION POSITION
, LOC.POST_OFFICE POST_OFFICE
, LOC.POSTAL_PLUS4_CODE POSTAL_PLUS4_CODE
, LOC.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, LOC.PROGRAM_ID PROGRAM_ID
, LOC.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, LOC.REQUEST_ID REQUEST_ID
, LOC.ROOM ROOM
, LOC.RURAL_ROUTE_NUMBER RURAL_ROUTE_NUMBER
, LOC.RURAL_ROUTE_TYPE RURAL_ROUTE_TYPE
, LOC.SALES_TAX_GEOCODE SALES_TAX_GEOCODE
, LOC.SALES_TAX_INSIDE_CITY_LIMITS SALES_TAX_INSIDE_CITY_LIMITS
, LOC.SECONDARY_SUFFIX_ELEMENT SECONDARY_SUFFIX_ELEMENT
, LOC.SHORT_DESCRIPTION SHORT_DESCRIPTION
, LOC.STREET STREET
, LOC.STREET_NUMBER STREET_NUMBER
, LOC.STREET_SUFFIX STREET_SUFFIX
, LOC.SUITE SUITE
, TIME.TIMEZONE_CODE TIME_ZONE
, LOC.TRAILING_DIRECTORY_CODE TRAILING_DIRECTORY_CODE
, LOC.VALIDATED_FLAG VALIDATED_FLAG
, LOC.WH_UPDATE_DATE WH_UPDATE_DATE
, FTT.TERRITORY_SHORT_NAME COUNTRY_NAME
, CPT2.EMAIL_ADDRESS EMAIL_ADDRESS
, CNT.JOB_TITLE JOB_TITLE
, ALK1.MEANING JOB_TITLE_TYPE
, CNT.DEPARTMENT_CODE DEPARTMENT_CODE
, CNT.DEPARTMENT DEPARTMENT
, CPT.TELEPHONE_TYPE TELEPHONE_TYPE
, CPT.PHONE_LINE_TYPE PHONE_LINE_TYPE
, CPT.PHONE_COUNTRY_CODE PHONE_COUNTRY_CODE
, CPT.PHONE_AREA_CODE PHONE_AREA_CODE
, CPT.PHONE_NUMBER PHONE_NUMBER
, CPT.PHONE_EXTENSION PHONE_EXTENSION
, NVL(CPT.PHONE_COUNTRY_CODE
, ' ')||'-'|| NVL(CPT.PHONE_AREA_CODE
, ' ')||'-'|| CPT.PHONE_NUMBER||' '||CPT.PHONE_EXTENSION FULL_PHONE_NUMBER
, CPT.PHONE_AREA_CODE||CPT.PHONE_NUMBER PHONE_AREA_NUMBER
, ALK.MEANING RELATIONSHIP_TYPE
, REL.RELATIONSHIP_ID RELATIONSHIP_ID
, R.ATTRIBUTE1 ATTRIBUTE1
, R.ATTRIBUTE2 ATTRIBUTE2
, R.ATTRIBUTE3 ATTRIBUTE3
, R.ATTRIBUTE4 ATTRIBUTE4
, R.ATTRIBUTE5 ATTRIBUTE5
, R.ATTRIBUTE6 ATTRIBUTE6
, R.ATTRIBUTE7 ATTRIBUTE7
, R.ATTRIBUTE8 ATTRIBUTE8
, R.ATTRIBUTE9 ATTRIBUTE9
, R.ATTRIBUTE10 ATTRIBUTE10
, R.ATTRIBUTE11 ATTRIBUTE11
, R.ATTRIBUTE12 ATTRIBUTE12
, R.ATTRIBUTE13 ATTRIBUTE13
, R.ATTRIBUTE14 ATTRIBUTE14
, R.ATTRIBUTE15 ATTRIBUTE15
, R.ATTRIBUTE16 ATTRIBUTE16
, R.ATTRIBUTE17 ATTRIBUTE17
, R.ATTRIBUTE18 ATTRIBUTE18
, R.ATTRIBUTE19 ATTRIBUTE19
, R.ATTRIBUTE20 ATTRIBUTE20
, CPT.PRIMARY_FLAG CP_PRIMARY_FLAG
FROM HZ_RELATIONSHIPS REL
, HZ_PARTIES P
, HZ_PARTIES O
, HZ_PARTIES R
, HZ_CONTACT_POINTS CPT
, HZ_CONTACT_POINTS CPT2
, HZ_ORG_CONTACTS CNT
, AR_LOOKUPS ALK
, AR_LOOKUPS ALK1
, FND_TERRITORIES_TL FTT
, HZ_PARTY_SITES SITE
, HZ_LOCATIONS LOC
, FND_TIMEZONES_TL TIME
, FND_TIMEZONES_B TIME_B
WHERE SITE.LOCATION_ID = LOC.LOCATION_ID(+)
AND TIME_B.UPGRADE_TZ_ID(+)= LOC.TIMEZONE_ID
AND TIME_B.TIMEZONE_CODE = TIME.TIMEZONE_CODE(+)
AND TIME.LANGUAGE(+) = USERENV('LANG')
AND R.PARTY_ID = SITE.PARTY_ID(+)
AND SITE.IDENTIFYING_ADDRESS_FLAG(+) = 'Y'
AND O.PARTY_TYPE = 'ORGANIZATION'
AND O.PARTY_ID = REL.OBJECT_ID
AND REL.OBJECT_TYPE = O.PARTY_TYPE
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.SUBJECT_ID = P.PARTY_ID
AND REL.SUBJECT_TYPE = P.PARTY_TYPE
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.RELATIONSHIP_ID = CNT.PARTY_RELATIONSHIP_ID
AND R.PARTY_ID = REL.PARTY_ID
AND R.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND CPT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CPT.OWNER_TABLE_ID(+) = REL.PARTY_ID
AND CPT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND CPT2.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CPT2.OWNER_TABLE_ID(+) = REL.PARTY_ID
AND CPT2.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND REL.RELATIONSHIP_CODE = ALK.LOOKUP_CODE (+)
AND ALK.LOOKUP_TYPE (+) = 'PARTY_RELATIONS_TYPE'
AND ALK.ENABLED_FLAG (+) = 'Y'
AND LOC.COUNTRY = FTT.TERRITORY_CODE(+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND FTT.SOURCE_LANG(+) = USERENV('LANG')
AND ALK1.LOOKUP_TYPE(+) = 'RESPONSIBILITY'
AND ALK1.ENABLED_FLAG(+) = 'Y'
AND ALK1.LOOKUP_CODE(+) = CNT.JOB_TITLE_CODE