[Home] [Help]
PACKAGE BODY: APPS.IGI_EFC_UPGRADE
Source
1 PACKAGE BODY IGI_EFC_UPGRADE AS
2 -- $Header: igiefupb.pls 120.0.12010000.1 2008/07/29 09:03:45 appldev ship $
3 -- Stores the budget id
4 TYPE budget_type IS TABLE OF gl_budget_assignment_ranges.funding_budget_version_id%TYPE;
5
6 -- Stores the value SEGMENT_LOW and SEGMENT_HIGH of a particular segment
7 TYPE segment_range_rec IS RECORD (segment_low VARCHAR2(25),
8 segment_high VARCHAR2(25),
9 segment_number NUMBER,
10 data_type NUMBER,
11 budget_tab BUDGET_TYPE);
12
13 -- Array of segment ranges. Array index indicates the Segment number.
14 TYPE segment_range_type IS VARRAY(30) OF segment_range_rec;
15
16 -- Record corresponding to table gl_budget_assignment_ranges
17 TYPE budget_range_rec IS RECORD (range_id gl_budget_assignment_ranges.range_id%TYPE,
18 ledger_id gl_budget_assignment_ranges.ledger_id%TYPE,
19 segment_range_tab SEGMENT_RANGE_TYPE,
20 budget_tab BUDGET_TYPE);
21
22
23
24 -- Table of Budget Assignment Ranges. Corresponds to table
25 -- gl_budget_assignment_ranges
26 TYPE budget_range_type IS TABLE OF budget_range_rec;
27
28 -- Stores information for a particular segment. Used by Splitting and Merging
29 -- logic
30 TYPE segment_rec IS RECORD (index_number NUMBER,
31 segment_number NUMBER,
32 segment_high VARCHAR2(25),
33 segment_low VARCHAR2(25),
34 budget_tab BUDGET_TYPE,
35 data_type NUMBER);
36
37 -- Array of Segment Records. Used by Splitting and Merging logic.
38 TYPE segment_type IS TABLE OF segment_rec;
39
40 TYPE segment_rec_index_type IS TABLE OF NUMBER;
41
42 TYPE BC_OPTIONS_TAB IS TABLE OF IGI_UPG_GL_BUDORG_BC_OPTIONS%ROWTYPE;
43
44
45 segment_tab SEGMENT_TYPE;
46 budget_range_tab BUDGET_RANGE_TYPE;
47
48 g_debug_enabled NUMBER;
49
50 PROCEDURE PRINT_BUDGET_INFO (p_range_id NUMBER,
51 errbuf OUT NOCOPY VARCHAR2,
52 retcode OUT NOCOPY NUMBER)
53
54 IS
55 CURSOR C_UPG_GL_BUDGET_ASSIGNMENT (pp_range_id NUMBER)
56 IS
57 SELECT IGIGL.*,ent.NAME BUDGET_NAME,led.NAME LEDGER_NAME
58 FROM IGI_UPG_GL_BUDGET_ASSIGNMENT IGIGL,
59 gl_budget_entities ent,
60 GL_LEDGERS led
61 WHERE IGIGL.RANGE_ID = pp_range_id
62 AND IGIGL.budget_entity_id = ent.budget_entity_id
63 AND IGIGL.ledger_id = led.ledger_id
64 ;
65
66 CURSOR C_BC_OPTIONS (pp_range_id NUMBER)
67 IS
68 SELECT IGIBC.*,BV.BUDGET_NAME BUDGET_NAME
69 FROM IGI_UPG_GL_BUDORG_BC_OPTIONS IGIBC,
70 GL_BUDGET_VERSIONS BV
71 WHERE IGIBC.RANGE_ID = pp_range_id AND
72 IGIBC.FUNDING_BUDGET_VERSION_ID = BV.BUDGET_VERSION_ID
73 ;
74
75
76 TYPE IGI_BC_OPTIONS_TAB IS TABLE OF C_BC_OPTIONS%ROWTYPE;
77
78 lc_upg_gl_bud C_UPG_GL_BUDGET_ASSIGNMENT%ROWTYPE;
79 lc_bc_options IGI_BC_OPTIONS_TAB;
80
81 BEGIN
82 IF p_range_id IS NULL THEN
83 errbuf := 'Module: PRINT_BUDGET_INFO => '||'p_range_id is NULL';
84 retcode := -1;
85 RETURN;
86 END IF;
87 BEGIN
88
89
90
91 OPEN C_UPG_GL_BUDGET_ASSIGNMENT(p_range_id);
92 FETCH C_UPG_GL_BUDGET_ASSIGNMENT INTO lc_upg_gl_bud;
93 CLOSE C_UPG_GL_BUDGET_ASSIGNMENT;
94
95 fnd_file.put(fnd_file.output,lc_upg_gl_bud.LEDGER_NAME||' ');
96 fnd_file.put(fnd_file.output,lc_upg_gl_bud.BUDGET_NAME||' ');
97
98 fnd_file.put(fnd_file.output,lc_upg_gl_bud.CURRENCY_CODE||' ');
99
100 IF lc_upg_gl_bud.SEGMENT1_LOW IS NOT NULL THEN
101 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT1_LOW||'-');
102 END IF;
103
104 IF lc_upg_gl_bud.SEGMENT2_LOW IS NOT NULL THEN
105 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT2_LOW||'-');
106 END IF;
107
108 IF lc_upg_gl_bud.SEGMENT3_LOW IS NOT NULL THEN
109 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT3_LOW||'-');
110 END IF;
111
112 IF lc_upg_gl_bud.SEGMENT4_LOW IS NOT NULL THEN
113 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT4_LOW||'-');
114 END IF;
115
116 IF lc_upg_gl_bud.SEGMENT5_LOW IS NOT NULL THEN
117 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT5_LOW||'-');
118 END IF;
119
120 IF lc_upg_gl_bud.SEGMENT6_LOW IS NOT NULL THEN
121 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT6_LOW||'-');
122 END IF;
123
124 IF lc_upg_gl_bud.SEGMENT7_LOW IS NOT NULL THEN
125 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT7_LOW||'-');
126 END IF;
127
128 IF lc_upg_gl_bud.SEGMENT8_LOW IS NOT NULL THEN
129 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT8_LOW||'-');
130 END IF;
131
132 IF lc_upg_gl_bud.SEGMENT9_LOW IS NOT NULL THEN
133 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT9_LOW||'-');
134 END IF;
135
136 IF lc_upg_gl_bud.SEGMENT10_LOW IS NOT NULL THEN
137 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT10_LOW||'-');
138 END IF;
139
140 IF lc_upg_gl_bud.SEGMENT11_LOW IS NOT NULL THEN
141 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT11_LOW||'-');
142 END IF;
143
144 IF lc_upg_gl_bud.SEGMENT12_LOW IS NOT NULL THEN
145 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT12_LOW||'-');
146 END IF;
147
148 IF lc_upg_gl_bud.SEGMENT13_LOW IS NOT NULL THEN
149 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT13_LOW||'-');
150 END IF;
151
152 IF lc_upg_gl_bud.SEGMENT14_LOW IS NOT NULL THEN
153 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT14_LOW||'-');
154 END IF;
155
156 IF lc_upg_gl_bud.SEGMENT15_LOW IS NOT NULL THEN
157 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT15_LOW||'-');
158 END IF;
159
160 IF lc_upg_gl_bud.SEGMENT16_LOW IS NOT NULL THEN
161 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT16_LOW||'-');
162 END IF;
163
164 IF lc_upg_gl_bud.SEGMENT17_LOW IS NOT NULL THEN
165 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT17_LOW||'-');
166 END IF;
167
168 IF lc_upg_gl_bud.SEGMENT18_LOW IS NOT NULL THEN
169 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT18_LOW||'-');
170 END IF;
171 IF lc_upg_gl_bud.SEGMENT19_LOW IS NOT NULL THEN
172 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT19_LOW||'-');
173 END IF;
174
175 IF lc_upg_gl_bud.SEGMENT20_LOW IS NOT NULL THEN
176 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT20_LOW||'-');
177 END IF;
178
179 IF lc_upg_gl_bud.SEGMENT21_LOW IS NOT NULL THEN
180 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT21_LOW||'-');
181 END IF;
182
183 IF lc_upg_gl_bud.SEGMENT22_LOW IS NOT NULL THEN
184 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT22_LOW||'-');
185 END IF;
186
187 IF lc_upg_gl_bud.SEGMENT23_LOW IS NOT NULL THEN
188 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT23_LOW||'-');
189 END IF;
190
191 IF lc_upg_gl_bud.SEGMENT24_LOW IS NOT NULL THEN
192 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT24_LOW||'-');
193 END IF;
194
195 IF lc_upg_gl_bud.SEGMENT25_LOW IS NOT NULL THEN
196 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT25_LOW||'-');
197 END IF;
198
199 IF lc_upg_gl_bud.SEGMENT26_LOW IS NOT NULL THEN
200 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT26_LOW||'-');
201 END IF;
202
203 IF lc_upg_gl_bud.SEGMENT27_LOW IS NOT NULL THEN
204 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT27_LOW||'-');
205 END IF;
206
207 IF lc_upg_gl_bud.SEGMENT28_LOW IS NOT NULL THEN
208 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT28_LOW||'-');
209 END IF;
210
211 IF lc_upg_gl_bud.SEGMENT29_LOW IS NOT NULL THEN
212 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT29_LOW||'-');
213 END IF;
214
215 IF lc_upg_gl_bud.SEGMENT30_LOW IS NOT NULL THEN
216 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT30_LOW||'-');
217 END IF;
218
219 fnd_file.put(fnd_file.output,' to ');
220
221 IF lc_upg_gl_bud.SEGMENT1_HIGH IS NOT NULL THEN
222 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT1_HIGH||'-');
223 END IF;
224
225 IF lc_upg_gl_bud.SEGMENT2_HIGH IS NOT NULL THEN
226 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT2_HIGH||'-');
227 END IF;
228
229 IF lc_upg_gl_bud.SEGMENT3_HIGH IS NOT NULL THEN
230 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT3_HIGH||'-');
231 END IF;
232
233 IF lc_upg_gl_bud.SEGMENT4_HIGH IS NOT NULL THEN
234 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT4_HIGH||'-');
235 END IF;
236
237 IF lc_upg_gl_bud.SEGMENT5_HIGH IS NOT NULL THEN
238 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT5_HIGH||'-');
239 END IF;
240
241 IF lc_upg_gl_bud.SEGMENT6_HIGH IS NOT NULL THEN
242 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT6_HIGH||'-');
243 END IF;
244
245 IF lc_upg_gl_bud.SEGMENT7_HIGH IS NOT NULL THEN
246 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT7_HIGH||'-');
247 END IF;
248
249 IF lc_upg_gl_bud.SEGMENT8_HIGH IS NOT NULL THEN
250 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT8_HIGH||'-');
251 END IF;
252
253 IF lc_upg_gl_bud.SEGMENT9_HIGH IS NOT NULL THEN
254 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT9_HIGH||'-');
255 END IF;
256
257 IF lc_upg_gl_bud.SEGMENT10_HIGH IS NOT NULL THEN
258 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT10_HIGH||'-');
259 END IF;
260
261 IF lc_upg_gl_bud.SEGMENT11_HIGH IS NOT NULL THEN
262 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT11_HIGH||'-');
263 END IF;
264
265 IF lc_upg_gl_bud.SEGMENT12_HIGH IS NOT NULL THEN
266 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT12_HIGH||'-');
267 END IF;
268
269 IF lc_upg_gl_bud.SEGMENT13_HIGH IS NOT NULL THEN
270 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT13_HIGH||'-');
271 END IF;
272
273 IF lc_upg_gl_bud.SEGMENT14_HIGH IS NOT NULL THEN
274 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT14_HIGH||'-');
275 END IF;
276
277 IF lc_upg_gl_bud.SEGMENT15_HIGH IS NOT NULL THEN
278 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT15_HIGH||'-');
279 END IF;
280
281 IF lc_upg_gl_bud.SEGMENT16_HIGH IS NOT NULL THEN
282 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT16_HIGH||'-');
283 END IF;
284
285 IF lc_upg_gl_bud.SEGMENT17_HIGH IS NOT NULL THEN
286 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT17_HIGH||'-');
287 END IF;
288
289 IF lc_upg_gl_bud.SEGMENT18_HIGH IS NOT NULL THEN
290 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT18_HIGH||'-');
291 END IF;
292 IF lc_upg_gl_bud.SEGMENT19_HIGH IS NOT NULL THEN
293 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT19_HIGH||'-');
294 END IF;
295
296 IF lc_upg_gl_bud.SEGMENT20_HIGH IS NOT NULL THEN
297 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT20_HIGH||'-');
298 END IF;
299
300 IF lc_upg_gl_bud.SEGMENT21_HIGH IS NOT NULL THEN
301 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT21_HIGH||'-');
302 END IF;
303
304 IF lc_upg_gl_bud.SEGMENT22_HIGH IS NOT NULL THEN
305 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT22_HIGH||'-');
306 END IF;
307
308 IF lc_upg_gl_bud.SEGMENT23_HIGH IS NOT NULL THEN
309 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT23_HIGH||'-');
310 END IF;
311
312 IF lc_upg_gl_bud.SEGMENT24_HIGH IS NOT NULL THEN
313 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT24_HIGH||'-');
314 END IF;
315
316 IF lc_upg_gl_bud.SEGMENT25_HIGH IS NOT NULL THEN
317 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT25_HIGH||'-');
318 END IF;
319
320 IF lc_upg_gl_bud.SEGMENT26_HIGH IS NOT NULL THEN
321 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT26_HIGH||'-');
322 END IF;
323
324 IF lc_upg_gl_bud.SEGMENT27_HIGH IS NOT NULL THEN
325 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT27_HIGH||'-');
326 END IF;
327
328 IF lc_upg_gl_bud.SEGMENT28_HIGH IS NOT NULL THEN
329 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT28_HIGH||'-');
330 END IF;
331
332 IF lc_upg_gl_bud.SEGMENT29_HIGH IS NOT NULL THEN
333 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT29_HIGH||'-');
334 END IF;
335
336 IF lc_upg_gl_bud.SEGMENT30_HIGH IS NOT NULL THEN
337 fnd_file.put(fnd_file.output,lc_upg_gl_bud.SEGMENT30_HIGH||'-');
338 END IF;
339
340 fnd_file.put_line(fnd_file.output,' ');
341
342 fnd_file.put(fnd_file.output,'Associated Budgets are ');
343
344 OPEN C_BC_OPTIONS(p_range_id);
345 FETCH C_BC_OPTIONS BULK COLLECT INTO lc_bc_options;
346 CLOSE C_BC_OPTIONS;
347
348 FOR i IN 1..lc_bc_options.COUNT LOOP
349 fnd_file.put(fnd_file.output,lc_bc_options(i).budget_name||', ');
350 END LOOP;
351
352 fnd_file.put_line(fnd_file.output,' ');
353
354 EXCEPTION
355 WHEN OTHERS THEN
356 fnd_file.put_line(fnd_file.output, 'Module: PRINT_BUDGET_INFO => '||SQLERRM);
357 errbuf := 'Module: PRINT_BUDGET_INFO => '||SQLERRM;
358 retcode := -1;
359 RETURN;
360 END;
361 END;
362
363
364 FUNCTION COMPARE_BUDGETS (p_budget_tab1 budget_type, p_budget_tab2 BC_OPTIONS_TAB) RETURN BOOLEAN
365 IS
366 l_exists boolean;
367 BEGIN
368 FOR i IN 1..p_budget_tab2.COUNT LOOP
369 l_exists := FALSE;
370 FOR j IN 1..p_budget_tab1.COUNT LOOP
371 IF p_budget_tab1(j) = p_budget_tab2(i).FUNDING_BUDGET_VERSION_ID THEN
372 l_exists := TRUE;
373 EXIT;
374 END IF;
375 END LOOP;
376 IF NOT l_exists THEN
377 RETURN FALSE;
378 END IF;
379 END LOOP;
380
381 FOR i IN 1..p_budget_tab1.COUNT LOOP
382 l_exists := FALSE;
383 FOR j IN 1..p_budget_tab2.COUNT LOOP
384 IF p_budget_tab2(j).FUNDING_BUDGET_VERSION_ID = p_budget_tab1(i) THEN
385 l_exists := TRUE;
386 EXIT;
387 END IF;
388 END LOOP;
389 IF NOT l_exists THEN
390 RETURN FALSE;
391 END IF;
392 END LOOP;
393
394 RETURN TRUE;
395 END;
396
397
398
399 FUNCTION COMPARE_BUDGETS (p_budget_tab1 budget_type, p_budget_tab2 budget_type) RETURN BOOLEAN
400 IS
401 l_exists boolean;
402 BEGIN
403 FOR i IN 1..p_budget_tab2.COUNT LOOP
404 l_exists := FALSE;
405 FOR j IN 1..p_budget_tab1.COUNT LOOP
406 IF p_budget_tab1(j) = p_budget_tab2(i) THEN
407 l_exists := TRUE;
408 EXIT;
409 END IF;
410 END LOOP;
411 IF NOT l_exists THEN
412 RETURN FALSE;
413 END IF;
414 END LOOP;
415
416 FOR i IN 1..p_budget_tab1.COUNT LOOP
417 l_exists := FALSE;
418 FOR j IN 1..p_budget_tab2.COUNT LOOP
419 IF p_budget_tab2(j) = p_budget_tab1(i) THEN
420 l_exists := TRUE;
421 EXIT;
422 END IF;
423 END LOOP;
424 IF NOT l_exists THEN
425 RETURN FALSE;
426 END IF;
427 END LOOP;
428
429 RETURN TRUE;
430 END;
431
432
433 -- This function takes two budget tables as input as returns the concatenated
434 -- table. Repeated budgets are outputted only once
435 FUNCTION MERGE_BUDGETS (p_budget_tab1 budget_type, p_budget_tab2 budget_type) RETURN budget_type
436 IS
437 l_ret_budget_tab budget_type;
438 l_exists boolean;
439 BEGIN
440 l_ret_budget_tab := budget_type();
441 l_ret_budget_tab := p_budget_tab1;
442 FOR i IN 1..p_budget_tab2.COUNT LOOP
443 l_exists := FALSE;
444 FOR j IN 1..p_budget_tab1.COUNT LOOP
445 IF p_budget_tab1(j) = p_budget_tab2(i) THEN
446 l_exists := TRUE;
447 END IF;
448 END LOOP;
449 IF NOT l_exists THEN
450 l_ret_budget_tab.extend(1);
451 l_ret_budget_tab(l_ret_budget_tab.COUNT) := p_budget_tab2(i);
452 END IF;
453 END LOOP;
454 RETURN l_ret_budget_tab;
455
456 END MERGE_BUDGETS;
457
458
459 FUNCTION PREVIOUS_VALUE (p_segment_value VARCHAR2, p_data_type NUMBER) RETURN VARCHAR2
460 IS
461
462 l_prev_value varchar2(30);
463 BEGIN
464 --Data type 0 indicates the data is numeric
465 IF p_data_type = 0 THEN
466 l_prev_value := p_segment_value - 1;
467 IF length(l_prev_value)<length(p_segment_value) THEN
468 FOR i IN 1..length(p_segment_value)-length(l_prev_value) LOOP
469 l_prev_value := '0'||l_prev_value;
470 END LOOP;
471 END IF;
472 RETURN l_prev_value;
473 END IF;
474 RETURN NULL;
475 END PREVIOUS_VALUE;
476
477 FUNCTION NEXT_VALUE (p_segment_value VARCHAR2,p_data_type NUMBER) RETURN VARCHAR2
478 IS
479 l_next_value varchar2(30);
480 BEGIN
481 --Data type 0 indicates the data is numeric
482 IF p_data_type = 0 THEN
483 l_next_value := p_segment_value + 1;
484 IF length(l_next_value)<length(p_segment_value) THEN
485 FOR i IN 1..length(p_segment_value)-length(l_next_value) LOOP
486 l_next_value := '0'||l_next_value;
487 END LOOP;
488 END IF;
489 RETURN l_next_value;
490 END IF;
491 RETURN NULL;
492 END NEXT_VALUE;
493
494
495 PROCEDURE SPLIT_RANGES (p_segment1 IN segment_range_rec,
496 p_segment2 IN segment_range_rec,
497 p_segment_tab IN OUT NOCOPY segment_type,
498 p_index_number IN NUMBER,
499 errbuf OUT NOCOPY VARCHAR2,
500 retcode OUT NOCOPY VARCHAR2)
501 IS
502
503 l_exists BOOLEAN;
504 l_segment1 segment_range_rec;
505 l_segment2 segment_range_rec;
506
507 BEGIN
508
509
510
511 IF p_segment1.segment_low >= p_segment2.segment_low THEN
512 l_segment1 := p_segment1;
513 l_segment2 := p_segment2;
514 ELSE
515 l_segment1 := p_segment2;
516 l_segment2 := p_segment1;
517 END IF;
518
519 IF g_debug_enabled = 1 THEN
520 fnd_file.put_line(fnd_file.log, 'l_segment1.segment_low = '||l_segment1.segment_low);
521 fnd_file.put_line(fnd_file.log, 'l_segment1.segment_high = '||l_segment1.segment_high);
522 fnd_file.put_line(fnd_file.log, 'l_segment2.segment_low = '||l_segment2.segment_low);
523 fnd_file.put_line(fnd_file.log, 'l_segment2.segment_high = '||l_segment2.segment_high);
524 END IF;
525
526 IF l_segment1.segment_number <> l_segment2.segment_number THEN
527
528 fnd_file.put_line(fnd_file.log, 'Module: SPLIT_RANGES '||'Splitting failed as segment_numbers do not match');
529 errbuf := 'Module: SPLIT_RANGES '||'Splitting failed as segment_numbers do not match';
530 retcode := -1;
531 RETURN;
532 END IF;
533
534 BEGIN
535
536 IF l_segment1.segment_low >= l_segment2.segment_low THEN
537 IF l_segment1.segment_low > l_segment2.segment_low THEN
538 p_segment_tab.extend(1);
539 p_segment_tab(p_segment_tab.COUNT).index_number := p_index_number;
540 p_segment_tab(p_segment_tab.COUNT).segment_number := l_segment1.segment_number;
541 p_segment_tab(p_segment_tab.COUNT).segment_low := l_segment2.segment_low;
542 p_segment_tab(p_segment_tab.COUNT).segment_high := PREVIOUS_VALUE(l_segment1.segment_low,l_segment1.data_type);
543
544 IF p_segment_tab(p_segment_tab.COUNT).segment_high IS NULL THEN
545 fnd_file.put_line(fnd_file.log, 'Module: SPLIT_RANGES '||'Null value retured ');
546 errbuf := 'Module: SPLIT_RANGES '||'Null value retured for PREVIOUS_VALUE(l_segment1.segment_low,l_segment1.data_type);';
547 retcode := -1;
548 RETURN;
549 END IF;
550
551 p_segment_tab(p_segment_tab.COUNT).budget_tab := BUDGET_TYPE();
552 p_segment_tab(p_segment_tab.COUNT).budget_tab := l_segment2.budget_tab;
553 END IF;
554
555
556
557 IF l_segment1.segment_high >= l_segment2.segment_high THEN
558 p_segment_tab.extend(1);
559 p_segment_tab(p_segment_tab.COUNT).index_number := p_index_number;
560 p_segment_tab(p_segment_tab.COUNT).segment_number := l_segment1.segment_number;
561 p_segment_tab(p_segment_tab.COUNT).segment_low := l_segment1.segment_low;
562 p_segment_tab(p_segment_tab.COUNT).segment_high := l_segment2.segment_high;
563 p_segment_tab(p_segment_tab.COUNT).budget_tab := BUDGET_TYPE();
564 p_segment_tab(p_segment_tab.COUNT).budget_tab := MERGE_BUDGETS(l_segment1.budget_tab,l_segment2.budget_tab);
565
566 IF p_segment_tab(p_segment_tab.COUNT).budget_tab IS NULL THEN
567 fnd_file.put_line(fnd_file.log, 'Module: SPLIT_RANGES '||'Null value retured ');
568 errbuf := 'Module: SPLIT_RANGES '||'Null value retured for p_segment_tab(p_segment_tab.COUNT).budget_tab';
569 retcode := -1;
570 RETURN;
571 END IF;
572
573 IF l_segment1.segment_high > l_segment2.segment_high THEN
574 p_segment_tab.extend(1);
575 p_segment_tab(p_segment_tab.COUNT).index_number := p_index_number;
576 p_segment_tab(p_segment_tab.COUNT).segment_number := l_segment1.segment_number;
577 p_segment_tab(p_segment_tab.COUNT).segment_low := NEXT_VALUE(l_segment2.segment_high,l_segment2.data_type);
578
579 IF p_segment_tab(p_segment_tab.COUNT).segment_low IS NULL THEN
580 fnd_file.put_line(fnd_file.log, 'Module: SPLIT_RANGES '||'Null value retured ');
581 errbuf := 'Module: SPLIT_RANGES '||'Null value retured for NEXT_VALUE(l_segment2.segment_high,l_segment2.data_type);';
582 retcode := -1;
583 RETURN;
584 END IF;
585
586 p_segment_tab(p_segment_tab.COUNT).segment_high := l_segment1.segment_high;
587 p_segment_tab(p_segment_tab.COUNT).budget_tab := BUDGET_TYPE();
588 p_segment_tab(p_segment_tab.COUNT).budget_tab := l_segment1.budget_tab;
589 END IF;
590
591 ELSE
592
593 p_segment_tab.extend(1);
594 p_segment_tab(p_segment_tab.COUNT).index_number := p_index_number;
595 p_segment_tab(p_segment_tab.COUNT).segment_number := l_segment1.segment_number;
596 p_segment_tab(p_segment_tab.COUNT).segment_low := l_segment1.segment_low;
597 p_segment_tab(p_segment_tab.COUNT).segment_high := l_segment1.segment_high;
598 p_segment_tab(p_segment_tab.COUNT).budget_tab := BUDGET_TYPE();
599 p_segment_tab(p_segment_tab.COUNT).budget_tab := MERGE_BUDGETS(l_segment1.budget_tab,l_segment2.budget_tab);
600
601 IF p_segment_tab(p_segment_tab.COUNT).budget_tab IS NULL THEN
602 fnd_file.put_line(fnd_file.log, 'Module: SPLIT_RANGES '||'Null value retured ');
603 errbuf := 'Module: SPLIT_RANGES '||'Null value retured for p_segment_tab(p_segment_tab.COUNT).budget_tab';
604 retcode := -1;
605 RETURN;
606 END IF;
607
608 p_segment_tab.extend(1);
609 p_segment_tab(p_segment_tab.COUNT).index_number := p_index_number;
610 p_segment_tab(p_segment_tab.COUNT).segment_number := l_segment1.segment_number;
611 p_segment_tab(p_segment_tab.COUNT).segment_low := NEXT_VALUE(l_segment1.segment_high,l_segment1.data_type);
612
613 IF p_segment_tab(p_segment_tab.COUNT).segment_low IS NULL THEN
614 fnd_file.put_line(fnd_file.log, 'Module: SPLIT_RANGES '||'Null value retured ');
615 errbuf := 'Module: SPLIT_RANGES '||'Null value retured for NEXT_VALUE(l_segment2.segment_high,l_segment2.data_type);';
616 retcode := -1;
617 RETURN;
618 END IF;
619
620 p_segment_tab(p_segment_tab.COUNT).segment_high := l_segment2.segment_high;
621 p_segment_tab(p_segment_tab.COUNT).budget_tab := BUDGET_TYPE();
622 p_segment_tab(p_segment_tab.COUNT).budget_tab := l_segment2.budget_tab;
623
624
625 END IF;
626
627
628 END IF;
629
630 EXCEPTION
631 WHEN OTHERS THEN
632 fnd_file.put_line(fnd_file.log, 'Module: SPLIT_RANGES '||'Splitting failed '||SQLERRM);
633 errbuf := 'Module: SPLIT_RANGES '||'Splitting failed '||SQLERRM;
634 retcode := -1;
635 RETURN;
636 END;
637
638 END SPLIT_RANGES;
639
640
641 PROCEDURE RECURSIVE_MERGE (p_budget_range1 IN BUDGET_RANGE_REC,
642 p_segment_tab IN SEGMENT_TYPE,
643 p_final_budget_ranges IN OUT NOCOPY BUDGET_RANGE_TYPE,
644 p_no_of_segments IN number,
645 p_current_segment_index IN number,
646 p_segment_rec_index_tab IN SEGMENT_REC_INDEX_TYPE,
647 errbuf OUT NOCOPY VARCHAR2,
648 retcode OUT NOCOPY NUMBER)
649 IS
650 l_segment_rec_index_tab SEGMENT_REC_INDEX_TYPE;
651 l_boolean BOOLEAN;
652 l_budget_tab budget_type;
653 l_index NUMBER;
654 l_record_exists BOOLEAN;
655 l_record_index NUMBER;
656 l_exact_match_count NUMBER;
657 l_overlap_count NUMBER;
658 l_overlap_index NUMBER;
659 l_errbuf VARCHAR2(2000);
660 l_retcode NUMBER;
661 BEGIN
662 IF p_no_of_segments < p_current_segment_index THEN
663 fnd_file.put_line(fnd_file.log, 'Module: RECURSIVE_MERGE '||'p_no_of_segments < p_current_segment_index');
664 errbuf := 'Module: RECURSIVE_MERGE '||'p_no_of_segments < p_current_segment_index';
665 retcode := -1;
666 RETURN;
667 END IF;
668
669 -- Initialize l_segment_rec_index_tab for first call
670 IF p_current_segment_index = 1 THEN
671 l_segment_rec_index_tab := SEGMENT_REC_INDEX_TYPE();
672 ELSE
673 l_segment_rec_index_tab := p_segment_rec_index_tab;
674 END IF;
675
676 -- If p_no_of_segments = p_current_segment_index then recursion has reached
677 -- the last segment. Processing needs to start here.
678 IF p_no_of_segments = p_current_segment_index THEN
679
680 l_budget_tab := budget_type();
681 FOR cnt IN 1..p_segment_tab.COUNT LOOP
682
683 IF p_segment_tab(cnt).index_number = p_current_segment_index THEN
684
685 l_segment_rec_index_tab.extend(1);
686 l_segment_rec_index_tab(l_segment_rec_index_tab.COUNT) := cnt;
687
688 FOR i IN 1..p_segment_tab(l_segment_rec_index_tab(1)).budget_tab.COUNT LOOP
689 l_boolean := TRUE;
690
691 FOR j IN 2..l_segment_rec_index_tab.COUNT LOOP
692
693 EXIT WHEN NOT l_boolean;
694
695 l_boolean := FALSE;
696
697 FOR k IN 1..p_segment_tab(l_segment_rec_index_tab(j)).budget_tab.COUNT LOOP
698 IF p_segment_tab(l_segment_rec_index_tab(1)).budget_tab(i)
699 = p_segment_tab(l_segment_rec_index_tab(j)).budget_tab(k) THEN
700 l_boolean := TRUE;
701 EXIT;
702 END IF;
703 END LOOP;
704
705 END LOOP;
706
707 IF l_boolean = TRUE THEN
708 l_budget_tab.extend(1);
709 l_budget_tab(l_budget_tab.COUNT) := p_segment_tab(l_segment_rec_index_tab(1)).budget_tab(i);
710 END IF;
711
712 END LOOP;
713
714 IF l_budget_tab.COUNT > 0 THEN
715
716
717 FOR n IN 1..p_final_budget_ranges.COUNT LOOP
718
719 l_exact_match_count := 0;
720 l_overlap_count := 0;
721 l_record_exists := FALSE;
722 l_overlap_index := -1;
723 IF
724 COMPARE_BUDGETS(p_final_budget_ranges(n).budget_tab,l_budget_tab)
725 THEN
726 FOR m IN 1..l_segment_rec_index_tab.COUNT LOOP
727 l_index := l_segment_rec_index_tab(m);
728 IF
729 p_final_budget_ranges(n).segment_range_tab(p_segment_tab(l_index).segment_number).segment_low
730 = p_segment_tab(l_index).segment_low
731 AND
732 p_final_budget_ranges(n).segment_range_tab(p_segment_tab(l_index).segment_number).segment_high
733 = p_segment_tab(l_index).segment_high
734 THEN
735 l_exact_match_count := l_exact_match_count +1;
736 ELSE
737 IF NEXT_VALUE(p_final_budget_ranges(n).segment_range_tab(p_segment_tab(l_index)
738 .segment_number).segment_high
739 ,p_final_budget_ranges(n).segment_range_tab(p_segment_tab(l_index).segment_number).data_type)
740 = p_segment_tab(l_index).segment_low THEN
741 l_overlap_count := l_overlap_count + 1;
742 l_overlap_index := l_index;
743 END IF;
744 END IF;
745 END LOOP;
746 END IF;
747 IF l_overlap_count = 1 AND l_exact_match_count = l_segment_rec_index_tab.COUNT - 1 THEN
748 l_record_index := n;
749 l_record_exists := TRUE;
750 EXIT;
751 END IF;
752
753 END LOOP;
754
755 IF l_record_exists THEN
756
757 p_final_budget_ranges(l_record_index).segment_range_tab(p_segment_tab(l_overlap_index)
758 .segment_number).segment_high := p_segment_tab(l_overlap_index).segment_high;
759
760 ELSE
761
762 p_final_budget_ranges.extend(1);
763 p_final_budget_ranges(p_final_budget_ranges.COUNT).budget_tab := budget_type();
764 p_final_budget_ranges(p_final_budget_ranges.COUNT).budget_tab := l_budget_tab;
765 p_final_budget_ranges(p_final_budget_ranges.COUNT).segment_range_tab := segment_range_type();
766
767
768 FOR m IN 1..30 LOOP
769 p_final_budget_ranges(p_final_budget_ranges.COUNT).segment_range_tab.extend(1);
770 p_final_budget_ranges(p_final_budget_ranges.COUNT).segment_range_tab(m).data_type
771 := p_budget_range1.segment_range_tab(m).data_type;
772 p_final_budget_ranges(p_final_budget_ranges.COUNT).segment_range_tab(m).segment_low
773 := p_budget_range1.segment_range_tab(m).segment_low;
774 p_final_budget_ranges(p_final_budget_ranges.COUNT).segment_range_tab(m).segment_high
775 := p_budget_range1.segment_range_tab(m).segment_high;
776 END LOOP;
777
778 FOR m IN 1..l_segment_rec_index_tab.COUNT LOOP
779 l_index := l_segment_rec_index_tab(m);
780 p_final_budget_ranges(p_final_budget_ranges.COUNT).segment_range_tab(p_segment_tab(l_index)
781 .segment_number).segment_low := p_segment_tab(l_index).segment_low;
782 p_final_budget_ranges(p_final_budget_ranges.COUNT).segment_range_tab(p_segment_tab(l_index)
783 .segment_number).segment_high := p_segment_tab(l_index).segment_high;
784 END LOOP;
785
786 END IF;
787 END IF;
788 l_budget_tab.trim(l_budget_tab.COUNT);
789 l_segment_rec_index_tab.trim;
790 END IF;
791 END LOOP;
792 ELSE
793
794 FOR cnt IN 1..p_segment_tab.COUNT LOOP
795
796 IF p_segment_tab(cnt).index_number = p_current_segment_index THEN
797
798 l_segment_rec_index_tab.extend(1);
799 l_segment_rec_index_tab(l_segment_rec_index_tab.COUNT) := cnt;
800
801 RECURSIVE_MERGE (p_budget_range1,
802 p_segment_tab,
803 p_final_budget_ranges,
804 p_no_of_segments,
805 p_current_segment_index + 1,
806 l_segment_rec_index_tab,
807 l_errbuf,
808 l_retcode);
809 IF l_retcode IS NOT NULL AND l_retcode = -1 THEN
810 retcode := l_retcode;
811 errbuf := l_errbuf;
812 RETURN;
813 END IF;
814
815 l_segment_rec_index_tab.trim;
816 END IF;
817 END LOOP;
818 END IF;
819
820
821 END RECURSIVE_MERGE;
822
823 PROCEDURE MERGE_SEGMENTS (p_budget_range1 IN BUDGET_RANGE_REC,
824 p_segment_tab IN SEGMENT_TYPE,
825 p_final_budget_ranges IN OUT NOCOPY BUDGET_RANGE_TYPE,
826 p_no_of_segments number,
827 errbuf OUT NOCOPY VARCHAR2,
828 retcode OUT NOCOPY NUMBER)
829
830
831
832
833 IS
834
835 l_errbuf VARCHAR2(2000);
836 l_retcode VARCHAR2(2000);
837 BEGIN
838 IF p_budget_range1.range_id IS NULL THEN
839 fnd_file.put_line(fnd_file.log, 'p_budget_range1.range_id is NULL');
840 errbuf := 'p_budget_range1.range_id is NULL';
841 retcode := -1;
842 RETURN;
843 END IF;
844 RECURSIVE_MERGE(p_budget_range1,
845 p_segment_tab,
846 p_final_budget_ranges,
847 p_no_of_segments,
848 1,
849 NULL,
850 l_errbuf,
851 l_retcode);
852 errbuf := l_errbuf;
853 retcode := l_retcode;
854
855 END MERGE_SEGMENTS;
856
857
858 PROCEDURE PRINT_MERGE_INFO(p_final_budget_ranges IN BUDGET_RANGE_TYPE)
859 IS
860 BEGIN
861 FOR i IN 1..p_final_budget_ranges.COUNT LOOP
862
863 FOR k IN 1..p_final_budget_ranges(i).segment_range_tab.COUNT LOOP
864 IF p_final_budget_ranges(i).segment_range_tab(k).segment_low IS NOT NULL THEN
865 fnd_file.put(fnd_file.log, '-'||p_final_budget_ranges(i).segment_range_tab(k).segment_low);
866 END IF;
867 END LOOP;
868 fnd_file.put(fnd_file.log, ' to ');
869
870 FOR k IN 1..p_final_budget_ranges(i).segment_range_tab.COUNT LOOP
871 IF p_final_budget_ranges(i).segment_range_tab(k).segment_high IS NOT NULL THEN
872 fnd_file.put(fnd_file.log, '-'||p_final_budget_ranges(i).segment_range_tab(k).segment_high);
873 END IF;
874 END LOOP;
875
876 FOR j IN 1..p_final_budget_ranges(i).budget_tab.COUNT LOOP
877 fnd_file.put(fnd_file.log, ' '||p_final_budget_ranges(i).budget_tab(j));
878 END LOOP;
879 fnd_file.put_line(fnd_file.log, '');
880 END LOOP;
881 END PRINT_MERGE_INFO;
882
883 PROCEDURE PRINT_SPLIT_INFO (p_segment_tab segment_type)
884 IS
885 BEGIN
886 FOR i IN 1..p_segment_tab.COUNT LOOP
887 fnd_file.put(fnd_file.log, p_segment_tab(i).segment_low||' to '||p_segment_tab(i).segment_high||' ');
888 FOR j IN 1..p_segment_tab(i).budget_tab.COUNT LOOP
889 fnd_file.put(fnd_file.log,p_segment_tab(i).budget_tab(j));
890 fnd_file.put(fnd_file.log,'');
891 END LOOP;
892 fnd_file.put_line(fnd_file.log,'');
893 END LOOP;
894
895 END PRINT_SPLIT_INFO;
896
897
898 PROCEDURE DEBUG_LOG (p_log_message VARCHAR2, p_module VARCHAR2)
899 IS
900 BEGIN
901 fnd_file.put_line(fnd_file.log,'Module:'||p_module||' => '||p_log_message);
902 END;
903
904
905 PROCEDURE PRINT_OUTPUT (p_output_message VARCHAR2)
906 IS
907 BEGIN
908 fnd_file.put_line(fnd_file.log,p_output_message);
909 END;
910
911
912
913 PROCEDURE LOOP_AND_PROCESS (p_data_type NUMBER,
914 p_mode NUMBER,
915 errbuf OUT NOCOPY VARCHAR2,
916 retcode OUT NOCOPY NUMBER
917 )
918 IS
919
920 TYPE BUD_ASSIGN_TAB IS TABLE OF IGI_UPG_GL_BUDGET_ASSIGNMENT%ROWTYPE;
921
922
923 CURSOR C_NON_OVERLAPPING_RANGES IS
924 SELECT * FROM IGI_UPG_GL_BUDGET_ASSIGNMENT BA1
925 WHERE NOT EXISTS
926 (SELECT 1 FROM
927 IGI_UPG_GL_BUDGET_ASSIGNMENT BA2
928 WHERE BA1.RANGE_ID <> BA2.RANGE_ID
929 AND BA2.ledger_id = BA1.ledger_id
930 AND BA2.currency_code = BA1.currency_code
931 AND NVL(BA1.SEGMENT1_LOW,'X') <= NVL(BA2.SEGMENT1_HIGH,'X')
932 AND NVL(BA1.SEGMENT1_HIGH,'X') >= NVL(BA2.SEGMENT1_LOW,'X')
933 AND NVL(BA1.SEGMENT2_LOW,'X') <= NVL(BA2.SEGMENT2_HIGH,'X')
934 AND NVL(BA1.SEGMENT2_HIGH,'X') >= NVL(BA2.SEGMENT2_LOW,'X')
935 AND NVL(BA1.SEGMENT3_LOW,'X') <= NVL(BA2.SEGMENT3_HIGH,'X')
936 AND NVL(BA1.SEGMENT3_HIGH,'X') >= NVL(BA2.SEGMENT3_LOW,'X')
937 AND NVL(BA1.SEGMENT4_LOW,'X') <= NVL(BA2.SEGMENT4_HIGH,'X')
938 AND NVL(BA1.SEGMENT4_HIGH,'X') >= NVL(BA2.SEGMENT4_LOW,'X')
939 AND NVL(BA1.SEGMENT5_LOW,'X') <= NVL(BA2.SEGMENT5_HIGH,'X')
940 AND NVL(BA1.SEGMENT5_HIGH,'X') >= NVL(BA2.SEGMENT5_LOW,'X')
941 AND NVL(BA1.SEGMENT6_LOW,'X') <= NVL(BA2.SEGMENT6_HIGH,'X')
942 AND NVL(BA1.SEGMENT6_HIGH,'X') >= NVL(BA2.SEGMENT6_LOW,'X')
943 AND NVL(BA1.SEGMENT7_LOW,'X') <= NVL(BA2.SEGMENT7_HIGH,'X')
944 AND NVL(BA1.SEGMENT7_HIGH,'X') >= NVL(BA2.SEGMENT7_LOW,'X')
945 AND NVL(BA1.SEGMENT8_LOW,'X') <= NVL(BA2.SEGMENT8_HIGH,'X')
946 AND NVL(BA1.SEGMENT8_HIGH,'X') >= NVL(BA2.SEGMENT8_LOW,'X')
947 AND NVL(BA1.SEGMENT9_LOW,'X') <= NVL(BA2.SEGMENT9_HIGH,'X')
948 AND NVL(BA1.SEGMENT9_HIGH,'X') >= NVL(BA2.SEGMENT9_LOW,'X')
949 AND NVL(BA1.SEGMENT10_LOW,'X') <= NVL(BA2.SEGMENT10_HIGH,'X')
950 AND NVL(BA1.SEGMENT10_HIGH,'X') >= NVL(BA2.SEGMENT10_LOW,'X')
951 AND NVL(BA1.SEGMENT11_LOW,'X') <= NVL(BA2.SEGMENT11_HIGH,'X')
952 AND NVL(BA1.SEGMENT11_HIGH,'X') >= NVL(BA2.SEGMENT11_LOW,'X')
953 AND NVL(BA1.SEGMENT12_LOW,'X') <= NVL(BA2.SEGMENT12_HIGH,'X')
954 AND NVL(BA1.SEGMENT12_HIGH,'X') >= NVL(BA2.SEGMENT12_LOW,'X')
955 AND NVL(BA1.SEGMENT13_LOW,'X') <= NVL(BA2.SEGMENT13_HIGH,'X')
956 AND NVL(BA1.SEGMENT13_HIGH,'X') >= NVL(BA2.SEGMENT13_LOW,'X')
957 AND NVL(BA1.SEGMENT14_LOW,'X') <= NVL(BA2.SEGMENT14_HIGH,'X')
958 AND NVL(BA1.SEGMENT14_HIGH,'X') >= NVL(BA2.SEGMENT14_LOW,'X')
959 AND NVL(BA1.SEGMENT15_LOW,'X') <= NVL(BA2.SEGMENT15_HIGH,'X')
960 AND NVL(BA1.SEGMENT15_HIGH,'X') >= NVL(BA2.SEGMENT15_LOW,'X')
961 AND NVL(BA1.SEGMENT16_LOW,'X') <= NVL(BA2.SEGMENT16_HIGH,'X')
962 AND NVL(BA1.SEGMENT16_HIGH,'X') >= NVL(BA2.SEGMENT16_LOW,'X')
963 AND NVL(BA1.SEGMENT17_LOW,'X') <= NVL(BA2.SEGMENT17_HIGH,'X')
964 AND NVL(BA1.SEGMENT17_HIGH,'X') >= NVL(BA2.SEGMENT17_LOW,'X')
965 AND NVL(BA1.SEGMENT18_LOW,'X') <= NVL(BA2.SEGMENT18_HIGH,'X')
966 AND NVL(BA1.SEGMENT18_HIGH,'X') >= NVL(BA2.SEGMENT18_LOW,'X')
967 AND NVL(BA1.SEGMENT19_LOW,'X') <= NVL(BA2.SEGMENT19_HIGH,'X')
968 AND NVL(BA1.SEGMENT19_HIGH,'X') >= NVL(BA2.SEGMENT19_LOW,'X')
969 AND NVL(BA1.SEGMENT20_LOW,'X') <= NVL(BA2.SEGMENT20_HIGH,'X')
970 AND NVL(BA1.SEGMENT20_HIGH,'X') >= NVL(BA2.SEGMENT20_LOW,'X')
971 AND NVL(BA1.SEGMENT21_LOW,'X') <= NVL(BA2.SEGMENT21_HIGH,'X')
972 AND NVL(BA1.SEGMENT21_HIGH,'X') >= NVL(BA2.SEGMENT21_LOW,'X')
973 AND NVL(BA1.SEGMENT22_LOW,'X') <= NVL(BA2.SEGMENT22_HIGH,'X')
974 AND NVL(BA1.SEGMENT22_HIGH,'X') >= NVL(BA2.SEGMENT22_LOW,'X')
975 AND NVL(BA1.SEGMENT23_LOW,'X') <= NVL(BA2.SEGMENT23_HIGH,'X')
976 AND NVL(BA1.SEGMENT23_HIGH,'X') >= NVL(BA2.SEGMENT23_LOW,'X')
977 AND NVL(BA1.SEGMENT24_LOW,'X') <= NVL(BA2.SEGMENT24_HIGH,'X')
978 AND NVL(BA1.SEGMENT24_HIGH,'X') >= NVL(BA2.SEGMENT24_LOW,'X')
979 AND NVL(BA1.SEGMENT25_LOW,'X') <= NVL(BA2.SEGMENT25_HIGH,'X')
980 AND NVL(BA1.SEGMENT25_HIGH,'X') >= NVL(BA2.SEGMENT25_LOW,'X')
981 AND NVL(BA1.SEGMENT26_LOW,'X') <= NVL(BA2.SEGMENT26_HIGH,'X')
982 AND NVL(BA1.SEGMENT26_HIGH,'X') >= NVL(BA2.SEGMENT26_LOW,'X')
983 AND NVL(BA1.SEGMENT27_LOW,'X') <= NVL(BA2.SEGMENT27_HIGH,'X')
984 AND NVL(BA1.SEGMENT27_HIGH,'X') >= NVL(BA2.SEGMENT27_LOW,'X')
985 AND NVL(BA1.SEGMENT28_LOW,'X') <= NVL(BA2.SEGMENT28_HIGH,'X')
986 AND NVL(BA1.SEGMENT28_HIGH,'X') >= NVL(BA2.SEGMENT28_LOW,'X')
987 AND NVL(BA1.SEGMENT29_LOW,'X') <= NVL(BA2.SEGMENT29_HIGH,'X')
988 AND NVL(BA1.SEGMENT29_HIGH,'X') >= NVL(BA2.SEGMENT29_LOW,'X')
989 AND NVL(BA1.SEGMENT30_LOW,'X') <= NVL(BA2.SEGMENT30_HIGH,'X')
990 AND NVL(BA1.SEGMENT30_HIGH,'X') >= NVL(BA2.SEGMENT30_LOW,'X')
991
992 );
993
994 CURSOR C_EXACT_OVERLAPPING_RANGE(p_range_id NUMBER) IS
995 SELECT * FROM IGI_UPG_GL_BUDGET_ASSIGNMENT BA1
996 WHERE EXISTS
997 (SELECT 1 FROM
998 IGI_UPG_GL_BUDGET_ASSIGNMENT BA2
999 WHERE
1000 BA2.range_id = p_range_id
1001 AND BA2.ledger_id = BA1.ledger_id
1002 AND BA2.currency_code = BA1.currency_code
1003 AND BA1.RANGE_ID <> BA2.RANGE_ID
1004 AND NVL(BA1.SEGMENT1_LOW,'X') = NVL(BA2.SEGMENT1_LOW,'X')
1005 AND NVL(BA1.SEGMENT1_HIGH,'X') = NVL(BA2.SEGMENT1_HIGH,'X')
1006 AND NVL(BA1.SEGMENT2_LOW,'X') = NVL(BA2.SEGMENT2_LOW,'X')
1007 AND NVL(BA1.SEGMENT2_HIGH,'X') = NVL(BA2.SEGMENT2_HIGH,'X')
1008 AND NVL(BA1.SEGMENT3_LOW,'X') = NVL(BA2.SEGMENT3_LOW,'X')
1009 AND NVL(BA1.SEGMENT3_HIGH,'X') = NVL(BA2.SEGMENT3_HIGH,'X')
1010 AND NVL(BA1.SEGMENT4_LOW,'X') = NVL(BA2.SEGMENT4_LOW,'X')
1011 AND NVL(BA1.SEGMENT4_HIGH,'X') = NVL(BA2.SEGMENT4_HIGH,'X')
1012 AND NVL(BA1.SEGMENT5_LOW,'X') = NVL(BA2.SEGMENT5_LOW,'X')
1013 AND NVL(BA1.SEGMENT5_HIGH,'X') = NVL(BA2.SEGMENT5_HIGH,'X')
1014 AND NVL(BA1.SEGMENT6_LOW,'X') = NVL(BA2.SEGMENT6_LOW,'X')
1015 AND NVL(BA1.SEGMENT6_HIGH,'X') = NVL(BA2.SEGMENT6_HIGH,'X')
1016 AND NVL(BA1.SEGMENT7_LOW,'X') = NVL(BA2.SEGMENT7_LOW,'X')
1017 AND NVL(BA1.SEGMENT7_HIGH,'X') = NVL(BA2.SEGMENT7_HIGH,'X')
1018 AND NVL(BA1.SEGMENT8_LOW,'X') = NVL(BA2.SEGMENT8_LOW,'X')
1019 AND NVL(BA1.SEGMENT8_HIGH,'X') = NVL(BA2.SEGMENT8_HIGH,'X')
1020 AND NVL(BA1.SEGMENT9_LOW,'X') = NVL(BA2.SEGMENT9_LOW,'X')
1021 AND NVL(BA1.SEGMENT9_HIGH,'X') = NVL(BA2.SEGMENT9_HIGH,'X')
1022 AND NVL(BA1.SEGMENT10_LOW,'X') = NVL(BA2.SEGMENT10_LOW,'X')
1023 AND NVL(BA1.SEGMENT10_HIGH,'X') = NVL(BA2.SEGMENT10_HIGH,'X')
1024 AND NVL(BA1.SEGMENT11_LOW,'X') = NVL(BA2.SEGMENT11_LOW,'X')
1025 AND NVL(BA1.SEGMENT11_HIGH,'X') = NVL(BA2.SEGMENT11_HIGH,'X')
1026 AND NVL(BA1.SEGMENT12_LOW,'X') = NVL(BA2.SEGMENT12_LOW,'X')
1027 AND NVL(BA1.SEGMENT12_HIGH,'X') = NVL(BA2.SEGMENT12_HIGH,'X')
1028 AND NVL(BA1.SEGMENT13_LOW,'X') = NVL(BA2.SEGMENT13_LOW,'X')
1029 AND NVL(BA1.SEGMENT13_HIGH,'X') = NVL(BA2.SEGMENT13_HIGH,'X')
1030 AND NVL(BA1.SEGMENT14_LOW,'X') = NVL(BA2.SEGMENT14_LOW,'X')
1031 AND NVL(BA1.SEGMENT14_HIGH,'X') = NVL(BA2.SEGMENT14_HIGH,'X')
1032 AND NVL(BA1.SEGMENT15_LOW,'X') = NVL(BA2.SEGMENT15_LOW,'X')
1033 AND NVL(BA1.SEGMENT15_HIGH,'X') = NVL(BA2.SEGMENT15_HIGH,'X')
1034 AND NVL(BA1.SEGMENT16_LOW,'X') = NVL(BA2.SEGMENT16_LOW,'X')
1035 AND NVL(BA1.SEGMENT16_HIGH,'X') = NVL(BA2.SEGMENT16_HIGH,'X')
1036 AND NVL(BA1.SEGMENT17_LOW,'X') = NVL(BA2.SEGMENT17_LOW,'X')
1037 AND NVL(BA1.SEGMENT17_HIGH,'X') = NVL(BA2.SEGMENT17_HIGH,'X')
1038 AND NVL(BA1.SEGMENT18_LOW,'X') = NVL(BA2.SEGMENT18_LOW,'X')
1039 AND NVL(BA1.SEGMENT18_HIGH,'X') = NVL(BA2.SEGMENT18_HIGH,'X')
1040 AND NVL(BA1.SEGMENT19_LOW,'X') = NVL(BA2.SEGMENT19_LOW,'X')
1041 AND NVL(BA1.SEGMENT19_HIGH,'X') = NVL(BA2.SEGMENT19_HIGH,'X')
1042 AND NVL(BA1.SEGMENT20_LOW,'X') = NVL(BA2.SEGMENT20_LOW,'X')
1043 AND NVL(BA1.SEGMENT20_HIGH,'X') = NVL(BA2.SEGMENT20_HIGH,'X')
1044 AND NVL(BA1.SEGMENT21_LOW,'X') = NVL(BA2.SEGMENT21_LOW,'X')
1045 AND NVL(BA1.SEGMENT21_HIGH,'X') = NVL(BA2.SEGMENT21_HIGH,'X')
1046 AND NVL(BA1.SEGMENT22_LOW,'X') = NVL(BA2.SEGMENT22_LOW,'X')
1047 AND NVL(BA1.SEGMENT22_HIGH,'X') = NVL(BA2.SEGMENT22_HIGH,'X')
1048 AND NVL(BA1.SEGMENT23_LOW,'X') = NVL(BA2.SEGMENT23_LOW,'X')
1049 AND NVL(BA1.SEGMENT23_HIGH,'X') = NVL(BA2.SEGMENT23_HIGH,'X')
1050 AND NVL(BA1.SEGMENT24_LOW,'X') = NVL(BA2.SEGMENT24_LOW,'X')
1051 AND NVL(BA1.SEGMENT24_HIGH,'X') = NVL(BA2.SEGMENT24_HIGH,'X')
1052 AND NVL(BA1.SEGMENT25_LOW,'X') = NVL(BA2.SEGMENT25_LOW,'X')
1053 AND NVL(BA1.SEGMENT25_HIGH,'X') = NVL(BA2.SEGMENT25_HIGH,'X')
1054 AND NVL(BA1.SEGMENT26_LOW,'X') = NVL(BA2.SEGMENT26_LOW,'X')
1055 AND NVL(BA1.SEGMENT26_HIGH,'X') = NVL(BA2.SEGMENT26_HIGH,'X')
1056 AND NVL(BA1.SEGMENT27_LOW,'X') = NVL(BA2.SEGMENT27_LOW,'X')
1057 AND NVL(BA1.SEGMENT27_HIGH,'X') = NVL(BA2.SEGMENT27_HIGH,'X')
1058 AND NVL(BA1.SEGMENT28_LOW,'X') = NVL(BA2.SEGMENT28_LOW,'X')
1059 AND NVL(BA1.SEGMENT28_HIGH,'X') = NVL(BA2.SEGMENT28_HIGH,'X')
1060 AND NVL(BA1.SEGMENT29_LOW,'X') = NVL(BA2.SEGMENT29_LOW,'X')
1061 AND NVL(BA1.SEGMENT29_HIGH,'X') = NVL(BA2.SEGMENT29_HIGH,'X')
1062 AND NVL(BA1.SEGMENT30_LOW,'X') = NVL(BA2.SEGMENT30_LOW,'X')
1063 AND NVL(BA1.SEGMENT30_HIGH,'X') = NVL(BA2.SEGMENT30_HIGH,'X')
1064 );
1065
1066 CURSOR C_OVERLAPPING_RANGE(p_range_id NUMBER) IS
1067 SELECT * FROM IGI_UPG_GL_BUDGET_ASSIGNMENT BA1
1068 WHERE EXISTS
1069 (SELECT 1 FROM
1070 IGI_UPG_GL_BUDGET_ASSIGNMENT BA2
1071 WHERE
1072 BA2.range_id = p_range_id
1073 AND BA2.ledger_id = BA1.ledger_id
1074 AND BA2.currency_code = BA1.currency_code
1075 AND BA1.RANGE_ID <> BA2.RANGE_ID
1076 AND NVL(BA1.SEGMENT1_LOW,'X') <= NVL(BA2.SEGMENT1_HIGH,'X')
1077 AND NVL(BA1.SEGMENT1_HIGH,'X') >= NVL(BA2.SEGMENT1_LOW,'X')
1078 AND NVL(BA1.SEGMENT2_LOW,'X') <= NVL(BA2.SEGMENT2_HIGH,'X')
1079 AND NVL(BA1.SEGMENT2_HIGH,'X') >= NVL(BA2.SEGMENT2_LOW,'X')
1080 AND NVL(BA1.SEGMENT3_LOW,'X') <= NVL(BA2.SEGMENT3_HIGH,'X')
1081 AND NVL(BA1.SEGMENT3_HIGH,'X') >= NVL(BA2.SEGMENT3_LOW,'X')
1082 AND NVL(BA1.SEGMENT4_LOW,'X') <= NVL(BA2.SEGMENT4_HIGH,'X')
1083 AND NVL(BA1.SEGMENT4_HIGH,'X') >= NVL(BA2.SEGMENT4_LOW,'X')
1084 AND NVL(BA1.SEGMENT5_LOW,'X') <= NVL(BA2.SEGMENT5_HIGH,'X')
1085 AND NVL(BA1.SEGMENT5_HIGH,'X') >= NVL(BA2.SEGMENT5_LOW,'X')
1086 AND NVL(BA1.SEGMENT6_LOW,'X') <= NVL(BA2.SEGMENT6_HIGH,'X')
1087 AND NVL(BA1.SEGMENT6_HIGH,'X') >= NVL(BA2.SEGMENT6_LOW,'X')
1088 AND NVL(BA1.SEGMENT7_LOW,'X') <= NVL(BA2.SEGMENT7_HIGH,'X')
1089 AND NVL(BA1.SEGMENT7_HIGH,'X') >= NVL(BA2.SEGMENT7_LOW,'X')
1090 AND NVL(BA1.SEGMENT8_LOW,'X') <= NVL(BA2.SEGMENT8_HIGH,'X')
1091 AND NVL(BA1.SEGMENT8_HIGH,'X') >= NVL(BA2.SEGMENT8_LOW,'X')
1092 AND NVL(BA1.SEGMENT9_LOW,'X') <= NVL(BA2.SEGMENT9_HIGH,'X')
1093 AND NVL(BA1.SEGMENT9_HIGH,'X') >= NVL(BA2.SEGMENT9_LOW,'X')
1094 AND NVL(BA1.SEGMENT10_LOW,'X') <= NVL(BA2.SEGMENT10_HIGH,'X')
1095 AND NVL(BA1.SEGMENT10_HIGH,'X') >= NVL(BA2.SEGMENT10_LOW,'X')
1096 AND NVL(BA1.SEGMENT11_LOW,'X') <= NVL(BA2.SEGMENT11_HIGH,'X')
1097 AND NVL(BA1.SEGMENT11_HIGH,'X') >= NVL(BA2.SEGMENT11_LOW,'X')
1098 AND NVL(BA1.SEGMENT12_LOW,'X') <= NVL(BA2.SEGMENT12_HIGH,'X')
1099 AND NVL(BA1.SEGMENT12_HIGH,'X') >= NVL(BA2.SEGMENT12_LOW,'X')
1100 AND NVL(BA1.SEGMENT13_LOW,'X') <= NVL(BA2.SEGMENT13_HIGH,'X')
1101 AND NVL(BA1.SEGMENT13_HIGH,'X') >= NVL(BA2.SEGMENT13_LOW,'X')
1102 AND NVL(BA1.SEGMENT14_LOW,'X') <= NVL(BA2.SEGMENT14_HIGH,'X')
1103 AND NVL(BA1.SEGMENT14_HIGH,'X') >= NVL(BA2.SEGMENT14_LOW,'X')
1104 AND NVL(BA1.SEGMENT15_LOW,'X') <= NVL(BA2.SEGMENT15_HIGH,'X')
1105 AND NVL(BA1.SEGMENT15_HIGH,'X') >= NVL(BA2.SEGMENT15_LOW,'X')
1106 AND NVL(BA1.SEGMENT16_LOW,'X') <= NVL(BA2.SEGMENT16_HIGH,'X')
1107 AND NVL(BA1.SEGMENT16_HIGH,'X') >= NVL(BA2.SEGMENT16_LOW,'X')
1108 AND NVL(BA1.SEGMENT17_LOW,'X') <= NVL(BA2.SEGMENT17_HIGH,'X')
1109 AND NVL(BA1.SEGMENT17_HIGH,'X') >= NVL(BA2.SEGMENT17_LOW,'X')
1110 AND NVL(BA1.SEGMENT18_LOW,'X') <= NVL(BA2.SEGMENT18_HIGH,'X')
1111 AND NVL(BA1.SEGMENT18_HIGH,'X') >= NVL(BA2.SEGMENT18_LOW,'X')
1112 AND NVL(BA1.SEGMENT19_LOW,'X') <= NVL(BA2.SEGMENT19_HIGH,'X')
1113 AND NVL(BA1.SEGMENT19_HIGH,'X') >= NVL(BA2.SEGMENT19_LOW,'X')
1114 AND NVL(BA1.SEGMENT20_LOW,'X') <= NVL(BA2.SEGMENT20_HIGH,'X')
1115 AND NVL(BA1.SEGMENT20_HIGH,'X') >= NVL(BA2.SEGMENT20_LOW,'X')
1116 AND NVL(BA1.SEGMENT21_LOW,'X') <= NVL(BA2.SEGMENT21_HIGH,'X')
1117 AND NVL(BA1.SEGMENT21_HIGH,'X') >= NVL(BA2.SEGMENT21_LOW,'X')
1118 AND NVL(BA1.SEGMENT22_LOW,'X') <= NVL(BA2.SEGMENT22_HIGH,'X')
1119 AND NVL(BA1.SEGMENT22_HIGH,'X') >= NVL(BA2.SEGMENT22_LOW,'X')
1120 AND NVL(BA1.SEGMENT23_LOW,'X') <= NVL(BA2.SEGMENT23_HIGH,'X')
1121 AND NVL(BA1.SEGMENT23_HIGH,'X') >= NVL(BA2.SEGMENT23_LOW,'X')
1122 AND NVL(BA1.SEGMENT24_LOW,'X') <= NVL(BA2.SEGMENT24_HIGH,'X')
1123 AND NVL(BA1.SEGMENT24_HIGH,'X') >= NVL(BA2.SEGMENT24_LOW,'X')
1124 AND NVL(BA1.SEGMENT25_LOW,'X') <= NVL(BA2.SEGMENT25_HIGH,'X')
1125 AND NVL(BA1.SEGMENT25_HIGH,'X') >= NVL(BA2.SEGMENT25_LOW,'X')
1126 AND NVL(BA1.SEGMENT26_LOW,'X') <= NVL(BA2.SEGMENT26_HIGH,'X')
1127 AND NVL(BA1.SEGMENT26_HIGH,'X') >= NVL(BA2.SEGMENT26_LOW,'X')
1128 AND NVL(BA1.SEGMENT27_LOW,'X') <= NVL(BA2.SEGMENT27_HIGH,'X')
1129 AND NVL(BA1.SEGMENT27_HIGH,'X') >= NVL(BA2.SEGMENT27_LOW,'X')
1130 AND NVL(BA1.SEGMENT28_LOW,'X') <= NVL(BA2.SEGMENT28_HIGH,'X')
1131 AND NVL(BA1.SEGMENT28_HIGH,'X') >= NVL(BA2.SEGMENT28_LOW,'X')
1132 AND NVL(BA1.SEGMENT29_LOW,'X') <= NVL(BA2.SEGMENT29_HIGH,'X')
1133 AND NVL(BA1.SEGMENT29_HIGH,'X') >= NVL(BA2.SEGMENT29_LOW,'X')
1134 AND NVL(BA1.SEGMENT30_LOW,'X') <= NVL(BA2.SEGMENT30_HIGH,'X')
1135 AND NVL(BA1.SEGMENT30_HIGH,'X') >= NVL(BA2.SEGMENT30_LOW,'X')
1136 );
1137
1138 CURSOR C_ALL_RANGES IS
1139 SELECT * FROM IGI_UPG_GL_BUDGET_ASSIGNMENT BA1;
1140
1141
1142 CURSOR C_BC_OPTIONS (p_range_id NUMBER)
1143 IS
1144 SELECT * FROM IGI_UPG_GL_BUDORG_BC_OPTIONS
1145 WHERE RANGE_ID = p_range_id;
1146
1147 TYPE non_overlapping_ranges_tab IS TABLE OF C_NON_OVERLAPPING_RANGES%ROWTYPE;
1148
1149 lc_non_overlapping_ranges non_overlapping_ranges_tab;
1150
1151 lc_exact_merge_range1 IGI_UPG_GL_BUDGET_ASSIGNMENT%ROWTYPE;
1152 lc_exact_merge_range1_bc BC_OPTIONS_TAB;
1153
1154 lc_exact_merge_range2 IGI_UPG_GL_BUDGET_ASSIGNMENT%ROWTYPE;
1155 lc_exact_merge_range2_bc BC_OPTIONS_TAB;
1156
1157
1158
1159 lc_merge_range1 IGI_UPG_GL_BUDGET_ASSIGNMENT%ROWTYPE;
1160 lc_merge_range1_bc BC_OPTIONS_TAB;
1161
1162 lc_merge_range2 IGI_UPG_GL_BUDGET_ASSIGNMENT%ROWTYPE;
1163 lc_merge_range2_bc BC_OPTIONS_TAB;
1164
1165 p_segment1 segment_range_rec;
1166 p_segment2 segment_range_rec;
1167 l_index_number NUMBER;
1168 l_data_type NUMBER;
1169
1170 l_exact_overlap_exists BOOLEAN;
1171
1172 p_segment_tab segment_type;
1173
1174 p_budget_range1 BUDGET_RANGE_REC;
1175 p_budget_range2 BUDGET_RANGE_REC;
1176
1177 l_final_budget_ranges BUDGET_RANGE_TYPE;
1178
1179 l_range_id_seq NUMBER(38);
1180 l_seq_number NUMBER;
1181
1182 l_actual_range_id NUMBER(38);
1183
1184 l_errbuf VARCHAR2(2000);
1185 l_retcode NUMBER;
1186
1187 BEGIN
1188
1189 l_data_type := p_data_type;
1190 fnd_file.put_line(fnd_file.output,'Ledger - Budget Organization - Currency - Range From - Range To ');
1191 BEGIN
1192 SELECT gl_budget_assignment_ranges_s.NEXTVAL
1193 INTO l_range_id_seq
1194 FROM dual;
1195 l_range_id_seq := l_range_id_seq + 1;
1196 EXCEPTION
1197 WHEN OTHERS THEN
1198 fnd_file.put_line(fnd_file.output, 'Error fetching sequence value from gl_budget_assignment_ranges_s');
1199 fnd_file.put_line(fnd_file.log, 'Module: LOOP_AND_PROCESS =>'||'Error fetching sequence value
1200 from gl_budget_assignment_ranges_s');
1201 errbuf := 'Module: LOOP_AND_PROCESS => Error fetching sequence value from gl_budget_assignment_ranges_s';
1202 retcode := -1;
1203 RETURN;
1204 END;
1205
1206 IF l_range_id_seq IS NULL OR l_range_id_seq = 0 THEN
1207 l_range_id_seq := 0;
1208 END IF;
1209
1210
1211 LOOP
1212 l_exact_overlap_exists := FALSE;
1213 --First open NON)OVERLAPPING_RANGES and put them into GL table
1214 OPEN C_NON_OVERLAPPING_RANGES;
1215 FETCH C_NON_OVERLAPPING_RANGES BULK COLLECT INTO lc_non_overlapping_ranges;
1216 CLOSE C_NON_OVERLAPPING_RANGES;
1217
1218 FOR i IN 1..lc_non_overlapping_ranges.COUNT LOOP
1219 IF p_mode = 1 THEN
1220 BEGIN
1221 SELECT gl_budget_assignment_ranges_s.NEXTVAL
1222 INTO l_actual_range_id
1223 FROM dual;
1224 EXCEPTION
1225 WHEN OTHERS THEN
1226 fnd_file.put_line(fnd_file.output, 'Error fetching sequence value from gl_budget_assignment_ranges_s');
1227 fnd_file.put_line(fnd_file.log, 'Module: LOOP_AND_PROCESS (overlap) =>'
1228 ||'Error fetching sequence value from gl_budget_assignment_ranges_s');
1229 errbuf := 'Module: LOOP_AND_PROCESS (overlap) => Error fetching sequence value from gl_budget_assignment_ranges_s';
1230 retcode := -1;
1231 RETURN;
1232 END;
1233
1234 PRINT_BUDGET_INFO(lc_non_overlapping_ranges(i).range_id,
1235 l_errbuf,
1236 l_retcode);
1237
1238 IF l_retcode IS NOT NULL and l_retcode = -1 THEN
1239 errbuf := l_errbuf;
1240 retcode := l_retcode;
1241 RETURN;
1242 END IF;
1243
1244 BEGIN
1245
1246 INSERT INTO IGI_EFC_UPG_BACKUP_INFO (range_id) VALUES (l_actual_range_id);
1247
1248 INSERT INTO GL_BUDGET_ASSIGNMENT_RANGES
1249 (
1250 BUDGET_ENTITY_ID,
1251 LEDGER_ID,
1252 CURRENCY_CODE,
1253 ENTRY_CODE,
1254 RANGE_ID,
1255 STATUS,
1256 LAST_UPDATE_DATE,
1257 AUTOMATIC_ENCUMBRANCE_FLAG,
1258 CREATED_BY,
1259 CREATION_DATE,
1260 FUNDS_CHECK_LEVEL_CODE,
1261 LAST_UPDATED_BY,
1262 LAST_UPDATE_LOGIN,
1263 SEQUENCE_NUMBER,
1264 SEGMENT1_LOW,
1265 SEGMENT1_HIGH,
1266 SEGMENT2_LOW,
1267 SEGMENT2_HIGH,
1268 SEGMENT3_LOW,
1269 SEGMENT3_HIGH,
1270 SEGMENT4_LOW,
1271 SEGMENT4_HIGH,
1272 SEGMENT5_LOW,
1273 SEGMENT5_HIGH,
1274 SEGMENT6_LOW,
1275 SEGMENT6_HIGH,
1276 SEGMENT7_LOW,
1277 SEGMENT7_HIGH,
1278 SEGMENT8_LOW,
1279 SEGMENT8_HIGH,
1280 SEGMENT9_LOW,
1281 SEGMENT9_HIGH,
1282 SEGMENT10_LOW,
1283 SEGMENT10_HIGH,
1284 SEGMENT11_LOW,
1285 SEGMENT11_HIGH,
1286 SEGMENT12_LOW,
1287 SEGMENT12_HIGH,
1288 SEGMENT13_LOW,
1289 SEGMENT13_HIGH,
1290 SEGMENT14_LOW,
1291 SEGMENT14_HIGH,
1292 SEGMENT15_LOW,
1293 SEGMENT15_HIGH,
1294 SEGMENT16_LOW,
1295 SEGMENT16_HIGH,
1296 SEGMENT17_LOW,
1297 SEGMENT17_HIGH,
1298 SEGMENT18_LOW,
1299 SEGMENT18_HIGH,
1300 SEGMENT19_LOW,
1301 SEGMENT19_HIGH,
1302 SEGMENT20_LOW,
1303 SEGMENT20_HIGH,
1304 SEGMENT21_LOW,
1305 SEGMENT21_HIGH,
1306 SEGMENT22_LOW,
1307 SEGMENT22_HIGH,
1308 SEGMENT23_LOW,
1309 SEGMENT23_HIGH,
1310 SEGMENT24_LOW,
1311 SEGMENT24_HIGH,
1312 SEGMENT25_LOW,
1313 SEGMENT25_HIGH,
1314 SEGMENT26_LOW,
1315 SEGMENT26_HIGH,
1316 SEGMENT27_LOW,
1317 SEGMENT27_HIGH,
1318 SEGMENT28_LOW,
1319 SEGMENT28_HIGH,
1320 SEGMENT29_LOW,
1321 SEGMENT29_HIGH,
1322 SEGMENT30_LOW,
1323 SEGMENT30_HIGH,
1324 AMOUNT_TYPE,
1325 BOUNDARY_CODE,
1326 CONTEXT,
1327 FUNDING_BUDGET_VERSION_ID,
1328 PROGRAM_APPLICATION_ID,
1329 PROGRAM_ID,
1330 PROGRAM_UPDATE_DATE,
1331 REQUEST_ID,
1332 ATTRIBUTE1,
1333 ATTRIBUTE2,
1334 ATTRIBUTE3,
1335 ATTRIBUTE4,
1336 ATTRIBUTE5,
1337 ATTRIBUTE6,
1338 ATTRIBUTE7,
1339 ATTRIBUTE8,
1340 ATTRIBUTE9,
1341 ATTRIBUTE10,
1342 ATTRIBUTE11,
1343 ATTRIBUTE12,
1344 ATTRIBUTE13,
1345 ATTRIBUTE14,
1346 ATTRIBUTE15
1347 )
1348
1349 VALUES (
1350 lc_non_overlapping_ranges(i).budget_entity_id,
1351 lc_non_overlapping_ranges(i).ledger_id,
1352 lc_non_overlapping_ranges(i).currency_code,
1353 lc_non_overlapping_ranges(i).entry_code,
1354 l_actual_range_id,
1355 lc_non_overlapping_ranges(i).status,
1356 sysdate,
1357 lc_non_overlapping_ranges(i).automatic_encumbrance_flag,
1358 lc_non_overlapping_ranges(i).created_by,
1359 lc_non_overlapping_ranges(i).creation_date,
1360 lc_non_overlapping_ranges(i).funds_check_level_code,
1361 lc_non_overlapping_ranges(i).last_updated_by,
1362 lc_non_overlapping_ranges(i).last_update_login,
1363 lc_non_overlapping_ranges(i).sequence_number,
1364 lc_non_overlapping_ranges(i).SEGMENT1_LOW,
1365 lc_non_overlapping_ranges(i).SEGMENT1_HIGH,
1366 lc_non_overlapping_ranges(i).SEGMENT2_LOW,
1367 lc_non_overlapping_ranges(i).SEGMENT2_HIGH,
1368 lc_non_overlapping_ranges(i).SEGMENT3_LOW,
1369 lc_non_overlapping_ranges(i).SEGMENT3_HIGH,
1370 lc_non_overlapping_ranges(i).SEGMENT4_LOW,
1371 lc_non_overlapping_ranges(i).SEGMENT4_HIGH,
1372 lc_non_overlapping_ranges(i).SEGMENT5_LOW,
1373 lc_non_overlapping_ranges(i).SEGMENT5_HIGH,
1374 lc_non_overlapping_ranges(i).SEGMENT6_LOW,
1375 lc_non_overlapping_ranges(i).SEGMENT6_HIGH,
1376 lc_non_overlapping_ranges(i).SEGMENT7_LOW,
1377 lc_non_overlapping_ranges(i).SEGMENT7_HIGH,
1378 lc_non_overlapping_ranges(i).SEGMENT8_LOW,
1379 lc_non_overlapping_ranges(i).SEGMENT8_HIGH,
1380 lc_non_overlapping_ranges(i).SEGMENT9_LOW,
1381 lc_non_overlapping_ranges(i).SEGMENT9_HIGH,
1382 lc_non_overlapping_ranges(i).SEGMENT10_LOW,
1383 lc_non_overlapping_ranges(i).SEGMENT10_HIGH,
1384 lc_non_overlapping_ranges(i).SEGMENT11_LOW,
1385 lc_non_overlapping_ranges(i).SEGMENT11_HIGH,
1386 lc_non_overlapping_ranges(i).SEGMENT12_LOW,
1387 lc_non_overlapping_ranges(i).SEGMENT12_HIGH,
1388 lc_non_overlapping_ranges(i).SEGMENT13_LOW,
1389 lc_non_overlapping_ranges(i).SEGMENT13_HIGH,
1390 lc_non_overlapping_ranges(i).SEGMENT14_LOW,
1391 lc_non_overlapping_ranges(i).SEGMENT14_HIGH,
1392 lc_non_overlapping_ranges(i).SEGMENT15_LOW,
1393 lc_non_overlapping_ranges(i).SEGMENT15_HIGH,
1394 lc_non_overlapping_ranges(i).SEGMENT16_LOW,
1395 lc_non_overlapping_ranges(i).SEGMENT16_HIGH,
1396 lc_non_overlapping_ranges(i).SEGMENT17_LOW,
1397 lc_non_overlapping_ranges(i).SEGMENT17_HIGH,
1398 lc_non_overlapping_ranges(i).SEGMENT18_LOW,
1399 lc_non_overlapping_ranges(i).SEGMENT18_HIGH,
1400 lc_non_overlapping_ranges(i).SEGMENT19_LOW,
1401 lc_non_overlapping_ranges(i).SEGMENT19_HIGH,
1402 lc_non_overlapping_ranges(i).SEGMENT20_LOW,
1403 lc_non_overlapping_ranges(i).SEGMENT20_HIGH,
1404 lc_non_overlapping_ranges(i).SEGMENT21_LOW,
1405 lc_non_overlapping_ranges(i).SEGMENT21_HIGH,
1406 lc_non_overlapping_ranges(i).SEGMENT22_LOW,
1407 lc_non_overlapping_ranges(i).SEGMENT22_HIGH,
1408 lc_non_overlapping_ranges(i).SEGMENT23_LOW,
1409 lc_non_overlapping_ranges(i).SEGMENT23_HIGH,
1410 lc_non_overlapping_ranges(i).SEGMENT24_LOW,
1411 lc_non_overlapping_ranges(i).SEGMENT24_HIGH,
1412 lc_non_overlapping_ranges(i).SEGMENT25_LOW,
1413 lc_non_overlapping_ranges(i).SEGMENT25_HIGH,
1414 lc_non_overlapping_ranges(i).SEGMENT26_LOW,
1415 lc_non_overlapping_ranges(i).SEGMENT26_HIGH,
1416 lc_non_overlapping_ranges(i).SEGMENT27_LOW,
1417 lc_non_overlapping_ranges(i).SEGMENT27_HIGH,
1418 lc_non_overlapping_ranges(i).SEGMENT28_LOW,
1419 lc_non_overlapping_ranges(i).SEGMENT28_HIGH,
1420 lc_non_overlapping_ranges(i).SEGMENT29_LOW,
1421 lc_non_overlapping_ranges(i).SEGMENT29_HIGH,
1422 lc_non_overlapping_ranges(i).SEGMENT30_LOW,
1423 lc_non_overlapping_ranges(i).SEGMENT30_HIGH,
1424 lc_non_overlapping_ranges(i).amount_type,
1425 lc_non_overlapping_ranges(i).boundary_code,
1426 lc_non_overlapping_ranges(i).context,
1427 lc_non_overlapping_ranges(i).funding_budget_version_id,
1428 lc_non_overlapping_ranges(i).program_application_id,
1429 lc_non_overlapping_ranges(i).program_id,
1430 lc_non_overlapping_ranges(i).program_update_date,
1431 lc_non_overlapping_ranges(i).request_id,
1432 lc_non_overlapping_ranges(i).attribute1,
1433 lc_non_overlapping_ranges(i).attribute2,
1434 lc_non_overlapping_ranges(i).attribute3,
1435 lc_non_overlapping_ranges(i).attribute4,
1436 lc_non_overlapping_ranges(i).attribute5,
1437 lc_non_overlapping_ranges(i).attribute6,
1438 lc_non_overlapping_ranges(i).attribute7,
1439 lc_non_overlapping_ranges(i).attribute8,
1440 lc_non_overlapping_ranges(i).attribute9,
1441 lc_non_overlapping_ranges(i).attribute10,
1442 lc_non_overlapping_ranges(i).attribute11,
1443 lc_non_overlapping_ranges(i).attribute12,
1444 lc_non_overlapping_ranges(i).attribute13,
1445 lc_non_overlapping_ranges(i).attribute14,
1446 lc_non_overlapping_ranges(i).attribute15
1447 );
1448
1449 INSERT INTO GL_BUDORG_BC_OPTIONS
1450 (
1451 RANGE_ID,
1452 FUNDING_BUDGET_VERSION_ID,
1453 FUNDS_CHECK_LEVEL_CODE,
1454 AMOUNT_TYPE,
1455 BOUNDARY_CODE,
1456 CREATED_BY,
1457 CREATION_DATE,
1458 LAST_UPDATED_BY,
1459 LAST_UPDATE_LOGIN,
1460 LAST_UPDATE_DATE
1461 )
1462 SELECT
1463 l_actual_range_id,
1464 FUNDING_BUDGET_VERSION_ID,
1465 FUNDS_CHECK_LEVEL_CODE,
1466 AMOUNT_TYPE,
1467 BOUNDARY_CODE,
1468 CREATED_BY,
1469 CREATION_DATE,
1470 LAST_UPDATED_BY,
1471 LAST_UPDATE_LOGIN,
1472 LAST_UPDATE_DATE
1473 FROM IGI_UPG_GL_BUDORG_BC_OPTIONS
1474 WHERE RANGE_ID = lc_non_overlapping_ranges(i).range_id;
1475
1476
1477 DELETE FROM IGI_UPG_GL_BUDORG_BC_OPTIONS WHERE RANGE_ID = lc_non_overlapping_ranges(i).range_id;
1478
1479 DELETE FROM IGI_UPG_GL_BUDGET_ASSIGNMENT WHERE RANGE_ID = lc_non_overlapping_ranges(i).range_id;
1480
1481
1482
1483 EXCEPTION
1484 WHEN OTHERS THEN
1485 fnd_file.put_line(fnd_file.output, 'Error processing demerged data');
1486 fnd_file.put_line(fnd_file.log, 'Module: LOOP_AND_PROCESS =>'||'Error processing demerged data =>'||SQLERRM);
1487 errbuf := 'Module: LOOP_AND_PROCESS =>'||'Error processing demerged data =>'||SQLERRM;
1488 retcode := -1;
1489 RETURN;
1490 END;
1491 ELSE
1492 BEGIN
1493
1494 PRINT_BUDGET_INFO(lc_non_overlapping_ranges(i).range_id,
1495 l_errbuf,
1496 l_retcode);
1497 IF l_retcode IS NOT NULL and l_retcode = -1 THEN
1498 errbuf := l_errbuf;
1499 retcode := l_retcode;
1500 RETURN;
1501 END IF;
1502
1503
1504 DELETE FROM IGI_UPG_GL_BUDORG_BC_OPTIONS WHERE RANGE_ID = lc_non_overlapping_ranges(i).range_id;
1505
1506 DELETE FROM IGI_UPG_GL_BUDGET_ASSIGNMENT WHERE RANGE_ID = lc_non_overlapping_ranges(i).range_id;
1507 EXCEPTION
1508 WHEN OTHERS THEN
1509 fnd_file.put_line(fnd_file.output, 'Error processing demerged data - prelim mode');
1510 fnd_file.put_line(fnd_file.log, 'Module: LOOP_AND_PROCESS =>'
1511 ||'Error processing demerged data - prelim mode =>'||SQLERRM);
1512 errbuf := 'Module: LOOP_AND_PROCESS =>'||'Error processing demerged data - prelim mode =>'||SQLERRM;
1513 retcode := -1;
1514 RETURN;
1515 END;
1516 END IF;
1517 END LOOP;
1518
1519 --Then check for exact overlap
1520 OPEN C_ALL_RANGES;
1521 FETCH C_ALL_RANGES INTO lc_exact_merge_range1;
1522 IF C_ALL_RANGES%NOTFOUND THEN
1523 CLOSE C_ALL_RANGES;
1524 fnd_file.put_line(fnd_file.log, 'Module: LOOP_AND_PROCESS => Processing completed');
1525 EXIT;
1526 END IF;
1527 CLOSE C_ALL_RANGES;
1528
1529
1530
1531 OPEN C_EXACT_OVERLAPPING_RANGE(lc_exact_merge_range1.range_id);
1532 FETCH C_EXACT_OVERLAPPING_RANGE INTO lc_exact_merge_range2;
1533 IF C_EXACT_OVERLAPPING_RANGE%FOUND THEN
1534 l_exact_overlap_exists := TRUE;
1535 ELSE
1536 l_exact_overlap_exists := FALSE;
1537 END IF;
1538 CLOSE C_EXACT_OVERLAPPING_RANGE;
1539
1540 IF l_exact_overlap_exists THEN
1541
1542
1543 BEGIN
1544
1545 UPDATE IGI_UPG_GL_BUDORG_BC_OPTIONS
1546 SET RANGE_ID = lc_exact_merge_range1.range_id
1547 WHERE RANGE_ID = lc_exact_merge_range2.range_id;
1548
1549 DELETE FROM IGI_UPG_GL_BUDGET_ASSIGNMENT WHERE RANGE_ID = lc_exact_merge_range2.range_id;
1550
1551 EXCEPTION
1552 WHEN OTHERS THEN
1553 fnd_file.put_line(fnd_file.output, 'Error merging data which overlaps exactly');
1554 fnd_file.put_line(fnd_file.log, 'Module: LOOP_AND_PROCESS =>'
1555 ||'Error merging data which overlaps exactly =>'||SQLERRM);
1556 errbuf := 'Module: LOOP_AND_PROCESS =>'||'Error merging data which overlaps exactly =>'||SQLERRM;
1557 retcode := -1;
1558 RETURN;
1559 END;
1560 --Continue executing the loop
1561 --Goto is used to emulate the behaviour of continue
1562 GOTO CONTINUE;
1563 END IF;
1564
1565 --Then do the splitting logic
1566
1567 OPEN C_ALL_RANGES;
1568 FETCH C_ALL_RANGES INTO lc_merge_range1;
1569 IF C_ALL_RANGES%NOTFOUND THEN
1570 CLOSE C_ALL_RANGES;
1571 fnd_file.put_line(fnd_file.log, 'Module: LOOP_AND_PROCESS => Processing completed');
1572 EXIT;
1573 END IF;
1574 IF C_ALL_RANGES%FOUND AND l_data_type <> 0 THEN
1575 CLOSE C_ALL_RANGES;
1576 fnd_file.put_line(fnd_file.output, 'Module: LOOP_AND_PROCESS - You have segment ranges which merge with other segment ranges
1577 for the same ledger and currency. This upgrade script does
1578 not support merging segment ranges for segments which have variable
1579 non numeric values. Merging segment ranges are supported only if your
1580 segment contains fixed length numeric values. Please contact Oracle Support for help');
1581 fnd_file.put_line(fnd_file.log, 'Module: LOOP_AND_PROCESS - You have segment ranges which merge with other segment ranges
1582 for the same ledger and currency code. This upgrade script does
1583 not support merging segment ranges for segments which have variable
1584 non numeric values. Merging segment ranges are supported only if your
1585 segment contains fixed length numeric values. Please contact Oracle Support for help');
1586 errbuf := 'Aborting Upgrade Non numeric segments exists and merging ranges are found';
1587 retcode := -1;
1588 RETURN;
1589 END IF;
1590 CLOSE C_ALL_RANGES;
1591
1592 OPEN C_BC_OPTIONS(lc_merge_range1.range_id);
1593 FETCH C_BC_OPTIONS BULK COLLECT INTO lc_merge_range1_bc;
1594 CLOSE C_BC_OPTIONS;
1595
1596 OPEN C_OVERLAPPING_RANGE(lc_merge_range1.range_id);
1597 FETCH C_OVERLAPPING_RANGE INTO lc_merge_range2;
1598 CLOSE C_OVERLAPPING_RANGE;
1599
1600 OPEN C_BC_OPTIONS(lc_merge_range2.range_id);
1601 FETCH C_BC_OPTIONS BULK COLLECT INTO lc_merge_range2_bc;
1602 CLOSE C_BC_OPTIONS;
1603
1604 p_budget_range1.range_id := lc_merge_range1.range_id;
1605 p_budget_range1.segment_range_tab := segment_range_type();
1606 p_budget_range1.segment_range_tab.extend(30);
1607
1608 p_budget_range1.segment_range_tab(1).segment_low := lc_merge_range1.SEGMENT1_LOW;
1609 p_budget_range1.segment_range_tab(1).segment_high := lc_merge_range1.SEGMENT1_HIGH;
1610 p_budget_range1.segment_range_tab(1).data_type := l_data_type;
1611
1612 p_budget_range1.segment_range_tab(2).segment_low := lc_merge_range1.SEGMENT2_LOW;
1613 p_budget_range1.segment_range_tab(2).segment_high := lc_merge_range1.SEGMENT2_HIGH;
1614 p_budget_range1.segment_range_tab(2).data_type := l_data_type;
1615
1616 p_budget_range1.segment_range_tab(3).segment_low := lc_merge_range1.SEGMENT3_LOW;
1617 p_budget_range1.segment_range_tab(3).segment_high := lc_merge_range1.SEGMENT3_HIGH;
1618 p_budget_range1.segment_range_tab(3).data_type := l_data_type;
1619
1620 p_budget_range1.segment_range_tab(4).segment_low := lc_merge_range1.SEGMENT4_LOW;
1621 p_budget_range1.segment_range_tab(4).segment_high := lc_merge_range1.SEGMENT4_HIGH;
1622 p_budget_range1.segment_range_tab(4).data_type := l_data_type;
1623
1624 p_budget_range1.segment_range_tab(5).segment_low := lc_merge_range1.SEGMENT5_LOW;
1625 p_budget_range1.segment_range_tab(5).segment_high := lc_merge_range1.SEGMENT5_HIGH;
1626 p_budget_range1.segment_range_tab(5).data_type := l_data_type;
1627
1628 p_budget_range1.segment_range_tab(6).segment_low := lc_merge_range1.SEGMENT6_LOW;
1629 p_budget_range1.segment_range_tab(6).segment_high := lc_merge_range1.SEGMENT6_HIGH;
1630 p_budget_range1.segment_range_tab(6).data_type := l_data_type;
1631
1632 p_budget_range1.segment_range_tab(7).segment_low := lc_merge_range1.SEGMENT7_LOW;
1633 p_budget_range1.segment_range_tab(7).segment_high := lc_merge_range1.SEGMENT7_HIGH;
1634 p_budget_range1.segment_range_tab(7).data_type := l_data_type;
1635
1636 p_budget_range1.segment_range_tab(8).segment_low := lc_merge_range1.SEGMENT8_LOW;
1637 p_budget_range1.segment_range_tab(8).segment_high := lc_merge_range1.SEGMENT8_HIGH;
1638 p_budget_range1.segment_range_tab(8).data_type := l_data_type;
1639
1640 p_budget_range1.segment_range_tab(9).segment_low := lc_merge_range1.SEGMENT9_LOW;
1641 p_budget_range1.segment_range_tab(9).segment_high := lc_merge_range1.SEGMENT9_HIGH;
1642 p_budget_range1.segment_range_tab(9).data_type := l_data_type;
1643
1644 p_budget_range1.segment_range_tab(10).segment_low := lc_merge_range1.SEGMENT10_LOW;
1645 p_budget_range1.segment_range_tab(10).segment_high := lc_merge_range1.SEGMENT10_HIGH;
1646 p_budget_range1.segment_range_tab(10).data_type := l_data_type;
1647
1648 p_budget_range1.segment_range_tab(11).segment_low := lc_merge_range1.SEGMENT11_LOW;
1649 p_budget_range1.segment_range_tab(11).segment_high := lc_merge_range1.SEGMENT11_HIGH;
1650 p_budget_range1.segment_range_tab(11).data_type := l_data_type;
1651
1652 p_budget_range1.segment_range_tab(12).segment_low := lc_merge_range1.SEGMENT12_LOW;
1653 p_budget_range1.segment_range_tab(12).segment_high := lc_merge_range1.SEGMENT12_HIGH;
1654 p_budget_range1.segment_range_tab(12).data_type := l_data_type;
1655
1656 p_budget_range1.segment_range_tab(13).segment_low := lc_merge_range1.SEGMENT13_LOW;
1657 p_budget_range1.segment_range_tab(13).segment_high := lc_merge_range1.SEGMENT13_HIGH;
1658 p_budget_range1.segment_range_tab(13).data_type := l_data_type;
1659
1660 p_budget_range1.segment_range_tab(14).segment_low := lc_merge_range1.SEGMENT14_LOW;
1661 p_budget_range1.segment_range_tab(14).segment_high := lc_merge_range1.SEGMENT14_HIGH;
1662 p_budget_range1.segment_range_tab(14).data_type := l_data_type;
1663
1664 p_budget_range1.segment_range_tab(15).segment_low := lc_merge_range1.SEGMENT15_LOW;
1665 p_budget_range1.segment_range_tab(15).segment_high := lc_merge_range1.SEGMENT15_HIGH;
1666 p_budget_range1.segment_range_tab(15).data_type := l_data_type;
1667
1668 p_budget_range1.segment_range_tab(16).segment_low := lc_merge_range1.SEGMENT16_LOW;
1669 p_budget_range1.segment_range_tab(16).segment_high := lc_merge_range1.SEGMENT16_HIGH;
1670 p_budget_range1.segment_range_tab(16).data_type := l_data_type;
1671
1672 p_budget_range1.segment_range_tab(17).segment_low := lc_merge_range1.SEGMENT17_LOW;
1673 p_budget_range1.segment_range_tab(17).segment_high := lc_merge_range1.SEGMENT17_HIGH;
1674 p_budget_range1.segment_range_tab(17).data_type := l_data_type;
1675
1676 p_budget_range1.segment_range_tab(18).segment_low := lc_merge_range1.SEGMENT18_LOW;
1677 p_budget_range1.segment_range_tab(18).segment_high := lc_merge_range1.SEGMENT18_HIGH;
1678 p_budget_range1.segment_range_tab(18).data_type := l_data_type;
1679
1680 p_budget_range1.segment_range_tab(19).segment_low := lc_merge_range1.SEGMENT19_LOW;
1681 p_budget_range1.segment_range_tab(19).segment_high := lc_merge_range1.SEGMENT19_HIGH;
1682 p_budget_range1.segment_range_tab(19).data_type := l_data_type;
1683
1684 p_budget_range1.segment_range_tab(20).segment_low := lc_merge_range1.SEGMENT20_LOW;
1685 p_budget_range1.segment_range_tab(20).segment_high := lc_merge_range1.SEGMENT20_HIGH;
1686 p_budget_range1.segment_range_tab(20).data_type := l_data_type;
1687
1688 p_budget_range1.segment_range_tab(21).segment_low := lc_merge_range1.SEGMENT21_LOW;
1689 p_budget_range1.segment_range_tab(21).segment_high := lc_merge_range1.SEGMENT21_HIGH;
1690 p_budget_range1.segment_range_tab(21).data_type := l_data_type;
1691
1692 p_budget_range1.segment_range_tab(22).segment_low := lc_merge_range1.SEGMENT22_LOW;
1693 p_budget_range1.segment_range_tab(22).segment_high := lc_merge_range1.SEGMENT22_HIGH;
1694 p_budget_range1.segment_range_tab(22).data_type := l_data_type;
1695
1696 p_budget_range1.segment_range_tab(23).segment_low := lc_merge_range1.SEGMENT23_LOW;
1697 p_budget_range1.segment_range_tab(23).segment_high := lc_merge_range1.SEGMENT23_HIGH;
1698 p_budget_range1.segment_range_tab(23).data_type := l_data_type;
1699
1700 p_budget_range1.segment_range_tab(24).segment_low := lc_merge_range1.SEGMENT24_LOW;
1701 p_budget_range1.segment_range_tab(24).segment_high := lc_merge_range1.SEGMENT24_HIGH;
1702 p_budget_range1.segment_range_tab(24).data_type := l_data_type;
1703
1704 p_budget_range1.segment_range_tab(25).segment_low := lc_merge_range1.SEGMENT25_LOW;
1705 p_budget_range1.segment_range_tab(25).segment_high := lc_merge_range1.SEGMENT25_HIGH;
1706 p_budget_range1.segment_range_tab(25).data_type := l_data_type;
1707
1708 p_budget_range1.segment_range_tab(26).segment_low := lc_merge_range1.SEGMENT26_LOW;
1709 p_budget_range1.segment_range_tab(26).segment_high := lc_merge_range1.SEGMENT26_HIGH;
1710 p_budget_range1.segment_range_tab(26).data_type := l_data_type;
1711
1712 p_budget_range1.segment_range_tab(27).segment_low := lc_merge_range1.SEGMENT27_LOW;
1713 p_budget_range1.segment_range_tab(27).segment_high := lc_merge_range1.SEGMENT27_HIGH;
1714 p_budget_range1.segment_range_tab(27).data_type := l_data_type;
1715
1716 p_budget_range1.segment_range_tab(28).segment_low := lc_merge_range1.SEGMENT28_LOW;
1717 p_budget_range1.segment_range_tab(28).segment_high := lc_merge_range1.SEGMENT28_HIGH;
1718 p_budget_range1.segment_range_tab(28).data_type := l_data_type;
1719
1720 p_budget_range1.segment_range_tab(29).segment_low := lc_merge_range1.SEGMENT29_LOW;
1721 p_budget_range1.segment_range_tab(29).segment_high := lc_merge_range1.SEGMENT29_HIGH;
1722 p_budget_range1.segment_range_tab(29).data_type := l_data_type;
1723
1724 p_budget_range1.segment_range_tab(30).segment_low := lc_merge_range1.SEGMENT30_LOW;
1725 p_budget_range1.segment_range_tab(30).segment_high := lc_merge_range1.SEGMENT30_HIGH;
1726 p_budget_range1.segment_range_tab(30).data_type := l_data_type;
1727
1728
1729 p_budget_range2.range_id := lc_merge_range2.range_id;
1730 p_budget_range2.segment_range_tab := segment_range_type();
1731 p_budget_range2.segment_range_tab.extend(30);
1732
1733 p_budget_range2.segment_range_tab(1).segment_low := lc_merge_range2.SEGMENT1_LOW;
1734 p_budget_range2.segment_range_tab(1).segment_high := lc_merge_range2.SEGMENT1_HIGH;
1735 p_budget_range2.segment_range_tab(1).data_type := l_data_type;
1736
1737 p_budget_range2.segment_range_tab(2).segment_low := lc_merge_range2.SEGMENT2_LOW;
1738 p_budget_range2.segment_range_tab(2).segment_high := lc_merge_range2.SEGMENT2_HIGH;
1739 p_budget_range2.segment_range_tab(2).data_type := l_data_type;
1740
1741 p_budget_range2.segment_range_tab(3).segment_low := lc_merge_range2.SEGMENT3_LOW;
1742 p_budget_range2.segment_range_tab(3).segment_high := lc_merge_range2.SEGMENT3_HIGH;
1743 p_budget_range2.segment_range_tab(3).data_type := l_data_type;
1744
1745 p_budget_range2.segment_range_tab(4).segment_low := lc_merge_range2.SEGMENT4_LOW;
1746 p_budget_range2.segment_range_tab(4).segment_high := lc_merge_range2.SEGMENT4_HIGH;
1747 p_budget_range2.segment_range_tab(4).data_type := l_data_type;
1748
1749 p_budget_range2.segment_range_tab(5).segment_low := lc_merge_range2.SEGMENT5_LOW;
1750 p_budget_range2.segment_range_tab(5).segment_high := lc_merge_range2.SEGMENT5_HIGH;
1751 p_budget_range2.segment_range_tab(5).data_type := l_data_type;
1752
1753 p_budget_range2.segment_range_tab(6).segment_low := lc_merge_range2.SEGMENT6_LOW;
1754 p_budget_range2.segment_range_tab(6).segment_high := lc_merge_range2.SEGMENT6_HIGH;
1755 p_budget_range2.segment_range_tab(6).data_type := l_data_type;
1756
1757 p_budget_range2.segment_range_tab(7).segment_low := lc_merge_range2.SEGMENT7_LOW;
1758 p_budget_range2.segment_range_tab(7).segment_high := lc_merge_range2.SEGMENT7_HIGH;
1759 p_budget_range2.segment_range_tab(7).data_type := l_data_type;
1760
1761 p_budget_range2.segment_range_tab(8).segment_low := lc_merge_range2.SEGMENT8_LOW;
1762 p_budget_range2.segment_range_tab(8).segment_high := lc_merge_range2.SEGMENT8_HIGH;
1763 p_budget_range2.segment_range_tab(8).data_type := l_data_type;
1764
1765 p_budget_range2.segment_range_tab(9).segment_low := lc_merge_range2.SEGMENT9_LOW;
1766 p_budget_range2.segment_range_tab(9).segment_high := lc_merge_range2.SEGMENT9_HIGH;
1767 p_budget_range2.segment_range_tab(9).data_type := l_data_type;
1768
1769 p_budget_range2.segment_range_tab(10).segment_low := lc_merge_range2.SEGMENT10_LOW;
1770 p_budget_range2.segment_range_tab(10).segment_high := lc_merge_range2.SEGMENT10_HIGH;
1771 p_budget_range2.segment_range_tab(10).data_type := l_data_type;
1772
1773 p_budget_range2.segment_range_tab(11).segment_low := lc_merge_range2.SEGMENT11_LOW;
1774 p_budget_range2.segment_range_tab(11).segment_high := lc_merge_range2.SEGMENT11_HIGH;
1775 p_budget_range2.segment_range_tab(11).data_type := l_data_type;
1776
1777 p_budget_range2.segment_range_tab(12).segment_low := lc_merge_range2.SEGMENT12_LOW;
1778 p_budget_range2.segment_range_tab(12).segment_high := lc_merge_range2.SEGMENT12_HIGH;
1779 p_budget_range2.segment_range_tab(12).data_type := l_data_type;
1780
1781 p_budget_range2.segment_range_tab(13).segment_low := lc_merge_range2.SEGMENT13_LOW;
1782 p_budget_range2.segment_range_tab(13).segment_high := lc_merge_range2.SEGMENT13_HIGH;
1783 p_budget_range2.segment_range_tab(13).data_type := l_data_type;
1784
1785 p_budget_range2.segment_range_tab(14).segment_low := lc_merge_range2.SEGMENT14_LOW;
1786 p_budget_range2.segment_range_tab(14).segment_high := lc_merge_range2.SEGMENT14_HIGH;
1787 p_budget_range2.segment_range_tab(14).data_type := l_data_type;
1788
1789 p_budget_range2.segment_range_tab(15).segment_low := lc_merge_range2.SEGMENT15_LOW;
1790 p_budget_range2.segment_range_tab(15).segment_high := lc_merge_range2.SEGMENT15_HIGH;
1791 p_budget_range2.segment_range_tab(15).data_type := l_data_type;
1792
1793 p_budget_range2.segment_range_tab(16).segment_low := lc_merge_range2.SEGMENT16_LOW;
1794 p_budget_range2.segment_range_tab(16).segment_high := lc_merge_range2.SEGMENT16_HIGH;
1795 p_budget_range2.segment_range_tab(16).data_type := l_data_type;
1796
1797 p_budget_range2.segment_range_tab(17).segment_low := lc_merge_range2.SEGMENT17_LOW;
1798 p_budget_range2.segment_range_tab(17).segment_high := lc_merge_range2.SEGMENT17_HIGH;
1799 p_budget_range2.segment_range_tab(17).data_type := l_data_type;
1800
1801 p_budget_range2.segment_range_tab(18).segment_low := lc_merge_range2.SEGMENT18_LOW;
1802 p_budget_range2.segment_range_tab(18).segment_high := lc_merge_range2.SEGMENT18_HIGH;
1803 p_budget_range2.segment_range_tab(18).data_type := l_data_type;
1804
1805 p_budget_range2.segment_range_tab(19).segment_low := lc_merge_range2.SEGMENT19_LOW;
1806 p_budget_range2.segment_range_tab(19).segment_high := lc_merge_range2.SEGMENT19_HIGH;
1807 p_budget_range2.segment_range_tab(19).data_type := l_data_type;
1808
1809 p_budget_range2.segment_range_tab(20).segment_low := lc_merge_range2.SEGMENT20_LOW;
1810 p_budget_range2.segment_range_tab(20).segment_high := lc_merge_range2.SEGMENT20_HIGH;
1811 p_budget_range2.segment_range_tab(20).data_type := l_data_type;
1812
1813 p_budget_range2.segment_range_tab(21).segment_low := lc_merge_range2.SEGMENT21_LOW;
1814 p_budget_range2.segment_range_tab(21).segment_high := lc_merge_range2.SEGMENT21_HIGH;
1815 p_budget_range2.segment_range_tab(21).data_type := l_data_type;
1816
1817 p_budget_range2.segment_range_tab(22).segment_low := lc_merge_range2.SEGMENT22_LOW;
1818 p_budget_range2.segment_range_tab(22).segment_high := lc_merge_range2.SEGMENT22_HIGH;
1819 p_budget_range2.segment_range_tab(22).data_type := l_data_type;
1820
1821 p_budget_range2.segment_range_tab(23).segment_low := lc_merge_range2.SEGMENT23_LOW;
1822 p_budget_range2.segment_range_tab(23).segment_high := lc_merge_range2.SEGMENT23_HIGH;
1823 p_budget_range2.segment_range_tab(23).data_type := l_data_type;
1824
1825 p_budget_range2.segment_range_tab(24).segment_low := lc_merge_range2.SEGMENT24_LOW;
1826 p_budget_range2.segment_range_tab(24).segment_high := lc_merge_range2.SEGMENT24_HIGH;
1827 p_budget_range2.segment_range_tab(24).data_type := l_data_type;
1828
1829 p_budget_range2.segment_range_tab(25).segment_low := lc_merge_range2.SEGMENT25_LOW;
1830 p_budget_range2.segment_range_tab(25).segment_high := lc_merge_range2.SEGMENT25_HIGH;
1831 p_budget_range2.segment_range_tab(25).data_type := l_data_type;
1832
1833 p_budget_range2.segment_range_tab(26).segment_low := lc_merge_range2.SEGMENT26_LOW;
1834 p_budget_range2.segment_range_tab(26).segment_high := lc_merge_range2.SEGMENT26_HIGH;
1835 p_budget_range2.segment_range_tab(26).data_type := l_data_type;
1836
1837 p_budget_range2.segment_range_tab(27).segment_low := lc_merge_range2.SEGMENT27_LOW;
1838 p_budget_range2.segment_range_tab(27).segment_high := lc_merge_range2.SEGMENT27_HIGH;
1839 p_budget_range2.segment_range_tab(27).data_type := l_data_type;
1840
1841 p_budget_range2.segment_range_tab(28).segment_low := lc_merge_range2.SEGMENT28_LOW;
1842 p_budget_range2.segment_range_tab(28).segment_high := lc_merge_range2.SEGMENT28_HIGH;
1843 p_budget_range2.segment_range_tab(28).data_type := l_data_type;
1844
1845 p_budget_range2.segment_range_tab(29).segment_low := lc_merge_range2.SEGMENT29_LOW;
1846 p_budget_range2.segment_range_tab(29).segment_high := lc_merge_range2.SEGMENT29_HIGH;
1847 p_budget_range2.segment_range_tab(29).data_type := l_data_type;
1848
1849 p_budget_range2.segment_range_tab(30).segment_low := lc_merge_range2.SEGMENT30_LOW;
1850 p_budget_range2.segment_range_tab(30).segment_high := lc_merge_range2.SEGMENT30_HIGH;
1851 p_budget_range2.segment_range_tab(30).data_type := l_data_type;
1852
1853
1854 l_index_number := 0;
1855 p_segment_tab := segment_type();
1856
1857 FOR i IN 1..30 LOOP
1858 IF p_budget_range1.segment_range_tab(i).segment_low IS NOT NULL AND
1859 p_budget_range1.segment_range_tab(i).segment_high IS NOT NULL AND
1860 p_budget_range2.segment_range_tab(i).segment_low IS NOT NULL AND
1861 p_budget_range2.segment_range_tab(i).segment_high IS NOT NULL AND
1862 (p_budget_range1.segment_range_tab(i).segment_low <= p_budget_range2.segment_range_tab(i).segment_high
1863 AND
1864 p_budget_range1.segment_range_tab(i).segment_high >= p_budget_range2.segment_range_tab(i).segment_low
1865 )
1866 THEN
1867
1868 IF p_budget_range1.segment_range_tab(i).segment_low = p_budget_range2.segment_range_tab(i).segment_low
1869 AND p_budget_range2.segment_range_tab(i).segment_high = p_budget_range2.segment_range_tab(i).segment_high THEN
1870 NULL;
1871
1872 ELSE
1873 l_index_number := l_index_number + 1;
1874
1875
1876
1877 p_segment1.segment_number := i;
1878 p_segment1.data_type := l_data_type;
1879 p_segment1.segment_low := p_budget_range1.segment_range_tab(i).SEGMENT_LOW;
1880 p_segment1.segment_high := p_budget_range1.segment_range_tab(i).SEGMENT_HIGH;
1881 p_segment1.budget_tab := BUDGET_TYPE();
1882
1883 FOR m IN 1..lc_merge_range1_bc.COUNT LOOP
1884 p_segment1.budget_tab.extend(1);
1885 p_segment1.budget_tab(m) := lc_merge_range1_bc(m).funding_budget_version_id;
1886 END LOOP;
1887
1888
1889 p_segment2.segment_number := i;
1890 p_segment2.data_type := l_data_type;
1891 p_segment2.segment_low := p_budget_range2.segment_range_tab(i).SEGMENT_LOW;
1892 p_segment2.segment_high := p_budget_range2.segment_range_tab(i).SEGMENT_HIGH;
1893 p_segment2.budget_tab := BUDGET_TYPE();
1894
1895 FOR m IN 1..lc_merge_range2_bc.COUNT LOOP
1896 p_segment2.budget_tab.extend(1);
1897 p_segment2.budget_tab(m) := lc_merge_range2_bc(m).funding_budget_version_id;
1898 END LOOP;
1899
1900 SPLIT_RANGES(p_segment1,
1901 p_segment2,
1902 p_segment_tab,
1903 l_index_number,
1904 l_errbuf,
1905 l_retcode);
1906
1907 IF l_retcode IS NOT NULL AND l_retcode = -1 THEN
1908 retcode := l_retcode;
1909 errbuf := l_errbuf;
1910 RETURN;
1911 END IF;
1912
1913
1914
1915 END IF;
1916
1917 END IF;
1918
1919 END LOOP;
1920
1921 IF g_debug_enabled = 1 THEN
1922 PRINT_SPLIT_INFO(p_segment_tab);
1923 END IF;
1924
1925 l_final_budget_ranges := BUDGET_RANGE_TYPE();
1926
1927
1928 MERGE_SEGMENTS(p_budget_range1, p_segment_tab, l_final_budget_ranges, l_index_number, l_errbuf, l_retcode);
1929
1930 IF l_retcode IS NOT NULL AND l_retcode = -1 THEN
1931 retcode := l_retcode;
1932 errbuf := l_errbuf;
1933 RETURN;
1934 END IF;
1935
1936 IF g_debug_enabled = 1 THEN
1937 PRINT_MERGE_INFO(l_final_budget_ranges);
1938 END IF;
1939
1940
1941
1942
1943
1944 FOR i IN 1..l_final_budget_ranges.COUNT LOOP
1945 IF COMPARE_BUDGETS(l_final_budget_ranges(i).budget_tab,lc_merge_range2_bc) THEN
1946
1947 l_range_id_seq := l_range_id_seq + 1;
1948
1949 DECLARE
1950 l_seq_number1 NUMBER;
1951 l_seq_number2 NUMBER;
1952 BEGIN
1953 SELECT max(sequence_number)+1
1954 INTO l_seq_number1
1955 FROM IGI_UPG_GL_BUDGET_ASSIGNMENT
1956 WHERE
1957 budget_entity_id = lc_merge_range2.budget_entity_id;
1958
1959 SELECT max(sequence_number)+1
1960 INTO l_seq_number2
1961 FROM GL_BUDGET_ASSIGNMENT_RANGES
1962 WHERE
1963 budget_entity_id = lc_merge_range2.budget_entity_id;
1964
1965 IF l_seq_number1 IS NULL and l_seq_number2 IS NULL THEN
1966 l_seq_number := 10;
1967 ELSE
1968 IF nvl(l_seq_number1,-1) < nvl(l_seq_number2,-1) THEN
1969 l_seq_number := l_seq_number2;
1970
1971 ELSE
1972 l_seq_number := l_seq_number1;
1973 END IF;
1974 END IF;
1975
1976 EXCEPTION
1977 WHEN OTHERS THEN
1978 l_seq_number := 10;
1979
1980 END;
1981
1982 IF l_seq_number IS NULL OR l_seq_number = 0 THEN
1983 l_seq_number := 10;
1984 END IF;
1985
1986 BEGIN
1987
1988 INSERT INTO IGI_UPG_GL_BUDGET_ASSIGNMENT
1989 (
1990 BUDGET_ENTITY_ID,
1991 LEDGER_ID,
1992 CURRENCY_CODE,
1993 ENTRY_CODE,
1994 RANGE_ID,
1995 STATUS,
1996 LAST_UPDATE_DATE,
1997 AUTOMATIC_ENCUMBRANCE_FLAG,
1998 CREATED_BY,
1999 CREATION_DATE,
2000 FUNDS_CHECK_LEVEL_CODE,
2001 LAST_UPDATED_BY,
2002 LAST_UPDATE_LOGIN,
2003 SEQUENCE_NUMBER,
2004 SEGMENT1_LOW,
2005 SEGMENT1_HIGH,
2006 SEGMENT2_LOW,
2007 SEGMENT2_HIGH,
2008 SEGMENT3_LOW,
2009 SEGMENT3_HIGH,
2010 SEGMENT4_LOW,
2011 SEGMENT4_HIGH,
2012 SEGMENT5_LOW,
2013 SEGMENT5_HIGH,
2014 SEGMENT6_LOW,
2015 SEGMENT6_HIGH,
2016 SEGMENT7_LOW,
2017 SEGMENT7_HIGH,
2018 SEGMENT8_LOW,
2019 SEGMENT8_HIGH,
2020 SEGMENT9_LOW,
2021 SEGMENT9_HIGH,
2022 SEGMENT10_LOW,
2023 SEGMENT10_HIGH,
2024 SEGMENT11_LOW,
2025 SEGMENT11_HIGH,
2026 SEGMENT12_LOW,
2027 SEGMENT12_HIGH,
2028 SEGMENT13_LOW,
2029 SEGMENT13_HIGH,
2030 SEGMENT14_LOW,
2031 SEGMENT14_HIGH,
2032 SEGMENT15_LOW,
2033 SEGMENT15_HIGH,
2034 SEGMENT16_LOW,
2035 SEGMENT16_HIGH,
2036 SEGMENT17_LOW,
2037 SEGMENT17_HIGH,
2038 SEGMENT18_LOW,
2039 SEGMENT18_HIGH,
2040 SEGMENT19_LOW,
2041 SEGMENT19_HIGH,
2042 SEGMENT20_LOW,
2043 SEGMENT20_HIGH,
2044 SEGMENT21_LOW,
2045 SEGMENT21_HIGH,
2046 SEGMENT22_LOW,
2047 SEGMENT22_HIGH,
2048 SEGMENT23_LOW,
2049 SEGMENT23_HIGH,
2050 SEGMENT24_LOW,
2051 SEGMENT24_HIGH,
2052 SEGMENT25_LOW,
2053 SEGMENT25_HIGH,
2054 SEGMENT26_LOW,
2055 SEGMENT26_HIGH,
2056 SEGMENT27_LOW,
2057 SEGMENT27_HIGH,
2058 SEGMENT28_LOW,
2059 SEGMENT28_HIGH,
2060 SEGMENT29_LOW,
2061 SEGMENT29_HIGH,
2062 SEGMENT30_LOW,
2063 SEGMENT30_HIGH,
2064 AMOUNT_TYPE,
2065 BOUNDARY_CODE,
2066 CONTEXT,
2067 FUNDING_BUDGET_VERSION_ID,
2068 PROGRAM_APPLICATION_ID,
2069 PROGRAM_ID,
2070 PROGRAM_UPDATE_DATE,
2071 REQUEST_ID,
2072 ATTRIBUTE1,
2073 ATTRIBUTE2,
2074 ATTRIBUTE3,
2075 ATTRIBUTE4,
2076 ATTRIBUTE5,
2077 ATTRIBUTE6,
2078 ATTRIBUTE7,
2079 ATTRIBUTE8,
2080 ATTRIBUTE9,
2081 ATTRIBUTE10,
2082 ATTRIBUTE11,
2083 ATTRIBUTE12,
2084 ATTRIBUTE13,
2085 ATTRIBUTE14,
2086 ATTRIBUTE15
2087 )
2088
2089 VALUES (
2090 lc_merge_range2.budget_entity_id,
2091 lc_merge_range2.ledger_id,
2092 lc_merge_range2.currency_code,
2093 lc_merge_range2.entry_code,
2094 l_range_id_seq,
2095 lc_merge_range2.status,
2096 sysdate,
2097 lc_merge_range2.automatic_encumbrance_flag,
2098 lc_merge_range2.created_by,
2099 lc_merge_range2.creation_date,
2100 lc_merge_range2.funds_check_level_code,
2101 lc_merge_range2.last_updated_by,
2102 lc_merge_range2.last_update_login,
2103 l_seq_number,
2104 l_final_budget_ranges(i).segment_range_tab(1).SEGMENT_LOW,
2105 l_final_budget_ranges(i).segment_range_tab(1).SEGMENT_HIGH,
2106 l_final_budget_ranges(i).segment_range_tab(2).SEGMENT_LOW,
2107 l_final_budget_ranges(i).segment_range_tab(2).SEGMENT_HIGH,
2108 l_final_budget_ranges(i).segment_range_tab(3).SEGMENT_LOW,
2109 l_final_budget_ranges(i).segment_range_tab(3).SEGMENT_HIGH,
2110 l_final_budget_ranges(i).segment_range_tab(4).SEGMENT_LOW,
2111 l_final_budget_ranges(i).segment_range_tab(4).SEGMENT_HIGH,
2112 l_final_budget_ranges(i).segment_range_tab(5).SEGMENT_LOW,
2113 l_final_budget_ranges(i).segment_range_tab(5).SEGMENT_HIGH,
2114 l_final_budget_ranges(i).segment_range_tab(6).SEGMENT_LOW,
2115 l_final_budget_ranges(i).segment_range_tab(6).SEGMENT_HIGH,
2116 l_final_budget_ranges(i).segment_range_tab(7).SEGMENT_LOW,
2117 l_final_budget_ranges(i).segment_range_tab(7).SEGMENT_HIGH,
2118 l_final_budget_ranges(i).segment_range_tab(8).SEGMENT_LOW,
2119 l_final_budget_ranges(i).segment_range_tab(8).SEGMENT_HIGH,
2120 l_final_budget_ranges(i).segment_range_tab(9).SEGMENT_LOW,
2121 l_final_budget_ranges(i).segment_range_tab(9).SEGMENT_HIGH,
2122 l_final_budget_ranges(i).segment_range_tab(10).SEGMENT_LOW,
2123 l_final_budget_ranges(i).segment_range_tab(10).SEGMENT_HIGH,
2124 l_final_budget_ranges(i).segment_range_tab(11).SEGMENT_LOW,
2125 l_final_budget_ranges(i).segment_range_tab(11).SEGMENT_HIGH,
2126 l_final_budget_ranges(i).segment_range_tab(12).SEGMENT_LOW,
2127 l_final_budget_ranges(i).segment_range_tab(12).SEGMENT_HIGH,
2128 l_final_budget_ranges(i).segment_range_tab(13).SEGMENT_LOW,
2129 l_final_budget_ranges(i).segment_range_tab(13).SEGMENT_HIGH,
2130 l_final_budget_ranges(i).segment_range_tab(14).SEGMENT_LOW,
2131 l_final_budget_ranges(i).segment_range_tab(14).SEGMENT_HIGH,
2132 l_final_budget_ranges(i).segment_range_tab(15).SEGMENT_LOW,
2133 l_final_budget_ranges(i).segment_range_tab(15).SEGMENT_HIGH,
2134 l_final_budget_ranges(i).segment_range_tab(16).SEGMENT_LOW,
2135 l_final_budget_ranges(i).segment_range_tab(16).SEGMENT_HIGH,
2136 l_final_budget_ranges(i).segment_range_tab(17).SEGMENT_LOW,
2137 l_final_budget_ranges(i).segment_range_tab(17).SEGMENT_HIGH,
2138 l_final_budget_ranges(i).segment_range_tab(18).SEGMENT_LOW,
2139 l_final_budget_ranges(i).segment_range_tab(18).SEGMENT_HIGH,
2140 l_final_budget_ranges(i).segment_range_tab(19).SEGMENT_LOW,
2141 l_final_budget_ranges(i).segment_range_tab(19).SEGMENT_HIGH,
2142 l_final_budget_ranges(i).segment_range_tab(20).SEGMENT_LOW,
2143 l_final_budget_ranges(i).segment_range_tab(20).SEGMENT_HIGH,
2144 l_final_budget_ranges(i).segment_range_tab(21).SEGMENT_LOW,
2145 l_final_budget_ranges(i).segment_range_tab(21).SEGMENT_HIGH,
2146 l_final_budget_ranges(i).segment_range_tab(22).SEGMENT_LOW,
2147 l_final_budget_ranges(i).segment_range_tab(22).SEGMENT_HIGH,
2148 l_final_budget_ranges(i).segment_range_tab(23).SEGMENT_LOW,
2149 l_final_budget_ranges(i).segment_range_tab(23).SEGMENT_HIGH,
2150 l_final_budget_ranges(i).segment_range_tab(24).SEGMENT_LOW,
2151 l_final_budget_ranges(i).segment_range_tab(24).SEGMENT_HIGH,
2152 l_final_budget_ranges(i).segment_range_tab(25).SEGMENT_LOW,
2153 l_final_budget_ranges(i).segment_range_tab(25).SEGMENT_HIGH,
2154 l_final_budget_ranges(i).segment_range_tab(26).SEGMENT_LOW,
2155 l_final_budget_ranges(i).segment_range_tab(26).SEGMENT_HIGH,
2156 l_final_budget_ranges(i).segment_range_tab(27).SEGMENT_LOW,
2157 l_final_budget_ranges(i).segment_range_tab(27).SEGMENT_HIGH,
2158 l_final_budget_ranges(i).segment_range_tab(28).SEGMENT_LOW,
2159 l_final_budget_ranges(i).segment_range_tab(28).SEGMENT_HIGH,
2160 l_final_budget_ranges(i).segment_range_tab(29).SEGMENT_LOW,
2161 l_final_budget_ranges(i).segment_range_tab(29).SEGMENT_HIGH,
2162 l_final_budget_ranges(i).segment_range_tab(30).SEGMENT_LOW,
2163 l_final_budget_ranges(i).segment_range_tab(30).SEGMENT_HIGH,
2164 lc_merge_range2.amount_type,
2165 lc_merge_range2.boundary_code,
2166 lc_merge_range2.context,
2167 lc_merge_range2.funding_budget_version_id,
2168 lc_merge_range2.program_application_id,
2169 lc_merge_range2.program_id,
2170 lc_merge_range2.program_update_date,
2171 lc_merge_range2.request_id,
2172 lc_merge_range2.attribute1,
2173 lc_merge_range2.attribute2,
2174 lc_merge_range2.attribute3,
2175 lc_merge_range2.attribute4,
2176 lc_merge_range2.attribute5,
2177 lc_merge_range2.attribute6,
2178 lc_merge_range2.attribute7,
2179 lc_merge_range2.attribute8,
2180 lc_merge_range2.attribute9,
2181 lc_merge_range2.attribute10,
2182 lc_merge_range2.attribute11,
2183 lc_merge_range2.attribute12,
2184 lc_merge_range2.attribute13,
2185 lc_merge_range2.attribute14,
2186 lc_merge_range2.attribute15
2187 );
2188 EXCEPTION
2189 WHEN OTHERS THEN
2190 fnd_file.put_line(fnd_file.output, 'Error inserting records into IGI_UPG_GL_BUDGET_ASSIGNMENT Location 1');
2191 fnd_file.put_line(fnd_file.log, 'Module: LOOP_AND_PROCESS =>'
2192 ||'Error inserting records into IGI_UPG_GL_BUDGET_ASSIGNMENT Location 1 '||SQLERRM);
2193 errbuf := 'Module: LOOP_AND_PROCESS =>'||'Error inserting records into IGI_UPG_GL_BUDGET_ASSIGNMENT Location 1 '||SQLERRM;
2194 retcode := -1;
2195 RETURN;
2196 END;
2197 FOR j IN 1..l_final_budget_ranges(i).budget_tab.COUNT LOOP
2198 FOR k IN 1..lc_merge_range2_bc.COUNT LOOP
2199 IF l_final_budget_ranges(i).budget_tab(j) = lc_merge_range2_bc(k).funding_budget_version_id THEN
2200 BEGIN
2201 INSERT INTO IGI_UPG_GL_BUDORG_BC_OPTIONS
2202 (
2203 RANGE_ID,
2204 FUNDING_BUDGET_VERSION_ID,
2205 FUNDS_CHECK_LEVEL_CODE,
2206 AMOUNT_TYPE,
2207 BOUNDARY_CODE,
2208 CREATED_BY,
2209 CREATION_DATE,
2210 LAST_UPDATED_BY,
2211 LAST_UPDATE_LOGIN,
2212 LAST_UPDATE_DATE
2213 )
2214 VALUES
2215 (
2216 l_range_id_seq,
2217 lc_merge_range2_bc(k).funding_budget_version_id,
2218 lc_merge_range2_bc(k).funds_check_level_code,
2219 lc_merge_range2_bc(k).amount_type,
2220 lc_merge_range2_bc(k).boundary_code,
2221 lc_merge_range2_bc(k).created_by,
2222 lc_merge_range2_bc(k).creation_date,
2223 lc_merge_range2_bc(k).last_updated_by,
2224 lc_merge_range2_bc(k).last_update_login,
2225 sysdate
2226 );
2227 EXCEPTION
2228 WHEN OTHERS THEN
2229 fnd_file.put_line(fnd_file.output, 'Error inserting records into IGI_UPG_GL_BUDORG_BC_OPTIONS Location 1');
2230 fnd_file.put_line(fnd_file.log, 'Module: LOOP_AND_PROCESS =>'
2231 ||'Error inserting records into IGI_UPG_GL_BUDORG_BC_OPTIONS Location 1 '||SQLERRM);
2232 errbuf := 'Module: LOOP_AND_PROCESS =>'||'Error inserting records into
2233 IGI_UPG_GL_BUDORG_BC_OPTIONS Location 1 '||SQLERRM;
2234 retcode := -1;
2235 RETURN;
2236 END;
2237 EXIT;
2238 END IF;
2239 END LOOP;
2240 END LOOP;
2241
2242 ELSE
2243
2244
2245 l_range_id_seq := l_range_id_seq + 1;
2246
2247
2248 DECLARE
2249 l_seq_number1 NUMBER;
2250 l_seq_number2 NUMBER;
2251 BEGIN
2252 SELECT max(sequence_number)+1
2253 INTO l_seq_number1
2254 FROM IGI_UPG_GL_BUDGET_ASSIGNMENT
2255 WHERE
2256 budget_entity_id = lc_merge_range1.budget_entity_id;
2257
2258 SELECT max(sequence_number)+1
2259 INTO l_seq_number2
2260 FROM GL_BUDGET_ASSIGNMENT_RANGES
2261 WHERE
2262 budget_entity_id = lc_merge_range1.budget_entity_id;
2263
2264 IF l_seq_number1 IS NULL and l_seq_number2 IS NULL THEN
2265 l_seq_number := 10;
2266 ELSE
2267 IF nvl(l_seq_number1,-1) < nvl(l_seq_number2,-1) THEN
2268 l_seq_number := l_seq_number2;
2269
2270 ELSE
2271 l_seq_number := l_seq_number1;
2272 END IF;
2273 END IF;
2274
2275 EXCEPTION
2276 WHEN OTHERS THEN
2277 l_seq_number := 10;
2278
2279 END;
2280
2281 IF l_seq_number IS NULL OR l_seq_number = 0 THEN
2282 l_seq_number := 10;
2283 END IF;
2284
2285 BEGIN
2286
2287 INSERT INTO IGI_UPG_GL_BUDGET_ASSIGNMENT
2288 (
2289 BUDGET_ENTITY_ID,
2290 LEDGER_ID,
2291 CURRENCY_CODE,
2292 ENTRY_CODE,
2293 RANGE_ID,
2294 STATUS,
2295 LAST_UPDATE_DATE,
2296 AUTOMATIC_ENCUMBRANCE_FLAG,
2297 CREATED_BY,
2298 CREATION_DATE,
2299 FUNDS_CHECK_LEVEL_CODE,
2300 LAST_UPDATED_BY,
2301 LAST_UPDATE_LOGIN,
2302 SEQUENCE_NUMBER,
2303 SEGMENT1_LOW,
2304 SEGMENT1_HIGH,
2305 SEGMENT2_LOW,
2306 SEGMENT2_HIGH,
2307 SEGMENT3_LOW,
2308 SEGMENT3_HIGH,
2309 SEGMENT4_LOW,
2310 SEGMENT4_HIGH,
2311 SEGMENT5_LOW,
2312 SEGMENT5_HIGH,
2313 SEGMENT6_LOW,
2314 SEGMENT6_HIGH,
2315 SEGMENT7_LOW,
2316 SEGMENT7_HIGH,
2317 SEGMENT8_LOW,
2318 SEGMENT8_HIGH,
2319 SEGMENT9_LOW,
2320 SEGMENT9_HIGH,
2321 SEGMENT10_LOW,
2322 SEGMENT10_HIGH,
2323 SEGMENT11_LOW,
2324 SEGMENT11_HIGH,
2325 SEGMENT12_LOW,
2326 SEGMENT12_HIGH,
2327 SEGMENT13_LOW,
2328 SEGMENT13_HIGH,
2329 SEGMENT14_LOW,
2330 SEGMENT14_HIGH,
2331 SEGMENT15_LOW,
2332 SEGMENT15_HIGH,
2333 SEGMENT16_LOW,
2334 SEGMENT16_HIGH,
2335 SEGMENT17_LOW,
2336 SEGMENT17_HIGH,
2337 SEGMENT18_LOW,
2338 SEGMENT18_HIGH,
2339 SEGMENT19_LOW,
2340 SEGMENT19_HIGH,
2341 SEGMENT20_LOW,
2342 SEGMENT20_HIGH,
2343 SEGMENT21_LOW,
2344 SEGMENT21_HIGH,
2345 SEGMENT22_LOW,
2346 SEGMENT22_HIGH,
2347 SEGMENT23_LOW,
2348 SEGMENT23_HIGH,
2349 SEGMENT24_LOW,
2350 SEGMENT24_HIGH,
2351 SEGMENT25_LOW,
2352 SEGMENT25_HIGH,
2353 SEGMENT26_LOW,
2354 SEGMENT26_HIGH,
2355 SEGMENT27_LOW,
2356 SEGMENT27_HIGH,
2357 SEGMENT28_LOW,
2358 SEGMENT28_HIGH,
2359 SEGMENT29_LOW,
2360 SEGMENT29_HIGH,
2361 SEGMENT30_LOW,
2362 SEGMENT30_HIGH,
2363 AMOUNT_TYPE,
2364 BOUNDARY_CODE,
2365 CONTEXT,
2366 FUNDING_BUDGET_VERSION_ID,
2367 PROGRAM_APPLICATION_ID,
2368 PROGRAM_ID,
2369 PROGRAM_UPDATE_DATE,
2370 REQUEST_ID,
2371 ATTRIBUTE1,
2372 ATTRIBUTE2,
2373 ATTRIBUTE3,
2374 ATTRIBUTE4,
2375 ATTRIBUTE5,
2376 ATTRIBUTE6,
2377 ATTRIBUTE7,
2378 ATTRIBUTE8,
2379 ATTRIBUTE9,
2380 ATTRIBUTE10,
2381 ATTRIBUTE11,
2382 ATTRIBUTE12,
2383 ATTRIBUTE13,
2384 ATTRIBUTE14,
2385 ATTRIBUTE15
2386 )
2387
2388 VALUES (
2389 lc_merge_range1.budget_entity_id,
2390 lc_merge_range1.ledger_id,
2391 lc_merge_range1.currency_code,
2392 lc_merge_range1.entry_code,
2393 l_range_id_seq,
2394 lc_merge_range1.status,
2395 sysdate,
2396 lc_merge_range1.automatic_encumbrance_flag,
2397 lc_merge_range1.created_by,
2398 lc_merge_range1.creation_date,
2399 lc_merge_range1.funds_check_level_code,
2400 lc_merge_range1.last_updated_by,
2401 lc_merge_range1.last_update_login,
2402 l_seq_number,
2403 l_final_budget_ranges(i).segment_range_tab(1).SEGMENT_LOW,
2404 l_final_budget_ranges(i).segment_range_tab(1).SEGMENT_HIGH,
2405 l_final_budget_ranges(i).segment_range_tab(2).SEGMENT_LOW,
2406 l_final_budget_ranges(i).segment_range_tab(2).SEGMENT_HIGH,
2407 l_final_budget_ranges(i).segment_range_tab(3).SEGMENT_LOW,
2408 l_final_budget_ranges(i).segment_range_tab(3).SEGMENT_HIGH,
2409 l_final_budget_ranges(i).segment_range_tab(4).SEGMENT_LOW,
2410 l_final_budget_ranges(i).segment_range_tab(4).SEGMENT_HIGH,
2411 l_final_budget_ranges(i).segment_range_tab(5).SEGMENT_LOW,
2412 l_final_budget_ranges(i).segment_range_tab(5).SEGMENT_HIGH,
2413 l_final_budget_ranges(i).segment_range_tab(6).SEGMENT_LOW,
2414 l_final_budget_ranges(i).segment_range_tab(6).SEGMENT_HIGH,
2415 l_final_budget_ranges(i).segment_range_tab(7).SEGMENT_LOW,
2416 l_final_budget_ranges(i).segment_range_tab(7).SEGMENT_HIGH,
2417 l_final_budget_ranges(i).segment_range_tab(8).SEGMENT_LOW,
2418 l_final_budget_ranges(i).segment_range_tab(8).SEGMENT_HIGH,
2419 l_final_budget_ranges(i).segment_range_tab(9).SEGMENT_LOW,
2420 l_final_budget_ranges(i).segment_range_tab(9).SEGMENT_HIGH,
2421 l_final_budget_ranges(i).segment_range_tab(10).SEGMENT_LOW,
2422 l_final_budget_ranges(i).segment_range_tab(10).SEGMENT_HIGH,
2423 l_final_budget_ranges(i).segment_range_tab(11).SEGMENT_LOW,
2424 l_final_budget_ranges(i).segment_range_tab(11).SEGMENT_HIGH,
2425 l_final_budget_ranges(i).segment_range_tab(12).SEGMENT_LOW,
2426 l_final_budget_ranges(i).segment_range_tab(12).SEGMENT_HIGH,
2427 l_final_budget_ranges(i).segment_range_tab(13).SEGMENT_LOW,
2428 l_final_budget_ranges(i).segment_range_tab(13).SEGMENT_HIGH,
2429 l_final_budget_ranges(i).segment_range_tab(14).SEGMENT_LOW,
2430 l_final_budget_ranges(i).segment_range_tab(14).SEGMENT_HIGH,
2431 l_final_budget_ranges(i).segment_range_tab(15).SEGMENT_LOW,
2432 l_final_budget_ranges(i).segment_range_tab(15).SEGMENT_HIGH,
2433 l_final_budget_ranges(i).segment_range_tab(16).SEGMENT_LOW,
2434 l_final_budget_ranges(i).segment_range_tab(16).SEGMENT_HIGH,
2435 l_final_budget_ranges(i).segment_range_tab(17).SEGMENT_LOW,
2436 l_final_budget_ranges(i).segment_range_tab(17).SEGMENT_HIGH,
2437 l_final_budget_ranges(i).segment_range_tab(18).SEGMENT_LOW,
2438 l_final_budget_ranges(i).segment_range_tab(18).SEGMENT_HIGH,
2439 l_final_budget_ranges(i).segment_range_tab(19).SEGMENT_LOW,
2440 l_final_budget_ranges(i).segment_range_tab(19).SEGMENT_HIGH,
2441 l_final_budget_ranges(i).segment_range_tab(20).SEGMENT_LOW,
2442 l_final_budget_ranges(i).segment_range_tab(20).SEGMENT_HIGH,
2443 l_final_budget_ranges(i).segment_range_tab(21).SEGMENT_LOW,
2444 l_final_budget_ranges(i).segment_range_tab(21).SEGMENT_HIGH,
2445 l_final_budget_ranges(i).segment_range_tab(22).SEGMENT_LOW,
2446 l_final_budget_ranges(i).segment_range_tab(22).SEGMENT_HIGH,
2447 l_final_budget_ranges(i).segment_range_tab(23).SEGMENT_LOW,
2448 l_final_budget_ranges(i).segment_range_tab(23).SEGMENT_HIGH,
2449 l_final_budget_ranges(i).segment_range_tab(24).SEGMENT_LOW,
2450 l_final_budget_ranges(i).segment_range_tab(24).SEGMENT_HIGH,
2451 l_final_budget_ranges(i).segment_range_tab(25).SEGMENT_LOW,
2452 l_final_budget_ranges(i).segment_range_tab(25).SEGMENT_HIGH,
2453 l_final_budget_ranges(i).segment_range_tab(26).SEGMENT_LOW,
2454 l_final_budget_ranges(i).segment_range_tab(26).SEGMENT_HIGH,
2455 l_final_budget_ranges(i).segment_range_tab(27).SEGMENT_LOW,
2456 l_final_budget_ranges(i).segment_range_tab(27).SEGMENT_HIGH,
2457 l_final_budget_ranges(i).segment_range_tab(28).SEGMENT_LOW,
2458 l_final_budget_ranges(i).segment_range_tab(28).SEGMENT_HIGH,
2459 l_final_budget_ranges(i).segment_range_tab(29).SEGMENT_LOW,
2460 l_final_budget_ranges(i).segment_range_tab(29).SEGMENT_HIGH,
2461 l_final_budget_ranges(i).segment_range_tab(30).SEGMENT_LOW,
2462 l_final_budget_ranges(i).segment_range_tab(30).SEGMENT_HIGH,
2463 lc_merge_range1.amount_type,
2464 lc_merge_range1.boundary_code,
2465 lc_merge_range1.context,
2466 lc_merge_range1.funding_budget_version_id,
2467 lc_merge_range1.program_application_id,
2468 lc_merge_range1.program_id,
2469 lc_merge_range1.program_update_date,
2470 lc_merge_range1.request_id,
2471 lc_merge_range1.attribute1,
2472 lc_merge_range1.attribute2,
2473 lc_merge_range1.attribute3,
2474 lc_merge_range1.attribute4,
2475 lc_merge_range1.attribute5,
2476 lc_merge_range1.attribute6,
2477 lc_merge_range1.attribute7,
2478 lc_merge_range1.attribute8,
2479 lc_merge_range1.attribute9,
2480 lc_merge_range1.attribute10,
2481 lc_merge_range1.attribute11,
2482 lc_merge_range1.attribute12,
2483 lc_merge_range1.attribute13,
2484 lc_merge_range1.attribute14,
2485 lc_merge_range1.attribute15
2486 );
2487
2488 EXCEPTION
2489 WHEN OTHERS THEN
2490 fnd_file.put_line(fnd_file.output, 'Error inserting records into IGI_UPG_GL_BUDGET_ASSIGNMENT Location 2');
2491 fnd_file.put_line(fnd_file.log, 'Module: LOOP_AND_PROCESS =>'
2492 ||'Error inserting records into IGI_UPG_GL_BUDGET_ASSIGNMENT Location 2 '||SQLERRM);
2493 errbuf := 'Module: LOOP_AND_PROCESS =>'||'Error inserting records into
2494 IGI_UPG_GL_BUDGET_ASSIGNMENT Location 2 '||SQLERRM;
2495 retcode := -1;
2496 RETURN;
2497 END;
2498
2499 FOR j IN 1..l_final_budget_ranges(i).budget_tab.COUNT LOOP
2500 FOR k IN 1..lc_merge_range1_bc.COUNT LOOP
2501 IF l_final_budget_ranges(i).budget_tab(j) = lc_merge_range1_bc(k).funding_budget_version_id
2502 THEN
2503 BEGIN
2504 INSERT INTO IGI_UPG_GL_BUDORG_BC_OPTIONS
2505 (
2506 RANGE_ID,
2507 FUNDING_BUDGET_VERSION_ID,
2508 FUNDS_CHECK_LEVEL_CODE,
2509 AMOUNT_TYPE,
2510 BOUNDARY_CODE,
2511 CREATED_BY,
2512 CREATION_DATE,
2513 LAST_UPDATED_BY,
2514 LAST_UPDATE_LOGIN,
2515 LAST_UPDATE_DATE
2516 )
2517 VALUES
2518 (
2519 l_range_id_seq,
2520 lc_merge_range1_bc(k).funding_budget_version_id,
2521 lc_merge_range1_bc(k).funds_check_level_code,
2522 lc_merge_range1_bc(k).amount_type,
2523 lc_merge_range1_bc(k).boundary_code,
2524 lc_merge_range1_bc(k).created_by,
2525 lc_merge_range1_bc(k).creation_date,
2526 lc_merge_range1_bc(k).last_updated_by,
2527 lc_merge_range1_bc(k).last_update_login,
2528 sysdate
2529 );
2530 EXCEPTION
2531 WHEN OTHERS THEN
2532 fnd_file.put_line(fnd_file.output, 'Error inserting records into
2533 IGI_UPG_GL_BUDORG_BC_OPTIONS Location 2');
2534 fnd_file.put_line(fnd_file.log, 'Module: LOOP_AND_PROCESS =>'||'Error inserting records into
2535 IGI_UPG_GL_BUDORG_BC_OPTIONS Location 2 '||SQLERRM);
2536 errbuf := 'Module: LOOP_AND_PROCESS =>'||'Error inserting records into
2537 IGI_UPG_GL_BUDORG_BC_OPTIONS Location 2 '||SQLERRM;
2538 retcode := -1;
2539 RETURN;
2540 END;
2541 EXIT;
2542 END IF;
2543 IF l_final_budget_ranges(i).budget_tab(j) = lc_merge_range2_bc(k).funding_budget_version_id
2544 THEN
2545 BEGIN
2546 INSERT INTO IGI_UPG_GL_BUDORG_BC_OPTIONS
2547 (
2548 RANGE_ID,
2549 FUNDING_BUDGET_VERSION_ID,
2550 FUNDS_CHECK_LEVEL_CODE,
2551 AMOUNT_TYPE,
2552 BOUNDARY_CODE,
2553 CREATED_BY,
2554 CREATION_DATE,
2555 LAST_UPDATED_BY,
2556 LAST_UPDATE_LOGIN,
2557 LAST_UPDATE_DATE
2558 )
2559 VALUES
2560 (
2561 l_range_id_seq,
2562 lc_merge_range2_bc(k).funding_budget_version_id,
2563 lc_merge_range2_bc(k).funds_check_level_code,
2564 lc_merge_range2_bc(k).amount_type,
2565 lc_merge_range2_bc(k).boundary_code,
2566 lc_merge_range2_bc(k).created_by,
2567 lc_merge_range2_bc(k).creation_date,
2568 lc_merge_range2_bc(k).last_updated_by,
2569 lc_merge_range2_bc(k).last_update_login,
2570 sysdate
2571 );
2572 EXCEPTION
2573 WHEN OTHERS THEN
2574 fnd_file.put_line(fnd_file.output, 'Error inserting records into
2575 IGI_UPG_GL_BUDORG_BC_OPTIONS Location 3');
2576 fnd_file.put_line(fnd_file.log, 'Module: LOOP_AND_PROCESS =>'||'Error inserting records into
2577 IGI_UPG_GL_BUDORG_BC_OPTIONS Location 3 '||SQLERRM);
2578 errbuf := 'Module: LOOP_AND_PROCESS =>'||'Error inserting records into
2579 IGI_UPG_GL_BUDORG_BC_OPTIONS Location 3 '||SQLERRM;
2580 retcode := -1;
2581 RETURN;
2582 END;
2583 EXIT;
2584 END IF;
2585 END LOOP; -- END lc_merge_range1_bc.COUNT FOR LOOP
2586 END LOOP; -- END l_final_budget_ranges(i).budget_tab FOR LOOP
2587
2588
2589 END IF;
2590 END LOOP; -- End l_final_budget_ranges FOR LOOP
2591
2592 BEGIN
2593 DELETE FROM IGI_UPG_GL_BUDGET_ASSIGNMENT WHERE
2594 range_id = lc_merge_range2.range_id OR
2595 range_id = lc_merge_range1.range_id;
2596
2597 DELETE FROM IGI_UPG_GL_BUDORG_BC_OPTIONS WHERE
2598 range_id = lc_merge_range2.range_id OR
2599 range_id = lc_merge_range1.range_id;
2600 EXCEPTION
2601 WHEN OTHERS THEN
2602 fnd_file.put_line(fnd_file.output, 'Error deleting data');
2603 fnd_file.put_line(fnd_file.log, 'Module: LOOP_AND_PROCESS =>'||'Error deleting data'||SQLERRM);
2604 errbuf := 'Module: LOOP_AND_PROCESS =>'||'Error deleting data'||SQLERRM;
2605 retcode := -1;
2606 RETURN;
2607 END;
2608
2609 <<continue>>
2610 NULL;
2611
2612 END LOOP;
2613
2614 END LOOP_AND_PROCESS;
2615
2616
2617 PROCEDURE START_EFC_UPGRADE
2618 (
2619 errbuf OUT NOCOPY VARCHAR2,
2620 retcode OUT NOCOPY NUMBER,
2621 p_mode IN NUMBER,
2622 p_data_type IN NUMBER,
2623 p_debug_enabled IN NUMBER
2624 )
2625 IS
2626
2627 CURSOR C_OVERLAPPING_RANGE_EXISTS IS
2628 SELECT 1 FROM DUAL WHERE EXISTS
2629 (
2630 SELECT 1 FROM GL_BUDGET_ASSIGNMENT_RANGES BA1
2631 WHERE EXISTS
2632 (SELECT 1 FROM
2633 GL_BUDGET_ASSIGNMENT_RANGES BA2
2634 WHERE
2635 BA2.ledger_id = BA1.ledger_id
2636 AND BA2.currency_code = BA1.currency_code
2637 AND BA1.RANGE_ID <> BA2.RANGE_ID
2638 AND NVL(BA1.SEGMENT1_LOW,'X') <= NVL(BA2.SEGMENT1_HIGH,'X')
2639 AND NVL(BA1.SEGMENT1_HIGH,'X') >= NVL(BA2.SEGMENT1_LOW,'X')
2640 AND NVL(BA1.SEGMENT2_LOW,'X') <= NVL(BA2.SEGMENT2_HIGH,'X')
2641 AND NVL(BA1.SEGMENT2_HIGH,'X') >= NVL(BA2.SEGMENT2_LOW,'X')
2642 AND NVL(BA1.SEGMENT3_LOW,'X') <= NVL(BA2.SEGMENT3_HIGH,'X')
2643 AND NVL(BA1.SEGMENT3_HIGH,'X') >= NVL(BA2.SEGMENT3_LOW,'X')
2644 AND NVL(BA1.SEGMENT4_LOW,'X') <= NVL(BA2.SEGMENT4_HIGH,'X')
2645 AND NVL(BA1.SEGMENT4_HIGH,'X') >= NVL(BA2.SEGMENT4_LOW,'X')
2646 AND NVL(BA1.SEGMENT5_LOW,'X') <= NVL(BA2.SEGMENT5_HIGH,'X')
2647 AND NVL(BA1.SEGMENT5_HIGH,'X') >= NVL(BA2.SEGMENT5_LOW,'X')
2648 AND NVL(BA1.SEGMENT6_LOW,'X') <= NVL(BA2.SEGMENT6_HIGH,'X')
2649 AND NVL(BA1.SEGMENT6_HIGH,'X') >= NVL(BA2.SEGMENT6_LOW,'X')
2650 AND NVL(BA1.SEGMENT7_LOW,'X') <= NVL(BA2.SEGMENT7_HIGH,'X')
2651 AND NVL(BA1.SEGMENT7_HIGH,'X') >= NVL(BA2.SEGMENT7_LOW,'X')
2652 AND NVL(BA1.SEGMENT8_LOW,'X') <= NVL(BA2.SEGMENT8_HIGH,'X')
2653 AND NVL(BA1.SEGMENT8_HIGH,'X') >= NVL(BA2.SEGMENT8_LOW,'X')
2654 AND NVL(BA1.SEGMENT9_LOW,'X') <= NVL(BA2.SEGMENT9_HIGH,'X')
2655 AND NVL(BA1.SEGMENT9_HIGH,'X') >= NVL(BA2.SEGMENT9_LOW,'X')
2656 AND NVL(BA1.SEGMENT10_LOW,'X') <= NVL(BA2.SEGMENT10_HIGH,'X')
2657 AND NVL(BA1.SEGMENT10_HIGH,'X') >= NVL(BA2.SEGMENT10_LOW,'X')
2658 AND NVL(BA1.SEGMENT11_LOW,'X') <= NVL(BA2.SEGMENT11_HIGH,'X')
2659 AND NVL(BA1.SEGMENT11_HIGH,'X') >= NVL(BA2.SEGMENT11_LOW,'X')
2660 AND NVL(BA1.SEGMENT12_LOW,'X') <= NVL(BA2.SEGMENT12_HIGH,'X')
2661 AND NVL(BA1.SEGMENT12_HIGH,'X') >= NVL(BA2.SEGMENT12_LOW,'X')
2662 AND NVL(BA1.SEGMENT13_LOW,'X') <= NVL(BA2.SEGMENT13_HIGH,'X')
2663 AND NVL(BA1.SEGMENT13_HIGH,'X') >= NVL(BA2.SEGMENT13_LOW,'X')
2664 AND NVL(BA1.SEGMENT14_LOW,'X') <= NVL(BA2.SEGMENT14_HIGH,'X')
2665 AND NVL(BA1.SEGMENT14_HIGH,'X') >= NVL(BA2.SEGMENT14_LOW,'X')
2666 AND NVL(BA1.SEGMENT15_LOW,'X') <= NVL(BA2.SEGMENT15_HIGH,'X')
2667 AND NVL(BA1.SEGMENT15_HIGH,'X') >= NVL(BA2.SEGMENT15_LOW,'X')
2668 AND NVL(BA1.SEGMENT16_LOW,'X') <= NVL(BA2.SEGMENT16_HIGH,'X')
2669 AND NVL(BA1.SEGMENT16_HIGH,'X') >= NVL(BA2.SEGMENT16_LOW,'X')
2670 AND NVL(BA1.SEGMENT17_LOW,'X') <= NVL(BA2.SEGMENT17_HIGH,'X')
2671 AND NVL(BA1.SEGMENT17_HIGH,'X') >= NVL(BA2.SEGMENT17_LOW,'X')
2672 AND NVL(BA1.SEGMENT18_LOW,'X') <= NVL(BA2.SEGMENT18_HIGH,'X')
2673 AND NVL(BA1.SEGMENT18_HIGH,'X') >= NVL(BA2.SEGMENT18_LOW,'X')
2674 AND NVL(BA1.SEGMENT19_LOW,'X') <= NVL(BA2.SEGMENT19_HIGH,'X')
2675 AND NVL(BA1.SEGMENT19_HIGH,'X') >= NVL(BA2.SEGMENT19_LOW,'X')
2676 AND NVL(BA1.SEGMENT20_LOW,'X') <= NVL(BA2.SEGMENT20_HIGH,'X')
2677 AND NVL(BA1.SEGMENT20_HIGH,'X') >= NVL(BA2.SEGMENT20_LOW,'X')
2678 AND NVL(BA1.SEGMENT21_LOW,'X') <= NVL(BA2.SEGMENT21_HIGH,'X')
2679 AND NVL(BA1.SEGMENT21_HIGH,'X') >= NVL(BA2.SEGMENT21_LOW,'X')
2680 AND NVL(BA1.SEGMENT22_LOW,'X') <= NVL(BA2.SEGMENT22_HIGH,'X')
2681 AND NVL(BA1.SEGMENT22_HIGH,'X') >= NVL(BA2.SEGMENT22_LOW,'X')
2682 AND NVL(BA1.SEGMENT23_LOW,'X') <= NVL(BA2.SEGMENT23_HIGH,'X')
2683 AND NVL(BA1.SEGMENT23_HIGH,'X') >= NVL(BA2.SEGMENT23_LOW,'X')
2684 AND NVL(BA1.SEGMENT24_LOW,'X') <= NVL(BA2.SEGMENT24_HIGH,'X')
2685 AND NVL(BA1.SEGMENT24_HIGH,'X') >= NVL(BA2.SEGMENT24_LOW,'X')
2686 AND NVL(BA1.SEGMENT25_LOW,'X') <= NVL(BA2.SEGMENT25_HIGH,'X')
2687 AND NVL(BA1.SEGMENT25_HIGH,'X') >= NVL(BA2.SEGMENT25_LOW,'X')
2688 AND NVL(BA1.SEGMENT26_LOW,'X') <= NVL(BA2.SEGMENT26_HIGH,'X')
2689 AND NVL(BA1.SEGMENT26_HIGH,'X') >= NVL(BA2.SEGMENT26_LOW,'X')
2690 AND NVL(BA1.SEGMENT27_LOW,'X') <= NVL(BA2.SEGMENT27_HIGH,'X')
2691 AND NVL(BA1.SEGMENT27_HIGH,'X') >= NVL(BA2.SEGMENT27_LOW,'X')
2692 AND NVL(BA1.SEGMENT28_LOW,'X') <= NVL(BA2.SEGMENT28_HIGH,'X')
2693 AND NVL(BA1.SEGMENT28_HIGH,'X') >= NVL(BA2.SEGMENT28_LOW,'X')
2694 AND NVL(BA1.SEGMENT29_LOW,'X') <= NVL(BA2.SEGMENT29_HIGH,'X')
2695 AND NVL(BA1.SEGMENT29_HIGH,'X') >= NVL(BA2.SEGMENT29_LOW,'X')
2696 AND NVL(BA1.SEGMENT30_LOW,'X') <= NVL(BA2.SEGMENT30_HIGH,'X')
2697 AND NVL(BA1.SEGMENT30_HIGH,'X') >= NVL(BA2.SEGMENT30_LOW,'X')
2698 )
2699 AND BA1.RANGE_ID NOT IN (
2700 SELECT RANGE_ID FROM GL_BUDGET_ASSIGNMENT_RANGES BA3
2701 WHERE
2702 BA3.LEDGER_ID = BA1.LEDGER_ID AND
2703 BA3.CURRENCY_CODE = BA1.CURRENCY_CODE AND
2704 EXISTS
2705 (SELECT 1 FROM
2706 GL_BUDGET_ASSIGNMENT_RANGES BA4
2707 WHERE
2708 BA4.ledger_id = BA3.ledger_id
2709 AND BA4.currency_code = BA3.currency_code
2710 AND BA3.RANGE_ID <> BA4.RANGE_ID
2711 AND NVL(BA3.SEGMENT1_LOW,'X') = NVL(BA4.SEGMENT1_LOW,'X')
2712 AND NVL(BA3.SEGMENT1_HIGH,'X') = NVL(BA4.SEGMENT1_HIGH,'X')
2713 AND NVL(BA3.SEGMENT2_LOW,'X') = NVL(BA4.SEGMENT2_LOW,'X')
2714 AND NVL(BA3.SEGMENT2_HIGH,'X') = NVL(BA4.SEGMENT2_HIGH,'X')
2715 AND NVL(BA3.SEGMENT3_LOW,'X') = NVL(BA4.SEGMENT3_LOW,'X')
2716 AND NVL(BA3.SEGMENT3_HIGH,'X') = NVL(BA4.SEGMENT3_HIGH,'X')
2717 AND NVL(BA3.SEGMENT4_LOW,'X') = NVL(BA4.SEGMENT4_LOW,'X')
2718 AND NVL(BA3.SEGMENT4_HIGH,'X') = NVL(BA4.SEGMENT4_HIGH,'X')
2719 AND NVL(BA3.SEGMENT5_LOW,'X') = NVL(BA4.SEGMENT5_LOW,'X')
2720 AND NVL(BA3.SEGMENT5_HIGH,'X') = NVL(BA4.SEGMENT5_HIGH,'X')
2721 AND NVL(BA3.SEGMENT6_LOW,'X') = NVL(BA4.SEGMENT6_LOW,'X')
2722 AND NVL(BA3.SEGMENT6_HIGH,'X') = NVL(BA4.SEGMENT6_HIGH,'X')
2723 AND NVL(BA3.SEGMENT7_LOW,'X') = NVL(BA4.SEGMENT7_LOW,'X')
2724 AND NVL(BA3.SEGMENT7_HIGH,'X') = NVL(BA4.SEGMENT7_HIGH,'X')
2725 AND NVL(BA3.SEGMENT8_LOW,'X') = NVL(BA4.SEGMENT8_LOW,'X')
2726 AND NVL(BA3.SEGMENT8_HIGH,'X') = NVL(BA4.SEGMENT8_HIGH,'X')
2727 AND NVL(BA3.SEGMENT9_LOW,'X') = NVL(BA4.SEGMENT9_LOW,'X')
2728 AND NVL(BA3.SEGMENT9_HIGH,'X') = NVL(BA4.SEGMENT9_HIGH,'X')
2729 AND NVL(BA3.SEGMENT10_LOW,'X') = NVL(BA4.SEGMENT10_LOW,'X')
2730 AND NVL(BA3.SEGMENT10_HIGH,'X') = NVL(BA4.SEGMENT10_HIGH,'X')
2731 AND NVL(BA3.SEGMENT11_LOW,'X') = NVL(BA4.SEGMENT11_LOW,'X')
2732 AND NVL(BA3.SEGMENT11_HIGH,'X') = NVL(BA4.SEGMENT11_HIGH,'X')
2733 AND NVL(BA3.SEGMENT12_LOW,'X') = NVL(BA4.SEGMENT12_LOW,'X')
2734 AND NVL(BA3.SEGMENT12_HIGH,'X') = NVL(BA4.SEGMENT12_HIGH,'X')
2735 AND NVL(BA3.SEGMENT13_LOW,'X') = NVL(BA4.SEGMENT13_LOW,'X')
2736 AND NVL(BA3.SEGMENT13_HIGH,'X') = NVL(BA4.SEGMENT13_HIGH,'X')
2737 AND NVL(BA3.SEGMENT14_LOW,'X') = NVL(BA4.SEGMENT14_LOW,'X')
2738 AND NVL(BA3.SEGMENT14_HIGH,'X') = NVL(BA4.SEGMENT14_HIGH,'X')
2739 AND NVL(BA3.SEGMENT15_LOW,'X') = NVL(BA4.SEGMENT15_LOW,'X')
2740 AND NVL(BA3.SEGMENT15_HIGH,'X') = NVL(BA4.SEGMENT15_HIGH,'X')
2741 AND NVL(BA3.SEGMENT16_LOW,'X') = NVL(BA4.SEGMENT16_LOW,'X')
2742 AND NVL(BA3.SEGMENT16_HIGH,'X') = NVL(BA4.SEGMENT16_HIGH,'X')
2743 AND NVL(BA3.SEGMENT17_LOW,'X') = NVL(BA4.SEGMENT17_LOW,'X')
2744 AND NVL(BA3.SEGMENT17_HIGH,'X') = NVL(BA4.SEGMENT17_HIGH,'X')
2745 AND NVL(BA3.SEGMENT18_LOW,'X') = NVL(BA4.SEGMENT18_LOW,'X')
2746 AND NVL(BA3.SEGMENT18_HIGH,'X') = NVL(BA4.SEGMENT18_HIGH,'X')
2747 AND NVL(BA3.SEGMENT19_LOW,'X') = NVL(BA4.SEGMENT19_LOW,'X')
2748 AND NVL(BA3.SEGMENT19_HIGH,'X') = NVL(BA4.SEGMENT19_HIGH,'X')
2749 AND NVL(BA3.SEGMENT20_LOW,'X') = NVL(BA4.SEGMENT20_LOW,'X')
2750 AND NVL(BA3.SEGMENT20_HIGH,'X') = NVL(BA4.SEGMENT20_HIGH,'X')
2751 AND NVL(BA3.SEGMENT21_LOW,'X') = NVL(BA4.SEGMENT21_LOW,'X')
2752 AND NVL(BA3.SEGMENT21_HIGH,'X') = NVL(BA4.SEGMENT21_HIGH,'X')
2753 AND NVL(BA3.SEGMENT22_LOW,'X') = NVL(BA4.SEGMENT22_LOW,'X')
2754 AND NVL(BA3.SEGMENT22_HIGH,'X') = NVL(BA4.SEGMENT22_HIGH,'X')
2755 AND NVL(BA3.SEGMENT23_LOW,'X') = NVL(BA4.SEGMENT23_LOW,'X')
2756 AND NVL(BA3.SEGMENT23_HIGH,'X') = NVL(BA4.SEGMENT23_HIGH,'X')
2757 AND NVL(BA3.SEGMENT24_LOW,'X') = NVL(BA4.SEGMENT24_LOW,'X')
2758 AND NVL(BA3.SEGMENT24_HIGH,'X') = NVL(BA4.SEGMENT24_HIGH,'X')
2759 AND NVL(BA3.SEGMENT25_LOW,'X') = NVL(BA4.SEGMENT25_LOW,'X')
2760 AND NVL(BA3.SEGMENT25_HIGH,'X') = NVL(BA4.SEGMENT25_HIGH,'X')
2761 AND NVL(BA3.SEGMENT26_LOW,'X') = NVL(BA4.SEGMENT26_LOW,'X')
2762 AND NVL(BA3.SEGMENT26_HIGH,'X') = NVL(BA4.SEGMENT26_HIGH,'X')
2763 AND NVL(BA3.SEGMENT27_LOW,'X') = NVL(BA4.SEGMENT27_LOW,'X')
2764 AND NVL(BA3.SEGMENT27_HIGH,'X') = NVL(BA4.SEGMENT27_HIGH,'X')
2765 AND NVL(BA3.SEGMENT28_LOW,'X') = NVL(BA4.SEGMENT28_LOW,'X')
2766 AND NVL(BA3.SEGMENT28_HIGH,'X') = NVL(BA4.SEGMENT28_HIGH,'X')
2767 AND NVL(BA3.SEGMENT29_LOW,'X') = NVL(BA4.SEGMENT29_LOW,'X')
2768 AND NVL(BA3.SEGMENT29_HIGH,'X') = NVL(BA4.SEGMENT29_HIGH,'X')
2769 AND NVL(BA3.SEGMENT30_LOW,'X') = NVL(BA4.SEGMENT30_LOW,'X')
2770 AND NVL(BA3.SEGMENT30_HIGH,'X') = NVL(BA4.SEGMENT30_HIGH,'X')
2771 ))
2772 AND EXISTS
2773 (SELECT 1
2774 FROM GL_BUDGET_ENTITIES glent
2775 WHERE
2776 BA1.ledger_id = glent.ledger_id AND
2777 EXISTS (
2778 SELECT 1
2779 FROM PSA_EFC_OPTIONS psaefc
2780 WHERE psaefc.set_of_books_id = glent.ledger_id
2781 AND psaefc.mult_funding_budgets_flag = 'Y'
2782 ))
2783
2784 );
2785
2786 CURSOR c_ledger_info IS
2787 SELECT DISTINCT glent.ledger_id
2788 FROM gl_budget_entities glent
2789 WHERE EXISTS (
2790 SELECT 1
2791 FROM psa_efc_options psaefc
2792 WHERE psaefc.set_of_books_id = glent.ledger_id
2793 AND psaefc.mult_funding_budgets_flag = 'Y'
2794 );
2795
2796 CURSOR c_backup_exists IS
2797 SELECT * FROM IGI_EFC_BUDGET_ASSIGNMENT_BCK;
2798
2799 lc_backup_exists c_backup_exists%ROWTYPE;
2800 lc_c_ledger_info c_ledger_info%ROWTYPE;
2801
2802 l_errbuf VARCHAR2(2000);
2803 l_retcode NUMBER;
2804
2805 lc_overlapping_range_exists C_OVERLAPPING_RANGE_EXISTS%ROWTYPE;
2806
2807 BEGIN
2808
2809 SAVEPOINT EFC_UPGRADE_START;
2810
2811 OPEN c_ledger_info;
2812 FETCH c_ledger_info INTO lc_c_ledger_info;
2813 IF c_ledger_info%NOTFOUND THEN
2814 CLOSE c_ledger_info;
2815 fnd_file.put_line(fnd_file.output, 'No EFC Ledgers Found');
2816 fnd_file.put_line(fnd_file.log, 'Module: START_EFC_UPGRADE =>'||'No EFC Ledgers Found');
2817 errbuf := 'No EFC Ledgers Found';
2818 retcode := -1;
2819 RETURN;
2820 END IF;
2821 CLOSE c_ledger_info;
2822
2823 g_debug_enabled := p_debug_enabled;
2824
2825 OPEN C_OVERLAPPING_RANGE_EXISTS;
2826 FETCH C_OVERLAPPING_RANGE_EXISTS INTO lc_overlapping_range_exists;
2827 IF C_OVERLAPPING_RANGE_EXISTS%FOUND AND p_data_type <> 0 THEN
2828 CLOSE C_OVERLAPPING_RANGE_EXISTS;
2829 fnd_file.put_line(fnd_file.output, 'You have segment ranges which merge with other segment ranges
2830 for the same ledger and currency. This upgrade script does
2831 not support merging segment ranges for segments which have variable
2832 non numeric values. Merging segment ranges are supported only if your
2833 segment contains fixed length numeric values. Please contact Oracle Support for help');
2834 fnd_file.put_line(fnd_file.log, 'You have segment ranges which merge with other segment ranges
2835 for the same ledger and currency code. This upgrade script does
2836 not support merging segment ranges for segments which have variable
2837 non numeric values. Merging segment ranges are supported only if your
2838 segment contains fixed length numeric values. Please contact Oracle Support for help');
2839 errbuf := 'Non numeric segments exists and merging ranges are found';
2840 retcode := -1;
2841 RETURN;
2842 ELSE
2843 CLOSE C_OVERLAPPING_RANGE_EXISTS;
2844 END IF;
2845
2846 IF p_mode = 1 THEN
2847 fnd_file.put_line(fnd_file.output, 'Running EFC Upgrade in Final Mode');
2848 fnd_file.put_line(fnd_file.log, 'Module: START_EFC_UPGRADE =>'||'Running EFC Upgrade in Final Mode');
2849 ELSE
2850 fnd_file.put_line(fnd_file.output, 'Running EFC Upgrade in Preliminary Mode');
2851 fnd_file.put_line(fnd_file.log, 'Module: START_EFC_UPGRADE =>'||'Running EFC Upgrade in Preliminary Mode');
2852 END IF;
2853
2854 BEGIN
2855
2856 DELETE FROM IGI_UPG_GL_BUDGET_ASSIGNMENT;
2857 DELETE FROM IGI_UPG_GL_BUDORG_BC_OPTIONS;
2858
2859 EXCEPTION
2860 WHEN OTHERS THEN
2861 fnd_file.put_line(fnd_file.output, 'Purging of tables failed');
2862 fnd_file.put_line(fnd_file.log, 'Module: START_EFC_UPGRADE =>'||'Purging of tables failed =>'||SQLERRM);
2863 errbuf := 'Purging of tables failed'||SQLERRM;
2864 retcode := -1;
2865 RETURN;
2866 END;
2867
2868
2869 IF p_mode = 1 THEN
2870
2871 OPEN c_backup_exists;
2872 FETCH c_backup_exists INTO lc_backup_exists;
2873 IF c_backup_exists%FOUND THEN
2874 --Error final mode run twice
2875 CLOSE c_backup_exists;
2876 fnd_file.put_line(fnd_file.output, 'Upgrade Script has been run in Final Mode and thus cannot be run in Final Mode again');
2877 fnd_file.put_line(fnd_file.log, 'Module: START_EFC_UPGRADE =>'||'Upgrade Script has been run in Final Mode');
2878 errbuf := 'Upgrade Script has been run in Final Mode and thus cannot be run again';
2879 retcode := -1;
2880 RETURN;
2881 END IF;
2882 CLOSE c_backup_exists;
2883
2884 BEGIN
2885
2886 INSERT INTO IGI_EFC_BUDGET_ASSIGNMENT_BCK
2887 (
2888 BUDGET_ENTITY_ID,
2889 LEDGER_ID,
2890 CURRENCY_CODE,
2891 ENTRY_CODE,
2892 RANGE_ID,
2893 STATUS,
2894 LAST_UPDATE_DATE,
2895 AUTOMATIC_ENCUMBRANCE_FLAG,
2896 CREATED_BY,
2897 CREATION_DATE,
2898 FUNDS_CHECK_LEVEL_CODE,
2899 LAST_UPDATED_BY,
2900 LAST_UPDATE_LOGIN,
2901 SEQUENCE_NUMBER,
2902 SEGMENT1_LOW,
2903 SEGMENT1_HIGH,
2904 SEGMENT2_LOW,
2905 SEGMENT2_HIGH,
2906 SEGMENT3_LOW,
2907 SEGMENT3_HIGH,
2908 SEGMENT4_LOW,
2909 SEGMENT4_HIGH,
2910 SEGMENT5_LOW,
2911 SEGMENT5_HIGH,
2912 SEGMENT6_LOW,
2913 SEGMENT6_HIGH,
2914 SEGMENT7_LOW,
2915 SEGMENT7_HIGH,
2916 SEGMENT8_LOW,
2917 SEGMENT8_HIGH,
2918 SEGMENT9_LOW,
2919 SEGMENT9_HIGH,
2920 SEGMENT10_LOW,
2921 SEGMENT10_HIGH,
2922 SEGMENT11_LOW,
2923 SEGMENT11_HIGH,
2924 SEGMENT12_LOW,
2925 SEGMENT12_HIGH,
2926 SEGMENT13_LOW,
2927 SEGMENT13_HIGH,
2928 SEGMENT14_LOW,
2929 SEGMENT14_HIGH,
2930 SEGMENT15_LOW,
2931 SEGMENT15_HIGH,
2932 SEGMENT16_LOW,
2933 SEGMENT16_HIGH,
2934 SEGMENT17_LOW,
2935 SEGMENT17_HIGH,
2936 SEGMENT18_LOW,
2937 SEGMENT18_HIGH,
2938 SEGMENT19_LOW,
2939 SEGMENT19_HIGH,
2940 SEGMENT20_LOW,
2941 SEGMENT20_HIGH,
2942 SEGMENT21_LOW,
2943 SEGMENT21_HIGH,
2944 SEGMENT22_LOW,
2945 SEGMENT22_HIGH,
2946 SEGMENT23_LOW,
2947 SEGMENT23_HIGH,
2948 SEGMENT24_LOW,
2949 SEGMENT24_HIGH,
2950 SEGMENT25_LOW,
2951 SEGMENT25_HIGH,
2952 SEGMENT26_LOW,
2953 SEGMENT26_HIGH,
2954 SEGMENT27_LOW,
2955 SEGMENT27_HIGH,
2956 SEGMENT28_LOW,
2957 SEGMENT28_HIGH,
2958 SEGMENT29_LOW,
2959 SEGMENT29_HIGH,
2960 SEGMENT30_LOW,
2961 SEGMENT30_HIGH,
2962 AMOUNT_TYPE,
2963 BOUNDARY_CODE,
2964 CONTEXT,
2965 FUNDING_BUDGET_VERSION_ID,
2966 PROGRAM_APPLICATION_ID,
2967 PROGRAM_ID,
2968 PROGRAM_UPDATE_DATE,
2969 REQUEST_ID,
2970 ATTRIBUTE1,
2971 ATTRIBUTE2,
2972 ATTRIBUTE3,
2973 ATTRIBUTE4,
2974 ATTRIBUTE5,
2975 ATTRIBUTE6,
2976 ATTRIBUTE7,
2977 ATTRIBUTE8,
2978 ATTRIBUTE9,
2979 ATTRIBUTE10,
2980 ATTRIBUTE11,
2981 ATTRIBUTE12,
2982 ATTRIBUTE13,
2983 ATTRIBUTE14,
2984 ATTRIBUTE15
2985 )
2986 SELECT
2987 BUDGET_ENTITY_ID,
2988 LEDGER_ID,
2989 CURRENCY_CODE,
2990 ENTRY_CODE,
2991 RANGE_ID,
2992 STATUS,
2993 LAST_UPDATE_DATE,
2994 AUTOMATIC_ENCUMBRANCE_FLAG,
2995 CREATED_BY,
2996 CREATION_DATE,
2997 FUNDS_CHECK_LEVEL_CODE,
2998 LAST_UPDATED_BY,
2999 LAST_UPDATE_LOGIN,
3000 SEQUENCE_NUMBER,
3001 SEGMENT1_LOW,
3002 SEGMENT1_HIGH,
3003 SEGMENT2_LOW,
3004 SEGMENT2_HIGH,
3005 SEGMENT3_LOW,
3006 SEGMENT3_HIGH,
3007 SEGMENT4_LOW,
3008 SEGMENT4_HIGH,
3009 SEGMENT5_LOW,
3010 SEGMENT5_HIGH,
3011 SEGMENT6_LOW,
3012 SEGMENT6_HIGH,
3013 SEGMENT7_LOW,
3014 SEGMENT7_HIGH,
3015 SEGMENT8_LOW,
3016 SEGMENT8_HIGH,
3017 SEGMENT9_LOW,
3018 SEGMENT9_HIGH,
3019 SEGMENT10_LOW,
3020 SEGMENT10_HIGH,
3021 SEGMENT11_LOW,
3022 SEGMENT11_HIGH,
3023 SEGMENT12_LOW,
3024 SEGMENT12_HIGH,
3025 SEGMENT13_LOW,
3026 SEGMENT13_HIGH,
3027 SEGMENT14_LOW,
3028 SEGMENT14_HIGH,
3029 SEGMENT15_LOW,
3030 SEGMENT15_HIGH,
3031 SEGMENT16_LOW,
3032 SEGMENT16_HIGH,
3033 SEGMENT17_LOW,
3034 SEGMENT17_HIGH,
3035 SEGMENT18_LOW,
3036 SEGMENT18_HIGH,
3037 SEGMENT19_LOW,
3038 SEGMENT19_HIGH,
3039 SEGMENT20_LOW,
3040 SEGMENT20_HIGH,
3041 SEGMENT21_LOW,
3042 SEGMENT21_HIGH,
3043 SEGMENT22_LOW,
3044 SEGMENT22_HIGH,
3045 SEGMENT23_LOW,
3046 SEGMENT23_HIGH,
3047 SEGMENT24_LOW,
3048 SEGMENT24_HIGH,
3049 SEGMENT25_LOW,
3050 SEGMENT25_HIGH,
3051 SEGMENT26_LOW,
3052 SEGMENT26_HIGH,
3053 SEGMENT27_LOW,
3054 SEGMENT27_HIGH,
3055 SEGMENT28_LOW,
3056 SEGMENT28_HIGH,
3057 SEGMENT29_LOW,
3058 SEGMENT29_HIGH,
3059 SEGMENT30_LOW,
3060 SEGMENT30_HIGH,
3061 AMOUNT_TYPE,
3062 BOUNDARY_CODE,
3063 CONTEXT,
3064 FUNDING_BUDGET_VERSION_ID,
3065 PROGRAM_APPLICATION_ID,
3066 PROGRAM_ID,
3067 PROGRAM_UPDATE_DATE,
3068 REQUEST_ID,
3069 ATTRIBUTE1,
3070 ATTRIBUTE2,
3071 ATTRIBUTE3,
3072 ATTRIBUTE4,
3073 ATTRIBUTE5,
3074 ATTRIBUTE6,
3075 ATTRIBUTE7,
3076 ATTRIBUTE8,
3077 ATTRIBUTE9,
3078 ATTRIBUTE10,
3079 ATTRIBUTE11,
3080 ATTRIBUTE12,
3081 ATTRIBUTE13,
3082 ATTRIBUTE14,
3083 ATTRIBUTE15
3084 FROM GL_BUDGET_ASSIGNMENT_RANGES gar
3085 WHERE exists (SELECT 1
3086 FROM gl_budget_entities glent
3087 WHERE
3088 gar.ledger_id = glent.ledger_id AND
3089 EXISTS (
3090 SELECT 1
3091 FROM psa_efc_options psaefc
3092 WHERE psaefc.set_of_books_id = glent.ledger_id
3093 AND psaefc.mult_funding_budgets_flag = 'Y'
3094 ));
3095
3096 EXCEPTION
3097 WHEN OTHERS THEN
3098 fnd_file.put_line(fnd_file.output, 'Unexpected Error: Please check the log');
3099 fnd_file.put_line(fnd_file.log, 'Module: START_EFC_UPGRADE =>'||'Error while inserting into
3100 IGI_EFC_BUDGET_ASSIGNMENT_BCK =>'||SQLERRM);
3101 errbuf := 'Module: START_EFC_UPGRADE =>'||'Error while inserting into
3102 IGI_EFC_BUDGET_ASSIGNMENT_BCK =>'||SQLERRM;
3103 retcode := -1;
3104 RETURN;
3105 END;
3106
3107
3108 BEGIN
3109 INSERT INTO IGI_BUDORG_BC_OPTIONS_BCK
3110 (
3111 RANGE_ID,
3112 FUNDING_BUDGET_VERSION_ID,
3113 FUNDS_CHECK_LEVEL_CODE,
3114 AMOUNT_TYPE,
3115 BOUNDARY_CODE,
3116 CREATED_BY,
3117 CREATION_DATE,
3118 LAST_UPDATED_BY,
3119 LAST_UPDATE_LOGIN,
3120 LAST_UPDATE_DATE
3121 )
3122 SELECT
3123 RANGE_ID,
3124 FUNDING_BUDGET_VERSION_ID,
3125 FUNDS_CHECK_LEVEL_CODE,
3126 AMOUNT_TYPE,
3127 BOUNDARY_CODE,
3128 CREATED_BY,
3129 CREATION_DATE,
3130 LAST_UPDATED_BY,
3131 LAST_UPDATE_LOGIN,
3132 LAST_UPDATE_DATE
3133 FROM GL_BUDORG_BC_OPTIONS
3134 WHERE range_id IN
3135 (SELECT range_id
3136 FROM
3137 IGI_EFC_BUDGET_ASSIGNMENT_BCK);
3138
3139 EXCEPTION
3140 WHEN OTHERS THEN
3141 fnd_file.put_line(fnd_file.output, 'Unexpected Error: Please check the log');
3142 fnd_file.put_line(fnd_file.log, 'Module: START_EFC_UPGRADE =>'||'Error while inserting into
3143 IGI_BUDORG_BC_OPTIONS_BCK =>'||SQLERRM);
3144 errbuf := 'Module: START_EFC_UPGRADE =>'||'Error while inserting into
3145 IGI_BUDORG_BC_OPTIONS_BCK =>'||SQLERRM;
3146 retcode := -1;
3147 RETURN;
3148 END;
3149
3150
3151 BEGIN
3152 INSERT INTO IGI_GL_BUDGET_ASSIGN_BCK
3153 (
3154 LEDGER_ID,
3155 BUDGET_ENTITY_ID,
3156 CURRENCY_CODE,
3157 CODE_COMBINATION_ID,
3158 RANGE_ID,
3159 ENTRY_CODE,
3160 LAST_UPDATE_DATE,
3161 LAST_UPDATED_BY,
3162 AUTOMATIC_ENCUMBRANCE_FLAG,
3163 FUNDS_CHECK_LEVEL_CODE,
3164 ORDERING_VALUE,
3165 CREATION_DATE,
3166 CREATED_BY,
3167 LAST_UPDATE_LOGIN,
3168 ATTRIBUTE1,
3169 ATTRIBUTE2,
3170 ATTRIBUTE3,
3171 ATTRIBUTE4,
3172 ATTRIBUTE5,
3173 ATTRIBUTE6,
3174 ATTRIBUTE7,
3175 ATTRIBUTE8,
3176 CONTEXT,
3177 AMOUNT_TYPE,
3178 BOUNDARY_CODE,
3179 FUNDING_BUDGET_VERSION_ID,
3180 PROGRAM_APPLICATION_ID,
3181 PROGRAM_ID,
3182 PROGRAM_UPDATE_DATE,
3183 REQUEST_ID
3184 )
3185 SELECT
3186 LEDGER_ID,
3187 BUDGET_ENTITY_ID,
3188 CURRENCY_CODE,
3189 CODE_COMBINATION_ID,
3190 RANGE_ID,
3191 ENTRY_CODE,
3192 LAST_UPDATE_DATE,
3193 LAST_UPDATED_BY,
3194 AUTOMATIC_ENCUMBRANCE_FLAG,
3195 FUNDS_CHECK_LEVEL_CODE,
3196 ORDERING_VALUE,
3197 CREATION_DATE,
3198 CREATED_BY,
3199 LAST_UPDATE_LOGIN,
3200 ATTRIBUTE1,
3201 ATTRIBUTE2,
3202 ATTRIBUTE3,
3203 ATTRIBUTE4,
3204 ATTRIBUTE5,
3205 ATTRIBUTE6,
3206 ATTRIBUTE7,
3207 ATTRIBUTE8,
3208 CONTEXT,
3209 AMOUNT_TYPE,
3210 BOUNDARY_CODE,
3211 FUNDING_BUDGET_VERSION_ID,
3212 PROGRAM_APPLICATION_ID,
3213 PROGRAM_ID,
3214 PROGRAM_UPDATE_DATE,
3215 REQUEST_ID
3216 FROM GL_BUDGET_ASSIGNMENTS
3217 WHERE range_id IN
3218 (SELECT range_id
3219 FROM
3220 IGI_EFC_BUDGET_ASSIGNMENT_BCK);
3221
3222 EXCEPTION
3223 WHEN OTHERS THEN
3224 fnd_file.put_line(fnd_file.output, 'Unexpected Error: Please check the log');
3225 fnd_file.put_line(fnd_file.log, 'Module: START_EFC_UPGRADE =>'||'Error while inserting into
3226 IGI_GL_BUDGET_ASSIGN_BCK =>'||SQLERRM);
3227 errbuf := 'Module: START_EFC_UPGRADE =>'||'Error while inserting into
3228 IGI_GL_BUDGET_ASSIGN_BCK =>'||SQLERRM;
3229 retcode := -1;
3230 RETURN;
3231 END;
3232 END IF; -- If mode is Final mode
3233
3234 BEGIN
3235
3236 INSERT INTO IGI_UPG_GL_BUDGET_ASSIGNMENT
3237 (
3238 BUDGET_ENTITY_ID,
3239 LEDGER_ID,
3240 CURRENCY_CODE,
3241 ENTRY_CODE,
3242 RANGE_ID,
3243 STATUS,
3244 LAST_UPDATE_DATE,
3245 AUTOMATIC_ENCUMBRANCE_FLAG,
3246 CREATED_BY,
3247 CREATION_DATE,
3248 FUNDS_CHECK_LEVEL_CODE,
3249 LAST_UPDATED_BY,
3250 LAST_UPDATE_LOGIN,
3251 SEQUENCE_NUMBER,
3252 SEGMENT1_LOW,
3253 SEGMENT1_HIGH,
3254 SEGMENT2_LOW,
3255 SEGMENT2_HIGH,
3256 SEGMENT3_LOW,
3257 SEGMENT3_HIGH,
3258 SEGMENT4_LOW,
3259 SEGMENT4_HIGH,
3260 SEGMENT5_LOW,
3261 SEGMENT5_HIGH,
3262 SEGMENT6_LOW,
3263 SEGMENT6_HIGH,
3264 SEGMENT7_LOW,
3265 SEGMENT7_HIGH,
3266 SEGMENT8_LOW,
3267 SEGMENT8_HIGH,
3268 SEGMENT9_LOW,
3269 SEGMENT9_HIGH,
3270 SEGMENT10_LOW,
3271 SEGMENT10_HIGH,
3272 SEGMENT11_LOW,
3273 SEGMENT11_HIGH,
3274 SEGMENT12_LOW,
3275 SEGMENT12_HIGH,
3276 SEGMENT13_LOW,
3277 SEGMENT13_HIGH,
3278 SEGMENT14_LOW,
3279 SEGMENT14_HIGH,
3280 SEGMENT15_LOW,
3281 SEGMENT15_HIGH,
3282 SEGMENT16_LOW,
3283 SEGMENT16_HIGH,
3284 SEGMENT17_LOW,
3285 SEGMENT17_HIGH,
3286 SEGMENT18_LOW,
3287 SEGMENT18_HIGH,
3288 SEGMENT19_LOW,
3289 SEGMENT19_HIGH,
3290 SEGMENT20_LOW,
3291 SEGMENT20_HIGH,
3292 SEGMENT21_LOW,
3293 SEGMENT21_HIGH,
3294 SEGMENT22_LOW,
3295 SEGMENT22_HIGH,
3296 SEGMENT23_LOW,
3297 SEGMENT23_HIGH,
3298 SEGMENT24_LOW,
3299 SEGMENT24_HIGH,
3300 SEGMENT25_LOW,
3301 SEGMENT25_HIGH,
3302 SEGMENT26_LOW,
3303 SEGMENT26_HIGH,
3304 SEGMENT27_LOW,
3305 SEGMENT27_HIGH,
3306 SEGMENT28_LOW,
3307 SEGMENT28_HIGH,
3308 SEGMENT29_LOW,
3309 SEGMENT29_HIGH,
3310 SEGMENT30_LOW,
3311 SEGMENT30_HIGH,
3312 AMOUNT_TYPE,
3313 BOUNDARY_CODE,
3314 CONTEXT,
3315 FUNDING_BUDGET_VERSION_ID,
3316 PROGRAM_APPLICATION_ID,
3317 PROGRAM_ID,
3318 PROGRAM_UPDATE_DATE,
3319 REQUEST_ID,
3320 ATTRIBUTE1,
3321 ATTRIBUTE2,
3322 ATTRIBUTE3,
3323 ATTRIBUTE4,
3324 ATTRIBUTE5,
3325 ATTRIBUTE6,
3326 ATTRIBUTE7,
3327 ATTRIBUTE8,
3328 ATTRIBUTE9,
3329 ATTRIBUTE10,
3330 ATTRIBUTE11,
3331 ATTRIBUTE12,
3332 ATTRIBUTE13,
3333 ATTRIBUTE14,
3334 ATTRIBUTE15
3335 )
3336 SELECT
3337 BUDGET_ENTITY_ID,
3338 LEDGER_ID,
3339 CURRENCY_CODE,
3340 ENTRY_CODE,
3341 RANGE_ID,
3342 STATUS,
3343 LAST_UPDATE_DATE,
3344 AUTOMATIC_ENCUMBRANCE_FLAG,
3345 CREATED_BY,
3346 CREATION_DATE,
3347 FUNDS_CHECK_LEVEL_CODE,
3348 LAST_UPDATED_BY,
3349 LAST_UPDATE_LOGIN,
3350 SEQUENCE_NUMBER,
3351 SEGMENT1_LOW,
3352 SEGMENT1_HIGH,
3353 SEGMENT2_LOW,
3354 SEGMENT2_HIGH,
3355 SEGMENT3_LOW,
3356 SEGMENT3_HIGH,
3357 SEGMENT4_LOW,
3358 SEGMENT4_HIGH,
3359 SEGMENT5_LOW,
3360 SEGMENT5_HIGH,
3361 SEGMENT6_LOW,
3362 SEGMENT6_HIGH,
3363 SEGMENT7_LOW,
3364 SEGMENT7_HIGH,
3365 SEGMENT8_LOW,
3366 SEGMENT8_HIGH,
3367 SEGMENT9_LOW,
3368 SEGMENT9_HIGH,
3369 SEGMENT10_LOW,
3370 SEGMENT10_HIGH,
3371 SEGMENT11_LOW,
3372 SEGMENT11_HIGH,
3373 SEGMENT12_LOW,
3374 SEGMENT12_HIGH,
3375 SEGMENT13_LOW,
3376 SEGMENT13_HIGH,
3377 SEGMENT14_LOW,
3378 SEGMENT14_HIGH,
3379 SEGMENT15_LOW,
3380 SEGMENT15_HIGH,
3381 SEGMENT16_LOW,
3382 SEGMENT16_HIGH,
3383 SEGMENT17_LOW,
3384 SEGMENT17_HIGH,
3385 SEGMENT18_LOW,
3386 SEGMENT18_HIGH,
3387 SEGMENT19_LOW,
3388 SEGMENT19_HIGH,
3389 SEGMENT20_LOW,
3390 SEGMENT20_HIGH,
3391 SEGMENT21_LOW,
3392 SEGMENT21_HIGH,
3393 SEGMENT22_LOW,
3394 SEGMENT22_HIGH,
3395 SEGMENT23_LOW,
3396 SEGMENT23_HIGH,
3397 SEGMENT24_LOW,
3398 SEGMENT24_HIGH,
3399 SEGMENT25_LOW,
3400 SEGMENT25_HIGH,
3401 SEGMENT26_LOW,
3402 SEGMENT26_HIGH,
3403 SEGMENT27_LOW,
3404 SEGMENT27_HIGH,
3405 SEGMENT28_LOW,
3406 SEGMENT28_HIGH,
3407 SEGMENT29_LOW,
3408 SEGMENT29_HIGH,
3409 SEGMENT30_LOW,
3410 SEGMENT30_HIGH,
3411 AMOUNT_TYPE,
3412 BOUNDARY_CODE,
3413 CONTEXT,
3414 FUNDING_BUDGET_VERSION_ID,
3415 PROGRAM_APPLICATION_ID,
3416 PROGRAM_ID,
3417 PROGRAM_UPDATE_DATE,
3418 REQUEST_ID,
3419 ATTRIBUTE1,
3420 ATTRIBUTE2,
3421 ATTRIBUTE3,
3422 ATTRIBUTE4,
3423 ATTRIBUTE5,
3424 ATTRIBUTE6,
3425 ATTRIBUTE7,
3426 ATTRIBUTE8,
3427 ATTRIBUTE9,
3428 ATTRIBUTE10,
3429 ATTRIBUTE11,
3430 ATTRIBUTE12,
3431 ATTRIBUTE13,
3432 ATTRIBUTE14,
3433 ATTRIBUTE15
3434 FROM GL_BUDGET_ASSIGNMENT_RANGES gar
3435 WHERE exists (SELECT 1
3436 FROM GL_BUDGET_ENTITIES glent
3437 WHERE
3438 gar.ledger_id = glent.ledger_id AND
3439 EXISTS (
3440 SELECT 1
3441 FROM PSA_EFC_OPTIONS psaefc
3442 WHERE psaefc.set_of_books_id = glent.ledger_id
3443 AND psaefc.mult_funding_budgets_flag = 'Y'
3444 ));
3445
3446 EXCEPTION
3447 WHEN OTHERS THEN
3448 fnd_file.put_line(fnd_file.output, 'Unexpected Error: Please check the log');
3449 fnd_file.put_line(fnd_file.log, 'Module: START_EFC_UPGRADE =>'||'Error while inserting into
3450 IGI_UPG_GL_BUDGET_ASSIGNMENT =>'||SQLERRM);
3451 errbuf := 'Module: START_EFC_UPGRADE =>'||'Error while inserting into
3452 IGI_UPG_GL_BUDGET_ASSIGNMENT =>'||SQLERRM;
3453 retcode := -1;
3454 RETURN;
3455 END;
3456
3457
3458 BEGIN
3459
3460 INSERT INTO IGI_UPG_GL_BUDORG_BC_OPTIONS
3461 (
3462 RANGE_ID,
3463 FUNDING_BUDGET_VERSION_ID,
3464 FUNDS_CHECK_LEVEL_CODE,
3465 AMOUNT_TYPE,
3466 BOUNDARY_CODE,
3467 CREATED_BY,
3468 CREATION_DATE,
3469 LAST_UPDATED_BY,
3470 LAST_UPDATE_LOGIN,
3471 LAST_UPDATE_DATE
3472 )
3473 SELECT
3474 RANGE_ID,
3475 FUNDING_BUDGET_VERSION_ID,
3476 FUNDS_CHECK_LEVEL_CODE,
3477 AMOUNT_TYPE,
3478 BOUNDARY_CODE,
3479 CREATED_BY,
3480 CREATION_DATE,
3481 LAST_UPDATED_BY,
3482 LAST_UPDATE_LOGIN,
3483 LAST_UPDATE_DATE
3484 FROM GL_BUDORG_BC_OPTIONS
3485 WHERE range_id IN
3486 (SELECT range_id
3487 FROM
3488 IGI_UPG_GL_BUDGET_ASSIGNMENT);
3489
3490 EXCEPTION
3491 WHEN OTHERS THEN
3492 fnd_file.put_line(fnd_file.output, 'Unexpected Error: Please check the log');
3493 fnd_file.put_line(fnd_file.log, 'Module: START_EFC_UPGRADE =>'||'Error while inserting into
3494 IGI_UPG_GL_BUDORG_BC_OPTIONS =>'||SQLERRM);
3495 errbuf := 'Module: START_EFC_UPGRADE =>'||'Error while inserting into
3496 IGI_UPG_GL_BUDORG_BC_OPTIONS =>'||SQLERRM;
3497 retcode := -1;
3498 RETURN;
3499 END;
3500
3501 IF p_mode = 1 THEN
3502
3503 BEGIN
3504
3505 DELETE FROM GL_BUDORG_BC_OPTIONS
3506 WHERE range_id IN
3507 (SELECT range_id
3508 FROM
3509 IGI_EFC_BUDGET_ASSIGNMENT_BCK);
3510
3511 DELETE FROM GL_BUDGET_ASSIGNMENT_RANGES
3512 WHERE range_id IN
3513 (SELECT range_id
3514 FROM
3515 IGI_EFC_BUDGET_ASSIGNMENT_BCK);
3516
3517 DELETE FROM GL_BUDGET_ASSIGNMENTS
3518 WHERE range_id IN
3519 (SELECT range_id
3520 FROM
3521 IGI_EFC_BUDGET_ASSIGNMENT_BCK);
3522
3523 EXCEPTION
3524 WHEN OTHERS THEN
3525 fnd_file.put_line(fnd_file.output, 'Unexpected Error: Please check the log');
3526 fnd_file.put_line(fnd_file.log, 'Module: START_EFC_UPGRADE =>'||'Error while deletion =>'||SQLERRM);
3527 errbuf := 'Module: START_EFC_UPGRADE =>'||'Error while deletion =>'||SQLERRM;
3528 retcode := -1;
3529 RETURN;
3530 END;
3531
3532 END IF;
3533
3534 LOOP_AND_PROCESS(p_data_type,p_mode,l_errbuf,l_retcode);
3535
3536 IF l_retcode IS NOT NULL AND l_retcode = -1 THEN
3537 errbuf := l_errbuf;
3538 retcode := l_retcode;
3539 fnd_file.put_line(fnd_file.output, 'Unexpected Error: Please check the log');
3540 fnd_file.put_line(fnd_file.log, 'Module: START_EFC_UPGRADE =>'|| l_errbuf);
3541 ROLLBACK TO EFC_UPGRADE_START;
3542 fnd_file.put_line(fnd_file.log, 'Rollback Completed');
3543 ELSE
3544 IF p_mode = 1 THEN
3545 COMMIT;
3546 fnd_file.put_line(fnd_file.output, 'Process completed successfully in Final Mode');
3547 fnd_file.put_line(fnd_file.log, 'Process completed successfully in Final Mode');
3548 ELSE
3549 ROLLBACK TO EFC_UPGRADE_START;
3550 fnd_file.put_line(fnd_file.output, 'Process completed successfully in Preliminary Mode');
3551 fnd_file.put_line(fnd_file.log, 'Process completed successfully in Preliminary Mode');
3552 END IF;
3553
3554 END IF;
3555
3556 END START_EFC_UPGRADE;
3557
3558 END IGI_EFC_UPGRADE;