VIEW: APPS.PA_C_JOBLEVEL_RES_V

Source

View Text - Preformatted

SELECT per.person_id resource_source_id, res.resource_id, per.full_name resource_name, (select resource_type_code from pa_resource_types WHERE resource_type_id(+) = res.resource_type_id ) resource_type, res.resource_type_id resource_type_id, decode(per.current_employee_flag, 'Y', per.employee_number, per.npw_number) resource_number, assign.job_id resource_job_id, pj.name resource_job_name, pa_hr_update_api.get_job_level(assign.job_id, pj.job_group_id) resource_job_level, assign.organization_id resource_organization_id, pa_resource_utils.get_organization_name(assign.organization_id) resource_organization_name, add1.country resource_country_code, fnd.territory_short_name resource_country_name, assign.effective_start_date assignment_start_date, assign.effective_end_date assignment_end_date, add1.date_from address_date_from, add1.date_to address_date_to, NULL grade_date_from, NULL grade_date_to from per_assignments_f assign, hr_organization_information b1, (select person_id, actual_termination_date from per_periods_of_service union all select person_id, actual_termination_date from per_periods_of_placement) hpp, per_people_f per, per_assignment_status_types ast, pa_resource_txn_attributes txn, pa_all_organizations paorg, pa_resources res, per_jobs pj, per_addresses add1, fnd_territories_vl fnd where per.person_id = assign.person_id and assign.assignment_type in ('E', 'C') and assign.job_id is not null and assign.primary_flag = 'Y' and (per.employee_number is not null OR per.npw_number is not NULL) and sysdate between per.effective_start_date and per.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 hpp.person_id = per.person_id and nvl(hpp.actual_termination_date, trunc(sysdate)) >= trunc(sysdate) and add1.person_id = hpp.person_id and add1.primary_flag = 'Y' and add1.country = fnd.territory_code and assign.organization_id is not null and assign.organization_id = b1.organization_id and b1.org_information_context = 'Exp Organization Defaults' and b1.organization_id = paorg.organization_id and (paorg.organization_id,paorg.org_id) = ( select h1.organization_id, h1.org_id from pa_all_organizations h1 where h1.pa_org_use_type = 'EXPENDITURES' and h1.inactive_date is null and h1.organization_id = assign.organization_id and rownum = 1 ) and paorg.pa_org_use_type = 'EXPENDITURES' and paorg.inactive_date is null and per.person_id = txn.person_id and txn.resource_id = res.resource_id and pj.job_id = assign.job_id and pj.job_group_id is not null

View Text - HTML Formatted

SELECT PER.PERSON_ID RESOURCE_SOURCE_ID
, RES.RESOURCE_ID
, PER.FULL_NAME RESOURCE_NAME
, (SELECT RESOURCE_TYPE_CODE
FROM PA_RESOURCE_TYPES
WHERE RESOURCE_TYPE_ID(+) = RES.RESOURCE_TYPE_ID ) RESOURCE_TYPE
, RES.RESOURCE_TYPE_ID RESOURCE_TYPE_ID
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PER.EMPLOYEE_NUMBER
, PER.NPW_NUMBER) RESOURCE_NUMBER
, ASSIGN.JOB_ID RESOURCE_JOB_ID
, PJ.NAME RESOURCE_JOB_NAME
, PA_HR_UPDATE_API.GET_JOB_LEVEL(ASSIGN.JOB_ID
, PJ.JOB_GROUP_ID) RESOURCE_JOB_LEVEL
, ASSIGN.ORGANIZATION_ID RESOURCE_ORGANIZATION_ID
, PA_RESOURCE_UTILS.GET_ORGANIZATION_NAME(ASSIGN.ORGANIZATION_ID) RESOURCE_ORGANIZATION_NAME
, ADD1.COUNTRY RESOURCE_COUNTRY_CODE
, FND.TERRITORY_SHORT_NAME RESOURCE_COUNTRY_NAME
, ASSIGN.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASSIGN.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, ADD1.DATE_FROM ADDRESS_DATE_FROM
, ADD1.DATE_TO ADDRESS_DATE_TO
, NULL GRADE_DATE_FROM
, NULL GRADE_DATE_TO
FROM PER_ASSIGNMENTS_F ASSIGN
, HR_ORGANIZATION_INFORMATION B1
, (SELECT PERSON_ID
, ACTUAL_TERMINATION_DATE
FROM PER_PERIODS_OF_SERVICE UNION ALL SELECT PERSON_ID
, ACTUAL_TERMINATION_DATE
FROM PER_PERIODS_OF_PLACEMENT) HPP
, PER_PEOPLE_F PER
, PER_ASSIGNMENT_STATUS_TYPES AST
, PA_RESOURCE_TXN_ATTRIBUTES TXN
, PA_ALL_ORGANIZATIONS PAORG
, PA_RESOURCES RES
, PER_JOBS PJ
, PER_ADDRESSES ADD1
, FND_TERRITORIES_VL FND
WHERE PER.PERSON_ID = ASSIGN.PERSON_ID
AND ASSIGN.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND ASSIGN.JOB_ID IS NOT NULL
AND ASSIGN.PRIMARY_FLAG = 'Y'
AND (PER.EMPLOYEE_NUMBER IS NOT NULL OR PER.NPW_NUMBER IS NOT NULL)
AND SYSDATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.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 HPP.PERSON_ID = PER.PERSON_ID
AND NVL(HPP.ACTUAL_TERMINATION_DATE
, TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND ADD1.PERSON_ID = HPP.PERSON_ID
AND ADD1.PRIMARY_FLAG = 'Y'
AND ADD1.COUNTRY = FND.TERRITORY_CODE
AND ASSIGN.ORGANIZATION_ID IS NOT NULL
AND ASSIGN.ORGANIZATION_ID = B1.ORGANIZATION_ID
AND B1.ORG_INFORMATION_CONTEXT = 'EXP ORGANIZATION DEFAULTS'
AND B1.ORGANIZATION_ID = PAORG.ORGANIZATION_ID
AND (PAORG.ORGANIZATION_ID
, PAORG.ORG_ID) = ( SELECT H1.ORGANIZATION_ID
, H1.ORG_ID
FROM PA_ALL_ORGANIZATIONS H1
WHERE H1.PA_ORG_USE_TYPE = 'EXPENDITURES'
AND H1.INACTIVE_DATE IS NULL
AND H1.ORGANIZATION_ID = ASSIGN.ORGANIZATION_ID
AND ROWNUM = 1 )
AND PAORG.PA_ORG_USE_TYPE = 'EXPENDITURES'
AND PAORG.INACTIVE_DATE IS NULL
AND PER.PERSON_ID = TXN.PERSON_ID
AND TXN.RESOURCE_ID = RES.RESOURCE_ID
AND PJ.JOB_ID = ASSIGN.JOB_ID
AND PJ.JOB_GROUP_ID IS NOT NULL