eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AHL_SRH_MR_HISTORY_V

Source

View Text - Preformatted


SELECT UE.unit_effectivity_id unit_effectivity_id, UE.Object_version_number Object_version_number, UE.Csi_item_instance_id Csi_item_instance_id, UE.Mr_header_id Mr_header_id, UE.Cs_Incident_id Cs_Incident_id, DECODE (UE.Mr_header_id,NULL, (SELECT CS.INCIDENT_NUMBER FROM CS_INCIDENTS_ALL_B CS WHERE CS.INCIDENT_ID=UE.CS_INCIDENT_ID ), MR.TITLE) MR_TITLE, MR.VERSION_NUMBER MR_VERSION_NUMBER, (SELECT MRL.DESCRIPTION FROM AHL_MR_HEADERS_TL MRL WHERE MR.MR_HEADER_ID = MRL.MR_HEADER_ID AND MRL.LANGUAGE = USERENV('LANG') ) MR_DESCRIPTION, MR.CATEGORY_CODE MR_CATEGORY_CODE, (SELECT CAT.MEANING FROM FND_LOOKUP_VALUES CAT WHERE CAT.LOOKUP_TYPE = 'AHL_FMP_MR_CATEGORY' AND CAT.LANGUAGE = USERENV('LANG') AND CAT.LOOKUP_CODE = MR.CATEGORY_CODE ) MR_CATEGORY, MR.PROGRAM_TYPE_CODE MR_PROGRAM_TYPE_CODE, (SELECT PRG.MEANING FROM FND_LOOKUP_VALUES PRG WHERE PRG.LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_TYPE' AND PRG.LANGUAGE = USERENV('LANG') AND PRG.LOOKUP_CODE = MR.PROGRAM_TYPE_CODE ) MR_PROGRAM_TYPE, MR.PROGRAM_SUBTYPE_CODE MR_PROGRAM_SUBTYPE_CODE, (SELECT SUB.MEANING FROM FND_LOOKUP_VALUES SUB WHERE SUB.LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_SUBTYPE' AND SUB.LANGUAGE = USERENV('LANG') AND SUB.LOOKUP_CODE = MR.PROGRAM_SUBTYPE_CODE ) MR_PROGRAM_SUBTYPE, (SELECT CS.INCIDENT_NUMBER FROM CS_INCIDENTS_ALL_B CS WHERE CS.INCIDENT_ID=UE.CS_INCIDENT_ID ) incident_number, (SELECT CSI.INSTANCE_NUMBER FROM CSI_ITEM_INSTANCES CSI WHERE CSI.INSTANCE_ID=UE.CSI_ITEM_INSTANCE_ID ) Instance_number, MR.EFFECTIVE_FROM EFFECTIVE_FROM, MR.EFFECTIVE_TO EFFECTIVE_TO, RTRIM((AHL_COMPLETIONS_PVT.get_mr_status( UE.Unit_effectivity_id ))) STATUS_CODE, (SELECT FL.MEANING FROM FND_LOOKUP_VALUES FL WHERE FL.LOOKUP_TYPE='AHL_PRD_MR_STATUS' AND FL.LANGUAGE = USERENV('LANG') AND FL.LOOKUP_CODE =AHL_COMPLETIONS_PVT.get_mr_status( UE.Unit_effectivity_id ) ) STATUS, VST.Visit_id Visit_id, (SELECT VISIT_NAME FROM AHL_VISITS_TL WHERE VISIT_ID = VST.VISIT_ID AND LANGUAGE = USERENV('LANG') ) VISIT_NAME, VST.Visit_number Visit_number, VST.VISIT_TYPE_CODE VISIT_TYPE_CODE, (SELECT FLVT.MEANING FROM FND_LOOKUP_VALUES FLVT WHERE FLVT.LOOKUP_TYPE='AHL_PLANNING_VISIT_TYPE' AND FLVT.LANGUAGE = USERENV('LANG') AND FLVT.LOOKUP_CODE =VST.VISIT_TYPE_CODE ) VISIT_TYPE_MEAN, VST.Start_date_time VISIT_START_DATE, MR.Qa_inspection_type Qa_inspection_type, (SELECT UC.NAME FROM AHL_UNIT_CONFIG_HEADERS UC WHERE UC.Csi_item_instance_id = UE.Csi_item_instance_id ) UNIT_NAME, VST.Organization_id Organization_id, (SELECT ORG.ORGANIZATION_NAME FROM ORG_ORGANIZATION_DEFINITIONS ORG WHERE ORG.ORGANIZATION_ID = VST.ORGANIZATION_ID ) Organization_name, VST.Department_id Department_id, (SELECT BOM.Description FROM BOM_DEPARTMENTS BOM WHERE BOM.DEPARTMENT_ID=VST.DEPARTMENT_ID ) DEPARTMENT_NAME, WO.actual_start_date actual_start_date, WO.actual_end_date actual_end_date, ( CASE WHEN MR.QA_INSPECTION_TYPE IS NULL THEN NULL WHEN UE.qa_collection_id IS NOT NULL THEN (SELECT qa.plan_id FROM qa_results qa WHERE qa.collection_id = UE.qa_collection_id AND rownum < 2 ) ELSE (SELECT QP.plan_id FROM QA_PLANS QP, QA_PLAN_TRANSACTIONS QPT, QA_PLAN_COLLECTION_TRIGGERS QPCT WHERE ((QP.EFFECTIVE_TO IS NULL AND TRUNC(SYSDATE) >= QP.EFFECTIVE_FROM) OR (QP.EFFECTIVE_FROM IS NULL AND TRUNC(SYSDATE) <= QP.EFFECTIVE_TO) OR (QP.EFFECTIVE_FROM IS NOT NULL AND QP.EFFECTIVE_TO IS NOT NULL AND TRUNC(SYSDATE) BETWEEN QP.EFFECTIVE_FROM AND QP.EFFECTIVE_TO) OR (QP.EFFECTIVE_FROM IS NULL AND QP.EFFECTIVE_TO IS NULL )) AND QP.plan_id = QPT.plan_id AND QPT.plan_transaction_id = QPCT.plan_transaction_id AND QP.organization_id = VST.Organization_id AND QPT.transaction_number IN (9999,2001) AND QPCT.collection_trigger_id = 87 AND QPCT.low_value = MR.QA_INSPECTION_TYPE GROUP BY qp.plan_id, qpt.transaction_number HAVING transaction_number = MAX(transaction_number) ) END) PLAN_ID, UE.Qa_Collection_id COLLECTION_ID, WO.WORKORDER_ID WORKORDER_ID, WO.WORKORDER_NAME JOB_NUMBER, CSI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, (SELECT MTLI.CONCATENATED_SEGMENTS FROM MTL_SYSTEM_ITEMS_KFV MTLI WHERE mtli.inventory_item_id = csi.inventory_item_id AND MTLI.ORGANIZATION_ID = VST.ORGANIZATION_ID ) WO_PART_NUMBER, (SELECT MTLI.DESCRIPTION FROM MTL_SYSTEM_ITEMS_KFV MTLI WHERE mtli.inventory_item_id = csi.inventory_item_id AND MTLI.ORGANIZATION_ID = VST.ORGANIZATION_ID ) ITEM_DESCRIPTION, NVL(VT.INSTANCE_ID,VST.ITEM_INSTANCE_ID) ITEM_INSTANCE_ID, CSI.INSTANCE_NUMBER ITEM_INSTANCE_NUMBER, CSI.SERIAL_NUMBER SERIAL_NUMBER, WO.STATUS_CODE JOB_STATUS_CODE, (SELECT MLU.MEANING FROM FND_LOOKUP_VALUES MLU WHERE MLU.LOOKUP_TYPE='AHL_JOB_STATUS' AND MLU.LANGUAGE = USERENV('LANG') AND MLU.LOOKUP_CODE =WO.STATUS_CODE ) JOB_STATUS_MEANING, (SELECT WDJ.DESCRIPTION FROM WIP_DISCRETE_JOBS WDJ WHERE WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID ) JOB_DESCRIPTION, VT.VISIT_TASK_ID VISIT_TASK_ID, VT.VISIT_TASK_NUMBER VISIT_TASK_NUMBER, (SELECT VISIT_TASK_NAME FROM AHL_VISIT_TASKS_TL VTL WHERE VISIT_TASK_ID = VT.VISIT_TASK_ID AND VTL.LANGUAGE = USERENV('LANG') ) VISIT_TASK_NAME FROM AHL_UNIT_EFFECTIVITIES_B UE , AHL_MR_HEADERS_B MR, AHL_VISITS_B VST, AHL_VISIT_TASKS_B VT, CSI_ITEM_INSTANCES CSI, AHL_WORKORDERS WO, AHL_UNIT_DEFERRALS_B ORIG_DEF, AHL_UNIT_DEFERRALS_B DEF WHERE UE.APPLICATION_USG_CODE =RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE'))) AND WO.MASTER_WORKORDER_FLAG='N' AND WO.STATUS_CODE NOT IN ('17','22') AND UE.MR_HEADER_ID = MR.MR_HEADER_ID(+) AND ORIG_DEF.UNIT_DEFERRAL_TYPE(+) ='DEFERRAL' AND ORIG_DEF.UNIT_EFFECTIVITY_ID(+) = UE.ORIG_DEFERRAL_UE_ID AND DEF.UNIT_DEFERRAL_TYPE(+) ='DEFERRAL' AND DEF.UNIT_EFFECTIVITY_ID(+) = UE.Unit_effectivity_id AND UE.Unit_effectivity_id = VT.Unit_effectivity_id AND VST.visit_id = VT.visit_id AND VT.TASK_TYPE_CODE <> 'STAGE' AND VT.visit_task_id = WO.visit_task_id AND UE.Csi_item_instance_id = CSI.INSTANCE_ID

View Text - HTML Formatted

SELECT UE.UNIT_EFFECTIVITY_ID UNIT_EFFECTIVITY_ID
, UE.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, UE.CSI_ITEM_INSTANCE_ID CSI_ITEM_INSTANCE_ID
, UE.MR_HEADER_ID MR_HEADER_ID
, UE.CS_INCIDENT_ID CS_INCIDENT_ID
, DECODE (UE.MR_HEADER_ID
, NULL
, (SELECT CS.INCIDENT_NUMBER
FROM CS_INCIDENTS_ALL_B CS
WHERE CS.INCIDENT_ID=UE.CS_INCIDENT_ID )
, MR.TITLE) MR_TITLE
, MR.VERSION_NUMBER MR_VERSION_NUMBER
, (SELECT MRL.DESCRIPTION
FROM AHL_MR_HEADERS_TL MRL
WHERE MR.MR_HEADER_ID = MRL.MR_HEADER_ID
AND MRL.LANGUAGE = USERENV('LANG') ) MR_DESCRIPTION
, MR.CATEGORY_CODE MR_CATEGORY_CODE
, (SELECT CAT.MEANING
FROM FND_LOOKUP_VALUES CAT
WHERE CAT.LOOKUP_TYPE = 'AHL_FMP_MR_CATEGORY'
AND CAT.LANGUAGE = USERENV('LANG')
AND CAT.LOOKUP_CODE = MR.CATEGORY_CODE ) MR_CATEGORY
, MR.PROGRAM_TYPE_CODE MR_PROGRAM_TYPE_CODE
, (SELECT PRG.MEANING
FROM FND_LOOKUP_VALUES PRG
WHERE PRG.LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_TYPE'
AND PRG.LANGUAGE = USERENV('LANG')
AND PRG.LOOKUP_CODE = MR.PROGRAM_TYPE_CODE ) MR_PROGRAM_TYPE
, MR.PROGRAM_SUBTYPE_CODE MR_PROGRAM_SUBTYPE_CODE
, (SELECT SUB.MEANING
FROM FND_LOOKUP_VALUES SUB
WHERE SUB.LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_SUBTYPE'
AND SUB.LANGUAGE = USERENV('LANG')
AND SUB.LOOKUP_CODE = MR.PROGRAM_SUBTYPE_CODE ) MR_PROGRAM_SUBTYPE
, (SELECT CS.INCIDENT_NUMBER
FROM CS_INCIDENTS_ALL_B CS
WHERE CS.INCIDENT_ID=UE.CS_INCIDENT_ID ) INCIDENT_NUMBER
, (SELECT CSI.INSTANCE_NUMBER
FROM CSI_ITEM_INSTANCES CSI
WHERE CSI.INSTANCE_ID=UE.CSI_ITEM_INSTANCE_ID ) INSTANCE_NUMBER
, MR.EFFECTIVE_FROM EFFECTIVE_FROM
, MR.EFFECTIVE_TO EFFECTIVE_TO
, RTRIM((AHL_COMPLETIONS_PVT.GET_MR_STATUS( UE.UNIT_EFFECTIVITY_ID ))) STATUS_CODE
, (SELECT FL.MEANING
FROM FND_LOOKUP_VALUES FL
WHERE FL.LOOKUP_TYPE='AHL_PRD_MR_STATUS'
AND FL.LANGUAGE = USERENV('LANG')
AND FL.LOOKUP_CODE =AHL_COMPLETIONS_PVT.GET_MR_STATUS( UE.UNIT_EFFECTIVITY_ID ) ) STATUS
, VST.VISIT_ID VISIT_ID
, (SELECT VISIT_NAME
FROM AHL_VISITS_TL
WHERE VISIT_ID = VST.VISIT_ID
AND LANGUAGE = USERENV('LANG') ) VISIT_NAME
, VST.VISIT_NUMBER VISIT_NUMBER
, VST.VISIT_TYPE_CODE VISIT_TYPE_CODE
, (SELECT FLVT.MEANING
FROM FND_LOOKUP_VALUES FLVT
WHERE FLVT.LOOKUP_TYPE='AHL_PLANNING_VISIT_TYPE'
AND FLVT.LANGUAGE = USERENV('LANG')
AND FLVT.LOOKUP_CODE =VST.VISIT_TYPE_CODE ) VISIT_TYPE_MEAN
, VST.START_DATE_TIME VISIT_START_DATE
, MR.QA_INSPECTION_TYPE QA_INSPECTION_TYPE
, (SELECT UC.NAME
FROM AHL_UNIT_CONFIG_HEADERS UC
WHERE UC.CSI_ITEM_INSTANCE_ID = UE.CSI_ITEM_INSTANCE_ID ) UNIT_NAME
, VST.ORGANIZATION_ID ORGANIZATION_ID
, (SELECT ORG.ORGANIZATION_NAME
FROM ORG_ORGANIZATION_DEFINITIONS ORG
WHERE ORG.ORGANIZATION_ID = VST.ORGANIZATION_ID ) ORGANIZATION_NAME
, VST.DEPARTMENT_ID DEPARTMENT_ID
, (SELECT BOM.DESCRIPTION
FROM BOM_DEPARTMENTS BOM
WHERE BOM.DEPARTMENT_ID=VST.DEPARTMENT_ID ) DEPARTMENT_NAME
, WO.ACTUAL_START_DATE ACTUAL_START_DATE
, WO.ACTUAL_END_DATE ACTUAL_END_DATE
, ( CASE WHEN MR.QA_INSPECTION_TYPE IS NULL THEN NULL WHEN UE.QA_COLLECTION_ID IS NOT NULL THEN (SELECT QA.PLAN_ID
FROM QA_RESULTS QA
WHERE QA.COLLECTION_ID = UE.QA_COLLECTION_ID
AND ROWNUM < 2 ) ELSE (SELECT QP.PLAN_ID
FROM QA_PLANS QP
, QA_PLAN_TRANSACTIONS QPT
, QA_PLAN_COLLECTION_TRIGGERS QPCT
WHERE ((QP.EFFECTIVE_TO IS NULL
AND TRUNC(SYSDATE) >= QP.EFFECTIVE_FROM) OR (QP.EFFECTIVE_FROM IS NULL
AND TRUNC(SYSDATE) <= QP.EFFECTIVE_TO) OR (QP.EFFECTIVE_FROM IS NOT NULL
AND QP.EFFECTIVE_TO IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN QP.EFFECTIVE_FROM
AND QP.EFFECTIVE_TO) OR (QP.EFFECTIVE_FROM IS NULL
AND QP.EFFECTIVE_TO IS NULL ))
AND QP.PLAN_ID = QPT.PLAN_ID
AND QPT.PLAN_TRANSACTION_ID = QPCT.PLAN_TRANSACTION_ID
AND QP.ORGANIZATION_ID = VST.ORGANIZATION_ID
AND QPT.TRANSACTION_NUMBER IN (9999
, 2001)
AND QPCT.COLLECTION_TRIGGER_ID = 87
AND QPCT.LOW_VALUE = MR.QA_INSPECTION_TYPE GROUP BY QP.PLAN_ID
, QPT.TRANSACTION_NUMBER HAVING TRANSACTION_NUMBER = MAX(TRANSACTION_NUMBER) ) END) PLAN_ID
, UE.QA_COLLECTION_ID COLLECTION_ID
, WO.WORKORDER_ID WORKORDER_ID
, WO.WORKORDER_NAME JOB_NUMBER
, CSI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, (SELECT MTLI.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MTLI
WHERE MTLI.INVENTORY_ITEM_ID = CSI.INVENTORY_ITEM_ID
AND MTLI.ORGANIZATION_ID = VST.ORGANIZATION_ID ) WO_PART_NUMBER
, (SELECT MTLI.DESCRIPTION
FROM MTL_SYSTEM_ITEMS_KFV MTLI
WHERE MTLI.INVENTORY_ITEM_ID = CSI.INVENTORY_ITEM_ID
AND MTLI.ORGANIZATION_ID = VST.ORGANIZATION_ID ) ITEM_DESCRIPTION
, NVL(VT.INSTANCE_ID
, VST.ITEM_INSTANCE_ID) ITEM_INSTANCE_ID
, CSI.INSTANCE_NUMBER ITEM_INSTANCE_NUMBER
, CSI.SERIAL_NUMBER SERIAL_NUMBER
, WO.STATUS_CODE JOB_STATUS_CODE
, (SELECT MLU.MEANING
FROM FND_LOOKUP_VALUES MLU
WHERE MLU.LOOKUP_TYPE='AHL_JOB_STATUS'
AND MLU.LANGUAGE = USERENV('LANG')
AND MLU.LOOKUP_CODE =WO.STATUS_CODE ) JOB_STATUS_MEANING
, (SELECT WDJ.DESCRIPTION
FROM WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID ) JOB_DESCRIPTION
, VT.VISIT_TASK_ID VISIT_TASK_ID
, VT.VISIT_TASK_NUMBER VISIT_TASK_NUMBER
, (SELECT VISIT_TASK_NAME
FROM AHL_VISIT_TASKS_TL VTL
WHERE VISIT_TASK_ID = VT.VISIT_TASK_ID
AND VTL.LANGUAGE = USERENV('LANG') ) VISIT_TASK_NAME
FROM AHL_UNIT_EFFECTIVITIES_B UE
, AHL_MR_HEADERS_B MR
, AHL_VISITS_B VST
, AHL_VISIT_TASKS_B VT
, CSI_ITEM_INSTANCES CSI
, AHL_WORKORDERS WO
, AHL_UNIT_DEFERRALS_B ORIG_DEF
, AHL_UNIT_DEFERRALS_B DEF
WHERE UE.APPLICATION_USG_CODE =RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE')))
AND WO.MASTER_WORKORDER_FLAG='N'
AND WO.STATUS_CODE NOT IN ('17'
, '22')
AND UE.MR_HEADER_ID = MR.MR_HEADER_ID(+)
AND ORIG_DEF.UNIT_DEFERRAL_TYPE(+) ='DEFERRAL'
AND ORIG_DEF.UNIT_EFFECTIVITY_ID(+) = UE.ORIG_DEFERRAL_UE_ID
AND DEF.UNIT_DEFERRAL_TYPE(+) ='DEFERRAL'
AND DEF.UNIT_EFFECTIVITY_ID(+) = UE.UNIT_EFFECTIVITY_ID
AND UE.UNIT_EFFECTIVITY_ID = VT.UNIT_EFFECTIVITY_ID
AND VST.VISIT_ID = VT.VISIT_ID
AND VT.TASK_TYPE_CODE <> 'STAGE'
AND VT.VISIT_TASK_ID = WO.VISIT_TASK_ID
AND UE.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID