VIEW: APPS.AHL_PP_EMPLOYEE_ASSIGN_V

Source

View Text - Preformatted

SELECT AWAS.ASSIGNMENT_ID, AWAS.OBJECT_VERSION_NUMBER, AWAS.EMPLOYEE_ID, PEPF.EMPLOYEE_NUMBER, PEPF.FULL_NAME EMPLOYEE_NAME, AWOS.WORKORDER_ID JOB_ID, AWOS.JOB_NUMBER JOB_NUMBER, AWOS.JOB_DESCRIPTION JOB_DESCRIPTION, AWOS.JOB_STATUS_CODE, AWOS.JOB_STATUS_MEANING JOB_STATUS_NAME, AWOP.WORKORDER_OPERATION_ID OPERATION_ID, AWOP.OPERATION_SEQUENCE_NUM OPERATION_SEQUENCE, AWOP.SCHEDULED_START_DATE OPERATION_START_DATE, AWOP.SCHEDULED_END_DATE OPERATION_END_DATE, ADS.SHIFT_NUM SHIFT_NUMBER, AWAS.ASSIGN_START_DATE ASSIGNMENT_START_DATE, AWAS.ASSIGN_END_DATE ASSIGNMENT_END_DATE, AWOS.ORGANIZATION_ID ORGANIZATION_ID, AWOS.ORGANIZATION_NAME, AWOS.DEPARTMENT_ID DEPARTMENT_ID, AWOS.DEPARTMENT_NAME, AWOS.VISIT_NUMBER, AWOS.VISIT_ID, AWOS.PRIORITY JOB_PRIORITY_CODE, AWOS.PRIORITY_MEANING JOB_PRIORITY_NAME, NULL, AWOS.OBJECT_VERSION_NUMBER JOB_OBJECT_VERSION_NUMBER , AWOS.COLLECTION_ID WO_COLLECTION_ID, AWOS.PLAN_ID WO_PLAN_ID, AWOS.ITEM_INSTANCE_ID, AWOP.OBJECT_VERSION_NUMBER OPER_OBJECT_VERSION_NUMBER, AWOP.STATUS_CODE OPERATION_STATUS_CODE, AWOP.STATUS OPERATION_STATUS_MEANING, AWOP.OPERATION_CODE, AWOS.WIP_ENTITY_ID, AWOS.VISIT_STATUS_CODE, AWOS.INCIDENT_ID SERVICE_REQUEST_ID, AWOP.PLAN_ID OPER_PLAN_ID, AWOP.COLLECTION_ID OPER_COLLECTION_ID, AWOS.ROUTE_ID, AWOS.UNIT_EFFECTIVITY_ID, AWOS.UE_OBJECT_VERSION_NUMBER, AWOS.UE_PLAN_ID, AWOS.UE_COLLECTION_ID, AWOS.UE_STATUS_CODE, AOPR.RESOURCE_ID, BOMR.RESOURCE_TYPE, MFGL.MEANING, BOMR.RESOURCE_CODE, BOMR.DESCRIPTION, AOPR.RESOURCE_SEQUENCE_NUM, AWOS.ACTUAL_START_DATE, AWOS.ACTUAL_START_HR, AWOS.ACTUAL_START_MI, AWOS.ACTUAL_END_DATE, AWOS.ACTUAL_END_HR, AWOS.ACTUAL_END_MI, AWOS.SCHEDULED_START_DATE, AWOS.SCHEDULED_START_HR, AWOS.SCHEDULED_START_MI, AWOS.SCHEDULED_END_DATE, AWOS.SCHEDULED_END_HR, AWOS.SCHEDULED_END_MI, AHL_PRD_RESOURCE_TRANX_PVT.Get_Transacted_Hours(AWOS.WIP_ENTITY_ID, AWOP.OPERATION_SEQUENCE_NUM, AOPR.RESOURCE_SEQUENCE_NUM,AWAS.EMPLOYEE_ID), AHL_PRD_UTIL_PKG.ISDELASG_ENABLED(AWAS.ASSIGNMENT_ID, AWOS.WORKORDER_ID), AHL_PRD_UTIL_PKG.IS_UNIT_LOCKED(AWOS.WORKORDER_ID, NULL, NULL, NULL) FROM AHL_WORK_ASSIGNMENTS AWAS, AHL_WORKORDERS_V AWOS, AHL_WORKORDER_OPERATIONS_V AWOP, AHL_OPERATION_RESOURCES AOPR, BOM_RESOURCES BOMR, MFG_LOOKUPS MFGL, AHL_DEPARTMENT_SHIFTS ADS, PER_PEOPLE_F PEPF, PER_PERSON_TYPES PEPT WHERE AWAS.OPERATION_RESOURCE_ID = AOPR.OPERATION_RESOURCE_ID AND AWOP.WORKORDER_OPERATION_ID = AOPR.WORKORDER_OPERATION_ID AND AWOP.WORKORDER_ID = AWOS.WORKORDER_ID AND AWOS.DEPARTMENT_ID = ADS.DEPARTMENT_ID (+) AND AWAS.EMPLOYEE_ID = PEPF.PERSON_ID AND PEPT.PERSON_TYPE_ID = PEPF.PERSON_TYPE_ID AND SYSTEM_PERSON_TYPE ='EMP' AND AOPR.RESOURCE_ID = BOMR.RESOURCE_ID AND MFGL.LOOKUP_CODE(+) = BOMR.RESOURCE_TYPE AND MFGL.LOOKUP_TYPE(+) = 'BOM_RESOURCE_TYPE'

View Text - HTML Formatted

SELECT AWAS.ASSIGNMENT_ID
, AWAS.OBJECT_VERSION_NUMBER
, AWAS.EMPLOYEE_ID
, PEPF.EMPLOYEE_NUMBER
, PEPF.FULL_NAME EMPLOYEE_NAME
, AWOS.WORKORDER_ID JOB_ID
, AWOS.JOB_NUMBER JOB_NUMBER
, AWOS.JOB_DESCRIPTION JOB_DESCRIPTION
, AWOS.JOB_STATUS_CODE
, AWOS.JOB_STATUS_MEANING JOB_STATUS_NAME
, AWOP.WORKORDER_OPERATION_ID OPERATION_ID
, AWOP.OPERATION_SEQUENCE_NUM OPERATION_SEQUENCE
, AWOP.SCHEDULED_START_DATE OPERATION_START_DATE
, AWOP.SCHEDULED_END_DATE OPERATION_END_DATE
, ADS.SHIFT_NUM SHIFT_NUMBER
, AWAS.ASSIGN_START_DATE ASSIGNMENT_START_DATE
, AWAS.ASSIGN_END_DATE ASSIGNMENT_END_DATE
, AWOS.ORGANIZATION_ID ORGANIZATION_ID
, AWOS.ORGANIZATION_NAME
, AWOS.DEPARTMENT_ID DEPARTMENT_ID
, AWOS.DEPARTMENT_NAME
, AWOS.VISIT_NUMBER
, AWOS.VISIT_ID
, AWOS.PRIORITY JOB_PRIORITY_CODE
, AWOS.PRIORITY_MEANING JOB_PRIORITY_NAME
, NULL
, AWOS.OBJECT_VERSION_NUMBER JOB_OBJECT_VERSION_NUMBER
, AWOS.COLLECTION_ID WO_COLLECTION_ID
, AWOS.PLAN_ID WO_PLAN_ID
, AWOS.ITEM_INSTANCE_ID
, AWOP.OBJECT_VERSION_NUMBER OPER_OBJECT_VERSION_NUMBER
, AWOP.STATUS_CODE OPERATION_STATUS_CODE
, AWOP.STATUS OPERATION_STATUS_MEANING
, AWOP.OPERATION_CODE
, AWOS.WIP_ENTITY_ID
, AWOS.VISIT_STATUS_CODE
, AWOS.INCIDENT_ID SERVICE_REQUEST_ID
, AWOP.PLAN_ID OPER_PLAN_ID
, AWOP.COLLECTION_ID OPER_COLLECTION_ID
, AWOS.ROUTE_ID
, AWOS.UNIT_EFFECTIVITY_ID
, AWOS.UE_OBJECT_VERSION_NUMBER
, AWOS.UE_PLAN_ID
, AWOS.UE_COLLECTION_ID
, AWOS.UE_STATUS_CODE
, AOPR.RESOURCE_ID
, BOMR.RESOURCE_TYPE
, MFGL.MEANING
, BOMR.RESOURCE_CODE
, BOMR.DESCRIPTION
, AOPR.RESOURCE_SEQUENCE_NUM
, AWOS.ACTUAL_START_DATE
, AWOS.ACTUAL_START_HR
, AWOS.ACTUAL_START_MI
, AWOS.ACTUAL_END_DATE
, AWOS.ACTUAL_END_HR
, AWOS.ACTUAL_END_MI
, AWOS.SCHEDULED_START_DATE
, AWOS.SCHEDULED_START_HR
, AWOS.SCHEDULED_START_MI
, AWOS.SCHEDULED_END_DATE
, AWOS.SCHEDULED_END_HR
, AWOS.SCHEDULED_END_MI
, AHL_PRD_RESOURCE_TRANX_PVT.GET_TRANSACTED_HOURS(AWOS.WIP_ENTITY_ID
, AWOP.OPERATION_SEQUENCE_NUM
, AOPR.RESOURCE_SEQUENCE_NUM
, AWAS.EMPLOYEE_ID)
, AHL_PRD_UTIL_PKG.ISDELASG_ENABLED(AWAS.ASSIGNMENT_ID
, AWOS.WORKORDER_ID)
, AHL_PRD_UTIL_PKG.IS_UNIT_LOCKED(AWOS.WORKORDER_ID
, NULL
, NULL
, NULL)
FROM AHL_WORK_ASSIGNMENTS AWAS
, AHL_WORKORDERS_V AWOS
, AHL_WORKORDER_OPERATIONS_V AWOP
, AHL_OPERATION_RESOURCES AOPR
, BOM_RESOURCES BOMR
, MFG_LOOKUPS MFGL
, AHL_DEPARTMENT_SHIFTS ADS
, PER_PEOPLE_F PEPF
, PER_PERSON_TYPES PEPT
WHERE AWAS.OPERATION_RESOURCE_ID = AOPR.OPERATION_RESOURCE_ID
AND AWOP.WORKORDER_OPERATION_ID = AOPR.WORKORDER_OPERATION_ID
AND AWOP.WORKORDER_ID = AWOS.WORKORDER_ID
AND AWOS.DEPARTMENT_ID = ADS.DEPARTMENT_ID (+)
AND AWAS.EMPLOYEE_ID = PEPF.PERSON_ID
AND PEPT.PERSON_TYPE_ID = PEPF.PERSON_TYPE_ID
AND SYSTEM_PERSON_TYPE ='EMP'
AND AOPR.RESOURCE_ID = BOMR.RESOURCE_ID
AND MFGL.LOOKUP_CODE(+) = BOMR.RESOURCE_TYPE
AND MFGL.LOOKUP_TYPE(+) = 'BOM_RESOURCE_TYPE'