[Home] [Help]
PACKAGE BODY: APPS.FII_FA_CAT_C
Source
1 PACKAGE BODY FII_FA_CAT_C AS
2 /* $Header: FIIFACATB.pls 120.1 2005/10/30 05:06:01 appldev noship $ */
3
4 g_debug_flag Varchar2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
5
6 g_retcode VARCHAR2(20) := NULL;
7 g_fii_schema VARCHAR2(30);
8 g_worker_num NUMBER;
9 g_phase VARCHAR2(300);
10 g_mtc_structure_id NUMBER;
11 g_mtc_value_set_id NUMBER;
12 g_mtc_column_name VARCHAR2(30) := NULL;
13 g_fii_user_id NUMBER;
14 g_fii_login_id NUMBER;
15 g_current_language VARCHAR2(30);
16 g_max_cat_id NUMBER;
17 g_new_max_cat_id NUMBER;
18 g_mode VARCHAR2(1);
19
20 G_LOGIN_INFO_NOT_AVABLE EXCEPTION;
21 G_NO_SLG_SETUP EXCEPTION;
22
23 -- ---------------------------------------------------------------
24 -- Private procedures and Functions;
25 -- ---------------------------------------------------------------
26
27 -- ---------------------------------------------------------------
28 -- PROCEDURE INIT_DBI_CHANGE_LOG
29 -- ---------------------------------------------------------------
30 PROCEDURE INIT_DBI_CHANGE_LOG IS
31
32 l_calling_fn VARCHAR2(40) := 'FII_FA_CAT_C.INIT_DBI_CHANGE_LOG';
33
34 BEGIN
35
36 If g_debug_flag = 'Y' then
37 FII_MESSAGE.Func_Ent(l_calling_fn);
38 End if;
39
40 If g_debug_flag = 'Y' then
41 FII_UTIL.Write_Log('Inserting DBI log items into FII_CHANGE_LOG');
42 End if;
43
44 ---------------------------------------------
45 -- Populate FII_CHANGE_LOG with inital set up
46 -- entries if it hasn't been set up already
47 ---------------------------------------------
48 INSERT INTO FII_CHANGE_LOG (
49 log_item,
50 item_value,
51 creation_date,
52 created_by,
53 last_update_date,
54 last_update_login,
55 last_updated_by)
56 SELECT 'MAX_ASSET_CAT_ID',
57 '0',
58 sysdate,
59 g_fii_user_id,
60 sysdate,
61 g_fii_login_id,
62 g_fii_user_id
63 FROM DUAL
64 WHERE NOT EXISTS
65 (SELECT 1
66 FROM FII_CHANGE_LOG
67 WHERE log_item = 'MAX_ASSET_CAT_ID');
68
69 If g_debug_flag = 'Y' then
70 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' log items into FII_CHANGE_LOG');
71 End if;
72
73 If g_debug_flag = 'Y' then
74 FII_MESSAGE.Func_Succ(l_calling_fn);
75 End if;
76
77 EXCEPTION
78
79 WHEN OTHERS THEN
80 rollback;
81 g_retcode := -1;
82 FII_UTIL.Write_Log('Error occured in Procedure: INIT_DBI_CHANGE_LOG Message: ' || sqlerrm);
83 FII_MESSAGE.Func_Fail(l_calling_fn);
84 raise;
85
86 END INIT_DBI_CHANGE_LOG;
87
88 -------------------------------------------------------
89 -- FUNCTION GET_STRUCTURE_NAME
90 -------------------------------------------------------
91 FUNCTION GET_STRUCTURE_NAME (p_structure_id IN NUMBER) RETURN VARCHAR2 IS
92
93 l_structure_name VARCHAR2(30);
94 l_calling_fn VARCHAR2(40) := 'FII_FA_CAT_C.GET_STRUCTURE_NAME';
95
96 BEGIN
97 If g_debug_flag = 'Y' then
98 FII_MESSAGE.Func_Ent(l_calling_fn);
99 End if;
100
101 g_phase := 'Getting user name for flex structure: ' || p_structure_id;
102
103 SELECT DISTINCT id_flex_structure_name
104 INTO l_structure_name
105 FROM fnd_id_flex_structures_tl t
106 WHERE application_id = 140
107 AND id_flex_code = 'CAT#'
108 AND id_flex_num = p_structure_id
109 AND language = g_current_language;
110
111 If g_debug_flag = 'Y' then
112 FII_UTIL.Write_Log('l_structure_name' || l_structure_name);
113 FII_MESSAGE.Func_Succ(l_calling_fn);
114 End if;
115
116 return l_structure_name;
117
118 EXCEPTION
119 WHEN OTHERS THEN
120 g_retcode := -1;
121 FII_UTIL.Write_Log('
122 ------------------------
123 Error in Function: GET_COA_NAME
124 Phase: '||g_phase||'
125 Message: '||sqlerrm);
126 FII_MESSAGE.Func_Fail(l_calling_fn);
127 raise;
128
129 END GET_STRUCTURE_NAME;
130
131
132 ---------------------------------------------------------------------
133 -- PROCEDURE GET_CAT_SEGMENTS
134 ---------------------------------------------------------------------
135 PROCEDURE GET_CAT_SEGMENTS IS
136
137 l_major_seg VARCHAR2(30);
138 l_major_flex_value_set_id NUMBER;
139 l_major_val_type_code VARCHAR2(30);
140 l_major_table_name VARCHAR2(240);
141 l_major_value_column_name VARCHAR2(240);
142 l_major_id_column_name VARCHAR2(240);
143 l_major_add_where_clause long;
144
145 l_minor_seg VARCHAR2(30);
146 l_minor_flex_value_set_id NUMBER;
147 l_minor_val_type_code VARCHAR2(30);
148 l_minor_table_name VARCHAR2(240);
149 l_minor_value_column_name VARCHAR2(240);
150 l_minor_id_column_name VARCHAR2(240);
151 l_minor_add_where_clause long;
152
153 l_structure_name VARCHAR2(30);
154 l_flex_structure_id NUMBER;
155 l_parent_value_set_id NUMBER;
156 l_dependant_value_set_flag VARCHAR2(1) := 'N';
157 l_calling_fn VARCHAR2(40) := 'FII_FA_CAT_C.GET_CAT_SEGMENTS';
158
159 error_found exception;
160
161
162 BEGIN
163
164 If g_debug_flag = 'Y' then
165 FII_MESSAGE.Func_Ent(l_calling_fn);
166 End if;
167
168 If g_debug_flag = 'Y' then
169 FII_UTIL.Write_Log('Getting Category segments column information for flex structures');
170 End if;
171 ----------------------------------------------------
172 -- Given a structure ID, it will get:
173 -- 1. Major segment
174 -- 2. Minor segment
175 -- of the flex structure
176 --
177 -- Note that technically neither qualifier is required
178 -- at setup, so return values can be null..
179 -----------------------------------------------------
180
181 -- do not trap the NO_DATA_FOUND handler for Major segment
182 -- because this must be specified for DBI to work at all
183
184 select sys.category_flex_structure,
185 fsav.application_column_name,
186 seg.flex_value_set_id,
187 fv.validation_type,
188 PARENT_FLEX_VALUE_SET_ID
189 into l_flex_structure_id,
190 l_major_seg,
191 l_major_flex_value_set_id,
192 l_major_val_type_code,
193 l_parent_value_set_id
194 FROM fnd_id_flex_segments seg,
195 FND_SEGMENT_ATTRIBUTE_VALUES fsav,
196 FA_SYSTEM_CONTROLS sys,
197 fnd_flex_value_sets fv
198 WHERE fsav.application_id = 140
199 AND fsav.id_flex_code = 'CAT#'
200 AND fsav.id_flex_num = sys.category_flex_structure
201 AND fsav.segment_attribute_type = 'BASED_CATEGORY'
202 AND fsav.attribute_value = 'Y'
203 AND seg.application_id = 140
204 AND seg.id_flex_code = 'CAT#'
205 AND seg.id_flex_num = sys.CATEGORY_FLEX_STRUCTURE
206 AND seg.APPLICATION_COLUMN_NAME = fsav.application_column_name
207 AND fv.flex_value_set_id = seg.flex_value_set_id;
208
209 if (l_major_val_type_code = 'F') then
210 select application_table_name,
211 value_column_name,
212 id_column_name,
213 additional_where_clause
214 into l_major_table_name,
215 l_major_value_column_name,
216 l_major_id_column_name,
217 l_major_add_where_clause
218 from fnd_flex_validation_tables
219 where flex_value_set_id = l_major_flex_value_set_id;
220 end if;
221
222 If g_debug_flag = 'Y' then
223 FII_UTIL.Write_Log('major segment: ' || l_major_seg);
224 FII_UTIL.Write_Log('major segment parent value set: ' ||
225 to_char(l_parent_value_set_id));
226 FII_UTIL.Write_Log('major segment validation_type: ' ||
227 l_major_val_type_code);
228 if (l_major_val_type_code = 'F') then
229 FII_UTIL.Write_Log('major table name: ' ||
230 l_major_table_name);
231 FII_UTIL.Write_Log('major value column name: ' ||
232 l_major_value_column_name);
233 FII_UTIL.Write_Log('major id column name: ' ||
234 l_major_id_column_name);
235 end if;
236 End if;
237
238
239 -- can't accomidate a value set from multiple tables...
240 if (instrb(l_major_table_name, ',') > 0) then
241
242 FII_UTIL.Write_Log('
243 ----------------------------
244 Error occured in Procedure: GET_CAT_SEGMENTS
245 Message: ' || 'Invalid Setup: Table validated value sets may not have multiple source tables');
246
247 raise error_found;
248 end if;
249
250
251 -- can't allow a dependant value set in major category
252 if (l_parent_value_set_id is not null) then
253
254 FII_UTIL.Write_Log('
255 ----------------------------
256 Error occured in Procedure: GET_CAT_SEGMENTS
257 Message: ' || 'Invalid Setup: Major Category may not be a dependant value set');
258
259 raise error_found;
260 end if;
261
262
263 begin
264
265 select fsav.application_column_name,
266 seg.flex_value_set_id,
267 fv.validation_type,
268 PARENT_FLEX_VALUE_SET_ID
269 INTO l_minor_seg,
270 l_minor_flex_value_set_id,
271 l_minor_val_type_code,
272 l_parent_value_set_id
273 FROM fnd_id_flex_segments seg,
274 FND_SEGMENT_ATTRIBUTE_VALUES fsav,
275 FA_SYSTEM_CONTROLS sys,
276 fnd_flex_value_sets fv
277 WHERE fsav.application_id = 140
278 AND fsav.id_flex_code = 'CAT#'
279 AND fsav.id_flex_num = sys.category_flex_structure
280 AND fsav.segment_attribute_type = 'MINOR_CATEGORY'
281 AND fsav.attribute_value = 'Y'
282 AND seg.application_id = 140
283 AND seg.id_flex_code = 'CAT#'
284 AND seg.id_flex_num = sys.CATEGORY_FLEX_STRUCTURE
285 AND seg.APPLICATION_COLUMN_NAME = fsav.application_column_name
286 AND fv.flex_value_set_id = seg.flex_value_set_id;
287
288 if (l_minor_val_type_code = 'F') then
289 select application_table_name,
290 value_column_name,
291 id_column_name,
292 additional_where_clause
293 into l_minor_table_name,
294 l_minor_value_column_name,
295 l_minor_id_column_name,
296 l_minor_add_where_clause
297 from fnd_flex_validation_tables
298 where flex_value_set_id = l_minor_flex_value_set_id;
299 end if;
300
301
302 If g_debug_flag = 'Y' then
303 FII_UTIL.Write_Log('minor segment: ' || l_minor_seg);
304 FII_UTIL.Write_Log('minor segment parent value set: ' ||
305 to_char(l_parent_value_set_id));
306 FII_UTIL.Write_Log('major segment validation_type: ' ||
307 l_major_val_type_code);
308
309 if (l_minor_val_type_code = 'F') then
310 FII_UTIL.Write_Log('minor table name: ' ||
311 l_minor_table_name);
312 FII_UTIL.Write_Log('minor value column name: ' ||
313 l_minor_value_column_name);
314 FII_UTIL.Write_Log('minor id column name: ' ||
315 l_minor_id_column_name);
316 end if;
317 End if;
318
319 -- can't accomidate a value set from multiple tables...
320 if (instrb(l_minor_table_name, ',') > 0) then
321
322 FII_UTIL.Write_Log('
323 ----------------------------
324 Error occured in Procedure: GET_CAT_SEGMENTS
325 Message: ' || 'Invalid Setup: Table validated value sets may not have multiple source tables');
326
327 raise error_found;
328 end if;
329
330 if (l_parent_value_set_id <> l_major_flex_value_set_id) then
331 -- we need to know value set is the major value set to get unique values
332 FII_UTIL.Write_Log('
333 ----------------------------
334 Error occured in Procedure: GET_CAT_SEGMENTS
335 Message: ' || 'Invalid Setup: A Dependant Minor Category must be Dependent on the Major segment');
336
337 raise error_found;
338 elsif l_parent_value_set_id is not null then
339 l_dependant_value_set_flag := 'Y';
340 end if;
341
342 exception
343 when no_data_found then
344 If g_debug_flag = 'Y' then
345 FII_UTIL.Write_Log(l_calling_fn || ': minor segment not defined, continuing');
346 End if;
347
348 end;
349
350 INSERT INTO FII_FA_CAT_SEGMENTS(
351 flex_structure_id,
352 major_seg_name,
353 major_val_type_code,
354 major_table_name,
355 major_value_column_name,
356 major_id_column_name,
357 major_add_where_clause,
358 minor_seg_name,
359 minor_val_type_code,
360 minor_table_name,
361 minor_value_column_name,
362 minor_id_column_name,
363 minor_add_where_clause,
364 dependant_value_set_flag,
365 CREATION_DATE,
366 CREATED_BY,
367 LAST_UPDATE_DATE,
368 LAST_UPDATED_BY,
369 LAST_UPDATE_LOGIN
370 )
371 VALUES(
372 l_flex_structure_id,
373 l_major_seg,
374 l_major_val_type_code,
375 l_major_table_name,
376 l_major_value_column_name,
377 l_major_id_column_name,
378 l_major_add_where_clause,
379 l_minor_seg,
380 l_minor_val_type_code,
381 l_minor_table_name,
382 l_minor_value_column_name,
383 l_minor_id_column_name,
384 l_minor_add_where_clause,
385 l_dependant_value_set_flag,
386 sysdate,
387 g_fii_user_id,
388 sysdate,
389 g_fii_user_id,
390 g_fii_login_id
391 );
392
393 If g_debug_flag = 'Y' then
394 FII_UTIL.Write_Log('calling FND_STATS for FII_FA_CAT_SEGMENTS');
395 End if;
396
397 FND_STATS.gather_table_stats
398 (ownname => g_fii_schema,
399 tabname => 'FII_FA_CAT_SEGMENTS');
400
401 If g_debug_flag = 'Y' then
402 FII_MESSAGE.Func_Succ(l_calling_fn);
403 End if;
404
405 EXCEPTION
406 WHEN NO_DATA_FOUND THEN
407 -----------------------------------------------
408 -- 1. Get user name of the chart of accounts
409 -- 2. Print out translated messages to indicate
410 -- that set up for chart of account is not
411 -- complete
412 -----------------------------------------------
413 l_structure_name := GET_STRUCTURE_NAME(l_flex_structure_id);
414
415 FII_MESSAGE.write_log(
416 msg_name => 'FII_COA_SEG_NOT_FOUND',
417 token_num => 1,
418 t1 => 'COA_NAME',
419 v1 => l_structure_name);
420
421 FII_MESSAGE.write_output(
422 msg_name => 'FII_COA_SEG_NOT_FOUND',
423 token_num => 1,
424 t1 => 'COA_NAME',
425 v1 => l_structure_name);
426
427 FII_MESSAGE.Func_Fail(l_calling_fn);
428
429 RAISE;
430
431 WHEN ERROR_FOUND THEN
432 rollback;
433 FII_UTIL.Write_Log('
434 ----------------------------
435 Error occured in Procedure: GET_CAT_SEGMENTS' );
436 RAISE;
437
438 WHEN OTHERS THEN
439 rollback;
440 FII_UTIL.Write_Log('
441 ----------------------------
442 Error occured in Procedure: GET_CAT_SEGMENTS
443 Message: ' || sqlerrm);
444 FII_MESSAGE.Func_Fail(l_calling_fn);
445 RAISE;
446 END GET_CAT_SEGMENTS;
447
448
449
450 -----------------------------------------------------------------------------
451 -- PROCEDURE INSERT_INTO_CAT_DIM
452 -----------------------------------------------------------------------------
453 PROCEDURE INSERT_INTO_CAT_DIM (p_major_seg IN VARCHAR2,
454 p_major_val_type_code IN VARCHAR2,
455 p_major_table_name IN VARCHAR2,
456 p_major_value_column_name IN VARCHAR2,
457 p_major_id_column_name IN VARCHAR2,
458 -- p_major_add_where_clause IN VARCHAR2,
459 p_minor_seg IN VARCHAR2,
460 p_minor_val_type_code IN VARCHAR2,
461 p_minor_table_name IN VARCHAR2,
462 p_minor_value_column_name IN VARCHAR2,
463 p_minor_id_column_name IN VARCHAR2,
464 -- p_minor_add_where_clause IN VARCHAR2,
465 p_dependant_value_set_flag IN VARCHAR2,
466 p_max_cat_id IN VARCHAR2) IS
467
468 l_ins_stmt long;
469 l_sel_stmt long;
470 l_from_stmt long;
471 l_where_stmt long;
472
473 cursor c_major_id is
474 select distinct
475 dim1.major_value,
476 dim2.major_id
477 from fii_fa_cat_dimensions dim1,
478 fii_fa_cat_dimensions dim2
479 where dim1.major_id is null
480 and dim1.major_value = dim2.major_value(+);
481
482 cursor c_minor_id is
483 select distinct
484 dim1.minor_value,
485 dim2.minor_id
486 from fii_fa_cat_dimensions dim1,
487 fii_fa_cat_dimensions dim2
488 where dim1.minor_id is null
489 and dim1.minor_value = dim2.minor_value(+);
490
491 l_major_id_tbl num_tbl;
492 l_major_id_seq_tbl num_tbl;
493 l_major_value_tbl v30_tbl;
494 l_minor_id_tbl num_tbl;
495 l_minor_id_seq_tbl num_tbl;
496 l_minor_value_tbl v30_tbl;
497
498 l_stmt long;
499 l_calling_fn VARCHAR2(40) := 'FII_FA_CAT_C.INSERT_INTO_CAT';
500
501 BEGIN
502
503 If g_debug_flag = 'Y' then
504 FII_MESSAGE.Func_Ent(l_calling_fn);
505 End if;
506
507 IF g_debug_flag = 'Y' then
508 FII_UTIL.Write_Log('Inserting IDs in flex structure: ' ||
509 p_major_seg || ' - ' ||
510 p_minor_seg);
511 END IF;
512
513 ---------------------------------------------
514 -- Inserting records into FA CAT dimension
515 ---------------------------------------------
516
517 If g_debug_flag = 'Y' then
518 FII_UTIL.Write_Log(l_calling_fn || ': p_max_cat_id before insert: ' || to_char(p_max_cat_id));
519 FII_UTIL.Write_Log(l_calling_fn || ': p_major_seg before insert: ' || p_major_seg);
520 FII_UTIL.Write_Log(l_calling_fn || ': p_major_table_name before insert: ' || p_major_table_name);
521 FII_UTIL.Write_Log(l_calling_fn || ': p_major_value_column_name before insert: ' || p_major_value_column_name);
522 FII_UTIL.Write_Log(l_calling_fn || ': p_major_id_column_name before insert: ' || p_major_id_column_name);
523 FII_UTIL.Write_Log(l_calling_fn || ': p_minor_seg before insert: ' || p_minor_seg);
524 FII_UTIL.Write_Log(l_calling_fn || ': p_minor_table_name before insert: ' || p_minor_table_name);
525 FII_UTIL.Write_Log(l_calling_fn || ': p_minor_value_column_name before insert: ' || p_minor_value_column_name);
526 FII_UTIL.Write_Log(l_calling_fn || ': p_minor_id_column_name before insert: ' || p_minor_id_column_name);
527 FII_UTIL.Write_Log(l_calling_fn || ': p_dependant_value_set_flag before insert: ' || p_dependant_value_set_flag);
528 FII_UTIL.Write_Log(l_calling_fn || ': g_fii_user_id before insert: ' || to_char(nvl(g_fii_user_id, -99)));
529 FII_UTIL.Write_Log(l_calling_fn || ': g_fii_login_id before insert: ' || to_char(nvl(g_fii_login_id, -99)));
530 End if;
531
532
533 l_ins_stmt :=
534 'INSERT INTO FII_FA_CAT_DIMENSIONS (
535 category_id,
536 flex_structure_id,
537 creation_date,
538 created_by,
539 last_update_date,
540 last_updated_by,
541 last_update_login,
542 major_id,
543 major_value,
544 minor_id,
545 minor_value ) '; -- complete here
546
547 l_sel_stmt :=
548 ' SELECT distinct cat.category_id, -- use distinct for id based table validated sets
549 sys.category_flex_structure,
550 sysdate,
551 ' ||g_fii_user_id || ',
552 sysdate,
553 ' || g_fii_user_id || ',
554 ' || g_fii_login_id || ' , '; -- completed below
555
556 l_from_stmt :=
557 ' FROM fnd_id_flex_segments seg1,
558 FA_CATEGORIES_B cat,
559 FA_SYSTEM_CONTROLS sys, '; -- completed below
560
561 l_where_stmt :=
562 ' WHERE cat.category_id > ' || p_max_cat_id || '
563 AND seg1.application_id = 140
564 AND seg1.id_flex_code = ''CAT#''
565 AND seg1.id_flex_num = sys.CATEGORY_FLEX_STRUCTURE
566 AND seg1.APPLICATION_COLUMN_NAME = ''' || p_major_seg || '''';
567
568
569 if (p_major_val_type_code = 'F') then
570
571 if (p_major_id_column_name = '' or
572 p_major_id_column_name is null) then
573 l_sel_stmt := l_sel_stmt ||
574 ' null, ' ||
575 ' maj_tab.' || p_major_value_column_name || ' , '; -- completed below
576 else
577 l_sel_stmt := l_sel_stmt ||
578 ' maj_tab.' || p_major_id_column_name || ' , ' ||
579 ' cat.' || p_major_value_column_name || ' '; -- completed below
580 end if;
581
582 l_from_stmt := l_from_stmt ||
583 ' ' || p_major_table_name || ' maj_tab ';
584
585 l_where_stmt := l_where_stmt ||
586 ' and maj_tab.' || p_major_value_column_name || ' = ' ||
587 ' cat.' || p_major_seg || ' ';
588 else
589
590 l_sel_stmt := l_sel_stmt ||
591 ' flx1.flex_value_id, '||
592 ' flx1.flex_value, '; -- completed below
593
594 l_from_stmt := l_from_stmt ||
595 ' fnd_flex_values flx1 '; -- completed below
596
597 l_where_stmt := l_where_stmt ||
598 ' AND flx1.FLEX_VALUE = cat.' || p_major_seg ||
599 ' AND flx1.flex_value_set_id = seg1.flex_value_set_id ';
600
601 end if;
602
603
604
605 -- now process the minors...
606 -- commas are always prepended here in select/from clauses
607
608 if (p_minor_seg is null) then
609 l_sel_stmt := l_sel_stmt || ' NULL, NULL ';
610 else
611
612 l_from_stmt := l_from_stmt ||
613 ', fnd_id_flex_segments seg2 ';
614
615 l_where_stmt := l_where_stmt ||
616 ' AND seg2.application_id = 140
617 AND seg2.id_flex_code = ''CAT#''
618 AND seg2.id_flex_num = sys.CATEGORY_FLEX_STRUCTURE
619 AND seg2.APPLICATION_COLUMN_NAME = ''' || p_minor_seg || '''';
620
621 if (p_minor_val_type_code = 'F') then
622
623 if (p_minor_id_column_name = '' or
624 p_minor_id_column_name is null) then
625 l_sel_stmt := l_sel_stmt ||
626 ' null, ' ||
627 ' min_tab.' || p_minor_value_column_name; -- complete
628 else
629 l_sel_stmt := l_sel_stmt ||
630 ' min_tab.' || p_minor_id_column_name || ' , ' ||
631 ' cat.' || p_minor_value_column_name || ' '; -- complete
632 end if;
633
634 l_from_stmt := l_from_stmt ||
635 ' , ' || p_minor_table_name || ' min_tab ';
636
637 l_where_stmt := l_where_stmt ||
638 ' and min_tab.' || p_minor_value_column_name || ' = ' ||
639 ' cat.' || p_minor_seg || ' ';
640
641 else
642
643 l_sel_stmt := l_sel_stmt ||
644 ' flx2.flex_value_id, ' ||
645 ' flx2.flex_value '; -- complete
646
647 l_from_stmt := l_from_stmt ||
648 ' , fnd_flex_values flx2 '; -- complete
649
650 l_where_stmt := l_where_stmt ||
651 ' AND flx2.FLEX_VALUE = cat.' || p_minor_seg ||
652 ' AND flx2.flex_value_set_id = seg2.flex_value_set_id ';
653
654 if g_debug_flag = 'Y' then
655 FII_UTIL.Write_Log(l_calling_fn || 'checking dependant value flag');
656 end if;
657
658 if (nvl(p_dependant_value_set_flag, 'N') = 'Y') then
659 if g_debug_flag = 'Y' then
660 FII_UTIL.Write_Log(l_calling_fn || ' appending parent flex value to where clause ');
661 end if;
662
663 l_where_stmt := l_where_stmt ||
664 ' AND flx2.parent_flex_value_low = flx1.flex_value';
665 end if;
666
667 end if;
668
669 end if;
670
671
672
673
674 -- join all clauses together...
675 l_stmt := l_ins_stmt || l_sel_stmt || l_from_stmt || l_where_stmt;
676
677 If g_debug_flag = 'Y' then
678 FII_UTIL.Write_Log(l_stmt);
679 FII_UTIL.start_timer;
680 End if;
681
682 execute immediate l_stmt;
683
684 If g_debug_flag = 'Y' then
685 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' records into FII_FA_CAT_DIMENSIONS');
686 FII_UTIL.stop_timer;
687 FII_UTIL.print_timer('Duration');
688 FII_UTIL.Write_Log('');
689 End if;
690
691
692 -- if either value set is table based without an id column, then see if value already exists
693 -- with surragate key. if not, assign one...
694
695 if (p_major_val_type_code = 'F' and
696 (p_major_id_column_name = '' or
697 p_major_id_column_name is null)) then
698
699 if g_debug_flag = 'Y' then
700 FII_UTIL.Write_Log(l_calling_fn || 'opening/fetching c_major_id cursor');
701 end if;
702
703 open c_major_id;
704 fetch c_major_id bulk collect
705 into l_major_value_tbl,
706 l_major_id_tbl;
707 close c_major_id;
708
709 if g_debug_flag = 'Y' then
710 FII_UTIL.Write_Log(l_calling_fn || 'l_major_id_tbl.count: ' || to_char(l_major_id_tbl.count));
711
712 if (l_major_id_tbl.count > 0) then
713 FII_UTIL.Write_Log(l_calling_fn || 'l_major_id_tbl(1): ' || to_char(l_major_id_tbl(1)));
714 FII_UTIL.Write_Log(l_calling_fn || 'l_major_value_tbl(1): ' || to_char(l_major_value_tbl(1)));
715 end if;
716
717 end if;
718
719
720 forall i in 1..l_major_id_tbl.count
721 update fii_fa_cat_dimensions
722 set major_id = nvl(l_major_id_tbl(i), fii_fa_cat_dimensions_s.nextval)
723 where major_value = l_major_value_tbl(i);
724
725 end if;
726
727 if (p_minor_seg is not null and
728 p_minor_val_type_code = 'F' and
729 (p_minor_id_column_name = '' or
730 p_minor_id_column_name is null)) then
731
732 open c_minor_id;
733 fetch c_minor_id bulk collect
734 into l_minor_value_tbl,
735 l_minor_id_tbl;
736 close c_minor_id;
737
738 forall i in 1..l_minor_id_tbl.count
739 update fii_fa_cat_dimensions
740 set minor_id = nvl(l_minor_id_tbl(i), fii_fa_cat_dimensions_s1.nextval)
741 where minor_value = l_minor_value_tbl(i);
742
743 end if;
744
745
746
747 If g_debug_flag = 'Y' then
748 FII_MESSAGE.Func_Succ(l_calling_fn);
749 End if;
750
751 EXCEPTION
752
753 WHEN OTHERS THEN
754 rollback;
755 g_retcode := -1;
756 FII_UTIL.Write_Log('
757 -----------------------------
758 Error occured in Procedure: INSERT_INTO_CAT_DIM
759 Message: ' || sqlerrm);
760 FII_MESSAGE.Func_Fail(l_calling_fn);
761 raise;
762 END INSERT_INTO_CAT_DIM;
763
764 ------------------------------------------------------------------
765 -- PROCEDURE RECORD_MAX_PROCESSED_CAT_ID
766 ------------------------------------------------------------------
767 PROCEDURE RECORD_MAX_PROCESSED_CAT_ID IS
768
769 l_tmp_max_cat_id NUMBER;
770 l_calling_fn varchar2(40) := 'FII_FA_CAT_C.RECORD_MAX_PROCESSED_CAT_ID';
771
772 BEGIN
773
774 If g_debug_flag = 'Y' then
775 FII_MESSAGE.Func_Ent(l_calling_fn);
776 End if;
777
778 g_phase := 'Updating max CAT ID processed';
779
780 If g_debug_flag = 'Y' then
781 FII_UTIL.Write_Log('');
782 FII_UTIL.Write_Log(g_phase);
783 FII_UTIL.start_timer;
784 End if;
785
786 --------------------------------------------------------------
787 -- Get the real max cat id that was inserted into CAT dimension
788 -- the g_new_max_cat_id recorded at the beginning of the program
789 -- may not necessary be the largest ID that was inserted.
790 -- New ids could have been created while the program is
791 -- running. So record this max cat id from fii_fa_cat_dimensions
792 --
793 -- Note that original g_new_max_cat_id is from FA_CATEGORIES_B,
794 --------------------------------------------------------------
795
796 g_phase := 'SELECT FROM fii_fa_cat_dimensions';
797
798 SELECT MAX(category_id)
799 INTO l_tmp_max_cat_id
800 FROM fii_fa_cat_dimensions;
801
802 If g_debug_flag = 'Y' then
803 FII_UTIL.Write_Log(l_calling_fn || ': l_tmp_max_cat_id: ' || to_char(l_tmp_max_cat_id));
804 End if;
805
806
807 -- we should pick the larger one for g_new_max_cat_id
808 -- between l_tmp_max_cat_id and the original g_new_max_cat_id
809 if g_new_max_cat_id < l_tmp_max_cat_id then
810 g_new_max_cat_id := l_tmp_max_cat_id;
811 end if;
812
813 g_phase := 'UPDATE fii_change_log';
814
815 -- we also update PROD_CAT_SET_ID here
816 UPDATE fii_change_log
817 SET item_value = to_char(g_new_max_cat_id),
818 last_update_date = SYSDATE,
819 last_update_login = g_fii_login_id,
820 last_updated_by = g_fii_user_id
821 WHERE log_item = 'MAX_ASSET_CAT_ID';
822
823 If g_debug_flag = 'Y' then
824 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_change_log');
825 End if;
826
827 If g_debug_flag = 'Y' then
828 FII_UTIL.stop_timer;
829 FII_UTIL.print_timer('Duration');
830 FII_UTIL.Write_Log('');
831 End if;
832
833 If g_debug_flag = 'Y' then
834 FII_MESSAGE.Func_Succ(l_calling_fn);
835 End if;
836
837 EXCEPTION
838 WHEN OTHERS THEN
839 rollback;
840 g_retcode := -1;
841 FII_UTIL.Write_Log('
842 -------------------------------------------
843 Error occured in Procedure: RECORD_MAX_PROCESSED_CAT_ID
844 Phase: ' || g_phase || '
845 Message: ' || sqlerrm);
846 FII_MESSAGE.Func_Fail(l_calling_fn);
847 raise;
848
849 END RECORD_MAX_PROCESSED_CAT_ID;
850
851 ------------------------------------------------------------------
852 -- FUNCTION NEW_CAT_IN_FA
853 ------------------------------------------------------------------
854 FUNCTION NEW_CAT_IN_FA RETURN BOOLEAN IS
855
856 l_calling_fn varchar2(40) := 'FII_FA_CAT_C.NEW_CAT_IN_FA';
857
858 BEGIN
859
860 If g_debug_flag = 'Y' then
861 FII_MESSAGE.Func_Ent(l_calling_fn);
862 End if;
863
864 g_phase := 'Identifying Max CAT ID processed';
865
866 If g_debug_flag = 'Y' then
867 FII_UTIL.Write_Log(g_phase);
868 FII_UTIL.Write_Log('');
869 End if;
870
871 SELECT item_value
872 INTO g_max_cat_id
873 FROM fii_change_log
874 WHERE log_item = 'MAX_ASSET_CAT_ID';
875
876 If g_debug_flag = 'Y' then
877 FII_UTIL.Write_Log(l_calling_fn || ': g_max_cat_id: ' || to_char(g_max_cat_id));
878 End if;
879
880
881 g_phase := 'Identifying current Max Cat ID in FA';
882 If g_debug_flag = 'Y' then
883 FII_UTIL.Write_Log(g_phase);
884 FII_UTIL.Write_Log('');
885 End if;
886
887 SELECT max(category_id)
888 INTO g_new_max_cat_id
889 FROM fa_categories;
890
891 If g_debug_flag = 'Y' then
892 FII_UTIL.Write_Log(l_calling_fn || ': g_max_cat_id: ' || to_char(g_max_cat_id));
893 End if;
894
895
896 If g_debug_flag = 'Y' then
897 FII_MESSAGE.Func_Succ(l_calling_fn);
898 End if;
899
900 IF g_new_max_cat_id > g_max_cat_id THEN
901 RETURN TRUE;
902 ELSE
903 RETURN FALSE;
904 END IF;
905
906 EXCEPTION
907 WHEN OTHERS THEN
908 rollback;
909 g_retcode := -1;
910 FII_UTIL.Write_Log('
911 -------------------------------------------
912 Error occured in Function: NEW_CAT_IN_FA
913 Phase: ' || g_phase || '
914 Message: ' || sqlerrm);
915 FII_MESSAGE.Func_Fail(l_calling_fn);
916 raise;
917 END NEW_CAT_IN_FA;
918
919
920
921 ------------------------------------------------------------------
922 -- PROCEDURE INSERT_NEW_CAT
923 ------------------------------------------------------------------
924 PROCEDURE INSERT_NEW_CAT IS
925
926 CURSOR sss_list IS
927 SELECT DISTINCT major_seg_name,
928 major_val_type_code,
929 major_table_name,
930 major_value_column_name,
931 major_id_column_name,
932 minor_seg_name,
933 minor_val_type_code,
934 minor_table_name,
935 minor_value_column_name,
936 minor_id_column_name,
937 dependant_value_set_flag
938 FROM FII_FA_CAT_SEGMENTS;
939
940 l_calling_fn varchar2(40) := 'FII_FA_CAT_ID_C.INSERT_NEW_CAT';
941
942 BEGIN
943
944 If g_debug_flag = 'Y' then
945 FII_MESSAGE.Func_Ent(l_calling_fn);
946 End if;
947
948 g_phase := 'Identifying Max CAT ID processed';
949 If g_debug_flag = 'Y' then
950 FII_UTIL.Write_Log(g_phase);
951 FII_UTIL.Write_Log('');
952 End if;
953
954 SELECT item_value
955 INTO g_max_cat_id
956 FROM fii_change_log
957 WHERE log_item = 'MAX_ASSET_CAT_ID';
958
959 If g_debug_flag = 'Y' then
960 FII_UTIL.Write_Log(l_calling_fn || ': g_max_cat_id: ' || to_char(g_max_cat_id));
961 End if;
962
963
964 g_phase := 'Identifying current Max CAT ID in FA';
965 If g_debug_flag = 'Y' then
966 FII_UTIL.Write_Log(g_phase);
967 FII_UTIL.Write_Log('');
968 End if;
969
970 ------------------------------------------------------
971 -- g_mode = 'L' if program is run in Initial Load mode
972 ------------------------------------------------------
973 IF (g_mode = 'L') then
974
975 --Clean up the CAT dimension table
976
977 g_phase := 'TRUNCATE FII_FA_CAT_DIMENSIONS';
978
979 FII_UTIL.TRUNCATE_TABLE('FII_FA_CAT_DIMENSIONS',g_fii_schema,g_retcode);
980
981 --Update FII_CHANGE_LOG to reset MAX_CAT_ID
982
983 g_phase := 'UPDATE fii_change_log';
984
985 UPDATE fii_change_log
986 SET item_value = '0',
987 last_update_date = sysdate,
988 last_update_login = g_fii_login_id,
989 last_updated_by = g_fii_user_id
990 WHERE log_item = 'MAX_ASSET_CAT_ID';
991
992 If g_debug_flag = 'Y' then
993 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_change_log');
994 End if;
995
996 g_max_cat_id := 0;
997
998 END IF;
999
1000 g_phase := 'SELECT FROM fa_categories';
1001
1002 SELECT max(category_id)
1003 INTO g_new_max_cat_id
1004 FROM fa_categories_b;
1005
1006 IF (g_new_max_cat_id > g_max_cat_id) THEN
1007
1008 g_phase := 'Insert new CAT IDs into FII_FA_CAT_DIMENSIONS table';
1009 If g_debug_flag = 'Y' then
1010 FII_UTIL.Write_Log(g_phase);
1011 FII_UTIL.Write_Log('');
1012 End if;
1013
1014 -----------------------------------------------------------------
1015 -- Using this SQL to get major and minor segments for each
1016 -- flex structure. These information are needed to build the
1017 -- dynamic SQL in the INSERT_INTO_CAT_ID API.
1018 --
1019 -- NOTE in FA this is not a global temp table but a permanent
1020 -- one used not only here, but in the PMV's as well
1021 -- only call get_cat_segments when called in initial mode
1022 -----------------------------------------------------------------
1023
1024 if (g_mode = 'L') then
1025 FII_UTIL.TRUNCATE_TABLE('FII_FA_CAT_SEGMENTS', g_fii_schema, g_retcode);
1026
1027 g_phase := 'INSERT INTO FII_FA_CAT_SEGMENTS';
1028
1029 GET_CAT_SEGMENTS;
1030 end if;
1031
1032 ----------------------------------------------------
1033 -- Looping through each group of COA_IDs in the
1034 -- FII_FA_CAT_SEGMENTS table to process the CAT IDs
1035 ----------------------------------------------------
1036
1037 FOR sss IN sss_list LOOP
1038
1039 If g_debug_flag = 'Y' then
1040 FII_UTIL.Write_Log(l_calling_fn || ': in sss loop');
1041 End if;
1042
1043
1044 g_phase := 'Call INSERT_INTO_CAT_ID_DIM';
1045
1046 INSERT_INTO_CAT_DIM(
1047 sss.major_seg_name,
1048 sss.major_val_type_code,
1049 sss.major_table_name,
1050 sss.major_value_column_name,
1051 sss.major_id_column_name,
1052 sss.minor_seg_name,
1053 sss.minor_val_type_code,
1054 sss.minor_table_name,
1055 sss.minor_value_column_name,
1056 sss.minor_id_column_name,
1057 sss.dependant_value_set_flag,
1058 g_max_cat_id
1059 );
1060
1061 END LOOP;
1062
1063 ------------------------------------------------------
1064 -- Record the max CCID processed
1065 ------------------------------------------------------
1066 g_phase := 'Call RECORD_MAX_PROCESSED_CAT_ID';
1067
1068 RECORD_MAX_PROCESSED_CAT_ID;
1069
1070 ELSE
1071 If g_debug_flag = 'Y' then
1072 FII_UTIL.Write_Log('No new CCID in GL');
1073 End if;
1074 END IF;
1075
1076
1077 --------------------------------------------------------
1078 -- Gather statistics for the use of cost-based optimizer
1079 --------------------------------------------------------
1080 -- Will seed this in RSG?
1081 -- Per DBI - needs to be done though, can take out later if needed
1082 FND_STATS.gather_table_stats
1083 (ownname => g_fii_schema,
1084 tabname => 'FII_FA_CAT_DIMENSIONS');
1085
1086 If g_debug_flag = 'Y' then
1087 FII_MESSAGE.Func_Succ(l_calling_fn);
1088 End if;
1089
1090 EXCEPTION
1091
1092 WHEN OTHERS THEN
1093
1094 if g_mode = 'L' then
1095
1096 --program is run in Initial Load mode, truncate the table and reset LOG
1097
1098 FII_UTIL.TRUNCATE_TABLE('FII_FA_CAT_DIMENSIONS',g_fii_schema,g_retcode);
1099
1100 UPDATE fii_change_log
1101 SET item_value = '0',
1102 last_update_date = sysdate,
1103 last_update_login = g_fii_login_id,
1104 last_updated_by = g_fii_user_id
1105 WHERE log_item = 'MAX_ASSET_CAT_ID';
1106
1107 g_max_cat_id := 0;
1108
1109 end if;
1110
1111 rollback;
1112 g_retcode := -1;
1113 FII_UTIL.Write_Log('
1114 -----------------------------
1115 Error occured in Procedure: INSERT_NEW_CAT
1116 Phase: ' || g_phase || '
1117 Message: ' || sqlerrm);
1118 FII_MESSAGE.Func_Fail(l_calling_fn);
1119 raise;
1120
1121 END INSERT_NEW_CAT;
1122
1123 -----------------------------------------------------
1124 -- PROCEDURE USE_RANGES
1125 -----------------------------------------------------
1126 -- no need for this in FA
1127
1128 -------------------------------------------------------
1129 -- FUNCTION INVALID_PROD_CODE_EXIST
1130 -------------------------------------------------------
1131 -- no need for this in FA
1132
1133 -------------------------------------------------------
1134 -- PROCEDURE MAINTAIN_PROD_ASSGN
1135 -------------------------------------------------------
1136 -- not needed for FA
1137
1138
1139 --------------------------------------------------------
1140 -- PROCEDURE INITIALIZE
1141 --------------------------------------------------------
1142 PROCEDURE INITIALIZE is
1143
1144 l_status VARCHAR2(30);
1145 l_industry VARCHAR2(30);
1146 l_stmt VARCHAR2(50);
1147 l_dir VARCHAR2(100);
1148 l_old_prod_cat NUMBER(15);
1149 l_check NUMBER;
1150
1151 l_calling_fn VARCHAR2(40) := 'FII_FA_CAT_ID_C.INITIALIZE';
1152
1153 BEGIN
1154
1155 If g_debug_flag = 'Y' then
1156 FII_MESSAGE.Func_Ent(l_calling_fn);
1157 End if;
1158
1159 ----------------------------------------------
1160 -- Do set up for log file
1161 ----------------------------------------------
1162 g_phase := 'Set up for log file';
1163
1164 If g_debug_flag = 'Y' then
1165 FII_UTIL.Write_Log(g_phase);
1166 End if;
1167
1168 l_dir := fnd_profile.value('BIS_DEBUG_LOG_DIRECTORY');
1169 ------------------------------------------------------
1170 -- Set default directory in case if the profile option
1171 -- BIS_DEBUG_LOG_DIRECTORY is not set up
1172 ------------------------------------------------------
1173 if l_dir is NULL then
1174 l_dir := FII_UTIL.get_utl_file_dir ;
1175 end if;
1176
1177 ----------------------------------------------------------------
1178 -- FII_UTIL.initialize will get profile options FII_DEBUG_MODE
1179 -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
1180 -- the log files and output files are written to
1181 ----------------------------------------------------------------
1182 FII_UTIL.initialize('FII_FA_CAT_ID.log','FII_FA_CAT_ID.out',l_dir, 'FII_FA_CAT_ID_C');
1183
1184 -- --------------------------------------------------------
1185 -- Check source ledger setup for DBI
1186 -- --------------------------------------------------------
1187 g_phase := 'Check source ledger setup for DBI';
1188 if g_debug_flag = 'Y' then
1189 FII_UTIL.write_log(g_phase);
1190 end if;
1191
1192 l_check := FII_EXCEPTION_CHECK_PKG.check_slg_setup;
1193
1194 if l_check <> 0 then
1195 RAISE G_NO_SLG_SETUP;
1196 end if;
1197
1198 -- --------------------------------------------------------
1199 -- Find out the user ID, login ID, and current language
1200 -- --------------------------------------------------------
1201 g_phase := 'Find User ID, Login ID, and Current Language';
1202
1203 If g_debug_flag = 'Y' then
1204 FII_UTIL.Write_Log(g_phase);
1205 End if;
1206
1207 g_fii_user_id := FND_GLOBAL.User_Id;
1208 g_fii_login_id := FND_GLOBAL.Login_Id;
1209 g_current_language := FND_GLOBAL.current_language;
1210
1211 IF (g_fii_user_id IS NULL OR g_fii_login_id IS NULL) THEN
1212 RAISE G_LOGIN_INFO_NOT_AVABLE;
1213 END IF;
1214 -- --------------------------------------------------------
1215 -- Find the schema owner
1216 -- --------------------------------------------------------
1217 g_phase := 'Find schema owner for FII';
1218
1219 If g_debug_flag = 'Y' then
1220 FII_UTIL.Write_Log(g_phase);
1221 End if;
1222
1223 IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, g_fii_schema))
1224 THEN NULL;
1225 END IF;
1226
1227 If g_debug_flag = 'Y' then
1228 FII_MESSAGE.Func_Succ(l_calling_fn);
1229 End if;
1230
1231 EXCEPTION
1232
1233 WHEN G_NO_SLG_SETUP THEN
1234 FII_UTIL.write_log ('No source ledger setup for DBI');
1235 g_retcode := -1;
1236 FII_MESSAGE.Func_Fail(l_calling_fn);
1237 raise;
1238
1239 WHEN G_LOGIN_INFO_NOT_AVABLE THEN
1240 FII_UTIL.Write_Log ('Can not get User ID and Login ID, program exit');
1241 g_retcode := -1;
1242 FII_MESSAGE.Func_Fail(l_calling_fn);
1243 raise;
1244
1245 WHEN OTHERS THEN
1246 g_retcode := -1;
1247 FII_UTIL.Write_Log('
1248 ------------------------
1249 Error in Procedure: INITIALIZE
1250 Phase: '||g_phase||'
1251 Message: '||sqlerrm);
1252 FII_MESSAGE.Func_Fail(l_calling_fn);
1253 raise;
1254
1255 END INITIALIZE;
1256
1257 -----------------------------------------------------------------
1258 -- PROCEDURE DETECT_RELOAD
1259 --
1260 -- NOTE: currently such a procedure is NOT needed because FA only
1261 -- allows for one category flex structure per instance
1262 -- If this is enhanced later on, this will need to added
1263 -- reference GLCCID dimensions code for example
1264 --
1265 -----------------------------------------------------------------
1266
1267 -----------------------------------------------------------------
1268 -- PROCEDURE MAIN
1269 -----------------------------------------------------------------
1270 PROCEDURE Main (errbuf IN OUT NOCOPY VARCHAR2 ,
1271 retcode IN OUT NOCOPY VARCHAR2,
1272 pmode IN VARCHAR2) IS
1273
1274 ret_val BOOLEAN := FALSE;
1275 l_calling_fn VARCHAR2(40) := 'FII_FA_CAT_ID_C.Main';
1276
1277 BEGIN
1278
1279 If g_debug_flag = 'Y' then
1280 FII_MESSAGE.Func_Ent(l_calling_fn);
1281 End if;
1282
1283 errbuf := NULL;
1284 retcode := 0;
1285 g_retcode := 0;
1286 g_mode := pmode;
1287
1288 ---------------------------------------------------
1289 -- Initialize all global variables from profile
1290 -- options and other resources
1291 ---------------------------------------------------
1292 g_phase := 'Call INITIALIZE';
1293
1294 INITIALIZE;
1295
1296 ---------------------------------------------------
1297 -- Clean up temporary tables used by the program
1298 ---------------------------------------------------
1299 -- FII_UTIL.TRUNCATE_TABLE ('FII_FA_CAT_PROD_INT', g_fii_schema, g_retcode);
1300
1301 ---------------------------------------------------
1302 -- Inserting the basic items into FII_CHANGE_LOG if
1303 -- they have not been inserted
1304 ---------------------------------------------------
1305 g_phase := 'Call INIT_DBI_CHANGE_LOG';
1306
1307 INIT_DBI_CHANGE_LOG;
1308
1309 ---------------------------------------------------
1310 -- Populate the global temp table FII_CCID_SLGMENTS
1311 ---------------------------------------------------
1312 -- g_phase := 'Call POPULATE_SLG_TMP';
1313
1314 -- POPULATE_SLG_TMP;
1315
1316 ---------------------------------------------------
1317 -- Check if program is called in Initial mode
1318 ---------------------------------------------------
1319 if (g_mode = 'L') then
1320
1321 NULL;
1322
1323 ELSE
1324
1325 ----------------------------------------------------
1326 -- Detect if there's changes in fii_slg_assignments
1327 -- table. If yes, then truncate CCID dimension and
1328 -- reset the max CCID processed to 0
1329 --
1330 -- Since FA doesn't allow for multiple flex structures
1331 -- and because the setup shouldn't be changed, we are
1332 -- rmeoving this reload logic. See GLCCID if ever
1333 -- this is deemed required
1334 -----------------------------------------------------
1335
1336 -- g_phase := 'Call DETECT_RELOAD';
1337 -- DETECT_RELOAD;
1338
1339 NULL;
1340
1341 END IF;
1342
1343 ----------------------------------------------------
1344 -- Find out what are the new CCIDs to process and
1345 -- insert these new CCIDs into FII_FA_CAT_ID_DIMENSIONS
1346 -- table
1347 -----------------------------------------------------
1348 g_phase := 'Call INSERT_NEW_CAT';
1349
1350 INSERT_NEW_CAT;
1351
1352 ----------------------------------------------------
1353 -- Set CCID_RELOAD flag to 'N' after an initial load
1354 -- Bug 3401590
1355 --
1356 -- Since FA doesn't allow for multiple flex structures
1357 -- and because the setup shouldn't be changed, we are
1358 -- removing the update for reload item in fii_change_log
1359 -- See GLCCID if ever this is deemed required
1360 ----------------------------------------------------
1361
1362 ---------------------------------------------------
1363 -- Clean up temporary tables before exit
1364 ---------------------------------------------------
1365
1366 ------------------------------------------------------
1367 -- We have finished the data processing for CCID table
1368 -- it is a logical point to commit.
1369 ------------------------------------------------------
1370 COMMIT;
1371
1372 retcode := g_retcode;
1373
1374 If g_debug_flag = 'Y' then
1375 FII_MESSAGE.Func_Succ(l_calling_fn);
1376 End if;
1377
1378 EXCEPTION
1379 WHEN OTHERS THEN
1380 rollback;
1381
1382 FII_UTIL.Write_Log('
1383 -----------------------------
1384 Error occured in Procedure: MAIN
1385 Phase: ' || g_phase || '
1386 Message: ' || sqlerrm);
1387
1388 FII_MESSAGE.Func_Fail(l_calling_fn);
1389
1390 retcode := g_retcode;
1391 ret_val := FND_CONCURRENT.Set_Completion_Status
1392 (status => 'ERROR', message => substr(sqlerrm,1,180));
1393 END MAIN;
1394
1395 END FII_FA_CAT_C;