VIEW: APPS.HXC_TCD_DETAILS_V

Source

View Text - Preformatted

SELECT distinct emp.person_id person_id, nvl(emp.employee_number,emp.npw_number) person_number, emp.full_name person_name, emp.effective_start_date effective_start_date, emp.effective_end_date effective_end_date, emp_asgn.supervisor_id supervisor_id, (SELECT DISTINCT full_name FROM per_all_people_f WHERE person_id = emp_asgn.supervisor_id AND trunc(effective_start_date) <= trunc(sysdate) AND trunc(effective_end_date) >= trunc(sysdate)) supervisor_name, emp_asgn.organization_id organization_id, (SELECT distinct NAME FROM hr_all_organization_units WHERE organization_id = emp_asgn.organization_id) ORGANIZATION, emp_asgn.location_id location_id, (SELECT distinct location_code FROM hr_locations_all WHERE location_id = emp_asgn.location_id) location, (SELECT distinct payroll_name FROM pay_all_payrolls_f WHERE payroll_id = emp_asgn.payroll_id AND trunc(effective_start_date) <= trunc(SYSDATE) AND trunc(effective_end_date) >= trunc(SYSDATE) ) payroll FROM per_all_people_f emp, per_all_assignments_f emp_asgn WHERE emp.person_id = emp_asgn.person_id AND emp_asgn.primary_flag = 'Y' AND emp_asgn.assignment_type IN ('E', 'C') AND trunc(emp.effective_start_date) <= trunc(SYSDATE) AND trunc(emp.effective_end_date) >= trunc(SYSDATE) AND trunc(emp_asgn.effective_start_date) <= trunc(SYSDATE) AND trunc(emp_asgn.effective_end_date) >= trunc(SYSDATE)

View Text - HTML Formatted

SELECT DISTINCT EMP.PERSON_ID PERSON_ID
, NVL(EMP.EMPLOYEE_NUMBER
, EMP.NPW_NUMBER) PERSON_NUMBER
, EMP.FULL_NAME PERSON_NAME
, EMP.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, EMP.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, EMP_ASGN.SUPERVISOR_ID SUPERVISOR_ID
, (SELECT DISTINCT FULL_NAME
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = EMP_ASGN.SUPERVISOR_ID
AND TRUNC(EFFECTIVE_START_DATE) <= TRUNC(SYSDATE)
AND TRUNC(EFFECTIVE_END_DATE) >= TRUNC(SYSDATE)) SUPERVISOR_NAME
, EMP_ASGN.ORGANIZATION_ID ORGANIZATION_ID
, (SELECT DISTINCT NAME
FROM HR_ALL_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = EMP_ASGN.ORGANIZATION_ID) ORGANIZATION
, EMP_ASGN.LOCATION_ID LOCATION_ID
, (SELECT DISTINCT LOCATION_CODE
FROM HR_LOCATIONS_ALL
WHERE LOCATION_ID = EMP_ASGN.LOCATION_ID) LOCATION
, (SELECT DISTINCT PAYROLL_NAME
FROM PAY_ALL_PAYROLLS_F
WHERE PAYROLL_ID = EMP_ASGN.PAYROLL_ID
AND TRUNC(EFFECTIVE_START_DATE) <= TRUNC(SYSDATE)
AND TRUNC(EFFECTIVE_END_DATE) >= TRUNC(SYSDATE) ) PAYROLL
FROM PER_ALL_PEOPLE_F EMP
, PER_ALL_ASSIGNMENTS_F EMP_ASGN
WHERE EMP.PERSON_ID = EMP_ASGN.PERSON_ID
AND EMP_ASGN.PRIMARY_FLAG = 'Y'
AND EMP_ASGN.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND TRUNC(EMP.EFFECTIVE_START_DATE) <= TRUNC(SYSDATE)
AND TRUNC(EMP.EFFECTIVE_END_DATE) >= TRUNC(SYSDATE)
AND TRUNC(EMP_ASGN.EFFECTIVE_START_DATE) <= TRUNC(SYSDATE)
AND TRUNC(EMP_ASGN.EFFECTIVE_END_DATE) >= TRUNC(SYSDATE)