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_PSI_JE_DTL_PKG

Source


1 PACKAGE BODY FII_PSI_JE_DTL_PKG AS
2 /* $Header: FIIPSIJEDTLB.pls 120.9 2006/08/22 07:35:00 wywong noship $ */
3 
4 
5 g_carryfwd_msg VARCHAR2(240) := fnd_message.get_string('FII','FII_PSI_CARRYFWD');
6 
7 PROCEDURE GET_ENCUM_JRNL (
8         p_page_parameter_tbl    IN  BIS_PMV_PAGE_PARAMETER_TBL,
9         jrnl_dtl_sql             OUT NOCOPY VARCHAR2,
10         jrnl_dtl_output          OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
11 IS
12         sqlstmt                 VARCHAR2(14000);
13 	l_company_where		VARCHAR2(1000); -- To store company id dynamic where clause
14 	l_cost_center_where	VARCHAR2(1000); -- To store cost center dynamic where clause
15 	l_fud1_where		VARCHAR2(1000); -- To store user dim 1 dynamic where clause
16 	l_fud2_where		VARCHAR2(1000); -- To store user dim 2 dynamic where clause
17 	l_category_where	VARCHAR2(1000); -- To store category dynamic where clause
18 	l_enc_category_where	VARCHAR2(1000); -- To store category dynamic where clause
19 
20 
21 BEGIN
22 
23 FII_EA_UTIL_PKG.reset_globals;
24 FII_EA_UTIL_PKG.get_parameters(p_page_parameter_tbl=>p_page_parameter_tbl);
25 
26 /* Preparing Dynamic where clauses for PMV sql */
27 
28 l_category_where 	:= ' AND f.natural_account_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM ';
29 l_enc_category_where 	:= ' AND f.fin_category_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM ';
30 l_company_where 	:= ' f.company_id = &FII_COMPANIES+FII_COMPANIES ';
31 l_cost_center_where 	:= ' AND f.cost_center_id = &ORGANIZATION+HRI_CL_ORGCC ';
32 
33 l_fud1_where := REPLACE(fii_ea_util_pkg.get_fud1_for_detail, 'fud1_id', 'user_dim1_id');
34 l_fud2_where := REPLACE(fii_ea_util_pkg.get_fud2_for_detail, 'fud2_id', 'user_dim2_id');
35 
36 SELECT	report_date_julian INTO fii_ea_util_pkg.g_curr_per_start_id
37 FROM fii_time_day
38 WHERE	report_date = fii_ea_util_pkg.g_curr_per_start;
39 
40 SELECT	report_date_julian INTO fii_ea_util_pkg.g_as_of_date_id
41 FROM fii_time_day
42 WHERE	report_date = fii_ea_util_pkg.g_as_of_date;
43 
44 /* For a Purchase Order, gl_je_lines.reference_2 and gl_je_lines.reference_4 store po_header_id and po_number respectively.
45 We join between gl_je_lines and po_distributions_all on po_header_id to fetch po_release_id (to be passed in PO drill).
46 While joining, we are using outer join since all encumbrance entries may not come through PO. */
47 
48 /* Bug 4586540. Since jln.reference_2 can also have alphanumeric values, using TO_NUMBER directly was leading to
49 'invalid number' error due to failure of TO_NUMBER function to convert alphanumeric values to numbers. So perf
50 team suggested to use TRANSLATE and TO_NUMBER functions together. */
51 
52 /* PMV SQL */
53 
54 sqlstmt :=
55 ' SELECT
56 	inline_view.FII_PSI_JOURNAL_NAME 	FII_PSI_JOURNAL_NAME,
57 	inline_view.FII_PSI_CURRENCY 		FII_PSI_CURRENCY,
58 	inline_view.FII_PSI_ENTERED_AMOUNT 	FII_PSI_ENTERED_AMOUNT,
59 	inline_view.FII_PSI_NET_AMOUNT 		FII_PSI_NET_AMOUNT,
60 	inline_view.FII_PSI_JOURNAL_DATE 	FII_PSI_JOURNAL_DATE,
61 	inline_view.FII_PSI_DOC_NUMBER 		FII_PSI_DOC_NUMBER,
62 	inline_view.FII_PSI_CATEGORY 		FII_PSI_CATEGORY,
63 	inline_view.FII_PSI_DESCRIPTION 	FII_PSI_DESCRIPTION,
64 	inline_view.FII_PSI_SOURCE 		FII_PSI_SOURCE,
65 	inline_view.FII_PSI_GT_NET_AMOUNT 	FII_PSI_GT_NET_AMOUNT,
66 	DECODE(inline_view.FII_PSI_CATEGORY, ''Purchases'', DECODE(inline_view.FII_PSI_DOC_NUMBER,NULL,'''',
67 		''pFunctionName=FII_EA_POA_DRILL&PoHeaderId='' || PO_HEADER_ID ||
68                      ''&PoReleaseId='' || PO_RELEASE_ID || ''&addBreadCrumb=Y&retainAM=Y''))	FII_PSI_DOC_NUMBER_DRILL
69 FROM
70 (SELECT
71  	f.name 				FII_PSI_JOURNAL_NAME,
72      	f.currency_code 		FII_PSI_CURRENCY,
73 	SUM(NVL(jln.entered_cr, 0) -  NVL(jln.entered_dr, 0))*-1 FII_PSI_ENTERED_AMOUNT,
74 	SUM(NVL(jln.accounted_cr, 0) -  NVL(jln.accounted_dr, 0))*-1 FII_PSI_NET_AMOUNT,
75      	jln.effective_date		FII_PSI_JOURNAL_DATE,
76 	jln.reference_4			FII_PSI_DOC_NUMBER,
77      	f.je_category 			FII_PSI_CATEGORY,
78      	f.description 			FII_PSI_DESCRIPTION,
79      	jes.user_je_source_name	 	FII_PSI_SOURCE,
80 	DECODE(LENGTH(TRANSLATE(jln.reference_2,''0''||TRANSLATE(jln.reference_2,''a0123456789'',''a''),''0''))
81                -LENGTH(jln.reference_2),
82                0, TO_NUMBER(jln.reference_2),
83                NULL) PO_HEADER_ID,
84         NULL                            PO_RELEASE_ID,
85 	(SUM(SUM(NVL(jln.accounted_cr, 0) -NVL(jln.accounted_dr,0))) OVER()) *-1 FII_PSI_GT_NET_AMOUNT
86  FROM 	gl_je_headers f,
87      	gl_je_lines jln,
88 	fii_gl_processed_header_ids fgl,
89 	gl_je_sources_tl jes
90 WHERE  	f.je_header_id = fgl.je_header_id
91 	AND	f.je_header_id = jln.je_header_id
92 	AND     f.je_source = jes.je_source_name
93 	AND  	f.actual_flag = ''E''
94 	AND 	jes.language =   userenv(''LANG'')
95 	AND	jln.effective_date BETWEEN :CURR_PERIOD_START AND :ASOF_DATE
96 	AND	jln.code_combination_id IN (	SELECT	f.code_combination_id
97 						FROM	fii_gl_ccid_dimensions f
98 			 			WHERE	'||l_company_where||'
99 							'||l_cost_center_where||'
100 							'||l_category_where||'
101 							'||l_fud1_where||'
102 							'||l_fud2_where||'
103 					    )
104 GROUP BY	f.je_header_id, f.name, jln.effective_date, jln.reference_4, f.currency_code,
105 		f.je_category, f.description, jes.user_je_source_name, jln.reference_2
106 
107 UNION ALL
108 
109 SELECT		'''||g_carryfwd_msg||''' 				FII_PSI_JOURNAL_NAME,
110 		functional_currency 		FII_PSI_CURRENCY,
111 		SUM(NVL(obligated_amount_prim, 0) +  NVL(committed_amount_prim, 0)+ NVL(other_amount_prim, 0))*-1 FII_PSI_ENTERED_AMOUNT,
112 		SUM(NVL(obligated_amount_prim, 0) +  NVL(committed_amount_prim, 0)+ NVL(other_amount_prim, 0))*-1 FII_PSI_NET_AMOUNT,
113 		posted_date		FII_PSI_JOURNAL_DATE,
114 		NULL FII_PSI_DOC_NUMBER,
115 		NULL 			FII_PSI_CATEGORY,
116 		'''||g_carryfwd_msg||''' 			FII_PSI_DESCRIPTION,
117 		NULL	 	FII_PSI_SOURCE,
118 		NULL		PO_HEADER_ID,
119 		NULL		PO_RELEASE_ID,
120 		(SUM(SUM(NVL(obligated_amount_prim, 0) +  NVL(committed_amount_prim, 0)+ NVL(other_amount_prim, 0))) OVER())*-1 FII_PSI_GT_NET_AMOUNT
121 
122 FROM 		fii_gl_enc_carryfwd_f f
123 
124 WHERE		'||l_company_where||l_cost_center_where||l_enc_category_where||l_fud1_where||l_fud2_where||'
125 		AND f.time_id between :CURR_PERIOD_START_ID AND :ASOF_DATE_ID
126 GROUP BY	functional_currency,posted_date ) inline_view
127 
128 ORDER BY	FII_PSI_JOURNAL_DATE DESC';
129 
130 
131 -- Attach bind parameters
132 
133 FII_EA_UTIL_PKG.bind_variable(
134         p_sqlstmt=>sqlstmt,
135         p_page_parameter_tbl=>p_page_parameter_tbl,
136         p_sql_output=>jrnl_dtl_sql,
137         p_bind_output_table=>jrnl_dtl_output);
138 
139 END GET_ENCUM_JRNL;
140 
141 --***********************************--
142 
143 /* Code for Budget Journal Entry Details Report */
144 
145 PROCEDURE GET_BUDGET_JRNL (
146         p_page_parameter_tbl    IN  BIS_PMV_PAGE_PARAMETER_TBL,
147         jrnl_dtl_sql             OUT NOCOPY VARCHAR2,
148         jrnl_dtl_output          OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
149 IS
150         l_prim_global_curr        VARCHAR2(20);
151         l_sec_global_curr         VARCHAR2(20);
152         sqlstmt                 VARCHAR2(14000);
153 	l_ledger_where		VARCHAR2(1000);
154 	l_company_where		VARCHAR2(1000); -- To store company id dynamic where clause
155 	l_cost_center_where	VARCHAR2(1000); -- To store cost center dynamic where clause
156 	l_fud1_where		VARCHAR2(1000); -- To store user dim 1 dynamic where clause
157 	l_fud2_where		VARCHAR2(1000); -- To store user dim 2 dynamic where clause
158 	l_category_where	VARCHAR2(1000); -- To store category dynamic where clause
159 
160         l_industry_profile      VARCHAR2(1);
161         l_budget_source         VARCHAR2(15);
162         l_currency_where        VARCHAR2(1000) := NULL;
163 
164 BEGIN
165 
166 FII_EA_UTIL_PKG.reset_globals;
167 FII_EA_UTIL_PKG.get_parameters(p_page_parameter_tbl=>p_page_parameter_tbl);
168 
169 /* Preparing Dynamic where clauses for PMV sql */
170 
171 l_category_where 	:= ' AND f.natural_account_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM ';
172 l_company_where 	:= ' f.company_id = &FII_COMPANIES+FII_COMPANIES ';
173 l_cost_center_where 	:= ' AND f.cost_center_id = &ORGANIZATION+HRI_CL_ORGCC ';
174 
175 l_fud1_where := REPLACE(fii_ea_util_pkg.get_fud1_for_detail, 'fud1_id', 'user_dim1_id');
176 l_fud2_where := REPLACE(fii_ea_util_pkg.get_fud2_for_detail, 'fud2_id', 'user_dim2_id');
177 
178 l_prim_global_curr := BIS_COMMON_PARAMETERS.get_currency_code;
179 l_sec_global_curr  := BIS_COMMON_PARAMETERS.get_secondary_currency_code;
180 
181 /* Bugfix 5470346
182    - If industry = 'G', add: currency_code = primary currency
183                              and currency code != 'STAT'
184    - If budget source = 'GL', add: currency_code in (primary, secondary currency)
185                                    and currency_code != 'STAT' */
186 -- Find out if this is commercial or government install
187 l_industry_profile := FND_PROFILE.value('INDUSTRY');
188 
189 -- Find out the source of budget
190 l_budget_source := FND_PROFILE.value('FII_BUDGET_SOURCE');
191 
192 -- Set currency where clause depends on industry and budget source profile
193 IF (l_industry_profile = 'G') THEN
194   l_currency_where := ' AND f.currency_code = '''||l_prim_global_curr||'''
195                         AND f.currency_code != ''STAT''';
196 ELSIF (l_budget_source = 'GL') THEN
197   l_currency_where := ' AND f.currency_code IN ('''||l_prim_global_curr||''',
198                                                 '''||l_sec_global_curr||''')
199                         AND f.currency_code != ''STAT''';
200 END IF;
201 
202 /* PMV SQL */
203 sqlstmt :=
204 ' SELECT
205 	inline_view.FII_PSI_JOURNAL_NAME 	FII_PSI_JOURNAL_NAME,
206 	inline_view.FII_PSI_CURRENCY 		FII_PSI_CURRENCY,
207 	inline_view.FII_PSI_ENTERED_AMOUNT 	FII_PSI_ENTERED_AMOUNT,
208 	inline_view.FII_PSI_LINE_AMOUNT 	FII_PSI_LINE_AMOUNT,
209 	inline_view.FII_PSI_JOURNAL_DATE 	FII_PSI_JOURNAL_DATE,
210 	inline_view.FII_PSI_DOC_NUMBER 		FII_PSI_DOC_NUMBER,
211 	inline_view.FII_PSI_CATEGORY 		FII_PSI_CATEGORY,
212 	inline_view.FII_PSI_DESCRIPTION 	FII_PSI_DESCRIPTION,
213 	inline_view.FII_PSI_SOURCE 		FII_PSI_SOURCE,
214 	inline_view.FII_PSI_GT_LINE_AMOUNT 	FII_PSI_GT_LINE_AMOUNT
215 FROM
216 	(SELECT
217 	 	f.name 				FII_PSI_JOURNAL_NAME,
218      		f.currency_code 		FII_PSI_CURRENCY,
219 		SUM(NVL(jln.entered_cr, 0) -  NVL(jln.entered_dr, 0))*-1 FII_PSI_ENTERED_AMOUNT,
220 		SUM(NVL(jln.accounted_cr, 0) -  NVL(jln.accounted_dr, 0))*-1 FII_PSI_LINE_AMOUNT,
221 	     	jln.effective_date		FII_PSI_JOURNAL_DATE,
222 		jln.reference_4			FII_PSI_DOC_NUMBER,
223 	     	f.je_category 			FII_PSI_CATEGORY,
224      		f.description 			FII_PSI_DESCRIPTION,
225 	     	jes.user_je_source_name	 	FII_PSI_SOURCE,
226 		(SUM(SUM(NVL(jln.accounted_cr, 0) -NVL(jln.accounted_dr,0))) OVER())*-1 FII_PSI_GT_LINE_AMOUNT
227 
228 	 FROM 	gl_je_headers f,
229      		gl_je_lines jln,
230 		gl_je_sources_tl jes,
231 		gl_ledgers_public_v sob,
232                 gl_periods per,
233 		fii_slg_budget_asgns slba
234 	WHERE  	f.je_header_id = jln.je_header_id
235 		AND     f.je_source = jes.je_source_name
236 		AND  	f.actual_flag = ''B''
237 		AND     f.status = ''P''
238                 AND     sob.ledger_id = f.ledger_id
239                 AND     per.period_set_name = sob.period_set_name
240                 AND     f.posted_date between per.start_date and per.end_date
241                 AND     per.adjustment_period_flag = ''N''
242 		AND  	per.period_type = sob.accounted_period_type
243 		AND 	jes.language =   userenv(''LANG'')
244 		AND	jln.effective_date BETWEEN :CURR_PERIOD_START AND :ASOF_DATE
245 		AND	f.budget_version_id = slba.budget_version_id '||l_currency_where||'
246 		AND	jln.code_combination_id IN (    SELECT  f.code_combination_id
247 							FROM	fii_gl_ccid_dimensions f
248 				 			WHERE   '||l_company_where||l_cost_center_where||l_category_where
249 								||l_fud1_where||l_fud2_where||'
250 						    )
251 	GROUP BY	f.je_header_id, f.name, jln.effective_date, jln.reference_4, f.currency_code,
252 			f.je_category, f.description, jes.user_je_source_name
253 
254 UNION ALL
255 
256 SELECT		'''||g_carryfwd_msg||'''	FII_PSI_JOURNAL_NAME,
257 		sob.currency_code 		FII_PSI_CURRENCY,
258 		SUM(NVL(b.begin_balance_cr,0) - NVL(b.begin_balance_dr,0))*-1 FII_PSI_ENTERED_AMOUNT,
259 	        SUM(NVL(b.begin_balance_cr,0) - NVL(b.begin_balance_dr,0))*-1 FII_PSI_LINE_AMOUNT,
260 		p.start_date		FII_PSI_JOURNAL_DATE,
261 		NULL FII_PSI_DOC_NUMBER,
262 		NULL 			FII_PSI_CATEGORY,
263 		'''||g_carryfwd_msg||''' 			FII_PSI_DESCRIPTION,
264 		NULL	 	FII_PSI_SOURCE,
265 		(SUM(SUM(NVL(b.begin_balance_cr,0) - NVL(b.begin_balance_dr,0))) OVER())*-1 FII_GT_PSI_LINE_AMOUNT
266 
267  FROM		FII_SOURCE_LEDGER_GROUPS fslg,
268 	        FII_SLG_ASSIGNMENTS      slga,
269 		FII_SLG_BUDGET_ASGNS     slba,
270 		FII_GL_CCID_DIMENSIONS   f,
271 		FII_FIN_CAT_TYPE_ASSGNS  fcta,
272 		GL_BALANCES              b,
273 		GL_PERIODS               p,
274 	        GL_LEDGERS_PUBLIC_V      sob
275 
276  WHERE		fslg.usage_code = ''DBI''
277 		AND   slga.source_ledger_group_id = fslg.source_ledger_group_id
278 		AND   slba.source_ledger_group_id = slga.source_ledger_group_id
279 		AND   slba.ledger_id = slga.ledger_id
280 		AND   sob.ledger_id = slba.ledger_id
281 		AND   p.period_set_name = sob.period_set_name
282 		AND   p.period_type     = sob.accounted_period_type
283 		AND   p.period_num      = 1
284 		AND   p.start_date BETWEEN :CURR_PERIOD_START AND :ASOF_DATE
285 		AND   b.actual_flag     = ''B''
286 		AND   b.period_name     = p.period_name
287 		AND   b.ledger_id = slga.ledger_id
288 		AND   b.budget_version_id = slba.budget_version_id
289 		AND   b.currency_code = '''||l_prim_global_curr||'''
290                 AND   b.currency_code != ''STAT''
291 		AND  (b.begin_balance_dr <> 0 OR b.begin_balance_cr <> 0)
292 		AND   b.code_combination_id = f.code_combination_id
293 		AND '||l_company_where||l_cost_center_where||l_category_where||l_fud1_where||l_fud2_where||'
294 		AND   (f.company_id = slga.bal_seg_value_id OR
295 			slga.bal_seg_value_id = -1)
296 		AND   f.chart_of_accounts_id = slga.chart_of_accounts_id
297 		AND   fcta.fin_category_id = f.natural_account_id
298 		AND   fcta.fin_cat_type_code = ''OE''
299 
300 GROUP BY	sob.currency_code, p.start_date ) inline_view
301 
302 ORDER BY	FII_PSI_JOURNAL_DATE 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_BUDGET_JRNL;
316 
317 END FII_PSI_JE_DTL_PKG;
318