VIEW: APPS.PA_C_RESOURCE_V

Source

View Text - Preformatted

SELECT per.person_id resource_source_id, res.resource_id, per.full_name resource_name, per.first_name resource_first_name, per.last_name resource_last_name, per.middle_names resource_middle_names, type.resource_type_code resource_type, res.resource_type_id resource_type_id, decode(per.current_employee_flag, 'Y', per.employee_number, per.npw_number) resource_number, assign.organization_id resource_Organization_Id, pa_resource_utils.get_organization_name(assign.organization_id) resource_Organization_Name, assign.effective_start_date assignment_start_date, assign.effective_end_date assignment_end_date from per_people_f per, per_assignments_f assign, per_assignment_status_types ast, pa_resources res, pa_resource_types type, pa_resource_txn_attributes txn WHERE per.person_id = assign.person_id and (per.employee_number IS NOT NULL OR per.npw_number IS NOT NULL) and trunc(sysdate) BETWEEN per.effective_start_date AND per.effective_end_date and assign.primary_flag = 'Y' and assign.assignment_type in ('E', 'C') and assign.job_id is not null and trunc(sysdate) <= assign.effective_end_date and ast.assignment_status_type_id = assign.assignment_status_type_id and ast.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') and per.person_id = txn.person_id(+) and txn.resource_id = res.resource_id(+) and res.resource_type_id = type.resource_type_id(+) and assign.effective_start_date = (SELECT min(assign1.effective_start_date) FROM per_assignments_f assign1, per_assignment_status_types ast1 WHERE assign1.person_id = assign.person_id AND assign1.effective_end_date >= trunc(sysdate) AND assign1.primary_flag = 'Y' AND assign1.assignment_type in ('E', 'C') AND assign1.job_id is not null AND ast1.assignment_status_type_id = assign1.assignment_status_type_id AND ast1.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') ) and (per.current_employee_flag = 'Y' OR per.current_npw_flag = 'Y')

View Text - HTML Formatted

SELECT PER.PERSON_ID RESOURCE_SOURCE_ID
, RES.RESOURCE_ID
, PER.FULL_NAME RESOURCE_NAME
, PER.FIRST_NAME RESOURCE_FIRST_NAME
, PER.LAST_NAME RESOURCE_LAST_NAME
, PER.MIDDLE_NAMES RESOURCE_MIDDLE_NAMES
, TYPE.RESOURCE_TYPE_CODE RESOURCE_TYPE
, RES.RESOURCE_TYPE_ID RESOURCE_TYPE_ID
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PER.EMPLOYEE_NUMBER
, PER.NPW_NUMBER) RESOURCE_NUMBER
, ASSIGN.ORGANIZATION_ID RESOURCE_ORGANIZATION_ID
, PA_RESOURCE_UTILS.GET_ORGANIZATION_NAME(ASSIGN.ORGANIZATION_ID) RESOURCE_ORGANIZATION_NAME
, ASSIGN.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASSIGN.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
FROM PER_PEOPLE_F PER
, PER_ASSIGNMENTS_F ASSIGN
, PER_ASSIGNMENT_STATUS_TYPES AST
, PA_RESOURCES RES
, PA_RESOURCE_TYPES TYPE
, PA_RESOURCE_TXN_ATTRIBUTES TXN
WHERE PER.PERSON_ID = ASSIGN.PERSON_ID
AND (PER.EMPLOYEE_NUMBER IS NOT NULL OR PER.NPW_NUMBER IS NOT NULL)
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND ASSIGN.PRIMARY_FLAG = 'Y'
AND ASSIGN.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND ASSIGN.JOB_ID IS NOT NULL
AND TRUNC(SYSDATE) <= ASSIGN.EFFECTIVE_END_DATE
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASSIGN.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'ACTIVE_CWK')
AND PER.PERSON_ID = TXN.PERSON_ID(+)
AND TXN.RESOURCE_ID = RES.RESOURCE_ID(+)
AND RES.RESOURCE_TYPE_ID = TYPE.RESOURCE_TYPE_ID(+)
AND ASSIGN.EFFECTIVE_START_DATE = (SELECT MIN(ASSIGN1.EFFECTIVE_START_DATE)
FROM PER_ASSIGNMENTS_F ASSIGN1
, PER_ASSIGNMENT_STATUS_TYPES AST1
WHERE ASSIGN1.PERSON_ID = ASSIGN.PERSON_ID
AND ASSIGN1.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)
AND ASSIGN1.PRIMARY_FLAG = 'Y'
AND ASSIGN1.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND ASSIGN1.JOB_ID IS NOT NULL
AND AST1.ASSIGNMENT_STATUS_TYPE_ID = ASSIGN1.ASSIGNMENT_STATUS_TYPE_ID
AND AST1.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'ACTIVE_CWK') )
AND (PER.CURRENT_EMPLOYEE_FLAG = 'Y' OR PER.CURRENT_NPW_FLAG = 'Y')