VIEW: APPS.OKX_CUST_CONTACTS_V

Source

View Text - Preformatted

SELECT CAR.CUST_ACCOUNT_ROLE_ID ID1, '#' ID2, R.OBJECT_ID PARTY_ID, '#' PARTY_ID2, SUBSTR(OC.JOB_TITLE,1,40) DESCRIPTION, SUBSTRB(P.PERSON_LAST_NAME,1,50) || ', ' || SUBSTRB(P.PERSON_FIRST_NAME,1,40) NAME, CAR.CUST_ACCOUNT_ID CUST_ACCT_ID, CAR.ROLE_TYPE, CAR.CUST_ACCT_SITE_ID, CAR.STATUS STATUS , P2.EMAIL_ADDRESS EMAIL_ADDRESS, P.ORIG_SYSTEM_REFERENCE CONTACT_REFERENCE_ID, OC.JOB_TITLE JOB_TITLE, OC.DEPARTMENT DEPARTMENT, ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL,p2.ADDRESS1,p2.ADDRESS2,p2.ADDRESS3, p2.ADDRESS4,p2.CITY,p2.COUNTY,p2.STATE,p2.PROVINCE,p2.POSTAL_CODE,NULL,p2.COUNTRY,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'N','N',300,1,1) ADDRESS , P.PERSON_PRE_NAME_ADJUNCT TITLE, P.PARTY_ID PERSON_PARTY_ID FROM HZ_PARTIES P2,HZ_CUST_ACCOUNT_ROLES CAR,HZ_PARTIES P,HZ_ORG_CONTACTS OC,HZ_RELATIONSHIPS R WHERE CAR.ROLE_TYPE = 'CONTACT' AND R.PARTY_ID = CAR.PARTY_ID AND R.CONTENT_SOURCE_TYPE = 'USER_ENTERED' AND OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID AND P.PARTY_ID = R.SUBJECT_ID AND EXISTS (SELECT 'X' FROM hz_cust_acct_sites cas WHERE cas.cust_account_id = car.cust_account_id ) AND R.PARTY_ID=P2.PARTY_ID AND R.DIRECTIONAL_FLAG = 'F'

View Text - HTML Formatted

SELECT CAR.CUST_ACCOUNT_ROLE_ID ID1
, '#' ID2
, R.OBJECT_ID PARTY_ID
, '#' PARTY_ID2
, SUBSTR(OC.JOB_TITLE
, 1
, 40) DESCRIPTION
, SUBSTRB(P.PERSON_LAST_NAME
, 1
, 50) || '
, ' || SUBSTRB(P.PERSON_FIRST_NAME
, 1
, 40) NAME
, CAR.CUST_ACCOUNT_ID CUST_ACCT_ID
, CAR.ROLE_TYPE
, CAR.CUST_ACCT_SITE_ID
, CAR.STATUS STATUS
, P2.EMAIL_ADDRESS EMAIL_ADDRESS
, P.ORIG_SYSTEM_REFERENCE CONTACT_REFERENCE_ID
, OC.JOB_TITLE JOB_TITLE
, OC.DEPARTMENT DEPARTMENT
, ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL
, P2.ADDRESS1
, P2.ADDRESS2
, P2.ADDRESS3
, P2.ADDRESS4
, P2.CITY
, P2.COUNTY
, P2.STATE
, P2.PROVINCE
, P2.POSTAL_CODE
, NULL
, P2.COUNTRY
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'N'
, 'N'
, 300
, 1
, 1) ADDRESS
, P.PERSON_PRE_NAME_ADJUNCT TITLE
, P.PARTY_ID PERSON_PARTY_ID
FROM HZ_PARTIES P2
, HZ_CUST_ACCOUNT_ROLES CAR
, HZ_PARTIES P
, HZ_ORG_CONTACTS OC
, HZ_RELATIONSHIPS R
WHERE CAR.ROLE_TYPE = 'CONTACT'
AND R.PARTY_ID = CAR.PARTY_ID
AND R.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
AND OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
AND P.PARTY_ID = R.SUBJECT_ID
AND EXISTS (SELECT 'X'
FROM HZ_CUST_ACCT_SITES CAS
WHERE CAS.CUST_ACCOUNT_ID = CAR.CUST_ACCOUNT_ID )
AND R.PARTY_ID=P2.PARTY_ID
AND R.DIRECTIONAL_FLAG = 'F'