[Home] [Help]
PACKAGE BODY: APPS.BIS_PMV_REGION_ITEMS_PVT
Source
1 PACKAGE BODY BIS_PMV_REGION_ITEMS_PVT AS
2 /* $Header: BISVRITB.pls 120.1 2005/10/06 07:09:26 adrao 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 | BISVRITB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Module: Private API for Region Items |
13 REM | |
14 REM | NOTES |
15 REM | 02/03/04 nbarik Created. |
16 REM | 05/22/04 adrao Added Exception Handling |
17 REM | 10/03/05 adrao Added new region item attribute Grand_Total_Flag |
18 REM | for Bug#4594984 |
19 REM +=======================================================================+
20 */
21
22 PROCEDURE CREATE_REGION_ITEMS
23 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
24 , p_region_code IN VARCHAR2
25 , p_region_application_id IN NUMBER
26 , p_Region_Item_Tbl IN BIS_AK_REGION_PUB.Bis_Region_Item_Tbl_Type
27 , x_return_status OUT NOCOPY VARCHAR2
28 , x_msg_count OUT NOCOPY NUMBER
29 , x_msg_data OUT NOCOPY VARCHAR2
30 ) IS
31 l_Region_Item_Rec BIS_AK_REGION_PUB.Bis_Region_Item_Rec_Type;
32 l_commit VARCHAR2(1) := 'N';
33 BEGIN
34 FND_MSG_PUB.Initialize;
35 x_return_status := FND_API.G_RET_STS_SUCCESS;
36 IF (p_Region_Item_Tbl IS NOT NULL AND p_Region_Item_Tbl.COUNT > 0) THEN
37 FOR i IN p_Region_Item_Tbl.FIRST..p_Region_Item_Tbl.LAST LOOP
38 l_Region_Item_Rec := p_Region_Item_Tbl(i);
39 BIS_AK_REGION_PUB.INSERT_REGION_ITEM_ROW
40 ( p_commit => p_commit
41 , p_region_code => p_region_code
42 , p_region_application_id => p_region_application_id
43 , p_Region_Item_Rec => l_Region_Item_Rec
44 , x_return_status => x_return_status
45 , x_msg_count => x_msg_count
46 , x_msg_data => x_msg_data
47 );
48 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
49 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
50 END IF;
51
52 IF (p_commit = FND_API.G_TRUE) THEN
53 l_commit := 'Y';
54 END IF;
55
56 -- No primary key validation on BIS_AK_REGION_EXTENSION
57 BIS_REGION_ITEM_EXTENSION_PVT.CREATE_REGION_ITEM_RECORD
58 ( pRegionCode => p_region_code
59 , pRegionAppId => p_region_application_id
60 , pAttributeCode => l_Region_Item_Rec.Attribute_Code
61 , pAttributeAppId => l_Region_Item_Rec.Attribute_Application_Id
62 , pAttribute16 => l_Region_Item_Rec.Additional_View_By
63 , pAttribute17 => l_Region_Item_Rec.Rolling_Lookup
64 , pAttribute18 => l_Region_Item_Rec.Operator_Lookup
65 , pAttribute19 => l_Region_Item_Rec.Dual_YAxis_Graphs
66 , pAttribute20 => l_Region_Item_Rec.Custom_View_Name
67 , pAttribute21 => l_Region_Item_Rec.Graph_Measure_Type
68 , pAttribute22 => l_Region_Item_Rec.Hide_Target_In_Table
69 , pAttribute23 => l_Region_Item_Rec.Parameter_Render_Type
70 , pAttribute24 => l_Region_Item_Rec.Privilege
71 , pAttribute25 => NULL
72 , pAttribute26 => l_Region_Item_Rec.Grand_Total_Flag
73 , pAttribute27 => NULL
74 , pAttribute28 => NULL
75 , pAttribute29 => NULL
76 , pAttribute30 => NULL
77 , pAttribute31 => NULL
78 , pAttribute32 => NULL
79 , pAttribute33 => NULL
80 , pAttribute34 => NULL
81 , pAttribute35 => NULL
82 , pAttribute36 => NULL
83 , pAttribute37 => NULL
84 , pAttribute38 => NULL
85 , pAttribute39 => NULL
86 , pAttribute40 => NULL
87 , pCommit => l_commit
88 );
89 END LOOP;
90 END IF;
91 x_return_status := FND_API.G_RET_STS_SUCCESS;
92 EXCEPTION
93 WHEN FND_API.G_EXC_ERROR THEN
94 IF (x_msg_data IS NULL) THEN
95 FND_MSG_PUB.Count_And_Get
96 ( p_encoded => FND_API.G_FALSE
97 , p_count => x_msg_count
98 , p_data => x_msg_data
99 );
100 END IF;
101 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
102 x_return_status := FND_API.G_RET_STS_ERROR;
103 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
104 IF (x_msg_data IS NULL) THEN
105 FND_MSG_PUB.Count_And_Get
106 ( p_encoded => FND_API.G_FALSE
107 , p_count => x_msg_count
108 , p_data => x_msg_data
109 );
110 END IF;
111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
112 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
113 WHEN OTHERS THEN
114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
115 IF (x_msg_data IS NOT NULL) THEN
116 x_msg_data := x_msg_data||' -> BIS_PMV_REGION_ITEMS_PVT.CREATE_REGION_ITEMS ';
117 ELSE
118 x_msg_data := SQLERRM||' at BIS_PMV_REGION_ITEMS_PVT.CREATE_REGION_ITEMS ';
119 END IF;
120 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
121 END CREATE_REGION_ITEMS;
122
123 PROCEDURE UPDATE_REGION_ITEMS
124 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
125 , p_region_code IN VARCHAR2
126 , p_region_application_id IN NUMBER
127 , p_Region_Item_Tbl IN BIS_AK_REGION_PUB.Bis_Region_Item_Tbl_Type
128 , x_return_status OUT NOCOPY VARCHAR2
129 , x_msg_count OUT NOCOPY NUMBER
130 , x_msg_data OUT NOCOPY VARCHAR2
131 ) IS
132 l_Region_Item_Rec BIS_AK_REGION_PUB.Bis_Region_Item_Rec_Type;
133 l_commit VARCHAR2(1) := 'N';
134 BEGIN
135 FND_MSG_PUB.Initialize;
136 x_return_status := FND_API.G_RET_STS_SUCCESS;
137 IF (p_Region_Item_Tbl IS NOT NULL AND p_Region_Item_Tbl.COUNT > 0) THEN
138 FOR i IN p_Region_Item_Tbl.FIRST..p_Region_Item_Tbl.LAST LOOP
139 l_Region_Item_Rec := p_Region_Item_Tbl(i);
140 BIS_AK_REGION_PUB.UPDATE_REGION_ITEM_ROW
141 ( p_commit => p_commit
142 , p_region_code => p_region_code
143 , p_region_application_id => p_region_application_id
144 , p_Region_Item_Rec => l_Region_Item_Rec
145 , x_return_status => x_return_status
146 , x_msg_count => x_msg_count
147 , x_msg_data => x_msg_data
148 );
149 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
151 END IF;
152
153 IF (p_commit = FND_API.G_TRUE) THEN
154 l_commit := 'Y';
155 END IF;
156
157 -- No primary key validation on BIS_AK_REGION_EXTENSION
158 BIS_REGION_ITEM_EXTENSION_PVT.UPDATE_REGION_ITEM_RECORD
159 ( pRegionCode => p_region_code
160 , pRegionAppId => p_region_application_id
161 , pAttributeCode => l_Region_Item_Rec.Attribute_Code
162 , pAttributeAppId => l_Region_Item_Rec.Attribute_Application_Id
163 , pAttribute16 => l_Region_Item_Rec.Additional_View_By
164 , pAttribute17 => l_Region_Item_Rec.Rolling_Lookup
165 , pAttribute18 => l_Region_Item_Rec.Operator_Lookup
166 , pAttribute19 => l_Region_Item_Rec.Dual_YAxis_Graphs
167 , pAttribute20 => l_Region_Item_Rec.Custom_View_Name
168 , pAttribute21 => l_Region_Item_Rec.Graph_Measure_Type
169 , pAttribute22 => l_Region_Item_Rec.Hide_Target_In_Table
170 , pAttribute23 => l_Region_Item_Rec.Parameter_Render_Type
171 , pAttribute24 => l_Region_Item_Rec.Privilege
172 , pAttribute25 => NULL
173 , pAttribute26 => l_Region_Item_Rec.Grand_Total_Flag
174 , pAttribute27 => NULL
175 , pAttribute28 => NULL
176 , pAttribute29 => NULL
177 , pAttribute30 => NULL
178 , pAttribute31 => NULL
179 , pAttribute32 => NULL
180 , pAttribute33 => NULL
181 , pAttribute34 => NULL
182 , pAttribute35 => NULL
183 , pAttribute36 => NULL
184 , pAttribute37 => NULL
185 , pAttribute38 => NULL
186 , pAttribute39 => NULL
187 , pAttribute40 => NULL
188 , pCommit => l_commit
189 );
190 END LOOP;
191 END IF;
192 x_return_status := FND_API.G_RET_STS_SUCCESS;
193 EXCEPTION
194 WHEN FND_API.G_EXC_ERROR THEN
195 IF (x_msg_data IS NULL) THEN
196 FND_MSG_PUB.Count_And_Get
197 ( p_encoded => FND_API.G_FALSE
198 , p_count => x_msg_count
199 , p_data => x_msg_data
200 );
201 END IF;
202 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
203 x_return_status := FND_API.G_RET_STS_ERROR;
204 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
205 IF (x_msg_data IS NULL) THEN
206 FND_MSG_PUB.Count_And_Get
207 ( p_encoded => FND_API.G_FALSE
208 , p_count => x_msg_count
209 , p_data => x_msg_data
210 );
211 END IF;
212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
213 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
214 WHEN OTHERS THEN
215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
216 IF (x_msg_data IS NOT NULL) THEN
217 x_msg_data := x_msg_data||' -> BIS_PMV_REGION_ITEMS_PVT.UPDATE_REGION_ITEMS ';
218 ELSE
219 x_msg_data := SQLERRM||' at BIS_PMV_REGION_ITEMS_PVT.UPDATE_REGION_ITEMS ';
220 END IF;
221 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
222 END UPDATE_REGION_ITEMS;
223
224 PROCEDURE DELETE_REGION_ITEMS
225 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
226 , p_region_code IN VARCHAR2
227 , p_region_application_id IN NUMBER
228 , p_Attribute_Code_Tbl IN BISVIEWER.t_char
229 , p_Attribute_Appl_Id_Tbl IN BISVIEWER.t_num
230 , x_return_status OUT NOCOPY VARCHAR2
231 , x_msg_count OUT NOCOPY NUMBER
232 , x_msg_data OUT NOCOPY VARCHAR2
233 ) IS
234 BEGIN
235 FND_MSG_PUB.Initialize;
236 x_return_status := FND_API.G_RET_STS_SUCCESS;
237 IF (p_Attribute_Code_Tbl IS NOT NULL AND p_Attribute_Code_Tbl.COUNT > 0) THEN
238 FOR i IN p_Attribute_Code_Tbl.FIRST..p_Attribute_Code_Tbl.LAST LOOP
239 BIS_AK_REGION_PUB.DELETE_REGION_ITEM_ROW
240 ( p_REGION_CODE => p_region_code
241 , p_REGION_APPLICATION_ID => p_region_application_id
242 , p_ATTRIBUTE_CODE => p_Attribute_Code_Tbl(i)
243 , p_ATTRIBUTE_APPLICATION_ID => p_Attribute_Appl_Id_Tbl(i)
244 , x_return_status => x_return_status
245 , x_msg_count => x_msg_count
246 , x_msg_data => x_msg_data
247 , p_commit => p_commit
248 );
249 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
250 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
251 END IF;
252
253 -- No primary key validation on BIS_AK_REGION_EXTENSION
254 BIS_REGION_ITEM_EXTENSION_PVT.DELETE_REGION_ITEM_RECORD
255 ( p_commit => p_commit
256 , pRegionCode => p_region_code
257 , pRegionAppId => p_region_application_id
258 , pAttributeCode => p_Attribute_Code_Tbl(i)
259 , pAttributeAppId => p_Attribute_Appl_Id_Tbl(i)
260 );
261 END LOOP;
262 END IF;
263 x_return_status := FND_API.G_RET_STS_SUCCESS;
264 EXCEPTION
265 WHEN FND_API.G_EXC_ERROR THEN
266 IF (x_msg_data IS NULL) THEN
267 FND_MSG_PUB.Count_And_Get
268 ( p_encoded => FND_API.G_FALSE
269 , p_count => x_msg_count
270 , p_data => x_msg_data
271 );
272 END IF;
273 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
274 x_return_status := FND_API.G_RET_STS_ERROR;
275 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
276 IF (x_msg_data IS NULL) THEN
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 END IF;
283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
284 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
285 WHEN OTHERS THEN
286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
287 IF (x_msg_data IS NOT NULL) THEN
288 x_msg_data := x_msg_data||' -> BIS_PMV_REGION_ITEMS_PVT.DELETE_REGION_ITEMS ';
289 ELSE
290 x_msg_data := SQLERRM||' at BIS_PMV_REGION_ITEMS_PVT.DELETE_REGION_ITEMS ';
291 END IF;
292 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
293 END DELETE_REGION_ITEMS;
294
295 END BIS_PMV_REGION_ITEMS_PVT;