[Home] [Help]
PACKAGE BODY: APPS.GL_LEDGER_NORM_SEG_VALS_PKG
Source
1 PACKAGE BODY GL_LEDGER_NORM_SEG_VALS_PKG AS
2 /* $Header: glistsvb.pls 120.5 2003/04/24 01:35:34 djogg noship $ */
3
4 --
5 -- PUBLIC FUNCTIONS
6 --
7
8 FUNCTION Get_Record_Id RETURN NUMBER
9 IS
10 CURSOR get_id IS
11 SELECT GL_LEDGER_NORM_SEG_VALS_REC_S.NEXTVAL
12 FROM dual;
13
14 v_record_id NUMBER(15);
15
16 BEGIN
17 OPEN get_id;
18 FETCH get_id INTO v_record_id;
19
20 IF get_id%FOUND THEN
21 CLOSE get_id;
22 ELSE
23 CLOSE get_id;
24 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
25 fnd_message.set_token('SEQUENCE', 'GL_LEDGER_NORM_SEG_VALS_REC_S');
26 app_exception.raise_exception;
27 END IF;
28
29 RETURN (v_record_id);
30
31 EXCEPTION
32 WHEN app_exceptions.application_exception THEN
33 RAISE;
34 WHEN OTHERS THEN
35 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
36 fnd_message.set_token('PROCEDURE', 'GL_LEDGER_NORM_SEG_VALS_PKG.Get_Record_Id');
37 RAISE;
38
39 END Get_Record_Id;
40
41 -- **********************************************************************
42
43 PROCEDURE Check_Unique(X_Rowid VARCHAR2,
44 X_Ledger_Id NUMBER,
45 X_Segment_Value VARCHAR2,
46 X_Segment_Type_Code VARCHAR2,
47 X_Start_Date DATE,
48 X_End_Date DATE
49 ) IS
50 dummy VARCHAR2(1);
51
52 CURSOR check_unique IS
53 SELECT 'X'
54 FROM GL_LEDGER_NORM_SEG_VALS
55 WHERE ledger_id = X_Ledger_Id
56 AND segment_value = X_Segment_Value
57 AND segment_type_code = X_Segment_Type_Code
58 AND (start_date <= X_End_Date
59 OR start_date IS NULL
60 OR X_End_Date IS NULL)
61 AND (end_date >= X_Start_Date
62 OR end_date IS NULL
63 OR X_Start_Date IS NULL)
64 AND NVL(status_code,'X') <> 'D'
65 AND ((X_Rowid IS NULL) or (rowid <> X_Rowid)) ;
66
67 BEGIN
68
69 OPEN check_unique;
70 FETCH check_unique INTO dummy;
71 IF check_unique%FOUND THEN
72 CLOSE check_unique;
73 fnd_message.set_name( 'SQLGL', 'GL_LEDGER_UNIQUE_SEGVAL_ASSIGN' );
74 app_exception.raise_exception;
75 END IF;
76
77 CLOSE check_unique;
78
79 EXCEPTION
80 WHEN app_exceptions.application_exception THEN
81 RAISE;
82 WHEN OTHERS THEN
83 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
84 fnd_message.set_token('PROCEDURE',
85 'GL_LEDGER_NORM_SEG_VALS_PKG.check_unique');
86 RAISE;
87 END Check_Unique;
88
89 -- **********************************************************************
90
91 FUNCTION Check_Exist(X_Ledger_Id NUMBER,
92 X_Segment_Type_Code VARCHAR2) RETURN BOOLEAN
93 IS
94 dummy VARCHAR2(1);
95
96 CURSOR check_exist IS
97 SELECT 'X'
98 FROM GL_LEDGER_NORM_SEG_VALS
99 WHERE ledger_id = X_Ledger_Id
100 AND segment_type_code = X_Segment_Type_Code
101 AND NVL(status_code,'X') <> 'D';
102
103 BEGIN
104 OPEN check_exist;
105 FETCH check_exist INTO dummy;
106 IF check_exist%NOTFOUND THEN
107 CLOSE check_exist;
108 RETURN FALSE;
109 ELSE
110 CLOSE check_exist;
111 RETURN TRUE;
112 END IF;
113
114 EXCEPTION
115 WHEN OTHERS THEN
116 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
117 fnd_message.set_token('PROCEDURE',
118 'GL_LEDGER_NORM_SEG_VALS_PKG.Check_Exist');
119 RAISE;
120
121 END Check_Exist;
122
123 -- **********************************************************************
124
125 FUNCTION Check_Conc_With_Flat(X_Ledger_Id NUMBER,
126 X_Segment_Type_Code VARCHAR2) RETURN BOOLEAN
127 IS
128 CURSOR Seg_Val_Request_Id IS
129 SELECT DISTINCT request_id
130 FROM GL_LEDGER_NORM_SEG_VALS
131 WHERE ledger_id = X_Ledger_Id
132 AND segment_type_code = X_Segment_Type_Code
133 AND request_id IS NOT NULL
134 AND NVL(status_code,'X') <> 'D';
135
136 v_request_id Seg_Val_Request_Id%ROWTYPE;
137
138 call_status BOOLEAN;
139 rphase VARCHAR2(80);
140 rstatus VARCHAR2(80);
141 dphase VARCHAR2(30);
142 dstatus VARCHAR2(30);
143 message VARCHAR2(240);
144 request_id NUMBER(15);
145
146 BEGIN
147 FOR v_request_id IN Seg_Val_Request_Id LOOP
148 --
149 -- Prevent a record from be modified if it is currenlty processed by
150 -- the Flattening program
151 --
152 request_id := v_request_id.request_id;
153
154 call_status :=
155 FND_CONCURRENT.GET_REQUEST_STATUS(request_id => request_id,
156 appl_shortname => 'SQLGL',
157 program => 'GLSTFL',
158 phase => rphase,
159 status => rstatus,
160 dev_phase => dphase,
161 dev_status => dstatus,
162 message => message);
163
164 IF (dphase = 'RUNNING') THEN
165 return (FALSE);
166 END IF;
167
168 END LOOP;
169
170 return (TRUE);
171
172 EXCEPTION
173 WHEN OTHERS THEN
174 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
175 fnd_message.set_token('PROCEDURE',
176 'GL_LEDGER_NORM_SEG_VALS_PKG.Check_Conc_With_Flat');
177 RAISE;
178
179 END Check_Conc_With_Flat;
180
181 -- **********************************************************************
182
183 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
184 X_Ledger_Id NUMBER,
185 X_Segment_Type_Code VARCHAR2,
186 X_Segment_Value VARCHAR2,
187 X_Segment_Value_Type_Code VARCHAR2,
188 X_Record_Id NUMBER,
189 X_Last_Update_Date DATE,
190 X_Last_Updated_By NUMBER,
191 X_Creation_Date DATE,
192 X_Created_By NUMBER,
193 X_Last_Update_Login NUMBER,
194 X_Start_Date DATE,
195 X_End_Date DATE,
196 X_Context VARCHAR2,
197 X_Attribute1 VARCHAR2,
198 X_Attribute2 VARCHAR2,
199 X_Attribute3 VARCHAR2,
200 X_Attribute4 VARCHAR2,
201 X_Attribute5 VARCHAR2,
202 X_Attribute6 VARCHAR2,
203 X_Attribute7 VARCHAR2,
204 X_Attribute8 VARCHAR2,
205 X_Attribute9 VARCHAR2,
206 X_Attribute10 VARCHAR2,
207 X_Attribute11 VARCHAR2,
208 X_Attribute12 VARCHAR2,
209 X_Attribute13 VARCHAR2,
210 X_Attribute14 VARCHAR2,
211 X_Attribute15 VARCHAR2,
212 X_Request_Id NUMBER
213 ) IS
214 CURSOR C
215 IS SELECT rowid
216 FROM GL_LEDGER_NORM_SEG_VALS
217 WHERE ledger_id = X_Ledger_Id
218 AND segment_type_code = X_Segment_Type_Code
219 AND segment_value = X_Segment_Value
220 AND (start_date = X_Start_Date OR
221 (start_date IS NULL AND X_Start_Date IS NULL))
222 AND (end_date = X_End_Date OR
223 (end_date IS NULL AND X_End_Date IS NULL))
224 AND NVL(status_code,'X') <> 'D';
225
226 BEGIN
227 -- Verify that this combination is unique and does not overlap with other dates.
228 GL_LEDGER_NORM_SEG_VALS_PKG.Check_Unique(X_Rowid,
229 X_Ledger_Id,
230 X_Segment_Value,
231 X_Segment_Type_Code,
232 X_Start_Date,
233 X_End_Date);
234
235 INSERT INTO GL_LEDGER_NORM_SEG_VALS(
236 ledger_id,
237 segment_type_code,
238 segment_value,
239 segment_value_type_code,
240 status_code,
241 record_id,
242 last_update_date,
243 last_updated_by,
244 creation_date,
245 created_by,
246 last_update_login,
247 start_date,
248 end_date,
249 context,
250 attribute1,
251 attribute2,
252 attribute3,
253 attribute4,
254 attribute5,
255 attribute6,
256 attribute7,
257 attribute8,
258 attribute9,
259 attribute10,
260 attribute11,
261 attribute12,
262 attribute13,
263 attribute14,
264 attribute15,
265 request_id
266 ) VALUES (
267 X_Ledger_Id,
268 X_Segment_Type_Code,
269 X_Segment_Value,
270 X_Segment_Value_Type_Code,
271 'I',
272 X_Record_Id,
273 X_Last_Update_Date,
274 X_Last_Updated_By,
275 X_Creation_Date,
276 X_Created_By,
277 X_Last_Update_Login,
278 X_Start_Date,
279 X_End_Date,
280 X_Context,
281 X_Attribute1,
282 X_Attribute2,
283 X_Attribute3,
284 X_Attribute4,
285 X_Attribute5,
286 X_Attribute6,
287 X_Attribute7,
288 X_Attribute8,
289 X_Attribute9,
290 X_Attribute10,
291 X_Attribute11,
292 X_Attribute12,
293 X_Attribute13,
294 X_Attribute14,
295 X_Attribute15,
296 X_Request_Id
297 );
298
299 OPEN C;
300 FETCH C INTO X_Rowid;
301 IF (C%NOTFOUND) THEN
302 CLOSE C;
303 RAISE NO_DATA_FOUND;
304 END IF;
305 CLOSE C;
306 END Insert_Row;
307
308 -- **********************************************************************
309
310 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
311 X_Ledger_Id NUMBER,
312 X_Segment_Type_Code VARCHAR2,
313 X_Segment_Value VARCHAR2,
314 X_Segment_Value_Type_Code VARCHAR2,
315 X_Record_Id NUMBER,
316 X_Start_Date DATE,
317 X_End_Date DATE,
318 X_Context VARCHAR2,
319 X_Attribute1 VARCHAR2,
320 X_Attribute2 VARCHAR2,
321 X_Attribute3 VARCHAR2,
322 X_Attribute4 VARCHAR2,
323 X_Attribute5 VARCHAR2,
324 X_Attribute6 VARCHAR2,
325 X_Attribute7 VARCHAR2,
326 X_Attribute8 VARCHAR2,
327 X_Attribute9 VARCHAR2,
328 X_Attribute10 VARCHAR2,
329 X_Attribute11 VARCHAR2,
330 X_Attribute12 VARCHAR2,
331 X_Attribute13 VARCHAR2,
332 X_Attribute14 VARCHAR2,
333 X_Attribute15 VARCHAR2,
334 X_Request_Id NUMBER
335 ) IS
336 CURSOR C IS
337 SELECT *
338 FROM GL_LEDGER_NORM_SEG_VALS
339 WHERE rowid = X_Rowid
340 FOR UPDATE of Ledger_Id NOWAIT;
341 Recinfo C%ROWTYPE;
342
343 call_status BOOLEAN;
344 rphase VARCHAR2(80);
345 rstatus VARCHAR2(80);
346 dphase VARCHAR2(30);
347 dstatus VARCHAR2(30);
348 message VARCHAR2(240);
349 v_request_id NUMBER(15);
350
351 BEGIN
352 --
353 -- Prevent a record from being modified if it is currently being
354 -- processed by the Flattening program
355 --
356 IF(X_Request_Id IS NOT NULL) THEN
357 v_request_id := X_Request_Id;
358 call_status :=
359 FND_CONCURRENT.GET_REQUEST_STATUS(request_id => v_request_id,
360 appl_shortname => 'SQLGL',
361 program => 'GLSTFL',
362 phase => rphase,
363 status => rstatus,
364 dev_phase => dphase,
365 dev_status => dstatus,
366 message => message);
367
368 IF (dphase = 'RUNNING') THEN
369 FND_MESSAGE.Set_Name('SQLGL', 'GL_LEDGER_RECORD_PROC_BY_FLAT');
370 APP_EXCEPTION.Raise_Exception;
371 END IF;
372 END IF;
373
374 OPEN C;
375 FETCH C INTO Recinfo;
376 if (C%NOTFOUND) then
377 CLOSE C;
378 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
379 APP_EXCEPTION.Raise_Exception;
380 end if;
381 CLOSE C;
382 IF (
383 ( (Recinfo.ledger_id = X_Ledger_Id)
384 OR ( (Recinfo.ledger_id IS NULL)
385 AND (X_Ledger_Id IS NULL)))
386 AND ( (Recinfo.segment_type_code = X_Segment_Type_Code)
387 OR ( (Recinfo.segment_type_code IS NULL)
388 AND (X_Segment_Type_Code IS NULL)))
389 AND ( (Recinfo.segment_value = X_Segment_Value)
390 OR ( (Recinfo.segment_value IS NULL)
391 AND (X_Segment_Value IS NULL)))
392 AND ( (Recinfo.segment_value_type_code = X_Segment_Value_Type_Code)
393 OR ( (Recinfo.segment_value_type_code IS NULL)
394 AND (X_Segment_Value_Type_Code IS NULL)))
395 AND ( (Recinfo.record_id = X_Record_Id)
396 OR ( (Recinfo.record_id IS NULL)
397 AND (X_Record_Id IS NULL)))
398 AND ( (Recinfo.start_date = X_Start_Date)
399 OR ( (Recinfo.start_date IS NULL)
400 AND (X_Start_Date IS NULL)))
401 AND ( (Recinfo.end_date = X_End_Date)
402 OR ( (Recinfo.end_date IS NULL)
403 AND (X_End_Date IS NULL)))
404 AND ( (Recinfo.context = X_Context)
405 OR ( (Recinfo.context IS NULL)
406 AND (X_Context IS NULL)))
407 AND ( (Recinfo.attribute1 = X_Attribute1)
408 OR ( (Recinfo.attribute1 IS NULL)
409 AND (X_Attribute1 IS NULL)))
410 AND ( (Recinfo.attribute2 = X_Attribute2)
411 OR ( (Recinfo.attribute2 IS NULL)
412 AND (X_Attribute2 IS NULL)))
413 AND ( (Recinfo.attribute3 = X_Attribute3)
414 OR ( (Recinfo.attribute3 IS NULL)
415 AND (X_Attribute3 IS NULL)))
416 AND ( (Recinfo.attribute4 = X_Attribute4)
417 OR ( (Recinfo.attribute4 IS NULL)
418 AND (X_Attribute4 IS NULL)))
419 AND ( (Recinfo.attribute5 = X_Attribute5)
420 OR ( (Recinfo.attribute5 IS NULL)
421 AND (X_Attribute5 IS NULL)))
422 AND ( (Recinfo.attribute6 = X_Attribute6)
423 OR ( (Recinfo.attribute6 IS NULL)
424 AND (X_Attribute6 IS NULL)))
425 AND ( (Recinfo.attribute7 = X_Attribute7)
426 OR ( (Recinfo.attribute7 IS NULL)
427 AND (X_Attribute7 IS NULL)))
428 AND ( (Recinfo.attribute8 = X_Attribute8)
429 OR ( (Recinfo.attribute8 IS NULL)
430 AND (X_Attribute8 IS NULL)))
431 AND ( (Recinfo.attribute9 = X_Attribute9)
432 OR ( (Recinfo.attribute9 IS NULL)
433 AND (X_Attribute9 IS NULL)))
434 AND ( (Recinfo.attribute10 = X_Attribute10)
435 OR ( (Recinfo.attribute10 IS NULL)
436 AND (X_Attribute10 IS NULL)))
437 AND ( (Recinfo.attribute11 = X_Attribute11)
438 OR ( (Recinfo.attribute11 IS NULL)
439 AND (X_Attribute11 IS NULL)))
440 AND ( (Recinfo.attribute12 = X_Attribute12)
441 OR ( (Recinfo.attribute12 IS NULL)
442 AND (X_Attribute12 IS NULL)))
443 AND ( (Recinfo.attribute13 = X_Attribute13)
444 OR ( (Recinfo.attribute13 IS NULL)
445 AND (X_Attribute13 IS NULL)))
446 AND ( (Recinfo.attribute14 = X_Attribute14)
447 OR ( (Recinfo.attribute14 IS NULL)
448 AND (X_Attribute14 IS NULL)))
449 AND ( (Recinfo.attribute15 = X_Attribute15)
450 OR ( (Recinfo.attribute15 IS NULL)
451 AND (X_Attribute15 IS NULL)))
452 AND ( (Recinfo.request_id = X_Request_Id)
453 OR ( (Recinfo.request_id IS NULL)
454 AND (X_Request_Id IS NULL)))
455 ) THEN
456 RETURN;
457 ELSE
458 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
459 APP_EXCEPTION.Raise_Exception;
460 END IF;
461 END Lock_Row;
462
463 -- **********************************************************************
464
465 PROCEDURE Update_Row(X_Rowid VARCHAR2,
466 X_Ledger_Id NUMBER,
467 X_Segment_Type_Code VARCHAR2,
468 X_Segment_Value VARCHAR2,
469 X_Segment_Value_Type_Code VARCHAR2,
470 X_Record_Id NUMBER,
471 X_Last_Update_Date DATE,
472 X_Last_Updated_By NUMBER,
473 X_Last_Update_Login NUMBER,
474 X_Start_Date DATE,
475 X_End_Date DATE,
476 X_Context VARCHAR2,
477 X_Attribute1 VARCHAR2,
478 X_Attribute2 VARCHAR2,
479 X_Attribute3 VARCHAR2,
480 X_Attribute4 VARCHAR2,
481 X_Attribute5 VARCHAR2,
482 X_Attribute6 VARCHAR2,
483 X_Attribute7 VARCHAR2,
484 X_Attribute8 VARCHAR2,
485 X_Attribute9 VARCHAR2,
486 X_Attribute10 VARCHAR2,
487 X_Attribute11 VARCHAR2,
488 X_Attribute12 VARCHAR2,
489 X_Attribute13 VARCHAR2,
490 X_Attribute14 VARCHAR2,
491 X_Attribute15 VARCHAR2,
492 X_Request_Id NUMBER
493 )
494 IS
495 v_status_code VARCHAR2(1);
496
497 BEGIN
498 -- Verify that this combination is unique and does not overlap with other dates.
499 GL_LEDGER_NORM_SEG_VALS_PKG.Check_Unique(X_Rowid,
500 X_Ledger_Id,
501 X_Segment_Value,
502 X_Segment_Type_Code,
503 X_Start_Date,
504 X_End_Date);
505
506 -- If a row has a status_code of 'I', the Flattening Program has not been run yet.
507 -- In this case, the status_code should remain 'I'.
508 -- Otherwise, the status_code should be 'U'.
509 SELECT NVL(status_code,'X')
510 INTO v_status_code
511 FROM GL_LEDGER_NORM_SEG_VALS
512 WHERE rowid = X_Rowid;
513
514 IF (SQL%NOTFOUND) THEN
515 RAISE NO_DATA_FOUND;
516 END IF;
517
518 IF (v_status_code <> 'I') THEN
519 v_status_code := 'U';
520 END IF;
521
522 UPDATE GL_LEDGER_NORM_SEG_VALS
523 SET
524 ledger_id = X_Ledger_Id,
525 segment_type_code = X_Segment_Type_Code,
526 segment_value = X_Segment_Value,
527 segment_value_type_code = X_Segment_Value_Type_Code,
528 status_code = v_status_code,
529 record_id = X_Record_Id,
530 last_update_date = X_Last_Update_Date,
531 last_updated_by = X_Last_Updated_By,
532 last_update_login = X_Last_Update_Login,
533 start_date = X_Start_Date,
534 end_date = X_End_Date,
535 context = X_Context,
536 attribute1 = X_Attribute1,
537 attribute2 = X_Attribute2,
538 attribute3 = X_Attribute3,
539 attribute4 = X_Attribute4,
540 attribute5 = X_Attribute5,
541 attribute6 = X_Attribute6,
542 attribute7 = X_Attribute7,
543 attribute8 = X_Attribute8,
544 attribute9 = X_Attribute9,
545 attribute10 = X_Attribute10,
546 attribute11 = X_Attribute11,
547 attribute12 = X_Attribute12,
548 attribute13 = X_Attribute13,
549 attribute14 = X_Attribute14,
550 attribute15 = X_Attribute15,
551 request_id = X_Request_Id
552 WHERE rowid = X_Rowid;
553
554 IF (SQL%NOTFOUND) THEN
555 RAISE NO_DATA_FOUND;
556 END IF;
557 END Update_Row;
558
559 -- **********************************************************************
560
561 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
562 BEGIN
563 -- This is a norm table. We do not delete row since the Flattening program will
564 -- take care of this.
565 -- Set the status code to 'Delete'.
566 UPDATE GL_LEDGER_NORM_SEG_VALS
567 SET status_code = 'D'
568 WHERE rowid = X_Rowid;
569
570 IF (SQL%NOTFOUND) THEN
571 RAISE NO_DATA_FOUND;
572 END IF;
573
574 END Delete_Row;
575
576 -- **********************************************************************
577
578 PROCEDURE Delete_All_Rows(X_Ledger_Id NUMBER,
579 X_Segment_Type_Code VARCHAR2) IS
580 BEGIN
581 -- This is a norm table. We do not delete row since the Flattening program will
582 -- take care of this.
583 -- Set the status code to 'Delete'.
584 UPDATE GL_LEDGER_NORM_SEG_VALS
585 SET status_code = 'D'
586 WHERE ledger_id = X_Ledger_Id
587 and segment_type_code = X_Segment_Type_Code;
588
589 END Delete_All_Rows;
590
591 END GL_LEDGER_NORM_SEG_VALS_PKG;