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_EA_JE_TRAN_PKG

Source


1 PACKAGE BODY FII_EA_JE_TRAN_PKG AS
2 /* $Header: FIIEAJ1B.pls 120.4 2006/08/08 17:00:32 vkazhipu noship $ */
3 
4 
5 /* Getting category type for a category */
6 PROCEDURE get_cat_type
7 IS
8 
9 l_type VARCHAR2(100);
10 BEGIN
11 
12 	SELECT fin_cat_type_code
13 	INTO FII_EA_JE_TRAN_PKG.g_fin_type
14 	FROM fii_fin_cat_type_assgns
15 	WHERE fin_category_id= fii_ea_util_pkg.g_fin_category_id
16 	AND fin_cat_type_code IN ('EXP','R');
17 
18 EXCEPTION
19 WHEN no_data_found THEN
20    FII_EA_JE_TRAN_PKG.g_fin_type := NULL;
21 END get_cat_type;
22 
23 
24 PROCEDURE get_je_tran (
25         p_page_parameter_tbl    IN  BIS_PMV_PAGE_PARAMETER_TBL,
26         jrnl_dtl_sql             OUT NOCOPY VARCHAR2,
27         jrnl_dtl_output          OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
28 IS
29         l_as_of_date            DATE;
30         l_currency              VARCHAR2(240);
31         sqlstmt                 VARCHAR2(14000);
32 	l_ledger_where		VARCHAR2(1000);
33 	l_company_where		VARCHAR2(1000); -- To store company id dynamic where clause
34 	l_cost_center_where	VARCHAR2(1000); -- To store cost center dynamic where clause
35 	l_fud1_where		VARCHAR2(1000); -- To store user dim 1 dynamic where clause
36 	l_fud2_where		VARCHAR2(1000); -- To store user dim 2 dynamic where clause
37 	l_category_where	VARCHAR2(1000); -- To store categoty dynamic where clause
38 	l_source_where		VARCHAR2(1000); -- To store je source dynamic where clause
39 	l_entered_amount	VARCHAR2(1000); -- To store dynamic Select entered amt columns based on g_fin_type is Expense or Revenue.
40 	l_functional_amount	VARCHAR2(1000); -- To store dynamic Select functional amt columns based on g_fin_type is Expense or Revenue.
41 	l_gt_functional_amount	VARCHAR2(1000); -- To store dynamic Select grand total functional columns based on g_fin_type is Expense or Revenue.
42 	l_fud1_enabled_flag	VARCHAR2(1) := NULL;
43 	l_fud2_enabled_flag	VARCHAR2(1) := NULL;
44 
45 
46 BEGIN
47 
48 FII_EA_UTIL_PKG.reset_globals;
49 FII_EA_UTIL_PKG.get_parameters(p_page_parameter_tbl=>p_page_parameter_tbl);
50 
51 /* Getting category type for a category */
52 FII_EA_JE_TRAN_PKG.get_cat_type;
53 
54 /* Preparing Dynamic where clauses for PMV sql */
55 
56 l_ledger_where          := ' AND f.ledger_id = &FII_LEDGER+FII_LEDGER ';
57 l_category_where 	:= ' AND f.natural_account_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM ';
58 l_company_where 	:= ' f.company_id = &FII_COMPANIES+FII_COMPANIES ';
59 l_cost_center_where 	:= ' AND f.cost_center_id = &ORGANIZATION+HRI_CL_ORGCC ';
60 
61 
62 
63 l_fud1_where := fii_ea_util_pkg.get_fud1_for_detail;
64 l_fud2_where := fii_ea_util_pkg.get_fud2_for_detail;
65 
66 l_fud1_where := REPLACE(l_fud1_where,'f.fud1_id','f.user_dim1_id');
67 l_fud2_where := REPLACE(l_fud2_where,'f.fud2_id','f.user_dim2_id');
68 
69 
70 /* Selecting Chart of Accounts ID */
71 
72 	BEGIN
73 		SELECT chart_of_accounts_id INTO FII_EA_UTIL_PKG.g_coaid
74                 FROM gl_ledgers_public_v
75                 WHERE ledger_id = FII_EA_UTIL_PKG.g_ledger_id;
76 	END;
77 
78 /* Select columns based on call for Expense or Revenue */
79 
80 IF FII_EA_JE_TRAN_PKG.g_fin_type = 'EXP' THEN /* Expense*/
81 
82 	l_entered_amount := ' SUM(NVL(jln.entered_cr, 0) -  NVL(jln.entered_dr, 0))*-1 FII_EA_ENTERED_AMOUNT, ';
83 	l_functional_amount := ' SUM(NVL(jln.accounted_cr, 0) -  NVL(jln.accounted_dr, 0))*-1 FII_EA_FUNCTIONAL_AMOUNT, ';
84 	l_gt_functional_amount := ' SUM(SUM(NVL(jln.accounted_cr, 0) -NVL(jln.accounted_dr,0))*-1) OVER() FII_GT_EA_FUNCTIONAL_AMOUNT ';
85 
86 ELSIF FII_EA_JE_TRAN_PKG.g_fin_type = 'R' THEN /* Revenue */
87 
88 	l_entered_amount := ' SUM(NVL(jln.entered_cr, 0) -  NVL(jln.entered_dr, 0)) FII_EA_ENTERED_AMOUNT, ';
89 	l_functional_amount := ' SUM(NVL(jln.accounted_cr, 0) -  NVL(jln.accounted_dr, 0)) FII_EA_FUNCTIONAL_AMOUNT, ';
90 	l_gt_functional_amount := ' SUM(SUM(NVL(jln.accounted_cr, 0) -NVL(jln.accounted_dr,0))) OVER() FII_GT_EA_FUNCTIONAL_AMOUNT ';
91 
92 END IF;
93 
94 /* PMV SQL */
95 
96 sqlstmt :=
97 ' SELECT
98 	g.FII_EA_JOURNAL_NAME 		FII_EA_JOURNAL_NAME,
99 	g.FII_EA_CURRENCY 		FII_EA_CURRENCY,
100 	g.FII_EA_ENTERED_AMOUNT 	FII_EA_ENTERED_AMOUNT,
101 	g.FII_EA_FUNCTIONAL_AMOUNT 	FII_EA_FUNCTIONAL_AMOUNT,
102 	g.FII_EA_JOURNAL_DATE 		FII_EA_JOURNAL_DATE,
103 	g.FII_EA_CATEGORY 		FII_EA_CATEGORY,
104 	g.FII_EA_DESCRIPTION 		FII_EA_DESCRIPTION,
105 	g.FII_EA_SOURCE 		FII_EA_SOURCE,
106 	g.FII_GT_EA_FUNCTIONAL_AMOUNT 	FII_GT_EA_FUNCTIONAL_AMOUNT
107 FROM
108 (SELECT
109 	(rank () over(ORDER BY NLSSORT(f.name, ''NLS_SORT= BINARY'') nulls last,f.NAME)) -1 rnk,
110  	f.name 				FII_EA_JOURNAL_NAME,
111      	f.currency_code 		FII_EA_CURRENCY,
112 	'||l_entered_amount||'
113 	'||l_functional_amount||'
114      	jln.effective_date		FII_EA_JOURNAL_DATE,
115      	f.je_category 			FII_EA_CATEGORY,
116      	f.description 			FII_EA_DESCRIPTION,
117      	jes.user_je_source_name	 	FII_EA_SOURCE,
118 	'||l_gt_functional_amount||'
119  FROM 	gl_je_headers f,
120      	gl_je_lines jln,
121 	fii_gl_processed_header_ids fgl,
122 	gl_je_sources_tl jes
123 WHERE  	f.je_header_id = fgl.je_header_id
124 AND	f.je_header_id = jln.je_header_id
125 AND     f.je_source = :SOURCE_GROUP
126 '||l_ledger_where||'
127 AND     f.je_source = jes.je_source_name
128 AND  	f.actual_flag = ''A''
129 and 	jes.language =   userenv(''LANG'')
130 AND	jln.effective_date BETWEEN :CURR_PERIOD_START AND :ASOF_DATE
131 AND	jln.code_combination_id IN (SELECT f.code_combination_id
132 				   FROM fii_gl_ccid_dimensions f
133 			 	   WHERE '||l_company_where||'
134 				   '||l_cost_center_where||'
135 				   '||l_category_where||'
136 				   AND f.chart_of_accounts_id = :COAID
137 				   '||l_fud1_where||'
138 				   '||l_fud2_where||'
139 				   )
140 GROUP BY f.je_header_id, f.name, jln.effective_date, f.currency_code, f.je_category, f.description, jes.user_je_source_name) g
141 WHERE  (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
142 ORDER BY FII_EA_FUNCTIONAL_AMOUNT DESC  ';
143 
144 
145 -- Attach bind parameters
146 
147 FII_EA_UTIL_PKG.bind_variable(
148         p_sqlstmt=>sqlstmt,
149         p_page_parameter_tbl=>p_page_parameter_tbl,
150         p_sql_output=>jrnl_dtl_sql,
151         p_bind_output_table=>jrnl_dtl_output);
152 
153 END get_je_tran;
154 
155 --***********************************--
156 
157 /* Code for Journal Transaction Line Level Report */
158 
159 PROCEDURE get_je_line_tran (
160         p_page_parameter_tbl    IN  BIS_PMV_PAGE_PARAMETER_TBL,
161         jrnl_dtl_sql             OUT NOCOPY VARCHAR2,
162         jrnl_dtl_output          OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
163 IS
164         l_as_of_date            DATE;
165         l_currency              VARCHAR2(240);
166         sqlstmt                 VARCHAR2(14000);
167 	l_ledger_where		VARCHAR2(1000);
168 	l_company_where		VARCHAR2(1000); -- To store company id dynamic where clause
169 	l_cost_center_where	VARCHAR2(1000); -- To store cost center dynamic where clause
170 	l_fud1_where		VARCHAR2(1000); -- To store user dim 1 dynamic where clause
171 	l_fud2_where		VARCHAR2(1000); -- To store user dim 2 dynamic where clause
172 	l_category_where	VARCHAR2(1000); -- To store categoty dynamic where clause
173 	l_source_where		VARCHAR2(1000); -- To store je source dynamic where clause
174 	l_entered_amount	VARCHAR2(1000); -- To store dynamic Select entered amt columns based on g_fin_type is Expense or Revenue.
175 	l_functional_amount	VARCHAR2(1000); -- To store dynamic Select functional amt columns based on g_fin_type is Expense or Revenue.
176 	l_gt_functional_amount	VARCHAR2(1000); -- To store dynamic Select grand total functional columns based on g_fin_type is Expense or Revenue.
177 	l_fud1_enabled_flag	VARCHAR2(1) := NULL;
178 	l_fud2_enabled_flag	VARCHAR2(1) := NULL;
179 
180 
181 BEGIN
182 
183 FII_EA_UTIL_PKG.get_parameters(p_page_parameter_tbl=>p_page_parameter_tbl);
184 
185 /* Getting category type for a category */
186 FII_EA_JE_TRAN_PKG.get_cat_type;
187 
188 /* Preparing Dynamic where clauses for PMV sql */
189 
190 l_ledger_where          := ' AND f.ledger_id = &FII_LEDGER+FII_LEDGER ';
191 l_category_where 	:= ' AND f.natural_account_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM ';
192 l_company_where 	:= ' f.company_id = &FII_COMPANIES+FII_COMPANIES ';
193 l_cost_center_where 	:= ' AND f.cost_center_id = &ORGANIZATION+HRI_CL_ORGCC ';
194 
195 
196 l_fud1_where := fii_ea_util_pkg.get_fud1_for_detail;
197 l_fud2_where := fii_ea_util_pkg.get_fud2_for_detail;
198 
199 l_fud1_where := REPLACE(l_fud1_where,'f.fud1_id','f.user_dim1_id');
200 l_fud2_where := REPLACE(l_fud2_where,'f.fud2_id','f.user_dim2_id');
201 
202 
203 /* Selecting Chart of Accounts ID */
204 
205 	BEGIN
206 		SELECT chart_of_accounts_id INTO FII_EA_UTIL_PKG.g_coaid
207                 FROM gl_ledgers_public_v
208                 WHERE ledger_id = FII_EA_UTIL_PKG.g_ledger_id;
209 	END;
210 
211 /* Select columns based on call for Expense or Revenue */
212 
213 IF FII_EA_JE_TRAN_PKG.g_fin_type = 'EXP' THEN /* Expense*/
214 
215 	l_entered_amount := ' (NVL(jln.entered_cr, 0) -  NVL(jln.entered_dr, 0))*-1 FII_EA_ENTERED_AMOUNT, ';
216 	l_functional_amount := ' (NVL(jln.accounted_cr, 0) -  NVL(jln.accounted_dr, 0)) *-1 FII_EA_FUNCTIONAL_AMOUNT, ';
217 	l_gt_functional_amount := ' SUM((NVL(jln.accounted_cr, 0) -NVL(jln.accounted_dr,0)) *-1) OVER() FII_GT_EA_FUNCTIONAL_AMOUNT ';
218 
219 ELSIF FII_EA_JE_TRAN_PKG.g_fin_type = 'R' THEN /* Revenue */
220 
221 
222 	l_entered_amount := ' (NVL(jln.entered_cr, 0) -  NVL(jln.entered_dr, 0)) FII_EA_ENTERED_AMOUNT, ';
223 	l_functional_amount := ' (NVL(jln.accounted_cr, 0) -  NVL(jln.accounted_dr, 0))  FII_EA_FUNCTIONAL_AMOUNT, ';
224 	l_gt_functional_amount := ' SUM((NVL(jln.accounted_cr, 0) -NVL(jln.accounted_dr,0))) OVER() FII_GT_EA_FUNCTIONAL_AMOUNT ';
225 
226 END IF;
227 
228 /* PMV SQL */
229 sqlstmt :=
230 ' SELECT
231 	g.FII_EA_JOURNAL_NAME 		FII_EA_JOURNAL_NAME,
232 	g.FII_EA_CURRENCY 		FII_EA_CURRENCY,
233 	g.FII_EA_ENTERED_AMOUNT 	FII_EA_ENTERED_AMOUNT,
234 	g.FII_EA_LINE_NUMBER		FII_EA_LINE_NUMBER,
235 	g.FII_EA_ACCOUNT_NUMBER		FII_EA_ACCOUNT_NUMBER,
236 	g.FII_EA_FUNCTIONAL_AMOUNT 	FII_EA_FUNCTIONAL_AMOUNT,
237 	g.FII_EA_JOURNAL_DATE 		FII_EA_JOURNAL_DATE,
238 	g.FII_EA_CATEGORY 		FII_EA_CATEGORY,
239 	g.FII_EA_DESCRIPTION 		FII_EA_DESCRIPTION,
240 	g.FII_EA_SOURCE 		FII_EA_SOURCE,
241 	g.FII_EA_REFERENCE_1		FII_EA_REFERENCE_1,
242 	g.FII_EA_REFERENCE_2		FII_EA_REFERENCE_2,
243 	g.FII_EA_REFERENCE_3		FII_EA_REFERENCE_3,
244 	g.FII_EA_REFERENCE_4		FII_EA_REFERENCE_4,
245 	g.FII_EA_REFERENCE_5		FII_EA_REFERENCE_5,
246 	g.FII_EA_REFERENCE_6		FII_EA_REFERENCE_6,
247 	g.FII_EA_REFERENCE_7		FII_EA_REFERENCE_7,
248 	g.FII_EA_REFERENCE_8		FII_EA_REFERENCE_8,
249 	g.FII_EA_REFERENCE_9		FII_EA_REFERENCE_9,
250 	g.FII_EA_REFERENCE_10		FII_EA_REFERENCE_10,
251 	g.FII_GT_EA_FUNCTIONAL_AMOUNT 	FII_GT_EA_FUNCTIONAL_AMOUNT
252 FROM
253 (SELECT
254 	(rank () over(ORDER BY NLSSORT(f.name, ''NLS_SORT= BINARY'') nulls last,f.NAME,jln.effective_date,jln.je_line_num)) -1 rnk,
255  	f.name 				FII_EA_JOURNAL_NAME,
256      	f.currency_code 		FII_EA_CURRENCY,
257 	'||l_entered_amount||'
258 	jln.je_line_num			FII_EA_LINE_NUMBER,
259 	RTRIM(glc.concatenated_segments)	FII_EA_ACCOUNT_NUMBER,
260 	'||l_functional_amount||'
261      	jln.effective_date		FII_EA_JOURNAL_DATE,
262      	f.je_category 			FII_EA_CATEGORY,
263      	f.description 			FII_EA_DESCRIPTION,
264      	jes.user_je_source_name		FII_EA_SOURCE,
265 	TRIM(jln.reference_1)			FII_EA_REFERENCE_1,
266 	TRIM(jln.reference_2)			FII_EA_REFERENCE_2,
267 	TRIM(jln.reference_3)			FII_EA_REFERENCE_3,
268 	TRIM(jln.reference_4)			FII_EA_REFERENCE_4,
269 	TRIM(jln.reference_5)			FII_EA_REFERENCE_5,
270 	TRIM(jln.reference_6)			FII_EA_REFERENCE_6,
271 	TRIM(jln.reference_7)			FII_EA_REFERENCE_7,
272 	TRIM(jln.reference_8)			FII_EA_REFERENCE_8,
273 	TRIM(jln.reference_9)			FII_EA_REFERENCE_9,
274 	TRIM(jln.reference_10)			FII_EA_REFERENCE_10,
275 	'||l_gt_functional_amount||'
276  FROM 	gl_je_headers f,
277      	gl_je_lines jln,
278 	fii_gl_processed_header_ids fgl,
279 	gl_code_combinations_kfv glc,
280 	gl_je_sources_tl jes
281 WHERE  	f.je_header_id = fgl.je_header_id
282 AND	f.je_header_id = jln.je_header_id
283 AND     f.je_source = :SOURCE_GROUP
284 '||l_ledger_where||'
285 AND     f.je_source = jes.je_source_name
286 AND     f.actual_flag = ''A''
287 AND 	jes.language =   userenv(''LANG'')
288 AND	jln.effective_date BETWEEN :CURR_PERIOD_START AND :ASOF_DATE
289 AND 	jln.code_combination_id = glc.code_combination_id
290 AND 	glc.chart_of_accounts_id = :COAID
291 AND	jln.code_combination_id IN (SELECT f.code_combination_id
292 				   FROM fii_gl_ccid_dimensions f
293 			 	   WHERE '||l_company_where||'
294 				   '||l_cost_center_where||'
295 				   '||l_category_where||'
296 				   AND f.chart_of_accounts_id = :COAID
297 				   '||l_fud1_where||'
298 				   '||l_fud2_where||'
299 				   )
300 ) g
301 WHERE  (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
302 ORDER BY FII_EA_FUNCTIONAL_AMOUNT DESC  ';
303 
304 
305 
306 
307 -- Attach bind parameters
308 
309 FII_EA_UTIL_PKG.bind_variable(
310         p_sqlstmt=>sqlstmt,
311         p_page_parameter_tbl=>p_page_parameter_tbl,
312         p_sql_output=>jrnl_dtl_sql,
313         p_bind_output_table=>jrnl_dtl_output);
314 
315 END get_je_line_tran;
316 
317 
318 
319 END FII_EA_JE_TRAN_PKG;
320