[Home] [Help]
PACKAGE BODY: APPS.AMS_APPR_HIST_PVT
Source
1 PACKAGE BODY AMS_Appr_Hist_PVT as
2 /* $Header: amsvaphb.pls 115.1 2002/12/12 12:56:48 vmodur noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Appr_Hist_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- 12-DEC-2002 VMODUR Fixed GSCC Warning related to l_org_id
12 --
13 -- NOTE
14 --
15 -- This Api is generated with Latest version of
16 -- Rosetta, where g_miss indicates NULL and
17 -- NULL indicates missing value. Rosetta Version 1.55
18 -- End of Comments
19 -- ===============================================================
20
21
22 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Appr_Hist_PVT';
23 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvaphb.pls';
24
25 -- G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
26 -- G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
27 --
28 -- Foreward Procedure Declarations
29 --
30
31 PROCEDURE Default_Appr_Hist_Items (
32 p_appr_hist_rec IN appr_hist_rec_type ,
33 x_appr_hist_rec OUT NOCOPY appr_hist_rec_type
34 ) ;
35
36
37
38 -- Hint: Primary key needs to be returned.
39 -- ==============================================================================
40 -- Start of Comments
41 -- ==============================================================================
42 -- API Name
43 -- Create_Appr_Hist
44 -- Type
45 -- Private
46 -- Pre-Req
47 --
48 -- Parameters
49 --
50 -- IN
51 -- p_api_version_number IN NUMBER Required
52 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
53 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
54 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
55 -- p_appr_hist_rec IN appr_hist_rec_type Required
56 --
57 -- OUT
58 -- x_return_status OUT VARCHAR2
59 -- x_msg_count OUT NUMBER
60 -- x_msg_data OUT VARCHAR2
61 -- Version : Current version 1.0
62 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
63 -- and basic operation, developer must manually add parameters and business logic as necessary.
64 --
65 -- History
66 --
67 -- NOTE
68 --
69 -- End of Comments
70 -- ==============================================================================
71
72 PROCEDURE Create_Appr_Hist(
73 p_api_version_number IN NUMBER,
74 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
75 p_commit IN VARCHAR2 := FND_API.G_FALSE,
76 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
77
78 x_return_status OUT NOCOPY VARCHAR2,
79 x_msg_count OUT NOCOPY NUMBER,
80 x_msg_data OUT NOCOPY VARCHAR2,
81
82 p_appr_hist_rec IN appr_hist_rec_type := g_miss_appr_hist_rec
83 )
84
85 IS
86 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Appr_Hist';
87 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
88 l_return_status_full VARCHAR2(1);
89 l_object_version_number NUMBER := 1;
90 -- l_org_id NUMBER := FND_API.G_MISS_NUM;
91 l_object_id NUMBER;
92 l_dummy NUMBER;
93
94 BEGIN
95 -- Standard Start of API savepoint
96 SAVEPOINT create_appr_hist_pvt;
97
98 -- Standard call to check for call compatibility.
99 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
100 p_api_version_number,
101 l_api_name,
102 G_PKG_NAME)
103 THEN
104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
105 END IF;
106
107
108 -- Initialize message list if p_init_msg_list is set to TRUE.
109 IF FND_API.to_Boolean( p_init_msg_list )
110 THEN
111 FND_MSG_PUB.initialize;
112 END IF;
113
114
115 -- Initialize API return status to SUCCESS
116 x_return_status := FND_API.G_RET_STS_SUCCESS;
117
118 -- =========================================================================
119 -- Validate Environment
120 -- =========================================================================
121
122 IF FND_GLOBAL.USER_ID IS NULL
123 THEN
124 RAISE FND_API.G_EXC_ERROR;
125 END IF;
126
127
128
129 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
130 THEN
131
132 -- Invoke validation procedures
133 Validate_appr_hist(
134 p_api_version_number => 1.0,
135 p_init_msg_list => FND_API.G_FALSE,
136 p_validation_level => p_validation_level,
137 p_validation_mode => JTF_PLSQL_API.g_create,
138 p_appr_hist_rec => p_appr_hist_rec,
139 x_return_status => x_return_status,
140 x_msg_count => x_msg_count,
141 x_msg_data => x_msg_data);
142 END IF;
143
144 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
145 RAISE FND_API.G_EXC_ERROR;
146 END IF;
147
148 l_object_id := p_appr_hist_rec.object_id;
149
150 -- Invoke table handler(Ams_Appr_Hist_Pkg.Insert_Row)
151 Ams_Appr_Hist_Pkg.Insert_Row(
152 p_object_id => l_object_id,
153 p_object_type_code => p_appr_hist_rec.object_type_code,
154 p_sequence_num => p_appr_hist_rec.sequence_num,
155 p_object_version_num => p_appr_hist_rec.object_version_num,
156 p_last_update_date => SYSDATE,
157 p_last_updated_by => FND_GLOBAL.USER_ID,
158 p_creation_date => SYSDATE,
159 p_created_by => FND_GLOBAL.USER_ID,
160 p_action_code => p_appr_hist_rec.action_code,
161 p_action_date => p_appr_hist_rec.action_date,
162 p_approver_id => p_appr_hist_rec.approver_id,
163 p_approval_detail_id => p_appr_hist_rec.approval_detail_id,
164 p_note => p_appr_hist_rec.note,
165 p_last_update_login => FND_GLOBAL.conc_login_id,
166 p_approval_type => p_appr_hist_rec.approval_type,
167 p_approver_type => p_appr_hist_rec.approver_type,
168 p_custom_setup_id => p_appr_hist_rec.custom_setup_id,
169 p_log_message => p_appr_hist_rec.log_message
170 );
171
172 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
173 RAISE FND_API.G_EXC_ERROR;
174 END IF;
175 --
176 -- End of API body
177 --
178
179 -- Standard check for p_commit
180 IF FND_API.to_Boolean( p_commit )
181 THEN
182 COMMIT WORK;
183 END IF;
184
185 -- Standard call to get message count and if count is 1, get message info.
186 FND_MSG_PUB.Count_And_Get
187 (p_count => x_msg_count,
188 p_data => x_msg_data
189 );
190 EXCEPTION
191
192 WHEN AMS_Utility_PVT.resource_locked THEN
193 x_return_status := FND_API.g_ret_sts_error;
194
195 WHEN FND_API.G_EXC_ERROR THEN
196 ROLLBACK TO CREATE_Appr_Hist_PVT;
197 x_return_status := FND_API.G_RET_STS_ERROR;
198 -- Standard call to get message count and if count=1, get the message
199 FND_MSG_PUB.Count_And_Get (
200 p_encoded => FND_API.G_FALSE,
201 p_count => x_msg_count,
202 p_data => x_msg_data
203 );
204
205 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
206 ROLLBACK TO CREATE_Appr_Hist_PVT;
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
208 -- Standard call to get message count and if count=1, get the message
209 FND_MSG_PUB.Count_And_Get (
210 p_encoded => FND_API.G_FALSE,
211 p_count => x_msg_count,
212 p_data => x_msg_data
213 );
214
215 WHEN OTHERS THEN
216 ROLLBACK TO CREATE_Appr_Hist_PVT;
217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
218 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
219 THEN
220 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
221 END IF;
222 -- Standard call to get message count and if count=1, get the message
223 FND_MSG_PUB.Count_And_Get (
224 p_encoded => FND_API.G_FALSE,
225 p_count => x_msg_count,
226 p_data => x_msg_data
227 );
228 End Create_Appr_Hist;
229
230
231 -- ==============================================================================
232 -- Start of Comments
233 -- ==============================================================================
234 -- API Name
235 -- Update_Appr_Hist
236 -- Type
237 -- Private
238 -- Pre-Req
239 --
240 -- Parameters
241 --
242 -- IN
243 -- p_api_version_number IN NUMBER Required
244 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
245 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
246 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
247 -- p_appr_hist_rec IN appr_hist_rec_type Required
248 --
249 -- OUT
250 -- x_return_status OUT VARCHAR2
251 -- x_msg_count OUT NUMBER
252 -- x_msg_data OUT VARCHAR2
253 -- Version : Current version 1.0
254 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
255 -- and basic operation, developer must manually add parameters and business logic as necessary.
256 --
257 -- History
258 --
259 -- NOTE
260 --
261 -- End of Comments
262 -- ==============================================================================
263
264 PROCEDURE Update_Appr_Hist(
265 p_api_version_number IN NUMBER,
266 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
267 p_commit IN VARCHAR2 := FND_API.G_FALSE,
268 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
269 x_return_status OUT NOCOPY VARCHAR2,
270 x_msg_count OUT NOCOPY NUMBER,
271 x_msg_data OUT NOCOPY VARCHAR2,
272
273 p_appr_hist_rec IN appr_hist_rec_type
274 )
275
276 IS
277
278
279 CURSOR c_get_appr_hist IS
280 SELECT *
281 FROM AMS_APPROVAL_HISTORY
282 WHERE object_id = p_appr_hist_rec.object_id
283 AND object_type_code = p_appr_hist_rec.object_type_code
284 AND approval_type = p_appr_hist_rec.approval_type
285 AND sequence_num = p_appr_hist_rec.sequence_num;
286 -- Hint: Developer need to provide Where clause
287
288
289 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Appr_Hist';
290 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
291 -- Local Variables
292 l_object_version_num NUMBER;
293 l_object_id NUMBER;
294 l_ref_appr_hist_rec c_get_Appr_Hist%ROWTYPE ;
295 l_tar_appr_hist_rec appr_hist_rec_type := P_appr_hist_rec;
296 l_rowid ROWID;
297
298 BEGIN
299
300 -- Standard Start of API savepoint
301 SAVEPOINT update_appr_hist_pvt;
302
303
304 -- Standard call to check for call compatibility.
305 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
306 p_api_version_number,
307 l_api_name,
308 G_PKG_NAME)
309 THEN
310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
311 END IF;
312
313
314 -- Initialize message list if p_init_msg_list is set to TRUE.
315 IF FND_API.to_Boolean( p_init_msg_list )
316 THEN
317 FND_MSG_PUB.initialize;
318 END IF;
319
320 -- Initialize API return status to SUCCESS
321 x_return_status := FND_API.G_RET_STS_SUCCESS;
322
323 OPEN c_get_Appr_Hist;
324
325 FETCH c_get_Appr_Hist INTO l_ref_appr_hist_rec ;
326
327 IF ( c_get_Appr_Hist%NOTFOUND) THEN
328 AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
332 END IF;
329 p_token_name => 'INFO',
330 p_token_value => 'Appr_Hist') ;
331 RAISE FND_API.G_EXC_ERROR;
333 CLOSE c_get_Appr_Hist;
334
335 -- This code is commented because we are note creating or updating objects
336 -- the version number is really the version number of the campaign or event
337 -- that was submitted for approval
338 -- API gen thought it is the object_version_number used for pseudo locking
339 /*
340 If (l_tar_appr_hist_rec.object_version_num is NULL or
341 l_tar_appr_hist_rec.object_version_num = FND_API.G_MISS_NUM ) Then
342 AMS_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
343 p_token_name => 'COLUMN',
344 p_token_value => 'Last_Update_Date') ;
345 raise FND_API.G_EXC_ERROR;
346 End if;
347 -- Check Whether record has been changed by someone else
348 If (l_tar_appr_hist_rec.object_version_num <> l_ref_appr_hist_rec.object_version_num) Then
349 AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
350 p_token_name => 'INFO',
351 p_token_value => 'Appr_Hist') ;
352 raise FND_API.G_EXC_ERROR;
353 End if;
354
355 */
356 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
357 THEN
358
359 -- Invoke validation procedures
360 Validate_appr_hist(
361 p_api_version_number => 1.0,
362 p_init_msg_list => FND_API.G_FALSE,
363 p_validation_level => p_validation_level,
364 p_validation_mode => JTF_PLSQL_API.g_update,
365 p_appr_hist_rec => p_appr_hist_rec,
366 x_return_status => x_return_status,
367 x_msg_count => x_msg_count,
368 x_msg_data => x_msg_data);
369 END IF;
370
371 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
372 RAISE FND_API.G_EXC_ERROR;
373 END IF;
374
375 -- Invoke table handler(Ams_Appr_Hist_Pkg.Update_Row)
376 Ams_Appr_Hist_Pkg.Update_Row(
377 p_object_id => p_appr_hist_rec.object_id,
378 p_object_type_code => p_appr_hist_rec.object_type_code,
379 p_sequence_num => p_appr_hist_rec.sequence_num,
380 p_object_version_num => p_appr_hist_rec.object_version_num,
381 p_last_update_date => SYSDATE,
382 p_last_updated_by => FND_GLOBAL.USER_ID,
383 p_action_code => p_appr_hist_rec.action_code,
384 p_action_date => p_appr_hist_rec.action_date,
385 p_approver_id => p_appr_hist_rec.approver_id,
386 p_approval_detail_id => p_appr_hist_rec.approval_detail_id,
387 p_note => p_appr_hist_rec.note,
388 p_last_update_login => FND_GLOBAL.conc_login_id,
389 p_approval_type => p_appr_hist_rec.approval_type,
390 p_approver_type => p_appr_hist_rec.approver_type,
391 p_custom_setup_id => p_appr_hist_rec.custom_setup_id,
392 p_log_message => p_appr_hist_rec.log_message);
393
394 --
395 -- End of API body.
396 --
397
398 -- Standard check for p_commit
399 IF FND_API.to_Boolean( p_commit )
400 THEN
401 COMMIT WORK;
402 END IF;
403
404
405
406
407
408 -- Standard call to get message count and if count is 1, get message info.
409 FND_MSG_PUB.Count_And_Get
410 (p_count => x_msg_count,
411 p_data => x_msg_data
412 );
413 EXCEPTION
414
415 WHEN AMS_Utility_PVT.resource_locked THEN
416 x_return_status := FND_API.g_ret_sts_error;
417 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
418
419 WHEN FND_API.G_EXC_ERROR THEN
420 ROLLBACK TO UPDATE_Appr_Hist_PVT;
421 x_return_status := FND_API.G_RET_STS_ERROR;
422 -- Standard call to get message count and if count=1, get the message
423 FND_MSG_PUB.Count_And_Get (
424 p_encoded => FND_API.G_FALSE,
425 p_count => x_msg_count,
426 p_data => x_msg_data
427 );
428
429 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
430 ROLLBACK TO UPDATE_Appr_Hist_PVT;
431 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
432 -- Standard call to get message count and if count=1, get the message
433 FND_MSG_PUB.Count_And_Get (
434 p_encoded => FND_API.G_FALSE,
435 p_count => x_msg_count,
436 p_data => x_msg_data
437 );
438
439 WHEN OTHERS THEN
440 ROLLBACK TO UPDATE_Appr_Hist_PVT;
441 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
442 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
443 THEN
444 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
445 END IF;
446 -- Standard call to get message count and if count=1, get the message
447 FND_MSG_PUB.Count_And_Get (
448 p_encoded => FND_API.G_FALSE,
449 p_count => x_msg_count,
450 p_data => x_msg_data
451 );
452 End Update_Appr_Hist;
453
454
455 -- ==============================================================================
456 -- Start of Comments
457 -- ==============================================================================
458 -- API Name
459 -- Delete_Appr_Hist
460 -- Type
461 -- Private
462 -- Pre-Req
463 --
464 -- Parameters
465 --
469 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
466 -- IN
467 -- p_api_version_number IN NUMBER Required
468 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
470 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
471 -- p_object_id IN NUMBER
472 -- p_object_version_number IN NUMBER Optional Default = NULL
473 --
474 -- OUT
475 -- x_return_status OUT VARCHAR2
476 -- x_msg_count OUT NUMBER
477 -- x_msg_data OUT VARCHAR2
478 -- Version : Current version 1.0
479 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
480 -- and basic operation, developer must manually add parameters and business logic as necessary.
481 --
482 -- History
483 --
484 -- NOTE
485 --
486 -- End of Comments
487 -- ==============================================================================
488
489 PROCEDURE Delete_Appr_Hist(
490 p_api_version_number IN NUMBER,
491 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
492 p_commit IN VARCHAR2 := FND_API.G_FALSE,
493 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
494 x_return_status OUT NOCOPY VARCHAR2,
495 x_msg_count OUT NOCOPY NUMBER,
496 x_msg_data OUT NOCOPY VARCHAR2,
497 p_object_id IN NUMBER,
498 p_object_type_code IN VARCHAR2,
499 p_sequence_num IN NUMBER,
500 p_action_code IN VARCHAR2,
501 p_object_version_num IN NUMBER,
502 p_approval_type IN VARCHAR2
503 )
504
505 IS
506 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Appr_Hist';
507 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
508 l_object_version_number NUMBER;
509
510 BEGIN
511 -- Standard Start of API savepoint
512 SAVEPOINT delete_appr_hist_pvt;
513
514 -- Standard call to check for call compatibility.
515 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
516 p_api_version_number,
517 l_api_name,
518 G_PKG_NAME)
519 THEN
520 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
521 END IF;
522
523
524 -- Initialize message list if p_init_msg_list is set to TRUE.
525 IF FND_API.to_Boolean( p_init_msg_list )
526 THEN
527 FND_MSG_PUB.initialize;
528 END IF;
529
530 -- Initialize API return status to SUCCESS
531 x_return_status := FND_API.G_RET_STS_SUCCESS;
532
533 -- Invoke table handler(Ams_Appr_Hist_Pkg.Delete_Row)
534 Ams_Appr_Hist_Pkg.Delete_Row(
535 p_object_id => p_object_id,
536 p_object_type_code => p_object_type_code,
537 p_sequence_num => p_sequence_num,
538 p_action_code => p_action_code,
539 p_object_version_num => p_object_version_num,
540 p_approval_type => p_approval_type);
541 --
542 -- End of API body
543 --
544
545 -- Standard check for p_commit
546 IF FND_API.to_Boolean( p_commit )
547 THEN
548 COMMIT WORK;
549 END IF;
550
551 -- Standard call to get message count and if count is 1, get message info.
552 FND_MSG_PUB.Count_And_Get
553 (p_count => x_msg_count,
554 p_data => x_msg_data
555 );
556 EXCEPTION
557
558 WHEN AMS_Utility_PVT.resource_locked THEN
559 x_return_status := FND_API.g_ret_sts_error;
560
561 WHEN FND_API.G_EXC_ERROR THEN
562 ROLLBACK TO DELETE_Appr_Hist_PVT;
563 x_return_status := FND_API.G_RET_STS_ERROR;
564 -- Standard call to get message count and if count=1, get the message
565 FND_MSG_PUB.Count_And_Get (
566 p_encoded => FND_API.G_FALSE,
567 p_count => x_msg_count,
568 p_data => x_msg_data
569 );
570
571 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
572 ROLLBACK TO DELETE_Appr_Hist_PVT;
573 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
574 -- Standard call to get message count and if count=1, get the message
575 FND_MSG_PUB.Count_And_Get (
576 p_encoded => FND_API.G_FALSE,
577 p_count => x_msg_count,
578 p_data => x_msg_data
579 );
580
581 WHEN OTHERS THEN
582 ROLLBACK TO DELETE_Appr_Hist_PVT;
583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
584 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
585 THEN
586 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
587 END IF;
588 -- Standard call to get message count and if count=1, get the message
589 FND_MSG_PUB.Count_And_Get (
590 p_encoded => FND_API.G_FALSE,
591 p_count => x_msg_count,
592 p_data => x_msg_data
593 );
594 End Delete_Appr_Hist;
595
596 PROCEDURE check_Appr_Hist_Uk_Items(
597 p_appr_hist_rec IN appr_hist_rec_type,
598 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
599 x_return_status OUT NOCOPY VARCHAR2)
600 IS
601 l_valid_flag VARCHAR2(1);
602
603 BEGIN
604 x_return_status := FND_API.g_ret_sts_success;
605 /*
606 IF p_validation_mode = JTF_PLSQL_API.g_create
607 AND p_appr_hist_rec.object_id IS NOT NULL
608 THEN
612 );
609 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
610 'ams_approval_history',
611 'object_id = ''' || p_appr_hist_rec.object_id ||''''
613 END IF;
614
615 IF l_valid_flag = FND_API.g_false THEN
616 x_return_status := FND_API.g_ret_sts_error;
617 END IF;
618 */
619 END check_Appr_Hist_Uk_Items;
620
621
622
623 PROCEDURE check_Appr_Hist_Req_Items(
624 p_appr_hist_rec IN appr_hist_rec_type,
625 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
626 x_return_status OUT NOCOPY VARCHAR2
627 )
628 IS
629 BEGIN
630 x_return_status := FND_API.g_ret_sts_success;
631
632 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
633
634
635 IF p_appr_hist_rec.object_id = FND_API.G_MISS_NUM OR p_appr_hist_rec.object_id IS NULL THEN
636 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'OBJECT_ID' );
637 x_return_status := FND_API.g_ret_sts_error;
638 END IF;
639
640
641 IF p_appr_hist_rec.object_type_code = FND_API.g_miss_char OR p_appr_hist_rec.object_type_code IS NULL THEN
642 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'OBJECT_TYPE_CODE' );
643 x_return_status := FND_API.g_ret_sts_error;
644 END IF;
645
646
647 IF p_appr_hist_rec.sequence_num = FND_API.G_MISS_NUM OR p_appr_hist_rec.sequence_num IS NULL THEN
648 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'SEQUENCE_NUM' );
649 x_return_status := FND_API.g_ret_sts_error;
650 END IF;
651
652
653 IF p_appr_hist_rec.object_version_num = FND_API.G_MISS_NUM OR p_appr_hist_rec.object_version_num IS NULL THEN
654 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'OBJECT_VERSION_NUM' );
655 x_return_status := FND_API.g_ret_sts_error;
656 END IF;
657
658
659 ELSE
660
661
662 IF p_appr_hist_rec.object_id = FND_API.G_MISS_NUM THEN
663 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'OBJECT_ID' );
664 x_return_status := FND_API.g_ret_sts_error;
665 END IF;
666
667
668 IF p_appr_hist_rec.object_type_code = FND_API.g_miss_char THEN
669 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'OBJECT_TYPE_CODE' );
670 x_return_status := FND_API.g_ret_sts_error;
671 END IF;
672
673
674 IF p_appr_hist_rec.sequence_num = FND_API.G_MISS_NUM THEN
675 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'SEQUENCE_NUM' );
676 x_return_status := FND_API.g_ret_sts_error;
677 END IF;
678
679
680 IF p_appr_hist_rec.object_version_num = FND_API.G_MISS_NUM THEN
681 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'OBJECT_VERSION_NUM' );
682 x_return_status := FND_API.g_ret_sts_error;
683 END IF;
684 END IF;
685
686 END check_Appr_Hist_Req_Items;
687
688
689
690 PROCEDURE check_Appr_Hist_Fk_Items(
691 p_appr_hist_rec IN appr_hist_rec_type,
692 x_return_status OUT NOCOPY VARCHAR2
693 )
694 IS
695 BEGIN
696 x_return_status := FND_API.g_ret_sts_success;
697
698 -- Enter custom code here
699
700 END check_Appr_Hist_Fk_Items;
701
702
703
704 PROCEDURE check_Appr_Hist_Lookup_Items(
705 p_appr_hist_rec IN appr_hist_rec_type,
706 x_return_status OUT NOCOPY VARCHAR2
707 )
708 IS
709 BEGIN
710 x_return_status := FND_API.g_ret_sts_success;
711
712 -- Enter custom code here
713
714 END check_Appr_Hist_Lookup_Items;
715
716
717
718 PROCEDURE Check_Appr_Hist_Items (
719 P_appr_hist_rec IN appr_hist_rec_type,
720 p_validation_mode IN VARCHAR2,
721 x_return_status OUT NOCOPY VARCHAR2
722 )
723 IS
724 l_return_status VARCHAR2(1);
725 BEGIN
726
727 l_return_status := FND_API.g_ret_sts_success;
728 -- Check Items Uniqueness API calls
729
730 check_Appr_hist_Uk_Items(
731 p_appr_hist_rec => p_appr_hist_rec,
732 p_validation_mode => p_validation_mode,
733 x_return_status => x_return_status);
734 IF x_return_status <> FND_API.g_ret_sts_success THEN
735 l_return_status := FND_API.g_ret_sts_error;
736 END IF;
737
738 -- Check Items Required/NOT NULL API calls
739
740 check_appr_hist_req_items(
741 p_appr_hist_rec => p_appr_hist_rec,
742 p_validation_mode => p_validation_mode,
743 x_return_status => x_return_status);
744 IF x_return_status <> FND_API.g_ret_sts_success THEN
745 l_return_status := FND_API.g_ret_sts_error;
746 END IF;
747 -- Check Items Foreign Keys API calls
748
749 check_appr_hist_FK_items(
750 p_appr_hist_rec => p_appr_hist_rec,
751 x_return_status => x_return_status);
752 IF x_return_status <> FND_API.g_ret_sts_success THEN
753 l_return_status := FND_API.g_ret_sts_error;
754 END IF;
755 -- Check Items Lookups
756
757 check_appr_hist_Lookup_items(
758 p_appr_hist_rec => p_appr_hist_rec,
759 x_return_status => x_return_status);
760 IF x_return_status <> FND_API.g_ret_sts_success THEN
761 l_return_status := FND_API.g_ret_sts_error;
762 END IF;
763
764 x_return_status := l_return_status;
765
766 END Check_appr_hist_Items;
767
768
769
770
774 x_complete_rec OUT NOCOPY appr_hist_rec_type)
771
772 PROCEDURE Complete_Appr_Hist_Rec (
773 p_appr_hist_rec IN appr_hist_rec_type,
775 IS
776 l_return_status VARCHAR2(1);
777
778 CURSOR c_complete IS
779 SELECT *
780 FROM ams_approval_history
781 WHERE object_id = p_appr_hist_rec.object_id;
782 l_appr_hist_rec c_complete%ROWTYPE;
783 BEGIN
784 x_complete_rec := p_appr_hist_rec;
785
786
787 OPEN c_complete;
788 FETCH c_complete INTO l_appr_hist_rec;
789 CLOSE c_complete;
790
791 -- object_id
792 IF p_appr_hist_rec.object_id IS NULL THEN
793 x_complete_rec.object_id := l_appr_hist_rec.object_id;
794 END IF;
795
796 -- object_type_code
797 IF p_appr_hist_rec.object_type_code IS NULL THEN
798 x_complete_rec.object_type_code := l_appr_hist_rec.object_type_code;
799 END IF;
800
801 -- sequence_num
802 IF p_appr_hist_rec.sequence_num IS NULL THEN
803 x_complete_rec.sequence_num := l_appr_hist_rec.sequence_num;
804 END IF;
805
806 -- object_version_num
807 IF p_appr_hist_rec.object_version_num IS NULL THEN
808 x_complete_rec.object_version_num := l_appr_hist_rec.object_version_num;
809 END IF;
810
811 -- last_update_date
812 IF p_appr_hist_rec.last_update_date IS NULL THEN
813 x_complete_rec.last_update_date := l_appr_hist_rec.last_update_date;
814 END IF;
815
816 -- last_updated_by
817 IF p_appr_hist_rec.last_updated_by IS NULL THEN
818 x_complete_rec.last_updated_by := l_appr_hist_rec.last_updated_by;
819 END IF;
820
821 -- creation_date
822 IF p_appr_hist_rec.creation_date IS NULL THEN
823 x_complete_rec.creation_date := l_appr_hist_rec.creation_date;
824 END IF;
825
826 -- created_by
827 IF p_appr_hist_rec.created_by IS NULL THEN
828 x_complete_rec.created_by := l_appr_hist_rec.created_by;
829 END IF;
830
831 -- action_code
832 IF p_appr_hist_rec.action_code IS NULL THEN
833 x_complete_rec.action_code := l_appr_hist_rec.action_code;
834 END IF;
835
836 -- action_date
837 IF p_appr_hist_rec.action_date IS NULL THEN
838 x_complete_rec.action_date := l_appr_hist_rec.action_date;
839 END IF;
840
841 -- approver_id
842 IF p_appr_hist_rec.approver_id IS NULL THEN
843 x_complete_rec.approver_id := l_appr_hist_rec.approver_id;
844 END IF;
845
846 -- approval_detail_id
847 IF p_appr_hist_rec.approval_detail_id IS NULL THEN
848 x_complete_rec.approval_detail_id := l_appr_hist_rec.approval_detail_id;
849 END IF;
850
851 -- note
852 IF p_appr_hist_rec.note IS NULL THEN
853 x_complete_rec.note := l_appr_hist_rec.note;
854 END IF;
855
856 -- last_update_login
857 IF p_appr_hist_rec.last_update_login IS NULL THEN
858 x_complete_rec.last_update_login := l_appr_hist_rec.last_update_login;
859 END IF;
860
861 -- approval_type
862 IF p_appr_hist_rec.approval_type IS NULL THEN
863 x_complete_rec.approval_type := l_appr_hist_rec.approval_type;
864 END IF;
865
866 -- approver_type
867 IF p_appr_hist_rec.approver_type IS NULL THEN
868 x_complete_rec.approver_type := l_appr_hist_rec.approver_type;
869 END IF;
870
871 -- custom_setup_id
872 IF p_appr_hist_rec.custom_setup_id IS NULL THEN
873 x_complete_rec.custom_setup_id := l_appr_hist_rec.custom_setup_id;
874 END IF;
875
876 -- log_message
877 IF p_appr_hist_rec.log_message IS NULL THEN
878 x_complete_rec.log_message := l_appr_hist_rec.log_message;
879 END IF;
880 -- Note: Developers need to modify the procedure
881 -- to handle any business specific requirements.
882 END Complete_Appr_Hist_Rec;
883
884
885
886
887 PROCEDURE Default_Appr_Hist_Items ( p_appr_hist_rec IN appr_hist_rec_type ,
888 x_appr_hist_rec OUT NOCOPY appr_hist_rec_type )
889 IS
890 l_appr_hist_rec appr_hist_rec_type := p_appr_hist_rec;
891 BEGIN
892 -- Developers should put their code to default the record type
893 -- e.g. IF p_campaign_rec.status_code IS NULL
894 -- OR p_campaign_rec.status_code = FND_API.G_MISS_CHAR THEN
895 -- l_campaign_rec.status_code := 'NEW' ;
896 -- END IF ;
897 --
898 NULL ;
899 END;
900
901
902
903
904 PROCEDURE Validate_Appr_Hist(
905 p_api_version_number IN NUMBER,
906 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
907 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
908 p_appr_hist_rec IN appr_hist_rec_type,
909 p_validation_mode IN VARCHAR2,
910 x_return_status OUT NOCOPY VARCHAR2,
911 x_msg_count OUT NOCOPY NUMBER,
912 x_msg_data OUT NOCOPY VARCHAR2
913 )
914 IS
915 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Appr_Hist';
916 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
917 l_object_version_number NUMBER;
918 l_appr_hist_rec appr_hist_rec_type;
919
920 BEGIN
921 -- Standard Start of API savepoint
922 SAVEPOINT validate_appr_hist_;
923
924 -- Standard call to check for call compatibility.
925 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
926 p_api_version_number,
927 l_api_name,
928 G_PKG_NAME)
929 THEN
933
930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
931 END IF;
932
934 -- Initialize message list if p_init_msg_list is set to TRUE.
935 IF FND_API.to_Boolean( p_init_msg_list )
936 THEN
937 FND_MSG_PUB.initialize;
938 END IF;
939
940
941 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
942
943 Check_appr_hist_Items(
944 p_appr_hist_rec => p_appr_hist_rec,
945 p_validation_mode => p_validation_mode,
946 x_return_status => x_return_status
947 );
948
949 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
950 RAISE FND_API.G_EXC_ERROR;
951 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
952 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
953 END IF;
954 END IF;
955
956 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
957 Default_Appr_Hist_Items (p_appr_hist_rec => p_appr_hist_rec ,
958 x_appr_hist_rec => l_appr_hist_rec) ;
959 END IF ;
960
961
962 Complete_appr_hist_Rec(
963 p_appr_hist_rec => l_appr_hist_rec,
964 x_complete_rec => l_appr_hist_rec
965 );
966
967 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
968 Validate_appr_hist_Rec(
969 p_api_version_number => 1.0,
970 p_init_msg_list => FND_API.G_FALSE,
971 x_return_status => x_return_status,
972 x_msg_count => x_msg_count,
973 x_msg_data => x_msg_data,
974 p_appr_hist_rec => l_appr_hist_rec);
975
976 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
977 RAISE FND_API.G_EXC_ERROR;
978 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
979 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
980 END IF;
981 END IF;
982
983 -- Initialize API return status to SUCCESS
984 x_return_status := FND_API.G_RET_STS_SUCCESS;
985
986 -- Standard call to get message count and if count is 1, get message info.
987 FND_MSG_PUB.Count_And_Get
988 (p_count => x_msg_count,
989 p_data => x_msg_data
990 );
991 EXCEPTION
992
993 WHEN AMS_Utility_PVT.resource_locked THEN
994 x_return_status := FND_API.g_ret_sts_error;
995 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
996
997 WHEN FND_API.G_EXC_ERROR THEN
998 ROLLBACK TO VALIDATE_Appr_Hist_;
999 x_return_status := FND_API.G_RET_STS_ERROR;
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
1007 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1008 ROLLBACK TO VALIDATE_Appr_Hist_;
1009 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1010 -- Standard call to get message count and if count=1, get the message
1011 FND_MSG_PUB.Count_And_Get (
1012 p_encoded => FND_API.G_FALSE,
1013 p_count => x_msg_count,
1014 p_data => x_msg_data
1015 );
1016
1017 WHEN OTHERS THEN
1018 ROLLBACK TO VALIDATE_Appr_Hist_;
1019 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1020 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1021 THEN
1022 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1023 END IF;
1024 -- Standard call to get message count and if count=1, get the message
1025 FND_MSG_PUB.Count_And_Get (
1026 p_encoded => FND_API.G_FALSE,
1027 p_count => x_msg_count,
1028 p_data => x_msg_data
1029 );
1030 End Validate_Appr_Hist;
1031
1032
1033 PROCEDURE Validate_Appr_Hist_Rec (
1034 p_api_version_number IN NUMBER,
1035 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1036 x_return_status OUT NOCOPY VARCHAR2,
1037 x_msg_count OUT NOCOPY NUMBER,
1038 x_msg_data OUT NOCOPY VARCHAR2,
1039 p_appr_hist_rec IN appr_hist_rec_type
1040 )
1041 IS
1042 BEGIN
1043 -- Initialize message list if p_init_msg_list is set to TRUE.
1044 IF FND_API.to_Boolean( p_init_msg_list )
1045 THEN
1046 FND_MSG_PUB.initialize;
1047 END IF;
1048
1049
1050
1051 -- Initialize API return status to SUCCESS
1052 x_return_status := FND_API.G_RET_STS_SUCCESS;
1053
1054 -- Hint: Validate data
1055 -- If data not valid
1056 -- THEN
1057 -- x_return_status := FND_API.G_RET_STS_ERROR;
1058
1059 -- Standard call to get message count and if count is 1, get message info.
1060 FND_MSG_PUB.Count_And_Get
1061 (p_count => x_msg_count,
1062 p_data => x_msg_data
1063 );
1064 END Validate_appr_hist_Rec;
1065
1066 END AMS_Appr_Hist_PVT;