[Home] [Help]
PACKAGE BODY: APPS.FEM_SETUP_PKG
Source
1 PACKAGE BODY fem_setup_pkg AS
2 /* $Header: fem_setup_pkg.plb 120.3.12000000.2 2007/08/16 18:22:43 srawat ship $ */
3
4 /*===========================================================================+
5 | PROCEDURE
6 |
7 |
8 | DESCRIPTION
9 |
10 | This procedure is used to register the segments for Activity
11 | Flex Field.
12 |
13 | SCOPE - PRIVATE
14 |
15 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
16 |
17 | ARGUMENTS :
18 |
19 | RETURNS : NONE
20 |
21 | NOTES
22 |
23 |
24 | MODIFICATION HISTORY
25 |
26 +===========================================================================*/
27
28
29 PROCEDURE register_activity_ff(p_api_version IN NUMBER,
30 p_init_msg_list IN VARCHAR2,
31 p_commit IN VARCHAR2,
32 p_encoded IN VARCHAR2,
33 x_return_status OUT NOCOPY VARCHAR2,
34 x_msg_count OUT NOCOPY NUMBER,
35 x_msg_data OUT NOCOPY VARCHAR2) AS
36
37 CURSOR select_activity_obj_cur IS
38 SELECT column_name,
39 DECODE(column_name,'TASK_ID', 'Task',
40 'COMPANY_COST_CENTER_ORG_ID', 'Organization',
41 'CUSTOMER_ID', 'Customer',
42 'CHANNEL_ID', 'Channel',
43 'PRODUCT_ID', 'Product',
44 'PROJECT_ID', 'Project',
45 'USER_DIM1_ID', 'User dimension 1',
46 'USER_DIM2_ID', 'User dimension 2',
47 'USER_DIM3_ID', 'User dimension 3',
48 'USER_DIM4_ID', 'User dimension 4',
49 'USER_DIM5_ID', 'User dimension 5',
50 'USER_DIM6_ID', 'User dimension 6',
51 'USER_DIM7_ID', 'User dimension 7',
52 'USER_DIM8_ID', 'User dimension 8',
53 'USER_DIM9_ID', 'User dimension 9',
54 'USER_DIM10_ID', 'User dimension 10',
55 'UNKNOWN') user_column_name,
56 DECODE(column_name,'TASK_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT1','UNKNOWN'),
57 'COMPANY_COST_CENTER_ORG_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT2','UNKNOWN'),
58 'CUSTOMER_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT3','UNKNOWN'),
59 'CHANNEL_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT4','UNKNOWN'),
60 'PRODUCT_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT5','UNKNOWN'),
61 'PROJECT_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT6','UNKNOWN'),
62 'USER_DIM1_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT7','UNKNOWN'),
63 'USER_DIM2_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT8','UNKNOWN'),
64 'USER_DIM3_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT9','UNKNOWN'),
65 'USER_DIM4_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT10','UNKNOWN'),
66 'USER_DIM5_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT11','UNKNOWN'),
67 'USER_DIM6_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT12','UNKNOWN'),
68 'USER_DIM7_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT13','UNKNOWN'),
69 'USER_DIM8_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT14','UNKNOWN'),
70 'USER_DIM9_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT15','UNKNOWN'),
71 'USER_DIM10_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT16','UNKNOWN'),
72 'UNKNOWN') act_segment_num,
73 vs_member_vl_object_name vs_name
74 FROM fem_column_requiremnt_b fcr,
75 fem_xdim_dimensions fxd
76 WHERE fcr.dimension_id = fxd.dimension_id
77 AND fcr.activity_dim_component_flag = 'Y'
78 ORDER BY act_segment_num; --Bug#4209065
79
80 j NUMBER := 0;
81
82 act_ff_rec fnd_flex_key_api.flexfield_type;
83 act_str_rec fnd_flex_key_api.structure_type;
84 act_seg_rec fnd_flex_key_api.segment_type;
85
86 dummy_rec fnd_flex_key_api.structure_type;
87
88 find_act_defn BOOLEAN := TRUE;
89
90 l_column_name VARCHAR2(500);
91
92 l_api_version NUMBER;
93 l_init_msg_list VARCHAR2(1);
94 l_commit VARCHAR2(1);
95 l_encoded VARCHAR2(1);
96
97 l_api_name CONSTANT VARCHAR2(30) := 'register_activity_ff';
98
99
100 BEGIN
101
102 l_api_version := NVL(p_api_version, c_api_version);
103 l_init_msg_list := NVL(p_init_msg_list, c_false);
104 l_commit := NVL(p_commit, c_false);
105 l_encoded := NVL(p_encoded, c_true);
106
107 x_return_status := c_success;
108
109 fem_engines_pkg.tech_message (p_severity => g_log_level_1
110 ,p_module => g_block||'.'||l_api_name
111 ,p_msg_text => 'Begin');
112
113 --------------------
114 --Query the elements
115 --------------------
116
117 FOR sel_act_cost_obj_rec IN select_activity_obj_cur
118 LOOP
119
120 IF sel_act_cost_obj_rec.user_column_name <> 'UNKNOWN' THEN
121 l_column_name := sel_act_cost_obj_rec.column_name;
122
123 --------------------------------
124 --Get handle to FF and structure
125 --------------------------------
126
127 IF find_act_defn THEN
128 -- If the set_session_mode is not used, handle to FF and structure returns NULL
129 fnd_flex_key_api.set_session_mode('seed_data');
130 act_ff_rec := fnd_flex_key_api.find_flexfield('FEM','FEAC');
131 act_str_rec := fnd_flex_key_api.find_structure(act_ff_rec,'Activity Flexfield');
132 find_act_defn := FALSE;
133 END IF;
134
135 ------------------------------------
136 --End Get handle to FF and structure
137 ------------------------------------
138
139 j := j + 1;
140
141 -----------------------------------------------
142 --Register the new segments to the FF structure
143 -----------------------------------------------
144
145 fem_engines_pkg.tech_message (p_severity => g_log_level_2
146 ,p_module => g_block||'.'||l_api_name
147 ,p_msg_text => 'Register New Segment');
148
149 act_seg_rec := fnd_flex_key_api.new_segment(flexfield => act_ff_rec,
150 structure => act_str_rec,
151 segment_name => l_column_name,
152 description => l_column_name,
153 column_name => sel_act_cost_obj_rec.act_segment_num,
154 segment_number => j,
155 enabled_flag => 'Y',
156 displayed_flag => 'Y',
157 indexed_flag => 'Y',
158 value_set => sel_act_cost_obj_rec.vs_name,
159 default_type => NULL,
160 default_value => NULL,
161 required_flag => 'Y',
162 security_flag => 'N',
163 range_code => NULL,
164 display_size => 25,
165 description_size => 50,
166 concat_size => 25,
167 lov_prompt => l_column_name,
168 window_prompt => sel_act_cost_obj_rec.user_column_name,
169 runtime_property_function => null,
170 additional_where_clause => null );
171
172 ---------------------------------------------------
173 --End Register the new segments to the FF structure
174 ---------------------------------------------------
175
176 ----------------------------------
177 --Add the segment to the structure
178 ----------------------------------
179
180 fem_engines_pkg.tech_message (p_severity => g_log_level_2
181 ,p_module => g_block||'.'||l_api_name
182 ,p_msg_text => 'Add New Segment to structure');
183
184 fnd_flex_key_api.add_segment(flexfield => act_ff_rec,
185 structure => act_str_rec,
186 segment => act_seg_rec);
187
188 --------------------------------------
189 --End Add the segment to the structure
190 --------------------------------------
191
192 END IF;
193
194 END LOOP;
195
196 ------------------------
197 --End Query the elements
198 ------------------------
199
200 ----------------------------
201 --Compile FF definition FEAC
202 ----------------------------
203
204 IF j > 0 THEN
205
206 fem_engines_pkg.tech_message (p_severity => g_log_level_2
207 ,p_module => g_block||'.'||l_api_name
208 ,p_msg_text => 'Compiling the flexfield definition');
209
210 compile_ff( p_api_version => l_api_version,
211 p_init_msg_list => l_init_msg_list,
212 p_commit => l_commit,
213 p_encoded => l_encoded,
214 x_return_status => x_return_status,
215 x_msg_count => x_msg_count,
216 x_msg_data => x_msg_data,
217 p_ff_name => 'FEAC',
218 p_comdim_ff_rec => act_ff_rec,
219 p_comdim_str_rec => act_str_rec);
220 END IF;
221
222 --------------------------------
223 --End Compile FF definition FEAC
224 --------------------------------
225
226 fem_engines_pkg.tech_message (p_severity => g_log_level_1
227 ,p_module => g_block||'.'||l_api_name
228 ,p_msg_text => 'End');
229
230
231 EXCEPTION
232 WHEN OTHERS THEN
233
234 x_return_status := c_error;
235
236 fem_engines_pkg.tech_message (p_severity => g_log_level_4
237 ,p_module => g_block||'.'||l_api_name
238 ,p_msg_text => 'Exception');
239
240 -- Bug#6331569: Add message logging.
241 IF fnd_flex_key_api.message IS NOT NULL THEN
242 fem_engines_pkg.tech_message ( p_severity => g_log_level_4
243 ,p_module => g_block||'.'||l_api_name
244 ,p_msg_text => fnd_flex_key_api.message);
245 END IF;
246
247 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
248 p_count => x_msg_count,
249 p_data => x_msg_data);
250
251 END register_activity_ff;
252
253 /*===========================================================================+
254 | PROCEDURE
255 |
256 |
257 | DESCRIPTION
258 |
259 | This procedure is used to register the flex field for Cost Object
260 |
261 |
262 | SCOPE - PRIVATE
263 |
264 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
265 |
266 | ARGUMENTS :
267 |
268 | RETURNS : NONE
269 |
270 | NOTES
271 |
272 |
273 | MODIFICATION HISTORY
274 |
275 +===========================================================================*/
276
277
278
279 PROCEDURE register_cost_ff(p_api_version IN NUMBER,
280 p_init_msg_list IN VARCHAR2,
281 p_commit IN VARCHAR2,
282 p_encoded IN VARCHAR2,
283 x_return_status OUT NOCOPY VARCHAR2,
284 x_msg_count OUT NOCOPY NUMBER,
285 x_msg_data OUT NOCOPY VARCHAR2) AS
286
287 CURSOR select_cost_obj_cur IS
288 SELECT column_name,
289 DECODE(column_name,'FINANCIAL_ELEM_ID', 'Financial Element',
290 'LEDGER_ID', 'Ledger',
291 'PRODUCT_ID', 'Product',
292 'COMPANY_COST_CENTER_ORG_ID', 'Organization',
293 'CUSTOMER_ID', 'Customer',
294 'CHANNEL_ID', 'Channel',
295 'PROJECT_ID', 'Project',
296 'USER_DIM1_ID', 'User dimension 1',
297 'USER_DIM2_ID', 'User dimension 2',
298 'USER_DIM3_ID', 'User dimension 3',
299 'USER_DIM4_ID', 'User dimension 4',
300 'USER_DIM5_ID', 'User dimension 5',
301 'USER_DIM6_ID', 'User dimension 6',
302 'USER_DIM7_ID', 'User dimension 7',
303 'USER_DIM8_ID', 'User dimension 8',
304 'USER_DIM9_ID', 'User dimension 9',
305 'USER_DIM10_ID', 'User dimension 10',
306 'UNKNOWN') user_column_name,
307 DECODE(column_name,'FINANCIAL_ELEM_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT1','UNKNOWN'),
308 'LEDGER_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT2','UNKNOWN'),
309 'PRODUCT_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT3','UNKNOWN'),
310 'COMPANY_COST_CENTER_ORG_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT4','UNKNOWN'),
311 'CUSTOMER_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT5','UNKNOWN'),
312 'CHANNEL_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT6','UNKNOWN'),
313 'PROJECT_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT7','UNKNOWN'),
314 'USER_DIM1_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT8','UNKNOWN'),
315 'USER_DIM2_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT9','UNKNOWN'),
316 'USER_DIM3_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT10','UNKNOWN'),
317 'USER_DIM4_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT11','UNKNOWN'),
318 'USER_DIM5_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT12','UNKNOWN'),
319 'USER_DIM6_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT13','UNKNOWN'),
320 'USER_DIM7_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT14','UNKNOWN'),
321 'USER_DIM8_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT15','UNKNOWN'),
322 'USER_DIM9_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT16','UNKNOWN'),
323 'USER_DIM10_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT17','UNKNOWN'),
324 'UNKNOWN') cost_segment_num,
325 vs_member_vl_object_name vs_name
326 FROM fem_column_requiremnt_b fcr,
327 fem_xdim_dimensions fxd
328 WHERE fcr.dimension_id = fxd.dimension_id
329 AND cost_obj_dim_component_flag = 'Y'
330 ORDER BY cost_segment_num; --Bug#4209065
331
332 k NUMBER := 0;
333
334 cost_ff_rec fnd_flex_key_api.flexfield_type;
335 cost_str_rec fnd_flex_key_api.structure_type;
336 cost_seg_rec fnd_flex_key_api.segment_type;
337
338 find_cost_defn BOOLEAN := TRUE;
339
340 l_column_name VARCHAR2(500);
341
342 l_api_version NUMBER;
343 l_init_msg_list VARCHAR2(1);
344 l_commit VARCHAR2(1);
345 l_encoded VARCHAR2(1);
346
347 l_api_name CONSTANT VARCHAR2(30) := 'register_cost_ff';
348
349 BEGIN
350
351 l_api_version := NVL(p_api_version, c_api_version);
352 l_init_msg_list := NVL(p_init_msg_list, c_false);
353 l_commit := NVL(p_commit, c_false);
354 l_encoded := NVL(p_encoded, c_true);
355
356 x_return_status := c_success;
357
358 fem_engines_pkg.tech_message (p_severity => g_log_level_1
359 ,p_module => g_block||'.'||l_api_name
360 ,p_msg_text => 'Begin');
361
362
363 --------------------
364 --Query the elements
365 --------------------
366
367 FOR sel_act_cost_obj_rec IN select_cost_obj_cur
368 LOOP
369
370 IF sel_act_cost_obj_rec.user_column_name <> 'UNKNOWN' THEN
371 l_column_name := sel_act_cost_obj_rec.column_name;
372
373 --------------------------------
374 --Get handle to FF and structure
375 -----------------------------------------
376
377 IF find_cost_defn THEN
378 --If the set_session_mode is not used, handle to FF and structure returns NULL
379 fnd_flex_key_api.set_session_mode('customer_data');
380 cost_ff_rec := fnd_flex_key_api.find_flexfield('FEM','FECO');
381 cost_str_rec := fnd_flex_key_api.find_structure(cost_ff_rec,'Cost Object Flexfield');
382 find_cost_defn := FALSE;
383 END IF;
384
385 ------------------------------------
386 --End Get handle to FF and structure
387 ------------------------------------
388
389 k := k + 1;
390
391 -----------------------------------------------
392 --Register the new segments to the FF structure
393 -----------------------------------------------
394
395 fem_engines_pkg.tech_message (p_severity => g_log_level_2
396 ,p_module => g_block||'.'||l_api_name
397 ,p_msg_text => 'Register New Segment');
398
399 cost_seg_rec := fnd_flex_key_api.new_segment(flexfield => cost_ff_rec,
400 structure => cost_str_rec,
401 segment_name => l_column_name,
402 description => l_column_name,
403 column_name => sel_act_cost_obj_rec.cost_segment_num,
404 segment_number => k,
405 enabled_flag => 'Y',
406 displayed_flag => 'Y',
407 indexed_flag => 'Y',
408 value_set => sel_act_cost_obj_rec.vs_name,
409 default_type => NULL,
410 default_value => NULL,
411 required_flag => 'Y',
412 security_flag => 'N',
413 range_code => NULL,
414 display_size => 25,
415 description_size => 50,
416 concat_size => 25,
417 lov_prompt => l_column_name,
418 window_prompt => sel_act_cost_obj_rec.user_column_name,
419 runtime_property_function => null,
420 additional_where_clause => null );
421
422 ---------------------------------------------------
423 --End Register the new segments to the FF structure
424 ---------------------------------------------------
425
426 ----------------------------------
427 --Add the segment to the structure
428 ----------------------------------
429
430 fem_engines_pkg.tech_message (p_severity => g_log_level_2
431 ,p_module => g_block||'.'||l_api_name
432 ,p_msg_text => 'Add New Segment to structure');
433
434 fnd_flex_key_api.add_segment(flexfield => cost_ff_rec,
435 structure => cost_str_rec,
436 segment => cost_seg_rec);
437
438 --------------------------------------
439 --End Add the segment to the structure
440 --------------------------------------
441
442 END IF;
443
444 END LOOP;
445
446 ------------------------
447 --End Query the elements
448 ------------------------
449
450 IF k > 0 THEN
451
452 fem_engines_pkg.tech_message (p_severity => g_log_level_2
453 ,p_module => g_block||'.'||l_api_name
454 ,p_msg_text => 'Compiling the flexfield definition');
455
456 compile_ff( p_api_version => l_api_version,
457 p_init_msg_list => l_init_msg_list,
458 p_commit => l_commit,
459 p_encoded => l_encoded,
460 x_return_status => x_return_status,
461 x_msg_count => x_msg_count,
462 x_msg_data => x_msg_data,
463 p_ff_name => 'FECO',
464 p_comdim_ff_rec => cost_ff_rec,
465 p_comdim_str_rec => cost_str_rec);
466 END IF;
467
468 fem_engines_pkg.tech_message (p_severity => g_log_level_1
469 ,p_module => g_block||'.'||l_api_name
470 ,p_msg_text => 'End');
471
472 EXCEPTION
473 WHEN OTHERS THEN
474 x_return_status := c_error;
475
476 fem_engines_pkg.tech_message (p_severity => g_log_level_4
477 ,p_module => g_block||'.'||l_api_name
478 ,p_msg_text => 'Exception');
479
480 -- Bug#6331569: Add message logging.
481 IF fnd_flex_key_api.message IS NOT NULL THEN
482 fem_engines_pkg.tech_message ( p_severity => g_log_level_4
483 ,p_module => g_block||'.'||l_api_name
484 ,p_msg_text => fnd_flex_key_api.message);
485 END IF;
486
487 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
488 p_count => x_msg_count,
489 p_data => x_msg_data);
490
491 END register_cost_ff;
492
493 /*===========================================================================+
494 | PROCEDURE
495 | compile_ff
496 |
497 | DESCRIPTION
498 |
499 | This procedure is used to compile the flex field after adding
500 | the new segments.
501 |
502 | SCOPE - PRIVATE
503 |
504 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
505 |
506 | ARGUMENTS :
507 |
508 | RETURNS : NONE
509 |
510 | NOTES
511 |
512 |
513 | MODIFICATION HISTORY
514 |
515 +===========================================================================*/
516
517 PROCEDURE compile_ff(p_api_version IN NUMBER,
518 p_init_msg_list IN VARCHAR2,
519 p_commit IN VARCHAR2,
520 p_encoded IN VARCHAR2,
521 x_return_status OUT NOCOPY VARCHAR2,
522 x_msg_count OUT NOCOPY NUMBER,
523 x_msg_data OUT NOCOPY VARCHAR2,
524 p_ff_name IN VARCHAR2,
525 p_comdim_ff_rec IN fnd_flex_key_api.flexfield_type,
526 p_comdim_str_rec IN fnd_flex_key_api.structure_type) AS
527
528 mod_structure fnd_flex_key_api.structure_type;
529 orig_structure fnd_flex_key_api.structure_type;
530 request_id NUMBER;
531
532 l_flex_num NUMBER;
533 l_flex_structure_code VARCHAR2(30);
534
535 l_api_version NUMBER;
536 l_init_msg_list VARCHAR2(1);
537 l_commit VARCHAR2(1);
538 l_encoded VARCHAR2(1);
539
540 l_api_name CONSTANT VARCHAR2(30) := 'compile_ff';
541
542
543 CURSOR get_flex_num_cur IS
544 SELECT id_flex_num,id_flex_structure_code
545 FROM fnd_id_flex_structures
546 WHERE application_id = 274
547 AND id_flex_code = p_ff_name;
548
549 BEGIN
550
551 l_api_version := NVL(p_api_version, c_api_version);
552 l_init_msg_list := NVL(p_init_msg_list, c_false);
553 l_commit := NVL(p_commit, c_false);
554 l_encoded := NVL(p_encoded, c_true);
555
556 x_return_status := c_success;
557
558 ----------------------------
559 -- Freeze the FF definition
560 ----------------------------
561
562 orig_structure := p_comdim_str_rec;
563
564 fem_engines_pkg.tech_message (p_severity => g_log_level_2
565 ,p_module => g_block||'.'||l_api_name
566 ,p_msg_text => 'Modifying the structure to set flags');
567
568 mod_structure := fnd_flex_key_api.new_structure(flexfield => p_comdim_ff_rec,
569 -- Bug#6331569: Explicitly pass structure code.
570 structure_code => orig_structure.structure_code,
571 structure_title => orig_structure.structure_name,
572 description => orig_structure.description,
573 view_name => orig_structure.view_name,
574 freeze_flag => 'Y',
575 enabled_flag => 'Y',
576 segment_separator => orig_structure.segment_separator,
577 cross_val_flag => 'N',
578 freeze_rollup_flag => 'N',
579 dynamic_insert_flag => 'Y',
580 shorthand_enabled_flag => 'N',
581 shorthand_prompt => '',
582 shorthand_length => NULL);
583
584 fnd_flex_key_api.modify_structure(flexfield => p_comdim_ff_rec,
585 original => orig_structure,
586 modified => mod_structure);
587
588 --------------------------------
589 -- End Freeze the FF definition
590 --------------------------------
591
592 fnd_global.apps_initialize(fnd_global.user_id,
593 fnd_global.resp_id,
594 fnd_global.resp_appl_id);
595
596 ----------------
597 -- Get ID FF Num
598 ----------------
599
600 OPEN get_flex_num_cur;
601 FETCH get_flex_num_cur INTO l_flex_num,l_flex_structure_code ;
602 CLOSE get_flex_num_cur;
603
604 --------------------
605 -- End Get ID FF Num
606 --------------------
607
608 ---------------------
609 -- Compile flexfield
610 ---------------------
611
612 fem_engines_pkg.tech_message (p_severity => g_log_level_2
613 ,p_module => g_block||'.'||l_api_name
614 ,p_msg_text => 'Compiling the flexfield through SRS');
615
616 request_id := fnd_request.submit_request('FND',
617 'FDFCMPK',
618 'Compiling Flexfield',
619 SYSDATE,
620 FALSE,
621 'K',
622 'FEM',
623 p_ff_name,
624 TO_CHAR(l_flex_num));
625
626 IF request_id = 0 THEN
627 fnd_message.retrieve(x_msg_data);
628 fnd_message.raise_error;
629 END IF;
630
631 ------------------------
632 -- End Compile flexfield
633 ------------------------
634
635 -------------------------------------------------------------
636 -- Update FEM_XDIM_DIMENSIONS_VL with FLEX_FIELD Information
637 -------------------------------------------------------------
638
639 fem_engines_pkg.tech_message (p_severity => g_log_level_2
640 ,p_module => g_block||'.'||l_api_name
641 ,p_msg_text => 'Updating fem_xdim_dimensions with FF details');
642
643 IF p_ff_name = 'FEAC' THEN
644 UPDATE fem_xdim_dimensions
645 SET id_flex_num = l_flex_num ,
646 id_flex_structure_code = l_flex_structure_code,
647 id_flex_code= p_ff_name
648 WHERE dimension_id = 10;
649 ELSIF p_ff_name = 'FECO' THEN
650 UPDATE fem_xdim_dimensions
651 SET id_flex_num = l_flex_num ,
652 id_flex_structure_code = l_flex_structure_code,
653 id_flex_code= p_ff_name
654 WHERE dimension_id = 11;
655 END IF;
656
657 fem_engines_pkg.tech_message (p_severity => g_log_level_1
658 ,p_module => g_block||'.'||l_api_name
659 ,p_msg_text => 'End');
660
661 EXCEPTION
662 WHEN OTHERS THEN
663 x_return_status := c_error;
664
665 fem_engines_pkg.tech_message (p_severity => g_log_level_4
666 ,p_module => g_block||'.'||l_api_name
667 ,p_msg_text => 'Exception');
668
669 -- Bug#6331569: Add message logging.
670 IF fnd_flex_key_api.message IS NOT NULL THEN
671 fem_engines_pkg.tech_message ( p_severity => g_log_level_4
672 ,p_module => g_block||'.'||l_api_name
673 ,p_msg_text => fnd_flex_key_api.message);
674 END IF;
675
676 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
677 p_count => x_msg_count,
678 p_data => x_msg_data);
679
680 END compile_ff;
681 ------------------------------------------------------------------------
682 /*
683 PROCEDURE validate_proc_key(p_api_version IN NUMBER,
684 p_init_msg_list IN VARCHAR2,
685 p_commit IN VARCHAR2,
686 p_encoded IN VARCHAR2,
687 x_return_status OUT NOCOPY VARCHAR2,
688 x_msg_count OUT NOCOPY NUMBER,
689 x_msg_data OUT NOCOPY VARCHAR2,
690 p_col_list_rec IN fem_col_list_arr_typ ,
691 p_table_name IN VARCHAR2) AS
692
693 TYPE proc_list_arr IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
694
695 proc_list_rec proc_list_arr;
696
697 l_raise_error BOOLEAN := FALSE;
698 i NUMBER := 0;
699 j NUMBER := 0;
700
701 found BOOLEAN := FALSE;
702
703 l_api_version NUMBER;
704 l_init_msg_list VARCHAR2(1);
705 l_commit VARCHAR2(1);
706 l_encoded VARCHAR2(1);
707
708 l_api_name CONSTANT VARCHAR2(30) := 'validate_proc_key';
709
710 BEGIN
711
712 l_api_version := NVL(p_api_version, c_api_version);
713 l_init_msg_list := NVL(p_init_msg_list, c_false);
714 l_commit := NVL(p_commit, c_false);
715 l_encoded := NVL(p_encoded, c_true);
716
717 x_return_status := c_success;
718
719 SELECT column_name
720 BULK COLLECT INTO proc_list_rec
721 FROM fem_tab_column_prop
722 WHERE table_name = p_table_name
723 AND column_property_code = 'PROCESSING_KEY';
724
725 IF proc_list_rec.COUNT <> p_col_list_rec.COUNT THEN
726 l_raise_error := TRUE;
727 END IF;
728
729 IF NOT l_raise_error THEN
730 FOR i IN proc_list_rec.FIRST..proc_list_rec.LAST LOOP
731 FOR j IN 1..p_col_list_rec.COUNT LOOP
732 IF proc_list_rec(i) = p_col_list_rec(j).col_name THEN
733 found := TRUE;
734 EXIT;
735 END IF;
736 END LOOP;
737 IF NOT found THEN
738 l_raise_error := TRUE;
739 EXIT;
740 ELSE
741 found := FALSE;
742 END IF;
743 END LOOP;
744 END IF;
745
746 IF l_raise_error THEN
747 x_return_status := c_error;
748 END IF;
749
750 fnd_msg_pub.count_and_get(p_count => x_msg_count,
751 p_data => x_msg_data);
752
753 EXCEPTION
754 WHEN OTHERS THEN
755 x_return_status := c_error;
756
757 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
758 p_count => x_msg_count,
759 p_data => x_msg_data);
760
761 END validate_proc_key;
762 */
763 /*===========================================================================+
764 | PROCEDURE
765 | validate_proc_key
766 |
767 | DESCRIPTION
768 |
769 | This procedure is used by PFT/FEM Engines to check whether the
770 | component dimensions of Activity/Cost Object is not part of the table's
771 | processing key.
772 |
773 |
774 | SCOPE - PRIVATE
775 |
776 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
777 |
778 | ARGUMENTS :
779 |
780 | p_dimension_varchar_label - ACTIVITY/COST_OBJECT
781 | p_table_name - Processing Table.
782 |
783 | RETURNS : NONE
784 |
785 | NOTES
786 |
787 |
788 | MODIFICATION HISTORY
789 | sshanmug 09-Jun-05 Bug:4475823 - modifications/fixes for improved
790 | integration with the Rollup Engine (FEM) and
791 | Activity Rate Engine (PFT)
792 +===========================================================================*/
793
794 PROCEDURE validate_proc_key(p_api_version IN NUMBER,
795 p_init_msg_list IN VARCHAR2,
796 p_commit IN VARCHAR2,
797 p_encoded IN VARCHAR2,
798 x_return_status OUT NOCOPY VARCHAR2,
799 x_msg_count OUT NOCOPY NUMBER,
800 x_msg_data OUT NOCOPY VARCHAR2,
801 p_dimension_varchar_label IN VARCHAR2,
802 p_table_name IN VARCHAR2) AS
803
804 i NUMBER := 0;
805 j NUMBER := 0;
806
807 found BOOLEAN := FALSE;
808
809 l_api_version NUMBER;
810 l_init_msg_list VARCHAR2(1);
811 l_commit VARCHAR2(1);
812 l_encoded VARCHAR2(1);
813 l_exists VARCHAR2(1);
814
815 l_api_name CONSTANT VARCHAR2(30) := 'validate_proc_key';
816
817
818 CURSOR l_inv_act_dim_column_cur IS
819 SELECT comp.column_name
820 FROM fem_column_requiremnt_b comp
821 WHERE comp.activity_dim_component_flag = 'Y'
822 AND NOT EXISTS (
823 SELECT 1
824 FROM fem_tab_column_prop proc
825 WHERE proc.table_name = p_table_name
826 AND proc.column_property_code = 'PROCESSING_KEY'
827 AND proc.column_name = comp.column_name
828 );
829
830 CURSOR l_inv_co_dim_column_cur IS
831 SELECT comp.column_name
832 FROM fem_column_requiremnt_b comp
833 WHERE comp.cost_obj_dim_component_flag = 'Y'
834 AND NOT EXISTS (
835 SELECT 1
836 FROM fem_tab_column_prop proc
837 WHERE proc.table_name = p_table_name
838 AND proc.column_property_code = 'PROCESSING_KEY'
839 AND proc.column_name = comp.column_name
840 );
841
842
843
844 BEGIN
845
846 l_api_version := NVL(p_api_version, c_api_version);
847 l_init_msg_list := NVL(p_init_msg_list, c_false);
848 l_commit := NVL(p_commit, c_false);
849 l_encoded := NVL(p_encoded, c_true);
850
851 x_return_status := c_success;
852
853 fem_engines_pkg.tech_message (
854 p_severity => g_log_level_1
855 ,p_module => g_block||'.'||l_api_name||'.Begin'
856 ,p_msg_text => 'Dimension'||p_dimension_varchar_label);
857
858
859 IF p_dimension_varchar_label = 'ACTIVITY' THEN
860
861 FOR l_inv_act_dim_column_rec IN l_inv_act_dim_column_cur LOOP
862
863 -- If a record is returned in the l_inv_act_dim_column_cur, then that
864 -- component dimension is not part of the table's processing key.
865
866 x_return_status := c_error;
867
868 FND_MESSAGE.set_name('FEM', 'FEM_COMP_ENG_PROCESS_KEY_ERROR');
869 FND_MESSAGE.set_token('DIMENSION',
870 FEM_DIMENSION_UTIL_PKG.Get_Dimension_Name(p_dimension_varchar_label));
871 FND_MESSAGE.set_token('TABLE', p_table_name);
872 FND_MESSAGE.set_token('COLUMN_NAME',
873 l_inv_act_dim_column_rec.column_name);
874 FND_MSG_PUB.Add;
875
876 END LOOP;
877
878 ELSIF p_dimension_varchar_label = 'COST_OBJECT' THEN
879
880 FOR l_inv_co_dim_column_rec IN l_inv_co_dim_column_cur LOOP
881
882 -- If a record is returned in the l_inv_co_dim_column_cur, then that
883 -- component dimension is not part of the table's processing key.
884
885 x_return_status := c_error;
886
887 FND_MESSAGE.set_name('FEM', 'FEM_COMP_ENG_PROCESS_KEY_ERROR');
888 FND_MESSAGE.set_token('DIMENSION',
889 FEM_DIMENSION_UTIL_PKG.Get_Dimension_Name(p_dimension_varchar_label));
890 FND_MESSAGE.set_token('TABLE', p_table_name);
891 FND_MESSAGE.set_token('COLUMN_NAME',
892 l_inv_co_dim_column_rec.column_name);
893 FND_MSG_PUB.Add;
894
895 END LOOP;
896
897 END IF;
898
899 FND_MSG_PUB.Count_And_Get(
900 p_count => x_msg_count
901 ,p_data => x_msg_data);
902
903 fem_engines_pkg.tech_message (
904 p_severity => g_log_level_1
905 ,p_module => g_block||'.'||l_api_name||'.End'
906 ,p_msg_text => 'Dimension'||p_dimension_varchar_label);
907
908 EXCEPTION
909
910 WHEN OTHERS THEN
911 x_return_status := c_error;
912
913 FND_MSG_PUB.Count_And_Get(
914 p_count => x_msg_count
915 ,p_data => x_msg_data );
916
917 fem_engines_pkg.tech_message (
918 p_severity => g_log_level_4
919 ,p_module => g_block||'.'||l_api_name||'.Exception'
920 ,p_msg_text => 'Dimension'||p_dimension_varchar_label);
921
922 END validate_proc_key;
923
924 /*===========================================================================+
925 | PROCEDURE
926 | delete_flexfield
927 |
928 | DESCRIPTION
929 |
930 | This procedure is used to delete the flex field structure which
931 | was created after freezing the FF definition.
932 |
933 |
934 | SCOPE - PRIVATE
935 |
936 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
937 |
938 | ARGUMENTS :
939 |
940 | p_dimension_varchar_label - ACTIVITY/COST_OBJECT
941 |
942 | RETURNS : NONE
943 |
944 | NOTES
945 |
946 |
947 | MODIFICATION HISTORY
948 | 18-May-06 sshanmug Bug:5224927: Flexfield Delete
949 | API for EPF Refresh Engine.
950 |
951 +===========================================================================*/
952
953
954 PROCEDURE delete_flexfield(p_api_version IN NUMBER,
955 p_init_msg_list IN VARCHAR2,
956 p_commit IN VARCHAR2,
957 p_encoded IN VARCHAR2,
958 x_return_status OUT NOCOPY VARCHAR2,
959 x_msg_count OUT NOCOPY NUMBER,
960 x_msg_data OUT NOCOPY VARCHAR2,
961 p_dimension_varchar_label IN VARCHAR2)
962
963 AS
964
965 l_api_version NUMBER;
966 l_init_msg_list VARCHAR2(1);
967 l_commit VARCHAR2(1);
968 l_encoded VARCHAR2(1);
969
970 l_api_name CONSTANT VARCHAR2(30) := 'delete_flexfield';
971
972 BEGIN
973
974 ----------------------------------------------------------------
975 --Initialize API call
976 ----------------------------------------------------------------
977
978 l_api_version := NVL(p_api_version, c_api_version);
979 l_init_msg_list := NVL(p_init_msg_list, c_false);
980 l_commit := NVL(p_commit, c_false);
981 l_encoded := NVL(p_encoded, c_true);
982
983 -- Standard call to check for call compatibility
984
985 IF NOT FND_API.Compatible_API_Call (
986 p_current_version_number => l_api_version
987 ,p_caller_version_number => p_api_version
988 ,p_api_name => l_api_name
989 ,p_pkg_name => 'fem_setup_pkg'
990 ) THEN
991
992 raise FND_API.G_EXC_UNEXPECTED_ERROR;
993
994 END IF;
995
996 -- Initialize Message Stack on FND_MSG_PUB
997
998 IF(FND_API.To_Boolean(p_init_msg_list)) THEN
999
1000 FND_MSG_PUB.Initialize;
1001
1002 END IF;
1003
1004 -- Initialize the OUT parameter
1005
1006 x_return_status := c_success;
1007
1008 fem_engines_pkg.tech_message (
1009 p_severity => g_log_level_1
1010 ,p_module => g_block||'.'||l_api_name||'.Begin'
1011 ,p_msg_text => 'Dimension'||p_dimension_varchar_label);
1012
1013 -- If the set_session_mode is not used, handle to FF and structure returns NULL
1014
1015 fnd_flex_key_api.set_session_mode('customer_data');
1016
1017 ----------------------------------------------------------------
1018 -- Delete the Flexfield
1019 ----------------------------------------------------------------
1020
1021 IF p_dimension_varchar_label = 'ACTIVITY' THEN
1022
1023 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1024 ,p_module => g_block||'.'||l_api_name
1025 ,p_msg_text => 'Deleting FF');
1026
1027 fnd_flex_key_api.delete_flexfield('FEM','FEAC');
1028
1029 ELSE -- DIMENSION IS COST OBJECT
1030
1031 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1032 ,p_module => g_block||'.'||l_api_name
1033 ,p_msg_text => 'Deleting FF');
1034
1035 fnd_flex_key_api.delete_flexfield('FEM','FECO');
1036
1037 END IF;
1038
1039 -----------------------
1040 -- Finalize API Call --
1041 -----------------------
1042
1043 -- Standard check of p_commit
1044
1045 IF FND_API.To_Boolean(p_commit) THEN
1046
1047 commit work;
1048
1049 END IF;
1050
1051 -- Standard call to get message count and if count is 1, get message info
1052
1053 FND_MSG_PUB.Count_And_Get (
1054 p_count => x_msg_count
1055 ,p_data => x_msg_data
1056 );
1057
1058 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1059 ,p_module => g_block||'.'||l_api_name
1060 ,p_msg_text => 'End');
1061
1062 EXCEPTION
1063
1064 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1065
1066 x_return_status := c_error;
1067
1068 fem_engines_pkg.tech_message (p_severity => g_log_level_4
1069 ,p_module => g_block||'.'||l_api_name
1070 ,p_msg_text => 'Exception');
1071
1072 FND_MSG_PUB.Count_And_Get(
1073 p_count => x_msg_count
1074 ,p_data => x_msg_data );
1075
1076
1077 WHEN OTHERS THEN
1078
1079 x_return_status := c_error;
1080
1081 fem_engines_pkg.tech_message (p_severity => g_log_level_4
1082 ,p_module => g_block||'.'||l_api_name
1083 ,p_msg_text => 'Exception');
1084
1085 FND_MSG_PUB.Count_And_Get (p_encoded => p_encoded,
1086 p_count => x_msg_count,
1087 p_data => x_msg_data);
1088
1089 END delete_flexfield;
1090
1091 END fem_setup_pkg;