VIEW: APPS.PA_R_PROJECT_RESOURCES_V

Source

View Text - Preformatted

SELECT asgn.person_id person_id , SUBSTR(per.full_name,1,60) name , asgn.organization_id organization_id , asgn.effective_start_date assignment_start_date , asgn.effective_end_date assignment_end_date , sysdate start_date , po.actual_termination_date termination_date , to_number(org_info.org_information1) default_OU , to_number(org_info_c.org_information1) calendar_id , person_types.system_person_type p_type , person_types.user_person_type user_type , decode(txn.person_id, NULL, 'NOT EXISTS', 'EXISTS') res_exists , per.effective_start_date per_start_date , per.effective_end_date per_end_date , decode(per.current_employee_flag, 'Y', per.employee_number, per.npw_number) per_emp_number , per.email_address per_email , per.work_telephone per_work_phone , per.business_group_id per_business_group_id , per.first_name per_first_name , per.last_name per_last_name , per.middle_names per_middle_name , job.name job_name , asgn.supervisor_id supervisor_id , pa_expenditures_utils.getorgtlname(per.business_group_id) org_name , 'EMPLOYEE' resource_type , asgn.job_id , job.job_group_id job_group_id , asgn.location_id from per_all_assignments_f asgn , hr_organization_information org_info , hr_organization_information org_info_c , (select person_id, actual_termination_date from per_periods_of_service union all select person_id, actual_termination_date from per_periods_of_placement) po , per_all_people_f per , per_person_types person_types , per_person_type_usages_f ptu , per_assignment_status_types status_types , pa_resource_txn_attributes txn , pa_all_organizations org , per_jobs job , per_job_extra_info job_info WHERE asgn.person_id = per.person_id and asgn.assignment_status_type_id = status_types.assignment_status_type_id and status_types.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') and per.person_id = ptu.person_id and asgn.effective_start_date between ptu.effective_start_date and ptu.effective_end_date and ptu.person_type_id = person_types.person_type_id and person_types.system_person_type in ('EMP', 'CWK') and (per.current_employee_flag = 'Y' OR per.current_npw_flag = 'Y') and asgn.organization_id = org_info.organization_id and asgn.organization_id = org_info_c.organization_id (+) and org_info.org_information_context = 'Exp Organization Defaults' and org_info_c.org_information_context(+) = 'Resource Defaults' and asgn.primary_flag = 'Y' and asgn.person_id = po.person_id and nvl(po.actual_termination_date, trunc(sysdate)) >= trunc(sysdate) and per.person_id = txn.person_id(+) and asgn.job_id is not null and (per.employee_number is not null OR per.npw_number is not null) and asgn.assignment_type in ('E', 'C') and asgn.organization_id = org.organization_id and asgn.job_id = job.job_id and asgn.effective_start_date between per.effective_start_date and per.effective_end_date and job.job_id = job_info.job_id and job_info.jei_information_category = 'Job Category' and job_info.jei_information3 = 'Y' and org_info.organization_id = org.organization_id and org.pa_org_use_type = 'EXPENDITURES' and org.inactive_date is null and (org.organization_id,org.org_id) = ( select org1.organization_id, org1.org_id from pa_all_organizations org1 where org1.pa_org_use_type = 'EXPENDITURES' and org1.inactive_date is null and org1.organization_id = org.organization_id and rownum = 1 )

View Text - HTML Formatted

SELECT ASGN.PERSON_ID PERSON_ID
, SUBSTR(PER.FULL_NAME
, 1
, 60) NAME
, ASGN.ORGANIZATION_ID ORGANIZATION_ID
, ASGN.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASGN.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, SYSDATE START_DATE
, PO.ACTUAL_TERMINATION_DATE TERMINATION_DATE
, TO_NUMBER(ORG_INFO.ORG_INFORMATION1) DEFAULT_OU
, TO_NUMBER(ORG_INFO_C.ORG_INFORMATION1) CALENDAR_ID
, PERSON_TYPES.SYSTEM_PERSON_TYPE P_TYPE
, PERSON_TYPES.USER_PERSON_TYPE USER_TYPE
, DECODE(TXN.PERSON_ID
, NULL
, 'NOT EXISTS'
, 'EXISTS') RES_EXISTS
, PER.EFFECTIVE_START_DATE PER_START_DATE
, PER.EFFECTIVE_END_DATE PER_END_DATE
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PER.EMPLOYEE_NUMBER
, PER.NPW_NUMBER) PER_EMP_NUMBER
, PER.EMAIL_ADDRESS PER_EMAIL
, PER.WORK_TELEPHONE PER_WORK_PHONE
, PER.BUSINESS_GROUP_ID PER_BUSINESS_GROUP_ID
, PER.FIRST_NAME PER_FIRST_NAME
, PER.LAST_NAME PER_LAST_NAME
, PER.MIDDLE_NAMES PER_MIDDLE_NAME
, JOB.NAME JOB_NAME
, ASGN.SUPERVISOR_ID SUPERVISOR_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(PER.BUSINESS_GROUP_ID) ORG_NAME
, 'EMPLOYEE' RESOURCE_TYPE
, ASGN.JOB_ID
, JOB.JOB_GROUP_ID JOB_GROUP_ID
, ASGN.LOCATION_ID
FROM PER_ALL_ASSIGNMENTS_F ASGN
, HR_ORGANIZATION_INFORMATION ORG_INFO
, HR_ORGANIZATION_INFORMATION ORG_INFO_C
, (SELECT PERSON_ID
, ACTUAL_TERMINATION_DATE
FROM PER_PERIODS_OF_SERVICE UNION ALL SELECT PERSON_ID
, ACTUAL_TERMINATION_DATE
FROM PER_PERIODS_OF_PLACEMENT) PO
, PER_ALL_PEOPLE_F PER
, PER_PERSON_TYPES PERSON_TYPES
, PER_PERSON_TYPE_USAGES_F PTU
, PER_ASSIGNMENT_STATUS_TYPES STATUS_TYPES
, PA_RESOURCE_TXN_ATTRIBUTES TXN
, PA_ALL_ORGANIZATIONS ORG
, PER_JOBS JOB
, PER_JOB_EXTRA_INFO JOB_INFO
WHERE ASGN.PERSON_ID = PER.PERSON_ID
AND ASGN.ASSIGNMENT_STATUS_TYPE_ID = STATUS_TYPES.ASSIGNMENT_STATUS_TYPE_ID
AND STATUS_TYPES.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'ACTIVE_CWK')
AND PER.PERSON_ID = PTU.PERSON_ID
AND ASGN.EFFECTIVE_START_DATE BETWEEN PTU.EFFECTIVE_START_DATE
AND PTU.EFFECTIVE_END_DATE
AND PTU.PERSON_TYPE_ID = PERSON_TYPES.PERSON_TYPE_ID
AND PERSON_TYPES.SYSTEM_PERSON_TYPE IN ('EMP'
, 'CWK')
AND (PER.CURRENT_EMPLOYEE_FLAG = 'Y' OR PER.CURRENT_NPW_FLAG = 'Y')
AND ASGN.ORGANIZATION_ID = ORG_INFO.ORGANIZATION_ID
AND ASGN.ORGANIZATION_ID = ORG_INFO_C.ORGANIZATION_ID (+)
AND ORG_INFO.ORG_INFORMATION_CONTEXT = 'EXP ORGANIZATION DEFAULTS'
AND ORG_INFO_C.ORG_INFORMATION_CONTEXT(+) = 'RESOURCE DEFAULTS'
AND ASGN.PRIMARY_FLAG = 'Y'
AND ASGN.PERSON_ID = PO.PERSON_ID
AND NVL(PO.ACTUAL_TERMINATION_DATE
, TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND PER.PERSON_ID = TXN.PERSON_ID(+)
AND ASGN.JOB_ID IS NOT NULL
AND (PER.EMPLOYEE_NUMBER IS NOT NULL OR PER.NPW_NUMBER IS NOT NULL)
AND ASGN.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND ASGN.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ASGN.JOB_ID = JOB.JOB_ID
AND ASGN.EFFECTIVE_START_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND JOB.JOB_ID = JOB_INFO.JOB_ID
AND JOB_INFO.JEI_INFORMATION_CATEGORY = 'JOB CATEGORY'
AND JOB_INFO.JEI_INFORMATION3 = 'Y'
AND ORG_INFO.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ORG.PA_ORG_USE_TYPE = 'EXPENDITURES'
AND ORG.INACTIVE_DATE IS NULL
AND (ORG.ORGANIZATION_ID
, ORG.ORG_ID) = ( SELECT ORG1.ORGANIZATION_ID
, ORG1.ORG_ID
FROM PA_ALL_ORGANIZATIONS ORG1
WHERE ORG1.PA_ORG_USE_TYPE = 'EXPENDITURES'
AND ORG1.INACTIVE_DATE IS NULL
AND ORG1.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ROWNUM = 1 )