[Home] [Help]
PACKAGE BODY: APPS.AMS_MET_TPL_ASSOC_PVT
Source
1 PACKAGE BODY Ams_Met_Tpl_Assoc_Pvt AS
2 /* $Header: amsvmtab.pls 115.8 2003/02/14 06:53:19 sunkumar noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Met_Tpl_Assoc_PVT
7 -- Purpose
8 --
9 -- History
10 -- 03/05/2002 dmvincen Created.
11 -- 06/03/2002 dmvincen Restrict duplicate associations.
12 -- 30/01/2003 sunkumar restricted updation of associations
13 -- for seeded metrics apart from the enable flag.
14 -- NOTE
15 --
16 -- End of Comments
17 -- ===============================================================
18
19
20 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Met_Tpl_Assoc_PVT';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvmtab.pls';
22
23 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
24 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
25 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
26
27 PROCEDURE Complete_met_tpl_assoc_Rec (
28 p_ref_met_tpl_assoc_rec IN met_tpl_assoc_rec_type,
29 x_tar_met_tpl_assoc_rec OUT NOCOPY met_tpl_assoc_rec_type);
30
31 -- Hint: Primary key needs to be returned.
32 PROCEDURE Create_Met_Tpl_Assoc(
33 p_api_version_number IN NUMBER,
34 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
35 p_commit IN VARCHAR2 := FND_API.G_FALSE,
36 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
37
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41
42 p_met_tpl_assoc_rec IN met_tpl_assoc_rec_type := g_miss_met_tpl_assoc_rec,
43 x_METRIC_TPL_ASSOC_ID OUT NOCOPY NUMBER
44 )
45
46 IS
47 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Met_Tpl_Assoc';
48 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
49 l_return_status_full VARCHAR2(1);
50 l_object_version_number NUMBER := 1;
51 l_org_id NUMBER := FND_API.G_MISS_NUM;
52 l_METRIC_TPL_ASSOC_ID NUMBER;
53 l_dummy NUMBER;
54
55 CURSOR c_id IS
56 SELECT AMS_MET_TPL_ASSOCS_s.NEXTVAL
57 FROM dual;
58
59 CURSOR c_id_exists (l_id IN NUMBER) IS
60 SELECT 1
61 FROM AMS_MET_TPL_ASSOCS
62 WHERE METRIC_TPL_ASSOC_ID = l_id;
63
64 BEGIN
65 -- Standard Start of API savepoint
66 SAVEPOINT CREATE_Met_Tpl_Assoc_PVT;
67
68 -- Standard call to check for call compatibility.
69 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
70 p_api_version_number,
71 l_api_name,
72 G_PKG_NAME)
73 THEN
74 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
75 END IF;
76
77 -- Initialize message list if p_init_msg_list is set to TRUE.
78 IF FND_API.to_Boolean( p_init_msg_list )
79 THEN
80 FND_MSG_PUB.initialize;
81 END IF;
82
83 -- Debug Message
84 IF (AMS_DEBUG_HIGH_ON) THEN
85
86 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || 'start');
87 END IF;
88
89
90 --sunkumar 30/01/2003
91 --check if we are trying to create associations for a seeded metric template
92 /*IF p_met_tpl_assoc_rec.metric_tpl_header_id < 10000 THEN
93 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
94 THEN
95 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_METR_TPL_SEEDED');
96 END IF;
97
98 RAISE FND_API.G_EXC_ERROR;
99 END IF; */
100
101 -- Initialize API return status to SUCCESS
102 x_return_status := FND_API.G_RET_STS_SUCCESS;
103
104 -- Local variable initialization
105
106 IF p_met_tpl_assoc_rec.METRIC_TPL_ASSOC_ID IS NULL OR
107 p_met_tpl_assoc_rec.METRIC_TPL_ASSOC_ID = FND_API.g_miss_num THEN
108 LOOP
109 l_dummy := NULL;
110 OPEN c_id;
111 FETCH c_id INTO l_METRIC_TPL_ASSOC_ID;
112 CLOSE c_id;
113
114 OPEN c_id_exists(l_METRIC_TPL_ASSOC_ID);
115 FETCH c_id_exists INTO l_dummy;
116 CLOSE c_id_exists;
117 EXIT WHEN l_dummy IS NULL;
118 END LOOP;
119 ELSE
120 l_METRIC_TPL_ASSOC_ID := p_met_tpl_assoc_rec.METRIC_TPL_ASSOC_ID;
121 END IF;
122
123 -- =========================================================================
124 -- Validate Environment
125 -- =========================================================================
126
127 IF FND_GLOBAL.User_Id IS NULL
128 THEN
129 Ams_Utility_Pvt.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
130 RAISE FND_API.G_EXC_ERROR;
131 END IF;
132
133 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
134 THEN
135 -- Debug message
136 IF (AMS_DEBUG_HIGH_ON) THEN
137
138 Ams_Utility_Pvt.debug_message('Private API: Validate_Met_Tpl_Assoc');
139 END IF;
140
141 -- Invoke validation procedures
142 Validate_met_tpl_assoc(
143 p_api_version_number => 1.0,
144 p_init_msg_list => FND_API.G_FALSE,
145 p_validation_level => p_validation_level,
146 p_validation_mode => JTF_PLSQL_API.g_create,
147 p_met_tpl_assoc_rec => p_met_tpl_assoc_rec,
148 x_return_status => x_return_status,
149 x_msg_count => x_msg_count,
150 x_msg_data => x_msg_data);
151 END IF;
152
153 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
154 RAISE FND_API.G_EXC_ERROR;
155 END IF;
156
157
158 -- Debug Message
159 IF (AMS_DEBUG_HIGH_ON) THEN
160
161 Ams_Utility_Pvt.debug_message( 'Private API: Calling create table handler');
162 END IF;
163
164 -- Invoke table handler(AMS_MET_TPL_ASSOCS_PKG.Insert_Row)
165 Ams_Met_Tpl_Assocs_Pkg.Insert_Row(
166 p_metric_tpl_assoc_id => l_METRIC_TPL_ASSOC_ID,
167 p_last_update_date => SYSDATE,
168 p_last_updated_by => FND_GLOBAL.USER_ID,
169 p_creation_date => SYSDATE,
170 p_created_by => FND_GLOBAL.USER_ID,
171 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
172 px_object_version_number => l_object_version_number,
173 p_metric_tpl_header_id => p_met_tpl_assoc_rec.metric_tpl_header_id,
174 p_association_type => p_met_tpl_assoc_rec.association_type,
175 p_used_by_id => p_met_tpl_assoc_rec.used_by_id,
176 p_used_by_code => p_met_tpl_assoc_rec.used_by_code,
177 p_enabled_flag => p_met_tpl_assoc_rec.enabled_flag);
178
179 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
180 RAISE FND_API.G_EXC_ERROR;
181 END IF;
182
183 x_METRIC_TPL_ASSOC_ID := l_METRIC_TPL_ASSOC_ID;
184 --
185 -- End of API body
186 --
187
188 -- Standard check for p_commit
189 IF FND_API.to_Boolean( p_commit )
190 THEN
191 COMMIT WORK;
192 END IF;
193
194
195 -- Debug Message
196 IF (AMS_DEBUG_HIGH_ON) THEN
197
198 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || 'end');
199 END IF;
200
201 -- Standard call to get message count and if count is 1, get message info.
202 FND_MSG_PUB.Count_And_Get
203 (p_count => x_msg_count,
204 p_data => x_msg_data
205 );
206 EXCEPTION
207
208 WHEN Ams_Utility_Pvt.resource_locked THEN
209 x_return_status := FND_API.g_ret_sts_error;
210 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
211
212 WHEN FND_API.G_EXC_ERROR THEN
213 ROLLBACK TO CREATE_Met_Tpl_Assoc_PVT;
214 x_return_status := FND_API.G_RET_STS_ERROR;
215 -- Standard call to get message count and if count=1, get the message
216 FND_MSG_PUB.Count_And_Get (
217 p_encoded => FND_API.G_FALSE,
218 p_count => x_msg_count,
219 p_data => x_msg_data
220 );
221
222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
223 ROLLBACK TO CREATE_Met_Tpl_Assoc_PVT;
224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
225 -- Standard call to get message count and if count=1, get the message
226 FND_MSG_PUB.Count_And_Get (
227 p_encoded => FND_API.G_FALSE,
228 p_count => x_msg_count,
229 p_data => x_msg_data
230 );
231
232 WHEN OTHERS THEN
233 ROLLBACK TO CREATE_Met_Tpl_Assoc_PVT;
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
235 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
236 THEN
237 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
238 END IF;
239 -- Standard call to get message count and if count=1, get the message
240 FND_MSG_PUB.Count_And_Get (
241 p_encoded => FND_API.G_FALSE,
242 p_count => x_msg_count,
243 p_data => x_msg_data
244 );
245 END Create_Met_Tpl_Assoc;
246
247
248 PROCEDURE Update_Met_Tpl_Assoc(
249 p_api_version_number IN NUMBER,
250 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
251 p_commit IN VARCHAR2 := FND_API.G_FALSE,
252 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
253
254 x_return_status OUT NOCOPY VARCHAR2,
255 x_msg_count OUT NOCOPY NUMBER,
256 x_msg_data OUT NOCOPY VARCHAR2,
257
258 p_met_tpl_assoc_rec IN met_tpl_assoc_rec_type,
259 x_object_version_number OUT NOCOPY NUMBER
260 )
261
262 IS
263
264 CURSOR c_get_met_tpl_assoc(l_metric_tpl_assoc_id NUMBER) IS
265 SELECT METRIC_TPL_ASSOC_ID,
266 LAST_UPDATE_DATE,
267 LAST_UPDATED_BY,
268 CREATION_DATE,
269 CREATED_BY,
270 LAST_UPDATE_LOGIN,
271 OBJECT_VERSION_NUMBER,
272 METRIC_TPL_HEADER_ID,
273 ASSOCIATION_TYPE,
274 USED_BY_ID,
275 USED_BY_CODE,
276 ENABLED_FLAG
277 FROM AMS_MET_TPL_ASSOCS
278 WHERE metric_tpl_assoc_id = l_metric_tpl_assoc_id;
279 -- Hint: Developer need to provide Where clause
280
281 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Met_Tpl_Assoc';
282 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
283 -- Local Variables
284 l_object_version_number NUMBER;
285 l_METRIC_TPL_ASSOC_ID NUMBER;
286 l_ref_met_tpl_assoc_rec met_tpl_assoc_rec_type ;
287 l_tar_met_tpl_assoc_rec met_tpl_assoc_rec_type := P_met_tpl_assoc_rec;
288 l_rowid ROWID;
289
290 BEGIN
291 -- Standard Start of API savepoint
292 SAVEPOINT UPDATE_Met_Tpl_Assoc_PVT;
293
294 -- Standard call to check for call compatibility.
295 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
296 p_api_version_number,
297 l_api_name,
298 G_PKG_NAME)
299 THEN
300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
301 END IF;
302
303 -- Initialize message list if p_init_msg_list is set to TRUE.
304 IF FND_API.to_Boolean( p_init_msg_list )
305 THEN
306 FND_MSG_PUB.initialize;
307 END IF;
308
309 -- Debug Message
310 IF (AMS_DEBUG_HIGH_ON) THEN
311
312 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || 'start');
313 END IF;
314
315
316
317 -- Initialize API return status to SUCCESS
318 x_return_status := FND_API.G_RET_STS_SUCCESS;
319
320 -- Debug Message
321 IF (AMS_DEBUG_HIGH_ON) THEN
322
323 Ams_Utility_Pvt.debug_message('Private API: - Open Cursor to Select');
324 END IF;
325
326 OPEN c_get_Met_Tpl_Assoc( l_tar_met_tpl_assoc_rec.metric_tpl_assoc_id);
327
328 FETCH c_get_Met_Tpl_Assoc INTO l_ref_met_tpl_assoc_rec ;
329
330 IF ( c_get_Met_Tpl_Assoc%NOTFOUND) THEN
331 Ams_Utility_Pvt.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
332 p_token_name => 'INFO',
333 p_token_value => 'Met_Tpl_Assoc') ;
334 RAISE FND_API.G_EXC_ERROR;
335 END IF;
336 -- Debug Message
337 IF (AMS_DEBUG_HIGH_ON) THEN
338
339 Ams_Utility_Pvt.debug_message('Private API: - Close Cursor');
340 END IF;
341 CLOSE c_get_Met_Tpl_Assoc;
342
343 IF (l_tar_met_tpl_assoc_rec.object_version_number IS NULL OR
344 l_tar_met_tpl_assoc_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
345 Ams_Utility_Pvt.Error_Message(p_message_name => 'API_VERSION_MISSING',
346 p_token_name => 'COLUMN',
347 p_token_value => 'Last_Update_Date') ;
348 RAISE FND_API.G_EXC_ERROR;
349 END IF;
350
351 -- Check Whether record has been changed by someone else
352 IF (l_tar_met_tpl_assoc_rec.object_version_number <>
353 l_ref_met_tpl_assoc_rec.object_version_number) THEN
354 Ams_Utility_Pvt.Error_Message(p_message_name => 'API_RECORD_CHANGED',
355 p_token_name => 'INFO',
356 p_token_value => 'Met_Tpl_Assoc') ;
357 RAISE FND_API.G_EXC_ERROR;
358 END IF;
359
360 Complete_met_tpl_assoc_Rec(l_ref_met_tpl_assoc_rec, l_tar_met_tpl_assoc_rec);
361
362
363 --sunkumar 30/01/2003
364 --check if we are trying to update associations for a seeded metric template
365 IF p_met_tpl_assoc_rec.metric_tpl_header_id < 10000 THEN
366 IF (( (l_tar_met_tpl_assoc_rec.association_type <>l_ref_met_tpl_assoc_rec.association_type )
367 OR (l_tar_met_tpl_assoc_rec.used_by_id <>l_ref_met_tpl_assoc_rec.used_by_id)
368 OR (l_tar_met_tpl_assoc_rec.metric_tpl_header_id <>l_ref_met_tpl_assoc_rec.metric_tpl_header_id))
369 AND (l_tar_met_tpl_assoc_rec.enabled_flag=l_ref_met_tpl_assoc_rec.enabled_flag)) THEN
370 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
371 THEN
372 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_METR_TPL_SEEDED_MOD');
373 END IF;
374
375
376 RAISE FND_API.G_EXC_ERROR;
377 END IF;
378 END IF;
379
380
381 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
382 THEN
383 -- Debug message
384 IF (AMS_DEBUG_HIGH_ON) THEN
385
386 Ams_Utility_Pvt.debug_message('Private API: Validate_Met_Tpl_Assoc');
387 END IF;
388
389 -- Invoke validation procedures
390 Validate_met_tpl_assoc(
391 p_api_version_number => 1.0,
392 p_init_msg_list => FND_API.G_FALSE,
393 p_validation_level => p_validation_level,
394 p_validation_mode => JTF_PLSQL_API.g_update,
395 p_met_tpl_assoc_rec => p_met_tpl_assoc_rec,
396 x_return_status => x_return_status,
397 x_msg_count => x_msg_count,
398 x_msg_data => x_msg_data);
399 END IF;
400
401 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
402 RAISE FND_API.G_EXC_ERROR;
403 END IF;
404
405 x_object_version_number :=
406 l_ref_met_tpl_assoc_rec.object_version_number + 1;
407
408 -- Debug Message
409 IF (AMS_DEBUG_HIGH_ON) THEN
410
411 Ams_Utility_Pvt.debug_message('Private API: Calling update table handler');
412 END IF;
413
414 -- Invoke table handler(AMS_MET_TPL_ASSOCS_PKG.Update_Row)
415 Ams_Met_Tpl_Assocs_Pkg.Update_Row(
416 p_metric_tpl_assoc_id => p_met_tpl_assoc_rec.metric_tpl_assoc_id,
417 p_last_update_date => SYSDATE,
418 p_last_updated_by => FND_GLOBAL.USER_ID,
419 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
420 p_object_version_number => x_object_version_number,
421 p_metric_tpl_header_id => p_met_tpl_assoc_rec.metric_tpl_header_id,
422 p_association_type => p_met_tpl_assoc_rec.association_type,
423 p_used_by_id => p_met_tpl_assoc_rec.used_by_id,
424 p_used_by_code => p_met_tpl_assoc_rec.used_by_code,
425 p_enabled_flag => p_met_tpl_assoc_rec.enabled_flag);
426
427 --
428 -- End of API body.
429 --
430
431 -- Standard check for p_commit
432 IF FND_API.to_Boolean( p_commit )
433 THEN
434 COMMIT WORK;
435 END IF;
436
437 -- Debug Message
438 IF (AMS_DEBUG_HIGH_ON) THEN
439
440 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || ': end');
441 END IF;
442
443 -- Standard call to get message count and if count is 1, get message info.
444 FND_MSG_PUB.Count_And_Get
445 (p_count => x_msg_count,
446 p_data => x_msg_data
447 );
448 EXCEPTION
449
450 WHEN Ams_Utility_Pvt.resource_locked THEN
451 x_return_status := FND_API.g_ret_sts_error;
452 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
453
454 WHEN FND_API.G_EXC_ERROR THEN
455 ROLLBACK TO UPDATE_Met_Tpl_Assoc_PVT;
456 x_return_status := FND_API.G_RET_STS_ERROR;
457 -- Standard call to get message count and if count=1, get the message
458 FND_MSG_PUB.Count_And_Get (
459 p_encoded => FND_API.G_FALSE,
460 p_count => x_msg_count,
461 p_data => x_msg_data
462 );
463
464 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
465 ROLLBACK TO UPDATE_Met_Tpl_Assoc_PVT;
466 x_return_status := FND_API.G_RET_STS_UNEXP_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 OTHERS THEN
475 ROLLBACK TO UPDATE_Met_Tpl_Assoc_PVT;
476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
477 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
478 THEN
479 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
480 END IF;
481 -- Standard call to get message count and if count=1, get the message
482 FND_MSG_PUB.Count_And_Get (
483 p_encoded => FND_API.G_FALSE,
484 p_count => x_msg_count,
485 p_data => x_msg_data
486 );
487 END Update_Met_Tpl_Assoc;
488
489
490 PROCEDURE Delete_Met_Tpl_Assoc(
491 p_api_version_number IN NUMBER,
492 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
493 p_commit IN VARCHAR2 := FND_API.G_FALSE,
494 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
495 x_return_status OUT NOCOPY VARCHAR2,
496 x_msg_count OUT NOCOPY NUMBER,
497 x_msg_data OUT NOCOPY VARCHAR2,
498 p_METRIC_TPL_ASSOC_ID IN NUMBER,
499 p_object_version_number IN NUMBER
500 )
501
502 IS
503 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Met_Tpl_Assoc';
504 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
505 l_object_version_number NUMBER;
506
507 BEGIN
508 -- Standard Start of API savepoint
509 SAVEPOINT DELETE_Met_Tpl_Assoc_PVT;
510
511 -- Standard call to check for call compatibility.
512 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
513 p_api_version_number,
514 l_api_name,
515 G_PKG_NAME)
516 THEN
517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
518 END IF;
519
520 -- Initialize message list if p_init_msg_list is set to TRUE.
521 IF FND_API.to_Boolean( p_init_msg_list )
522 THEN
523 FND_MSG_PUB.initialize;
524 END IF;
525
526 -- Debug Message
527 IF (AMS_DEBUG_HIGH_ON) THEN
528
529 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || 'start');
530 END IF;
531
532 --sunkumar 30/01/2003
533 --check if we are trying to delete associations for a seeded metric template
534 IF p_METRIC_TPL_ASSOC_ID < 10000 THEN
535 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
536 THEN
537 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_METR_TPL_SEEDED');
538 END IF;
539
540 RAISE FND_API.G_EXC_ERROR;
541 END IF;
542
543
544
545 -- Initialize API return status to SUCCESS
546 x_return_status := FND_API.G_RET_STS_SUCCESS;
547
548 --
549 -- Api body
550 --
551 -- Debug Message
552 IF (AMS_DEBUG_HIGH_ON) THEN
553
554 Ams_Utility_Pvt.debug_message( 'Private API: Calling delete table handler');
555 END IF;
556
557 -- Invoke table handler(AMS_MET_TPL_ASSOCS_PKG.Delete_Row)
558 Ams_Met_Tpl_Assocs_Pkg.Delete_Row(
559 p_METRIC_TPL_ASSOC_ID => p_METRIC_TPL_ASSOC_ID);
560 --
561 -- End of API body
562 --
563
564 -- Standard check for p_commit
565 IF FND_API.to_Boolean( p_commit )
566 THEN
567 COMMIT WORK;
568 END IF;
569
570
571 -- Debug Message
572 IF (AMS_DEBUG_HIGH_ON) THEN
573
574 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || 'end');
575 END IF;
576
577 -- Standard call to get message count and if count is 1, get message info.
578 FND_MSG_PUB.Count_And_Get
579 (p_count => x_msg_count,
580 p_data => x_msg_data
581 );
582 EXCEPTION
583
584 WHEN Ams_Utility_Pvt.resource_locked THEN
585 x_return_status := FND_API.g_ret_sts_error;
586 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
587
588 WHEN FND_API.G_EXC_ERROR THEN
589 ROLLBACK TO DELETE_Met_Tpl_Assoc_PVT;
590 x_return_status := FND_API.G_RET_STS_ERROR;
591 -- Standard call to get message count and if count=1, get the message
592 FND_MSG_PUB.Count_And_Get (
593 p_encoded => FND_API.G_FALSE,
594 p_count => x_msg_count,
595 p_data => x_msg_data
596 );
597
598 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
599 ROLLBACK TO DELETE_Met_Tpl_Assoc_PVT;
600 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
601 -- Standard call to get message count and if count=1, get the message
602 FND_MSG_PUB.Count_And_Get (
603 p_encoded => FND_API.G_FALSE,
604 p_count => x_msg_count,
605 p_data => x_msg_data
606 );
607
608 WHEN OTHERS THEN
609 ROLLBACK TO DELETE_Met_Tpl_Assoc_PVT;
610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
611 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
612 THEN
613 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
614 END IF;
615 -- Standard call to get message count and if count=1, get the message
616 FND_MSG_PUB.Count_And_Get (
617 p_encoded => FND_API.G_FALSE,
618 p_count => x_msg_count,
619 p_data => x_msg_data
620 );
621 END Delete_Met_Tpl_Assoc;
622
623
624 -- Hint: Primary key needs to be returned.
625 PROCEDURE Lock_Met_Tpl_Assoc(
626 p_api_version_number IN NUMBER,
627 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
628
629 x_return_status OUT NOCOPY VARCHAR2,
630 x_msg_count OUT NOCOPY NUMBER,
631 x_msg_data OUT NOCOPY VARCHAR2,
632
633 p_METRIC_TPL_ASSOC_ID IN NUMBER,
634 p_object_version IN NUMBER
635 )
636
637 IS
638 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Met_Tpl_Assoc';
639 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
640 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
641 l_METRIC_TPL_ASSOC_ID NUMBER;
642
643 CURSOR c_Met_Tpl_Assoc IS
644 SELECT METRIC_TPL_ASSOC_ID
645 FROM AMS_MET_TPL_ASSOCS
646 WHERE METRIC_TPL_ASSOC_ID = p_METRIC_TPL_ASSOC_ID
647 AND object_version_number = p_object_version
648 FOR UPDATE NOWAIT;
649
650 BEGIN
651
652 -- Debug Message
653 IF (AMS_DEBUG_HIGH_ON) THEN
654
655 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || 'start');
656 END IF;
657
658 -- Initialize message list if p_init_msg_list is set to TRUE.
659 IF FND_API.to_Boolean( p_init_msg_list )
660 THEN
661 FND_MSG_PUB.initialize;
662 END IF;
663
664 -- Standard call to check for call compatibility.
665 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
666 p_api_version_number,
667 l_api_name,
668 G_PKG_NAME)
669 THEN
670 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
671 END IF;
672
673
674 -- Initialize API return status to SUCCESS
675 x_return_status := FND_API.G_RET_STS_SUCCESS;
676
677
678 ------------------------ lock -------------------------
679
680 IF (AMS_DEBUG_HIGH_ON) THEN
681
682
683
684 Ams_Utility_Pvt.debug_message(l_full_name||': start');
685
686 END IF;
687 OPEN c_Met_Tpl_Assoc;
688
689 FETCH c_Met_Tpl_Assoc INTO l_METRIC_TPL_ASSOC_ID;
690
691 IF (c_Met_Tpl_Assoc%NOTFOUND) THEN
692 CLOSE c_Met_Tpl_Assoc;
693 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
694 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
695 FND_MSG_PUB.ADD;
696 END IF;
697 RAISE FND_API.g_exc_error;
698 END IF;
699
700 CLOSE c_Met_Tpl_Assoc;
701
702 -------------------- finish --------------------------
703 FND_MSG_PUB.count_and_get(
704 p_encoded => FND_API.g_false,
705 p_count => x_msg_count,
706 p_data => x_msg_data);
707 IF (AMS_DEBUG_HIGH_ON) THEN
708
709 Ams_Utility_Pvt.debug_message(l_full_name ||': end');
710 END IF;
711 EXCEPTION
712
713 WHEN Ams_Utility_Pvt.resource_locked THEN
714 x_return_status := FND_API.g_ret_sts_error;
715 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
716
717 WHEN FND_API.G_EXC_ERROR THEN
718 ROLLBACK TO LOCK_Met_Tpl_Assoc_PVT;
719 x_return_status := FND_API.G_RET_STS_ERROR;
720 -- Standard call to get message count and if count=1, get the message
721 FND_MSG_PUB.Count_And_Get (
722 p_encoded => FND_API.G_FALSE,
723 p_count => x_msg_count,
724 p_data => x_msg_data
725 );
726
727 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
728 ROLLBACK TO LOCK_Met_Tpl_Assoc_PVT;
729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
730 -- Standard call to get message count and if count=1, get the message
731 FND_MSG_PUB.Count_And_Get (
732 p_encoded => FND_API.G_FALSE,
733 p_count => x_msg_count,
734 p_data => x_msg_data
735 );
736
737 WHEN OTHERS THEN
738 ROLLBACK TO LOCK_Met_Tpl_Assoc_PVT;
739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
740 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
741 THEN
742 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
743 END IF;
744 -- Standard call to get message count and if count=1, get the message
745 FND_MSG_PUB.Count_And_Get (
746 p_encoded => FND_API.G_FALSE,
747 p_count => x_msg_count,
748 p_data => x_msg_data
749 );
750 END Lock_Met_Tpl_Assoc;
751
752
753 PROCEDURE check_met_tpl_assoc_uk_items(
754 p_met_tpl_assoc_rec IN met_tpl_assoc_rec_type,
755 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
756 x_return_status OUT NOCOPY VARCHAR2)
757 IS
758 l_valid_flag VARCHAR2(1);
759 CURSOR c_dup_assoc (p_met_tpl_hdr_id NUMBER, p_assoc_type VARCHAR2,
760 p_used_by_id NUMBER, p_used_by_code VARCHAR2) IS
761 SELECT FND_API.G_FALSE
762 FROM AMS_MET_TPL_ASSOCS
763 WHERE METRIC_TPL_HEADER_ID = p_met_tpl_hdr_id
764 AND ASSOCIATION_TYPE = p_assoc_type
765 AND NVL(p_used_by_id,-1) = NVL(used_by_id,-1)
766 AND NVL(p_used_by_code, '0') = NVL(used_by_code, '0');
767
768 BEGIN
769 x_return_status := FND_API.g_ret_sts_success;
770 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
771 l_valid_flag := Ams_Utility_Pvt.check_uniqueness(
772 'AMS_MET_TPL_ASSOCS',
773 'METRIC_TPL_ASSOC_ID = ' || p_met_tpl_assoc_rec.METRIC_TPL_ASSOC_ID
774 );
775 -- ELSE
776 -- l_valid_flag := Ams_Utility_Pvt.check_uniqueness(
777 -- 'AMS_MET_TPL_ASSOCS',
778 -- 'METRIC_TPL_ASSOC_ID = ' || p_met_tpl_assoc_rec.METRIC_TPL_ASSOC_ID ||
779 -- ' AND METRIC_TPL_ASSOC_ID <> ' || p_met_tpl_assoc_rec.METRIC_TPL_ASSOC_ID
780 -- );
781 END IF;
782
783 IF l_valid_flag = FND_API.g_false THEN
784 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_METRIC_TPL_ASSOC_ID_DUPLICATE');
785 x_return_status := FND_API.g_ret_sts_error;
786 RETURN;
787 END IF;
788
789 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
790 OPEN c_dup_assoc(p_met_tpl_assoc_rec.METRIC_TPL_HEADER_ID,
791 p_met_tpl_assoc_rec.ASSOCIATION_TYPE,
792 p_met_tpl_assoc_rec.used_by_id,
793 p_met_tpl_assoc_rec.used_by_code);
794 FETCH c_dup_assoc INTO l_valid_flag;
795 CLOSE c_dup_assoc;
796 IF l_valid_flag = FND_API.g_false THEN
797 Ams_Utility_Pvt.Error_Message(p_message_name =>
798 'AMS_METRIC_TPL_ASSOC_VALUE_DUP');
799 x_return_status := FND_API.g_ret_sts_error;
800 RETURN;
801 END IF;
802 END IF;
803
804 END check_met_tpl_assoc_uk_items;
805
806 PROCEDURE check_met_tpl_assoc_req_items(
807 p_met_tpl_assoc_rec IN met_tpl_assoc_rec_type,
808 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
809 x_return_status OUT NOCOPY VARCHAR2
810 )
811 IS
812 BEGIN
813 x_return_status := FND_API.g_ret_sts_success;
814
815 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
816
817 -- IF p_met_tpl_assoc_rec.metric_tpl_assoc_id = FND_API.g_miss_num OR
818 -- p_met_tpl_assoc_rec.metric_tpl_assoc_id IS NULL THEN
819 -- FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
820 -- FND_MESSAGE.set_token('MISS_FIELD','METRIC_TPL_ASSOC_ID');
821 -- x_return_status := FND_API.g_ret_sts_error;
822 -- RETURN;
823 -- END IF;
824 --
825 -- IF p_met_tpl_assoc_rec.last_update_date = FND_API.g_miss_date OR p_met_tpl_assoc_rec.last_update_date IS NULL THEN
826 -- FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
827 -- FND_MESSAGE.set_token('MISS_FIELD','LAST_UPDATE_DATE');
828 -- x_return_status := FND_API.g_ret_sts_error;
829 -- RETURN;
830 -- END IF;
831 --
832 -- IF p_met_tpl_assoc_rec.last_updated_by = FND_API.g_miss_num OR p_met_tpl_assoc_rec.last_updated_by IS NULL THEN
833 -- FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
834 -- FND_MESSAGE.set_token('MISS_FIELD','LAST_UPDATED_BY');
835 -- x_return_status := FND_API.g_ret_sts_error;
836 -- RETURN;
837 -- END IF;
838 --
839 -- IF p_met_tpl_assoc_rec.creation_date = FND_API.g_miss_date OR p_met_tpl_assoc_rec.creation_date IS NULL THEN
840 -- FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
841 -- FND_MESSAGE.set_token('MISS_FIELD','CREATION_DATE');
842 -- x_return_status := FND_API.g_ret_sts_error;
843 -- RETURN;
844 -- END IF;
845 --
846 -- IF p_met_tpl_assoc_rec.created_by = FND_API.g_miss_num OR p_met_tpl_assoc_rec.created_by IS NULL THEN
847 -- FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
848 -- FND_MESSAGE.set_token('MISS_FIELD','CREATED_BY');
849 -- x_return_status := FND_API.g_ret_sts_error;
850 -- RETURN;
851 -- END IF;
852 --
853 IF p_met_tpl_assoc_rec.metric_tpl_header_id = FND_API.g_miss_num OR p_met_tpl_assoc_rec.metric_tpl_header_id IS NULL THEN
854 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
855 FND_MESSAGE.set_token('MISS_FIELD','METRIC_TPL_HEADER_ID');
856 x_return_status := FND_API.g_ret_sts_error;
857 RETURN;
858 END IF;
859
860 IF p_met_tpl_assoc_rec.association_type = FND_API.g_miss_char OR p_met_tpl_assoc_rec.association_type IS NULL THEN
861 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
862 FND_MESSAGE.set_token('MISS_FIELD','ASSOCIATION_TYPE');
863 x_return_status := FND_API.g_ret_sts_error;
864 RETURN;
865 END IF;
866
867 IF p_met_tpl_assoc_rec.enabled_flag = FND_API.g_miss_char OR p_met_tpl_assoc_rec.enabled_flag IS NULL THEN
868 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
869 FND_MESSAGE.set_token('MISS_FIELD','ENABLED_FLAG');
870 x_return_status := FND_API.g_ret_sts_error;
871 RETURN;
872 END IF;
873 ELSE
874
875 IF p_met_tpl_assoc_rec.metric_tpl_assoc_id IS NULL THEN
876 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_MTA_NO_MTA_ID');
877 x_return_status := FND_API.g_ret_sts_error;
878 RETURN;
879 END IF;
880
881 -- IF p_met_tpl_assoc_rec.last_update_date IS NULL THEN
882 -- Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_met_tpl_assoc_NO_last_update_date');
883 -- x_return_status := FND_API.g_ret_sts_error;
884 -- RETURN;
885 -- END IF;
886 --
887 -- IF p_met_tpl_assoc_rec.last_updated_by IS NULL THEN
888 -- Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_met_tpl_assoc_NO_last_updated_by');
889 -- x_return_status := FND_API.g_ret_sts_error;
890 -- RETURN;
891 -- END IF;
892 --
893 -- IF p_met_tpl_assoc_rec.creation_date IS NULL THEN
894 -- Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_met_tpl_assoc_NO_creation_date');
895 -- x_return_status := FND_API.g_ret_sts_error;
896 -- RETURN;
897 -- END IF;
898 --
899 -- IF p_met_tpl_assoc_rec.created_by IS NULL THEN
900 -- Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_met_tpl_assoc_NO_created_by');
901 -- x_return_status := FND_API.g_ret_sts_error;
902 -- RETURN;
903 -- END IF;
904
905 IF p_met_tpl_assoc_rec.metric_tpl_header_id IS NULL THEN
906 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_MTA_NO_MTH_ID');
907 x_return_status := FND_API.g_ret_sts_error;
908 RETURN;
909 END IF;
910
911 IF p_met_tpl_assoc_rec.association_type IS NULL THEN
912 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_MTA_NO_ASSOCIATION_TYPE');
913 x_return_status := FND_API.g_ret_sts_error;
914 RETURN;
915 END IF;
916
917 IF p_met_tpl_assoc_rec.enabled_flag IS NULL THEN
918 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_MTA_NO_ENABLED_FLAG');
919 x_return_status := FND_API.g_ret_sts_error;
920 RETURN;
921 END IF;
922 END IF;
923
924 END check_met_tpl_assoc_req_items;
925
926 PROCEDURE check_met_tpl_assoc_FK_items(
927 p_met_tpl_assoc_rec IN met_tpl_assoc_rec_type,
928 x_return_status OUT NOCOPY VARCHAR2
929 )
930 IS
931 CURSOR c_check_header_id(p_METRIC_TPL_HEADER_ID NUMBER) IS
932 SELECT 1 FROM AMS_MET_TPL_HEADERS_VL
933 WHERE METRIC_TPL_HEADER_ID = p_METRIC_TPL_HEADER_ID;
934
935 CURSOR c_check_custom_setup(p_used_by_id NUMBER) IS
936 SELECT 1 FROM ams_custom_setups_vl
937 WHERE custom_setup_id = p_used_by_id;
938
939 CURSOR c_check_object_type(p_used_by_code VARCHAR2) IS
940 SELECT 1 FROM ams_lookups
941 WHERE lookup_code = p_used_by_code
942 AND lookup_type = 'AMS_SYS_ARC_QUALIFIER';
943
944 l_dummy NUMBER;
945 BEGIN
946 x_return_status := FND_API.g_ret_sts_success;
947
948 -- Enter custom code here
949
950 -- Validate metric_tpl_header_id exists.
951 OPEN c_check_header_id(p_met_tpl_assoc_rec.metric_tpl_header_id);
952 FETCH c_check_header_id INTO l_dummy;
953 IF c_check_header_id%NOTFOUND THEN
954 Ams_Utility_Pvt.error_message(p_message_name => 'AMS_MTA_BAD_HEADER_ID');
955 x_return_status := FND_API.g_ret_sts_error;
956 END IF;
957 CLOSE c_check_header_id;
958
959 IF p_met_tpl_assoc_rec.association_type = 'CUSTOM_SETUP' THEN
960 OPEN c_check_custom_setup(p_met_tpl_assoc_rec.used_by_id);
961 FETCH c_check_custom_setup INTO l_dummy;
962 IF c_check_custom_setup%NOTFOUND THEN
963 Ams_Utility_Pvt.error_message(p_message_name => 'AMS_MTA_BAD_CUSTOM_SETUP');
964 x_return_status := FND_API.g_ret_sts_error;
965 END IF;
966 CLOSE c_check_custom_setup;
967 IF p_met_tpl_assoc_rec.used_by_code IS NOT NULL THEN
968 Ams_Utility_Pvt.error_message(p_message_name => 'AMS_MTA_BAD_USED_BY');
969 x_return_status := FND_API.g_ret_sts_error;
970 END IF;
971 ELSIF p_met_tpl_assoc_rec.association_type = 'OBJECT_TYPE' THEN
972 OPEN c_check_object_type(p_met_tpl_assoc_rec.used_by_code);
973 FETCH c_check_object_type INTO l_dummy;
974 IF c_check_object_type%NOTFOUND THEN
975 Ams_Utility_Pvt.error_message(p_message_name => 'AMS_MTA_BAD_OBJECT_TYPE');
976 x_return_status := FND_API.g_ret_sts_error;
977 END IF;
978 CLOSE c_check_object_type;
979 IF p_met_tpl_assoc_rec.used_by_id IS NOT NULL THEN
980 Ams_Utility_Pvt.error_message(p_message_name => 'AMS_MTA_BAD_USED_BY');
981 x_return_status := FND_API.g_ret_sts_error;
982 END IF;
983 END IF;
984
985 END check_met_tpl_assoc_FK_items;
986
987 PROCEDURE check_met_tpl_assoc_lkp_items(
988 p_met_tpl_assoc_rec IN met_tpl_assoc_rec_type,
989 x_return_status OUT NOCOPY VARCHAR2
990 )
991 IS
992 BEGIN
993 x_return_status := FND_API.g_ret_sts_success;
994
995 -- Enter custom code here
996
997 -- Validate association types.
998 IF Ams_Utility_Pvt.check_lookup_exists(p_lookup_type => 'AMS_METRIC_TPL_ASSOC_TYPES',
999 p_lookup_code => p_met_tpl_assoc_rec.association_type) = FND_API.G_FALSE THEN
1000 Ams_Utility_Pvt.error_message(p_message_name => 'AMS_MTA_BAD_ASSOC_TYPE');
1001 x_return_status := FND_API.g_ret_sts_error;
1002 END IF;
1003
1004 IF Ams_Utility_Pvt.is_y_or_n(p_met_tpl_assoc_rec.enabled_flag) = FND_API.G_FALSE THEN
1005 Ams_Utility_Pvt.error_message(p_message_name => 'AMS_MTA_BAD_ENABLED_FLAG');
1006 x_return_status := FND_API.g_ret_sts_error;
1007 END IF;
1008
1009 END check_met_tpl_assoc_lkp_items;
1010
1011 PROCEDURE Check_met_tpl_assoc_Items (
1012 P_met_tpl_assoc_rec IN met_tpl_assoc_rec_type,
1013 p_validation_mode IN VARCHAR2,
1014 x_return_status OUT NOCOPY VARCHAR2
1015 )
1016 IS
1017 BEGIN
1018
1019 -- Check Items Uniqueness API calls
1020
1021 check_met_tpl_assoc_uk_items(
1022 p_met_tpl_assoc_rec => p_met_tpl_assoc_rec,
1023 p_validation_mode => p_validation_mode,
1024 x_return_status => x_return_status);
1025 IF x_return_status <> FND_API.g_ret_sts_success THEN
1026 RETURN;
1027 END IF;
1028
1029 -- Check Items Required/NOT NULL API calls
1030
1031 check_met_tpl_assoc_req_items(
1032 p_met_tpl_assoc_rec => p_met_tpl_assoc_rec,
1033 p_validation_mode => p_validation_mode,
1034 x_return_status => x_return_status);
1035 IF x_return_status <> FND_API.g_ret_sts_success THEN
1036 RETURN;
1037 END IF;
1038 -- Check Items Foreign Keys API calls
1039
1040 check_met_tpl_assoc_FK_items(
1041 p_met_tpl_assoc_rec => p_met_tpl_assoc_rec,
1042 x_return_status => x_return_status);
1043 IF x_return_status <> FND_API.g_ret_sts_success THEN
1044 RETURN;
1045 END IF;
1046 -- Check Items Lookups
1047
1048 check_met_tpl_assoc_lkp_items(
1049 p_met_tpl_assoc_rec => p_met_tpl_assoc_rec,
1050 x_return_status => x_return_status);
1051 IF x_return_status <> FND_API.g_ret_sts_success THEN
1052 RETURN;
1053 END IF;
1054
1055 END Check_met_tpl_assoc_Items;
1056
1057
1058
1059 PROCEDURE Complete_met_tpl_assoc_Rec (
1060 p_ref_met_tpl_assoc_rec IN met_tpl_assoc_rec_type,
1061 x_tar_met_tpl_assoc_rec OUT NOCOPY met_tpl_assoc_rec_type)
1062 IS
1063 l_return_status VARCHAR2(1);
1064 /*
1065 CURSOR c_complete IS
1066 SELECT *
1067 FROM ams_met_tpl_assocs
1068 WHERE METRIC_TPL_ASSOC_ID = p_met_tpl_assoc_rec.METRIC_TPL_ASSOC_ID;
1069 l_met_tpl_assoc_rec c_complete%ROWTYPE;
1070 */
1071 BEGIN
1072 --x_complete_rec := p_met_tpl_assoc_rec;
1073
1074
1075 --OPEN c_complete;
1076 --FETCH c_complete INTO l_met_tpl_assoc_rec;
1077 --CLOSE c_complete;
1078
1079 -- -- metric_tpl_assoc_id
1080 -- IF p_met_tpl_assoc_rec.metric_tpl_assoc_id = FND_API.g_miss_num THEN
1081 -- x_complete_rec.metric_tpl_assoc_id := l_met_tpl_assoc_rec.metric_tpl_assoc_id;
1082 -- END IF;
1083 --
1084 -- -- last_update_date
1085 -- IF p_met_tpl_assoc_rec.last_update_date = FND_API.g_miss_date THEN
1086 -- x_complete_rec.last_update_date := l_met_tpl_assoc_rec.last_update_date;
1087 -- END IF;
1088 --
1089 -- -- last_updated_by
1090 -- IF p_met_tpl_assoc_rec.last_updated_by = FND_API.g_miss_num THEN
1091 -- x_complete_rec.last_updated_by := l_met_tpl_assoc_rec.last_updated_by;
1092 -- END IF;
1093 --
1094 -- -- creation_date
1095 -- IF p_met_tpl_assoc_rec.creation_date = FND_API.g_miss_date THEN
1096 -- x_complete_rec.creation_date := l_met_tpl_assoc_rec.creation_date;
1097 -- END IF;
1098 --
1099 -- -- created_by
1100 -- IF p_met_tpl_assoc_rec.created_by = FND_API.g_miss_num THEN
1101 -- x_complete_rec.created_by := l_met_tpl_assoc_rec.created_by;
1102 -- END IF;
1103 --
1104 -- -- last_update_login
1105 -- IF p_met_tpl_assoc_rec.last_update_login = FND_API.g_miss_num THEN
1106 -- x_complete_rec.last_update_login := l_met_tpl_assoc_rec.last_update_login;
1107 -- END IF;
1108 --
1109 -- -- object_version_number
1110 -- IF p_met_tpl_assoc_rec.object_version_number = FND_API.g_miss_num THEN
1111 -- x_complete_rec.object_version_number := l_met_tpl_assoc_rec.object_version_number;
1112 -- END IF;
1113
1114 -- metric_tpl_header_id
1115 IF x_tar_met_tpl_assoc_rec.metric_tpl_header_id = FND_API.g_miss_num THEN
1116 x_tar_met_tpl_assoc_rec.metric_tpl_header_id := p_ref_met_tpl_assoc_rec.metric_tpl_header_id;
1117 END IF;
1118
1119 -- association_type
1120 IF x_tar_met_tpl_assoc_rec.association_type = FND_API.g_miss_char THEN
1121 x_tar_met_tpl_assoc_rec.association_type := p_ref_met_tpl_assoc_rec.association_type;
1122 END IF;
1123
1124 -- used_by_id
1125 IF x_tar_met_tpl_assoc_rec.used_by_id = FND_API.g_miss_num THEN
1126 x_tar_met_tpl_assoc_rec.used_by_id := p_ref_met_tpl_assoc_rec.used_by_id;
1127 END IF;
1128
1129 -- used_by_code
1130 IF x_tar_met_tpl_assoc_rec.used_by_code = FND_API.g_miss_char THEN
1131 x_tar_met_tpl_assoc_rec.used_by_code := p_ref_met_tpl_assoc_rec.used_by_code;
1132 END IF;
1133
1134 -- enabled_flag
1135 IF x_tar_met_tpl_assoc_rec.enabled_flag = FND_API.g_miss_char THEN
1136 x_tar_met_tpl_assoc_rec.enabled_flag := p_ref_met_tpl_assoc_rec.enabled_flag;
1137 END IF;
1138
1139 -- Note: Developers need to modify the procedure
1140 -- to handle any business specific requirements.
1141 END Complete_met_tpl_assoc_Rec;
1142
1143 PROCEDURE Validate_met_tpl_assoc(
1144 p_api_version_number IN NUMBER,
1145 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1146 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1147 p_met_tpl_assoc_rec IN met_tpl_assoc_rec_type,
1148 p_validation_mode IN VARCHAR2,
1149 x_return_status OUT NOCOPY VARCHAR2,
1150 x_msg_count OUT NOCOPY NUMBER,
1151 x_msg_data OUT NOCOPY VARCHAR2
1152 )
1153 IS
1154 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Met_Tpl_Assoc';
1155 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1156 l_object_version_number NUMBER;
1157 l_met_tpl_assoc_rec Ams_Met_Tpl_Assoc_Pvt.met_tpl_assoc_rec_type;
1158
1159 BEGIN
1160 -- Standard Start of API savepoint
1161 SAVEPOINT VALIDATE_Met_Tpl_Assoc_;
1162
1163 -- Standard call to check for call compatibility.
1164 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1165 p_api_version_number,
1166 l_api_name,
1167 G_PKG_NAME)
1168 THEN
1169 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1170 END IF;
1171
1172 -- Initialize message list if p_init_msg_list is set to TRUE.
1173 IF FND_API.to_Boolean( p_init_msg_list )
1174 THEN
1175 FND_MSG_PUB.initialize;
1176 END IF;
1177 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1178 Check_met_tpl_assoc_Items(
1179 p_met_tpl_assoc_rec => p_met_tpl_assoc_rec,
1180 p_validation_mode => p_validation_mode,
1181 x_return_status => x_return_status
1182 );
1183
1184 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1185 RAISE FND_API.G_EXC_ERROR;
1186 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1188 END IF;
1189 END IF;
1190
1191 -- Complete_met_tpl_assoc_Rec(
1192 -- p_met_tpl_assoc_rec => p_met_tpl_assoc_rec,
1193 -- x_complete_rec => l_met_tpl_assoc_rec
1194 -- );
1195
1196 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1197 Validate_met_tpl_assoc_Rec(
1198 p_api_version_number => 1.0,
1199 p_init_msg_list => FND_API.G_FALSE,
1200 x_return_status => x_return_status,
1201 x_msg_count => x_msg_count,
1202 x_msg_data => x_msg_data,
1203 p_met_tpl_assoc_rec => l_met_tpl_assoc_rec);
1204
1205 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1206 RAISE FND_API.G_EXC_ERROR;
1207 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1208 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1209 END IF;
1210 END IF;
1211
1212 -- Debug Message
1213 IF (AMS_DEBUG_HIGH_ON) THEN
1214
1215 Ams_Utility_Pvt.debug_message('PRIVATE API: ' || l_api_name || 'START');
1216 END IF;
1217
1218 -- Initialize API return status to SUCCESS
1219 x_return_status := FND_API.G_RET_STS_SUCCESS;
1220
1221 -- Debug Message
1222 IF (AMS_DEBUG_HIGH_ON) THEN
1223
1224 Ams_Utility_Pvt.debug_message('PRIVATE API: ' || l_api_name || 'END');
1225 END IF;
1226
1227 -- Standard call to get message count and if count is 1, get message info.
1228 FND_MSG_PUB.Count_And_Get
1229 (p_count => x_msg_count,
1230 p_data => x_msg_data
1231 );
1232 EXCEPTION
1233
1234 WHEN Ams_Utility_Pvt.resource_locked THEN
1235 x_return_status := FND_API.g_ret_sts_error;
1236 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1237
1238 WHEN FND_API.G_EXC_ERROR THEN
1239 ROLLBACK TO VALIDATE_Met_Tpl_Assoc_;
1240 x_return_status := FND_API.G_RET_STS_ERROR;
1241 -- Standard call to get message count and if count=1, get the message
1242 FND_MSG_PUB.Count_And_Get (
1243 p_encoded => FND_API.G_FALSE,
1244 p_count => x_msg_count,
1245 p_data => x_msg_data
1246 );
1247
1248 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1249 ROLLBACK TO VALIDATE_Met_Tpl_Assoc_;
1250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1251 -- Standard call to get message count and if count=1, get the message
1252 FND_MSG_PUB.Count_And_Get (
1253 p_encoded => FND_API.G_FALSE,
1254 p_count => x_msg_count,
1255 p_data => x_msg_data
1256 );
1257
1258 WHEN OTHERS THEN
1259 ROLLBACK TO VALIDATE_Met_Tpl_Assoc_;
1260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1261 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1262 THEN
1263 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1264 END IF;
1265 -- Standard call to get message count and if count=1, get the message
1266 FND_MSG_PUB.Count_And_Get (
1267 p_encoded => FND_API.G_FALSE,
1268 p_count => x_msg_count,
1269 p_data => x_msg_data
1270 );
1271 END Validate_Met_Tpl_Assoc;
1272
1273
1274 PROCEDURE Validate_met_tpl_assoc_rec(
1275 p_api_version_number IN NUMBER,
1276 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1277 x_return_status OUT NOCOPY VARCHAR2,
1278 x_msg_count OUT NOCOPY NUMBER,
1279 x_msg_data OUT NOCOPY VARCHAR2,
1280 p_met_tpl_assoc_rec IN met_tpl_assoc_rec_type
1281 )
1282 IS
1283 BEGIN
1284 -- Initialize message list if p_init_msg_list is set to TRUE.
1285 IF FND_API.to_Boolean( p_init_msg_list )
1286 THEN
1287 FND_MSG_PUB.initialize;
1288 END IF;
1289
1290 -- Initialize API return status to SUCCESS
1291 x_return_status := FND_API.G_RET_STS_SUCCESS;
1292
1293 -- Hint: Validate data
1294 -- If data not valid
1295 -- THEN
1296 -- x_return_status := FND_API.G_RET_STS_ERROR;
1297
1298 -- Debug Message
1299 IF (AMS_DEBUG_HIGH_ON) THEN
1300
1301 Ams_Utility_Pvt.debug_message('PRIVATE API: Validate_met_tpl_assoc_rec');
1302 END IF;
1303 -- Standard call to get message count and if count is 1, get message info.
1304 FND_MSG_PUB.Count_And_Get
1305 (p_count => x_msg_count,
1306 p_data => x_msg_data
1307 );
1308 END Validate_met_tpl_assoc_Rec;
1309
1310 END Ams_Met_Tpl_Assoc_Pvt;