VIEW: APPS.ASF_COMPETITORS_LOV_V

Source

View Text - Preformatted

SELECT distinct a.party_id, a.party_name, a.address1, a.address2, a.address3, a.address4, a.city, a.province, a.county, a.state, a.postal_code, a.country, a.status, null from hz_parties a where a.party_id in ( select object_id from hz_relationships b, hz_organization_profiles p where b.relationship_code='COMPETITOR_OF' and b.subject_table_name = 'HZ_PARTIES' and b.object_table_name = 'HZ_PARTIES' and b.subject_id = p.party_id and b.status = 'A' and sysdate between b.start_date and nvl(b.end_date,sysdate) and decode(p.status(+),'A',1,'I',1,NULL,1,-99) = 1 and p.internal_flag='Y')

View Text - HTML Formatted

SELECT DISTINCT A.PARTY_ID
, A.PARTY_NAME
, A.ADDRESS1
, A.ADDRESS2
, A.ADDRESS3
, A.ADDRESS4
, A.CITY
, A.PROVINCE
, A.COUNTY
, A.STATE
, A.POSTAL_CODE
, A.COUNTRY
, A.STATUS
, NULL
FROM HZ_PARTIES A
WHERE A.PARTY_ID IN ( SELECT OBJECT_ID
FROM HZ_RELATIONSHIPS B
, HZ_ORGANIZATION_PROFILES P
WHERE B.RELATIONSHIP_CODE='COMPETITOR_OF'
AND B.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND B.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND B.SUBJECT_ID = P.PARTY_ID
AND B.STATUS = 'A'
AND SYSDATE BETWEEN B.START_DATE
AND NVL(B.END_DATE
, SYSDATE)
AND DECODE(P.STATUS(+)
, 'A'
, 1
, 'I'
, 1
, NULL
, 1
, -99) = 1
AND P.INTERNAL_FLAG='Y')