VIEW: APPS.AHL_VISIT_DETAILS_V

Source

View Text - Preformatted

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, MP.ORGANIZATION_CODE ORG_CODE, HROU.NAME ORG_NAME, AVTS.DEPARTMENT_ID DEPT_ID, BDPT.DEPARTMENT_CODE DEPT_CODE, 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, MTL_PARAMETERS MP, 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 = MP.ORGANIZATION_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_VWP_VISIT_STATUS' AND FNVS.LOOKUP_CODE(+) = AVTS.STATUS_CODE AND (AVTS.ORGANIZATION_ID is NULL OR AVTS.ORGANIZATION_ID IN ( SELECT organization_id FROM org_organization_definitions WHERE operating_unit = mo_global.get_current_org_id() ))

View Text - HTML Formatted

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
, MP.ORGANIZATION_CODE ORG_CODE
, HROU.NAME ORG_NAME
, AVTS.DEPARTMENT_ID DEPT_ID
, BDPT.DEPARTMENT_CODE DEPT_CODE
, 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
, MTL_PARAMETERS MP
, 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 = MP.ORGANIZATION_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_VWP_VISIT_STATUS'
AND FNVS.LOOKUP_CODE(+) = AVTS.STATUS_CODE
AND (AVTS.ORGANIZATION_ID IS NULL OR AVTS.ORGANIZATION_ID IN ( SELECT ORGANIZATION_ID
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE OPERATING_UNIT = MO_GLOBAL.GET_CURRENT_ORG_ID() ))