VIEW: APPS.IEX_LM_CKEY_PARTY_V

Source

View Text - Preformatted

SELECT party.party_id PARTY_ID, 0 relationship_id, null relationship_type, 0 LOCATION_ID, party.party_name PARTY_NAME, alk1.meaning party_type, alk1.lookup_code party_type_code, 0 PERSON_ID, null job_title, party.address1 ADDRESS1, party.address2 ADDRESS2, party.address3 ADDRESS3, party.address4 ADDRESS4, party.city CITY, party.state STATE, party.province PROVINCE, party.postal_code POSTAL_CODE, ftt.TERRITORY_SHORT_NAME Territory_short_name, cpt.PHONE_COUNTRY_CODE COUNTRY_CODE, cpt.PHONE_AREA_CODE AREA_CODE, cpt.PHONE_NUMBER PHONE_NUMBER, cpt.PHONE_EXTENSION EXTENSION, cpt.EMAIL_ADDRESS EMAIL_ADDRESS, party.JGZZ_FISCAL_CODE TAX_ID, party.ORIG_SYSTEM_REFERENCE PARTY_EXTERNAL_REF_NUM, cpt.primary_flag CP_PRIMARY_FLAG, alk.meaning STATUS, party.STATUS status_code FROM HZ_PARTIES PARTY, HZ_CONTACT_POINTS CPT, AR_LOOKUPS ALK1, AR_LOOKUPS ALK, FND_TERRITORIES_TL FTT WHERE PARTY_ID = CPT.OWNER_TABLE_ID AND CPT.OWNER_TABLE_NAME = 'HZ_PARTIES' AND CPT.STATUS = 'A' AND CPT.CONTACT_POINT_TYPE in ('PHONE','EMAIL') AND PARTY.PARTY_TYPE = 'PERSON' AND ALK1.LOOKUP_TYPE ='PARTY_TYPE' AND alk1.lookup_code = PARTY.PARTY_TYPE AND ALK1.ENABLED_FLAG = 'Y' AND ALK.LOOKUP_TYPE ='CODE_STATUS' AND ALK.LOOKUP_CODE = PARTY.STATUS AND ALK.ENABLED_FLAG = 'Y' AND FTT.LANGUAGE(+) = userenv('LANG') AND FTT.SOURCE_LANG(+) = userenv('LANG') AND PARTY.COUNTRY = FTT.TERRITORY_CODE(+) UNION select hz.party_id PARTY_ID, 0 relationship_id, null relationship_type, 0 location_id, hz.party_name PARTY_NAME, alk.meaning party_type, alk.lookup_code party_type_code, 0 PERSON_ID, null job_title, hz.address1 address1,hz.address2 address2, hz.address3 address3, hz.address4 address4, hz.city CITY, hz.state STATE, hz.province PROVINCE, hz.postal_code POSTAL_CODE, fnd.territory_short_name Territory_short_name, hcp.phone_country_code COUNTRY_CODE, hcp.phone_area_code AREA_CODE, hcp.phone_number PHONE_NUMBER, hcp.phone_extension EXTENSION, hcp.email_address EMAIL_ADDRESS, hz.jgzz_fiscal_code TAX_ID, hz.orig_system_reference PARTY_EXTERNAL_REF_NUM, hcp.primary_flag CP_PRIMARY_FLAG, alk1.meaning status, hz.status status_code from hz_parties hz, hz_contact_points hcp, fnd_territories_tl fnd, ar_lookups alk, ar_lookups alk1 where PARTY_ID = hcp.owner_table_id and hcp.owner_table_name = 'HZ_PARTIES' and hcp.contact_point_type in ('PHONE','EMAIL') and hcp.status = 'A' and hz.party_type='ORGANIZATION' and hz.COUNTRY = FND.TERRITORY_CODE(+) and FND.LANGUAGE(+) = USERENV('LANG') AND alk.lookup_type ='PARTY_TYPE' AND alk1.lookup_code = hz.party_type AND ALK.ENABLED_FLAG = 'Y' AND ALK1.LOOKUP_TYPE ='CODE_STATUS' AND ALK1.LOOKUP_CODE = hz.STATUS AND ALK1.ENABLED_FLAG = 'Y' UNION SELECT O.PARTY_ID PARTY_ID, R.PARTY_ID RELATIONSHIP_ID, ALK.MEANING relationship_type, 0 location_id, R.PARTY_NAME PARTY_NAME, ALK2.meaning PARTY_TYPE, ALK2.lookup_code PARTY_TYPE_CODE, P.PARTY_ID PERSON_ID, cnt.job_title job_title, P.ADDRESS1 ADDRESS1, P.ADDRESS2 ADDRESS2, P.ADDRESS3 ADDRESS3, P.ADDRESS4 ADDRESS4, P.CITY CITY, P.STATE STATE, P.PROVINCE PROVINCE, P.POSTAL_CODE POSTAL_CODE, FTT.TERRITORY_SHORT_NAME Territory_short_name, CPT.PHONE_COUNTRY_CODE COUNTRY_CODE, CPT.PHONE_AREA_CODE AREA_CODE, CPT.PHONE_NUMBER PHONE_NUMBER, CPT.PHONE_EXTENSION EXTENSION, CPT.EMAIL_ADDRESS EMAIL_ADDRESS, null TAX_ID, r.orig_system_reference PARTY_EXTERNAL_REF_NUM, CPT.primary_flag CP_PRIMARY_FLAG, ALK3.meaning status, O.STATUS status_code FROM HZ_RELATIONSHIPS REL , HZ_PARTIES P , HZ_PARTIES O , HZ_PARTIES R , HZ_CONTACT_POINTS CPT , HZ_ORG_CONTACTS CNT , AR_LOOKUPS ALK , AR_LOOKUPS ALK1 , AR_LOOKUPS ALK2 , AR_LOOKUPS ALK3 , 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.STATUS = 'A' 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 in ('PHONE','EMAIL') AND CPT.STATUS = 'A' 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 ALK2.LOOKUP_TYPE ='PARTY_TYPE' AND ALK2.LOOKUP_CODE = R.PARTY_TYPE AND ALK2.ENABLED_FLAG = 'Y' AND ALK3.LOOKUP_TYPE ='CODE_STATUS' AND ALK3.LOOKUP_CODE = O.STATUS AND ALK3.ENABLED_FLAG = 'Y' AND ALK1.LOOKUP_TYPE(+) = 'RESPONSIBILITY' AND ALK1.ENABLED_FLAG(+) = 'Y' AND ALK1.LOOKUP_CODE(+) = CNT.JOB_TITLE_CODE

View Text - HTML Formatted

SELECT PARTY.PARTY_ID PARTY_ID
, 0 RELATIONSHIP_ID
, NULL RELATIONSHIP_TYPE
, 0 LOCATION_ID
, PARTY.PARTY_NAME PARTY_NAME
, ALK1.MEANING PARTY_TYPE
, ALK1.LOOKUP_CODE PARTY_TYPE_CODE
, 0 PERSON_ID
, NULL JOB_TITLE
, PARTY.ADDRESS1 ADDRESS1
, PARTY.ADDRESS2 ADDRESS2
, PARTY.ADDRESS3 ADDRESS3
, PARTY.ADDRESS4 ADDRESS4
, PARTY.CITY CITY
, PARTY.STATE STATE
, PARTY.PROVINCE PROVINCE
, PARTY.POSTAL_CODE POSTAL_CODE
, FTT.TERRITORY_SHORT_NAME TERRITORY_SHORT_NAME
, CPT.PHONE_COUNTRY_CODE COUNTRY_CODE
, CPT.PHONE_AREA_CODE AREA_CODE
, CPT.PHONE_NUMBER PHONE_NUMBER
, CPT.PHONE_EXTENSION EXTENSION
, CPT.EMAIL_ADDRESS EMAIL_ADDRESS
, PARTY.JGZZ_FISCAL_CODE TAX_ID
, PARTY.ORIG_SYSTEM_REFERENCE PARTY_EXTERNAL_REF_NUM
, CPT.PRIMARY_FLAG CP_PRIMARY_FLAG
, ALK.MEANING STATUS
, PARTY.STATUS STATUS_CODE
FROM HZ_PARTIES PARTY
, HZ_CONTACT_POINTS CPT
, AR_LOOKUPS ALK1
, AR_LOOKUPS ALK
, FND_TERRITORIES_TL FTT
WHERE PARTY_ID = CPT.OWNER_TABLE_ID
AND CPT.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND CPT.STATUS = 'A'
AND CPT.CONTACT_POINT_TYPE IN ('PHONE'
, 'EMAIL')
AND PARTY.PARTY_TYPE = 'PERSON'
AND ALK1.LOOKUP_TYPE ='PARTY_TYPE'
AND ALK1.LOOKUP_CODE = PARTY.PARTY_TYPE
AND ALK1.ENABLED_FLAG = 'Y'
AND ALK.LOOKUP_TYPE ='CODE_STATUS'
AND ALK.LOOKUP_CODE = PARTY.STATUS
AND ALK.ENABLED_FLAG = 'Y'
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND FTT.SOURCE_LANG(+) = USERENV('LANG')
AND PARTY.COUNTRY = FTT.TERRITORY_CODE(+) UNION SELECT HZ.PARTY_ID PARTY_ID
, 0 RELATIONSHIP_ID
, NULL RELATIONSHIP_TYPE
, 0 LOCATION_ID
, HZ.PARTY_NAME PARTY_NAME
, ALK.MEANING PARTY_TYPE
, ALK.LOOKUP_CODE PARTY_TYPE_CODE
, 0 PERSON_ID
, NULL JOB_TITLE
, HZ.ADDRESS1 ADDRESS1
, HZ.ADDRESS2 ADDRESS2
, HZ.ADDRESS3 ADDRESS3
, HZ.ADDRESS4 ADDRESS4
, HZ.CITY CITY
, HZ.STATE STATE
, HZ.PROVINCE PROVINCE
, HZ.POSTAL_CODE POSTAL_CODE
, FND.TERRITORY_SHORT_NAME TERRITORY_SHORT_NAME
, HCP.PHONE_COUNTRY_CODE COUNTRY_CODE
, HCP.PHONE_AREA_CODE AREA_CODE
, HCP.PHONE_NUMBER PHONE_NUMBER
, HCP.PHONE_EXTENSION EXTENSION
, HCP.EMAIL_ADDRESS EMAIL_ADDRESS
, HZ.JGZZ_FISCAL_CODE TAX_ID
, HZ.ORIG_SYSTEM_REFERENCE PARTY_EXTERNAL_REF_NUM
, HCP.PRIMARY_FLAG CP_PRIMARY_FLAG
, ALK1.MEANING STATUS
, HZ.STATUS STATUS_CODE
FROM HZ_PARTIES HZ
, HZ_CONTACT_POINTS HCP
, FND_TERRITORIES_TL FND
, AR_LOOKUPS ALK
, AR_LOOKUPS ALK1
WHERE PARTY_ID = HCP.OWNER_TABLE_ID
AND HCP.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND HCP.CONTACT_POINT_TYPE IN ('PHONE'
, 'EMAIL')
AND HCP.STATUS = 'A'
AND HZ.PARTY_TYPE='ORGANIZATION'
AND HZ.COUNTRY = FND.TERRITORY_CODE(+)
AND FND.LANGUAGE(+) = USERENV('LANG')
AND ALK.LOOKUP_TYPE ='PARTY_TYPE'
AND ALK1.LOOKUP_CODE = HZ.PARTY_TYPE
AND ALK.ENABLED_FLAG = 'Y'
AND ALK1.LOOKUP_TYPE ='CODE_STATUS'
AND ALK1.LOOKUP_CODE = HZ.STATUS
AND ALK1.ENABLED_FLAG = 'Y' UNION SELECT O.PARTY_ID PARTY_ID
, R.PARTY_ID RELATIONSHIP_ID
, ALK.MEANING RELATIONSHIP_TYPE
, 0 LOCATION_ID
, R.PARTY_NAME PARTY_NAME
, ALK2.MEANING PARTY_TYPE
, ALK2.LOOKUP_CODE PARTY_TYPE_CODE
, P.PARTY_ID PERSON_ID
, CNT.JOB_TITLE JOB_TITLE
, P.ADDRESS1 ADDRESS1
, P.ADDRESS2 ADDRESS2
, P.ADDRESS3 ADDRESS3
, P.ADDRESS4 ADDRESS4
, P.CITY CITY
, P.STATE STATE
, P.PROVINCE PROVINCE
, P.POSTAL_CODE POSTAL_CODE
, FTT.TERRITORY_SHORT_NAME TERRITORY_SHORT_NAME
, CPT.PHONE_COUNTRY_CODE COUNTRY_CODE
, CPT.PHONE_AREA_CODE AREA_CODE
, CPT.PHONE_NUMBER PHONE_NUMBER
, CPT.PHONE_EXTENSION EXTENSION
, CPT.EMAIL_ADDRESS EMAIL_ADDRESS
, NULL TAX_ID
, R.ORIG_SYSTEM_REFERENCE PARTY_EXTERNAL_REF_NUM
, CPT.PRIMARY_FLAG CP_PRIMARY_FLAG
, ALK3.MEANING STATUS
, O.STATUS STATUS_CODE
FROM HZ_RELATIONSHIPS REL
, HZ_PARTIES P
, HZ_PARTIES O
, HZ_PARTIES R
, HZ_CONTACT_POINTS CPT
, HZ_ORG_CONTACTS CNT
, AR_LOOKUPS ALK
, AR_LOOKUPS ALK1
, AR_LOOKUPS ALK2
, AR_LOOKUPS ALK3
, 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.STATUS = 'A'
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 IN ('PHONE'
, 'EMAIL')
AND CPT.STATUS = 'A'
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 ALK2.LOOKUP_TYPE ='PARTY_TYPE'
AND ALK2.LOOKUP_CODE = R.PARTY_TYPE
AND ALK2.ENABLED_FLAG = 'Y'
AND ALK3.LOOKUP_TYPE ='CODE_STATUS'
AND ALK3.LOOKUP_CODE = O.STATUS
AND ALK3.ENABLED_FLAG = 'Y'
AND ALK1.LOOKUP_TYPE(+) = 'RESPONSIBILITY'
AND ALK1.ENABLED_FLAG(+) = 'Y'
AND ALK1.LOOKUP_CODE(+) = CNT.JOB_TITLE_CODE