VIEW: APPS.GMS_CONTACTS_V

Source

View Text - Preformatted

SELECT AWD.AWARD_ID , CONT.CUST_ACCOUNT_ROLE_ID CONTACT_ID , L.MEANING , SUBSTRB(PARTY.PERSON_FIRST_NAME,1,40) FIRST_NAME , SUBSTRB(PARTY.PERSON_LAST_NAME,1,50) LAST_NAME , CONT.STATUS , ORG_CONT.JOB_TITLE , CONT_PTS.EMAIL_ADDRESS , CONT.ORIG_SYSTEM_REFERENCE , SITE.CUST_ACCT_SITE_ID ADDRESS_ID , CON.CUSTOMER_ID , CON.USAGE_CODE ,CON.PRIMARY_FLAG ,CON.LAST_UPDATE_DATE ,CON.LAST_UPDATED_BY ,CON.CREATION_DATE ,CON.CREATED_BY ,CON.LAST_UPDATE_LOGIN ,CON.ROWID ,AWD.ORG_ID FROM GMS_AWARDS AWD, GMS_AWARDS_CONTACTS CON, HZ_CUST_ACCOUNT_ROLES CONT, HZ_PARTIES PARTY, HZ_ORG_CONTACTS ORG_CONT, HZ_CONTACT_POINTS CONT_PTS, HZ_CUST_ACCT_SITES_ALL SITE, HZ_RELATIONSHIPS REL, AR_LOOKUPS L WHERE CONT.ROLE_TYPE = L.LOOKUP_CODE(+) AND L.LOOKUP_TYPE(+) = 'CONTACT_TITLE' AND AWD.FUNDING_SOURCE_ID = CONT.CUST_ACCOUNT_ID AND CON.AWARD_ID = AWD.AWARD_ID AND CON.CONTACT_ID = CONT.CUST_ACCOUNT_ROLE_ID AND SITE.CUST_ACCT_SITE_ID(+) = CONT.CUST_ACCT_SITE_ID AND CONT.PARTY_ID = REL.PARTY_ID AND PARTY.PARTY_TYPE = 'PERSON' AND REL.SUBJECT_ID = PARTY.PARTY_ID AND CONT_PTS.OWNER_TABLE_ID(+) = REL.PARTY_ID AND CONT_PTS.CONTACT_POINT_TYPE(+) = 'EMAIL' AND CONT_PTS.STATUS(+) = 'A' AND CONT_PTS.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND CONT_PTS.PRIMARY_FLAG(+) = 'Y' AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.RELATIONSHIP_CODE = 'CONTACT_OF' AND REL.DIRECTIONAL_FLAG='F' AND CONT.ROLE_TYPE ='CONTACT' AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID

View Text - HTML Formatted

SELECT AWD.AWARD_ID
, CONT.CUST_ACCOUNT_ROLE_ID CONTACT_ID
, L.MEANING
, SUBSTRB(PARTY.PERSON_FIRST_NAME
, 1
, 40) FIRST_NAME
, SUBSTRB(PARTY.PERSON_LAST_NAME
, 1
, 50) LAST_NAME
, CONT.STATUS
, ORG_CONT.JOB_TITLE
, CONT_PTS.EMAIL_ADDRESS
, CONT.ORIG_SYSTEM_REFERENCE
, SITE.CUST_ACCT_SITE_ID ADDRESS_ID
, CON.CUSTOMER_ID
, CON.USAGE_CODE
, CON.PRIMARY_FLAG
, CON.LAST_UPDATE_DATE
, CON.LAST_UPDATED_BY
, CON.CREATION_DATE
, CON.CREATED_BY
, CON.LAST_UPDATE_LOGIN
, CON.ROWID
, AWD.ORG_ID
FROM GMS_AWARDS AWD
, GMS_AWARDS_CONTACTS CON
, HZ_CUST_ACCOUNT_ROLES CONT
, HZ_PARTIES PARTY
, HZ_ORG_CONTACTS ORG_CONT
, HZ_CONTACT_POINTS CONT_PTS
, HZ_CUST_ACCT_SITES_ALL SITE
, HZ_RELATIONSHIPS REL
, AR_LOOKUPS L
WHERE CONT.ROLE_TYPE = L.LOOKUP_CODE(+)
AND L.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND AWD.FUNDING_SOURCE_ID = CONT.CUST_ACCOUNT_ID
AND CON.AWARD_ID = AWD.AWARD_ID
AND CON.CONTACT_ID = CONT.CUST_ACCOUNT_ROLE_ID
AND SITE.CUST_ACCT_SITE_ID(+) = CONT.CUST_ACCT_SITE_ID
AND CONT.PARTY_ID = REL.PARTY_ID
AND PARTY.PARTY_TYPE = 'PERSON'
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND CONT_PTS.OWNER_TABLE_ID(+) = REL.PARTY_ID
AND CONT_PTS.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND CONT_PTS.STATUS(+) = 'A'
AND CONT_PTS.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONT_PTS.PRIMARY_FLAG(+) = 'Y'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.RELATIONSHIP_CODE = 'CONTACT_OF'
AND REL.DIRECTIONAL_FLAG='F'
AND CONT.ROLE_TYPE ='CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID