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