SELECT     v_Package.Name AS Package_Name, 
		   SMS_DistributionDPStatus.PackageID,
		   'Package Type (Text)' =
				Case
					when v_Package.PackageType = 0 Then 'Software Distribution Package'
					when v_Package.PackageType = 3 Then 'Driver Package'
					when v_Package.PackageType = 4 Then 'Task Sequence Package'
					when v_Package.PackageType = 5 Then 'Software Update Package'
					when v_Package.PackageType = 6 Then 'Device Setting Package'
					when v_Package.PackageType = 7 Then 'Virtual Package'
					when v_Package.PackageType = 8 Then 'Application'
					when v_Package.PackageType = 257 Then 'Image Package'
					when v_Package.PackageType = 258 Then 'Boot Image Package'
					when v_Package.PackageType = 259 Then 'Operating System Install Package'
				Else
					'Unknown'
				End, 
		   SMS_DistributionDPStatus.Name AS DP_Name,
		   CASE
			    WHEN MessageState = 1 THEN 'Success'
				WHEN MessageState = 2 THEN 'In Progress'
				WHEN MessageState = 4 THEN 'Error'
			Else
				'Unknown'
			End as 'Content_Status',
           CASE 
				WHEN MessageCategory = 31 THEN 'Success' 
				WHEN MessageCategory = 76 THEN 'In Progress' 
				WHEN MessageCategory = 13 THEN 'Failed to distribute content'
				WHEN MessageCategory = 70 THEN 'Failed to update package'
				WHEN MessageCategory = 78 THEN 'Failed to connect to remote distribution point'
				WHEN MessageCategory = 51 THEN 'Content transfer manager was instructed to send content to the distribution point'
				WHEN MessageCategory = 37 THEN 'Content transfer manager was instructed to send content to the distribution point'
				WHEN MessageCategory = 1  THEN 'Waiting for prestage content'
				WHEN MessageCategory = 77 THEN 'Waiting for prestage content'
				WHEN MessageCategory = 62 THEN 'Content hash has been sucessfully verified'
				WHEN MessageCategory = 7  THEN 'The source folder for content does not exist'
				WHEN MessageCategory = 72 THEN 'Content is being redistributed to distribution point'
				WHEN MessageCategory = 4 THEN 'Content was successfully refreshed'
				WHEN MessageCategory = 998 THEN 'Content transfer manager was instructed to send content to the distribution point'
				WHEN MessageCategory = 165 THEN 'Content was distributed to distribution point'
				WHEN MessageCategory = 61 THEN 'Failed to validate content hash'
				WHEN MessageCategory = 24 THEN 'Failed to connect to distribution point'
				ELSE 'Unknown' 
		   END 
		   AS 'Detailed_Status', 
		   SMS_DistributionDPStatus.MessageState,
		   SMS_DistributionDPStatus.MessageCategory, 
           SMS_DistributionDPStatus.ObjectTypeID, 
           SMS_DistributionDPStatus.LastUpdateDate, 
           SMS_DistributionDPStatus.InsString3, 
           v_Package.PkgSourcePath, 
           v_Package.PackageType
FROM         vSMS_DistributionDPStatus AS SMS_DistributionDPStatus INNER JOIN
                      v_Package ON SMS_DistributionDPStatus.PackageID = v_Package.PackageID   
           order by SMS_DistributionDPStatus.PackageID