[Home] [Help]
PACKAGE BODY: APPS.FEM_COMP_DIM_MEMBER_LOADER_PKG
Source
1 PACKAGE BODY FEM_COMP_DIM_MEMBER_LOADER_PKG AS
2 /* $Header: femcompdimldrb.plb 120.6 2006/09/08 14:29:01 navekuma noship $ */
3
4
5 PROCEDURE Pre_Process (x_pre_process_status OUT NOCOPY VARCHAR2
6 ,p_execution_mode IN VARCHAR2
7 ,p_dimension_varchar_label IN VARCHAR2);
8
9 PROCEDURE Get_Display_Codes (p_dimension_varchar_label IN VARCHAR2,
10 p_structure_id IN NUMBER);
11
12 PROCEDURE Metadata_Initialize(p_dimension_varchar_label IN VARCHAR2);
13
14
15
16 /*===========================================================================
17 | This Procedure is to intialize the TABLE TYPE variable which stores
18 | the flex field information of the Activity and Cost Objects
19 |
20 |
21 | The TABLE TYPE variable holds the following values for each
22 | component Dimension
23 |
24 | ATTRIBUTE VALUE
25 |
26 | dimension_varchar_label Component Dimension varchar label
27 | dimension_id -999
28 | member_col null
29 | member_display_code_col null
30 | member_b_table_name null
31 | value_set_required_flag null
32 | member_sql null
33 |
34 |
35 | PARAMETER INFORMATION
36 |
37 | p_dimension_varchar_label The Varchar Label of Composite Dimension
38 |
39 | MODIFICATION HISTORY
40 | sshanmug 11-May-05 Created.
41 |
42 ============================================================================*/
43
44 PROCEDURE Metadata_Initialize(p_dimension_varchar_label IN VARCHAR2)
45
46 IS
47
48 c_proc_name CONSTANT VARCHAR2(20) := 'Metadata_Initialize';
49 i NUMBER; -- counting variable for no:of segments of Flex Field
50
51 BEGIN
52
53 fem_engines_pkg.tech_message (
54 p_severity => c_log_level_1
55 ,p_module => c_block||'.'||c_proc_name||'.Begin'
56 ,p_msg_text => 'Dimension'||p_dimension_varchar_label);
57
58 ----------------------------------------------------------------------------
59 --Clean the previous Values
60 ----------------------------------------------------------------------------
61
62 t_component_dim_dc.DELETE;
63
64 ----------------------------------------------------------------------------
65 -- "t_component_dim_dc" is a TABLE TYPE which holds the display code
66 -- values of all the component dimension members of Activity/Cost Object
67 ----------------------------------------------------------------------------
68
69 IF p_dimension_varchar_label = 'COST_OBJECT' THEN
70
71 t_component_dim_dc(1) := 'FINANCIAL_ELEM_DISPLAY_CODE';
72 t_component_dim_dc(2) := 'LEDGER_DISPLAY_CODE';
73 t_component_dim_dc(3) := 'PRODUCT_DISPLAY_CODE';
74 t_component_dim_dc(4) := 'CCTR_ORG_DISPLAY_CODE';
75 t_component_dim_dc(5) := 'CUSTOMER_DISPLAY_CODE';
76 t_component_dim_dc(6) := 'CHANNEL_DISPLAY_CODE';
77 t_component_dim_dc(7) := 'PROJECT_DISPLAY_CODE';
78 t_component_dim_dc(8) := 'USER_DIM1_DISPLAY_CODE';
79 t_component_dim_dc(9) := 'USER_DIM2_DISPLAY_CODE';
80 t_component_dim_dc(10) := 'USER_DIM3_DISPLAY_CODE';
81 t_component_dim_dc(11) := 'USER_DIM4_DISPLAY_CODE';
82 t_component_dim_dc(12) := 'USER_DIM5_DISPLAY_CODE';
83 t_component_dim_dc(13) := 'USER_DIM6_DISPLAY_CODE';
84 t_component_dim_dc(14) := 'USER_DIM7_DISPLAY_CODE';
85 t_component_dim_dc(15) := 'USER_DIM8_DISPLAY_CODE';
86 t_component_dim_dc(16) := 'USER_DIM9_DISPLAY_CODE';
87 t_component_dim_dc(17) := 'USER_DIM10_DISPLAY_CODE';
88
89 --------------------------------------------------------------------------
90 --As per Cost Object HLD, the Cost Object FF can have 17 segments and
91 --hence initializing it to 17
92 --------------------------------------------------------------------------
93 i := 17;
94
95
96 ELSIF p_dimension_varchar_label = 'ACTIVITY' THEN
97
98 t_component_dim_dc(1) := 'TASK_DISPLAY_CODE';
99 t_component_dim_dc(2) := 'CCTR_ORG_DISPLAY_CODE';
100 t_component_dim_dc(3) := 'CUSTOMER_DISPLAY_CODE';
101 t_component_dim_dc(4) := 'CHANNEL_DISPLAY_CODE';
102 t_component_dim_dc(5) := 'PRODUCT_DISPLAY_CODE';
103 t_component_dim_dc(6) := 'PROJECT_DISPLAY_CODE';
104 t_component_dim_dc(7) := 'USER_DIM1_DISPLAY_CODE';
105 t_component_dim_dc(8) := 'USER_DIM2_DISPLAY_CODE';
106 t_component_dim_dc(9) := 'USER_DIM3_DISPLAY_CODE';
107 t_component_dim_dc(10) := 'USER_DIM4_DISPLAY_CODE';
108 t_component_dim_dc(11) := 'USER_DIM5_DISPLAY_CODE';
109 t_component_dim_dc(12) := 'USER_DIM6_DISPLAY_CODE';
110 t_component_dim_dc(13) := 'USER_DIM7_DISPLAY_CODE';
111 t_component_dim_dc(14) := 'USER_DIM8_DISPLAY_CODE';
112 t_component_dim_dc(15) := 'USER_DIM9_DISPLAY_CODE';
113 t_component_dim_dc(16) := 'USER_DIM10_DISPLAY_CODE';
114
115 --------------------------------------------------------------------------
116 --As per Activity HLD, the Cost Object FF can have 16 segments and
117 --hence initializing it to 16
118 --------------------------------------------------------------------------
119 i := 16;
120
121 END IF;
122
123 ---------------------------------------------------------------------------
124 --Initialize the TABLE TYPE variable 't_metadata' with default values.
125 ---------------------------------------------------------------------------
126
127 FOR j IN 1 .. i LOOP
128
129 t_metadata(j).dimension_varchar_label := NULL;
130 t_metadata(j).member_display_code_col := t_component_dim_dc(j);
131 t_metadata(j).dimension_id := -999;
132 t_metadata(j).member_col := NULL;
133 t_metadata(j).member_b_table_name := NULL;
134 t_metadata(j).value_set_required_flag := NULL;
135 t_metadata(j).member_sql := NULL;
136
137 END LOOP;
138
139 fem_engines_pkg.tech_message (
140 p_severity => c_log_level_1
141 ,p_module => c_block||'.'||c_proc_name||'.End'
142 ,p_msg_text => 'Dimension'||p_dimension_varchar_label);
143
144 EXCEPTION
145
146 WHEN others THEN
147
148 fem_engines_pkg.tech_message (
149 p_severity => c_log_level_4
150 ,p_module => c_block||'.'||c_proc_name||'.Exception'
151 ,p_msg_text => 'Dimension'||p_dimension_varchar_label||
152 'Code'||SQLCODE||'Err'||SQLERRM);
153
154 RAISE e_terminate;
155
156 END Metadata_Initialize;
157
158
159 /*===========================================================================+
160 | PROCEDURE
161 | Get_Display_Code
162 |
163 | DESCRIPTION
164 | This procedure concatenates the individual component dimension members
165 | of the Composite Dimensions(Activity and Cost Objects).
166 |
167 | The component dimension members are concatenated to form a
168 | single Composite Dimension Member.The component dimension members
169 | are separated by the delimiter of the corresponding flex filed.
170 |
171 |ARGUMENTS : IN:
172 |
173 | p_dimension_varchar_label - Composite Dimension Name
174 | p_structure_id - FF structure Code
175 |
176 | MODIFICATION HISTORY
177 | Aturlapa 06-APR-05 Created
178 | sshanmug 11-May-05 Generalised the common piece of code both AC/CO.
179 +===========================================================================*/
180
181 PROCEDURE Get_Display_Codes (p_dimension_varchar_label IN VARCHAR2,
182 p_structure_id IN NUMBER)
183
184 IS
185
186 c_proc_name CONSTANT VARCHAR2(20) := 'Get_Display_Codes';
187
188 -- FF Details
189 l_segment_delimiter VARCHAR2(1);
190 p_ff_code_activity VARCHAR2(4) := 'FEAC';
191 p_ff_code_cost VARCHAR2(4) := 'FECO';
192
193 --Counting Variable
194 v_last_row NUMBER;
195
196 BEGIN
197
198 fem_engines_pkg.tech_message (
199 p_severity => c_log_level_1
200 ,p_module => c_block||'.'||c_proc_name||'.Begin'
201 ,p_msg_text => 'Dimension'||p_dimension_varchar_label);
202
203 ----------------------------------------------------------------------------
204 --Get the count of records in the interface table (this fetch)
205 ----------------------------------------------------------------------------
206
207 v_last_row := t_status.COUNT;
208
209 ----------------------------------------------------------------------------
210 --Clean the previous Values
211 ----------------------------------------------------------------------------
212
213 t_display_code.DELETE;
214
215 ----------------------------------------------------------------------------
216 --Get the segment delimiter
217 ----------------------------------------------------------------------------
218
219 IF p_dimension_varchar_label = 'COST_OBJECT' THEN
220
221 l_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER('FEM',p_ff_code_cost,
222 p_structure_id);
223 ELSIF p_dimension_varchar_label = 'ACTIVITY' THEN
224
225 l_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER('FEM',p_ff_code_activity,
226 p_structure_id);
227 END IF;
228
229
230
231 FOR i IN 1..v_last_row LOOP
232
233 --------------------------------------------------------------------------
234 -- Concatenate only rows with STATUS = 'LOAD'
235 --------------------------------------------------------------------------
236
237 IF t_status(i) = 'LOAD' THEN
238
239 IF p_dimension_varchar_label = 'COST_OBJECT' THEN
240
241 ----------------------------------------------------------------------
242 --Fin Elem and Ledger are mandatory for CO hence it is concatenated
243 ----------------------------------------------------------------------
244
245 t_display_code(i) := t_fin_elem_dc(i) || l_segment_delimiter;
246 t_display_code(i) := t_display_code(i) || t_ledger_dc(i);
247
248 ----------------------------------------------------------------------
249 --Concatenate other segments only if they are not null.
250 ----------------------------------------------------------------------
251
252 IF t_product_dc(i) IS NOT NULL THEN
253 t_display_code(i) := t_display_code(i) ||
254 l_segment_delimiter||t_product_dc(i);
255 END IF;
256 IF t_cctr_org_dc(i) IS NOT NULL THEN
257 t_display_code(i) := t_display_code(i) ||
258 l_segment_delimiter||t_cctr_org_dc(i);
259 END IF;
260 IF t_customer_dc(i) IS NOT NULL THEN
261 t_display_code(i) := t_display_code(i) ||
262 l_segment_delimiter||t_customer_dc(i);
263 END IF;
264 IF t_channel_dc(i) IS NOT NULL THEN
265 t_display_code(i) := t_display_code(i) ||
266 l_segment_delimiter||t_channel_dc(i);
267 END IF;
268 IF t_project_dc(i) IS NOT NULL THEN
269 t_display_code(i) := t_display_code(i) ||
270 l_segment_delimiter||t_project_dc(i);
271 END IF;
272
273 ELSIF p_dimension_varchar_label = 'ACTIVITY' THEN
274
275 ----------------------------------------------------------------------
276 --Task is mandatory for Activity hence it is concatenated
277 ----------------------------------------------------------------------
278
279 t_display_code(i) := t_task_dc(i);
280
284
281 -----------------------------------------------------------------------
282 --Concatenate other segments only if they are not null.
283 -----------------------------------------------------------------------
285 IF t_cctr_org_dc(i) IS NOT NULL THEN
286 t_display_code(i) := t_display_code(i) ||
287 l_segment_delimiter||t_cctr_org_dc(i);
288 END IF;
289 IF t_customer_dc(i) IS NOT NULL THEN
290 t_display_code(i) := t_display_code(i) ||
291 l_segment_delimiter||t_customer_dc(i);
292 END IF;
293 IF t_channel_dc(i) IS NOT NULL THEN
294 t_display_code(i) := t_display_code(i) ||
295 l_segment_delimiter||t_channel_dc(i);
296 END IF;
297 IF t_product_dc(i) IS NOT NULL THEN
298 t_display_code(i) := t_display_code(i) ||
299 l_segment_delimiter||t_product_dc(i);
300 END IF;
301 IF t_project_dc(i) IS NOT NULL THEN
302 t_display_code(i) := t_display_code(i) ||
303 l_segment_delimiter||t_project_dc(i);
304 END IF;
305
306 END IF;
307
308 ------------------------------------------------------------------------
309 -- The following component Dimensions are common for both Activity and
310 -- Cost Object. Hence it will be common code for both dimensions.
311 ------------------------------------------------------------------------
312
313 IF t_user_dim1_dc(i) IS NOT NULL THEN
314 t_display_code(i) := t_display_code(i) ||
315 l_segment_delimiter||t_user_dim1_dc(i);
316 END IF;
317 IF t_user_dim2_dc(i) IS NOT NULL THEN
318 t_display_code(i) := t_display_code(i) ||
319 l_segment_delimiter||t_user_dim2_dc(i);
320 END IF;
321 IF t_user_dim3_dc(i) IS NOT NULL THEN
322 t_display_code(i) := t_display_code(i) ||
323 l_segment_delimiter||t_user_dim3_dc(i);
324 END IF;
325 IF t_user_dim4_dc(i) IS NOT NULL THEN
326 t_display_code(i) := t_display_code(i) ||
327 l_segment_delimiter||t_user_dim4_dc(i);
328 END IF;
329 IF t_user_dim5_dc(i) IS NOT NULL THEN
330 t_display_code(i) := t_display_code(i) ||
331 l_segment_delimiter||t_user_dim5_dc(i);
332 END IF;
333 IF t_user_dim6_dc(i) IS NOT NULL THEN
334 t_display_code(i) := t_display_code(i) ||
335 l_segment_delimiter||t_user_dim6_dc(i);
336 END IF;
337 IF t_user_dim7_dc(i) IS NOT NULL THEN
338 t_display_code(i) := t_display_code(i) ||
339 l_segment_delimiter||t_user_dim7_dc(i);
340 END IF;
344 END IF;
341 IF t_user_dim8_dc(i) IS NOT NULL THEN
342 t_display_code(i) := t_display_code(i) ||
343 l_segment_delimiter||t_user_dim8_dc(i);
345 IF t_user_dim9_dc(i) IS NOT NULL THEN
346 t_display_code(i) := t_display_code(i) ||
347 l_segment_delimiter||t_user_dim9_dc(i);
348 END IF;
349 IF t_user_dim10_dc(i) IS NOT NULL THEN
350 t_display_code(i) := t_display_code(i) ||
351 l_segment_delimiter||t_user_dim10_dc(i);
352 END IF;
353
354 ELSE
355
356 t_display_code(i) := NULL;
357
358 END IF; -- STATUS = 'LOAD'
359
360 END LOOP;
361
362 -----------------------------------------------------------------------------
363 ---End Concatenate the segments
364 -----------------------------------------------------------------------------
365
366 fem_engines_pkg.tech_message (
367 p_severity => c_log_level_1
368 ,p_module => c_block||'.'||c_proc_name||'.End'
369 ,p_msg_text => 'Dimension'||p_dimension_varchar_label);
370
371 EXCEPTION
372
373 WHEN others THEN
374 fem_engines_pkg.tech_message (
375 p_severity => c_log_level_4
376 ,p_module => c_block||'.'||c_proc_name||'.Exception'
377 ,p_msg_text => 'Dimension'||p_dimension_varchar_label||
378 'Code'||SQLCODE||'Err'||SQLERRM);
379
380 RAISE e_terminate;
381
382 END Get_Display_Codes;
383
384 /*===========================================================================+
385 | PROCEDURE
386 | pre_process
387 |
388 | DESCRIPTION
389 |
390 | This Procedure is used to get the flexfield information of the composite
391 | Dimensions (Activity and Cost Object).It populates the component dimension
392 | information into a TABLE TYPE variable.
393 |
394 | SCOPE - PRIVATE
395 |
396 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
397 |
398 | ARGUMENTS : IN:
399 |
400 | p_execution_mode - 'S'(Snapshot) / 'E'(Error Reprocessing) Mode.
401 | p_dimension_varchar_label - Indicates the Dimension
402 |
403 | OUT:
404 |
405 | x_pre_process_status - Status of the Procedure.
406 |
407 | RETURNS : NONE
408 |
409 | NOTES
410 |
411 |
412 | MODIFICATION HISTORY
413 | Aturlapa 31-MAR-05 Created
414 | sshanmug 10-May-05 Incorporated the comments from Nico.
415 +===========================================================================*/
416
417 PROCEDURE Pre_Process (x_pre_process_status OUT NOCOPY VARCHAR2
418 ,p_execution_mode IN VARCHAR2
419 ,p_dimension_varchar_label IN VARCHAR2)
420 IS
421
422 c_proc_name CONSTANT varchar2(20) := 'Pre_Process';
423
424 -- variable to get the status of AC/CO FF Definition.
425 l_dim_active_flag VARCHAR2(1);
426
427 BEGIN
428
429 -- Initialize the return status
430 x_pre_process_status := 'SUCCESS';
431
432 fem_engines_pkg.tech_message (
433 p_severity => c_log_level_1
434 ,p_module => c_block||'.'||c_proc_name||'.Begin'
435 ,p_msg_text => 'Mode'||p_execution_mode
436 ||'Dimension'||p_dimension_varchar_label);
437
438 --------------------------------------------------------------------------
439 -- Check whether the AC/CO structure is defined or not
440 -- Raise the Exception if the AC / CO Structure is not freezed.
441 --------------------------------------------------------------------------
442 BEGIN
443
444 SELECT dimension_active_flag
445 INTO l_dim_active_flag
446 FROM Fem_Xdim_Dimensions_VL
447 WHERE dimension_varchar_label = p_dimension_varchar_label;
448
449 EXCEPTION
450
451 WHEN no_data_found THEN
452 fem_engines_pkg.tech_message (
453 p_severity => c_log_level_4
454 ,p_module => c_block||'.'||c_proc_name||'.Freeze_Exception'
455 ,p_msg_text => 'Dimension'||p_dimension_varchar_label||
456 'Code'||SQLCODE||'Err'||SQLERRM);
457 END;
458
459 IF l_dim_active_flag = 'N' THEN
460
461 fem_engines_pkg.tech_message (
462 p_severity => c_log_level_5
463 ,p_module => c_block||'.'||c_proc_name
464 ,p_msg_text => 'Dimension'||p_dimension_varchar_label||' Flex Field
465 Definition Not Freezed');
466
467 RAISE e_no_structure_defined;
468 END IF;
469
470 ---------------------------------------------------------------------------
471 --Clean up the MetaData Variable
472 ---------------------------------------------------------------------------
473
474 t_metadata.DELETE;
475
476 ---------------------------------------------------------------------------
480
477 --Initialize the TABLE TYPE Variable which holds the metadata information
478 ---------------------------------------------------------------------------
479 Metadata_Initialize(p_dimension_varchar_label);
481 fem_engines_pkg.tech_message (
482 p_severity => c_log_level_1
483 ,p_module => c_block||'.'||c_proc_name
484 ,p_msg_text => 'After Metadata_Initialize');
485
486 IF p_dimension_varchar_label = 'COST_OBJECT' THEN
487
488 -------------------------------------------------------------------------
489 --This loop runs for all the component Dimension members of
490 --the Cost Object Dimension and populates their details into
491 --'t_metadata' variable
492 -------------------------------------------------------------------------
493
494 FOR c_metadata_cost IN (
495 SELECT x.dimension_id
496 ,x.member_col
497 ,x.member_display_code_col
498 ,x.member_b_table_name
499 ,x.value_set_required_flag
500 FROM FEM_COLUMN_REQUIREMNT_B c
501 ,FEM_XDIM_DIMENSIONS x
502 WHERE c.dimension_id = x.dimension_id
503 AND c.cost_obj_dim_component_flag = 'Y'
504 ORDER BY 1 )
505 LOOP
506 FOR i IN 1 .. t_metadata.COUNT LOOP
507 IF c_metadata_cost.member_display_code_col = t_component_dim_dc(i) THEN
508 t_metadata(i).dimension_id := c_metadata_cost.dimension_id;
509 t_metadata(i).dimension_varchar_label := CASE t_component_dim_dc(i)
510 WHEN 'FINANCIAL_ELEM_DISPLAY_CODE' THEN 'FINANCIAL_ELEMENT'
511 WHEN 'LEDGER_DISPLAY_CODE' THEN 'LEDGER'
512 WHEN 'PRODUCT_DISPLAY_CODE' THEN 'PRODUCT'
513 WHEN 'CCTR_ORG_DISPLAY_CODE' THEN 'COMPANY_COST_CENTER_ORG'
514 WHEN 'CUSTOMER_DISPLAY_CODE' THEN 'CUSTOMER'
515 WHEN 'CHANNEL_DISPLAY_CODE' THEN 'CHANNEL'
516 WHEN 'PROJECT_DISPLAY_CODE' THEN 'PROJECT'
517 WHEN 'USER_DIM1_DISPLAY_CODE' THEN 'USER_DIM1'
518 WHEN 'USER_DIM2_DISPLAY_CODE' THEN 'USER_DIM2'
519 WHEN 'USER_DIM3_DISPLAY_CODE' THEN 'USER_DIM3'
520 WHEN 'USER_DIM4_DISPLAY_CODE' THEN 'USER_DIM4'
521 WHEN 'USER_DIM5_DISPLAY_CODE' THEN 'USER_DIM5'
522 WHEN 'USER_DIM6_DISPLAY_CODE' THEN 'USER_DIM6'
523 WHEN 'USER_DIM7_DISPLAY_CODE' THEN 'USER_DIM7'
524 WHEN 'USER_DIM8_DISPLAY_CODE' THEN 'USER_DIM8'
525 WHEN 'USER_DIM9_DISPLAY_CODE' THEN 'USER_DIM9'
526 WHEN 'USER_DIM10_DISPLAY_CODE' THEN 'USER_DIM10'
527 ELSE NULL
528 END;
529 t_metadata(i).member_col := c_metadata_cost.member_col;
530 t_metadata(i).member_b_table_name :=
531 c_metadata_cost.member_b_table_name;
532 t_metadata(i).value_set_required_flag :=
533 c_metadata_cost.value_set_required_flag;
534 t_metadata(i).member_sql :=
535 ' SELECT '||c_metadata_cost.member_col||
536 ' FROM '||c_metadata_cost.member_b_table_name||
537 ' WHERE enabled_flag = ''Y'''||
538 ' AND '||c_metadata_cost.member_display_code_col||' = :b_dc_val';
539
540 IF (c_metadata_cost.value_set_required_flag = 'Y') THEN
541 t_metadata(i).member_sql := t_metadata(i).member_sql||
542 ' AND value_set_id = :b_value_set_id';
543 END IF;
544 END IF;
545 END LOOP;
546 END LOOP;
547
548 -------------------------------------------------------------------------
549 -- Build Engine SQL --
550 -------------------------------------------------------------------------
551
552 g_select_statement :=
553 'SELECT b.rowid,'||
554 ' GLOBAL_VS_COMBO_DISPLAY_CODE,'||
555 ' financial_elem_display_code,'||
556 ' ledger_display_code,'||
557 ' product_display_code,'||
558 ' CCTR_ORG_DISPLAY_CODE,'||
559 ' customer_display_code,'||
560 ' channel_display_code,'||
561 ' project_display_code,'||
562 ' user_dim1_display_code,'||
563 ' user_dim2_display_code,'||
564 ' user_dim3_display_code,'||
565 ' user_dim4_display_code,'||
566 ' user_dim5_display_code,'||
567 ' user_dim6_display_code,'||
568 ' user_dim7_display_code,'||
569 ' user_dim8_display_code,'||
570 ' user_dim9_display_code,'||
571 ' user_dim10_display_code,'||
572 ' status'||
573 ' FROM FEM_COST_OBJECTS_T B'||
574 ' WHERE {{data_slice}} ';
575
576 ELSIF p_dimension_varchar_label = 'ACTIVITY' THEN
577
578 -------------------------------------------------------------------------
579 --Initialize the TABLE TYPE Variable which holds the metadata information
583
580 -------------------------------------------------------------------------
581
582 Metadata_Initialize(p_dimension_varchar_label);
584 -------------------------------------------------------------------------
585 --Thid loop runs for all the component Dimension members of
586 --the Activity Dimension and populates their details into
587 --'t_metadata' variable
588 -------------------------------------------------------------------------
589
590 FOR c_metadata_activity IN (
591 SELECT x.dimension_id
592 ,x.member_col
593 ,x.member_display_code_col
594 ,x.member_b_table_name
595 ,x.value_set_required_flag
596 FROM FEM_COLUMN_REQUIREMNT_B c
597 ,FEM_XDIM_DIMENSIONS x
598 WHERE c.dimension_id = x.dimension_id
599 AND c.activity_dim_component_flag = 'Y'
600 ORDER BY 1 )
601 LOOP
602 FOR i IN 1 .. t_metadata.COUNT LOOP
603 IF c_metadata_activity.member_display_code_col
604 = t_component_dim_dc(i) THEN
605 t_metadata(i).dimension_id := c_metadata_activity.dimension_id;
606 t_metadata(i).dimension_varchar_label := CASE t_component_dim_dc(i)
607 WHEN 'TASK_DISPLAY_CODE' THEN 'TASK'
608 WHEN 'CCTR_ORG_DISPLAY_CODE' THEN 'COMPANY_COST_CENTER_ORG'
609 WHEN 'CUSTOMER_DISPLAY_CODE' THEN 'CUSTOMER'
610 WHEN 'CHANNEL_DISPLAY_CODE' THEN 'CHANNEL'
611 WHEN 'PRODUCT_DISPLAY_CODE' THEN 'PRODUCT'
612 WHEN 'PROJECT_DISPLAY_CODE' THEN 'PROJECT'
613 WHEN 'USER_DIM1_DISPLAY_CODE' THEN 'USER_DIM1'
614 WHEN 'USER_DIM2_DISPLAY_CODE' THEN 'USER_DIM2'
615 WHEN 'USER_DIM3_DISPLAY_CODE' THEN 'USER_DIM3'
616 WHEN 'USER_DIM4_DISPLAY_CODE' THEN 'USER_DIM4'
617 WHEN 'USER_DIM5_DISPLAY_CODE' THEN 'USER_DIM5'
618 WHEN 'USER_DIM6_DISPLAY_CODE' THEN 'USER_DIM6'
619 WHEN 'USER_DIM7_DISPLAY_CODE' THEN 'USER_DIM7'
620 WHEN 'USER_DIM8_DISPLAY_CODE' THEN 'USER_DIM8'
621 WHEN 'USER_DIM9_DISPLAY_CODE' THEN 'USER_DIM9'
622 WHEN 'USER_DIM10_DISPLAY_CODE' THEN 'USER_DIM10'
623 ELSE NULL
624 END;
625 t_metadata(i).member_col := c_metadata_activity.member_col;
626 t_metadata(i).member_b_table_name :=
627 c_metadata_activity.member_b_table_name;
628 t_metadata(i).value_set_required_flag :=
629 c_metadata_activity.value_set_required_flag;
630 t_metadata(i).member_sql :=
631 ' SELECT '||c_metadata_activity.member_col||
632 ' FROM '||c_metadata_activity.member_b_table_name||
633 ' WHERE enabled_flag = ''Y'''||
634 ' AND '||c_metadata_activity.member_display_code_col||' = :b_dc_val';
635
636 IF (c_metadata_activity.value_set_required_flag = 'Y') THEN
637 t_metadata(i).member_sql := t_metadata(i).member_sql||
638 ' AND value_set_id = :b_value_set_id';
639 END IF;
640 END IF;
641 END LOOP;
642 END LOOP;
643
644 -------------------------------------------------------------------------
645 -- Build Engine SQL --
646 -------------------------------------------------------------------------
647
648 g_select_statement :=
649 'SELECT b.rowid,'||
650 ' GLOBAL_VS_COMBO_DISPLAY_CODE,'||
651 ' TASK_DISPLAY_CODE,'||
652 ' CCTR_ORG_DISPLAY_CODE,'||
653 ' customer_display_code,'||
654 ' channel_display_code,'||
655 ' product_display_code,'||
656 ' project_display_code,'||
657 ' user_dim1_display_code,'||
658 ' user_dim2_display_code,'||
659 ' user_dim3_display_code,'||
660 ' user_dim4_display_code,'||
661 ' user_dim5_display_code,'||
662 ' user_dim6_display_code,'||
663 ' user_dim7_display_code,'||
664 ' user_dim8_display_code,'||
665 ' user_dim9_display_code,'||
666 ' user_dim10_display_code,'||
667 ' status'||
668 ' FROM FEM_ACTIVITIES_T B'||
669 ' WHERE {{data_slice}} ';
670 END IF;
671
672 --Need to confirm with Nico
673 /* IF (p_execution_mode = 'S') THEN
674 g_select_statement := g_select_statement||' AND status = ''LOAD''';
675 END IF; */
676
677 fem_engines_pkg.tech_message (
678 p_severity => c_log_level_1
679 ,p_module => c_block||'.'||c_proc_name||'.End'
680 ,p_msg_text => 'Mode'||p_execution_mode
681 ||'Dimension'||p_dimension_varchar_label);
682
683 EXCEPTION
684
685 WHEN e_no_structure_defined THEN
686
687 fem_engines_pkg.tech_message (
688 p_severity => c_log_level_4
689 ,p_module => c_block||'.'||c_proc_name||'.Exception'
690 ,p_app_name => c_fem
691 ,p_msg_name => G_NO_STRUCTURE_DEFINED
692 ,P_TOKEN1 => 'OPERATION'
693 ,P_VALUE1 => p_dimension_varchar_label);
694
695 x_pre_process_status := 'ERROR';
696
700 p_severity => c_log_level_4
697 WHEN others THEN
698
699 fem_engines_pkg.tech_message (
701 ,p_module => c_block||'.'||c_proc_name||'.Exception'
702 ,p_msg_text => 'Mode'||p_execution_mode||
703 'Dimension'||p_dimension_varchar_label||
704 'Code'||SQLCODE||'Err'||SQLERRM);
705
706 x_pre_process_status := 'ERROR';
707
708 END Pre_Process;
709
710 /*===========================================================================+
711 | PROCEDURE
712 | process_rows
713 |
714 | DESCRIPTION
715 |
716 | This procedure is used to process all the rows in the interface
717 | table of composite dimensions (FEM_ACTIVITIES_T/FEM_COST_OBJECTS_T) and
718 | performs various validations on these records,concatenate the component
719 | dimension members and inserts only the valid records into the member table
720 | of Composite Dimensions.The invalid records will be processed in
721 | 'Error Reproceesing' Mode.
722 |
723 | SCOPE - PRIVATE
724 |
725 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
726 |
727 | ARGUMENTS :
728 |
729 | RETURNS : NONE
730 |
731 | NOTES
732 |
733 |
734 | MODIFICATION HISTORY
735 | Aturlapa 31-MAR-05 Created
736 | sshanmug 17-MAY-05 Incorporated comments from Nico
737 | sshanmug 09-Jun-05 Changed the signature of the Process_Rows as
738 | per 'Bind Variable Push MP Framework'
739 | navekuma 25-Apr-06 Bug#4736810 error counts not appearing in Concurrent Log.
740 +===========================================================================*/
741
742 PROCEDURE Process_Rows(p_eng_sql IN VARCHAR2
743 ,p_slc_pred IN VARCHAR2
744 ,p_proc_num IN VARCHAR2
745 ,p_part_code IN VARCHAR2
746 ,p_fetch_limit IN NUMBER
747 ,p_dimension_varchar_label IN VARCHAR2
748 ,p_execution_mode IN VARCHAR2
749 ,p_structure_id IN NUMBER
750 ,p_req_id IN NUMBER )
751 IS
752
753 c_proc_name CONSTANT VARCHAR2(20) := 'Process_Rows';
754
755 lv_status VARCHAR2(200);
756
757 v_fetch_limit NUMBER;
758 v_rows_processed NUMBER :=0; --Bug#4736810
759 v_rows_rejected NUMBER :=0;
760 v_rows_loaded NUMBER :=0;
761
762 v_cost_object_dc FEM_COST_OBJECTS.cost_object_display_code%TYPE;
763 v_activity_dc FEM_ACTIVITIES.activity_display_code%TYPE;
764
765 v_cost_structure_id NUMBER;
766 v_activity_structure_id NUMBER;
767
768 v_select_stmt LONG;
769 v_data_slc VARCHAR2(4000);
770
771 v_update_stmt VARCHAR2(4000);
772 v_delete_stmt VARCHAR2(4000);
773 v_member_table_name VARCHAR2(200);
774
775 v_CREATED_BY NUMBER := fnd_global.user_id;
776 v_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
777 v_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
778
779 v_last_row NUMBER;
780 v_mbr_last_row NUMBER;
781
782 --Needed for Pre_Process
783 x_pre_process_status VARCHAR2(30);
784
785 -- Follwoing 3 params are needed for FEM_DIM_UTILS_PVT.Check_Unique_Member
786 v_return_status VARCHAR2(20);
787 v_msg_count NUMBER;
788 v_msg_data VARCHAR2(200);
789
790 l_count NUMBER;-- var to keep track of invalid values for each validation
791 l_validation_sql VARCHAR2(2000);
792 -- MP variables
793 v_loop_counter NUMBER;
794 v_slc_id NUMBER;
795 v_slc_val1 VARCHAR2(100);
796 v_slc_val2 VARCHAR2(100);
797 v_slc_val3 VARCHAR2(100);
798 v_slc_val4 VARCHAR2(100);
799 v_mp_status VARCHAR2(30);
800 v_mp_message VARCHAR2(4000);
801 v_num_vals NUMBER;
802 v_part_name VARCHAR2(4000);
803 p_part_name VARCHAR2(4000);
804 v_status NUMBER;
805 v_message VARCHAR2(4000);
806
807 ----------------------------------------
808 -- Ref cursors used in this Procedure --
809 ----------------------------------------
810
811 TYPE cv_curs IS REF CURSOR;
812 cv_get_rows cv_curs;
813 cv_get_invalid_fin_elems cv_curs;
814 cv_get_invalid_ledgers cv_curs;
815 cv_get_invalid_gvscs cv_curs;
816 cv_get_invalid_comp_dims cv_curs;
817
818
819 ------------------------------------------
820 -- DML Statements used in the procedure --
821 -------------------------------------------
822
823 v_insert_cost_stmt CONSTANT LONG :=
824 'INSERT INTO FEM_COST_OBJECTS ('||
825 ' COST_OBJECT_ID, '||
826 ' COST_OBJECT_DISPLAY_CODE, '||
827 ' SUMMARY_FLAG, '||
828 ' START_DATE_ACTIVE, '||
829 ' END_DATE_ACTIVE, '||
830 ' COST_OBJECT_STRUCTURE_ID, '||
831 ' LOCAL_VS_COMBO_ID, '||
832 ' UOM_CODE, '||
833 ' FINANCIAL_ELEM_ID, '||
834 ' LEDGER_ID, '||
835 ' PRODUCT_ID, '||
836 ' COMPANY_COST_CENTER_ORG_ID, '||
837 ' CUSTOMER_ID, '||
838 ' CHANNEL_ID, '||
839 ' PROJECT_ID, '||
840 ' USER_DIM1_ID, '||
841 ' USER_DIM2_ID, '||
842 ' USER_DIM3_ID, '||
843 ' USER_DIM4_ID, '||
844 ' USER_DIM5_ID, '||
845 ' USER_DIM6_ID, '||
846 ' USER_DIM7_ID, '||
847 ' USER_DIM8_ID, '||
848 ' USER_DIM9_ID, '||
849 ' USER_DIM10_ID, '||
850 ' SEGMENT1, '||
854 ' SEGMENT5, '||
851 ' SEGMENT2, '||
852 ' SEGMENT3, '||
853 ' SEGMENT4, '||
855 ' SEGMENT6, '||
856 ' SEGMENT7, '||
857 ' SEGMENT8, '||
858 ' SEGMENT9, '||
859 ' SEGMENT10, '||
860 ' SEGMENT11, '||
861 ' SEGMENT12, '||
862 ' SEGMENT13, '||
863 ' SEGMENT14, '||
864 ' SEGMENT15, '||
865 ' SEGMENT16, '||
866 ' SEGMENT17, '||
867 ' SEGMENT18, '||
868 ' SEGMENT19, '||
869 ' SEGMENT20, '||
870 ' SEGMENT21, '||
871 ' SEGMENT22, '||
872 ' SEGMENT23, '||
873 ' SEGMENT24, '||
874 ' SEGMENT25, '||
875 ' SEGMENT26, '||
876 ' SEGMENT27, '||
877 ' SEGMENT28, '||
878 ' SEGMENT29, '||
879 ' SEGMENT30, '||
880 ' CREATION_DATE, '||
881 ' CREATED_BY, '||
882 ' LAST_UPDATED_BY, '||
883 ' LAST_UPDATE_DATE, '||
884 ' LAST_UPDATE_LOGIN, '||
885 ' OBJECT_VERSION_NUMBER, '||
886 ' ENABLED_FLAG, '||
887 ' PERSONAL_FLAG, '||
888 ' READ_ONLY_FLAG )'||
889 ' SELECT fem_cost_objects_s.nextval,'||
890 ' :b_COST_OBJECT_DISPLAY_CODE, '||
891 ' :b_SUMMARY_FLAG, '||
892 ' :b_START_DATE_ACTIVE, '||
893 ' :b_END_DATE_ACTIVE, '||
894 ' :b_COST_OBJECT_STRUCTURE_ID, '||
895 ' :b_LOCAL_VS_COMBO_ID, '||
896 ' :b_UOM_CODE, '||
897 ' :b_FINANCIAL_ELEM_ID, '||
898 ' :b_LEDGER_ID, '||
899 ' :b_PRODUCT_ID, '||
900 ' :b_COMPANY_COST_CENTER_ORG_ID, '||
901 ' :b_CUSTOMER_ID, '||
902 ' :b_CHANNEL_ID, '||
903 ' :b_PROJECT_ID, '||
904 ' :b_USER_DIM1_ID, '||
905 ' :b_USER_DIM2_ID, '||
906 ' :b_USER_DIM3_ID, '||
907 ' :b_USER_DIM4_ID, '||
908 ' :b_USER_DIM5_ID, '||
909 ' :b_USER_DIM6_ID, '||
910 ' :b_USER_DIM7_ID, '||
911 ' :b_USER_DIM8_ID, '||
912 ' :b_USER_DIM9_ID, '||
913 ' :b_USER_DIM10_ID, '||
914 ' :b_SEGMENT1, '||
915 ' :b_SEGMENT2, '||
916 ' :b_SEGMENT3, '||
917 ' :b_SEGMENT4, '||
918 ' :b_SEGMENT5, '||
919 ' :b_SEGMENT6, '||
920 ' :b_SEGMENT7, '||
921 ' :b_SEGMENT8, '||
922 ' :b_SEGMENT9, '||
923 ' :b_SEGMENT10, '||
924 ' :b_SEGMENT11, '||
925 ' :b_SEGMENT12, '||
926 ' :b_SEGMENT13, '||
927 ' :b_SEGMENT14, '||
928 ' :b_SEGMENT15, '||
929 ' :b_SEGMENT16, '||
930 ' :b_SEGMENT17, '||
931 ' :b_SEGMENT18, '||
932 ' :b_SEGMENT19, '||
933 ' :b_SEGMENT20, '||
934 ' :b_SEGMENT21, '||
935 ' :b_SEGMENT22, '||
936 ' :b_SEGMENT23, '||
937 ' :b_SEGMENT24, '||
938 ' :b_SEGMENT25, '||
939 ' :b_SEGMENT26, '||
940 ' :b_SEGMENT27, '||
941 ' :b_SEGMENT28, '||
942 ' :b_SEGMENT29, '||
943 ' :b_SEGMENT30, '||
944 ' :b_CREATION_DATE, '||
945 ' :b_CREATED_BY, '||
946 ' :b_LAST_UPDATED_BY, '||
947 ' :b_LAST_UPDATE_DATE, '||
948 ' :b_LAST_UPDATE_LOGIN, '||
949 ' :b_OBJECT_VERSION_NUMBER, '||
950 ' :b_ENABLED_FLAG, '||
951 ' :b_PERSONAL_FLAG, '||
952 ' :b_read_only_flag '||
953 ' FROM dual'||
954 ' WHERE :b_status = ''LOAD''';
955
956 v_insert_activity_stmt CONSTANT LONG :=
957 'INSERT INTO FEM_ACTIVITIES ('||
958 ' ACTIVITY_ID, '||
959 ' ACTIVITY_DISPLAY_CODE, '||
960 ' SUMMARY_FLAG, '||
961 ' START_DATE_ACTIVE, '||
962 ' END_DATE_ACTIVE, '||
963 ' ACTIVITY_STRUCTURE_ID, '||
964 ' LOCAL_VS_COMBO_ID, '||
965 ' TASK_ID, '||
966 ' COMPANY_COST_CENTER_ORG_ID, '||
967 ' CUSTOMER_ID, '||
968 ' CHANNEL_ID, '||
969 ' PRODUCT_ID, '||
970 ' PROJECT_ID, '||
971 ' USER_DIM1_ID, '||
972 ' USER_DIM2_ID, '||
973 ' USER_DIM3_ID, '||
974 ' USER_DIM4_ID, '||
975 ' USER_DIM5_ID, '||
976 ' USER_DIM6_ID, '||
977 ' USER_DIM7_ID, '||
978 ' USER_DIM8_ID, '||
979 ' USER_DIM9_ID, '||
980 ' USER_DIM10_ID, '||
981 ' SEGMENT1, '||
982 ' SEGMENT2, '||
983 ' SEGMENT3, '||
984 ' SEGMENT4, '||
985 ' SEGMENT5, '||
986 ' SEGMENT6, '||
987 ' SEGMENT7, '||
988 ' SEGMENT8, '||
989 ' SEGMENT9, '||
990 ' SEGMENT10, '||
991 ' SEGMENT11, '||
992 ' SEGMENT12, '||
996 ' SEGMENT16, '||
993 ' SEGMENT13, '||
994 ' SEGMENT14, '||
995 ' SEGMENT15, '||
997 ' SEGMENT17, '||
998 ' SEGMENT18, '||
999 ' SEGMENT19, '||
1000 ' SEGMENT20, '||
1001 ' SEGMENT21, '||
1002 ' SEGMENT22, '||
1003 ' SEGMENT23, '||
1004 ' SEGMENT24, '||
1005 ' SEGMENT25, '||
1006 ' SEGMENT26, '||
1007 ' SEGMENT27, '||
1008 ' SEGMENT28, '||
1009 ' SEGMENT29, '||
1010 ' SEGMENT30, '||
1011 ' CREATION_DATE, '||
1012 ' CREATED_BY, '||
1013 ' LAST_UPDATED_BY, '||
1014 ' LAST_UPDATE_DATE, '||
1015 ' LAST_UPDATE_LOGIN, '||
1016 ' OBJECT_VERSION_NUMBER, '||
1017 ' ENABLED_FLAG, '||
1018 ' PERSONAL_FLAG, '||
1019 ' READ_ONLY_FLAG )'||
1020 ' SELECT fem_activities_s.nextval,'||
1021 ' :b_ACTIVITY_DISPLAY_CODE, '||
1022 ' :b_SUMMARY_FLAG, '||
1023 ' :b_START_DATE_ACTIVE, '||
1024 ' :b_END_DATE_ACTIVE, '||
1025 ' :b_ACTIVITY_STRUCTURE_ID, '||
1026 ' :b_LOCAL_VS_COMBO_ID, '||
1027 ' :b_TASK_ID, '||
1028 ' :b_COMPANY_COST_CENTER_ORG_ID, '||
1029 ' :b_CUSTOMER_ID, '||
1030 ' :b_CHANNEL_ID, '||
1031 ' :b_PRODUCT_ID, '||
1032 ' :b_PROJECT_ID, '||
1033 ' :b_USER_DIM1_ID, '||
1034 ' :b_USER_DIM2_ID, '||
1035 ' :b_USER_DIM3_ID, '||
1036 ' :b_USER_DIM4_ID, '||
1037 ' :b_USER_DIM5_ID, '||
1038 ' :b_USER_DIM6_ID, '||
1039 ' :b_USER_DIM7_ID, '||
1040 ' :b_USER_DIM8_ID, '||
1041 ' :b_USER_DIM9_ID, '||
1042 ' :b_USER_DIM10_ID, '||
1043 ' :b_SEGMENT1, '||
1044 ' :b_SEGMENT2, '||
1045 ' :b_SEGMENT3, '||
1046 ' :b_SEGMENT4, '||
1047 ' :b_SEGMENT5, '||
1048 ' :b_SEGMENT6, '||
1049 ' :b_SEGMENT7, '||
1050 ' :b_SEGMENT8, '||
1051 ' :b_SEGMENT9, '||
1052 ' :b_SEGMENT10, '||
1053 ' :b_SEGMENT11, '||
1054 ' :b_SEGMENT12, '||
1055 ' :b_SEGMENT13, '||
1056 ' :b_SEGMENT14, '||
1057 ' :b_SEGMENT15, '||
1058 ' :b_SEGMENT16, '||
1059 ' :b_SEGMENT17, '||
1060 ' :b_SEGMENT18, '||
1061 ' :b_SEGMENT19, '||
1062 ' :b_SEGMENT20, '||
1063 ' :b_SEGMENT21, '||
1064 ' :b_SEGMENT22, '||
1065 ' :b_SEGMENT23, '||
1066 ' :b_SEGMENT24, '||
1067 ' :b_SEGMENT25, '||
1068 ' :b_SEGMENT26, '||
1069 ' :b_SEGMENT27, '||
1070 ' :b_SEGMENT28, '||
1071 ' :b_SEGMENT29, '||
1072 ' :b_SEGMENT30, '||
1073 ' :b_CREATION_DATE, '||
1074 ' :b_CREATED_BY, '||
1075 ' :b_LAST_UPDATED_BY, '||
1076 ' :b_LAST_UPDATE_DATE, '||
1077 ' :b_LAST_UPDATE_LOGIN, '||
1078 ' :b_OBJECT_VERSION_NUMBER, '||
1079 ' :b_ENABLED_FLAG, '||
1080 ' :b_PERSONAL_FLAG, '||
1081 ' :b_read_only_flag '||
1082 ' FROM dual'||
1083 ' WHERE :b_status = ''LOAD''';
1084
1085 v_update_cost_stmt CONSTANT VARCHAR2(4000) :=
1086 'UPDATE FEM_COST_OBJECTS_T '||
1087 ' SET status = :b_status'||
1088 ' WHERE rowid = :b_rowid';
1089
1090 v_update_activity_stmt CONSTANT VARCHAR2(4000) :=
1091 'UPDATE FEM_ACTIVITIES_T '||
1092 ' SET status = :b_status'||
1093 ' WHERE rowid = :b_rowid';
1094
1095 v_delete_cost_stmt CONSTANT VARCHAR2(4000) :=
1096 'DELETE FROM FEM_COST_OBJECTS_T '||
1097 ' WHERE rowid = :b_rowid'||
1098 ' AND :b_status = ''LOAD''';
1099
1100 v_delete_activity_stmt CONSTANT VARCHAR2(4000) :=
1101 'DELETE FROM FEM_ACTIVITIES_T '||
1102 ' WHERE rowid = :b_rowid'||
1103 ' AND :b_status = ''LOAD''';
1104
1105
1106 BEGIN
1107
1108 fem_engines_pkg.tech_message(
1109 p_severity => c_log_level_2
1110 ,p_module => c_block||'.'||c_proc_name||'.Begin'
1111 ,p_msg_text => 'Execution Mode' || p_execution_mode||
1112 'Dimension' || p_dimension_varchar_label);
1113
1114
1115 --------------------------------------------------------------------------
1116 -- This procedure gets the flexfield info of the Composite Dimension
1117 -- and populates the TABLE Type variable
1118 --------------------------------------------------------------------------
1119
1120 Pre_Process (x_pre_process_status
1121 ,p_execution_mode
1122 ,p_dimension_varchar_label);
1123
1124
1125
1126 fem_engines_pkg.tech_message(
1127 p_severity => c_log_level_5
1128 ,p_module => c_block||'.'||c_proc_name||'.After Pre_Process'
1129 ,p_msg_text => 'Pre_Process Error '||x_pre_process_status);
1130
1131
1132 --------------------------------------------------------------------------
1133 -- Check for the error message from procedure pre-process
1134 --------------------------------------------------------------------------
1135
1136
1137 IF x_pre_process_status = 'ERROR' THEN
1138
1139 fem_engines_pkg.tech_message (
1140 p_severity => c_log_level_4
1141 ,p_module => c_block||'.'||c_proc_name||'Pre_Process Error'
1142 ,p_msg_text => 'Code'||SQLCODE||'Err'||SQLERRM);
1143
1144 RAISE e_terminate;
1145
1146 END IF;
1147
1148 -----------------------------------------------------------------------------
1149 --Initialize MultiProcessing variables
1150 -----------------------------------------------------------------------------
1151
1155 v_data_slc := '1=1';
1152 v_data_slc := p_slc_pred;
1153
1154 IF v_data_slc IS NULL THEN
1156 END IF;
1157
1158 IF (p_fetch_limit IS NOT NULL) THEN
1159 v_fetch_limit := p_fetch_limit;
1160 ELSE
1161 v_fetch_limit := c_fetch_limit;
1162 END IF;
1163
1164 -----------------------------------------------------------------------------
1165 -- Add data slice to select statement
1166 -----------------------------------------------------------------------------
1167
1168 -- v_select_stmt := REPLACE(p_eng_sql,'{{data_slice}}',v_data_slc);
1169
1170 v_select_stmt := REPLACE(g_select_statement,'{{data_slice}}',v_data_slc);
1171
1172 -----------------------------------------------------------------------------
1173 --Assign the update/delete statement according to the dimension
1174 -----------------------------------------------------------------------------
1175
1176
1177 IF (p_dimension_varchar_label = 'COST_OBJECT') THEN
1178 v_update_stmt := v_update_cost_stmt;
1179 v_delete_stmt := v_delete_cost_stmt;
1180 v_member_table_name := 'fem_cost_objects_t';
1181
1182 ELSIF (p_dimension_varchar_label = 'ACTIVITY') THEN
1183 v_update_stmt := v_update_activity_stmt;
1184 v_delete_stmt := v_delete_activity_stmt;
1185 v_member_table_name := 'fem_activities_t';
1186
1187 END IF;
1188
1189
1190 fem_engines_pkg.tech_message (
1191 p_severity => c_log_level_1
1192 ,p_module => c_block||'.'||c_proc_name
1193 ,p_msg_text => 'v_update_stmt '||v_update_stmt||
1194 'v_member_table_name '||v_member_table_name);
1195
1196
1197 fem_engines_pkg.tech_message (
1198 p_severity => c_log_level_1
1199 ,p_module => c_block||'.'||c_proc_name
1200 ,p_msg_text => 'v_delete_stmt '||v_delete_stmt||
1201 'v_member_table_name '||v_member_table_name);
1202 LOOP
1203
1204 FEM_Multi_Proc_Pkg.Get_Data_Slice(
1205 x_slc_id => v_slc_id,
1206 x_slc_val1 => v_slc_val1,
1207 x_slc_val2 => v_slc_val2,
1208 x_slc_val3 => v_slc_val3,
1209 x_slc_val4 => v_slc_val4,
1210 x_num_vals => v_num_vals,
1211 x_part_name => v_part_name,
1212 p_req_id => p_req_id,
1213 p_proc_num => p_proc_num);
1214
1215 fem_engines_pkg.tech_message (
1216 p_severity => c_log_level_1
1217 ,p_module=> c_block||'.'||c_proc_name||'.Get_Data_Slice'
1218 ,p_msg_text => 'v_slc_id '||v_slc_id||
1219 'v_slc_val2'||v_slc_val2||
1220 'v_slc_val3'|| v_slc_val3||'v_slc_val4'|| v_slc_val4||
1221 'v_num_vals'|| v_num_vals||'v_part_name'|| v_part_name||
1222 'p_req_id'|| p_req_id||
1223 'p_proc_num'|| p_proc_num);
1224
1225 EXIT WHEN (v_slc_id IS NULL);
1226
1227 IF (p_part_code > 0) AND
1228 (NVL(v_part_name,'null') <> NVL(p_part_name,'null'))
1229 THEN
1230 v_part_name := p_part_name;
1231 v_select_stmt := REPLACE(v_select_stmt,'{{table_partition}}',v_part_name);
1232 END IF;
1233
1234 -----------------------------------------------------------------------------
1235 -- In Error Reprocessing mode, update status to LOAD
1236 -----------------------------------------------------------------------------
1237
1238 IF (p_execution_mode = 'E') THEN
1239
1240
1241 IF (v_num_vals = 4)
1242 THEN
1243 EXECUTE IMMEDIATE
1244 ' UPDATE '||v_member_table_name||' b'||
1245 ' SET status = ''LOAD'''||
1246 ' WHERE status <> ''LOAD'''||
1247 ' AND '||v_data_slc
1248 USING v_slc_val1,v_slc_val2,v_slc_val3,v_slc_val4;
1249 ELSIF (v_num_vals = 3)
1250 THEN
1251 EXECUTE IMMEDIATE
1252 ' UPDATE '||v_member_table_name||' b'||
1253 ' SET status = ''LOAD'''||
1254 ' WHERE status <> ''LOAD'''||
1255 ' AND '||v_data_slc
1256 USING v_slc_val1,v_slc_val2,v_slc_val3;
1257 ELSIF (v_num_vals = 2)
1258 THEN
1259 EXECUTE IMMEDIATE
1260 ' UPDATE '||v_member_table_name||' b'||
1261 ' SET status = ''LOAD'''||
1262 ' WHERE status <> ''LOAD'''||
1263 ' AND '||v_data_slc
1264 USING v_slc_val1,v_slc_val2;
1265 ELSIF (v_num_vals = 1)
1266 THEN
1267 EXECUTE IMMEDIATE
1268 ' UPDATE '||v_member_table_name||' b'||
1269 ' SET status = ''LOAD'''||
1270 ' WHERE status <> ''LOAD'''||
1271 ' AND '||v_data_slc
1272 USING v_slc_val1;
1273 ELSE
1274 EXIT;
1275 END IF;
1276 fem_engines_pkg.tech_message (
1277 p_severity => c_log_level_1
1278 ,p_module => c_block||'.'||c_proc_name||'.Error Reproceesing Mode'
1279 ,p_msg_text => 'v_data_slc '||v_data_slc||
1280 'v_member_table_name'||v_member_table_name);
1281 END IF;
1282
1283
1284 /*------------------------------------------------------------------------------
1285 VALIDATION#1
1286
1287 This validation checks whether the values in GLOBAL_VS_COMBO_DISPLAY_CODE
1288 column of interface table is valid.
1289 -------------------------------------------------------------------------------*/
1290
1291 fem_engines_pkg.tech_message (
1292 p_severity => c_log_level_1
1293 ,p_module => c_block||'.'||c_proc_name||'.Start of Validation#1'
1294 ,p_msg_text => 'v_data_slc '||v_data_slc||
1295 'v_member_table_name'||v_member_table_name);
1296
1297
1298 l_count :=0 ;
1302 ' (SELECT 1 FROM fem_global_vs_combos_b g '||
1299 l_validation_sql:= ' SELECT B.rowid '||
1300 ' FROM '||v_member_table_name||' B'||
1301 ' WHERE not exists '||
1303 ' WHERE B.global_vs_combo_display_code= g.global_vs_combo_display_code)'||
1304 ' AND B.status = ''LOAD'''||
1305 -- ' AND 1=1';
1309 IF (v_num_vals = 4)
1306 ' AND '||v_data_slc;
1307
1308
1310 THEN
1311 OPEN cv_get_invalid_gvscs FOR
1312 l_validation_sql
1313 USING v_slc_val1,v_slc_val2,v_slc_val3,v_slc_val4;
1314 ELSIF (v_num_vals = 3)
1315 THEN
1316 OPEN cv_get_invalid_gvscs FOR
1317 l_validation_sql
1318 USING v_slc_val1,v_slc_val2,v_slc_val3;
1319 ELSIF (v_num_vals = 2)
1320 THEN
1321 OPEN cv_get_invalid_gvscs FOR
1322 l_validation_sql
1323 USING v_slc_val1,v_slc_val2;
1324 ELSIF (v_num_vals = 1)
1325 THEN
1326 OPEN cv_get_invalid_gvscs FOR
1327 l_validation_sql
1328 USING v_slc_val1;
1329 ELSE
1330 EXIT;
1331 END IF;
1332
1333 LOOP
1334 EXIT WHEN cv_get_invalid_gvscs%NOTFOUND;
1335 FETCH cv_get_invalid_gvscs BULK COLLECT
1336 INTO t_rowid
1337 LIMIT v_fetch_limit;
1338
1339 -- local var which holds the no : of invalid values
1340
1341 l_count := l_count + t_rowid.COUNT;
1342
1343 --Get the count of no : of records this fetch
1344 v_last_row := t_rowid.COUNT;
1345
1346 IF (v_last_row IS NOT NULL) THEN
1347 lv_status := 'INVALID_GVSC';
1348
1349 FORALL i IN 1..v_last_row
1350 EXECUTE IMMEDIATE v_update_stmt USING lv_status,t_rowid(i);
1351 t_rowid.DELETE;
1352 COMMIT;
1353
1354
1355 END IF; -- v_last_row
1356
1357 END LOOP;
1358
1359 fem_engines_pkg.tech_message (
1360 p_severity => c_log_level_1
1361 ,p_module => c_block||'.'||c_proc_name||'.Validation#1 - End'
1362 ,p_msg_text => 'v_data_slc '||v_data_slc||
1363 'v_member_table_name'||v_member_table_name||
1364 'No:of Invalid Records'|| l_count);
1365
1366
1367 CLOSE cv_get_invalid_gvscs;
1368
1369 /*------------------------------------------------------------------------------
1370 VALIDATION#2:
1371
1372 ***This is only for Cost Object Dimension ***
1373
1374 1. The members of the Financial Element should have the value of
1375 COST_OBJECT_UNIT_FLAG attribute as 'Y' and 'DATA_TYPE_CODE' attribute
1376 as 'RATE"
1377
1378 2.The Members of Ledger Dimension and Global_VS_Combo Column should be in sync.
1379 -------------------------------------------------------------------------------*/
1380
1381 IF p_dimension_varchar_label = 'COST_OBJECT' THEN
1382
1383 FOR c_attr IN (
1384 SELECT a.attribute_id
1385 ,v.version_id
1386 ,a.attribute_varchar_label
1387 ,a.dimension_id
1388 FROM fem_dim_attributes_vl a
1389 ,fem_dim_attr_versions_vl v
1390 WHERE a.attribute_id = v.attribute_id
1391 AND v.default_version_flag = 'Y'
1392 AND (
1393 (a.attribute_varchar_label IN ('COST_OBJECT_UNIT_FLAG','DATA_TYPE_CODE')
1394 AND a.dimension_id = 12) -- Financial Element
1395 OR
1396 (a.attribute_varchar_label IN ('GLOBAL_VS_COMBO')
1397 AND a.dimension_id = 7) -- Ledger
1398 )
1399 AND v.default_version_flag = 'Y' )
1400 LOOP
1401 IF c_attr.dimension_id = 12 THEN -- Financial Element
1402
1403 IF c_attr.attribute_varchar_label = 'COST_OBJECT_UNIT_FLAG' THEN
1404
1405 l_count :=0 ;
1406 l_validation_sql:= ' SELECT b.rowid'||
1407 ' FROM fem_cost_objects_t b'||
1408 ' ,fem_fin_elems_attr a'||
1409 ' ,fem_fin_elems_vl m'||
1410 ' WHERE a.financial_elem_id = m.financial_elem_id'||
1411 ' AND m.financial_elem_display_code = b.financial_elem_display_code'||
1412 ' AND a.attribute_id = '||c_attr.attribute_id||
1413 ' AND a.version_id = '||c_attr.version_id||
1414 ' AND a.dim_attribute_varchar_member <> ''Y'''||
1415 ' AND b.status = ''LOAD'''||
1416 ' AND '||v_data_slc;
1417
1418 IF (v_num_vals = 4)
1419 THEN
1420 OPEN cv_get_invalid_fin_elems FOR
1421 l_validation_sql
1422 USING v_slc_val1,v_slc_val2,v_slc_val3,v_slc_val4;
1423 ELSIF (v_num_vals = 3)
1424 THEN
1425 OPEN cv_get_invalid_fin_elems FOR
1426 l_validation_sql
1427 USING v_slc_val1,v_slc_val2,v_slc_val3;
1431 l_validation_sql
1428 ELSIF (v_num_vals = 2)
1429 THEN
1430 OPEN cv_get_invalid_fin_elems FOR
1432 USING v_slc_val1,v_slc_val2;
1433 ELSIF (v_num_vals = 1)
1434 THEN
1435 OPEN cv_get_invalid_fin_elems FOR
1436 l_validation_sql
1437 USING v_slc_val1;
1438 ELSE
1439 EXIT;
1440 END IF;
1441
1442 LOOP
1443
1444 EXIT WHEN cv_get_invalid_fin_elems%NOTFOUND;
1445 FETCH cv_get_invalid_fin_elems BULK COLLECT
1446 INTO t_rowid
1447 LIMIT v_fetch_limit;
1448
1449 -- local var which holds the no : of invalid values
1450
1451 l_count := l_count + t_rowid.COUNT;
1452
1453 v_last_row := t_rowid.COUNT;
1454
1455 IF (v_last_row IS NOT NULL) THEN
1456 lv_status := 'INVALID_FIN_ELEMS_NOT_COUC_FLAG';
1457 FORALL i IN 1..v_last_row
1458 EXECUTE IMMEDIATE v_update_stmt USING lv_status,t_rowid(i);
1459 END IF; -- v_last_row
1460
1461 END LOOP;
1462
1463 CLOSE cv_get_invalid_fin_elems;
1464
1465 fem_engines_pkg.tech_message (
1466 p_severity => c_log_level_1
1467 ,p_module => c_block||'.'||c_proc_name||'.Validation#2.1'
1468 ,p_msg_text => 'v_data_slc '||v_data_slc||
1469 'v_member_table_name'||v_member_table_name||
1470 'No:of Invalid Records'|| l_count);
1471
1472 /* ELSE -- DATA_TYPE_CODE = 'RATE'
1473
1474 l_count :=0 ;
1475
1476 OPEN cv_get_invalid_fin_elems FOR
1477 ' SELECT b.rowid'||
1478 ' FROM fem_cost_objects_t b'||
1479 ' ,fem_fin_elems_attr a'||
1480 ' ,fem_fin_elems_vl m'||
1481 ' WHERE a.financial_elem_id = m.financial_elem_id'||
1482 ' AND m.financial_elem_display_code = b.financial_elem_display_code'||
1483 ' AND a.attribute_id = '||c_attr.attribute_id||
1484 ' AND a.version_id = '||c_attr.version_id||
1488
1485 ' AND a.dim_attribute_varchar_member <> ''RATE'''||
1486 ' AND b.status = ''LOAD'''||
1487 ' AND '||v_data_slc;
1489 LOOP
1490
1491 EXIT WHEN cv_get_invalid_fin_elems%NOTFOUND;
1492 FETCH cv_get_invalid_fin_elems BULK COLLECT
1493 INTO t_rowid
1494 LIMIT v_fetch_limit;
1495
1496 -- local var which holds the no : of invalid values
1497
1498 l_count := l_count + t_rowid.COUNT;
1499
1500 v_last_row := t_rowid.COUNT;
1501 IF (v_last_row IS NOT NULL) THEN
1502 lv_status := 'INVALID_FIN_ELEMS_NOT_RATE_DATA_TYPE';
1503 FORALL i IN 1..v_last_row
1504 EXECUTE IMMEDIATE v_update_stmt USING lv_status,t_rowid(i);
1505 END IF; -- v_last_row
1506
1507 END LOOP;
1508
1509 CLOSE cv_get_invalid_fin_elems;
1510
1511 fem_engines_pkg.tech_message (
1512 p_severity => c_log_level_1
1513 ,p_module => c_block||'.'||c_proc_name||'.Validation#2.2'
1514 ,p_msg_text => 'v_data_slc '||v_data_slc||
1515 'v_member_table_name'||v_member_table_name||
1516 'No:of Invalid Records'|| l_count);*/
1517
1518 END IF; -- attribute_varchar_label = 'COST_OBJECT_UNIT_FLAG'
1519
1520 ELSE --- if the dimension_id = 7(ledger)
1521
1522 l_count :=0 ;
1523 l_validation_sql := ' SELECT b.rowid'||
1524 ' FROM fem_cost_objects_t b'||
1525 ' WHERE NOT EXISTS ('||
1526 ' SELECT 1'||
1527 ' FROM fem_ledgers_b l'||
1528 ' ,fem_ledgers_attr a'||
1529 ' ,fem_global_vs_combos_b g'||
1530 ' WHERE l.ledger_display_code = b.ledger_display_code'||
1531 ' AND a.ledger_id = l.ledger_id'||
1532 ' AND a.attribute_id = '||c_attr.attribute_id||
1533 ' AND a.version_id = '||c_attr.version_id||
1534 ' AND a.dim_attribute_numeric_member = g.global_vs_combo_id'||
1535 ' AND g.global_vs_combo_display_code = b.global_vs_combo_display_code'||
1536 ' )'||
1537 ' AND status = ''LOAD'''||
1538 ' AND '||v_data_slc;
1539
1540
1541 IF (v_num_vals = 4)
1542 THEN
1543 OPEN cv_get_invalid_ledgers FOR
1544 l_validation_sql
1545 USING v_slc_val1,v_slc_val2,v_slc_val3,v_slc_val4;
1546 ELSIF (v_num_vals = 3)
1547 THEN
1548 OPEN cv_get_invalid_ledgers FOR
1549 l_validation_sql
1550 USING v_slc_val1,v_slc_val2,v_slc_val3;
1551 ELSIF (v_num_vals = 2)
1552 THEN
1553 OPEN cv_get_invalid_ledgers FOR
1554 l_validation_sql
1555 USING v_slc_val1,v_slc_val2;
1556 ELSIF (v_num_vals = 1)
1557 THEN
1558 OPEN cv_get_invalid_ledgers FOR
1559 l_validation_sql
1560 USING v_slc_val1;
1561 ELSE
1562 EXIT;
1563 END IF;
1564 LOOP
1565
1566 EXIT WHEN cv_get_invalid_ledgers%NOTFOUND;
1567 FETCH cv_get_invalid_ledgers BULK COLLECT
1571 -- local var which holds the no : of invalid values
1568 INTO t_rowid
1569 LIMIT v_fetch_limit;
1570
1572
1573 l_count := l_count + t_rowid.COUNT;
1574
1575 v_last_row := t_rowid.COUNT;
1576
1577 IF (v_last_row IS NOT NULL) THEN
1578 lv_status := 'INVALID_LEDGER_FOR_GVSC';
1579 FORALL i IN 1..v_last_row
1580 EXECUTE IMMEDIATE v_update_stmt USING lv_status,t_rowid(i);
1581 END IF; -- v_last_row
1582
1583 END LOOP;
1584
1585 CLOSE cv_get_invalid_ledgers;
1586
1587 fem_engines_pkg.tech_message (
1588 p_severity => c_log_level_1
1589 ,p_module=> c_block||'.'||c_proc_name||'.Validation#2.3'
1590 ,p_msg_text => 'v_data_slc '||v_data_slc||
1591 'v_member_table_name'||v_member_table_name||
1592 'No:of Invalid Records'|| l_count);
1593
1594 END IF; -- dimension_id = 7(ledger)
1595
1596 t_rowid.DELETE;
1597
1598 END LOOP; -- c_attr
1599
1600 END IF; --- p_dimension_varchar_label = 'COST_OBJECT'
1601
1602 COMMIT;
1603 /*------------------------------------------------------------------------------
1604 VALIDATION#3:
1605
1606 --This validation ensures that the strucutre of the composite
1607 --dimension flex field is in synch with the records of the interface table.
1608 --(ie) Those component dimensions defined as a part of FlexField should only
1609 --have the 'DISPLAY_CODE' values in the interface table.Other component
1610 --dimension's display code values should be null.Moreover the display code of
1611 --component dimension which is a part of Flex Field Definition should not be
1612 -- null(Inverse of the above scenario).
1613
1614 ------------------------------------------------------------------------------*/
1615
1616 l_count := 0;
1617
1618 FOR i IN 1..t_metadata.COUNT LOOP -- Loop within the component dimensions
1619
1620 IF (t_metadata(i).dimension_id <> -999) THEN
1621 l_validation_sql:= ' SELECT b.rowid '||
1622 ' FROM '||v_member_table_name||' b'||
1623 ' WHERE '||t_metadata(i).member_display_code_col||' is null'||
1624 ' AND status = ''LOAD'''||
1625 ' AND '||v_data_slc;
1626
1627
1628 IF (v_num_vals = 4)
1629 THEN
1630 OPEN cv_get_invalid_comp_dims FOR
1631 l_validation_sql
1632 USING v_slc_val1,v_slc_val2,v_slc_val3,v_slc_val4;
1633 ELSIF (v_num_vals = 3)
1634 THEN
1635 OPEN cv_get_invalid_comp_dims FOR
1636 l_validation_sql
1637 USING v_slc_val1,v_slc_val2,v_slc_val3;
1638 ELSIF (v_num_vals = 2)
1639 THEN
1640 OPEN cv_get_invalid_comp_dims FOR
1641 l_validation_sql
1642 USING v_slc_val1,v_slc_val2;
1643 ELSIF (v_num_vals = 1)
1644 THEN
1645 OPEN cv_get_invalid_comp_dims FOR
1646 l_validation_sql
1647 USING v_slc_val1;
1648 ELSE
1649 EXIT;
1650 END IF;
1651
1652 ELSE
1653 l_validation_sql:= ' SELECT b.rowid '||
1654 ' FROM '||v_member_table_name||' b'||
1655 ' WHERE '||t_metadata(i).member_display_code_col||' is not null'||
1656 ' AND status = ''LOAD'''||
1657 ' AND '||v_data_slc;
1658
1659
1660 IF (v_num_vals = 4)
1661 THEN
1662 OPEN cv_get_invalid_comp_dims FOR
1663 l_validation_sql
1664 USING v_slc_val1,v_slc_val2,v_slc_val3,v_slc_val4;
1665 ELSIF (v_num_vals = 3)
1666 THEN
1667 OPEN cv_get_invalid_comp_dims FOR
1668 l_validation_sql
1669 USING v_slc_val1,v_slc_val2,v_slc_val3;
1670 ELSIF (v_num_vals = 2)
1671 THEN
1672 OPEN cv_get_invalid_comp_dims FOR
1673 l_validation_sql
1674 USING v_slc_val1,v_slc_val2;
1675 ELSIF (v_num_vals = 1)
1676 THEN
1677 OPEN cv_get_invalid_comp_dims FOR
1678 l_validation_sql
1679 USING v_slc_val1;
1680 ELSE
1681 EXIT;
1682 END IF;
1683
1684 END IF;
1685
1686 LOOP
1687 EXIT WHEN cv_get_invalid_comp_dims%NOTFOUND;
1688 FETCH cv_get_invalid_comp_dims BULK COLLECT
1689 INTO t_rowid
1690 LIMIT v_fetch_limit;
1691
1692 -- local var which holds the no : of invalid values
1693
1694 l_count := l_count + t_rowid.COUNT;
1695
1696 v_last_row := t_rowid.COUNT;
1697
1698 IF (v_last_row IS NOT NULL) THEN
1699
1700 lv_status := 'INVALID_STR_'||t_metadata(i).member_display_code_col;
1701
1702 FORALL i IN 1..v_last_row
1703 EXECUTE IMMEDIATE v_update_stmt USING lv_status,t_rowid(i);
1704 t_rowid.DELETE;
1705
1706 END IF; -- v_last_row
1707
1708 END LOOP; -- cursor
1709
1710 CLOSE cv_get_invalid_comp_dims;
1711
1712 -- END IF;
1713
1714 END LOOP; -- FOR LOOP
1715
1716 COMMIT;
1717
1718 fem_engines_pkg.tech_message (
1719 p_severity => c_log_level_1
1720 ,p_module=> c_block||'.'||c_proc_name||'.Validation#3'
1721 ,p_msg_text => 'v_data_slc '||v_data_slc||
1722 'v_member_table_name'||v_member_table_name||
1723 'No:of Invalid Records'|| l_count);
1724
1725 ------------------------------------------------------------------------------
1726 -- end of Validation # 3
1727 ------------------------------------------------------------------------------
1728
1732 ------------------------------------------------------------------------------
1729 ------------------------------------------------------------------------------
1730 -- Start processing Rows from Interface table.
1731 -- Open the cursor to get the rows from interface table
1733 --Bind Variable Push
1734
1735
1736 IF (v_num_vals = 4)
1737 THEN
1738 OPEN cv_get_rows FOR
1739 v_select_stmt
1740 USING v_slc_val1,v_slc_val2,v_slc_val3,v_slc_val4;
1741 ELSIF (v_num_vals = 3)
1742 THEN
1743 OPEN cv_get_rows FOR
1744 v_select_stmt
1745 USING v_slc_val1,v_slc_val2,v_slc_val3;
1746 ELSIF (v_num_vals = 2)
1747 THEN
1748 OPEN cv_get_rows FOR
1749 v_select_stmt
1750 USING v_slc_val1,v_slc_val2;
1751 ELSIF (v_num_vals = 1)
1752 THEN
1753 OPEN cv_get_rows FOR
1754 v_select_stmt
1755 USING v_slc_val1;
1756 ELSE
1757 EXIT;
1758 END IF;
1759
1760
1761 LOOP
1762 EXIT WHEN cv_get_rows%NOTFOUND;
1763 IF p_dimension_varchar_label = 'COST_OBJECT' THEN
1764 FETCH cv_get_rows BULK COLLECT
1765 INTO t_rowid,
1766 t_global_vs_combo_dc,
1767 t_fin_elem_dc,
1768 t_ledger_dc,
1769 t_product_dc,
1770 t_cctr_org_dc,
1771 t_customer_dc,
1772 t_channel_dc,
1773 t_project_dc,
1774 t_user_dim1_dc,
1775 t_user_dim2_dc,
1776 t_user_dim3_dc,
1777 t_user_dim4_dc,
1778 t_user_dim5_dc,
1779 t_user_dim6_dc,
1780 t_user_dim7_dc,
1781 t_user_dim8_dc,
1782 t_user_dim9_dc,
1783 t_user_dim10_dc,
1784 t_status
1785 LIMIT v_fetch_limit;
1786
1787 ELSIF p_dimension_varchar_label = 'ACTIVITY' THEN
1788 FETCH cv_get_rows BULK COLLECT
1789 INTO t_rowid,
1790 t_global_vs_combo_dc,
1791 t_task_dc,
1792 t_cctr_org_dc,
1793 t_customer_dc,
1794 t_channel_dc,
1795 t_product_dc,
1796 t_project_dc,
1797 t_user_dim1_dc,
1798 t_user_dim2_dc,
1799 t_user_dim3_dc,
1800 t_user_dim4_dc,
1801 t_user_dim5_dc,
1802 t_user_dim6_dc,
1803 t_user_dim7_dc,
1804 t_user_dim8_dc,
1805 t_user_dim9_dc,
1806 t_user_dim10_dc,
1807 t_status
1808 LIMIT v_fetch_limit;
1809
1810 END IF; -- End of Fetch rows
1811
1812 -- Get the no:of rows this fetch
1813 v_mbr_last_row := t_status.COUNT;
1814
1815 fem_engines_pkg.tech_message (
1816 p_severity => c_log_level_1
1817 ,p_module=> c_block||'.'||c_proc_name||'.Validation#3'
1818 ,p_msg_text => 'v_data_slc '||v_data_slc||
1819 'v_member_table_name'||v_member_table_name||
1820 'No:of Invalid Records'|| l_count);
1821
1822 /* IF (x_rows_loaded IS NULL) THEN
1823 x_rows_loaded := 0;
1824 END IF;
1825
1826 x_rows_loaded := x_rows_loaded + v_mbr_last_row; */
1827
1828
1829 /*------------------------------------------------------------------------------
1830 VALIDATION#4:
1831 -- The Member Ids of component dimension members are populated in the
1832 -- following piece of code.
1833 -- If the member is not present in the component dimension member table
1834 -- that row will be marked as invalid.
1835 ------------------------------------------------------------------------------*/
1836
1837 -- Loop within the number of records in interface table
1838 FOR j IN 1..v_mbr_last_row LOOP
1839 -- Initialize the TABLE TYPE Varible
1840 t_channel_id(j) := NULL;
1841 t_cctr_org_id(j) := NULL;
1842 t_customer_id(j) := NULL;
1843 t_fin_elem_id(j) := NULL;
1844 t_ledger_id(j) := NULL;
1845 t_product_id(j) := NULL;
1846 t_project_id(j) := NULL;
1847 t_task_id(j) := NULL;
1848 t_user_dim1_id(j) := NULL;
1849 t_user_dim2_id(j) := NULL;
1850 t_user_dim3_id(j) := NULL;
1851 t_user_dim4_id(j) := NULL;
1852 t_user_dim5_id(j) := NULL;
1853 t_user_dim6_id(j) := NULL;
1854 t_user_dim7_id(j) := NULL;
1855 t_user_dim8_id(j) := NULL;
1856 t_user_dim9_id(j) := NULL;
1857 t_user_dim10_id(j):= NULL;
1858
1859 t_global_vs_combo_id(j) := -1;
1860
1861 FOR i IN 1..t_metadata.COUNT LOOP
1862
1863 IF (t_metadata(i).dimension_id <> '-999') AND (t_status(j) = 'LOAD')THEN
1864
1865 FOR c_value_set IN (
1866 SELECT g.dimension_id
1867 ,g.value_set_id
1868 ,g.global_vs_combo_id
1869 FROM FEM_GLOBAL_VS_COMBO_DEFS g,
1870 FEM_GLOBAL_VS_COMBOS_b m
1871 WHERE g.global_vs_combo_id = m.global_vs_combo_id
1872 AND g.dimension_id = t_metadata(i).dimension_id
1873 AND m.global_vs_combo_display_code = t_global_vs_combo_dc(j)
1874 ORDER BY 1)
1875 LOOP
1876
1877 t_global_vs_combo_id(j) := c_value_set.global_vs_combo_id;
1878
1879 --Ledger is not handled here as it is non VSR Dimension
1880
1881 CASE t_metadata(i).member_display_code_col
1882
1883 WHEN 'FINANCIAL_ELEM_DISPLAY_CODE' THEN
1884
1885 BEGIN
1886 EXECUTE IMMEDIATE t_metadata(i).member_sql
1887 INTO t_fin_elem_id(j)
1891 WHEN no_data_found THEN
1888 USING t_fin_elem_dc(j),c_value_set.value_set_id;
1889
1890 EXCEPTION
1892 t_status(j) := 'INVALID_FIN_ELEM';
1893 END;
1894
1895 WHEN 'TASK_DISPLAY_CODE' THEN
1896
1897 BEGIN
1898 EXECUTE IMMEDIATE t_metadata(i).member_sql
1899 INTO t_task_id(j)
1900 USING t_task_dc(j),c_value_set.value_set_id;
1901
1902 EXCEPTION
1903 WHEN no_data_found THEN
1904 t_status(j) := 'INVALID_TASK';
1905 END;
1906
1907 WHEN 'CHANNEL_DISPLAY_CODE' THEN
1908
1909 BEGIN
1910 EXECUTE IMMEDIATE t_metadata(i).member_sql
1911 INTO t_channel_id(j)
1912 USING t_channel_dc(j), c_value_set.value_set_id;
1913
1914 EXCEPTION
1915 WHEN no_data_found THEN
1916 t_status(j) := 'INVALID_CHANNEL';
1917 END;
1918
1919 WHEN 'CCTR_ORG_DISPLAY_CODE' THEN
1920
1921 BEGIN
1922 EXECUTE IMMEDIATE t_metadata(i).member_sql
1923 INTO t_cctr_org_id(j)
1924 USING t_cctr_org_dc(j),c_value_set.value_set_id;
1925
1926 EXCEPTION
1927 WHEN no_data_found THEN
1928 t_status(j) := 'INVALID_CCTR_ORG';
1929 END;
1930
1931 WHEN 'CUSTOMER_DISPLAY_CODE' THEN
1932
1933 BEGIN
1934 EXECUTE IMMEDIATE t_metadata(i).member_sql
1935 INTO t_customer_id(j)
1936 USING t_customer_dc(j),c_value_set.value_set_id;
1937
1938 EXCEPTION
1939 WHEN no_data_found THEN
1940 t_status(j) := 'INVALID_CUSTOMER';
1941 END;
1942
1943 WHEN 'PRODUCT_DISPLAY_CODE' THEN
1944
1945 BEGIN
1946 EXECUTE IMMEDIATE t_metadata(i).member_sql
1947 INTO t_product_id(j)
1948 USING t_product_dc(j),c_value_set.value_set_id;
1949
1950 EXCEPTION
1951 WHEN no_data_found THEN
1952 t_status(j) := 'INVALID_PRODUCT';
1953 END;
1954
1955 WHEN 'PROJECT_DISPLAY_CODE' THEN
1956
1957 BEGIN
1958 EXECUTE IMMEDIATE t_metadata(i).member_sql
1959 INTO t_project_id(j)
1960 USING t_project_dc(j),c_value_set.value_set_id;
1961
1962 EXCEPTION
1963 WHEN no_data_found THEN
1964 t_status(j) := 'INVALID_PROJECT';
1965 END;
1966
1967 WHEN 'USER_DIM1_DISPLAY_CODE' THEN
1968
1969 BEGIN
1970 EXECUTE IMMEDIATE t_metadata(i).member_sql
1971 INTO t_user_dim1_id(j)
1972 USING t_user_dim1_dc(j),c_value_set.value_set_id;
1973
1974 EXCEPTION
1975 WHEN no_data_found THEN
1976 t_status(j) := 'INVALID_USER_DIM1';
1977 END;
1978
1979 WHEN 'USER_DIM2_DISPLAY_CODE' THEN
1980
1981 BEGIN
1982 EXECUTE IMMEDIATE t_metadata(i).member_sql
1983 INTO t_user_dim2_id(j)
1984 USING t_user_dim2_dc(j),c_value_set.value_set_id;
1985
1986 EXCEPTION
1987 WHEN no_data_found THEN
1988 t_status(j) := 'INVALID_USER_DIM2';
1989 END;
1990
1991 WHEN 'USER_DIM3_DISPLAY_CODE' THEN
1992
1993 BEGIN
1994 EXECUTE IMMEDIATE t_metadata(i).member_sql
1995 INTO t_user_dim3_id(j)
1996 USING t_user_dim3_dc(j),c_value_set.value_set_id;
1997
1998 EXCEPTION
1999 WHEN no_data_found THEN
2000 t_status(j) := 'INVALID_USER_DIM3';
2001 END;
2002
2003 WHEN 'USER_DIM4_DISPLAY_CODE' THEN
2004
2005 BEGIN
2006 EXECUTE IMMEDIATE t_metadata(i).member_sql
2007 INTO t_user_dim4_id(j)
2008 USING t_user_dim4_dc(j),c_value_set.value_set_id;
2009
2010 EXCEPTION
2011 WHEN no_data_found THEN
2012 t_status(j) := 'INVALID_USER_DIM4';
2013 END;
2014
2015 WHEN 'USER_DIM5_DISPLAY_CODE' THEN
2016
2017 BEGIN
2018 EXECUTE IMMEDIATE t_metadata(i).member_sql
2019 INTO t_user_dim5_id(j)
2020 USING t_user_dim5_dc(j),c_value_set.value_set_id;
2021
2022 EXCEPTION
2023 WHEN no_data_found THEN
2024 t_status(j) := 'INVALID_USER_DIM5';
2025 END;
2026
2027 WHEN 'USER_DIM6_DISPLAY_CODE' THEN
2028
2029 BEGIN
2030 EXECUTE IMMEDIATE t_metadata(i).member_sql
2031 INTO t_user_dim6_id(j)
2035 WHEN no_data_found THEN
2032 USING t_user_dim6_dc(j),c_value_set.value_set_id;
2033
2034 EXCEPTION
2036 t_status(j) := 'INVALID_USER_DIM6';
2037 END;
2038
2039 WHEN 'USER_DIM7_DISPLAY_CODE' THEN
2040
2041 BEGIN
2042 EXECUTE IMMEDIATE t_metadata(i).member_sql
2043 INTO t_user_dim7_id(j)
2044 USING t_user_dim7_dc(j),c_value_set.value_set_id;
2045
2046 EXCEPTION
2047 WHEN no_data_found THEN
2048 t_status(j) := 'INVALID_USER_DIM7';
2049 END;
2050
2051 WHEN 'USER_DIM8_DISPLAY_CODE' THEN
2052
2053 BEGIN
2054 EXECUTE IMMEDIATE t_metadata(i).member_sql
2055 INTO t_user_dim8_id(j)
2056 USING t_user_dim8_dc(j),c_value_set.value_set_id;
2057
2058 EXCEPTION
2059 WHEN no_data_found THEN
2060 t_status(j) := 'INVALID_USER_DIM8';
2061 END;
2062
2063 WHEN 'USER_DIM9_DISPLAY_CODE' THEN
2064
2065 BEGIN
2066 EXECUTE IMMEDIATE t_metadata(i).member_sql
2067 INTO t_user_dim9_id(j)
2068 USING t_user_dim9_dc(j),c_value_set.value_set_id;
2069
2070 EXCEPTION
2071 WHEN no_data_found THEN
2072 t_status(j) := 'INVALID_USER_DIM9';
2073 END;
2074
2075 WHEN 'USER_DIM10_DISPLAY_CODE' THEN
2076
2077 BEGIN
2078 EXECUTE IMMEDIATE t_metadata(i).member_sql
2079 INTO t_user_dim10_id(j)
2080 USING t_user_dim10_dc(j),c_value_set.value_set_id;
2081
2082 EXCEPTION
2083 WHEN no_data_found THEN
2084 t_status(j) := 'INVALID_USER_DIM10';
2085 END;
2086
2087 ELSE NULL;
2088
2089 END CASE;
2090
2091 END LOOP; -- c_value_Set
2092
2093 END IF;
2094
2095 /* IF (t_status(j) <> 'LOAD') THEN
2096 x_rows_rejected := x_rows_rejected + 1;
2097 END IF;*/
2098
2099 --Bug :4690847 : Removed to comment to populate the ledger_id
2100 IF p_dimension_varchar_label = 'COST_OBJECT'
2101 AND t_metadata(i).member_display_code_col = 'LEDGER_DISPLAY_CODE'
2102 AND t_status(j) = 'LOAD' THEN
2103 BEGIN
2104 SELECT ledger_id
2105 INTO t_ledger_id(j)
2106 FROM fem_ledgers_vl
2107 WHERE ledger_display_code = t_ledger_dc(j);
2108
2109 EXECUTE IMMEDIATE t_metadata(i).member_sql
2110 INTO t_ledger_id(j)
2111 USING t_ledger_dc(j);
2112
2113 EXCEPTION
2114 WHEN no_data_found THEN
2115 t_status(j) := 'INVALID_LEDGER';
2116 END;
2117
2118 END IF; -- if dim_id <> -999
2119
2120
2121 END LOOP; --1..17(i)
2122
2123
2124
2125
2126 --------------------------------------------------------------------------
2127 -- Initialize UOM_CODE column for cost objects
2128 --------------------------------------------------------------------------
2129
2130 IF p_dimension_varchar_label = 'COST_OBJECT' THEN
2131
2132 IF (t_product_id(j) IS NOT NULL) AND (t_status(j) = 'LOAD') THEN
2133 BEGIN
2134 SELECT prod.dim_attribute_varchar_member AS uom_code
2135 INTO t_uom_code(j)
2136 FROM fem_products_attr prod,
2137 fem_dim_attributes_b attr,
2138 fem_dim_attr_versions_vl ver
2139 WHERE prod.product_id = t_product_id(j)
2140 AND prod.attribute_id = attr.attribute_id
2141 AND prod.version_id = ver.version_id
2142 AND attr.attribute_varchar_label = 'PRODUCT_UOM'
2143 AND ver.attribute_id = attr.attribute_id
2144 AND ver.default_version_flag = 'Y';
2145 EXCEPTION
2146 WHEN no_data_found THEN
2147 BEGIN
2148 SELECT default_member_display_code INTO t_uom_code(j)
2149 FROM Fem_Xdim_Dimensions_VL
2150 WHERE dimension_varchar_label = 'UOM';
2151
2152 IF t_uom_code(j) IS NULL THEN
2153 t_status(j) := 'INVALID_COST_OBJ_DEFAULT_UOM' ;
2154 END IF;
2155 END;
2156 END;
2157
2158 ELSE -- prodcut dimension is not a component dimension or invalid members
2159
2160 SELECT default_member_display_code INTO t_uom_code(j)
2161 FROM Fem_Xdim_Dimensions_VL
2162 WHERE dimension_varchar_label = 'UOM';
2163
2164 IF t_uom_code(j) IS NULL THEN
2165 IF t_status(j) = 'LOAD' THEN
2166 t_status(j) := 'INVALID_COST_OBJ_DEFAULT_UOM' ;
2167 END IF;
2168 END IF;
2169
2170 END IF; -- UOM code
2171
2172 END IF; -- Cost Object
2173
2174 END LOOP; -- 1...v_member_last_row.(j)
2175
2176 fem_engines_pkg.tech_message (
2177 p_severity => c_log_level_1
2178 ,p_module=> c_block||'.'||c_proc_name||'.Validation#4 - End');
2179
2180
2184
2181 ----------------------------------------------------------------------------
2182 -- Get the concatenated display code
2183 -----------------------------------------------------------------------------
2185 Get_Display_Codes(p_dimension_varchar_label, p_structure_id);
2186
2187 ----------------------------------------------------------------------------
2188 -- VALIDATION#5
2189 -- check for existence of unique member (eliminate unique records in
2190 -- set of records selected for insertion)
2191 -- (ie) The following combination must be unique
2192 -- Display Code + GVSC id
2193 -----------------------------------------------------------------------------
2194
2195 FOR i IN 1..v_mbr_last_row LOOP
2196 FOR j IN (i+1) .. v_mbr_last_row LOOP
2197 IF t_display_code(i) = t_display_code(j)
2198 AND t_global_vs_combo_id(i) = t_global_vs_combo_id(j) THEN
2199 t_status(i) := 'MEMBER_EXISTS';
2200 END IF;
2201 END LOOP;
2202 END LOOP;
2203
2204
2205 fem_engines_pkg.tech_message (
2206 p_severity => c_log_level_1
2207 ,p_module=> c_block||'.'||c_proc_name||'.Validation#5 - End');
2208
2209 ----------------------------------------------------------------------------
2210 -- VALIDATION#6
2211 -- Check for uniqueness of records in the interface table and
2212 -- Composite Dimension member table
2213 -- Bug:4465969 : Change in signature of 'Check_Unique_Member' API
2214 ----------------------------------------------------------------------------
2215
2216 FOR i IN 1..v_mbr_last_row LOOP
2217 IF t_status(i) = 'LOAD' THEN
2218
2219 FEM_DIM_UTILS_PVT.Check_Unique_Member( p_api_version => 1.0,
2220 p_return_status => v_return_status,
2221 p_msg_count => v_msg_count,
2222 p_msg_data => v_msg_data,
2223 p_comp_dim_flag => 'Y',
2224 p_member_name => NULL,
2225 p_member_display_code => t_display_code(i),
2226 p_dimension_varchar_label => p_dimension_varchar_label,
2227 p_value_set_id => NULL,
2228 p_global_vs_combo_id => t_global_vs_combo_id(i),
2229 p_member_group_id => NULL,
2230 p_member_id => NULL);
2231
2232 IF v_return_status = FND_API.G_RET_STS_ERROR THEN
2233 t_status(i) := 'MEMBER_EXISTS';
2234 END IF;
2235 END IF;
2236 END LOOP;
2237
2238
2239 fem_engines_pkg.tech_message (
2240 p_severity => c_log_level_1
2241 ,p_module=> c_block||'.'||c_proc_name||'.Validation#6 - End');
2242
2243 ----------------------------------------------------------------------------
2244 -- Insert the valid record into composite dimension member table
2245 ----------------------------------------------------------------------------
2246
2247 IF p_dimension_varchar_label = 'COST_OBJECT' THEN
2248 FORALL i IN 1..v_mbr_last_row
2249 EXECUTE IMMEDIATE v_insert_cost_stmt
2250 USING t_display_code(i),
2251 'N',
2252 sysdate,
2253 sysdate,
2254 p_structure_id,
2255 t_global_vs_combo_id(i),
2256 t_uom_code(i),
2257 t_fin_elem_id(i),
2258 t_ledger_id(i),
2259 t_product_id(i),
2260 t_cctr_org_id(i),
2261 t_customer_id(i),
2262 t_channel_id(i),
2263 t_project_id(i),
2264 t_user_dim1_id(i),
2265 t_user_dim2_id(i),
2266 t_user_dim3_id(i),
2267 t_user_dim4_id(i),
2268 t_user_dim5_id(i),
2269 t_user_dim6_id(i),
2270 t_user_dim7_id(i),
2271 t_user_dim8_id(i),
2272 t_user_dim9_id(i),
2273 t_user_dim10_id(i),
2274 t_fin_elem_dc(i),
2275 t_ledger_dc(i),
2276 t_product_dc(i),
2277 t_cctr_org_dc(i),
2278 t_customer_dc(i),
2279 t_channel_dc(i),
2280 t_project_dc(i),
2281 t_user_dim1_dc(i),
2282 t_user_dim2_dc(i),
2283 t_user_dim3_dc(i),
2284 t_user_dim4_dc(i),
2285 t_user_dim5_dc(i),
2286 t_user_dim6_dc(i),
2287 t_user_dim7_dc(i),
2288 t_user_dim8_dc(i),
2289 t_user_dim9_dc(i),
2290 t_user_dim10_dc(i),
2291 '',
2292 '',
2293 '',
2294 '',
2295 '',
2296 '',
2297 '',
2298 '',
2299 '',
2300 '',
2301 '',
2302 '',
2303 '',
2304 sysdate,
2305 v_CREATED_BY,
2306 v_LAST_UPDATED_BY,
2307 sysdate,
2308 v_LAST_UPDATE_LOGIN,
2309 1,
2310 'Y',
2311 'N',
2312 'N',
2313 t_status(i);
2314
2315 ELSIF p_dimension_varchar_label = 'ACTIVITY' THEN
2316 FORALL i IN 1..v_mbr_last_row
2317 EXECUTE IMMEDIATE v_insert_activity_stmt
2318 USING t_display_code(i),
2319 'N',
2320 sysdate,
2321 sysdate,
2322 p_structure_id,
2323 t_global_vs_combo_id(i),
2324 t_task_id(i),
2325 t_cctr_org_id(i),
2326 t_customer_id(i),
2330 t_user_dim1_id(i),
2327 t_channel_id(i),
2328 t_product_id(i),
2329 t_project_id(i),
2331 t_user_dim2_id(i),
2332 t_user_dim3_id(i),
2333 t_user_dim4_id(i),
2334 t_user_dim5_id(i),
2335 t_user_dim6_id(i),
2336 t_user_dim7_id(i),
2337 t_user_dim8_id(i),
2338 t_user_dim9_id(i),
2339 t_user_dim10_id(i),
2340 t_task_dc(i),
2341 t_cctr_org_dc(i),
2342 t_customer_dc(i),
2343 t_channel_dc(i),
2344 t_product_dc(i),
2345 t_project_dc(i),
2346 t_user_dim1_dc(i),
2347 t_user_dim2_dc(i),
2348 t_user_dim3_dc(i),
2349 t_user_dim4_dc(i),
2350 t_user_dim5_dc(i),
2351 t_user_dim6_dc(i),
2352 t_user_dim7_dc(i),
2353 t_user_dim8_dc(i),
2354 t_user_dim9_dc(i),
2355 t_user_dim10_dc(i),
2356 '',
2357 '',
2358 '',
2359 '',
2360 '',
2361 '',
2362 '',
2363 '',
2364 '',
2365 '',
2366 '',
2367 '',
2368 '',
2369 '',
2370 sysdate,
2371 v_CREATED_BY,
2372 v_LAST_UPDATED_BY,
2373 sysdate,
2374 v_LAST_UPDATE_LOGIN,
2375 1,
2376 'Y',
2377 'N',
2378 'N',
2379 t_status(i);
2380
2381 END IF; -- Actvity / Cost Object
2382
2383 ----------------------------------------------------------------------------
2384 --Populate the parameters for logging
2385 ----------------------------------------------------------------------------
2386 v_rows_loaded := v_rows_loaded + SQL%ROWCOUNT;
2387 v_rows_processed := v_rows_processed + cv_get_rows%ROWCOUNT;
2388 v_rows_rejected := v_rows_rejected + (v_rows_processed - v_rows_loaded);
2389 --Bug#4736810
2390
2391
2392
2393 ----------------------------------------------------------------------------
2394 -- This is common for both Activity and Cost Object
2395 -- Update the 'STATUS' column of Interface table
2396 -- Delete the insertes rows from interface tables ((ie) STATUS = LOAD )
2397 ----------------------------------------------------------------------------
2398
2399 FORALL i IN 1..v_mbr_last_row
2400 EXECUTE IMMEDIATE v_update_stmt USING t_status(i),t_rowid(i);
2401
2402 FORALL i IN 1..v_mbr_last_row
2403 EXECUTE IMMEDIATE v_delete_stmt USING t_rowid(i),t_status(i);
2404
2405 END LOOP; --- Bulk fetch from interface tables.
2406
2407 CLOSE cv_get_rows;
2408
2409
2410 fem_engines_pkg.tech_message (
2411 p_severity => c_log_level_4
2412 ,p_module => c_block||'.'||c_proc_name
2413 ,p_msg_text => 'Dimension'||p_dimension_varchar_label||
2414 'Data Slice'||v_data_slc||
2415 'Rows Processed'||v_rows_processed||
2416 'Rows Loaded'||v_rows_loaded||
2417 'Rows Rejected'||v_rows_rejected );
2418
2419 --x_rows_rejected := get_mp_rows_rejected (x_rows_rejected)
2420
2421 --------------------------
2422 -- Commit the transaaction
2423 --------------------------
2424
2425 COMMIT;
2426
2427 --------------------------------------------
2428 -- Delete Collections for Next Bulk Fetch --
2429 --------------------------------------------
2430
2431 t_fin_elem_id.DELETE;
2432 t_ledger_id.DELETE;
2433 t_rowid.DELETE;
2434 t_global_vs_combo_id.DELETE;
2435 t_task_id.DELETE;
2436 t_cctr_org_id.DELETE;
2437 t_channel_id.DELETE;
2438 t_customer_id.DELETE;
2439 t_product_id.DELETE;
2440 t_project_id.DELETE;
2441 t_user_dim1_id.DELETE;
2442 t_user_dim2_id.DELETE;
2443 t_user_dim3_id.DELETE;
2444 t_user_dim4_id.DELETE;
2445 t_user_dim5_id.DELETE;
2446 t_user_dim6_id.DELETE;
2447 t_user_dim7_id.DELETE;
2448 t_user_dim8_id.DELETE;
2449 t_user_dim9_id.DELETE;
2450 t_user_dim10_id.DELETE;
2451 t_global_vs_combo_dc.DELETE;
2452 t_task_dc.DELETE;
2453 t_cctr_org_dc.DELETE;
2454 t_channel_dc.DELETE;
2455 t_customer_dc.DELETE;
2456 t_product_dc.DELETE;
2457 t_project_dc.DELETE;
2458 t_fin_elem_dc.DELETE;
2459 t_ledger_dc.DELETE;
2460 t_user_dim1_dc.DELETE;
2461 t_user_dim2_dc.DELETE;
2462 t_user_dim3_dc.DELETE;
2463 t_user_dim4_dc.DELETE;
2464 t_user_dim5_dc.DELETE;
2465 t_user_dim6_dc.DELETE;
2466 t_user_dim7_dc.DELETE;
2467 t_user_dim8_dc.DELETE;
2468 t_user_dim9_dc.DELETE;
2469 t_user_dim10_dc.DELETE;
2470 t_display_code.DELETE;
2471 t_status.DELETE;
2472
2473 IF (v_rows_rejected > 0)
2474 THEN
2475 FEM_ENGINES_PKG.PUT_MESSAGE
2476 (p_app_name => 'FEM',
2477 p_msg_name => 'FEM_DATAX_LDR_BAD_DATA_ERR',
2478 p_token1 => 'COUNT',
2479 p_value1 => v_rows_rejected);
2480 v_message := FND_MSG_PUB.GET(p_encoded => c_false);
2481 fem_engines_pkg.tech_message (
2482 p_severity => c_log_level_2
2483 ,p_module => c_block||'.'||c_proc_name
2484 ,p_msg_text =>'FEM_DATAX_LDR_BAD_DATA_ERR');
2485
2486
2487 v_status := 0;
2488 END IF;
2489
2490
2491 FEM_Multi_Proc_Pkg.Post_Data_Slice(
2495 p_message => v_message,
2492 p_req_id => p_req_id,
2493 p_slc_id => v_slc_id,
2494 p_status => v_status,
2496 p_rows_processed => v_rows_processed,
2497 p_rows_loaded => v_rows_loaded,
2498 p_rows_rejected => v_rows_rejected);
2499
2500 END LOOP;
2501
2502
2503 EXCEPTION
2504 WHEN e_terminate THEN
2505
2506 fem_engines_pkg.tech_message(
2507 p_severity => c_log_level_4
2508 ,p_module => c_block||'.'||c_proc_name||'Exception');
2509
2510 IF cv_get_rows%ISOPEN THEN
2511 CLOSE cv_get_rows;
2512 END IF;
2513
2514 IF cv_get_invalid_fin_elems%ISOPEN THEN
2515 CLOSE cv_get_invalid_fin_elems;
2516 END IF;
2517
2518 IF cv_get_invalid_ledgers%ISOPEN THEN
2519 CLOSE cv_get_invalid_ledgers;
2520 END IF;
2521
2522 IF cv_get_invalid_gvscs%ISOPEN THEN
2523 CLOSE cv_get_invalid_gvscs;
2524 END IF;
2525
2526 IF cv_get_invalid_comp_dims%ISOPEN THEN
2527 CLOSE cv_get_invalid_comp_dims;
2528 END IF;
2529
2530 RAISE FEM_DIM_MEMBER_LOADER_PKG.e_main_terminate;
2531
2532 WHEN OTHERS THEN
2533 fem_engines_pkg.tech_message (
2534 p_severity => c_log_level_4
2535 ,p_module => c_block||'.'||c_proc_name||'.Exception'
2536 ,p_msg_text => 'Dimension'||p_dimension_varchar_label||
2537 'Code'||SQLCODE||'Err'||SQLERRM);
2538
2539 IF cv_get_rows%ISOPEN THEN
2540 CLOSE cv_get_rows;
2541 END IF;
2542
2543 IF cv_get_invalid_fin_elems%ISOPEN THEN
2544 CLOSE cv_get_invalid_fin_elems;
2545 END IF;
2546
2547 IF cv_get_invalid_ledgers%ISOPEN THEN
2548 CLOSE cv_get_invalid_ledgers;
2549 END IF;
2550
2551 IF cv_get_invalid_gvscs%ISOPEN THEN
2552 CLOSE cv_get_invalid_gvscs;
2553 END IF;
2554
2555 IF cv_get_invalid_comp_dims%ISOPEN THEN
2556 CLOSE cv_get_invalid_comp_dims;
2557 END IF;
2558
2559 RAISE FEM_DIM_MEMBER_LOADER_PKG.e_main_terminate;
2560
2561 END process_rows;
2562
2563 /***************************************************************************/
2564
2565 END FEM_COMP_DIM_MEMBER_LOADER_PKG;