VIEW: APPS.AHL_SRH_MR_HISTORY_ALL_V

Source

View Text - Preformatted

SELECT UE.unit_effectivity_id, TO_CHAR('WO'), UE.Object_version_number, UE.Csi_item_instance_id, UE.Mr_header_id, UE.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.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.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.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_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 ), (SELECT CS.INCIDENT_NUMBER FROM CS_INCIDENTS_ALL_B CS WHERE CS.INCIDENT_ID=UE.CS_INCIDENT_ID ), (SELECT CSI.INSTANCE_NUMBER FROM CSI_ITEM_INSTANCES CSI WHERE CSI.INSTANCE_ID=UE.CSI_ITEM_INSTANCE_ID ), MR.EFFECTIVE_FROM, MR.EFFECTIVE_TO, RTRIM((AHL_COMPLETIONS_PVT.get_mr_status( UE.Unit_effectivity_id ))), (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 ) ), VST.Visit_id, (SELECT VISIT_NAME FROM AHL_VISITS_TL WHERE VISIT_ID = VST.VISIT_ID AND LANGUAGE = USERENV('LANG') ), VST.Visit_number, VST.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 ) , TO_CHAR(NULL), VST.Start_date_time , MR.Qa_inspection_type , (SELECT UC.NAME FROM AHL_UNIT_CONFIG_HEADERS UC WHERE UC.Csi_item_instance_id = UE.Csi_item_instance_id ) , VST.Organization_id , (SELECT ORG.ORGANIZATION_NAME FROM ORG_ORGANIZATION_DEFINITIONS ORG WHERE ORG.ORGANIZATION_ID = VST.ORGANIZATION_ID ) , VST.Department_id , (SELECT BOM.Description FROM BOM_DEPARTMENTS BOM WHERE BOM.DEPARTMENT_ID=VST.DEPARTMENT_ID ) , WO.actual_start_date , WO.actual_end_date , UE.Qa_Collection_id , WO.WORKORDER_ID , WO.WORKORDER_NAME , CSI.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 ) , (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 ) , NVL(VT.INSTANCE_ID,VST.ITEM_INSTANCE_ID) , CSI.INSTANCE_NUMBER , CSI.SERIAL_NUMBER , WO.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 ) , (SELECT WDJ.DESCRIPTION FROM WIP_DISCRETE_JOBS WDJ WHERE WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID ) , VT.VISIT_TASK_ID , VT.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') ), TO_NUMBER(NULL), TO_CHAR(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL) FROM AHL_UNIT_EFFECTIVITIES_B UE , AHL_MR_HEADERS_B MR, AHL_VISITS_VL VST, AHL_VISIT_TASKS_VL 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.visit_task_id = WO.visit_task_id AND UE.Csi_item_instance_id = CSI.INSTANCE_ID UNION SELECT UE.unit_effectivity_id, TO_CHAR('OSP'), UE.Object_version_number, UE.Csi_item_instance_id, UE.Mr_header_id, UE.Cs_Incident_id, MR.TITLE, 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.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.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_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) , NULL, (SELECT CSI.INSTANCE_NUMBER FROM CSI_ITEM_INSTANCES CSI WHERE CSI.INSTANCE_ID=UE.CSI_ITEM_INSTANCE_ID) , MR.EFFECTIVE_FROM, MR.EFFECTIVE_TO, RTRIM((AHL_COMPLETIONS_PVT.get_mr_status( UE.Unit_effectivity_id ))) , (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 ) ) , TO_NUMBER(NULL), TO_CHAR(NULL), TO_NUMBER(NULL), TO_CHAR(NULL), TO_CHAR(NULL), (SELECT FLVT.MEANING FROM FND_LOOKUP_VALUES FLVT WHERE FLVT.LOOKUP_TYPE='AHL_UMP_SEARCH_FOR_TYPE' AND FLVT.LANGUAGE = USERENV('LANG') AND FLVT.LOOKUP_CODE = AOA.PLANNING_TYPE_CODE), TO_DATE(NULL), MR.Qa_inspection_type, (SELECT UC.NAME FROM AHL_UNIT_CONFIG_HEADERS UC WHERE UC.Csi_item_instance_id = UE.Csi_item_instance_id) , csi.inv_master_ORGANIZATION_ID Organization_id, (SELECT ORG.ORGANIZATION_NAME FROM ORG_ORGANIZATION_DEFINITIONS ORG WHERE ORG.ORGANIZATION_ID = csi.inv_master_ORGANIZATION_ID), TO_NUMBER(NULL), TO_CHAR(NULL), TO_DATE(NULL), AOA.ACCOMPLISHED_DATE, UE.Qa_Collection_id, TO_NUMBER(NULL), TO_CHAR(NULL), CSI.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 = csi.inv_master_ORGANIZATION_ID), (SELECT MTLI.DESCRIPTION FROM MTL_SYSTEM_ITEMS_KFV MTLI WHERE mtli.inventory_item_id = csi.inventory_item_id AND MTLI.ORGANIZATION_ID = csi.inv_master_organization_id) , csi.instance_id , CSI.INSTANCE_NUMBER , CSI.SERIAL_NUMBER , TO_CHAR(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_CHAR(NULL), AOA.Accomplishment_id, AOA.PLANNING_TYPE_CODE, AOOL.OSP_ORDER_LINE_ID, AOOL.OSP_ORDER_ID, AOOL.OSP_LINE_NUMBER, AOOV.OSP_ORDER_NUMBER FROM AHL_UNIT_EFFECTIVITIES_B UE , AHL_OSP_ACCOMPLISHMENTS AOA, AHL_MR_HEADERS_B MR, CSI_ITEM_INSTANCES CSI, AHL_OSP_ORDER_LINES AOOL, AHL_OSP_ORDERS_VL AOOV WHERE UE.APPLICATION_USG_CODE =RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE'))) AND UE.MR_HEADER_ID = MR.MR_HEADER_ID AND UE.Unit_effectivity_id = AOA.unit_effectivity_id AND UE.Csi_item_instance_id = CSI.INSTANCE_ID AND AOA.OSP_ORDER_LINE_ID = AOOL.OSP_ORDER_LINE_ID AND AOOL.OSP_ORDER_ID = AOOV.OSP_ORDER_ID

View Text - HTML Formatted

SELECT UE.UNIT_EFFECTIVITY_ID
, TO_CHAR('WO')
, UE.OBJECT_VERSION_NUMBER
, UE.CSI_ITEM_INSTANCE_ID
, UE.MR_HEADER_ID
, UE.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.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.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.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_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 )
, (SELECT CS.INCIDENT_NUMBER
FROM CS_INCIDENTS_ALL_B CS
WHERE CS.INCIDENT_ID=UE.CS_INCIDENT_ID )
, (SELECT CSI.INSTANCE_NUMBER
FROM CSI_ITEM_INSTANCES CSI
WHERE CSI.INSTANCE_ID=UE.CSI_ITEM_INSTANCE_ID )
, MR.EFFECTIVE_FROM
, MR.EFFECTIVE_TO
, RTRIM((AHL_COMPLETIONS_PVT.GET_MR_STATUS( UE.UNIT_EFFECTIVITY_ID )))
, (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 ) )
, VST.VISIT_ID
, (SELECT VISIT_NAME
FROM AHL_VISITS_TL
WHERE VISIT_ID = VST.VISIT_ID
AND LANGUAGE = USERENV('LANG') )
, VST.VISIT_NUMBER
, VST.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 )
, TO_CHAR(NULL)
, VST.START_DATE_TIME
, MR.QA_INSPECTION_TYPE
, (SELECT UC.NAME
FROM AHL_UNIT_CONFIG_HEADERS UC
WHERE UC.CSI_ITEM_INSTANCE_ID = UE.CSI_ITEM_INSTANCE_ID )
, VST.ORGANIZATION_ID
, (SELECT ORG.ORGANIZATION_NAME
FROM ORG_ORGANIZATION_DEFINITIONS ORG
WHERE ORG.ORGANIZATION_ID = VST.ORGANIZATION_ID )
, VST.DEPARTMENT_ID
, (SELECT BOM.DESCRIPTION
FROM BOM_DEPARTMENTS BOM
WHERE BOM.DEPARTMENT_ID=VST.DEPARTMENT_ID )
, WO.ACTUAL_START_DATE
, WO.ACTUAL_END_DATE
, UE.QA_COLLECTION_ID
, WO.WORKORDER_ID
, WO.WORKORDER_NAME
, CSI.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 )
, (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 )
, NVL(VT.INSTANCE_ID
, VST.ITEM_INSTANCE_ID)
, CSI.INSTANCE_NUMBER
, CSI.SERIAL_NUMBER
, WO.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 )
, (SELECT WDJ.DESCRIPTION
FROM WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID )
, VT.VISIT_TASK_ID
, VT.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') )
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM AHL_UNIT_EFFECTIVITIES_B UE
, AHL_MR_HEADERS_B MR
, AHL_VISITS_VL VST
, AHL_VISIT_TASKS_VL 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.VISIT_TASK_ID = WO.VISIT_TASK_ID
AND UE.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID UNION SELECT UE.UNIT_EFFECTIVITY_ID
, TO_CHAR('OSP')
, UE.OBJECT_VERSION_NUMBER
, UE.CSI_ITEM_INSTANCE_ID
, UE.MR_HEADER_ID
, UE.CS_INCIDENT_ID
, MR.TITLE
, 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.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.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_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)
, NULL
, (SELECT CSI.INSTANCE_NUMBER
FROM CSI_ITEM_INSTANCES CSI
WHERE CSI.INSTANCE_ID=UE.CSI_ITEM_INSTANCE_ID)
, MR.EFFECTIVE_FROM
, MR.EFFECTIVE_TO
, RTRIM((AHL_COMPLETIONS_PVT.GET_MR_STATUS( UE.UNIT_EFFECTIVITY_ID )))
, (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 ) )
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, (SELECT FLVT.MEANING
FROM FND_LOOKUP_VALUES FLVT
WHERE FLVT.LOOKUP_TYPE='AHL_UMP_SEARCH_FOR_TYPE'
AND FLVT.LANGUAGE = USERENV('LANG')
AND FLVT.LOOKUP_CODE = AOA.PLANNING_TYPE_CODE)
, TO_DATE(NULL)
, MR.QA_INSPECTION_TYPE
, (SELECT UC.NAME
FROM AHL_UNIT_CONFIG_HEADERS UC
WHERE UC.CSI_ITEM_INSTANCE_ID = UE.CSI_ITEM_INSTANCE_ID)
, CSI.INV_MASTER_ORGANIZATION_ID ORGANIZATION_ID
, (SELECT ORG.ORGANIZATION_NAME
FROM ORG_ORGANIZATION_DEFINITIONS ORG
WHERE ORG.ORGANIZATION_ID = CSI.INV_MASTER_ORGANIZATION_ID)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_DATE(NULL)
, AOA.ACCOMPLISHED_DATE
, UE.QA_COLLECTION_ID
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, CSI.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 = CSI.INV_MASTER_ORGANIZATION_ID)
, (SELECT MTLI.DESCRIPTION
FROM MTL_SYSTEM_ITEMS_KFV MTLI
WHERE MTLI.INVENTORY_ITEM_ID = CSI.INVENTORY_ITEM_ID
AND MTLI.ORGANIZATION_ID = CSI.INV_MASTER_ORGANIZATION_ID)
, CSI.INSTANCE_ID
, CSI.INSTANCE_NUMBER
, CSI.SERIAL_NUMBER
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, AOA.ACCOMPLISHMENT_ID
, AOA.PLANNING_TYPE_CODE
, AOOL.OSP_ORDER_LINE_ID
, AOOL.OSP_ORDER_ID
, AOOL.OSP_LINE_NUMBER
, AOOV.OSP_ORDER_NUMBER
FROM AHL_UNIT_EFFECTIVITIES_B UE
, AHL_OSP_ACCOMPLISHMENTS AOA
, AHL_MR_HEADERS_B MR
, CSI_ITEM_INSTANCES CSI
, AHL_OSP_ORDER_LINES AOOL
, AHL_OSP_ORDERS_VL AOOV
WHERE UE.APPLICATION_USG_CODE =RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE')))
AND UE.MR_HEADER_ID = MR.MR_HEADER_ID
AND UE.UNIT_EFFECTIVITY_ID = AOA.UNIT_EFFECTIVITY_ID
AND UE.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
AND AOA.OSP_ORDER_LINE_ID = AOOL.OSP_ORDER_LINE_ID
AND AOOL.OSP_ORDER_ID = AOOV.OSP_ORDER_ID