VIEW: APPS.AHL_MR_INSTANCES_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, (select cit.name || '-' || cs.incident_number from cs_incidents_all_vl cs, cs_incident_types_vl cit WHERE cs.incident_type_id = cit.incident_type_id AND cs.incident_id = UE.Cs_Incident_id), UE.Title ), DECODE( UE.Mr_header_id, null, UE.Cs_incident_summary, UE.description ), UE.Cs_incident_number, UE.Instance_number, FL.Lookup_code, FL.Meaning, VST.Visit_id, VST.Visit_number, VST.Start_date_time, UE.Qa_inspection_type_code, UE.Cs_incident_number, AHL_UTILITY_PVT.get_unit_name(UE.Csi_item_instance_id), VST.Organization_id, ORG.Organization_name, VST.Department_id, (select BOM.Description FROM BOM_DEPARTMENTS BOM WHERE BOM.department_id = VST.department_id) Description, WIP.scheduled_start_date, WIP.scheduled_completion_date, DECODE(FL.lookup_code, 'ACCOMPLISHED', WO.actual_start_date, 'ALL_JOBS_COMPLETE', WO.actual_start_date, 'ALL_JOBS_CLOSED', WO.actual_start_date, NULL), DECODE(FL.lookup_code, 'ACCOMPLISHED', WO.actual_end_date, 'ALL_JOBS_COMPLETE', WO.actual_end_date, 'ALL_JOBS_CLOSED', WO.actual_end_date, NULL), UE.Qa_Plan_id, UE.Qa_Collection_id FROM (SELECT ORGANIZATION_ID, organization_name FROM org_organization_definitions WHERE NVL (operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id()) ORG, FND_LOOKUP_VALUES_VL FL, WIP_DISCRETE_JOBS WIP, AHL_WORKORDERS WO, AHL_UE_DEFERRAL_DETAILS_V UE, AHL_VISITS_B VST, AHL_VISIT_TASKS_B VT WHERE VST.Organization_id = ORG.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 VST.visit_id = VT.visit_id AND WIP.wip_entity_id = WO.wip_entity_id AND WO.visit_task_id = VT.visit_task_id AND UE.Unit_effectivity_id = VT.unit_effectivity_id AND VT.task_type_code IN ( 'SUMMARY' , 'UNASSOCIATED' )

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
, (SELECT CIT.NAME || '-' || CS.INCIDENT_NUMBER
FROM CS_INCIDENTS_ALL_VL CS
, CS_INCIDENT_TYPES_VL CIT
WHERE CS.INCIDENT_TYPE_ID = CIT.INCIDENT_TYPE_ID
AND CS.INCIDENT_ID = UE.CS_INCIDENT_ID)
, UE.TITLE )
, DECODE( UE.MR_HEADER_ID
, NULL
, UE.CS_INCIDENT_SUMMARY
, UE.DESCRIPTION )
, UE.CS_INCIDENT_NUMBER
, UE.INSTANCE_NUMBER
, FL.LOOKUP_CODE
, FL.MEANING
, VST.VISIT_ID
, VST.VISIT_NUMBER
, VST.START_DATE_TIME
, UE.QA_INSPECTION_TYPE_CODE
, UE.CS_INCIDENT_NUMBER
, AHL_UTILITY_PVT.GET_UNIT_NAME(UE.CSI_ITEM_INSTANCE_ID)
, VST.ORGANIZATION_ID
, ORG.ORGANIZATION_NAME
, VST.DEPARTMENT_ID
, (SELECT BOM.DESCRIPTION
FROM BOM_DEPARTMENTS BOM
WHERE BOM.DEPARTMENT_ID = VST.DEPARTMENT_ID) DESCRIPTION
, WIP.SCHEDULED_START_DATE
, WIP.SCHEDULED_COMPLETION_DATE
, DECODE(FL.LOOKUP_CODE
, 'ACCOMPLISHED'
, WO.ACTUAL_START_DATE
, 'ALL_JOBS_COMPLETE'
, WO.ACTUAL_START_DATE
, 'ALL_JOBS_CLOSED'
, WO.ACTUAL_START_DATE
, NULL)
, DECODE(FL.LOOKUP_CODE
, 'ACCOMPLISHED'
, WO.ACTUAL_END_DATE
, 'ALL_JOBS_COMPLETE'
, WO.ACTUAL_END_DATE
, 'ALL_JOBS_CLOSED'
, WO.ACTUAL_END_DATE
, NULL)
, UE.QA_PLAN_ID
, UE.QA_COLLECTION_ID
FROM (SELECT ORGANIZATION_ID
, ORGANIZATION_NAME
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE NVL (OPERATING_UNIT
, MO_GLOBAL.GET_CURRENT_ORG_ID()) = MO_GLOBAL.GET_CURRENT_ORG_ID()) ORG
, FND_LOOKUP_VALUES_VL FL
, WIP_DISCRETE_JOBS WIP
, AHL_WORKORDERS WO
, AHL_UE_DEFERRAL_DETAILS_V UE
, AHL_VISITS_B VST
, AHL_VISIT_TASKS_B VT
WHERE VST.ORGANIZATION_ID = ORG.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 VST.VISIT_ID = VT.VISIT_ID
AND WIP.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WO.VISIT_TASK_ID = VT.VISIT_TASK_ID
AND UE.UNIT_EFFECTIVITY_ID = VT.UNIT_EFFECTIVITY_ID
AND VT.TASK_TYPE_CODE IN ( 'SUMMARY'
, 'UNASSOCIATED' )