View: AHL_VISIT_DETAILS_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	This view stores the query to retrieve Visit details (ITEM,INSTANCE, ORGANIZATION, DEPARTMENT, UNIT information)
Implementation/DBA Data:	APPS.AHL_VISIT_DETAILS_V

View Text

SELECT AVTS.VISIT_ID VISIT_ID
, AVTS.VISIT_NUMBER VISIT_NUMBER
, AVTS.VISIT_NAME VISIT_NAME
, ASSO_PRIMARY_VISIT_ID
, (SELECT VISIT_NUMBER
FROM AHL_VISITS_VL
WHERE VISIT_ID IN ( SELECT ASSO_PRIMARY_VISIT_ID
FROM AHL_VISITS_VL
WHERE VISIT_ID = AVTS.VISIT_ID)) PRIMARY_VISIT_NUMBER
, AVTS.ORGANIZATION_ID ORG_ID
, HROU.NAME ORG_NAME
, AVTS.DEPARTMENT_ID DEPT_ID
, BDPT.DESCRIPTION DEPT_DESCRIPTION
, AVTS.OBJECT_VERSION_NUMBER
, AVTS.START_DATE_TIME
, NULL END_DATE
, 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_DELETE_FLAG
, FNSD.MEANING SIMULATION_DELETE_MEAN
, AVTS.SIMULATION_PLAN_ID
, ASPL.SIMULATION_PLAN_NAME
, (SELECT MAX(DUE_DATE)
FROM AHL_UNIT_EFFECTIVITIES_B 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
, AVTS. CLOSE_DATE_TIME
, AVTS.SPACE_CATEGORY_CODE
, FNSC.MEANING SPACE_CATEGORY_MEAN
, AHL_UTILITY_PVT.GET_UNIT_NAME(AVTS.ITEM_INSTANCE_ID)
, AVTS.STATUS_CODE
, FNVS.MEANING STATUS_MEANING
FROM AHL_VISITS_VL AVTS
, CSI_ITEM_INSTANCES CSIS
, HR_ALL_ORGANIZATION_UNITS HROU
, BOM_DEPARTMENTS BDPT
, MTL_SYSTEM_ITEMS_B_KFV MTSB
, AHL_SIMULATION_PLANS_VL ASPL
, FND_LOOKUP_VALUES_VL FLVT
, FND_LOOKUP_VALUES_VL FNSD
, FND_LOOKUP_VALUES_VL FNSC
, FND_LOOKUP_VALUES_VL FNVS
WHERE AVTS.ITEM_INSTANCE_ID = CSIS.INSTANCE_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 AVTS.SIMULATION_PLAN_ID = ASPL.SIMULATION_PLAN_ID(+)
AND FLVT.LOOKUP_TYPE(+) = 'AHL_PLANNING_VISIT_TYPE'
AND FLVT.LOOKUP_CODE(+) = AVTS.VISIT_TYPE_CODE
AND FNSD.LOOKUP_TYPE(+) = 'AHL_YES_NO_TYPE'
AND FNSD.LOOKUP_CODE(+) = AVTS.SIMULATION_DELETE_FLAG
AND FNSC.LOOKUP_TYPE(+) = 'AHL_SPACE_CATEGORY'
AND FNSC.LOOKUP_CODE(+) = AVTS.SPACE_CATEGORY_CODE
AND FNVS.LOOKUP_TYPE(+) = 'AHL_VISIT_STATUS'
AND FNVS.LOOKUP_CODE(+) = AVTS.STATUS_CODE

Columns