View: AHL_SEARCH_VISITS_V

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

View Text

SELECT AVTS.VISIT_ID VISIT_ID
, AVTS.VISIT_NUMBER
, AVTS.VISIT_NAME
, AVTS.ORGANIZATION_ID ORGANIZATION_ID
, HROU.NAME ORGANIZATION_NAME
, AVTS.DEPARTMENT_ID DEPARTMENT_ID
, BDPT.DESCRIPTION DEPARTMENT_NAME
, AVTS.OBJECT_VERSION_NUMBER
, AVTS.START_DATE_TIME
, AVTS.STATUS_CODE
, FLVT1.MEANING STATUS_MEAN
, AVTS.TEMPLATE_FLAG
, AVTS.ITEM_INSTANCE_ID ITEM_INSTANCE_ID
, CSIS.SERIAL_NUMBER SERIAL_NUMBER
, AVTS.INVENTORY_ITEM_ID
, AVTS.ITEM_ORGANIZATION_ID
, MTSB.CONCATENATED_SEGMENTS ITEM_DESCRIPTION
, AVTS.VISIT_TYPE_CODE
, FLVT.MEANING VISIT_TYPE_MEAN
, AVTS.SIMULATION_PLAN_ID
, ASPV.SIMULATION_PLAN_NAME
, ASPV.PRIMARY_PLAN_FLAG
, AVTS.SPACE_CATEGORY_CODE
, FLVT2.MEANING SPACE_CATEGORY_MEAN
, AVTS.SERVICE_REQUEST_ID
, AVTS.CLOSE_DATE_TIME
, CSAB.INCIDENT_NUMBER
, AHL_VWP_VISITS_PVT.GET_UNITNAME(AVTS.ITEM_INSTANCE_ID) UNIT_NAME
FROM AHL_VISITS_VL AVTS
, AHL_SIMULATION_PLANS_VL ASPV
, CSI_ITEM_INSTANCES CSIS
, HR_ALL_ORGANIZATION_UNITS HROU
, BOM_DEPARTMENTS BDPT
, MTL_SYSTEM_ITEMS_B_KFV MTSB
, FND_LOOKUP_VALUES_VL FLVT
, FND_LOOKUP_VALUES_VL FLVT1
, FND_LOOKUP_VALUES_VL FLVT2
, CS_INCIDENTS_ALL_B CSAB
WHERE AVTS.ITEM_INSTANCE_ID = CSIS.INSTANCE_ID
AND AVTS.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+)
AND AVTS.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
AND AVTS.SIMULATION_PLAN_ID = ASPV.SIMULATION_PLAN_ID(+)
AND AVTS. INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID
AND AVTS. ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID
AND FLVT.LOOKUP_TYPE(+) = 'AHL_PLANNING_VISIT_TYPE'
AND FLVT.LOOKUP_CODE(+) = AVTS.VISIT_TYPE_CODE
AND FLVT1.LOOKUP_TYPE(+) = 'AHL_VWP_VISIT_STATUS'
AND FLVT1.LOOKUP_CODE(+) = AVTS.STATUS_CODE
AND FLVT2.LOOKUP_TYPE(+) = 'AHL_LTP_SPACE_CATEGORY'
AND FLVT2.LOOKUP_CODE(+) = AVTS.SPACE_CATEGORY_CODE
AND AVTS.SERVICE_REQUEST_ID = CSAB.INCIDENT_ID(+)
AND AVTS.TEMPLATE_FLAG = 'N'
AND AVTS.STATUS_CODE <>'DELETED'

Columns