VIEW: APPS.AHL_PRD_VISITS_V

Source

View Text - Preformatted

SELECT AVTS.VISIT_ID, AVTS.OBJECT_VERSION_NUMBER, AVTS.VISIT_NUMBER, AVTS.VISIT_NAME, AVTS.ORGANIZATION_ID, HROU.NAME, AVTS.DEPARTMENT_ID, BDPT.DESCRIPTION, AVTS.START_DATE_TIME, AVTS.CLOSE_DATE_TIME, WIP.SCHEDULED_START_DATE, WIP.SCHEDULED_COMPLETION_DATE, DECODE( AVTS.STATUS_CODE, 'CLOSED', WO.ACTUAL_START_DATE, NULL ), DECODE( AVTS.STATUS_CODE, 'CLOSED', WO.ACTUAL_END_DATE, NULL ), AVTS.STATUS_CODE, FLVT1.MEANING, AVTS.ITEM_INSTANCE_ID, CSIS.SERIAL_NUMBER, CSIS.INVENTORY_ITEM_ID, AVTS.ITEM_ORGANIZATION_ID , MTSB.CONCATENATED_SEGMENTS, AVTS.VISIT_TYPE_CODE , FLVT.MEANING, AHL_UTIL_UC_PKG.get_unit_name(AVTS.ITEM_INSTANCE_ID), AHL_PRD_UTIL_PKG.is_unit_locked(NULL, NULL, AVTS.VISIT_ID, NULL), AVTS.inv_locator_id, MTLI.subinventory_code, decode(MSI.segment19, null, MTLI.concatenated_segments, INV_PROJECT.GET_LOCSEGS(MTLI.concatenated_segments) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101) || INV_ProjectLocator_PUB.get_project_number(MSI.segment19) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101) || INV_ProjectLocator_PUB.get_task_number(MSI.segment20)) locator_segments, AHL_PRD_PRINT_PVT.Get_JC_File_Location('VST',AVTS.VISIT_ID) FILE_LOCATION, AHL_PRD_PRINT_PVT.Generated_JobCard_Exists('VST',AVTS.VISIT_ID) JOB_CARD_EXISTS, AHL_PRD_PRINT_PVT.Get_Latest_JCG_Status('VST',AVTS.VISIT_ID) LATEST_JC_STATUS, AHL_PRD_PRINT_PVT.Get_JCG_Error_Msg('VST',AVTS.VISIT_ID) JOB_CARD_ERROR_MSG FROM AHL_VISITS_VL AVTS, 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, WIP_DISCRETE_JOBS WIP, AHL_WORKORDERS WO, mtl_item_locations_kfv MTLI, mtl_item_locations MSI WHERE AVTS.ITEM_INSTANCE_ID = CSIS.INSTANCE_ID(+) AND AVTS.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+) AND AVTS.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+) AND CSIS.INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+) AND CSIS.LAST_VLD_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 WIP.WIP_ENTITY_ID = WO.WIP_ENTITY_ID AND WO.VISIT_TASK_ID IS NULL AND WO.VISIT_ID = AVTS.VISIT_ID AND AVTS.STATUS_CODE IN ('RELEASED', 'CLOSED', 'PARTIALLY RELEASED') AND MTLI.inventory_location_id(+) = AVTS.inv_locator_id AND MSI.inventory_location_id(+) = AVTS.inv_locator_id AND AVTS.ORGANIZATION_ID IN (SELECT ORGANIZATION_ID FROM org_organization_definitions WHERE NVL (operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id())

View Text - HTML Formatted

SELECT AVTS.VISIT_ID
, AVTS.OBJECT_VERSION_NUMBER
, AVTS.VISIT_NUMBER
, AVTS.VISIT_NAME
, AVTS.ORGANIZATION_ID
, HROU.NAME
, AVTS.DEPARTMENT_ID
, BDPT.DESCRIPTION
, AVTS.START_DATE_TIME
, AVTS.CLOSE_DATE_TIME
, WIP.SCHEDULED_START_DATE
, WIP.SCHEDULED_COMPLETION_DATE
, DECODE( AVTS.STATUS_CODE
, 'CLOSED'
, WO.ACTUAL_START_DATE
, NULL )
, DECODE( AVTS.STATUS_CODE
, 'CLOSED'
, WO.ACTUAL_END_DATE
, NULL )
, AVTS.STATUS_CODE
, FLVT1.MEANING
, AVTS.ITEM_INSTANCE_ID
, CSIS.SERIAL_NUMBER
, CSIS.INVENTORY_ITEM_ID
, AVTS.ITEM_ORGANIZATION_ID
, MTSB.CONCATENATED_SEGMENTS
, AVTS.VISIT_TYPE_CODE
, FLVT.MEANING
, AHL_UTIL_UC_PKG.GET_UNIT_NAME(AVTS.ITEM_INSTANCE_ID)
, AHL_PRD_UTIL_PKG.IS_UNIT_LOCKED(NULL
, NULL
, AVTS.VISIT_ID
, NULL)
, AVTS.INV_LOCATOR_ID
, MTLI.SUBINVENTORY_CODE
, DECODE(MSI.SEGMENT19
, NULL
, MTLI.CONCATENATED_SEGMENTS
, INV_PROJECT.GET_LOCSEGS(MTLI.CONCATENATED_SEGMENTS) || FND_FLEX_EXT.GET_DELIMITER('INV'
, 'MTLL'
, 101) || INV_PROJECTLOCATOR_PUB.GET_PROJECT_NUMBER(MSI.SEGMENT19) || FND_FLEX_EXT.GET_DELIMITER('INV'
, 'MTLL'
, 101) || INV_PROJECTLOCATOR_PUB.GET_TASK_NUMBER(MSI.SEGMENT20)) LOCATOR_SEGMENTS
, AHL_PRD_PRINT_PVT.GET_JC_FILE_LOCATION('VST'
, AVTS.VISIT_ID) FILE_LOCATION
, AHL_PRD_PRINT_PVT.GENERATED_JOBCARD_EXISTS('VST'
, AVTS.VISIT_ID) JOB_CARD_EXISTS
, AHL_PRD_PRINT_PVT.GET_LATEST_JCG_STATUS('VST'
, AVTS.VISIT_ID) LATEST_JC_STATUS
, AHL_PRD_PRINT_PVT.GET_JCG_ERROR_MSG('VST'
, AVTS.VISIT_ID) JOB_CARD_ERROR_MSG
FROM AHL_VISITS_VL AVTS
, 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
, WIP_DISCRETE_JOBS WIP
, AHL_WORKORDERS WO
, MTL_ITEM_LOCATIONS_KFV MTLI
, MTL_ITEM_LOCATIONS MSI
WHERE AVTS.ITEM_INSTANCE_ID = CSIS.INSTANCE_ID(+)
AND AVTS.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+)
AND AVTS.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
AND CSIS.INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+)
AND CSIS.LAST_VLD_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 WIP.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WO.VISIT_TASK_ID IS NULL
AND WO.VISIT_ID = AVTS.VISIT_ID
AND AVTS.STATUS_CODE IN ('RELEASED'
, 'CLOSED'
, 'PARTIALLY RELEASED')
AND MTLI.INVENTORY_LOCATION_ID(+) = AVTS.INV_LOCATOR_ID
AND MSI.INVENTORY_LOCATION_ID(+) = AVTS.INV_LOCATOR_ID
AND AVTS.ORGANIZATION_ID IN (SELECT ORGANIZATION_ID
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE NVL (OPERATING_UNIT
, MO_GLOBAL.GET_CURRENT_ORG_ID()) = MO_GLOBAL.GET_CURRENT_ORG_ID())