[Home] [Help]
PACKAGE BODY: APPS.CSI_PRICING_ATTRIBS_PUB
Source
1 PACKAGE BODY csi_pricing_attribs_pub AS
2 /* $Header: csippab.pls 120.1 2005/08/16 10:48:54 sguthiva noship $ */
3
4 g_pkg_name VARCHAR2(30) := 'csi_pricing_attribs_pub';
5
6 /*------------------------------------------------------*/
7 /* procedure name: get_pricing_attribs */
8 /* description : Gets the pricing attributes of an */
9 /* item instance */
10 /*------------------------------------------------------*/
11
12
13 PROCEDURE get_pricing_attribs
14 ( p_api_version IN NUMBER
15 ,p_commit IN VARCHAR2
16 ,p_init_msg_list IN VARCHAR2
17 ,p_validation_level IN NUMBER
18 ,p_pricing_attribs_query_rec IN csi_datastructures_pub.pricing_attribs_query_rec
19 ,p_time_stamp IN DATE
20 ,x_pricing_attribs_tbl OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl
21 ,x_return_status OUT NOCOPY VARCHAR2
22 ,x_msg_count OUT NOCOPY NUMBER
23 ,x_msg_data OUT NOCOPY VARCHAR2
24 )
25
26 IS
27 l_api_name CONSTANT VARCHAR2(30) := 'get_pricing_attribs';
28 l_api_version CONSTANT NUMBER := 1.0;
29 l_debug_level NUMBER;
30 l_pri_rec csi_datastructures_pub.pricing_attribs_rec;
31 l_rows_processed NUMBER;
32 l_where_clause VARCHAR2(2000) := '';
33 l_select_stmt VARCHAR2(20000) := 'SELECT * FROM csi_i_pricing_attribs ';
34 l_cur_get_pri NUMBER;
35 l_count NUMBER := 0;
36 l_trace_enable_flag VARCHAR2(1) :='N';
37
38 BEGIN
39
40 -- Standard Start of API savepoint
41 -- SAVEPOINT get_pricing_attribs;
42
43 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
44
45 csi_utility_grp.check_ib_active;
46
47 -- Standard call to check for call compatibility.
48 IF NOT FND_API.Compatible_API_Call (l_api_version,
49 p_api_version,
50 l_api_name ,
51 g_pkg_name)
52 THEN
53 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
54 END IF;
55
56
57 -- Initialize message list if p_init_msg_list is set to TRUE.
58 IF FND_API.to_Boolean( p_init_msg_list ) THEN
59 FND_MSG_PUB.initialize;
60 END IF;
61
62
63 -- Initialize API return status to success
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65
66
67 -- Check the profile option debug_level for debug message reporting
68 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
69
70 -- If debug_level = 1 then dump the procedure name
71 IF (l_debug_level > 0) THEN
72 csi_gen_utility_pvt.put_line( 'get_pricing_attribs');
73 END IF;
74
75
76 -- If the debug level = 2 then dump all the parameters values.
77 IF (l_debug_level > 1) THEN
78 csi_gen_utility_pvt.put_line( p_api_version ||'-'
79 || p_commit ||'-'
80 || p_init_msg_list ||'-'
81 || p_validation_level ||'-'
82 || p_time_stamp );
83
84 -- Dump pricing_attribs_rec
85 csi_gen_utility_pvt.dump_pricing_attribs_query_rec(p_pricing_attribs_query_rec);
86 END IF;
87
88 /***** srramakr commented for bug # 3304439
89 -- Check for the profile option and enable trace
90 l_trace_enable_flag:=CSI_GEN_UTILITY_PVT.enable_trace(l_trace_flag => l_trace_enable_flag);
91 -- End enable trace
92 *****/
93
94 -- Start API body
95 -- check if atleast one query parameters are passed
96 IF (p_pricing_attribs_query_rec.pricing_attribute_id = FND_API.G_MISS_NUM)
97 AND (p_pricing_attribs_query_rec.instance_id = FND_API.G_MISS_NUM) THEN
98 FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_PARAMETERS');
99 FND_MSG_PUB.ADD;
100 RAISE FND_API.G_EXC_ERROR;
101 END IF;
102
103
104 -- Generate the where clause
105 csi_pricing_attribs_pvt.Gen_pri_Where_Clause
106 ( p_pri_query_rec => p_pricing_attribs_query_rec,
107 x_where_clause => l_where_clause );
108
109 -- Build the select statement
110 l_select_stmt := l_select_stmt || ' where '||l_where_clause;
111
112 -- Open the cursor
113 l_cur_get_pri := dbms_sql.open_cursor;
114
115 --Parse the select statement
116 dbms_sql.parse(l_cur_get_pri, l_select_stmt , dbms_sql.native);
117
118 -- Bind the variables
119 csi_pricing_attribs_pvt.Bind_pri_variable
120 (p_pricing_attribs_query_rec,
121 l_cur_get_pri );
122
123 -- Define output variables
124 csi_pricing_attribs_pvt.Define_pri_Columns(l_cur_get_pri);
125
126 -- execute the select statement
127 l_rows_processed := dbms_sql.execute(l_cur_get_pri);
128
129
130 LOOP
131 EXIT WHEN DBMS_SQL.FETCH_ROWS(l_cur_get_pri) = 0;
132 csi_pricing_attribs_pvt.Get_pri_Column_Values(l_cur_get_pri, l_pri_rec);
133 l_count := l_count + 1;
134 x_pricing_attribs_tbl(l_count) := l_pri_rec;
135 END LOOP;
136
137 -- Close the cursor
138 DBMS_SQL.CLOSE_CURSOR(l_cur_get_pri);
139
140 IF (p_time_stamp IS NOT NULL) AND (p_time_stamp <> FND_API.G_MISS_DATE) THEN
141 IF p_time_stamp <= sysdate THEN
142 csi_pricing_attribs_pvt.Construct_pri_From_Hist(x_pricing_attribs_tbl, p_time_stamp);
143 ELSE
144 FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_PARAMETERS');
145 FND_MESSAGE.SET_TOKEN('TIME_STAMP',p_time_stamp);
146 FND_MSG_PUB.ADD;
147 RAISE FND_API.G_EXC_ERROR;
148 END IF;
149 END IF;
150
151 -- End of API body
152
153 -- Standard check of p_commit.
154 /*
155 IF FND_API.To_Boolean( p_commit ) THEN
156 COMMIT WORK;
157 END IF;
158 */
159
160 /***** srramakr commented for bug # 3304439
161 -- Check for the profile option and disable the trace
162 IF (l_trace_enable_flag = 'Y') THEN
163 dbms_session.set_sql_trace(false);
164 END IF;
165 -- End disable trace
166 ****/
167
168 -- Standard call to get message count and if count is get message info.
169 FND_MSG_PUB.Count_And_Get
170 (p_count => x_msg_count ,
171 p_data => x_msg_data
172 );
173
174
175 EXCEPTION
176
177 WHEN FND_API.G_EXC_ERROR THEN
178 -- ROLLBACK TO get_pricing_attribs;
179 x_return_status := FND_API.G_RET_STS_ERROR ;
180 FND_MSG_PUB.Count_And_Get
181 ( p_count => x_msg_count,
182 p_data => x_msg_data
183 );
184
185 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
186 -- ROLLBACK TO get_pricing_attribs;
187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
188 FND_MSG_PUB.Count_And_Get
189 ( p_count => x_msg_count,
190 p_data => x_msg_data
191 );
192
193 WHEN OTHERS THEN
194 IF DBMS_SQL.IS_OPEN(l_cur_get_pri) THEN
195 DBMS_SQL.CLOSE_CURSOR(l_cur_get_pri);
196 END IF;
197
198 -- ROLLBACK TO get_pricing_attribs;
199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
200
201 IF FND_MSG_PUB.Check_Msg_Level
202 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
203 THEN
204 FND_MSG_PUB.Add_Exc_Msg
205 ( g_pkg_name ,
206 l_api_name
207 );
208 END IF;
209
210 FND_MSG_PUB.Count_And_Get
211 ( p_count => x_msg_count,
212 p_data => x_msg_data
213 );
214
215 END get_pricing_attribs;
216
217
218 /*------------------------------------------------------*/
219 /* procedure name: create_pricing_attribs */
220 /* description : Associates pricing attributes to an */
221 /* item instance */
222 /*------------------------------------------------------*/
223
224
225 PROCEDURE create_pricing_attribs
226 ( p_api_version IN NUMBER
227 ,p_commit IN VARCHAR2
228 ,p_init_msg_list IN VARCHAR2
229 ,p_validation_level IN NUMBER
230 ,p_pricing_attribs_tbl IN OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl
231 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
232 ,x_return_status OUT NOCOPY VARCHAR2
233 ,x_msg_count OUT NOCOPY NUMBER
234 ,x_msg_data OUT NOCOPY VARCHAR2
235 )
236
237 IS
238 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PRICING_ATTRIBS';
239 l_api_version CONSTANT NUMBER := 1.0;
240 l_debug_level NUMBER;
241 l_msg_index NUMBER;
242 l_msg_count NUMBER;
243 l_trace_enable_flag VARCHAR2(1) :='N';
244
245 BEGIN
246
247 -- Standard Start of API savepoint
248 SAVEPOINT create_pricing_attribs;
249
250 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
251
252 csi_utility_grp.check_ib_active;
253
254
255 -- Standard call to check for call compatibility.
256 IF NOT FND_API.Compatible_API_Call (l_api_version,
257 p_api_version,
258 l_api_name ,
259 g_pkg_name)
260 THEN
261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
262 END IF;
263
264
265 -- Initialize message list if p_init_msg_list is set to TRUE.
266 IF FND_API.to_Boolean( p_init_msg_list ) THEN
267 FND_MSG_PUB.initialize;
268 END IF;
269
270
271 -- Initialize API return status to success
272 x_return_status := FND_API.G_RET_STS_SUCCESS;
273
274
275 -- Check the profile option debug_level for debug message reporting
276
277 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
278
279 -- If debug_level = 1 then dump the procedure name
280 IF (l_debug_level > 0) THEN
281 csi_gen_utility_pvt.put_line( 'create_pricing_attribs');
282 END IF;
283
284
285 -- If the debug level = 2 then dump all the parameters values.
286 IF (l_debug_level > 1) THEN
287 csi_gen_utility_pvt.put_line( p_api_version ||'-'
288 || p_commit ||'-'
289 || p_init_msg_list ||'-'
290 || p_validation_level );
291 -- Dump pricing_attribs_tbl
292 csi_gen_utility_pvt.dump_pricing_attribs_tbl(p_pricing_attribs_tbl);
293 -- Dump txn_rec
294 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
295
296 END IF;
297
298 /***** srramakr commented for bug # 3304439
299 -- Check for the profile option and enable trace
300 l_trace_enable_flag:=CSI_GEN_UTILITY_PVT.enable_trace(l_trace_flag => l_trace_enable_flag);
301 -- End enable trace
302 ****/
303
304 -- Start API body
305
306 IF p_pricing_attribs_tbl.COUNT > 0 THEN
307 FOR tab_row IN p_pricing_attribs_tbl.FIRST .. p_pricing_attribs_tbl.LAST
308 LOOP
309 IF p_pricing_attribs_tbl.EXISTS(tab_row) THEN
310 csi_pricing_attribs_pvt.create_pricing_attribs
311 ( p_api_version => p_api_version
312 ,p_commit => p_commit
313 ,p_init_msg_list => p_init_msg_list
314 ,p_validation_level => p_validation_level
315 ,p_pricing_attribs_rec => p_pricing_attribs_tbl(tab_row)
316 ,p_txn_rec => p_txn_rec
317 ,x_return_status => x_return_status
318 ,x_msg_count => x_msg_count
319 ,x_msg_data => x_msg_data
320 );
321
322 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
323 l_msg_index := 1;
324 l_msg_count := x_msg_count;
325 WHILE l_msg_count > 0 LOOP
326 x_msg_data := FND_MSG_PUB.GET
327 ( l_msg_index,
328 FND_API.G_FALSE );
329
330 csi_gen_utility_pvt.put_line( ' Failed Pub:create_pricing_attribs..');
331 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
332 l_msg_index := l_msg_index + 1;
333 l_msg_count := l_msg_count - 1;
334 END LOOP;
335 RAISE FND_API.G_EXC_ERROR;
336 END IF;
337 END IF;
338 END LOOP;
339 END IF;
340
341 -- End of API body
342
343
344 -- Standard check of p_commit.
345 IF FND_API.To_Boolean( p_commit ) THEN
346 COMMIT WORK;
347 END IF;
348
349 /***** srramakr commented for bug # 3304439
350 -- Check for the profile option and disable the trace
351 IF (l_trace_enable_flag = 'Y') THEN
352 dbms_session.set_sql_trace(false);
353 END IF;
354 -- End disable trace
355 ****/
356
357 -- Standard call to get message count and if count is get message info.
358 FND_MSG_PUB.Count_And_Get
359 (p_count => x_msg_count ,
360 p_data => x_msg_data
361 );
362
363
364 EXCEPTION
365
366 WHEN FND_API.G_EXC_ERROR THEN
367 ROLLBACK TO create_pricing_attribs;
368 x_return_status := FND_API.G_RET_STS_ERROR ;
369 FND_MSG_PUB.Count_And_Get
370 ( p_count => x_msg_count,
371 p_data => x_msg_data
372 );
373
374 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
375 ROLLBACK TO create_pricing_attribs;
376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
377 FND_MSG_PUB.Count_And_Get
378 ( p_count => x_msg_count,
379 p_data => x_msg_data
380 );
381
382 WHEN OTHERS THEN
383 ROLLBACK TO create_pricing_attribs;
384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
385
386 IF FND_MSG_PUB.Check_Msg_Level
387 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
388 THEN
389 FND_MSG_PUB.Add_Exc_Msg
390 ( g_pkg_name,
391 l_api_name
392 );
393 END IF;
394
395 FND_MSG_PUB.Count_And_Get
396 ( p_count => x_msg_count,
397 p_data => x_msg_data
398 );
399
400 END create_pricing_attribs;
401
402
403
404 /*------------------------------------------------------*/
405 /* procedure name: update_pricing_attribs */
406 /* description : Updates the existing pricing */
407 /* attributes for an item instance */
408 /* */
409 /*------------------------------------------------------*/
410
411
412 PROCEDURE update_pricing_attribs
413 ( p_api_version IN NUMBER
414 ,p_commit IN VARCHAR2
415 ,p_init_msg_list IN VARCHAR2
416 ,p_validation_level IN NUMBER
417 ,p_pricing_attribs_tbl IN csi_datastructures_pub.pricing_attribs_tbl
418 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
419 ,x_return_status OUT NOCOPY VARCHAR2
420 ,x_msg_count OUT NOCOPY NUMBER
421 ,x_msg_data OUT NOCOPY VARCHAR2
422 )
423
424 IS
425 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PRICING_ATTRIBS';
426 l_api_version CONSTANT NUMBER := 1.0;
427 l_debug_level NUMBER;
428 l_msg_index NUMBER;
429 l_msg_count NUMBER;
430 l_trace_enable_flag VARCHAR2(1) :='N';
431
432 BEGIN
433
434 -- Standard Start of API savepoint
435 SAVEPOINT update_pricing_attribs;
436
437 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
438
439 csi_utility_grp.check_ib_active;
440
441
442 -- Standard call to check for call compatibility.
443 IF NOT FND_API.Compatible_API_Call (l_api_version,
444 p_api_version,
445 l_api_name ,
446 g_pkg_name)
447 THEN
448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
449 END IF;
450
451
452 -- Initialize message list if p_init_msg_list is set to TRUE.
453 IF FND_API.to_Boolean( p_init_msg_list ) THEN
454 FND_MSG_PUB.initialize;
455 END IF;
456
457
458 -- Initialize API return status to success
459 x_return_status := FND_API.G_RET_STS_SUCCESS;
460
461
462 -- Check the profile option debug_level for debug message reporting
463 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
464
465
466 -- If debug_level = 1 then dump the procedure name
467 IF (l_debug_level > 0) THEN
468 csi_gen_utility_pvt.put_line( 'update_pricing_attribs');
469 END IF;
470
471
472 -- If the debug level = 2 then dump all the parameters values.
473 IF (l_debug_level > 1) THEN
474 csi_gen_utility_pvt.put_line( p_api_version ||'-'
475 || p_commit ||'-'
476 || p_init_msg_list ||'-'
477 || p_validation_level);
478 -- Dump pricing_attribs_tbl
479 csi_gen_utility_pvt.dump_pricing_attribs_tbl(p_pricing_attribs_tbl);
480 -- Dump txn_rec
481 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
482 END IF;
483
484 /***** srramakr commented for bug # 3304439
485 -- Check for the profile option and enable trace
486 l_trace_enable_flag:=CSI_GEN_UTILITY_PVT.enable_trace(l_trace_flag => l_trace_enable_flag);
487 -- End enable trace
488 ****/
489
490 -- Start API body
491 IF p_pricing_attribs_tbl.COUNT > 0 THEN
492 FOR tab_row IN p_pricing_attribs_tbl.FIRST .. p_pricing_attribs_tbl.LAST
493 LOOP
494 IF p_pricing_attribs_tbl.EXISTS(tab_row) THEN
495 csi_pricing_attribs_pvt.update_pricing_attribs
496 ( p_api_version => p_api_version
497 ,p_commit => fnd_api.g_false
498 ,p_init_msg_list => p_init_msg_list
499 ,p_validation_level => p_validation_level
500 ,p_pricing_attribs_rec => p_pricing_attribs_tbl(tab_row)
501 ,p_txn_rec => p_txn_rec
502 ,x_return_status => x_return_status
503 ,x_msg_count => x_msg_count
504 ,x_msg_data => x_msg_data
505 );
506
507 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
508 l_msg_index := 1;
509
510 l_msg_count := x_msg_count;
511 WHILE l_msg_count > 0 LOOP
512 x_msg_data := FND_MSG_PUB.GET
513 (l_msg_index,
514 FND_API.G_FALSE );
515
516 csi_gen_utility_pvt.put_line( ' Failed Pub:update_pricing_attribs..');
517 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
518 l_msg_index := l_msg_index + 1;
519 l_msg_count := l_msg_count - 1;
520 END LOOP;
521 RAISE FND_API.G_EXC_ERROR;
522 END IF;
523 END IF;
524 END LOOP;
525 END IF;
526
527 -- End of API body
528
529
530 -- Standard check of p_commit.
531 IF FND_API.To_Boolean( p_commit ) THEN
532 COMMIT WORK;
533 END IF;
534
535 /***** srramakr commented for bug # 3304439
536 -- Check for the profile option and disable the trace
537 IF (l_trace_enable_flag = 'Y') THEN
538 dbms_session.set_sql_trace(false);
539 END IF;
540 -- End disable trace
541 ****/
542
543 -- Standard call to get message count and if count is get message info.
544 FND_MSG_PUB.Count_And_Get
545 (p_count => x_msg_count ,
546 p_data => x_msg_data
547 );
548
549
550 EXCEPTION
551 WHEN FND_API.G_EXC_ERROR THEN
552 ROLLBACK TO update_pricing_attribs;
553 x_return_status := FND_API.G_RET_STS_ERROR ;
554 FND_MSG_PUB.Count_And_Get
555 ( p_count => x_msg_count,
556 p_data => x_msg_data
557 );
558
559 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
560 ROLLBACK TO update_pricing_attribs;
561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
562 FND_MSG_PUB.Count_And_Get
563 ( p_count => x_msg_count,
564 p_data => x_msg_data
565 );
566
567 WHEN OTHERS THEN
568 ROLLBACK TO update_pricing_attribs;
569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
570
571 IF FND_MSG_PUB.Check_Msg_Level
572 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
573 THEN
574 FND_MSG_PUB.Add_Exc_Msg
575 ( g_pkg_name,
576 l_api_name
577 );
578 END IF;
579
580 FND_MSG_PUB.Count_And_Get
581 ( p_count => x_msg_count,
582 p_data => x_msg_data
583 );
584
585 END update_pricing_attribs;
586
587
588
589 /*--------------------------------------------------------*/
590 /* procedure name: expire_pricing_attribs */
591 /* description : Expires the existing pricing */
592 /* attributes for an item instance */
593 /* */
594 /*--------------------------------------------------------*/
595
596
597 PROCEDURE expire_pricing_attribs
598 ( p_api_version IN NUMBER
599 ,p_commit IN VARCHAR2
600 ,p_init_msg_list IN VARCHAR2
601 ,p_validation_level IN NUMBER
602 ,p_pricing_attribs_tbl IN csi_datastructures_pub.pricing_attribs_tbl
603 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
604 ,x_return_status OUT NOCOPY VARCHAR2
605 ,x_msg_count OUT NOCOPY NUMBER
606 ,x_msg_data OUT NOCOPY VARCHAR2
607 )
608
609 IS
610 l_api_name CONSTANT VARCHAR2(30) := 'EXPIRE_PRICING_ATTRIBS';
611 l_api_version CONSTANT NUMBER := 1.0;
612 l_debug_level NUMBER;
613 l_msg_index NUMBER;
614 l_msg_count NUMBER;
615 l_trace_enable_flag VARCHAR2(1) :='N';
616
617 BEGIN
618
619 -- Standard Start of API savepoint
620 SAVEPOINT expire_pricing_attribs;
621
622 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
623
624 csi_utility_grp.check_ib_active;
625
626 -- Standard call to check for call compatibility.
627 IF NOT FND_API.Compatible_API_Call (l_api_version,
628 p_api_version,
629 l_api_name ,
630 g_pkg_name)
631 THEN
632 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
633 END IF;
634
635
636 -- Initialize message list if p_init_msg_list is set to TRUE.
637 IF FND_API.to_Boolean( p_init_msg_list ) THEN
638 FND_MSG_PUB.initialize;
639 END IF;
640
641
642 -- Initialize API return status to success
643 x_return_status := FND_API.G_RET_STS_SUCCESS;
644
645
646 -- Check the profile option debug_level for debug message reporting
647 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
648
649 -- If debug_level = 1 then dump the procedure name
650 IF (l_debug_level > 0) THEN
651 csi_gen_utility_pvt.put_line( 'expire_pricing_attribs');
652 END IF;
653
654
655 -- If the debug level = 2 then dump all the parameters values.
656 IF (l_debug_level > 1) THEN
657 csi_gen_utility_pvt.put_line( p_api_version ||'-'
658 || p_commit ||'-'
659 || p_init_msg_list ||'-'
660 || p_validation_level );
661
662 -- Dump pricing_attribs_tbl
663 csi_gen_utility_pvt.dump_pricing_attribs_tbl(p_pricing_attribs_tbl);
664 -- Dump txn_rec
665 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
666
667 END IF;
668
669 /***** srramakr commented for bug # 3304439
670 -- Check for the profile option and enable trace
671 l_trace_enable_flag:=CSI_GEN_UTILITY_PVT.enable_trace(l_trace_flag => l_trace_enable_flag);
672 -- End enable trace
673 ****/
674
675 -- Start API body
676 IF p_pricing_attribs_tbl.COUNT > 0 THEN
677 FOR tab_row IN p_pricing_attribs_tbl.FIRST .. p_pricing_attribs_tbl.LAST
678 LOOP
679 IF p_pricing_attribs_tbl.EXISTS(tab_row) THEN
680 csi_pricing_attribs_pvt.expire_pricing_attribs
681 ( p_api_version => p_api_version
682 ,p_commit => fnd_api.g_false
683 ,p_init_msg_list => p_init_msg_list
684 ,p_validation_level => p_validation_level
685 ,p_pricing_attribs_rec => p_pricing_attribs_tbl(tab_row)
686 ,p_txn_rec => p_txn_rec
687 ,x_return_status => x_return_status
688 ,x_msg_count => x_msg_count
689 ,x_msg_data => x_msg_data
690 );
691
692 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
693 l_msg_index := 1;
694 l_msg_count := x_msg_count;
695 WHILE l_msg_count > 0 LOOP
696 x_msg_data := FND_MSG_PUB.GET
697 (l_msg_index,
698 FND_API.G_FALSE );
699
700 csi_gen_utility_pvt.put_line( ' Failed Pub:expire_pricing_attribs..');
701 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
702 l_msg_index := l_msg_index + 1;
703 l_msg_count := l_msg_count - 1;
704 END LOOP;
705 RAISE FND_API.G_EXC_ERROR;
706 END IF;
707
708 END IF;
709 END LOOP;
710 END IF;
711
712 -- End of API body
713
714
715 -- Standard check of p_commit.
716 IF FND_API.To_Boolean( p_commit ) THEN
717 COMMIT WORK;
718 END IF;
719
720 /***** srramakr commented for bug # 3304439
721 -- Check for the profile option and disable the trace
722 IF (l_trace_enable_flag = 'Y') THEN
723 dbms_session.set_sql_trace(false);
724 END IF;
725 -- End disable trace
726 ****/
727
728 -- Standard call to get message count and if count is get message info.
729 FND_MSG_PUB.Count_And_Get
730 (p_count => x_msg_count ,
731 p_data => x_msg_data
732 );
733
734
735 EXCEPTION
736
737 WHEN FND_API.G_EXC_ERROR THEN
738
739 ROLLBACK TO expire_pricing_attribs;
740 x_return_status := FND_API.G_RET_STS_ERROR ;
741 FND_MSG_PUB.Count_And_Get
742 ( p_count => x_msg_count,
743 p_data => x_msg_data
744 );
745
746 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
747
748 ROLLBACK TO expire_pricing_attribs;
749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
750 FND_MSG_PUB.Count_And_Get
751 ( p_count => x_msg_count,
752 p_data => x_msg_data
753 );
754
755 WHEN OTHERS THEN
756
757 ROLLBACK TO expire_pricing_attribs;
758 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
759
760 IF FND_MSG_PUB.Check_Msg_Level
761 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
762 THEN
763 FND_MSG_PUB.Add_Exc_Msg
764 ( g_pkg_name,
765 l_api_name
766 );
767 END IF;
768
769 FND_MSG_PUB.Count_And_Get
770 ( p_count => x_msg_count,
771 p_data => x_msg_data
772 );
773
774 END expire_pricing_attribs;
775
776
777 END csi_pricing_attribs_pub;
778