VIEW: APPS.AST_LM_REL_FUZZY_V

Source

View Text - Preformatted

SELECT O.PARTY_NAME , O.PARTY_ID , R.PARTY_ID , O.PARTY_NUMBER , null , to_number(null) cust_account_id , 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 , R.ADDRESS1|| R.ADDRESS2|| R.ADDRESS3|| R.ADDRESS4 , R.ADDRESS1 , R.ADDRESS2 , R.ADDRESS3 , R.ADDRESS4 , R.CITY , R.STATE , R.PROVINCE , R.COUNTY , R.POSTAL_CODE , R.COUNTRY , FTT.TERRITORY_SHORT_NAME , R.EMAIL_ADDRESS , CNT.JOB_TITLE , ALK1.MEANING , CNT.DEPARTMENT_CODE , CNT.DEPARTMENT , ALK.DESCRIPTION , REL.RELATIONSHIP_ID , O.CERTIFICATION_LEVEL , alk2.meaning CERT_LEVEL_MEAN , FUZZYPARTY.SEARCH_CONTEXT_ID , r.primary_PHONE_COUNTRY_CODE , r.primary_PHONE_AREA_CODE , r.primary_PHONE_NUMBER , r.primary_PHONE_EXTENSION , NVL(r.primary_PHONE_COUNTRY_CODE , '   ')||'-'|| NVL(r.primary_PHONE_AREA_CODE ,'  ')||'-'|| r.primary_PHONE_NUMBER ||''|| r.primary_PHONE_EXTENSION , r.primary_PHONE_AREA_CODE||r.primary_PHONE_NUMBER FROM HZ_RELATIONSHIPS REL , HZ_PARTIES P , HZ_PARTIES O , HZ_PARTIES R , HZ_ORG_CONTACTS CNT , AR_LOOKUPS ALK , AR_LOOKUPS ALK1 , AR_LOOKUPS ALK2 , FND_TERRITORIES_TL FTT , HZ_RELATIONSHIP_TYPES HZ , HZ_MATCHED_PARTIES_GT FUZZYPARTY WHERE 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 REL.DIRECTIONAL_FLAG IN ('B' ,'F') AND R.PARTY_ID = REL.PARTY_ID AND R.PARTY_TYPE = 'PARTY_RELATIONSHIP' 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 R.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 AND FUZZYPARTY.PARTY_ID=O.PARTY_ID

View Text - HTML Formatted

SELECT O.PARTY_NAME
, O.PARTY_ID
, R.PARTY_ID
, O.PARTY_NUMBER
, NULL
, TO_NUMBER(NULL) CUST_ACCOUNT_ID
, 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
, R.ADDRESS1|| R.ADDRESS2|| R.ADDRESS3|| R.ADDRESS4
, R.ADDRESS1
, R.ADDRESS2
, R.ADDRESS3
, R.ADDRESS4
, R.CITY
, R.STATE
, R.PROVINCE
, R.COUNTY
, R.POSTAL_CODE
, R.COUNTRY
, FTT.TERRITORY_SHORT_NAME
, R.EMAIL_ADDRESS
, CNT.JOB_TITLE
, ALK1.MEANING
, CNT.DEPARTMENT_CODE
, CNT.DEPARTMENT
, ALK.DESCRIPTION
, REL.RELATIONSHIP_ID
, O.CERTIFICATION_LEVEL
, ALK2.MEANING CERT_LEVEL_MEAN
, FUZZYPARTY.SEARCH_CONTEXT_ID
, R.PRIMARY_PHONE_COUNTRY_CODE
, R.PRIMARY_PHONE_AREA_CODE
, R.PRIMARY_PHONE_NUMBER
, R.PRIMARY_PHONE_EXTENSION
, NVL(R.PRIMARY_PHONE_COUNTRY_CODE
, ' ')||'-'|| NVL(R.PRIMARY_PHONE_AREA_CODE
, ' ')||'-'|| R.PRIMARY_PHONE_NUMBER ||''|| R.PRIMARY_PHONE_EXTENSION
, R.PRIMARY_PHONE_AREA_CODE||R.PRIMARY_PHONE_NUMBER
FROM HZ_RELATIONSHIPS REL
, HZ_PARTIES P
, HZ_PARTIES O
, HZ_PARTIES R
, HZ_ORG_CONTACTS CNT
, AR_LOOKUPS ALK
, AR_LOOKUPS ALK1
, AR_LOOKUPS ALK2
, FND_TERRITORIES_TL FTT
, HZ_RELATIONSHIP_TYPES HZ
, HZ_MATCHED_PARTIES_GT FUZZYPARTY
WHERE 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 REL.DIRECTIONAL_FLAG IN ('B'
, 'F')
AND R.PARTY_ID = REL.PARTY_ID
AND R.PARTY_TYPE = 'PARTY_RELATIONSHIP'
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 R.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
AND FUZZYPARTY.PARTY_ID=O.PARTY_ID