VIEW: APPS.JTF_TERR_QLFY_RSC_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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