How to find the current item_id of Service Offering?

Products

• CA Service Catalog
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

I need to find out the item_id of a specific service, so I can make a Web Service call involving it - how can I find that in the database?

Environment

CA Service Catalog 17.3.X

Resolution

We'll take one of the "out of the box" offerings, as an example, "Procure Desktop". There's nothing particularly special about it; it just has a few rate items to it and as an ootb item it will exist on any system by default. The query 

select o.offering_id,o.offering_name, 
i.parent_id,i.rate_plan_id, i.child_id, 
rd.item_id,rd.rate_plan_id,rd.item_text 
from usm_offering o,usm_offering_ratedef_inclusion i,usm_rate_definition rd 
where o.offering_name = 'Procure Desktop' 
and i.parent_id = o.offering_id 
and rd.rate_plan_id = i.rate_plan_id 
and rd.status = 1 
and i.child_id = rd.item_id 

will return all the item_text information for every item_id associated with the given offering. However if all you're after is to get the list of item_id values for a given offering, then you can simplify this to: 

select o.offering_id,o.offering_name, 
i.parent_id,i.rate_plan_id, i.child_id 
from usm_offering o,usm_offering_ratedef_inclusion i 
where o.offering_name = 'Procure Desktop' 
and i.parent_id = o.offering_id 

replacing the offering_name with the one you need. There are a great many rate item IDs for each Service Offering; which one or ones you're after for any particular information is another question; this is where you need to use rate_row, rate_col, status and item_type columns in the usm_rate_definition table, so that bigger query may still be of use to you.