[Home] [Help]
11: BEGIN
12:
13: -- This is the routine that processes changes in explicit ledger
14: -- sets. The basic flow is as follows:
15: -- 1) Clean up GL_LEDGER_SET_ASSIGNMENTS
16: -- 2) Clean up GL_ACCESS_SET_NORM_ASSIGN
17: -- 3) For all newly created ledger sets, populate implicit access set
18: -- information into GL_ACCESS_SET_NORM_ASSIGN
19: -- 4) Mark all outdated mappings in GL_LEDGER_SET_ASSIGNMENTS for
15: -- 1) Clean up GL_LEDGER_SET_ASSIGNMENTS
16: -- 2) Clean up GL_ACCESS_SET_NORM_ASSIGN
17: -- 3) For all newly created ledger sets, populate implicit access set
18: -- information into GL_ACCESS_SET_NORM_ASSIGN
19: -- 4) Mark all outdated mappings in GL_LEDGER_SET_ASSIGNMENTS for
20: -- delete.
21: -- 5) Insert new mappings into GL_LEDGER_SET_ASSIGNMENTS
22: -- 6) Check if looping exists in the ledger set assignments. If so,
23: -- error out.
17: -- 3) For all newly created ledger sets, populate implicit access set
18: -- information into GL_ACCESS_SET_NORM_ASSIGN
19: -- 4) Mark all outdated mappings in GL_LEDGER_SET_ASSIGNMENTS for
20: -- delete.
21: -- 5) Insert new mappings into GL_LEDGER_SET_ASSIGNMENTS
22: -- 6) Check if looping exists in the ledger set assignments. If so,
23: -- error out.
24:
25: GL_MESSAGE.Func_Ent
24:
25: GL_MESSAGE.Func_Ent
26: (func_name => 'GL_FLATTEN_LEDGER_SETS.Fix_Explicit_Sets');
27:
28: -- Clean up the GL_LEDGER_SET_ASSIGNMENTS table for any
29: -- unprocessed data left over from previous failed run
30:
31: IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
32: GL_MESSAGE.Write_Log
35: t1 => 'ROUTINE',
36: v1 => 'Fix_Explicit_Sets()',
37: t2 => 'ACTION',
38: v2 => 'Deleting records with status code I in ' ||
39: 'GL_LEDGER_SET_ASSIGNMENTS...');
40: END IF;
41:
42: DELETE from GL_LEDGER_SET_ASSIGNMENTS
43: WHERE status_code = 'I'
38: v2 => 'Deleting records with status code I in ' ||
39: 'GL_LEDGER_SET_ASSIGNMENTS...');
40: END IF;
41:
42: DELETE from GL_LEDGER_SET_ASSIGNMENTS
43: WHERE status_code = 'I'
44: AND ledger_set_id IN
45: (SELECT ledger_id
46: FROM GL_LEDGERS
52: token_num => 2,
53: t1 => 'NUM',
54: v1 => TO_CHAR(row_count),
55: t2 => 'TABLE',
56: v2 => 'GL_LEDGER_SET_ASSIGNMENTS');
57: row_count := 0;
58:
59: IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
60: GL_MESSAGE.Write_Log
63: t1 => 'ROUTINE',
64: v1 => 'Fix_Explicit_Sets()',
65: t2 => 'ACTION',
66: v2 => 'Updating records with status code D in ' ||
67: 'GL_LEDGER_SET_ASSIGNMENTS...');
68: END IF;
69:
70: UPDATE GL_LEDGER_SET_ASSIGNMENTS
71: SET status_code = NULL
66: v2 => 'Updating records with status code D in ' ||
67: 'GL_LEDGER_SET_ASSIGNMENTS...');
68: END IF;
69:
70: UPDATE GL_LEDGER_SET_ASSIGNMENTS
71: SET status_code = NULL
72: WHERE status_code = 'D'
73: AND ledger_set_id IN
74: (SELECT ledger_id
82: token_num => 2,
83: t1 => 'NUM',
84: v1 => TO_CHAR(row_count),
85: t2 => 'TABLE',
86: v2 => 'GL_LEDGER_SET_ASSIGNMENTS');
87: row_count := 0;
88:
89: -- Delete from GL_ACCESS_SET_NORM_ASSIGN for any new assignments
90: -- created for the implicit access sets associated with
223: -- This section of code will mark any outdated ledger set/ledger
224: -- mappings for delete.
225: -- Here is the sequence of events:
226: -- 1) For all records in GL_LEDGER_SET_NORM_ASSIGN with a status_code
227: -- of 'D', go into GL_LEDGER_SET_ASSIGNMENTS and determine all
228: -- records that contain the deleted mappings, and mark them for
229: -- delete as well.
230: -- 2) For any ledger sets that are deleted, mark all of their
231: -- descendants for delete as well.
239: t1 => 'ROUTINE',
240: v1 => 'Fix_Explicit_Sets()',
241: t2 => 'ACTION',
242: v2 => 'Marking outdated ledger set/ledger mappings ' ||
243: 'in GL_LEDGER_SET_ASSIGNMENTS for delete...');
244:
245: GL_MESSAGE.Write_Log
246: (msg_name => 'SHRD0180',
247: token_num => 2,
249: v1 => 'Fix_Explicit_Sets()',
250: t2 => 'ACTION',
251: v2 => 'First, mark all ascendants containing the ' ||
252: 'deleted links ' ||
253: 'for delete in GL_LEDGER_SET_ASSIGNMENTS...');
254: END IF;
255:
256: UPDATE GL_LEDGER_SET_ASSIGNMENTS gllsa1
257: SET gllsa1.status_code = 'D'
252: 'deleted links ' ||
253: 'for delete in GL_LEDGER_SET_ASSIGNMENTS...');
254: END IF;
255:
256: UPDATE GL_LEDGER_SET_ASSIGNMENTS gllsa1
257: SET gllsa1.status_code = 'D'
258: WHERE NVL(gllsa1.status_code, 'X') <> 'D'
259: AND (gllsa1.ledger_set_id, gllsa1.ledger_id) IN
260: (SELECT distinct gllsa2.ledger_set_id, gllsna.ledger_id
259: AND (gllsa1.ledger_set_id, gllsa1.ledger_id) IN
260: (SELECT distinct gllsa2.ledger_set_id, gllsna.ledger_id
261: FROM GL_LEDGER_SET_NORM_ASSIGN gllsna,
262: GL_LEDGERS gll,
263: GL_LEDGER_SET_ASSIGNMENTS gllsa2
264: WHERE gllsna.status_code = 'D'
265: AND gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
266: AND gll.ledger_id = gllsna.ledger_set_id
267: AND gll.chart_of_accounts_id =
276: token_num => 2,
277: t1 => 'NUM',
278: v1 => TO_CHAR(row_count),
279: t2 => 'TABLE',
280: v2 => 'GL_LEDGER_SET_ASSIGNMENTS');
281: row_count := 0;
282:
283: IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
284: GL_MESSAGE.Write_Log
287: t1 => 'ROUTINE',
288: v1 => 'Fix_Explicit_Sets()',
289: t2 => 'ACTION',
290: v2 => 'Second, mark descendants of deleted ledger sets ' ||
291: 'for delete in GL_LEDGER_SET_ASSIGNMENTS...');
292: END IF;
293:
294: UPDATE GL_LEDGER_SET_ASSIGNMENTS gllsa1
295: SET gllsa1.status_code = 'D'
290: v2 => 'Second, mark descendants of deleted ledger sets ' ||
291: 'for delete in GL_LEDGER_SET_ASSIGNMENTS...');
292: END IF;
293:
294: UPDATE GL_LEDGER_SET_ASSIGNMENTS gllsa1
295: SET gllsa1.status_code = 'D'
296: WHERE NVL(gllsa1.status_code, 'X') <> 'D'
297: AND (gllsa1.ledger_set_id, gllsa1.ledger_id) IN
298: (SELECT distinct gllsa2.ledger_set_id, gllsa3.ledger_id
297: AND (gllsa1.ledger_set_id, gllsa1.ledger_id) IN
298: (SELECT distinct gllsa2.ledger_set_id, gllsa3.ledger_id
299: FROM GL_LEDGER_SET_NORM_ASSIGN gllsna,
300: GL_LEDGERS gll,
301: GL_LEDGER_SET_ASSIGNMENTS gllsa2,
302: GL_LEDGER_SET_ASSIGNMENTS gllsa3
303: WHERE gllsna.status_code = 'D'
304: AND gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
305: AND gll.ledger_id = gllsna.ledger_set_id
298: (SELECT distinct gllsa2.ledger_set_id, gllsa3.ledger_id
299: FROM GL_LEDGER_SET_NORM_ASSIGN gllsna,
300: GL_LEDGERS gll,
301: GL_LEDGER_SET_ASSIGNMENTS gllsa2,
302: GL_LEDGER_SET_ASSIGNMENTS gllsa3
303: WHERE gllsna.status_code = 'D'
304: AND gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
305: AND gll.ledger_id = gllsna.ledger_set_id
306: AND gll.chart_of_accounts_id =
316: token_num => 2,
317: t1 => 'NUM',
318: v1 => TO_CHAR(row_count),
319: t2 => 'TABLE',
320: v2 => 'GL_LEDGER_SET_ASSIGNMENTS');
321: row_count := 0;
322:
323: -- Commit changes so far before going into a loop
324: FND_CONCURRENT.Af_Commit;
330: t1 => 'ROUTINE',
331: v1 => 'Fix_Explicit_Sets()',
332: t2 => 'ACTION',
333: v2 => 'Third, reconnect all deleted mappings in ' ||
334: 'GL_LEDGER_SET_ASSIGNMENTS that are included via ' ||
335: 'other effective paths...');
336: END IF;
337:
338: WHILE NOT stop_processing
336: END IF;
337:
338: WHILE NOT stop_processing
339: LOOP
340: UPDATE GL_LEDGER_SET_ASSIGNMENTS gllsa1
341: SET gllsa1.status_code = NULL
342: WHERE gllsa1.status_code = 'D'
343: AND gllsa1.ledger_set_id IN
344: (SELECT gll.ledger_id
348: AND gll.object_type_code = 'S'
349: AND gll.automatically_created_flag = 'N')
350: AND ( EXISTS
351: (SELECT 1
352: FROM GL_LEDGER_SET_ASSIGNMENTS gllsa2,
353: GL_LEDGER_SET_ASSIGNMENTS gllsa3
354: WHERE gllsa2.status_code is NULL
355: AND gllsa2.ledger_id = gllsa1.ledger_id
356: AND gllsa3.status_code is NULL
349: AND gll.automatically_created_flag = 'N')
350: AND ( EXISTS
351: (SELECT 1
352: FROM GL_LEDGER_SET_ASSIGNMENTS gllsa2,
353: GL_LEDGER_SET_ASSIGNMENTS gllsa3
354: WHERE gllsa2.status_code is NULL
355: AND gllsa2.ledger_id = gllsa1.ledger_id
356: AND gllsa3.status_code is NULL
357: AND gllsa3.ledger_set_id = gllsa1.ledger_set_id
373: token_num => 2,
374: t1 => 'NUM',
375: v1 => TO_CHAR(row_count),
376: t2 => 'TABLE',
377: v2 => 'GL_LEDGER_SET_ASSIGNMENTS');
378:
379: row_count := 0;
380:
381: -- This section of the code will insert new ledger set/ledger
378:
379: row_count := 0;
380:
381: -- This section of the code will insert new ledger set/ledger
382: -- mappings into GL_LEDGER_SET_ASSIGNMENTS.
383: -- Here is the sequence of events:
384: -- 1) Insert a self mapping record for each new ledger set.
385: -- 2) Add all newly added ledgers to the respective ledger sets.
386: -- 3) Insert mappings for all descendants of newly added child ledger
393: t1 => 'ROUTINE',
394: v1 => 'Fix_Explicit_Sets()',
395: t2 => 'ACTION',
396: v2 => 'Inserting self mapping record for new ledger sets ' ||
397: 'into GL_LEDGER_SET_ASSIGNMENTS');
398: END IF;
399:
400: INSERT INTO GL_LEDGER_SET_ASSIGNMENTS
401: (ledger_set_id, ledger_id, status_code, last_update_date,
396: v2 => 'Inserting self mapping record for new ledger sets ' ||
397: 'into GL_LEDGER_SET_ASSIGNMENTS');
398: END IF;
399:
400: INSERT INTO GL_LEDGER_SET_ASSIGNMENTS
401: (ledger_set_id, ledger_id, status_code, last_update_date,
402: last_updated_by, last_update_login, creation_date,
403: created_by, start_date, end_date)
404: (SELECT distinct gll.ledger_id, gll.ledger_id, 'I', SYSDATE,
411: AND gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
412: AND gll.automatically_created_flag = 'N'
413: AND NOT EXISTS
414: (SELECT 1
415: FROM GL_LEDGER_SET_ASSIGNMENTS gllsa
416: WHERE gllsa.ledger_set_id = gll.ledger_id
417: AND gllsa.ledger_id = gll.ledger_id
418: AND NVL(gllsa.status_code, 'X') <> 'D'));
419:
422: token_num => 2,
423: t1 => 'NUM',
424: v1 => TO_CHAR(row_count),
425: t2 => 'TABLE',
426: v2 => 'GL_LEDGER_SET_ASSIGNMENTS');
427: row_count := 0;
428:
429: IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
430: GL_MESSAGE.Write_Log
433: t1 => 'ROUTINE',
434: v1 => 'Fix_Explicit_Sets()',
435: t2 => 'ACTION',
436: v2 => 'Inserting new ledgers to the respective ' ||
437: 'ledger sets into GL_LEDGER_SET_ASSIGNMENTS...');
438: END IF;
439:
440: INSERT INTO GL_LEDGER_SET_ASSIGNMENTS
441: (ledger_set_id, ledger_id, status_code, last_update_date,
436: v2 => 'Inserting new ledgers to the respective ' ||
437: 'ledger sets into GL_LEDGER_SET_ASSIGNMENTS...');
438: END IF;
439:
440: INSERT INTO GL_LEDGER_SET_ASSIGNMENTS
441: (ledger_set_id, ledger_id, status_code, last_update_date,
442: last_updated_by, last_update_login, creation_date,
443: created_by, start_date, end_date)
444: (SELECT distinct gllsa.ledger_set_id, gllsna.ledger_id, 'I', SYSDATE,
447: SYSDATE, GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
448: NULL, NULL
449: FROM GL_LEDGER_SET_NORM_ASSIGN gllsna,
450: GL_LEDGERS gll,
451: GL_LEDGER_SET_ASSIGNMENTS gllsa
452: WHERE gllsna.status_code = 'I'
453: AND gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
454: AND gll.ledger_id = gllsna.ledger_id
455: AND gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
457: AND gllsa.ledger_id = gllsna.ledger_set_id
458: AND NVL(gllsa.status_code, 'X') <> 'D'
459: AND NOT EXISTS
460: (SELECT 1
461: FROM GL_LEDGER_SET_ASSIGNMENTS gllsa2
462: WHERE gllsa2.ledger_set_id = gllsa.ledger_set_id
463: AND gllsa2.ledger_id = gllsna.ledger_id
464: AND NVL(gllsa2.status_code, 'X') <> 'D'));
465:
468: token_num => 2,
469: t1 => 'NUM',
470: v1 => TO_CHAR(row_count),
471: t2 => 'TABLE',
472: v2 => 'GL_LEDGER_SET_ASSIGNMENTS');
473: row_count := 0;
474:
475: -- Commit before going into a loop
476: FND_CONCURRENT.Af_Commit;
484: t1 => 'ROUTINE',
485: v1 => 'Fix_Explicit_Sets()',
486: t2 => 'ACTION',
487: v2 => 'Inserting all descendants of new ledger sets into ' ||
488: 'GL_LEDGER_SET_ASSIGNMENTS...');
489: END IF;
490:
491: WHILE NOT stop_processing
492: LOOP
489: END IF;
490:
491: WHILE NOT stop_processing
492: LOOP
493: INSERT INTO GL_LEDGER_SET_ASSIGNMENTS
494: (ledger_set_id, ledger_id, status_code, last_update_date,
495: last_updated_by, last_update_login, creation_date,
496: created_by, start_date, end_date)
497: (SELECT distinct gllsa1.ledger_set_id, gllsa2.ledger_id,
500: GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
501: NULL, NULL
502: FROM GL_LEDGER_SET_NORM_ASSIGN gllsna,
503: GL_LEDGERS gll,
504: GL_LEDGER_SET_ASSIGNMENTS gllsa1,
505: GL_LEDGER_SET_ASSIGNMENTS gllsa2
506: WHERE gllsna.status_code = 'I'
507: AND gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
508: AND gll.ledger_id = gllsna.ledger_set_id
501: NULL, NULL
502: FROM GL_LEDGER_SET_NORM_ASSIGN gllsna,
503: GL_LEDGERS gll,
504: GL_LEDGER_SET_ASSIGNMENTS gllsa1,
505: GL_LEDGER_SET_ASSIGNMENTS gllsa2
506: WHERE gllsna.status_code = 'I'
507: AND gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
508: AND gll.ledger_id = gllsna.ledger_set_id
509: AND gll.chart_of_accounts_id =
515: AND gllsa2.ledger_set_id = gllsna.ledger_id
516: AND NVL(gllsa2.status_code, 'X') <> 'D'
517: AND NOT EXISTS
518: (SELECT 1
519: FROM GL_LEDGER_SET_ASSIGNMENTS gllsa3
520: WHERE gllsa3.ledger_set_id = gllsa1.ledger_set_id
521: AND gllsa3.ledger_id = gllsa2.ledger_id
522: AND NVL(gllsa3.status_code, 'X') <> 'D'));
523:
531: token_num => 2,
532: t1 => 'NUM',
533: v1 => TO_CHAR(row_count),
534: t2 => 'TABLE',
535: v2 => 'GL_LEDGER_SET_ASSIGNMENTS');
536:
537: BEGIN
538: SELECT 1
539: INTO loop_exists
539: INTO loop_exists
540: FROM DUAL
541: WHERE EXISTS
542: (SELECT 1
543: FROM GL_LEDGER_SET_ASSIGNMENTS gllsa1,
544: GL_LEDGERS gll,
545: GL_LEDGER_SET_ASSIGNMENTS gllsa2
546: WHERE gllsa1.status_code = 'I'
547: AND gll.ledger_id = gllsa1.ledger_set_id
541: WHERE EXISTS
542: (SELECT 1
543: FROM GL_LEDGER_SET_ASSIGNMENTS gllsa1,
544: GL_LEDGERS gll,
545: GL_LEDGER_SET_ASSIGNMENTS gllsa2
546: WHERE gllsa1.status_code = 'I'
547: AND gll.ledger_id = gllsa1.ledger_set_id
548: AND gll.chart_of_accounts_id =
549: GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
621: GL_MESSAGE.Func_Ent
622: (func_name => 'GL_FLATTEN_LEDGER_SETS.Clean_Up_Explicit_Sets');
623:
624: -- Run the following statements to clean up both
625: -- GL_LEDGER_SET_NORM_ASSIGN and GL_LEDGER_SET_ASSIGNMENTS
626:
627: IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
628: GL_MESSAGE.Write_Log
629: (msg_name => 'SHRD0180',
694: token_num => 2,
695: t1 => 'ROUTINE',
696: v1 => 'Clean_Up_Explicit_Sets()',
697: t2 => 'ACTION',
698: v2 => 'Deleting records from GL_LEDGER_SET_ASSIGNMENTS...');
699: END IF;
700:
701: -- Delete records from GL_LEDGER_SET_ASSIGNMENTS
702:
697: t2 => 'ACTION',
698: v2 => 'Deleting records from GL_LEDGER_SET_ASSIGNMENTS...');
699: END IF;
700:
701: -- Delete records from GL_LEDGER_SET_ASSIGNMENTS
702:
703: DELETE from GL_LEDGER_SET_ASSIGNMENTS
704: WHERE status_code = 'D'
705: AND ledger_set_id IN
699: END IF;
700:
701: -- Delete records from GL_LEDGER_SET_ASSIGNMENTS
702:
703: DELETE from GL_LEDGER_SET_ASSIGNMENTS
704: WHERE status_code = 'D'
705: AND ledger_set_id IN
706: (SELECT ledger_id
707: FROM GL_LEDGERS
714: token_num => 2,
715: t1 => 'NUM',
716: v1 => TO_CHAR(row_count),
717: t2 => 'TABLE',
718: v2 => 'GL_LEDGER_SET_ASSIGNMENTS');
719: row_count := 0;
720:
721: IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
722: GL_MESSAGE.Write_log
724: token_num => 2,
725: t1 => 'ROUTINE',
726: v1 => 'Clean_Up_Explicit_Sets()',
727: t2 => 'ACTION',
728: v2 => 'Updating records in GL_LEDGER_SET_ASSIGNMENTS...');
729: END IF;
730:
731: -- Update records in GL_LEDGER_SET_ASSIGNMENTS
732: -- Bear in mind there will never be U records in gllsa
727: t2 => 'ACTION',
728: v2 => 'Updating records in GL_LEDGER_SET_ASSIGNMENTS...');
729: END IF;
730:
731: -- Update records in GL_LEDGER_SET_ASSIGNMENTS
732: -- Bear in mind there will never be U records in gllsa
733:
734: UPDATE GL_LEDGER_SET_ASSIGNMENTS
735: SET status_code = NULL
730:
731: -- Update records in GL_LEDGER_SET_ASSIGNMENTS
732: -- Bear in mind there will never be U records in gllsa
733:
734: UPDATE GL_LEDGER_SET_ASSIGNMENTS
735: SET status_code = NULL
736: WHERE status_code = 'I'
737: AND ledger_set_id IN
738: (SELECT ledger_id
746: token_num => 2,
747: t1 => 'NUM',
748: v1 => TO_CHAR(row_count),
749: t2 => 'TABLE',
750: v2 => 'GL_LEDGER_SET_ASSIGNMENTS');
751:
752: GL_MESSAGE.Func_Succ
753: (func_name => 'GL_FLATTEN_LEDGER_SETS.Clean_Up_Explicit_Sets');
754: