VIEW: APPS.ASF_CNT_PERSON_LOV_V

Source

View Text - Preformatted

SELECT distinct a.party_id, b.object_id, a.party_name, c.meaning, hz.party_name, a.address1, a.address2, a.address3, a.address4, a.city, a.province, a.state, a.postal_code, a.county, a.country, a.status, a.person_first_name, a.person_middle_name, a.person_last_name, a.person_pre_name_adjunct, relationship_code, relationship_id, b.party_id from hz_parties a,hz_relationships b, ar_lookups c, hz_parties hz WHERE a.party_id = b.subject_id and b.object_id = hz.party_id and a.party_type='PERSON' and b.relationship_code=c.lookup_code and c.lookup_type = 'PARTY_RELATIONS_TYPE' and b.subject_table_name = 'HZ_PARTIES' and b.object_table_name = 'HZ_PARTIES' and a.status in ('A','I') and b.status = 'A' and sysdate between b.start_date and nvl(b.end_date,sysdate) and hz.status in ('A','I')

View Text - HTML Formatted

SELECT DISTINCT A.PARTY_ID
, B.OBJECT_ID
, A.PARTY_NAME
, C.MEANING
, HZ.PARTY_NAME
, A.ADDRESS1
, A.ADDRESS2
, A.ADDRESS3
, A.ADDRESS4
, A.CITY
, A.PROVINCE
, A.STATE
, A.POSTAL_CODE
, A.COUNTY
, A.COUNTRY
, A.STATUS
, A.PERSON_FIRST_NAME
, A.PERSON_MIDDLE_NAME
, A.PERSON_LAST_NAME
, A.PERSON_PRE_NAME_ADJUNCT
, RELATIONSHIP_CODE
, RELATIONSHIP_ID
, B.PARTY_ID
FROM HZ_PARTIES A
, HZ_RELATIONSHIPS B
, AR_LOOKUPS C
, HZ_PARTIES HZ
WHERE A.PARTY_ID = B.SUBJECT_ID
AND B.OBJECT_ID = HZ.PARTY_ID
AND A.PARTY_TYPE='PERSON'
AND B.RELATIONSHIP_CODE=C.LOOKUP_CODE
AND C.LOOKUP_TYPE = 'PARTY_RELATIONS_TYPE'
AND B.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND B.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND A.STATUS IN ('A'
, 'I')
AND B.STATUS = 'A'
AND SYSDATE BETWEEN B.START_DATE
AND NVL(B.END_DATE
, SYSDATE)
AND HZ.STATUS IN ('A'
, 'I')