1 PACKAGE BODY gl_security_pkg AS
2 /* $Header: gluoaseb.pls 120.21 2007/11/26 10:36:34 dthakker ship $ */
3
4 --
5 -- Global variables
6 --
7 RESPONSIBILITY_ID FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE;
8 LDGR_ID GL_LEDGERS.LEDGER_ID%TYPE;
9 ACCESS_ID GL_ACCESS_SETS.ACCESS_SET_ID%TYPE;
10 COA_ID GL_ACCESS_SETS.CHART_OF_ACCOUNTS_ID%TYPE;
11 SECURITY_SEGMENT_CODE GL_ACCESS_SETS.SECURITY_SEGMENT_CODE%TYPE;
12 BAL_MGMT_SEG_COL_NAME VARCHAR2(2000);
13
14 -- Added under the bug 4730993
15 RESP_APPLICATION_ID FND_RESPONSIBILITY.APPLICATION_ID%TYPE;
16
17 -- If init() has been executed, the inilialized value is TRUE
18 initialized boolean := FALSE;
19
20 -- Cache secured segment column name. If the segment is security enabled
21 -- ,the value of the flag is TRUE
22 seg1_flag boolean;
23 seg2_flag boolean;
24 seg3_flag boolean;
25 seg4_flag boolean;
26 seg5_flag boolean;
27 seg6_flag boolean;
28 seg7_flag boolean;
29 seg8_flag boolean;
30 seg9_flag boolean;
31 seg10_flag boolean;
32 seg11_flag boolean;
33 seg12_flag boolean;
34 seg13_flag boolean;
35 seg14_flag boolean;
36 seg15_flag boolean;
37 seg16_flag boolean;
38 seg17_flag boolean;
39 seg18_flag boolean;
40 seg19_flag boolean;
41 seg20_flag boolean;
42 seg21_flag boolean;
43 seg22_flag boolean;
44 seg23_flag boolean;
45 seg24_flag boolean;
46 seg25_flag boolean;
47 seg26_flag boolean;
48 seg27_flag boolean;
49 seg28_flag boolean;
50 seg29_flag boolean;
51 seg30_flag boolean;
52
53 TYPE col_name IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
54 seg_col_name col_name;
55
56 --
57 -- PRIVATE FUNCTIONS
58 --
59 PROCEDURE init_global_var IS
60 appl_id NUMBER(15);
61 resp_id NUMBER(15);
62 l_ledger_id NUMBER(15);
63 l_segment_attr_type VARCHAR2(2000);
64 l_segment_column VARCHAR2(2000);
65 flag BOOLEAN := FALSE;
66 BEGIN
67
68 -- Cache Global variables
69 ACCESS_ID := fnd_profile.value('GL_ACCESS_SET_ID');
70 RESPONSIBILITY_ID := fnd_global.resp_id;
71 resp_id := RESPONSIBILITY_ID;
72
73 -- Added under the bug 4730993
74 -- Get application id
75 RESP_APPLICATION_ID := fnd_global.resp_appl_id;
76 appl_id := RESP_APPLICATION_ID;
77
78
79 /* Commented under the bug 4730993
80 -- Get application id
81 SELECT application_id
82 into appl_id
83 FROM FND_RESPONSIBILITY
84 WHERE responsibility_id = resp_id;
85 */
86
87 -- The set of books id from the profile option GL_SET_OF_BKS_ID
88 -- is stored in the local variable ldgr_id.
89 LDGR_ID := to_number(fnd_profile.value_specific('GL_SET_OF_BKS_ID',
90 null,
91 resp_id,
92 appl_id));
93 l_ledger_id := LDGR_ID;
94
95 IF ACCESS_ID IS NULL THEN
96 BEGIN
97 -- Get chart of accounts id based upon Ledger
98 SELECT chart_of_accounts_id
99 INTO COA_ID
100 FROM GL_LEDGERS
101 WHERE ledger_id = l_ledger_id;
102 EXCEPTION
103 WHEN NO_DATA_FOUND THEN
104 RETURN;
105 END;
106 ELSE
107 BEGIN
108 -- Get chart of accounts id based upon Access Set
109 SELECT chart_of_accounts_id, security_segment_code
110 INTO COA_ID, SECURITY_SEGMENT_CODE
111 FROM GL_ACCESS_SETS
112 WHERE access_set_id = ACCESS_ID;
113 EXCEPTION
114 WHEN NO_DATA_FOUND THEN
115 RETURN;
116 END;
117 END IF;
118
119 -- Run for GL_BALANCING and GL_MANAGEMENT conditionally
120 IF SECURITY_SEGMENT_CODE = 'B' THEN
121 l_segment_attr_type := 'GL_BALANCING';
122 flag := FND_FLEX_APIS.GET_SEGMENT_COLUMN(X_APPLICATION_ID => '101',
123 X_ID_FLEX_CODE => 'GL#',
124 X_ID_FLEX_NUM => COA_ID,
125 X_SEG_ATTR_TYPE => l_segment_attr_type,
126 X_APP_COLUMN_NAME => l_segment_column);
127 BAL_MGMT_SEG_COL_NAME := l_segment_column;
128
129 ELSIF SECURITY_SEGMENT_CODE = 'M' THEN
130 l_segment_attr_type := 'GL_MANAGEMENT';
131 flag := FND_FLEX_APIS.GET_SEGMENT_COLUMN(X_APPLICATION_ID => '101',
132 X_ID_FLEX_CODE => 'GL#',
133 X_ID_FLEX_NUM => COA_ID,
134 X_SEG_ATTR_TYPE => l_segment_attr_type,
135 X_APP_COLUMN_NAME => l_segment_column);
136 BAL_MGMT_SEG_COL_NAME := l_segment_column;
137
138 END IF;
139
140 END init_global_var;
141
142 -- Procedure
143 -- init
144 -- Purpose
145 -- To initialize, populate and update GL_BIS_SEGVAL_INT temporary
146 -- table based on segment value security rules.
147 -- This procedure is called from the Discoverer's POST_LOGON trigger.
148 --
149 PROCEDURE init IS
150 Pragma AUTONOMOUS_TRANSACTION; -- 6341771 for discoverer issue
151 program_name VARCHAR2(48);
152 l_module v$session.module%TYPE;
153 l_gl_bis_disco_flag varchar2(1);
154 l_session_id number;
155 BEGIN
156 -- Now there is no need to check for the module name for a session
157 -- as this procedure is always going to be called from the
158 -- Discoverer's POST_LOGON trigger
159 -- Populate the Global Temporary Table GL_BIS_SEGVAL_INT
160 -- with the security rules
161
162 delete from GL_BIS_SEGVAL_INT; -- 6341771, clean up the existing rows
163 init_segval;
164 commit; -- 6341771, execute commit in autonomous transaction
165 END init;
166
167 -- Procedure
168 -- init_segval
169 -- Purpose
170 -- Initialize, populate and update GL_BIS_SEGVAL_INT temporary
171 -- table based on segment value security rules
172 --
173 PROCEDURE init_segval IS
174
175 CURSOR seg_cur(coa_id number) is
176 SELECT sg.application_column_name,
177 sg.flex_value_set_id,
178 sg.segment_num,
179 vs.validation_type
180 FROM FND_FLEX_VALUE_SETS vs, FND_ID_FLEX_SEGMENTS sg
181 WHERE sg.application_id = 101
182 AND sg.id_flex_code = 'GL#'
183 AND sg.id_flex_num = coa_id
184 AND sg.security_enabled_flag = 'Y'
185 AND vs.flex_value_set_id = sg.flex_value_set_id;
186
187 CURSOR rule_cur(resp_id number, v_id number, appl_id number) IS
188 SELECT flex_value_rule_id, parent_flex_value_low
189 FROM fnd_flex_value_rule_usages
190 WHERE application_id = appl_id
191 AND responsibility_id = resp_id
192 AND flex_value_set_id = v_id;
193
194 CURSOR in_range_cur(rule_id number) IS
195 SELECT flex_value_low, flex_value_high
196 FROM fnd_flex_value_rule_lines
197 WHERE flex_value_rule_id = rule_id
198 AND include_exclude_indicator = 'I'
199 ORDER BY nlssort(decode(flex_value_low,
200 NULL,
201 '1',
202 '2' || flex_value_low),
203 'NLS_SORT=BINARY'),
204 nlssort(decode(flex_value_high,
205 NULL,
206 '3',
207 '2' || flex_value_high),
208 'NLS_SORT=BINARY');
209
210 CURSOR ex_range_cur(rule_id number) IS
211 SELECT flex_value_low, flex_value_high
212 FROM fnd_flex_value_rule_lines
213 WHERE flex_value_rule_id = rule_id
214 AND include_exclude_indicator <> 'I'
215 ORDER BY nlssort(decode(flex_value_low,
216 NULL,
217 '1',
218 '2' || flex_value_low),
219 'NLS_SORT=BINARY'),
220 nlssort(decode(flex_value_high,
221 NULL,
222 '3',
223 '2' || flex_value_high),
224 'NLS_SORT=BINARY');
225
226 resp_id NUMBER(15);
227 appl_id NUMBER(15);
228 rule_id NUMBER(15);
229 segment_column_name VARCHAR2(20);
230 value_set_id NUMBER(15);
231 segnum NUMBER(3);
232 validate_type VARCHAR2(1);
233 del_stmt VARCHAR2(200);
234 sql_stmt VARCHAR2(5200);
235 sql_stmt2 VARCHAR2(1000);
236 v_column_name VARCHAR2(240);
237 v_appl_table_name VARCHAR2(240);
238 old_low VARCHAR2(150);
239 old_high VARCHAR2(150);
240 new_low VARCHAR2(150);
241 new_high VARCHAR2(150);
242 parent_segment VARCHAR2(150);
243 allrows boolean;
244 -- sql_stmt is 5200. Reserve 200 for del_stmt, and each extra clause
245 -- is mostly 100 (101 for only the first line) max.
246 max_line CONSTANT NUMBER := 50;
247 count_line NUMBER;
248 count_stmt NUMBER;
249 first_row boolean;
250 first_rule_range boolean;
251 pname_pos NUMBER;
252 i NUMBER;
253 BEGIN
254 sql_stmt := NULL;
255 sql_stmt2 := NULL;
256
257 -- Initialize all segN_flag
258 seg1_flag := FALSE;
259 seg2_flag := FALSE;
260 seg3_flag := FALSE;
261 seg4_flag := FALSE;
262 seg5_flag := FALSE;
263 seg6_flag := FALSE;
264 seg7_flag := FALSE;
265 seg8_flag := FALSE;
266 seg9_flag := FALSE;
267 seg10_flag := FALSE;
268 seg11_flag := FALSE;
269 seg12_flag := FALSE;
270 seg13_flag := FALSE;
271 seg14_flag := FALSE;
272 seg15_flag := FALSE;
273 seg16_flag := FALSE;
274 seg17_flag := FALSE;
275 seg18_flag := FALSE;
276 seg19_flag := FALSE;
277 seg20_flag := FALSE;
278 seg21_flag := FALSE;
279 seg22_flag := FALSE;
280 seg23_flag := FALSE;
281 seg24_flag := FALSE;
282 seg25_flag := FALSE;
283 seg26_flag := FALSE;
284 seg27_flag := FALSE;
285 seg28_flag := FALSE;
286 seg29_flag := FALSE;
287 seg30_flag := FALSE;
288
289 -- Initialize table seg_col_name
290 FOR i in 1 .. 30 LOOP
291 seg_col_name(i) := NULL;
292 END LOOP;
293
294 -- Initialize package variables
295 -- RESPONSIBILITY_ID,
296 -- RESP_APPLICATION_ID,
297 -- ACCESS_ID,
298 -- LDGR_ID,
299 -- COA_ID
300 -- SECURITY_SEGMENT_CODE
301 init_global_var;
302
303 IF (COA_ID IS NULL) THEN
304 RETURN;
305 END IF;
306
307 -- Cache Global variables
308 resp_id := RESPONSIBILITY_ID;
309
310 -- Get application_id
311 -- Modified under bug 4730993
312 appl_id := RESP_APPLICATION_ID;
313
314 -- Loop for each security enabled segment
315 OPEN seg_cur(coa_id);
316 LOOP
317 FETCH seg_cur INTO segment_column_name,
318 value_set_id,
319 segnum,
320 validate_type;
321
322 EXIT WHEN seg_cur%NOTFOUND;
323
324 IF validate_type <> 'F' THEN
325 -- Not table validated segment
326 sql_stmt := 'INSERT INTO GL_BIS_SEGVAL_INT( ' ||
327 'segment_column_name,' ||
328 'segment_value, ' ||
329 'parent_segment) ' ||
330 'SELECT ''' || segment_column_name || ''',' ||
331 'flex_value, parent_flex_value_low ' ||
332 'FROM FND_FLEX_VALUES ' ||
333 'WHERE flex_value_set_id=' || value_set_id;
334
335 ELSE
336 -- Table validated segment
337 SELECT value_column_name, application_table_name
338 INTO v_column_name, v_appl_table_name
339 FROM FND_FLEX_VALIDATION_TABLES
340 WHERE flex_value_set_id = value_set_id;
341
342 sql_stmt := 'INSERT INTO GL_BIS_SEGVAL_INT( ' ||
343 'segment_column_name,' ||
344 'segment_value,' ||
345 'parent_segment) ' ||
346 ' SELECT ''' || segment_column_name || ''',' ||
347 v_column_name || ',' || 'NULL' ||
348 ' FROM ' || v_appl_table_name;
349
350 -- Insert parent segment value for table validated segment
351 sql_stmt2 := 'INSERT INTO GL_BIS_SEGVAL_INT( ' ||
352 'segment_column_name,' ||
353 'segment_value, ' ||
354 'parent_segment) ' ||
355 ' SELECT ''' || segment_column_name || ''',' ||
356 ' flex_value, NULL' ||
357 ' FROM FND_FLEX_VALUES ' ||
358 ' WHERE flex_value_set_id= ' || value_set_id ||
359 ' AND summary_flag = ''Y'' ';
360 END IF;
361
362 IF (sql_stmt IS NOT NULL) THEN
363 EXECUTE IMMEDIATE sql_stmt;
364 END IF;
365
366 IF ((sql_stmt2 IS NOT NULL) AND (validate_type = 'F')) THEN
367 EXECUTE IMMEDIATE sql_stmt2;
368 END IF;
369
370 -- Loop for each security rule of the given responsibility and
371 -- and value set id
372
373 OPEN rule_cur(resp_id, value_set_id, appl_id);
374 LOOP
375 FETCH rule_cur INTO rule_id, parent_segment;
376
377 EXIT WHEN rule_cur%NOTFOUND;
378
379 -- Build Dynamic SQL statement to delete segment values that are
380 -- not in the include range of the given segment security rule.
381 -- The program first figures out the gap between all include
382 -- ranges and then deletes segment values in the gap from temporary
383 -- table. To find the gap, we store previous range in old_low and
384 -- old_high and store current range in new_low and new_high. By
385 -- comparing these values, we can obtain the gap between two ranges
386 -- and build additionl where clause SQL statements.
387
388 -- Variables initialization
389 -- Old_low : Stores the From range value of the previous range in
390 -- this security rule.
391 -- Old_high: Stores the To range value of the previous range in
392 -- this security rule.
393 -- New_low : The From range value of the current range in this
394 -- security rule.
395 -- New_high: The To range value of the current range in this
396 -- security rule.
397 -- First_rule_range: The flag to check if the range is the first
398 -- range of the given security rule
399 -- Allrows: If allrows is TRUE, then include every value of this
400 -- segment
401 -- First_row: The flag to check if the beginning of SQL statement
402 -- has been built. If not, the flag is TRUE.
403 -- Count_stmt: If count_stmt = 0, we build the Dynamic SQL starting
404 -- with 'AND'.If cont_stmt > 0, we build the dynamic SQL
405 -- statement starting with 'OR'.
406 -- Count_line: Whenever count_line reaches max_line, we execute the
407 -- current statement and restart the dynamic SQL.
408
409 old_low := NULL;
410 old_high := NULL;
411 new_low := NULL;
412 new_high := NULL;
413 first_row := TRUE;
414 first_rule_range := TRUE;
415 allrows := FALSE;
416 count_line := 0;
417 count_stmt := 0;
418
419 -- The first part of the delete statement is fixed within the rule
420 del_stmt := 'DELETE /*+ index(gl_bis_segval_int gl_bis_segval_int_n1) */ FROM GL_BIS_SEGVAL_INT ' ||
421 'WHERE segment_column_name=''' || segment_column_name || '''';
422
423 -- If the segment is a dependent segment, then add dynamic
424 -- SQL statement where clause for the parent segment
425 IF (parent_segment IS NOT NULL) THEN
426 del_stmt := del_stmt || ' AND parent_segment=''' || parent_segment || '''';
427 END IF;
428
429
430 -- Build Dynamic SQL statement to delete all segment values
431 -- not in the include range
432 -- Dynamic SQL statement example:
433 -- DELETE /*+ index(gl_bis_segval_int gl_bis_segval_int_n1) */
434 -- FROM GL_BIS_SEGVAL_INT
435 -- WHERE segment_column_name = 'SEGMENT1'
436 -- AND parent_segment = '01'
437 -- AND ( segment_value < '100'
438 -- OR (segment_value > '300' AND segment_value < '500')...)
439 --
440
441 OPEN in_range_cur(rule_id);
442 -- Loop for each include range
443 LOOP
444 FETCH in_range_cur INTO new_low, new_high;
445 EXIT WHEN in_range_cur%NOTFOUND;
446
447 -- IF first_row is TRUE, then build the beginning of Dynamic
448 -- SQL statement.
449
450 IF (first_row) THEN
451 sql_stmt := del_stmt;
452 first_row := FALSE;
453 END IF;
454
455 -- Build Where Clause for gaps between include ranges
456 -- If both new_low and new_high are NULL, we keep all values
457 -- of this segment. We set the allrows flag to TRUE, so no
458 -- SQL statement will be executed.
459 -- If old_low and old_high are NULL and this range is not the
460 -- first rule range of this security rule, there must be NULL
461 -- value in both FROM and TO fields somewhere within this security
462 -- rule. So we will include every value of this segment,too.
463
464 IF ((new_low IS NULL and new_high IS NULL) OR
465 ((old_low IS NULL and old_high IS NULL) AND
466 (NOT first_rule_range))) THEN
467 -- Include all rows
468 allrows := TRUE;
469 EXIT;
470 ELSE
471
472 -- If this range is the first range in this security rule,
473 -- we just store the range into old_low and old_high variables.
474
475 IF (first_rule_range) THEN
476 old_low := new_low;
477 old_high := new_high;
478
479 IF (new_low IS NOT NULL) THEN
480 -- According to the sort order of rule range, the From
481 -- value of the first rule range is the smallest value
482 -- if it is not NULL. So we build a where clause
483 -- to delete any segment value less then this new_low
484
485 IF (count_stmt <> 0) THEN
486 sql_stmt := sql_stmt || 'OR segment_value < ''' ||
487 new_low || '''';
488 ELSE
489 sql_stmt := sql_stmt || ' AND (segment_value < ''' ||
490 new_low || '''';
491 count_stmt := count_stmt + 1;
492 END IF;
493 count_line := count_line + 1;
494 END IF;
495 first_rule_range := FALSE;
496
497 ELSIF (new_low <= old_high OR new_low IS NULL OR
498 old_high IS NULL) THEN
499 -- If new_low is less than old_high, there is an overlap between
500 -- two include ranges. We then reset the old_high to merge these
501 -- two ranges.
502 old_high := greatest(old_high, new_high);
503
504 ELSE
505 -- If new_low is greater then old_high, there is a gap
506 -- between these two include ranges. The gap is
507 -- between new_low and old_high
508 IF (count_stmt <> 0) THEN
509 sql_stmt := sql_stmt ||
510 ' OR (segment_value > ''' || old_high ||
511 ''' AND segment_value < ''' || new_low || ''')';
512 ELSE
513 sql_stmt := sql_stmt ||
514 ' AND ((segment_value > ''' || old_high ||
515 ''' AND segment_value < ''' || new_low || ''')';
516 count_stmt := count_stmt + 1;
517 END IF;
518 count_line := count_line + 1;
519 old_low := new_low;
520 old_high := new_high;
521 END IF;
522
523 -- If we have hit the max number of lines, execute the statement
524 -- and reset to start over building the statement
525 IF (count_line = max_line) THEN
526 IF (count_stmt <> 0) THEN
527 sql_stmt := sql_stmt || ')';
528 END IF;
529
530 -- Execute Dynamic SQL statement
531 EXECUTE IMMEDIATE sql_stmt;
532
533 -- reset the variables before looping back
534 first_row := TRUE;
535 count_line := 0;
536 count_stmt := 0;
537 sql_stmt := NULL;
538 END IF;
539
540 END IF;
541 END LOOP;
542
543 -- If old_low and old_high are NULL and this range is not the
544 -- first rule range of this security rule, there must be NULL
545 -- value in both FROM and TO fields within this security rule.
546 -- So we will not delete any values for this segment.
547 IF (old_low IS NULL AND old_high IS NULL AND (NOT first_rule_range)) THEN
548 allrows := TRUE;
549 END IF;
550
551 -- If old_high IS NOT NULL, the value is the highest To range in
552 -- all include ranges for this security rule. We delete all segment
553 -- values which are greater then old_high.
554
555 IF (old_high IS NOT NULL) THEN
556 -- If we have just reset the statement with this as the only range
557 -- left, then need to re-build the beginning of the dynamic SQL
558 IF (sql_stmt IS NULL) THEN
559 sql_stmt := del_stmt;
560 END IF;
561
562 IF (count_stmt <> 0) THEN
563 sql_stmt := sql_stmt || ' OR segment_value > ''' || old_high ||
564 ''' ';
565 ELSE
566 sql_stmt := sql_stmt || ' AND ( segment_value > ''' ||
567 old_high || ''' ';
568 count_stmt := count_stmt + 1;
569 END IF;
570 END IF;
571
572 IF (count_stmt <> 0) THEN
573 sql_stmt := sql_stmt || ')';
574 END IF;
575
576 -- Execute Dynamic SQL statement
577 IF ((NOT allrows) AND sql_stmt IS NOT NULL) THEN
578 EXECUTE IMMEDIATE sql_stmt;
579 END IF;
580
581 CLOSE in_range_cur;
582
583 count_line := 0;
584 count_stmt := 0;
585 first_row := TRUE;
586 sql_stmt := NULL;
587
588 -- Build Dynamic SQL statement to delete all segment values
589 -- in the exclude range
590 -- Dynamic SQL statement example:
591 -- DELETE /*+ index(gl_bis_segval_int gl_bis_segval_int_n1) */
592 -- FROM GL_BIS_SEGVAL_INT
593 -- WHERE segment_column_name = 'SEGMENT1'
594 -- AND parent_segment = '01'
595 -- AND ( segment_value < '100'
596 -- OR (segment_value >= '300' AND segment_value <= '500')...)
597 --
598
599 OPEN ex_range_cur(rule_id);
600 -- Loop for each exclude range
601 LOOP
602 FETCH ex_range_cur INTO new_low, new_high;
603 EXIT WHEN ex_range_cur%NOTFOUND;
604
605 -- If first_row is TRUE, we build the beginning Dynamic SQL
606 -- statement for exclude ranges
607
608 IF (first_row) THEN
609 sql_stmt := del_stmt;
610 first_row := FALSE;
611 END IF;
612
613 -- If new_low and new_high are both NULL, then we delete
614 -- all segment values of this segment
615 IF (new_low IS NULL AND new_high IS NULL) THEN
616 /* exclude all segments */
617 EXIT;
618
619 ELSIF (new_low is NULL AND new_high IS NOT NULL) THEN
620 -- If new_low is NULL and new_high IS NOT NULL, we delete segment
621 -- values that are less then new_high.
622 IF (count_stmt <> 0) THEN
623 sql_stmt := sql_stmt || ' OR segment_value <= ''' || new_high ||
624 ''' ';
625 ELSE
626 sql_stmt := sql_stmt || ' AND (segment_value <= ''' ||
627 new_high || ''' ';
628 count_stmt := count_stmt + 1;
629 END IF;
630 count_line := count_line + 1;
631
632 ELSIF (new_low IS NOT NULL AND new_high IS NULL) THEN
633 -- If new_low IS NOT NULL and new_high is NULL, we delete segment
634 -- values that are greater then new_low.
635 IF (count_stmt <> 0) THEN
636 sql_stmt := sql_stmt || ' OR segment_value >= ''' || new_low ||
637 '''';
638 ELSE
639 sql_stmt := sql_stmt || ' AND (segment_value >= ''' ||
640 new_low || '''';
641 count_stmt := count_stmt + 1;
642 END IF;
643 count_line := count_line + 1;
644
645 ELSE
646 -- If both new_low and new_high are not NULL, we delete all
647 -- segment values between new_low and new_high.
648 IF (count_stmt <> 0) THEN
649 sql_stmt := sql_stmt ||
650 ' OR (segment_value >= ''' || new_low ||
651 ''' AND segment_value <= ''' || new_high || ''')';
652 ELSE
653 sql_stmt := sql_stmt ||
654 ' AND ((segment_value >= ''' || new_low ||
655 ''' AND segment_value <= ''' || new_high || ''')';
656 count_stmt := count_stmt + 1;
657 END IF;
658 count_line := count_line + 1;
659 END IF;
660
661 -- If we have hit the max number of lines, execute the statement
662 -- and reset to start over building the statement
663 IF (count_line = max_line) THEN
664 IF (count_stmt <> 0) THEN
665 sql_stmt := sql_stmt || ')';
666 END IF;
667
668 -- Execute Dynamic SQL statement
669 EXECUTE IMMEDIATE sql_stmt;
670
671 -- reset the variables before looping back
672 first_row := TRUE;
673 count_line := 0;
674 count_stmt := 0;
675 sql_stmt := NULL;
676 END IF;
677
678 END LOOP;
679
680 IF (count_stmt <> 0) THEN
681 sql_stmt := sql_stmt || ')';
682 END IF;
683
684 -- Execute Dynamic SQL statement
685 IF (sql_stmt IS NOT NULL) THEN
686 EXECUTE IMMEDIATE sql_stmt;
687 END IF;
688 CLOSE ex_range_cur;
689
690 END LOOP;
691 CLOSE rule_cur;
692
693 -- Set the seg_flag for each security enabled segments
694 -- and assign value to seg_col_name table
695
696 IF (segment_column_name = 'SEGMENT1') THEN
697 seg1_flag := TRUE;
698 seg_col_name(segnum) := 'SEGMENT1';
699 ELSIF (segment_column_name = 'SEGMENT2') THEN
700 seg2_flag := TRUE;
701 seg_col_name(segnum) := 'SEGMENT2';
702 ELSIF (segment_column_name = 'SEGMENT3') THEN
703 seg3_flag := TRUE;
704 seg_col_name(segnum) := 'SEGMENT3';
705 ELSIF (segment_column_name = 'SEGMENT4') THEN
706 seg4_flag := TRUE;
707 seg_col_name(segnum) := 'SEGMENT4';
708 ELSIF (segment_column_name = 'SEGMENT5') THEN
709 seg5_flag := TRUE;
710 seg_col_name(segnum) := 'SEGMENT5';
711 ELSIF (segment_column_name = 'SEGMENT6') THEN
712 seg6_flag := TRUE;
713 seg_col_name(segnum) := 'SEGMENT6';
714 ELSIF (segment_column_name = 'SEGMENT7') THEN
715 seg7_flag := TRUE;
716 seg_col_name(segnum) := 'SEGMENT7';
717 ELSIF (segment_column_name = 'SEGMENT8') THEN
718 seg8_flag := TRUE;
719 seg_col_name(segnum) := 'SEGMENT8';
720 ELSIF (segment_column_name = 'SEGMENT9') THEN
721 seg9_flag := TRUE;
722 seg_col_name(segnum) := 'SEGMENT9';
723 ELSIF (segment_column_name = 'SEGMENT10') THEN
724 seg10_flag := TRUE;
725 seg_col_name(segnum) := 'SEGMENT10';
726 ELSIF (segment_column_name = 'SEGMENT11') THEN
727 seg11_flag := TRUE;
728 seg_col_name(segnum) := 'SEGMENT11';
729 ELSIF (segment_column_name = 'SEGMENT12') THEN
730 seg12_flag := TRUE;
731 seg_col_name(segnum) := 'SEGMENT12';
732 ELSIF (segment_column_name = 'SEGMENT13') THEN
733 seg13_flag := TRUE;
734 seg_col_name(segnum) := 'SEGMENT13';
735 ELSIF (segment_column_name = 'SEGMENT14') THEN
736 seg14_flag := TRUE;
737 seg_col_name(segnum) := 'SEGMENT14';
738 ELSIF (segment_column_name = 'SEGMENT15') THEN
739 seg15_flag := TRUE;
740 seg_col_name(segnum) := 'SEGMENT15';
741 ELSIF (segment_column_name = 'SEGMENT16') THEN
742 seg16_flag := TRUE;
743 seg_col_name(segnum) := 'SEGMENT16';
744 ELSIF (segment_column_name = 'SEGMENT17') THEN
745 seg17_flag := TRUE;
746 seg_col_name(segnum) := 'SEGMENT17';
747 ELSIF (segment_column_name = 'SEGMENT18') THEN
748 seg18_flag := TRUE;
749 seg_col_name(segnum) := 'SEGMENT18';
750 ELSIF (segment_column_name = 'SEGMENT19') THEN
751 seg19_flag := TRUE;
752 seg_col_name(segnum) := 'SEGMENT19';
753 ELSIF (segment_column_name = 'SEGMENT20') THEN
754 seg20_flag := TRUE;
755 seg_col_name(segnum) := 'SEGMENT20';
756 ELSIF (segment_column_name = 'SEGMENT21') THEN
757 seg21_flag := TRUE;
758 seg_col_name(segnum) := 'SEGMENT21';
759 ELSIF (segment_column_name = 'SEGMENT22') THEN
760 seg22_flag := TRUE;
761 seg_col_name(segnum) := 'SEGMENT22';
762 ELSIF (segment_column_name = 'SEGMENT23') THEN
763 seg23_flag := TRUE;
764 seg_col_name(segnum) := 'SEGMENT23';
765 ELSIF (segment_column_name = 'SEGMENT24') THEN
766 seg24_flag := TRUE;
767 seg_col_name(segnum) := 'SEGMENT24';
768 ELSIF (segment_column_name = 'SEGMENT25') THEN
769 seg25_flag := TRUE;
770 seg_col_name(segnum) := 'SEGMENT25';
771 ELSIF (segment_column_name = 'SEGMENT26') THEN
772 seg26_flag := TRUE;
773 seg_col_name(segnum) := 'SEGMENT26';
774 ELSIF (segment_column_name = 'SEGMENT27') THEN
775 seg27_flag := TRUE;
776 seg_col_name(segnum) := 'SEGMENT27';
777 ELSIF (segment_column_name = 'SEGMENT28') THEN
778 seg28_flag := TRUE;
779 seg_col_name(segnum) := 'SEGMENT28';
780 ELSIF (segment_column_name = 'SEGMENT29') THEN
781 seg29_flag := TRUE;
782 seg_col_name(segnum) := 'SEGMENT29';
783 ELSIF (segment_column_name = 'SEGMENT30') THEN
784 seg30_flag := TRUE;
785 seg_col_name(segnum) := 'SEGMENT30';
786 END IF;
787 END LOOP;
788
789 initialized := TRUE;
790
791 CLOSE seg_cur;
792
793 END init_segval;
794
795 -- Function
796 -- Validate_access
797 -- Purpose
798 -- Validate the given code combination id and ledger id
799 -- according to the rules stored in GL_BIS_SEGVAL_INT temporary
800 -- table by gl_security_pkg.init
801 --
802 FUNCTION validate_access(p_ledger_id IN NUMBER DEFAULT NULL,
803 ccid IN NUMBER DEFAULT NULL) RETURN VARCHAR2 IS
804 segment1 VARCHAR2(150);
805 segment2 VARCHAR2(150);
806 segment3 VARCHAR2(150);
807 segment4 VARCHAR2(150);
808 segment5 VARCHAR2(150);
809 segment6 VARCHAR2(150);
810 segment7 VARCHAR2(150);
811 segment8 VARCHAR2(150);
812 segment9 VARCHAR2(150);
813 segment10 VARCHAR2(150);
814 segment11 VARCHAR2(150);
815 segment12 VARCHAR2(150);
816 segment13 VARCHAR2(150);
817 segment14 VARCHAR2(150);
818 segment15 VARCHAR2(150);
819 segment16 VARCHAR2(150);
820 segment17 VARCHAR2(150);
821 segment18 VARCHAR2(150);
822 segment19 VARCHAR2(150);
823 segment20 VARCHAR2(150);
824 segment21 VARCHAR2(150);
825 segment22 VARCHAR2(150);
826 segment23 VARCHAR2(150);
827 segment24 VARCHAR2(150);
828 segment25 VARCHAR2(150);
829 segment26 VARCHAR2(150);
830 segment27 VARCHAR2(150);
831 segment28 VARCHAR2(150);
832 segment29 VARCHAR2(150);
833 segment30 VARCHAR2(150);
834 total_count NUMBER;
835 l_seg_value VARCHAR2(2000);
836
837 --New private function for R12
838 FUNCTION get_seg_value(p_seg_colname VARCHAR2) RETURN VARCHAR2 IS
839 l_seg_value VARCHAR2(2000);
840 BEGIN
841 CASE
842 WHEN p_seg_colname = 'SEGMENT1' THEN
843 l_seg_value := segment1;
844 WHEN p_seg_colname = 'SEGMENT2' THEN
845 l_seg_value := segment2;
846 WHEN p_seg_colname = 'SEGMENT3' THEN
847 l_seg_value := segment3;
848 WHEN p_seg_colname = 'SEGMENT4' THEN
849 l_seg_value := segment4;
850 WHEN p_seg_colname = 'SEGMENT5' THEN
851 l_seg_value := segment5;
852 WHEN p_seg_colname = 'SEGMENT6' THEN
853 l_seg_value := segment6;
854 WHEN p_seg_colname = 'SEGMENT7' THEN
855 l_seg_value := segment7;
856 WHEN p_seg_colname = 'SEGMENT8' THEN
857 l_seg_value := segment8;
858 WHEN p_seg_colname = 'SEGMENT9' THEN
859 l_seg_value := segment9;
860 WHEN p_seg_colname = 'SEGMENT10' THEN
861 l_seg_value := segment10;
862 WHEN p_seg_colname = 'SEGMENT11' THEN
863 l_seg_value := segment11;
864 WHEN p_seg_colname = 'SEGMENT12' THEN
865 l_seg_value := segment12;
866 WHEN p_seg_colname = 'SEGMENT13' THEN
867 l_seg_value := segment13;
868 WHEN p_seg_colname = 'SEGMENT14' THEN
869 l_seg_value := segment14;
870 WHEN p_seg_colname = 'SEGMENT15' THEN
871 l_seg_value := segment15;
872 WHEN p_seg_colname = 'SEGMENT16' THEN
873 l_seg_value := segment16;
874 WHEN p_seg_colname = 'SEGMENT17' THEN
875 l_seg_value := segment17;
876 WHEN p_seg_colname = 'SEGMENT18' THEN
877 l_seg_value := segment18;
878 WHEN p_seg_colname = 'SEGMENT19' THEN
879 l_seg_value := segment19;
880 WHEN p_seg_colname = 'SEGMENT20' THEN
881 l_seg_value := segment20;
882 WHEN p_seg_colname = 'SEGMENT21' THEN
883 l_seg_value := segment21;
884 WHEN p_seg_colname = 'SEGMENT22' THEN
885 l_seg_value := segment22;
886 WHEN p_seg_colname = 'SEGMENT23' THEN
887 l_seg_value := segment23;
888 WHEN p_seg_colname = 'SEGMENT24' THEN
889 l_seg_value := segment24;
890 WHEN p_seg_colname = 'SEGMENT25' THEN
891 l_seg_value := segment25;
892 WHEN p_seg_colname = 'SEGMENT26' THEN
893 l_seg_value := segment26;
894 WHEN p_seg_colname = 'SEGMENT27' THEN
895 l_seg_value := segment27;
896 WHEN p_seg_colname = 'SEGMENT28' THEN
897 l_seg_value := segment28;
898 WHEN p_seg_colname = 'SEGMENT29' THEN
899 l_seg_value := segment29;
900 WHEN p_seg_colname = 'SEGMENT30' THEN
901 l_seg_value := segment30;
902 END CASE;
903 RETURN l_seg_value;
904 END get_seg_value;
905
906 BEGIN
907 -- Check if init() is executed or not. If not, then return
908 IF (NOT initialized) THEN
909 RETURN('FALSE');
910 END IF;
911
912 total_count := 0;
913
914 -- If only ledger id parameter is given, then validate
915 -- only ledger id.
916 IF ACCESS_ID IS NULL THEN
917 IF ((p_ledger_id IS NOT NULL) AND (ccid IS NULL)) THEN
918 IF (p_ledger_id = LDGR_ID) THEN
919 RETURN('TRUE');
920 ELSE
921 RETURN('FALSE');
922 END IF;
923 END IF;
924 ELSE
925 -- Added check for the access set ID
926 IF ((p_ledger_id IS NOT NULL) AND (ccid IS NULL)) THEN
927 SELECT count(*)
928 INTO total_count
929 FROM gl_access_set_ledgers
930 WHERE ledger_id = p_ledger_id
931 AND access_set_id = ACCESS_ID;
932
933 IF total_count > 0 THEN
934 RETURN('TRUE');
935 ELSE
936 RETURN('FALSE');
937 END IF;
938 END IF;
939 END IF;
940
941 -- If the given code combination id is not NULL, then
942 -- validate the ccid
943 IF (ccid IS NOT NULL) THEN
944 BEGIN
945 SELECT segment1,
946 segment2,
947 segment3,
948 segment4,
949 segment5,
950 segment6,
951 segment7,
952 segment8,
953 segment9,
954 segment10,
955 segment11,
956 segment12,
957 segment13,
958 segment14,
959 segment15,
960 segment16,
961 segment17,
962 segment18,
963 segment19,
964 segment20,
965 segment21,
966 segment22,
967 segment23,
968 segment24,
969 segment25,
970 segment26,
971 segment27,
972 segment28,
973 segment29,
974 segment30
975 INTO segment1,
976 segment2,
977 segment3,
978 segment4,
979 segment5,
980 segment6,
981 segment7,
982 segment8,
983 segment9,
984 segment10,
985 segment11,
986 segment12,
987 segment13,
988 segment14,
989 segment15,
990 segment16,
991 segment17,
992 segment18,
993 segment19,
994 segment20,
995 segment21,
996 segment22,
997 segment23,
998 segment24,
999 segment25,
1000 segment26,
1001 segment27,
1002 segment28,
1003 segment29,
1004 segment30
1005 FROM GL_CODE_COMBINATIONS
1006 WHERE code_combination_id = ccid;
1007 EXCEPTION
1008 WHEN NO_DATA_FOUND THEN
1009 RETURN('FALSE');
1010 END;
1011
1012 -- For each segment of the given code combination id, if the
1013 -- segment is secruity enabled, we check if the segment value
1014 -- exists in the GL_BIS_SEGVAL_INT temporary table. If not,
1015 -- then return FALSE.
1016
1017 IF (seg1_flag) THEN
1018 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1019 count(*)
1020 into total_count
1021 FROM GL_BIS_SEGVAL_INT
1022 WHERE segment_column_name = 'SEGMENT1'
1023 AND segment_value = segment1;
1024
1025 IF (total_count = 0) THEN
1026 return('FALSE');
1027 END IF;
1028 END IF;
1029
1030 IF (seg2_flag) THEN
1031 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1032 count(*)
1033 into total_count
1034 FROM GL_BIS_SEGVAL_INT
1035 WHERE segment_column_name = 'SEGMENT2'
1036 AND segment_value = segment2;
1037
1038 IF (total_count = 0) THEN
1039 return('FALSE');
1040 END IF;
1041 END IF;
1042
1043 IF (seg3_flag) THEN
1044 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1045 count(*)
1046 into total_count
1047 FROM GL_BIS_SEGVAL_INT
1048 WHERE segment_column_name = 'SEGMENT3'
1049 AND segment_value = segment3;
1050
1051 IF (total_count = 0) THEN
1052 return('FALSE');
1053 END IF;
1054 END IF;
1055
1056 IF (seg4_flag) THEN
1057 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1058 count(*)
1059 into total_count
1060 FROM GL_BIS_SEGVAL_INT
1061 WHERE segment_column_name = 'SEGMENT4'
1062 AND segment_value = segment4;
1063
1064 IF (total_count = 0) THEN
1065 return('FALSE');
1066 END IF;
1067 END IF;
1068
1069 IF (seg5_flag) THEN
1070 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1071 count(*)
1072 into total_count
1073 FROM GL_BIS_SEGVAL_INT
1074 WHERE segment_column_name = 'SEGMENT5'
1075 AND segment_value = segment5;
1076
1077 IF (total_count = 0) THEN
1078 return('FALSE');
1079 END IF;
1080 END IF;
1081
1082 IF (seg6_flag) THEN
1083 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1084 count(*)
1085 into total_count
1086 FROM GL_BIS_SEGVAL_INT
1087 WHERE segment_column_name = 'SEGMENT6'
1088 AND segment_value = segment6;
1089
1090 IF (total_count = 0) THEN
1091 return('FALSE');
1092 END IF;
1093 END IF;
1094
1095 IF (seg7_flag) THEN
1096 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1097 count(*)
1098 into total_count
1099 FROM GL_BIS_SEGVAL_INT
1100 WHERE segment_column_name = 'SEGMENT7'
1101 AND segment_value = segment7;
1102
1103 IF (total_count = 0) THEN
1104 return('FALSE');
1105 END IF;
1106 END IF;
1107
1108 IF (seg8_flag) THEN
1109 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1110 count(*)
1111 into total_count
1112 FROM GL_BIS_SEGVAL_INT
1113 WHERE segment_column_name = 'SEGMENT8'
1114 AND segment_value = segment8;
1115
1116 IF (total_count = 0) THEN
1117 return('FALSE');
1118 END IF;
1119 END IF;
1120
1121 IF (seg9_flag) THEN
1122 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1123 count(*)
1124 into total_count
1125 FROM GL_BIS_SEGVAL_INT
1126 WHERE segment_column_name = 'SEGMENT9'
1127 AND segment_value = segment9;
1128
1129 IF (total_count = 0) THEN
1130 return('FALSE');
1131 END IF;
1132 END IF;
1133
1134 IF (seg10_flag) THEN
1135 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1136 count(*)
1137 into total_count
1138 FROM GL_BIS_SEGVAL_INT
1139 WHERE segment_column_name = 'SEGMENT10'
1140 AND segment_value = segment10;
1141
1142 IF (total_count = 0) THEN
1143 return 'FALSE';
1144 END IF;
1145 END IF;
1146
1147 IF (seg11_flag) THEN
1148 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1149 count(*)
1150 into total_count
1151 FROM GL_BIS_SEGVAL_INT
1152 WHERE segment_column_name = 'SEGMENT11'
1153 AND segment_value = segment11;
1154
1155 IF (total_count = 0) THEN
1156 return('FALSE');
1157 END IF;
1158 END IF;
1159
1160 IF (seg12_flag) THEN
1161 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1162 count(*)
1163 into total_count
1164 FROM GL_BIS_SEGVAL_INT
1165 WHERE segment_column_name = 'SEGMENT12'
1166 AND segment_value = segment12;
1167
1168 IF (total_count = 0) THEN
1169 return('FALSE');
1170 END IF;
1171 END IF;
1172
1173 IF (seg13_flag) THEN
1174 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1175 count(*)
1176 into total_count
1177 FROM GL_BIS_SEGVAL_INT
1178 WHERE segment_column_name = 'SEGMENT13'
1179 AND segment_value = segment13;
1180
1181 IF (total_count = 0) THEN
1182 return('FALSE');
1183 END IF;
1184 END IF;
1185
1186 IF (seg14_flag) THEN
1187 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1188 count(*)
1189 into total_count
1190 FROM GL_BIS_SEGVAL_INT
1191 WHERE segment_column_name = 'SEGMENT14'
1192 AND segment_value = segment14;
1193
1194 IF (total_count = 0) THEN
1195 return('FALSE');
1196 END IF;
1197 END IF;
1198
1199 IF (seg15_flag) THEN
1200 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1201 count(*)
1202 into total_count
1203 FROM GL_BIS_SEGVAL_INT
1204 WHERE segment_column_name = 'SEGMENT15'
1205 AND segment_value = segment15;
1206
1207 IF (total_count = 0) THEN
1208 return('FALSE');
1209 END IF;
1210 END IF;
1211
1212 IF (seg16_flag) THEN
1213 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1214 count(*)
1215 into total_count
1216 FROM GL_BIS_SEGVAL_INT
1217 WHERE segment_column_name = 'SEGMENT16'
1218 AND segment_value = segment16;
1219
1220 IF (total_count = 0) THEN
1221 return 'FALSE';
1222 END IF;
1223 END IF;
1224
1225 IF (seg17_flag) THEN
1226 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1227 count(*)
1228 into total_count
1229 FROM GL_BIS_SEGVAL_INT
1230 WHERE segment_column_name = 'SEGMENT17'
1231 AND segment_value = segment17;
1232
1233 IF (total_count = 0) THEN
1234 return 'FALSE';
1235 END IF;
1236 END IF;
1237
1238 IF (seg18_flag) THEN
1239 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1240 count(*)
1241 into total_count
1242 FROM GL_BIS_SEGVAL_INT
1243 WHERE segment_column_name = 'SEGMENT18'
1244 AND segment_value = segment18;
1245
1246 IF (total_count = 0) THEN
1247 return('FALSE');
1248 END IF;
1249 END IF;
1250
1251 IF (seg19_flag) THEN
1252 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1253 count(*)
1254 into total_count
1255 FROM GL_BIS_SEGVAL_INT
1256 WHERE segment_column_name = 'SEGMENT19'
1257 AND segment_value = segment19;
1258
1259 IF (total_count = 0) THEN
1260 return('FALSE');
1261 END IF;
1262 END IF;
1263
1264 IF (seg20_flag) THEN
1265 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1266 count(*)
1267 into total_count
1268 FROM GL_BIS_SEGVAL_INT
1269 WHERE segment_column_name = 'SEGMENT20'
1270 AND segment_value = segment20;
1271
1272 IF (total_count = 0) THEN
1273 return('FALSE');
1274 END IF;
1275 END IF;
1276
1277 IF (seg21_flag) THEN
1278 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1279 count(*)
1280 into total_count
1281 FROM GL_BIS_SEGVAL_INT
1282 WHERE segment_column_name = 'SEGMENT21'
1283 AND segment_value = segment21;
1284
1285 IF (total_count = 0) THEN
1286 return('FALSE');
1287 END IF;
1288 END IF;
1289
1290 IF (seg22_flag) THEN
1291 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1292 count(*)
1293 into total_count
1294 FROM GL_BIS_SEGVAL_INT
1295 WHERE segment_column_name = 'SEGMENT22'
1296 AND segment_value = segment22;
1297
1298 IF (total_count = 0) THEN
1299 return('FALSE');
1300 END IF;
1301 END IF;
1302
1303 IF (seg23_flag) THEN
1304 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1305 count(*)
1306 into total_count
1307 FROM GL_BIS_SEGVAL_INT
1308 WHERE segment_column_name = 'SEGMENT23'
1309 AND segment_value = segment23;
1310
1311 IF (total_count = 0) THEN
1312 return('FALSE');
1313 END IF;
1314 END IF;
1315
1316 IF (seg24_flag) THEN
1317 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1318 count(*)
1319 into total_count
1320 FROM GL_BIS_SEGVAL_INT
1321 WHERE segment_column_name = 'SEGMENT24'
1322 AND segment_value = segment24;
1323
1324 IF (total_count = 0) THEN
1325 return('FALSE');
1326 END IF;
1327 END IF;
1328
1329 IF (seg25_flag) THEN
1330 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1331 count(*)
1332 into total_count
1333 FROM GL_BIS_SEGVAL_INT
1334 WHERE segment_column_name = 'SEGMENT25'
1335 AND segment_value = segment25;
1336
1337 IF (total_count = 0) THEN
1338 return('FALSE');
1339 END IF;
1340 END IF;
1341
1342 IF (seg26_flag) THEN
1343 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1344 count(*)
1345 into total_count
1346 FROM GL_BIS_SEGVAL_INT
1347 WHERE segment_column_name = 'SEGMENT26'
1348 AND segment_value = segment26;
1349
1350 IF (total_count = 0) THEN
1351 return('FALSE');
1352 END IF;
1353 END IF;
1354
1355 IF (seg27_flag) THEN
1356 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1357 count(*)
1358 into total_count
1359 FROM GL_BIS_SEGVAL_INT
1360 WHERE segment_column_name = 'SEGMENT27'
1361 AND segment_value = segment27;
1362
1363 IF (total_count = 0) THEN
1364 return('FALSE');
1365 END IF;
1366 END IF;
1367
1368 IF (seg28_flag) THEN
1369 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1370 count(*)
1371 into total_count
1372 FROM GL_BIS_SEGVAL_INT
1373 WHERE segment_column_name = 'SEGMENT28'
1374 AND segment_value = segment28;
1375
1376 IF (total_count = 0) THEN
1377 return('FALSE');
1378 END IF;
1379 END IF;
1380
1381 IF (seg29_flag) THEN
1382 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1383 count(*)
1384 into total_count
1385 FROM GL_BIS_SEGVAL_INT
1386 WHERE segment_column_name = 'SEGMENT29'
1387 AND segment_value = segment29;
1388
1389 IF (total_count = 0) THEN
1390 return('FALSE');
1391 END IF;
1392 END IF;
1393
1394 IF (seg30_flag) THEN
1395 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1396 count(*)
1397 into total_count
1398 FROM GL_BIS_SEGVAL_INT
1399 WHERE segment_column_name = 'SEGMENT30'
1400 AND segment_value = segment30;
1401
1402 IF (total_count = 0) THEN
1403 return('FALSE');
1404 END IF;
1405 END IF;
1406
1407
1408 -- Validate the given ledger id.
1409 -- If the given ledger id is NULL, then we return TRUE
1410 -- because the ccid is also valid from previous logic.
1411 -- If the given ledger id is not NULL, we return TRUE
1412 -- if the ledger id is valid.
1413 IF ACCESS_ID IS NULL THEN
1414 IF (p_ledger_id IS NOT NULL) THEN
1415 IF (p_ledger_id = LDGR_ID) THEN
1416 RETURN('TRUE');
1417 ELSE
1418 RETURN('FALSE');
1419 END IF;
1420 ELSE -- Ledger context not available, validate only segment security rules
1421 RETURN('TRUE');
1422 END IF;
1423 ELSE
1424 -- added access set ID check
1425 IF (p_ledger_id IS NOT NULL) THEN
1426 IF SECURITY_SEGMENT_CODE = 'F' THEN
1427 SELECT count(*) into total_count
1428 FROM gl_access_set_ledgers
1429 WHERE ledger_id = p_ledger_id
1430 AND access_set_id = ACCESS_ID;
1431
1432 ELSIF SECURITY_SEGMENT_CODE IN ('B','M') THEN
1433 IF BAL_MGMT_SEG_COL_NAME IS NULL THEN
1434 RETURN ('FALSE');
1435 END IF;
1436
1437 l_seg_value := get_seg_value(BAL_MGMT_SEG_COL_NAME);
1438
1439 SELECT count(*) into total_count
1440 FROM gl_access_set_assignments gasa
1441 WHERE gasa.segment_value = l_seg_value
1442 AND gasa.ledger_id = p_ledger_id
1443 AND gasa.access_set_id = ACCESS_ID;
1444 END IF;
1445
1446 IF total_count > 0 THEN
1447 RETURN ('TRUE');
1448 ELSE
1449 RETURN ('FALSE');
1450 END IF;
1451
1452 -- Ledger context not available, validate only segment security rules
1453 ELSE
1454 RETURN ('TRUE');
1455 END IF;
1456 END IF; --IF (ACCESS_ID IS NULL)
1457 END IF; -- IF (ccid IS NOT NULL ) THEN
1458
1459 END validate_access;
1460
1461 -- Function
1462 -- Validate_segval
1463 -- Purpose
1464 -- Validate the given segment number and segment value
1465 -- according to the rules stored in GL_BIS_SEGVAL_INT temporary
1466 -- table by gl_security_pkg.init
1467 --
1468 FUNCTION validate_segval(segnum1 IN NUMBER DEFAULT NULL,
1469 segnum2 IN NUMBER DEFAULT NULL,
1470 segval1 IN VARCHAR2 DEFAULT NULL,
1471 segval2 IN VARCHAR2 DEFAULT NULL,
1472 p_ledger_id IN NUMBER DEFAULT NULL)
1473 RETURN VARCHAR2 IS
1474 seg1_name VARCHAR2(30);
1475 seg2_name VARCHAR2(30);
1476 count1 NUMBER;
1477 BEGIN
1478
1479 -- Check if init() is executed or not. If not, then return
1480 IF (NOT initialized) THEN
1481 RETURN('FALSE');
1482 END IF;
1483
1484 -- Validate first segment number and segment value
1485 IF (segnum1 IS NOT NULL AND segnum1 <> -1) THEN
1486 IF (seg_col_name(segnum1) IS NOT NULL) THEN
1487
1488 seg1_name := seg_col_name(segnum1);
1489
1490 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1491 count(*)
1492 into count1
1493 FROM GL_BIS_SEGVAL_INT
1494 WHERE segment_column_name = seg1_name
1495 AND segment_value = segval1;
1496
1497 IF (count1 = 0) THEN
1498 return('FALSE');
1499 ELSE
1500 IF p_ledger_id IS NOT NULL AND
1501 ACCESS_ID IS NOT NULL AND
1502 SECURITY_SEGMENT_CODE IN ('B','M') THEN
1503 IF BAL_MGMT_SEG_COL_NAME IS NULL THEN
1504 RETURN ('FALSE');
1505 END IF;
1506
1507 IF seg1_name = BAL_MGMT_SEG_COL_NAME THEN
1508 count1 := 0;
1509 SELECT count(*) into count1
1510 FROM gl_access_set_assignments gasa
1511 WHERE gasa.segment_value = segval1
1512 AND gasa.ledger_id = p_ledger_id
1513 AND gasa.access_set_id = ACCESS_ID;
1514
1515 IF count1 = 0 THEN
1516 RETURN ('FALSE');
1517 END IF;
1518 END IF;
1519 END IF;
1520 END IF;
1521 END IF;
1522 END IF;
1523
1524 count1 := 0;
1525
1526 -- Validate second segment nummber and segment value
1527 IF (segnum2 IS NOT NULL AND segnum2 <> -1) THEN
1528 IF (seg_col_name(segnum2) IS NOT NULL) THEN
1529
1530 seg2_name := seg_col_name(segnum2);
1531
1532 SELECT /*+ index(gl_bis_segval_int gl_bis_segval_int_n1 ) */
1533 count(*)
1534 into count1
1535 FROM GL_BIS_SEGVAL_INT
1536 WHERE segment_column_name = seg2_name
1537 AND segment_value = segval2;
1538
1539 IF (count1 = 0) THEN
1540 return('FALSE');
1541 ELSE
1542 IF p_ledger_id IS NOT NULL AND
1543 ACCESS_ID IS NOT NULL AND
1544 SECURITY_SEGMENT_CODE IN ('B','M') THEN
1545
1546 IF BAL_MGMT_SEG_COL_NAME IS NULL THEN
1547 RETURN ('FALSE');
1548 END IF;
1549
1550 IF seg2_name = BAL_MGMT_SEG_COL_NAME THEN
1551 count1 := 0;
1552 SELECT count(*)
1553 INTO count1
1554 FROM gl_access_set_assignments gasa
1555 WHERE gasa.segment_value = segval2
1556 AND gasa.ledger_id = p_ledger_id
1557 AND gasa.access_set_id = ACCESS_ID;
1558
1559 IF count1 = 0 THEN
1560 RETURN ('FALSE');
1561 END IF;
1562 END IF;
1563 END IF;
1564 END IF;
1565 END IF;
1566 END IF;
1567
1568 return('TRUE');
1569
1570 END validate_segval;
1571
1572 FUNCTION login_led_id RETURN NUMBER IS
1573 BEGIN
1574 IF (NOT initialized) THEN
1575 RETURN(-1);
1576 ELSE
1577 RETURN(LDGR_ID);
1578 END IF;
1579 END login_led_id;
1580
1581 --Added new parameterized function
1582 FUNCTION login_led_id(p_ledger_id IN NUMBER) RETURN NUMBER IS
1583 l_total_count NUMBER;
1584 BEGIN
1585 IF p_ledger_id IS NULL THEN
1586 RETURN(-1);
1587 ELSIF (NOT initialized) THEN
1588 RETURN(-1);
1589 END IF;
1590
1591 SELECT count(*)
1592 INTO l_total_count
1593 FROM gl_access_set_ledgers
1594 WHERE ledger_id = p_ledger_id
1595 AND access_set_id = ACCESS_ID;
1596
1597 IF l_total_count > 0 THEN
1598 RETURN(p_ledger_id);
1599 ELSE
1600 RETURN(-1);
1601 END IF;
1602
1603 END login_led_id;
1604
1605 --Added new function
1606 FUNCTION login_access_id RETURN NUMBER IS
1607 BEGIN
1608 IF (NOT initialized) THEN
1609 RETURN(-1);
1610 ELSE
1611 RETURN(ACCESS_ID);
1612 END IF;
1613 END login_access_id;
1614
1615 END gl_security_pkg;
[Home] [Help]