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