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_ALTERNATES_V

Source

View Text - Preformatted


SELECT csi.instance_id csi_item_instance_id, csi.instance_number csi_instance_number, csi.object_version_number csi_object_version_number, csi.inventory_item_id, csi.inv_master_organization_id inventory_org_id, csi.unit_of_measure uom_code, csi.quantity, p.party_id owner_id, ((select hz.party_number from hz_parties hz where hz.party_id = p.party_id and p.party_source_table = 'HZ_PARTIES') UNION ALL (select po.segment1 from po_vendors po where po.vendor_id = p.party_id and p.party_source_table = 'PO_VENDORS')) owner_number, ((select hz.party_name from hz_parties hz where hz.party_id = p.party_id and p.party_source_table = 'HZ_PARTIES') UNION ALL (select po.vendor_name from po_vendors po where po.vendor_id = p.party_id and p.party_source_table = 'PO_VENDORS')) owner_name, (select decode(p.party_source_table,'HZ_PARTIES','PARTY','PO_VENDORS','VENDOR') from dual) party_type, p.object_version_number csi_party_object_version_num, a.relationship_id, assoc.priority, csi.serial_number, csi.inventory_revision, csi.install_date, csi.location_id csi_location_id, ahl_util_uc_pkg.getcsi_locationCode(csi.location_id, csi.location_type_code) Owner_Site_Number, csi.location_type_code csi_location_type_code, ahl_util_uc_pkg.getcsi_locationDesc(csi.location_id, csi.location_type_code, csi.inv_master_organization_id, csi.inv_subinventory_name, csi.inv_locator_id, csi.wip_job_id) Location_description, (select meaning from csi_lookups f where csi.instance_usage_code = f.lookup_code and f.lookup_type = 'CSI_INSTANCE_USAGE_CODE') Status, kfv.concatenated_segments Item_Number, kfv.description Item_description, (select to_date(ciea.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea where ciea.instance_id = csi.instance_id and ciea.attribute_code = 'AHL_MFG_DATE' and ciea.attribute_level = 'GLOBAL') mfg_date, (select mstat.description from mtl_material_statuses mstat where mstat.status_id = csi.instance_condition_id) condition, (select org.organization_code from org_organization_definitions org where organization_id = csi.inv_master_organization_id) organization_code from csi_item_instances csi, ahl_item_associations_b assoc, ahl_mc_relationships a, csi_i_parties p, mtl_system_items_kfv kfv WHERE csi.inventory_item_id = kfv.inventory_item_id and csi.inv_master_organization_id = kfv.organization_id and csi.inventory_item_id = assoc.inventory_item_id and csi.inv_master_organization_id = assoc.inventory_org_id and a.item_group_id = assoc.item_group_id and assoc.item_association_id = ahl_util_uc_pkg.validate_alternate_item(a.relationship_id,csi.inventory_item_id,csi.inv_master_organization_id, csi.quantity, csi.inventory_revision, csi.unit_of_measure) and ((csi.instance_usage_code = 'OUT_OF_SERVICE' OR csi.instance_usage_code IS NULL OR csi.instance_usage_code='OUT_OF_ENTERPRISE') AND csi.location_type_code NOT IN ('WIP','PO','IN-TRANSIT','PROJECT','INVENTORY')) and trunc(sysdate) < trunc(nvl(csi.active_end_date, sysdate+1)) and p.instance_id = csi.instance_id and p.relationship_type_code = 'OWNER' and (p.party_source_table ='HZ_PARTIES' OR p.party_source_table ='PO_VENDORS' ) and NOT EXISTS (select subject_id from csi_ii_relationships where (subject_id = csi.instance_id or object_id = csi.instance_id) and relationship_type_code = 'COMPONENT-OF' and trunc(sysdate) >= trunc(nvl(active_start_date,sysdate)) and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)) ) and csi.inv_master_organization_id in (select organization_id FROM org_organization_definitions WHERE NVL(operating_unit,mo_global.get_current_org_id()) = mo_global.get_current_org_id())

View Text - HTML Formatted

SELECT CSI.INSTANCE_ID CSI_ITEM_INSTANCE_ID
, CSI.INSTANCE_NUMBER CSI_INSTANCE_NUMBER
, CSI.OBJECT_VERSION_NUMBER CSI_OBJECT_VERSION_NUMBER
, CSI.INVENTORY_ITEM_ID
, CSI.INV_MASTER_ORGANIZATION_ID INVENTORY_ORG_ID
, CSI.UNIT_OF_MEASURE UOM_CODE
, CSI.QUANTITY
, P.PARTY_ID OWNER_ID
, ((SELECT HZ.PARTY_NUMBER
FROM HZ_PARTIES HZ
WHERE HZ.PARTY_ID = P.PARTY_ID
AND P.PARTY_SOURCE_TABLE = 'HZ_PARTIES') UNION ALL (SELECT PO.SEGMENT1
FROM PO_VENDORS PO
WHERE PO.VENDOR_ID = P.PARTY_ID
AND P.PARTY_SOURCE_TABLE = 'PO_VENDORS')) OWNER_NUMBER
, ((SELECT HZ.PARTY_NAME
FROM HZ_PARTIES HZ
WHERE HZ.PARTY_ID = P.PARTY_ID
AND P.PARTY_SOURCE_TABLE = 'HZ_PARTIES') UNION ALL (SELECT PO.VENDOR_NAME
FROM PO_VENDORS PO
WHERE PO.VENDOR_ID = P.PARTY_ID
AND P.PARTY_SOURCE_TABLE = 'PO_VENDORS')) OWNER_NAME
, (SELECT DECODE(P.PARTY_SOURCE_TABLE
, 'HZ_PARTIES'
, 'PARTY'
, 'PO_VENDORS'
, 'VENDOR')
FROM DUAL) PARTY_TYPE
, P.OBJECT_VERSION_NUMBER CSI_PARTY_OBJECT_VERSION_NUM
, A.RELATIONSHIP_ID
, ASSOC.PRIORITY
, CSI.SERIAL_NUMBER
, CSI.INVENTORY_REVISION
, CSI.INSTALL_DATE
, CSI.LOCATION_ID CSI_LOCATION_ID
, AHL_UTIL_UC_PKG.GETCSI_LOCATIONCODE(CSI.LOCATION_ID
, CSI.LOCATION_TYPE_CODE) OWNER_SITE_NUMBER
, CSI.LOCATION_TYPE_CODE CSI_LOCATION_TYPE_CODE
, AHL_UTIL_UC_PKG.GETCSI_LOCATIONDESC(CSI.LOCATION_ID
, CSI.LOCATION_TYPE_CODE
, CSI.INV_MASTER_ORGANIZATION_ID
, CSI.INV_SUBINVENTORY_NAME
, CSI.INV_LOCATOR_ID
, CSI.WIP_JOB_ID) LOCATION_DESCRIPTION
, (SELECT MEANING
FROM CSI_LOOKUPS F
WHERE CSI.INSTANCE_USAGE_CODE = F.LOOKUP_CODE
AND F.LOOKUP_TYPE = 'CSI_INSTANCE_USAGE_CODE') STATUS
, KFV.CONCATENATED_SEGMENTS ITEM_NUMBER
, KFV.DESCRIPTION ITEM_DESCRIPTION
, (SELECT TO_DATE(CIEA.ATTRIBUTE_VALUE
, 'DD/MM/YYYY')
FROM CSI_INST_EXTEND_ATTRIB_V CIEA
WHERE CIEA.INSTANCE_ID = CSI.INSTANCE_ID
AND CIEA.ATTRIBUTE_CODE = 'AHL_MFG_DATE'
AND CIEA.ATTRIBUTE_LEVEL = 'GLOBAL') MFG_DATE
, (SELECT MSTAT.DESCRIPTION
FROM MTL_MATERIAL_STATUSES MSTAT
WHERE MSTAT.STATUS_ID = CSI.INSTANCE_CONDITION_ID) CONDITION
, (SELECT ORG.ORGANIZATION_CODE
FROM ORG_ORGANIZATION_DEFINITIONS ORG
WHERE ORGANIZATION_ID = CSI.INV_MASTER_ORGANIZATION_ID) ORGANIZATION_CODE
FROM CSI_ITEM_INSTANCES CSI
, AHL_ITEM_ASSOCIATIONS_B ASSOC
, AHL_MC_RELATIONSHIPS A
, CSI_I_PARTIES P
, MTL_SYSTEM_ITEMS_KFV KFV
WHERE CSI.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
AND CSI.INV_MASTER_ORGANIZATION_ID = KFV.ORGANIZATION_ID
AND CSI.INVENTORY_ITEM_ID = ASSOC.INVENTORY_ITEM_ID
AND CSI.INV_MASTER_ORGANIZATION_ID = ASSOC.INVENTORY_ORG_ID
AND A.ITEM_GROUP_ID = ASSOC.ITEM_GROUP_ID
AND ASSOC.ITEM_ASSOCIATION_ID = AHL_UTIL_UC_PKG.VALIDATE_ALTERNATE_ITEM(A.RELATIONSHIP_ID
, CSI.INVENTORY_ITEM_ID
, CSI.INV_MASTER_ORGANIZATION_ID
, CSI.QUANTITY
, CSI.INVENTORY_REVISION
, CSI.UNIT_OF_MEASURE)
AND ((CSI.INSTANCE_USAGE_CODE = 'OUT_OF_SERVICE' OR CSI.INSTANCE_USAGE_CODE IS NULL OR CSI.INSTANCE_USAGE_CODE='OUT_OF_ENTERPRISE')
AND CSI.LOCATION_TYPE_CODE NOT IN ('WIP'
, 'PO'
, 'IN-TRANSIT'
, 'PROJECT'
, 'INVENTORY'))
AND TRUNC(SYSDATE) < TRUNC(NVL(CSI.ACTIVE_END_DATE
, SYSDATE+1))
AND P.INSTANCE_ID = CSI.INSTANCE_ID
AND P.RELATIONSHIP_TYPE_CODE = 'OWNER'
AND (P.PARTY_SOURCE_TABLE ='HZ_PARTIES' OR P.PARTY_SOURCE_TABLE ='PO_VENDORS' )
AND NOT EXISTS (SELECT SUBJECT_ID
FROM CSI_II_RELATIONSHIPS
WHERE (SUBJECT_ID = CSI.INSTANCE_ID OR OBJECT_ID = CSI.INSTANCE_ID)
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(SYSDATE) >= TRUNC(NVL(ACTIVE_START_DATE
, SYSDATE))
AND TRUNC(SYSDATE) < TRUNC(NVL(ACTIVE_END_DATE
, SYSDATE+1)) )
AND CSI.INV_MASTER_ORGANIZATION_ID IN (SELECT ORGANIZATION_ID
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE NVL(OPERATING_UNIT
, MO_GLOBAL.GET_CURRENT_ORG_ID()) = MO_GLOBAL.GET_CURRENT_ORG_ID())