[Home] [Help]
PACKAGE BODY: APPS.GL_FLATTEN_LEDGER_SETS
Source
1 PACKAGE BODY GL_FLATTEN_LEDGER_SETS AS
2 /* $Header: glufllsb.pls 120.9 2005/05/05 01:38:15 kvora ship $ */
3
4 -- ********************************************************************
5
6 FUNCTION Fix_Explicit_Sets RETURN BOOLEAN IS
7 row_count NUMBER := 0;
8 stop_processing BOOLEAN := FALSE;
9 loop_exists NUMBER := 0;
10 GLSTFL_fatal_err EXCEPTION;
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
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
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
33 (msg_name => 'SHRD0180',
34 token_num => 2,
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'
44 AND ledger_set_id IN
45 (SELECT ledger_id
46 FROM GL_LEDGERS
47 WHERE object_type_code = 'S'
48 AND chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
49
50 row_count := SQL%ROWCOUNT;
51 GL_MESSAGE.Write_Log(msg_name => 'SHRD0119',
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
61 (msg_name => 'SHRD0180',
62 token_num => 2,
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
72 WHERE status_code = 'D'
73 AND ledger_set_id IN
74 (SELECT ledger_id
75 FROM GL_LEDGERS
76 WHERE object_type_code = 'S'
77 AND chart_of_accounts_id =
78 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
79
80 row_count := SQL%ROWCOUNT;
81 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
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
91 -- these explicit ledger sets that are left over from pervious runs.
92 -- We don't need to reset the D record for this table since the
93 -- implicit access sets of ledger sets only contain 1 assignment,
94 -- and that is the ledger set itself.
95 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
96 GL_MESSAGE.Write_Log
97 (msg_name => 'SHRD0180',
98 token_num => 2,
99 t1 => 'ROUTINE',
100 v1 => 'Fix_Explicit_Sets()',
101 t2 => 'ACTION',
102 v2 => 'Deleting records with status code I in ' ||
103 'GL_ACCESS_SET_NORM_ASSIGN...');
104 END IF;
105
106 DELETE from GL_ACCESS_SET_NORM_ASSIGN
107 WHERE status_code = 'I'
108 AND access_set_id IN
109 (SELECT implicit_access_set_id
110 FROM GL_LEDGERS
111 WHERE object_type_code = 'S'
112 AND chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
113
114 row_count := SQL%ROWCOUNT;
115 GL_MESSAGE.Write_Log(msg_name => 'SHRD0119',
116 token_num => 2,
117 t1 => 'NUM',
118 v1 => TO_CHAR(row_count),
119 t2 => 'TABLE',
120 v2 => 'GL_ACCESS_SET_NORM_ASSIGN');
121 row_count := 0;
122
123 -- Commit all work so far
124 FND_CONCURRENT.Af_Commit;
125
126 -- Populate access information into GL_ACCESS_SET_NORM_ASSIGN
127 -- for all newly created ledger sets.
128
129 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
130 GL_MESSAGE.Write_Log
131 (msg_name => 'SHRD0180',
132 token_num => 2,
133 t1 => 'ROUTINE',
134 v1 => 'Fix_Explicit_Sets()',
135 t2 => 'ACTION',
136 v2 => 'Insert access information into ' ||
137 'GL_ACCESS_SET_NORM_ASSIGN ' ||
138 'for new ledger sets...');
139 END IF;
140
141 INSERT INTO GL_ACCESS_SET_NORM_ASSIGN
142 (access_set_id, ledger_id, all_segment_value_flag,
143 segment_value_type_code, access_privilege_code, status_code,
144 record_id, link_id, last_update_date, last_updated_by,
145 last_update_login, creation_date, created_by, request_id,
146 segment_value, start_date, end_date)
147 (SELECT distinct
148 gll.implicit_access_set_id, gllsna.ledger_set_id, 'Y',
149 'S', 'B', 'I', -1,
150 NULL, SYSDATE, GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
151 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
152 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
153 GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID,
154 NULL, gllsna.start_date, gllsna.end_date
155 FROM GL_LEDGER_SET_NORM_ASSIGN gllsna,
156 GL_LEDGERS gll
157 WHERE gllsna.status_code = 'I'
158 AND gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
159 AND gll.ledger_id = gllsna.ledger_set_id
160 AND gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
161 AND gll.automatically_created_flag = 'N'
162 AND NOT EXISTS
163 (SELECT 1
164 FROM GL_ACCESS_SET_NORM_ASSIGN glasna
165 WHERE glasna.access_set_id =
166 gll.implicit_access_set_id
167 AND glasna.ledger_id = gllsna.ledger_set_id
168 AND glasna.access_privilege_code = 'B'
169 AND glasna.all_segment_value_flag = 'Y'
170 AND glasna.segment_value_type_code = 'S'
171 AND glasna.segment_value is NULL
172 AND NVL(glasna.status_code, 'X') <> 'D'));
173
174 row_count := SQL%ROWCOUNT;
175 GL_MESSAGE.Write_Log(msg_name => 'SHRD0117',
176 token_num => 2,
177 t1 => 'NUM',
178 v1 => TO_CHAR(row_count),
179 t2 => 'TABLE',
180 v2 => 'GL_ACCESS_SET_NORM_ASSIGN');
181 row_count := 0;
182
183 -- Update the record_id column of the newly created records
184 -- in GL_ACCESS_SET_NORM_ASSIGN
185
186 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
187 GL_MESSAGE.Write_Log
188 (msg_name => 'SHRD0180',
189 token_num => 2,
190 t1 => 'ROUTINE',
191 v1 => 'Fix_Explicit_Sets()',
192 t2 => 'ACTION',
193 v2 => 'Updating records with new record_id in ' ||
194 'GL_ACCESS_SET_NORM_ASSIGN ' ||
195 'for new ledger sets...');
196 END IF;
197
198 UPDATE GL_ACCESS_SET_NORM_ASSIGN glasna
199 SET glasna.record_id = GL_ACCESS_SET_NORM_ASSIGN_S.nextval
200 WHERE glasna.status_code = 'I'
201 AND glasna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
202 AND glasna.record_id = -1
203 AND glasna.access_set_id IN
204 (SELECT gll.implicit_access_set_id
205 FROM GL_LEDGERS gll
206 WHERE gll.chart_of_accounts_id =
207 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
208 AND gll.automatically_created_flag = 'N'
209 AND gll.object_type_code = 'S');
210
211 row_count := SQL%ROWCOUNT;
212 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
213 token_num => 2,
214 t1 => 'NUM',
215 v1 => TO_CHAR(row_count),
216 t2 => 'TABLE',
217 v2 => 'GL_ACCESS_SET_NORM_ASSIGN');
218 row_count := 0;
219
220 -- Commit all work
221 FND_CONCURRENT.Af_Commit;
222
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.
232 -- 3) Restore any mappings that are included via other paths. This
233 -- will be run in a loop until no changes occur.
234
235 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
236 GL_MESSAGE.Write_Log
237 (msg_name => 'SHRD0180',
238 token_num => 2,
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,
248 t1 => 'ROUTINE',
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'
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
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 =
268 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
269 AND gll.automatically_created_flag = 'N'
270 AND gll.object_type_code = 'S'
271 AND gllsa2.ledger_id = gllsna.ledger_set_id)
272 AND gllsa1.ledger_set_id <> gllsa1.ledger_id;
273
274 row_count := SQL%ROWCOUNT;
275 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
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
285 (msg_name => 'SHRD0180',
286 token_num => 2,
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'
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
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 =
307 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
308 AND gll.automatically_created_flag = 'N'
309 AND gll.object_type_code = 'S'
310 AND gllsa2.ledger_id = gllsna.ledger_set_id
311 AND gllsa3.ledger_set_id = gllsna.ledger_id)
312 AND gllsa1.ledger_set_id <> gllsa1.ledger_id;
313
314 row_count := SQL%ROWCOUNT;
315 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
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;
325
326 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
327 GL_MESSAGE.Write_Log
328 (msg_name => 'SHRD0180',
329 token_num => 2,
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
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
345 FROM GL_LEDGERS gll
346 WHERE gll.chart_of_accounts_id =
347 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_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
357 AND gllsa3.ledger_set_id = gllsa1.ledger_set_id
358 AND gllsa3.ledger_id = gllsa2.ledger_set_id)
359 OR EXISTS
360 (SELECT 1
361 FROM GL_LEDGER_SET_NORM_ASSIGN gllsna
362 WHERE gllsna.ledger_set_id = gllsa1.ledger_set_id
363 AND gllsna.ledger_id = gllsa1.ledger_id
364 AND gllsna.status_code is NULL));
365
366 row_count := row_count + NVL(SQL%ROWCOUNT, 0);
367 stop_processing := SQL%NOTFOUND;
368
369 FND_CONCURRENT.Af_Commit;
370 END LOOP;
371
372 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
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
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
387 -- sets. This will be run in a loop until no changes occur.
388
389 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
390 GL_MESSAGE.Write_Log
391 (msg_name => 'SHRD0180',
392 token_num => 2,
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,
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,
405 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
406 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
407 SYSDATE, GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
408 NULL, NULL
409 FROM GL_LEDGERS gll
410 WHERE gll.object_type_code = 'S'
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
420 row_count := SQL%ROWCOUNT;
421 GL_MESSAGE.Write_Log(msg_name => 'SHRD0117',
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
431 (msg_name => 'SHRD0180',
432 token_num => 2,
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,
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,
445 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
446 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
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
456 AND gll.object_type_code = 'L'
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
466 row_count := SQL%ROWCOUNT;
467 GL_MESSAGE.Write_Log(msg_name => 'SHRD0117',
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;
477
478 stop_processing := FALSE;
479
480 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
481 GL_MESSAGE.Write_Log
482 (msg_name => 'SHRD0180',
483 token_num => 2,
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
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,
498 'I', SYSDATE, GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
499 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
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
509 AND gll.chart_of_accounts_id =
510 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
511 AND gll.object_type_code = 'S'
512 AND gll.automatically_created_flag = 'N'
513 AND gllsa1.ledger_id = gllsna.ledger_set_id
514 AND NVL(gllsa1.status_code, 'X') <> 'D'
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
524 row_count := row_count + NVL(SQL%ROWCOUNT, 0);
525 stop_processing := SQL%NOTFOUND;
526
527 FND_CONCURRENT.Af_Commit;
528 END LOOP;
529
530 GL_MESSAGE.Write_Log(msg_name => 'SHRD0117',
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
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
548 AND gll.chart_of_accounts_id =
549 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
550 AND gll.object_type_code = 'S'
551 AND gll.automatically_created_flag = 'N'
552 AND gllsa1.ledger_set_id <> gllsa1.ledger_id
553 AND NVL(gllsa2.status_code, 'X') <> 'D'
554 AND gllsa2.ledger_set_id = gllsa1.ledger_id
555 AND gllsa2.ledger_id = gllsa1.ledger_set_id);
556 EXCEPTION
557 WHEN NO_DATA_FOUND THEN
558 loop_exists := 0;
559 END;
560
561 IF (loop_exists <> 0) THEN
562 -- report error
563 FND_FILE.put_line(FND_FILE.LOG,
564 'loop count := ' || TO_CHAR(NVL(SQL%ROWCOUNT,0)));
565
566 GL_MESSAGE.Write_Log(msg_name => 'FLAT0019',
567 token_num => 0);
568
569 RAISE GLSTFL_fatal_err;
570 END IF;
571
572
573 GL_MESSAGE.Func_Succ
574 (func_name => 'GL_FLATTEN_LEDGER_SETS.Fix_Explicit_Sets');
575
576 RETURN TRUE;
577
578 -- Exception handling
579 EXCEPTION
580 WHEN GLSTFL_fatal_err THEN
581
582 GL_MESSAGE.Write_Log
583 (msg_name => 'FLAT0002',
584 token_num => 1,
585 t1 => 'ROUTINE_NAME',
586 v1 => 'GL_FLATTEN_LEDGER_SETS.Fix_Exlicit_Sets()');
587
588 -- Rollback
589 FND_CONCURRENT.Af_Rollback;
590
591 GL_MESSAGE.Func_Fail
592 (func_name =>'GL_FLATTEN_LEDGER_SETS.Fix_Explicit_Sets');
593
594 RETURN FALSE;
595
596 WHEN OTHERS THEN
597 GL_MESSAGE.Write_Log
598 (msg_name => 'SHRD0203',
599 token_num => 2,
600 t1 => 'FUNCTION',
601 v1 => 'GL_FLATTEN_LEDGER_SETS.Fix_Explicit_Sets()',
602 t2 => 'SQLERRMC',
603 v2 => SQLERRM);
604
605 -- Rollback
606 FND_CONCURRENT.Af_Rollback;
607
608 GL_MESSAGE.Func_Fail
609 (func_name => 'GL_FLATTEN_LEDGER_SETS.Fix_Explicit_Sets');
610
611 RETURN FALSE;
612
613 END Fix_Explicit_Sets;
614
615 -- ******************************************************************
616
617 FUNCTION Clean_Up_Explicit_Sets RETURN BOOLEAN IS
618 row_count NUMBER := 0;
619 BEGIN
620
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',
630 token_num => 2,
631 t1 => 'ROUTINE',
632 v1 => 'Clean_Up_Explicit_Sets()',
633 t2 => 'ACTION',
634 v2 => 'Deleting records from GL_LEDGER_SET_NORM_ASSIGN...');
635 END IF;
636
637 -- Delete records from GL_LEDGER_SET_NORM_ASSIGN
638
639 DELETE from GL_LEDGER_SET_NORM_ASSIGN
640 WHERE status_code = 'D'
641 AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
642 AND ledger_set_id IN
643 (SELECT ledger_id
644 FROM GL_LEDGERS
645 WHERE object_type_code = 'S'
646 AND chart_of_accounts_id =
647 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
648
649 row_count := SQL%ROWCOUNT;
650 GL_MESSAGE.Write_Log(msg_name => 'SHRD0119',
651 token_num => 2,
652 t1 => 'NUM',
653 v1 => TO_CHAR(row_count),
654 t2 => 'TABLE',
655 v2 => 'GL_LEDGER_SET_NORM_ASSIGN');
656 row_count := 0;
657
658 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
659 GL_MESSAGE.Write_Log
660 (msg_name => 'SHRD0180',
661 token_num => 2,
662 t1 => 'ROUTINE',
663 v1 => 'Clean_Up_Explicit_Sets()',
664 t2 => 'ACTION',
665 v2 => 'Updating records in GL_LEDGER_SET_NORM_ASSIGN...');
666 END IF;
667
668 -- Update records in GL_LEDGER_SET_NORM_ASSIGN
669 -- Bear in mind there will never be U records in gllsna
670
671 UPDATE GL_LEDGER_SET_NORM_ASSIGN
672 SET status_code = NULL, request_id = NULL
673 WHERE status_code = 'I'
674 AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
675 AND ledger_set_id IN
676 (SELECT ledger_id
677 FROM GL_LEDGERS
678 WHERE object_type_code = 'S'
679 AND chart_of_accounts_id =
680 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
681
682 row_count := SQL%ROWCOUNT;
683 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
684 token_num => 2,
685 t1 => 'NUM',
686 v1 => TO_CHAR(row_count),
687 t2 => 'TABLE',
688 v2 => 'GL_LEDGER_SET_NORM_ASSIGN');
689 row_count := 0;
690
691 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
692 GL_MESSAGE.Write_Log
693 (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
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
708 WHERE object_type_code = 'S'
709 AND chart_of_accounts_id =
710 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
711
712 row_count := SQL%ROWCOUNT;
713 GL_MESSAGE.Write_Log(msg_name => 'SHRD0119',
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
723 (msg_name => 'SHRD0180',
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
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
739 FROM GL_LEDGERS
740 WHERE object_type_code = 'S'
741 AND chart_of_accounts_id =
742 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
743
744 row_count := SQL%ROWCOUNT;
745 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
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
755 RETURN TRUE;
756
757 EXCEPTION
758 WHEN OTHERS THEN
759 GL_MESSAGE.Write_Log
760 (msg_name => 'SHRD0203',
761 token_num => 2,
762 t1 => 'FUNCTION',
763 v1 => 'GL_FLATTEN_LEDGER_SETS.Clean_Up_Explicit_Sets()',
764 t2 => 'SQLERRMC',
765 v2 => SQLERRM);
766
767 GL_MESSAGE.Func_Fail
768 (func_name => 'GL_FLATTEN_LEDGER_SETS.Clean_Up_Explicit_Sets');
769
770 RETURN FALSE;
771
772 END Clean_Up_Explicit_Sets;
773
774 -- ******************************************************************
775
776 END GL_FLATTEN_LEDGER_SETS;
777