View: AHL_SEARCH_TEMPLATE_TASK_V

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

View Text

SELECT ATAV.VISIT_ID
, AVTS.VISIT_NUMBER
, AVTS.VISIT_NAME
, ATAV.VISIT_TASK_ID
, ATAV.VISIT_TASK_NUMBER
, ATAV.VISIT_TASK_NAME
, ATAV.INVENTORY_ITEM_ID
, ATAV.ITEM_ORGANIZATION_ID
, MTSB.CONCATENATED_SEGMENTS
, ATAV.MR_ROUTE_ID
, MRHV.TITLE
, AMRV.ROUTE_NUMBER
, AVTS.TEMPLATE_FLAG
, ATAV.TASK_TYPE_CODE
, LKUP1.MEANING
FROM AHL_VISITS_VL AVTS
, MTL_SYSTEM_ITEMS_B_KFV MTSB
, AHL_VISIT_TASKS_VL ATAV
, AHL_MR_ROUTES_V AMRV
, AHL_MR_HEADERS_APP_V MRHV
, FND_LOOKUP_VALUES_VL LKUP1
WHERE ATAV. INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID
AND ATAV. ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID
AND AVTS.VISIT_ID = ATAV.VISIT_ID
AND (ATAV.MR_ROUTE_ID = AMRV.MR_ROUTE_ID (+)
AND AMRV.MR_HEADER_ID = MRHV.MR_HEADER_ID (+))
AND LKUP1.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_TYPE'
AND LKUP1.LOOKUP_CODE(+) = ATAV.TASK_TYPE_CODE
AND AVTS.TEMPLATE_FLAG ='Y'
AND ATAV.SUMMARY_TASK_FLAG = 'N'
AND ATAV.MR_ID IS NULL UNION SELECT ATAV.VISIT_ID
, AVTS.VISIT_NUMBER
, AVTS.VISIT_NAME
, ATAV.VISIT_TASK_ID
, ATAV.VISIT_TASK_NUMBER
, ATAV.VISIT_TASK_NAME
, ATAV.INVENTORY_ITEM_ID
, ATAV.ITEM_ORGANIZATION_ID
, MTSB.CONCATENATED_SEGMENTS
, ATAV.MR_ROUTE_ID
, MRHV.TITLE
, NULL
, AVTS.TEMPLATE_FLAG
, ATAV.TASK_TYPE_CODE
, LKUP1.MEANING
FROM AHL_VISITS_VL AVTS
, MTL_SYSTEM_ITEMS_B_KFV MTSB
, AHL_VISIT_TASKS_VL ATAV
, AHL_MR_HEADERS_VL MRHV
, FND_LOOKUP_VALUES_VL LKUP1
WHERE ATAV. INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID
AND ATAV. ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID
AND AVTS.VISIT_ID = ATAV.VISIT_ID
AND LKUP1.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_TYPE'
AND LKUP1.LOOKUP_CODE(+) = ATAV.TASK_TYPE_CODE
AND AVTS.TEMPLATE_FLAG ='Y'
AND ATAV.SUMMARY_TASK_FLAG = 'N'
AND ATAV.MR_ID IS NOT NULL
AND MRHV.MR_HEADER_ID = ATAV.MR_ID

Columns