VIEW: APPS.AHL_SR_NON_ROUTINES_V

Source

View Text - Preformatted

SELECT CS.INCIDENT_ID, CS.OBJECT_VERSION_NUMBER, CS.INCIDENT_NUMBER, CS.INCIDENT_STATUS_ID, (SELECT CS_STATUS.NAME FROM CS_INCIDENT_STATUSES_TL CS_STATUS WHERE LANGUAGE = USERENV('LANG') AND CS.INCIDENT_STATUS_ID = CS_STATUS.INCIDENT_STATUS_ID ) SR_INCIDENT_STATUS, CS.INCIDENT_TYPE_ID, (SELECT CS_TYPE.NAME FROM CS_INCIDENT_TYPES_TL CS_TYPE WHERE LANGUAGE = USERENV('LANG') AND CS.INCIDENT_TYPE_ID = CS_TYPE.INCIDENT_TYPE_ID) SR_INCIDENT_TYPE, CS.INCIDENT_DATE, CS.INCIDENT_OCCURRED_DATE, CS.INCIDENT_URGENCY_ID, (SELECT CS_URGENCY.NAME FROM CS_INCIDENT_URGENCIES_TL CS_URGENCY WHERE LANGUAGE = USERENV('LANG') AND CS.INCIDENT_URGENCY_ID = CS_URGENCY.INCIDENT_URGENCY_ID ) SR_INCIDENT_URGENCY, CS.INCIDENT_SEVERITY_ID, (SELECT CS_SEV.NAME FROM CS_INCIDENT_SEVERITIES_TL CS_SEV WHERE LANGUAGE = USERENV('LANG') AND CS.INCIDENT_SEVERITY_ID = CS_SEV.INCIDENT_SEVERITY_ID) SR_INCIDENT_SEVERITY, CS.EXPECTED_RESOLUTION_DATE, CS.CALLER_TYPE, (SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'CS_SR_CALLER_TYPE' AND lookup_code = CS.CALLER_TYPE AND language = userenv('LANG')) SR_CALLER_TYPE_NAME, CS.CUSTOMER_ID, PARTY.PARTY_NAME SR_CUSTOMER_NAME, SR_CONT.CONTACT_TYPE, (SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'CS_SR_CONTACT_TYPE' AND lookup_code = SR_CONT.CONTACT_TYPE AND language = userenv('LANG')) SR_CONTACT_TYPE_NAME, SR_CONT.PARTY_ID SR_CONTACT_PARTY_ID, DECODE(SR_CONT.CONTACT_TYPE,'PERSON',PARTY.PARTY_NAME, (SELECT CS_EMPL.FULL_NAME FROM PER_ALL_PEOPLE_F CS_EMPL WHERE TRUNC(SYSDATE) BETWEEN CS_EMPL.EFFECTIVE_START_DATE AND CS_EMPL.EFFECTIVE_END_DATE AND CS_EMPL.PERSON_ID = SR_CONT.PARTY_ID)) SR_CONTACT_NAME, CS.RESOLUTION_CODE, (SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'REQUEST_RESOLUTION_CODE' AND lookup_code = CS.RESOLUTION_CODE AND language = userenv('LANG')) SR_RESOLUTION_MEANING, CS.PROBLEM_CODE, (SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'REQUEST_PROBLEM_CODE' AND lookup_code = CS.PROBLEM_CODE AND language = userenv('LANG')) SR_PROBLEM_MEANING, (SELECT CS_TL.SUMMARY FROM CS_INCIDENTS_ALL_TL CS_TL WHERE LANGUAGE = USERENV('LANG') AND CS.INCIDENT_ID = CS_TL.INCIDENT_ID ) SR_PROBLEM_SUMMARY, UE.UNIT_EFFECTIVITY_ID UE_UNIT_EFFECTIVITY_ID, UE.OBJECT_VERSION_NUMBER UE_OBJECT_VERSION_NUMBER, UE.CSI_ITEM_INSTANCE_ID UE_CSI_INSTANCE_ID, CSI.INSTANCE_NUMBER UE_INSTANCE_NUMBER, CSI.UNIT_OF_MEASURE UE_INSTANCE_UOM, UCH.UNIT_CONFIG_HEADER_ID UE_UNIT_CONFIG_ID, UCH.NAME UE_UNIT_NAME, UCH.MASTER_CONFIG_ID UE_MASTER_CONFIG_ID, DECODE(UCH.UNIT_CONFIG_STATUS_CODE, 'QUARANTINE', 'T', 'DEACTIVATE_QUARANTINE', 'T', 'F') UE_UC_QUAR_FLAG, CSI.INVENTORY_ITEM_ID UE_INVENTORY_ITEM_ID, MTL.CONCATENATED_SEGMENTS UE_ITEM_NUMBER, CSI.SERIAL_NUMBER UE_SERIAL_NUMBER, UE.STATUS_CODE UE_STATUS_CODE, (SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'AHL_UNIT_EFFECTIVITY_STATUS' AND lookup_code = UE.STATUS_CODE AND language = userenv('LANG')) UE_STATUS, UE.DUE_DATE UE_DUE_DATE, UE.LOG_SERIES_CODE UE_LOG_SERIES_CODE, (SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'AHL_LOG_SERIES_CODE' AND lookup_code =UE.LOG_SERIES_CODE AND language = userenv('LANG')) UE_LOG_SERIES, UE.LOG_SERIES_NUMBER LOG_NUMBER, NVL2(UE.LOG_SERIES_CODE, AHL_UTILITY_PVT.GET_LOOKUP_MEANING('AHL_LOG_SERIES_CODE', UE.LOG_SERIES_CODE) ||'-'||UE.LOG_SERIES_NUMBER, NULL) UE_LOG_CODE_NUMBER, UE.FLIGHT_NUMBER, UE.MEL_CDL_TYPE_CODE, (SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'AHL_MEL_CDL_TYPE' AND lookup_code =UE.MEL_CDL_TYPE_CODE AND language = userenv('LANG')) UE_MEL_CDL_TYPE, UE.POSITION_PATH_ID UE_POSITION_PATH_ID, UE_POSREF_LKUP.LOOKUP_CODE UE_POSITION_REF_CODE, UE_POSREF_LKUP.MEANING UE_POSITION_REF_MEANING, UE_POSREF_LKUP.DESCRIPTION UE_POSITION_REF_DESC, UE.ATA_CODE UE_ATA_CODE, UE_ATA_LKUP.MEANING UE_ATA_MEANING, UE_ATA_LKUP.DESCRIPTION UE_ATA_DESC, ATASEQ.ATA_SEQUENCE_ID UE_ATA_SEQUENCE_ID, ATASEQ.MEL_CDL_HEADER_ID, ATASEQ.UE_ATA_HEADER_REVISION, ATASEQ.UE_ATA_REPAIR_CATEGORY, ATASEQ.UE_ATA_REPAIR_TIME, ATASEQ.UE_ATA_PC_NODE_NAME, ATASEQ.UE_DEFERRAL_TYPE, ATASEQ.UE_DEFERRAL_APPR_STATUS, ATASEQ.APPROVER_NOTES, AHL_UMP_NONROUTINES_PVT.GET_MEL_CDL_STATUS(UE.UNIT_EFFECTIVITY_ID, 'F') UE_MEL_CDL_STATUS, VISIT_TASK.ORGANIZATION_ID UE_CLEAR_STATION_ORG_ID, ORG.ORGANIZATION_CODE UE_CLEAR_STATION_ORG, WO.OWNING_DEPARTMENT UE_CLEAR_STATION_DEPT_ID, DEPT.DEPARTMENT_CODE UE_CLEAR_STATION_DEPT, VISIT_TASK.VISIT_NUMBER UE_VISIT_NUMBER, VISIT_TASK.VISIT_NAME UE_VISIT_NAME, VISIT_TASK.VISIT_TASK_NUMBER UE_VISIT_TASK_NUMBER, VISIT_TASK.VISIT_TASK_NAME UE_VISIT_TASK_NAME, VISIT_TASK.QUANTITY UE_VISIT_TASK_QUANTITY, NVL2(VISIT_TASK.VISIT_TASK_ID, 'T', 'F') SR_IN_VWP_FLAG, NVL2(WO.VISIT_TASK_ID, 'T', 'F') SR_IN_PRD_FLAG, (SELECT JNTT.NOTES_DETAIL FROM JTF_NOTES_B JNTB, JTF_NOTES_TL JNTT WHERE JNTB.JTF_NOTE_ID = JNTT.JTF_NOTE_ID AND JNTT.LANGUAGE = USERENV('LANG') AND JNTB.SOURCE_OBJECT_CODE = 'AHL_MEL_CDL' AND JNTB.SOURCE_OBJECT_ID = ATASEQ.MEL_CDL_ATA_SEQUENCE_ID) UE_ATA_REMARKS, UE.ORIGINATING_WO_ID, ( select workorder_name from ahl_workorders where workorder_id = UE.ORIGINATING_WO_ID ) ORIGINATING_WO_NAME, VISIT_TASK.VISIT_ID UE_VISIT_ID, WO.WORKORDER_NAME FROM CS_INCIDENTS_ALL_B CS, HZ_PARTIES PARTY, (SELECT INCIDENT_ID, PARTY_ID, CONTACT_TYPE FROM CS_HZ_SR_CONTACT_POINTS WHERE PRIMARY_FLAG = 'Y') SR_CONT, AHL_UNIT_EFFECTIVITIES_B UE, CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTL, ( SELECT UNIT_CONFIG_HEADER_ID, NAME, MASTER_CONFIG_ID, UNIT_CONFIG_STATUS_CODE FROM AHL_UNIT_CONFIG_HEADERS WHERE UNIT_CONFIG_STATUS_CODE NOT IN ('DRAFT', 'APPROVAL_REJECTED', 'APPROVAL_PENDING') ) UCH, ( SELECT LOOKUP_CODE, MEANING, DESCRIPTION FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_POSITION_REFERENCE' AND LANGUAGE = USERENV('LANG') ) UE_POSREF_LKUP, ( SELECT LOOKUP_CODE, MEANING, DESCRIPTION FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_ATA_CODE' AND LANGUAGE = USERENV('LANG') ) UE_ATA_LKUP, ( SELECT UDF.UNIT_EFFECTIVITY_ID, UDF.ATA_SEQUENCE_ID, UDF.UNIT_DEFERRAL_TYPE UE_DEFERRAL_TYPE, UDF.APPROVAL_STATUS_CODE UE_DEFERRAL_APPR_STATUS, ATA.MEL_CDL_ATA_SEQUENCE_ID, ATA.MEL_CDL_HEADER_ID, HDR.REVISION UE_ATA_HEADER_REVISION, SRURG.NAME UE_ATA_REPAIR_CATEGORY, REPCAT.REPAIR_TIME UE_ATA_REPAIR_TIME, PCN.NAME UE_ATA_PC_NODE_NAME, UDFTL.approver_notes FROM AHL_UNIT_DEFERRALS_B UDF, AHL_MEL_CDL_ATA_SEQUENCES ATA, AHL_MEL_CDL_HEADERS HDR, AHL_PC_NODES_B PCN, AHL_REPAIR_CATEGORIES REPCAT, CS_INCIDENT_URGENCIES_TL SRURG, AHL_UNIT_DEFERRALS_TL UDFTL WHERE UDF.UNIT_DEFERRAL_TYPE IN ('MEL', 'CDL') AND UDF.ATA_SEQUENCE_ID = ATA.MEL_CDL_ATA_SEQUENCE_ID AND ATA.MEL_CDL_HEADER_ID = HDR.MEL_CDL_HEADER_ID AND HDR.PC_NODE_ID = PCN.PC_NODE_ID AND ATA.REPAIR_CATEGORY_ID = REPCAT.REPAIR_CATEGORY_ID AND REPCAT.SR_URGENCY_ID = SRURG.INCIDENT_URGENCY_ID AND SRURG.LANGUAGE = USERENV('LANG') AND UDF.UNIT_DEFERRAL_ID = UDFTL.UNIT_DEFERRAL_ID AND UDFTL.LANGUAGE = USERENV('LANG') ) ATASEQ, ( SELECT VST.VISIT_NUMBER, VST.VISIT_ID, VST_TL.VISIT_NAME, VST.ORGANIZATION_ID, VST_TASK.UNIT_EFFECTIVITY_ID, VST_TASK.VISIT_TASK_ID, VST_TASK.QUANTITY, VST_TASK.VISIT_TASK_NUMBER, VST_TASK_TL.VISIT_TASK_NAME FROM AHL_VISITS_B VST, AHL_VISITS_TL VST_TL, AHL_VISIT_TASKS_B VST_TASK, AHL_VISIT_TASKS_TL VST_TASK_TL WHERE VST.VISIT_ID = VST_TL.VISIT_ID AND VST_TL.LANGUAGE = USERENV('LANG') AND VST_TASK.VISIT_TASK_ID = VST_TASK_TL.VISIT_TASK_ID AND VST_TASK_TL.LANGUAGE = USERENV('LANG') AND VST.VISIT_ID = VST_TASK.VISIT_ID AND VST_TASK.TASK_TYPE_CODE IN ('SUMMARY', 'UNASSOCIATED') ) VISIT_TASK, ( SELECT AHLWO.VISIT_TASK_ID, AHLWO.STATUS_CODE, AHLWO.WORKORDER_NAME, WIPDJ.OWNING_DEPARTMENT FROM AHL_WORKORDERS AHLWO, WIP_DISCRETE_JOBS WIPDJ WHERE AHLWO.WIP_ENTITY_ID = WIPDJ.WIP_ENTITY_ID ) WO, MTL_PARAMETERS ORG, BOM_DEPARTMENTS DEPT WHERE UE.OBJECT_TYPE = 'SR' AND UE.CS_INCIDENT_ID = CS.INCIDENT_ID AND CS.CUSTOMER_ID = PARTY.PARTY_ID AND SR_CONT.INCIDENT_ID(+) = CS.INCIDENT_ID AND UE.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID AND CSI.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID AND CSI.LAST_VLD_ORGANIZATION_ID = MTL.ORGANIZATION_ID AND UCH.UNIT_CONFIG_HEADER_ID (+) = UE.UNIT_CONFIG_HEADER_ID AND AHL_MC_PATH_POSITION_PVT.GET_POSREF_BY_ID(UE.POSITION_PATH_ID, 'T') = UE_POSREF_LKUP.LOOKUP_CODE(+) AND UE.ATA_CODE = UE_ATA_LKUP.LOOKUP_CODE(+) AND ATASEQ.UNIT_EFFECTIVITY_ID(+) = UE.UNIT_EFFECTIVITY_ID AND VISIT_TASK.ORGANIZATION_ID = ORG.ORGANIZATION_ID (+) AND WO.OWNING_DEPARTMENT = DEPT.DEPARTMENT_ID(+) AND UE.UNIT_EFFECTIVITY_ID = VISIT_TASK.UNIT_EFFECTIVITY_ID(+) AND VISIT_TASK.VISIT_TASK_ID = WO.VISIT_TASK_ID(+)

View Text - HTML Formatted

SELECT CS.INCIDENT_ID
, CS.OBJECT_VERSION_NUMBER
, CS.INCIDENT_NUMBER
, CS.INCIDENT_STATUS_ID
, (SELECT CS_STATUS.NAME
FROM CS_INCIDENT_STATUSES_TL CS_STATUS
WHERE LANGUAGE = USERENV('LANG')
AND CS.INCIDENT_STATUS_ID = CS_STATUS.INCIDENT_STATUS_ID ) SR_INCIDENT_STATUS
, CS.INCIDENT_TYPE_ID
, (SELECT CS_TYPE.NAME
FROM CS_INCIDENT_TYPES_TL CS_TYPE
WHERE LANGUAGE = USERENV('LANG')
AND CS.INCIDENT_TYPE_ID = CS_TYPE.INCIDENT_TYPE_ID) SR_INCIDENT_TYPE
, CS.INCIDENT_DATE
, CS.INCIDENT_OCCURRED_DATE
, CS.INCIDENT_URGENCY_ID
, (SELECT CS_URGENCY.NAME
FROM CS_INCIDENT_URGENCIES_TL CS_URGENCY
WHERE LANGUAGE = USERENV('LANG')
AND CS.INCIDENT_URGENCY_ID = CS_URGENCY.INCIDENT_URGENCY_ID ) SR_INCIDENT_URGENCY
, CS.INCIDENT_SEVERITY_ID
, (SELECT CS_SEV.NAME
FROM CS_INCIDENT_SEVERITIES_TL CS_SEV
WHERE LANGUAGE = USERENV('LANG')
AND CS.INCIDENT_SEVERITY_ID = CS_SEV.INCIDENT_SEVERITY_ID) SR_INCIDENT_SEVERITY
, CS.EXPECTED_RESOLUTION_DATE
, CS.CALLER_TYPE
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'CS_SR_CALLER_TYPE'
AND LOOKUP_CODE = CS.CALLER_TYPE
AND LANGUAGE = USERENV('LANG')) SR_CALLER_TYPE_NAME
, CS.CUSTOMER_ID
, PARTY.PARTY_NAME SR_CUSTOMER_NAME
, SR_CONT.CONTACT_TYPE
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'CS_SR_CONTACT_TYPE'
AND LOOKUP_CODE = SR_CONT.CONTACT_TYPE
AND LANGUAGE = USERENV('LANG')) SR_CONTACT_TYPE_NAME
, SR_CONT.PARTY_ID SR_CONTACT_PARTY_ID
, DECODE(SR_CONT.CONTACT_TYPE
, 'PERSON'
, PARTY.PARTY_NAME
, (SELECT CS_EMPL.FULL_NAME
FROM PER_ALL_PEOPLE_F CS_EMPL
WHERE TRUNC(SYSDATE) BETWEEN CS_EMPL.EFFECTIVE_START_DATE
AND CS_EMPL.EFFECTIVE_END_DATE
AND CS_EMPL.PERSON_ID = SR_CONT.PARTY_ID)) SR_CONTACT_NAME
, CS.RESOLUTION_CODE
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'REQUEST_RESOLUTION_CODE'
AND LOOKUP_CODE = CS.RESOLUTION_CODE
AND LANGUAGE = USERENV('LANG')) SR_RESOLUTION_MEANING
, CS.PROBLEM_CODE
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'REQUEST_PROBLEM_CODE'
AND LOOKUP_CODE = CS.PROBLEM_CODE
AND LANGUAGE = USERENV('LANG')) SR_PROBLEM_MEANING
, (SELECT CS_TL.SUMMARY
FROM CS_INCIDENTS_ALL_TL CS_TL
WHERE LANGUAGE = USERENV('LANG')
AND CS.INCIDENT_ID = CS_TL.INCIDENT_ID ) SR_PROBLEM_SUMMARY
, UE.UNIT_EFFECTIVITY_ID UE_UNIT_EFFECTIVITY_ID
, UE.OBJECT_VERSION_NUMBER UE_OBJECT_VERSION_NUMBER
, UE.CSI_ITEM_INSTANCE_ID UE_CSI_INSTANCE_ID
, CSI.INSTANCE_NUMBER UE_INSTANCE_NUMBER
, CSI.UNIT_OF_MEASURE UE_INSTANCE_UOM
, UCH.UNIT_CONFIG_HEADER_ID UE_UNIT_CONFIG_ID
, UCH.NAME UE_UNIT_NAME
, UCH.MASTER_CONFIG_ID UE_MASTER_CONFIG_ID
, DECODE(UCH.UNIT_CONFIG_STATUS_CODE
, 'QUARANTINE'
, 'T'
, 'DEACTIVATE_QUARANTINE'
, 'T'
, 'F') UE_UC_QUAR_FLAG
, CSI.INVENTORY_ITEM_ID UE_INVENTORY_ITEM_ID
, MTL.CONCATENATED_SEGMENTS UE_ITEM_NUMBER
, CSI.SERIAL_NUMBER UE_SERIAL_NUMBER
, UE.STATUS_CODE UE_STATUS_CODE
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_UNIT_EFFECTIVITY_STATUS'
AND LOOKUP_CODE = UE.STATUS_CODE
AND LANGUAGE = USERENV('LANG')) UE_STATUS
, UE.DUE_DATE UE_DUE_DATE
, UE.LOG_SERIES_CODE UE_LOG_SERIES_CODE
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_LOG_SERIES_CODE'
AND LOOKUP_CODE =UE.LOG_SERIES_CODE
AND LANGUAGE = USERENV('LANG')) UE_LOG_SERIES
, UE.LOG_SERIES_NUMBER LOG_NUMBER
, NVL2(UE.LOG_SERIES_CODE
, AHL_UTILITY_PVT.GET_LOOKUP_MEANING('AHL_LOG_SERIES_CODE'
, UE.LOG_SERIES_CODE) ||'-'||UE.LOG_SERIES_NUMBER
, NULL) UE_LOG_CODE_NUMBER
, UE.FLIGHT_NUMBER
, UE.MEL_CDL_TYPE_CODE
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_MEL_CDL_TYPE'
AND LOOKUP_CODE =UE.MEL_CDL_TYPE_CODE
AND LANGUAGE = USERENV('LANG')) UE_MEL_CDL_TYPE
, UE.POSITION_PATH_ID UE_POSITION_PATH_ID
, UE_POSREF_LKUP.LOOKUP_CODE UE_POSITION_REF_CODE
, UE_POSREF_LKUP.MEANING UE_POSITION_REF_MEANING
, UE_POSREF_LKUP.DESCRIPTION UE_POSITION_REF_DESC
, UE.ATA_CODE UE_ATA_CODE
, UE_ATA_LKUP.MEANING UE_ATA_MEANING
, UE_ATA_LKUP.DESCRIPTION UE_ATA_DESC
, ATASEQ.ATA_SEQUENCE_ID UE_ATA_SEQUENCE_ID
, ATASEQ.MEL_CDL_HEADER_ID
, ATASEQ.UE_ATA_HEADER_REVISION
, ATASEQ.UE_ATA_REPAIR_CATEGORY
, ATASEQ.UE_ATA_REPAIR_TIME
, ATASEQ.UE_ATA_PC_NODE_NAME
, ATASEQ.UE_DEFERRAL_TYPE
, ATASEQ.UE_DEFERRAL_APPR_STATUS
, ATASEQ.APPROVER_NOTES
, AHL_UMP_NONROUTINES_PVT.GET_MEL_CDL_STATUS(UE.UNIT_EFFECTIVITY_ID
, 'F') UE_MEL_CDL_STATUS
, VISIT_TASK.ORGANIZATION_ID UE_CLEAR_STATION_ORG_ID
, ORG.ORGANIZATION_CODE UE_CLEAR_STATION_ORG
, WO.OWNING_DEPARTMENT UE_CLEAR_STATION_DEPT_ID
, DEPT.DEPARTMENT_CODE UE_CLEAR_STATION_DEPT
, VISIT_TASK.VISIT_NUMBER UE_VISIT_NUMBER
, VISIT_TASK.VISIT_NAME UE_VISIT_NAME
, VISIT_TASK.VISIT_TASK_NUMBER UE_VISIT_TASK_NUMBER
, VISIT_TASK.VISIT_TASK_NAME UE_VISIT_TASK_NAME
, VISIT_TASK.QUANTITY UE_VISIT_TASK_QUANTITY
, NVL2(VISIT_TASK.VISIT_TASK_ID
, 'T'
, 'F') SR_IN_VWP_FLAG
, NVL2(WO.VISIT_TASK_ID
, 'T'
, 'F') SR_IN_PRD_FLAG
, (SELECT JNTT.NOTES_DETAIL
FROM JTF_NOTES_B JNTB
, JTF_NOTES_TL JNTT
WHERE JNTB.JTF_NOTE_ID = JNTT.JTF_NOTE_ID
AND JNTT.LANGUAGE = USERENV('LANG')
AND JNTB.SOURCE_OBJECT_CODE = 'AHL_MEL_CDL'
AND JNTB.SOURCE_OBJECT_ID = ATASEQ.MEL_CDL_ATA_SEQUENCE_ID) UE_ATA_REMARKS
, UE.ORIGINATING_WO_ID
, ( SELECT WORKORDER_NAME
FROM AHL_WORKORDERS
WHERE WORKORDER_ID = UE.ORIGINATING_WO_ID ) ORIGINATING_WO_NAME
, VISIT_TASK.VISIT_ID UE_VISIT_ID
, WO.WORKORDER_NAME
FROM CS_INCIDENTS_ALL_B CS
, HZ_PARTIES PARTY
, (SELECT INCIDENT_ID
, PARTY_ID
, CONTACT_TYPE
FROM CS_HZ_SR_CONTACT_POINTS
WHERE PRIMARY_FLAG = 'Y') SR_CONT
, AHL_UNIT_EFFECTIVITIES_B UE
, CSI_ITEM_INSTANCES CSI
, MTL_SYSTEM_ITEMS_KFV MTL
, ( SELECT UNIT_CONFIG_HEADER_ID
, NAME
, MASTER_CONFIG_ID
, UNIT_CONFIG_STATUS_CODE
FROM AHL_UNIT_CONFIG_HEADERS
WHERE UNIT_CONFIG_STATUS_CODE NOT IN ('DRAFT'
, 'APPROVAL_REJECTED'
, 'APPROVAL_PENDING') ) UCH
, ( SELECT LOOKUP_CODE
, MEANING
, DESCRIPTION
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_POSITION_REFERENCE'
AND LANGUAGE = USERENV('LANG') ) UE_POSREF_LKUP
, ( SELECT LOOKUP_CODE
, MEANING
, DESCRIPTION
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_ATA_CODE'
AND LANGUAGE = USERENV('LANG') ) UE_ATA_LKUP
, ( SELECT UDF.UNIT_EFFECTIVITY_ID
, UDF.ATA_SEQUENCE_ID
, UDF.UNIT_DEFERRAL_TYPE UE_DEFERRAL_TYPE
, UDF.APPROVAL_STATUS_CODE UE_DEFERRAL_APPR_STATUS
, ATA.MEL_CDL_ATA_SEQUENCE_ID
, ATA.MEL_CDL_HEADER_ID
, HDR.REVISION UE_ATA_HEADER_REVISION
, SRURG.NAME UE_ATA_REPAIR_CATEGORY
, REPCAT.REPAIR_TIME UE_ATA_REPAIR_TIME
, PCN.NAME UE_ATA_PC_NODE_NAME
, UDFTL.APPROVER_NOTES
FROM AHL_UNIT_DEFERRALS_B UDF
, AHL_MEL_CDL_ATA_SEQUENCES ATA
, AHL_MEL_CDL_HEADERS HDR
, AHL_PC_NODES_B PCN
, AHL_REPAIR_CATEGORIES REPCAT
, CS_INCIDENT_URGENCIES_TL SRURG
, AHL_UNIT_DEFERRALS_TL UDFTL
WHERE UDF.UNIT_DEFERRAL_TYPE IN ('MEL'
, 'CDL')
AND UDF.ATA_SEQUENCE_ID = ATA.MEL_CDL_ATA_SEQUENCE_ID
AND ATA.MEL_CDL_HEADER_ID = HDR.MEL_CDL_HEADER_ID
AND HDR.PC_NODE_ID = PCN.PC_NODE_ID
AND ATA.REPAIR_CATEGORY_ID = REPCAT.REPAIR_CATEGORY_ID
AND REPCAT.SR_URGENCY_ID = SRURG.INCIDENT_URGENCY_ID
AND SRURG.LANGUAGE = USERENV('LANG')
AND UDF.UNIT_DEFERRAL_ID = UDFTL.UNIT_DEFERRAL_ID
AND UDFTL.LANGUAGE = USERENV('LANG') ) ATASEQ
, ( SELECT VST.VISIT_NUMBER
, VST.VISIT_ID
, VST_TL.VISIT_NAME
, VST.ORGANIZATION_ID
, VST_TASK.UNIT_EFFECTIVITY_ID
, VST_TASK.VISIT_TASK_ID
, VST_TASK.QUANTITY
, VST_TASK.VISIT_TASK_NUMBER
, VST_TASK_TL.VISIT_TASK_NAME
FROM AHL_VISITS_B VST
, AHL_VISITS_TL VST_TL
, AHL_VISIT_TASKS_B VST_TASK
, AHL_VISIT_TASKS_TL VST_TASK_TL
WHERE VST.VISIT_ID = VST_TL.VISIT_ID
AND VST_TL.LANGUAGE = USERENV('LANG')
AND VST_TASK.VISIT_TASK_ID = VST_TASK_TL.VISIT_TASK_ID
AND VST_TASK_TL.LANGUAGE = USERENV('LANG')
AND VST.VISIT_ID = VST_TASK.VISIT_ID
AND VST_TASK.TASK_TYPE_CODE IN ('SUMMARY'
, 'UNASSOCIATED') ) VISIT_TASK
, ( SELECT AHLWO.VISIT_TASK_ID
, AHLWO.STATUS_CODE
, AHLWO.WORKORDER_NAME
, WIPDJ.OWNING_DEPARTMENT
FROM AHL_WORKORDERS AHLWO
, WIP_DISCRETE_JOBS WIPDJ
WHERE AHLWO.WIP_ENTITY_ID = WIPDJ.WIP_ENTITY_ID ) WO
, MTL_PARAMETERS ORG
, BOM_DEPARTMENTS DEPT
WHERE UE.OBJECT_TYPE = 'SR'
AND UE.CS_INCIDENT_ID = CS.INCIDENT_ID
AND CS.CUSTOMER_ID = PARTY.PARTY_ID
AND SR_CONT.INCIDENT_ID(+) = CS.INCIDENT_ID
AND UE.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
AND CSI.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
AND CSI.LAST_VLD_ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND UCH.UNIT_CONFIG_HEADER_ID (+) = UE.UNIT_CONFIG_HEADER_ID
AND AHL_MC_PATH_POSITION_PVT.GET_POSREF_BY_ID(UE.POSITION_PATH_ID
, 'T') = UE_POSREF_LKUP.LOOKUP_CODE(+)
AND UE.ATA_CODE = UE_ATA_LKUP.LOOKUP_CODE(+)
AND ATASEQ.UNIT_EFFECTIVITY_ID(+) = UE.UNIT_EFFECTIVITY_ID
AND VISIT_TASK.ORGANIZATION_ID = ORG.ORGANIZATION_ID (+)
AND WO.OWNING_DEPARTMENT = DEPT.DEPARTMENT_ID(+)
AND UE.UNIT_EFFECTIVITY_ID = VISIT_TASK.UNIT_EFFECTIVITY_ID(+)
AND VISIT_TASK.VISIT_TASK_ID = WO.VISIT_TASK_ID(+)