[Home] [Help]
PACKAGE BODY: APPS.FII_PSI_BUD_ENC_DETAIL_PKG
Source
1 PACKAGE BODY FII_PSI_BUD_ENC_DETAIL_PKG AS
2 /* $Header: FIIPSIBEDTLB.pls 120.4 2006/09/14 05:48:59 sajgeo noship $ */
3
4 ---------------------------------------------------------------------------------
5 -- This procedure is called by the Budget Trend by Account Detail report.
6 ---------------------------------------------------------------------------------
7 PROCEDURE get_bud_trend_dtl (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
8 p_exp_trend_dtl_sql out NOCOPY VARCHAR2,
9 p_exp_trend_dtl_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
10
11 IS
12 l_fin_cat VARCHAR2(10);
13 l_trend_type VARCHAR2(10);
14 l_sqlstmt VARCHAR2(25000);
15
16 BEGIN
17
18 -- In the Budget Trend by Account Detail report, we will only query up Expenses
19 -- data for budget/base budget.
20 l_fin_cat := 'OE';
21 l_trend_type := 'B';
22
23 l_sqlstmt := get_bud_enc_trend_dtl (p_page_parameter_tbl => p_page_parameter_tbl,
24 p_fin_cat => l_fin_cat,
25 p_trend_type => l_trend_type);
26
27 fii_ea_util_pkg.bind_variable(p_sqlstmt => l_sqlstmt,
28 p_page_parameter_tbl => p_page_parameter_tbl,
29 p_sql_output => p_exp_trend_dtl_sql,
30 p_bind_output_table => p_exp_trend_dtl_output);
31
32 END get_bud_trend_dtl;
33
34 ---------------------------------------------------------------------------------
35 -- This procedure is called by the Encumbrance Trend by Account Detail report.
36 ---------------------------------------------------------------------------------
37 PROCEDURE get_enc_trend_dtl (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
38 p_rev_trend_dtl_sql out NOCOPY VARCHAR2,
39 p_rev_trend_dtl_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
40
41 IS
42 l_fin_cat VARCHAR2(10);
43 l_trend_type VARCHAR2(10);
44 l_sqlstmt VARCHAR2(25000);
45
46 BEGIN
47
48 -- In the Encumbrance Trend by Account Detail report, we will only query up Expenses
49 -- data for encumbrances
50 l_fin_cat := 'OE';
51 l_trend_type := 'E';
52
53 l_sqlstmt := get_bud_enc_trend_dtl (p_page_parameter_tbl => p_page_parameter_tbl,
54 p_fin_cat => l_fin_cat,
55 p_trend_type => l_trend_type);
56
57 fii_ea_util_pkg.bind_variable(p_sqlstmt => l_sqlstmt,
58 p_page_parameter_tbl => p_page_parameter_tbl,
59 p_sql_output => p_rev_trend_dtl_sql,
60 p_bind_output_table => p_rev_trend_dtl_output);
61
62 END get_enc_trend_dtl;
63
64 ---------------------------------------------------------------------------------
65 -- This is the main function which constructs the PMV sql.
66
67 FUNCTION get_bud_enc_trend_dtl ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
68 p_fin_cat IN VARCHAR2,
69 p_trend_type IN VARCHAR2) RETURN VARCHAR2 IS
70
71 get_bud_enc_trend_dtl BIS_QUERY_ATTRIBUTES;
72 l_sqlstmt VARCHAR2(25000);
73 l_sqlstmt2 VARCHAR2(1000);
74 l_as_of_date DATE;
75 l_page_period_type VARCHAR2(100);
76
77 l_company_id VARCHAR2(30);
78 l_company_id_from VARCHAR2(300);
79 l_company_id_where VARCHAR2(1000);
80
81 l_cost_center_id VARCHAR2(30);
82 l_curr_view VARCHAR2(4);
83 l_ledger_where VARCHAR2(500);
84 l_fin_category_id VARCHAR2(30);
85 l_fin_category_id_from VARCHAR2(100);
86 l_fin_category_id_where VARCHAR2(240);
87
88 l_fud1_where VARCHAR2(240);
89 l_fud2_where VARCHAR2(240);
90 l_amt_columns VARCHAR2(15000);
91 l_amt_where VARCHAR2(240);
92
93 l_months NUMBER;
94 l_year_id NUMBER;
95 l_sequence NUMBER;
96 l_prior_year_id NUMBER;
97 xtd VARCHAR2(200);
98 hist_amt1 VARCHAR2(200);
99 hist_amt2 VARCHAR2(200);
100 hist_amt3 VARCHAR2(200);
101 hist_amt4 VARCHAR2(200);
102 p_hist_amt1 VARCHAR2(200);
103 p_hist_amt2 VARCHAR2(200);
104 p_hist_amt3 VARCHAR2(200);
105 l_prior_year_sqlstmt VARCHAR2(15000);
106 l_prior_year_sqlstmt2 VARCHAR2(500);
107 l_prior_year_amt VARCHAR2(2000);
108
109 l_period VARCHAR2(50);
110 l_url_common VARCHAR2(200);
111 l_url1 VARCHAR2(500);
112 l_url2 VARCHAR2(500);
113 l_url3 VARCHAR2(500);
114 l_url4 VARCHAR2(500);
115 l_url5 VARCHAR2(500);
116 l_as_of_date_2 VARCHAR2(50);
117 l_previous_three_end_date DATE;
118 l_previous_two_end_date DATE;
119 l_previous_one_end_date DATE;
120 l_previous_three_end_date_2 VARCHAR2(50);
121 l_previous_two_end_date_2 VARCHAR2(50);
122 l_previous_one_end_date_2 VARCHAR2(50);
123 l_child_cc_id NUMBER(15);
124 l_child_company_id NUMBER(15);
125 l_having_clause VARCHAR2(250);
126 l_order VARCHAR2(500);
127 l_order2 VARCHAR2(2500);
128
129 BEGIN
130 -- initialization. Calling fii_ea_util_pkg APIs necessary for constructing
131 -- the PMV sql.
132
133 fii_ea_util_pkg.reset_globals;
134 fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);
135 fii_ea_util_pkg.get_rolling_period;
136 l_ledger_where := fii_ea_util_pkg.get_ledger_for_detail;
137 l_as_of_date := fii_ea_util_pkg.g_as_of_date;
138 l_page_period_type := fii_ea_util_pkg.g_page_period_type;
139
140 l_company_id := fii_ea_util_pkg.g_company_id;
141 l_cost_center_id := fii_ea_util_pkg.g_cost_center_id;
142 l_curr_view := fii_ea_util_pkg.g_curr_view;
143 l_fin_category_id := fii_ea_util_pkg.g_fin_category_id;
144 l_fud1_where := fii_ea_util_pkg.get_fud1_for_detail;
145 l_fud2_where := fii_ea_util_pkg.get_fud2_for_detail;
146
147 l_previous_three_end_date := fii_ea_util_pkg.g_previous_three_end_date;
148 l_previous_two_end_date := fii_ea_util_pkg.g_previous_two_end_date;
149 l_previous_one_end_date := fii_ea_util_pkg.g_previous_one_end_date;
150
151
152 -- order by clause
153 IF (p_page_parameter_tbl.count > 0) THEN
154 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
155 IF p_page_parameter_tbl(i).parameter_name = 'ORDERBY' THEN
156 l_order := p_page_parameter_tbl(i).parameter_value;
157 END IF;
158 END LOOP;
159 END IF;
160
161
162 CASE
163 WHEN (INSTR(l_order,'FII_PSI_COL_COMPANY')>0 and INSTR(l_order,'ASC')>0) THEN
164 l_order2 := 'NLSSORT(com_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
165 NLSSORT(cc_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
166 NLSSORT(fin_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
167 sum(FII_PSI_XTD) DESC';
168 WHEN (INSTR(l_order,'FII_PSI_COL_COMPANY')>0 and INSTR(l_order,'DESC')>0) THEN
169 l_order2 := 'NLSSORT(com_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') DESC,
170 NLSSORT(cc_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
171 NLSSORT(fin_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
172 sum(FII_PSI_XTD) DESC';
173 WHEN (INSTR(l_order,'FII_PSI_COL_COST_CENTER')>0 and INSTR(l_order,'ASC')>0) THEN
174 l_order2 := 'NLSSORT(cc_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
175 NLSSORT(com_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
176 NLSSORT(fin_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
177 sum(FII_PSI_XTD) DESC';
178 WHEN (INSTR(l_order,'FII_PSI_COL_COST_CENTER')>0 and INSTR(l_order,'DESC')>0) THEN
179 l_order2 := 'NLSSORT(cc_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') DESC,
180 NLSSORT(com_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
181 NLSSORT(fin_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
182 sum(FII_PSI_XTD) DESC';
183 WHEN (INSTR(l_order,'FII_PSI_COL_FIN_CAT')>0 and INSTR(l_order,'ASC')>0) THEN
184 l_order2 := 'NLSSORT(fin_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
185 NLSSORT(com_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
186 NLSSORT(cc_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
187 sum(FII_PSI_XTD) DESC';
188 WHEN (INSTR(l_order,'FII_PSI_COL_FIN_CAT')>0 and INSTR(l_order,'DESC')>0) THEN
189 l_order2 := 'NLSSORT(fin_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') DESC,
190 NLSSORT(com_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
191 NLSSORT(cc_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
192 sum(FII_PSI_XTD) DESC';
193 WHEN (INSTR(l_order,'FII_PSI_XTD')>0 and INSTR(l_order,'ASC')>0) THEN
194 l_order2 := 'sum(FII_PSI_XTD) ASC,
195 NLSSORT(com_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
196 NLSSORT(cc_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
197 NLSSORT(fin_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC';
198 WHEN (INSTR(l_order,'FII_PSI_XTD')>0 and INSTR(l_order,'DESC')>0) THEN
199 l_order2 := 'sum(FII_PSI_XTD) DESC,
200 NLSSORT(com_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
201 NLSSORT(cc_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
202 NLSSORT(fin_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC';
203 WHEN (INSTR(l_order,'FII_PSI_BUD_ADJ')>0 and INSTR(l_order,'ASC')>0) THEN
204 l_order2 := '(sum(FII_PSI_XTD) - sum(FII_PSI_BUD_ORIG)) ASC,
205 NLSSORT(com_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
206 NLSSORT(cc_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
207 NLSSORT(fin_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC';
208 WHEN (INSTR(l_order,'FII_PSI_BUD_ADJ')>0 and INSTR(l_order,'DESC')>0) THEN
209 l_order2 := '(sum(FII_PSI_XTD) - sum(FII_PSI_BUD_ORIG)) DESC,
210 NLSSORT(com_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
211 NLSSORT(cc_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
212 NLSSORT(fin_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC';
213 WHEN (INSTR(l_order,'FII_PSI_BUD_ORIG')>0 and INSTR(l_order,'ASC')>0) THEN
214 l_order2 := 'sum(FII_PSI_BUD_ORIG) ASC,
215 NLSSORT(com_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
216 NLSSORT(cc_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
217 NLSSORT(fin_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC';
218 WHEN (INSTR(l_order,'FII_PSI_BUD_ORIG')>0 and INSTR(l_order,'DESC')>0) THEN
219 l_order2 := 'sum(FII_PSI_BUD_ORIG) DESC,
220 NLSSORT(com_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
221 NLSSORT(cc_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC,
222 NLSSORT(fin_flex.FLEX_VALUE_MEANING, ''NLS_SORT= BINARY'') ASC';
223 END CASE;
224
225
226 -- cost center dimension top node. l_child_cc_id will be used when
227 -- ((l_company_id <> 'All') AND (l_cost_center_id = 'All'))
228
229 SELECT dbi_hier_top_node_id INTO l_child_cc_id
230 FROM fii_financial_dimensions
231 WHERE DIMENSION_SHORT_NAME = 'HRI_CL_ORGCC';
232
233
234 -- company dimension top node. l_child_company_id will be used when
235 -- ((l_company_id = 'All') AND (l_cost_center_id <> 'All'))
236
237 SELECT dbi_hier_top_node_id INTO l_child_company_id
238 FROM fii_financial_dimensions
239 WHERE DIMENSION_SHORT_NAME = 'FII_COMPANIES';
240
241 -- from clauses and where clauses for different combinations of
242 -- Company and Cost center parameters
243
244 IF ((l_company_id <> 'All') AND (l_cost_center_id = 'All')) THEN
245 l_company_id_from := ', fii_cost_center_grants ccg
246 , fii_company_hierarchies co_hier
247 , fii_cost_ctr_hierarchies cc_hier
248 , fii_com_cc_dim_maps ccom_map ';
249 l_company_id_where := ' and co_hier.parent_company_id = :COMPANY_ID
250 and co_hier.child_company_id = ccom_map.parent_company_dim_id
251 and ccom_map.child_company_id = f.company_id
252 and ccg.user_id = fnd_global.user_id
253 and ccg.report_region_code = '''||fii_ea_util_pkg.g_region_code||'''
254 and cc_hier.parent_cc_id = ccg.cost_center_id
255 and cc_hier.child_cc_id = ccom_map.parent_cost_center_dim_id
256 and ccom_map.child_cost_center_id = f.cost_center_id';
257 ELSIF ((l_company_id = 'All') AND (l_cost_center_id <> 'All')) THEN
258 l_company_id_from := ', fii_company_grants fcg
259 , fii_company_hierarchies co_hier
260 , fii_cost_ctr_hierarchies cc_hier
261 , fii_com_cc_dim_maps ccom_map ';
262 l_company_id_where := ' and fcg.user_id = fnd_global.user_id
263 and fcg.report_region_code = '''||fii_ea_util_pkg.g_region_code||'''
264 and co_hier.parent_company_id = fcg.company_id
265 and co_hier.child_company_id = ccom_map.parent_company_dim_id
266 and ccom_map.child_company_id = f.company_id
267 and cc_hier.parent_cc_id = :COST_CENTER_ID
268 and cc_hier.child_cc_id = ccom_map.parent_cost_center_dim_id
269 and ccom_map.child_cost_center_id = f.cost_center_id';
270 ELSIF ((l_company_id <> 'All') AND (l_cost_center_id <> 'All')) THEN
271 l_company_id_from := ', fii_company_hierarchies co_hier
272 , fii_cost_ctr_hierarchies cc_hier
273 , fii_com_cc_dim_maps ccom_map ';
274 l_company_id_where := ' and co_hier.parent_company_id = :COMPANY_ID
275 and co_hier.child_company_id = ccom_map.parent_company_dim_id
276 and ccom_map.child_company_id = f.company_id
277 and cc_hier.parent_cc_id = :COST_CENTER_ID
278 and cc_hier.child_cc_id = ccom_map.parent_cost_center_dim_id
279 and ccom_map.child_cost_center_id = f.cost_center_id';
280 ELSIF ((l_company_id = 'All') AND (l_cost_center_id = 'All')) THEN
281 l_company_id_from := ', fii_company_grants fcg
282 , fii_cost_center_grants ccg
283 , fii_company_hierarchies co_hier
284 , fii_cost_ctr_hierarchies cc_hier
285 , fii_com_cc_dim_maps ccom_map ';
286 l_company_id_where := ' and fcg.user_id = fnd_global.user_id
287 and fcg.report_region_code = '''||fii_ea_util_pkg.g_region_code||'''
288 and co_hier.parent_company_id = fcg.company_id
289 and fcg.user_id = ccg.user_id
290 and fcg.report_region_code = ccg.report_region_code
291 and co_hier.child_company_id = ccom_map.parent_company_dim_id
292 and ccom_map.child_company_id = f.company_id
293 and cc_hier.parent_cc_id = ccg.cost_center_id
294 and cc_hier.child_cc_id = ccom_map.parent_cost_center_dim_id
295 and ccom_map.child_cost_center_id = f.cost_center_id';
296 END IF;
297
298
299
300 -- from clauses and where clauses for category parameter
301
302 IF (l_fin_category_id IS NOT NULL AND l_fin_category_id <> 'All') THEN
303 l_fin_category_id_from := ', fii_full_fin_item_hiers fin_hier';
304 l_fin_category_id_where := ' and fin_hier.parent_fin_cat_id = :FIN_CATEGORY_ID
305 and fin_hier.child_fin_cat_id = f.fin_category_id';
306 ELSE
307 l_fin_category_id_from := '';
308 l_fin_category_id_where := '';
309 END IF;
310
311
312 -- constructing urls
313
314 CASE l_page_period_type
315
316 WHEN 'FII_TIME_ENT_YEAR' THEN
317 l_period := '&FII_TIME_ENT_QTR=TIME+FII_TIME_ENT_QTR';
318
319 WHEN 'FII_TIME_ENT_QTR' THEN
320 l_period := '&FII_TIME_ENT_PERIOD=TIME+FII_TIME_ENT_PERIOD';
321
322 WHEN 'FII_TIME_ENT_PERIOD' THEN
323 l_period := null;
324
325 END CASE;
326
327 SELECT to_char(end_date, 'DD/MM/YYYY') INTO l_as_of_date_2
328 FROM fii_time_ent_period
329 WHERE TRUNC(l_as_of_date) BETWEEN start_date AND end_date;
330
331 l_previous_three_end_date_2 := to_char(l_previous_three_end_date,'DD/MM/YYYY');
332 l_previous_two_end_date_2 := to_char(l_previous_two_end_date,'DD/MM/YYYY');
333 l_previous_one_end_date_2 := to_char(l_previous_one_end_date,'DD/MM/YYYY');
334
335 -- Setting the report name for drilldown
336 IF p_trend_type = 'B' THEN
337 l_url_common := '&pFunctionName=FII_PSI_BUDGET_JE_DTL&FII_COMPANIES=FII_PSI_COL_COMPANY_ID&HRI_CL_ORGCC=FII_PSI_COL_COST_CENTER_ID&GL_FII_FIN_ITEM=FII_PSI_COL_FIN_CAT_ID&pParamIds=Y';
338 ELSE
339 l_url_common := '&pFunctionName=FII_PSI_ENCUM_JE_DTL&FII_COMPANIES=FII_PSI_COL_COMPANY_ID&HRI_CL_ORGCC=FII_PSI_COL_COST_CENTER_ID&GL_FII_FIN_ITEM=FII_PSI_COL_FIN_CAT_ID&pParamIds=Y';
340 END IF;
341
342 -- l_url1 for FII_PSI_XTD_DRILL,
343 -- l_url2 for FII_PSI_HIST_COL1_DRILL,
344 -- l_url3 for FII_PSI_HIST_COL2_DRILL,
345 -- l_url4 for FII_PSI_HIST_COL3_DRILL,
346 -- l_url5 for FII_PSI_HIST_COL4_DRILL,
347
348 -- if period=year then xtd = ytd and no drill-down on fii_psi_xtd column.
349
350 IF l_page_period_type = 'FII_TIME_ENT_YEAR' THEN
351 l_url1 := null;
352 ELSE
353 l_url1 := 'AS_OF_DATE='||l_as_of_date_2||l_url_common||'';
354 END IF;
355
356 -- if period=month then no rolling periods and no drill-downs on FII_PSI_HIST_COLn columns
357
358 IF l_page_period_type = 'FII_TIME_ENT_PERIOD' THEN
359 l_url2 := null;
360 l_url3 := null;
361 l_url4 := null;
362 l_url5 := null;
363 else
364 l_url2 := 'AS_OF_DATE='||l_previous_three_end_date_2||l_period||l_url_common||'';
365 l_url3 := 'AS_OF_DATE='||l_previous_two_end_date_2||l_period||l_url_common||'';
366 l_url4 := 'AS_OF_DATE='||l_previous_one_end_date_2||l_period||l_url_common||'';
367 l_url5 := 'AS_OF_DATE='||l_as_of_date_2||l_period||l_url_common||'';
368 end if;
369
370
371 -- l_having_clause - to ignore records where all columns in the report for a
372 -- particular row are null. If period_type = Month, only one column is
373 -- displayed. If th
374 IF l_page_period_type = 'FII_TIME_ENT_PERIOD' THEN
375 l_having_clause := ' HAVING (SUM(FII_PSI_XTD) IS NOT NULL) ';
376 ELSE
377 l_having_clause := ' HAVING (SUM(FII_PSI_XTD) IS NOT NULL OR SUM(FII_PSI_HIST_COL1) IS NOT NULL
378 OR SUM(FII_PSI_HIST_COL2) IS NOT NULL OR SUM(FII_PSI_HIST_COL3) IS NOT NULL
379 OR SUM(FII_PSI_HIST_COL4) IS NOT NULL) ';
380 END IF;
381
382
383
384
385 -- begin year
386 -- Populating xtd and hist_amt4 columns in the report when period_type = Year
387
388 IF l_page_period_type = 'FII_TIME_ENT_YEAR' THEN
389
390 -- to find the sequence of the month of as-of-date. Based on the sequence number
391 -- the xtd and hist_amt4 columns of the report will be populated.
392 SELECT SEQUENCE INTO l_months
393 FROM fii_time_ent_period WHERE l_as_of_date BETWEEN start_date AND end_date;
394
395 CASE l_months
396 WHEN 12 THEN
397 xtd := 'G_YEAR';
398 hist_amt4 := 'G_QTR4';
399 WHEN 11 THEN
400 xtd := 'decode(G_QTR1, null, 0, G_QTR1) + decode(G_QTR2, null, 0, G_QTR2) '||
401 ' + decode(G_QTR3, null, 0, G_QTR3) + decode(G_MONTH10, null, 0, G_MONTH10) '||
402 ' + decode(G_MONTH11, null, 0, G_MONTH1)';
403 hist_amt4 := 'decode(G_MONTH10, null, 0, G_MONTH10) + decode(G_MONTH11, null, 0, G_MONTH11)';
404 WHEN 10 THEN
405 xtd := 'decode(G_QTR1, null, 0, G_QTR1) + decode(G_QTR2, null, 0, G_QTR2) '||
406 '+ decode(G_QTR3, null, 0, G_QTR3) + decode(G_MONTH10, null, 0, G_MONTH10)';
407 hist_amt4 := 'G_MONTH10';
408 WHEN 9 THEN
409 xtd := 'decode(G_QTR1, null, 0, G_QTR1) + decode(G_QTR2, null, 0, G_QTR2) '||
410 '+ decode(G_QTR3, null, 0, G_QTR3)';
411 hist_amt4 := 'G_QTR3';
412 WHEN 8 THEN
413 xtd := 'decode(G_QTR1, null, 0, G_QTR1) + decode(G_QTR2, null, 0, G_QTR2) '||
414 '+ decode(G_MONTH7, null, 0, G_MONTH7) + decode(G_MONTH8, null, 0, G_MONTH8)';
415 hist_amt4 := 'decode(G_MONTH7, null, 0, G_MONTH7) + decode(G_MONTH8, null, 0, G_MONTH8)';
416 WHEN 7 THEN
417 xtd := 'decode(G_QTR1, null, 0, G_QTR1) + decode(G_QTR2, null, 0, G_QTR2) '||
418 '+ decode(G_MONTH7, null, 0, G_MONTH7)';
419 hist_amt4 := 'G_MONTH7';
420 WHEN 6 THEN
421 xtd := 'decode(G_QTR1, null, 0, G_QTR1) + decode(G_QTR2, null, 0, G_QTR2)';
422 hist_amt4 := 'G_QTR2';
423 WHEN 5 THEN
424 xtd := 'decode(G_QTR1, null, 0, G_QTR1) + decode(G_MONTH4, null, 0, G_MONTH4) '||
425 '+ decode(G_MONTH5, null, 0, G_MONTH5)';
426 hist_amt4 := 'DECODE(G_MONTH4, NULL, 0, G_MONTH4) + DECODE(G_MONTH5, NULL, 0, G_MONTH5)';
427 WHEN 4 THEN
428 xtd := 'decode(G_QTR1, null, 0, G_QTR1) + decode(G_MONTH4, null, 0, G_MONTH4)';
429 hist_amt4 := 'G_MONTH4';
430 WHEN 3 THEN
431 xtd := 'G_QTR1';
432 hist_amt4 := 'G_QTR1';
433 WHEN 2 THEN
434 xtd := 'decode(G_MONTH1, null, 0, G_MONTH1) + decode(G_MONTH2, null, 0, G_MONTH2)';
435 hist_amt4 := 'decode(G_MONTH1, null, 0, G_MONTH1) + decode(G_MONTH2, null, 0, G_MONTH2)';
436 WHEN 1 THEN
437 xtd := 'G_MONTH1';
438 hist_amt4 := 'G_MONTH1';
439 END CASE;
440
441 -- Populating hist_amt1, hist_amt2 and hist_amt3 columns in the report when period_type = Year
442 -- to find the sequence of the quarter of as-of-date. Based on the sequence number
443 -- the hist_amt1, hist_amt2 and hist_amt3 columns of the report will be populated.
444
445 SELECT ENT_YEAR_ID, SEQUENCE INTO l_year_id, l_sequence
446 FROM fii_time_ent_qtr WHERE l_as_of_date BETWEEN start_date AND end_date;
447 CASE l_sequence
448 WHEN 4 THEN
449 hist_amt1 := 'G_QTR1';
450 hist_amt2 := 'G_QTR2';
451 hist_amt3 := 'G_QTR3';
452 p_hist_amt1 := null;
453 p_hist_amt2 := null;
454 p_hist_amt3 := null;
455 l_prior_year_id := 0;
456
457 WHEN 3 THEN
458 hist_amt1 := null;
459 hist_amt2 := 'G_QTR1';
460 hist_amt3 := 'G_QTR2';
461 l_prior_year_id := l_year_id - 1;
462 p_hist_amt1 := 'G_QTR4';
463 p_hist_amt2 := null;
464 p_hist_amt3 := null;
465
466 WHEN 2 THEN
467 hist_amt1 := null;
468 hist_amt2 := null;
469 hist_amt3 := 'G_QTR1';
470 l_prior_year_id := l_year_id - 1;
471 p_hist_amt1 := 'G_QTR3';
472 p_hist_amt2 := 'G_QTR4';
473 p_hist_amt3 := null;
474
475 WHEN 1 THEN
476 hist_amt1 := null;
477 hist_amt2 := null;
478 hist_amt3 := null;
479 l_prior_year_id := l_year_id - 1;
480 p_hist_amt1 := 'G_QTR2';
481 p_hist_amt2 := 'G_QTR3';
482 p_hist_amt3 := 'G_QTR4';
483
484 END CASE;
485
486 -- begin quarter
487 -- Populating xtd and hist_amt4 columns in the report when period_type = Quarter
488
489 ELSIF l_page_period_type = 'FII_TIME_ENT_QTR' THEN
490
491 -- to find the sequence of the month of as-of-date. Based on the sequence number
492 -- the xtd and hist_amt4 columns of the report will be populated.
493 SELECT SEQUENCE INTO l_months
494 FROM fii_time_ent_period WHERE l_as_of_date BETWEEN start_date AND end_date;
495
496 CASE l_months
497 WHEN 12 THEN
498 xtd := 'G_QTR4';
499 hist_amt4 := 'G_MONTH12';
500 WHEN 11 THEN
501 xtd := 'decode(G_MONTH10, null, 0, G_MONTH10) + decode(G_MONTH11, null, 0, G_MONTH11)';
502 hist_amt4 := 'G_MONTH11';
503 WHEN 10 THEN
504 xtd := 'G_MONTH10';
505 hist_amt4 := 'G_MONTH10';
506 WHEN 9 THEN
507 xtd := 'G_QTR3';
508 hist_amt4 := 'G_MONTH9';
509 WHEN 8 THEN
510 xtd := 'decode(G_MONTH7, null, 0, G_MONTH7) + decode(G_MONTH8, null, 0, G_MONTH8)';
511 hist_amt4 := 'G_MONTH8';
512 WHEN 7 THEN
513 xtd := 'G_MONTH7';
514 hist_amt4 := 'G_MONTH7';
515 WHEN 6 THEN
516 xtd := 'G_QTR2';
517 hist_amt4 := 'G_MONTH6';
518 WHEN 5 THEN
519 xtd := 'decode(G_MONTH4, null, 0, G_MONTH4) + decode(G_MONTH5, null, 0, G_MONTH5)';
520 hist_amt4 := 'G_MONTH5';
521 WHEN 4 THEN
522 xtd := 'G_MONTH4';
523 hist_amt4 := 'G_MONTH4';
524 WHEN 3 THEN
525 xtd := 'G_QTR1';
526 hist_amt4 := 'G_MONTH3';
527 WHEN 2 THEN
528 xtd := 'decode(G_MONTH1, null, 0, G_MONTH1) + decode(G_MONTH2, null, 0, G_MONTH2)';
529 hist_amt4 := 'G_MONTH2';
530 WHEN 1 THEN
531 xtd := 'G_MONTH1';
532 hist_amt4 := 'G_MONTH1';
533 END CASE;
534
535 -- Populating hist_amt1, hist_amt2 and hist_amt3 columns in the report when period_type = Quarter
536 -- to find the sequence of the quarter of as-of-date. Based on the sequence number
537 -- the hist_amt1, hist_amt2 and hist_amt3 columns of the report will be populated.
538
539 SELECT ent_year_id, sequence INTO l_year_id, l_sequence
540 FROM fii_time_ent_period WHERE l_as_of_date BETWEEN start_date AND end_date;
541
542 CASE l_sequence
543 WHEN 3 then
544 hist_amt1 := null;
545 hist_amt2 := 'G_MONTH1';
546 hist_amt3 := 'G_MONTH2';
547 l_prior_year_id := l_year_id - 1;
548 p_hist_amt1 := 'G_MONTH12';
549 p_hist_amt2 := null;
550 p_hist_amt3 := null;
551
552 WHEN 2 then
553 hist_amt1 := null;
554 hist_amt2 := null;
555 hist_amt3 := 'G_MONTH1';
556 l_prior_year_id := l_year_id - 1;
557 p_hist_amt1 := 'G_MONTH11';
558 p_hist_amt2 := 'G_MONTH12';
559 p_hist_amt3 := null;
560
561 WHEN 1 then
562 hist_amt1 := null;
563 hist_amt2 := null;
564 hist_amt3 := null;
565 l_prior_year_id := l_year_id - 1;
566 p_hist_amt1 := 'G_MONTH10';
567 p_hist_amt2 := 'G_MONTH11';
568 p_hist_amt3 := 'G_MONTH12';
569 ELSE -- 4 or more
570 hist_amt1 := 'G_MONTH'||(l_sequence - 3);
571 hist_amt2 := 'G_MONTH'||(l_sequence - 2);
572 hist_amt3 := 'G_MONTH'||(l_sequence - 1);
573 p_hist_amt1 := null;
574 p_hist_amt2 := null;
575 p_hist_amt3 := null;
576 l_prior_year_id := 0;
577 END CASE;
578
579 -- Populating xtd and rolling period columns in the report when period_type = Period
580
581 ELSIF l_page_period_type = 'FII_TIME_ENT_PERIOD' THEN
582
583 -- to find the sequence of the month of as-of-date. Based on the sequence number
584 -- the xtd column of the report will be populated.
585 SELECT sequence, ent_year_id INTO l_months, l_year_id
586 FROM fii_time_ent_period WHERE l_as_of_date BETWEEN start_date AND end_date;
587
588 xtd := 'G_MONTH'||l_months;
589
590 l_prior_year_id := 0;
591 hist_amt1 := null;
592 hist_amt2 := null;
593 hist_amt3 := null;
594 hist_amt4 := null;
595 p_hist_amt1 := null;
596 p_hist_amt2 := null;
597 p_hist_amt3 := null;
598
599 END IF; -- period_type - period
600
601 fii_ea_util_pkg.g_year_id := l_year_id;
602 fii_ea_util_pkg.g_prior_year_id := l_prior_year_id;
603
604 IF p_trend_type = 'B' THEN
605
606 l_amt_columns := ' DECODE(f.amount_type_code, ''B'',
607 SUM(CASE WHEN ('||xtd||')=0 THEN TO_NUMBER(NULL)
608 ELSE ('||xtd||') END), TO_NUMBER(NULL)) FII_PSI_XTD,
609 DECODE(f.amount_type_code, ''BB'',
610 SUM(CASE WHEN ('||xtd||')=0 THEN TO_NUMBER(NULL)
611 ELSE ('||xtd||') END), TO_NUMBER(NULL)) FII_PSI_BUD_ORIG,
612 DECODE(f.amount_type_code, ''B'',
613 SUM(CASE WHEN ('||hist_amt1||'+0) = 0 THEN TO_NUMBER(NULL)
614 ELSE ('||hist_amt1||'+0) END), TO_NUMBER(NULL)) FII_PSI_HIST_COL1,
615 DECODE(f.amount_type_code, ''B'',
616 SUM(CASE WHEN ('||hist_amt2||'+0) = 0 THEN TO_NUMBER(NULL)
617 ELSE ('||hist_amt2||'+0) END), TO_NUMBER(NULL)) FII_PSI_HIST_COL2,
618 DECODE(f.amount_type_code, ''B'',
619 SUM(CASE WHEN ('||hist_amt3||'+0) = 0 THEN TO_NUMBER(NULL)
620 ELSE ('||hist_amt3||'+0) END), TO_NUMBER(NULL)) FII_PSI_HIST_COL3,
621 DECODE(f.amount_type_code, ''B'',
622 SUM(CASE WHEN ('||hist_amt4||'+0) = 0 THEN TO_NUMBER(NULL)
623 ELSE ('||hist_amt4||'+0) END), TO_NUMBER(NULL)) FII_PSI_HIST_COL4 ';
624
625 l_amt_where := ' AND f.amount_type_code in (''B'', ''BB'') ';
626
627 ELSIF p_trend_type = 'E' THEN
628 l_amt_columns := ' SUM(CASE WHEN ('||xtd||')=0 THEN TO_NUMBER(NULL)
629 ELSE ('||xtd||') END) FII_PSI_XTD,
630 SUM(CASE WHEN ('||hist_amt1||'+0) = 0 THEN TO_NUMBER(NULL)
631 ELSE ('||hist_amt1||'+0) END) FII_PSI_HIST_COL1,
632 SUM(CASE WHEN ('||hist_amt2||'+0) = 0 THEN TO_NUMBER(NULL)
633 ELSE ('||hist_amt2||'+0) END) FII_PSI_HIST_COL2,
634 SUM(CASE WHEN ('||hist_amt3||'+0) = 0 THEN TO_NUMBER(NULL)
635 ELSE ('||hist_amt3||'+0) END) FII_PSI_HIST_COL3,
636 SUM(CASE WHEN ('||hist_amt4||'+0) = 0 THEN TO_NUMBER(NULL)
637 ELSE ('||hist_amt4||'+0) END) FII_PSI_HIST_COL4 ';
638
639 l_amt_where := ' AND f.amount_type_code in (''E'') ';
640 END IF;
641
642 -- Constructing the sql when rolling periods extend to previous year
643
644 -- coded like SUM('||p_hist_amt1||'+0). Sometimes p_hist_amt1 can be null.
645 -- Then sum(null) will give sql error. To avoid this sql error, added +0
646
647
648 IF l_prior_year_id = 0 THEN
649 l_prior_year_sqlstmt:= null;
650 ELSE
651 l_prior_year_sqlstmt:=
652 ' UNION ALL
653 SELECT
654 f.company_id FII_PSI_COL_COMPANY_ID,
655 f.cost_center_id FII_PSI_COL_COST_CENTER_ID,
656 f.fin_category_id FII_PSI_COL_FIN_CAT_ID,
657 TO_NUMBER(NULL) FII_PSI_XTD,';
658
659 IF p_trend_type = 'B' THEN
660 l_prior_year_sqlstmt2 := ' TO_NUMBER(NULL) FII_PSI_BUD_ORIG,';
661 ELSE
662 l_prior_year_sqlstmt2 := null;
663 END IF;
664
665 IF p_trend_type = 'B' THEN
666 l_prior_year_amt :=
667 ' DECODE(f.amount_type_code, ''B'',
668 SUM(CASE WHEN ('||p_hist_amt1||'+0) = 0 THEN TO_NUMBER(NULL)
669 ELSE ('||p_hist_amt1||'+0) END), TO_NUMBER(NULL)) FII_PSI_HIST_COL1,
670 DECODE(f.amount_type_code, ''B'',
671 SUM(CASE WHEN ('||p_hist_amt2||'+0) = 0 THEN TO_NUMBER(NULL)
672 ELSE ('||p_hist_amt2||'+0) END), TO_NUMBER(NULL)) FII_PSI_HIST_COL2,
673 DECODE(f.amount_type_code, ''B'',
674 SUM(CASE WHEN ('||p_hist_amt3||'+0) = 0 THEN TO_NUMBER(NULL)
675 ELSE ('||p_hist_amt3||'+0) END), TO_NUMBER(NULL)) FII_PSI_HIST_COL3,
676 TO_NUMBER(NULL) FII_PSI_HIST_COL4 ';
677 ELSIF p_trend_type = 'E' THEN
678 l_prior_year_amt :=
679 ' SUM(CASE WHEN ('||p_hist_amt1||'+0) = 0 THEN TO_NUMBER(NULL)
680 ELSE ('||p_hist_amt1||'+0) END) FII_PSI_HIST_COL1,
681 SUM(CASE WHEN ('||p_hist_amt2||'+0) = 0 THEN TO_NUMBER(NULL)
682 ELSE ('||p_hist_amt2||'+0) END) FII_PSI_HIST_COL2,
683 SUM(CASE WHEN ('||p_hist_amt3||'+0) = 0 THEN TO_NUMBER(NULL)
684 ELSE ('||p_hist_amt3||'+0) END) FII_PSI_HIST_COL3,
685 TO_NUMBER(NULL) FII_PSI_HIST_COL4 ';
686 END IF;
687
688 l_prior_year_sqlstmt := l_prior_year_sqlstmt || l_prior_year_sqlstmt2 || l_prior_year_amt ||
689 ' FROM
690 fii_gl_local_snap_f'||l_curr_view||' f
691 '||l_company_id_from||l_fin_category_id_from||'
692 WHERE f.year_id = :PRIOR_YEAR_ID
693 AND f.fin_cat_type_code = '''||p_fin_cat||'''
694 '||l_amt_where||'
695 '||l_company_id_where||l_fin_category_id_where||l_fud1_where||l_fud2_where||'
696 '||l_ledger_where||'
697 GROUP BY
698 f.company_id,
699 f.cost_center_id,
700 f.fin_category_id';
701
702 IF p_trend_type = 'B' THEN
703 l_prior_year_sqlstmt := l_prior_year_sqlstmt || ', f.amount_type_code ';
704 END IF;
705
706 END IF;
707
708
709 -- constructing the mail sql. The PMV engine executes this query.
710 -- The quary results populate the reports.
711
712 l_sqlstmt:= '
713 SELECT
714 FII_PSI_COL_COMPANY,
715 FII_PSI_COL_COMPANY_ID,
716 FII_PSI_COL_COST_CENTER,
717 FII_PSI_COL_COST_CENTER_ID,
718 FII_PSI_COL_FIN_CAT,
719 FII_PSI_COL_FIN_CAT_ID,
720 FII_PSI_COL_FIN_CAT_DESP,
721 FII_PSI_XTD,';
722
723 IF p_trend_type = 'B' THEN
724 l_sqlstmt2 := ' FII_PSI_BUD_ORIG FII_PSI_BUD_ORIG,
725 FII_PSI_BUD_ADJ FII_PSI_BUD_ADJ, ';
726 ELSE
727 l_sqlstmt2 := null;
728 END IF;
729
730 l_sqlstmt:= l_sqlstmt || l_sqlstmt2 ||
731 ' FII_PSI_HIST_COL1,
732 FII_PSI_HIST_COL2,
733 FII_PSI_HIST_COL3,
734 FII_PSI_HIST_COL4,
735 FII_PSI_GT_XTD,
736 FII_PSI_GT_HIST_COL1,
737 FII_PSI_GT_HIST_COL2,
738 FII_PSI_GT_HIST_COL3,
739 FII_PSI_GT_HIST_COL4,
740 DECODE(FII_PSI_XTD, NULL, NULL, '''||l_url1||''') FII_PSI_XTD_DRILL,
741 DECODE(FII_PSI_HIST_COL1, NULL, NULL, '''||l_url2||''') FII_PSI_HIST_COL1_DRILL,
742 DECODE(FII_PSI_HIST_COL2, NULL, NULL, '''||l_url3||''') FII_PSI_HIST_COL2_DRILL,
743 DECODE(FII_PSI_HIST_COL3, NULL, NULL, '''||l_url4||''') FII_PSI_HIST_COL3_DRILL,
744 DECODE(FII_PSI_HIST_COL4, NULL, NULL, '''||l_url5||''') FII_PSI_HIST_COL4_DRILL
745 FROM (
746 SELECT
747 com_flex.FLEX_VALUE_MEANING FII_PSI_COL_COMPANY,
748 FII_PSI_COL_COMPANY_ID,
749 cc_flex.FLEX_VALUE_MEANING FII_PSI_COL_COST_CENTER,
750 FII_PSI_COL_COST_CENTER_ID,
751 fin_flex.FLEX_VALUE_MEANING FII_PSI_COL_FIN_CAT,
752 FII_PSI_COL_FIN_CAT_ID,
753 com_flex.DESCRIPTION||''.''||cc_flex.DESCRIPTION||''.''||fin_flex.DESCRIPTION
754 FII_PSI_COL_FIN_CAT_DESP,
755 sum(FII_PSI_XTD) FII_PSI_XTD,';
756
757 IF p_trend_type = 'B' THEN
758 l_sqlstmt2 := ' sum(FII_PSI_BUD_ORIG) FII_PSI_BUD_ORIG,
759 sum(FII_PSI_XTD) - sum(FII_PSI_BUD_ORIG) FII_PSI_BUD_ADJ,';
760 ELSE
761 l_sqlstmt2 := '';
762 END IF;
763
764 l_sqlstmt := l_sqlstmt || l_sqlstmt2 ||
765 ' sum(FII_PSI_HIST_COL1) FII_PSI_HIST_COL1,
766 sum(FII_PSI_HIST_COL2) FII_PSI_HIST_COL2,
767 sum(FII_PSI_HIST_COL3) FII_PSI_HIST_COL3,
768 sum(FII_PSI_HIST_COL4) FII_PSI_HIST_COL4,
769 sum(sum(FII_PSI_XTD)) over() FII_PSI_GT_XTD,
770 sum(sum(FII_PSI_HIST_COL1)) over() FII_PSI_GT_HIST_COL1,
771 sum(sum(FII_PSI_HIST_COL2)) over() FII_PSI_GT_HIST_COL2,
772 sum(sum(FII_PSI_HIST_COL3)) over() FII_PSI_GT_HIST_COL3,
773 sum(sum(FII_PSI_HIST_COL4)) over() FII_PSI_GT_HIST_COL4,
774 (rank () OVER (ORDER BY '||l_order2||' nulls last )) - 1 rnk
775 FROM
776 (SELECT
777 f.company_id FII_PSI_COL_COMPANY_ID,
778 f.cost_center_id FII_PSI_COL_COST_CENTER_ID,
779 f.fin_category_id FII_PSI_COL_FIN_CAT_ID, ' || l_amt_columns || '
780 FROM
781 fii_gl_local_snap_f'||l_curr_view||' f
782 '||l_company_id_from||l_fin_category_id_from||'
783 WHERE f.year_id = :YEAR_ID
784 AND f.fin_cat_type_code = '''||p_fin_cat||'''
785 '||l_amt_where||'
786 '||l_company_id_where||l_fin_category_id_where||l_fud1_where||l_fud2_where||'
787 '||l_ledger_where||'
788 GROUP BY f.company_id,
789 f.cost_center_id,
790 f.fin_category_id';
791
792 IF p_trend_type = 'B' THEN
793 l_sqlstmt2 := ' , f.amount_type_code ';
794 ELSE
795 l_sqlstmt2 := null;
796 END IF;
797
798 l_sqlstmt := l_sqlstmt || l_sqlstmt2 ||
799 l_prior_year_sqlstmt||' )
800 ,fnd_flex_values_tl com_flex
801 ,fnd_flex_values_tl cc_flex
802 ,fnd_flex_values_tl fin_flex
803 WHERE FII_PSI_COL_COMPANY_ID = com_flex.flex_value_id
804 AND com_flex.language = userenv(''LANG'')
805 AND FII_PSI_COL_COST_CENTER_ID = cc_flex.flex_value_id
806 AND cc_flex.language = userenv(''LANG'')
807 AND FII_PSI_COL_FIN_CAT_ID = fin_flex.flex_value_id
808 AND fin_flex.language = userenv(''LANG'')
809 group by
810 com_flex.flex_value_meaning,
811 FII_PSI_COL_COMPANY_ID,
812 cc_flex.flex_value_meaning,
813 FII_PSI_COL_COST_CENTER_ID,
814 fin_flex.flex_value_meaning,
815 FII_PSI_COL_FIN_CAT_ID,
816 com_flex.DESCRIPTION||''.''||cc_flex.DESCRIPTION||''.''||fin_flex.DESCRIPTION
817 '||l_having_clause||'
818 &ORDER_BY_CLAUSE nulls last)
819 WHERE ((rnk between &START_INDEX and &END_INDEX) or (&END_INDEX = -1))';
820
821
822 RETURN l_sqlstmt;
823
824 END get_bud_enc_trend_dtl;
825
826
827 END FII_PSI_BUD_ENC_DETAIL_PKG;
828