[Home] [Help]
PACKAGE BODY: APPS.PV_PRGM_CONTRACTS_PVT
Source
1 PACKAGE BODY PV_PRGM_CONTRACTS_PVT AS
2 /* $Header: pvxvppcb.pls 120.2 2005/09/13 10:44:12 ktsao ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_PRGM_CONTRACTS_PVT
7 -- Purpose
8 --
9 -- History
10 -- 7-MAR-2002 Peter.Nixon Created
11 -- 30-APR-2002 Peter.Nixon Modified
12 -- 04-JUN-2002 Karen.Tsao Modified
13 -- Uncomment the CONTRACT_ID checking in Check_FK_Items
14 -- 10-JUN-2002 Karen.Tsao Modified the token of error message of duplicate GEO_HIERARCHY_ID
15 -- in Check_Uk_Items. Passed Geo_Area_Name instead of program_contracts_id.
16 -- 11-JUN-2002 Karen.Tsao Modified to reverse logic of G_MISS_XXX and NULL.
17 -- 10-SEP-2002 Karen.Tsao Modified to Create_Prgm_Contracts, Update_Prgm_Contracts,
18 -- Complete_Rec, and Check_UK_Items for new column DEFAULT_CONTRACT_FLAG.
19 -- 13-SEP-2002 Karen.Tsao Added Delete_Default_Prgm_Contracts procedure.
20 -- 27-NOV-2002 Karen.Tsao 1. Debug message to be wrapped with IF check.
21 -- 2. Replace of COPY with NOCOPY string.
22 -- 10-DEC-2002 Karen.Tsao 1. Use <> instead of !=
23 -- 2. Added line "WHENEVER OSERROR EXIT FAILURE ROLLBACK;"
24 -- 01-JUL-2003 Karen.Tsao Made modification to accommodate deleteing default_contract_flag column.
25 -- 23-JUL-2003 Karen.Tsao Added Terminate_Contract API.
26 -- 28-AUG-2003 Karen.Tsao Change membership_type to member_type_code.
27 -- 24-OCT-2003 Karen.Tsao Passed Fnd_Api.G_FALSE to p_init_msg_list in Terminate_Contract.
28 -- 11-NOV-2003 Karen.Tsao Modified Terminate_Contract:
29 -- 1. Took out the "ROLLBACK TO CREATE_PRGM_CONTRACTS_PVT;" which is wrong.
30 -- 2. Assigned OKC_API.G_MISS_XXX to l_in_kolchrv_rec.
31 -- 13-DEC-2004 Karen.Tsao language_code column is added for R12.
32 -- 26-MAY-2005 Karen.Tsao Remove language_code column and related changes.
33 -- 06-SEP-2005 Karen.Tsao Modified Check_FK_Items to check against OKC_TERMS_TEMPLATES_ALL.
34 -- 13-SEP-2005 Karen.Tsao Removed Terminate_Contract API.
35 --
36 -- Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA
37 -- All rights reserved.
38 --
39 -- End of Comments
40 -- ===============================================================
41
42
43 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PV_PRGM_CONTRACTS_PVT';
44 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvppcb.pls';
45
46
47 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
48 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
49 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
50
51 PROCEDURE Create_Prgm_Contracts(
52 p_api_version_number IN NUMBER
53 ,p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE
54 ,p_commit IN VARCHAR2 := Fnd_Api.G_FALSE
55 ,p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL
56
57 ,x_return_status OUT NOCOPY VARCHAR2
58 ,x_msg_count OUT NOCOPY NUMBER
59 ,x_msg_data OUT NOCOPY VARCHAR2
60
61 ,p_prgm_contracts_rec IN prgm_contracts_rec_type := g_miss_prgm_contracts_rec
62 ,x_program_contracts_id OUT NOCOPY NUMBER
63 )
64
65 IS
66 l_api_version_number CONSTANT NUMBER := 1.0;
67 l_api_name CONSTANT VARCHAR2(30) := 'Create_Prgm_Contracts';
68 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
69
70 l_return_status VARCHAR2(1);
71 l_prgm_contracts_rec prgm_contracts_rec_type := p_prgm_contracts_rec;
72
76 -- Cursor to get the sequence for pv_program_contracts_id
73 l_object_version_number NUMBER := 1;
74 l_uniqueness_check VARCHAR2(1);
75
77 CURSOR c_prgm_contracts_id_seq IS
78 SELECT PV_PROGRAM_CONTRACTS_S.NEXTVAL
79 FROM dual;
80
81
82 -- Cursor to validate the uniqueness
83 CURSOR c_prgm_cntrcts_id_seq_exists (l_id IN NUMBER) IS
84 SELECT 'X'
85 FROM PV_PROGRAM_CONTRACTS
86 WHERE program_contracts_id = l_id;
87
88 BEGIN
89 ---------------Initialize --------------------
90 -- Standard Start of API savepoint
91 SAVEPOINT Create_Prgm_Contracts_PVT;
92
93 -- Initialize message list if p_init_msg_list is set to TRUE.
94 IF FND_API.to_Boolean( p_init_msg_list )
95 THEN
96 FND_MSG_PUB.initialize;
97 END IF;
98
99 -- Standard call to check for call compatibility.
100 IF NOT FND_API.Compatible_API_Call (
101 l_api_version_number
102 ,p_api_version_number
103 ,l_api_name
104 ,G_PKG_NAME
105 )
106 THEN
107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
108 END IF;
109
110 -- Debug Message
111 IF (PV_DEBUG_HIGH_ON) THEN
112
113 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - start');
114 END IF;
115
116
117 -- Initialize API return status to SUCCESS
118 x_return_status := FND_API.G_RET_STS_SUCCESS;
119
120 --------------- validate -------------------------
121
122 IF (PV_DEBUG_HIGH_ON) THEN
123
124
125
126 PVX_Utility_PVT.debug_message(l_full_name ||': validate');
127
128 END IF;
129
130 IF FND_GLOBAL.User_Id IS NULL THEN
131 FND_MESSAGE.set_name('PV', 'PV_API_USER_PROFILE_MISSING');
132 FND_MSG_PUB.add;
133 RAISE FND_API.G_EXC_ERROR;
134 END IF;
135
136
137 IF l_prgm_contracts_rec.program_contracts_id IS NULL OR
138 l_prgm_contracts_rec.program_contracts_id = FND_API.g_miss_num THEN
139 LOOP
140 -- Get the identifier
141 OPEN c_prgm_contracts_id_seq;
142 FETCH c_prgm_contracts_id_seq INTO l_prgm_contracts_rec.program_contracts_id;
143 CLOSE c_prgm_contracts_id_seq;
144
145 -- Check the uniqueness of the identifier
146 OPEN c_prgm_cntrcts_id_seq_exists(l_prgm_contracts_rec.program_contracts_id);
147 FETCH c_prgm_cntrcts_id_seq_exists INTO l_uniqueness_check;
148 -- Exit when the identifier uniqueness is established
149 EXIT WHEN c_prgm_cntrcts_id_seq_exists%ROWCOUNT = 0;
150 CLOSE c_prgm_cntrcts_id_seq_exists;
151 END LOOP;
152 END IF;
153
154 -- Debug message
155 IF (PV_DEBUG_HIGH_ON) THEN
156
157 PVX_UTILITY_PVT.debug_message('Private API: ' || l_full_name || ' - program_contracts_id = '|| l_prgm_contracts_rec.program_contracts_id);
158 END IF;
159
160 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
161 -- Debug message
162 IF (PV_DEBUG_HIGH_ON) THEN
163
164 PVX_UTILITY_PVT.debug_message('Private API: ' || l_full_name || ' - Validate_Prgm_Contracts');
165 END IF;
166
167 -- Populate the default required items
168 l_prgm_contracts_rec.last_update_date := SYSDATE;
169 l_prgm_contracts_rec.last_updated_by := FND_GLOBAL.user_id;
170 l_prgm_contracts_rec.creation_date := SYSDATE;
171 l_prgm_contracts_rec.created_by := FND_GLOBAL.user_id;
172 l_prgm_contracts_rec.last_update_login := FND_GLOBAL.conc_login_id;
173 l_prgm_contracts_rec.object_version_number := l_object_version_number;
174
175 -- Invoke validation procedures
176 Validate_Prgm_Contracts(
177 p_api_version_number => 1.0
178 ,p_init_msg_list => Fnd_Api.G_FALSE
179 ,p_validation_level => p_validation_level
180 ,p_validation_mode => JTF_PLSQL_API.g_create
181 ,p_prgm_contracts_rec => l_prgm_contracts_rec
182 ,x_return_status => x_return_status
183 ,x_msg_count => x_msg_count
184 ,x_msg_data => x_msg_data
185 );
186 -- Debug message
187 IF (PV_DEBUG_HIGH_ON) THEN
188
189 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - Validate_Prgm_Contracts return_status = ' || x_return_status );
190 END IF;
191
192 END IF;
193
194 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
195 RAISE FND_API.G_EXC_ERROR;
196 END IF;
197
198 IF x_return_status = FND_API.g_ret_sts_error THEN
199 RAISE FND_API.g_exc_error;
200 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
201 RAISE FND_API.g_exc_unexpected_error;
202 END IF;
203
204 -- Debug Message
205 IF (PV_DEBUG_HIGH_ON) THEN
206
207 PVX_UTILITY_PVT.debug_message( 'Private API:' || l_full_name || ' - Calling create table handler');
208 END IF;
209
210 -- Invoke table handler(PV_PRGM_CONTRACTS_PKG.Insert_Row)
211 PV_PRGM_CONTRACTS_PKG.Insert_Row(
212 px_program_contracts_id => l_prgm_contracts_rec.program_contracts_id
216 ,p_last_update_date => l_prgm_contracts_rec.last_update_date
213 ,p_program_id => l_prgm_contracts_rec.program_id
214 ,p_geo_hierarchy_id => l_prgm_contracts_rec.geo_hierarchy_id
215 ,p_contract_id => l_prgm_contracts_rec.contract_id
217 ,p_last_updated_by => l_prgm_contracts_rec.last_updated_by
218 ,p_creation_date => l_prgm_contracts_rec.creation_date
219 ,p_created_by => l_prgm_contracts_rec.created_by
220 ,p_last_update_login => l_prgm_contracts_rec.last_update_login
221 ,p_object_version_number => l_object_version_number
222 ,p_member_type_code => l_prgm_contracts_rec.member_type_code
223 );
224
225
226 x_program_contracts_id := l_prgm_contracts_rec.program_contracts_id;
227
228 IF l_return_status = FND_API.g_ret_sts_error THEN
229 RAISE FND_API.g_exc_error;
230 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
231 RAISE FND_API.g_exc_unexpected_error;
232 END IF;
233
234 FND_MSG_PUB.count_and_get(
235 p_encoded => FND_API.g_false
236 ,p_count => x_msg_count
237 ,p_data => x_msg_data
238 );
239
240 -- Debug Message
241 IF (PV_DEBUG_HIGH_ON) THEN
242
243 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
244 END IF;
245
246 -- Standard check for p_commit
247 IF FND_API.to_Boolean( p_commit ) THEN
248 COMMIT WORK;
249 END IF;
250
251
252 EXCEPTION
253 WHEN FND_API.G_EXC_ERROR THEN
254 ROLLBACK TO CREATE_PRGM_CONTRACTS_PVT;
255 x_return_status := Fnd_Api.G_RET_STS_ERROR;
256 -- Standard call to get message count and if count=1, get the message
257 Fnd_Msg_Pub.Count_And_Get (
258 p_encoded => Fnd_Api.G_FALSE
259 ,p_count => x_msg_count
260 ,p_data => x_msg_data
261 );
262
263 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
264 ROLLBACK TO CREATE_PRGM_CONTRACTS_PVT;
265 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
266 -- Standard call to get message count and if count=1, get the message
267 Fnd_Msg_Pub.Count_And_Get (
268 p_encoded => Fnd_Api.G_FALSE
269 ,p_count => x_msg_count
270 ,p_data => x_msg_data
271 );
272
273 WHEN OTHERS THEN
274 ROLLBACK TO CREATE_PRGM_CONTRACTS_PVT;
275 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
276 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
277 THEN
278 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
279 END IF;
280 -- Standard call to get message count and if count=1, get the message
281 Fnd_Msg_Pub.Count_And_Get (
282 p_encoded => Fnd_Api.G_FALSE
283 ,p_count => x_msg_count
284 ,p_data => x_msg_data
285 );
286
287 END Create_Prgm_Contracts;
288
289
290 PROCEDURE Update_Prgm_Contracts(
291 p_api_version_number IN NUMBER
292 ,p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE
293 ,p_commit IN VARCHAR2 := Fnd_Api.G_FALSE
294 ,p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL
295
296 ,x_return_status OUT NOCOPY VARCHAR2
297 ,x_msg_count OUT NOCOPY NUMBER
298 ,x_msg_data OUT NOCOPY VARCHAR2
299
300 ,p_prgm_contracts_rec IN prgm_contracts_rec_type
301 )
302
303 IS
304
305 CURSOR c_get_Prgm_Contracts(cv_program_contracts_id NUMBER) IS
306 SELECT *
307 FROM PV_PROGRAM_CONTRACTS
308 WHERE program_contracts_id = cv_program_contracts_id;
309
310 l_api_name CONSTANT VARCHAR2(30) := 'Update_Prgm_Contracts';
311 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
312 l_api_version_number CONSTANT NUMBER := 1.0;
313
314 -- Local Variables
315 l_ref_prgm_contracts_rec c_get_Prgm_Contracts%ROWTYPE ;
316 l_tar_prgm_contracts_rec PV_PRGM_CONTRACTS_PVT.prgm_contracts_rec_type := p_prgm_contracts_rec;
317 l_rowid ROWID;
318
319 BEGIN
320 ---------Initialize ------------------
321
322 -- Standard Start of API savepoint
323 SAVEPOINT UPDATE_Prgm_Contracts_PVT;
324
325 -- Standard call to check for call compatibility.
326 IF NOT FND_API.Compatible_API_Call (l_api_version_number
327 ,p_api_version_number
328 ,l_api_name
329 ,G_PKG_NAME
330 )
331 THEN
332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
333 END IF;
334
335 -- Initialize message list if p_init_msg_list is set to TRUE.
336 IF FND_API.to_Boolean( p_init_msg_list )
337 THEN
338 FND_MSG_PUB.initialize;
339 END IF;
340
341 -- Initialize API return status to SUCCESS
342 x_return_status := FND_API.G_RET_STS_SUCCESS;
343
344 OPEN c_get_Prgm_Contracts( l_tar_prgm_contracts_rec.program_contracts_id);
345 FETCH c_get_Prgm_Contracts INTO l_ref_prgm_contracts_rec ;
346
347 IF ( c_get_Prgm_Contracts%NOTFOUND) THEN
348 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_ENTITY');
349 FND_MESSAGE.set_token('MODE','Update');
350 FND_MESSAGE.set_token('ENTITY','Program_Contracts');
351 FND_MESSAGE.set_token('ID',TO_CHAR(l_tar_prgm_contracts_rec.program_contracts_id));
352 FND_MSG_PUB.ADD;
353 RAISE FND_API.G_EXC_ERROR;
354 END IF;
355
356 -- Debug Message
357 IF (PV_DEBUG_HIGH_ON) THEN
358
359 PVX_UTILITY_PVT.debug_message('Private API: '||l_full_name||' - Close Cursor');
360 END IF;
361 CLOSE c_get_Prgm_Contracts;
362
363 IF (l_tar_prgm_contracts_rec.object_version_number IS NULL OR
364 l_tar_prgm_contracts_rec.object_version_number = Fnd_Api.G_MISS_NUM ) THEN
365
366 FND_MESSAGE.set_name('PV', 'PV_API_VERSION_MISSING');
367 FND_MESSAGE.set_token('COLUMN','OBJECT_VERSION_NUMBER');
368 FND_MSG_PUB.add;
369 RAISE FND_API.G_EXC_ERROR;
370 END IF;
371
372 -- Check Whether record has been changed by someone else
373 IF (l_tar_prgm_contracts_rec.object_version_number <> l_ref_prgm_contracts_rec.object_version_number) THEN
374 FND_MESSAGE.set_name('PV', 'PV_API_RECORD_CHANGED');
375 FND_MESSAGE.set_token('VALUE','PROGRAM_CONTRACTS');
376 FND_MSG_PUB.ADD;
377 RAISE FND_API.G_EXC_ERROR;
378 END IF;
379
380 IF ( p_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
381 -- Debug message
382 IF (PV_DEBUG_HIGH_ON) THEN
383
384 PVX_UTILITY_PVT.debug_message('Private API: '||l_full_name||' - Validate_Prgm_Contracts');
385 END IF;
386
387 -- Invoke validation procedures
388 Validate_Prgm_Contracts(
389 p_api_version_number => 1.0
390 ,p_init_msg_list => FND_API.G_FALSE
391 ,p_validation_level => p_validation_level
392 ,p_validation_mode => JTF_PLSQL_API.g_update
393 ,p_prgm_contracts_rec => p_prgm_contracts_rec
394 ,x_return_status => x_return_status
395 ,x_msg_count => x_msg_count
396 ,x_msg_data => x_msg_data
397 );
398 END IF;
399
400 IF x_return_status = FND_API.g_ret_sts_error THEN
401 RAISE FND_API.g_exc_error;
402 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
403 RAISE FND_API.g_exc_unexpected_error;
404 END IF;
405
406 -- replace g_miss_char/num/date with current column values
407 Complete_Rec(
408 p_prgm_contracts_rec => p_prgm_contracts_rec
409 ,x_complete_rec => l_tar_prgm_contracts_rec
410 );
411
412 -- Debug Message
413 IF (PV_DEBUG_HIGH_ON) THEN
414
418 -- Invoke table handler(PV_PRGM_CONTRACTS_PKG.Update_Row)
415 Pvx_Utility_Pvt.debug_message('Private API: '||l_full_name||' - Calling update table handler');
416 END IF;
417
419 PV_PRGM_CONTRACTS_PKG.Update_Row(
420 p_program_contracts_id => l_tar_prgm_contracts_rec.program_contracts_id
421 ,p_program_id => l_tar_prgm_contracts_rec.program_id
422 ,p_geo_hierarchy_id => l_tar_prgm_contracts_rec.geo_hierarchy_id
423 ,p_contract_id => l_tar_prgm_contracts_rec.contract_id
424 ,p_last_update_date => SYSDATE
425 ,p_last_updated_by => FND_GLOBAL.user_id
426 ,p_last_update_login => FND_GLOBAL.conc_login_id
427 ,p_object_version_number => l_tar_prgm_contracts_rec.object_version_number
428 ,p_member_type_code => l_tar_prgm_contracts_rec.member_type_code
429 );
430
431 -- Debug Message
432 IF (PV_DEBUG_HIGH_ON) THEN
433
434 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
435 END IF;
436
437 -- Check for commit
438 IF FND_API.to_boolean(p_commit) THEN
439 COMMIT;
440 END IF;
441
442 FND_MSG_PUB.count_and_get(
443 p_encoded => FND_API.g_false
444 ,p_count => x_msg_count
445 ,p_data => x_msg_data
446 );
447
448
449 EXCEPTION
450 WHEN Fnd_Api.G_EXC_ERROR THEN
451 ROLLBACK TO UPDATE_Prgm_Contracts_PVT;
452 x_return_status := Fnd_Api.G_RET_STS_ERROR;
453 -- Standard call to get message count and if count=1, get the message
454 Fnd_Msg_Pub.Count_And_Get (
455 p_encoded => Fnd_Api.G_FALSE
456 ,p_count => x_msg_count
457 ,p_data => x_msg_data
458 );
459
460 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
461 ROLLBACK TO UPDATE_Prgm_Contracts_PVT;
462 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
463 -- Standard call to get message count and if count=1, get the message
464 Fnd_Msg_Pub.Count_And_Get (
465 p_encoded => Fnd_Api.G_FALSE
466 ,p_count => x_msg_count
467 ,p_data => x_msg_data
468 );
469
470 WHEN OTHERS THEN
471 ROLLBACK TO UPDATE_Prgm_Contracts_PVT;
472 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
473 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
474 THEN
475 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
476 END IF;
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 END Update_Prgm_Contracts;
485
486 PROCEDURE Delete_Prgm_Contracts(
487 p_api_version_number IN NUMBER
488 ,p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE
489 ,p_commit IN VARCHAR2 := Fnd_Api.G_FALSE
490 ,p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL
491 ,x_return_status OUT NOCOPY VARCHAR2
492 ,x_msg_count OUT NOCOPY NUMBER
493 ,x_msg_data OUT NOCOPY VARCHAR2
494 ,p_program_contracts_id IN NUMBER
495 ,p_object_version_number IN NUMBER
496 )
497
498 IS
499
500 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Prgm_Contracts';
501 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
502 l_api_version_number CONSTANT NUMBER := 1.0;
503 l_object_version_number NUMBER;
504
505 BEGIN
506
507 ---- Initialize----------------
508
509 -- Standard Start of API savepoint
510 SAVEPOINT DELETE_Prgm_Contracts_PVT;
511
512 -- Debug Message
513 IF (PV_DEBUG_HIGH_ON) THEN
514
515 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - start');
516 END IF;
517
518 -- Standard call to check for call compatibility.
519 IF NOT FND_API.Compatible_API_Call (l_api_version_number
520 ,p_api_version_number
521 ,l_api_name
522 ,G_PKG_NAME
523 )
524 THEN
525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
526 END IF;
527
528 -- Initialize message list if p_init_msg_list is set to TRUE.
529 IF FND_API.to_Boolean( p_init_msg_list )
530 THEN
531 FND_MSG_PUB.initialize;
532 END IF;
533
534 -- Initialize API return status to SUCCESS
535 x_return_status := FND_API.G_RET_STS_SUCCESS;
536
537 -- Debug Message
538 IF (PV_DEBUG_HIGH_ON) THEN
539
540 PVX_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
541 END IF;
542
543 -- Invoke table handler(PV_PRGM_CONTRACTS_PKG.Delete_Row)
544 PV_PRGM_CONTRACTS_PKG.Delete_Row(
545 p_program_contracts_id => p_program_contracts_id
546 ,p_object_version_number => p_object_version_number
547 );
548
549 -- Check for commit
550 IF FND_API.to_boolean(p_commit) THEN
551 COMMIT;
552 END IF;
553
554 FND_MSG_PUB.count_and_get(
555 p_encoded => FND_API.g_false
556 ,p_count => x_msg_count
557 ,p_data => x_msg_data
558 );
559
560 -- Debug Message
561 IF (PV_DEBUG_HIGH_ON) THEN
562
563 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
564 END IF;
565
566 EXCEPTION
567 WHEN Fnd_Api.G_EXC_ERROR THEN
568 ROLLBACK TO DELETE_Prgm_Contracts_PVT;
569 x_return_status := Fnd_Api.G_RET_STS_ERROR;
570 -- Standard call to get message count and if count=1, get the message
571 Fnd_Msg_Pub.Count_And_Get (
572 p_encoded => Fnd_Api.G_FALSE
573 ,p_count => x_msg_count
574 ,p_data => x_msg_data
575 );
576
577 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
578 ROLLBACK TO DELETE_Prgm_Contracts_PVT;
579 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
580 -- Standard call to get message count and if count=1, get the message
581 Fnd_Msg_Pub.Count_And_Get (
582 p_encoded => Fnd_Api.G_FALSE
583 ,p_count => x_msg_count
584 ,p_data => x_msg_data
585 );
586
587 WHEN OTHERS THEN
588 ROLLBACK TO DELETE_Prgm_Contracts_PVT;
589 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
590 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
591 THEN
592 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
593 END IF;
594 -- Standard call to get message count and if count=1, get the message
595 Fnd_Msg_Pub.Count_And_Get (
596 p_encoded => Fnd_Api.G_FALSE
597 ,p_count => x_msg_count
598 ,p_data => x_msg_data
599 );
600
601 END Delete_Prgm_Contracts;
602
603 PROCEDURE Lock_Prgm_Contracts(
604 p_api_version_number IN NUMBER
605 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
606
607 ,x_return_status OUT NOCOPY VARCHAR2
608 ,x_msg_count OUT NOCOPY NUMBER
609 ,x_msg_data OUT NOCOPY VARCHAR2
610
611 ,px_program_contracts_id IN NUMBER
612 ,p_object_version IN NUMBER
613 )
614
615 IS
616
617 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Prgm_Contracts';
618 l_api_version_number CONSTANT NUMBER := 1.0;
619 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
620 l_program_contracts_id NUMBER;
621
622 CURSOR c_Prgm_Contracts IS
623 SELECT program_contracts_id
624 FROM PV_PROGRAM_CONTRACTS
625 WHERE program_contracts_id = px_program_contracts_id
626 AND object_version_number = p_object_version
627 FOR UPDATE NOWAIT;
628
629 BEGIN
630
631 -- Debug Message
632 IF (PV_DEBUG_HIGH_ON) THEN
633
634 Pvx_Utility_Pvt.debug_message('Private API: ' || l_full_name || ' - start');
635 END IF;
636
637 -- Initialize message list if p_init_msg_list is set to TRUE.
638 IF Fnd_Api.to_Boolean( p_init_msg_list )
639 THEN
640 Fnd_Msg_Pub.initialize;
641 END IF;
642
643 -- Standard call to check for call compatibility.
644 IF NOT Fnd_Api.Compatible_API_Call (
645 l_api_version_number
646 ,p_api_version_number
647 ,l_api_name
648 ,G_PKG_NAME
649 )
650 THEN
651 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
652 END IF;
653
654
655 -- Initialize API return status to SUCCESS
656 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
657
658
659 ------------------------ lock -------------------------
660
661 IF (PV_DEBUG_HIGH_ON) THEN
662
663
664
665 Pvx_Utility_Pvt.debug_message(l_full_name||': start');
666
667 END IF;
668 OPEN c_Prgm_Contracts;
669
670 FETCH c_Prgm_Contracts INTO l_program_contracts_id;
671
672 IF (c_Prgm_Contracts%NOTFOUND) THEN
673 CLOSE c_Prgm_Contracts;
674 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
675 Fnd_Message.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
676 Fnd_Msg_Pub.ADD;
677 END IF;
678 RAISE Fnd_Api.g_exc_error;
679 END IF;
680
681 CLOSE c_Prgm_Contracts;
682
683 -------------------- finish --------------------------
684 Fnd_Msg_Pub.count_and_get(
685 p_encoded => Fnd_Api.g_false
686 ,p_count => x_msg_count
687 ,p_data => x_msg_data
688 );
689
690 -- Debug Message
691 IF (PV_DEBUG_HIGH_ON) THEN
692
693 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
697 /*
694 END IF;
695
696 EXCEPTION
698 WHEN PVX_Utility_PVT.resource_locked THEN
699 x_return_status := FND_API.g_ret_sts_error;
700 PVX_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
701 */
702 WHEN Fnd_Api.G_EXC_ERROR THEN
703 ROLLBACK TO LOCK_Prgm_Contracts_PVT;
704 x_return_status := Fnd_Api.G_RET_STS_ERROR;
705 -- Standard call to get message count and if count=1, get the message
706 Fnd_Msg_Pub.Count_And_Get (
707 p_encoded => Fnd_Api.G_FALSE
708 ,p_count => x_msg_count
709 ,p_data => x_msg_data
710 );
711
712 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
713 ROLLBACK TO LOCK_Prgm_Contracts_PVT;
714 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
715 -- Standard call to get message count and if count=1, get the message
716 Fnd_Msg_Pub.Count_And_Get (
717 p_encoded => Fnd_Api.G_FALSE
718 ,p_count => x_msg_count
719 ,p_data => x_msg_data
720 );
721
722 WHEN OTHERS THEN
723 ROLLBACK TO LOCK_Prgm_Contracts_PVT;
724 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
725 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
726 THEN
727 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
728 END IF;
729 -- Standard call to get message count and if count=1, get the message
730 Fnd_Msg_Pub.Count_And_Get (
731 p_encoded => Fnd_Api.G_FALSE
732 ,p_count => x_msg_count
733 ,p_data => x_msg_data
734 );
735 END Lock_Prgm_Contracts;
736
737
738
739 PROCEDURE Check_UK_Items(
740 p_prgm_contracts_rec IN prgm_contracts_rec_type
741 ,p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create
742 ,x_return_status OUT NOCOPY VARCHAR2
743 )
744
745 IS
746
747 l_valid_flag VARCHAR2(1);
748 l_geo_area_name VARCHAR2(240);
749 l_program_contract_id NUMBER;
750
751 -- Cursor to get the geoAreaName of the given location hierarchy id
752 CURSOR c_geo_area_name (l_loc_hie_id IN NUMBER) IS
753 select DECODE(LH.LOCATION_TYPE_CODE, 'AREA1', LH.AREA1_NAME, 'AREA2',LH.AREA2_NAME,
754 'COUNTRY', LH.COUNTRY_NAME, 'CREGION', LH.COUNTRY_REGION_NAME,
755 'STATE', LH.STATE_NAME, 'SREGION', LH.STATE_REGION_NAME, 'CITY', LH.CITY_NAME,
756 'POSTAL_CODE', LH.POSTAL_CODE_START||'-'||LH.POSTAL_CODE_END, to_char(LOCATION_HIERARCHY_ID))
757 from JTF_LOC_HIERARCHIES_VL LH
758 where LOCATION_HIERARCHY_ID = l_loc_hie_id;
759
760 CURSOR c_get_program_contract_id (cv_program_id NUMBER, cv_geo_hierarchy_id NUMBER, cv_member_type_code VARCHAR2) IS
761 SELECT program_contracts_id
762 FROM pv_program_contracts
763 WHERE program_id = cv_program_id
764 and geo_hierarchy_id = cv_geo_hierarchy_id
765 and member_type_code = cv_member_type_code;
766
767 BEGIN
768
769 x_return_status := FND_API.g_ret_sts_success;
770 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
771
772 l_valid_flag := PVX_UTILITY_PVT.check_uniqueness(
773 'PV_PROGRAM_CONTRACTS'
774 ,'PROGRAM_CONTRACTS_ID = ''' || p_prgm_contracts_rec.program_contracts_id ||''''
775 );
776
777 IF l_valid_flag = Fnd_Api.g_false THEN
778 FND_MESSAGE.set_name('PV', 'PV_API_DUPLICATE_ENTITY');
779 FND_MESSAGE.set_token('ID',TO_CHAR(p_prgm_contracts_rec.program_contracts_id) );
780 FND_MESSAGE.set_token('ENTITY','Program_Contracts');
781 FND_MSG_PUB.ADD;
782 x_return_status := Fnd_Api.g_ret_sts_error;
783 RETURN;
784 END IF;
785 END IF;
786
787 -- Debug message
788 IF (PV_DEBUG_HIGH_ON) THEN
789
790 PVX_UTILITY_PVT.debug_message('- In Check_UK_Items API Before PROGRAM_ID/GEO_HIERARCHY_ID combo check' );
791 END IF;
792
793 l_valid_flag := PVX_UTILITY_PVT.check_uniqueness(
794 'PV_PROGRAM_CONTRACTS'
795 ,'PROGRAM_ID = ''' || p_prgm_contracts_rec.PROGRAM_ID ||''' AND GEO_HIERARCHY_ID = ''' || p_prgm_contracts_rec.GEO_HIERARCHY_ID || ''' AND MEMBER_TYPE_CODE = ''' || p_prgm_contracts_rec.member_type_code || ''''
796 );
797
798
799 IF l_valid_flag = Fnd_Api.g_false THEN
800 OPEN c_get_program_contract_id(p_prgm_contracts_rec.PROGRAM_ID, p_prgm_contracts_rec.GEO_HIERARCHY_ID, p_prgm_contracts_rec.member_type_code);
801 FETCH c_get_program_contract_id INTO l_program_contract_id;
802 IF l_program_contract_id <> p_prgm_contracts_rec.program_contracts_id THEN
803
804 FND_MESSAGE.set_name('PV', 'PV_API_DUPLICATE_ENTITY');
805
806 -- Get the get_area_name
807 OPEN c_geo_area_name(p_prgm_contracts_rec.geo_hierarchy_id);
808 FETCH c_geo_area_name into l_geo_area_name;
809 FND_MESSAGE.set_token('ID', l_geo_area_name);
810
811 FND_MESSAGE.set_token('ENTITY','Program Contracts');
812 FND_MSG_PUB.ADD;
813 x_return_status := Fnd_Api.g_ret_sts_error;
814 RETURN;
815 END IF;
816 END IF;
817
818
819 /*
820
821 IF l_valid_flag = Fnd_Api.g_false THEN
822 FND_MESSAGE.set_name('PV', 'PV_API_DUPLICATE_ENTITY');
823
824 -- Get the get_area_name
825 OPEN c_geo_area_name(p_prgm_contracts_rec.geo_hierarchy_id);
829 FND_MESSAGE.set_token('ENTITY','Program_Contracts');
826 FETCH c_geo_area_name into l_geo_area_name;
827 FND_MESSAGE.set_token('ID', l_geo_area_name);
828
830 FND_MSG_PUB.ADD;
831 x_return_status := Fnd_Api.g_ret_sts_error;
832 RETURN;
833 END IF;
834 END IF;
835 */
836 END Check_UK_Items;
837
838
839
840 PROCEDURE Check_Req_Items(
841 p_prgm_contracts_rec IN prgm_contracts_rec_type
842 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
843 ,x_return_status OUT NOCOPY VARCHAR2
844 )
845
846 IS
847
848 BEGIN
849
850 x_return_status := FND_API.g_ret_sts_success;
851
852 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
853
854 IF p_prgm_contracts_rec.program_contracts_id = Fnd_Api.g_miss_num
855 OR p_prgm_contracts_rec.program_contracts_id IS NULL THEN
856 Fnd_Message.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
857 Fnd_Message.set_token('COLUMN','PROGRAM_CONTRACTS_ID');
858 FND_MSG_PUB.add;
859 x_return_status := FND_API.g_ret_sts_error;
860 RETURN;
861 END IF;
862
863
864 IF p_prgm_contracts_rec.program_id = Fnd_Api.g_miss_num
865 OR p_prgm_contracts_rec.program_id IS NULL THEN
866 Fnd_Message.set_name('PV','PV_API_MISSING_REQ_COLUMN');
867 Fnd_Message.set_token('COLUMN','PROGRAM_ID');
868 FND_MSG_PUB.add;
869 x_return_status := FND_API.g_ret_sts_error;
870 RETURN;
871 END IF;
872
873
874 IF p_prgm_contracts_rec.geo_hierarchy_id = Fnd_Api.g_miss_num
875 OR p_prgm_contracts_rec.geo_hierarchy_id IS NULL THEN
876 Fnd_Message.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
877 Fnd_Message.set_token('COLUMN','GEO_HIERARCHY_ID');
878 FND_MSG_PUB.add;
879 x_return_status := FND_API.g_ret_sts_error;
880 RETURN;
881 END IF;
882
883
884 IF p_prgm_contracts_rec.contract_id = Fnd_Api.g_miss_num
885 OR p_prgm_contracts_rec.contract_id IS NULL THEN
886 Fnd_Message.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
887 Fnd_Message.set_token('COLUMN','CONTRACT_ID');
888 FND_MSG_PUB.add;
889 x_return_status := FND_API.g_ret_sts_error;
890 RETURN;
891 END IF;
892
893
894 IF p_prgm_contracts_rec.last_update_date = Fnd_Api.g_miss_date
895 OR p_prgm_contracts_rec.last_update_date IS NULL THEN
896 Fnd_Message.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
897 Fnd_Message.set_token('COLUMN','LAST_UPDATE_DATE');
898 FND_MSG_PUB.add;
899 x_return_status := FND_API.g_ret_sts_error;
900 RETURN;
901 END IF;
902
903
904 IF p_prgm_contracts_rec.last_updated_by = Fnd_Api.g_miss_num
905 OR p_prgm_contracts_rec.last_updated_by IS NULL THEN
906 Fnd_Message.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
907 Fnd_Message.set_token('COLUMN','LAST_UPDATED_BY');
908 FND_MSG_PUB.add;
909 x_return_status := FND_API.g_ret_sts_error;
910 RETURN;
911 END IF;
912
913
914 IF p_prgm_contracts_rec.creation_date = Fnd_Api.g_miss_date
915 OR p_prgm_contracts_rec.creation_date IS NULL THEN
916 Fnd_Message.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
917 Fnd_Message.set_token('COLUMN','CREATION_DATE');
918 FND_MSG_PUB.add;
919 x_return_status := FND_API.g_ret_sts_error;
920 RETURN;
921 END IF;
922
923
924 IF p_prgm_contracts_rec.created_by = Fnd_Api.g_miss_num
925 OR p_prgm_contracts_rec.created_by IS NULL THEN
926 Fnd_Message.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
927 Fnd_Message.set_token('COLUMN','CREATED_BY');
928 FND_MSG_PUB.add;
929 x_return_status := FND_API.g_ret_sts_error;
930 RETURN;
931 END IF;
932
933
934 IF p_prgm_contracts_rec.last_update_login = Fnd_Api.g_miss_num
935 OR p_prgm_contracts_rec.last_update_login IS NULL THEN
936 Fnd_Message.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
937 Fnd_Message.set_token('COLUMN','LAST_UPDATE_LOGIN');
938 FND_MSG_PUB.add;
939 x_return_status := FND_API.g_ret_sts_error;
940 RETURN;
941 END IF;
942
943
944 IF p_prgm_contracts_rec.object_version_number = Fnd_Api.g_miss_num
945 OR p_prgm_contracts_rec.object_version_number IS NULL THEN
946 Fnd_Message.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
947 Fnd_Message.set_token('COLUMN','OBJECT_VERSION_NUMBER');
948 FND_MSG_PUB.add;
949 x_return_status := FND_API.g_ret_sts_error;
950 RETURN;
951 END IF;
952
953 IF p_prgm_contracts_rec.member_type_code = Fnd_Api.g_miss_char
954 OR p_prgm_contracts_rec.member_type_code IS NULL THEN
955 Fnd_Message.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
956 Fnd_Message.set_token('COLUMN','MEMBER_TYPE_CODE');
957 FND_MSG_PUB.add;
958 x_return_status := FND_API.g_ret_sts_error;
959 RETURN;
960 END IF;
961
962 ELSE
963
964
965 IF p_prgm_contracts_rec.program_contracts_id IS NULL THEN
966 Fnd_Message.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
970 RETURN;
967 Fnd_Message.set_token('COLUMN','PROGRAM_CONTRACTS_ID');
968 FND_MSG_PUB.add;
969 x_return_status := FND_API.g_ret_sts_error;
971 END IF;
972
973
974 IF p_prgm_contracts_rec.object_version_number IS NULL THEN
975 Fnd_Message.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
976 Fnd_Message.set_token('COLUMN','object_version_number');
977 Fnd_Msg_Pub.ADD;
978 x_return_status := Fnd_Api.g_ret_sts_error;
979 RETURN;
980 END IF;
981 END IF;
982
983 END Check_Req_Items;
984
985
986
987 PROCEDURE Check_FK_Items(
988 p_prgm_contracts_rec IN prgm_contracts_rec_type
989 ,x_return_status OUT NOCOPY VARCHAR2
990 )
991 IS
992
993 BEGIN
994
995 x_return_status := FND_API.g_ret_sts_success;
996
997 ----------------------- PROGRAM_ID ------------------------
998 IF (p_prgm_contracts_rec.PROGRAM_ID <> FND_API.g_miss_num
999 AND p_prgm_contracts_rec.PROGRAM_ID IS NOT NULL ) THEN
1000
1001 -- Debug message
1002 IF (PV_DEBUG_HIGH_ON) THEN
1003
1004 PVX_UTILITY_PVT.debug_message('- In Check_FK_Items : Before PROGRAM_ID fk check : PROGRAM_ID ' || p_prgm_contracts_rec.PROGRAM_ID);
1005 END IF;
1006
1007 IF PVX_Utility_PVT.check_fk_exists(
1008 'PV_PARTNER_PROGRAM_B', -- Parent schema object having the primary key
1009 'PROGRAM_ID', -- Column name in the parent object that maps to the fk value
1010 p_prgm_contracts_rec.PROGRAM_ID, -- Value of fk to be validated against the parent object's pk column
1011 PVX_UTILITY_PVT.g_number, -- datatype of fk
1012 NULL
1013 ) = FND_API.g_false
1014 THEN
1015 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1016 THEN
1017 FND_MESSAGE.set_name('PV', 'PV_NOT_A_VALID_PARTNER_PROGRAM');
1018 FND_MSG_PUB.add;
1019 END IF;
1020
1021 x_return_status := FND_API.g_ret_sts_error;
1022 RETURN;
1023 END IF;
1024 END IF;
1025
1026 IF (PV_DEBUG_HIGH_ON) THEN
1027
1028
1029
1030 PVX_UTILITY_PVT.debug_message('In Check_FK_Items : After program_id fk check ');
1031
1032 END IF;
1033
1034 ----------------------- GEO_HIERARCHY_ID ------------------------
1035 IF (p_prgm_contracts_rec.GEO_HIERARCHY_ID <> FND_API.g_miss_num
1036 AND p_prgm_contracts_rec.GEO_HIERARCHY_ID IS NOT NULL ) THEN
1037
1038 -- Debug message
1039 IF (PV_DEBUG_HIGH_ON) THEN
1040
1041 PVX_UTILITY_PVT.debug_message('- In Check_FK_Items : Before GEO_HIERARCHY_ID fk check : GEO_HIERARCHY_ID ' || p_prgm_contracts_rec.GEO_HIERARCHY_ID);
1042 END IF;
1043
1044 IF PVX_Utility_PVT.check_fk_exists(
1045 'JTF_LOC_HIERARCHIES_VL', -- Parent schema object having the primary key
1046 'LOCATION_HIERARCHY_ID', -- Column name in the parent object that maps to the fk value
1047 p_prgm_contracts_rec.GEO_HIERARCHY_ID, -- Value of fk to be validated against the parent object's pk column
1048 PVX_UTILITY_PVT.g_number, -- datatype of fk
1049 NULL
1050 ) = FND_API.g_false
1051 THEN
1052 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1053 THEN
1054 FND_MESSAGE.set_name('PV', 'PV_NOT_A_GEO_HIERARCHY');
1055 FND_MSG_PUB.add;
1056 END IF;
1057
1058 x_return_status := FND_API.g_ret_sts_error;
1059 RETURN;
1060 END IF;
1061 END IF;
1062
1063 ----------------------- CONTRACT_ID ------------------------
1064 IF (p_prgm_contracts_rec.CONTRACT_ID <> FND_API.g_miss_num
1065 AND p_prgm_contracts_rec.CONTRACT_ID IS NOT NULL ) THEN
1066
1067 -- Debug message
1068 IF (PV_DEBUG_HIGH_ON) THEN
1069
1070 PVX_UTILITY_PVT.debug_message('- In Check_FK_Items : Before CONTRACT_ID fk check : CONTRACT_ID ' || p_prgm_contracts_rec.CONTRACT_ID);
1071 END IF;
1072
1073 IF PVX_Utility_PVT.check_fk_exists(
1074 'OKC_TERMS_TEMPLATES_ALL', -- Parent schema object having the primary key
1075 'TEMPLATE_ID', -- Column name in the parent object that maps to the fk value
1076 p_prgm_contracts_rec.CONTRACT_ID, -- Value of fk to be validated against the parent object's pk column
1077 PVX_UTILITY_PVT.g_number, -- datatype of fk
1078 NULL
1079 ) = FND_API.g_false
1080 THEN
1081 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1082 THEN
1083 FND_MESSAGE.set_name('PV', 'PV_NOT_A_VALID_CONTRACT');
1084 FND_MSG_PUB.add;
1085 END IF;
1086
1087 x_return_status := FND_API.g_ret_sts_error;
1088 RETURN;
1089 END IF;
1090 END IF;
1091
1092 END Check_FK_Items;
1093
1094
1095
1096 PROCEDURE Check_Lookup_Items(
1097 p_prgm_contracts_rec IN prgm_contracts_rec_type
1098 ,x_return_status OUT NOCOPY VARCHAR2
1099 )
1100 IS
1101
1102 BEGIN
1103
1104 x_return_status := FND_API.g_ret_sts_success;
1105
1106 -- No Lookup Items for PV_Program_Contracts Table
1107
1108 END Check_Lookup_Items;
1109
1110
1111
1112 PROCEDURE Check_Items (
1113 p_prgm_contracts_rec IN prgm_contracts_rec_type
1117
1114 ,p_validation_mode IN VARCHAR2
1115 ,x_return_status OUT NOCOPY VARCHAR2
1116 )
1118 IS
1119
1120 l_api_name CONSTANT VARCHAR2(30) := 'Check_Items';
1121 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1122
1123 BEGIN
1124
1125 -- Debug message
1126 IF (PV_DEBUG_HIGH_ON) THEN
1127
1128 PVX_UTILITY_PVT.debug_message('- Check_Items API prior to Check_Req_Items call');
1129 END IF;
1130
1131 -- Check Items Required/NOT NULL API calls
1132 Check_Req_Items(
1133 p_prgm_contracts_rec => p_prgm_contracts_rec
1134 ,p_validation_mode => p_validation_mode
1135 ,x_return_status => x_return_status
1136 );
1137
1138 -- Debug message
1139 IF (PV_DEBUG_HIGH_ON) THEN
1140
1141 PVX_UTILITY_PVT.debug_message('- After Check_Req_Items. return status = ' || x_return_status);
1142 END IF;
1143
1144 IF x_return_status <> FND_API.g_ret_sts_success THEN
1145 RETURN;
1146 END IF;
1147
1148 -- Debug message
1149 IF (PV_DEBUG_HIGH_ON) THEN
1150
1151 PVX_UTILITY_PVT.debug_message('- Check_Items API prior to Check_UK_Items call');
1152 END IF;
1153
1154 -- Check Items Uniqueness API calls
1155 Check_UK_Items(
1156 p_prgm_contracts_rec => p_prgm_contracts_rec
1157 ,p_validation_mode => p_validation_mode
1158 ,x_return_status => x_return_status
1159 );
1160
1161 -- Debug message
1162 IF (PV_DEBUG_HIGH_ON) THEN
1163
1164 PVX_UTILITY_PVT.debug_message('- After Check_UK_Items. return status = ' || x_return_status);
1165 END IF;
1166
1167 IF x_return_status <> FND_API.g_ret_sts_success THEN
1168 RETURN;
1169 END IF;
1170
1171 -- Debug message
1172 IF (PV_DEBUG_HIGH_ON) THEN
1173
1174 PVX_UTILITY_PVT.debug_message('- Check_Items API prior to Check_FK_Items call');
1175 END IF;
1176
1177 -- Check Items Foreign Keys API calls
1178 Check_FK_Items(
1179 p_prgm_contracts_rec => p_prgm_contracts_rec
1180 ,x_return_status => x_return_status
1181 );
1182
1183 -- Debug message
1184 IF (PV_DEBUG_HIGH_ON) THEN
1185
1186 PVX_UTILITY_PVT.debug_message('- After Check_FK_Items. return status = ' || x_return_status);
1187 END IF;
1188
1189 IF x_return_status <> FND_API.g_ret_sts_success THEN
1190 RETURN;
1191 END IF;
1192
1193 -- Debug message
1194 IF (PV_DEBUG_HIGH_ON) THEN
1195
1196 PVX_UTILITY_PVT.debug_message('- Check_Items API prior to Check_Lookup_Items call');
1197 END IF;
1198
1199 -- Check Items Lookups
1200 Check_Lookup_Items(
1201 p_prgm_contracts_rec => p_prgm_contracts_rec
1202 ,x_return_status => x_return_status);
1203 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
1204 RETURN;
1205 END IF;
1206
1207 END Check_Items;
1208
1209
1210
1211 PROCEDURE Complete_Rec (
1212 p_prgm_contracts_rec IN prgm_contracts_rec_type
1213 ,x_complete_rec OUT NOCOPY prgm_contracts_rec_type
1214 )
1215 IS
1216
1217 CURSOR c_complete IS
1218 SELECT *
1219 FROM PV_PROGRAM_CONTRACTS
1220 WHERE program_contracts_id = p_prgm_contracts_rec.program_contracts_id;
1221
1222 l_prgm_contracts_rec c_complete%ROWTYPE;
1223
1224 BEGIN
1225
1226 x_complete_rec := p_prgm_contracts_rec;
1227
1228
1229 OPEN c_complete;
1230 FETCH c_complete INTO l_prgm_contracts_rec;
1231 CLOSE c_complete;
1232
1233 -- Debug message
1234 IF (PV_DEBUG_HIGH_ON) THEN
1235
1236 PVX_UTILITY_PVT.debug_message('- In Complete_Rec API prior to assigning program_id');
1237 END IF;
1238
1239 -- program_contracts_id
1240 --IF p_prgm_contracts_rec.program_contracts_id = Fnd_Api.g_miss_num THEN
1241 IF p_prgm_contracts_rec.program_contracts_id IS NULL THEN
1242 x_complete_rec.program_contracts_id := l_prgm_contracts_rec.program_contracts_id;
1243 END IF;
1244
1245 -- program_id
1246 --IF p_prgm_contracts_rec.program_id = Fnd_Api.g_miss_num THEN
1247 IF p_prgm_contracts_rec.program_id IS NULL THEN
1248 x_complete_rec.program_id := l_prgm_contracts_rec.program_id;
1249 END IF;
1250
1251 -- geo_hierarchy_id
1252 --IF p_prgm_contracts_rec.geo_hierarchy_id = Fnd_Api.g_miss_num THEN
1253 IF p_prgm_contracts_rec.geo_hierarchy_id IS NULL THEN
1254 x_complete_rec.geo_hierarchy_id := l_prgm_contracts_rec.geo_hierarchy_id;
1255 END IF;
1256
1257 -- contract_id
1258 --IF p_prgm_contracts_rec.contract_id = Fnd_Api.g_miss_num THEN
1259 IF p_prgm_contracts_rec.contract_id IS NULL THEN
1260 x_complete_rec.contract_id := l_prgm_contracts_rec.contract_id;
1261 END IF;
1262
1263 -- last_update_date
1264 --IF p_prgm_contracts_rec.last_update_date = Fnd_Api.g_miss_date THEN
1265 IF p_prgm_contracts_rec.last_update_date IS NULL THEN
1266 x_complete_rec.last_update_date := l_prgm_contracts_rec.last_update_date;
1267 END IF;
1268
1269 -- last_updated_by
1270 --IF p_prgm_contracts_rec.last_updated_by = Fnd_Api.g_miss_num THEN
1271 IF p_prgm_contracts_rec.last_updated_by IS NULL THEN
1272 x_complete_rec.last_updated_by := l_prgm_contracts_rec.last_updated_by;
1273 END IF;
1274
1275 -- creation_date
1276 --IF p_prgm_contracts_rec.creation_date = Fnd_Api.g_miss_date THEN
1277 IF p_prgm_contracts_rec.creation_date IS NULL THEN
1281 -- created_by
1278 x_complete_rec.creation_date := l_prgm_contracts_rec.creation_date;
1279 END IF;
1280
1282 --IF p_prgm_contracts_rec.created_by = Fnd_Api.g_miss_num THEN
1283 IF p_prgm_contracts_rec.created_by IS NULL THEN
1284 x_complete_rec.created_by := l_prgm_contracts_rec.created_by;
1285 END IF;
1286
1287 -- last_update_login
1288 --IF p_prgm_contracts_rec.last_update_login = Fnd_Api.g_miss_num THEN
1289 IF p_prgm_contracts_rec.last_update_login IS NULL THEN
1290 x_complete_rec.last_update_login := l_prgm_contracts_rec.last_update_login;
1291 END IF;
1292
1293 -- object_version_number
1294 --IF p_prgm_contracts_rec.object_version_number = Fnd_Api.g_miss_num THEN
1295 IF p_prgm_contracts_rec.object_version_number IS NULL THEN
1296 x_complete_rec.object_version_number := l_prgm_contracts_rec.object_version_number;
1297 END IF;
1298
1299 -- member_type_code
1300 --IF p_prgm_contracts_rec.member_type_code = Fnd_Api.g_miss_char THEN
1301 IF p_prgm_contracts_rec.member_type_code IS NULL THEN
1302 x_complete_rec.member_type_code := l_prgm_contracts_rec.member_type_code;
1303 END IF;
1304
1305 END Complete_Rec;
1306
1307
1308
1309 PROCEDURE Validate_Prgm_Contracts(
1310 p_api_version_number IN NUMBER
1311 ,p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE
1312 ,p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL
1313 ,p_prgm_contracts_rec IN prgm_contracts_rec_type
1314 ,p_validation_mode IN VARCHAR2
1315 ,x_return_status OUT NOCOPY VARCHAR2
1316 ,x_msg_count OUT NOCOPY NUMBER
1317 ,x_msg_data OUT NOCOPY VARCHAR2
1318 )
1319
1320 IS
1321
1322 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Prgm_Contracts';
1323 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1324 l_api_version_number CONSTANT NUMBER := 1.0;
1325 l_object_version_number NUMBER;
1326 l_prgm_contracts_rec PV_PRGM_CONTRACTS_PVT.prgm_contracts_rec_type;
1327
1328 BEGIN
1329
1330 -- Standard Start of API savepoint
1331 SAVEPOINT VALIDATE_Prgm_Contracts_;
1332
1333 -- Initialize API return status to SUCCESS
1334 x_return_status := FND_API.G_RET_STS_SUCCESS;
1335
1336 -- Standard call to check for call compatibility.
1337 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1338 p_api_version_number,
1339 l_api_name,
1340 G_PKG_NAME)
1341 THEN
1342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1343 END IF;
1344
1345 -- Initialize message list if p_init_msg_list is set to TRUE.
1346 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1347 FND_MSG_PUB.initialize;
1348 END IF;
1349
1350 -- Debug Message
1351 IF (PV_DEBUG_HIGH_ON) THEN
1352
1353 PVX_UTILITY_PVT.debug_message(' Private API: ' || l_full_name || ' - start');
1354 END IF;
1355
1356 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1357 -- Debug message
1358 IF (PV_DEBUG_HIGH_ON) THEN
1359
1360 PVX_UTILITY_PVT.debug_message(' Private API: ' || l_full_name || ' - prior to Check_Items call');
1361 END IF;
1362
1363 Check_Items(
1364 p_prgm_contracts_rec => p_prgm_contracts_rec
1365 ,p_validation_mode => p_validation_mode
1366 ,x_return_status => x_return_status
1367 );
1368
1369 -- Debug message
1370 IF (PV_DEBUG_HIGH_ON) THEN
1371
1372 PVX_UTILITY_PVT.debug_message(' Private API: ' || l_full_name || ' - return status after Check_Items call ' || x_return_status);
1373 END IF;
1374
1375 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1376 RAISE FND_API.G_EXC_ERROR;
1377 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1378 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1379 END IF;
1380 END IF;
1381
1382 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1383 Validate_Rec(
1384 p_api_version_number => 1.0
1385 ,p_init_msg_list => Fnd_Api.G_FALSE
1386 ,x_return_status => x_return_status
1387 ,x_msg_count => x_msg_count
1388 ,x_msg_data => x_msg_data
1389 ,p_prgm_contracts_rec => l_prgm_contracts_rec
1390 );
1391
1392 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1393 RAISE FND_API.G_EXC_ERROR;
1394 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1395 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1396 END IF;
1397 END IF;
1398
1399
1400 -- Debug Message
1401 IF (PV_DEBUG_HIGH_ON) THEN
1402
1403 PVX_UTILITY_PVT.debug_message('Private API: ' || l_full_name || ' - end');
1404 END IF;
1405
1406 -- Standard call to get message count and if count is 1, get message info.
1407 FND_MSG_PUB.Count_And_Get
1408 ( p_encoded => FND_API.G_FALSE,
1409 p_count => x_msg_count,
1410 p_data => x_msg_data
1411 );
1412
1413 EXCEPTION
1414
1415 WHEN Fnd_Api.G_EXC_ERROR THEN
1416 ROLLBACK TO VALIDATE_Prgm_Contracts_;
1417 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1418
1419 -- Standard call to get message count and if count=1, get the message
1420 Fnd_Msg_Pub.Count_And_Get (
1421 p_encoded => Fnd_Api.G_FALSE
1422 ,p_count => x_msg_count
1423 ,p_data => x_msg_data
1424 );
1425
1426 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1427 ROLLBACK TO VALIDATE_Prgm_Contracts_;
1428 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1429 -- Standard call to get message count and if count=1, get the message
1430 Fnd_Msg_Pub.Count_And_Get (
1431 p_encoded => Fnd_Api.G_FALSE
1432 ,p_count => x_msg_count
1433 ,p_data => x_msg_data
1434 );
1435
1436 WHEN OTHERS THEN
1437 ROLLBACK TO VALIDATE_Prgm_Contracts_;
1438 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1439 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
1440 THEN
1441 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1442 END IF;
1443 -- Standard call to get message count and if count=1, get the message
1444 Fnd_Msg_Pub.Count_And_Get (
1445 p_encoded => Fnd_Api.G_FALSE
1446 ,p_count => x_msg_count
1447 ,p_data => x_msg_data
1448 );
1449
1450 END Validate_Prgm_Contracts;
1451
1452
1453 PROCEDURE Validate_Rec(
1454 p_api_version_number IN NUMBER
1455 ,p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE
1456 ,x_return_status OUT NOCOPY VARCHAR2
1457 ,x_msg_count OUT NOCOPY NUMBER
1458 ,x_msg_data OUT NOCOPY VARCHAR2
1459 ,p_prgm_contracts_rec IN prgm_contracts_rec_type
1460 ,p_validation_mode IN VARCHAR2
1461 )
1462
1463 IS
1464
1465 BEGIN
1466 -- Initialize message list if p_init_msg_list is set to TRUE.
1467 IF Fnd_Api.to_Boolean( p_init_msg_list )
1468 THEN
1469 Fnd_Msg_Pub.initialize;
1470 END IF;
1471
1472 -- Initialize API return status to SUCCESS
1473 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1474
1475 -- Hint: Validate data
1476 -- If data not valid
1477 -- THEN
1478 -- x_return_status := FND_API.G_RET_STS_ERROR;
1479
1480 -- Debug Message
1481 IF (PV_DEBUG_HIGH_ON) THEN
1482
1483 Pvx_Utility_Pvt.debug_message('Private API: Validate_dm_model_rec');
1484 END IF;
1485
1486 -- Standard call to get message count and if count is 1, get message info.
1487 Fnd_Msg_Pub.Count_And_Get(
1488 p_count => x_msg_count
1489 ,p_data => x_msg_data
1490 );
1491
1492 END Validate_Rec;
1493
1494 END PV_PRGM_CONTRACTS_PVT;