[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_QUERY_TIME
Source
1 PACKAGE BODY HRI_OLTP_PMV_QUERY_TIME AS
2 /* $Header: hriopqtm.pkb 120.1 2005/11/22 03:22:40 cbridge noship $ */
3 /*----------------------------------------------------------------------------*/
4 /* Procedure generates the in-line view of periods, utilized by the DBI trend
5 /* reports. All Variables are passed by the AK SQL,
6 /*----------------------------------------------------------------------------*/
7
8 PROCEDURE GET_TIME_CLAUSE(
9 p_projection_type VARCHAR2 DEFAULT 'N'
10 ,p_page_period_type VARCHAR2
11 ,p_page_comp_type VARCHAR2
12 ,o_trend_table OUT NOCOPY VARCHAR2
13 ,o_previous_periods OUT NOCOPY VARCHAR2
14 ,o_projection_periods OUT NOCOPY VARCHAR2
15 )
16 IS
17
18 BEGIN
19
20 -- projection support is required for Budgeting report
21 IF (p_projection_type = 'Y') THEN
22
23 IF (p_page_period_type = 'FII_ROLLING_YEAR') THEN
24 -- 5 periods ( 3 previous, current, 1 projected)
25 o_previous_periods := 4;
26 o_projection_periods := 1;
27 --
28 ELSIF (p_page_period_type = 'FII_ROLLING_QTR') THEN
29 -- 7 peiods ( 3 (4) previous, current, 3 projected)
30 o_previous_periods := 7;
31 o_projection_periods := 3;
32 ELSIF (p_page_period_type = 'FII_ROLLING_MONTH') THEN
33 -- 12 periods (6 previous, current, 5 projected)
34 o_previous_periods := 11;
35 o_projection_periods := 3;
36 --
37 ELSIF (p_page_period_type = 'FII_ROLLING_WEEK') THEN
38 -- 13 seven day periods (12 previous, current, 6 projected)
39 o_previous_periods :=12;
40 o_projection_periods :=4;
41 --
42 ELSE
43 --
44 o_previous_periods :=0;
45 o_projection_periods :=0;
46 --
47 END IF;
48 --
49
50 -- add a projection amount (days) to the offsets
51 --Template Query
52 --Frist Select returns previous and current periods.
53 -- Second Select return's projected periods
54 o_trend_table :=
55 ' (SELECT
56 TO_CHAR (' || '&' || 'BIS_CURRENT_ASOF_DATE + tro.offset, '''|| 'dd-Mon-YYYY' ||''')
57 value
58 ,' || '&' || 'BIS_CURRENT_ASOF_DATE + tro.offset period_as_of_date
59 ,' || '&' || 'BIS_PREVIOUS_ASOF_DATE + tro.offset prev_period_as_of_date
60 ,' || '&' || 'BIS_CURRENT_ASOF_DATE + (tro.offset + tro.start_date_offset)
61 period_start_date
62 ,' || '&' || 'BIS_CURRENT_ASOF_DATE + tro.offset period_end_date
63 ,tro.period_number period_number
64 ,-tro.period_number period_order
65 FROM
66 fii_time_rolling_offsets tro
67 WHERE
68 tro.period_type = :TIME_PERIOD_TYPE
69 AND tro.comparison_type = :TIME_COMPARISON_TYPE
70 AND tro.period_number <= :TIME_PERIOD_NUMBER
71 UNION ALL
72 SELECT
73 TO_CHAR (' || '&' || 'BIS_CURRENT_ASOF_DATE - tro.offset, '''|| 'dd-Mon-YYYY' ||''')
74 value
75 ,' || '&' || 'BIS_CURRENT_ASOF_DATE - tro.offset period_as_of_date
76 ,' || '&' || 'BIS_PREVIOUS_ASOF_DATE - tro.offset prev_period_as_of_date
77 ,' || '&' || 'BIS_CURRENT_ASOF_DATE - (tro.offset - tro.start_date_offset)
78 period_start_date
79 ,' || '&' || 'BIS_CURRENT_ASOF_DATE - tro.offset period_end_date
80 ,tro.period_number period_number
81 ,tro.period_number period_order
82 FROM
83 fii_time_rolling_offsets tro
84 WHERE
85 tro.period_type = :TIME_PROJECT_PERIOD_TYPE
86 AND tro.comparison_type = :TIME_PROJECT_COMPARISON_TYPE
87 AND tro.period_number <= :TIME_PROJECT_PERIOD_NUMBER
88 AND tro.period_number <> 0
89 )
90 ';
91 ELSE -- not a projection type trend.
92 IF (p_page_period_type = 'FII_ROLLING_YEAR') THEN
93 -- 5 periods ( 3 previous, current, 0 projected)
94 o_previous_periods := 3;
95 --
96 ELSIF (p_page_period_type = 'FII_ROLLING_QTR') THEN
97 --
98 IF (p_page_comp_type = 'SEQUENTIAL') THEN
99 -- 8 periods (7 previous, current, 0 projected)
100 o_previous_periods := 7;
101 --
102 ELSIF (p_page_comp_type = 'YEARLY') THEN
103 -- 8 periods (3 previous, current, 0 projected) -
104 o_previous_periods := 3;
105 --
106 END If;
107 --
108 ELSIF (p_page_period_type = 'FII_ROLLING_MONTH') THEN
109 -- 12 periods (6 previous, current, 0 projected)
110 o_previous_periods := 11;
111 --
112 ELSIF (p_page_period_type = 'FII_ROLLING_WEEK') THEN
113 -- 13 seven day periods (6 previous, current, 0 projected)
114 o_previous_periods := 12;
115 --
116 ELSE
117 o_previous_periods := 0;
118 END IF;
119 -- offsets table without projection periods (default)
120 o_trend_table :=
121 ' (SELECT
122 TO_CHAR (' || '&' || 'BIS_CURRENT_ASOF_DATE + tro.offset, '''|| 'dd-Mon-YYYY' ||''')
123 value
124 ,' || '&' || 'BIS_CURRENT_ASOF_DATE + tro.offset period_as_of_date
125 ,' || '&' || 'BIS_PREVIOUS_ASOF_DATE + tro.offset prev_period_as_of_date
126 ,' || '&' || 'BIS_CURRENT_ASOF_DATE + (tro.offset + tro.start_date_offset)
127 period_start_date
128 ,' || '&' || 'BIS_CURRENT_ASOF_DATE + tro.offset period_end_date
129 ,tro.period_number period_number
130 ,-tro.period_number period_order
131 FROM
132 fii_time_rolling_offsets tro
133 WHERE
134 tro.period_type = :TIME_PERIOD_TYPE
135 AND tro.comparison_type = :TIME_COMPARISON_TYPE
136 AND tro.period_number <= :TIME_PERIOD_NUMBER
137 )
138 ';
139 END IF;
140
141 END GET_TIME_CLAUSE ; -- GET_TIME_CLAUSE PROCEDURE
142
143 --
144 -- -------------------------------------------------------------------------
145 -- Get the trend periods only
146 -- -------------------------------------------------------------------------
147 --
148 FUNCTION get_time_clause(p_past_trend IN VARCHAR2 DEFAULT 'Y',
149 p_future_trend IN VARCHAR2 DEFAULT 'N')
150 RETURN VARCHAR2 IS
151 --
152 l_past_trend_sql VARCHAR2(1000);
153 l_future_trend_sql VARCHAR2(1000);
154 l_trend_sql VARCHAR2(1000);
155 --
156 BEGIN
157 --
158 l_past_trend_sql :=
159 'SELECT /*+ NOPARALLEL(toff) */
160 TO_CHAR(&BIS_CURRENT_ASOF_DATE + offset, '''|| 'dd-Mon-YYYY' ||''') value
161 ,&BIS_CURRENT_ASOF_DATE + offset period_as_of_date
162 ,&BIS_PREVIOUS_ASOF_DATE + offset prev_period_as_of_date
163 ,&BIS_CURRENT_ASOF_DATE + (offset + start_date_offset) period_start_date
164 ,&BIS_CURRENT_ASOF_DATE + offset period_end_date
165 ,period_number period_number
166 ,-period_number period_order
167 FROM
168 fii_time_rolling_offsets toff
169 WHERE period_type = :TIME_PERIOD_TYPE
170 AND comparison_type = :TIME_COMPARISON_TYPE
171 AND period_number <= :TIME_PERIOD_NUMBER';
172 --
173 l_future_trend_sql :=
174 'SELECT
175 TO_CHAR(&BIS_CURRENT_ASOF_DATE - offset, '''|| 'dd-Mon-YYYY' ||''') value
176 ,&BIS_CURRENT_ASOF_DATE - offset period_as_of_date
177 ,&BIS_PREVIOUS_ASOF_DATE - offset prev_period_as_of_date
178 ,&BIS_CURRENT_ASOF_DATE - (offset - start_date_offset) period_start_date
179 ,&BIS_CURRENT_ASOF_DATE - offset period_end_date
180 ,period_number period_number
181 ,period_number period_order
182 FROM fii_time_rolling_offsets
183 WHERE period_type = :TIME_PROJECT_PERIOD_TYPE
184 AND comparison_type = :TIME_PROJECT_COMPARISON_TYPE
185 AND period_number <= :TIME_PROJECT_PERIOD_NUMBER
186 AND period_number <> 0';
187 --
188 IF p_past_trend = 'Y' THEN
189 --
190 l_trend_sql := l_past_trend_sql;
191 --
192 IF p_future_trend = 'Y' THEN
193 --
194 l_trend_sql :=
195 l_trend_sql ||'
196 UNION ALL
197 ' || l_future_trend_sql;
198 --
199 END IF;
200 --
201 ELSIF p_future_trend = 'Y' THEN
202 --
203 l_trend_sql := l_future_trend_sql;
204 --
205 END IF;
206 --
207 RETURN l_trend_sql;
208 --
209 END get_time_clause;
210
211 PROCEDURE get_period_binds
212 (p_projection_type VARCHAR2 DEFAULT 'N'
213 ,p_page_period_type VARCHAR2
214 ,p_page_comp_type VARCHAR2
215 ,o_previous_periods OUT NOCOPY NUMBER
216 ,o_projection_periods OUT NOCOPY NUMBER) IS
217
218 BEGIN
219
220 -- projection support is required for Budgeting report
221 IF (p_projection_type = 'Y') THEN
222
223 IF (p_page_period_type = 'FII_ROLLING_YEAR') THEN
224 -- 5 periods ( 3 previous, current, 1 projected)
225 o_previous_periods := 4;
226 o_projection_periods := 1;
227 --
228 ELSIF (p_page_period_type = 'FII_ROLLING_QTR') THEN
229 -- 7 peiods ( 3 (4) previous, current, 3 projected)
230 o_previous_periods := 7;
231 o_projection_periods := 3;
232 ELSIF (p_page_period_type = 'FII_ROLLING_MONTH') THEN
233 -- 12 periods (6 previous, current, 5 projected)
234 o_previous_periods := 11;
235 o_projection_periods := 3;
236 --
237 ELSIF (p_page_period_type = 'FII_ROLLING_WEEK') THEN
238 -- 13 seven day periods (12 previous, current, 6 projected)
239 o_previous_periods :=12;
240 o_projection_periods :=4;
241 --
242 ELSE
243 --
244 o_previous_periods :=0;
245 o_projection_periods :=0;
246 --
247 END IF;
248 --
249
250 ELSE -- not a projection type trend.
251 IF (p_page_period_type = 'FII_ROLLING_YEAR') THEN
252 -- 4 periods ( 3 previous, current, 0 projected)
253 o_previous_periods := 3;
254 --
255 ELSIF (p_page_period_type = 'FII_ROLLING_QTR') THEN
256 --
257 IF (p_page_comp_type = 'SEQUENTIAL') THEN
258 -- 8 periods (7 previous, current, 0 projected)
259 o_previous_periods := 7;
260 --
261 ELSIF (p_page_comp_type = 'YEARLY') THEN
262 -- 4 periods (3 previous, current, 0 projected) -
263 o_previous_periods := 3;
264 --
265 END If;
266 --
267 ELSIF (p_page_period_type = 'FII_ROLLING_MONTH') THEN
268 -- 12 periods (11 previous, current, 0 projected)
269 o_previous_periods := 11;
270 --
271 ELSIF (p_page_period_type = 'FII_ROLLING_WEEK') THEN
272 -- 13 seven day periods (12 previous, current, 0 projected)
273 o_previous_periods := 12;
274 --
275 ELSE
276 o_previous_periods := 0;
277 END IF;
278 END IF;
279
280 END get_period_binds;
281
282 END HRI_OLTP_PMV_QUERY_TIME;
283