1 PACKAGE BODY gl_je_header_security AS
2 /* $Header: gluhdsvb.pls 120.2 2003/04/24 01:38:12 djogg noship $ */
3
4 --
5 -- PUBLIC FUNCTIONS
6 --
7
8 -- **********************************************************************
9
10 -- NAME
11 -- check_header_valid_lsv
12 -- DESCRIPTION
13 -- Return 'Y' if all the ledger segment values are valid
14 -- for the given header and a date.
15 -- If no date is provided, the date is ignored.
16 FUNCTION check_header_valid_lsv ( x_je_header_id IN NUMBER,
17 x_edate IN DATE) RETURN VARCHAR2
18 IS
19 CURSOR has_all_lsv( p_je_header_id NUMBER )
20 IS
21 SELECT ldg.ledger_id,
22 ldg.bal_seg_value_option_code,
23 ldg.mgt_seg_value_option_code
24 FROM GL_LEDGERS ldg,
25 GL_JE_HEADERS h
26 WHERE ldg.ledger_id = h.ledger_id
27 AND h.je_header_id = p_je_header_id;
28
29 CURSOR is_invalid_lsv( p_je_header_id NUMBER,
30 p_segment_type_code VARCHAR2,
31 p_edate DATE,
32 p_ledger_id NUMBER)
33 IS
34 SELECT 'Invalid'
35 FROM GL_JE_SEGMENT_VALUES jsv
36 WHERE jsv.segment_type_code = p_segment_type_code
37 AND jsv.je_header_id = p_je_header_id
38 AND NOT EXISTS (SELECT 'Valid'
39 FROM GL_LEDGER_SEGMENT_VALUES sv
40 WHERE NVL(trunc(p_edate), NVL(sv.start_date,TO_DATE('1950/01/01','YYYY/MM/DD')))
41 BETWEEN NVL(sv.start_date,TO_DATE('1950/01/01','YYYY/MM/DD'))
42 AND NVL(sv.end_date,TO_DATE('9999/01/01','YYYY/MM/DD'))
43 AND sv.segment_value = jsv.segment_value
44 AND sv.segment_type_code = p_segment_type_code
45 AND sv.ledger_id = p_ledger_id);
46
47 CURSOR is_invalid( p_je_header_id NUMBER,
48 p_edate DATE,
49 p_ledger_id NUMBER)
50 IS
51 SELECT 'Invalid'
52 FROM GL_JE_SEGMENT_VALUES jsv
53 WHERE jsv.je_header_id = p_je_header_id
54 AND NOT EXISTS (SELECT 'Valid'
55 FROM GL_LEDGER_SEGMENT_VALUES sv
56 WHERE NVL(trunc(p_edate), NVL(sv.start_date,TO_DATE('1950/01/01','YYYY/MM/DD')))
57 BETWEEN NVL(sv.start_date,TO_DATE('1950/01/01','YYYY/MM/DD'))
58 AND NVL(sv.end_date,TO_DATE('9999/01/01','YYYY/MM/DD'))
59 AND sv.segment_value = jsv.segment_value
60 AND sv.segment_type_code = jsv.segment_type_code
61 AND sv.ledger_id = p_ledger_id);
62
63 dummy VARCHAR2(30);
64 x_ledger_id NUMBER(15);
65 bsv_option VARCHAR2(1);
66 msv_option VARCHAR2(1);
67 valid_lsv VARCHAR2(1) := 'N';
68
69 INVALID_JE_HEADER_ID EXCEPTION;
70
71 BEGIN
72 OPEN has_all_lsv( x_je_header_id );
73 FETCH has_all_lsv INTO x_ledger_id,
74 bsv_option,
75 msv_option;
76
77 IF (has_all_lsv%NOTFOUND) THEN
78 CLOSE has_all_lsv;
79 RAISE INVALID_JE_HEADER_ID;
80 ELSE
81 CLOSE has_all_lsv;
82 END IF;
83
84 IF (bsv_option = 'A' AND msv_option = 'A') THEN
85 -- All the lsvs are allowed, return Yes.
86 valid_lsv := 'Y';
87 RETURN(valid_lsv);
88
89 ELSIF (bsv_option = 'I' AND msv_option = 'A') THEN
90 -- All the msvs are allowed.
91 -- Check if all the bsvs of the header are allowed.
92 OPEN is_invalid_lsv(x_je_header_id,
93 'B',
94 x_edate,
95 x_ledger_id);
96 FETCH is_invalid_lsv INTO dummy;
97
98 IF is_invalid_lsv%FOUND THEN
99 CLOSE is_invalid_lsv;
100 RETURN(valid_lsv);
101 ELSE
102 CLOSE is_invalid_lsv;
103 valid_lsv := 'Y';
104 RETURN(valid_lsv);
105 END IF;
106
107 ELSIF (bsv_option = 'A' AND msv_option = 'I') THEN
108 -- All the bsvs are allowed.
109 -- Check if all the msvs of the header are allowed.
110 OPEN is_invalid_lsv(x_je_header_id,
111 'M',
112 x_edate,
113 x_ledger_id);
114 FETCH is_invalid_lsv INTO dummy;
115
116 IF is_invalid_lsv%FOUND THEN
117 CLOSE is_invalid_lsv;
118 RETURN(valid_lsv);
119 ELSE
120 CLOSE is_invalid_lsv;
121 valid_lsv := 'Y';
122 RETURN(valid_lsv);
123 END IF;
124
125 ELSIF (bsv_option = 'I' AND msv_option = 'I') THEN
126
127 -- Check if all the lsvs of the header are allowed.
128 OPEN is_invalid(x_je_header_id,
129 x_edate,
130 x_ledger_id);
131 FETCH is_invalid INTO dummy;
132
133 IF is_invalid%FOUND THEN
134 CLOSE is_invalid;
135 RETURN(valid_lsv);
136 ELSE
137 CLOSE is_invalid;
138 valid_lsv := 'Y';
139 RETURN(valid_lsv);
140 END IF;
141 END IF;
142
143 EXCEPTION
144 WHEN OTHERS THEN
145 app_exception.raise_exception;
146 END check_header_valid_lsv;
147
148 -- **********************************************************************
149
150 -- NAME
151 -- check_header_write_all
152 -- DESCRIPTION
153 -- Check both the valid ledger segment values and whether the user has write access.
154 -- The valid ledger segment values are checked first, then if they are valid,
155 -- the access privilege is checked.
156 FUNCTION check_header_write_all ( x_access_set_id IN NUMBER,
157 x_je_header_id IN NUMBER,
158 x_edate IN DATE) RETURN VARCHAR2
159 IS
160 valid_lsv VARCHAR2(1);
161 access_privilege_code VARCHAR2(1) := 'Z';
162 x_je_batch_id NUMBER;
163
164 BEGIN
165 -- Check the header segment values
166 valid_lsv := gl_je_header_security.check_header_valid_lsv ( x_je_header_id => x_je_header_id,
167 x_edate => x_edate);
168 IF (valid_lsv = 'N') THEN
169 RETURN (access_privilege_code);
170 END IF;
171
172 SELECT je_batch_id
173 INTO x_je_batch_id
174 FROM gl_je_headers
175 WHERE je_header_id = x_je_header_id;
176
177 -- Check if the user has write access to the header
178 access_privilege_code := gl_access_set_security_pkg.get_journal_access(
179 access_set_id => x_access_set_id,
180 header_only => FALSE,
181 check_mode => gl_access_set_security_pkg.WRITE_ACCESS,
182 je_id => x_je_batch_id);
183
184 IF (access_privilege_code=gl_access_set_security_pkg.WRITE_ACCESS) THEN
185 access_privilege_code := 'Y';
186 ELSE
187 access_privilege_code := 'N';
188 END IF;
189
190 RETURN (access_privilege_code);
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 app_exception.raise_exception;
195 END check_header_write_all;
196
197 END gl_je_header_security;
[Home] [Help]