View: ASF_PARTNERS_LOV_V

View Text

SELECT A.SOURCE_ID
, B.PARTY_NAME
, B.PARTY_TYPE
, H.ADDRESS1
, H.ADDRESS2
, H.ADDRESS3
, H.ADDRESS4
, H.CITY
, H.PROVINCE
, H.COUNTY
, H.STATE
, H.POSTAL_CODE
, H.COUNTRY
, B.STATUS
, A.RESOURCE_ID
, A.ADDRESS_ID
, A.CATEGORY
, A.START_DATE_ACTIVE
, A.END_DATE_ACTIVE
FROM JTF_RS_RESOURCE_EXTNS A
, JTF_RS_ROLE_RELATIONS RL
, JTF_RS_ROLES_B R
, HZ_PARTIES B
, (SELECT D.ADDRESS1
, D.ADDRESS2
, D.ADDRESS3
, D.ADDRESS4
, D.CITY
, D.PROVINCE
, D.COUNTY
, D.STATE
, D.POSTAL_CODE
, D.COUNTRY
, C.PARTY_SITE_ID
FROM HZ_PARTY_SITES C
, HZ_LOCATIONS D
WHERE C.LOCATION_ID = D.LOCATION_ID
AND C.STATUS IN ('A'
, 'I') ) H
WHERE A.SOURCE_ID=B.PARTY_ID
AND A.RESOURCE_ID = RL.ROLE_RESOURCE_ID
AND R.ROLE_ID = RL.ROLE_ID
AND RL.ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL'
AND R.ROLE_TYPE_CODE IN ('SALES'
, 'TELESALES'
, 'FIELDSALES'
, 'PRM')
AND A.CATEGORY IN ('PARTNER'
, 'PARTY')
AND A.ADDRESS_ID=H.PARTY_SITE_ID(+)
AND B.STATUS IN ('A'
, 'I')

Columns