[Home] [Help]
PACKAGE BODY: APPS.GL_ACCESS_DETAILS_PKG
Source
1 PACKAGE BODY gl_access_details_pkg AS
2 /* $Header: glistadb.pls 120.7 2005/05/05 01:21:59 kvora ship $ */
3
4 FUNCTION get_record_id RETURN NUMBER IS
5 CURSOR get_new_id IS
6 SELECT GL_ACCESS_SET_NORM_ASSIGN_S.NEXTVAL
7 FROM dual;
8 new_id NUMBER;
9 BEGIN
10 OPEN get_new_id;
11 FETCH get_new_id INTO new_id;
12
13 IF get_new_id%FOUND THEN
14 CLOSE get_new_id;
15 RETURN (new_id);
16 ELSE
17 CLOSE get_new_id;
18 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
19 fnd_message.set_token('SEQUENCE', 'GL_ACCESS_SET_NORM_ASSIGN_S');
20 app_exception.raise_exception;
21 END IF;
22
23 EXCEPTION
24 WHEN app_exceptions.application_exception THEN
25 RAISE;
26 WHEN OTHERS THEN
27 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
28 fnd_message.set_token('PROCEDURE',
29 'gl_access_details_pkg.next_record_id');
30 RAISE;
31 END get_record_id;
32
33 FUNCTION is_ledger_set(X_Ledger_Id NUMBER) RETURN BOOLEAN IS
34 type_code VARCHAR2(1);
35 BEGIN
36 SELECT object_type_code
37 INTO type_code
38 FROM GL_LEDGERS
39 WHERE ledger_id = X_Ledger_Id;
40
41 RETURN (type_code = 'S');
42 END is_ledger_set;
43
44 PROCEDURE Insert_Row(
45 X_Rowid IN OUT NOCOPY VARCHAR2,
46 X_Access_Set_Id NUMBER,
47 X_Ledger_Id NUMBER,
48 X_All_Segment_Value_Flag VARCHAR2,
49 X_Segment_Value_Type_Code VARCHAR2,
50 X_Access_Privilege_Code VARCHAR2,
51 X_Record_Id NUMBER,
52 X_User_Id NUMBER,
53 X_Login_Id NUMBER,
54 X_Date DATE,
55 X_Segment_Value VARCHAR2 DEFAULT NULL,
56 X_Start_Date DATE DEFAULT NULL,
57 X_End_Date DATE DEFAULT NULL,
58 X_Status_Code VARCHAR2 DEFAULT NULL,
59 X_Link_Id NUMBER DEFAULT NULL,
60 X_Request_Id NUMBER DEFAULT NULL,
61 X_Context VARCHAR2 DEFAULT NULL,
62 X_Attribute1 VARCHAR2 DEFAULT NULL,
63 X_Attribute2 VARCHAR2 DEFAULT NULL,
64 X_Attribute3 VARCHAR2 DEFAULT NULL,
65 X_Attribute4 VARCHAR2 DEFAULT NULL,
66 X_Attribute5 VARCHAR2 DEFAULT NULL,
67 X_Attribute6 VARCHAR2 DEFAULT NULL,
68 X_Attribute7 VARCHAR2 DEFAULT NULL,
69 X_Attribute8 VARCHAR2 DEFAULT NULL,
70 X_Attribute9 VARCHAR2 DEFAULT NULL,
71 X_Attribute10 VARCHAR2 DEFAULT NULL,
72 X_Attribute11 VARCHAR2 DEFAULT NULL,
73 X_Attribute12 VARCHAR2 DEFAULT NULL,
74 X_Attribute13 VARCHAR2 DEFAULT NULL,
75 X_Attribute14 VARCHAR2 DEFAULT NULL,
76 X_Attribute15 VARCHAR2 DEFAULT NULL
77 ) IS
78 CURSOR C IS
79 SELECT rowid
80 FROM GL_ACCESS_SET_NORM_ASSIGN
81 WHERE access_set_id = X_Access_Set_Id
82 AND ledger_id = X_Ledger_Id
83 AND all_segment_value_flag = X_All_Segment_Value_Flag
84 AND segment_value_type_code = X_Segment_Value_Type_Code
85 AND access_privilege_code = X_Access_Privilege_Code
86 AND (segment_value = X_Segment_Value OR segment_value IS NULL)
87 AND (start_date = X_Start_Date OR start_date IS NULL)
88 AND (end_date = X_End_Date OR end_date IS NULL);
89 BEGIN
90 INSERT INTO GL_ACCESS_SET_NORM_ASSIGN (
91 access_set_id,
92 ledger_id,
93 all_segment_value_flag,
94 segment_value_type_code,
95 access_privilege_code,
96 record_id,
97 last_update_date,
98 last_updated_by,
99 creation_date,
100 created_by,
101 last_update_login,
102 segment_value,
103 start_date,
104 end_date,
105 status_code,
106 link_id,
107 request_id,
108 context,
109 attribute1,
110 attribute2,
111 attribute3,
112 attribute4,
113 attribute5,
114 attribute6,
115 attribute7,
116 attribute8,
117 attribute9,
118 attribute10,
119 attribute11,
120 attribute12,
121 attribute13,
122 attribute14,
123 attribute15
124 ) VALUES (
125 X_Access_Set_Id,
126 X_Ledger_Id,
127 X_All_Segment_Value_Flag,
128 X_Segment_Value_Type_Code,
129 X_Access_Privilege_Code,
130 X_Record_Id,
131 X_Date,
132 X_User_Id,
133 X_Date,
134 X_User_Id,
135 X_Login_Id,
136 X_Segment_Value,
137 X_Start_Date,
138 X_End_Date,
139 X_Status_Code,
140 X_Link_Id,
141 X_Request_Id,
142 X_Context,
143 X_Attribute1,
144 X_Attribute2,
145 X_Attribute3,
146 X_Attribute4,
147 X_Attribute5,
148 X_Attribute6,
149 X_Attribute7,
150 X_Attribute8,
151 X_Attribute9,
152 X_Attribute10,
153 X_Attribute11,
154 X_Attribute12,
155 X_Attribute13,
156 X_Attribute14,
157 X_Attribute15
158 );
159
160 OPEN C;
161 FETCH C INTO X_Rowid;
162 if (C%NOTFOUND) then
163 CLOSE C;
164 RAISE NO_DATA_FOUND;
165 end if;
166 CLOSE C;
167 END Insert_Row;
168
169 PROCEDURE Lock_Row(
170 X_Rowid VARCHAR2,
171 X_Access_Set_Id NUMBER,
172 X_Ledger_Id NUMBER,
173 X_All_Segment_Value_Flag VARCHAR2,
174 X_Segment_Value_Type_Code VARCHAR2,
175 X_Access_Privilege_Code VARCHAR2,
176 X_Record_Id NUMBER,
177 X_Last_Update_Date DATE,
178 X_Last_Updated_By NUMBER,
179 X_Creation_Date DATE,
180 X_Created_By NUMBER,
181 X_Last_Update_Login NUMBER,
182 X_Segment_Value VARCHAR2 DEFAULT NULL,
183 X_Start_Date DATE DEFAULT NULL,
184 X_End_Date DATE DEFAULT NULL,
185 X_Status_Code VARCHAR2 DEFAULT NULL,
186 X_Link_Id NUMBER DEFAULT NULL,
187 X_Request_Id NUMBER DEFAULT NULL,
188 X_Context VARCHAR2 DEFAULT NULL,
189 X_Attribute1 VARCHAR2 DEFAULT NULL,
190 X_Attribute2 VARCHAR2 DEFAULT NULL,
191 X_Attribute3 VARCHAR2 DEFAULT NULL,
192 X_Attribute4 VARCHAR2 DEFAULT NULL,
193 X_Attribute5 VARCHAR2 DEFAULT NULL,
194 X_Attribute6 VARCHAR2 DEFAULT NULL,
195 X_Attribute7 VARCHAR2 DEFAULT NULL,
196 X_Attribute8 VARCHAR2 DEFAULT NULL,
197 X_Attribute9 VARCHAR2 DEFAULT NULL,
198 X_Attribute10 VARCHAR2 DEFAULT NULL,
199 X_Attribute11 VARCHAR2 DEFAULT NULL,
200 X_Attribute12 VARCHAR2 DEFAULT NULL,
201 X_Attribute13 VARCHAR2 DEFAULT NULL,
202 X_Attribute14 VARCHAR2 DEFAULT NULL,
203 X_Attribute15 VARCHAR2 DEFAULT NULL
204 ) IS
205 CURSOR C IS
206 SELECT *
207 FROM GL_ACCESS_SET_NORM_ASSIGN
208 WHERE rowid = X_Rowid
209 FOR UPDATE of Access_Set_Id NOWAIT;
210 Recinfo C%ROWTYPE;
211 BEGIN
212 OPEN C;
213 FETCH C INTO Recinfo;
214 if (C%NOTFOUND) then
215 CLOSE C;
216 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
217 APP_EXCEPTION.RAISE_EXCEPTION;
218 end if;
219 CLOSE C;
220
221 if (
222 ( (Recinfo.access_set_id = X_Access_Set_Id)
223 OR ( (Recinfo.access_set_id IS NULL)
224 AND (X_Access_Set_Id IS NULL)))
225 AND ( (Recinfo.ledger_id = X_Ledger_Id)
226 OR ( (Recinfo.ledger_id IS NULL)
227 AND (X_Ledger_Id IS NULL)))
228 AND ( (Recinfo.all_segment_value_flag = X_All_Segment_Value_Flag)
229 OR ( (Recinfo.all_segment_value_flag IS NULL)
230 AND (X_All_Segment_Value_Flag IS NULL)))
234 AND ( (Recinfo.access_privilege_code = X_Access_Privilege_Code)
231 AND ( (Recinfo.segment_value_type_code = X_Segment_Value_Type_Code)
232 OR ( (Recinfo.segment_value_type_code IS NULL)
233 AND (X_Segment_Value_Type_Code IS NULL)))
235 OR ( (Recinfo.access_privilege_code IS NULL)
236 AND (X_Access_Privilege_Code IS NULL)))
237 AND ( (Recinfo.record_id = X_Record_Id)
238 OR ( (Recinfo.record_id IS NULL)
239 AND (X_Record_Id IS NULL)))
240 AND ( (Recinfo.last_update_date = X_Last_Update_Date)
241 OR ( (Recinfo.last_update_date IS NULL)
242 AND (X_Last_Update_Date IS NULL)))
243 AND ( (Recinfo.last_updated_by = X_Last_Updated_By)
244 OR ( (Recinfo.last_updated_by IS NULL)
245 AND (X_Last_Updated_By IS NULL)))
246 AND ( (Recinfo.creation_date = X_Creation_Date)
247 OR ( (Recinfo.creation_date IS NULL)
248 AND (X_Creation_Date IS NULL)))
249 AND ( (Recinfo.created_by = X_Created_By)
250 OR ( (Recinfo.created_by IS NULL)
251 AND (X_Created_By IS NULL)))
252 AND ( (Recinfo.last_update_login = X_Last_Update_Login)
253 OR ( (Recinfo.last_update_login IS NULL)
254 AND (X_Last_Update_Login IS NULL)))
255 AND ( (Recinfo.segment_value = X_Segment_Value)
256 OR ( (Recinfo.segment_value IS NULL)
257 AND (X_Segment_Value IS NULL)))
258 AND ( (Recinfo.start_date = X_Start_Date)
259 OR ( (Recinfo.start_date IS NULL)
260 AND (X_Start_Date IS NULL)))
261 AND ( (Recinfo.end_date = X_End_Date)
262 OR ( (Recinfo.end_date IS NULL)
263 AND (X_End_Date IS NULL)))
264 AND ( (Recinfo.status_code = X_Status_Code)
265 OR ( (Recinfo.status_code IS NULL)
266 AND (X_Status_Code IS NULL)))
267 AND ( (Recinfo.link_id = X_Link_Id)
268 OR ( (Recinfo.link_id IS NULL)
269 AND (X_Link_Id IS NULL)))
270 AND ( (Recinfo.request_id = X_Request_Id)
271 OR ( (Recinfo.request_id IS NULL)
272 AND (X_Request_Id IS NULL)))
273 AND ( (Recinfo.context = X_Context)
274 OR ( (Recinfo.context IS NULL)
275 AND (X_Context IS NULL)))
276 AND ( (Recinfo.attribute1 = X_Attribute1)
277 OR ( (Recinfo.attribute1 IS NULL)
278 AND (X_Attribute1 IS NULL)))
279 AND ( (Recinfo.attribute2 = X_Attribute2)
280 OR ( (Recinfo.attribute2 IS NULL)
281 AND (X_Attribute2 IS NULL)))
282 AND ( (Recinfo.attribute3 = X_Attribute3)
283 OR ( (Recinfo.attribute3 IS NULL)
284 AND (X_Attribute3 IS NULL)))
285 AND ( (Recinfo.attribute4 = X_Attribute4)
286 OR ( (Recinfo.attribute4 IS NULL)
287 AND (X_Attribute4 IS NULL)))
288 AND ( (Recinfo.attribute5 = X_Attribute5)
289 OR ( (Recinfo.attribute5 IS NULL)
290 AND (X_Attribute5 IS NULL)))
291 AND ( (Recinfo.attribute6 = X_Attribute6)
292 OR ( (Recinfo.attribute6 IS NULL)
293 AND (X_Attribute6 IS NULL)))
294 AND ( (Recinfo.attribute7 = X_Attribute7)
295 OR ( (Recinfo.attribute7 IS NULL)
296 AND (X_Attribute7 IS NULL)))
297 AND ( (Recinfo.attribute8 = X_Attribute8)
298 OR ( (Recinfo.attribute8 IS NULL)
299 AND (X_Attribute8 IS NULL)))
300 AND ( (Recinfo.attribute9 = X_Attribute9)
301 OR ( (Recinfo.attribute9 IS NULL)
302 AND (X_Attribute9 IS NULL)))
303 AND ( (Recinfo.attribute10 = X_Attribute10)
304 OR ( (Recinfo.attribute10 IS NULL)
305 AND (X_Attribute10 IS NULL)))
306 AND ( (Recinfo.attribute11 = X_Attribute11)
307 OR ( (Recinfo.attribute11 IS NULL)
308 AND (X_Attribute11 IS NULL)))
309 AND ( (Recinfo.attribute12 = X_Attribute12)
310 OR ( (Recinfo.attribute12 IS NULL)
314 AND (X_Attribute13 IS NULL)))
311 AND (X_Attribute12 IS NULL)))
312 AND ( (Recinfo.attribute13 = X_Attribute13)
313 OR ( (Recinfo.attribute13 IS NULL)
315 AND ( (Recinfo.attribute14 = X_Attribute14)
316 OR ( (Recinfo.attribute14 IS NULL)
320 AND (X_Attribute15 IS NULL)))
317 AND (X_Attribute14 IS NULL)))
318 AND ( (Recinfo.attribute15 = X_Attribute15)
319 OR ( (Recinfo.attribute15 IS NULL)
321 ) then
322 return;
323 else
324 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
325 APP_EXCEPTION.RAISE_EXCEPTION;
326 end if;
327 END Lock_Row;
328
329 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
330 BEGIN
331 UPDATE GL_ACCESS_SET_NORM_ASSIGN
332 SET status_code = 'D'
333 WHERE rowid = X_Rowid;
334
335 if SQL%NOTFOUND then
336 RAISE NO_DATA_FOUND;
337 end if;
338 END Delete_Row;
339
340 PROCEDURE check_duplicate(
341 X_Access_Set_Id NUMBER,
342 X_Ledger_Id NUMBER,
343 X_All_Segment_Value_Flag VARCHAR2,
344 X_Segment_Value_Type_Code VARCHAR2,
345 X_Access_Privilege_Code VARCHAR2,
346 X_Segment_Value VARCHAR2) IS
347 CURSOR get_duplicate IS
348 SELECT 'duplicate'
349 FROM gl_access_set_norm_assign
350 WHERE access_set_id = X_Access_Set_Id
351 AND ledger_id = X_Ledger_Id
352 AND all_segment_value_flag = X_All_Segment_Value_Flag
353 AND segment_value_type_code = X_Segment_Value_Type_Code
354 AND access_privilege_code = X_Access_Privilege_Code
355 AND nvl(segment_value,'X') = nvl(X_Segment_Value, 'X')
356 AND (status_code <> 'D' or status_code is NULL);
357
358 dummy VARCHAR2(100);
359
360 BEGIN
361 OPEN get_duplicate;
362 FETCH get_duplicate INTO dummy;
363
364 IF get_duplicate%FOUND THEN
365 CLOSE get_duplicate;
366 fnd_message.set_name('SQLGL', 'GL_ACCESS_SET_DUPLICATE_DETAIL');
367 app_exception.raise_exception;
368 END IF;
369
370 CLOSE get_duplicate;
371
372 EXCEPTION
373 WHEN app_exceptions.application_exception THEN
374 RAISE;
375 WHEN OTHERS THEN
376 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
377 fnd_message.set_token('PROCEDURE',
378 'GL_ACCESS_DETAILS_PKG.check_duplicate');
379 RAISE;
380 END check_duplicate;
381
382 PROCEDURE validate_access_detail(X_Das_Coa_Id NUMBER,
383 X_Das_Period_Set_Name VARCHAR2,
384 X_Das_Period_Type VARCHAR2,
385 X_Das_Security_Code VARCHAR2,
386 X_Das_Value_Set_Id NUMBER,
387 X_Ledger_Id NUMBER,
388 X_All_Segment_Value_Flag VARCHAR2,
389 X_Segment_Value VARCHAR2,
390 X_Segment_Value_Type_Code VARCHAR2) IS
391 l_ledger_coa_id NUMBER;
392 l_ledger_period_set_name VARCHAR2(30);
393 l_ledger_period_type VARCHAR2(30);
394
395 l_summary_flag VARCHAR2(1);
396 BEGIN
397 -- get ledger info
398 SELECT chart_of_accounts_id, period_set_name, accounted_period_type
399 INTO l_ledger_coa_id, l_ledger_period_set_name, l_ledger_period_type
400 FROM GL_LEDGERS
401 WHERE ledger_id = X_Ledger_Id;
402
403 -- check ledger info against access set
404 IF ( X_das_coa_id <> l_ledger_coa_id
405 OR X_das_period_set_name <> l_ledger_period_set_name
406 OR X_das_period_type <> l_ledger_period_type) THEN
407 fnd_message.set_name('SQLGL', 'GL_API_DAS_DETL_LEDGER_ERROR');
408 app_exception.raise_exception;
409 END IF;
410
411 -- check access set type vs. all_segment_value_flag
412 IF (X_das_security_code = 'F' AND X_All_Segment_Value_Flag <> 'Y') THEN
413 fnd_message.set_name('SQLGL', 'GL_API_DEPENDENT_VALUE');
414 fnd_message.set_token('DEPATTR', 'AllSegmentValueFlag');
415 fnd_message.set_token('VALUE', X_das_security_code);
416 fnd_message.set_token('ATTRIBUTE', 'SecuritySegmentCode');
417 app_exception.raise_exception;
418 END IF;
419
420 -- check all_segment_value_flag, segment_value and segment_value_type_code
421 IF (X_All_Segment_Value_Flag = 'Y') THEN
422 IF (X_Segment_Value IS NOT NULL) THEN
423 fnd_message.set_name('SQLGL', 'GL_API_DEP_NULL_VALUE');
424 fnd_message.set_token('DEPATTR', 'SegmentValue');
425 fnd_message.set_token('VALUE', X_All_Segment_Value_Flag);
426 fnd_message.set_token('ATTRIBUTE', 'AllSegmentValueFlag');
427 app_exception.raise_exception;
428 ELSIF (X_Segment_Value_Type_Code <> 'S') THEN
429 fnd_message.set_name('SQLGL', 'GL_API_DEPENDENT_VALUE');
430 fnd_message.set_token('DEPATTR', 'SegmentValueTypeCode');
431 fnd_message.set_token('VALUE', X_All_Segment_Value_Flag);
432 fnd_message.set_token('ATTRIBUTE', 'AllSegmentValueFlag');
433 app_exception.raise_exception;
434 END IF;
435
436 ELSE
437 IF (X_Segment_Value IS NULL) THEN
438 fnd_message.set_name('SQLGL', 'GL_API_DEPENDENT_VALUE');
439 fnd_message.set_token('DEPATTR', 'SegmentValue');
440 fnd_message.set_token('VALUE', X_All_Segment_Value_Flag);
441 fnd_message.set_token('ATTRIBUTE', 'AllSegmentValueFlag');
442 app_exception.raise_exception;
443 ELSE
444 -- attempt to get the summary flag of the segment value
445 BEGIN
446 l_summary_flag := GL_FLEXFIELDS_PKG.get_summary_flag(
447 X_das_value_set_id,
448 X_Segment_Value);
449 EXCEPTION
450 WHEN NO_DATA_FOUND THEN
451 fnd_message.set_name('SQLGL', 'GL_API_VALUE_NOT_EXIST');
452 fnd_message.set_token('VALUE', X_Segment_Value);
453 fnd_message.set_token('ATTRIBUTE', 'SegmentValue');
454 app_exception.raise_exception;
455 WHEN OTHERS THEN
456 RAISE;
457 END;
458
459 IF ((l_summary_flag = 'Y' AND X_Segment_Value_Type_Code <> 'C') OR
460 (l_summary_flag = 'N' AND X_Segment_Value_Type_Code <> 'S')) THEN
461 fnd_message.set_name('SQLGL', 'GL_API_DEPENDENT_VALUE');
462 fnd_message.set_token('DEPATTR', 'SegmentValueTypeCode');
463 fnd_message.set_token('VALUE', X_Segment_Value);
464 fnd_message.set_token('ATTRIBUTE', 'SegmentValue');
465 app_exception.raise_exception;
466 END IF;
467
468 END IF; -- end X_Segment_Value
469 END IF; -- end X_All_Segment_Value_Flag
470
471 EXCEPTION
472 WHEN app_exceptions.application_exception THEN
473 RAISE;
474 WHEN OTHERS THEN
475 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
476 fnd_message.set_token('PROCEDURE',
477 'GL_ACCESS_DETAILS_PKG.validate_access_detail');
478 RAISE;
479 END validate_access_detail;
480
481 END gl_access_details_pkg;