[Home] [Help]
PACKAGE BODY: APPS.CUG_INCIDNT_ATTR_VALS_PVT
Source
1 PACKAGE BODY CUG_INCIDNT_ATTR_VALS_PVT as
2 /* $Header: CUGRINTB.pls 115.10 2004/01/27 00:27:17 aneemuch ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(100) := 'CUG_INCIDNT_ATTR_VALS_PVT';
5
6
7 procedure CREATE_RUNTIME_DATA (
8 p_api_version IN NUMBER,
9 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
10 p_commit IN VARCHAR := FND_API.G_FALSE,
11 p_sr_tbl IN OUT NOCOPY sr_tbl,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2,
14 x_return_status OUT NOCOPY VARCHAR2 )
15 is
16 l_api_name CONSTANT VARCHAR2(30) := 'CUG_INCIDNT_ATTR_VALS_PVT';
17 l_api_version CONSTANT NUMBER := 1.0;
18 l_sr_id NUMBER :=FND_API.G_MISS_NUM;
19 l_current_date DATE :=sysdate;
20 l_created_by NUMBER := fnd_global.user_id;
21 l_login NUMBER :=fnd_global.login_id;
22 l_rowid VARCHAR2(100);
23 l_date_format VARCHAR2(100);
24 l_num_rec number ;
25 l_incident_id number;
26 l_sr_attr_code varchar2(30);
27 l_sr_attr_value varchar2(1997);
28 l_sr_tbl CUG_INCIDNT_ATTR_VALS_PVT.sr_tbl;
29
30 --Begin - To fix bug # 2440305
31 l_incident_type_id NUMBER;
32 CURSOR l_CheckIfSRHdrInfoPresent_csr IS
33 select INCIDENT_TYPE_ID, SR_ATTRIBUTE_CODE from CUG_SR_TYPE_ATTR_MAPS_B
34 WHERE INCIDENT_TYPE_ID = l_incident_type_id;
35 l_CheckIfSRHdrInfoPresent_rec l_CheckIfSRHdrInfoPresent_csr%ROWTYPE;
36 --End - To fix bug # 2440305
37
38 begin
39 --Begin - To fix bug # 2440305
40
41 -- Fix for bug 2505327. Changed table index from 0 to 1. rmanabat 08/13/02.
42 l_incident_id := p_sr_tbl(1).incident_id;
43
44 SELECT incident_type_id into l_incident_type_id from cs_incidents_all_b where incident_id = l_incident_id;
45 OPEN l_CheckIfSRHdrInfoPresent_csr;
46 FETCH l_CheckIfSRHdrInfoPresent_csr into l_CheckIfSRHdrInfoPresent_rec;
47 IF(l_CheckIfSRHdrInfoPresent_csr%NOTFOUND) THEN
48 x_return_status := FND_API.G_RET_STS_SUCCESS;
49 ELSE
50 --End - To fix bug # 2440305
51 x_return_status := FND_API.G_RET_STS_SUCCESS;
52 -- Initialize message list if p_init_msg_list is set to TRUE.
53
54 l_num_rec := p_sr_tbl.count;
55 IF l_num_rec > 0
56 THEN
57 FOR i IN 1..l_num_rec
58 LOOP
59 p_sr_tbl(i).override_addr_valid_flag := 'N';
60 p_sr_tbl(i).OBJECT_VERSION_NUMBER := null;
61 p_sr_tbl(i).ATTRIBUTE1 := null;
62 p_sr_tbl(i).ATTRIBUTE2 := null;
63 p_sr_tbl(i).ATTRIBUTE3 := null;
64 p_sr_tbl(i).ATTRIBUTE4 := null;
65 p_sr_tbl(i).ATTRIBUTE5 := null;
66 p_sr_tbl(i).ATTRIBUTE6 := null;
67 p_sr_tbl(i).ATTRIBUTE7 := null;
68 p_sr_tbl(i).ATTRIBUTE8 := null;
69 p_sr_tbl(i).ATTRIBUTE9 := null;
70 p_sr_tbl(i).ATTRIBUTE10 := null;
71 p_sr_tbl(i).ATTRIBUTE11 := null;
72 p_sr_tbl(i).ATTRIBUTE12 := null;
73 p_sr_tbl(i).ATTRIBUTE13 := null;
74 p_sr_tbl(i).ATTRIBUTE14 := null;
75 p_sr_tbl(i).ATTRIBUTE15 := null;
76 p_sr_tbl(i).ATTRIBUTE_CATEGORY := null;
77
78
79 BEGIN
80 SELECT lookup_code
81 INTO l_sr_attr_code
82 FROM FND_LOOKUPS
83 WHERE lookup_type = 'CUG_SR_TYPE_ATTRIBUTES'
84 and description = p_sr_tbl(i).sr_question ;
85
86 EXCEPTION
87 When NO_DATA_FOUND then
88 FND_MESSAGE.SET_NAME('CUG','CUG_INVALID_SR_TYPE_QUESTION');
89 FND_MSG_PUB.ADD;
90 l_sr_attr_code := null;
91 When OTHERS then
92 FND_MESSAGE.SET_NAME('CUG','CUG_INVALID_SR_TYPE_QUESTION');
93 FND_MSG_PUB.ADD;
94 l_sr_attr_code := null;
95 END;
96
97
98 select CUG_INCIDNT_ATTR_VALS_B_S.NEXTVAL
99 into p_sr_tbl(i).incidnt_attr_val_id
100 from dual;
101
102
103 CUG_INCIDNT_ATTR_VALS_PKG.INSERT_ROW (
104 X_ROWID => l_rowid,
105 X_INCIDNT_ATTR_VAL_ID => p_sr_tbl(i).incidnt_attr_val_id ,
106 X_OBJECT_VERSION_NUMBER => p_sr_tbl(i).OBJECT_VERSION_NUMBER,
107 X_INCIDENT_ID => p_sr_tbl(i).incident_id ,
108 X_SR_ATTRIBUTE_CODE =>l_sr_attr_code,
109 X_OVERRIDE_ADDR_VALID_FLAG => p_sr_tbl(i).override_addr_valid_flag,
110 X_ATTRIBUTE1 =>p_sr_tbl(i).ATTRIBUTE1,
111 X_ATTRIBUTE2 => p_sr_tbl(i).ATTRIBUTE2,
112 X_ATTRIBUTE3=> p_sr_tbl(i).ATTRIBUTE3,
113 X_ATTRIBUTE4 => p_sr_tbl(i).ATTRIBUTE4,
114 X_ATTRIBUTE5 => p_sr_tbl(i).ATTRIBUTE5,
115 X_ATTRIBUTE6 =>p_sr_tbl(i).ATTRIBUTE6,
116 X_ATTRIBUTE7 => p_sr_tbl(i).ATTRIBUTE7,
117 X_ATTRIBUTE8 => p_sr_tbl(i).ATTRIBUTE8,
118 X_ATTRIBUTE9 => p_sr_tbl(i).ATTRIBUTE9,
119 X_ATTRIBUTE10 => p_sr_tbl(i).ATTRIBUTE10,
120 X_ATTRIBUTE11 => p_sr_tbl(i).ATTRIBUTE11,
121 X_ATTRIBUTE12 => p_sr_tbl(i).ATTRIBUTE12,
122 X_ATTRIBUTE13 => p_sr_tbl(i).ATTRIBUTE13,
123 X_ATTRIBUTE14 => p_sr_tbl(i).ATTRIBUTE14,
124 X_ATTRIBUTE15 => p_sr_tbl(i).ATTRIBUTE15,
125 X_ATTRIBUTE_CATEGORY=> p_sr_tbl(i).ATTRIBUTE_CATEGORY ,
126 X_SR_ATTRIBUTE_VALUE => p_sr_tbl(i).sr_answer ,
127 X_CREATION_DATE =>l_current_date,
128 X_CREATED_BY => l_created_by,
129 X_LAST_UPDATE_DATE => l_current_date,
130 X_LAST_UPDATED_BY =>l_created_by,
131 X_LAST_UPDATE_LOGIN => l_login
132 );
133 END LOOP;
134 end if;
135
136 -- Standard check of p_commit.
137 IF FND_API.To_Boolean( p_commit ) THEN
138 COMMIT WORK;
139 END IF;
140
141
142 --Begin - To fix bug # 2440305
143 END IF;
144 CLOSE l_CheckIfSRHdrInfoPresent_csr;
145 --End - To fix bug # 2440305
146
147 -- Standard call to get message count and if count is 1, get message info.
148 FND_MSG_PUB.Count_And_Get
149 (p_count => x_msg_count ,
150 p_data => x_msg_data
151 );
152
153 EXCEPTION
154
155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
156 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
157 FND_MSG_PUB.count_and_get( p_encoded=> FND_API.G_FALSE,
158 p_count => x_msg_count,
159 p_data => x_msg_data);
160 WHEN FND_API.G_EXC_ERROR THEN
161 x_return_status := FND_API.G_RET_STS_ERROR;
162 FND_MSG_PUB.count_and_get( p_encoded=> FND_API.G_FALSE,
163 p_count => x_msg_count,
164 p_data => x_msg_data);
165 WHEN OTHERS THEN
166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
167 FND_MSG_PUB.count_and_get( p_encoded=> FND_API.G_FALSE,
168 p_count => x_msg_count,
169 p_data => x_msg_data);
170 END CREATE_RUNTIME_DATA;
171
172 /* Changed for 9i compatibility bug 2543479 .. the parameters should
173 match the declarations in the Spec
174 p_init_msg_list IN VARCHAR2 DEFAULT NULL ,
175 p_commit IN VARCHAR2 DEFAULT NULL , */
176
177 PROCEDURE LAUNCH_WORKFLOW(
178 p_api_version IN NUMBER ,
179 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
180 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
181 x_return_status OUT NOCOPY VARCHAR2 ,
182 x_msg_count OUT NOCOPY NUMBER ,
183 x_msg_data OUT NOCOPY VARCHAR2 ,
184 p_incident_id IN NUMBER ,
185 p_source IN VARCHAR2 DEFAULT NULL )
186 IS
187
188 l_api_name CONSTANT VARCHAR2(30) := 'launch_workflow' ;
189 l_api_version CONSTANT NUMBER := 1.0 ;
190
191 l_itemkey VARCHAR2(240);
192 l_wf_process_id NUMBER;
193 l_initiator_role VARCHAR2(100);
194 l_initiator_display_name VARCHAR2(240);
195
196 CURSOR l_servereq_csr IS
197 SELECT CSI.incident_number,
198 CSI.workflow_process_id,
199 CSI.incident_type_id,
200 CST.name,CST.workflow,
201 CST.autolaunch_workflow_flag
202 FROM cs_incidents_all_b CSI, cs_incident_types_vl CST
203 WHERE CSI.incident_id = p_incident_id
204 AND CST.incident_type_id = CSI.incident_type_id
205 FOR UPDATE OF workflow_process_id NOWAIT;
206
207 l_servereq_csr_rec l_servereq_csr%ROWTYPE;
208
209 BEGIN
210 --Initialize message listif p_init_msg_list is set to TRUE.
211 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
212 FND_MSG_PUB.initialize;
213 END IF;
214
215 -- Initialize API return status to success
216 x_return_status := FND_API.G_RET_STS_SUCCESS;
217
218 -- API body
219
220 IF (p_incident_id IS NOT NULL ) THEN
221
222 OPEN l_servereq_csr;
223 FETCH l_servereq_csr INTO l_servereq_csr_rec;
224 -- EXIT WHEN l_servereq_csr%NOTFOUND;
225
226 -- Construct the unique item key
227 SELECT cs_wf_process_id_s.NEXTVAL INTO l_wf_process_id FROM DUAL;
228 l_itemkey := l_servereq_csr_rec.incident_number || '-' || to_char(l_wf_process_id);
229
230 -- Update the workflow process ID of the request
231 IF TO_NUMBER(FND_PROFILE.VALUE('USER_ID')) IS NOT NULL THEN
232 UPDATE CS_INCIDENTS_ALL_B
233 SET workflow_process_id = l_wf_process_id,
234 last_updated_by = TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
235 last_update_date = sysdate
236 WHERE CURRENT OF l_ServeReq_csr;
237 ELSE
238 UPDATE CS_INCIDENTS_ALL_B
239 SET workflow_process_id = l_wf_process_id,
240 last_update_date = sysdate
241 WHERE CURRENT OF l_ServeReq_csr;
242 END IF;
243
244
245 IF l_servereq_csr_rec.workflow is not null THEN
246
247 wf_engine.CreateProcess (Itemtype => 'SERVEREQ',
248 Itemkey => l_itemkey,
249 process => l_servereq_csr_rec.workflow);
250
251 wf_engine.startprocess (itemtype => 'SERVEREQ',
252 itemkey => l_itemkey);
253
254 END IF;
255 -- Commented the following and added above if condition to take care of the
256 -- condition when no workflow is associated 06/14/2002
257
258 /*
259 IF (p_source = 'FORM') THEN
260
261 wf_engine.CreateProcess (Itemtype => 'SERVEREQ',
262 Itemkey => l_itemkey,
263 process => l_servereq_csr_rec.workflow);
264
265 wf_engine.startprocess (itemtype => 'SERVEREQ',
266 itemkey => l_itemkey);
267
268 ELSE
269
270 wf_engine.CreateProcess (Itemtype => 'SERVEREQ',
271 Itemkey => l_itemkey,
272 process => 'CUG_GENERIC_WORKFLOW');
273
274 wf_engine.startprocess (itemtype => 'SERVEREQ',
275 itemkey => l_itemkey);
276
277 END IF;
278 */
279
280 END IF;
281
282 -- Endof API body.
283
284 -- Standard check for p_commit.
285 IF FND_API.To_Boolean ( p_commit ) THEN
286 COMMIT WORK;
287 END IF;
288
289
290 -- Standard call to get messgage count and if count is 1, get message info.
291 FND_MSG_PUB.Count_And_get (
292 p_count => x_msg_count,
293 p_data => x_msg_data
294 );
295
296
297 EXCEPTION
298
299 WHEN FND_API.G_EXC_ERROR THEN
300 IF (l_servereq_csr%ISOPEN) THEN
301 CLOSE l_servereq_csr;
302 END IF;
303 x_return_status := FND_API.G_RET_STS_ERROR;
304 FND_MSG_PUB.Count_And_Get
305 (
306 p_count => x_msg_count,
307 p_data => x_msg_data
308 );
309
310 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
311 IF (l_servereq_csr%ISOPEN) THEN
312 CLOSE l_servereq_csr;
313 END IF;
314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
315 FND_MSG_PUB.Count_And_Get
316 (
317 p_count => x_msg_count,
318 p_data => x_msg_data
319 );
320
321 WHEN OTHERS THEN
322 IF (l_servereq_csr%ISOPEN) THEN
323 CLOSE l_servereq_csr;
324 END IF;
325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
326 IF FND_MSG_PUB.Check_Msg_Level
327 ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
328 THEN
329 FND_MSG_PUB.Add_Exc_Msg
330 (
331 G_PKG_NAME,
332 l_api_name
333 );
334 END IF;
335 FND_MSG_PUB.Count_And_Get
336 (
337 p_count => x_msg_count,
338 p_data => x_msg_data
339 );
340
341 END launch_workflow;
342
343
344 PROCEDURE Create_Address_Note (
345 p_api_version IN NUMBER,
346 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
347 p_commit IN VARCHAR := FND_API.G_FALSE,
348 p_incident_id IN Number,
349 x_msg_count OUT NOCOPY NUMBER,
350 x_msg_data OUT NOCOPY VARCHAR2,
351 x_return_status OUT NOCOPY VARCHAR2 ,
352 x_note_id OUT NOCOPY NUMBER )
353 is
354 l_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS ;
355 l_msg_data VARCHAR2(240) := null;
356 l_commit VARCHAR2(1) := FND_API.G_FALSE;
357 l_addr_notes VARCHAR2(2000) := null;
358 l_msg_count NUMBER;
359 l_login_id NUMBER := 0 ;
360 l_created_by_user_id NUMBER := 0;
361 l_incident_id NUMBER := p_incident_id ;
362 l_note_context_tab_dflt JTF_NOTES_PUB.jtf_note_contexts_tbl_type;
363
364
365 Begin
366 x_return_status := FND_API.G_RET_STS_SUCCESS;
367
368 --dbms_output.put_line( ' Came Here 10' || x_return_status );
369 -- Changed from CHR(10) to '' to Comply with GSCC Error for New Line File.SQL.10
370 -- P.S DO NOT TRY TO INDENT THIS CODE SINCE IT WILL EFFECT THE NEW LINE CHAR
371 Begin
372
373 Select 'Incident Address ' ||'
374 ' || 'Address : ' || nvl(incident_address, ' ') || '
375 ' || 'City : ' || nvl(incident_city, ' ') || '
376 ' || 'State : ' || nvl(incident_state, ' ') || '
377 ' || 'Postal Code : ' || nvl(incident_postal_code, ' ') || '
378 ' || 'Country : ' || nvl(incident_country, ' ')
379 into l_addr_notes
380 From cs_incidents_all_b
381 Where incident_id = l_incident_id;
382 EXCEPTION
383 When NO_DATA_FOUND then
384 null;
385 -- dbms_output.put_line( ' Came Here 20 No Data Found' || x_return_status );
386 END;
387
388
389
390 /* May be add these also
391 incident_province
392 incident_county
393 */
394 -- dbms_output.put_line( ' Came Here 30' || x_return_status );
395 -- dbms_output.put_line( ' Came Here 40' || l_addr_notes );
396 JTF_NOTES_PUB.Create_note (
397 p_api_version => 1.0 ,
398 p_init_msg_list => 'T',
399 p_commit => l_commit,
400 p_validation_level => csc_core_utils_pvt.g_valid_level_none,
401 x_return_status => l_return_status ,
402 x_msg_count => l_msg_count,
403 x_msg_data => l_msg_data ,
404 p_source_object_id => l_incident_id,
405 p_source_object_code => 'SR',
406 p_notes => l_addr_notes ,
407 p_entered_by => l_created_by_user_id,
408 p_entered_date => sysdate,
409 x_jtf_note_id => x_note_id ,
410 p_last_update_date => sysdate,
411 p_last_updated_by => l_created_by_user_id,
412 p_creation_date => sysdate,
413 p_created_by => l_created_by_user_id,
414 p_last_update_login => l_login_id,
415 p_note_type => 'CUG_SR_ATTR_DETAILS',
416 p_jtf_note_contexts_tab => l_note_context_tab_dflt
417 );
418 /*
419 dbms_output.put_line( ' Came Here 50' || x_return_status );
420 dbms_output.put_line( 'x_note_id is ' || x_note_id );
421 dbms_output.put_line( 'l_return_status is ' || l_return_status );
422 dbms_output.put_line( 'l_msg_data is ' || l_msg_data );
423
424 */
425 -- Standard check of p_commit.
426 IF FND_API.To_Boolean( p_commit ) THEN
427 COMMIT WORK;
428 END IF;
429 -- Standard call to get message count and if count is 1, get message info.
430 FND_MSG_PUB.Count_And_Get
431 (p_count => x_msg_count ,
432 p_data => x_msg_data
433 );
434
435 EXCEPTION
436
437 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
438 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
439 FND_MSG_PUB.count_and_get( p_encoded=> FND_API.G_FALSE,
440 p_count => x_msg_count,
441 p_data => x_msg_data);
442 WHEN FND_API.G_EXC_ERROR THEN
443 x_return_status := FND_API.G_RET_STS_ERROR;
444 FND_MSG_PUB.count_and_get( p_encoded=> FND_API.G_FALSE,
445 p_count => x_msg_count,
446 p_data => x_msg_data);
447 WHEN OTHERS THEN
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
449 FND_MSG_PUB.count_and_get( p_encoded=> FND_API.G_FALSE,
450 p_count => x_msg_count,
451 p_data => x_msg_data);
452
453
454 END Create_Address_Note;
455
456 end CUG_INCIDNT_ATTR_VALS_PVT ;