VIEW: APPS.BIL_DIMV_SALESFORCE

Source

View Text - Preformatted

SELECT DISTINCT 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 AND NVL(PEOPLE.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE AND NVL(PEOPLE.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

View Text - HTML Formatted

SELECT DISTINCT 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
AND NVL(PEOPLE.EFFECTIVE_START_DATE
, SYSDATE) <= SYSDATE
AND NVL(PEOPLE.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