How to list all services with prices for a BU

Products

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

Issue/Introduction

N/A

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. 

Environment

Windows

Resolution

select 
o.offering_id,o.offering_name, 
rp.rate_plan_name, 
rd.item_text,rd.numeric_1,rd.text_1,rd.category,rd.category_class,rd.status,rd.item_type, 
ori.id,ori.parent_id,rp.rate_plan_id,rd.rate_plan_id

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

Additional Information

Note:

This query is to be seen and taken as a (good) starting point.
And possibly needs to be changed to meet your needs.