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