VIEW: APPS.AS_SALES_TEAM_DISTINCT_V

Source

View Text - Preformatted

SELECT ACC.PERSON_ID , ACC.ROWID , ACC.CUSTOMER_ID , ACC.ADDRESS_ID , ACC.PARTNER_CUSTOMER_ID , ACC.PARTNER_ADDRESS_ID , ACC.SALESFORCE_ID , ACC.REASSIGN_REASON , ACC.REASSIGN_FLAG , HREMP.FIRST_NAME , HREMP.LAST_NAME , CUST.CUSTOMER_NAME , CUST.CUSTOMER_NUMBER , ADDR.CITY , HREMP.EMAIL_ADDRESS , HREMP.WORK_TELEPHONE , SGT.GROUP_ID , SGT.GROUP_NAME , 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.TEAM_LEADER_FLAG , ACC.CREATED_PERSON_ID , ACC.LEAD_ID , ACC.FREEZE_DATE ,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 , ACC.ORG_ID , ACC.SALESFORCE_ROLE_CODE , ASLKP2.MEANING , ACC.SALESFORCE_RELATIONSHIP_CODE , ASLKP1.MEANING , ACC.PARTNER_CONT_PARTY_ID FROM AS_PARTY_CUSTOMERS_V cust , AS_PARTY_ADDRESSES_V addr , PER_ALL_PEOPLE_F hremp , JTF_RS_GROUPS_TL sgt , JTF_RS_RESOURCE_EXTNS sf , AS_LOOKUPS aslkp1 , AS_LOOKUPS aslkp2 , AS_ACCESSES_ALL acc , JTF_RS_GROUP_MEMBERS mem WHERE ACC.PARTNER_ADDRESS_ID = ADDR.ADDRESS_ID(+) AND SF.CATEGORY(+) = 'EMPLOYEE' AND SF.RESOURCE_ID = MEM.RESOURCE_ID AND MEM.GROUP_ID =SGT.GROUP_ID(+) AND SGT.LANGUAGE(+) = userenv('LANG') AND ACC.PERSON_ID = SF.SOURCE_ID(+) AND ASLKP1.LOOKUP_TYPE(+) = 'SALESFORCE_RELATIONSHIP' AND ACC.SALESFORCE_RELATIONSHIP_CODE = ASLKP1.LOOKUP_CODE(+) AND ACC.SALESFORCE_ROLE_CODE = ASLKP2.LOOKUP_CODE(+) AND ASLKP2.LOOKUP_TYPE(+) = 'ROLE_TYPE' AND ACC.PARTNER_CUSTOMER_ID = CUST.CUSTOMER_ID(+) AND TRUNC(SYSDATE) BETWEEN HREMP.EFFECTIVE_START_DATE(+) AND HREMP.EFFECTIVE_END_DATE(+) AND ACC.PERSON_ID = HREMP.PERSON_ID(+) AND ACC.LEAD_ID IS NULL

View Text - HTML Formatted

SELECT ACC.PERSON_ID
, ACC.ROWID
, ACC.CUSTOMER_ID
, ACC.ADDRESS_ID
, ACC.PARTNER_CUSTOMER_ID
, ACC.PARTNER_ADDRESS_ID
, ACC.SALESFORCE_ID
, ACC.REASSIGN_REASON
, ACC.REASSIGN_FLAG
, HREMP.FIRST_NAME
, HREMP.LAST_NAME
, CUST.CUSTOMER_NAME
, CUST.CUSTOMER_NUMBER
, ADDR.CITY
, HREMP.EMAIL_ADDRESS
, HREMP.WORK_TELEPHONE
, SGT.GROUP_ID
, SGT.GROUP_NAME
, 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.TEAM_LEADER_FLAG
, ACC.CREATED_PERSON_ID
, ACC.LEAD_ID
, ACC.FREEZE_DATE
, 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
, ACC.ORG_ID
, ACC.SALESFORCE_ROLE_CODE
, ASLKP2.MEANING
, ACC.SALESFORCE_RELATIONSHIP_CODE
, ASLKP1.MEANING
, ACC.PARTNER_CONT_PARTY_ID
FROM AS_PARTY_CUSTOMERS_V CUST
, AS_PARTY_ADDRESSES_V ADDR
, PER_ALL_PEOPLE_F HREMP
, JTF_RS_GROUPS_TL SGT
, JTF_RS_RESOURCE_EXTNS SF
, AS_LOOKUPS ASLKP1
, AS_LOOKUPS ASLKP2
, AS_ACCESSES_ALL ACC
, JTF_RS_GROUP_MEMBERS MEM
WHERE ACC.PARTNER_ADDRESS_ID = ADDR.ADDRESS_ID(+)
AND SF.CATEGORY(+) = 'EMPLOYEE'
AND SF.RESOURCE_ID = MEM.RESOURCE_ID
AND MEM.GROUP_ID =SGT.GROUP_ID(+)
AND SGT.LANGUAGE(+) = USERENV('LANG')
AND ACC.PERSON_ID = SF.SOURCE_ID(+)
AND ASLKP1.LOOKUP_TYPE(+) = 'SALESFORCE_RELATIONSHIP'
AND ACC.SALESFORCE_RELATIONSHIP_CODE = ASLKP1.LOOKUP_CODE(+)
AND ACC.SALESFORCE_ROLE_CODE = ASLKP2.LOOKUP_CODE(+)
AND ASLKP2.LOOKUP_TYPE(+) = 'ROLE_TYPE'
AND ACC.PARTNER_CUSTOMER_ID = CUST.CUSTOMER_ID(+)
AND TRUNC(SYSDATE) BETWEEN HREMP.EFFECTIVE_START_DATE(+)
AND HREMP.EFFECTIVE_END_DATE(+)
AND ACC.PERSON_ID = HREMP.PERSON_ID(+)
AND ACC.LEAD_ID IS NULL