VIEW: APPS.AHL_UNIT_EFFECTIVITIES_V

Source

View Text - Preformatted

SELECT B.UNIT_EFFECTIVITY_ID, B.OBJECT_VERSION_NUMBER, B.LAST_UPDATE_DATE, B.LAST_UPDATED_BY, B.CREATION_DATE, B.CREATED_BY, B.LAST_UPDATE_LOGIN, B.MR_INTERVAL_ID, B.CSI_ITEM_INSTANCE_ID, B.MR_EFFECTIVITY_ID, B.MR_HEADER_ID, AHL_UMP_UTIL_PKG.GET_UNITNAME(B.CSI_ITEM_INSTANCE_ID) UNIT_NAME, MTL.CONCATENATED_SEGMENTS PART_NUMBER, CSI.SERIAL_NUMBER, CSI.INSTANCE_NUMBER, B.EARLIEST_DUE_DATE, B.DUE_DATE, B.LATEST_DUE_DATE, B.STATUS_CODE, F4.MEANING STATUS, B.MESSAGE_CODE, F5.MEANING MESSAGE, B.TOLERANCE_FLAG, F6.MEANING TOLERANCE, B.DATE_RUN, B.DUE_COUNTER_VALUE, C.NAME COUNTER_NAME, to_number(NULL) NET_READING, T.REMARKS, B.PRECEDING_UE_ID, B.FORECAST_SEQUENCE, B.REPETITIVE_MR_FLAG, B.SET_DUE_DATE, B.ACCOMPLISHED_DATE, MRB.TITLE TITLE, MRB.VERSION_NUMBER, MRT.DESCRIPTION DESCRIPTION, MRB.PROGRAM_TYPE_CODE PROGRAM_TYPE_CODE, F1.MEANING PROGRAM_TYPE, MRB.IMPLEMENT_STATUS_CODE, F2.MEANING IMPLEMENT_STATUS, MRB.CATEGORY_CODE, F3.MEANING CATEGORY, MRB.REVISION, B.SERVICE_LINE_ID, CLEB.LINE_NUMBER, B.PROGRAM_MR_HEADER_ID, B.CANCEL_REASON_CODE, PGM.TITLE PROGRAM_TITLE, CHRB.CONTRACT_NUMBER, CHRB.CONTRACT_NUMBER_MODIFIER, ORG_TITLE.TITLE, ORG_TITLE.CSI_ITEM_INSTANCE_ID ORIG_UE_INSTANCE_ID, ( SELECT MR.TITLE FROM AHL_MR_HEADERS_B MR, AHL_UNIT_EFFECTIVITIES_B DEP WHERE DEP.MR_HEADER_ID = MR.MR_HEADER_ID AND DEP.UNIT_EFFECTIVITY_ID = B.PRECEDING_UE_ID ) DEPENDANT_TITLE, B.ATTRIBUTE_CATEGORY, B.ATTRIBUTE1, B.ATTRIBUTE2, B.ATTRIBUTE3, B.ATTRIBUTE4, B.ATTRIBUTE5, B.ATTRIBUTE6, B.ATTRIBUTE7, B.ATTRIBUTE8, B.ATTRIBUTE9, B.ATTRIBUTE10, B.ATTRIBUTE11, B.ATTRIBUTE12, B.ATTRIBUTE13, B.ATTRIBUTE14, B.ATTRIBUTE15, B.DEFER_FROM_UE_ID, ( SELECT MR.TITLE FROM AHL_MR_HEADERS_B MR, AHL_UNIT_EFFECTIVITIES_B DEF WHERE DEF.MR_HEADER_ID = MR.MR_HEADER_ID AND DEF.UNIT_EFFECTIVITY_ID = B.DEFER_FROM_UE_ID ) DEFER_FROM_MR_TITLE, B.QA_COLLECTION_ID, TO_NUMBER(NULL) CS_INCIDENT_ID, TO_CHAR(NULL) CS_INCIDENT_NUMBER, TO_CHAR(NULL) CS_INCIDENT_SUMMARY, B.APPLICATION_USG_CODE, B.OBJECT_TYPE, B.COUNTER_ID, B.MANUALLY_PLANNED_FLAG, MPF.MEANING MANUALLY_PLANNED_DESC FROM AHL_UNIT_EFFECTIVITIES_B B, AHL_UNIT_EFFECTIVITIES_TL T, AHL_MR_HEADERS_B MRB, AHL_MR_HEADERS_TL MRT, AHL_MR_INTERVALS I, CSI_COUNTER_TEMPLATE_VL C, CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTL, OKC_K_HEADERS_ALL_B CHRB, OKC_K_LINES_B CLEB, AHL_MR_HEADERS_B PGM, FND_LOOKUP_VALUES F1, FND_LOOKUP_VALUES F2, FND_LOOKUP_VALUES F3, FND_LOOKUP_VALUES F4, FND_LOOKUP_VALUES F5, FND_LOOKUP_VALUES F6, FND_LOOKUP_VALUES MPF, ( SELECT MR.TITLE, REL.RELATED_UE_ID, ORIG.CSI_ITEM_INSTANCE_ID FROM AHL_MR_HEADERS_B MR, AHL_UNIT_EFFECTIVITIES_B ORIG, AHL_UE_RELATIONSHIPS REL WHERE ORIG.UNIT_EFFECTIVITY_ID = REL.ORIGINATOR_UE_ID AND ORIG.MR_HEADER_ID = MR.MR_HEADER_ID AND ORIG.OBJECT_TYPE = 'MR' UNION ALL SELECT CIT.NAME || '-' || CS.INCIDENT_NUMBER TITLE, REL.RELATED_UE_ID, ORIG.CSI_ITEM_INSTANCE_ID FROM CS_INCIDENTS_ALL_B CS, CS_INCIDENT_TYPES_TL CIT, AHL_UNIT_EFFECTIVITIES_B ORIG, AHL_UE_RELATIONSHIPS REL WHERE ORIG.UNIT_EFFECTIVITY_ID = REL.ORIGINATOR_UE_ID AND ORIG.CS_INCIDENT_ID = CS.INCIDENT_ID AND CS.INCIDENT_TYPE_ID = CIT.INCIDENT_TYPE_ID AND CIT.LANGUAGE = USERENV('LANG') AND ORIG.OBJECT_TYPE = 'SR' ) ORG_TITLE WHERE B.MR_HEADER_ID = MRB.MR_HEADER_ID AND MRB.MR_HEADER_ID = MRT.MR_HEADER_ID AND MRT.LANGUAGE = USERENV('LANG') AND B.UNIT_EFFECTIVITY_ID = T.UNIT_EFFECTIVITY_ID AND T.LANGUAGE = USERENV('LANG') AND B.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID AND CSI.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID AND CSI.INV_MASTER_ORGANIZATION_ID = MTL.ORGANIZATION_ID AND B.MR_INTERVAL_ID = I.MR_INTERVAL_ID(+) AND I.COUNTER_ID = C.COUNTER_ID(+) AND B.SERVICE_LINE_ID = CLEB.ID (+) AND CLEB.DNZ_CHR_ID = CHRB.ID(+) AND B.PROGRAM_MR_HEADER_ID = PGM.MR_HEADER_ID(+) AND B.APPLICATION_USG_CODE = FND_PROFILE.VALUE('AHL_APPLN_USAGE') AND B.OBJECT_TYPE = 'MR' AND F1.LOOKUP_CODE = MRB.PROGRAM_TYPE_CODE AND F1.LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_TYPE' AND F1.LANGUAGE = USERENV('LANG') AND F2.LOOKUP_CODE = MRB.IMPLEMENT_STATUS_CODE AND F2.LOOKUP_TYPE = 'AHL_FMP_MR_IMPLEMENT_STATUS' AND F2.LANGUAGE = USERENV('LANG') AND F3.LOOKUP_CODE = MRB.CATEGORY_CODE AND F3.LOOKUP_TYPE = 'AHL_FMP_MR_CATEGORY' AND F3.LANGUAGE = USERENV('LANG') AND F4.LOOKUP_CODE (+) = B.STATUS_CODE AND F4.LOOKUP_TYPE (+) = 'AHL_UNIT_EFFECTIVITY_STATUS' AND F4.LANGUAGE (+) = USERENV('LANG') AND F5.LOOKUP_CODE (+) = B.MESSAGE_CODE AND F5.LOOKUP_TYPE (+) = 'AHL_UMP_MESSAGE' AND F5.LANGUAGE (+)= USERENV('LANG') AND F6.LOOKUP_CODE (+) = B.TOLERANCE_FLAG AND F6.LOOKUP_TYPE (+) = 'AHL_YES_NO_TYPE' AND F6.LANGUAGE (+)= USERENV('LANG') AND MPF.LOOKUP_CODE (+) = B.MANUALLY_PLANNED_FLAG AND MPF.LOOKUP_TYPE (+) = 'AHL_YES_NO_TYPE' AND MPF.LANGUAGE (+) = USERENV('LANG') AND B.UNIT_EFFECTIVITY_ID = ORG_TITLE.RELATED_UE_ID (+) UNION ALL SELECT B.UNIT_EFFECTIVITY_ID, B.OBJECT_VERSION_NUMBER, B.LAST_UPDATE_DATE, B.LAST_UPDATED_BY, B.CREATION_DATE, B.CREATED_BY, B.LAST_UPDATE_LOGIN, B.MR_INTERVAL_ID, B.CSI_ITEM_INSTANCE_ID, B.MR_EFFECTIVITY_ID, B.MR_HEADER_ID, AHL_UMP_UTIL_PKG.GET_UNITNAME(B.CSI_ITEM_INSTANCE_ID) UNIT_NAME, MTL.CONCATENATED_SEGMENTS PART_NUMBER, CSI.SERIAL_NUMBER, CSI.INSTANCE_NUMBER, B.EARLIEST_DUE_DATE, B.DUE_DATE, B.LATEST_DUE_DATE, B.STATUS_CODE, F2.MEANING STATUS, B.MESSAGE_CODE, F3.MEANING MESSAGE, B.TOLERANCE_FLAG, F4.MEANING TOLERANCE, B.DATE_RUN, B.DUE_COUNTER_VALUE, TO_CHAR(NULL) COUNTER_NAME, TO_NUMBER(NULL) NET_READING, T.REMARKS, B.PRECEDING_UE_ID, B.FORECAST_SEQUENCE, B.REPETITIVE_MR_FLAG, B.SET_DUE_DATE, B.ACCOMPLISHED_DATE, (CITT.NAME || '-' || CSB.INCIDENT_NUMBER) TITLE, TO_NUMBER(NULL) VERSION_NUMBER, CST.SUMMARY DESCRIPTION, 'NON-ROUTINE' PROGRAM_TYPE_CODE, ( SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_TYPE' AND LOOKUP_CODE = 'NON-ROUTINE' AND LANGUAGE = USERENV('LANG') ) PROGRAM_TYPE, TO_CHAR(NULL) IMPLEMENT_STATUS_CODE, TO_CHAR(NULL) IMPLEMENT_STATUS, TO_CHAR(NULL) CATEGORY_CODE, TO_CHAR(NULL) CATEGORY, TO_CHAR(NULL) REVISION, TO_NUMBER(NULL) SERVICE_LINE_ID, TO_CHAR(NULL) LINE_NUMBER, TO_NUMBER(NULL) PROGRAM_MR_HEADER_ID, B.CANCEL_REASON_CODE, TO_CHAR(NULL) PROGRAM_TITLE, TO_CHAR(NULL) CONTRACT_NUMBER, TO_CHAR(NULL) CONTRACT_NUMBER_MODIFIER, TO_CHAR(NULL) ORIGINATOR_TITLE, TO_NUMBER(NULL) ORIG_UE_INSTANCE_ID, TO_CHAR(NULL) DEPENDANT_TITLE, B.ATTRIBUTE_CATEGORY, B.ATTRIBUTE1, B.ATTRIBUTE2, B.ATTRIBUTE3, B.ATTRIBUTE4, B.ATTRIBUTE5, B.ATTRIBUTE6, B.ATTRIBUTE7, B.ATTRIBUTE8, B.ATTRIBUTE9, B.ATTRIBUTE10, B.ATTRIBUTE11, B.ATTRIBUTE12, B.ATTRIBUTE13, B.ATTRIBUTE14, B.ATTRIBUTE15, B.DEFER_FROM_UE_ID, ( SELECT MR.TITLE FROM AHL_MR_HEADERS_B MR, AHL_UNIT_EFFECTIVITIES_B DEF WHERE DEF.UNIT_EFFECTIVITY_ID = B.DEFER_FROM_UE_ID AND DEF.MR_HEADER_ID = MR.MR_HEADER_ID ) DEFER_FROM_MR_TITLE, B.QA_COLLECTION_ID, CSB.INCIDENT_ID CS_INCIDENT_ID, CSB.INCIDENT_NUMBER CS_INCIDENT_NUMBER, CST.SUMMARY CS_INCIDENT_SUMMARY, B.APPLICATION_USG_CODE, B.OBJECT_TYPE, B.COUNTER_ID, 'Y' MANUALLY_PLANNED_FLAG, ( SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_YES_NO_TYPE' AND LOOKUP_CODE = 'Y' AND LANGUAGE = USERENV('LANG') ) MANUALLY_PLANNED_DESC FROM CS_INCIDENT_TYPES_B CITB, CS_INCIDENT_TYPES_TL CITT, CS_INCIDENTS_ALL_B CSB, CS_INCIDENTS_ALL_TL CST, MTL_SYSTEM_ITEMS_KFV MTL, CSI_ITEM_INSTANCES CSI, AHL_UNIT_EFFECTIVITIES_B B, AHL_UNIT_EFFECTIVITIES_TL T, FND_LOOKUP_VALUES F2, FND_LOOKUP_VALUES F3, FND_LOOKUP_VALUES F4 WHERE B.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID AND CSI.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID AND CSI.INV_MASTER_ORGANIZATION_ID = MTL.ORGANIZATION_ID AND B.CS_INCIDENT_ID = CSB.INCIDENT_ID AND CSB.INCIDENT_ID = CST.INCIDENT_ID AND CST.LANGUAGE = USERENV('LANG') AND CITB.INCIDENT_TYPE_ID = CSB.INCIDENT_TYPE_ID AND CITB.INCIDENT_TYPE_ID = CITT.INCIDENT_TYPE_ID AND CITT.LANGUAGE = USERENV('LANG') AND B.APPLICATION_USG_CODE = FND_PROFILE.VALUE('AHL_APPLN_USAGE') AND B.OBJECT_TYPE = 'SR' AND F2.LOOKUP_CODE (+) = B.STATUS_CODE AND F2.LOOKUP_TYPE (+)= 'AHL_UNIT_EFFECTIVITY_STATUS' AND F2.LANGUAGE (+) = USERENV('LANG') AND F3.LOOKUP_CODE (+) = B.MESSAGE_CODE AND F3.LOOKUP_TYPE (+) = 'AHL_UMP_MESSAGE' AND F3.LANGUAGE (+) = USERENV('LANG') AND F4.LOOKUP_CODE (+) = B.TOLERANCE_FLAG AND F4.LOOKUP_TYPE (+) = 'AHL_YES_NO_TYPE' AND F4.LANGUAGE (+) = USERENV('LANG') AND B.UNIT_EFFECTIVITY_ID = T.UNIT_EFFECTIVITY_ID AND T.LANGUAGE = USERENV('LANG')

View Text - HTML Formatted

SELECT B.UNIT_EFFECTIVITY_ID
, B.OBJECT_VERSION_NUMBER
, B.LAST_UPDATE_DATE
, B.LAST_UPDATED_BY
, B.CREATION_DATE
, B.CREATED_BY
, B.LAST_UPDATE_LOGIN
, B.MR_INTERVAL_ID
, B.CSI_ITEM_INSTANCE_ID
, B.MR_EFFECTIVITY_ID
, B.MR_HEADER_ID
, AHL_UMP_UTIL_PKG.GET_UNITNAME(B.CSI_ITEM_INSTANCE_ID) UNIT_NAME
, MTL.CONCATENATED_SEGMENTS PART_NUMBER
, CSI.SERIAL_NUMBER
, CSI.INSTANCE_NUMBER
, B.EARLIEST_DUE_DATE
, B.DUE_DATE
, B.LATEST_DUE_DATE
, B.STATUS_CODE
, F4.MEANING STATUS
, B.MESSAGE_CODE
, F5.MEANING MESSAGE
, B.TOLERANCE_FLAG
, F6.MEANING TOLERANCE
, B.DATE_RUN
, B.DUE_COUNTER_VALUE
, C.NAME COUNTER_NAME
, TO_NUMBER(NULL) NET_READING
, T.REMARKS
, B.PRECEDING_UE_ID
, B.FORECAST_SEQUENCE
, B.REPETITIVE_MR_FLAG
, B.SET_DUE_DATE
, B.ACCOMPLISHED_DATE
, MRB.TITLE TITLE
, MRB.VERSION_NUMBER
, MRT.DESCRIPTION DESCRIPTION
, MRB.PROGRAM_TYPE_CODE PROGRAM_TYPE_CODE
, F1.MEANING PROGRAM_TYPE
, MRB.IMPLEMENT_STATUS_CODE
, F2.MEANING IMPLEMENT_STATUS
, MRB.CATEGORY_CODE
, F3.MEANING CATEGORY
, MRB.REVISION
, B.SERVICE_LINE_ID
, CLEB.LINE_NUMBER
, B.PROGRAM_MR_HEADER_ID
, B.CANCEL_REASON_CODE
, PGM.TITLE PROGRAM_TITLE
, CHRB.CONTRACT_NUMBER
, CHRB.CONTRACT_NUMBER_MODIFIER
, ORG_TITLE.TITLE
, ORG_TITLE.CSI_ITEM_INSTANCE_ID ORIG_UE_INSTANCE_ID
, ( SELECT MR.TITLE
FROM AHL_MR_HEADERS_B MR
, AHL_UNIT_EFFECTIVITIES_B DEP
WHERE DEP.MR_HEADER_ID = MR.MR_HEADER_ID
AND DEP.UNIT_EFFECTIVITY_ID = B.PRECEDING_UE_ID ) DEPENDANT_TITLE
, B.ATTRIBUTE_CATEGORY
, B.ATTRIBUTE1
, B.ATTRIBUTE2
, B.ATTRIBUTE3
, B.ATTRIBUTE4
, B.ATTRIBUTE5
, B.ATTRIBUTE6
, B.ATTRIBUTE7
, B.ATTRIBUTE8
, B.ATTRIBUTE9
, B.ATTRIBUTE10
, B.ATTRIBUTE11
, B.ATTRIBUTE12
, B.ATTRIBUTE13
, B.ATTRIBUTE14
, B.ATTRIBUTE15
, B.DEFER_FROM_UE_ID
, ( SELECT MR.TITLE
FROM AHL_MR_HEADERS_B MR
, AHL_UNIT_EFFECTIVITIES_B DEF
WHERE DEF.MR_HEADER_ID = MR.MR_HEADER_ID
AND DEF.UNIT_EFFECTIVITY_ID = B.DEFER_FROM_UE_ID ) DEFER_FROM_MR_TITLE
, B.QA_COLLECTION_ID
, TO_NUMBER(NULL) CS_INCIDENT_ID
, TO_CHAR(NULL) CS_INCIDENT_NUMBER
, TO_CHAR(NULL) CS_INCIDENT_SUMMARY
, B.APPLICATION_USG_CODE
, B.OBJECT_TYPE
, B.COUNTER_ID
, B.MANUALLY_PLANNED_FLAG
, MPF.MEANING MANUALLY_PLANNED_DESC
FROM AHL_UNIT_EFFECTIVITIES_B B
, AHL_UNIT_EFFECTIVITIES_TL T
, AHL_MR_HEADERS_B MRB
, AHL_MR_HEADERS_TL MRT
, AHL_MR_INTERVALS I
, CSI_COUNTER_TEMPLATE_VL C
, CSI_ITEM_INSTANCES CSI
, MTL_SYSTEM_ITEMS_KFV MTL
, OKC_K_HEADERS_ALL_B CHRB
, OKC_K_LINES_B CLEB
, AHL_MR_HEADERS_B PGM
, FND_LOOKUP_VALUES F1
, FND_LOOKUP_VALUES F2
, FND_LOOKUP_VALUES F3
, FND_LOOKUP_VALUES F4
, FND_LOOKUP_VALUES F5
, FND_LOOKUP_VALUES F6
, FND_LOOKUP_VALUES MPF
, ( SELECT MR.TITLE
, REL.RELATED_UE_ID
, ORIG.CSI_ITEM_INSTANCE_ID
FROM AHL_MR_HEADERS_B MR
, AHL_UNIT_EFFECTIVITIES_B ORIG
, AHL_UE_RELATIONSHIPS REL
WHERE ORIG.UNIT_EFFECTIVITY_ID = REL.ORIGINATOR_UE_ID
AND ORIG.MR_HEADER_ID = MR.MR_HEADER_ID
AND ORIG.OBJECT_TYPE = 'MR' UNION ALL SELECT CIT.NAME || '-' || CS.INCIDENT_NUMBER TITLE
, REL.RELATED_UE_ID
, ORIG.CSI_ITEM_INSTANCE_ID
FROM CS_INCIDENTS_ALL_B CS
, CS_INCIDENT_TYPES_TL CIT
, AHL_UNIT_EFFECTIVITIES_B ORIG
, AHL_UE_RELATIONSHIPS REL
WHERE ORIG.UNIT_EFFECTIVITY_ID = REL.ORIGINATOR_UE_ID
AND ORIG.CS_INCIDENT_ID = CS.INCIDENT_ID
AND CS.INCIDENT_TYPE_ID = CIT.INCIDENT_TYPE_ID
AND CIT.LANGUAGE = USERENV('LANG')
AND ORIG.OBJECT_TYPE = 'SR' ) ORG_TITLE
WHERE B.MR_HEADER_ID = MRB.MR_HEADER_ID
AND MRB.MR_HEADER_ID = MRT.MR_HEADER_ID
AND MRT.LANGUAGE = USERENV('LANG')
AND B.UNIT_EFFECTIVITY_ID = T.UNIT_EFFECTIVITY_ID
AND T.LANGUAGE = USERENV('LANG')
AND B.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
AND CSI.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
AND CSI.INV_MASTER_ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND B.MR_INTERVAL_ID = I.MR_INTERVAL_ID(+)
AND I.COUNTER_ID = C.COUNTER_ID(+)
AND B.SERVICE_LINE_ID = CLEB.ID (+)
AND CLEB.DNZ_CHR_ID = CHRB.ID(+)
AND B.PROGRAM_MR_HEADER_ID = PGM.MR_HEADER_ID(+)
AND B.APPLICATION_USG_CODE = FND_PROFILE.VALUE('AHL_APPLN_USAGE')
AND B.OBJECT_TYPE = 'MR'
AND F1.LOOKUP_CODE = MRB.PROGRAM_TYPE_CODE
AND F1.LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_TYPE'
AND F1.LANGUAGE = USERENV('LANG')
AND F2.LOOKUP_CODE = MRB.IMPLEMENT_STATUS_CODE
AND F2.LOOKUP_TYPE = 'AHL_FMP_MR_IMPLEMENT_STATUS'
AND F2.LANGUAGE = USERENV('LANG')
AND F3.LOOKUP_CODE = MRB.CATEGORY_CODE
AND F3.LOOKUP_TYPE = 'AHL_FMP_MR_CATEGORY'
AND F3.LANGUAGE = USERENV('LANG')
AND F4.LOOKUP_CODE (+) = B.STATUS_CODE
AND F4.LOOKUP_TYPE (+) = 'AHL_UNIT_EFFECTIVITY_STATUS'
AND F4.LANGUAGE (+) = USERENV('LANG')
AND F5.LOOKUP_CODE (+) = B.MESSAGE_CODE
AND F5.LOOKUP_TYPE (+) = 'AHL_UMP_MESSAGE'
AND F5.LANGUAGE (+)= USERENV('LANG')
AND F6.LOOKUP_CODE (+) = B.TOLERANCE_FLAG
AND F6.LOOKUP_TYPE (+) = 'AHL_YES_NO_TYPE'
AND F6.LANGUAGE (+)= USERENV('LANG')
AND MPF.LOOKUP_CODE (+) = B.MANUALLY_PLANNED_FLAG
AND MPF.LOOKUP_TYPE (+) = 'AHL_YES_NO_TYPE'
AND MPF.LANGUAGE (+) = USERENV('LANG')
AND B.UNIT_EFFECTIVITY_ID = ORG_TITLE.RELATED_UE_ID (+) UNION ALL SELECT B.UNIT_EFFECTIVITY_ID
, B.OBJECT_VERSION_NUMBER
, B.LAST_UPDATE_DATE
, B.LAST_UPDATED_BY
, B.CREATION_DATE
, B.CREATED_BY
, B.LAST_UPDATE_LOGIN
, B.MR_INTERVAL_ID
, B.CSI_ITEM_INSTANCE_ID
, B.MR_EFFECTIVITY_ID
, B.MR_HEADER_ID
, AHL_UMP_UTIL_PKG.GET_UNITNAME(B.CSI_ITEM_INSTANCE_ID) UNIT_NAME
, MTL.CONCATENATED_SEGMENTS PART_NUMBER
, CSI.SERIAL_NUMBER
, CSI.INSTANCE_NUMBER
, B.EARLIEST_DUE_DATE
, B.DUE_DATE
, B.LATEST_DUE_DATE
, B.STATUS_CODE
, F2.MEANING STATUS
, B.MESSAGE_CODE
, F3.MEANING MESSAGE
, B.TOLERANCE_FLAG
, F4.MEANING TOLERANCE
, B.DATE_RUN
, B.DUE_COUNTER_VALUE
, TO_CHAR(NULL) COUNTER_NAME
, TO_NUMBER(NULL) NET_READING
, T.REMARKS
, B.PRECEDING_UE_ID
, B.FORECAST_SEQUENCE
, B.REPETITIVE_MR_FLAG
, B.SET_DUE_DATE
, B.ACCOMPLISHED_DATE
, (CITT.NAME || '-' || CSB.INCIDENT_NUMBER) TITLE
, TO_NUMBER(NULL) VERSION_NUMBER
, CST.SUMMARY DESCRIPTION
, 'NON-ROUTINE' PROGRAM_TYPE_CODE
, ( SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_TYPE'
AND LOOKUP_CODE = 'NON-ROUTINE'
AND LANGUAGE = USERENV('LANG') ) PROGRAM_TYPE
, TO_CHAR(NULL) IMPLEMENT_STATUS_CODE
, TO_CHAR(NULL) IMPLEMENT_STATUS
, TO_CHAR(NULL) CATEGORY_CODE
, TO_CHAR(NULL) CATEGORY
, TO_CHAR(NULL) REVISION
, TO_NUMBER(NULL) SERVICE_LINE_ID
, TO_CHAR(NULL) LINE_NUMBER
, TO_NUMBER(NULL) PROGRAM_MR_HEADER_ID
, B.CANCEL_REASON_CODE
, TO_CHAR(NULL) PROGRAM_TITLE
, TO_CHAR(NULL) CONTRACT_NUMBER
, TO_CHAR(NULL) CONTRACT_NUMBER_MODIFIER
, TO_CHAR(NULL) ORIGINATOR_TITLE
, TO_NUMBER(NULL) ORIG_UE_INSTANCE_ID
, TO_CHAR(NULL) DEPENDANT_TITLE
, B.ATTRIBUTE_CATEGORY
, B.ATTRIBUTE1
, B.ATTRIBUTE2
, B.ATTRIBUTE3
, B.ATTRIBUTE4
, B.ATTRIBUTE5
, B.ATTRIBUTE6
, B.ATTRIBUTE7
, B.ATTRIBUTE8
, B.ATTRIBUTE9
, B.ATTRIBUTE10
, B.ATTRIBUTE11
, B.ATTRIBUTE12
, B.ATTRIBUTE13
, B.ATTRIBUTE14
, B.ATTRIBUTE15
, B.DEFER_FROM_UE_ID
, ( SELECT MR.TITLE
FROM AHL_MR_HEADERS_B MR
, AHL_UNIT_EFFECTIVITIES_B DEF
WHERE DEF.UNIT_EFFECTIVITY_ID = B.DEFER_FROM_UE_ID
AND DEF.MR_HEADER_ID = MR.MR_HEADER_ID ) DEFER_FROM_MR_TITLE
, B.QA_COLLECTION_ID
, CSB.INCIDENT_ID CS_INCIDENT_ID
, CSB.INCIDENT_NUMBER CS_INCIDENT_NUMBER
, CST.SUMMARY CS_INCIDENT_SUMMARY
, B.APPLICATION_USG_CODE
, B.OBJECT_TYPE
, B.COUNTER_ID
, 'Y' MANUALLY_PLANNED_FLAG
, ( SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_YES_NO_TYPE'
AND LOOKUP_CODE = 'Y'
AND LANGUAGE = USERENV('LANG') ) MANUALLY_PLANNED_DESC
FROM CS_INCIDENT_TYPES_B CITB
, CS_INCIDENT_TYPES_TL CITT
, CS_INCIDENTS_ALL_B CSB
, CS_INCIDENTS_ALL_TL CST
, MTL_SYSTEM_ITEMS_KFV MTL
, CSI_ITEM_INSTANCES CSI
, AHL_UNIT_EFFECTIVITIES_B B
, AHL_UNIT_EFFECTIVITIES_TL T
, FND_LOOKUP_VALUES F2
, FND_LOOKUP_VALUES F3
, FND_LOOKUP_VALUES F4
WHERE B.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
AND CSI.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
AND CSI.INV_MASTER_ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND B.CS_INCIDENT_ID = CSB.INCIDENT_ID
AND CSB.INCIDENT_ID = CST.INCIDENT_ID
AND CST.LANGUAGE = USERENV('LANG')
AND CITB.INCIDENT_TYPE_ID = CSB.INCIDENT_TYPE_ID
AND CITB.INCIDENT_TYPE_ID = CITT.INCIDENT_TYPE_ID
AND CITT.LANGUAGE = USERENV('LANG')
AND B.APPLICATION_USG_CODE = FND_PROFILE.VALUE('AHL_APPLN_USAGE')
AND B.OBJECT_TYPE = 'SR'
AND F2.LOOKUP_CODE (+) = B.STATUS_CODE
AND F2.LOOKUP_TYPE (+)= 'AHL_UNIT_EFFECTIVITY_STATUS'
AND F2.LANGUAGE (+) = USERENV('LANG')
AND F3.LOOKUP_CODE (+) = B.MESSAGE_CODE
AND F3.LOOKUP_TYPE (+) = 'AHL_UMP_MESSAGE'
AND F3.LANGUAGE (+) = USERENV('LANG')
AND F4.LOOKUP_CODE (+) = B.TOLERANCE_FLAG
AND F4.LOOKUP_TYPE (+) = 'AHL_YES_NO_TYPE'
AND F4.LANGUAGE (+) = USERENV('LANG')
AND B.UNIT_EFFECTIVITY_ID = T.UNIT_EFFECTIVITY_ID
AND T.LANGUAGE = USERENV('LANG')