VIEW: APPS.PA_CUSTOMER_CONTACT_NAMES_V

Source

View Text - Preformatted

SELECT DISTINCT substrb(party.person_last_name,1,50)||', '||substrb(party.person_first_name,1,40) ,org_cont.job_title ,acct_role.cust_account_role_id ,acct_role.cust_account_id ,role_resp.responsibility_type ,acct_role.cust_acct_site_id FROM hz_parties party ,hz_cust_account_roles acct_role ,hz_relationships rel ,hz_org_contacts org_cont ,hz_role_responsibility role_resp ,hz_cust_acct_sites acct_site WHERE acct_role.cust_account_role_id = role_resp.cust_account_role_id and acct_role.party_id = rel.party_id and acct_role.role_type = 'CONTACT' and rel.relationship_id = org_cont.party_relationship_id and party.party_id = rel.subject_id and nvl(acct_role.current_role_state, 'A') = 'A' and acct_role.cust_acct_site_id IS NULL and rel.subject_type = 'PERSON' and rel.object_table_name = 'HZ_PARTIES' and rel.subject_table_name = 'HZ_PARTIES' UNION ALL SELECT DISTINCT substrb(party.person_last_name,1,50)||', '||substrb(party.person_first_name,1,40) ,org_cont.job_title ,acct_role.cust_account_role_id ,acct_role.cust_account_id ,role_resp.responsibility_type ,acct_role.cust_acct_site_id FROM hz_parties party ,hz_cust_account_roles acct_role ,hz_relationships rel ,hz_org_contacts org_cont ,hz_role_responsibility role_resp ,hz_cust_acct_sites acct_site WHERE acct_role.cust_account_role_id = role_resp.cust_account_role_id and acct_role.party_id = rel.party_id and acct_role.role_type = 'CONTACT' and rel.relationship_id = org_cont.party_relationship_id and party.party_id = rel.subject_id and nvl(acct_role.current_role_state, 'A') = 'A' and acct_role.cust_acct_site_id = acct_site.cust_acct_site_id and rel.subject_type = 'PERSON' and rel.object_table_name = 'HZ_PARTIES' and rel.subject_table_name = 'HZ_PARTIES' ORDER BY 1

View Text - HTML Formatted

SELECT DISTINCT SUBSTRB(PARTY.PERSON_LAST_NAME
, 1
, 50)||'
, '||SUBSTRB(PARTY.PERSON_FIRST_NAME
, 1
, 40)
, ORG_CONT.JOB_TITLE
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
, ACCT_ROLE.CUST_ACCOUNT_ID
, ROLE_RESP.RESPONSIBILITY_TYPE
, ACCT_ROLE.CUST_ACCT_SITE_ID
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_ROLE_RESPONSIBILITY ROLE_RESP
, HZ_CUST_ACCT_SITES ACCT_SITE
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = ROLE_RESP.CUST_ACCOUNT_ROLE_ID
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
AND PARTY.PARTY_ID = REL.SUBJECT_ID
AND NVL(ACCT_ROLE.CURRENT_ROLE_STATE
, 'A') = 'A'
AND ACCT_ROLE.CUST_ACCT_SITE_ID IS NULL
AND REL.SUBJECT_TYPE = 'PERSON'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' UNION ALL SELECT DISTINCT SUBSTRB(PARTY.PERSON_LAST_NAME
, 1
, 50)||'
, '||SUBSTRB(PARTY.PERSON_FIRST_NAME
, 1
, 40)
, ORG_CONT.JOB_TITLE
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
, ACCT_ROLE.CUST_ACCOUNT_ID
, ROLE_RESP.RESPONSIBILITY_TYPE
, ACCT_ROLE.CUST_ACCT_SITE_ID
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_ROLE_RESPONSIBILITY ROLE_RESP
, HZ_CUST_ACCT_SITES ACCT_SITE
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = ROLE_RESP.CUST_ACCOUNT_ROLE_ID
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
AND PARTY.PARTY_ID = REL.SUBJECT_ID
AND NVL(ACCT_ROLE.CURRENT_ROLE_STATE
, 'A') = 'A'
AND ACCT_ROLE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND REL.SUBJECT_TYPE = 'PERSON'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' ORDER BY 1