[Home] [Help]
PACKAGE BODY: APPS.BIS_BUCKET_PVT
Source
1 PACKAGE BODY BIS_BUCKET_PVT AS
2 /* $Header: BISVBKTB.pls 120.1 2006/07/26 07:58:21 ankgoel noship $ */
3
4 --=============================================================================
5 FUNCTION CHECK_RANGE_NAME (
6 p_bis_bucket_rec IN BIS_BUCKET_PUB.bis_bucket_rec_type
7 ) RETURN BOOLEAN;
8 --=============================================================================
9 FUNCTION CHECK_RANGE_VAL_LOW (
10 p_bis_bucket_rec IN BIS_BUCKET_PUB.bis_bucket_rec_type
11 ) RETURN BOOLEAN;
12 --=============================================================================
13 FUNCTION CHECK_RANGE_VAL_HIGH (
14 p_bis_bucket_rec IN BIS_BUCKET_PUB.bis_bucket_rec_type
15 ) RETURN BOOLEAN;
16 --=============================================================================
17 PROCEDURE sync_bucket_ranges (
18 p_id IN NUMBER
19 , p_deleted_ranges IN VARCHAR2
20 , p_new_ranges IN VARCHAR2);
21 --=============================================================================
22 --Called by Java API
23 --It should take all the data passed to it and builds a record of type BIS_BUCKET_REC_TYPE
24 --and call BIS_BUCKET_PVT. CREATE_BIS_BUCKET with it.
25 PROCEDURE CREATE_BIS_BUCKET_WRAPPER (
26 p_short_name IN BIS_BUCKET.short_name%TYPE
27 ,p_name IN BIS_BUCKET_TL.name%TYPE
28 ,p_type IN BIS_BUCKET.type%TYPE
29 ,p_application_id IN BIS_BUCKET.application_id%TYPE
30 ,p_range1_name IN BIS_BUCKET_TL.range1_name%TYPE
31 ,p_range1_low IN BIS_BUCKET.range1_low%TYPE
32 ,p_range1_high IN BIS_BUCKET.range1_high%TYPE
33 ,p_range2_name IN BIS_BUCKET_TL.range2_name%TYPE
34 ,p_range2_low IN BIS_BUCKET.range2_low%TYPE
35 ,p_range2_high IN BIS_BUCKET.range2_high%TYPE
36 ,p_range3_name IN BIS_BUCKET_TL.range3_name%TYPE
37 ,p_range3_low IN BIS_BUCKET.range3_low%TYPE
38 ,p_range3_high IN BIS_BUCKET.range3_high%TYPE
39 ,p_range4_name IN BIS_BUCKET_TL.range4_name%TYPE
40 ,p_range4_low IN BIS_BUCKET.range4_low%TYPE
41 ,p_range4_high IN BIS_BUCKET.range4_high%TYPE
42 ,p_range5_name IN BIS_BUCKET_TL.range5_name%TYPE
43 ,p_range5_low IN BIS_BUCKET.range5_low%TYPE
44 ,p_range5_high IN BIS_BUCKET.range5_high%TYPE
45 ,p_range6_name IN BIS_BUCKET_TL.range6_name%TYPE
46 ,p_range6_low IN BIS_BUCKET.range6_low%TYPE
47 ,p_range6_high IN BIS_BUCKET.range6_high%TYPE
48 ,p_range7_name IN BIS_BUCKET_TL.range7_name%TYPE
49 ,p_range7_low IN BIS_BUCKET.range7_low%TYPE
50 ,p_range7_high IN BIS_BUCKET.range7_high%TYPE
51 ,p_range8_name IN BIS_BUCKET_TL.range8_name%TYPE
52 ,p_range8_low IN BIS_BUCKET.range8_low%TYPE
53 ,p_range8_high IN BIS_BUCKET.range8_high%TYPE
54 ,p_range9_name IN BIS_BUCKET_TL.range9_name%TYPE
55 ,p_range9_low IN BIS_BUCKET.range9_low%TYPE
56 ,p_range9_high IN BIS_BUCKET.range9_high%TYPE
57 ,p_range10_name IN BIS_BUCKET_TL.range10_name%TYPE
58 ,p_range10_low IN BIS_BUCKET.range10_low%TYPE
59 ,p_range10_high IN BIS_BUCKET.range10_high%TYPE
60 ,p_description IN BIS_BUCKET_TL.description%TYPE
61 ,p_updatable IN BIS_BUCKET.updatable%TYPE := 'F'
62 ,p_expandable IN BIS_BUCKET.expandable%TYPE := 'F'
63 ,p_discontinuous IN BIS_BUCKET.discontinuous%TYPE := 'F'
64 ,p_overlapping IN BIS_BUCKET.overlapping%TYPE := 'F'
65 ,p_uom IN BIS_BUCKET.uom%TYPE
66 ,x_return_status OUT NOCOPY VARCHAR2
67 ,x_error_msg OUT NOCOPY VARCHAR2
68 )
69 IS
70
71 l_bis_bucket_rec BIS_BUCKET_PUB.bis_bucket_rec_type;
72 l_return_status VARCHAR2(10);
73 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
74
75 BEGIN
76
77 l_bis_bucket_rec.short_name := p_short_name;
78 l_bis_bucket_rec.name := p_name;
79 l_bis_bucket_rec.type := p_type;
80 l_bis_bucket_rec.application_id := p_application_id;
81 l_bis_bucket_rec.range1_name := p_range1_name;
82 l_bis_bucket_rec.range1_low := p_range1_low;
83 l_bis_bucket_rec.range1_high := p_range1_high;
84 l_bis_bucket_rec.range2_name := p_range2_name;
85 l_bis_bucket_rec.range2_low := p_range2_low;
86 l_bis_bucket_rec.range2_high := p_range2_high;
87 l_bis_bucket_rec.range3_name := p_range3_name;
88 l_bis_bucket_rec.range3_low := p_range3_low;
89 l_bis_bucket_rec.range3_high := p_range3_high;
90 l_bis_bucket_rec.range4_name := p_range4_name;
91 l_bis_bucket_rec.range4_low := p_range4_low;
92 l_bis_bucket_rec.range4_high := p_range4_high;
93 l_bis_bucket_rec.range5_name := p_range5_name;
94 l_bis_bucket_rec.range5_low := p_range5_low;
95 l_bis_bucket_rec.range5_high := p_range5_high;
96 l_bis_bucket_rec.range6_name := p_range6_name;
97 l_bis_bucket_rec.range6_low := p_range6_low;
98 l_bis_bucket_rec.range6_high := p_range6_high;
99 l_bis_bucket_rec.range7_name := p_range7_name;
100 l_bis_bucket_rec.range7_low := p_range7_low;
101 l_bis_bucket_rec.range7_high := p_range7_high;
102 l_bis_bucket_rec.range8_name := p_range8_name;
103 l_bis_bucket_rec.range8_low := p_range8_low;
104 l_bis_bucket_rec.range8_high := p_range8_high;
105 l_bis_bucket_rec.range9_name := p_range9_name;
106 l_bis_bucket_rec.range9_low := p_range9_low;
107 l_bis_bucket_rec.range9_high := p_range9_high;
108 l_bis_bucket_rec.range10_name := p_range10_name;
109 l_bis_bucket_rec.range10_low := p_range10_low;
110 l_bis_bucket_rec.range10_high := p_range10_high;
111 l_bis_bucket_rec.description := p_description;
112 l_bis_bucket_rec.updatable := p_updatable;
113 l_bis_bucket_rec.expandable := p_expandable;
114 l_bis_bucket_rec.discontinuous := p_discontinuous;
115 l_bis_bucket_rec.overlapping := p_overlapping;
116 l_bis_bucket_rec.uom := p_uom;
117
118 BIS_BUCKET_PVT.CREATE_BIS_BUCKET (
119 p_bis_bucket_rec => l_bis_bucket_rec
120 ,x_return_status => l_return_status
121 ,x_error_tbl => l_error_tbl
122 );
123
124 x_return_status := l_return_status;
125
126 IF (l_error_tbl.EXISTS(1)) THEN
127 x_error_msg := l_error_tbl(1).Error_Msg_Name;
128 END IF;
129
130 EXCEPTION
131 WHEN FND_API.G_EXC_ERROR THEN
132 x_return_status := FND_API.G_RET_STS_ERROR ;
133 IF (l_error_tbl.EXISTS(1)) THEN
134 x_error_msg := l_error_tbl(1).Error_Msg_Name;
135 END IF;
136 WHEN OTHERS THEN
137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
138 IF (l_error_tbl.EXISTS(1)) THEN
139 x_error_msg := l_error_tbl(1).Error_Msg_Name;
140 END IF;
141
142 END CREATE_BIS_BUCKET_WRAPPER;
143
144
145
146 --Called by Java API
147 --It should take all the data passed to it and builds a record of type BIS_BUCKET_REC_TYPE
148 --and call BIS_BUCKET_PVT.UPDATE_BIS_BUCKET with it.
149 PROCEDURE UPDATE_BIS_BUCKET_WRAPPER (
150 p_bucket_id IN BIS_BUCKET.bucket_id%TYPE
151 ,p_short_name IN BIS_BUCKET.short_name%TYPE
152 ,p_name IN BIS_BUCKET_TL.name%TYPE
153 ,p_type IN BIS_BUCKET.type%TYPE
154 ,p_application_id IN BIS_BUCKET.application_id%TYPE
155 ,p_range1_name IN BIS_BUCKET_TL.range1_name%TYPE
156 ,p_range1_low IN BIS_BUCKET.range1_low%TYPE
157 ,p_range1_high IN BIS_BUCKET.range1_high%TYPE
158 ,p_range2_name IN BIS_BUCKET_TL.range2_name%TYPE
159 ,p_range2_low IN BIS_BUCKET.range2_low%TYPE
160 ,p_range2_high IN BIS_BUCKET.range2_high%TYPE
161 ,p_range3_name IN BIS_BUCKET_TL.range3_name%TYPE
162 ,p_range3_low IN BIS_BUCKET.range3_low%TYPE
163 ,p_range3_high IN BIS_BUCKET.range3_high%TYPE
164 ,p_range4_name IN BIS_BUCKET_TL.range4_name%TYPE
165 ,p_range4_low IN BIS_BUCKET.range4_low%TYPE
166 ,p_range4_high IN BIS_BUCKET.range4_high%TYPE
167 ,p_range5_name IN BIS_BUCKET_TL.range5_name%TYPE
168 ,p_range5_low IN BIS_BUCKET.range5_low%TYPE
169 ,p_range5_high IN BIS_BUCKET.range5_high%TYPE
170 ,p_range6_name IN BIS_BUCKET_TL.range6_name%TYPE
171 ,p_range6_low IN BIS_BUCKET.range6_low%TYPE
172 ,p_range6_high IN BIS_BUCKET.range6_high%TYPE
173 ,p_range7_name IN BIS_BUCKET_TL.range7_name%TYPE
174 ,p_range7_low IN BIS_BUCKET.range7_low%TYPE
175 ,p_range7_high IN BIS_BUCKET.range7_high%TYPE
176 ,p_range8_name IN BIS_BUCKET_TL.range8_name%TYPE
177 ,p_range8_low IN BIS_BUCKET.range8_low%TYPE
178 ,p_range8_high IN BIS_BUCKET.range8_high%TYPE
179 ,p_range9_name IN BIS_BUCKET_TL.range9_name%TYPE
180 ,p_range9_low IN BIS_BUCKET.range9_low%TYPE
181 ,p_range9_high IN BIS_BUCKET.range9_high%TYPE
182 ,p_range10_name IN BIS_BUCKET_TL.range10_name%TYPE
183 ,p_range10_low IN BIS_BUCKET.range10_low%TYPE
184 ,p_range10_high IN BIS_BUCKET.range10_high%TYPE
185 ,p_description IN BIS_BUCKET_TL.description%TYPE
186 ,p_updatable IN BIS_BUCKET.updatable%TYPE := 'F'
187 ,p_expandable IN BIS_BUCKET.expandable%TYPE := 'F'
188 ,p_discontinuous IN BIS_BUCKET.discontinuous%TYPE := 'F'
189 ,p_overlapping IN BIS_BUCKET.overlapping%TYPE := 'F'
190 ,p_uom IN BIS_BUCKET.uom%TYPE
191 ,x_return_status OUT NOCOPY VARCHAR2
192 ,x_error_msg OUT NOCOPY VARCHAR2
193 )
194 IS
195
196 l_bis_bucket_rec BIS_BUCKET_PUB.bis_bucket_rec_type;
197 l_return_status VARCHAR2(10);
198 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
199 l_ret VARCHAR2(10);
200
201 BEGIN
202
203 l_bis_bucket_rec.short_name := p_short_name;
204 l_bis_bucket_rec.name := p_name;
205 l_bis_bucket_rec.type := p_type;
206 l_bis_bucket_rec.application_id := p_application_id;
207 l_bis_bucket_rec.range1_name := p_range1_name;
208 l_bis_bucket_rec.range1_low := p_range1_low;
209 l_bis_bucket_rec.range1_high := p_range1_high;
210 l_bis_bucket_rec.range2_name := p_range2_name;
211 l_bis_bucket_rec.range2_low := p_range2_low;
212 l_bis_bucket_rec.range2_high := p_range2_high;
213 l_bis_bucket_rec.range3_name := p_range3_name;
214 l_bis_bucket_rec.range3_low := p_range3_low;
215 l_bis_bucket_rec.range3_high := p_range3_high;
216 l_bis_bucket_rec.range4_name := p_range4_name;
217 l_bis_bucket_rec.range4_low := p_range4_low;
218 l_bis_bucket_rec.range4_high := p_range4_high;
219 l_bis_bucket_rec.range5_name := p_range5_name;
220 l_bis_bucket_rec.range5_low := p_range5_low;
221 l_bis_bucket_rec.range5_high := p_range5_high;
222 l_bis_bucket_rec.range6_name := p_range6_name;
223 l_bis_bucket_rec.range6_low := p_range6_low;
224 l_bis_bucket_rec.range6_high := p_range6_high;
225 l_bis_bucket_rec.range7_name := p_range7_name;
226 l_bis_bucket_rec.range7_low := p_range7_low;
227 l_bis_bucket_rec.range7_high := p_range7_high;
228 l_bis_bucket_rec.range8_name := p_range8_name;
229 l_bis_bucket_rec.range8_low := p_range8_low;
230 l_bis_bucket_rec.range8_high := p_range8_high;
231 l_bis_bucket_rec.range9_name := p_range9_name;
232 l_bis_bucket_rec.range9_low := p_range9_low;
233 l_bis_bucket_rec.range9_high := p_range9_high;
234 l_bis_bucket_rec.range10_name := p_range10_name;
235 l_bis_bucket_rec.range10_low := p_range10_low;
236 l_bis_bucket_rec.range10_high := p_range10_high;
237 l_bis_bucket_rec.description := p_description;
238 l_bis_bucket_rec.updatable := p_updatable;
239 l_bis_bucket_rec.expandable := p_expandable;
240 l_bis_bucket_rec.discontinuous := p_discontinuous;
241 l_bis_bucket_rec.overlapping := p_overlapping;
242 l_bis_bucket_rec.uom := p_uom;
243
244 BIS_BUCKET_PVT.UPDATE_BIS_BUCKET (
245 p_bis_bucket_rec => l_bis_bucket_rec
246 ,x_return_status => l_return_status
247 ,x_error_tbl => l_error_tbl
248 );
249
250 x_return_status := l_return_status;
251 IF (l_error_tbl.EXISTS(1)) THEN
252 x_error_msg := l_error_tbl(1).Error_Msg_Name;
253 END IF;
254
255 EXCEPTION
256 WHEN FND_API.G_EXC_ERROR THEN
257 x_return_status := FND_API.G_RET_STS_ERROR ;
258 IF (l_error_tbl.EXISTS(1)) THEN
259 x_error_msg := l_error_tbl(1).Error_Msg_Name;
260 END IF;
261 WHEN OTHERS THEN
262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
263 IF (l_error_tbl.EXISTS(1)) THEN
264 x_error_msg := l_error_tbl(1).Error_Msg_Name;
265 END IF;
266 END UPDATE_BIS_BUCKET_WRAPPER;
267
268 PROCEDURE UPDATE_CUST_BUCKET (
269 p_commit IN VARCHAR2 := FND_API.G_FALSE
270 , p_id IN BIS_BUCKET_CUSTOMIZATIONS.id%TYPE
271 , p_bucket_id IN BIS_BUCKET_CUSTOMIZATIONS.bucket_id%TYPE
272 , p_user_id IN BIS_BUCKET_CUSTOMIZATIONS.user_id%TYPE
273 , p_responsibility_id IN BIS_BUCKET_CUSTOMIZATIONS.responsibility_id%TYPE
274 , p_application_id IN BIS_BUCKET_CUSTOMIZATIONS.application_id%TYPE
275 , p_org_id IN BIS_BUCKET_CUSTOMIZATIONS.org_id%TYPE
276 , p_site_id IN BIS_BUCKET_CUSTOMIZATIONS.site_id%TYPE
277 , p_page_id IN BIS_BUCKET_CUSTOMIZATIONS.page_id%TYPE
278 , p_function_id IN BIS_BUCKET_CUSTOMIZATIONS.function_id%TYPE
279 , p_range1_low IN BIS_BUCKET_CUSTOMIZATIONS.range1_low%TYPE
280 , p_range1_high IN BIS_BUCKET_CUSTOMIZATIONS.range1_high%TYPE
281 , p_range2_low IN BIS_BUCKET_CUSTOMIZATIONS.range2_low%TYPE
282 , p_range2_high IN BIS_BUCKET_CUSTOMIZATIONS.range2_high%TYPE
283 , p_range3_low IN BIS_BUCKET_CUSTOMIZATIONS.range3_low%TYPE
284 , p_range3_high IN BIS_BUCKET_CUSTOMIZATIONS.range3_high%TYPE
285 , p_range4_low IN BIS_BUCKET_CUSTOMIZATIONS.range4_low%TYPE
286 , p_range4_high IN BIS_BUCKET_CUSTOMIZATIONS.range4_high%TYPE
287 , p_range5_low IN BIS_BUCKET_CUSTOMIZATIONS.range5_low%TYPE
288 , p_range5_high IN BIS_BUCKET_CUSTOMIZATIONS.range5_high%TYPE
289 , p_range6_low IN BIS_BUCKET_CUSTOMIZATIONS.range6_low%TYPE
290 , p_range6_high IN BIS_BUCKET_CUSTOMIZATIONS.range6_high%TYPE
291 , p_range7_low IN BIS_BUCKET_CUSTOMIZATIONS.range7_low%TYPE
292 , p_range7_high IN BIS_BUCKET_CUSTOMIZATIONS.range7_high%TYPE
293 , p_range8_low IN BIS_BUCKET_CUSTOMIZATIONS.range8_low%TYPE
294 , p_range8_high IN BIS_BUCKET_CUSTOMIZATIONS.range8_high%TYPE
295 , p_range9_low IN BIS_BUCKET_CUSTOMIZATIONS.range9_low%TYPE
296 , p_range9_high IN BIS_BUCKET_CUSTOMIZATIONS.range9_high%TYPE
297 , p_range10_low IN BIS_BUCKET_CUSTOMIZATIONS.range10_low%TYPE
298 , p_range10_high IN BIS_BUCKET_CUSTOMIZATIONS.range10_high%TYPE
299 , p_range1_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range1_name%TYPE
300 , p_range2_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range2_name%TYPE
301 , p_range3_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range3_name%TYPE
302 , p_range4_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range4_name%TYPE
303 , p_range5_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range5_name%TYPE
304 , p_range6_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range6_name%TYPE
305 , p_range7_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range7_name%TYPE
306 , p_range8_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range8_name%TYPE
307 , p_range9_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range9_name%TYPE
308 , p_range10_name IN BIS_BUCKET_CUSTOMIZATIONS_TL.range10_name%TYPE
309 , p_customized IN BIS_BUCKET_CUSTOMIZATIONS.customized%TYPE
310 , p_deleted_ranges IN VARCHAR2
311 , p_new_ranges IN VARCHAR2
312 , x_return_status OUT NOCOPY VARCHAR2
313 , x_msg_count OUT NOCOPY NUMBER
314 , x_msg_data OUT NOCOPY VARCHAR2
315 )
316 IS
317 BEGIN
318
319 SAVEPOINT SP_UPDATE_BUCKET_CUST;
320
321 x_return_status := FND_API.G_RET_STS_SUCCESS;
322 FND_MSG_PUB.initialize;
323
324 UPDATE bis_bucket_customizations SET
325 bucket_id = p_bucket_id,
326 user_id = p_user_id,
327 responsibility_id = p_responsibility_id,
328 application_id = p_application_id,
329 org_id = p_org_id,
330 site_id = p_site_id,
331 page_id = p_page_id,
332 function_id = p_function_id,
333 range1_low = p_range1_low,
334 range1_high = p_range1_high,
335 range2_low = p_range2_low,
336 range2_high = p_range2_high,
337 range3_low = p_range3_low,
338 range3_high = p_range3_high,
339 range4_low = p_range4_low,
340 range4_high = p_range4_high,
341 range5_low = p_range5_low,
342 range5_high = p_range5_high,
343 range6_low = p_range6_low,
344 range6_high = p_range6_high,
345 range7_low = p_range7_low,
349 range9_low = p_range9_low,
346 range7_high = p_range7_high,
347 range8_low = p_range8_low,
348 range8_high = p_range8_high,
350 range9_high = p_range9_high,
351 range10_low = p_range10_low,
352 range10_high = p_range10_high,
353 CUSTOMIZED = p_customized,
354 last_update_date = SYSDATE,
355 last_updated_by = FND_GLOBAL.USER_ID,
356 last_update_login = FND_GLOBAL.LOGIN_ID
357 WHERE id = p_id;
358
359 UPDATE bis_bucket_customizations_tl SET
360 range1_name = p_range1_name,
361 range2_name = p_range2_name,
362 range3_name = p_range3_name,
363 range4_name = p_range4_name,
364 range5_name = p_range5_name,
365 range6_name = p_range6_name,
366 range7_name = p_range7_name,
367 range8_name = p_range8_name,
368 range9_name = p_range9_name,
369 range10_name = p_range10_name,
370 last_update_date = SYSDATE,
371 last_updated_by = FND_GLOBAL.USER_ID,
372 last_update_login = FND_GLOBAL.LOGIN_ID,
373 source_lang = userenv('LANG')
374 where id = p_id
375 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
376
377 sync_bucket_ranges (
378 p_id => p_id
379 , p_deleted_ranges => p_deleted_ranges
380 , p_new_ranges => p_new_ranges
381 );
382
383 IF (p_commit = FND_API.G_TRUE) THEN
384 COMMIT;
385 END IF;
386
387 EXCEPTION
388 WHEN FND_API.G_EXC_ERROR THEN
389 IF (x_msg_data IS NULL) THEN
390 FND_MSG_PUB.Count_And_Get
391 ( p_encoded => FND_API.G_FALSE
392 , p_count => x_msg_count
393 , p_data => x_msg_data
394 );
395 END IF;
396 ROLLBACK TO SP_UPDATE_BUCKET_CUST;
397 x_return_status := FND_API.G_RET_STS_ERROR;
398 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
399 IF (x_msg_data IS NULL) THEN
400 FND_MSG_PUB.Count_And_Get
401 ( p_encoded => FND_API.G_FALSE
402 , p_count => x_msg_count
403 , p_data => x_msg_data
404 );
405 END IF;
406 ROLLBACK TO SP_UPDATE_BUCKET_CUST;
407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
408 WHEN NO_DATA_FOUND THEN
409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
410 IF (x_msg_data IS NOT NULL) THEN
411 x_msg_data := x_msg_data||' -> BIS_BUCKET_PVT.UPDATE_CUST_BUCKET ';
412 ELSE
413 x_msg_data := SQLERRM||' at BIS_BUCKET_PVT.UPDATE_CUST_BUCKET ';
414 END IF;
415 ROLLBACK TO SP_UPDATE_BUCKET_CUST;
416 WHEN OTHERS THEN
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
418 IF (x_msg_data IS NOT NULL) THEN
419 x_msg_data := x_msg_data||' -> BIS_BUCKET_PVT.UPDATE_CUST_BUCKET ';
420 ELSE
421 x_msg_data := SQLERRM||' at BIS_BUCKET_PVT.UPDATE_CUST_BUCKET ';
422 END IF;
423 ROLLBACK TO SP_UPDATE_BUCKET_CUST;
424 END UPDATE_CUST_BUCKET;
425
426 PROCEDURE sync_bucket_ranges (
427 p_id IN NUMBER
428 , p_deleted_ranges IN VARCHAR2
429 , p_new_ranges IN VARCHAR2
430 )
431 IS
432 l_deleted_ranges VARCHAR2(100);
433 l_new_ranges VARCHAR2(100);
434 l_range_num VARCHAR2(2);
435 l_next_pos NUMBER := 0;
436 l_sql VARCHAR2(100);
437 l_pointer NUMBER;
438
439 CURSOR c_installed_lang IS
440 SELECT L.language_code FROM fnd_languages L
441 WHERE L.installed_flag IN ('I', 'B')
442 AND L.language_code <> userenv('LANG');
443 l_lang_rec c_installed_lang%ROWTYPE;
444
445 CURSOR c_bucket(p_lang_code VARCHAR2) IS
446 SELECT range1_name, range2_name, range3_name, range4_name,
447 range5_name, range6_name, range7_name, range8_name, range9_name, range10_name
448 FROM bis_bucket_customizations_tl
449 WHERE id = p_id
450 AND language = source_lang
451 AND source_lang = p_lang_code;
452 l_bucket_rec c_bucket%ROWTYPE;
453
454 l_range_labels BIS_BUCKET_PVT.RangeLabels;
455 c_delete_marker VARCHAR2(10) := '@#!$';
456
457 BEGIN
458
459 l_range_labels := RangeLabels(NULL);
460 FOR i IN 1..19 LOOP
461 l_range_labels.extend;
462 l_range_labels(l_range_labels.LAST) := NULL;
463 END LOOP;
464
465 FOR l_lang_rec IN c_installed_lang LOOP
466
467 IF (c_bucket%ISOPEN) THEN
468 CLOSE c_bucket;
469 END IF;
470 OPEN c_bucket(l_lang_rec.language_code);
471 FETCH c_bucket INTO l_bucket_rec;
472
473 IF (c_bucket%FOUND) THEN
474 l_range_labels(1) := l_bucket_rec.range1_name;
475 l_range_labels(2) := l_bucket_rec.range2_name;
476 l_range_labels(3) := l_bucket_rec.range3_name;
477 l_range_labels(4) := l_bucket_rec.range4_name;
478 l_range_labels(5) := l_bucket_rec.range5_name;
479 l_range_labels(6) := l_bucket_rec.range6_name;
480 l_range_labels(7) := l_bucket_rec.range7_name;
481 l_range_labels(8) := l_bucket_rec.range8_name;
482 l_range_labels(9) := l_bucket_rec.range9_name;
483 l_range_labels(10) := l_bucket_rec.range10_name;
484 END IF;
485
486 CLOSE c_bucket;
487
488 /*
489 Handle deleted ranges first for non-US rows.
490 Make the range name labels as NULL for the deleted range numbers.
494 l_deleted_ranges := p_deleted_ranges;
491 */
492 IF (p_deleted_ranges IS NOT NULL) THEN
493
495
496 WHILE (LENGTH(l_deleted_ranges) > 0) LOOP
497 l_next_pos := INSTR(l_deleted_ranges, ',');
498 IF (l_next_pos = 0) THEN
499 l_range_num := TO_NUMBER(l_deleted_ranges);
500 l_deleted_ranges := NULL;
501 ELSE
502 l_range_num := TO_NUMBER(SUBSTR(l_deleted_ranges, 1, l_next_pos - 1));
503 l_deleted_ranges := SUBSTR(l_deleted_ranges, l_next_pos + 1);
504 END IF;
505
506 l_range_labels(l_range_num) := c_delete_marker;
507 END LOOP;
508
509 END IF;
510
511 /*
512 Handle new ranges for non-US rows.
513 Shift range names towards right after the new range numbers,
514 and insert the range labels from US rows for newly created blank range columns.
515 */
516 IF (p_new_ranges IS NOT NULL) THEN
517
518 l_new_ranges := p_new_ranges;
519
520 WHILE (LENGTH(l_new_ranges) > 0) LOOP
521 l_next_pos := INSTR(l_new_ranges, ',');
522
523 IF (l_next_pos = 0) THEN
524 l_range_num := TO_NUMBER(l_new_ranges);
525 l_new_ranges := NULL;
526 ELSE
527 l_range_num := TO_NUMBER(SUBSTR(l_new_ranges, 1, l_next_pos - 1));
528 l_new_ranges := SUBSTR(l_new_ranges, l_next_pos + 1);
529 END IF;
530
531 FOR i IN REVERSE (l_range_num + 2)..l_range_labels.COUNT LOOP
532 l_range_labels(i) := l_range_labels(i - 1);
533 END LOOP;
534 l_range_labels(l_range_num + 1) := NULL;
535
536 END LOOP;
537
538 END IF;
539
540 /*
541 Arrange all the ranges one after the other by shifting left over the deleted ranges.
542 */
543 l_pointer := 1;
544 WHILE (l_pointer < l_range_labels.COUNT) LOOP
545 IF (l_range_labels(l_pointer) = c_delete_marker) THEN
546 FOR j IN l_pointer..(l_range_labels.COUNT - 1) LOOP
547 l_range_labels(j) := l_range_labels(j + 1);
548 END LOOP;
549 ELSE
550 l_pointer := l_pointer + 1;
551 END IF;
552 END LOOP;
553
554 /*
555 Insert new range labels from US rows.
556 */
557
558 IF (c_bucket%ISOPEN) THEN
559 CLOSE c_bucket;
560 END IF;
561 OPEN c_bucket(userenv('LANG'));
562 FETCH c_bucket INTO l_bucket_rec;
563
564 IF (c_bucket%FOUND) THEN
565 UPDATE bis_bucket_customizations_tl
566 SET range1_name = NVL(l_range_labels(1), l_bucket_rec.range1_name),
567 range2_name = NVL(l_range_labels(2), l_bucket_rec.range2_name),
568 range3_name = NVL(l_range_labels(3), l_bucket_rec.range3_name),
569 range4_name = NVL(l_range_labels(4), l_bucket_rec.range4_name),
570 range5_name = NVL(l_range_labels(5), l_bucket_rec.range5_name),
571 range6_name = NVL(l_range_labels(6), l_bucket_rec.range6_name),
572 range7_name = NVL(l_range_labels(7), l_bucket_rec.range7_name),
573 range8_name = NVL(l_range_labels(8), l_bucket_rec.range8_name),
574 range9_name = NVL(l_range_labels(9), l_bucket_rec.range9_name),
575 range10_name = NVL(l_range_labels(10), l_bucket_rec.range10_name)
576 WHERE language = source_lang
577 AND language = l_lang_rec.language_code
578 AND id = p_id ;
579 END IF;
580 CLOSE c_bucket;
581
582 END LOOP;
583
584 EXCEPTION
585 WHEN OTHERS THEN
586 IF (c_bucket%ISOPEN) THEN
587 CLOSE c_bucket;
588 END IF;
589 RAISE;
590 END sync_bucket_ranges;
591
592
593 PROCEDURE reset_bucket (
594 p_bucket_id IN NUMBER
595 )
596 IS
597 CURSOR c_base_bucket IS
601 range4_low, range4_high,
598 SELECT range1_low, range1_high,
599 range2_low, range2_high,
600 range3_low, range3_high,
602 range5_low, range5_high,
603 range6_low, range6_high,
604 range7_low, range7_high,
605 range8_low, range8_high,
606 range9_low, range9_high,
607 range10_low, range10_high
608 FROM bis_bucket
609 WHERE bucket_id = p_bucket_id;
610 l_base_bucket_rec c_base_bucket%ROWTYPE;
611
612 CURSOR c_base_bucket_tl(p_lang VARCHAR2) IS
613 SELECT range1_name,
614 range2_name,
615 range3_name,
616 range4_name,
617 range5_name,
618 range6_name,
619 range7_name,
620 range8_name,
621 range9_name,
622 range10_name
623 FROM bis_bucket_tl
624 WHERE bucket_id = p_bucket_id
625 AND language = p_lang;
626 l_base_bucket_tl_rec c_base_bucket_tl%ROWTYPE;
627
628 CURSOR c_installed_lang IS
629 SELECT L.language_code FROM fnd_languages L
630 WHERE L.installed_flag IN ('I', 'B');
631 l_lang_rec c_installed_lang%ROWTYPE;
632
633 BEGIN
634
635 SAVEPOINT SP_RESET_CUST_BUCKET;
636
637 IF (c_base_bucket%ISOPEN) THEN
638 CLOSE c_base_bucket;
639 END IF;
640 OPEN c_base_bucket;
641 FETCH c_base_bucket INTO l_base_bucket_rec;
642 IF(c_base_bucket%FOUND) THEN
643 UPDATE bis_bucket_customizations SET
644 user_id = NULL,
645 responsibility_id = NULL,
646 application_id = NULL,
647 org_id = NULL,
648 site_id = NULL,
649 page_id = NULL,
650 function_id = NULL,
651 range1_low = l_base_bucket_rec.range1_low,
652 range1_high = l_base_bucket_rec.range1_high,
653 range2_low = l_base_bucket_rec.range2_low,
654 range2_high = l_base_bucket_rec.range2_high,
655 range3_low = l_base_bucket_rec.range3_low,
656 range3_high = l_base_bucket_rec.range3_high,
657 range4_low = l_base_bucket_rec.range4_low,
658 range4_high = l_base_bucket_rec.range4_high,
659 range5_low = l_base_bucket_rec.range5_low,
660 range5_high = l_base_bucket_rec.range5_high,
661 range6_low = l_base_bucket_rec.range6_low,
662 range6_high = l_base_bucket_rec.range6_high,
663 range7_low = l_base_bucket_rec.range7_low,
664 range7_high = l_base_bucket_rec.range7_high,
665 range8_low = l_base_bucket_rec.range8_low,
666 range8_high = l_base_bucket_rec.range8_high,
667 range9_low = l_base_bucket_rec.range9_low,
668 range9_high = l_base_bucket_rec.range9_high,
669 range10_low = l_base_bucket_rec.range10_low,
670 range10_high = l_base_bucket_rec.range10_high,
671 CUSTOMIZED = 'F',
672 last_update_date = SYSDATE,
673 last_updated_by = FND_GLOBAL.USER_ID,
674 last_update_login = FND_GLOBAL.LOGIN_ID
675 WHERE bucket_id = p_bucket_id;
676 END IF;
677
678 CLOSE c_base_bucket;
679
680 FOR l_lang_rec IN c_installed_lang LOOP
681
682 IF (c_base_bucket_tl%ISOPEN) THEN
683 CLOSE c_base_bucket_tl;
684 END IF;
685 OPEN c_base_bucket_tl(l_lang_rec.language_code);
686 FETCH c_base_bucket_tl INTO l_base_bucket_tl_rec;
687 IF(c_base_bucket_tl%FOUND) THEN
688
689 UPDATE bis_bucket_customizations_tl SET
690 range1_name = l_base_bucket_tl_rec.range1_name,
691 range2_name = l_base_bucket_tl_rec.range2_name,
692 range3_name = l_base_bucket_tl_rec.range3_name,
693 range4_name = l_base_bucket_tl_rec.range4_name,
694 range5_name = l_base_bucket_tl_rec.range5_name,
695 range6_name = l_base_bucket_tl_rec.range6_name,
696 range7_name = l_base_bucket_tl_rec.range7_name,
697 range8_name = l_base_bucket_tl_rec.range8_name,
698 range9_name = l_base_bucket_tl_rec.range9_name,
699 range10_name = l_base_bucket_tl_rec.range10_name,
700 last_update_date = SYSDATE,
701 last_updated_by = FND_GLOBAL.USER_ID,
702 last_update_login = FND_GLOBAL.LOGIN_ID
703 WHERE id IN (SELECT id FROM bis_bucket_customizations WHERE bucket_id = p_bucket_id)
704 AND language = l_lang_rec.language_code;
705
706 END IF;
707
708 CLOSE c_base_bucket_tl;
709
710 END LOOP;
711
712 EXCEPTION
713 WHEN OTHERS THEN
714 IF (c_base_bucket%ISOPEN) THEN
715 CLOSE c_base_bucket;
716 END IF;
717 IF (c_base_bucket_tl%ISOPEN) THEN
718 CLOSE c_base_bucket_tl;
719 END IF;
720 ROLLBACK TO SP_RESET_CUST_BUCKET;
721 RAISE;
722 END reset_bucket;
723
724
725 --Called by Java API
726 --It should call BIS_BUCKET_PVT. RETRIEVE _BIS_BUCKET with the short name
727 --and using the record of type BIS_BUCKET_REC_TYPE data obtained from that procedure,
728 --it should populates the out parameters
729 PROCEDURE RETRIEVE_BIS_BUCKET_WRAPPER (
730 p_short_name IN BIS_BUCKET.short_name%TYPE := BIS_UTILITIES_PUB.G_NULL_CHAR
731 ,x_bucket_id OUT NOCOPY BIS_BUCKET.bucket_id%TYPE
732 ,x_name OUT NOCOPY BIS_BUCKET_TL.name%TYPE
733 ,x_type OUT NOCOPY BIS_BUCKET.type%TYPE
734 ,x_application_id OUT NOCOPY BIS_BUCKET.application_id%TYPE
738 ,x_range2_name OUT NOCOPY BIS_BUCKET_TL.range2_name%TYPE
735 ,x_range1_name OUT NOCOPY BIS_BUCKET_TL.range1_name%TYPE
736 ,x_range1_low OUT NOCOPY BIS_BUCKET.range1_low%TYPE
737 ,x_range1_high OUT NOCOPY BIS_BUCKET.range1_high%TYPE
739 ,x_range2_low OUT NOCOPY BIS_BUCKET.range2_low%TYPE
740 ,x_range2_high OUT NOCOPY BIS_BUCKET.range2_high%TYPE
741 ,x_range3_name OUT NOCOPY BIS_BUCKET_TL.range3_name%TYPE
742 ,x_range3_low OUT NOCOPY BIS_BUCKET.range3_low%TYPE
743 ,x_range3_high OUT NOCOPY BIS_BUCKET.range3_high%TYPE
744 ,x_range4_name OUT NOCOPY BIS_BUCKET_TL.range4_name%TYPE
745 ,x_range4_low OUT NOCOPY BIS_BUCKET.range4_low%TYPE
746 ,x_range4_high OUT NOCOPY BIS_BUCKET.range4_high%TYPE
747 ,x_range5_name OUT NOCOPY BIS_BUCKET_TL.range5_name%TYPE
748 ,x_range5_low OUT NOCOPY BIS_BUCKET.range5_low%TYPE
749 ,x_range5_high OUT NOCOPY BIS_BUCKET.range5_high%TYPE
750 ,x_range6_name OUT NOCOPY BIS_BUCKET_TL.range6_name%TYPE
751 ,x_range6_low OUT NOCOPY BIS_BUCKET.range6_low%TYPE
752 ,x_range6_high OUT NOCOPY BIS_BUCKET.range6_high%TYPE
753 ,x_range7_name OUT NOCOPY BIS_BUCKET_TL.range7_name%TYPE
754 ,x_range7_low OUT NOCOPY BIS_BUCKET.range7_low%TYPE
755 ,x_range7_high OUT NOCOPY BIS_BUCKET.range7_high%TYPE
756 ,x_range8_name OUT NOCOPY BIS_BUCKET_TL.range8_name%TYPE
757 ,x_range8_low OUT NOCOPY BIS_BUCKET.range8_low%TYPE
758 ,x_range8_high OUT NOCOPY BIS_BUCKET.range8_high%TYPE
759 ,x_range9_name OUT NOCOPY BIS_BUCKET_TL.range9_name%TYPE
760 ,x_range9_low OUT NOCOPY BIS_BUCKET.range9_low%TYPE
761 ,x_range9_high OUT NOCOPY BIS_BUCKET.range9_high%TYPE
762 ,x_range10_name OUT NOCOPY BIS_BUCKET_TL.range10_name%TYPE
763 ,x_range10_low OUT NOCOPY BIS_BUCKET.range10_low%TYPE
764 ,x_range10_high OUT NOCOPY BIS_BUCKET.range10_high%TYPE
765 ,x_description OUT NOCOPY BIS_BUCKET_TL.description%TYPE
766 ,x_updatable OUT NOCOPY BIS_BUCKET.updatable%TYPE
767 ,x_expandable OUT NOCOPY BIS_BUCKET.expandable%TYPE
768 ,x_discontinuous OUT NOCOPY BIS_BUCKET.discontinuous%TYPE
769 ,x_overlapping OUT NOCOPY BIS_BUCKET.overlapping%TYPE
770 ,x_uom OUT NOCOPY BIS_BUCKET.uom%TYPE
771 ,x_return_status OUT NOCOPY VARCHAR2
772 ,x_error_msg OUT NOCOPY VARCHAR2
773 )
774 IS
775
776 l_bis_bucket_rec BIS_BUCKET_PUB.bis_bucket_rec_type;
777 l_return_status VARCHAR2(10);
778 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
779
780 BEGIN
781
782 BIS_BUCKET_PVT.RETRIEVE_BIS_BUCKET (
783 p_short_name => p_short_name
784 ,x_bis_bucket_rec => l_bis_bucket_rec
785 ,x_return_status => l_return_status
786 ,x_error_tbl => l_error_tbl
787 );
788
789 x_bucket_id := l_bis_bucket_rec.bucket_id;
790 x_name := l_bis_bucket_rec.name;
791 x_type := l_bis_bucket_rec.type;
792 x_application_id := l_bis_bucket_rec.application_id;
793 x_range1_name := l_bis_bucket_rec.range1_name;
794 x_range1_low := l_bis_bucket_rec.range1_low;
795 x_range1_high := l_bis_bucket_rec.range1_high;
796 x_range2_name := l_bis_bucket_rec.range2_name;
797 x_range2_low := l_bis_bucket_rec.range2_low;
798 x_range2_high := l_bis_bucket_rec.range2_high;
799 x_range3_name := l_bis_bucket_rec.range3_name;
800 x_range3_low := l_bis_bucket_rec.range3_low;
801 x_range3_high := l_bis_bucket_rec.range3_high;
802 x_range4_name := l_bis_bucket_rec.range4_name;
803 x_range4_low := l_bis_bucket_rec.range4_low;
804 x_range4_high := l_bis_bucket_rec.range4_high;
805 x_range5_name := l_bis_bucket_rec.range5_name;
806 x_range5_low := l_bis_bucket_rec.range5_low;
807 x_range5_high := l_bis_bucket_rec.range5_high;
808 x_range6_name := l_bis_bucket_rec.range6_name;
809 x_range6_low := l_bis_bucket_rec.range6_low;
810 x_range6_high := l_bis_bucket_rec.range6_high;
811 x_range7_name := l_bis_bucket_rec.range7_name;
812 x_range7_low := l_bis_bucket_rec.range7_low;
813 x_range7_high := l_bis_bucket_rec.range7_high;
814 x_range8_name := l_bis_bucket_rec.range8_name;
815 x_range8_low := l_bis_bucket_rec.range8_low;
816 x_range8_high := l_bis_bucket_rec.range8_high;
817 x_range9_name := l_bis_bucket_rec.range9_name;
818 x_range9_low := l_bis_bucket_rec.range9_low;
819 x_range9_high := l_bis_bucket_rec.range9_high;
820 x_range10_name := l_bis_bucket_rec.range10_name;
821 x_range10_low := l_bis_bucket_rec.range10_low;
822 x_range10_high := l_bis_bucket_rec.range10_high;
823 x_description := l_bis_bucket_rec.description;
824 x_updatable := l_bis_bucket_rec.updatable;
825 x_expandable := l_bis_bucket_rec.expandable;
826 x_discontinuous := l_bis_bucket_rec.discontinuous;
827 x_overlapping := l_bis_bucket_rec.overlapping;
828 x_uom := l_bis_bucket_rec.uom;
829
830 x_return_status := l_return_status;
831 IF (l_error_tbl.EXISTS(1)) THEN
832 x_error_msg := l_error_tbl(1).Error_Msg_Name;
833 END IF;
834
835 EXCEPTION
836 WHEN FND_API.G_EXC_ERROR THEN
837 x_return_status := FND_API.G_RET_STS_ERROR ;
838 IF (l_error_tbl.EXISTS(1)) THEN
839 x_error_msg := l_error_tbl(1).Error_Msg_Name;
840 END IF;
841 WHEN OTHERS THEN
842 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
843 IF (l_error_tbl.EXISTS(1)) THEN
844 x_error_msg := l_error_tbl(1).Error_Msg_Name;
845 END IF;
846
850 --This API should call BIS_BUCKET_PVT.DELETE_BIS_BUCKET
847 END RETRIEVE_BIS_BUCKET_WRAPPER;
848
849
851 PROCEDURE DELETE_BIS_BUCKET_WRAPPER (
852 p_bucket_id IN BIS_BUCKET.bucket_id%TYPE := BIS_UTILITIES_PUB.G_NULL_NUM
853 ,p_short_name IN BIS_BUCKET.short_name%TYPE := BIS_UTILITIES_PUB.G_NULL_CHAR
854 ,x_return_status OUT NOCOPY VARCHAR2
855 ,x_error_msg OUT NOCOPY VARCHAR2
856 )
857 IS
858
859 l_return_status VARCHAR2(10);
860 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
861
862 BEGIN
863 BIS_BUCKET_PVT.DELETE_BIS_BUCKET(
864 p_bucket_id => p_bucket_id
865 ,p_short_name => p_short_name
866 ,x_return_status => l_return_status
867 ,x_error_tbl => l_error_tbl
868 );
869 x_return_status := l_return_status;
870 IF (l_error_tbl.EXISTS(1)) THEN
871 x_error_msg := l_error_tbl(1).Error_Msg_Name;
872 END IF;
873
874 EXCEPTION
875 WHEN FND_API.G_EXC_ERROR THEN
876 x_return_status := FND_API.G_RET_STS_ERROR ;
877 IF (l_error_tbl.EXISTS(1)) THEN
878 x_error_msg := l_error_tbl(1).Error_Msg_Name;
879 END IF;
880 WHEN OTHERS THEN
881 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
882 IF (l_error_tbl.EXISTS(1)) THEN
883 x_error_msg := l_error_tbl(1).Error_Msg_Name;
884 END IF;
885
886 END DELETE_BIS_BUCKET_WRAPPER;
887
888
889 --This API should generate the bucket_id in sequence and insert the data passed to it,
890 --into the tables BIS_BUCKET, BIS_BUCKET_TL
891 PROCEDURE CREATE_BIS_BUCKET (
892 p_bis_bucket_rec IN BIS_BUCKET_PUB.bis_bucket_rec_type
893 ,x_return_status OUT NOCOPY VARCHAR2
894 ,x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
895 )
896 IS
897
898 l_bucket_id NUMBER;
899 l_id NUMBER;
900 l_user_id NUMBER;
901 l_login_id NUMBER;
902 l_short_count NUMBER;
903 l_fnd_type_count NUMBER;
904 l_bis_type_count NUMBER;
905 l_bis_bucket_rec BIS_BUCKET_PUB.bis_bucket_rec_type;
906 l_return_status VARCHAR2(10);
907 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
908
909 BEGIN
910 SAVEPOINT SP_CREATE_BUCKET;
911
912 l_user_id := fnd_global.user_id;
913 l_login_id := fnd_global.LOGIN_ID;
914 l_bis_bucket_rec := p_bis_bucket_rec;
915
916 BIS_BUCKET_PVT.Validate_Bucket (
917 p_bis_bucket_rec => l_bis_bucket_rec
918 ,x_return_status => x_return_status
919 ,x_error_Tbl => x_error_tbl
920 );
921
922 --Check if the bucket short name is unique
923 SELECT COUNT(short_name) INTO l_short_count
924 FROM BIS_BUCKET
925 WHERE short_name = l_bis_bucket_rec.short_name;
926
927 IF (l_short_count > 0) THEN
928
929 l_error_tbl := x_error_tbl ;
930 BIS_UTILITIES_PVT.Add_Error_Message (
931 p_error_msg_name => 'BIS_INVALID_SHORT_NAME'
932 ,p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
933 ,p_error_proc_name => G_PKG_NAME||'.CREATE_BIS_BUCKET'
934 ,p_error_type => BIS_UTILITIES_PUB.G_ERROR
935 ,p_error_table => l_error_tbl
936 ,x_error_table => x_error_tbl
937 );
938 RAISE FND_API.G_EXC_ERROR;
939 END IF;
940
941 SELECT bis_bucket_s.nextval INTO l_bucket_id FROM dual;
942
943 INSERT into BIS_BUCKET (
944 BUCKET_ID,
945 SHORT_NAME,
946 TYPE,
947 APPLICATION_ID,
948 RANGE1_LOW,
949 RANGE1_HIGH,
950 RANGE2_LOW,
951 RANGE2_HIGH,
952 RANGE3_LOW,
953 RANGE3_HIGH,
954 RANGE4_LOW,
955 RANGE4_HIGH,
956 RANGE5_LOW,
957 RANGE5_HIGH,
958 RANGE6_LOW,
959 RANGE6_HIGH,
960 RANGE7_LOW,
961 RANGE7_HIGH,
962 RANGE8_LOW,
963 RANGE8_HIGH,
964 RANGE9_LOW,
965 RANGE9_HIGH,
966 RANGE10_LOW,
967 RANGE10_HIGH,
968 UPDATABLE,
969 EXPANDABLE,
970 DISCONTINUOUS,
971 OVERLAPPING,
972 UOM,
973 CREATION_DATE,
974 CREATED_BY,
975 LAST_UPDATE_DATE,
976 LAST_UPDATED_BY,
977 LAST_UPDATE_LOGIN
978 )
979 VALUES (
980 l_bucket_id,
981 l_bis_bucket_rec.short_name,
982 l_bis_bucket_rec.type,
983 l_bis_bucket_rec.application_id,
984 l_bis_bucket_rec.range1_low,
985 l_bis_bucket_rec.range1_high,
986 l_bis_bucket_rec.range2_low,
987 l_bis_bucket_rec.range2_high,
988 l_bis_bucket_rec.range3_low,
989 l_bis_bucket_rec.range3_high,
990 l_bis_bucket_rec.range4_low,
991 l_bis_bucket_rec.range4_high,
992 l_bis_bucket_rec.range5_low,
993 l_bis_bucket_rec.range5_high,
994 l_bis_bucket_rec.range6_low,
995 l_bis_bucket_rec.range6_high,
996 l_bis_bucket_rec.range7_low,
997 l_bis_bucket_rec.range7_high,
998 l_bis_bucket_rec.range8_low,
999 l_bis_bucket_rec.range8_high,
1000 l_bis_bucket_rec.range9_low,
1001 l_bis_bucket_rec.range9_high,
1002 l_bis_bucket_rec.range10_low,
1003 l_bis_bucket_rec.range10_high,
1004 NVL(l_bis_bucket_rec.updatable,'F'),
1005 NVL(l_bis_bucket_rec.expandable,'F'),
1009 SYSDATE,
1006 NVL(l_bis_bucket_rec.discontinuous,'F'),
1007 NVL(l_bis_bucket_rec.overlapping,'F'),
1008 l_bis_bucket_rec.uom,
1010 l_user_id,
1011 SYSDATE,
1012 l_user_id,
1013 l_login_id
1014 );
1015
1016 INSERT into BIS_BUCKET_TL (
1017 BUCKET_ID,
1018 LANGUAGE,
1019 NAME,
1020 RANGE1_NAME,
1021 RANGE2_NAME,
1022 RANGE3_NAME,
1023 RANGE4_NAME,
1024 RANGE5_NAME,
1025 RANGE6_NAME,
1026 RANGE7_NAME,
1027 RANGE8_NAME,
1028 RANGE9_NAME,
1029 RANGE10_NAME,
1030 DESCRIPTION,
1031 TRANSLATED,
1032 SOURCE_LANG,
1033 CREATION_DATE,
1034 CREATED_BY,
1035 LAST_UPDATE_DATE,
1036 LAST_UPDATED_BY,
1037 LAST_UPDATE_LOGIN
1038 )
1039 SELECT
1040 l_bucket_id,
1041 L.LANGUAGE_CODE,
1042 l_bis_bucket_rec.name,
1043 l_bis_bucket_rec.range1_name,
1044 l_bis_bucket_rec.range2_name,
1045 l_bis_bucket_rec.range3_name,
1046 l_bis_bucket_rec.range4_name,
1047 l_bis_bucket_rec.range5_name,
1048 l_bis_bucket_rec.range6_name,
1049 l_bis_bucket_rec.range7_name,
1050 l_bis_bucket_rec.range8_name,
1051 l_bis_bucket_rec.range9_name,
1052 l_bis_bucket_rec.range10_name,
1053 l_bis_bucket_rec.description,
1054 'Y',
1055 userenv('LANG'),
1056 SYSDATE,
1057 l_user_id,
1058 SYSDATE,
1059 l_user_id,
1060 l_login_id
1061 FROM FND_LANGUAGES L
1062 WHERE L.INSTALLED_FLAG in ('I', 'B')
1063 AND NOT exists (
1064 SELECT NULL
1065 FROM BIS_BUCKET_TL B
1066 WHERE B.BUCKET_ID = l_bucket_id
1067 AND B.LANGUAGE = L.LANGUAGE_CODE
1068 );
1069
1070 SELECT bis_bucket_customizations_s.nextval
1071 INTO l_id
1072 FROM DUAL;
1073
1074 INSERT INTO bis_bucket_customizations
1075 (ID,
1076 BUCKET_ID,
1077 RANGE1_LOW,
1078 RANGE1_HIGH,
1079 RANGE2_LOW,
1080 RANGE2_HIGH,
1081 RANGE3_LOW,
1082 RANGE3_HIGH,
1083 RANGE4_LOW,
1084 RANGE4_HIGH,
1085 RANGE5_LOW,
1086 RANGE5_HIGH,
1087 RANGE6_LOW,
1088 RANGE6_HIGH,
1089 RANGE7_LOW,
1090 RANGE7_HIGH,
1091 RANGE8_LOW,
1092 RANGE8_HIGH,
1093 RANGE9_LOW,
1094 RANGE9_HIGH,
1095 RANGE10_LOW,
1096 RANGE10_HIGH,
1097 CREATED_BY,
1098 CREATION_DATE,
1099 LAST_UPDATED_BY,
1100 LAST_UPDATE_DATE,
1101 LAST_UPDATE_LOGIN,
1102 CUSTOMIZED)
1103 VALUES
1104 ( l_id,
1105 l_bucket_id,
1106 l_bis_bucket_rec.RANGE1_LOW,
1107 l_bis_bucket_rec.RANGE1_HIGH,
1108 l_bis_bucket_rec.RANGE2_LOW,
1109 l_bis_bucket_rec.RANGE2_HIGH,
1110 l_bis_bucket_rec.RANGE3_LOW,
1111 l_bis_bucket_rec.RANGE3_HIGH,
1112 l_bis_bucket_rec.RANGE4_LOW,
1113 l_bis_bucket_rec.RANGE4_HIGH,
1114 l_bis_bucket_rec.RANGE5_LOW,
1115 l_bis_bucket_rec.RANGE5_HIGH,
1116 l_bis_bucket_rec.RANGE6_LOW,
1117 l_bis_bucket_rec.RANGE6_HIGH,
1118 l_bis_bucket_rec.RANGE7_LOW,
1119 l_bis_bucket_rec.RANGE7_HIGH,
1120 l_bis_bucket_rec.RANGE8_LOW,
1121 l_bis_bucket_rec.RANGE8_HIGH,
1122 l_bis_bucket_rec.RANGE9_LOW,
1123 l_bis_bucket_rec.RANGE9_HIGH,
1124 l_bis_bucket_rec.RANGE10_LOW,
1125 l_bis_bucket_rec.RANGE10_HIGH,
1126 l_user_id,
1127 SYSDATE,
1128 l_user_id,
1129 SYSDATE,
1130 l_login_id,
1131 'F');
1132
1133 INSERT INTO bis_bucket_customizations_tl
1134 (ID,
1135 RANGE1_NAME,
1136 RANGE2_NAME,
1137 RANGE3_NAME,
1138 RANGE4_NAME,
1139 RANGE5_NAME,
1140 RANGE6_NAME,
1141 RANGE7_NAME,
1142 RANGE8_NAME,
1143 RANGE9_NAME,
1144 RANGE10_NAME,
1145 LANGUAGE,
1146 TRANSLATED,
1147 SOURCE_LANG,
1148 CREATED_BY,
1149 CREATION_DATE,
1150 LAST_UPDATED_BY,
1151 LAST_UPDATE_DATE,
1152 LAST_UPDATE_LOGIN)
1153 SELECT
1154 l_id,
1155 l_bis_bucket_rec.range1_name,
1156 l_bis_bucket_rec.range2_name,
1157 l_bis_bucket_rec.range3_name,
1158 l_bis_bucket_rec.range4_name,
1159 l_bis_bucket_rec.range5_name,
1160 l_bis_bucket_rec.range6_name,
1161 l_bis_bucket_rec.range7_name,
1162 l_bis_bucket_rec.range8_name,
1163 l_bis_bucket_rec.range9_name,
1164 l_bis_bucket_rec.range10_name,
1165 L.LANGUAGE_CODE,
1166 'Y',
1167 userenv('LANG'),
1168 l_user_id,
1169 SYSDATE,
1170 l_user_id,
1171 SYSDATE,
1172 l_login_id
1173 from FND_LANGUAGES L
1174 where L.INSTALLED_FLAG in ('I', 'B')
1175 and not exists
1176 (select NULL
1177 from BIS_BUCKET_CUSTOMIZATIONS_TL T
1178 where T.ID = l_id
1179 and T.LANGUAGE = L.LANGUAGE_CODE);
1180
1181 COMMIT;
1182 x_return_status := FND_API.G_RET_STS_SUCCESS;
1183
1184 EXCEPTION
1185 WHEN FND_API.G_EXC_ERROR THEN
1186 x_return_status := FND_API.G_RET_STS_ERROR ;
1187 ROLLBACK TO SAVEPOINT SP_CREATE_BUCKET; -- can flow here when CREATE_BIS_BUCKET_TYPE() throws error
1188 RAISE FND_API.G_EXC_ERROR;
1189
1190 WHEN others THEN
1191 ROLLBACK TO SAVEPOINT SP_CREATE_BUCKET;
1195 BIS_UTILITIES_PVT.Add_Error_Message (
1192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1193
1194 l_error_tbl := x_error_tbl;
1196 p_error_msg_id => SQLCODE
1197 ,p_error_description => SQLERRM
1198 ,p_error_proc_name => G_PKG_NAME||'.CREATE_BIS_BUCKET'
1199 ,p_error_table => l_error_tbl
1200 ,x_error_table => x_error_tbl
1201 );
1202 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1203
1204 END CREATE_BIS_BUCKET;
1205
1206
1207
1208
1209 --This API should update the tables BIS_BUCKET, BIS_BUCKET_TL
1210 --using the short name or the bucket id as the where clause value
1211 PROCEDURE UPDATE_BIS_BUCKET (
1212 p_bis_bucket_rec IN BIS_BUCKET_PUB.bis_bucket_rec_type
1213 ,x_return_status OUT NOCOPY VARCHAR2
1214 ,x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1215 )
1216 IS
1217
1218 --l_bis_bucket_rec_orig BIS_BUCKET_PUB.bis_bucket_rec_type;
1219 l_bis_bucket_rec BIS_BUCKET_PUB.bis_bucket_rec_type;
1220 l_return_status VARCHAR2(10);
1221 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1222 l_user_id NUMBER;
1223 l_login_id NUMBER;
1224 l_ret_name VARCHAR2(200);
1225 l_ret_id VARCHAR2(200);
1226 l_short_name VARCHAR2(200);
1227 l_bis_type_count NUMBER;
1228 l_bucket_id NUMBER;
1229 l_id NUMBER;
1230 l_custom VARCHAR2(1);
1231 l_err VARCHAR2(32000);
1232
1233 BEGIN
1234 SAVEPOINT SP_UPDATE_BUCKET;
1235
1236 l_user_id := fnd_global.USER_ID;
1237 l_login_id := fnd_global.LOGIN_ID;
1238
1239 l_short_name := p_bis_bucket_rec.short_name;
1240
1241 l_ret_name := BIS_UTILITIES_PUB.Value_Missing(p_bis_bucket_rec.short_name);
1242 l_ret_id := BIS_UTILITIES_PUB.Value_Missing(p_bis_bucket_rec.bucket_id);
1243 IF (l_ret_name = FND_API.G_FALSE) THEN
1244 SELECT bucket_id, short_name INTO l_bucket_id, l_short_name
1245 FROM BIS_BUCKET
1246 WHERE SHORT_NAME = p_bis_bucket_rec.short_name;
1247 ELSIF (l_ret_id = FND_API.G_FALSE) THEN
1248 SELECT bucket_id, short_name INTO l_bucket_id, l_short_name
1249 FROM BIS_BUCKET
1250 WHERE BUCKET_ID = p_bis_bucket_rec.bucket_id;
1251 ELSE
1252 l_error_tbl := x_error_tbl ;
1253 BIS_UTILITIES_PVT.Add_Error_Message (
1254 p_error_msg_name => 'BIS_INVALID_SHORT_NAME'
1255 ,p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1256 ,p_error_proc_name => G_PKG_NAME||'.UPDATE_BIS_BUCKET'
1257 ,p_error_type => BIS_UTILITIES_PUB.G_ERROR
1258 ,p_error_table => l_error_tbl
1259 ,x_error_table => x_error_tbl
1260 );
1261 RAISE FND_API.G_EXC_ERROR;
1262 END IF;
1263
1264 BIS_BUCKET_PVT.Validate_Bucket (
1265 p_bis_bucket_rec => p_bis_bucket_rec
1266 ,x_return_status => x_return_status
1267 ,x_error_Tbl => x_error_tbl
1268 );
1269
1270 l_bis_bucket_rec.name := p_bis_bucket_rec.name;
1271 l_bis_bucket_rec.type := p_bis_bucket_rec.type;
1272 l_bis_bucket_rec.application_id := p_bis_bucket_rec.application_id;
1273 l_bis_bucket_rec.range1_name := p_bis_bucket_rec.range1_name;
1274 l_bis_bucket_rec.range1_low := p_bis_bucket_rec.range1_low;
1275 l_bis_bucket_rec.range1_high := p_bis_bucket_rec.range1_high;
1276 l_bis_bucket_rec.range2_name := p_bis_bucket_rec.range2_name;
1277 l_bis_bucket_rec.range2_low := p_bis_bucket_rec.range2_low;
1278 l_bis_bucket_rec.range2_high := p_bis_bucket_rec.range2_high;
1279 l_bis_bucket_rec.range3_name := p_bis_bucket_rec.range3_name;
1280 l_bis_bucket_rec.range3_low := p_bis_bucket_rec.range3_low;
1281 l_bis_bucket_rec.range3_high := p_bis_bucket_rec.range3_high;
1282 l_bis_bucket_rec.range4_name := p_bis_bucket_rec.range4_name;
1283 l_bis_bucket_rec.range4_low := p_bis_bucket_rec.range4_low;
1284 l_bis_bucket_rec.range4_high := p_bis_bucket_rec.range4_high;
1285 l_bis_bucket_rec.range5_name := p_bis_bucket_rec.range5_name;
1286 l_bis_bucket_rec.range5_low := p_bis_bucket_rec.range5_low;
1287 l_bis_bucket_rec.range5_high := p_bis_bucket_rec.range5_high;
1288 l_bis_bucket_rec.range6_name := p_bis_bucket_rec.range6_name;
1289 l_bis_bucket_rec.range6_low := p_bis_bucket_rec.range6_low;
1290 l_bis_bucket_rec.range6_high := p_bis_bucket_rec.range6_high;
1291 l_bis_bucket_rec.range7_name := p_bis_bucket_rec.range7_name;
1292 l_bis_bucket_rec.range7_low := p_bis_bucket_rec.range7_low;
1293 l_bis_bucket_rec.range7_high := p_bis_bucket_rec.range7_high;
1294 l_bis_bucket_rec.range8_name := p_bis_bucket_rec.range8_name;
1295 l_bis_bucket_rec.range8_low := p_bis_bucket_rec.range8_low;
1296 l_bis_bucket_rec.range8_high := p_bis_bucket_rec.range8_high;
1297 l_bis_bucket_rec.range9_name := p_bis_bucket_rec.range9_name;
1298 l_bis_bucket_rec.range9_low := p_bis_bucket_rec.range9_low;
1299 l_bis_bucket_rec.range9_high := p_bis_bucket_rec.range9_high;
1300 l_bis_bucket_rec.range10_name := p_bis_bucket_rec.range10_name;
1301 l_bis_bucket_rec.range10_low := p_bis_bucket_rec.range10_low;
1302 l_bis_bucket_rec.range10_high := p_bis_bucket_rec.range10_high;
1303 l_bis_bucket_rec.description := p_bis_bucket_rec.description;
1304 l_bis_bucket_rec.updatable := p_bis_bucket_rec.updatable;
1305 l_bis_bucket_rec.expandable := p_bis_bucket_rec.expandable;
1306 l_bis_bucket_rec.discontinuous := p_bis_bucket_rec.discontinuous;
1310
1307 l_bis_bucket_rec.overlapping := p_bis_bucket_rec.overlapping;
1308 l_bis_bucket_rec.uom := p_bis_bucket_rec.uom;
1309
1311 UPDATE BIS_BUCKET SET
1312 TYPE = l_bis_bucket_rec.type
1313 ,APPLICATION_ID = l_bis_bucket_rec.application_id
1314 ,RANGE1_LOW = l_bis_bucket_rec.range1_low
1315 ,RANGE1_HIGH = l_bis_bucket_rec.range1_high
1316 ,RANGE2_LOW = l_bis_bucket_rec.range2_low
1317 ,RANGE2_HIGH = l_bis_bucket_rec.range2_high
1318 ,RANGE3_LOW = l_bis_bucket_rec.range3_low
1319 ,RANGE3_HIGH = l_bis_bucket_rec.range3_high
1320 ,RANGE4_LOW = l_bis_bucket_rec.range4_low
1321 ,RANGE4_HIGH = l_bis_bucket_rec.range4_high
1322 ,RANGE5_LOW = l_bis_bucket_rec.range5_low
1323 ,RANGE5_HIGH = l_bis_bucket_rec.range5_high
1324 ,RANGE6_LOW = l_bis_bucket_rec.range6_low
1325 ,RANGE6_HIGH = l_bis_bucket_rec.range6_high
1326 ,RANGE7_LOW = l_bis_bucket_rec.range7_low
1327 ,RANGE7_HIGH = l_bis_bucket_rec.range7_high
1328 ,RANGE8_LOW = l_bis_bucket_rec.range8_low
1329 ,RANGE8_HIGH = l_bis_bucket_rec.range8_high
1330 ,RANGE9_LOW = l_bis_bucket_rec.range9_low
1331 ,RANGE9_HIGH = l_bis_bucket_rec.range9_high
1332 ,RANGE10_LOW = l_bis_bucket_rec.range10_low
1333 ,RANGE10_HIGH = l_bis_bucket_rec.range10_high
1334 ,UPDATABLE = NVL(l_bis_bucket_rec.updatable,'F')
1335 ,EXPANDABLE = NVL(l_bis_bucket_rec.expandable,'F')
1336 ,DISCONTINUOUS = NVL(l_bis_bucket_rec.discontinuous,'F')
1337 ,OVERLAPPING = NVL(l_bis_bucket_rec.overlapping,'F')
1338 ,UOM = l_bis_bucket_rec.uom
1339 ,LAST_UPDATE_DATE = SYSDATE
1340 ,LAST_UPDATED_BY = l_user_id
1341 ,LAST_UPDATE_LOGIN = l_login_id
1342 WHERE SHORT_NAME = l_short_name;
1343
1344 UPDATE BIS_BUCKET_TL SET
1345 NAME = l_bis_bucket_rec.name
1346 ,RANGE1_NAME = l_bis_bucket_rec.range1_name
1347 ,RANGE2_NAME = l_bis_bucket_rec.range2_name
1348 ,RANGE3_NAME = l_bis_bucket_rec.range3_name
1349 ,RANGE4_NAME = l_bis_bucket_rec.range4_name
1350 ,RANGE5_NAME = l_bis_bucket_rec.range5_name
1351 ,RANGE6_NAME = l_bis_bucket_rec.range6_name
1352 ,RANGE7_NAME = l_bis_bucket_rec.range7_name
1353 ,RANGE8_NAME = l_bis_bucket_rec.range8_name
1354 ,RANGE9_NAME = l_bis_bucket_rec.range9_name
1355 ,RANGE10_NAME = l_bis_bucket_rec.range10_name
1356 ,DESCRIPTION = l_bis_bucket_rec.description
1357 ,LAST_UPDATE_DATE = SYSDATE
1358 ,LAST_UPDATED_BY = l_user_id
1359 ,LAST_UPDATE_LOGIN = l_login_id
1360 ,SOURCE_LANG = userenv('LANG')
1361 WHERE BUCKET_ID = l_bucket_Id
1362 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
1363
1364 SELECT ID, CUSTOMIZED INTO l_id, l_custom
1365 FROM bis_bucket_customizations
1366 WHERE bucket_id = l_bucket_Id AND rownum < 2;
1367
1368 IF (l_custom = 'F') THEN --update cust tables
1369
1370 update BIS_BUCKET_CUSTOMIZATIONS set
1371 RANGE1_LOW = l_bis_bucket_rec.range1_low
1372 ,RANGE1_HIGH = l_bis_bucket_rec.range1_high
1373 ,RANGE2_LOW = l_bis_bucket_rec.range2_low
1374 ,RANGE2_HIGH = l_bis_bucket_rec.range2_high
1375 ,RANGE3_LOW = l_bis_bucket_rec.range3_low
1376 ,RANGE3_HIGH = l_bis_bucket_rec.range3_high
1377 ,RANGE4_LOW = l_bis_bucket_rec.range4_low
1378 ,RANGE4_HIGH = l_bis_bucket_rec.range4_high
1379 ,RANGE5_LOW = l_bis_bucket_rec.range5_low
1380 ,RANGE5_HIGH = l_bis_bucket_rec.range5_high
1381 ,RANGE6_LOW = l_bis_bucket_rec.range6_low
1382 ,RANGE6_HIGH = l_bis_bucket_rec.range6_high
1383 ,RANGE7_LOW = l_bis_bucket_rec.range7_low
1384 ,RANGE7_HIGH = l_bis_bucket_rec.range7_high
1385 ,RANGE8_LOW = l_bis_bucket_rec.range8_low
1386 ,RANGE8_HIGH = l_bis_bucket_rec.range8_high
1387 ,RANGE9_LOW = l_bis_bucket_rec.range9_low
1388 ,RANGE9_HIGH = l_bis_bucket_rec.range9_high
1389 ,RANGE10_LOW = l_bis_bucket_rec.range10_low
1390 ,RANGE10_HIGH = l_bis_bucket_rec.range10_high
1391 ,LAST_UPDATE_DATE = SYSDATE
1392 ,LAST_UPDATED_BY = l_user_id
1393 ,LAST_UPDATE_LOGIN = l_login_id
1394 where BUCKET_ID = l_bucket_Id;
1395
1396 update BIS_BUCKET_CUSTOMIZATIONS_TL set
1397 RANGE1_NAME = l_bis_bucket_rec.range1_name
1398 ,RANGE2_NAME = l_bis_bucket_rec.range2_name
1399 ,RANGE3_NAME = l_bis_bucket_rec.range3_name
1400 ,RANGE4_NAME = l_bis_bucket_rec.range4_name
1401 ,RANGE5_NAME = l_bis_bucket_rec.range5_name
1402 ,RANGE6_NAME = l_bis_bucket_rec.range6_name
1403 ,RANGE7_NAME = l_bis_bucket_rec.range7_name
1404 ,RANGE8_NAME = l_bis_bucket_rec.range8_name
1405 ,RANGE9_NAME = l_bis_bucket_rec.range9_name
1406 ,RANGE10_NAME = l_bis_bucket_rec.range10_name
1407 ,LAST_UPDATE_DATE = SYSDATE
1408 ,LAST_UPDATED_BY = l_user_id
1409 ,LAST_UPDATE_LOGIN = l_login_id
1410 ,SOURCE_LANG = userenv('LANG')
1411 where ID = l_id
1412 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1413
1414 END IF;
1415
1416 COMMIT;
1417 x_return_status := FND_API.G_RET_STS_SUCCESS;
1418
1419 EXCEPTION
1420 WHEN NO_DATA_FOUND THEN
1421 ROLLBACK TO SAVEPOINT SP_UPDATE_BUCKET;
1422 l_error_tbl := x_error_tbl;
1423 BIS_UTILITIES_PVT.Add_Error_Message (
1424 p_error_msg_name => 'BIS_INVALID_BUCKET_ID_NAME'
1425 ,p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1429 ,x_error_table => x_error_tbl
1426 ,p_error_proc_name => G_PKG_NAME||'.UPDATE_BIS_BUCKET'
1427 ,p_error_type => BIS_UTILITIES_PUB.G_ERROR
1428 ,p_error_table => l_error_tbl
1430 );
1431 x_return_status := FND_API.G_RET_STS_ERROR ;
1432 RAISE FND_API.G_EXC_ERROR;
1433 WHEN FND_API.G_EXC_ERROR THEN
1434 ROLLBACK TO SAVEPOINT SP_UPDATE_BUCKET;
1435 x_return_status := FND_API.G_RET_STS_ERROR ;
1436 RAISE FND_API.G_EXC_ERROR;
1437 WHEN others THEN
1438 ROLLBACK TO SAVEPOINT SP_UPDATE_BUCKET;
1439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1440 l_error_tbl := x_error_tbl;
1441 BIS_UTILITIES_PVT.Add_Error_Message (
1442 p_error_msg_id => SQLCODE
1443 ,p_error_description => SQLERRM
1444 ,p_error_proc_name => G_PKG_NAME||'.UPDATE_BIS_BUCKET'
1445 ,p_error_table => l_error_tbl
1446 ,x_error_table => x_error_tbl
1447 );
1448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1449
1450 END UPDATE_BIS_BUCKET;
1451
1452
1453 --This API should delete a row from the tables BIS_BUCKET and BIS_BUCKET_TL
1454 --using the short name or the bucket id as the where clause value
1455 --It should also delete the rows from BIS_BUCKET_CUSTOMIZATIONS and
1456 --BIS_BUCKET_CUSTOMIZATIONS_TL tables.
1457 PROCEDURE DELETE_BIS_BUCKET (
1458 p_bucket_id IN BIS_BUCKET.bucket_id%TYPE := BIS_UTILITIES_PUB.G_NULL_NUM
1459 ,p_short_name IN BIS_BUCKET.short_name%TYPE := BIS_UTILITIES_PUB.G_NULL_CHAR
1460 ,x_return_status OUT NOCOPY VARCHAR2
1461 ,x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1462 )
1463 IS
1464
1465 l_dcount NUMBER;
1466 l_ret_id VARCHAR2(200);
1467 l_ret_name VARCHAR2(200);
1468 l_bucket_id NUMBER;
1469 l_return_status VARCHAR2(10);
1470 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1471
1472 BEGIN
1473
1474 l_ret_name := BIS_UTILITIES_PUB.Value_Missing(p_short_name);
1475 l_ret_id := BIS_UTILITIES_PUB.Value_Missing(p_bucket_id);
1476
1477 IF (l_ret_name = FND_API.G_FALSE) THEN
1478 SELECT bucket_id INTO l_bucket_id FROM BIS_BUCKET
1479 WHERE SHORT_NAME = p_short_name;
1480
1481 DELETE from BIS_BUCKET_CUSTOMIZATIONS_TL
1482 WHERE id in (
1483 SELECT id
1484 FROM BIS_BUCKET_CUSTOMIZATIONS
1485 WHERE bucket_id = l_bucket_id);
1486
1487 DELETE from BIS_BUCKET_CUSTOMIZATIONS
1488 WHERE bucket_id = l_bucket_id;
1489
1490 DELETE from BIS_BUCKET_TL
1491 WHERE bucket_id = l_bucket_id;
1492
1493 DELETE from BIS_BUCKET
1494 WHERE short_name = p_short_name;
1495
1496 ELSIF (l_ret_id = FND_API.G_FALSE) THEN
1497 SELECT count(BUCKET_ID) INTO l_dcount FROM BIS_BUCKET
1498 WHERE BUCKET_ID = p_bucket_id;
1499
1500 IF (l_dcount = 0) THEN
1501 l_error_tbl := x_error_tbl ;
1502 BIS_UTILITIES_PVT.Add_Error_Message (
1503 p_error_msg_name => 'BIS_INVALID_BUCKET_ID'
1504 ,p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1505 ,p_error_proc_name => G_PKG_NAME||'.DELETE_BUCKET'
1506 ,p_error_type => BIS_UTILITIES_PUB.G_ERROR
1507 ,p_error_table => l_error_tbl
1508 ,x_error_table => x_error_tbl
1509 );
1510 RAISE FND_API.G_EXC_ERROR;
1511 ELSE
1512 DELETE from BIS_BUCKET_CUSTOMIZATIONS_TL
1513 WHERE id in (
1514 SELECT id
1515 FROM BIS_BUCKET_CUSTOMIZATIONS
1516 WHERE bucket_id = p_bucket_id);
1517
1518 DELETE from BIS_BUCKET_CUSTOMIZATIONS
1519 WHERE bucket_id = p_bucket_id;
1520
1521 DELETE from BIS_BUCKET_TL
1522 WHERE bucket_id = p_bucket_id;
1523
1524 DELETE from BIS_BUCKET
1525 WHERE bucket_id = p_bucket_id;
1526
1527 END IF;
1528 ELSE
1529 l_error_tbl := x_error_tbl ;
1530 BIS_UTILITIES_PVT.Add_Error_Message (
1531 p_error_msg_name => 'BIS_INVALID_BUCKET_ID_NAME'
1532 ,p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1533 ,p_error_proc_name => G_PKG_NAME||'.DELETE_BUCKET'
1534 ,p_error_type => BIS_UTILITIES_PUB.G_ERROR
1535 ,p_error_table => l_error_tbl
1536 ,x_error_table => x_error_tbl
1537 );
1538 RAISE FND_API.G_EXC_ERROR;
1539 END IF;
1540
1541 COMMIT;
1542 x_return_status := FND_API.G_RET_STS_SUCCESS;
1543
1544 EXCEPTION
1545 WHEN NO_DATA_FOUND THEN
1546 x_return_status := FND_API.G_RET_STS_ERROR ;
1547 l_error_tbl := x_error_tbl ;
1548 BIS_UTILITIES_PVT.Add_Error_Message (
1549 p_error_msg_name => 'BIS_INVALID_SHORT_NAME'
1550 ,p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1551 ,p_error_proc_name => G_PKG_NAME||'.DELETE_BUCKET'
1552 ,p_error_type => BIS_UTILITIES_PUB.G_ERROR
1553 ,p_error_table => l_error_tbl
1554 ,x_error_table => x_error_tbl
1555 );
1556 RAISE FND_API.G_EXC_ERROR;
1557 WHEN FND_API.G_EXC_ERROR THEN
1558 x_return_status := FND_API.G_RET_STS_ERROR ;
1559 RAISE FND_API.G_EXC_ERROR;
1560
1561 WHEN others THEN
1562 ROLLBACK;
1563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1564
1565 l_error_tbl := x_error_tbl;
1569 ,p_error_proc_name => G_PKG_NAME||'.DELETE_BIS_BUCKET'
1566 BIS_UTILITIES_PVT.Add_Error_Message (
1567 p_error_msg_id => SQLCODE
1568 ,p_error_description => SQLERRM
1570 ,p_error_table => l_error_tbl
1571 ,x_error_table => x_error_tbl
1572 );
1573 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1574
1575 END DELETE_BIS_BUCKET;
1576
1577
1578 --This API should populate a record of type bis_bucket_rec_type based on the bucket short name
1579 -- Modified for bug #3394457 , ankgoel
1580 -- Retrieve the record always from the customization table
1581 PROCEDURE RETRIEVE_BIS_BUCKET (
1582 p_short_name IN BIS_BUCKET.short_name%TYPE := BIS_UTILITIES_PUB.G_NULL_CHAR
1583 ,x_bis_bucket_rec OUT NOCOPY BIS_BUCKET_PUB.bis_bucket_rec_type
1584 ,x_return_status OUT NOCOPY VARCHAR2
1585 ,x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1586 )
1587 IS
1588
1589 l_ret_name VARCHAR2(200);
1590 l_dcount NUMBER;
1591 l_return_status VARCHAR2(10);
1592 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1593
1594
1595 CURSOR c_bucket_cust (p_short_name VARCHAR2) IS
1596 SELECT
1597 BC.BUCKET_ID,
1598 B.NAME,
1599 B.TYPE,
1600 BC.APPLICATION_ID,
1601 BC.RANGE1_NAME,
1602 BC.RANGE1_LOW,
1603 BC.RANGE1_HIGH,
1604 BC.RANGE2_NAME,
1605 BC.RANGE2_LOW,
1606 BC.RANGE2_HIGH,
1607 BC.RANGE3_NAME,
1608 BC.RANGE3_LOW,
1609 BC.RANGE3_HIGH,
1610 BC.RANGE4_NAME,
1611 BC.RANGE4_LOW,
1612 BC.RANGE4_HIGH,
1613 BC.RANGE5_NAME,
1614 BC.RANGE5_LOW,
1615 BC.RANGE5_HIGH,
1616 BC.RANGE6_NAME,
1617 BC.RANGE6_LOW,
1618 BC.RANGE6_HIGH,
1619 BC.RANGE7_NAME,
1620 BC.RANGE7_LOW,
1621 BC.RANGE7_HIGH,
1622 BC.RANGE8_NAME,
1623 BC.RANGE8_LOW,
1624 BC.RANGE8_HIGH,
1625 BC.RANGE9_NAME,
1626 BC.RANGE9_LOW,
1627 BC.RANGE9_HIGH,
1628 BC.RANGE10_NAME,
1629 BC.RANGE10_LOW,
1630 BC.RANGE10_HIGH,
1631 B.DESCRIPTION,
1632 B.UPDATABLE,
1633 B.EXPANDABLE,
1634 B.DISCONTINUOUS,
1635 B.OVERLAPPING,
1636 B.UOM
1637 FROM BIS_BUCKET_CUSTOMIZATIONS_VL BC,BIS_BUCKET_VL B
1638 WHERE B.SHORT_NAME = p_short_name
1639 AND B.BUCKET_ID=BC.BUCKET_ID;
1640
1641
1642 BEGIN
1643
1644 l_ret_name := BIS_UTILITIES_PUB.Value_Missing(p_short_name);
1645
1646 SELECT count(SHORT_NAME) INTO l_dcount FROM BIS_BUCKET
1647 WHERE SHORT_NAME = p_short_name;
1648
1649 IF ((l_ret_name = FND_API.G_TRUE) OR (l_dcount = 0)) THEN
1650 l_error_tbl := x_error_tbl ;
1651 BIS_UTILITIES_PVT.Add_Error_Message (
1652 p_error_msg_name => 'BIS_INVALID_SHORT_NAME'
1653 ,p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1654 ,p_error_proc_name => G_PKG_NAME||'.DELETE_BUCKET'
1655 ,p_error_type => BIS_UTILITIES_PUB.G_ERROR
1656 ,p_error_table => l_error_tbl
1657 ,x_error_table => x_error_tbl
1658 );
1659 RAISE FND_API.G_EXC_ERROR;
1660 END IF;
1661
1662 -- Fix for bug #3339143
1663
1664 OPEN c_bucket_cust (p_short_name => p_short_name) ;
1665
1666 FETCH c_bucket_cust INTO
1667 x_bis_bucket_rec.bucket_id
1668 ,x_bis_bucket_rec.name
1669 ,x_bis_bucket_rec.type
1670 ,x_bis_bucket_rec.application_id
1671 ,x_bis_bucket_rec.range1_name
1672 ,x_bis_bucket_rec.range1_low
1673 ,x_bis_bucket_rec.range1_high
1674 ,x_bis_bucket_rec.range2_name
1675 ,x_bis_bucket_rec.range2_low
1676 ,x_bis_bucket_rec.range2_high
1677 ,x_bis_bucket_rec.range3_name
1678 ,x_bis_bucket_rec.range3_low
1679 ,x_bis_bucket_rec.range3_high
1680 ,x_bis_bucket_rec.range4_name
1681 ,x_bis_bucket_rec.range4_low
1682 ,x_bis_bucket_rec.range4_high
1683 ,x_bis_bucket_rec.range5_name
1684 ,x_bis_bucket_rec.range5_low
1685 ,x_bis_bucket_rec.range5_high
1686 ,x_bis_bucket_rec.range6_name
1687 ,x_bis_bucket_rec.range6_low
1688 ,x_bis_bucket_rec.range6_high
1689 ,x_bis_bucket_rec.range7_name
1690 ,x_bis_bucket_rec.range7_low
1691 ,x_bis_bucket_rec.range7_high
1692 ,x_bis_bucket_rec.range8_name
1693 ,x_bis_bucket_rec.range8_low
1694 ,x_bis_bucket_rec.range8_high
1695 ,x_bis_bucket_rec.range9_name
1696 ,x_bis_bucket_rec.range9_low
1697 ,x_bis_bucket_rec.range9_high
1698 ,x_bis_bucket_rec.range10_name
1699 ,x_bis_bucket_rec.range10_low
1700 ,x_bis_bucket_rec.range10_high
1701 ,x_bis_bucket_rec.description
1702 ,x_bis_bucket_rec.updatable
1703 ,x_bis_bucket_rec.expandable
1704 ,x_bis_bucket_rec.discontinuous
1705 ,x_bis_bucket_rec.overlapping
1706 ,x_bis_bucket_rec.uom;
1707
1708
1709 IF (c_bucket_cust%ISOPEN) THEN
1710 CLOSE c_bucket_cust;
1711 END IF;
1712 x_return_status := FND_API.G_RET_STS_SUCCESS;
1713
1714 EXCEPTION
1715 WHEN FND_API.G_EXC_ERROR THEN
1716 IF (c_bucket_cust%ISOPEN) THEN
1720 RAISE FND_API.G_EXC_ERROR;
1717 CLOSE c_bucket_cust;
1718 END IF;
1719 x_return_status := FND_API.G_RET_STS_ERROR ;
1721 WHEN others THEN
1722 IF (c_bucket_cust%ISOPEN) THEN
1723 CLOSE c_bucket_cust;
1724 END IF;
1725 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1726
1727 l_error_tbl := x_error_tbl;
1728 BIS_UTILITIES_PVT.Add_Error_Message (
1729 p_error_msg_id => SQLCODE
1730 ,p_error_description => SQLERRM
1731 ,p_error_proc_name => G_PKG_NAME||'.RETRIEVE_BIS_BUCKET'
1732 ,p_error_table => l_error_tbl
1733 ,x_error_table => x_error_tbl
1734 );
1735
1736 END RETRIEVE_BIS_BUCKET;
1737
1738 PROCEDURE TRANSLATE_BUCKET (
1739 p_bis_bucket_rec IN BIS_BUCKET_PUB.bis_bucket_rec_type
1740 ,p_owner IN VARCHAR2
1741 ,x_return_status OUT NOCOPY VARCHAR2
1742 ,x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1743 )
1744 IS
1745
1746 l_user_id NUMBER;
1747 l_login_id NUMBER;
1748 l_bis_bucket_rec BIS_BUCKET_PUB.bis_bucket_rec_type;
1749 l_bis_bucket_rec_orig BIS_BUCKET_PUB.bis_bucket_rec_type;
1750 l_return_status VARCHAR2(10);
1751 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1752
1753 BEGIN
1754
1755 l_bis_bucket_rec := p_bis_bucket_rec;
1756
1757 BIS_BUCKET_PVT.RETRIEVE_BIS_BUCKET (
1758 p_short_name => l_bis_bucket_rec.short_name
1759 ,x_bis_bucket_rec => l_bis_bucket_rec_orig
1760 ,x_return_status => l_return_status
1761 ,x_error_tbl => l_error_tbl
1762 );
1763
1764 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1765 x_error_tbl := l_error_tbl;
1766 RAISE FND_API.G_EXC_ERROR;
1767 END IF;
1768
1769 l_bis_bucket_rec.bucket_id := l_bis_bucket_rec_orig.bucket_id;
1770
1771 IF (p_owner = BIS_UTILITIES_PUB.G_SEED_OWNER) THEN
1772 l_user_id := BIS_UTILITIES_PUB.G_SEED_USER_ID;
1773 ELSE
1774 l_user_id := fnd_global.user_id;
1775 END IF;
1776
1777 l_login_id := fnd_global.LOGIN_ID;
1778
1779 UPDATE BIS_BUCKET_TL
1780 SET
1781 NAME = l_bis_bucket_rec.name
1782 ,RANGE1_NAME = l_bis_bucket_rec.range1_name
1783 ,RANGE2_NAME = l_bis_bucket_rec.range2_name
1784 ,RANGE3_NAME = l_bis_bucket_rec.range3_name
1785 ,RANGE4_NAME = l_bis_bucket_rec.range4_name
1786 ,RANGE5_NAME = l_bis_bucket_rec.range5_name
1787 ,RANGE6_NAME = l_bis_bucket_rec.range6_name
1788 ,RANGE7_NAME = l_bis_bucket_rec.range7_name
1789 ,RANGE8_NAME = l_bis_bucket_rec.range8_name
1790 ,RANGE9_NAME = l_bis_bucket_rec.range9_name
1791 ,RANGE10_NAME = l_bis_bucket_rec.range10_name
1792 ,DESCRIPTION = l_bis_bucket_rec.description
1793 ,LAST_UPDATE_DATE = SYSDATE
1794 ,LAST_UPDATED_BY = l_user_id
1795 ,LAST_UPDATE_LOGIN = l_login_id
1796 ,SOURCE_LANG = userenv('LANG')
1797 WHERE BUCKET_ID = l_bis_bucket_rec.bucket_id
1798 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
1799
1800 UPDATE BIS_BUCKET_CUSTOMIZATIONS_TL
1801 SET
1802 RANGE1_NAME = l_bis_bucket_rec.range1_name
1803 ,RANGE2_NAME = l_bis_bucket_rec.range2_name
1804 ,RANGE3_NAME = l_bis_bucket_rec.range3_name
1805 ,RANGE4_NAME = l_bis_bucket_rec.range4_name
1806 ,RANGE5_NAME = l_bis_bucket_rec.range5_name
1807 ,RANGE6_NAME = l_bis_bucket_rec.range6_name
1808 ,RANGE7_NAME = l_bis_bucket_rec.range7_name
1809 ,RANGE8_NAME = l_bis_bucket_rec.range8_name
1810 ,RANGE9_NAME = l_bis_bucket_rec.range9_name
1811 ,RANGE10_NAME = l_bis_bucket_rec.range10_name
1812 ,LAST_UPDATE_DATE = SYSDATE
1813 ,LAST_UPDATED_BY = l_user_id
1814 ,LAST_UPDATE_LOGIN = l_login_id
1815 ,SOURCE_LANG = userenv('LANG')
1816 WHERE id in (
1817 SELECT id
1818 FROM BIS_BUCKET_CUSTOMIZATIONS
1819 WHERE bucket_id = l_bis_bucket_rec.bucket_id)
1820 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
1821
1822 COMMIT;
1823 x_return_status := FND_API.G_RET_STS_SUCCESS;
1824
1825 EXCEPTION
1826 WHEN FND_API.G_EXC_ERROR THEN
1827 x_return_status := FND_API.G_RET_STS_ERROR ;
1828
1829 WHEN OTHERS THEN
1830 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1831
1832 l_error_tbl := x_error_tbl;
1833 BIS_UTILITIES_PVT.Add_Error_Message(
1834 p_error_msg_id => SQLCODE
1835 ,p_error_description => SQLERRM
1836 ,p_error_proc_name => G_PKG_NAME||'.Translate_bucket'
1837 ,p_error_table => l_error_tbl
1838 ,x_error_table => x_error_tbl
1839 );
1840
1841 END TRANSLATE_BUCKET;
1842
1843
1844 FUNCTION CHECK_RANGE_NAME (
1845 p_bis_bucket_rec IN BIS_BUCKET_PUB.bis_bucket_rec_type
1846 ) RETURN BOOLEAN
1847 IS
1848
1849 TYPE range_name_t IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
1850 l_range_name range_name_t;
1851 l_hash_value binary_integer;
1852
1853 BEGIN
1854
1855 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range1_name)= FND_API.G_TRUE) THEN
1856 l_hash_value :=Dbms_Utility.Get_Hash_Value (
1857 name => p_bis_bucket_rec.range1_name
1858 ,base => 2
1859 ,hash_size => 1048576
1860 );
1861
1865 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range2_name)= FND_API.G_TRUE) THEN
1862 l_range_name(l_hash_value) := p_bis_bucket_rec.range1_name;
1863 END IF;
1864
1866 l_hash_value:=Dbms_Utility.Get_Hash_Value (
1867 name => p_bis_bucket_rec.range2_name
1868 ,base => 2
1869 ,hash_size => 1048576
1870 );
1871
1872 IF (l_range_name.exists(l_hash_value)) THEN
1873 RAISE FND_API.G_EXC_ERROR;
1874 ELSE
1875 l_range_name(l_hash_value) := p_bis_bucket_rec.range2_name;
1876 END IF;
1877 END IF;
1878
1879 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range3_name)= FND_API.G_TRUE) THEN
1880 l_hash_value:=Dbms_Utility.Get_Hash_Value (
1881 name => p_bis_bucket_rec.range3_name
1882 ,base => 2
1883 ,hash_size => 1048576
1884 );
1885
1886 IF (l_range_name.exists(l_hash_value)) THEN
1887 RAISE FND_API.G_EXC_ERROR;
1888 ELSE
1889 l_range_name(l_hash_value) := p_bis_bucket_rec.range3_name;
1890 END IF;
1891 END IF;
1892
1893
1894 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range4_name)= FND_API.G_TRUE) THEN
1895 l_hash_value:=Dbms_Utility.Get_Hash_Value (
1896 name => p_bis_bucket_rec.range4_name
1897 ,base => 2
1898 ,hash_size => 1048576
1899 );
1900
1901 IF (l_range_name.exists(l_hash_value)) THEN
1902 RAISE FND_API.G_EXC_ERROR;
1903 ELSE
1904 l_range_name(l_hash_value) := p_bis_bucket_rec.range4_name;
1905 END IF;
1906 END IF;
1907
1908
1909 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range5_name)= FND_API.G_TRUE) THEN
1910 l_hash_value:=Dbms_Utility.Get_Hash_Value (
1911 name => p_bis_bucket_rec.range5_name
1912 ,base => 2
1913 ,hash_size => 1048576
1914 );
1915
1916 IF (l_range_name.exists(l_hash_value)) THEN
1917 RAISE FND_API.G_EXC_ERROR;
1918 ELSE
1919 l_range_name(l_hash_value) := p_bis_bucket_rec.range5_name;
1920 END IF;
1921 END IF;
1922
1923
1924 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range6_name)= FND_API.G_TRUE) THEN
1925 l_hash_value:=Dbms_Utility.Get_Hash_Value (
1926 name => p_bis_bucket_rec.range6_name
1927 ,base => 2
1928 ,hash_size => 1048576
1929 );
1930
1931 IF (l_range_name.exists(l_hash_value)) THEN
1932 RAISE FND_API.G_EXC_ERROR;
1933 ELSE
1934 l_range_name(l_hash_value) := p_bis_bucket_rec.range6_name;
1935 END IF;
1936 END IF;
1937
1938
1939 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range7_name)= FND_API.G_TRUE) THEN
1940 l_hash_value:=Dbms_Utility.Get_Hash_Value (
1941 name => p_bis_bucket_rec.range7_name
1942 ,base => 2
1943 ,hash_size => 1048576
1944 );
1945
1946 IF (l_range_name.exists(l_hash_value)) THEN
1947 RAISE FND_API.G_EXC_ERROR;
1948 ELSE
1949 l_range_name(l_hash_value) := p_bis_bucket_rec.range7_name;
1950 END IF;
1951 END IF;
1952
1953
1954 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range8_name)= FND_API.G_TRUE) THEN
1955 l_hash_value:=Dbms_Utility.Get_Hash_Value (
1956 name => p_bis_bucket_rec.range8_name
1957 ,base => 2
1958 ,hash_size => 1048576
1959 );
1960
1961 IF (l_range_name.exists(l_hash_value)) THEN
1962 RAISE FND_API.G_EXC_ERROR;
1963 ELSE
1964 l_range_name(l_hash_value) := p_bis_bucket_rec.range8_name;
1965 END IF;
1966 END IF;
1967
1968
1969 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range9_name)= FND_API.G_TRUE) THEN
1970 l_hash_value:=Dbms_Utility.Get_Hash_Value (
1971 name => p_bis_bucket_rec.range9_name
1972 ,base => 2
1973 ,hash_size => 1048576
1974 );
1975
1976 IF (l_range_name.exists(l_hash_value)) THEN
1977 RAISE FND_API.G_EXC_ERROR;
1978 ELSE
1979 l_range_name(l_hash_value) := p_bis_bucket_rec.range9_name;
1980 END IF;
1981 END IF;
1982
1983
1984 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range10_name)= FND_API.G_TRUE) THEN
1985 l_hash_value:=Dbms_Utility.Get_Hash_Value (
1986 name => p_bis_bucket_rec.range10_name
1987 ,base => 2
1988 ,hash_size => 1048576
1989 );
1990
1991 IF (l_range_name.exists(l_hash_value)) THEN
1992 RAISE FND_API.G_EXC_ERROR;
1993 ELSE
1994 l_range_name(l_hash_value) := p_bis_bucket_rec.range10_name;
1995 END IF;
1996 END IF;
1997
1998
1999 RETURN TRUE;
2000
2001 EXCEPTION
2002 WHEN FND_API.G_EXC_ERROR THEN
2003 RETURN FALSE;
2004
2005 WHEN others THEN
2006 RETURN FALSE;
2007
2008 END CHECK_RANGE_NAME;
2009
2010 --=============================================================================
2011 FUNCTION CHECK_RANGE_VAL_LOW (
2015
2012 p_bis_bucket_rec IN BIS_BUCKET_PUB.bis_bucket_rec_type
2013 ) RETURN BOOLEAN
2014 IS
2016 TYPE range_low_t IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
2017 l_range_low range_low_t;
2018 l_hash_value binary_integer;
2019
2020 BEGIN
2021 IF((p_bis_bucket_rec.overlapping ='T') OR (p_bis_bucket_rec.discontinuous='T')) THEN
2022 RETURN TRUE;
2023 END IF;
2024
2025 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range1_low)= FND_API.G_TRUE) THEN
2026 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2027 name => p_bis_bucket_rec.range1_low
2028 ,base => 2
2029 ,hash_size => 1048576
2030 );
2031
2032 IF (l_range_low.exists(l_hash_value)) THEN
2033 RAISE FND_API.G_EXC_ERROR;
2034 ELSE
2035 l_range_low(l_hash_value) := p_bis_bucket_rec.range1_low;
2036 END IF;
2037 END IF;
2038
2039 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range2_low)= FND_API.G_TRUE) THEN
2040 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2041 name => p_bis_bucket_rec.range2_low
2042 ,base => 2
2043 ,hash_size => 1048576
2044 );
2045
2046 IF (l_range_low.exists(l_hash_value)) THEN
2047 RAISE FND_API.G_EXC_ERROR;
2048 ELSE
2049 l_range_low(l_hash_value) := p_bis_bucket_rec.range2_low;
2050 END IF;
2051 END IF;
2052
2053 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range3_low)= FND_API.G_TRUE) THEN
2054 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2055 name => p_bis_bucket_rec.range3_low
2056 ,base => 2
2057 ,hash_size => 1048576
2058 );
2059
2060 IF (l_range_low.exists(l_hash_value)) THEN
2061 RAISE FND_API.G_EXC_ERROR;
2062 ELSE
2063 l_range_low(l_hash_value) := p_bis_bucket_rec.range3_low;
2064 END IF;
2065 END IF;
2066
2067 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range4_low)= FND_API.G_TRUE) THEN
2068 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2069 name => p_bis_bucket_rec.range4_low
2070 ,base => 2
2071 ,hash_size => 1048576
2072 );
2073
2074 IF (l_range_low.exists(l_hash_value)) THEN
2075 RAISE FND_API.G_EXC_ERROR;
2076 ELSE
2077 l_range_low(l_hash_value) := p_bis_bucket_rec.range4_low;
2078 END IF;
2079 END IF;
2080
2081 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range5_low)= FND_API.G_TRUE) THEN
2082 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2083 name => p_bis_bucket_rec.range5_low
2084 ,base => 2
2085 ,hash_size => 1048576
2086 );
2087
2088 IF (l_range_low.exists(l_hash_value)) THEN
2089 RAISE FND_API.G_EXC_ERROR;
2090 ELSE
2091 l_range_low(l_hash_value) := p_bis_bucket_rec.range5_low;
2092 END IF;
2093 END IF;
2094
2095 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range6_low)= FND_API.G_TRUE) THEN
2096 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2097 name => p_bis_bucket_rec.range6_low
2098 ,base => 2
2099 ,hash_size => 1048576
2100 );
2101
2102 IF (l_range_low.exists(l_hash_value)) THEN
2103 RAISE FND_API.G_EXC_ERROR;
2104 ELSE
2105 l_range_low(l_hash_value) := p_bis_bucket_rec.range6_low;
2106 END IF;
2107 END IF;
2108
2109 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range7_low)= FND_API.G_TRUE) THEN
2110 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2111 name => p_bis_bucket_rec.range7_low
2112 ,base => 2
2113 ,hash_size => 1048576
2114 );
2115
2116 IF (l_range_low.exists(l_hash_value)) THEN
2117 RAISE FND_API.G_EXC_ERROR;
2118 ELSE
2119 l_range_low(l_hash_value) := p_bis_bucket_rec.range7_low;
2120 END IF;
2121 END IF;
2122
2123 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range8_low)= FND_API.G_TRUE) THEN
2124 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2125 name => p_bis_bucket_rec.range8_low
2126 ,base => 2
2127 ,hash_size => 1048576
2128 );
2129
2130 IF (l_range_low.exists(l_hash_value)) THEN
2131 RAISE FND_API.G_EXC_ERROR;
2132 ELSE
2133 l_range_low(l_hash_value) := p_bis_bucket_rec.range8_low;
2134 END IF;
2135 END IF;
2136
2137 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range9_low)= FND_API.G_TRUE) THEN
2138 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2139 name => p_bis_bucket_rec.range9_low
2140 ,base => 2
2141 ,hash_size => 1048576
2142 );
2143
2144 IF (l_range_low.exists(l_hash_value)) THEN
2145 RAISE FND_API.G_EXC_ERROR;
2146 ELSE
2147 l_range_low(l_hash_value) := p_bis_bucket_rec.range9_low;
2148 END IF;
2152 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2149 END IF;
2150
2151 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range10_low)= FND_API.G_TRUE) THEN
2153 name => p_bis_bucket_rec.range10_low
2154 ,base => 2
2155 ,hash_size => 1048576
2156 );
2157
2158 IF (l_range_low.exists(l_hash_value)) THEN
2159 RAISE FND_API.G_EXC_ERROR;
2160 ELSE
2161 l_range_low(l_hash_value) := p_bis_bucket_rec.range10_low;
2162 END IF;
2163 END IF;
2164
2165 RETURN TRUE;
2166
2167 EXCEPTION
2168 WHEN FND_API.G_EXC_ERROR THEN
2169 RETURN FALSE;
2170
2171 WHEN others THEN
2172 RETURN FALSE;
2173
2174 END CHECK_RANGE_VAL_LOW;
2175 --=============================================================================
2176 FUNCTION CHECK_RANGE_VAL_HIGH (
2177 p_bis_bucket_rec IN BIS_BUCKET_PUB.bis_bucket_rec_type
2178 ) RETURN BOOLEAN
2179 IS
2180
2181 TYPE range_high_t IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
2182 l_range_high range_high_t;
2183 l_hash_value binary_integer;
2184
2185 BEGIN
2186 IF((p_bis_bucket_rec.overlapping ='T') OR (p_bis_bucket_rec.discontinuous='T')) THEN
2187 RETURN TRUE;
2188 END IF;
2189 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range1_high)= FND_API.G_TRUE) THEN
2190 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2191 name => p_bis_bucket_rec.range1_high
2192 ,base => 2
2193 ,hash_size => 1048576
2194 );
2195
2196 IF (l_range_high.exists(l_hash_value)) THEN
2197 RAISE FND_API.G_EXC_ERROR;
2198 ELSE
2199 l_range_high(l_hash_value) := p_bis_bucket_rec.range1_high;
2200 END IF;
2201 END IF;
2202
2203 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range2_high)= FND_API.G_TRUE) THEN
2204 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2205 name => p_bis_bucket_rec.range2_high
2206 ,base => 2
2207 ,hash_size => 1048576
2208 );
2209
2210 IF (l_range_high.exists(l_hash_value)) THEN
2211 RAISE FND_API.G_EXC_ERROR;
2212 ELSE
2213 l_range_high(l_hash_value) := p_bis_bucket_rec.range2_high;
2214 END IF;
2215 END IF;
2216
2217 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range3_high)= FND_API.G_TRUE) THEN
2218 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2219 name => p_bis_bucket_rec.range3_high
2220 ,base => 2
2221 ,hash_size => 1048576
2222 );
2223
2224 IF (l_range_high.exists(l_hash_value)) THEN
2225 RAISE FND_API.G_EXC_ERROR;
2226 ELSE
2227 l_range_high(l_hash_value) := p_bis_bucket_rec.range3_high;
2228 END IF;
2229 END IF;
2230
2231 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range4_high)= FND_API.G_TRUE) THEN
2232 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2233 name => p_bis_bucket_rec.range4_high
2234 ,base => 2
2235 ,hash_size => 1048576
2236 );
2237
2238 IF (l_range_high.exists(l_hash_value)) THEN
2239 RAISE FND_API.G_EXC_ERROR;
2240 ELSE
2241 l_range_high(l_hash_value) := p_bis_bucket_rec.range4_high;
2242 END IF;
2243 END IF;
2244
2245 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range5_high)= FND_API.G_TRUE) THEN
2246 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2247 name => p_bis_bucket_rec.range5_high
2248 ,base => 2
2249 ,hash_size => 1048576
2250 );
2251
2252 IF (l_range_high.exists(l_hash_value)) THEN
2253 RAISE FND_API.G_EXC_ERROR;
2254 ELSE
2255 l_range_high(l_hash_value) := p_bis_bucket_rec.range5_high;
2256 END IF;
2257 END IF;
2258
2259 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range6_high)= FND_API.G_TRUE) THEN
2260 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2261 name => p_bis_bucket_rec.range6_high
2262 ,base => 2
2263 ,hash_size => 1048576
2264 );
2265
2266 IF (l_range_high.exists(l_hash_value)) THEN
2267 RAISE FND_API.G_EXC_ERROR;
2268 ELSE
2269 l_range_high(l_hash_value) := p_bis_bucket_rec.range6_high;
2270 END IF;
2271 END IF;
2272
2273 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range7_high)= FND_API.G_TRUE) THEN
2274 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2275 name => p_bis_bucket_rec.range7_high
2276 ,base => 2
2277 ,hash_size => 1048576
2278 );
2279
2280 IF (l_range_high.exists(l_hash_value)) THEN
2281 RAISE FND_API.G_EXC_ERROR;
2282 ELSE
2283 l_range_high(l_hash_value) := p_bis_bucket_rec.range7_high;
2284 END IF;
2285 END IF;
2286
2287 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range8_high)= FND_API.G_TRUE) THEN
2288 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2292 );
2289 name => p_bis_bucket_rec.range8_high
2290 ,base => 2
2291 ,hash_size => 1048576
2293
2294 IF (l_range_high.exists(l_hash_value)) THEN
2295 RAISE FND_API.G_EXC_ERROR;
2296 ELSE
2297 l_range_high(l_hash_value) := p_bis_bucket_rec.range8_high;
2298 END IF;
2299 END IF;
2300
2301 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range9_high)= FND_API.G_TRUE) THEN
2302 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2303 name => p_bis_bucket_rec.range9_high
2304 ,base => 2
2305 ,hash_size => 1048576
2306 );
2307
2308 IF (l_range_high.exists(l_hash_value)) THEN
2309 RAISE FND_API.G_EXC_ERROR;
2310 ELSE
2311 l_range_high(l_hash_value) := p_bis_bucket_rec.range9_high;
2312 END IF;
2313 END IF;
2314
2315 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range10_high)= FND_API.G_TRUE) THEN
2316 l_hash_value :=DBMS_UTILITY.GET_HASH_VALUE (
2317 name => p_bis_bucket_rec.range10_high
2318 ,base => 2
2319 ,hash_size => 1048576
2320 );
2321
2322 IF (l_range_high.exists(l_hash_value)) THEN
2323 RAISE FND_API.G_EXC_ERROR;
2324 ELSE
2325 l_range_high(l_hash_value) := p_bis_bucket_rec.range10_high;
2326 END IF;
2327 END IF;
2328
2329 RETURN TRUE;
2330
2331 EXCEPTION
2332 WHEN FND_API.G_EXC_ERROR THEN
2333 RETURN FALSE;
2334
2335 WHEN others THEN
2336 RETURN FALSE;
2337
2338 END CHECK_RANGE_VAL_HIGH;
2339 --=============================================================================
2340 -- Each bucket type is a lookup code
2341 -- All lookup codes are under 'BIS_BUCKET_TYPE' lookup type
2342
2343 FUNCTION IS_BUCKET_TYPE_EXISTS (
2344 p_bucket_type IN VARCHAR2
2345 ) RETURN BOOLEAN IS
2346
2347 l_dummy NUMBER;
2348
2349 CURSOR c_fnd_lookups (cp_lookup_code VARCHAR2) IS
2350 SELECT 1
2351 FROM fnd_lookup_types a, fnd_lookup_values b
2352 WHERE UPPER(b.lookup_code) = UPPER(cp_lookup_code)
2353 AND b.lookup_type = a.lookup_type
2354 AND a.lookup_type = 'BIS_BUCKET_TYPE';
2355
2356 BEGIN
2357
2358 IF (c_fnd_lookups%ISOPEN) THEN
2359 CLOSE c_fnd_lookups;
2360 END IF;
2361
2362 OPEN c_fnd_lookups(cp_lookup_code => p_bucket_type);
2363 FETCH c_fnd_lookups INTO l_dummy;
2364 IF (c_fnd_lookups%NOTFOUND) THEN
2365 CLOSE c_fnd_lookups;
2366 RETURN FALSE;
2367 ELSE
2368 CLOSE c_fnd_lookups;
2369 RETURN TRUE;
2370 END IF;
2371
2372 EXCEPTION
2373 WHEN OTHERS THEN
2374 IF (c_fnd_lookups%ISOPEN) THEN
2375 CLOSE c_fnd_lookups;
2376 END IF;
2377 RETURN FALSE;
2378
2379 END IS_BUCKET_TYPE_EXISTS;
2380 --=============================================================================
2381 PROCEDURE Validate_Bucket (
2382 p_bis_bucket_rec IN BIS_BUCKET_PUB.bis_bucket_rec_type
2383 ,x_return_status OUT NOCOPY VARCHAR2
2384 ,x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2385 ) IS
2386
2387 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2388 l_bis_bucket_rec BIS_BUCKET_PUB.bis_bucket_rec_type;
2389
2390 l_bucket_ranges_tbl BIS_BUCKET_PVT.BIS_BUCKET_RANGES_TBL;
2391
2392 BEGIN
2393
2394 x_return_status := FND_API.G_RET_STS_SUCCESS;
2395 FND_MSG_PUB.Initialize;
2396
2397 l_bis_bucket_rec := p_bis_bucket_rec;
2398
2399 IF NOT (IS_VALID_APPLICATION_ID(p_application_id => l_bis_bucket_rec.application_id)) THEN
2400 l_error_tbl := x_error_tbl;
2401 BIS_UTILITIES_PVT.Add_Error_Message (
2402 p_error_msg_name => 'BIS_INVALID_APPLICATION_ID'
2403 ,p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2404 ,p_error_proc_name => G_PKG_NAME||'.CREATE_BIS_BUCKET_APPLICATION_ID'
2405 ,p_error_type => BIS_UTILITIES_PUB.G_ERROR
2406 ,p_error_table => l_error_tbl
2407 ,x_error_table => x_error_tbl
2408 );
2409 RAISE FND_API.G_EXC_ERROR;
2410 END IF;
2411
2412 BIS_BUCKET_PVT.Validate_Bucket_Common (
2413 p_bis_bucket_rec => l_bis_bucket_rec
2414 ,x_return_status => x_return_status
2415 ,x_error_Tbl => x_error_tbl
2416 );
2417
2418 EXCEPTION
2419 WHEN FND_API.G_EXC_ERROR THEN
2420 x_return_status := FND_API.G_RET_STS_ERROR ;
2421 --dbms_output.put_line( 'x_return_status (Not wrapper) is ' || x_return_status);
2422 RAISE FND_API.G_EXC_ERROR;
2423 WHEN OTHERS THEN
2424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2426 END Validate_Bucket;
2427 --=============================================================================
2428
2429 FUNCTION GET_REPORT_LISTS(
2430 p_bucket_short_name IN VARCHAR2 DEFAULT NULL
2431 ,p_bucket_id IN NUMBER DEFAULT NULL
2432 ) return VARCHAR2
2433 IS
2434
2435 l_bucket_short_name VARCHAR2(30);
2436 l_reports_name VARCHAR2(32000);
2437 l_count NUMBER;
2438
2439 cursor c_akregion (p1 varchar2, p2 varchar2) IS
2440 SELECT region_code
2441 FROM ak_region_items
2442 WHERE (attribute1 = p1
2443 OR attribute1 IS NULL)
2444 AND attribute2 = p2
2445 GROUP BY region_code;
2446
2447 -- Added filter critera type='WWW' for enh #3471325
2448 cursor c_formfunction (p1 varchar2) IS
2449 SELECT user_function_name
2450 FROM fnd_form_functions_vl
2451 WHERE parameters like '%pRegionCode=%' -- For perf tuning.
2452 AND parameters like '%pRegionCode=' || p1 || '&%'
2453 AND type = 'WWW';
2454
2455 --Fix for bug #3488336
2456 -- Added filter critera type='WWW' for enh #3471325
2457 cursor c_webfunction (p1 varchar2) IS
2458 SELECT user_function_name
2459 FROM fnd_form_functions_vl
2460 WHERE upper(web_html_call) like 'BISVIEWER.SHOWREPORT(''' || p1 || '''%'
2461 AND type = 'WWW';
2462
2463 BEGIN
2464
2465 l_reports_name := '';
2466 l_count := 0;
2467
2468 IF ((p_bucket_short_name is NULL) AND (p_bucket_id is NULL)) THEN
2469 return '';
2470 END IF;
2471
2472 IF (p_bucket_short_name is NULL) THEN
2473 select short_name into l_bucket_short_name
2474 FROM bis_bucket
2475 WHERE bucket_id = p_bucket_id;
2476 ELSE
2477 l_bucket_short_name := p_bucket_short_name;
2478 END IF;
2479
2480 FOR akr in c_akregion(c_bucket_att, l_bucket_short_name) LOOP
2481
2482 FOR ffv in c_formfunction(akr.region_code) LOOP
2483
2484 IF (l_count = 0) THEN
2485 l_reports_name := l_reports_name || ffv.user_function_name;
2486 ELSE
2487 l_reports_name := l_reports_name || ', ' || ffv.user_function_name;
2488 END IF;
2489
2490 l_count := l_count + 1;
2491
2492 END LOOP;
2493 -- Fix #3488336
2494 FOR wbh in c_webfunction(akr.region_code) LOOP
2495
2496 IF (l_count = 0) THEN
2497 l_reports_name := l_reports_name || wbh.user_function_name;
2498 ELSE
2499 l_reports_name := l_reports_name || ', ' || wbh.user_function_name;
2500 END IF;
2501
2502 l_count := l_count + 1;
2503
2504 END LOOP;
2505
2506 END LOOP;
2507 RETURN l_reports_name;
2508
2509 EXCEPTION
2510
2511 WHEN OTHERS THEN -- if no such table exists
2512 RETURN '';
2513 END GET_REPORT_LISTS;
2514
2515 --=============================================================================
2516 --API for populating the table of records with low and high range values
2517 --Needed for range validations -- overlappig and discontinous.
2518 -- If the bucket has no label, it doesn't count as a valid bucket.
2519 --=============================================================================
2520 PROCEDURE Populate_Loc_Bucket_Range_Tbl
2521 (
2522 p_bis_bucket_rec IN BIS_BUCKET_PUB.bis_bucket_rec_type
2523 ,x_bucket_ranges_tbl OUT NOCOPY BIS_BUCKET_PVT.bis_bucket_ranges_tbl
2524 ,x_return_status OUT NOCOPY VARCHAR2
2525 ,x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2526 )
2527 IS
2528 l_valid_bucket_count NUMBER := 0;
2529 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2530
2531 BEGIN
2532
2533 x_return_status := FND_API.G_RET_STS_SUCCESS;
2534
2535 IF ((BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range1_name)= FND_API.G_TRUE)
2536 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range1_low)= FND_API.G_TRUE)
2537 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range1_high)= FND_API.G_TRUE)) THEN
2538 x_bucket_ranges_tbl(l_valid_bucket_count).range_low := p_bis_bucket_rec.range1_low;
2539 x_bucket_ranges_tbl(l_valid_bucket_count).range_high := p_bis_bucket_rec.range1_high;
2540 l_valid_bucket_count := l_valid_bucket_count + 1;
2541 END IF;
2542
2543 IF ((BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range2_name)= FND_API.G_TRUE)
2544 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range2_low)= FND_API.G_TRUE)
2545 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range2_high)= FND_API.G_TRUE)) THEN
2546 x_bucket_ranges_tbl(l_valid_bucket_count).range_low := p_bis_bucket_rec.range2_low;
2547 x_bucket_ranges_tbl(l_valid_bucket_count).range_high := p_bis_bucket_rec.range2_high;
2548 l_valid_bucket_count := l_valid_bucket_count + 1;
2549 END IF;
2550
2551 IF ((BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range3_name)= FND_API.G_TRUE)
2552 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range3_low)= FND_API.G_TRUE)
2553 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range3_high)= FND_API.G_TRUE)) THEN
2554 x_bucket_ranges_tbl(l_valid_bucket_count).range_low := p_bis_bucket_rec.range3_low;
2555 x_bucket_ranges_tbl(l_valid_bucket_count).range_high := p_bis_bucket_rec.range3_high;
2556 l_valid_bucket_count := l_valid_bucket_count + 1;
2557 END IF;
2558
2559 IF ((BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range4_name)= FND_API.G_TRUE)
2560 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range4_low)= FND_API.G_TRUE)
2561 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range4_high)= FND_API.G_TRUE)) THEN
2565 END IF;
2562 x_bucket_ranges_tbl(l_valid_bucket_count).range_low := p_bis_bucket_rec.range4_low;
2563 x_bucket_ranges_tbl(l_valid_bucket_count).range_high := p_bis_bucket_rec.range4_high;
2564 l_valid_bucket_count := l_valid_bucket_count + 1;
2566
2567 IF ((BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range5_name)= FND_API.G_TRUE)
2568 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range5_low)= FND_API.G_TRUE)
2569 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range5_high)= FND_API.G_TRUE)) THEN
2570 x_bucket_ranges_tbl(l_valid_bucket_count).range_low := p_bis_bucket_rec.range5_low;
2571 x_bucket_ranges_tbl(l_valid_bucket_count).range_high := p_bis_bucket_rec.range5_high;
2572 l_valid_bucket_count := l_valid_bucket_count + 1;
2573 END IF;
2574
2575 IF ((BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range6_name)= FND_API.G_TRUE)
2576 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range6_low)= FND_API.G_TRUE)
2577 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range6_high)= FND_API.G_TRUE)) THEN
2578 x_bucket_ranges_tbl(l_valid_bucket_count).range_low := p_bis_bucket_rec.range6_low;
2579 x_bucket_ranges_tbl(l_valid_bucket_count).range_high := p_bis_bucket_rec.range6_high;
2580 l_valid_bucket_count := l_valid_bucket_count + 1;
2581 END IF;
2582
2583 IF ((BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range7_name)= FND_API.G_TRUE)
2584 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range7_low)= FND_API.G_TRUE)
2585 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range7_high)= FND_API.G_TRUE)) THEN
2586 x_bucket_ranges_tbl(l_valid_bucket_count).range_low := p_bis_bucket_rec.range7_low;
2587 x_bucket_ranges_tbl(l_valid_bucket_count).range_high := p_bis_bucket_rec.range7_high;
2588 l_valid_bucket_count := l_valid_bucket_count + 1;
2589 END IF;
2590
2591 IF ((BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range8_name)= FND_API.G_TRUE)
2592 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range8_low)= FND_API.G_TRUE)
2593 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range8_high)= FND_API.G_TRUE)) THEN
2594 x_bucket_ranges_tbl(l_valid_bucket_count).range_low := p_bis_bucket_rec.range8_low;
2595 x_bucket_ranges_tbl(l_valid_bucket_count).range_high := p_bis_bucket_rec.range8_high;
2596 l_valid_bucket_count := l_valid_bucket_count + 1;
2597 END IF;
2598
2599 IF ((BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range9_name)= FND_API.G_TRUE)
2600 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range9_low)= FND_API.G_TRUE)
2601 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range9_high)= FND_API.G_TRUE)) THEN
2602 x_bucket_ranges_tbl(l_valid_bucket_count).range_low := p_bis_bucket_rec.range9_low;
2603 x_bucket_ranges_tbl(l_valid_bucket_count).range_high := p_bis_bucket_rec.range9_high;
2604 l_valid_bucket_count := l_valid_bucket_count + 1;
2605 END IF;
2606
2607 IF ((BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range10_name)= FND_API.G_TRUE)
2608 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range10_low)= FND_API.G_TRUE)
2609 OR (BIS_UTILITIES_PUB.Value_Not_Missing(p_bis_bucket_rec.range10_high)= FND_API.G_TRUE)) THEN
2610 x_bucket_ranges_tbl(l_valid_bucket_count).range_low := p_bis_bucket_rec.range10_low;
2611 x_bucket_ranges_tbl(l_valid_bucket_count).range_high := p_bis_bucket_rec.range10_high;
2612 END IF;
2613
2614
2615 EXCEPTION
2616 WHEN OTHERS THEN
2617 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2618 BIS_UTILITIES_PVT.Add_Error_Message (
2619 p_error_msg_id => SQLCODE
2620 ,p_error_description => SQLERRM
2621 ,p_error_proc_name => G_PKG_NAME||'.Populate_Loc_Bucket_Range_Tbl'
2622 ,p_error_table => l_error_tbl
2623 ,x_error_table => x_error_tbl
2624 );
2625 RAISE FND_API.G_EXC_ERROR;
2626 END Populate_Loc_Bucket_Range_Tbl;
2627
2628 --=============================================================================
2629 --API for validating the overlapping feature of the bucket
2630 -- If it allows overlapping, no validation is needed.
2631 -- Validation is only needed if it doesn't allow overlapping.
2632 --=============================================================================
2633 PROCEDURE Validate_Bucket_Overlapping (
2634 p_overlapping IN VARCHAR2
2635 ,p_bucket_ranges_tbl IN BIS_BUCKET_PVT.bis_bucket_ranges_tbl
2636 ,x_return_status OUT NOCOPY VARCHAR2
2637 ,x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2638 )
2639 IS
2640
2641 l_bucket_count NUMBER := 0;
2642 l_error_code VARCHAR2(1);
2643 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2644
2645 BEGIN
2646
2647 x_return_status := FND_API.G_RET_STS_SUCCESS;
2648 l_bucket_count := p_bucket_ranges_tbl.COUNT();
2649
2650 IF (p_overlapping = 'F') THEN
2651 FOR buckets IN 1 .. l_bucket_count - 1 LOOP
2652 --dbms_output.put_line( 'bucket number(overlapping) is ' || buckets);
2653 --dbms_output.put_line( 'bucket.range_low is ' || p_bucket_ranges_tbl(buckets).RANGE_LOW);
2654 --dbms_output.put_line( 'bucket-1.range_high is ' || p_bucket_ranges_tbl(buckets-1).RANGE_HIGH);
2655 IF (p_bucket_ranges_tbl(buckets).RANGE_LOW IS NULL) THEN
2656 RAISE FND_API.G_EXC_ERROR;
2657 ELSIF (p_bucket_ranges_tbl(buckets - 1).RANGE_HIGH IS NULL) THEN
2658 RAISE FND_API.G_EXC_ERROR;
2659 --ELSIF ((p_bucket_ranges_tbl(buckets).RANGE_LOW IS NOT NULL) AND (p_bucket_ranges_tbl(buckets - 1).RANGE_HIGH IS NOT NULL)) THEN
2660 ELSIF (p_bucket_ranges_tbl(buckets).RANGE_LOW < p_bucket_ranges_tbl(buckets - 1).RANGE_HIGH) THEN
2661 RAISE FND_API.G_EXC_ERROR;
2662 END IF;
2663 END LOOP;
2664 END IF;
2665
2666
2667 EXCEPTION
2668 WHEN FND_API.G_EXC_ERROR THEN -- overlapping occurs
2669 x_return_status := FND_API.G_RET_STS_ERROR;
2673 ,p_error_proc_name => G_PKG_NAME||'.Validate_Bucket_Overlapping'
2670 BIS_UTILITIES_PVT.Add_Error_Message (
2671 p_error_msg_name => 'BIS_BUCKET_SET_OVERLAPPING'
2672 ,p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2674 ,p_error_type => BIS_UTILITIES_PUB.G_ERROR
2675 ,p_error_table => l_error_tbl
2676 ,x_error_table => x_error_tbl
2677 );
2678 RAISE FND_API.G_EXC_ERROR;
2679 WHEN OTHERS THEN
2680 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2681 BIS_UTILITIES_PVT.Add_Error_Message (
2682 p_error_msg_id => SQLCODE
2683 ,p_error_description => SQLERRM
2684 ,p_error_proc_name => G_PKG_NAME||'.Validate_Bucket_Overlapping'
2685 ,p_error_table => l_error_tbl
2686 ,x_error_table => x_error_tbl
2687 );
2688 RAISE FND_API.G_EXC_ERROR;
2689 END Validate_Bucket_Overlapping;
2690
2691
2692 --=============================================================================
2693 --API for validating the discontinuous feature of the bucket
2694 -- If it allows discontinuous, no validation is needed.
2695 -- Validation is only needed if it doesn't allow discontinuous.
2696 --=============================================================================
2697 PROCEDURE Validate_Bucket_Discontinuous (
2698 p_discontinuous IN VARCHAR2
2699 ,p_bucket_ranges_tbl IN BIS_BUCKET_PVT.bis_bucket_ranges_tbl
2700 ,x_return_status OUT NOCOPY VARCHAR2
2701 ,x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2702 )
2703 IS
2704
2705 l_bucket_count NUMBER := 0;
2706 l_error_code VARCHAR2(1);
2707 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2708
2709 BEGIN
2710
2711 x_return_status := FND_API.G_RET_STS_SUCCESS;
2712 l_bucket_count := p_bucket_ranges_tbl.COUNT();
2713
2714 IF (p_discontinuous = 'F') THEN
2715 FOR buckets IN 1 .. l_bucket_count - 1 LOOP
2716 --dbms_output.put_line( 'bucket number(discontinuous) is ' || buckets);
2717 --dbms_output.put_line( 'bucket.range_low is ' || p_bucket_ranges_tbl(buckets).RANGE_LOW);
2718 --dbms_output.put_line( 'bucket-1.range_high is ' || p_bucket_ranges_tbl(buckets-1).RANGE_HIGH);
2719 IF (p_bucket_ranges_tbl(buckets).RANGE_LOW IS NULL) THEN
2720 RAISE FND_API.G_EXC_ERROR;
2721 ELSIF (p_bucket_ranges_tbl(buckets - 1).RANGE_HIGH IS NULL) THEN
2722 RAISE FND_API.G_EXC_ERROR;
2723 ELSIF (p_bucket_ranges_tbl(buckets).RANGE_LOW <> p_bucket_ranges_tbl(buckets-1).RANGE_HIGH) THEN
2724 RAISE FND_API.G_EXC_ERROR;
2725 END IF;
2726 END LOOP;
2727 END IF;
2728
2729 EXCEPTION
2730 WHEN FND_API.G_EXC_ERROR THEN -- discontinuous occurs
2731 x_return_status := FND_API.G_RET_STS_ERROR;
2732 BIS_UTILITIES_PVT.Add_Error_Message (
2733 p_error_msg_name => 'BIS_BUCKET_SET_DISCONTINUOUS'
2734 ,p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2735 ,p_error_proc_name => G_PKG_NAME||'.Validate_Bucket_Discontinuous'
2736 ,p_error_type => BIS_UTILITIES_PUB.G_ERROR
2737 ,p_error_table => l_error_tbl
2738 ,x_error_table => x_error_tbl
2739 );
2740 RAISE FND_API.G_EXC_ERROR;
2741 WHEN OTHERS THEN
2742 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2743 BIS_UTILITIES_PVT.Add_Error_Message (
2744 p_error_msg_id => SQLCODE
2745 ,p_error_description => SQLERRM
2746 ,p_error_proc_name => G_PKG_NAME||'.Validate_Bucket_Discontinuous'
2747 ,p_error_table => l_error_tbl
2748 ,x_error_table => x_error_tbl
2749 );
2750 RAISE FND_API.G_EXC_ERROR;
2751 END Validate_Bucket_Discontinuous;
2752
2753 --=============================================================================
2754 --API for validating that the FROM is always less than or equal to TO
2755 --=============================================================================
2756 PROCEDURE Validate_From_To (
2757 p_bucket_ranges_tbl IN BIS_BUCKET_PVT.bis_bucket_ranges_tbl
2758 ,x_return_status OUT NOCOPY VARCHAR2
2759 ,x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2760 )
2761 IS
2762
2763 l_bucket_count NUMBER := 0;
2764 l_error_code VARCHAR2(1);
2765 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2766 l_error_message VARCHAR2(50);
2767
2768 BEGIN
2769
2770 x_return_status := FND_API.G_RET_STS_SUCCESS;
2771 l_bucket_count := p_bucket_ranges_tbl.COUNT();
2772
2773 FOR buckets IN 0 .. l_bucket_count - 1 LOOP
2774 IF ((p_bucket_ranges_tbl(buckets).RANGE_LOW IS NULL) AND (p_bucket_ranges_tbl(buckets).RANGE_HIGH) IS NULL) THEN
2775 l_error_message := 'BIS_BUCKET_NULL';
2776 RAISE FND_API.G_EXC_ERROR;
2777 ELSIF (p_bucket_ranges_tbl(buckets).RANGE_LOW > p_bucket_ranges_tbl(buckets).RANGE_HIGH) THEN
2778 l_error_message := 'BIS_BUCKET_FROM_TO';
2779 RAISE FND_API.G_EXC_ERROR;
2780 END IF;
2781 END LOOP;
2782
2783 EXCEPTION
2784 WHEN FND_API.G_EXC_ERROR THEN -- FROM > TO
2785 x_return_status := FND_API.G_RET_STS_ERROR;
2786 BIS_UTILITIES_PVT.Add_Error_Message (
2787 p_error_msg_name => l_error_message
2788 ,p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2789 ,p_error_proc_name => G_PKG_NAME||'.Validate_From_To'
2790 ,p_error_type => BIS_UTILITIES_PUB.G_ERROR
2791 ,p_error_table => l_error_tbl
2792 ,x_error_table => x_error_tbl
2793 );
2794 RAISE FND_API.G_EXC_ERROR;
2795 WHEN OTHERS THEN
2796 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2797 BIS_UTILITIES_PVT.Add_Error_Message (
2798 p_error_msg_id => SQLCODE
2799 ,p_error_description => SQLERRM
2800 ,p_error_proc_name => G_PKG_NAME||'.Validate_From_To'
2801 ,p_error_table => l_error_tbl
2802 ,x_error_table => x_error_tbl
2806
2803 );
2804 RAISE FND_API.G_EXC_ERROR;
2805 END Validate_From_To;
2807 --=============================================================================
2808 --This is the java wrapper for bucket customization UI.
2809 PROCEDURE VALIDATE_BUCKET_WRAPPER (
2810 p_short_name IN BIS_BUCKET.short_name%TYPE
2811 ,p_name IN BIS_BUCKET_TL.name%TYPE
2812 ,p_type IN BIS_BUCKET.type%TYPE
2813 ,p_application_id IN BIS_BUCKET.application_id%TYPE
2814 ,p_range1_name IN BIS_BUCKET_TL.range1_name%TYPE
2815 ,p_range1_low IN BIS_BUCKET.range1_low%TYPE
2816 ,p_range1_high IN BIS_BUCKET.range1_high%TYPE
2817 ,p_range2_name IN BIS_BUCKET_TL.range2_name%TYPE
2818 ,p_range2_low IN BIS_BUCKET.range2_low%TYPE
2819 ,p_range2_high IN BIS_BUCKET.range2_high%TYPE
2820 ,p_range3_name IN BIS_BUCKET_TL.range3_name%TYPE
2821 ,p_range3_low IN BIS_BUCKET.range3_low%TYPE
2822 ,p_range3_high IN BIS_BUCKET.range3_high%TYPE
2823 ,p_range4_name IN BIS_BUCKET_TL.range4_name%TYPE
2824 ,p_range4_low IN BIS_BUCKET.range4_low%TYPE
2825 ,p_range4_high IN BIS_BUCKET.range4_high%TYPE
2826 ,p_range5_name IN BIS_BUCKET_TL.range5_name%TYPE
2827 ,p_range5_low IN BIS_BUCKET.range5_low%TYPE
2828 ,p_range5_high IN BIS_BUCKET.range5_high%TYPE
2829 ,p_range6_name IN BIS_BUCKET_TL.range6_name%TYPE
2830 ,p_range6_low IN BIS_BUCKET.range6_low%TYPE
2831 ,p_range6_high IN BIS_BUCKET.range6_high%TYPE
2832 ,p_range7_name IN BIS_BUCKET_TL.range7_name%TYPE
2833 ,p_range7_low IN BIS_BUCKET.range7_low%TYPE
2834 ,p_range7_high IN BIS_BUCKET.range7_high%TYPE
2835 ,p_range8_name IN BIS_BUCKET_TL.range8_name%TYPE
2836 ,p_range8_low IN BIS_BUCKET.range8_low%TYPE
2837 ,p_range8_high IN BIS_BUCKET.range8_high%TYPE
2838 ,p_range9_name IN BIS_BUCKET_TL.range9_name%TYPE
2839 ,p_range9_low IN BIS_BUCKET.range9_low%TYPE
2840 ,p_range9_high IN BIS_BUCKET.range9_high%TYPE
2841 ,p_range10_name IN BIS_BUCKET_TL.range10_name%TYPE
2842 ,p_range10_low IN BIS_BUCKET.range10_low%TYPE
2843 ,p_range10_high IN BIS_BUCKET.range10_high%TYPE
2844 ,p_description IN BIS_BUCKET_TL.description%TYPE
2845 ,p_updatable IN BIS_BUCKET.updatable%TYPE := 'F'
2846 ,p_expandable IN BIS_BUCKET.expandable%TYPE := 'F'
2847 ,p_discontinuous IN BIS_BUCKET.discontinuous%TYPE := 'F'
2848 ,p_overlapping IN BIS_BUCKET.overlapping%TYPE := 'F'
2849 ,p_uom IN BIS_BUCKET.uom%TYPE
2850 ,x_return_status OUT NOCOPY VARCHAR2
2851 ,x_error_msg OUT NOCOPY VARCHAR2
2852 )
2853 IS
2854
2855 l_bis_bucket_rec BIS_BUCKET_PUB.bis_bucket_rec_type;
2856 l_return_status VARCHAR2(10);
2857 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2858
2859 BEGIN
2860
2861 l_bis_bucket_rec.short_name := p_short_name;
2862 l_bis_bucket_rec.name := p_name;
2863 l_bis_bucket_rec.type := p_type;
2864 l_bis_bucket_rec.application_id := p_application_id;
2865 l_bis_bucket_rec.range1_name := p_range1_name;
2866 l_bis_bucket_rec.range1_low := p_range1_low;
2867 l_bis_bucket_rec.range1_high := p_range1_high;
2868 l_bis_bucket_rec.range2_name := p_range2_name;
2869 l_bis_bucket_rec.range2_low := p_range2_low;
2870 l_bis_bucket_rec.range2_high := p_range2_high;
2871 l_bis_bucket_rec.range3_name := p_range3_name;
2872 l_bis_bucket_rec.range3_low := p_range3_low;
2873 l_bis_bucket_rec.range3_high := p_range3_high;
2874 l_bis_bucket_rec.range4_name := p_range4_name;
2875 l_bis_bucket_rec.range4_low := p_range4_low;
2876 l_bis_bucket_rec.range4_high := p_range4_high;
2877 l_bis_bucket_rec.range5_name := p_range5_name;
2878 l_bis_bucket_rec.range5_low := p_range5_low;
2879 l_bis_bucket_rec.range5_high := p_range5_high;
2880 l_bis_bucket_rec.range6_name := p_range6_name;
2881 l_bis_bucket_rec.range6_low := p_range6_low;
2882 l_bis_bucket_rec.range6_high := p_range6_high;
2883 l_bis_bucket_rec.range7_name := p_range7_name;
2884 l_bis_bucket_rec.range7_low := p_range7_low;
2885 l_bis_bucket_rec.range7_high := p_range7_high;
2886 l_bis_bucket_rec.range8_name := p_range8_name;
2887 l_bis_bucket_rec.range8_low := p_range8_low;
2888 l_bis_bucket_rec.range8_high := p_range8_high;
2889 l_bis_bucket_rec.range9_name := p_range9_name;
2890 l_bis_bucket_rec.range9_low := p_range9_low;
2891 l_bis_bucket_rec.range9_high := p_range9_high;
2892 l_bis_bucket_rec.range10_name := p_range10_name;
2893 l_bis_bucket_rec.range10_low := p_range10_low;
2894 l_bis_bucket_rec.range10_high := p_range10_high;
2895 l_bis_bucket_rec.description := p_description;
2896 l_bis_bucket_rec.updatable := p_updatable;
2897 l_bis_bucket_rec.expandable := p_expandable;
2898 l_bis_bucket_rec.discontinuous := p_discontinuous;
2899 l_bis_bucket_rec.overlapping := p_overlapping;
2900 l_bis_bucket_rec.uom := p_uom;
2901
2902 BIS_BUCKET_PVT.Validate_Bucket_Common (
2903 p_bis_bucket_rec => l_bis_bucket_rec
2904 ,x_return_status => l_return_status
2905 ,x_error_Tbl => l_error_tbl
2906 );
2907
2908 x_return_status := l_return_status;
2909
2910 IF (l_error_tbl.EXISTS(1)) THEN
2911 x_error_msg := l_error_tbl(1).Error_Msg_Name;
2912 END IF;
2913
2914 EXCEPTION
2915 WHEN FND_API.G_EXC_ERROR THEN
2916 x_return_status := FND_API.G_RET_STS_ERROR ;
2917 IF (l_error_tbl.EXISTS(1)) THEN
2918 x_error_msg := l_error_tbl(1).Error_Msg_Name;
2919 END IF;
2920 WHEN OTHERS THEN
2921 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2922 IF (l_error_tbl.EXISTS(1)) THEN
2923 x_error_msg := l_error_tbl(1).Error_Msg_Name;
2924 END IF;
2925 END VALIDATE_BUCKET_WRAPPER;
2926
2927 FUNCTION IS_VALID_APPLICATION_ID (
2928 p_application_id IN NUMBER
2929 ) RETURN BOOLEAN
2930 IS
2931
2932 l_count NUMBER;
2933
2934 BEGIN
2935 SELECT COUNT(application_id) into l_count
2936 FROM FND_APPLICATION
2937 WHERE APPLICATION_ID = p_application_id;
2941 ELSE
2938
2939 IF (l_count = 0) THEN
2940 RETURN FALSE;
2942 RETURN TRUE;
2943 END IF;
2944
2945 EXCEPTION
2946 WHEN OTHERS THEN
2947 RETURN FALSE;
2948
2949 END IS_VALID_APPLICATION_ID;
2950
2951 --=============================================================================
2952 --The common validation that good for bucket. (Normal, Customized)
2953 PROCEDURE Validate_Bucket_Common (
2954 p_bis_bucket_rec IN BIS_BUCKET_PUB.bis_bucket_rec_type
2955 ,x_return_status OUT NOCOPY VARCHAR2
2956 ,x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2957 ) IS
2958
2959 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2960 l_bis_bucket_rec BIS_BUCKET_PUB.bis_bucket_rec_type;
2961
2962 l_bucket_ranges_tbl BIS_BUCKET_PVT.BIS_BUCKET_RANGES_TBL;
2963
2964 BEGIN
2965
2966 x_return_status := FND_API.G_RET_STS_SUCCESS;
2967 FND_MSG_PUB.Initialize;
2968
2969 l_bis_bucket_rec := p_bis_bucket_rec;
2970
2971 IF NOT (IS_BUCKET_TYPE_EXISTS(p_bucket_type=> l_bis_bucket_rec.type)) THEN
2972 l_error_tbl := x_error_tbl;
2973 BIS_UTILITIES_PVT.Add_Error_Message (
2974 p_error_msg_name => 'BIS_INVALID_BUCKET_TYPE'
2975 ,p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2976 ,p_error_proc_name => G_PKG_NAME||'.CREATE_BIS_BUCKET_TYPE'
2977 ,p_error_type => BIS_UTILITIES_PUB.G_ERROR
2978 ,p_error_table => l_error_tbl
2979 ,x_error_table => x_error_tbl
2980 );
2981 RAISE FND_API.G_EXC_ERROR;
2982 END IF;
2983
2984 IF NOT ( CHECK_RANGE_NAME(p_bis_bucket_rec => l_bis_bucket_rec) ) THEN
2985 l_error_tbl := x_error_tbl;
2986 BIS_UTILITIES_PVT.Add_Error_Message (
2987 p_error_msg_name => 'BIS_DUPLICATE_RANGE_NAME'
2988 ,p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2989 ,p_error_proc_name => G_PKG_NAME||'.CREATE_BIS_BUCKET'
2990 ,p_error_type => BIS_UTILITIES_PUB.G_ERROR
2991 ,p_error_table => l_error_tbl
2992 ,x_error_table => x_error_tbl
2993 );
2994 RAISE FND_API.G_EXC_ERROR;
2995 END IF;
2996
2997 IF NOT ( CHECK_RANGE_VAL_HIGH(p_bis_bucket_rec => l_bis_bucket_rec) ) THEN
2998 l_error_tbl := x_error_tbl;
2999 BIS_UTILITIES_PVT.Add_Error_Message (
3000 p_error_msg_name => 'BIS_DUPLICATE_VAL_HIGH'
3001 ,p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
3002 ,p_error_proc_name => G_PKG_NAME||'.CREATE_BIS_BUCKET'
3003 ,p_error_type => BIS_UTILITIES_PUB.G_ERROR
3004 ,p_error_table => l_error_tbl
3005 ,x_error_table => x_error_tbl
3006 );
3007 RAISE FND_API.G_EXC_ERROR;
3008 END IF;
3009
3010 IF NOT ( CHECK_RANGE_VAL_LOW(p_bis_bucket_rec => l_bis_bucket_rec) ) THEN
3011 l_error_tbl := x_error_tbl;
3012 BIS_UTILITIES_PVT.Add_Error_Message (
3013 p_error_msg_name => 'BIS_DUPLICATE_VAL_LOW'
3014 ,p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
3015 ,p_error_proc_name => G_PKG_NAME||'.CREATE_BIS_BUCKET'
3016 ,p_error_type => BIS_UTILITIES_PUB.G_ERROR
3017 ,p_error_table => l_error_tbl
3018 ,x_error_table => x_error_tbl
3019 );
3020 RAISE FND_API.G_EXC_ERROR;
3021 END IF;
3022
3023 Populate_Loc_Bucket_Range_Tbl (
3024 p_bis_bucket_rec => l_bis_bucket_rec
3025 ,x_bucket_ranges_tbl => l_bucket_ranges_tbl
3026 ,x_return_status => x_return_status
3027 ,x_error_tbl => x_error_tbl
3028 );
3029
3030 --dbms_output.put_line( 'x_return_status(Pop) is ' || x_return_status);
3031
3032 Validate_From_To (
3033 p_bucket_ranges_tbl => l_bucket_ranges_tbl
3034 ,x_return_status => x_return_status
3035 ,x_error_tbl => x_error_tbl
3036 );
3037
3038 --dbms_output.put_line( 'x_return_status(From-To) is ' || x_return_status);
3039
3040 Validate_Bucket_Overlapping (
3041 p_overlapping => l_bis_bucket_rec.overlapping
3042 ,p_bucket_ranges_tbl => l_bucket_ranges_tbl
3043 ,x_return_status => x_return_status
3044 ,x_error_tbl => x_error_tbl
3045 );
3046
3047 --dbms_output.put_line( 'x_return_status(Overlapping) is ' || x_return_status);
3048
3049 Validate_Bucket_Discontinuous (
3050 p_discontinuous => l_bis_bucket_rec.discontinuous
3051 ,p_bucket_ranges_tbl => l_bucket_ranges_tbl
3052 ,x_return_status => x_return_status
3053 ,x_error_tbl => x_error_tbl
3054 );
3055
3056 --dbms_output.put_line( 'x_return_status(Disc) is ' || x_return_status);
3057
3058 EXCEPTION
3059 WHEN FND_API.G_EXC_ERROR THEN
3060 x_return_status := FND_API.G_RET_STS_ERROR ;
3061 --dbms_output.put_line( 'x_return_status (Not wrapper) is ' || x_return_status);
3062 RAISE FND_API.G_EXC_ERROR;
3063 WHEN OTHERS THEN
3064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3065 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3066 END Validate_Bucket_Common;
3067 --=============================================================================
3068
3069
3070 PROCEDURE ADD_LANGUAGE
3071 IS
3072 BEGIN
3073 DELETE FROM BIS_BUCKET_TL T
3074 WHERE NOT EXISTS
3075 (SELECT NULL
3076 FROM BIS_BUCKET B
3077 WHERE B.BUCKET_ID = T.BUCKET_ID
3078 );
3079
3080 UPDATE BIS_BUCKET_TL T SET (
3081 NAME,
3082 RANGE1_NAME,
3083 RANGE2_NAME,
3084 RANGE3_NAME,
3085 RANGE4_NAME,
3086 RANGE5_NAME,
3087 RANGE6_NAME,
3088 RANGE7_NAME,
3089 RANGE8_NAME,
3090 RANGE9_NAME,
3091 RANGE10_NAME,
3092 DESCRIPTION
3093 ) = (SELECT
3094 B.NAME,
3095 B.RANGE1_NAME,
3096 B.RANGE2_NAME,
3097 B.RANGE3_NAME,
3098 B.RANGE4_NAME,
3099 B.RANGE5_NAME,
3100 B.RANGE6_NAME,
3101 B.RANGE7_NAME,
3102 B.RANGE8_NAME,
3103 B.RANGE9_NAME,
3104 B.RANGE10_NAME,
3105 B.DESCRIPTION
3106 FROM BIS_BUCKET_TL B
3107 WHERE B.BUCKET_ID = T.BUCKET_ID
3108 AND B.LANGUAGE = T.SOURCE_LANG)
3109 WHERE (
3110 T.BUCKET_ID,
3111 T.LANGUAGE
3112 ) IN (SELECT
3113 SUBT.BUCKET_ID,
3114 SUBT.LANGUAGE
3115 FROM BIS_BUCKET_TL SUBB, BIS_BUCKET_TL SUBT
3116 WHERE SUBB.BUCKET_ID = SUBT.BUCKET_ID
3117 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
3118 AND (SUBB.NAME <> SUBT.NAME
3119 OR SUBB.RANGE1_NAME <> SUBT.RANGE1_NAME
3120 OR (SUBB.RANGE1_NAME IS NULL AND SUBT.RANGE1_NAME IS NOT NULL)
3121 OR (SUBB.RANGE1_NAME IS NOT NULL AND SUBT.RANGE1_NAME IS NULL)
3122 OR SUBB.RANGE2_NAME <> SUBT.RANGE2_NAME
3123 OR (SUBB.RANGE2_NAME IS NULL AND SUBT.RANGE2_NAME IS NOT NULL)
3124 OR (SUBB.RANGE2_NAME IS NOT NULL AND SUBT.RANGE2_NAME IS NULL)
3125 OR SUBB.RANGE3_NAME <> SUBT.RANGE3_NAME
3126 OR (SUBB.RANGE3_NAME IS NULL AND SUBT.RANGE3_NAME IS NOT NULL)
3127 OR (SUBB.RANGE3_NAME IS NOT NULL AND SUBT.RANGE3_NAME IS NULL)
3128 OR SUBB.RANGE4_NAME <> SUBT.RANGE4_NAME
3129 OR (SUBB.RANGE4_NAME IS NULL AND SUBT.RANGE4_NAME IS NOT NULL)
3130 OR (SUBB.RANGE4_NAME IS NOT NULL AND SUBT.RANGE4_NAME IS NULL)
3131 OR SUBB.RANGE5_NAME <> SUBT.RANGE5_NAME
3132 OR (SUBB.RANGE5_NAME IS NULL AND SUBT.RANGE5_NAME IS NOT NULL)
3133 OR (SUBB.RANGE5_NAME IS NOT NULL AND SUBT.RANGE5_NAME IS NULL)
3134 OR SUBB.RANGE6_NAME <> SUBT.RANGE6_NAME
3135 OR (SUBB.RANGE6_NAME IS NULL AND SUBT.RANGE6_NAME IS NOT NULL)
3136 OR (SUBB.RANGE6_NAME IS NOT NULL AND SUBT.RANGE6_NAME IS NULL)
3137 OR SUBB.RANGE7_NAME <> SUBT.RANGE7_NAME
3138 OR (SUBB.RANGE7_NAME IS NULL AND SUBT.RANGE7_NAME IS NOT NULL)
3139 OR (SUBB.RANGE7_NAME IS NOT NULL AND SUBT.RANGE7_NAME IS NULL)
3140 OR SUBB.RANGE8_NAME <> SUBT.RANGE8_NAME
3141 OR (SUBB.RANGE8_NAME IS NULL AND SUBT.RANGE8_NAME IS NOT NULL)
3142 OR (SUBB.RANGE8_NAME IS NOT NULL AND SUBT.RANGE8_NAME IS NULL)
3143 OR SUBB.RANGE9_NAME <> SUBT.RANGE9_NAME
3144 OR (SUBB.RANGE9_NAME IS NULL AND SUBT.RANGE9_NAME IS NOT NULL)
3145 OR (SUBB.RANGE9_NAME IS NOT NULL AND SUBT.RANGE9_NAME IS NULL)
3146 OR SUBB.RANGE10_NAME <> SUBT.RANGE10_NAME
3147 OR (SUBB.RANGE10_NAME IS NULL AND SUBT.RANGE10_NAME IS NOT NULL)
3148 OR (SUBB.RANGE10_NAME IS NOT NULL AND SUBT.RANGE10_NAME IS NULL)
3149 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
3150 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
3151 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
3152 ));
3153
3154 INSERT INTO BIS_BUCKET_TL (
3155 CREATED_BY,
3156 CREATION_DATE,
3157 LAST_UPDATED_BY,
3158 LAST_UPDATE_DATE,
3159 LAST_UPDATE_LOGIN,
3160 BUCKET_ID,
3161 NAME,
3162 RANGE1_NAME,
3163 RANGE2_NAME,
3164 RANGE3_NAME,
3165 RANGE4_NAME,
3166 RANGE5_NAME,
3167 RANGE6_NAME,
3168 RANGE7_NAME,
3169 RANGE8_NAME,
3170 RANGE9_NAME,
3171 RANGE10_NAME,
3172 DESCRIPTION,
3173 LANGUAGE,
3174 SOURCE_LANG
3175 ) SELECT /*+ ORDERED */
3176 B.CREATED_BY,
3177 B.CREATION_DATE,
3178 B.LAST_UPDATED_BY,
3179 B.LAST_UPDATE_DATE,
3180 B.LAST_UPDATE_LOGIN,
3181 B.BUCKET_ID,
3182 B.NAME,
3183 B.RANGE1_NAME,
3184 B.RANGE2_NAME,
3185 B.RANGE3_NAME,
3186 B.RANGE4_NAME,
3187 B.RANGE5_NAME,
3188 B.RANGE6_NAME,
3189 B.RANGE7_NAME,
3190 B.RANGE8_NAME,
3191 B.RANGE9_NAME,
3192 B.RANGE10_NAME,
3193 B.DESCRIPTION,
3194 L.LANGUAGE_CODE,
3195 B.SOURCE_LANG
3196 FROM BIS_BUCKET_TL B, FND_LANGUAGES L
3197 WHERE L.INSTALLED_FLAG in ('I', 'B')
3198 AND B.LANGUAGE = userenv('LANG')
3199 AND NOT EXISTS
3200 (SELECT NULL
3201 FROM BIS_BUCKET_TL T
3202 WHERE T.BUCKET_ID = B.BUCKET_ID
3203 AND T.LANGUAGE = L.LANGUAGE_CODE);
3204 END ADD_LANGUAGE;
3205
3206
3207 END BIS_BUCKET_PVT;