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, AHL_PRD_PRINT_PVT.Get_JC_File_Location(DECODE(UE.object_type,'MR','MR','NR'), UE.unit_effectivity_id) FILE_LOCATION, AHL_PRD_PRINT_PVT.Generated_JobCard_Exists(DECODE(UE.object_type,'MR','MR','NR'), UE.unit_effectivity_id) JOB_CARD_EXISTS, AHL_PRD_PRINT_PVT.Get_Latest_JCG_Status(DECODE(UE.object_type,'MR','MR','NR'), UE.unit_effectivity_id) LATEST_JC_STATUS, AHL_PRD_PRINT_PVT.Get_JCG_Error_Msg(DECODE(UE.object_type,'MR','MR','NR'), UE.unit_effectivity_id) JOB_CARD_ERROR_MSG, (select msi.concatenated_segments from mtl_system_items_kfv msi where msi.inventory_item_id = ii.inventory_item_id and msi.organization_id = ii.inv_master_organization_id) Item_Number, ii.serial_number, ue.version_number, ue.effective_to, mrhdr.program_type_code, lkp2.meaning program_type, mrhdr.service_type_code, lkp.meaning service_type 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, CSI_ITEM_INSTANCES II, AHL_MR_HEADERS_B MRHDR, FND_LOOKUP_VALUES_VL LKP, FND_LOOKUP_VALUES_VL LKP2 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.status_code NOT IN ('17','22') 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' ) AND UE.Csi_item_instance_id = ii.instance_id AND UE.mr_header_id = MRHDR.mr_header_id (+) AND MRHDR.service_type_code = LKP.lookup_code (+) AND LKP.lookup_type(+) = 'AHL_FMP_MR_SERVICE_TYPE' AND NVL(LKP.ENABLED_FLAG(+),'N') = 'Y' AND SYSDATE BETWEEN LKP.start_date_active(+) and nvl(LKP.end_date_active(+),sysdate+1) AND MRHDR.program_type_code = LKP2.lookup_code (+) AND LKP2.lookup_type(+) = 'AHL_FMP_MR_PROGRAM_TYPE' AND NVL(LKP2.ENABLED_FLAG(+),'N') = 'Y' AND SYSDATE BETWEEN LKP2.start_date_active(+) and nvl(LKP2.end_date_active(+),sysdate+1)

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
, AHL_PRD_PRINT_PVT.GET_JC_FILE_LOCATION(DECODE(UE.OBJECT_TYPE
, 'MR'
, 'MR'
, 'NR')
, UE.UNIT_EFFECTIVITY_ID) FILE_LOCATION
, AHL_PRD_PRINT_PVT.GENERATED_JOBCARD_EXISTS(DECODE(UE.OBJECT_TYPE
, 'MR'
, 'MR'
, 'NR')
, UE.UNIT_EFFECTIVITY_ID) JOB_CARD_EXISTS
, AHL_PRD_PRINT_PVT.GET_LATEST_JCG_STATUS(DECODE(UE.OBJECT_TYPE
, 'MR'
, 'MR'
, 'NR')
, UE.UNIT_EFFECTIVITY_ID) LATEST_JC_STATUS
, AHL_PRD_PRINT_PVT.GET_JCG_ERROR_MSG(DECODE(UE.OBJECT_TYPE
, 'MR'
, 'MR'
, 'NR')
, UE.UNIT_EFFECTIVITY_ID) JOB_CARD_ERROR_MSG
, (SELECT MSI.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MSI
WHERE MSI.INVENTORY_ITEM_ID = II.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = II.INV_MASTER_ORGANIZATION_ID) ITEM_NUMBER
, II.SERIAL_NUMBER
, UE.VERSION_NUMBER
, UE.EFFECTIVE_TO
, MRHDR.PROGRAM_TYPE_CODE
, LKP2.MEANING PROGRAM_TYPE
, MRHDR.SERVICE_TYPE_CODE
, LKP.MEANING SERVICE_TYPE
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
, CSI_ITEM_INSTANCES II
, AHL_MR_HEADERS_B MRHDR
, FND_LOOKUP_VALUES_VL LKP
, FND_LOOKUP_VALUES_VL LKP2
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.STATUS_CODE NOT IN ('17'
, '22')
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' )
AND UE.CSI_ITEM_INSTANCE_ID = II.INSTANCE_ID
AND UE.MR_HEADER_ID = MRHDR.MR_HEADER_ID (+)
AND MRHDR.SERVICE_TYPE_CODE = LKP.LOOKUP_CODE (+)
AND LKP.LOOKUP_TYPE(+) = 'AHL_FMP_MR_SERVICE_TYPE'
AND NVL(LKP.ENABLED_FLAG(+)
, 'N') = 'Y'
AND SYSDATE BETWEEN LKP.START_DATE_ACTIVE(+)
AND NVL(LKP.END_DATE_ACTIVE(+)
, SYSDATE+1)
AND MRHDR.PROGRAM_TYPE_CODE = LKP2.LOOKUP_CODE (+)
AND LKP2.LOOKUP_TYPE(+) = 'AHL_FMP_MR_PROGRAM_TYPE'
AND NVL(LKP2.ENABLED_FLAG(+)
, 'N') = 'Y'
AND SYSDATE BETWEEN LKP2.START_DATE_ACTIVE(+)
AND NVL(LKP2.END_DATE_ACTIVE(+)
, SYSDATE+1)