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