VIEW: APPS.AHL_SEARCH_VISITS_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.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, NVL(ASPV.PRIMARY_PLAN_FLAG,'Y') PRIMARY_PLAN_FLAG, AVTS.SPACE_CATEGORY_CODE, FLVT2.MEANING SPACE_CATEGORY_MEAN, AVTS.SERVICE_REQUEST_ID, AVTS.CLOSE_DATE_TIME, CSAB.INCIDENT_NUMBER, UC.NAME UNIT_NAME, AVTS.PRIORITY_CODE, FLVT3.MEANING PRIORITY_MEAN, AVTS.PROJECT_TEMPLATE_ID, AVTS.UNIT_SCHEDULE_ID, PA.NAME PROJECT_TEMPLATE_NAME, AVTS.ASSO_PRIMARY_VISIT_ID, AVTS.REQUEST_ID||' '||FL.MEANING CP_REQUEST, AVTS.PAST_DATED_VISIT_FLAG 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, FND_LOOKUP_VALUES_VL FLVT3, PA_PROJECTS PA, AHL_UNIT_CONFIG_HEADERS UC, CS_INCIDENTS_ALL_B CSAB, FND_CONCURRENT_REQUESTS FCR, FND_LOOKUPS FL WHERE AVTS.ITEM_INSTANCE_ID = UC.CSI_ITEM_INSTANCE_ID(+) AND 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 FLVT3.LOOKUP_TYPE(+) = 'AHL_VWP_VISIT_PRIORITY' AND FLVT3.LOOKUP_CODE(+) = AVTS.PRIORITY_CODE AND PA.PROJECT_ID(+) = AVTS.PROJECT_TEMPLATE_ID AND AVTS.SERVICE_REQUEST_ID = CSAB.INCIDENT_ID(+) AND AVTS.TEMPLATE_FLAG = 'N' AND AVTS.STATUS_CODE <> 'DELETED' AND AVTS.REQUEST_ID = FCR.REQUEST_ID (+) AND FL.LOOKUP_CODE (+) = FCR.STATUS_CODE AND FL.LOOKUP_TYPE (+) = 'CP_STATUS_CODE' AND UC.active_end_date is null AND (HROU.ORGANIZATION_ID is null OR HROU.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
, 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
, NVL(ASPV.PRIMARY_PLAN_FLAG
, 'Y') PRIMARY_PLAN_FLAG
, AVTS.SPACE_CATEGORY_CODE
, FLVT2.MEANING SPACE_CATEGORY_MEAN
, AVTS.SERVICE_REQUEST_ID
, AVTS.CLOSE_DATE_TIME
, CSAB.INCIDENT_NUMBER
, UC.NAME UNIT_NAME
, AVTS.PRIORITY_CODE
, FLVT3.MEANING PRIORITY_MEAN
, AVTS.PROJECT_TEMPLATE_ID
, AVTS.UNIT_SCHEDULE_ID
, PA.NAME PROJECT_TEMPLATE_NAME
, AVTS.ASSO_PRIMARY_VISIT_ID
, AVTS.REQUEST_ID||' '||FL.MEANING CP_REQUEST
, AVTS.PAST_DATED_VISIT_FLAG
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
, FND_LOOKUP_VALUES_VL FLVT3
, PA_PROJECTS PA
, AHL_UNIT_CONFIG_HEADERS UC
, CS_INCIDENTS_ALL_B CSAB
, FND_CONCURRENT_REQUESTS FCR
, FND_LOOKUPS FL
WHERE AVTS.ITEM_INSTANCE_ID = UC.CSI_ITEM_INSTANCE_ID(+)
AND 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 FLVT3.LOOKUP_TYPE(+) = 'AHL_VWP_VISIT_PRIORITY'
AND FLVT3.LOOKUP_CODE(+) = AVTS.PRIORITY_CODE
AND PA.PROJECT_ID(+) = AVTS.PROJECT_TEMPLATE_ID
AND AVTS.SERVICE_REQUEST_ID = CSAB.INCIDENT_ID(+)
AND AVTS.TEMPLATE_FLAG = 'N'
AND AVTS.STATUS_CODE <> 'DELETED'
AND AVTS.REQUEST_ID = FCR.REQUEST_ID (+)
AND FL.LOOKUP_CODE (+) = FCR.STATUS_CODE
AND FL.LOOKUP_TYPE (+) = 'CP_STATUS_CODE'
AND UC.ACTIVE_END_DATE IS NULL
AND (HROU.ORGANIZATION_ID IS NULL OR HROU.ORGANIZATION_ID IN ( SELECT ORGANIZATION_ID
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE OPERATING_UNIT = MO_GLOBAL.GET_CURRENT_ORG_ID() ) )