Create a Catalog Request via Webservice

[Paulo Freire Ferrarini]

I´m using process automation to create a process that allows me to create a Service Catalog Request via webservice.

I´m using the method addRequestWithForm to create the request.

In the process of the request creation I want this process to be dinamic, so I need to search in catalog database to found the follow required information:

offering_id, rete_item_id and item_id to be able to create my requests.

I´m able to find offering_id searching in table usm_offering.

But I´m not able to find relations that make me find the other required values.

Is there other tables that, with the offering_id value makes me find the other values?

[Jason_Wolfe]

Hey Paulo,

 

For addRequestWithForm you will need the offering id, option group id and the id of the form element of the option.

 

Following are a couple of options:

 

1) Use web services:

• Call getRatePlansIncludedInOffering passing the offering id, this will return the option group id(s)
• Call getRatePlanDefinition passing the offering id and the option group id returned from getRatePlansIncludedInOffering, you can parse the results for the itemid with itemtype=14 (form)

 

For more information on these methods I would recommend taking a look at Administration > Tools > Links > Web Services API.

 

2) Database queries:

• Pass the offering id to the following which will return the associated option group id(s):

         select * from usm_offering_rplan_inclusion where parent_id= <offering_id>

• Pass the option group id to the following which return the itemid of the form element:

         select * from usm_rate_definition where rate_plan_id=<option group id> and item_type=14 and status <> 0

 

Thanks,
Jason

[Paulo Freire Ferrarini]

OK, thanks for that information.

It will help me a lot.

Now I need only one more.

The forms associated to my service offerings are different, with each composed of diferent field.

Now what I want is to know all the form field names because I will need to populate the fields to submit the form,

[Jason_Wolfe]

Again a couple of options:

 

1) Call getFormDefinitions web service method

2) Query:

 

select * from usm_form_entities where form_entity_path COLLATE DATABASE_DEFAULT like '%'+(select text_1 COLLATE DATABASE_DEFAULT from usm_rate_definition where rate_plan_id=<option group id> and item_type=14 and status <> 0)+'%'

 

Note you might need to adjust the like query if your option has multiple forms associated.

[Paulo Freire Ferrarini]

Hi

With this query I can discover the form field labels, but to submit form with webservice I need the form fields ids.

Where I can find that?

[Jason_Wolfe]

The form field attributes including the _id are stored in usm_form_component_attributes. usm_form_component_attributes.form_comp_id correlates to usm_form_entities.form_entity_id.

 

Alternatively, getFormDefinitions does include this info:

 

<item>
 <key xsi:type="soapenc:string">_id</key>
 <value xsi:type="ns2:AttributeValue">
  <locale xsi:type="xsd:string">_system</locale>
  <name xsi:type="xsd:string">_id</name>
  <type xsi:type="xsd:string">1</type>
  <value xsi:type="xsd:string">caReportQuery</value>
 </value>
</item>

[Paulo Freire Ferrarini]

Thanks for the information, but I need a new one.

Some of my service options are flagged to make attachements required.

Because of this, I´m not able to submit the request via webservice without an attachment.

I need to know where I found this information.

Searching database I found the following table:

select * from usm_rate_definition, but I also find that if I change the service option this table contains the line with old and new definitions.

From my search too I think that the attachment required value is saved in the ENUM_9 database field.

What I need to do is to find the top item_id form this table where item_type = 0?

[Jason_Wolfe]

Hey Paulo,

 

At quick glance that looks to be the case, attachment mandatory sets enum_9=1 so you might be able to check similar to the following:

 

select top 1 enum_9 from usm_rate_definition where rate_plan_id=<rate_plan_id> and status <> 0 

 

Thanks,

Jason

[Paulo Freire Ferrarini]

I'm trying to create the attachment with one of this 2 WS methods:

addAttachmentToRequestItemWithPath

addRequestAttachmentWithPath

The request is being attached, but I receive an error to submit the request.

If I access the created request in catalog interface, I see the attachment but if I try to submit by interface I receive a message that attachment is mandatory.

If I attach a new file by interface I'm able to submit this request.

Any Idea?

[Paulo Freire Ferrarini]

Someone has an idea?

[Louis van Amelsfort]

Good Afternoon Paulo.

I fiound the following explanation.
And although only tested through the SOAPUI, it might give you an idea for your environment then too.

1) we create a empty request
Client:
<requestData xsi:type="soapenc:string" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">name: ABAKUS: REQ0030198 - RITM0042146!created_date:2017-07-31 04:25:56!desired_date:!priority:4!req_by_user_id:H715000!req_for_user_id:H715000!comments:ServiceNow SYS_ID:CAT001371</requestData>
<requestValuesData xsi:type="soapenc:string" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">authorization_number:RITM0042146!project:CAT001371!gl_code:88888!cost_center:(spezifiziert in der Service auftrag Form)!ship_to_address1:N/A!ship_to_city:N/A!ship_to_state:N/A!ship_to_postal:N/A!ship_to_country:N/A</requestValuesData>

Local amelo01-I1660:
<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://services.soap.usm.ca.com">
<soapenv:Header/>
<soapenv:Body>
<ser:saveRequest soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<sessionID xsi:type="xsd:string">8dc7b70b-d514-4fe8-aa8c-cd316ac8e1b6</sessionID>
<requestData xsi:type="xsd:string">name: ABAKUS: REQ0030198 - RITM0042146|created_date:2017-07-31 04:25:56|desired_date:|priority:4|req_by_user_id:spadmin|req_for_user_id:amelo01|comments:ServiceNow SYS_ID:CAT001371</requestData>
<requestValuesData xsi:type="xsd:string">authorization_number:RITM0042146|project:CAT001371|gl_code:88888|cost_center:(spezifiziert in der Service auftrag Form)|ship_to_address1:N/A|ship_to_city:N/A|ship_to_state:N/A|ship_to_postal:N/A|ship_to_country:N/A</requestValuesData>
<requestValuesDataType xsi:type="xsd:string"></requestValuesDataType>
<addedOfferings xsi:type="xsd:string"></addedOfferings>
<removedOfferings xsi:type="xsd:string"></removedOfferings>
<addedItems xsi:type="xsd:string"></addedItems>
</ser:saveRequest>
</soapenv:Body>
</soapenv:Envelope>

Result: <requestID xsi:type="xsd:int">10167</requestID>

2) then we save the service into the request
SELECT o.offering_id, o.offering_name,
rp.rate_plan_id, rp.status,rp.date_available, rp.is_inherited, rp.total_rows, rp.total_cols,rp.domain,rp.sort_order_no,rp.rate_plan_name,rp.description,
ori.id as iid, ori.layout as layout
FROM usm_offering o, usm_rate_plan rp,usm_offering_rplan_inclusion ori
WHERE o.offering_name = 'SAP Lieferant'
AND ori.parent_id=o.offering_id
AND rp.rate_plan_id=ori.child_id
AND rp.status in (1,6)
Result: 10248 SAP Lieferant 10171 ~ Bestellung von Services

Client:
<requestID xsi:type="xsd:int">298719</requestID>
<addedOfferings xsi:type="soapenc:string" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">offering_id:12396</addedOfferings>

Local amelo01-I1660:
<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://services.soap.usm.ca.com">
<soapenv:Header/>
<soapenv:Body>
<ser:saveRequestItems soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<sessionID xsi:type="xsd:string">8dc7b70b-d514-4fe8-aa8c-cd316ac8e1b6</sessionID>
<requestID xsi:type="xsd:int">10167</requestID>
<addedOfferings xsi:type="xsd:string">offering_id:10248</addedOfferings>
<removedOfferings xsi:type="xsd:string"></removedOfferings>
<addedItems xsi:type="xsd:string"></addedItems>
</ser:saveRequestItems>
</soapenv:Body>
</soapenv:Envelope>

3) after that we try to fill out the attached form (and here occurs the error)
select id from usm_subscription_detail where request_id=10167 and subscription_type=5
Result: 10703

Client:
<subscriptionDetailID xsi:type="xsd:int">4376088</subscriptionDetailID>
<formValuesData xsi:type="soapenc:string" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">pri_phone_number:9932431|email_address:[e-mail]|first_name:Rüdiger|last_name:Kinert|userid:H715000|FromDate:|befrVon:|befrVonPflicht:|ToDate:|befrBis:|befrBisPflicht:|lokation_lokation:Chemnitz - Chemnitztalstr. 13 (enviaM)|lokation_raum:302a|lokation_haus:CT01|Beschreibung: Anfragetyp: Vorhandener Nutzer zuordnen.|ServiceCode:SN(CAT001371);acc#|Account:0b4cd065f6cdba4f9254cbe52421aaba~H715000|hiddenFields:;hiddenFields;FromDate;ToDate;ServiceCode;befrBisPflicht;befrVonPflicht;Beschreibung_pflicht;PC;</formValuesData>

Original data from client:
<formValuesData xsi:type="xsd:string">pri_phone_number:9932431|email_address:[e-mail]|first_name:Louis|last_name:Amelsfort|userid:amelo01|FromDate:|befrVon:|befrVonPflicht:|ToDate:|befrBis:|befrBisPflicht:|lokation_lokation:Chemnitz - Chemnitztalstr. 13 (enviaM)|lokation_raum:302a|lokation_haus:CT01|Beschreibung: Anfragetyp: Vorhandener Nutzer zuordnen.|ServiceCode:SN(CAT001371);acc#|Account:0b4cd065f6cdba4f9254cbe52421aaba~H715000|hiddenFields:;hiddenFields;FromDate;ToDate;ServiceCode;befrBisPflicht;befrVonPflicht;Beschreibung_pflicht;PC;</formValuesData>

Local amelo01-I1660:
<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://services.soap.usm.ca.com">
<soapenv:Header/>
<soapenv:Body>
<ser:saveRequestForm soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<sessionID xsi:type="xsd:string">8dc7b70b-d514-4fe8-aa8c-cd316ac8e1b6</sessionID>
<subscriptionDetailID xsi:type="xsd:int">10703</subscriptionDetailID>
<formValuesData xsi:type="xsd:string">pri_phone_number:9932431|email_address:[e-mail]|first_name:Louis|last_name:Amelsfort|userid:amelo01</formValuesData>
</ser:saveRequestForm>
</soapenv:Body>
</soapenv:Envelope>
Result: NO error.

With:
<formValuesData xsi:type="xsd:string">pri_phone_number:9932431|email_address:[e-mail]|first_name:Louis|last_name:Amelsfort|userid:amelo01</formValuesData>
It works okay.

I then added "|FromDate:" and get the below error:
<formValuesData xsi:type="xsd:string">pri_phone_number:9932431|email_address:[e-mail]|first_name:Louis|last_name:Amelsfort|userid:amelo01|FromDate:</formValuesData>
Result: Error in view.log:
2017/08/04 18.15.41.640 DEBUG [http-bio-8080-exec-11] [RequestServiceImpl] propsFormData :{userid=H715000, first_name=Rüdiger, lokation_haus=CT01, befrBisPflicht=, email_address=[e-mail], hiddenFields=;hiddenFields;FromDate;ToDate;ServiceCode;befrBisPflicht;befrVonPflicht;Beschreibung_pflicht;PC;, FromDate=, lokation_lokation=Chemnitz - Chemnitztalstr. 13 (enviaM), ServiceCode=SN(CAT001371);acc#, Account=0b4cd065f6cdba4f9254cbe52421aaba|H715000, lokation_raum=302a, befrVonPflicht=, befrBis=, ToDate=, last_name=Kinert, pri_phone_number=9932431, Beschreibung= Anfragetyp: Vorhandener Nutzer zuordnen., befrVon=}
2017/08/04 18.15.41.641 ERROR [http-bio-8080-exec-11] [RequestServiceImpl] SOAPREQ010
java.lang.NullPointerException

<formValuesData xsi:type="xsd:string">pri_phone_number:9932431|email_address:[e-mail]|first_name:Louis|last_name:Amelsfort|userid:amelo01|FromDate:08/04/2017 0:0:0</formValuesData>
"|FromDate:08/04/2017 0:0:0", all runs okay again.

<formValuesData xsi:type="xsd:string">pri_phone_number:9932431|email_address:[e-mail]|first_name:Louis|last_name:Amelsfort|userid:amelo01|FromDate:08/04/2017 0:0:0|befrVon:08/04/2017 0:0:0|befrVonPflicht:|ToDate:08/04/2017 0:0:0|befrBis:08/04/2017 0:0:0|befrBisPflicht:|lokation_lokation:Chemnitz - Chemnitztalstr. 13 (enviaM)|lokation_raum:302a|lokation_haus:CT01|Beschreibung: Anfragetyp: Vorhandener Nutzer zuordnen.|ServiceCode:SN(CAT001371);acc#|Account:0b4cd065f6cdba4f9254cbe52421aaba~H715000|hiddenFields:;hiddenFields;FromDate;ToDate;ServiceCode;befrBisPflicht;befrVonPflicht;Beschreibung_pflicht;PC;</formValuesData>
>>ERROR:
<formValuesData xsi:type="xsd:string">pri_phone_number:9932431|email_address:[e-mail]|first_name:Louis|last_name:Amelsfort|userid:amelo01|FromDate:08/04/2017 0:0:0|befrVon:08/04/2017 0:0:0|befrVonPflicht:08/04/2017 0:0:0|ToDate:08/04/2017 0:0:0|befrBis:08/04/2017 0:0:0|befrBisPflicht:08/04/2017 0:0:0|lokation_lokation:Chemnitz - Chemnitztalstr. 13 (enviaM)|lokation_raum:302a|lokation_haus:CT01|Beschreibung: Anfragetyp: Vorhandener Nutzer zuordnen.|ServiceCode:SN(CAT001371);acc#|Account:0b4cd065f6cdba4f9254cbe52421aaba~H715000|hiddenFields:;hiddenFields;FromDate;ToDate;ServiceCode;befrBisPflicht;befrVonPflicht;Beschreibung_pflicht;PC;</formValuesData>

Date fields may not be empty. All 'date' fields have been given a value. 
The date value must be composed of date & time (08/04/2017 0:0:0)

Kind regards, Louis.