[Home] [Help]
PACKAGE BODY: APPS.FII_EA_UTIL_PKG
Source
1 PACKAGE BODY fii_ea_util_pkg AS
2 /* $Header: FIIEAUTILB.pls 120.12 2006/07/27 09:27:39 wywong noship $ */
3
4 g_min_start_date date;
5
6 TYPE viewby_dimension is RECORD (par_id NUMBER, id NUMBER, sort_order NUMBER, description VARCHAR2(100));
7 TYPE PL1 is table of viewby_dimension index by binary_integer;
8 TYPE plsqltable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
9 TYPE viewby IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
10 company_table PL1; -- table of records for company dimension
11 cc_table PL1; -- table of records for CC
12 fin_cat_table PL1; -- table of records for category
13 fud1_table PL1; -- table of records for fud1
14 fud2_table PL1; -- table of records for fud2
15 par_comp_id_aggrt_plsql plsqltable;
16 -- par_comp_id_aggrt_plsql depicts the plsql table for storing parent company id while inserting in fii_pmv_aggrt_gt
17 --table. similar explanation for other plsql variables
18
19 /* various plsql table's declaration */
20
21 comp_id_aggrt_plsql plsqltable;
22 par_cc_id_aggrt_plsql plsqltable;
23 cc_id_aggrt_plsql plsqltable;
24 par_fin_cat_id_aggrt_plsql plsqltable;
25 par_fud1_id_aggrt_plsql plsqltable;
26 fin_cat_id_aggrt_plsql plsqltable;
27 fud1_id_aggrt_plsql plsqltable;
28 fud2_id_aggrt_plsql plsqltable;
29 sort_order_aggrt_plsql plsqltable;
30 sort_order_nonaggrt_plsql plsqltable;
31 comp_id_nonaggrt_plsql plsqltable;
32 cc_id_nonaggrt_plsql plsqltable;
33 fin_cat_id_nonaggrt_plsql plsqltable;
34 fud1_id_nonaggrt_plsql plsqltable;
35 fud2_id_nonaggrt_plsql plsqltable;
36 aggrt_viewbydescription viewby;
37 nonaggrt_viewbydescription viewby;
38
39 l_cat_join VARCHAR2(10000);
40 l_fud1_join VARCHAR2(10000);
41 l_fud2_join VARCHAR2(10000);
42 l_aggrt_cat_join VARCHAR2(10000); -- gives category join while hitting aggrt MVs
43 l_nonaggrt_cat_join VARCHAR2(10000); -- -- gives category join while hitting non-aggrt MVs
44
45 l_aggrt_fud1_join VARCHAR2(10000); -- gives fud1 join while hitting aggrt MVs
46 l_nonaggrt_fud1_join VARCHAR2(10000); -- gives fud1 join while hitting non-aggrt MVs
47 -- -------------------------------------------------
48 -- Re-set the globals variables to NULL
49 -- -------------------------------------------------
50 PROCEDURE reset_globals IS
51 BEGIN
52
53 g_as_of_date := NULL;
54 g_page_period_type := NULL;
55 g_currency := NULL;
56 g_view_by := NULL;
57 g_time_comp := NULL;
58 g_previous_asof_date := NULL;
59 g_company_id := 'All';
60 g_parent_company_id := NULL;
61 g_top_company_id := NULL;
62 g_cost_center_id := 'All';
63 g_parent_cost_center_id := NULL;
64 g_top_cost_center_id := NULL;
65 g_fin_category_id := 'All';
66 g_parent_fin_category_id := NULL;
67 g_fin_cat_type := 'OE';
68 g_ledger_id := 'All';
69 g_fud1_id := 'All';
70 g_parent_fud1_id := NULL;
71 g_top_fud1_id := NULL;
72 g_fud2_id := 'All';
73 g_parent_fud2_id := NULL;
74 g_top_fud2_id := NULL;
75 g_curr_view := NULL;
76 g_actual_bitand := NULL;
77 g_budget_bitand := NULL;
78 g_hist_actual_bitand := NULL; -- used to display historical data in summary reports
79 g_forecast_bitand := NULL;
80 g_previous_one_end_date := NULL;
81 g_previous_two_end_date := NULL;
82 g_previous_three_end_date := NULL;
83 g_je_source_group := NULL;
84 g_unassigned_id := NULL;
85 g_coaid := NULL;
86 g_curr_per_start := NULL;
87 g_curr_per_end := NULL;
88 g_prior_per_start := NULL;
89 g_prior_per_end := NULL;
90 g_curr_month_start := NULL;
91 g_hist_budget_bitand := NULL;
92 g_amount_type := NULL;
93 g_boundary := NULL;
94 g_boundary_end := NULL;
95 g_prior_boundary_end := NULL;
96 g_amount_type_bitand := NULL;
97 g_snapshot := 'N';
98 g_maj_cat_id := 'All';
99 g_fin_cat_top_node_count :=0; -- g_fin_cat_top_node_count gives the # of Expenses/Revenue top nodes for financial category dimension
100 g_category_id :=0; -- similar to g_fin_catgory_id.. when category parameter chosen is 'All' and we have only one top node,
101 -- it will have top node id..for multiple top nodes scenario, it will be 0..else
102 -- it will have the specific fin category's id..
103 g_udd1_id :=0; -- similar to g_fud1_id.. when fud1 parameter chosen is 'All', it will have top node id else
104 -- it will have the specific fud1 parameter's id..
105 g_dir_msg := NULL;
106 g_min_cat_id := 'All';
107 g_region_code := NULL;
108 g_sd_prior := NULL;
109 g_sd_prior_prior := NULL;
110
111 /* 4439400 Budgets */
112 g_bud_as_of_date := NULL;
113 g_previous_bud_asof_date := NULL;
114 g_id := -9999;
115 g_time_id := NULL; -- global var for storing time_id of g_as_of_date, based on period type
116 g_aggrt_gt_record_count :=0;
117 g_non_aggrt_gt_record_count :=0;
118 g_if_trend_sum_mv := 'N';
119 g_fin_aggregate_flag := 'N';
120 g_ud1_aggregate_flag := 'N';
121 g_company_count :=0;
122 g_cc_count :=0;
123 g_company_is_leaf := 'N';
124 g_cost_center_is_leaf := 'N';
125 g_fin_cat_is_leaf := 'N';
126 g_ud1_is_leaf := 'N';
127 g_ud2_is_leaf := 'N';
128 g_display_sequence := NULL;
129
130 -- Added for P&L Analysis
131 g_five_yr_back := NULL;
132 g_py_sday := NULL;
133 g_exp_asof_date := NULL;
134 g_cy_period_end := NULL;
135 g_ent_pyr_end := NULL;
136 g_actual_period_type := NULL;
137 g_budget_period_type := NULL;
138 g_forecast_period_type := NULL;
139 g_where_period_type := NULL;
140 g_ent_cyr_end := NULL;
141 g_curr_per_sequence := NULL;
142 g_period_type := NULL;
143 g_exp_start := NULL;
144 g_exp_begin_date := NULL;
145 g_fin_type := NULL;
146
147 END reset_globals;
148
149 PROCEDURE get_parameters (
150 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL) IS
151
152 l_unassigned_vset_id NUMBER;
153 l_retcode NUMBER;
154
155 l_sys_month_start DATE;
156 l_max_end_date DATE;
157 l_period_count NUMBER;
158
159 BUDGET_TIME_UNIT VARCHAR2(1);
160 FORECAST_TIME_UNIT VARCHAR2(1);
161
162 BEGIN
163
164 -- -------------------------------------------------
165 -- Parse thru the parameter table and set globals
166 -- -------------------------------------------------
167 IF (p_page_parameter_tbl.count > 0) THEN
168 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
169
170 IF p_page_parameter_tbl(i).parameter_name = 'AS_OF_DATE' THEN
171 g_as_of_date := to_date(p_page_parameter_tbl(i).parameter_value,'DD/MM/YYYY');
172 ELSIF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
173 g_page_period_type := p_page_parameter_tbl(i).parameter_value;
174 ELSIF p_page_parameter_tbl(i).parameter_name = 'CURRENCY+FII_CURRENCIES' THEN
175 g_currency := substr(p_page_parameter_tbl(i).parameter_id,2,11);
176 ELSIF p_page_parameter_tbl(i).parameter_name = 'VIEW_BY' THEN
177 g_view_by := p_page_parameter_tbl(i).parameter_value;
178 ELSIF p_page_parameter_tbl(i).parameter_name = 'BIS_REGION_CODE' THEN
179 g_region_code := p_page_parameter_tbl(i).parameter_value;
180 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME_COMPARISON_TYPE' THEN
181 g_time_comp := p_page_parameter_tbl(i).parameter_value;
182 ELSIF p_page_parameter_tbl(i).parameter_name = 'BIS_PREVIOUS_ASOF_DATE' THEN
183 g_previous_asof_date := to_date(p_page_parameter_tbl(i).parameter_value,'DD/MM/YYYY');
184 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_COMPANIES+FII_COMPANIES' THEN
185 g_company_id := nvl(replace(p_page_parameter_tbl(i).parameter_id,'''', null),'All');
186 ELSIF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+HRI_CL_ORGCC' THEN
187 g_cost_center_id := nvl(replace(p_page_parameter_tbl(i).parameter_id, '''', null),'All');
188 ELSIF p_page_parameter_tbl(i).parameter_name = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
189 g_fin_category_id := nvl(replace(p_page_parameter_tbl(i).parameter_id, '''', null), 'All');
190 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_LEDGER+FII_LEDGER' THEN
191 g_ledger_id := nvl(replace(p_page_parameter_tbl(i).parameter_id, '''', null),'All');
192 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN
193 g_fud1_id := nvl(replace(p_page_parameter_tbl(i).parameter_id, '''', null),'All');
194 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_USER_DEFINED+FII_USER_DEFINED_2' THEN
195 g_fud2_id := nvl(replace(p_page_parameter_tbl(i).parameter_id, '''', null),'All');
196 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_EA_JE_SOURCE_GROUP' THEN
197 g_je_source_group := p_page_parameter_tbl(i).parameter_id;
198 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_ASSET_CATEGORIES+FII_ASSET_CAT_MAJOR' THEN
199 g_maj_cat_id := nvl(replace(p_page_parameter_tbl(i).parameter_id,'''', null),'All');
200 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_ASSET_CATEGORIES+FII_ASSET_CAT_MINOR' THEN
201 g_min_cat_id := nvl(replace(p_page_parameter_tbl(i).parameter_id,'''', null),'All');
202 ELSIF p_page_parameter_tbl(i).parameter_name = 'BIS_ICX_SESSION_ID' THEN
203 g_session_id := NVL(p_page_parameter_tbl(i).parameter_value,0);
204 END IF;
205 END LOOP;
206 END IF;
207
208 -- Following check is required for COGS Rolling Trend report (P&L Analysis)
209
210 IF g_page_period_type IS NULL THEN
211 g_page_period_type := 'FII_TIME_ENT_PERIOD';
212 END IF;
213 -- -----------------------------------------------------------
214 -- Added for P&L Analysis
215 -- Get budget/forecast levels FROM profile options
216 -- Default assume budget & forecast are loaded at period level
217 -- -----------------------------------------------------------
218 BUDGET_TIME_UNIT := NVL(FND_PROFILE.Value( 'FII_BUDGET_TIME_UNIT'),'P');
219 FORECAST_TIME_UNIT := NVL(FND_PROFILE.Value( 'FII_FORECAST_TIME_UNIT'),'P');
220
221 SELECT nvl(min(start_date), trunc(sysdate)) INTO g_min_start_date
222 FROM fii_time_ent_period;
223
224 SELECT nvl(max(end_date), trunc(sysdate)), count(1) INTO l_max_end_date, l_period_count
225 FROM fii_time_ent_period;
226
227 SELECT NVL(fii_time_api.ent_cper_START(trunc(sysdate)), l_max_end_date) INTO l_sys_month_start FROM DUAL;
228
229 /* 4439400 : Added g_bud_as_of_date for Public Sector Budgets */
230 IF g_as_of_date > trunc(sysdate) THEN
231 SELECT NVL(fii_time_api.ent_cper_END(g_as_of_date),l_max_end_date) INTO g_as_of_date FROM dual;
232 g_bud_as_of_date := g_as_of_date;
233 ELSIF g_as_of_date < trunc(sysdate) AND g_as_of_date >= l_sys_month_start THEN
234 SELECT NVL(fii_time_api.ent_pper_END(g_as_of_date),l_max_end_date) INTO g_as_of_date FROM dual;
235 g_bud_as_of_date := g_as_of_date;
236 ELSIF g_as_of_date = trunc(sysdate) THEN
237 g_as_of_date := nvl(to_date(FND_PROFILE.value('FII_TEST_SYSDATE'), 'DD/MM/YYYY'),trunc(sysdate));
238 SELECT NVL(fii_time_api.ent_cper_END(g_as_of_date),l_max_end_date) INTO g_bud_as_of_date FROM dual;
239 g_snapshot := 'Y';
240 IF l_period_count = 0 THEN --time dimension is null, so join to fii_time_structures which is null to report no data found.
241 g_snapshot := 'N';
242 END IF;
243 ELSE
244 SELECT NVL(fii_time_api.ent_cper_END(g_as_of_date),l_max_end_date) INTO g_as_of_date FROM dual;
245 g_bud_as_of_date := g_as_of_date;
246 END IF;
247
248 SELECT nvl(min(start_date), g_min_start_date) INTO g_curr_month_start
249 FROM fii_time_ent_period
250 WHERE g_as_of_date between start_date and END_date;
251
252
253 IF g_previous_asof_date IS NULL THEN
254 g_previous_asof_date := g_min_start_date;
255 END IF;
256
257 -- Added for P&L Analysis
258 SELECT NVL( fii_time_api.sd_lyswk(g_as_of_date),g_min_start_date),
259 NVL(fii_time_api.ent_pyr_end(g_as_of_date),g_min_start_date),
260 NVL(fii_time_api.ent_cyr_end(g_as_of_date),g_min_start_date)
261 INTO g_py_sday,
262 g_ent_pyr_end,
263 g_ent_cyr_end
264 FROM dual;
265
266 CASE g_page_period_type
267 WHEN 'FII_TIME_WEEK' THEN g_actual_period_type := 11;
268 WHEN 'FII_TIME_ENT_PERIOD' THEN g_actual_period_type := 23;
269 WHEN 'FII_TIME_ENT_QTR' THEN g_actual_period_type := 55;
270 WHEN 'FII_TIME_ENT_YEAR' THEN g_actual_period_type := 119;
271 END CASE;
272
273 CASE g_page_period_type
274 WHEN 'FII_TIME_WEEK' THEN g_period_type := 16;
275 WHEN 'FII_TIME_ENT_PERIOD' THEN g_period_type := 32;
276 WHEN 'FII_TIME_ENT_QTR' THEN g_period_type := 64;
277 WHEN 'FII_TIME_ENT_YEAR' THEN g_period_type := 128;
278 END CASE;
279
280 CASE BUDGET_TIME_UNIT
281 WHEN 'D' then
282 g_budget_period_type := g_actual_period_type;
283 WHEN 'P' THEN
284 CASE g_page_period_type
285 WHEN 'FII_TIME_WEEK' THEN g_budget_period_type := 0;
286 WHEN 'FII_TIME_ENT_PERIOD' THEN g_budget_period_type := 256;
287 WHEN 'FII_TIME_ENT_QTR' THEN g_budget_period_type := 512;
288 WHEN 'FII_TIME_ENT_YEAR' THEN g_budget_period_type := 128;
289 END CASE;
290 WHEN 'Q' THEN
291 CASE g_page_period_type
292 WHEN 'FII_TIME_WEEK' THEN g_budget_period_type := 0;
293 WHEN 'FII_TIME_ENT_PERIOD' THEN g_budget_period_type := 0;
294 WHEN 'FII_TIME_ENT_QTR' THEN g_budget_period_type := 512;
295 WHEN 'FII_TIME_ENT_YEAR' THEN g_budget_period_type := 128;
296 END CASE;
297 WHEN 'Y' THEN
298 CASE g_page_period_type
299 WHEN 'FII_TIME_WEEK' THEN g_budget_period_type := 0;
300 WHEN 'FII_TIME_ENT_PERIOD' THEN g_budget_period_type := 0;
301 WHEN 'FII_TIME_ENT_QTR' THEN g_budget_period_type := 0;
302 WHEN 'FII_TIME_ENT_YEAR' THEN g_budget_period_type := 128;
303 END CASE;
304 END CASE;
305
306 CASE FORECAST_TIME_UNIT
307 WHEN 'D' THEN
308 g_forecast_period_type := g_actual_period_type;
309 WHEN 'P' THEN
310 CASE g_page_period_type
311 WHEN 'FII_TIME_WEEK' THEN g_forecast_period_type := 0;
312 WHEN 'FII_TIME_ENT_PERIOD' THEN g_forecast_period_type := 256;
313 WHEN 'FII_TIME_ENT_QTR' THEN g_forecast_period_type := 512;
314 WHEN 'FII_TIME_ENT_YEAR' THEN g_forecast_period_type := 128;
315 END CASE;
316 WHEN 'Q' THEN
317 CASE g_page_period_type
318 WHEN 'FII_TIME_WEEK' THEN g_forecast_period_type := 0;
319 WHEN 'FII_TIME_ENT_PERIOD' THEN g_forecast_period_type := 0;
320 WHEN 'FII_TIME_ENT_QTR' THEN g_forecast_period_type := 512;
321 WHEN 'FII_TIME_ENT_YEAR' THEN g_forecast_period_type := 128;
322 END CASE;
323 WHEN 'Y' THEN
324 CASE g_page_period_type
325 WHEN 'FII_TIME_WEEK' THEN g_forecast_period_type := 0;
326 WHEN 'FII_TIME_ENT_PERIOD' THEN g_forecast_period_type := 0;
327 WHEN 'FII_TIME_ENT_QTR' THEN g_forecast_period_type := 0;
328 WHEN 'FII_TIME_ENT_YEAR' THEN g_forecast_period_type := 128;
329 END CASE;
330 END CASE;
331
332 CASE g_page_period_type
333 WHEN 'FII_TIME_WEEK' THEN g_where_period_type := 11;
334 WHEN 'FII_TIME_ENT_PERIOD' THEN g_where_period_type := 279; --1+2+4+16+256
335 WHEN 'FII_TIME_ENT_QTR' THEN g_where_period_type := 823; --1+2+4+16+32+256+512
336 WHEN 'FII_TIME_ENT_YEAR' THEN g_where_period_type := 1015; --1+2+4+16+32+64+128+256+512
337 END CASE;
338
339 -- -------------------------------------------------
340 -- If user views in primary global, use 1st view which
341 -- SELECTs the primary amount. For secondary global
342 -- currency, use 2nd view which SELECTs secondary amount
343 -- Default assumed to be viewing data in primary global
344 -- -------------------------------------------------
345 IF g_currency = 'FII_GLOBAL1' THEN
346 g_curr_view := '_p_v';
347 ELSIF g_currency = 'FII_GLOBAL2' THEN
348 g_curr_view := '_s_v';
349 ELSE
350 g_curr_view := '_p_v';
351 END IF;
352
353 SELECT NVL(fii_time_api.ent_sd_lysper_end(g_as_of_date), g_min_start_date) INTO g_sd_prior FROM DUAL;
354 SELECT NVL(fii_time_api.ent_sd_lysper_end(fii_time_api.ent_sd_lysper_end(g_as_of_date)), g_min_start_date) INTO g_sd_prior_prior FROM DUAL;
355
356 /* Bug 4439400: Added g_previous_bud_asof_date for public sector budget */
357 CASE g_page_period_type
358 WHEN 'FII_TIME_ENT_PERIOD' THEN
359 g_actual_bitand := 64;
360 g_hist_actual_bitand := 64;
361 g_budget_bitand := 4;
362 g_hist_budget_bitand := 4;
363 g_forecast_bitand := 4;
364 IF g_time_comp = 'SEQUENTIAL' THEN
365 SELECT NVL(fii_time_api.ent_sd_pper_end(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;
366 SELECT NVL(fii_time_api.ent_sd_pper_end(g_bud_as_of_date),g_min_start_date) INTO g_previous_bud_asof_date FROM dual;
367 ELSE
368 SELECT NVL(fii_time_api.ent_sd_lysper_END(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;
369 SELECT NVL(fii_time_api.ent_sd_lysper_END(g_bud_as_of_date),g_min_start_date) INTO g_previous_bud_asof_date FROM dual;
370 END IF;
371 SELECT NVL(fii_time_api.ent_cper_start(g_as_of_date), g_min_start_date) INTO g_curr_per_start FROM DUAL;
372 SELECT NVL(fii_time_api.ent_cper_end(g_as_of_date), g_min_start_date) INTO g_curr_per_end FROM DUAL;
373 SELECT NVL(fii_time_api.ent_cper_start(g_previous_asof_date), g_min_start_date) INTO g_prior_per_start FROM DUAL;
374 SELECT NVL(fii_time_api.ent_cper_end(g_previous_asof_date), g_min_start_date) INTO g_prior_per_end FROM DUAL;
375
376 SELECT ent_period_id INTO g_time_id
377 FROM fii_time_ent_period per
378 WHERE g_as_of_date BETWEEN start_date AND end_date;
379
380 -- Added for P&L Analysis
381 SELECT NVL(fii_time_api.ent_pper_end(g_as_of_date),g_min_start_date),
382 NVL(fii_time_api.ent_sd_lysper_end(g_as_of_date),g_min_start_date)
383 INTO g_cy_period_end,
384 g_exp_asof_date
385 FROM DUAL;
386
387 SELECT DISTINCT a.sequence INTO g_curr_per_sequence
388 FROM fii_time_ent_period a
389 WHERE g_as_of_date BETWEEN a.START_DATE AND a.END_DATE;
390
391 SELECT NVL(fii_time_api.ent_sd_lysper_end(g_exp_asof_date),g_min_start_date)
392 INTO g_exp_start
393 FROM dual;
394
395 g_exp_begin_date := g_exp_asof_date;
396
397 WHEN 'FII_TIME_ENT_QTR' THEN
398 g_actual_bitand := 128;
399 g_hist_actual_bitand := 64;
400 g_budget_bitand := 8;
401 g_hist_budget_bitand := 4;
402 g_forecast_bitand := 8;
403 IF g_time_comp = 'SEQUENTIAL' THEN
404 SELECT NVL(fii_time_api.ent_sd_pqtr_end(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;
405 SELECT NVL(fii_time_api.ent_sd_pqtr_end(g_bud_as_of_date),g_min_start_date) INTO g_previous_bud_asof_date FROM dual;
406 ELSE
407 SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;
408 SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_bud_as_of_date),g_min_start_date) INTO g_previous_bud_asof_date FROM dual;
409 END IF;
410 SELECT NVL(fii_time_api.ent_cqtr_start(g_as_of_date), g_min_start_date) INTO g_curr_per_start FROM DUAL;
411 SELECT NVL(fii_time_api.ent_cqtr_end(g_as_of_date), g_min_start_date) INTO g_curr_per_end FROM DUAL;
412 SELECT NVL(fii_time_api.ent_cqtr_start(g_previous_asof_date), g_min_start_date) INTO g_prior_per_start FROM DUAL;
413 SELECT NVL(fii_time_api.ent_cqtr_end(g_previous_asof_date), g_min_start_date) INTO g_prior_per_end FROM DUAL;
414
415 SELECT ent_qtr_id INTO g_time_id
416 FROM fii_time_ent_period per
417 WHERE g_as_of_date BETWEEN start_date AND end_date;
418
419 -- Added for P&L Analysis
420 SELECT NVL( fii_time_api.ent_pqtr_end(g_as_of_date),g_min_start_date) INTO g_cy_period_end FROM dual;
421
422 IF (g_time_comp = 'SEQUENTIAL') THEN
423 SELECT DISTINCT a.ent_qtr_id INTO g_curr_per_sequence
424 FROM fii_time_ent_qtr a
425 WHERE g_as_of_date BETWEEN a.START_DATE AND a.END_DATE;
426
427 SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_as_of_date),g_min_start_date)
428 INTO g_exp_asof_date
429 FROM dual;
430
431 SELECT NVL(fii_time_api.ent_sd_lysqtr_end(fii_time_api.ent_sd_lysqtr_end(g_exp_asof_date)),g_min_start_date)
432 INTO g_exp_begin_date
433 FROM dual;
434 ELSE
435 SELECT DISTINCT a.sequence INTO g_curr_per_sequence
436 FROM fii_time_ent_qtr a
437 WHERE g_as_of_date BETWEEN a.START_DATE AND a.END_DATE;
438
439 SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_as_of_date),g_min_start_date)
440 INTO g_exp_asof_date
441 FROM dual;
442
443 g_exp_begin_date := g_exp_asof_date;
444 END IF;
445
446 SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_exp_asof_date),g_min_start_date)
447 INTO g_exp_start
448 FROM dual;
449
450 WHEN 'FII_TIME_ENT_YEAR' THEN
451 g_actual_bitand := 256;
452 g_hist_actual_bitand := 128;
453 g_budget_bitand := 16;
454 g_hist_budget_bitand := 8;
455 g_forecast_bitand := 16;
456 SELECT NVL(fii_time_api.ent_sd_lyr_end(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;
457 SELECT NVL(fii_time_api.ent_sd_lyr_end(g_bud_as_of_date),g_min_start_date) INTO g_previous_bud_asof_date FROM dual;
458 SELECT NVL(fii_time_api.ent_cyr_start(g_as_of_date), g_min_start_date) INTO g_curr_per_start FROM DUAL;
459 SELECT NVL(fii_time_api.ent_cyr_end(g_as_of_date), g_min_start_date) INTO g_curr_per_end FROM DUAL;
460 SELECT NVL(fii_time_api.ent_cyr_start(g_previous_asof_date), g_min_start_date) INTO g_prior_per_start FROM DUAL;
461 SELECT NVL(fii_time_api.ent_cyr_end(g_previous_asof_date), g_min_start_date) INTO g_prior_per_end FROM DUAL;
462
463 SELECT ent_year_id INTO g_time_id
464 FROM fii_time_ent_period per
465 WHERE g_as_of_date BETWEEN start_date AND end_date;
466
467 SELECT NVL(MAX(sequence),0)
468 INTO g_display_sequence
469 FROM fii_time_ent_period
470 WHERE start_date >= g_curr_per_start
471 AND end_date <= g_as_of_date;
472
473 -- Added for P&L Analysis
474 g_cy_period_end := NULL;
475 g_curr_per_sequence := NULL;
476 g_exp_asof_date := NULL;
477 g_exp_start := NULL;
478
479 ELSE g_actual_bitand := 64;
480 END CASE;
481
482 FII_GL_EXTRACTION_UTIL.get_unassigned_id(g_unassigned_id, l_unassigned_vset_id, l_retcode);
483
484 g_amount_type := NVL(FND_PROFILE.value('FII_PSI_AMOUNT_TYPE'), 'YTD');
485 g_boundary := NVL(FND_PROFILE.value('FII_PSI_BOUNDARY'), 'Y');
486
487 IF g_boundary = 'P' THEN
488 g_boundary_end := NVL(fii_time_api.ent_cper_end(g_as_of_date), g_min_start_date);
489 g_prior_boundary_end := NVL(fii_time_api.ent_cper_end(g_previous_asof_date), g_min_start_date);
490 ELSIF g_boundary = 'Q' THEN
491 g_boundary_end := NVL(fii_time_api.ent_cqtr_end(g_as_of_date), g_min_start_date);
492 g_prior_boundary_end := NVL(fii_time_api.ent_cqtr_end(g_previous_asof_date), g_min_start_date);
493 ELSIF g_boundary ='Y' THEN
494 g_boundary_end := NVL(fii_time_api.ent_cyr_end(g_as_of_date), g_min_start_date);
495 g_prior_boundary_end := NVL(fii_time_api.ent_cyr_end(g_previous_asof_date), g_min_start_date);
496 END IF;
497
498 IF g_amount_type ='PTD' THEN
499 g_amount_type_bitand := 64;
500 ELSIF g_amount_type ='QTD' THEN
501 g_amount_type_bitand := 128;
502 ELSIF g_amount_type ='YTD' THEN
503 g_amount_type_bitand := 256;
504 END IF;
505
506 g_dir_msg := FND_MESSAGE.get_string('FII', 'FII_GL_DIR');
507
508 END get_parameters;
509
510
511 PROCEDURE get_rolling_period
512 IS
513
514 BEGIN
515
516
517 CASE g_page_period_type
518
519 WHEN 'FII_TIME_ENT_YEAR' THEN
520 SELECT NVL(fii_time_api.ent_pqtr_END(g_as_of_date),g_min_start_date) INTO g_previous_one_END_date FROM dual;
521 SELECT NVL(fii_time_api.ent_pqtr_END(g_previous_one_END_date),g_min_start_date) INTO g_previous_two_END_date FROM dual;
522 SELECT NVL(fii_time_api.ent_pqtr_END(g_previous_two_END_date),g_min_start_date) INTO g_previous_three_END_date FROM dual;
523 WHEN 'FII_TIME_ENT_QTR' THEN
524 SELECT NVL(fii_time_api.ent_pper_END(g_as_of_date),g_min_start_date) INTO g_previous_one_END_date FROM dual;
525 SELECT NVL(fii_time_api.ent_pper_END(g_previous_one_END_date),g_min_start_date) INTO g_previous_two_END_date FROM dual;
526 SELECT NVL(fii_time_api.ent_pper_END(g_previous_two_END_date),g_min_start_date) INTO g_previous_three_END_date FROM dual;
527 WHEN 'FII_TIME_ENT_PERIOD' THEN
528 g_previous_one_END_date := NULL;
529 g_previous_two_END_date := NULL;
530 g_previous_three_END_date :=NULL;
531 END CASE;
532
533 END get_rolling_period;
534
535 PROCEDURE get_viewby_id(p_aggrt_viewby_id OUT NOCOPY VARCHAR2, p_snap_aggrt_viewby_id OUT NOCOPY VARCHAR2, p_nonaggrt_viewby_id OUT NOCOPY VARCHAR2) IS
536
537 -- fix for bug 4127077. The following cursor checks for the presence of any top node which is also a leaf node
538
539 CURSOR get_leaf_top_nodes(g_fin_cat_type VARCHAR2) IS
540 SELECT a.fin_category_id
541 FROM fii_fin_cat_type_assgns a, fii_fin_item_leaf_hiers b
542 WHERE a.FIN_CAT_TYPE_CODE = g_fin_cat_type
543 AND a.TOP_NODE_FLAG = 'Y'
544 and a.fin_category_id = b.CHILD_FIN_CAT_ID
545 and b.is_leaf_flag = 'Y';
546 BEGIN
547
548 FOR a IN get_leaf_top_nodes (g_fin_cat_type)
549 LOOP
550 g_top_node_is_leaf := 'Y';
551 END LOOP;
552
553 CASE g_view_by
554 WHEN 'FII_COMPANIES+FII_COMPANIES' THEN
555 p_aggrt_viewby_id := 'f.company_id';
556 p_snap_aggrt_viewby_id := 'f.company_id';
557 p_nonaggrt_viewby_id := 'co_hier.parent_company_id';
558 WHEN 'ORGANIZATION+HRI_CL_ORGCC' THEN
559 p_aggrt_viewby_id := 'f.cost_center_id';
560 p_snap_aggrt_viewby_id := 'f.cost_center_id';
561 p_nonaggrt_viewby_id := 'cc_hier.parent_cc_id';
562 WHEN 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
563 IF g_fin_category_id = 'All' THEN
564 SELECT count(*) INTO g_fin_cat_top_node_count
565 FROM fii_fin_cat_type_assgns a
566 WHERE a.FIN_CAT_TYPE_CODE = g_fin_cat_type
567 AND a.TOP_NODE_FLAG = 'Y';
568
569 IF g_top_node_is_leaf = 'Y' THEN
570 p_aggrt_viewby_id := 'f.fin_category_id';
571 p_snap_aggrt_viewby_id := 'f.fin_category_id';
572 ELSIF g_fin_cat_top_node_count = 1 THEN
573 p_aggrt_viewby_id := 'f.fin_category_id';
574 p_snap_aggrt_viewby_id := 'f.fin_category_id';
575 ELSE
576 p_aggrt_viewby_id := 'f.parent_fin_category_id';
577 p_snap_aggrt_viewby_id := 'f.parent_fin_category_id';
578 END IF;
579 ELSE
580 p_aggrt_viewby_id := 'f.fin_category_id';
581 p_snap_aggrt_viewby_id := 'f.fin_category_id';
582 END IF;
583 p_nonaggrt_viewby_id := 'fin_hier.parent_fin_cat_id';
584
585 WHEN 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN
586 p_aggrt_viewby_id := 'f.fud1_id';
587 p_snap_aggrt_viewby_id := 'f.fud1_id';
588 p_nonaggrt_viewby_id := 'fud1_hier.parent_value_id';
589 WHEN 'FII_USER_DEFINED+FII_USER_DEFINED_2' THEN
590 p_aggrt_viewby_id := 'inner_inline_view.fud2_id';
591 p_snap_aggrt_viewby_id := 'gt.fud2_id';
592 p_nonaggrt_viewby_id := 'fud2_hier.parent_value_id';
593 END CASE;
594
595 END get_viewby_id;
596
597 ----------------------------------------------------------
598
599 PROCEDURE insert_into_aggrt_gt IS
600
601 BEGIN
602
603 FOR a IN company_table.FIRST..company_table.LAST LOOP
604 FOR b IN cc_table.FIRST..cc_table.LAST LOOP
605 FOR c IN fin_cat_table.FIRST..fin_cat_table.LAST LOOP
606 FOR d IN fud1_table.FIRST..fud1_table.LAST LOOP
607 FOR e IN fud2_table.FIRST..fud2_table.LAST LOOP
608 g_aggrt_gt_record_count := g_aggrt_gt_record_count+1;
609 par_comp_id_aggrt_plsql(g_aggrt_gt_record_count) := company_table(a).par_id;
610 comp_id_aggrt_plsql(g_aggrt_gt_record_count) := company_table(a).id;
611 par_cc_id_aggrt_plsql(g_aggrt_gt_record_count) := cc_table(b).par_id;
612 cc_id_aggrt_plsql(g_aggrt_gt_record_count) := cc_table(b).id;
613 par_fin_cat_id_aggrt_plsql(g_aggrt_gt_record_count) := fin_cat_table(c).par_id;
614 fin_cat_id_aggrt_plsql(g_aggrt_gt_record_count) := fin_cat_table(c).id;
615 par_fud1_id_aggrt_plsql(g_aggrt_gt_record_count) := fud1_table(d).par_id;
616 fud1_id_aggrt_plsql(g_aggrt_gt_record_count) := fud1_table(d).id;
617 fud2_id_aggrt_plsql(g_aggrt_gt_record_count) := fud2_table(e).id;
618 CASE g_view_by
619 WHEN 'FII_COMPANIES+FII_COMPANIES' THEN
620 aggrt_viewbydescription(g_aggrt_gt_record_count) := company_table(a).description;
621 sort_order_aggrt_plsql(g_aggrt_gt_record_count) := company_table(a).sort_order;
622 WHEN 'ORGANIZATION+HRI_CL_ORGCC' THEN
623 aggrt_viewbydescription(g_aggrt_gt_record_count) := cc_table(b).description;
624 sort_order_aggrt_plsql(g_aggrt_gt_record_count) := cc_table(b).sort_order;
625 WHEN 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
626 aggrt_viewbydescription(g_aggrt_gt_record_count) := fin_cat_table(c).description;
627 sort_order_aggrt_plsql(g_aggrt_gt_record_count) := fin_cat_table(c).sort_order;
628 WHEN 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN
629 aggrt_viewbydescription(g_aggrt_gt_record_count) := fud1_table(d).description;
630 sort_order_aggrt_plsql(g_aggrt_gt_record_count) := fud1_table(d).sort_order;
631 WHEN 'FII_USER_DEFINED+FII_USER_DEFINED_2' THEN
632 aggrt_viewbydescription(g_aggrt_gt_record_count) :=fud2_table(e).description;
633 sort_order_aggrt_plsql(g_aggrt_gt_record_count) := fud2_table(e).sort_order;
634 ELSE
635 aggrt_viewbydescription(g_aggrt_gt_record_count) := NULL;
636 sort_order_aggrt_plsql(g_aggrt_gt_record_count) := NULL;
637 END CASE;
638 END LOOP;
639 END LOOP;
640 END LOOP;
641 END LOOP;
642 END LOOP;
643
644 END insert_into_aggrt_gt;
645
646 PROCEDURE insert_into_non_aggrt_gt IS
647
648 BEGIN
649
650 FOR a IN company_table.FIRST..company_table.LAST LOOP
651 FOR b IN cc_table.FIRST..cc_table.LAST LOOP
652 FOR c IN fin_cat_table.FIRST..fin_cat_table.LAST LOOP
653 FOR d IN fud1_table.FIRST..fud1_table.LAST LOOP
654 FOR e IN fud2_table.FIRST..fud2_table.LAST LOOP
655 g_non_aggrt_gt_record_count := g_non_aggrt_gt_record_count+1;
656 comp_id_nonaggrt_plsql(g_non_aggrt_gt_record_count) := company_table(a).id;
657 cc_id_nonaggrt_plsql(g_non_aggrt_gt_record_count) := cc_table(b).id;
658 fin_cat_id_nonaggrt_plsql(g_non_aggrt_gt_record_count) := fin_cat_table(c).id;
659 fud1_id_nonaggrt_plsql(g_non_aggrt_gt_record_count) := fud1_table(d).id;
660 fud2_id_nonaggrt_plsql(g_non_aggrt_gt_record_count) := fud2_table(e).id;
661 CASE g_view_by
662 WHEN 'FII_COMPANIES+FII_COMPANIES' THEN
663 nonaggrt_viewbydescription(g_non_aggrt_gt_record_count) := company_table(a).description;
664 sort_order_nonaggrt_plsql(g_non_aggrt_gt_record_count) := company_table(a).sort_order;
665 WHEN 'ORGANIZATION+HRI_CL_ORGCC' THEN
666 nonaggrt_viewbydescription(g_non_aggrt_gt_record_count) := cc_table(b).description;
667 sort_order_nonaggrt_plsql(g_non_aggrt_gt_record_count) := cc_table(b).sort_order;
668 WHEN 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
669 nonaggrt_viewbydescription(g_non_aggrt_gt_record_count) := fin_cat_table(c).description;
670 sort_order_nonaggrt_plsql(g_non_aggrt_gt_record_count) := fin_cat_table(c).sort_order;
671 WHEN 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN
672 nonaggrt_viewbydescription(g_non_aggrt_gt_record_count) := fud1_table(d).description;
673 sort_order_nonaggrt_plsql(g_non_aggrt_gt_record_count) := fud1_table(d).sort_order;
674 WHEN 'FII_USER_DEFINED+FII_USER_DEFINED_2' THEN
675 nonaggrt_viewbydescription(g_non_aggrt_gt_record_count) :=fud2_table(e).description;
676 sort_order_nonaggrt_plsql(g_non_aggrt_gt_record_count) := fud2_table(e).sort_order;
677 ELSE
678 nonaggrt_viewbydescription(g_non_aggrt_gt_record_count) := NULL;
679 sort_order_nonaggrt_plsql(g_non_aggrt_gt_record_count) := NULL;
680 END CASE;
681 END LOOP;
682 END LOOP;
683 END LOOP;
684 END LOOP;
685 END LOOP;
686
687 END insert_into_non_aggrt_gt;
688
689 /* below mentioned procedure forms join for company dimension, when company parameter chosen is All.
690 It also inserts records into company dimension PL/SQL table */
691
692 PROCEDURE form_all_company_join(p_comp_agg_flag IN VARCHAR2, p_cc_agg_flag IN VARCHAR2,p_company_id IN NUMBER) IS
693
694 l_company_sql VARCHAR2(10000);
695 l_company_join VARCHAR2(10000);
696 l_leaf_flag VARCHAR2(1);
697 l_comp_agg_flag VARCHAR2(1);
698
699
700 BEGIN
701
702 l_comp_agg_flag := p_comp_agg_flag;
703
704 IF g_view_by = 'FII_COMPANIES+FII_COMPANIES' THEN
705
706 IF g_company_count = 1 THEN -- WHEN only 1 record in security
707 -- table FOR that user, we will check for_viewby_flag ELSE security table's aggregated_flag will be used
708
709 SELECT for_viewby_flag INTO l_comp_agg_flag
710 FROM fii_com_pmv_agrt_nodes
711 WHERE company_id = p_company_id;
712
713 check_if_leaf(p_company_id);
714
715 IF g_company_is_leaf = 'Y' THEN
716 IF g_if_trend_sum_mv = 'Y' OR (l_comp_agg_flag = 'Y' and p_cc_agg_flag = 'Y' and g_fin_aggregate_flag = 'Y' and g_ud1_aggregate_flag = 'Y') THEN
717 SELECT parent_company_id INTO g_parent_company_id
718 FROM fii_company_hierarchies
719 WHERE child_company_id = p_company_id
720 and parent_level = child_level-1;
721 l_company_join := 'parent_company_id = '||g_parent_company_id||'
722 and child_company_id = '||p_company_id;
723 ELSE
724 l_company_join := 'parent_company_id = '||p_company_id||'
725 and child_level = parent_level';
726 END IF;
727 ELSE
728 l_company_join := 'parent_company_id = '||p_company_id||'
729 and child_level = parent_level+1';
730 END IF;
731
732 ELSE -- we have > 1 record in company security table FOR that user
733
734 IF g_if_trend_sum_mv = 'Y' OR (l_comp_agg_flag = 'Y' and p_cc_agg_flag = 'Y' and g_fin_aggregate_flag = 'Y' and g_ud1_aggregate_flag = 'Y') THEN
735 SELECT parent_company_id INTO g_parent_company_id
736 FROM fii_company_hierarchies
737 WHERE child_company_id = p_company_id
738 and parent_level = child_level-1;
739 l_company_join := 'parent_company_id = '||g_parent_company_id||'
740 and child_company_id = '||p_company_id;
741 ELSE
742 l_company_join := 'parent_company_id = '||p_company_id||'
743 and child_company_id = '||p_company_id;
744 END IF;
745 END IF; -- END of IF g_company_count=1 IF LOOP
746
747 ELSE -- non viewby company
748 IF g_if_trend_sum_mv = 'Y' OR (l_comp_agg_flag = 'Y' and p_cc_agg_flag = 'Y' and g_fin_aggregate_flag = 'Y' and g_ud1_aggregate_flag = 'Y') THEN
749 SELECT parent_company_id INTO g_parent_company_id
750 FROM fii_company_hierarchies
751 WHERE child_company_id = p_company_id
752 and parent_level = child_level-1;
753 l_company_join := 'parent_company_id = '||g_parent_company_id||'
754 and child_company_id = '||p_company_id;
755 ELSE
756 l_company_join := 'parent_company_id = '||p_company_id||'
757 and child_company_id = '||p_company_id;
758 END IF;
759 END IF; -- END of IF viewby company control structure
760
761 IF g_view_by = 'FII_COMPANIES+FII_COMPANIES' THEN
762 l_company_sql := 'SELECT co_hier.parent_company_id, co_hier.next_level_company_id, co_hier.next_level_company_sort_order, viewby_dim.description
763 FROM fii_company_hierarchies co_hier, fnd_flex_values_tl viewby_dim
764 WHERE viewby_dim.flex_value_id = co_hier.next_level_company_id
765 and viewby_dim.language = userenv(''LANG'')
766 and '||l_company_join;
767 ELSE -- we dont need to join to fnd_flex_values_tl hence passing NULL description
768 l_company_sql := 'SELECT co_hier.parent_company_id, co_hier.next_level_company_id, NULL,NULL
769 FROM fii_company_hierarchies co_hier
770 WHERE '||l_company_join;
771 END IF;
772
773 -- fetch records in company dimension's pl/sql table of records
774
775 EXECUTE IMMEDIATE l_company_sql BULK COLLECT INTO company_table;
776
777 END form_all_company_join;
778
779 /* below mentioned procedure forms join for company dimension, when any specific value is chosen for
780 company parameter. It also inserts records into company dimension PL/SQL table */
781
782 PROCEDURE form_specific_company_join(p_comp_aggregate_flag IN VARCHAR2, p_cc_agg_flag IN VARCHAR2) IS
783
784 l_company_sql VARCHAR2(10000);
785 l_company_join VARCHAR2(10000);
786 l_leaf_flag VARCHAR2(1);
787
788 BEGIN
789
790 IF g_if_trend_sum_mv = 'Y' OR (p_comp_aggregate_flag = 'Y' and p_cc_agg_flag = 'Y' and g_fin_aggregate_flag = 'Y' and g_ud1_aggregate_flag = 'Y') THEN
791 IF g_view_by = 'FII_COMPANIES+FII_COMPANIES' THEN
792
793 check_if_leaf(g_company_id);
794
795 IF g_company_is_leaf = 'Y' THEN
796 SELECT parent_company_id INTO g_parent_company_id
797 FROM fii_company_hierarchies
798 WHERE child_company_id = g_company_id
799 and parent_level = child_level-1;
800 l_company_join := 'parent_company_id ='||g_parent_company_id||'
801 and child_company_id ='||g_company_id;
802 ELSE
803 l_company_join := 'parent_company_id ='||g_company_id||'
804 and child_level = parent_level + 1';
805 END IF;
806 ELSE -- nonviewby company
807 SELECT parent_company_id INTO g_parent_company_id
808 FROM fii_company_hierarchies
809 WHERE child_company_id = g_company_id
810 and parent_level = child_level-1;
811 l_company_join := 'parent_company_id ='||g_parent_company_id||'
812 and child_company_id ='||g_company_id;
813 END IF;
814 ELSE -- non aggrt MV is being hit
815 IF g_view_by = 'FII_COMPANIES+FII_COMPANIES' THEN
816
817 check_if_leaf(g_company_id);
818
819 IF g_company_is_leaf = 'Y' THEN
820 l_company_join := 'parent_company_id ='||g_company_id||'
821 and child_company_id ='||g_company_id;
822 ELSE
823 l_company_join := 'parent_company_id ='||g_company_id||'
824 and child_level = parent_level + 1';
825 END IF;
826 ELSE
827 l_company_join := 'parent_company_id ='||g_company_id||'
828 and child_company_id ='||g_company_id;
829 END IF;
830 END IF;
831
832 IF g_view_by = 'FII_COMPANIES+FII_COMPANIES' THEN
833 l_company_sql := 'SELECT co_hier.parent_company_id,co_hier.next_level_company_id, co_hier.next_level_company_sort_order, viewby_dim.description
834 FROM fii_company_hierarchies co_hier, fnd_flex_values_tl viewby_dim
835 WHERE viewby_dim.flex_value_id = co_hier.next_level_company_id
836 and viewby_dim.language = userenv(''LANG'')
837 and '||l_company_join;
838 ELSE -- no need to join to tl table
839 l_company_sql := 'SELECT co_hier.parent_company_id,co_hier.next_level_company_id, NULL, NULL
840 FROM fii_company_hierarchies co_hier
841 WHERE '||l_company_join;
842 END IF;
843
844 EXECUTE IMMEDIATE l_company_sql BULK COLLECT INTO company_table;
845
846 END form_specific_company_join;
847
848 /* below mentioned procedure forms join for cost center dimension, when cc parameter chosen is All.
849 It also inserts records into CC dimension PL/SQL table */
850
851 PROCEDURE form_all_cc_join(p_comp_agg_flag IN VARCHAR2, p_cc_agg_flag IN VARCHAR2, p_cc_id IN NUMBER) IS
852
853 l_cc_sql VARCHAR2(10000);
854 l_cc_join VARCHAR2(10000);
855 l_leaf_flag VARCHAR2(1);
856 l_cc_agg_flag VARCHAR2(1);
857
858
859 BEGIN
860
861 l_cc_agg_flag := p_cc_agg_flag;
862
863 IF g_view_by = 'ORGANIZATION+HRI_CL_ORGCC' THEN
864
865 IF g_cc_count = 1 THEN
866 SELECT for_viewby_flag INTO l_cc_agg_flag
867 FROM fii_cc_pmv_agrt_nodes
868 WHERE cost_center_id = p_cc_id;
869
870 check_if_leaf(p_cc_id);
871
872 IF g_cost_center_is_leaf = 'Y' THEN
873 IF g_if_trend_sum_mv = 'Y' OR (p_comp_agg_flag = 'Y' and l_cc_agg_flag = 'Y' and g_fin_aggregate_flag = 'Y' and g_ud1_aggregate_flag = 'Y') THEN
874 SELECT parent_cc_id INTO g_parent_cost_center_id
875 FROM fii_cost_ctr_hierarchies
876 WHERE child_cc_id = p_cc_id
877 and parent_level = child_level-1;
878 l_cc_join := 'parent_cc_id = '||g_parent_cost_center_id||'
879 and child_cc_id = '||p_cc_id;
880 ELSE
881 l_cc_join := 'parent_cc_id = '||p_cc_id||'
882 and child_level = parent_level';
883 END IF;
884 ELSE
885 l_cc_join := 'parent_cc_id = '||p_cc_id||'
886 and child_level = parent_level+1';
887 END IF;
888 ELSE -- we have > 1 record in cc security table FOR that user
889 IF g_if_trend_sum_mv = 'Y' OR (p_comp_agg_flag = 'Y' and l_cc_agg_flag = 'Y' and g_fin_aggregate_flag = 'Y' and g_ud1_aggregate_flag = 'Y') THEN
890 SELECT parent_cc_id INTO g_parent_cost_center_id
891 FROM fii_cost_ctr_hierarchies
892 WHERE child_cc_id = p_cc_id
893 and parent_level = child_level-1;
894 l_cc_join := 'parent_cc_id = '||g_parent_cost_center_id||'
895 and child_cc_id = '||p_cc_id;
896 ELSE
897 l_cc_join := 'parent_cc_id = '||p_cc_id||'
898 and child_cc_id = '||p_cc_id;
899 END IF;
900 END IF ; -- END of IF g_cc_count=1 IF LOOP
901 ELSE -- non viewby CC
902 IF g_if_trend_sum_mv = 'Y' OR (p_comp_agg_flag = 'Y' and l_cc_agg_flag = 'Y' and g_fin_aggregate_flag = 'Y' and g_ud1_aggregate_flag = 'Y') THEN
903 SELECT parent_cc_id INTO g_parent_cost_center_id
904 FROM fii_cost_ctr_hierarchies
905 WHERE child_cc_id = p_cc_id
906 and parent_level = child_level-1;
907 l_cc_join := 'parent_cc_id = '||g_parent_cost_center_id||'
908 and child_cc_id = '||p_cc_id;
909 ELSE
910 l_cc_join := 'parent_cc_id = '||p_cc_id||'
911 and child_cc_id = '||p_cc_id;
912 END IF;
913 END IF; -- END of IF viewby cc
914 IF g_view_by = 'ORGANIZATION+HRI_CL_ORGCC' THEN
915 l_cc_sql := 'SELECT cc_hier.parent_cc_id, cc_hier.next_level_cc_id, cc_hier.next_level_cc_sort_order, viewby_dim.description
916 FROM fii_cost_ctr_hierarchies cc_hier, fnd_flex_values_tl viewby_dim
917 WHERE viewby_dim.flex_value_id = cc_hier.next_level_cc_id
918 and viewby_dim.language = userenv(''LANG'')
919 and '||l_cc_join;
920 ELSE
921 l_cc_sql := 'SELECT cc_hier.parent_cc_id, cc_hier.next_level_cc_id, NULL, NULL
922 FROM fii_cost_ctr_hierarchies cc_hier
923 WHERE '||l_cc_join;
924 END IF;
925 -- fetch all records FOR CC in CC dimension's table of records
926 EXECUTE IMMEDIATE l_cc_sql BULK COLLECT INTO cc_table;
927
928 END form_all_cc_join;
929
930 /* below mentioned procedure forms join for cost center dimension, when any specific value is chosen for cc parameter.
931 It also inserts records into CC dimension PL/SQL table */
932
933 PROCEDURE form_specific_cc_join(p_comp_agg_flag IN VARCHAR2,p_cc_agg_flag IN VARCHAR2) IS
934
935 l_cc_sql VARCHAR2(10000);
936 l_cc_join VARCHAR2(10000);
937 l_leaf_flag VARCHAR2(1);
938
939 BEGIN
940
941 IF g_if_trend_sum_mv = 'Y' OR (p_comp_agg_flag = 'Y' and p_cc_agg_flag = 'Y' and g_fin_aggregate_flag = 'Y' and g_ud1_aggregate_flag = 'Y') THEN
942 IF g_view_by = 'ORGANIZATION+HRI_CL_ORGCC' THEN
943
944 check_if_leaf(g_cost_center_id);
945
946 IF g_cost_center_is_leaf = 'Y' THEN
947
948 SELECT parent_cc_id INTO g_parent_cost_center_id
949 FROM fii_cost_ctr_hierarchies
950 WHERE child_cc_id = g_cost_center_id
951 and parent_level = child_level-1;
952 l_cc_join := 'parent_cc_id = '||g_parent_cost_center_id||'
953 and child_cc_id = '||g_cost_center_id;
954 ELSE
955 l_cc_join := 'parent_cc_id = '||g_cost_center_id||'
956 and child_level = parent_level + 1';
957 END IF;
958 ELSE
959 SELECT parent_cc_id INTO g_parent_cost_center_id
960 FROM fii_cost_ctr_hierarchies
961 WHERE child_cc_id = g_cost_center_id
962 and parent_level = child_level-1;
963 l_cc_join := 'parent_cc_id = '||g_parent_cost_center_id||'
964 and child_cc_id = '||g_cost_center_id;
965 END IF;
966 ELSE
967 IF g_view_by = 'ORGANIZATION+HRI_CL_ORGCC' THEN
968
969 check_if_leaf(g_cost_center_id);
970
971 IF g_cost_center_is_leaf = 'Y' THEN
972 l_cc_join := 'parent_cc_id ='||g_cost_center_id||'
973 and child_cc_id ='||g_cost_center_id;
974 ELSE
975 l_cc_join := 'parent_cc_id ='||g_cost_center_id||'
976 and child_level = parent_level + 1';
977 END IF;
978 ELSE
979 l_cc_join := 'parent_cc_id ='||g_cost_center_id||'
980 and child_cc_id ='||g_cost_center_id;
981 END IF;
982 END IF;
983
984 IF g_view_by = 'ORGANIZATION+HRI_CL_ORGCC' THEN
985 l_cc_sql := 'SELECT cc_hier.parent_cc_id, cc_hier.next_level_cc_id, cc_hier.next_level_cc_sort_order, viewby_dim.description
986 FROM fii_cost_ctr_hierarchies cc_hier, fnd_flex_values_tl viewby_dim
987 WHERE viewby_dim.flex_value_id = cc_hier.next_level_cc_id
988 and viewby_dim.language = userenv(''LANG'')
989 and '||l_cc_join;
990 ELSE
991 l_cc_sql := 'SELECT cc_hier.parent_cc_id, cc_hier.next_level_cc_id, NULL, NULL
992 FROM fii_cost_ctr_hierarchies cc_hier
993 WHERE '||l_cc_join;
994 END IF;
995
996 EXECUTE IMMEDIATE l_cc_sql BULK COLLECT INTO cc_table;
997
998 END form_specific_cc_join;
999
1000 /* below mentioned procedure inserts records into financial category, UD1 and UD2 dimension PL/SQL tables, based on their joins formed in earlier steps */
1001
1002 PROCEDURE other_misc_stuff(p_comp_agg_flag IN VARCHAR2, p_cc_agg_flag IN VARCHAR2, p_aggrt_gt_is_empty OUT NOCOPY VARCHAR2, p_non_aggrt_gt_is_empty OUT NOCOPY VARCHAR2) IS
1003
1004 l_fin_cat_sql VARCHAR2(10000);
1005 l_fud1_sql VARCHAR2(10000);
1006 l_fud2_sql VARCHAR2(10000);
1007 l_company_join VARCHAR2(10000);
1008 l_leaf_flag VARCHAR2(1);
1009 l_comp_agg_flag VARCHAR2(1);
1010
1011 BEGIN
1012
1013 IF (g_if_trend_sum_mv = 'Y') OR (p_comp_agg_flag = 'Y' and p_cc_agg_flag = 'Y' and g_fin_aggregate_flag = 'Y' and g_ud1_aggregate_flag = 'Y') THEN
1014 l_cat_join := l_aggrt_cat_join;
1015 l_fud1_join := l_aggrt_fud1_join;
1016 ELSE -- non-aggrt MVs
1017 l_cat_join := l_nonaggrt_cat_join;
1018 l_fud1_join := l_nonaggrt_fud1_join;
1019 END IF;
1020
1021 /* In the following if-then-else block, for 'All' chosen and single fin cat top node scenarios or a specific
1022 fin cat node, GT tables will store the description of next-level fin_cat_id else for multiple top nodes,
1023 it will store the description of parent fin_cat_ids..done as part of bug 4099357*/
1024
1025 IF g_fin_cat_type IS NULL THEN
1026 l_fin_cat_sql := 'SELECT NULL, NULL, NULL, NULL FROM dual';
1027 ELSIF g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
1028 IF g_top_node_is_leaf = 'Y' THEN
1029 l_fin_cat_sql := 'SELECT fin_hier.parent_fin_cat_id, fin_hier.next_level_fin_cat_id, fin_hier.next_level_fin_cat_sort_order, viewby_dim.description
1030 FROM fii_fin_item_leaf_hiers fin_hier, fnd_flex_values_tl viewby_dim
1031 WHERE viewby_dim.flex_value_id = fin_hier.next_level_fin_cat_id
1032 and viewby_dim.language = userenv(''LANG'')
1033 and '||l_cat_join;
1034 ELSIF (g_fin_cat_top_node_count = 1 or g_fin_cat_top_node_count = 0) THEN
1035 l_fin_cat_sql := 'SELECT fin_hier.parent_fin_cat_id, fin_hier.next_level_fin_cat_id, fin_hier.next_level_fin_cat_sort_order, viewby_dim.description
1036 FROM fii_fin_item_leaf_hiers fin_hier, fnd_flex_values_tl viewby_dim
1037 WHERE viewby_dim.flex_value_id = fin_hier.next_level_fin_cat_id
1038 and viewby_dim.language = userenv(''LANG'')
1039 and '||l_cat_join;
1040 ELSE
1041 l_fin_cat_sql := 'SELECT fin_hier.parent_fin_cat_id, fin_hier.next_level_fin_cat_id, fin_hier.next_level_fin_cat_sort_order, viewby_dim.description
1042 FROM fii_fin_item_leaf_hiers fin_hier, fnd_flex_values_tl viewby_dim
1043 WHERE viewby_dim.flex_value_id = fin_hier.parent_fin_cat_id
1044 and viewby_dim.language = userenv(''LANG'')
1045 and '||l_cat_join;
1046 END IF;
1047 ELSE
1048 l_fin_cat_sql := 'SELECT fin_hier.parent_fin_cat_id, fin_hier.next_level_fin_cat_id, NULL,NULL
1049 FROM fii_fin_item_leaf_hiers fin_hier
1050 WHERE '||l_cat_join;
1051 END IF;
1052 -- fetch all records FOR Category in category's table of records
1053
1054 IF g_fin_cat_type IS NULL THEN
1055 EXECUTE IMMEDIATE l_fin_cat_sql BULK COLLECT INTO fin_cat_table;
1056 ELSIF g_fin_category_id = 'All' THEN
1057 EXECUTE IMMEDIATE l_fin_cat_sql BULK COLLECT INTO fin_cat_table using g_fin_cat_type;
1058 ELSE
1059 EXECUTE IMMEDIATE l_fin_cat_sql BULK COLLECT INTO fin_cat_table;
1060 END IF;
1061
1062 IF g_if_trend_sum_mv = 'Y' THEN
1063 l_fud1_sql := 'SELECT NULL, NULL, NULL, NULL FROM dual';
1064 ELSIF g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN
1065 l_fud1_sql := ' SELECT fud1_hier.parent_value_id, fud1_hier.next_level_value_id, fud1_hier.next_level_value_sort_order, viewby_dim.description
1066 FROM fii_udd1_hierarchies fud1_hier, fnd_flex_values_tl viewby_dim
1067 WHERE viewby_dim.flex_value_id = fud1_hier.next_level_value_id
1068 and viewby_dim.language = userenv(''LANG'')
1069 and '||l_fud1_join;
1070 ELSE
1071 l_fud1_sql := ' SELECT fud1_hier.parent_value_id, fud1_hier.next_level_value_id, NULL, null
1072 FROM fii_udd1_hierarchies fud1_hier
1073 WHERE '||l_fud1_join;
1074 END IF;
1075 -- fetch all records for FUD1 in fud1's table of records
1076 EXECUTE IMMEDIATE l_fud1_sql BULK COLLECT INTO fud1_table;
1077
1078 IF g_if_trend_sum_mv = 'Y' THEN
1079 l_fud2_sql := 'SELECT NULL, NULL, NULL, NULL FROM dual';
1080 ELSIF g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_2' THEN
1081 l_fud2_sql := ' SELECT fud2_hier.parent_value_id, fud2_hier.next_level_value_id, fud2_hier.next_level_value_sort_order, viewby_dim.description
1082 FROM fii_udd2_hierarchies fud2_hier, fnd_flex_values_tl viewby_dim
1083 WHERE viewby_dim.flex_value_id = fud2_hier.next_level_value_id
1084 and viewby_dim.language = userenv(''LANG'')
1085 and '||l_fud2_join;
1086 ELSE
1087 l_fud2_sql := ' SELECT fud2_hier.parent_value_id, fud2_hier.next_level_value_id, NULL, null
1088 FROM fii_udd2_hierarchies fud2_hier
1089 WHERE '||l_fud2_join;
1090 END IF;
1091 -- fetch all records for FUD2 in fud2's table of records
1092 EXECUTE IMMEDIATE l_fud2_sql BULK COLLECT INTO fud2_table;
1093
1094 IF (g_if_trend_sum_mv = 'Y') OR (p_comp_agg_flag = 'Y' and p_cc_agg_flag = 'Y' and g_fin_aggregate_flag = 'Y' and g_ud1_aggregate_flag = 'Y') THEN
1095 p_aggrt_gt_is_empty := 'N';
1096 p_non_aggrt_gt_is_empty := 'Y';
1097 insert_into_aggrt_gt;
1098 ELSE
1099 p_aggrt_gt_is_empty := 'Y';
1100 p_non_aggrt_gt_is_empty := 'N';
1101 insert_into_non_aggrt_gt;
1102
1103 END IF;
1104
1105 END other_misc_stuff;
1106
1107 PROCEDURE populate_security_gt_tables (p_aggrt_gt_is_empty OUT NOCOPY VARCHAR2,
1108 p_non_aggrt_gt_is_empty OUT NOCOPY VARCHAR2) IS
1109
1110 /* variable declaration section */
1111
1112 l_top_node VARCHAR2(1000);
1113 l_company_sql VARCHAR2(10000);
1114 l_cc_sql VARCHAR2(10000);
1115 l_fin_cat_sql VARCHAR2(10000);
1116 l_fud1_sql VARCHAR2(10000);
1117 l_fud2_sql VARCHAR2(10000);
1118 l_leaf_flag VARCHAR2(1) := 'N';
1119 l_company_aggregate_flag VARCHAR2(1);
1120 l_cc_aggregate_flag VARCHAR2(1);
1121 l_company_join VARCHAR2(10000);
1122 l_cc_join VARCHAR2(10000);
1123 l_viewbydim VARCHAR2(10000);
1124 l_fud1_enabled_flag VARCHAR2(1); -- flag to check if fud1 is enabled
1125 l_fud2_enabled_flag VARCHAR2(1);
1126 l_schema_name VARCHAR2(10);
1127 l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
1128
1129 -- definition of company cursor
1130
1131 CURSOR company_cursor IS
1132 SELECT sec.company_id company_id, sec.aggregated_flag agg_flag
1133 FROM fii_company_grants sec
1134 WHERE sec.user_id = fnd_global.user_id
1135 and report_region_code = g_region_code;
1136
1137 -- definition of cost center cursor
1138
1139 CURSOR cost_center_cursor IS
1140 SELECT sec.cost_center_id cc_id, sec.aggregated_flag agg_flag
1141 FROM fii_cost_center_grants sec
1142 WHERE sec.user_id = fnd_global.user_id
1143 and report_region_code = g_region_code;
1144
1145 BEGIN
1146
1147 l_schema_name := FII_UTIL.get_schema_name('FII');
1148 EXECUTE IMMEDIATE 'truncate table '||l_schema_name||'.fii_pmv_aggrt_gt';
1149 EXECUTE IMMEDIATE 'truncate table '||l_schema_name||'.fii_pmv_non_aggrt_gt';
1150
1151 /* We set this variable to yes, (whenever current procedure is invoked from EA page portlets) OR
1152 (view by <> UD1/UD2 AND param values for Fin category, UD1, UD2, ledger are 'All' */
1153
1154 IF g_region_code = 'FII_EA_PAGE' OR (g_view_by <> 'FII_USER_DEFINED+FII_USER_DEFINED_1' AND g_view_by <> 'FII_USER_DEFINED+FII_USER_DEFINED_2' AND g_fin_category_id = 'All' AND g_ledger_id = 'All' AND g_fud1_id = 'All' AND g_fud2_id = 'All') THEN
1155 g_if_trend_sum_mv := 'Y';
1156 END IF;
1157
1158 p_aggrt_gt_is_empty := 'Y';
1159 p_non_aggrt_gt_is_empty := 'Y';
1160
1161 -- Check whether fin category and UD1 parameter values are aggregated or not
1162
1163 IF g_fud1_id = 'All' THEN
1164 g_ud1_aggregate_flag := 'Y';
1165 ELSIF g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN
1166 SELECT for_viewby_flag INTO g_ud1_aggregate_flag
1167 FROM fii_udd1_pmv_agrt_nodes
1168 WHERE udd1_value_id=g_fud1_id;
1169 ELSE
1170 SELECT aggregated_flag INTO g_ud1_aggregate_flag FROM fii_udd1_pmv_agrt_nodes WHERE udd1_value_id=g_fud1_id;
1171 END IF;
1172
1173 IF g_fin_category_id = 'All' THEN
1174 g_fin_aggregate_flag := 'Y';
1175 ELSIF g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
1176 SELECT for_viewby_flag INTO g_fin_aggregate_flag FROM fii_fc_pmv_agrt_nodes WHERE fin_category_id=g_fin_category_id;
1177 ELSE
1178 SELECT aggregated_flag INTO g_fin_aggregate_flag FROM fii_fc_pmv_agrt_nodes WHERE fin_category_id=g_fin_category_id;
1179 END IF;
1180
1181 -- fin category join is being formed. Child_level = parent_level check has been
1182 --included to take care of loading of budget/forecast at summary nodes
1183
1184 IF g_fin_category_id = 'All' THEN -- this portion eliminates the need to use cursor for multiple top nodes for category
1185
1186 /* fix for bug 4127077. If we any top node which is also a leaf, then we pick up the
1187 records with OM as parent category and top node as child from fii_gl_agrt_sum_mv ELSE we pick up
1188 records with top nodes as parent categories and their next level children as child categories */
1189
1190 /* bug 4337351. For non-viewby fin category scenarios, while inserting records into fii_pmv_aggrt_gt table,
1191 we earlier picked up top node as parent fin category id and its next-level children as child fin category ids.
1192 This resulted in more NUMBER of records being inserted in gt tables which degraded performance. Instead, now,
1193 for non-viewby category cases, we pick up the combination of Operating Margin-Top node(s) which results in significant perf improvement */
1194
1195
1196 IF g_view_by <> 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
1197 l_aggrt_cat_join := 'EXISTS ( SELECT 1
1198 FROM fii_fin_cat_type_assgns a
1199 , fii_fin_item_leaf_hiers b
1200 WHERE a.FIN_CAT_TYPE_CODE = :g_fin_cat_type
1201 AND a.TOP_NODE_FLAG = ''Y''
1202 and a.fin_category_id = b.CHILD_FIN_CAT_ID
1203 and a.fin_category_id <> b.PARENT_FIN_CAT_ID
1204 AND a.fin_Category_id = fin_hier.child_fin_cat_id
1205 ) and (child_level <> parent_level AND child_level = parent_level+1)';
1206
1207 ELSIF g_top_node_is_leaf = 'N' THEN
1208 l_aggrt_cat_join := 'EXISTS ( SELECT 1
1209 FROM fii_fin_cat_type_assgns a
1210 , fii_fin_item_leaf_hiers b
1211 WHERE a.FIN_CAT_TYPE_CODE = :g_fin_cat_type
1212 AND a.TOP_NODE_FLAG = ''Y''
1213 and a.fin_category_id = b.CHILD_FIN_CAT_ID
1214 and a.fin_category_id <> b.PARENT_FIN_CAT_ID
1215 AND a.fin_Category_id = fin_hier.parent_fin_cat_id
1216 ) and (child_level = parent_level OR child_level = parent_level+1)';
1217 ELSE
1218 l_aggrt_cat_join := 'EXISTS ( SELECT 1
1219 FROM fii_fin_cat_type_assgns a
1220 , fii_fin_item_leaf_hiers b
1221 WHERE a.FIN_CAT_TYPE_CODE = :g_fin_cat_type
1222 AND a.TOP_NODE_FLAG = ''Y''
1223 and a.fin_category_id = b.CHILD_FIN_CAT_ID
1224 and a.fin_category_id <> b.PARENT_FIN_CAT_ID
1225 AND a.fin_Category_id = fin_hier.child_fin_cat_id
1226 ) and (child_level <> parent_level AND child_level = parent_level+1)';
1227 END IF;
1228
1229 /* the following if-then-else block picks up self records for top nodes as well as their
1230 next-level children's records, when we have only one fin cat top node else for multiple
1231 top nodes, it pick up only self records for top nodes..done as part of bug 4099357 */
1232
1233 IF g_if_trend_sum_mv = 'N' THEN
1234 IF g_fin_cat_top_node_count = 1 THEN
1235
1236 SELECT a.fin_category_id INTO g_category_id
1237 FROM fii_fin_cat_type_assgns a
1238 WHERE a.FIN_CAT_TYPE_CODE = g_fin_cat_type
1239 and a.TOP_NODE_FLAG = 'Y';
1240
1241 l_nonaggrt_cat_join := 'EXISTS ( SELECT 1
1242 FROM fii_fin_cat_type_assgns a
1243 , fii_fin_item_leaf_hiers b
1244 WHERE a.FIN_CAT_TYPE_CODE = :g_fin_cat_type
1245 AND a.TOP_NODE_FLAG = ''Y''
1246 and a.fin_category_id = b.CHILD_FIN_CAT_ID
1247 and a.fin_category_id <> b.PARENT_FIN_CAT_ID
1248 AND a.fin_Category_id = fin_hier.parent_fin_cat_id
1249 ) and (child_level = parent_level OR child_level = parent_level+1)';
1250 ELSE
1251 l_nonaggrt_cat_join := 'EXISTS ( SELECT 1
1252 FROM fii_fin_cat_type_assgns a
1253 , fii_fin_item_leaf_hiers b
1254 WHERE a.FIN_CAT_TYPE_CODE = :g_fin_cat_type
1255 AND a.TOP_NODE_FLAG = ''Y''
1256 and a.fin_category_id = b.CHILD_FIN_CAT_ID
1257 and a.fin_category_id <> b.PARENT_FIN_CAT_ID
1258 AND a.fin_Category_id = fin_hier.parent_fin_cat_id
1259 ) and child_level = parent_level';
1260 END IF;
1261 END IF;
1262 END IF;
1263
1264 /* Below mentioned joins need to be formed, only when we are not hitting fii_gl_trend_sum_mv -
1265
1266 1. When specific financial category has been chosen i.e. formation of l_aggrt_cat_join and g_non_aggrt_cat_join
1267 2. l_aggrt_fud1_join and l_nonaggrt_fud1_join
1268 3. l_fud2_join
1269
1270 */
1271
1272 IF g_if_trend_sum_mv = 'N' THEN
1273 IF g_fin_category_id <> 'All' THEN
1274 g_category_id := g_fin_category_id;
1275
1276 IF g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
1277 -- we need to consider next_level category nodes
1278
1279 check_if_leaf(g_fin_category_id);
1280
1281 IF g_fin_cat_is_leaf = 'Y' THEN
1282
1283 SELECT parent_fin_cat_id INTO g_parent_fin_category_id
1284 FROM fii_fin_item_leaf_hiers
1285 WHERE child_fin_cat_id = g_fin_category_id
1286 and parent_level=child_level-1;
1287 l_aggrt_cat_join := 'parent_fin_cat_id = '||g_parent_fin_category_id||'
1288 and child_fin_cat_id = '||g_fin_category_id;
1289 ELSE
1290 l_aggrt_cat_join := 'parent_fin_cat_id = '||g_fin_category_id||'
1291 and (child_level = parent_level OR child_level = parent_level+1)';
1292
1293 END IF;
1294 ELSE -- we can pick up the parent category directly
1295 SELECT parent_fin_cat_id INTO g_parent_fin_category_id
1296 FROM fii_fin_item_leaf_hiers
1297 WHERE child_fin_cat_id = g_fin_category_id
1298 and parent_level=child_level-1;
1299 l_aggrt_cat_join := 'parent_fin_cat_id = '||g_parent_fin_category_id||'
1300 and child_fin_cat_id = '||g_fin_category_id;
1301 END IF;
1302
1303 SELECT is_leaf_flag INTO l_leaf_flag
1304 FROM fii_fin_item_leaf_hiers
1305 WHERE parent_fin_cat_id=g_fin_category_id
1306 and parent_fin_cat_id = child_fin_cat_id;
1307
1308 IF l_leaf_flag = 'Y' THEN
1309 l_nonaggrt_cat_join := 'parent_fin_cat_id = '||g_fin_category_id||'
1310 and child_fin_cat_id = '||g_fin_category_id;
1311 ELSE
1312 l_nonaggrt_cat_join := 'parent_fin_cat_id = '||g_fin_category_id||'
1313 and (child_level = parent_level OR child_level = parent_level+1)';
1314 END IF;
1315 END IF;
1316
1317 -- fud1 join is being formed
1318 SELECT dbi_enabled_flag INTO l_fud1_enabled_flag
1319 FROM fii_financial_dimensions
1320 WHERE dimension_short_name = 'FII_USER_DEFINED_1';
1321
1322 IF l_fud1_enabled_flag = 'N' THEN
1323 SELECT parent_value_id INTO g_top_fud1_id
1324 FROM fii_udd1_hierarchies;
1325
1326 g_udd1_id := g_top_fud1_id;
1327
1328 l_aggrt_fud1_join := 'parent_value_id = '||g_top_fud1_id||' and
1329 child_value_id = '||g_top_fud1_id;
1330 l_nonaggrt_fud1_join := 'parent_value_id = '||g_top_fud1_id||' and
1331 child_value_id = '||g_top_fud1_id;
1332 ELSE
1333 -- we need to consider next_level fud1 nodes
1334 IF g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN
1335 IF g_fud1_id = 'All' THEN
1336 SELECT NVL(dbi_hier_top_node_id, -99999) INTO g_top_fud1_id
1337 FROM fii_financial_dimensions
1338 WHERE dimension_short_name = 'FII_USER_DEFINED_1';
1339
1340 g_udd1_id := g_top_fud1_id;
1341
1342 l_aggrt_fud1_join := 'parent_value_id = '||g_top_fud1_id||'
1343 and (child_level = parent_level OR child_level = parent_level+1)';
1344 ELSE
1345
1346 g_udd1_id := g_fud1_id;
1347
1348 check_if_leaf(g_fud1_id);
1349
1350 IF g_ud1_is_leaf = 'Y' THEN
1351
1352 SELECT parent_value_id INTO g_parent_fud1_id
1353 FROM fii_udd1_hierarchies
1354 WHERE child_value_id = g_fud1_id
1355 and parent_level = child_level-1;
1356 l_aggrt_fud1_join := 'parent_value_id = '||g_parent_fud1_id||'
1357 and child_value_id = '||g_fud1_id;
1358 ELSE
1359 l_aggrt_fud1_join := 'parent_value_id = '||g_fud1_id||'
1360 and (child_level = parent_level OR child_level = parent_level+1)';
1361 END IF;
1362 END IF;
1363 ELSE -- we can consider parent fud1 node directly
1364 IF g_fud1_id = 'All' THEN
1365 SELECT NVL(dbi_hier_top_node_id, -99999) INTO g_top_fud1_id
1366 FROM fii_financial_dimensions
1367 WHERE dimension_short_name = 'FII_USER_DEFINED_1';
1368
1369 g_udd1_id := g_top_fud1_id;
1370
1371 l_aggrt_fud1_join := 'parent_value_id = -999
1372 and child_value_id = '||g_top_fud1_id;
1373 ELSE
1374 g_udd1_id := g_fud1_id;
1375
1376 SELECT parent_value_id INTO g_parent_fud1_id
1377 FROM fii_udd1_hierarchies
1378 WHERE child_value_id = g_fud1_id
1379 and parent_level=child_level-1;
1380 l_aggrt_fud1_join := 'parent_value_id = '||g_parent_fud1_id||'
1381 and child_value_id = '||g_fud1_id;
1382 END IF ;
1383 END IF;
1384 END IF;
1385
1386
1387 -- l_nonaggrt_fud1_join is the join for fud1 WHEN non-aggrt tables are hit
1388 IF g_fud1_id = 'All' THEN
1389 IF g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN
1390 l_nonaggrt_fud1_join := 'parent_value_id = '||g_top_fud1_id||'
1391 and (child_level = parent_level OR child_level = parent_level+1)';
1392 ELSE
1393 l_nonaggrt_fud1_join := 'parent_value_id = '||g_top_fud1_id||'
1394 and child_value_id = '||g_top_fud1_id;
1395 END IF;
1396 ELSE
1397 IF g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN
1398 l_nonaggrt_fud1_join := 'parent_value_id = '||g_fud1_id||'
1399 and (child_level = parent_level OR child_level = parent_level+1)';
1400 ELSE
1401 l_nonaggrt_fud1_join := 'parent_value_id = '||g_fud1_id||'
1402 and child_value_id = '||g_fud1_id;
1403 END IF;
1404 END IF;
1405
1406 -- fud2 join is being formed
1407
1408 SELECT dbi_enabled_flag INTO l_fud2_enabled_flag
1409 FROM fii_financial_dimensions
1410 WHERE dimension_short_name = 'FII_USER_DEFINED_2';
1411 If l_fud2_enabled_flag = 'N' THEN
1412 SELECT parent_value_id INTO g_top_fud2_id
1413 FROM fii_udd2_hierarchies;
1414 l_fud2_join := 'parent_value_id = '||g_top_fud2_id||' and
1415 child_value_id = '||g_top_fud2_id;
1416 ELSE
1417 IF g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_2' THEN
1418 IF g_fud2_id = 'All' THEN
1419 SELECT NVL(dbi_hier_top_node_id, -99999) INTO g_top_fud2_id
1420 FROM fii_financial_dimensions
1421 WHERE dimension_short_name = 'FII_USER_DEFINED_2';
1422 l_fud2_join := 'parent_value_id = '||g_top_fud2_id||'
1423 and parent_level+1 = child_level';
1424 ELSE
1425 check_if_leaf(g_fud2_id);
1426
1427 IF g_ud2_is_leaf = 'Y' THEN
1428 l_fud2_join := 'parent_value_id = '||g_fud2_id||'
1429 and child_value_id = '||g_fud2_id;
1430 ELSE
1431 l_fud2_join := 'parent_value_id = '||g_fud2_id||'
1432 and parent_level+1 = child_level';
1433 END IF;
1434 END IF;
1435 ELSE
1436 IF g_fud2_id = 'All' THEN
1437 SELECT NVL(dbi_hier_top_node_id, -99999) INTO g_top_fud2_id
1438 FROM fii_financial_dimensions
1439 WHERE dimension_short_name = 'FII_USER_DEFINED_2';
1440 l_fud2_join := 'parent_value_id = '||g_top_fud2_id||'
1441 and child_value_id = '||g_top_fud2_id;
1442 ELSE
1443 l_fud2_join := 'parent_value_id = '||g_fud2_id||'
1444 and child_value_id = '||g_fud2_id;
1445 END IF ;
1446 END IF;
1447 END IF;
1448 END IF;
1449
1450 --Now that we have formed joins for category, fud1 and fud2, we come to case1..
1451
1452 --------------------1st case--- both company and CC param All ------------------------
1453 IF g_company_id = 'All' THEN
1454 SELECT count(*) INTO g_company_count
1455 FROM fii_company_grants
1456 WHERE user_id = fnd_global.user_id
1457 and report_region_code = g_region_code;
1458
1459 IF g_cost_center_id = 'All' THEN
1460 SELECT count(*) INTO g_cc_count
1461 FROM fii_cost_center_grants
1462 WHERE user_id = fnd_global.user_id
1463 and report_region_code = g_region_code;
1464
1465 FOR i in company_cursor LOOP
1466 FOR j in cost_center_cursor LOOP
1467
1468 -- call various procedures to populate PL/SQL tables for different dimensions
1469
1470 form_all_company_join(i.agg_flag, j.agg_flag, i.company_id);
1471 form_all_cc_join(i.agg_flag, j.agg_flag, j.cc_id);
1472 other_misc_stuff(i.agg_flag, j.agg_flag, p_aggrt_gt_is_empty, p_non_aggrt_gt_is_empty);
1473
1474 END LOOP;
1475 END LOOP;
1476 FORALL y in 1..g_aggrt_gt_record_count
1477 INSERT INTO fii_pmv_aggrt_gt VALUES ( par_comp_id_aggrt_plsql(y),comp_id_aggrt_plsql(y),par_cc_id_aggrt_plsql(y),
1478 cc_id_aggrt_plsql(y),par_fin_cat_id_aggrt_plsql(y),fin_cat_id_aggrt_plsql(y),
1479 par_fud1_id_aggrt_plsql(y),fud1_id_aggrt_plsql(y),fud2_id_aggrt_plsql(y),
1480 aggrt_viewbydescription(y), sort_order_aggrt_plsql(y));
1481 FORALL z in 1..g_non_aggrt_gt_record_count
1482 INSERT INTO fii_pmv_non_aggrt_gt VALUES (comp_id_nonaggrt_plsql(z),cc_id_nonaggrt_plsql(z),fin_cat_id_nonaggrt_plsql(z),
1483 fud1_id_nonaggrt_plsql(z),fud2_id_nonaggrt_plsql(z),nonaggrt_viewbydescription(z),sort_order_nonaggrt_plsql(z));
1484
1485 IF l_debug_mode = 'Y' THEN
1486 insert_into_debug_tables;
1487 END IF;
1488
1489 ELSE
1490 ---------------2nd case----- company - All and specific cost center chosen-------------
1491 FOR i in company_cursor LOOP
1492 IF g_view_by = 'ORGANIZATION+HRI_CL_ORGCC' THEN
1493 SELECT for_viewby_flag
1494 INTO l_cc_aggregate_flag
1495 FROM fii_cc_pmv_agrt_nodes
1496 WHERE cost_center_id=g_cost_center_id;
1497 ELSE
1498 SELECT aggregated_flag INTO l_cc_aggregate_flag FROM fii_cc_pmv_agrt_nodes id
1499 WHERE cost_center_id=g_cost_center_id;
1500 END IF;
1501
1502 -- call various procedures to populate PL/SQL tables for different dimensions
1503
1504 form_all_company_join(i.agg_flag, l_cc_aggregate_flag, i.company_id);
1505 form_specific_cc_join(i.agg_flag, l_cc_aggregate_flag);
1506 other_misc_stuff(i.agg_flag, l_cc_aggregate_flag, p_aggrt_gt_is_empty, p_non_aggrt_gt_is_empty);
1507
1508 END LOOP;
1509 FORALL y in 1..g_aggrt_gt_record_count
1510 INSERT INTO fii_pmv_aggrt_gt VALUES ( par_comp_id_aggrt_plsql(y),comp_id_aggrt_plsql(y),par_cc_id_aggrt_plsql(y),
1511 cc_id_aggrt_plsql(y),par_fin_cat_id_aggrt_plsql(y),fin_cat_id_aggrt_plsql(y),
1512 par_fud1_id_aggrt_plsql(y),fud1_id_aggrt_plsql(y),fud2_id_aggrt_plsql(y),
1513 aggrt_viewbydescription(y), sort_order_aggrt_plsql(y));
1514 FORALL z in 1..g_non_aggrt_gt_record_count
1515 INSERT INTO fii_pmv_non_aggrt_gt VALUES (comp_id_nonaggrt_plsql(z),cc_id_nonaggrt_plsql(z),fin_cat_id_nonaggrt_plsql(z),
1516 fud1_id_nonaggrt_plsql(z),fud2_id_nonaggrt_plsql(z),nonaggrt_viewbydescription(z),sort_order_nonaggrt_plsql(z));
1517
1518 IF l_debug_mode = 'Y' THEN
1519 insert_into_debug_tables;
1520 END IF;
1521 END IF;
1522 ELSE
1523 IF g_cost_center_id = 'All' THEN
1524 -------------------- 3rd case...------ specific company and Cost Center - All is chosen------------------------------
1525 SELECT count(*) INTO g_cc_count
1526 FROM fii_cost_center_grants
1527 WHERE user_id=fnd_global.user_id
1528 and report_region_code = g_region_code;
1529
1530 FOR j in cost_center_cursor LOOP
1531
1532 IF g_view_by = 'FII_COMPANIES+FII_COMPANIES' THEN
1533 SELECT for_viewby_flag
1534 INTO l_company_aggregate_flag
1535 FROM fii_com_pmv_agrt_nodes
1536 WHERE company_id=g_company_id;
1537 ELSE
1538 SELECT aggregated_flag INTO l_company_aggregate_flag
1539 FROM fii_com_pmv_agrt_nodes
1540 WHERE company_id=g_company_id;
1541 END IF;
1542
1543 -- call various procedures to populate PL/SQL tables for different dimensions
1544
1545 form_specific_company_join(l_company_aggregate_flag, j.agg_flag);
1546 form_all_cc_join(l_company_aggregate_flag, j.agg_flag, j.cc_id);
1547 other_misc_stuff(l_company_aggregate_flag, j.agg_flag, p_aggrt_gt_is_empty, p_non_aggrt_gt_is_empty);
1548
1549 END LOOP;
1550 FORALL y in 1..g_aggrt_gt_record_count
1551 INSERT INTO fii_pmv_aggrt_gt VALUES ( par_comp_id_aggrt_plsql(y),comp_id_aggrt_plsql(y),par_cc_id_aggrt_plsql(y),
1552 cc_id_aggrt_plsql(y),par_fin_cat_id_aggrt_plsql(y),fin_cat_id_aggrt_plsql(y),
1553 par_fud1_id_aggrt_plsql(y),fud1_id_aggrt_plsql(y),fud2_id_aggrt_plsql(y),
1554 aggrt_viewbydescription(y), sort_order_aggrt_plsql(y));
1555 FORALL i in 1..g_non_aggrt_gt_record_count
1556 INSERT INTO fii_pmv_non_aggrt_gt VALUES (comp_id_nonaggrt_plsql(i),cc_id_nonaggrt_plsql(i),fin_cat_id_nonaggrt_plsql(i),
1557 fud1_id_nonaggrt_plsql(i),fud2_id_nonaggrt_plsql(i),nonaggrt_viewbydescription(i),sort_order_nonaggrt_plsql(i));
1558
1559 IF l_debug_mode = 'Y' THEN
1560 insert_into_debug_tables;
1561 END IF;
1562 ELSE
1563 ---------------- 4th case..both param specific ---------------------------------------------------
1564
1565 IF g_view_by = 'FII_COMPANIES+FII_COMPANIES' THEN
1566 SELECT for_viewby_flag
1567 INTO l_company_aggregate_flag
1568 FROM fii_com_pmv_agrt_nodes
1569 WHERE company_id = g_company_id;
1570 ELSE
1571 SELECT aggregated_flag INTO l_company_aggregate_flag
1572 FROM fii_com_pmv_agrt_nodes
1573 WHERE company_id=g_company_id;
1574 END IF;
1575
1576 IF g_view_by = 'ORGANIZATION+HRI_CL_ORGCC' THEN
1577 SELECT for_viewby_flag
1578 INTO l_cc_aggregate_flag
1579 FROM fii_cc_pmv_agrt_nodes
1580 WHERE cost_center_id = g_cost_center_id;
1581 ELSE
1582 SELECT aggregated_flag INTO l_cc_aggregate_flag
1583 FROM fii_cc_pmv_agrt_nodes
1584 WHERE cost_center_id=g_cost_center_id;
1585 END IF;
1586
1587 -- call various procedures to populate PL/SQL tables for different dimensions
1588
1589 form_specific_company_join(l_company_aggregate_flag,l_cc_aggregate_flag);
1590 form_specific_cc_join(l_company_aggregate_flag, l_cc_aggregate_flag);
1591 other_misc_stuff(l_company_aggregate_flag, l_cc_aggregate_flag, p_aggrt_gt_is_empty, p_non_aggrt_gt_is_empty);
1592
1593 FORALL y in 1..g_aggrt_gt_record_count
1594 INSERT INTO fii_pmv_aggrt_gt VALUES ( par_comp_id_aggrt_plsql(y),comp_id_aggrt_plsql(y),par_cc_id_aggrt_plsql(y),
1595 cc_id_aggrt_plsql(y),par_fin_cat_id_aggrt_plsql(y),fin_cat_id_aggrt_plsql(y),
1596 par_fud1_id_aggrt_plsql(y),fud1_id_aggrt_plsql(y),fud2_id_aggrt_plsql(y),
1597 aggrt_viewbydescription(y), sort_order_aggrt_plsql(y));
1598 FORALL i in 1..g_non_aggrt_gt_record_count
1599 INSERT INTO fii_pmv_non_aggrt_gt VALUES (comp_id_nonaggrt_plsql(i),cc_id_nonaggrt_plsql(i),fin_cat_id_nonaggrt_plsql(i),
1600 fud1_id_nonaggrt_plsql(i),fud2_id_nonaggrt_plsql(i),nonaggrt_viewbydescription(i),sort_order_nonaggrt_plsql(i));
1601
1602 IF l_debug_mode = 'Y' THEN
1603 insert_into_debug_tables;
1604 END IF;
1605
1606 END IF;
1607 END IF;
1608
1609 EXCEPTION
1610 WHEN OTHERS THEN
1611 p_aggrt_gt_is_empty := 'Y';
1612 p_non_aggrt_gt_is_empty := 'Y';
1613
1614 END populate_security_gt_tables;
1615
1616 -----------------------------------------------------
1617
1618 PROCEDURE insert_into_debug_tables IS
1619 /* logic for this api...
1620 1. We first search for existence of debug table. If it doesn't exist, we create it else we delete the records inserted for the same session_id and region code.
1621 2. We then insert all columns of corresponding gt table + session_id and report_region_code into debug tables.
1622 */
1623
1624 l_agrt_table_count NUMBER := 0;
1625 l_non_agrt_table_count NUMBER := 0;
1626 l_schema_name VARCHAR2(10) := FII_UTIL.get_schema_name('FII');
1627
1628 BEGIN
1629 -- g_aggrt_gt_record_count and g_non_aggrt_gt_record_count are the number of records in fii_pmv_aggrt_gt and fii_pmv_non_aggrt_gt tables respectively
1630
1631 IF g_aggrt_gt_record_count > 0 THEN -- it means that fii_pmv_aggrt_gt has been populated so only now, we should insert records into fii_debug_agrt table
1632
1633 BEGIN
1634
1635 SELECT 1 INTO l_agrt_table_count
1636 FROM dba_tables
1637 WHERE table_name = 'FII_DEBUG_AGRT'
1638 and owner = l_schema_name;
1639
1640 EXCEPTION
1641 WHEN NO_DATA_FOUND THEN
1642 l_agrt_table_count := 0;
1643 END;
1644
1645 IF l_agrt_table_count = 0 THEN
1646
1647 EXECUTE IMMEDIATE 'CREATE TABLE '||l_schema_name||'.FII_DEBUG_AGRT (PARENT_COMPANY_ID NUMBER,
1648 COMPANY_ID NUMBER,
1649 PARENT_CC_ID NUMBER,
1650 CC_ID NUMBER,
1651 PARENT_FIN_CATEGORY_ID NUMBER,
1652 FIN_CATEGORY_ID NUMBER,
1653 PARENT_FUD1_ID NUMBER,
1654 FUD1_ID NUMBER,
1655 FUD2_ID NUMBER,
1656 VIEWBY VARCHAR2(100),
1657 SORT_ORDER NUMBER,
1658 SESSION_ID NUMBER,
1659 REGION_CODE VARCHAR2(50))';
1660 ELSE
1661 EXECUTE IMMEDIATE 'DELETE FROM '||l_schema_name||'.FII_DEBUG_AGRT WHERE REGION_CODE = '''||g_region_code||''' AND SESSION_ID = '||g_session_id;
1662
1663 END IF;
1664
1665 EXECUTE IMMEDIATE 'INSERT INTO '||l_schema_name||'.FII_DEBUG_AGRT (PARENT_COMPANY_ID,
1666 COMPANY_ID,
1667 PARENT_CC_ID,
1668 CC_ID,
1669 PARENT_FIN_CATEGORY_ID,
1670 FIN_CATEGORY_ID,
1671 PARENT_FUD1_ID,
1672 FUD1_ID,
1673 FUD2_ID,
1674 VIEWBY,
1675 SORT_ORDER,
1676 SESSION_ID,
1677 REGION_CODE)
1678
1679 SELECT gt.PARENT_COMPANY_ID,
1680 gt.COMPANY_ID,
1681 gt.PARENT_CC_ID,
1682 gt.CC_ID ,
1683 gt.PARENT_FIN_CATEGORY_ID,
1684 gt.FIN_CATEGORY_ID,
1685 gt.PARENT_FUD1_ID,
1686 gt.FUD1_ID,
1687 gt.FUD2_ID,
1688 gt.VIEWBY,
1689 gt.SORT_ORDER,
1690 '||g_session_id||','''||g_region_code||'''
1691 FROM fii_pmv_aggrt_gt gt';
1692 END IF;
1693
1694 IF g_non_aggrt_gt_record_count > 0 THEN -- it means that fii_pmv_non_aggrt_gt has been populated so only now, we should insert records into fii_debug_non_agrt table
1695
1696 BEGIN
1697
1698 SELECT 1 INTO l_non_agrt_table_count
1699 FROM dba_tables
1700 WHERE table_name = 'FII_DEBUG_NON_AGRT'
1701 and owner = l_schema_name;
1702
1703 EXCEPTION
1704 WHEN NO_DATA_FOUND THEN
1705 l_non_agrt_table_count := 0;
1706 END;
1707
1708 IF l_non_agrt_table_count = 0 THEN
1709
1710 EXECUTE IMMEDIATE 'CREATE TABLE '||l_schema_name||'.FII_DEBUG_NON_AGRT (
1711 COMPANY_ID NUMBER,
1712 COST_CENTER_ID NUMBER,
1713 FIN_CATEGORY_ID NUMBER,
1714 FUD1_ID NUMBER,
1715 FUD2_ID NUMBER,
1716 VIEWBY VARCHAR2(100),
1717 SORT_ORDER NUMBER,
1718 SESSION_ID NUMBER,
1719 REGION_CODE VARCHAR2(50))';
1720 ELSE
1721 EXECUTE IMMEDIATE 'DELETE FROM '||l_schema_name||'.FII_DEBUG_NON_AGRT WHERE REGION_CODE = '''||g_region_code||''' AND SESSION_ID = '||g_session_id;
1722 END IF;
1723 EXECUTE IMMEDIATE 'INSERT INTO '||l_schema_name||'.FII_DEBUG_NON_AGRT (COMPANY_ID,
1724 COST_CENTER_ID,
1725 FIN_CATEGORY_ID,
1726 FUD1_ID,
1727 FUD2_ID,
1728 VIEWBY,
1729 SORT_ORDER,
1730 SESSION_ID,
1731 REGION_CODE)
1732
1733 SELECT gt.company_id,
1734 gt.cost_center_id,
1735 gt.fin_category_id,
1736 gt.fud1_id,
1737 gt.fud2_id,
1738 gt.viewby,
1739 gt.sort_order,
1740 '||g_session_id||','''||g_region_code||'''
1741 FROM fii_pmv_non_aggrt_gt gt';
1742 END IF;
1743
1744 END insert_into_debug_tables;
1745
1746 ----------------------------------------------------------
1747
1748 FUNCTION period_label (p_as_of_date IN DATE) RETURN VARCHAR2 IS
1749 stmt VARCHAR2(240);
1750 BEGIN
1751 IF g_page_period_type = 'FII_TIME_ENT_YEAR' THEN
1752 SELECT name INTO stmt
1753 FROM fii_time_ent_year
1754 WHERE p_as_of_date between start_date and end_date;
1755 ELSIF g_page_period_type = 'FII_TIME_ENT_QTR' THEN
1756 SELECT name INTO stmt
1757 FROM fii_time_ent_qtr
1758 WHERE p_as_of_date between start_date and end_date;
1759 ELSIF g_page_period_type = 'FII_TIME_ENT_PERIOD' THEN
1760 SELECT name INTO stmt
1761 FROM fii_time_ent_period
1762 WHERE p_as_of_date between start_date and end_date;
1763 END IF;
1764 RETURN stmt;
1765 END period_label;
1766
1767
1768 FUNCTION curr_period_label RETURN VARCHAR2 IS
1769 stmt VARCHAR2(240);
1770 BEGIN
1771 stmt := period_label(g_as_of_date);
1772 return stmt;
1773 END curr_period_label;
1774
1775
1776 FUNCTION prior_period_label RETURN VARCHAR2 IS
1777 stmt VARCHAR2(240);
1778 BEGIN
1779 stmt := period_label(g_previous_asof_date);
1780 return stmt;
1781 END prior_period_label;
1782
1783 FUNCTION change_label RETURN VARCHAR2 IS
1784 stmt VARCHAR2(240);
1785 BEGIN
1786 -- IF fii_ea_util_pkg.g_time_comp = 'BUDGET' THEN
1787 -- stmt := fnd_message.get_string('FII', 'FII_GL_PCNT_BUDGET');
1788 -- ELSIF fii_ea_util_pkg.g_time_comp = 'FORECAST' THEN
1789 -- stmt := fnd_message.get_string('FII', 'FII_GL_PCNT_FORECAST');
1790 -- ELSE
1791 stmt := fnd_message.get_string('FII', 'FII_GL_CHANGE');
1792 --END IF;
1793
1794 return stmt;
1795
1796 END change_label;
1797
1798
1799 FUNCTION get_ledger_for_detail RETURN VARCHAR2 IS
1800 l_stmt VARCHAR2(500);
1801 BEGIN
1802 -- bug 4249974. Removed join, when ledger parameter is "All".
1803
1804 IF g_ledger_id <> 'All' THEN
1805 l_stmt := 'and f.ledger_id = &FII_LEDGER+FII_LEDGER ';
1806 END IF;
1807
1808 return l_stmt;
1809 END get_ledger_for_detail;
1810
1811 FUNCTION get_fud1_for_detail RETURN VARCHAR2 IS
1812 l_stmt VARCHAR2(200);
1813 l_enabled_flag VARCHAR2(1);
1814 BEGIN
1815 SELECT dbi_enabled_flag INTO l_enabled_flag
1816 FROM fii_financial_dimensions
1817 WHERE dimension_short_name = 'FII_USER_DEFINED_1';
1818
1819 IF l_enabled_flag = 'Y' THEN
1820
1821 IF g_fud1_id = 'All' THEN
1822 IF g_fud2_id = 'All' THEN
1823 l_stmt := ' ';
1824 ELSE
1825 l_stmt := ' and f.fud1_id in (SELECT child_value_id FROM fii_full_udd1_hiers WHERE parent_value_id = -999)';
1826 END IF;
1827 ELSE
1828 l_stmt := ' and f.fud1_id in (SELECT child_value_id FROM fii_full_udd1_hiers WHERE parent_value_id = &FII_USER_DEFINED+FII_USER_DEFINED_1)';
1829 END IF;
1830
1831 ELSE --FUD1 not enabled
1832 l_stmt := ' and f.fud1_id = :UNASSIGNED_ID';
1833 END IF;
1834
1835 return l_stmt;
1836 END get_fud1_for_detail;
1837
1838 FUNCTION get_fud2_for_detail RETURN VARCHAR2 IS
1839 l_stmt VARCHAR2(200);
1840 l_enabled_flag VARCHAR2(1);
1841 BEGIN
1842 SELECT dbi_enabled_flag INTO l_enabled_flag
1843 FROM fii_financial_dimensions
1844 WHERE dimension_short_name = 'FII_USER_DEFINED_2';
1845
1846 IF l_enabled_flag = 'Y' THEN
1847
1848 IF g_fud2_id = 'All' THEN
1849 l_stmt := ' ';
1850 ELSE
1851 l_stmt := ' and f.fud2_id in (SELECT child_value_id FROM fii_full_udd2_hiers WHERE parent_value_id = &FII_USER_DEFINED+FII_USER_DEFINED_2)';
1852 END IF;
1853
1854 ELSE --FUD2 not enabled.
1855 l_stmt := ' and f.fud2_id = :UNASSIGNED_ID';
1856 END IF;
1857
1858 return l_stmt;
1859 END get_fud2_for_detail;
1860
1861
1862 FUNCTION get_curr RETURN VARCHAR2 IS
1863
1864 stmt VARCHAR2(240);
1865
1866 BEGIN
1867
1868 stmt := 'FII_GLOBAL1';
1869
1870 RETURN stmt;
1871
1872 END get_curr;
1873
1874 FUNCTION xtd ( p_page_id IN VARCHAR2,
1875 p_user_id IN VARCHAR2,
1876 p_session_id IN VARCHAR2,
1877 p_function_name IN VARCHAR2
1878 ) RETURN VARCHAR2
1879 IS
1880
1881 stmt VARCHAR2(240);
1882
1883 BEGIN
1884
1885 stmt := BIS_PMV_PORTAL_UTIL_PUB.getTimeLevelLabel(p_page_id, p_user_id, p_session_id, p_function_name);
1886
1887 RETURN stmt;
1888
1889 END xtd;
1890
1891 FUNCTION prior_xtd( p_page_id IN VARCHAR2,
1892 p_user_id IN VARCHAR2,
1893 p_session_id IN VARCHAR2,
1894 p_function_name IN VARCHAR2) RETURN VARCHAR2
1895 IS
1896
1897 stmt VARCHAR2(240);
1898
1899 BEGIN
1900 stmt := fnd_message.get_string('FII', 'FII_GL_PMV')||' ' ||BIS_PMV_PORTAL_UTIL_PUB.getTimeLevelLabel(p_page_id, p_user_id, p_session_id, p_function_name);
1901
1902 RETURN stmt;
1903
1904 END prior_xtd;
1905
1906 FUNCTION prior_graph( p_page_id IN VARCHAR2,
1907 p_user_id IN VARCHAR2,
1908 p_session_id IN VARCHAR2,
1909 p_function_name IN VARCHAR2) RETURN VARCHAR2
1910 IS
1911
1912 stmt VARCHAR2(240);
1913
1914 BEGIN
1915
1916 IF fii_ea_util_pkg.g_time_comp = 'BUDGET' THEN
1917 stmt := fnd_message.get_string('FII', 'FII_GL_BUDGET');
1918 ELSIF fii_ea_util_pkg.g_time_comp = 'FORECAST' THEN
1919 stmt := fnd_message.get_string('FII', 'FII_GL_FORECAST');
1920 ELSIF fii_ea_util_pkg.g_region_code = 'FII_PL_GROSS_MARGIN_SUMM' OR fii_ea_util_pkg.g_region_code = 'FII_PL_OPER_MARGIN_SUMM' THEN
1921 stmt := fnd_message.get_string('FII', 'FII_PL_INCOME');
1922 ELSE
1923 stmt := fnd_message.get_string('FII', 'FII_GL_PMV')||' ' ||BIS_PMV_PORTAL_UTIL_PUB.getTimeLevelLabel(p_page_id, p_user_id, p_session_id, p_function_name);
1924 END IF;
1925
1926 RETURN stmt;
1927
1928 END prior_graph;
1929
1930
1931 FUNCTION get_rolling_period_label(p_sequence IN VARCHAR2) RETURN VARCHAR2 IS
1932
1933 stmt VARCHAR2(240);
1934 l_asof_date DATE := fii_ea_util_pkg.g_as_of_date;
1935 l_temp_date DATE := NULL;
1936
1937 BEGIN
1938
1939 IF fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN
1940
1941 CASE p_sequence
1942
1943 WHEN '1' THEN
1944 l_temp_date := fii_time_api.ent_pqtr_END(fii_time_api.ent_pqtr_END(fii_time_api.ent_pqtr_END(l_asof_date)));
1945 WHEN '2' THEN
1946 l_temp_date := fii_time_api.ent_pqtr_END(fii_time_api.ent_pqtr_END(l_asof_date));
1947 WHEN '3' THEN
1948 l_temp_date := fii_time_api.ent_pqtr_END(l_asof_date);
1949 WHEN '4' THEN
1950 stmt := FND_Message.get_string('FII', 'FII_QTD');
1951 RETURN stmt;
1952 ELSE
1953 RETURN NULL;
1954
1955 END CASE;
1956
1957 SELECT name INTO stmt
1958 FROM fii_time_ent_qtr
1959 WHERE l_temp_date = END_date;
1960
1961
1962
1963 ELSIF fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR' THEN
1964
1965 CASE p_sequence
1966
1967 WHEN '1' THEN
1968 l_temp_date := fii_time_api.ent_pper_END(fii_time_api.ent_pper_END(fii_time_api.ent_pper_END(l_asof_date)));
1969 WHEN '2' THEN
1970 l_temp_date := fii_time_api.ent_pper_END(fii_time_api.ent_pper_END(l_asof_date));
1971 WHEN '3' THEN
1972 l_temp_date := fii_time_api.ent_pper_END(l_asof_date);
1973 WHEN '4' THEN
1974 stmt := FND_Message.get_string('FII', 'FII_MTD');
1975 RETURN stmt;
1976 ELSE
1977 RETURN NULL;
1978
1979 END CASE;
1980
1981 SELECT name INTO stmt
1982 FROM fii_time_ent_period
1983 WHERE l_temp_date = END_date;
1984
1985 ELSE
1986 RETURN NULL;
1987
1988 END IF;
1989
1990 RETURN stmt;
1991
1992 END get_rolling_period_label;
1993
1994 FUNCTION get_com_name RETURN VARCHAR2
1995 IS
1996 l_industry VARCHAR2(10);
1997 l_company_name VARCHAR2(2000);
1998
1999 BEGIN
2000
2001 l_industry := fnd_profile.value('INDUSTRY');
2002
2003 IF l_industry = 'G' THEN
2004 l_company_name := fnd_message.get_string('FII','FII_DIM_FUND');
2005 ELSE
2006 l_company_name := fnd_message.get_string('FII','FII_DIM_COMPANY');
2007 END IF;
2008
2009 RETURN l_company_name;
2010
2011 END get_com_name;
2012
2013
2014 -- Added as part of bug 4099419..this procedure checks if category and fud1 parameter chosen are leaf nodes
2015
2016 PROCEDURE check_if_leaf(p_id IN NUMBER) IS
2017
2018 BEGIN
2019
2020 IF g_view_by = 'FII_COMPANIES+FII_COMPANIES' THEN
2021 SELECT is_leaf_flag INTO g_company_is_leaf
2022 FROM fii_company_hierarchies
2023 WHERE parent_company_id = p_id
2024 and parent_company_id = child_company_id;
2025
2026 ELSIF g_view_by = 'ORGANIZATION+HRI_CL_ORGCC' THEN
2027 SELECT is_leaf_flag INTO g_cost_center_is_leaf
2028 FROM fii_cost_ctr_hierarchies
2029 WHERE parent_cc_id = p_id
2030 and parent_cc_id = child_cc_id;
2031
2032 ELSIF g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
2033
2034 g_id := NVL(g_category_id,-9999);
2035
2036 IF g_fin_cat_top_node_count = 1 or g_fin_cat_top_node_count = 0 THEN
2037 SELECT is_leaf_flag INTO g_fin_cat_is_leaf
2038 FROM fii_fin_item_leaf_hiers
2039 WHERE parent_fin_cat_id = p_id
2040 and parent_fin_cat_id = child_fin_cat_id;
2041 END IF;
2042
2043 ELSIF g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN
2044
2045 g_id := NVL(g_udd1_id,-9999);
2046
2047 SELECT is_leaf_flag INTO g_ud1_is_leaf
2048 FROM fii_udd1_hierarchies
2049 WHERE parent_value_id = p_id
2050 and parent_value_id = child_value_id;
2051
2052 ELSIF g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_2' THEN
2053 SELECT is_leaf_flag INTO g_ud2_is_leaf
2054 FROM fii_udd1_hierarchies
2055 WHERE parent_value_id = p_id
2056 and parent_value_id = child_value_id;
2057 END IF;
2058
2059 EXCEPTION
2060 WHEN OTHERS THEN
2061 NULL;
2062
2063 END check_if_leaf;
2064
2065 PROCEDURE Bind_Variable (p_sqlstmt IN Varchar2,
2066 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
2067 p_sql_output OUT NOCOPY Varchar2,
2068 p_bind_output_table OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
2069
2070 l_bind_rec BIS_QUERY_ATTRIBUTES;
2071
2072 BEGIN
2073
2074 p_bind_output_table := BIS_QUERY_ATTRIBUTES_TBL();
2075 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
2076 p_sql_output := p_sqlstmt;
2077
2078 p_bind_output_table.EXTEND;
2079 l_bind_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
2080 l_bind_rec.attribute_value := g_view_by;
2081 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
2082 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2083
2084 p_bind_output_table.EXTEND;
2085 l_bind_rec.attribute_name := ':PREVIOUS_ASOF_DATE';
2086 l_bind_rec.attribute_value := to_char(g_previous_asof_date, 'DD/MM/YYYY');
2087 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2088 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2089 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2090
2091 p_bind_output_table.EXTEND;
2092 l_bind_rec.attribute_name := ':ASOF_DATE';
2093 l_bind_rec.attribute_value := to_char(g_as_of_date, 'DD/MM/YYYY');
2094 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2095 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2096 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2097
2098 /* Bug 4439400: Added ASOF_BUD_DATE and PREVIOUS_BUD_ASOF_DATE */
2099 p_bind_output_table.EXTEND;
2100 l_bind_rec.attribute_name := ':BUD_ASOF_DATE';
2101 l_bind_rec.attribute_value := to_char(g_bud_as_of_date, 'DD/MM/YYYY');
2102 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2103 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2104 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2105
2106 p_bind_output_table.EXTEND;
2107 l_bind_rec.attribute_name := ':PREVIOUS_BUD_ASOF_DATE';
2108 l_bind_rec.attribute_value := to_char(g_previous_bud_asof_date, 'DD/MM/YYYY');
2109 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2110 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2111 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2112
2113 p_bind_output_table.EXTEND;
2114 l_bind_rec.attribute_name := ':COMPANY_ID';
2115 l_bind_rec.attribute_value := to_char(g_company_id);
2116 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2117 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2118 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2119
2120 p_bind_output_table.EXTEND;
2121 l_bind_rec.attribute_name := ':PARENT_COMPANY_ID';
2122 l_bind_rec.attribute_value := to_char(g_parent_company_id);
2123 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2124 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2125 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2126
2127 p_bind_output_table.EXTEND;
2128 l_bind_rec.attribute_name := ':TOP_COMPANY_ID';
2129 l_bind_rec.attribute_value := to_char(g_top_company_id);
2130 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2131 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2132 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2133
2134 p_bind_output_table.EXTEND;
2135 l_bind_rec.attribute_name := ':COST_CENTER_ID';
2136 l_bind_rec.attribute_value := to_char(g_cost_center_id);
2137 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2138 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2139 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2140
2141 p_bind_output_table.EXTEND;
2142 l_bind_rec.attribute_name := ':PARENT_COST_CENTER_ID';
2143 l_bind_rec.attribute_value := to_char(g_parent_cost_center_id);
2144 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2145 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2146 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2147
2148 p_bind_output_table.EXTEND;
2149 l_bind_rec.attribute_name := ':TOP_COST_CENTER_ID';
2150 l_bind_rec.attribute_value := to_char(g_top_cost_center_id);
2151 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2152 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2153 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2154
2155 p_bind_output_table.EXTEND;
2156 l_bind_rec.attribute_name := ':FIN_CATEGORY_ID';
2157 l_bind_rec.attribute_value := to_char(g_fin_category_id);
2158 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2159 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2160 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2161
2162 p_bind_output_table.EXTEND;
2163 l_bind_rec.attribute_name := ':PARENT_FIN_CATEGORY_ID';
2164 l_bind_rec.attribute_value := to_char(g_parent_fin_category_id);
2165 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2166 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2167 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2168
2169 p_bind_output_table.EXTEND;
2170 l_bind_rec.attribute_name := ':FUD1_ID';
2171 l_bind_rec.attribute_value := to_char(g_fud1_id);
2172 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2173 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2174 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2175
2176 p_bind_output_table.EXTEND;
2177 l_bind_rec.attribute_name := ':PARENT_FUD1_ID';
2178 l_bind_rec.attribute_value := to_char(g_parent_fud1_id);
2179 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2180 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2181 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2182
2183 p_bind_output_table.EXTEND;
2184 l_bind_rec.attribute_name := ':TOP_FUD1_ID';
2185 l_bind_rec.attribute_value := to_char(g_top_fud1_id);
2186 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2187 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2188 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2189
2190 p_bind_output_table.EXTEND;
2191 l_bind_rec.attribute_name := ':FUD2_ID';
2192 l_bind_rec.attribute_value := to_char(g_fud2_id);
2193 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2194 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2195 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2196
2197 p_bind_output_table.EXTEND;
2198 l_bind_rec.attribute_name := ':PARENT_FUD2_ID';
2199 l_bind_rec.attribute_value := to_char(g_parent_fud2_id);
2200 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2201 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2202 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2203
2204 p_bind_output_table.EXTEND;
2205 l_bind_rec.attribute_name := ':TOP_FUD2_ID';
2206 l_bind_rec.attribute_value := to_char(g_top_fud2_id);
2207 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2208 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2209 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2210
2211 p_bind_output_table.EXTEND;
2212 l_bind_rec.attribute_name := ':LEDGER_ID';
2213 l_bind_rec.attribute_value := to_char(g_ledger_id);
2214 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2215 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2216 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2217
2218 p_bind_output_table.EXTEND;
2219 l_bind_rec.attribute_name := ':CURR_VIEW';
2220 l_bind_rec.attribute_value := to_char(g_curr_view);
2221 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2222 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2223 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2224
2225 p_bind_output_table.EXTEND;
2226 l_bind_rec.attribute_name := ':CURRENCY';
2227 l_bind_rec.attribute_value := to_char(g_currency);
2228 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2229 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2230 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2231
2232 p_bind_output_table.EXTEND;
2233 l_bind_rec.attribute_name := ':ACTUAL_BITAND';
2234 l_bind_rec.attribute_value := to_char(g_actual_bitand);
2235 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2236 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2237 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2238
2239 p_bind_output_table.EXTEND;
2240 l_bind_rec.attribute_name := ':HIST_ACTUAL_BITAND';
2241 l_bind_rec.attribute_value := to_char(g_hist_actual_bitand);
2242 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2243 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2244 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2245
2246 p_bind_output_table.EXTEND;
2247 l_bind_rec.attribute_name := ':BUDGET_BITAND';
2248 l_bind_rec.attribute_value := to_char(g_budget_bitand);
2249 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2250 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2251 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2252
2253 p_bind_output_table.EXTEND;
2254 l_bind_rec.attribute_name := ':HIST_BUDGET_BITAND';
2255 l_bind_rec.attribute_value := to_char(g_hist_budget_bitand);
2256 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2257 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2258 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2259
2260 p_bind_output_table.EXTEND;
2261 l_bind_rec.attribute_name := ':FORECAST_BITAND';
2262 l_bind_rec.attribute_value := to_char(g_forecast_bitand);
2263 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2264 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2265 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2266
2267 p_bind_output_table.EXTEND;
2268 l_bind_rec.attribute_name := ':PREVIOUS_ONE_END_DATE';
2269 l_bind_rec.attribute_value := to_char(g_previous_one_END_date, 'DD/MM/YYYY');
2270 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2271 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2272 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2273
2274 p_bind_output_table.EXTEND;
2275 l_bind_rec.attribute_name := ':PREVIOUS_TWO_END_DATE';
2276 l_bind_rec.attribute_value := to_char(g_previous_two_END_date, 'DD/MM/YYYY');
2277 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2278 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2279 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2280
2281 p_bind_output_table.EXTEND;
2282 l_bind_rec.attribute_name := ':PREVIOUS_THREE_END_DATE';
2283 l_bind_rec.attribute_value := to_char(g_previous_three_END_date, 'DD/MM/YYYY');
2284 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2285 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2286 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2287
2288 p_bind_output_table.EXTEND;
2289 l_bind_rec.attribute_name := ':YEAR_ID';
2290 l_bind_rec.attribute_value := to_char(g_year_id);
2291 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2292 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2293 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2294
2295 p_bind_output_table.EXTEND;
2296 l_bind_rec.attribute_name := ':PRIOR_YEAR_ID';
2297 l_bind_rec.attribute_value := to_char(g_prior_year_id);
2298 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2299 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2300 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2301
2302 p_bind_output_table.EXTEND;
2303 l_bind_rec.attribute_name := ':COAID';
2304 l_bind_rec.attribute_value := to_char(g_coaid);
2305 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2306 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2307 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2308
2309 p_bind_output_table.EXTEND;
2310 l_bind_rec.attribute_name := ':SOURCE_GROUP';
2311 l_bind_rec.attribute_value := to_char(g_je_source_group);
2312 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2313 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2314 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2315
2316 p_bind_output_table.EXTEND;
2317 l_bind_rec.attribute_name := ':PERIOD_SET_NAME';
2318 l_bind_rec.attribute_value := to_char(g_period_set_name);
2319 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2320 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2321 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2322
2323 p_bind_output_table.EXTEND;
2324 l_bind_rec.attribute_name := ':ACCOUNTED_PERIOD_TYPE';
2325 l_bind_rec.attribute_value := to_char(g_accounted_period_type);
2326 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2327 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2328 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2329
2330 p_bind_output_table.EXTEND;
2331 l_bind_rec.attribute_name := ':UNASSIGNED_ID';
2332 l_bind_rec.attribute_value := to_char(g_unassigned_id);
2333 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2334 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2335 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2336
2337 p_bind_output_table.EXTEND;
2338 l_bind_rec.attribute_name := ':CURR_PERIOD_START';
2339 l_bind_rec.attribute_value := to_char(g_curr_per_start, 'DD/MM/YYYY');
2340 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2341 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2342 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2343
2344 p_bind_output_table.EXTEND;
2345 l_bind_rec.attribute_name := ':CURR_PERIOD_END';
2346 l_bind_rec.attribute_value := to_char(g_curr_per_end, 'DD/MM/YYYY');
2347 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2348 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2349 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2350
2351 p_bind_output_table.EXTEND;
2352 l_bind_rec.attribute_name := ':PRIOR_PERIOD_START';
2353 l_bind_rec.attribute_value := to_char(g_prior_per_start, 'DD/MM/YYYY');
2354 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2355 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2356 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2357
2358 p_bind_output_table.EXTEND;
2359 l_bind_rec.attribute_name := ':PRIOR_PERIOD_END';
2360 l_bind_rec.attribute_value := to_char(g_prior_per_end, 'DD/MM/YYYY');
2361 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2362 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2363 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2364
2365 p_bind_output_table.EXTEND;
2366 l_bind_rec.attribute_name := ':CURR_MONTH_START';
2367 l_bind_rec.attribute_value := to_char(g_curr_month_start, 'DD/MM/YYYY');
2368 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2369 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2370 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2371
2372 p_bind_output_table.EXTEND;
2373 l_bind_rec.attribute_name := ':BOUNDARY_END';
2374 l_bind_rec.attribute_value := to_char(g_boundary_end, 'DD/MM/YYYY');
2375 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2376 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2377 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2378
2379 p_bind_output_table.EXTEND;
2380 l_bind_rec.attribute_name := ':PRIOR_BOUNDARY_END';
2381 l_bind_rec.attribute_value := to_char(g_prior_boundary_end, 'DD/MM/YYYY');
2382 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2383 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2384 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2385
2386 p_bind_output_table.EXTEND;
2387 l_bind_rec.attribute_name := ':AMOUNT_TYPE_BITAND';
2388 l_bind_rec.attribute_value := to_char(g_amount_type_bitand);
2389 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2390 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2391 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2392
2393 p_bind_output_table.EXTEND;
2394 l_bind_rec.attribute_name := ':DIR_MSG';
2395 l_bind_rec.attribute_value := to_char(g_dir_msg);
2396 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2397 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2398 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2399
2400 p_bind_output_table.EXTEND;
2401 l_bind_rec.attribute_name := ':SD_PRIOR';
2402 l_bind_rec.attribute_value := to_char(g_sd_prior, 'DD/MM/YYYY');
2403 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2404 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2405 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2406
2407 p_bind_output_table.EXTEND;
2408 l_bind_rec.attribute_name := ':SD_PRIOR_PRIOR';
2409 l_bind_rec.attribute_value := to_char(g_sd_prior_prior, 'DD/MM/YYYY');
2410 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2411 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2412 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2413
2414 p_bind_output_table.EXTEND;
2415 l_bind_rec.attribute_name := ':MIN_CAT_ID';
2416 l_bind_rec.attribute_value := to_char(g_min_cat_id);
2417 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2418 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2419 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2420
2421 p_bind_output_table.EXTEND;
2422 l_bind_rec.attribute_name := ':G_ID';
2423 l_bind_rec.attribute_value := to_char(g_id);
2424 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2425 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2426 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2427
2428 p_bind_output_table.EXTEND;
2429 l_bind_rec.attribute_name := ':TIME_ID';
2430 l_bind_rec.attribute_value := to_char(g_time_id);
2431 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2432 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2433 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2434
2435 p_bind_output_table.EXTEND;
2436 l_bind_rec.attribute_name := ':CATEGORY_ID';
2437 l_bind_rec.attribute_value := to_char(g_category_id);
2438 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2439 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2440 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2441
2442 p_bind_output_table.EXTEND;
2443 l_bind_rec.attribute_name := ':UDD1_ID';
2444 l_bind_rec.attribute_value := to_char(g_udd1_id);
2445 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2446 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2447 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2448
2449 p_bind_output_table.EXTEND;
2450 l_bind_rec.attribute_name := ':DISPLAY_SEQUENCE';
2451 l_bind_rec.attribute_value := to_char(g_display_sequence);
2452 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2453 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2454 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2455
2456
2457 p_bind_output_table.EXTEND;
2458 l_bind_rec.attribute_name := ':CURR_PERIOD_START_ID';
2459 l_bind_rec.attribute_value := to_char(g_curr_per_start_id);
2460 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2461 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2462 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2463
2464 p_bind_output_table.EXTEND;
2465 l_bind_rec.attribute_name := ':ASOF_DATE_ID';
2466 l_bind_rec.attribute_value := to_char(g_as_of_date_id);
2467 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2468 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2469 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2470
2471 -- Added for P&L Analysis
2472 p_bind_output_table.EXTEND;
2473 l_bind_rec.attribute_name := ':FIVE_YR_BACK';
2474 l_bind_rec.attribute_value := to_char(g_five_yr_back, 'DD/MM/YYYY');
2475 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2476 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2477 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2478
2479 p_bind_output_table.EXTEND;
2480 l_bind_rec.attribute_name := ':PY_SAME_DAY';
2481 l_bind_rec.attribute_value := to_char(g_py_sday, 'DD/MM/YYYY');
2482 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2483 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2484 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2485
2486 p_bind_output_table.EXTEND;
2487 l_bind_rec.attribute_name := ':P_EXP_ASOF';
2488 l_bind_rec.attribute_value := to_char(g_exp_asof_date, 'DD/MM/YYYY');
2489 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2490 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2491 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2492
2493
2494 p_bind_output_table.EXTEND;
2495 l_bind_rec.attribute_name := ':CY_PERIOD_END';
2496 l_bind_rec.attribute_value := to_char(g_cy_period_end, 'DD/MM/YYYY');
2497 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2498 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2499 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2500
2501 p_bind_output_table.EXTEND;
2502 l_bind_rec.attribute_name := ':ENT_PYR_END';
2503 l_bind_rec.attribute_value := to_char(g_ent_pyr_end, 'DD/MM/YYYY');
2504 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2505 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2506 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2507
2508 p_bind_output_table.EXTEND;
2509 l_bind_rec.attribute_name := ':ACTUAL_PERIOD_TYPE';
2510 l_bind_rec.attribute_value := to_char(g_actual_period_type);
2511 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2512 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2513 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2514
2515 p_bind_output_table.EXTEND;
2516 l_bind_rec.attribute_name := ':BUDGET_PERIOD_TYPE';
2517 l_bind_rec.attribute_value := to_char(g_budget_period_type);
2518 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2519 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2520 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2521
2522 p_bind_output_table.EXTEND;
2523 l_bind_rec.attribute_name := ':FORECAST_PERIOD_TYPE';
2524 l_bind_rec.attribute_value := to_char(g_forecast_period_type);
2525 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2526 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2527 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2528
2529 p_bind_output_table.EXTEND;
2530 l_bind_rec.attribute_name := ':WHERE_PERIOD_TYPE';
2531 l_bind_rec.attribute_value := to_char(g_where_period_type);
2532 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2533 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2534 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2535
2536 p_bind_output_table.EXTEND;
2537 l_bind_rec.attribute_name := ':ENT_CYR_END';
2538 l_bind_rec.attribute_value := to_char(g_ent_cyr_end, 'DD/MM/YYYY');
2539 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2540 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2541 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2542
2543 p_bind_output_table.EXTEND;
2544 l_bind_rec.attribute_name := ':CURR_EFFECTIVE_SEQ';
2545 l_bind_rec.attribute_value := to_char(g_curr_per_sequence);
2546 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2547 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2548 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2549
2550 p_bind_output_table.EXTEND;
2551 l_bind_rec.attribute_name := ':PERIOD_TYPE';
2552 l_bind_rec.attribute_value := to_char(g_period_type);
2553 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2554 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2555 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2556
2557 p_bind_output_table.EXTEND;
2558 l_bind_rec.attribute_name := ':P_EXP_START';
2559 l_bind_rec.attribute_value := to_char(g_exp_start, 'DD/MM/YYYY');
2560 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2561 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2562 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2563
2564 p_bind_output_table.EXTEND;
2565 l_bind_rec.attribute_name := ':P_EXP_BEGIN';
2566 l_bind_rec.attribute_value := to_char(g_exp_begin_date, 'DD/MM/YYYY');
2567 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2568 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2569 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2570
2571 p_bind_output_table.EXTEND;
2572 l_bind_rec.attribute_name := ':FIN_TYPE';
2573 l_bind_rec.attribute_value := to_char(g_fin_type);
2574 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2575 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2576 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
2577
2578 END bind_variable;
2579
2580 END fii_ea_util_pkg;