[Home] [Help]
PACKAGE BODY: APPS.FII_EA_SOURCE_PKG
Source
1 PACKAGE BODY FII_EA_SOURCE_PKG AS
2 /* $Header: FIIEASOURCEB.pls 120.3 2005/06/22 15:12:21 sajgeo noship $ */
3
4 ---------------------------------------------------------------------------------
5 -- the get_exp_source procedure is called by Expense Source report.
6 -- It is a wrapper for get_rev_exp_source function.
7
8 PROCEDURE get_exp_source (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
9 p_exp_source_sql out NOCOPY VARCHAR2,
10 p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
11
12 is
13 l_multi_factor NUMBER;
14 l_sqlstmt VARCHAR2(15000);
15
16 BEGIN
17
18 -- in table fii_gl_je_summary_b, the expenses are negative numbers and
19 -- revenues are positive numbers. Hence in Expense Source report, the
20 -- numbers are multiplied by -1. In Revenue Source report, the numbers
21 -- are multiplied by +1.
22
23 l_multi_factor := -1;
24
25 l_sqlstmt := get_rev_exp_source (p_page_parameter_tbl => p_page_parameter_tbl,
26 p_multi_factor => l_multi_factor);
27
28 fii_ea_util_pkg.bind_variable(p_sqlstmt => l_sqlstmt,
29 p_page_parameter_tbl => p_page_parameter_tbl,
30 p_sql_output => p_exp_source_sql,
31 p_bind_output_table => p_exp_source_output);
32
33 END get_exp_source;
34
35 ---------------------------------------------------------------------------------
36 -- the get_rev_source procedure is called by Revenue Source report.
37 -- It is a wrapper for get_rev_exp_source function.
38
39 PROCEDURE get_rev_source (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
40 p_rev_source_sql out NOCOPY VARCHAR2,
41 p_rev_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
42
43 is
44 l_multi_factor NUMBER;
45 l_sqlstmt VARCHAR2(15000);
46
47 BEGIN
48 -- in table fii_gl_je_summary_b, the expenses are negative numbers and
49 -- revenues are positive numbers. Hence in Expense Source report, the
50 -- numbers are multiplied by -1. In Revenue Source report, the numbers
51 -- are multiplied by +1.
52
53 l_multi_factor := 1;
54
55 l_sqlstmt := get_rev_exp_source (p_page_parameter_tbl => p_page_parameter_tbl,
56 p_multi_factor => l_multi_factor);
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_rev_source_sql,
61 p_bind_output_table => p_rev_source_output);
62
63 END get_rev_source;
64
65
66 ---------------------------------------------------------------------------------
67 -- This is the main function which constructs the PMV sql.
68
69 FUNCTION get_rev_exp_source (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
70 p_multi_factor IN NUMBER) return VARCHAR2
71 IS
72
73 get_rev_exp_source BIS_QUERY_ATTRIBUTES;
74 l_sqlstmt VARCHAR2(15000);
75
76 l_ledger_where VARCHAR2(500);
77 l_fud1_where VARCHAR2(240);
78 l_fud2_where VARCHAR2(240);
79 l_curr_view VARCHAR2(4);
80 l_ledger_id VARCHAR2(30);
81 l_url_ap VARCHAR2(500);
82 l_url_fa VARCHAR2(500);
83 l_url_other VARCHAR2(500);
84 l_url_common VARCHAR2(500);
85
86
87 BEGIN
88 -- initialization. Calling fii_ea_util_pkg APIs necessary for constructing
89 -- the PMV sql.
90
91 fii_ea_util_pkg.reset_globals;
92 fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);
93 l_ledger_where := fii_ea_util_pkg.get_ledger_for_detail;
94 l_fud1_where := fii_ea_util_pkg.get_fud1_for_detail;
95 l_fud2_where := fii_ea_util_pkg.get_fud2_for_detail;
96 l_curr_view := fii_ea_util_pkg.g_curr_view;
97 l_ledger_id := fii_ea_util_pkg.g_ledger_id;
98
99 -- ledger , fii_ledger_v is already joining to FII_SOURCE_LEDGER_GROUPS, FII_SLG_ASSIGNMENTS.
100 -- pslau: will review this code during perf testing
101
102 IF l_ledger_id = 'All' THEN
103 l_ledger_where := '';
104 ELSE
105 l_ledger_where := l_ledger_where;
106 end if;
107
108 -- constructing urls for drill-down reports. The drill-down reports depend on the FII_EA_COL_JE_SOURCE_CODE.
109 -- FII_EA_COL_JE_SOURCE_CODE is the look-up code which is based on the je_source.
110
111 l_url_common := '&FII_LEDGER=FII_EA_COL_LEDGER_ID&FII_CURRENCIES=FII_EA_COL_FUNC_CURRENCY&pParamIds=Y';
112
113 l_url_ap := 'pFunctionName=FII_EA_AP_TRAN&FII_EA_JE_SOURCE_GROUP=FII_EA_COL_JE_SOURCE_CODE'||l_url_common||'';
114 l_url_fa := 'pFunctionName=FII_EA_DPRN_EXP_MAJ'||l_url_common||'';
115 l_url_other := 'pFunctionName=FII_EA_JE_TRAN&FII_EA_JE_SOURCE_GROUP=FII_EA_COL_JE_SOURCE_CODE'||l_url_common||'';
116
117 -- A new look-up type named 'FII_EA_FUNCTIONAL_GROUP' is created and a bunch of look-up codes
118 -- are created thereunder. The look-up code value depends on the je_source value.
119
120 -- The Expense Source and Revenue Source reports are drill-down reports from Expense / Revenue Trend
121 -- by Account Detail reports for a specific company, cost-center and category. Hence in Expense / Revenue
122 -- Source reports - the company, cost-center and category parameters can never be 'All' .
123
124
125 l_sqlstmt:= '
126 SELECT
127 FII_EA_COL_LEDGER_ID,
128 FII_EA_COL_LEDGER,
129 FII_EA_COL_JOURNAL_SOURCE,
130 FII_EA_COL_JE_SOURCE_CODE,
131 FII_EA_COL_FUNC_CURRENCY,
132 FII_EA_FUNC_AMT,
133 FII_EA_XTD,
134 FII_EA_GT_XTD,
135 decode(FII_EA_COL_JOURNAL_SOURCE,''AP Translator'','''||l_url_ap||''', ''Payables'','''||l_url_ap||''',
136 ''Assets'','''||l_url_fa||''', '''||l_url_other||''') FII_EA_FUNC_AMT_DRILL
137 FROM (SELECT
138 sob.id FII_EA_COL_LEDGER_ID,
139 sob.value FII_EA_COL_LEDGER,
140 jes.user_je_source_name FII_EA_COL_JOURNAL_SOURCE,
141 f.je_source FII_EA_COL_JE_SOURCE_CODE,
142 f.functional_currency FII_EA_COL_FUNC_CURRENCY,
143 '||p_multi_factor||' * sum(f.amount_b) FII_EA_FUNC_AMT,
144 '||p_multi_factor||' * sum(f.amount_g) FII_EA_XTD,
145 '||p_multi_factor||' * sum(sum(f.amount_g)) over() FII_EA_GT_XTD,
146 (rank () OVER (ORDER BY NLSSORT(sob.value, ''NLS_SORT= BINARY'') ASC
147 nulls last)) - 1 rnk
148 from fii_gl_je_summary_b'||l_curr_view||' f,
149 fii_ledger_v sob,
150 fii_time_structures cal,
151 gl_je_sources_tl jes
152 where cal.report_date = :ASOF_DATE
153 and bitand(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND
154 and cal.time_id = f.time_id
155 and cal.period_type_id = f.period_type_id
156 and sob.id = f.ledger_id
157 and f.company_id = &FII_COMPANIES+FII_COMPANIES
158 and f.cost_center_id = &ORGANIZATION+HRI_CL_ORGCC
159 and f.fin_category_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM
160 AND f.je_source = jes.je_source_name
161 and jes.language = userenv(''LANG'')
162 '||l_ledger_where||l_fud1_where||l_fud2_where||'
163 group by sob.id,
164 sob.value,
165 jes.user_je_source_name,
166 f.je_source,
167 f.functional_currency)
168 WHERE ((rnk between &START_INDEX and &END_INDEX) or (&END_INDEX = -1))
169 ORDER BY NLSSORT(FII_EA_COL_LEDGER, ''NLS_SORT= BINARY'') ASC,NLSSORT(FII_EA_COL_JOURNAL_SOURCE,''NLS_SORT= BINARY'') ASC, FII_EA_XTD DESC, NLSSORT(FII_EA_COL_FUNC_CURRENCY, ''NLS_SORT= BINARY'') ASC nulls last';
170
171
172 RETURN l_sqlstmt;
173
174 end get_rev_exp_source;
175
176
177
178 END FII_EA_SOURCE_PKG;
179