View: AHL_SEARCH_VISIT_TASK_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	This view stores the query to retrieve Visits task information by search visit tasks screen
Implementation/DBA Data:	APPS.AHL_SEARCH_VISIT_TASK_V

View Text

SELECT AVTS.VISIT_ID VISIT_ID
, AVTS.VISIT_NUMBER
, AVTS.TEMPLATE_FLAG
, AVTS.STATUS_CODE
, ATSK.VISIT_TASK_ID
, ATSK.VISIT_TASK_NUMBER
, ATSK.VISIT_TASK_NAME
, ATSK.INVENTORY_ITEM_ID
, MTSB.CONCATENATED_SEGMENTS
, ATSK.ITEM_ORGANIZATION_ID
, ATSK.INSTANCE_ID
, CSIS.SERIAL_NUMBER
, ATSK.MR_ROUTE_ID
, AMRH.TITLE
, AMRR.ROUTE_NUMBER
, AWO.WORKORDER_ID
, AWO.WORKORDER_NAME
, AWO.STATUS_CODE
, LKUP2.MEANING
, AWO.ACTUAL_START_DATE
, AWO.ACTUAL_END_DATE
, ATSK.SERVICE_REQUEST_ID
, SR.INCIDENT_NUMBER
, TO_DATE( NULL)
, TO_DATE(NULL)
, ATSK.TASK_TYPE_CODE
, LKUP1.MEANING
, AUEF.DUE_DATE
, ATSK.DEPARTMENT_ID
, BDPT.DESCRIPTION DEPARTMENT_NAME
, ATSK.ORIGINATING_TASK_ID
, ORIGTSK.VISIT_TASK_NUMBER ORIGINATING_TASK_NUMBER
, ORIGTSK.VISIT_TASK_NAME ORIGINATING_TASK_NAME
, ATSK.MR_ID
, ATSK.UNIT_EFFECTIVITY_ID
, AMRH.DESCRIPTION
FROM AHL_VISITS_VL AVTS
, AHL_VISIT_TASKS_VL ATSK
, AHL_VISIT_TASKS_VL ORIGTSK
, AHL_MR_ROUTES_V AMRR
, AHL_MR_HEADERS_VL AMRH
, AHL_UNIT_EFFECTIVITIES_VL AUEF
, CSI_ITEM_INSTANCES CSIS
, CS_INCIDENTS_ALL_B SR
, BOM_DEPARTMENTS BDPT
, FND_LOOKUP_VALUES_VL LKUP1
, FND_LOOKUP_VALUES_VL LKUP2
, MTL_SYSTEM_ITEMS_B_KFV MTSB
, AHL_WORKORDERS AWO
WHERE ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+)
AND ATSK.ORIGINATING_TASK_ID = ORIGTSK.VISIT_TASK_ID(+)
AND (ATSK.MR_ROUTE_ID = AMRR.MR_ROUTE_ID (+)
AND AMRR.MR_HEADER_ID= AMRH.MR_HEADER_ID (+))
AND ATSK.SERVICE_REQUEST_ID=SR.INCIDENT_ID (+)
AND ATSK.UNIT_EFFECTIVITY_ID=AUEF.UNIT_EFFECTIVITY_ID(+)
AND ATSK. INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID
AND ATSK. ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID
AND LKUP1.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_TYPE'
AND LKUP1.LOOKUP_CODE(+) = ATSK.TASK_TYPE_CODE
AND AVTS.VISIT_ID = ATSK.VISIT_ID
AND AVTS.TEMPLATE_FLAG = 'N'
AND ATSK.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
AND ATSK.VISIT_TASK_ID = AWO.VISIT_TASK_ID(+)
AND NVL(ATSK.STATUS_CODE
, 'X') <> 'DELETED'
AND ATSK.TASK_TYPE_CODE <> 'SUMMARY'
AND LKUP2.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS'
AND LKUP2.LOOKUP_CODE(+) = AWO.STATUS_CODE UNION SELECT AVTS.VISIT_ID VISIT_ID
, AVTS.VISIT_NUMBER
, AVTS.TEMPLATE_FLAG
, AVTS.STATUS_CODE
, ATSK.VISIT_TASK_ID
, ATSK.VISIT_TASK_NUMBER
, ATSK.VISIT_TASK_NAME
, ATSK.INVENTORY_ITEM_ID
, MTSB.CONCATENATED_SEGMENTS
, ATSK.ITEM_ORGANIZATION_ID
, ATSK.INSTANCE_ID
, CSIS.SERIAL_NUMBER
, ATSK.MR_ROUTE_ID
, AMRH.TITLE
, TO_CHAR(NULL)
, AWO.WORKORDER_ID
, AWO.WORKORDER_NAME
, AWO.STATUS_CODE
, LKUP2.MEANING
, AWO.ACTUAL_START_DATE
, AWO.ACTUAL_END_DATE
, ATSK.SERVICE_REQUEST_ID
, SR.INCIDENT_NUMBER
, TO_DATE(NULL)
, TO_DATE(NULL)
, ATSK.TASK_TYPE_CODE
, LKUP1.MEANING
, AUEF.DUE_DATE
, ATSK.DEPARTMENT_ID
, BDPT.DESCRIPTION DEPARTMENT_NAME
, ATSK.ORIGINATING_TASK_ID
, ORIGTSK.VISIT_TASK_NUMBER ORIGINATING_TASK_NUMBER
, ORIGTSK.VISIT_TASK_NAME ORIGINATING_TASK_NAME
, ATSK.MR_ID
, ATSK.UNIT_EFFECTIVITY_ID
, AMRH.DESCRIPTION
FROM AHL_VISITS_VL AVTS
, AHL_VISIT_TASKS_VL ATSK
, AHL_VISIT_TASKS_VL ORIGTSK
, AHL_MR_HEADERS_VL AMRH
, AHL_UNIT_EFFECTIVITIES_VL AUEF
, CSI_ITEM_INSTANCES CSIS
, CS_INCIDENTS_ALL_B SR
, BOM_DEPARTMENTS BDPT
, FND_LOOKUP_VALUES_VL LKUP1
, FND_LOOKUP_VALUES_VL LKUP2
, MTL_SYSTEM_ITEMS_B_KFV MTSB
, AHL_WORKORDERS AWO
WHERE ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+)
AND ATSK.ORIGINATING_TASK_ID = ORIGTSK.VISIT_TASK_ID(+)
AND ATSK.SERVICE_REQUEST_ID = SR.INCIDENT_ID(+)
AND ATSK.UNIT_EFFECTIVITY_ID = AUEF.UNIT_EFFECTIVITY_ID(+)
AND ATSK.INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID
AND ATSK.ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID
AND LKUP1.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_TYPE'
AND LKUP1.LOOKUP_CODE(+) = ATSK.TASK_TYPE_CODE
AND AVTS.VISIT_ID = ATSK.VISIT_ID
AND AVTS.TEMPLATE_FLAG = 'N'
AND ATSK.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
AND ATSK.VISIT_TASK_ID = AWO.VISIT_TASK_ID(+)
AND ATSK.MR_ID IS NOT NULL
AND NVL(ATSK.STATUS_CODE
, 'X') <> 'DELETED'
AND ATSK.TASK_TYPE_CODE = 'SUMMARY'
AND AMRH.MR_HEADER_ID = ATSK.MR_ID
AND LKUP2.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS'
AND LKUP2.LOOKUP_CODE(+) = AWO.STATUS_CODE UNION SELECT AVTS.VISIT_ID VISIT_ID
, AVTS.VISIT_NUMBER
, AVTS.TEMPLATE_FLAG
, AVTS.STATUS_CODE
, ATSK.VISIT_TASK_ID
, ATSK.VISIT_TASK_NUMBER
, ATSK.VISIT_TASK_NAME
, ATSK.INVENTORY_ITEM_ID
, MTSB.CONCATENATED_SEGMENTS
, ATSK.ITEM_ORGANIZATION_ID
, ATSK.INSTANCE_ID
, CSIS.SERIAL_NUMBER
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, ATSK.TASK_TYPE_CODE
, LKUP1.MEANING
, TO_DATE(NULL)
, ATSK.DEPARTMENT_ID
, BDPT.DESCRIPTION DEPARTMENT_NAME
, ATSK.ORIGINATING_TASK_ID
, ORIGTSK.VISIT_TASK_NUMBER ORIGINATING_TASK_NUMBER
, ORIGTSK.VISIT_TASK_NAME ORIGINATING_TASK_NAME
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
FROM AHL_VISITS_VL AVTS
, AHL_VISIT_TASKS_VL ATSK
, AHL_VISIT_TASKS_VL ORIGTSK
, CSI_ITEM_INSTANCES CSIS
, BOM_DEPARTMENTS BDPT
, FND_LOOKUP_VALUES_VL LKUP1
, MTL_SYSTEM_ITEMS_B_KFV MTSB
WHERE ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+)
AND ATSK.ORIGINATING_TASK_ID = ORIGTSK.VISIT_TASK_ID(+)
AND ATSK.INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID
AND ATSK.ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID
AND LKUP1.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_TYPE'
AND LKUP1.LOOKUP_CODE(+) = ATSK.TASK_TYPE_CODE
AND AVTS.VISIT_ID = ATSK.VISIT_ID
AND AVTS.TEMPLATE_FLAG = 'N'
AND ATSK.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
AND ATSK.MR_ID IS NULL
AND NVL(ATSK.STATUS_CODE
, 'X') <> 'DELETED'
AND ATSK.TASK_TYPE_CODE = 'SUMMARY'

Columns