View: AHL_UE_DEFERRAL_DETAILS_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	View to retrieve the deferral and unit effectivity details.
Implementation/DBA Data:	APPS.AHL_UE_DEFERRAL_DETAILS_V

View Text

SELECT UE.UNIT_EFFECTIVITY_ID
, UE.OBJECT_VERSION_NUMBER
, UE.CSI_ITEM_INSTANCE_ID
, UE.MR_HEADER_ID
, CSI.INSTANCE_NUMBER
, UE.EARLIEST_DUE_DATE
, UE.DUE_DATE
, UE.LATEST_DUE_DATE
, UE.STATUS_CODE
, F1.MEANING
, ( CASE WHEN UE.STATUS_CODE IN ('ACCOMPLISHED'
, 'DEFERRED'
, 'TERMINATED') THEN UE.STATUS_CODE WHEN UE.ORIG_DEFERRAL_UE_ID IS NOT NULL THEN ORIG_DEF.APPROVAL_STATUS_CODE WHEN DEF.APPROVAL_STATUS_CODE IS NOT NULL THEN DEF.APPROVAL_STATUS_CODE ELSE UE.STATUS_CODE END)UMP_STATUS_CODE
, DECODE(ORIG_DEFERRAL_UE_ID
, NULL
, NVL(DEF.APPROVAL_STATUS_CODE
, UE.STATUS_CODE)
, ORIG_DEF.APPROVAL_STATUS_CODE) DEF_STATUS_CODE
, DECODE(ORIG_DEFERRAL_UE_ID
, NULL
, DECODE(DEF.APPROVAL_STATUS_CODE
, NULL
, F1.MEANING
, F2.MEANING)
, F3.MEANING) DEF_STATUS
, MR.REPETITIVE_FLAG
, MR.TITLE
, MR.VERSION_NUMBER
, MR.DESCRIPTION
, MR.REVISION
, MR.QA_INSPECTION_TYPE_CODE
, UE.ORIG_DEFERRAL_UE_ID
, UE.QA_COLLECTION_ID
, UE.CS_INCIDENT_ID
, CS.INCIDENT_NUMBER CS_INCIDENT_NUMBER
, CS.SUMMARY CS_INCIDENT_SUMMARY
, (CASE WHEN MR.QA_INSPECTION_TYPE_CODE 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_VAL_V QP
, QA_PLAN_TRANSACTIONS QPT
, QA_PLAN_COLLECTION_TRIGGERS QPCT
WHERE QP.PLAN_ID = QPT.PLAN_ID
AND QPT.PLAN_TRANSACTION_ID = QPCT.PLAN_TRANSACTION_ID
AND QP.ORGANIZATION_ID = ( SELECT ORGANIZATION_ID
FROM AHL_VISITS_B VST
, AHL_VISIT_TASKS_B TSK
WHERE VST.VISIT_ID = TSK.VISIT_ID
AND TSK.UNIT_EFFECTIVITY_ID = UE.UNIT_EFFECTIVITY_ID
AND ROWNUM < 2)
AND QPT.TRANSACTION_NUMBER IN (9999
, 2001)
AND QPCT.COLLECTION_TRIGGER_ID = 87
AND QPCT.LOW_VALUE = MR.QA_INSPECTION_TYPE_CODE GROUP BY QP.PLAN_ID
, QPT.TRANSACTION_NUMBER HAVING TRANSACTION_NUMBER = MAX(TRANSACTION_NUMBER)) END) QA_PLAN_ID
, UE.APPLICATION_USG_CODE
, UE.OBJECT_TYPE
, UE.ATTRIBUTE_CATEGORY
, UE.ATTRIBUTE1
, UE.ATTRIBUTE2
, UE.ATTRIBUTE3
, UE.ATTRIBUTE4
, UE.ATTRIBUTE5
, UE.ATTRIBUTE6
, UE.ATTRIBUTE7
, UE.ATTRIBUTE8
, UE.ATTRIBUTE9
, UE.ATTRIBUTE10
, UE.ATTRIBUTE11
, UE.ATTRIBUTE12
, UE.ATTRIBUTE13
, UE.ATTRIBUTE14
, UE.ATTRIBUTE15
FROM AHL_MR_HEADERS_APP_V MR
, CSI_ITEM_INSTANCES CSI
, CS_INCIDENTS_ALL_VL CS
, AHL_UNIT_DEFERRALS_B ORIG_DEF
, AHL_UNIT_DEFERRALS_B DEF
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_UNIT_EFFECTIVITY_STATUS') F1
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_PRD_DF_APPR_STATUS_TYPES') F2
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_PRD_DF_APPR_STATUS_TYPES') F3
, AHL_UNIT_EFFECTIVITIES_APP_V UE
WHERE UE.MR_HEADER_ID = MR.MR_HEADER_ID(+)
AND UE.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
AND UE.CS_INCIDENT_ID = CS.INCIDENT_ID(+)
AND UE.ORIG_DEFERRAL_UE_ID = ORIG_DEF.UNIT_EFFECTIVITY_ID(+)
AND ORIG_DEF.UNIT_DEFERRAL_TYPE(+) = 'DEFERRAL'
AND UE.UNIT_EFFECTIVITY_ID = DEF.UNIT_EFFECTIVITY_ID(+)
AND DEF.UNIT_DEFERRAL_TYPE(+) = 'DEFERRAL'
AND UE.STATUS_CODE = F1.LOOKUP_CODE(+)
AND F2.LOOKUP_CODE(+) = DEF.APPROVAL_STATUS_CODE
AND F3.LOOKUP_CODE(+) = ORIG_DEF.APPROVAL_STATUS_CODE

Columns