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