[Home] [Help]
PACKAGE BODY: APPS.FII_GL_UTIL_PKG
Source
1 PACKAGE BODY fii_gl_util_pkg AS
2 /* $Header: FIIGLC5B.pls 120.9 2006/05/18 19:49:35 vkazhipu noship $ */
3
4 g_min_start_date date;
5 g_min_week_start_date date;
6 /*
7 reset_globals
8 2 When setting gid, also picked the mv to use
9 These are one AND the same logic so it's good to go together
10 3 If substr or replace are needed on BIS parameters values, do them
11 ahead of time so global variables get what we want. Do not want
12 to substr on globals everywhere unless necessary
13
14 get_mgr_pmv_sql
15 1 Introduce logic to handle when ccc_org_id is specified
16 2 Merged in logic for handling view by cost center cases
17 3 Re-organized the logic
18
19 get_lob_pmv_sql
20 1 Introduce logic to handle when ccc_org_id is specified
21 2 Re-organized the logic
22 3 Introduce logic to handle when lob parameter value is
23 a leaf for a performance gain. Introduced function get_lob,
24 which has not been written but is similar to get_supervisor
25
26 get_ccc_pmv_sql
27 1 Re-organized logic given most code was merged INTO
28 get_mgr_pmv_sql AND get_viewby_sql
29 2 Introduced function ccc_within_mgr_lob which is not yet written
30 to check if a given ccc belongs to given mgr/lob
31 */
32
33
34 -- -------------------------------------------------
35 -- Re-set the globals variables to NULL
36 -- -------------------------------------------------
37 PROCEDURE reset_globals IS
38 BEGIN
39 g_period_type := NULL;
40 g_ent_period_type := NULL;
41 g_act_where_period_type := NULL;
42 g_where_period_type := NULL;
43 g_actual_period_type := NULL;
44 g_budget_period_type := NULL;
45 g_forecast_period_type := NULL;
46 g_view := NULL;
47 g_view_by := NULL;
48 g_as_of_date := NULL;
49 g_previous_asof_date := NULL;
50 g_curr_start := NULL;
51 g_curr_end := NULL;
52 g_temp := NULL;
53 g_prior_start := NULL;
54 g_prior_end := NULL;
55 g_mgr_id := NULL;
56 g_fin_id := NULL;
57 g_lob_id := NULL;
58 g_ccc_id := NULL;
59 g_time_comp := NULL;
60 g_currency := NULL;
61 g_gid := NULL;
62 g_lob_from_clause := NULL;
63 g_mgr_from_clause := NULL;
64 g_lob_join := NULL;
65 g_cat_join := NULL;
66 g_mgr_join := NULL;
67 g_ccc_join := NULL;
68 g_viewby_from_clause := NULL;
69 g_viewby_join := NULL;
70 g_viewby_value := NULL;
71 g_viewby_id := NULL;
72 g_fin_type := NULL;
73 g_month_id := NULL;
74 g_page_period_type := NULL;
75 g_py_sper_end := NULL;
76 g_curr_per_sequence := NULL;
77 g_p_period_end := NULL;
78 g_p_p_period_end := NULL;
79 g_cy_period_end := NULL;
80 g_ent_pyr_start := NULL;
81 g_ent_pyr_end := NULL;
82 g_ent_cyr_start := NULL;
83 g_ent_cyr_end := NULL;
84 g_viewby_type := NULL;
85 g_total_hc := NULL;
86 g_py_sday := NULL;
87 g_begin_date := NULL;
88 g_rpt_begin_date := NULL;
89 g_global_curr_view := NULL;
90 g_non_ag_cat_from_clause := NULL;
91 g_non_ag_cat_join := NULL;
92 g_rev_msg := NULL;
93 g_exp_msg := NULL;
94 g_cog_msg := NULL;
95 g_dir_msg := NULL;
96 g_prod_id := NULL;
97 g_cat_join2 := NULL;
98 g_lob_is_top_node := 'N';
99 g_cc_owner := NULL;
100 g_ccc_mgr_join := NULL;
101 g_ppy_sday := NULL;
102 g_new_date := NULL;
103 g_new_date2 := NULL;
104 g_detail_start := NULL;
105 g_detail_end := NULL;
106 g_top_spend_start := NULL;
107 g_top_spend_end := NULL;
108 g_exp_asof_date := NULL;
109 g_exp_begin_date := NULL;
110 g_exp_start := NULL;
111 g_sd_lyr := NULL;
112 g_five_yr_back := NULL;
113 --added for bug fix 5002238
114 --added by vkazhipu
115 --changing l_id AND l_dim_flag to bind variables
116 g_l_id := NULL;
117 g_dim_flag := NULL;
118 g_bitmask := NULL;
119 --added for bug fix 4969910
120 --added by hpoddar
121 g_start_id := NULL;
122 g_end_id := NULL;
123 g_slice_type_flag := NULL;
124 g_prev_mgr_id := NULL;
125 g_emp_id := NULL;
126 g_curr_start_period_id := NULL;
127 g_curr_end_period_id := NULL;
128
129 END reset_globals;
130
131 -- -------------------------------------------------
132 -- Parse thru the parameter talbe AND set globals
133 -- -------------------------------------------------
134 PROCEDURE get_parameters (
135 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL) IS
136
137 l_lob_enabled_flag varchar2(1);
138 l_date_range_check NUMBER;
139 BEGIN
140
141 -- -------------------------------------------------
142 -- Parse thru the parameter table AND set globals
143 -- -------------------------------------------------
144 IF (p_page_parameter_tbl.count > 0) THEN
145 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
146 IF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
147 g_page_period_type := p_page_parameter_tbl(i).parameter_value;
148 ELSIF p_page_parameter_tbl(i).parameter_name = 'VIEW_BY' THEN
149 g_view_by := p_page_parameter_tbl(i).parameter_value;
150 ELSIF p_page_parameter_tbl(i).parameter_name = 'CURRENCY+FII_CURRENCIES' THEN
151 g_currency := substr(p_page_parameter_tbl(i).parameter_id,2,11);
152 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME_COMPARISON_TYPE' THEN
153 g_time_comp := p_page_parameter_tbl(i).parameter_value;
154 ELSIF p_page_parameter_tbl(i).parameter_name = 'AS_OF_DATE' THEN
155 g_as_of_date :=
156 to_date(p_page_parameter_tbl(i).parameter_value,'DD-MM-YYYY');
157 ELSIF p_page_parameter_tbl(i).parameter_name = 'BIS_PREVIOUS_ASOF_DATE' THEN
158 g_previous_asof_date :=
159 to_date(p_page_parameter_tbl(i).parameter_value,'DD-MM-YYYY');
160 ELSIF p_page_parameter_tbl(i).parameter_name = 'LOB+FII_LOB' THEN
161 g_lob_id := replace(get_first_string(p_page_parameter_tbl(i).parameter_id),'''', null);
162 ELSIF p_page_parameter_tbl(i).parameter_name = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
163 g_mgr_id := replace(p_page_parameter_tbl(i).parameter_id, '''', null);
164 ELSIF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+HRI_CL_ORGCC' THEN
165 g_ccc_id := NVL(
166 replace(get_first_string(p_page_parameter_tbl(i).parameter_id), '''', null), -999);
167 ELSIF p_page_parameter_tbl(i).parameter_name = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
168 g_fin_id := NVL(
169 replace(get_first_string(p_page_parameter_tbl(i).parameter_id), '''', null),-999);
170 ELSIF p_page_parameter_tbl(i).parameter_name = 'fii_month+fii_month_level' THEN
171 g_month_id := replace(p_page_parameter_tbl(i).parameter_id, '''', null);
172 ELSIF p_page_parameter_tbl(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT' THEN
173 g_prod_id := replace(p_page_parameter_tbl(i).parameter_id, '''', null);
174 END IF;
175 END LOOP;
176 END IF;
177
178
179
180 -- Added following line for bug 3893359
181 --moved to top by vkazhipu for bug 5098174 - 18-May 2006
182
183 SELECT MIN(start_date) INTO g_min_start_date
184 FROM fii_time_ent_period;
185 SELECT MIN(start_date) INTO g_min_week_start_date
186 FROM fii_time_week;
187
188 --added by VKAZHIPU for bug 5098174
189 -- This will prevent SQL errors when date is selected outside the range
190
191 BEGIN
192
193 SELECT 1 into l_date_range_check FROM FII_TIME_DAY
194 where g_as_of_date between start_date and end_date;
195
196 EXCEPTION
197
198 WHEN OTHERS THEN
199
200 g_as_of_date := g_min_start_date;
201
202 END;
203
204 ---when specific cost center is chosen exp/rev/cogs trend reports are performing bad. so we
205 IF ((g_ccc_id IS NOT NULL) AND (g_ccc_id <> -999)) THEN
206 SELECT parent_manager_id
207 INTO g_cc_owner
208 FROM fii_com_cc_mappings
209 WHERE company_cost_center_org_id = g_ccc_id;
210 END IF;
211
212 /* When LOB chosen is All, we assign the top node LOB id to g_lob_id */
213
214 -- Bug 4135136. Pick the top node id FROM pruned hierarchy table when the
215 -- dimension is disabled, otherwise pick FROM fii_financial_dimensions table.
216
217 SELECT dbi_enabled_flag INTO l_lob_enabled_flag
218 FROM fii_financial_dimensions
219 WHERE dimension_short_name = 'FII_LOB';
220
221 If l_lob_enabled_flag = 'N' THEN
222 IF g_lob_id IS NULL OR g_lob_id = 'ALL' THEN
223 SELECT parent_lob_id INTO g_lob_id
224 FROM fii_lob_hierarchies;
225
226 g_lob_is_top_node := 'N';
227
228 END IF;
229 ELSE
230 IF g_lob_id IS NULL OR g_lob_id = 'ALL' THEN
231
232 SELECT dbi_hier_top_node_id INTO g_lob_id
233 FROM fii_financial_dimensions
234 WHERE dimension_short_name = 'FII_LOB';
235
236 g_lob_is_top_node := 'Y';
237 END IF;
238 END IF;
239
240 IF g_page_period_type IS NULL THEN
241 g_page_period_type := 'FII_TIME_ENT_QTR';
242 END IF;
243
244 IF g_mgr_id IS NULL THEN
245 g_mgr_id := -99999;
246 END IF;
247
248 IF g_time_comp IS NULL THEN
249 g_time_comp := 'YEARLY';
250 END IF;
251
252 IF g_as_of_date IS NULL THEN
253 g_as_of_date := trunc(sysdate);
254 END IF;
255
256 IF g_previous_asof_date IS NULL THEN
257 g_previous_asof_date := trunc(sysdate);
258 END IF;
259
260 -- -------------------------------------------------
261 -- Set time related global variables
262 -- -------------------------------------------------
263 /* Commented out for bug 3893359 AND replaced with SELECT
264 IF (g_as_of_date IS NOT NULL) THEN
265 g_ent_pyr_start := fii_time_api.ent_pyr_start(g_as_of_date);
266 g_ent_pyr_end:= fii_time_api.ent_pyr_end(g_as_of_date);
267 g_ent_cyr_start := fii_time_api.ent_cyr_start(g_as_of_date);
268 g_ent_cyr_end := fii_time_api.ent_cyr_end(g_as_of_date);
269 g_p_period_end := fii_time_api.ent_sd_lysper_end(g_as_of_date);
270 g_p_p_period_end := fii_time_api.ent_sd_lysper_end(g_p_period_end);
271
272 END IF;
273 */
274
275
276 IF (g_as_of_date IS NOT NULL) THEN
277 --g_ent_cyr_end := fii_time_api.ent_cyr_end(g_as_of_date);
278 SELECT NVL(fii_time_api.ent_cyr_end(g_as_of_date),g_min_start_date) INTO g_ent_cyr_end from dual;
279 SELECT NVL(fii_time_api.ent_pyr_start(g_as_of_date),g_min_start_date),
280 NVL(fii_time_api.ent_pyr_end(g_as_of_date),g_min_start_date),
281 NVL(fii_time_api.ent_cyr_start(g_as_of_date),g_min_start_date),
282 NVL( fii_time_api.ent_sd_lysper_end(g_as_of_date),g_min_start_date)
283 INTO g_ent_pyr_start,
284 g_ent_pyr_end,
285 g_ent_cyr_start,
286 g_p_period_end
287 FROM dual;
288
289 SELECT NVL(fii_time_api.ent_sd_lysper_end(g_p_period_end),g_min_start_date)
290 INTO g_p_p_period_end
291 FROM dual;
292 End IF;
293
294 -- End of 3893359
295
296 -- -------------------------------------------------
297 -- If user views in primary global, use 1st view which
298 -- selects the primary amount. For secondary global
299 -- currency, use 2nd view which selects secondary amount
300 -- Default assumed to be viewing data in primary global
301 -- -------------------------------------------------
302 IF g_currency = 'FII_GLOBAL1' THEN
303 g_global_curr_view := '1';
304 ELSIF g_currency = 'FII_GLOBAL2' THEN
305 g_global_curr_view := '2';
306 ELSE
307 g_global_curr_view := '1';
308 END IF;
309
310 -- -------------------------------------------------
311 -- Choose the MV to use AND set corronsponding gid
312 -- Three cases:
313 -- 1 When query involves cost center ie cost center
314 -- parameter populated or viewby is cost center,
315 -- use the fii_gl_mgmt_ccc_mv, which has no gid
316 -- 2 When query involves lob but not cost center,
317 -- use the 2nd group set of fii_gl_mgmt_sum_mv
318 -- 3 When query doesn't involve lob or cost center,
319 -- use the 1st group set of fii_gl_mgmt_sum_mv
320 -- -------------------------------------------------
321 IF (g_view_by = 'ORGANIZATION+HRI_CL_ORGCC') OR ((g_ccc_id IS NOT NULL) AND (g_ccc_id <> -999)) THEN
322 g_gid := NULL;
323 g_view := ' , FII_GL_MGMT_CCC_V'||g_global_curr_view||' f';
324 ELSIF (g_view_by = 'LOB+FII_LOB') OR ((g_lob_id IS NOT NULL) AND (g_lob_is_top_node <> 'Y')) THEN
325 g_gid := ' AND f.gid = 0';
326 g_view := ' , FII_GL_MGMT_SUM_V'||g_global_curr_view||' f';
327 ELSE
328 g_gid := ' AND f.gid = 4';
329 g_view := ' , FII_GL_MGMT_SUM_V'||g_global_curr_view||' f';
330 END IF;
331
332 g_cog_msg := FND_MESSAGE.get_string('FII', 'FII_GL_X');
333 g_exp_msg := FND_MESSAGE.get_string('FII', 'FII_GL_OE');
334 g_rev_msg := FND_MESSAGE.get_string('FII', 'FII_GL_R');
335 g_dir_msg := FND_MESSAGE.get_string('FII', 'FII_GL_DIR');
336
337 --added for bug fix 5002238
338 --by vkazhipu
339 --changing l_id AND l_dim_flag to bind variables
340
341 IF g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
342 g_l_id := fii_gl_util_pkg.g_mgr_id;
343 g_dim_flag := NVL(fii_gl_util_pkg.g_mgr_is_leaf,'N');
344
345
346 ELSIF g_view_by = 'LOB+FII_LOB' THEN
347 g_l_id := fii_gl_util_pkg.g_lob_id;
348 g_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
349
350 ELSIF g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
351 g_l_id := fii_gl_util_pkg.g_fin_id;
352 g_dim_flag := fii_gl_util_pkg.g_fincat_is_leaf;
353 ELSE
354 g_l_id := -9999;
355 g_dim_flag := 'Y';
356 END IF;
357
358 --added for bug fix 4969910
359 --added by hpoddar
360 --changing l_start, l_end and l_slice_type_flag to bind variables
361
362 CASE g_page_period_type
363 WHEN 'FII_TIME_WEEK' THEN g_slice_type_flag := 'M';
364 SELECT MIN(ent_period_id), MAX(ent_period_id) INTO g_start_id, g_end_id
365 FROM fii_time_ent_period
366 WHERE ENT_PERIOD_ID =
367 (SELECT ENT_PERIOD_ID FROM fii_time_ent_period
368 WHERE g_as_of_date BETWEEN start_date AND end_date);
369
370 WHEN 'FII_TIME_ENT_PERIOD' THEN g_slice_type_flag := 'M';
371 SELECT MIN(ent_period_id), MAX(ent_period_id) INTO g_start_id, g_end_id
372 FROM fii_time_ent_period
373 WHERE ENT_PERIOD_ID =
374 (SELECT ENT_PERIOD_ID FROM fii_time_ent_period
375 WHERE g_as_of_date BETWEEN start_date AND end_date);
376
377 WHEN 'FII_TIME_ENT_QTR' THEN g_slice_type_flag := 'Q';
378 SELECT MIN(ent_period_id), MAX(ent_period_id) INTO g_start_id, g_end_id
379 FROM fii_time_ent_period
380 WHERE ENT_QTR_ID =
381 (SELECT ENT_QTR_ID FROM fii_time_ent_period
382 WHERE g_as_of_date BETWEEN start_date AND end_date);
383
384 WHEN 'FII_TIME_ENT_YEAR' THEN g_slice_type_flag := 'Y';
385 SELECT MIN(ent_period_id), MAX(ent_period_id) INTO g_start_id, g_end_id
386 FROM fii_time_ent_period
387 WHERE ENT_YEAR_ID =
388 (SELECT ENT_YEAR_ID FROM fii_time_ent_period
389 WHERE g_as_of_date BETWEEN start_date AND end_date);
390 END CASE;
391
392 END get_parameters;
393
394
395 -- RYLIU2, there are performance enhancements which are not being taken cared
396 -- of here. I am not sure the differences BETWEEN these period types AND how
397 -- they are used.
398 -- g_period_type, g_where_period_type, g_act_where_period_type,
399 -- g_actual_period_type, g_ent_period_type
400 -- Can we discuss it in some details next week.
401
402
403 PROCEDURE get_bitmasks IS
404 BUDGET_TIME_UNIT VARCHAR2(1);
405 FORECAST_TIME_UNIT VARCHAR2(1);
406 l_time_parameter VARCHAR2(100) :=NULL;
407
408 BEGIN
409
410 -- -----------------------------------------------------------
411 -- Get budget/forecast levels FROM profile options
412 -- Default assume budget & forecast are loaded at period level
413 -- -----------------------------------------------------------
414 BUDGET_TIME_UNIT := NVL(FND_PROFILE.Value( 'FII_BUDGET_TIME_UNIT'),'P');
415 FORECAST_TIME_UNIT := NVL(FND_PROFILE.Value( 'FII_FORECAST_TIME_UNIT'),'P');
416
417 CASE g_page_period_type
418 WHEN 'FII_TIME_WEEK' THEN g_period_type := 16;
419 WHEN 'FII_TIME_ENT_PERIOD' THEN g_period_type := 32;
420 WHEN 'FII_TIME_ENT_QTR' THEN g_period_type := 64;
421 WHEN 'FII_TIME_ENT_YEAR' THEN g_period_type := 128;
422 END CASE;
423
424 -- Get the correct masks for the period types
425 CASE g_page_period_type
426 WHEN 'FII_TIME_WEEK' THEN g_where_period_type := 11;
427 WHEN 'FII_TIME_ENT_PERIOD' THEN g_where_period_type := 279; --1+2+4+16+256
428 WHEN 'FII_TIME_ENT_QTR' THEN g_where_period_type := 823; --1+2+4+16+32+256+512
429 WHEN 'FII_TIME_ENT_YEAR' THEN g_where_period_type := 1015; --1+2+4+16+32+64+128+256+512
430 END CASE;
431
432 CASE g_page_period_type
433 WHEN 'FII_TIME_WEEK' THEN g_act_where_period_type := 11;
434 WHEN 'FII_TIME_ENT_PERIOD' THEN g_act_where_period_type := 23; --1+2+4+16
435 WHEN 'FII_TIME_ENT_QTR' THEN g_act_where_period_type := 55; --1+2+4+16+32
436 WHEN 'FII_TIME_ENT_YEAR' THEN g_act_where_period_type := 247; --1+2+4+16+32+64+128
437 END CASE;
438
439 CASE g_page_period_type
440 WHEN 'FII_TIME_WEEK' THEN g_actual_period_type := 11;
441 WHEN 'FII_TIME_ENT_PERIOD' THEN g_actual_period_type := 23;
442 WHEN 'FII_TIME_ENT_QTR' THEN g_actual_period_type := 55;
443 WHEN 'FII_TIME_ENT_YEAR' THEN g_actual_period_type := 119;
444 END CASE;
445
446 CASE g_page_period_type
447 WHEN 'FII_TIME_WEEK' THEN g_ent_period_type := 2048;
448 WHEN 'FII_TIME_ENT_PERIOD' THEN g_ent_period_type := 256;
449 WHEN 'FII_TIME_ENT_QTR' THEN g_ent_period_type := 512;
450 WHEN 'FII_TIME_ENT_YEAR' THEN g_ent_period_type := 128;
451 END CASE;
452
453 /* code modified by ilavenil on 02/20/03. 2808245 - budget should run for the entire period */
454 CASE BUDGET_TIME_UNIT
455 WHEN 'D' then
456 g_budget_period_type := g_actual_period_type;
457 WHEN 'P' THEN
458 CASE g_page_period_type
459 WHEN 'FII_TIME_WEEK' THEN g_budget_period_type := 0;
460 WHEN 'FII_TIME_ENT_PERIOD' THEN g_budget_period_type := 256;
461 WHEN 'FII_TIME_ENT_QTR' THEN g_budget_period_type := 512;
462 WHEN 'FII_TIME_ENT_YEAR' THEN g_budget_period_type := 128;
463 END CASE;
464 WHEN 'Q' THEN
465 CASE g_page_period_type
466 WHEN 'FII_TIME_WEEK' THEN g_budget_period_type := 0;
467 WHEN 'FII_TIME_ENT_PERIOD' THEN g_budget_period_type := 0;
468 WHEN 'FII_TIME_ENT_QTR' THEN g_budget_period_type := 512;
469 WHEN 'FII_TIME_ENT_YEAR' THEN g_budget_period_type := 128;
470 END CASE;
471 WHEN 'Y' THEN
472 CASE g_page_period_type
473 WHEN 'FII_TIME_WEEK' THEN g_budget_period_type := 0;
474 WHEN 'FII_TIME_ENT_PERIOD' THEN g_budget_period_type := 0;
475 WHEN 'FII_TIME_ENT_QTR' THEN g_budget_period_type := 0;
476 WHEN 'FII_TIME_ENT_YEAR' THEN g_budget_period_type := 128;
477 END CASE;
478 END CASE;
479
480 CASE FORECAST_TIME_UNIT
481 WHEN 'D' THEN
482 g_forecast_period_type := g_actual_period_type;
483 WHEN 'P' THEN
484 CASE g_page_period_type
485 WHEN 'FII_TIME_WEEK' THEN g_forecast_period_type := 0;
486 WHEN 'FII_TIME_ENT_PERIOD' THEN g_forecast_period_type := 256;
487 WHEN 'FII_TIME_ENT_QTR' THEN g_forecast_period_type := 512;
488 WHEN 'FII_TIME_ENT_YEAR' THEN g_forecast_period_type := 128;
489 END CASE;
490 WHEN 'Q' THEN
491 CASE g_page_period_type
492 WHEN 'FII_TIME_WEEK' THEN g_forecast_period_type := 0;
493 WHEN 'FII_TIME_ENT_PERIOD' THEN g_forecast_period_type := 0;
494 WHEN 'FII_TIME_ENT_QTR' THEN g_forecast_period_type := 512;
495 WHEN 'FII_TIME_ENT_YEAR' THEN g_forecast_period_type := 128;
496 END CASE;
497 WHEN 'Y' THEN
498 CASE g_page_period_type
499 WHEN 'FII_TIME_WEEK' THEN g_forecast_period_type := 0;
500 WHEN 'FII_TIME_ENT_PERIOD' THEN g_forecast_period_type := 0;
501 WHEN 'FII_TIME_ENT_QTR' THEN g_forecast_period_type := 0;
502 WHEN 'FII_TIME_ENT_YEAR' THEN g_forecast_period_type := 128;
503 END CASE;
504 END CASE;
505
506 -- Most of the following global variables are specific to the
507 -- Exp. per head AND T & E Trend reports. So the some of the bind names
508 -- AND global variable names are not intuitive
509
510 SELECT NVL( fii_time_api.ent_sd_lysper_end(g_as_of_date),g_min_start_date) INTO g_sd_lyr FROM dual;
511
512 CASE g_page_period_type
513
514 WHEN 'FII_TIME_WEEK' THEN
515
516 --g_curr_end := fii_time_api.cwk_end(g_as_of_date);
517 SELECT NVL(fii_time_api.cwk_end(g_as_of_date),g_min_start_date) INTO g_curr_end FROM DUAL;
518
519 SELECT NVL(fii_time_api.cwk_end(g_previous_asof_date),g_min_start_date),
520 NVL( fii_time_api.pwk_end(g_as_of_date),g_min_start_date),
521 NVL( fii_time_api.sd_lyswk(g_as_of_date),g_min_start_date),
522 NVL(DECODE(fii_time_api.cwk_start(g_as_of_date),g_min_week_start_date,g_min_start_date,fii_time_api.cwk_start(g_as_of_date)),g_min_start_date)
523 INTO g_py_sper_end,
524 g_cy_period_end,
525 g_py_sday,
526 g_curr_start
527 FROM dual;
528
529 SELECT report_date_julian INTO g_curr_start_day_id FROM fii_time_day WHERE report_date = g_curr_start;
530 SELECT report_date_julian INTO g_curr_end_day_id FROM fii_time_day WHERE report_date = g_curr_end;
531
532 g_temp := NULL;
533
534 SELECT NVL(DECODE(fii_time_api.cwk_start(g_previous_asof_date),g_min_week_start_date,g_min_start_date,fii_time_api.cwk_start(g_previous_asof_date)),g_min_start_date),
535 NVL( fii_time_api.cwk_end(g_previous_asof_date),g_min_start_date),
536 NVL( fii_time_api.sd_lyswk(g_as_of_date),g_min_start_date)
537 INTO g_prior_start,
538 g_prior_end,
539 g_exp_asof_date
540 FROM dual;
541
542 SELECT NVL(fii_time_api.sd_lyswk(g_exp_asof_date),g_min_start_date)
543 INTO g_exp_start
544 FROM dual;
545
546 g_exp_begin_date := g_as_of_date - 91;
547
548 SELECT ent_period_start_date
549 INTO g_top_spend_start
550 FROM fii_time_day
551 WHERE report_date = g_as_of_date;
552
553 SELECT MAX(end_date) INTO g_top_spend_end
554 FROM fii_time_ent_period
555 WHERE ent_period_id = ( SELECT ent_period_id
556 FROM fii_time_ent_period
557 WHERE g_as_of_date BETWEEN start_date AND end_date);
558
559 CASE g_time_comp
560 WHEN 'BUDGET' THEN
561 SELECT NVL( fii_time_api.cwk_start(g_as_of_date),g_min_start_date)
562 INTO g_prior_start
563 FROM dual;
564
565 --g_prior_end := fii_time_api.cwk_end(g_as_of_date);
566 SELECT NVL(fii_time_api.cwk_end(g_as_of_date),g_min_start_date) INTO g_prior_end FROM DUAL;
567
568 SELECT report_date_julian INTO g_prior_start_day_id FROM fii_time_day WHERE report_date = g_curr_start;
569 SELECT report_date_julian INTO g_prior_end_day_id FROM fii_time_day WHERE report_date = g_curr_end;
570 ELSE
571 SELECT report_date_julian INTO g_prior_start_day_id FROM fii_time_day WHERE report_date = g_prior_start;
572 SELECT report_date_julian INTO g_prior_end_day_id FROM fii_time_day WHERE report_date = g_prior_end;
573 END CASE;
574
575 SELECT NVL(fii_time_api.sd_lyswk(g_py_sday),g_min_start_date)
576 INTO g_rpt_begin_date
577 FROM dual;
578
579 g_begin_date := g_as_of_date - 91;
580
581 SELECT DISTINCT a.sequence INTO g_curr_per_sequence
582 FROM fii_time_week a
583 WHERE g_as_of_date BETWEEN a.START_DATE AND a.END_DATE;
584
585 WHEN 'FII_TIME_ENT_PERIOD' THEN
586 IF (g_previous_asof_date IS NULL) THEN
587 SELECT NVL(fii_time_api.ent_sd_lysper_beg(g_as_of_date),g_min_start_date)
588 INTO g_previous_asof_date
589 FROM dual;
590 END IF;
591
592 SELECT NVL( fii_time_api.ent_cper_end(g_previous_asof_date),g_min_start_date),
593 NVL(fii_time_api.ent_pper_end(g_as_of_date),g_min_start_date),
594 NVL( fii_time_api.ent_sd_lysper_end(g_as_of_date),g_min_start_date),
595 NVL( fii_time_api.ent_sd_lysper_end(g_sd_lyr),g_min_start_date),
596 NVL(fii_time_api.ent_cper_start(g_as_of_date),g_min_start_date),
597 NVL(fii_time_api.ent_cper_start(g_previous_asof_date),g_min_start_date),
598 NVL(fii_time_api.ent_cper_end(g_previous_asof_date),g_min_start_date),
599 NVL(fii_time_api.ent_sd_lysper_end(g_as_of_date),g_min_start_date)
600 INTO g_py_sper_end,
601 g_cy_period_end,
602 g_py_sday,
603 g_ppy_sday,
604 g_curr_start,
605 g_prior_start,
606 g_prior_end,
607 g_exp_asof_date
608 FROM dual;
609
610 SELECT NVL(fii_time_api.ent_sd_lysper_end(g_exp_asof_date),g_min_start_date)
611 INTO g_exp_start
612 FROM dual;
613
614 --g_curr_end := fii_time_api.ent_cper_end(g_as_of_date);
615 SELECT NVL(fii_time_api.ent_cper_end(g_as_of_date),g_min_start_date) INTO g_curr_end FROM DUAL;
616
617 SELECT ent_period_id INTO g_curr_start_period_id FROM fii_time_ent_period WHERE start_date = g_curr_start;
618 SELECT ent_period_id INTO g_curr_end_period_id FROM fii_time_ent_period WHERE end_date = g_curr_end;
619 SELECT report_date_julian INTO g_curr_start_day_id FROM fii_time_day WHERE report_date = g_curr_start;
620 SELECT report_date_julian INTO g_curr_end_day_id FROM fii_time_day WHERE report_date = g_curr_end;
621
622 g_temp := NULL;
623
624 g_exp_begin_date := g_exp_asof_date;
625
626 SELECT ent_period_start_date
627 INTO g_top_spend_start
628 FROM fii_time_day
629 WHERE report_date = g_as_of_date;
630
631 SELECT MAX(end_date)
632 INTO g_top_spend_end
633 FROM fii_time_ent_period
634 WHERE ent_period_id = ( SELECT ent_period_id
635 FROM fii_time_ent_period
636 WHERE g_as_of_date BETWEEN start_date AND end_date);
637
638 CASE g_time_comp
639 WHEN 'BUDGET' THEN
640 SELECT NVL( fii_time_api.ent_cper_start(g_as_of_date),g_min_start_date)
641 INTO g_prior_start
642 FROM dual;
643
644 --g_prior_end := fii_time_api.ent_cper_end(g_as_of_date);
645 SELECT NVL(fii_time_api.ent_cper_end(g_as_of_date),g_min_start_date) INTO g_prior_end FROM DUAL;
646
647 SELECT report_date_julian INTO g_prior_start_day_id FROM fii_time_day WHERE report_date = g_curr_start;
648 SELECT report_date_julian INTO g_prior_end_day_id FROM fii_time_day WHERE report_date = g_curr_end;
649 ELSE
650 SELECT report_date_julian INTO g_prior_start_day_id FROM fii_time_day WHERE report_date = g_prior_start;
651 SELECT report_date_julian INTO g_prior_end_day_id FROM fii_time_day WHERE report_date = g_prior_end;
652 END CASE;
653
654 SELECT NVL( fii_time_api.ent_sd_lysper_end(g_py_sday),g_min_start_date)
655 INTO g_rpt_begin_date
656 FROM dual;
657
658 g_begin_date := g_py_sday;
659
660 SELECT DISTINCT a.sequence INTO g_curr_per_sequence
661 FROM fii_time_ent_period a
662 WHERE g_as_of_date BETWEEN a.START_DATE AND a.END_DATE;
663
664 WHEN 'FII_TIME_ENT_QTR' THEN
665
666 SELECT NVL(fii_time_api.ent_cqtr_end(g_previous_asof_date),g_min_start_date),
667 NVL( fii_time_api.ent_pqtr_end(g_as_of_date),g_min_start_date),
668 NVL( fii_time_api.ent_sd_lysqtr_end(g_as_of_date),g_min_start_date),
669 NVL(fii_time_api.ent_cqtr_start(g_as_of_date),g_min_start_date),
670 NVL(fii_time_api.ent_cqtr_start(g_previous_asof_date),g_min_start_date),
671 NVL(fii_time_api.ent_cqtr_end(g_previous_asof_date),g_min_start_date)
672 INTO g_py_sper_end,
673 g_cy_period_end,
674 g_py_sday,
675 g_curr_start,
676 g_prior_start,
677 g_prior_end
678 FROM dual;
679
680 --g_curr_end := fii_time_api.ent_cqtr_end(g_as_of_date);
681 SELECT NVL(fii_time_api.ent_cqtr_end(g_as_of_date),g_min_start_date) INTO g_curr_end FROM DUAL;
682
683 SELECT ent_period_id INTO g_curr_start_period_id FROM fii_time_ent_period WHERE start_date = g_curr_start;
684 SELECT ent_period_id INTO g_curr_end_period_id FROM fii_time_ent_period WHERE end_date = g_curr_end;
685 SELECT report_date_julian INTO g_curr_start_day_id FROM fii_time_day WHERE report_date = g_curr_start;
686 SELECT report_date_julian INTO g_curr_end_day_id FROM fii_time_day WHERE report_date = g_curr_end;
687
688 g_temp := NULL;
689
690 SELECT ent_qtr_start_date
691 INTO g_top_spend_start
692 FROM fii_time_day
693 WHERE report_date = g_as_of_date;
694
695 SELECT MAX(end_date)
696 INTO g_top_spend_end
697 FROM fii_time_ent_period
698 WHERE ent_qtr_id =( SELECT ent_qtr_id
699 FROM fii_time_ent_period
700 WHERE g_as_of_date BETWEEN start_date AND end_date);
701
702 SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_py_sday),g_min_start_date)
703 INTO g_rpt_begin_date
704 FROM dual;
705
706 IF (g_time_comp = 'SEQUENTIAL') THEN
707
708 SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_rpt_begin_date),g_min_start_date)
709 INTO g_begin_date
710 FROM dual;
711
712 SELECT DISTINCT a.ent_qtr_id INTO g_curr_per_sequence
713 FROM fii_time_ent_qtr a
714 WHERE g_as_of_date BETWEEN a.START_DATE AND a.END_DATE;
715
716 SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_as_of_date),g_min_start_date)
717 INTO g_exp_asof_date
718 FROM dual;
719
720 SELECT NVL(fii_time_api.ent_sd_lysqtr_end(fii_time_api.ent_sd_lysqtr_end(g_exp_asof_date)),g_min_start_date)
721 INTO g_exp_begin_date
722 FROM dual;
723 ELSE
724 g_begin_date := g_py_sday;
725
726 SELECT DISTINCT a.sequence INTO g_curr_per_sequence
727 FROM fii_time_ent_qtr a
728 WHERE g_as_of_date BETWEEN a.START_DATE AND a.END_DATE;
729
730 SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_as_of_date),g_min_start_date)
731 INTO g_exp_asof_date
732 FROM dual;
733
734 g_exp_begin_date := g_exp_asof_date;
735
736 END IF;
737
738 SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_exp_asof_date),g_min_start_date)
739 INTO g_exp_start
740 FROM dual;
741
742 CASE g_time_comp
743 WHEN 'BUDGET' THEN
744 SELECT NVL( fii_time_api.ent_cqtr_start(g_as_of_date),g_min_start_date)
745 INTO g_prior_start
746 FROM dual;
747
748 --g_prior_end := fii_time_api.ent_cqtr_end(g_as_of_date);
749 SELECT NVL(fii_time_api.ent_cqtr_end(g_as_of_date),g_min_start_date) INTO g_prior_end from dual;
750
751 SELECT report_date_julian INTO g_prior_start_day_id FROM fii_time_day WHERE report_date = g_curr_start;
752 SELECT report_date_julian INTO g_prior_end_day_id FROM fii_time_day WHERE report_date = g_curr_end;
753 ELSE
754 SELECT report_date_julian INTO g_prior_start_day_id FROM fii_time_day WHERE report_date = g_prior_start;
755 SELECT report_date_julian INTO g_prior_end_day_id FROM fii_time_day WHERE report_date = g_prior_end;
756 END CASE;
757
758 WHEN 'FII_TIME_ENT_YEAR' THEN
759
760 g_cy_period_end := NULL;
761 g_py_sday := NULL;
762
763 SELECT NVL( fii_time_api.ent_pyr_end(g_as_of_date),g_min_start_date),
764 NVL( fii_time_api.ent_cyr_start(g_as_of_date),g_min_start_date),
765 NVL( fii_time_api.ent_cper_start(g_as_of_date),g_min_start_date),
766 NVL( fii_time_api.ent_cyr_start(g_previous_asof_date),g_min_start_date),
767 NVL( fii_time_api.ent_cyr_end(g_previous_asof_date),g_min_start_date)
768 INTO g_py_sper_end,
769 g_curr_start,
770 g_temp,
771 g_prior_start,
772 g_prior_end
773 FROM dual;
774
775 --g_curr_end := fii_time_api.ent_cyr_end(g_as_of_date);
776 SELECT NVL(fii_time_api.ent_cyr_end(g_as_of_date),g_min_start_date) INTO g_curr_end FROM DUAL;
777
778 SELECT ent_period_id INTO g_curr_start_period_id FROM fii_time_ent_period WHERE start_date = g_curr_start;
779 SELECT ent_period_id INTO g_curr_end_period_id FROM fii_time_ent_period WHERE end_date = g_curr_end;
780 SELECT report_date_julian INTO g_curr_start_day_id FROM fii_time_day WHERE report_date = g_curr_start;
781 SELECT report_date_julian INTO g_curr_end_day_id FROM fii_time_day WHERE report_date = g_curr_end;
782
783 g_exp_asof_date := NULL;
784 g_exp_start := NULL;
785
786 SELECT ent_year_start_date
787 INTO g_top_spend_start
788 FROM fii_time_day
789 WHERE report_date = g_as_of_date;
790
791 SELECT MAX(end_date)
792 INTO g_top_spend_end
793 FROM fii_time_ent_period
794 WHERE ent_year_id = ( SELECT ent_year_id
795 FROM fii_time_ent_period
796 WHERE g_as_of_date BETWEEN start_date AND end_date);
797
798 CASE g_time_comp
799 WHEN 'BUDGET' THEN
800 SELECT NVL( fii_time_api.ent_cyr_start(g_as_of_date),g_min_start_date)
801 INTO g_prior_start
802 FROM dual;
803
804 --g_prior_end := fii_time_api.ent_cyr_end(g_as_of_date);
805 SELECT NVL(fii_time_api.ent_cyr_end(g_as_of_date),g_min_start_date) INTO g_prior_end from dual;
806
807 SELECT report_date_julian INTO g_prior_start_day_id FROM fii_time_day WHERE report_date = g_curr_start;
808 SELECT report_date_julian INTO g_prior_end_day_id FROM fii_time_day WHERE report_date = g_curr_end;
809
810 ELSE
811 SELECT report_date_julian INTO g_prior_start_day_id FROM fii_time_day WHERE report_date = g_prior_start;
812 SELECT report_date_julian INTO g_prior_end_day_id FROM fii_time_day WHERE report_date = g_prior_end;
813 END CASE;
814
815 g_rpt_begin_date := NULL;
816 g_begin_date := NULL;
817 g_curr_per_sequence := NULL;
818 END CASE;
819
820 IF g_month_id IS NULL THEN NULL;
821 ELSE
822 SELECT start_date, end_date
823 INTO g_detail_start, g_detail_end
824 FROM fii_time_ent_period
825 WHERE ent_period_id = g_month_id;
826 END IF;
827
828 SELECT NVL(to_char(MIN(ent_period_id)),g_month_id)
829 INTO l_time_parameter
830 FROM fii_time_ent_period
831 WHERE g_as_of_date BETWEEN start_date AND end_date;
832
833 IF (g_month_id <> l_time_parameter) THEN
834
835 g_bitmask := 256;
836
837 SELECT end_date
838 INTO g_new_date
839 FROM fii_time_ent_period
840 WHERE ent_period_id = g_month_id;
841
842 SELECT NVL(fii_time_api.ent_sd_lysper_end(g_new_date),g_min_start_date)
843 INTO g_new_date2
844 FROM dual;
845 ELSE
846 g_bitmask := 23;
847
848 g_new_date := g_as_of_date;
849
850 SELECT NVL(fii_time_api.ent_sd_lyr_end(g_as_of_date),g_min_start_date)
851 INTO g_new_date2
852 FROM dual;
853
854 END IF;
855
856 END get_bitmasks;
857
858
859 -- -------------------------------------------------
860 -- Set the view by global variables depending
861 -- the view by for the given sql
862 -- -------------------------------------------------
863 PROCEDURE get_viewby_sql IS
864 BEGIN
865
866 CASE g_view_by
867 WHEN 'HRI_PERSON+HRI_PER_USRDR_H' THEN
868 g_viewby_value := ' ppl.value ';
869 g_viewby_id := ' f.person_id ';
870 g_viewby_from_clause := ' hri_dbi_cl_per_n_v ppl ';
871 g_viewby_join := ' ppl.id = f.viewby_id
872 AND sysdate BETWEEN ppl.effective_start_date AND ppl.effective_end_date';
873 WHEN 'LOB+FII_LOB' THEN
874 get_lob;
875 g_viewby_value := ' NVL(tl.description,tl.flex_value_meaning) ';
876 IF g_lob_is_leaf = 'Y' THEN
877 g_viewby_id := ' f.line_of_business_id ';
878 ELSE
879 g_viewby_id := ' lob.next_level_lob_id ';
880 END IF;
881 g_viewby_from_clause := ' fnd_flex_values_tl tl ';
882 g_viewby_join := ' f.viewby_id = tl.flex_value_id AND tl.language = userenv(''LANG'')';
883 WHEN 'ORGANIZATION+HRI_CL_ORGCC' THEN
884 g_viewby_value := ' cc.name ';
885 g_viewby_id := ' f.cost_center_org_id ';
886 g_viewby_from_clause := ' hr_all_organization_units_tl cc ';
887 g_viewby_join := ' cc.organization_id = f.viewby_id AND cc.language = userenv(''LANG'')';
888 WHEN 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
889 g_viewby_value := ' NVL(tl.description,tl.flex_value_meaning) ';
890 g_viewby_id := ' f.fin_category_id ';
891 g_viewby_from_clause := ' fnd_flex_values_tl tl ';
892 g_viewby_join := ' f.viewby_id = tl.flex_value_id AND tl.language = userenv(''LANG'')';
893 END CASE;
894
895 END get_viewby_sql;
896
897 -- -------------------------------------------------
898 -- Set the manager related FROM/WHERE clauses
899 -- -------------------------------------------------
900 PROCEDURE get_mgr_pmv_sql IS
901 l_mgr_mgr_id NUMBER;
902 BEGIN
903
904 IF ((g_ccc_id IS NOT NULL) AND (g_ccc_id <> -999)) THEN
905 -- ---------------------------------------------------------
906 -- Whenever cost center parameter is specified, we no longer
907 -- need any filter on mgr regardless of the view by
908 -- ---------------------------------------------------------
909 g_mgr_join := NULL;
910 g_mgr_from_clause := NULL;
911
912 ELSE
913 if g_mgr_id <> -99999 then
914 get_supervisor(l_mgr_mgr_id);
915 END if;
916 CASE g_view_by
917 WHEN 'ORGANIZATION+HRI_CL_ORGCC' THEN
918 -- ---------------------------------------------------------
919 -- For viewby cost center, we join to mgr hierarchy table
920 -- because fii_gl_mgmg_ccc_mv does not aggregate up mgr
921 -- Exception when manager is leaf, we can directly query
922 -- against the mv without going thru mgr hierarchy table
923 -- ---------------------------------------------------------
924 IF g_mgr_is_leaf = 'Y' THEN
925 IF g_mgr_id = -99999 THEN g_mgr_join := ' AND f.manager_id = -99999 ';
926 ELSE g_mgr_join := ' AND f.manager_id = &HRI_PERSON+HRI_PER_USRDR_H ';
927 END IF;
928 ELSE
929 IF g_mgr_id = -99999 THEN g_mgr_join := ' AND h.mgr_id = -99999
930 AND h.emp_id = f.manager_id ';
931 g_mgr_from_clause := ', fii_cc_mgr_hierarchies h';
932 ELSE
933 g_mgr_join := ' AND h.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
934 AND h.emp_id = f.manager_id ';
935 g_mgr_from_clause := ', fii_cc_mgr_hierarchies h';
936 END IF;
937 END IF;
938
939 WHEN 'HRI_PERSON+HRI_PER_USRDR_H' THEN
940 -- ---------------------------------------------------------
941 -- For viewby manager, we go to fii_gl_mgmt_sum_mv which
942 -- does aggregate up mgr. we filter on the column manager_id
943 -- AND view by person id. Exception when manager is leaf,
944 -- we filter on the column person_id AND view by person_id
945 -- because of the self vs Org records reduction at
946 -- the leaf level
947 -- ---------------------------------------------------------
948 IF g_mgr_is_leaf = 'Y' THEN
949 g_mgr_mgr_id := l_mgr_mgr_id;
950 IF g_mgr_id = -99999 THEN g_mgr_join := ' AND f.person_id = -99999
951 AND f.manager_id = :MGR_MGR_ID ';
952 ELSE g_mgr_join := ' AND f.person_id = &HRI_PERSON+HRI_PER_USRDR_H
953 AND f.manager_id = :MGR_MGR_ID ';
954 END IF;
955 ELSE
956 IF g_mgr_id = -99999 THEN g_mgr_join := ' AND f.manager_id = -99999 ';
957 ELSE g_mgr_join := ' AND f.manager_id = &HRI_PERSON+HRI_PER_USRDR_H ';
958 END IF;
959 END IF;
960
961 ELSE
962 -- ---------------------------------------------------------
963 -- All other viewby's have standard filter on manager_id AND
964 -- person_id
965 -- ---------------------------------------------------------
966 g_mgr_mgr_id := l_mgr_mgr_id;
967 IF g_mgr_id = -99999 THEN g_mgr_join := ' AND f.person_id = -99999 AND f.manager_id = :MGR_MGR_ID ';
968 ELSE g_mgr_join := ' AND f.person_id = &HRI_PERSON+HRI_PER_USRDR_H
969 AND f.manager_id = :MGR_MGR_ID ';
970 END IF;
971 END CASE;
972
973 END IF;
974
975 END get_mgr_pmv_sql;
976
977 FUNCTION get_first_string (l_id IN VARCHAR2) RETURN VARCHAR2 IS
978
979 l_pos INTEGER := 0;
980 l_string VARCHAR2(100) := NULL;
981 BEGIN
982 l_string := l_id;
983 l_pos := instr(l_string, ',');
984 IF l_pos <> 0 THEN
985 l_string := substr(l_string, 0, l_pos - 1);
986 END IF;
987
988 return l_string;
989
990 END get_first_string;
991
992 -- -------------------------------------------------
993 -- Set the LOB related FROM/WHERE clauses
994 -- -------------------------------------------------
995 PROCEDURE get_lob_pmv_sql IS
996 BEGIN
997
998 IF ((g_ccc_id IS NOT NULL) AND (g_ccc_id <> -999)) THEN
999 -- ---------------------------------------------------------
1000 -- Whenever cost center parameter is specified, we no longer
1001 -- need any filter on lob regardless of the view by
1002 -- ---------------------------------------------------------
1003 g_lob_join := NULL;
1004 g_lob_from_clause := NULL;
1005
1006 ELSE
1007 -- ---------------------------------------------------------
1008 -- When query involves LOB (LOB specified or is view by LOB)
1009 -- We need to add filter for LOB. Two cases:
1010 -- 1 LOB is leaf, we directly query the mv
1011 -- 2 LOB specified is not leaf, we go thru lob hier table
1012 -- ---------------------------------------------------------
1013 IF (g_view_by = 'LOB+FII_LOB') OR (g_lob_is_top_node <> 'Y') THEN
1014 get_lob;
1015 IF (g_lob_is_leaf = 'Y') THEN
1016 g_lob_join := ' AND f.line_of_business_id = :LOB_ID ';
1017 ELSE
1018 g_lob_join := ' AND lob.parent_lob_id = :LOB_ID
1019 AND lob.child_lob_id = f.line_of_business_id';
1020 g_lob_from_clause := ' ,fii_lob_hierarchies lob';
1021 END IF;
1022 END IF;
1023 END IF;
1024
1025 END get_lob_pmv_sql;
1026
1027
1028 -- -------------------------------------------------
1029 -- Set the cost center related FROM/WHERE clauses
1030 -- -------------------------------------------------
1031 PROCEDURE get_ccc_pmv_sql IS
1032 BEGIN
1033
1034 IF ((g_ccc_id IS NOT NULL) AND (g_ccc_id <> -999)) THEN
1035 g_ccc_mgr_join := ' AND f.manager_id = :CCC_OWNER ';
1036 -- ----------------------------------------------------
1037 -- If company cost center is specified, conditions on mgr
1038 -- or lob are no longer needed because ccc is at lower
1039 -- granularity. However, if the given ccc does not belong to
1040 -- the given manager or lob parameter, the query will
1041 -- return no data. In such a case, we simply give 1=2
1042 -- condition to void the whole query
1043 -- ----------------------------------------------------
1044 IF (ccc_within_mgr_lob(g_ccc_id, g_lob_id, g_mgr_id) = 'Y') THEN
1045 g_ccc_join := ' AND f.cost_center_org_id = &ORGANIZATION+HRI_CL_ORGCC ';
1046 ELSE
1047 g_ccc_join := ' AND 1 = 2 ';
1048 END IF;
1049 END IF;
1050
1051 END get_ccc_pmv_sql;
1052
1053
1054
1055 PROCEDURE get_cat_pmv_sql IS
1056 l_fin_id NUMBER;
1057 l_parent_fin_id NUMBER;
1058 -- Bug 4249917. Increased the size of the following variables to 32000.
1059 l_category_id VARCHAR2(32000) := null;
1060 l_category_id2 VARCHAR2(32000) := null;
1061 l_category_id3 VARCHAR2(32000) := null;
1062 l_first BOOLEAN := TRUE;
1063 type1 VARCHAR2(10);
1064 type2 VARCHAR2(10);
1065 type3 VARCHAR2(10);
1066 CURSOR get_top_nodes(type1 VARCHAR2, type2 VARCHAR2, type3 VARCHAR2) IS
1067 SELECT a.fin_category_id fin_category_id, b.PARENT_FIN_CAT_ID parent_fin_cat_id
1068 FROM fii_fin_cat_type_assgns a, fii_fin_item_hierarchies b
1069 WHERE a.FIN_CAT_TYPE_CODE IN (type1, type2, type3)
1070 AND a.TOP_NODE_FLAG = 'Y'
1071 AND a.fin_category_id = b.CHILD_FIN_CAT_ID
1072 AND a.fin_category_id <> b.PARENT_FIN_CAT_ID;
1073
1074
1075 -- ------------------------------------------------------
1076 -- CASE statement stes category types based on report
1077 -- Example: Expense related reports only need 'OE' etc.
1078 -- ------------------------------------------------------
1079 BEGIN
1080 CASE g_fin_type
1081 WHEN 'R' THEN
1082 type1 := 'R';
1083 type2 := 'R';
1084 type3 := 'R';
1085 WHEN 'OE' THEN
1086 type1 := 'OE';
1087 type2 := 'OE';
1088 type3 := 'OE';
1089 WHEN 'CGS' THEN
1090 type1 := 'CGS';
1091 type2 := 'CGS';
1092 type3 := 'CGS';
1093 WHEN 'TE' THEN
1094 type1 := 'TE';
1095 type2 := 'TE';
1096 type3 := 'TE';
1097 WHEN 'OM' THEN
1098 type1 := 'R';
1099 type2 := 'OE';
1100 type3 := 'CGS';
1101 WHEN 'GM' THEN
1102 type1 := 'R';
1103 type2 := 'CGS';
1104 type3 := 'R';
1105 END CASE;
1106
1107 IF (g_fin_id = -999 or g_fin_id IS NULL) THEN
1108 FOR a IN get_top_nodes (type1, type2, type3)
1109 LOOP
1110 l_fin_id := a.fin_category_id;
1111 get_fin_item(l_fin_id, l_parent_fin_id);
1112 IF (get_top_nodes%ROWCOUNT = 1) THEN
1113 IF (g_fincat_is_leaf = 'N') THEN
1114 l_category_id := a.fin_category_id;
1115 ELSE
1116 l_category_id3 := '('||a.fin_category_id||','||a.parent_fin_cat_id||')';
1117 END IF;
1118 IF (l_category_id IS NULL) THEN
1119 l_category_id := '-999';
1120 END IF;
1121 IF (l_category_id3 IS NULL) THEN
1122 l_category_id3 := '(-999 , -999)';
1123 END IF;
1124 l_category_id2 := '('||a.fin_category_id||','||a.parent_fin_cat_id||')';
1125 l_first := FALSE;
1126 ELSE
1127 IF (g_fincat_is_leaf = 'N') THEN
1128 l_category_id := l_category_id ||','||a.fin_category_id;
1129 ELSE
1130 l_category_id3 := l_category_id3 ||','||'('||a.fin_category_id||','||a.parent_fin_cat_id||')';
1131 END IF;
1132 l_category_id2 := l_category_id2 ||','||'('||a.fin_category_id||','||a.parent_fin_cat_id||')';
1133 END IF;
1134 END LOOP;
1135 END IF;
1136
1137 -- ------------------------------------------------------
1138 -- If no top nodes are defined for the specified fin type/s,
1139 -- report should not error out. In this case,
1140 -- we use a category id that does not exist i.e. -999
1141 -- ------------------------------------------------------
1142
1143 IF (l_category_id IS NULL) THEN
1144 l_category_id := '-999';
1145 END IF;
1146
1147 IF (l_category_id2 IS NULL) THEN
1148 l_category_id2 := '(-999 , -999)';
1149 END IF;
1150
1151 IF (l_category_id3 IS NULL) THEN
1152 l_category_id3 := '(-999 , -999)';
1153 END IF;
1154
1155 l_fin_id := g_fin_id;
1156 get_fin_item(l_fin_id, l_parent_fin_id);
1157
1158 IF g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
1159 IF (g_fin_id = -999 or g_fin_id IS NULL) THEN
1160 g_cat_join := ' AND (f.parent_fin_category_id in ('||l_category_id||')
1161 or (f.fin_category_id, f.parent_fin_category_id) in ('||l_category_id3||'))';
1162 /* Modified g_cat_join2 as part of bug fix for 3769162 */
1163 g_cat_join2 := ' AND (cat_hier.parent_fin_cat_id in ('||l_category_id||')
1164 or (cat_hier.child_fin_cat_id, cat_hier.parent_fin_cat_id) in ('||l_category_id3||'))';
1165 ELSE
1166 IF g_fincat_is_leaf = 'Y' THEN
1167 g_parent_fin_id := l_parent_fin_id;
1168 g_cat_join := ' AND f.parent_fin_category_id = :PARENT_FIN_ID
1169 AND f.fin_category_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM';
1170 ELSE
1171 g_cat_join := ' AND f.parent_fin_category_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM';
1172
1173 END IF;
1174 g_cat_join2 := ' AND cat_hier.parent_fin_cat_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM';
1175 END IF;
1176
1177 ELSE
1178
1179 IF (g_fin_id = -999 or g_fin_id IS NULL) THEN
1180 --If Category is not a paramater or view by, g_fin_id will be NULL
1181 g_cat_join := ' AND (f.fin_category_id, f.parent_fin_category_id) in ('||l_category_id2||')';
1182
1183 ELSE
1184 g_parent_fin_id := l_parent_fin_id;
1185 g_cat_join := ' AND f.parent_fin_category_id = :PARENT_FIN_ID
1186 AND f.fin_category_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM';
1187 END IF;
1188
1189 END IF;
1190
1191 END get_cat_pmv_sql;
1192
1193
1194 PROCEDURE get_non_ag_cat_pmv_sql IS
1195 -- Bug 4249917. Increased the size of l_category_id to 32000.
1196 l_fin_id NUMBER;
1197 l_parent_fin_id NUMBER;
1198 l_category_id VARCHAR2(32000) := null;
1199 l_first BOOLEAN := TRUE;
1200 type1 VARCHAR2(10);
1201 CURSOR get_top_nodes(type1 VARCHAR2) IS
1202 SELECT a.fin_category_id fin_category_id, b.PARENT_FIN_CAT_ID parent_fin_cat_id
1203 FROM fii_fin_cat_type_assgns a, fii_fin_item_hierarchies b
1204 WHERE a.FIN_CAT_TYPE_CODE = type1
1205 AND a.TOP_NODE_FLAG = 'Y'
1206 AND a.fin_category_id = b.CHILD_FIN_CAT_ID;
1207
1208
1209 -- ------------------------------------------------------
1210 -- CASE statement stes category types based on report
1211 -- Example: Expense related reports only need 'OE' etc.
1212 -- ------------------------------------------------------
1213 BEGIN
1214
1215 IF (g_fin_id = -999 or g_fin_id IS NULL) THEN
1216 FOR a IN get_top_nodes (g_fin_type)
1217 LOOP
1218 IF (get_top_nodes%ROWCOUNT = 1) THEN
1219 l_category_id := a.fin_category_id;
1220 l_first := FALSE;
1221 ELSE
1222 l_category_id := l_category_id ||','||a.fin_category_id;
1223 END IF;
1224 END LOOP;
1225 END IF;
1226
1227 -- ------------------------------------------------------
1228 -- If no top nodes are defined for the specified fin type/s,
1229 -- report should not error out. In this case,
1230 -- we use a category id that does not exist i.e. -999
1231 -- ------------------------------------------------------
1232
1233 IF (l_category_id IS NULL) THEN
1234 l_category_id := '-999';
1235 END IF;
1236
1237 l_fin_id := g_fin_id;
1238 get_fin_item(l_fin_id, l_parent_fin_id);
1239
1240 g_non_ag_cat_from_clause := ', fii_fin_item_hierarchies fin';
1241
1242 IF (g_fin_id = -999 OR g_fin_id IS NULL) THEN
1243 g_non_ag_cat_join := ' AND fin.parent_fin_cat_id in
1244 ('||l_category_id||')
1245 AND fin.child_fin_cat_id = f.fin_category_id';
1246 ELSE
1247 g_non_ag_cat_join := ' AND fin.parent_fin_cat_id = &FINANCIAL ITEM+GL_FII_FIN_ITEM
1248 AND fin.child_fin_cat_id = f.fin_category_id';
1249 END IF;
1250
1251 END get_non_ag_cat_pmv_sql;
1252
1253 PROCEDURE Bind_Variable (p_sqlstmt IN Varchar2,
1254 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
1255 p_sql_output OUT NOCOPY Varchar2,
1256 p_bind_output_table OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
1257
1258 l_bind_rec BIS_QUERY_ATTRIBUTES;
1259
1260 BEGIN
1261
1262 p_bind_output_table := BIS_QUERY_ATTRIBUTES_TBL();
1263 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1264 p_sql_output := p_sqlstmt;
1265
1266 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
1267
1268 IF (g_viewby_type IS NOT NULL) THEN
1269
1270 p_bind_output_table.EXTEND;
1271 l_bind_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
1272 l_bind_rec.attribute_value := g_viewby_type;
1273 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
1274 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1275 END IF;
1276
1277 -- RYLIU2, let's clean out any binds not really used
1278 p_bind_output_table.EXTEND;
1279 l_bind_rec.attribute_name := ':PERIOD_TYPE';
1280 l_bind_rec.attribute_value := to_char(g_period_type);
1281 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1282 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1283 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1284 p_bind_output_table.EXTEND;
1285 l_bind_rec.attribute_name := ':WHERE_PERIOD_TYPE';
1286 l_bind_rec.attribute_value := to_char(g_where_period_type);
1287 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1288 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1289 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1290 p_bind_output_table.EXTEND;
1291 l_bind_rec.attribute_name := ':ACT_WHERE_PERIOD_TYPE';
1292 l_bind_rec.attribute_value := to_char(g_act_where_period_type);
1293 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1294 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1295 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1296 p_bind_output_table.EXTEND;
1297 l_bind_rec.attribute_name := ':ENT_PERIOD_TYPE';
1298 l_bind_rec.attribute_value := to_char(g_ent_period_type);
1299 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1300 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1301 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1302 p_bind_output_table.EXTEND;
1303 l_bind_rec.attribute_name := ':ACTUAL_PERIOD_TYPE';
1304 l_bind_rec.attribute_value := to_char(g_actual_period_type);
1305 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1306 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1307 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1308 p_bind_output_table.EXTEND;
1309 l_bind_rec.attribute_name := ':BUDGET_PERIOD_TYPE';
1310 l_bind_rec.attribute_value := to_char(g_budget_period_type);
1311 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1312 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1313 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1314 p_bind_output_table.EXTEND;
1315 l_bind_rec.attribute_name := ':FORECAST_PERIOD_TYPE';
1316 l_bind_rec.attribute_value := to_char(g_forecast_period_type);
1317 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1318 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1319 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1320 p_bind_output_table.EXTEND;
1321 l_bind_rec.attribute_name := ':GLOBAL_CURR_VIEW';
1322 l_bind_rec.attribute_value := to_char(g_global_curr_view);
1323 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1324 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1325 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1326 p_bind_output_table.EXTEND;
1327 l_bind_rec.attribute_name := ':VIEW_BY';
1328 l_bind_rec.attribute_value := to_char(g_view_by);
1329 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1330 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1331 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1332 p_bind_output_table.EXTEND;
1333 l_bind_rec.attribute_name := ':LOB_ID';
1334 l_bind_rec.attribute_value := to_char(g_lob_id);
1335 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1336 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1337 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1338 IF (g_ccc_id = -999) THEN
1339 p_bind_output_table.EXTEND;
1340 l_bind_rec.attribute_name := ':CCC_ID';
1341 l_bind_rec.attribute_value := 'All';
1342 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1343 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1344 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1345 ELSE
1346 p_bind_output_table.EXTEND;
1347 l_bind_rec.attribute_name := ':CCC_ID';
1348 l_bind_rec.attribute_value := to_char(g_ccc_id);
1349 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1350 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1351 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1352 END IF;
1353 p_bind_output_table.EXTEND;
1354 l_bind_rec.attribute_name := ':PREVIOUS_ASOF_DATE';
1355 l_bind_rec.attribute_value := to_char(g_previous_asof_date, 'DD/MM/YYYY');
1356 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1357 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1358 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1359 p_bind_output_table.EXTEND;
1360 l_bind_rec.attribute_name := ':ASOF_DATE';
1361 l_bind_rec.attribute_value := to_char(g_as_of_date, 'DD/MM/YYYY');
1362 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1363 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1364 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1365 p_bind_output_table.EXTEND;
1366 l_bind_rec.attribute_name := ':MGR_ID';
1367 l_bind_rec.attribute_value := to_char(g_mgr_id);
1368 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1369 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1370 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1371 p_bind_output_table.EXTEND;
1372 l_bind_rec.attribute_name := ':MGR_MGR_ID';
1373 l_bind_rec.attribute_value := to_char(g_mgr_mgr_id);
1374 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1375 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1376 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1377 p_bind_output_table.EXTEND;
1378 l_bind_rec.attribute_name := ':FIN_ID';
1379 l_bind_rec.attribute_value := to_char(g_fin_id);
1380 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1381 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1382 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1383 p_bind_output_table.EXTEND;
1384 l_bind_rec.attribute_name := ':PARENT_FIN_ID';
1385 l_bind_rec.attribute_value := to_char(g_parent_fin_id);
1386 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1387 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1388 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1389 p_bind_output_table.EXTEND;
1390 l_bind_rec.attribute_name := ':PY_SPER_END';
1391 l_bind_rec.attribute_value := to_char(g_py_sper_end, 'DD/MM/YYYY');
1392 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1393 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1394 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1395 p_bind_output_table.EXTEND;
1396 l_bind_rec.attribute_name := ':CURR_EFFECTIVE_SEQ';
1397 l_bind_rec.attribute_value := to_char(g_curr_per_sequence);
1398 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1399 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1400 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1401 p_bind_output_table.EXTEND;
1402 l_bind_rec.attribute_name := ':P_PERIOD_END';
1403 l_bind_rec.attribute_value := to_char(g_p_period_end, 'DD/MM/YYYY');
1404 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1405 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1406 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1407 p_bind_output_table.EXTEND;
1408 l_bind_rec.attribute_name := ':P_P_PERIOD_END';
1409 l_bind_rec.attribute_value := to_char(g_p_p_period_end, 'DD/MM/YYYY');
1410 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1411 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1412 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1413 p_bind_output_table.EXTEND;
1414 l_bind_rec.attribute_name := ':CURRENCY';
1415 l_bind_rec.attribute_value := to_char(g_currency);
1416 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1417 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1418 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1419 p_bind_output_table.EXTEND;
1420 l_bind_rec.attribute_name := ':GID';
1421 l_bind_rec.attribute_value := to_char(g_gid);
1422 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1423 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1424 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1425 p_bind_output_table.EXTEND;
1426 l_bind_rec.attribute_name := ':MONTH_ID';
1427 l_bind_rec.attribute_value := to_char(g_month_id);
1428 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1429 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1430 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1431 p_bind_output_table.EXTEND;
1432 l_bind_rec.attribute_name := ':CY_PERIOD_END';
1433 l_bind_rec.attribute_value := to_char(g_cy_period_end, 'DD/MM/YYYY');
1434 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1435 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1436 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1437 p_bind_output_table.EXTEND;
1438 l_bind_rec.attribute_name := ':ENT_PYR_START';
1439 l_bind_rec.attribute_value := to_char(g_ent_pyr_start, 'DD/MM/YYYY');
1440 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1441 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1442 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1443 p_bind_output_table.EXTEND;
1444 l_bind_rec.attribute_name := ':ENT_PYR_END';
1445 l_bind_rec.attribute_value := to_char(g_ent_pyr_end, 'DD/MM/YYYY');
1446 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1447 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1448 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1449 p_bind_output_table.EXTEND;
1450 l_bind_rec.attribute_name := ':ENT_CYR_START';
1451 l_bind_rec.attribute_value := to_char(g_ent_cyr_start, 'DD/MM/YYYY');
1452 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1453 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1454 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1455 p_bind_output_table.EXTEND;
1456 l_bind_rec.attribute_name := ':PY_SAME_DAY';
1457 l_bind_rec.attribute_value := to_char(g_py_sday, 'DD/MM/YYYY');
1458 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1459 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1460 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1461 p_bind_output_table.EXTEND;
1462 l_bind_rec.attribute_name := ':FIVE_YR_BACK';
1463 l_bind_rec.attribute_value := to_char(g_five_yr_back, 'DD/MM/YYYY');
1464 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1465 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1466 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1467 p_bind_output_table.EXTEND;
1468 l_bind_rec.attribute_name := ':RPT_BEGIN_DATE';
1469 l_bind_rec.attribute_value := to_char(g_rpt_begin_date, 'DD/MM/YYYY');
1470 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1471 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1472 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1473 p_bind_output_table.EXTEND;
1474 l_bind_rec.attribute_name := ':BEGIN_DATE';
1475 l_bind_rec.attribute_value := to_char(g_begin_date, 'DD/MM/YYYY');
1476 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1477 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1478 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1479 p_bind_output_table.EXTEND;
1480 l_bind_rec.attribute_name := ':ENT_CYR_END';
1481 l_bind_rec.attribute_value := to_char(g_ent_cyr_end, 'DD/MM/YYYY');
1482 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1483 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1484 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1485 p_bind_output_table.EXTEND;
1486 l_bind_rec.attribute_name := ':P_CURR_START';
1487 l_bind_rec.attribute_value := to_char(g_curr_start, 'DD-MM-YYYY');
1488 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1489 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1490 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1491 p_bind_output_table.EXTEND;
1492 l_bind_rec.attribute_name := ':P_CURR_END';
1493 l_bind_rec.attribute_value := to_char(g_curr_end, 'DD-MM-YYYY');
1494 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1495 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1496 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1497 p_bind_output_table.EXTEND;
1498 l_bind_rec.attribute_name := ':P_PRIOR_START';
1499 l_bind_rec.attribute_value := to_char(g_prior_start, 'DD-MM-YYYY');
1500 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1501 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1502 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1503 p_bind_output_table.EXTEND;
1504 l_bind_rec.attribute_name := ':P_PRIOR_END';
1505 l_bind_rec.attribute_value := to_char(g_prior_end, 'DD-MM-YYYY');
1506 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1507 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1508 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1509 p_bind_output_table.EXTEND;
1510 l_bind_rec.attribute_name := ':P_TEMP';
1511 l_bind_rec.attribute_value := to_char(g_temp, 'DD-MM-YYYY');
1512 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1513 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1514 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1515 p_bind_output_table.EXTEND;
1516 l_bind_rec.attribute_name := ':REV_MSG';
1517 l_bind_rec.attribute_value := to_char(g_rev_msg);
1518 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1519 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1520 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1521 p_bind_output_table.EXTEND;
1522 l_bind_rec.attribute_name := ':EXP_MSG';
1523 l_bind_rec.attribute_value := to_char(g_exp_msg);
1524 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1525 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1526 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1527 p_bind_output_table.EXTEND;
1528 l_bind_rec.attribute_name := ':COG_MSG';
1529 l_bind_rec.attribute_value := to_char(g_cog_msg);
1530 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1531 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1532 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1533 p_bind_output_table.EXTEND;
1534 l_bind_rec.attribute_name := ':DIR_MSG';
1535 l_bind_rec.attribute_value := to_char(g_dir_msg);
1536 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1537 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1538 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1539 p_bind_output_table.EXTEND;
1540 l_bind_rec.attribute_name := ':CCC_OWNER';
1541 l_bind_rec.attribute_value := to_char(g_cc_owner);
1542 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1543 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1544 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1545 p_bind_output_table.EXTEND;
1546
1547 l_bind_rec.attribute_name := ':PPY_SAME_DAY';
1548 l_bind_rec.attribute_value := to_char(g_ppy_sday, 'DD/MM/YYYY');
1549 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1550 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1551 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1552 p_bind_output_table.EXTEND;
1553
1554 l_bind_rec.attribute_name := ':P_AS_OF';
1555 l_bind_rec.attribute_value := to_char(g_new_date, 'DD/MM/YYYY');
1556 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1557 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1558 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1559 p_bind_output_table.EXTEND;
1560
1561 l_bind_rec.attribute_name := ':P_PREV_AS_OF';
1562 l_bind_rec.attribute_value := to_char(g_new_date2, 'DD/MM/YYYY');
1563 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1564 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1565 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1566 p_bind_output_table.EXTEND;
1567
1568 l_bind_rec.attribute_name := ':P_DET_START';
1569 l_bind_rec.attribute_value := to_char(g_detail_start, 'DD/MM/YYYY');
1570 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1571 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1572 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1573 p_bind_output_table.EXTEND;
1574
1575 l_bind_rec.attribute_name := ':P_DET_END';
1576 l_bind_rec.attribute_value := to_char(g_detail_end, 'DD/MM/YYYY');
1577 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1578 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1579 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1580 p_bind_output_table.EXTEND;
1581
1582 l_bind_rec.attribute_name := ':P_TOP_SPEND_START';
1583 l_bind_rec.attribute_value := to_char(g_top_spend_start, 'DD/MM/YYYY');
1584 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1585 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1586 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1587 p_bind_output_table.EXTEND;
1588
1589 l_bind_rec.attribute_name := ':P_TOP_SPEND_END';
1590 l_bind_rec.attribute_value := to_char(g_top_spend_end, 'DD/MM/YYYY');
1591 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1592 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1593 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1594 p_bind_output_table.EXTEND;
1595
1596 l_bind_rec.attribute_name := ':P_EXP_ASOF';
1597 l_bind_rec.attribute_value := to_char(g_exp_asof_date, 'DD/MM/YYYY');
1598 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1599 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1600 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1601 p_bind_output_table.EXTEND;
1602
1603 l_bind_rec.attribute_name := ':P_EXP_START';
1604 l_bind_rec.attribute_value := to_char(g_exp_start, 'DD/MM/YYYY');
1605 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1606 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1607 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1608 p_bind_output_table.EXTEND;
1609
1610 l_bind_rec.attribute_name := ':P_EXP_BEGIN';
1611 l_bind_rec.attribute_value := to_char(g_exp_begin_date, 'DD/MM/YYYY');
1612 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1613 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1614 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1615 p_bind_output_table.EXTEND;
1616
1617 l_bind_rec.attribute_name := ':P_SD_LYR';
1618 l_bind_rec.attribute_value := to_char(g_sd_lyr, 'DD/MM/YYYY');
1619 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1620 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1621 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1622 p_bind_output_table.EXTEND;
1623
1624 --added by vkazhipu for bug fix 5002238
1625
1626 l_bind_rec.attribute_name := ':L_ID';
1627 l_bind_rec.attribute_value := to_char(g_l_id);
1628 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1629 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1630 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1631 p_bind_output_table.EXTEND;
1632
1633 l_bind_rec.attribute_name := ':DIM_FLAG';
1634 l_bind_rec.attribute_value := to_char(g_dim_flag);
1635 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1636 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1637 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1638
1639 p_bind_output_table.EXTEND;
1640 l_bind_rec.attribute_name := ':FIN_TYPE';
1641 l_bind_rec.attribute_value := to_char(g_fin_type);
1642 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1643 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1644 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1645
1646 p_bind_output_table.EXTEND;
1647 l_bind_rec.attribute_name := ':BITMASK';
1648 l_bind_rec.attribute_value := to_char(g_bitmask);
1649 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1650 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1651 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1652
1653 --added by hpoddar for bug fix 4969910
1654
1655 p_bind_output_table.EXTEND;
1656 l_bind_rec.attribute_name := ':START_ID';
1657 l_bind_rec.attribute_value := to_char(g_start_id);
1658 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1659 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1660 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1661
1662 p_bind_output_table.EXTEND;
1663 l_bind_rec.attribute_name := ':END_ID';
1664 l_bind_rec.attribute_value := to_char(g_end_id);
1665 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1666 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1667 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1668
1669 p_bind_output_table.EXTEND;
1670 l_bind_rec.attribute_name := ':SLICE_TYPE_FLAG';
1671 l_bind_rec.attribute_value := to_char(g_slice_type_flag);
1672 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1673 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1674 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1675
1676 p_bind_output_table.EXTEND;
1677 l_bind_rec.attribute_name := ':PREV_MGR_ID';
1678 l_bind_rec.attribute_value := to_char(g_prev_mgr_id);
1679 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1680 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1681 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1682
1683 p_bind_output_table.EXTEND;
1684 l_bind_rec.attribute_name := ':EMP_ID';
1685 l_bind_rec.attribute_value := to_char(g_emp_id);
1686 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1687 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1688 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1689
1690 --added by hpoddar for bug fix 5002661
1691
1692 p_bind_output_table.EXTEND;
1693 l_bind_rec.attribute_name := ':TOTAL_HC';
1694 l_bind_rec.attribute_value := to_char(g_total_hc);
1695 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1696 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1697 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1698
1699 p_bind_output_table.EXTEND;
1700 l_bind_rec.attribute_name := ':CURR_START_PERIOD_ID';
1701 l_bind_rec.attribute_value := to_char(g_curr_start_period_id);
1702 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1703 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1704 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1705
1706 p_bind_output_table.EXTEND;
1707 l_bind_rec.attribute_name := ':CURR_END_PERIOD_ID';
1708 l_bind_rec.attribute_value := to_char(g_curr_end_period_id);
1709 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1710 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1711 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1712
1713 --added by hpoddar for bug fix 5002564
1714
1715 p_bind_output_table.EXTEND;
1716 l_bind_rec.attribute_name := ':CURR_START_DAY_ID';
1717 l_bind_rec.attribute_value := to_char(g_curr_start_day_id);
1718 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1719 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1720 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1721
1722 p_bind_output_table.EXTEND;
1723 l_bind_rec.attribute_name := ':CURR_END_DAY_ID';
1724 l_bind_rec.attribute_value := to_char(g_curr_end_day_id);
1725 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1726 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1727 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1728
1729 p_bind_output_table.EXTEND;
1730 l_bind_rec.attribute_name := ':PRIOR_START_DAY_ID';
1731 l_bind_rec.attribute_value := to_char(g_prior_start_day_id);
1732 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1733 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1734 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1735
1736 p_bind_output_table.EXTEND;
1737 l_bind_rec.attribute_name := ':PRIOR_END_DAY_ID';
1738 l_bind_rec.attribute_value := to_char(g_prior_end_day_id);
1739 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1740 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1741 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
1742
1743 END bind_variable;
1744
1745 PROCEDURE get_supervisor (l_mgr_mgr_id OUT NOCOPY NUMBER) IS
1746
1747 l_mgr_mgr_level NUMBER;
1748 l_mgr_level NUMBER;
1749
1750 BEGIN
1751
1752 -- ----------------------------------------------------------
1753 -- We do not need to SELECT mgr_level. We only need to
1754 -- calculate it because the top node -999 is not defined
1755 -- in the mgr hierarchies table.
1756 -- Thus we get incorrect l_mgr_mgr_id for the
1757 -- manager who is already top node.
1758 -- ----------------------------------------------------------
1759
1760 SELECT mgr_level INTO l_mgr_level
1761 FROM fii_cc_mgr_hierarchies
1762 WHERE EMP_ID = g_mgr_id
1763 AND DIRECT_ID = g_mgr_id
1764 AND MGR_ID = g_mgr_id;
1765
1766 IF (l_mgr_level <> 1) THEN
1767 SELECT distinct MGR_ID INTO l_mgr_mgr_id
1768 FROM fii_cc_mgr_hierarchies
1769 WHERE DIRECT_ID = g_mgr_id
1770 AND EMP_ID = g_mgr_id
1771 AND (DIRECT_LEVEL = 1 OR MGR_ID <> DIRECT_ID);
1772 ELSE l_mgr_mgr_id := '-999';
1773 END IF;
1774
1775 SELECT IS_LEAF_FLAG INTO g_mgr_is_leaf
1776 FROM fii_cc_mgr_hierarchies
1777 WHERE EMP_ID = g_mgr_id
1778 AND MGR_ID = g_mgr_id;
1779
1780
1781
1782 -- RYLIU2, error handing when no rows retrieved
1783 -- mbedekar Can we discuss how we want to do this?
1784
1785 END get_supervisor;
1786
1787 PROCEDURE get_lob IS
1788
1789 BEGIN
1790
1791 IF (g_lob_is_top_node = 'Y') THEN
1792 g_lob_is_leaf := 'N';
1793 ELSE
1794 SELECT IS_LEAF_FLAG INTO g_lob_is_leaf
1795 FROM fii_lob_hierarchies
1796 WHERE CHILD_LOB_ID = g_lob_id
1797 AND PARENT_LOB_ID = g_lob_id;
1798 END IF;
1799
1800
1801 END get_lob;
1802
1803 FUNCTION ccc_within_mgr_lob( g_ccc_id IN NUMBER,
1804 g_lob_id IN VARCHAR2,
1805 g_mgr_id IN NUMBER) return VARCHAR2 IS
1806
1807 is_within_mgr NUMBER;
1808 is_within_lob NUMBER;
1809 BEGIN
1810 is_within_mgr := 1;
1811 -- After implementing a dependent LOV for the cost center
1812 -- the cost center selected is always one owned by the
1813 -- manager selected or someone reporting to him.
1814 -- Therefore we set the variable to 1.
1815
1816 IF g_lob_is_top_node = 'Y' THEN
1817 is_within_lob := 1;
1818 ELSE
1819 BEGIN
1820
1821 SELECT 1
1822 INTO is_within_lob
1823 FROM fii_com_cc_mappings mapp,
1824 fii_lob_hierarchies x
1825 WHERE mapp.COMPANY_COST_CENTER_ORG_ID = g_ccc_id
1826 AND x.parent_lob_id = g_lob_id
1827 AND x.child_lob_id = mapp.parent_lob_id
1828 AND rownum = 1;
1829
1830 EXCEPTION
1831 WHEN NO_DATA_FOUND THEN
1832 is_within_lob := 0;
1833 END;
1834
1835 END IF;
1836
1837 IF (is_within_mgr > 0 AND is_within_lob > 0) THEN
1838 RETURN 'Y';
1839 ELSE
1840 RETURN 'N';
1841 END IF;
1842
1843 END ccc_within_mgr_lob;
1844
1845 PROCEDURE get_fin_item ( l_fin_id IN NUMBER,
1846 l_p_fin_id OUT NOCOPY NUMBER) IS
1847
1848 BEGIN
1849
1850 IF (l_fin_id <> -999) THEN
1851 SELECT PARENT_FIN_CAT_ID INTO l_p_fin_id
1852 FROM fii_fin_item_hierarchies
1853 WHERE NEXT_LEVEL_FIN_CAT_ID = l_fin_id
1854 AND CHILD_FIN_CAT_ID = l_fin_id
1855 AND (NEXT_LEVEL = 1 or PARENT_FIN_CAT_ID <> NEXT_LEVEL_FIN_CAT_ID);
1856 ELSE l_p_fin_id := -999;
1857 END IF;
1858
1859 IF (l_fin_id <> -999) THEN
1860 SELECT NEXT_LEVEL_IS_LEAF INTO g_fincat_is_leaf
1861 FROM fii_fin_item_hierarchies
1862 WHERE CHILD_FIN_CAT_ID = l_fin_id
1863 AND NEXT_LEVEL_FIN_CAT_ID = l_fin_id
1864 AND PARENT_FIN_CAT_ID = l_fin_id;
1865 ELSE g_fincat_is_leaf := 'N';
1866 END IF;
1867
1868 END get_fin_item;
1869
1870 END fii_gl_util_pkg;
1871