VIEW: APPS.AHL_UE_DEFERRAL_DETAILS_V

Source

View Text - Preformatted

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','CANCELLED') 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, MR.CATEGORY_CODE, MR.CATEGORY, MR.PROGRAM_TYPE_CODE, MR.PROGRAM_TYPE, MR.PROGRAM_SUBTYPE_CODE, MR.PROGRAM_SUBTYPE, MR.EFFECTIVE_FROM, MR.EFFECTIVE_TO, 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 tsk.task_type_code IN ('SUMMARY','UNASSOCIATED') and vst.status_code NOT IN ('DELETED','CANCELLED') 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, UE.manually_planned_flag, UE.ORIGINATING_WO_ID From AHL_MR_HEADERS_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

View Text - HTML Formatted

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'
, 'CANCELLED') 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
, MR.CATEGORY_CODE
, MR.CATEGORY
, MR.PROGRAM_TYPE_CODE
, MR.PROGRAM_TYPE
, MR.PROGRAM_SUBTYPE_CODE
, MR.PROGRAM_SUBTYPE
, MR.EFFECTIVE_FROM
, MR.EFFECTIVE_TO
, 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 TSK.TASK_TYPE_CODE IN ('SUMMARY'
, 'UNASSOCIATED')
AND VST.STATUS_CODE NOT IN ('DELETED'
, 'CANCELLED')
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
, UE.MANUALLY_PLANNED_FLAG
, UE.ORIGINATING_WO_ID
FROM AHL_MR_HEADERS_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