VIEW: APPS.AST_SALES_TEAM_EMP_V

Source

View Text - Preformatted

SELECT acc.rowid , acc.access_id , acc.last_update_date , acc.last_updated_by , acc.creation_date , acc.created_by , acc.last_update_login , acc.request_id , acc.program_application_id , acc.program_id , acc.program_update_date , acc.access_type , acc.freeze_flag , acc.reassign_flag , acc.team_leader_flag , acc.person_id , acc.customer_id , acc.address_id , acc.salesforce_id , acc.created_person_id , acc.lead_id , acc.freeze_date , acc.reassign_reason , acc.downloadable_flag , 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 , sf.source_first_name , sf.source_last_name , sf.source_name , sf.source_email , sf.source_phone , sg.group_id , sg.group_name , acc.salesforce_role_code , aslkp2.meaning , acc.salesforce_relationship_code , aslkp3.meaning , acc.reassign_request_date , acc.reassign_requested_person_id , acc.sales_lead_id , loc.city , loc.postal_code , loc.state , loc.province , loc.county , loc.country , ftv.territory_short_name, loc.address1 , loc.address2 , loc.address3 , loc.address4 , acc.owner_flag, job.name from as_accesses_all acc , jtf_rs_resource_extns sf , jtf_rs_groups_tl sg , as_lookups aslkp2 , as_lookups aslkp3 , hz_party_sites site , hz_locations loc, fnd_territories_vl ftv, per_assignments_f assign, per_jobs job WHERE acc.salesforce_role_code = aslkp2.lookup_code(+) and aslkp2.lookup_type(+) = 'ROLE_TYPE' and acc.salesforce_relationship_code = aslkp3.lookup_code(+) and aslkp3.lookup_type(+) = 'SALESFORCE_RELATIONSHIP' and acc.sales_group_id = sg.group_id(+) and sg.language(+) = userenv('LANG') and acc.salesforce_id = sf.resource_id and sf.category = 'EMPLOYEE' and sysdate between sf.start_date_active and nvl(sf.end_date_active,sysdate) and acc.address_id=site.party_site_id(+) and site.location_id=loc.location_id(+) and loc.country = ftv.territory_code(+) and assign.primary_flag(+) = 'Y' and assign.assignment_type(+) = 'E' and assign.job_id = job.job_id(+) and assign.person_id(+) = acc.person_id and trunc(sysdate) between assign.effective_start_date(+) and assign.effective_end_date(+)

View Text - HTML Formatted

SELECT ACC.ROWID
, ACC.ACCESS_ID
, ACC.LAST_UPDATE_DATE
, ACC.LAST_UPDATED_BY
, ACC.CREATION_DATE
, ACC.CREATED_BY
, ACC.LAST_UPDATE_LOGIN
, ACC.REQUEST_ID
, ACC.PROGRAM_APPLICATION_ID
, ACC.PROGRAM_ID
, ACC.PROGRAM_UPDATE_DATE
, ACC.ACCESS_TYPE
, ACC.FREEZE_FLAG
, ACC.REASSIGN_FLAG
, ACC.TEAM_LEADER_FLAG
, ACC.PERSON_ID
, ACC.CUSTOMER_ID
, ACC.ADDRESS_ID
, ACC.SALESFORCE_ID
, ACC.CREATED_PERSON_ID
, ACC.LEAD_ID
, ACC.FREEZE_DATE
, ACC.REASSIGN_REASON
, ACC.DOWNLOADABLE_FLAG
, 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
, SF.SOURCE_FIRST_NAME
, SF.SOURCE_LAST_NAME
, SF.SOURCE_NAME
, SF.SOURCE_EMAIL
, SF.SOURCE_PHONE
, SG.GROUP_ID
, SG.GROUP_NAME
, ACC.SALESFORCE_ROLE_CODE
, ASLKP2.MEANING
, ACC.SALESFORCE_RELATIONSHIP_CODE
, ASLKP3.MEANING
, ACC.REASSIGN_REQUEST_DATE
, ACC.REASSIGN_REQUESTED_PERSON_ID
, ACC.SALES_LEAD_ID
, LOC.CITY
, LOC.POSTAL_CODE
, LOC.STATE
, LOC.PROVINCE
, LOC.COUNTY
, LOC.COUNTRY
, FTV.TERRITORY_SHORT_NAME
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, ACC.OWNER_FLAG
, JOB.NAME
FROM AS_ACCESSES_ALL ACC
, JTF_RS_RESOURCE_EXTNS SF
, JTF_RS_GROUPS_TL SG
, AS_LOOKUPS ASLKP2
, AS_LOOKUPS ASLKP3
, HZ_PARTY_SITES SITE
, HZ_LOCATIONS LOC
, FND_TERRITORIES_VL FTV
, PER_ASSIGNMENTS_F ASSIGN
, PER_JOBS JOB
WHERE ACC.SALESFORCE_ROLE_CODE = ASLKP2.LOOKUP_CODE(+)
AND ASLKP2.LOOKUP_TYPE(+) = 'ROLE_TYPE'
AND ACC.SALESFORCE_RELATIONSHIP_CODE = ASLKP3.LOOKUP_CODE(+)
AND ASLKP3.LOOKUP_TYPE(+) = 'SALESFORCE_RELATIONSHIP'
AND ACC.SALES_GROUP_ID = SG.GROUP_ID(+)
AND SG.LANGUAGE(+) = USERENV('LANG')
AND ACC.SALESFORCE_ID = SF.RESOURCE_ID
AND SF.CATEGORY = 'EMPLOYEE'
AND SYSDATE BETWEEN SF.START_DATE_ACTIVE
AND NVL(SF.END_DATE_ACTIVE
, SYSDATE)
AND ACC.ADDRESS_ID=SITE.PARTY_SITE_ID(+)
AND SITE.LOCATION_ID=LOC.LOCATION_ID(+)
AND LOC.COUNTRY = FTV.TERRITORY_CODE(+)
AND ASSIGN.PRIMARY_FLAG(+) = 'Y'
AND ASSIGN.ASSIGNMENT_TYPE(+) = 'E'
AND ASSIGN.JOB_ID = JOB.JOB_ID(+)
AND ASSIGN.PERSON_ID(+) = ACC.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN ASSIGN.EFFECTIVE_START_DATE(+)
AND ASSIGN.EFFECTIVE_END_DATE(+)