1 PACKAGE BODY GL_LEDGER_SET_NORM_ASSIGN_PKG AS
2 /* $Header: glistlab.pls 120.7 2005/05/05 01:23:24 kvora ship $ */
3
4
5 --
6 -- PUBLIC FUNCTIONS
7 --
8
9 PROCEDURE check_unique(X_Rowid VARCHAR2,
10 X_Ledger_Set_Id NUMBER,
11 X_Ledger_Id NUMBER) IS
12 CURSOR c_dup IS
13 SELECT 'Duplicate'
14 FROM gl_ledger_set_norm_assign la
15 WHERE la.ledger_set_id = X_ledger_set_id
16 AND la.ledger_id = X_ledger_id
17 AND nvl(la.status_code, 'X') <> 'D'
18 AND ( X_rowid is NULL
19 OR
20 la.rowid <> X_rowid );
21 dummy VARCHAR2(100);
22
23 BEGIN
24 OPEN c_dup;
25 FETCH c_dup INTO dummy;
26
27 IF c_dup%FOUND THEN
28 CLOSE c_dup;
29 fnd_message.set_name( 'SQLGL', 'GL_DUPLICATE_LEDGER_ASSIGN' );
30 app_exception.raise_exception;
31 END IF;
32
33 CLOSE c_dup;
34
35 EXCEPTION
36 WHEN app_exceptions.application_exception THEN
37 RAISE;
38 WHEN OTHERS THEN
39 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
40 fnd_message.set_token('PROCEDURE',
41 'GL_LEDGER_SET_NORM_ASSIGN_PKG.check_unique');
42 RAISE;
43
44 END check_unique;
45
46 -- **********************************************************************
47
48 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
49 X_Ledger_Set_Id NUMBER,
50 X_Ledger_Id NUMBER,
51 X_Object_Type_Code VARCHAR2,
52 X_Last_Update_Date DATE,
53 X_Last_Updated_By NUMBER,
54 X_Creation_Date DATE,
55 X_Created_By NUMBER,
56 X_Last_Update_Login NUMBER,
57 X_Start_Date DATE,
58 X_End_Date DATE,
59 X_Context VARCHAR2,
60 X_Attribute1 VARCHAR2,
61 X_Attribute2 VARCHAR2,
62 X_Attribute3 VARCHAR2,
63 X_Attribute4 VARCHAR2,
64 X_Attribute5 VARCHAR2,
65 X_Attribute6 VARCHAR2,
66 X_Attribute7 VARCHAR2,
67 X_Attribute8 VARCHAR2,
68 X_Attribute9 VARCHAR2,
69 X_Attribute10 VARCHAR2,
70 X_Attribute11 VARCHAR2,
71 X_Attribute12 VARCHAR2,
72 X_Attribute13 VARCHAR2,
73 X_Attribute14 VARCHAR2,
74 X_Attribute15 VARCHAR2,
75 X_Request_Id NUMBER
76 ) IS
77
78 L_Ledger_Id NUMBER;
79 L_Has_Loops BOOLEAN := FALSE;
80
81 CURSOR C IS SELECT rowid FROM gl_ledger_set_norm_assign
82 WHERE ledger_set_id = X_Ledger_Set_Id
83 AND ledger_id = X_Ledger_Id;
84
85 BEGIN
86
87 -- Check for loops only if assigning a ledger set.
88 IF (X_Object_Type_Code = 'S') THEN
89 -- Check that this ledger assignment does not create a loop.
90 L_Ledger_Id := X_Ledger_Set_Id;
91
92
93 -- This code checks to see if you are creating a loop by
94 -- defining a ledger set as a child of itself. Since a ledger
95 -- set may be a child of multiple ledger sets it is not possible
96 -- to check all scenarios. The flattening program will report
97 -- errors in this case.
98 LOOP
99 BEGIN
100 SELECT ledger_set_id
101 INTO L_Ledger_Id
102 FROM gl_ledger_set_norm_assign
103 WHERE ledger_id = L_Ledger_Id
104 AND nvl(status_code, 'X') <> 'D';
105 EXCEPTION
106 WHEN NO_DATA_FOUND THEN
107 EXIT;
108 WHEN TOO_MANY_ROWS THEN
109 EXIT;
110 END;
111
112 IF (X_Ledger_Id = L_Ledger_Id) THEN
113 L_Has_Loops := TRUE;
114 EXIT;
115 END IF;
116 END LOOP;
117
118 IF (L_Has_Loops) THEN
119 FND_MESSAGE.Set_Name('SQLGL', 'GL_LSET_ASSIGNMENT_LOOP');
120 APP_EXCEPTION.Raise_Exception;
121 END IF;
122
123 END IF; -- if object_type_code = 'S'
124
125 INSERT INTO gl_ledger_set_norm_assign(
126 ledger_set_id,
127 ledger_id,
128 status_code,
129 last_update_date,
130 last_updated_by,
131 creation_date,
132 created_by,
133 last_update_login,
134 start_date,
135 end_date,
136 context,
137 attribute1,
138 attribute2,
139 attribute3,
140 attribute4,
141 attribute5,
142 attribute6,
143 attribute7,
144 attribute8,
145 attribute9,
146 attribute10,
147 attribute11,
148 attribute12,
149 attribute13,
150 attribute14,
151 attribute15,
152 request_id
153 ) VALUES (
154 X_Ledger_Set_Id,
155 X_Ledger_Id,
156 'I',
157 X_Last_Update_Date,
158 X_Last_Updated_By,
159 X_Creation_Date,
160 X_Created_By,
161 X_Last_Update_Login,
162 X_Start_Date,
163 X_End_Date,
164 X_Context,
165 X_Attribute1,
166 X_Attribute2,
167 X_Attribute3,
168 X_Attribute4,
169 X_Attribute5,
170 X_Attribute6,
171 X_Attribute7,
172 X_Attribute8,
173 X_Attribute9,
174 X_Attribute10,
175 X_Attribute11,
176 X_Attribute12,
177 X_Attribute13,
178 X_Attribute14,
179 X_Attribute15,
180 X_Request_Id
181 );
182
183 OPEN C;
184 FETCH C INTO X_Rowid;
185 if (C%NOTFOUND) then
186 CLOSE C;
187 Raise NO_DATA_FOUND;
188 end if;
189 CLOSE C;
190
191 EXCEPTION
192 WHEN app_exceptions.application_exception THEN
193 RAISE;
194 WHEN OTHERS THEN
195 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
196 fnd_message.set_token('PROCEDURE',
197 'GL_LEDGER_SET_NORM_ASSIGN_PKG.insert_row');
198 RAISE;
199
200 END Insert_Row;
201
202 -- **********************************************************************
203
204 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
205 X_Ledger_Set_Id NUMBER,
206 X_Ledger_Id NUMBER,
207 X_Start_Date DATE,
208 X_End_Date DATE,
209 X_Context VARCHAR2,
210 X_Attribute1 VARCHAR2,
211 X_Attribute2 VARCHAR2,
212 X_Attribute3 VARCHAR2,
213 X_Attribute4 VARCHAR2,
214 X_Attribute5 VARCHAR2,
215 X_Attribute6 VARCHAR2,
216 X_Attribute7 VARCHAR2,
217 X_Attribute8 VARCHAR2,
218 X_Attribute9 VARCHAR2,
219 X_Attribute10 VARCHAR2,
220 X_Attribute11 VARCHAR2,
221 X_Attribute12 VARCHAR2,
222 X_Attribute13 VARCHAR2,
223 X_Attribute14 VARCHAR2,
224 X_Attribute15 VARCHAR2,
225 X_Request_Id NUMBER
226 ) IS
227
228 CURSOR C IS
229 SELECT *
230 FROM gl_ledger_set_norm_assign
231 WHERE rowid = X_Rowid
232 FOR UPDATE of Ledger_Id NOWAIT;
233 Recinfo C%ROWTYPE;
234 l_request_id NUMBER(15);
235 l_call_status BOOLEAN;
236 l_rphase VARCHAR2(80);
237 l_rstatus VARCHAR2(80);
238 l_dphase VARCHAR2(30);
239 l_dstatus VARCHAR2(30);
240 l_message VARCHAR2(240);
241
242
243 BEGIN
244 -- Prevent a record from be modified if it is currently processed by
245 -- the Flattening program
246 IF(X_Request_Id IS NOT NULL) THEN
247 l_request_id := X_Request_Id;
248 l_call_status :=
249 FND_CONCURRENT.GET_REQUEST_STATUS(request_id => l_request_id,
250 appl_shortname => 'SQLGL',
251 program => 'GLSTFL',
252 phase => l_rphase,
253 status => l_rstatus,
254 dev_phase => l_dphase,
255 dev_status => l_dstatus,
256 message => l_message);
257
258 IF (l_dphase = 'RUNNING') THEN
259 FND_MESSAGE.Set_Name('GL', 'GL_LEDGER_RECORD_PROC_BY_FLAT');
260 APP_EXCEPTION.Raise_Exception;
261 END IF;
262 END IF;
263
264 OPEN C;
265 FETCH C INTO Recinfo;
266 if (C%NOTFOUND) then
267 CLOSE C;
268 RAISE NO_DATA_FOUND;
269 end if;
270 CLOSE C;
271 if (
272 ( (Recinfo.ledger_set_id = X_Ledger_Set_Id)
273 OR ( (Recinfo.ledger_set_id IS NULL)
274 AND (X_Ledger_Set_Id IS NULL)))
275 AND ( (Recinfo.ledger_id = X_Ledger_Id)
276 OR ( (Recinfo.ledger_id IS NULL)
277 AND (X_Ledger_Id IS NULL)))
278 AND ( (Recinfo.start_date = X_Start_Date)
279 OR ( (Recinfo.start_date IS NULL)
280 AND (X_Start_Date IS NULL)))
281 AND ( (Recinfo.end_date = X_End_Date)
282 OR ( (Recinfo.end_date IS NULL)
283 AND (X_End_Date IS NULL)))
284 AND ( (Recinfo.context = X_Context)
285 OR ( (Recinfo.context IS NULL)
286 AND (X_Context IS NULL)))
287 AND ( (Recinfo.attribute1 = X_Attribute1)
288 OR ( (Recinfo.attribute1 IS NULL)
289 AND (X_Attribute1 IS NULL)))
290 AND ( (Recinfo.attribute2 = X_Attribute2)
291 OR ( (Recinfo.attribute2 IS NULL)
292 AND (X_Attribute2 IS NULL)))
293 AND ( (Recinfo.attribute3 = X_Attribute3)
294 OR ( (Recinfo.attribute3 IS NULL)
295 AND (X_Attribute3 IS NULL)))
296 AND ( (Recinfo.attribute4 = X_Attribute4)
297 OR ( (Recinfo.attribute4 IS NULL)
298 AND (X_Attribute4 IS NULL)))
299 AND ( (Recinfo.attribute5 = X_Attribute5)
300 OR ( (Recinfo.attribute5 IS NULL)
301 AND (X_Attribute5 IS NULL)))
302 AND ( (Recinfo.attribute6 = X_Attribute6)
303 OR ( (Recinfo.attribute6 IS NULL)
304 AND (X_Attribute6 IS NULL)))
305 AND ( (Recinfo.attribute7 = X_Attribute7)
306 OR ( (Recinfo.attribute7 IS NULL)
307 AND (X_Attribute7 IS NULL)))
308 AND ( (Recinfo.attribute8 = X_Attribute8)
309 OR ( (Recinfo.attribute8 IS NULL)
310 AND (X_Attribute8 IS NULL)))
311 AND ( (Recinfo.attribute9 = X_Attribute9)
312 OR ( (Recinfo.attribute9 IS NULL)
313 AND (X_Attribute9 IS NULL)))
314 AND ( (Recinfo.attribute10 = X_Attribute10)
315 OR ( (Recinfo.attribute10 IS NULL)
316 AND (X_Attribute10 IS NULL)))
317 AND ( (Recinfo.attribute11 = X_Attribute11)
318 OR ( (Recinfo.attribute11 IS NULL)
319 AND (X_Attribute11 IS NULL)))
320 AND ( (Recinfo.attribute12 = X_Attribute12)
321 OR ( (Recinfo.attribute12 IS NULL)
322 AND (X_Attribute12 IS NULL)))
323 AND ( (Recinfo.attribute13 = X_Attribute13)
324 OR ( (Recinfo.attribute13 IS NULL)
325 AND (X_Attribute13 IS NULL)))
326 AND ( (Recinfo.attribute14 = X_Attribute14)
327 OR ( (Recinfo.attribute14 IS NULL)
328 AND (X_Attribute14 IS NULL)))
329 AND ( (Recinfo.attribute15 = X_Attribute15)
330 OR ( (Recinfo.attribute15 IS NULL)
331 AND (X_Attribute15 IS NULL)))
332 AND ( (Recinfo.request_id = X_Request_Id)
333 OR ( (Recinfo.request_id IS NULL)
334 AND (X_Request_Id IS NULL)))
335 ) then
336 return;
337 else
338 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
339 APP_EXCEPTION.Raise_Exception;
340 end if;
341 END Lock_Row;
342
343 -- **********************************************************************
344
345 /* This routine should be deleted if it is not required. The Ledger Sets
346 form does not use this routine. */
347 PROCEDURE Update_Row(X_Rowid VARCHAR2,
348 X_Ledger_Set_Id NUMBER,
349 X_Ledger_Id NUMBER,
350 X_Last_Update_Date DATE,
351 X_Last_Updated_By NUMBER,
352 X_Last_Update_Login NUMBER,
353 X_Start_Date DATE,
354 X_End_Date DATE,
355 X_Context VARCHAR2,
356 X_Attribute1 VARCHAR2,
357 X_Attribute2 VARCHAR2,
358 X_Attribute3 VARCHAR2,
359 X_Attribute4 VARCHAR2,
360 X_Attribute5 VARCHAR2,
361 X_Attribute6 VARCHAR2,
362 X_Attribute7 VARCHAR2,
363 X_Attribute8 VARCHAR2,
364 X_Attribute9 VARCHAR2,
365 X_Attribute10 VARCHAR2,
366 X_Attribute11 VARCHAR2,
367 X_Attribute12 VARCHAR2,
368 X_Attribute13 VARCHAR2,
369 X_Attribute14 VARCHAR2,
370 X_Attribute15 VARCHAR2,
371 X_Request_Id NUMBER
372 ) IS
373 BEGIN
374
375 -- If a row has a status_code of 'I', the Flattening Program has not yet
376 -- been run. In this case, the status_code should remain 'I', else
377 -- status_code should be 'U'.
378 UPDATE gl_ledger_set_norm_assign
379 SET
380 ledger_set_id = X_Ledger_Set_Id,
381 ledger_id = X_Ledger_Id,
382 status_code = decode(nvl(status_code, 'I'),
383 'I', status_code, 'U'),
384 last_update_date = X_Last_Update_Date,
385 last_updated_by = X_Last_Updated_By,
386 last_update_login = X_Last_Update_Login,
387 start_date = X_Start_Date,
388 end_date = X_End_Date,
389 context = X_Context,
390 attribute1 = X_Attribute1,
391 attribute2 = X_Attribute2,
392 attribute3 = X_Attribute3,
393 attribute4 = X_Attribute4,
394 attribute5 = X_Attribute5,
395 attribute6 = X_Attribute6,
396 attribute7 = X_Attribute7,
397 attribute8 = X_Attribute8,
398 attribute9 = X_Attribute9,
399 attribute10 = X_Attribute10,
400 attribute11 = X_Attribute11,
401 attribute12 = X_Attribute12,
402 attribute13 = X_Attribute13,
403 attribute14 = X_Attribute14,
404 attribute15 = X_Attribute15,
405 request_id = X_Request_Id
406 WHERE rowid = X_Rowid;
407
408 if (SQL%NOTFOUND) then
409 Raise NO_DATA_FOUND;
410 end if;
411 END Update_Row;
412
413 -- **********************************************************************
414
415 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
416
417 BEGIN
418
419 -- This is a norm table. We do not actually delete the row since the
420 -- Flattening program will take care of this.
421 -- Instead, set the status code to 'Delete'.
422 UPDATE GL_LEDGER_SET_NORM_ASSIGN
423 SET status_code = 'D'
424 WHERE rowid = X_Rowid;
425
426 if (SQL%NOTFOUND) then
427 Raise NO_DATA_FOUND;
428 end if;
429
430 EXCEPTION
431 WHEN NO_DATA_FOUND THEN
432 RAISE NO_DATA_FOUND;
433 WHEN OTHERS THEN
434 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
435 fnd_message.set_token('PROCEDURE',
436 'GL_LEDGER_SET_NORM_ASSIGN_PKG.delete_row');
437 RAISE;
438
439 END Delete_Row;
440
441 -- **********************************************************************
442
443 FUNCTION Check_Assignments_Exist(X_Ledger_Set_Id NUMBER)
444 RETURN BOOLEAN IS
445
446 num NUMBER;
447 CURSOR assignments IS
448 SELECT 1
449 FROM dual
450 WHERE EXISTS (SELECT 1
451 FROM GL_LEDGER_SET_NORM_ASSIGN
452 WHERE ledger_set_id = X_Ledger_Set_Id
453 AND (status_code <> 'D' OR status_code IS NULL));
454 BEGIN
455 OPEN assignments;
456 FETCH assignments INTO num;
457 IF assignments%NOTFOUND THEN
458 CLOSE assignments;
459 RETURN FALSE;
460 END IF;
461
462 CLOSE assignments;
463 RETURN TRUE;
464 END Check_Assignments_Exist;
465
466 -- **********************************************************************
467
468 PROCEDURE validate_ledger_assignment(X_Ls_Coa_Id NUMBER,
469 X_Ls_Period_Set_Name VARCHAR2,
470 X_Ls_Period_Type VARCHAR2,
471 X_Ledger_Id NUMBER) IS
472 l_ledger_coa_id NUMBER;
473 l_ledger_period_set_name VARCHAR2(30);
474 l_ledger_period_type VARCHAR2(30);
475 BEGIN
476 -- get ledger info
477 SELECT chart_of_accounts_id, period_set_name, accounted_period_type
478 INTO l_ledger_coa_id, l_ledger_period_set_name, l_ledger_period_type
479 FROM GL_LEDGERS
480 WHERE ledger_id = X_Ledger_Id;
481
482 -- check ledger info against ledger set
483 IF ( X_Ls_Coa_Id <> l_ledger_coa_id
484 OR X_Ls_Period_Set_Name <> l_ledger_period_set_name
485 OR X_Ls_Period_Type <> l_ledger_period_type) THEN
486 fnd_message.set_name('SQLGL', 'GL_API_LS_DETL_LEDGER_ERROR');
487 app_exception.raise_exception;
488 END IF;
489
490 EXCEPTION
491 WHEN app_exceptions.application_exception THEN
492 RAISE;
493 WHEN OTHERS THEN
494 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
495 fnd_message.set_token('PROCEDURE',
496 'GL_LEDGER_SET_NORM_ASSIGN_PKG.validate_ledger_assignment');
497 RAISE;
498
499 END validate_ledger_assignment;
500
501 -- **********************************************************************
502
503 END GL_LEDGER_SET_NORM_ASSIGN_PKG;
[Home] [Help]