VIEW: APPS.AS_TERRITORY_ACCESSES_V

Source

View Text - Preformatted

SELECT distinct acc.rowid ,acc.access_id ,accterr.territory_id ,acc.last_update_date ,acc.last_updated_by ,acc.creation_date ,acc.created_by ,acc.last_update_login ,acc.access_type ,acc.freeze_flag ,acc.reassign_flag ,acc.team_leader_flag ,acc.salesforce_id ,acc.sales_group_id ,acc.person_id ,acc.customer_id ,acc.address_id ,acc.created_person_id ,acc.lead_id ,acc.freeze_date ,acc.reassign_reason ,acc.attribute_category ,acc.attribute1 ,acc.attribute2 ,acc.attribute3 ,acc.attribute4 ,acc.attribute5 ,acc.attribute6 ,acc.attribute7 ,acc.attribute8 ,acc.attribute9 ,acc.attribute10 ,acc.attribute11 ,acc.attribute12 ,acc.attribute13 ,acc.attribute14 ,acc.attribute15 ,aslkp1.meaning ,people.first_name ,people.last_name ,lead.lead_number ,lead.description ,cust.customer_name ,cust.customer_number ,addr.address_id ,addr.address1 ,addr.city ,aslkp2.meaning ,acc.salesforce_role_code ,aslkp3.meaning ,acc.salesforce_role_code ,acc.internal_update_access from PER_ALL_PEOPLE_F people , AS_LOOKUPS aslkp1 , AS_LEADS lead , AS_PARTY_ADDRESSES_V addr , AS_PARTY_CUSTOMERS_V cust , AS_ACCESSES acc , AS_TERRITORY_ACCESSES accterr , AS_LOOKUPS aslkp2 , AS_LOOKUPS aslkp3 where acc.access_type = aslkp1.lookup_code(+) and aslkp1.lookup_type(+) = 'ACCESS_TYPE' and acc.customer_id = cust.customer_id(+) and ( cust.customer_id = addr.customer_id or ( cust.customer_id is not null and addr.customer_id is null)) and acc.lead_id = lead.lead_id(+) and acc.person_id = people.person_id(+) and trunc(sysdate) between people.effective_start_date(+) and people.effective_end_date(+) and acc.address_id = addr.address_id(+) and accterr.access_id = acc.access_id(+) and acc.salesforce_role_code = aslkp2.lookup_code(+) and aslkp2.lookup_type(+) = 'ROLE_CODE' and acc.salesforce_relationship_code = aslkp3.lookup_code(+) and aslkp3.lookup_type(+) = 'SALESFORCE_RELATIONSHIP'

View Text - HTML Formatted

SELECT DISTINCT ACC.ROWID
, ACC.ACCESS_ID
, ACCTERR.TERRITORY_ID
, ACC.LAST_UPDATE_DATE
, ACC.LAST_UPDATED_BY
, ACC.CREATION_DATE
, ACC.CREATED_BY
, ACC.LAST_UPDATE_LOGIN
, ACC.ACCESS_TYPE
, ACC.FREEZE_FLAG
, ACC.REASSIGN_FLAG
, ACC.TEAM_LEADER_FLAG
, ACC.SALESFORCE_ID
, ACC.SALES_GROUP_ID
, ACC.PERSON_ID
, ACC.CUSTOMER_ID
, ACC.ADDRESS_ID
, ACC.CREATED_PERSON_ID
, ACC.LEAD_ID
, ACC.FREEZE_DATE
, ACC.REASSIGN_REASON
, ACC.ATTRIBUTE_CATEGORY
, ACC.ATTRIBUTE1
, ACC.ATTRIBUTE2
, ACC.ATTRIBUTE3
, ACC.ATTRIBUTE4
, ACC.ATTRIBUTE5
, ACC.ATTRIBUTE6
, ACC.ATTRIBUTE7
, ACC.ATTRIBUTE8
, ACC.ATTRIBUTE9
, ACC.ATTRIBUTE10
, ACC.ATTRIBUTE11
, ACC.ATTRIBUTE12
, ACC.ATTRIBUTE13
, ACC.ATTRIBUTE14
, ACC.ATTRIBUTE15
, ASLKP1.MEANING
, PEOPLE.FIRST_NAME
, PEOPLE.LAST_NAME
, LEAD.LEAD_NUMBER
, LEAD.DESCRIPTION
, CUST.CUSTOMER_NAME
, CUST.CUSTOMER_NUMBER
, ADDR.ADDRESS_ID
, ADDR.ADDRESS1
, ADDR.CITY
, ASLKP2.MEANING
, ACC.SALESFORCE_ROLE_CODE
, ASLKP3.MEANING
, ACC.SALESFORCE_ROLE_CODE
, ACC.INTERNAL_UPDATE_ACCESS
FROM PER_ALL_PEOPLE_F PEOPLE
, AS_LOOKUPS ASLKP1
, AS_LEADS LEAD
, AS_PARTY_ADDRESSES_V ADDR
, AS_PARTY_CUSTOMERS_V CUST
, AS_ACCESSES ACC
, AS_TERRITORY_ACCESSES ACCTERR
, AS_LOOKUPS ASLKP2
, AS_LOOKUPS ASLKP3
WHERE ACC.ACCESS_TYPE = ASLKP1.LOOKUP_CODE(+)
AND ASLKP1.LOOKUP_TYPE(+) = 'ACCESS_TYPE'
AND ACC.CUSTOMER_ID = CUST.CUSTOMER_ID(+)
AND ( CUST.CUSTOMER_ID = ADDR.CUSTOMER_ID OR ( CUST.CUSTOMER_ID IS NOT NULL
AND ADDR.CUSTOMER_ID IS NULL))
AND ACC.LEAD_ID = LEAD.LEAD_ID(+)
AND ACC.PERSON_ID = PEOPLE.PERSON_ID(+)
AND TRUNC(SYSDATE) BETWEEN PEOPLE.EFFECTIVE_START_DATE(+)
AND PEOPLE.EFFECTIVE_END_DATE(+)
AND ACC.ADDRESS_ID = ADDR.ADDRESS_ID(+)
AND ACCTERR.ACCESS_ID = ACC.ACCESS_ID(+)
AND ACC.SALESFORCE_ROLE_CODE = ASLKP2.LOOKUP_CODE(+)
AND ASLKP2.LOOKUP_TYPE(+) = 'ROLE_CODE'
AND ACC.SALESFORCE_RELATIONSHIP_CODE = ASLKP3.LOOKUP_CODE(+)
AND ASLKP3.LOOKUP_TYPE(+) = 'SALESFORCE_RELATIONSHIP'