View: JTF_TERR_QLFY_RSC_V

Product:	JTF - CRM Foundation
Description:	This view stores all the informations about resources, groups and teams .
Implementation/DBA Data:	APPS.JTF_TERR_QLFY_RSC_V

View Text

SELECT JRRE.RESOURCE_ID
, JRRE.RESOURCE_NUMBER
, 'RS_EMPLOYEE' RESOURCE_TYPE
, JRRE.SOURCE_NAME
, JRRE.SOURCE_EMAIL
, JRRE.START_DATE_ACTIVE
, JRRE.END_DATE_ACTIVE
, JRRE.SOURCE_PHONE
, JRRE.CATEGORY
, PJ.JOB_ID
, PJ.NAME
, PAP.POSITION_ID
, PAP.NAME
, JRRE.PRIMARY_LANGUAGE
, PCE.COMPETENCE_ID
, PCE.PROFICIENCY_LEVEL_ID
, JRRE.SOURCE_CITY
, JRRE.SOURCE_POSTAL_CODE
FROM PER_ASSIGNMENTS_F PAF
, PER_COMPETENCE_ELEMENTS PCE
, PER_COMPETENCES PC
, PER_RATING_LEVELS PRL
, PER_ALL_POSITIONS PAP
, PER_JOBS PJ
, JTF_RS_RESOURCE_EXTNS JRRE
WHERE PCE.COMPETENCE_ID = PC.COMPETENCE_ID (+)
AND PCE.PROFICIENCY_LEVEL_ID = PRL.RATING_LEVEL_ID (+)
AND ( (PCE.EFFECTIVE_DATE_FROM IS NULL OR PCE.EFFECTIVE_DATE_FROM <= SYSDATE)
AND (PCE.EFFECTIVE_DATE_TO IS NULL OR PCE.EFFECTIVE_DATE_TO >= SYSDATE ) )
AND PCE.PERSON_ID (+) = JRRE.SOURCE_ID
AND PAF.JOB_ID = PJ.JOB_ID (+)
AND PAF.POSITION_ID = PAP.POSITION_ID (+)
AND PAF.PRIMARY_FLAG = 'Y'
AND PAF.ASSIGNMENT_TYPE = 'E'
AND PAF.PERSON_ID = JRRE.SOURCE_ID
AND JRRE.CATEGORY = 'EMPLOYEE' UNION ALL SELECT JRRE.RESOURCE_ID
, JRRE.RESOURCE_NUMBER
, 'RS_PARTNER' RESOURCE_TYPE
, JRRE.SOURCE_NAME ||' - '|| JRRE.SOURCE_CITY ||' - '|| JRPCV.CONTACT_NAME
, JRRE.SOURCE_EMAIL
, JRRE.START_DATE_ACTIVE
, JRRE.END_DATE_ACTIVE
, JRRE.SOURCE_PHONE
, JRRE.CATEGORY
, TO_NUMBER (NULL) JOB_ID
, NULL JOB_TITLE
, TO_NUMBER (NULL) POSITION_ID
, NULL POSITION
, JRRE.PRIMARY_LANGUAGE
, TO_NUMBER(NULL) COMPETENCE_ID
, TO_NUMBER(NULL) PROFICIENCY_LEVEL_ID
, JRRE.SOURCE_CITY
, JRRE.SOURCE_POSTAL_CODE
FROM JTF_RS_RESOURCE_EXTNS JRRE
, JTF_RS_PARTY_CONTACTS_VL JRPCV
WHERE JRRE.CONTACT_ID = JRPCV.CONTACT_ID (+)
AND JRRE.CATEGORY = 'PARTNER' UNION ALL SELECT JRRE.RESOURCE_ID
, JRRE.RESOURCE_NUMBER
, 'RS_OTHER' RESOURCE_TYPE
, JRS.NAME
, JRS.EMAIL_ADDRESS
, JRRE.START_DATE_ACTIVE
, JRRE.END_DATE_ACTIVE
, NULL PHONE
, JRRE.CATEGORY
, TO_NUMBER (NULL) JOB_ID
, NULL JOB_TITLE
, TO_NUMBER (NULL) POSITION_ID
, NULL POSITION
, JRRE.PRIMARY_LANGUAGE
, TO_NUMBER(NULL) COMPETENCE_ID
, TO_NUMBER(NULL) PROFICIENCY_LEVEL_ID
, NULL CITY
, NULL POSTAL_CODE
FROM JTF_RS_RESOURCE_EXTNS JRRE
, JTF_RS_SALESREPS JRS
, ORG_ORGANIZATION_DEFINITIONS ORG
WHERE JRRE.RESOURCE_ID = JRS.RESOURCE_ID
AND JRS.ORG_ID = ORG.ORGANIZATION_ID
AND JRRE.CATEGORY = 'OTHER' UNION ALL SELECT JRRE.RESOURCE_ID
, JRRE.RESOURCE_NUMBER
, 'RS_SUPPLIER_CONTACT' RESOURCE_TYPE
, JRRE.SOURCE_NAME
, JRRE.SOURCE_EMAIL
, JRRE.START_DATE_ACTIVE
, JRRE.END_DATE_ACTIVE
, JRRE.SOURCE_PHONE
, JRRE.CATEGORY
, TO_NUMBER (NULL) JOB_ID
, NULL JOB_TITLE
, TO_NUMBER (NULL) POSITION_ID
, NULL POSITION
, JRRE.PRIMARY_LANGUAGE
, TO_NUMBER(NULL) COMPETENCE_ID
, TO_NUMBER(NULL) PROFICIENCY_LEVEL_ID
, JRRE.SOURCE_CITY
, JRRE.SOURCE_POSTAL_CODE
FROM JTF_RS_RESOURCE_EXTNS JRRE
WHERE JRRE.CATEGORY = 'SUPPLIER_CONTACT' UNION ALL SELECT JRRE.RESOURCE_ID
, JRRE.RESOURCE_NUMBER
, 'RS_TBH' RESOURCE_TYPE
, JRRE.SOURCE_NAME
, JRRE.SOURCE_EMAIL
, JRRE.START_DATE_ACTIVE
, JRRE.END_DATE_ACTIVE
, JRRE.SOURCE_PHONE
, JRRE.CATEGORY
, TO_NUMBER (NULL) JOB_ID
, NULL JOB_TITLE
, TO_NUMBER (NULL) POSITION_ID
, NULL POSITION
, JRRE.PRIMARY_LANGUAGE
, TO_NUMBER(NULL) COMPETENCE_ID
, TO_NUMBER(NULL) PROFICIENCY_LEVEL_ID
, JRRE.SOURCE_CITY
, JRRE.SOURCE_POSTAL_CODE
FROM JTF_RS_RESOURCE_EXTNS JRRE
WHERE JRRE.CATEGORY = 'TBH' UNION ALL SELECT JRRE.RESOURCE_ID
, JRRE.RESOURCE_NUMBER
, 'RS_PARTY' RESOURCE_TYPE
, JRRE.SOURCE_NAME
, JRRE.SOURCE_EMAIL
, JRRE.START_DATE_ACTIVE
, JRRE.END_DATE_ACTIVE
, JRRE.SOURCE_PHONE
, JRRE.CATEGORY
, TO_NUMBER (NULL) JOB_ID
, NULL JOB_TITLE
, TO_NUMBER (NULL) POSITION_ID
, NULL POSITION
, JRRE.PRIMARY_LANGUAGE
, TO_NUMBER(NULL) COMPETENCE_ID
, TO_NUMBER(NULL) PROFICIENCY_LEVEL_ID
, NULL CITY
, NULL POSTAL_CODE
FROM JTF_RS_RESOURCE_EXTNS JRRE
WHERE JRRE.CATEGORY = 'PARTY' UNION ALL SELECT JRGV.GROUP_ID RESOURCE_ID
, JRGV.GROUP_NUMBER RESOURCE_NUMBER
, 'RS_GROUP' RESOURCE_TYPE
, JRGV.GROUP_NAME RESOURCE_NAME
, JRGV.EMAIL_ADDRESS EMAIL
, JRGV.START_DATE_ACTIVE START_DATE_ACTIVE
, JRGV.END_DATE_ACTIVE END_DATE_ACTIVE
, NULL PHONE
, NULL CATEGORY
, TO_NUMBER(NULL) JOB_ID
, NULL JOB_TITLE
, TO_NUMBER(NULL) POSITION_ID
, NULL POSITION
, NULL LANGUAGE
, TO_NUMBER(NULL) COMPETENCE_ID
, TO_NUMBER(NULL) PROFICIENCY_LEVEL_ID
, NULL CITY
, NULL POSTAL_CODE
FROM JTF_RS_GROUPS_VL JRGV UNION ALL SELECT JRTV.TEAM_ID RESOURCE_ID
, JRTV.TEAM_NUMBER RESOURCE_NUMBER
, 'RS_TEAM' RESOURCE_TYPE
, JRTV.TEAM_NAME RESOURCE_NAME
, JRTV.EMAIL_ADDRESS EMAIL
, JRTV.START_DATE_ACTIVE START_DATE_ACTIVE
, JRTV.END_DATE_ACTIVE END_DATE_ACTIVE
, NULL PHONE
, NULL CATEGORY
, TO_NUMBER(NULL) JOB_ID
, NULL JOB_TITLE
, TO_NUMBER(NULL) POSITION_ID
, NULL POSITION
, NULL LANGUAGE
, TO_NUMBER(NULL) COMPETENCE_ID
, TO_NUMBER(NULL) PROFICIENCY_LEVEL_ID
, NULL CITY
, NULL POSTAL_CODE
FROM JTF_RS_TEAMS_VL JRTV

Columns