1 PACKAGE BODY FII_COM_CC_DIM_MAPS_PKG AS
2 /* $Header: FIICCMPB.pls 120.1 2005/10/30 05:05:42 appldev noship $ */
3
4 G_UNASSIGNED_ID NUMBER(15);
5 G_CO_MAP_SEG VARCHAR2(30);
6 G_CC_MAP_SEG VARCHAR2(30);
7 g_phase VARCHAR2(120);
8 g_schema_name VARCHAR2(120) := 'FII';
9 g_retcode VARCHAR2(20) := NULL;
10 g_debug_mode VARCHAR2(1)
11 := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
12
13 -- *******************************************************************
14 -- Initialize (Get the unassigned ID and the segments to which
15 -- Company and Cost Center Dimension is attached)
16
17 PROCEDURE Initialize IS
18
19 l_dir VARCHAR2(160);
20 l_vset_id NUMBER(15);
21 l_ret_code NUMBER;
22
23 BEGIN
24
25 ------------------------------
26 -- Do the set up for log file
27 ------------------------------
28 l_dir := fnd_profile.value('BIS_DEBUG_LOG_DIRECTORY');
29
30 ------------------------------------------------------
31 -- Set default directory in case if the profile option
32 -- BIS_DEBUG_LOG_DIRECTORY is not set up
33 ------------------------------------------------------
34 if l_dir is NULL then
35 l_dir := FII_UTIL.get_utl_file_dir;
36 end if;
37
38 ----------------------------------------------------------------
39 -- FII_UTIL.initialize will get profile options FII_DEBUG_MODE
40 -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
41 -- the log files and output files are written to
42 ----------------------------------------------------------------
43 FII_UTIL.initialize('FII_COM_CC_DIM_MAPS_PKG.log',
44 'FII_COM_CC_DIM_MAPS_PKG.out',l_dir,'FII_COM_CC_DIM_MAPS_PKG');
45
46
47 -- Obtain FII schema name
48 g_schema_name := FII_UTIL.get_schema_name ('FII');
49
50 -- Obtain user ID, login ID and initialize package variables
51 FII_USER_ID := FND_GLOBAL.USER_ID;
52 FII_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
53
54 -- If any of the above values is not set, error out
55 IF (FII_User_Id is NULL OR FII_Login_Id is NULL) THEN
56 FII_UTIL.Write_Log ('>>> Failed Intialization');
57 RAISE COMCCDIM_fatal_err;
58 END IF;
59
60 -- Determine if process will be run in debug mode
61 IF (NVL(G_Debug_Mode, 'N') <> 'N') THEN
62 FIIDIM_Debug := TRUE;
63 FII_UTIL.Write_Log (' Debug On');
64 ELSE
65 FIIDIM_Debug := FALSE;
66 FII_UTIL.Write_Log (' Debug Off');
67 END IF;
68
69 -- Turn trace on if process is run in debug mode
70 IF (FIIDIM_Debug) THEN
71 -- Program running in debug mode, turning trace on
72 EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
73 FII_UTIL.Write_Log ('Initialize: Set Trace On');
74 END IF;
75
76 IF (FIIDIM_Debug) THEN
77 FII_UTIL.Write_Log ('Initialize: Now start processing '|| 'Company Cost Center Mapping');
78 End If;
79
80 -- --------------------------------------------------------
81 -- Find the unassigned ID for company and cost center
82 -- --------------------------------------------------------
83 g_phase := 'Find the shipped FII value set id and the unassigned value id';
84 -- This is to be replaced by a call to the api in FII_GL_EXTRACTION_UTIL package
85 FII_GL_EXTRACTION_UTIL.get_unassigned_id(G_UNASSIGNED_ID, l_vset_id, l_ret_code);
86 IF(l_ret_code = -1) THEN
87 RAISE COMCCDIM_fatal_err;
88 END IF;
89 -- ---------------------------------------------------------------------------
90 -- Get the segment to which the dimension is mapped for company and cost center
91 -- ----------------------------------------------------------------------------
92
93 g_phase := 'Find the segments to which Company and Cost Center are mapped' ;
94
95 BEGIN
96 SELECT BALANCING_OR_COST_CENTER
97 INTO G_CO_MAP_SEG
98 FROM FII_FINANCIAL_DIMENSIONS
99 WHERE DIMENSION_SHORT_NAME = 'FII_COMPANIES';
100
101 EXCEPTION
102 WHEN NO_DATA_FOUND THEN
103 FII_UTIL.Write_Log ('Set up for Company not done');
104 G_CO_MAP_SEG := NULL;
105 END;
106
107 BEGIN
108 SELECT BALANCING_OR_COST_CENTER
109 INTO G_CC_MAP_SEG
110 FROM FII_FINANCIAL_DIMENSIONS
111 WHERE DIMENSION_SHORT_NAME = 'HRI_CL_ORGCC';
112
113 EXCEPTION
114 WHEN NO_DATA_FOUND THEN
115 FII_UTIL.Write_Log ('Set up for Cost Center not done');
116 G_CC_MAP_SEG := NULL;
117 END;
118
119 IF (FIIDIM_Debug) THEN
120 FII_UTIL.Write_Log ('COST CENTER is Mapped to segment : '|| G_CC_MAP_SEG );
121 FII_UTIL.Write_Log ('COMPANY is Mapped to segment : '|| G_CO_MAP_SEG);
122 END IF;
123
124
125
126 Exception
127
128 When COMCCDIM_fatal_err then
129 FII_UTIL.Write_Log ('FII_COM_CC_DIM_MAPS_PKG.Initialize : '|| 'User defined error');
130 FII_UTIL.Write_Log ('G_PHASE : ' || g_phase);
131 FND_CONCURRENT.Af_Rollback;
132 FII_MESSAGE.Func_Fail(func_name => 'FII_COM_CC_DIM_MAPS_PKG.Initialize');
133 raise;
134
135 When others then
136 FII_UTIL.Write_Log ('Unexpected error when calling Initialize...');
137 FII_UTIL.Write_Log ( 'G_PHASE: ' || g_phase);
138 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
139 RAISE;
140
141 END Initialize;
142
143 -- *******************************************************************
144 -- Populate_com_cc_Maps (Populate the Global temporary table )
145 -- Insert G_UNASSIGNED_ID as the parent if the company/ cost center
146 -- id is not present in the dimension
147
148 PROCEDURE Populate_com_cc_Maps IS
149 BEGIN
150 IF (FIIDIM_Debug) THEN
151 FII_MESSAGE.Func_Ent(func_name => 'FII_COM_CC_DIM_MAPS_PKG.Populate_com_cc_Maps');
152 END IF;
153
154 -- Insert records in the com cc mappings GI table
155 g_phase := 'Insert into FII_COM_CC_DIM_MAP_GT';
156
157 ------------------------------------------------------------------------------
158 -- There is a possibility of having Company_id and cost_center_id
159 -- in FII_GL_CCID_DIMENSIONS which might not be present in the
160 -- corresponding Dimension hierarchy (it might now be mapped)
161 -- To populate those id's also in FII_COM_CC_DIM_MAPS_GT
162 -- outer join is used.
163 -- Also since both Company and Cost Center Dimension can be mapped
164 -- to either Balancing / Cost Center Segment so we need to identify
165 -- that the dimension is mapped to which segment. If the Dimension
166 -- mapped to Balancing Segment then the Company_id from FII_GL_CCID_DIMENSIONS
167 -- or FII_BUDGET_BASE should be mapped to the dimension table
168 -- and if the dimension is mapped to Cost Center Segment then
169 -- Cost_Center_id From FII_GL_CCID_DIMENSIONS and FII_BUDGET_BASE
170 -- should be mapped to the dimension table. Decode is used for this purpose
171 --------------------------------------------------------------------------------
172
173 INSERT INTO FII_COM_CC_DIM_MAPS_GT (PARENT_COMPANY_DIM_ID,
174 CHILD_COMPANY_ID,
175 PARENT_COST_CENTER_DIM_ID,
176 CHILD_COST_CENTER_ID)
177 SELECT
178 h1.parent_company_id,
179 g.company_id,
180 h2.parent_cc_id,
181 g.cost_center_id
182 FROM
183 (select distinct COMPANY_ID, COST_CENTER_ID
184 from FII_GL_CCID_DIMENSIONS
185 UNION
186 select distinct COMPANY_ID, COST_CENTER_ID
187 from FII_BUDGET_BASE
188 )g,
189
190 (SELECT fh.parent_COMPANY_id, fh.child_COMPANY_id
191 FROM FII_FULL_COMPANY_HIERS fh
192 WHERE fh.parent_company_id IN
193 (SELECT ph.parent_COMPANY_id
194 FROM FII_COMPANY_HIERARCHIES ph
195 WHERE ph.is_leaf_flag = 'Y')) h1,
196 (SELECT fh.parent_cc_id, fh.child_cc_id
197 FROM FII_FULL_COST_CTR_HIERS fh
198 WHERE fh.parent_cc_id IN
199 (SELECT ph.parent_cc_id
200 FROM FII_COST_CTR_HIERARCHIES ph
201 WHERE ph.is_leaf_flag = 'Y')) h2
202 WHERE DECODE(G_CO_MAP_SEG,
203 'BALANCING', g.COMPANY_ID,
204 'COST CENTER', g.cost_center_id, G_UNASSIGNED_ID) =
205 h1.child_company_id
206 AND DECODE(G_CC_MAP_SEG,
207 'BALANCING', g.COMPANY_ID,
208 'COST CENTER', g.cost_center_id, G_UNASSIGNED_ID) =
209 h2.child_cc_id ;
210 -- Removed outer join as we dont want unassigned as a parent
211 IF (FIIDIM_Debug) THEN
212 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows from FII_COM_CC_DIM_MAP_GT');
213 FII_MESSAGE.Func_Succ(func_name => 'FII_COM_CC_DIM_MAPS_PKG.Populate_com_cc_Maps');
214 END IF;
215
216 Exception
217
218 When others then
219 FII_UTIL.Write_Log ('Unexpected error when calling Populate_com_cc_Maps...');
220 FII_UTIL.Write_Log ( 'G_PHASE: ' || g_phase);
221 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
222 RAISE;
223
224 END Populate_com_cc_Maps;
225
226
227 -- **************************************************************************
228 -- This is the main procedure of COMPANY COST CENTER Mapping Table population
229 -- program (initial populate).
230
231 PROCEDURE Init_Load (errbuf OUT NOCOPY VARCHAR2,
232 retcode OUT NOCOPY VARCHAR2) IS
233 ret_val BOOLEAN := FALSE;
234
235 BEGIN
236
237 IF (FIIDIM_Debug) THEN
238 FII_MESSAGE.Func_Ent(func_name => 'FII_COM_CC_DIM_MAPS_PKG.Init_Load');
239 END IF;
240
241 --First do the initialization
242 g_phase := 'Calling intialize procedure';
243
244 Initialize;
245
246 -- Populate the com cc dim maps GT.
247 g_phase := 'Populate FII_COM_CC_DIM_MAPS_GT';
248
249 Populate_com_cc_Maps;
250 -- Populate by selecting from the mappings table
251
252 -- Now all the mappings are populated in the GT table.
253 -- Copy data from GT table to Mappings table
254 g_phase := 'Copy from FII_COM_CC_DIM_MAP_GT to FII_COM_CC_DIM_MAPS';
255
256 FII_UTIL.truncate_table ('FII_COM_CC_DIM_MAPS', 'FII', g_retcode);
257
258 Insert into /*+ APPEND */ FII_COM_CC_DIM_MAPS (
259 PARENT_COMPANY_DIM_ID,
260 CHILD_COMPANY_ID,
261 PARENT_COST_CENTER_DIM_ID,
262 CHILD_COST_CENTER_ID,
263 creation_date,
264 created_by,
265 last_update_date,
266 last_updated_by,
267 last_update_login)
268 SELECT
269 PARENT_COMPANY_DIM_ID,
270 CHILD_COMPANY_ID,
271 PARENT_COST_CENTER_DIM_ID,
272 CHILD_COST_CENTER_ID,
273 SYSDATE,
274 FII_USER_ID,
275 SYSDATE,
276 FII_USER_ID,
277 FII_LOGIN_ID
278 FROM FII_COM_CC_DIM_MAPS_GT;
279
280 IF (FIIDIM_Debug) THEN
281 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows from FII_COM_CC_DIM_MAPS');
282 END IF;
283
284
285 FND_CONCURRENT.Af_Commit;
286
287 --Call FND_STATS to collect statistics after populating the tables.
288 -- This will be in RSG data.
289 g_phase := 'gather_table_stats for FII_COM_CC_DIM_MAPS';
290
291 FND_STATS.gather_table_stats
292 (ownname => g_schema_name,
293 tabname => 'FII_COM_CC_DIM_MAPS');
294
295 g_phase := 'gather_table_stats for MLOG$_FII_COM_CC_DIM_MAPS';
296
297 FND_STATS.gather_table_stats
298 (ownname => g_schema_name,
299 tabname => 'MLOG$_FII_COM_CC_DIM_MAPS');
300
301 --=====================================================================
302
303 IF (FIIDIM_Debug) THEN
304 FII_MESSAGE.Func_Succ(func_name => 'FII_COM_CC_DIM_MAPS_PKG.Init_Load');
305 END IF;
306
307 -- Exception handling
308
309 EXCEPTION
310
311 WHEN COMCCDIM_fatal_err THEN
312
313 FII_UTIL.Write_Log ('FII_COM_CC_DIM_MAPS_PKG.Init_Load: '||
314 'User defined error');
315 FND_CONCURRENT.Af_Rollback;
316 FII_MESSAGE.Func_Fail(func_name => 'FII_COM_CC_DIM_MAPS_PKG.Init_Load');
317 retcode := sqlcode;
318 ret_val := FND_CONCURRENT.Set_Completion_Status
319 (status => 'ERROR', message => substr(sqlerrm,1,180));
320
321 WHEN OTHERS THEN
322 FII_UTIL.Write_Log ('Init_Load -> phase: '|| g_phase);
323 FII_UTIL.Write_Log (
324 'Other error in FII_CC_MAINTAIN_PKG.Init_Load: ' || substr(sqlerrm,1,180));
325 FND_CONCURRENT.Af_Rollback;
326 FII_MESSAGE.Func_Fail(func_name => 'FII_COM_CC_DIM_MAPS_PKG.Init_Load');
327 retcode := sqlcode;
328 ret_val := FND_CONCURRENT.Set_Completion_Status
329 (status => 'ERROR', message => substr(sqlerrm,1,180));
330
331 END Init_Load;
332
333
334 -- **************************************************************************
335 -- This is the main procedure of COMPANY COST CENTER Mapping Table population
336 -- program (incremental update).
337
338 PROCEDURE Incre_Update (errbuf OUT NOCOPY VARCHAR2,
339 retcode OUT NOCOPY VARCHAR2) IS
340
341 ret_val BOOLEAN := FALSE;
342 ret_code VARCHAR2(2):=0;
343 BEGIN
344
345 IF (FIIDIM_Debug) THEN
346 FII_MESSAGE.Func_Ent(func_name => 'FII_COM_CC_DIM_MAPS_PKG.Incre_Update');
347 END IF;
348
349 --First do the initialization
350 g_phase := 'Calling intialize procedure';
351
352 Initialize;
353
354 -- Populate the com cc dim maps GT table.
355 g_phase := 'Populate FII_COM_CC_DIM_MAPS_GT';
356
357 Populate_com_cc_Maps;
358
359 -- -----------------------------------------------------
360 -- Incremental maintainence of FII_COM_CC_DIM_MAPS
361 -- This is done in 2 statements
362 -- One Delete statement and one Insert statement
363 -- Do a diff with the mappings table with the GT table
364 -- ------------------------------------------------------
365
366 -- Do a diff with the mappings table
367 DELETE FROM FII_COM_CC_DIM_MAPS
368 WHERE
369 (PARENT_COMPANY_DIM_ID,
370 CHILD_COMPANY_ID,
371 PARENT_COST_CENTER_DIM_ID,
372 CHILD_COST_CENTER_ID)IN
373 (SELECT PARENT_COMPANY_DIM_ID,
374 CHILD_COMPANY_ID,
375 PARENT_COST_CENTER_DIM_ID,
376 CHILD_COST_CENTER_ID
377 FROM FII_COM_CC_DIM_MAPS
378 MINUS
379 SELECT PARENT_COMPANY_DIM_ID,
380 CHILD_COMPANY_ID,
381 PARENT_COST_CENTER_DIM_ID,
382 CHILD_COST_CENTER_ID
383 FROM FII_COM_CC_DIM_MAPS_GT);
384
385
386 IF (FIIDIM_Debug) THEN
387 FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_COM_CC_DIM_MAPS');
388 END IF;
389
390
391 Insert into FII_COM_CC_DIM_MAPS (
392 PARENT_COMPANY_DIM_ID,
393 CHILD_COMPANY_ID,
394 PARENT_COST_CENTER_DIM_ID,
395 CHILD_COST_CENTER_ID,
396 creation_date,
397 created_by,
398 last_update_date,
399 last_updated_by,
400 last_update_login)
401 (SELECT
402 PARENT_COMPANY_DIM_ID,
403 CHILD_COMPANY_ID,
404 PARENT_COST_CENTER_DIM_ID,
405 CHILD_COST_CENTER_ID,
406 SYSDATE,
407 FII_USER_ID,
408 SYSDATE,
409 FII_USER_ID,
410 FII_LOGIN_ID
411 FROM FII_COM_CC_DIM_MAPS_GT
412 MINUS
413 SELECT
414 PARENT_COMPANY_DIM_ID,
415 CHILD_COMPANY_ID,
416 PARENT_COST_CENTER_DIM_ID,
417 CHILD_COST_CENTER_ID,
418 SYSDATE,
419 FII_USER_ID,
420 SYSDATE,
421 FII_USER_ID,
422 FII_LOGIN_ID
423 FROM FII_COM_CC_DIM_MAPS);
424
425 IF (FIIDIM_Debug) THEN
426 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COM_CC_DIM_MAPS');
427 END IF;
428
429 FND_CONCURRENT.Af_Commit;
430
431 -- Call FND_STATS to collect statistics after populating the tables.
432 -- This will be in RSG data.
433 g_phase := 'gather_table_stats for FII_COM_CC_DIM_MAPS';
434
435 FND_STATS.gather_table_stats
436 (ownname => g_schema_name,
437 tabname => 'FII_COM_CC_DIM_MAPS');
438
439 -- Bug 4200473. Not to analyze MLOG in incremental run.
440 -- As per performance teams suggestions.
441 --g_phase := 'gather_table_stats for MLOG$_FII_COM_CC_DIM_MAPS';
442
443 --FND_STATS.gather_table_stats
444 --(ownname => g_schema_name,
445 --tabname => 'MLOG$_FII_COM_CC_DIM_MAPS');
446
447 IF (FIIDIM_Debug) THEN
448 FII_MESSAGE.Func_Succ(func_name => 'FII_COM_CC_DIM_MAPS_PKG.Incre_Update');
449 END IF;
450
451 -- Exception handling
452
453 EXCEPTION
454 WHEN COMCCDIM_fatal_err THEN
455 FII_UTIL.Write_Log ('FII_COM_CC_DIM_MAPS_PKG.Incre_Update'||
456 'User defined error');
457
458 FND_CONCURRENT.Af_Rollback;
459 FII_MESSAGE.Func_Fail(func_name => 'FII_COM_CC_DIM_MAPS_PKG.Incre_Update');
460 retcode := sqlcode;
461 ret_val := FND_CONCURRENT.Set_Completion_Status
462 (status => 'ERROR', message => substr(sqlerrm,1,180));
463
464 WHEN OTHERS THEN
465 FII_UTIL.Write_Log ('Incre_Update -> phase: '|| g_phase);
466 FII_UTIL.Write_Log (
467 'Other error in FII_COM_CC_DIM_MAPS_PKG.Incre_Update: ' || substr(sqlerrm,1,180));
468
469
470 FND_CONCURRENT.Af_Rollback;
471 FII_MESSAGE.Func_Fail(func_name => 'FII_COM_CC_DIM_MAPS_PKG.Incre_Update');
472 retcode := sqlcode;
473 ret_val := FND_CONCURRENT.Set_Completion_Status
474 (status => 'ERROR', message => substr(sqlerrm,1,180));
475
476 END Incre_Update;
477
478 END FII_COM_CC_DIM_MAPS_PKG ;
[Home] [Help]