VIEW: APPS.GMS_LOC_CONTACTS_V

Source

View Text - Preformatted

SELECT awd.award_id ,cont.cust_account_role_id ,l.meaning , substrb(party.person_first_name,1,40) , substrb(party.person_last_name,1,50) , cont.status , org_cont.job_title , org_cont.mail_stop , cont.orig_system_reference , site.cust_acct_site_id ,con.customer_id ,con.usage_code ,l1.meaning ,con.primary_flag ,con.last_update_date ,con.last_updated_by ,con.creation_date ,con.created_by ,con.last_update_login ,con.rowid FROM gms_awards awd ,gms_awards_contacts con ,hz_cust_account_roles cont ,hz_parties party ,hz_org_contacts org_cont ,hz_cust_acct_sites_all site ,hz_relationships rel ,ar_lookups l ,ar_lookups l1 WHERE cont.role_type = l.lookup_code(+) AND l.lookup_type(+) = 'CONTACT_TITLE' AND con.award_id = awd.award_id AND l1.lookup_type(+) = 'SITE_USE_CODE' AND l1.lookup_code(+) = con.usage_code AND con.contact_id = CONT.cust_account_role_id AND awd.bill_to_customer_id = CONT.cust_account_id AND CONT.status = 'A' AND CONT.party_id = rel.party_id AND rel.subject_id = party.party_id AND party.party_type = 'PERSON' AND rel.subject_table_name = 'HZ_PARTIES' AND rel.object_table_name = 'HZ_PARTIES' AND CONT.role_type ='CONTACT' AND org_cont.party_relationship_id = rel.relationship_id AND SITE.cust_acct_site_id = CONT.cust_acct_site_id

View Text - HTML Formatted

SELECT AWD.AWARD_ID
, CONT.CUST_ACCOUNT_ROLE_ID
, L.MEANING
, SUBSTRB(PARTY.PERSON_FIRST_NAME
, 1
, 40)
, SUBSTRB(PARTY.PERSON_LAST_NAME
, 1
, 50)
, CONT.STATUS
, ORG_CONT.JOB_TITLE
, ORG_CONT.MAIL_STOP
, CONT.ORIG_SYSTEM_REFERENCE
, SITE.CUST_ACCT_SITE_ID
, CON.CUSTOMER_ID
, CON.USAGE_CODE
, L1.MEANING
, CON.PRIMARY_FLAG
, CON.LAST_UPDATE_DATE
, CON.LAST_UPDATED_BY
, CON.CREATION_DATE
, CON.CREATED_BY
, CON.LAST_UPDATE_LOGIN
, CON.ROWID
FROM GMS_AWARDS AWD
, GMS_AWARDS_CONTACTS CON
, HZ_CUST_ACCOUNT_ROLES CONT
, HZ_PARTIES PARTY
, HZ_ORG_CONTACTS ORG_CONT
, HZ_CUST_ACCT_SITES_ALL SITE
, HZ_RELATIONSHIPS REL
, AR_LOOKUPS L
, AR_LOOKUPS L1
WHERE CONT.ROLE_TYPE = L.LOOKUP_CODE(+)
AND L.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND CON.AWARD_ID = AWD.AWARD_ID
AND L1.LOOKUP_TYPE(+) = 'SITE_USE_CODE'
AND L1.LOOKUP_CODE(+) = CON.USAGE_CODE
AND CON.CONTACT_ID = CONT.CUST_ACCOUNT_ROLE_ID
AND AWD.BILL_TO_CUSTOMER_ID = CONT.CUST_ACCOUNT_ID
AND CONT.STATUS = 'A'
AND CONT.PARTY_ID = REL.PARTY_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND PARTY.PARTY_TYPE = 'PERSON'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND CONT.ROLE_TYPE ='CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND SITE.CUST_ACCT_SITE_ID = CONT.CUST_ACCT_SITE_ID