[Home] [Help]
PACKAGE BODY: APPS.FII_GL_SRC_INV_PKG
Source
1 PACKAGE BODY FII_GL_SRC_INV_PKG AS
2 /* $Header: FIIGLSRB.pls 120.46 2006/08/04 07:38:11 sajgeo noship $ */
3
4 PROCEDURE get_exp_source (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
5 exp_source_sql out NOCOPY VARCHAR2, exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL, fin_type IN VARCHAR2)
6 IS
7 sqlstmt VARCHAR2(32000);
8 sqlstmt2 VARCHAR2(500);
9 l_cal_join VARCHAR2(10000);
10 l_rpt_struct_join VARCHAR2(100);
11 l_time_parameter VARCHAR2(100);
12 l_bitmask NUMBER;
13 l_ccc_join VARCHAR2(500);
14 l_lob_join VARCHAR2(500);
15 l_amount_col VARCHAR2(500):= NULL;
16 l_sign VARCHAR2(1):=NULL;
17 l_source_misc VARCHAR2(50);
18
19
20 BEGIN
21
22 fii_gl_util_pkg.reset_globals;
23 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
24 fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
25 fii_gl_util_pkg.get_bitmasks;
26 fii_gl_util_pkg.g_fin_type := fin_type;
27 fii_gl_util_pkg.get_mgr_pmv_sql;
28 fii_gl_util_pkg.get_lob_pmv_sql;
29 fii_gl_util_pkg.get_non_ag_cat_pmv_sql;
30 fii_gl_util_pkg.get_ccc_pmv_sql;
31
32
33 /* -------------------------------------------------------------------------
34 * Check if we are looking at the current month. If so, we need PTD amounts.
35 * Otherwise, we need to find out the amounts for the whole month
36 * -------------------------------------------------------------------------
37 Commented for bug 5002688. Calculation of l_bitmask was already being done in util pkg. so, picking it from util now
38
39 select nvl(to_char(min(ent_period_id)),fii_gl_util_pkg.g_month_id) into l_time_parameter
40 from fii_time_ent_period
41 where fii_gl_util_pkg.g_as_of_date between start_date and end_date;
42
43 IF (fii_gl_util_pkg.g_month_id <> l_time_parameter) THEN
44 l_bitmask := 256;
45 ELSE
46 l_bitmask := 23;
47 END IF; */
48
49 /************************************************************
50 * FII_MEASURE1 = Source ID, not display in the report *
51 * FII_MEASURE4 = Manager ID, not display in the report *
52 * FII_MEASURE5 = Cost Center ID, not display in the report *
53 * FII_MEASURE6 = Currency ID, not display in the report *
54 * FII_MEASURE7 = Category ID, not display in the report *
55 * FII_MEASURE8 = Month ID, not display in the report *
56 * FII_MEASURE3 = Source Name, display in the report *
57 * FII_MEASURE2 = Actual Amount *
58 ************************************************************/
59
60 IF fii_gl_util_pkg.g_ccc_join IS NOT NULL THEN
61 l_ccc_join :=
62 REPLACE(fii_gl_util_pkg.g_ccc_join,'f.cost_center_org_id','m.company_cost_center_org_id');
63 END IF;
64
65 IF fii_gl_util_pkg.g_lob_join IS NOT NULL THEN
66
67 l_lob_join :=
68 REPLACE(fii_gl_util_pkg.g_lob_join,'f.line_of_business_id','m.parent_lob_id');
69
70 END IF ;
71
72
73 IF fii_gl_util_pkg.g_fin_type <> 'R' THEN
74 l_sign := '-';
75 END IF;
76
77 IF fii_gl_util_pkg.g_currency = 'FII_GLOBAL1' THEN
78 l_amount_col := 'prim_amount_g';
79 ELSIF fii_gl_util_pkg.g_currency = 'FII_GLOBAL2' THEN
80 l_amount_col := 'sec_amount_g';
81 ELSE
82 l_amount_col := 'prim_amount_g';
83 END IF;
84
85 l_source_misc :=
86 ltrim(rtrim(fnd_message.get_string('FII','FII_GL_SOURCE_MISC')));
87
88 sqlstmt :=
89 '
90 SELECT
91 FII_MEASURE1,
92 :MGR_ID FII_MEASURE4,
93 :CCC_ID FII_MEASURE5,
94 :CURRENCY FII_MEASURE6,
95 :FIN_ID FII_MEASURE7,
96 :MONTH_ID FII_MEASURE8,
97 FII_MEASURE3,
98 SUM(FII_MEASURE2) FII_MEASURE2,
99 :LOB_ID FII_ATTRIBUTE2,
100 CASE WHEN je_source_name = ''Payables'' AND :FIN_TYPE = ''OE''
101 THEN ''pFunctionName=FII_GL_INV_EXP_DET_R&pParamIds=Y'' ELSE
102 CASE WHEN je_source_name = ''Payables'' AND :FIN_TYPE = ''CGS''
103 THEN ''pFunctionName=FII_GL_INV_COR_DET_R&pParamIds=Y'' ELSE
104 CASE WHEN je_source_name = ''Receivables'' AND FII_MEASURE3 <>
105 '''||'''||user_je_source_name||'' ''||'''||l_source_misc||'''||'''||'''
106 AND :FIN_TYPE = ''R''
107 THEN ''pFunctionName=FII_GL_INV_REV_DET_R&pParamIds=Y''
108 ELSE
109 ''''
110 end
111 end
112 end FII_URL
113 FROM (
114 SELECT
115 f.je_source FII_MEASURE1,
116 DECODE(src.je_source_name, ''Receivables'', DECODE(f.je_category,
117 ''Misc Receipts'', '''||'''||src.user_je_source_name||'' ''
118 ||'''||l_source_misc||'''||'''||''',
119 src.user_je_source_name), src.user_je_source_name) FII_MEASURE3,
120 SUM('||l_sign||l_amount_col||') FII_MEASURE2,
121 src.je_source_name je_source_name,
122 src.user_je_source_name user_je_source_name
123 FROM fii_gl_je_summary_b f,
124 fii_com_cc_mappings m,
125 fii_fin_cat_mappings c,
126 fii_time_rpt_struct cal,
127 gl_je_sources_tl src,
128 fii_cc_mgr_hierarchies h
129 '||fii_gl_util_pkg.g_lob_from_clause||'
130 WHERE f.company_id = m.company_id
131 AND f.cost_center_id = m.cost_center_id
132 AND m.valid_mgr_flag = ''Y''
133 AND c.child_fin_cat_id = f.fin_category_id
134 AND src.je_source_name = f.je_source
135 AND h.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
136 AND h.emp_id = m.manager_id
137 AND f.period_type_id = cal.period_type_id
138 AND cal.time_id = f.time_id
139 AND cal.report_date = to_date(:P_AS_OF, ''DD-MM-YYYY'')
140 AND BITAND(cal.record_type_id, :BITMASK)= cal.record_type_id
141 '||l_ccc_join||l_lob_join||'
142 AND src.language = userenv(''LANG'')
143 AND f.company_id = m.company_id
144 AND f.cost_center_id = m.cost_center_id
145 AND c.child_fin_cat_id = f.fin_category_id
146 AND c.parent_fin_cat_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM
147 GROUP BY f.je_source, src.user_je_source_name,
148 src.je_source_name,f.je_category)
149 GROUP BY FII_MEASURE1, FII_MEASURE3, je_source_name,user_je_source_name ';
150
151
152
153 /*
154 sqlstmt :=
155 ' select f.je_source FII_MEASURE1,
156 :MGR_ID FII_MEASURE4,
157 :CCC_ID FII_MEASURE5,
158 :CURRENCY FII_MEASURE6,
159 :FIN_ID FII_MEASURE7,
160 :MONTH_ID FII_MEASURE8,
161 src.user_je_source_name FII_MEASURE3,
162 sum(f.actual_g) FII_MEASURE2,
163 :LOB_ID FII_ATTRIBUTE2,
164 case when src.je_source_name = ''Payables'' and :FIN_TYPE = ''OE''
165 then ''pFunctionName=FII_GL_INV_EXP_DET_R&pParamIds=Y'' else
166 case when src.je_source_name = ''Payables'' and :FIN_TYPE = ''CGS''
167 then ''pFunctionName=FII_GL_INV_COR_DET_R&pParamIds=Y'' else
168 case when src.je_source_name = ''Receivables'' and :FIN_TYPE = ''R''
169 then ''pFunctionName=FII_GL_INV_REV_DET_R&pParamIds=Y''
170 else
171 ''''
172 end
173 end
174 end FII_URL
175 from fii_gl_base_v'|| fii_gl_util_pkg.g_global_curr_view ||' f,
176 fii_time_rpt_struct cal,
177 gl_je_sources_tl src,
178 fii_cc_mgr_hierarchies h
179 '||fii_gl_util_pkg.g_non_ag_cat_from_clause||fii_gl_util_pkg.g_lob_from_clause||'
180 where src.je_source_name = f.je_source
181 and h.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
182 and h.emp_id = f.manager_id
183 and f.period_type_id = cal.period_type_id
184 and cal.time_id = f.time_id
185 and cal.report_date = to_date(:P_AS_OF, ''DD-MM-YYYY'')
186 and bitand(cal.record_type_id, :BITMASK)= cal.record_type_id
187 '||fii_gl_util_pkg.g_non_ag_cat_join||fii_gl_util_pkg.g_ccc_join||fii_gl_util_pkg.g_lob_join||'
188 and src.language = userenv(''LANG'')
189 group by f.je_source, src.user_je_source_name, src.je_source_name';
190 */
191
192
193 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_source_sql, exp_source_output);
194
195 END get_exp_source;
196
197 PROCEDURE get_rev_src (
198 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, rev_src_sql out NOCOPY VARCHAR2, rev_src_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
199 l_fin_type VARCHAR2(1);
200 BEGIN
201 l_fin_type := 'R';
202 fii_gl_src_inv_pkg.get_exp_source(p_page_parameter_tbl, rev_src_sql, rev_src_output, l_fin_type );
203
204 END get_rev_src;
205
206 PROCEDURE get_exp_src (
207 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, exp_src_sql out NOCOPY VARCHAR2, exp_src_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
208 l_fin_type VARCHAR2(2);
209 BEGIN
210 l_fin_type := 'OE';
211 fii_gl_src_inv_pkg.get_exp_source(p_page_parameter_tbl, exp_src_sql, exp_src_output, l_fin_type );
212
213 END get_exp_src;
214
215 PROCEDURE get_cogs_src (
216 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, cogs_src_sql out NOCOPY VARCHAR2,cogs_src_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
217 l_fin_type VARCHAR2(3);
218 BEGIN
219 l_fin_type := 'CGS';
220 fii_gl_src_inv_pkg.get_exp_source(p_page_parameter_tbl, cogs_src_sql, cogs_src_output, l_fin_type );
221
222 END get_cogs_src;
223
224 PROCEDURE get_inv_exp_det (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
225 inv_exp_det_sql out NOCOPY VARCHAR2, inv_exp_det_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
226 IS
227 inv_exp_det_rec BIS_QUERY_ATTRIBUTES;
228 sqlstmt VARCHAR2(32000);
229 l_ccc_join VARCHAR2(500) := NULL;
230 l_lob_join VARCHAR2(500) := NULL;
231 l_from_clause VARCHAR2(100);
232 l_hint VARCHAR2(100) := NULL;
233 l_prim_sec VARCHAR2(30);
234
235
236 BEGIN
237 fii_gl_util_pkg.reset_globals;
238 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
239 fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
240 fii_gl_util_pkg.get_bitmasks;
241 IF ((fii_gl_util_pkg.g_ccc_id IS NOT NULL) AND (fii_gl_util_pkg.g_ccc_id <> -999)) THEN
242 IF (fii_gl_util_pkg.ccc_within_mgr_lob(fii_gl_util_pkg.g_ccc_id, fii_gl_util_pkg.g_lob_id, fii_gl_util_pkg.g_mgr_id) = 'Y') THEN
243 l_ccc_join := ' and com.company_cost_center_org_id = &ORGANIZATION+HRI_CL_ORGCC
244 and com.company_id = f.company_id
245 and com.cost_center_id = f.cost_center_id ';
246 l_from_clause := ', fii_com_cc_mappings com';
247 ELSE
248 l_ccc_join := ' and 1 = 2 ';
249 END IF;
250 ELSIF (fii_gl_util_pkg.g_lob_id IS NOT NULL) AND (fii_gl_util_pkg.g_lob_is_top_node <> 'Y') THEN
251
252
253 l_lob_join := ' and lob.parent_lob_id = :LOB_ID
254 and lob.child_lob_id = com.parent_lob_id
255 and com.company_id = f.company_id
256 and com.cost_center_id = f.cost_center_id
257 and mgr.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
258 and mgr.emp_id = com.parent_manager_id';
259 l_from_clause := ', fii_com_cc_mappings com ,
260 fii_lob_hierarchies lob,
261 fii_cc_mgr_hierarchies mgr';
262 l_hint := '/*+leading(lob) */';
263 ELSIF (fii_gl_util_pkg.g_lob_id IS NULL) OR (fii_gl_util_pkg.g_lob_is_top_node = 'Y') THEN
264
265
266 l_lob_join := ' and com.company_id = f.company_id
267 and com.cost_center_id = f.cost_center_id
268 and mgr.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
269 and mgr.emp_id = com.parent_manager_id';
270 l_from_clause := ', fii_com_cc_mappings com,
271 fii_cc_mgr_hierarchies mgr';
272 l_hint := '/*+ leading(mgr) index(fi, fii_fin_cat_mappings_n1) */';
273
274 END IF;
275
276 IF fii_gl_util_pkg.g_global_curr_view = '1' THEN
277 l_prim_sec := 'prim_amount_g';
278 ELSE
279 l_prim_sec := 'sec_amount_g';
280 END IF;
281
282 /* As bug fix for 3597165, changed hint to use fii_fin_cat_mappings_n1 (instead of fii_fin_cat_mappings_n3) and moved group by in the inner sql */
283
284 /*******************************************************
285 * FII_MEASURE1 = Invoice # *
286 * FII_MEASURE2 = Invoice Date *
287 * FII_MEASURE3 = Invoice Description *
288 * FII_MEASURE4 = Supplier *
289 * FII_MEASURE5 = Actual Amount *
290 *******************************************************/
291
292
293 sqlstmt := '
294 SELECT inline.invoice_num FII_MEASURE1,
295 inline.invoice_date FII_MEASURE2,
296 ai.description FII_MEASURE3,
297 pov.vendor_name FII_MEASURE4,
298 inline.actual_g FII_MEASURE5,
299 inline.gt_actual_g FII_ATTRIBUTE1
300 FROM po_vendors pov,
301 ap_invoices_all ai,
302 ( SELECT F.invoice_num invoice_num,
303 F.invoice_date invoice_date,
304 F.supplier_id supplier_id,
305 F.invoice_id invoice_id,
306 SUM('||l_prim_sec||') actual_g,
307 SUM(SUM('||l_prim_sec||')) OVER() gt_actual_g
308 FROM FII_AP_INV_B F,
309 FII_Fin_Cat_Mappings FI,
310 FII_Fin_Item_Hierarchies HIER,
311 GL_Import_References GIR,
312 FII_GL_Processed_Header_IDS PH
313 ' || l_from_clause || '
314 WHERE HIER.Parent_Fin_Cat_ID = &FINANCIAL ITEM+GL_FII_FIN_ITEM
315 AND HIER.Child_Fin_Cat_ID = FI.Parent_Fin_Cat_ID
316 and f.account_date between to_date(:P_DET_START, ''DD-MM-YYYY'') and to_date(:P_DET_END, ''DD-MM-YYYY'')
317 AND FI.Child_Fin_Cat_ID = F.Fin_Category_ID
318 ' || l_lob_join || l_ccc_join || '
319 AND F.GL_SL_Link_ID = GIR.Gl_SL_Link_ID
320 AND F.GL_SL_Link_Table = GIR.GL_SL_Link_Table
321 AND GIR.JE_Header_ID = PH.JE_Header_ID
322 GROUP BY F.invoice_num, F.invoice_id, F.supplier_id, F.invoice_date ) inline
323 WHERE inline.supplier_id=pov.vendor_id
324 AND inline.invoice_id=ai.invoice_id';
325
326
327
328 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, inv_exp_det_sql, inv_exp_det_output);
329
330 END get_inv_exp_det;
331
332 PROCEDURE get_inv_rev_det (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
333 inv_rev_det_sql out NOCOPY VARCHAR2, inv_rev_det_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
334 IS
335 sqlstmt VARCHAR2(32000);
336 l_ccc_join VARCHAR2(500) := NULL;
337 l_lob_join VARCHAR2(500) := NULL;
338 l_from_clause VARCHAR2(100) := NULL;
339 l_hint VARCHAR2(100) := NULL;
340
341 BEGIN
342
343 fii_gl_util_pkg.reset_globals;
344 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
345 fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
346 fii_gl_util_pkg.get_bitmasks;
347 IF ((fii_gl_util_pkg.g_ccc_id IS NOT NULL) AND (fii_gl_util_pkg.g_ccc_id <> -999)) THEN
348 IF (fii_gl_util_pkg.ccc_within_mgr_lob(fii_gl_util_pkg.g_ccc_id, fii_gl_util_pkg.g_lob_id, fii_gl_util_pkg.g_mgr_id) = 'Y') THEN
349 l_ccc_join := ' and com.company_cost_center_org_id = &ORGANIZATION+HRI_CL_ORGCC
350 and com.company_id = f.company_id
351 and com.cost_center_id = f.cost_center_id ';
352 l_from_clause := ', fii_com_cc_mappings com';
353 ELSE
354 l_ccc_join := ' and 1 = 2 ';
355 END IF;
356 ELSIF (fii_gl_util_pkg.g_lob_id IS NOT NULL) AND (fii_gl_util_pkg.g_lob_is_top_node <> 'Y') THEN
357 l_lob_join := ' and lob.parent_lob_id = :LOB_ID
358 and lob.child_lob_id = com.parent_lob_id
359 and com.company_id = f.company_id
360 and com.cost_center_id = f.cost_center_id
361 and mgr.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
362 and mgr.emp_id = com.parent_manager_id';
363 l_from_clause := ', fii_com_cc_mappings com,
364 fii_lob_hierarchies lob,
365 fii_cc_mgr_hierarchies mgr';
366 l_hint := '/*+leading(lob) */';
367 ELSIF (fii_gl_util_pkg.g_lob_id IS NULL) OR (fii_gl_util_pkg.g_lob_is_top_node = 'Y') THEN
368 l_lob_join := ' and com.company_id = f.company_id
369 and com.cost_center_id = f.cost_center_id
370 and mgr.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
371 and mgr.emp_id = com.parent_manager_id';
372 l_from_clause := ', fii_com_cc_mappings com,
373 fii_cc_mgr_hierarchies mgr';
374 l_hint := '/*+ leading(mgr) index(cat, fii_fin_cat_mappings_n1) */';
375
376 END IF;
377
378 /* As bug fix for 3597183, changed hint to use fii_fin_cat_mappings_n1 (instead of fii_fin_cat_mappings_n3) and moved group by in the inner sql */
379 /*******************************************************
380 * FII_MEASURE1 = Cost Center *
381 * FII_MEASURE2 = Acct *
382 * FII_MEASURE3 = Account Name *
383 * FII_MEASURE4 = Customer *
384 * FII_MEASURE5 = Invoice Number *
385 * FII_MEASURE6 = Invoice Date *
386 * FII_MEASURE7 = Amount *
387 *******************************************************/
388
389 sqlstmt := '
390 select fnd1.flex_value FII_MEASURE1,
391 inline.fin_category_id FII_MEASURE2,
392 fnd.description FII_MEASURE3,
393 cust.party_name FII_MEASURE4,
394 inline.invoice_number FII_MEASURE5,
395 inline.invoice_date FII_MEASURE6,
396 inline.sum_actual_g FII_MEASURE7,
397 inline.gt_actual_g FII_ATTRIBUTE1
398 from hz_parties cust,
399 fnd_flex_values_tl fnd,
400 fnd_flex_values fnd1,
401
402 (select '||l_hint||'
403 f.cost_center_id cost_center_id,
404 f.fin_category_id fin_category_id,
405 f.invoice_number invoice_number,
406 to_char(f.invoice_date,''DD-MON-YYYY'') invoice_date,
407 sum(f.actual_g) sum_actual_g,
408 sum(sum(f.actual_g)) over() gt_actual_g,
409 f.bill_to_party_id bill_to_party_id
410 from fii_ar_revenue_b_v'||fii_gl_util_pkg.g_global_curr_view||' f,
411 fii_fin_cat_mappings cat,
412 fii_fin_item_hierarchies hier'||l_from_clause||'
413 where f.posted_flag = ''Y'''||l_ccc_join||l_lob_join||'
414 and f.gl_date between to_date(:P_DET_START, ''DD-MM-YYYY'') and to_date(:P_DET_END, ''DD-MM-YYYY'')
415 and hier.parent_fin_cat_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM
416 and hier.child_fin_cat_id = cat.parent_fin_cat_id
417 and cat.child_fin_cat_id = f.fin_category_id
418 group by f.invoice_date,
419 f.bill_to_party_id,
420 f.cost_center_id,
421 f.fin_category_id,
422 f.invoice_number) inline
423
424 where cust.party_id = inline.bill_to_party_id
425 and fnd1.flex_value_id = inline.cost_center_id
426 and fnd.flex_value_id = inline.fin_category_id
427 and fnd.language = userenv(''Lang'')';
428
429
430 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, inv_rev_det_sql, inv_rev_det_output);
431
432
433 END get_inv_rev_det;
434
435 PROCEDURE fii_drill_across(pSource IN varchar2,
436 pCategory IN varchar2, pCostCenter IN varchar2, pMonth IN varchar2,
437 pCurrency IN varchar2, pManager IN varchar2, pAsOfDateValue IN varchar2, pLOB IN varchar2) IS
438
439 l_FunctionName varchar2(100);
440 l_fin_leaf_node varchar2(1);
441
442 BEGIN
443
444 if pSource = 'Payables' then
445
446 select FunctionName into l_FunctionName from
447 (select 'FII_GL_INV_EXP_DET_R' FunctionName
448 from fii_fin_item_hierarchies cat
449 where cat.parent_fin_cat_id in (select fin_category_id from fii_fin_cat_type_assgns where FIN_CAT_TYPE_CODE = 'OE' and TOP_NODE_FLAG = 'Y')
450 and cat.child_fin_cat_id=pCategory
451 union all
452 select 'FII_GL_INV_COR_DET_R' FunctionName
453 from fii_fin_item_hierarchies cat
454 where cat.parent_fin_cat_id in (select fin_category_id from fii_fin_cat_type_assgns where FIN_CAT_TYPE_CODE = 'CGS' and TOP_NODE_FLAG = 'Y')
455 and cat.child_fin_cat_id=pCategory);
456
457 bisviewer_pub.showreport(pURLString => 'pFunctionName='||l_FunctionName||'&FII_DIM2='||pCategory||'&FII_DIM7='||
458 pCostCenter||'&FII_DIM9='||pMonth||'&FII_MEASURE9='||pLOB||'&FII_DIM8='||pCurrency||'&FII_DIM1='||pManager||'&pParamIds=Y',
459 pUserId => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
460 pSessionId => icx_sec.getID(icx_sec.PV_SESSION_ID),
461 pRespId => icx_sec.getId(icx_sec.PV_RESPONSIBILITY_ID));
462
463 elsif pSource = 'Receivables' then
464
465 bisviewer_pub.showreport(pURLString => 'pFunctionName=FII_GL_INV_REV_DET_R&FII_DIM2='||pCategory||'&FII_DIM7='||
466 pCostCenter||'&FII_DIM9='||pMonth||'&FII_MEASURE9='||pLOB||'&FII_DIM8='||pCurrency||'&FII_DIM1='||pManager||'&pParamIds=Y',
467 pUserId => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
468 pSessionId => icx_sec.getID(icx_sec.PV_SESSION_ID),
469 pRespId => icx_sec.getId(icx_sec.PV_RESPONSIBILITY_ID));
470
471 elsif (pSource = 'FII_GL_EXP_CAT3' OR pSource = 'FII_GL_REV_CAT3' OR pSource = 'FII_GL_COG_CAT3')then
472
473 SELECT DISTINCT next_level_is_leaf
474 INTO l_fin_leaf_node
475 FROM fii_fin_item_hierarchies
476 WHERE next_level_fin_cat_id = pCategory
477 and parent_fin_cat_id = next_level_fin_cat_id
478 and child_fin_cat_id = next_level_fin_cat_id;
479
480 IF l_fin_leaf_node = 'Y' THEN
481 select FunctionName into l_FunctionName from
482 (select 'FII_GL_INV_EXP_R' FunctionName
483 from fii_fin_item_hierarchies cat
484 where cat.parent_fin_cat_id in (select fin_category_id from fii_fin_cat_type_assgns where FIN_CAT_TYPE_CODE = 'OE' and TOP_NODE_FLAG = 'Y')
485 and cat.child_fin_cat_id=pCategory
486 union all
487 select 'FII_GL_INV_REV_R' FunctionName
488 from fii_fin_item_hierarchies cat
489 where cat.parent_fin_cat_id in (select fin_category_id from fii_fin_cat_type_assgns where FIN_CAT_TYPE_CODE = 'R' and TOP_NODE_FLAG = 'Y')
490 and cat.child_fin_cat_id=pCategory
491 union all
492 select 'FII_GL_INV_COR_R' FunctionName
493 from fii_fin_item_hierarchies cat
494 where cat.parent_fin_cat_id in (select fin_category_id from fii_fin_cat_type_assgns where FIN_CAT_TYPE_CODE = 'CGS' and TOP_NODE_FLAG = 'Y')
495 and cat.child_fin_cat_id=pCategory);
496
497 bisviewer_pub.showreport(pURLString => 'pFunctionName='||l_FunctionName||'&FII_DIM2='||pCategory||'&FII_DIM8='||
498 pCostCenter||'&FII_DIM9='||pMonth||'&FII_DIM7='||pCurrency||'&FII_DIM1='||pManager||'&FII_MEASURE9='||pLOB||'&AS_OF_DATE='||pAsOfDateValue||
499 '&pParamIds=Y',
500 pUserId => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
501 pSessionId => icx_sec.getID(icx_sec.PV_SESSION_ID),
502 pRespId => icx_sec.getId(icx_sec.PV_RESPONSIBILITY_ID));
503
504 else
505 bisviewer_pub.showreport(pURLString => 'pFunctionName=FII_GL_EXP_CAT3&FII_DIM9='||pLOB||'&VIEW_BY=VIEW_BY&FII_DIM7='||
506 pCategory||'&FII_DIM10='||pCostCenter||'&FII_DIM3='||pMonth||'&FII_DIM8='||pCurrency||'&FII_DIM1='||
507 pManager||'&AS_OF_DATE='||pAsOfDateValue||'&pParamIds=Y',
508 pUserId => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
509 pSessionId => icx_sec.getID(icx_sec.PV_SESSION_ID),
510 pRespId => icx_sec.getId(icx_sec.PV_RESPONSIBILITY_ID));
511
512 end if;
513
514 else
515
516 select FunctionName into l_FunctionName from
517 (select 'FII_GL_INV_EXP_R' FunctionName
518 from fii_fin_item_hierarchies cat
519 where cat.parent_fin_cat_id in (select fin_category_id from fii_fin_cat_type_assgns where FIN_CAT_TYPE_CODE = 'OE' and TOP_NODE_FLAG = 'Y')
520 and cat.child_fin_cat_id=pCategory
521 union all
522 select 'FII_GL_INV_REV_R' FunctionName
523 from fii_fin_item_hierarchies cat
524 where cat.parent_fin_cat_id in (select fin_category_id from fii_fin_cat_type_assgns where FIN_CAT_TYPE_CODE = 'R' and TOP_NODE_FLAG = 'Y')
525 and cat.child_fin_cat_id=pCategory
526 union all
527 select 'FII_GL_INV_COR_R' FunctionName
528 from fii_fin_item_hierarchies cat
529 where cat.parent_fin_cat_id in (select fin_category_id from fii_fin_cat_type_assgns where FIN_CAT_TYPE_CODE = 'CGS' and TOP_NODE_FLAG = 'Y')
530 and cat.child_fin_cat_id=pCategory);
531
532 bisviewer_pub.showreport(pURLString => 'pFunctionName='||l_FunctionName||'&FII_DIM2='||pCategory||'&FII_DIM8='||
533 pCostCenter||'&FII_DIM9='||pMonth||'&FII_DIM7='||pCurrency||'&FII_DIM1='||pManager||'&FII_MEASURE9='||pLOB||'&AS_OF_DATE='||pAsOfDateValue||
534 '&pParamIds=Y',
535 pUserId => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
536 pSessionId => icx_sec.getID(icx_sec.PV_SESSION_ID),
537 pRespId => icx_sec.getId(icx_sec.PV_RESPONSIBILITY_ID));
538
539 end if;
540
541 END;
542
543 END fii_gl_src_inv_pkg;