[Home] [Help]
PACKAGE BODY: APPS.FEM_BI_DIMENSION_UTILS_PKG
Source
1 PACKAGE BODY FEM_BI_DIMENSION_UTILS_PKG AS
2 /* $Header: fem_bi_dim_utils.plb 120.1 2008/02/20 06:54:26 jcliving noship $ */
3
4 -------------------------------
5 -- Declare package constants --
6 -------------------------------
7
8 -- Log Level Constants
9 G_LOG_LEVEL_STATEMENT constant number := FND_LOG.Level_Statement; --1--
10 G_LOG_LEVEL_PROCEDURE constant number := FND_LOG.Level_Procedure; --2--
11 G_LOG_LEVEL_EVENT constant number := FND_LOG.Level_Event; --3--
12 G_LOG_LEVEL_EXCEPTION constant number := FND_LOG.Level_Exception; --4--
13 G_LOG_LEVEL_ERROR constant number := FND_LOG.Level_Error; --5--
14 G_LOG_LEVEL_UNEXPECTED constant number := FND_LOG.Level_Unexpected;--6--
15
16 ------------------------------
17 -- Declare package messages --
18 ------------------------------
19 G_GL_POST_201 constant varchar2(30) := 'FEM_GL_POST_201';
20 G_GL_POST_202 constant varchar2(30) := 'FEM_GL_POST_202';
21 G_GL_POST_203 constant varchar2(30) := 'FEM_GL_POST_203';
22 G_GL_POST_206 constant varchar2(30) := 'FEM_GL_POST_206';
23 G_GL_POST_215 constant varchar2(30) := 'FEM_GL_POST_215';
24
25 G_BI_ATTR_NO_ATTRIBUTES_WRN constant varchar2(30) := 'FEM_BI_ATTR_NO_ATTRIBUTES_WRN';
26
27 G_BI_ATTR_INVALID_DIMENSION constant varchar2(30) := 'FEM_BI_ATTR_INVALID_DIMENSION';
28 G_BI_ATTR_REQ_STATUS constant varchar2(30) := 'FEM_BI_ATTR_REQ_STATUS';
29 G_BI_ATTR_REQ_SUB_FAILURE constant varchar2(30) := 'FEM_BI_ATTR_REQ_SUB_FAILURE';
30 G_BI_ATTR_REQ_SUB_SUCCESS constant varchar2(30) := 'FEM_BI_ATTR_REQ_SUB_SUCCESS';
31
32 --------------------------------------
33 -- Declare package type definitions --
34 --------------------------------------
35
36 -------------------------------
37 -- Declare package variables --
38 -------------------------------
39
40 -- FND_GLOBAL variables
41 g_req_id number;
42 g_user_id number;
43 g_login_id number;
44
45 --------------------------------
46 -- Declare package exceptions --
47 --------------------------------
48
49 -- Materialized View Does Not Exist Exception
50 g_mv_notexists_exception exception;
51 pragma exception_init(g_mv_notexists_exception,-12003);
52
53 -- Materialized View Exists Exception
54 g_mv_exists_exception exception;
55 pragma exception_init(g_mv_exists_exception,-12006);
56
57 -- Materialized View Create Exception
58 g_mv_create_exception exception;
59
60 -----------------------------------------------
61 -- Declare private procedures and functions --
62 -----------------------------------------------
63
64 FUNCTION Transformation (
65 p_dimension_varchar_label in varchar2
66 ,p_build_mode in varchar2 := 'DEFERRED'
67 ,p_refresh_mode in varchar2 := 'COMPLETE'
68 ,p_enable_qrewrite in varchar2 := 'N'
69 ,p_next_extent in varchar2 := '2M'
70 ,p_seed_db_link in varchar2 := null
71 ) RETURN varchar2;
72
73 PROCEDURE Create_MV_Objects (
74 p_attr_mv_name_prefix in varchar2
75 ,p_attr_query in long
76 ,p_attr_vl_query_select in long
77 ,p_member_col in varchar2
78 ,p_member_display_code_col in varchar2
79 ,p_member_name_col in varchar2
80 ,p_value_set_select in varchar2
81 ,p_data_tablespace in varchar2
82 ,p_index_tablespace in varchar2
83 ,p_storage in varchar2
84 ,p_build_mode in varchar2
85 ,p_refresh_mode in varchar2
86 ,p_enable_qrewrite in varchar2
87 );
88
89 PROCEDURE Get_Dim_Attribute_Sql (
90 p_dimension_id in number
91 ,p_attribute_table_name in varchar2
92 ,p_member_col in varchar2
93 ,p_value_set_required_flag in varchar2
94 ,x_attrd_attr_select out nocopy long
95 ,x_attrn_attr_select out nocopy long
96 ,x_attrn_vl_attr_select out nocopy long
97 );
98
99 PROCEDURE Get_Seed_Dim_Attribute_Sql (
100 p_dimension_id in number
101 ,p_attribute_table_name in varchar2
102 ,p_member_col in varchar2
103 ,p_value_set_required_flag in varchar2
104 ,p_seed_db_link in varchar2
105 ,x_attrd_attr_select out nocopy long
106 ,x_attrn_attr_select out nocopy long
107 ,x_attrn_vl_attr_select out nocopy long
108 );
109
110 --------------------------------------------------------------------------------
111 -- Package bodies for functions/procedures
112 --------------------------------------------------------------------------------
113
114 -- Public Function and Procedure Bodies ---------------------------------------
115
116 /*===========================================================================+
117 | PROCEDURE
118 | Run_Transformation
119 |
120 | DESCRIPTION
121 | Runs Attribute Transformation for all supported dimensions
122 |
123 | SCOPE - PUBLIC
124 |
125 | ARGUMENTS
126 | x_errbuf Standard Concurrent Program parameter
127 | x_retcode Standard Concurrent Program parameter
128 | p_dimension_varchar_label Dimension Varchar Label
129 | p_seed_db_link Seed DB Link (INTERNAL USE ONLY)
130 +===========================================================================*/
131
132 PROCEDURE Run_Transformation (
133 x_errbuf out nocopy varchar2
134 ,x_retcode out nocopy varchar2
135 ,p_dimension_varchar_label in varchar2
136 ,p_seed_db_link in varchar2 := null
137 ) IS
138
139 -----------------------
140 -- Declare constants --
141 -----------------------
142 l_api_name constant varchar2(100) := 'Run_Transformation';
143
144 ---------------------
145 -- Declare cursors --
146 ---------------------
147 cursor l_bi_attr_dims_csr is
148 select *
149 from fem_bi_attr_dimensions_v
150 where dimension_varchar_label <> 'ALL'
151 order by dimension_varchar_label;
152
153 cursor l_child_requests_csr (p_parent_request_id number) is
154 select *
155 from fnd_concurrent_requests
156 where parent_request_id = p_parent_request_id;
157
158 -------------------
159 -- Declare Types --
160 -------------------
161 type bi_attr_dims_table is table of fem_bi_attr_dimensions_v%rowtype;
162 type child_requests_table is table of fnd_concurrent_requests%rowtype;
163
164 -----------------------
165 -- Declare variables --
166 -----------------------
167 l_bi_attr_dims_tbl bi_attr_dims_table;
168 l_child_requests_tbl child_requests_table;
169
170 l_module_name varchar2(200);
171 l_function_name varchar2(200);
172
173 l_request_data varchar2(100);
174
175 l_child_request_id number;
176
177 l_dimension_name varchar2(80);
178
179 l_phase varchar2(100);
180 l_status varchar2(100);
181 l_dev_phase varchar2(100);
182 l_dev_status varchar2(100);
183 l_message varchar2(500);
184
185 l_dummy_number number;
186 l_dummy_boolean boolean;
187
188 l_completion_status varchar2(30);
189
190 l_warnings number := 0;
191 l_errors number := 0;
192
193 l_attr_trans_invalid_dim_err exception;
194 l_attr_trans_child_sub_failed exception;
195
196 BEGIN
197
198 l_module_name := G_MODULE||'.'||lower(l_api_name);
199 l_function_name := G_PACKAGE_NAME||'.'||l_api_name;
200
201 -------------------------------------------
202 -- Start Procedure Logging and Messaging --
203 -------------------------------------------
204 FEM_ENGINES_PKG.Tech_Message (
205 p_severity => G_LOG_LEVEL_PROCEDURE
206 ,p_module => l_module_name || '.begin'
207 ,p_app_name => G_FEM
208 ,p_msg_name => G_GL_POST_201
209 ,p_token1 => 'FUNC_NAME'
210 ,p_value1 => l_function_name
211 ,p_token2 => 'TIME'
212 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
213 );
214
215 FEM_ENGINES_PKG.User_Message (
216 p_app_name => G_FEM
217 ,p_msg_name => G_GL_POST_201
218 ,p_token1 => 'FUNC_NAME'
219 ,p_value1 => l_function_name
220 ,p_token2 => 'TIME'
221 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
222 );
223
224 ------------------------------------------------
225 -- Initialize Package and Procedure Variables --
226 ------------------------------------------------
227 g_req_id := nvl(FND_GLOBAL.Conc_Request_ID,-1);
228 g_user_id := nvl(FND_GLOBAL.User_ID,-1);
229 g_login_id := nvl(FND_GLOBAL.Conc_Login_ID, FND_GLOBAL.Login_ID);
230
231 ------------------------------
232 -- Validate input parameter --
233 ------------------------------
234 begin
235 select 1 into l_dummy_number
236 from fem_bi_attr_dimensions_v
237 where dimension_varchar_label = p_dimension_varchar_label;
238 exception
239 when no_data_found then
240 raise l_attr_trans_invalid_dim_err;
241 when others then
242 raise;
243 end;
244
245 ----------------------
246 -- Start Processing --
247 ----------------------
248 if (p_dimension_varchar_label <> 'ALL') then
249
250 /***********************************************************************
251 Individual execution does not use FND_CONC_GLOBAL
252 ************************************************************************/
253
254 -- Call Transformation
255 l_completion_status := Transformation (
256 p_dimension_varchar_label => p_dimension_varchar_label
257 ,p_seed_db_link => p_seed_db_link
258 );
259
260 else
261
262 /***********************************************************************
263 Batch execution uses FND_CONC_GLOBAL
264
265 Read the value from REQUEST_DATA. If this is the first run of
266 the program, then this value will be NULL. Thus, submitting
267 child requests. Otherwise,the program is reawaken and REQUEST_DATA
268 will be the value that we passed to SET_REQ_GLOBALS on the previous
269 run.
270
271 References for PL/SQL Concurrent Processing Recursive Calls
272 -----------------------------------------------------------
273 1. Chapter 21: PL/SQL APIs for Concurrent Processing,
274 Oracle Applications Developers Guide,
275 2. Note 221542.1: Sample Code for FND_SUBMIT and FND_REQUEST API's
276 3. WSHDDSHB.pls
277 4. cefcshfb.pls
278 ************************************************************************/
279
280 l_request_data := FND_CONC_GLOBAL.Request_Data;
281
282 if l_request_data is null then
283
284 /**********************************************************************
285 Parent is initiated
286 **********************************************************************/
287 -- Get Dimension information
288 open l_bi_attr_dims_csr;
289 fetch l_bi_attr_dims_csr bulk collect into l_bi_attr_dims_tbl;
290 close l_bi_attr_dims_csr;
291
292 -- Run transformation for each dimension using a child process
293 for i in 1..l_bi_attr_dims_tbl.LAST loop
294
295 if (p_seed_db_link is not null) then
296
297 l_child_request_id :=
298 FND_REQUEST.Submit_Request (
299 application => G_FEM
300 ,program => 'FEM_BI_DIM_ATTR_TRANS'
301 ,description => l_bi_attr_dims_tbl(i).dimension_name
302 ,start_time => NULL
303 ,sub_request => TRUE
304 ,argument1 => l_bi_attr_dims_tbl(i).dimension_varchar_label
305 ,argument2 => p_seed_db_link
306 );
307
308 else
309
310 l_child_request_id :=
311 FND_REQUEST.Submit_Request (
312 application => G_FEM
313 ,program => 'FEM_BI_DIM_ATTR_TRANS'
314 ,description => l_bi_attr_dims_tbl(i).dimension_name
315 ,start_time => NULL
316 ,sub_request => TRUE
317 ,argument1 => l_bi_attr_dims_tbl(i).dimension_varchar_label
318 );
319
320 end if;
321
322 if l_child_request_id = 0 then
323
324 -- If a request submission is failed, raise an exception
325 l_dimension_name := l_bi_attr_dims_tbl(i).dimension_name;
326
327 x_errbuf := FND_MESSAGE.Get;
328
329 raise l_attr_trans_child_sub_failed;
330
331 else
332
333 FEM_ENGINES_PKG.User_Message (
334 p_app_name => G_FEM
335 ,p_msg_name => G_BI_ATTR_REQ_SUB_SUCCESS
336 ,p_token1 => 'DIMENSION'
337 ,p_value1 => l_bi_attr_dims_tbl(i).dimension_name
338 ,p_token2 => 'REQ_ID'
339 ,p_value2 => to_char(l_child_request_id)
340 );
341
342 FEM_ENGINES_PKG.Tech_Message (
343 p_severity => G_LOG_LEVEL_STATEMENT
344 ,p_module => l_module_name||'.child_req_submission'
345 ,p_app_name => G_FEM
346 ,p_msg_name => G_BI_ATTR_REQ_SUB_SUCCESS
347 ,p_token1 => 'DIMENSION'
348 ,p_value1 => l_bi_attr_dims_tbl(i).dimension_name
349 ,p_token2 => 'REQ_ID'
350 ,p_value2 => to_char(l_child_request_id)
351 );
352
353 end if;
354
355 end loop;
356
357 --
358 -- Put the program into the PAUSED status and indicate the end of
359 -- initial execution
360 --
361 FND_CONC_GLOBAL.Set_Req_Globals (
362 conc_status => 'PAUSED'
363 ,request_data => 'SUBMITTED'
364 );
365
366 l_completion_status := 'NORMAL';
367
368 else -- if l_request_data is null then
369
370 /**********************************************************************
371 Parent is reawakened
372 **********************************************************************/
376 close l_child_requests_csr;
373 -- Get child process ids
374 open l_child_requests_csr (g_req_id);
375 fetch l_child_requests_csr bulk collect into l_child_requests_tbl;
377
378 for i in 1..l_child_requests_tbl.LAST loop
379
380 l_status := NULL;
381 l_dev_status := NULL;
382
383 l_dummy_boolean :=
384 FND_CONCURRENT.Get_Request_Status (
385 request_id => l_child_requests_tbl(i).request_id
386 ,phase => l_phase
387 ,status => l_status
388 ,dev_phase => l_dev_phase
389 ,dev_status => l_dev_status
390 ,message => l_message
391 );
392
393 if l_dev_status = 'WARNING' then
394 l_warnings:= l_warnings + 1;
395 elsif l_dev_status <> 'NORMAL' then
396 l_errors := l_errors + 1;
397 end if;
398
399 FEM_ENGINES_PKG.Tech_Message (
400 p_severity => G_LOG_LEVEL_STATEMENT
401 ,p_module => l_module_name||'.child_req_status'
402 ,p_app_name => G_FEM
403 ,p_msg_name => G_BI_ATTR_REQ_STATUS
404 ,p_token1 => 'DIMENSION'
405 ,p_value1 => l_child_requests_tbl(i).description
406 ,p_token2 => 'REQ_ID'
407 ,p_value2 => to_char(l_child_requests_tbl(i).request_id)
408 ,p_token3 => 'STATUS'
409 ,p_value3 => l_status
410 );
411
412 FEM_ENGINES_PKG.User_Message (
413 p_app_name => G_FEM
414 ,p_msg_name => G_BI_ATTR_REQ_STATUS
415 ,p_token1 => 'DIMENSION'
416 ,p_value1 => l_child_requests_tbl(i).description
417 ,p_token2 => 'REQ_ID'
418 ,p_value2 => to_char(l_child_requests_tbl(i).request_id)
419 ,p_token3 => 'STATUS'
420 ,p_value3 => l_status
421 );
422
423 end loop;
424
425 if l_errors = 0 and l_warnings = 0 then
426
427 -- If all dimensions transformations are successful
428 l_completion_status := 'NORMAL';
429
430 elsif (l_errors > 0) and (l_errors = l_child_requests_tbl.count) then
431
432 -- If all dimensions transformations are failed
433 l_completion_status := 'ERROR';
434
435 else
436
437 -- If some dimensions transformations are successful
438 l_completion_status := 'WARNING';
439
440 end if;
441
442 end if; -- if l_request_data is null then
443
444 end if; -- if p_dimension_varchar_label = 'ALL' then
445
446 -----------------------------------------
447 -- End Procedure Logging and Messaging --
448 -----------------------------------------
449 if l_completion_status = 'NORMAL' then
450
451 x_retcode := '0';
452
453 FEM_ENGINES_PKG.Tech_Message (
454 p_severity => G_LOG_LEVEL_PROCEDURE
455 ,p_module => l_module_name||'.end'
456 ,p_app_name => G_FEM
457 ,p_msg_name => G_GL_POST_202
458 ,p_token1 => 'FUNC_NAME'
459 ,p_value1 => l_function_name
460 ,p_token2 => 'TIME'
461 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
462 );
463
464 FEM_ENGINES_PKG.User_Message (
465 p_app_name => G_FEM
466 ,p_msg_name => G_GL_POST_202
467 ,p_token1 => 'FUNC_NAME'
468 ,p_value1 => l_function_name
469 ,p_token2 => 'TIME'
470 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
471 );
472
473 elsif l_completion_status = 'WARNING' then
474
475 x_retcode := '1';
476
477 FEM_ENGINES_PKG.Tech_Message (
478 p_severity => G_LOG_LEVEL_PROCEDURE
479 ,p_module => l_module_name||'.end'
480 ,p_app_name => G_FEM
481 ,p_msg_name => G_GL_POST_206
482 );
483
487 );
484 FEM_ENGINES_PKG.User_Message (
485 p_app_name => G_FEM
486 ,p_msg_name => G_GL_POST_206
488
489 else
490
491 x_retcode := '2';
492
493 FEM_ENGINES_PKG.Tech_Message (
494 p_severity => G_LOG_LEVEL_PROCEDURE
495 ,p_module => l_module_name||'.end'
496 ,p_app_name => G_FEM
497 ,p_msg_name => G_GL_POST_203
498 ,p_token1 => 'FUNC_NAME'
499 ,p_value1 => l_function_name
500 ,p_token2 => 'TIME'
501 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
502 );
503
504 FEM_ENGINES_PKG.User_Message (
505 p_app_name => G_FEM
506 ,p_msg_name => G_GL_POST_203
507 ,p_token1 => 'FUNC_NAME'
508 ,p_value1 => l_function_name
509 ,p_token2 => 'TIME'
510 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
511 );
512
513 end if;
514
515 EXCEPTION
516
517 when l_attr_trans_invalid_dim_err then
518
519 rollback;
520
521 x_retcode := '2';
522
523 FEM_ENGINES_PKG.Tech_Message (
524 p_severity => G_LOG_LEVEL_EXCEPTION
525 ,p_module => l_module_name||'.invalid_dimension'
526 ,p_app_name => G_FEM
527 ,p_msg_name => G_BI_ATTR_INVALID_DIMENSION
528 ,p_token1 => 'DIMENSION'
529 ,p_value1 => p_dimension_varchar_label
530 );
531
532 FEM_ENGINES_PKG.User_Message (
533 p_app_name => G_FEM
534 ,p_msg_name => G_BI_ATTR_INVALID_DIMENSION
535 ,p_token1 => 'DIMENSION'
536 ,p_value1 => p_dimension_varchar_label
537 );
538
539 FEM_ENGINES_PKG.Tech_Message (
540 p_severity => G_LOG_LEVEL_EXCEPTION
541 ,p_module => l_module_name||'.invalid_dimension'
542 ,p_app_name => G_FEM
543 ,p_msg_name => G_GL_POST_203
544 ,p_token1 => 'FUNC_NAME'
545 ,p_value1 => l_function_name
546 ,p_token2 => 'TIME'
547 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
548 );
549
550 FEM_ENGINES_PKG.User_Message (
551 p_app_name => G_FEM
552 ,p_msg_name => G_GL_POST_203
553 ,p_token1 => 'FUNC_NAME'
554 ,p_value1 => l_function_name
555 ,p_token2 => 'TIME'
556 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
557 );
558
559 when l_attr_trans_child_sub_failed then
560
561 rollback;
562
563 x_retcode := '2';
564
565 FEM_ENGINES_PKG.Tech_Message (
566 p_severity => G_LOG_LEVEL_EXCEPTION
567 ,p_module => l_module_name||'.sub_failed'
568 ,p_app_name => G_FEM
569 ,p_msg_name => G_BI_ATTR_REQ_SUB_FAILURE
570 ,p_token1 => 'DIMENSION'
571 ,p_value1 => l_dimension_name
572 );
573
574 FEM_ENGINES_PKG.User_Message (
575 p_app_name => G_FEM
576 ,p_msg_name => G_BI_ATTR_REQ_SUB_FAILURE
577 ,p_token1 => 'DIMENSION'
578 ,p_value1 => l_dimension_name
579 );
580
581 FEM_ENGINES_PKG.Tech_Message (
582 p_severity => G_LOG_LEVEL_EXCEPTION
583 ,p_module => l_module_name||'.sub_failed'
584 ,p_app_name => G_FEM
585 ,p_msg_name => G_GL_POST_203
586 ,p_token1 => 'FUNC_NAME'
587 ,p_value1 => l_function_name
588 ,p_token2 => 'TIME'
589 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
590 );
591
592 FEM_ENGINES_PKG.User_Message (
593 p_app_name => G_FEM
594 ,p_msg_name => G_GL_POST_203
595 ,p_token1 => 'FUNC_NAME'
596 ,p_value1 => l_function_name
597 ,p_token2 => 'TIME'
598 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
599 );
600
601 when others then
602
603 rollback;
604
605 x_retcode := '2';
606
607 FEM_ENGINES_PKG.Tech_Message (
608 p_severity => G_LOG_LEVEL_EXCEPTION
609 ,p_module => l_module_name||'.others'
610 ,p_app_name => G_FEM
614 );
611 ,p_msg_name => G_GL_POST_215
612 ,p_token1 => 'ERR_MSG'
613 ,p_value1 => SQLERRM
615
616 FEM_ENGINES_PKG.User_Message (
617 p_app_name => G_FEM
618 ,p_msg_name => G_GL_POST_215
619 ,p_token1 => 'ERR_MSG'
620 ,p_value1 => SQLERRM
621 );
622
623 FEM_ENGINES_PKG.Tech_Message (
624 p_severity => G_LOG_LEVEL_EXCEPTION
625 ,p_module => l_module_name||'.others'
626 ,p_app_name => G_FEM
627 ,p_msg_name => G_GL_POST_203
628 ,p_token1 => 'FUNC_NAME'
629 ,p_value1 => l_function_name
630 ,p_token2 => 'TIME'
631 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
632 );
633
634 FEM_ENGINES_PKG.User_Message (
635 p_app_name => G_FEM
636 ,p_msg_name => G_GL_POST_203
637 ,p_token1 => 'FUNC_NAME'
638 ,p_value1 => l_function_name
639 ,p_token2 => 'TIME'
640 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
641 );
642
643 END Run_Transformation;
644
645
646 /*===========================================================================+
647 | PROCEDURE
648 | Get_Pago_Cal_Period_ID
649 |
650 | DESCRIPTION
651 | Returns the prior Calendar Period ID for the given Calendar Period.
652 |
653 | The returned Calendar Period will have the same Calendar, Dimension Group,
654 | and Adjustment Period Flag value.
655 |
656 | SCOPE - PUBLIC
657 |
658 | ARGUMENTS
659 | p_cal_period_id Calendar Period Id
660 +===========================================================================*/
661
662 FUNCTION Get_Pago_Cal_Period_ID (
663 p_cal_period_id in number
664 ) RETURN number
665 IS
666
667 l_pago_cal_period_id number;
668
669 cursor l_pago_cal_periods_csr (
670 p_cal_period_id in number
671 ) is
672 select cp2.cal_period_id
673 from fem_cal_periods_b cp
674 ,fem_cal_periods_attr cpa_adj
675 ,fem_cal_periods_b cp2
676 ,fem_cal_periods_attr cpa2_adj
677 ,fem_dimensions_b dim
678 ,fem_dim_attributes_b a
679 ,fem_dim_attr_versions_b v
680 where cp.cal_period_id = p_cal_period_id
681 and dim.dimension_varchar_label = 'CAL_PERIOD'
682 and a.dimension_id = dim.dimension_id
683 and a.attribute_varchar_label = 'ADJ_PERIOD_FLAG'
684 and v.attribute_id = a.attribute_id
685 and v.default_version_flag = 'Y'
686 and cpa_adj.attribute_id = a.attribute_id
687 and cpa_adj.version_id = v.version_id
688 and cpa_adj.cal_period_id = cp.cal_period_id
689 and cpa2_adj.attribute_id = a.attribute_id
690 and cpa2_adj.version_id = v.version_id
691 and cpa2_adj.cal_period_id = cp2.cal_period_id
692 and cp2.calendar_id = cp.calendar_id
693 and cp2.dimension_group_id = cp.dimension_group_id
694 and cpa2_adj.dim_attribute_varchar_member = cpa_adj.dim_attribute_varchar_member
695 and cp2.cal_period_id < cp.cal_period_id
696 order by cp2.cal_period_id desc;
697
698 BEGIN
699
700 open l_pago_cal_periods_csr (p_cal_period_id);
701
702 fetch l_pago_cal_periods_csr
703 into l_pago_cal_period_id;
704
705 close l_pago_cal_periods_csr;
706
707 return l_pago_cal_period_id;
708
709 EXCEPTION
710
711 when others then
712 if (l_pago_cal_periods_csr%isopen) then
713 close l_pago_cal_periods_csr;
714 end if;
715 return null;
716
717 END Get_Pago_Cal_Period_ID;
718
719
720 /*===========================================================================+
721 | PROCEDURE
722 | Get_Yago_Cal_Period_ID
723 |
724 | DESCRIPTION
725 | Returns the prior year Calendar Period ID for the given Calendar Period.
726 |
727 | The returned Calendar Period will have the same Calendar and Dimension
728 | Group. As Adjusment Periods can have overlapping date ranges, only
729 | non adjustment periods are processed and returned.
730 |
731 | SCOPE - PUBLIC
732 |
733 | ARGUMENTS
734 | p_cal_period_id Calendar Period Id
735 +===========================================================================*/
736
737 FUNCTION Get_Yago_Cal_Period_ID (
738 p_cal_period_id in number
739 ) RETURN number
740 IS
741
742 l_yago_cal_period_id number;
743
744 cursor l_yago_cal_periods_csr (
745 p_cal_period_id in number
746 ) is
747 select cp2.cal_period_id
748 from fem_cal_periods_b cp
749 ,fem_cal_periods_attr cpa_adj
750 ,fem_cal_periods_attr cpa_start
751 ,fem_cal_periods_attr cpa_end
752 ,fem_cal_periods_b cp2
753 ,fem_cal_periods_attr cpa2_adj
754 ,fem_cal_periods_attr cpa2_start
755 ,fem_cal_periods_attr cpa2_end
756 ,fem_dimensions_b dim
757 ,fem_dim_attributes_b a_adj
758 ,fem_dim_attr_versions_b v_adj
759 ,fem_dim_attributes_b a_start
760 ,fem_dim_attr_versions_b v_start
764 and dim.dimension_varchar_label = 'CAL_PERIOD'
761 ,fem_dim_attributes_b a_end
762 ,fem_dim_attr_versions_b v_end
763 where cp.cal_period_id = p_cal_period_id
765 and a_adj.dimension_id = dim.dimension_id
766 and a_adj.attribute_varchar_label = 'ADJ_PERIOD_FLAG'
767 and v_adj.attribute_id = a_adj.attribute_id
768 and v_adj.default_version_flag = 'Y'
769 and a_start.dimension_id = dim.dimension_id
770 and a_start.attribute_varchar_label = 'CAL_PERIOD_START_DATE'
771 and v_start.attribute_id = a_start.attribute_id
772 and v_start.default_version_flag = 'Y'
773 and a_end.dimension_id = dim.dimension_id
774 and a_end.attribute_varchar_label = 'CAL_PERIOD_END_DATE'
775 and v_end.attribute_id = a_end.attribute_id
776 and v_end.default_version_flag = 'Y'
777 and cpa_adj.attribute_id = a_adj.attribute_id
778 and cpa_adj.version_id = v_adj.version_id
779 and cpa_adj.cal_period_id = cp.cal_period_id
780 and cpa_adj.dim_attribute_varchar_member = 'N'
781 and cpa_start.attribute_id = a_start.attribute_id
782 and cpa_start.version_id = v_start.version_id
783 and cpa_start.cal_period_id = cp.cal_period_id
784 and cpa_end.attribute_id = a_end.attribute_id
785 and cpa_end.version_id = v_end.version_id
786 and cpa_end.cal_period_id = cp.cal_period_id
787 and cpa2_adj.attribute_id = a_adj.attribute_id
788 and cpa2_adj.version_id = v_adj.version_id
789 and cpa2_adj.cal_period_id = cp2.cal_period_id
790 and cpa2_adj.dim_attribute_varchar_member = 'N'
791 and cpa2_start.attribute_id = a_start.attribute_id
792 and cpa2_start.version_id = v_start.version_id
793 and cpa2_start.cal_period_id = cp2.cal_period_id
794 and cpa2_end.attribute_id = a_end.attribute_id
795 and cpa2_end.version_id = v_end.version_id
796 and cpa2_end.cal_period_id = cp2.cal_period_id
797 and cp2.calendar_id = cp.calendar_id
798 and cp2.dimension_group_id = cp.dimension_group_id
799 and add_months(cpa_end.date_assign_value,-12) between cpa2_start.date_assign_value and cpa2_end.date_assign_value
800 and add_months(cpa_start.date_assign_value,-12) between cpa2_start.date_assign_value and cpa2_end.date_assign_value;
801
802 BEGIN
803
804 open l_yago_cal_periods_csr (p_cal_period_id);
805
806 fetch l_yago_cal_periods_csr
807 into l_yago_cal_period_id;
808
809 close l_yago_cal_periods_csr;
810
811 return l_yago_cal_period_id;
812
813 EXCEPTION
814
815 when others then
816 if (l_yago_cal_periods_csr%isopen) then
817 close l_yago_cal_periods_csr;
818 end if;
819 return null;
820
821 END Get_Yago_Cal_Period_ID;
822
823
824 /*===========================================================================+
825 | FOR INTERNAL USE ONLY.
826 +===========================================================================*/
827
828 PROCEDURE Run_Seed_Transformation (
829 p_dimension_varchar_label in varchar2
830 ,p_seed_db_link in varchar2
831 ,x_completion_status out nocopy varchar2
832 ) IS
833
834 -----------------------
835 -- Declare constants --
836 -----------------------
837 l_api_name constant varchar2(100) := 'Run_Seed_Transformation';
838
839 ---------------------
840 -- Declare cursors --
841 ---------------------
842 cursor l_bi_attr_dims_csr is
843 select *
844 from fem_bi_attr_dimensions_v
845 where dimension_varchar_label <> 'ALL'
846 order by dimension_varchar_label;
847
848 -------------------
849 -- Declare Types --
850 -------------------
851 type bi_attr_dims_table is table of fem_bi_attr_dimensions_v%rowtype;
852
853 -----------------------
854 -- Declare variables --
855 -----------------------
856 l_bi_attr_dims_tbl bi_attr_dims_table;
857
858 l_module_name varchar2(200);
859 l_function_name varchar2(200);
860
861 l_dummy_number number;
862 l_dummy_boolean boolean;
863
864 l_completion_status varchar2(30);
865
866 l_warnings number := 0;
867 l_errors number := 0;
868
869 l_attr_trans_invalid_dim_err exception;
870
871 BEGIN
872
873 l_module_name := G_MODULE||'.'||lower(l_api_name);
874 l_function_name := G_PACKAGE_NAME||'.'||l_api_name;
875
876 -------------------------------------------
877 -- Start Procedure Logging and Messaging --
878 -------------------------------------------
879 FEM_ENGINES_PKG.Tech_Message (
880 p_severity => G_LOG_LEVEL_PROCEDURE
881 ,p_module => l_module_name || '.begin'
882 ,p_app_name => G_FEM
883 ,p_msg_name => G_GL_POST_201
884 ,p_token1 => 'FUNC_NAME'
885 ,p_value1 => l_function_name
886 ,p_token2 => 'TIME'
887 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
888 );
889
890 ------------------------------
891 -- Validate input parameter --
892 ------------------------------
893 begin
897 exception
894 select 1 into l_dummy_number
895 from fem_bi_attr_dimensions_v
896 where dimension_varchar_label = p_dimension_varchar_label;
898 when no_data_found then
899 raise l_attr_trans_invalid_dim_err;
900 when others then
901 raise;
902 end;
903
904 ----------------------
905 -- Start Processing --
906 ----------------------
907 if (p_dimension_varchar_label <> 'ALL') then
908
909 -- Call Transformation
910 x_completion_status := Transformation (
911 p_dimension_varchar_label => p_dimension_varchar_label
912 ,p_seed_db_link => p_seed_db_link
913 );
914
915 else
916
917 -- Get Dimension information
918 open l_bi_attr_dims_csr;
919 fetch l_bi_attr_dims_csr bulk collect into l_bi_attr_dims_tbl;
920 close l_bi_attr_dims_csr;
921
922 -- Run transformation for each dimension using a child process
923 for i in 1..l_bi_attr_dims_tbl.LAST loop
924
925 l_completion_status := Transformation (
926 p_dimension_varchar_label => l_bi_attr_dims_tbl(i).dimension_varchar_label
927 ,p_seed_db_link => p_seed_db_link
928 );
929
930 if l_completion_status = 'WARNING' then
931 l_warnings:= l_warnings + 1;
932 elsif l_completion_status <> 'NORMAL' then
933 l_errors := l_errors + 1;
934 end if;
935
936 end loop;
937
938 if l_errors = 0 and l_warnings = 0 then
939
940 -- If all dimensions transformations are successful
941 x_completion_status := 'NORMAL';
942
943 elsif (l_errors > 0) then
944
945 -- If all dimensions transformations are failed
946 x_completion_status := 'ERROR';
947
948 else
949
950 -- If some dimensions transformations are successful
951 x_completion_status := 'WARNING';
952
953 end if;
954
955 end if; -- if p_dimension_varchar_label = 'ALL' then
956
957 -----------------------------------------
958 -- End Procedure Logging and Messaging --
959 -----------------------------------------
960 if x_completion_status = 'NORMAL' then
961
962 FEM_ENGINES_PKG.Tech_Message (
963 p_severity => G_LOG_LEVEL_PROCEDURE
964 ,p_module => l_module_name||'.end'
965 ,p_app_name => G_FEM
966 ,p_msg_name => G_GL_POST_202
967 ,p_token1 => 'FUNC_NAME'
968 ,p_value1 => l_function_name
969 ,p_token2 => 'TIME'
970 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
971 );
972
973 elsif x_completion_status = 'WARNING' then
974
975 FEM_ENGINES_PKG.Tech_Message (
976 p_severity => G_LOG_LEVEL_PROCEDURE
977 ,p_module => l_module_name||'.end'
978 ,p_app_name => G_FEM
979 ,p_msg_name => G_GL_POST_206
980 );
981
982 x_completion_status := x_completion_status||': '||l_warnings;
983
984 else
985
986 FEM_ENGINES_PKG.Tech_Message (
987 p_severity => G_LOG_LEVEL_PROCEDURE
988 ,p_module => l_module_name||'.end'
989 ,p_app_name => G_FEM
990 ,p_msg_name => G_GL_POST_203
991 ,p_token1 => 'FUNC_NAME'
992 ,p_value1 => l_function_name
993 ,p_token2 => 'TIME'
994 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
995 );
996
997 x_completion_status := x_completion_status||': '||l_errors;
998
999 end if;
1000
1001 EXCEPTION
1002
1003 when l_attr_trans_invalid_dim_err then
1004
1005 rollback;
1006
1007 FEM_ENGINES_PKG.Tech_Message (
1008 p_severity => G_LOG_LEVEL_EXCEPTION
1009 ,p_module => l_module_name||'.invalid_dimension'
1010 ,p_app_name => G_FEM
1011 ,p_msg_name => G_BI_ATTR_INVALID_DIMENSION
1012 ,p_token1 => 'DIMENSION'
1013 ,p_value1 => p_dimension_varchar_label
1014 );
1015
1016 x_completion_status := 'ERROR: INVALID_DIMENSION';
1017
1018 when others then
1019
1020 rollback;
1021
1022 FEM_ENGINES_PKG.Tech_Message (
1023 p_severity => G_LOG_LEVEL_EXCEPTION
1024 ,p_module => l_module_name||'.others'
1025 ,p_app_name => G_FEM
1026 ,p_msg_name => G_GL_POST_215
1027 ,p_token1 => 'ERR_MSG'
1028 ,p_value1 => SQLERRM
1029 );
1030
1031 x_completion_status := 'ERROR: OTHER_EXCEPTION';
1032
1033 END Run_Seed_Transformation;
1034
1035
1036 -- Private Function and Procedure Bodies ---------------------------------------
1037
1038 /*===========================================================================+
1039 | FUNCTION
1040 | Transformation
1041 |
1042 | DESCRIPTION
1043 | Transforms an individual dimension attribute model.
1044 |
1045 | SCOPE - PRIVATE
1046 |
1047 | ARGUMENTS
1048 | p_dimension_varchar_label Dimension Varchar Label
1049 +===========================================================================*/
1050
1051 FUNCTION Transformation (
1052 p_dimension_varchar_label in varchar2
1056 ,p_next_extent in varchar2 := '2M'
1053 ,p_build_mode in varchar2 := 'DEFERRED'
1054 ,p_refresh_mode in varchar2 := 'COMPLETE'
1055 ,p_enable_qrewrite in varchar2 := 'N'
1057 ,p_seed_db_link in varchar2 := null
1058 ) RETURN varchar2
1059 IS
1060
1061 -----------------------
1062 -- Declare constants --
1063 -----------------------
1064 l_api_name constant varchar2(100) := 'Transformation';
1065
1066 -----------------------
1067 -- Declare variables --
1068 -----------------------
1069 l_return_status varchar2(30);
1070
1071 l_module_name varchar2(200);
1072 l_function_name varchar2(200);
1073
1074 l_data_tablespace varchar2(30);
1075 l_index_tablespace varchar2(30);
1076 l_enable_qrewrite varchar2(30);
1077 l_storage varchar2(200);
1078
1079 l_dimension_id number;
1080 l_dimension_name varchar2(80);
1081 l_member_b_table_name varchar2(30);
1082 l_member_tl_table_name varchar2(30);
1083 l_attribute_table_name varchar2(30);
1084 l_member_col varchar2(30);
1085 l_member_display_code_col varchar2(30);
1086 l_member_name_col varchar2(30);
1087 l_member_description_col varchar2(200);
1088 l_group_use_code varchar2(30);
1089 l_value_set_required_flag varchar2(1);
1090 l_enabled_applicable_flag varchar2(1);
1091 l_read_only_applicable_flag varchar2(1);
1092
1093 l_value_set_select varchar2(200);
1094
1095 l_attrd_query long;
1096 l_attrd_query_select long;
1097 l_attrd_attr_select long;
1098 l_attrd_query_from varchar2(2000);
1099 l_attrd_query_where varchar2(2000);
1100
1101 l_attrn_query long;
1102 l_attrn_query_select long;
1103 l_attrn_attr_select long;
1104 l_attrn_query_from varchar2(2000);
1105 l_attrn_query_where varchar2(2000);
1106
1107 l_attrn_vl_query_select long;
1108 l_attrn_vl_attr_select long;
1109
1110 l_no_attributes_exception exception;
1111
1112 BEGIN
1113
1114 l_return_status := 'NORMAL';
1115
1116 l_module_name :=
1117 G_MODULE||'.'||lower(l_api_name)||'.'||lower(p_dimension_varchar_label);
1118 l_function_name :=
1119 G_PACKAGE_NAME||'.'||l_api_name ||'.'||p_dimension_varchar_label;
1120
1121 -------------------------------------------
1122 -- Start Procedure Logging and Messaging --
1123 -------------------------------------------
1124 FEM_ENGINES_PKG.Tech_Message (
1125 p_severity => G_LOG_LEVEL_PROCEDURE
1126 ,p_module => l_module_name || '.begin'
1127 ,p_app_name => G_FEM
1128 ,p_msg_name => G_GL_POST_201
1129 ,p_token1 => 'FUNC_NAME'
1130 ,p_value1 => l_function_name
1131 ,p_token2 => 'TIME'
1132 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
1133 );
1134
1135 FEM_ENGINES_PKG.User_Message (
1136 p_app_name => G_FEM
1137 ,p_msg_name => G_GL_POST_201
1138 ,p_token1 => 'FUNC_NAME'
1139 ,p_value1 => l_function_name
1140 ,p_token2 => 'TIME'
1141 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
1142 );
1143
1144 ------------------------------------------------
1145 -- Initialize Package and Procedure Variables --
1146 ------------------------------------------------
1147 if p_enable_qrewrite = 'Y' then
1148 l_enable_qrewrite := 'ENABLE';
1149 else
1150 l_enable_qrewrite := 'DISABLE';
1151 end if;
1152
1153 -- Set the Storage Clause for the CREATE MATERIALIZED VIEW statement
1154 l_storage :=
1155 ' STORAGE(INITIAL 4K NEXT '||p_next_extent||' MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0)';
1156
1157 -- Get the data and index tablespace names for creating materialized views and their indexes
1158 l_data_tablespace := ad_mv.g_mv_data_tablespace;
1159 l_index_tablespace := ad_mv.g_mv_index_tablespace;
1160
1161 ----------------------------------
1162 -- Get the Dimension's metadata --
1163 ----------------------------------
1164 select dimension_id
1165 ,dimension_name
1166 ,member_b_table_name
1167 ,member_tl_table_name
1168 ,attribute_table_name
1169 ,member_col
1170 ,decode(member_display_code_col
1171 ,member_col,null
1172 ,member_display_code_col)
1173 ,decode(member_name_col
1174 ,member_display_code_col,null
1175 ,member_col,null
1176 ,member_name_col)
1177 ,decode(member_description_col
1178 ,member_name_col,null
1179 ,member_display_code_col,null
1180 ,member_col,null
1181 ,member_description_col)
1182 ,group_use_code
1183 ,value_set_required_flag
1184 ,logical_delete_applicable_flag
1185 ,read_only_applicable_flag
1186 into l_dimension_id
1187 ,l_dimension_name
1188 ,l_member_b_table_name
1189 ,l_member_tl_table_name
1190 ,l_attribute_table_name
1191 ,l_member_col
1195 ,l_group_use_code
1192 ,l_member_display_code_col
1193 ,l_member_name_col
1194 ,l_member_description_col
1196 ,l_value_set_required_flag
1197 ,l_enabled_applicable_flag
1198 ,l_read_only_applicable_flag
1199 from fem_xdim_dimensions_vl
1200 where dimension_varchar_label = p_dimension_varchar_label;
1201
1202 ----------------------------------------------------------------------------
1203 -- Initialize variables used in dynamic SQL based on dimension properties --
1204 ----------------------------------------------------------------------------
1205 l_attrd_query_select :=
1206 ' select b.'||l_member_col;
1207
1208 l_attrd_query_from :=
1209 ' from '||l_member_b_table_name||' b';
1210
1211 l_attrd_query_where :=
1212 ' where 1=1';
1213
1214 l_attrn_query_select :=
1215 ' select b.'||l_member_col;
1216
1217 l_attrn_vl_query_select :=
1218 ' select '||l_member_col;
1219
1220 l_attrn_query_from :=
1221 ' from '||l_member_b_table_name||' b'||
1222 ' ,'||l_member_tl_table_name||' tl';
1223
1224 l_attrn_query_where :=
1225 ' where tl.'||l_member_col||' = b.'||l_member_col;
1226
1227 -- Must include value_set_id columns if a VSR dimension
1228 if (l_value_set_required_flag = 'Y') then
1229
1230 l_value_set_select :=
1231 ' ,value_set_id';
1232
1233 l_attrd_query_select := l_attrd_query_select||
1234 ' ,b.value_set_id';
1235
1236 l_attrn_query_select := l_attrn_query_select||
1237 ' ,b.value_set_id';
1238
1239 l_attrn_vl_query_select := l_attrn_vl_query_select||
1240 ' ,value_set_id';
1241
1242 l_attrn_query_where := l_attrn_query_where||
1243 ' and tl.value_set_id = b.value_set_id';
1244
1245 end if;
1246
1247 l_attrn_query_select := l_attrn_query_select||
1248 ' ,tl.language'||
1249 ' ,tl.source_lang';
1250
1251 if (l_member_display_code_col is not null) then
1252
1253 l_attrd_query_select := l_attrd_query_select||
1254 ' ,b.'||l_member_display_code_col;
1255
1256 end if;
1257
1258 if (l_member_name_col is not null) then
1259
1260 l_attrn_query_select := l_attrn_query_select||
1261 ' ,tl.'||l_member_name_col;
1262
1263 l_attrn_vl_query_select := l_attrn_vl_query_select||
1264 ' ,'||l_member_name_col;
1265
1266 end if;
1267
1268 if (l_member_description_col is not null) then
1269
1270 l_attrn_query_select := l_attrn_query_select||
1271 ' ,tl.'||l_member_description_col;
1272
1273 l_attrn_vl_query_select := l_attrn_vl_query_select||
1274 ' ,'||l_member_description_col;
1275
1276 end if;
1277
1278 if (l_group_use_code <> 'NOT_SUPPORTED') then
1279
1280 -- If dimension supports dimension groups, then add the necessary
1281 -- dimension group columns and tables before building final SELECT
1282 -- statement.
1283
1284 l_attrd_query_select := l_attrd_query_select||
1285 ' ,b.dimension_group_id'||
1286 ' ,dgb.dimension_group_display_code';
1287
1288 l_attrd_query_from := l_attrd_query_from||
1289 ' ,fem_dimension_grps_b dgb';
1290
1291 l_attrd_query_where := l_attrd_query_where||
1292 ' and dgb.dimension_group_id (+) = b.dimension_group_id';
1293
1294 l_attrn_query_select := l_attrn_query_select||
1295 ' ,b.dimension_group_id'||
1296 ' ,(select dgtl.dimension_group_name'||
1297 ' from fem_dimension_grps_tl dgtl'||
1298 ' where dgtl.dimension_group_id = b.dimension_group_id'||
1299 ' and dgtl.language = tl.language'||
1300 ' ) as dimension_group_name'||
1301 ' ,(select dgtl.description'||
1302 ' from fem_dimension_grps_tl dgtl'||
1303 ' where dgtl.dimension_group_id = b.dimension_group_id'||
1304 ' and dgtl.language = tl.language'||
1305 ' ) as dimension_group_desc';
1306
1307 l_attrn_vl_query_select := l_attrn_vl_query_select||
1308 ' ,dimension_group_id'||
1309 ' ,dimension_group_name'||
1310 ' ,dimension_group_desc';
1311
1312 end if;
1313
1314 -- Need to add the Calendar colums for the Calendar Period dimension
1315 -- Also add PAGO and YAGO columns as Calendar Period dimension columns for
1316 -- use in variance reporting.
1317 if (p_dimension_varchar_label = 'CAL_PERIOD') then
1318
1319 l_attrd_query_select := l_attrd_query_select||
1320 ' ,b.calendar_id'||
1321 ' ,cal.calendar_display_code'||
1322 ' ,FEM_BI_DIMENSION_UTILS_PKG.Get_Pago_Cal_Period_ID(b.'||l_member_col||') pago_cal_period_id'||
1323 ' ,FEM_BI_DIMENSION_UTILS_PKG.Get_Yago_Cal_Period_ID(b.'||l_member_col||') yago_cal_period_id';
1324
1325 l_attrd_query_from := l_attrd_query_from||
1326 ' ,fem_calendars_b cal';
1327
1328 l_attrd_query_where := l_attrd_query_where||
1329 ' and cal.calendar_id = b.calendar_id';
1330
1331 l_attrn_query_select := l_attrn_query_select||
1332 ' ,b.calendar_id'||
1333 ' ,cal.calendar_name'||
1334 ' ,cal.description as calendar_desc';
1335
1336 l_attrn_vl_query_select := l_attrn_vl_query_select||
1337 ' ,calendar_id'||
1338 ' ,calendar_name'||
1339 ' ,calendar_desc';
1340
1344 l_attrn_query_where := l_attrn_query_where||
1341 l_attrn_query_from := l_attrn_query_from||
1342 ' ,fem_calendars_tl cal';
1343
1345 ' and cal.calendar_id = b.calendar_id'||
1346 ' and cal.language = tl.language';
1347
1348 end if;
1349
1350 if (l_enabled_applicable_flag = 'Y') then
1351
1352 l_attrd_query_select := l_attrd_query_select||
1353 ' ,b.enabled_flag';
1354
1355 end if;
1356
1357 if (l_read_only_applicable_flag = 'Y') then
1358
1359 l_attrd_query_select := l_attrd_query_select||
1360 ' ,b.read_only_flag';
1361
1362 end if;
1363
1364 l_attrd_query_select := l_attrd_query_select||
1365 ' ,b.personal_flag';
1366
1367 -- Get the dynamic SQL for querying Attribute Values
1368 if (p_seed_db_link is not null) then
1369
1370 Get_Seed_Dim_Attribute_Sql (
1371 p_dimension_id => l_dimension_id
1372 ,p_attribute_table_name => l_attribute_table_name
1373 ,p_member_col => l_member_col
1374 ,p_value_set_required_flag => l_value_set_required_flag
1375 ,p_seed_db_link => p_seed_db_link
1376 ,x_attrd_attr_select => l_attrd_attr_select
1377 ,x_attrn_attr_select => l_attrn_attr_select
1378 ,x_attrn_vl_attr_select => l_attrn_vl_attr_select
1379 );
1380
1381 else
1382
1383 Get_Dim_Attribute_Sql (
1384 p_dimension_id => l_dimension_id
1385 ,p_attribute_table_name => l_attribute_table_name
1386 ,p_member_col => l_member_col
1387 ,p_value_set_required_flag => l_value_set_required_flag
1388 ,x_attrd_attr_select => l_attrd_attr_select
1389 ,x_attrn_attr_select => l_attrn_attr_select
1390 ,x_attrn_vl_attr_select => l_attrn_vl_attr_select
1391 );
1392
1393 end if;
1394
1395 if (l_attrd_attr_select is null) then
1396
1397 FEM_ENGINES_PKG.User_Message (
1398 p_app_name => G_FEM
1399 ,p_msg_name => G_BI_ATTR_NO_ATTRIBUTES_WRN
1400 ,p_token1 => 'DIMENSION'
1401 ,p_value1 => l_function_name
1402 );
1403
1404 l_return_status := 'WARNING';
1405
1406 end if;
1407
1408 l_attrd_query_select :=
1409 l_attrd_query_select||
1410 l_attrd_attr_select||
1411 ' ,b.created_by'||
1412 ' ,b.creation_date'||
1413 ' ,b.last_updated_by'||
1414 ' ,b.last_update_date'||
1415 ' ,b.last_update_login';
1416
1417 l_attrn_query_select :=
1418 l_attrn_query_select||
1419 l_attrn_attr_select||
1420 ' ,tl.created_by'||
1421 ' ,tl.creation_date'||
1422 ' ,tl.last_updated_by'||
1423 ' ,tl.last_update_date'||
1424 ' ,tl.last_update_login';
1425
1426 l_attrn_vl_query_select :=
1427 l_attrn_vl_query_select||
1428 l_attrn_vl_attr_select||
1429 ' ,created_by'||
1430 ' ,creation_date'||
1431 ' ,last_updated_by'||
1432 ' ,last_update_date'||
1433 ' ,last_update_login';
1434
1435 ----------------------------------------------------
1436 -- Build the complete SQL for the Attribute Views --
1437 ----------------------------------------------------
1438 -- Attribute Value Diplay Codes
1439 l_attrd_query :=
1440 l_attrd_query_select||
1441 l_attrd_query_from||
1442 l_attrd_query_where;
1443
1444 -- for i in 0..(round((length(l_attrd_query)/255),0)) loop
1445 -- dbms_output.put_line(substr(l_attrd_query,255*i+1,255));
1446 -- end loop;
1447
1448 -- Attribute Value Names
1449 l_attrn_query :=
1450 l_attrn_query_select||
1451 l_attrn_query_from||
1452 l_attrn_query_where;
1453
1454 -- for i in 0..(round((length(l_attrn_query)/255),0)) loop
1455 -- dbms_output.put_line(substr(l_attrn_query,255*i+1,255));
1456 -- end loop;
1457
1458 -----------------------------------------------------------------
1459 -- Create the DB objects for supporting the Materialized Views --
1460 -----------------------------------------------------------------
1461 Create_MV_Objects (
1462 p_attr_mv_name_prefix => l_attribute_table_name||'D'
1463 ,p_attr_query => l_attrd_query
1464 ,p_attr_vl_query_select => null
1465 ,p_member_col => l_member_col
1466 ,p_member_display_code_col => l_member_display_code_col
1467 ,p_member_name_col => null
1468 ,p_value_set_select => l_value_set_select
1469 ,p_data_tablespace => l_data_tablespace
1470 ,p_index_tablespace => l_index_tablespace
1471 ,p_storage => l_storage
1472 ,p_build_mode => p_build_mode
1473 ,p_refresh_mode => p_refresh_mode
1474 ,p_enable_qrewrite => l_enable_qrewrite
1475 );
1476
1477 Create_MV_Objects (
1478 p_attr_mv_name_prefix => l_attribute_table_name||'N'
1479 ,p_attr_query => l_attrn_query
1480 ,p_attr_vl_query_select => l_attrn_vl_query_select
1481 ,p_member_col => l_member_col
1482 ,p_member_display_code_col => null
1483 ,p_member_name_col => l_member_name_col
1484 ,p_value_set_select => l_value_set_select
1488 ,p_build_mode => p_build_mode
1485 ,p_data_tablespace => l_data_tablespace
1486 ,p_index_tablespace => l_index_tablespace
1487 ,p_storage => l_storage
1489 ,p_refresh_mode => p_refresh_mode
1490 ,p_enable_qrewrite => l_enable_qrewrite
1491 );
1492
1493 -----------------------------------------
1494 -- End Procedure Logging and Messaging --
1495 -----------------------------------------
1496 FEM_ENGINES_PKG.Tech_Message (
1497 p_severity => G_LOG_LEVEL_PROCEDURE
1498 ,p_module => l_module_name || '.end'
1499 ,p_app_name => G_FEM
1500 ,p_msg_name => G_GL_POST_202
1501 ,p_token1 => 'FUNC_NAME'
1502 ,p_value1 => l_function_name
1503 ,p_token2 => 'TIME'
1504 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
1505 );
1506
1507 FEM_ENGINES_PKG.User_Message (
1508 p_app_name => G_FEM
1509 ,p_msg_name => G_GL_POST_202
1510 ,p_token1 => 'FUNC_NAME'
1511 ,p_value1 => l_function_name
1512 ,p_token2 => 'TIME'
1513 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
1514 );
1515
1516 return(l_return_status);
1517
1518 EXCEPTION
1519
1520 when g_mv_create_exception then
1521
1522 rollback;
1523
1524 FEM_ENGINES_PKG.Tech_Message (
1525 p_severity => G_LOG_LEVEL_EXCEPTION
1526 ,p_module => l_module_name||'.mv_create_exception'
1527 ,p_app_name => G_FEM
1528 ,p_msg_name => G_GL_POST_203
1529 ,p_token1 => 'FUNC_NAME'
1530 ,p_value1 => l_function_name
1531 ,p_token2 => 'TIME'
1532 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
1533 );
1534
1535 FEM_ENGINES_PKG.User_Message (
1536 p_app_name => G_FEM
1537 ,p_msg_name => G_GL_POST_203
1538 ,p_token1 => 'FUNC_NAME'
1539 ,p_value1 => l_function_name
1540 ,p_token2 => 'TIME'
1541 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
1542 );
1543
1544 return('ERROR');
1545
1546 when others then
1547
1548 rollback;
1549
1550 FEM_ENGINES_PKG.Tech_Message (
1551 p_severity => G_LOG_LEVEL_EXCEPTION
1552 ,p_module => l_module_name||'.others'
1553 ,p_app_name => G_FEM
1554 ,p_msg_name => G_GL_POST_215
1555 ,p_token1 => 'ERR_MSG'
1556 ,p_value1 => SQLERRM
1557 );
1558
1559 FEM_ENGINES_PKG.User_Message (
1560 p_app_name => G_FEM
1561 ,p_msg_name => G_GL_POST_215
1562 ,p_token1 => 'ERR_MSG'
1563 ,p_value1 => SQLERRM
1564 );
1565
1566 FEM_ENGINES_PKG.Tech_Message (
1567 p_severity => G_LOG_LEVEL_EXCEPTION
1568 ,p_module => l_module_name||'.others'
1569 ,p_app_name => G_FEM
1570 ,p_msg_name => G_GL_POST_203
1571 ,p_token1 => 'FUNC_NAME'
1572 ,p_value1 => l_function_name
1573 ,p_token2 => 'TIME'
1574 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
1575 );
1576
1577 FEM_ENGINES_PKG.User_Message (
1578 p_app_name => G_FEM
1579 ,p_msg_name => G_GL_POST_203
1580 ,p_token1 => 'FUNC_NAME'
1581 ,p_value1 => l_function_name
1582 ,p_token2 => 'TIME'
1583 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
1584 );
1585
1586 return('ERROR');
1587
1588 END Transformation;
1589
1590
1591 /*===========================================================================+
1592 | PROCEDURE
1593 | Create_MV_Objects
1594 |
1595 | DESCRIPTION
1596 | Creates all the DB Objects to supporting the Attribute Materialized
1597 ? Views.
1598 |
1599 | SCOPE - PRIVATE
1600 |
1601 | ARGUMENTS
1602 | p_attr_mv_name_prefix Attribute MV Name Prefix
1603 | p_attr_query Attribute MV Query Statement
1604 | p_attr_vl_query_select Attribute VL View Select Statement
1605 | p_member_col Dimension Member ID Column Name
1606 | p_member_display_code_col Dimension Member Display Code Column Name
1607 | p_member_name_col Dimension Member Name Column Name
1608 | p_value_set_select Value Set Select Statement
1609 | p_data_tablespace MV Data Tablespace Name
1610 | p_index_tablespace MV Index Tablespace Name
1611 | p_storage MV Storage Clause
1612 | p_build_mode MV Build Mode
1613 | p_refresh_mode MV Refresh Mode
1614 | p_enable_qrewrite MV Enable Query Rewrite Clause
1615 +===========================================================================*/
1616
1617 PROCEDURE Create_MV_Objects (
1618 p_attr_mv_name_prefix in varchar2
1619 ,p_attr_query in long
1620 ,p_attr_vl_query_select in long
1621 ,p_member_col in varchar2
1622 ,p_member_display_code_col in varchar2
1623 ,p_member_name_col in varchar2
1624 ,p_value_set_select in varchar2
1625 ,p_data_tablespace in varchar2
1626 ,p_index_tablespace in varchar2
1630 ,p_enable_qrewrite in varchar2
1627 ,p_storage in varchar2
1628 ,p_build_mode in varchar2
1629 ,p_refresh_mode in varchar2
1631 )
1632 IS
1633
1634 -----------------------
1635 -- Declare constants --
1636 -----------------------
1637 l_api_name constant varchar2(100) := G_PACKAGE_NAME||'.Create_MV_Objects';
1638
1639 -----------------------
1640 -- Declare variables --
1641 -----------------------
1642 l_module_name varchar2(200);
1643 l_function_name varchar2(200);
1644
1645 l_attr_v_name varchar2(30);
1646 l_attr_mv_name varchar2(30);
1647 l_attr_vl_name varchar2(30);
1648
1649 l_pk_index_columns varchar2(2000);
1650 l_u1_index_columns varchar2(2000);
1651
1652 l_dynamic_sql varchar2(2000);
1653
1654 BEGIN
1655
1656 l_module_name :=
1657 G_MODULE||'.'||lower(l_api_name)||'.'||lower(p_attr_mv_name_prefix);
1658 l_function_name :=
1659 G_PACKAGE_NAME||'.'||l_api_name ||'.'||p_attr_mv_name_prefix;
1660
1661 -------------------------------------------
1662 -- Start Procedure Logging and Messaging --
1663 -------------------------------------------
1664 FEM_ENGINES_PKG.Tech_Message (
1665 p_severity => G_LOG_LEVEL_PROCEDURE
1666 ,p_module => l_module_name || '.begin'
1667 ,p_app_name => G_FEM
1668 ,p_msg_name => G_GL_POST_201
1669 ,p_token1 => 'FUNC_NAME'
1670 ,p_value1 => l_function_name
1671 ,p_token2 => 'TIME'
1672 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
1673 );
1674
1675 FEM_ENGINES_PKG.User_Message (
1676 p_app_name => G_FEM
1677 ,p_msg_name => G_GL_POST_201
1678 ,p_token1 => 'FUNC_NAME'
1679 ,p_value1 => l_function_name
1680 ,p_token2 => 'TIME'
1681 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
1682 );
1683
1684 ------------------------------------------------
1685 -- Initialize Package and Procedure Variables --
1686 ------------------------------------------------
1687
1688 -- Set the names for the views and materialized views
1689 l_attr_v_name := p_attr_mv_name_prefix||'_V';
1690 l_attr_mv_name := p_attr_mv_name_prefix||'_MV';
1691 l_attr_vl_name := p_attr_mv_name_prefix||'_VL';
1692
1693 ------------------------------------------
1694 -- Drop The Attribute Materialized View --
1695 ------------------------------------------
1696 begin
1697
1698 l_dynamic_sql :=
1699 ' DROP MATERIALIZED VIEW '||l_attr_mv_name;
1700
1701 FEM_ENGINES_PKG.Tech_Message (
1702 p_severity => G_LOG_LEVEL_STATEMENT
1703 ,p_module => l_module_name||'.drop_mv'
1704 ,p_msg_text => l_dynamic_sql
1705 );
1706
1707 execute immediate l_dynamic_sql;
1708
1709 exception
1710 when g_mv_notexists_exception then null;
1711 end;
1712
1713 ----------------------------------------------------------
1714 -- Create View For Collapsing Attribute Dimension Model --
1715 ----------------------------------------------------------
1716 if (G_LOG_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1717
1718 for i in 0..(round((length(p_attr_query)/4000),0)) loop
1719
1720 FEM_ENGINES_PKG.Tech_Message (
1721 p_severity => G_LOG_LEVEL_STATEMENT
1722 ,p_module => l_module_name||'.create_v.'||lower(l_attr_v_name)||'.'||i
1723 ,p_msg_text => substr(p_attr_query,4000*i+1,4000)
1724 );
1725
1726 end loop;
1727
1728 end if;
1729
1730 execute immediate
1731 ' CREATE OR REPLACE VIEW '||l_attr_v_name||
1732 ' AS '||p_attr_query;
1733
1734 --------------------------------------------
1735 -- Create The Attribute Materialized View --
1736 --------------------------------------------
1737 l_dynamic_sql :=
1738 ' CREATE MATERIALIZED VIEW '||l_attr_mv_name||
1739 ' TABLESPACE '||p_data_tablespace||
1740 ' INITRANS 4 MAXTRANS 255 '||
1741 p_storage||
1742 ' BUILD '||p_build_mode||
1743 ' USING INDEX TABLESPACE '||p_index_tablespace||
1744 p_storage||
1745 ' REFRESH '||p_refresh_mode ||' ON DEMAND '||
1746 p_enable_qrewrite||' QUERY REWRITE '||
1747 ' AS '||
1748 ' SELECT *'||
1749 ' FROM '||l_attr_v_name;
1750
1751 FEM_ENGINES_PKG.Tech_Message (
1752 p_severity => G_LOG_LEVEL_STATEMENT
1753 ,p_module => l_module_name||'.create_mv'
1754 ,p_msg_text => l_dynamic_sql
1755 );
1756
1757 execute immediate l_dynamic_sql;
1758
1759 -----------------------------------
1760 -- Create The Attribute MLS View --
1761 -----------------------------------
1762 if (p_attr_vl_query_select is not null) then
1763
1764 l_dynamic_sql :=
1765 ' CREATE OR REPLACE VIEW '||l_attr_vl_name||
1766 ' AS'||
1767 p_attr_vl_query_select||
1768 ' FROM '||l_attr_mv_name||
1769 ' WHERE LANGUAGE = USERENV(''LANG'')';
1770
1771 FEM_ENGINES_PKG.Tech_Message (
1772 p_severity => G_LOG_LEVEL_STATEMENT
1776
1773 ,p_module => l_module_name||'.create_vl'
1774 ,p_msg_text => l_dynamic_sql
1775 );
1777 execute immediate l_dynamic_sql;
1778
1779 end if;
1780
1781 --------------------------------------------
1782 -- Create The Materialized View's Indexes --
1783 --------------------------------------------
1784 if (p_member_display_code_col is not null) then
1785
1786 l_pk_index_columns :=
1787 p_member_col||
1788 p_value_set_select;
1789
1790 l_u1_index_columns :=
1791 p_member_display_code_col||
1792 p_value_set_select;
1793
1794 elsif (p_member_name_col is not null) then
1795
1796 l_pk_index_columns :=
1797 p_member_col||
1798 p_value_set_select||
1799 ' ,language';
1800
1801 l_u1_index_columns :=
1802 p_member_name_col||
1803 p_value_set_select||
1804 ' ,language';
1805
1806 end if;
1807
1808 -- Primary Key Index
1809 if (l_pk_index_columns is not null) then
1810
1811 l_dynamic_sql :=
1812 ' CREATE INDEX '||l_attr_mv_name||'_PK'||
1813 ' ON '||l_attr_mv_name||' ('||
1814 l_pk_index_columns||
1815 ' )'||
1816 ' TABLESPACE '||p_index_tablespace||
1817 ' INITRANS 4 MAXTRANS 255 '||
1818 p_storage;
1819
1820 FEM_ENGINES_PKG.Tech_Message (
1821 p_severity => G_LOG_LEVEL_STATEMENT
1822 ,p_module => l_module_name||'.create_mv_pk'
1823 ,p_msg_text => l_dynamic_sql
1824 );
1825
1826 execute immediate l_dynamic_sql;
1827
1828 end if;
1829
1830 -- Alternate Unique Index #1
1831 if (l_u1_index_columns is not null) then
1832
1833 l_dynamic_sql :=
1834 ' CREATE INDEX '||l_attr_mv_name||'_U1'||
1835 ' ON '||l_attr_mv_name||' ('||
1836 l_u1_index_columns||
1837 ' )'||
1838 ' TABLESPACE '||p_index_tablespace||
1839 ' INITRANS 4 MAXTRANS 255 '||
1840 p_storage;
1841
1842 FEM_ENGINES_PKG.Tech_Message (
1843 p_severity => G_LOG_LEVEL_STATEMENT
1844 ,p_module => l_module_name||'.create_mv_u1'
1845 ,p_msg_text => l_dynamic_sql
1846 );
1847
1848 execute immediate l_dynamic_sql;
1849
1850 end if;
1851
1852 -----------------------------------
1853 -- Refresh The Materialized View --
1854 -----------------------------------
1855 FEM_ENGINES_PKG.Tech_Message (
1856 p_severity => G_LOG_LEVEL_STATEMENT
1857 ,p_module => l_module_name||'.refresh_mv'
1858 ,p_msg_text => l_attr_mv_name
1859 );
1860
1861 DBMS_MVIEW.Refresh(l_attr_mv_name,'?');
1862
1863 -----------------------------------------
1864 -- End Procedure Logging and Messaging --
1865 -----------------------------------------
1866 FEM_ENGINES_PKG.Tech_Message (
1867 p_severity => G_LOG_LEVEL_PROCEDURE
1868 ,p_module => l_module_name || '.end'
1869 ,p_app_name => G_FEM
1870 ,p_msg_name => G_GL_POST_202
1871 ,p_token1 => 'FUNC_NAME'
1872 ,p_value1 => l_function_name
1873 ,p_token2 => 'TIME'
1874 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
1875 );
1876
1877 FEM_ENGINES_PKG.User_Message (
1878 p_app_name => G_FEM
1879 ,p_msg_name => G_GL_POST_202
1880 ,p_token1 => 'FUNC_NAME'
1881 ,p_value1 => l_function_name
1882 ,p_token2 => 'TIME'
1883 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
1884 );
1885
1886 EXCEPTION
1887
1888 when others then
1889
1890 rollback;
1891
1892 FEM_ENGINES_PKG.Tech_Message (
1893 p_severity => G_LOG_LEVEL_EXCEPTION
1894 ,p_module => l_module_name||'.others'
1895 ,p_app_name => G_FEM
1896 ,p_msg_name => G_GL_POST_215
1897 ,p_token1 => 'ERR_MSG'
1898 ,p_value1 => SQLERRM
1899 );
1900
1901 FEM_ENGINES_PKG.User_Message (
1902 p_app_name => G_FEM
1903 ,p_msg_name => G_GL_POST_215
1904 ,p_token1 => 'ERR_MSG'
1905 ,p_value1 => SQLERRM
1906 );
1907
1908 FEM_ENGINES_PKG.Tech_Message (
1909 p_severity => G_LOG_LEVEL_EXCEPTION
1910 ,p_module => l_module_name||'.others'
1911 ,p_app_name => G_FEM
1912 ,p_msg_name => G_GL_POST_203
1913 ,p_token1 => 'FUNC_NAME'
1914 ,p_value1 => l_function_name
1915 ,p_token2 => 'TIME'
1916 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
1917 );
1918
1919 FEM_ENGINES_PKG.User_Message (
1920 p_app_name => G_FEM
1921 ,p_msg_name => G_GL_POST_203
1922 ,p_token1 => 'FUNC_NAME'
1923 ,p_value1 => l_function_name
1924 ,p_token2 => 'TIME'
1925 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
1926 );
1927
1928 raise g_mv_create_exception;
1929
1930 END Create_MV_Objects;
1931
1932
1933 /*===========================================================================+
1934 | PROCEDURE
1935 | Get_Dim_Attribute_Sql
1936 |
1937 | DESCRIPTION
1941 |
1938 | Gets dynamic SQL statement for selecting dimension attribute values.
1939 |
1940 | SCOPE - PRIVATE
1942 | ARGUMENTS
1943 | p_dimension_id Dimension ID
1944 | p_attribute_table_name Attribute Table Name
1945 | p_member_col Member Column Name
1946 | p_value_set_required_flag Value Set Required Flag
1947 +===========================================================================*/
1948
1949 PROCEDURE Get_Dim_Attribute_Sql (
1950 p_dimension_id in number
1951 ,p_attribute_table_name in varchar2
1952 ,p_member_col in varchar2
1953 ,p_value_set_required_flag in varchar2
1954 ,x_attrd_attr_select out nocopy long
1955 ,x_attrn_attr_select out nocopy long
1956 ,x_attrn_vl_attr_select out nocopy long
1957 )
1958 IS
1959
1960 -----------------------
1961 -- Declare constants --
1962 -----------------------
1963 l_api_name constant varchar2(100) := G_PACKAGE_NAME||'.Get_Dim_Attribute_Sql';
1964
1965 -----------------------
1966 -- Declare variables --
1967 -----------------------
1968 l_module_name varchar2(200);
1969 l_function_name varchar2(200);
1970
1971 l_value_set_where varchar2(100);
1972
1973 l_attr_mem_b_tab varchar2(30);
1974 l_attr_mem_tl_tab varchar2(30);
1975 l_attr_mem_col varchar2(30);
1976 l_attr_mem_display_code_col varchar2(30);
1977 l_attr_mem_name_col varchar2(30);
1978 l_attrn_value_set_where varchar2(100);
1979 l_attrd_value_set_where varchar2(100);
1980 l_attrn_language_where varchar2(100);
1981
1982 l_attrn_sql_stmt varchar2(2000);
1983 l_attrd_sql_stmt varchar2(2000);
1984 l_attr_col varchar2(30);
1985
1986 l_sign_attrd_sql_stmt varchar2(2000);
1987 l_sign_attribute_id number;
1988 l_sign_attr_version_id number;
1989
1990 l_bsc_attrn_sql_stmt varchar2(2000);
1991 l_bsc_attrd_sql_stmt varchar2(2000);
1992 l_bsc_attribute_id number;
1993 l_bsc_attr_version_id number;
1994
1995 BEGIN
1996
1997 l_module_name :=
1998 G_MODULE||'.'||lower(l_api_name)||'.'||lower(p_attribute_table_name);
1999 l_function_name :=
2000 G_PACKAGE_NAME||'.'||l_api_name ||'.'||p_attribute_table_name;
2001
2002 -------------------------------------------
2003 -- Start Procedure Logging and Messaging --
2004 -------------------------------------------
2005 FEM_ENGINES_PKG.Tech_Message (
2006 p_severity => G_LOG_LEVEL_PROCEDURE
2007 ,p_module => l_module_name || '.begin'
2008 ,p_app_name => G_FEM
2009 ,p_msg_name => G_GL_POST_201
2010 ,p_token1 => 'FUNC_NAME'
2011 ,p_value1 => l_function_name
2012 ,p_token2 => 'TIME'
2013 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
2014 );
2015
2016 FEM_ENGINES_PKG.User_Message (
2017 p_app_name => G_FEM
2018 ,p_msg_name => G_GL_POST_201
2019 ,p_token1 => 'FUNC_NAME'
2020 ,p_value1 => l_function_name
2021 ,p_token2 => 'TIME'
2022 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
2023 );
2024
2025 ------------------------------------------------
2026 -- Initialize Package and Procedure Variables --
2027 ------------------------------------------------
2028 x_attrd_attr_select := null;
2029 x_attrn_attr_select := null;
2030 x_attrn_vl_attr_select := null;
2031
2032 if (p_value_set_required_flag = 'Y') then
2033 l_value_set_where := ' and attr.value_set_id = b.value_set_id';
2034 end if;
2035
2036 ------------------------------------------------------
2037 -- Loop through all Dimension Attribute Definitions --
2038 ------------------------------------------------------
2039 for attr_rec in (
2040 select a.attribute_id
2041 ,v.version_id
2042 ,a.attribute_varchar_label
2043 ,a.attribute_data_type_code
2044 ,a.attribute_value_column_name
2045 ,a.attribute_dimension_id
2046 ,dim.dimension_varchar_label as attr_dimension_varchar_label
2047 from fem_dim_attributes_b a
2048 ,fem_dim_attr_versions_b v
2049 ,fem_dimensions_b dim
2050 where a.dimension_id = p_dimension_id
2051 and a.personal_flag = 'N'
2052 and a.queryable_for_reporting_flag = 'Y'
2053 and a.allow_multiple_assignment_flag = 'N'
2054 and v.attribute_id = a.attribute_id
2055 and v.default_version_flag = 'Y'
2056 and dim.dimension_id (+) = a.attribute_dimension_id
2057 order by a.attribute_required_flag desc
2058 ,a.attribute_varchar_label
2059 ) loop
2060
2061 -- If Attribute Definition points to a Dimension, then must denormalize
2062 -- the attribute value by querying the appropriate dimension member
2063 -- TL table to get the member Name.
2064 if (attr_rec.attribute_data_type_code = 'DIMENSION') then
2065
2066 -----------------------------------------
2067 -- Get the Dimension metadata
2068 -----------------------------------------
2072 ,member_display_code_col
2069 select member_b_table_name
2070 ,member_tl_table_name
2071 ,member_col
2073 ,member_name_col
2074 ,decode(value_set_required_flag
2075 ,'Y',' and adb.value_set_id = attr.dim_attribute_value_set_id'
2076 ,null)
2077 ,decode(value_set_required_flag
2078 ,'Y',' and adtl.value_set_id = attr.dim_attribute_value_set_id'
2079 ,null)
2080 ,decode(member_tl_table_name
2081 ,member_vl_object_name,null
2082 ,' and adtl.language = tl.language')
2083 into l_attr_mem_b_tab
2084 ,l_attr_mem_tl_tab
2085 ,l_attr_mem_col
2086 ,l_attr_mem_display_code_col
2087 ,l_attr_mem_name_col
2088 ,l_attrd_value_set_where
2089 ,l_attrn_value_set_where
2090 ,l_attrn_language_where
2091 from fem_xdim_dimensions
2092 where dimension_id = attr_rec.attribute_dimension_id;
2093
2094 l_attrd_sql_stmt :=
2095 ' select adb.'||l_attr_mem_display_code_col||
2096 ' from '||l_attr_mem_b_tab||' adb'||
2097 ' ,'||p_attribute_table_name||' attr'||
2098 ' where adb.'||l_attr_mem_col||' = attr.'||attr_rec.attribute_value_column_name||
2099 l_attrd_value_set_where||
2100 ' and attr.attribute_id = '||attr_rec.attribute_id||
2101 ' and attr.version_id = '||attr_rec.version_id||
2102 ' and attr.'||p_member_col||' = b.'||p_member_col||
2103 l_value_set_where;
2104
2105 l_attrn_sql_stmt :=
2106 ' select adtl.'||l_attr_mem_name_col||
2107 ' from '||l_attr_mem_tl_tab||' adtl'||
2108 ' ,'||p_attribute_table_name||' attr'||
2109 ' where adtl.'||l_attr_mem_col||' = attr.'||attr_rec.attribute_value_column_name||
2110 l_attrn_value_set_where||
2111 l_attrn_language_where||
2112 ' and attr.attribute_id = '||attr_rec.attribute_id||
2113 ' and attr.version_id = '||attr_rec.version_id||
2114 ' and attr.'||p_member_col||' = b.'||p_member_col||
2115 l_value_set_where;
2116
2117 -- If the attribute definition points to the Extended Account Type dimension,
2118 -- then we must also include the Extended Account Type's SIGN and
2119 -- BASIC_ACCOUNT_TYPE attribute values.
2120 if (attr_rec.attr_dimension_varchar_label = 'EXTENDED_ACCOUNT_TYPE') then
2121
2122 select a.attribute_id
2123 ,v.version_id
2124 into l_sign_attribute_id
2125 ,l_sign_attr_version_id
2126 from fem_dim_attributes_b a
2127 ,fem_dim_attr_versions_b v
2128 where a.dimension_id = attr_rec.attribute_dimension_id
2129 and a.attribute_varchar_label = 'SIGN'
2130 and v.attribute_id = a.attribute_id
2131 and v.default_version_flag = 'Y';
2132
2133 l_sign_attrd_sql_stmt :=
2134 ' select ext_attr.number_assign_value'||
2135 ' from '||p_attribute_table_name||' attr'||
2136 ' ,fem_ext_acct_types_attr ext_attr'||
2137 ' where attr.attribute_id = '||attr_rec.attribute_id||
2138 ' and attr.version_id = '||attr_rec.version_id||
2139 ' and attr.'||p_member_col||' = b.'||p_member_col||
2140 l_value_set_where||
2141 ' and ext_attr.attribute_id = '||l_sign_attribute_id||
2142 ' and ext_attr.version_id = '||l_sign_attr_version_id||
2143 ' and ext_attr.ext_account_type_code = attr.'||attr_rec.attribute_value_column_name;
2144
2145 select a.attribute_id
2146 ,v.version_id
2147 into l_bsc_attribute_id
2148 ,l_bsc_attr_version_id
2149 from fem_dim_attributes_b a
2150 ,fem_dim_attr_versions_b v
2151 where a.dimension_id = attr_rec.attribute_dimension_id
2152 and a.attribute_varchar_label = 'BASIC_ACCOUNT_TYPE_CODE'
2153 and v.attribute_id = a.attribute_id
2154 and v.default_version_flag = 'Y';
2155
2156 l_bsc_attrd_sql_stmt :=
2157 ' select ext_attr.dim_attribute_varchar_member'||
2158 ' from '||p_attribute_table_name||' attr'||
2159 ' ,fem_ext_acct_types_attr ext_attr'||
2160 ' where attr.attribute_id = '||attr_rec.attribute_id||
2161 ' and attr.version_id = '||attr_rec.version_id||
2162 ' and attr.'||p_member_col||' = b.'||p_member_col||
2163 l_value_set_where||
2164 ' and ext_attr.attribute_id = '||l_bsc_attribute_id||
2165 ' and ext_attr.version_id = '||l_bsc_attr_version_id||
2166 ' and ext_attr.ext_account_type_code = attr.'||attr_rec.attribute_value_column_name;
2167
2168 l_bsc_attrn_sql_stmt :=
2169 ' select bsc_tl.basic_account_type_name'||
2170 ' from '||p_attribute_table_name||' attr'||
2171 ' ,fem_ext_acct_types_attr ext_attr'||
2172 ' ,fem_basic_acct_types_tl bsc_tl'||
2173 ' where attr.attribute_id = '||attr_rec.attribute_id||
2174 ' and attr.version_id = '||attr_rec.version_id||
2175 ' and attr.'||p_member_col||' = b.'||p_member_col||
2176 l_value_set_where||
2177 ' and ext_attr.attribute_id = '||l_bsc_attribute_id||
2178 ' and ext_attr.version_id = '||l_bsc_attr_version_id||
2179 ' and ext_attr.ext_account_type_code = attr.'||attr_rec.attribute_value_column_name||
2180 ' and bsc_tl.basic_account_type_code = ext_attr.dim_attribute_varchar_member'||
2181 ' and bsc_tl.language = tl.language';
2182
2183 end if;
2184
2188 ' select attr.'||attr_rec.attribute_value_column_name||
2185 else
2186
2187 l_attrd_sql_stmt :=
2189 ' from '||p_attribute_table_name||' attr'||
2190 ' where attr.attribute_id = '||attr_rec.attribute_id||
2191 ' and attr.version_id = '||attr_rec.version_id||
2192 ' and attr.'||p_member_col||' = b.'||p_member_col||
2193 l_value_set_where;
2194
2195 l_attrn_sql_stmt := null;
2196
2197 end if;
2198
2199 -- Use the attribute varchar label as the column name as the max length
2200 -- is 30 characters and the string is not translated. Must replace any
2201 -- non-alphanumeric characters with a underscore (_).
2202 l_attr_col := upper(translate(attr_rec.attribute_varchar_label
2203 ,' ,.<>/?;:[]{}\|-=+`~!@#$%^&*()"'''
2204 ,'________________________________'
2205 ));
2206
2207 if (G_LOG_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2208
2209 FEM_ENGINES_PKG.Tech_Message (
2210 p_severity => G_LOG_LEVEL_STATEMENT
2211 ,p_module => l_module_name||'.'||l_attr_col||'.attrn'
2212 ,p_msg_text => l_attrn_sql_stmt
2213 );
2214
2215 FEM_ENGINES_PKG.Tech_Message (
2216 p_severity => G_LOG_LEVEL_STATEMENT
2217 ,p_module => l_module_name||'.'||l_attr_col||'.attrd'
2218 ,p_msg_text => l_attrd_sql_stmt
2219 );
2220
2221 end if;
2222
2223 x_attrd_attr_select := x_attrd_attr_select||
2224 ' ,('||l_attrd_sql_stmt||') '||l_attr_col;
2225
2226 if (l_attrn_sql_stmt is not null) then
2227
2228 x_attrn_attr_select := x_attrn_attr_select||
2229 ' ,('||l_attrn_sql_stmt||') '||l_attr_col;
2230
2231 x_attrn_vl_attr_select := x_attrn_vl_attr_select||
2232 ' ,'||l_attr_col;
2233
2234 end if;
2235
2236 -- If the attribute definition points to the Extended Account Type dimension,
2237 -- then we must also include the Extended Account Type's SIGN and
2238 -- BASIC_ACCOUNT_TYPE attribute values.
2239 if (attr_rec.attr_dimension_varchar_label = 'EXTENDED_ACCOUNT_TYPE') then
2240
2241 x_attrd_attr_select := x_attrd_attr_select||
2242 ' ,('||l_sign_attrd_sql_stmt||') EXTENDED_ACCOUNT_SIGN'||
2243 ' ,('||l_bsc_attrd_sql_stmt||') BASIC_ACCOUNT_TYPE';
2244
2245 x_attrn_attr_select := x_attrn_attr_select||
2246 ' ,('||l_bsc_attrn_sql_stmt||') BASIC_ACCOUNT_TYPE';
2247
2248 x_attrn_vl_attr_select := x_attrn_vl_attr_select||
2249 ' ,BASIC_ACCOUNT_TYPE';
2250
2251 -- Set the SIGN and BASIC_ACCOUNT_TYPE attribute SQL to null
2252 l_sign_attrd_sql_stmt := null;
2253 l_bsc_attrd_sql_stmt := null;
2254 l_bsc_attrn_sql_stmt := null;
2255
2256 end if;
2257
2258 end loop;
2259
2260 -----------------------------------------
2261 -- End Procedure Logging and Messaging --
2262 -----------------------------------------
2263 FEM_ENGINES_PKG.Tech_Message (
2264 p_severity => G_LOG_LEVEL_PROCEDURE
2265 ,p_module => l_module_name || '.end'
2266 ,p_app_name => G_FEM
2267 ,p_msg_name => G_GL_POST_202
2268 ,p_token1 => 'FUNC_NAME'
2269 ,p_value1 => l_function_name
2270 ,p_token2 => 'TIME'
2271 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
2272 );
2273
2274 FEM_ENGINES_PKG.User_Message (
2275 p_app_name => G_FEM
2276 ,p_msg_name => G_GL_POST_202
2277 ,p_token1 => 'FUNC_NAME'
2278 ,p_value1 => l_function_name
2279 ,p_token2 => 'TIME'
2280 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
2281 );
2282
2283 EXCEPTION
2284
2285 when others then
2286
2287 rollback;
2288
2289 FEM_ENGINES_PKG.Tech_Message (
2290 p_severity => G_LOG_LEVEL_EXCEPTION
2291 ,p_module => l_module_name||'.others'
2292 ,p_app_name => G_FEM
2293 ,p_msg_name => G_GL_POST_215
2294 ,p_token1 => 'ERR_MSG'
2295 ,p_value1 => SQLERRM
2296 );
2297
2298 FEM_ENGINES_PKG.User_Message (
2299 p_app_name => G_FEM
2300 ,p_msg_name => G_GL_POST_215
2301 ,p_token1 => 'ERR_MSG'
2302 ,p_value1 => SQLERRM
2303 );
2304
2305 FEM_ENGINES_PKG.Tech_Message (
2306 p_severity => G_LOG_LEVEL_EXCEPTION
2307 ,p_module => l_module_name||'.others'
2308 ,p_app_name => G_FEM
2309 ,p_msg_name => G_GL_POST_203
2310 ,p_token1 => 'FUNC_NAME'
2311 ,p_value1 => l_function_name
2312 ,p_token2 => 'TIME'
2313 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
2314 );
2315
2316 FEM_ENGINES_PKG.User_Message (
2317 p_app_name => G_FEM
2318 ,p_msg_name => G_GL_POST_203
2319 ,p_token1 => 'FUNC_NAME'
2320 ,p_value1 => l_function_name
2321 ,p_token2 => 'TIME'
2322 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
2323 );
2324
2325 x_attrd_attr_select := null;
2326 x_attrn_attr_select := null;
2327 x_attrn_vl_attr_select := null;
2328
2329 END Get_Dim_Attribute_Sql;
2330
2331
2335
2332 /*===========================================================================+
2333 | FOR INTERNAL USE ONLY.
2334 +===========================================================================*/
2336 PROCEDURE Get_Seed_Dim_Attribute_Sql (
2337 p_dimension_id in number
2338 ,p_attribute_table_name in varchar2
2339 ,p_member_col in varchar2
2340 ,p_value_set_required_flag in varchar2
2341 ,p_seed_db_link in varchar2
2342 ,x_attrd_attr_select out nocopy long
2343 ,x_attrn_attr_select out nocopy long
2344 ,x_attrn_vl_attr_select out nocopy long
2345 )
2346 IS
2347
2348 -----------------------
2349 -- Declare constants --
2350 -----------------------
2351 l_api_name constant varchar2(100) := G_PACKAGE_NAME||'.Get_Seed_Dim_Attribute_Sql';
2352
2353 -------------------
2354 -- Declare types --
2355 -------------------
2356 type attribute_record is record (
2357 attribute_varchar_label fem_dim_attributes_b.attribute_varchar_label%type
2358 ,version_display_code fem_dim_attr_versions_b.version_display_code%type
2359 ,attribute_data_type_code fem_dim_attributes_b.attribute_data_type_code%type
2360 ,attribute_value_column_name fem_dim_attributes_b.attribute_value_column_name%type
2361 ,attribute_dimension_id fem_dim_attributes_b.attribute_dimension_id%type
2362 ,attr_dimension_varchar_label fem_dimensions_b.dimension_varchar_label%type
2363 );
2364
2365 type attribute_table is table of attribute_record
2366 index by binary_integer;
2367
2368 type dynamic_cursor is ref cursor;
2369
2370 -----------------------
2371 -- Declare variables --
2372 -----------------------
2373 l_module_name varchar2(200);
2374 l_function_name varchar2(200);
2375
2376 l_attr_csr dynamic_cursor;
2377 l_attr_csr_stmt varchar2(2000);
2378
2379 l_attr_tbl attribute_table;
2380
2381 l_value_set_where varchar2(100);
2382
2383 l_attr_mem_b_tab varchar2(30);
2384 l_attr_mem_tl_tab varchar2(30);
2385 l_attr_mem_col varchar2(30);
2386 l_attr_mem_display_code_col varchar2(30);
2387 l_attr_mem_name_col varchar2(30);
2388 l_attrd_value_set_where varchar2(100);
2389 l_attrn_value_set_where varchar2(100);
2390 l_attrn_language_where varchar2(100);
2391
2392 l_attrd_sql_stmt varchar2(2000);
2393 l_attrn_sql_stmt varchar2(2000);
2394 l_attr_col varchar2(30);
2395
2396 l_sign_attrd_sql_stmt varchar2(2000);
2397
2398 l_bsc_attrd_sql_stmt varchar2(2000);
2399 l_bsc_attrn_sql_stmt varchar2(2000);
2400
2401 BEGIN
2402
2403 l_module_name :=
2404 G_MODULE||'.'||lower(l_api_name)||'.'||lower(p_attribute_table_name);
2405 l_function_name :=
2406 G_PACKAGE_NAME||'.'||l_api_name ||'.'||p_attribute_table_name;
2407
2408 -------------------------------------------
2409 -- Start Procedure Logging and Messaging --
2410 -------------------------------------------
2411 FEM_ENGINES_PKG.Tech_Message (
2412 p_severity => G_LOG_LEVEL_PROCEDURE
2413 ,p_module => l_module_name || '.begin'
2414 ,p_app_name => G_FEM
2415 ,p_msg_name => G_GL_POST_201
2416 ,p_token1 => 'FUNC_NAME'
2417 ,p_value1 => l_function_name
2418 ,p_token2 => 'TIME'
2419 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
2420 );
2421
2422 FEM_ENGINES_PKG.User_Message (
2423 p_app_name => G_FEM
2424 ,p_msg_name => G_GL_POST_201
2425 ,p_token1 => 'FUNC_NAME'
2426 ,p_value1 => l_function_name
2427 ,p_token2 => 'TIME'
2428 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
2429 );
2430
2431 ------------------------------------------------
2432 -- Initialize Package and Procedure Variables --
2433 ------------------------------------------------
2434 x_attrd_attr_select := null;
2435 x_attrn_attr_select := null;
2436 x_attrn_vl_attr_select := null;
2437
2438 if (p_value_set_required_flag = 'Y') then
2439 l_value_set_where := ' and attr.value_set_id = b.value_set_id';
2440 end if;
2441
2442 ------------------------------------------------------
2443 -- Loop through all Dimension Attribute Definitions --
2444 ------------------------------------------------------
2445 l_attr_csr_stmt :=
2446 ' select a.attribute_varchar_label'||
2447 ' ,v.version_display_code'||
2448 ' ,a.attribute_data_type_code'||
2449 ' ,a.attribute_value_column_name'||
2450 ' ,a.attribute_dimension_id'||
2451 ' ,dim.dimension_varchar_label as attr_dimension_varchar_label'||
2452 ' from fem_dim_attributes_b@'||p_seed_db_link||' a'||
2453 ' ,fem_dim_attr_versions_b@'||p_seed_db_link||' v'||
2454 ' ,fem_dimensions_b@'||p_seed_db_link||' dim'||
2455 ' where a.dimension_id = :b_dimension_id'||
2456 ' and a.personal_flag = ''N'''||
2457 ' and a.queryable_for_reporting_flag = ''Y'''||
2458 ' and a.allow_multiple_assignment_flag = ''N'''||
2459 ' and v.attribute_id = a.attribute_id'||
2460 ' and v.default_version_flag = ''Y'''||
2464
2461 ' and dim.dimension_id (+) = a.attribute_dimension_id'||
2462 ' order by a.attribute_required_flag desc'||
2463 ' ,a.attribute_varchar_label';
2465 open l_attr_csr for l_attr_csr_stmt
2466 using p_dimension_id;
2467
2468 fetch l_attr_csr bulk collect into l_attr_tbl;
2469 close l_attr_csr;
2470
2471 if (l_attr_tbl.LAST is not null) then
2472
2473 for i in 1..l_attr_tbl.LAST loop
2474
2475 -- If Attribute Definition points to a Dimension, then must denormalize
2476 -- the attribute value by querying the appropriate dimension member
2477 -- TL table to get the member Name.
2478 if (l_attr_tbl(i).attribute_data_type_code = 'DIMENSION') then
2479
2480 -----------------------------------------
2481 -- Get the Dimension metadata
2482 -----------------------------------------
2483 execute immediate
2484 ' select member_b_table_name'||
2485 ' ,member_tl_table_name'||
2486 ' ,member_col'||
2487 ' ,member_display_code_col'||
2488 ' ,member_name_col'||
2489 ' ,decode(value_set_required_flag'||
2490 ' ,''Y'','' and adb.value_set_id = attr.dim_attribute_value_set_id'''||
2491 ' ,null)'||
2492 ' ,decode(value_set_required_flag'||
2493 ' ,''Y'','' and adtl.value_set_id = attr.dim_attribute_value_set_id'''||
2494 ' ,null)'||
2495 ' ,decode(member_tl_table_name'||
2496 ' ,member_vl_object_name,null'||
2497 ' ,'' and adtl.language = tl.language'')'||
2498 ' from fem_xdim_dimensions@'||p_seed_db_link||
2499 ' where dimension_id = :b_attribute_dimension_id'
2500 into l_attr_mem_b_tab
2501 ,l_attr_mem_tl_tab
2502 ,l_attr_mem_col
2503 ,l_attr_mem_display_code_col
2504 ,l_attr_mem_name_col
2505 ,l_attrd_value_set_where
2506 ,l_attrn_value_set_where
2507 ,l_attrn_language_where
2508 using l_attr_tbl(i).attribute_dimension_id;
2509
2510 l_attrd_sql_stmt :=
2511 ' select adb.'||l_attr_mem_display_code_col||
2512 ' from '||l_attr_mem_b_tab||' adb'||
2513 ' ,'||p_attribute_table_name||' attr'||
2514 ' ,fem_dim_attributes_b a'||
2515 ' ,fem_dim_attr_versions_b v'||
2516 ' where adb.'||l_attr_mem_col||' = attr.'||l_attr_tbl(i).attribute_value_column_name||
2517 l_attrd_value_set_where||
2521 ' and v.version_display_code = '''||l_attr_tbl(i).version_display_code||''''||
2518 ' and a.dimension_id = '||p_dimension_id||
2519 ' and a.attribute_varchar_label = '''||l_attr_tbl(i).attribute_varchar_label||''''||
2520 ' and v.attribute_id = a.attribute_id'||
2522 ' and attr.attribute_id = a.attribute_id'||
2523 ' and attr.version_id = v.version_id'||
2524 ' and attr.'||p_member_col||' = b.'||p_member_col||
2525 l_value_set_where;
2526
2527 l_attrn_sql_stmt :=
2528 ' select adtl.'||l_attr_mem_name_col||
2529 ' from '||l_attr_mem_tl_tab||' adtl'||
2530 ' ,'||p_attribute_table_name||' attr'||
2531 ' ,fem_dim_attributes_b a'||
2532 ' ,fem_dim_attr_versions_b v'||
2533 ' where adtl.'||l_attr_mem_col||' = attr.'||l_attr_tbl(i).attribute_value_column_name||
2534 l_attrn_value_set_where||
2535 l_attrn_language_where||
2536 ' and a.dimension_id = '||p_dimension_id||
2540 ' and attr.attribute_id = a.attribute_id'||
2537 ' and a.attribute_varchar_label = '''||l_attr_tbl(i).attribute_varchar_label||''''||
2538 ' and v.attribute_id = a.attribute_id'||
2539 ' and v.version_display_code = '''||l_attr_tbl(i).version_display_code||''''||
2541 ' and attr.version_id = v.version_id'||
2542 ' and attr.'||p_member_col||' = b.'||p_member_col||
2543 l_value_set_where;
2544
2545 -- If the attribute definition points to the Extended Account Type dimension,
2546 -- then we must also include the Extended Account Type's SIGN and
2547 -- BASIC_ACCOUNT_TYPE attribute values.
2548 if (l_attr_tbl(i).attr_dimension_varchar_label = 'EXTENDED_ACCOUNT_TYPE') then
2549
2550 l_sign_attrd_sql_stmt :=
2551 ' select ext_attr.number_assign_value'||
2552 ' from '||p_attribute_table_name||' attr'||
2553 ' ,fem_dim_attributes_b a'||
2554 ' ,fem_dim_attr_versions_b v'||
2555 ' ,fem_ext_acct_types_attr ext_attr'||
2556 ' ,fem_dim_attributes_b ext_a'||
2557 ' ,fem_dim_attr_versions_b ext_v'||
2558 ' where a.dimension_id = '||p_dimension_id||
2559 ' and a.attribute_varchar_label = '''||l_attr_tbl(i).attribute_varchar_label||''''||
2560 ' and v.attribute_id = a.attribute_id'||
2561 ' and v.version_display_code = '''||l_attr_tbl(i).version_display_code||''''||
2562 ' and attr.attribute_id = a.attribute_id'||
2563 ' and attr.version_id = v.version_id'||
2564 ' and attr.'||p_member_col||' = b.'||p_member_col||
2565 l_value_set_where||
2566 ' and ext_a.dimension_id = '||l_attr_tbl(i).attribute_dimension_id||
2567 ' and ext_a.attribute_varchar_label = ''SIGN'''||
2568 ' and ext_v.attribute_id = ext_a.attribute_id'||
2569 ' and ext_v.version_display_code = ''Default'''||
2570 ' and ext_attr.attribute_id = ext_a.attribute_id'||
2571 ' and ext_attr.version_id = ext_v.version_id'||
2572 ' and ext_attr.ext_account_type_code = attr.'||l_attr_tbl(i).attribute_value_column_name;
2573
2574 l_bsc_attrd_sql_stmt :=
2575 ' select ext_attr.dim_attribute_varchar_member'||
2576 ' from '||p_attribute_table_name||' attr'||
2577 ' ,fem_dim_attributes_b a'||
2578 ' ,fem_dim_attr_versions_b v'||
2579 ' ,fem_ext_acct_types_attr ext_attr'||
2580 ' ,fem_dim_attributes_b ext_a'||
2581 ' ,fem_dim_attr_versions_b ext_v'||
2582 ' where a.dimension_id = '||p_dimension_id||
2583 ' and a.attribute_varchar_label = '''||l_attr_tbl(i).attribute_varchar_label||''''||
2584 ' and v.attribute_id = a.attribute_id'||
2585 ' and v.version_display_code = '''||l_attr_tbl(i).version_display_code||''''||
2586 ' and attr.attribute_id = a.attribute_id'||
2587 ' and attr.version_id = v.version_id'||
2588 ' and attr.'||p_member_col||' = b.'||p_member_col||
2589 l_value_set_where||
2590 ' and ext_a.dimension_id = '||l_attr_tbl(i).attribute_dimension_id||
2591 ' and ext_a.attribute_varchar_label = ''BASIC_ACCOUNT_TYPE_CODE'''||
2592 ' and ext_v.attribute_id = ext_a.attribute_id'||
2593 ' and ext_v.version_display_code = ''Default'''||
2594 ' and ext_attr.attribute_id = ext_a.attribute_id'||
2595 ' and ext_attr.version_id = ext_v.version_id'||
2596 ' and ext_attr.ext_account_type_code = attr.'||l_attr_tbl(i).attribute_value_column_name;
2597
2598 l_bsc_attrn_sql_stmt :=
2599 ' select bsc_tl.basic_account_type_name'||
2600 ' from '||p_attribute_table_name||' attr'||
2601 ' ,fem_dim_attributes_b a'||
2602 ' ,fem_dim_attr_versions_b v'||
2603 ' ,fem_ext_acct_types_attr ext_attr'||
2604 ' ,fem_dim_attributes_b ext_a'||
2605 ' ,fem_dim_attr_versions_b ext_v'||
2606 ' ,fem_basic_acct_types_tl bsc_tl'||
2607 ' where a.dimension_id = '||p_dimension_id||
2608 ' and a.attribute_varchar_label = '''||l_attr_tbl(i).attribute_varchar_label||''''||
2609 ' and v.attribute_id = a.attribute_id'||
2610 ' and v.version_display_code = '''||l_attr_tbl(i).version_display_code||''''||
2611 ' and attr.attribute_id = a.attribute_id'||
2612 ' and attr.version_id = v.version_id'||
2613 ' and attr.'||p_member_col||' = b.'||p_member_col||
2614 l_value_set_where||
2615 ' and ext_a.dimension_id = '||l_attr_tbl(i).attribute_dimension_id||
2616 ' and ext_a.attribute_varchar_label = ''BASIC_ACCOUNT_TYPE_CODE'''||
2617 ' and ext_v.attribute_id = ext_a.attribute_id'||
2618 ' and ext_v.version_display_code = ''Default'''||
2619 ' and ext_attr.attribute_id = ext_a.attribute_id'||
2620 ' and ext_attr.version_id = ext_v.version_id'||
2621 ' and ext_attr.ext_account_type_code = attr.'||l_attr_tbl(i).attribute_value_column_name||
2622 ' and bsc_tl.basic_account_type_code = ext_attr.dim_attribute_varchar_member'||
2623 ' and bsc_tl.language = tl.language';
2624
2625 end if;
2626
2627 else
2628
2629 l_attrd_sql_stmt :=
2630 ' select attr.'||l_attr_tbl(i).attribute_value_column_name||
2631 ' from '||p_attribute_table_name||' attr'||
2632 ' ,fem_dim_attributes_b a'||
2633 ' ,fem_dim_attr_versions_b v'||
2634 ' where a.dimension_id = '||p_dimension_id||
2635 ' and a.attribute_varchar_label = '''||l_attr_tbl(i).attribute_varchar_label||''''||
2636 ' and v.attribute_id = a.attribute_id'||
2637 ' and v.version_display_code = '''||l_attr_tbl(i).version_display_code||''''||
2638 ' and attr.attribute_id = a.attribute_id'||
2639 ' and attr.version_id = v.version_id'||
2640 ' and attr.'||p_member_col||' = b.'||p_member_col||
2641 l_value_set_where;
2642
2643 l_attrn_sql_stmt := null;
2644
2645 end if;
2646
2647 -- Use the attribute varchar label as the column name as the max length
2648 -- is 30 characters and the string is not translated. Must replace any
2649 -- non-alphanumeric characters with a underscore (_).
2650 l_attr_col := upper(translate(l_attr_tbl(i).attribute_varchar_label
2651 ,' ,.<>/?;:[]{}\|-=+`~!@#$%^&*()"'''
2652 ,'________________________________'
2653 ));
2654
2655 if (G_LOG_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2656
2657 FEM_ENGINES_PKG.Tech_Message (
2658 p_severity => G_LOG_LEVEL_STATEMENT
2659 ,p_module => l_module_name||'.'||l_attr_col||'.attrd'
2660 ,p_msg_text => l_attrd_sql_stmt
2661 );
2662
2663 FEM_ENGINES_PKG.Tech_Message (
2664 p_severity => G_LOG_LEVEL_STATEMENT
2665 ,p_module => l_module_name||'.'||l_attr_col||'.attrn'
2666 ,p_msg_text => l_attrn_sql_stmt
2667 );
2668
2669 end if;
2670
2671 x_attrd_attr_select := x_attrd_attr_select||
2672 ' ,('||l_attrd_sql_stmt||') '||l_attr_col;
2673
2674 if (l_attrn_sql_stmt is not null) then
2675
2676 x_attrn_attr_select := x_attrn_attr_select||
2677 ' ,('||l_attrn_sql_stmt||') '||l_attr_col;
2678
2679 x_attrn_vl_attr_select := x_attrn_vl_attr_select||
2680 ' ,'||l_attr_col;
2681
2682 end if;
2683
2684 -- If the attribute definition points to the Extended Account Type dimension,
2685 -- then we must also include the Extended Account Type's SIGN and
2686 -- BASIC_ACCOUNT_TYPE attribute values.
2687 if (l_attr_tbl(i).attr_dimension_varchar_label = 'EXTENDED_ACCOUNT_TYPE') then
2688
2689 x_attrd_attr_select := x_attrd_attr_select||
2690 ' ,('||l_sign_attrd_sql_stmt||') EXTENDED_ACCOUNT_SIGN'||
2691 ' ,('||l_bsc_attrd_sql_stmt||') BASIC_ACCOUNT_TYPE';
2692
2693 x_attrn_attr_select := x_attrn_attr_select||
2694 ' ,('||l_bsc_attrn_sql_stmt||') BASIC_ACCOUNT_TYPE';
2695
2696 x_attrn_vl_attr_select := x_attrn_vl_attr_select||
2697 ' ,BASIC_ACCOUNT_TYPE';
2698
2699 -- Set the SIGN and BASIC_ACCOUNT_TYPE attribute SQL to null
2700 l_sign_attrd_sql_stmt := null;
2701 l_bsc_attrd_sql_stmt := null;
2702 l_bsc_attrn_sql_stmt := null;
2703
2704 end if;
2705
2706 end loop;
2707
2708 end if;
2709
2710 -----------------------------------------
2711 -- End Procedure Logging and Messaging --
2712 -----------------------------------------
2713 FEM_ENGINES_PKG.Tech_Message (
2714 p_severity => G_LOG_LEVEL_PROCEDURE
2715 ,p_module => l_module_name || '.end'
2716 ,p_app_name => G_FEM
2717 ,p_msg_name => G_GL_POST_202
2718 ,p_token1 => 'FUNC_NAME'
2719 ,p_value1 => l_function_name
2720 ,p_token2 => 'TIME'
2721 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
2722 );
2723
2724 FEM_ENGINES_PKG.User_Message (
2725 p_app_name => G_FEM
2726 ,p_msg_name => G_GL_POST_202
2727 ,p_token1 => 'FUNC_NAME'
2728 ,p_value1 => l_function_name
2729 ,p_token2 => 'TIME'
2730 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
2731 );
2732
2733 EXCEPTION
2734
2735 when others then
2736
2737 rollback;
2738
2739 FEM_ENGINES_PKG.Tech_Message (
2740 p_severity => G_LOG_LEVEL_EXCEPTION
2741 ,p_module => l_module_name||'.others'
2742 ,p_app_name => G_FEM
2743 ,p_msg_name => G_GL_POST_215
2744 ,p_token1 => 'ERR_MSG'
2745 ,p_value1 => SQLERRM
2746 );
2747
2748 FEM_ENGINES_PKG.User_Message (
2749 p_app_name => G_FEM
2750 ,p_msg_name => G_GL_POST_215
2751 ,p_token1 => 'ERR_MSG'
2752 ,p_value1 => SQLERRM
2753 );
2754
2755 FEM_ENGINES_PKG.Tech_Message (
2756 p_severity => G_LOG_LEVEL_EXCEPTION
2757 ,p_module => l_module_name||'.others'
2758 ,p_app_name => G_FEM
2759 ,p_msg_name => G_GL_POST_203
2760 ,p_token1 => 'FUNC_NAME'
2761 ,p_value1 => l_function_name
2762 ,p_token2 => 'TIME'
2763 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
2764 );
2765
2766 FEM_ENGINES_PKG.User_Message (
2767 p_app_name => G_FEM
2768 ,p_msg_name => G_GL_POST_203
2769 ,p_token1 => 'FUNC_NAME'
2770 ,p_value1 => l_function_name
2771 ,p_token2 => 'TIME'
2772 ,p_value2 => to_char(sysdate)||' '||to_char(sysdate,'HH24:MI:SS')
2773 );
2774
2775 x_attrd_attr_select := null;
2776 x_attrn_attr_select := null;
2777 x_attrn_vl_attr_select := null;
2778
2779 END Get_Seed_Dim_Attribute_Sql;
2780
2781
2782 END FEM_BI_DIMENSION_UTILS_PKG;