[Home] [Help]
PACKAGE BODY: APPS.FII_GL_CCID_C
Source
1 PACKAGE BODY FII_GL_CCID_C AS
2 /* $Header: FIIGLCCB.pls 120.52 2006/03/27 19:15:22 juding 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_prod_cat_set_id NUMBER := -999;
11 g_mtc_structure_id NUMBER;
12 g_mtc_value_set_id NUMBER;
13 g_mtc_column_name VARCHAR2(30) := NULL;
14 g_fii_user_id NUMBER;
15 g_fii_login_id NUMBER;
16 g_current_language VARCHAR2(30);
17 g_max_ccid NUMBER;
18 g_new_max_ccid NUMBER;
19 g_mode VARCHAR2(1);
20 g_log_item VARCHAR2(50);
21 g_dimension_name VARCHAR2(30);
22
23 G_LOGIN_INFO_NOT_AVABLE EXCEPTION;
24 G_DUPLICATE_PROD_ASGN EXCEPTION;
25 G_NO_PROD_SEG_DEFINED EXCEPTION;
26 G_INVALID_PROD_CODE_EXIST EXCEPTION;
27 G_NEW_PROD_CAT_FOUND EXCEPTION;
28 G_NO_SLG_SETUP EXCEPTION;
29 G_NO_UNASSIGNED_ID EXCEPTION;
30 G_PROD_CAT_ENABLED_FLAG VARCHAR2(1) := 'N';
31 G_UD1_ENABLED VARCHAR2(1) := 'N';
32 G_UD2_ENABLED VARCHAR2(1) := 'N';
33 G_UNASSIGNED_ID NUMBER(15);
34
35 -- ---------------------------------------------------------------
36 -- Private procedures and Functions;
37 -- ---------------------------------------------------------------
38
39 -- ---------------------------------------------------------------
40 -- PROCEDURE INIT_DBI_CHANGE_LOG
41 -- ---------------------------------------------------------------
42
43 PROCEDURE INIT_DBI_CHANGE_LOG IS
44 BEGIN
45
46 If g_debug_flag = 'Y' then
47 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.INIT_DBI_CHANGE_LOG');
48 End if;
49
50 If g_debug_flag = 'Y' then
51 FII_UTIL.Write_Log('Inserting DBI log items into FII_CHANGE_LOG');
52 End if;
53 ---------------------------------------------
54 -- Populate FII_CHANGE_LOG with inital set up
55 -- entries if it hasn't been set up already
56 ---------------------------------------------
57 INSERT INTO FII_CHANGE_LOG (
58 log_item,
59 item_value,
60 creation_date,
61 created_by,
62 last_update_date,
63 last_update_login,
64 last_updated_by)
65 SELECT
66 DECODE(glrm.multiplier,
67 1, 'AR_RESUMMARIZE',
68 2, 'GL_RESUMMARIZE',
69 3, 'AP_RESUMMARIZE',
70 4, 'MAX_CCID',
71 5, 'CCID_RELOAD',
72 6, 'PROD_CAT_SET_ID',
73 7, 'GL_PROD_CHANGE',
74 8, 'AR_PROD_CHANGE'),
75 DECODE(glrm.multiplier,
76 1, 'N',
77 2, 'N',
78 3, 'N',
79 4, '0',
80 5, 'N',
81 6, G_PROD_CAT_SET_ID,
82 7, 'N',
83 8, 'N'),
84 sysdate,
85 g_fii_user_id,
86 sysdate,
87 g_fii_login_id,
88 g_fii_user_id
89 FROM GL_ROW_MULTIPLIERS glrm
90 WHERE glrm.multiplier between 1 and 8
91 AND NOT EXISTS
92 (SELECT 1
93 FROM FII_CHANGE_LOG
94 WHERE log_item = DECODE(glrm.multiplier,
95 1, 'AR_RESUMMARIZE',
96 2, 'GL_RESUMMARIZE',
97 3, 'AP_RESUMMARIZE',
98 4, 'MAX_CCID',
99 5, 'CCID_RELOAD',
100 6, 'PROD_CAT_SET_ID',
101 7, 'GL_PROD_CHANGE',
102 8, 'AR_PROD_CHANGE'));
103
104 If g_debug_flag = 'Y' then
105 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' log items into FII_CHANGE_LOG');
106 End if;
107
108 If g_debug_flag = 'Y' then
109 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.INIT_DBI_CHANGE_LOG');
110 End if;
111
112 EXCEPTION
113
114 WHEN OTHERS THEN
115 rollback;
116 g_retcode := -1;
117 FII_UTIL.Write_Log('
118 Error occured in Procedure: INIT_DBI_CHANGE_LOG
119 Message: ' || sqlerrm);
120 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.INIT_DBI_CHANGE_LOG');
121 raise;
122
123 END INIT_DBI_CHANGE_LOG;
124
125 -------------------------------------------------------
126 -- FUNCTION GET_COA_NAME
127 -------------------------------------------------------
128 FUNCTION GET_COA_NAME (p_coa_id IN NUMBER) RETURN VARCHAR2 IS
129
130 l_coa_name VARCHAR2(30);
131
132 BEGIN
133 If g_debug_flag = 'Y' then
134 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.GET_COA_NAME');
135 End if;
136
137 g_phase := 'Getting user name for chart of account ID: ' || p_coa_id;
138
139 SELECT DISTINCT id_flex_structure_name INTO l_coa_name
140 FROM fnd_id_flex_structures_tl t
141 WHERE application_id = 101
142 AND id_flex_code = 'GL#'
143 AND id_flex_num = p_coa_id
144 AND language = g_current_language;
145
146 If g_debug_flag = 'Y' then
147 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.GET_COA_NAME');
148 End if;
149
150 return l_coa_name;
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 g_retcode := -1;
155 FII_UTIL.Write_Log('
156 ------------------------
157 Error in Function: GET_COA_NAME
158 Phase: '||g_phase||'
159 Message: '||sqlerrm);
160 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.GET_COA_NAME');
161 raise;
162
163 END GET_COA_NAME;
164
165 ---------------------------------------------------------------------
166 -- PROCEDURE GET_ACCT_SEGMENTS
167 ---------------------------------------------------------------------
168 PROCEDURE GET_ACCT_SEGMENTS (p_coa_id IN NUMBER,
169 p_company_seg OUT NOCOPY VARCHAR2,
170 p_cc_seg OUT NOCOPY VARCHAR2,
171 p_natural_seg OUT NOCOPY VARCHAR2) IS
172
173 v_coa_name VARCHAR2(30);
174
175 BEGIN
176
177 If g_debug_flag = 'Y' then
178 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.GET_ACCT_SEGMENTS');
179 End if;
180
181 If g_debug_flag = 'Y' then
182 FII_UTIL.Write_Log('Getting Account segments column information for chart of account ID: '
183 || p_coa_id);
184 End if;
185 ----------------------------------------------------
186 -- Given a chart of account ID, it will get:
187 -- 1. Balancing segment
188 -- 2. Accounting segment
189 -- 3. Cost Center segment
190 -- of the chart of acccounts
191 -----------------------------------------------------
192 SELECT fsav1.application_column_name,
193 fsav2.application_column_name,
194 fsav3.application_column_name
195 INTO p_company_seg,
196 p_cc_seg,
197 p_natural_seg
198 FROM FND_SEGMENT_ATTRIBUTE_VALUES fsav1,
199 FND_SEGMENT_ATTRIBUTE_VALUES fsav2,
200 FND_SEGMENT_ATTRIBUTE_VALUES fsav3
201 WHERE fsav1.application_id = 101
202 AND fsav1.id_flex_code = 'GL#'
203 AND fsav1.id_flex_num = p_coa_id
204 AND fsav1.segment_attribute_type = 'GL_BALANCING'
205 AND fsav1.attribute_value = 'Y'
206 AND fsav2.application_id = 101
207 AND fsav2.id_flex_code = 'GL#'
208 AND fsav2.id_flex_num = p_coa_id
209 AND fsav2.segment_attribute_type = 'FA_COST_CTR'
210 AND fsav2.attribute_value = 'Y'
211 AND fsav3.application_id = 101
212 AND fsav3.id_flex_code = 'GL#'
213 AND fsav3.id_flex_num = p_coa_id
214 AND fsav3.segment_attribute_type = 'GL_ACCOUNT'
215 AND fsav3.attribute_value = 'Y';
216
217 If g_debug_flag = 'Y' then
218 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.GET_ACCT_SEGMENTS');
219 End if;
220
221 EXCEPTION
222 WHEN NO_DATA_FOUND THEN
223 -----------------------------------------------
224 -- 1. Get user name of the chart of accounts
225 -- 2. Print out translated messages to indicate
226 -- that set up for chart of account is not
227 -- complete
228 -----------------------------------------------
229 v_coa_name := GET_COA_NAME(p_coa_id);
230
231 FII_MESSAGE.write_log(
232 msg_name => 'FII_COA_SEG_NOT_FOUND',
233 token_num => 1,
234 t1 => 'COA_NAME',
235 v1 => v_coa_name);
236
237 FII_MESSAGE.write_output(
238 msg_name => 'FII_COA_SEG_NOT_FOUND',
239 token_num => 1,
240 t1 => 'COA_NAME',
241 v1 => v_coa_name);
242
243 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.GET_ACCT_SEGMENTS');
244
245 RAISE;
246
247 WHEN OTHERS THEN
248 rollback;
249 FII_UTIL.Write_Log('
250 ----------------------------
251 Error occured in Procedure: GET_ACCT_SEGMENTS
252 Message: ' || sqlerrm);
253 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.GET_ACCT_SEGMENTS');
254 RAISE;
255 END GET_ACCT_SEGMENTS;
256
257 -----------------------------------------------------------------------------
258 -- PROCEDURE POPULATE_SLG_TMP
259 -- This procedure populates the global temp table FII_CCID_SLG_GT
260 -----------------------------------------------------------------------------
261 Procedure POPULATE_SLG_TMP IS
262
263 l_coa_id number(15);
264 l_company_seg varchar2(120);
265 l_stmt varchar2(1000);
266
267 Cursor tmp_coa_list IS
268 select distinct COA_ID
269 from FII_CCID_SLG_GT
270 where BAL_SEG_VALUE_ID = -1;
271
272 Begin
273
274 If g_debug_flag = 'Y' then
275 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.POPULATE_SLG_TMP');
276 End if;
277
278 IF g_debug_flag = 'Y' then
279 FII_UTIL.Write_Log('Insert to FII_CCID_SLG_GT by select DISTINCT ');
280 END IF;
281
282 insert into FII_CCID_SLG_GT
283 (COA_ID,
284 BAL_SEG_VALUE,
285 BAL_SEG_VALUE_ID)
286 select DISTINCT
287 sts.chart_of_accounts_id,
288 sts.bal_seg_value,
289 sts.bal_seg_value_id
290 from fii_slg_assignments sts,
291 fii_source_ledger_groups slg
292 where slg.usage_code = 'DBI'
293 and slg.source_ledger_group_id = sts.source_ledger_group_id;
294
295 If g_debug_flag = 'Y' then
296 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_CCID_SLG_GT');
297 End if;
298
299 IF g_debug_flag = 'Y' then
300 FII_UTIL.Write_Log('Update FII_CCID_SLG_GT for BAL_SEG_VALUE_ID = -1 (insert all company values)');
301 END IF;
302
303 For l_coa_rec IN tmp_coa_list LOOP
304 l_coa_id := l_coa_rec.COA_ID;
305
306 delete from FII_CCID_SLG_GT where COA_ID = l_coa_id;
307
308 SELECT application_column_name INTO l_company_seg
309 FROM FND_SEGMENT_ATTRIBUTE_VALUES
310 WHERE application_id = 101
311 AND id_flex_code = 'GL#'
312 AND id_flex_num = l_coa_id
313 AND segment_attribute_type = 'GL_BALANCING'
314 AND attribute_value = 'Y';
315
316 l_stmt := 'INSERT INTO FII_CCID_SLG_GT
317 (COA_ID,
318 BAL_SEG_VALUE,
319 BAL_SEG_VALUE_ID)
320 SELECT DISTINCT
321 CHART_OF_ACCOUNTS_ID,
322 ' || l_company_seg || ',
323 -2
324 FROM GL_CODE_COMBINATIONS
325 WHERE CHART_OF_ACCOUNTS_ID = ' || l_coa_id || '
326 AND SUMMARY_FLAG = ''N''
327 AND TEMPLATE_ID IS NULL ';
328
329 If g_debug_flag = 'Y' then
330 FII_UTIL.Write_Log(' ');
331 FII_UTIL.Write_Log(l_stmt);
332 End if;
333
334 EXECUTE IMMEDIATE l_stmt;
335
336 If g_debug_flag = 'Y' then
337 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_CCID_SLG_GT');
338 End if;
339
340 END LOOP;
341
342 If g_debug_flag = 'Y' then
343 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.POPULATE_SLG_TMP');
344 End if;
345
346 Exception
347
348 WHEN OTHERS THEN
349 rollback;
350 g_retcode := -1;
351 FII_UTIL.Write_Log('
352 -----------------------------
353 Error occured in Procedure: POPULATE_SLG_TMP;
354 Message: ' || sqlerrm);
355 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.POPULATE_SLG_TMP');
356 raise;
357 End POPULATE_SLG_TMP;
358
359 -----------------------------------------------------------------------------
360 -- PROCEDURE INSERT_INTO_CCID_DIM
361 -----------------------------------------------------------------------------
362 PROCEDURE INSERT_INTO_CCID_DIM (p_company_seg IN VARCHAR2,
363 p_cc_seg IN VARCHAR2,
364 p_natural_seg IN VARCHAR2,
365 p_ud1_seg IN VARCHAR2,
366 p_ud2_seg IN VARCHAR2) IS
367 l_stmt VARCHAR2(10000);
368
369 BEGIN
370 If g_debug_flag = 'Y' then
371 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.INSERT_INTO_CCID_DIM');
372 End if;
373
374 IF g_debug_flag = 'Y' then
375 FII_UTIL.Write_Log('Inserting CCIDs in chart of accounts: ' ||
376 p_company_seg || ' - ' ||
377 p_cc_seg || ' - ' ||
378 p_natural_seg || ' - ' ||
379 p_ud1_seg || ' - ' ||
380 p_ud2_seg);
381 END IF;
382
383 ---------------------------------------------
384 -- Inserting records into GL CCID dimension
385 -- Two new dimensions UD1 and UD2 have been added for Expense Analysis.
386 -- Hence two new columns have been introduced in FII_GL_CCID_DIMENSIONS.
387 ---------------------------------------------
388 l_stmt := 'INSERT INTO FII_GL_CCID_DIMENSIONS (
389 code_combination_id,
390 chart_of_accounts_id,
391 company_id,
392 cost_center_id,
393 natural_account_id,
394 company_cost_center_org_id,
395 creation_date,
396 created_by,
397 last_update_date,
398 last_updated_by,
399 last_update_login,
400 user_dim1_id,
401 user_dim2_id)
402 SELECT /*+ ordered use_nl(seg1,seg2,seg3)
403 use_hash(glcc) */
404 glcc.code_combination_id,
405 glcc.chart_of_accounts_id,
406 flx1.flex_value_id,
407 flx2.flex_value_id,
408 flx3.flex_value_id,
409 NVL(glcc.company_cost_center_org_id, -1),
410 sysdate,
411 ' ||g_fii_user_id || ',
412 sysdate,
413 ' || g_fii_user_id || ',
414 ' || g_fii_login_id ;
415
416 -- UD1/UD2 to be populated only if it is enabled.
417 -- In case UD1 or UD2 is disabled an unassigned id will be populated.
418
419 IF(G_UD1_ENABLED = 'N' OR p_ud1_seg is null) THEN
420 l_stmt := l_stmt || ',' || G_UNASSIGNED_ID || ',';
421 ELSE
422 l_stmt := l_stmt || ',flx4.flex_value_id,';
423 END IF;
424
425 IF(G_UD2_ENABLED = 'N' OR p_ud2_seg is null) THEN
426 l_stmt := l_stmt || G_UNASSIGNED_ID ;
427 ELSE
428 l_stmt := l_stmt || 'flx5.flex_value_id';
429 END IF;
430
431
432 l_stmt := l_stmt || ' FROM ( select coa_id, udd1_vset_id, udd2_vset_id
433 from FII_ACCT_SEG_GT
434 where company_seg_name = ''' || p_company_seg || '''
435 and costctr_seg_name = ''' || p_cc_seg || '''
436 and natural_seg_name = ''' || p_natural_seg || '''
437 and nvl(udd1_seg_name, 1) = nvl(''' || p_ud1_seg || ''',1)
438 and nvl(udd2_seg_name, 1) =nvl(''' || p_ud2_seg || ''',1)
439 ) accts,
440 FII_CCID_SLG_GT csg,
441 fnd_id_flex_segments seg1,
442 fnd_id_flex_segments seg2,
443 fnd_id_flex_segments seg3,
444 GL_CODE_COMBINATIONS glcc,
445 fnd_flex_values flx1,
446 fnd_flex_values flx2,
447 fnd_flex_values flx3 ';
448
449 IF(G_UD1_ENABLED = 'Y' AND p_ud1_seg is not null) THEN
450
451 l_stmt := l_stmt || ',fnd_flex_values flx4';
452
453 END IF;
454
455 IF(G_UD2_ENABLED = 'Y' AND p_ud2_seg is not null) THEN
456
457 l_stmt := l_stmt || ',fnd_flex_values flx5';
458
459 END IF;
460
461 l_stmt := l_stmt || ' WHERE csg.coa_id = accts.coa_id
462 AND glcc.chart_of_accounts_id = csg.coa_id
463 AND glcc.' || p_company_seg || ' = csg.BAL_SEG_VALUE
464 AND glcc.code_combination_id > ' || g_max_ccid || '
465 AND glcc.summary_flag = ''N''
466 AND glcc.template_ID IS NULL
467
468 AND seg1.application_id = 101
469 AND seg1.id_flex_code = ''GL#''
470 AND seg1.id_flex_num = csg.coa_id
471 AND seg1.APPLICATION_COLUMN_NAME = ''' || p_company_seg || '''
472 AND flx1.flex_value_set_id = seg1.flex_value_set_id
473 AND glcc.' || p_company_seg || ' = flx1.FLEX_VALUE
474
475 AND seg2.application_id = 101
476 AND seg2.id_flex_code = ''GL#''
477 AND seg2.id_flex_num = csg.coa_id
478 AND seg2.APPLICATION_COLUMN_NAME = ''' || p_cc_seg || '''
479 AND flx2.flex_value_set_id = seg2.flex_value_set_id
480 AND glcc.' || p_cc_seg || ' = flx2.FLEX_VALUE
481
482 AND seg3.application_id = 101
483 AND seg3.id_flex_code = ''GL#''
484 AND seg3.id_flex_num = csg.coa_id
485 AND seg3.APPLICATION_COLUMN_NAME = ''' ||p_natural_seg || '''
486 AND flx3.flex_value_set_id = seg3.flex_value_set_id
487 AND glcc.' || p_natural_seg || ' = flx3.FLEX_VALUE';
488
489 IF(G_UD1_ENABLED = 'Y' AND p_ud1_seg is not null) THEN
490
491 l_stmt := l_stmt || ' AND flx4.flex_value_set_id = accts.udd1_vset_id
492 AND glcc.' || p_ud1_seg || ' = flx4.FLEX_VALUE';
493 END IF;
494
495 IF(G_UD2_ENABLED = 'Y' AND p_ud2_seg is not null) THEN
496
497 l_stmt := l_stmt ||' AND flx5.flex_value_set_id = accts.udd2_vset_id
498 AND glcc.' || p_ud2_seg || ' = flx5.FLEX_VALUE';
499 END IF;
500
501
502 If g_debug_flag = 'Y' then
503 FII_UTIL.Write_Log(l_stmt);
504 FII_UTIL.start_timer;
505 End if;
506
507 execute immediate l_stmt;
508
509 If g_debug_flag = 'Y' then
510 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' records into FII_GL_CCID_DIMENSIONS');
511 FII_UTIL.stop_timer;
512 FII_UTIL.print_timer('Duration');
513 FII_UTIL.Write_Log('');
514 End if;
515
516 If g_debug_flag = 'Y' then
517 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.INSERT_INTO_CCID_DIM');
518 End if;
519
520 EXCEPTION
521
522 -------------------------------------------------------
523 -- Do not need handle the case when company cost org ID
524 -- is NULL. For CCID which company cost org ID is NULL
525 -- we will insert -1 into the company cost org ID field
526 -- in FII_GL_CCID_DIMENSIONS table
527 -------------------------------------------------------
528
529 WHEN OTHERS THEN
530 rollback;
531 g_retcode := -1;
532 FII_UTIL.Write_Log('
533 -----------------------------
534 Error occured in Procedure: INSERT_INTO_CCID_DIM
535 Message: ' || sqlerrm);
536 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.INSERT_INTO_CCID_DIM');
537 raise;
538 END INSERT_INTO_CCID_DIM;
539
540 -----------------------------------------------------------------------------
541 -- PROCEDURE INSERT_INTO_CCID_DIM_INIT
542 -----------------------------------------------------------------------------
543 PROCEDURE INSERT_INTO_CCID_DIM_INIT (p_company_seg IN VARCHAR2,
544 p_cc_seg IN VARCHAR2,
545 p_natural_seg IN VARCHAR2,
546 p_ud1_seg IN VARCHAR2,
547 p_ud2_seg IN VARCHAR2) IS
548
549 l_stmt VARCHAR2(10000);
550
551 BEGIN
552
553 If g_debug_flag = 'Y' then
554 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.INSERT_INTO_CCID_DIM_INIT');
555 End if;
556
557 IF g_debug_flag = 'Y' then
558 FII_UTIL.Write_Log('Inserting CCIDs in chart of accounts: ' ||
559 p_company_seg || ' - ' ||
560 p_cc_seg || ' - ' ||
561 p_natural_seg);
562 END IF;
563
564 ---------------------------------------------
565 -- Inserting records into GL CCID dimension
566 -- Two new dimensions UD1 and UD2 have been added for Expense Analysis.
567 -- Hence two new columns have been introduced in FII_GL_CCID_DIMENSIONS.
568 ---------------------------------------------
569 l_stmt := 'INSERT /*+ append parallel(fii) */ INTO
570 FII_GL_CCID_DIMENSIONS fii (
571 code_combination_id,
572 chart_of_accounts_id,
573 company_id,
574 cost_center_id,
575 natural_account_id,
576 company_cost_center_org_id,
577 creation_date,
578 created_by,
579 last_update_date,
580 last_updated_by,
581 last_update_login,
582 user_dim1_id, user_dim2_id)
583 SELECT /*+ ordered use_nl(seg1,seg2,seg3)
584 use_hash(glcc) parallel(glcc) */
585 glcc.code_combination_id,
586 glcc.chart_of_accounts_id,
587 flx1.flex_value_id,
588 flx2.flex_value_id,
589 flx3.flex_value_id,
590 NVL(glcc.company_cost_center_org_id, -1),
591 sysdate,
592 ' ||g_fii_user_id || ',
593 sysdate,
594 ' || g_fii_user_id || ',
595 ' || g_fii_login_id || ',';
596 --to_number (NULL)
597
598 IF(G_UD1_ENABLED = 'N' OR p_ud1_seg is null) THEN
599 l_stmt := l_stmt || G_UNASSIGNED_ID || ',' ;
600 ELSE
601 l_stmt := l_stmt || 'flx4.flex_value_id,';
602 END IF;
603
604 IF(G_UD2_ENABLED = 'N' OR p_ud2_seg is null) THEN
605 l_stmt := l_stmt || G_UNASSIGNED_ID ;
606 ELSE
607 l_stmt := l_stmt || 'flx5.flex_value_id';
608 END IF;
609
610 l_stmt := l_stmt || ' FROM ( select coa_id, udd1_vset_id, udd2_vset_id
611 from FII_ACCT_SEG_GT
612 where company_seg_name = ''' || p_company_seg || '''
613 and costctr_seg_name = ''' || p_cc_seg || '''
614 and natural_seg_name = ''' || p_natural_seg || '''
615 and nvl(udd1_seg_name, 1) = nvl('''|| p_ud1_seg ||''',1)
616 and nvl(udd2_seg_name, 1) =nvl('''|| p_ud2_seg ||''',1)
617 ) accts,
618 FII_CCID_SLG_GT csg,
619 fnd_id_flex_segments seg1,
620 fnd_id_flex_segments seg2,
621 fnd_id_flex_segments seg3,
622 GL_CODE_COMBINATIONS glcc,
623 fnd_flex_values flx1,
624 fnd_flex_values flx2,
625 fnd_flex_values flx3 ';
626
627 -- UD1/UD2 to be populated only if it is enabled.
628 -- In case UD1 or UD2 is disabled an unassigned id will be populated.
629
630 IF(G_UD1_ENABLED = 'Y' AND p_ud1_seg is not null) THEN
631
632 l_stmt := l_stmt || ',fnd_flex_values flx4';
633
634 END IF;
635
636 IF(G_UD2_ENABLED = 'Y' AND p_ud2_seg is not null) THEN
637
638 l_stmt := l_stmt || ',fnd_flex_values flx5';
639
640 END IF;
641 l_stmt := l_stmt || ' WHERE csg.coa_id = accts.coa_id
642 AND glcc.chart_of_accounts_id = csg.coa_id
643 AND glcc.' || p_company_seg || ' = csg.BAL_SEG_VALUE
644 AND glcc.summary_flag = ''N''
645 AND glcc.template_ID IS NULL
646
647 AND seg1.application_id = 101
648 AND seg1.id_flex_code = ''GL#''
649 AND seg1.id_flex_num = csg.coa_id
650 AND seg1.APPLICATION_COLUMN_NAME = ''' || p_company_seg || '''
651 AND flx1.flex_value_set_id = seg1.flex_value_set_id
652 AND glcc.' || p_company_seg || ' = flx1.FLEX_VALUE
653
654 AND seg2.application_id = 101
655 AND seg2.id_flex_code = ''GL#''
656 AND seg2.id_flex_num = csg.coa_id
657 AND seg2.APPLICATION_COLUMN_NAME = ''' || p_cc_seg || '''
658 AND flx2.flex_value_set_id = seg2.flex_value_set_id
659 AND glcc.' || p_cc_seg || ' = flx2.FLEX_VALUE
660
661 AND seg3.application_id = 101
662 AND seg3.id_flex_code = ''GL#''
663 AND seg3.id_flex_num = csg.coa_id
664 AND seg3.APPLICATION_COLUMN_NAME = ''' ||p_natural_seg || '''
665 AND flx3.flex_value_set_id = seg3.flex_value_set_id
666 AND glcc.' || p_natural_seg || ' = flx3.FLEX_VALUE';
667
668 IF(G_UD1_ENABLED = 'Y' AND p_ud1_seg is not null) THEN
669
670 l_stmt := l_stmt ||' AND flx4.flex_value_set_id = accts.udd1_vset_id
671 AND glcc.' || p_ud1_seg || ' = flx4.FLEX_VALUE';
672 END IF;
673
674 IF(G_UD2_ENABLED = 'Y' AND p_ud2_seg is not null) THEN
675
676 l_stmt := l_stmt ||' AND flx5.flex_value_set_id = accts.udd2_vset_id
677 AND glcc.' || p_ud2_seg || ' = flx5.FLEX_VALUE';
678 END IF;
679
680 If g_debug_flag = 'Y' then
681 FII_UTIL.Write_Log(l_stmt);
682 FII_UTIL.start_timer;
683 End if;
684
685 EXECUTE IMMEDIATE l_stmt;
686
687 If g_debug_flag = 'Y' then
688 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' records into FII_GL_CCID_DIMENSIONS');
689 FII_UTIL.stop_timer;
690 FII_UTIL.print_timer('Duration');
691 FII_UTIL.Write_Log('');
692 End if;
693
694 --need this to avoid ORA-12838: cannot read/modify an object after modifying it in parallel
695 COMMIT;
696
697 If g_debug_flag = 'Y' then
698 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.INSERT_INTO_CCID_DIM_INIT');
699 End if;
700
701 EXCEPTION
702
703 WHEN OTHERS THEN
704 FII_UTIL.TRUNCATE_TABLE('FII_GL_CCID_DIMENSIONS', g_fii_schema, g_retcode);
705 g_retcode := -1;
706 FII_UTIL.Write_Log('
707 -----------------------------
708 Error occured in Procedure: INSERT_INTO_CCID_DIM_INIT
709 Message: ' || sqlerrm);
710 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.INSERT_INTO_CCID_DIM_INIT');
711 raise;
712 END INSERT_INTO_CCID_DIM_INIT;
713
714 ------------------------------------------------------------------
715 -- PROCEDURE RECORD_MAX_PROCESSED_CCID
716 ------------------------------------------------------------------
717 PROCEDURE RECORD_MAX_PROCESSED_CCID IS
718
719 l_tmp_max_ccid NUMBER;
720
721 BEGIN
722
723 If g_debug_flag = 'Y' then
724 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.RECORD_MAX_PROCESSED_CCID');
725 End if;
726
727 g_phase := 'Updating max CCID processed';
728 If g_debug_flag = 'Y' then
729 FII_UTIL.Write_Log('');
730 FII_UTIL.Write_Log(g_phase);
731 FII_UTIL.start_timer;
732 End if;
733
734 --------------------------------------------------------------
735 -- Get the real max ccid that was inserted into CCID dimension
736 -- the g_new_max_ccid recorded at the beginning of the program
737 -- may not necessary be the largest CCID that was inserted.
738 -- New ccids could have been created while the program is
739 -- running. So record this max ccid from fii_gl_ccid_dimensions
740 --
741 -- Note that origianl g_new_max_ccid is from GL_CODE_COMBINATIONS,
742 --------------------------------------------------------------
743
744 g_phase := 'SELECT FROM fii_gl_ccid_dimensions';
745
746 SELECT MAX(code_combination_id) INTO l_tmp_max_ccid
747 FROM fii_gl_ccid_dimensions;
748
749 -- we should pick the larger one for g_new_max_ccid
750 -- between l_tmp_max_ccid and the original g_new_max_ccid
751 if g_new_max_ccid < l_tmp_max_ccid then
752 g_new_max_ccid := l_tmp_max_ccid;
753 end if;
754
755 g_phase := 'UPDATE fii_change_log';
756
757 -- we also update PROD_CAT_SET_ID here
758 UPDATE fii_change_log
759 SET item_value = decode (log_item, 'MAX_CCID', to_char(g_new_max_ccid),
760 'PROD_CAT_SET_ID', g_prod_cat_set_id),
761 last_update_date = SYSDATE,
762 last_update_login = g_fii_login_id,
763 last_updated_by = g_fii_user_id
764 WHERE log_item = 'MAX_CCID'
765 OR (log_item = 'PROD_CAT_SET_ID' and g_prod_cat_set_id is not null);
766
767 If g_debug_flag = 'Y' then
768 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_change_log');
769 End if;
770
771 If g_debug_flag = 'Y' then
772 FII_UTIL.stop_timer;
773 FII_UTIL.print_timer('Duration');
774 FII_UTIL.Write_Log('');
775 End if;
776
777 If g_debug_flag = 'Y' then
778 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.RECORD_MAX_PROCESSED_CCID');
779 End if;
780
781 EXCEPTION
782 WHEN OTHERS THEN
783 rollback;
784 g_retcode := -1;
785 FII_UTIL.Write_Log('
786 -------------------------------------------
787 Error occured in Procedure: RECORD_MAX_PROCESSED_CCID
788 Phase: ' || g_phase || '
789 Message: ' || sqlerrm);
790 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.RECORD_MAX_PROCESSED_CCID');
791 raise;
792
793 END RECORD_MAX_PROCESSED_CCID;
794
795 ------------------------------------------------------------------
796 -- FUNCTION NEW_CCID_IN_GL
797 ------------------------------------------------------------------
798 FUNCTION NEW_CCID_IN_GL RETURN BOOLEAN IS
799 BEGIN
800
801 If g_debug_flag = 'Y' then
802 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.NEW_CCID_IN_GL');
803 End if;
804
805 g_phase := 'Identifying Max CCID processed';
806 If g_debug_flag = 'Y' then
807 FII_UTIL.Write_Log(g_phase);
808 FII_UTIL.Write_Log('');
809 End if;
810
811 -- Bug 4152799.
812 g_log_item := 'MAX_CCID';
813
814 SELECT item_value INTO g_max_ccid
815 FROM fii_change_log
816 WHERE log_item = g_log_item;
817
818 g_phase := 'Identifying current Max CCID in GL';
819 If g_debug_flag = 'Y' then
820 FII_UTIL.Write_Log(g_phase);
821 FII_UTIL.Write_Log('');
822 End if;
823
824 SELECT max(code_combination_id) INTO g_new_max_ccid
825 FROM gl_code_combinations;
826
827 If g_debug_flag = 'Y' then
828 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.NEW_CCID_IN_GL');
829 End if;
830
831 IF g_new_max_ccid > g_max_ccid THEN
832 RETURN TRUE;
833 ELSE
834 RETURN FALSE;
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 Function: NEW_CCID_IN_GL
844 Phase: ' || g_phase || '
845 Message: ' || sqlerrm);
846 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.NEW_CCID_IN_GL');
847 raise;
848 END NEW_CCID_IN_GL;
849
850 ------------------------------------------------------------------
851 -- PROCEDURE PROCESS_NULL_CCC_ORG_ID
852 ------------------------------------------------------------------
853 PROCEDURE PROCESS_NULL_CCC_ORG_ID IS
854
855 BEGIN
856
857 If g_debug_flag = 'Y' then
858 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.PROCESS_NULL_CCC_ORG_ID');
859 End if;
860
861 --------------------------------------------------------------
862 -- Updating CCID Dimension for CCIDs with NULL CCC ORG ID
863 --------------------------------------------------------------
864 g_phase := 'Updating CCID Dimension for CCIDs with NULL CCC ORG ID';
865 If g_debug_flag = 'Y' then
866 FII_UTIL.Write_Log(g_phase);
867 FII_UTIL.start_timer;
868 End if;
869
870 UPDATE fii_gl_ccid_dimensions dim
871 SET dim.company_cost_center_org_id =
872 (SELECT NVL(gcc.company_cost_center_org_id, -1)
873 FROM gl_code_combinations gcc
874 WHERE gcc.code_combination_id = dim.code_combination_id)
875 WHERE dim.company_cost_center_org_id = -1;
876
877 IF g_debug_flag = 'Y' then
878 FII_UTIL.Write_Log('Assigned CCC ORG ID to ' || SQL%ROWCOUNT
879 || ' CCIDs in FII_GL_CCID_DIMENSIONS');
880 FII_UTIL.stop_timer;
881 FII_UTIL.print_timer('Duration');
882 END IF;
883
884 If g_debug_flag = 'Y' then
885 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.PROCESS_NULL_CCC_ORG_ID');
886 End if;
887
888 EXCEPTION
889
890 WHEN OTHERS THEN
891 rollback;
892 g_retcode := -1;
893 FII_UTIL.Write_Log('
894 -----------------------------
895 Error occured in Procedure: PROCESS_NULL_CCC_ORG_ID
896 Phase: ' || g_phase || '
897 Message: ' || sqlerrm);
898 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.PROCESS_NULL_CCC_ORG_ID');
899 RAISE;
900
901 END PROCESS_NULL_CCC_ORG_ID;
902
903
904 ------------------------------------------------------------------
905 -- PROCEDURE INSERT_NEW_CCID
906 ------------------------------------------------------------------
907 PROCEDURE INSERT_NEW_CCID IS
908
909 CURSOR sss_list IS
910 SELECT DISTINCT company_seg_name, costctr_seg_name,
911 natural_seg_name, udd1_seg_name, udd2_seg_name
912 FROM FII_ACCT_SEG_GT;
913
914 BEGIN
915
916 If g_debug_flag = 'Y' then
917 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.INSERT_NEW_CCID');
918 End if;
919
920 g_phase := 'Identifying Max CCID processed';
921 If g_debug_flag = 'Y' then
922 FII_UTIL.Write_Log(g_phase);
923 FII_UTIL.Write_Log('');
924 End if;
925
926 -- Bug 4152799.
927 g_log_item := 'MAX_CCID';
928
929 SELECT item_value INTO g_max_ccid
930 FROM fii_change_log
931 WHERE log_item = g_log_item;
932
933 g_phase := 'Identifying current Max CCID in GL';
934 If g_debug_flag = 'Y' then
935 FII_UTIL.Write_Log(g_phase);
936 FII_UTIL.Write_Log('');
937 End if;
938
939 ------------------------------------------------------
940 -- g_mode = 'L' if program is run in Initial Load mode
941 ------------------------------------------------------
942 IF (g_mode = 'L') then
943
944 --Clean up the CCID dimension table
945
946 g_phase := 'TRUNCATE FII_GL_CCID_DIMENSIONS';
947
948 FII_UTIL.TRUNCATE_TABLE('FII_GL_CCID_DIMENSIONS',g_fii_schema,g_retcode);
949
950 --------------------------------------------------------------------------
951 --Bug 3205051: we should not force re-summarization for CCID initial load
952 -- This is wrong: Update FII_DIM_MAPPING_RULES to force
953 -- using product assignments
954 --*UPDATE fii_dim_mapping_rules
955 --* SET status_code = 'O',
956 --* last_update_date = sysdate,
957 --* last_update_login = g_fii_login_id,
958 --* last_updated_by = g_fii_user_id
959 --* WHERE dimension_short_name = 'ENI_ITEM_VBH_CAT';
960 --------------------------------------------------------------------------
961
962 --Update FII_CHANGE_LOG to reset MAX_CCID
963
964 g_phase := 'UPDATE fii_change_log';
965
966 -- Bug 4152799.
967
968 UPDATE fii_change_log
969 SET item_value = '0',
970 last_update_date = sysdate,
971 last_update_login = g_fii_login_id,
972 last_updated_by = g_fii_user_id
973 WHERE log_item = g_log_item;
974
975 If g_debug_flag = 'Y' then
976 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_change_log');
977 End if;
978
979 g_max_ccid := 0;
980
981 END IF;
982
983 g_phase := 'SELECT FROM gl_code_combinations';
984
985 SELECT max(code_combination_id) INTO g_new_max_ccid
986 FROM gl_code_combinations;
987
988 IF (g_new_max_ccid > g_max_ccid) THEN
989
990 g_phase := 'Insert new CCIDs into FII_GL_CCID_DIMENSIONS table';
991 If g_debug_flag = 'Y' then
992 FII_UTIL.Write_Log(g_phase);
993 FII_UTIL.Write_Log('');
994 End if;
995
996 ------------------------------------------------------------------
997 -- Using this SQL to get company segment, cost center segment
998 -- and natural account segment for each chart of account.
999 -- These information are needed to build the dynamic SQL
1000 -- in the INSERT_INTO_CCID API.
1001 -- For supporting UD1/UD2 dimensions as well segment names for
1002 -- these two dimensions are also populated. Also to avoid join
1003 -- with FII_DIM_MAPPING_RULES the value set id is also populated.
1004 ------------------------------------------------------------------
1005
1006 FII_UTIL.TRUNCATE_TABLE('FII_ACCT_SEG_GT', g_fii_schema, g_retcode);
1007
1008 g_phase := 'INSERT INTO FII_ACCT_SEG_GT';
1009
1010 INSERT INTO FII_ACCT_SEG_GT(
1011 coa_id, company_seg_name, costctr_seg_name, natural_seg_name
1012 )
1013 SELECT coa_list.chart_of_accounts_id,
1014 fsav1.application_column_name,
1015 fsav2.application_column_name,
1016 fsav3.application_column_name
1017 FROM ( SELECT DISTINCT sts.chart_of_accounts_id
1018 FROM fii_slg_assignments sts,
1019 fii_source_ledger_groups slg
1020 WHERE slg.usage_code = 'DBI'
1021 AND slg.source_ledger_group_id = sts.source_ledger_group_id
1022 ) coa_list,
1023 FND_SEGMENT_ATTRIBUTE_VALUES fsav1,
1024 FND_SEGMENT_ATTRIBUTE_VALUES fsav2,
1025 FND_SEGMENT_ATTRIBUTE_VALUES fsav3
1026 WHERE fsav1.application_id = 101
1027 AND fsav1.id_flex_code = 'GL#'
1028 AND fsav1.id_flex_num = coa_list.chart_of_accounts_id
1029 AND fsav1.segment_attribute_type = 'GL_BALANCING'
1030 AND fsav1.attribute_value = 'Y'
1031 AND fsav2.application_id = 101
1032 AND fsav2.id_flex_code = 'GL#'
1033 AND fsav2.id_flex_num = coa_list.chart_of_accounts_id
1034 AND fsav2.segment_attribute_type = 'FA_COST_CTR'
1035 AND fsav2.attribute_value = 'Y'
1036 AND fsav3.application_id = 101
1037 AND fsav3.id_flex_code = 'GL#'
1038 AND fsav3.id_flex_num = coa_list.chart_of_accounts_id
1039 AND fsav3.segment_attribute_type = 'GL_ACCOUNT'
1040 AND fsav3.attribute_value = 'Y';
1041
1042 If g_debug_flag = 'Y' then
1043 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_ACCT_SEG_GT');
1044 End if;
1045
1046 --------------------------------------------------------------------------------
1047 -- For supporting UD1/UD2 dimensions as well segment names for
1048 -- these two dimensions are also populated. Also to avoid join
1049 -- with FII_DIM_MAPPING_RULES in INSERT_INTO_CCID_DIM_INIT/INSERT_INTO_CCID_DIM
1050 -- the value set id is also populated.
1051 --------------------------------------------------------------------------------
1052
1053 IF(G_UD1_ENABLED = 'Y' ) THEN
1054 g_dimension_name := 'FII_USER_DEFINED_1';
1055 UPDATE FII_ACCT_SEG_GT tab1
1056 SET (udd1_seg_name, udd1_vset_id) = (select application_column_name1, flex_value_set_id1
1057 from fii_dim_mapping_rules
1058 where chart_of_accounts_id = tab1.coa_id
1059 and dimension_short_name = g_dimension_name);
1060 END IF;
1061
1062 If g_debug_flag = 'Y' then
1063 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_ACCT_SEG_GT');
1064 End if;
1065
1066 IF(G_UD2_ENABLED = 'Y' ) THEN
1067 g_dimension_name := 'FII_USER_DEFINED_2';
1068 UPDATE FII_ACCT_SEG_GT tab1
1069 SET (udd2_seg_name, udd2_vset_id) = (select application_column_name1, flex_value_set_id1
1070 from fii_dim_mapping_rules
1071 where chart_of_accounts_id = tab1.coa_id
1072 and dimension_short_name = g_dimension_name);
1073 END IF;
1074
1075 If g_debug_flag = 'Y' then
1076 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_ACCT_SEG_GT');
1077 End if;
1078
1079 ----------------------------------------------------
1080 -- Looping through each group of COA_IDs in the
1081 -- FII_ACCT_SEG_GT table to process the CCIDs
1082 ----------------------------------------------------
1083
1084 FOR sss IN sss_list LOOP
1085
1086 IF (g_mode = 'L') then
1087
1088 g_phase := 'Call INSERT_INTO_CCID_DIM_INIT';
1089
1090 INSERT_INTO_CCID_DIM_INIT(
1091 sss.company_seg_name,
1092 sss.costctr_seg_name,
1093 sss.natural_seg_name,
1094 sss.udd1_seg_name,
1095 sss.udd2_seg_name);
1096 ELSE
1097 g_phase := 'Call INSERT_INTO_CCID_DIM';
1098
1099 INSERT_INTO_CCID_DIM(
1100 sss.company_seg_name,
1101 sss.costctr_seg_name,
1102 sss.natural_seg_name,
1103 sss.udd1_seg_name,
1104 sss.udd2_seg_name);
1105 END IF;
1106
1107 END LOOP;
1108
1109 ------------------------------------------------------
1110 -- Record the max CCID processed
1111 ------------------------------------------------------
1112 g_phase := 'Call RECORD_MAX_PROCESSED_CCID';
1113
1114 RECORD_MAX_PROCESSED_CCID;
1115
1116 ELSE
1117 If g_debug_flag = 'Y' then
1118 FII_UTIL.Write_Log('No new CCID in GL');
1119 End if;
1120 END IF;
1121
1122 ------------------------------------------------------
1123 -- Process CCIDs with NULL Company Cost Center Org ID
1124 -- Including old CCIDs which are already in CCID Dim.
1125 ------------------------------------------------------
1126 -- Bug 4073775. Removed the call to PROCESS_NULL_CCC_ORG_ID.
1127 --IF (g_mode <> 'L') THEN
1128
1129 -- g_phase := 'Call PROCESS_NULL_CCC_ORG_ID';
1130
1131 -- PROCESS_NULL_CCC_ORG_ID;
1132 --END IF;
1133
1134 --------------------------------------------------------
1135 -- Gather statistics for the use of cost-based optimizer
1136 --------------------------------------------------------
1137 --Will seed this in RSG
1138 -- FND_STATS.gather_table_stats
1139 -- (ownname => g_fii_schema,
1140 -- tabname => 'FII_GL_CCID_DIMENSIONS');
1141
1142 If g_debug_flag = 'Y' then
1143 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.INSERT_NEW_CCID');
1144 End if;
1145
1146 EXCEPTION
1147
1148 WHEN OTHERS THEN
1149
1150 if g_mode = 'L' then
1151
1152 --program is run in Initial Load mode, truncate the table and reset LOG
1153
1154 FII_UTIL.TRUNCATE_TABLE('FII_GL_CCID_DIMENSIONS',g_fii_schema,g_retcode);
1155
1156 -- Bug 4152799.
1157 g_log_item := 'MAX_CCID';
1158
1159 UPDATE fii_change_log
1160 SET item_value = '0',
1161 last_update_date = sysdate,
1162 last_update_login = g_fii_login_id,
1163 last_updated_by = g_fii_user_id
1164 WHERE log_item = g_log_item;
1165
1166 g_max_ccid := 0;
1167
1168 end if;
1169
1170 rollback;
1171 g_retcode := -1;
1172 FII_UTIL.Write_Log('
1173 -----------------------------
1174 Error occured in Procedure: INSERT_NEW_CCID
1175 Phase: ' || g_phase || '
1176 Message: ' || sqlerrm);
1177 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.INSERT_NEW_CCID');
1178 raise;
1179
1180 END INSERT_NEW_CCID;
1181
1182 --------------------------------------------------------------
1183 -- PROCEDURE USE_SEG
1184 --------------------------------------------------------------
1185 PROCEDURE USE_SEG (p_coa_id IN NUMBER, p_product_seg IN VARCHAR2) IS
1186
1187 l_stmt VARCHAR2(5000);
1188
1189 BEGIN
1190 If g_debug_flag = 'Y' then
1191 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.USE_SEG');
1192 End if;
1193
1194 g_phase := 'Updating Product assignment using Segment';
1195 If g_debug_flag = 'Y' then
1196 FII_UTIL.Write_Log(g_phase);
1197 End if;
1198 -----------------------------------------------------
1199 -- Product segment of the CCID records the product
1200 -- reporting classification
1201 -----------------------------------------------------
1202 l_stmt := 'UPDATE fii_gl_ccid_dimensions glcc
1203 SET (glcc.product_id, glcc.PROD_CATEGORY_ID) =
1204 (SELECT flx1.flex_value_id, mtc.category_id
1205 FROM gl_code_combinations glccd,
1206 mtl_categories mtc,
1207 fnd_id_flex_segments seg1,
1208 fnd_flex_values flx1
1209 WHERE glccd.code_combination_id = glcc.code_combination_id
1210 AND mtc.structure_id = ' || g_mtc_structure_id || '
1211 AND mtc.' || g_mtc_column_name || ' = glccd.' || p_product_seg || '
1212 AND seg1.application_id = 101
1213 AND seg1.id_flex_code = ''GL#''
1214 AND seg1.id_flex_num = ' || p_coa_id || '
1215 AND seg1.application_column_name = ''' || p_product_seg || '''
1216 AND glccd.' || p_product_seg || ' = flx1.flex_value
1217 AND flx1.flex_value_set_id = seg1.flex_value_set_id)
1218 WHERE glcc.chart_of_accounts_id = ' || p_coa_id;
1219
1220 If g_debug_flag = 'Y' then
1221 FII_UTIL.Write_Log('');
1222 FII_UTIL.Write_Log(l_stmt);
1223 FII_UTIL.start_timer;
1224 End if;
1225
1226 EXECUTE IMMEDIATE l_stmt;
1227
1228 If g_debug_flag = 'Y' then
1229 FII_UTIL.Write_Log('Updated Product Assignments for ' || SQL%ROWCOUNT
1230 || ' records in FII_GL_CCID_DIMENSIONS');
1231 FII_UTIL.stop_timer;
1232 FII_UTIL.print_timer('Duration');
1233 End if;
1234
1235 If g_debug_flag = 'Y' then
1236 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.USE_SEG');
1237 End if;
1238
1239 EXCEPTION
1240
1241 WHEN OTHERS THEN
1242 rollback;
1243 g_retcode := -1;
1244 FII_UTIL.Write_Log('
1245 -------------------------------------------
1246 Error occured in Procedure: USE_SEG
1247 Phase: ' || g_phase || ' Message: ' || sqlerrm);
1248 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.USE_SEG');
1249 raise;
1250
1251 END USE_SEG;
1252
1253 -----------------------------------------------------
1254 -- PROCEDURE USE_RANGES
1255 -----------------------------------------------------
1256 PROCEDURE USE_RANGES(p_coa_id IN NUMBER, p_product_seg IN VARCHAR2) IS
1257
1258 l_duplicate_asgn NUMBER := 0;
1259 l_stmt VARCHAR2(8000);
1260 l_coa_name VARCHAR2(30);
1261 l_ccid NUMBER;
1262 l_cat_name VARCHAR2(240);
1263
1264 CURSOR c_dup_prod_asgn IS
1265 SELECT code_combination_id, count(*) cnt
1266 FROM fii_gl_ccid_prod_int
1267 GROUP BY code_combination_id
1268 HAVING count(*) > 1;
1269
1270 CURSOR c_dup_prod_cat (p_ccid NUMBER) IS
1271 SELECT cat.description cat_name
1272 FROM fii_gl_ccid_prod_int int,
1273 mtl_categories cat
1274 WHERE int.code_combination_id = p_ccid
1275 AND int.prod_category_id = cat.category_id;
1276
1277 BEGIN
1278
1279 If g_debug_flag = 'Y' then
1280 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.USE_RANGES');
1281 End if;
1282
1283 g_phase := 'Populating FII_GL_CCID_PROD_INT';
1284 If g_debug_flag = 'Y' then
1285 FII_UTIL.Write_Log(g_phase);
1286 End if;
1287
1288 ---------------------------------------------------
1289 -- Product mapping information is actually stored
1290 -- in FII_PRODUCT_ASSIGNMENTS table.
1291 -- We will first store the CCID, product mapping
1292 -- info in FII_GL_CCID_PROD_INT
1293 ---------------------------------------------------
1294 l_stmt := 'INSERT INTO FII_GL_CCID_PROD_INT (
1295 code_combination_id,
1296 prod_category_id,
1297 last_update_date,
1298 last_updated_by,
1299 creation_date,
1300 created_by,
1301 last_update_login)
1302 SELECT
1303 glcc.code_combination_id,
1304 fipa.prod_category_id,
1305 sysdate,
1306 ' ||g_fii_user_id || ',
1307 sysdate,
1308 ' || g_fii_user_id || ',
1309 ' || g_fii_login_id || '
1310 FROM gl_code_combinations glcc,
1311 fii_product_assignments fipa
1312 WHERE glcc.chart_of_accounts_id = :p_coa_id
1313 AND fipa.chart_of_accounts_id = glcc.chart_of_accounts_id
1314 AND fipa.PROD_CATEGORY_SET_ID = :G_PROD_CAT_SET_ID
1315 AND NVL(glcc.segment1,1) >= NVL(fipa.segment1_low, NVL(glcc.segment1,1))
1316 AND NVL(glcc.segment1,1) <= NVL(fipa.segment1_high, NVL(glcc.segment1,1))
1317 AND NVL(glcc.segment2,1) >= NVL(fipa.segment2_low, NVL(glcc.segment2,1))
1318 AND NVL(glcc.segment2,1) <= NVL(fipa.segment2_high, NVL(glcc.segment2,1))
1319 AND NVL(glcc.segment3,1) >= NVL(fipa.segment3_low, NVL(glcc.segment3,1))
1320 AND NVL(glcc.segment3,1) <= NVL(fipa.segment3_high, NVL(glcc.segment3,1))
1321 AND NVL(glcc.segment4,1) >= NVL(fipa.segment4_low, NVL(glcc.segment4,1))
1322 AND NVL(glcc.segment4,1) <= NVL(fipa.segment4_high, NVL(glcc.segment4,1))
1323 AND NVL(glcc.segment5,1) >= NVL(fipa.segment5_low, NVL(glcc.segment5,1))
1324 AND NVL(glcc.segment5,1) <= NVL(fipa.segment5_high, NVL(glcc.segment5,1))
1325 AND NVL(glcc.segment6,1) >= NVL(fipa.segment6_low, NVL(glcc.segment6,1))
1326 AND NVL(glcc.segment6,1) <= NVL(fipa.segment6_high, NVL(glcc.segment6,1))
1327 AND NVL(glcc.segment7,1) >= NVL(fipa.segment7_low, NVL(glcc.segment7,1))
1328 AND NVL(glcc.segment7,1) <= NVL(fipa.segment7_high, NVL(glcc.segment7,1))
1329 AND NVL(glcc.segment8,1) >= NVL(fipa.segment8_low, NVL(glcc.segment8,1))
1330 AND NVL(glcc.segment8,1) <= NVL(fipa.segment8_high, NVL(glcc.segment8,1))
1331 AND NVL(glcc.segment9,1) >= NVL(fipa.segment9_low, NVL(glcc.segment9,1))
1332 AND NVL(glcc.segment9,1) <= NVL(fipa.segment9_high, NVL(glcc.segment9,1))
1333 AND NVL(glcc.segment10,1) >= NVL(fipa.segment10_low, NVL(glcc.segment10,1))
1334 AND NVL(glcc.segment10,1) <= NVL(fipa.segment10_high, NVL(glcc.segment10,1))
1335 AND NVL(glcc.segment11,1) >= NVL(fipa.segment11_low, NVL(glcc.segment11,1))
1336 AND NVL(glcc.segment11,1) <= NVL(fipa.segment11_high, NVL(glcc.segment11,1))
1337 AND NVL(glcc.segment12,1) >= NVL(fipa.segment12_low, NVL(glcc.segment12,1))
1338 AND NVL(glcc.segment12,1) <= NVL(fipa.segment12_high, NVL(glcc.segment12,1))
1339 AND NVL(glcc.segment13,1) >= NVL(fipa.segment13_low, NVL(glcc.segment13,1))
1340 AND NVL(glcc.segment13,1) <= NVL(fipa.segment13_high, NVL(glcc.segment13,1))
1341 AND NVL(glcc.segment14,1) >= NVL(fipa.segment14_low, NVL(glcc.segment14,1))
1342 AND NVL(glcc.segment14,1) <= NVL(fipa.segment14_high, NVL(glcc.segment14,1))
1343 AND NVL(glcc.segment15,1) >= NVL(fipa.segment15_low, NVL(glcc.segment15,1))
1344 AND NVL(glcc.segment15,1) <= NVL(fipa.segment15_high, NVL(glcc.segment15,1))
1345 AND NVL(glcc.segment16,1) >= NVL(fipa.segment16_low, NVL(glcc.segment16,1))
1346 AND NVL(glcc.segment16,1) <= NVL(fipa.segment16_high, NVL(glcc.segment16,1))
1347 AND NVL(glcc.segment17,1) >= NVL(fipa.segment17_low, NVL(glcc.segment17,1))
1348 AND NVL(glcc.segment17,1) <= NVL(fipa.segment17_high, NVL(glcc.segment17,1))
1349 AND NVL(glcc.segment18,1) >= NVL(fipa.segment18_low, NVL(glcc.segment18,1))
1350 AND NVL(glcc.segment18,1) <= NVL(fipa.segment18_high, NVL(glcc.segment18,1))
1351 AND NVL(glcc.segment19,1) >= NVL(fipa.segment19_low, NVL(glcc.segment19,1))
1352 AND NVL(glcc.segment19,1) <= NVL(fipa.segment19_high, NVL(glcc.segment19,1))
1353 AND NVL(glcc.segment20,1) >= NVL(fipa.segment20_low, NVL(glcc.segment20,1))
1354 AND NVL(glcc.segment20,1) <= NVL(fipa.segment20_high, NVL(glcc.segment20,1))
1355 AND NVL(glcc.segment21,1) >= NVL(fipa.segment21_low, NVL(glcc.segment21,1))
1356 AND NVL(glcc.segment21,1) <= NVL(fipa.segment21_high, NVL(glcc.segment21,1))
1357 AND NVL(glcc.segment22,1) >= NVL(fipa.segment22_low, NVL(glcc.segment22,1))
1358 AND NVL(glcc.segment22,1) <= NVL(fipa.segment22_high, NVL(glcc.segment22,1))
1359 AND NVL(glcc.segment23,1) >= NVL(fipa.segment23_low, NVL(glcc.segment23,1))
1360 AND NVL(glcc.segment23,1) <= NVL(fipa.segment23_high, NVL(glcc.segment23,1))
1361 AND NVL(glcc.segment24,1) >= NVL(fipa.segment24_low, NVL(glcc.segment24,1))
1362 AND NVL(glcc.segment24,1) <= NVL(fipa.segment24_high, NVL(glcc.segment24,1))
1363 AND NVL(glcc.segment25,1) >= NVL(fipa.segment25_low, NVL(glcc.segment25,1))
1364 AND NVL(glcc.segment25,1) <= NVL(fipa.segment25_high, NVL(glcc.segment25,1))
1365 AND NVL(glcc.segment26,1) >= NVL(fipa.segment26_low, NVL(glcc.segment26,1))
1366 AND NVL(glcc.segment26,1) <= NVL(fipa.segment26_high, NVL(glcc.segment26,1))
1367 AND NVL(glcc.segment27,1) >= NVL(fipa.segment27_low, NVL(glcc.segment27,1))
1368 AND NVL(glcc.segment27,1) <= NVL(fipa.segment27_high, NVL(glcc.segment27,1))
1369 AND NVL(glcc.segment28,1) >= NVL(fipa.segment28_low, NVL(glcc.segment28,1))
1370 AND NVL(glcc.segment28,1) <= NVL(fipa.segment28_high, NVL(glcc.segment28,1))
1371 AND NVL(glcc.segment29,1) >= NVL(fipa.segment29_low, NVL(glcc.segment29,1))
1372 AND NVL(glcc.segment29,1) <= NVL(fipa.segment29_high, NVL(glcc.segment29,1))
1373 AND NVL(glcc.segment30,1) >= NVL(fipa.segment30_low, NVL(glcc.segment30,1))
1374 AND NVL(glcc.segment30,1) <= NVL(fipa.segment30_high, NVL(glcc.segment30,1))';
1375
1376
1377 If g_debug_flag = 'Y' then
1378 FII_UTIL.Write_Log(l_stmt);
1379 FII_UTIL.start_timer;
1380 End if;
1381
1382 EXECUTE IMMEDIATE l_stmt using p_coa_id, G_PROD_CAT_SET_ID;
1383
1384 If g_debug_flag = 'Y' then
1385 FII_UTIL.Write_Log('Inserted Product Assignments for ' || SQL%ROWCOUNT
1386 || ' records in FII_GL_CCID_PROD_INT');
1387 FII_UTIL.stop_timer;
1388 FII_UTIL.print_timer('Duration');
1389 End if;
1390
1391 ------------------------------------------------------------------
1392 -- Checking if single CCID assigned to multiple product categories
1393 ------------------------------------------------------------------
1394 g_phase := 'Checking if single CCID assigned to multiple product categories';
1395 If g_debug_flag = 'Y' then
1396 FII_UTIL.Write_Log(g_phase);
1397 End if;
1398
1399
1400 For rec_dup_prod_asgn IN c_dup_prod_asgn Loop
1401
1402 l_ccid := rec_dup_prod_asgn.code_combination_id;
1403 l_duplicate_asgn := l_duplicate_asgn + 1;
1404
1405 IF (l_duplicate_asgn = 1) THEN
1406
1407 l_coa_name := GET_COA_NAME (p_coa_id);
1408
1409 FII_MESSAGE.write_log(
1410 msg_name => 'FII_DUPLICATE_PROD_ASGN',
1411 token_num => 1,
1412 t1 => 'COA_NAME',
1413 v1 => l_coa_name);
1414
1415 FII_MESSAGE.write_log(
1416 msg_name => 'FII_REFER_TO_OUTPUT',
1417 token_num => 0);
1418
1419 ----------------------------------------------------
1420 -- Print out translated message to let user know
1421 -- there are CCIDs with multiple product assignments
1422 ----------------------------------------------------
1423 FII_MESSAGE.write_output(
1424 msg_name => 'FII_DUPLICATE_PROD_ASGN',
1425 token_num => 1,
1426 t1 => 'COA_NAME',
1427 v1 => l_coa_name);
1428
1429 FII_MESSAGE.write_output(
1430 msg_name => 'FII_DUP_PROD_ASGN_RPT_HDR',
1431 token_num => 0);
1432
1433 END IF;
1434
1435 -------------------------------------------------------------
1436 --Print out the list of ccid with multiple product categories
1437 -------------------------------------------------------------
1438 For rec_dup_prod_cat IN c_dup_prod_cat (l_ccid) Loop
1439 l_cat_name := rec_dup_prod_cat.cat_name;
1440 FII_UTIL.Write_Output (l_ccid || ' ' || l_cat_name);
1441 End Loop;
1442
1443 End Loop;
1444
1445 If l_duplicate_asgn > 0 Then
1446 RAISE G_DUPLICATE_PROD_ASGN;
1447 End If;
1448
1449
1450 -------------------------------------------------------------
1451 -- Updating FII_GL_CCID_DIMENSIONS with product assignments information
1452 -------------------------------------------------------------
1453 g_phase := 'Updating FII_GL_CCID_DIMENSIONS to fix product assignments';
1454 If g_debug_flag = 'Y' then
1455 FII_UTIL.Write_Log(g_phase);
1456 FII_UTIL.start_timer;
1457 End if;
1458
1459 UPDATE fii_gl_ccid_dimensions glcc
1460 SET glcc.PROD_CATEGORY_ID =
1461 (SELECT NVL(int.prod_category_id, glcc.prod_category_id)
1462 FROM fii_gl_ccid_prod_int int
1463 WHERE int.code_combination_id = glcc.code_combination_id
1464 AND glcc.chart_of_accounts_id = p_coa_id)
1465 WHERE glcc.chart_of_accounts_id = p_coa_id;
1466
1467 If g_debug_flag = 'Y' then
1468 FII_UTIL.Write_Log('Updated Product Assignments for ' || SQL%ROWCOUNT
1469 || ' records in FII_GL_CCID_DIMENSIONS');
1470 FII_UTIL.stop_timer;
1471 FII_UTIL.print_timer('Duration');
1472 End if;
1473
1474 If g_debug_flag = 'Y' then
1475 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.USE_RANGES');
1476 End if;
1477
1478 EXCEPTION
1479
1480 WHEN G_DUPLICATE_PROD_ASGN THEN
1481 rollback;
1482 g_retcode := -1;
1483 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.USE_RANGES');
1484 raise;
1485
1486 WHEN OTHERS THEN
1487 rollback;
1488 g_retcode := -1;
1489 FII_UTIL.Write_Log('
1490 --------------------------------------
1491 Error occured in Procedure: USE_RANGES
1492 Phase: ' || g_phase || '
1493 Message: ' || sqlerrm);
1494 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.USE_RANGES');
1495 raise;
1496
1497 END USE_RANGES;
1498
1499 -------------------------------------------------------
1500 -- FUNCTION INVALID_PROD_CODE_EXIST
1501 -------------------------------------------------------
1502 FUNCTION INVALID_PROD_CODE_EXIST RETURN BOOLEAN IS
1503
1504 l_count NUMBER := 0;
1505
1506 BEGIN
1507
1508 If g_debug_flag = 'Y' then
1509 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.INVALID_PROD_CODE_EXIST');
1510 End if;
1511
1512 g_phase := 'Check for invalid product code in FII_GL_CCID_DIMENSIONS';
1513
1514 ---------------------------------------------------------------------
1515 -- This function is called after the product id, product category
1516 -- mapping information has been entered into FII_GL_CCID_DIMENSIONS
1517 -- table. At this point, if PRODUCT_ID is populated but the
1518 -- the product category is not populated, then that would mean the
1519 -- the product code is invalid. Every product code should be
1520 -- mapped to a product category
1521 --
1522 -- Note that this has no effect for multiple segment mapping since
1523 -- product_id is alway NULL
1524 ---------------------------------------------------------------------
1525 begin
1526 SELECT 1 INTO l_count
1527 FROM fii_gl_ccid_dimensions
1528 WHERE PRODUCT_ID IS NOT NULL
1529 AND PROD_CATEGORY_ID IS NULL
1530 AND rownum = 1;
1531 exception
1532 when NO_DATA_FOUND then
1533 l_count := 0;
1534 end;
1535
1536 If g_debug_flag = 'Y' then
1537 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.INVALID_PROD_CODE_EXIST');
1538 End if;
1539
1540 IF l_count > 0 THEN
1541 RETURN TRUE;
1542 ELSE
1543 RETURN FALSE;
1544 END IF;
1545
1546 EXCEPTION
1547 WHEN OTHERS THEN
1548 g_retcode := -1;
1549 FII_UTIL.Write_Log('
1550 ------------------------
1551 Error in Function: INVALID_PROD_CODE_EXIST
1552 Phase: '||g_phase||'
1553 Message: '||sqlerrm);
1554 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.INVALID_PROD_CODE_EXIST');
1555 raise;
1556
1557 END INVALID_PROD_CODE_EXIST;
1558
1559 -------------------------------------------------------
1560 -- PROCEDURE MAINTAIN_PROD_ASSGN
1561 -------------------------------------------------------
1562 PROCEDURE MAINTAIN_PROD_ASSGN IS
1563
1564 -------------------------------------------------
1565 -- This cursor loops through charts of account
1566 -- which contains new CCIDs as well as charts of
1567 -- accounts containing CCIDs with updated product
1568 -- assignments. The cursor are ordered by coa_id.
1569 --------------------------------------------------
1570 CURSOR coa_list IS
1571 select coa_id,
1572 prod_seg,
1573 assignment_type_code,
1574 fact_resummarization_needed
1575 from (
1576 SELECT DISTINCT
1577 map.chart_of_accounts_id coa_id,
1578 NVL(map.application_column_name1,'NO_PROD_COLUMN') prod_seg,
1579 map.mapping_type_code assignment_type_code,
1580 'FALSE' fact_resummarization_needed
1581 FROM fii_gl_ccid_dimensions gcc,
1582 fii_dim_mapping_rules map
1583 WHERE gcc.chart_of_accounts_id = map.chart_of_accounts_id
1584 AND map.dimension_short_name = g_dimension_name
1585 AND gcc.code_combination_id > g_max_ccid
1586 UNION ALL
1587 SELECT chart_of_accounts_id coa_id,
1588 NVL(application_column_name1,'NO_PROD_COLUMN') prod_seg,
1589 mapping_type_code assignment_type_code,
1590 'TRUE' fact_resummarization_needed
1591 FROM fii_dim_mapping_rules
1592 WHERE dimension_short_name = g_dimension_name
1593 AND status_code = 'O')
1594 order by 1;
1595
1596 l_resummarization_needed VARCHAR2(10) := 'FALSE';
1597
1598 l_prod_seg VARCHAR2(30);
1599 l_assignment_code VARCHAR2(1);
1600 l_coa_name VARCHAR2(30);
1601
1602 l_previous_coa_id NUMBER := 0;
1603 l_current_coa_id NUMBER := 0;
1604
1605 BEGIN
1606
1607 If g_debug_flag = 'Y' then
1608 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.MAINTAIN_PROD_ASSGN');
1609 End if;
1610
1611 g_phase := 'Maintain Product Assignment for CCIDs';
1612 If g_debug_flag = 'Y' then
1613 FII_UTIL.Write_Log(g_phase);
1614 End if;
1615
1616 -- Bug 4152799
1617 g_dimension_name := 'ENI_ITEM_VBH_CAT';
1618 FOR coa_rec IN coa_list LOOP
1619
1620 ------------------------------------------------------
1621 -- If product assignment for existing CCID has changed,
1622 -- then the base summary would need to be truncated and
1623 -- repopulated again
1624 -------------------------------------------------------
1625 IF (coa_rec.fact_resummarization_needed = 'TRUE') THEN
1626 l_resummarization_needed := 'TRUE';
1627 END IF;
1628
1629 -----------------------------------------------------
1630 --If the current COA_ID is the same as the previous
1631 --one, then we skip this one and go to the next COA
1632 -----------------------------------------------------
1633 l_current_coa_id := coa_rec.coa_id;
1634 IF (l_current_coa_id = l_previous_coa_id) THEN
1635 GOTO end_loop;
1636 END IF;
1637
1638 l_prod_seg := coa_rec.prod_seg;
1639 l_assignment_code := coa_rec.assignment_type_code;
1640 l_current_coa_id := coa_rec.coa_id;
1641 If g_debug_flag = 'Y' then
1642 FII_UTIL.Write_Log('Maintaining Product Assignment for Chart of Accounts ID: '
1643 || coa_rec.coa_id);
1644 End if;
1645
1646 ---------------------------------------------------
1647 -- Check if the assignment type is 'Single Segment'
1648 -- If assignment type is 'Single Segment', then the
1649 -- product segment column name need to be defined
1650 -- in FII_DIM_MAPPING_RULES table
1651 ---------------------------------------------------
1652 IF (l_prod_seg = 'NO_PROD_COLUMN' AND l_assignment_code = 'S') THEN
1653
1654 ---------------------------------------------
1655 -- Get the user name of the chart of accounts
1656 ---------------------------------------------
1657 l_coa_name := GET_COA_NAME(coa_rec.coa_id);
1658
1659 FII_MESSAGE.write_log(
1660 msg_name => 'FII_COA_PROD_UNASSIGN',
1661 token_num => 1,
1662 t1 => 'COA_NAME',
1663 v1 => l_coa_name);
1664
1665 ------------------------------------------------
1666 -- Print out translated message to let user know
1667 -- certain chart of accounts does not have
1668 -- product assignment defined in
1669 -- FII_DIM_MAPPING_RULES table
1670 -------------------------------------------------
1671 FII_MESSAGE.write_output(
1672 msg_name => 'FII_COA_PROD_UNASSIGN',
1673 token_num => 1,
1674 t1 => 'COA_NAME',
1675 v1 => l_coa_name);
1676
1677 raise G_NO_PROD_SEG_DEFINED;
1678 END IF;
1679
1680 ------------------------------------------------------
1681 -- Depending on what the product assignment type is.
1682 -- If 'S', then product reporting classification
1683 -- information is stored in the product segment of
1684 -- the CCID. If type is 'R', then product reporting
1685 -- classification information is stored in table
1686 -- FII_PRODUCT_ASSIGNMENTS
1687 ------------------------------------------------------
1688 IF (l_assignment_code = 'S') THEN
1689 if g_mtc_column_name is NULL then
1690 FII_UTIL.Write_Log('Error in MAINTAIN_PROD_ASSGN: null g_mtc_column_name');
1691 raise G_NO_PROD_SEG_DEFINED;
1692 end if;
1693
1694 USE_SEG (coa_rec.coa_id, coa_rec.prod_seg);
1695
1696 ELSIF (l_assignment_code = 'R') THEN
1697
1698 USE_RANGES (coa_rec.coa_id, coa_rec.prod_seg);
1699
1700 END IF;
1701
1702 ----------------------------------------------------------------
1703 -- After product code and product category information
1704 -- have been inserted into FII_GL_CCID_DIMENSIONS table
1705 -- we will verify if there's any case of 'Invalid Product ID'.
1706 -- This situtation is when a CCID has 'product ID' populated,
1707 -- but there's no corresponding category ID. All valid
1708 -- product ID should be mapped to a category
1709 -- This is for single segment case only.
1710 ----------------------------------------------------------------
1711 IF (l_assignment_code = 'S' and INVALID_PROD_CODE_EXIST) THEN
1712
1713 FII_MESSAGE.write_log(
1714 msg_name => 'FII_INVALID_PROD_CODE_EXIST',
1715 token_num => 0);
1716
1717 --------------------------------------------------
1718 -- Let user know there are invalid product code.
1719 -- Program will exit with error status immediately
1720 --------------------------------------------------
1721 FII_MESSAGE.write_output(
1722 msg_name => 'FII_INVALID_PROD_CODE_EXIST',
1723 token_num => 0);
1724
1725 RAISE G_INVALID_PROD_CODE_EXIST;
1726 END IF;
1727
1728 <<end_loop>>
1729 l_previous_coa_id := l_current_coa_id;
1730
1731 END LOOP;
1732
1733 IF (l_resummarization_needed = 'TRUE') THEN
1734
1735 g_phase:= 'Updating FII_CHANGE_LOG to indicate resummarization is needed';
1736 If g_debug_flag = 'Y' then
1737 FII_UTIL.Write_Log(g_phase);
1738 End if;
1739
1740 --Bug 3234044: should not require AP Resummarization --> remove AP_RESUMMARIZE
1741 --Bug 3401590: use 2 new log items for GL, AR reload (initial)
1742 UPDATE FII_CHANGE_LOG
1743 SET item_value = 'Y',
1744 last_update_date = SYSDATE,
1745 last_update_login = g_fii_login_id,
1746 last_updated_by = g_fii_user_id
1747 WHERE log_item IN ('AR_PROD_CHANGE', 'GL_PROD_CHANGE');
1748
1749 If g_debug_flag = 'Y' then
1750 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_CHANGE_LOG');
1751 End if;
1752
1753 g_phase:= 'Updating FII_DIM_MAPPING_RULES to indicate product assignments are now current';
1754 If g_debug_flag = 'Y' then
1755 FII_UTIL.Write_Log(g_phase);
1756 End if;
1757
1758 -- Bug 4152799
1759 g_dimension_name := 'ENI_ITEM_VBH_CAT';
1760
1761 UPDATE fii_dim_mapping_rules
1762 SET status_code = 'C',
1763 last_update_date = sysdate,
1764 last_update_login = g_fii_login_id,
1765 last_updated_by = g_fii_user_id
1766 WHERE dimension_short_name = g_dimension_name
1767 AND status_code = 'O';
1768
1769 If g_debug_flag = 'Y' then
1770 FII_UTIL.Write_Log ('Updated ' || SQL%ROWCOUNT || ' records in FII_DIM_MAPPING_RULES');
1771 End if;
1772 END IF;
1773
1774 If g_debug_flag = 'Y' then
1775 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.MAINTAIN_PROD_ASSGN');
1776 End if;
1777
1778 EXCEPTION
1779
1780 WHEN G_NO_PROD_SEG_DEFINED THEN
1781 g_retcode := -1;
1782 ROLLBACK;
1783 FII_UTIL.Write_Log('
1784 ---------------------------------------
1785 Error occured in Procedure: MAINTAIN_PROD_ASSGN -> NO_PROD_SEG_DEFINED
1786 Phase: '||g_phase||'
1787 Message: '||sqlerrm);
1788 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.MAINTAIN_PROD_ASSGN');
1789 raise;
1790
1791 WHEN G_INVALID_PROD_CODE_EXIST THEN
1792 g_retcode := -1;
1793 ROLLBACK;
1794 FII_UTIL.Write_Log('
1795 ---------------------------------------
1796 Error occured in Procedure: MAINTAIN_PROD_ASSGN -> INVALID_PROD_CODE_EXIST
1797 Phase: '||g_phase||'
1798 Message: '||sqlerrm);
1799 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.MAINTAIN_PROD_ASSGN');
1800 raise;
1801
1802 WHEN OTHERS THEN
1803 g_retcode := -1;
1804 rollback;
1805 FII_UTIL.Write_Log('
1806 ---------------------------------------
1807 Error occured in Procedure: MAINTAIN_PROD_ASSGN
1808 Phase: '||g_phase||'
1809 Message: '||sqlerrm);
1810 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.MAINTAIN_PROD_ASSGN');
1811 raise;
1812
1813 END MAINTAIN_PROD_ASSGN;
1814
1815
1816 --------------------------------------------------------
1817 -- PROCEDURE INITIALIZE
1818 --------------------------------------------------------
1819 PROCEDURE INITIALIZE is
1820 l_status VARCHAR2(30);
1821 l_industry VARCHAR2(30);
1822 l_stmt VARCHAR2(50);
1823 l_dir VARCHAR2(400);
1824 l_old_prod_cat NUMBER(15);
1825 l_check NUMBER;
1826 l_vset_id NUMBER(15);
1827 l_ret_code NUMBER;
1828 BEGIN
1829
1830 If g_debug_flag = 'Y' then
1831 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.INITIALIZE');
1832 End if;
1833
1834 ----------------------------------------------
1835 -- Do set up for log file
1836 ----------------------------------------------
1837 g_phase := 'Set up for log file';
1838 If g_debug_flag = 'Y' then
1839 FII_UTIL.Write_Log(g_phase);
1840 End if;
1841
1842 l_dir := fnd_profile.value('BIS_DEBUG_LOG_DIRECTORY');
1843 ------------------------------------------------------
1844 -- Set default directory in case if the profile option
1845 -- BIS_DEBUG_LOG_DIRECTORY is not set up
1846 ------------------------------------------------------
1847 if l_dir is NULL then
1848 l_dir := FII_UTIL.get_utl_file_dir ;
1849 end if;
1850
1851 ----------------------------------------------------------------
1852 -- FII_UTIL.initialize will get profile options FII_DEBUG_MODE
1853 -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
1854 -- the log files and output files are written to
1855 ----------------------------------------------------------------
1856 FII_UTIL.initialize('FII_GL_CCID.log','FII_GL_CCID.out',l_dir, 'FII_GL_CCID_C');
1857
1858 -- --------------------------------------------------------
1859 -- Check source ledger setup for DBI
1860 -- --------------------------------------------------------
1861 g_phase := 'Check source ledger setup for DBI';
1862 if g_debug_flag = 'Y' then
1863 FII_UTIL.write_log(g_phase);
1864 end if;
1865
1866 l_check := FII_EXCEPTION_CHECK_PKG.check_slg_setup;
1867
1868 if l_check <> 0 then
1869 RAISE G_NO_SLG_SETUP;
1870 end if;
1871
1872 -- --------------------------------------------------------
1873 -- Find out the user ID, login ID, and current language
1874 -- --------------------------------------------------------
1875 g_phase := 'Find User ID, Login ID, and Current Language';
1876 If g_debug_flag = 'Y' then
1877 FII_UTIL.Write_Log(g_phase);
1878 End if;
1879
1880 g_fii_user_id := FND_GLOBAL.User_Id;
1881 g_fii_login_id := FND_GLOBAL.Login_Id;
1882 g_current_language := FND_GLOBAL.current_language;
1883
1884 IF (g_fii_user_id IS NULL OR g_fii_login_id IS NULL) THEN
1885 RAISE G_LOGIN_INFO_NOT_AVABLE;
1886 END IF;
1887 -- --------------------------------------------------------
1888 -- Find the schema owner
1889 -- --------------------------------------------------------
1890 g_phase := 'Find schema owner for FII';
1891 If g_debug_flag = 'Y' then
1892 FII_UTIL.Write_Log(g_phase);
1893 End if;
1894
1895 IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, g_fii_schema))
1896 THEN NULL;
1897 END IF;
1898
1899 -- --------------------------------------------------------
1900 -- Set the G_PROD_CAT_ENABLED_FLAG for product Category
1901 -- dimension to check if product Category
1902 -- dimension is enabled for DBI or not.
1903 -- Bug 3679295
1904 -- --------------------------------------------------------
1905
1906 g_phase := 'Checking if product Category is enabled or not';
1907
1908 If g_debug_flag = 'Y' then
1909 FII_UTIL.Write_Log(g_phase);
1910 End if;
1911 BEGIN
1912 -- Bug 4152799.
1913 g_dimension_name := 'ENI_ITEM_VBH_CAT';
1914 SELECT dbi_enabled_flag into G_PROD_CAT_ENABLED_FLAG
1915 FROM FII_FINANCIAL_DIMENSIONS
1916 WHERE dimension_short_name = g_dimension_name;
1917
1918 EXCEPTION
1919 WHEN NO_DATA_FOUND THEN
1920 -- If the Product Category set up is not done then set the flag to 'N'
1921 FII_UTIL.Write_Log ('Set up for product category not done');
1922 G_PROD_CAT_ENABLED_FLAG := 'N';
1923 END;
1924
1925 If g_debug_flag = 'Y' then
1926 FII_UTIL.Write_Log('G_PROD_CAT_ENABLED_FLAG = '||G_PROD_CAT_ENABLED_FLAG);
1927 End if;
1928
1929 IF G_PROD_CAT_ENABLED_FLAG = 'Y' THEN
1930 ------------------------------------------------------------
1931 -- Call ENI's API to get default category set ID associated
1932 -- with the product reporting classification structure then
1933 -- get the structure ID associated with the category set
1934 ------------------------------------------------------------
1935 g_phase := 'Getting category set ID associated with product reporting classification structure';
1936 If g_debug_flag = 'Y' then
1937 FII_UTIL.Write_Log(g_phase);
1938 End if;
1939
1940 begin
1941 G_PROD_CAT_SET_ID := ENI_DENORM_HRCHY.get_category_set_id;
1942 exception
1943 when others then
1944 If g_debug_flag = 'Y' then
1945 FII_UTIL.Write_Log('Error occured while: '|| g_phase);
1946 FII_UTIL.Write_Log('The product category dimension is not set up properly.');
1947 End if;
1948 end;
1949
1950 g_phase := 'Getting structure ID associated with the category set';
1951 If g_debug_flag = 'Y' then
1952 FII_UTIL.Write_Log(g_phase);
1953 End if;
1954
1955 begin
1956 SELECT structure_id INTO g_mtc_structure_id
1957 FROM mtl_category_sets_vl
1958 WHERE category_set_id = g_prod_cat_set_id;
1959 exception
1960 when others then
1961 If g_debug_flag = 'Y' then
1962 FII_UTIL.Write_Log('Error occured while: '|| g_phase);
1963 FII_UTIL.Write_Log('The product category dimension is not set up properly.');
1964 End if;
1965 end;
1966
1967 g_phase := 'Getting value set ID associated with the product structure';
1968 If g_debug_flag = 'Y' then
1969 FII_UTIL.Write_Log(g_phase);
1970 End if;
1971
1972 begin
1973 g_mtc_value_set_id := ENI_VALUESET_CATEGORY.GET_FLEX_VALUE_SET_ID
1974 (P_APPL_ID => 401,
1975 P_ID_FLEX_CODE => 'MCAT',
1976 P_VBH_CATSET_ID => g_prod_cat_set_id);
1977 exception
1978 when others then
1979 If g_debug_flag = 'Y' then
1980 FII_UTIL.Write_Log('Error occured while: '|| g_phase);
1981 FII_UTIL.Write_Log('The product category dimension is not set up properly.');
1982 End if;
1983 end;
1984
1985
1986 g_phase := 'Getting segment name in MTL_CATEGORIES associated with product structure';
1987 If g_debug_flag = 'Y' then
1988 FII_UTIL.Write_Log(g_phase);
1989 End if;
1990
1991 begin
1992 --ENI just reports on the first enabled segment that is
1993 --associated with the structure. So we get segment name as:
1994 SELECT application_column_name into g_mtc_column_name
1995 FROM
1996 (select application_column_name
1997 from fnd_id_flex_segments
1998 where application_id = 401
1999 and id_flex_code = 'MCAT'
2000 and id_flex_num = g_mtc_structure_id
2001 and flex_value_set_id = g_mtc_value_set_id
2002 and enabled_flag = 'Y'
2003 order by to_number(substr(application_column_name, 8, 2)) ASC)
2004 WHERE rownum = 1;
2005 exception
2006 when others then
2007 FII_UTIL.Write_Log ('g_mtc_column_name is NULL');
2008 g_mtc_column_name := NULL;
2009 end;
2010
2011 ----------------------------------------------------------------------
2012 --If the program is run in Incremental mode, check the last
2013 --processed product category in FII_CHANGE_LOG with the current
2014 --one G_PROD_CAT_SET_ID from ENI. If they are not same, error out
2015 --with message asking user to either run the program in Initial mode;
2016 --or revert the Product Catalog to the old one.
2017 ----------------------------------------------------------------------
2018 g_phase := 'Checking product category for incremental update...';
2019 If g_debug_flag = 'Y' then
2020 FII_UTIL.Write_Log(g_phase);
2021 End if;
2022
2023 If g_mode <> 'L' then
2024 begin
2025
2026 -- Bug 4152799.
2027 g_log_item := 'PROD_CAT_SET_ID';
2028
2029 SELECT item_value INTO l_old_prod_cat
2030 FROM fii_change_log
2031 WHERE log_item = g_log_item;
2032 exception
2033 when others then
2034 l_old_prod_cat := NULL;
2035 end;
2036 if l_old_prod_cat is not NULL and l_old_prod_cat <> G_PROD_CAT_SET_ID then
2037 FII_MESSAGE.write_log(
2038 msg_name => 'FII_NEW_PROD_CAT_FOUND',
2039 token_num => 0);
2040 FII_MESSAGE.write_output(
2041 msg_name => 'FII_NEW_PROD_CAT_FOUND',
2042 token_num => 0);
2043 raise G_NEW_PROD_CAT_FOUND;
2044 end if;
2045 End If;
2046
2047 END IF;
2048
2049 -- ----------------------------------------------------------------
2050 -- Get the UNASSIGNED ID using the api in gl extraction util package
2051 -- -----------------------------------------------------------------
2052 g_phase := 'Find the shipped FII value set id';
2053 If g_debug_flag = 'Y' then
2054 FII_UTIL.Write_Log(g_phase);
2055 End if;
2056 FII_GL_EXTRACTION_UTIL.get_unassigned_id(G_UNASSIGNED_ID, l_vset_id, l_ret_code);
2057
2058 IF(l_ret_code = -1) THEN
2059 RAISE G_NO_UNASSIGNED_ID;
2060 END IF;
2061
2062 -- --------------------------------------------------------
2063 -- Get the enabled flag for UDD1 and UDD2
2064 -- --------------------------------------------------------
2065 g_phase := 'Get the DBI Enabled flag for UDD1';
2066 If g_debug_flag = 'Y' then
2067 FII_UTIL.Write_Log(g_phase);
2068 End if;
2069 BEGIN
2070 -- Bug 4152799.
2071 g_dimension_name := 'FII_USER_DEFINED_1';
2072 SELECT DBI_ENABLED_FLAG
2073 INTO G_UD1_ENABLED
2074 FROM FII_FINANCIAL_DIMENSIONS
2075 WHERE DIMENSION_SHORT_NAME = g_dimension_name;
2076
2077 EXCEPTION
2078 WHEN NO_DATA_FOUND THEN
2079 -- If the User Defined Dimension1 set up is not done then set the flag to 'N'
2080 FII_UTIL.Write_Log ('Set up for User Defined Dimension1 not done');
2081 G_UD1_ENABLED := 'N';
2082 END;
2083
2084 g_phase := 'Get the DBI Enabled flag for UDD2';
2085
2086 If g_debug_flag = 'Y' then
2087 FII_UTIL.Write_Log(g_phase);
2088 End if;
2089
2090 BEGIN
2091 -- Bug 4152799.
2092 g_dimension_name := 'FII_USER_DEFINED_2';
2093 SELECT DBI_ENABLED_FLAG
2094 INTO G_UD2_ENABLED
2095 FROM FII_FINANCIAL_DIMENSIONS
2096 WHERE DIMENSION_SHORT_NAME = g_dimension_name;
2097 EXCEPTION
2098 WHEN NO_DATA_FOUND THEN
2099 -- If the User Defined Dimension2 set up is not done then set the flag to 'N'
2100 FII_UTIL.Write_Log ('Set up for User Defined Dimension2 not done');
2101 G_UD2_ENABLED := 'N';
2102 END;
2103
2104 If g_debug_flag = 'Y' then
2105 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.INITIALIZE');
2106 End if;
2107
2108 EXCEPTION
2109
2110 WHEN G_NO_SLG_SETUP THEN
2111 FII_UTIL.write_log ('No source ledger setup for DBI');
2112 g_retcode := -1;
2113 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.INITIALIZE');
2114 raise;
2115
2116 WHEN G_NO_UNASSIGNED_ID THEN
2117 FII_UTIL.write_log ('No UNASSIGNED ID');
2118 g_retcode := -1;
2119 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.INITIALIZE');
2120 raise;
2121
2122 WHEN G_LOGIN_INFO_NOT_AVABLE THEN
2123 FII_UTIL.Write_Log ('Can not get User ID and Login ID, program exit');
2124 g_retcode := -1;
2125 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.INITIALIZE');
2126 raise;
2127
2128 WHEN G_NEW_PROD_CAT_FOUND THEN
2129 FII_UTIL.Write_Log ('>>New product catalog is detected for incremental update');
2130 g_retcode := -1;
2131 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.INITIALIZE');
2132 raise;
2133
2134 WHEN OTHERS THEN
2135 g_retcode := -1;
2136 FII_UTIL.Write_Log('
2137 ------------------------
2138 Error in Procedure: INITIALIZE
2139 Phase: '||g_phase||'
2140 Message: '||sqlerrm);
2141 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.INITIALIZE');
2142 raise;
2143
2144 END INITIALIZE;
2145
2146 -----------------------------------------------------------------
2147 -- PROCEDURE DETECT_RELOAD
2148 -----------------------------------------------------------------
2149 PROCEDURE DETECT_RELOAD IS
2150
2151 l_reload VARCHAR2(1);
2152
2153 BEGIN
2154
2155 If g_debug_flag = 'Y' then
2156 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.DETECT_RELOAD');
2157 End if;
2158
2159 g_phase := 'Detect if reload is necessary';
2160 If g_debug_flag = 'Y' then
2161 FII_UTIL.Write_Log(g_phase);
2162 End if;
2163
2164 -- Bug 4152799.
2165 g_log_item := 'CCID_RELOAD';
2166 SELECT item_value INTO l_reload
2167 FROM fii_change_log
2168 WHERE log_item = g_log_item;
2169
2170 IF (l_reload = 'Y') THEN
2171
2172 g_phase := 'Truncate CCID dimension';
2173 If g_debug_flag = 'Y' then
2174 FII_UTIL.Write_Log(g_phase);
2175 End if;
2176
2177 FII_UTIL.TRUNCATE_TABLE ('FII_GL_CCID_DIMENSIONS', g_fii_schema, g_retcode);
2178
2179 -------------------------------------------------------------------
2180 --Bug 3401590: should not update FII_DIM_MAPPING_RULES here
2181 --
2182 --**Update FII_DIM_MAPPING_RULES to force using product assignments
2183 --UPDATE fii_dim_mapping_rules
2184 -- SET status_code = 'O',
2185 -- last_update_date = sysdate,
2186 -- last_update_login = g_fii_login_id,
2187 -- last_updated_by = g_fii_user_id
2188 -- WHERE dimension_short_name = 'ENI_ITEM_VBH_CAT';
2189 -------------------------------------------------------------------
2190
2191 g_phase := 'Reset max CCID processed to 0';
2192 If g_debug_flag = 'Y' then
2193 FII_UTIL.Write_Log(g_phase);
2194 End if;
2195
2196 -- Bug 4152799.
2197 g_log_item := 'MAX_CCID';
2198
2199 UPDATE fii_change_log
2200 SET item_value = '0',
2201 last_update_date = sysdate,
2202 last_update_login = g_fii_login_id,
2203 last_updated_by = g_fii_user_id
2204 WHERE log_item = g_log_item;
2205
2206 IF g_debug_flag = 'Y' THEN
2207 FII_UTIL.Write_Log(SQL%ROWCOUNT || ' record got updated');
2208 END IF;
2209
2210 g_phase := 'Reset CCID_RELOAD to N';
2211 If g_debug_flag = 'Y' then
2212 FII_UTIL.Write_Log(g_phase);
2213 End if;
2214
2215 -- Bug 4152799.
2216 g_log_item := 'CCID_RELOAD';
2217
2218 UPDATE fii_change_log
2219 SET item_value = 'N',
2220 last_update_date = sysdate,
2221 last_update_login = g_fii_login_id,
2222 last_updated_by = g_fii_user_id
2223 WHERE log_item = g_log_item;
2224
2225 IF g_debug_flag = 'Y' THEN
2226 FII_UTIL.Write_Log(SQL%ROWCOUNT || ' record got updated');
2227 END IF;
2228
2229 ELSE
2230 If g_debug_flag = 'Y' then
2231 FII_UTIL.Write_Log('No reload is necessary');
2232 End if;
2233 END IF;
2234
2235 If g_debug_flag = 'Y' then
2236 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.DETECT_RELOAD');
2237 End if;
2238
2239 EXCEPTION
2240 WHEN OTHERS THEN
2241 g_retcode := -1;
2242 FII_UTIL.Write_Log('
2243 -----------------------------
2244 Error occured in Procedure: DETECT_RELOAD
2245 Phase: ' || g_phase || '
2246 Message: ' || sqlerrm);
2247 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.DETECT_RELOAD');
2248 raise;
2249 END DETECT_RELOAD;
2250
2251 -----------------------------------------------------------------
2252 -- PROCEDURE MAIN
2253 -----------------------------------------------------------------
2254 PROCEDURE Main (errbuf IN OUT NOCOPY VARCHAR2 ,
2255 retcode IN OUT NOCOPY VARCHAR2,
2256 pmode IN VARCHAR2) IS
2257
2258 ret_val BOOLEAN := FALSE;
2259
2260 BEGIN
2261
2262 If g_debug_flag = 'Y' then
2263 FII_MESSAGE.Func_Ent('FII_GL_CCID_C.Main');
2264 End if;
2265
2266 errbuf := NULL;
2267 retcode := 0;
2268 g_retcode := 0;
2269 g_mode := pmode;
2270
2271 if (g_mode = 'L') then
2272
2273 g_phase := 'alter session enable';
2274
2275 execute immediate 'alter session enable parallel dml';
2276 execute immediate 'alter session enable parallel query';
2277 end if;
2278
2279 ---------------------------------------------------
2280 -- Initialize all global variables from profile
2281 -- options and other resources
2282 ---------------------------------------------------
2283 g_phase := 'Call INITIALIZE';
2284
2285 INITIALIZE;
2286
2287 ---------------------------------------------------
2288 -- Clean up temporary tables used by the program
2289 ---------------------------------------------------
2290 FII_UTIL.TRUNCATE_TABLE ('FII_GL_CCID_PROD_INT', g_fii_schema, g_retcode);
2291
2292 ---------------------------------------------------
2293 -- Inserting the basic items into FII_CHANGE_LOG if
2294 -- they have not been inserted
2295 ---------------------------------------------------
2296 g_phase := 'Call INIT_DBI_CHANGE_LOG';
2297
2298 INIT_DBI_CHANGE_LOG;
2299
2300 ---------------------------------------------------
2301 -- Populate the global temp table FII_CCID_SLG_GT
2302 ---------------------------------------------------
2303 g_phase := 'Call POPULATE_SLG_TMP';
2304
2305 POPULATE_SLG_TMP;
2306
2307 ---------------------------------------------------
2308 -- Check if program is called in Initial mode
2309 ---------------------------------------------------
2310 if (g_mode = 'L') then
2311
2312 NULL;
2313
2314 ELSE
2315
2316 ----------------------------------------------------
2317 -- Detect if there's changes in fii_slg_assignments
2318 -- table. If yes, then truncate CCID dimension and
2319 -- reset the max CCID processed to 0
2320 -----------------------------------------------------
2321 g_phase := 'Call DETECT_RELOAD';
2322
2323 DETECT_RELOAD;
2324
2325 END IF;
2326
2327 ----------------------------------------------------
2328 -- Find out what are the new CCIDs to process and
2329 -- insert these new CCIDs into FII_GL_CCID_DIMENSIONS
2330 -- table
2331 -----------------------------------------------------
2332 g_phase := 'Call INSERT_NEW_CCID';
2333
2334 INSERT_NEW_CCID;
2335
2336 ----------------------------------------------------
2337 -- Update FII_GL_CCID_DIMENSIONS table with Product
2338 -- assignment information for each CCID
2339 ----------------------------------------------------
2340 g_phase := 'Call MAINTAIN_PROD_ASSGN';
2341
2342 --Bug 3679295. Check if the Product Category Dimension is enabled or not.
2343 IF (G_PROD_CAT_ENABLED_FLAG = 'Y') THEN
2344 MAINTAIN_PROD_ASSGN;
2345 END IF;
2346
2347 -----------------------------------------------------
2348 -- Enh 3985835. Callout to FII_CCID_CALLOUT.UPDATE_FC
2349 -----------------------------------------------------
2350 g_phase := 'Call FII_CCID_CALLOUT.UPDATE_FC';
2351 FII_CCID_CALLOUT.UPDATE_FC(g_max_ccid, g_new_max_ccid);
2352
2353 ----------------------------------------------------
2354 -- Set CCID_RELOAD flag to 'N' after an initial load
2355 -- Bug 3401590
2356 ----------------------------------------------------
2357 if (g_mode = 'L') then
2358 g_phase := 'UPDATE fii_change_log';
2359
2360 -- Bug 4152799.
2361 g_log_item := 'CCID_RELOAD';
2362
2363 UPDATE fii_change_log
2364 SET item_value = 'N',
2365 last_update_date = sysdate,
2366 last_update_login = g_fii_login_id,
2367 last_updated_by = g_fii_user_id
2368 WHERE log_item = g_log_item
2369 AND item_value = 'Y';
2370
2371 If g_debug_flag = 'Y' then
2372 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_change_log');
2373 End if;
2374
2375 end if;
2376
2377 ---------------------------------------------------
2378 -- Clean up temporary tables before exit
2379 ---------------------------------------------------
2380 g_phase := 'TRUNCATE FII_GL_CCID_PROD_INT';
2381
2382 FII_UTIL.TRUNCATE_TABLE ('FII_GL_CCID_PROD_INT', g_fii_schema, g_retcode);
2383
2384 ------------------------------------------------------
2385 -- We have finished the data processing for CCID table
2386 -- it is a logical point to commit.
2387 ------------------------------------------------------
2388 COMMIT;
2389
2390 if (g_mode = 'L') then
2391
2392 g_phase := 'alter session disable';
2393
2394 execute immediate 'alter session disable parallel dml';
2395 execute immediate 'alter session disable parallel query';
2396 end if;
2397
2398 retcode := g_retcode;
2399
2400 If g_debug_flag = 'Y' then
2401 FII_MESSAGE.Func_Succ('FII_GL_CCID_C.Main');
2402 End if;
2403
2404 EXCEPTION
2405 WHEN OTHERS THEN
2406 rollback;
2407
2408 FII_UTIL.Write_Log('
2409 -----------------------------
2410 Error occured in Procedure: MAIN
2411 Phase: ' || g_phase || '
2412 Message: ' || sqlerrm);
2413
2414 FII_MESSAGE.Func_Fail('FII_GL_CCID_C.Main');
2415
2416 retcode := g_retcode;
2417 ret_val := FND_CONCURRENT.Set_Completion_Status
2418 (status => 'ERROR', message => substr(sqlerrm,1,180));
2419 END MAIN;
2420
2421 END FII_GL_CCID_C;