[Home] [Help]
PACKAGE BODY: APPS.AMS_METRIC_TEMPLATE_PVT
Source
1 PACKAGE BODY Ams_Metric_Template_Pvt AS
2 /* $Header: amsvmthb.pls 120.0 2005/05/31 13:58:49 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Metric_Template_PVT
7 -- Purpose
8 --
9 -- History
10 -- 03/05/2002 dmvincen Created.
11 -- 08-Sep-2003 Sunkumar Bug#3130095 Metric Template UI Enh. 11510
12 -- 20-apr-2004 sunkumar Cannot create/update template header name
13 -- to contain phrases like 'AND'
14 -- 19-Jan-2005 dmvincen Bug4057287: Fixed many bugs see bug details.
15 --
16 -- NOTE
17 --
18 -- End of Comments
19 -- ===============================================================
20
21
22 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Metric_Template_PVT';
23 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvmthb.pls';
24
25 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
26 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
27 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
28
29 PROCEDURE Complete_metric_tpl_header_Rec (
30 p_ref_metric_tpl_header_rec IN metric_tpl_header_rec_type,
31 x_tar_metric_tpl_header_rec IN OUT NOCOPY metric_tpl_header_rec_type);
32
33 -- Hint: Primary key needs to be returned.
34 PROCEDURE Create_Metric_Template(
35 p_api_version_number IN NUMBER,
36 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
37 p_commit IN VARCHAR2 := FND_API.G_FALSE,
38 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
39
40 x_return_status OUT NOCOPY VARCHAR2,
41 x_msg_count OUT NOCOPY NUMBER,
42 x_msg_data OUT NOCOPY VARCHAR2,
43
44 p_metric_tpl_header_rec IN metric_tpl_header_rec_type := g_miss_metric_tpl_header_rec,
45 x_metric_tpl_header_id OUT NOCOPY NUMBER
46 )
47 IS
48 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Metric_Template';
49 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
50 l_return_status_full VARCHAR2(1);
51 l_object_version_number NUMBER := 1;
52 l_org_id NUMBER := FND_API.G_MISS_NUM;
53 l_METRIC_TPL_HEADER_ID NUMBER;
54 l_dummy NUMBER;
55 l_metric_tpl_header_rec metric_tpl_header_rec_type := p_metric_tpl_header_rec;
56
57 CURSOR c_id IS
58 SELECT AMS_MET_TPL_HEADERS_ALL_S.NEXTVAL
59 FROM dual;
60
61 CURSOR c_id_exists (l_id IN NUMBER) IS
62 SELECT 1
63 FROM AMS_MET_TPL_HEADERS_B
64 WHERE METRIC_TPL_HEADER_ID = l_id;
65
66 BEGIN
67 -- Standard Start of API savepoint
68 SAVEPOINT CREATE_Metric_Template_PVT;
69
70 -- Standard call to check for call compatibility.
71 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
72 p_api_version_number,
73 l_api_name,
74 G_PKG_NAME)
75 THEN
76 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
77 END IF;
78
79 -- Initialize message list if p_init_msg_list is set to TRUE.
80 IF FND_API.to_Boolean( p_init_msg_list )
81 THEN
82 FND_MSG_PUB.initialize;
83 END IF;
84
85 -- Debug Message
86 IF (AMS_DEBUG_HIGH_ON) THEN
87
88 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || 'start');
89 END IF;
90
91
92 -- Initialize API return status to SUCCESS
93 x_return_status := FND_API.G_RET_STS_SUCCESS;
94
95 -- Local variable initialization
96
97 IF l_metric_tpl_header_rec.METRIC_TPL_HEADER_ID IS NULL OR
98 l_metric_tpl_header_rec.METRIC_TPL_HEADER_ID = FND_API.g_miss_num THEN
99 LOOP
100 l_dummy := NULL;
101 OPEN c_id;
102 FETCH c_id INTO l_METRIC_TPL_HEADER_ID;
103 CLOSE c_id;
104
105 OPEN c_id_exists(l_METRIC_TPL_HEADER_ID);
106 FETCH c_id_exists INTO l_dummy;
107 CLOSE c_id_exists;
108 EXIT WHEN l_dummy IS NULL;
109 END LOOP;
110 l_metric_tpl_header_rec.metric_tpl_header_id := l_metric_tpl_header_id;
111 ELSE
112 l_metric_tpl_header_id := p_metric_tpl_header_rec.metric_tpl_header_id;
113 END IF;
114
115 -- =========================================================================
116 -- Validate Environment
117 -- =========================================================================
118
119 IF FND_GLOBAL.User_Id IS NULL
120 THEN
121 Ams_Utility_Pvt.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
122 RAISE FND_API.G_EXC_ERROR;
123 END IF;
124
125 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
126 THEN
127 -- Debug message
128 IF (AMS_DEBUG_HIGH_ON) THEN
129
130 Ams_Utility_Pvt.debug_message('Private API: Validate_Metric_Template');
131 END IF;
132
133 -- Invoke validation procedures
134 Validate_metric_template(
135 p_api_version_number => 1.0,
136 p_init_msg_list => FND_API.G_FALSE,
137 p_validation_level => p_validation_level,
138 p_validation_mode => JTF_PLSQL_API.g_create,
139 p_metric_tpl_header_rec => l_metric_tpl_header_rec,
140 x_return_status => x_return_status,
141 x_msg_count => x_msg_count,
142 x_msg_data => x_msg_data);
143 END IF;
144
145 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
146 RAISE FND_API.G_EXC_ERROR;
147 END IF;
148
149
150 -- Debug Message
151 IF (AMS_DEBUG_HIGH_ON) THEN
152
153 Ams_Utility_Pvt.debug_message( 'Private API: Calling create table handler');
154 END IF;
155
156 -- Invoke table handler(AMS_MET_TPL_HEADERS_B_PKG.Insert_Row)
157 Ams_Met_Tpl_Headers_B_Pkg.Insert_Row(
158 px_metric_tpl_header_id => l_metric_tpl_header_id,
159 p_last_update_date => SYSDATE,
160 p_last_updated_by => FND_GLOBAL.USER_ID,
161 p_creation_date => SYSDATE,
162 p_created_by => FND_GLOBAL.USER_ID,
163 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
164 px_object_version_number => l_object_version_number,
165 p_enabled_flag => l_metric_tpl_header_rec.enabled_flag,
166 p_application_id => l_metric_tpl_header_rec.application_id,
167 p_METRIC_TPL_HEADER_NAME => l_metric_tpl_header_rec.METRIC_TPL_HEADER_NAME,
168 p_DESCRIPTION => l_metric_tpl_header_rec.DESCRIPTION,
169 p_object_type => l_metric_tpl_header_rec.object_type,
170 p_association_type => l_metric_tpl_header_rec.association_type,
171 p_used_by_id => l_metric_tpl_header_rec.used_by_id,
172 p_used_by_code => l_metric_tpl_header_rec.used_by_code);
173
174 x_metric_tpl_header_id := l_metric_tpl_header_id;
175
176 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
177 RAISE FND_API.G_EXC_ERROR;
178 END IF;
179 --
180 -- End of API body
181 --
182
183 -- Standard check for p_commit
184 IF FND_API.to_Boolean( p_commit )
185 THEN
186 COMMIT WORK;
187 END IF;
188
189
190 -- Debug Message
191 IF (AMS_DEBUG_HIGH_ON) THEN
192
193 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || 'end');
194 END IF;
195
196 -- Standard call to get message count and if count is 1, get message info.
197 FND_MSG_PUB.Count_And_Get
198 (p_count => x_msg_count,
199 p_data => x_msg_data
200 );
201 EXCEPTION
202
203 WHEN Ams_Utility_Pvt.resource_locked THEN
204 x_return_status := FND_API.g_ret_sts_error;
205 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
206
207 WHEN FND_API.G_EXC_ERROR THEN
208 ROLLBACK TO CREATE_Metric_Template_PVT;
209 x_return_status := FND_API.G_RET_STS_ERROR;
210 -- Standard call to get message count and if count=1, get the message
211 FND_MSG_PUB.Count_And_Get (
212 p_encoded => FND_API.G_FALSE,
213 p_count => x_msg_count,
214 p_data => x_msg_data
215 );
216
217 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
218 ROLLBACK TO CREATE_Metric_Template_PVT;
219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
220 -- Standard call to get message count and if count=1, get the message
221 FND_MSG_PUB.Count_And_Get (
222 p_encoded => FND_API.G_FALSE,
223 p_count => x_msg_count,
224 p_data => x_msg_data
225 );
226
227 WHEN OTHERS THEN
228 ROLLBACK TO CREATE_Metric_Template_PVT;
229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
230 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
231 THEN
232 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
233 END IF;
234 -- Standard call to get message count and if count=1, get the message
235 FND_MSG_PUB.Count_And_Get (
236 p_encoded => FND_API.G_FALSE,
237 p_count => x_msg_count,
238 p_data => x_msg_data
239 );
240 END Create_Metric_Template;
241
242
243 PROCEDURE Update_Metric_Template(
244 p_api_version_number IN NUMBER,
245 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
246 p_commit IN VARCHAR2 := FND_API.G_FALSE,
247 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
248
249 x_return_status OUT NOCOPY VARCHAR2,
250 x_msg_count OUT NOCOPY NUMBER,
251 x_msg_data OUT NOCOPY VARCHAR2,
252
253 p_metric_tpl_header_rec IN metric_tpl_header_rec_type,
254 x_object_version_number OUT NOCOPY NUMBER
255 )
256 IS
257
258 CURSOR c_get_metric_template(l_metric_tpl_header_id NUMBER) IS
259 SELECT METRIC_TPL_HEADER_ID ,
260 LAST_UPDATE_DATE ,
261 LAST_UPDATED_BY ,
262 CREATION_DATE ,
263 CREATED_BY ,
264 LAST_UPDATE_LOGIN ,
265 OBJECT_VERSION_NUMBER ,
266 APPLICATION_ID ,
267 ENABLED_FLAG ,
268 METRIC_TPL_HEADER_NAME,
269 DESCRIPTION,
270 OBJECT_TYPE,
271 ASSOCIATION_TYPE,
272 USED_BY_ID,
273 USED_BY_CODE
274
275 FROM AMS_MET_TPL_HEADERS_VL
276 WHERE metric_tpl_header_id = l_metric_tpl_header_id;
277
278 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Metric_Template';
279 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
280 -- Local Variables
281 l_object_version_number NUMBER;
282 l_METRIC_TPL_HEADER_ID NUMBER;
283 l_ref_metric_tpl_header_rec metric_tpl_header_rec_type;
284 l_tar_metric_tpl_header_rec metric_tpl_header_rec_type;
285 l_rowid ROWID;
286
287 BEGIN
288 -- Standard Start of API savepoint
289 SAVEPOINT UPDATE_Metric_Template_PVT;
290
291 -- Standard call to check for call compatibility.
292 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
293 p_api_version_number,
294 l_api_name,
295 G_PKG_NAME)
296 THEN
297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
298 END IF;
299
300 -- Initialize message list if p_init_msg_list is set to TRUE.
301 IF FND_API.to_Boolean( p_init_msg_list )
302 THEN
303 FND_MSG_PUB.initialize;
304 END IF;
305
306 -- Debug Message
307 IF (AMS_DEBUG_HIGH_ON) THEN
308 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || ': start');
309 END IF;
310
311 -- Initialize API return status to SUCCESS
312 x_return_status := FND_API.G_RET_STS_SUCCESS;
313
314 -- Initialize the tar record.
315 l_tar_metric_tpl_header_rec := P_metric_tpl_header_rec;
316
317 IF (AMS_DEBUG_HIGH_ON) THEN
318 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name ||
319 ': ASSOCIATION_TYPE = '|| l_tar_metric_tpl_header_rec.association_type);
320 END IF;
321
322 IF (l_tar_metric_tpl_header_rec.METRIC_TPL_HEADER_ID IS NULL OR
323 l_tar_metric_tpl_header_rec.METRIC_TPL_HEADER_ID = FND_API.G_MISS_NUM )
324 THEN
325 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_MISSING_FIELD',
326 p_token_name => 'COLUMN',
327 p_token_value => 'METRIC_TPL_HEADER_ID');
328 RAISE FND_API.G_EXC_ERROR;
329 END IF;
330
331 IF (l_tar_metric_tpl_header_rec.object_version_number IS NULL OR
332 l_tar_metric_tpl_header_rec.object_version_number = FND_API.G_MISS_NUM)
333 THEN
334 Ams_Utility_Pvt.Error_Message(p_message_name => 'API_VERSION_MISSING',
335 p_token_name => 'COLUMN',
336 p_token_value => 'object_version_number');
337 RAISE FND_API.G_EXC_ERROR;
338 END IF;
339
340 OPEN c_get_Metric_Template(l_tar_metric_tpl_header_rec.metric_tpl_header_id);
341
342 FETCH c_get_Metric_Template INTO l_ref_metric_tpl_header_rec;
343
344 IF ( c_get_Metric_Template%NOTFOUND) THEN
345 Ams_Utility_Pvt.Error_Message(
346 p_message_name => 'API_MISSING_UPDATE_TARGET',
347 p_token_name => 'INFO',
348 p_token_value => 'Metric_Template');
349 CLOSE c_get_Metric_Template;
350 RAISE FND_API.G_EXC_ERROR;
351 END IF;
352
353 CLOSE c_get_Metric_Template;
354
355 -- Check Whether record has been changed by someone else
356 IF (l_tar_metric_tpl_header_rec.object_version_number <>
357 l_ref_metric_tpl_header_rec.object_version_number) THEN
358 Ams_Utility_Pvt.Error_Message(p_message_name => 'API_RECORD_CHANGED',
359 p_token_name => 'INFO',
360 p_token_value => 'Metric_Template');
361 RAISE FND_API.G_EXC_ERROR;
362 END IF;
363
364 -- 13-Jan-2005 dmvincen : Changing name, desc, and enabled is allowed.
365 /*************
366 --11/26/02 sunil : check if the template is a seeded one
367 IF (l_ref_metric_tpl_header_rec.metric_tpl_header_id < 10000) THEN
368 IF (((l_tar_metric_tpl_header_rec.metric_tpl_header_name <>
369 l_ref_metric_tpl_header_rec.metric_tpl_header_name)
370 OR (l_tar_metric_tpl_header_rec.description <>
371 l_ref_metric_tpl_header_rec.description ))
372 AND (l_tar_metric_tpl_header_rec.enabled_flag =
373 l_ref_metric_tpl_header_rec.enabled_flag)) THEN
374 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
375 THEN
376 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_METR_TPL_SEEDED_MOD');
377 END IF;
378
379 RAISE FND_API.G_EXC_ERROR;
380 END IF;
381 END IF;
382 ***************/
383
384 l_object_version_number := l_ref_metric_tpl_header_rec.object_version_number + 1;
385
386 Complete_metric_tpl_header_Rec(l_ref_metric_tpl_header_rec,
390 THEN
387 l_tar_metric_tpl_header_rec);
388
389 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
391 -- Debug message
392 IF (AMS_DEBUG_HIGH_ON) THEN
393
394 Ams_Utility_Pvt.debug_message('Private API: Validate_Metric_Template');
395 END IF;
396
397 -- Invoke validation procedures
398 Validate_metric_template(
399 p_api_version_number => 1.0,
400 p_init_msg_list => FND_API.G_FALSE,
401 p_validation_level => p_validation_level,
402 p_validation_mode => JTF_PLSQL_API.g_update,
403 p_metric_tpl_header_rec => l_tar_metric_tpl_header_rec,
404 x_return_status => x_return_status,
405 x_msg_count => x_msg_count,
406 x_msg_data => x_msg_data);
407 END IF;
408
409 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
410 RAISE FND_API.G_EXC_ERROR;
411 END IF;
412
413 -- Debug Message
414 IF (AMS_DEBUG_HIGH_ON) THEN
415
416 Ams_Utility_Pvt.debug_message('Private API: Calling Ams_Met_Tpl_Headers_B_Pkg.Update_Row');
417 END IF;
418
419 -- Invoke table handler(AMS_MET_TPL_HEADERS_B_PKG.Update_Row)
420 Ams_Met_Tpl_Headers_B_Pkg.Update_Row(
421 p_metric_tpl_header_id => l_ref_metric_tpl_header_rec.metric_tpl_header_id,
422 p_last_update_date => SYSDATE,
423 p_last_updated_by => FND_GLOBAL.USER_ID,
424 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
425 p_object_version_number => l_object_version_number,
426 p_enabled_flag => l_tar_metric_tpl_header_rec.enabled_flag,
427 p_application_id => l_tar_metric_tpl_header_rec.application_id,
428 p_METRIC_TPL_HEADER_NAME => l_tar_metric_tpl_header_rec.METRIC_TPL_HEADER_NAME,
429 p_DESCRIPTION => l_tar_metric_tpl_header_rec.DESCRIPTION,
430 p_object_type => l_ref_metric_tpl_header_rec.object_type,
431 p_association_type => l_ref_metric_tpl_header_rec.association_type,
432 p_used_by_id => l_ref_metric_tpl_header_rec.used_by_id,
433 p_used_by_code => l_ref_metric_tpl_header_rec.used_by_code);
434 x_object_version_number := l_object_version_number;
435
436 --
437 -- End of API body.
438 --
439
440 -- Standard check for p_commit
441 IF FND_API.to_Boolean( p_commit )
442 THEN
443 COMMIT WORK;
444 END IF;
445
446
447 -- Debug Message
448 IF (AMS_DEBUG_HIGH_ON) THEN
449
450 Ams_Utility_Pvt.debug_message('PRIVATE API: ' || l_api_name || ': END');
451 END IF;
452
453 -- Standard call to get message count and if count is 1, get message info.
454 FND_MSG_PUB.Count_And_Get
455 (p_count => x_msg_count,
456 p_data => x_msg_data
457 );
458 EXCEPTION
459
460 WHEN Ams_Utility_Pvt.resource_locked THEN
461 x_return_status := FND_API.g_ret_sts_error;
462 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
463
464 WHEN FND_API.G_EXC_ERROR THEN
465 ROLLBACK TO UPDATE_Metric_Template_PVT;
466 x_return_status := FND_API.G_RET_STS_ERROR;
467 -- Standard call to get message count and if count=1, get the message
468 FND_MSG_PUB.Count_And_Get (
469 p_encoded => FND_API.G_FALSE,
470 p_count => x_msg_count,
471 p_data => x_msg_data
472 );
473
474 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
475 ROLLBACK TO UPDATE_Metric_Template_PVT;
476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
477 -- Standard call to get message count and if count=1, get the message
478 FND_MSG_PUB.Count_And_Get (
479 p_encoded => FND_API.G_FALSE,
480 p_count => x_msg_count,
481 p_data => x_msg_data
482 );
483
484 WHEN OTHERS THEN
485 ROLLBACK TO UPDATE_Metric_Template_PVT;
486 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
487 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
488 THEN
489 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
490 END IF;
491 -- Standard call to get message count and if count=1, get the message
492 FND_MSG_PUB.Count_And_Get (
493 p_encoded => FND_API.G_FALSE,
494 p_count => x_msg_count,
495 p_data => x_msg_data
496 );
497 END Update_Metric_Template;
498
499
500 PROCEDURE Delete_Metric_Template(
501 p_api_version_number IN NUMBER,
502 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
503 p_commit IN VARCHAR2 := FND_API.G_FALSE,
504 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
505 x_return_status OUT NOCOPY VARCHAR2,
506 x_msg_count OUT NOCOPY NUMBER,
507 x_msg_data OUT NOCOPY VARCHAR2,
508 p_metric_tpl_header_id IN NUMBER,
509 p_object_version_number IN NUMBER
510 )
511 IS
512 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Metric_Template';
513 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
514 l_object_version_number NUMBER;
515
516 BEGIN
520 -- Standard call to check for call compatibility.
517 -- Standard Start of API savepoint
518 SAVEPOINT DELETE_Metric_Template_PVT;
519
521 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
522 p_api_version_number,
523 l_api_name,
524 G_PKG_NAME)
525 THEN
526 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
527 END IF;
528
529 -- Initialize message list if p_init_msg_list is set to TRUE.
530 IF FND_API.to_Boolean( p_init_msg_list )
531 THEN
532 FND_MSG_PUB.initialize;
533 END IF;
534
535 -- Debug Message
536 IF (AMS_DEBUG_HIGH_ON) THEN
537
538 Ams_Utility_Pvt.debug_message('PRIVATE API: ' || l_api_name || ': START');
539 END IF;
540
541
542 --11/26/02 sunil : check if the template is a seeded one
543 IF p_metric_tpl_header_id < 10000 THEN
544 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
545 THEN
546 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_METR_TPL_SEEDED');
547 END IF;
548
549 RAISE FND_API.G_EXC_ERROR;
550 END IF;
551
552
553
554 -- Initialize API return status to SUCCESS
555 x_return_status := FND_API.G_RET_STS_SUCCESS;
556
557 --
558 -- Api body
559 --
560
561 -- Debug Message
562 IF (AMS_DEBUG_HIGH_ON) THEN
563
564 Ams_Utility_Pvt.debug_message(
565 'PRIVATE API: DELETING metric template id='||p_metric_tpl_header_id);
566 END IF;
567
568 -- Debug Message
569 IF (AMS_DEBUG_HIGH_ON) THEN
570
571 Ams_Utility_Pvt.debug_message( 'PRIVATE API: DELETING metric template details');
572 END IF;
573
574 DELETE FROM ams_met_tpl_details
575 WHERE metric_tpl_header_id = p_metric_tpl_header_id;
576
577 -- Debug Message
578 IF (AMS_DEBUG_HIGH_ON) THEN
579
580 Ams_Utility_Pvt.debug_message( 'PRIVATE API: DELETING metric template associations');
581 END IF;
582
583 DELETE FROM ams_met_tpl_assocs
584 WHERE metric_tpl_header_id = p_metric_tpl_header_id;
585
586 -- Debug Message
587 IF (AMS_DEBUG_HIGH_ON) THEN
588
589 Ams_Utility_Pvt.debug_message( 'PRIVATE API: Calling DELETE TABLE handler');
590 END IF;
591
592 -- Invoke table handler(AMS_MET_TPL_HEADERS_B_PKG.Delete_Row)
593 Ams_Met_Tpl_Headers_B_Pkg.Delete_Row(
594 p_METRIC_TPL_HEADER_ID => p_METRIC_TPL_HEADER_ID);
595 --
596 -- End of API body
597 --
598
599 -- Standard check for p_commit
600 IF FND_API.to_Boolean( p_commit )
601 THEN
602 COMMIT WORK;
603 END IF;
604
605
606 -- Debug Message
607 IF (AMS_DEBUG_HIGH_ON) THEN
608
609 Ams_Utility_Pvt.debug_message('PRIVATE API: ' || l_api_name || 'END');
610 END IF;
611
612 -- Standard call to get message count and if count is 1, get message info.
613 FND_MSG_PUB.Count_And_Get
614 (p_count => x_msg_count,
615 p_data => x_msg_data
616 );
617 EXCEPTION
618
619 WHEN Ams_Utility_Pvt.resource_locked THEN
620 x_return_status := FND_API.g_ret_sts_error;
621 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
622
623 WHEN FND_API.G_EXC_ERROR THEN
624 ROLLBACK TO DELETE_Metric_Template_PVT;
625 x_return_status := FND_API.G_RET_STS_ERROR;
626 -- Standard call to get message count and if count=1, get the message
627 FND_MSG_PUB.Count_And_Get (
628 p_encoded => FND_API.G_FALSE,
629 p_count => x_msg_count,
630 p_data => x_msg_data
631 );
632
633 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
634 ROLLBACK TO DELETE_Metric_Template_PVT;
635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
636 -- Standard call to get message count and if count=1, get the message
637 FND_MSG_PUB.Count_And_Get (
638 p_encoded => FND_API.G_FALSE,
639 p_count => x_msg_count,
640 p_data => x_msg_data
641 );
642
643 WHEN OTHERS THEN
644 ROLLBACK TO DELETE_Metric_Template_PVT;
645 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
646 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
647 THEN
648 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
649 END IF;
650 -- Standard call to get message count and if count=1, get the message
651 FND_MSG_PUB.Count_And_Get (
652 p_encoded => FND_API.G_FALSE,
653 p_count => x_msg_count,
654 p_data => x_msg_data
655 );
656 END Delete_Metric_Template;
657
658 -- Hint: Primary key needs to be returned.
659 PROCEDURE Lock_Metric_Template(
660 p_api_version_number IN NUMBER,
661 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
662
663 x_return_status OUT NOCOPY VARCHAR2,
664 x_msg_count OUT NOCOPY NUMBER,
665 x_msg_data OUT NOCOPY VARCHAR2,
666
670
667 p_metric_tpl_header_id IN NUMBER,
668 p_object_version IN NUMBER
669 )
671 IS
672 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Metric_Template';
673 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
674 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
675 l_METRIC_TPL_HEADER_ID NUMBER;
676
677 CURSOR c_Metric_Template IS
678 SELECT METRIC_TPL_HEADER_ID
679 FROM AMS_MET_TPL_HEADERS_B
680 WHERE METRIC_TPL_HEADER_ID = p_METRIC_TPL_HEADER_ID
681 AND object_version_number = p_object_version
682 FOR UPDATE NOWAIT;
683
684 BEGIN
685
686 -- Debug Message
687 IF (AMS_DEBUG_HIGH_ON) THEN
688
689 Ams_Utility_Pvt.debug_message('PRIVATE API: ' || l_api_name || 'START');
690 END IF;
691
692 -- Initialize message list if p_init_msg_list is set to TRUE.
693 IF FND_API.to_Boolean( p_init_msg_list )
694 THEN
695 FND_MSG_PUB.initialize;
696 END IF;
697
698 -- Standard call to check for call compatibility.
699 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
700 p_api_version_number,
701 l_api_name,
702 G_PKG_NAME)
703 THEN
704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
705 END IF;
706
707
708 -- Initialize API return status to SUCCESS
709 x_return_status := FND_API.G_RET_STS_SUCCESS;
710
711
712 ------------------------ lock -------------------------
713
714 IF (AMS_DEBUG_HIGH_ON) THEN
715
716
717
718 Ams_Utility_Pvt.debug_message(l_full_name||': START');
719
720 END IF;
721 OPEN c_Metric_Template;
722
723 FETCH c_Metric_Template INTO l_METRIC_TPL_HEADER_ID;
724
725 IF (c_Metric_Template%NOTFOUND) THEN
726 CLOSE c_Metric_Template;
727 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
728 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
729 FND_MSG_PUB.ADD;
730 END IF;
731 RAISE FND_API.g_exc_error;
732 END IF;
733
734 CLOSE c_Metric_Template;
735
736 -------------------- finish --------------------------
737 FND_MSG_PUB.count_and_get(
738 p_encoded => FND_API.g_false,
739 p_count => x_msg_count,
740 p_data => x_msg_data);
741 IF (AMS_DEBUG_HIGH_ON) THEN
742
743 Ams_Utility_Pvt.debug_message(l_full_name ||': END');
744 END IF;
745 EXCEPTION
746
747 WHEN Ams_Utility_Pvt.resource_locked THEN
748 x_return_status := FND_API.g_ret_sts_error;
749 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
750
751 WHEN FND_API.G_EXC_ERROR THEN
752 ROLLBACK TO LOCK_Metric_Template_PVT;
753 x_return_status := FND_API.G_RET_STS_ERROR;
754 -- Standard call to get message count and if count=1, get the message
755 FND_MSG_PUB.Count_And_Get (
756 p_encoded => FND_API.G_FALSE,
757 p_count => x_msg_count,
758 p_data => x_msg_data
759 );
760
761 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
762 ROLLBACK TO LOCK_Metric_Template_PVT;
763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764 -- Standard call to get message count and if count=1, get the message
765 FND_MSG_PUB.Count_And_Get (
766 p_encoded => FND_API.G_FALSE,
767 p_count => x_msg_count,
768 p_data => x_msg_data
769 );
770
771 WHEN OTHERS THEN
772 ROLLBACK TO LOCK_Metric_Template_PVT;
773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
774 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
775 THEN
776 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
777 END IF;
778 -- Standard call to get message count and if count=1, get the message
779 FND_MSG_PUB.Count_And_Get (
780 p_encoded => FND_API.G_FALSE,
781 p_count => x_msg_count,
782 p_data => x_msg_data
783 );
784 END Lock_Metric_Template;
785
786
787 PROCEDURE CHECK_met_tpl_hdr_UK_ITEMS(
788 p_metric_tpl_header_rec IN metric_tpl_header_rec_type,
789 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
790 x_return_status OUT NOCOPY VARCHAR2)
791 IS
792 l_valid_flag VARCHAR2(1) := FND_API.g_false;
793
794
795 /*sunkumar - 20-apr-2004 validations for name of metric template */
796 /* CREATING template WITH THE WORD "AND" RATHER THAN "&" CREATES ERROR */
797
798 CURSOR c_check_header_name(p_met_tpl_header_name VARCHAR2) IS
799 SELECT count(1)
800 FROM AMS_MET_TPL_HEADERS_VL
801 WHERE METRIC_TPL_HEADER_NAME = p_met_tpl_header_name;
802
803 CURSOR c_check_header_detail(p_met_tpl_header_id number,
804 p_met_tpl_header_name VARCHAR2) IS
805 SELECT 1
806 FROM AMS_MET_TPL_HEADERS_VL
807 WHERE METRIC_TPL_HEADER_ID <> p_met_tpl_header_id
808 AND METRIC_TPL_HEADER_NAME = p_met_tpl_header_name;
809
810 /*ENd Changes sunkumar */
811
815 FROM ams_metrics_vl
812 CURSOR c_crt_get_dup_names(p_metrics_name VARCHAR2,
813 p_arc_metric_used_for_object VARCHAR2) IS
814 SELECT 1
816 WHERE UPPER(METRICS_NAME) = UPPER(p_metrics_name)
817 AND arc_metric_used_for_object = p_arc_metric_used_for_object;
818
819 l_count number;
820
821 BEGIN
822 x_return_status := FND_API.g_ret_sts_success;
823
824 IF AMS_DEBUG_HIGH_ON THEN
825 Ams_Utility_Pvt.debug_message('PRIVATE API: Check_met_tpl_hdr_UK_items:'
826 || ' name/id=' || p_metric_tpl_header_rec.metric_tpl_header_name
827 || '/' || p_metric_tpl_header_rec.metric_tpl_header_id);
828 END IF;
829
830 -- Validate the PK ID is unique.
831 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
832 l_valid_flag := Ams_Utility_Pvt.check_uniqueness(
833 'AMS_MET_TPL_HEADERS_VL',
834 'METRIC_TPL_HEADER_ID = ' || p_metric_tpl_header_rec.METRIC_TPL_HEADER_ID
835 );
836 IF l_valid_flag = FND_API.g_false THEN
837 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_MTH_ID_DUPLICATE');
838 x_return_status := FND_API.g_ret_sts_error;
839 RETURN;
840 END IF;
841 -- ELSE
842 -- l_valid_flag := Ams_Utility_Pvt.check_uniqueness(
843 -- 'AMS_MET_TPL_HEADERS_VL',
844 -- 'METRIC_TPL_HEADER_ID = ' || p_metric_tpl_header_rec.METRIC_TPL_HEADER_ID ||
845 -- ' AND METRIC_TPL_HEADER_ID <> ' || p_metric_tpl_header_rec.METRIC_TPL_HEADER_ID
846 -- );
847 END IF;
848
849 -- Validate the name is unique.
850
851 /*sunkumar - 20-apr-2004 validations for name of metric template */
852 /* CREATING template WITH THE WORD "AND" RATHER THAN "&" CREATES ERROR */
853
854 l_valid_flag := FND_API.G_TRUE;
855
856 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
857
858 OPEN c_check_header_name(p_metric_tpl_header_rec.Metric_Tpl_header_name);
859 FETCH c_check_header_name INTO l_count;
860 CLOSE c_check_header_name;
861 IF l_count > 0 THEN
862 l_valid_flag := FND_API.G_FALSE;
863 END IF;
864
865 ELSE
866
867 OPEN c_check_header_detail(p_metric_tpl_header_rec.metric_tpl_header_id,
868 p_metric_tpl_header_rec.metric_tpl_header_name);
869 FETCH c_check_header_detail INTO l_count;
870 CLOSE c_check_header_detail;
871 IF l_count > 0 THEN
872 l_valid_flag := FND_API.G_FALSE;
873 END IF;
874
875 END IF;
876
877 /*End changes sunkumar */
878
879 IF l_valid_flag = FND_API.g_false THEN
880 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_MTH_NAME_DUPLICATE');
881 x_return_status := FND_API.g_ret_sts_error;
882 RETURN;
883 END IF;
884
885 END check_met_tpl_hdr_uk_items;
886
887 PROCEDURE CHECK_met_tpl_hdr_REQ_ITEMS(
888 p_metric_tpl_header_rec IN metric_tpl_header_rec_type,
889 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
890 x_return_status OUT NOCOPY VARCHAR2
891 )
892 IS
893 BEGIN
894 x_return_status := FND_API.g_ret_sts_success;
895
896 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
897
898 IF p_metric_tpl_header_rec.metric_tpl_header_id IS NULL OR
899 p_metric_tpl_header_rec.metric_tpl_header_id = FND_API.g_miss_num THEN
900 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
901 FND_MESSAGE.set_token('MISS_FIELD','METRIC_TPL_HEADER_ID');
902 x_return_status := FND_API.g_ret_sts_error;
903 RETURN;
904 END IF;
905
906 IF p_metric_tpl_header_rec.enabled_flag IS NULL OR
907 p_metric_tpl_header_rec.enabled_flag = FND_API.g_miss_char THEN
908 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
909 FND_MESSAGE.set_token('MISS_FIELD','ENABLED_FLAG');
910 x_return_status := FND_API.g_ret_sts_error;
911 RETURN;
912 END IF;
913
914 IF p_metric_tpl_header_rec.METRIC_TPL_HEADER_NAME IS NULL OR
915 p_metric_tpl_header_rec.METRIC_TPL_HEADER_NAME = FND_API.g_miss_char THEN
916 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
917 FND_MESSAGE.set_token('MISS_FIELD','METRIC_TPL_HEADER_NAME');
918 x_return_status := FND_API.g_ret_sts_error;
919 RETURN;
920 END IF;
921
922 IF p_metric_tpl_header_rec.DESCRIPTION IS NOT NULL AND
923 p_metric_tpl_header_rec.DESCRIPTION = FND_API.g_miss_char THEN
924 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
925 FND_MESSAGE.set_token('MISS_FIELD','DESCRIPTION');
926 x_return_status := FND_API.g_ret_sts_error;
927 RETURN;
928 END IF;
929
930 IF p_metric_tpl_header_rec.ASSOCIATION_TYPE IS NULL OR
931 p_metric_tpl_header_rec.ASSOCIATION_TYPE = FND_API.g_miss_char THEN
932 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
933 FND_MESSAGE.set_token('MISS_FIELD','ASSOCIATION_TYPE');
934 x_return_status := FND_API.g_ret_sts_error;
935 RETURN;
936 END IF;
937
938 IF p_metric_tpl_header_rec.ASSOCIATION_TYPE = 'CUSTOM_SETUP' AND
939 (p_metric_tpl_header_rec.USED_BY_ID IS NULL OR
943 x_return_status := FND_API.g_ret_sts_error;
940 p_metric_tpl_header_rec.USED_BY_ID = FND_API.g_miss_num) THEN
941 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
942 FND_MESSAGE.set_token('MISS_FIELD','USED_BY_ID');
944 RETURN;
945 ELSIF p_metric_tpl_header_rec.ASSOCIATION_TYPE = 'OBJECT_TYPE' AND
946 (p_metric_tpl_header_rec.USED_BY_CODE IS NULL OR
947 p_metric_tpl_header_rec.USED_BY_CODE = FND_API.g_miss_char) THEN
948 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
949 FND_MESSAGE.set_token('MISS_FIELD','USED_BY_CODE');
950 x_return_status := FND_API.g_ret_sts_error;
951 RETURN;
952 END IF;
953
954 ELSE -- Update
955
956 IF p_metric_tpl_header_rec.metric_tpl_header_id IS NULL OR
957 p_metric_tpl_header_rec.metric_tpl_header_id = FND_API.g_miss_num THEN
958 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_MTH_NO_MET_TPL_HDR_ID');
959 x_return_status := FND_API.g_ret_sts_error;
960 RETURN;
961 END IF;
962
963 IF p_metric_tpl_header_rec.enabled_flag IS NULL OR
964 p_metric_tpl_header_rec.enabled_flag = FND_API.g_miss_char THEN
965 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_MTH_NO_ENABLED_FLAG');
966 x_return_status := FND_API.g_ret_sts_error;
967 RETURN;
968 END IF;
969
970 IF p_metric_tpl_header_rec.METRIC_TPL_HEADER_NAME IS NULL OR
971 p_metric_tpl_header_rec.METRIC_TPL_HEADER_NAME = FND_API.g_miss_char THEN
972 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
973 FND_MESSAGE.set_token('MISS_FIELD','METRIC_TPL_HEADER_NAME');
974 x_return_status := FND_API.g_ret_sts_error;
975 RETURN;
976 END IF;
977
978 IF p_metric_tpl_header_rec.DESCRIPTION IS NOT NULL AND
979 p_metric_tpl_header_rec.DESCRIPTION = FND_API.g_miss_char THEN
980 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
981 FND_MESSAGE.set_token('MISS_FIELD','DESCRIPTION');
982 x_return_status := FND_API.g_ret_sts_error;
983 RETURN;
984 END IF;
985
986 IF p_metric_tpl_header_rec.ASSOCIATION_TYPE IS NULL OR
987 p_metric_tpl_header_rec.ASSOCIATION_TYPE = FND_API.g_miss_char THEN
988 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
989 FND_MESSAGE.set_token('MISS_FIELD','ASSOCIATION_TYPE');
990 x_return_status := FND_API.g_ret_sts_error;
991 RETURN;
992 END IF;
993
994 IF p_metric_tpl_header_rec.ASSOCIATION_TYPE = 'CUSTOM_SETUP' AND
995 (p_metric_tpl_header_rec.USED_BY_ID IS NULL OR
996 p_metric_tpl_header_rec.USED_BY_ID = FND_API.g_miss_num) THEN
997 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
998 FND_MESSAGE.set_token('MISS_FIELD','USED_BY_ID');
999 x_return_status := FND_API.g_ret_sts_error;
1000 RETURN;
1001 ELSIF p_metric_tpl_header_rec.ASSOCIATION_TYPE = 'OBJECT_TYPE' AND
1002 (p_metric_tpl_header_rec.USED_BY_CODE IS NULL OR
1003 p_metric_tpl_header_rec.USED_BY_CODE = FND_API.g_miss_char) THEN
1004 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
1005 FND_MESSAGE.set_token('MISS_FIELD','USED_BY_CODE');
1006 x_return_status := FND_API.g_ret_sts_error;
1007 RETURN;
1008 END IF;
1009
1010 END IF;
1011
1012 END CHECK_met_tpl_hdr_REQ_ITEMS;
1013
1014 PROCEDURE CHECK_met_tpl_hdr_FK_ITEMS(
1015 p_metric_tpl_header_rec IN metric_tpl_header_rec_type,
1016 x_return_status OUT NOCOPY VARCHAR2
1017 )
1018 IS
1019 CURSOR c_checkObjectType(p_objectType VARCHAR2) IS
1020 SELECT count(1) FROM ams_lookups
1021 WHERE lookup_type = 'AMS_METRIC_OBJECT_TYPE'
1022 AND lookup_code = p_objectType;
1023
1024 CURSOR c_checkCustomSetup(p_setupId NUMBER) IS
1025 SELECT count(1)
1026 FROM ams_custom_setups_B a, ams_lookups b
1027 WHERE custom_setup_id = p_setupId
1028 AND b.lookup_type in ( 'AMS_METRIC_OBJECT_TYPE', 'AMS_ROLLUP_TYPE')
1029 AND b.lookup_code = a.object_type;
1030
1031 l_count NUMBER;
1032 BEGIN
1033 x_return_status := FND_API.g_ret_sts_success;
1034
1035 IF p_metric_tpl_header_rec.ASSOCIATION_TYPE = 'OBJECT_TYPE' THEN
1036
1037 IF AMS_DEBUG_HIGH_ON THEN
1038 Ams_Utility_Pvt.debug_message('PRIVATE API: Check_met_tpl_hdr_FK'||
1039 ': object_type = '|| p_metric_tpl_header_rec.USED_BY_CODE);
1040 END IF;
1041
1042 OPEN c_checkObjectType(p_metric_tpl_header_rec.USED_BY_CODE);
1043 FETCH c_checkObjectType INTO l_count;
1044 CLOSE c_checkObjectTYpe;
1045
1046 IF l_count = 0 THEN
1047 Ams_Utility_Pvt.error_message('AMS_MTH_INVALID_OBJECT_TYPE');
1048 x_return_status := FND_API.g_ret_sts_error;
1049 END IF;
1050
1051 ELSIF p_metric_tpl_header_rec.ASSOCIATION_TYPE = 'CUSTOM_SETUP' THEN
1052
1053 IF AMS_DEBUG_HIGH_ON THEN
1054 Ams_Utility_Pvt.debug_message('PRIVATE API: Check_met_tpl_hdr_FK'||
1055 ': custom_setup_id = '|| p_metric_tpl_header_rec.USED_BY_ID);
1056 END IF;
1057
1058 OPEN c_checkCustomSetup(p_metric_tpl_header_rec.USED_BY_ID);
1059 FETCH c_checkCustomSetup INTO l_count;
1060 CLOSE c_checkCustomSetup;
1064 x_return_status := FND_API.g_ret_sts_error;
1061
1062 IF l_count = 0 THEN
1063 Ams_Utility_Pvt.error_message('AMS_MTH_INVALID_CUSTOM_SETUP');
1065 END IF;
1066
1067 END IF;
1068
1069 IF AMS_DEBUG_HIGH_ON THEN
1070 Ams_Utility_Pvt.debug_message('PRIVATE API: Check_met_tpl_hdr_FK'||
1071 ': x_return_status = '|| x_return_status);
1072 END IF;
1073
1074 END CHECK_met_tpl_hdr_FK_ITEMS;
1075
1076 PROCEDURE CHECK_met_tpl_hdr_LKP(
1077 p_metric_tpl_header_rec IN metric_tpl_header_rec_type,
1078 x_return_status OUT NOCOPY VARCHAR2
1079 )
1080 IS
1081 BEGIN
1082 x_return_status := FND_API.g_ret_sts_success;
1083
1084 -- Enter custom code here
1085 IF Ams_Utility_Pvt.is_y_or_n(p_metric_tpl_header_rec.enabled_flag) =
1086 FND_API.G_FALSE THEN
1087 Ams_Utility_Pvt.error_message(
1088 p_message_name => 'AMS_MTH_BAD_ENABLED_FLAG');
1089 x_return_status := FND_API.g_ret_sts_error;
1090 END IF;
1091
1092 IF AMS_DEBUG_HIGH_ON THEN
1093 Ams_Utility_Pvt.debug_message('PRIVATE API: Check_met_tpl_hdr_LKP'||
1094 ': lookup_code = '|| p_metric_tpl_header_rec.ASSOCIATION_TYPE);
1095 END IF;
1096
1097 IF AMS_UTILITY_PVT.Check_Lookup_Exists('ams_lookups',
1098 'AMS_METRIC_TPL_ASSOC_TYPES',
1099 p_metric_tpl_header_rec.ASSOCIATION_TYPE) = FND_API.G_FALSE THEN
1100 Ams_Utility_Pvt.error_message(
1101 p_message_name => 'AMS_MTH_INVALID_ASSOC_TYPE');
1102 x_return_status := FND_API.g_ret_sts_error;
1103 END IF;
1104
1105 END CHECK_met_tpl_hdr_LKP;
1106
1107 PROCEDURE Check_met_tpl_hdr_Items (
1108 P_metric_tpl_header_rec IN metric_tpl_header_rec_type,
1109 p_validation_mode IN VARCHAR2,
1110 x_return_status OUT NOCOPY VARCHAR2
1111 )
1112 IS
1113 BEGIN
1114
1115 -- Check Items Uniqueness API calls
1116
1117 -- Debug Message
1118 IF AMS_DEBUG_HIGH_ON THEN
1119 Ams_Utility_Pvt.debug_message('PRIVATE API: Check_met_tpl_hdr_items: UK');
1120 END IF;
1121
1122 check_met_tpl_hdr_uk_items(
1123 p_metric_tpl_header_rec => p_metric_tpl_header_rec,
1124 p_validation_mode => p_validation_mode,
1125 x_return_status => x_return_status);
1126 IF x_return_status <> FND_API.g_ret_sts_success THEN
1127 RETURN;
1128 END IF;
1129
1130 -- Check Items Required/NOT NULL API calls
1131
1132 -- Debug Message
1133 IF AMS_DEBUG_HIGH_ON THEN
1134 Ams_Utility_Pvt.debug_message('PRIVATE API: Check_met_tpl_hdr_items: REQ');
1135 END IF;
1136
1137 CHECK_met_tpl_hdr_REQ_ITEMS(
1138 p_metric_tpl_header_rec => p_metric_tpl_header_rec,
1139 p_validation_mode => p_validation_mode,
1140 x_return_status => x_return_status);
1141 IF x_return_status <> FND_API.g_ret_sts_success THEN
1142 RETURN;
1143 END IF;
1144
1145 -- Check Items Lookups
1146
1147 -- Debug Message
1148 IF AMS_DEBUG_HIGH_ON THEN
1149 Ams_Utility_Pvt.debug_message('PRIVATE API: Check_met_tpl_hdr_items: LKP');
1150 END IF;
1151
1152 CHECK_met_tpl_hdr_LKP(
1153 p_metric_tpl_header_rec => p_metric_tpl_header_rec,
1154 x_return_status => x_return_status);
1155 IF x_return_status <> FND_API.g_ret_sts_success THEN
1156 RETURN;
1157 END IF;
1158
1159 -- Check Items Foreign Keys API calls
1160
1161 -- Debug Message
1162 IF AMS_DEBUG_HIGH_ON THEN
1163 Ams_Utility_Pvt.debug_message('PRIVATE API: Check_met_tpl_hdr_items: FK');
1164 END IF;
1165
1166 CHECK_met_tpl_hdr_FK_ITEMS(
1167 p_metric_tpl_header_rec => p_metric_tpl_header_rec,
1168 x_return_status => x_return_status);
1169 IF x_return_status <> FND_API.g_ret_sts_success THEN
1170 RETURN;
1171 END IF;
1172
1173 -- Debug Message
1174 IF AMS_DEBUG_HIGH_ON THEN
1175 Ams_Utility_Pvt.debug_message('PRIVATE API: Check_met_tpl_hdr_items: DONE');
1176 END IF;
1177
1178 END Check_met_tpl_hdr_Items;
1179
1180
1181 -- PARAMETERS:
1182 -- p_ref_metric_tpl_header_rec - record copy from the database
1183 -- x_tar_metric_tpl_header_rec - input record to complete.
1184 PROCEDURE Complete_metric_tpl_header_Rec (
1185 p_ref_metric_tpl_header_rec IN metric_tpl_header_rec_type,
1186 x_tar_metric_tpl_header_rec IN OUT NOCOPY metric_tpl_header_rec_type)
1187 IS
1188 -- l_return_status VARCHAR2(1);
1189
1190 -- CURSOR c_complete IS
1191 -- SELECT *
1192 -- FROM ams_met_tpl_headers_vl
1193 -- WHERE metric_tpl_header_id = p_metric_tpl_header_rec.metric_tpl_header_id;
1194 -- l_metric_tpl_header_rec c_complete%ROWTYPE;
1195 BEGIN
1196 -- x_complete_rec := p_metric_tpl_header_rec;
1197
1198 -- OPEN c_complete;
1199 -- FETCH c_complete INTO l_metric_tpl_header_rec;
1200 -- CLOSE c_complete;
1201
1202 -- metric_tpl_header_id
1203 -- IF p_metric_tpl_header_rec.metric_tpl_header_id = FND_API.g_miss_num THEN
1204 -- x_complete_rec.metric_tpl_header_id := l_metric_tpl_header_rec.metric_tpl_header_id;
1205 -- END IF;
1206
1207 -- last_update_date
1208 -- IF p_metric_tpl_header_rec.last_update_date = FND_API.g_miss_date THEN
1209 -- x_complete_rec.last_update_date := l_metric_tpl_header_rec.last_update_date;
1210 -- END IF;
1211
1212 -- last_updated_by
1213 -- IF p_metric_tpl_header_rec.last_updated_by = FND_API.g_miss_num THEN
1214 -- x_complete_rec.last_updated_by := l_metric_tpl_header_rec.last_updated_by;
1215 -- END IF;
1216
1217 -- creation_date
1218 -- IF p_metric_tpl_header_rec.creation_date = FND_API.g_miss_date THEN
1219 -- x_complete_rec.creation_date := l_metric_tpl_header_rec.creation_date;
1220 -- END IF;
1221
1222 -- created_by
1223 -- IF p_metric_tpl_header_rec.created_by = FND_API.g_miss_num THEN
1224 -- x_complete_rec.created_by := l_metric_tpl_header_rec.created_by;
1225 -- END IF;
1226
1227 -- last_update_login
1228 -- IF p_metric_tpl_header_rec.last_update_login = FND_API.g_miss_num THEN
1229 -- x_complete_rec.last_update_login := l_metric_tpl_header_rec.last_update_login;
1230 -- END IF;
1231
1232 -- object_version_number
1233 -- IF p_metric_tpl_header_rec.object_version_number = FND_API.g_miss_num THEN
1234 -- x_complete_rec.object_version_number := l_metric_tpl_header_rec.object_version_number;
1235 -- END IF;
1236
1237 -- enabled_flag
1238 IF x_tar_metric_tpl_header_rec.enabled_flag = FND_API.g_miss_char THEN
1239 x_tar_metric_tpl_header_rec.enabled_flag := p_ref_metric_tpl_header_rec.enabled_flag;
1240 END IF;
1241
1242 -- application_id
1243 IF x_tar_metric_tpl_header_rec.application_id = FND_API.g_miss_num THEN
1244 x_tar_metric_tpl_header_rec.application_id := p_ref_metric_tpl_header_rec.application_id;
1245 END IF;
1246
1247 -- METRIC_TPL_HEADER_NAME
1248 IF x_tar_metric_tpl_header_rec.METRIC_TPL_HEADER_NAME = FND_API.g_miss_char
1249 THEN
1250 x_tar_metric_tpl_header_rec.METRIC_TPL_HEADER_NAME :=
1251 p_ref_metric_tpl_header_rec.METRIC_TPL_HEADER_NAME;
1252 END IF;
1253
1254 -- DESCRIPTION
1255 IF x_tar_metric_tpl_header_rec.DESCRIPTION = FND_API.g_miss_char THEN
1256 x_tar_metric_tpl_header_rec.DESCRIPTION :=
1257 p_ref_metric_tpl_header_rec.DESCRIPTION;
1258 END IF;
1259
1260 -- ASSOCIATION_TYPE
1261 IF x_tar_metric_tpl_header_rec.ASSOCIATION_TYPE = FND_API.g_miss_char THEN
1262 x_tar_metric_tpl_header_rec.ASSOCIATION_TYPE :=
1263 p_ref_metric_tpl_header_rec.ASSOCIATION_TYPE;
1264 END IF;
1265
1266 -- USED_BY_ID
1267 IF x_tar_metric_tpl_header_rec.USED_BY_ID = FND_API.g_miss_num THEN
1268 x_tar_metric_tpl_header_rec.USED_BY_ID :=
1269 p_ref_metric_tpl_header_rec.USED_BY_ID;
1270 END IF;
1271
1272 -- USED_BY_CODE
1273 IF x_tar_metric_tpl_header_rec.USED_BY_CODE = FND_API.g_miss_char THEN
1274 x_tar_metric_tpl_header_rec.USED_BY_CODE :=
1275 p_ref_metric_tpl_header_rec.USED_BY_CODE;
1276 END IF;
1277
1278 -- Note: Developers need to modify the procedure
1279 -- to handle any business specific requirements.
1280 END Complete_metric_tpl_header_Rec;
1281
1282 PROCEDURE Validate_metric_template(
1283 p_api_version_number IN NUMBER,
1284 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1285 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1286 p_metric_tpl_header_rec IN metric_tpl_header_rec_type,
1287 p_validation_mode IN VARCHAR2,
1288 x_return_status OUT NOCOPY VARCHAR2,
1289 x_msg_count OUT NOCOPY NUMBER,
1290 x_msg_data OUT NOCOPY VARCHAR2
1291 )
1292 IS
1293 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Metric_Template';
1294 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1295 l_object_version_number NUMBER;
1296
1297 BEGIN
1298 -- Standard Start of API savepoint
1299 SAVEPOINT VALIDATE_Metric_Template_;
1300
1301 -- Standard call to check for call compatibility.
1305 G_PKG_NAME)
1302 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1303 p_api_version_number,
1304 l_api_name,
1306 THEN
1307 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1308 END IF;
1309
1310 -- Debug Message
1311 IF AMS_DEBUG_HIGH_ON THEN
1312 Ams_Utility_Pvt.debug_message('PRIVATE API: ' || l_api_name || ': START');
1313 END IF;
1314
1315 -- Initialize API return status to SUCCESS
1316 x_return_status := FND_API.G_RET_STS_SUCCESS;
1317
1318 -- Initialize message list if p_init_msg_list is set to TRUE.
1319 IF FND_API.to_Boolean( p_init_msg_list )
1320 THEN
1321 FND_MSG_PUB.initialize;
1322 END IF;
1323
1324 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1325 -- Debug Message
1326 IF AMS_DEBUG_HIGH_ON THEN
1327 Ams_Utility_Pvt.debug_message('PRIVATE API: ' || l_api_name || ': Checking header items');
1328 END IF;
1329
1330 Check_met_tpl_hdr_Items(
1331 p_metric_tpl_header_rec => p_metric_tpl_header_rec,
1332 p_validation_mode => p_validation_mode,
1333 x_return_status => x_return_status
1334 );
1335
1336 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1337 RAISE FND_API.G_EXC_ERROR;
1338 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1340 END IF;
1341 END IF;
1342
1343 IF p_validation_mode = JTF_PLSQL_API.g_update THEN
1344
1345 -- Debug Message
1346 IF AMS_DEBUG_HIGH_ON THEN
1347 Ams_Utility_Pvt.debug_message('PRIVATE API: ' || l_api_name || ': UPDATE MODE');
1348 END IF;
1349
1350 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1351 Validate_met_tpl_hdr_Rec(
1352 p_api_version_number => 1.0,
1353 p_init_msg_list => FND_API.G_FALSE,
1354 x_return_status => x_return_status,
1355 x_msg_count => x_msg_count,
1356 x_msg_data => x_msg_data,
1357 p_metric_tpl_header_rec => p_metric_tpl_header_rec);
1358
1359 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1360 RAISE FND_API.G_EXC_ERROR;
1361 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1362 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1363 END IF;
1364 END IF;
1365 END IF;
1366
1367 -- Debug Message
1368 IF AMS_DEBUG_HIGH_ON THEN
1369 Ams_Utility_Pvt.debug_message('PRIVATE API: ' || l_api_name || ': END');
1370 END IF;
1371
1372 -- Standard call to get message count and if count is 1, get message info.
1373 FND_MSG_PUB.Count_And_Get
1374 (p_count => x_msg_count,
1375 p_data => x_msg_data
1376 );
1377 EXCEPTION
1378
1379 WHEN Ams_Utility_Pvt.resource_locked THEN
1380 x_return_status := FND_API.g_ret_sts_error;
1381 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1382
1383 WHEN FND_API.G_EXC_ERROR THEN
1384 ROLLBACK TO VALIDATE_Metric_Template_;
1385 x_return_status := FND_API.G_RET_STS_ERROR;
1386 -- Standard call to get message count and if count=1, get the message
1387 FND_MSG_PUB.Count_And_Get (
1388 p_encoded => FND_API.G_FALSE,
1389 p_count => x_msg_count,
1390 p_data => x_msg_data
1391 );
1392
1393 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1394 ROLLBACK TO VALIDATE_Metric_Template_;
1395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1396 -- Standard call to get message count and if count=1, get the message
1397 FND_MSG_PUB.Count_And_Get (
1398 p_encoded => FND_API.G_FALSE,
1399 p_count => x_msg_count,
1400 p_data => x_msg_data
1401 );
1402
1403 WHEN OTHERS THEN
1404 ROLLBACK TO VALIDATE_Metric_Template_;
1405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1406 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1407 THEN
1408 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1409 END IF;
1410 -- Standard call to get message count and if count=1, get the message
1411 FND_MSG_PUB.Count_And_Get (
1412 p_encoded => FND_API.G_FALSE,
1413 p_count => x_msg_count,
1414 p_data => x_msg_data
1415 );
1416 END Validate_Metric_Template;
1417
1418
1419 PROCEDURE Validate_met_tpl_hdr_rec(
1420 p_api_version_number IN NUMBER,
1421 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1422 x_return_status OUT NOCOPY VARCHAR2,
1423 x_msg_count OUT NOCOPY NUMBER,
1424 x_msg_data OUT NOCOPY VARCHAR2,
1425 p_metric_tpl_header_rec IN metric_tpl_header_rec_type
1426 )
1427 IS
1428 BEGIN
1429 -- Initialize message list if p_init_msg_list is set to TRUE.
1430 IF FND_API.to_Boolean( p_init_msg_list )
1431 THEN
1432 FND_MSG_PUB.initialize;
1433 END IF;
1434
1435 -- Initialize API return status to SUCCESS
1436 x_return_status := FND_API.G_RET_STS_SUCCESS;
1437
1438 -- Hint: Validate data
1439 -- If data not valid
1440 -- THEN
1441 -- x_return_status := FND_API.G_RET_STS_ERROR;
1442
1443 -- Debug Message
1444 IF (AMS_DEBUG_HIGH_ON) THEN
1445
1446 Ams_Utility_Pvt.debug_message('PRIVATE API: Validate_met_tpl_hdr_rec');
1447 END IF;
1448 -- Standard call to get message count and if count is 1, get message info.
1449 FND_MSG_PUB.Count_And_Get
1450 (p_count => x_msg_count,
1451 p_data => x_msg_data
1452 );
1453 END Validate_met_tpl_hdr_Rec;
1454
1455 END Ams_Metric_Template_Pvt;