[Home] [Help]
PACKAGE BODY: APPS.GL_RECURRING_HEADERS_PKG
Source
1 PACKAGE BODY GL_RECURRING_HEADERS_PKG as
2 /* $Header: glirechb.pls 120.5 2005/05/05 01:19:57 kvora ship $ */
3
4
5 --
6 -- PUBLIC FUNCTIONS
7 --
8
9 FUNCTION is_valid_header_exist(
10 x_ledger_id NUMBER,
11 x_recurring_batch_id NUMBER ) RETURN BOOLEAN IS
12
13 CURSOR c_active IS
14 SELECT 'found'
15 FROM gl_recurring_headers head
16 WHERE head.LEDGER_ID = x_ledger_id
17 AND head.RECURRING_BATCH_ID = x_recurring_batch_id
18 AND TRUNC( sysdate )
19 BETWEEN NVL( head.START_DATE_ACTIVE, TRUNC( sysdate ) )
20 AND NVL( head.END_DATE_ACTIVE, TRUNC( sysdate ) );
21
22 dummy VARCHAR2( 100 );
23
24 BEGIN
25
26 OPEN c_active;
27
28 FETCH c_active INTO dummy;
29
30 IF c_active%FOUND THEN
31 CLOSE c_active;
32 RETURN( TRUE );
33 ELSE
34 CLOSE c_active;
35 RETURN( FALSE );
36 END IF;
37
38 EXCEPTION
39 WHEN app_exceptions.application_exception THEN
40 RAISE;
41 WHEN OTHERS THEN
42 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
43 fnd_message.set_token('PROCEDURE',
44 'GL_RECURRING_HEADERS_PKG.is_valid_header_exist');
45 RAISE;
46
47 END is_valid_header_exist;
48
49 -- **********************************************************************
50
51 PROCEDURE check_unique( x_rowid VARCHAR2,
52 x_name VARCHAR2,
53 x_batchid NUMBER ) IS
54 CURSOR c_dup IS
55 SELECT 'Duplicate'
56 FROM gl_recurring_headers h
57 WHERE upper( h.name) = upper( x_name )
58 AND h.recurring_batch_id = x_batchid
59 AND ( x_rowid is NULL
60 OR
61 h.rowid <> x_rowid );
62
63 dummy VARCHAR2(100);
64
65 BEGIN
66 OPEN c_dup;
67 FETCH c_dup INTO dummy;
68
69 IF c_dup%FOUND THEN
70 CLOSE c_dup;
71 fnd_message.set_name( 'SQLGL', 'GL_DUPLICATE_REC_HEADER' );
72 app_exception.raise_exception;
73 END IF;
74
75 CLOSE c_dup;
76
77 EXCEPTION
78 WHEN app_exceptions.application_exception THEN
79 RAISE;
80 WHEN OTHERS THEN
81 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
82 fnd_message.set_token('PROCEDURE',
83 'GL_RECURRING_HEADERS_PKG.check_unique');
84 RAISE;
85
86 END check_unique;
87
88 -- **********************************************************************
89
90 FUNCTION get_unique_id RETURN NUMBER IS
91
92 CURSOR c_getid IS
93 SELECT GL_RECURRING_HEADERS_S.NEXTVAL
94 FROM dual;
95
96 id number;
97
98 BEGIN
99 OPEN c_getid;
100 FETCH c_getid INTO id;
101
102 IF c_getid%FOUND THEN
103 CLOSE c_getid;
104 RETURN( id );
105 ELSE
106 CLOSE c_getid;
107 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
108 fnd_message.set_token('SEQUENCE', 'GL_RECURRING_HEADERS_S');
109 app_exception.raise_exception;
110 END IF;
111
112 EXCEPTION
113 WHEN app_exceptions.application_exception THEN
114 RAISE;
115 WHEN OTHERS THEN
116 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
117 fnd_message.set_token('PROCEDURE',
118 'GL_RECURRING_HEADERS_PKG.get_unique_id');
119 RAISE;
120
121 END get_unique_id;
122
123 -- **********************************************************************
124
125 PROCEDURE delete_rows( x_batch_id NUMBER ) IS
126
127 CURSOR c_header IS
128 SELECT h.RECURRING_HEADER_ID
129 FROM GL_RECURRING_HEADERS h
130 WHERE h.RECURRING_BATCH_ID = x_batch_id;
131
132 header_id NUMBER;
133
134 BEGIN
135
136 OPEN c_header;
137 LOOP
138 FETCH c_header
139 INTO header_id;
140 EXIT WHEN c_header%NOTFOUND;
141
142 DELETE
143 FROM GL_RECURRING_LINE_CALC_RULES
144 WHERE RECURRING_HEADER_ID = header_id;
145
146 DELETE
147 FROM GL_RECURRING_LINES
148 WHERE RECURRING_HEADER_ID = header_id;
149
150 END LOOP;
151
152 CLOSE c_header;
153
154 DELETE
155 FROM GL_RECURRING_HEADERS
156 WHERE RECURRING_BATCH_ID = x_batch_id;
157
158 EXCEPTION
159 WHEN app_exceptions.application_exception THEN
160 RAISE;
161 WHEN OTHERS THEN
162 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
163 fnd_message.set_token('PROCEDURE',
164 'GL_RECURRING_HEADERS_PKG.delete_rows');
165 RAISE;
166
167 END delete_rows;
168
169 -- **********************************************************************
170
171 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
172 X_Recurring_Header_Id IN OUT NOCOPY NUMBER,
173 X_Last_Update_Date DATE,
174 X_Last_Updated_By NUMBER,
175 X_Ledger_Id NUMBER,
176 X_Name VARCHAR2,
177 X_Je_Category_Name VARCHAR2,
178 X_Enabled_Flag VARCHAR2,
179 X_Allocation_Flag VARCHAR2,
180 X_Currency_Code VARCHAR2,
181 X_Currency_Conversion_Type VARCHAR2,
182 X_Creation_Date DATE,
183 X_Created_By NUMBER,
184 X_Last_Update_Login NUMBER,
185 X_Recurring_Batch_Id NUMBER,
186 X_Period_Type VARCHAR2,
187 X_Last_Executed_Period_Name VARCHAR2,
188 X_Last_Executed_Date DATE,
189 X_Start_Date_Active DATE,
190 X_End_Date_Active DATE,
191 X_Attribute1 VARCHAR2,
192 X_Attribute2 VARCHAR2,
193 X_Attribute3 VARCHAR2,
194 X_Attribute4 VARCHAR2,
195 X_Attribute5 VARCHAR2,
196 X_Attribute6 VARCHAR2,
197 X_Attribute7 VARCHAR2,
198 X_Attribute8 VARCHAR2,
199 X_Attribute9 VARCHAR2,
200 X_Attribute10 VARCHAR2,
201 X_Context VARCHAR2
202 ) IS
203
204 CURSOR C IS
205 SELECT rowid
206 FROM GL_RECURRING_HEADERS
207 WHERE recurring_header_id = X_Recurring_Header_Id;
208
209 CURSOR C2 IS
210 SELECT gl_recurring_headers_s.nextval
211 FROM dual;
212
213 BEGIN
214
215 if (X_Recurring_Header_Id is NULL) then
216 OPEN C2;
217 FETCH C2 INTO X_Recurring_Header_Id;
218
219 if (C2%NOTFOUND) then
220 CLOSE C2;
221 RAISE NO_DATA_FOUND;
222 end if;
223
224 CLOSE C2;
225 end if;
226
227 -- Consolidating call to check unique to save on round trips
228 check_unique(X_rowid, X_name, X_Recurring_Batch_id);
229
230 INSERT INTO GL_RECURRING_HEADERS(
231 recurring_header_id,
232 last_update_date,
233 last_updated_by,
234 ledger_id,
235 name,
236 je_category_name,
237 enabled_flag,
238 allocation_flag,
239 currency_code,
240 currency_conversion_type,
241 creation_date,
242 created_by,
243 last_update_login,
244 recurring_batch_id,
245 period_type,
246 last_executed_period_name,
247 last_executed_date,
248 start_date_active,
249 end_date_active,
250 attribute1,
251 attribute2,
252 attribute3,
253 attribute4,
254 attribute5,
255 attribute6,
256 attribute7,
257 attribute8,
258 attribute9,
259 attribute10,
260 context
261 ) VALUES (
262 X_Recurring_Header_Id,
263 X_Last_Update_Date,
264 X_Last_Updated_By,
265 X_Ledger_Id,
266 X_Name,
267 X_Je_Category_Name,
268 X_Enabled_Flag,
269 X_Allocation_Flag,
270 X_Currency_Code,
271 X_Currency_Conversion_Type,
272 X_Creation_Date,
273 X_Created_By,
274 X_Last_Update_Login,
275 X_Recurring_Batch_Id,
276 X_Period_Type,
277 X_Last_Executed_Period_Name,
278 X_Last_Executed_Date,
279 X_Start_Date_Active,
280 X_End_Date_Active,
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_Context
292
293 );
294
295 OPEN C;
296 FETCH C INTO X_Rowid;
297 if (C%NOTFOUND) then
298 CLOSE C;
299 RAISE NO_DATA_FOUND;
300 end if;
301 CLOSE C;
302 END Insert_Row;
303
304
305
306 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
307 X_Recurring_Header_Id NUMBER,
308 X_Ledger_Id NUMBER,
309 X_Name VARCHAR2,
310 X_Je_Category_Name VARCHAR2,
311 X_Enabled_Flag VARCHAR2,
312 X_Allocation_Flag VARCHAR2,
313 X_Currency_Code VARCHAR2,
314 X_Currency_Conversion_Type VARCHAR2,
315 X_Recurring_Batch_Id NUMBER,
316 X_Period_Type VARCHAR2,
317 X_Last_Executed_Period_Name VARCHAR2,
318 X_Last_Executed_Date DATE,
319 X_Start_Date_Active DATE,
320 X_End_Date_Active DATE,
321 X_Attribute1 VARCHAR2,
322 X_Attribute2 VARCHAR2,
323 X_Attribute3 VARCHAR2,
324 X_Attribute4 VARCHAR2,
325 X_Attribute5 VARCHAR2,
326 X_Attribute6 VARCHAR2,
327 X_Attribute7 VARCHAR2,
328 X_Attribute8 VARCHAR2,
329 X_Attribute9 VARCHAR2,
330 X_Attribute10 VARCHAR2,
331 X_Context VARCHAR2
332 ) IS
333 CURSOR C IS
334 SELECT *
335 FROM GL_RECURRING_HEADERS
336 WHERE rowid = X_Rowid
337 FOR UPDATE OF Recurring_Header_Id NOWAIT;
338 Recinfo C%ROWTYPE;
339
340 BEGIN
341 OPEN C;
342 FETCH C INTO Recinfo;
343 if (C%NOTFOUND) then
344 CLOSE C;
345 RAISE NO_DATA_FOUND;
346 end if;
347 CLOSE C;
348 if (
349 ( (Recinfo.recurring_header_id = X_Recurring_Header_Id)
350 OR ( (Recinfo.recurring_header_id IS NULL)
351 AND (X_Recurring_Header_Id IS NULL)))
352 AND ( (Recinfo.ledger_id = X_Ledger_Id)
353 OR ( (Recinfo.ledger_id IS NULL)
354 AND (X_Ledger_Id IS NULL)))
355 AND ( (Recinfo.name = X_Name)
356 OR ( (Recinfo.name IS NULL)
357 AND (X_Name IS NULL)))
358 AND ( (Recinfo.je_category_name = X_Je_Category_Name)
359 OR ( (Recinfo.je_category_name IS NULL)
360 AND (X_Je_Category_Name IS NULL)))
361 AND ( (Recinfo.enabled_flag = X_Enabled_Flag)
362 OR ( (Recinfo.enabled_flag IS NULL)
363 AND (X_Enabled_Flag IS NULL)))
364 AND ( (Recinfo.allocation_flag = X_Allocation_Flag)
365 OR ( (Recinfo.allocation_flag IS NULL)
366 AND (X_Allocation_Flag IS NULL)))
367 AND ( (Recinfo.currency_code = X_Currency_Code)
368 OR ( (Recinfo.currency_code IS NULL)
369 AND (X_Currency_Code IS NULL)))
370 AND ( (Recinfo.currency_conversion_type = X_Currency_Conversion_Type)
371 OR ( (Recinfo.currency_conversion_type IS NULL)
372 AND (X_Currency_Conversion_Type IS NULL)))
373 AND ( (Recinfo.recurring_batch_id = X_Recurring_Batch_Id)
374 OR ( (Recinfo.recurring_batch_id IS NULL)
375 AND (X_Recurring_Batch_Id IS NULL)))
376 AND ( (Recinfo.period_type = X_Period_Type)
377 OR ( (Recinfo.period_type IS NULL)
378 AND (X_Period_Type IS NULL)))
379 AND ( (Recinfo.last_executed_period_name = X_Last_Executed_Period_Name)
380 OR ( (Recinfo.last_executed_period_name IS NULL)
381 AND (X_Last_Executed_Period_Name IS NULL)))
382 AND ( (Recinfo.last_executed_date = X_Last_Executed_Date)
383 OR ( (Recinfo.last_executed_date IS NULL)
384 AND (X_Last_Executed_Date IS NULL)))
388 AND ( (Recinfo.end_date_active = X_End_Date_Active)
385 AND ( (Recinfo.start_date_active = X_Start_Date_Active)
386 OR ( (Recinfo.start_date_active IS NULL)
387 AND (X_Start_Date_Active IS NULL)))
389 OR ( (Recinfo.end_date_active IS NULL)
390 AND (X_End_Date_Active IS NULL)))
391 AND ( (Recinfo.attribute1 = X_Attribute1)
392 OR ( (Recinfo.attribute1 IS NULL)
393 AND (X_Attribute1 IS NULL)))
394 AND ( (Recinfo.attribute2 = X_Attribute2)
395 OR ( (Recinfo.attribute2 IS NULL)
396 AND (X_Attribute2 IS NULL)))
397 AND ( (Recinfo.attribute3 = X_Attribute3)
398 OR ( (Recinfo.attribute3 IS NULL)
399 AND (X_Attribute3 IS NULL)))
400 AND ( (Recinfo.attribute4 = X_Attribute4)
401 OR ( (Recinfo.attribute4 IS NULL)
402 AND (X_Attribute4 IS NULL)))
403 AND ( (Recinfo.attribute5 = X_Attribute5)
404 OR ( (Recinfo.attribute5 IS NULL)
405 AND (X_Attribute5 IS NULL)))
406 AND ( (Recinfo.attribute6 = X_Attribute6)
407 OR ( (Recinfo.attribute6 IS NULL)
408 AND (X_Attribute6 IS NULL)))
409 AND ( (Recinfo.attribute7 = X_Attribute7)
410 OR ( (Recinfo.attribute7 IS NULL)
411 AND (X_Attribute7 IS NULL)))
412 AND ( (Recinfo.attribute8 = X_Attribute8)
413 OR ( (Recinfo.attribute8 IS NULL)
414 AND (X_Attribute8 IS NULL)))
415 AND ( (Recinfo.attribute9 = X_Attribute9)
416 OR ( (Recinfo.attribute9 IS NULL)
417 AND (X_Attribute9 IS NULL)))
418 AND ( (Recinfo.attribute10 = X_Attribute10)
419 OR ( (Recinfo.attribute10 IS NULL)
420 AND (X_Attribute10 IS NULL)))
421 AND ( (Recinfo.context = X_Context)
422 OR ( (Recinfo.context IS NULL)
423 AND (X_Context IS NULL)))
424 ) then
425 return;
426 else
427 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
428 APP_EXCEPTION.RAISE_EXCEPTION;
429 end if;
430 END Lock_Row;
431
432
433
434
435 PROCEDURE Update_Row(X_Rowid VARCHAR2,
436 X_Recurring_Header_Id NUMBER,
437 X_Last_Update_Date DATE,
438 X_Last_Updated_By NUMBER,
439 X_Ledger_Id NUMBER,
440 X_Name VARCHAR2,
441 X_Je_Category_Name VARCHAR2,
442 X_Enabled_Flag VARCHAR2,
443 X_Allocation_Flag VARCHAR2,
444 X_Currency_Code VARCHAR2,
445 X_Currency_Conversion_Type VARCHAR2,
446 X_Last_Update_Login NUMBER,
447 X_Recurring_Batch_Id NUMBER,
448 X_Period_Type VARCHAR2,
449 X_Last_Executed_Period_Name VARCHAR2,
450 X_Last_Executed_Date DATE,
451 X_Start_Date_Active DATE,
452 X_End_Date_Active DATE,
453 X_Attribute1 VARCHAR2,
454 X_Attribute2 VARCHAR2,
455 X_Attribute3 VARCHAR2,
456 X_Attribute4 VARCHAR2,
457 X_Attribute5 VARCHAR2,
458 X_Attribute6 VARCHAR2,
459 X_Attribute7 VARCHAR2,
460 X_Attribute8 VARCHAR2,
461 X_Attribute9 VARCHAR2,
462 X_Attribute10 VARCHAR2,
463 X_Context VARCHAR2
464 -- Currency_Changed IN OUT NOCOPY VARCHAR2
465 ) IS
466
467 func_curr VARCHAR2(15);
468
469 BEGIN
470
471 -- Consolidating call to check unique to save on round trips
472 check_unique(X_rowid, X_name, X_Recurring_Batch_id);
473
474 UPDATE GL_RECURRING_HEADERS
475 SET
476 recurring_header_id = X_Recurring_Header_Id,
477 last_update_date = X_Last_Update_Date,
478 last_updated_by = X_Last_Updated_By,
479 ledger_id = X_Ledger_Id,
480 name = X_Name,
481 je_category_name = X_Je_Category_Name,
482 enabled_flag = X_Enabled_Flag,
483 allocation_flag = X_Allocation_Flag,
484 currency_code = X_Currency_Code,
485 currency_conversion_type = X_Currency_Conversion_Type,
486 last_update_login = X_Last_Update_Login,
487 recurring_batch_id = X_Recurring_Batch_Id,
488 period_type = X_Period_Type,
489 last_executed_period_name = X_Last_Executed_Period_Name,
490 last_executed_date = X_Last_Executed_Date,
491 start_date_active = X_Start_Date_Active,
492 end_date_active = X_End_Date_Active,
493 attribute1 = X_Attribute1,
494 attribute2 = X_Attribute2,
495 attribute3 = X_Attribute3,
496 attribute4 = X_Attribute4,
497 attribute5 = X_Attribute5,
498 attribute6 = X_Attribute6,
499 attribute7 = X_Attribute7,
500 attribute8 = X_Attribute8,
501 attribute9 = X_Attribute9,
502 attribute10 = X_Attribute10,
503 context = X_Context
504 WHERE rowid = X_rowid;
505
506 if (SQL%NOTFOUND) then
507 RAISE NO_DATA_FOUND;
508 end if;
509
510 END Update_Row;
511
512
513
514 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
515 BEGIN
516 DELETE
517 FROM GL_RECURRING_HEADERS
518 WHERE rowid = X_Rowid;
519
520 if (SQL%NOTFOUND) then
521 RAISE NO_DATA_FOUND;
522 end if;
523 END Delete_Row;
524
525
526
527 END GL_RECURRING_HEADERS_PKG;