CA Service Management

Hi Team ,

We are trying the fetch the Cost / Other Info from Catalog request ticket which has value like "Related ticket : 1452771"

Here Related ticket is nothing but respective service desk ticket number.

Please let us know how can we fetch the data using SOAP or by SQL query.

Good Morning Kunal.

Please check on the below information.

As for usm_rate_definition.item_type, the following goes:
0 = Text
3 = Rate
6 = Numeric
7 = Boolean
8 = Adjustment
9 = Date
11= Day of Billing
14= Form

The query then could be like this:
select item_id,rate_plan_id,item_text,item_type,text_1,text_4 from usm_rate_definition where item_type = 3

Kind regards, Louis.

Good Morning Kunal.

The next query relates the 'cost' element to the offering:

select
o.offering_id,o.offering_name,
rp.rate_plan_name,
rd.item_text,rd.text_1,rd.text_4,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
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

order by o.offering_id

Thanks and kind regards, Louis.

Good Morning Kunal.

Not sure whether my earlier response got through.

The next query relates the 'cost' element to the offering:

select 
o.offering_id,o.offering_name, 
rp.rate_plan_name, 
rd.item_text,rd.text_1,rd.text_4,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 
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 

order by o.offering_id

Thanks and kind regards, Louis.

please refer to the screen shot I am trying to fetch related ticket as highlighted :

Good Afternoon Kunal.

Then check also on the following:

Login to SC and goto Catalog/Config/Request Management Configuration (request)
And check this: Access Control: Show Amount Column

Which specifies which user roles can view the Amount Column when viewing a request.
Default: Service Delivery Administrator, Super Business Unit Administrator, Administrator, Request Manager, Catalog Administrator

Kind regards, Louis.

Hi Louis,

I have used the SQL query but it does not give the related ticket value, as per the screenshot updated earlier  in the post i need to get this ticket number from a catalog request ticket.

Good Afternoon Kunal.

I did not understand your question right from the beginning.

When you wrote "We are trying the fetch the�Cost / Other Info from Catalog", I though you wanted to read the cost information. Not the ticket information. Sorry for this.

Execute this query for the relation between the Request in SC and the ticket number in SDM:

SELECT persid,ref_num,request_item_id,request_id FROM usm_link_ticket_request_item

Kind regards, Louis.

Good Afternoon Kunal.

And some examples of queries to be executed:

SELECT persid,ref_num,request_item_id,request_id FROM usm_link_ticket_request_item where request_id = 10009

select * from usm_request where request_id = 10009

-- depending on the result 'chg:' or 'cr:', change order or request/incident

select * from chg where persid = 'chg:400001'
select * from call_req where persid = 'cr:400001'

Kind regards, Louis.

Thanks Louis for your quick response