VIEW: APPS.PA_R_PROJECT_RESOURCES_IND_T_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, decode( ptype.system_person_type, 'EX_EMP' , 'EMP' , 'EX_CWK', 'CWK') p_type, ptype.user_person_type user_type, asgn.location_id, per.effective_start_date per_start_date, per.effective_end_date per_end_date, decode( ptype.system_person_type, 'EX_EMP', per.employee_number, 'EX_CWK', 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 FROM per_all_assignments_f asgn, pa_resources par, pa_resource_txn_attributes parta, (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 ptype, per_person_type_usages_f ptu, per_assignment_status_types status, per_jobs job WHERE asgn.person_id = per.person_id AND per.person_id = parta.person_id AND parta.resource_id = par.resource_id AND nvl(par.future_term_wf_flag, 'N') = 'Y' AND asgn.assignment_status_type_id = status.assignment_status_type_id AND status.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 = ptype.person_type_id AND ptype.system_person_type IN('EX_EMP', 'EX_CWK') AND(nvl(per.current_employee_flag, 'N') <> 'Y' AND nvl(per.current_npw_flag, 'N') <> 'Y') AND asgn.primary_flag = 'Y' AND asgn.person_id = po.person_id AND nvl(po.actual_termination_date, TRUNC(sysdate)) >= TRUNC(sysdate) AND(per.employee_number IS NOT NULL OR per.npw_number IS NOT NULL) AND asgn.assignment_type NOT IN('E', 'C') AND asgn.job_id IS NOT NULL AND asgn.job_id = job.job_id AND asgn.effective_start_date BETWEEN per.effective_start_date AND per.effective_end_date

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
, DECODE( PTYPE.SYSTEM_PERSON_TYPE
, 'EX_EMP'
, 'EMP'
, 'EX_CWK'
, 'CWK') P_TYPE
, PTYPE.USER_PERSON_TYPE USER_TYPE
, ASGN.LOCATION_ID
, PER.EFFECTIVE_START_DATE PER_START_DATE
, PER.EFFECTIVE_END_DATE PER_END_DATE
, DECODE( PTYPE.SYSTEM_PERSON_TYPE
, 'EX_EMP'
, PER.EMPLOYEE_NUMBER
, 'EX_CWK'
, 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
FROM PER_ALL_ASSIGNMENTS_F ASGN
, PA_RESOURCES PAR
, PA_RESOURCE_TXN_ATTRIBUTES PARTA
, (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 PTYPE
, PER_PERSON_TYPE_USAGES_F PTU
, PER_ASSIGNMENT_STATUS_TYPES STATUS
, PER_JOBS JOB
WHERE ASGN.PERSON_ID = PER.PERSON_ID
AND PER.PERSON_ID = PARTA.PERSON_ID
AND PARTA.RESOURCE_ID = PAR.RESOURCE_ID
AND NVL(PAR.FUTURE_TERM_WF_FLAG
, 'N') = 'Y'
AND ASGN.ASSIGNMENT_STATUS_TYPE_ID = STATUS.ASSIGNMENT_STATUS_TYPE_ID
AND STATUS.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 = PTYPE.PERSON_TYPE_ID
AND PTYPE.SYSTEM_PERSON_TYPE IN('EX_EMP'
, 'EX_CWK') AND(NVL(PER.CURRENT_EMPLOYEE_FLAG
, 'N') <> 'Y'
AND NVL(PER.CURRENT_NPW_FLAG
, 'N') <> 'Y')
AND ASGN.PRIMARY_FLAG = 'Y'
AND ASGN.PERSON_ID = PO.PERSON_ID
AND NVL(PO.ACTUAL_TERMINATION_DATE
, TRUNC(SYSDATE)) >= TRUNC(SYSDATE) AND(PER.EMPLOYEE_NUMBER IS NOT NULL OR PER.NPW_NUMBER IS NOT NULL)
AND ASGN.ASSIGNMENT_TYPE NOT IN('E'
, 'C')
AND ASGN.JOB_ID IS NOT NULL
AND ASGN.JOB_ID = JOB.JOB_ID
AND ASGN.EFFECTIVE_START_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE