VIEW: APPS.AHL_SEARCH_VISIT_TASK_V

Source

View Text - Preformatted

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, ARV.ROUTE_TYPE_CODE, LKUP4.MEANING "ROUTE_TYPE", 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, ATSK.START_DATE_TIME, ATSK.END_DATE_TIME, ATSK.TASK_TYPE_CODE, LKUP1.MEANING, ATSK.STATUS_CODE "TASK_STATUS_CODE", LKUP3.MEANING "TASK_STATUS_NAME", ATSK.STAGE_ID, ASTG.STAGE_NUM, ASTG.STAGE_NAME, 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, ATSK.QUANTITY, CSIS.UNIT_OF_MEASURE, CSIS.INSTANCE_NUMBER 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_B AUEF, CSI_ITEM_INSTANCES CSIS, CS_INCIDENTS_ALL_B SR, BOM_DEPARTMENTS BDPT, FND_LOOKUP_VALUES_VL LKUP1, FND_LOOKUP_VALUES_VL LKUP2, FND_LOOKUP_VALUES_VL LKUP3, FND_LOOKUP_VALUES_VL LKUP4, AHL_VWP_STAGES_VL ASTG, MTL_SYSTEM_ITEMS_B_KFV MTSB, AHL_ROUTES_B ARV, 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 AMRR.ROUTE_ID = ARV.ROUTE_ID (+) AND LKUP4.LOOKUP_TYPE (+) = 'AHL_ROUTE_TYPE' AND LKUP4.LOOKUP_CODE (+) = ARV.ROUTE_TYPE_CODE 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 LKUP3.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS' AND LKUP3.LOOKUP_CODE(+) = ATSK.STATUS_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 AND ATSK.STAGE_ID = ASTG.STAGE_ID(+) 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), to_char(NULL), 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, ATSK.START_DATE_TIME, ATSK.END_DATE_TIME, ATSK.TASK_TYPE_CODE, LKUP1.MEANING, ATSK.STATUS_CODE "TASK_STATUS_CODE", LKUP3.MEANING "TASK_STATUS_NAME", ATSK.STAGE_ID, ASTG.STAGE_NUM, ASTG.STAGE_NAME, 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, ATSK.QUANTITY, CSIS.UNIT_OF_MEASURE, CSIS.INSTANCE_NUMBER FROM AHL_VISITS_VL AVTS, AHL_VISIT_TASKS_VL ATSK, AHL_VISIT_TASKS_VL ORIGTSK, AHL_MR_HEADERS_VL AMRH, AHL_UNIT_EFFECTIVITIES_B AUEF, CSI_ITEM_INSTANCES CSIS, CS_INCIDENTS_ALL_B SR, AHL_VWP_STAGES_VL ASTG, BOM_DEPARTMENTS BDPT, FND_LOOKUP_VALUES_VL LKUP1, FND_LOOKUP_VALUES_VL LKUP2, FND_LOOKUP_VALUES_VL LKUP3, 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 LKUP3.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS' AND LKUP3.LOOKUP_CODE(+) = ATSK.STATUS_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 AND ATSK.STAGE_ID = ASTG.STAGE_ID(+) 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_char(NULL), to_char(NULL), to_number(null), to_char(NULL), to_char(NULL), to_char(NULL), to_date(NULL), to_date(NULL), ATSK.SERVICE_REQUEST_ID, SR.INCIDENT_NUMBER, ATSK.START_DATE_TIME, ATSK.END_DATE_TIME, ATSK.TASK_TYPE_CODE, LKUP1.MEANING, ATSK.STATUS_CODE "TASK_STATUS_CODE", LKUP2.MEANING "TASK_STATUS_NAME", ATSK.STAGE_ID, ASTG.STAGE_NUM, ASTG.STAGE_NAME, 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), ATSK.QUANTITY, CSIS.UNIT_OF_MEASURE, CSIS.INSTANCE_NUMBER FROM AHL_VISITS_VL AVTS, AHL_VISIT_TASKS_VL ATSK, AHL_VISIT_TASKS_VL ORIGTSK, CSI_ITEM_INSTANCES CSIS, CS_INCIDENTS_ALL_B SR, AHL_VWP_STAGES_VL ASTG, BOM_DEPARTMENTS BDPT, FND_LOOKUP_VALUES_VL LKUP1, FND_LOOKUP_VALUES_VL LKUP2, MTL_SYSTEM_ITEMS_B_KFV MTSB 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.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 LKUP2.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS' AND LKUP2.LOOKUP_CODE(+) = ATSK.STATUS_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' AND ATSK.STAGE_ID = ASTG.STAGE_ID(+)

View Text - HTML Formatted

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
, ARV.ROUTE_TYPE_CODE
, LKUP4.MEANING "ROUTE_TYPE"
, 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
, ATSK.START_DATE_TIME
, ATSK.END_DATE_TIME
, ATSK.TASK_TYPE_CODE
, LKUP1.MEANING
, ATSK.STATUS_CODE "TASK_STATUS_CODE"
, LKUP3.MEANING "TASK_STATUS_NAME"
, ATSK.STAGE_ID
, ASTG.STAGE_NUM
, ASTG.STAGE_NAME
, 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
, ATSK.QUANTITY
, CSIS.UNIT_OF_MEASURE
, CSIS.INSTANCE_NUMBER
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_B AUEF
, CSI_ITEM_INSTANCES CSIS
, CS_INCIDENTS_ALL_B SR
, BOM_DEPARTMENTS BDPT
, FND_LOOKUP_VALUES_VL LKUP1
, FND_LOOKUP_VALUES_VL LKUP2
, FND_LOOKUP_VALUES_VL LKUP3
, FND_LOOKUP_VALUES_VL LKUP4
, AHL_VWP_STAGES_VL ASTG
, MTL_SYSTEM_ITEMS_B_KFV MTSB
, AHL_ROUTES_B ARV
, 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 AMRR.ROUTE_ID = ARV.ROUTE_ID (+)
AND LKUP4.LOOKUP_TYPE (+) = 'AHL_ROUTE_TYPE'
AND LKUP4.LOOKUP_CODE (+) = ARV.ROUTE_TYPE_CODE
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 LKUP3.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS'
AND LKUP3.LOOKUP_CODE(+) = ATSK.STATUS_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
AND ATSK.STAGE_ID = ASTG.STAGE_ID(+) 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)
, TO_CHAR(NULL)
, 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
, ATSK.START_DATE_TIME
, ATSK.END_DATE_TIME
, ATSK.TASK_TYPE_CODE
, LKUP1.MEANING
, ATSK.STATUS_CODE "TASK_STATUS_CODE"
, LKUP3.MEANING "TASK_STATUS_NAME"
, ATSK.STAGE_ID
, ASTG.STAGE_NUM
, ASTG.STAGE_NAME
, 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
, ATSK.QUANTITY
, CSIS.UNIT_OF_MEASURE
, CSIS.INSTANCE_NUMBER
FROM AHL_VISITS_VL AVTS
, AHL_VISIT_TASKS_VL ATSK
, AHL_VISIT_TASKS_VL ORIGTSK
, AHL_MR_HEADERS_VL AMRH
, AHL_UNIT_EFFECTIVITIES_B AUEF
, CSI_ITEM_INSTANCES CSIS
, CS_INCIDENTS_ALL_B SR
, AHL_VWP_STAGES_VL ASTG
, BOM_DEPARTMENTS BDPT
, FND_LOOKUP_VALUES_VL LKUP1
, FND_LOOKUP_VALUES_VL LKUP2
, FND_LOOKUP_VALUES_VL LKUP3
, 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 LKUP3.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS'
AND LKUP3.LOOKUP_CODE(+) = ATSK.STATUS_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
AND ATSK.STAGE_ID = ASTG.STAGE_ID(+) 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_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, ATSK.SERVICE_REQUEST_ID
, SR.INCIDENT_NUMBER
, ATSK.START_DATE_TIME
, ATSK.END_DATE_TIME
, ATSK.TASK_TYPE_CODE
, LKUP1.MEANING
, ATSK.STATUS_CODE "TASK_STATUS_CODE"
, LKUP2.MEANING "TASK_STATUS_NAME"
, ATSK.STAGE_ID
, ASTG.STAGE_NUM
, ASTG.STAGE_NAME
, 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)
, ATSK.QUANTITY
, CSIS.UNIT_OF_MEASURE
, CSIS.INSTANCE_NUMBER
FROM AHL_VISITS_VL AVTS
, AHL_VISIT_TASKS_VL ATSK
, AHL_VISIT_TASKS_VL ORIGTSK
, CSI_ITEM_INSTANCES CSIS
, CS_INCIDENTS_ALL_B SR
, AHL_VWP_STAGES_VL ASTG
, BOM_DEPARTMENTS BDPT
, FND_LOOKUP_VALUES_VL LKUP1
, FND_LOOKUP_VALUES_VL LKUP2
, MTL_SYSTEM_ITEMS_B_KFV MTSB
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.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 LKUP2.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS'
AND LKUP2.LOOKUP_CODE(+) = ATSK.STATUS_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'
AND ATSK.STAGE_ID = ASTG.STAGE_ID(+)