Get IFS License usage from database

Hi, Check FND_LICENSED_ROLE_USAGE view and it should have the limited user count along will the full user count:

Hi @Srikanth  yes I found that, but I want to know how many licenses are spare.

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

1SELECT 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 @dsj, no that’s not what i’m after. I can get the Full User license count with no issues:

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 @Garak,

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 .

1--- Get LTUSELECT 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"'))) licWHERE PARAMETER = 'LICENSE_FILE'UNION--Get ATUSELECT 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"'))) licWHERE PARAMETER = 'LICENSE_FILE'UNION--Get CTUSELECT 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"'))) licWHERE PARAMETER = 'LICENSE_FILE'

Regards,

Mitchel

Thank @MiLeNL

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.

1--- Get LTUSELECT 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"'))) licLEFT JOIN fnd_licensed_role_usage lru on (extractvalue(value(lic),                   '*/PERMISSION_SET',                   'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"')) = lru.ROLEWHERE PARAMETER = 'LICENSE_FILE'UNION--Get ATUSELECT 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"'))) licLEFT JOIN fnd_licensed_role_usage lru on (extractvalue(value(lic),                   '*/PERMISSION_SET',                   'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"')) = lru.ROLEWHERE PARAMETER = 'LICENSE_FILE'UNION--Get CTUSELECT 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"'))) licLEFT JOIN fnd_licensed_role_usage lru on (extractvalue(value(lic),                   '*/PERMISSION_SET',                   'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"')) = lru.ROLEWHERE 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:

1-- Get FullSELECT    '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 REMAININGFROM fnd_license_info fndliUNION--- Get LTUSELECT 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"'))) licleft join fnd_licensed_role_usage lru on (extractvalue(value(lic),                   '*/PERMISSION_SET',                   'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"')) = lru.ROLEWHERE PARAMETER = 'LICENSE_FILE'UNION--Get ATUSELECT 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"'))) licleft join fnd_licensed_role_usage lru on (extractvalue(value(lic),                   '*/PERMISSION_SET',                   'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"')) = lru.ROLEWHERE PARAMETER = 'LICENSE_FILE'UNION--Get CTUSELECT 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"'))) licleft join fnd_licensed_role_usage lru on (extractvalue(value(lic),                   '*/PERMISSION_SET',                   'xmlns="urn:ifsworld-com:schemas:fndlicensemanagement_ifs_license_file"')) = lru.ROLEWHERE PARAMETER = 'LICENSE_FILE'