View: BIL_DIMV_SALESFORCE

Product:	BIL - Sales Intelligence
Description:	Salesforce view (sales reps)
Implementation/DBA Data:	APPS.BIL_DIMV_SALESFORCE

View Text

SELECT JRE.RESOURCE_ID
, JRE.RESOURCE_NUMBER
, JRE.SOURCE_ID
, JRS.STATUS
, JRE.CATEGORY
, PEOPLE.LAST_NAME
, PEOPLE.FIRST_NAME
, CONCAT(CONCAT(PEOPLE.FIRST_NAME
, ' ')
, PEOPLE.LAST_NAME) FULL_NAME
, JRE.COMPENSATION_CURRENCY_CODE
, JRE.START_DATE_ACTIVE
, JRE.END_DATE_ACTIVE
, PAF.EMPLOYMENT_CATEGORY
, NVL(PAF.JOB_ID
, -999)
, JRE.MANAGING_EMPLOYEE_ID
, TO_CHAR(JRE.RESOURCE_ID) ID
, SUBSTR(CONCAT(CONCAT(PEOPLE.FIRST_NAME
, ' ')
, PEOPLE.LAST_NAME)
, 1
, 80) VALUE
FROM JTF_RS_RESOURCE_EXTNS JRE
, PER_ALL_PEOPLE_F PEOPLE
, JTF_RS_SALESREPS JRS
, PER_ALL_ASSIGNMENTS_F PAF
WHERE JRS.RESOURCE_ID = JRE.RESOURCE_ID
AND JRS.PERSON_ID = PEOPLE.PERSON_ID
AND PAF.PERSON_ID = PEOPLE.PERSON_ID
AND PAF.PRIMARY_FLAG = 'Y'
AND NVL(PAF.EFFECTIVE_START_DATE
, SYSDATE) <= SYSDATE
AND NVL(PAF.EFFECTIVE_END_DATE
, SYSDATE) >= SYSDATE UNION ALL (SELECT TO_NUMBER(FL.LOOKUP_CODE) SALESFORCE_PK
, FL.LOOKUP_CODE RESOURCE_NUMBER
, TO_NUMBER(FL.LOOKUP_CODE) EMPLOYEE_PERSON_FK
, SUBSTR(FL.MEANING
, 1
, 30) SALESFORCE_STATUS
, SUBSTR(FL.MEANING
, 1
, 30) SALESFORCE_TYPE
, SUBSTR(FL.MEANING
, 1
, 40) LAST_NAME
, SUBSTR(FL.MEANING
, 1
, 20) FIRST_NAME
, SUBSTR(FL.MEANING
, 1
, 40) FULL_NAME
, SUBSTR(FL.MEANING
, 1
, 15) COMPENSATION_CURRENCY_CODE
, FL.START_DATE_ACTIVE START_DATE_ACTIVE
, FL.END_DATE_ACTIVE END_DATE_ACTIVE
, NULL
, TO_NUMBER(FL.LOOKUP_CODE) JOB_FK
, TO_NUMBER(FL.LOOKUP_CODE) MANAGING_EMPLOYEE_ID
, SUBSTR(FL.LOOKUP_CODE
, 1
, 40) ID
, SUBSTR(FL.MEANING
, 1
, 40) VALUE
FROM FND_LOOKUPS FL
WHERE FL.LOOKUP_TYPE = 'BIL_VALUE_TYPE'
AND FL.LOOKUP_CODE = '-999' ) WITH READ ONLY

Columns