VIEW: APPS.AST_LM_REL_EXPAND_V

Source

View Text - Preformatted

SELECT DISTINCT O.PARTY_NAME , SITE.PARTY_SITE_ID, O.PARTY_ID , O.PARTY_NUMBER , null , to_number(null) cust_account_id , R.PARTY_ID , R.PARTY_NUMBER , REL.STATUS , O.SIC_CODE , O.FISCAL_YEAREND_MONTH , O.CURR_FY_POTENTIAL_REVENUE , O.TAX_REFERENCE , P.PARTY_ID , P.PARTY_NAME , P.PERSON_FIRST_NAME , P.PERSON_MIDDLE_NAME , P.PERSON_LAST_NAME , P.PERSON_FIRST_NAME || ' ' || P.PERSON_LAST_NAME , loc.ADDRESS1|| loc.ADDRESS2|| loc.ADDRESS3|| loc.ADDRESS4 , loc.ADDRESS1 , loc.ADDRESS2 , loc.ADDRESS3 , loc.ADDRESS4 , loc.CITY , loc.COUNTY , loc.STATE , loc.PROVINCE , loc.POSTAL_CODE , loc.ADDRESS_LINES_PHONETIC , loc.ACTUAL_CONTENT_SOURCE , loc.ADDRESS_EFFECTIVE_DATE , loc.ADDRESS_ERROR_CODE , loc.ADDRESS_EXPIRATION_DATE , loc.ADDRESS_KEY , loc.ADDRESS_STYLE , loc.APARTMENT_FLAG , loc.APARTMENT_NUMBER , loc.APPLICATION_ID , loc.BUILDING , loc.CLLI_CODE , loc.CONTENT_SOURCE_TYPE , loc.COUNTRY , loc.CREATED_BY , loc.CREATED_BY_MODULE , loc.CREATION_DATE , loc.DELIVERY_POINT_CODE , loc.DESCRIPTION , loc.DODAAC , loc.FA_LOCATION_ID , loc.FLOOR , loc.GEOMETRY_STATUS_CODE , loc.GLOBAL_ATTRIBUTE_CATEGORY , loc.GLOBAL_ATTRIBUTE1 , loc.GLOBAL_ATTRIBUTE10 , loc.GLOBAL_ATTRIBUTE11 , loc.GLOBAL_ATTRIBUTE12 , loc.GLOBAL_ATTRIBUTE13 , loc.GLOBAL_ATTRIBUTE14 , loc.GLOBAL_ATTRIBUTE15 , loc.GLOBAL_ATTRIBUTE16 , loc.GLOBAL_ATTRIBUTE17 , loc.GLOBAL_ATTRIBUTE18 , loc.GLOBAL_ATTRIBUTE19 , loc.GLOBAL_ATTRIBUTE2 , loc.GLOBAL_ATTRIBUTE20 , loc.GLOBAL_ATTRIBUTE3 , loc.GLOBAL_ATTRIBUTE4 , loc.GLOBAL_ATTRIBUTE5 , loc.GLOBAL_ATTRIBUTE6 , loc.GLOBAL_ATTRIBUTE7 , loc.GLOBAL_ATTRIBUTE8 , loc.GLOBAL_ATTRIBUTE9 , loc.HOUSE_NUMBER , loc.LAST_UPDATE_DATE , loc.LAST_UPDATE_LOGIN , loc.LAST_UPDATED_BY , loc.LIFE_CYCLE_STATUS , loc.LOC_HIERARCHY_ID , loc.LOCATION_DIRECTIONS , loc.OBJECT_VERSION_NUMBER , loc.ORIG_SYSTEM_REFERENCE , loc.OVERSEAS_ADDRESS_FLAG , loc.PO_BOX_NUMBER , loc.POSITION , loc.POST_OFFICE , loc.POSTAL_PLUS4_CODE , loc.PROGRAM_APPLICATION_ID , loc.PROGRAM_ID , loc.PROGRAM_UPDATE_DATE , loc.REQUEST_ID , loc.ROOM , loc.RURAL_ROUTE_NUMBER , loc.RURAL_ROUTE_TYPE , loc.SALES_TAX_GEOCODE , loc.SALES_TAX_INSIDE_CITY_LIMITS , loc.SECONDARY_SUFFIX_ELEMENT , loc.SHORT_DESCRIPTION , loc.STREET , loc.STREET_NUMBER , loc.STREET_SUFFIX , loc.SUITE , time.timezone_code TIME_ZONE , loc.TRAILING_DIRECTORY_CODE , loc.VALIDATED_FLAG , FTT.TERRITORY_SHORT_NAME , CPT2.EMAIL_ADDRESS , CNT.JOB_TITLE , ALK1.MEANING , CNT.DEPARTMENT_CODE , CNT.DEPARTMENT , CPT.TELEPHONE_TYPE , CPT.PHONE_LINE_TYPE , CPT.PHONE_COUNTRY_CODE , CPT.PHONE_AREA_CODE , CPT.PHONE_NUMBER , CPT.PHONE_EXTENSION , NVL(CPT.PHONE_COUNTRY_CODE, '   ')||'-'|| NVL(CPT.PHONE_AREA_CODE, ' ')||'-'|| CPT.PHONE_NUMBER||' '|| CPT.PHONE_EXTENSION , CPT.PHONE_AREA_CODE|| CPT.PHONE_NUMBER , ALK.DESCRIPTION , REL.RELATIONSHIP_ID , R.ATTRIBUTE1 , R.ATTRIBUTE2 , R.ATTRIBUTE3 , R.ATTRIBUTE4 , R.ATTRIBUTE5 , R.ATTRIBUTE6 , R.ATTRIBUTE7 , R.ATTRIBUTE8 , R.ATTRIBUTE9 , R.ATTRIBUTE10 , R.ATTRIBUTE11 , R.ATTRIBUTE12 , R.ATTRIBUTE13 , R.ATTRIBUTE14 , R.ATTRIBUTE15 , R.ATTRIBUTE16 , R.ATTRIBUTE17 , R.ATTRIBUTE18 , R.ATTRIBUTE19 , R.ATTRIBUTE20 , CPT.PRIMARY_FLAG , O.CERTIFICATION_LEVEL, ALK2.MEANING CERT_LEVEL_MEAN 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 , AR_LOOKUPS ALK2 , FND_TERRITORIES_TL FTT , HZ_PARTY_SITES SITE , HZ_LOCATIONS LOC , FND_TIMEZONES_B TIME , HZ_RELATIONSHIP_TYPES HZ WHERE SITE.LOCATION_ID = LOC.LOCATION_ID(+) AND TIME.UPGRADE_TZ_ID(+) = LOC.TIMEZONE_ID AND r.party_id = site.party_id(+) AND site.IDENTIFYING_ADDRESS_FLAG(+) = 'Y' AND O.PARTY_TYPE = 'ORGANIZATION' AND P.PARTY_TYPE = 'PERSON' 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 ALK.LOOKUP_TYPE(+) = 'HZ_RELATIONSHIP_ROLE' AND HZ.ROLE = ALK.LOOKUP_CODE(+) AND rel.object_type = hz.object_type AND rel.subject_type = hz.subject_type AND rel.relationship_type= hz.relationship_type AND rel.relationship_code= hz.forward_rel_code 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 AND O.STATUS IN('A','I') AND ALK2.LOOKUP_TYPE(+)='HZ_PARTY_CERT_LEVEL' AND ALK2.LOOKUP_CODE(+)=O.certification_level

View Text - HTML Formatted

SELECT DISTINCT O.PARTY_NAME
, SITE.PARTY_SITE_ID
, O.PARTY_ID
, O.PARTY_NUMBER
, NULL
, TO_NUMBER(NULL) CUST_ACCOUNT_ID
, R.PARTY_ID
, R.PARTY_NUMBER
, REL.STATUS
, O.SIC_CODE
, O.FISCAL_YEAREND_MONTH
, O.CURR_FY_POTENTIAL_REVENUE
, O.TAX_REFERENCE
, P.PARTY_ID
, P.PARTY_NAME
, P.PERSON_FIRST_NAME
, P.PERSON_MIDDLE_NAME
, P.PERSON_LAST_NAME
, P.PERSON_FIRST_NAME || ' ' || P.PERSON_LAST_NAME
, LOC.ADDRESS1|| LOC.ADDRESS2|| LOC.ADDRESS3|| LOC.ADDRESS4
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.COUNTY
, LOC.STATE
, LOC.PROVINCE
, LOC.POSTAL_CODE
, LOC.ADDRESS_LINES_PHONETIC
, LOC.ACTUAL_CONTENT_SOURCE
, LOC.ADDRESS_EFFECTIVE_DATE
, LOC.ADDRESS_ERROR_CODE
, LOC.ADDRESS_EXPIRATION_DATE
, LOC.ADDRESS_KEY
, LOC.ADDRESS_STYLE
, LOC.APARTMENT_FLAG
, LOC.APARTMENT_NUMBER
, LOC.APPLICATION_ID
, LOC.BUILDING
, LOC.CLLI_CODE
, LOC.CONTENT_SOURCE_TYPE
, LOC.COUNTRY
, LOC.CREATED_BY
, LOC.CREATED_BY_MODULE
, LOC.CREATION_DATE
, LOC.DELIVERY_POINT_CODE
, LOC.DESCRIPTION
, LOC.DODAAC
, LOC.FA_LOCATION_ID
, LOC.FLOOR
, LOC.GEOMETRY_STATUS_CODE
, LOC.GLOBAL_ATTRIBUTE_CATEGORY
, LOC.GLOBAL_ATTRIBUTE1
, LOC.GLOBAL_ATTRIBUTE10
, LOC.GLOBAL_ATTRIBUTE11
, LOC.GLOBAL_ATTRIBUTE12
, LOC.GLOBAL_ATTRIBUTE13
, LOC.GLOBAL_ATTRIBUTE14
, LOC.GLOBAL_ATTRIBUTE15
, LOC.GLOBAL_ATTRIBUTE16
, LOC.GLOBAL_ATTRIBUTE17
, LOC.GLOBAL_ATTRIBUTE18
, LOC.GLOBAL_ATTRIBUTE19
, LOC.GLOBAL_ATTRIBUTE2
, LOC.GLOBAL_ATTRIBUTE20
, LOC.GLOBAL_ATTRIBUTE3
, LOC.GLOBAL_ATTRIBUTE4
, LOC.GLOBAL_ATTRIBUTE5
, LOC.GLOBAL_ATTRIBUTE6
, LOC.GLOBAL_ATTRIBUTE7
, LOC.GLOBAL_ATTRIBUTE8
, LOC.GLOBAL_ATTRIBUTE9
, LOC.HOUSE_NUMBER
, LOC.LAST_UPDATE_DATE
, LOC.LAST_UPDATE_LOGIN
, LOC.LAST_UPDATED_BY
, LOC.LIFE_CYCLE_STATUS
, LOC.LOC_HIERARCHY_ID
, LOC.LOCATION_DIRECTIONS
, LOC.OBJECT_VERSION_NUMBER
, LOC.ORIG_SYSTEM_REFERENCE
, LOC.OVERSEAS_ADDRESS_FLAG
, LOC.PO_BOX_NUMBER
, LOC.POSITION
, LOC.POST_OFFICE
, LOC.POSTAL_PLUS4_CODE
, LOC.PROGRAM_APPLICATION_ID
, LOC.PROGRAM_ID
, LOC.PROGRAM_UPDATE_DATE
, LOC.REQUEST_ID
, LOC.ROOM
, LOC.RURAL_ROUTE_NUMBER
, LOC.RURAL_ROUTE_TYPE
, LOC.SALES_TAX_GEOCODE
, LOC.SALES_TAX_INSIDE_CITY_LIMITS
, LOC.SECONDARY_SUFFIX_ELEMENT
, LOC.SHORT_DESCRIPTION
, LOC.STREET
, LOC.STREET_NUMBER
, LOC.STREET_SUFFIX
, LOC.SUITE
, TIME.TIMEZONE_CODE TIME_ZONE
, LOC.TRAILING_DIRECTORY_CODE
, LOC.VALIDATED_FLAG
, FTT.TERRITORY_SHORT_NAME
, CPT2.EMAIL_ADDRESS
, CNT.JOB_TITLE
, ALK1.MEANING
, CNT.DEPARTMENT_CODE
, CNT.DEPARTMENT
, CPT.TELEPHONE_TYPE
, CPT.PHONE_LINE_TYPE
, CPT.PHONE_COUNTRY_CODE
, CPT.PHONE_AREA_CODE
, CPT.PHONE_NUMBER
, CPT.PHONE_EXTENSION
, NVL(CPT.PHONE_COUNTRY_CODE
, ' ')||'-'|| NVL(CPT.PHONE_AREA_CODE
, ' ')||'-'|| CPT.PHONE_NUMBER||' '|| CPT.PHONE_EXTENSION
, CPT.PHONE_AREA_CODE|| CPT.PHONE_NUMBER
, ALK.DESCRIPTION
, REL.RELATIONSHIP_ID
, R.ATTRIBUTE1
, R.ATTRIBUTE2
, R.ATTRIBUTE3
, R.ATTRIBUTE4
, R.ATTRIBUTE5
, R.ATTRIBUTE6
, R.ATTRIBUTE7
, R.ATTRIBUTE8
, R.ATTRIBUTE9
, R.ATTRIBUTE10
, R.ATTRIBUTE11
, R.ATTRIBUTE12
, R.ATTRIBUTE13
, R.ATTRIBUTE14
, R.ATTRIBUTE15
, R.ATTRIBUTE16
, R.ATTRIBUTE17
, R.ATTRIBUTE18
, R.ATTRIBUTE19
, R.ATTRIBUTE20
, CPT.PRIMARY_FLAG
, O.CERTIFICATION_LEVEL
, ALK2.MEANING CERT_LEVEL_MEAN
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
, AR_LOOKUPS ALK2
, FND_TERRITORIES_TL FTT
, HZ_PARTY_SITES SITE
, HZ_LOCATIONS LOC
, FND_TIMEZONES_B TIME
, HZ_RELATIONSHIP_TYPES HZ
WHERE SITE.LOCATION_ID = LOC.LOCATION_ID(+)
AND TIME.UPGRADE_TZ_ID(+) = LOC.TIMEZONE_ID
AND R.PARTY_ID = SITE.PARTY_ID(+)
AND SITE.IDENTIFYING_ADDRESS_FLAG(+) = 'Y'
AND O.PARTY_TYPE = 'ORGANIZATION'
AND P.PARTY_TYPE = 'PERSON'
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 ALK.LOOKUP_TYPE(+) = 'HZ_RELATIONSHIP_ROLE'
AND HZ.ROLE = ALK.LOOKUP_CODE(+)
AND REL.OBJECT_TYPE = HZ.OBJECT_TYPE
AND REL.SUBJECT_TYPE = HZ.SUBJECT_TYPE
AND REL.RELATIONSHIP_TYPE= HZ.RELATIONSHIP_TYPE
AND REL.RELATIONSHIP_CODE= HZ.FORWARD_REL_CODE
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
AND O.STATUS IN('A'
, 'I')
AND ALK2.LOOKUP_TYPE(+)='HZ_PARTY_CERT_LEVEL'
AND ALK2.LOOKUP_CODE(+)=O.CERTIFICATION_LEVEL