VIEW: APPS.AR_CUSTOMERS_ASSIGNED_V

Source

View Text - Preformatted

SELECT DISTINCT party.party_id, accts.cust_account_id FROM hz_parties party, hz_cust_accounts accts WHERE party.party_id = accts.party_id AND accts.status = 'A' AND Party.party_type = 'PERSON' AND Party.status = 'A' UNION SELECT DISTINCT Rel.subject_id as party_id, Role.cust_account_id FROM hz_cust_account_roles Role, hz_role_responsibility Resp, hz_relationships Rel WHERE Role.current_role_state = 'A' AND Role.role_type = 'CONTACT' AND Role.cust_acct_site_id IS NULL AND (Role.end_date is null or Role.end_date > sysdate) AND Resp.cust_account_role_id = Role.cust_account_role_id AND Resp.responsibility_type = 'SELF_SERVICE_USER' AND Rel.party_id = Role.party_id AND Rel.subject_type = 'PERSON' AND (Rel.end_date is null OR Rel.end_date > sysdate) AND Rel.status = 'A' AND Rel.subject_table_name = 'HZ_PARTIES'

View Text - HTML Formatted

SELECT DISTINCT PARTY.PARTY_ID
, ACCTS.CUST_ACCOUNT_ID
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS ACCTS
WHERE PARTY.PARTY_ID = ACCTS.PARTY_ID
AND ACCTS.STATUS = 'A'
AND PARTY.PARTY_TYPE = 'PERSON'
AND PARTY.STATUS = 'A' UNION SELECT DISTINCT REL.SUBJECT_ID AS PARTY_ID
, ROLE.CUST_ACCOUNT_ID
FROM HZ_CUST_ACCOUNT_ROLES ROLE
, HZ_ROLE_RESPONSIBILITY RESP
, HZ_RELATIONSHIPS REL
WHERE ROLE.CURRENT_ROLE_STATE = 'A'
AND ROLE.ROLE_TYPE = 'CONTACT'
AND ROLE.CUST_ACCT_SITE_ID IS NULL
AND (ROLE.END_DATE IS NULL OR ROLE.END_DATE > SYSDATE)
AND RESP.CUST_ACCOUNT_ROLE_ID = ROLE.CUST_ACCOUNT_ROLE_ID
AND RESP.RESPONSIBILITY_TYPE = 'SELF_SERVICE_USER'
AND REL.PARTY_ID = ROLE.PARTY_ID
AND REL.SUBJECT_TYPE = 'PERSON'
AND (REL.END_DATE IS NULL OR REL.END_DATE > SYSDATE)
AND REL.STATUS = 'A'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'