VIEW: APPS.AHL_UNIT_HEADER_DETAILS_V

Source

View Text - Preformatted

SELECT u.unit_config_header_id, u.object_version_number, u.name, u.master_config_id, u.csi_item_instance_id, u.unit_config_status_code, p.party_id owner_id, p.party_number owner_number, p.party_name owner_name, p.party_type, p.object_version_number csi_party_object_version_num, csi.object_version_number csi_object_version_number, u.active_start_date, u.active_end_date, f.meaning Status_meaning, csi.location_id owner_site_id, decode(csi.location_type_code, 'WIP', wip.wip_entity_name, ahl_util_uc_pkg.getcsi_locationCode(csi.location_id, csi.location_type_code) ) Owner_Site_Number, ahl_util_uc_pkg.getcsi_locationDesc(csi.location_id, csi.location_type_code, csi.inv_organization_id,csi.inv_subinventory_name, csi. inv_locator_id, csi.wip_job_id) Location_Description, csi.serial_number, amr.position_ref_code position_ref_code, f1.meaning position_ref_meaning, a.name master_config_name, a.revision master_config_revision, a.description master_config_description, kfv.concatenated_segments Item_Number, kfv.description Item_Description from ahl_unit_config_headers u, csi_item_instances csi, csi_inst_party_details_v p, fnd_lookups f, ahl_mc_headers_vl a, ahl_mc_relationships amr, fnd_lookups f1, mtl_system_items_kfv kfv, wip_entities wip WHERE u.csi_item_instance_id = csi.instance_id and csi.instance_id = p.instance_id and p.relationship_type_code = 'OWNER' and csi.inventory_item_id = kfv.inventory_item_id and csi.last_vld_organization_id = kfv.organization_id and u.unit_config_status_code = f.lookup_code(+) and f.lookup_type(+) = 'AHL_CONFIG_STATUS' and u.master_config_id = a.mc_header_id and a.mc_header_id = amr.mc_header_id and amr.parent_relationship_id is null and amr.position_ref_code = f1.lookup_code(+) and f1.lookup_type(+) = 'AHL_POSITION_REFERENCE' and csi.wip_job_id = wip.wip_entity_id(+) and trunc(sysdate) < trunc(nvl(u.active_end_date, sysdate+1))

View Text - HTML Formatted

SELECT U.UNIT_CONFIG_HEADER_ID
, U.OBJECT_VERSION_NUMBER
, U.NAME
, U.MASTER_CONFIG_ID
, U.CSI_ITEM_INSTANCE_ID
, U.UNIT_CONFIG_STATUS_CODE
, P.PARTY_ID OWNER_ID
, P.PARTY_NUMBER OWNER_NUMBER
, P.PARTY_NAME OWNER_NAME
, P.PARTY_TYPE
, P.OBJECT_VERSION_NUMBER CSI_PARTY_OBJECT_VERSION_NUM
, CSI.OBJECT_VERSION_NUMBER CSI_OBJECT_VERSION_NUMBER
, U.ACTIVE_START_DATE
, U.ACTIVE_END_DATE
, F.MEANING STATUS_MEANING
, CSI.LOCATION_ID OWNER_SITE_ID
, DECODE(CSI.LOCATION_TYPE_CODE
, 'WIP'
, WIP.WIP_ENTITY_NAME
, AHL_UTIL_UC_PKG.GETCSI_LOCATIONCODE(CSI.LOCATION_ID
, CSI.LOCATION_TYPE_CODE) ) OWNER_SITE_NUMBER
, AHL_UTIL_UC_PKG.GETCSI_LOCATIONDESC(CSI.LOCATION_ID
, CSI.LOCATION_TYPE_CODE
, CSI.INV_ORGANIZATION_ID
, CSI.INV_SUBINVENTORY_NAME
, CSI. INV_LOCATOR_ID
, CSI.WIP_JOB_ID) LOCATION_DESCRIPTION
, CSI.SERIAL_NUMBER
, AMR.POSITION_REF_CODE POSITION_REF_CODE
, F1.MEANING POSITION_REF_MEANING
, A.NAME MASTER_CONFIG_NAME
, A.REVISION MASTER_CONFIG_REVISION
, A.DESCRIPTION MASTER_CONFIG_DESCRIPTION
, KFV.CONCATENATED_SEGMENTS ITEM_NUMBER
, KFV.DESCRIPTION ITEM_DESCRIPTION
FROM AHL_UNIT_CONFIG_HEADERS U
, CSI_ITEM_INSTANCES CSI
, CSI_INST_PARTY_DETAILS_V P
, FND_LOOKUPS F
, AHL_MC_HEADERS_VL A
, AHL_MC_RELATIONSHIPS AMR
, FND_LOOKUPS F1
, MTL_SYSTEM_ITEMS_KFV KFV
, WIP_ENTITIES WIP
WHERE U.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
AND CSI.INSTANCE_ID = P.INSTANCE_ID
AND P.RELATIONSHIP_TYPE_CODE = 'OWNER'
AND CSI.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
AND CSI.LAST_VLD_ORGANIZATION_ID = KFV.ORGANIZATION_ID
AND U.UNIT_CONFIG_STATUS_CODE = F.LOOKUP_CODE(+)
AND F.LOOKUP_TYPE(+) = 'AHL_CONFIG_STATUS'
AND U.MASTER_CONFIG_ID = A.MC_HEADER_ID
AND A.MC_HEADER_ID = AMR.MC_HEADER_ID
AND AMR.PARENT_RELATIONSHIP_ID IS NULL
AND AMR.POSITION_REF_CODE = F1.LOOKUP_CODE(+)
AND F1.LOOKUP_TYPE(+) = 'AHL_POSITION_REFERENCE'
AND CSI.WIP_JOB_ID = WIP.WIP_ENTITY_ID(+)
AND TRUNC(SYSDATE) < TRUNC(NVL(U.ACTIVE_END_DATE
, SYSDATE+1))