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