Product: | AS - Sales Foundation |
---|---|
Description: | Territory accesses view |
Implementation/DBA Data: | APPS.AS_TERRITORY_ACCESSES_V |
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'