[Home] [Help]
PACKAGE BODY: APPS.GL_DEFAS_ACCESS_SETS_PKG
Source
1 PACKAGE BODY gl_defas_access_sets_pkg AS
2 /* $Header: glistdab.pls 120.5 2006/03/13 19:56:21 cma ship $ */
3
4 --
5 -- PUBLIC FUNCTIONS
6 --
7
8 FUNCTION get_unique_id RETURN NUMBER IS
9 CURSOR get_new_id IS
10 SELECT GL_DEFAS_ACCESS_SETS_S.NEXTVAL
11 FROM dual;
12 new_id NUMBER;
13 BEGIN
14 OPEN get_new_id;
15 FETCH get_new_id INTO new_id;
16
17 IF get_new_id%FOUND THEN
18 CLOSE get_new_id;
19 RETURN (new_id);
20 ELSE
21 CLOSE get_new_id;
22 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
23 fnd_message.set_token('SEQUENCE', 'GL_DEFAS_ACCESS_SETS_S');
24 app_exception.raise_exception;
25 END IF;
26
27 EXCEPTION
28 WHEN app_exceptions.application_exception THEN
29 RAISE;
30 WHEN OTHERS THEN
31 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
32 fnd_message.set_token('PROCEDURE', 'gl_defas_access_sets_pkg.get_unique_id');
33 RAISE;
34 END get_unique_id;
35
36
37 FUNCTION get_dbname_id RETURN NUMBER IS
38 CURSOR get_new_id IS
39 SELECT GL.GL_DEFAS_DBNAME_S.NEXTVAL
40 FROM dual;
41 new_id NUMBER;
42 BEGIN
43 OPEN get_new_id;
44 FETCH get_new_id INTO new_id;
45
46 IF get_new_id%FOUND THEN
47 CLOSE get_new_id;
48 RETURN (new_id);
49 ELSE
50 CLOSE get_new_id;
51 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
52 fnd_message.set_token('SEQUENCE', 'GL_DEFAS_DBNAME_S');
53 app_exception.raise_exception;
54 END IF;
55
56 EXCEPTION
57 WHEN app_exceptions.application_exception THEN
58 RAISE;
59 WHEN OTHERS THEN
60 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
61 fnd_message.set_token('PROCEDURE', 'gl_defas_access_sets_pkg.get_dbname_id');
62 RAISE;
63 END get_dbname_id;
64
65 --
66 -- PUBLIC FUNCTIONS
67 --
68 PROCEDURE check_unique_name(x_name IN VARCHAR2 ) IS
69
70 CURSOR c_dup IS
71 SELECT 'Duplicate'
72 FROM GL_DEFAS_ACCESS_SETS a
73 WHERE a.user_definition_access_set = x_name;
74
75 dummy VARCHAR2(100);
76
77 BEGIN
78 OPEN c_dup;
79 FETCH c_dup INTO dummy;
80
81 IF c_dup%FOUND THEN
82 CLOSE c_dup;
83 fnd_message.set_name( 'SQLGL', 'GL_DEFAS_DUPLICATE' );
84 app_exception.raise_exception;
85 END IF;
86
87 CLOSE c_dup;
88
89 EXCEPTION
90 WHEN app_exceptions.application_exception THEN
91 RAISE;
92 WHEN OTHERS THEN
93 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
94 fnd_message.set_token('PROCEDURE',
95 'GL_DEFAS_ACCESS_SETS_PKG.check_unique_name');
96 RAISE;
97 END check_unique_name;
98
99 PROCEDURE check_assign (X_Definition_Access_Set_Id IN NUMBER,
100 X_Assign_Flag IN OUT NOCOPY VARCHAR2) IS
101 CURSOR assign IS
102 SELECT 'Y'
103 FROM DUAL
104 WHERE exists (
105 SELECT *
106 FROM gl_defas_resp_assign
107 WHERE definition_access_set_id = X_Definition_Access_Set_Id);
108
109 BEGIN
110 OPEN assign;
111 FETCH assign INTO X_Assign_Flag;
112 CLOSE assign;
113
114 EXCEPTION
115 WHEN app_exceptions.application_exception THEN
116 RAISE;
117 WHEN OTHERS THEN
118 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
119 fnd_message.set_token('PROCEDURE',
120 'GL_DEFAS_ACCESS_SETS_PKG.check_assign');
121 RAISE;
122
123 END check_assign;
124
125 PROCEDURE translate_row(X_Definition_Access_Set VARCHAR2,
126 X_User_Definition_Access_Set VARCHAR2,
127 X_Description VARCHAR2,
128 X_Owner VARCHAR2,
129 X_Force_Edits VARCHAR2) IS
130 user_id number := 0;
131 BEGIN
132 if (X_Owner = 'SEED') then
133 user_id := 1;
134 end if;
135
136 /* Update only if force_edits is 'Y' or if user_id = 1 */
137 if ( user_id = 1 or X_Force_Edits = 'Y' ) then
138 UPDATE GL_DEFAS_ACCESS_SETS
139 SET
140 user_definition_access_set = X_User_Definition_Access_Set,
141 description = X_Description,
142 last_update_date = sysdate,
143 last_updated_by = user_id,
144 last_update_login = 0
145 WHERE definition_access_set = X_Definition_Access_Set
146 and userenv('LANG') IN
147 ( select language_code
148 from fnd_languages
149 where installed_flag = 'B' );
150 end if;
151
152 if (sql%notfound) then
153 null;
154 end if;
155
156 END translate_row;
157
158 PROCEDURE load_row(X_Definition_Access_Set VARCHAR2,
159 X_User_Definition_Access_Set VARCHAR2,
160 X_Description VARCHAR2,
161 X_Attribute1 VARCHAR2,
162 X_Attribute2 VARCHAR2,
163 X_Attribute3 VARCHAR2,
164 X_Attribute4 VARCHAR2,
165 X_Attribute5 VARCHAR2,
166 X_Attribute6 VARCHAR2,
167 X_Attribute7 VARCHAR2,
168 X_Attribute8 VARCHAR2,
169 X_Attribute9 VARCHAR2,
170 X_Attribute10 VARCHAR2,
171 X_Attribute11 VARCHAR2,
172 X_Attribute12 VARCHAR2,
173 X_Attribute13 VARCHAR2,
174 X_Attribute14 VARCHAR2,
175 X_Attribute15 VARCHAR2,
176 X_Context VARCHAR2,
177 X_Owner VARCHAR2,
178 X_Force_Edits VARCHAR2) IS
179 user_id number := 0;
180 v_creation_date date;
181 v_rowid rowid := null;
182 BEGIN
183
184 -- validate input parameters
185 if ( X_User_Definition_Access_Set IS NULL) then
186 fnd_message.set_name('SQLGL', 'GL_LOAD_ROW_NO_DATA');
187 app_exception.raise_exception;
188 end if;
189
190 if (X_OWNER = 'SEED') then
191 user_id := 1;
192 end if;
193
194 /* Check if the row exists in the database. If it does, retrieves
195 the creation date for update_row. */
196 select creation_date
197 into v_creation_date
198 from gl_defas_access_sets
199 where definition_access_set = X_Definition_Access_Set;
200
201 if (SQL%NOTFOUND) then
202 RAISE NO_DATA_FOUND;
203 end if;
204
205 /* Update only if force_edits is 'Y' or if user_id = 1 */
206 if ( user_id = 1 or X_Force_Edits = 'Y' ) then
207 UPDATE GL_DEFAS_ACCESS_SETS
208 SET
209 user_definition_access_set = X_Definition_Access_Set,
210 last_update_date = sysdate,
211 last_updated_by = user_id,
212 last_update_login = 0,
213 attribute1 = X_Attribute1,
214 attribute2 = X_Attribute2,
215 attribute3 = X_Attribute3,
216 attribute4 = X_Attribute4,
217 attribute5 = X_Attribute5,
218 attribute6 = X_Attribute6,
219 attribute7 = X_Attribute7,
220 attribute8 = X_Attribute8,
221 attribute9 = X_Attribute9,
222 attribute10 = X_Attribute10,
223 attribute11 = X_Attribute11,
224 attribute12 = X_Attribute12,
225 attribute13 = X_Attribute13,
226 attribute14 = X_Attribute14,
227 attribute15 = X_Attribute15,
228 context = X_Context,
229 description = X_Description
230 WHERE definition_access_set = X_Definition_Access_Set;
231
232 if (SQL%NOTFOUND) then
233 RAISE NO_DATA_FOUND;
234 end if;
235 end if;
236 EXCEPTION
237 when NO_DATA_FOUND then
238 INSERT INTO GL_DEFAS_ACCESS_SETS
239 (definition_access_set_id,
240 definition_access_set,
241 user_definition_access_set,
242 description,
243 last_update_date,
244 last_updated_by,
245 last_update_login,
246 creation_date,
247 created_by,
248 context,
249 attribute1,
250 attribute2,
251 attribute3,
252 attribute4,
253 attribute5,
254 attribute6,
255 attribute7,
256 attribute8,
257 attribute9,
258 attribute10,
259 attribute11,
260 attribute12,
261 attribute13,
262 attribute14,
263 attribute15)
264 VALUES
265 (gl_defas_access_sets_pkg.get_unique_id,
266 X_Definition_Access_Set,
267 X_User_Definition_Access_Set,
268 X_Description,
269 sysdate,
270 user_id,
271 0,
272 sysdate,
273 user_id,
274 X_Context,
275 X_Attribute1,
276 X_Attribute2,
277 X_Attribute3,
278 X_Attribute4,
279 X_Attribute5,
280 X_Attribute6,
281 X_Attribute7,
282 X_Attribute8,
283 X_Attribute9,
284 X_Attribute10,
285 X_Attribute11,
286 X_Attribute12,
287 X_Attribute13,
288 X_Attribute14,
289 X_Attribute15);
290
291 END load_row;
292
293
294 END gl_defas_access_sets_pkg;