1 PACKAGE BODY FII_EA_DPRN_EXP_PKG AS
2 /* $Header: FIIEAMAJB.pls 120.3 2006/03/20 13:00:23 hpoddar noship $ */
3
4 -- ----------------------------------------------------------------------
5 --
6 -- GET_DPRN_EXP_MAJ: This procedure is called from Depreciation Expense
7 -- by Major Category report. It is the main procedure
8 -- that reads the report parameter values, builds the
9 -- PMV sql and passes back to the calling PMV report.
10 --
11 -- ----------------------------------------------------------------------
12 PROCEDURE GET_DPRN_EXP_MAJ (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
13 get_dprn_exp_maj_sql OUT NOCOPY VARCHAR2,
14 get_dprn_exp_maj_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) is
15
16 l_trunc_sql VARCHAR2(100);
17 l_sqlstmt VARCHAR2(2000);
18 l_sqlstmt2 VARCHAR2(5000);
19
20 l_company_id fii_ea_util_pkg.g_company_id%TYPE;
21 l_cost_center_id fii_ea_util_pkg.g_cost_center_id%TYPE;
22 l_fin_category_id fii_ea_util_pkg.g_fin_category_id%TYPE;
23 l_ledger_id fii_ea_util_pkg.g_ledger_id%TYPE;
24 l_actual_bitand fii_ea_util_pkg.g_actual_bitand%TYPE;
25 l_fud1_id fii_ea_util_pkg.g_fud1_id%TYPE;
26 l_fud2_id fii_ea_util_pkg.g_fud2_id%TYPE;
27 l_asof_date fii_ea_util_pkg.g_as_of_date%TYPE;
28 l_prev_asof_date fii_ea_util_pkg.g_previous_asof_date%TYPE;
29 l_curr fii_ea_util_pkg.g_currency%TYPE;
30
31 l_fud1_where VARCHAR2(240);
32 l_fud2_where VARCHAR2(240);
33 l_asset_url VARCHAR2(240);
34 l_minor_url VARCHAR2(240);
35
36 l_maj_seg_name VARCHAR2(30);
37
38 Cursor C1 is select major_seg_name
39 from fii_fa_cat_segments;
40
41 BEGIN
42
43 -- reset the global variables
44 fii_ea_util_pkg.reset_globals;
45
46 -- read the paramaters and populate the global variables
47 fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);
48
49 -- copy values to local variables
50 l_company_id := fii_ea_util_pkg.g_company_id;
51 l_cost_center_id := fii_ea_util_pkg.g_cost_center_id;
52 l_fin_category_id := fii_ea_util_pkg.g_fin_category_id;
53 l_actual_bitand := fii_ea_util_pkg.g_actual_bitand;
54 l_asof_date := fii_ea_util_pkg.g_as_of_date;
55 l_prev_asof_date := fii_ea_util_pkg.g_previous_asof_date;
56 l_ledger_id := fii_ea_util_pkg.g_ledger_id;
57 l_fud1_id := fii_ea_util_pkg.g_fud1_id;
58 l_fud2_id := fii_ea_util_pkg.g_fud2_id;
59
60 -- Company Id will always be passed
61 -- Cost Center Id will always be passed
62 -- Financial Category Id will always be passed
63 -- Ledger Id will always be passed
64
65 -- fud1 may be either All or specific
66 -- We need to build the where clause this way
67 -- instead of calling fii_ea_util_pkg.get_fud1_for_detail
68 -- so that it can also be used for the insert SQL below
69 if (l_fud1_id IS NOT NULL AND l_fud1_id <> 'All') THEN
70 l_fud1_where := ' and f.user_dim1_id = :l_fud1_id';
71 else
72 l_fud1_where := '';
73 end if;
74
75 --fud2 may be either All or specific
76 -- We need to build the where clause this way
77 -- instead of calling fii_ea_util_pkg.get_fud2_for_detail
78 -- so that it can also be used for the insert SQL below
79 if (l_fud2_id IS NOT NULL AND l_fud2_id <> 'All') THEN
80 l_fud2_where := ' and f.user_dim2_id = :l_fud2_id';
81 else
82 l_fud2_where := '';
83 end if;
84
85 -- get the flex segment column name used for the Major Category segment
86 -- This value determines which column is being used in fa_categories to store Major Category
87 open C1;
88 fetch C1 into l_maj_seg_name;
89 close C1;
90
91 -- Cleanup the global temp table.
92 -- We need to do this b'cause every time the user picks selects a report parameter
93 -- this package is run and will create/insert new rows in the global temp
94
95 l_trunc_sql := 'delete from FII_EA_MAJ_CAT_GT';
96 execute immediate l_trunc_sql;
97
98
99 -- Prepare the stmnt to populate the global temp table
100 -- This stamtement will store all major categories and the count of its minor categories
101 -- for the passed in parameters. This is needed to determine the drill down report
102 l_sqlstmt := 'Insert into FII_EA_MAJ_CAT_GT( asset_cat_major_id,
103 minor_count )
104 ( select distinct f.asset_cat_major_id,
105 count(f.asset_cat_minor_id)
106 from fii_fa_exp_mv f,
107 fii_time_structures cal
108 where f.time_id = cal.time_id
109 and cal.period_type_id = f.period_type_id
110 and cal.report_date in ( :ASOF_DATE, :PREVIOUS_ASOF_DATE)
111 and bitand(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND
112 and f.ledger_id = :LEDGER_ID
113 and f.company_id = :COMPANY_ID
114 and f.cost_center_id = :COST_CENTER_ID
115 and f.natural_account_id = :FIN_CATEGORY_ID
116 '||l_fud1_where||' '||l_fud2_where||
117 ' group by f.asset_cat_major_id )';
118
119 IF (l_fud2_id IS NOT NULL AND l_fud2_id <> 'All' AND l_fud1_id IS NOT NULL AND l_fud1_id <> 'All') THEN
120 EXECUTE IMMEDIATE l_sqlstmt using l_asof_date,l_prev_asof_date,l_actual_bitand,l_actual_bitand,l_ledger_id,l_company_id,l_cost_center_id,l_fin_category_id,l_fud1_id, l_fud2_id;
121 ELSIF l_fud1_id IS NOT NULL AND l_fud1_id <> 'All' THEN
122 EXECUTE IMMEDIATE l_sqlstmt using l_asof_date,l_prev_asof_date,l_actual_bitand,l_actual_bitand,l_ledger_id,l_company_id,l_cost_center_id,l_fin_category_id,l_fud1_id;
123 ELSIF l_fud2_id IS NOT NULL AND l_fud2_id <> 'All' THEN
124 EXECUTE IMMEDIATE l_sqlstmt using l_asof_date,l_prev_asof_date,l_actual_bitand,l_actual_bitand,l_ledger_id,l_company_id,l_cost_center_id,l_fin_category_id,l_fud2_id;
125 ELSE EXECUTE IMMEDIATE l_sqlstmt using l_asof_date,l_prev_asof_date,l_actual_bitand,l_actual_bitand,l_ledger_id,l_company_id,l_cost_center_id,l_fin_category_id;
126 END IF;
127
128 -- reinitialize to use the API whereclause
129 l_fud1_where := replace(fii_ea_util_pkg.get_fud1_for_detail, 'fud1_id', 'user_dim1_id');
130 l_fud2_where := replace(fii_ea_util_pkg.get_fud2_for_detail, 'fud2_id', 'user_dim2_id');
131
132 l_asset_url := 'pFunctionName=FII_EA_DPRN_EXP_LIST&pParamIds=Y&FII_EA_ASSET_CAT_MAJOR=FII_EA_ASSET_CAT_MAJ_ID';
133 l_minor_url := 'pFunctionName=FII_EA_DPRN_EXP_MIN&pParamIds=Y&FII_EA_ASSET_CAT_MAJOR=FII_EA_ASSET_CAT_MAJ_ID';
134
135 -- The following is the main SQL that will be passed back to the PMV report
136 -- Note that it joins with the global temp table FII_EA_MAJ_CAT_GT that is populated earlier
137 -- Since this report now joins/looks at fa_categories table, we dont have to worry about
138 -- what value set type ( table based/dependant/independant ) is being used for the kff setups.
139 -- The fa_categories stores the value in the corresponding segment column
140 -- that is setup for the major segment valueset
141
142 l_sqlstmt2 := ' select maj.fii_ea_major_cat FII_EA_ASSET_CAT_MAJOR,
143 maj.fii_ea_major_cat_id FII_EA_ASSET_CAT_MAJ_ID,
144 decode(gtemp.minor_count, 0, '''||l_asset_url||''', '''||l_minor_url||''') FII_EA_ASSET_CAT_MAJOR_DRILL,
145 maj.fii_xtd_amount FII_EA_XTD,
146 maj.fii_xtd_prior_amount FII_EA_PRIOR_XTD,
147 ( ((maj.fii_xtd_amount - maj.fii_xtd_prior_amount ) /
148 DECODE(maj.fii_xtd_prior_amount, 0, to_number(null), maj.fii_xtd_prior_amount)) *100) FII_EA_CHANGE,
149 sum(maj.fii_xtd_amount) over() FII_EA_GT_XTD,
150 sum(maj.fii_xtd_prior_amount) over() FII_EA_GT_PRIOR_XTD,
151 ( ( (sum(maj.fii_xtd_amount) over() - sum(maj.fii_xtd_prior_amount) over()) /
152 decode ( (sum(maj.fii_xtd_prior_amount) over()), 0, to_number(null),
153 (sum(maj.fii_xtd_prior_amount) over()) )) * 100 ) FII_EA_GT_CHANGE
154 from ( select f.asset_cat_major_id FII_EA_MAJOR_CAT_ID,
155 cat.'||l_maj_seg_name||' FII_EA_MAJOR_CAT,
156 SUM(CASE WHEN cal.report_date = :ASOF_DATE
157 THEN tot_amount_t ELSE to_number(null) END) FII_XTD_AMOUNT,
158 SUM(CASE WHEN cal.report_date = :PREVIOUS_ASOF_DATE
159 THEN tot_amount_t ELSE to_number(null) END) FII_XTD_PRIOR_AMOUNT
160 from fa_categories cat,
161 fii_fa_exp_mv f,
162 fii_time_structures cal
163 where f.time_id = cal.time_id
164 and cal.period_type_id = f.period_type_id
165 and cal.report_date in (:ASOF_DATE, :PREVIOUS_ASOF_DATE)
166 and f.asset_cat_id = cat.category_id
167 and bitand(cal.record_type_id, :ACTUAL_BITAND ) = :ACTUAL_BITAND
168 and f.company_id = &FII_COMPANIES+FII_COMPANIES
169 and f.ledger_id = &FII_LEDGER+FII_LEDGER
170 and f.cost_center_id = &ORGANIZATION+HRI_CL_ORGCC
171 and f.natural_account_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM '||
172 l_fud1_where||' '||l_fud2_where||
173 ' group by f.asset_cat_major_id, cat.'||l_maj_seg_name||' ) maj,
174 FII_EA_MAJ_CAT_GT gtemp
175 where gtemp.asset_cat_major_id = maj.fii_ea_major_cat_id
176 order by maj.fii_xtd_amount DESC ';
177
178 fii_ea_util_pkg.bind_variable(l_sqlstmt2, p_page_parameter_tbl, get_dprn_exp_maj_sql, get_dprn_exp_maj_output);
179
180 end GET_DPRN_EXP_MAJ;
181
182
183
184 PROCEDURE GET_DPRN_EXP_MIN( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
185 get_dprn_exp_min_sql OUT NOCOPY VARCHAR2,
186 get_dprn_exp_min_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) is
187
188 l_sqlstmt VARCHAR2(5000);
189
190 l_asset_cat_maj_id fii_ea_util_pkg.g_maj_cat_id%TYPE;
191
192 l_fud1_where VARCHAR2(1000);
193 l_fud2_where VARCHAR2(1000);
194 l_asset_url VARCHAR2(240);
195
196 l_min_seg_name VARCHAR2(30);
197
198 Cursor C1 is select minor_seg_name
199 from fii_fa_cat_segments;
200
201 BEGIN
202
203 -- reset the global variables
204 fii_ea_util_pkg.reset_globals;
205
206 -- read the paramaters and populate the global variables
207 fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);
208
209 -- Company Id will always be passed
210 -- Cost Center Id will always be passed
211 -- Financial Category Id will always be passed
212 -- Ledger Id will always be passed
213 -- Asset Category Major Id will always be passed
214
215 -- copy values to local variables
216 l_asset_cat_maj_id:= fii_ea_util_pkg.g_maj_cat_id;
217 l_fud1_where := replace(fii_ea_util_pkg.get_fud1_for_detail, 'fud1_id', 'user_dim1_id');
218 l_fud2_where := replace(fii_ea_util_pkg.get_fud2_for_detail, 'fud2_id', 'user_dim2_id');
219
220 -- get the flex segment column name used for the Minor Category segment
221 -- This value determines which column is being used in fa_categories to store Minor Category
222 open C1;
223 fetch C1 into l_min_seg_name;
224 close C1;
225
226 l_asset_url := 'pFunctionName=FII_EA_DPRN_EXP_LIST&pParamIds=Y&FII_EA_ASSET_CAT_MINOR=FII_EA_ASSET_CAT_MIN_ID';
227
228 -- The following is the main SQL that will be passed back to the PMV report
229 -- Since this report now joins/looks at fa_categories table, we dont have to worry about
230 -- what value set type ( table based/dependant/independant ) is being used for the kff setups.
231 -- The fa_categories stores the value in the corresponding segment column
232 -- that is setup for the major segment valueset
233
234 l_sqlstmt := ' select min.fii_ea_minor_cat FII_EA_ASSET_CAT_MINOR,
235 min.fii_ea_minor_cat_id FII_EA_ASSET_CAT_MIN_ID,
236 '''||l_asset_url||''' FII_EA_ASSET_CAT_MINOR_DRILL,
237 min.fii_xtd_amount FII_EA_XTD,
238 min.fii_xtd_prior_amount FII_EA_PRIOR_XTD,
239 ( ((min.fii_xtd_amount - min.fii_xtd_prior_amount ) /
240 DECODE(min.fii_xtd_prior_amount, 0, to_number(null),
241 min.fii_xtd_prior_amount)) *100) FII_EA_CHANGE,
242 sum(min.fii_xtd_amount) over() FII_EA_GT_XTD,
243 sum(min.fii_xtd_prior_amount) over() FII_EA_GT_PRIOR_XTD,
244 (( (sum(min.fii_xtd_amount) over() - sum(min.fii_xtd_prior_amount) over()) /
245 decode ( (sum(min.fii_xtd_prior_amount) over()), 0, to_number(null),
246 (sum(min.fii_xtd_prior_amount) over()) )) * 100 ) FII_EA_GT_CHANGE
247 from ( select f.asset_cat_minor_id FII_EA_MINOR_CAT_ID,
248 cat.'||l_min_seg_name||' FII_EA_MINOR_CAT,
249 SUM(CASE WHEN cal.report_date = :ASOF_DATE
250 THEN tot_amount_t ELSE to_number(null) END) FII_XTD_AMOUNT,
251 SUM(CASE WHEN cal.report_date = :PREVIOUS_ASOF_DATE
252 THEN tot_amount_t ELSE to_number(null) END) FII_XTD_PRIOR_AMOUNT
253 from fa_categories cat,
254 fii_fa_exp_mv f,
255 fii_time_structures cal
256 where f.time_id = cal.time_id
257 and cal.period_type_id = f.period_type_id
258 and cal.report_date in (:ASOF_DATE, :PREVIOUS_ASOF_DATE)
259 and f.asset_cat_id = cat.category_id
260 and bitand(cal.record_type_id, :ACTUAL_BITAND ) = :ACTUAL_BITAND
261 and f.company_id = &FII_COMPANIES+FII_COMPANIES
262 and f.ledger_id = &FII_LEDGER+FII_LEDGER
263 and f.cost_center_id = &ORGANIZATION+HRI_CL_ORGCC
264 and f.natural_account_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM
265 and f.asset_cat_major_id = &FII_ASSET_CATEGORIES+FII_ASSET_CAT_MAJOR '
266 ||l_fud1_where||' '||l_fud2_where||
267 ' group by f.asset_cat_minor_id, cat.'||l_min_seg_name||' ) min
268 order by min.fii_xtd_amount DESC ';
269
270 fii_ea_util_pkg.bind_variable(l_sqlstmt, p_page_parameter_tbl, get_dprn_exp_min_sql, get_dprn_exp_min_output);
271
272 END GET_DPRN_EXP_MIN;
273
274
275
276 PROCEDURE GET_DPRN_EXP_LIST( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
277 get_dprn_exp_list_sql OUT NOCOPY VARCHAR2,
278 get_dprn_exp_list_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
279
280 l_sqlstmt VARCHAR2(5000);
281
282 l_cat_maj_id fii_ea_util_pkg.g_maj_cat_id%TYPE;
283 l_cat_min_id fii_ea_util_pkg.g_maj_cat_id%TYPE;
284
285 l_fud1_where VARCHAR2(240);
286 l_fud2_where VARCHAR2(240);
287 l_cat_min_where VARCHAR2(100);
288 l_asset_url VARCHAR2(240);
289 l_period_type fii_ea_util_pkg.g_page_period_type%TYPE;
290
291 BEGIN
292
293 -- reset the global variables
294 fii_ea_util_pkg.reset_globals;
295
296 -- read the paramaters and populate the global variables
297 fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);
298
299 -- copy values to local variables
300 l_cat_maj_id:= fii_ea_util_pkg.g_maj_cat_id;
301 l_cat_min_id:= fii_ea_util_pkg.g_min_cat_id;
302 l_fud1_where := replace(fii_ea_util_pkg.get_fud1_for_detail, 'fud1_id', 'user_dim1_id');
303 l_fud2_where := replace(fii_ea_util_pkg.get_fud2_for_detail, 'fud2_id', 'user_dim2_id');
304
305 -- Min cat may be All or Specific
306 if ( l_cat_min_id IS NOT NULL AND l_cat_min_id <> 'All') then
307 l_cat_min_where := 'and f.asset_cat_minor_id = :MIN_CAT_ID';
308 else
309 l_cat_min_where := '';
310 end if;
311
312 l_asset_url := 'pFunctionName=FII_EA_IA_DRILL&AssetId=FII_EA_ASSET_ID&AssetNumber=FII_EA_ASSET_NUM&addBreadCrumb=Y&retainAM=Y';
313
314 l_sqlstmt := ' select av.asset_number FII_EA_ASSET_NUM,
315 av.asset_id FII_EA_ASSET_ID,
316 '''||l_asset_url||''' FII_EA_ASSET_DRILL,
317 sum(av.xtd) FII_EA_XTD,
318 sum(av.prior_xtd) FII_EA_PRIOR_XTD,
319 ( ((sum(av.xtd) - sum(av.prior_xtd))/
320 DECODE ( sum(av.prior_xtd), 0, to_number(null), sum(av.prior_xtd))) *100) FII_EA_CHANGE,
321 av.description FII_EA_ASSET_DESCR,
322 sum(SUM(av.xtd)) over() FII_EA_GT_XTD,
323 sum(SUM(av.prior_xtd)) over() FII_EA_GT_PRIOR_XTD,
324 ( ( sum(SUM(av.xtd)) over() - sum(SUM(av.prior_xtd)) over() )/
325 ( decode( (sum(SUM(av.prior_xtd)) over()), 0, to_number(null),
326 (sum(SUM(av.prior_xtd)) over() ) )) ) *100 FII_EA_GT_CHANGE
327 from ( select f.asset_id,
328 f.asset_number,
329 a.description,
330 amount_t xtd,
331 to_number(null) prior_xtd
332 from fa_additions_tl a,
333 fii_fa_exp_f f
334 where f.account_date between :CURR_PERIOD_START AND :ASOF_DATE
335 and f.asset_id = a.asset_id
336 and a.language = userenv(''LANG'')
337 and f.company_id = &FII_COMPANIES+FII_COMPANIES
338 and f.ledger_id = &FII_LEDGER+FII_LEDGER
339 and f.cost_center_id = &ORGANIZATION+HRI_CL_ORGCC
340 and f.natural_account_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM
341 and f.asset_cat_major_id = &FII_ASSET_CATEGORIES+FII_ASSET_CAT_MAJOR '
342 ||l_cat_min_where||' '||l_fud1_where||' '||l_fud2_where||
343 ' UNION ALL
344 select f.asset_id,
345 f.asset_number,
346 a.description,
347 to_number(null),
348 amount_t
349 from fa_additions_tl a,
350 fii_fa_exp_f f
351 where f.account_date between :PRIOR_PERIOD_START AND :PRIOR_PERIOD_END
352 and f.asset_id = a.asset_id
353 and a.language = userenv(''LANG'')
354 and f.company_id = &FII_COMPANIES+FII_COMPANIES
355 and f.ledger_id = &FII_LEDGER+FII_LEDGER
356 and f.cost_center_id = &ORGANIZATION+HRI_CL_ORGCC
357 and f.natural_account_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM
358 and f.asset_cat_major_id = &FII_ASSET_CATEGORIES+FII_ASSET_CAT_MAJOR '
359 ||l_cat_min_where||' '||l_fud1_where||' '||l_fud2_where||
360 ' ) av
361 group by av.asset_id,
362 av.asset_number,
363 av.description,
364 '''||l_asset_url||'''
365 order by 4 DESC ';
366
367 fii_ea_util_pkg.bind_variable(l_sqlstmt, p_page_parameter_tbl, get_dprn_exp_list_sql, get_dprn_exp_list_output);
368
369 END GET_DPRN_EXP_LIST;
370
371
372 END FII_EA_DPRN_EXP_PKG;
[Home] [Help]