88:
89: TYPE spread_record_type IS RECORD
90: (start_date DATE,
91: end_date DATE,
92: period_name gl_periods.period_name%TYPE, --VARCHAR2,
93: actual_days INTEGER,
94: actual_periods NUMBER,
95: allocation NUMBER,
96: percentage NUMBER,
2077: EXPLAIN PLAN IS:
2078: ================
2079: 1:SELECT STATEMENT :(cost=8,rows=2)
2080: 2:SORT ORDER BY :(cost=8,rows=2)
2081: 3:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=2)
2082: 4:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=2)
2083: **/
2084: CURSOR get_gl_periods_csr IS
2085: SELECT START_DATE, END_DATE, PERIOD_NAME
2078: ================
2079: 1:SELECT STATEMENT :(cost=8,rows=2)
2080: 2:SORT ORDER BY :(cost=8,rows=2)
2081: 3:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=2)
2082: 4:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=2)
2083: **/
2084: CURSOR get_gl_periods_csr IS
2085: SELECT START_DATE, END_DATE, PERIOD_NAME
2086: FROM gl_periods gp
2080: 2:SORT ORDER BY :(cost=8,rows=2)
2081: 3:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=2)
2082: 4:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=2)
2083: **/
2084: CURSOR get_gl_periods_csr IS
2085: SELECT START_DATE, END_DATE, PERIOD_NAME
2086: FROM gl_periods gp
2087: WHERE gp.period_set_name = l_period_set_name
2088: AND gp.period_type = decode(l_time_phase_code,'G',l_accounted_period_type,'P',l_pa_period_type)
2082: 4:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=2)
2083: **/
2084: CURSOR get_gl_periods_csr IS
2085: SELECT START_DATE, END_DATE, PERIOD_NAME
2086: FROM gl_periods gp
2087: WHERE gp.period_set_name = l_period_set_name
2088: AND gp.period_type = decode(l_time_phase_code,'G',l_accounted_period_type,'P',l_pa_period_type)
2089: AND gp.adjustment_period_flag = 'N'
2090: AND gp.start_date <= l_end_date -- plan end date
2096: EXPLAIN PLAN IS:
2097: ================
2098: 1:SELECT STATEMENT :(cost=8,rows=1)
2099: 2:FILTER :(cost=,rows=)
2100: 3:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=8,rows=1)
2101: 4:INDEX RANGE SCAN GL_PERIODS_N1 :(cost=2,rows=1)
2102: 5:SORT AGGREGATE :(cost=,rows=1)
2103: 6:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=4)
2104: 7:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=4)
2097: ================
2098: 1:SELECT STATEMENT :(cost=8,rows=1)
2099: 2:FILTER :(cost=,rows=)
2100: 3:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=8,rows=1)
2101: 4:INDEX RANGE SCAN GL_PERIODS_N1 :(cost=2,rows=1)
2102: 5:SORT AGGREGATE :(cost=,rows=1)
2103: 6:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=4)
2104: 7:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=4)
2105: 3:SORT AGGREGATE :(cost=,rows=1)
2099: 2:FILTER :(cost=,rows=)
2100: 3:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=8,rows=1)
2101: 4:INDEX RANGE SCAN GL_PERIODS_N1 :(cost=2,rows=1)
2102: 5:SORT AGGREGATE :(cost=,rows=1)
2103: 6:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=4)
2104: 7:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=4)
2105: 3:SORT AGGREGATE :(cost=,rows=1)
2106: 4:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=5)
2107: 5:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=5)
2100: 3:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=8,rows=1)
2101: 4:INDEX RANGE SCAN GL_PERIODS_N1 :(cost=2,rows=1)
2102: 5:SORT AGGREGATE :(cost=,rows=1)
2103: 6:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=4)
2104: 7:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=4)
2105: 3:SORT AGGREGATE :(cost=,rows=1)
2106: 4:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=5)
2107: 5:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=5)
2108: CURSOR get_gl_periods_csr IS
2102: 5:SORT AGGREGATE :(cost=,rows=1)
2103: 6:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=4)
2104: 7:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=4)
2105: 3:SORT AGGREGATE :(cost=,rows=1)
2106: 4:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=5)
2107: 5:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=5)
2108: CURSOR get_gl_periods_csr IS
2109: SELECT START_DATE, END_DATE, PERIOD_NAME
2110: FROM gl_periods gp
2103: 6:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=4)
2104: 7:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=4)
2105: 3:SORT AGGREGATE :(cost=,rows=1)
2106: 4:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=5)
2107: 5:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=5)
2108: CURSOR get_gl_periods_csr IS
2109: SELECT START_DATE, END_DATE, PERIOD_NAME
2110: FROM gl_periods gp
2111: WHERE gp.period_set_name = l_period_set_name
2104: 7:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=4)
2105: 3:SORT AGGREGATE :(cost=,rows=1)
2106: 4:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=5)
2107: 5:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=5)
2108: CURSOR get_gl_periods_csr IS
2109: SELECT START_DATE, END_DATE, PERIOD_NAME
2110: FROM gl_periods gp
2111: WHERE gp.period_set_name = l_period_set_name
2112: AND gp.period_type = decode(l_time_phase_code,'G',l_accounted_period_type,'P',l_pa_period_type)
2106: 4:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=5)
2107: 5:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=5)
2108: CURSOR get_gl_periods_csr IS
2109: SELECT START_DATE, END_DATE, PERIOD_NAME
2110: FROM gl_periods gp
2111: WHERE gp.period_set_name = l_period_set_name
2112: AND gp.period_type = decode(l_time_phase_code,'G',l_accounted_period_type,'P',l_pa_period_type)
2113: AND gp.start_date >=
2114: (SELECT MIN(start_date)
2111: WHERE gp.period_set_name = l_period_set_name
2112: AND gp.period_type = decode(l_time_phase_code,'G',l_accounted_period_type,'P',l_pa_period_type)
2113: AND gp.start_date >=
2114: (SELECT MIN(start_date)
2115: FROM gl_periods
2116: WHERE end_date >= l_start_date
2117: AND period_set_name = l_period_set_name
2118: AND period_type =
2119: decode(l_time_phase_code,'G',
2121: 'P',l_pa_period_type)
2122: AND adjustment_period_flag = 'N')
2123: AND gp.end_date <=
2124: (SELECT MAX(end_date)
2125: FROM gl_periods
2126: WHERE start_date <= l_end_date
2127: AND period_set_name = l_period_set_name
2128: AND period_type =
2129: decode(l_time_phase_code,'G',
2179: print_msg(' '||'time_phase_code => '||l_time_phase_code);
2180: End If;
2181:
2182:
2183: -- Get periods from gl_periods
2184:
2185: l_start_date := p_start_date;
2186: l_end_date := p_end_date;
2187: x_spread_amounts := spread_table_type();
2185: l_start_date := p_start_date;
2186: l_end_date := p_end_date;
2187: x_spread_amounts := spread_table_type();
2188: n := 0;
2189: FOR rec IN get_gl_periods_csr
2190: LOOP
2191: --print_msg('inside get_gl_periods_csr for SD['||rec.start_date||']');
2192: n := n + 1;
2193: x_spread_amounts.EXTEND();
2187: x_spread_amounts := spread_table_type();
2188: n := 0;
2189: FOR rec IN get_gl_periods_csr
2190: LOOP
2191: --print_msg('inside get_gl_periods_csr for SD['||rec.start_date||']');
2192: n := n + 1;
2193: x_spread_amounts.EXTEND();
2194: tmp_rec.start_date := rec.start_date;
2195: tmp_rec.end_date := rec.end_date;