VIEW: APPS.IEX_LM_REL_QUICK_V

Source

View Text - Preformatted

SELECT O.PARTY_NAME PARTY_NAME, O.PARTY_ID PARTY_ID, R.PARTY_ID REL_PARTY_ID, to_char(null) ACCOUNT_NUMBER, to_number(null) CUST_ACCOUNT_ID, REL.STATUS STATUS, O.SIC_CODE SIC_CODE, O.FISCAL_YEAREND_MONTH FISCAL_YEAREND_MONTH, O.CURR_FY_POTENTIAL_REVENUE CURRENT_FY_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, R.ADDRESS1||R.ADDRESS2||R.ADDRESS3||R.ADDRESS4 ADDRESS, R.ADDRESS1 ADDRESS1, R.ADDRESS2 ADDRESS2, R.ADDRESS3 ADDRESS3, R.ADDRESS4 ADDRESS4, R.CITY CITY, R.STATE STATE, R.PROVINCE PROVINCE, R.COUNTY COUNTY, R.POSTAL_CODE POSTAL_CODE, R.COUNTRY COUNTRY, FTT.TERRITORY_SHORT_NAME COUNTRY_NAME, R.EMAIL_ADDRESS EMAIL_ADDRESS, CNT.JOB_TITLE JOB_TITLE, ALK1.MEANING JOB_TITLE_TYPE, CNT.DEPARTMENT_CODE DEPARTMENT_CODE, CNT.DEPARTMENT DEPARTMENT, ALK.MEANING RELATIONSHIP_TYPE, REL.RELATIONSHIP_ID RELATIONSHIP_ID FROM HZ_RELATIONSHIPS REL , HZ_PARTIES P , HZ_PARTIES O , HZ_PARTIES R , HZ_ORG_CONTACTS CNT , AR_LOOKUPS ALK , AR_LOOKUPS ALK1 , FND_TERRITORIES_TL FTT WHERE 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 REL.DIRECTIONAL_FLAG IN ('B','F') AND R.PARTY_ID = REL.PARTY_ID AND R.PARTY_TYPE = 'PARTY_RELATIONSHIP' AND REL.RELATIONSHIP_CODE = ALK.LOOKUP_CODE AND ALK.LOOKUP_TYPE = 'PARTY_RELATIONS_TYPE' 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

View Text - HTML Formatted

SELECT O.PARTY_NAME PARTY_NAME
, O.PARTY_ID PARTY_ID
, R.PARTY_ID REL_PARTY_ID
, TO_CHAR(NULL) ACCOUNT_NUMBER
, TO_NUMBER(NULL) CUST_ACCOUNT_ID
, REL.STATUS STATUS
, O.SIC_CODE SIC_CODE
, O.FISCAL_YEAREND_MONTH FISCAL_YEAREND_MONTH
, O.CURR_FY_POTENTIAL_REVENUE CURRENT_FY_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
, R.ADDRESS1||R.ADDRESS2||R.ADDRESS3||R.ADDRESS4 ADDRESS
, R.ADDRESS1 ADDRESS1
, R.ADDRESS2 ADDRESS2
, R.ADDRESS3 ADDRESS3
, R.ADDRESS4 ADDRESS4
, R.CITY CITY
, R.STATE STATE
, R.PROVINCE PROVINCE
, R.COUNTY COUNTY
, R.POSTAL_CODE POSTAL_CODE
, R.COUNTRY COUNTRY
, FTT.TERRITORY_SHORT_NAME COUNTRY_NAME
, R.EMAIL_ADDRESS EMAIL_ADDRESS
, CNT.JOB_TITLE JOB_TITLE
, ALK1.MEANING JOB_TITLE_TYPE
, CNT.DEPARTMENT_CODE DEPARTMENT_CODE
, CNT.DEPARTMENT DEPARTMENT
, ALK.MEANING RELATIONSHIP_TYPE
, REL.RELATIONSHIP_ID RELATIONSHIP_ID
FROM HZ_RELATIONSHIPS REL
, HZ_PARTIES P
, HZ_PARTIES O
, HZ_PARTIES R
, HZ_ORG_CONTACTS CNT
, AR_LOOKUPS ALK
, AR_LOOKUPS ALK1
, FND_TERRITORIES_TL FTT
WHERE 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 REL.DIRECTIONAL_FLAG IN ('B'
, 'F')
AND R.PARTY_ID = REL.PARTY_ID
AND R.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND REL.RELATIONSHIP_CODE = ALK.LOOKUP_CODE
AND ALK.LOOKUP_TYPE = 'PARTY_RELATIONS_TYPE'
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