eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AHL_UNIT_DETAILS_V

Source

View Text - Preformatted


SELECT a.relationship_id, csi.instance_id csi_item_instance_id, csi.object_version_number csi_object_version, f.meaning position_ref_meaning, f1.meaning position_necessity_meaning, csi.quantity, csi.unit_of_measure uom_code, cii.relationship_id csi_ii_relationship_id, cii.object_version_number csi_ii_object_version, cii.object_id parent_csi_instance_id, amh.description, a.parent_relationship_id, csi.serial_number, csi.inventory_revision revision, csi.lot_number, kfv.concatenated_segments Item_Number, to_date(ciea.attribute_value, 'DD/MM/YYYY') mfg_date from ahl_mc_relationships a, ahl_mc_headers_vl amh, csi_ii_relationships cii, csi_item_instances csi, fnd_lookups f, fnd_lookups f1, mtl_system_items_kfv kfv, csi_inst_extend_attrib_v ciea WHERE a.relationship_id = to_number(cii.position_reference) and a.mc_header_id = amh.mc_header_id and cii.subject_id = csi.instance_id and cii.relationship_type_code = 'COMPONENT-OF' and f.lookup_type = 'AHL_POSITION_REFERENCE' and f.lookup_code = a.position_ref_code and f1.lookup_type = 'AHL_POSITION_NECESSITY' and f1.lookup_code = nvl(a.position_necessity_code, 'MANDATORY') and csi.inventory_item_id = kfv.inventory_item_id and csi.inv_master_organization_id = kfv.organization_id and csi.instance_id = ciea.instance_id(+) and ciea.attribute_code(+) = 'AHL_MFG_DATE' and ciea.attribute_level(+) = 'GLOBAL' and trunc(sysdate) >= trunc(nvl(cii.active_start_date,sysdate)) and trunc(sysdate) < trunc(nvl(cii.active_end_date,sysdate+1)) UNION select a.relationship_id, csi.instance_id csi_item_instance_id, csi.object_version_number csi_object_version, f.meaning position_ref_meaning, f1.meaning position_necessity_meaning, csi.quantity, csi.unit_of_measure uom_code, to_number(null) csi_ii_relationship_id, to_number(null) csi_ii_object_version, to_number(null) parent_csi_instance_id, amh.description, a.parent_relationship_id, csi.serial_number, csi.inventory_revision revision, csi.lot_number, kfv.concatenated_segments Item_Number, to_date(null, 'DD/MM/YYYY') mfg_date from ahl_mc_relationships a, ahl_mc_headers_vl amh, ahl_unit_config_headers u, csi_item_instances csi, fnd_lookups f, fnd_lookups f1, mtl_system_items_kfv kfv where amh.mc_header_id = u.master_config_id and a.mc_header_id = amh.mc_header_id and a.parent_relationship_id is null and u.csi_item_instance_id = csi.instance_id and f.lookup_type = 'AHL_POSITION_REFERENCE' and f.lookup_code = a.position_ref_code and f1.lookup_type = 'AHL_POSITION_NECESSITY' and f1.lookup_code = nvl(a.position_necessity_code, 'MANDATORY') and csi.inventory_item_id = kfv.inventory_item_id and csi.inv_master_organization_id = kfv.organization_id and trunc(sysdate) < trunc(nvl(u.active_end_date, sysdate+1))

View Text - HTML Formatted

SELECT A.RELATIONSHIP_ID
, CSI.INSTANCE_ID CSI_ITEM_INSTANCE_ID
, CSI.OBJECT_VERSION_NUMBER CSI_OBJECT_VERSION
, F.MEANING POSITION_REF_MEANING
, F1.MEANING POSITION_NECESSITY_MEANING
, CSI.QUANTITY
, CSI.UNIT_OF_MEASURE UOM_CODE
, CII.RELATIONSHIP_ID CSI_II_RELATIONSHIP_ID
, CII.OBJECT_VERSION_NUMBER CSI_II_OBJECT_VERSION
, CII.OBJECT_ID PARENT_CSI_INSTANCE_ID
, AMH.DESCRIPTION
, A.PARENT_RELATIONSHIP_ID
, CSI.SERIAL_NUMBER
, CSI.INVENTORY_REVISION REVISION
, CSI.LOT_NUMBER
, KFV.CONCATENATED_SEGMENTS ITEM_NUMBER
, TO_DATE(CIEA.ATTRIBUTE_VALUE
, 'DD/MM/YYYY') MFG_DATE
FROM AHL_MC_RELATIONSHIPS A
, AHL_MC_HEADERS_VL AMH
, CSI_II_RELATIONSHIPS CII
, CSI_ITEM_INSTANCES CSI
, FND_LOOKUPS F
, FND_LOOKUPS F1
, MTL_SYSTEM_ITEMS_KFV KFV
, CSI_INST_EXTEND_ATTRIB_V CIEA
WHERE A.RELATIONSHIP_ID = TO_NUMBER(CII.POSITION_REFERENCE)
AND A.MC_HEADER_ID = AMH.MC_HEADER_ID
AND CII.SUBJECT_ID = CSI.INSTANCE_ID
AND CII.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND F.LOOKUP_TYPE = 'AHL_POSITION_REFERENCE'
AND F.LOOKUP_CODE = A.POSITION_REF_CODE
AND F1.LOOKUP_TYPE = 'AHL_POSITION_NECESSITY'
AND F1.LOOKUP_CODE = NVL(A.POSITION_NECESSITY_CODE
, 'MANDATORY')
AND CSI.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
AND CSI.INV_MASTER_ORGANIZATION_ID = KFV.ORGANIZATION_ID
AND CSI.INSTANCE_ID = CIEA.INSTANCE_ID(+)
AND CIEA.ATTRIBUTE_CODE(+) = 'AHL_MFG_DATE'
AND CIEA.ATTRIBUTE_LEVEL(+) = 'GLOBAL'
AND TRUNC(SYSDATE) >= TRUNC(NVL(CII.ACTIVE_START_DATE
, SYSDATE))
AND TRUNC(SYSDATE) < TRUNC(NVL(CII.ACTIVE_END_DATE
, SYSDATE+1)) UNION SELECT A.RELATIONSHIP_ID
, CSI.INSTANCE_ID CSI_ITEM_INSTANCE_ID
, CSI.OBJECT_VERSION_NUMBER CSI_OBJECT_VERSION
, F.MEANING POSITION_REF_MEANING
, F1.MEANING POSITION_NECESSITY_MEANING
, CSI.QUANTITY
, CSI.UNIT_OF_MEASURE UOM_CODE
, TO_NUMBER(NULL) CSI_II_RELATIONSHIP_ID
, TO_NUMBER(NULL) CSI_II_OBJECT_VERSION
, TO_NUMBER(NULL) PARENT_CSI_INSTANCE_ID
, AMH.DESCRIPTION
, A.PARENT_RELATIONSHIP_ID
, CSI.SERIAL_NUMBER
, CSI.INVENTORY_REVISION REVISION
, CSI.LOT_NUMBER
, KFV.CONCATENATED_SEGMENTS ITEM_NUMBER
, TO_DATE(NULL
, 'DD/MM/YYYY') MFG_DATE
FROM AHL_MC_RELATIONSHIPS A
, AHL_MC_HEADERS_VL AMH
, AHL_UNIT_CONFIG_HEADERS U
, CSI_ITEM_INSTANCES CSI
, FND_LOOKUPS F
, FND_LOOKUPS F1
, MTL_SYSTEM_ITEMS_KFV KFV
WHERE AMH.MC_HEADER_ID = U.MASTER_CONFIG_ID
AND A.MC_HEADER_ID = AMH.MC_HEADER_ID
AND A.PARENT_RELATIONSHIP_ID IS NULL
AND U.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
AND F.LOOKUP_TYPE = 'AHL_POSITION_REFERENCE'
AND F.LOOKUP_CODE = A.POSITION_REF_CODE
AND F1.LOOKUP_TYPE = 'AHL_POSITION_NECESSITY'
AND F1.LOOKUP_CODE = NVL(A.POSITION_NECESSITY_CODE
, 'MANDATORY')
AND CSI.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
AND CSI.INV_MASTER_ORGANIZATION_ID = KFV.ORGANIZATION_ID
AND TRUNC(SYSDATE) < TRUNC(NVL(U.ACTIVE_END_DATE
, SYSDATE+1))