[Home] [Help]
PACKAGE BODY: APPS.GL_FLATTEN_LEDGER_SEG_VALS
Source
1 PACKAGE BODY GL_FLATTEN_LEDGER_SEG_VALS AS
2 /* $Header: glufllvb.pls 120.11 2011/06/02 05:30:56 skotakar ship $ */
3
4 -- ********************************************************************
5 -- FUNCTION
6 -- FIX_BY_COA
7 -- PURPOSE
8 -- This function is the entry point when flattening program is called in
9 -- LV mode, it indicates changes in the ledger definition.
10 -- HISTORY
11 -- 06-04-2001 Srini Pala Created
12 -- ARGUMENTS
13 -- EXAMPLE
14 -- RET_STATUS := FIX_BY_COA()
15 --
16
17
18 FUNCTION FIX_BY_COA RETURN BOOLEAN IS
19
20 l_number_of_rows NUMBER := 0;
21 ret_val BOOLEAN := TRUE;
22 GLSTFL_FATAL_ERR EXCEPTION;
23 l_status_flag VARCHAR2(1);
24 BEGIN
25
26 GL_MESSAGE.FUNC_ENT (FUNC_NAME =>
27 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa');
28
29
30 -- The flow of this routine is as follows
31 -- First clean records with status_code 'I' and update records with
32 -- status_code ='D' to NULL in the GL_LEDGER_SEGMENT_VALUES table
33 -- Detect any changes in GL_LEDGER_NORM_SEG_VALS table, then mainatain
34 -- GL_LEDGER_SEGMENT_VALUES based on these changes.
35 -- Calls routine Error_Check to makesure that the data is fine.
36
37
38 -- Cleaning GL_LEDGER_SEGMENT_VALUES before processing
39
40 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_DEBUG) THEN
41 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0180',
42 TOKEN_NUM => 2,
43 T1 =>'ROUTINE',
44 V1 =>
45 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa()',
46 T2 =>'ACTION',
47 V2 =>'DELETING RECORDS WITH STATUS CODE I '
48 ||' IN THE TABLE'
49 ||' GL_LEDGER_SEGMENT_VALUES');
50 END IF;
51
52 -- To improve performance for bug fix # 5075776
53 l_status_flag := 'I';
54
55 DELETE
56 FROM GL_LEDGER_SEGMENT_VALUES
57 WHERE STATUS_CODE = l_status_flag
58 AND LEDGER_ID IN
59 (SELECT LEDGER_ID
60 FROM GL_LEDGERS
61 WHERE CHART_OF_ACCOUNTS_ID = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
62
63 L_Number_Of_Rows := SQL%ROWCOUNT;
64 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0119',
65 TOKEN_NUM =>2,
66 T1 =>'NUM',
67 V1 => TO_CHAR(L_NUMBER_OF_ROWS),
68 T2 =>'TABLE',
69 V2 =>'GL_LEDGER_SEGMENT_VALUES');
70
71
72 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_DEBUG) THEN
73 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0180',
74 TOKEN_NUM => 2,
75 T1 =>'ROUTINE',
76 V1 =>
77 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa()',
78 T2 =>'ACTION',
79 V2 =>'Updating records with status code D'
80 ||' in the table'
81 ||' GL_LEDGER_SEGMENT_VALUES');
82 END IF;
83
84 UPDATE GL_LEDGER_SEGMENT_VALUES
85 SET status_code = NULL
86 WHERE status_code = 'D'
87 AND ledger_id IN
88 (SELECT ledger_id
89 FROM GL_LEDGERS
90 WHERE chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
91
92 L_Number_Of_Rows := SQL%ROWCOUNT;
93 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0118',
94 TOKEN_NUM =>2,
95 T1 =>'NUM',
96 V1 => TO_CHAR(L_NUMBER_OF_ROWS),
97 T2 =>'TABLE',
98 V2 =>'GL_LEDGER_SEGMENT_VALUES');
99
100 FND_CONCURRENT.AF_COMMIT; -- COMMIT Point
101
102 -- Update Start_Date/End_Date In GL_LEDGER_SEGMENT_VALUES
103
104 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_DEBUG) THEN
105 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0180',
106 TOKEN_NUM => 2,
107 T1 =>'ROUTINE',
108 V1 =>
109 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa()',
110 T2 =>'ACTION',
111 V2 =>'UPDATING START_DATE/END_DATE'
112 ||' IN THE TABLE'
113 ||' GL_LEDGER_SEGMENT_VALUES');
114 END IF;
115
116 UPDATE GL_LEDGER_SEGMENT_VALUES GLLSV
117 SET (GLLSV.START_DATE, GLLSV.END_DATE) =
118 (SELECT GLLNSV.START_DATE, GLLNSV.END_DATE
119 FROM GL_LEDGER_NORM_SEG_VALS GLLNSV
120 WHERE GLLNSV.RECORD_ID = GLLSV.PARENT_RECORD_ID)
121 WHERE GLLSV.PARENT_RECORD_ID IN
122 (SELECT GLLNSV2.RECORD_ID
123 FROM GL_LEDGERS GLL,
124 GL_LEDGER_NORM_SEG_VALS GLLNSV2
125 WHERE GLL.CHART_OF_ACCOUNTS_ID =
126 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
127 AND GLLNSV2.STATUS_CODE = 'U'
128 AND GLLNSV2.REQUEST_ID =
129 GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
130 AND GLLNSV2.LEDGER_ID = GLL.LEDGER_ID);
131
132 L_Number_Of_Rows := SQL%ROWCOUNT;
133 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0118',
134 TOKEN_NUM =>2,
135 T1 =>'NUM',
136 V1 => TO_CHAR(L_NUMBER_OF_ROWS),
137 T2 =>'TABLE',
138 V2 =>'GL_LEDGER_SEGMENT_VALUES');
139
140 -- Marking outdated records in GL_LEDGER_SEGMENT_VALUES for delete.
141
142 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_DEBUG) THEN
143 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0180',
144 TOKEN_NUM => 2,
145 T1 =>'ROUTINE',
146 V1 =>
147 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa()',
148 T2 =>'ACTION',
149 V2 =>'Updating outdated records'
150 ||' in the table'
151 ||' GL_LEDGER_SEGMENT_VALUES for delete');
152 END IF;
153
154 UPDATE GL_LEDGER_SEGMENT_VALUES
155 SET STATUS_CODE = 'D'
156 WHERE PARENT_RECORD_ID IN
157 (SELECT RECORD_ID
158 FROM GL_LEDGERS GLL,
159 GL_LEDGER_NORM_SEG_VALS GLLNSV
160 WHERE GLL.CHART_OF_ACCOUNTS_ID =
161 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
162 AND GLLNSV.STATUS_CODE = 'D'
163 AND GLLNSV.LEDGER_ID = GLL.LEDGER_ID);
164
165 L_Number_Of_Rows := SQL%ROWCOUNT;
166 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0118',
167 TOKEN_NUM =>2,
168 T1 =>'NUM',
169 V1 => TO_CHAR(l_number_of_rows),
170 T2 =>'TABLE',
171 V2 =>'GL_LEDGER_SEGMENT_VALUES');
172
173 -- Inserting new Ledger-Segment value assignments into the table
174 -- GL_LEDGER_SEGMENT_VALUES.
175
176 -- The following statement inserts a record into GL_LEDGER_SEGMENT_VALUES
177 -- table for every new record in GL_LEDGER_NORM_SEG_VALS with
178 -- status_code 'I' and segment_value_type_code of 'S'.
179
180 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_DEBUG) THEN
181 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0180',
182 TOKEN_NUM => 2,
183 T1 =>'ROUTINE',
184 V1 =>
185 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa()',
186 T2 =>'ACTION',
187 V2 =>'Inserting new record(S) Into'
188 ||' GL_LEDGER_SEGMENT_VALUES'
189 ||' for every record with status code I '
190 ||' and segment_value_type_code of S'
191 ||' in the table'
192 ||' GL_LEDGER_NORM_SEG_VALS ');
193 END IF;
194
195 -- Bug 8265487 - Added GLL.IMPLICIT_ACCESS_SET_ID IS NOT NULL
196 INSERT INTO GL_LEDGER_SEGMENT_VALUES
197 (LEDGER_ID, SEGMENT_TYPE_CODE, SEGMENT_VALUE, STATUS_CODE,
198 PARENT_RECORD_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
199 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, START_DATE,
200 END_DATE)
201 (SELECT GLLNSV.LEDGER_ID, GLLNSV.SEGMENT_TYPE_CODE,
202 GLLNSV.SEGMENT_VALUE, 'I', GLLNSV.RECORD_ID,
203 SYSDATE,
204 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
205 SYSDATE,
206 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
207 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
208 GLLNSV.START_DATE, GLLNSV.END_DATE
209 FROM GL_LEDGERS GLL,
210 GL_LEDGER_NORM_SEG_VALS GLLNSV
211 WHERE GLL.CHART_OF_ACCOUNTS_ID =
212 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
213 AND GLLNSV.LEDGER_ID = GLL.LEDGER_ID
214 AND GLLNSV.STATUS_CODE = 'I'
215 AND GLL.IMPLICIT_ACCESS_SET_ID IS NOT NULL
216 AND GLLNSV.REQUEST_ID =
217 GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
218 AND GLLNSV.SEGMENT_VALUE_TYPE_CODE = 'S');
219
220
221 L_Number_Of_Rows := SQL%ROWCOUNT;
222 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0117',
223 TOKEN_NUM =>2,
224 T1 =>'NUM',
225 V1 =>TO_CHAR(L_NUMBER_OF_ROWS),
226 T2 =>'TABLE',
227 V2 =>'GL_LEDGER_SEGMENT_VALUES');
228 l_number_of_rows := 0;
229
230 -- The following statement inserts a record into GL_LEDGER_SEGMENT_VALUES
231 -- table for every new record in GL_LEDGER_NORM_SEG_VALS with
232 -- status_code 'I' and segment_value_type_code of 'C'.
233
234 If (GL_FLATTEN_SETUP_DATA.GLSTFL_DEBUG) THEN
235 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0180',
236 TOKEN_NUM => 2,
237 T1 =>'ROUTINE',
238 V1 =>
239 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa()',
240 T2 =>'ACTION',
241 V2 =>'INSERT NEW RECORD(S) INTO'
242 ||' GL_LEDGER_SEGMENT_VALUES'
243 ||' FOR EVERY RECORD WITH STATUS CODE I '
244 ||' AND SEGMENT_VALUE_TYPE_CODE OF C'
245 ||' IN THE TABLE'
246 ||' GL_LEDGER_NORM_SEG_VALS ');
247 END IF;
248
249 -- Obtain a shared Lock on balancing and management value set ids
250
251 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE = 'VH') OR
252 (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE = 'LV')THEN
253
254 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
255 GL_MESSAGE.Write_Log(msg_name => 'SHRD0181',
256 token_num => 3,
257 t1 => 'ROUTINE',
258 v1 =>
259 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa()',
260 t2 => 'VARIABLE',
261 v2 => 'GLSTFL_Bal_Vs_Id',
262 t3 => 'VALUE',
263 v3 =>
264 TO_CHAR(GL_FLATTEN_SETUP_DATA.GLSTFL_Bal_Vs_Id));
265
266 GL_MESSAGE.Write_Log(msg_name => 'SHRD0181',
267 token_num => 3,
268 t1 => 'ROUTINE',
269 v1 =>
270 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa()',
271 t2 => 'VARIABLE',
272 v2 => 'GLSTFL_Mgt_Vs_Id',
273 t3 => 'VALUE',
274 v3 =>
275 TO_CHAR(GL_FLATTEN_SETUP_DATA.GLSTFL_Mgt_Vs_Id));
276 END IF;
277
278 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
279 GL_MESSAGE.Write_Log
280 (msg_name => 'SHRD0180',
281 token_num => 2,
282 t1 => 'ROUTINE',
283 v1 => 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa()',
284 t2 => 'ACTION',
285 v2 => 'Obtain shared lock on balancing segment...');
286 END IF;
287
288 ret_val := GL_FLATTEN_SETUP_DATA.Request_Lock
289 (X_Param_Type => 'V',
290 X_Param_Id =>
291 GL_FLATTEN_SETUP_DATA.GLSTFL_Bal_Vs_Id,
292 X_Lock_Mode => 4, -- SHARED mode
293 X_Keep_Looping => TRUE,
294 X_Max_Trys => 5);
295
296 IF (NOT ret_val) THEN
297 RAISE GLSTFL_fatal_err;
298 END IF;
299
300 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
301 GL_MESSAGE.Write_Log
302 (msg_name => 'SHRD0180',
303 token_num => 2,
304 t1 => 'ROUTINE',
305 v1 => 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa()',
306 t2 => 'ACTION',
307 v2 => 'Obtain shared lock on management segment...');
308 END IF;
309
310 -- If mgt_vs_id is different from bal_vs_id, obtain SHARED lock for
311 -- management segment Value set id
312
313 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Bal_Vs_Id <>
314 GL_FLATTEN_SETUP_DATA.GLSTFL_Mgt_Vs_Id) THEN
315
316 ret_val := GL_FLATTEN_SETUP_DATA.Request_Lock
317 (X_Param_Type => 'V',
318 X_Param_Id =>
319 GL_FLATTEN_SETUP_DATA.GLSTFL_Mgt_Vs_Id,
320 X_Lock_Mode => 4, -- SHARED mode
321 X_Keep_Looping => TRUE,
322 X_Max_Trys => 5);
323
324 IF (NOT ret_val) THEN
325 RAISE GLSTFL_fatal_err;
326 END IF;
327
328 END IF;
329
330 END IF; -- End for operation mode 'VH'
331
332 -- These locks will be released in GL_FLATTEN_SETUP_DATA.Main()package
333 -- after successfull completion of the clean up routines.
334
335 INSERT INTO GL_LEDGER_SEGMENT_VALUES
336 (LEDGER_ID, SEGMENT_TYPE_CODE,SEGMENT_VALUE, STATUS_CODE,
337 PARENT_RECORD_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
338 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, START_DATE,
339 END_DATE)
340 (SELECT GLLNSV.LEDGER_ID, GLLNSV.SEGMENT_TYPE_CODE,
341 GLSVH.CHILD_FLEX_VALUE, 'I', GLLNSV.RECORD_ID,
342 SYSDATE,
343 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
344 SYSDATE,
345 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
346 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
347 GLLNSV.START_DATE, GLLNSV.END_DATE
348 FROM GL_LEDGERS GLL,
349 GL_LEDGER_NORM_SEG_VALS GLLNSV,
350 GL_SEG_VAL_HIERARCHIES GLSVH
351 WHERE GLL.CHART_OF_ACCOUNTS_ID =
352 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
353 AND GLLNSV.LEDGER_ID = GLL.LEDGER_ID
354 AND GLLNSV.STATUS_CODE = 'I'
355 AND GLLNSV.REQUEST_ID =
356 GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
357 AND GLLNSV.SEGMENT_VALUE_TYPE_CODE = 'C'
358 AND GLSVH.FLEX_VALUE_SET_ID =
359 DECODE(GLLNSV.SEGMENT_TYPE_CODE,
360 'B',GLL.BAL_SEG_VALUE_SET_ID,
361 'M',GLL.MGT_SEG_VALUE_SET_ID)
362 AND GLSVH.PARENT_FLEX_VALUE = GLLNSV.SEGMENT_VALUE
363 AND GLSVH.STATUS_CODE IS NULL);
364
365 L_Number_Of_Rows := SQL%ROWCOUNT;
366 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0117',
367 TOKEN_NUM =>2,
368 T1 =>'NUM',
369 V1 =>TO_CHAR(L_NUMBER_OF_ROWS),
370 T2 =>'TABLE',
371 V2 =>'GL_LEDGER_SEGMENT_VALUES');
372
373 l_number_of_rows := 0;
374
375 -- ALC changes.
376 -- In the new Additional ledger representation , all ALCs associated with
377 -- a source ledger should also have records for the specific segment values.
378
379 -- The following statment will takes care of the above requirement and
380 -- if there is a new ALC added to the source ledger.
381
382 -- Update and delete of these rows will be taken care by the original
383 -- logic in FIX_BY_COA() of this package.
384
385
386 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_DEBUG) THEN
387 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0180',
388 TOKEN_NUM => 2,
389 T1 =>'ROUTINE',
390 V1 =>
391 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa()',
392 T2 =>'ACTION',
393 V2 =>'Inserting ALC ledger record(S) into'
394 ||' GL_LEDGER_SEGMENT_VALUES'
395 ||' for every source ledger '
396 ||' in the '
397 ||' GL_LEDGER_NORM_SEG_VALS table');
398 END IF;
399
400 INSERT INTO GL_LEDGER_SEGMENT_VALUES
401 (LEDGER_ID, SEGMENT_TYPE_CODE, SEGMENT_VALUE, STATUS_CODE,
402 PARENT_RECORD_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
403 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, START_DATE,
404 END_DATE)
405 (SELECT glr.target_ledger_id,gllsv.segment_type_code,
406 gllsv.segment_value, 'I', gllsv.parent_record_id,
407 sysdate,
408 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
409 sysdate,
410 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
411 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
412 gllsv.start_date, gllsv.end_date
413 FROM GL_LEDGERS gll
414 ,GL_LEDGER_RELATIONSHIPS glr
415 ,GL_LEDGER_SEGMENT_VALUES gllsv
416 WHERE gll.chart_of_accounts_id =
417 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
418 AND (gll.bal_seg_value_option_code = 'I' OR
419 gll.mgt_seg_value_option_code = 'I')
420 AND gll.alc_ledger_type_code = 'TARGET'
421 AND glr.target_ledger_id = gll.ledger_id
422 AND glr.target_ledger_category_code = 'ALC'
423 AND glr.relationship_type_code IN ('JOURNAL', 'SUBLEDGER')
424 AND glr.application_id = 101
425 AND gllsv.ledger_id = glr.source_ledger_id
426 AND gllsv.segment_type_code IN
427 (DECODE(gll.bal_seg_value_option_code,'I','B',''),
428 DECODE(gll.mgt_seg_value_option_code,'I','M',''))
429 AND NVL(GLLSV.STATUS_CODE,'X') <> 'D'
430 AND NOT EXISTS
431 (SELECT 1
432 FROM GL_LEDGER_SEGMENT_VALUES gllsv2
433 WHERE gllsv2.ledger_id = glr.target_ledger_id
434 AND gllsv2.segment_type_code = gllsv.SEGMENT_TYPE_CODE
435 AND gllsv2.segment_value = gllsv.segment_value
436 AND NVL(gllsv2.start_date,
437 TO_DATE('01/01/1950','MM/DD/YYYY'))
438 = NVL(gllsv.start_date,
439 TO_DATE('01/01/1950','MM/DD/YYYY'))
440 AND NVL(gllsv2.end_date,
441 TO_DATE('12/31/9999','MM/DD/YYYY'))
442 = NVL(gllsv.end_date,
443 TO_DATE('12/31/9999','MM/DD/YYYY'))));
444
445
446 l_number_of_rows := SQL%ROWCOUNT;
447 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0117',
448 TOKEN_NUM =>2,
449 T1 =>'NUM',
450 V1 =>TO_CHAR(L_NUMBER_OF_ROWS),
451 T2 =>'TABLE',
452 V2 =>'GL_LEDGER_SEGMENT_VALUES');
453 l_number_of_rows := 0;
454
455 -- Check for any date overlap
456
457 IF (NOT ERROR_CHECK) THEN
458
459 RAISE GLSTFL_Fatal_Err;
460
461 END IF;
462
463 FND_CONCURRENT.AF_COMMIT; -- COMMIT POINT
464
465 GL_MESSAGE.FUNC_SUCC(FUNC_NAME =>
466 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa');
467
468 RETURN TRUE;
469
470 EXCEPTION
471
472 WHEN GLSTFL_FATAL_ERR THEN
473
474 GL_MESSAGE.Write_Log(MSG_NAME =>'FLAT0002',
475 TOKEN_NUM => 1,
476 T1 =>'ROUTINE_NAME',
477 V1 =>
478 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa()');
479
480
481 GL_MESSAGE.FUNC_FAIL(FUNC_NAME =>
482 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa');
483
484 FND_CONCURRENT.AF_ROLLBACK; -- ROLLBACK POINT
485
486 RETURN FALSE;
487
488 WHEN OTHERS THEN
489
490 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0203',
491 TOKEN_NUM =>2,
492 T1 =>'FUNCTION',
493 V1 =>'FIX_BY_COA()',
494 T2 =>'SQLERRMC',
495 V2 => SQLERRM);
496
497 GL_MESSAGE.FUNC_FAIL(FUNC_NAME =>
498 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa');
499
500 FND_CONCURRENT.AF_ROLLBACK; -- ROLLBACK POINT
501
502 RETURN FALSE;
503
504 END FIX_BY_COA;
505
506 -- ******************************************************************
507 -- FUNCTION
508 -- FIX_BY_VALUE_SET
509 -- PURPOSE
510 -- This Function is the entry point when flattening program is called in
511 -- SH mode, it indicates changes in segment hierarchy.
512 -- HISTORY
513 -- 06-04-2001 SRINI PALA CREATED
514 -- ARGUMENTS
515
516 -- EXAMPLE
517 -- RET_STATUS := FIX_BY_VALUE_SET()
518 --
519
520 FUNCTION FIX_BY_VALUE_SET RETURN BOOLEAN IS
521
522 L_Number_Of_Rows NUMBER :=0;
523 L_Check_Id NUMBER :=0;
524 GLSTFL_fatal_err EXCEPTION;
525
526 BEGIN
527
528 GL_MESSAGE.FUNC_ENT(FUNC_NAME =>
529 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Value_Set');
530
531 BEGIN
532
533 -- Checking if the value set is used by any ledger(S)
534
535 SELECT 1 INTO L_Check_Id
536 FROM DUAL
537 WHERE EXISTS
538 (SELECT 1
539 FROM GL_LEDGERS GLL
540 WHERE GLL.BAL_SEG_VALUE_SET_ID =
541 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
542 OR GLL.MGT_SEG_VALUE_SET_ID =
543 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
544 AND ROWNUM = 1);
545
546 EXCEPTION
547
548 WHEN NO_DATA_FOUND THEN
549 L_Check_Id := 0;
550
551 END;
552
553 IF (L_Check_Id <> 1) THEN
554 GL_MESSAGE.Write_Log(MSG_NAME =>'FLAT0001',
555 TOKEN_NUM => 1,
556 T1 =>'ROUTINE_NAME',
557 V1 =>
558 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Value_Set()');
559
560 GL_MESSAGE.FUNC_SUCC(FUNC_NAME =>
561 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Value_Set');
562
563 RETURN TRUE;
564
565 ELSE
566
567 -- Cleaning GL_LEDGER_SEGMENT_VALUES before processing
568
569 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_DEBUG) THEN
570 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0180',
571 TOKEN_NUM => 2,
572 T1 =>'ROUTINE',
573 V1 =>
574 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Value_Set()',
575 T2 =>'ACTION',
576 V2 =>'DELETING RECORDS WITH'
577 ||' STATUS CODE I'
578 ||' IN THE TABLE'
579 ||' GL_LEDGER_SEGMENT_VALUES');
580 END IF;
581
582 DELETE
583 FROM GL_LEDGER_SEGMENT_VALUES
584 WHERE STATUS_CODE = 'I'
585 AND LEDGER_ID IN
586 (SELECT LEDGER_ID
587 FROM GL_LEDGERS
588 WHERE BAL_SEG_VALUE_SET_ID =
589 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
590 OR MGT_SEG_VALUE_SET_ID =
591 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID);
592
593 L_Number_Of_Rows := SQL%ROWCOUNT;
594 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0119',
595 TOKEN_NUM =>2,
596 T1 =>'NUM',
597 V1 =>TO_CHAR(L_NUMBER_OF_ROWS),
598 T2 =>'TABLE',
599 V2 =>'GL_LEDGER_SEGMENT_VALUES');
600
601 -- Cleaning GL_LEDGER_SEGMENT_VALUES before processing
602
603 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_DEBUG) THEN
604 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0180',
605 TOKEN_NUM => 2,
606 T1 =>'ROUTINE',
607 V1 =>
608 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Value_Set()',
609 T2 =>'ACTION',
610 V2 =>'UPDATING RECORDS WITH STATUS'
611 ||' CODE D TO NULL IN THE TABLE'
612 ||' GL_LEDGER_SEGMENT_VALUES');
613 END IF;
614
615 UPDATE GL_LEDGER_SEGMENT_VALUES
616 SET STATUS_CODE = NULL
617 WHERE STATUS_CODE = 'D'
618 AND LEDGER_ID IN
619 (SELECT LEDGER_ID
620 FROM GL_LEDGERS
621 WHERE BAL_SEG_VALUE_SET_ID =
622 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
623 OR MGT_SEG_VALUE_SET_ID =
624 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID);
625
626
627 L_Number_Of_Rows := SQL%ROWCOUNT;
628 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0118',
629 TOKEN_NUM =>2,
630 T1 =>'NUM',
631 V1 =>TO_CHAR(L_NUMBER_OF_ROWS),
632 T2 =>'TABLE',
633 V2 =>'GL_LEDGER_SEGMENT_VALUES');
634
635 FND_CONCURRENT.AF_COMMIT; -- COMMIT Point
636
637 -- Marking Parent-Child segment value mappings in
638 -- GL_LEDGER_SEGMENT_VALUES for delete
639
640 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_DEBUG) THEN
641 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0180',
642 TOKEN_NUM => 2,
643 T1 =>'ROUTINE',
644 V1 =>
645 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Value_Set()',
646 T2 =>'ACTION',
647 V2 =>'UPDATING PARENT-CHILD SEGMENT'
648 ||' VALUE MAPPINGS IN'
649 ||' GL_LEDGER_SEGMENT_VALUES'
650 ||' FOR DELETE');
651 END IF;
652
653 UPDATE GL_LEDGER_SEGMENT_VALUES GLLSV
654 SET GLLSV.STATUS_CODE = 'D'
655 WHERE (GLLSV.LEDGER_ID, GLLSV.PARENT_RECORD_ID,
656 GLLSV.SEGMENT_VALUE) IN
657 (SELECT GLLNSV.LEDGER_ID, GLLNSV.RECORD_ID,
658 GLSVH.CHILD_FLEX_VALUE
659 FROM GL_SEG_VAL_HIERARCHIES GLSVH,
660 GL_LEDGER_NORM_SEG_VALS GLLNSV,
661 GL_LEDGERS GLL
662 WHERE GLSVH.FLEX_VALUE_SET_ID =
663 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
664 AND GLSVH.STATUS_CODE = 'D'
665 AND GLLNSV.SEGMENT_VALUE_TYPE_CODE = 'C'
666 AND GLLNSV.STATUS_CODE IS NULL
667 AND GLLNSV.SEGMENT_VALUE =
668 GLSVH.PARENT_FLEX_VALUE
669 AND GLL.LEDGER_ID = GLLNSV.LEDGER_ID
670 AND (
671 ( GLL.BAL_SEG_VALUE_SET_ID =
672 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
673 AND GLLNSV.SEGMENT_TYPE_CODE = 'B')
674 OR
675 ( GLL.MGT_SEG_VALUE_SET_ID =
676 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
677 AND GLLNSV.SEGMENT_TYPE_CODE = 'M')));
678
679 L_Number_Of_Rows := SQL%ROWCOUNT;
680 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0118',
681 TOKEN_NUM =>2,
682 T1 =>'NUM',
683 V1 =>TO_CHAR(L_NUMBER_OF_ROWS),
684 T2 =>'TABLE',
685 V2 =>'GL_LEDGER_SEGMENT_VALUES');
686
687
688
689 -- Inserting New Parent-Child segment value mappings into
690 -- GL_LEDGER_SEGMENT_VALUES.
691
692 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_DEBUG) THEN
693
694 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0180',
695 TOKEN_NUM => 2,
696 T1 =>'ROUTINE',
697 V1 =>
698 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Value_Set()',
699 T2 =>'ACTION',
700 V2 =>'Insert new segment values'
701 ||' from segment value hierarchy'
702 ||' into GL_LEDGER_SEGMENT_VALUES');
703
704
705 END IF;
706
707 INSERT INTO GL_LEDGER_SEGMENT_VALUES
708 (LEDGER_ID, SEGMENT_TYPE_CODE, SEGMENT_VALUE, STATUS_CODE,
709 PARENT_RECORD_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
710 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, START_DATE,
711 END_DATE)
712 (SELECT GLLNSV.LEDGER_ID, GLLNSV.SEGMENT_TYPE_CODE,
713 GLSVH.CHILD_FLEX_VALUE, 'I', GLLNSV.RECORD_ID,
714 SYSDATE,
715 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
716 SYSDATE,
717 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
718 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
719 GLLNSV.START_DATE, GLLNSV.END_DATE
720 FROM GL_SEG_VAL_HIERARCHIES GLSVH,
721 GL_LEDGER_NORM_SEG_VALS GLLNSV,
722 GL_LEDGERS GLL
723 WHERE GLSVH.FLEX_VALUE_SET_ID =
724 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
725 AND GLSVH.STATUS_CODE = 'I'
726 AND GLLNSV.SEGMENT_VALUE_TYPE_CODE = 'C'
727 AND GLLNSV.STATUS_CODE IS NULL
728 AND GLLNSV.SEGMENT_VALUE = GLSVH.PARENT_FLEX_VALUE
729 AND GLL.LEDGER_ID = GLLNSV.LEDGER_ID
730 AND (
731 (GLL.BAL_SEG_VALUE_SET_ID =
732 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
733 AND GLLNSV.SEGMENT_TYPE_CODE = 'B')
734 OR
735 (GLL.MGT_SEG_VALUE_SET_ID =
736 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
737 AND GLLNSV.SEGMENT_TYPE_CODE = 'M')));
738
739
740 L_Number_Of_Rows := SQL%ROWCOUNT;
741 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0117',
742 TOKEN_NUM =>2,
743 T1 =>'NUM',
744 V1 =>TO_CHAR(L_NUMBER_OF_ROWS),
745 T2 =>'TABLE',
746 V2 =>'GL_LEDGER_SEGMENT_VALUES');
747
748 END IF; -- Value set ID If - Else control ends here.
749
750 IF (NOT ERROR_CHECK) THEN
751
752 RAISE GLSTFL_FATAL_ERR;
753
754 END IF;
755
756 FND_CONCURRENT.AF_COMMIT; -- COMMIT Point
757
758 GL_MESSAGE.FUNC_SUCC(FUNC_NAME =>
759 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Value_Set');
760
761 RETURN TRUE;
762
763 EXCEPTION
764
765 WHEN GLSTFL_FATAL_ERR THEN
766
767 GL_MESSAGE.Write_Log(MSG_NAME =>'FLAT0002',
768 TOKEN_NUM => 1,
769 T1 =>'ROUTINE_NAME',
770 V1 => 'Fix_By_Value_Set()');
771
772 GL_MESSAGE.FUNC_FAIL(FUNC_NAME =>
773 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Value_Set');
774
775 FND_CONCURRENT.AF_ROLLBACK; -- ROLLBACK Point
776
777 RETURN FALSE;
778
779 WHEN OTHERS THEN
780
781 GL_MESSAGE.Write_Log (MSG_NAME =>'SHRD0102',
782 TOKEN_NUM => 1,
783 T1 =>'EMESSAGE',
784 V1 => SQLERRM);
785
786 GL_MESSAGE.FUNC_FAIL(FUNC_NAME =>
787 'GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Value_Set');
788
789 FND_CONCURRENT.AF_ROLLBACK;
790
791 RETURN FALSE;
792
793 END FIX_BY_VALUE_SET;
794
795 -- *****************************************************************
796
797 -- FUNCTION
798 -- Clean_Up_By_Coa
799 -- PURPOSE
800 -- This Function is to clean the tables GL_LEDGER_NORM_SEG_VALUES
801 -- and GL_LEDGER_SEGMENT_VALUES for a particular Chart Of Accounts.
802 -- HISTORY
803 -- 06-04-2001 SRINI PALA CREATED
804 -- ARGUMENTS
805
806 -- EXAMPLE
807 -- RET_STATUS := Clean_Up_By_Coa();
808 --
809 --
810
811 FUNCTION Clean_Up_By_Coa RETURN BOOLEAN IS
812
813
814 L_Number_Of_Rows NUMBER :=0;
815 l_status VARCHAR2(1);
816
817 BEGIN
818
819 GL_MESSAGE.FUNC_ENT(FUNC_NAME =>
820 'GL_FLATTEN_LEDGER_SEG_VALS.Clean_Up_By_Coa');
821
822 -- Bug 8265487 -- Should update STATUS_CODE only for completed ledgers
823 UPDATE GL_LEDGER_NORM_SEG_VALS
824 SET STATUS_CODE = NULL, request_id = NULL
825 WHERE STATUS_CODE IN ( 'I','U')
826 AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
827 AND LEDGER_ID IN (SELECT LEDGER_ID FROM GL_LEDGERS
828 WHERE CHART_OF_ACCOUNTS_ID = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
829 AND IMPLICIT_ACCESS_SET_ID IS NOT NULL);
830
831
832 /* AND LEDGER_ID IN
833 (SELECT LEDGER_ID
834 FROM GL_LEDGERS
835 WHERE CHART_OF_ACCOUNTS_ID =
836 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID); */
837
838 L_Number_Of_Rows := SQL%ROWCOUNT;
839 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0118',
840 TOKEN_NUM =>2,
841 T1 =>'NUM',
842 V1 =>TO_CHAR(L_NUMBER_OF_ROWS),
843 T2 => 'TABLE',
844 V2 => 'GL_LEDGER_NORM_SEG_VALS');
845
846 l_number_of_rows := 0;
847
848 UPDATE GL_LEDGER_SEGMENT_VALUES
849 SET STATUS_CODE = NULL
850 WHERE STATUS_CODE = 'I'
851 AND LEDGER_ID IN
852 (SELECT LEDGER_ID
853 FROM GL_LEDGERS
854 WHERE CHART_OF_ACCOUNTS_ID =
855 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
856
857 L_Number_Of_Rows := SQL%ROWCOUNT;
858 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0118',
859 TOKEN_NUM =>2,
860 T1 =>'NUM',
861 V1 =>TO_CHAR(L_NUMBER_OF_ROWS),
862 T2 => 'TABLE',
863 V2 => 'GL_LEDGER_SEGMENT_VALUES');
864 l_number_of_rows := 0;
865
866 -- To improve performance for bug fix # 5075776
867 l_status := 'D';
868
869 DELETE
870 FROM GL_LEDGER_NORM_SEG_VALS
871 WHERE STATUS_CODE = l_status
872 AND LEDGER_ID IN
873 (SELECT LEDGER_ID
874 FROM GL_LEDGERS
875 WHERE CHART_OF_ACCOUNTS_ID =
876 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
877
878 L_Number_Of_Rows := SQL%ROWCOUNT;
879 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0119',
880 TOKEN_NUM => 2,
881 T1 => 'NUM',
882 V1 => TO_CHAR(L_NUMBER_OF_ROWS),
883 T2 =>'TABLE',
884 V2 => 'GL_LEDGER_NORM_SEG_VALS');
885 l_number_of_rows := 0;
886
887 DELETE
888 FROM GL_LEDGER_SEGMENT_VALUES
889 WHERE STATUS_CODE = l_status
890 AND LEDGER_ID IN
891 (SELECT LEDGER_ID
892 FROM GL_LEDGERS
893 WHERE CHART_OF_ACCOUNTS_ID =
894 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
895
896 L_Number_Of_Rows := SQL%ROWCOUNT;
897 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0119',
898 TOKEN_NUM => 2,
899 T1 => 'NUM',
900 V1 => TO_CHAR(L_NUMBER_OF_ROWS),
901 T2 =>'TABLE',
902 V2 => 'GL_LEDGER_SEGMENT_VALUES');
903 l_number_of_rows := 0;
904
905 GL_MESSAGE.FUNC_SUCC(FUNC_NAME =>
906 'GL_FLATTEN_LEDGER_SEG_VALS.Clean_Up_By_Coa');
907
908 RETURN TRUE;
909
910 EXCEPTION
911
912 WHEN OTHERS THEN
913 GL_MESSAGE.Write_Log (MSG_NAME =>'SHRD0102',
914 TOKEN_NUM => 1,
915 T1 =>'EMESSAGE',
916 V1 => SQLERRM);
917
918 GL_MESSAGE.FUNC_FAIL(FUNC_NAME =>
919 'GL_FLATTEN_LEDGER_SEG_VALS.Clean_Up_By_Coa');
920
921 FND_CONCURRENT.AF_ROLLBACK; -- ROLLBACK Point
922
923 RETURN FALSE;
924
925 END CLEAN_UP_BY_COA;
926
927 -- ******************************************************************
928
929 -- FUNCTION
930 -- Clean_Up_By_Value_Set
931 -- PURPOSE
932 -- This Function is to clean the tables GL_LEDGER_NORM_SEG_VALUES
933 -- and GL_LEDGER_SEGMENT_VALUES for a particular value set.
934 -- HISTORY
935 -- 06-04-2001 Srini Pala Created
936 -- ARGUMENTS
937
938 -- EXAMPLE
939 -- RET_STATUS := Clean_Up_By_Value_Set();
940 --
941
942 FUNCTION CLEAN_UP_BY_VALUE_SET RETURN BOOLEAN IS
943
944 L_Number_Of_Rows NUMBER :=0;
945
946 BEGIN
947
948 GL_MESSAGE.FUNC_ENT(FUNC_NAME =>
949 'GL_FLATTEN_LEDGER_SEG_VALS.Clean_Up_By_Value_Set');
950
951 UPDATE GL_LEDGER_SEGMENT_VALUES
952 SET STATUS_CODE = NULL
953 WHERE STATUS_CODE = 'I'
954 AND LEDGER_ID IN
955 (SELECT LEDGER_ID
956 FROM GL_LEDGERS
957 WHERE BAL_SEG_VALUE_SET_ID =
958 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
959 OR MGT_SEG_VALUE_SET_ID =
960 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID);
961
962 L_Number_Of_Rows := SQL%ROWCOUNT;
963 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0118',
964 TOKEN_NUM =>2,
965 T1 =>'NUM',
966 V1 =>TO_CHAR(L_NUMBER_OF_ROWS),
967 T2 => 'TABLE',
968 V2 => 'GL_LEDGER_SEGMENT_VALUES');
969
970 DELETE
971 FROM GL_LEDGER_SEGMENT_VALUES
972 WHERE STATUS_CODE = 'D'
973 AND LEDGER_ID IN
974 (SELECT LEDGER_ID
975 FROM GL_LEDGERS
976 WHERE BAL_SEG_VALUE_SET_ID =
977 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
978 OR MGT_SEG_VALUE_SET_ID =
979 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID);
980
981 L_Number_Of_Rows := SQL%ROWCOUNT;
982 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0119',
983 TOKEN_NUM => 2,
984 T1 => 'NUM',
985 V1 => TO_CHAR(L_NUMBER_OF_ROWS),
986 T2 =>'TABLE',
987 V2 => 'GL_LEDGER_SEGMENT_VALUES');
988
989
990 GL_MESSAGE.FUNC_SUCC(FUNC_NAME =>
991 'GL_FLATTEN_LEDGER_SEG_VALS.Clean_Up_By_Value_Set');
992
993 RETURN TRUE;
994
995 EXCEPTION
996
997 WHEN OTHERS THEN
998
999 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0102',
1000 TOKEN_NUM => 1,
1001 T1 =>'EMESSAGE',
1002 V1 => SQLERRM);
1003
1004 GL_MESSAGE.FUNC_FAIL(FUNC_NAME =>
1005 'GL_FLATTEN_LEDGER_SEG_VALS.Clean_Up_By_Value_Set');
1006
1007 FND_CONCURRENT.AF_ROLLBACK; -- ROLLBACK Point
1008
1009 RETURN FALSE;
1010
1011 END Clean_Up_By_Value_Set;
1012
1013
1014 -- ******************************************************************
1015 -- FUNCTION
1016 -- ERROR_CHECK
1017 -- PURPOSE
1018 -- This function checks if a segment value has been assigned to a
1019 -- particular ledger more than once on a given date range.
1020 -- If it returns FALSE then the package should error out
1021 -- HISTORY
1022 -- 06-04-2001 SRINI PALA CREATED
1023 -- ARGUMENTS
1024 --
1025 -- EXAMPLE
1026 -- RET_STATUS := ERROR_CHECK();
1027 --
1028
1029 FUNCTION ERROR_CHECK RETURN BOOLEAN IS
1030
1031 L_Ledger_Id NUMBER :=0;
1032
1033 L_Segment_Val VARCHAR2(25);
1034
1035 L_Ledger_Name VARCHAR2(30);
1036
1037 BEGIN
1038
1039 GL_MESSAGE.FUNC_ENT(FUNC_NAME =>
1040 'GL_FLATTEN_LEDGER_SEG_VALS.Error_Check');
1041
1042 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE = 'VH') OR
1043 (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE = 'LV') THEN
1044
1045 -- Separate PL/SQL block for opearation Mode 'VH'
1046
1047 DECLARE
1048
1049 CURSOR Cursor_LV_Ledger IS
1050 SELECT DISTINCT GLLSV1.LEDGER_ID, GLLSV2.SEGMENT_VALUE
1051 FROM GL_LEDGERS GLL,
1052 GL_LEDGER_SEGMENT_VALUES GLLSV1,
1053 GL_LEDGER_SEGMENT_VALUES GLLSV2
1054 WHERE GLL.CHART_OF_ACCOUNTS_ID =
1055 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
1056 AND GLLSV1.LEDGER_ID = GLL.LEDGER_ID
1057 AND GLLSV1.LEDGER_ID = GLLSV2.LEDGER_ID
1058 AND GLLSV1.SEGMENT_TYPE_CODE = GLLSV2.SEGMENT_TYPE_CODE
1059 AND NVL(GLLSV1.STATUS_CODE,'X') <>'D'
1060 AND NVL(GLLSV2.STATUS_CODE,'X') <>'D'
1061 AND GLLSV1.SEGMENT_VALUE = GLLSV2.SEGMENT_VALUE
1062 AND GLLSV1.ROWID <>GLLSV2.ROWID
1063 AND ( NVL(GLLSV1.START_DATE,
1064 TO_DATE('01/01/1950', 'MM/DD/YYYY'))
1065 BETWEEN NVL(GLLSV2.START_DATE,
1066 TO_DATE('01/01/1950','MM/DD/YYYY'))
1067 AND NVL(GLLSV2.END_DATE,
1068 TO_DATE('12/31/9999','MM/DD/YYYY'))
1069 OR NVL(GLLSV1.END_DATE,
1070 TO_DATE('12/31/9999','MM/DD/YYYY'))
1071 BETWEEN NVL(GLLSV2.START_DATE,
1072 TO_DATE('01/01/1950','MM/DD/YYYY'))
1073 AND NVL(GLLSV2.END_DATE,
1074 TO_DATE('12/31/9999','MM/DD/YYYY')));
1075 BEGIN
1076
1077 IF (NOT Cursor_LV_Ledger%ISOPEN) THEN
1078
1079 OPEN Cursor_LV_Ledger;
1080
1081 END IF;
1082
1083 LOOP
1084
1085 FETCH Cursor_LV_Ledger INTO L_Ledger_Id, L_Segment_Val;
1086
1087 EXIT WHEN Cursor_LV_Ledger%NOTFOUND;
1088
1089 IF (L_SEGMENT_VAL IS NOT NULL) THEN
1090
1091 SELECT NAME INTO L_LEDGER_NAME
1092 FROM GL_LEDGERS
1093 WHERE LEDGER_ID = L_LEDGER_ID;
1094
1095 GL_MESSAGE.Write_Log(MSG_NAME =>'FLAT0003',
1096 TOKEN_NUM => 2,
1097 T1 =>'SEGMENT_VALUE',
1098 V1 =>L_SEGMENT_VAL,
1099 T2 =>'LEDGER_NAME',
1100 V2 =>L_LEDGER_NAME);
1101
1102 END IF;
1103
1104 END LOOP;
1105
1106 IF (Cursor_Lv_Ledger%ROWCOUNT >= 1) THEN
1107
1108 GL_MESSAGE.FUNC_FAIL(FUNC_NAME =>
1109 'GL_FLATTEN_LEDGER_SEG_VALS.Error_Check');
1110
1111 RETURN FALSE;
1112
1113 END IF;
1114
1115 CLOSE Cursor_LV_Ledger;
1116
1117 EXCEPTION
1118
1119 WHEN OTHERS THEN
1120
1121 RETURN FALSE;
1122
1123 END; -- VH mode opearation PL/SQL block ends
1124
1125 END IF; -- VH Opearation mode If control block ends here.
1126
1127 IF ((GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE = 'FF')
1128 OR (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE = 'SH')) THEN
1129
1130 DECLARE
1131
1132 CURSOR Cursor_SH_Ledger IS
1133 SELECT GLLSV1.LEDGER_ID, GLLSV2.SEGMENT_VALUE
1134 FROM GL_LEDGERS GLL,
1135 GL_LEDGER_SEGMENT_VALUES GLLSV1,
1136 GL_LEDGER_SEGMENT_VALUES GLLSV2
1137 WHERE ( GLL.BAL_SEG_VALUE_SET_ID =
1138 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
1139 OR GLL.MGT_SEG_VALUE_SET_ID =
1140 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID)
1141 AND GLLSV1.LEDGER_ID = GLL.LEDGER_ID
1142 AND GLLSV1.LEDGER_ID = GLLSV2.LEDGER_ID
1143 AND GLLSV1.SEGMENT_TYPE_CODE = GLLSV2.SEGMENT_TYPE_CODE
1144 AND NVL(GLLSV1.STATUS_CODE,'X') <>'D'
1145 AND NVL(GLLSV2.STATUS_CODE,'X') <>'D'
1146 AND GLLSV1.SEGMENT_VALUE = GLLSV2.SEGMENT_VALUE
1147 AND GLLSV1.ROWID <>GLLSV2.ROWID
1148 AND ( NVL(GLLSV1.START_DATE,
1149 TO_DATE('01/01/1950', 'MM/DD/YYYY'))
1150 BETWEEN NVL(GLLSV2.START_DATE,
1151 TO_DATE('01/01/1950','MM/DD/YYYY'))
1152 AND NVL(GLLSV2.END_DATE,
1153 TO_DATE('12/31/9999','MM/DD/YYYY'))
1154 OR NVL(GLLSV1.END_DATE,
1155 TO_DATE('12/31/9999','MM/DD/YYYY'))
1156 BETWEEN NVL(GLLSV2.START_DATE,
1157 TO_DATE('01/01/1950','MM/DD/YYYY'))
1158 AND NVL(GLLSV2.END_DATE,
1159 TO_DATE('12/31/9999','MM/DD/YYYY')));
1160 BEGIN
1161
1162 IF (NOT Cursor_SH_Ledger%ISOPEN) THEN
1163
1164 OPEN Cursor_SH_Ledger;
1165
1166 END IF;
1167
1168 LOOP
1169
1170 FETCH Cursor_SH_Ledger INTO L_Ledger_Id, L_Segment_Val;
1171
1172 EXIT WHEN Cursor_SH_Ledger%NOTFOUND;
1173
1174 IF (L_Segment_Val IS NOT NULL) THEN
1175
1176 SELECT NAME INTO L_LEDGER_NAME
1177 FROM GL_LEDGERS
1178 WHERE LEDGER_ID = L_LEDGER_ID;
1179
1180 GL_MESSAGE.Write_Log(MSG_NAME =>'FLAT0003',
1181 TOKEN_NUM => 2,
1182 T1 =>'SEGMENT_VALUE',
1183 V1 => L_Segment_Val,
1184 T2 =>'LEDGER_NAME',
1185 V2 =>L_Ledger_Name);
1186
1187 END IF;
1188
1189 END LOOP;
1190
1191 IF (Cursor_SH_Ledger%ROWCOUNT >= 1) THEN
1192
1193 GL_MESSAGE.FUNC_FAIL(FUNC_NAME =>
1194 'GL_FLATTEN_LEDGER_SEG_VALS.Error_Check');
1195
1196 RETURN FALSE;
1197
1198 END IF;
1199
1200 CLOSE Cursor_SH_Ledger;
1201
1202 EXCEPTION
1203
1204 WHEN OTHERS THEN
1205
1206 GL_MESSAGE.FUNC_FAIL(FUNC_NAME =>
1207 'GL_FLATTEN_LEDGER_SEG_VALS.Error_Check');
1208
1209 RETURN FALSE;
1210
1211 END; -- 'SH' and 'FF' mode PL/SQL block ends
1212
1213 END IF; -- 'SH' and 'FF' Opearation mode If control block ends here.
1214
1215 GL_MESSAGE.FUNC_SUCC(FUNC_NAME =>
1216 'GL_FLATTEN_LEDGER_SEG_VALS.Error_Check');
1217
1218 RETURN TRUE;
1219
1220 EXCEPTION
1221
1222 WHEN NO_DATA_FOUND THEN
1223
1224 GL_MESSAGE.FUNC_SUCC(FUNC_NAME =>
1225 'GL_FLATTEN_LEDGER_SEG_VALS.Error_Check');
1226
1227 RETURN TRUE;
1228
1229 WHEN OTHERS THEN
1230
1231 GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0102',
1232 TOKEN_NUM => 1,
1233 T1 =>'EMESSAGE',
1234 V1 => SQLERRM);
1235
1236 GL_MESSAGE.FUNC_FAIL(FUNC_NAME =>
1237 'GL_FLATTEN_LEDGER_SEG_VALS.Error_Check');
1238
1239 RETURN FALSE;
1240
1241 END ERROR_CHECK;
1242
1243 END GL_FLATTEN_LEDGER_SEG_VALS;