View: AHL_VISITS_INFO_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	This view stores the query to retrieve Visit information
Implementation/DBA Data:	APPS.AHL_VISITS_INFO_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.ITEM_INSTANCE_ID ITEM_INSTANCE_ID
, CSIS.SERIAL_NUMBER SERIAL_NUMBER
, AVTS.INVENTORY_ITEM_ID
, MTSB.CONCATENATED_SEGMENTS ITEM_DESCRIPTION
, AVTS.VISIT_TYPE_CODE
, FLVT.MEANING VISIT_TYPE_MEAN
, AVTS.SIMULATION_PLAN_ID
, ASML.SIMULATION_PLAN_NAME
, (SELECT MAX(DUE_DATE)
FROM AHL_UNIT_EFFECTIVITIES_APP_V A
, AHL_VISIT_TASKS_B B
WHERE A.UNIT_EFFECTIVITY_ID = B.UNIT_EFFECTIVITY_ID
AND B.VISIT_ID = AVTS.VISIT_ID GROUP BY VISIT_ID ) DUE_BY
, ASML. PRIMARY_PLAN_FLAG
, AVTS.SCHEDULE_DESIGNATOR MRP_SCH_DESIGNATOR
, AVTS.CLOSE_DATE_TIME PLANNED_END_DATE
, AHL_UTILITY_PVT.GET_UNIT_NAME(AVTS.ITEM_INSTANCE_ID) UNIT_NAME
, AVTS.SPACE_CATEGORY_CODE
, FLSC.MEANING SPACE_CATEGORY_MEAN
, AVTS.STATUS_CODE
, FLVS.MEANING STATUS_MEAN
, TO_CHAR(AVTS.START_DATE_TIME
, 'HH24') START_TIME_HOURS
, TO_CHAR(AVTS.CLOSE_DATE_TIME
, 'HH24') PLANNED_END_TIME_HOURS
, AHL_LTP_SPACE_SCHEDULE_PVT.GET_DERIVED_END_DATE(AVTS.VISIT_ID) END_DATE_TIME
FROM AHL_VISITS_VL AVTS
, AHL_SIMULATION_PLANS_VL ASML
, CSI_ITEM_INSTANCES CSIS
, HR_ALL_ORGANIZATION_UNITS HROU
, BOM_DEPARTMENTS BDPT
, MTL_SYSTEM_ITEMS_KFV MTSB
, FND_LOOKUP_VALUES_VL FLVT
, FND_LOOKUP_VALUES_VL FLSC
, FND_LOOKUP_VALUES_VL FLVS
WHERE AVTS.ITEM_INSTANCE_ID = CSIS.INSTANCE_ID
AND AVTS.SIMULATION_PLAN_ID = ASML.SIMULATION_PLAN_ID
AND AVTS.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+)
AND AVTS.DEPARTMENT_ID = BDPT.DEPARTMENT_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 FLSC.LOOKUP_TYPE(+) = 'AHL_LTP_SPACE_CATEGORY'
AND FLSC.LOOKUP_CODE(+) = AVTS.SPACE_CATEGORY_CODE
AND FLVS.LOOKUP_TYPE(+) = 'AHL_VWP_VISIT_STATUS'
AND FLVS.LOOKUP_CODE(+) = AVTS.STATUS_CODE
AND AVTS.TEMPLATE_FLAG = 'N' ORDER BY VISIT_ID

Columns