Decode UserName in B1Upf

[LoHa]

Hi Together,

since SAP B1 10 2305 the B1upf.xml does not containing the UserNames in clear form anymore.

For years I use a query to get a complete overview about licenses assigned to my users. Now it is not possible anymore.

Did someone has an idea how to manage it in the future, so I can still use my query.

/*Variable to store License-xml B1Upf*/
DECLARE @xmlFile XML
/*Variable to store License-xml CKS*/
DECLARE @CKSLic XML

/*Put License-xml in variable - Perhabs it is necessary in the future to change path*/
SET @xmlFile = (SELECT * FROM OPENROWSET(BULK '\\ABC\lib\B1Upf.xml', SINGLE_BLOB) AS xmldata)
/*Put License-xml in variable - Perhabs it is necessary in the future to change path*/
SET @CKSLic = (SELECT * FROM OPENROWSET(BULK '\\ABC\b1_shr\WorkingFolder\License\HagerEnergy GmbH.Assignment.xml', SINGLE_BLOB) AS xmldata)

--SELECT @xmlFile /*Debug only*/
--SELECT @CKSLic /*Debug only*/
;
/*Get User_Code from B1Upfxml*/
WITH UserNodes AS
(
SELECT 
	  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS UserIndex
	, One.Users.value('(UserName/text())[1]', 'nvarchar(max)') AS UserCode
	, One.Users.value('(IsConnected/text())[1]', 'nvarchar(max)') AS IsConnected
	, One.Users.query('.') AS CustomerNode

FROM 
	@xmlFile.nodes('Users/User') AS One(Users)
)
/*Assign Licenses to User_Code*/
, UserLicense AS  
(
SELECT 
	  Lic.*
	, ROW_NUMBER() OVER(PARTITION BY Lic.UserIndex ORDER BY (SELECT NULL)) AS ModuleIndex
	, Two.Modules.value('KeyType[1]','nvarchar(max)') AS LicType
	, Two.Modules.value('KeyDesc[1]','nvarchar(max)') AS LicDesc

 FROM 
	UserNodes AS Lic
	CROSS APPLY 
		Lic.CustomerNode.nodes('User/Modules/Module') As Two(Modules)
 )
/*Get Licenses from CKSxml*/
,CKSLic as
(
SELECT
	  CKSLic.row.value('Name[1]','nvarchar(max)') AS [Lizenz],
      CKSUser.row.value('.','nvarchar(max)') AS [Benutzer] /*Punkt holt alle Daten aus dem Unterknoten*/
FROM 
	@CKSLic.nodes('LicenseAssignment/Info/Modules/Module') CKSLic(row)
	CROSS APPLY CKSLic.row.nodes('Users/User') CKSUser(row)
)
--SELECT * FROM CKSLic /*Debug only*/
--SELECT * FROM UserNodes /*Debug only*/
--SELECT * FROM UserLicense /*Debug only*/


 /*Put all Licenses together*/
 SELECT 
	  [OUSR].[USERID]
	, [OUSR].[USER_CODE] AS [Benutzercode]
	, [OUSR].[U_Name] AS [Benutzername]
	, [OUSR].[Locked]
	, (
		SELECT MAX([USR5].[Date]) 
		from [USR5] 
		WHERE [USR5].[UserCode] = [OUSR].[USER_CODE]
		group by [USR5].[UserCode] , [USR5].[Action]
		having [USR5].[Action] = 'I'
	  ) AS [Letzter Login]
	, (SELECT IsConnected FROM UserNodes WHERE UserCode = [OUSR].[USER_CODE]) AS [Angemeldet]
	, CASE WHEN CAST([OUSR].[U_SCB_ML_Password] AS nvarchar(max)) <> '' THEN 'Y' ELSE '' END AS [MobLogUser]
	, CASE WHEN [OUSR].[U_SCB_E3DC_MobilePwd] <> '' THEN 'Y' ELSE '' END AS [MobPhotoUser]
	, CASE 
		WHEN 
			[OUSR].[GROUPS] = 99 
		THEN 
			'Y'
		ELSE '' 
		END AS [User wurde Gelöscht]
	, [OUSR].[SUPERUSER]
	, [UserLicense].[LicDesc] AS [SAP-Lizenz-User]
	, (SELECT
		STUFF((
			SELECT 
				', ' + [UserLicense].[LicDesc]  
			FROM 
				[UserLicense] 
			WHERE 
				[UserLicense].[LicDesc] not like '%User%'
					AND
						[UserLicense].[UserCode] = OUSR.USER_CODE
 
		FOR XML PATH ('')), 1, 2, '')
		) AS [SAP Sonstige]
	/*CKS Lizenzzuweisung*/
	, (SELECT
		STUFF((
			SELECT 
				', ' + REPLACE(REPLACE(Lizenz,'cks.DMS: ',''),'cks.','')
			FROM 
				CKSLic 

			WHERE 
				CKsLic.[Benutzer] = [OUSR].[USER_CODE]
		FOR XML PATH ('')), 1, 2, '')
		) AS [CKS_XML]
	, (SELECT
		STUFF((
			SELECT 
				', ' + 
				CASE [U_LicType]
				WHEN 101 THEN 'Prof'
				WHEN 201 THEN 'Web'
				WHEN 601 THEN 'ADC'
				ELSE
				'Unbekannt'
				END
  
			FROM 
				[dbo].[@CKS_LICENSEUSER] 

			WHERE 
				[dbo].[@CKS_LICENSEUSER].[U_UserCode] = [OUSR].[USER_CODE]
			ORDER BY 
				[U_LicType] ASC
 
		FOR XML PATH ('')), 1, 2, '')
		) AS [CKS_Table]
	, CASE WHEN (SELECT TOP 1 [U_LicType] FROM [dbo].[@CKS_LICENSEUSER] WHERE [dbo].[@CKS_LICENSEUSER].[U_UserCode] = [OUSR].[USER_CODE]) is not null and [T6].[Code] is null
	  THEN 'Anlegen' 
	  ELSE [T6].[Code] 
	  END AS [CKSOutl]
	/*CKS Kategorien*/
	, (SELECT
		STUFF((
				SELECT 
					 ' | ' + T98.U_CategoryName + ': ' +
					 CASE 
						WHEN T99.U_Permission = 1 THEN 'VB' 
						WHEN T99.U_Permission = 2 THEN 'LB' 
						WHEN T99.U_Permission = 3 THEN 'Keine' 
						ELSE 'N.A.' 
						END
				FROM 
					[dbo].[@CKS_USRCATPRMSSNS]  T99 
						INNER JOIN [dbo].[@CKS_CATEGORY] T98 
							ON T98.DocEntry = T99.U_CategoryId
				WHERE
					T99.U_USERID = [OUSR].[USERID]
 
		FOR XML PATH ('')), 1, 3, '')) AS [CKSVorgang]
	, [T1].[U_IsLUsed] AS [COR_CUSTOMIZE]
	, [T2].[U_IsLUsed] AS [COR_EnterpriseSearch]
	, [T3].[U_IsLUsed] AS [COR_LD_ACCOUNTING]
	, [T4].[U_IsLUsed] AS [Swiss Layout Designer]
	, [T5].[U_IsLUsed] AS [COR_SERVICE]
	, [OUSR].[E_Mail] AS [User-Email]

FROM 
	[OUSR]
	/*Coresuite*/
	LEFT JOIN [dbo].[@COR_COR_LICENCE] [T1] 
		ON [T1].[U_UserId] = [OUSR].[USERID] AND [T1].[U_ModuleGuid] = 'COR_CUSTOMIZE'

	LEFT JOIN [dbo].[@COR_COR_LICENCE] [T2] 
		ON [T2].[U_UserId] = [OUSR].[USERID] AND [T2].[U_ModuleGuid] = 'COR_EnterpriseSearch'

	LEFT JOIN [dbo].[@COR_COR_LICENCE] [T3] 
		ON [T3].[U_UserId] = [OUSR].[USERID] AND [T3].[U_ModuleGuid] = 'COR_LD_ACCOUNTING'

	LEFT JOIN [dbo].[@COR_COR_LICENCE] [T4] 
		ON [T4].[U_UserId] = [OUSR].[USERID] AND [T4].[U_ModuleGuid] = 'Swiss Layout Designer'

	LEFT JOIN [dbo].[@COR_COR_LICENCE] [T5] 
		ON [T5].[U_UserId] = [OUSR].[USERID] AND [T5].[U_ModuleGuid] = 'COR_SERVICE'
	
	/*CKS*/
	LEFT JOIN [dbo].[@CKS_MAILSETTINGS] [T6]
		ON [T6].[Name] = [OUSR].[USER_CODE]
	LEFT JOIN [dbo].[@CKS_USRCATPRMSSNS] [T7]
		ON [T7].[U_USERID] = [OUSR].[USERID]
	LEFT JOIN [dbo].[@CKS_CATEGORY] [T8] 
		ON T8.DocEntry = [T7].[U_CategoryId]

	/*XML*/
	/*Be careful !!! Old-License-Desc = Professional User -> New-License-Desc = SAP Business One Professional User*/
	LEFT JOIN [UserLicense] 
		ON [UserLicense].[UserCode] = [OUSR].[USER_CODE] AND UserLicense.LicDesc like '%User%'
--WHERE [OUSR].[Locked] = 'N'

ORDER BY [OUSR].[USER_CODE]<br>

Regards Lothar

[HansVos]

Did you manage to fix this ?
We also have the same issue.

Kind regards,

Hans Vos

[LoHa]

Hi Hans,

meanwhile I made a corsuite-customize rule which reads out the values in the License Form and puts it in an UDT. I did not found another solution but I'm open minded.

regards Lothar

[rogerborges73]

pille una solucion arcaica

[LoHa]

Which one?