VIEW: APPS.AHL_PP_ASSIGNMENT_V

Source

View Text - Preformatted

SELECT AWAS.ASSIGNMENT_ID, AWAS.OBJECT_VERSION_NUMBER, AWAS.EMPLOYEE_ID, PEPF.EMPLOYEE_NUMBER, PEPF.FULL_NAME EMPLOYEE_NAME, APRV.JOB_ID JOB_ID, APRV.OPERATION_ID OPERATION_ID, APRV.OPERATION_SEQUENCE OPERATION_SEQUENCE, APRV.OPERATION_STATUS_CODE OPERATION_STATUS_CODE, APRV.STATUS OPERATION_STATUS_MEAN, APRV.OPERATION_START_DATE OPERATION_START_DATE, APRV.OPERATION_END_DATE OPERATION_END_DATE, AWAS.ASSIGN_START_DATE ASSIGNMENT_START_DATE, TO_CHAR (AWAS.ASSIGN_START_DATE, 'HH24') ASSIGN_START_HOUR, TO_CHAR (AWAS.ASSIGN_START_DATE, 'MI') ASSIGN_START_MIN, AWAS.ASSIGN_END_DATE ASSIGNMENT_END_DATE, TO_CHAR (AWAS.ASSIGN_END_DATE, 'HH24') ASSIGN_END_HOUR, TO_CHAR (AWAS.ASSIGN_END_DATE, 'MI') ASSIGN_END_MIN, AWAS.INSTANCE_ID INSTANCE_ID, AWAS.SERIAL_NUMBER SERIAL_NUMBER, APRV.RESOURCE_TYPE_CODE RESOURCE_TYPE_CODE, APRV.RESOURCE_TYPE_NAME RESOURCE_TYPE_NAME, APRV.RESOURCE_ID RESOURCE_ID, APRV.REQUIREMENT_ID OPERATION_RESOURCE_ID, APRV.RESOURCE_CODE RESOURCE_CODE, APRV.RESOURCE_NAME RESOURCE_NAME, APRV.RESOURCE_SEQUENCE RESOURCE_SEQUENCE, AWAS.SELF_ASSIGNED_FLAG FROM AHL_WORK_ASSIGNMENTS AWAS, AHL_PP_REQUIREMENT_V APRV, PER_PEOPLE_F PEPF, PER_PERSON_TYPES PEPT WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID AND AWAS.EMPLOYEE_ID = PEPF.PERSON_ID AND PEPT.PERSON_TYPE_ID = PEPF.PERSON_TYPE_ID AND PEPT.SYSTEM_PERSON_TYPE = 'EMP' AND AWAS.ASSIGN_START_DATE between PEPF.EFFECTIVE_START_DATE AND PEPF.EFFECTIVE_END_DATE AND AWAS.ASSIGN_END_DATE between PEPF.EFFECTIVE_START_DATE AND PEPF.EFFECTIVE_END_DATE union SELECT AWAS.ASSIGNMENT_ID, AWAS.OBJECT_VERSION_NUMBER, AWAS.EMPLOYEE_ID, NULL, NULL, APRV.JOB_ID JOB_ID, APRV.OPERATION_ID OPERATION_ID, APRV.OPERATION_SEQUENCE OPERATION_SEQUENCE, APRV.OPERATION_STATUS_CODE OPERATION_STATUS_CODE, APRV.STATUS OPERATION_STATUS_MEAN, APRV.OPERATION_START_DATE OPERATION_START_DATE, APRV.OPERATION_END_DATE OPERATION_END_DATE, AWAS.ASSIGN_START_DATE ASSIGNMENT_START_DATE, TO_CHAR (AWAS.ASSIGN_START_DATE, 'HH24') ASSIGN_START_HOUR, TO_CHAR (AWAS.ASSIGN_START_DATE, 'MI') ASSIGN_START_MIN, AWAS.ASSIGN_END_DATE ASSIGNMENT_END_DATE, TO_CHAR (AWAS.ASSIGN_END_DATE, 'HH24') ASSIGN_END_HOUR, TO_CHAR (AWAS.ASSIGN_END_DATE, 'MI') ASSIGN_END_MIN, AWAS.INSTANCE_ID INSTANCE_ID, AWAS.SERIAL_NUMBER SERIAL_NUMBER, APRV.RESOURCE_TYPE_CODE RESOURCE_TYPE_CODE, APRV.RESOURCE_TYPE_NAME RESOURCE_TYPE_NAME, APRV.RESOURCE_ID RESOURCE_ID, APRV.REQUIREMENT_ID OPERATION_RESOURCE_ID, APRV.RESOURCE_CODE RESOURCE_CODE, APRV.RESOURCE_NAME RESOURCE_NAME, APRV.RESOURCE_SEQUENCE RESOURCE_SEQUENCE, AWAS.SELF_ASSIGNED_FLAG FROM AHL_WORK_ASSIGNMENTS AWAS, AHL_PP_REQUIREMENT_V APRV WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID AND AWAS.SERIAL_NUMBER IS NOT NULL

View Text - HTML Formatted

SELECT AWAS.ASSIGNMENT_ID
, AWAS.OBJECT_VERSION_NUMBER
, AWAS.EMPLOYEE_ID
, PEPF.EMPLOYEE_NUMBER
, PEPF.FULL_NAME EMPLOYEE_NAME
, APRV.JOB_ID JOB_ID
, APRV.OPERATION_ID OPERATION_ID
, APRV.OPERATION_SEQUENCE OPERATION_SEQUENCE
, APRV.OPERATION_STATUS_CODE OPERATION_STATUS_CODE
, APRV.STATUS OPERATION_STATUS_MEAN
, APRV.OPERATION_START_DATE OPERATION_START_DATE
, APRV.OPERATION_END_DATE OPERATION_END_DATE
, AWAS.ASSIGN_START_DATE ASSIGNMENT_START_DATE
, TO_CHAR (AWAS.ASSIGN_START_DATE
, 'HH24') ASSIGN_START_HOUR
, TO_CHAR (AWAS.ASSIGN_START_DATE
, 'MI') ASSIGN_START_MIN
, AWAS.ASSIGN_END_DATE ASSIGNMENT_END_DATE
, TO_CHAR (AWAS.ASSIGN_END_DATE
, 'HH24') ASSIGN_END_HOUR
, TO_CHAR (AWAS.ASSIGN_END_DATE
, 'MI') ASSIGN_END_MIN
, AWAS.INSTANCE_ID INSTANCE_ID
, AWAS.SERIAL_NUMBER SERIAL_NUMBER
, APRV.RESOURCE_TYPE_CODE RESOURCE_TYPE_CODE
, APRV.RESOURCE_TYPE_NAME RESOURCE_TYPE_NAME
, APRV.RESOURCE_ID RESOURCE_ID
, APRV.REQUIREMENT_ID OPERATION_RESOURCE_ID
, APRV.RESOURCE_CODE RESOURCE_CODE
, APRV.RESOURCE_NAME RESOURCE_NAME
, APRV.RESOURCE_SEQUENCE RESOURCE_SEQUENCE
, AWAS.SELF_ASSIGNED_FLAG
FROM AHL_WORK_ASSIGNMENTS AWAS
, AHL_PP_REQUIREMENT_V APRV
, PER_PEOPLE_F PEPF
, PER_PERSON_TYPES PEPT
WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID
AND AWAS.EMPLOYEE_ID = PEPF.PERSON_ID
AND PEPT.PERSON_TYPE_ID = PEPF.PERSON_TYPE_ID
AND PEPT.SYSTEM_PERSON_TYPE = 'EMP'
AND AWAS.ASSIGN_START_DATE BETWEEN PEPF.EFFECTIVE_START_DATE
AND PEPF.EFFECTIVE_END_DATE
AND AWAS.ASSIGN_END_DATE BETWEEN PEPF.EFFECTIVE_START_DATE
AND PEPF.EFFECTIVE_END_DATE UNION SELECT AWAS.ASSIGNMENT_ID
, AWAS.OBJECT_VERSION_NUMBER
, AWAS.EMPLOYEE_ID
, NULL
, NULL
, APRV.JOB_ID JOB_ID
, APRV.OPERATION_ID OPERATION_ID
, APRV.OPERATION_SEQUENCE OPERATION_SEQUENCE
, APRV.OPERATION_STATUS_CODE OPERATION_STATUS_CODE
, APRV.STATUS OPERATION_STATUS_MEAN
, APRV.OPERATION_START_DATE OPERATION_START_DATE
, APRV.OPERATION_END_DATE OPERATION_END_DATE
, AWAS.ASSIGN_START_DATE ASSIGNMENT_START_DATE
, TO_CHAR (AWAS.ASSIGN_START_DATE
, 'HH24') ASSIGN_START_HOUR
, TO_CHAR (AWAS.ASSIGN_START_DATE
, 'MI') ASSIGN_START_MIN
, AWAS.ASSIGN_END_DATE ASSIGNMENT_END_DATE
, TO_CHAR (AWAS.ASSIGN_END_DATE
, 'HH24') ASSIGN_END_HOUR
, TO_CHAR (AWAS.ASSIGN_END_DATE
, 'MI') ASSIGN_END_MIN
, AWAS.INSTANCE_ID INSTANCE_ID
, AWAS.SERIAL_NUMBER SERIAL_NUMBER
, APRV.RESOURCE_TYPE_CODE RESOURCE_TYPE_CODE
, APRV.RESOURCE_TYPE_NAME RESOURCE_TYPE_NAME
, APRV.RESOURCE_ID RESOURCE_ID
, APRV.REQUIREMENT_ID OPERATION_RESOURCE_ID
, APRV.RESOURCE_CODE RESOURCE_CODE
, APRV.RESOURCE_NAME RESOURCE_NAME
, APRV.RESOURCE_SEQUENCE RESOURCE_SEQUENCE
, AWAS.SELF_ASSIGNED_FLAG
FROM AHL_WORK_ASSIGNMENTS AWAS
, AHL_PP_REQUIREMENT_V APRV
WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID
AND AWAS.SERIAL_NUMBER IS NOT NULL