eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.FII_GL_PROFIT_AND_LOSS

Source


1 PACKAGE BODY FII_GL_PROFIT_AND_LOSS  AS
2 /* $Header: FIIGLPLB.pls 120.43 2006/03/27 12:13:50 hpoddar noship $ */
3 
4 
5 
6 PROCEDURE GET_OPER_PROFIT1 (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
7 oper_profit_sql out NOCOPY VARCHAR2,
8 oper_profit_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
9  IS
10   oper_profit_rec BIS_QUERY_ATTRIBUTES;
11   sqlstmt                 VARCHAR2(32000);
12   comp_amt		  VARCHAR2(500);
13 
14 BEGIN
15     fii_gl_util_pkg.reset_globals;
16 	fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
17 	fii_gl_util_pkg.get_bitmasks;
18 	fii_gl_util_pkg.g_fin_type := 'OM';
19 	fii_gl_util_pkg.g_view_by := 'FINANCIAL ITEM+GL_FII_FIN_ITEM';
20 	fii_gl_util_pkg.get_viewby_sql;
21 	fii_gl_util_pkg.get_mgr_pmv_sql;
22 	fii_gl_util_pkg.get_cat_pmv_sql;
23 
24 
25       IF ('||fii_gl_util_pkg.g_time_comp||' = 'BUDGET') THEN
26 
27 		comp_amt := 'SUM(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
28                                   and bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
29                                   then f.budget_g else 0 end) prior_amt';
30       ELSE
31 
32 		comp_amt := 'SUM(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
33                                   and bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
34                                   then f.actual_g else 0 end) prior_amt';
35       END IF;
36 
37              sqlstmt := '
38              SELECT  decode(viewbytype, ''CGS'', :COG_MSG, ''OE'', :EXP_MSG, ''R'', :REV_MSG,viewby) VIEWBY,
39                      sum(decode(viewbytype, ''CGS'',grand_total, curr_amt)) FII_MEASURE1,
40                      sum(decode(viewbytype, ''CGS'',prior_grand_total, prior_amt)) FII_MEASURE2
41                      from
42                      (select oper.viewby, cat.fin_category_id viewby_id, cat.fin_cat_type_code viewbytype, oper.curr_amt, oper.prior_amt,
43                       sum(case when cat.fin_cat_type_code =''R'' then oper.curr_amt else oper.curr_amt*(-1) end) over () grand_total,
44                       sum(case when cat.fin_cat_type_code=''R'' then oper.prior_amt else oper.prior_amt*(-1) end) over () prior_grand_total
45                       from
46                              (SELECT  /*+ ordered (cal, f) */ '||fii_gl_util_pkg.g_viewby_value||' viewby,
47 			      tl.flex_value_id viewby_id,
48                               SUM(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
49                                   and bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
50                                   then f.actual_g else 0 end) curr_amt,
51 
52                               '||comp_amt||'
53 
54                               FROM  FII_TIME_RPT_STRUCT      cal
55 				    '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||',' ||fii_gl_util_pkg.g_viewby_from_clause||'
56                               WHERE f.time_id = cal.time_id
57 			      '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_gid||'
58                               AND   f.period_type_id = cal.period_type_id
59 			      AND   (tl.flex_value_id = '||fii_gl_util_pkg.g_viewby_id||' OR
60 				     tl.flex_value_id = f.parent_fin_category_id)
61 			      AND   tl.language = '''||userenv('LANG')||'''
62                               AND   BITAND(cal.record_type_id, :WHERE_PERIOD_TYPE)= cal.record_type_id
63                               AND   cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
64                               GROUP BY '||fii_gl_util_pkg.g_viewby_value||', tl.flex_value_id
65                               order by '||fii_gl_util_pkg.g_viewby_value||' desc) oper,
66                                        (select fin_category_id, fin_cat_type_code from fii_fin_cat_type_assgns
67 					where top_node_flag = ''Y'' and fin_cat_type_code in (''R'', ''OE'', ''CGS'')) cat
68                      where oper.viewby_id (+)= cat.fin_category_id)
69 		     group by decode(viewbytype, ''CGS'', :COG_MSG, ''OE'', :EXP_MSG, ''R'', :REV_MSG,viewby)';
70 
71 	fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, oper_profit_sql, oper_profit_output);
72 
73 END get_oper_profit1;
74 
75 PROCEDURE GET_OPER_PROFIT (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
76 oper_profit_sql out NOCOPY VARCHAR2,
77 oper_profit_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
78  IS
79   oper_profit_rec BIS_QUERY_ATTRIBUTES;
80   sqlstmt                 VARCHAR2(32000);
81   l_prior_or_budget1      VARCHAR2(1000);
82   l_prior_or_budget2      VARCHAR2(1000);
83   l_prior_or_budget3      VARCHAR2(4000);
84 
85 BEGIN
86     fii_gl_util_pkg.reset_globals;
87 	fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
88 	fii_gl_util_pkg.get_bitmasks;
89 	fii_gl_util_pkg.g_fin_type := 'OM';
90 	fii_gl_util_pkg.g_view_by := 'HRI_PERSON+HRI_PER_USRDR_H';
91 	fii_gl_util_pkg.get_viewby_sql;
92 	fii_gl_util_pkg.get_mgr_pmv_sql;
93 	fii_gl_util_pkg.get_cat_pmv_sql;
94 
95   -- when budget comparison type is selected, budget amount is used to calculate the change column
96   -- in the KPI portlet; when year/year or sequential comparison type is selected, prior actual
97   -- amount is used to calculate the change column in the KPI portlet
98   IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
99         l_prior_or_budget1 :='NVL(sum(CY_BUDGET_REV), 0) 		FII_MEASURE2,
100 			      SUM(NVL(sum(CY_BUDGET_REV), 0)) OVER () 		FII_GRAND_TOTAL2,';
101 
102         l_prior_or_budget2 :='NVL(sum(CY_BUDGET_EXP), 0)		FII_MEASURE4,
103 			      SUM(NVL(sum(CY_BUDGET_EXP), 0)) OVER ()		FII_GRAND_TOTAL4,';
104 
105         l_prior_or_budget3 :='NULL		FII_MEASURE13,
106 			      (sum(NVL(sum(CY_BUDGET_REV),0)) over() - (sum(NVL(sum(CY_BUDGET_EXP),0)) over() + sum(NVL(sum(CY_BUDGET_CGS),0)) over() )) /
107              		NULLIF(sum(sum(CY_BUDGET_REV)) over(),0)*100    FII_MEASURE10,
108 	NVL(sum(CY_BUDGET_REV), 0) - (NVL(sum(CY_BUDGET_EXP), 0) + NVL(sum(CY_BUDGET_CGS), 0))		FII_MEASURE6,
109 	SUM(NVL(sum(CY_BUDGET_REV), 0) - (NVL(sum(CY_BUDGET_EXP), 0) + NVL(sum(CY_BUDGET_CGS), 0))) OVER ()		FII_GRAND_TOTAL6';
110 
111   ELSE
112         l_prior_or_budget1 :='NVL(sum(PY_ACTUAL_REV),0)		FII_MEASURE2,
113 			      SUM(NVL(sum(PY_ACTUAL_REV),0)) OVER ()		FII_GRAND_TOTAL2,';
114 
115         l_prior_or_budget2 :='NVL(sum(PY_ACTUAL_EXP),0)		FII_MEASURE4,
116 			      SUM(NVL(sum(PY_ACTUAL_EXP),0)) OVER ()		FII_GRAND_TOTAL4,';
117 
118         l_prior_or_budget3 :='NULL		FII_MEASURE13,
119 			 (sum(NVL(sum(PY_ACTUAL_REV),0)) over() - (sum(NVL(sum(PY_ACTUAL_EXP),0)) over() + sum(NVL(sum(PY_ACTUAL_CGS),0)) over() )) /
120              		NULLIF(sum(sum(PY_ACTUAL_REV)) over(),0)*100    FII_MEASURE10,
121 			NVL(sum(PY_ACTUAL_REV),0) - (NVL(sum(PY_ACTUAL_EXP),0) + NVL(sum(PY_ACTUAL_CGS),0)) 		FII_MEASURE6,
122 			SUM(NVL(sum(PY_ACTUAL_REV),0) - (NVL(sum(PY_ACTUAL_EXP),0) + NVL(sum(PY_ACTUAL_CGS),0))) OVER () 		FII_GRAND_TOTAL6';
123 
124   END IF;
125 
126 /* --------------- Mapping ----------------
127  * manager                     VIEWBY
128  * revenue_amount              FII_MEASURE1,
129  * revenue_amount_previous     FII_MEASURE2,
130  * expenses_amount             FII_MEASURE3,
131  * expenses_amount_previous    FII_MEASURE4,
132  * operating_income            FII_MEASURE5,
133  * operating_income_previous   FII_MEASURE6
134  */
135 
136     sqlstmt := '
137              SELECT  '||fii_gl_util_pkg.g_viewby_value||'			  VIEWBY,
138                      f.viewby_id				                  VIEWBYID,
139 		     NVL(sum(CY_ACTUAL_REV),0)                                    FII_MEASURE1,
140 		     SUM(NVL(sum(CY_ACTUAL_REV),0)) OVER ()                       FII_GRAND_TOTAL1,
141 		     NVL(sum(CY_ACTUAL_EXP),0)                                    FII_MEASURE3,
142 		     SUM(NVL(sum(CY_ACTUAL_EXP),0)) OVER ()                       FII_GRAND_TOTAL3,
143         	     NULL							  FII_MEASURE14,
144 		     NVL(sum(CY_ACTUAL_REV),0) - (NVL(sum(CY_ACTUAL_EXP),0) + NVL(sum(CY_ACTUAL_CGS),0)) FII_MEASURE5,
145 		     SUM(NVL(sum(CY_ACTUAL_REV),0) - (NVL(sum(CY_ACTUAL_EXP),0) + NVL(sum(CY_ACTUAL_CGS),0))) OVER () FII_GRAND_TOTAL5,
146 		     (NVL(sum(sum(CY_ACTUAL_REV)) over(),0) - (NVL(sum(sum(CY_ACTUAL_EXP)) over(),0) + NVL(sum(sum(CY_ACTUAL_CGS)) over(),0) )) /
147              		NULLIF(sum(sum(CY_ACTUAL_REV)) over(),0)*100	  FII_MEASURE9,
148 		     to_number(NULL)						  FII_MEASURE15,
149 		     to_number(NULL)						  FII_MEASURE16,
150 		     '||l_prior_or_budget1||'
151                      '||l_prior_or_budget2||'
152                      '||l_prior_or_budget3||'
153              FROM
154 		   '||fii_gl_util_pkg.g_viewby_from_clause||',
155 		(select '||fii_gl_util_pkg.g_viewby_id||' viewby_id,
156     		 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
157 			  and asgn.fin_cat_type_code = ''R''
158                           then f.actual_g
159                           else to_number(NULL) end)      CY_ACTUAL_REV,
160 		 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
161 			  and asgn.fin_cat_type_code = ''OE''
162                           then f.actual_g
163                           else to_number(NULL) end)      CY_ACTUAL_EXP,
164 		 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
165 			  and asgn.fin_cat_type_code = ''CGS''
166                           then f.actual_g
167                           else to_number(NULL) end)      CY_ACTUAL_CGS,
168                  sum(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
169                           and asgn.fin_cat_type_code = ''R''
170 			  then f.budget_g
171                           else to_number(NULL) end)      CY_BUDGET_REV,
172 		  sum(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
173                           and asgn.fin_cat_type_code = ''OE''
174 			  then f.budget_g
175                           else to_number(NULL) end)      CY_BUDGET_EXP,
176 		  sum(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
177                           and asgn.fin_cat_type_code = ''CGS''
178 			  then f.budget_g
179                           else to_number(NULL) end)      CY_BUDGET_CGS,
180 		 to_number(NULL)                         PY_ACTUAL_REV,
181 		 to_number(NULL)			 PY_ACTUAL_EXP,
182 		 to_number(NULL)			 PY_ACTUAL_CGS
183                  FROM  FII_TIME_RPT_STRUCT                        cal,
184 		       FII_FIN_CAT_TYPE_ASSGNS                    asgn
185 		   '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||'
186 		 WHERE f.time_id = cal.time_id
187 	     '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||'
188              '||fii_gl_util_pkg.g_gid||'
189              AND   f.period_type_id = cal.period_type_id
190              AND   BITAND(cal.record_type_id, :WHERE_PERIOD_TYPE)= cal.record_type_id
191              AND   cal.report_date = &BIS_CURRENT_ASOF_DATE
192 	     AND   asgn.fin_category_id = f.fin_category_id
193              GROUP BY '||fii_gl_util_pkg.g_viewby_id||'
194 		union all
195 
196 		select '||fii_gl_util_pkg.g_viewby_id||' viewby_id,
197 		        to_number(NULL)				CY_ACTUAL_REV,
198                 to_number(NULL)				CY_ACTUAL_EXP,
199                 to_number(NULL)			    CY_ACTUAL_CGS,
200                 to_number(NULL)				CY_BUDGET_REV,
201                 to_number(NULL)				CY_BUDGET_EXP,
202 		        to_number(NULL)				CY_BUDGET_CGS,
203         	sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
204 			  and asgn.fin_cat_type_code = ''R''
205                           then f.actual_g
206                           else to_number(NULL) end)      PY_ACTUAL_REV,
207 		 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
208 			  and asgn.fin_cat_type_code = ''OE''
209                           then f.actual_g
210                           else to_number(NULL) end)      PY_ACTUAL_EXP,
211 		 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
212 			  and asgn.fin_cat_type_code = ''CGS''
213                           then f.actual_g
214                           else to_number(NULL) end)      PY_ACTUAL_CGS
215 		 FROM  FII_TIME_RPT_STRUCT                        cal,
216                 FII_FIN_CAT_TYPE_ASSGNS                    asgn
217 		   '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||'
218              WHERE f.time_id = cal.time_id
219 	     '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||'
220              '||fii_gl_util_pkg.g_gid||'
221              AND   f.period_type_id = cal.period_type_id
222              AND   BITAND(cal.record_type_id, :ACT_WHERE_PERIOD_TYPE)= cal.record_type_id
223              AND   cal.report_date = &BIS_PREVIOUS_ASOF_DATE
224 	     AND   asgn.fin_category_id = f.fin_category_id
225 		GROUP BY '||fii_gl_util_pkg.g_viewby_id||')  f
226 
227 	     WHERE '||fii_gl_util_pkg.g_viewby_join||'
228              GROUP BY '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id';
229 
230 	fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, oper_profit_sql, oper_profit_output);
231 END get_oper_profit;
232 
233 PROCEDURE GET_REV_BY_CHANNEL (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
234 rev_by_channel_sql out NOCOPY VARCHAR2, rev_by_channel_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
235  IS
236    rev_by_channel_rec     BIS_QUERY_ATTRIBUTES;
237    sqlstmt                VARCHAR2(32000);
238 
239 BEGIN
240     fii_gl_util_pkg.reset_globals;
241 	fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
242     fii_gl_util_pkg.get_bitmasks;
243 
244 
245 
246   /* --- Mapping -------
247    * channel                     FII_ATTRIBUTE1,
248    * revenue_amount              FII_MEASURE1,
249    * revenue_amount_previous     FII_MEASURE2,
250    * revenue_amount              FII_MEASURE4,
251    * revenue_amount_previous     FII_MEASURE5,
252    */
253 
254  IF fii_gl_util_pkg.g_mgr_id = -99999 THEN /* Done for bug 3875336 */
255 
256  sqlstmt := '
257 
258 	SELECT	NULL	FII_ATTRIBUTE1,
259 		NULL    FII_MEASURE9,
260 		NULL    FII_MEASURE1,
261 		NULL    FII_MEASURE2,
262 		NULL    FII_MEASURE4,
263 		NULL    FII_MEASURE5,
264 		NULL	FII_MEASURE7,
265 		NULL	FII_MEASURE8
266 	FROM	DUAL
267 	WHERE	1=2';
268 
269 ELSE
270 
271 sqlstmt := '
272       SELECT bsc.value                                        FII_ATTRIBUTE1,
273              bsc.id                                           FII_MEASURE9,
274 	     sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
275                       then f.actual_g else 0 end)           FII_MEASURE1,
276              sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
277                       then f.actual_g else 0 end)           FII_MEASURE2,
278              sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
279                       then f.actual_g else 0 end)           FII_MEASURE4,
280              sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
281                       then f.actual_g else 0 end)           FII_MEASURE5,
282              sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
283                       then f.actual_g else 0 end)) over() FII_MEASURE7,
284              sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
285                       then f.actual_g else 0 end)) over() FII_MEASURE8
286       FROM   bis_sales_channels_v     bsc,
287              fii_ar_rev_sum_v'|| fii_gl_util_pkg.g_global_curr_view ||'        f,
288 	     fii_cc_mgr_hierarchies   h,
289              FII_TIME_RPT_STRUCT      cal
290       where f.sales_channel_code = bsc.id
291       and   h.mgr_id 	 	 =  &HRI_PERSON+HRI_PER_USRDR_H
292       and   f.manager_id 	 =  h.emp_id
293       and   f.time_id            = cal.time_id
294       and   f.period_type_id     = cal.period_type_id
295       and   bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE)= cal.record_type_id
296       and   cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
297       group by bsc.value, bsc.id';
298 
299 END IF;
300 
301 	fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, rev_by_channel_sql, rev_by_channel_output);
302 
303 END GET_REV_BY_CHANNEL;
304 
305 END fii_gl_profit_and_loss;