View: CSD_SERIAL_NUMBERS_V

Product:	CSD - Depot Repair
Description:	View that displays all possible serial numbers, from IB, service request, and mtl
Implementation/DBA Data:	APPS.CSD_SERIAL_NUMBERS_V

View Text

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'
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') )

Columns