VIEW: APPS.AHL_MR_ITEM_DETAILS_V

Source

View Text - Preformatted

SELECT mr.mr_header_id, mr.object_version_number, mr.title, mr.version_number, mr.category_code, mr.mr_status_code, status.meaning, mr.program_type_code, progtype.meaning, mr.effective_from, mr.effective_to, mr.revision, mr_tl.description, mrr.mr_header_id, pmr.title, dv.document_id, dv.document_title, mri.inventory_item_id, mri.item_number, mr.type_code, type.meaning FROM ahl_mr_headers_b mr, ahl_mr_headers_tl mr_tl, (SELECT lookup_code, meaning FROM fnd_lookup_values WHERE lookup_type = 'AHL_FMP_MR_PROGRAM_TYPE' and language = userenv('LANG')) progtype, (SELECT lookup_code, meaning FROM fnd_lookup_values WHERE lookup_type = 'AHL_FMP_REVISION_STATUS' and language = userenv('LANG')) status, (SELECT lookup_code, meaning FROM fnd_lookup_values WHERE lookup_type = 'AHL_FMP_MR_TYPE' and language = userenv('LANG')) type, ahl_mr_relationships mrr, ahl_mr_headers_b pmr, ( SELECT ads.document_id, ads.document_title, adt.aso_object_id FROM ahl_documents_tl ads, ahl_doc_title_assos_b adt, ahl_doc_revisions_b adr WHERE adt.aso_object_type_code = 'MR' and adt.doc_revision_id is not null and ads.document_id = adr.document_id (+) and ads.language = userenv('LANG') and adr.doc_revision_id = adt.doc_revision_id and adt.source_ref_code (+) = 'SOURCE' UNION ALL SELECT ads.document_id, ads.document_title, adt.aso_object_id FROM ahl_doc_title_assos_b adt, ahl_documents_tl ads WHERE adt.aso_object_type_code = 'MR' and adt.doc_revision_id is null and ads.document_id = adt.document_id and ads.language = userenv('LANG') and adt.source_ref_code (+) = 'SOURCE' ) dv, ( SELECT DISTINCT ef.mr_header_id, ef.mr_effectivity_id, mtl.inventory_item_id, mtl.concatenated_segments item_number FROM mtl_system_items_kfv mtl, ahl_position_alternates_v pa, ahl_mr_effectivities ef, ahl_mr_headers_b mr WHERE mtl.inventory_item_id = pa.inventory_item_id and pa.relationship_id = ef.relationship_id and ef.mr_header_id = mr.mr_header_id and mr.application_usg_code= fnd_profile.value('AHL_APPLN_USAGE') and ef.relationship_id is not null and ef.inventory_item_id is null UNION ALL SELECT DISTINCT ef.mr_header_id, ef.mr_effectivity_id, ef.inventory_item_id, mtl.concatenated_segments item_number FROM mtl_system_items_kfv mtl, ahl_mr_effectivities ef, ahl_mr_headers_b mr WHERE mtl.inventory_item_id = ef.inventory_item_id and ef.mr_header_id = mr.mr_header_id and mr.application_usg_code= fnd_profile.value('AHL_APPLN_USAGE') and ef.inventory_item_id is not null ) mri WHERE mr.mr_header_id = mr_tl.mr_header_id and mr_tl.language = userenv('LANG') and mr.application_usg_code = fnd_profile.value('AHL_APPLN_USAGE') and progtype.lookup_code = mr.program_type_code and status.lookup_code = mr.mr_status_code and type.lookup_code(+) = mr.type_code and mr.mr_header_id = mrr.related_mr_header_id (+) and mrr.relationship_code (+) = 'PARENT' and pmr.mr_header_id (+) = mrr.mr_header_id and dv.aso_object_id (+) = mr.mr_header_id and mr.mr_header_id = mri.mr_header_id (+)

View Text - HTML Formatted

SELECT MR.MR_HEADER_ID
, MR.OBJECT_VERSION_NUMBER
, MR.TITLE
, MR.VERSION_NUMBER
, MR.CATEGORY_CODE
, MR.MR_STATUS_CODE
, STATUS.MEANING
, MR.PROGRAM_TYPE_CODE
, PROGTYPE.MEANING
, MR.EFFECTIVE_FROM
, MR.EFFECTIVE_TO
, MR.REVISION
, MR_TL.DESCRIPTION
, MRR.MR_HEADER_ID
, PMR.TITLE
, DV.DOCUMENT_ID
, DV.DOCUMENT_TITLE
, MRI.INVENTORY_ITEM_ID
, MRI.ITEM_NUMBER
, MR.TYPE_CODE
, TYPE.MEANING
FROM AHL_MR_HEADERS_B MR
, AHL_MR_HEADERS_TL MR_TL
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_TYPE'
AND LANGUAGE = USERENV('LANG')) PROGTYPE
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_FMP_REVISION_STATUS'
AND LANGUAGE = USERENV('LANG')) STATUS
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_FMP_MR_TYPE'
AND LANGUAGE = USERENV('LANG')) TYPE
, AHL_MR_RELATIONSHIPS MRR
, AHL_MR_HEADERS_B PMR
, ( SELECT ADS.DOCUMENT_ID
, ADS.DOCUMENT_TITLE
, ADT.ASO_OBJECT_ID
FROM AHL_DOCUMENTS_TL ADS
, AHL_DOC_TITLE_ASSOS_B ADT
, AHL_DOC_REVISIONS_B ADR
WHERE ADT.ASO_OBJECT_TYPE_CODE = 'MR'
AND ADT.DOC_REVISION_ID IS NOT NULL
AND ADS.DOCUMENT_ID = ADR.DOCUMENT_ID (+)
AND ADS.LANGUAGE = USERENV('LANG')
AND ADR.DOC_REVISION_ID = ADT.DOC_REVISION_ID
AND ADT.SOURCE_REF_CODE (+) = 'SOURCE' UNION ALL SELECT ADS.DOCUMENT_ID
, ADS.DOCUMENT_TITLE
, ADT.ASO_OBJECT_ID
FROM AHL_DOC_TITLE_ASSOS_B ADT
, AHL_DOCUMENTS_TL ADS
WHERE ADT.ASO_OBJECT_TYPE_CODE = 'MR'
AND ADT.DOC_REVISION_ID IS NULL
AND ADS.DOCUMENT_ID = ADT.DOCUMENT_ID
AND ADS.LANGUAGE = USERENV('LANG')
AND ADT.SOURCE_REF_CODE (+) = 'SOURCE' ) DV
, ( SELECT DISTINCT EF.MR_HEADER_ID
, EF.MR_EFFECTIVITY_ID
, MTL.INVENTORY_ITEM_ID
, MTL.CONCATENATED_SEGMENTS ITEM_NUMBER
FROM MTL_SYSTEM_ITEMS_KFV MTL
, AHL_POSITION_ALTERNATES_V PA
, AHL_MR_EFFECTIVITIES EF
, AHL_MR_HEADERS_B MR
WHERE MTL.INVENTORY_ITEM_ID = PA.INVENTORY_ITEM_ID
AND PA.RELATIONSHIP_ID = EF.RELATIONSHIP_ID
AND EF.MR_HEADER_ID = MR.MR_HEADER_ID
AND MR.APPLICATION_USG_CODE= FND_PROFILE.VALUE('AHL_APPLN_USAGE')
AND EF.RELATIONSHIP_ID IS NOT NULL
AND EF.INVENTORY_ITEM_ID IS NULL UNION ALL SELECT DISTINCT EF.MR_HEADER_ID
, EF.MR_EFFECTIVITY_ID
, EF.INVENTORY_ITEM_ID
, MTL.CONCATENATED_SEGMENTS ITEM_NUMBER
FROM MTL_SYSTEM_ITEMS_KFV MTL
, AHL_MR_EFFECTIVITIES EF
, AHL_MR_HEADERS_B MR
WHERE MTL.INVENTORY_ITEM_ID = EF.INVENTORY_ITEM_ID
AND EF.MR_HEADER_ID = MR.MR_HEADER_ID
AND MR.APPLICATION_USG_CODE= FND_PROFILE.VALUE('AHL_APPLN_USAGE')
AND EF.INVENTORY_ITEM_ID IS NOT NULL ) MRI
WHERE MR.MR_HEADER_ID = MR_TL.MR_HEADER_ID
AND MR_TL.LANGUAGE = USERENV('LANG')
AND MR.APPLICATION_USG_CODE = FND_PROFILE.VALUE('AHL_APPLN_USAGE')
AND PROGTYPE.LOOKUP_CODE = MR.PROGRAM_TYPE_CODE
AND STATUS.LOOKUP_CODE = MR.MR_STATUS_CODE
AND TYPE.LOOKUP_CODE(+) = MR.TYPE_CODE
AND MR.MR_HEADER_ID = MRR.RELATED_MR_HEADER_ID (+)
AND MRR.RELATIONSHIP_CODE (+) = 'PARENT'
AND PMR.MR_HEADER_ID (+) = MRR.MR_HEADER_ID
AND DV.ASO_OBJECT_ID (+) = MR.MR_HEADER_ID
AND MR.MR_HEADER_ID = MRI.MR_HEADER_ID (+)