1 PACKAGE BODY gl_defas_resp_assign_pkg AS
2 /* $Header: glistrab.pls 120.5 2005/09/02 10:35:14 adesu ship $ */
3
4 PROCEDURE Insert_Row(
5 X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_Definition_Access_Set_Id IN NUMBER,
7 X_Security_Group_Id IN NUMBER,
8 X_Responsibility_Id IN NUMBER,
9 X_Application_Id IN NUMBER,
10 X_Last_Update_Date IN DATE,
11 X_Last_Updated_By IN NUMBER,
12 X_Last_Update_Login IN NUMBER,
13 X_Creation_Date IN DATE,
14 X_Created_By IN NUMBER,
15 X_Status_Code IN VARCHAR2,
16 X_Request_Id IN VARCHAR2,
17 X_Attribute1 IN VARCHAR2,
18 X_Attribute2 IN VARCHAR2,
19 X_Attribute3 IN VARCHAR2,
20 X_Attribute4 IN VARCHAR2,
21 X_Attribute5 IN VARCHAR2,
22 X_Attribute6 IN VARCHAR2,
23 X_Attribute7 IN VARCHAR2,
24 X_Attribute8 IN VARCHAR2,
25 X_Attribute9 IN VARCHAR2,
26 X_Attribute10 IN VARCHAR2,
27 X_Attribute11 IN VARCHAR2,
28 X_Attribute12 IN VARCHAR2,
29 X_Attribute13 IN VARCHAR2,
30 X_Attribute14 IN VARCHAR2,
31 X_Attribute15 IN VARCHAR2,
32 X_Context IN VARCHAR2, X_Default_Flag IN VARCHAR2,
33 X_Default_View_Flag IN VARCHAR2,
34 X_Default_Use_Flag IN VARCHAR2,
35 X_Default_Modify_Flag IN VARCHAR2
36 ) IS
37 CURSOR C IS
38 SELECT rowid
39 FROM gl_defas_resp_assign
40 WHERE definition_access_set_id = X_Definition_Access_Set_Id
41 AND application_id = X_Application_Id
42 AND responsibility_id = X_Responsibility_Id;
43
44 BEGIN
45 INSERT INTO gl_defas_resp_assign(
46 security_group_id,
47 application_id,
48 responsibility_id,
49 definition_access_set_id,
50 last_update_date,
51 last_updated_by,
52 last_update_login,
53 creation_date,
54 created_by,
55 status_code,
56 request_id,
57 attribute1,
58 attribute2,
59 attribute3,
60 attribute4,
61 attribute5,
62 attribute6,
63 attribute7,
64 attribute8,
65 attribute9,
66 attribute10,
67 attribute11,
68 attribute12,
69 attribute13,
70 attribute14,
71 attribute15,
72 context,
73 default_flag,
74 default_view_access_flag,
75 default_use_access_flag,
76 default_modify_access_flag)
77 VALUES(
78 X_Security_Group_Id,
79 X_Application_Id,
80 X_Responsibility_Id,
81 X_Definition_Access_Set_Id,
82 X_Last_Update_Date,
83 X_Last_Updated_By,
84 X_Last_Update_Login,
85 X_Creation_Date,
86 X_Created_By,
87 X_Status_Code,
88 X_Request_Id,
89 X_Attribute1,
90 X_Attribute2,
91 X_Attribute3,
92 X_Attribute4,
93 X_Attribute5,
94 X_Attribute6,
95 X_Attribute7,
96 X_Attribute8,
97 X_Attribute9,
98 X_Attribute10,
99 X_Attribute11,
100 X_Attribute12,
101 X_Attribute13,
102 X_Attribute14,
103 X_Attribute15,
104 X_Context,
105 X_Default_Flag,
106 X_Default_View_Flag,
107 X_Default_Use_Flag,
108 X_Default_Modify_Flag);
109
110 OPEN C;
111 FETCH C INTO X_Rowid;
112 if (C%NOTFOUND) then
113 CLOSE C;
114 RAISE NO_DATA_FOUND;
115 end if;
116 CLOSE C;
117
118 END Insert_Row;
119
120
121 PROCEDURE Lock_Row(
122 X_Rowid IN OUT NOCOPY VARCHAR2,
123 X_Definition_Access_Set_Id IN NUMBER,
124 X_Security_Group_Id IN NUMBER,
125 X_Responsibility_Id IN NUMBER,
126 X_Application_Id IN NUMBER,
127 X_Last_Update_Date IN DATE,
128 X_Last_Updated_By IN NUMBER,
129 X_Last_Update_Login IN NUMBER,
130 X_Creation_Date IN DATE,
131 X_Created_By IN NUMBER,
132 X_Status_Code IN VARCHAR2,
133 X_Request_Id IN VARCHAR2,
134 X_Attribute1 IN VARCHAR2,
135 X_Attribute2 IN VARCHAR2,
136 X_Attribute3 IN VARCHAR2,
137 X_Attribute4 IN VARCHAR2,
138 X_Attribute5 IN VARCHAR2,
139 X_Attribute6 IN VARCHAR2,
140 X_Attribute7 IN VARCHAR2,
141 X_Attribute8 IN VARCHAR2,
142 X_Attribute9 IN VARCHAR2,
143 X_Attribute10 IN VARCHAR2,
144 X_Attribute11 IN VARCHAR2,
145 X_Attribute12 IN VARCHAR2,
146 X_Attribute13 IN VARCHAR2,
147 X_Attribute14 IN VARCHAR2,
148 X_Attribute15 IN VARCHAR2,
149 X_Context IN VARCHAR2,
150 X_Default_Flag IN VARCHAR2,
151 X_Default_View_Flag IN VARCHAR2,
152 X_Default_Use_Flag IN VARCHAR2,
153 X_Default_Modify_Flag IN VARCHAR2
154 ) IS
155 CURSOR C IS
156 SELECT *
157 FROM gl_defas_resp_assign
158 WHERE rowid = X_Rowid
159 FOR UPDATE of Definition_Access_Set_Id NOWAIT;
160 Recinfo C%ROWTYPE;
161 l_request_id NUMBER(15);
162 l_call_status BOOLEAN;
163 l_rphase VARCHAR2(80);
164 l_rstatus VARCHAR2(80);
165 l_dphase VARCHAR2(30);
166 l_dstatus VARCHAR2(30);
167 l_message VARCHAR2(240);
168
169 BEGIN
170 IF(X_Request_Id IS NOT NULL) THEN
171 l_request_id := X_Request_Id;
172 l_call_status :=
173 FND_CONCURRENT.GET_REQUEST_STATUS(request_id => l_request_id,
174 appl_shortname => 'SQLGL',
175 program => 'GL',
176 phase => l_rphase,
177 status => l_rstatus,
178 dev_phase => l_dphase,
179 dev_status => l_dstatus,
180 message => l_message);
181
182 IF (l_dphase = 'RUNNING') THEN
183 FND_MESSAGE.Set_Name('GL', 'GL_LEDGER_RECORD_PROC_BY_FLAT');
184 APP_EXCEPTION.Raise_Exception;
185 END IF;
186 END IF;
187
188 OPEN C;
189 FETCH C INTO Recinfo;
190 if (C%NOTFOUND) then
191 CLOSE C;
192 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
193 APP_EXCEPTION.RAISE_EXCEPTION;
194 end if;
195 CLOSE C;
196
197 if (
198 ( (Recinfo.definition_access_set_id = X_Definition_Access_Set_Id)
199 OR ( (Recinfo.definition_access_set_id IS NULL)
200 AND (X_Definition_Access_Set_Id IS NULL)))
201 AND ( (Recinfo.security_group_id = X_Security_Group_Id)
202 OR ( (Recinfo.security_group_id IS NULL)
203 AND (X_Security_Group_Id IS NULL)))
204 AND ( (Recinfo.application_id = X_Application_Id)
205 OR ( (Recinfo.application_id IS NULL)
206 AND (X_Application_Id IS NULL)))
207 AND ( (Recinfo.responsibility_id = X_Responsibility_Id)
208 OR ( (Recinfo.responsibility_Id IS NULL)
209 AND (X_Responsibility_Id IS NULL)))
210 AND ( (Recinfo.last_update_date = X_Last_Update_Date)
211 OR ( (Recinfo.last_update_date IS NULL)
212 AND (X_Last_Update_Date IS NULL)))
213 AND ( (Recinfo.last_updated_by = X_Last_Updated_By)
214 OR ( (Recinfo.last_updated_by IS NULL)
215 AND (X_Last_Updated_By IS NULL)))
216 AND ( (Recinfo.last_update_login = X_Last_Update_Login)
217 OR ( (Recinfo.last_update_login IS NULL)
218 AND (X_Last_Update_Login IS NULL)))
219 AND ( (Recinfo.creation_date = X_Creation_Date)
220 OR ( (Recinfo.creation_date IS NULL)
221 AND (X_Creation_Date IS NULL)))
222 AND ( (Recinfo.created_by = X_Created_By)
223 OR ( (Recinfo.created_by IS NULL)
224 AND (X_Created_By IS NULL)))
225 AND ( (Recinfo.status_code = X_Status_Code)
226 OR ( (Recinfo.status_code IS NULL)
227 AND (X_Status_Code IS NULL)))
228 AND ( (Recinfo.request_id = X_Request_Id)
229 OR ( (Recinfo.request_id IS NULL)
230 AND (X_Request_Id IS NULL)))
231 AND ( (Recinfo.attribute1 = X_Attribute1)
232 OR ( (Recinfo.attribute1 IS NULL)
233 AND (X_Attribute1 IS NULL)))
234 AND ( (Recinfo.attribute2 = X_Attribute2)
235 OR ( (Recinfo.attribute2 IS NULL)
236 AND (X_Attribute2 IS NULL)))
237 AND ( (Recinfo.attribute3 = X_Attribute3)
238 OR ( (Recinfo.attribute3 IS NULL)
239 AND (X_Attribute3 IS NULL)))
240 AND ( (Recinfo.attribute4 = X_Attribute4)
241 OR ( (Recinfo.attribute4 IS NULL)
242 AND (X_Attribute4 IS NULL)))
243 AND ( (Recinfo.attribute5 = X_Attribute5)
244 OR ( (Recinfo.attribute5 IS NULL)
245 AND (X_Attribute5 IS NULL)))
246 AND ( (Recinfo.attribute6 = X_Attribute6)
247 OR ( (Recinfo.attribute6 IS NULL)
248 AND (X_Attribute6 IS NULL)))
249 AND ( (Recinfo.attribute7 = X_Attribute7)
250 OR ( (Recinfo.attribute7 IS NULL)
251 AND (X_Attribute7 IS NULL)))
252 AND ( (Recinfo.attribute8 = X_Attribute8)
253 OR ( (Recinfo.attribute8 IS NULL)
254 AND (X_Attribute8 IS NULL)))
255 AND ( (Recinfo.attribute9 = X_Attribute9)
256 OR ( (Recinfo.attribute9 IS NULL)
257 AND (X_Attribute9 IS NULL)))
258 AND ( (Recinfo.attribute10 = X_Attribute10)
259 OR ( (Recinfo.attribute10 IS NULL)
260 AND (X_Attribute10 IS NULL)))
261 AND ( (Recinfo.attribute11 = X_Attribute11)
262 OR ( (Recinfo.attribute11 IS NULL)
263 AND (X_Attribute11 IS NULL)))
264 AND ( (Recinfo.attribute12 = X_Attribute12)
265 OR ( (Recinfo.attribute12 IS NULL)
266 AND (X_Attribute12 IS NULL)))
267 AND ( (Recinfo.attribute13 = X_Attribute13)
268 OR ( (Recinfo.attribute13 IS NULL)
269 AND (X_Attribute13 IS NULL)))
270 AND ( (Recinfo.attribute14 =X_Attribute14)
271 OR ( (Recinfo.attribute14 IS NULL)
272 AND (X_Attribute14 IS NULL)))
273 AND ( (Recinfo.attribute15 = X_Attribute15)
274 OR ( (Recinfo.attribute15 IS NULL)
275 AND (X_Attribute15 IS NULL)))
276 AND ( (Recinfo.context = X_Context)
277 OR ( (Recinfo.context IS NULL)
278 AND (X_Context IS NULL)))
279 AND ( (Recinfo.default_flag = X_Default_Flag)
280 OR ( (Recinfo.default_flag IS NULL)
281 AND (X_Default_Flag IS NULL)))
282 AND ( (Recinfo.default_view_access_flag = X_Default_View_Flag)
283 OR ( (Recinfo.default_view_access_flag IS NULL)
284 AND (X_Default_View_Flag IS NULL)))
285 AND ( (Recinfo.default_use_access_flag = X_Default_Use_Flag)
286 OR ( (Recinfo.default_use_access_flag IS NULL)
287 AND (X_Default_Use_Flag IS NULL)))
288 AND ( (Recinfo.default_modify_access_flag = X_Default_Modify_Flag)
289 OR ( (Recinfo.default_modify_access_flag IS NULL)
290 AND (X_Default_Modify_Flag IS NULL)))
291 ) then
292 return;
293 else
294 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
295 APP_EXCEPTION.RAISE_EXCEPTION;
296 end if;
297
298 END Lock_Row;
299
300 PROCEDURE Update_Row(
301 X_Rowid IN OUT NOCOPY VARCHAR2,
302 X_Last_Update_Date IN DATE,
303 X_Last_Updated_By IN NUMBER,
304 X_Last_Update_Login IN NUMBER,
305 X_Status_Code IN VARCHAR2,
306 X_Request_Id IN VARCHAR2,
307 X_Attribute1 IN VARCHAR2,
308 X_Attribute2 IN VARCHAR2,
309 X_Attribute3 IN VARCHAR2,
310 X_Attribute4 IN VARCHAR2,
311 X_Attribute5 IN VARCHAR2,
312 X_Attribute6 IN VARCHAR2,
313 X_Attribute7 IN VARCHAR2,
314 X_Attribute8 IN VARCHAR2,
315 X_Attribute9 IN VARCHAR2,
316 X_Attribute10 IN VARCHAR2,
317 X_Attribute11 IN VARCHAR2,
318 X_Attribute12 IN VARCHAR2,
319 X_Attribute13 IN VARCHAR2,
320 X_Attribute14 IN VARCHAR2,
321 X_Attribute15 IN VARCHAR2,
322 X_Context IN VARCHAR2,
323 X_Default_Flag IN VARCHAR2,
324 X_Default_View_Flag IN VARCHAR2,
325 X_Default_Use_Flag IN VARCHAR2,
326 X_Default_Modify_Flag IN VARCHAR2
327 ) IS
328 BEGIN
329 UPDATE gl_defas_resp_assign
330 SET last_update_date = X_Last_Update_Date,
331 last_updated_by = X_Last_Updated_By,
332 last_update_login = X_Last_Update_Login,
333 status_code = X_Status_Code,
334 request_id = X_Request_Id,
335 attribute1 = X_Attribute1,
336 attribute2 = X_Attribute2,
337 attribute3 = X_Attribute3,
338 attribute4 = X_Attribute4,
339 attribute5 = X_Attribute5,
340 attribute6 = X_Attribute6,
341 attribute7 = X_Attribute7,
342 attribute8 = X_Attribute8,
343 attribute9 = X_Attribute9,
344 attribute10 = X_Attribute10,
345 attribute11 = X_Attribute11,
346 attribute12 = X_Attribute12,
347 attribute13 = X_Attribute13,
348 attribute14 = X_Attribute14,
349 attribute15 = X_Attribute15,
350 context = X_Context,
351 default_flag = X_Default_Flag,
352 default_view_access_flag = X_Default_View_Flag,
353 default_use_access_flag = X_Default_Use_Flag,
354 default_modify_access_flag = X_Default_Modify_Flag
355 WHERE rowid = X_Rowid;
356
357 if (SQL%NOTFOUND) then
358 Raise NO_DATA_FOUND;
359 end if;
360
361 END Update_Row;
362
363
364 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
365 BEGIN
366 UPDATE GL_DEFAS_RESP_ASSIGN
367 SET status_code = 'D'
368 WHERE rowid = X_Rowid;
369
370 if SQL%NOTFOUND then
371 RAISE NO_DATA_FOUND;
372 end if;
373
374 END Delete_Row;
375
376
377 PROCEDURE check_unique_set(X_Definition_Access_Set_Id NUMBER,
378 X_Application_Id NUMBER,
379 X_Responsibility_Id NUMBER,
380 X_Security_Group_Id NUMBER)IS
381
382 CURSOR c_dup IS
383 SELECT 'Duplicate'
384 FROM GL_DEFAS_RESP_ASSIGN r
385 WHERE r.application_id = X_Application_Id
386 AND r.responsibility_Id = X_Responsibility_Id
387 AND r.security_group_id = X_Security_Group_Id
388 AND r.definition_access_set_id = X_Definition_Access_Set_Id
389 AND (r.status_code <>'D' or r.status_code is null);
390
391 dummy VARCHAR2(100);
392
393 BEGIN
394 OPEN c_dup;
395 FETCH c_dup INTO dummy;
396
397 IF c_dup%FOUND THEN
398 CLOSE c_dup;
399 fnd_message.set_name( 'SQLGL', 'GL_DEFAS_ASSIGN_RESP_DUP' );
400 app_exception.raise_exception;
401 END IF;
402
403 CLOSE c_dup;
404
405 EXCEPTION
406 WHEN app_exceptions.application_exception THEN
407 RAISE;
408 WHEN OTHERS THEN
409 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
410 fnd_message.set_token('PROCEDURE',
411 'GL_DEFAS_RESP_ASSIGN_PKG.check_unique_set');
412 RAISE;
413
414 END check_unique_set;
415
416
417 END gl_defas_resp_assign_pkg;
[Home] [Help]