VIEW: APPS.AST_LM_REL_SMARTSEARCH_V

Source

View Text - Preformatted

SELECT o.party_name, o.party_id, o.party_number, 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, R.PARTY_ID, r.party_number, R.PARTY_NAME, ALK1.DESCRIPTION, orgcnt.JOB_TITLE, alk.meaning JOB_TITLE_CODE, smartcontact.score score, r.email_address, NVL(primphone.phone_country_code, '   ')||'-'|| NVL(primphone.phone_area_code, '  ')||'-'|| primphone.phone_number ||''||primphone.phone_extension FULL_PHONE_NUMBER, primphone.phone_area_code||primphone.phone_number PHONE_AREA_NUMBER, primphone.phone_area_code, primphone.phone_country_code, primphone.phone_number, primphone.phone_extension, r.URL, ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS_LABEL ( Null, r.ADDRESS1, r.ADDRESS2, r.ADDRESS3, r.ADDRESS4, r.CITY, r.COUNTY, r.STATE, r.PROVINCE, r.POSTAL_CODE, r.COUNTRY, FTT.TERRITORY_SHORT_NAME, Null, Null, Null, Null, Null, FND_PROFILE.Value('AST_DEFAULT_COUNTRY'), NULL, NULL, 2000, 1, 1 ), r.ADDRESS1||' '||r.ADDRESS2||' '||r.ADDRESS3||' '||r.ADDRESS4 Address, r.CITY, r.COUNTY, r.STATE, r.POSTAL_CODE, r.PROVINCE, r.country, FTT.TERRITORY_SHORT_NAME, REL.STATUS, ALK2.MEANING STATUS_MEAN, r.PARTY_TYPE, o.CERTIFICATION_LEVEL, alk3.meaning CERT_LEVEL_MEAN, null ACCOUNT_NUMBER, to_number(null) CUST_ACCOUNT_ID, smartcontact.search_context_id from hz_parties o, hz_parties p, hz_parties r, hz_relationships rel, hz_relationship_types reltype, hz_contact_points primphone, hz_org_contacts orgcnt, hz_matched_contacts_gt smartcontact, FND_TERRITORIES_TL FTT, ar_lookups alk, ar_lookups alk1, ar_lookups alk2, ar_lookups alk3 WHERE o.party_id=rel.object_id AND o.status in ('A', 'I') AND o.party_type='ORGANIZATION' AND p.party_id=rel.subject_id AND p.status in ('A', 'I') AND p.party_type = 'PERSON' AND rel.party_id=r.party_id AND r.party_type='PARTY_RELATIONSHIP' AND r.status in ('A', 'I') AND rel.subject_Type='PERSON' AND rel.objecT_type='ORGANIZATION' AND reltype.relationship_type=rel.relationship_type AND rel.relationship_code=relTYPE.forward_rel_code AND reltype.subject_type=rel.subject_Type AND reltype.object_type=rel.object_type AND smartcontact.org_contact_id=orgcnt.org_contact_id AND orgcnt.party_relationship_id=rel.relationship_id AND smartcontact.party_id=rel.object_id AND ALK.LOOKUP_TYPE(+) = 'RESPONSIBILITY' AND ALK.ENABLED_FLAG(+) = 'Y' AND ALK.LOOKUP_CODE(+) = ORGCNT.JOB_TITLE_CODE AND ALK1.LOOKUP_TYPE='HZ_RELATIONSHIP_ROLE' AND ALK1.LOOKUP_CODE=RELTYPE.ROLE AND ALK2.LOOKUP_TYPE='REGISTRY_STATUS' AND ALK2.LOOKUP_CODE=REL.STATUS AND primphone.owner_table_id(+)=rel.party_id AND primphone.owner_table_name(+)='HZ_PARTIES' AND primphone.contact_point_type(+)='PHONE' AND primphone.primary_flag(+)='Y' AND alk3.lookup_type(+)='HZ_PARTY_CERT_LEVEL' AND alk3.lookup_code(+)=o.certification_level AND r.COUNTRY = FTT.TERRITORY_CODE(+) AND FTT.LANGUAGE(+) = userenv('LANG') AND FTT.SOURCE_LANG(+) = userenv('LANG')

View Text - HTML Formatted

SELECT O.PARTY_NAME
, O.PARTY_ID
, O.PARTY_NUMBER
, 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
, R.PARTY_ID
, R.PARTY_NUMBER
, R.PARTY_NAME
, ALK1.DESCRIPTION
, ORGCNT.JOB_TITLE
, ALK.MEANING JOB_TITLE_CODE
, SMARTCONTACT.SCORE SCORE
, R.EMAIL_ADDRESS
, NVL(PRIMPHONE.PHONE_COUNTRY_CODE
, ' ')||'-'|| NVL(PRIMPHONE.PHONE_AREA_CODE
, ' ')||'-'|| PRIMPHONE.PHONE_NUMBER ||''||PRIMPHONE.PHONE_EXTENSION FULL_PHONE_NUMBER
, PRIMPHONE.PHONE_AREA_CODE||PRIMPHONE.PHONE_NUMBER PHONE_AREA_NUMBER
, PRIMPHONE.PHONE_AREA_CODE
, PRIMPHONE.PHONE_COUNTRY_CODE
, PRIMPHONE.PHONE_NUMBER
, PRIMPHONE.PHONE_EXTENSION
, R.URL
, ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS_LABEL ( NULL
, R.ADDRESS1
, R.ADDRESS2
, R.ADDRESS3
, R.ADDRESS4
, R.CITY
, R.COUNTY
, R.STATE
, R.PROVINCE
, R.POSTAL_CODE
, R.COUNTRY
, FTT.TERRITORY_SHORT_NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, FND_PROFILE.VALUE('AST_DEFAULT_COUNTRY')
, NULL
, NULL
, 2000
, 1
, 1 )
, R.ADDRESS1||' '||R.ADDRESS2||' '||R.ADDRESS3||' '||R.ADDRESS4 ADDRESS
, R.CITY
, R.COUNTY
, R.STATE
, R.POSTAL_CODE
, R.PROVINCE
, R.COUNTRY
, FTT.TERRITORY_SHORT_NAME
, REL.STATUS
, ALK2.MEANING STATUS_MEAN
, R.PARTY_TYPE
, O.CERTIFICATION_LEVEL
, ALK3.MEANING CERT_LEVEL_MEAN
, NULL ACCOUNT_NUMBER
, TO_NUMBER(NULL) CUST_ACCOUNT_ID
, SMARTCONTACT.SEARCH_CONTEXT_ID
FROM HZ_PARTIES O
, HZ_PARTIES P
, HZ_PARTIES R
, HZ_RELATIONSHIPS REL
, HZ_RELATIONSHIP_TYPES RELTYPE
, HZ_CONTACT_POINTS PRIMPHONE
, HZ_ORG_CONTACTS ORGCNT
, HZ_MATCHED_CONTACTS_GT SMARTCONTACT
, FND_TERRITORIES_TL FTT
, AR_LOOKUPS ALK
, AR_LOOKUPS ALK1
, AR_LOOKUPS ALK2
, AR_LOOKUPS ALK3
WHERE O.PARTY_ID=REL.OBJECT_ID
AND O.STATUS IN ('A'
, 'I')
AND O.PARTY_TYPE='ORGANIZATION'
AND P.PARTY_ID=REL.SUBJECT_ID
AND P.STATUS IN ('A'
, 'I')
AND P.PARTY_TYPE = 'PERSON'
AND REL.PARTY_ID=R.PARTY_ID
AND R.PARTY_TYPE='PARTY_RELATIONSHIP'
AND R.STATUS IN ('A'
, 'I')
AND REL.SUBJECT_TYPE='PERSON'
AND REL.OBJECT_TYPE='ORGANIZATION'
AND RELTYPE.RELATIONSHIP_TYPE=REL.RELATIONSHIP_TYPE
AND REL.RELATIONSHIP_CODE=RELTYPE.FORWARD_REL_CODE
AND RELTYPE.SUBJECT_TYPE=REL.SUBJECT_TYPE
AND RELTYPE.OBJECT_TYPE=REL.OBJECT_TYPE
AND SMARTCONTACT.ORG_CONTACT_ID=ORGCNT.ORG_CONTACT_ID
AND ORGCNT.PARTY_RELATIONSHIP_ID=REL.RELATIONSHIP_ID
AND SMARTCONTACT.PARTY_ID=REL.OBJECT_ID
AND ALK.LOOKUP_TYPE(+) = 'RESPONSIBILITY'
AND ALK.ENABLED_FLAG(+) = 'Y'
AND ALK.LOOKUP_CODE(+) = ORGCNT.JOB_TITLE_CODE
AND ALK1.LOOKUP_TYPE='HZ_RELATIONSHIP_ROLE'
AND ALK1.LOOKUP_CODE=RELTYPE.ROLE
AND ALK2.LOOKUP_TYPE='REGISTRY_STATUS'
AND ALK2.LOOKUP_CODE=REL.STATUS
AND PRIMPHONE.OWNER_TABLE_ID(+)=REL.PARTY_ID
AND PRIMPHONE.OWNER_TABLE_NAME(+)='HZ_PARTIES'
AND PRIMPHONE.CONTACT_POINT_TYPE(+)='PHONE'
AND PRIMPHONE.PRIMARY_FLAG(+)='Y'
AND ALK3.LOOKUP_TYPE(+)='HZ_PARTY_CERT_LEVEL'
AND ALK3.LOOKUP_CODE(+)=O.CERTIFICATION_LEVEL
AND R.COUNTRY = FTT.TERRITORY_CODE(+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND FTT.SOURCE_LANG(+) = USERENV('LANG')