[Home] [Help]
50: --: The following procedure is used to create the BSC Dimension entity.
51: --: It is the entry point to populate all necessary meta data.
52: --: This procedure is part of the Dimension API.
53: procedure Create_Dim_Level(
54: p_commit IN varchar2 ---:= FND_API.G_FALSE
55: ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
56: ,p_create_tables IN BOOLEAN
57: ,x_return_status OUT NOCOPY varchar2
58: ,x_msg_count OUT NOCOPY number
64: l_count number;
65:
66: begin
67: --Assign passed Record values to local Record.
68: x_return_status := FND_API.G_RET_STS_SUCCESS;
69: l_Dim_Level_Rec := p_Dim_Level_Rec;
70:
71: -- Check to see that dimension level has not been created. If it has not then create it
72: -- else do nothing.
144: ,l_Dim_Level_Rec
145: ,x_return_status
146: ,x_msg_count
147: ,x_msg_data);
148: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
149: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150: END IF;
151: END IF;
152: EXCEPTION
145: ,x_return_status
146: ,x_msg_count
147: ,x_msg_data);
148: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
149: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150: END IF;
151: END IF;
152: EXCEPTION
153: WHEN FND_API.G_EXC_ERROR THEN
149: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150: END IF;
151: END IF;
152: EXCEPTION
153: WHEN FND_API.G_EXC_ERROR THEN
154: IF (x_msg_data IS NULL) THEN
155: FND_MSG_PUB.Count_And_Get
156: ( p_encoded => 'F'
157: , p_count => x_msg_count
157: , p_count => x_msg_count
158: , p_data => x_msg_data
159: );
160: END IF;
161: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
162: x_return_status := FND_API.G_RET_STS_ERROR;
163: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
164: IF (x_msg_data IS NULL) THEN
165: FND_MSG_PUB.Count_And_Get
158: , p_data => x_msg_data
159: );
160: END IF;
161: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
162: x_return_status := FND_API.G_RET_STS_ERROR;
163: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
164: IF (x_msg_data IS NULL) THEN
165: FND_MSG_PUB.Count_And_Get
166: ( p_encoded => 'F'
159: );
160: END IF;
161: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
162: x_return_status := FND_API.G_RET_STS_ERROR;
163: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
164: IF (x_msg_data IS NULL) THEN
165: FND_MSG_PUB.Count_And_Get
166: ( p_encoded => 'F'
167: , p_count => x_msg_count
167: , p_count => x_msg_count
168: , p_data => x_msg_data
169: );
170: END IF;
171: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
172: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
173: WHEN NO_DATA_FOUND THEN
174: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
175: IF (x_msg_data IS NOT NULL) THEN
168: , p_data => x_msg_data
169: );
170: END IF;
171: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
172: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
173: WHEN NO_DATA_FOUND THEN
174: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
175: IF (x_msg_data IS NOT NULL) THEN
176: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
170: END IF;
171: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
172: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
173: WHEN NO_DATA_FOUND THEN
174: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
175: IF (x_msg_data IS NOT NULL) THEN
176: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
177: ELSE
178: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
178: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
179: END IF;
180: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
181: WHEN OTHERS THEN
182: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
183: IF (x_msg_data IS NOT NULL) THEN
184: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
185: ELSE
186: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
189: end Create_Dim_Level;
190:
191:
192: procedure Create_Dim_Level(
193: p_commit IN varchar2 --:= FND_API.G_FALSE
194: ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
195: ,x_return_status OUT NOCOPY varchar2
196: ,x_msg_count OUT NOCOPY number
197: ,x_msg_data OUT NOCOPY varchar2
198: ) is
199:
200: l_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
201: BEGIN
202: x_return_status := FND_API.G_RET_STS_SUCCESS;
203: l_Dim_Level_Rec := p_Dim_Level_Rec;
204: l_Dim_Level_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Level_Rec.Bsc_Last_Update_Date, SYSDATE);
205:
206: BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level
211: , x_return_status => x_return_status
212: , x_msg_count => x_msg_count
213: , x_msg_data => x_msg_data
214: );
215: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
216: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
217: END IF;
218: EXCEPTION
219: WHEN FND_API.G_EXC_ERROR THEN
212: , x_msg_count => x_msg_count
213: , x_msg_data => x_msg_data
214: );
215: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
216: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
217: END IF;
218: EXCEPTION
219: WHEN FND_API.G_EXC_ERROR THEN
220: IF (x_msg_data IS NULL) THEN
215: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
216: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
217: END IF;
218: EXCEPTION
219: WHEN FND_API.G_EXC_ERROR THEN
220: IF (x_msg_data IS NULL) THEN
221: FND_MSG_PUB.Count_And_Get
222: ( p_encoded => 'F'
223: , p_count => x_msg_count
223: , p_count => x_msg_count
224: , p_data => x_msg_data
225: );
226: END IF;
227: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
228: x_return_status := FND_API.G_RET_STS_ERROR;
229: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
230: IF (x_msg_data IS NULL) THEN
231: FND_MSG_PUB.Count_And_Get
224: , p_data => x_msg_data
225: );
226: END IF;
227: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
228: x_return_status := FND_API.G_RET_STS_ERROR;
229: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
230: IF (x_msg_data IS NULL) THEN
231: FND_MSG_PUB.Count_And_Get
232: ( p_encoded => 'F'
225: );
226: END IF;
227: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
228: x_return_status := FND_API.G_RET_STS_ERROR;
229: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
230: IF (x_msg_data IS NULL) THEN
231: FND_MSG_PUB.Count_And_Get
232: ( p_encoded => 'F'
233: , p_count => x_msg_count
233: , p_count => x_msg_count
234: , p_data => x_msg_data
235: );
236: END IF;
237: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
238: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
239: WHEN NO_DATA_FOUND THEN
240: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
241: IF (x_msg_data IS NOT NULL) THEN
234: , p_data => x_msg_data
235: );
236: END IF;
237: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
238: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
239: WHEN NO_DATA_FOUND THEN
240: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
241: IF (x_msg_data IS NOT NULL) THEN
242: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
236: END IF;
237: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
238: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
239: WHEN NO_DATA_FOUND THEN
240: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
241: IF (x_msg_data IS NOT NULL) THEN
242: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
243: ELSE
244: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
244: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
245: END IF;
246: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
247: WHEN OTHERS THEN
248: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
249: IF (x_msg_data IS NOT NULL) THEN
250: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
251: ELSE
252: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
257: /************************************************************************************
258: ************************************************************************************/
259:
260: procedure Retrieve_Dim_Level(
261: p_commit IN varchar2 --:= FND_API.G_FALSE
262: ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
263: ,x_Dim_Level_Rec IN OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
264: ,x_return_status OUT NOCOPY varchar2
265: ,x_msg_count OUT NOCOPY number
266: ,x_msg_data OUT NOCOPY varchar2
267: ) is
268:
269: begin
270: x_return_status := FND_API.G_RET_STS_SUCCESS;
271: Retrieve_Bsc_Dim_Levels_Md( p_commit
272: ,p_Dim_Level_Rec
273: ,x_Dim_Level_Rec
274: ,x_return_status
273: ,x_Dim_Level_Rec
274: ,x_return_status
275: ,x_msg_count
276: ,x_msg_data);
277: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
278: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279: END IF;
280: EXCEPTION
281: WHEN FND_API.G_EXC_ERROR THEN
274: ,x_return_status
275: ,x_msg_count
276: ,x_msg_data);
277: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
278: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279: END IF;
280: EXCEPTION
281: WHEN FND_API.G_EXC_ERROR THEN
282: IF (x_msg_data IS NULL) THEN
277: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
278: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279: END IF;
280: EXCEPTION
281: WHEN FND_API.G_EXC_ERROR THEN
282: IF (x_msg_data IS NULL) THEN
283: FND_MSG_PUB.Count_And_Get
284: ( p_encoded => 'F'
285: , p_count => x_msg_count
285: , p_count => x_msg_count
286: , p_data => x_msg_data
287: );
288: END IF;
289: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
290: x_return_status := FND_API.G_RET_STS_ERROR;
291: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
292: IF (x_msg_data IS NULL) THEN
293: FND_MSG_PUB.Count_And_Get
286: , p_data => x_msg_data
287: );
288: END IF;
289: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
290: x_return_status := FND_API.G_RET_STS_ERROR;
291: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
292: IF (x_msg_data IS NULL) THEN
293: FND_MSG_PUB.Count_And_Get
294: ( p_encoded => 'F'
287: );
288: END IF;
289: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
290: x_return_status := FND_API.G_RET_STS_ERROR;
291: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
292: IF (x_msg_data IS NULL) THEN
293: FND_MSG_PUB.Count_And_Get
294: ( p_encoded => 'F'
295: , p_count => x_msg_count
295: , p_count => x_msg_count
296: , p_data => x_msg_data
297: );
298: END IF;
299: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
300: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
301: WHEN NO_DATA_FOUND THEN
302: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303: IF (x_msg_data IS NOT NULL) THEN
296: , p_data => x_msg_data
297: );
298: END IF;
299: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
300: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
301: WHEN NO_DATA_FOUND THEN
302: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303: IF (x_msg_data IS NOT NULL) THEN
304: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Dim_Level ';
298: END IF;
299: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
300: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
301: WHEN NO_DATA_FOUND THEN
302: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303: IF (x_msg_data IS NOT NULL) THEN
304: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Dim_Level ';
305: ELSE
306: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Dim_Level ';
306: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Dim_Level ';
307: END IF;
308: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
309: WHEN OTHERS THEN
310: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
311: IF (x_msg_data IS NOT NULL) THEN
312: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Dim_Level ';
313: ELSE
314: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Dim_Level ';
319: /************************************************************************************
320: ************************************************************************************/
321:
322: procedure Update_Dim_Level(
323: p_commit IN varchar2 --:= FND_API.G_FALSE
324: ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
325: ,x_return_status OUT NOCOPY varchar2
326: ,x_msg_count OUT NOCOPY number
327: ,x_msg_data OUT NOCOPY varchar2
329:
330: l_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
331: begin
332:
333: x_return_status := FND_API.G_RET_STS_SUCCESS;
334:
335: l_Dim_Level_Rec := p_Dim_Level_Rec;
336: l_Dim_Level_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Level_Rec.Bsc_Last_Update_Date, SYSDATE);
337:
340: ,x_return_status
341: ,x_msg_count
342: ,x_msg_data);
343:
344: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
345: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
346: END IF;
347: EXCEPTION
348: WHEN FND_API.G_EXC_ERROR THEN
341: ,x_msg_count
342: ,x_msg_data);
343:
344: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
345: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
346: END IF;
347: EXCEPTION
348: WHEN FND_API.G_EXC_ERROR THEN
349: IF (x_msg_data IS NULL) THEN
344: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
345: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
346: END IF;
347: EXCEPTION
348: WHEN FND_API.G_EXC_ERROR THEN
349: IF (x_msg_data IS NULL) THEN
350: FND_MSG_PUB.Count_And_Get
351: ( p_encoded => 'F'
352: , p_count => x_msg_count
352: , p_count => x_msg_count
353: , p_data => x_msg_data
354: );
355: END IF;
356: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
357: x_return_status := FND_API.G_RET_STS_ERROR;
358: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
359: IF (x_msg_data IS NULL) THEN
360: FND_MSG_PUB.Count_And_Get
353: , p_data => x_msg_data
354: );
355: END IF;
356: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
357: x_return_status := FND_API.G_RET_STS_ERROR;
358: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
359: IF (x_msg_data IS NULL) THEN
360: FND_MSG_PUB.Count_And_Get
361: ( p_encoded => 'F'
354: );
355: END IF;
356: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
357: x_return_status := FND_API.G_RET_STS_ERROR;
358: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
359: IF (x_msg_data IS NULL) THEN
360: FND_MSG_PUB.Count_And_Get
361: ( p_encoded => 'F'
362: , p_count => x_msg_count
362: , p_count => x_msg_count
363: , p_data => x_msg_data
364: );
365: END IF;
366: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
367: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
368: WHEN NO_DATA_FOUND THEN
369: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
370: IF (x_msg_data IS NOT NULL) THEN
363: , p_data => x_msg_data
364: );
365: END IF;
366: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
367: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
368: WHEN NO_DATA_FOUND THEN
369: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
370: IF (x_msg_data IS NOT NULL) THEN
371: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Update_Dim_Level ';
365: END IF;
366: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
367: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
368: WHEN NO_DATA_FOUND THEN
369: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
370: IF (x_msg_data IS NOT NULL) THEN
371: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Update_Dim_Level ';
372: ELSE
373: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Update_Dim_Level ';
373: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Update_Dim_Level ';
374: END IF;
375: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
376: WHEN OTHERS THEN
377: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
378: IF (x_msg_data IS NOT NULL) THEN
379: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Update_Dim_Level ';
380: ELSE
381: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Update_Dim_Level ';
386: /************************************************************************************
387: ************************************************************************************/
388:
389: procedure Delete_Dim_Level(
390: p_commit IN varchar2 --:= FND_API.G_FALSE
391: ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
392: ,x_return_status OUT NOCOPY varchar2
393: ,x_msg_count OUT NOCOPY number
394: ,x_msg_data OUT NOCOPY varchar2
394: ,x_msg_data OUT NOCOPY varchar2
395: ) is
396:
397: begin
398: x_return_status := FND_API.G_RET_STS_SUCCESS;
399: BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level
400: (
401: p_commit => p_commit
402: , p_Dim_Level_Rec => p_Dim_Level_Rec
408: ,p_Dim_Level_Rec
409: ,x_return_status
410: ,x_msg_count
411: ,x_msg_data);
412: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
413: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
414: END IF;
415: EXCEPTION
416: WHEN FND_API.G_EXC_ERROR THEN
409: ,x_return_status
410: ,x_msg_count
411: ,x_msg_data);
412: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
413: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
414: END IF;
415: EXCEPTION
416: WHEN FND_API.G_EXC_ERROR THEN
417: IF (x_msg_data IS NULL) THEN
412: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
413: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
414: END IF;
415: EXCEPTION
416: WHEN FND_API.G_EXC_ERROR THEN
417: IF (x_msg_data IS NULL) THEN
418: FND_MSG_PUB.Count_And_Get
419: ( p_encoded => 'F'
420: , p_count => x_msg_count
420: , p_count => x_msg_count
421: , p_data => x_msg_data
422: );
423: END IF;
424: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
425: x_return_status := FND_API.G_RET_STS_ERROR;
426: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
427: IF (x_msg_data IS NULL) THEN
428: FND_MSG_PUB.Count_And_Get
421: , p_data => x_msg_data
422: );
423: END IF;
424: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
425: x_return_status := FND_API.G_RET_STS_ERROR;
426: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
427: IF (x_msg_data IS NULL) THEN
428: FND_MSG_PUB.Count_And_Get
429: ( p_encoded => 'F'
422: );
423: END IF;
424: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
425: x_return_status := FND_API.G_RET_STS_ERROR;
426: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
427: IF (x_msg_data IS NULL) THEN
428: FND_MSG_PUB.Count_And_Get
429: ( p_encoded => 'F'
430: , p_count => x_msg_count
430: , p_count => x_msg_count
431: , p_data => x_msg_data
432: );
433: END IF;
434: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
435: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
436: WHEN NO_DATA_FOUND THEN
437: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
438: IF (x_msg_data IS NOT NULL) THEN
431: , p_data => x_msg_data
432: );
433: END IF;
434: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
435: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
436: WHEN NO_DATA_FOUND THEN
437: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
438: IF (x_msg_data IS NOT NULL) THEN
439: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level ';
433: END IF;
434: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
435: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
436: WHEN NO_DATA_FOUND THEN
437: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
438: IF (x_msg_data IS NOT NULL) THEN
439: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level ';
440: ELSE
441: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level ';
441: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level ';
442: END IF;
443: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
444: WHEN OTHERS THEN
445: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
446: IF (x_msg_data IS NOT NULL) THEN
447: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level ';
448: ELSE
449: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level ';
458: --: dimension id, dimension names, dimension view/table columns.
459: --: This procedure is part of the Dimension API.
460:
461: procedure Create_Bsc_Dim_Levels_Md(
462: p_commit IN varchar2 -- := FND_API.G_FALSE
463: ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
464: ,x_return_status OUT NOCOPY varchar2
465: ,x_msg_count OUT NOCOPY number
466: ,x_msg_data OUT NOCOPY varchar2
468:
469: l_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
470:
471: begin
472: x_return_status := FND_API.G_RET_STS_SUCCESS;
473: -- Assign all values in the passed "Record" parameter to the locally defined
474: -- "Record" variable.
475: l_Dim_Level_Rec := p_Dim_Level_Rec;
476:
492: ,l_Dim_Level_Rec
493: ,x_return_status
494: ,x_msg_count
495: ,x_msg_data);
496: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
497: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
498: END IF;
499: l_Dim_Level_Rec.Bsc_Level_Column_Name := 'USER_CODE';
500: l_Dim_Level_Rec.Bsc_Level_Column_Type := 'U';
493: ,x_return_status
494: ,x_msg_count
495: ,x_msg_data);
496: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
497: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
498: END IF;
499: l_Dim_Level_Rec.Bsc_Level_Column_Name := 'USER_CODE';
500: l_Dim_Level_Rec.Bsc_Level_Column_Type := 'U';
501: Create_Bsc_Sys_Dim_Lvl_Cols( p_commit
502: ,l_Dim_Level_Rec
503: ,x_return_status
504: ,x_msg_count
505: ,x_msg_data);
506: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
507: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
508: END IF;
509: l_Dim_Level_Rec.Bsc_Level_Column_Name := 'NAME';
510: l_Dim_Level_Rec.Bsc_Level_Column_Type := 'D';
503: ,x_return_status
504: ,x_msg_count
505: ,x_msg_data);
506: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
507: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
508: END IF;
509: l_Dim_Level_Rec.Bsc_Level_Column_Name := 'NAME';
510: l_Dim_Level_Rec.Bsc_Level_Column_Type := 'D';
511: Create_Bsc_Sys_Dim_Lvl_Cols( p_commit
512: ,l_Dim_Level_Rec
513: ,x_return_status
514: ,x_msg_count
515: ,x_msg_data);
516: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
517: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
518: END IF;
519: EXCEPTION
520: WHEN FND_API.G_EXC_ERROR THEN
513: ,x_return_status
514: ,x_msg_count
515: ,x_msg_data);
516: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
517: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
518: END IF;
519: EXCEPTION
520: WHEN FND_API.G_EXC_ERROR THEN
521: IF (x_msg_data IS NULL) THEN
516: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
517: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
518: END IF;
519: EXCEPTION
520: WHEN FND_API.G_EXC_ERROR THEN
521: IF (x_msg_data IS NULL) THEN
522: FND_MSG_PUB.Count_And_Get
523: ( p_encoded => 'F'
524: , p_count => x_msg_count
524: , p_count => x_msg_count
525: , p_data => x_msg_data
526: );
527: END IF;
528: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
529: x_return_status := FND_API.G_RET_STS_ERROR;
530: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
531: IF (x_msg_data IS NULL) THEN
532: FND_MSG_PUB.Count_And_Get
525: , p_data => x_msg_data
526: );
527: END IF;
528: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
529: x_return_status := FND_API.G_RET_STS_ERROR;
530: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
531: IF (x_msg_data IS NULL) THEN
532: FND_MSG_PUB.Count_And_Get
533: ( p_encoded => 'F'
526: );
527: END IF;
528: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
529: x_return_status := FND_API.G_RET_STS_ERROR;
530: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
531: IF (x_msg_data IS NULL) THEN
532: FND_MSG_PUB.Count_And_Get
533: ( p_encoded => 'F'
534: , p_count => x_msg_count
534: , p_count => x_msg_count
535: , p_data => x_msg_data
536: );
537: END IF;
538: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
539: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
540: WHEN NO_DATA_FOUND THEN
541: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
542: IF (x_msg_data IS NOT NULL) THEN
535: , p_data => x_msg_data
536: );
537: END IF;
538: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
539: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
540: WHEN NO_DATA_FOUND THEN
541: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
542: IF (x_msg_data IS NOT NULL) THEN
543: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Dim_Levels_Md ';
537: END IF;
538: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
539: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
540: WHEN NO_DATA_FOUND THEN
541: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
542: IF (x_msg_data IS NOT NULL) THEN
543: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Dim_Levels_Md ';
544: ELSE
545: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Dim_Levels_Md ';
545: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Dim_Levels_Md ';
546: END IF;
547: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
548: WHEN OTHERS THEN
549: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550: IF (x_msg_data IS NOT NULL) THEN
551: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Dim_Levels_Md ';
552: ELSE
553: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Dim_Levels_Md ';
558: /************************************************************************************
559: ************************************************************************************/
560:
561: procedure Retrieve_Bsc_Dim_Levels_Md(
562: p_commit IN varchar2 --:= FND_API.G_FALSE
563: ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
564: ,x_Dim_Level_Rec IN OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
565: ,x_return_status OUT NOCOPY varchar2
566: ,x_msg_count OUT NOCOPY number
567: ,x_msg_data OUT NOCOPY varchar2
568: ) is
569:
570: begin
571: x_return_status := FND_API.G_RET_STS_SUCCESS;
572: BSC_DIMENSION_LEVELS_PVT.Retrieve_Bsc_Dim_Levels_Md( p_commit
573: ,p_Dim_Level_Rec
574: ,x_Dim_Level_Rec
575: ,x_return_status
581: ,x_Dim_Level_Rec
582: ,x_return_status
583: ,x_msg_count
584: ,x_msg_data);
585: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
586: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
587: END IF;
588: EXCEPTION
589: WHEN FND_API.G_EXC_ERROR THEN
582: ,x_return_status
583: ,x_msg_count
584: ,x_msg_data);
585: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
586: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
587: END IF;
588: EXCEPTION
589: WHEN FND_API.G_EXC_ERROR THEN
590: IF (x_msg_data IS NULL) THEN
585: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
586: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
587: END IF;
588: EXCEPTION
589: WHEN FND_API.G_EXC_ERROR THEN
590: IF (x_msg_data IS NULL) THEN
591: FND_MSG_PUB.Count_And_Get
592: ( p_encoded => 'F'
593: , p_count => x_msg_count
593: , p_count => x_msg_count
594: , p_data => x_msg_data
595: );
596: END IF;
597: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
598: x_return_status := FND_API.G_RET_STS_ERROR;
599: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
600: IF (x_msg_data IS NULL) THEN
601: FND_MSG_PUB.Count_And_Get
594: , p_data => x_msg_data
595: );
596: END IF;
597: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
598: x_return_status := FND_API.G_RET_STS_ERROR;
599: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
600: IF (x_msg_data IS NULL) THEN
601: FND_MSG_PUB.Count_And_Get
602: ( p_encoded => 'F'
595: );
596: END IF;
597: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
598: x_return_status := FND_API.G_RET_STS_ERROR;
599: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
600: IF (x_msg_data IS NULL) THEN
601: FND_MSG_PUB.Count_And_Get
602: ( p_encoded => 'F'
603: , p_count => x_msg_count
603: , p_count => x_msg_count
604: , p_data => x_msg_data
605: );
606: END IF;
607: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
608: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
609: WHEN NO_DATA_FOUND THEN
610: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
611: IF (x_msg_data IS NOT NULL) THEN
604: , p_data => x_msg_data
605: );
606: END IF;
607: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
608: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
609: WHEN NO_DATA_FOUND THEN
610: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
611: IF (x_msg_data IS NOT NULL) THEN
612: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Dim_Levels_Md ';
606: END IF;
607: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
608: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
609: WHEN NO_DATA_FOUND THEN
610: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
611: IF (x_msg_data IS NOT NULL) THEN
612: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Dim_Levels_Md ';
613: ELSE
614: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Dim_Levels_Md ';
614: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Dim_Levels_Md ';
615: END IF;
616: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
617: WHEN OTHERS THEN
618: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
619: IF (x_msg_data IS NOT NULL) THEN
620: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Dim_Levels_Md ';
621: ELSE
622: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Dim_Levels_Md ';
627: /************************************************************************************
628: ************************************************************************************/
629:
630: procedure Update_Bsc_Dim_Levels_Md(
631: p_commit IN varchar2 --:= FND_API.G_FALSE
632: ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
633: ,x_return_status OUT NOCOPY varchar2
634: ,x_msg_count OUT NOCOPY number
635: ,x_msg_data OUT NOCOPY varchar2
637:
638: l_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
639:
640: begin
641: x_return_status := FND_API.G_RET_STS_SUCCESS;
642: -- Assign all values in the passed "Record" parameter to the locally defined
643: -- "Record" variable.
644: l_Dim_Level_Rec := p_Dim_Level_Rec;
645: l_Dim_Level_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Level_Rec.Bsc_Last_Update_Date, SYSDATE);
655: ,l_Dim_Level_Rec
656: ,x_return_status
657: ,x_msg_count
658: ,x_msg_data);
659: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
660: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
661: END IF;
662: EXCEPTION
663: WHEN FND_API.G_EXC_ERROR THEN
656: ,x_return_status
657: ,x_msg_count
658: ,x_msg_data);
659: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
660: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
661: END IF;
662: EXCEPTION
663: WHEN FND_API.G_EXC_ERROR THEN
664: IF (x_msg_data IS NULL) THEN
659: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
660: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
661: END IF;
662: EXCEPTION
663: WHEN FND_API.G_EXC_ERROR THEN
664: IF (x_msg_data IS NULL) THEN
665: FND_MSG_PUB.Count_And_Get
666: ( p_encoded => 'F'
667: , p_count => x_msg_count
667: , p_count => x_msg_count
668: , p_data => x_msg_data
669: );
670: END IF;
671: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
672: x_return_status := FND_API.G_RET_STS_ERROR;
673: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
674: IF (x_msg_data IS NULL) THEN
675: FND_MSG_PUB.Count_And_Get
668: , p_data => x_msg_data
669: );
670: END IF;
671: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
672: x_return_status := FND_API.G_RET_STS_ERROR;
673: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
674: IF (x_msg_data IS NULL) THEN
675: FND_MSG_PUB.Count_And_Get
676: ( p_encoded => 'F'
669: );
670: END IF;
671: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
672: x_return_status := FND_API.G_RET_STS_ERROR;
673: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
674: IF (x_msg_data IS NULL) THEN
675: FND_MSG_PUB.Count_And_Get
676: ( p_encoded => 'F'
677: , p_count => x_msg_count
677: , p_count => x_msg_count
678: , p_data => x_msg_data
679: );
680: END IF;
681: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
683: WHEN NO_DATA_FOUND THEN
684: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
685: IF (x_msg_data IS NOT NULL) THEN
678: , p_data => x_msg_data
679: );
680: END IF;
681: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
683: WHEN NO_DATA_FOUND THEN
684: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
685: IF (x_msg_data IS NOT NULL) THEN
686: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Dim_Levels_Md ';
680: END IF;
681: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
683: WHEN NO_DATA_FOUND THEN
684: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
685: IF (x_msg_data IS NOT NULL) THEN
686: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Dim_Levels_Md ';
687: ELSE
688: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Dim_Levels_Md ';
688: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Dim_Levels_Md ';
689: END IF;
690: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
691: WHEN OTHERS THEN
692: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
693: IF (x_msg_data IS NOT NULL) THEN
694: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Dim_Levels_Md ';
695: ELSE
696: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Dim_Levels_Md ';
701: /************************************************************************************
702: ************************************************************************************/
703:
704: procedure Delete_Bsc_Dim_Levels_Md(
705: p_commit IN varchar2 --:= FND_API.G_FALSE
706: ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
707: ,x_return_status OUT NOCOPY varchar2
708: ,x_msg_count OUT NOCOPY number
709: ,x_msg_data OUT NOCOPY varchar2
711:
712: -- Procedure to delete meta data for the Dimension Level.
713:
714: begin
715: x_return_status := FND_API.G_RET_STS_SUCCESS;
716: -- Call the private package.procedure.
717: BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Dim_Levels_Md( p_commit
718: ,p_Dim_Level_Rec
719: ,x_return_status
718: ,p_Dim_Level_Rec
719: ,x_return_status
720: ,x_msg_count
721: ,x_msg_data);
722: IF ((x_return_status = FND_API.G_RET_STS_ERROR) OR (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
723: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
724: END IF;
725: Delete_Bsc_Sys_Dim_Lvl_Cols( p_commit
726: ,p_Dim_Level_Rec
719: ,x_return_status
720: ,x_msg_count
721: ,x_msg_data);
722: IF ((x_return_status = FND_API.G_RET_STS_ERROR) OR (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
723: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
724: END IF;
725: Delete_Bsc_Sys_Dim_Lvl_Cols( p_commit
726: ,p_Dim_Level_Rec
727: ,x_return_status
726: ,p_Dim_Level_Rec
727: ,x_return_status
728: ,x_msg_count
729: ,x_msg_data);
730: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
731: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
732: END IF;
733: EXCEPTION
734: WHEN FND_API.G_EXC_ERROR THEN
727: ,x_return_status
728: ,x_msg_count
729: ,x_msg_data);
730: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
731: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
732: END IF;
733: EXCEPTION
734: WHEN FND_API.G_EXC_ERROR THEN
735: IF (x_msg_data IS NULL) THEN
730: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
731: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
732: END IF;
733: EXCEPTION
734: WHEN FND_API.G_EXC_ERROR THEN
735: IF (x_msg_data IS NULL) THEN
736: FND_MSG_PUB.Count_And_Get
737: ( p_encoded => 'F'
738: , p_count => x_msg_count
738: , p_count => x_msg_count
739: , p_data => x_msg_data
740: );
741: END IF;
742: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
743: x_return_status := FND_API.G_RET_STS_ERROR;
744: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
745: IF (x_msg_data IS NULL) THEN
746: FND_MSG_PUB.Count_And_Get
739: , p_data => x_msg_data
740: );
741: END IF;
742: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
743: x_return_status := FND_API.G_RET_STS_ERROR;
744: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
745: IF (x_msg_data IS NULL) THEN
746: FND_MSG_PUB.Count_And_Get
747: ( p_encoded => 'F'
740: );
741: END IF;
742: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
743: x_return_status := FND_API.G_RET_STS_ERROR;
744: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
745: IF (x_msg_data IS NULL) THEN
746: FND_MSG_PUB.Count_And_Get
747: ( p_encoded => 'F'
748: , p_count => x_msg_count
748: , p_count => x_msg_count
749: , p_data => x_msg_data
750: );
751: END IF;
752: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
753: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
754: WHEN NO_DATA_FOUND THEN
755: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
756: IF (x_msg_data IS NOT NULL) THEN
749: , p_data => x_msg_data
750: );
751: END IF;
752: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
753: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
754: WHEN NO_DATA_FOUND THEN
755: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
756: IF (x_msg_data IS NOT NULL) THEN
757: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Dim_Levels_Md ';
751: END IF;
752: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
753: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
754: WHEN NO_DATA_FOUND THEN
755: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
756: IF (x_msg_data IS NOT NULL) THEN
757: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Dim_Levels_Md ';
758: ELSE
759: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Dim_Levels_Md ';
759: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Dim_Levels_Md ';
760: END IF;
761: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
762: WHEN OTHERS THEN
763: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764: IF (x_msg_data IS NOT NULL) THEN
765: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Dim_Levels_Md ';
766: ELSE
767: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Dim_Levels_Md ';
775: --: This procedure populates column information for the Dimension view/table.
776: --: This procedure is part of the Dimension API.
777:
778: procedure Create_Bsc_Sys_Dim_Lvl_Cols(
779: p_commit IN varchar2 -- := FND_API.G_FALSE
780: ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
781: ,x_return_status OUT NOCOPY varchar2
782: ,x_msg_count OUT NOCOPY number
783: ,x_msg_data OUT NOCOPY varchar2
783: ,x_msg_data OUT NOCOPY varchar2
784: ) is
785:
786: begin
787: x_return_status := FND_API.G_RET_STS_SUCCESS;
788: -- Call the private package.procedure.
789: BSC_DIMENSION_LEVELS_PVT.Create_Bsc_Sys_Dim_Lvl_Cols( p_commit
790: ,p_Dim_Level_Rec
791: ,x_return_status
792: ,x_msg_count
793: ,x_msg_data);
794:
795: EXCEPTION
796: WHEN FND_API.G_EXC_ERROR THEN
797: IF (x_msg_data IS NULL) THEN
798: FND_MSG_PUB.Count_And_Get
799: ( p_encoded => 'F'
800: , p_count => x_msg_count
800: , p_count => x_msg_count
801: , p_data => x_msg_data
802: );
803: END IF;
804: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
805: x_return_status := FND_API.G_RET_STS_ERROR;
806: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
807: IF (x_msg_data IS NULL) THEN
808: FND_MSG_PUB.Count_And_Get
801: , p_data => x_msg_data
802: );
803: END IF;
804: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
805: x_return_status := FND_API.G_RET_STS_ERROR;
806: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
807: IF (x_msg_data IS NULL) THEN
808: FND_MSG_PUB.Count_And_Get
809: ( p_encoded => 'F'
802: );
803: END IF;
804: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
805: x_return_status := FND_API.G_RET_STS_ERROR;
806: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
807: IF (x_msg_data IS NULL) THEN
808: FND_MSG_PUB.Count_And_Get
809: ( p_encoded => 'F'
810: , p_count => x_msg_count
810: , p_count => x_msg_count
811: , p_data => x_msg_data
812: );
813: END IF;
814: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
815: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
816: WHEN NO_DATA_FOUND THEN
817: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
818: IF (x_msg_data IS NOT NULL) THEN
811: , p_data => x_msg_data
812: );
813: END IF;
814: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
815: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
816: WHEN NO_DATA_FOUND THEN
817: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
818: IF (x_msg_data IS NOT NULL) THEN
819: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Sys_Dim_Lvl_Cols ';
813: END IF;
814: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
815: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
816: WHEN NO_DATA_FOUND THEN
817: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
818: IF (x_msg_data IS NOT NULL) THEN
819: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Sys_Dim_Lvl_Cols ';
820: ELSE
821: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Sys_Dim_Lvl_Cols ';
821: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Sys_Dim_Lvl_Cols ';
822: END IF;
823: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
824: WHEN OTHERS THEN
825: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
826: IF (x_msg_data IS NOT NULL) THEN
827: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Sys_Dim_Lvl_Cols ';
828: ELSE
829: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Sys_Dim_Lvl_Cols ';
834: /************************************************************************************
835: ************************************************************************************/
836:
837: procedure Retrieve_Bsc_Sys_Dim_Lvl_Cols(
838: p_commit IN varchar2 --:= FND_API.G_FALSE
839: ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
840: ,x_Dim_Level_Rec IN OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
841: ,x_return_status OUT NOCOPY varchar2
842: ,x_msg_count OUT NOCOPY number
843: ,x_msg_data OUT NOCOPY varchar2
844: ) is
845:
846: begin
847: x_return_status := FND_API.G_RET_STS_SUCCESS;
848: BSC_DIMENSION_LEVELS_PVT.Retrieve_Bsc_Sys_Dim_Lvl_Cols( p_commit
849: ,p_Dim_Level_Rec
850: ,x_Dim_Level_Rec
851: ,x_return_status
852: ,x_msg_count
853: ,x_msg_data);
854:
855: EXCEPTION
856: WHEN FND_API.G_EXC_ERROR THEN
857: IF (x_msg_data IS NULL) THEN
858: FND_MSG_PUB.Count_And_Get
859: ( p_encoded => 'F'
860: , p_count => x_msg_count
860: , p_count => x_msg_count
861: , p_data => x_msg_data
862: );
863: END IF;
864: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
865: x_return_status := FND_API.G_RET_STS_ERROR;
866: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
867: IF (x_msg_data IS NULL) THEN
868: FND_MSG_PUB.Count_And_Get
861: , p_data => x_msg_data
862: );
863: END IF;
864: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
865: x_return_status := FND_API.G_RET_STS_ERROR;
866: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
867: IF (x_msg_data IS NULL) THEN
868: FND_MSG_PUB.Count_And_Get
869: ( p_encoded => 'F'
862: );
863: END IF;
864: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
865: x_return_status := FND_API.G_RET_STS_ERROR;
866: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
867: IF (x_msg_data IS NULL) THEN
868: FND_MSG_PUB.Count_And_Get
869: ( p_encoded => 'F'
870: , p_count => x_msg_count
870: , p_count => x_msg_count
871: , p_data => x_msg_data
872: );
873: END IF;
874: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
875: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
876: WHEN NO_DATA_FOUND THEN
877: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
878: IF (x_msg_data IS NOT NULL) THEN
871: , p_data => x_msg_data
872: );
873: END IF;
874: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
875: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
876: WHEN NO_DATA_FOUND THEN
877: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
878: IF (x_msg_data IS NOT NULL) THEN
879: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Sys_Dim_Lvl_Cols ';
873: END IF;
874: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
875: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
876: WHEN NO_DATA_FOUND THEN
877: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
878: IF (x_msg_data IS NOT NULL) THEN
879: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Sys_Dim_Lvl_Cols ';
880: ELSE
881: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Sys_Dim_Lvl_Cols ';
881: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Sys_Dim_Lvl_Cols ';
882: END IF;
883: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
884: WHEN OTHERS THEN
885: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
886: IF (x_msg_data IS NOT NULL) THEN
887: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Sys_Dim_Lvl_Cols ';
888: ELSE
889: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Sys_Dim_Lvl_Cols ';
894: /************************************************************************************
895: ************************************************************************************/
896:
897: procedure Update_Bsc_Sys_Dim_Lvl_Cols(
898: p_commit IN varchar2 --:= FND_API.G_FALSE
899: ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
900: ,x_return_status OUT NOCOPY varchar2
901: ,x_msg_count OUT NOCOPY number
902: ,x_msg_data OUT NOCOPY varchar2
902: ,x_msg_data OUT NOCOPY varchar2
903: ) is
904:
905: begin
906: x_return_status := FND_API.G_RET_STS_SUCCESS;
907: BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Sys_Dim_Lvl_Cols( p_commit
908: ,p_Dim_Level_Rec
909: ,x_return_status
910: ,x_msg_count
911: ,x_msg_data);
912:
913:
914: EXCEPTION
915: WHEN FND_API.G_EXC_ERROR THEN
916: IF (x_msg_data IS NULL) THEN
917: FND_MSG_PUB.Count_And_Get
918: ( p_encoded => 'F'
919: , p_count => x_msg_count
919: , p_count => x_msg_count
920: , p_data => x_msg_data
921: );
922: END IF;
923: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
924: x_return_status := FND_API.G_RET_STS_ERROR;
925: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
926: IF (x_msg_data IS NULL) THEN
927: FND_MSG_PUB.Count_And_Get
920: , p_data => x_msg_data
921: );
922: END IF;
923: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
924: x_return_status := FND_API.G_RET_STS_ERROR;
925: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
926: IF (x_msg_data IS NULL) THEN
927: FND_MSG_PUB.Count_And_Get
928: ( p_encoded => 'F'
921: );
922: END IF;
923: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
924: x_return_status := FND_API.G_RET_STS_ERROR;
925: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
926: IF (x_msg_data IS NULL) THEN
927: FND_MSG_PUB.Count_And_Get
928: ( p_encoded => 'F'
929: , p_count => x_msg_count
929: , p_count => x_msg_count
930: , p_data => x_msg_data
931: );
932: END IF;
933: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
934: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
935: WHEN NO_DATA_FOUND THEN
936: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
937: IF (x_msg_data IS NOT NULL) THEN
930: , p_data => x_msg_data
931: );
932: END IF;
933: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
934: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
935: WHEN NO_DATA_FOUND THEN
936: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
937: IF (x_msg_data IS NOT NULL) THEN
938: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Sys_Dim_Lvl_Cols ';
932: END IF;
933: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
934: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
935: WHEN NO_DATA_FOUND THEN
936: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
937: IF (x_msg_data IS NOT NULL) THEN
938: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Sys_Dim_Lvl_Cols ';
939: ELSE
940: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Sys_Dim_Lvl_Cols ';
940: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Sys_Dim_Lvl_Cols ';
941: END IF;
942: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
943: WHEN OTHERS THEN
944: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
945: IF (x_msg_data IS NOT NULL) THEN
946: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Sys_Dim_Lvl_Cols ';
947: ELSE
948: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Sys_Dim_Lvl_Cols ';
953: /************************************************************************************
954: ************************************************************************************/
955:
956: procedure Delete_Bsc_Sys_Dim_Lvl_Cols(
957: p_commit IN varchar2 --:= FND_API.G_FALSE
958: ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
959: ,x_return_status OUT NOCOPY varchar2
960: ,x_msg_count OUT NOCOPY number
961: ,x_msg_data OUT NOCOPY varchar2
962: ) is
963:
964: -- Procedure to Delete data on Dimension Level column information.
965: begin
966: x_return_status := FND_API.G_RET_STS_SUCCESS;
967: -- Call the private package.procedure.
968: BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Sys_Dim_Lvl_Cols( p_commit
969: ,p_Dim_Level_Rec
970: ,x_return_status
969: ,p_Dim_Level_Rec
970: ,x_return_status
971: ,x_msg_count
972: ,x_msg_data);
973: IF ((x_return_status = FND_API.G_RET_STS_ERROR) OR (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
974: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
975: END IF;
976: EXCEPTION
977: WHEN FND_API.G_EXC_ERROR THEN
970: ,x_return_status
971: ,x_msg_count
972: ,x_msg_data);
973: IF ((x_return_status = FND_API.G_RET_STS_ERROR) OR (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
974: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
975: END IF;
976: EXCEPTION
977: WHEN FND_API.G_EXC_ERROR THEN
978: IF (x_msg_data IS NULL) THEN
973: IF ((x_return_status = FND_API.G_RET_STS_ERROR) OR (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
974: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
975: END IF;
976: EXCEPTION
977: WHEN FND_API.G_EXC_ERROR THEN
978: IF (x_msg_data IS NULL) THEN
979: FND_MSG_PUB.Count_And_Get
980: ( p_encoded => 'F'
981: , p_count => x_msg_count
981: , p_count => x_msg_count
982: , p_data => x_msg_data
983: );
984: END IF;
985: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
986: x_return_status := FND_API.G_RET_STS_ERROR;
987: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
988: IF (x_msg_data IS NULL) THEN
989: FND_MSG_PUB.Count_And_Get
982: , p_data => x_msg_data
983: );
984: END IF;
985: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
986: x_return_status := FND_API.G_RET_STS_ERROR;
987: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
988: IF (x_msg_data IS NULL) THEN
989: FND_MSG_PUB.Count_And_Get
990: ( p_encoded => 'F'
983: );
984: END IF;
985: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
986: x_return_status := FND_API.G_RET_STS_ERROR;
987: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
988: IF (x_msg_data IS NULL) THEN
989: FND_MSG_PUB.Count_And_Get
990: ( p_encoded => 'F'
991: , p_count => x_msg_count
991: , p_count => x_msg_count
992: , p_data => x_msg_data
993: );
994: END IF;
995: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
996: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
997: WHEN NO_DATA_FOUND THEN
998: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
999: IF (x_msg_data IS NOT NULL) THEN
992: , p_data => x_msg_data
993: );
994: END IF;
995: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
996: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
997: WHEN NO_DATA_FOUND THEN
998: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
999: IF (x_msg_data IS NOT NULL) THEN
1000: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Sys_Dim_Lvl_Cols ';
994: END IF;
995: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
996: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
997: WHEN NO_DATA_FOUND THEN
998: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
999: IF (x_msg_data IS NOT NULL) THEN
1000: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Sys_Dim_Lvl_Cols ';
1001: ELSE
1002: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Sys_Dim_Lvl_Cols ';
1002: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Sys_Dim_Lvl_Cols ';
1003: END IF;
1004: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1005: WHEN OTHERS THEN
1006: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1007: IF (x_msg_data IS NOT NULL) THEN
1008: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Sys_Dim_Lvl_Cols ';
1009: ELSE
1010: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Sys_Dim_Lvl_Cols ';
1023: /*---------------------------------------------------------------------------------------
1024: Procedure :
1025: ---------------------------------------------------------------------------------------*/
1026: PROCEDURE Create_Dim_Level_Relation(
1027: p_commit IN varchar2 --:= FND_API.G_FALSE
1028: ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1029: ,x_return_status OUT NOCOPY varchar2
1030: ,x_msg_count OUT NOCOPY number
1031: ,x_msg_data OUT NOCOPY varchar2
1035: v_Bsc_Pmf_Dim_Rec BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type;
1036:
1037:
1038: BEGIN
1039: x_return_status := FND_API.G_RET_STS_SUCCESS;
1040: v_Dim_Level_Rec := p_Dim_Level_Rec;
1041:
1042: -- if the child is a PMF Level Check for Import the Level --
1043: if p_Dim_Level_Rec.Bsc_Source <> 'BSC' then
1073: end if;
1074:
1075: end if;
1076:
1077: x_return_status := FND_API.G_RET_STS_SUCCESS;
1078: EXCEPTION
1079: WHEN FND_API.G_EXC_ERROR THEN
1080: IF (x_msg_data IS NULL) THEN
1081: FND_MSG_PUB.Count_And_Get
1075: end if;
1076:
1077: x_return_status := FND_API.G_RET_STS_SUCCESS;
1078: EXCEPTION
1079: WHEN FND_API.G_EXC_ERROR THEN
1080: IF (x_msg_data IS NULL) THEN
1081: FND_MSG_PUB.Count_And_Get
1082: ( p_encoded => 'F'
1083: , p_count => x_msg_count
1083: , p_count => x_msg_count
1084: , p_data => x_msg_data
1085: );
1086: END IF;
1087: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1088: x_return_status := FND_API.G_RET_STS_ERROR;
1089: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1090: IF (x_msg_data IS NULL) THEN
1091: FND_MSG_PUB.Count_And_Get
1084: , p_data => x_msg_data
1085: );
1086: END IF;
1087: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1088: x_return_status := FND_API.G_RET_STS_ERROR;
1089: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1090: IF (x_msg_data IS NULL) THEN
1091: FND_MSG_PUB.Count_And_Get
1092: ( p_encoded => 'F'
1085: );
1086: END IF;
1087: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1088: x_return_status := FND_API.G_RET_STS_ERROR;
1089: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1090: IF (x_msg_data IS NULL) THEN
1091: FND_MSG_PUB.Count_And_Get
1092: ( p_encoded => 'F'
1093: , p_count => x_msg_count
1093: , p_count => x_msg_count
1094: , p_data => x_msg_data
1095: );
1096: END IF;
1097: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1098: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1099: WHEN NO_DATA_FOUND THEN
1100: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1101: IF (x_msg_data IS NOT NULL) THEN
1094: , p_data => x_msg_data
1095: );
1096: END IF;
1097: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1098: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1099: WHEN NO_DATA_FOUND THEN
1100: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1101: IF (x_msg_data IS NOT NULL) THEN
1102: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level_Relation ';
1096: END IF;
1097: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1098: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1099: WHEN NO_DATA_FOUND THEN
1100: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1101: IF (x_msg_data IS NOT NULL) THEN
1102: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level_Relation ';
1103: ELSE
1104: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level_Relation ';
1104: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level_Relation ';
1105: END IF;
1106: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1107: WHEN OTHERS THEN
1108: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1109: IF (x_msg_data IS NOT NULL) THEN
1110: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level_Relation ';
1111: ELSE
1112: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level_Relation ';
1117: /*---------------------------------------------------------------------------------------
1118: Procedure :
1119: ---------------------------------------------------------------------------------------*/
1120: PROCEDURE Delete_Dim_Level_Relation(
1121: p_commit IN varchar2 --:= FND_API.G_FALSE
1122: ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1123: ,x_return_status OUT NOCOPY varchar2
1124: ,x_msg_count OUT NOCOPY number
1125: ,x_msg_data OUT NOCOPY varchar2
1129: v_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
1130:
1131:
1132: BEGIN
1133: x_return_status := FND_API.G_RET_STS_SUCCESS;
1134: v_Dim_Level_Rec := p_Dim_Level_Rec;
1135:
1136: -- Check Bsc_Level_Id if the child is a PMF --
1137: IF (v_Dim_Level_Rec.Bsc_Level_Id IS NULL) THEN
1163:
1164: --DBMS_OUTPUT.PUT_LINE('End Delete_Dim_Level_Relation ' );
1165:
1166: EXCEPTION
1167: WHEN FND_API.G_EXC_ERROR THEN
1168: IF (x_msg_data IS NULL) THEN
1169: FND_MSG_PUB.Count_And_Get
1170: ( p_encoded => 'F'
1171: , p_count => x_msg_count
1171: , p_count => x_msg_count
1172: , p_data => x_msg_data
1173: );
1174: END IF;
1175: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1176: x_return_status := FND_API.G_RET_STS_ERROR;
1177: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1178: IF (x_msg_data IS NULL) THEN
1179: FND_MSG_PUB.Count_And_Get
1172: , p_data => x_msg_data
1173: );
1174: END IF;
1175: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1176: x_return_status := FND_API.G_RET_STS_ERROR;
1177: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1178: IF (x_msg_data IS NULL) THEN
1179: FND_MSG_PUB.Count_And_Get
1180: ( p_encoded => 'F'
1173: );
1174: END IF;
1175: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1176: x_return_status := FND_API.G_RET_STS_ERROR;
1177: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1178: IF (x_msg_data IS NULL) THEN
1179: FND_MSG_PUB.Count_And_Get
1180: ( p_encoded => 'F'
1181: , p_count => x_msg_count
1181: , p_count => x_msg_count
1182: , p_data => x_msg_data
1183: );
1184: END IF;
1185: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1186: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1187: WHEN NO_DATA_FOUND THEN
1188: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1189: IF (x_msg_data IS NOT NULL) THEN
1182: , p_data => x_msg_data
1183: );
1184: END IF;
1185: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1186: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1187: WHEN NO_DATA_FOUND THEN
1188: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1189: IF (x_msg_data IS NOT NULL) THEN
1190: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level_Relation ';
1184: END IF;
1185: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1186: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1187: WHEN NO_DATA_FOUND THEN
1188: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1189: IF (x_msg_data IS NOT NULL) THEN
1190: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level_Relation ';
1191: ELSE
1192: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level_Relation ';
1192: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level_Relation ';
1193: END IF;
1194: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1195: WHEN OTHERS THEN
1196: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1197: IF (x_msg_data IS NOT NULL) THEN
1198: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level_Relation ';
1199: ELSE
1200: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level_Relation ';
1205: Is_Valid_Relationship :
1206: Return tre if the future relation is valid
1207: --------------------------------------------------------------------------------------*/
1208: FUNCTION Is_Valid_Relationship(
1209: p_commit IN varchar2 --:= FND_API.G_FALSE
1210: ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1211: ,x_return_status OUT NOCOPY varchar2
1212: ,x_msg_count OUT NOCOPY number
1213: ,x_msg_data OUT NOCOPY varchar2
1216: v_Temp BOOLEAN;
1217: v_msg varchar2(4000);
1218:
1219: BEGIN
1220: x_return_status := FND_API.G_RET_STS_SUCCESS;
1221: FND_MSG_PUB.Initialize;
1222: v_Temp := BSC_DIMENSION_LEVELS_PVT.Evaluate_Circular_Relationship
1223: ( p_Dim_Level_Rec.Bsc_Level_Id
1224: ,p_Dim_Level_Rec.Bsc_Parent_Level_Id
1233: if x_return_status = 'SAME' then
1234: FND_MESSAGE.SET_NAME('BSC','BSC_SAME_DIM_LEVEL_REL');
1235: FND_MESSAGE.SET_TOKEN('LEVEL_CHILD', BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Name(p_Dim_Level_Rec.Bsc_Level_Id) );
1236: FND_MSG_PUB.ADD;
1237: RAISE FND_API.G_EXC_ERROR;
1238: else
1239: FND_MESSAGE.SET_NAME('BSC','BSC_CIRCULAR_DIM_LEVEL_REL');
1240: FND_MESSAGE.SET_TOKEN('LEVEL_CHILD', BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Name(p_Dim_Level_Rec.Bsc_Level_Id) );
1241: FND_MESSAGE.SET_TOKEN('LEVEL_PARENT', BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Name(p_Dim_Level_Rec.Bsc_Parent_Level_Id));
1242: FND_MSG_PUB.ADD;
1243: --FND_MESSAGE.SET_NAME('BSC','BSC_EXISTING_DEPENDENCIES');
1244: --FND_MESSAGE.SET_TOKEN('DEPENDENCY', SUBSTR(v_msg, 1, 220)) ;
1245: --FND_MSG_PUB.ADD;
1246: RAISE FND_API.G_EXC_ERROR;
1247: end if;
1248: end if;
1249: x_return_status := FND_API.G_RET_STS_SUCCESS;
1250: RETURN v_Temp;
1245: --FND_MSG_PUB.ADD;
1246: RAISE FND_API.G_EXC_ERROR;
1247: end if;
1248: end if;
1249: x_return_status := FND_API.G_RET_STS_SUCCESS;
1250: RETURN v_Temp;
1251: EXCEPTION
1252: WHEN FND_API.G_EXC_ERROR THEN
1253: IF (x_msg_data IS NULL) THEN
1248: end if;
1249: x_return_status := FND_API.G_RET_STS_SUCCESS;
1250: RETURN v_Temp;
1251: EXCEPTION
1252: WHEN FND_API.G_EXC_ERROR THEN
1253: IF (x_msg_data IS NULL) THEN
1254: FND_MSG_PUB.Count_And_Get
1255: ( p_encoded => 'F'
1256: , p_count => x_msg_count
1256: , p_count => x_msg_count
1257: , p_data => x_msg_data
1258: );
1259: END IF;
1260: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1261: x_return_status := FND_API.G_RET_STS_ERROR;
1262: RETURN FALSE;
1263: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1264: IF (x_msg_data IS NULL) THEN
1257: , p_data => x_msg_data
1258: );
1259: END IF;
1260: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1261: x_return_status := FND_API.G_RET_STS_ERROR;
1262: RETURN FALSE;
1263: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1264: IF (x_msg_data IS NULL) THEN
1265: FND_MSG_PUB.Count_And_Get
1259: END IF;
1260: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1261: x_return_status := FND_API.G_RET_STS_ERROR;
1262: RETURN FALSE;
1263: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1264: IF (x_msg_data IS NULL) THEN
1265: FND_MSG_PUB.Count_And_Get
1266: ( p_encoded => 'F'
1267: , p_count => x_msg_count
1267: , p_count => x_msg_count
1268: , p_data => x_msg_data
1269: );
1270: END IF;
1271: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1272: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1273: RETURN FALSE;
1274: WHEN NO_DATA_FOUND THEN
1275: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1268: , p_data => x_msg_data
1269: );
1270: END IF;
1271: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1272: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1273: RETURN FALSE;
1274: WHEN NO_DATA_FOUND THEN
1275: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1276: IF (x_msg_data IS NOT NULL) THEN
1271: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1272: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1273: RETURN FALSE;
1274: WHEN NO_DATA_FOUND THEN
1275: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1276: IF (x_msg_data IS NOT NULL) THEN
1277: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Is_Valid_Relationship ';
1278: ELSE
1279: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Is_Valid_Relationship ';
1280: END IF;
1281: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1282: RETURN FALSE;
1283: WHEN OTHERS THEN
1284: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1285: IF (x_msg_data IS NOT NULL) THEN
1286: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Is_Valid_Relationship ';
1287: ELSE
1288: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Is_Valid_Relationship ';
1295: -- is_dependent :
1296: -- Return Out NOCOPY parameter x_return_value = 'TRUE' or 'FALSE'
1297: --------------------------------------------------------------------------------------
1298: PROCEDURE is_dependent(
1299: p_commit IN varchar2 --:= FND_API.G_FALSE
1300: ,p_child_dim_level_short_name IN varchar2
1301: ,p_parent_dim_level_short_name IN varchar2
1302: ,x_return_value OUT NOCOPY varchar2
1303: ,x_return_status OUT NOCOPY varchar2 /* return FND_API.G_FALSE or FND_API.G_TRUE */
1299: p_commit IN varchar2 --:= FND_API.G_FALSE
1300: ,p_child_dim_level_short_name IN varchar2
1301: ,p_parent_dim_level_short_name IN varchar2
1302: ,x_return_value OUT NOCOPY varchar2
1303: ,x_return_status OUT NOCOPY varchar2 /* return FND_API.G_FALSE or FND_API.G_TRUE */
1304: ,x_msg_count OUT NOCOPY number
1305: ,x_msg_data OUT NOCOPY varchar2
1306: ) IS
1307:
1313: v_parent varchar2(50);
1314: v_count number;
1315:
1316: BEGIN
1317: x_return_status := FND_API.G_RET_STS_SUCCESS;
1318: x_return_value := FND_API.G_FALSE;
1319:
1320: Select COUNT(1)
1321: into v_count
1314: v_count number;
1315:
1316: BEGIN
1317: x_return_status := FND_API.G_RET_STS_SUCCESS;
1318: x_return_value := FND_API.G_FALSE;
1319:
1320: Select COUNT(1)
1321: into v_count
1322: from BSC_SYS_DIM_LEVEL_RELS_V
1323: where SHORT_NAME = UPPER(p_child_dim_level_short_name)
1324: and PARENT_SHORT_NAME = UPPER(p_parent_dim_level_short_name);
1325:
1326: if v_count <> 0 then
1327: x_return_value := FND_API.G_TRUE;
1328: else
1329: OPEN c_parents;
1330: LOOP
1331: FETCH c_parents INTO v_parent;
1335: ,p_parent_dim_level_short_name
1336: ,x_return_value
1337: ,x_return_status ,x_msg_count ,x_msg_data
1338: );
1339: if x_return_value = FND_API.G_TRUE then
1340: EXIT;
1341: end if;
1342: END LOOP;
1343: CLOSE c_parents;
1345:
1346: --DBMS_OUTPUT.PUT_LINE('end is_dependent ' );
1347:
1348: EXCEPTION
1349: WHEN FND_API.G_EXC_ERROR THEN
1350: IF (x_msg_data IS NULL) THEN
1351: FND_MSG_PUB.Count_And_Get
1352: ( p_encoded => 'F'
1353: , p_count => x_msg_count
1353: , p_count => x_msg_count
1354: , p_data => x_msg_data
1355: );
1356: END IF;
1357: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1358: x_return_status := FND_API.G_RET_STS_ERROR;
1359: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1360: IF (x_msg_data IS NULL) THEN
1361: FND_MSG_PUB.Count_And_Get
1354: , p_data => x_msg_data
1355: );
1356: END IF;
1357: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1358: x_return_status := FND_API.G_RET_STS_ERROR;
1359: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1360: IF (x_msg_data IS NULL) THEN
1361: FND_MSG_PUB.Count_And_Get
1362: ( p_encoded => 'F'
1355: );
1356: END IF;
1357: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1358: x_return_status := FND_API.G_RET_STS_ERROR;
1359: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1360: IF (x_msg_data IS NULL) THEN
1361: FND_MSG_PUB.Count_And_Get
1362: ( p_encoded => 'F'
1363: , p_count => x_msg_count
1363: , p_count => x_msg_count
1364: , p_data => x_msg_data
1365: );
1366: END IF;
1367: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1368: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1369: WHEN NO_DATA_FOUND THEN
1370: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1371: IF (x_msg_data IS NOT NULL) THEN
1364: , p_data => x_msg_data
1365: );
1366: END IF;
1367: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1368: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1369: WHEN NO_DATA_FOUND THEN
1370: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1371: IF (x_msg_data IS NOT NULL) THEN
1372: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.is_dependent ';
1366: END IF;
1367: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1368: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1369: WHEN NO_DATA_FOUND THEN
1370: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1371: IF (x_msg_data IS NOT NULL) THEN
1372: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.is_dependent ';
1373: ELSE
1374: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.is_dependent ';
1374: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.is_dependent ';
1375: END IF;
1376: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1377: WHEN OTHERS THEN
1378: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1379: IF (x_msg_data IS NOT NULL) THEN
1380: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.is_dependent ';
1381: ELSE
1382: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.is_dependent ';
1389: -- Return out NOCOPY parameter p_parent_dim_level_short_names with the parent
1390: -- short names separated by commas ","
1391: --------------------------------------------------------------------------------------
1392: PROCEDURE get_parent_dimension_levels(
1393: p_commit IN varchar2 --:= FND_API.G_FALSE
1394: ,p_child_dim_level_short_name IN varchar2
1395: ,p_parent_dim_level_short_names OUT NOCOPY varchar2
1396: ,x_return_status OUT NOCOPY varchar2 /* return FND_API.G_FALSE or FND_API.G_TRUE */
1397: ,x_msg_count OUT NOCOPY number
1392: PROCEDURE get_parent_dimension_levels(
1393: p_commit IN varchar2 --:= FND_API.G_FALSE
1394: ,p_child_dim_level_short_name IN varchar2
1395: ,p_parent_dim_level_short_names OUT NOCOPY varchar2
1396: ,x_return_status OUT NOCOPY varchar2 /* return FND_API.G_FALSE or FND_API.G_TRUE */
1397: ,x_msg_count OUT NOCOPY number
1398: ,x_msg_data OUT NOCOPY varchar2
1399: ) IS
1400:
1406: v_parent varchar2(50);
1407:
1408:
1409: BEGIN
1410: x_return_status := FND_API.G_RET_STS_SUCCESS;
1411: --DBMS_OUTPUT.PUT_LINE('Begin get_parent_dimension_levels' );
1412:
1413: p_parent_dim_level_short_names := null;
1414: OPEN c_parents;
1427:
1428: --DBMS_OUTPUT.PUT_LINE('end get_parent_dimension_levels' );
1429:
1430: EXCEPTION
1431: WHEN FND_API.G_EXC_ERROR THEN
1432: IF (x_msg_data IS NULL) THEN
1433: FND_MSG_PUB.Count_And_Get
1434: ( p_encoded => 'F'
1435: , p_count => x_msg_count
1435: , p_count => x_msg_count
1436: , p_data => x_msg_data
1437: );
1438: END IF;
1439: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1440: x_return_status := FND_API.G_RET_STS_ERROR;
1441: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1442: IF (x_msg_data IS NULL) THEN
1443: FND_MSG_PUB.Count_And_Get
1436: , p_data => x_msg_data
1437: );
1438: END IF;
1439: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1440: x_return_status := FND_API.G_RET_STS_ERROR;
1441: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1442: IF (x_msg_data IS NULL) THEN
1443: FND_MSG_PUB.Count_And_Get
1444: ( p_encoded => 'F'
1437: );
1438: END IF;
1439: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1440: x_return_status := FND_API.G_RET_STS_ERROR;
1441: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1442: IF (x_msg_data IS NULL) THEN
1443: FND_MSG_PUB.Count_And_Get
1444: ( p_encoded => 'F'
1445: , p_count => x_msg_count
1445: , p_count => x_msg_count
1446: , p_data => x_msg_data
1447: );
1448: END IF;
1449: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1450: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1451: WHEN NO_DATA_FOUND THEN
1452: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1453: IF (x_msg_data IS NOT NULL) THEN
1446: , p_data => x_msg_data
1447: );
1448: END IF;
1449: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1450: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1451: WHEN NO_DATA_FOUND THEN
1452: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1453: IF (x_msg_data IS NOT NULL) THEN
1454: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.get_parent_dimension_levels ';
1448: END IF;
1449: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1450: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1451: WHEN NO_DATA_FOUND THEN
1452: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1453: IF (x_msg_data IS NOT NULL) THEN
1454: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.get_parent_dimension_levels ';
1455: ELSE
1456: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.get_parent_dimension_levels ';
1456: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.get_parent_dimension_levels ';
1457: END IF;
1458: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1459: WHEN OTHERS THEN
1460: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1461: IF (x_msg_data IS NOT NULL) THEN
1462: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.get_parent_dimension_levels ';
1463: ELSE
1464: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.get_parent_dimension_levels ';
1470: -- Return out NOCOPY parameter p_parent_dim_level_short_names with the parent
1471: -- short names separated by commas ","
1472: --------------------------------------------------------------------------------------
1473: PROCEDURE get_child_dimension_levels(
1474: p_commit IN varchar2 --:= FND_API.G_FALSE
1475: ,p_parent_dim_level_short_name IN varchar2
1476: ,p_child_dim_level_short_names OUT NOCOPY varchar2
1477: ,x_return_status OUT NOCOPY varchar2 /* return FND_API.G_FALSE or FND_API.G_TRUE */
1478: ,x_msg_count OUT NOCOPY number
1473: PROCEDURE get_child_dimension_levels(
1474: p_commit IN varchar2 --:= FND_API.G_FALSE
1475: ,p_parent_dim_level_short_name IN varchar2
1476: ,p_child_dim_level_short_names OUT NOCOPY varchar2
1477: ,x_return_status OUT NOCOPY varchar2 /* return FND_API.G_FALSE or FND_API.G_TRUE */
1478: ,x_msg_count OUT NOCOPY number
1479: ,x_msg_data OUT NOCOPY varchar2
1480: ) IS
1481: CURSOR c_childs IS
1486: v_child varchar2(50);
1487:
1488:
1489: BEGIN
1490: x_return_status := FND_API.G_RET_STS_SUCCESS;
1491: --DBMS_OUTPUT.PUT_LINE('Begin get_child_dimension_levels' );
1492:
1493: p_child_dim_level_short_names := null;
1494: OPEN c_childs;
1507:
1508: --DBMS_OUTPUT.PUT_LINE('end get_child_dimension_levels' );
1509:
1510: EXCEPTION
1511: WHEN FND_API.G_EXC_ERROR THEN
1512: IF (x_msg_data IS NULL) THEN
1513: FND_MSG_PUB.Count_And_Get
1514: ( p_encoded => 'F'
1515: , p_count => x_msg_count
1515: , p_count => x_msg_count
1516: , p_data => x_msg_data
1517: );
1518: END IF;
1519: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1520: x_return_status := FND_API.G_RET_STS_ERROR;
1521: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1522: IF (x_msg_data IS NULL) THEN
1523: FND_MSG_PUB.Count_And_Get
1516: , p_data => x_msg_data
1517: );
1518: END IF;
1519: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1520: x_return_status := FND_API.G_RET_STS_ERROR;
1521: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1522: IF (x_msg_data IS NULL) THEN
1523: FND_MSG_PUB.Count_And_Get
1524: ( p_encoded => 'F'
1517: );
1518: END IF;
1519: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1520: x_return_status := FND_API.G_RET_STS_ERROR;
1521: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1522: IF (x_msg_data IS NULL) THEN
1523: FND_MSG_PUB.Count_And_Get
1524: ( p_encoded => 'F'
1525: , p_count => x_msg_count
1525: , p_count => x_msg_count
1526: , p_data => x_msg_data
1527: );
1528: END IF;
1529: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1530: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1531: WHEN NO_DATA_FOUND THEN
1532: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1533: IF (x_msg_data IS NOT NULL) THEN
1526: , p_data => x_msg_data
1527: );
1528: END IF;
1529: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1530: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1531: WHEN NO_DATA_FOUND THEN
1532: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1533: IF (x_msg_data IS NOT NULL) THEN
1534: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.get_child_dimension_levels ';
1528: END IF;
1529: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1530: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1531: WHEN NO_DATA_FOUND THEN
1532: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1533: IF (x_msg_data IS NOT NULL) THEN
1534: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.get_child_dimension_levels ';
1535: ELSE
1536: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.get_child_dimension_levels ';
1536: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.get_child_dimension_levels ';
1537: END IF;
1538: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1539: WHEN OTHERS THEN
1540: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1541: IF (x_msg_data IS NOT NULL) THEN
1542: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.get_child_dimension_levels ';
1543: ELSE
1544: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.get_child_dimension_levels ';
1551: -- is_Child_Parent :
1552: -- Check to see if the passed Dim Level Ids form a Parent child relationship.
1553: --
1554: -- Added by ADRAO for Enh#2901823
1555: -- Values returned wouid be in x_return_status (FND_API.G_FALSE or FND_API.G_TRUE)
1556: --------------------------------------------------------------------------------------
1557: FUNCTION is_Child_Parent
1558: (
1559: p_child_dim_level_short_name IN VARCHAR2
1564: ) RETURN BOOLEAN IS
1565:
1566: l_count NUMBER;
1567: BEGIN
1568: x_return_status := FND_API.G_RET_STS_SUCCESS;
1569:
1570: SELECT COUNT(DIM_LEVEL_ID)
1571: INTO l_count
1572: FROM BSC_SYS_DIM_LEVEL_RELS_V
1579: RETURN FALSE;
1580: END IF;
1581: EXCEPTION
1582: WHEN OTHERS THEN
1583: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1584: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1585: ,p_data => x_msg_data);
1586: RETURN FALSE;
1587: END is_Child_Parent;
1599: , x_msg_count OUT NOCOPY NUMBER
1600: , x_msg_data OUT NOCOPY VARCHAR2
1601: ) IS
1602: BEGIN
1603: x_return_status := FND_API.G_RET_STS_SUCCESS;
1604: BSC_DIMENSION_LEVELS_PVT.Retrieve_Relationship
1605: (
1606: p_Dim_Level_Rec => p_Dim_Level_Rec
1607: , x_Dim_Level_Rec => x_Dim_Level_Rec
1609: , x_msg_count => x_msg_count
1610: , x_msg_data => x_msg_data
1611: );
1612: EXCEPTION
1613: WHEN FND_API.G_EXC_ERROR THEN
1614: IF (x_msg_data IS NULL) THEN
1615: FND_MSG_PUB.Count_And_Get
1616: ( p_encoded => 'F'
1617: , p_count => x_msg_count
1617: , p_count => x_msg_count
1618: , p_data => x_msg_data
1619: );
1620: END IF;
1621: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1622: x_return_status := FND_API.G_RET_STS_ERROR;
1623: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1624: IF (x_msg_data IS NULL) THEN
1625: FND_MSG_PUB.Count_And_Get
1618: , p_data => x_msg_data
1619: );
1620: END IF;
1621: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1622: x_return_status := FND_API.G_RET_STS_ERROR;
1623: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1624: IF (x_msg_data IS NULL) THEN
1625: FND_MSG_PUB.Count_And_Get
1626: ( p_encoded => 'F'
1619: );
1620: END IF;
1621: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1622: x_return_status := FND_API.G_RET_STS_ERROR;
1623: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1624: IF (x_msg_data IS NULL) THEN
1625: FND_MSG_PUB.Count_And_Get
1626: ( p_encoded => 'F'
1627: , p_count => x_msg_count
1627: , p_count => x_msg_count
1628: , p_data => x_msg_data
1629: );
1630: END IF;
1631: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1632: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1633: WHEN NO_DATA_FOUND THEN
1634: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1635: IF (x_msg_data IS NOT NULL) THEN
1628: , p_data => x_msg_data
1629: );
1630: END IF;
1631: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1632: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1633: WHEN NO_DATA_FOUND THEN
1634: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1635: IF (x_msg_data IS NOT NULL) THEN
1636: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Relationship ';
1630: END IF;
1631: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1632: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1633: WHEN NO_DATA_FOUND THEN
1634: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1635: IF (x_msg_data IS NOT NULL) THEN
1636: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Relationship ';
1637: ELSE
1638: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Relationship ';
1638: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Relationship ';
1639: END IF;
1640: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1641: WHEN OTHERS THEN
1642: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1643: IF (x_msg_data IS NOT NULL) THEN
1644: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Relationship ';
1645: ELSE
1646: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Relationship ';
1658: , x_msg_count OUT NOCOPY NUMBER
1659: , x_msg_data OUT NOCOPY VARCHAR2
1660: ) IS
1661: BEGIN
1662: x_return_status := FND_API.G_RET_STS_SUCCESS;
1663: BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level
1664: (
1665: p_commit => FND_API.G_FALSE
1666: ,p_Dim_Level_Rec => p_Dim_Level_Rec
1661: BEGIN
1662: x_return_status := FND_API.G_RET_STS_SUCCESS;
1663: BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level
1664: (
1665: p_commit => FND_API.G_FALSE
1666: ,p_Dim_Level_Rec => p_Dim_Level_Rec
1667: ,x_return_status => x_return_status
1668: ,x_msg_count => x_msg_count
1669: ,x_msg_data => x_msg_data
1668: ,x_msg_count => x_msg_count
1669: ,x_msg_data => x_msg_data
1670: );
1671: EXCEPTION
1672: WHEN FND_API.G_EXC_ERROR THEN
1673: IF (x_msg_data IS NULL) THEN
1674: FND_MSG_PUB.Count_And_Get
1675: ( p_encoded => 'F'
1676: , p_count => x_msg_count
1676: , p_count => x_msg_count
1677: , p_data => x_msg_data
1678: );
1679: END IF;
1680: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1681: x_return_status := FND_API.G_RET_STS_ERROR;
1682: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1683: IF (x_msg_data IS NULL) THEN
1684: FND_MSG_PUB.Count_And_Get
1677: , p_data => x_msg_data
1678: );
1679: END IF;
1680: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1681: x_return_status := FND_API.G_RET_STS_ERROR;
1682: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1683: IF (x_msg_data IS NULL) THEN
1684: FND_MSG_PUB.Count_And_Get
1685: ( p_encoded => 'F'
1678: );
1679: END IF;
1680: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1681: x_return_status := FND_API.G_RET_STS_ERROR;
1682: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1683: IF (x_msg_data IS NULL) THEN
1684: FND_MSG_PUB.Count_And_Get
1685: ( p_encoded => 'F'
1686: , p_count => x_msg_count
1686: , p_count => x_msg_count
1687: , p_data => x_msg_data
1688: );
1689: END IF;
1690: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1691: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1692: WHEN NO_DATA_FOUND THEN
1693: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1694: IF (x_msg_data IS NOT NULL) THEN
1687: , p_data => x_msg_data
1688: );
1689: END IF;
1690: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1691: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1692: WHEN NO_DATA_FOUND THEN
1693: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1694: IF (x_msg_data IS NOT NULL) THEN
1695: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Drop_Dim_Level_Tabs ';
1689: END IF;
1690: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1691: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1692: WHEN NO_DATA_FOUND THEN
1693: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1694: IF (x_msg_data IS NOT NULL) THEN
1695: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Drop_Dim_Level_Tabs ';
1696: ELSE
1697: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
1697: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
1698: END IF;
1699: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1700: WHEN OTHERS THEN
1701: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1702: IF (x_msg_data IS NOT NULL) THEN
1703: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Drop_Dim_Level_Tabs ';
1704: ELSE
1705: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Drop_Dim_Level_Tabs ';
1707: --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1708: end Drop_Dim_Level_Tabs;
1709: --=============================================================================
1710: PROCEDURE Translate_Dimension_Level (
1711: p_Commit IN VARCHAR2 --:= FND_API.G_FALSE
1712: ,p_Bsc_Pmf_Dim_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type
1713: ,p_Bsc_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1714: ,x_return_status OUT NOCOPY VARCHAR2
1715: ,x_msg_count OUT NOCOPY NUMBER
1716: ,x_msg_data OUT NOCOPY VARCHAR2
1717: )
1718: IS
1719: BEGIN
1720: x_return_status := FND_API.G_RET_STS_SUCCESS;
1721: BSC_DIMENSION_LEVELS_PVT.Translate_Dimension_Level
1722: ( p_commit => p_Commit
1723: ,p_Bsc_Pmf_Dim_Rec => p_Bsc_Pmf_Dim_Rec
1724: ,p_Bsc_Dim_Level_Rec => p_Bsc_Dim_Level_Rec
1726: ,x_msg_count => x_msg_count
1727: ,x_msg_data => x_msg_data
1728: );
1729: EXCEPTION
1730: WHEN FND_API.G_EXC_ERROR THEN
1731: IF (x_msg_data IS NULL) THEN
1732: FND_MSG_PUB.Count_And_Get
1733: ( p_encoded => 'F'
1734: , p_count => x_msg_count
1734: , p_count => x_msg_count
1735: , p_data => x_msg_data
1736: );
1737: END IF;
1738: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1739: x_return_status := FND_API.G_RET_STS_ERROR;
1740: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1741: IF (x_msg_data IS NULL) THEN
1742: FND_MSG_PUB.Count_And_Get
1735: , p_data => x_msg_data
1736: );
1737: END IF;
1738: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1739: x_return_status := FND_API.G_RET_STS_ERROR;
1740: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1741: IF (x_msg_data IS NULL) THEN
1742: FND_MSG_PUB.Count_And_Get
1743: ( p_encoded => 'F'
1736: );
1737: END IF;
1738: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1739: x_return_status := FND_API.G_RET_STS_ERROR;
1740: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1741: IF (x_msg_data IS NULL) THEN
1742: FND_MSG_PUB.Count_And_Get
1743: ( p_encoded => 'F'
1744: , p_count => x_msg_count
1744: , p_count => x_msg_count
1745: , p_data => x_msg_data
1746: );
1747: END IF;
1748: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1749: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1750: WHEN NO_DATA_FOUND THEN
1751: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1752: IF (x_msg_data IS NOT NULL) THEN
1745: , p_data => x_msg_data
1746: );
1747: END IF;
1748: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1749: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1750: WHEN NO_DATA_FOUND THEN
1751: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1752: IF (x_msg_data IS NOT NULL) THEN
1753: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Translate_Dimension_Level ';
1747: END IF;
1748: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1749: --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1750: WHEN NO_DATA_FOUND THEN
1751: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1752: IF (x_msg_data IS NOT NULL) THEN
1753: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Translate_Dimension_Level ';
1754: ELSE
1755: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Translate_Dimension_Level ';
1755: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Translate_Dimension_Level ';
1756: END IF;
1757: --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1758: WHEN OTHERS THEN
1759: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1760: IF (x_msg_data IS NOT NULL) THEN
1761: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Translate_Dimension_Level ';
1762: ELSE
1763: x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Translate_Dimension_Level ';
1769: /*
1770: * API called from PMF for "All" enhancement
1771: */
1772: PROCEDURE load_dimension_level(
1773: p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1774: ,p_dim_level_rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1775: ,x_return_status OUT NOCOPY VARCHAR2
1776: ,x_msg_count OUT NOCOPY NUMBER
1777: ,x_msg_data OUT NOCOPY VARCHAR2
1783: l_table_type BSC_SYS_DIM_LEVELS_B.Table_Type%TYPE;
1784: l_level_pk_col BSC_SYS_DIM_LEVELS_B.Level_Pk_Col%TYPE;
1785:
1786: BEGIN
1787: x_return_status := FND_API.G_RET_STS_SUCCESS;
1788:
1789: l_bsc_dim_rec := p_dim_level_rec;
1790: BEGIN
1791: SELECT dim_level_id
1825:
1826: END IF;
1827:
1828: EXCEPTION
1829: WHEN FND_API.G_EXC_ERROR THEN
1830: x_return_status := FND_API.G_RET_STS_ERROR;
1831: FND_MSG_PUB.Count_And_Get(
1832: p_encoded => 'F'
1833: ,p_count => x_msg_count
1826: END IF;
1827:
1828: EXCEPTION
1829: WHEN FND_API.G_EXC_ERROR THEN
1830: x_return_status := FND_API.G_RET_STS_ERROR;
1831: FND_MSG_PUB.Count_And_Get(
1832: p_encoded => 'F'
1833: ,p_count => x_msg_count
1834: ,p_data => x_msg_data
1832: p_encoded => 'F'
1833: ,p_count => x_msg_count
1834: ,p_data => x_msg_data
1835: );
1836: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1837: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1838: FND_MSG_PUB.Count_And_Get(
1839: p_encoded => 'F'
1840: ,p_count => x_msg_count
1833: ,p_count => x_msg_count
1834: ,p_data => x_msg_data
1835: );
1836: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1837: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1838: FND_MSG_PUB.Count_And_Get(
1839: p_encoded => 'F'
1840: ,p_count => x_msg_count
1841: ,p_data => x_msg_data
1840: ,p_count => x_msg_count
1841: ,p_data => x_msg_data
1842: );
1843: WHEN OTHERS THEN
1844: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1845: IF (x_msg_data IS NOT NULL) THEN
1846: x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.load_dimension_level ';
1847: ELSE
1848: x_msg_data := 'BSC_DIMENSION_LEVELS_PUB.load_dimension_level '||SQLERRM;
1871: BEGIN
1872:
1873: BSC_DIMENSION_LEVELS_PVT.Trans_DimObj_By_Given_Lang
1874: (
1875: p_commit => FND_API.G_FALSE
1876: , p_dim_level_rec => p_dim_level_rec
1877: , x_return_status => x_return_status
1878: , x_msg_count => x_msg_count
1879: , x_msg_data => x_msg_data
1880: );
1881:
1882:
1883: EXCEPTION
1884: WHEN FND_API.G_EXC_ERROR THEN
1885: x_return_status := FND_API.G_RET_STS_ERROR;
1886: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1887: ,p_data => x_msg_data);
1888: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1881:
1882:
1883: EXCEPTION
1884: WHEN FND_API.G_EXC_ERROR THEN
1885: x_return_status := FND_API.G_RET_STS_ERROR;
1886: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1887: ,p_data => x_msg_data);
1888: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1889: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1884: WHEN FND_API.G_EXC_ERROR THEN
1885: x_return_status := FND_API.G_RET_STS_ERROR;
1886: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1887: ,p_data => x_msg_data);
1888: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1889: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1890: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1891: ,p_data => x_msg_data);
1892: WHEN NO_DATA_FOUND THEN
1885: x_return_status := FND_API.G_RET_STS_ERROR;
1886: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1887: ,p_data => x_msg_data);
1888: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1889: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1890: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1891: ,p_data => x_msg_data);
1892: WHEN NO_DATA_FOUND THEN
1893: x_return_status := FND_API.G_RET_STS_ERROR;
1889: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1890: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1891: ,p_data => x_msg_data);
1892: WHEN NO_DATA_FOUND THEN
1893: x_return_status := FND_API.G_RET_STS_ERROR;
1894: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1895: ,p_data => x_msg_data);
1896: WHEN OTHERS THEN
1897: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1893: x_return_status := FND_API.G_RET_STS_ERROR;
1894: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1895: ,p_data => x_msg_data);
1896: WHEN OTHERS THEN
1897: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1898: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1899: ,p_data => x_msg_data);
1900:
1901: END Trans_DimObj_By_Given_Lang;
1928: p_commit VARCHAR2(1);
1929: BEGIN
1930:
1931: l_Fix_Row_Flag := FALSE;
1932: p_commit := FND_API.G_TRUE;
1933:
1934: SAVEPOINT ValidImportDimLevelViews;
1935: FND_MSG_PUB.Initialize;
1936:
1953: );
1954:
1955:
1956: -- Sucess --
1957: x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1958:
1959: -- Call the refresh Dimension Object Views.
1960: BSC_BIS_DIM_OBJ_PUB.Refresh_BSC_PMF_Dim_View
1961: ( p_Short_Name => NULL
1964: , x_msg_data => x_Msg_Data
1965: );
1966:
1967:
1968: IF (p_commit = FND_API.G_TRUE) THEN
1969: COMMIT;
1970: END IF;
1971:
1972: BIS_UTILITIES_PUB.PUT_LINE
1981: , x_Return_Msg => x_Msg_Data
1982: );
1983:
1984: EXCEPTION
1985: WHEN FND_API.G_EXC_ERROR THEN
1986: x_return_status := FND_API.G_RET_STS_ERROR;
1987: RETCODE := 2; -- Concurrent program display Error
1988: ERRBUF := x_msg_data; -- Concurrent program err message
1989:
1982: );
1983:
1984: EXCEPTION
1985: WHEN FND_API.G_EXC_ERROR THEN
1986: x_return_status := FND_API.G_RET_STS_ERROR;
1987: RETCODE := 2; -- Concurrent program display Error
1988: ERRBUF := x_msg_data; -- Concurrent program err message
1989:
1990: BIS_UTILITIES_PUB.PUT_LINE
2000: );
2001:
2002: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2003: ,p_data => x_msg_data);
2004: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2005: RETCODE := 2; -- Concurrent program display Error
2006: ERRBUF := x_msg_data; -- Concurrent program err message
2007: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2008: BIS_UTILITIES_PUB.PUT_LINE
2003: ,p_data => x_msg_data);
2004: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2005: RETCODE := 2; -- Concurrent program display Error
2006: ERRBUF := x_msg_data; -- Concurrent program err message
2007: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2008: BIS_UTILITIES_PUB.PUT_LINE
2009: (
2010: p_Text => x_Msg_Data
2011: );
2018: );
2019: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2020: ,p_data => x_msg_data);
2021: WHEN NO_DATA_FOUND THEN
2022: x_return_status := FND_API.G_RET_STS_ERROR;
2023: RETCODE := 2; -- Concurrent program display Error
2024: ERRBUF := x_msg_data; -- Concurrent program err message
2025: BIS_UTILITIES_PUB.PUT_LINE
2026: (
2037: ,p_data => x_msg_data);
2038: WHEN OTHERS THEN
2039: RETCODE := 2; -- Concurrent program display Error
2040: ERRBUF := x_msg_data; -- Concurrent program err message
2041: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2042: BIS_UTILITIES_PUB.PUT_LINE
2043: (
2044: p_Text => x_Msg_Data
2045: );