[Home] [Help]
PACKAGE BODY: APPS.IEM_SERVICEREQUEST_PVT
Source
1 PACKAGE BODY IEM_SERVICEREQUEST_PVT AS
2 /* $Header: iemvsrvb.pls 120.3.12010000.2 2008/11/16 07:49:40 rtripath ship $ */
3 --
4 --
5 -- Purpose: Wrapper API for ServiceRequest
6 --
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- Liang Xia 3/24/2002 Created
10 -- Liang Xia 1/30/2003 Rewrite wrappers to compliance with CS changes in 115.9
11 -- Branching on iemvsrvb.pls 115.3, iemvsrvs.pls 115.2
12 -- Working CS file version:
13 -- cspsrs.pls 115.46
14 -- cspsrb.pls 115.55
15 -- csvsrs.pls 115.65
16 -- csvsrb.pls 115.193
17 -- Liang Xia 8/25/2003 Create Update_Status_Wrap based on 1159 release
18 -- cspsrs.pls 115.49
19 -- cspsrb.pls 115.63
20 -- csvsrs.pls 115.67
21 -- csvsrb.pls 115.222
22 -- Liang Xia 10/17/2003 Pass cust_pref_lang_code, last_update_channel to
23 -- updated SR api, default last_update_channel as "EMAIL"
24 -- for 115.10 compliance
25
26 -- Liang Xia 08/16/2004 Added fnd_global.apps_initialize for Update_Status on 115.10
27 -- to accomendate new feature of resp-type mapping feature
28 -- bug 3788494 ( iemvsrvs.pls 115.3 )
29 -- PKESANI 02/09/2006 As a part of ACSR project, added procedure IEM_CREATE_SR
30 -- as a wrapper API for auto create SR.
31 -- --------- ------ -----------------------------------------
32
33 -- Enter procedure, function bodies as shown below
34
35 /*GLOBAL VARIABLES AVAILABLE TO THE PUBLIC FOR CALLING
36 ===================================================*/
37
38 PROCEDURE Create_ServiceRequest_Wrap
39 ( p_api_version IN NUMBER,
40 p_init_msg_list IN VARCHAR2 := null,
41 p_commit IN VARCHAR2 := null,
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_msg_count OUT NOCOPY NUMBER,
44 x_msg_data OUT NOCOPY VARCHAR2,
45 p_resp_appl_id IN NUMBER := NULL,
46 p_resp_id IN NUMBER := NULL,
47 p_user_id IN NUMBER := NULL,
48 p_login_id IN NUMBER := NULL,
49 p_org_id IN NUMBER := NULL,
50 p_request_id IN NUMBER := NULL,
51 p_request_number IN VARCHAR2 := NULL,
52 p_service_request_rec IN service_request_rec_type,
53 p_notes IN notes_table,
54 p_contacts IN contacts_table,
55 x_request_id OUT NOCOPY NUMBER,
56 x_request_number OUT NOCOPY VARCHAR2,
57 x_interaction_id OUT NOCOPY NUMBER,
58 x_workflow_process_id OUT NOCOPY NUMBER
59 )
60
61 IS
62 -- l_api_version CONSTANT NUMBER := 2.0;
63 -- l_api_name CONSTANT VARCHAR2(30) := 'Create_ServiceRequest';
64 -- l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
65
66 l_api_name VARCHAR2(255):='Create_ServiceRequest_Wrap';
67 l_api_version_number NUMBER:=2.0;
68 --Added by LiangXia on Dec18,2002 for CS changes in 115.9
69 l_cs_version_number NUMBER:=3.0;
70
71
72 l_userid NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
73 l_login NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ;
74
75 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
76 l_msg_count NUMBER := 0;
77 l_msg_data VARCHAR2(2000);
78 l_index NUMBER := 1;
79
80 l_service_request_rec CS_ServiceRequest_PUB.service_request_rec_type;
81 l_notes CS_ServiceRequest_PUB.notes_table;
82 l_contacts CS_ServiceRequest_PUB.contacts_table;
83
84 --Added by LiangXia on Dec18,2002 for CS changes in 115.9
85 l_individual_owner NUMBER;
86 l_group_owner NUMBER;
87 l_individual_type VARCHAR2(200);
88
89 IEM_SERVICEREQUEST_NOT_CREATE EXCEPTION;
90
91 BEGIN
92 -- Standard Start of API savepoint
93 SAVEPOINT Create_ServiceRequest_Wrap;
94
95 -- Standard call to check for call compatibility.
96 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
97 p_api_version,
98 l_api_name,
99 G_PKG_NAME)
100 THEN
101
102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
103 END IF;
104
105
106 -- Initialize message list if p_init_msg_list is set to TRUE.
107 IF FND_API.to_Boolean( p_init_msg_list )
108 THEN
109 FND_MSG_PUB.initialize;
110 END IF;
111
112 CS_ServiceRequest_PUB.initialize_rec(l_service_request_rec);
113
114 -- IF p_service_request_rec is not null then
115 l_service_request_rec.request_date := p_service_request_rec.request_date;
116 l_service_request_rec.type_id := p_service_request_rec.type_id;
117 l_service_request_rec.type_name := p_service_request_rec.type_name;
118 l_service_request_rec.status_id := p_service_request_rec.status_id;
119 l_service_request_rec.status_name := p_service_request_rec.status_name;
120 l_service_request_rec.severity_id := p_service_request_rec.severity_id;
121 l_service_request_rec.severity_name := p_service_request_rec.severity_name;
122 l_service_request_rec.urgency_id := p_service_request_rec.urgency_id;
123 l_service_request_rec.urgency_name := p_service_request_rec.urgency_name;
124 l_service_request_rec.closed_date := p_service_request_rec.closed_date;
125 l_service_request_rec.owner_id := p_service_request_rec.owner_id;
126 l_service_request_rec.owner_group_id := p_service_request_rec.owner_group_id;
127 l_service_request_rec.publish_flag := p_service_request_rec.publish_flag;
128 l_service_request_rec.summary := p_service_request_rec.summary;
129 l_service_request_rec.caller_type := p_service_request_rec.caller_type;
130 l_service_request_rec.customer_id := p_service_request_rec.customer_id;
131 l_service_request_rec.customer_number := p_service_request_rec.customer_number;
132 l_service_request_rec.employee_id := p_service_request_rec.employee_id;
133 l_service_request_rec.employee_number := p_service_request_rec.employee_number;
134 l_service_request_rec.verify_cp_flag := p_service_request_rec.verify_cp_flag;
135 l_service_request_rec.customer_product_id := p_service_request_rec.customer_product_id;
136 l_service_request_rec.platform_id := p_service_request_rec.platform_id;
137 l_service_request_rec.platform_version := p_service_request_rec.platform_version;
138 l_service_request_rec.db_version := p_service_request_rec.db_version;
139 l_service_request_rec.platform_version_id := p_service_request_rec.platform_version_id;
140 l_service_request_rec.cp_component_id := p_service_request_rec.cp_component_id;
141 l_service_request_rec.cp_component_version_id := p_service_request_rec.cp_component_version_id;
142 l_service_request_rec.cp_subcomponent_id := p_service_request_rec.cp_subcomponent_id;
143 l_service_request_rec.cp_subcomponent_version_id := p_service_request_rec.cp_subcomponent_version_id;
144 l_service_request_rec.language_id := p_service_request_rec.language_id;
145 l_service_request_rec.language := p_service_request_rec.language;
146 l_service_request_rec.cp_ref_number := p_service_request_rec.cp_ref_number;
147 l_service_request_rec.inventory_item_id := p_service_request_rec.inventory_item_id ;
148 l_service_request_rec.inventory_item_conc_segs := p_service_request_rec.inventory_item_conc_segs;
149 l_service_request_rec.inventory_item_segment1 := p_service_request_rec.inventory_item_segment1;
150 l_service_request_rec.inventory_item_segment2 := p_service_request_rec.inventory_item_segment2;
151 l_service_request_rec.inventory_item_segment3 := p_service_request_rec.inventory_item_segment3;
152 l_service_request_rec.inventory_item_segment4 := p_service_request_rec.inventory_item_segment4;
153 l_service_request_rec.inventory_item_segment5 := p_service_request_rec.inventory_item_segment5;
154 l_service_request_rec.inventory_item_segment6 := p_service_request_rec.inventory_item_segment6;
155 l_service_request_rec.inventory_item_segment7 := p_service_request_rec.inventory_item_segment7;
156 l_service_request_rec.inventory_item_segment8 := p_service_request_rec.inventory_item_segment8;
157 l_service_request_rec.inventory_item_segment9 := p_service_request_rec.inventory_item_segment9;
158 l_service_request_rec.inventory_item_segment10 := p_service_request_rec.inventory_item_segment10;
159 l_service_request_rec.inventory_item_segment11 := p_service_request_rec.inventory_item_segment11;
160 l_service_request_rec.inventory_item_segment12 := p_service_request_rec.inventory_item_segment12;
161 l_service_request_rec.inventory_item_segment13 := p_service_request_rec.inventory_item_segment13;
162 l_service_request_rec.inventory_item_segment14 := p_service_request_rec.inventory_item_segment14;
163 l_service_request_rec.inventory_item_segment15 := p_service_request_rec.inventory_item_segment15;
164 l_service_request_rec.inventory_item_segment16 := p_service_request_rec.inventory_item_segment16;
165 l_service_request_rec.inventory_item_segment17 := p_service_request_rec.inventory_item_segment17;
166 l_service_request_rec.inventory_item_segment18 := p_service_request_rec.inventory_item_segment18;
167 l_service_request_rec.inventory_item_segment19 := p_service_request_rec.inventory_item_segment19;
168 l_service_request_rec.inventory_item_segment20 := p_service_request_rec.inventory_item_segment20;
169 l_service_request_rec.inventory_item_vals_or_ids := p_service_request_rec.inventory_item_vals_or_ids;
170 l_service_request_rec.inventory_org_id := p_service_request_rec.inventory_org_id;
171 l_service_request_rec.current_serial_number := p_service_request_rec.current_serial_number;
172 l_service_request_rec.original_order_number := p_service_request_rec.original_order_number;
173 l_service_request_rec.purchase_order_num := p_service_request_rec.purchase_order_num;
174 l_service_request_rec.problem_code := p_service_request_rec.problem_code;
175 l_service_request_rec.exp_resolution_date := p_service_request_rec.exp_resolution_date;
176 l_service_request_rec.install_site_use_id := p_service_request_rec.install_site_use_id;
177 l_service_request_rec.request_attribute_1 := p_service_request_rec.request_attribute_1;
178 l_service_request_rec.request_attribute_2 := p_service_request_rec.request_attribute_2;
179 l_service_request_rec.request_attribute_3 := p_service_request_rec.request_attribute_3;
180 l_service_request_rec.request_attribute_4 := p_service_request_rec.request_attribute_4;
181 l_service_request_rec.request_attribute_5 := p_service_request_rec.request_attribute_5;
182 l_service_request_rec.request_attribute_6 := p_service_request_rec.request_attribute_6;
183 l_service_request_rec.request_attribute_7 := p_service_request_rec.request_attribute_7;
184 l_service_request_rec.request_attribute_8 := p_service_request_rec.request_attribute_8;
185 l_service_request_rec.request_attribute_9 := p_service_request_rec.request_attribute_9;
186 l_service_request_rec.request_attribute_10 := p_service_request_rec.request_attribute_10;
187 l_service_request_rec.request_attribute_11 := p_service_request_rec.request_attribute_11;
188 l_service_request_rec.request_attribute_12 := p_service_request_rec.request_attribute_12;
189 l_service_request_rec.request_attribute_13 := p_service_request_rec.request_attribute_13;
190 l_service_request_rec.request_attribute_14 := p_service_request_rec.request_attribute_14;
191 l_service_request_rec.request_attribute_15 := p_service_request_rec.request_attribute_15;
192 l_service_request_rec.request_context := p_service_request_rec.request_context ;
193 l_service_request_rec.bill_to_site_use_id := p_service_request_rec.bill_to_site_use_id;
194 l_service_request_rec.bill_to_contact_id := p_service_request_rec.bill_to_contact_id;
195 l_service_request_rec.ship_to_site_use_id := p_service_request_rec.ship_to_site_use_id;
196 l_service_request_rec.ship_to_contact_id := p_service_request_rec.ship_to_contact_id ;
197 l_service_request_rec.resolution_code := p_service_request_rec.resolution_code;
198 l_service_request_rec.act_resolution_date := p_service_request_rec.act_resolution_date;
199 l_service_request_rec.public_comment_flag := p_service_request_rec.public_comment_flag ;
200 l_service_request_rec.parent_interaction_id := p_service_request_rec.parent_interaction_id;
201 l_service_request_rec.contract_service_id := p_service_request_rec.contract_service_id;
202 l_service_request_rec.contract_service_number := p_service_request_rec.contract_service_number;
203 l_service_request_rec.contract_id := p_service_request_rec.contract_id;
204 l_service_request_rec.project_number := p_service_request_rec.project_number;
205 l_service_request_rec.qa_collection_plan_id := p_service_request_rec.qa_collection_plan_id;
206 l_service_request_rec.account_id := p_service_request_rec.account_id;
207 l_service_request_rec.resource_type := p_service_request_rec.resource_type;
208 l_service_request_rec.resource_subtype_id := p_service_request_rec.resource_subtype_id;
209 l_service_request_rec.cust_po_number := p_service_request_rec.cust_po_number;
210 l_service_request_rec.cust_ticket_number := p_service_request_rec.cust_ticket_number;
211 l_service_request_rec.sr_creation_channel := p_service_request_rec.sr_creation_channel;
212 l_service_request_rec.obligation_date := p_service_request_rec.obligation_date ;
213 l_service_request_rec.time_zone_id := p_service_request_rec.time_zone_id ;
214 l_service_request_rec.time_difference := p_service_request_rec.time_difference ;
215 l_service_request_rec.site_id := p_service_request_rec.site_id;
216 l_service_request_rec.customer_site_id := p_service_request_rec.customer_site_id;
217 l_service_request_rec.territory_id := p_service_request_rec.territory_id;
218 l_service_request_rec.initialize_flag := p_service_request_rec.initialize_flag;
219 l_service_request_rec.cp_revision_id := p_service_request_rec.cp_revision_id;
220 l_service_request_rec.inv_item_revision := p_service_request_rec.inv_item_revision;
221 l_service_request_rec.inv_component_id := p_service_request_rec.inv_component_id;
222 l_service_request_rec.inv_component_version := p_service_request_rec.inv_component_version;
223 l_service_request_rec.inv_subcomponent_id := p_service_request_rec.inv_subcomponent_id;
224 l_service_request_rec.inv_subcomponent_version := p_service_request_rec.inv_subcomponent_version;
225 ------jngeorge---------------07/12/01
226 l_service_request_rec.tier := p_service_request_rec.tier;
227 l_service_request_rec.tier_version := p_service_request_rec.tier_version;
228 l_service_request_rec.operating_system := p_service_request_rec.operating_system;
229 l_service_request_rec.operating_system_version := p_service_request_rec.operating_system_version;
230 l_service_request_rec.database := p_service_request_rec.database;
231 l_service_request_rec.cust_pref_lang_id := p_service_request_rec.cust_pref_lang_id;
232 l_service_request_rec.category_id := p_service_request_rec.category_id;
233 l_service_request_rec.group_type := p_service_request_rec.group_type;
234 l_service_request_rec.group_territory_id := p_service_request_rec.group_territory_id;
235 l_service_request_rec.inv_platform_org_id := p_service_request_rec.inv_platform_org_id;
236 l_service_request_rec.component_version := p_service_request_rec.component_version;
237 l_service_request_rec.subcomponent_version := p_service_request_rec.subcomponent_version;
238 l_service_request_rec.product_revision := p_service_request_rec.product_revision;
239 l_service_request_rec.comm_pref_code := p_service_request_rec.comm_pref_code;
240 ---- Added for Post 11.5.6 Enhancement
241 -- added by Liang in 115.10 for client for update last_update_channel
242 l_service_request_rec.cust_pref_lang_code := P_service_request_rec.cust_pref_lang_code;
243 l_service_request_rec.last_update_channel := p_service_request_rec.last_update_channel;
244
245 --Added by LiangXia on Dec 19,2002 to for CS changes in 115.9
246 l_service_request_rec.creation_program_code := 'EMAILCENTER';
247 -- end if;
248
249 l_notes.delete;
250 IF p_notes.count>0 THEN
251 FOR i in p_notes.FIRST..p_notes.LAST LOOP
252 l_notes(l_index).NOTE := p_notes(i).NOTE;
253 l_notes(l_index).NOTE_DETAIL := p_notes(i).NOTE_DETAIL;
254 l_notes(l_index).NOTE_TYPE := p_notes(i).NOTE_TYPE;
255 l_notes(l_index).NOTE_CONTEXT_TYPE_01 := p_notes(i).NOTE_CONTEXT_TYPE_01;
256 l_notes(l_index).NOTE_CONTEXT_TYPE_ID_01 := p_notes(i).NOTE_CONTEXT_TYPE_ID_01;
257 l_notes(l_index).NOTE_CONTEXT_TYPE_02 := p_notes(i).NOTE_CONTEXT_TYPE_02;
258 l_notes(l_index).NOTE_CONTEXT_TYPE_ID_02 := p_notes(i).NOTE_CONTEXT_TYPE_ID_02;
259 l_notes(l_index).NOTE_CONTEXT_TYPE_03 := p_notes(i).NOTE_CONTEXT_TYPE_03;
260 l_notes(l_index).NOTE_CONTEXT_TYPE_ID_03 := p_notes(i).NOTE_CONTEXT_TYPE_ID_03;
261 l_index:=l_index+1;
262 END LOOP;
263 END IF;
264
265 l_contacts.delete;
266 l_index := 1;
267 IF p_contacts.count>0 THEN
268 FOR x in p_contacts.FIRST..p_contacts.LAST LOOP
269 l_contacts(l_index).SR_CONTACT_POINT_ID := p_contacts(x).SR_CONTACT_POINT_ID;
270 l_contacts(l_index).PARTY_ID := p_contacts(x).PARTY_ID;
271 l_contacts(l_index).CONTACT_POINT_ID := p_contacts(x).CONTACT_POINT_ID;
272 l_contacts(l_index).CONTACT_POINT_TYPE := p_contacts(x).CONTACT_POINT_TYPE;
273 l_contacts(l_index).PRIMARY_FLAG := p_contacts(x).PRIMARY_FLAG;
274 l_contacts(l_index).CONTACT_TYPE := p_contacts(x).CONTACT_TYPE;
275 l_index:=l_index+1;
276 END LOOP;
277 END IF;
278
279
280
281 CS_ServiceRequest_PUB.Create_ServiceRequest (
282 p_api_version => l_cs_version_number,
283 p_init_msg_list => p_init_msg_list,
284 p_commit => p_commit,
285 x_return_status =>l_return_status,
286 x_msg_count => l_msg_count,
287 x_msg_data => l_msg_data,
288 p_resp_appl_id => p_resp_appl_id,
289 p_resp_id => p_resp_id,
290 p_user_id => p_user_id,
291 p_login_id => p_login_id,
292 p_org_id => p_org_id,
293 p_request_id => p_request_id,
294 p_request_number => p_request_number,
295 p_service_request_rec => l_service_request_rec,
296 p_notes => l_notes,
297 p_contacts => l_contacts,
298 x_request_id => x_request_id,
299 x_request_number => x_request_number,
300 x_interaction_id => x_interaction_id,
301 x_workflow_process_id => x_workflow_process_id,
302
303 --Added by liangxia on Dec18, 2002 for CS changes in 115.9
304 x_individual_owner => l_individual_owner,
305 x_group_owner => l_group_owner,
306 x_individual_type => l_individual_type
307 );
308
309 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
310 raise IEM_SERVICEREQUEST_NOT_CREATE;
311 end if;
312
313 x_return_status := l_return_status ;
314
315 --Standard check of p_commit
316 IF FND_API.to_Boolean(p_commit) THEN
317 COMMIT WORK;
318 END IF;
319
320 EXCEPTION
321 WHEN IEM_SERVICEREQUEST_NOT_CREATE THEN
322 ROLLBACK TO Create_ServiceRequest_Wrap;
323 x_return_status := l_return_status ;
324 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
325
326 WHEN FND_API.G_EXC_ERROR THEN
327 ROLLBACK TO Create_ServiceRequest_Wrap;
328 x_return_status := FND_API.G_RET_STS_ERROR ;
329 FND_MSG_PUB.Count_And_Get
330 ( p_count => x_msg_count,p_data => x_msg_data);
331
332
333 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
334 ROLLBACK TO Create_ServiceRequest_Wrap;
335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
336 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
337
338 WHEN OTHERS THEN
339 ROLLBACK TO Create_ServiceRequest_Wrap;
340 x_return_status := FND_API.G_RET_STS_ERROR;
341
342 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
343 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , l_api_name);
344 END IF;
345
346 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
347 END;
348
349 PROCEDURE Update_ServiceRequest_Wrap(
350 p_api_version IN NUMBER,
351 p_init_msg_list IN VARCHAR2 := null,
352 p_commit IN VARCHAR2 := null,
353 x_return_status OUT NOCOPY VARCHAR2,
354 x_msg_count OUT NOCOPY NUMBER,
355 x_msg_data OUT NOCOPY VARCHAR2,
356 p_request_id IN NUMBER := NULL,
357 p_request_number IN VARCHAR2 := NULL,
358 p_audit_comments IN VARCHAR2 := NULL,
359 p_object_version_number IN NUMBER,
360 p_resp_appl_id IN NUMBER := NULL,
361 p_resp_id IN NUMBER := NULL,
362 p_last_updated_by IN NUMBER,
363 p_last_update_login IN NUMBER := NULL,
364 p_last_update_date IN DATE,
365 p_service_request_rec IN service_request_rec_type,
366 p_notes IN notes_table,
367 p_contacts IN contacts_table,
368 p_called_by_workflow IN VARCHAR2 := FND_API.G_FALSE,
369 p_workflow_process_id IN NUMBER := NULL,
370 x_workflow_process_id OUT NOCOPY NUMBER,
371 x_interaction_id OUT NOCOPY NUMBER
372 )
373 IS
374 -- l_api_version CONSTANT NUMBER := 2.0;
375 -- l_api_name CONSTANT VARCHAR2(30) := 'Update_ServiceRequest_Wrap';
376 -- l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
377
378 l_api_name VARCHAR2(255):='Update_ServiceRequest_Wrap';
379 l_api_version_number NUMBER:= 2.0;
380
381 --Added by LiangXia on Dec18,2002 for CS changes in 115.9
382 l_cs_version_number NUMBER:=3.0;
383
384 l_userid NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
385 l_login NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ;
386
387 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
388 l_msg_count NUMBER := 0;
389 l_msg_data VARCHAR2(2000);
390 l_index NUMBER := 1;
391
392 l_service_request_rec CS_ServiceRequest_PUB.service_request_rec_type;
393 l_notes CS_ServiceRequest_PUB.notes_table;
394 l_contacts CS_ServiceRequest_PUB.contacts_table;
395
396 IEM_SERVICEREQUEST_NOT_UPDATED EXCEPTION;
397
398 BEGIN
399 -- Standard Start of API savepoint
400 SAVEPOINT Update_ServiceRequest_Wrap;
401 -- Standard call to check for call compatibility.
402 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
403 p_api_version,
404 l_api_name,
405 G_PKG_NAME)
406 THEN
407
408 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
409 END IF;
410
411
412 -- Initialize message list if p_init_msg_list is set to TRUE.
413 IF FND_API.to_Boolean( p_init_msg_list )
414 THEN
415 FND_MSG_PUB.initialize;
416 END IF;
417
418 CS_ServiceRequest_PUB.initialize_rec(l_service_request_rec);
419
420 -- IF p_service_request_rec is not null then
421 l_service_request_rec.request_date := p_service_request_rec.request_date;
422 l_service_request_rec.type_id := p_service_request_rec.type_id;
423 l_service_request_rec.type_name := p_service_request_rec.type_name;
424 l_service_request_rec.status_id := p_service_request_rec.status_id;
425 l_service_request_rec.status_name := p_service_request_rec.status_name;
426 l_service_request_rec.severity_id := p_service_request_rec.severity_id;
427 l_service_request_rec.severity_name := p_service_request_rec.severity_name;
428 l_service_request_rec.urgency_id := p_service_request_rec.urgency_id;
429 l_service_request_rec.urgency_name := p_service_request_rec.urgency_name;
430 l_service_request_rec.closed_date := p_service_request_rec.closed_date;
431 l_service_request_rec.owner_id := p_service_request_rec.owner_id;
432 l_service_request_rec.owner_group_id := p_service_request_rec.owner_group_id;
433 l_service_request_rec.publish_flag := p_service_request_rec.publish_flag;
434 l_service_request_rec.summary := p_service_request_rec.summary;
435 l_service_request_rec.caller_type := p_service_request_rec.caller_type;
436 l_service_request_rec.customer_id := p_service_request_rec.customer_id;
437 l_service_request_rec.customer_number := p_service_request_rec.customer_number;
438 l_service_request_rec.employee_id := p_service_request_rec.employee_id;
439 l_service_request_rec.employee_number := p_service_request_rec.employee_number;
440 l_service_request_rec.verify_cp_flag := p_service_request_rec.verify_cp_flag;
441 l_service_request_rec.customer_product_id := p_service_request_rec.customer_product_id;
442 l_service_request_rec.platform_id := p_service_request_rec.platform_id;
443 l_service_request_rec.platform_version := p_service_request_rec.platform_version;
444 l_service_request_rec.db_version := p_service_request_rec.db_version;
445 l_service_request_rec.platform_version_id := p_service_request_rec.platform_version_id;
446 l_service_request_rec.cp_component_id := p_service_request_rec.cp_component_id;
447 l_service_request_rec.cp_component_version_id := p_service_request_rec.cp_component_version_id;
448 l_service_request_rec.cp_subcomponent_id := p_service_request_rec.cp_subcomponent_id;
449 l_service_request_rec.cp_subcomponent_version_id := p_service_request_rec.cp_subcomponent_version_id;
450 l_service_request_rec.language_id := p_service_request_rec.language_id;
451 l_service_request_rec.language := p_service_request_rec.language;
452 l_service_request_rec.cp_ref_number := p_service_request_rec.cp_ref_number;
453 l_service_request_rec.inventory_item_id := p_service_request_rec.inventory_item_id ;
454 l_service_request_rec.inventory_item_conc_segs := p_service_request_rec.inventory_item_conc_segs;
455 l_service_request_rec.inventory_item_segment1 := p_service_request_rec.inventory_item_segment1;
456 l_service_request_rec.inventory_item_segment2 := p_service_request_rec.inventory_item_segment2;
457 l_service_request_rec.inventory_item_segment3 := p_service_request_rec.inventory_item_segment3;
458 l_service_request_rec.inventory_item_segment4 := p_service_request_rec.inventory_item_segment4;
459 l_service_request_rec.inventory_item_segment5 := p_service_request_rec.inventory_item_segment5;
460 l_service_request_rec.inventory_item_segment6 := p_service_request_rec.inventory_item_segment6;
461 l_service_request_rec.inventory_item_segment7 := p_service_request_rec.inventory_item_segment7;
462 l_service_request_rec.inventory_item_segment8 := p_service_request_rec.inventory_item_segment8;
463 l_service_request_rec.inventory_item_segment9 := p_service_request_rec.inventory_item_segment9;
464 l_service_request_rec.inventory_item_segment10 := p_service_request_rec.inventory_item_segment10;
465 l_service_request_rec.inventory_item_segment11 := p_service_request_rec.inventory_item_segment11;
466 l_service_request_rec.inventory_item_segment12 := p_service_request_rec.inventory_item_segment12;
467 l_service_request_rec.inventory_item_segment13 := p_service_request_rec.inventory_item_segment13;
468 l_service_request_rec.inventory_item_segment14 := p_service_request_rec.inventory_item_segment14;
469 l_service_request_rec.inventory_item_segment15 := p_service_request_rec.inventory_item_segment15;
470 l_service_request_rec.inventory_item_segment16 := p_service_request_rec.inventory_item_segment16;
471 l_service_request_rec.inventory_item_segment17 := p_service_request_rec.inventory_item_segment17;
472 l_service_request_rec.inventory_item_segment18 := p_service_request_rec.inventory_item_segment18;
473 l_service_request_rec.inventory_item_segment19 := p_service_request_rec.inventory_item_segment19;
474 l_service_request_rec.inventory_item_segment20 := p_service_request_rec.inventory_item_segment20;
475 l_service_request_rec.inventory_item_vals_or_ids := p_service_request_rec.inventory_item_vals_or_ids;
476 l_service_request_rec.inventory_org_id := p_service_request_rec.inventory_org_id;
477 l_service_request_rec.current_serial_number := p_service_request_rec.current_serial_number;
478 l_service_request_rec.original_order_number := p_service_request_rec.original_order_number;
479 l_service_request_rec.purchase_order_num := p_service_request_rec.purchase_order_num;
480 l_service_request_rec.problem_code := p_service_request_rec.problem_code;
481 l_service_request_rec.exp_resolution_date := p_service_request_rec.exp_resolution_date;
482 l_service_request_rec.install_site_use_id := p_service_request_rec.install_site_use_id;
483 l_service_request_rec.request_attribute_1 := p_service_request_rec.request_attribute_1;
484 l_service_request_rec.request_attribute_2 := p_service_request_rec.request_attribute_2;
485 l_service_request_rec.request_attribute_3 := p_service_request_rec.request_attribute_3;
486 l_service_request_rec.request_attribute_4 := p_service_request_rec.request_attribute_4;
487 l_service_request_rec.request_attribute_5 := p_service_request_rec.request_attribute_5;
488 l_service_request_rec.request_attribute_6 := p_service_request_rec.request_attribute_6;
489 l_service_request_rec.request_attribute_7 := p_service_request_rec.request_attribute_7;
490 l_service_request_rec.request_attribute_8 := p_service_request_rec.request_attribute_8;
491 l_service_request_rec.request_attribute_9 := p_service_request_rec.request_attribute_9;
492 l_service_request_rec.request_attribute_10 := p_service_request_rec.request_attribute_10;
493 l_service_request_rec.request_attribute_11 := p_service_request_rec.request_attribute_11;
494 l_service_request_rec.request_attribute_12 := p_service_request_rec.request_attribute_12;
495 l_service_request_rec.request_attribute_13 := p_service_request_rec.request_attribute_13;
496 l_service_request_rec.request_attribute_14 := p_service_request_rec.request_attribute_14;
497 l_service_request_rec.request_attribute_15 := p_service_request_rec.request_attribute_15;
498 l_service_request_rec.request_context := p_service_request_rec.request_context ;
499 l_service_request_rec.bill_to_site_use_id := p_service_request_rec.bill_to_site_use_id;
500 l_service_request_rec.bill_to_contact_id := p_service_request_rec.bill_to_contact_id;
501 l_service_request_rec.ship_to_site_use_id := p_service_request_rec.ship_to_site_use_id;
502 l_service_request_rec.ship_to_contact_id := p_service_request_rec.ship_to_contact_id ;
503 l_service_request_rec.resolution_code := p_service_request_rec.resolution_code;
504 l_service_request_rec.act_resolution_date := p_service_request_rec.act_resolution_date;
505 l_service_request_rec.public_comment_flag := p_service_request_rec.public_comment_flag ;
506 l_service_request_rec.parent_interaction_id := p_service_request_rec.parent_interaction_id;
507 l_service_request_rec.contract_service_id := p_service_request_rec.contract_service_id;
508 l_service_request_rec.contract_service_number := p_service_request_rec.contract_service_number;
509 l_service_request_rec.contract_id := p_service_request_rec.contract_id;
510 l_service_request_rec.project_number := p_service_request_rec.project_number;
511 l_service_request_rec.qa_collection_plan_id := p_service_request_rec.qa_collection_plan_id;
512 l_service_request_rec.account_id := p_service_request_rec.account_id;
513 l_service_request_rec.resource_type := p_service_request_rec.resource_type;
514 l_service_request_rec.resource_subtype_id := p_service_request_rec.resource_subtype_id;
515 l_service_request_rec.cust_po_number := p_service_request_rec.cust_po_number;
516 l_service_request_rec.cust_ticket_number := p_service_request_rec.cust_ticket_number;
517 l_service_request_rec.sr_creation_channel := p_service_request_rec.sr_creation_channel;
518 l_service_request_rec.obligation_date := p_service_request_rec.obligation_date ;
519 l_service_request_rec.time_zone_id := p_service_request_rec.time_zone_id ;
520 l_service_request_rec.time_difference := p_service_request_rec.time_difference ;
521 l_service_request_rec.site_id := p_service_request_rec.site_id;
522 l_service_request_rec.customer_site_id := p_service_request_rec.customer_site_id;
523 l_service_request_rec.territory_id := p_service_request_rec.territory_id;
524 l_service_request_rec.initialize_flag := p_service_request_rec.initialize_flag;
525 l_service_request_rec.cp_revision_id := p_service_request_rec.cp_revision_id;
526 l_service_request_rec.inv_item_revision := p_service_request_rec.inv_item_revision;
527 l_service_request_rec.inv_component_id := p_service_request_rec.inv_component_id;
528 l_service_request_rec.inv_component_version := p_service_request_rec.inv_component_version;
529 l_service_request_rec.inv_subcomponent_id := p_service_request_rec.inv_subcomponent_id;
530 l_service_request_rec.inv_subcomponent_version := p_service_request_rec.inv_subcomponent_version;
531 ------jngeorge---------------07/12/01
532 l_service_request_rec.tier := p_service_request_rec.tier;
533 l_service_request_rec.tier_version := p_service_request_rec.tier_version;
534 l_service_request_rec.operating_system := p_service_request_rec.operating_system;
535 l_service_request_rec.operating_system_version := p_service_request_rec.operating_system_version;
536 l_service_request_rec.database := p_service_request_rec.database;
537 l_service_request_rec.cust_pref_lang_id := p_service_request_rec.cust_pref_lang_id;
538 l_service_request_rec.category_id := p_service_request_rec.category_id;
539 l_service_request_rec.group_type := p_service_request_rec.group_type;
540 l_service_request_rec.group_territory_id := p_service_request_rec.group_territory_id;
541 l_service_request_rec.inv_platform_org_id := p_service_request_rec.inv_platform_org_id;
542 l_service_request_rec.component_version := p_service_request_rec.component_version;
543 l_service_request_rec.subcomponent_version := p_service_request_rec.subcomponent_version;
544 l_service_request_rec.product_revision := p_service_request_rec.product_revision;
545 l_service_request_rec.comm_pref_code := p_service_request_rec.comm_pref_code;
546 ---- Added for Post 11.5.6 Enhancement
547 ---- Changed by Liang for 115.10 for Client to update Last_update_channel
548 l_service_request_rec.cust_pref_lang_code := P_service_request_rec.cust_pref_lang_code;
549 l_service_request_rec.last_update_channel := p_service_request_rec.last_update_channel;
550
551 --Added by LiangXia on Dec 19,2002 to for CS changes in 115.9
552 l_service_request_rec.last_update_program_code := 'EMAILCENTER';
553 -- end if;
554
555
556 l_notes.delete;
557 IF p_notes.count>0 THEN
558 FOR i in p_notes.FIRST..p_notes.LAST LOOP
559 l_notes(l_index).NOTE := p_notes(i).NOTE;
560 l_notes(l_index).NOTE_DETAIL := p_notes(i).NOTE_DETAIL;
561 l_notes(l_index).NOTE_TYPE := p_notes(i).NOTE_TYPE;
562 l_notes(l_index).NOTE_CONTEXT_TYPE_01 := p_notes(i).NOTE_CONTEXT_TYPE_01;
563 l_notes(l_index).NOTE_CONTEXT_TYPE_ID_01 := p_notes(i).NOTE_CONTEXT_TYPE_ID_01;
564 l_notes(l_index).NOTE_CONTEXT_TYPE_02 := p_notes(i).NOTE_CONTEXT_TYPE_02;
565 l_notes(l_index).NOTE_CONTEXT_TYPE_ID_02 := p_notes(i).NOTE_CONTEXT_TYPE_ID_02;
566 l_notes(l_index).NOTE_CONTEXT_TYPE_03 := p_notes(i).NOTE_CONTEXT_TYPE_03;
567 l_notes(l_index).NOTE_CONTEXT_TYPE_ID_03 := p_notes(i).NOTE_CONTEXT_TYPE_ID_03;
568 l_index:=l_index+1;
569 END LOOP;
570 END IF;
571
572 l_contacts.delete;
573 l_index := 1;
574 IF p_contacts.count>0 THEN
575 FOR x in p_contacts.FIRST..p_contacts.LAST LOOP
576 l_contacts(l_index).SR_CONTACT_POINT_ID := p_contacts(x).SR_CONTACT_POINT_ID;
577 l_contacts(l_index).PARTY_ID := p_contacts(x).PARTY_ID;
578 l_contacts(l_index).CONTACT_POINT_ID := p_contacts(x).CONTACT_POINT_ID;
579 l_contacts(l_index).CONTACT_POINT_TYPE := p_contacts(x).CONTACT_POINT_TYPE;
580 l_contacts(l_index).PRIMARY_FLAG := p_contacts(x).PRIMARY_FLAG;
581 l_contacts(l_index).CONTACT_TYPE := p_contacts(x).CONTACT_TYPE;
582 l_index:=l_index+1;
583 END LOOP;
584 END IF;
585
586 CS_ServiceRequest_PUB.Update_ServiceRequest (
587 p_api_version => l_cs_version_number,
588 p_init_msg_list => p_init_msg_list,
589 p_commit => p_commit,
590 x_return_status => l_return_status,
591 x_msg_count => l_msg_count,
592 x_msg_data => l_msg_data,
593 p_request_id => p_request_id,
594 p_request_number => p_request_number,
595 p_audit_comments => p_audit_comments,
596 p_object_version_number => p_object_version_number,
597 p_resp_appl_id => p_resp_appl_id,
598 p_resp_id => p_resp_id,
599 p_last_updated_by => p_last_updated_by,
600 p_last_update_login => p_last_update_login,
601 p_last_update_date => p_last_update_date,
602 p_service_request_rec => l_service_request_rec,
603 p_notes => l_notes,
604 p_contacts => l_contacts,
605 p_called_by_workflow => p_called_by_workflow,
606 p_workflow_process_id => p_workflow_process_id,
607 x_workflow_process_id => x_workflow_process_id,
608 x_interaction_id => x_interaction_id );
609
610
611 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
612 raise IEM_SERVICEREQUEST_NOT_UPDATED;
613 end if;
614
615 x_return_status := l_return_status ;
616 --Standard check of p_commit
617 IF FND_API.to_Boolean(p_commit) THEN
618 COMMIT WORK;
619 END IF;
620
621 EXCEPTION
622 WHEN IEM_SERVICEREQUEST_NOT_UPDATED THEN
623 ROLLBACK TO Update_ServiceRequest_Wrap;
624 x_return_status := l_return_status ;
625 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
626
627 WHEN FND_API.G_EXC_ERROR THEN
628 ROLLBACK TO Update_ServiceRequest_Wrap;
629 x_return_status := FND_API.G_RET_STS_ERROR ;
630 FND_MSG_PUB.Count_And_Get
631 ( p_count => x_msg_count,p_data => x_msg_data);
632
633
634 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
635 ROLLBACK TO Update_ServiceRequest_Wrap;
636 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
637 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
638
639 WHEN OTHERS THEN
640 ROLLBACK TO Update_ServiceRequest_Wrap;
641 x_return_status := FND_API.G_RET_STS_ERROR;
642
643 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
644 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , l_api_name);
645 END IF;
646
647 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
648 END;
649
650
651 PROCEDURE initialize_rec(
652 p_sr_record IN OUT NOCOPY service_request_rec_type
653 ) AS
654 BEGIN
655 p_sr_record.request_date := FND_API.G_MISS_DATE;
656 p_sr_record.type_id := FND_API.G_MISS_NUM;
657 p_sr_record.type_name := FND_API.G_MISS_CHAR;
658 p_sr_record.status_id := FND_API.G_MISS_NUM;
659 p_sr_record.status_name := FND_API.G_MISS_CHAR;
660 p_sr_record.severity_id := FND_API.G_MISS_NUM;
661 p_sr_record.severity_name := FND_API.G_MISS_CHAR;
662 p_sr_record.urgency_id := FND_API.G_MISS_NUM;
663 p_sr_record.urgency_name := FND_API.G_MISS_CHAR;
664 p_sr_record.closed_date := FND_API.G_MISS_DATE;
665 p_sr_record.owner_id := FND_API.G_MISS_NUM;
666 p_sr_record.owner_group_id := FND_API.G_MISS_NUM;
667 p_sr_record.publish_flag := FND_API.G_MISS_CHAR;
668 p_sr_record.summary := FND_API.G_MISS_CHAR;
669 p_sr_record.caller_type := FND_API.G_MISS_CHAR;
670 p_sr_record.customer_id := FND_API.G_MISS_NUM;
671 p_sr_record.customer_number := FND_API.G_MISS_CHAR;
672 p_sr_record.employee_id := FND_API.G_MISS_NUM;
673 p_sr_record.employee_number := FND_API.G_MISS_CHAR;
674 p_sr_record.verify_cp_flag := FND_API.G_MISS_CHAR;
675 p_sr_record.customer_product_id := FND_API.G_MISS_NUM;
676 p_sr_record.platform_id := FND_API.G_MISS_NUM;
677 p_sr_record.platform_version := FND_API.G_MISS_CHAR;
678 p_sr_record.db_version := FND_API.G_MISS_CHAR;
679 p_sr_record.platform_version_id := FND_API.G_MISS_NUM;
680 p_sr_record.cp_component_id := FND_API.G_MISS_NUM;
681 p_sr_record.cp_component_version_id := FND_API.G_MISS_NUM;
682 p_sr_record.cp_subcomponent_id := FND_API.G_MISS_NUM;
683 p_sr_record.cp_subcomponent_version_id := FND_API.G_MISS_NUM;
684 p_sr_record.language_id := FND_API.G_MISS_NUM;
685 p_sr_record.language := FND_API.G_MISS_CHAR;
686 p_sr_record.cp_ref_number := FND_API.G_MISS_NUM;
687 p_sr_record.inventory_item_id := FND_API.G_MISS_NUM;
688 p_sr_record.inventory_item_conc_segs := FND_API.G_MISS_CHAR;
689 p_sr_record.inventory_item_segment1 := FND_API.G_MISS_CHAR;
690 p_sr_record.inventory_item_segment2 := FND_API.G_MISS_CHAR;
691 p_sr_record.inventory_item_segment3 := FND_API.G_MISS_CHAR;
692 p_sr_record.inventory_item_segment4 := FND_API.G_MISS_CHAR;
693 p_sr_record.inventory_item_segment5 := FND_API.G_MISS_CHAR;
694 p_sr_record.inventory_item_segment6 := FND_API.G_MISS_CHAR;
695 p_sr_record.inventory_item_segment7 := FND_API.G_MISS_CHAR;
696 p_sr_record.inventory_item_segment8 := FND_API.G_MISS_CHAR;
697 p_sr_record.inventory_item_segment9 := FND_API.G_MISS_CHAR;
698 p_sr_record.inventory_item_segment10 := FND_API.G_MISS_CHAR;
699 p_sr_record.inventory_item_segment11 := FND_API.G_MISS_CHAR;
700 p_sr_record.inventory_item_segment12 := FND_API.G_MISS_CHAR;
701 p_sr_record.inventory_item_segment13 := FND_API.G_MISS_CHAR;
702 p_sr_record.inventory_item_segment14 := FND_API.G_MISS_CHAR;
703 p_sr_record.inventory_item_segment15 := FND_API.G_MISS_CHAR;
704 p_sr_record.inventory_item_segment16 := FND_API.G_MISS_CHAR;
705 p_sr_record.inventory_item_segment17 := FND_API.G_MISS_CHAR;
706 p_sr_record.inventory_item_segment18 := FND_API.G_MISS_CHAR;
707 p_sr_record.inventory_item_segment19 := FND_API.G_MISS_CHAR;
708 p_sr_record.inventory_item_segment20 := FND_API.G_MISS_CHAR;
709 p_sr_record.inventory_item_vals_or_ids := 'V';
710 p_sr_record.inventory_org_id := FND_API.G_MISS_NUM;
711 p_sr_record.current_serial_number := FND_API.G_MISS_CHAR;
712 p_sr_record.original_order_number := FND_API.G_MISS_NUM;
713 p_sr_record.purchase_order_num := FND_API.G_MISS_CHAR;
714 p_sr_record.problem_code := FND_API.G_MISS_CHAR;
715 p_sr_record.exp_resolution_date := FND_API.G_MISS_DATE;
716 p_sr_record.install_site_use_id := FND_API.G_MISS_NUM;
717 p_sr_record.request_attribute_1 := FND_API.G_MISS_CHAR;
718 p_sr_record.request_attribute_2 := FND_API.G_MISS_CHAR;
719 p_sr_record.request_attribute_3 := FND_API.G_MISS_CHAR;
720 p_sr_record.request_attribute_4 := FND_API.G_MISS_CHAR;
721 p_sr_record.request_attribute_5 := FND_API.G_MISS_CHAR;
722 p_sr_record.request_attribute_6 := FND_API.G_MISS_CHAR;
723 p_sr_record.request_attribute_7 := FND_API.G_MISS_CHAR;
724 p_sr_record.request_attribute_8 := FND_API.G_MISS_CHAR;
725 p_sr_record.request_attribute_9 := FND_API.G_MISS_CHAR;
726 p_sr_record.request_attribute_10 := FND_API.G_MISS_CHAR;
727 p_sr_record.request_attribute_11 := FND_API.G_MISS_CHAR;
728 p_sr_record.request_attribute_12 := FND_API.G_MISS_CHAR;
729 p_sr_record.request_attribute_13 := FND_API.G_MISS_CHAR;
730 p_sr_record.request_attribute_14 := FND_API.G_MISS_CHAR;
731 p_sr_record.request_attribute_15 := FND_API.G_MISS_CHAR;
732 p_sr_record.request_context := FND_API.G_MISS_CHAR;
733 p_sr_record.bill_to_site_use_id := FND_API.G_MISS_NUM;
734 p_sr_record.bill_to_contact_id := FND_API.G_MISS_NUM;
735 p_sr_record.ship_to_site_use_id := FND_API.G_MISS_NUM;
736 p_sr_record.ship_to_contact_id := FND_API.G_MISS_NUM;
737 p_sr_record.resolution_code := FND_API.G_MISS_CHAR;
738 p_sr_record.act_resolution_date := FND_API.G_MISS_DATE;
739 p_sr_record.public_comment_flag := FND_API.G_MISS_CHAR;
740 p_sr_record.parent_interaction_id := FND_API.G_MISS_NUM;
741 p_sr_record.contract_service_id := FND_API.G_MISS_NUM;
742 p_sr_record.contract_service_number := FND_API.G_MISS_CHAR;
743 p_sr_record.qa_collection_plan_id := FND_API.G_MISS_NUM;
744 --p_sr_record.account_id := FND_API.G_MISS_NUM;
745 p_sr_record.resource_type := FND_API.G_MISS_CHAR;
746 p_sr_record.resource_subtype_id := FND_API.G_MISS_NUM;
747 p_sr_record.cust_po_number := FND_API.G_MISS_CHAR;
748 p_sr_record.cust_ticket_number := FND_API.G_MISS_CHAR;
749 p_sr_record.sr_creation_channel := FND_API.G_MISS_CHAR;
750 p_sr_record.obligation_date := FND_API.G_MISS_DATE;
751 p_sr_record.time_zone_id := FND_API.G_MISS_NUM;
752 p_sr_record.time_difference := FND_API.G_MISS_NUM;
753 p_sr_record.site_id := FND_API.G_MISS_NUM;
754 p_sr_record.customer_site_id := FND_API.G_MISS_NUM;
755 p_sr_record.territory_id := FND_API.G_MISS_NUM;
756 p_sr_record.initialize_flag := G_INITIALIZED;
757 p_sr_record.cp_revision_id := FND_API.G_MISS_NUM;
758 p_sr_record.inv_item_revision := FND_API.G_MISS_CHAR;
759 p_sr_record.inv_component_id := FND_API.G_MISS_NUM;
760 p_sr_record.inv_component_version := FND_API.G_MISS_CHAR;
761 p_sr_record.inv_subcomponent_id := FND_API.G_MISS_NUM;
762 p_sr_record.inv_subcomponent_version := FND_API.G_MISS_CHAR;
763 -- Fix for Bug# 2155981
764 p_sr_record.project_number := FND_API.G_MISS_CHAR;
765 -----jngeorge-----enhancements-----11.5.6-----07/12/01
766 p_sr_record.tier := FND_API.G_MISS_CHAR;
767 p_sr_record.tier_version := FND_API.G_MISS_CHAR;
768 p_sr_record.operating_system := FND_API.G_MISS_CHAR;
769 p_sr_record.operating_system_version := FND_API.G_MISS_CHAR;
770 p_sr_record.database := FND_API.G_MISS_CHAR;
771 p_sr_record.cust_pref_lang_id := FND_API.G_MISS_NUM;
772 p_sr_record.category_id := FND_API.G_MISS_NUM;
773 p_sr_record.group_type := FND_API.G_MISS_CHAR;
774 p_sr_record.group_territory_id := FND_API.G_MISS_NUM;
775 p_sr_record.inv_platform_org_id := FND_API.G_MISS_NUM;
776 p_sr_record.product_revision := FND_API.G_MISS_CHAR;
777 p_sr_record.component_version := FND_API.G_MISS_CHAR;
778 p_sr_record.subcomponent_version := FND_API.G_MISS_CHAR;
779 p_sr_record.comm_pref_code := FND_API.G_MISS_CHAR;
780 --- Added for Post 11.5.6 Enhancements
781 p_sr_record.cust_pref_lang_code := FND_API.G_MISS_CHAR;
782 p_sr_record.last_update_channel := 'EMAIL'; --FND_API.G_MISS_CHAR;
783 -------jngeorge----07/12/01
784
785 END initialize_rec;
786
787 PROCEDURE Update_Status_Wrap
788 ( p_api_version IN NUMBER,
789 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
790 p_commit IN VARCHAR2 := FND_API.G_FALSE,
791 x_return_status OUT NOCOPY VARCHAR2,
792 x_msg_count OUT NOCOPY NUMBER,
793 x_msg_data OUT NOCOPY VARCHAR2,
794 p_resp_appl_id IN NUMBER := NULL,
795 p_resp_id IN NUMBER := NULL,
796 p_user_id IN NUMBER := NULL,
797 p_login_id IN NUMBER := FND_API.G_MISS_NUM,
798 p_request_id IN NUMBER := NULL,
799 p_request_number IN VARCHAR2 := NULL,
800 p_object_version_number IN NUMBER,
801 p_status_id IN NUMBER := NULL,
802 p_status IN VARCHAR2 := NULL,
803 p_closed_date IN DATE := FND_API.G_MISS_DATE,
804 p_audit_comments IN VARCHAR2 := NULL,
805 p_called_by_workflow IN VARCHAR2 := FND_API.G_FALSE,
806 p_workflow_process_id IN NUMBER := NULL,
807 p_comments IN VARCHAR2 := NULL,
808 p_public_comment_flag IN VARCHAR2 := FND_API.G_FALSE,
809 x_interaction_id OUT NOCOPY NUMBER
810 )
811 IS
812
813 l_api_name VARCHAR2(255):='Update_Status_Wrap';
814 l_api_version_number NUMBER:= 2.0;
815 l_cs_version_number NUMBER:= 2.0;
816
817 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
818 l_msg_count NUMBER := 0;
819 l_msg_data VARCHAR2(2000);
820 l_index NUMBER := 1;
821
822 l_resp_appl_id NUMBER := NULL;
823 l_resp_id NUMBER := NULL;
824 l_user_id NUMBER := NULL;
825 l_login_id NUMBER := FND_API.G_MISS_NUM;
826 l_request_id NUMBER := NULL;
827 l_request_number VARCHAR2(256) := null;
828 l_object_version_number NUMBER;
829 l_status_id NUMBER := NULL;
830 l_status VARCHAR2(30) := NULL;
831 l_closed_date DATE := FND_API.G_MISS_DATE;
832 l_audit_comments VARCHAR2(2000) := NULL;
833 l_called_by_workflow VARCHAR2(1):= FND_API.G_FALSE;
834 l_workflow_process_id NUMBER := NULL;
835 l_comments VARCHAR2(2000) := NULL;
836 l_public_comment_flag VARCHAR2(1):= FND_API.G_FALSE;
837 l_interaction_id NUMBER;
838
839 IEM_UPDATE_SR_STATUS_FAIL EXCEPTION;
840
841 begin
842 -- Standard Start of API savepoint
843 SAVEPOINT Update_Status_Wrap;
844
845 -- Standard call to check for call compatibility.
846 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
847 p_api_version,
848 l_api_name,
849 G_PKG_NAME)
850 THEN
851
852 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
853 END IF;
854
855
856 -- Initialize message list if p_init_msg_list is set to TRUE.
857 IF FND_API.to_Boolean( p_init_msg_list )
858 THEN
859 FND_MSG_PUB.initialize;
860 END IF;
861
862 l_resp_appl_id := p_resp_appl_id;
863 l_resp_id := p_resp_id;
864 l_user_id := p_user_id;
865 l_login_id := p_login_id;
866 l_request_id := p_request_id;
867 l_request_number := p_request_number;
868 l_object_version_number := p_object_version_number;
869 l_status_id := p_status_id;
870 l_status := p_status;
871 l_closed_date := p_closed_date;
872 l_audit_comments := p_audit_comments;
873 l_called_by_workflow := p_called_by_workflow;
874 l_workflow_process_id := p_workflow_process_id;
875 l_comments := p_comments;
876 l_public_comment_flag := p_public_comment_flag;
877
878 fnd_global.apps_initialize( user_id=>l_user_id,
879 resp_id=>l_resp_id,
880 resp_appl_id=>l_resp_appl_id);
881
882 CS_SERVICEREQUEST_PUB.Update_Status
883 ( p_api_version => l_cs_version_number,
884 p_init_msg_list => p_init_msg_list,
885 p_commit => p_commit,
886 x_return_status => l_return_status,
887 x_msg_count => l_msg_count,
888 x_msg_data => l_msg_data,
889 p_resp_appl_id => l_resp_appl_id,
890 p_resp_id => l_resp_id,
891 p_user_id => l_user_id,
892 p_login_id => l_login_id,
893 p_request_id => l_request_id,
894 p_request_number => l_request_number,
895 p_object_version_number => l_object_version_number,
896 p_status_id => l_status_id,
897 p_status => l_status,
898 p_closed_date => l_closed_date,
899 p_audit_comments => l_audit_comments,
900 p_called_by_workflow => l_called_by_workflow,
901 p_workflow_process_id => l_workflow_process_id,
902 p_comments => l_comments,
903 p_public_comment_flag => l_public_comment_flag,
904 x_interaction_id => l_interaction_id
905 );
906
907 l_interaction_id := x_interaction_id;
908
909 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
910 raise IEM_UPDATE_SR_STATUS_FAIL;
911 end if;
912
913 x_return_status := l_return_status ;
914
915 --Standard check of p_commit
916 IF FND_API.to_Boolean(p_commit) THEN
917 COMMIT WORK;
918 END IF;
919
920 EXCEPTION
921 WHEN IEM_UPDATE_SR_STATUS_FAIL THEN
922 ROLLBACK TO Update_Status_Wrap;
923 x_return_status := l_return_status ;
924 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
925
926 WHEN FND_API.G_EXC_ERROR THEN
927 ROLLBACK TO Update_Status_Wrap;
928 x_return_status := FND_API.G_RET_STS_ERROR ;
929 FND_MSG_PUB.Count_And_Get
930 ( p_count => x_msg_count,p_data => x_msg_data);
931
932
933 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
934 ROLLBACK TO Update_Status_Wrap;
935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
936 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
937
938 WHEN OTHERS THEN
939 ROLLBACK TO Update_Status_Wrap;
940 x_return_status := FND_API.G_RET_STS_ERROR;
941
942 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
943 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , l_api_name);
944 END IF;
945
946 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
947 END;
948
949 -------------------------------------------------------------------------------
950 -- Procedure : IEM_CREATE_SR
951 -- Description : auto create service request
952 -------------------------------------------------------------------------------
953
954 PROCEDURE IEM_CREATE_SR
955 ( p_api_version IN NUMBER,
956 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
957 p_commit IN VARCHAR2 := FND_API.G_FALSE,
958 p_message_id IN NUMBER,
959 p_note IN VARCHAR2,
960 p_party_id IN NUMBER,
961 p_sr_type_id IN NUMBER,
962 p_subject IN VARCHAR2,
963 p_employee_flag IN VARCHAR2,
964 p_note_type IN VARCHAR2,
965 p_contact_id IN NUMBER := NULL,
966 p_contact_point_id IN NUMBER := NULL,
967 x_return_status OUT NOCOPY VARCHAR2,
968 x_msg_count OUT NOCOPY NUMBER,
969 x_msg_data OUT NOCOPY VARCHAR2,
970 x_request_id OUT NOCOPY NUMBER
971 )
972
973 IS
974 l_api_name VARCHAR2(255):='IEM_CREATE_SR';
975 l_api_version_number NUMBER:=1.0;
976 l_cs_version_number NUMBER:=4.0;
977
978 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
979 l_msg_count NUMBER := 0;
980 l_msg_data VARCHAR2(2000);
981 l_index NUMBER := 1;
982
983 l_service_request_rec CS_ServiceRequest_PUB.service_request_rec_type;
984 p_notes CS_ServiceRequest_PUB.notes_table;
985 p_contacts CS_ServiceRequest_PUB.contacts_table;
986 l_sr_create_out_rec CS_ServiceRequest_PUB.sr_create_out_rec_type;
987 p_keyVals_tbl IEM_ROUTE_PUB.keyVals_tbl_type;
988
989 l_notes CS_ServiceRequest_PUB.notes_table;
990 l_contacts CS_ServiceRequest_PUB.contacts_table;
991
992 l_summary_prefix VARCHAR2(80);
993 l_summary VARCHAR2(300);
994 l_party_type VARCHAR2(80);
995 l_contact_type VARCHAR2(80);
996
997 --abhgauta - Debug for Bug 6373644
998 l_auto_assign VARCHAR2(10);
999
1000 IEM_SR_NOT_CREATE EXCEPTION;
1001
1002 CURSOR caller_type
1003 IS
1004 SELECT party_type
1005 FROM hz_parties a
1006 WHERE a.party_id = p_party_id
1007 AND a.status = 'A'
1008 AND a.party_type IN ('ORGANIZATION','PERSON');
1009 l_coverage_template_id NUMBER;
1010
1011 BEGIN
1012
1013 -- Standard Start of API savepoint
1014 SAVEPOINT IEM_CREATE_SR;
1015
1016 -- Standard call to check for call compatibility.
1017 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1018 p_api_version,
1019 l_api_name,
1020 G_PKG_NAME)
1021 THEN
1022 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1023 END IF;
1024
1025 -- Initialize message list if p_init_msg_list is set to TRUE.
1026 IF FND_API.to_Boolean( p_init_msg_list )
1027 THEN
1028 FND_MSG_PUB.initialize;
1029 END IF;
1030
1031 FND_PROFILE.Get('IEM_SR_SUM_PREFIX', l_summary_prefix);
1032
1033 l_summary := l_summary_prefix||' : '|| p_subject ;
1034
1035
1036 CS_ServiceRequest_PUB.initialize_rec(l_service_request_rec);
1037
1038
1039 l_service_request_rec.type_id := p_sr_type_id;
1040 l_service_request_rec.summary := l_summary;
1041 l_service_request_rec.customer_id := p_party_id;
1042 l_service_request_rec.sr_creation_channel := 'EMAIL';
1043 l_service_request_rec.creation_program_code := 'EMAILCENTER';
1044
1045 OPEN caller_type;
1046 FETCH caller_type INTO l_party_type;
1047
1048 IF (caller_type%NOTFOUND) THEN
1049 x_return_status := FND_API.G_RET_STS_ERROR;
1050 CLOSE caller_type;
1051 END IF;
1052
1053 CLOSE caller_type;
1054
1055 l_service_request_rec.caller_type := l_party_type; -- 'ORGANIZATION'/'PERSON';
1056
1057 l_index := 1;
1058
1059 l_notes(l_index).NOTE := p_note ;
1060 l_notes(l_index).NOTE_TYPE := p_NOTE_TYPE;
1061
1062 IF (p_contact_id IS NOT NULL and p_employee_flag = 'Y' )
1063 THEN
1064 l_index := 1;
1065 l_contacts(l_index).PARTY_ID := p_contact_id;
1066 l_contacts(l_index).PRIMARY_FLAG := 'Y';
1067 l_contacts(l_index).CONTACT_POINT_TYPE := 'EMAIL';
1068 l_contacts(l_index).CONTACT_TYPE := 'EMPLOYEE';
1069 ELSE
1070 IF (p_contact_id IS NOT NULL and p_employee_flag = 'N')
1071 THEN
1072 select party_type into l_contact_type
1073 from hz_parties
1074 where party_id = p_contact_id;
1075
1076 l_index := 1;
1077 l_contacts(l_index).PARTY_ID := p_contact_id;
1078 l_contacts(l_index).contact_point_id := p_contact_point_id;
1079 l_contacts(l_index).PRIMARY_FLAG := 'Y';
1080 l_contacts(l_index).CONTACT_POINT_TYPE := 'EMAIL';
1081 l_contacts(l_index).CONTACT_TYPE := l_contact_type;
1082
1083 END IF;
1084 END IF;
1085
1086 -- abhgauta - Debug for Bug 6373644
1087 l_auto_assign := fnd_profile.value('CS_AUTO_ASSIGN_OWNER_HTML');
1088
1089 l_coverage_template_id:=fnd_profile.value_specific('CS_SR_DEFAULT_COVERAGE');
1090
1091
1092 CS_ServiceRequest_PUB.Create_ServiceRequest (
1093 p_api_version => l_cs_version_number,
1094 p_init_msg_list => FND_API.G_FALSE,
1095 p_commit => FND_API.G_FALSE,
1096 x_return_status => l_return_status,
1097 x_msg_count => l_msg_count,
1098 x_msg_data => l_msg_data,
1099 p_resp_appl_id => NULL,
1100 p_resp_id => NULL,
1101 p_user_id => NULL,
1102 p_login_id => NULL,
1103 p_org_id => NULL,
1104 p_request_id => NULL,
1105 p_request_number => NULL,
1106 p_service_request_rec => l_service_request_rec,
1107 p_notes => l_notes,
1108 p_contacts => l_contacts,
1109 p_auto_assign => l_auto_assign,
1110 p_auto_generate_tasks => 'N',
1111 x_sr_create_out_rec => l_sr_create_out_rec,
1112 p_default_contract_sla_ind => 'Y',
1113 p_default_coverage_template_id => l_coverage_template_id
1114 );
1115
1116 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1117 raise IEM_SR_NOT_CREATE;
1118 end if;
1119
1120 x_return_status := l_return_status ;
1121 x_request_id := l_sr_create_out_rec.request_id;
1122
1123 --Standard check of p_commit
1124 IF FND_API.to_Boolean(p_commit) THEN
1125 COMMIT WORK;
1126 END IF;
1127
1128 EXCEPTION
1129 WHEN IEM_SR_NOT_CREATE THEN
1130 ROLLBACK TO IEM_CREATE_SR;
1131 x_return_status := l_return_status ;
1132 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1133
1134 WHEN FND_API.G_EXC_ERROR THEN
1135 ROLLBACK TO IEM_CREATE_SR;
1136 x_return_status := FND_API.G_RET_STS_ERROR ;
1137 FND_MSG_PUB.Count_And_Get
1138 ( p_count => x_msg_count,p_data => x_msg_data);
1139
1140
1141 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1142 ROLLBACK TO IEM_CREATE_SR;
1143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1144 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
1145
1146 WHEN OTHERS THEN
1147 ROLLBACK TO IEM_CREATE_SR;
1148 x_return_status := FND_API.G_RET_STS_ERROR;
1149
1150 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1151 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , l_api_name);
1152 END IF;
1153
1154 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
1155 END;
1156
1157
1158 end IEM_SERVICEREQUEST_PVT;