[Home] [Help]
PACKAGE BODY: APPS.CE_FORECAST_HEADERS_PKG
Source
1 PACKAGE BODY CE_FORECAST_HEADERS_PKG AS
2 /* $Header: cefhdrb.pls 120.1 2002/11/12 21:23:33 bhchung ship $ */
3 --
4 -- Package
5 -- CE_FORECASTT_HEADERS_PKG
6 -- Purpose
7 -- To group all the procedures/functions for table handling of the
8 -- ce_forecast_headers table.
9 -- History
10 -- 07.10.96 C. Kawamoto Created
11 --
12
13 FUNCTION body_revision RETURN VARCHAR2 IS
14 BEGIN
15
16 RETURN '$Revision: 120.1 $';
17
18 END body_revision;
19
20 FUNCTION spec_revision RETURN VARCHAR2 IS
21 BEGIN
22
23 RETURN G_spec_revision;
24
25 END spec_revision;
26 --
27 -- Procedure
28 -- Check_Unique
29 -- Purpose
30 -- Checks the uniqueness of the forecast inserted.
31 -- History
32 -- 07.10.96 C. Kawamoto Created
33 -- Example
34 -- CE_FORECAST_HEADERS_PKG.Check_Unique(...);
35 -- Notes
36 --
37 PROCEDURE Check_Unique(
38 X_rowid VARCHAR2,
39 X_name VARCHAR2) IS
40 CURSOR chk_duplicates IS
41 SELECT 'Duplidate'
42 FROM ce_forecast_headers cfh
43 WHERE cfh.name = X_name
44 AND (X_rowid IS NULL
45 OR cfh.rowid <> chartorowid(X_rowid));
46 dummy VARCHAR2(100);
47 BEGIN
48 OPEN chk_duplicates;
49 FETCH chk_duplicates INTO dummy;
50
51 IF chk_duplicates%FOUND THEN
52 FND_MESSAGE.Set_Name('CE', 'CE_DUPLICATE_FORECAST_HDR');
53 APP_EXCEPTION.Raise_exception;
54 END IF;
55 CLOSE chk_duplicates;
56 EXCEPTION
57 WHEN APP_EXCEPTIONS.application_exception THEN
58 RAISE;
59 WHEN OTHERS THEn
60 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
61 FND_MESSAGE.Set_Token('PROCEDURE', 'ce_cf_headers_pkg.check_unique');
62 RAISE;
63 END check_unique;
64
65 --
66 -- Procedure
67 -- insert_row
68 -- Purpose
69 -- To insert new row to ce_forecast_headers.
70 -- History
71 -- 07.10.96 C. Kawamoto Created
72 -- Example
73 -- CE_FORECAST_HEADERS_PKG.Insert_Row(...)
74 -- Notes
75 --
76
77 PROCEDURE Insert_Row(
78 X_rowid IN OUT NOCOPY VARCHAR2,
79 X_forecast_header_id IN OUT NOCOPY NUMBER,
80 X_name VARCHAR2,
81 X_description VARCHAR2,
82 X_aging_type VARCHAR2,
83 X_overdue_transactions VARCHAR2,
84 X_cutoff_period NUMBER,
85 X_transaction_calendar_id NUMBER,
86 X_start_project_id NUMBER,
87 X_end_project_id NUMBER,
88 X_treasury_template VARCHAR2,
89 X_created_by NUMBER,
90 X_creation_date DATE,
91 X_last_updated_by NUMBER,
92 X_last_update_date DATE,
93 X_last_update_login NUMBER,
94 X_attribute_category VARCHAR2,
95 X_attribute1 VARCHAR2,
96 X_attribute2 VARCHAR2,
97 X_attribute3 VARCHAR2,
98 X_attribute4 VARCHAR2,
99 X_attribute5 VARCHAR2,
100 X_attribute6 VARCHAR2,
101 X_attribute7 VARCHAR2,
102 X_attribute8 VARCHAR2,
103 X_attribute9 VARCHAR2,
104 X_attribute10 VARCHAR2,
105 X_attribute11 VARCHAR2,
106 X_attribute12 VARCHAR2,
107 X_attribute13 VARCHAR2,
108 X_attribute14 VARCHAR2,
109 X_attribute15 VARCHAR2
110 ) IS
111 CURSOR C IS SELECT rowid FROM ce_forecast_headers
112 WHERE forecast_header_id = X_forecast_header_id;
113 CURSOR C2 IS SELECT ce_forecast_headers_s.nextval FROM sys.dual;
114 p_row_id VARCHAR2(100);
115 p_forecast_column_id NUMBER;
116
117 BEGIN
118 OPEN C2;
119 FETCH C2 INTO X_forecast_header_id;
120 CLOSE C2;
121
122 INSERT INTO ce_forecast_headers(
123 forecast_header_id,
124 name,
125 description,
126 aging_type,
127 overdue_transactions,
128 cutoff_period,
129 transaction_calendar_id,
130 start_project_id,
131 end_project_id,
132 treasury_template,
133 created_by,
134 creation_date,
135 last_updated_by,
136 last_update_date,
137 last_update_login,
138 attribute_category,
139 attribute1,
140 attribute2,
141 attribute3,
142 attribute4,
143 attribute5,
144 attribute6,
145 attribute7,
146 attribute8,
147 attribute9,
148 attribute10,
149 attribute11,
150 attribute12,
151 attribute13,
152 attribute14,
153 attribute15
154 ) VALUES (
155 X_forecast_header_id,
156 X_name,
157 X_description,
158 X_aging_type,
159 X_overdue_transactions,
160 X_cutoff_period,
161 X_transaction_calendar_id,
162 X_start_project_id,
163 X_end_project_id,
164 X_treasury_template,
165 X_created_by,
166 X_creation_date,
167 X_last_updated_by,
168 X_last_update_date,
169 X_last_update_login,
170 X_attribute_category,
171 X_attribute1,
172 X_attribute2,
173 X_attribute3,
174 X_attribute4,
175 X_attribute5,
176 X_attribute6,
177 X_attribute7,
178 X_attribute8,
179 X_attribute9,
180 X_attribute10,
181 X_attribute11,
182 X_attribute12,
183 X_attribute13,
184 X_attribute14,
185 X_attribute15
186 );
187 OPEN C;
188 FETCH C INTO X_rowid;
189 if (C%NOTFOUND) then
190 CLOSE C;
191 Raise NO_DATA_FOUND;
192 end if;
193 CLOSE C;
194
195 IF(X_overdue_transactions = 'INCLUDE')THEN
196 CE_FORECAST_COLUMNS_PKG.insert_row(
197 X_rowid => p_row_id,
198 X_forecast_column_id => p_forecast_column_id,
199 X_forecast_header_id => X_forecast_header_id,
200 X_column_number => 0,
201 X_days_from => -X_cutoff_period+1,
202 X_days_to => 0,
203 X_developer_column_num => 0,
204 X_created_by => X_created_by,
205 X_creation_date => X_creation_date,
206 X_last_updated_by => X_last_updated_by,
207 X_last_update_date => X_last_update_date,
208 X_last_update_login => X_last_update_login,
209 X_attribute_category => null,
210 X_attribute1 => null,
211 X_attribute2 => null,
212 X_attribute3 => null,
213 X_attribute4 => null,
214 X_attribute5 => null,
215 X_attribute6 => null,
216 X_attribute7 => null,
217 X_attribute8 => null,
218 X_attribute9 => null,
219 X_attribute10 => null,
220 X_attribute11 => null,
221 X_attribute12 => null,
222 X_attribute13 => null,
223 X_attribute14 => null,
224 X_attribute15 => null);
225 END IF;
226
227 END Insert_Row;
228 --
229 -- Procedure
230 -- lock_row
231 -- Purpose
232 -- To lock a row from ce_forecast_headers.
233 -- History
234 -- 07.10.96 C. Kawamoto Created
235 -- Example
236 -- CE_FORECAST_HEADERS_PKG.Lock_Row(...)
237 -- Notes
238 --
239
240 PROCEDURE Lock_Row(
241 X_rowid VARCHAR2,
242 X_forecast_header_id NUMBER,
243 X_name VARCHAR2,
244 X_description VARCHAR2,
245 X_aging_type VARCHAR2,
246 X_overdue_transactions VARCHAR2,
247 X_cutoff_period NUMBER,
248 X_transaction_calendar_id NUMBER,
249 X_start_project_id NUMBER,
250 X_end_project_id NUMBER,
251 X_created_by NUMBER,
252 X_creation_date DATE,
253 X_last_updated_by NUMBER,
254 X_last_update_date DATE,
255 X_last_update_login NUMBER,
256 X_attribute_category VARCHAR2,
257 X_attribute1 VARCHAR2,
258 X_attribute2 VARCHAR2,
259 X_attribute3 VARCHAR2,
260 X_attribute4 VARCHAR2,
261 X_attribute5 VARCHAR2,
262 X_attribute6 VARCHAR2,
263 X_attribute7 VARCHAR2,
264 X_attribute8 VARCHAR2,
265 X_attribute9 VARCHAR2,
266 X_attribute10 VARCHAR2,
267 X_attribute11 VARCHAR2,
268 X_attribute12 VARCHAR2,
269 X_attribute13 VARCHAR2,
270 X_attribute14 VARCHAR2,
271 X_attribute15 VARCHAR2
272 ) IS
273 CURSOR C IS
274 SELECT *
275 FROM ce_forecast_headers
276 WHERE rowid = X_rowid
277 FOR UPDATE of forecast_header_id NOWAIT;
278 Recinfo C%ROWTYPE;
279 BEGIN
280 OPEN C;
281 FETCH C INTO recinfo;
282 if (C%NOTFOUND) then
283 CLOSE C;
284 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
285 APP_EXCEPTION.Raise_Exception;
286 end if;
287 CLOSE C;
288 if (
289 (Recinfo.forecast_header_id = X_forecast_header_id)
290 AND (Recinfo.name = X_name)
291 AND ( (Recinfo.description = X_description)
292 OR ( (Recinfo.description IS NULL)
293 AND (X_description IS NULL)))
294 AND (Recinfo.aging_type = X_aging_type)
295 AND ( (Recinfo.overdue_transactions = X_overdue_transactions)
296 OR ( (Recinfo.overdue_transactions IS NULL)
297 AND (X_overdue_transactions IS NULL)))
298 AND ( (Recinfo.cutoff_period = X_cutoff_period)
299 OR ( (Recinfo.cutoff_period IS NULL)
300 AND (X_cutoff_period IS NULL)))
301 AND ( (Recinfo.transaction_calendar_id = X_transaction_calendar_id)
302 OR ( (Recinfo.transaction_calendar_id IS NULL)
303 AND (X_transaction_calendar_id IS NULL)))
304 AND ( (Recinfo.start_project_id = X_start_project_id)
305 OR ( (Recinfo.start_project_id IS NULL)
306 AND (X_start_project_id IS NULL)))
307 AND ( (Recinfo.end_project_id = X_end_project_id)
308 OR ( (Recinfo.end_project_id IS NULL)
309 AND (X_end_project_id IS NULL)))
310 AND ( (Recinfo.attribute_category = X_attribute_category)
311 OR ( (Recinfo.attribute_category IS NULL)
312 AND (X_attribute_category IS NULL)))
313 AND ( (Recinfo.attribute1 = X_attribute1)
314 OR ( (Recinfo.attribute1 IS NULL)
315 AND (X_attribute1 IS NULL)))
316 AND ( (Recinfo.attribute2 = X_attribute2)
317 OR ( (Recinfo.attribute2 IS NULL)
318 AND (X_attribute2 IS NULL)))
319 AND ( (Recinfo.attribute3 = X_attribute3)
320 OR ( (Recinfo.attribute3 IS NULL)
321 AND (X_attribute3 IS NULL)))
322 AND ( (Recinfo.attribute4 = X_attribute4)
323 OR ( (Recinfo.attribute4 IS NULL)
324 AND (X_attribute4 IS NULL)))
325 AND ( (Recinfo.attribute5 = X_attribute5)
326 OR ( (Recinfo.attribute5 IS NULL)
327 AND (X_attribute5 IS NULL)))
328 AND ( (Recinfo.attribute6 = X_attribute6)
329 OR ( (Recinfo.attribute6 IS NULL)
330 AND (X_attribute6 IS NULL)))
331 AND ( (Recinfo.attribute7 = X_attribute7)
332 OR ( (Recinfo.attribute7 IS NULL)
333 AND (X_attribute7 IS NULL)))
334 AND ( (Recinfo.attribute8 = X_attribute8)
335 OR ( (Recinfo.attribute8 IS NULL)
336 AND (X_attribute8 IS NULL)))
337 AND ( (Recinfo.attribute9 = X_attribute9)
338 OR ( (Recinfo.attribute9 IS NULL)
339 AND (X_attribute9 IS NULL)))
340 AND ( (Recinfo.attribute10 = X_attribute10)
341 OR ( (Recinfo.attribute10 IS NULL)
342 AND (X_attribute10 IS NULL)))
343 AND ( (Recinfo.attribute11 = X_attribute11)
344 OR ( (Recinfo.attribute11 IS NULL)
345 AND (X_attribute11 IS NULL)))
346 AND ( (Recinfo.attribute12 = X_attribute12)
347 OR ( (Recinfo.attribute12 IS NULL)
348 AND (X_attribute12 IS NULL)))
349 AND ( (Recinfo.attribute13 = X_attribute13)
350 OR ( (Recinfo.attribute13 IS NULL)
351 AND (X_attribute13 IS NULL)))
352 AND ( (Recinfo.attribute14 = X_attribute14)
353 OR ( (Recinfo.attribute14 IS NULL)
354 AND (X_attribute14 IS NULL)))
355 AND ( (Recinfo.attribute15 = X_attribute15)
356 OR ( (Recinfo.attribute15 IS NULL)
357 AND (X_attribute15 IS NULL)))
358 ) then
359 return;
360 else
361 FND_MESSAGE.Set_name('FND', 'FORM_RECORD_CHANGED');
362 APP_EXCEPTION.Raise_Exception;
363 end if;
364 END Lock_Row;
365
366 --
367 -- Procedure
368 -- Update_Row
369 -- Purpose
370 -- To update ce_forecast_headers with changes made.
371 -- History
372 -- 07.10.96 C. Kawamoto Created
373 -- 07.22.97 W. Chan Add logic for overdue_transaction and
374 -- cutoff_period update
375 -- Example
376 -- CE_FORECAST_HEADERS_PKG.Update_Row(...);
377 -- Notes
378 --
379 PROCEDURE Update_Row(
380 X_rowid VARCHAR2,
381 X_forecast_header_id NUMBER,
382 X_name VARCHAR2,
383 X_description VARCHAR2,
384 X_aging_type VARCHAR2,
385 X_overdue_transactions VARCHAR2,
386 X_cutoff_period NUMBER,
387 X_transaction_calendar_id NUMBER,
388 X_start_project_id NUMBER,
389 X_end_project_id NUMBER,
390 X_created_by NUMBER,
391 X_creation_date DATE,
392 X_last_updated_by NUMBER,
393 X_last_update_date DATE,
394 X_last_update_login NUMBER,
395 X_attribute_category VARCHAR2,
396 X_attribute1 VARCHAR2,
397 X_attribute2 VARCHAR2,
398 X_attribute3 VARCHAR2,
399 X_attribute4 VARCHAR2,
400 X_attribute5 VARCHAR2,
401 X_attribute6 VARCHAR2,
402 X_attribute7 VARCHAR2,
403 X_attribute8 VARCHAR2,
404 X_attribute9 VARCHAR2,
405 X_attribute10 VARCHAR2,
406 X_attribute11 VARCHAR2,
407 X_attribute12 VARCHAR2,
408 X_attribute13 VARCHAR2,
409 X_attribute14 VARCHAR2,
410 X_attribute15 VARCHAR2
411 ) IS
412 p_row_id VARCHAR2(100);
413 p_overdue_transactions VARCHAR2(30);
414 p_cutoff_period NUMBER;
415 p_forecast_column_id NUMBER;
416 BEGIN
417
418 SELECT overdue_transactions, cutoff_period
419 INTO p_overdue_transactions, p_cutoff_period
420 FROM CE_FORECAST_HEADERS
421 WHERE rowid = X_rowid;
422
423 UPDATE ce_forecast_headers
424 SET
425 forecast_header_id = X_forecast_header_id,
426 name = X_name,
427 description = X_description,
428 aging_type = X_aging_type,
429 overdue_transactions = X_overdue_transactions,
430 cutoff_period = X_cutoff_period,
431 transaction_calendar_id = X_transaction_calendar_id,
432 start_project_id = X_start_project_id,
433 end_project_id = X_end_project_id,
434 attribute_category = X_attribute_category,
435 attribute1 = X_attribute1,
436 attribute2 = X_attribute2,
437 attribute3 = X_attribute3,
438 attribute4 = X_attribute4,
439 attribute5 = X_attribute5,
440 attribute6 = X_attribute6,
441 attribute7 = X_attribute7,
442 attribute8 = X_attribute8,
443 attribute9 = X_attribute9,
444 attribute10 = X_attribute10,
445 attribute11 = X_attribute11,
446 attribute12 = X_attribute12,
447 attribute13 = X_attribute13,
448 attribute14 = X_attribute14,
449 attribute15 = X_attribute15
450 WHERE rowid = X_rowid;
451 if (SQL%NOTFOUND) then
452 Raise NO_DATA_FOUND;
453 end if;
454
455 --
459 IF(p_overdue_transactions = 'EXCLUDE' AND
456 -- If overdue_transactions is updated from 'N' to 'Y', insert
457 -- new column for overdue transactions in column table
458 --
460 X_overdue_transactions = 'INCLUDE')THEN
461 CE_FORECAST_COLUMNS_PKG.insert_row(
462 X_rowid => p_row_id,
463 X_forecast_column_id => p_forecast_column_id,
464 X_forecast_header_id => X_forecast_header_id,
465 X_column_number => 0,
466 X_days_from => -X_cutoff_period+1,
467 X_days_to => 0,
468 X_developer_column_num => 0,
469 X_created_by => X_created_by,
470 X_creation_date => X_creation_date,
471 X_last_updated_by => X_last_updated_by,
472 X_last_update_date => X_last_update_date,
473 X_last_update_login => X_last_update_login,
474 X_attribute_category => null,
475 X_attribute1 => null,
476 X_attribute2 => null,
477 X_attribute3 => null,
478 X_attribute4 => null,
479 X_attribute5 => null,
480 X_attribute6 => null,
481 X_attribute7 => null,
482 X_attribute8 => null,
483 X_attribute9 => null,
484 X_attribute10 => null,
485 X_attribute11 => null,
486 X_attribute12 => null,
487 X_attribute13 => null,
488 X_attribute14 => null,
489 X_attribute15 => null);
490 END IF;
491
492 --
493 -- If cutoff_period is updated, update the column table accordingly
494 --
495 IF(p_overdue_transactions = 'INCLUDE' AND
496 X_overdue_transactions = 'INCLUDE' AND
497 p_cutoff_period <> X_cutoff_period) THEN
498 UPDATE CE_FORECAST_COLUMNS
499 SET days_from = -X_cutoff_period+1
500 WHERE forecast_header_id = X_forecast_header_id
501 AND column_number = 0
502 AND developer_column_num = 0;
503 END IF;
504
505 --
506 -- If overdue_transactions is updated from 'Y' to 'N', delete
507 -- the overdue column from the column table
508 --
509 IF(p_overdue_transactions = 'INCLUDE' AND
510 X_overdue_transactions = 'EXCLUDE')THEN
511 DELETE FROM CE_FORECAST_COLUMNS
512 WHERE forecast_header_id = X_forecast_header_id
513 AND column_number = 0
514 AND developer_column_num = 0;
515 IF(SQL%NOTFOUND)THEN
516 RAISE NO_DATA_FOUND;
517 END IF;
518 END IF;
519
520 END Update_Row;
521
522 --
523 -- Procedure
524 -- Delete_Row
525 -- Purpose
526 -- To delete a row from ce_forecast_headers.
527 -- History
528 -- 07.10.96 C. Kawamoto Created
529 -- Example
530 -- CE_FORECAST_HEADERS_PKG.Delete_Row(...);
531 -- Notes
532 --
533 PROCEDURE Delete_Row(X_rowid VARCHAR2) IS
534 p_forecast_header_id NUMBER;
535 BEGIN
536 --
537 -- delete all rows, columns, and headers for template
538 --
539 SELECT forecast_header_id
540 INTO p_forecast_header_id
541 FROM CE_FORECAST_HEADERS
542 WHERE rowid = X_rowid;
543
544 DELETE FROM CE_FORECAST_ROWS
545 WHERE forecast_header_id = p_forecast_header_id;
546
547 DELETE FROM CE_FORECAST_COLUMNS
548 WHERE forecast_header_id = p_forecast_header_id;
549
550 DELETE FROM ce_forecast_headers
551 WHERE rowid = X_rowid;
552 if (SQL%NOTFOUND) then
553 Raise NO_DATA_FOUND;
554 end if;
555 END Delete_Row;
556
557 PROCEDURE Delete_Forecasts(X_rowid VARCHAR2) IS
558 p_forecast_header_id NUMBER;
559 BEGIN
560 --
561 -- delete all forecasts and cells that belong to the template
562 --
563 SELECT forecast_header_id
564 INTO p_forecast_header_id
565 FROM CE_FORECAST_HEADERS
566 WHERE rowid = X_rowid;
567
568 DELETE FROM CE_FORECAST_CELLS
569 WHERE forecast_header_id = p_forecast_header_id;
570
571 DELETE FROM CE_FORECASTS
572 WHERE forecast_header_id = p_forecast_header_id;
573
574 END Delete_Forecasts;
575
576 END CE_FORECAST_HEADERS_PKG;