[Home] [Help]
PACKAGE BODY: APPS.PV_GQ_ELEMENTS_PVT
Source
1 PACKAGE BODY PV_Gq_Elements_PVT as
2 /* $Header: pvxvgqeb.pls 120.3 2005/08/26 10:20:33 appldev ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_Gq_Elements_PVT
7 -- Purpose
8 --
9 -- History
10 -- 15-DEC-2002 Karen.Tsao Created
11 -- 10-DEC-2002 Karen.Tsao 1. Use <> instead of !=
12 -- 2. Added line "WHENEVER OSERROR EXIT FAILURE ROLLBACK;"
13 -- 20-DEC-2002 Karen.Tsao 1. Change API_VERSION_MISSING to PV_API_VERSION_MISSING
14 -- 2. Change API_RECORD_CHANGED to PV_API_RECORD_CHANGED, INFO to VALUE
15 -- 20-JAN-2003 Karen.Tsao Added validation to make sure that a question is marked as mandatory only
16 -- if profile attribute exists.
17 -- 24-JUN-2003 Karen.Tsao Fixed for bug 3010255. Added check_uniqueness() for profile attribute in
18 -- check_Qsnr_Element_Uk_Items method.
19 -- 16-JAN-2004 Karen.Tsao Fixed for bug #3380368. Modified Logic in check_Qsnr_Element_Uk_Items().
20 -- 16-JAN-2004 Karen.Tsao Modified code in Check_Qsnr_Element_Items() for performance reason.
21 --
22 -- NOTE
23 --
24 -- This Api is generated with Latest version of
25 -- Rosetta, where g_miss indicates NULL and
26 -- NULL indicates missing value. Rosetta Version 1.55
27 -- End of Comments
28 -- ===============================================================
29
30
31 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_Gq_Elements_PVT';
32 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvgqeb.pls';
33
34 -- G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
35 -- G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
36 --
37 -- Foreward Procedure Declarations
38 --
39
40 PROCEDURE Default_Qsnr_Element_Items (
41 p_qsnr_element_rec IN qsnr_element_rec_type ,
42 x_qsnr_element_rec OUT NOCOPY qsnr_element_rec_type
43 ) ;
44
45
46
47 -- Hint: Primary key needs to be returned.
48 -- ==============================================================================
49 -- Start of Comments
50 -- ==============================================================================
51 -- API Name
52 -- Create_Gq_Elements
53 -- Type
54 -- Private
55 -- Pre-Req
56 --
57 -- Parameters
58 --
59 -- IN
60 -- p_api_version_number IN NUMBER Required
61 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
62 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
63 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
64 -- p_qsnr_element_rec IN qsnr_element_rec_type Required
65 --
66 -- OUT
67 -- x_return_status OUT VARCHAR2
68 -- x_msg_count OUT NUMBER
69 -- x_msg_data OUT VARCHAR2
70 -- Version : Current version 1.0
71 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
72 -- and basic operation, developer must manually add parameters and business logic as necessary.
73 --
74 -- History
75 --
76 -- NOTE
77 --
78 -- End of Comments
79 -- ==============================================================================
80
81 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
82 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
83 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
84
85 PROCEDURE Create_Gq_Elements(
86 p_api_version_number IN NUMBER,
87 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
88 p_commit IN VARCHAR2 := FND_API.G_FALSE,
89 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
90
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2,
94
95 p_qsnr_element_rec IN qsnr_element_rec_type := g_miss_qsnr_element_rec,
96 x_qsnr_element_id OUT NOCOPY NUMBER
97 )
98
99 IS
100 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Gq_Elements';
101 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
102 l_return_status_full VARCHAR2(1);
103 l_object_version_number NUMBER := 1;
104 l_org_id NUMBER := FND_API.G_MISS_NUM;
105 l_qsnr_element_id NUMBER;
106 l_dummy NUMBER;
107 l_qsnr_element_rec qsnr_element_rec_type := p_qsnr_element_rec;
108
109 CURSOR c_id IS
110 SELECT pv_ge_qsnr_elements_b_s.NEXTVAL
111 FROM dual;
112
113 CURSOR c_id_exists (l_id IN NUMBER) IS
114 SELECT 1
115 FROM PV_GE_QSNR_ELEMENTS_B
116 WHERE qsnr_element_id = l_id;
117 BEGIN
118 -- Standard Start of API savepoint
119 SAVEPOINT create_gq_elements_pvt;
120
121 -- Standard call to check for call compatibility.
122 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
123 p_api_version_number,
124 l_api_name,
125 G_PKG_NAME)
126 THEN
127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
128 END IF;
129
130
131 -- Initialize message list if p_init_msg_list is set to TRUE.
132 IF FND_API.to_Boolean( p_init_msg_list )
133 THEN
134 FND_MSG_PUB.initialize;
135 END IF;
136
137
138
139 -- Debug Message
140 IF (PV_DEBUG_HIGH_ON) THEN
141
142 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
143 END IF;
144
145
146
147 -- Initialize API return status to SUCCESS
148 x_return_status := FND_API.G_RET_STS_SUCCESS;
149
150 -- Local variable initialization
151
152 IF p_qsnr_element_rec.qsnr_element_id IS NULL OR p_qsnr_element_rec.qsnr_element_id = FND_API.g_miss_num THEN
153 LOOP
154 l_dummy := NULL;
155 OPEN c_id;
156 FETCH c_id INTO l_qsnr_element_id;
157 CLOSE c_id;
158
159 OPEN c_id_exists(l_qsnr_element_id);
160 FETCH c_id_exists INTO l_dummy;
161 CLOSE c_id_exists;
162 EXIT WHEN l_dummy IS NULL;
163 END LOOP;
164 ELSE
165 l_qsnr_element_id := p_qsnr_element_rec.qsnr_element_id;
166 END IF;
167 l_qsnr_element_rec.qsnr_element_id := l_qsnr_element_id;
168
169 -- =========================================================================
170 -- Validate Environment
171 -- =========================================================================
172
173 IF FND_GLOBAL.USER_ID IS NULL
174 THEN
175 PVX_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
176 RAISE FND_API.G_EXC_ERROR;
177 END IF;
178
179
180
181 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
182 THEN
183 -- Debug message
184 IF (PV_DEBUG_HIGH_ON) THEN
185
186 PVX_UTILITY_PVT.debug_message('Private API: Validate_Gq_Elements');
187 END IF;
188
189 -- Populate the default required items
190 l_qsnr_element_rec.last_update_date := SYSDATE;
191 l_qsnr_element_rec.last_updated_by := FND_GLOBAL.user_id;
192 l_qsnr_element_rec.creation_date := SYSDATE;
193 l_qsnr_element_rec.created_by := FND_GLOBAL.user_id;
194 l_qsnr_element_rec.last_update_login := FND_GLOBAL.conc_login_id;
195 l_qsnr_element_rec.object_version_number := l_object_version_number;
196
197 -- Invoke validation procedures
198 Validate_gq_elements(
199 p_api_version_number => 1.0,
200 p_init_msg_list => FND_API.G_FALSE,
201 p_validation_level => p_validation_level,
202 p_validation_mode => JTF_PLSQL_API.g_create,
203 p_qsnr_element_rec => l_qsnr_element_rec,
204 x_return_status => x_return_status,
205 x_msg_count => x_msg_count,
206 x_msg_data => x_msg_data);
207 END IF;
208
209 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
210 RAISE FND_API.G_EXC_ERROR;
211 END IF;
212
213
214 -- Debug Message
215 IF (PV_DEBUG_HIGH_ON) THEN
216
217 PVX_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
218 END IF;
219
220 -- Invoke table handler(Pv_Gq_Elements_Pkg.Insert_Row)
221
222 Pv_Gq_Elements_Pkg.Insert_Row(
223 px_qsnr_element_id => l_qsnr_element_rec.qsnr_element_id,
224 px_object_version_number => l_qsnr_element_rec.object_version_number,
225 p_arc_used_by_entity_code => l_qsnr_element_rec.arc_used_by_entity_code,
226 p_used_by_entity_id => l_qsnr_element_rec.used_by_entity_id,
227 p_qsnr_elmt_seq_num => l_qsnr_element_rec.qsnr_elmt_seq_num,
228 p_qsnr_elmt_type => l_qsnr_element_rec.qsnr_elmt_type,
229 p_entity_attr_id => l_qsnr_element_rec.entity_attr_id,
230 p_qsnr_elmt_page_num => l_qsnr_element_rec.qsnr_elmt_page_num,
231 p_is_required_flag => l_qsnr_element_rec.is_required_flag,
232 p_created_by => FND_GLOBAL.USER_ID,
233 p_creation_date => SYSDATE,
234 p_last_updated_by => FND_GLOBAL.USER_ID,
235 p_last_update_date => SYSDATE,
236 p_last_update_login => FND_GLOBAL.conc_login_id,
237 p_elmt_content => l_qsnr_element_rec.elmt_content
238 );
239
240 x_qsnr_element_id := l_qsnr_element_id;
241 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
242 RAISE FND_API.G_EXC_ERROR;
243 END IF;
244 --
245 -- End of API body
246 --
247
248 -- Standard check for p_commit
249 IF FND_API.to_Boolean( p_commit )
250 THEN
251 COMMIT WORK;
252 END IF;
253
254
255 -- Debug Message
256 IF (PV_DEBUG_HIGH_ON) THEN
257
258 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
259 END IF;
260
261
262 -- Standard call to get message count and if count is 1, get message info.
263 FND_MSG_PUB.Count_And_Get
264 (p_count => x_msg_count,
265 p_data => x_msg_data
266 );
267 EXCEPTION
268
269 WHEN PVX_Utility_PVT.resource_locked THEN
270 x_return_status := FND_API.g_ret_sts_error;
271 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
272
273 WHEN FND_API.G_EXC_ERROR THEN
274 ROLLBACK TO CREATE_Gq_Elements_PVT;
275 x_return_status := FND_API.G_RET_STS_ERROR;
276 -- Standard call to get message count and if count=1, get the message
277 FND_MSG_PUB.Count_And_Get (
278 p_encoded => FND_API.G_FALSE,
279 p_count => x_msg_count,
280 p_data => x_msg_data
281 );
282
283 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
284 ROLLBACK TO CREATE_Gq_Elements_PVT;
285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
286 -- Standard call to get message count and if count=1, get the message
287 FND_MSG_PUB.Count_And_Get (
288 p_encoded => FND_API.G_FALSE,
289 p_count => x_msg_count,
290 p_data => x_msg_data
291 );
292
293 WHEN OTHERS THEN
294 ROLLBACK TO CREATE_Gq_Elements_PVT;
295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
296 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
297 THEN
298 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
299 END IF;
300 -- Standard call to get message count and if count=1, get the message
301 FND_MSG_PUB.Count_And_Get (
302 p_encoded => FND_API.G_FALSE,
303 p_count => x_msg_count,
304 p_data => x_msg_data
305 );
306 End Create_Gq_Elements;
307
308
309
310
311
312
313 /*********************
314 *
315 *
316 * Copy_Row
317 *
318 *
319 *********************/
320 PROCEDURE Copy_Row
321 (
322 p_api_version_number IN NUMBER
323 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
324 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
325 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
326 ,x_return_status OUT NOCOPY VARCHAR2
327 ,x_msg_count OUT NOCOPY NUMBER
328 ,x_msg_data OUT NOCOPY VARCHAR2
329 ,p_src_object_id IN NUMBER
330 ,p_tar_object_id IN NUMBER
331 )
332 IS
333 CURSOR c_get_qsnr_element_rec (cv_program_id IN NUMBER) IS
334 SELECT qsnr_elmt_seq_num, qsnr_elmt_type, entity_attr_id, qsnr_elmt_page_num, is_required_flag
335 FROM pv_ge_qsnr_elements_b
336 WHERE arc_used_by_entity_code = 'PRGM' AND used_by_entity_id = cv_program_id
337 order by qsnr_element_id;
338
339 CURSOR c_get_qsnr_element_tl_rec (cv_program_id IN NUMBER) IS
340 SELECT tl.qsnr_element_id, elmt_content, source_lang, language
341 FROM pv_ge_qsnr_elements_b b, pv_ge_qsnr_elements_tl tl
342 WHERE b.qsnr_element_id = tl.qsnr_element_id
343 AND b.arc_used_by_entity_code = 'PRGM'
344 AND b.used_by_entity_id = cv_program_id
345 order by tl.qsnr_element_id;
346
347 CURSOR c_id IS
348 SELECT pv_ge_qsnr_elements_b_s.NEXTVAL
349 FROM dual;
350
351 CURSOR c_id_exists (l_id IN NUMBER) IS
352 SELECT 1
353 FROM PV_GE_QSNR_ELEMENTS_B
354 WHERE qsnr_element_id = l_id;
355
356 CURSOR c_get_additional_tl_info (cv_program_id IN NUMBER) IS
357 SELECT QSNR_TITLE, QSNR_HEADER, QSNR_FOOTER, language
358 FROM pv_partner_program_tl tl
359 WHERE program_id = cv_program_id;
360
361 l_qsnr_element_id NUMBER;
362 l_object_version_number NUMBER;
363 L_API_NAME CONSTANT VARCHAR2(30) := 'Copy_Row';
364 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
365 l_dummy NUMBER;
366 elmt_count NUMBER;
367
368 type numArray is table of number index by binary_integer;
369 type varcharArray is table of VARCHAR2(240) index by binary_integer;
370
371 qsnr_element_id_array numArray;
372 old_qsnr_element_id_array numArray;
373 new_qsnr_element_id_array numArray;
374 qsnr_elmt_seq_num_array numArray;
375 qsnr_elmt_type_array varcharArray;
376 entity_attr_id_array numArray;
377 qsnr_elmt_page_num_array numArray;
378 is_required_flag_array varcharArray;
379 elmt_content_array varcharArray;
380 source_lang_array varcharArray;
381 language_array varcharArray;
382
383
384 L_QSNR_TTL_ALL_PAGE_DSP_FLAG varchar2(1);
385 L_QSNR_HDR_ALL_PAGE_DSP_FLAG varchar2(1);
386 L_QSNR_FTR_ALL_PAGE_DSP_FLAG varchar2(1);
387 QSNR_TITLE_ARRAY varcharArray;
388 QSNR_HEADER_ARRAY varcharArray;
389 QSNR_FOOTER_ARRAY varcharArray;
390 PRGM_SOURCE_LANG_ARRAY varcharArray;
391 PRGM_LANGUAGE_ARRAY varcharArray;
392
393 BEGIN
394 -- Standard Start of API savepoint
395 SAVEPOINT Copy_Row;
396
397 -- Standard call to check for call compatibility.
398 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
399 p_api_version_number,
400 l_api_name,
401 G_PKG_NAME)
402 THEN
403 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
404 END IF;
405
406
407 -- Debug Message
408 IF (PV_DEBUG_HIGH_ON) THEN
409 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
410 END IF;
411
412 -- Initialize message list if p_init_msg_list is set to TRUE.
413 IF FND_API.to_Boolean( p_init_msg_list )
414 THEN
415 FND_MSG_PUB.initialize;
416 END IF;
417
418 -- Initialize API return status to SUCCESS
419 x_return_status := FND_API.G_RET_STS_SUCCESS;
420
421
422 IF (PV_DEBUG_HIGH_ON) THEN
423 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' : before open cursor');
424 END IF;
425
426 OPEN c_get_qsnr_element_rec (p_src_object_id);
427 LOOP
428 IF (PV_DEBUG_HIGH_ON) THEN
429 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' : inside fetching qsnr b table');
430 END IF;
431 FETCH c_get_qsnr_element_rec bulk collect into
432 qsnr_elmt_seq_num_array, qsnr_elmt_type_array, entity_attr_id_array,
433 qsnr_elmt_page_num_array, is_required_flag_array LIMIT 100;
434
435 IF (PV_DEBUG_HIGH_ON) THEN
436 PVX_UTILITY_PVT.debug_message(l_api_name || 'qsnr_elmt_seq_num_array.count =' || to_char(qsnr_elmt_seq_num_array.count));
437 END IF;
438
439 for i in 1..qsnr_elmt_seq_num_array.count LOOP
440
441 l_dummy := NULL;
442 OPEN c_id;
443 LOOP
444 --FETCH c_id INTO l_checklist_item_id; anubhav changed to
445 FETCH c_id INTO qsnr_element_id_array(i);
446 OPEN c_id_exists(qsnr_element_id_array(i));
447 FETCH c_id_exists INTO l_dummy;
448 CLOSE c_id_exists;
449 EXIT WHEN l_dummy IS NULL;
450 END LOOP;
451 CLOSE c_id;
452 IF (PV_DEBUG_HIGH_ON) THEN
453 PVX_UTILITY_PVT.debug_message(l_api_name || 'qsnr_element_id_array(i) =' || to_char(qsnr_element_id_array(i)));
454 END IF;
455
456 END LOOP;
457
458
459
460 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' : insert into b table');
461
462 forall i in 1..qsnr_element_id_array.count
463 INSERT INTO pv_ge_qsnr_elements_b(
464 qsnr_element_id,
465 object_version_number,
466 arc_used_by_entity_code,
467 used_by_entity_id,
468 qsnr_elmt_seq_num,
469 qsnr_elmt_type,
470 entity_attr_id,
471 qsnr_elmt_page_num,
472 is_required_flag,
473 created_by,
474 creation_date,
475 last_updated_by,
476 last_update_date,
477 last_update_login
478 )
479 VALUES
480 (
481 qsnr_element_id_array(i),
482 1,
483 'PRGM',
484 p_tar_object_id,
485 qsnr_elmt_seq_num_array(i),
486 qsnr_elmt_type_array(i),
487 entity_attr_id_array(i),
488 qsnr_elmt_page_num_array(i),
489 is_required_flag_array(i),
490 FND_GLOBAL.USER_ID,
491 SYSDATE,
492 FND_GLOBAL.USER_ID,
493 SYSDATE,
494 FND_GLOBAL.CONC_LOGIN_ID
495 );
496
497
498 exit when c_get_qsnr_element_rec%notfound;
499 END LOOP;
500 Close c_get_qsnr_element_rec;
501
502
503 open c_get_qsnr_element_tl_rec(p_src_object_id);
504 LOOP
505 Fetch c_get_qsnr_element_tl_rec bulk collect into
506 old_qsnr_element_id_array, elmt_content_array, source_lang_array, language_array limit 100;
507
508 elmt_count := 1;
509 for k in 1..old_qsnr_element_id_array.count loop
510 if ((k <> 1) and (old_qsnr_element_id_array(k) <> old_qsnr_element_id_array(k-1))) then
511 elmt_count := elmt_count + 1;
512 end if;
513 IF (PV_DEBUG_HIGH_ON) THEN
514 PVX_UTILITY_PVT.debug_message(l_api_name || 'k = ' || to_char(k));
515 PVX_UTILITY_PVT.debug_message(l_api_name || 'elmt_count = ' || to_char(elmt_count));
516 END IF;
517 new_qsnr_element_id_array(k) := qsnr_element_id_array(elmt_count);
518
519 end loop;
520
521 Forall j in 1..old_qsnr_element_id_array.count
522 INSERT INTO pv_ge_qsnr_elements_tl(
523 qsnr_element_id ,
524 language ,
525 last_update_date ,
526 last_updated_by ,
527 creation_date ,
528 created_by ,
529 last_update_login ,
530 source_lang ,
531 elmt_content
532 )
533 values
534 (
535 new_qsnr_element_id_array(j),
536 language_array(j),
537 SYSDATE,
538 FND_GLOBAL.USER_ID,
539 SYSDATE,
540 FND_GLOBAL.USER_ID,
541 FND_GLOBAL.CONC_LOGIN_ID,
542 source_lang_array(j),
543 elmt_content_array(j)
544 );
545
546 exit when c_get_qsnr_element_tl_rec%notfound;
547 END LOOP;
548 close c_get_qsnr_element_tl_rec;
549
550 IF (PV_DEBUG_HIGH_ON) THEN
551 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' : out of qsnr loop');
552 END IF;
553
554
555 OPEN c_get_additional_tl_info (p_src_object_id);
556 LOOP
557 IF (PV_DEBUG_HIGH_ON) THEN
558 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' : inside get addl - tl loop');
559 END IF;
560 FETCH c_get_additional_tl_info bulk collect into
561 QSNR_TITLE_ARRAY, QSNR_HEADER_ARRAY, QSNR_FOOTER_ARRAY, PRGM_LANGUAGE_ARRAY LIMIT 100;
562 Forall i in 1..QSNR_TITLE_ARRAY.count
563 update pv_partner_program_tl
564 set
565 QSNR_TITLE = QSNR_TITLE_ARRAY(i),
566 QSNR_HEADER = QSNR_HEADER_ARRAY(i),
567 QSNR_FOOTER = QSNR_FOOTER_ARRAY(i),
568 LAST_UPDATED_BY = FND_GLOBAL.user_id,
569 LAST_UPDATE_DATE = sysdate,
570 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
571 where program_id = p_tar_object_id
572 and LANGUAGE = PRGM_LANGUAGE_ARRAY(i);
573
574 exit when c_get_additional_tl_info%notfound;
575 end LOOP;
576 CLOSE c_get_additional_tl_info;
577
578
579 -- Check for commit
580 IF FND_API.to_boolean(p_commit) THEN
581 COMMIT;
582 END IF;
583
584 FND_MSG_PUB.count_and_get(
585 p_encoded => FND_API.g_false
586 ,p_count => x_msg_count
587 ,p_data => x_msg_data
588 );
589
590 EXCEPTION
591
592 WHEN PVX_Utility_PVT.resource_locked THEN
593 x_return_status := FND_API.g_ret_sts_error;
594 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
595
596 WHEN FND_API.G_EXC_ERROR THEN
597 ROLLBACK TO Copy_Row;
598 x_return_status := FND_API.G_RET_STS_ERROR;
599 -- Standard call to get message count and if count=1, get the message
600 FND_MSG_PUB.Count_And_Get (
601 p_encoded => FND_API.G_FALSE,
602 p_count => x_msg_count,
603 p_data => x_msg_data
604 );
605
606 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
607 ROLLBACK TO Copy_Row;
608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
609 -- Standard call to get message count and if count=1, get the message
610 FND_MSG_PUB.Count_And_Get (
611 p_encoded => FND_API.G_FALSE,
612 p_count => x_msg_count,
613 p_data => x_msg_data
614 );
615
616 WHEN OTHERS THEN
617 ROLLBACK TO Copy_Row;
618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
619 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
620 THEN
621 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
622 END IF;
623 -- Standard call to get message count and if count=1, get the message
624 FND_MSG_PUB.Count_And_Get (
625 p_encoded => FND_API.G_FALSE,
626 p_count => x_msg_count,
627 p_data => x_msg_data
628 );
629
630 END Copy_Row;
631
632
633
634 -- ==============================================================================
635 -- Start of Comments
636 -- ==============================================================================
637 -- API Name
638 -- Update_Gq_Elements
639 -- Type
640 -- Private
641 -- Pre-Req
642 --
643 -- Parameters
644 --
645 -- IN
646 -- p_api_version_number IN NUMBER Required
647 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
648 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
649 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
650 -- p_qsnr_element_rec IN qsnr_element_rec_type Required
651 --
652 -- OUT
653 -- x_return_status OUT VARCHAR2
654 -- x_msg_count OUT NUMBER
655 -- x_msg_data OUT VARCHAR2
656 -- Version : Current version 1.0
657 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
658 -- and basic operation, developer must manually add parameters and business logic as necessary.
659 --
660 -- History
661 --
662 -- NOTE
663 --
664 -- End of Comments
665 -- ==============================================================================
666
667 PROCEDURE Update_Gq_Elements(
668 p_api_version_number IN NUMBER,
669 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
670 p_commit IN VARCHAR2 := FND_API.G_FALSE,
671 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
672
673 x_return_status OUT NOCOPY VARCHAR2,
674 x_msg_count OUT NOCOPY NUMBER,
675 x_msg_data OUT NOCOPY VARCHAR2,
676
677 p_qsnr_element_rec IN qsnr_element_rec_type,
678 x_object_version_number OUT NOCOPY NUMBER
679 )
680
681 IS
682
683
684 CURSOR c_get_gq_elements(qsnr_element_id NUMBER) IS
685 SELECT *
686 FROM PV_GE_QSNR_ELEMENTS_B
687 WHERE qsnr_element_id = p_qsnr_element_rec.qsnr_element_id;
688 -- Hint: Developer need to provide Where clause
689
690
691 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Gq_Elements';
692 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
693 -- Local Variables
694 --l_object_version_number NUMBER;
695 l_object_version_number NUMBER := p_qsnr_element_rec.object_version_number;
696 l_qsnr_element_id NUMBER;
697 l_ref_qsnr_element_rec c_get_Gq_Elements%ROWTYPE ;
698 l_tar_qsnr_element_rec qsnr_element_rec_type := p_qsnr_element_rec;
699 l_rowid ROWID;
700
701 BEGIN
702 -- Standard Start of API savepoint
703 SAVEPOINT update_gq_elements_pvt;
704
705 -- Standard call to check for call compatibility.
706 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
707 p_api_version_number,
708 l_api_name,
709 G_PKG_NAME)
710 THEN
711 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
712 END IF;
713
714
715 -- Initialize message list if p_init_msg_list is set to TRUE.
716 IF FND_API.to_Boolean( p_init_msg_list )
717 THEN
718 FND_MSG_PUB.initialize;
719 END IF;
720
721
722
723 -- Debug Message
724 IF (PV_DEBUG_HIGH_ON) THEN
725
726 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
727 END IF;
728
729
730
731 -- Initialize API return status to SUCCESS
732 x_return_status := FND_API.G_RET_STS_SUCCESS;
733
734 -- Debug Message
735 IF (PV_DEBUG_HIGH_ON) THEN
736
737 PVX_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
738 END IF;
739
740 OPEN c_get_Gq_Elements( l_tar_qsnr_element_rec.qsnr_element_id);
741
742 FETCH c_get_Gq_Elements INTO l_ref_qsnr_element_rec ;
743
744 If ( c_get_Gq_Elements%NOTFOUND) THEN
745 PVX_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
746 p_token_name => 'INFO',
747 p_token_value => 'Gq_Elements') ;
748 RAISE FND_API.G_EXC_ERROR;
749 END IF;
750 -- Debug Message
751 IF (PV_DEBUG_HIGH_ON) THEN
752
753 PVX_UTILITY_PVT.debug_message('Private API: - Close Cursor');
754 END IF;
755 CLOSE c_get_Gq_Elements;
756
757
758 If (l_tar_qsnr_element_rec.object_version_number is NULL or
759 l_tar_qsnr_element_rec.object_version_number = FND_API.G_MISS_NUM ) Then
760 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_VERSION_MISSING',
761 p_token_name => 'COLUMN',
762 p_token_value => 'Object_Version_Number') ;
763 raise FND_API.G_EXC_ERROR;
764 End if;
765 -- Check Whether record has been changed by someone else
766 IF (PV_DEBUG_HIGH_ON) THEN
767
768 PVX_UTILITY_PVT.debug_message('tar: object_version_number = '|| l_tar_qsnr_element_rec.object_version_number);
769 END IF;
770 IF (PV_DEBUG_HIGH_ON) THEN
771
772 PVX_UTILITY_PVT.debug_message('ref: object_version_number = '|| l_ref_qsnr_element_rec.object_version_number);
773 END IF;
774
775 If (l_tar_qsnr_element_rec.object_version_number <> l_ref_qsnr_element_rec.object_version_number) Then
776 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RECORD_CHANGED',
777 p_token_name => 'VALUE',
778 p_token_value => 'Gq_Elements') ;
779 raise FND_API.G_EXC_ERROR;
780 End if;
781
782
783 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
784 THEN
785 -- Debug message
786 IF (PV_DEBUG_HIGH_ON) THEN
787
788 PVX_UTILITY_PVT.debug_message('Private API: Validate_Gq_Elements');
789 END IF;
790
791 -- Invoke validation procedures
792 Validate_gq_elements(
793 p_api_version_number => 1.0,
794 p_init_msg_list => FND_API.G_FALSE,
795 p_validation_level => p_validation_level,
796 p_validation_mode => JTF_PLSQL_API.g_update,
797 p_qsnr_element_rec => p_qsnr_element_rec,
798 x_return_status => x_return_status,
799 x_msg_count => x_msg_count,
800 x_msg_data => x_msg_data);
801 END IF;
802
803 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
804 RAISE FND_API.G_EXC_ERROR;
805 END IF;
806
807 -- Debug Message
808 IF (PV_DEBUG_HIGH_ON) THEN
809 PVX_UTILITY_PVT.debug_message('Private API: Calling update table handler');
810 END IF;
811
812 IF (PV_DEBUG_HIGH_ON) THEN
813 PVX_UTILITY_PVT.debug_message('p_qsnr_element_rec.qsnr_element_id = ' || p_qsnr_element_rec.qsnr_element_id);
814 END IF;
815
816 IF (PV_DEBUG_HIGH_ON) THEN
817 PVX_UTILITY_PVT.debug_message('p_qsnr_element_rec.qsnr_elmt_seq_num = ' || p_qsnr_element_rec.qsnr_elmt_seq_num);
818 END IF;
819
820 IF (PV_DEBUG_HIGH_ON) THEN
821 PVX_UTILITY_PVT.debug_message('p_qsnr_element_rec.elmt_content = ' || p_qsnr_element_rec.elmt_content);
822 END IF;
823
824 IF (PV_DEBUG_HIGH_ON) THEN
825 PVX_UTILITY_PVT.debug_message('p_qsnr_element_rec.entity_attr_id = ' || p_qsnr_element_rec.entity_attr_id);
826 IF (p_qsnr_element_rec.entity_attr_id = FND_API.G_MISS_NUM) THEN
827 PVX_UTILITY_PVT.debug_message('p_qsnr_element_rec.entity_attr_id = FND_API.G_MISS_NUM');
828 ELSIF (p_qsnr_element_rec.entity_attr_id is NULL) THEN
829 PVX_UTILITY_PVT.debug_message('p_qsnr_element_rec.entity_attr_id is NULL');
830 END IF;
831 END IF;
832
833 IF (PV_DEBUG_HIGH_ON) THEN
834 PVX_UTILITY_PVT.debug_message('p_qsnr_element_rec.is_required_flag = ' || p_qsnr_element_rec.is_required_flag);
835 END IF;
836
837
838 IF ((p_qsnr_element_rec.entity_attr_id is NULL or p_qsnr_element_rec.entity_attr_id = FND_API.G_MISS_NUM)
839 and p_qsnr_element_rec.is_required_flag = 'Y') THEN
840 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_MANDATORY_NO_ATTR');
841 raise FND_API.G_EXC_ERROR;
842 End if;
843
844 -- Invoke table handler(Pv_Gq_Elements_Pkg.Update_Row)
845 Pv_Gq_Elements_Pkg.Update_Row(
846 p_qsnr_element_id => p_qsnr_element_rec.qsnr_element_id,
847 px_object_version_number => l_object_version_number,
848 p_arc_used_by_entity_code => p_qsnr_element_rec.arc_used_by_entity_code,
849 p_used_by_entity_id => p_qsnr_element_rec.used_by_entity_id,
850 p_qsnr_elmt_seq_num => p_qsnr_element_rec.qsnr_elmt_seq_num,
851 p_qsnr_elmt_type => p_qsnr_element_rec.qsnr_elmt_type,
852 p_entity_attr_id => p_qsnr_element_rec.entity_attr_id,
853 p_qsnr_elmt_page_num => p_qsnr_element_rec.qsnr_elmt_page_num,
854 p_is_required_flag => p_qsnr_element_rec.is_required_flag,
855 p_last_updated_by => FND_GLOBAL.USER_ID,
856 p_last_update_date => SYSDATE,
857 p_last_update_login => FND_GLOBAL.conc_login_id,
858 p_elmt_content => p_qsnr_element_rec.elmt_content
859 );
860 x_object_version_number := l_object_version_number;
861 --
862 -- End of API body.
863 --
864
865 -- Standard check for p_commit
866 IF FND_API.to_Boolean( p_commit )
867 THEN
868 COMMIT WORK;
869 END IF;
870
871
872 -- Debug Message
873 IF (PV_DEBUG_HIGH_ON) THEN
874
875 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
876 END IF;
877
878
879 -- Standard call to get message count and if count is 1, get message info.
880 FND_MSG_PUB.Count_And_Get
881 (p_count => x_msg_count,
882 p_data => x_msg_data
883 );
884 EXCEPTION
885
886 WHEN PVX_Utility_PVT.resource_locked THEN
887 x_return_status := FND_API.g_ret_sts_error;
888 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
889
890 WHEN FND_API.G_EXC_ERROR THEN
891 ROLLBACK TO UPDATE_Gq_Elements_PVT;
892 x_return_status := FND_API.G_RET_STS_ERROR;
893 -- Standard call to get message count and if count=1, get the message
894 FND_MSG_PUB.Count_And_Get (
895 p_encoded => FND_API.G_FALSE,
896 p_count => x_msg_count,
897 p_data => x_msg_data
898 );
899
900 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
901 ROLLBACK TO UPDATE_Gq_Elements_PVT;
902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
903 -- Standard call to get message count and if count=1, get the message
904 FND_MSG_PUB.Count_And_Get (
905 p_encoded => FND_API.G_FALSE,
906 p_count => x_msg_count,
907 p_data => x_msg_data
908 );
909
910 WHEN OTHERS THEN
911 ROLLBACK TO UPDATE_Gq_Elements_PVT;
912 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
913 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
914 THEN
915 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
916 END IF;
917 -- Standard call to get message count and if count=1, get the message
918 FND_MSG_PUB.Count_And_Get (
919 p_encoded => FND_API.G_FALSE,
920 p_count => x_msg_count,
921 p_data => x_msg_data
922 );
923 End Update_Gq_Elements;
924
925
926 -- ==============================================================================
927 -- Start of Comments
928 -- ==============================================================================
929 -- API Name
930 -- Delete_Gq_Elements
931 -- Type
932 -- Private
933 -- Pre-Req
934 --
935 -- Parameters
936 --
937 -- IN
938 -- p_api_version_number IN NUMBER Required
939 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
940 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
941 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
942 -- p_qsnr_element_id IN NUMBER
943 -- p_object_version_number IN NUMBER Optional Default = NULL
944 --
945 -- OUT
946 -- x_return_status OUT VARCHAR2
947 -- x_msg_count OUT NUMBER
948 -- x_msg_data OUT VARCHAR2
949 -- Version : Current version 1.0
950 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
951 -- and basic operation, developer must manually add parameters and business logic as necessary.
952 --
953 -- History
954 --
955 -- NOTE
956 --
957 -- End of Comments
958 -- ==============================================================================
959
960 PROCEDURE Delete_Gq_Elements(
961 p_api_version_number IN NUMBER,
962 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
963 p_commit IN VARCHAR2 := FND_API.G_FALSE,
964 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
965 x_return_status OUT NOCOPY VARCHAR2,
966 x_msg_count OUT NOCOPY NUMBER,
967 x_msg_data OUT NOCOPY VARCHAR2,
968 p_qsnr_element_id IN NUMBER,
969 p_object_version_number IN NUMBER
970 )
971
972 IS
973
974 CURSOR c_get_elmt_greater_than (cv_qsnr_element_id NUMBER) IS
975 select gq.qsnr_element_id, gq.qsnr_elmt_seq_num, gq.qsnr_elmt_page_num, gq.object_version_number
976 from pv_ge_qsnr_elements_vl gq,
977 (select arc_used_by_entity_code, used_by_entity_id, qsnr_elmt_seq_num
978 from pv_ge_qsnr_elements_vl
979 where qsnr_element_id = cv_qsnr_element_id) tmp
980 where gq.arc_used_by_entity_code = tmp.arc_used_by_entity_code
981 and gq.used_by_entity_id = tmp.used_by_entity_id
982 and gq.qsnr_elmt_seq_num > tmp.qsnr_elmt_seq_num;
983
984 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Gq_Elements';
985 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
986 l_object_version_number NUMBER;
987 l_get_elmt_greater_than c_get_elmt_greater_than%ROWTYPE;
988 l_qsnr_element_rec qsnr_element_rec_type := g_miss_qsnr_element_rec;
989
990 BEGIN
991 -- Standard Start of API savepoint
992 SAVEPOINT delete_gq_elements_pvt;
993
994 -- Standard call to check for call compatibility.
995 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
996 p_api_version_number,
997 l_api_name,
998 G_PKG_NAME)
999 THEN
1000 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1001 END IF;
1002
1003
1004 -- Initialize message list if p_init_msg_list is set to TRUE.
1005 IF FND_API.to_Boolean( p_init_msg_list )
1006 THEN
1007 FND_MSG_PUB.initialize;
1008 END IF;
1009
1010
1011
1012 -- Debug Message
1013 IF (PV_DEBUG_HIGH_ON) THEN
1014
1015 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1016 END IF;
1017
1018
1019
1020 -- Initialize API return status to SUCCESS
1021 x_return_status := FND_API.G_RET_STS_SUCCESS;
1022
1023 --
1024 -- Api body
1025 --
1026
1027 -- Update the qsnr_elmt_page_num field of all records which have
1028 -- larger qsnr_element_id than the deleted one
1029 FOR l_get_elmt_greater_than IN c_get_elmt_greater_than (p_qsnr_element_id)
1030 LOOP
1031 l_qsnr_element_rec.qsnr_element_id := l_get_elmt_greater_than.qsnr_element_id;
1032 l_qsnr_element_rec.qsnr_elmt_seq_num := l_get_elmt_greater_than.qsnr_elmt_seq_num - 1;
1033 l_qsnr_element_rec.object_version_number := l_get_elmt_greater_than.object_version_number;
1034 IF (PV_DEBUG_HIGH_ON) THEN
1035
1036 PVX_UTILITY_PVT.debug_message('qsnr_element_id = ' || l_qsnr_element_rec.qsnr_element_id);
1037 END IF;
1038 IF (PV_DEBUG_HIGH_ON) THEN
1039
1040 PVX_UTILITY_PVT.debug_message('qsnr_elmt_page_num = ' || l_qsnr_element_rec.qsnr_elmt_page_num);
1041 END IF;
1042 IF (PV_DEBUG_HIGH_ON) THEN
1043
1044 PVX_UTILITY_PVT.debug_message('object_version_number = ' || l_qsnr_element_rec.object_version_number);
1045 END IF;
1046
1047 Update_Gq_Elements ( p_api_version_number => p_api_version_number
1048 ,p_init_msg_list => p_init_msg_list
1049 ,p_commit => p_commit
1050 ,p_validation_level => p_validation_level
1051 ,x_return_status => x_return_status
1052 ,x_msg_count => x_msg_count
1053 ,x_msg_data => x_msg_data
1054 ,p_qsnr_element_rec => l_qsnr_element_rec
1055 ,x_object_version_number => l_object_version_number
1056 );
1057
1058 END LOOP;
1059
1060 -- Debug Message
1061 IF (PV_DEBUG_HIGH_ON) THEN
1062
1063 PVX_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
1064 END IF;
1065
1066 -- Invoke table handler(Pv_Gq_Elements_Pkg.Delete_Row)
1067 Pv_Gq_Elements_Pkg.Delete_Row(
1068 p_qsnr_element_id => p_qsnr_element_id,
1069 p_object_version_number => p_object_version_number );
1070
1071 --
1072 -- End of API body
1073 --
1074
1075 -- Standard check for p_commit
1076 IF FND_API.to_Boolean( p_commit )
1077 THEN
1078 COMMIT WORK;
1079 END IF;
1080
1081
1082 -- Debug Message
1083 IF (PV_DEBUG_HIGH_ON) THEN
1084
1085 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1086 END IF;
1087
1088
1089 -- Standard call to get message count and if count is 1, get message info.
1090 FND_MSG_PUB.Count_And_Get
1091 (p_count => x_msg_count,
1092 p_data => x_msg_data
1093 );
1094 EXCEPTION
1095
1096 WHEN PVX_Utility_PVT.resource_locked THEN
1097 x_return_status := FND_API.g_ret_sts_error;
1098 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
1099
1100 WHEN FND_API.G_EXC_ERROR THEN
1101 ROLLBACK TO DELETE_Gq_Elements_PVT;
1102 x_return_status := FND_API.G_RET_STS_ERROR;
1103 -- Standard call to get message count and if count=1, get the message
1104 FND_MSG_PUB.Count_And_Get (
1105 p_encoded => FND_API.G_FALSE,
1106 p_count => x_msg_count,
1107 p_data => x_msg_data
1108 );
1109
1110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1111 ROLLBACK TO DELETE_Gq_Elements_PVT;
1112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1113 -- Standard call to get message count and if count=1, get the message
1114 FND_MSG_PUB.Count_And_Get (
1115 p_encoded => FND_API.G_FALSE,
1116 p_count => x_msg_count,
1117 p_data => x_msg_data
1118 );
1119
1120 WHEN OTHERS THEN
1121 ROLLBACK TO DELETE_Gq_Elements_PVT;
1122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1123 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1124 THEN
1125 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1126 END IF;
1127 -- Standard call to get message count and if count=1, get the message
1128 FND_MSG_PUB.Count_And_Get (
1129 p_encoded => FND_API.G_FALSE,
1130 p_count => x_msg_count,
1131 p_data => x_msg_data
1132 );
1133 End Delete_Gq_Elements;
1134
1135
1136 -- ==============================================================================
1137 -- Start of Comments
1138 -- ==============================================================================
1139 -- API Name
1140 -- Delete_Gq_PB_Elements
1141 -- Type
1142 -- Private
1143 -- Pre-Req
1144 --
1145 -- Parameters
1146 --
1147 -- IN
1148 -- p_api_version_number IN NUMBER Required
1149 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
1150 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1151 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1152 -- p_qsnr_element_id IN NUMBER
1153 -- p_object_version_number IN NUMBER Optional Default = NULL
1154 --
1155 -- OUT
1156 -- x_return_status OUT VARCHAR2
1157 -- x_msg_count OUT NUMBER
1158 -- x_msg_data OUT VARCHAR2
1159 -- Version : Current version 1.0
1160 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
1161 -- and basic operation, developer must manually add parameters and business logic as necessary.
1162 --
1163 -- History
1164 --
1165 -- NOTE
1166 --
1167 -- End of Comments
1168 -- ==============================================================================
1169
1170 PROCEDURE Delete_Gq_PB_Elements(
1171 p_api_version_number IN NUMBER,
1172 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1173 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1174 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1175 x_return_status OUT NOCOPY VARCHAR2,
1176 x_msg_count OUT NOCOPY NUMBER,
1177 x_msg_data OUT NOCOPY VARCHAR2,
1178 p_qsnr_element_id IN NUMBER,
1179 p_object_version_number IN NUMBER
1180 )
1181
1182 IS
1183
1184 CURSOR c_get_elmt_greater_than (cv_qsnr_element_id NUMBER) IS
1185 select gq.qsnr_element_id, gq.qsnr_elmt_seq_num, gq.qsnr_elmt_page_num, gq.object_version_number
1186 from pv_ge_qsnr_elements_vl gq,
1187 (select arc_used_by_entity_code, used_by_entity_id, qsnr_elmt_seq_num
1188 from pv_ge_qsnr_elements_vl
1189 where qsnr_element_id = cv_qsnr_element_id) tmp
1190 where gq.arc_used_by_entity_code = tmp.arc_used_by_entity_code
1191 and gq.used_by_entity_id = tmp.used_by_entity_id
1192 and gq.qsnr_elmt_seq_num > tmp.qsnr_elmt_seq_num;
1193
1194 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Gq_PB_Elements';
1195 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1196 l_object_version_number NUMBER;
1197 l_get_elmt_greater_than c_get_elmt_greater_than%ROWTYPE;
1198 l_qsnr_element_rec qsnr_element_rec_type := g_miss_qsnr_element_rec;
1199
1200 BEGIN
1201 -- Standard Start of API savepoint
1202 SAVEPOINT Delete_Gq_PB_Elements_pvt;
1203
1204 -- Standard call to check for call compatibility.
1205 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1206 p_api_version_number,
1207 l_api_name,
1208 G_PKG_NAME)
1209 THEN
1210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1211 END IF;
1212
1213
1214 -- Initialize message list if p_init_msg_list is set to TRUE.
1215 IF FND_API.to_Boolean( p_init_msg_list )
1216 THEN
1217 FND_MSG_PUB.initialize;
1218 END IF;
1219
1220
1221
1222 -- Debug Message
1223 IF (PV_DEBUG_HIGH_ON) THEN
1224
1225 PVX_UTILITY_PVT.debug_message('Private API: ...' || l_api_name || ' start');
1226 END IF;
1227
1228
1229
1230 -- Initialize API return status to SUCCESS
1231 x_return_status := FND_API.G_RET_STS_SUCCESS;
1232
1233 --
1234 -- Api body
1235 --
1236 -- Debug Message
1237 IF (PV_DEBUG_HIGH_ON) THEN
1238
1239 PVX_UTILITY_PVT.debug_message( 'p_qsnr_element_id = ' || p_qsnr_element_id);
1240 END IF;
1241 IF (PV_DEBUG_HIGH_ON) THEN
1242
1243 PVX_UTILITY_PVT.debug_message( 'p_object_version_number = ' || p_object_version_number);
1244 END IF;
1245
1246 IF (PV_DEBUG_HIGH_ON) THEN
1247
1248
1249
1250 PVX_UTILITY_PVT.debug_message( 'Private API: Updating qsnr_elmt_seq/page_num field');
1251
1252 END IF;
1253
1254 -- Update the qsnr_elmt_page_num field of all records which have
1255 -- larger qsnr_element_id than the deleted one
1256 FOR l_get_elmt_greater_than IN c_get_elmt_greater_than (p_qsnr_element_id)
1257 LOOP
1258 l_qsnr_element_rec.qsnr_element_id := l_get_elmt_greater_than.qsnr_element_id;
1259 l_qsnr_element_rec.qsnr_elmt_seq_num := l_get_elmt_greater_than.qsnr_elmt_seq_num - 1;
1260 l_qsnr_element_rec.qsnr_elmt_page_num := l_get_elmt_greater_than.qsnr_elmt_page_num - 1;
1261 l_qsnr_element_rec.object_version_number := l_get_elmt_greater_than.object_version_number;
1262 IF (PV_DEBUG_HIGH_ON) THEN
1263
1264 PVX_UTILITY_PVT.debug_message('qsnr_element_id = ' || l_qsnr_element_rec.qsnr_element_id);
1265 END IF;
1266 IF (PV_DEBUG_HIGH_ON) THEN
1267
1268 PVX_UTILITY_PVT.debug_message('qsnr_elmt_page_num = ' || l_qsnr_element_rec.qsnr_elmt_page_num);
1269 END IF;
1270 IF (PV_DEBUG_HIGH_ON) THEN
1271
1272 PVX_UTILITY_PVT.debug_message('object_version_number = ' || l_qsnr_element_rec.object_version_number);
1273 END IF;
1274
1275 Update_Gq_Elements ( p_api_version_number => p_api_version_number
1276 ,p_init_msg_list => p_init_msg_list
1277 ,p_commit => p_commit
1278 ,p_validation_level => p_validation_level
1279 ,x_return_status => x_return_status
1280 ,x_msg_count => x_msg_count
1281 ,x_msg_data => x_msg_data
1282 ,p_qsnr_element_rec => l_qsnr_element_rec
1283 ,x_object_version_number => l_object_version_number
1284 );
1285
1286 END LOOP;
1287
1288 IF (PV_DEBUG_HIGH_ON) THEN
1289
1290
1291
1292 PVX_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler...');
1293
1294 END IF;
1295 -- Invoke table handler(Pv_Gq_Elements_Pkg.Delete_Row)
1296 Pv_Gq_Elements_Pkg.Delete_Row(
1297 p_qsnr_element_id => p_qsnr_element_id,
1298 p_object_version_number => p_object_version_number );
1299 --
1300 -- End of API body
1301 --
1302
1303 -- Standard check for p_commit
1304 IF FND_API.to_Boolean( p_commit )
1305 THEN
1306 COMMIT WORK;
1307 END IF;
1308
1309
1310 -- Debug Message
1311 IF (PV_DEBUG_HIGH_ON) THEN
1312
1313 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1314 END IF;
1315
1316
1317 -- Standard call to get message count and if count is 1, get message info.
1318 FND_MSG_PUB.Count_And_Get
1319 (p_count => x_msg_count,
1320 p_data => x_msg_data
1321 );
1322 EXCEPTION
1323
1324 WHEN PVX_Utility_PVT.resource_locked THEN
1325 x_return_status := FND_API.g_ret_sts_error;
1326 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
1327
1328 WHEN FND_API.G_EXC_ERROR THEN
1329 ROLLBACK TO Delete_Gq_PB_Elements_PVT;
1330 x_return_status := FND_API.G_RET_STS_ERROR;
1331 -- Standard call to get message count and if count=1, get the message
1332 FND_MSG_PUB.Count_And_Get (
1333 p_encoded => FND_API.G_FALSE,
1334 p_count => x_msg_count,
1335 p_data => x_msg_data
1336 );
1337
1338 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1339 ROLLBACK TO Delete_Gq_PB_Elements_PVT;
1340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1341 -- Standard call to get message count and if count=1, get the message
1342 FND_MSG_PUB.Count_And_Get (
1343 p_encoded => FND_API.G_FALSE,
1344 p_count => x_msg_count,
1345 p_data => x_msg_data
1346 );
1347
1348 WHEN OTHERS THEN
1349 ROLLBACK TO Delete_Gq_PB_Elements_PVT;
1350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1351 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1352 THEN
1353 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1354 END IF;
1355 -- Standard call to get message count and if count=1, get the message
1356 FND_MSG_PUB.Count_And_Get (
1357 p_encoded => FND_API.G_FALSE,
1358 p_count => x_msg_count,
1359 p_data => x_msg_data
1360 );
1361 End Delete_Gq_PB_Elements;
1362
1363
1364
1365 -- Hint: Primary key needs to be returned.
1366 -- ==============================================================================
1367 -- Start of Comments
1368 -- ==============================================================================
1369 -- API Name
1370 -- Lock_Gq_Elements
1371 -- Type
1372 -- Private
1373 -- Pre-Req
1374 --
1375 -- Parameters
1376 --
1377 -- IN
1378 -- p_api_version_number IN NUMBER Required
1379 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
1380 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1381 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1382 -- p_qsnr_element_rec IN qsnr_element_rec_type Required
1383 --
1384 -- OUT
1385 -- x_return_status OUT VARCHAR2
1386 -- x_msg_count OUT NUMBER
1387 -- x_msg_data OUT VARCHAR2
1388 -- Version : Current version 1.0
1389 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
1390 -- and basic operation, developer must manually add parameters and business logic as necessary.
1391 --
1392 -- History
1393 --
1394 -- NOTE
1395 --
1396 -- End of Comments
1397 -- ==============================================================================
1398
1399 PROCEDURE Lock_Gq_Elements(
1400 p_api_version_number IN NUMBER,
1401 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1402
1403 x_return_status OUT NOCOPY VARCHAR2,
1404 x_msg_count OUT NOCOPY NUMBER,
1405 x_msg_data OUT NOCOPY VARCHAR2,
1406
1407 p_qsnr_element_id IN NUMBER,
1408 p_object_version IN NUMBER
1409 )
1410
1411 IS
1412 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Gq_Elements';
1413 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1414 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1415 l_qsnr_element_id NUMBER;
1416
1417 BEGIN
1418
1419 -- Debug Message
1420 IF (PV_DEBUG_HIGH_ON) THEN
1421
1422 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1423 END IF;
1424
1425
1426 -- Initialize message list if p_init_msg_list is set to TRUE.
1427 IF FND_API.to_Boolean( p_init_msg_list )
1428 THEN
1429 FND_MSG_PUB.initialize;
1430 END IF;
1431
1432
1433
1434 -- Standard call to check for call compatibility.
1435 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1436 p_api_version_number,
1437 l_api_name,
1438 G_PKG_NAME)
1439 THEN
1440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1441 END IF;
1442
1443
1444
1445 -- Initialize API return status to SUCCESS
1446 x_return_status := FND_API.G_RET_STS_SUCCESS;
1447
1448
1449 ------------------------ lock -------------------------
1450 Pv_Gq_Elements_Pkg.Lock_Row(l_qsnr_element_id,p_object_version);
1451
1452
1453 -------------------- finish --------------------------
1454 FND_MSG_PUB.count_and_get(
1455 p_encoded => FND_API.g_false,
1456 p_count => x_msg_count,
1457 p_data => x_msg_data);
1458 IF (PV_DEBUG_HIGH_ON) THEN
1459
1460 PVX_Utility_PVT.debug_message(l_full_name ||': end');
1461 END IF;
1462 EXCEPTION
1463
1464 WHEN PVX_Utility_PVT.resource_locked THEN
1465 x_return_status := FND_API.g_ret_sts_error;
1466 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
1467
1468 WHEN FND_API.G_EXC_ERROR THEN
1469 ROLLBACK TO LOCK_Gq_Elements_PVT;
1470 x_return_status := FND_API.G_RET_STS_ERROR;
1471 -- Standard call to get message count and if count=1, get the message
1472 FND_MSG_PUB.Count_And_Get (
1473 p_encoded => FND_API.G_FALSE,
1474 p_count => x_msg_count,
1475 p_data => x_msg_data
1476 );
1477
1478 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1479 ROLLBACK TO LOCK_Gq_Elements_PVT;
1480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1481 -- Standard call to get message count and if count=1, get the message
1482 FND_MSG_PUB.Count_And_Get (
1483 p_encoded => FND_API.G_FALSE,
1484 p_count => x_msg_count,
1485 p_data => x_msg_data
1486 );
1487
1488 WHEN OTHERS THEN
1489 ROLLBACK TO LOCK_Gq_Elements_PVT;
1490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1491 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1492 THEN
1493 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1494 END IF;
1495 -- Standard call to get message count and if count=1, get the message
1496 FND_MSG_PUB.Count_And_Get (
1497 p_encoded => FND_API.G_FALSE,
1498 p_count => x_msg_count,
1499 p_data => x_msg_data
1500 );
1501 End Lock_Gq_Elements;
1502
1503
1504
1505
1506 PROCEDURE check_Qsnr_Element_Uk_Items(
1507 p_qsnr_element_rec IN qsnr_element_rec_type,
1508 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1509 x_return_status OUT NOCOPY VARCHAR2)
1510 IS
1511 l_valid_flag VARCHAR2(1);
1512
1513 BEGIN
1514 x_return_status := FND_API.g_ret_sts_success;
1515 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1516 l_valid_flag := PVX_Utility_PVT.check_uniqueness(
1517 'pv_ge_qsnr_elements_b',
1518 'qsnr_element_id = ''' || p_qsnr_element_rec.qsnr_element_id ||''''
1519 );
1520
1521 IF l_valid_flag = FND_API.g_false THEN
1522 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_qsnr_element_id_DUPLICATE');
1523 x_return_status := FND_API.g_ret_sts_error;
1524 END IF;
1525 END IF;
1526
1527 -- Fixed for bug #3380368
1528 IF p_qsnr_element_rec.qsnr_elmt_type = 'QUESTION' and
1529 p_qsnr_element_rec.entity_attr_id is not null THEN
1530 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1531 l_valid_flag := PVX_Utility_PVT.check_uniqueness(
1532 'pv_ge_qsnr_elements_b',
1533 'used_by_entity_id = ''' || p_qsnr_element_rec.used_by_entity_id ||''' AND entity_attr_id = ''' || p_qsnr_element_rec.entity_attr_id || '''');
1534
1535 IF l_valid_flag = FND_API.g_false THEN
1536 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_QSNR_PROFILE_ATTR_DUPLICATE');
1537 x_return_status := FND_API.g_ret_sts_error;
1538 END IF;
1539 ELSIF p_validation_mode = JTF_PLSQL_API.g_update THEN
1540 l_valid_flag := PVX_Utility_PVT.check_uniqueness(
1541 'pv_ge_qsnr_elements_b',
1542 'used_by_entity_id = ''' || p_qsnr_element_rec.used_by_entity_id ||''' AND entity_attr_id = ''' || p_qsnr_element_rec.entity_attr_id || ''' AND QSNR_ELEMENT_ID <> ''' || p_qsnr_element_rec.QSNR_ELEMENT_ID || '''');
1543
1544 IF l_valid_flag = FND_API.g_false THEN
1545 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_QSNR_PROFILE_ATTR_DUPLICATE');
1546 x_return_status := FND_API.g_ret_sts_error;
1547 END IF;
1548 END IF;
1549 END IF;
1550
1551 END check_Qsnr_Element_Uk_Items;
1552
1553
1554
1555 PROCEDURE check_Qsnr_Element_Req_Items(
1556 p_qsnr_element_rec IN qsnr_element_rec_type,
1557 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1558 x_return_status OUT NOCOPY VARCHAR2
1559 )
1560 IS
1561 BEGIN
1562 x_return_status := FND_API.g_ret_sts_success;
1563
1564 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1565
1566
1567 IF p_qsnr_element_rec.qsnr_element_id = FND_API.G_MISS_NUM OR p_qsnr_element_rec.qsnr_element_id IS NULL THEN
1568 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'QSNR_ELEMENT_ID' );
1569 x_return_status := FND_API.g_ret_sts_error;
1570 END IF;
1571
1572
1573 IF p_qsnr_element_rec.object_version_number = FND_API.G_MISS_NUM OR p_qsnr_element_rec.object_version_number IS NULL THEN
1574 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'OBJECT_VERSION_NUMBER' );
1575 x_return_status := FND_API.g_ret_sts_error;
1576 END IF;
1577
1578
1579 IF p_qsnr_element_rec.arc_used_by_entity_code = FND_API.g_miss_char OR p_qsnr_element_rec.arc_used_by_entity_code IS NULL THEN
1580 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'ARC_USED_BY_ENTITY_CODE' );
1581 x_return_status := FND_API.g_ret_sts_error;
1582 END IF;
1583
1584
1585 IF p_qsnr_element_rec.used_by_entity_id = FND_API.G_MISS_NUM OR p_qsnr_element_rec.used_by_entity_id IS NULL THEN
1586 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'USED_BY_ENTITY_ID' );
1587 x_return_status := FND_API.g_ret_sts_error;
1588 END IF;
1589
1590
1591 IF p_qsnr_element_rec.qsnr_elmt_seq_num = FND_API.G_MISS_NUM OR p_qsnr_element_rec.qsnr_elmt_seq_num IS NULL THEN
1592 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'QSNR_ELMT_SEQ_NUM' );
1593 x_return_status := FND_API.g_ret_sts_error;
1594 END IF;
1595
1596
1597 IF p_qsnr_element_rec.qsnr_elmt_type = FND_API.g_miss_char OR p_qsnr_element_rec.qsnr_elmt_type IS NULL THEN
1598 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'QSNR_ELMT_TYPE' );
1599 x_return_status := FND_API.g_ret_sts_error;
1600 END IF;
1601
1602
1603 IF p_qsnr_element_rec.qsnr_elmt_page_num = FND_API.G_MISS_NUM OR p_qsnr_element_rec.qsnr_elmt_page_num IS NULL THEN
1604 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'QSNR_ELMT_PAGE_NUM' );
1605 x_return_status := FND_API.g_ret_sts_error;
1606 END IF;
1607
1608
1609 IF p_qsnr_element_rec.is_required_flag = FND_API.g_miss_char OR p_qsnr_element_rec.is_required_flag IS NULL THEN
1610 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'IS_REQUIRED_FLAG' );
1611 x_return_status := FND_API.g_ret_sts_error;
1612 END IF;
1613
1614
1615 IF p_qsnr_element_rec.created_by = FND_API.G_MISS_NUM OR p_qsnr_element_rec.created_by IS NULL THEN
1616 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'CREATED_BY' );
1617 x_return_status := FND_API.g_ret_sts_error;
1618 END IF;
1619
1620
1621 IF p_qsnr_element_rec.creation_date = FND_API.G_MISS_DATE OR p_qsnr_element_rec.creation_date IS NULL THEN
1622 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'CREATION_DATE' );
1623 x_return_status := FND_API.g_ret_sts_error;
1624 END IF;
1625
1626
1627 IF p_qsnr_element_rec.last_updated_by = FND_API.G_MISS_NUM OR p_qsnr_element_rec.last_updated_by IS NULL THEN
1628 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'LAST_UPDATED_BY' );
1629 x_return_status := FND_API.g_ret_sts_error;
1630 END IF;
1631
1632
1633 IF p_qsnr_element_rec.last_update_date = FND_API.G_MISS_DATE OR p_qsnr_element_rec.last_update_date IS NULL THEN
1634 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'LAST_UPDATE_DATE' );
1635 x_return_status := FND_API.g_ret_sts_error;
1636 END IF;
1637
1638
1639 ELSE
1640
1641
1642 IF p_qsnr_element_rec.qsnr_element_id = FND_API.G_MISS_NUM THEN
1643 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'QSNR_ELEMENT_ID' );
1644 x_return_status := FND_API.g_ret_sts_error;
1645 END IF;
1646
1647
1648 IF p_qsnr_element_rec.object_version_number = FND_API.G_MISS_NUM THEN
1649 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'OBJECT_VERSION_NUMBER' );
1650 x_return_status := FND_API.g_ret_sts_error;
1651 END IF;
1652
1653 /*
1654 IF p_qsnr_element_rec.arc_used_by_entity_code = FND_API.g_miss_char THEN
1655 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'ARC_USED_BY_ENTITY_CODE' );
1656 x_return_status := FND_API.g_ret_sts_error;
1657 END IF;
1658
1659
1660 IF p_qsnr_element_rec.used_by_entity_id = FND_API.G_MISS_NUM THEN
1661 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'USED_BY_ENTITY_ID' );
1662 x_return_status := FND_API.g_ret_sts_error;
1663 END IF;
1664
1665
1666 IF p_qsnr_element_rec.qsnr_elmt_seq_num = FND_API.G_MISS_NUM THEN
1667 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'QSNR_ELMT_SEQ_NUM' );
1668 x_return_status := FND_API.g_ret_sts_error;
1669 END IF;
1670
1671
1672 IF p_qsnr_element_rec.qsnr_elmt_type = FND_API.g_miss_char THEN
1673 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'QSNR_ELMT_TYPE' );
1674 x_return_status := FND_API.g_ret_sts_error;
1675 END IF;
1676
1677
1678 IF p_qsnr_element_rec.qsnr_elmt_page_num = FND_API.G_MISS_NUM THEN
1679 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'QSNR_ELMT_PAGE_NUM' );
1680 x_return_status := FND_API.g_ret_sts_error;
1681 END IF;
1682
1683
1684 IF p_qsnr_element_rec.is_required_flag = FND_API.g_miss_char THEN
1685 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'IS_REQUIRED_FLAG' );
1686 x_return_status := FND_API.g_ret_sts_error;
1687 END IF;
1688
1689
1690 IF p_qsnr_element_rec.created_by = FND_API.G_MISS_NUM THEN
1691 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'CREATED_BY' );
1692 x_return_status := FND_API.g_ret_sts_error;
1693 END IF;
1694
1695
1696 IF p_qsnr_element_rec.creation_date = FND_API.G_MISS_DATE THEN
1697 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'CREATION_DATE' );
1698 x_return_status := FND_API.g_ret_sts_error;
1699 END IF;
1700
1701
1702 IF p_qsnr_element_rec.last_updated_by = FND_API.G_MISS_NUM THEN
1703 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'LAST_UPDATED_BY' );
1704 x_return_status := FND_API.g_ret_sts_error;
1705 END IF;
1706
1707
1708 IF p_qsnr_element_rec.last_update_date = FND_API.G_MISS_DATE THEN
1709 PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'LAST_UPDATE_DATE' );
1710 x_return_status := FND_API.g_ret_sts_error;
1711 END IF;
1712 */
1713 END IF;
1714
1715 END check_Qsnr_Element_Req_Items;
1716
1717
1718
1719 PROCEDURE check_Qsnr_Element_Fk_Items(
1720 p_qsnr_element_rec IN qsnr_element_rec_type,
1721 x_return_status OUT NOCOPY VARCHAR2
1722 )
1723 IS
1724 BEGIN
1725 x_return_status := FND_API.g_ret_sts_success;
1726
1727 -- Enter custom code here
1728
1729 END check_Qsnr_Element_Fk_Items;
1730
1731
1732
1733 PROCEDURE check_Qs_Elemnt_Lookup_Items(
1734 p_qsnr_element_rec IN qsnr_element_rec_type,
1735 x_return_status OUT NOCOPY VARCHAR2
1736 )
1737 IS
1738 BEGIN
1739 x_return_status := FND_API.g_ret_sts_success;
1740
1741 ----------------------- PV_QUESTIONNAIRE_ENTITY_CODE LOOKUP ------------------------
1742 IF p_qsnr_element_rec.arc_used_by_entity_code <> FND_API.g_miss_char THEN
1743
1744 IF PVX_Utility_PVT.check_lookup_exists(
1745 'PV_LOOKUPS', -- Look up Table Name
1746 'PV_QUESTIONNAIRE_ENTITY_CODE', -- Lookup Type
1747 p_qsnr_element_rec.arc_used_by_entity_code -- Lookup Code
1748 ) = FND_API.g_false
1749 THEN
1750 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1751 THEN
1752 FND_MESSAGE.set_name('PV', 'PV_NOT_A_VALID_ENTITY_CODE');
1753 FND_MSG_PUB.add;
1754 END IF;
1755 x_return_status := FND_API.g_ret_sts_error;
1756 RETURN;
1757
1758 END IF;
1759 END IF;
1760 -- Debug message
1761 IF (PV_DEBUG_HIGH_ON) THEN
1762
1763 PVX_UTILITY_PVT.debug_message('- In Check_Lookup_Items : After program_level_code lookup check. x_return_status = '||x_return_status);
1764 END IF;
1765
1766 END check_Qs_Elemnt_Lookup_Items;
1767
1768
1769
1770 PROCEDURE Check_Qsnr_Element_Items (
1771 P_qsnr_element_rec IN qsnr_element_rec_type,
1772 p_validation_mode IN VARCHAR2,
1773 x_return_status OUT NOCOPY VARCHAR2
1774 )
1775 IS
1776 l_return_status VARCHAR2(1);
1777 BEGIN
1778
1779 l_return_status := FND_API.g_ret_sts_success;
1780 -- Check Items Uniqueness API calls
1781
1782 IF (PV_DEBUG_HIGH_ON) THEN
1783 PVX_UTILITY_PVT.debug_message('check_Qsnr_element_Uk_Items');
1784 END IF;
1785
1786 check_Qsnr_element_Uk_Items(
1787 p_qsnr_element_rec => p_qsnr_element_rec,
1788 p_validation_mode => p_validation_mode,
1789 x_return_status => x_return_status);
1790 IF x_return_status <> FND_API.g_ret_sts_success THEN
1791 return;
1792 END IF;
1793
1794 -- Check Items Required/NOT NULL API calls
1795
1796 IF (PV_DEBUG_HIGH_ON) THEN
1797 PVX_UTILITY_PVT.debug_message('check_qsnr_element_req_items');
1798 END IF;
1799
1800 check_qsnr_element_req_items(
1801 p_qsnr_element_rec => p_qsnr_element_rec,
1802 p_validation_mode => p_validation_mode,
1803 x_return_status => x_return_status);
1804 IF x_return_status <> FND_API.g_ret_sts_success THEN
1805 return;
1806 END IF;
1807 -- Check Items Foreign Keys API calls
1808
1809 IF (PV_DEBUG_HIGH_ON) THEN
1810 PVX_UTILITY_PVT.debug_message('check_qsnr_element_FK_items');
1811 END IF;
1812
1813 check_qsnr_element_FK_items(
1814 p_qsnr_element_rec => p_qsnr_element_rec,
1815 x_return_status => x_return_status);
1816 IF x_return_status <> FND_API.g_ret_sts_success THEN
1817 return;
1818 END IF;
1819 -- Check Items Lookups
1820
1821 IF (PV_DEBUG_HIGH_ON) THEN
1822 PVX_UTILITY_PVT.debug_message('check_qs_elemnt_Lookup_items');
1823 END IF;
1824
1825 check_qs_elemnt_Lookup_items(
1826 p_qsnr_element_rec => p_qsnr_element_rec,
1827 x_return_status => x_return_status);
1828 IF x_return_status <> FND_API.g_ret_sts_success THEN
1829 return;
1830 END IF;
1831 END Check_qsnr_element_Items;
1832
1833
1834
1835
1836
1837 PROCEDURE Complete_Qsnr_Element_Rec (
1838 p_qsnr_element_rec IN qsnr_element_rec_type,
1839 x_complete_rec OUT NOCOPY qsnr_element_rec_type)
1840 IS
1841 l_return_status VARCHAR2(1);
1842
1843 CURSOR c_complete IS
1844 SELECT *
1845 FROM pv_ge_qsnr_elements_b
1846 WHERE qsnr_element_id = p_qsnr_element_rec.qsnr_element_id;
1847 l_qsnr_element_rec c_complete%ROWTYPE;
1848 BEGIN
1849 x_complete_rec := p_qsnr_element_rec;
1850
1851
1852 OPEN c_complete;
1853 FETCH c_complete INTO l_qsnr_element_rec;
1854 CLOSE c_complete;
1855
1856 -- qsnr_element_id
1857 IF p_qsnr_element_rec.qsnr_element_id IS NULL THEN
1858 x_complete_rec.qsnr_element_id := l_qsnr_element_rec.qsnr_element_id;
1859 END IF;
1860
1861 -- object_version_number
1862 IF p_qsnr_element_rec.object_version_number IS NULL THEN
1863 x_complete_rec.object_version_number := l_qsnr_element_rec.object_version_number;
1864 END IF;
1865
1866 -- arc_used_by_entity_code
1867 IF p_qsnr_element_rec.arc_used_by_entity_code IS NULL THEN
1868 x_complete_rec.arc_used_by_entity_code := l_qsnr_element_rec.arc_used_by_entity_code;
1869 END IF;
1870
1871 -- used_by_entity_id
1872 IF p_qsnr_element_rec.used_by_entity_id IS NULL THEN
1873 x_complete_rec.used_by_entity_id := l_qsnr_element_rec.used_by_entity_id;
1874 END IF;
1875
1876 -- qsnr_elmt_seq_num
1877 IF p_qsnr_element_rec.qsnr_elmt_seq_num IS NULL THEN
1878 x_complete_rec.qsnr_elmt_seq_num := l_qsnr_element_rec.qsnr_elmt_seq_num;
1879 END IF;
1880
1881 -- qsnr_elmt_type
1882 IF p_qsnr_element_rec.qsnr_elmt_type IS NULL THEN
1883 x_complete_rec.qsnr_elmt_type := l_qsnr_element_rec.qsnr_elmt_type;
1884 END IF;
1885
1886 -- entity_attr_id
1887 IF p_qsnr_element_rec.entity_attr_id IS NULL THEN
1888 x_complete_rec.entity_attr_id := l_qsnr_element_rec.entity_attr_id;
1889 END IF;
1890
1891 -- qsnr_elmt_page_num
1892 IF p_qsnr_element_rec.qsnr_elmt_page_num IS NULL THEN
1893 x_complete_rec.qsnr_elmt_page_num := l_qsnr_element_rec.qsnr_elmt_page_num;
1894 END IF;
1895
1896 -- is_required_flag
1897 IF p_qsnr_element_rec.is_required_flag IS NULL THEN
1898 x_complete_rec.is_required_flag := l_qsnr_element_rec.is_required_flag;
1899 END IF;
1900
1901 -- created_by
1902 IF p_qsnr_element_rec.created_by IS NULL THEN
1903 x_complete_rec.created_by := l_qsnr_element_rec.created_by;
1904 END IF;
1905
1906 -- creation_date
1907 IF p_qsnr_element_rec.creation_date IS NULL THEN
1908 x_complete_rec.creation_date := l_qsnr_element_rec.creation_date;
1909 END IF;
1910
1911 -- last_updated_by
1912 IF p_qsnr_element_rec.last_updated_by IS NULL THEN
1913 x_complete_rec.last_updated_by := l_qsnr_element_rec.last_updated_by;
1914 END IF;
1915
1916 -- last_update_date
1917 IF p_qsnr_element_rec.last_update_date IS NULL THEN
1918 x_complete_rec.last_update_date := l_qsnr_element_rec.last_update_date;
1919 END IF;
1920
1921 -- last_update_login
1922 IF p_qsnr_element_rec.last_update_login IS NULL THEN
1923 x_complete_rec.last_update_login := l_qsnr_element_rec.last_update_login;
1924 END IF;
1925 -- Note: Developers need to modify the procedure
1926 -- to handle any business specific requirements.
1927 END Complete_Qsnr_Element_Rec;
1928
1929
1930
1931
1932 PROCEDURE Default_Qsnr_Element_Items ( p_qsnr_element_rec IN qsnr_element_rec_type ,
1933 x_qsnr_element_rec OUT NOCOPY qsnr_element_rec_type )
1934 IS
1935 l_qsnr_element_rec qsnr_element_rec_type := p_qsnr_element_rec;
1936 BEGIN
1937 -- Developers should put their code to default the record type
1938 -- e.g. IF p_campaign_rec.status_code IS NULL
1939 -- OR p_campaign_rec.status_code = FND_API.G_MISS_CHAR THEN
1940 -- l_campaign_rec.status_code := 'NEW' ;
1941 -- END IF ;
1942 --
1943 NULL ;
1944 END;
1945
1946
1947
1948
1949 PROCEDURE Validate_Gq_Elements(
1950 p_api_version_number IN NUMBER,
1951 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1952 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1953 p_qsnr_element_rec IN qsnr_element_rec_type,
1954 p_validation_mode IN VARCHAR2,
1955 x_return_status OUT NOCOPY VARCHAR2,
1956 x_msg_count OUT NOCOPY NUMBER,
1957 x_msg_data OUT NOCOPY VARCHAR2
1958 )
1959 IS
1960 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Gq_Elements';
1961 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1962 l_object_version_number NUMBER;
1963 l_qsnr_element_rec qsnr_element_rec_type;
1964 l_qsnr_element_rec_out qsnr_element_rec_type;
1965
1966 BEGIN
1967 -- Standard Start of API savepoint
1968 SAVEPOINT validate_gq_elements_;
1969
1970 -- Standard call to check for call compatibility.
1971 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1972 p_api_version_number,
1973 l_api_name,
1974 G_PKG_NAME)
1975 THEN
1976 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1977 END IF;
1978
1979
1980 -- Initialize message list if p_init_msg_list is set to TRUE.
1981 IF FND_API.to_Boolean( p_init_msg_list )
1982 THEN
1983 FND_MSG_PUB.initialize;
1984 END IF;
1985
1986 IF (PV_DEBUG_HIGH_ON) THEN
1987
1988
1989
1990 PVX_UTILITY_PVT.debug_message('Check_qsnr_element_Items');
1991
1992 END IF;
1993
1994 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1995 Check_qsnr_element_Items(
1996 p_qsnr_element_rec => p_qsnr_element_rec,
1997 p_validation_mode => p_validation_mode,
1998 x_return_status => x_return_status
1999 );
2000
2001 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2002 RAISE FND_API.G_EXC_ERROR;
2003 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2004 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2005 END IF;
2006 END IF;
2007
2008 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
2009 Default_Qsnr_Element_Items (p_qsnr_element_rec => p_qsnr_element_rec ,
2010 x_qsnr_element_rec => l_qsnr_element_rec) ;
2011 END IF ;
2012
2013 IF (PV_DEBUG_HIGH_ON) THEN
2014
2015
2016
2017 PVX_UTILITY_PVT.debug_message('Complete_qsnr_element_Rec');
2018
2019 END IF;
2020
2021 Complete_qsnr_element_Rec(
2022 p_qsnr_element_rec => l_qsnr_element_rec,
2023 x_complete_rec => l_qsnr_element_rec_out
2024 );
2025
2026 l_qsnr_element_rec := l_qsnr_element_rec_out;
2027
2028 IF (PV_DEBUG_HIGH_ON) THEN
2029
2030
2031
2032 PVX_UTILITY_PVT.debug_message('Validate_qsnr_element_Rec');
2033
2034 END IF;
2035
2036 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
2037 Validate_qsnr_element_Rec(
2038 p_api_version_number => 1.0,
2039 p_init_msg_list => FND_API.G_FALSE,
2040 x_return_status => x_return_status,
2041 x_msg_count => x_msg_count,
2042 x_msg_data => x_msg_data,
2043 p_qsnr_element_rec => l_qsnr_element_rec);
2044 IF (PV_DEBUG_HIGH_ON) THEN
2045
2046 PVX_UTILITY_PVT.debug_message('Validate_qsnr_element_Rec end.....');
2047 END IF;
2048
2049 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2050 RAISE FND_API.G_EXC_ERROR;
2051 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2052 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2053 END IF;
2054 END IF;
2055
2056
2057 -- Debug Message
2058 IF (PV_DEBUG_HIGH_ON) THEN
2059
2060 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2061 END IF;
2062
2063
2064
2065 -- Initialize API return status to SUCCESS
2066 x_return_status := FND_API.G_RET_STS_SUCCESS;
2067
2068
2069 -- Debug Message
2070 IF (PV_DEBUG_HIGH_ON) THEN
2071
2072 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2073 END IF;
2074
2075
2076 -- Standard call to get message count and if count is 1, get message info.
2077 FND_MSG_PUB.Count_And_Get
2078 (p_count => x_msg_count,
2079 p_data => x_msg_data
2080 );
2081 EXCEPTION
2082
2083 WHEN PVX_Utility_PVT.resource_locked THEN
2084 x_return_status := FND_API.g_ret_sts_error;
2085 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
2086
2087 WHEN FND_API.G_EXC_ERROR THEN
2088 ROLLBACK TO VALIDATE_Gq_Elements_;
2089 x_return_status := FND_API.G_RET_STS_ERROR;
2090 -- Standard call to get message count and if count=1, get the message
2091 FND_MSG_PUB.Count_And_Get (
2092 p_encoded => FND_API.G_FALSE,
2093 p_count => x_msg_count,
2094 p_data => x_msg_data
2095 );
2096
2097 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2098 ROLLBACK TO VALIDATE_Gq_Elements_;
2099 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2100 -- Standard call to get message count and if count=1, get the message
2101 FND_MSG_PUB.Count_And_Get (
2102 p_encoded => FND_API.G_FALSE,
2103 p_count => x_msg_count,
2104 p_data => x_msg_data
2105 );
2106
2107 WHEN OTHERS THEN
2108 ROLLBACK TO VALIDATE_Gq_Elements_;
2109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2110 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2111 THEN
2112 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2113 END IF;
2114 -- Standard call to get message count and if count=1, get the message
2115 FND_MSG_PUB.Count_And_Get (
2116 p_encoded => FND_API.G_FALSE,
2117 p_count => x_msg_count,
2118 p_data => x_msg_data
2119 );
2120 End Validate_Gq_Elements;
2121
2122
2123 PROCEDURE Validate_Qsnr_Element_Rec (
2124 p_api_version_number IN NUMBER,
2125 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2126 x_return_status OUT NOCOPY VARCHAR2,
2127 x_msg_count OUT NOCOPY NUMBER,
2128 x_msg_data OUT NOCOPY VARCHAR2,
2129 p_qsnr_element_rec IN qsnr_element_rec_type
2130 )
2131 IS
2132 BEGIN
2133 -- Initialize message list if p_init_msg_list is set to TRUE.
2134 IF FND_API.to_Boolean( p_init_msg_list )
2135 THEN
2136 FND_MSG_PUB.initialize;
2137 END IF;
2138
2139
2140
2141 -- Initialize API return status to SUCCESS
2142 x_return_status := FND_API.G_RET_STS_SUCCESS;
2143
2144 -- Hint: Validate data
2145 -- If data not valid
2146 -- THEN
2147 -- x_return_status := FND_API.G_RET_STS_ERROR;
2148
2149 -- Debug Message
2150 IF (PV_DEBUG_HIGH_ON) THEN
2151
2152 PVX_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
2153 END IF;
2154 -- Standard call to get message count and if count is 1, get message info.
2155 FND_MSG_PUB.Count_And_Get
2156 (p_count => x_msg_count,
2157 p_data => x_msg_data
2158 );
2159 END Validate_qsnr_element_Rec;
2160
2161 -- ==============================================================================
2162 -- Start of Comments
2163 -- ==============================================================================
2164 -- API Name
2165 -- Move_Qsnr_Element
2166 -- Type
2167 -- Private
2168 -- Pre-Req
2169 --
2170 -- Parameters
2171 --
2172 -- IN
2173 -- p_api_version_number IN NUMBER Required
2174 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
2175 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2176 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2177 -- p_qsnr_element_id IN NUMBER
2178 -- p_object_version_number IN NUMBER
2179 --
2180 -- OUT
2181 -- x_return_status OUT VARCHAR2
2182 -- x_msg_count OUT NUMBER
2183 -- x_msg_data OUT VARCHAR2
2184 -- Version : Current version 1.0
2185 --
2186 -- History
2187 --
2188 -- NOTE
2189 --
2190 -- End of Comments
2191 -- ==============================================================================
2192
2193 PROCEDURE Move_Qsnr_Element (
2194 p_api_version_number IN NUMBER
2195 ,p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE
2196 ,p_commit IN VARCHAR2 := Fnd_Api.G_FALSE
2197 ,p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL
2198
2199 ,x_return_status OUT NOCOPY VARCHAR2
2200 ,x_msg_count OUT NOCOPY NUMBER
2201 ,x_msg_data OUT NOCOPY VARCHAR2
2202
2203 ,p_qsnr_element_rec IN qsnr_element_rec_type
2204 ,p_movement IN VARCHAR2
2205 )
2206
2207 IS
2208
2209 CURSOR c_get_used_by_entity (cv_qsnr_element_id NUMBER) IS
2210 select arc_used_by_entity_code, used_by_entity_id, qsnr_elmt_seq_num
2211 from pv_ge_qsnr_elements_vl
2212 where qsnr_element_id = cv_qsnr_element_id;
2213
2214 CURSOR c_get_qsnr (cv_qsnr_elmt_seq_num NUMBER,
2215 cv_arc_used_by_entity_code VARCHAR2,
2216 cv_used_by_entity_id NUMBER) IS
2217 SELECT *
2218 FROM PV_GE_QSNR_ELEMENTS_VL
2219 WHERE qsnr_elmt_seq_num = cv_qsnr_elmt_seq_num
2220 AND arc_used_by_entity_code = cv_arc_used_by_entity_code
2221 AND used_by_entity_id = cv_used_by_entity_id;
2222
2223 l_api_name CONSTANT VARCHAR2(30) := 'Move_Up';
2224 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2225 l_api_version_number CONSTANT NUMBER := 1.0;
2226 l_current_qsnr_rec c_get_qsnr%ROWTYPE;
2227 l_up_qsnr_rec c_get_qsnr%ROWTYPE;
2228 l_temp NUMBER;
2229 --l_current_qsnr_element_rec qsnr_element_rec_type := g_miss_qsnr_element_rec;
2230 l_current_qsnr_element_rec qsnr_element_rec_type := p_qsnr_element_rec;
2231 l_up_qsnr_element_rec qsnr_element_rec_type := g_miss_qsnr_element_rec;
2232 l_get_used_by_entity c_get_used_by_entity%ROWTYPE;
2233 l_object_version_number NUMBER;
2234
2235 BEGIN
2236
2237 ---- Initialize----------------
2238
2239 -- Standard Start of API savepoint
2240 SAVEPOINT Move_Qsnr_Element;
2241
2242 -- Debug Message
2243 IF (PV_DEBUG_HIGH_ON) THEN
2244
2245 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - start');
2246 END IF;
2247
2248 -- Standard call to check for call compatibility.
2249 IF NOT FND_API.Compatible_API_Call (l_api_version_number
2250 ,p_api_version_number
2251 ,l_api_name
2252 ,G_PKG_NAME
2253 )
2254 THEN
2255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2256 END IF;
2257
2258 -- Initialize message list if p_init_msg_list is set to TRUE.
2259 IF FND_API.to_Boolean( p_init_msg_list )
2260 THEN
2261 FND_MSG_PUB.initialize;
2262 END IF;
2263
2264 -- Initialize API return status to SUCCESS
2265 x_return_status := FND_API.G_RET_STS_SUCCESS;
2266
2267 -- Debug Message
2268 IF (PV_DEBUG_HIGH_ON) THEN
2269
2270 PVX_UTILITY_PVT.debug_message( 'Private API: Start to move');
2271 END IF;
2272
2273 OPEN c_get_used_by_entity(p_qsnr_element_rec.qsnr_element_id);
2274 FETCH c_get_used_by_entity INTO l_get_used_by_entity;
2275 CLOSE c_get_used_by_entity;
2276
2277 IF (PV_DEBUG_HIGH_ON) THEN
2278
2279
2280
2281 PVX_UTILITY_PVT.debug_message( 'qsnr_elmt_seq_num = ' || l_get_used_by_entity.qsnr_elmt_seq_num);
2282
2283 END IF;
2284 IF (PV_DEBUG_HIGH_ON) THEN
2285
2286 PVX_UTILITY_PVT.debug_message( 'arc_used_by_entity_code = ' || l_get_used_by_entity.arc_used_by_entity_code);
2287 END IF;
2288 IF (PV_DEBUG_HIGH_ON) THEN
2289
2290 PVX_UTILITY_PVT.debug_message( 'used_by_entity_id = ' || l_get_used_by_entity.used_by_entity_id);
2291 END IF;
2292
2293 OPEN c_get_qsnr(l_get_used_by_entity.qsnr_elmt_seq_num,
2294 l_get_used_by_entity.arc_used_by_entity_code,
2295 l_get_used_by_entity.used_by_entity_id);
2296 FETCH c_get_qsnr INTO l_current_qsnr_rec;
2297 CLOSE c_get_qsnr;
2298
2299
2300 -- move up: get the above record
2301 -- move down: get the next record
2302 IF p_movement = 'U' THEN
2303 OPEN c_get_qsnr(l_get_used_by_entity.qsnr_elmt_seq_num - 1,
2304 l_get_used_by_entity.arc_used_by_entity_code,
2305 l_get_used_by_entity.used_by_entity_id);
2306 ELSIF p_movement = 'D' THEN
2307 OPEN c_get_qsnr(l_get_used_by_entity.qsnr_elmt_seq_num + 1,
2308 l_get_used_by_entity.arc_used_by_entity_code,
2309 l_get_used_by_entity.used_by_entity_id);
2310 END IF;
2311 FETCH c_get_qsnr INTO l_up_qsnr_rec;
2312 CLOSE c_get_qsnr;
2313
2314
2315 IF (PV_DEBUG_HIGH_ON) THEN
2316
2317
2318
2319
2320
2321 PVX_UTILITY_PVT.debug_message('qsnr_elmt_type = ' || l_current_qsnr_rec.qsnr_elmt_type);
2322
2323
2324 END IF;
2325 IF (PV_DEBUG_HIGH_ON) THEN
2326
2327 PVX_UTILITY_PVT.debug_message('qsnr_elmt_type = ' || l_up_qsnr_rec.qsnr_elmt_type);
2328 END IF;
2329
2330 IF l_up_qsnr_rec.qsnr_elmt_type <> 'PAGEBREAK'
2331 AND l_current_qsnr_rec.qsnr_elmt_type <> 'PAGEBREAK' THEN
2332 IF (PV_DEBUG_HIGH_ON) THEN
2333
2334 PVX_UTILITY_PVT.debug_message('None is Page Break');
2335 END IF;
2336
2337 l_up_qsnr_element_rec.qsnr_element_id := l_up_qsnr_rec.qsnr_element_id;
2338 l_up_qsnr_element_rec.object_version_number := l_up_qsnr_rec.object_version_number;
2339 l_current_qsnr_element_rec.qsnr_element_id := l_current_qsnr_rec.qsnr_element_id;
2340 l_current_qsnr_element_rec.object_version_number := l_current_qsnr_rec.object_version_number;
2341
2342 -- Exchange qsnr_elmt_seq
2343 l_up_qsnr_element_rec.qsnr_elmt_seq_num := l_current_qsnr_rec.qsnr_elmt_seq_num;
2344 l_current_qsnr_element_rec.qsnr_elmt_seq_num := l_up_qsnr_rec.qsnr_elmt_seq_num;
2345
2346 Update_Gq_Elements( p_api_version_number => 1.0,
2347 p_init_msg_list => FND_API.G_FALSE,
2348 p_validation_level => p_validation_level,
2349 p_commit => p_commit,
2350 x_return_status => x_return_status,
2351 x_msg_count => x_msg_count,
2352 x_msg_data => x_msg_data,
2353 p_qsnr_element_rec => l_current_qsnr_element_rec,
2354 x_object_version_number => l_object_version_number
2355 );
2356
2357 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2358 RAISE FND_API.G_EXC_ERROR;
2359 END IF;
2360
2361 IF x_return_status = FND_API.g_ret_sts_error THEN
2362 RAISE FND_API.g_exc_error;
2363 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2364 RAISE FND_API.g_exc_unexpected_error;
2365 END IF;
2366
2367 Update_Gq_Elements( p_api_version_number => 1.0,
2368 p_init_msg_list => FND_API.G_FALSE,
2369 p_validation_level => p_validation_level,
2370 p_commit => p_commit,
2371 x_return_status => x_return_status,
2372 x_msg_count => x_msg_count,
2373 x_msg_data => x_msg_data,
2374 p_qsnr_element_rec => l_up_qsnr_element_rec,
2375 x_object_version_number => l_object_version_number
2376 );
2377
2378 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2379 RAISE FND_API.G_EXC_ERROR;
2380 END IF;
2381
2382 IF x_return_status = FND_API.g_ret_sts_error THEN
2383 RAISE FND_API.g_exc_error;
2384 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2385 RAISE FND_API.g_exc_unexpected_error;
2386 END IF;
2387
2388 ELSIF l_current_qsnr_rec.qsnr_elmt_type = 'PAGEBREAK'
2389 AND l_up_qsnr_rec.qsnr_elmt_type <> 'PAGEBREAK' THEN
2390 IF (PV_DEBUG_HIGH_ON) THEN
2391
2392 PVX_UTILITY_PVT.debug_message('Current is Page Break');
2393 END IF;
2394
2395 l_up_qsnr_element_rec.qsnr_element_id := l_up_qsnr_rec.qsnr_element_id;
2396 l_up_qsnr_element_rec.object_version_number := l_up_qsnr_rec.object_version_number;
2397 l_current_qsnr_element_rec.qsnr_element_id := l_current_qsnr_rec.qsnr_element_id;
2398 l_current_qsnr_element_rec.object_version_number := l_current_qsnr_rec.object_version_number;
2399
2400 -- Exchange qsnr_elmt_seq
2401 l_up_qsnr_element_rec.qsnr_elmt_seq_num := l_current_qsnr_rec.qsnr_elmt_seq_num;
2402 l_current_qsnr_element_rec.qsnr_elmt_seq_num := l_up_qsnr_rec.qsnr_elmt_seq_num;
2403
2404 -- Modify the qsnr_elmt_page_num field
2405 IF p_movement = 'U' THEN
2406 l_up_qsnr_element_rec.qsnr_elmt_page_num := l_up_qsnr_rec.qsnr_elmt_page_num + 1;
2407 ELSIF p_movement = 'D' THEN
2408 l_up_qsnr_element_rec.qsnr_elmt_page_num := l_up_qsnr_rec.qsnr_elmt_page_num - 1;
2409 END IF;
2410 l_current_qsnr_element_rec.qsnr_elmt_page_num := l_current_qsnr_rec.qsnr_elmt_page_num;
2411
2412 Update_Gq_Elements( p_api_version_number => 1.0,
2413 p_init_msg_list => FND_API.G_FALSE,
2414 p_validation_level => p_validation_level,
2415 p_commit => p_commit,
2416 x_return_status => x_return_status,
2417 x_msg_count => x_msg_count,
2418 x_msg_data => x_msg_data,
2419 p_qsnr_element_rec => l_current_qsnr_element_rec,
2420 x_object_version_number => l_object_version_number
2421 );
2422
2423 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2424 RAISE FND_API.G_EXC_ERROR;
2425 END IF;
2426
2427 IF x_return_status = FND_API.g_ret_sts_error THEN
2428 RAISE FND_API.g_exc_error;
2429 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2430 RAISE FND_API.g_exc_unexpected_error;
2431 END IF;
2432
2433 Update_Gq_Elements( p_api_version_number => 1.0,
2434 p_init_msg_list => FND_API.G_FALSE,
2435 p_validation_level => p_validation_level,
2436 p_commit => p_commit,
2437 x_return_status => x_return_status,
2438 x_msg_count => x_msg_count,
2439 x_msg_data => x_msg_data,
2440 p_qsnr_element_rec => l_up_qsnr_element_rec,
2441 x_object_version_number => l_object_version_number
2442 );
2443
2444 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2445 RAISE FND_API.G_EXC_ERROR;
2446 END IF;
2447
2448 IF x_return_status = FND_API.g_ret_sts_error THEN
2449 RAISE FND_API.g_exc_error;
2450 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2451 RAISE FND_API.g_exc_unexpected_error;
2452 END IF;
2453
2454 ELSIF l_up_qsnr_rec.qsnr_elmt_type = 'PAGEBREAK'
2455 AND l_current_qsnr_rec.qsnr_elmt_type <> 'PAGEBREAK' THEN
2456 IF (PV_DEBUG_HIGH_ON) THEN
2457
2458 PVX_UTILITY_PVT.debug_message('Up is Page Break');
2459 END IF;
2460
2461 l_up_qsnr_element_rec.qsnr_element_id := l_up_qsnr_rec.qsnr_element_id;
2462 l_up_qsnr_element_rec.object_version_number := l_up_qsnr_rec.object_version_number;
2463 l_current_qsnr_element_rec.qsnr_element_id := l_current_qsnr_rec.qsnr_element_id;
2464 l_current_qsnr_element_rec.object_version_number := l_current_qsnr_rec.object_version_number;
2465
2466 -- Exchange qsnr_elmt_seq_num
2467 l_up_qsnr_element_rec.qsnr_elmt_seq_num := l_current_qsnr_rec.qsnr_elmt_seq_num;
2468 l_current_qsnr_element_rec.qsnr_elmt_seq_num := l_up_qsnr_rec.qsnr_elmt_seq_num;
2469
2470 -- Modify the qsnr_elmt_page_num field
2471 l_up_qsnr_element_rec.qsnr_elmt_page_num := l_up_qsnr_rec.qsnr_elmt_page_num;
2472 IF p_movement = 'U' THEN
2473 l_current_qsnr_element_rec.qsnr_elmt_page_num := l_current_qsnr_rec.qsnr_elmt_page_num - 1;
2474 ELSIF p_movement = 'D' THEN
2475 l_current_qsnr_element_rec.qsnr_elmt_page_num := l_current_qsnr_rec.qsnr_elmt_page_num + 1;
2476 END IF;
2477
2478 IF (PV_DEBUG_HIGH_ON) THEN
2479
2480
2481
2482 PVX_UTILITY_PVT.debug_message('l_up_qsnr_element_rec.qsnr_elmt_seq_num' || l_up_qsnr_element_rec.qsnr_elmt_seq_num);
2483
2484 END IF;
2485 IF (PV_DEBUG_HIGH_ON) THEN
2486
2487 PVX_UTILITY_PVT.debug_message('l_current_qsnr_rec.qsnr_elmt_seq_num' || l_current_qsnr_rec.qsnr_elmt_seq_num);
2488 END IF;
2489
2490
2491 Update_Gq_Elements( p_api_version_number => 1.0,
2492 p_init_msg_list => FND_API.G_FALSE,
2493 p_validation_level => p_validation_level,
2494 p_commit => p_commit,
2495 x_return_status => x_return_status,
2496 x_msg_count => x_msg_count,
2497 x_msg_data => x_msg_data,
2498 p_qsnr_element_rec => l_current_qsnr_element_rec,
2499 x_object_version_number => l_object_version_number
2500 );
2501
2502 IF x_return_status = FND_API.g_ret_sts_error THEN
2503 RAISE FND_API.g_exc_error;
2504 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2505 RAISE FND_API.g_exc_unexpected_error;
2506 END IF;
2507
2508 Update_Gq_Elements( p_api_version_number => 1.0,
2509 p_init_msg_list => FND_API.G_FALSE,
2510 p_validation_level => p_validation_level,
2511 p_commit => p_commit,
2512 x_return_status => x_return_status,
2513 x_msg_count => x_msg_count,
2514 x_msg_data => x_msg_data,
2515 p_qsnr_element_rec => l_up_qsnr_element_rec,
2516 x_object_version_number => l_object_version_number
2517 );
2518
2519 IF x_return_status = FND_API.g_ret_sts_error THEN
2520 RAISE FND_API.g_exc_error;
2521 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2522 RAISE FND_API.g_exc_unexpected_error;
2523 END IF;
2524 END IF;
2525
2526
2527 -- Debug Message
2528 IF (PV_DEBUG_HIGH_ON) THEN
2529
2530 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
2531 END IF;
2532
2533 -- Standard check for p_commit
2534 IF FND_API.to_Boolean( p_commit ) THEN
2535 COMMIT WORK;
2536 END IF;
2537
2538 FND_MSG_PUB.Count_And_Get
2539 (p_count => x_msg_count,
2540 p_data => x_msg_data
2541 );
2542
2543
2544 EXCEPTION
2545 WHEN FND_API.G_EXC_ERROR THEN
2546 ROLLBACK TO Move_Qsnr_Element;
2547 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2548 -- Standard call to get message count and if count=1, get the message
2549 Fnd_Msg_Pub.Count_And_Get (
2550 p_encoded => Fnd_Api.G_FALSE
2551 ,p_count => x_msg_count
2552 ,p_data => x_msg_data
2553 );
2554
2555 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2556 ROLLBACK TO Move_Qsnr_Element;
2557 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2558 -- Standard call to get message count and if count=1, get the message
2559 Fnd_Msg_Pub.Count_And_Get (
2560 p_encoded => Fnd_Api.G_FALSE
2561 ,p_count => x_msg_count
2562 ,p_data => x_msg_data
2563 );
2564
2565 WHEN OTHERS THEN
2566 ROLLBACK TO Move_Qsnr_Element;
2567 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2568 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
2569 THEN
2570 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2571 END IF;
2572 -- Standard call to get message count and if count=1, get the message
2573 Fnd_Msg_Pub.Count_And_Get (
2574 p_encoded => Fnd_Api.G_FALSE
2575 ,p_count => x_msg_count
2576 ,p_data => x_msg_data
2577 );
2578
2579 END Move_Qsnr_Element;
2580
2581 END PV_Gq_Elements_PVT;