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