Hi, I am creating a Lobby for the ICT team and want to show IFS Licence availability on it.
I can get this for Full User licenses no worries (in fact theres a premade Lobby Element for it), but for LTU and CTU I can only get find current usage, not availability (how many are free) or the Total (from which I could subtract the usage to give me the availabilty.
Is there a view or table to query this data from for LTU licences etc.?
Hi, Check FND_LICENSED_ROLE_USAGE view and it should have the limited user count along will the full user count:
Hi
This is tracked and available for Full, but I haven’t been able to find this or even the total number of licenses in the environment.
Even if I could get the total, I could subtract the usage you’ve shown above to give me the number of free licenses.
I have to look for the view but can’t you rely on the license management screen to get the limits as these values shouldn’t change unless a new license is imported, which is rare.
If there is a view that I can use to either directly pull or extrapolate the available number of licenses that’d be terrific.
Hi Garak,
Here is a select to check the license status
SELECT FLOOR(&AO..Fnd_License_API.Get_Expiration_Date - sysdate) as no_days_left_license,
&AO..fnd_license_api.Get_Licensed_Full_Users as Licensed_Full_Users,
&AO..fnd_license_api.Get_Consumed_Full_Users as Consumed_Full_Users
FROM dual;
license to spare = Licensed_Full_Users - Consumed_Full_Users
Hope it helps!
Damith
Hi
I want to know how many spare LTU and other non-Full User licenses are spare.
There’s several ways to get the Full User license information (multiple views and API functions available) but none of them seem to work for LTU’s that I can tell so far.
I can get LTU current consumption from the DB, but can’t find either a LICENSE_LIMIT for LTU’s like is available for FU lic’s, or a USERS_LEFT for LTUs which again is available for FU lics, despite the data displaying in on the License Management screen, so it must be stored somewhere.
I’m hoping somone knows where that somwhere is.
Either one of these fields for LTU’s would be suitable for me.
Hi
As I know License Info displayed in the License Management screen is a result of Activity Handler call (ManageLicense:QueryApplicationLicense) to Application server and think it grabs data from several places.
However, following could be useful for you to construct your requirement.
Open the license file from fnd_license_tab
select binary_value from fnd_license_tab
where parameter = 'LICENSE_FILE'
Here you can find the types of licenses you have and their limits.
Consumption of CTU license can be checked using the connected View in the license file.
Eg:
select * from LICENSED_TIMCLO_USERS
Consumption of LTU/ATU licenses can be obtained from select * from fnd_licensed_role_usage
Chers!
Damith
Hi Garak,
Some time back i got a similar question. Found out that the LTU/ATU/CTU license information is not stored in any view. But rather in the underlying license file of IFS. So did some research and came up with an SQL to get the info from the XML file. Now i don't have any environment with LTU licensing. So can't know for sure if that still works. But for CTU and ATU it still works. I made this some time back so might not be the most efficient 
.
--- Get LTU
SELECT extractvalue(value(lic),
'*/PERMISSION_SET',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') NAME,
extractvalue(value(lic),
'*/NUMBER_OF_USERS',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') NO_OF_USER
FROM ifsapp.FND_LICENSE,
TABLE(xmlsequence(extract(XMLType(binary_value, 871),
'*/LIMITED_TASK_USERS/LIMITED_TASK_USER',
'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"'))) lic
WHERE PARAMETER = 'LICENSE_FILE'
UNION
--Get ATU
SELECT extractvalue(value(lic),
'*/PERMISSION_SET',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') NAME,
extractvalue(value(lic),
'*/NUMBER_OF_USERS',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') NO_OF_USER
FROM ifsapp.FND_LICENSE,
TABLE(xmlsequence(extract(XMLType(binary_value, 871),
'*/ADDITIONAL_TASK_USERS/ADDITIONAL_TASK_USER',
'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"'))) lic
WHERE PARAMETER = 'LICENSE_FILE'
UNION
--Get CTU
SELECT extractvalue(value(lic),
'*/NAME',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') NAME,
extractvalue(value(lic),
'*/NUMBER_OF_USERS',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') NO_OF_USER
FROM ifsapp.FND_LICENSE,
TABLE(xmlsequence(extract(XMLType(binary_value, 871),
'*/CUSTOM_TASK_USERS/CUSTOM_TASK_USER',
'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"'))) lic
WHERE PARAMETER = 'LICENSE_FILE'Regards,
Mitchel
Thank
It does look pretty messy but it works.
It doesn’t work for our Warehouse Data Collection CTU licence as that usage seems to be tracked differently, (i.e. its not tracked in the fnd_licensed_role_user view like LTU and VIRTUAL_MAP/additional licenses are but I’m not too concerned about that one, as I am about the LTU’s which it got nicely.
--- Get LTU
SELECT extractvalue(value(lic),
'*/PERMISSION_SET',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') "NAME",
extractvalue(value(lic),
'*/NUMBER_OF_USERS',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') NO_OF_USER,
lru.USER_COUNT as TOTAL_LIC,
(extractvalue(value(lic),
'*/NUMBER_OF_USERS',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') - lru.USER_COUNT) AS REMAINING
FROM ifsapp.FND_LICENSE,
TABLE(xmlsequence(extract(XMLType(binary_value, 871),
'*/LIMITED_TASK_USERS/LIMITED_TASK_USER',
'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"'))) lic
LEFT JOIN fnd_licensed_role_usage lru on (extractvalue(value(lic),
'*/PERMISSION_SET',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"')) = lru.ROLE
WHERE PARAMETER = 'LICENSE_FILE'
UNION
--Get ATU
SELECT extractvalue(value(lic),
'*/PERMISSION_SET',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') NAME,
extractvalue(value(lic),
'*/NUMBER_OF_USERS',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') NO_OF_USER,
lru.USER_COUNT as TOTAL_LIC,
(extractvalue(value(lic),
'*/NUMBER_OF_USERS',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') - lru.USER_COUNT) AS REMAINING
FROM ifsapp.FND_LICENSE,
TABLE(xmlsequence(extract(XMLType(binary_value, 871),
'*/ADDITIONAL_TASK_USERS/ADDITIONAL_TASK_USER',
'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"'))) lic
LEFT JOIN fnd_licensed_role_usage lru on (extractvalue(value(lic),
'*/PERMISSION_SET',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"')) = lru.ROLE
WHERE PARAMETER = 'LICENSE_FILE'
UNION
--Get CTU
SELECT extractvalue(value(lic),
'*/NAME',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') NAME,
extractvalue(value(lic),
'*/NUMBER_OF_USERS',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') NO_OF_USER,
lru.USER_COUNT as TOTAL_LIC,
(extractvalue(value(lic),
'*/NUMBER_OF_USERS',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') - lru.USER_COUNT) AS REMAINING
FROM ifsapp.FND_LICENSE,
TABLE(xmlsequence(extract(XMLType(binary_value, 871),
'*/CUSTOM_TASK_USERS/CUSTOM_TASK_USER',
'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"'))) lic
LEFT JOIN fnd_licensed_role_usage lru on (extractvalue(value(lic),
'*/PERMISSION_SET',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"')) = lru.ROLE
WHERE PARAMETER = 'LICENSE_FILE'Sorry, correction to solution SQL (above will run in PL/SQL but below is corrected to allow it to work in an IAL), with added bonus inclusion of Full User licenses:
-- Get Full
SELECT
'Full User' AS NAME
, TO_CHAR(TO_NUMBER(fndli.LICENSE_LIMIT) - TO_NUMBER(fndli.USERS_LEFT)) AS NO_OF_USER
, TO_CHAR(fndli.LICENSE_LIMIT) AS TOTAL_LIC
, TO_CHAR(fndli.USERS_LEFT) AS REMAINING
FROM fnd_license_info fndli
UNION
--- Get LTU
SELECT extractvalue(value(lic),
'*/PERMISSION_SET', 'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') "NAME",
TO_CHAR(lru.USER_COUNT) as NO_OF_USER,
TO_CHAR(extractvalue(value(lic),
'*/NUMBER_OF_USERS', 'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"')) TOTAL_LIC,
TO_CHAR((extractvalue(value(lic), '*/NUMBER_OF_USERS', 'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') - lru.USER_COUNT)) AS REMAINING
FROM FND_LICENSE,
TABLE(xmlsequence(extract(XMLType(binary_value, 871),
'*/LIMITED_TASK_USERS/LIMITED_TASK_USER',
'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"'))) lic
left join fnd_licensed_role_usage lru on (extractvalue(value(lic),
'*/PERMISSION_SET',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"')) = lru.ROLE
WHERE PARAMETER = 'LICENSE_FILE'
UNION
--Get ATU
SELECT extractvalue(value(lic),
'*/PERMISSION_SET',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') NAME,
TO_CHAR(lru.USER_COUNT) as NO_OF_USER,
TO_CHAR(extractvalue(value(lic),
'*/NUMBER_OF_USERS',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"')) TOTAL_LIC,
TO_CHAR((extractvalue(value(lic),
'*/NUMBER_OF_USERS',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') - lru.USER_COUNT)) AS REMAINING
FROM FND_LICENSE,
TABLE(xmlsequence(extract(XMLType(binary_value, 871),
'*/ADDITIONAL_TASK_USERS/ADDITIONAL_TASK_USER',
'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"'))) lic
left join fnd_licensed_role_usage lru on (extractvalue(value(lic),
'*/PERMISSION_SET',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"')) = lru.ROLE
WHERE PARAMETER = 'LICENSE_FILE'
UNION
--Get CTU
SELECT extractvalue(value(lic),
'*/NAME',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') NAME,
TO_CHAR(extractvalue(value(lic),
'*/NUMBER_OF_USERS',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"')) NO_OF_USER,
TO_CHAR(lru.USER_COUNT) as TOTAL_LIC,
TO_CHAR((extractvalue(value(lic),
'*/NUMBER_OF_USERS',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"') - lru.USER_COUNT)) AS REMAINING
FROM FND_LICENSE,
TABLE(xmlsequence(extract(XMLType(binary_value, 871),
'*/CUSTOM_TASK_USERS/CUSTOM_TASK_USER',
'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"'))) lic
left join fnd_licensed_role_usage lru on (extractvalue(value(lic),
'*/PERMISSION_SET',
'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"')) = lru.ROLE
WHERE PARAMETER = 'LICENSE_FILE'
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.