VIEW: APPS.PA_C_ELIG_RESOURCE_V

Source

View Text - Preformatted

SELECT per.person_id resource_source_id, res.resource_id, pa_resource_utils.get_person_name_no_date(per.person_id) 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, per.employee_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, 'EMP' 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 ) AND least(greatest(assign.effective_start_date,sysdate),assign.effective_end_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 = 'E' 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 per.current_employee_flag = 'Y' AND org.LANGUAGE = USERENV ('LANG') UNION SELECT per.person_id resource_source_id, res.resource_id, pa_resource_utils.get_person_name_no_date(per.person_id) 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, 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, '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.npw_number IS NOT NULL) AND least(greatest(assign.effective_start_date,sysdate),assign.effective_end_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 = '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 = '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 per.current_npw_flag = 'Y' 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, per.employee_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, 'EMP' 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 ) AND (assign.effective_start_date-1) 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 = 'E' 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 ('TERM_ASSIGN') 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 per.current_employee_flag = 'Y' 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, 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, '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.npw_number IS NOT NULL) AND (assign.effective_start_date-1) 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 = '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 ('TERM_ASSIGN') 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 per.current_employee_flag <> 'Y' AND org.LANGUAGE = userenv('LANG')

View Text - HTML Formatted

SELECT PER.PERSON_ID RESOURCE_SOURCE_ID
, RES.RESOURCE_ID
, PA_RESOURCE_UTILS.GET_PERSON_NAME_NO_DATE(PER.PERSON_ID) 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
, PER.EMPLOYEE_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
, 'EMP' 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 )
AND LEAST(GREATEST(ASSIGN.EFFECTIVE_START_DATE
, SYSDATE)
, ASSIGN.EFFECTIVE_END_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 = 'E'
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 PER.CURRENT_EMPLOYEE_FLAG = 'Y'
AND ORG.LANGUAGE = USERENV ('LANG') UNION SELECT PER.PERSON_ID RESOURCE_SOURCE_ID
, RES.RESOURCE_ID
, PA_RESOURCE_UTILS.GET_PERSON_NAME_NO_DATE(PER.PERSON_ID) 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
, 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
, '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.NPW_NUMBER IS NOT NULL)
AND LEAST(GREATEST(ASSIGN.EFFECTIVE_START_DATE
, SYSDATE)
, ASSIGN.EFFECTIVE_END_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 = '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 = '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 PER.CURRENT_NPW_FLAG = 'Y'
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
, PER.EMPLOYEE_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
, 'EMP' 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 )
AND (ASSIGN.EFFECTIVE_START_DATE-1) 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 = 'E'
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 ('TERM_ASSIGN')
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 PER.CURRENT_EMPLOYEE_FLAG = 'Y'
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
, 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
, '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.NPW_NUMBER IS NOT NULL)
AND (ASSIGN.EFFECTIVE_START_DATE-1) 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 = '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 ('TERM_ASSIGN')
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 PER.CURRENT_EMPLOYEE_FLAG <> 'Y'
AND ORG.LANGUAGE = USERENV('LANG')