1 PACKAGE BODY GL_FLATTEN_ACCESS_SETS AS
2 /* $Header: gluflasb.pls 120.11 2006/01/13 02:21:38 spala ship $ */
3
4 -- ********************************************************************
5
6 FUNCTION Fix_Explicit_Sets RETURN BOOLEAN IS
7 ret_val BOOLEAN;
8 GLSTFL_fatal_err EXCEPTION;
9 BEGIN
10
11 -- This is the routine that processes changes in explicit ledger
12 -- sets. All changes in GL_ACCESS_SET_NORM_ASSIGN are done
13 -- through the form, so this routine only needs to call
14 -- Fix_Flattened_Table to maintain GL_ACCESS_SET_ASSIGNMENTS.
15 -- There is no need to clean up data before processing since
16 -- changes to GL_ACCESS_SET_ASSIGNMENTS will not be committed
17 -- until everything is done.
18
19 GL_MESSAGE.Func_Ent
20 (func_name => 'GL_FLATTEN_ACCESS_SETS.Fix_Explicit_Sets');
21
22 -- Call Fix_Flattened_Table to maintain GL_ACCESS_SET_ASSIGNMENTS.
23 ret_val := GL_FLATTEN_ACCESS_SETS.Fix_Flattened_Table;
24
25 IF (NOT ret_val) THEN
26 RAISE GLSTFL_fatal_err;
27 END IF;
28
29 GL_MESSAGE.Func_Succ
30 (func_name => 'GL_FLATTEN_ACCESS_SETS.Fix_Explicit_Sets');
31
32 RETURN TRUE;
33
34 -- Exception handling
35 EXCEPTION
36 WHEN GLSTFL_fatal_err THEN
37
38 GL_MESSAGE.Write_Log
39 (msg_name => 'FLAT0002',
40 token_num => 1,
41 t1 => 'ROUTINE_NAME',
42 v1 => 'GL_FLATTEN_ACCESS_SETS.Fix_Explicit_Sets()');
43
44 GL_MESSAGE.Func_Fail
45 (func_name =>'GL_FLATTEN_ACCESS_SETS.Fix_Explicit_Sets');
46
47 RETURN FALSE;
48
49 WHEN OTHERS THEN
50 GL_MESSAGE.Write_Log
51 (msg_name => 'SHRD0203',
52 token_num => 2,
53 t1 => 'FUNCTION',
54 v1 => 'GL_FLATTEN_ACCESS_SETS.Fix_Explicit_Sets()',
55 t2 => 'SQLERRMC',
56 v2 => SQLERRM);
57
58 GL_MESSAGE.Func_Fail
59 (func_name => 'GL_FLATTEN_ACCESS_SETS.Fix_Explicit_Sets');
60
61 RETURN FALSE;
62
63 END Fix_Explicit_Sets;
64
65 -- ******************************************************************
66
67 Function Fix_Implicit_Sets(Any_Ledger_Hier_Changes BOOLEAN)
68 RETURN BOOLEAN IS
69 row_count NUMBER := 0;
70 ret_val BOOLEAN;
71 GLSTFL_fatal_err EXCEPTION;
72 BEGIN
73
74 -- This is the routine that processes changes in the implicit
75 -- access sets due to modifications to the respective ledger
76 -- hierarchies.
77 -- The basic flow is as follows:
78 -- 1) Clean up GL_ACCESS_SET_NORM_ASSIGN for all implicit acces sets
79 -- within the chart of accounts. There is no need to clean
80 -- up GL_ACCESS_SET_ASSIGNMENTS since no changes should be
81 -- committed there unless everything has been completed.
82 -- 2) Call routine Fix_Norm_Table to maintain GL_ACCESS_SET_NORM_ASSIGN.
83 -- 3) Call routine Fix_Flattened_Table to maintain
84 -- GL_ACCESS_SET_ASSIGNMENTS.
85
86 GL_MESSAGE.Func_Ent
87 (func_name => 'GL_FLATTEN_ACCESS_SETS.Fix_Implicit_Sets');
88
89 -- Clean up GL_ACCESS_SET_NORM_ASSIGN
90 -- for any unprocessed data left over from previous failed run
91
92 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
93 GL_MESSAGE.Write_Log
94 (msg_name => 'SHRD0180',
95 token_num => 2,
96 t1 => 'ROUTINE',
97 v1 => 'Fix_Implicit_Sets()',
98 t2 => 'ACTION',
99 v2 => 'Deleting records with status code I in ' ||
100 'GL_ACCESS_SET_NORM_ASSIGN...');
101 END IF;
102
103 DELETE from GL_ACCESS_SET_NORM_ASSIGN
104 WHERE status_code = 'I'
105 AND access_set_id IN
106 (SELECT access_set_id
107 FROM GL_ACCESS_SETS
108 WHERE chart_of_accounts_id =
109 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
110 AND automatically_created_flag = 'Y');
111
112 row_count := SQL%ROWCOUNT;
113 GL_MESSAGE.Write_Log(msg_name => 'SHRD0119',
114 token_num => 2,
115 t1 => 'NUM',
116 v1 => TO_CHAR(row_count),
117 t2 => 'TABLE',
118 v2 => 'GL_ACCESS_SET_NORM_ASSIGN');
119 row_count := 0;
120
121 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
122 GL_MESSAGE.Write_Log
123 (msg_name => 'SHRD0180',
124 token_num => 2,
125 t1 => 'ROUTINE',
126 v1 => 'Fix_Implicit_Sets()',
127 t2 => 'ACTION',
128 v2 => 'Updating records with status code D or U in ' ||
129 'GL_ACCESS_SET_NORM_ASSIGN...');
130 END IF;
131
132 UPDATE GL_ACCESS_SET_NORM_ASSIGN
133 SET status_code = NULL
134 WHERE status_code IN ('D', 'U')
135 AND access_set_id IN
136 (SELECT access_set_id
137 FROM GL_ACCESS_SETS
138 WHERE chart_of_accounts_id =
139 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
140 AND automatically_created_flag = 'Y');
141
142 row_count := SQL%ROWCOUNT;
143 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
144 token_num => 2,
145 t1 => 'NUM',
146 v1 => TO_CHAR(row_count),
147 t2 => 'TABLE',
148 v2 => 'GL_ACCESS_SET_NORM_ASSIGN');
149 row_count := 0;
150
151 -- Commit all clean up work
152 FND_CONCURRENT.Af_Commit;
153
154 -- Call routines Fix_Norm_Table and Fix_Flattened_Table to
155 -- process data
156 ret_val := GL_FLATTEN_ACCESS_SETS.Fix_Norm_Table
157 (Ledgers_And_Hier => Any_Ledger_Hier_Changes);
158
159 IF (NOT ret_val) THEN
160 RAISE GLSTFL_fatal_err;
161 END IF;
162
163 ret_val := GL_FLATTEN_ACCESS_SETS.Fix_Flattened_Table;
164
165 IF (NOT ret_val) THEN
166 RAISE GLSTFL_fatal_err;
167 END IF;
168
169 GL_MESSAGE.Func_Succ
170 (func_name => 'GL_FLATTEN_ACCESS_SETS.Fix_Implicit_Sets');
171
172 RETURN TRUE;
173
174 EXCEPTION
175 WHEN GLSTFL_fatal_err THEN
176
177 GL_MESSAGE.Write_Log
178 (msg_name => 'FLAT0002',
179 token_num => 1,
180 t1 => 'ROUTINE_NAME',
181 v1 => 'GL_FLATTEN_ACCESS_SETS.Fix_Implicit_Sets()');
182
183 -- Rollback
184 FND_CONCURRENT.Af_Rollback;
185
186 GL_MESSAGE.Func_Fail
187 (func_name =>'GL_FLATTEN_ACCESS_SETS.Fix_Implicit_Sets');
188
189 RETURN FALSE;
190
191 WHEN OTHERS THEN
192 GL_MESSAGE.Write_Log
193 (msg_name => 'SHRD0203',
194 token_num => 2,
195 t1 => 'FUNCTION',
196 v1 => 'GL_FLATTEN_ACCESS_SETS.Fix_Implicit_Sets()',
197 t2 => 'SQLERRMC',
198 v2 => SQLERRM);
199
200 -- Rollback
201 FND_CONCURRENT.Af_Rollback;
202
203 GL_MESSAGE.Func_Fail
204 (func_name => 'GL_FLATTEN_ACCESS_SETS.Fix_Implicit_Sets');
205
206 RETURN FALSE;
207
208 END Fix_Implicit_Sets;
209
210 -- *****************************************************************
211
212 Function Fix_Norm_Table(Ledgers_And_Hier BOOLEAN)
213 RETURN BOOLEAN IS
214 row_count NUMBER := 0;
215 BEGIN
216
217 -- This routine maintains GL_ACCESS_SET_NORM_ASSIGN.
218 -- Here is the sequence of events:
219 -- 1) For each new ledger, create a self mapping
220 -- access set assignment for the ledger
221 -- itself if it doesn't exist already.
222 --** ALC changes:
223 --** Also insert associated ALC ledgers under the primary/source
224 --** ledger implicit access set.
225
226
227 /*-------------------------------------------------------------------+
228 | The following process has been suspended since we have no ledger
229 | hierarchy in Accounting Setup Flow.
230 +-------------------------------------------------------------------*/
231 /* *** -- If input parameter indicates there are changes in hierarchies:
232 *** --
233 *** -- 2) Mark records in GL_ACCESS_SET_NORM_ASSIGN for delete
234 *** -- based on marked records in GL_LEDGER_LEDGERS
235 *** -- Again, different statements will be used to process
236 *** -- legal and management hierarchies.
237 *** -- 3) Update records in GL_ACCESS_SET_NORM_ASSIGN based
238 *** -- on updated records in GL_LEDGER___HIERARCHIES.
239 *** -- 4) Insert new records into GL_ACCESS_SET_NORM_ASSIGN based
240 *** -- on new records in GL_LEDGER_HIERARCHIES. Several
241 *** -- statements will be run to process legal and management
242 *** -- hierarchies. */
243
244 GL_MESSAGE.Func_Ent
245 (func_name => 'GL_FLATTEN_ACCESS_SETS.Fix_Norm_Table');
246
247 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
248 GL_MESSAGE.Write_Log
249 (msg_name => 'SHRD0180',
250 token_num => 2,
251 t1 => 'ROUTINE',
252 v1 => 'Fix_Norm_Table()',
253 t2 => 'ACTION',
254 v2 => 'Inserting self mapping records and ALCs ' ||
255 'under its source ledger access set into ' ||
256 'GL_ACCESS_SET_NORM_ASSIGN ' ||
257 'for any new ledgers...');
258 END IF;
259
260 INSERT into GL_ACCESS_SET_NORM_ASSIGN
261 (access_set_id, ledger_id, all_segment_value_flag,
262 segment_value_type_code, access_privilege_code, status_code,
263 record_id, last_update_date, last_updated_by, last_update_login,
264 creation_date, created_by, request_id, segment_value,
265 start_date, end_date, link_id)
266 (SELECT gll.implicit_access_set_id, glr.target_ledger_id, 'Y',
267 'S', 'B', 'I', GL_ACCESS_SET_NORM_ASSIGN_S.nextval,
268 SYSDATE, GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
269 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
270 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
271 GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID,
272 NULL, NULL, NULL, NULL
273 FROM GL_LEDGERS gll,
274 GL_LEDGER_RELATIONSHIPS glr
275 WHERE gll.chart_of_accounts_id =
276 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
277 AND gll.object_type_code = 'L'
278 AND gll.implicit_access_set_id <>-1
279 AND glr.source_ledger_id = gll.ledger_id
280 AND glr.target_ledger_category_code IN ( 'ALC',
281 DECODE(gll.ledger_category_code,'PRIMARY','PRIMARY',''),
282 DECODE(gll.ledger_category_code,'SECONDARY', 'SECONDARY',''))
283 AND glr.relationship_type_code IN ('NONE','JOURNAL','SUBLEDGER')
284 AND glr.application_id = 101
285 AND NOT EXISTS
286 (SELECT 1
287 FROM GL_ACCESS_SET_NORM_ASSIGN glasna
288 WHERE glasna.access_set_id = gll.implicit_access_set_id
289 AND glasna.ledger_id = glr.target_ledger_id
290 AND glasna.all_segment_value_flag = 'Y'
291 AND glasna.segment_value_type_code = 'S'
292 AND glasna.access_privilege_code = 'B'
293 AND glasna.segment_value is NULL
294 AND glasna.start_date is NULL
295 AND glasna.end_date is NULL
296 AND NVL(glasna.status_code, 'X') <> 'D'));
297
298 row_count := SQL%ROWCOUNT;
299 GL_MESSAGE.Write_Log(msg_name => 'SHRD0117',
300 token_num => 2,
301 t1 => 'NUM',
302 v1 => TO_CHAR(row_count),
303 t2 => 'TABLE',
304 v2 => 'GL_ACCESS_SET_NORM_ASSIGN');
305 row_count := 0;
306
307 -- Commit all work
308 FND_CONCURRENT.Af_Commit;
309
310
311 GL_MESSAGE.Func_Succ
312 (func_name => 'GL_FLATTEN_ACCESS_SETS.Fix_Norm_Table');
313
314 RETURN TRUE;
315
316 EXCEPTION
317 WHEN OTHERS THEN
318 GL_MESSAGE.Write_Log
319 (msg_name => 'SHRD0203',
320 token_num => 2,
321 t1 => 'FUNCTION',
322 v1 => 'GL_FLATTEN_ACCESS_SETS.Fix_Norm_Table()',
323 t2 => 'SQLERRMC',
324 v2 => SQLERRM);
325
326 FND_CONCURRENT.Af_Rollback;
327
328 GL_MESSAGE.Func_Fail
329 (func_name => 'GL_FLATTEN_ACCESS_SETS.Fix_Norm_Table');
330
331 RETURN FALSE;
332
333 END Fix_Norm_Table;
334
335 -- ******************************************************************
336
337 FUNCTION Fix_Flattened_Table RETURN BOOLEAN IS
338 row_count NUMBER := 0;
339 ret_val BOOLEAN;
340 GLSTFL_fatal_err EXCEPTION;
341 bal_vs_id NUMBER(15);
342 mgt_vs_id NUMBER(15);
343 curr_as VARCHAR2(30) := NULL;
344 curr_ldg VARCHAR2(30) := NULL;
345 curr_seg_val VARCHAR2(15) := NULL;
346 BEGIN
347
348 -- This routine will call Populate_Temp_Table to process
349 -- all changes to access sets and populate GL_ACCESS_SET_ASSIGN_INT.
350 -- After determining which access set assignments should be
351 -- effective, all final data will be populated back to
352 -- GL_ACCESS_SET_ASSIGNMENTS.
353 -- Here is the sequence of events:
354 -- 1) For modes VH, LH, LS and AS, obtain a shared lock on both
355 -- the balancing and the management segments.
359 -- GL_ACCESS_SET_ASSIGN_INT
356 -- 2) Call Populate_Temp_Table to populate data into
357 -- GL_ACCESS_SET_ASSIGN_INT
358 -- 3) Delete records from GL_ACCESS_SET_ASSIGNMENTS based on
360 -- 4) Call Enable_Record to enable/disable correct assignments in
361 -- GL_ACCESS_SET_ASSIGNMENTS.
362 -- 5) Insert new records into GL_ACCESS_SET_ASSIGNMENTS
363 -- 6) For modes LH and VH, update records in GL_ACCESS_SET_ASSIGNMENTS.
364 --*** Step 6 is no longer required since we do not have ledger hierarchies
365 --*** and no updated records.
366 -- 7) For modes LH, VH, SH and FF, check if there are overlapping date
367 -- ranges for a particular ledger/segment value assignment in
368 -- any management hierarchies.
369 -- If so, report as error and abort processing.
370
371 GL_MESSAGE.Func_Ent
372 (func_name => 'GL_FLATTEN_ACCESS_SETS.Fix_Flattened_Table');
373
374 -- For modes LH, LS, VS and AS, obtain shared lock on both balancing
375 -- and management segments.
376 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE IN
377 ('VH', 'LH', 'LS', 'AS', 'VS')) THEN
378
379 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
380 GL_MESSAGE.Write_Log
381 (msg_name => 'SHRD0180',
382 token_num => 2,
383 t1 => 'ROUTINE',
384 v1 => 'Fix_Flattened_Table()',
385 t2 => 'ACTION',
386 v2 => 'Obtain shared lock on balancing segment...');
387 END IF;
388
389 ret_val := GL_FLATTEN_SETUP_DATA.Request_Lock
390 (X_Param_Type => 'V',
391 X_Param_Id =>
392 GL_FLATTEN_SETUP_DATA.GLSTFL_BAL_VS_ID,
393 X_Lock_Mode => 4, -- SHARED mode
394 X_Keep_Looping => TRUE,
395 X_Max_Trys => 5);
396
397 IF (NOT ret_val) THEN
398 RAISE GLSTFL_fatal_err;
399 END IF;
400
401 -- Obtain the second lock iff bal_vs_id <> mgt_vs_id
402
403 -- Now the Management segment value set is optional.
404 /* IF (GL_FLATTEN_SETUP_DATA.GLSTFL_BAL_VS_ID <>
405 GL_FLATTEN_SETUP_DATA.GLSTFL_MGT_VS_ID) THEN */
406
407 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_MGT_VS_ID IS NOT NULL) THEN
408
409 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_BAL_VS_ID <>
410 GL_FLATTEN_SETUP_DATA.GLSTFL_MGT_VS_ID) THEN
411 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
412 GL_MESSAGE.Write_Log
413 (msg_name => 'SHRD0180',
414 token_num => 2,
415 t1 => 'ROUTINE',
416 v1 => 'Fix_Flattened_Table()',
417 t2 => 'ACTION',
418 v2 => 'Obtain shared lock on management segment...');
419 END IF;
420
421 ret_val := GL_FLATTEN_SETUP_DATA.Request_Lock
422 (X_Param_Type => 'V',
423 X_Param_Id =>
424 GL_FLATTEN_SETUP_DATA.GLSTFL_MGT_VS_ID,
425 X_Lock_Mode => 4, -- SHARED mode
426 X_Keep_Looping => TRUE,
427 X_Max_Trys => 5);
428
429 IF (NOT ret_val) THEN
430 RAISE GLSTFL_fatal_err;
431 END IF;
432 END IF;
433 END IF;
434 END IF;
435
436 -- Call Populate_Temp_Table
437 ret_val := GL_FLATTEN_ACCESS_SETS.Populate_Temp_Table;
438
439 IF (NOT ret_val) THEN
440 RAISE GLSTFL_fatal_err;
441 END IF;
442
443 -- Delete records from GL_ACCESS_SET_ASSIGNMENTS based on
444 -- GL_ACCESS_SET_ASSIGN_INT
445 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
446 GL_MESSAGE.Write_Log
447 (msg_name => 'SHRD0180',
448 token_num => 2,
449 t1 => 'ROUTINE',
450 v1 => 'Fix_Flattened_Table()',
451 t2 => 'ACTION',
452 v2 => 'Deleting records from GL_ACCESS_SET_ASSIGNMENTS...');
453 END IF;
454
455 DELETE from GL_ACCESS_SET_ASSIGNMENTS glasa
456 WHERE (ABS(glasa.access_set_id), glasa.ledger_id,
457 glasa.segment_value, glasa.parent_record_id) IN
458 (SELECT glasai.access_set_id, glasai.ledger_id,
459 glasai.segment_value, glasai.parent_record_id
460 FROM GL_ACCESS_SET_ASSIGN_INT glasai
461 WHERE glasai.status_code = 'D');
462
463 row_count := SQL%ROWCOUNT;
464 GL_MESSAGE.Write_Log(msg_name => 'SHRD0119',
465 token_num => 2,
466 t1 => 'NUM',
467 v1 => TO_CHAR(row_count),
468 t2 => 'TABLE',
469 v2 => 'GL_ACCESS_SET_ASSIGNMENTS');
470 row_count :=0;
471
472 -- Here only call Enable_Record when not processing implicit access
473 -- sets ONLY. This means that the routine will NOT be called in modes
474 -- LH and VH
475 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE NOT IN ('LH', 'VH')) THEN
476 ret_val := GL_FLATTEN_ACCESS_SETS.Enable_Record;
477
478 IF (NOT ret_val) THEN
479 RAISE GLSTFL_fatal_err;
480 END IF;
481 END IF;
482
483 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
484
485 BEGIN
486 SELECT count(*)
487 INTO row_count
488 FROM GL_ACCESS_SET_ASSIGN_INT;
489 EXCEPTION
490 WHEN NO_DATA_FOUND THEN
491 row_count := 0;
492 END;
493
494 GL_MESSAGE.Write_Log
495 (msg_name => 'SHRD0180',
496 token_num => 2,
497 t1 => 'ROUTINE',
498 v1 => 'Fix_Flattened_Table()',
499 t2 => 'ACTION',
500 v2 => 'GL_ACCESS_SET_ASSIGN_INT has ' || TO_CHAR(row_count) ||
501 ' records...');
502
503 row_count := 0;
504 END IF;
505
506 -- Insert new records into GL_ACCESS_SET_ASSIGNMENTS
510 token_num => 2,
507 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
508 GL_MESSAGE.Write_Log
509 (msg_name => 'SHRD0180',
511 t1 => 'ROUTINE',
512 v1 => 'Fix_Flattened_Table()',
513 t2 => 'ACTION',
514 v2 => 'Inserting records into GL_ACCESS_SET_ASSIGNMENTS...');
515 END IF;
516
517 INSERT into GL_ACCESS_SET_ASSIGNMENTS
518 (access_set_id, ledger_id, segment_value, access_privilege_code,
519 parent_record_id, last_update_date, last_updated_by, last_update_login,
520 creation_date, created_by, start_date, end_date)
521 (SELECT glasai.access_set_id, glasai.ledger_id, glasai.segment_value,
522 glasai.access_privilege_code, glasai.parent_record_id, SYSDATE,
523 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
524 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
525 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
526 glasai.start_date, glasai.end_date
527 FROM GL_ACCESS_SET_ASSIGN_INT glasai
528 WHERE glasai.status_code = 'I'
529 AND NOT EXISTS
530 (SELECT 1
531 FROM GL_ACCESS_SET_ASSIGNMENTS glasa
532 WHERE ( glasa.access_set_id = glasai.access_set_id
533 OR glasa.access_set_id = -glasai.access_set_id)
534 AND glasa.parent_record_id = glasai.parent_record_id
535 AND glasa.ledger_id = glasai.ledger_id
536 AND glasa.segment_value = glasai.segment_value
537 AND NVL(glasa.start_date,
538 TO_DATE('01/01/1950', 'MM/DD/YYYY')) =
539 NVL(glasai.start_date,
540 TO_DATE('01/01/1950', 'MM/DD/YYYY'))
541 AND NVL(glasa.end_date,
542 TO_DATE('12/31/9999', 'MM/DD/YYYY')) =
543 NVL(glasai.end_date,
544 TO_DATE('12/31/9999', 'MM/DD/YYYY'))));
545
546 row_count := SQL%ROWCOUNT;
547 GL_MESSAGE.Write_Log(msg_name => 'SHRD0117',
548 token_num => 2,
549 t1 => 'NUM',
550 v1 => TO_CHAR(row_count),
551 t2 => 'TABLE',
552 v2 => 'GL_ACCESS_SET_ASSIGNMENTS');
553 row_count :=0;
554
555
556 -- Check if a particular ledger/segment value have overlapping effective
557 -- dates for management hierarchies
558 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE in ('LH', 'SH', 'FF', 'VH')) THEN
559 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
560 GL_MESSAGE.Write_Log
561 (msg_name => 'SHRD0180',
562 token_num => 2,
563 t1 => 'ROUTINE',
564 v1 => 'Fix_Flattened_Table()',
565 t2 => 'ACTION',
566 v2 => 'Checking if any ledger/segment value ' ||
567 'assignment associated with management hierarchies ' ||
568 'has overlapping effective date ranges...');
569 END IF;
570
571 -- Here we do not need to use ABS( ) around access_set_id since for
572 -- management hierarchies no records will be disabled
573 BEGIN
574
575 SELECT 1
576 INTO row_count
577 FROM GL_ACCESS_SETS glas,
578 GL_ACCESS_SET_ASSIGN_INT glasai,
579 GL_ACCESS_SET_ASSIGNMENTS glasa1,
580 GL_ACCESS_SET_ASSIGNMENTS glasa2
581 WHERE ( glas.secured_seg_value_set_id =
582 GL_FLATTEN_SETUP_DATA.GLSTFL_MGT_VS_ID
583 OR glas.secured_seg_value_set_id =
584 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID)
585 AND glas.automatically_created_flag = 'Y'
586 AND glas.security_segment_code = 'M'
587 AND glasai.access_set_id = glas.access_set_id
588 AND glasa1.access_set_id = glasai.access_set_id
589 AND glasa1.ledger_id = glasai.ledger_id
590 AND glasa1.segment_value = glasai.segment_value
591 AND glasa2.access_set_id = glasa1.access_set_id
592 AND glasa2.ledger_id = glasa1.ledger_id
593 AND glasa2.segment_value = glasa1.segment_value
594 AND glasa2.rowid <> glasa1.rowid
595 AND ( NVL(glasa1.start_date,
596 TO_DATE('01/01/1950', 'MM/DD/YYYY'))
597 BETWEEN NVL(glasa2.start_date,
598 TO_DATE('01/01/1950', 'MM/DD/YYYY'))
599 AND NVL(glasa2.end_date,
600 TO_DATE('12/31/9999', 'MM/DD/YYYY'))
601 OR NVL(glasa1.end_date,
602 TO_DATE('12/31/9999', 'MM/DD/YYYY'))
603 BETWEEN NVL(glasa2.start_date,
604 TO_DATE('01/01/1950', 'MM/DD/YYYY'))
605 AND NVL(glasa2.end_date,
606 TO_DATE('12/31/9999', 'MM/DD/YYYY')))
607 AND rownum <= 1;
608
609 EXCEPTION
610 WHEN NO_DATA_FOUND THEN
611 NULL;
612 END;
613
614 IF (SQL%FOUND) THEN
615 -- Overlapping date ranges found, print out error message
616 -- and abort.
617 GL_MESSAGE.Write_Log(msg_name => 'FLAT0016',
618 token_num => 0);
619
620 DECLARE
621 CURSOR overlap_dates_cursor IS
622 SELECT distinct glas.name, gll.name, glasa1.segment_value
623 FROM GL_ACCESS_SETS glas,
624 GL_ACCESS_SET_ASSIGN_INT glasai,
625 GL_ACCESS_SET_ASSIGNMENTS glasa1,
626 GL_ACCESS_SET_ASSIGNMENTS glasa2,
627 GL_LEDGERS gll
628 WHERE ( glas.secured_seg_value_set_id =
629 GL_FLATTEN_SETUP_DATA.GLSTFL_MGT_VS_ID
630 OR glas.secured_seg_value_set_id =
631 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID)
632 AND glas.automatically_created_flag = 'Y'
633 AND glas.security_segment_code = 'M'
634 AND glasai.access_set_id = glas.access_set_id
635 AND glasa1.access_set_id = glasai.access_set_id
636 AND glasa1.ledger_id = glasai.ledger_id
637 AND glasa1.segment_value = glasai.segment_value
638 AND glasa2.access_set_id = glasa1.access_set_id
639 AND glasa2.ledger_id = glasa1.ledger_id
640 AND glasa2.segment_value = glasa1.segment_value
641 AND glasa2.rowid <> glasa1.rowid
645 TO_DATE('01/01/1950', 'MM/DD/YYYY'))
642 AND ( NVL(glasa1.start_date,
643 TO_DATE('01/01/1950', 'MM/DD/YYYY'))
644 BETWEEN NVL(glasa2.start_date,
646 AND NVL(glasa2.end_date,
647 TO_DATE('12/31/9999', 'MM/DD/YYYY'))
648 OR NVL(glasa1.end_date,
649 TO_DATE('12/31/9999', 'MM/DD/YYYY'))
650 BETWEEN NVL(glasa2.start_date,
651 TO_DATE('01/01/1950', 'MM/DD/YYYY'))
652 AND NVL(glasa2.end_date,
653 TO_DATE('12/31/9999', 'MM/DD/YYYY')))
654 AND gll.ledger_id = glasa1.ledger_id;
655 BEGIN
656 IF (NOT overlap_dates_cursor%ISOPEN) THEN
657 OPEN overlap_dates_cursor;
658 END IF;
659
660 LOOP
661 FETCH overlap_dates_cursor INTO curr_as, curr_ldg, curr_seg_val;
662 EXIT WHEN overlap_dates_cursor%NOTFOUND;
663
664 IF (curr_as IS NOT NULL) THEN
665 GL_MESSAGE.Write_Log
666 (msg_name => 'FLAT0003',
667 token_num => 3,
668 t1 => 'ACCESS_SET_NAME',
669 v1 => curr_as,
670 t2 => 'LEDGER_NAME',
671 v2 => curr_ldg,
672 t3 => 'SEG_VAL',
673 v3 => curr_seg_val);
674 END IF;
675 END LOOP;
676 EXCEPTION
677 WHEN OTHERS THEN
678 NULL;
679 END;
680
681 RAISE GLSTFL_fatal_err;
682 END IF;
683 END IF; -- IF (...OP_MODE IN ('LV', 'LH'...
684
685 -- Note here we do not release the shared lock on balancing and
686 -- management segments since we want to pass it on to other
687 -- packages. The locks will be released by the main routine
688 -- when all clean up work is completed. This is to make sure
689 -- that SH and FF processes cannot start until all status codes
690 -- are reset to current.
691
692 GL_MESSAGE.Func_Succ
693 (func_name => 'GL_FLATTEN_ACCESS_SETS.Fix_Flattened_Table');
694
695 RETURN TRUE;
696
697 EXCEPTION
698 WHEN GLSTFL_fatal_err THEN
699
700 GL_MESSAGE.Write_Log
701 (msg_name => 'FLAT0002',
702 token_num => 1,
703 t1 => 'ROUTINE_NAME',
704 v1 => 'GL_FLATTEN_ACCESS_SETS.Fix_Flattened_Table()');
705
706 GL_MESSAGE.Func_Fail
707 (func_name => 'GL_FLATTEN_ACCESS_SETS.Fix_Flattened_Table');
708
709 RETURN FALSE;
710
711 WHEN OTHERS THEN
712 GL_MESSAGE.Write_Log
713 (msg_name => 'SHRD0203',
714 token_num => 2,
715 t1 => 'FUNCTION',
716 v1 => 'GL_FLATTEN_ACCESS_SETS.Fix_Flattened_Table()',
717 t2 => 'SQLERRMC',
718 v2 => SQLERRM);
719
720 GL_MESSAGE.Func_Fail
721 (func_name => 'GL_FLATTEN_ACCESS_SETS.Fix_Flattened_Table');
722
723 RETURN FALSE;
724
725 END Fix_Flattened_Table;
726
727 -- ******************************************************************
728
729 FUNCTION Populate_Temp_Table RETURN BOOLEAN IS
730 row_count NUMBER := 0;
731 cont_processing BOOLEAN := TRUE;
732 sh_ff_all_val_changed BOOLEAN := TRUE;
733 as_all_val_changed BOOLEAN := TRUE;
734 as_single_val_changed BOOLEAN := TRUE;
735 as_parent_val_changed BOOLEAN := TRUE;
736 BEGIN
737
738 -- This routine will populate GL_ACCESS_SET_ASSIGN_INT based on
739 -- the mode of operation. Since this is a relatively expensive
740 -- operation, we will only do work when there are indeed changes
741 -- that will affect the access sets.
742 -- Here is the sequence of events:
743 -- 1) For modes FF, SH, LS and AS, check if further processing is
744 -- necessary here.
745 -- 2) If processing is needed, run statements to populate
746 -- GL_ACCESS_SET_ASSIGN_INT based on the mode of operation.
747
748 GL_MESSAGE.Func_Ent
749 (func_name => 'GL_FLATTEN_ACCESS_SETS.Populate_Temp_Table');
750
751 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
752 GL_MESSAGE.Write_Log
753 (msg_name => 'SHRD0180',
754 token_num => 2,
755 t1 => 'ROUTINE',
756 v1 => 'Populate_Temp_Table()',
757 t2 => 'ACTION',
758 v2 => 'Checking if further processing is needed ' ||
759 'based on mode of operation...');
760 END IF;
761
762 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE IN ('SH', 'FF')) THEN
763
764 -- Check if there are new values added to the value set and
765 -- there are access sets associated with this value set with
766 -- segment_value_type_code of 'A'
767
768 BEGIN
769 row_count := 0;
770 SELECT 1
771 INTO row_count
772 FROM DUAL
773 WHERE EXISTS
774 (SELECT 1
775 FROM GL_SEG_VAL_HIERARCHIES glsvh
776 WHERE glsvh.flex_value_set_id =
777 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
778 AND glsvh.parent_flex_value = 'T'
779 AND glsvh.status_code = 'I');
780
781 -- Performance bug 4861665 fix.
782
783 IF (row_count = 1) THEN
784 SELECT 1
785 INTO row_count
786 FROM DUAL
787 WHERE EXISTS
788 (SELECT 1
789 FROM GL_ACCESS_SETS glas,
790 GL_ACCESS_SET_NORM_ASSIGN glasna
791 WHERE glas.secured_seg_value_set_id =
792 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
793 AND glas.security_segment_code <> 'F'
794 AND glasna.access_set_id = glas.access_set_id
795 AND glasna.all_segment_value_flag = 'Y');
796 END IF;
797 EXCEPTION
798 WHEN NO_DATA_FOUND THEN
799 sh_ff_all_val_changed := SQL%FOUND;
800 END;
801
805 -- ledger set.
802 ELSIF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE IN ('LS', 'VS')) THEN
803
804 -- Check if any access set assignment contains the changed
806 BEGIN
807 -- NOTE: gllsa records should never have a status_code of U
808 SELECT 1
809 INTO row_count
810 FROM DUAL
811 WHERE EXISTS
812 (SELECT 1
813 FROM GL_ACCESS_SET_NORM_ASSIGN glasna
814 WHERE glasna.ledger_id IN
815 (SELECT distinct gllsa.ledger_set_id
816 FROM GL_LEDGER_SET_ASSIGNMENTS gllsa,
817 GL_LEDGERS gll
818 WHERE gllsa.status_code IN ('I', 'D')
819 AND gll.ledger_id = gllsa.ledger_set_id
820 AND gll.chart_of_accounts_id =
821 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
822 AND gll.object_type_code = 'S'
823 AND gll.automatically_created_flag = 'N'));
824 EXCEPTION
825 WHEN NO_DATA_FOUND THEN
826 cont_processing := SQL%FOUND;
827 END;
828
829 ELSIF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE = 'AS') THEN
830
831 -- There are 3 checks we have to do here to determine what
832 -- statements should be run for further processing.
833 -- 1) If changes occurred in full ledger type access sets or
834 -- access set using a single segment value.
835 -- 2) If changes occurred in access sets using parent segment value
836 -- and their descendants.
837 -- 3) If changes occurred in access sets using all segment values.
838
839 BEGIN
840 SELECT 1
841 INTO row_count
842 FROM DUAL
843 WHERE EXISTS
844 (SELECT 1
845 FROM GL_ACCESS_SETS glas,
846 GL_ACCESS_SET_NORM_ASSIGN glasna
847 WHERE glas.chart_of_accounts_id =
848 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
849 AND glas.automatically_created_flag = 'N'
850 AND glasna.access_set_id = glas.access_set_id
851 AND glasna.request_id =
852 GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
853 AND glasna.status_code IN ('I', 'D', 'U')
854 AND ( ( glasna.all_segment_value_flag = 'N'
855 AND glasna.segment_value_type_code = 'S')
856 OR (glas.security_segment_code = 'F')));
857 EXCEPTION
858 WHEN NO_DATA_FOUND THEN
859 as_single_val_changed := SQL%FOUND;
860 END;
861
862 BEGIN
863 SELECT 1
864 INTO row_count
865 FROM DUAL
866 WHERE EXISTS
867 (SELECT 1
868 FROM GL_ACCESS_SETS glas,
869 GL_ACCESS_SET_NORM_ASSIGN glasna
870 WHERE glas.chart_of_accounts_id =
871 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
872 AND glas.automatically_created_flag = 'N'
873 AND glasna.access_set_id = glas.access_set_id
874 AND glasna.request_id =
875 GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
876 AND glasna.status_code IN ('I', 'D', 'U')
877 AND glasna.segment_value_type_code = 'C');
878
879 EXCEPTION
880 WHEN NO_DATA_FOUND THEN
881 as_parent_val_changed := SQL%FOUND;
882 END;
883
884 BEGIN
885 SELECT 1
886 INTO row_count
887 FROM DUAL
888 WHERE EXISTS
889 (SELECT 1
890 FROM GL_ACCESS_SETS glas,
891 GL_ACCESS_SET_NORM_ASSIGN glasna
892 WHERE glas.chart_of_accounts_id =
893 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
894 AND glas.automatically_created_flag = 'N'
895 AND glas.security_segment_code <> 'F'
896 AND glasna.access_set_id = glas.access_set_id
897 AND glasna.request_id =
898 GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
899 AND glasna.status_code IN ('I', 'D', 'U')
900 AND glasna.all_segment_value_flag = 'Y');
901 EXCEPTION
902 WHEN NO_DATA_FOUND THEN
903 as_all_val_changed := SQL%FOUND;
904 END;
905
906 -- Program shoud continue processing if any changes are found
907 cont_processing := (as_single_val_changed OR as_parent_val_changed OR
908 as_all_val_changed);
909 END IF;
910
911 IF (cont_processing) THEN
912
913 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
914 GL_MESSAGE.Write_Log
915 (msg_name => 'FLAT0017',
916 token_num => 1,
917 t1 => 'OP_MODE',
918 v1 => GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE);
919 END IF;
920
921 -- Since changes related to access sets are found, start
922 -- populating GL_ACCESS_SET_ASSIGN_INT for various modes
923 -- of operation.
924
925 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE IN ('FF', 'SH')) THEN
926
927 row_count := 0;
928
929 -- This statement process all segment value hierarchy changes,
930 -- thus it will be run in mode SH only. It is not relevant to
931 -- mode FF.
932 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE = 'SH') THEN
933
934 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
935 GL_MESSAGE.Write_Log
936 (msg_name => 'SHRD0180',
937 token_num => 2,
938 t1 => 'ROUTINE',
939 v1 => 'Populate_Temp_Table()',
940 t2 => 'ACTION',
941 v2 => 'Inserting records into ' ||
942 'GL_ACCESS_SET_ASSIGN_INT for segment ' ||
943 'value hierarchy changes...');
944 END IF;
945
946 INSERT into GL_ACCESS_SET_ASSIGN_INT
947 (access_set_id, ledger_id, segment_value, access_privilege_code,
948 status_code, parent_record_id, last_update_date, last_updated_by,
949 last_update_login, creation_date, created_by, start_date, end_date)
950 (SELECT glasna.access_set_id,
951 DECODE(gllsa.ledger_id,
952 NULL, glasna.ledger_id, gllsa.ledger_id),
953 glsvh.child_flex_value, glasna.access_privilege_code,
954 glsvh.status_code, glasna.record_id, SYSDATE,
955 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
959 FROM GL_SEG_VAL_HIERARCHIES glsvh,
956 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
957 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
958 glasna.start_date, glasna.end_date
960 GL_ACCESS_SETS glas,
961 GL_ACCESS_SET_NORM_ASSIGN glasna,
962 GL_LEDGER_SET_ASSIGNMENTS gllsa
963 WHERE glsvh.flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
964 AND glsvh.status_code in ('I', 'D')
965 AND glas.security_segment_code <> 'F'
966 AND glas.secured_seg_value_set_id =
967 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
968 AND glasna.access_set_id = glas.access_set_id
969 AND glasna.all_segment_value_flag = 'N'
970 AND glasna.segment_value_type_code = 'C'
971 AND glasna.segment_value = glsvh.parent_flex_value
972 AND NVL(glasna.status_code, 'X') <> 'I'
973 AND gllsa.ledger_set_id(+) = glasna.ledger_id
974 AND NVL(gllsa.status_code(+), 'X') <> 'I');
975
976 -- The above statement should process U records in glasna
977 -- since this will only happen in dates update of management
978 -- hierarchy assignments. We need to make sure all segment
979 -- value hierarchies changes in place to make sure that
980 -- the other process will update the right records.
981 -- (Update is done with parent_record_id and thus will not
982 -- check for the integrity of the segment value assignments!
983 --
984 -- Also, records in gllsa will never have a status_code of U.
985
986 row_count := SQL%ROWCOUNT;
987 GL_MESSAGE.Write_Log(msg_name => 'SHRD0117',
988 token_num => 2,
989 t1 => 'NUM',
990 v1 => TO_CHAR(row_count),
991 t2 => 'TABLE',
992 v2 => 'GL_ACCESS_SET_ASSIGN_INT');
993 row_count := 0;
994 END IF;
995
996 -- If there are new segment values added to the value set and
997 -- there are access set assignments associated with this value
998 -- set with all_segment_value_flag of 'Y', run this statement
999 -- to add in the new segment values to the assignments.
1000 IF (sh_ff_all_val_changed) THEN
1001
1002 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1003 GL_MESSAGE.Write_Log
1004 (msg_name => 'SHRD0180',
1005 token_num => 2,
1006 t1 => 'ROUTINE',
1007 v1 => 'Populate_Temp_Table()',
1008 t2 => 'ACTION',
1009 v2 => 'Inserting records into ' ||
1010 'GL_ACCESS_SET_ASSIGN_INT for new ' ||
1011 'segment values...');
1012 END IF;
1013
1014 INSERT into GL_ACCESS_SET_ASSIGN_INT
1015 (access_set_id, ledger_id, segment_value, access_privilege_code,
1016 status_code, parent_record_id, last_update_date, last_updated_by,
1017 last_update_login, creation_date, created_by, start_date, end_date)
1018 (SELECT glasna.access_set_id,
1019 DECODE(gllsa.ledger_id,
1020 NULL, glasna.ledger_id, gllsa.ledger_id),
1021 glsvh.child_flex_value, glasna.access_privilege_code,
1022 'I', glasna.record_id, SYSDATE,
1023 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1024 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
1025 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1026 glasna.start_date, glasna.end_date
1027 FROM GL_SEG_VAL_HIERARCHIES glsvh,
1028 GL_ACCESS_SETS glas,
1029 GL_ACCESS_SET_NORM_ASSIGN glasna,
1030 GL_LEDGER_SET_ASSIGNMENTS gllsa
1031 WHERE glsvh.flex_value_set_id = GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
1032 AND glsvh.parent_flex_value = 'T'
1033 AND glsvh.status_code = 'I'
1034 AND glas.secured_seg_value_set_id =
1035 GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
1036 AND glas.security_segment_code <> 'F'
1037 AND glasna.access_set_id = glas.access_set_id
1038 AND glasna.all_segment_value_flag = 'Y'
1039 AND NVL(glasna.status_code, 'X') <> 'I'
1040 AND gllsa.ledger_set_id(+) = glasna.ledger_id
1041 AND NVL(gllsa.status_code(+), 'X') <> 'I');
1042
1043 row_count := SQL%ROWCOUNT;
1044 GL_MESSAGE.Write_Log(msg_name => 'SHRD0117',
1045 token_num => 2,
1046 t1 => 'NUM',
1047 v1 => TO_CHAR(row_count),
1048 t2 => 'TABLE',
1049 v2 => 'GL_ACCESS_SET_ASSIGN_INT');
1050 row_count := 0;
1051
1052 END IF; -- IF (sh_ff_all_val_changed)...
1053
1054 ELSIF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE IN ('LH', 'VH')) THEN
1055
1056 row_count := 0;
1057
1058 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1059 GL_MESSAGE.Write_Log
1060 (msg_name => 'SHRD0180',
1061 token_num => 2,
1062 t1 => 'ROUTINE',
1063 v1 => 'Populate_Temp_Table()',
1064 t2 => 'ACTION',
1065 v2 => 'Inserting records into ' ||
1066 'GL_ACCESS_SET_ASSIGN_INT for legal ' ||
1067 'and management implicit access sets...');
1068 END IF;
1069
1070 -- This statement will process implicit access set changes from
1071 -- legal hierarchies.
1072
1073 INSERT into GL_ACCESS_SET_ASSIGN_INT
1074 (access_set_id, ledger_id, segment_value, access_privilege_code,
1075 status_code, parent_record_id, last_update_date, last_updated_by,
1076 last_update_login, creation_date, created_by, start_date, end_date)
1077 (SELECT glasna.access_set_id, glasna.ledger_id,
1078 NVL(glasna.segment_value, '-1'), glasna.access_privilege_code,
1079 glasna.status_code, glasna.record_id, SYSDATE,
1080 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1081 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
1082 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1083 glasna.start_date, glasna.end_date
1084 FROM GL_ACCESS_SET_NORM_ASSIGN glasna,
1085 GL_LEDGERS gll
1086 WHERE glasna.status_code IN ('I')
1087 AND glasna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
1091 AND gll.object_type_code = 'L');
1088 AND gll.implicit_access_set_id = glasna.access_set_id
1089 AND gll.chart_of_accounts_id =
1090 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
1092
1093
1094
1095 row_count := SQL%ROWCOUNT;
1096 GL_MESSAGE.Write_Log(msg_name => 'SHRD0117',
1097 token_num => 2,
1098 t1 => 'NUM',
1099 v1 => TO_CHAR(row_count),
1100 t2 => 'TABLE',
1101 v2 => 'GL_ACCESS_SET_ASSIGN_INT');
1102 row_count := 0;
1103
1104
1105 ELSIF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE IN ('LS', 'VS')) THEN
1106
1107 -- This section will process access set changes due to changes
1108 -- in the explicit ledger sets, as well as any changes that affect
1109 -- the implicit access set tied to these ledger sets.
1110
1111 row_count := 0;
1112
1113 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1114 GL_MESSAGE.Write_Log
1115 (msg_name => 'SHRD0180',
1116 token_num => 2,
1117 t1 => 'ROUTINE',
1118 v1 => 'Populate_Temp_Table()',
1119 t2 => 'ACTION',
1120 v2 => 'Inserting records into ' ||
1121 'GL_ACCESS_SET_ASSIGN_INT for changed ' ||
1122 'ledger sets ' ||
1123 'contained in full ledger type access sets ' ||
1124 'or access assignment with a single segment '||
1125 'value...');
1126 END IF;
1127
1128 -- This statement will not join to GL_SEG_VAL_HIERARCHIES since
1129 -- it only process changes in explicit ledger sets in full ledger
1130 -- type access sets, or access assignment with a single segment value.
1131 INSERT into GL_ACCESS_SET_ASSIGN_INT
1132 (access_set_id, ledger_id, segment_value, access_privilege_code,
1133 status_code, parent_record_id, last_update_date, last_updated_by,
1134 last_update_login, creation_date, created_by, start_date, end_date)
1135 (SELECT glasna.access_set_id, gllsa.ledger_id,
1136 NVL(glasna.segment_value, '-1'), glasna.access_privilege_code,
1137 gllsa.status_code, glasna.record_id, SYSDATE,
1138 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1139 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
1140 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1141 glasna.start_date, glasna.end_date
1142 FROM GL_LEDGER_SET_ASSIGNMENTS gllsa,
1143 GL_LEDGERS gll,
1144 GL_ACCESS_SET_NORM_ASSIGN glasna,
1145 GL_ACCESS_SETS glas
1146 WHERE gllsa.status_code IN ('I', 'D')
1147 AND gll.ledger_id = gllsa.ledger_set_id
1148 AND gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
1149 AND gll.object_type_code = 'S'
1150 AND gll.automatically_created_flag = 'N'
1151 AND glasna.ledger_id = gllsa.ledger_set_id
1152 AND NVL(glasna.status_code, 'X') NOT IN ('I', 'U')
1153 AND glas.access_set_id = glasna.access_set_id
1154 AND ( glas.security_segment_code = 'F'
1155 OR ( glasna.segment_value_type_code = 'S'
1156 AND glasna.all_segment_value_flag = 'N')));
1157
1158 row_count := SQL%ROWCOUNT;
1159 GL_MESSAGE.Write_Log(msg_name => 'SHRD0117',
1160 token_num => 2,
1161 t1 => 'NUM',
1162 v1 => TO_CHAR(row_count),
1163 t2 => 'TABLE',
1164 v2 => 'GL_ACCESS_SET_ASSIGN_INT');
1165 row_count := 0;
1166
1167 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1168 GL_MESSAGE.Write_Log
1169 (msg_name => 'SHRD0180',
1170 token_num => 2,
1171 t1 => 'ROUTINE',
1172 v1 => 'Populate_Temp_Table()',
1173 t2 => 'ACTION',
1174 v2 => 'Inserting records into ' ||
1175 'GL_ACCESS_SET_ASSIGN_INT for access sets ' ||
1176 'containing changed explicit ledger sets ' ||
1177 'with all segment values assigned...');
1178 END IF;
1179
1180 INSERT into GL_ACCESS_SET_ASSIGN_INT
1181 (access_set_id, ledger_id, segment_value, access_privilege_code,
1182 status_code, parent_record_id, last_update_date, last_updated_by,
1183 last_update_login, creation_date, created_by, start_date, end_date)
1184 (SELECT glasna.access_set_id, gllsa.ledger_id,
1185 glsvh.child_flex_value, glasna.access_privilege_code,
1186 gllsa.status_code, glasna.record_id, SYSDATE,
1187 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1188 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
1189 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1190 glasna.start_date, glasna.end_date
1191 FROM GL_LEDGER_SET_ASSIGNMENTS gllsa,
1192 GL_LEDGERS gll,
1193 GL_ACCESS_SET_NORM_ASSIGN glasna,
1194 GL_ACCESS_SETS glas,
1195 GL_SEG_VAL_HIERARCHIES glsvh
1196 WHERE gllsa.status_code IN ('I', 'D')
1197 AND gll.ledger_id = gllsa.ledger_set_id
1198 AND gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
1199 AND gll.object_type_code = 'S'
1200 AND gll.automatically_created_flag = 'N'
1201 AND glasna.ledger_id = gllsa.ledger_set_id
1202 AND glasna.all_segment_value_flag = 'Y'
1203 AND NVL(glasna.status_code, 'X') NOT IN ('I', 'U')
1204 AND glas.access_set_id = glasna.access_set_id
1205 AND glas.security_segment_code <> 'F'
1206 AND glsvh.flex_value_set_id = glas.secured_seg_value_set_id
1207 AND glsvh.parent_flex_value = 'T'
1208 AND NVL(glsvh.status_code, 'X') <> 'I');
1209
1210 row_count := SQL%ROWCOUNT;
1211 GL_MESSAGE.Write_Log(msg_name => 'SHRD0117',
1212 token_num => 2,
1213 t1 => 'NUM',
1214 v1 => TO_CHAR(row_count),
1215 t2 => 'TABLE',
1216 v2 => 'GL_ACCESS_SET_ASSIGN_INT');
1217 row_count := 0;
1218
1219 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1220 GL_MESSAGE.Write_Log
1221 (msg_name => 'SHRD0180',
1222 token_num => 2,
1223 t1 => 'ROUTINE',
1224 v1 => 'Populate_Temp_Table()',
1225 t2 => 'ACTION',
1229 'with parent segment values assigned...');
1226 v2 => 'Inserting records into ' ||
1227 'GL_ACCESS_SET_ASSIGN_INT for access sets ' ||
1228 'containing changed explicit ledger sets ' ||
1230 END IF;
1231
1232 INSERT into GL_ACCESS_SET_ASSIGN_INT
1233 (access_set_id, ledger_id, segment_value, access_privilege_code,
1234 status_code, parent_record_id, last_update_date, last_updated_by,
1235 last_update_login, creation_date, created_by, start_date, end_date)
1236 (SELECT glasna.access_set_id, gllsa.ledger_id,
1237 glsvh.child_flex_value, glasna.access_privilege_code,
1238 gllsa.status_code, glasna.record_id, SYSDATE,
1239 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1240 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
1241 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1242 glasna.start_date, glasna.end_date
1243 FROM GL_LEDGER_SET_ASSIGNMENTS gllsa,
1244 GL_LEDGERS gll,
1245 GL_ACCESS_SET_NORM_ASSIGN glasna,
1246 GL_ACCESS_SETS glas,
1247 GL_SEG_VAL_HIERARCHIES glsvh
1248 WHERE gllsa.status_code IN ('I', 'D')
1249 AND gll.ledger_id = gllsa.ledger_set_id
1250 AND gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
1251 AND gll.object_type_code = 'S'
1252 AND gll.automatically_created_flag = 'N'
1253 AND glasna.ledger_id = gllsa.ledger_set_id
1254 AND glasna.all_segment_value_flag = 'N'
1255 AND glasna.segment_value_type_code = 'C'
1256 AND NVL(glasna.status_code, 'X') NOT IN ('I', 'U')
1257 AND glas.access_set_id = glasna.access_set_id
1258 AND glas.security_segment_code <> 'F'
1259 AND glsvh.flex_value_set_id = glas.secured_seg_value_set_id
1260 AND glsvh.parent_flex_value = glasna.segment_value
1261 AND NVL(glsvh.status_code, 'X') <> 'I');
1262
1263 row_count := SQL%ROWCOUNT;
1264 GL_MESSAGE.Write_Log(msg_name => 'SHRD0117',
1265 token_num => 2,
1266 t1 => 'NUM',
1267 v1 => TO_CHAR(row_count),
1268 t2 => 'TABLE',
1269 v2 => 'GL_ACCESS_SET_ASSIGN_INT');
1270 row_count := 0;
1271
1272 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1273 GL_MESSAGE.Write_Log
1274 (msg_name => 'SHRD0180',
1275 token_num => 2,
1276 t1 => 'ROUTINE',
1277 v1 => 'Populate_Temp_Table()',
1278 t2 => 'ACTION',
1279 v2 => 'Inserting records into ' ||
1280 'GL_ACCESS_SET_ASSIGN_INT for implicit ' ||
1281 'access sets associated with changed ' ||
1282 'explicit ledger sets... ');
1283 END IF;
1284
1285 INSERT into GL_ACCESS_SET_ASSIGN_INT
1286 (access_set_id, ledger_id, segment_value, access_privilege_code,
1287 status_code, parent_record_id, last_update_date, last_updated_by,
1288 last_update_login, creation_date, created_by, start_date, end_date)
1289 (SELECT glasna.access_set_id, gllsa.ledger_id,
1290 NVL(glasna.segment_value, '-1'), glasna.access_privilege_code,
1291 gllsa.status_code, glasna.record_id, SYSDATE,
1292 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1293 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
1294 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1295 glasna.start_date, glasna.end_date
1296 FROM GL_LEDGER_SET_ASSIGNMENTS gllsa,
1297 GL_LEDGERS gll,
1298 GL_ACCESS_SET_NORM_ASSIGN glasna
1299 WHERE gllsa.status_code IN ('I', 'D')
1300 AND gll.ledger_id = gllsa.ledger_set_id
1301 AND gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
1302 AND gll.automatically_created_flag = 'N'
1303 AND gll.object_type_code = 'S'
1304 AND glasna.access_set_id = gll.implicit_access_set_id
1305 AND glasna.ledger_id = gllsa.ledger_set_id
1306 AND glasna.status_code = 'I'
1307 AND glasna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID);
1308
1309 row_count := SQL%ROWCOUNT;
1310 GL_MESSAGE.Write_Log(msg_name => 'SHRD0117',
1311 token_num => 2,
1312 t1 => 'NUM',
1313 v1 => TO_CHAR(row_count),
1314 t2 => 'TABLE',
1315 v2 => 'GL_ACCESS_SET_ASSIGN_INT');
1316 row_count := 0;
1317
1318 ELSIF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE = 'AS') THEN
1319
1320 -- This section will process changes in explicit access sets.
1321 -- Different statement will be run depending on the type
1322 -- of changes occurred as indicated by the checks earlier.
1323
1324 row_count := 0;
1325
1326 IF (as_single_val_changed) THEN
1327
1328 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1329 GL_MESSAGE.Write_Log
1330 (msg_name => 'SHRD0180',
1331 token_num => 2,
1332 t1 => 'ROUTINE',
1333 v1 => 'Populate_Temp_Table()',
1334 t2 => 'ACTION',
1335 v2 => 'Inserting records into ' ||
1336 'GL_ACCESS_SET_ASSIGN_INT for changed ' ||
1337 'access sets having full ledger access or ' ||
1338 'access assignments with single segment ' ||
1339 'value assigned...');
1340 END IF;
1341
1342 INSERT into GL_ACCESS_SET_ASSIGN_INT
1343 (access_set_id, ledger_id, segment_value, access_privilege_code,
1344 status_code, parent_record_id, last_update_date, last_updated_by,
1345 last_update_login, creation_date, created_by, start_date, end_date)
1346 (SELECT glasna.access_set_id,
1347 DECODE(gllsa.ledger_id,
1348 NULL, glasna.ledger_id, gllsa.ledger_id),
1349 NVL(glasna.segment_value, '-1'), glasna.access_privilege_code,
1350 glasna.status_code, glasna.record_id, SYSDATE,
1351 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1352 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
1353 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1354 glasna.start_date, glasna.end_date
1355 FROM GL_ACCESS_SET_NORM_ASSIGN glasna,
1356 GL_ACCESS_SETS glas,
1357 GL_LEDGER_SET_ASSIGNMENTS gllsa
1358 WHERE glasna.status_code IN ('I', 'D')
1362 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
1359 AND glasna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
1360 AND glas.access_set_id = glasna.access_set_id
1361 AND glas.chart_of_accounts_id =
1363 AND glas.automatically_created_flag = 'N'
1364 AND ( ( glasna.all_segment_value_flag = 'N'
1365 AND glasna.segment_value_type_code = 'S')
1366 OR glas.security_segment_code = 'F')
1367 AND gllsa.ledger_set_id(+) = glasna.ledger_id
1368 AND NVL(gllsa.status_code(+), 'X') <> 'I');
1369 -- gllsa never has U records
1370
1371 row_count := SQL%ROWCOUNT;
1372 GL_MESSAGE.Write_Log(msg_name => 'SHRD0117',
1373 token_num => 2,
1374 t1 => 'NUM',
1375 v1 => TO_CHAR(row_count),
1376 t2 => 'TABLE',
1377 v2 => 'GL_ACCESS_SET_ASSIGN_INT');
1378 row_count := 0;
1379 END IF; -- IF (as_single_val_changed) ...
1380
1381 IF (as_all_val_changed) THEN
1382
1383 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1384 GL_MESSAGE.Write_Log
1385 (msg_name => 'SHRD0180',
1386 token_num => 2,
1387 t1 => 'ROUTINE',
1388 v1 => 'Populate_Temp_Table()',
1389 t2 => 'ACTION',
1390 v2 => 'Inserting records into ' ||
1391 'GL_ACCESS_SET_ASSIGN_INT for changed ' ||
1392 'access sets having access assignments ' ||
1393 'with all segment values assigned...');
1394 END IF;
1395
1396 INSERT into GL_ACCESS_SET_ASSIGN_INT
1397 (access_set_id, ledger_id, segment_value, access_privilege_code,
1398 status_code, parent_record_id, last_update_date, last_updated_by,
1399 last_update_login, creation_date, created_by, start_date, end_date)
1400 (SELECT glasna.access_set_id,
1401 DECODE(gllsa.ledger_id,
1402 NULL, glasna.ledger_id, gllsa.ledger_id),
1403 glsvh.child_flex_value, glasna.access_privilege_code,
1404 glasna.status_code, glasna.record_id, SYSDATE,
1405 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1406 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
1407 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1408 glasna.start_date, glasna.end_date
1409 FROM GL_ACCESS_SET_NORM_ASSIGN glasna,
1410 GL_ACCESS_SETS glas,
1411 GL_LEDGER_SET_ASSIGNMENTS gllsa,
1412 GL_SEG_VAL_HIERARCHIES glsvh
1413 WHERE glasna.status_code IN ('I', 'D', 'U')
1414 AND glasna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
1415 AND glasna.all_segment_value_flag = 'Y'
1416 AND glas.access_set_id = glasna.access_set_id
1417 AND glas.chart_of_accounts_id =
1418 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
1419 AND glas.automatically_created_flag = 'N'
1420 AND glas.security_segment_code <> 'F'
1421 AND gllsa.ledger_set_id(+) = glasna.ledger_id
1422 AND NVL(gllsa.status_code(+), 'X') <> 'I'
1423 AND glsvh.flex_value_set_id = glas.secured_seg_value_set_id
1424 AND glsvh.parent_flex_value = 'T'
1425 AND NVL(glsvh.status_code, 'X') <> 'I');
1426
1427 row_count := SQL%ROWCOUNT;
1428 GL_MESSAGE.Write_Log(msg_name => 'SHRD0117',
1429 token_num => 2,
1430 t1 => 'NUM',
1431 v1 => TO_CHAR(row_count),
1432 t2 => 'TABLE',
1433 v2 => 'GL_ACCESS_SET_ASSIGN_INT');
1434 row_count := 0;
1435 END IF; -- IF (as_all_val_changed) ...
1436
1437 IF (as_parent_val_changed) THEN
1438
1439 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1440 GL_MESSAGE.Write_Log
1441 (msg_name => 'SHRD0180',
1442 token_num => 2,
1443 t1 => 'ROUTINE',
1444 v1 => 'Populate_Temp_Table()',
1445 t2 => 'ACTION',
1446 v2 => 'Inserting records into ' ||
1447 'GL_ACCESS_SET_ASSIGN_INT for changed ' ||
1448 'access sets having parent segment values ' ||
1449 'assigned...');
1450 END IF;
1451
1452 INSERT into GL_ACCESS_SET_ASSIGN_INT
1453 (access_set_id, ledger_id, segment_value, access_privilege_code,
1454 status_code, parent_record_id, last_update_date, last_updated_by,
1455 last_update_login, creation_date, created_by, start_date, end_date)
1456 (SELECT glasna.access_set_id,
1457 DECODE(gllsa.ledger_id,
1458 NULL, glasna.ledger_id, gllsa.ledger_id),
1459 glsvh.child_flex_value, glasna.access_privilege_code,
1460 glasna.status_code, glasna.record_id, SYSDATE,
1461 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1462 GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
1463 GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
1464 glasna.start_date, glasna.end_date
1465 FROM GL_ACCESS_SET_NORM_ASSIGN glasna,
1466 GL_ACCESS_SETS glas,
1467 GL_LEDGER_SET_ASSIGNMENTS gllsa,
1468 GL_SEG_VAL_HIERARCHIES glsvh
1469 WHERE glasna.status_code IN ('I', 'D', 'U')
1470 AND glasna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
1471 AND glasna.all_segment_value_flag = 'N'
1472 AND glasna.segment_value_type_code = 'C'
1473 AND glas.access_set_id = glasna.access_set_id
1474 AND glas.chart_of_accounts_id =
1475 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
1476 AND glas.automatically_created_flag = 'N'
1477 AND glas.security_segment_code <> 'F'
1478 AND gllsa.ledger_set_id(+) = glasna.ledger_id
1479 AND NVL(gllsa.status_code(+), 'X') <> 'I'
1480 AND glsvh.flex_value_set_id = glas.secured_seg_value_set_id
1481 AND glsvh.parent_flex_value = glasna.segment_value
1482 AND NVL(glsvh.status_code, 'X') <> 'I');
1483
1484 row_count := SQL%ROWCOUNT;
1485 GL_MESSAGE.Write_Log(msg_name => 'SHRD0117',
1486 token_num => 2,
1487 t1 => 'NUM',
1488 v1 => TO_CHAR(row_count),
1489 t2 => 'TABLE',
1490 v2 => 'GL_ACCESS_SET_ASSIGN_INT');
1491 row_count := 0;
1495
1492 END IF; -- IF (as_parent_val_changed) ...
1493
1494 END IF; -- IF (...OP_MODE IN ('SH', 'FF')) THEN ...
1496 ELSE
1497
1498 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1499 GL_MESSAGE.Write_Log
1500 (msg_name => 'FLAT0017',
1501 token_num => 1,
1502 t1 => 'OP_MODE',
1503 v1 => GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE);
1504 END IF;
1505
1506 END IF; -- IF (cont_processing...) THEN ...
1507
1508 -- Commit all work
1509 FND_CONCURRENT.Af_Commit;
1510
1511 GL_MESSAGE.Func_Succ
1512 (func_name => 'GL_FLATTEN_ACCESS_SETS.Populate_Temp_Table');
1513
1514 RETURN TRUE;
1515
1516 EXCEPTION
1517 WHEN OTHERS THEN
1518 GL_MESSAGE.Write_Log
1519 (msg_name => 'SHRD0203',
1520 token_num => 2,
1521 t1 => 'FUNCTION',
1522 v1 => 'GL_FLATTEN_ACCESS_SETS.Populate_Temp_Table()',
1523 t2 => 'SQLERRMC',
1524 v2 => SQLERRM);
1525
1526 -- Rollback
1527 FND_CONCURRENT.Af_Rollback;
1528
1529 GL_MESSAGE.Func_Fail
1530 (func_name => 'GL_FLATTEN_ACCESS_SETS.Populate_Temp_Table');
1531
1532 RETURN FALSE;
1533
1534 END Populate_Temp_Table;
1535
1536 -- ******************************************************************
1537
1538 FUNCTION Enable_Record RETURN BOOLEAN IS
1539 row_count NUMBER :=0;
1540 curr_as_id NUMBER :=0;
1541 curr_ldg_id NUMBER :=0;
1542 curr_seg_val VARCHAR2(15) := NULL;
1543 tot_row_fetch NUMBER :=0;
1544
1545 CURSOR dup_access_assign_cursor IS
1546 SELECT DISTINCT MIN(glasai.access_set_id),
1547 MIN(glasai.ledger_id), MIN(glasai.segment_value)
1548 FROM GL_ACCESS_SET_ASSIGN_INT glasai,
1549 GL_ACCESS_SETS glas
1550 WHERE glasai.status_code = 'I'
1551 AND glasai.access_set_id > 0
1552 AND glas.access_set_id = ABS(glasai.access_set_id)
1553 AND glas.automatically_created_flag = 'N'
1554 GROUP BY glasai.access_set_id, glasai.ledger_id,
1555 glasai.segment_value
1556 HAVING count(*) > 1;
1557
1558 BEGIN
1559
1560 -- After all changes have been processed, the program needs to
1561 -- determine which records should be enabled to take effect
1562 -- for a particular access set/ledger/segment value combination.
1563 -- This routine will do so by changing the access set ID to be
1564 -- negative for disabled records (positive for enabled ones).
1565 -- This routine will not process any records associated with
1566 -- implicit access sets. The algorithm used to maintain implicit
1567 -- access sets should guarantee that there will only be one
1568 -- record for each date range, in which case the record should
1569 -- always be enabled.
1570 -- The sequence of events is as follows:
1571 -- 1) Enable records in GL_ACCESS_SET_ASSIGNMENTS since the
1572 -- effective record might be deleted.
1573 -- 2) Disable records in GL_ACCESS_SET_ASSIGN_INT based on effective
1574 -- records in GL_ACCESS_SET_ASSIGNMENTS.
1575 -- 3) Pick the record in GL_ACCESS_SET_ASSIGN_INT having the greatest
1576 -- access code with the smallest rowid, then disable all other records.
1577 -- 4) Disable records in GL_ACCESS_SET_ASSIGNMENTS if there exists an
1578 -- effective record in GL_ACCESS_SET_ASSIGN_INT that has a higher
1579 -- access privilege.
1580
1581 GL_MESSAGE.Func_Ent
1582 (func_name => 'GL_FLATTEN_ACCESS_SETS.Enable_Record');
1583
1584 -- Pick a record with access_privilege_code of B that has
1585 -- the smallest rowid, and disable all other records
1586 -- Note this will only happen when the current effective
1587 -- record has been deleted. Thus we can make use of the
1588 -- 'D' records in GL_ACCESS_SET_ASSIGN_INT to search
1589 -- for the new effective record.
1590 -- Also here we do not need to check if the access sets are
1591 -- implicit or not, since only explicit access sets will ever
1592 -- have records with a negative access_set_id. This is because
1593 -- for implicit access sets they should never have more than
1594 -- 1 record for each access set/ledger/segment value combination.
1595 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1596 GL_MESSAGE.Write_Log
1597 (msg_name => 'SHRD0180',
1598 token_num => 2,
1599 t1 => 'ROUTINE',
1600 v1 => 'Enable_Record()',
1601 t2 => 'ACTION',
1602 v2 => 'Searching for disabled records in ' ||
1603 'GL_ACCESS_SET_ASSIGNMENTS with access ' ||
1604 'privilege B which has the smallest ' ||
1605 'rowid, then enable this record...');
1606 END IF;
1607
1608 UPDATE GL_ACCESS_SET_ASSIGNMENTS glasa1
1609 SET glasa1.access_set_id = -glasa1.access_set_id
1610 WHERE glasa1.rowid IN
1611 (SELECT MIN(glasa2.rowid)
1612 FROM GL_ACCESS_SET_ASSIGN_INT glasai,
1613 GL_ACCESS_SET_ASSIGNMENTS glasa2,
1614 GL_ACCESS_SET_ASSIGNMENTS glasa3
1615 WHERE glasai.status_code = 'D'
1616 AND glasa2.access_set_id = -glasai.access_set_id
1617 AND glasa2.ledger_id = glasai.ledger_id
1618 AND glasa2.segment_value = glasai.segment_value
1619 AND glasa2.access_privilege_code = 'B'
1620 AND glasa3.access_set_id(+) = glasai.access_set_id
1621 AND glasa3.ledger_id(+) = glasai.ledger_id
1622 AND glasa3.segment_value(+) = glasai.segment_value
1623 AND glasa3.rowid is NULL
1624 GROUP BY glasa2.access_set_id, glasa2.ledger_id,
1625 glasa2.segment_value);
1626
1627 row_count := SQL%ROWCOUNT;
1628 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
1629 token_num => 2,
1630 t1 => 'NUM',
1631 v1 => TO_CHAR(row_count),
1632 t2 => 'TABLE',
1636 -- Then, pick a record with access_privilege of R having the
1633 v2 => 'GL_ACCESS_SET_ASSIGNMENTS');
1634 row_count := 0;
1635
1637 -- smallest rowid and disable all other records.
1638 -- This statement will only update records if the first
1639 -- statement does not enable any record, since otherwise
1640 -- it will never pass the outer join test.
1641 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1642 GL_MESSAGE.Write_Log
1643 (msg_name => 'SHRD0180',
1644 token_num => 2,
1645 t1 => 'ROUTINE',
1646 v1 => 'Enable_Record()',
1647 t2 => 'ACTION',
1648 v2 => 'Updating records in ' ||
1649 'GL_ACCESS_SET_ASSIGNMENTS to enable ' ||
1650 'access assignments using the smallest ' ||
1651 'rowid with access privilege R...');
1652 END IF;
1653
1654 UPDATE GL_ACCESS_SET_ASSIGNMENTS glasa1
1655 SET glasa1.access_set_id = -glasa1.access_set_id
1656 WHERE glasa1.rowid IN
1657 (SELECT MIN(glasa2.rowid)
1658 FROM GL_ACCESS_SET_ASSIGN_INT glasai,
1659 GL_ACCESS_SET_ASSIGNMENTS glasa2,
1660 GL_ACCESS_SET_ASSIGNMENTS glasa3
1661 WHERE glasai.status_code = 'D'
1662 AND glasa2.access_set_id = -glasai.access_set_id
1663 AND glasa2.ledger_id = glasai.ledger_id
1664 AND glasa2.segment_value = glasai.segment_value
1665 AND glasa2.access_privilege_code = 'R'
1666 AND glasa3.access_set_id(+) = glasai.access_set_id
1667 AND glasa3.ledger_id(+) = glasai.ledger_id
1668 AND glasa3.segment_value(+) = glasai.segment_value
1669 AND glasa3.rowid is NULL
1670 GROUP BY glasa2.access_set_id, glasa2.ledger_id,
1671 glasa2.segment_value);
1672
1673 row_count := SQL%ROWCOUNT;
1674 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
1675 token_num => 2,
1676 t1 => 'NUM',
1677 v1 => TO_CHAR(row_count),
1678 t2 => 'TABLE',
1679 v2 => 'GL_ACCESS_SET_ASSIGNMENTS');
1680 row_count := 0;
1681
1682 -- Check if there exists a record in GL_ACCESS_SET_ASSIGNMENTS
1683 -- that has a higher or equal access privilege. If so, disable the
1684 -- corresponding record(s) in GL_ACCESS_SET_ASSIGN_INT.
1685 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1686 GL_MESSAGE.Write_Log
1687 (msg_name => 'SHRD0180',
1688 token_num => 2,
1689 t1 => 'ROUTINE',
1690 v1 => 'Enable_Record()',
1691 t2 => 'ACTION',
1692 v2 => 'Updating records in ' ||
1693 'GL_ACCESS_SET_ASSIGN_INT based on ' ||
1694 'GL_ACCESS_SET_ASSIGNMENTS to disable ' ||
1695 'access assignments...');
1696 END IF;
1697
1698 UPDATE GL_ACCESS_SET_ASSIGN_INT glasai1
1699 SET glasai1.access_set_id = -glasai1.access_set_id
1700 WHERE glasai1.rowid IN
1701 (SELECT glasai2.rowid
1702 FROM GL_ACCESS_SET_ASSIGN_INT glasai2,
1703 GL_ACCESS_SETS glas,
1704 GL_ACCESS_SET_ASSIGNMENTS glasa
1705 WHERE glasai2.status_code = 'I'
1706 AND glasai2.access_set_id > 0
1707 AND glas.access_set_id = glasai2.access_set_id
1708 AND glas.automatically_created_flag = 'N'
1709 AND glasa.access_set_id = glasai2.access_set_id
1710 AND glasa.ledger_id = glasai2.ledger_id
1711 AND glasa.segment_value = glasai2.segment_value
1712 AND ( glasa.access_privilege_code = 'B'
1713 OR ( glasa.access_privilege_code = 'R'
1714 AND glasai2.access_privilege_code = 'R')));
1715
1716 row_count := SQL%ROWCOUNT;
1717 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
1718 token_num => 2,
1719 t1 => 'NUM',
1720 v1 => TO_CHAR(row_count),
1721 t2 => 'TABLE',
1722 v2 => 'GL_ACCESS_SET_ASSIGN_INT');
1723 row_count := 0;
1724
1725
1726 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1727 GL_MESSAGE.Write_Log
1728 (msg_name => 'SHRD0180',
1729 token_num => 2,
1730 t1 => 'ROUTINE',
1731 v1 => 'Enable_Record()',
1732 t2 => 'ACTION',
1733 v2 => 'Searching for records in ' ||
1734 'GL_ACCESS_SET_ASSIGN_INT with access ' ||
1735 'privilege B which has the smallest ' ||
1736 'rowid, then disable all other records...');
1737 END IF;
1738
1739 -- For efficiency purposes, if all the records within
1740 -- GL_ACCESS_SET_ASSIGN_INT are unique, i.e. only 1 record
1741 -- exists in the table for each access_set_id/ledger_id/segment_value
1742 -- combination, the program will not need to run the enabling code
1743 -- within GL_ACCESS_SET_ASSIGN_INT. This is because no rows will
1744 -- be updated in the end.
1745 IF (NOT dup_access_assign_cursor%ISOPEN) THEN
1746 OPEN dup_access_assign_cursor;
1747 END IF;
1748
1749 LOOP
1750 FETCH dup_access_assign_cursor
1751 INTO curr_as_id, curr_ldg_id, curr_seg_val;
1752 EXIT WHEN dup_access_assign_cursor%NOTFOUND;
1753
1754 IF (curr_as_id IS NOT NULL) THEN
1755
1756 /*
1757 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1758 GL_MESSAGE.Write_Log
1759 (msg_name => 'FLAT0020',
1760 token_num => 3,
1761 t1 => 'AS_ID',
1762 v1 => TO_CHAR(curr_as_id),
1763 t2 => 'LDG_ID',
1764 v2 => TO_CHAR(curr_ldg_id),
1765 t3 => 'SEG_VAL',
1766 v3 => curr_seg_val);
1767 END IF;
1768 */
1769
1770 -- IF (NOT dup_access_assign_cursor%NOTFOUND) THEN
1771
1772 -- Then, pick a record with access_privilege_code of B that has
1773 -- the smallest rowid, and disable all other records
1774
1775 UPDATE GL_ACCESS_SET_ASSIGN_INT glasai1
1779 AND glasai1.segment_value = curr_seg_val
1776 SET glasai1.access_set_id = -glasai1.access_set_id
1777 WHERE glasai1.access_set_id = curr_as_id
1778 AND glasai1.ledger_id = curr_ldg_id
1780 AND glasai1.status_code = 'I'
1781 AND EXISTS
1782 (SELECT 1
1783 FROM GL_ACCESS_SET_ASSIGN_INT glasai2,
1784 GL_ACCESS_SETS glas
1785 WHERE glasai2.status_code IN ('I', 'U')
1786 AND glasai2.access_set_id = glasai1.access_set_id
1787 AND glasai2.ledger_id = glasai1.ledger_id
1788 AND glasai2.segment_value = glasai1.segment_value
1789 AND glas.access_set_id = glasai1.access_set_id
1790 AND glas.automatically_created_flag = 'N'
1791 AND ( ( glasai2.access_privilege_code =
1792 glasai1.access_privilege_code
1793 AND glasai2.rowid < glasai1.rowid)
1794 OR ( glasai2.access_privilege_code = 'B'
1795 AND glasai1.access_privilege_code = 'R')));
1796
1797 row_count := row_count + 1;
1798
1799 END IF;
1800 END LOOP;
1801
1802 /*
1803 ** IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1804 ** GL_MESSAGE.Write_Log
1805 ** (msg_name => 'FLAT0020',
1806 ** token_num => 1,
1807 ** t1 => 'NUM',
1808 ** v1 => TO_CHAR(dup_access_assign_cursor%ROWCOUNT));
1809 ** END IF;
1810 */
1811 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
1812 token_num => 2,
1813 t1 => 'NUM',
1814 v1 => TO_CHAR(row_count),
1815 t2 => 'TABLE',
1816 v2 => 'GL_ACCESS_SET_ASSIGN_INT');
1817 row_count := 0;
1818
1819 CLOSE dup_access_assign_cursor;
1820
1821 -- Here the only conflict that needs to be resolve is
1822 -- that the enabled record in GL_ACCESS_SET_ASSIGNMENTS has
1823 -- access privilege of R, while the enabled record in
1824 -- GL_ACCESS_SET_ASSIGN_INT has access privilege of B.
1825 -- In this case, the record in GL_ACCESS_SET_ASSIGNMENTS will
1826 -- be disabled.
1827
1828 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1829 GL_MESSAGE.Write_Log
1830 (msg_name => 'SHRD0180',
1831 token_num => 2,
1832 t1 => 'ROUTINE',
1833 v1 => 'Enable_Record()',
1834 t2 => 'ACTION',
1835 v2 => 'Updating records in ' ||
1836 'GL_ACCESS_SET_ASSIGNMENTS to disable ' ||
1837 'those with access privilege R if there ' ||
1838 'exists a record in GL_ACCESS_SET_ASSIGN_INT '||
1839 'with access privilege of B...');
1840 END IF;
1841
1842 UPDATE GL_ACCESS_SET_ASSIGNMENTS glasa
1843 SET glasa.access_set_id = -glasa.access_set_id
1844 WHERE glasa.access_privilege_code = 'R'
1845 AND glasa.access_set_id > 0
1846 AND (glasa.access_set_id, glasa.ledger_id,
1847 glasa.segment_value) IN
1848 (SELECT DISTINCT
1849 glasai.access_set_id, glasai.ledger_id,
1850 glasai.segment_value
1851 FROM GL_ACCESS_SET_ASSIGN_INT glasai,
1852 GL_ACCESS_SETS glas
1853 WHERE glasai.status_code IN ('I', 'U')
1854 AND glasai.access_privilege_code = 'B'
1855 AND glasai.access_set_id > 0
1856 AND glas.access_set_id = glasai.access_set_id
1857 AND glas.automatically_created_flag = 'N');
1858
1859 row_count := SQL%ROWCOUNT;
1860 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
1861 token_num => 2,
1862 t1 => 'NUM',
1863 v1 => TO_CHAR(row_count),
1864 t2 => 'TABLE',
1865 v2 => 'GL_ACCESS_SET_ASSIGNMENTS');
1866 row_count := 0;
1867
1868 GL_MESSAGE.Func_Succ
1869 (func_name => 'GL_FLATTEN_ACCESS_SETS.Enable_Record');
1870
1871 RETURN TRUE;
1872
1873 EXCEPTION
1874 WHEN OTHERS THEN
1875 GL_MESSAGE.Write_Log
1876 (msg_name => 'SHRD0203',
1877 token_num => 2,
1878 t1 => 'FUNCTION',
1879 v1 => 'GL_FLATTEN_ACCESS_SETS.Enable_Record()',
1880 t2 => 'SQLERRMC',
1881 v2 => SQLERRM);
1882
1883 GL_MESSAGE.Func_Fail
1884 (func_name => 'GL_FLATTEN_ACCESS_SETS.Enable_Record');
1885
1886 RETURN FALSE;
1887
1888 END Enable_Record;
1889
1890 -- ******************************************************************
1891
1892 FUNCTION Clean_Up_By_Coa RETURN BOOLEAN IS
1893 row_count NUMBER := 0;
1894 BEGIN
1895
1896 GL_MESSAGE.Func_Ent
1897 (func_name => 'GL_FLATTEN_ACCESS_SETS.Clean_Up_By_Coa');
1898
1899 -- Run the following statements using the right parameters
1900 -- to clean up GL_ACCESS_SET_NORM_ASSIGN
1901
1902 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1903 GL_MESSAGE.Write_Log
1904 (msg_name => 'SHRD0180',
1905 token_num => 2,
1906 t1 => 'ROUTINE',
1907 v1 => 'Clean_Up_By_Coa()',
1908 t2 => 'ACTION',
1909 v2 => 'Deleting records from GL_ACCESS_SET_NORM_ASSIGN...');
1910 END IF;
1911
1912 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE = 'AS') THEN
1913
1914 DELETE from GL_ACCESS_SET_NORM_ASSIGN
1915 WHERE status_code = 'D'
1916 AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
1917 AND access_set_id IN
1918 (SELECT access_set_id
1919 FROM GL_ACCESS_SETS
1920 WHERE chart_of_accounts_id =
1921 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
1922 AND automatically_created_flag = 'N');
1923
1924 ELSIF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE IN ('LH', 'VH')) THEN
1925
1926 DELETE from GL_ACCESS_SET_NORM_ASSIGN
1927 WHERE status_code = 'D'
1928 AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
1929 AND access_set_id IN
1930 (SELECT implicit_access_set_id
1931 FROM GL_LEDGERS
1935
1932 WHERE chart_of_accounts_id =
1933 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
1934 AND object_type_code = 'L');
1936 END IF;
1937
1938 row_count := SQL%ROWCOUNT;
1939 GL_MESSAGE.Write_Log(msg_name => 'SHRD0119',
1940 token_num => 2,
1941 t1 => 'NUM',
1942 v1 => TO_CHAR(row_count),
1943 t2 => 'TABLE',
1944 v2 => 'GL_ACCESS_SET_NORM_ASSIGN');
1945 row_count := 0;
1946
1947 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1948 GL_MESSAGE.Write_Log
1949 (msg_name => 'SHRD0180',
1950 token_num => 2,
1951 t1 => 'ROUTINE',
1952 v1 => 'Clean_Up_By_Coa()',
1953 t2 => 'ACTION',
1954 v2 => 'Updating records in GL_ACCESS_SET_NORM_ASSIGN...');
1955 END IF;
1956
1957 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE IN ('LS', 'VS')) THEN
1958
1959 UPDATE GL_ACCESS_SET_NORM_ASSIGN
1960 SET status_code = NULL, request_id = NULL
1961 WHERE status_code IN ('I', 'U')
1962 AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
1963 AND access_set_id IN
1964 (SELECT implicit_access_set_id
1965 FROM GL_LEDGERS
1966 WHERE chart_of_accounts_id =
1967 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
1968 AND object_type_code = 'S');
1969
1970 ELSIF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE = 'AS') THEN
1971
1972 UPDATE GL_ACCESS_SET_NORM_ASSIGN
1973 SET status_code = NULL, request_id = NULL
1974 WHERE status_code IN ('I', 'U')
1975 AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
1976 AND access_set_id IN
1977 (SELECT access_set_id
1978 FROM GL_ACCESS_SETS
1979 WHERE chart_of_accounts_id =
1980 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
1981 AND automatically_created_flag = 'N');
1982
1983 ELSIF (GL_FLATTEN_SETUP_DATA.GLSTFL_OP_MODE IN ('LH', 'VH')) THEN
1984
1985 UPDATE GL_ACCESS_SET_NORM_ASSIGN
1986 SET status_code = NULL, request_id = NULL
1987 WHERE status_code IN ('I', 'U')
1988 AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
1989 AND access_set_id IN
1990 (SELECT implicit_access_set_id
1991 FROM GL_LEDGERS
1992 WHERE chart_of_accounts_id =
1993 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
1994 AND object_type_code = 'L');
1995
1996 END IF;
1997
1998 row_count := SQL%ROWCOUNT;
1999 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
2000 token_num => 2,
2001 t1 => 'NUM',
2002 v1 => TO_CHAR(row_count),
2003 t2 => 'TABLE',
2004 v2 => 'GL_ACCESS_SET_NORM_ASSIGN');
2005 row_count := 0;
2006
2007 GL_MESSAGE.Func_Succ
2008 (func_name => 'GL_FLATTEN_ACCESS_SETS.Clean_Up_By_Coa');
2009
2010 RETURN TRUE;
2011
2012 EXCEPTION
2013 WHEN OTHERS THEN
2014 GL_MESSAGE.Write_Log
2015 (msg_name => 'SHRD0203',
2016 token_num => 2,
2017 t1 => 'FUNCTION',
2018 v1 => 'GL_FLATTEN_ACCESS_SETS.Clean_Up_By_Coa()',
2019 t2 => 'SQLERRMC',
2020 v2 => SQLERRM);
2021
2022 GL_MESSAGE.Func_Fail
2023 (func_name => 'GL_FLATTEN_ACCESS_SETS.Clean_Up_By_Coa');
2024
2025 RETURN FALSE;
2026
2027 END Clean_Up_By_Coa;
2028
2029 -- ******************************************************************
2030
2031 END GL_FLATTEN_ACCESS_SETS;
2032
[Home] [Help]