[Home] [Help]
PACKAGE BODY: APPS.GL_ACCESS_SET_SECURITY_PKG
Source
1 PACKAGE BODY gl_access_set_security_pkg AS
2 /* $Header: gluasecb.pls 120.9 2005/08/19 20:34:41 ticheng ship $ */
3
4 --
5 -- PRIVATE VARIABLES
6 --
7 c_access_set_id NUMBER(15);
8 c_coa_id NUMBER(15);
9 c_security_col VARCHAR2(15);
10 c_security_code VARCHAR2(1);
11 c_auto_created_flag VARCHAR2(1);
12 c_check_dates BOOLEAN;
13
14 --
15 -- PRIVATE FUNCTIONS
16 --
17
18 --
19 -- Function
20 -- get_security_column
21 --
22 -- Purpose
23 -- This function returns the security segment column name for the
24 -- access set.
25 --
26 FUNCTION get_security_column(x_access_set_id NUMBER) RETURN VARCHAR2 IS
27 dumdum BOOLEAN := FALSE;
28 access_set_name VARCHAR2(30);
29 period_set_name VARCHAR2(15);
30 accounted_period_type VARCHAR2(15);
31 auto_created_flag VARCHAR2(1);
32
33 seg_name VARCHAR2(30);
34 secseg_left_prompt VARCHAR2(80);
35 value_set VARCHAR2(60);
36
37 secure_seg_num NUMBER(15);
38 security_col_name VARCHAR2(15);
39 coa_id NUMBER(15);
40 BEGIN
41 IF (x_access_set_id = c_access_set_id AND c_access_set_id IS NOT NULL) THEN
42 RETURN c_security_col;
43 END IF;
44
45 -- Reinitialize c_check_dates;
46 c_check_dates := null;
47
48 -- get coa id and security segment code information
49 gl_access_sets_pkg.select_columns(
50 x_access_set_id,
51 access_set_name,
52 c_security_code,
53 coa_id,
54 period_set_name,
55 accounted_period_type,
56 c_auto_created_flag);
57
58 IF (c_security_code = 'F') THEN
59 security_col_name := null;
60 ELSE
61
62 IF (c_security_code = 'B') THEN
63 dumdum := FND_FLEX_APIS.get_qualifier_segnum(
64 101, 'GL#', coa_id, 'GL_BALANCING', secure_seg_num);
65 ELSIF (c_security_code = 'M') THEN
66 dumdum := FND_FLEX_APIS.get_qualifier_segnum(
67 101, 'GL#', coa_id, 'GL_MANAGEMENT', secure_seg_num);
68 END IF;
69
70 dumdum := FND_FLEX_APIS.get_segment_info(101, 'GL#', coa_id,
71 secure_seg_num, security_col_name,
72 seg_name, secseg_left_prompt, value_set);
73 END IF;
74
75 -- cache information to private global variables
76 c_access_set_id := x_access_set_id;
77 c_coa_id := coa_id;
78 c_security_col := security_col_name;
79
80 RETURN security_col_name;
81 END get_security_column;
82
83 --
84 -- Function
85 -- build_privilege_clause
86 --
87 -- Purpose
88 -- This function builds the privilege clause part of a where clause.
89 --
90 FUNCTION build_privilege_clause(access_privilege_code VARCHAR2) RETURN VARCHAR2 IS
91 BEGIN
92 IF (access_privilege_code =
93 gl_access_set_security_pkg.READ_ONLY_ACCESS) THEN
94 RETURN ('');
95 ELSIF (access_privilege_code =
96 gl_access_set_security_pkg.WRITE_ACCESS) THEN
97 RETURN ('AND acc.access_privilege_code IN (''B'', ''F'') ');
98 ELSIF (access_privilege_code =
99 gl_access_set_security_pkg.FULL_ACCESS) THEN
100 RETURN ('AND acc.access_privilege_code = ''F'' ');
101 ELSE
102 fnd_message.set_name('SQLGL', 'GL_INVALID_PARAM');
103 fnd_message.set_token('VALUE', access_privilege_code);
104 fnd_message.set_token('PARAM', 'access_privilege_code');
105 RAISE INVALID_PARAM;
106 END IF;
107 END build_privilege_clause;
108
109 --
110 -- Function
111 -- build_date_clause
112 --
113 -- Purpose
114 -- This function builds the date clause part of a where clause.
115 --
116 FUNCTION build_date_clause(edate DATE) RETURN VARCHAR2 IS
117 edatestr VARCHAR2(200);
118 BEGIN
119 IF (edate IS NULL) THEN
120 RETURN ('');
121 ELSE
122 edatestr := 'to_date(''' || to_char(edate, 'MM-DD-YYYY') ||
123 ''',''MM-DD-YYYY'') ';
124
125 RETURN ('AND '|| edatestr ||
126 'BETWEEN nvl(trunc(acc.start_date), ' || edatestr || '-1) ' ||
127 'AND nvl(trunc(acc.end_date), ' || edatestr || '+1) ');
128 END IF;
129 END build_date_clause;
130
131
132 --
133 -- Function
134 -- check_dates
135 --
136 -- Purpose
137 -- This function determines if date checking is necessary.
138 --
139 FUNCTION check_dates(c_access_set_id NUMBER) RETURN BOOLEAN IS
140 dumdum VARCHAR2(60);
141 security_col VARCHAR2(15);
142 BEGIN
143 -- Initialize the access set information. This call initializes
144 -- the c_auto_created_flag global variable. It will also clear out
145 -- c_check_dates if the access set changes
146 security_col := get_security_column(c_access_set_id);
147
148 IF (c_check_dates IS NOT NULL) THEN
149 IF (c_check_dates) THEN
150 RETURN TRUE;
151 ELSE
152 RETURN FALSE;
153 END IF;
154 END IF;
155
156 c_check_dates := TRUE;
157 -- If this access set is not an implicit one, then we don't need to check dates
158 IF (c_auto_created_flag = 'N') THEN
159 c_check_dates := FALSE;
160 ELSE
161 BEGIN
162 SELECT 'associated with ledger'
163 INTO dumdum
164 FROM gl_access_sets acc, gl_ledgers lgr
165 WHERE acc.access_set_id = c_access_set_id
166 AND lgr.ledger_id = acc.default_ledger_id
167 AND lgr.implicit_access_set_id = acc.access_set_id
168 AND lgr.object_type_code = 'L';
169 EXCEPTION
170 WHEN NO_DATA_FOUND THEN
171 c_check_dates := FALSE;
172 END;
173 END IF;
174
175 return(c_check_dates);
176 END check_dates;
177
178
179 --
180 -- PUBLIC FUNCTIONS
181 --
182
183 FUNCTION get_security_clause( access_set_id NUMBER,
184 access_privilege_code VARCHAR2,
185 ledger_check_mode VARCHAR2,
186 ledger_context VARCHAR2,
187 ledger_table_alias VARCHAR2,
188 segval_check_mode VARCHAR2,
189 segval_context VARCHAR2,
190 segval_table_alias VARCHAR2,
191 edate DATE ) RETURN VARCHAR2 IS
192 security_col VARCHAR2(15);
193 segval_mode VARCHAR2(15) := segval_check_mode;
194 ledger_table VARCHAR2(30);
195 segval_table VARCHAR2(30);
196
197 dum_num NUMBER(15);
198 BEGIN
199
200 IF (segval_mode = gl_access_set_security_pkg.CHECK_SEGVALS) THEN
201 security_col := get_security_column(access_set_id);
202
203 -- If the security column is null (Ledger Only access set), then segment
204 -- values will not be validated.
205 IF (security_col IS NULL) THEN
206 segval_mode := gl_access_set_security_pkg.NO_SEG_VALIDATION;
207 END IF;
208 END IF;
209
210 IF (ledger_table_alias IS NOT NULL) THEN
211 ledger_table := ledger_table_alias || '.';
212 ELSE
213 ledger_table := '';
214 END IF;
215
216 IF (segval_table_alias IS NOT NULL) THEN
217 segval_table := segval_table_alias || '.';
218 ELSE
219 segval_table := '';
220 END IF;
221
222 IF (ledger_check_mode = gl_access_set_security_pkg.CHECK_LEDGER_COLUMN) THEN
223
224 IF (ledger_context IS NULL) THEN
225 fnd_message.set_name('SQLGL', 'GL_INVALID_PARAM');
226 fnd_message.set_token('VALUE', ledger_context);
227 fnd_message.set_token('PARAM', 'ledger_context');
228 RAISE INVALID_PARAM;
229 END IF;
230
231 IF (segval_mode = gl_access_set_security_pkg.NO_SEG_VALIDATION) THEN
232 RETURN (ledger_table || ledger_context || ' IN ( ' ||
233 'SELECT acc.ledger_id ' ||
234 'FROM gl_access_set_ledgers acc ' ||
235 'WHERE acc.access_set_id = ' || to_char(access_set_id) || ' '||
236 gl_access_set_security_pkg.build_privilege_clause(access_privilege_code) ||
237 gl_access_set_security_pkg.build_date_clause(edate) || ')' );
238 ELSIF (segval_mode = gl_access_set_security_pkg.CHECK_SEGVALS) THEN
239 RETURN ('(' || ledger_table || ledger_context || ', ' ||
240 segval_table || security_col || ') IN ( ' ||
241 'SELECT acc.ledger_id, acc.segment_value ' ||
242 'FROM gl_access_set_assignments acc ' ||
243 'WHERE acc.access_set_id = ' || to_char(access_set_id) || ' '||
244 gl_access_set_security_pkg.build_privilege_clause(access_privilege_code) ||
245 gl_access_set_security_pkg.build_date_clause(edate) || ')' );
246 END IF;
247
248 ELSIF (ledger_check_mode = gl_access_set_security_pkg.CHECK_LEDGER_ID) THEN
249
250 BEGIN
251 SELECT to_number(ledger_context)
252 INTO dum_num
253 FROM dual;
254
255 IF (ledger_context IS NULL) THEN
256 RAISE INVALID_NUMBER;
257 END IF;
258
259 EXCEPTION
260 WHEN INVALID_NUMBER THEN
261 fnd_message.set_name('SQLGL', 'GL_INVALID_PARAM');
262 fnd_message.set_token('VALUE', ledger_context);
263 fnd_message.set_token('PARAM', 'ledger_context');
264 RAISE INVALID_PARAM;
265
266 END;
267
268 IF (segval_mode = gl_access_set_security_pkg.NO_SEG_VALIDATION) THEN
269 RETURN ('');
270 ELSIF (segval_mode = gl_access_set_security_pkg.CHECK_SEGVALS) THEN
271 RETURN (segval_table || security_col || ' IN ( ' ||
272 'SELECT acc.segment_value ' ||
273 'FROM gl_access_set_assignments acc ' ||
274 'WHERE acc.access_set_id = ' || to_char(access_set_id) || ' '||
275 'AND acc.ledger_id = ' || ledger_context || ' ' ||
276 gl_access_set_security_pkg.build_privilege_clause(access_privilege_code) ||
277 gl_access_set_security_pkg.build_date_clause(edate) || ')' );
278 END IF;
279
280 ELSIF (ledger_check_mode = gl_access_set_security_pkg.NO_LEDGER) THEN
281 IF (segval_mode = gl_access_set_security_pkg.NO_SEG_VALIDATION) THEN
282 RETURN ('');
283 ELSIF (segval_mode = gl_access_set_security_pkg.CHECK_SEGVALS) THEN
284 RETURN (segval_table || security_col || ' IN ( ' ||
285 'SELECT acc.segment_value ' ||
286 'FROM gl_access_set_assignments acc ' ||
287 'WHERE acc.access_set_id = ' || to_char(access_set_id) || ' '||
288 gl_access_set_security_pkg.build_privilege_clause(access_privilege_code) ||
289 gl_access_set_security_pkg.build_date_clause(edate) || ')' );
290 END IF;
291
292 ELSE
293 fnd_message.set_name('SQLGL', 'GL_INVALID_PARAM');
294 fnd_message.set_token('VALUE', ledger_check_mode);
295 fnd_message.set_token('PARAM', 'ledger_check_mode');
296 RAISE INVALID_PARAM;
297 END IF;
298
299 fnd_message.set_name('SQLGL', 'GL_INVALID_PARAM');
300 fnd_message.set_token('VALUE', segval_check_mode);
301 fnd_message.set_token('PARAM', 'segval_check_mode');
302 RAISE INVALID_PARAM;
303
304 END get_security_clause;
305
306 FUNCTION get_journal_security_clause( access_set_id NUMBER,
307 access_privilege_code VARCHAR2,
308 segval_check_mode VARCHAR2,
309 journal_table_alias VARCHAR2,
310 check_edate BOOLEAN )
311 RETURN VARCHAR2 IS
312 security_col VARCHAR2(15);
313 security_code VARCHAR2(1);
314 segval_mode VARCHAR2(15) := segval_check_mode;
315
316 retstring VARCHAR2(2000);
317 need_check_edate BOOLEAN;
318 edatestr VARCHAR2(500) := null;
319 BEGIN
320
321 -- First, get an in clause with just ledgers, for efficiency
322 retstring := gl_access_set_security_pkg.get_security_clause(
323 access_set_id,
324 access_privilege_code,
325 gl_access_set_security_pkg.CHECK_LEDGER_COLUMN,
326 'LEDGER_ID',
327 journal_table_alias,
328 gl_access_set_security_pkg.NO_SEG_VALIDATION,
329 NULL,
330 NULL,
331 NULL);
332
333 -- Initialize the access set information. This call initializes
334 -- the c_security_code global variable.
335 security_col := get_security_column(access_set_id);
336 security_code := c_security_code;
337
338 -- See if we really need to check dates. We only need to check dates
339 -- for implicitly created access sets that were created for ledgers.
340 need_check_edate := FALSE;
341 -- IF (check_edate) THEN
342 -- IF (gl_access_set_security_pkg.check_dates(access_set_id)) THEN
343 -- need_check_edate := TRUE;
344 -- END IF;
345 -- END IF;
346
347 -- If the caller doesn't want segment validation or this is a ledger-only
348 -- access set, then only check segment values
349 IF ( (segval_mode = gl_access_set_security_pkg.NO_SEG_VALIDATION)
350 OR (security_col IS NULL)
351 ) THEN
352 IF (access_privilege_code = gl_access_set_security_pkg.READ_ONLY_ACCESS) THEN
353 IF (need_check_edate) THEN
354 -- We know that the ledger is good, we just need to check the date.
355 RETURN(retstring || ' '||
356 'AND EXISTS ' ||
357 '(SELECT ''valid date'' '||
358 'FROM gl_access_set_ledgers acc ' ||
359 'WHERE acc.access_set_id = ' || to_char(access_set_id) || ' '||
360 'AND acc.ledger_id = '||journal_table_alias||'.ledger_id '||
361 'AND '||journal_table_alias||'.default_effective_date ' ||
362 'BETWEEN nvl(acc.start_date, '||
363 journal_table_alias||'.default_effective_date-1) '||
364 'AND nvl(acc.end_date, '||
365 journal_table_alias||'.default_effective_date+1) '||
366 ')');
367 ELSE
368 RETURN(retstring);
369 END IF;
370 ELSE
371 IF (need_check_edate) THEN
372 edatestr :=
373 'AND sv.default_effective_date ' ||
374 'BETWEEN nvl(acc.start_date, sv.default_effective_date-1) '||
375 'AND nvl(acc.end_date, sv.default_effective_date+1) ';
376 END IF;
377
378 -- For write access, just check the other journals in the batch
379 RETURN(retstring || ' '||
380 'AND NOT EXISTS ' ||
381 '(SELECT ''unwriteable journal'' '||
382 'FROM gl_je_headers sv ' ||
383 'WHERE sv.je_batch_id = '||journal_table_alias||'.je_batch_id '||
384 'AND NOT EXISTS ' ||
385 '(SELECT ''no access'' ' ||
386 'FROM gl_access_set_ledgers acc ' ||
387 'WHERE acc.access_set_id = ' || to_char(access_set_id) || ' '||
388 'AND acc.ledger_id = sv.ledger_id '||
389 'AND acc.access_privilege_code IN (''B'', ''F'') '||
390 edatestr || '))');
391 END IF;
392 END IF;
393
394 IF (access_privilege_code = gl_access_set_security_pkg.READ_ONLY_ACCESS) THEN
395 IF (need_check_edate) THEN
396 edatestr :=
397 'AND '||journal_table_alias||'.default_effective_date ' ||
398 'BETWEEN nvl(acc.start_date, '||
399 journal_table_alias||'.default_effective_date-1) '||
400 'AND nvl(acc.end_date, ' ||
401 journal_table_alias||'.default_effective_date+1) ';
402 END IF;
403
404 RETURN(retstring || ' '||
405 'AND EXISTS ' ||
406 '(SELECT ''readable line'' '||
407 'FROM gl_je_segment_values sv, ' ||
408 'gl_access_set_assignments acc ' ||
409 'WHERE sv.je_header_id = '||journal_table_alias||'.je_header_id '||
410 'AND sv.segment_type_code = '''||security_code||''' '||
411 'AND acc.access_set_id = '||to_char(access_set_id)|| ' '||
412 'AND acc.ledger_id = '||journal_table_alias||'.ledger_id '||
413 'AND acc.segment_value = sv.segment_value '||
414 edatestr || ') ');
415 ELSE -- WRITE_ACCESS
416 IF (need_check_edate) THEN
417 edatestr :=
418 'AND sv2.default_effective_date ' ||
419 'BETWEEN nvl(acc.start_date, sv2.default_effective_date-1) '||
420 'AND nvl(acc.end_date, sv2.default_effective_date+1) ';
421 END IF;
422
423 RETURN(retstring || ' '||
424 'AND NOT EXISTS ' ||
425 '(SELECT ''unwriteable line'' '||
426 'FROM gl_je_segment_values sv, gl_je_headers sv2 ' ||
427 'WHERE sv2.je_batch_id = '||journal_table_alias||'.je_batch_id '||
428 'AND sv.je_header_id = sv2.je_header_id ' ||
429 'AND sv.segment_type_code = '''||security_code||''' '||
430 'AND NOT EXISTS '||
431 '(SELECT ''unwriteable line'' ' ||
432 'FROM gl_access_set_assignments acc ' ||
433 'WHERE acc.access_set_id = '||to_char(access_set_id)|| ' '||
434 'AND acc.ledger_id = sv2.ledger_id '||
435 'AND acc.segment_value = sv.segment_value '||
436 'AND acc.access_privilege_code = ''B'' '||
437 edatestr || ')) ');
438 END IF;
439
440 END get_journal_security_clause;
441
442 FUNCTION get_batch_security_clause( access_set_id NUMBER,
443 access_privilege_code VARCHAR2,
444 segval_check_mode VARCHAR2,
445 batch_table_alias VARCHAR2,
446 check_edate BOOLEAN )
447 RETURN VARCHAR2 IS
448 security_col VARCHAR2(15);
449 security_code VARCHAR2(1);
450 segval_mode VARCHAR2(15) := segval_check_mode;
451 ledger_table VARCHAR2(30);
452 segval_table VARCHAR2(30);
453
454 retstring VARCHAR2(2000);
455 dum_num NUMBER(15);
456 edatestr VARCHAR2(500) := null;
457 BEGIN
458
459 -- Initialize the access set information. This call initializes
460 -- the c_security_code global variable.
461 security_col := get_security_column(access_set_id);
462 security_code := c_security_code;
463
464 -- See if we really need to check dates. We only need to check dates
465 -- for implicitly created access sets that were created for ledgers.
466 --IF (check_edate) THEN
467 -- IF (gl_access_set_security_pkg.check_dates(access_set_id)) THEN
468 -- edatestr :=
469 -- 'AND jeh.default_effective_date ' ||
470 -- 'BETWEEN nvl(acc.start_date, jeh.default_effective_date-1) '||
471 -- 'AND nvl(acc.end_date, jeh.default_effective_date+1) ';
472 -- END IF;
473 --END IF;
474
475 -- If the caller doesn't want segment validation or this is a ledger-only
476 -- access set, then build a statement without segment validation
477 IF ( (segval_mode = gl_access_set_security_pkg.NO_SEG_VALIDATION)
478 OR (security_col IS NULL)) THEN
479 IF (access_privilege_code = gl_access_set_security_pkg.READ_ONLY_ACCESS) THEN
480 RETURN(
481 'EXISTS ' ||
482 '(SELECT ''readable journal'' '||
483 'FROM gl_je_headers jeh, ' ||
484 'gl_access_set_ledgers acc ' ||
485 'WHERE jeh.je_batch_id = '||batch_table_alias||'.je_batch_id '||
486 'AND acc.access_set_id = '||to_char(access_set_id)||' '||
487 'AND acc.ledger_id = jeh.ledger_id ' ||
488 edatestr || ') ');
489 ELSE -- write access
490 RETURN(
491 'NOT EXISTS ' ||
492 '(SELECT ''unwriteable journal'' '||
493 'FROM gl_je_headers jeh ' ||
494 'WHERE jeh.je_batch_id = '||batch_table_alias||'.je_batch_id '||
495 'AND NOT EXISTS ' ||
496 '(SELECT ''unwriteable journal'' '||
497 'FROM gl_access_set_ledgers acc ' ||
498 'WHERE acc.access_set_id = '||to_char(access_set_id)||' '||
499 'AND acc.ledger_id = jeh.ledger_id ' ||
500 'AND acc.access_privilege_code IN (''B'', ''F'') '||
501 edatestr || ')) ');
502 END IF;
503
504 ELSE -- Need to check segment values
505 IF (access_privilege_code = gl_access_set_security_pkg.READ_ONLY_ACCESS) THEN
506 RETURN(
507 'EXISTS ' ||
508 '(SELECT ''readable line'' '||
509 'FROM gl_je_headers jeh, '||
510 'gl_je_segment_values sv, ' ||
511 'gl_access_set_assignments acc ' ||
512 'WHERE jeh.je_batch_id = '||batch_table_alias||'.je_batch_id '||
513 'AND sv.je_header_id = jeh.je_header_id '||
514 'AND sv.segment_type_code = '''||security_code||''' '||
515 'AND acc.access_set_id = '||to_char(access_set_id)|| ' '||
516 'AND acc.ledger_id = jeh.ledger_id '||
517 'AND acc.segment_value = sv.segment_value '||
518 edatestr || ') ');
519 ELSE -- WRITE_ACCESS
520 RETURN(
521 'NOT EXISTS ' ||
522 '(SELECT ''unwriteable line'' '||
523 'FROM gl_je_headers jeh, '||
524 'gl_je_segment_values sv ' ||
525 'WHERE jeh.je_batch_id = '||batch_table_alias||'.je_batch_id '||
526 'AND sv.je_header_id = jeh.je_header_id '||
527 'AND sv.segment_type_code = '''||security_code||''' '||
528 'AND NOT EXISTS '||
529 '(SELECT ''unwriteable line'' ' ||
530 'FROM gl_access_set_assignments acc ' ||
531 'WHERE acc.access_set_id = '||to_char(access_set_id)|| ' '||
532 'AND acc.ledger_id = jeh.ledger_id '||
533 'AND acc.segment_value = sv.segment_value '||
534 'AND acc.access_privilege_code = ''B'' '||
535 edatestr || ')) ');
536 END IF;
537 END IF;
538 END get_batch_security_clause;
539
540 FUNCTION get_journal_access ( access_set_id IN NUMBER,
541 header_only IN BOOLEAN,
542 check_mode IN VARCHAR2,
543 je_id IN NUMBER )
544 RETURN VARCHAR2 IS
545 security_col VARCHAR2(15);
546 security_code VARCHAR2(1);
547 access_level VARCHAR2(1);
548 dummy VARCHAR2(25);
549 sqlbuf VARCHAR2(2000);
550 lines_exist BOOLEAN;
551 BEGIN
552 -- Initialize the access set information. This call initializes
553 -- the c_security_code global variable.
554 security_col := get_security_column(access_set_id);
555 security_code := c_security_code;
556
557 -- Verify whether any lines exist
558 lines_exist := FALSE;
559 IF (security_code IS NOT NULL) THEN
560 BEGIN
561 IF (NOT header_only) THEN
562 SELECT 'has lines'
563 INTO dummy
564 FROM gl_je_headers jeh
565 WHERE jeh.je_batch_id = je_id
566 AND rownum = 1;
567 ELSE
568 -- Check for lines
569 SELECT 'has lines'
570 INTO dummy
571 FROM gl_je_headers jeh
572 WHERE jeh.je_header_id = je_id
573 AND rownum = 1;
574 END IF;
575
576 lines_exist := TRUE;
577 EXCEPTION
578 WHEN NO_DATA_FOUND THEN
579 -- If it has journals but no lines, then only check journals
580 security_code := NULL;
581 END;
582 END IF;
583
584 -- If we haven't found lines (or haven't checked) and we are
585 -- checking a batch, then verify that at least one journal exists
586 IF (NOT lines_exist AND NOT header_only) THEN
587 BEGIN
588 SELECT 'has journals'
589 INTO dummy
590 FROM gl_je_headers jeh
591 WHERE jeh.je_batch_id = je_id
592 AND rownum = 1;
593
594 EXCEPTION
595 WHEN NO_DATA_FOUND THEN
596 -- Anyone can write to a batch with no journals
597 RETURN(nvl(check_mode,gl_access_set_security_pkg.WRITE_ACCESS));
598 END;
599 END IF;
600
601 -- Check whether we are to check for write access
602 IF (nvl(check_mode,gl_access_set_security_pkg.WRITE_ACCESS)
603 <> gl_access_set_security_pkg.READ_ONLY_ACCESS) THEN
604
605 sqlbuf := 'SELECT ''no write'' '||
606 'FROM dual ' ||
607 'WHERE EXISTS ' ||
608 '(SELECT ''no write'' ' ||
609 'FROM gl_je_headers jeh ';
610
611 -- For ledger-only access sets, don't include gl_je_segment_values
612 IF (security_col IS NOT NULL) THEN
613 sqlbuf := sqlbuf ||
614 ', gl_je_segment_values sv ';
615 END IF;
616
617 IF (header_only) THEN
618 sqlbuf := sqlbuf ||
619 'WHERE jeh.je_header_id = :object_id ';
620 ELSE
621 sqlbuf := sqlbuf ||
622 'WHERE jeh.je_batch_id = :object_id ';
623 END IF;
624
625 -- For ledger-only access sets, don't include gl_je_segment_values
626 IF (security_col IS NOT NULL) THEN
627 sqlbuf := sqlbuf ||
628 'AND sv.je_header_id = jeh.je_header_id ' ||
629 'AND sv.segment_type_code = :security_seg ';
630 END IF;
631
632 sqlbuf := sqlbuf ||
633 'AND NOT EXISTS ' ||
634 '(SELECT ''write row'' '||
635 'FROM gl_access_set_assignments asa ' ||
636 'WHERE asa.access_set_id = :access_set_id ' ||
637 'AND asa.ledger_id = jeh.ledger_id ' ||
638 'AND asa.access_privilege_code = ''B'' ';
639
640 IF (security_col IS NOT NULL) THEN
641 sqlbuf := sqlbuf ||
642 'AND asa.segment_value = sv.segment_value ';
643
644 END IF;
645
646 sqlbuf := sqlbuf ||
647 'AND jeh.default_effective_date BETWEEN NVL(asa.start_date, '||
648 'jeh.default_effective_date - 1) '||
649 'AND NVL(asa.end_date, ' ||
650 'jeh.default_effective_date + 1))) ';
651
652 BEGIN
653 IF (security_col IS NOT NULL)THEN
654 EXECUTE IMMEDIATE sqlbuf
655 INTO dummy
656 USING IN je_id,
657 IN security_code,
658 IN access_set_id;
659 ELSE
660 EXECUTE IMMEDIATE sqlbuf
661 INTO dummy
662 USING IN je_id,
663 IN access_set_id;
664 END IF;
665
666 EXCEPTION
667 -- If nothing has been returned then we know that we DO
668 -- have write, so return write
669 WHEN NO_DATA_FOUND THEN
670 RETURN(gl_access_set_security_pkg.WRITE_ACCESS);
671 END;
672 END IF;
673
674 -- Check whether we are to check for read-only access
675 IF (nvl(check_mode, gl_access_set_security_pkg.READ_ONLY_ACCESS)
676 <> gl_access_set_security_pkg.WRITE_ACCESS) THEN
677 sqlbuf := 'SELECT ''has read'' '||
678 'FROM dual ' ||
679 'WHERE EXISTS ' ||
680 '(SELECT ''has read'' ' ||
681 'FROM gl_je_headers jeh, ';
682
683 -- For ledger-only access sets, don't include gl_je_segment_values
684 IF (security_col IS NOT NULL) THEN
685 sqlbuf := sqlbuf ||
686 'gl_je_segment_values sv, ';
687 END IF;
688
689 sqlbuf := sqlbuf ||
690 'gl_access_set_assignments asa ';
691
692 IF (header_only) THEN
693 sqlbuf := sqlbuf ||
694 'WHERE jeh.je_header_id = :object_id ';
695 ELSE
696 sqlbuf := sqlbuf ||
697 'WHERE jeh.je_batch_id = :object_id ';
698 END IF;
699
700 -- For ledger-only access sets, don't include gl_je_segment_values
701 IF (security_col IS NOT NULL) THEN
702 sqlbuf := sqlbuf ||
703 'AND sv.je_header_id = jeh.je_header_id ' ||
704 'AND sv.segment_type_code = :security_seg ' ||
705 'AND asa.segment_value = sv.segment_value ';
706 END IF;
707
708 sqlbuf := sqlbuf ||
709 'AND asa.access_set_id = :access_set_id ' ||
710 'AND asa.ledger_id = jeh.ledger_id ';
711
712 sqlbuf := sqlbuf ||
713 'AND jeh.default_effective_date BETWEEN NVL(asa.start_date, '||
714 'jeh.default_effective_date-1) '||
715 'AND NVL(asa.end_date, ' ||
716 'jeh.default_effective_date+1)) ';
717
718 BEGIN
719 IF (security_col IS NOT NULL)THEN
720 EXECUTE IMMEDIATE sqlbuf
721 INTO dummy
722 USING IN je_id,
723 IN security_code,
724 IN access_set_id;
725 ELSE
726 EXECUTE IMMEDIATE sqlbuf
727 INTO dummy
728 USING IN je_id,
729 IN access_set_id;
730 END IF;
731
732 IF (dummy IS NOT NULL) THEN
733 RETURN(gl_access_set_security_pkg.READ_ONLY_ACCESS);
734 END IF;
735 EXCEPTION
736 WHEN NO_DATA_FOUND THEN
737 NULL;
738 END;
739 END IF;
740
741 -- We haven't found anything, so return no access
742 RETURN(gl_access_set_security_pkg.NO_ACCESS);
743 END get_journal_access;
744
745 FUNCTION get_default_ledger_id( x_access_set_id NUMBER,
746 x_access_privilege_code VARCHAR2 )
747 RETURN NUMBER IS
748 CURSOR sdl IS
749 SELECT asl.ledger_id
750 FROM gl_access_set_ledgers asl
751 WHERE asl.access_set_id = x_access_set_id
752 AND asl.ledger_id = (select gas.default_ledger_id
753 from gl_access_sets gas
754 where gas.access_set_id = asl.access_set_id)
755 AND ( ( (x_access_privilege_code
756 = gl_access_set_security_pkg.FULL_ACCESS)
757 AND (asl.access_privilege_code = 'F'))
758 OR ( (x_access_privilege_code
759 = gl_access_set_security_pkg.WRITE_ACCESS)
760 AND (asl.access_privilege_code IN ('F', 'B')))
761 OR (x_access_privilege_code = 'R'));
762
763 CURSOR gdl IS
764 SELECT DISTINCT ledger_id
765 FROM gl_access_set_ledgers
766 WHERE access_set_id = x_access_set_id
767 AND ( ( (x_access_privilege_code
768 = gl_access_set_security_pkg.FULL_ACCESS)
769 AND (access_privilege_code = 'F'))
770 OR ( (x_access_privilege_code
771 = gl_access_set_security_pkg.WRITE_ACCESS)
772 AND (access_privilege_code IN ('F', 'B')))
773 OR (x_access_privilege_code = 'R'));
774
775 x_ledger_id NUMBER;
776 x_ledger_id2 NUMBER;
777 BEGIN
778 IF (x_access_privilege_code NOT IN
779 (gl_access_set_security_pkg.FULL_ACCESS,
780 gl_access_set_security_pkg.WRITE_ACCESS,
781 gl_access_set_security_pkg.READ_ONLY_ACCESS)) THEN
782 RETURN NULL;
783 END IF;
784
785 -- First choice: check if the access set has default ledger assigned that
786 -- satisfies the given access privilege level.
787 OPEN sdl;
788 FETCH sdl INTO x_ledger_id;
789 IF sdl%FOUND THEN
790 CLOSE sdl;
791 RETURN (x_ledger_id);
792 END IF;
793 CLOSE sdl;
794
795 -- Second choice: check if there is one and only one ledger assigned to
796 -- this access set that satisfies the access privilege level
797 OPEN gdl;
798 FETCH gdl INTO x_ledger_id;
799 IF gdl%FOUND THEN
800 FETCH gdl INTO x_ledger_id2;
801 IF gdl%FOUND THEN
802 x_ledger_id := null;
803 END IF;
804 END IF;
805 CLOSE gdl;
806
807 RETURN (x_ledger_id);
808 END get_default_ledger_id;
809
810 FUNCTION get_access( x_access_set_id NUMBER,
811 x_ledger_id NUMBER,
812 x_seg_qualifier VARCHAR2,
813 x_seg_val VARCHAR2,
814 x_code_combination_id NUMBER,
815 x_edate DATE ) RETURN VARCHAR2 IS
816 seg_val VARCHAR2(30);
817 acc_priv_code VARCHAR2(1);
818 security_col VARCHAR2(15);
819 sql_stmt VARCHAR2(100);
820
821 CURSOR check_access_ledger IS
822 SELECT access_privilege_code
823 FROM gl_access_set_ledgers
824 WHERE access_set_id = x_access_set_id
825 AND ledger_id = x_ledger_id
826 AND ( (x_edate IS NULL)
827 OR (trunc(x_edate) BETWEEN nvl(trunc(start_date), trunc(x_edate)-1)
828 AND nvl(trunc(end_date), trunc(x_edate)+1)));
829
830 CURSOR check_access_segment IS
831 SELECT decode(max(decode(access_privilege_code, 'B', 2, 1)),
832 1, 'R', 2, 'B', 'N')
833 FROM gl_access_set_assignments
834 WHERE access_set_id = x_access_set_id
835 AND segment_value = seg_val
836 AND ( (x_edate IS NULL)
837 OR (trunc(x_edate) BETWEEN nvl(trunc(start_date), trunc(x_edate)-1)
838 AND nvl(trunc(end_date), trunc(x_edate)+1)));
839
840 CURSOR check_access_ls IS
841 SELECT access_privilege_code
842 FROM gl_access_set_assignments
843 WHERE access_set_id = x_access_set_id
844 AND ledger_id = x_ledger_id
845 AND segment_value = seg_val
846 AND ( (x_edate IS NULL)
847 OR (trunc(x_edate) BETWEEN nvl(trunc(start_date), trunc(x_edate)-1)
848 AND nvl(trunc(end_date), trunc(x_edate)+1)));
849
850 BEGIN
851 security_col := get_security_column(x_access_set_id);
852
853 -- Resolve segment value
854 IF ( (security_col IS NULL)
855 OR (x_seg_qualifier IS NULL
856 AND x_seg_val IS NULL
857 AND x_code_combination_id IS NULL)) THEN
858 -- It is a Ledger Only access set, or no segment information available:
859 -- segment value will not be checked
860 seg_val := null;
861
862 ELSIF (x_seg_qualifier IS NULL OR x_seg_val IS NULL) THEN
863
864 IF (x_code_combination_id IS NOT NULL) THEN
865 -- find the segment value from ccid
866 sql_stmt := 'SELECT max(' || security_col || ') ' ||
867 'FROM gl_code_combinations ' ||
868 'WHERE code_combination_id = :x_ccid';
869
870 EXECUTE IMMEDIATE sql_stmt INTO seg_val USING x_code_combination_id;
871 ELSE
872 seg_val := null;
873 END IF;
874
875 ELSIF ( (c_security_code = 'B' AND x_seg_qualifier = 'GL_BALANCING')
876 OR (c_security_code = 'M' AND x_seg_qualifier = 'GL_MANAGEMENT')) THEN
877 -- use the segment value passed in
878 seg_val := x_seg_val;
879 END IF;
880
881 -- Use the proper cursor to get the access level
882 IF (x_ledger_id IS NULL) THEN
883
884 IF (seg_val IS NULL) THEN -- no check needed
885 acc_priv_code := WRITE_ACCESS;
886 ELSE -- only checks segment value
887 OPEN check_access_segment;
888 FETCH check_access_segment INTO acc_priv_code;
889 IF (check_access_segment%NOTFOUND) THEN
890 acc_priv_code := NO_ACCESS;
891 END IF;
892 CLOSE check_access_segment;
893 END IF;
894
895 ELSIF (seg_val IS NULL) THEN -- only checks ledger
896 OPEN check_access_ledger;
897 FETCH check_access_ledger INTO acc_priv_code;
898 IF (check_access_ledger%NOTFOUND) THEN
899 acc_priv_code := NO_ACCESS;
900 END IF;
901 CLOSE check_access_ledger;
902
903 ELSE -- checks both ledger and segment value
904 OPEN check_access_ls;
905 FETCH check_access_ls INTO acc_priv_code;
906 IF (check_access_ls%NOTFOUND) THEN
907 acc_priv_code := NO_ACCESS;
908 END IF;
909 CLOSE check_access_ls;
910 END IF;
911
912 RETURN acc_priv_code;
913 END get_access;
914
915 END gl_access_set_security_pkg;