1 PACKAGE BODY BSC_BIS_DIM_GROUP_PUB AS
2 /* $Header: BSCCPMDB.pls 120.0 2005/06/01 16:28:34 appldev noship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BISCPMDB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Module: Wrapper for Dimension, part of PMD APIs |
13 REM | |
14 REM | NOTES |
15 REM | 14-FEB-2003 PAJOHRI Created. |
16 REM | |
17 REM +=======================================================================+
18 */
19
20
21 /*********************************************************************************
22 UPDATE DIMENSION
23 *********************************************************************************/
24
25 PROCEDURE Update_Dimension
26 (
27 p_commit IN VARCHAR2 := FND_API.G_TRUE
28 , p_dimension_id IN NUMBER
29 , p_short_name IN VARCHAR2
30 , p_display_name IN VARCHAR2
31 , p_description IN VARCHAR2
32 , p_application_id IN NUMBER
33 , x_return_status OUT NOCOPY VARCHAR2
34 , x_msg_count OUT NOCOPY NUMBER
35 , x_msg_data OUT NOCOPY VARCHAR2
36 )IS
37 l_return_status VARCHAR2(30);
38 l_count NUMBER;
39 l_dimension_rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
40 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
41 l_msg_data VARCHAR2(2000);
42
43 CURSOR cr_dim_id IS
44 SELECT dimension_id, short_name
45 FROM bis_dimensions
46 WHERE dimension_id = p_dimension_id;
47
48 CURSOR cr_dim_short_name IS
49 SELECT dimension_id, short_name
50 FROM bis_dimensions
51 WHERE short_name = p_short_name;
52
53 BEGIN
54
55 --check for not null fields
56 IF (p_dimension_id IS NOT NULL) THEN
57 IF (cr_dim_id%ISOPEN) THEN
58 CLOSE cr_dim_id;
59 END IF;
60 OPEN cr_dim_id;
61 FETCH cr_dim_id
62 INTO l_dimension_rec.dimension_id
63 , l_dimension_rec.dimension_short_name;
64 IF (cr_dim_id%ROWCOUNT = 0) THEN
65 l_msg_data := 'Record does not exist for BIS_DIMENSIONS.DIMENSION_ID =<'||p_dimension_id||'>';
66 x_return_status := FND_API.G_RET_STS_ERROR;
67 END IF;
68 CLOSE cr_dim_id;
69 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
70 RAISE FND_API.G_EXC_ERROR;
71 END IF;
72 ELSIF (p_short_name IS NOT NULL) THEN
73 IF (cr_dim_short_name%ISOPEN) THEN
74 CLOSE cr_dim_short_name;
75 END IF;
76 OPEN cr_dim_short_name;
77 FETCH cr_dim_short_name
78 INTO l_dimension_rec.dimension_id
79 , l_dimension_rec.dimension_short_name;
80 IF (cr_dim_short_name%ROWCOUNT = 0) THEN
81 l_msg_data := 'Record does not exist for BIS_DIMENSIONS.SHORT_NAME =<'||p_short_name||'>';
82 x_return_status := FND_API.G_RET_STS_ERROR;
83 END IF;
84 CLOSE cr_dim_short_name;
85 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
86 RAISE FND_API.G_EXC_ERROR;
87 END IF;
88 ELSE
89 l_msg_data := 'Either of P_DIMENSION_ID or P_SHORT_NAME must be NOT NULL';
90 RAISE FND_API.G_EXC_ERROR;
91 END IF;
92
93 IF (p_display_name IS NULL) THEN
94 l_msg_data := 'P_DISPLAY_NAME must be NOT NULL';
95 RAISE FND_API.G_EXC_ERROR;
96 END IF;
97
98 --check for uniqueness of p_display_name
99 SELECT COUNT(*) INTO l_count FROM bis_dimensions_vl
100 WHERE name = p_display_name
101 AND dimension_id <> l_dimension_rec.dimension_id;
102 IF (l_count > 0) THEN
103 l_msg_data := 'P_DISPLAY_NAME =<'||p_display_name||'> must be UNIQUE';
104 RAISE FND_API.G_EXC_ERROR;
105 END IF;
106
107 --call PMF's API
108 l_dimension_rec.Dimension_Name := p_display_name;
109 l_dimension_rec.Description := p_description;
110
111 BIS_DIMENSION_PUB.Update_Dimension
112 (
113 p_api_version => 1.0
114 , p_commit => FND_API.G_FALSE
115 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
116 , p_Dimension_Rec => l_dimension_rec
117 , x_return_status => l_return_status
118 , x_error_Tbl => l_error_tbl
119 );
120 IF ((l_return_status = FND_API.G_RET_STS_SUCCESS) OR (l_return_status IS NULL) OR
121 ((l_return_status <> FND_API.G_RET_STS_ERROR) AND
122 (l_return_status <> FND_API.G_RET_STS_UNEXP_ERROR))) THEN
123 x_return_status := FND_API.G_RET_STS_SUCCESS;
124 IF (p_commit = FND_API.G_TRUE) THEN
125 COMMIT;
126 END if;
127 ELSE
128 IF (l_error_tbl.COUNT > 0) THEN
129 l_msg_data := l_error_tbl(l_error_tbl.COUNT).Error_Description;
130 END IF;
131
132 l_msg_data := 'BSC_BIS_DIM_GROUP_PUB.UPDATE_DIMENSION Failed : at BIS_DIMENSION_PUB.UPDATE_DIMENSION <'||l_msg_data||'>';
133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134 END IF;
135 EXCEPTION
136 WHEN FND_API.G_EXC_ERROR THEN
137 IF (cr_dim_id%ISOPEN) THEN
138 CLOSE cr_dim_id;
139 END IF;
140 IF (cr_dim_short_name%ISOPEN) THEN
141 CLOSE cr_dim_short_name;
142 END IF;
143 ROLLBACK;
144 x_return_status := FND_API.G_RET_STS_ERROR;
145 x_msg_data := l_msg_data;
146 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
147 IF (cr_dim_id%ISOPEN) THEN
148 CLOSE cr_dim_id;
149 END IF;
150 IF (cr_dim_short_name%ISOPEN) THEN
151 CLOSE cr_dim_short_name;
152 END IF;
153 ROLLBACK;
154 FND_MSG_PUB.Count_And_Get
155 (
156 p_count => x_msg_count
157 , p_data => x_msg_data
158 );
159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
160 x_msg_data := x_msg_data||' '||l_msg_data;
161 WHEN NO_DATA_FOUND THEN
162 IF (cr_dim_id%ISOPEN) THEN
163 CLOSE cr_dim_id;
164 END IF;
165 IF (cr_dim_short_name%ISOPEN) THEN
166 CLOSE cr_dim_short_name;
167 END IF;
168 ROLLBACK;
169 x_return_status := FND_API.G_RET_STS_ERROR;
170 FND_MSG_PUB.Count_And_Get
171 (
172 p_count => x_msg_count
173 , p_data => x_msg_data
174 );
175 WHEN OTHERS THEN
176 IF (cr_dim_id%ISOPEN) THEN
177 CLOSE cr_dim_id;
178 END IF;
179 IF (cr_dim_short_name%ISOPEN) THEN
180 CLOSE cr_dim_short_name;
181 END IF;
182 ROLLBACK;
183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
184 FND_MSG_PUB.Count_And_Get
185 (
186 p_count => x_msg_count
187 , p_data => x_msg_data
188 );
189 END UPDATE_DIMENSION;
190
191 END BSC_BIS_DIM_GROUP_PUB;
[Home] [Help]