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.CSD_SERIAL_NUMBERS_V

Source

View Text - Preformatted


SELECT cii.serial_number SERIAL_NUMBER, cii.instance_id INSTANCE_ID , cii.instance_number INSTANCE_NUMBER, cii.inventory_revision INVENTORY_REVISION, msi.inventory_item_id INVENTORY_ITEM_ID, msi.concatenated_segments CONCATENATED_SEGMENTS, msi.organization_id ORGANIZATION_ID, msi.description DESCRIPTION, msi.serial_number_control_code SERIAL_NUMBER_CONTROL_CODE, msi.revision_qty_control_code REVISION_QTY_CONTROL_CODE, cip.party_id CUSTOMER_ID FROM csi_item_instances cii, mtl_system_items_vl msi, csi_i_parties cip WHERE cii.owner_party_source_table = 'HZ_PARTIES' AND cii.serial_number is not null AND SYSDATE BETWEEN NVL(cii.active_start_date,SYSDATE-1) AND NVL(cii.active_end_date, SYSDATE+1) AND cii.inventory_item_id = msi.inventory_item_id AND cii.instance_id = cip.instance_id AND (cii.location_type_code in ('HZ_PARTY_SITES','HZ_LOCATIONS','VENDOR_SITE','INTERNAL_SITE') OR (cii.location_type_code = 'INVENTORY' and cii.active_end_date <= sysdate)) AND msi.enabled_flag = 'Y' AND msi.contract_item_type_code IS NULL AND msi.serv_req_enabled_code = 'E' AND msi.serial_number_control_code <> 1 AND msi.organization_id = cs_std.get_item_valdn_orgzn_id AND SYSDATE BETWEEN NVL(msi.start_date_active, SYSDATE-1) AND NVL(msi.end_date_active, SYSDATE+1) /* AND cip.relationship_type_code = 'OWNER' : bug fix 9774932 */ AND cip.party_source_table = 'HZ_PARTIES' UNION SELECT msn.serial_number SERIAL_NUMBER, to_number(null) INSTANCE_ID, null INSTANCE_NUMBER, msn.revision INVENTORY_REVISION, msi.inventory_item_id INVENTORY_ITEM_ID, msi.concatenated_segments CONCATENATED_SEGMENTS, msi.organization_id ORGANIZATION_ID, msi.description DESCRIPTION, msi.serial_number_control_code SERIAL_NUMBER_CONTROL_CODE, msi.revision_qty_control_code REVISION_QTY_CONTROL_CODE, to_number(NULL) CUSTOMER_ID FROM mtl_serial_numbers msn, mtl_system_items_vl msi WHERE msn.current_organization_id = cs_std.get_item_valdn_orgzn_id AND msn.inventory_item_id = msi.inventory_item_id AND msn.current_organization_id = msi.organization_id AND nvl(msi.comms_nl_trackable_flag, 'N') = 'N' AND msi.serial_number_control_code <> 1 AND msn.current_status = 4 /* 4=> issued out of stores */ UNION SELECT distinct cia.current_serial_number SERIAL_NUMBER, to_number(null) INSTANCE_ID, null INSTANCE_NUMBER, cia.inv_item_revision INVENTORY_REVISION, msi.inventory_item_id INVENTORY_ITEM_ID, msi.concatenated_segments CONCATENATED_SEGMENTS, msi.organization_id ORGANIZATION_ID, msi.description DESCRIPTION, msi.serial_number_control_code SERIAL_NUMBER_CONTROL_CODE, msi.revision_qty_control_code REVISION_QTY_CONTROL_CODE, to_number(NULL) CUSTOMER_ID FROM cs_incidents_all_b cia, mtl_system_items_vl msi WHERE cia.current_serial_number is not null AND cia.inv_organization_id = cs_std.get_item_valdn_orgzn_id AND cia.inventory_item_id = msi.inventory_item_id AND cia.inv_organization_id = msi.organization_id AND nvl(msi.comms_nl_trackable_flag, 'N') = 'N' AND msi.serial_number_control_code <> 1 AND not exists ( SELECT 'x' FROM mtl_serial_numbers msn WHERE cia.current_serial_number = msn.serial_number AND msn.current_organization_id = cia.inv_organization_id AND msn.inventory_item_id = cia.inventory_item_id AND decode(msn.revision,null,'999',msn.revision)= nvl(cia.inv_item_revision,'999') )

View Text - HTML Formatted

SELECT CII.SERIAL_NUMBER SERIAL_NUMBER
, CII.INSTANCE_ID INSTANCE_ID
, CII.INSTANCE_NUMBER INSTANCE_NUMBER
, CII.INVENTORY_REVISION INVENTORY_REVISION
, MSI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS CONCATENATED_SEGMENTS
, MSI.ORGANIZATION_ID ORGANIZATION_ID
, MSI.DESCRIPTION DESCRIPTION
, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, MSI.REVISION_QTY_CONTROL_CODE REVISION_QTY_CONTROL_CODE
, CIP.PARTY_ID CUSTOMER_ID
FROM CSI_ITEM_INSTANCES CII
, MTL_SYSTEM_ITEMS_VL MSI
, CSI_I_PARTIES CIP
WHERE CII.OWNER_PARTY_SOURCE_TABLE = 'HZ_PARTIES'
AND CII.SERIAL_NUMBER IS NOT NULL
AND SYSDATE BETWEEN NVL(CII.ACTIVE_START_DATE
, SYSDATE-1)
AND NVL(CII.ACTIVE_END_DATE
, SYSDATE+1)
AND CII.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND CII.INSTANCE_ID = CIP.INSTANCE_ID
AND (CII.LOCATION_TYPE_CODE IN ('HZ_PARTY_SITES'
, 'HZ_LOCATIONS'
, 'VENDOR_SITE'
, 'INTERNAL_SITE') OR (CII.LOCATION_TYPE_CODE = 'INVENTORY'
AND CII.ACTIVE_END_DATE <= SYSDATE))
AND MSI.ENABLED_FLAG = 'Y'
AND MSI.CONTRACT_ITEM_TYPE_CODE IS NULL
AND MSI.SERV_REQ_ENABLED_CODE = 'E'
AND MSI.SERIAL_NUMBER_CONTROL_CODE <> 1
AND MSI.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND SYSDATE BETWEEN NVL(MSI.START_DATE_ACTIVE
, SYSDATE-1)
AND NVL(MSI.END_DATE_ACTIVE
, SYSDATE+1) /*
AND CIP.RELATIONSHIP_TYPE_CODE = 'OWNER' : BUG FIX 9774932 */
AND CIP.PARTY_SOURCE_TABLE = 'HZ_PARTIES' UNION SELECT MSN.SERIAL_NUMBER SERIAL_NUMBER
, TO_NUMBER(NULL) INSTANCE_ID
, NULL INSTANCE_NUMBER
, MSN.REVISION INVENTORY_REVISION
, MSI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS CONCATENATED_SEGMENTS
, MSI.ORGANIZATION_ID ORGANIZATION_ID
, MSI.DESCRIPTION DESCRIPTION
, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, MSI.REVISION_QTY_CONTROL_CODE REVISION_QTY_CONTROL_CODE
, TO_NUMBER(NULL) CUSTOMER_ID
FROM MTL_SERIAL_NUMBERS MSN
, MTL_SYSTEM_ITEMS_VL MSI
WHERE MSN.CURRENT_ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND MSN.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSN.CURRENT_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND NVL(MSI.COMMS_NL_TRACKABLE_FLAG
, 'N') = 'N'
AND MSI.SERIAL_NUMBER_CONTROL_CODE <> 1
AND MSN.CURRENT_STATUS = 4 /* 4=> ISSUED OUT OF STORES */ UNION SELECT DISTINCT CIA.CURRENT_SERIAL_NUMBER SERIAL_NUMBER
, TO_NUMBER(NULL) INSTANCE_ID
, NULL INSTANCE_NUMBER
, CIA.INV_ITEM_REVISION INVENTORY_REVISION
, MSI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS CONCATENATED_SEGMENTS
, MSI.ORGANIZATION_ID ORGANIZATION_ID
, MSI.DESCRIPTION DESCRIPTION
, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, MSI.REVISION_QTY_CONTROL_CODE REVISION_QTY_CONTROL_CODE
, TO_NUMBER(NULL) CUSTOMER_ID
FROM CS_INCIDENTS_ALL_B CIA
, MTL_SYSTEM_ITEMS_VL MSI
WHERE CIA.CURRENT_SERIAL_NUMBER IS NOT NULL
AND CIA.INV_ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND CIA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND CIA.INV_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND NVL(MSI.COMMS_NL_TRACKABLE_FLAG
, 'N') = 'N'
AND MSI.SERIAL_NUMBER_CONTROL_CODE <> 1
AND NOT EXISTS ( SELECT 'X'
FROM MTL_SERIAL_NUMBERS MSN
WHERE CIA.CURRENT_SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND MSN.CURRENT_ORGANIZATION_ID = CIA.INV_ORGANIZATION_ID
AND MSN.INVENTORY_ITEM_ID = CIA.INVENTORY_ITEM_ID
AND DECODE(MSN.REVISION
, NULL
, '999'
, MSN.REVISION)= NVL(CIA.INV_ITEM_REVISION
, '999') )