[Home] [Help]
PACKAGE BODY: APPS.AMV_PERSPECTIVE_PVT
Source
1 PACKAGE BODY amv_perspective_pvt AS
2 /* $Header: amvvpspb.pls 120.1 2005/06/21 16:51:18 appldev ship $ */
3 --
4 -- NAME
5 -- AMV_PERSPECTIVE_PVT
6 --
7 -- HISTORY
8 -- 07/19/1999 PWU CREATED
9 --
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMV_PERSPECTIVE_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amvvpspb.pls';
12 --
13 -- Debug mode
14 g_debug boolean := TRUE;
15 TYPE CursorType IS REF CURSOR;
16 --
17 ----------------------------- Private Cursors in this package ------------------
18 CURSOR Check_DupPerspectName_csr(p_name IN varchar2) IS
19 Select
20 perspective_id
21 From amv_i_perspectives_tl
22 Where perspective_name = p_name
23 And language IN
24 (
25 Select L.language_code
26 From fnd_languages L
27 Where L.installed_flag in ('I', 'B')
28 )
29 ;
30 --
31 CURSOR Check_ValidPerspectName_csr(p_name varchar2) IS
32 Select
33 perspective_id
34 From amv_i_perspectives_tl
35 Where perspective_name = p_name
36 And language = userenv('lang');
37 --
38 --------------------------- Private Utility inside this package ----------------
39 --------------------------------------------------------------------------------
40 --
41 --
42 --------------------------------------------------------------------------------
43 PROCEDURE Add_Perspective
44 (
45 p_api_version IN NUMBER,
46 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
47 p_commit IN VARCHAR2 := FND_API.G_FALSE,
48 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_msg_count OUT NOCOPY NUMBER,
51 x_msg_data OUT NOCOPY VARCHAR2,
52 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
53 p_perspective_name IN VARCHAR2,
54 p_persp_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
55 x_perspective_id OUT NOCOPY NUMBER
56 ) IS
57 l_api_name CONSTANT VARCHAR2(30) := 'Add_Perspective';
58 l_api_version CONSTANT NUMBER := 1.0;
59 l_resource_id NUMBER := -1;
60 l_current_user_id NUMBER := -1;
61 l_current_login_id NUMBER := -1;
62 l_current_user_status VARCHAR2(80);
63 --
64 l_admin_flag VARCHAR2(1);
65 l_rowid VARCHAR2(500);
66 l_perspective_id NUMBER;
67 l_persp_description VARCHAR2(2000);
68 l_current_date date;
69 --
70 CURSOR Get_DateAndId_csr IS
71 select
72 AMV_I_PERSPECTIVES_B_S.nextval, sysdate
73 from dual;
74 --
75 BEGIN
76 -- Standard call to check for call compatibility.
77 SAVEPOINT Add_Perspective_Pvt;
78 IF NOT FND_API.Compatible_API_Call (
79 l_api_version,
80 p_api_version,
81 l_api_name,
82 G_PKG_NAME) THEN
83 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
84 END IF;
85 --Initialize message list if p_init_msg_list is TRUE.
86 IF FND_API.To_Boolean (p_init_msg_list) THEN
87 FND_MSG_PUB.initialize;
88 END IF;
89 -- Initialize API return status to success
90 x_return_status := FND_API.G_RET_STS_SUCCESS;
91 --
92 -- Get the current (login) user id.
93 AMV_UTILITY_PVT.Get_UserInfo(
94 x_resource_id => l_resource_id,
95 x_user_id => l_current_user_id,
96 x_login_id => l_current_login_id,
97 x_user_status => l_current_user_status
98 );
99 IF (p_check_login_user = FND_API.G_TRUE) THEN
100 -- Check if user is login and has the required privilege.
101 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
102 -- User is not login.
103 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
104 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
105 FND_MSG_PUB.Add;
106 END IF;
107 RAISE FND_API.G_EXC_ERROR;
108 END IF;
109 AMV_USER_PVT.Is_Administrator
110 (
111 p_api_version => 1.0,
112 x_return_status => x_return_status,
113 x_msg_count => x_msg_count,
114 x_msg_data => x_msg_data,
115 p_check_login_user => FND_API.G_FALSE,
116 p_resource_id => l_resource_id,
117 x_result_flag => l_admin_flag
118 );
119 IF (l_admin_flag <> FND_API.G_TRUE) THEN
120 -- User is not an administrator.
121 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
122 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
123 FND_MSG_PUB.Add;
124 END IF;
125 RAISE FND_API.G_EXC_ERROR;
126 END IF;
127 END IF;
128 -- This is to make database happy
129 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
130 l_current_login_id := -1;
131 l_current_user_id := -1;
132 END IF;
133 --
134 OPEN Check_DupPerspectName_csr(p_perspective_name);
135 FETCH Check_DupPerspectName_csr Into l_perspective_id;
136 IF Check_DupPerspectName_csr%FOUND THEN
137 -- The name is already used
138 CLOSE Check_DupPerspectName_csr;
139 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
140 FND_MESSAGE.Set_name('AMV','AMV_RECORD_NAME_DUPLICATED');
141 FND_MESSAGE.Set_Token('RECORD', 'AMV_PERSPECTIVE_TK', TRUE);
142 FND_MESSAGE.Set_Token('NAME', p_perspective_name);
143 FND_MSG_PUB.Add;
144 END IF;
145 RAISE FND_API.G_EXC_ERROR;
146 END IF;
147 CLOSE Check_DupPerspectName_csr;
148 --
149 OPEN Get_DateAndId_csr;
150 FETCH Get_DateAndId_csr Into l_perspective_id, l_current_date;
151 CLOSE Get_DateAndId_csr;
152 --
153 IF (p_persp_description = FND_API.G_MISS_CHAR) THEN
154 l_persp_description := NULL;
155 ELSE
156 l_persp_description := p_persp_description;
157 END IF;
158 --Do create the record now.
159 AMV_I_PERSPECTIVES_PKG.INSERT_ROW
160 (
161 X_ROWID => l_rowid,
162 X_PERSPECTIVE_ID => l_perspective_id,
163 X_OBJECT_VERSION_NUMBER => 1,
164 X_PERSPECTIVE_NAME => p_perspective_name,
165 X_DESCRIPTION => l_persp_description,
166 X_CREATION_DATE => l_current_date,
167 X_CREATED_BY => l_current_user_id,
168 X_LAST_UPDATE_DATE => l_current_date,
169 X_LAST_UPDATED_BY => l_current_user_id,
170 X_LAST_UPDATE_LOGIN => l_current_login_id
171 );
172 --Standard check of commit
173 IF FND_API.To_Boolean ( p_commit ) THEN
174 COMMIT WORK;
175 END IF;
176 -- Debug Message
177 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
178 FND_MESSAGE.Set_name('AMV','PVT Add Persp. API: End');
179 FND_MSG_PUB.Add;
180 END IF;
181 --Standard call to get message count and if count=1, get the message
182 FND_MSG_PUB.Count_And_Get (
183 p_encoded => FND_API.G_FALSE,
184 p_count => x_msg_count,
185 p_data => x_msg_data
186 );
187 EXCEPTION
188 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
189 ROLLBACK TO Add_Perspective_Pvt;
190 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
191 -- Standard call to get message count and if count=1, get the message
192 FND_MSG_PUB.Count_And_Get (
193 p_encoded => FND_API.G_FALSE,
194 p_count => x_msg_count,
195 p_data => x_msg_data
196 );
197 WHEN FND_API.G_EXC_ERROR THEN
198 ROLLBACK TO Add_Perspective_Pvt;
199 x_return_status := FND_API.G_RET_STS_ERROR;
200 -- Standard call to get message count and if count=1, get the message
201 FND_MSG_PUB.Count_And_Get (
202 p_encoded => FND_API.G_FALSE,
203 p_count => x_msg_count,
204 p_data => x_msg_data
205 );
206 WHEN OTHERS THEN
207 ROLLBACK TO Add_Perspective_Pvt;
208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
209 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
210 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
211 END IF;
212 -- Standard call to get message count and if count=1, get the message
213 FND_MSG_PUB.Count_And_Get (
214 p_encoded => FND_API.G_FALSE,
215 p_count => x_msg_count,
216 p_data => x_msg_data
217 );
218 --
219 END Add_Perspective;
220 --------------------------------------------------------------------------------
221 PROCEDURE Delete_Perspective
222 (
223 p_api_version IN NUMBER,
224 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
225 p_commit IN VARCHAR2 := FND_API.G_FALSE,
226 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
227 x_return_status OUT NOCOPY VARCHAR2,
228 x_msg_count OUT NOCOPY NUMBER,
229 x_msg_data OUT NOCOPY VARCHAR2,
230 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
231 p_perspective_id IN NUMBER := FND_API.G_MISS_NUM,
232 p_perspective_name IN VARCHAR2 := FND_API.G_MISS_CHAR
233 ) IS
234 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Perspective';
235 l_api_version CONSTANT NUMBER := 1.0;
236 l_resource_id NUMBER := -1;
237 l_current_user_id NUMBER := -1;
238 l_current_login_id NUMBER := -1;
239 l_current_user_status VARCHAR2(80);
240 --
241 l_admin_flag VARCHAR2(1);
242 l_perspective_id NUMBER;
243 --
244 --
245 BEGIN
246 -- Standard call to check for call compatibility.
247 SAVEPOINT Delete_Perspective_Pvt;
248 IF NOT FND_API.Compatible_API_Call (
249 l_api_version,
250 p_api_version,
251 l_api_name,
252 G_PKG_NAME) THEN
253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
254 END IF;
255 --Initialize message list if p_init_msg_list is TRUE.
256 IF FND_API.To_Boolean (p_init_msg_list) THEN
257 FND_MSG_PUB.initialize;
258 END IF;
259 -- Initialize API return status to success
260 x_return_status := FND_API.G_RET_STS_SUCCESS;
261 -- Get the current (login) user id.
262 AMV_UTILITY_PVT.Get_UserInfo(
263 x_resource_id => l_resource_id,
264 x_user_id => l_current_user_id,
265 x_login_id => l_current_login_id,
266 x_user_status => l_current_user_status
267 );
268 IF (p_check_login_user = FND_API.G_TRUE) THEN
269 -- Check if user is login and has the required privilege.
270 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
271 -- User is not login.
272 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
273 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
274 FND_MSG_PUB.Add;
275 END IF;
276 RAISE FND_API.G_EXC_ERROR;
277 END IF;
278 AMV_USER_PVT.Is_Administrator
279 (
280 p_api_version => 1.0,
281 x_return_status => x_return_status,
282 x_msg_count => x_msg_count,
283 x_msg_data => x_msg_data,
284 p_check_login_user => FND_API.G_FALSE,
285 p_resource_id => l_resource_id,
286 x_result_flag => l_admin_flag
287 );
288 IF (l_admin_flag <> FND_API.G_TRUE) THEN
289 -- User is not an administrator.
290 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
291 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
292 FND_MSG_PUB.Add;
293 END IF;
294 RAISE FND_API.G_EXC_ERROR;
295 END IF;
296 END IF;
297 -- This is to make database happy
298 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
299 l_current_login_id := -1;
300 l_current_user_id := -1;
301 END IF;
302 --
303 IF (p_perspective_id <> FND_API.G_MISS_NUM) THEN
304 -- Check if user pass the valid perspective id
305 IF AMV_UTILITY_PVT.Is_PerspectiveIdValid(p_perspective_id) = TRUE THEN
306 l_perspective_id := p_perspective_id;
307 ELSE
308 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
309 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
310 FND_MESSAGE.Set_Token('RECORD', 'AMV_PERSPECTIVE_TK', TRUE);
311 FND_MESSAGE.Set_Token('ID', to_char(p_perspective_id));
312 FND_MSG_PUB.Add;
313 END IF;
314 RAISE FND_API.G_EXC_ERROR;
315 END IF;
316 ELSIF (p_perspective_name <> FND_API.G_MISS_CHAR) THEN
317 OPEN Check_ValidPerspectName_csr(p_perspective_name);
318 FETCH Check_ValidPerspectName_csr Into l_perspective_id;
319 IF (Check_ValidPerspectName_csr%NOTFOUND) THEN
320 CLOSE Check_ValidPerspectName_csr;
321 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
322 FND_MESSAGE.Set_name('AMV','AMV_RECORD_NAME_MISSING');
323 FND_MESSAGE.Set_Token('RECORD', 'AMV_PERSPECTIVE_TK', TRUE);
324 FND_MESSAGE.Set_Token('NAME', p_perspective_name);
325 FND_MSG_PUB.Add;
326 END IF;
327 RAISE FND_API.G_EXC_ERROR;
328 END IF;
329 CLOSE Check_ValidPerspectName_csr;
330 ELSE
331 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
332 FND_MESSAGE.Set_name('AMV','AMV_NEED_RECORD_NAME_OR_ID');
333 FND_MESSAGE.Set_Token('RECORD', 'AMV_PERSPECTIVE_TK', TRUE);
334 FND_MSG_PUB.Add;
335 END IF;
336 RAISE FND_API.G_EXC_ERROR;
337 END IF;
338 -- Now do the deleting:
339 Delete from amv_c_chl_perspectives
340 where perspective_id = l_perspective_id;
341 --
342 Delete from amv_i_item_perspectives
343 where perspective_id = l_perspective_id;
344 --
345 AMV_I_PERSPECTIVES_PKG.DELETE_ROW
346 (
347 X_PERSPECTIVE_ID => l_perspective_id
348 );
349 --Standard check of commit
350 IF FND_API.To_Boolean ( p_commit ) THEN
351 COMMIT WORK;
352 END IF;
353 --Standard call to get message count and if count=1, get the message
354 FND_MSG_PUB.Count_And_Get (
355 p_encoded => FND_API.G_FALSE,
356 p_count => x_msg_count,
357 p_data => x_msg_data
358 );
359 EXCEPTION
360 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
361 ROLLBACK TO Delete_Perspective_Pvt;
362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
363 -- Standard call to get message count and if count=1, get the message
364 FND_MSG_PUB.Count_And_Get (
365 p_encoded => FND_API.G_FALSE,
366 p_count => x_msg_count,
367 p_data => x_msg_data
368 );
369 WHEN FND_API.G_EXC_ERROR THEN
370 ROLLBACK TO Delete_Perspective_Pvt;
371 x_return_status := FND_API.G_RET_STS_ERROR;
372 -- Standard call to get message count and if count=1, get the message
373 FND_MSG_PUB.Count_And_Get (
374 p_encoded => FND_API.G_FALSE,
375 p_count => x_msg_count,
376 p_data => x_msg_data
377 );
378 WHEN OTHERS THEN
379 ROLLBACK TO Delete_Perspective_Pvt;
380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
381 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
382 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
383 END IF;
384 -- Standard call to get message count and if count=1, get the message
385 FND_MSG_PUB.Count_And_Get (
386 p_encoded => FND_API.G_FALSE,
387 p_count => x_msg_count,
388 p_data => x_msg_data
389 );
390 --
391 END Delete_Perspective;
392 --------------------------------------------------------------------------------
393 PROCEDURE Update_Perspective
394 (
395 p_api_version IN NUMBER,
396 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
397 p_commit IN VARCHAR2 := FND_API.G_FALSE,
398 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
399 x_return_status OUT NOCOPY VARCHAR2,
400 x_msg_count OUT NOCOPY NUMBER,
401 x_msg_data OUT NOCOPY VARCHAR2,
402 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
403 p_perspective_id IN NUMBER := FND_API.G_MISS_NUM,
404 p_perspective_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
405 p_perspective_new_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
406 p_persp_description IN VARCHAR2 := FND_API.G_MISS_CHAR
407 ) IS
408 l_api_name CONSTANT VARCHAR2(30) := 'Update_Perspective';
409 l_api_version CONSTANT NUMBER := 1.0;
410 l_resource_id NUMBER := -1;
411 l_current_user_id NUMBER := -1;
412 l_current_login_id NUMBER := -1;
413 l_current_user_status VARCHAR2(80);
414 --
415 l_admin_flag VARCHAR2(1);
416 l_perspective_id NUMBER;
417 l_object_version NUMBER;
418 --
419 CURSOR Get_Version_csr(p_persp_id IN NUMBER) IS
420 Select
421 object_version_number
422 from Amv_i_perspectives_b
423 where perspective_id = p_persp_id;
424 --
425 --
426 BEGIN
427 -- Standard call to check for call compatibility.
428 SAVEPOINT Update_Perspective_Pvt;
429 IF NOT FND_API.Compatible_API_Call (
430 l_api_version,
431 p_api_version,
432 l_api_name,
433 G_PKG_NAME) THEN
434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
435 END IF;
436 --Initialize message list if p_init_msg_list is TRUE.
437 IF FND_API.To_Boolean (p_init_msg_list) THEN
438 FND_MSG_PUB.initialize;
439 END IF;
440 -- Initialize API return status to success
441 x_return_status := FND_API.G_RET_STS_SUCCESS;
442 -- Get the current (login) user id.
443 AMV_UTILITY_PVT.Get_UserInfo(
444 x_resource_id => l_resource_id,
445 x_user_id => l_current_user_id,
446 x_login_id => l_current_login_id,
447 x_user_status => l_current_user_status
448 );
449 IF (p_check_login_user = FND_API.G_TRUE) THEN
450 -- Check if user is login and has the required privilege.
451 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
452 -- User is not login.
453 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
454 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
455 FND_MSG_PUB.Add;
456 END IF;
457 RAISE FND_API.G_EXC_ERROR;
458 END IF;
459 AMV_USER_PVT.Is_Administrator
460 (
461 p_api_version => 1.0,
462 x_return_status => x_return_status,
463 x_msg_count => x_msg_count,
464 x_msg_data => x_msg_data,
465 p_check_login_user => FND_API.G_FALSE,
466 p_resource_id => l_resource_id,
467 x_result_flag => l_admin_flag
468 );
469 IF (l_admin_flag <> FND_API.G_TRUE) THEN
470 -- User is not an administrator.
471 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
472 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
473 FND_MSG_PUB.Add;
474 END IF;
475 RAISE FND_API.G_EXC_ERROR;
476 END IF;
477 END IF;
478 -- This is to make database happy
479 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
480 l_current_login_id := -1;
481 l_current_user_id := -1;
482 END IF;
483 -- Ensure the name is not used yet.
484 OPEN Check_DupPerspectName_csr(p_perspective_new_name);
485 FETCH Check_DupPerspectName_csr INTO l_perspective_id;
486 IF Check_DupPerspectName_csr%FOUND THEN
487 CLOSE Check_DupPerspectName_csr;
488 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
489 FND_MESSAGE.Set_name('AMV','AMV_RECORD_NAME_DUPLICATED');
490 FND_MESSAGE.Set_Token('RECORD', 'AMV_PERSPECTIVE_TK', TRUE);
491 FND_MESSAGE.Set_Token('NAME', p_perspective_new_name);
492 FND_MSG_PUB.Add;
493 END IF;
494 RAISE FND_API.G_EXC_ERROR;
495 END IF;
496 CLOSE Check_DupPerspectName_csr;
497 --
498 IF (p_perspective_id <> FND_API.G_MISS_NUM) THEN
499 -- Check if user pass the valid perspective id
500 IF AMV_UTILITY_PVT.Is_PerspectiveIdValid(p_perspective_id) = TRUE THEN
501 l_perspective_id := p_perspective_id;
502 ELSE
503 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
504 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
505 FND_MESSAGE.Set_Token('RECORD', 'AMV_PERSPECTIVE_TK', TRUE);
506 FND_MESSAGE.Set_Token('ID', to_char(p_perspective_id));
507 FND_MSG_PUB.Add;
508 END IF;
509 RAISE FND_API.G_EXC_ERROR;
510 END IF;
511 ELSIF (p_perspective_name <> FND_API.G_MISS_CHAR) THEN
512 OPEN Check_ValidPerspectName_csr(p_perspective_name);
513 FETCH Check_ValidPerspectName_csr Into l_perspective_id;
514 IF (Check_ValidPerspectName_csr%NOTFOUND) THEN
515 CLOSE Check_ValidPerspectName_csr;
516 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
517 FND_MESSAGE.Set_name('AMV','AMV_RECORD_NAME_MISSING');
518 FND_MESSAGE.Set_Token('RECORD', 'AMV_PERSPECTIVE_TK', TRUE);
519 FND_MESSAGE.Set_Token('NAME', p_perspective_name);
520 FND_MSG_PUB.Add;
521 END IF;
522 RAISE FND_API.G_EXC_ERROR;
523 END IF;
524 CLOSE Check_ValidPerspectName_csr;
525 ELSE
526 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
527 FND_MESSAGE.Set_name('AMV','AMV_NEED_RECORD_NAME_OR_ID');
528 FND_MESSAGE.Set_Token('RECORD', 'AMV_PERSPECTIVE_TK', TRUE);
529 FND_MSG_PUB.Add;
530 END IF;
531 RAISE FND_API.G_EXC_ERROR;
532 END IF;
533 -- Get the current version number
534 OPEN Get_Version_csr(l_perspective_id);
535 FETCH Get_Version_csr INTO l_object_version;
536 CLOSE Get_Version_csr;
537 -- Now do the updating:
538 AMV_I_PERSPECTIVES_PKG.UPDATE_ROW
539 (
540 X_PERSPECTIVE_ID => l_perspective_id,
541 X_OBJECT_VERSION_NUMBER => l_object_version + 1,
542 X_PERSPECTIVE_NAME => p_perspective_new_name,
543 X_DESCRIPTION => p_persp_description,
544 X_LAST_UPDATE_DATE => sysdate,
545 X_LAST_UPDATED_BY => l_current_user_id,
546 X_LAST_UPDATE_LOGIN => l_current_login_id
547 );
548 --Standard check of commit
549 IF FND_API.To_Boolean ( p_commit ) THEN
550 COMMIT WORK;
551 END IF;
552 -- Debug Message
553 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
554 FND_MESSAGE.Set_name('AMV','PVT Update Persp. API: End');
555 FND_MSG_PUB.Add;
556 END IF;
557 --Standard call to get message count and if count=1, get the message
558 FND_MSG_PUB.Count_And_Get (
559 p_encoded => FND_API.G_FALSE,
560 p_count => x_msg_count,
561 p_data => x_msg_data
562 );
563 --
564 EXCEPTION
565 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
566 ROLLBACK TO Update_Perspective_Pvt;
567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
568 -- Standard call to get message count and if count=1, get the message
569 FND_MSG_PUB.Count_And_Get (
570 p_encoded => FND_API.G_FALSE,
571 p_count => x_msg_count,
572 p_data => x_msg_data
573 );
574 WHEN FND_API.G_EXC_ERROR THEN
575 ROLLBACK TO Update_Perspective_Pvt;
576 x_return_status := FND_API.G_RET_STS_ERROR;
577 -- Standard call to get message count and if count=1, get the message
578 FND_MSG_PUB.Count_And_Get (
579 p_encoded => FND_API.G_FALSE,
580 p_count => x_msg_count,
581 p_data => x_msg_data
582 );
583 WHEN OTHERS THEN
584 ROLLBACK TO Update_Perspective_Pvt;
585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
586 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
587 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
588 END IF;
589 -- Standard call to get message count and if count=1, get the message
590 FND_MSG_PUB.Count_And_Get (
591 p_encoded => FND_API.G_FALSE,
592 p_count => x_msg_count,
593 p_data => x_msg_data
594 );
595 END Update_Perspective;
596 --
597 --------------------------------------------------------------------------------
598 PROCEDURE Get_Perspective
599 (
600 p_api_version IN NUMBER,
601 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
602 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
603 x_return_status OUT NOCOPY VARCHAR2,
604 x_msg_count OUT NOCOPY NUMBER,
605 x_msg_data OUT NOCOPY VARCHAR2,
606 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
607 p_perspective_id IN NUMBER := FND_API.G_MISS_NUM,
608 p_perspective_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
609 x_perspective_obj OUT NOCOPY AMV_PERSPECTIVE_OBJ_TYPE
610 ) IS
611 l_api_name CONSTANT VARCHAR2(30) := 'Get_Perspective';
612 l_api_version CONSTANT NUMBER := 1.0;
613 l_resource_id NUMBER := -1;
614 l_current_user_id NUMBER := -1;
615 l_current_login_id NUMBER := -1;
616 l_current_user_status VARCHAR2(80);
617 --
618 l_perspective_id NUMBER;
619 --
620 CURSOR Get_PerspRecord_csr (p_ID IN NUMBER) IS
621 Select
622 B.PERSPECTIVE_ID,
623 B.OBJECT_VERSION_NUMBER,
624 T.PERSPECTIVE_NAME,
625 T.DESCRIPTION,
626 T.LANGUAGE,
627 T.SOURCE_LANG,
628 B.CREATION_DATE,
629 B.CREATED_BY,
630 B.LAST_UPDATE_DATE,
631 B.LAST_UPDATED_BY,
632 B.LAST_UPDATE_LOGIN
633 From AMV_I_PERSPECTIVES_TL T, AMV_I_PERSPECTIVES_B B
634 Where B.PERSPECTIVE_ID = T.PERSPECTIVE_ID
635 And T.LANGUAGE = userenv('LANG')
636 And B.PERSPECTIVE_ID = p_ID;
637 --
638 l_perspective_rec Get_PerspRecord_csr%ROWTYPE;
639 --
640 BEGIN
641 -- Standard call to check for call compatibility.
642 IF NOT FND_API.Compatible_API_Call (
643 l_api_version,
644 p_api_version,
645 l_api_name,
646 G_PKG_NAME) THEN
647 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
648 END IF;
649 -- Debug Message
650 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
651 FND_MESSAGE.Set_name('AMV','PVT Get Persp. API: Start');
652 FND_MSG_PUB.Add;
653 END IF;
654 --Initialize message list if p_init_msg_list is TRUE.
655 IF FND_API.To_Boolean (p_init_msg_list) THEN
656 FND_MSG_PUB.initialize;
657 END IF;
658 -- Initialize API return status to success
659 x_return_status := FND_API.G_RET_STS_SUCCESS;
660 --
661 -- Get the current (login) user id.
662 AMV_UTILITY_PVT.Get_UserInfo(
663 x_resource_id => l_resource_id,
664 x_user_id => l_current_user_id,
665 x_login_id => l_current_login_id,
666 x_user_status => l_current_user_status
667 );
668 IF (p_check_login_user = FND_API.G_TRUE) THEN
669 -- Check if user is login and has the required privilege.
670 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
671 -- User is not login.
672 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
673 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
674 FND_MSG_PUB.Add;
675 END IF;
676 RAISE FND_API.G_EXC_ERROR;
677 END IF;
678 END IF;
679 -- This is to make database happy
680 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
681 l_current_login_id := -1;
682 l_current_user_id := -1;
683 END IF;
684 IF (p_perspective_id <> FND_API.G_MISS_NUM) THEN
685 -- Check if user pass the valid perspective id
686 IF AMV_UTILITY_PVT.Is_PerspectiveIdValid(p_perspective_id) = TRUE THEN
687 l_perspective_id := p_perspective_id;
688 ELSE
689 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
690 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
691 FND_MESSAGE.Set_Token('RECORD', 'AMV_PERSPECTIVE_TK', TRUE);
692 FND_MESSAGE.Set_Token('ID', to_char(p_perspective_id));
693 FND_MSG_PUB.Add;
694 END IF;
695 RAISE FND_API.G_EXC_ERROR;
696 END IF;
697 ELSIF (p_perspective_name <> FND_API.G_MISS_CHAR) THEN
698 OPEN Check_ValidPerspectName_csr(p_perspective_name);
699 FETCH Check_ValidPerspectName_csr Into l_perspective_id;
700 IF (Check_ValidPerspectName_csr%NOTFOUND) THEN
701 CLOSE Check_ValidPerspectName_csr;
702 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
703 FND_MESSAGE.Set_name('AMV','AMV_RECORD_NAME_MISSING');
704 FND_MESSAGE.Set_Token('RECORD', 'AMV_PERSPECTIVE_TK', TRUE);
705 FND_MESSAGE.Set_Token('NAME', p_perspective_name);
706 FND_MSG_PUB.Add;
707 END IF;
708 RAISE FND_API.G_EXC_ERROR;
709 END IF;
710 CLOSE Check_ValidPerspectName_csr;
711 ELSE
712 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
713 FND_MESSAGE.Set_name('AMV','AMV_NEED_RECORD_NAME_OR_ID');
714 FND_MESSAGE.Set_Token('RECORD', 'AMV_PERSPECTIVE_TK', TRUE);
715 FND_MSG_PUB.Add;
716 END IF;
717 RAISE FND_API.G_EXC_ERROR;
718 END IF;
719 -- Now query and return the perspective record
720 OPEN Get_PerspRecord_csr(l_perspective_id);
721 FETCH Get_PerspRecord_csr INTO l_perspective_rec;
722 IF (Get_PerspRecord_csr%NOTFOUND) THEN
723 CLOSE Get_PerspRecord_csr;
724 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
725 FND_MESSAGE.Set_name('AMV','AMV_RECORD_NOT_FOUND');
726 FND_MESSAGE.Set_Token('RECORD', 'AMV_PERSPECTIVE_TK', TRUE);
727 FND_MSG_PUB.Add;
728 END IF;
729 RAISE FND_API.G_EXC_ERROR;
730 ELSE
731 x_perspective_obj.perspective_id := l_perspective_rec.perspective_id;
732 x_perspective_obj.object_version_number := l_perspective_rec.object_version_number;
733 x_perspective_obj.perspective_name := l_perspective_rec.perspective_name;
734 x_perspective_obj.description := l_perspective_rec.description;
735 x_perspective_obj.language := l_perspective_rec.language;
736 x_perspective_obj.source_lang := l_perspective_rec.source_lang;
737 x_perspective_obj.creation_date := l_perspective_rec.creation_date;
738 x_perspective_obj.created_by := l_perspective_rec.created_by;
739 x_perspective_obj.last_update_date := l_perspective_rec.last_update_date;
740 x_perspective_obj.last_updated_by := l_perspective_rec.last_updated_by;
741 x_perspective_obj.last_update_login := l_perspective_rec.last_update_login;
742 /*
743 x_perspective_obj := AMV_PERSPECTIVE_OBJ_TYPE
744 (
745 l_perspective_rec.perspective_id,
746 l_perspective_rec.object_version_number,
747 l_perspective_rec.perspective_name,
748 l_perspective_rec.description,
749 l_perspective_rec.language,
750 l_perspective_rec.source_lang,
751 l_perspective_rec.creation_date,
752 l_perspective_rec.created_by,
753 l_perspective_rec.last_update_date,
754 l_perspective_rec.last_updated_by,
755 l_perspective_rec.last_update_login
756 );
757 */
758 END IF;
759 CLOSE Get_PerspRecord_csr;
760 --Standard call to get message count and if count=1, get the message
761 FND_MSG_PUB.Count_And_Get (
762 p_encoded => FND_API.G_FALSE,
763 p_count => x_msg_count,
764 p_data => x_msg_data
765 );
766 EXCEPTION
767 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
769 -- Standard call to get message count and if count=1, get the message
770 FND_MSG_PUB.Count_And_Get (
771 p_encoded => FND_API.G_FALSE,
772 p_count => x_msg_count,
773 p_data => x_msg_data
774 );
775 WHEN FND_API.G_EXC_ERROR THEN
776 x_return_status := FND_API.G_RET_STS_ERROR;
777 -- Standard call to get message count and if count=1, get the message
778 FND_MSG_PUB.Count_And_Get (
779 p_encoded => FND_API.G_FALSE,
780 p_count => x_msg_count,
781 p_data => x_msg_data
782 );
783 WHEN OTHERS THEN
784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
785 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
786 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
787 END IF;
788 -- Standard call to get message count and if count=1, get the message
789 FND_MSG_PUB.Count_And_Get (
790 p_encoded => FND_API.G_FALSE,
791 p_count => x_msg_count,
792 p_data => x_msg_data
793 );
794 --
795 END Get_Perspective;
796 --------------------------------------------------------------------------------
797 PROCEDURE Find_Perspective
798 (
799 p_api_version IN NUMBER,
800 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
801 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
802 x_return_status OUT NOCOPY VARCHAR2,
803 x_msg_count OUT NOCOPY NUMBER,
804 x_msg_data OUT NOCOPY VARCHAR2,
805 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
806 p_perspective_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
807 p_persp_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
808 p_subset_request_obj IN AMV_REQUEST_OBJ_TYPE,
809 x_subset_return_obj OUT NOCOPY AMV_RETURN_OBJ_TYPE,
810 x_perspective_obj_varray OUT NOCOPY AMV_PERSPECTIVE_OBJ_VARRAY
811 ) IS
812 l_api_name CONSTANT VARCHAR2(30) := 'Find_Perspective';
813 l_api_version CONSTANT NUMBER := 1.0;
814 l_resource_id NUMBER := -1;
815 l_current_user_id NUMBER := -1;
816 l_current_login_id NUMBER := -1;
817 l_current_user_status VARCHAR2(80);
818 --
819 l_cursor CursorType;
820 l_sql_statement VARCHAR2(2000);
821 l_sql_statement2 VARCHAR2(2000);
822 l_where_clause VARCHAR2(2000);
823 l_total_count NUMBER := 1;
824 l_fetch_count NUMBER := 0;
825 l_start_with NUMBER;
826 l_total_record_count NUMBER;
827 --
828 l_perspective_id NUMBER;
829 l_object_version_number NUMBER;
830 l_perspective_name VARCHAR2(80);
831 l_persp_description VARCHAR2(2000);
832 l_language VARCHAR2(4);
833 l_source_lang VARCHAR2(4);
834 l_creation_date DATE;
835 l_created_by NUMBER;
836 l_last_update_date DATE;
837 l_last_updated_by NUMBER;
838 l_last_update_login NUMBER;
839 --
840 BEGIN
841 -- Standard call to check for call compatibility.
842 IF NOT FND_API.Compatible_API_Call (
843 l_api_version,
844 p_api_version,
845 l_api_name,
846 G_PKG_NAME) THEN
847 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
848 END IF;
849 --Initialize message list if p_init_msg_list is TRUE.
850 IF FND_API.To_Boolean (p_init_msg_list) THEN
851 FND_MSG_PUB.initialize;
852 END IF;
853 -- Initialize API return status to success
854 x_return_status := FND_API.G_RET_STS_SUCCESS;
855 -- Get the current (login) user id.
856 AMV_UTILITY_PVT.Get_UserInfo(
857 x_resource_id => l_resource_id,
858 x_user_id => l_current_user_id,
859 x_login_id => l_current_login_id,
860 x_user_status => l_current_user_status
861 );
862 IF (p_check_login_user = FND_API.G_TRUE) THEN
863 -- Check if user is login and has the required privilege.
864 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
865 -- User is not login.
866 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
867 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
868 FND_MSG_PUB.Add;
869 END IF;
870 RAISE FND_API.G_EXC_ERROR;
871 END IF;
872 END IF;
873 -- This is to make database happy
874 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
875 l_current_login_id := -1;
876 l_current_user_id := -1;
877 END IF;
878 -- Now create SQL statement and find the results:
879 l_sql_statement :=
880 'Select ' ||
881 'B.PERSPECTIVE_ID, ' ||
882 'B.OBJECT_VERSION_NUMBER, ' ||
883 'T.PERSPECTIVE_NAME, ' ||
884 'T.DESCRIPTION, ' ||
885 'T.LANGUAGE, ' ||
886 'T.SOURCE_LANG, ' ||
887 'B.CREATION_DATE, ' ||
888 'B.CREATED_BY, ' ||
889 'B.LAST_UPDATE_DATE, ' ||
890 'B.LAST_UPDATED_BY, ' ||
891 'B.LAST_UPDATE_LOGIN ' ||
892 'From AMV_I_PERSPECTIVES_TL T, AMV_I_PERSPECTIVES_B B ';
893 l_sql_statement2 :=
894 'Select count(*) ' ||
895 'From AMV_I_PERSPECTIVES_TL T, AMV_I_PERSPECTIVES_B B ';
896 --
897 l_where_clause :=
898 'Where B.PERSPECTIVE_ID = T.PERSPECTIVE_ID ' ||
899 'And T.LANGUAGE = userenv(''LANG'') ';
900 IF (p_perspective_name IS NOT NULL AND
901 p_perspective_name <> FND_API.G_MISS_CHAR) THEN
902 l_where_clause := l_where_clause ||
903 'And T.PERSPECTIVE_NAME Like ''' || p_perspective_name || ''' ';
904 END IF;
905 IF (p_persp_description IS NOT NULL AND
906 p_persp_description <> FND_API.G_MISS_CHAR) THEN
907 l_where_clause := l_where_clause ||
908 'And T.DESCRIPTION Like ''' || p_persp_description || ''' ';
909 END IF;
910 l_sql_statement := l_sql_statement ||
911 l_where_clause || 'ORDER BY T.PERSPECTIVE_NAME ';
912 l_sql_statement2 := l_sql_statement2 ||
913 l_where_clause;
914 --
915 --dbms_output.put_line('sql stmt = '|| substr(l_sql_statement, 1, 80));
916 --dbms_output.put_line(substr(l_sql_statement, 81, 80));
917 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
918 FND_MESSAGE.Set_name('AMV','AMV_API_DEBUG_MESSAGE');
919 FND_MESSAGE.Set_Token('ROW', l_sql_statement);
920 FND_MSG_PUB.Add;
921 --
922 FND_MESSAGE.Set_name('AMV','AMV_API_DEBUG_MESSAGE');
923 FND_MESSAGE.Set_Token('ROW', l_sql_statement2);
924 FND_MSG_PUB.Add;
925 END IF;
926 --Execute the SQL statements to get the total count:
927 IF (p_subset_request_obj.return_total_count_flag = FND_API.G_TRUE) THEN
928 OPEN l_cursor FOR l_sql_statement2;
929 FETCH l_cursor INTO l_total_record_count;
930 CLOSE l_cursor;
931 END IF;
932 --Execute the SQL statements to get records
933 l_start_with := p_subset_request_obj.start_record_position;
934 x_perspective_obj_varray := AMV_PERSPECTIVE_OBJ_VARRAY();
935 OPEN l_cursor FOR l_sql_statement;
936 LOOP
937 FETCH l_cursor INTO
938 l_perspective_id,
939 l_object_version_number,
940 l_perspective_name,
941 l_persp_description,
942 l_language,
943 l_source_lang,
944 l_creation_date,
945 l_created_by,
946 l_last_update_date,
947 l_last_updated_by,
948 l_last_update_login;
949 EXIT WHEN l_cursor%NOTFOUND;
950 IF (l_start_with <= l_total_count AND
951 l_fetch_count < p_subset_request_obj.records_requested) THEN
952 l_fetch_count := l_fetch_count + 1;
953 x_perspective_obj_varray.extend;
954 x_perspective_obj_varray(l_fetch_count).perspective_id := l_perspective_id;
955 x_perspective_obj_varray(l_fetch_count).object_version_number := l_object_version_number;
956 x_perspective_obj_varray(l_fetch_count).perspective_name := l_perspective_name;
957 x_perspective_obj_varray(l_fetch_count).description := l_persp_description;
958 x_perspective_obj_varray(l_fetch_count).language := l_language;
959 x_perspective_obj_varray(l_fetch_count).source_lang := l_source_lang;
960 x_perspective_obj_varray(l_fetch_count).creation_date := l_creation_date;
961 x_perspective_obj_varray(l_fetch_count).created_by := l_created_by;
962 x_perspective_obj_varray(l_fetch_count).last_update_date := l_last_update_date;
963 x_perspective_obj_varray(l_fetch_count).last_updated_by := l_last_updated_by;
964 x_perspective_obj_varray(l_fetch_count).last_update_login := l_last_update_login;
965 /*
966 x_perspective_obj_varray(l_fetch_count) := amv_perspective_obj_type
967 (
968 l_perspective_id,
969 l_object_version_number,
970 l_perspective_name,
971 l_persp_description,
972 l_language,
973 l_source_lang,
974 l_creation_date,
975 l_created_by,
976 l_last_update_date,
977 l_last_updated_by,
978 l_last_update_login
979 );
980 */
981 END IF;
982 IF (l_fetch_count >= p_subset_request_obj.records_requested) THEN
983 exit;
984 END IF;
985 l_total_count := l_total_count + 1;
986 END LOOP;
987 CLOSE l_cursor;
988 x_subset_return_obj.returned_record_count := l_fetch_count;
989 x_subset_return_obj.next_record_position := p_subset_request_obj.start_record_position + l_fetch_count;
990 x_subset_return_obj.total_record_count := l_total_record_count;
991
992 /*
993 x_subset_return_obj := AMV_RETURN_OBJ_TYPE
994 (
995 l_fetch_count,
996 p_subset_request_obj.start_record_position + l_fetch_count,
997 l_total_record_count
998 );
999 */
1000 --Standard call to get message count and if count=1, get the message
1001 FND_MSG_PUB.Count_And_Get (
1002 p_encoded => FND_API.G_FALSE,
1003 p_count => x_msg_count,
1004 p_data => x_msg_data
1005 );
1006 EXCEPTION
1007 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1008 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1009 -- Standard call to get message count and if count=1, get the message
1010 FND_MSG_PUB.Count_And_Get (
1011 p_encoded => FND_API.G_FALSE,
1012 p_count => x_msg_count,
1013 p_data => x_msg_data
1014 );
1015 WHEN FND_API.G_EXC_ERROR THEN
1016 x_return_status := FND_API.G_RET_STS_ERROR;
1017 -- Standard call to get message count and if count=1, get the message
1018 FND_MSG_PUB.Count_And_Get (
1019 p_encoded => FND_API.G_FALSE,
1020 p_count => x_msg_count,
1021 p_data => x_msg_data
1022 );
1023 WHEN OTHERS THEN
1024 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1025 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1026 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1027 END IF;
1028 -- Standard call to get message count and if count=1, get the message
1029 FND_MSG_PUB.Count_And_Get (
1030 p_encoded => FND_API.G_FALSE,
1031 p_count => x_msg_count,
1032 p_data => x_msg_data
1033 );
1034 END Find_Perspective;
1035 --
1036 --------------------------------------------------------------------------------
1037 --------------------------------------------------------------------------------
1038 PROCEDURE Add_ItemPersps
1039 (
1040 p_api_version IN NUMBER,
1041 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1042 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1043 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1044 x_return_status OUT NOCOPY VARCHAR2,
1045 x_msg_count OUT NOCOPY NUMBER,
1046 x_msg_data OUT NOCOPY VARCHAR2,
1047 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1048 p_item_id IN NUMBER,
1049 p_perspective_array IN AMV_NUMBER_VARRAY_TYPE
1050 ) IS
1051 l_api_name CONSTANT VARCHAR2(30) := 'Add_ItemPersps';
1052 l_api_version CONSTANT NUMBER := 1.0;
1053 l_resource_id NUMBER := -1;
1054 l_current_user_id NUMBER := -1;
1055 l_current_login_id NUMBER := -1;
1056 l_current_user_status VARCHAR2(80);
1057 --
1058 l_admin_flag VARCHAR2(1);
1059 l_perspective_id NUMBER;
1060 l_count NUMBER;
1061 l_temp_number NUMBER;
1062 l_date DATE;
1063 --
1064 CURSOR Check_ItemPerspectives_csr (p_perspectiv_id IN NUMBER) is
1065 Select
1066 perspective_id
1067 From amv_i_item_perspectives
1068 Where item_id = p_item_id
1069 And perspective_id = p_perspectiv_id;
1070 --
1071 CURSOR Get_IDandDate_csr is
1072 Select amv_i_item_perspectives_s.nextval, sysdate
1073 From Dual;
1074 --
1075 BEGIN
1076 -- Standard call to check for call compatibility.
1077 SAVEPOINT Add_ItemPersps_Pvt;
1078 IF NOT FND_API.Compatible_API_Call (
1079 l_api_version,
1080 p_api_version,
1081 l_api_name,
1082 G_PKG_NAME) THEN
1083 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1084 END IF;
1085 --Initialize message list if p_init_msg_list is TRUE.
1086 IF FND_API.To_Boolean (p_init_msg_list) THEN
1087 FND_MSG_PUB.initialize;
1088 END IF;
1089 -- Initialize API return status to success
1090 x_return_status := FND_API.G_RET_STS_SUCCESS;
1091 --
1092 -- Get the current (login) user id.
1093 AMV_UTILITY_PVT.Get_UserInfo(
1094 x_resource_id => l_resource_id,
1095 x_user_id => l_current_user_id,
1096 x_login_id => l_current_login_id,
1097 x_user_status => l_current_user_status
1098 );
1099 IF (p_check_login_user = FND_API.G_TRUE) THEN
1100 -- Check if user is login and has the required privilege.
1101 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1102 -- User is not login.
1103 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1104 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1105 FND_MSG_PUB.Add;
1106 END IF;
1107 RAISE FND_API.G_EXC_ERROR;
1108 END IF;
1109 AMV_USER_PVT.Is_Administrator
1110 (
1111 p_api_version => 1.0,
1112 x_return_status => x_return_status,
1113 x_msg_count => x_msg_count,
1114 x_msg_data => x_msg_data,
1115 p_check_login_user => FND_API.G_FALSE,
1116 p_resource_id => l_resource_id,
1117 x_result_flag => l_admin_flag
1118 );
1119 IF (l_admin_flag <> FND_API.G_TRUE) THEN
1120 -- User is not an administrator.
1121 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1122 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
1123 FND_MSG_PUB.Add;
1124 END IF;
1125 RAISE FND_API.G_EXC_ERROR;
1126 END IF;
1127 END IF;
1128 -- This is to make database happy
1129 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1130 l_current_login_id := -1;
1131 l_current_user_id := -1;
1132 END IF;
1133 -- Check if item id is valid.
1134 IF (AMV_UTILITY_PVT.Is_ItemIdValid(p_item_id) <> TRUE) THEN
1135 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1136 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
1137 FND_MESSAGE.Set_Token('RECORD', 'AMV_ITEM_TK', TRUE);
1138 FND_MESSAGE.Set_Token('ID', to_char(p_item_id));
1139 FND_MSG_PUB.Add;
1140 END IF;
1141 RAISE FND_API.G_EXC_ERROR;
1142 END IF;
1143 l_count := p_perspective_array.count;
1144 FOR i IN 1..l_count LOOP
1145 l_perspective_id := p_perspective_array(i);
1146 OPEN Check_ItemPerspectives_csr(l_perspective_id);
1147 FETCH Check_ItemPerspectives_csr INTO l_temp_number;
1148 IF (Check_ItemPerspectives_csr%FOUND) THEN
1149 CLOSE Check_ItemPerspectives_csr;
1150 x_return_status := FND_API.G_RET_STS_ERROR;
1151 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1152 FND_MESSAGE.Set_name('AMV','AMV_ENTITY_HAS_ATTR');
1153 FND_MESSAGE.Set_Token('ENTITY', 'AMV_ITEM_TK', TRUE);
1154 FND_MESSAGE.Set_Token('ENTID', to_char(p_item_id));
1155 FND_MESSAGE.Set_Token('ATTRIBUTE', 'AMV_PERSPECTIVE_TK', TRUE);
1156 FND_MESSAGE.Set_Token('ATTRID', to_char(l_perspective_id));
1157 FND_MSG_PUB.Add;
1158 END IF;
1159 ELSE
1160 CLOSE Check_ItemPerspectives_csr;
1161 IF AMV_UTILITY_PVT.Is_PerspectiveIdValid(l_perspective_id) THEN
1162 OPEN Get_IDandDate_csr;
1163 FETCH Get_IDandDate_csr Into l_temp_number, l_date;
1164 CLOSE Get_IDandDate_csr;
1165 Insert Into amv_i_item_perspectives
1166 (
1167 ITEM_PERSPECTIVE_ID,
1168 OBJECT_VERSION_NUMBER,
1169 LAST_UPDATE_DATE,
1170 LAST_UPDATED_BY,
1171 CREATION_DATE,
1172 CREATED_BY,
1173 LAST_UPDATE_LOGIN,
1174 ITEM_ID,
1175 PERSPECTIVE_ID
1176 ) VALUES
1177 (
1178 l_temp_number,
1179 1,
1180 l_date,
1181 l_current_user_id,
1182 l_date,
1183 l_current_user_id,
1184 l_current_login_id,
1185 p_item_id,
1186 l_perspective_id
1187 );
1188 ELSE
1189 x_return_status := FND_API.G_RET_STS_ERROR;
1190 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1191 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
1192 FND_MESSAGE.Set_Token('RECORD', 'AMV_PERSPECTIVE_TK', TRUE);
1193 FND_MESSAGE.Set_Token('ID',
1194 to_char( nvl(l_perspective_id, -1) ) );
1195 FND_MSG_PUB.Add;
1196 END IF;
1197 END IF;
1198 END IF;
1199 END LOOP;
1200 --Standard check of commit
1201 IF FND_API.To_Boolean ( p_commit ) THEN
1202 COMMIT WORK;
1203 END IF;
1204 --Standard call to get message count and if count=1, get the message
1205 FND_MSG_PUB.Count_And_Get (
1206 p_encoded => FND_API.G_FALSE,
1207 p_count => x_msg_count,
1208 p_data => x_msg_data
1209 );
1210 EXCEPTION
1211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1212 ROLLBACK TO Add_ItemPersps_Pvt;
1213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1214 -- Standard call to get message count and if count=1, get the message
1215 FND_MSG_PUB.Count_And_Get (
1216 p_encoded => FND_API.G_FALSE,
1217 p_count => x_msg_count,
1218 p_data => x_msg_data
1219 );
1220 WHEN FND_API.G_EXC_ERROR THEN
1221 ROLLBACK TO Add_ItemPersps_Pvt;
1222 x_return_status := FND_API.G_RET_STS_ERROR;
1223 -- Standard call to get message count and if count=1, get the message
1224 FND_MSG_PUB.Count_And_Get (
1225 p_encoded => FND_API.G_FALSE,
1226 p_count => x_msg_count,
1227 p_data => x_msg_data
1228 );
1229 WHEN OTHERS THEN
1230 ROLLBACK TO Add_ItemPersps_Pvt;
1231 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1232 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1233 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1234 END IF;
1235 -- Standard call to get message count and if count=1, get the message
1236 FND_MSG_PUB.Count_And_Get (
1237 p_encoded => FND_API.G_FALSE,
1238 p_count => x_msg_count,
1239 p_data => x_msg_data
1240 );
1241 --
1242 END Add_ItemPersps;
1243 --------------------------------------------------------------------------------
1244 PROCEDURE Add_ItemPersps
1245 (
1246 p_api_version IN NUMBER,
1247 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1248 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1249 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1250 x_return_status OUT NOCOPY VARCHAR2,
1251 x_msg_count OUT NOCOPY NUMBER,
1252 x_msg_data OUT NOCOPY VARCHAR2,
1253 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1254 p_item_id IN NUMBER,
1255 p_perspective_id IN NUMBER
1256 ) IS
1257 l_api_name CONSTANT VARCHAR2(30) := 'Add_ItemPersps';
1258 l_api_version CONSTANT NUMBER := 1.0;
1259 l_resource_id NUMBER := -1;
1260 l_current_user_id NUMBER := -1;
1261 l_current_login_id NUMBER := -1;
1262 l_current_user_status VARCHAR2(80);
1263 --
1264 l_admin_flag VARCHAR2(1);
1265 l_item_persp NUMBER;
1266 l_date DATE;
1267 --
1268 CURSOR Check_ItemPerspectives_csr is
1269 Select
1270 perspective_id
1271 From amv_i_item_perspectives
1272 Where item_id = p_item_id
1273 And perspective_id = p_perspective_id;
1274 --
1275 CURSOR Get_IDandDate_csr is
1276 Select amv_i_item_perspectives_s.nextval, sysdate
1277 From Dual;
1278 --
1279 BEGIN
1280 -- Standard call to check for call compatibility.
1281 SAVEPOINT Add_ItemPersps_Pvt;
1282 IF NOT FND_API.Compatible_API_Call (
1283 l_api_version,
1284 p_api_version,
1285 l_api_name,
1286 G_PKG_NAME) THEN
1287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1288 END IF;
1289 --Initialize message list if p_init_msg_list is TRUE.
1290 IF FND_API.To_Boolean (p_init_msg_list) THEN
1291 FND_MSG_PUB.initialize;
1292 END IF;
1293 -- Initialize API return status to success
1294 x_return_status := FND_API.G_RET_STS_SUCCESS;
1295 -- Get the current (login) user id.
1296 AMV_UTILITY_PVT.Get_UserInfo(
1297 x_resource_id => l_resource_id,
1298 x_user_id => l_current_user_id,
1299 x_login_id => l_current_login_id,
1300 x_user_status => l_current_user_status
1301 );
1302 IF (p_check_login_user = FND_API.G_TRUE) THEN
1303 -- Check if user is login and has the required privilege.
1304 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1305 -- User is not login.
1306 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1307 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1308 FND_MSG_PUB.Add;
1309 END IF;
1310 RAISE FND_API.G_EXC_ERROR;
1311 END IF;
1312 AMV_USER_PVT.Is_Administrator
1313 (
1314 p_api_version => 1.0,
1315 x_return_status => x_return_status,
1316 x_msg_count => x_msg_count,
1317 x_msg_data => x_msg_data,
1318 p_check_login_user => FND_API.G_FALSE,
1319 p_resource_id => l_resource_id,
1320 x_result_flag => l_admin_flag
1321 );
1322 IF (l_admin_flag <> FND_API.G_TRUE) THEN
1323 -- User is not an administrator.
1324 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1325 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
1326 FND_MSG_PUB.Add;
1327 END IF;
1328 RAISE FND_API.G_EXC_ERROR;
1329 END IF;
1330 END IF;
1331 -- This is to make database happy
1332 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1333 l_current_login_id := -1;
1334 l_current_user_id := -1;
1335 END IF;
1336 -- Check if item id is valid.
1337 IF (AMV_UTILITY_PVT.Is_ItemIdValid(p_item_id) <> TRUE) THEN
1338 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1339 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
1340 FND_MESSAGE.Set_Token('RECORD', 'AMV_ITEM_TK', TRUE);
1341 FND_MESSAGE.Set_Token('ID', to_char(p_item_id));
1342 FND_MSG_PUB.Add;
1343 END IF;
1344 RAISE FND_API.G_EXC_ERROR;
1345 END IF;
1346 IF (AMV_UTILITY_PVT.Is_PerspectiveIdValid(p_perspective_id) <> TRUE) THEN
1347 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1348 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
1349 FND_MESSAGE.Set_Token('RECORD', 'AMV_PERSPECTIVE_TK', TRUE);
1350 FND_MESSAGE.Set_Token('ID', to_char(p_perspective_id));
1351 FND_MSG_PUB.Add;
1352 END IF;
1353 RAISE FND_API.G_EXC_ERROR;
1354 END IF;
1355 OPEN Check_ItemPerspectives_csr;
1356 FETCH Check_ItemPerspectives_csr INTO l_item_persp;
1357 IF (Check_ItemPerspectives_csr%FOUND) THEN
1358 CLOSE Check_ItemPerspectives_csr;
1359 x_return_status := FND_API.G_RET_STS_ERROR;
1360 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1361 FND_MESSAGE.Set_name('AMV','AMV_ENTITY_HAS_ATTR');
1362 FND_MESSAGE.Set_Token('ENTITY', 'AMV_ITEM_TK', TRUE);
1363 FND_MESSAGE.Set_Token('ENTID', to_char(p_item_id));
1364 FND_MESSAGE.Set_Token('ATTRIBUTE', 'AMV_PERSPECTIVE_TK', TRUE);
1365 FND_MESSAGE.Set_Token('ATTRID', to_char(p_perspective_id));
1366 FND_MSG_PUB.Add;
1367 END IF;
1368 ELSE
1369 CLOSE Check_ItemPerspectives_csr;
1370 OPEN Get_IDandDate_csr;
1371 FETCH Get_IDandDate_csr Into l_item_persp, l_date;
1372 CLOSE Get_IDandDate_csr;
1373 Insert Into amv_i_item_perspectives
1374 (
1375 ITEM_PERSPECTIVE_ID,
1376 LAST_UPDATE_DATE,
1377 LAST_UPDATED_BY,
1378 CREATION_DATE,
1379 CREATED_BY,
1380 LAST_UPDATE_LOGIN,
1381 ITEM_ID,
1382 PERSPECTIVE_ID
1383 ) VALUES
1384 (
1385 l_item_persp,
1386 l_date,
1387 l_current_user_id,
1388 l_date,
1389 l_current_user_id,
1390 l_current_login_id,
1391 p_item_id,
1392 p_perspective_id
1393 );
1394 END IF;
1395 --Standard check of commit
1396 IF FND_API.To_Boolean ( p_commit ) THEN
1397 COMMIT WORK;
1398 END IF;
1399 --Standard call to get message count and if count=1, get the message
1400 FND_MSG_PUB.Count_And_Get (
1401 p_encoded => FND_API.G_FALSE,
1402 p_count => x_msg_count,
1403 p_data => x_msg_data
1404 );
1405 EXCEPTION
1406 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1407 ROLLBACK TO Add_ItemPersps_Pvt;
1408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1409 -- Standard call to get message count and if count=1, get the message
1410 FND_MSG_PUB.Count_And_Get (
1411 p_encoded => FND_API.G_FALSE,
1412 p_count => x_msg_count,
1413 p_data => x_msg_data
1414 );
1415 WHEN FND_API.G_EXC_ERROR THEN
1416 ROLLBACK TO Add_ItemPersps_Pvt;
1417 x_return_status := FND_API.G_RET_STS_ERROR;
1418 -- Standard call to get message count and if count=1, get the message
1419 FND_MSG_PUB.Count_And_Get (
1420 p_encoded => FND_API.G_FALSE,
1421 p_count => x_msg_count,
1422 p_data => x_msg_data
1423 );
1424 WHEN OTHERS THEN
1425 ROLLBACK TO Add_ItemPersps_Pvt;
1426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1427 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1428 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1429 END IF;
1430 -- Standard call to get message count and if count=1, get the message
1431 FND_MSG_PUB.Count_And_Get (
1432 p_encoded => FND_API.G_FALSE,
1433 p_count => x_msg_count,
1434 p_data => x_msg_data
1435 );
1436 --
1437 END Add_ItemPersps;
1438 --------------------------------------------------------------------------------
1439 PROCEDURE Delete_ItemPersps
1440 (
1441 p_api_version IN NUMBER,
1442 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1443 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1444 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1445 x_return_status OUT NOCOPY VARCHAR2,
1446 x_msg_count OUT NOCOPY NUMBER,
1447 x_msg_data OUT NOCOPY VARCHAR2,
1448 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1449 p_item_id IN NUMBER,
1450 p_perspective_array IN AMV_NUMBER_VARRAY_TYPE
1451 ) IS
1452 l_api_name CONSTANT VARCHAR2(30) := 'Delete_ItemPersps';
1453 l_api_version CONSTANT NUMBER := 1.0;
1454 l_resource_id NUMBER := -1;
1455 l_current_user_id NUMBER := -1;
1456 l_current_login_id NUMBER := -1;
1457 l_current_user_status VARCHAR2(80);
1458 --
1459 l_admin_flag VARCHAR2(1);
1460 l_perspective_id NUMBER;
1461 l_count NUMBER;
1462 l_temp_number NUMBER;
1463 l_date DATE;
1464 --
1465 --
1466 CURSOR Check_ItemPerspectives_csr (p_perspectiv_id IN NUMBER) is
1467 Select
1468 perspective_id
1469 From amv_i_item_perspectives
1470 Where item_id = p_item_id
1471 And perspective_id = p_perspectiv_id;
1472 --
1473 BEGIN
1474 -- Standard call to check for call compatibility.
1475 SAVEPOINT Delete_ItemPersps_Pvt;
1476 IF NOT FND_API.Compatible_API_Call (
1477 l_api_version,
1478 p_api_version,
1479 l_api_name,
1480 G_PKG_NAME) THEN
1481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1482 END IF;
1483 --Initialize message list if p_init_msg_list is TRUE.
1484 IF FND_API.To_Boolean (p_init_msg_list) THEN
1485 FND_MSG_PUB.initialize;
1486 END IF;
1487 -- Initialize API return status to success
1488 x_return_status := FND_API.G_RET_STS_SUCCESS;
1489 -- Get the current (login) user id.
1490 AMV_UTILITY_PVT.Get_UserInfo(
1491 x_resource_id => l_resource_id,
1492 x_user_id => l_current_user_id,
1493 x_login_id => l_current_login_id,
1494 x_user_status => l_current_user_status
1495 );
1496 IF (p_check_login_user = FND_API.G_TRUE) THEN
1497 -- Check if user is login and has the required privilege.
1498 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1499 -- User is not login.
1500 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1501 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1502 FND_MSG_PUB.Add;
1503 END IF;
1504 RAISE FND_API.G_EXC_ERROR;
1505 END IF;
1506 AMV_USER_PVT.Is_Administrator
1507 (
1508 p_api_version => 1.0,
1509 x_return_status => x_return_status,
1510 x_msg_count => x_msg_count,
1511 x_msg_data => x_msg_data,
1512 p_check_login_user => FND_API.G_FALSE,
1513 p_resource_id => l_resource_id,
1514 x_result_flag => l_admin_flag
1515 );
1516 IF (l_admin_flag <> FND_API.G_TRUE) THEN
1517 -- User is not an administrator.
1518 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1519 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
1520 FND_MSG_PUB.Add;
1521 END IF;
1522 RAISE FND_API.G_EXC_ERROR;
1523 END IF;
1524 END IF;
1525 -- This is to make database happy
1526 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1527 l_current_login_id := -1;
1528 l_current_user_id := -1;
1529 END IF;
1530 -- Check if item id is valid.
1531 IF (AMV_UTILITY_PVT.Is_ItemIdValid(p_item_id) <> TRUE) THEN
1532 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1533 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
1534 FND_MESSAGE.Set_Token('RECORD', 'AMV_ITEM_TK', TRUE);
1535 FND_MESSAGE.Set_Token('ID', to_char(p_item_id));
1536 FND_MSG_PUB.Add;
1537 END IF;
1538 RAISE FND_API.G_EXC_ERROR;
1539 END IF;
1540 l_count := p_perspective_array.count;
1541 FOR i IN 1..l_count LOOP
1542 l_perspective_id := p_perspective_array(i);
1543 OPEN Check_ItemPerspectives_csr(l_perspective_id);
1544 FETCH Check_ItemPerspectives_csr INTO l_temp_number;
1545 IF (Check_ItemPerspectives_csr%NOTFOUND) THEN
1546 CLOSE Check_ItemPerspectives_csr;
1547 x_return_status := FND_API.G_RET_STS_ERROR;
1548 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1549 FND_MESSAGE.Set_name('AMV','AMV_ENTITY_HAS_NOT_ATTR');
1550 FND_MESSAGE.Set_Token('ENTITY', 'AMV_ITEM_TK', TRUE);
1551 FND_MESSAGE.Set_Token('ENTID', to_char(p_item_id));
1552 FND_MESSAGE.Set_Token('ATTRIBUTE', 'AMV_PERSPECTIVE_TK', TRUE);
1553 FND_MESSAGE.Set_Token('ATTRID', to_char(l_perspective_id));
1554 FND_MSG_PUB.Add;
1555 END IF;
1556 ELSE
1557 CLOSE Check_ItemPerspectives_csr;
1558 Delete from amv_i_item_perspectives
1559 Where item_id = p_item_id
1560 And perspective_id = l_perspective_id;
1561 END IF;
1562 END LOOP;
1563 --Standard check of commit
1564 IF FND_API.To_Boolean ( p_commit ) THEN
1565 COMMIT WORK;
1566 END IF;
1567 --Standard call to get message count and if count=1, get the message
1568 FND_MSG_PUB.Count_And_Get (
1569 p_encoded => FND_API.G_FALSE,
1570 p_count => x_msg_count,
1571 p_data => x_msg_data
1572 );
1573 EXCEPTION
1574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1575 ROLLBACK TO Delete_ItemPersps_Pvt;
1576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1577 -- Standard call to get message count and if count=1, get the message
1578 FND_MSG_PUB.Count_And_Get (
1579 p_encoded => FND_API.G_FALSE,
1580 p_count => x_msg_count,
1581 p_data => x_msg_data
1582 );
1583 WHEN FND_API.G_EXC_ERROR THEN
1584 ROLLBACK TO Delete_ItemPersps_Pvt;
1585 x_return_status := FND_API.G_RET_STS_ERROR;
1586 -- Standard call to get message count and if count=1, get the message
1587 FND_MSG_PUB.Count_And_Get (
1588 p_encoded => FND_API.G_FALSE,
1589 p_count => x_msg_count,
1590 p_data => x_msg_data
1591 );
1592 WHEN OTHERS THEN
1593 ROLLBACK TO Delete_ItemPersps_Pvt;
1594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1595 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1596 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1597 END IF;
1598 -- Standard call to get message count and if count=1, get the message
1599 FND_MSG_PUB.Count_And_Get (
1600 p_encoded => FND_API.G_FALSE,
1601 p_count => x_msg_count,
1602 p_data => x_msg_data
1603 );
1604 --
1605 end Delete_ItemPersps;
1606 --
1607 --------------------------------------------------------------------------------
1608 PROCEDURE Delete_ItemPersps
1609 (
1610 p_api_version IN NUMBER,
1611 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1612 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1613 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1614 x_return_status OUT NOCOPY VARCHAR2,
1615 x_msg_count OUT NOCOPY NUMBER,
1616 x_msg_data OUT NOCOPY VARCHAR2,
1617 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1618 p_item_id IN NUMBER,
1619 p_perspective_id IN NUMBER := FND_API.G_MISS_NUM
1620 ) IS
1621 l_api_name CONSTANT VARCHAR2(30) := 'Delete_ItemPersps';
1622 l_api_version CONSTANT NUMBER := 1.0;
1623 l_resource_id NUMBER := -1;
1624 l_current_user_id NUMBER := -1;
1625 l_current_login_id NUMBER := -1;
1626 l_current_user_status VARCHAR2(80);
1627 --
1628 l_admin_flag VARCHAR2(1);
1629 l_temp_number NUMBER;
1630 --
1631 CURSOR Check_ItemPerspectives_csr IS
1632 Select
1633 perspective_id
1634 From amv_i_item_perspectives
1635 Where item_id = p_item_id
1636 And perspective_id = p_perspective_id;
1637 --
1638 BEGIN
1639 -- Standard call to check for call compatibility.
1640 SAVEPOINT Delete_ItemPersps_Pvt;
1641 IF NOT FND_API.Compatible_API_Call (
1642 l_api_version,
1643 p_api_version,
1644 l_api_name,
1645 G_PKG_NAME) THEN
1646 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1647 END IF;
1648 --Initialize message list if p_init_msg_list is TRUE.
1649 IF FND_API.To_Boolean (p_init_msg_list) THEN
1650 FND_MSG_PUB.initialize;
1651 END IF;
1652 -- Initialize API return status to success
1653 x_return_status := FND_API.G_RET_STS_SUCCESS;
1654 -- Get the current (login) user id.
1655 AMV_UTILITY_PVT.Get_UserInfo(
1656 x_resource_id => l_resource_id,
1657 x_user_id => l_current_user_id,
1658 x_login_id => l_current_login_id,
1659 x_user_status => l_current_user_status
1660 );
1661 IF (p_check_login_user = FND_API.G_TRUE) THEN
1662 -- Check if user is login and has the required privilege.
1663 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1664 -- User is not login.
1665 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1666 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1667 FND_MSG_PUB.Add;
1668 END IF;
1669 RAISE FND_API.G_EXC_ERROR;
1670 END IF;
1671 AMV_USER_PVT.Is_Administrator
1672 (
1673 p_api_version => 1.0,
1674 x_return_status => x_return_status,
1675 x_msg_count => x_msg_count,
1676 x_msg_data => x_msg_data,
1677 p_check_login_user => FND_API.G_FALSE,
1678 p_resource_id => l_resource_id,
1679 x_result_flag => l_admin_flag
1680 );
1681 IF (l_admin_flag <> FND_API.G_TRUE) THEN
1682 -- User is not an administrator.
1683 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1684 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
1685 FND_MSG_PUB.Add;
1686 END IF;
1687 RAISE FND_API.G_EXC_ERROR;
1688 END IF;
1689 END IF;
1690 -- This is to make database happy
1691 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1692 l_current_login_id := -1;
1693 l_current_user_id := -1;
1694 END IF;
1695 -- Check if item id is valid.
1696 IF (AMV_UTILITY_PVT.Is_ItemIdValid(p_item_id) <> TRUE) THEN
1697 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1698 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
1699 FND_MESSAGE.Set_Token('RECORD', 'AMV_ITEM_TK', TRUE);
1700 FND_MESSAGE.Set_Token('ID', to_char(p_item_id));
1701 FND_MSG_PUB.Add;
1702 END IF;
1703 RAISE FND_API.G_EXC_ERROR;
1704 END IF;
1705 IF (p_perspective_id <> FND_API.G_MISS_NUM) THEN
1706 OPEN Check_ItemPerspectives_csr;
1707 FETCH Check_ItemPerspectives_csr INTO l_temp_number;
1708 IF (Check_ItemPerspectives_csr%NOTFOUND) THEN
1709 CLOSE Check_ItemPerspectives_csr;
1710 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1711 FND_MESSAGE.Set_name('AMV','AMV_ENTITY_HAS_NOT_ATTR');
1712 FND_MESSAGE.Set_Token('ENTITY', 'AMV_ITEM_TK', TRUE);
1713 FND_MESSAGE.Set_Token('ENTID', to_char(p_item_id));
1714 FND_MESSAGE.Set_Token('ATTRIBUTE', 'AMV_PERSPECTIVE_TK', TRUE);
1715 FND_MESSAGE.Set_Token('ATTRID', to_char(p_perspective_id));
1716 FND_MSG_PUB.Add;
1717 END IF;
1718 RAISE FND_API.G_EXC_ERROR;
1719 ELSE
1720 CLOSE Check_ItemPerspectives_csr;
1721 Delete from amv_i_item_perspectives
1722 Where item_id = p_item_id
1723 And perspective_id = p_perspective_id;
1724 END IF;
1725 ELSE
1726 -- p_perspective_id is not specified,
1727 -- caller wants to delete all the perspectives of the item.
1728 Delete from amv_i_item_perspectives
1729 Where item_id = p_item_id;
1730 END IF;
1731 --Standard check of commit
1732 IF FND_API.To_Boolean ( p_commit ) THEN
1733 COMMIT WORK;
1734 END IF;
1735 --Standard call to get message count and if count=1, get the message
1736 FND_MSG_PUB.Count_And_Get (
1737 p_encoded => FND_API.G_FALSE,
1738 p_count => x_msg_count,
1739 p_data => x_msg_data
1740 );
1741 EXCEPTION
1742 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1743 ROLLBACK TO Delete_ItemPersps_Pvt;
1744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1745 -- Standard call to get message count and if count=1, get the message
1746 FND_MSG_PUB.Count_And_Get (
1747 p_encoded => FND_API.G_FALSE,
1748 p_count => x_msg_count,
1749 p_data => x_msg_data
1750 );
1751 WHEN FND_API.G_EXC_ERROR THEN
1752 ROLLBACK TO Delete_ItemPersps_Pvt;
1753 x_return_status := FND_API.G_RET_STS_ERROR;
1754 -- Standard call to get message count and if count=1, get the message
1755 FND_MSG_PUB.Count_And_Get (
1756 p_encoded => FND_API.G_FALSE,
1757 p_count => x_msg_count,
1758 p_data => x_msg_data
1759 );
1760 WHEN OTHERS THEN
1761 ROLLBACK TO Delete_ItemPersps_Pvt;
1762 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1763 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1764 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1765 END IF;
1766 -- Standard call to get message count and if count=1, get the message
1767 FND_MSG_PUB.Count_And_Get (
1768 p_encoded => FND_API.G_FALSE,
1769 p_count => x_msg_count,
1770 p_data => x_msg_data
1771 );
1772 --
1773 end Delete_ItemPersps;
1774 --
1775 --------------------------------------------------------------------------------
1776 PROCEDURE Update_ItemPersps
1777 (
1778 p_api_version IN NUMBER,
1779 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1780 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1781 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1782 x_return_status OUT NOCOPY VARCHAR2,
1783 x_msg_count OUT NOCOPY NUMBER,
1784 x_msg_data OUT NOCOPY VARCHAR2,
1785 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1786 p_item_id IN NUMBER,
1787 p_perspective_array IN AMV_NUMBER_VARRAY_TYPE
1788 ) is
1789 begin
1790 Delete_ItemPersps
1791 (
1792 p_api_version => p_api_version,
1793 p_init_msg_list => p_init_msg_list,
1794 -- p_commit => p_commit,
1795 p_validation_level => p_validation_level,
1796 x_return_status => x_return_status,
1797 x_msg_count => x_msg_count,
1798 x_msg_data => x_msg_data,
1799 p_check_login_user => p_check_login_user,
1800 p_item_id => p_item_id
1801 );
1802 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1803 Add_ItemPersps
1804 (
1805 p_api_version => p_api_version,
1806 -- p_init_msg_list => p_init_msg_list,
1807 p_commit => p_commit,
1808 p_validation_level => p_validation_level,
1809 x_return_status => x_return_status,
1810 x_msg_count => x_msg_count,
1811 x_msg_data => x_msg_data,
1812 p_check_login_user => FND_API.G_FALSE,
1813 p_item_id => p_item_id,
1814 p_perspective_array => p_perspective_array
1815 );
1816 END IF;
1817 end Update_ItemPersps;
1818 --
1819 --------------------------------------------------------------------------------
1820 PROCEDURE Get_ItemPersps
1821 (
1822 p_api_version IN NUMBER,
1823 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1824 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1825 x_return_status OUT NOCOPY VARCHAR2,
1826 x_msg_count OUT NOCOPY NUMBER,
1827 x_msg_data OUT NOCOPY VARCHAR2,
1828 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1829 p_item_id IN NUMBER,
1830 x_perspective_obj_varray OUT NOCOPY AMV_PERSPECTIVE_OBJ_VARRAY
1831 ) IS
1832 l_api_name CONSTANT VARCHAR2(30) := 'Get_ItemPersps';
1833 l_api_version CONSTANT NUMBER := 1.0;
1834 l_resource_id NUMBER := -1;
1835 l_current_user_id NUMBER := -1;
1836 l_current_login_id NUMBER := -1;
1837 l_current_user_status VARCHAR2(80);
1838 --
1839 CURSOR Get_ItemPerspective_csr IS
1840 Select
1841 B.PERSPECTIVE_ID,
1842 B.OBJECT_VERSION_NUMBER,
1843 T.PERSPECTIVE_NAME,
1844 T.DESCRIPTION,
1845 T.LANGUAGE,
1846 T.SOURCE_LANG,
1847 B.CREATION_DATE,
1848 B.CREATED_BY,
1849 B.LAST_UPDATE_DATE,
1850 B.LAST_UPDATED_BY,
1851 B.LAST_UPDATE_LOGIN
1852 From AMV_I_PERSPECTIVES_TL T, AMV_I_PERSPECTIVES_B B,
1853 AMV_I_ITEM_PERSPECTIVES I
1854 Where B.PERSPECTIVE_ID = I.PERSPECTIVE_ID
1855 And T.PERSPECTIVE_ID = I.PERSPECTIVE_ID
1856 And T.LANGUAGE = userenv('LANG')
1857 And I.ITEM_ID = p_item_id
1858 Order BY T.PERSPECTIVE_NAME;
1859 --
1860 l_perspective_rec Get_ItemPerspective_csr%ROWTYPE;
1861 l_fetch_count NUMBER := 0;
1862 --
1863 BEGIN
1864 -- Standard call to check for call compatibility.
1865 IF NOT FND_API.Compatible_API_Call (
1866 l_api_version,
1867 p_api_version,
1868 l_api_name,
1869 G_PKG_NAME) THEN
1870 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1871 END IF;
1872 --Initialize message list if p_init_msg_list is TRUE.
1873 IF FND_API.To_Boolean (p_init_msg_list) THEN
1874 FND_MSG_PUB.initialize;
1875 END IF;
1876 -- Initialize API return status to success
1877 x_return_status := FND_API.G_RET_STS_SUCCESS;
1878 -- Get the current (login) user id.
1879 AMV_UTILITY_PVT.Get_UserInfo(
1880 x_resource_id => l_resource_id,
1881 x_user_id => l_current_user_id,
1882 x_login_id => l_current_login_id,
1883 x_user_status => l_current_user_status
1884 );
1885 IF (p_check_login_user = FND_API.G_TRUE) THEN
1886 -- Check if user is login and has the required privilege.
1887 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1888 -- User is not login.
1889 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1890 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1891 FND_MSG_PUB.Add;
1892 END IF;
1893 RAISE FND_API.G_EXC_ERROR;
1894 END IF;
1895 END IF;
1896 -- This is to make database happy
1897 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1898 l_current_login_id := -1;
1899 l_current_user_id := -1;
1900 END IF;
1901 -- Check if item id is valid.
1902 IF (AMV_UTILITY_PVT.Is_ItemIdValid(p_item_id) <> TRUE) THEN
1903 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1904 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
1905 FND_MESSAGE.Set_Token('RECORD', 'AMV_ITEM_TK', TRUE);
1906 FND_MESSAGE.Set_Token('ID', to_char(p_item_id));
1907 FND_MSG_PUB.Add;
1908 END IF;
1909 RAISE FND_API.G_EXC_ERROR;
1910 END IF;
1911 --Execute the SQL statements to get records
1912 x_perspective_obj_varray := AMV_PERSPECTIVE_OBJ_VARRAY();
1913 FOR psp_rec in Get_ItemPerspective_csr LOOP
1914 l_fetch_count := l_fetch_count + 1;
1915 x_perspective_obj_varray.extend;
1916 x_perspective_obj_varray(l_fetch_count).perspective_id := psp_rec.perspective_id;
1917 x_perspective_obj_varray(l_fetch_count).object_version_number := psp_rec.object_version_number;
1918 x_perspective_obj_varray(l_fetch_count).perspective_name := psp_rec.perspective_name;
1919 x_perspective_obj_varray(l_fetch_count).description := psp_rec.description;
1920 x_perspective_obj_varray(l_fetch_count).language := psp_rec.language;
1921 x_perspective_obj_varray(l_fetch_count).source_lang := psp_rec.source_lang;
1922 x_perspective_obj_varray(l_fetch_count).creation_date := psp_rec.creation_date;
1923 x_perspective_obj_varray(l_fetch_count).created_by := psp_rec.created_by;
1924 x_perspective_obj_varray(l_fetch_count).last_update_date := psp_rec.last_update_date;
1925 x_perspective_obj_varray(l_fetch_count).last_updated_by := psp_rec.last_updated_by;
1926 x_perspective_obj_varray(l_fetch_count).last_update_login := psp_rec.last_update_login;
1927 /*
1928 x_perspective_obj_varray(l_fetch_count) := amv_perspective_obj_type
1929 (
1930 psp_rec.perspective_id,
1931 psp_rec.object_version_number,
1932 psp_rec.perspective_name,
1933 psp_rec.description,
1934 psp_rec.language,
1935 psp_rec.source_lang,
1936 psp_rec.creation_date,
1937 psp_rec.created_by,
1938 psp_rec.last_update_date,
1939 psp_rec.last_updated_by,
1940 psp_rec.last_update_login
1941 );
1942 */
1943 END LOOP;
1944 --Standard call to get message count and if count=1, get the message
1945 FND_MSG_PUB.Count_And_Get (
1946 p_encoded => FND_API.G_FALSE,
1947 p_count => x_msg_count,
1948 p_data => x_msg_data
1949 );
1950 EXCEPTION
1951 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1952 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1953 -- Standard call to get message count and if count=1, get the message
1954 FND_MSG_PUB.Count_And_Get (
1955 p_encoded => FND_API.G_FALSE,
1956 p_count => x_msg_count,
1957 p_data => x_msg_data
1958 );
1959 WHEN FND_API.G_EXC_ERROR THEN
1960 x_return_status := FND_API.G_RET_STS_ERROR;
1961 -- Standard call to get message count and if count=1, get the message
1962 FND_MSG_PUB.Count_And_Get (
1963 p_encoded => FND_API.G_FALSE,
1964 p_count => x_msg_count,
1965 p_data => x_msg_data
1966 );
1967 WHEN OTHERS THEN
1968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1969 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1970 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1971 END IF;
1972 -- Standard call to get message count and if count=1, get the message
1973 FND_MSG_PUB.Count_And_Get (
1974 p_encoded => FND_API.G_FALSE,
1975 p_count => x_msg_count,
1976 p_data => x_msg_data
1977 );
1978 --
1979 END Get_ItemPersps;
1980 --
1981 --------------------------------------------------------------------------------
1982 END amv_perspective_pvt;