[Home] [Help]
PACKAGE BODY: APPS.GL_AUTHORIZATION_LIMITS_PKG
Source
1 PACKAGE BODY GL_AUTHORIZATION_LIMITS_PKG as
2 /* $Header: gliemalb.pls 120.5 2005/05/05 01:07:12 kvora ship $ */
3 --
4 -- Package
5 -- GL_AUTHORIZATION_LIMITS_PKG
6 -- Purpose
7 -- To contain validation and insertion routines for GL_AUTHORIZATION_LIMITS
8 -- History
9 -- 08-07-97 R Goyal Created.
10 --
11 -- Procedure
12 -- Insert_Row
13 -- Purpose
14 -- Inserts a row into GL_AUTHORIZATION_LIMITS
15 -- History
16 -- 08-07-97 R Goyal Created.
17 -- Arguments
18 -- all the columns of the table GL_AUTHORIZATION_LIMITS
19 -- Example
20 -- GL_AUTHORIZATION_LIMITS_PKG.Insert_Row(....;
21 -- Notes
22 --
23 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
24 X_Ledger_Id NUMBER,
25 X_Employee_Id NUMBER,
26 X_Authorization_Limit NUMBER,
27 X_Last_Update_Date DATE,
28 X_Last_Updated_By NUMBER,
29 X_Creation_Date DATE,
30 X_Created_By NUMBER,
31 X_Last_Update_Login NUMBER,
32 X_Attribute1 VARCHAR2,
33 X_Attribute2 VARCHAR2,
34 X_Attribute3 VARCHAR2,
35 X_Attribute4 VARCHAR2,
36 X_Attribute5 VARCHAR2,
37 X_Attribute6 VARCHAR2,
38 X_Attribute7 VARCHAR2,
39 X_Attribute8 VARCHAR2,
40 X_Attribute9 VARCHAR2,
41 X_Attribute10 VARCHAR2,
42 X_Attribute11 VARCHAR2,
43 X_Attribute12 VARCHAR2,
44 X_Attribute13 VARCHAR2,
45 X_Attribute14 VARCHAR2,
46 X_Attribute15 VARCHAR2,
47 X_Context VARCHAR2
48 ) IS
49 CURSOR C IS SELECT rowid FROM GL_AUTHORIZATION_LIMITS
50 WHERE ledger_id = X_Ledger_Id
51 AND employee_id = X_Employee_Id;
52 BEGIN
53 INSERT INTO GL_AUTHORIZATION_LIMITS(
54 ledger_id,
55 employee_id,
56 authorization_limit,
57 last_update_date,
58 last_updated_by,
59 creation_date,
60 created_by,
61 last_update_login,
62 attribute1,
63 attribute2,
64 attribute3,
65 attribute4,
66 attribute5,
67 attribute6,
68 attribute7,
69 attribute8,
70 attribute9,
71 attribute10,
72 attribute11,
73 attribute12,
74 attribute13,
75 attribute14,
76 attribute15,
77 context
78 ) VALUES (
79 X_Ledger_Id,
80 X_Employee_Id,
81 X_Authorization_Limit,
82 X_Last_Update_Date,
83 X_Last_Updated_By,
84 X_Creation_Date,
85 X_Created_By,
86 X_Last_Update_Login,
87 X_Attribute1,
88 X_Attribute2,
89 X_Attribute3,
90 X_Attribute4,
91 X_Attribute5,
92 X_Attribute6,
93 X_Attribute7,
94 X_Attribute8,
95 X_Attribute9,
96 X_Attribute10,
97 X_Attribute11,
98 X_Attribute12,
99 X_Attribute13,
100 X_Attribute14,
101 X_Attribute15,
102 X_Context
103 );
104 OPEN C;
105 FETCH C INTO X_Rowid;
106 if (C%NOTFOUND) then
107 CLOSE C;
108 RAISE NO_DATA_FOUND;
109 end if;
110 CLOSE C;
111
112 END Insert_Row;
113
114 --
115 -- Procedure
116 -- Lock_Row
117 -- Purpose
118 -- Locks a row into GL_AUTHORIZATION_LIMITS
119 -- History
120 -- 08-07-97 R Goyal Created.
121 -- Arguments
122 -- all the columns of the table GL_AUTHORIZATION_LIMITS
123 -- Example
124 -- GL_AUTHORIZATION_LIMITS_PKG.Lock_Row(....;
125 -- Notes
126 --
127 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
128 X_Ledger_Id NUMBER,
129 X_Employee_Id NUMBER,
130 X_Authorization_Limit NUMBER,
131 X_Attribute1 VARCHAR2,
132 X_Attribute2 VARCHAR2,
133 X_Attribute3 VARCHAR2,
134 X_Attribute4 VARCHAR2,
135 X_Attribute5 VARCHAR2,
136 X_Attribute6 VARCHAR2,
137 X_Attribute7 VARCHAR2,
138 X_Attribute8 VARCHAR2,
139 X_Attribute9 VARCHAR2,
140 X_Attribute10 VARCHAR2,
141 X_Attribute11 VARCHAR2,
142 X_Attribute12 VARCHAR2,
143 X_Attribute13 VARCHAR2,
144 X_Attribute14 VARCHAR2,
145 X_Attribute15 VARCHAR2,
146 X_Context VARCHAR2
147 ) IS
148 CURSOR C IS
149 SELECT *
150 FROM GL_AUTHORIZATION_LIMITS
151 WHERE rowid = X_Rowid
152 FOR UPDATE of Ledger_Id NOWAIT;
153 Recinfo C%ROWTYPE;
154 BEGIN
155 OPEN C;
156 FETCH C INTO Recinfo;
157 if (C%NOTFOUND) then
158 CLOSE C;
159 RAISE NO_DATA_FOUND;
160 end if;
161 CLOSE C;
162 if (
163 ( (Recinfo.ledger_id = X_Ledger_Id)
164 OR ( (Recinfo.ledger_id IS NULL)
165 AND (X_Ledger_Id IS NULL)))
166 AND ( (Recinfo.employee_id = X_Employee_Id)
167 OR ( (Recinfo.employee_id IS NULL)
168 AND (X_Employee_Id IS NULL)))
169 AND ( (Recinfo.authorization_limit = X_Authorization_Limit)
170 OR ( (Recinfo.authorization_limit IS NULL)
171 AND (X_Authorization_Limit IS NULL)))
172 AND ( (Recinfo.attribute1 = X_Attribute1)
173 OR ( (Recinfo.attribute1 IS NULL)
174 AND (X_Attribute1 IS NULL)))
175 AND ( (Recinfo.attribute2 = X_Attribute2)
176 OR ( (Recinfo.attribute2 IS NULL)
177 AND (X_Attribute2 IS NULL)))
178 AND ( (Recinfo.attribute3 = X_Attribute3)
179 OR ( (Recinfo.attribute3 IS NULL)
180 AND (X_Attribute3 IS NULL)))
181 AND ( (Recinfo.attribute4 = X_Attribute4)
182 OR ( (Recinfo.attribute4 IS NULL)
183 AND (X_Attribute4 IS NULL)))
184 AND ( (Recinfo.attribute5 = X_Attribute5)
185 OR ( (Recinfo.attribute5 IS NULL)
186 AND (X_Attribute5 IS NULL)))
187 AND ( (Recinfo.attribute6 = X_Attribute6)
188 OR ( (Recinfo.attribute6 IS NULL)
189 AND (X_Attribute6 IS NULL)))
190 AND ( (Recinfo.attribute7 = X_Attribute7)
191 OR ( (Recinfo.attribute7 IS NULL)
192 AND (X_Attribute7 IS NULL)))
193 AND ( (Recinfo.attribute8 = X_Attribute8)
194 OR ( (Recinfo.attribute8 IS NULL)
195 AND (X_Attribute8 IS NULL)))
196 AND ( (Recinfo.attribute9 = X_Attribute9)
197 OR ( (Recinfo.attribute9 IS NULL)
198 AND (X_Attribute9 IS NULL)))
199 AND ( (Recinfo.attribute10 = X_Attribute10)
200 OR ( (Recinfo.attribute10 IS NULL)
201 AND (X_Attribute10 IS NULL)))
202 AND ( (Recinfo.attribute11 = X_Attribute11)
203 OR ( (Recinfo.attribute11 IS NULL)
204 AND (X_Attribute11 IS NULL)))
205 AND ( (Recinfo.attribute12 = X_Attribute12)
206 OR ( (Recinfo.attribute12 IS NULL)
207 AND (X_Attribute12 IS NULL)))
208 AND ( (Recinfo.attribute13 = X_Attribute13)
209 OR ( (Recinfo.attribute13 IS NULL)
210 AND (X_Attribute13 IS NULL)))
211 AND ( (Recinfo.attribute14 = X_Attribute14)
212 OR ( (Recinfo.attribute14 IS NULL)
213 AND (X_Attribute14 IS NULL)))
214 AND ( (Recinfo.attribute15 = X_Attribute15)
215 OR ( (Recinfo.attribute15 IS NULL)
216 AND (X_Attribute15 IS NULL)))
217 AND ( (Recinfo.context = X_Context)
218 OR ( (Recinfo.context IS NULL)
219 AND (X_Context IS NULL)))
220 ) then
221 return;
222 else
223 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
224 APP_EXCEPTION.RAISE_EXCEPTION;
225 end if;
226 END Lock_Row;
227
228
229 --
230 -- Procedure
231 -- Update_Row
232 -- Purpose
233 -- Updates a row into GL_AUTHORIZATION_LIMITS
234 -- History
235 -- 08-07-97 R Goyal Created.
236 -- Arguments
237 -- all the columns of the table GL_AUTHORIZATION_LIMITS
238 -- Example
239 -- GL_AUTHORIZATION_LIMITS_PKG.Update_Row(....;
240 -- Notes
241 --
242 PROCEDURE Update_Row(X_Rowid VARCHAR2,
243 X_Ledger_Id NUMBER,
244 X_Employee_Id NUMBER,
245 X_Authorization_Limit NUMBER,
246 X_Last_Update_Date DATE,
247 X_Last_Updated_By NUMBER,
248 X_Last_Update_Login NUMBER,
249 X_Attribute1 VARCHAR2,
250 X_Attribute2 VARCHAR2,
251 X_Attribute3 VARCHAR2,
252 X_Attribute4 VARCHAR2,
253 X_Attribute5 VARCHAR2,
254 X_Attribute6 VARCHAR2,
255 X_Attribute7 VARCHAR2,
256 X_Attribute8 VARCHAR2,
257 X_Attribute9 VARCHAR2,
258 X_Attribute10 VARCHAR2,
259 X_Attribute11 VARCHAR2,
260 X_Attribute12 VARCHAR2,
261 X_Attribute13 VARCHAR2,
262 X_Attribute14 VARCHAR2,
263 X_Attribute15 VARCHAR2,
264 X_Context VARCHAR2
265 ) IS
266 BEGIN
267 UPDATE GL_AUTHORIZATION_LIMITS
268 SET
269 ledger_id = X_Ledger_Id,
270 employee_id = X_Employee_Id,
271 authorization_limit = X_Authorization_Limit,
272 last_update_date = X_Last_Update_Date,
273 last_updated_by = X_Last_Updated_By,
274 last_update_login = X_Last_Update_Login,
275 attribute1 = X_Attribute1,
276 attribute2 = X_Attribute2,
277 attribute3 = X_Attribute3,
278 attribute4 = X_Attribute4,
279 attribute5 = X_Attribute5,
280 attribute6 = X_Attribute6,
281 attribute7 = X_Attribute7,
282 attribute8 = X_Attribute8,
283 attribute9 = X_Attribute9,
284 attribute10 = X_Attribute10,
285 attribute11 = X_Attribute11,
286 attribute12 = X_Attribute12,
287 attribute13 = X_Attribute13,
288 attribute14 = X_Attribute14,
289 attribute15 = X_Attribute15,
290 context = X_Context
291 WHERE rowid = X_rowid;
292
293 if (SQL%NOTFOUND) then
294 RAISE NO_DATA_FOUND;
295 end if;
296
297 END Update_Row;
298
299 --
300 -- Procedure
301 -- Delete_Row
302 -- Purpose
303 -- Deletes a row from GL_AUTHORIZATION_LIMITS
304 -- History
305 -- 08-07-97 R Goyal Created.
306 -- Arguments
307 -- x_rowid Rowid of a row
308 -- Example
309 -- GL_AUTHORIZATION_LIMITS_PKG.delete_row('ajfdshj');
310 -- Notes
311 --
312 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
313 BEGIN
314 DELETE FROM GL_AUTHORIZATION_LIMITS
315 WHERE rowid = X_Rowid;
316
317 IF (SQL%NOTFOUND) THEN
318 RAISE NO_DATA_FOUND;
319 END IF;
320
321 END Delete_Row;
322
323 --
324 -- Procedure
325 -- check_unique
326 -- Purpose
327 -- Checks to make sure that the employee_name is unique
328 -- History
329 -- 08-08-97 R Goyal Created.
330 -- Arguments
331 -- row_id The row ID
332 -- X_Ledger_Id Ledger Id
333 -- x_employee_id Employee ID
334 -- Example
335 -- GL_AUTHORIZATION_LIMITS_PKG.check_unique(...
336 -- Notes
337 --
338 FUNCTION Check_Unique(X_Rowid VARCHAR2,
339 X_Ledger_Id NUMBER,
340 X_employee_id NUMBER,
341 X_employee_name VARCHAR2) RETURN BOOLEAN IS
342 dummy NUMBER := 0;
343 BEGIN
344 IF ( x_employee_id IS NOT NULL
345 AND X_Ledger_Id IS NOT NULL ) THEN
346 SELECT 1
347 INTO dummy
348 FROM dual
349 WHERE EXISTS
350 (SELECT 1
351 FROM GL_AUTHORIZATION_LIMITS A
352 WHERE A.ledger_id = X_Ledger_Id
353 AND A.employee_id = X_Employee_Id
354 AND ( A.rowid <> X_Rowid or X_Rowid is null));
355 ELSIF (x_employee_name IS NOT NULL) THEN
356 SELECT 1
357 INTO dummy
358 FROM dual
359 WHERE EXISTS
360 (SELECT 1
361 FROM GL_AUTHORIZATION_LIMITS_V A
362 WHERE A.ledger_id = X_Ledger_Id
363 AND A.employee_name = X_employee_name
364 AND ( A.rowid <> X_Rowid or X_Rowid is null));
365
366 END IF;
367
368 IF (dummy = 1) THEN
369 return (FALSE);
370 ELSE
371 return (TRUE);
372 END IF;
373 EXCEPTION
374 WHEN NO_DATA_FOUND THEN
375 return(TRUE);
376
377 END Check_Unique;
378
379 END GL_AUTHORIZATION_LIMITS_PKG;