[Home] [Help]
PACKAGE BODY: APPS.PV_GE_CHKLST_PVT
Source
1 PACKAGE BODY PV_Ge_Chklst_PVT as
2 /* $Header: pvxvgcib.pls 120.2 2005/08/26 10:19:32 appldev ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_Ge_Chklst_PVT
7 -- Purpose
8 --
9 -- History
10 -- 15 Nov 2002 anubhavk created
11 -- 19 Nov 2002 anubhavk Updated - For NOCOPY by running nocopy.sh
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):= 'PV_Ge_Chklst_PVT';
23 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvgcib.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_Ge_Chklst_Items (
32 p_ge_chklst_rec IN ge_chklst_rec_type ,
33 x_ge_chklst_rec OUT NOCOPY ge_chklst_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_Ge_Chklst
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_ge_chklst_rec IN ge_chklst_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 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
73 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
74 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
75
76 PROCEDURE Create_Ge_Chklst(
77 p_api_version_number IN NUMBER,
78 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
79 p_commit IN VARCHAR2 := FND_API.G_FALSE,
80 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
81
82 x_return_status OUT NOCOPY VARCHAR2,
83 x_msg_count OUT NOCOPY NUMBER,
84 x_msg_data OUT NOCOPY VARCHAR2,
85
86 p_ge_chklst_rec IN ge_chklst_rec_type := g_miss_ge_chklst_rec,
87 x_checklist_item_id OUT NOCOPY NUMBER
88 )
89
90 IS
91 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Ge_Chklst';
92 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
93 l_return_status_full VARCHAR2(1);
94 l_object_version_number NUMBER := 1;
95 l_org_id NUMBER := FND_API.G_MISS_NUM;
96 l_checklist_item_id NUMBER;
97 l_dummy NUMBER;
98 l_ge_chklst_rec ge_chklst_rec_type := p_ge_chklst_rec;
99 l_sequence_num NUMBER :=0;
100 CURSOR c_id IS
101 SELECT pv_ge_chklst_items_b_s.NEXTVAL
102 FROM dual;
103
104 CURSOR c_id_exists (l_id IN NUMBER) IS
105 SELECT 1
106 FROM PV_GE_CHKLST_ITEMS_B
107 WHERE checklist_item_id = l_id;
108 BEGIN
109 -- Standard Start of API savepoint
110 SAVEPOINT create_ge_chklst_pvt;
111
112 -- Standard call to check for call compatibility.
113 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
114 p_api_version_number,
115 l_api_name,
116 G_PKG_NAME)
117 THEN
118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
119 END IF;
120
121
122 -- Initialize message list if p_init_msg_list is set to TRUE.
123 IF FND_API.to_Boolean( p_init_msg_list )
124 THEN
125 FND_MSG_PUB.initialize;
126 END IF;
127
128
129
130 -- Debug Message
131 IF (PV_DEBUG_HIGH_ON) THEN
132
133 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
134 END IF;
135
136
137
138 -- Initialize API return status to SUCCESS
139 x_return_status := FND_API.G_RET_STS_SUCCESS;
140
141
142 -- Local variable initialization
143
144 IF p_ge_chklst_rec.checklist_item_id IS NULL OR p_ge_chklst_rec.checklist_item_id = FND_API.g_miss_num THEN
145 LOOP
146 l_dummy := NULL;
147 OPEN c_id;
148 --FETCH c_id INTO l_checklist_item_id; anubhav changed to
149 FETCH c_id INTO l_ge_chklst_rec.checklist_item_id;
150 CLOSE c_id;
151
152 --OPEN c_id_exists(l_checklist_item_id);
153 OPEN c_id_exists(l_ge_chklst_rec.checklist_item_id);
154 FETCH c_id_exists INTO l_dummy;
155 CLOSE c_id_exists;
156 EXIT WHEN l_dummy IS NULL;
157 END LOOP;
158 ELSE
159 --l_checklist_item_id := p_pgc_items_rec.checklist_item_id;
160 l_ge_chklst_rec.checklist_item_id := p_ge_chklst_rec.checklist_item_id;
161 END IF;
162
163 -- =========================================================================
164 -- Validate Environment
165 -- =========================================================================
166
167 IF FND_GLOBAL.USER_ID IS NULL
168 THEN
169 PVX_UTILITY_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
170 RAISE FND_API.G_EXC_ERROR;
171 END IF;
172
173
174
175 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
176 THEN
177 -- Debug message
178 IF (PV_DEBUG_HIGH_ON) THEN
179
180 PVX_UTILITY_PVT.debug_message('Private API: Validate_Ge_Chklst');
181 END IF;
182
183 -- Populate the default required items Anubhav
184 l_ge_chklst_rec.last_update_date := SYSDATE;
185 l_ge_chklst_rec.last_updated_by := FND_GLOBAL.user_id;
186 l_ge_chklst_rec.creation_date := SYSDATE;
187 l_ge_chklst_rec.created_by := FND_GLOBAL.user_id;
188 l_ge_chklst_rec.last_update_login := FND_GLOBAL.conc_login_id;
189 l_ge_chklst_rec.object_version_number := l_object_version_number;
190
191 -- Invoke validation procedures
192 Validate_ge_chklst(
193 p_api_version_number => 1.0,
194 p_init_msg_list => FND_API.G_FALSE,
195 p_validation_level => p_validation_level,
196 p_validation_mode => JTF_PLSQL_API.g_create,
197 --p_ge_chklst_rec => p_ge_chklst_rec,
198 p_ge_chklst_rec => l_ge_chklst_rec,
199 x_return_status => x_return_status,
200 x_msg_count => x_msg_count,
201 x_msg_data => x_msg_data);
202 END IF;
203
204 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
205 RAISE FND_API.G_EXC_ERROR;
206 END IF;
207
208 -- Local variable initialization
209
210 -- Debug Message
211 IF (PV_DEBUG_HIGH_ON) THEN
212
213 PVX_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
214 END IF;
215
216 -- Adding the call to get the latest sequence number
217 FOR cur IN (SELECT MAX(sequence_num) temp_sequence_num from pv_ge_chklst_items_vl where (used_by_entity_id = p_ge_chklst_rec.used_by_entity_id ))
218 LOOP
219
220 l_sequence_num := cur.temp_sequence_num;
221 END LOOP;
222
223 l_sequence_num := NVL(l_sequence_num,0) +1 ;
224
225
226 -- Invoke table handler(Pv_Ge_Chklst_Pkg.Insert_Row)
227 Pv_Ge_Chklst_Pkg.Insert_Row(
228 px_checklist_item_id => l_ge_chklst_rec.checklist_item_id,
229 px_object_version_number => l_object_version_number,
230 p_arc_used_by_entity_code => p_ge_chklst_rec.arc_used_by_entity_code,
231 p_used_by_entity_id => p_ge_chklst_rec.used_by_entity_id,
232 p_sequence_num => l_sequence_num,
233 p_is_required_flag => p_ge_chklst_rec.is_required_flag,
234 p_enabled_flag => p_ge_chklst_rec.enabled_flag,
235 p_created_by => FND_GLOBAL.USER_ID,
236 p_creation_date => SYSDATE,
237 p_last_updated_by => FND_GLOBAL.USER_ID,
238 p_last_update_date => SYSDATE,
239 p_last_update_login => FND_GLOBAL.conc_login_id,
240 p_checklist_item_name => p_ge_chklst_rec.checklist_item_name
241 );
242
243 x_checklist_item_id := l_checklist_item_id;
244 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
245 RAISE FND_API.G_EXC_ERROR;
246 END IF;
247 --
248 -- End of API body
249 --
250
251 -- Standard check for p_commit
252 IF FND_API.to_Boolean( p_commit )
253 THEN
254 COMMIT WORK;
255 END IF;
256
257
258 -- Debug Message
259 IF (PV_DEBUG_HIGH_ON) THEN
260
261 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
262 END IF;
263
264
265 -- Standard call to get message count and if count is 1, get message info.
266 FND_MSG_PUB.Count_And_Get
267 (p_count => x_msg_count,
268 p_data => x_msg_data
269 );
270 EXCEPTION
271
272 WHEN PVX_UTILITY_PVT.resource_locked THEN
273 x_return_status := FND_API.g_ret_sts_error;
274 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
275
276 WHEN FND_API.G_EXC_ERROR THEN
277 ROLLBACK TO CREATE_Ge_Chklst_PVT;
278 x_return_status := FND_API.G_RET_STS_ERROR;
279 -- Standard call to get message count and if count=1, get the message
280 FND_MSG_PUB.Count_And_Get (
281 p_encoded => FND_API.G_FALSE,
282 p_count => x_msg_count,
283 p_data => x_msg_data
284 );
285
286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
287 ROLLBACK TO CREATE_Ge_Chklst_PVT;
288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
289 -- Standard call to get message count and if count=1, get the message
290 FND_MSG_PUB.Count_And_Get (
291 p_encoded => FND_API.G_FALSE,
292 p_count => x_msg_count,
293 p_data => x_msg_data
294 );
295
296 WHEN OTHERS THEN
297 ROLLBACK TO CREATE_Ge_Chklst_PVT;
298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
299 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
300 THEN
301 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
302 END IF;
303 -- Standard call to get message count and if count=1, get the message
304 FND_MSG_PUB.Count_And_Get (
305 p_encoded => FND_API.G_FALSE,
306 p_count => x_msg_count,
307 p_data => x_msg_data
308 );
309 End Create_Ge_Chklst;
310
311 /*********************
312 *
313 *
314 * Copy_Row
315 *
316 *
317 *********************/
318 PROCEDURE Copy_Row
319 (
320 p_api_version_number IN NUMBER
321 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
322 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
323 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
324 ,x_return_status OUT NOCOPY VARCHAR2
325 ,x_msg_count OUT NOCOPY NUMBER
326 ,x_msg_data OUT NOCOPY VARCHAR2
327 ,p_src_object_id IN NUMBER
328 ,p_tar_object_id IN NUMBER
329 )
330
331 IS
332
333
334 CURSOR c_get_chklst_rec (cv_program_id IN NUMBER) IS
335 SELECT sequence_num, is_required_flag, enabled_flag
336 FROM pv_ge_chklst_items_b
337 WHERE arc_used_by_entity_code = 'PRGM' AND used_by_entity_id = cv_program_id
338 order by checklist_item_id;
339
340 CURSOR c_get_chklst_tl_rec (cv_program_id IN NUMBER) IS
341 SELECT tl.checklist_item_id, checklist_item_name, source_lang, language
342 FROM pv_ge_chklst_items_b b, pv_ge_chklst_items_tl tl
343 WHERE b.checklist_item_id = tl.checklist_item_id
344 AND b.arc_used_by_entity_code = 'PRGM'
345 AND b.used_by_entity_id = cv_program_id
346 order by tl.checklist_item_id;
347
348 CURSOR c_id IS
349 SELECT pv_ge_chklst_items_b_s.NEXTVAL
350 FROM dual;
351
352 CURSOR c_id_exists (l_id IN NUMBER) IS
353 SELECT 1
354 FROM pv_ge_chklst_items_b
355 WHERE checklist_item_id = l_id;
356
357 l_checklist_item_id NUMBER;
358 l_object_version_number NUMBER;
359 L_API_NAME CONSTANT VARCHAR2(30) := 'Copy_Row - Checklist';
360 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
361
362 l_dummy NUMBER;
363 elmt_count NUMBER;
364
365 type numArray is table of number index by binary_integer;
366 type varcharArray is table of VARCHAR2(240) index by binary_integer;
367
368 checklist_item_id_array numArray;
369 old_checklist_item_id_array numArray;
370 new_checklist_item_id_array numArray;
371 sequence_num_array numArray;
372 is_required_flag_array varcharArray;
373 enabled_flag_array varcharArray;
374 checklist_item_name_array varcharArray;
375 source_lang_array varcharArray;
376 language_array varcharArray;
377
378 BEGIN
379 -- Standard Start of API savepoint
380 SAVEPOINT Copy_Row;
381
382 -- Standard call to check for call compatibility.
383 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
384 p_api_version_number,
385 l_api_name,
386 G_PKG_NAME)
387 THEN
388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
389 END IF;
390
391
392 -- Initialize message list if p_init_msg_list is set to TRUE.
393 IF FND_API.to_Boolean( p_init_msg_list )
394 THEN
395 FND_MSG_PUB.initialize;
396 END IF;
397
398 -- Debug Message
399 IF (PV_DEBUG_HIGH_ON) THEN
400 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
401 END IF;
402
403
404 -- Initialize API return status to SUCCESS
405 x_return_status := FND_API.G_RET_STS_SUCCESS;
406
407
408 OPEN c_get_chklst_rec (p_src_object_id);
409 LOOP
410 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' : inside loop');
411 FETCH c_get_chklst_rec bulk collect into
412 sequence_num_array, is_required_flag_array, enabled_flag_array
413 LIMIT 100;
417 PVX_UTILITY_PVT.debug_message(l_api_name || 'testing1');
414 PVX_UTILITY_PVT.debug_message(l_api_name || 'sequence_num_array.count =' || to_char(sequence_num_array.count));
415
416 for i in 1..sequence_num_array.count LOOP
418
419 l_dummy := NULL;
420 OPEN c_id;
421 LOOP
422 PVX_UTILITY_PVT.debug_message(l_api_name || 'testing2');
423 FETCH c_id INTO checklist_item_id_array(i);
424 OPEN c_id_exists(checklist_item_id_array(i));
425 FETCH c_id_exists INTO l_dummy;
426 CLOSE c_id_exists;
427 EXIT WHEN l_dummy IS NULL;
428 END LOOP;
429 CLOSE c_id;
430 PVX_UTILITY_PVT.debug_message(l_api_name || 'checklist_item_id_array(i) =' || to_char(checklist_item_id_array(i)));
431
432 END LOOP;
433
434
435 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' : insert into b table');
436
437 forall i in 1..checklist_item_id_array.count
438 INSERT INTO pv_ge_chklst_items_b(
439 checklist_item_id,
440 object_version_number,
441 arc_used_by_entity_code,
442 used_by_entity_id,
443 sequence_num,
444 is_required_flag,
445 enabled_flag,
446 created_by,
447 creation_date,
448 last_updated_by,
449 last_update_date,
450 last_update_login
451 )
452 VALUES
453 (
454 checklist_item_id_array(i),
455 1,
456 'PRGM',
457 p_tar_object_id,
458 sequence_num_array(i),
459 is_required_flag_array(i),
460 enabled_flag_array(i),
461 FND_GLOBAL.USER_ID,
462 SYSDATE,
463 FND_GLOBAL.USER_ID,
464 SYSDATE,
465 FND_GLOBAL.CONC_LOGIN_ID
466 );
467
468 exit when c_get_chklst_rec%notfound;
469 END LOOP;
470 Close c_get_chklst_rec;
471
472
473 open c_get_chklst_tl_rec(p_src_object_id);
474 LOOP
475
476 Fetch c_get_chklst_tl_rec bulk collect into
477 old_checklist_item_id_array, checklist_item_name_array, source_lang_array, language_array limit 100;
478
479 elmt_count := 1;
480 for k in 1..old_checklist_item_id_array.count loop
481 if ((k <> 1) and (old_checklist_item_id_array(k) <> old_checklist_item_id_array(k-1))) then
482 elmt_count := elmt_count + 1;
483 end if;
484 PVX_UTILITY_PVT.debug_message(l_api_name || 'k = ' || to_char(k));
485 PVX_UTILITY_PVT.debug_message(l_api_name || 'elmt_count = ' || to_char(elmt_count));
486 new_checklist_item_id_array(k) := checklist_item_id_array(elmt_count);
487
488 end loop;
489
490 Forall j in 1..old_checklist_item_id_array.count
491 --PVX_UTILITY_PVT.debug_message(l_api_name || 'insert into pv_checklist_items_tl');
492 INSERT INTO pv_ge_chklst_items_tl(
493 checklist_item_id,
494 language ,
495 last_update_date ,
496 last_updated_by ,
497 creation_date ,
498 created_by ,
499 last_update_login ,
500 source_lang ,
501 checklist_item_name
502 )
503 values
504 (
505 new_checklist_item_id_array(j),
506 language_array(j),
507 SYSDATE,
508 FND_GLOBAL.USER_ID,
509 SYSDATE,
510 FND_GLOBAL.USER_ID,
511 FND_GLOBAL.CONC_LOGIN_ID,
512 source_lang_array(j),
513 checklist_item_name_array(j)
514 );
515
516 exit when c_get_chklst_tl_rec%notfound;
517 END LOOP;
518 close c_get_chklst_tl_rec;
519
520 -- Check for commit
521 IF FND_API.to_boolean(p_commit) THEN
522 COMMIT;
523 END IF;
524
525 FND_MSG_PUB.count_and_get(
526 p_encoded => FND_API.g_false
527 ,p_count => x_msg_count
528 ,p_data => x_msg_data
529 );
530
531 EXCEPTION
532
533 WHEN PVX_Utility_PVT.resource_locked THEN
534 x_return_status := FND_API.g_ret_sts_error;
535 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
536
537 WHEN FND_API.G_EXC_ERROR THEN
538 ROLLBACK TO Copy_Row;
539 x_return_status := FND_API.G_RET_STS_ERROR;
540 -- Standard call to get message count and if count=1, get the message
541 FND_MSG_PUB.Count_And_Get (
542 p_encoded => FND_API.G_FALSE,
543 p_count => x_msg_count,
544 p_data => x_msg_data
545 );
546
547 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
548 ROLLBACK TO Copy_Row;
549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550 -- Standard call to get message count and if count=1, get the message
551 FND_MSG_PUB.Count_And_Get (
552 p_encoded => FND_API.G_FALSE,
553 p_count => x_msg_count,
554 p_data => x_msg_data
555 );
556
557 WHEN OTHERS THEN
558 ROLLBACK TO Copy_Row;
559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
560 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
564 -- Standard call to get message count and if count=1, get the message
561 THEN
562 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
563 END IF;
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 END Copy_Row;
572
573 -- ==============================================================================
574 -- Start of Comments
575 -- ==============================================================================
576 -- API Name
577 -- Update_Ge_Chklst
578 -- Type
579 -- Private
580 -- Pre-Req
581 --
582 -- Parameters
583 --
584 -- IN
585 -- p_api_version_number IN NUMBER Required
586 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
587 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
588 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
589 -- p_ge_chklst_rec IN ge_chklst_rec_type Required
590 --
591 -- OUT
592 -- x_return_status OUT VARCHAR2
593 -- x_msg_count OUT NUMBER
594 -- x_msg_data OUT VARCHAR2
595 -- Version : Current version 1.0
596 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
597 -- and basic operation, developer must manually add parameters and business logic as necessary.
598 --
599 -- History
600 --
601 -- NOTE
602 --
603 -- End of Comments
604 -- ==============================================================================
605
606 PROCEDURE Update_Ge_Chklst(
607 p_api_version_number IN NUMBER,
608 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
609 p_commit IN VARCHAR2 := FND_API.G_FALSE,
610 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
611
612 x_return_status OUT NOCOPY VARCHAR2,
613 x_msg_count OUT NOCOPY NUMBER,
614 x_msg_data OUT NOCOPY VARCHAR2,
615
616 p_ge_chklst_rec IN ge_chklst_rec_type
617 )
618
619 IS
620
621
622 CURSOR c_get_ge_chklst(checklist_item_id NUMBER) IS
623 SELECT *
624 FROM PV_GE_CHKLST_ITEMS_B
625 WHERE checklist_item_id = p_ge_chklst_rec.checklist_item_id;
626 -- Hint: Developer need to provide Where clause
627
628
629 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Ge_Chklst';
630 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
631 -- Local Variables
632 l_object_version_number NUMBER;
633 l_checklist_item_id NUMBER;
634 l_ref_ge_chklst_rec c_get_Ge_Chklst%ROWTYPE ;
635 l_tar_ge_chklst_rec ge_chklst_rec_type := P_ge_chklst_rec;
636 l_rowid ROWID;
637
638 BEGIN
639 -- Standard Start of API savepoint
640 SAVEPOINT update_ge_chklst_pvt;
641
642 -- Standard call to check for call compatibility.
643 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
644 p_api_version_number,
645 l_api_name,
646 G_PKG_NAME)
647 THEN
648 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
649 END IF;
650
651
652 -- Initialize message list if p_init_msg_list is set to TRUE.
653 IF FND_API.to_Boolean( p_init_msg_list )
654 THEN
655 FND_MSG_PUB.initialize;
656 END IF;
657
658
659
660 -- Debug Message
661 IF (PV_DEBUG_HIGH_ON) THEN
662
663 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
664 END IF;
665
666
667
668 -- Initialize API return status to SUCCESS
669 x_return_status := FND_API.G_RET_STS_SUCCESS;
670
671 -- Debug Message
672 IF (PV_DEBUG_HIGH_ON) THEN
673
674 PVX_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
675 END IF;
676
677 OPEN c_get_Ge_Chklst( l_tar_ge_chklst_rec.checklist_item_id);
678
679 FETCH c_get_Ge_Chklst INTO l_ref_ge_chklst_rec ;
680
681 If ( c_get_Ge_Chklst%NOTFOUND) THEN
682 PVX_UTILITY_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
683 p_token_name => 'INFO',
684 p_token_value => 'Ge_Chklst') ;
685 RAISE FND_API.G_EXC_ERROR;
686 END IF;
687 -- Debug Message
688 IF (PV_DEBUG_HIGH_ON) THEN
689
690 PVX_UTILITY_PVT.debug_message('Private API: - Close Cursor');
691 END IF;
692 CLOSE c_get_Ge_Chklst;
693
694
695 If (l_tar_ge_chklst_rec.object_version_number is NULL or
696 l_tar_ge_chklst_rec.object_version_number = FND_API.G_MISS_NUM ) Then
697 PVX_UTILITY_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
698 p_token_name => 'COLUMN',
699 p_token_value => 'Last_Update_Date') ;
700 raise FND_API.G_EXC_ERROR;
704 PVX_UTILITY_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
701 End if;
702 -- Check Whether record has been changed by someone else
703 If (l_tar_ge_chklst_rec.object_version_number <> l_ref_ge_chklst_rec.object_version_number) Then
705 p_token_name => 'INFO',
706 p_token_value => 'Ge_Chklst') ;
707 raise FND_API.G_EXC_ERROR;
708 End if;
709
710
711 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
712 THEN
713 -- Debug message
714 IF (PV_DEBUG_HIGH_ON) THEN
715
716 PVX_UTILITY_PVT.debug_message('Private API: Validate_Ge_Chklst');
717 END IF;
718
719 -- Invoke validation procedures
720 Validate_ge_chklst(
721 p_api_version_number => 1.0,
722 p_init_msg_list => FND_API.G_FALSE,
723 p_validation_level => p_validation_level,
724 p_validation_mode => JTF_PLSQL_API.g_update,
725 p_ge_chklst_rec => p_ge_chklst_rec,
726 x_return_status => x_return_status,
727 x_msg_count => x_msg_count,
728 x_msg_data => x_msg_data);
729 END IF;
730
731 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
732 RAISE FND_API.G_EXC_ERROR;
733 END IF;
734
735
736 -- Debug Message
737 --IF (PV_DEBUG_HIGH_ON) THENPVX_UTILITY_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');END IF;
738
739 -- Invoke table handler(Pv_Ge_Chklst_Pkg.Update_Row)
740 Pv_Ge_Chklst_Pkg.Update_Row(
741 p_checklist_item_id => p_ge_chklst_rec.checklist_item_id,
742 p_object_version_number => p_ge_chklst_rec.object_version_number,
743 p_arc_used_by_entity_code => p_ge_chklst_rec.arc_used_by_entity_code,
744 p_used_by_entity_id => p_ge_chklst_rec.used_by_entity_id,
745 p_sequence_num => p_ge_chklst_rec.sequence_num,
746 p_is_required_flag => p_ge_chklst_rec.is_required_flag,
747 p_enabled_flag => p_ge_chklst_rec.enabled_flag,
748 p_last_updated_by => FND_GLOBAL.USER_ID,
749 p_last_update_date => SYSDATE,
750 p_last_update_login => FND_GLOBAL.conc_login_id,
751 p_checklist_item_name => p_ge_chklst_rec.checklist_item_name
752 );
753 --
754 -- End of API body.
755 --
756
757 -- Standard check for p_commit
758 IF FND_API.to_Boolean( p_commit )
759 THEN
760 COMMIT WORK;
761 END IF;
762
763
764 -- Debug Message
765 IF (PV_DEBUG_HIGH_ON) THEN
766
767 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
768 END IF;
769
770
771 -- Standard call to get message count and if count is 1, get message info.
772 FND_MSG_PUB.Count_And_Get
773 (p_count => x_msg_count,
774 p_data => x_msg_data
775 );
776 EXCEPTION
777
778 WHEN PVX_UTILITY_PVT.resource_locked THEN
779 x_return_status := FND_API.g_ret_sts_error;
780 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
781
782 WHEN FND_API.G_EXC_ERROR THEN
783 ROLLBACK TO UPDATE_Ge_Chklst_PVT;
784 x_return_status := FND_API.G_RET_STS_ERROR;
785 -- Standard call to get message count and if count=1, get the message
786 FND_MSG_PUB.Count_And_Get (
787 p_encoded => FND_API.G_FALSE,
788 p_count => x_msg_count,
789 p_data => x_msg_data
790 );
791
792 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
793 ROLLBACK TO UPDATE_Ge_Chklst_PVT;
794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
795 -- Standard call to get message count and if count=1, get the message
796 FND_MSG_PUB.Count_And_Get (
797 p_encoded => FND_API.G_FALSE,
798 p_count => x_msg_count,
799 p_data => x_msg_data
800 );
801
802 WHEN OTHERS THEN
803 ROLLBACK TO UPDATE_Ge_Chklst_PVT;
804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
805 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
806 THEN
807 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
808 END IF;
809 -- Standard call to get message count and if count=1, get the message
810 FND_MSG_PUB.Count_And_Get (
811 p_encoded => FND_API.G_FALSE,
812 p_count => x_msg_count,
813 p_data => x_msg_data
814 );
815 End Update_Ge_Chklst;
816
817
818 -- ==============================================================================
819 -- Start of Comments
820 -- ==============================================================================
821 -- API Name
822 -- Delete_Ge_Chklst
823 -- Type
824 -- Private
825 -- Pre-Req
826 --
827 -- Parameters
828 --
829 -- IN
830 -- p_api_version_number IN NUMBER Required
831 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
832 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
836 --
833 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
834 -- p_checklist_item_id IN NUMBER
835 -- p_object_version_number IN NUMBER Optional Default = NULL
837 -- OUT
838 -- x_return_status OUT VARCHAR2
839 -- x_msg_count OUT NUMBER
840 -- x_msg_data OUT VARCHAR2
841 -- Version : Current version 1.0
842 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
843 -- and basic operation, developer must manually add parameters and business logic as necessary.
844 --
845 -- History
846 --
847 -- NOTE
848 --
849 -- End of Comments
850 -- ==============================================================================
851
852 PROCEDURE Delete_Ge_Chklst(
853 p_api_version_number IN NUMBER,
854 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
855 p_commit IN VARCHAR2 := FND_API.G_FALSE,
856 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
857 x_return_status OUT NOCOPY VARCHAR2,
858 x_msg_count OUT NOCOPY NUMBER,
859 x_msg_data OUT NOCOPY VARCHAR2,
860 p_checklist_item_id IN NUMBER,
861 p_object_version_number IN NUMBER
862 )
863
864 IS
865 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Ge_Chklst';
866 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
867 l_object_version_number NUMBER;
868
869 BEGIN
870 -- Standard Start of API savepoint
871 SAVEPOINT delete_ge_chklst_pvt;
872
873 -- Standard call to check for call compatibility.
874 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
875 p_api_version_number,
876 l_api_name,
877 G_PKG_NAME)
878 THEN
879 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
880 END IF;
881
882
883 -- Initialize message list if p_init_msg_list is set to TRUE.
884 IF FND_API.to_Boolean( p_init_msg_list )
885 THEN
886 FND_MSG_PUB.initialize;
887 END IF;
888
889
890
891 -- Debug Message
892 IF (PV_DEBUG_HIGH_ON) THEN
893
894 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
895 END IF;
896
897
898
899 -- Initialize API return status to SUCCESS
900 x_return_status := FND_API.G_RET_STS_SUCCESS;
901
902 --
903 -- Api body
904 --
905 -- Debug Message
906 IF (PV_DEBUG_HIGH_ON) THEN
907
908 PVX_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
909 END IF;
910
911 -- Invoke table handler(Pv_Ge_Chklst_Pkg.Delete_Row)
912 Pv_Ge_Chklst_Pkg.Delete_Row(
913 p_checklist_item_id => p_checklist_item_id,
914 p_object_version_number => p_object_version_number );
915 --
916 -- End of API body
917 --
918
919 -- Standard check for p_commit
920 IF FND_API.to_Boolean( p_commit )
921 THEN
922 COMMIT WORK;
923 END IF;
924
925
926 -- Debug Message
927 IF (PV_DEBUG_HIGH_ON) THEN
928
929 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
930 END IF;
931
932
933 -- Standard call to get message count and if count is 1, get message info.
934 FND_MSG_PUB.Count_And_Get
935 (p_count => x_msg_count,
936 p_data => x_msg_data
937 );
938 EXCEPTION
939
940 WHEN PVX_UTILITY_PVT.resource_locked THEN
941 x_return_status := FND_API.g_ret_sts_error;
942 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
943
944 WHEN FND_API.G_EXC_ERROR THEN
945 ROLLBACK TO DELETE_Ge_Chklst_PVT;
946 x_return_status := FND_API.G_RET_STS_ERROR;
947 -- Standard call to get message count and if count=1, get the message
948 FND_MSG_PUB.Count_And_Get (
949 p_encoded => FND_API.G_FALSE,
950 p_count => x_msg_count,
951 p_data => x_msg_data
952 );
953
954 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
955 ROLLBACK TO DELETE_Ge_Chklst_PVT;
956 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
957 -- Standard call to get message count and if count=1, get the message
958 FND_MSG_PUB.Count_And_Get (
959 p_encoded => FND_API.G_FALSE,
960 p_count => x_msg_count,
961 p_data => x_msg_data
962 );
963
964 WHEN OTHERS THEN
965 ROLLBACK TO DELETE_Ge_Chklst_PVT;
966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
967 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
968 THEN
969 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
970 END IF;
971 -- Standard call to get message count and if count=1, get the message
975 p_data => x_msg_data
972 FND_MSG_PUB.Count_And_Get (
973 p_encoded => FND_API.G_FALSE,
974 p_count => x_msg_count,
976 );
977 End Delete_Ge_Chklst;
978
979
980
981 -- Hint: Primary key needs to be returned.
982 -- ==============================================================================
983 -- Start of Comments
984 -- ==============================================================================
985 -- API Name
986 -- Lock_Ge_Chklst
987 -- Type
988 -- Private
989 -- Pre-Req
990 --
991 -- Parameters
992 --
993 -- IN
994 -- p_api_version_number IN NUMBER Required
995 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
996 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
997 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
998 -- p_ge_chklst_rec IN ge_chklst_rec_type Required
999 --
1000 -- OUT
1001 -- x_return_status OUT VARCHAR2
1002 -- x_msg_count OUT NUMBER
1003 -- x_msg_data OUT VARCHAR2
1004 -- Version : Current version 1.0
1005 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
1006 -- and basic operation, developer must manually add parameters and business logic as necessary.
1007 --
1008 -- History
1009 --
1010 -- NOTE
1011 --
1012 -- End of Comments
1013 -- ==============================================================================
1014
1015 PROCEDURE Lock_Ge_Chklst(
1016 p_api_version_number IN NUMBER,
1017 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1018
1019 x_return_status OUT NOCOPY VARCHAR2,
1020 x_msg_count OUT NOCOPY NUMBER,
1021 x_msg_data OUT NOCOPY VARCHAR2,
1022
1023 p_checklist_item_id IN NUMBER,
1024 p_object_version IN NUMBER
1025 )
1026
1027 IS
1028 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Ge_Chklst';
1029 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1030 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1031 l_checklist_item_id NUMBER;
1032
1033 BEGIN
1034
1035 -- Debug Message
1036 IF (PV_DEBUG_HIGH_ON) THEN
1037
1038 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1039 END IF;
1040
1041
1042 -- Initialize message list if p_init_msg_list is set to TRUE.
1043 IF FND_API.to_Boolean( p_init_msg_list )
1044 THEN
1045 FND_MSG_PUB.initialize;
1046 END IF;
1047
1048
1049
1050 -- Standard call to check for call compatibility.
1051 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1052 p_api_version_number,
1053 l_api_name,
1054 G_PKG_NAME)
1055 THEN
1056 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1057 END IF;
1058
1059
1060
1061 -- Initialize API return status to SUCCESS
1062 x_return_status := FND_API.G_RET_STS_SUCCESS;
1063
1064
1065 ------------------------ lock -------------------------
1066 Pv_Ge_Chklst_Pkg.Lock_Row(l_checklist_item_id,p_object_version);
1067
1068
1069 -------------------- finish --------------------------
1070 FND_MSG_PUB.count_and_get(
1071 p_encoded => FND_API.g_false,
1072 p_count => x_msg_count,
1073 p_data => x_msg_data);
1074 IF (PV_DEBUG_HIGH_ON) THEN
1075
1076 PVX_UTILITY_PVT.debug_message(l_full_name ||': end');
1077 END IF;
1078 EXCEPTION
1079
1080 WHEN PVX_UTILITY_PVT.resource_locked THEN
1081 x_return_status := FND_API.g_ret_sts_error;
1082 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
1083
1084 WHEN FND_API.G_EXC_ERROR THEN
1085 ROLLBACK TO LOCK_Ge_Chklst_PVT;
1086 x_return_status := FND_API.G_RET_STS_ERROR;
1087 -- Standard call to get message count and if count=1, get the message
1088 FND_MSG_PUB.Count_And_Get (
1089 p_encoded => FND_API.G_FALSE,
1090 p_count => x_msg_count,
1091 p_data => x_msg_data
1092 );
1093
1094 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1095 ROLLBACK TO LOCK_Ge_Chklst_PVT;
1096 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1097 -- Standard call to get message count and if count=1, get the message
1098 FND_MSG_PUB.Count_And_Get (
1099 p_encoded => FND_API.G_FALSE,
1100 p_count => x_msg_count,
1101 p_data => x_msg_data
1102 );
1103
1104 WHEN OTHERS THEN
1105 ROLLBACK TO LOCK_Ge_Chklst_PVT;
1106 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1107 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1108 THEN
1109 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1110 END IF;
1111 -- Standard call to get message count and if count=1, get the message
1115 p_data => x_msg_data
1112 FND_MSG_PUB.Count_And_Get (
1113 p_encoded => FND_API.G_FALSE,
1114 p_count => x_msg_count,
1116 );
1117 End Lock_Ge_Chklst;
1118
1119
1120
1121
1122 PROCEDURE check_Ge_Chklst_Uk_Items(
1123 p_ge_chklst_rec IN ge_chklst_rec_type,
1124 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1125 x_return_status OUT NOCOPY VARCHAR2)
1126 IS
1127 l_valid_flag VARCHAR2(1);
1128
1129 BEGIN
1130 x_return_status := FND_API.g_ret_sts_success;
1131 IF p_validation_mode = JTF_PLSQL_API.g_create
1132 AND p_ge_chklst_rec.checklist_item_id IS NOT NULL
1133 THEN
1134 l_valid_flag := PVX_UTILITY_PVT.check_uniqueness(
1135 'pv_ge_chklst_items_b',
1136 'checklist_item_id = ''' || p_ge_chklst_rec.checklist_item_id ||''''
1137 );
1138 END IF;
1139
1140 IF l_valid_flag = FND_API.g_false THEN
1141 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_checklist_item_id_DUPLICATE');
1142 x_return_status := FND_API.g_ret_sts_error;
1143 END IF;
1144
1145 END check_Ge_Chklst_Uk_Items;
1146
1147
1148
1149 PROCEDURE check_Ge_Chklst_Req_Items(
1150 p_ge_chklst_rec IN ge_chklst_rec_type,
1151 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1152 x_return_status OUT NOCOPY VARCHAR2
1153 )
1154 IS
1155 BEGIN
1156 x_return_status := FND_API.g_ret_sts_success;
1157
1158 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1159
1160
1161 IF p_ge_chklst_rec.checklist_item_id = FND_API.G_MISS_NUM OR p_ge_chklst_rec.checklist_item_id IS NULL THEN
1162 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'CHECKLIST_ITEM_ID' );
1163 x_return_status := FND_API.g_ret_sts_error;
1164 END IF;
1165
1166
1167 IF p_ge_chklst_rec.object_version_number = FND_API.G_MISS_NUM OR p_ge_chklst_rec.object_version_number IS NULL THEN
1168 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'OBJECT_VERSION_NUMBER' );
1169 x_return_status := FND_API.g_ret_sts_error;
1170 END IF;
1171
1172
1173 IF p_ge_chklst_rec.arc_used_by_entity_code = FND_API.g_miss_char OR p_ge_chklst_rec.arc_used_by_entity_code IS NULL THEN
1174 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'ARC_USED_BY_ENTITY_CODE' );
1175 x_return_status := FND_API.g_ret_sts_error;
1176 END IF;
1177
1178
1179 IF p_ge_chklst_rec.used_by_entity_id = FND_API.G_MISS_NUM OR p_ge_chklst_rec.used_by_entity_id IS NULL THEN
1180 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'USED_BY_ENTITY_ID' );
1181 x_return_status := FND_API.g_ret_sts_error;
1182 END IF;
1183
1184
1185 IF p_ge_chklst_rec.sequence_num = FND_API.G_MISS_NUM OR p_ge_chklst_rec.sequence_num IS NULL THEN
1186 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'SEQUENCE_NUM' );
1187 x_return_status := FND_API.g_ret_sts_error;
1188 END IF;
1189
1190
1191 IF p_ge_chklst_rec.is_required_flag = FND_API.g_miss_char OR p_ge_chklst_rec.is_required_flag IS NULL THEN
1192 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'IS_REQUIRED_FLAG' );
1193 x_return_status := FND_API.g_ret_sts_error;
1194 END IF;
1195
1196
1197 IF p_ge_chklst_rec.enabled_flag = FND_API.g_miss_char OR p_ge_chklst_rec.enabled_flag IS NULL THEN
1198 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'ENABLED_FLAG' );
1199 x_return_status := FND_API.g_ret_sts_error;
1200 END IF;
1201
1202
1203 ELSE
1204
1205
1206 IF p_ge_chklst_rec.checklist_item_id = FND_API.G_MISS_NUM THEN
1207 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'CHECKLIST_ITEM_ID' );
1208 x_return_status := FND_API.g_ret_sts_error;
1209 END IF;
1210
1211
1212 IF p_ge_chklst_rec.object_version_number = FND_API.G_MISS_NUM THEN
1213 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'OBJECT_VERSION_NUMBER' );
1214 x_return_status := FND_API.g_ret_sts_error;
1215 END IF;
1216
1217
1218 IF p_ge_chklst_rec.arc_used_by_entity_code = FND_API.g_miss_char THEN
1219 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'ARC_USED_BY_ENTITY_CODE' );
1220 x_return_status := FND_API.g_ret_sts_error;
1221 END IF;
1222
1223
1224 IF p_ge_chklst_rec.used_by_entity_id = FND_API.G_MISS_NUM THEN
1225 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'USED_BY_ENTITY_ID' );
1226 x_return_status := FND_API.g_ret_sts_error;
1227 END IF;
1228
1229
1230 IF p_ge_chklst_rec.sequence_num = FND_API.G_MISS_NUM THEN
1231 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'SEQUENCE_NUM' );
1232 x_return_status := FND_API.g_ret_sts_error;
1233 END IF;
1234
1235
1236 IF p_ge_chklst_rec.is_required_flag = FND_API.g_miss_char THEN
1237 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'IS_REQUIRED_FLAG' );
1238 x_return_status := FND_API.g_ret_sts_error;
1239 END IF;
1240
1241
1242 IF p_ge_chklst_rec.enabled_flag = FND_API.g_miss_char THEN
1243 PVX_UTILITY_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'ENABLED_FLAG' );
1244 x_return_status := FND_API.g_ret_sts_error;
1245 END IF;
1246 END IF;
1247
1248 END check_Ge_Chklst_Req_Items;
1249
1250
1251
1252 PROCEDURE check_Ge_Chklst_Fk_Items(
1253 p_ge_chklst_rec IN ge_chklst_rec_type,
1254 x_return_status OUT NOCOPY VARCHAR2
1255 )
1256 IS
1257 BEGIN
1258 x_return_status := FND_API.g_ret_sts_success;
1259
1260 -- Enter custom code here
1261
1262 END check_Ge_Chklst_Fk_Items;
1263
1264
1265
1266 PROCEDURE check_Ge_Chklst_Lookup_Items(
1267 p_ge_chklst_rec IN ge_chklst_rec_type,
1268 x_return_status OUT NOCOPY VARCHAR2
1269 )
1270 IS
1271 BEGIN
1272 x_return_status := FND_API.g_ret_sts_success;
1273
1274 -- Enter custom code here
1275
1276 END check_Ge_Chklst_Lookup_Items;
1277
1278
1279
1280 PROCEDURE Check_Ge_Chklst_Items (
1281 P_ge_chklst_rec IN ge_chklst_rec_type,
1282 p_validation_mode IN VARCHAR2,
1283 x_return_status OUT NOCOPY VARCHAR2
1284 )
1285 IS
1286 l_return_status VARCHAR2(1);
1287 BEGIN
1288
1289 l_return_status := FND_API.g_ret_sts_success;
1290 -- Check Items Uniqueness API calls
1291
1292 check_Ge_chklst_Uk_Items(
1293 p_ge_chklst_rec => p_ge_chklst_rec,
1294 p_validation_mode => p_validation_mode,
1295 x_return_status => x_return_status);
1296 IF x_return_status <> FND_API.g_ret_sts_success THEN
1297 l_return_status := FND_API.g_ret_sts_error;
1298 END IF;
1299
1300 -- Check Items Required/NOT NULL API calls
1301
1302 check_ge_chklst_req_items(
1303 p_ge_chklst_rec => p_ge_chklst_rec,
1304 p_validation_mode => p_validation_mode,
1305 x_return_status => x_return_status);
1306 IF x_return_status <> FND_API.g_ret_sts_success THEN
1307 l_return_status := FND_API.g_ret_sts_error;
1308 END IF;
1309 -- Check Items Foreign Keys API calls
1310
1311 check_ge_chklst_FK_items(
1312 p_ge_chklst_rec => p_ge_chklst_rec,
1313 x_return_status => x_return_status);
1314 IF x_return_status <> FND_API.g_ret_sts_success THEN
1315 l_return_status := FND_API.g_ret_sts_error;
1316 END IF;
1317 -- Check Items Lookups
1318
1319 check_ge_chklst_Lookup_items(
1320 p_ge_chklst_rec => p_ge_chklst_rec,
1321 x_return_status => x_return_status);
1322 IF x_return_status <> FND_API.g_ret_sts_success THEN
1323 l_return_status := FND_API.g_ret_sts_error;
1324 END IF;
1325
1326 x_return_status := l_return_status;
1327
1328 END Check_ge_chklst_Items;
1329
1330
1331
1332
1333
1334 PROCEDURE Complete_Ge_Chklst_Rec (
1335 p_ge_chklst_rec IN ge_chklst_rec_type,
1336 x_complete_rec OUT NOCOPY ge_chklst_rec_type)
1337 IS
1338 l_return_status VARCHAR2(1);
1339
1340 CURSOR c_complete IS
1341 SELECT *
1342 FROM pv_ge_chklst_items_b
1343 WHERE checklist_item_id = p_ge_chklst_rec.checklist_item_id;
1344 l_ge_chklst_rec c_complete%ROWTYPE;
1345 BEGIN
1346 x_complete_rec := p_ge_chklst_rec;
1347
1348
1349 OPEN c_complete;
1350 FETCH c_complete INTO l_ge_chklst_rec;
1351 CLOSE c_complete;
1352
1353 -- checklist_item_id
1354 IF p_ge_chklst_rec.checklist_item_id IS NULL THEN
1355 x_complete_rec.checklist_item_id := l_ge_chklst_rec.checklist_item_id;
1356 END IF;
1357
1358 -- object_version_number
1362
1359 IF p_ge_chklst_rec.object_version_number IS NULL THEN
1360 x_complete_rec.object_version_number := l_ge_chklst_rec.object_version_number;
1361 END IF;
1363 -- arc_used_by_entity_code
1364 IF p_ge_chklst_rec.arc_used_by_entity_code IS NULL THEN
1365 x_complete_rec.arc_used_by_entity_code := l_ge_chklst_rec.arc_used_by_entity_code;
1366 END IF;
1367
1368 -- used_by_entity_id
1369 IF p_ge_chklst_rec.used_by_entity_id IS NULL THEN
1370 x_complete_rec.used_by_entity_id := l_ge_chklst_rec.used_by_entity_id;
1371 END IF;
1372
1373 -- sequence_num
1374 IF p_ge_chklst_rec.sequence_num IS NULL THEN
1375 x_complete_rec.sequence_num := l_ge_chklst_rec.sequence_num;
1376 END IF;
1377
1378 -- is_required_flag
1379 IF p_ge_chklst_rec.is_required_flag IS NULL THEN
1380 x_complete_rec.is_required_flag := l_ge_chklst_rec.is_required_flag;
1381 END IF;
1382
1383 -- enabled_flag
1384 IF p_ge_chklst_rec.enabled_flag IS NULL THEN
1385 x_complete_rec.enabled_flag := l_ge_chklst_rec.enabled_flag;
1386 END IF;
1387
1388 -- created_by
1389 IF p_ge_chklst_rec.created_by IS NULL THEN
1390 x_complete_rec.created_by := l_ge_chklst_rec.created_by;
1391 END IF;
1392
1393 -- creation_date
1394 IF p_ge_chklst_rec.creation_date IS NULL THEN
1395 x_complete_rec.creation_date := l_ge_chklst_rec.creation_date;
1396 END IF;
1397
1398 -- last_updated_by
1399 IF p_ge_chklst_rec.last_updated_by IS NULL THEN
1400 x_complete_rec.last_updated_by := l_ge_chklst_rec.last_updated_by;
1401 END IF;
1402
1403 -- last_update_date
1404 IF p_ge_chklst_rec.last_update_date IS NULL THEN
1405 x_complete_rec.last_update_date := l_ge_chklst_rec.last_update_date;
1406 END IF;
1407
1408 -- last_update_login
1409 IF p_ge_chklst_rec.last_update_login IS NULL THEN
1410 x_complete_rec.last_update_login := l_ge_chklst_rec.last_update_login;
1411 END IF;
1412 -- Note: Developers need to modify the procedure
1413 -- to handle any business specific requirements.
1414 END Complete_Ge_Chklst_Rec;
1415
1416
1417
1418
1419 PROCEDURE Default_Ge_Chklst_Items ( p_ge_chklst_rec IN ge_chklst_rec_type ,
1420 x_ge_chklst_rec OUT NOCOPY ge_chklst_rec_type )
1421 IS
1422 l_ge_chklst_rec ge_chklst_rec_type := p_ge_chklst_rec;
1423 BEGIN
1424 -- Developers should put their code to default the record type
1425 -- e.g. IF p_campaign_rec.status_code IS NULL
1426 -- OR p_campaign_rec.status_code = FND_API.G_MISS_CHAR THEN
1427 -- l_campaign_rec.status_code := 'NEW' ;
1428 -- END IF ;
1429 --
1430 NULL ;
1431 END;
1432
1433
1434
1435
1436 PROCEDURE Validate_Ge_Chklst(
1437 p_api_version_number IN NUMBER,
1438 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1439 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1440 p_ge_chklst_rec IN ge_chklst_rec_type,
1441 p_validation_mode IN VARCHAR2,
1442 x_return_status OUT NOCOPY VARCHAR2,
1443 x_msg_count OUT NOCOPY NUMBER,
1444 x_msg_data OUT NOCOPY VARCHAR2
1445 )
1446 IS
1447 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Ge_Chklst';
1448 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1449 l_object_version_number NUMBER;
1450 l_ge_chklst_rec ge_chklst_rec_type;
1451 l_ge_chklst_rec_out ge_chklst_rec_type;
1452
1453 BEGIN
1454 -- Standard Start of API savepoint
1455 SAVEPOINT validate_ge_chklst_;
1456
1457 -- Standard call to check for call compatibility.
1458 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1459 p_api_version_number,
1460 l_api_name,
1461 G_PKG_NAME)
1462 THEN
1463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1464 END IF;
1465
1466
1467 -- Initialize message list if p_init_msg_list is set to TRUE.
1468 IF FND_API.to_Boolean( p_init_msg_list )
1469 THEN
1470 FND_MSG_PUB.initialize;
1471 END IF;
1472
1473
1474 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1475 Check_ge_chklst_Items(
1476 p_ge_chklst_rec => p_ge_chklst_rec,
1477 p_validation_mode => p_validation_mode,
1478 x_return_status => x_return_status
1479 );
1480
1481 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1482 RAISE FND_API.G_EXC_ERROR;
1483 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1485 END IF;
1486 END IF;
1487
1488 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1489 Default_Ge_Chklst_Items (p_ge_chklst_rec => p_ge_chklst_rec ,
1490 x_ge_chklst_rec => l_ge_chklst_rec) ;
1491 END IF ;
1492
1493
1494 Complete_ge_chklst_Rec(
1495 p_ge_chklst_rec => l_ge_chklst_rec,
1496 x_complete_rec => l_ge_chklst_rec_out
1497 );
1498
1499 l_ge_chklst_rec := l_ge_chklst_rec_out;
1500
1501 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1502 Validate_ge_chklst_Rec(
1503 p_api_version_number => 1.0,
1504 p_init_msg_list => FND_API.G_FALSE,
1505 x_return_status => x_return_status,
1506 x_msg_count => x_msg_count,
1507 x_msg_data => x_msg_data,
1508 p_ge_chklst_rec => l_ge_chklst_rec);
1509
1510 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1511 RAISE FND_API.G_EXC_ERROR;
1512 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1514 END IF;
1515 END IF;
1516
1517
1518 -- Debug Message
1519 IF (PV_DEBUG_HIGH_ON) THEN
1520
1521 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1522 END IF;
1523
1524
1525
1529
1526 -- Initialize API return status to SUCCESS
1527 x_return_status := FND_API.G_RET_STS_SUCCESS;
1528
1530 -- Debug Message
1531 IF (PV_DEBUG_HIGH_ON) THEN
1532
1533 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1534 END IF;
1535
1536
1537 -- Standard call to get message count and if count is 1, get message info.
1538 FND_MSG_PUB.Count_And_Get
1539 (p_count => x_msg_count,
1540 p_data => x_msg_data
1541 );
1542 EXCEPTION
1543
1544 WHEN PVX_UTILITY_PVT.resource_locked THEN
1545 x_return_status := FND_API.g_ret_sts_error;
1546 PVX_UTILITY_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
1547
1548 WHEN FND_API.G_EXC_ERROR THEN
1549 ROLLBACK TO VALIDATE_Ge_Chklst_;
1550 x_return_status := FND_API.G_RET_STS_ERROR;
1551 -- Standard call to get message count and if count=1, get the message
1552 FND_MSG_PUB.Count_And_Get (
1553 p_encoded => FND_API.G_FALSE,
1554 p_count => x_msg_count,
1555 p_data => x_msg_data
1556 );
1557
1558 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1559 ROLLBACK TO VALIDATE_Ge_Chklst_;
1560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1561 -- Standard call to get message count and if count=1, get the message
1562 FND_MSG_PUB.Count_And_Get (
1563 p_encoded => FND_API.G_FALSE,
1564 p_count => x_msg_count,
1565 p_data => x_msg_data
1566 );
1567
1568 WHEN OTHERS THEN
1569 ROLLBACK TO VALIDATE_Ge_Chklst_;
1570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1571 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1572 THEN
1573 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1574 END IF;
1575 -- Standard call to get message count and if count=1, get the message
1576 FND_MSG_PUB.Count_And_Get (
1577 p_encoded => FND_API.G_FALSE,
1578 p_count => x_msg_count,
1579 p_data => x_msg_data
1580 );
1581 End Validate_Ge_Chklst;
1582
1583
1584 PROCEDURE Validate_Ge_Chklst_Rec (
1585 p_api_version_number IN NUMBER,
1586 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1587 x_return_status OUT NOCOPY VARCHAR2,
1588 x_msg_count OUT NOCOPY NUMBER,
1589 x_msg_data OUT NOCOPY VARCHAR2,
1590 p_ge_chklst_rec IN ge_chklst_rec_type
1591 )
1592 IS
1593 BEGIN
1594 -- Initialize message list if p_init_msg_list is set to TRUE.
1595 IF FND_API.to_Boolean( p_init_msg_list )
1596 THEN
1597 FND_MSG_PUB.initialize;
1598 END IF;
1599
1600
1601
1602 -- Initialize API return status to SUCCESS
1603 x_return_status := FND_API.G_RET_STS_SUCCESS;
1604
1605 -- Hint: Validate data
1606 -- If data not valid
1607 -- THEN
1608 -- x_return_status := FND_API.G_RET_STS_ERROR;
1609
1610 -- Debug Message
1611 IF (PV_DEBUG_HIGH_ON) THEN
1612
1613 PVX_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1614 END IF;
1615 -- Standard call to get message count and if count is 1, get message info.
1616 FND_MSG_PUB.Count_And_Get
1617 (p_count => x_msg_count,
1618 p_data => x_msg_data
1619 );
1620 END Validate_ge_chklst_Rec;
1621
1622 PROCEDURE Check_Item_In_Chklst_Resp (
1623 p_checklist_item_id IN NUMBER,
1624 x_response OUT NOCOPY NUMBER
1625 )
1626 IS
1627 l_response NUMBER;
1628 BEGIN
1629 l_response := 0;
1630
1631 FOR cur IN (SELECT 1 FROM pv_ge_chklst_responses WHERE checklist_item_id = p_checklist_item_id)
1632 LOOP
1633
1634 l_response :=1;
1635 EXIT WHEN l_response = 1;
1636
1637 END LOOP;
1638
1639 x_response := l_response;
1640
1641 END Check_Item_In_Chklst_Resp;
1642
1643
1644
1645
1646 END PV_Ge_Chklst_PVT;