VIEW: APPS.AHL_SEARCH_TEMPLATE_TASK_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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