VIEW: APPS.AHL_VISITS_INFO_V

Source

View Text - Preformatted

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, (SELECT MAX(END_DATE_TIME) FROM AHL_VISIT_TASKS_VL WHERE VISIT_ID = AVTS.VISIT_ID) END_DATE_TIME, (SELECT MC_HEADER_ID FROM AHL_MC_HEADERS_B WHERE MC_ID IN (SELECT MASTER_CONFIG_ID FROM AHL_UNIT_CONFIG_HEADERS WHERE CSI_ITEM_INSTANCE_ID = AVTS.ITEM_INSTANCE_ID AND CONFIG_STATUS_CODE = 'COMPLETE')) MC_HEADER_ID 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

View Text - HTML Formatted

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
, (SELECT MAX(END_DATE_TIME)
FROM AHL_VISIT_TASKS_VL
WHERE VISIT_ID = AVTS.VISIT_ID) END_DATE_TIME
, (SELECT MC_HEADER_ID
FROM AHL_MC_HEADERS_B
WHERE MC_ID IN (SELECT MASTER_CONFIG_ID
FROM AHL_UNIT_CONFIG_HEADERS
WHERE CSI_ITEM_INSTANCE_ID = AVTS.ITEM_INSTANCE_ID
AND CONFIG_STATUS_CODE = 'COMPLETE')) MC_HEADER_ID
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