VIEW: APPS.AHL_SRH_MR_HISTORY_V

Source

View Text - Preformatted

SELECT UE.unit_effectivity_id, UE.Object_version_number, UE.Csi_item_instance_id, UE.Mr_header_id, UE.Cs_Incident_id, DECODE( UE.Mr_header_id, null, UE.Cs_incident_number, UE.Title ), UE.VERSION_NUMBER, UE.DESCRIPTION, UE.CATEGORY_CODE, UE.CATEGORY, UE.PROGRAM_TYPE_CODE, UE.PROGRAM_TYPE, UE.PROGRAM_SUBTYPE_CODE, UE.PROGRAM_SUBTYPE, UE.Cs_incident_number incident_number, UE.Instance_number, UE.EFFECTIVE_FROM, UE.EFFECTIVE_TO, FL.Lookup_code, FL.Meaning, VST.Visit_id, VST.VISIT_NAME, VST.Visit_number, VST.VISIT_TYPE_CODE, VST.VISIT_TYPE_MEAN, VST.Start_date_time, UE.Qa_inspection_type_code, UC.Name, VST.Organization_id, ORG.Organization_name, VST.Department_id, BOM.Description, WO.actual_start_date, WO.actual_end_date, UE.Qa_Plan_id, UE.Qa_Collection_id, WO.WORKORDER_ID, WO.JOB_NUMBER, WO.INVENTORY_ITEM_ID, WO.WO_PART_NUMBER, WO.ITEM_DESCRIPTION, WO.ITEM_INSTANCE_ID, WO.ITEM_INSTANCE_NUMBER, WO.SERIAL_NUMBER, WO.JOB_STATUS_CODE, WO.JOB_STATUS_MEANING, WO.JOB_DESCRIPTION, VT.VISIT_TASK_ID, VT.VISIT_TASK_NUMBER, VT.VISIT_TASK_NAME FROM BOM_DEPARTMENTS BOM, ORG_ORGANIZATION_DEFINITIONS ORG, AHL_UNIT_CONFIG_HEADERS UC, FND_LOOKUP_VALUES_VL FL, AHL_UE_DEFERRAL_DETAILS_V UE, AHL_VISITS_INFO_V VST, AHL_VISIT_TASKS_V VT, AHL_WORKORDERS_V WO WHERE BOM.Department_id (+) = VST.Department_id AND ORG.Organization_id = VST.Organization_id AND FL.Lookup_type = 'AHL_PRD_MR_STATUS' AND FL.Lookup_code = AHL_COMPLETIONS_PVT.get_mr_status( UE.Unit_effectivity_id ) AND UC.Csi_item_instance_id (+) = UE.Csi_item_instance_id AND UE.Unit_effectivity_id = VT.Unit_effectivity_id AND VST.visit_id = VT.visit_id AND VT.visit_task_id = WO.visit_task_id

View Text - HTML Formatted

SELECT UE.UNIT_EFFECTIVITY_ID
, UE.OBJECT_VERSION_NUMBER
, UE.CSI_ITEM_INSTANCE_ID
, UE.MR_HEADER_ID
, UE.CS_INCIDENT_ID
, DECODE( UE.MR_HEADER_ID
, NULL
, UE.CS_INCIDENT_NUMBER
, UE.TITLE )
, UE.VERSION_NUMBER
, UE.DESCRIPTION
, UE.CATEGORY_CODE
, UE.CATEGORY
, UE.PROGRAM_TYPE_CODE
, UE.PROGRAM_TYPE
, UE.PROGRAM_SUBTYPE_CODE
, UE.PROGRAM_SUBTYPE
, UE.CS_INCIDENT_NUMBER INCIDENT_NUMBER
, UE.INSTANCE_NUMBER
, UE.EFFECTIVE_FROM
, UE.EFFECTIVE_TO
, FL.LOOKUP_CODE
, FL.MEANING
, VST.VISIT_ID
, VST.VISIT_NAME
, VST.VISIT_NUMBER
, VST.VISIT_TYPE_CODE
, VST.VISIT_TYPE_MEAN
, VST.START_DATE_TIME
, UE.QA_INSPECTION_TYPE_CODE
, UC.NAME
, VST.ORGANIZATION_ID
, ORG.ORGANIZATION_NAME
, VST.DEPARTMENT_ID
, BOM.DESCRIPTION
, WO.ACTUAL_START_DATE
, WO.ACTUAL_END_DATE
, UE.QA_PLAN_ID
, UE.QA_COLLECTION_ID
, WO.WORKORDER_ID
, WO.JOB_NUMBER
, WO.INVENTORY_ITEM_ID
, WO.WO_PART_NUMBER
, WO.ITEM_DESCRIPTION
, WO.ITEM_INSTANCE_ID
, WO.ITEM_INSTANCE_NUMBER
, WO.SERIAL_NUMBER
, WO.JOB_STATUS_CODE
, WO.JOB_STATUS_MEANING
, WO.JOB_DESCRIPTION
, VT.VISIT_TASK_ID
, VT.VISIT_TASK_NUMBER
, VT.VISIT_TASK_NAME
FROM BOM_DEPARTMENTS BOM
, ORG_ORGANIZATION_DEFINITIONS ORG
, AHL_UNIT_CONFIG_HEADERS UC
, FND_LOOKUP_VALUES_VL FL
, AHL_UE_DEFERRAL_DETAILS_V UE
, AHL_VISITS_INFO_V VST
, AHL_VISIT_TASKS_V VT
, AHL_WORKORDERS_V WO
WHERE BOM.DEPARTMENT_ID (+) = VST.DEPARTMENT_ID
AND ORG.ORGANIZATION_ID = VST.ORGANIZATION_ID
AND FL.LOOKUP_TYPE = 'AHL_PRD_MR_STATUS'
AND FL.LOOKUP_CODE = AHL_COMPLETIONS_PVT.GET_MR_STATUS( UE.UNIT_EFFECTIVITY_ID )
AND UC.CSI_ITEM_INSTANCE_ID (+) = UE.CSI_ITEM_INSTANCE_ID
AND UE.UNIT_EFFECTIVITY_ID = VT.UNIT_EFFECTIVITY_ID
AND VST.VISIT_ID = VT.VISIT_ID
AND VT.VISIT_TASK_ID = WO.VISIT_TASK_ID