VIEW: APPS.PA_C_ELIG_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, org.name, assign.effective_start_date assignment_start_date, assign.effective_end_date assignment_end_date, null, null, decode(per.current_employee_flag, 'Y', 'EMP', 'CWK') person_type, per.email_address resource_email_address 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, per_job_extra_info jei, hr_all_organization_units_tl org WHERE per.person_id = assign.person_id and (per.employee_number IS NOT NULL OR per.npw_number IS NOT NULL) AND assign.effective_start_date BETWEEN per.effective_start_date AND per.effective_end_date and exists (select organization_id from pa_all_organizations where pa_org_use_type = 'EXPENDITURES' and inactive_date is NULL and organization_id = assign.organization_id) and assign.primary_flag = 'Y' and assign.assignment_type in ('E', 'C') and assign.job_id is not null and assign.job_id = jei.job_id and jei.jei_information_category = 'Job Category' and jei.jei_information6 = 'Y' 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 org.organization_id=assign.organization_id and org.LANGUAGE = USERENV ('LANG') UNION 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, org.name, assign.effective_start_date assignment_start_date, assign.effective_end_date assignment_end_date, NULL, NULL, decode(per.current_employee_flag, 'Y', 'EMP', 'CWK') person_type, per.email_address resource_email_address 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, per_job_extra_info jei, hr_all_organization_units_tl org WHERE per.person_id = assign.person_id AND(per.employee_number IS NOT NULL OR per.npw_number IS NOT NULL) AND assign.effective_start_date BETWEEN per.effective_start_date AND per.effective_end_date AND EXISTS (SELECT organization_id FROM pa_all_organizations WHERE pa_org_use_type = 'EXPENDITURES' AND inactive_date IS NULL AND organization_id = assign.organization_id) AND assign.primary_flag = 'Y' AND assign.assignment_type NOT IN('E', 'C') AND res.future_term_wf_flag = 'Y' AND assign.job_id IS NOT NULL AND assign.job_id = jei.job_id AND jei.jei_information_category = 'Job Category' AND jei.jei_information6 = 'Y' 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 org.organization_id = assign.organization_id AND org.LANGUAGE = userenv('LANG')

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
, ORG.NAME
, ASSIGN.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASSIGN.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, NULL
, NULL
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, 'EMP'
, 'CWK') PERSON_TYPE
, PER.EMAIL_ADDRESS RESOURCE_EMAIL_ADDRESS
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
, PER_JOB_EXTRA_INFO JEI
, HR_ALL_ORGANIZATION_UNITS_TL ORG
WHERE PER.PERSON_ID = ASSIGN.PERSON_ID
AND (PER.EMPLOYEE_NUMBER IS NOT NULL OR PER.NPW_NUMBER IS NOT NULL)
AND ASSIGN.EFFECTIVE_START_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND EXISTS (SELECT ORGANIZATION_ID
FROM PA_ALL_ORGANIZATIONS
WHERE PA_ORG_USE_TYPE = 'EXPENDITURES'
AND INACTIVE_DATE IS NULL
AND ORGANIZATION_ID = ASSIGN.ORGANIZATION_ID)
AND ASSIGN.PRIMARY_FLAG = 'Y'
AND ASSIGN.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND ASSIGN.JOB_ID IS NOT NULL
AND ASSIGN.JOB_ID = JEI.JOB_ID
AND JEI.JEI_INFORMATION_CATEGORY = 'JOB CATEGORY'
AND JEI.JEI_INFORMATION6 = 'Y'
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 ORG.ORGANIZATION_ID=ASSIGN.ORGANIZATION_ID
AND ORG.LANGUAGE = USERENV ('LANG') UNION 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
, ORG.NAME
, ASSIGN.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASSIGN.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, NULL
, NULL
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, 'EMP'
, 'CWK') PERSON_TYPE
, PER.EMAIL_ADDRESS RESOURCE_EMAIL_ADDRESS
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
, PER_JOB_EXTRA_INFO JEI
, HR_ALL_ORGANIZATION_UNITS_TL ORG
WHERE PER.PERSON_ID = ASSIGN.PERSON_ID AND(PER.EMPLOYEE_NUMBER IS NOT NULL OR PER.NPW_NUMBER IS NOT NULL)
AND ASSIGN.EFFECTIVE_START_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND EXISTS (SELECT ORGANIZATION_ID
FROM PA_ALL_ORGANIZATIONS
WHERE PA_ORG_USE_TYPE = 'EXPENDITURES'
AND INACTIVE_DATE IS NULL
AND ORGANIZATION_ID = ASSIGN.ORGANIZATION_ID)
AND ASSIGN.PRIMARY_FLAG = 'Y'
AND ASSIGN.ASSIGNMENT_TYPE NOT IN('E'
, 'C')
AND RES.FUTURE_TERM_WF_FLAG = 'Y'
AND ASSIGN.JOB_ID IS NOT NULL
AND ASSIGN.JOB_ID = JEI.JOB_ID
AND JEI.JEI_INFORMATION_CATEGORY = 'JOB CATEGORY'
AND JEI.JEI_INFORMATION6 = 'Y'
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 ORG.ORGANIZATION_ID = ASSIGN.ORGANIZATION_ID
AND ORG.LANGUAGE = USERENV('LANG')