CA Service CatalogCA Service Management - Asset Portfolio ManagementCA Service Management - Service Desk Manager
We need to make a list of all the services and the respective prices in a specific BU. I can see the unit cost in the usm_rate_definition but we need to retrieve the final price that is shown in the user interface. -- I want to retrieve the list of all the offerings in a specific BU lets say Germany. And the respective prices of those offerings of that BU.
from usm_offering o,usm_offering_rplan_inclusion ori,usm_rate_plan rp,usm_rate_definition rd
where o.offering_id in (select o.offering_id from usm_rate_definition rd, usm_rate_plan rp, usm_offering_rplan_inclusion ori, usm_offering o where rp.rate_plan_id = rd.rate_plan_id and ori.child_id = rp.rate_plan_id and o.offering_id = ori.parent_id )
and rd.item_type=3 and o.domain = 'CATBU1' and rd.status = 1 and o.offering_id = ori.parent_id and ori.child_id = rp.rate_plan_id and rp.rate_plan_id = rd.rate_plan_id
Please note: 1. The line reading "o.domain = 'CATBU1' and" needs to be accomodated to the name of your BU. 2. The query can (of course) be changed per your wishes 3. Columns usm_rate_definition rd: rd.item_text = 'Cost', rd.numeric_1 = The price, rd.text_1 = The unit. 4. usm_rate_definition.item_type value and meaning: 0 = Text 3 = Rate 6 = Numeric 7 = Boolean 8 = Adjustment 9 = Date 11= Day of Billing 14= Form
This query is to be seen and taken as a (good) starting point. And possibly needs to be changed to meet your needs.