[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_SUTIL_PKG
Source
1 PACKAGE BODY POA_DBI_SUTIL_PKG AS
2 /* $Header: poadbisutilb.pls 120.22 2006/08/27 19:15:11 sriswami noship $ */
3
4 PROCEDURE populate_mv_bmap(p_mv_bmap_tbl out NOCOPY poa_dbi_mv_bmap_tbl, p_mv_set in varchar2) ;
5 PROCEDURE add_dimension(p_dim IN VARCHAR2,
6 p_func_area IN VARCHAR2,
7 p_version IN VARCHAR2,
8 p_role IN VARCHAR2,
9 p_mv_set IN VARCHAR2,
10 p_generate_where_clause IN VARCHAR2,
11 p_dim_map IN OUT NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map);
12
13 FUNCTION get_bmap(p_dim IN VARCHAR2) RETURN NUMBER;
14
15 PROCEDURE get_binds(
16 p_trend IN VARCHAR2,
17 p_mv_set IN VARCHAR2,
18 p_xtd IN VARCHAR2,
19 p_comparison_type IN VARCHAR2,
20 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL,
21 p_context_code IN VARCHAR2,
22 p_param IN BIS_PMV_PAGE_PARAMETER_TBL
23 ) ;
24 function get_msg (p_current in varchar2)return varchar2;
25
26 PROCEDURE bind_reqfact_date(
27 p_custom_output IN OUT NOCOPY bis_query_attributes_tbl)
28 is
29 l_last_refresh_date date;
30 l_custom_rec BIS_QUERY_ATTRIBUTES;
31 begin
32
33 IF p_custom_output is null THEN
34 p_custom_output := bis_query_attributes_tbl();
35 END IF;
36
37 l_last_refresh_date := fnd_date.displaydt_to_date(
38 bis_collection_utilities.get_last_refresh_period('POAREQLN'));
39
40 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
41 l_custom_rec.attribute_name := '&REQ_FACT_UPDATE_DATE';
42 l_custom_rec.attribute_value := to_char(l_last_refresh_date,'DD/MM/YYYY HH24:MI:SS');
43 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
44 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
45 p_custom_output.extend;
46 p_custom_output(p_custom_output.count) := l_custom_rec;
47
48 END bind_reqfact_date;
49
50 FUNCTION get_filter_where(p_cols in POA_DBI_FILTER_TBL)
51 return VARCHAR2 IS
52 l_where VARCHAR2(1000);
53
54 BEGIN
55 l_where := 'coalesce(';
56 for i in 1..p_cols.COUNT LOOP
57 if(i <> 1) then
58 l_where := l_where || ',';
59 end if;
60 l_where := l_where || '
61 decode(' || p_cols(i) || ',0,null,' || p_cols(i) || ')';
62 END LOOP;
63
64 l_where := l_where || ' ) is not null ';
65
66 return l_where;
67 END;
68
69 PROCEDURE process_parameters(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
70 p_view_by out NOCOPY VARCHAR2,
71 p_view_by_col_name OUT NOCOPY VARCHAR2,
72 p_view_by_value OUT NOCOPY VARCHAR2,
73 p_comparison_type out NOCOPY VARCHAR2,
74 p_xtd out NOCOPY VARCHAR2,
75 p_as_of_date out NOCOPY DATE,
76 p_prev_as_of_date out NOCOPY DATE,
77 p_cur_suffix out NOCOPY VARCHAR2,
78 p_nested_pattern out NOCOPY NUMBER,
79 p_where_clause out NOCOPY VARCHAR2,
80 p_mv out NOCOPY VARCHAR2,
81 p_join_tbl out NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl,
82 p_in_join_tbl out NOCOPY poa_dbi_util_pkg.poa_dbi_in_join_tbl,
83 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL,
84 p_trend in VARCHAR2,
85 p_func_area IN VARCHAR2,
86 p_version IN VARCHAR2,
87 p_role IN VARCHAR2,
88 p_mv_set IN VARCHAR2)
89 IS
90 l_dim_map poa_dbi_util_pkg.poa_dbi_dim_map;
91 l_dim_bmap NUMBER;
92 l_commodity_value varchar2(10) := null;
93 l_region_code varchar2(30) := null;
94 l_company_value varchar2(30) := 'All';
95 l_cost_center_value varchar2(30) := 'All';
96 l_aggregation_level NUMBER;
97 l_context_code VARCHAR2(10) := 'OU/COM';
98
99 l_and_agg_col VARCHAR2(50);
100 BEGIN
101 l_dim_bmap :=0;
102 l_context_code := get_sec_context(p_param);
103 init_dim_map(l_dim_map, p_func_area, p_version, p_role, p_mv_set);
104
105 poa_dbi_util_pkg.get_parameter_values(p_param, l_dim_map,p_view_by, p_comparison_type, p_xtd, p_as_of_date, p_prev_as_of_date, p_cur_suffix, p_nested_pattern,l_dim_bmap );
106
107
108 --- Begin Spend Analysis Trend change
109 IF(p_mv_set='FIIIV' OR p_mv_set='FIIPA')
110 THEN
111 IF(p_cur_suffix = 'g')
112 THEN p_cur_suffix := 'prim_g' ;
113 ELSIF(p_cur_suffix = 'sg')
114 THEN p_cur_suffix := 'sec_g';
115 END IF ;
116 END IF ; --p_mv_set
117 ---ENd Spend Analysis Trend change
118
119 /* add in the security dimensions that must always be present in bmap */
120 IF(l_context_code = 'SUPPLIER') THEN
121 l_dim_bmap := poa_dbi_util_pkg.bitor(l_dim_bmap,SUPPLIER_BMAP);
122 ELSIF(l_context_code = 'COMP') THEN
123 l_dim_bmap := poa_dbi_util_pkg.bitor(l_dim_bmap,COMPANY_BMAP);
124 l_dim_bmap := poa_dbi_util_pkg.bitor(l_dim_bmap,COSTCTR_BMAP);
125 ELSIF(l_context_code = 'OU/COM' OR l_context_code = 'OU' OR l_context_code = 'NEG' OR l_context_code = 'OUX' ) THEN
126 l_dim_bmap := poa_dbi_util_pkg.bitor(l_dim_bmap, OPER_UNIT_BMAP);
127 /* if the role is commodity manager then commodity should be added to bmap*/
128 if (((p_role = 'COM') AND (l_context_code = 'OU/COM'))
129 OR (l_context_code = 'NEG')) then
130 l_dim_bmap := poa_dbi_util_pkg.bitor(l_dim_bmap, COMMODITY_BMAP);
131 end if;
132 END IF;
133
134 if(p_mv_set='POD' or p_mv_set='PODCUT') then
135 p_mv := 'POA_POD_BS_MV';
136 elsif(p_mv_set='PODA' or p_mv_set='PODCUTA') then /*pod aggregated mv*/
137 p_mv := 'POA_POD_002_MV';
138 elsif(p_mv_set='PODB' or p_mv_set='PODCUTB') then /*pod base mv*/
139 p_mv := 'POA_POD_001_MV';
140 elsif(p_mv_set='API') then
141 p_mv := 'POA_API_BS_MV';
142 elsif(p_mv_set='APIA') then /*api aggregated mv*/
143 p_mv := 'POA_API_002_MV';
144 elsif(p_mv_set='APIB') then /*api base mv*/
145 p_mv := 'POA_API_001_MV';
146 elsif(p_mv_set='PQC') then
147 p_mv := 'POA_PQC_BS_MV';
148 elsif(p_mv_set='PQCA') then /*pqc aggregated mv*/
149 p_mv := 'POA_PQC_002_MV';
150 elsif(p_mv_set='PQCB') then /*pqc base mv*/
151 p_mv := 'POA_PQC_001_MV';
152 elsif(p_mv_set = 'RTX') then
153 p_mv := 'POA_RTX_BS_MV';
154 elsif(p_mv_set = 'IDL') then
155 p_mv := 'POA_IDL_BS_MV';
156 elsif(p_mv_set='IDLA') then /*idl aggregated mv*/
157 p_mv := 'POA_IDL_002_MV';
158 elsif(p_mv_set='IDLB') then /*idl base mv*/
159 p_mv := 'POA_IDL_001_MV';
160 elsif(p_mv_set = 'MID') then
161 p_mv := 'POA_MID_BS_MV';
162 elsif(p_mv_set = 'REQMP') then
163 p_mv := 'POA_REQ_001_MV';
164 elsif(p_mv_set = 'REQMF') then
165 p_mv := 'POA_REQ_002_MV';
166 elsif(p_mv_set = 'REQS') then
167 p_mv := 'POA_REQ_000_MV';
168 --- Begin Spend Analysis Trend change
169 elsif(p_mv_set='FIIIV')
170 then p_mv := 'FII_AP_IVATY_XB_MV';
171 elsif(p_mv_set='FIIPA')
172 then p_mv := 'FII_AP_PAID_XB_MV';
173 --- End Spend Analysis Trend change
174 --- Begin Sourcing Management Change
175 elsif(p_mv_set = 'NEG') then
176 p_mv := 'POA_NEG_001_MV';
177 --- End Sourcing Management Change
178 end if;
179
180 l_aggregation_level := get_agg_level(l_dim_bmap, p_mv_set);
181
182 if(l_dim_map.exists(p_view_by)) then
183 p_view_by_col_name := l_dim_map(p_view_by).col_name;
184 p_view_by_value := l_dim_map(p_view_by).value;
185 end if;
186
187 if(p_mv_set = 'REQS') then
188 p_where_clause := '2=2';
189 else
190 p_where_clause := '';
191 end if;
192
193 --- Begin Spend Analysis Trend change
194 IF p_mv_set = 'FIIIV' OR p_mv_set = 'FIIPA'
195 THEN
196 l_and_agg_col := ' and fact.gid=' ;
197 ELSE
198 l_and_agg_col := ' and fact.aggregation_level=' ;
199 END IF ;
200 --- End Spend Analysis Trend change
201
202 p_where_clause := p_where_clause
203 || l_and_agg_col
204 || l_aggregation_level || ' '
205 || poa_dbi_util_pkg.get_where_clauses(l_dim_map, p_trend)
206 || get_in_security_where_clauses(
207 l_dim_map
208 , l_context_code
209 , p_func_area
210 , p_version
211 , p_role
212 , p_trend);
213
214 get_join_info(p_view_by, l_dim_map,p_join_tbl, p_func_area, p_version);
215
216 populate_in_join_tbl(p_in_join_tbl, p_param, l_dim_map, l_context_code, p_version, p_mv_set, p_where_clause);
217
218 get_binds(p_trend,p_mv_set,p_xtd,p_comparison_type,x_custom_output, l_context_code, p_param);
219
220 END process_parameters;
221
222 PROCEDURE drill_process_parameters(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
223 p_cur_suffix out NOCOPY VARCHAR2,
224 p_where_clause out NOCOPY VARCHAR2,
225 p_in_join_tbl out NOCOPY poa_dbi_util_pkg.poa_dbi_in_join_tbl,
226 p_func_area IN VaRCHAR2,
227 p_version IN VARCHAR2,
228 p_role IN VARCHAR2,
229 p_mv_set IN VARCHAR2)
230 IS
231 l_dim_map poa_dbi_util_pkg.poa_dbi_dim_map;
232 l_context_code VARCHAR2(10) := 'OU/COM';
233 l_where_clause_unused varchar2(500);
234 BEGIN
235
236 l_context_code := get_sec_context(p_param);
237
238 init_dim_map(l_dim_map, p_func_area, p_version, p_role, p_mv_set);
239
240 poa_dbi_util_pkg.get_drill_param_values(p_param, l_dim_map, p_cur_suffix);
241
242 p_where_clause :=poa_dbi_util_pkg.get_where_clauses(l_dim_map, 'N')
243 || get_in_security_where_clauses(l_dim_map, l_context_code , p_func_area, p_version, p_role, 'N');
244
245 /* note that we are passing a dummy to the p_where_clause parameter. The drill report is
246 * expected to provide its own security filters*/
247 populate_in_join_tbl(p_in_join_tbl, p_param, l_dim_map, l_context_code, p_version, p_mv_set, l_where_clause_unused);
248
249 END;
250
251 /* OBSOLETE-- No new code should call this. Use
252 get_in_security_where_clauses instead */
253 FUNCTION get_security_where_clauses(p_dim_map poa_dbi_util_pkg.poa_dbi_dim_map,
254 p_func_area in VARCHAR2,
255 p_version in VARCHAR2,
256 p_role in VARCHAR2,
257 p_trend in VARCHAR2 := 'N') return VARCHAR2 IS
258 l_sec_where_clause VARCHAR2(1000):='';
259 l_commod_where VARCHAR2(1000);
260 l_ou_where VARCHAR2(1000);
261 BEGIN
262
263 l_ou_where := poa_dbi_util_pkg.get_ou_sec_where(
264 p_dim_map('ORGANIZATION+FII_OPERATING_UNITS').value,
265 p_dim_map('ORGANIZATION+FII_OPERATING_UNITS').col_name,
266 p_trend);
267 if(l_ou_where is not null) then
268 l_sec_where_clause := l_sec_where_clause || ' and ' || l_ou_where;
269 end if;
270
271 if(p_version = '6.0') then
272 l_commod_where := poa_dbi_util_pkg.get_commodity_sec_where(
273 p_dim_map('ITEM+POA_COMMODITIES').value,
274 p_trend);
275 if(l_commod_where is not null ) then
276 l_sec_where_clause := l_sec_where_clause || ' and ' || l_commod_where;
277 end if;
278 end if;
279
280 return l_sec_where_clause;
281 END;
282
283 FUNCTION get_in_security_where_clauses(p_dim_map poa_dbi_util_pkg.poa_dbi_dim_map,
284 p_context_code in VARCHAR2,
285 p_func_area in VARCHAR2,
286 p_version in VARCHAR2,
287 p_role in VARCHAR2,
288 p_trend in VARCHAR2 := 'N') return VARCHAR2 IS
289 l_in_sec_where_clause VARCHAR2(1000) :='';
290 l_commod_where VARCHAR2(1000) := '';
291 l_sup_where VARCHAR2(1000) := '';
292 l_ou_where VARCHAR2(1000) := '';
293 BEGIN
294 IF(p_context_code = 'OU/COM' or p_context_code ='OU' or p_context_code = 'NEG'
295 OR p_context_code = 'OUX' ) THEN
296 l_ou_where := poa_dbi_util_pkg.get_in_ou_sec_where(
297 p_dim_map('ORGANIZATION+FII_OPERATING_UNITS').value,
298 p_dim_map('ORGANIZATION+FII_OPERATING_UNITS').col_name,
299 p_use_bind => 'N');
300 if(l_ou_where is not null) then
301 l_in_sec_where_clause := l_ou_where;
302 end if;
303 IF(p_context_code = 'OU/COM' or p_context_code = 'NEG') THEN
304 if(p_version = '6.0' or p_version = '7.0' or p_version = '8.0') then
305 l_commod_where := poa_dbi_util_pkg.get_in_commodity_sec_where(
306 p_dim_map('ITEM+POA_COMMODITIES').value,
307 p_trend);
308 if(l_commod_where is not null ) then
309 l_in_sec_where_clause := l_in_sec_where_clause || l_commod_where;
310 end if;
311 end if;
312 END IF;
313 ELSIF(p_context_code='SUPPLIER') THEN
314 l_sup_where := poa_dbi_util_pkg.get_in_supplier_sec_where(p_dim_map('SUPPLIER+POA_SUPPLIERS').value);
315 IF(l_sup_where IS NOT NULL) THEN
316 l_in_sec_where_clause := l_in_sec_where_clause || l_sup_where;
317 END IF;
318 END IF;
319 return l_in_sec_where_clause;
320 END;
321
322
323 PROCEDURE add_dimension(p_dim IN VARCHAR2,
324 p_func_area IN VARCHAR2,
325 p_version IN VARCHAR2,
326 p_role IN VARCHAR2,
327 p_mv_set IN VARCHAR2,
328 p_generate_where_clause IN VARCHAR2,
329 p_dim_map IN OUT NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map) IS
330
331 l_dim_rec poa_dbi_util_pkg.poa_dbi_dim_rec;
332
333 BEGIN
334 l_dim_rec.col_name := get_col_name(p_dim,p_func_area, p_version, p_mv_set);
335 l_dim_rec.view_by_table := get_table(p_dim, p_func_area, p_version);
336 l_dim_rec.generate_where_clause := p_generate_where_clause;
337 l_dim_rec.bmap := get_bmap(p_dim);
338 p_dim_map(p_dim) := l_dim_rec;
339 END;
340
341
342 PROCEDURE init_dim_map(p_dim_map out NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map,
343 p_func_area IN VARCHAR2,
344 p_version IN VARCHAR2,
345 p_role IN VARCHAR2,
346 p_mv_set IN VARCHAR2) IS
347
348 l_dim_rec poa_dbi_util_pkg.poa_dbi_dim_rec;
349
350 BEGIN
351
352 add_dimension('HRI_PERSON+HRI_PER',
353 p_func_area,
354 p_version,
355 p_role,
356 p_mv_set,
357 p_generate_where_clause => 'Y',
358 p_dim_map => p_dim_map);
359 add_dimension('SUPPLIER+POA_SUPPLIERS',
360 p_func_area,
361 p_version,
362 p_role,
363 p_mv_set,
364 p_generate_where_clause => 'Y',
365 p_dim_map => p_dim_map);
366 add_dimension('SUPPLIER+POA_SUPPLIER_SITES',
367 p_func_area,
368 p_version,
369 p_role,
370 p_mv_set,
371 p_generate_where_clause => 'Y',
372 p_dim_map => p_dim_map);
373 add_dimension('ORGANIZATION+FII_OPERATING_UNITS',
374 p_func_area,
375 p_version,
376 p_role,
377 p_mv_set,
378 p_generate_where_clause => 'Y',
379 p_dim_map => p_dim_map);
380 add_dimension('ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION',
381 p_func_area,
382 p_version,
383 p_role,
384 p_mv_set,
385 p_generate_where_clause => 'Y',
386 p_dim_map => p_dim_map);
387 add_dimension('LOOKUP+RETURN_REASON',
388 p_func_area,
389 p_version,
390 p_role,
391 p_mv_set,
392 p_generate_where_clause => 'Y',
393 p_dim_map => p_dim_map);
394 add_dimension('ITEM+POA_COMMODITIES',
395 p_func_area,
396 p_version,
397 p_role,
398 p_mv_set,
399 p_generate_where_clause => 'Y',
400 p_dim_map => p_dim_map);
401 add_dimension('LOOKUP+CONTRACT_DOCTYPE',
402 p_func_area,
403 p_version,
404 p_role,
405 p_mv_set,
409 p_func_area,
406 p_generate_where_clause => 'Y',
407 p_dim_map => p_dim_map);
408 add_dimension('ITEM+ENI_ITEM_PO_CAT',
410 p_version,
411 p_role,
412 p_mv_set,
413 p_generate_where_clause => 'Y',
414 p_dim_map => p_dim_map);
415 add_dimension('ITEM+POA_ITEMS',
416 p_func_area,
417 p_version,
418 p_role,
419 p_mv_set,
420 p_generate_where_clause => 'Y',
421 p_dim_map => p_dim_map);
422 add_dimension('POA_PERSON+REQUESTER',
423 p_func_area,
424 p_version,
425 p_role,
426 p_mv_set,
427 p_generate_where_clause => 'Y',
428 p_dim_map => p_dim_map);
429 add_dimension('FII_COMPANIES+FII_COMPANIES',
430 p_func_area,
431 p_version,
432 p_role,
433 p_mv_set,
434 p_generate_where_clause => 'N',
435 p_dim_map => p_dim_map);
436 add_dimension('ORGANIZATION+HRI_CL_ORGCC',
437 p_func_area,
438 p_version,
439 p_role,
440 p_mv_set,
441 p_generate_where_clause => 'N',
442 p_dim_map => p_dim_map);
443 add_dimension('POA_PERSON+NEG_CREATOR',
444 p_func_area,
445 p_version,
446 p_role,
447 p_mv_set,
448 p_generate_where_clause => 'Y',
449 p_dim_map => p_dim_map);
450 add_dimension('LOOKUP+NEG_DOCTYPES',
451 p_func_area,
452 p_version,
453 p_role,
454 p_mv_set,
455 p_generate_where_clause => 'Y',
456 p_dim_map => p_dim_map);
457 add_dimension('POA_PERSON+INV_CREATOR',
458 p_func_area,
459 p_version,
460 p_role,
461 p_mv_set,
462 p_generate_where_clause => 'Y',
463 p_dim_map => p_dim_map);
464 END;
465
466 FUNCTION get_col_name(dim_name VARCHAR2, p_func_area in VARCHAR2, p_version in VARCHAR2, p_mv_set in VARCHAR2) return VARCHAR2
467 is
468 l_col_name varchar2(100);
469
470 begin
471 if(dim_name = 'ORGANIZATION+FII_OPERATING_UNITS') then
472 l_col_name := 'org_id';
473 elsif(dim_name = 'ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION') THEN
474 IF (p_mv_set in ('PQC','API','REQS','REQMP','REQMF')) THEN
475 l_col_name := 'ship_to_org_id';
476 ELSE
477 l_col_name := 'receiving_org_id';
478 END IF;
479 elsif(dim_name = 'ITEM+ENI_ITEM_PO_CAT') then
480 l_col_name := 'category_id';
481 elsif(dim_name ='ITEM+POA_ITEMS') then
482 l_col_name := 'po_item_id';
483 elsif(dim_name ='HRI_PERSON+HRI_PER' ) then
484 if(p_mv_set = 'IDL' or p_mv_set = 'MID' or
485 p_mv_set = 'IDLA' or p_mv_set = 'IDLB') then
486 l_col_name := 'inv_d_created_by';
487 else
488 l_col_name := 'buyer_id';
489 end if;
490 elsif(dim_name = 'POA_PERSON+INV_CREATOR') then
491 l_col_name := 'inv_d_created_by';
492 elsif(dim_name = 'POA_PERSON+REQUESTER') then
493 l_col_name := 'requester_id';
494 elsif(dim_name = 'SUPPLIER+POA_SUPPLIERS') then
495 l_col_name := 'supplier_id';
496 elsif(dim_name ='SUPPLIER+POA_SUPPLIER_SITES') then
497 l_col_name := 'supplier_site_id';
498 elsif(dim_name = 'ITEM+POA_COMMODITIES') then
499 l_col_name := 'commodity_id';
500 elsif(dim_name = 'LOOKUP+RETURN_REASON') then
501 l_col_name := 'reason_id';
502 elsif(dim_name = 'LOOKUP+CONTRACT_DOCTYPE') then
503 l_col_name := 'contract_type';
504 elsif(dim_name = 'FII_COMPANIES+FII_COMPANIES') then
505 l_col_name := 'company_id';
506 elsif(dim_name = 'ORGANIZATION+HRI_CL_ORGCC') then
507 l_col_name := 'cost_center_id';
508 elsif(dim_name = 'POA_PERSON+NEG_CREATOR') then
509 l_col_name := 'negotiation_creator_id';
510 elsif(dim_name = 'LOOKUP+NEG_DOCTYPES') then
511 l_col_name := 'doctype_id';
512 end if;
513
514 return l_col_name;
515 END;
516
517 FUNCTION get_bmap(p_dim IN VARCHAR2) RETURN NUMBER IS
518 l_bmap NUMBER;
519 BEGIN
520 l_bmap := (case p_dim
521 WHEN 'ORGANIZATION+FII_OPERATING_UNITS' THEN OPER_UNIT_BMAP
525 WHEN 'POA_PERSON+INV_CREATOR' THEN CLERK_BMAP
522 WHEN 'ITEM+ENI_ITEM_PO_CAT' THEN CATEGORY_BMAP
523 WHEN 'ITEM+POA_ITEMS' THEN ITEM_BMAP
524 WHEN 'HRI_PERSON+HRI_PER' THEN BUYER_BMAP
526 WHEN 'SUPPLIER+POA_SUPPLIERS' THEN SUPPLIER_BMAP
527 WHEN 'SUPPLIER+POA_SUPPLIER_SITES' THEN SUPPLIER_SITE_BMAP
528 WHEN 'ITEM+POA_COMMODITIES' THEN COMMODITY_BMAP
529 WHEN 'ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION' THEN REC_ORG_BMAP
533 WHEN 'FII_COMPANIES+FII_COMPANIES' THEN COMPANY_BMAP
530 WHEN 'LOOKUP+RETURN_REASON' THEN REASON_BMAP
531 WHEN 'LOOKUP+CONTRACT_DOCTYPE' THEN DOCTYPE_BMAP
532 WHEN 'POA_PERSON+REQUESTER' THEN REQUESTER_BMAP
534 WHEN 'ORGANIZATION+HRI_CL_ORGCC' THEN COSTCTR_BMAP
535 WHEN 'POA_PERSON+NEG_CREATOR' THEN BUYER_BMAP
536 WHEN 'LOOKUP+NEG_DOCTYPES' THEN DOCTYPE_BMAP
537 ELSE ''
538 END);
539 return l_bmap;
540 end;
541
542 FUNCTION get_table(dim_name VARCHAR2, p_func_area in VARCHAR2, p_version in VARCHAR2) return VARCHAR2
543 is
544 l_table varchar2(4000);
545
546 begin
547
548 l_table := (CASE dim_name
549 WHEN 'ORGANIZATION+FII_OPERATING_UNITS' THEN '(select organization_id id, name value from hr_all_organization_units_tl where language = userenv(''LANG''))'
550 WHEN 'ITEM+ENI_ITEM_PO_CAT' THEN 'eni_item_po_cat_v'
551 WHEN 'ITEM+POA_ITEMS' THEN 'poa_items_v '
552 WHEN 'HRI_PERSON+HRI_PER' THEN '(select person_id id, substrb(first_name,1,1) || ''. ''|| last_name value from per_all_people_f where sysdate between effective_start_date and effective_end_date)'
553 WHEN 'POA_PERSON+INV_CREATOR' THEN '(select person_id id, substrb(first_name,1,1) || ''. ''|| last_name value from per_all_people_f where sysdate between effective_start_date and effective_end_date)'
554 WHEN 'SUPPLIER+POA_SUPPLIERS' THEN 'poa_suppliers_v'
555 WHEN 'SUPPLIER+POA_SUPPLIER_SITES' THEN 'poa_supplier_sites_v'
556 WHEN 'ITEM+POA_COMMODITIES' THEN 'po_commodities_tl'
557 WHEN 'ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION' THEN '(select organization_id id, name value from hr_all_organization_units_tl where language = userenv(''LANG''))'
558 WHEN 'LOOKUP+RETURN_REASON' THEN 'mtl_transaction_reasons'
559 WHEN 'LOOKUP+CONTRACT_DOCTYPE' THEN 'poa_dbi_contract_type_v'
560 WHEN 'POA_PERSON+REQUESTER' THEN '(select person_id id, substrb(first_name,1,1) || ''. ''|| last_name value from per_all_people_f where sysdate between effective_start_date and effective_end_date)'
561 WHEN 'FII_COMPANIES+FII_COMPANIES' THEN
562 '(select t.flex_value_id id, t.description value, f.summary_flag
563 from fnd_flex_values_tl t, fii_com_pmv_agrt_nodes c, fnd_flex_values f
564 where c.company_id = t.flex_value_id and t.language = userenv(''LANG'') and t.flex_value_id = f.flex_value_id)'
565 WHEN 'ORGANIZATION+HRI_CL_ORGCC' THEN
566 '(select t.flex_value_id id, t.description value, f.summary_flag
567 from fnd_flex_values_tl t, fii_cc_pmv_agrt_nodes c, fnd_flex_values f
568 where c.cost_center_id = t.flex_value_id and t.language = userenv(''LANG'') and t.flex_value_id = f.flex_value_id)'
569 WHEN 'POA_PERSON+NEG_CREATOR' THEN '(select person_id id, substrb(first_name,1,1) || ''. ''|| last_name value from per_all_people_f where sysdate between effective_start_date and effective_end_date)'
570 WHEN 'LOOKUP+NEG_DOCTYPES' THEN 'poa_neg_doctypes_v'
571 ELSE ''
572 END);
573
574 return l_table;
575 end;
576
577
578
579 PROCEDURE get_join_info(p_view_by IN varchar2,
580 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map,
581 x_join_tbl OUT NOCOPY poa_dbi_util_pkg.POA_DBI_JOIN_TBL,
582 p_func_area IN varchar2, p_version IN varchar2)
583 IS
584 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
585
586 BEGIN
587 x_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
588 if(NOT p_dim_map.exists(p_view_by)) then
589 return;
590 end if;
591
592 l_join_rec.table_name := p_dim_map(p_view_by).view_by_table;
593 l_join_rec.table_alias := 'v';
594 l_join_rec.fact_column := p_dim_map(p_view_by).col_name;
595
596 if(p_view_by ='ITEM+POA_COMMODITIES') then
597 l_join_rec.additional_where_clause :=
598 ' (v.language=USERENV(''LANG'') or v.language is null) ';
599 l_join_rec.column_name := 'commodity_id';
600 l_join_rec.dim_outer_join := 'Y';
601 elsif(p_view_by = 'LOOKUP+RETURN_REASON') then
602 l_join_rec.column_name := 'reason_id(+)';
603 else
604 l_join_rec.column_name := 'id';
605 end if;
606
607 if(p_view_by = 'HRI_PERSON+HRI_PER' and p_func_area = 'AP') then
608 l_join_rec.dim_outer_join := 'Y';
609 end if;
610 if(p_view_by = 'POA_PERSON+INV_CREATOR' and p_func_area = 'AP') then
611 l_join_rec.dim_outer_join := 'Y';
612 end if;
613 --Added to handle the unassigned row issue
614 if(p_func_area = 'PO' and p_version = '7.1') then
615 if ((p_view_by = 'HRI_PERSON+HRI_PER')
616 or (p_view_by = 'SUPPLIER+POA_SUPPLIERS') ) then
617 l_join_rec.dim_outer_join := 'Y';
618 end if;
619 end if;
620
621 if(p_func_area = 'PO' and p_version = '8.0') then
622 if ((p_view_by = 'POA_PERSON+NEG_CREATOR')
623 or (p_view_by = 'LOOKUP+NEG_DOCTYPES') ) then
624 l_join_rec.dim_outer_join := 'Y';
625 end if;
626 end if;
627
628 if(p_view_by = 'FII_COMPANIES+FII_COMPANIES')then
629 l_join_rec.inner_alias := 'com';
630 elsif (p_view_by = 'ORGANIZATION+HRI_CL_ORGCC') then
631 l_join_rec.inner_alias := 'cc';
632 end if;
633
634 x_join_tbl.extend;
635 x_join_tbl(x_join_tbl.count) := l_join_rec;
636
637 if(p_view_by = 'ITEM+POA_ITEMS' and
638 (p_version = '6.0' or p_version = '7.0' or p_version='7.1' or p_version='8.0')) then
639 l_join_rec.table_name := 'mtl_units_of_measure_vl';
640 l_join_rec.table_alias := 'v2';
641 l_join_rec.fact_column :='base_uom';
642 l_join_rec.column_name := 'unit_of_measure';
643 l_join_rec.dim_outer_join := 'Y';
644
645 x_join_tbl.extend;
646 x_join_tbl(x_join_tbl.count) := l_join_rec;
647 end if;
648
649 END;
650
651
652 PROCEDURE populate_in_join_tbl(
653 p_in_join_tbl out NOCOPY poa_dbi_util_pkg.poa_dbi_in_join_tbl
654 , p_param in BIS_PMV_PAGE_PARAMETER_TBL
655 , p_dim_map in poa_dbi_util_pkg.poa_dbi_dim_map
656 , p_context_code VARCHAR2
657 , p_version in VARCHAR2
658 , p_mv_set in varchar2
659 , p_where_clause in out nocopy varchar2)
660 IS
661 l_ou_value VARCHAR2(10);
662 l_commodity_value VARCHAR2(10);
663 l_in_join_rec poa_dbi_util_pkg.POA_DBI_IN_JOIN_REC;
664 l_company_value varchar2(30) := 'All';
665 l_cost_ctr_value varchar2(30) := 'All';
666 l_region_code varchar2(40);
667 l_viewby varchar2(50);
668 l_sql varchar2(2000);
669 l_agg_flag varchar2(1);
670 BEGIN
671
672 p_in_join_tbl := poa_dbi_util_pkg.poa_dbi_in_join_tbl();
673
674 IF(p_context_code = 'OU/COM' or p_context_code = 'OU' or p_context_code = 'NEG'
675 OR p_context_code = 'OUX'
676 ) THEN
677 l_ou_value := p_dim_map('ORGANIZATION+FII_OPERATING_UNITS').value;
678 if(l_ou_value is null or
679 l_ou_value = '' or
680 l_ou_value = 'All') then
681 null;
682 --l_in_join_rec.table_name := 'per_organization_list';
683 --l_in_join_rec.table_alias := 'orgl';
684 --p_in_join_tbl.extend;
685 --p_in_join_tbl(p_in_join_tbl.count) := l_in_join_rec;
686
687 end if;
688
689 IF(p_context_code = 'OU/COM' or p_context_code = 'NEG') THEN
690 if(p_version = '6.0' or p_version = '7.0' or p_version = '8.0') then
691 l_commodity_value := p_dim_map('ITEM+POA_COMMODITIES').value;
692 if(l_commodity_value is null or
693 l_commodity_value = '' or
694 l_commodity_value = 'All') then
695 null;
696 /* Commented out, as the Security Clauses are handled in Util Package itself
697 l_in_join_rec.table_name := 'po_commodity_grants';
698 l_in_join_rec.table_alias := 'sec';
699 p_in_join_tbl.extend;
700 p_in_join_tbl(p_in_join_tbl.count) := l_in_join_rec;
701
702 l_in_join_rec.table_name := 'fnd_user';
703 l_in_join_rec.table_alias := 'u';
704 p_in_join_tbl.extend;
705 p_in_join_tbl(p_in_join_tbl.count) := l_in_join_rec;
706
707 l_in_join_rec.table_name := 'fnd_menu_entries';
708 l_in_join_rec.table_alias := 'poa_me';
709 p_in_join_tbl.extend;
710 p_in_join_tbl(p_in_join_tbl.count) := l_in_join_rec;
711
712 l_in_join_rec.table_name := 'fnd_form_functions';
713 l_in_join_rec.table_alias := 'f';
714 p_in_join_tbl.extend;
715 p_in_join_tbl(p_in_join_tbl.count) := l_in_join_rec; */
716 end if;
717 end if;
718 END IF;
719 ELSIF(p_context_code = 'SUPPLIER') THEN
720 null;
721 /* l_in_join_rec.table_name := 'ak_web_user_sec_attr_values';
722 l_in_join_rec.table_alias := 'isp';
723 p_in_join_tbl.extend;
724 p_in_join_tbl(p_in_join_tbl.count) := l_in_join_rec;
725
726 l_in_join_rec.table_name := 'fnd_application';
727 l_in_join_rec.table_alias := 'appl';
728 p_in_join_tbl.extend;
729 p_in_join_tbl(p_in_join_tbl.count) := l_in_join_rec;
730 */
731 ELSIF(p_context_code = 'COMP') THEN
732 /*get ak-region, company, cost-center values*/
733 for i in 1..p_param.count loop
734 if (p_param(i).parameter_name = 'BIS_REGION_CODE') then
735 l_region_code := p_param(i).parameter_value;
736 elsif (p_param(i).parameter_name = 'FII_COMPANIES+FII_COMPANIES') then
737 l_company_value := nvl(p_param(i).parameter_id,'All');
738 elsif (p_param(i).parameter_name = 'ORGANIZATION+HRI_CL_ORGCC') then
739 l_cost_ctr_value := nvl(p_param(i).parameter_id,'All');
740 elsif (p_param(i).parameter_name = 'VIEW_BY') then
741 l_viewby := p_param(i).parameter_value;
742 end if;
743 end loop;
744 if (l_company_value = '''''')then
745 l_company_value := 'All';
746 end if;
747 if (l_cost_ctr_value = '''''')then
748 l_cost_ctr_value := 'All';
749 end if;
750 l_company_value := translate(l_company_value,'''',' ');
751 l_cost_ctr_value := translate(l_cost_ctr_value,'''',' ');
752 get_company_sql(l_viewby, l_company_value, l_region_code, l_sql, l_agg_flag);
753 p_where_clause := p_where_clause || ' and com.child_company_id = fact.company_id ';
754 if(p_mv_set = 'APIA' or p_mv_set = 'PODA' or p_mv_set = 'PQCA' or p_mv_set = 'IDLA' or p_mv_set = 'PODCUTA') then
755 if(l_sql like '%fii_company_hierarchies%') then
756 l_in_join_rec.table_name := '(select /*+no_merge*/ a.parent_company_id, a.company_id, a.company_id child_company_id '||
757 'from ('||l_sql||')a '||
758 'where a.com_agg_flag = ''Y'')';
759 p_where_clause := p_where_clause || ' and fact.parent_company_id = com.parent_company_id ';
760 else
761 l_in_join_rec.table_name := '(select /*+no_merge*/ a.company_id, a.company_id child_company_id '||
762 'from ('||l_sql||')a '||
763 'where a.com_agg_flag = ''Y'')';
764 end if;
765 else
766 l_in_join_rec.table_name := '(select /*+no_merge*/ a.company_id, com.child_company_id, a.com_agg_flag '||
767 'from ('||l_sql||')a, '||
768 'fii_company_hierarchies com '||
769 'where a.company_id = com.parent_company_id)';
770 end if;
774 p_in_join_tbl(p_in_join_tbl.count) := l_in_join_rec;
771 l_in_join_rec.table_alias := 'com';
772 l_in_join_rec.aggregated_flag := l_agg_flag;
773 p_in_join_tbl.extend;
775
776 get_cost_ctr_sql(l_viewby, l_cost_ctr_value, l_region_code, l_sql, l_agg_flag);
777 p_where_clause := p_where_clause || ' and cc.child_cc_id = fact.cost_center_id ';
778 if(p_mv_set = 'APIA' or p_mv_set = 'PODA' or p_mv_set = 'PQCA' or p_mv_set = 'IDLA' or p_mv_set = 'PODCUTA') then
779 if(l_sql like '%fii_cost_ctr_hierarchies%') then
780 l_in_join_rec.table_name := '(select /*+no_merge*/ b.parent_cc_id, b.cost_center_id, b.cost_center_id child_cc_id '||
781 'from ('||l_sql||')b '||
782 'where b.cc_agg_flag = ''Y'')';
783 p_where_clause := p_where_clause || ' and fact.parent_cc_id = cc.parent_cc_id ';
784 else
785 l_in_join_rec.table_name := '(select /*+no_merge*/ b.cost_center_id, b.cost_center_id child_cc_id '||
786 'from ('||l_sql||')b '||
787 'where b.cc_agg_flag = ''Y'')';
788 end if;
789 else
790 l_in_join_rec.table_name := '(select /*+no_merge*/ b.cost_center_id, cc.child_cc_id, b.cc_agg_flag '||
791 'from ('||l_sql||')b, '||
792 'fii_cost_ctr_hierarchies cc '||
793 'where b.cost_center_id = cc.parent_cc_id)';
794 end if;
795 l_in_join_rec.table_alias := 'cc';
796 l_in_join_rec.aggregated_flag := l_agg_flag;
797 p_in_join_tbl.extend;
798 p_in_join_tbl(p_in_join_tbl.count) := l_in_join_rec;
799
800 if(p_mv_set = 'APIB' or p_mv_set = 'PODB' or p_mv_set = 'PQCB' or p_mv_set = 'IDLB' or p_mv_set = 'PODCUTB') then
801 p_where_clause := p_where_clause || ' and (com.com_agg_flag = ''N'' or cc.cc_agg_flag = ''N'' ) ';
802 end if;
803
804 END IF;
805 END;
806
807 FUNCTION get_viewby_select_clause(p_viewby IN VARCHAR2, p_func_area IN VARCHAR2,
808 p_version IN VARCHAR2) RETURN VARCHAR2 IS
809 BEGIN
810
811 IF(p_func_area = 'PO' and p_version = '7.1') THEN
812 IF ((p_viewby = 'HRI_PERSON+HRI_PER')
813 or (p_viewby = 'SUPPLIER+POA_SUPPLIERS') ) THEN
814 return
815 'select decode(v.value,null,fnd_message.get_string'
816 || '(''POA'', ''POA_DBI_APL_UNASSIGNED''),v.value) VIEWBY,'
817 || fnd_global.newline
818 || ' decode(v.id, null, -1, v.id) VIEWBYID,'
819 || fnd_global.newline;
820 end if;
821 end if;
822
823 IF(p_func_area = 'PO' and p_version = '8.0') THEN
824 IF ((p_viewby = 'POA_PERSON+NEG_CREATOR')
825 or (p_viewby = 'LOOKUP+NEG_DOCTYPES') ) THEN
826 return
827 'select decode(v.value,null,fnd_message.get_string'
828 || '(''POA'', ''POA_DBI_APL_UNASSIGNED''),v.value) VIEWBY,'
829 || fnd_global.newline
830 || ' decode(v.id, null, -1, v.id) VIEWBYID,'
831 || fnd_global.newline;
832 end if;
833 end if;
834
835 if(p_viewby = 'ITEM+POA_COMMODITIES') then
836 return
837 'select decode(v.name, null, fnd_message.get_string(''POA'',''POA_DBI_APL_UNASSIGNED''), v.name) VIEWBY,
838 decode(v.commodity_id,null, -1, v.commodity_id) VIEWBYID,';
839 elsif(p_viewby = 'LOOKUP+RETURN_REASON') then
840 return
841 'select decode(v.reason_name, null, fnd_message.get_string(''POA'', ''POA_DBI_APL_UNASSIGNED''), v.reason_name) VIEWBY,
842 decode(v.reason_id, null, -1, v.reason_id) VIEWBYID,';
843 else
844 return
845 'select v.value VIEWBY,v.id VIEWBYID,';
846 end if;
847 END;
848
849 FUNCTION get_fact_hint(p_mv IN VARCHAR2) RETURN VARCHAR2 IS
850 BEGIN
851 return '/*+ INDEX_COMBINE(fact '|| p_mv || ' ' || p_mv ||'_n1) */';
852 END;
853
854
855 FUNCTION get_agg_level(p_dim_bmap IN NUMBER,
856 p_mv_set IN VARCHAR2)
857 RETURN NUMBER
858 IS
859 l_index NUMBER:= 1;
860 l_cost NUMBER;
861 l_agg_lvl_tbl POA_DBI_AGG_LEVEL_TBL;
862 BEGIN
863 populate_agg_level(l_agg_lvl_tbl,p_mv_set);
864 l_cost := l_agg_lvl_tbl(1).agg_bmap;
865 FOR i IN l_agg_lvl_tbl.FIRST .. l_agg_lvl_tbl.LAST
866 LOOP
867 IF (bitand(l_agg_lvl_tbl(i).agg_bmap, p_dim_bmap) = p_dim_bmap) THEN
868 IF(l_agg_lvl_tbl(i).agg_bmap < l_cost) THEN
869 l_cost := l_agg_lvl_tbl(i).agg_bmap;
870 l_index := i;
871 END IF;
872 END IF;
873 END LOOP;
874 return l_agg_lvl_tbl(l_index).agg_level;
875 END;
876
877 PROCEDURE populate_agg_level(
878 p_agg_lvl_tbl OUT NOCOPY POA_DBI_AGG_LEVEL_TBL,
879 p_mv_set IN VARCHAR2
880 )
881 IS
882 l_rec POA_DBI_AGG_LEVEL_REC;
883 BEGIN
884 p_agg_lvl_tbl := POA_DBI_AGG_LEVEL_TBL();
885 if(p_mv_set = 'POD' or p_mv_set='PODCUT') then
886 p_agg_lvl_tbl.extend(5);
887
888 l_rec.agg_level := 0;
889 l_rec.agg_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP
890 + BUYER_BMAP + CATEGORY_BMAP + ITEM_BMAP
891 + SUPPLIER_BMAP + SUPPLIER_SITE_BMAP
892 + DOCTYPE_BMAP;
893 p_agg_lvl_tbl(1) := l_rec;
894
895 l_rec.agg_level := 15;
896 l_rec.agg_bmap := OPER_UNIT_BMAP;
897 p_agg_lvl_tbl(2) := l_rec;
898
899 l_rec.agg_level := 7;
900 l_rec.agg_bmap := OPER_UNIT_BMAP + COMMODITY_BMAP;
901 p_agg_lvl_tbl(3) := l_rec;
902
903 l_rec.agg_level := 3;
904 l_rec.agg_bmap := OPER_UNIT_BMAP + COMMODITY_BMAP + CATEGORY_BMAP;
905 p_agg_lvl_tbl(4) := l_rec;
906
907 l_rec.agg_level := 1;
908 l_rec.agg_bmap := OPER_UNIT_BMAP + COMMODITY_BMAP
909 + SUPPLIER_BMAP + CATEGORY_BMAP;
910 p_agg_lvl_tbl(5) := l_rec;
914 l_rec.agg_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP
911 elsif(p_mv_set = 'PODA' or p_mv_set='PODCUTA') then
912 p_agg_lvl_tbl.extend(2);
913 l_rec.agg_level := 0;
915 + CATEGORY_BMAP + ITEM_BMAP
916 + SUPPLIER_BMAP + DOCTYPE_BMAP
917 + COMPANY_BMAP + COSTCTR_BMAP;
918 p_agg_lvl_tbl(1) := l_rec;
919 l_rec.agg_level := 1;
920 l_rec.agg_bmap := COMPANY_BMAP + COSTCTR_BMAP
921 + COMMODITY_BMAP + CATEGORY_BMAP;
922 p_agg_lvl_tbl(2) := l_rec;
923 elsif(p_mv_set = 'PODB' or p_mv_set='PODCUTB') then
924 p_agg_lvl_tbl.extend(4);
925 l_rec.agg_level := 0;
926 l_rec.agg_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP
927 + CATEGORY_BMAP + ITEM_BMAP
928 + SUPPLIER_BMAP + DOCTYPE_BMAP
929 + COMPANY_BMAP + COSTCTR_BMAP;
930 p_agg_lvl_tbl(1) := l_rec;
931 l_rec.agg_level := 3;
932 l_rec.agg_bmap := COMMODITY_BMAP + CATEGORY_BMAP
933 + COMPANY_BMAP + COSTCTR_BMAP;
934 p_agg_lvl_tbl(2) := l_rec;
935 l_rec.agg_level := 5;
936 l_rec.agg_bmap := OPER_UNIT_BMAP + SUPPLIER_BMAP
937 + COMPANY_BMAP + COSTCTR_BMAP;
938 p_agg_lvl_tbl(3) := l_rec;
939 l_rec.agg_level := 7;
940 l_rec.agg_bmap := COMPANY_BMAP + COSTCTR_BMAP;
941 p_agg_lvl_tbl(4) := l_rec;
942 ELSIF(p_mv_set = 'REQMP' or p_mv_set = 'REQMF' or p_mv_set ='REQS') THEN
943 p_agg_lvl_tbl.extend(4);
944
945 l_rec.agg_level := 0;
946 l_rec.agg_bmap := OPER_UNIT_BMAP + REQUESTER_BMAP
947 + BUYER_BMAP + CATEGORY_BMAP
948 + ITEM_BMAP + SUPPLIER_BMAP
949 + REC_ORG_BMAP;
950 p_agg_lvl_tbl(1) := l_rec;
951
952 l_rec.agg_level := 1;
953 l_rec.agg_bmap := OPER_UNIT_BMAP + CATEGORY_BMAP
954 + BUYER_BMAP ;
955 p_agg_lvl_tbl(2) := l_rec;
956
957 l_rec.agg_level := 3;
958 l_rec.agg_bmap := OPER_UNIT_BMAP + BUYER_BMAP;
959 p_agg_lvl_tbl(3) := l_rec;
960
961 l_rec.agg_level := 7;
962 l_rec.agg_bmap := OPER_UNIT_BMAP;
963 p_agg_lvl_tbl(4) := l_rec;
964
965 ELSIF(p_mv_set = 'IDL') THEN
966 p_agg_lvl_tbl.extend(3);
967
968 l_rec.agg_level := 0;
969 l_rec.agg_bmap := OPER_UNIT_BMAP + SUPPLIER_BMAP
970 + SUPPLIER_SITE_BMAP + CLERK_BMAP;
971 p_agg_lvl_tbl(1) := l_rec;
972
973 l_rec.agg_level := 1;
974 l_rec.agg_bmap := OPER_UNIT_BMAP + SUPPLIER_BMAP;
975 p_agg_lvl_tbl(2) := l_rec;
976
977 l_rec.agg_level := 3;
978 l_rec.agg_bmap := OPER_UNIT_BMAP;
979 p_agg_lvl_tbl(3) := l_rec;
980
981 ELSIF(p_mv_set = 'IDLA') THEN
982 p_agg_lvl_tbl.extend(2);
983
984 l_rec.agg_level := 0;
985 l_rec.agg_bmap := COMPANY_BMAP + COSTCTR_BMAP
986 + OPER_UNIT_BMAP + SUPPLIER_BMAP
987 + CLERK_BMAP;
988 p_agg_lvl_tbl(1) := l_rec;
989 l_rec.agg_level := 1;
990 l_rec.agg_bmap := COMPANY_BMAP + COSTCTR_BMAP;
991 p_agg_lvl_tbl(2) := l_rec;
992 ELSIF(p_mv_set = 'IDLB') THEN
993 p_agg_lvl_tbl.extend(2);
994
995 l_rec.agg_level := 0;
996 l_rec.agg_bmap := COMPANY_BMAP + COSTCTR_BMAP
997 + OPER_UNIT_BMAP + SUPPLIER_BMAP
998 + CLERK_BMAP;
999 p_agg_lvl_tbl(1) := l_rec;
1000 l_rec.agg_level := 1;
1001 l_rec.agg_bmap := COMPANY_BMAP + COSTCTR_BMAP;
1002 p_agg_lvl_tbl(2) := l_rec;
1003 ELSIF(p_mv_set = 'PQC') THEN
1004 p_agg_lvl_tbl.extend(3);
1005
1006 l_rec.agg_level := 0;
1007 l_rec.agg_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP
1008 + REC_ORG_BMAP + BUYER_BMAP
1009 + CATEGORY_BMAP + ITEM_BMAP
1010 + SUPPLIER_BMAP + SUPPLIER_SITE_BMAP;
1011 p_agg_lvl_tbl(1) := l_rec;
1012
1013 l_rec.agg_level := 1;
1014 l_rec.agg_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP
1015 + CATEGORY_BMAP + SUPPLIER_BMAP;
1016 p_agg_lvl_tbl(2) := l_rec;
1017
1018 l_rec.agg_level := 3;
1019 l_rec.agg_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP;
1020 p_agg_lvl_tbl(3) := l_rec;
1021
1022 ELSIF(p_mv_set = 'PQCA') then
1023 p_agg_lvl_tbl.extend(2);
1024
1025 l_rec.agg_level := 0;
1026 l_rec.agg_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP
1027 + CATEGORY_BMAP + ITEM_BMAP
1028 + SUPPLIER_BMAP + COMPANY_BMAP
1029 + COSTCTR_BMAP;
1030 p_agg_lvl_tbl(1) := l_rec;
1031 l_rec.agg_level := 1;
1032 l_rec.agg_bmap := COMPANY_BMAP + COSTCTR_BMAP
1033 + COMMODITY_BMAP + CATEGORY_BMAP;
1034 p_agg_lvl_tbl(2) := l_rec;
1035 ELSIF(p_mv_set = 'PQCB') then
1036 p_agg_lvl_tbl.extend(4);
1037
1038 l_rec.agg_level := 0;
1039 l_rec.agg_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP
1040 + CATEGORY_BMAP + ITEM_BMAP
1041 + SUPPLIER_BMAP + COMPANY_BMAP
1042 + COSTCTR_BMAP;
1043 p_agg_lvl_tbl(1) := l_rec;
1044
1045 l_rec.agg_level := 1;
1046 l_rec.agg_bmap := COMPANY_BMAP + COSTCTR_BMAP
1047 + SUPPLIER_BMAP + OPER_UNIT_BMAP;
1048 p_agg_lvl_tbl(2) := l_rec;
1049
1050 l_rec.agg_level := 2;
1051 l_rec.agg_bmap := COMPANY_BMAP + COSTCTR_BMAP
1052 + COMMODITY_BMAP + CATEGORY_BMAP;
1053 p_agg_lvl_tbl(3) := l_rec;
1054
1055 l_rec.agg_level := 3;
1056 l_rec.agg_bmap := COMPANY_BMAP + COSTCTR_BMAP;
1057 p_agg_lvl_tbl(4) := l_rec;
1058 ELSIF(p_mv_set = 'RTX') THEN
1059 p_agg_lvl_tbl.extend(4);
1060
1061 l_rec.agg_level := 0;
1065 + ITEM_BMAP + SUPPLIER_BMAP
1062 l_rec.agg_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP
1063 + REC_ORG_BMAP
1064 + BUYER_BMAP + CATEGORY_BMAP
1066 + SUPPLIER_SITE_BMAP + REASON_BMAP;
1067 p_agg_lvl_tbl(1) := l_rec;
1068
1069 l_rec.agg_level := 1;
1070 l_rec.agg_bmap := OPER_UNIT_BMAP + COMMODITY_BMAP
1071 + CATEGORY_BMAP + SUPPLIER_BMAP ;
1072 p_agg_lvl_tbl(2) := l_rec;
1073
1074 l_rec.agg_level := 3;
1075 l_rec.agg_bmap := OPER_UNIT_BMAP + COMMODITY_BMAP
1076 + SUPPLIER_BMAP;
1077 p_agg_lvl_tbl(3) := l_rec;
1078
1079 l_rec.agg_level := 7;
1080 l_rec.agg_bmap := OPER_UNIT_BMAP + COMMODITY_BMAP;
1081 p_agg_lvl_tbl(4) := l_rec;
1082
1083
1084 ELSIF(p_mv_set = 'MID') THEN
1085
1086 p_agg_lvl_tbl.extend(3);
1087
1088 l_rec.agg_level := 0;
1089 l_rec.agg_bmap := OPER_UNIT_BMAP + SUPPLIER_BMAP
1090 + SUPPLIER_SITE_BMAP + CLERK_BMAP;
1091 p_agg_lvl_tbl(1) := l_rec;
1092
1093 l_rec.agg_level := 1;
1094 l_rec.agg_bmap := OPER_UNIT_BMAP + SUPPLIER_BMAP;
1095 p_agg_lvl_tbl(2) := l_rec;
1096
1097 l_rec.agg_level := 3;
1098 l_rec.agg_bmap := OPER_UNIT_BMAP;
1099 p_agg_lvl_tbl(3) := l_rec;
1100
1101 ELSIF(p_mv_set = 'API') THEN
1102 p_agg_lvl_tbl.extend(3);
1103
1104 l_rec.agg_level := 0;
1105 l_rec.agg_bmap := OPER_UNIT_BMAP + BUYER_BMAP + CATEGORY_BMAP
1106 + ITEM_BMAP + SUPPLIER_BMAP + SUPPLIER_SITE_BMAP
1107 + COMMODITY_BMAP;
1108 p_agg_lvl_tbl(1) := l_rec;
1109
1110 l_rec.agg_level := 1;
1111 l_rec.agg_bmap := OPER_UNIT_BMAP + COMMODITY_BMAP + CATEGORY_BMAP;
1112 p_agg_lvl_tbl(2) := l_rec;
1113
1114 l_rec.agg_level := 3;
1115 l_rec.agg_bmap := OPER_UNIT_BMAP + COMMODITY_BMAP;
1116 p_agg_lvl_tbl(3) := l_rec;
1117 ELSIF(p_mv_set = 'APIA') THEN
1118 p_agg_lvl_tbl.extend(2);
1119
1120 l_rec.agg_level := 0;
1121 l_rec.agg_bmap := OPER_UNIT_BMAP + CATEGORY_BMAP
1122 + ITEM_BMAP + SUPPLIER_BMAP + COMMODITY_BMAP
1123 + COMPANY_BMAP + COSTCTR_BMAP;
1124 p_agg_lvl_tbl(1) := l_rec;
1125 l_rec.agg_level := 1;
1126 l_rec.agg_bmap := COMPANY_BMAP + COSTCTR_BMAP
1127 + COMMODITY_BMAP + CATEGORY_BMAP;
1128 p_agg_lvl_tbl(2) := l_rec;
1129 ELSIF(p_mv_set = 'APIB') THEN
1130 p_agg_lvl_tbl.extend(3);
1131
1132 l_rec.agg_level := 0;
1133 l_rec.agg_bmap := OPER_UNIT_BMAP + CATEGORY_BMAP
1134 + ITEM_BMAP + SUPPLIER_BMAP + COMMODITY_BMAP
1135 + COMPANY_BMAP + COSTCTR_BMAP;
1136 p_agg_lvl_tbl(1) := l_rec;
1137
1138 l_rec.agg_level := 1;
1139 l_rec.agg_bmap := COMPANY_BMAP + COSTCTR_BMAP +
1140 SUPPLIER_BMAP + OPER_UNIT_BMAP +
1141 COMMODITY_BMAP + CATEGORY_BMAP;
1142 p_agg_lvl_tbl(2) := l_rec;
1143
1144 l_rec.agg_level := 3;
1145 l_rec.agg_bmap := COMPANY_BMAP + COSTCTR_BMAP;
1146 p_agg_lvl_tbl(3) := l_rec;
1147
1148 ---Begin Spend Analysis Trend Changes
1149 ELSIF(p_mv_set = 'FIIIV' or p_mv_set = 'FIIPA') THEN
1150 p_agg_lvl_tbl.extend(2);
1151 l_rec.agg_level := 0;
1152 l_rec.agg_bmap := OPER_UNIT_BMAP + SUPPLIER_BMAP ;
1153 p_agg_lvl_tbl(1) := l_rec ;
1154
1155 l_rec.agg_level := 4;
1156 l_rec.agg_bmap := OPER_UNIT_BMAP;
1157 p_agg_lvl_tbl(2) := l_rec ;
1158 ---End Spend Analysis Trend Changes
1159
1160 ---Begin Sourcing Management Change
1161 ELSIF(p_mv_set = 'NEG') THEN
1162 p_agg_lvl_tbl.extend(3);
1163
1164 l_rec.agg_level := 0;
1165 l_rec.agg_bmap := OPER_UNIT_BMAP + BUYER_BMAP + CATEGORY_BMAP
1166 + ITEM_BMAP + SUPPLIER_BMAP + SUPPLIER_SITE_BMAP
1167 + COMMODITY_BMAP + DOCTYPE_BMAP;
1168 p_agg_lvl_tbl(1) := l_rec;
1169
1170 l_rec.agg_level := 1;
1171 l_rec.agg_bmap := OPER_UNIT_BMAP + COMMODITY_BMAP + CATEGORY_BMAP + SUPPLIER_BMAP;
1172 p_agg_lvl_tbl(2) := l_rec;
1173
1174 l_rec.agg_level := 3;
1175 l_rec.agg_bmap := OPER_UNIT_BMAP + COMMODITY_BMAP + CATEGORY_BMAP;
1176 p_agg_lvl_tbl(3) := l_rec;
1177 ---End Sourcing Management Change
1178 end if;
1179 EXCEPTION
1180 WHEN OTHERS THEN
1181 POA_LOG.debug_line('populate_agg_lvl ' || Sqlerrm || sqlcode || sysdate);
1182 raise;
1183 END populate_agg_level;
1184
1185 PROCEDURE get_binds(
1186 p_trend IN VARCHAR2,
1187 p_mv_set IN VARCHAR2,
1188 p_xtd IN VARCHAR2,
1189 p_comparison_type IN VARCHAR2,
1190 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL,
1191 p_context_code IN VARCHAR2,
1192 p_param IN BIS_PMV_PAGE_PARAMETER_TBL
1193 )
1194 IS
1195 l_custom_rec BIS_QUERY_ATTRIBUTES;
1196 BEGIN
1197 /* special case for PQC Cumulative trend, which cannot have the normal trend binds */
1198 if(p_trend = 'Y' and p_mv_set <> 'PQC') then
1199 poa_dbi_util_pkg.get_custom_trend_binds(p_xtd, p_comparison_type,x_custom_output);
1200 else
1201 poa_dbi_util_pkg.get_custom_status_binds(x_custom_output);
1202 end if;
1203
1204 if(p_xtd like 'RL%') then
1205 poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output, p_xtd);
1206 end if;
1207
1208 if(p_mv_set = 'REQS') then
1209 bind_reqfact_date(x_custom_output);
1210 end if;
1211
1212 /* for reports having company and cost center security, we have to bind five variables:
1213 * report_region_code, company_id, top_company_id, cost_center_id and top_cost_center_id
1214 */
1218
1215 if (p_context_code = 'COMP') then
1216 bind_com_cc_values(x_custom_output, p_param);
1217 end if;
1219
1220 END get_binds;
1221
1222
1223 /* THIS FUNCTION get_mv IS NOW OBSOLETE because of the ROLLUP_CHANGES */
1224
1225 FUNCTION get_mv(p_dim_bmap IN NUMBER,
1226 p_func_area in VARCHAR2,
1227 p_version in VARCHAR2,
1228 p_mv_set in VARCHAR2) return VARCHAR2 IS
1229
1230 l_index NUMBER := 1;
1231 l_cost NUMBER;
1232 l_mv_bmap_tbl POA_DBI_MV_BMAP_TBL;
1233
1234 begin
1235
1236 populate_mv_bmap(l_mv_bmap_tbl, p_mv_set);
1237
1238 l_cost := l_mv_bmap_tbl(1).mv_bmap;
1239
1240 FOR i IN l_mv_bmap_tbl.FIRST .. l_mv_bmap_tbl.LAST
1241 LOOP
1242 IF (bitand(l_mv_bmap_tbl(i).mv_bmap, p_dim_bmap) = p_dim_bmap) THEN
1243 IF(l_mv_bmap_tbl(i).mv_bmap < l_cost) THEN
1244 l_cost := l_mv_bmap_tbl(i).mv_bmap;
1245 l_index := i;
1246 END IF;
1247 END IF;
1248 END LOOP;
1249
1250 return l_mv_bmap_tbl(l_index).mv_name;
1251
1252 END;
1253
1254
1255 /* This function is now obsolete because of the rollup arch */
1256 PROCEDURE populate_mv_bmap(p_mv_bmap_tbl out NOCOPY poa_dbi_mv_bmap_tbl,
1257 p_mv_set in varchar2)
1258 IS
1259
1260 l_rec POA_DBI_MV_BMAP_REC;
1261
1262 BEGIN
1263
1264 p_mv_bmap_tbl := POA_DBI_MV_BMAP_TBL();
1265
1266 if(p_mv_set = 'POD') then
1267 p_mv_bmap_tbl.extend(5);
1268
1269 l_rec.mv_name := 'poa_pod_bs_mv';
1270 l_rec.mv_bmap := OPER_UNIT_BMAP + BUYER_BMAP + CATEGORY_BMAP + ITEM_BMAP + SUPPLIER_BMAP + SUPPLIER_SITE_BMAP;
1271 p_mv_bmap_tbl(1) := l_rec;
1272
1273 l_rec.mv_name := 'poa_pod_o_mv';
1274 l_rec.mv_bmap := OPER_UNIT_BMAP;
1275 p_mv_bmap_tbl(2) := l_rec;
1276
1277 l_rec.mv_name := 'poa_pod_oc_mv';
1278 l_rec.mv_bmap := OPER_UNIT_BMAP + CATEGORY_BMAP;
1279 p_mv_bmap_tbl(3) := l_rec;
1280
1281 l_rec.mv_name := 'poa_pod_obc_mv';
1282 l_rec.mv_bmap := OPER_UNIT_BMAP + BUYER_BMAP + CATEGORY_BMAP;
1283 p_mv_bmap_tbl(4) := l_rec;
1284
1285 l_rec.mv_name := 'poa_pod_ocs_mv';
1286 l_rec.mv_bmap := OPER_UNIT_BMAP + CATEGORY_BMAP + SUPPLIER_BMAP;
1287 p_mv_bmap_tbl(5) := l_rec;
1288
1289 elsif(p_mv_set = 'IDL') then
1290
1291 p_mv_bmap_tbl.extend(4);
1292
1293 l_rec.mv_name := 'poa_idl_bs_mv';
1294 l_rec.mv_bmap := OPER_UNIT_BMAP + CLERK_BMAP + SUPPLIER_BMAP + SUPPLIER_SITE_BMAP;
1295 p_mv_bmap_tbl(1) := l_rec;
1296
1297 l_rec.mv_name := 'poa_idl_os_mv';
1298 l_rec.mv_bmap := OPER_UNIT_BMAP + SUPPLIER_BMAP;
1299 p_mv_bmap_tbl(2) := l_rec;
1300
1301 l_rec.mv_name := 'poa_idl_o_mv';
1302 l_rec.mv_bmap := OPER_UNIT_BMAP;
1303 p_mv_bmap_tbl(3) := l_rec;
1304
1305 l_rec.mv_name := 'poa_idl_osy_mv';
1306 l_rec.mv_bmap := OPER_UNIT_BMAP + SUPPLIER_BMAP + CLERK_BMAP;
1307 p_mv_bmap_tbl(4) := l_rec;
1308
1309 elsif(p_mv_set = 'MID') then
1310
1311 p_mv_bmap_tbl.extend(4);
1312
1313 l_rec.mv_name := 'poa_mid_bs_mv';
1314 l_rec.mv_bmap := OPER_UNIT_BMAP + CLERK_BMAP + SUPPLIER_BMAP + SUPPLIER_SITE_BMAP;
1315 p_mv_bmap_tbl(1) := l_rec;
1316
1317 l_rec.mv_name := 'poa_mid_os_mv';
1318 l_rec.mv_bmap := OPER_UNIT_BMAP + SUPPLIER_BMAP;
1319 p_mv_bmap_tbl(2) := l_rec;
1320
1321 l_rec.mv_name := 'poa_mid_o_mv';
1322 l_rec.mv_bmap := OPER_UNIT_BMAP;
1323 p_mv_bmap_tbl(3) := l_rec;
1324
1325 l_rec.mv_name := 'poa_mid_osy_mv';
1326 l_rec.mv_bmap := OPER_UNIT_BMAP + SUPPLIER_BMAP + CLERK_BMAP;
1327 p_mv_bmap_tbl(4) := l_rec;
1328
1329 elsif(p_mv_set = 'RTX') then
1330
1331 p_mv_bmap_tbl.extend(4);
1332
1333 l_rec.mv_name := 'poa_rtx_bs_mv';
1334 l_rec.mv_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP + BUYER_BMAP + CATEGORY_BMAP + ITEM_BMAP + SUPPLIER_BMAP + SUPPLIER_SITE_BMAP + REASON_BMAP;
1335 p_mv_bmap_tbl(1) := l_rec;
1336
1337 l_rec.mv_name := 'poa_rtx_3_mv';
1338 l_rec.mv_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP;
1339 p_mv_bmap_tbl(2) := l_rec;
1340
1341 l_rec.mv_name := 'poa_rtx_4_mv';
1342 l_rec.mv_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP + SUPPLIER_BMAP;
1343 p_mv_bmap_tbl(3) := l_rec;
1344
1345 l_rec.mv_name := 'poa_rtx_5_mv';
1346 l_rec.mv_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP + CATEGORY_BMAP;
1347 p_mv_bmap_tbl(4) := l_rec;
1348
1349 elsif(p_mv_set = 'PQC') then
1350
1351 p_mv_bmap_tbl.extend(5);
1352
1353 l_rec.mv_name := 'poa_pqc_bs_mv';
1354 l_rec.mv_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP + REC_ORG_BMAP + BUYER_BMAP + CATEGORY_BMAP + ITEM_BMAP + SUPPLIER_BMAP + SUPPLIER_SITE_BMAP;
1355 p_mv_bmap_tbl(1) := l_rec;
1356
1357 l_rec.mv_name := 'poa_pqc_3_mv';
1358 l_rec.mv_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP;
1359 p_mv_bmap_tbl(2) := l_rec;
1360
1361 l_rec.mv_name := 'poa_pqc_4_mv';
1362 l_rec.mv_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP + CATEGORY_BMAP ;
1363 p_mv_bmap_tbl(3) := l_rec;
1364
1365 l_rec.mv_name := 'poa_pqc_5_mv';
1366 l_rec.mv_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP + SUPPLIER_BMAP;
1367 p_mv_bmap_tbl(4) := l_rec;
1368
1369 l_rec.mv_name := 'poa_pqc_6_mv';
1370 l_rec.mv_bmap := COMMODITY_BMAP + OPER_UNIT_BMAP + CATEGORY_BMAP + SUPPLIER_BMAP;
1371 p_mv_bmap_tbl(5) := l_rec;
1372
1373 else
1374 if(p_mv_set = 'PODCUT') then
1375 p_mv_bmap_tbl.extend(4);
1376
1377 l_rec.mv_name := 'poa_pod_bs_mv';
1378 l_rec.mv_bmap := OPER_UNIT_BMAP + BUYER_BMAP + CATEGORY_BMAP + ITEM_BMAP + SUPPLIER_BMAP + SUPPLIER_SITE_BMAP + COMMODITY_BMAP;
1379 p_mv_bmap_tbl(1) := l_rec;
1380
1381 l_rec.mv_name := 'poa_pod_11_mv';
1385 l_rec.mv_name := 'poa_pod_12_mv';
1382 l_rec.mv_bmap := OPER_UNIT_BMAP + COMMODITY_BMAP;
1383 p_mv_bmap_tbl(2) := l_rec;
1384
1386 l_rec.mv_bmap := OPER_UNIT_BMAP + CATEGORY_BMAP + SUPPLIER_BMAP + COMMODITY_BMAP;
1387 p_mv_bmap_tbl(3) := l_rec;
1388
1389 l_rec.mv_name := 'poa_pod_13_mv';
1390 l_rec.mv_bmap := OPER_UNIT_BMAP + CATEGORY_BMAP + COMMODITY_BMAP;
1391 p_mv_bmap_tbl(4) := l_rec;
1392
1393 elsif (p_mv_set = 'API') then
1394 p_mv_bmap_tbl.extend(2);
1395
1396 l_rec.mv_name := 'poa_api_bs_mv';
1397 l_rec.mv_bmap := OPER_UNIT_BMAP + BUYER_BMAP + CATEGORY_BMAP + ITEM_BMAP + SUPPLIER_BMAP + SUPPLIER_SITE_BMAP + COMMODITY_BMAP;
1398 p_mv_bmap_tbl(1) := l_rec;
1399
1400 l_rec.mv_name := 'poa_api_3_mv';
1401 l_rec.mv_bmap := OPER_UNIT_BMAP + COMMODITY_BMAP + CATEGORY_BMAP ;
1402 p_mv_bmap_tbl(2) := l_rec;
1403
1404 end if;
1405 end if;
1406
1407 EXCEPTION
1408 WHEN OTHERS THEN
1409 POA_LOG.debug_line('populate_mv_bmap ' || Sqlerrm || sqlcode || sysdate);
1410 raise;
1411
1412 END populate_mv_bmap;
1413
1414 /* Function name: get_display_category
1415 *
1416 * This function is used to restrict the values in the category LOV
1417 * to only the values the user has access to
1418 */
1419 function get_display_category(
1420 p_category_code in varchar2,
1421 p_selected_commodity in varchar2,
1422 p_context_code in varchar2,
1423 p_restrict_lov in varchar2 := 'Y'
1424 ) return varchar2
1425 is
1426 l_exists number;
1427 begin
1428 l_exists := 0;
1429 if(p_restrict_lov = 'N' and p_context_code = 'OU/COM') then
1430 return '1';
1431 end if;
1432
1433 if(p_context_code = 'OUX') then
1434 return '1';
1435 end if;
1436
1437 if(p_context_code = 'SUPPLIER' or p_context_code='OU' or p_context_code = 'COMP') then
1438 if(p_selected_commodity = 'ALL' or p_selected_commodity = '-1') then
1439 return '1';
1440 else
1441 select count(*) into l_exists
1442 from po_commodity_categories cat
1443 where cat.commodity_id = p_selected_commodity
1444 and cat.category_id = p_category_code;
1445 end if;
1446 else
1447 select count(*) into l_exists
1448 from po_commodity_categories cat,
1449 po_commodity_grants gr,
1450 fnd_user usr
1451 where usr.user_id = fnd_global.user_id
1452 and gr.person_id = usr.employee_id
1453 and cat.commodity_id = gr.commodity_id
1454 and cat.category_id = p_category_code
1455 and (to_char(gr.commodity_id) = p_selected_commodity or p_selected_commodity = 'ALL');
1456 end if;
1457
1458 if(l_exists = 0) then
1459 return '0';
1460 else
1461 return '1';
1462 end if;
1463 exception
1464 when others then
1465 return '2';
1466 end get_display_category;
1467
1468 /* Function name: get_display_supplier
1469 *
1470 * This function is used to restrict the values in the supplier LOV
1471 * to only the values the user has access to
1472 */
1473 function get_display_supplier(p_supplier_id in varchar2,
1474 p_context_code in varchar2
1475 ) return varchar2
1476 is
1477 l_exists number;
1478 begin
1479 -- Non-Supplier View
1480 if(p_context_code <> 'SUPPLIER') then
1481 return '1';
1482 end if;
1483
1484 -- Supplier View of Supplier Management Page
1485 l_exists := 0;
1486 /* Check if user has access to p_supplier_id based on iSP Securing Attributes */
1487 select count(*) into l_exists
1488 from ak_web_user_sec_attr_values isp,
1489 fnd_application appl
1490 where isp.web_user_id = fnd_global.user_id
1491 and isp.attribute_application_id = appl.application_id
1492 and appl.application_short_name = 'POS'
1493 and isp.attribute_code = 'ICX_SUPPLIER_ORG_ID'
1494 and isp.number_value = p_supplier_id;
1495
1496 if(l_exists = 0) then
1497 return '0';
1498 else
1499 return '1';
1500 end if;
1501
1502 exception
1503 when others then
1504 return '2';
1505 end get_display_supplier;
1506
1507 /* Function name: get_display_supplier_sites
1508 *
1509 * This function is used to restrict the values in the supplier site LOV
1510 * to only the values the user has access to
1511 */
1512 function get_display_supplier_site(p_supplier_site_id in varchar2,
1513 p_context_code in varchar2
1514 ) return varchar2
1515 is
1516 l_exists number;
1517 begin
1518 -- Non-Supplier View
1519 if(p_context_code <> 'SUPPLIER') then
1520 return '1';
1521 end if;
1522
1523 -- Supplier View of Supplier Management Page
1524 l_exists := 0;
1525 /* Check if user has access to p_supplier_id based on iSP Securing Attributes */
1526 select count(*)
1527 into l_exists
1528 from
1529 poa_supplier_sites_v sup,
1530 ak_web_user_sec_attr_values isp,
1531 fnd_application appl
1532 where isp.attribute_application_id = appl.application_id
1533 and isp.number_value = sup.vendor_id
1534 and isp.web_user_id = fnd_global.user_id
1535 and isp.attribute_code = 'ICX_SUPPLIER_ORG_ID'
1536 and to_char(sup.id) = p_supplier_site_id
1537 and appl.application_short_name = 'POS';
1538
1539 if(l_exists = 0) then
1540 return '0';
1541 else
1542 return '1';
1543 end if;
1544
1545 exception
1549
1546 when others then
1547 return '2';
1548 end get_display_supplier_site;
1550
1551 /* Function name: get_display_ou
1552 *
1553 * This function is used to restrict the values in the OU LOV
1554 * to only the values the user has access to
1555 */
1556 function get_display_ou(p_ou_id in varchar2,
1557 p_context_code in varchar2
1558 ) return varchar2
1559 is
1560 l_exists number;
1561 l_sec_profile_id number ;
1562 begin
1563 -- for Supplier Management and Spend Analysis dashboards
1564 if(p_context_code = 'SUPPLIER' or p_context_code = 'COMP') then
1565 return '1';
1566 end if;
1567 ---Begin changes for MOAC
1568 l_sec_profile_id := poa_dbi_util_pkg.get_sec_profile ;
1569 ---End changes for MOAC
1570
1571 -- for OU Secured dashboards
1572 l_exists := 0;
1573 ---Begin changes for MOAC
1574 IF NVL(l_sec_profile_id,-1) <> -1
1575 THEN
1576 ---End changes for MOAC
1577 select count(*) into l_exists
1578 from fii_operating_units_v v,
1579 per_organization_list per
1580 where v.id = p_ou_id
1581 and v.id = per.organization_id
1582 and security_profile_id = l_sec_profile_id ;
1583 ---Begin changes for MOAC
1584 ELSE
1585 SELECT COUNT(*)
1586 INTO l_exists
1587 FROM fii_operating_units_v v
1588 WHERE v.id = p_ou_id
1589 AND v.id = poa_dbi_util_pkg.get_ou_org_id ;
1590 END IF ;
1591 ---End changes for MOAC
1592
1593 if(l_exists = 0) then
1594 return '0';
1595 else
1596 return '1';
1597 end if;
1598
1599 exception
1600 when others then
1601 return '2';
1602 end get_display_ou;
1603
1604 /* Function name: get_display_com
1605 *
1606 * This function is used to restrict the values in the company LOV
1607 * to only the values the user has access to and enforce the standard
1608 * hierarchical LOV behaviour
1609 */
1610 function get_display_com(p_id in varchar2, p_parent_id in varchar2, p_selected_company in varchar2) return varchar2
1611 is
1612 l_count number;
1613 begin
1614 if (p_selected_company = 'ALL') then
1615 if (p_id <> p_parent_id) then
1616 /* we want to consider only those rows that represent a
1617 * self relation
1618 */
1619 return '0';
1620 else
1621 select count(*) into l_count
1622 from fii_company_grants
1623 where user_id = fnd_global.user_id
1624 and company_id = to_number(p_id)
1625 and report_region_code = 'POA_DBI_INV_STATUS';
1626 /* we are using Invoice Amount report as a reference to determine the
1627 * companies the user has access to
1628 */
1629
1630 if (l_count > 0) then
1631 return '1';
1632 else
1633 return '0';
1634 end if;
1635
1636 end if;
1637 else /* p_selected_company <> 'ALL' */
1638 if (p_parent_id = p_selected_company) then
1639 return '1';
1640 else
1641 return '0';
1642 end if;
1643 end if;
1644 end get_display_com;
1645
1646 /* Function name: get_display_cc
1647 *
1648 * This function is used to restrict the values in the cost-center LOV
1649 * to only the values the user has access to and enforce the standard
1650 * hierarchical LOV behaviour
1651 */
1652 function get_display_cc(p_id in varchar2, p_parent_id in varchar2, p_selected_cc in varchar2) return varchar2
1653 is
1654 l_count number;
1655 begin
1656 if (p_selected_cc = 'ALL') then
1657 if (p_id <> p_parent_id) then
1658 /* we want to consider only those rows that represent a
1659 * self relation
1660 */
1661 return '0';
1662 else
1663 select count(*) into l_count
1664 from fii_cost_center_grants
1665 where user_id = fnd_global.user_id
1666 and cost_center_id = to_number(p_id)
1667 and report_region_code = 'POA_DBI_INV_STATUS';
1668 /* we are using Invoice Amount report as a reference to determine the
1669 * cost centers the user has access to
1670 */
1671
1672 if (l_count > 0) then
1673 return '1';
1674 else
1675 return '0';
1676 end if;
1677
1678 end if;
1679 else
1680 if (p_parent_id = p_selected_cc) then
1681 return '1';
1682 else
1683 return '0';
1684 end if;
1685 end if;
1686 end get_display_cc;
1687
1688 /* Function name: get_display_commodity
1689 *
1690 * This function is used to restrict the values in the commodity LOV
1691 * to only the values the user has access to in case of dashboards that
1692 * are secured by commodity
1693 */
1694 function get_display_commodity(p_commodity_id in varchar2,
1695 p_context_code in varchar2
1696 ) return varchar2
1697 is
1698 l_exists NUMBER;
1699 begin
1700 -- for Supplier Management and Spend Analysis dashboard
1701 if(p_context_code = 'SUPPLIER' or p_context_code='OU' or p_context_code = 'COMP') then
1702 return '1';
1703 end if;
1704
1705 -- for Commodity Secured dashboards
1706 l_exists := 0;
1707 /* select count(*) into l_exists
1708 from
1709 po_commodity_grants sec,
1710 fnd_user,
1711 fnd_menu_entries me,
1712 fnd_form_functions f
1713 where
1717 and f.function_name='POA_DBI_COMMODITY_RPTS_VIEW'
1714 user_id=fnd_global.user_id
1715 and person_id=employee_id
1716 and sec.commodity_id=p_commodity_id
1718 and me.function_id=f.function_id
1719 and sec.menu_id=me.menu_id; */
1720 select count(*) into l_exists
1721 from po_commodity_grants sec,
1722 fnd_menus menu,
1723 fnd_user usr
1724 where usr.user_id = fnd_global.user_id
1725 and sec.person_id = usr.employee_id
1726 and sec.commodity_id = p_commodity_id
1727 and menu.menu_name = 'PO_COMMODITY_MANAGER'
1728 and sec.menu_id = menu.menu_id;
1729
1730 if(l_exists = 0) then
1731 return '0';
1732 else
1733 return '1';
1734 end if;
1735
1736 exception
1737 when others then
1738 return '2';
1739 end get_display_commodity;
1740
1741 procedure hide_parameter(p_param in bis_pmv_page_parameter_tbl, hideParameter out nocopy varchar2)
1742 is
1743 l_bis_calling_parameter varchar2(100) := 'NA';
1744 l_context_code varchar2(10) := 'OU/COM';
1745 l_flag varchar2(1) := 'N';
1746 l_region_code varchar2(100) := 'NA';
1747 begin
1748 l_context_code := get_sec_context(p_param);
1749 l_bis_calling_parameter := get_bis_calling_parameter(p_param);
1750 for i in 1..p_param.count loop
1751 if (p_param(i).parameter_name = 'BIS_REGION_CODE') then
1752 l_region_code := p_param(i).parameter_value;
1753 end if;
1754 end loop;
1755 if(l_context_code = 'OU' or l_context_code = 'SUPPLIER') then
1756 if(l_bis_calling_parameter IN ('TIME+FII_TIME_DAY', 'TIME+FII_TIME_WEEK', 'TIME+FII_TIME_ENT_PERIOD',
1757 'TIME+FII_TIME_ENT_QTR','TIME+FII_TIME_ENT_YEAR')) then
1758 l_flag := 'Y';
1759 end if;
1760 if(l_region_code = 'POA_DBI_SPND_TREND' AND l_bis_calling_parameter = 'SUPPLIER+POA_SUPPLIER_SITES') THEN
1761 l_flag := 'Y';
1762 end if;
1763 end if;
1764 if(l_context_code = 'OU/COM' or l_context_code = 'COMP' or l_context_code = 'NEG'
1765 OR l_context_code = 'OUX'
1766 ) then
1767 if(l_bis_calling_parameter IN ('TIME+FII_ROLLING_WEEK', 'TIME+FII_ROLLING_MONTH',
1768 'TIME+FII_ROLLING_QTR','TIME+FII_ROLLING_YEAR')) then
1769 l_flag := 'Y';
1770 end if;
1771 end if;
1772 hideParameter := l_flag;
1773 end hide_parameter;
1774
1775 /* Procedure name: hide_parameter2
1776 *
1777 * This procedure is called by PMV for company, cost-center and FUD1
1778 * dimensions to determine whether they should be hidden or not. This
1779 * procedure returns a 'N' (meaning show) if the context code is COMP. Hence the three
1780 * dimensions are hidden on the Commodity Spend Analysis dashboard but
1781 * shown on the Spend Analysis (Public Sector) dashboard.
1782 */
1783 procedure hide_parameter2(p_param in bis_pmv_page_parameter_tbl, hideParameter out nocopy varchar2)
1784 is
1785 l_context_code varchar2(10) := 'OU/COM';
1786 l_flag varchar2(1) := 'Y';
1787 begin
1788 l_context_code := get_sec_context(p_param);
1789 if(l_context_code='COMP') then
1790 l_flag := 'N';
1791 else
1792 l_flag := 'Y';
1793 end if;
1794 hideParameter := l_flag;
1795 end hide_parameter2;
1796
1797 /* Procedure name: hide_parameter3
1798 *
1799 * This procedure is called by PMV for organization, buyer and supplier-site
1800 * dimensions to determine whether they should be hidden or not. This
1801 * procedure returns a 'Y' (meaning hide) if the context code is COMP. Hence the three
1802 * dimensions are shown on the Commodity Spend Analysis dashboard but
1803 * hidden on the Spend Analysis (Public Sector) dashboard.
1804 */
1805 procedure hide_parameter3(p_param in bis_pmv_page_parameter_tbl, hideParameter out nocopy varchar2)
1806 is
1807 l_context_code varchar2(10) := 'OU/COM';
1808 l_bis_calling_parameter varchar2(100);
1809 l_flag varchar2(1) := 'Y';
1810 begin
1811 l_context_code := get_sec_context(p_param);
1812 l_bis_calling_parameter := get_bis_calling_parameter(p_param);
1813 if(l_context_code='COMP') then
1814 l_flag := 'Y';
1815 else
1816 l_flag := 'N';
1817 end if;
1818 if(l_context_code = 'NEG' and l_bis_calling_parameter = 'SUPPLIER+POA_SUPPLIER_SITES') then
1819 l_flag := 'Y';
1820 end if;
1821
1822 hideParameter := l_flag;
1823 end hide_parameter3;
1824
1825 /* Procedure name: hide_commodity
1826 *
1827 * This procedure is called by PMV for the commodity region item of PO Purchases
1828 * report to determine whether the parameter should be hidden or not.
1829 */
1830 procedure hide_commodity(p_param in bis_pmv_page_parameter_tbl, hideParameter out nocopy varchar2)
1831 is
1832 l_function_name varchar2(30);
1833 l_bis_calling_parameter varchar2(100) := 'NA';
1834 l_context_code varchar2(10) := 'OU/COM';
1835 l_flag varchar2(1) := 'N';
1836 begin
1837 l_context_code := get_sec_context(p_param);
1838 l_bis_calling_parameter := get_bis_calling_parameter(p_param);
1839 if(l_context_code = 'OUX' and l_bis_calling_parameter = 'ITEM+POA_COMMODITIES') then
1840 l_flag := 'Y';
1841 else
1842 l_flag := 'N';
1843 end if;
1844 hideParameter := l_flag;
1845 end hide_commodity;
1846
1847 /* Procedure name: get_company_sql
1848 *
1849 * This procedure determines the subquery for the company dimension
1850 * which is used by the reports of the Spend Analysis dashboard. It takes
1851 * the report viewby and the selected company as input and returns the
1852 * subquery and a flag which indicates whether all the nodes accessed are
1853 * aggregated or not.
1854 */
1858 p_company_sql out nocopy varchar2,
1855 procedure get_company_sql(p_viewby in varchar2,
1856 p_company_id in varchar2,
1857 p_region_code in varchar2,
1859 p_agg_flag out nocopy varchar2)
1860 is
1861 l_leaf_flag varchar2(1);
1862 l_viewby_sql varchar2(1000);
1863 l_company_count number;
1864 l_rtn varchar2(5);
1865 l_top_node varchar2(20);
1866 l_non_agrt_nodes number;
1867 begin
1868 l_rtn := fnd_global.newline;
1869 l_non_agrt_nodes := 0;
1870
1871 if(p_viewby <> 'FII_COMPANIES+FII_COMPANIES') then
1872 if(p_company_id = 'All') then
1873 l_viewby_sql := 'select company_id, aggregated_flag com_agg_flag '||l_rtn||
1874 'from fii_company_grants '||l_rtn||
1875 'where user_id = fnd_global.user_id '||l_rtn||
1876 'and report_region_code = ®IONCODE';
1877 select count(*) into l_non_agrt_nodes
1878 from fii_company_grants
1879 where user_id = fnd_global.user_id
1880 and report_region_code = p_region_code
1881 and aggregated_flag = 'N';
1882 else
1883 l_viewby_sql := 'select company_id, aggregated_flag com_agg_flag '||l_rtn||
1884 'from fii_com_pmv_agrt_nodes '||l_rtn||
1885 'where company_id = &COMPANYID';
1886 select count(*) into l_non_agrt_nodes
1887 from fii_com_pmv_agrt_nodes
1888 where company_id = to_number(p_company_id)
1889 and aggregated_flag = 'N';
1890 end if;
1891 else
1892 if(p_company_id = 'All') then
1893
1894 select count(1) into l_company_count
1895 from fii_company_grants
1896 where user_id = fnd_global.user_id
1897 and report_region_code = p_region_code;
1898
1899 if(l_company_count = 1) then
1900
1901 select to_char(company_id) into l_top_node
1902 from fii_company_grants
1903 where user_id = fnd_global.user_id
1904 and report_region_code = p_region_code;
1905
1906 select is_leaf_flag into l_leaf_flag
1907 from fii_company_hierarchies
1908 where parent_company_id = to_number(l_top_node)
1909 and parent_company_id = child_company_id;
1910
1911 if(l_leaf_flag = 'Y') then
1912 l_viewby_sql := 'select company_id, aggregated_flag com_agg_flag '||l_rtn||
1913 'from fii_com_pmv_agrt_nodes '||l_rtn||
1914 'where company_id = &TOPCOMPANYID';
1915 select count(*) into l_non_agrt_nodes
1916 from fii_com_pmv_agrt_nodes
1917 where company_id = l_top_node
1918 and aggregated_flag = 'N';
1919 else
1920 l_viewby_sql := 'select parent_company_id, child_company_id company_id, aggregate_next_level_flag com_agg_flag '||l_rtn||
1921 'from fii_company_hierarchies '||l_rtn||
1922 'where child_level = parent_level+1 '||l_rtn||
1923 'and parent_company_id = &TOPCOMPANYID';
1924 select count(*) into l_non_agrt_nodes
1925 from fii_company_hierarchies
1926 where child_level = parent_level + 1
1927 and parent_company_id = l_top_node
1928 and aggregate_next_level_flag = 'N';
1929 end if;
1930 else
1931 l_viewby_sql := 'select company_id, aggregated_flag com_agg_flag '||l_rtn||
1932 'from fii_company_grants '||l_rtn||
1933 'where user_id = fnd_global.user_id '||l_rtn||
1934 'and report_region_code = ®IONCODE';
1935 select count(*) into l_non_agrt_nodes
1936 from fii_company_grants
1937 where user_id = fnd_global.user_id
1938 and report_region_code = p_region_code
1939 and aggregated_flag = 'N';
1940 end if;
1941 else
1942 select is_leaf_flag into l_leaf_flag
1943 from fii_company_hierarchies
1944 where parent_company_id = to_number(p_company_id)
1945 and parent_company_id = child_company_id;
1946 if(l_leaf_flag = 'Y') then
1947 l_viewby_sql := 'select company_id, aggregated_flag com_agg_flag '||l_rtn||
1948 'from fii_com_pmv_agrt_nodes '||l_rtn||
1949 'where company_id = &COMPANYID';
1950 select count(*) into l_non_agrt_nodes
1951 from fii_com_pmv_agrt_nodes
1952 where company_id = to_number(p_company_id)
1953 and aggregated_flag = 'N';
1954 else
1955 l_viewby_sql := 'select parent_company_id, child_company_id company_id, aggregate_next_level_flag com_agg_flag '||l_rtn||
1956 'from fii_company_hierarchies '||l_rtn||
1957 'where child_level = parent_level+1 '||l_rtn||
1958 'and parent_company_id = &COMPANYID';
1959 select count(*) into l_non_agrt_nodes
1960 from fii_company_hierarchies
1961 where child_level = parent_level + 1
1962 and parent_company_id = p_company_id
1963 and aggregate_next_level_flag = 'N';
1964 end if;
1965 end if;
1966 end if;
1967 p_company_sql := l_viewby_sql;
1968 if(l_non_agrt_nodes = 0) then
1969 p_agg_flag := 'Y';
1970 else
1971 p_agg_flag := 'N';
1972 end if;
1973 end;
1974
1975 /* Procedure name: get_cost_ctr_sql
1976 *
1977 * This procedure determines the subquery for the cost center dimension
1978 * which is used by the reports of the Spend Analysis dashboard. It takes
1979 * the report viewby and the selected cost-center as input and returns the
1983 procedure get_cost_ctr_sql(p_viewby in varchar2,
1980 * subquery and a flag which indicates whether all the nodes accessed are
1981 * aggregated or not.
1982 */
1984 p_cost_center_id in varchar2,
1985 p_region_code in varchar2,
1986 p_cost_ctr_sql out nocopy varchar2,
1987 p_agg_flag out nocopy varchar2)
1988 is
1989 l_leaf_flag varchar2(1);
1990 l_viewby_sql varchar2(1000);
1991 l_cost_center_count number;
1992 l_rtn varchar2(5);
1993 l_top_node varchar2(20);
1994 l_non_agrt_nodes number;
1995 begin
1996 l_rtn := fnd_global.newline;
1997 l_non_agrt_nodes := 0;
1998
1999 if(p_viewby <> 'ORGANIZATION+HRI_CL_ORGCC') then
2000 if(p_cost_center_id = 'All') then
2001 l_viewby_sql := 'select cost_center_id, aggregated_flag cc_agg_flag '||l_rtn||
2002 'from fii_cost_center_grants '||l_rtn||
2003 'where user_id = fnd_global.user_id '||l_rtn||
2004 'and report_region_code = ®IONCODE';
2005 select count(*) into l_non_agrt_nodes
2006 from fii_cost_center_grants
2007 where user_id = fnd_global.user_id
2008 and report_region_code = p_region_code
2009 and aggregated_flag = 'N';
2010 else
2011 l_viewby_sql := 'select cost_center_id, aggregated_flag cc_agg_flag '||l_rtn||
2012 'from fii_cc_pmv_agrt_nodes '||l_rtn||
2013 'where cost_center_id = &COSTCTRID';
2014 select count(*) into l_non_agrt_nodes
2015 from fii_cc_pmv_agrt_nodes
2016 where cost_center_id = p_cost_center_id
2017 and aggregated_flag = 'N';
2018 end if;
2019 else
2020 if(p_cost_center_id = 'All') then
2021
2022 select count(1) into l_cost_center_count
2023 from fii_cost_center_grants
2024 where user_id = fnd_global.user_id
2025 and report_region_code = p_region_code;
2026
2027 if(l_cost_center_count = 1) then
2028
2029 select to_char(cost_center_id) into l_top_node
2030 from fii_cost_center_grants
2031 where user_id = fnd_global.user_id
2032 and report_region_code = p_region_code;
2033
2034 select is_leaf_flag into l_leaf_flag
2035 from fii_cost_ctr_hierarchies
2036 where parent_cc_id = to_number(l_top_node)
2037 and parent_cc_id = child_cc_id;
2038
2039 if(l_leaf_flag = 'Y') then
2040 l_viewby_sql := 'select cost_center_id, aggregated_flag cc_agg_flag '||l_rtn||
2041 'from fii_cc_pmv_agrt_nodes '||l_rtn||
2042 'where cost_center_id = &TOPCOSTCTRID';
2043 select count(*) into l_non_agrt_nodes
2044 from fii_cc_pmv_agrt_nodes
2045 where cost_center_id = l_top_node
2046 and aggregated_flag = 'N';
2047 else
2048 l_viewby_sql := 'select parent_cc_id, child_cc_id cost_center_id, aggregate_next_level_flag cc_agg_flag '||l_rtn||
2049 'from fii_cost_ctr_hierarchies '||l_rtn||
2050 'where child_level = parent_level+1 '||l_rtn||
2051 'and parent_cc_id = &TOPCOSTCTRID';
2052 select count(*) into l_non_agrt_nodes
2053 from fii_cost_ctr_hierarchies
2054 where child_level = parent_level + 1
2055 and parent_cc_id = l_top_node
2056 and aggregate_next_level_flag = 'N';
2057 end if;
2058 else
2059 l_viewby_sql := 'select cost_center_id, aggregated_flag cc_agg_flag '||l_rtn||
2060 'from fii_cost_center_grants '||l_rtn||
2061 'where user_id = fnd_global.user_id '||l_rtn||
2062 'and report_region_code = ®IONCODE';
2063 select count(*) into l_non_agrt_nodes
2064 from fii_cost_center_grants
2065 where user_id = fnd_global.user_id
2066 and report_region_code = p_region_code
2067 and aggregated_flag = 'N';
2068 end if;
2069 else
2070 select is_leaf_flag into l_leaf_flag
2071 from fii_cost_ctr_hierarchies
2072 where parent_cc_id = p_cost_center_id
2073 and parent_cc_id = child_cc_id;
2074 if(l_leaf_flag = 'Y') then
2075 l_viewby_sql := 'select cost_center_id, aggregated_flag cc_agg_flag '||l_rtn||
2076 'from fii_cc_pmv_agrt_nodes '||l_rtn||
2077 'where cost_center_id = &COSTCTRID';
2078 select count(*) into l_non_agrt_nodes
2079 from fii_cc_pmv_agrt_nodes
2080 where cost_center_id = p_cost_center_id
2081 and aggregated_flag = 'N';
2082 else
2083 l_viewby_sql := 'select parent_cc_id, child_cc_id cost_center_id, aggregate_next_level_flag cc_agg_flag '||l_rtn||
2084 'from fii_cost_ctr_hierarchies '||l_rtn||
2085 'where child_level = parent_level+1 '||l_rtn||
2086 'and parent_cc_id = &COSTCTRID';
2087 select count(*) into l_non_agrt_nodes
2088 from fii_cost_ctr_hierarchies
2089 where child_level = parent_level + 1
2090 and parent_cc_id = p_cost_center_id
2091 and aggregate_next_level_flag = 'N';
2092 end if;
2093 end if;
2094 end if;
2095 p_cost_ctr_sql := l_viewby_sql;
2096 if(l_non_agrt_nodes = 0) then
2097 p_agg_flag := 'Y';
2098 else
2099 p_agg_flag := 'N';
2100 end if;
2101 end;
2102
2103 /* function name: get_sec_context
2104 *
2105 * This function takes the parameter table passed by PMV as input as returns
2109 function get_sec_context(p_param in BIS_PMV_PAGE_PARAMETER_TBL) return varchar2
2106 * the context code. The context code is the value associated with the
2107 * POA_CONTEXT1 parameter
2108 */
2110 is
2111 l_value varchar2(10) := 'OU/COM';
2112 begin
2113 for i in 1..p_param.count loop
2114 if(p_param(i).parameter_name = 'POA_CONTEXT1' and p_param(i).parameter_id is not null) then
2115 l_value := p_param(i).parameter_value;
2116 end if;
2117 end loop;
2118 IF(l_value is NULL or l_value = 'ALL') THEN
2119 l_value := 'OU/COM';
2120 END IF;
2121 return (l_value);
2122 end;
2123
2124 /* function name: get_bis_calling_parameter
2125 *
2126 * This function takes the parameter table passed by PMV as input and returns
2127 * the calling parameter string. The calling parameter is the value associated with the
2128 * BIS_CALLING_PARAMETER parameter. This is available only in Show/Hide
2129 * Function
2130 */
2131 function get_bis_calling_parameter(p_param in BIS_PMV_PAGE_PARAMETER_TBL) return varchar2
2132 is
2133 l_value varchar2(100);
2134 begin
2135 for i in 1..p_param.count loop
2136 if(p_param(i).parameter_name = 'BIS_CALLING_PARAMETER' and p_param(i).parameter_id is not null) then
2137 l_value := p_param(i).parameter_value;
2138 end if;
2139 end loop;
2140 return (l_value);
2141 end;
2142
2143 /* function name: get_supplier_id_ou
2144 *
2145 * This function is used by the OU context page, wherein the Supplier LOV in the Supplier
2146 * Management Dashboard should not be secured. It must pick up the first alphabetical
2147 * Supplier Value and must pass it back to the calling function.
2148 */
2149 function get_supplier_id_ou return varchar2
2150 is
2151 l_supplier_id number;
2152 l_return_string varchar2(100);
2153 begin
2154 SELECT id into l_supplier_id
2155 FROM (
2156 SELECT id
2157 FROM poa_suppliers_v
2158 ORDER BY value)
2159 WHERE ROWNUM=1;
2160 l_return_string := '&POA_SUPPLIERS='||l_supplier_id;
2161 return l_return_string;
2162 end;
2163
2164 /* function name: get_supplier_id_sup
2165 *
2166 * This function is used by the Supplier context page, wherein the Supplier LOV in the Supplier
2167 * Management Dashboard should be secured. It must pick up the first alphabetical
2168 * Supplier Value and must pass it back to the calling function.
2169 */
2170 function get_supplier_id_sup return varchar2
2171 is
2172 l_supplier_id number;
2173 l_return_string varchar2(100);
2174 begin
2175 SELECT id into l_supplier_id
2176 FROM (
2177 SELECT id
2178 FROM
2179 poa_suppliers_v v,
2180 ak_web_user_sec_attr_values isp,
2181 fnd_application appl
2182 WHERE
2183 fnd_global.user_id = isp.web_user_id
2184 AND isp.attribute_application_id = appl.application_id
2185 AND appl.application_short_name = 'POS'
2186 AND isp.attribute_code = 'ICX_SUPPLIER_ORG_ID'
2187 AND v.id = isp.number_value
2188 ORDER BY value)
2189 WHERE ROWNUM=1;
2190 l_return_string := '&POA_SUPPLIERS='||l_supplier_id;
2191 return l_return_string;
2192 end;
2193
2194 /* function name: get_curr_label
2195 *
2196 * This function is called by POA_DBI_PQC_TREND ak region to get the column
2197 * heading suffix for current period columns
2198 */
2199 function get_curr_label return varchar2 is
2200 stmt varchar2(240);
2201 begin
2202 stmt := get_msg('Y');
2203 return stmt;
2204 end get_curr_label;
2205
2206 /* function name: get_pri_label
2207 *
2208 * This function is called by POA_DBI_PQC_TREND ak region to get the column
2209 * heading suffix for prior period columns
2210 */
2211 function get_pri_label return varchar2 is
2212 stmt varchar2(240);
2213 begin
2214 stmt := get_msg('N');
2215 return stmt;
2216 end get_pri_label;
2217
2218 /* function name: get_msg
2219 *
2220 * This function is called by get_curr_label and get_pri_label to get the
2221 * label to be displayed in Cumulative price savings report's column headings
2222 */
2223 function get_msg (p_current in varchar2)return varchar2 is
2224 stmt varchar2(240);
2225 l_asof_date date;
2226 l_week varchar2(10);
2227 l_year varchar2(10);
2228 begin
2229 if (p_current = 'Y') then
2230 l_asof_date:=g_as_of_date;
2231 else
2232 l_asof_date:=g_previous_asof_date;
2233 end if;
2234
2235 if g_page_period_type = 'FII_TIME_ENT_YEAR' then
2236 select name into stmt
2237 from fii_time_ent_year
2238 where l_asof_date between start_date and end_date;
2239 elsif g_page_period_type = 'FII_TIME_ENT_QTR' then
2240 select name into stmt
2241 from fii_time_ent_qtr
2242 where l_asof_date between start_date and end_date;
2243 elsif g_page_period_type = 'FII_TIME_ENT_PERIOD' then
2244 select name into stmt
2245 from fii_time_ent_period
2246 where l_asof_date between start_date and end_date;
2247 elsif g_page_period_type = 'FII_TIME_WEEK' then
2248 select to_char(sequence) into l_week
2249 from fii_time_week
2250 where l_asof_date between start_date and end_date;
2251
2252 select substr(week_id,3,2) into l_year
2253 from fii_time_week
2254 where l_asof_date between start_date and end_date;
2255
2256 stmt := fnd_message.get_string('FII', 'FII_AR_WEEK')||' '||l_week||' '||l_year;
2257 end if;
2258 return stmt;
2259 end get_msg;
2260
2264 * of three global variables which are used by get_msg function
2261 /* function name: get_parameters
2262 *
2263 * This procedure is called by poa_dbi_pqc_pkg.trend_sql to set the values
2265 */
2266 procedure get_parameters (p_page_parameter_tbl in bis_pmv_page_parameter_tbl) is
2267 l_lob_enabled_flag varchar2(1);
2268 begin
2269 -- -------------------------------------------------
2270 -- Parse through the parameter table and set globals
2271 -- -------------------------------------------------
2272 if (p_page_parameter_tbl.count > 0) then
2273 for i in p_page_parameter_tbl.first..p_page_parameter_tbl.last loop
2274 if p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' then
2275 g_page_period_type := p_page_parameter_tbl(i).parameter_value;
2276 elsif p_page_parameter_tbl(i).parameter_name = 'AS_OF_DATE' then
2277 g_as_of_date := to_date(p_page_parameter_tbl(i).parameter_value,'DD-MM-YYYY');
2278 elsif p_page_parameter_tbl(i).parameter_name = 'BIS_PREVIOUS_ASOF_DATE' then
2279 g_previous_asof_date := to_date(p_page_parameter_tbl(i).parameter_value,'DD-MM-YYYY');
2280 end if;
2281 end loop;
2282 end if;
2283
2284 if g_as_of_date is null then
2285 g_as_of_date := trunc(sysdate);
2286 end if;
2287
2288 if g_previous_asof_date is null then
2289 g_previous_asof_date := trunc(sysdate);
2290 end if;
2291
2292 if g_page_period_type is null then
2293 g_page_period_type := 'FII_TIME_ENT_QTR';
2294 end if;
2295
2296 end get_parameters;
2297
2298 /* procedure name: bind_com_cc_values
2299 *
2300 * This procedure populates x_custom_output with five binds that are
2301 * required for reports having company and cost center security.
2302 */
2303 procedure bind_com_cc_values(
2304 x_custom_output in out nocopy bis_query_attributes_tbl,
2305 p_param in bis_pmv_page_parameter_tbl
2306 )
2307 is
2308 l_company_value varchar2(30) := 'All';
2309 l_top_company_value varchar2(30) := 'All';
2310 l_cost_ctr_value varchar2(30) := 'All';
2311 l_top_cost_ctr_value varchar2(30) := 'All';
2312 l_region_code varchar2(40);
2313 l_custom_rec BIS_QUERY_ATTRIBUTES;
2314 l_count number;
2315 begin
2316
2317 for i in 1..p_param.count loop
2318 if (p_param(i).parameter_name = 'BIS_REGION_CODE') then
2319 l_region_code := p_param(i).parameter_value;
2320 elsif (p_param(i).parameter_name = 'FII_COMPANIES+FII_COMPANIES') then
2321 l_company_value := nvl(p_param(i).parameter_id,'All');
2322 elsif (p_param(i).parameter_name = 'ORGANIZATION+HRI_CL_ORGCC') then
2323 l_cost_ctr_value := nvl(p_param(i).parameter_id,'All');
2324 end if;
2325 end loop;
2326
2327 if (l_company_value = '''''')then
2328 l_company_value := 'All';
2329 end if;
2330
2331 if (l_cost_ctr_value = '''''')then
2332 l_cost_ctr_value := 'All';
2333 end if;
2334
2335 l_company_value := translate(l_company_value,'''',' ');
2336 l_cost_ctr_value := translate(l_cost_ctr_value,'''',' ');
2337
2338 /* check how many companies the user has access to
2339 */
2340 select count(1) into l_count
2341 from fii_company_grants
2342 where user_id = fnd_global.user_id
2343 and report_region_code = l_region_code;
2344
2345 if (l_count = 1) then
2346 select to_char(company_id) into l_top_company_value
2347 from fii_company_grants
2348 where user_id = fnd_global.user_id
2349 and report_region_code = l_region_code;
2350 else
2351 /* if the user does not have access to exactly one company, then the
2352 * l_top_company_value should have an unused value
2353 */
2354 l_top_company_value := '-9999';
2355 end if;
2356
2357 /* check how many cost-centers the user has access to
2358 */
2359 select count(1) into l_count
2360 from fii_cost_center_grants
2361 where user_id = fnd_global.user_id
2362 and report_region_code = l_region_code;
2363
2364 if (l_count = 1) then
2365 select to_char(cost_center_id) into l_top_cost_ctr_value
2366 from fii_cost_center_grants
2367 where user_id = fnd_global.user_id
2368 and report_region_code = l_region_code;
2369 else
2370 /* if the user does not have access to exactly one cost-center, then the
2371 * l_top_cost_ctr_value should have an unused value
2372 */
2373 l_top_cost_ctr_value := '-9999';
2374 end if;
2375
2376 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
2377
2378 if x_custom_output is null then
2379 x_custom_output := bis_query_attributes_tbl();
2380 end if;
2381
2382 l_custom_rec.attribute_name := '®IONCODE';
2383 l_custom_rec.attribute_value := l_region_code;
2384 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2385 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.CHARACTER_BIND;
2386 x_custom_output.EXTEND;
2387 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
2388
2389 l_custom_rec.attribute_name := '&COMPANYID';
2390 l_custom_rec.attribute_value := l_company_value;
2391 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2392 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.CHARACTER_BIND;
2393 x_custom_output.EXTEND;
2394 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
2395
2396 l_custom_rec.attribute_name := '&TOPCOMPANYID';
2397 l_custom_rec.attribute_value := l_top_company_value;
2398 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2399 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.CHARACTER_BIND;
2400 x_custom_output.EXTEND;
2401 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
2402
2403 l_custom_rec.attribute_name := '&COSTCTRID';
2404 l_custom_rec.attribute_value := l_cost_ctr_value;
2405 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2406 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.CHARACTER_BIND;
2407 x_custom_output.EXTEND;
2408 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
2409
2410 l_custom_rec.attribute_name := '&TOPCOSTCTRID';
2411 l_custom_rec.attribute_value := l_top_cost_ctr_value;
2412 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2413 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.CHARACTER_BIND;
2414 x_custom_output.EXTEND;
2415 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
2416 end bind_com_cc_values;
2417
2418 end poa_dbi_sutil_pkg;