[Home] [Help]
PACKAGE BODY: APPS.CE_FORECAST_COLUMNS_PKG
Source
1 PACKAGE BODY CE_FORECAST_COLUMNS_PKG AS
2 /* $Header: cefcolb.pls 120.1 2002/11/12 21:19:39 bhchung ship $ */
3 --
4 -- Package
5 -- CE_FORECAST_COLUMNS_PKG
6 -- Purpose
7 -- To group all the procedures/functions for table handling of the
8 -- ce_forecast_columns 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 column inserted.
31 -- History
32 -- 07.10.96 C. Kawamoto Created
33 -- Example
34 -- CE_FORECAST_COLUMNS_PKG.Check_Unique(...)
35 -- Notes
36 --
37
38 PROCEDURE Check_Unique(
39 X_rowid VARCHAR2,
40 X_forecast_header_id NUMBER,
41 X_column_number NUMBER) IS
42 CURSOR chk_duplicates IS
43 SELECT 'Duplicate'
44 FROM ce_forecast_columns cfc
45 WHERE cfc.forecast_header_id = X_forecast_header_id
46 AND cfc.column_number = X_column_number
47 AND (X_rowid IS NULL
48 OR cfc.rowid <> chartorowid(X_rowid));
49 dummy VARCHAR2(100);
50 BEGIN
51 OPEN chk_duplicates;
52 FETCH chk_duplicates INTO dummy;
53
54 IF chk_duplicates%FOUND THEN
55 FND_MESSAGE.Set_Name('CE', 'CE_DUPLICATE_COLUMN_NUMBER');
56 APP_EXCEPTION.Raise_exception;
57 END IF;
58 CLOSE chk_duplicates;
59
60 EXCEPTION
61 WHEN APP_EXCEPTIONS.application_exception THEN
62 RAISE;
63 WHEN OTHERS THEN
64 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
65 FND_MESSAGE.Set_Token('PROCEDURE', 'ce_cf_columns_pkg.check_unique');
66 RAISE;
67 END Check_Unique;
68
69 --
70 -- Procedure
71 -- Check_Unique_Aging
72 -- Purpose
73 -- Checks the uniqueness of the forecast agings inserted.
74 -- History
75 -- 07.10.96 C. Kawamoto Created
76 -- Example
77 -- CE_FORECAST_COLUMNS_PKG.Check_Unique_Aging(...)
78 -- Notes
79 --
80
81 PROCEDURE Check_Unique_Aging(
82 X_rowid VARCHAR2,
83 X_forecast_header_id NUMBER,
84 X_days_from NUMBER,
85 X_days_to NUMBER) IS
86 CURSOR chk_duplicate_agings IS
87 SELECT 'Duplicate'
88 FROM ce_forecast_columns cfc
89 WHERE cfc.forecast_header_id = X_forecast_header_id
90 AND cfc.days_from = X_days_from
91 AND cfc.days_to = X_days_to
92 AND (X_rowid IS NULL
93 OR cfc.rowid <> chartorowid(X_rowid));
94 dummy VARCHAR2(100);
95 BEGIN
96 OPEN chk_duplicate_agings;
97 FETCH chk_duplicate_agings INTO dummy;
98
99 IF chk_duplicate_agings%FOUND THEN
100 FND_MESSAGE.Set_Name('CE', 'CE_DUPLICATE_FORECAST_AGING');
101 APP_EXCEPTION.Raise_exception;
102 END IF;
103 CLOSE chk_duplicate_agings;
104
105 EXCEPTION
106 WHEN APP_EXCEPTIONS.application_exception THEN
107 RAISE;
108 WHEN OTHERS THEN
109 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
110 FND_MESSAGE.Set_Token('PROCEDURE', 'ce_cf_columns_pkg.check_unique_aging');
111 RAISE;
112 END Check_Unique_Aging;
113
114
115 --
116 -- Procedure
117 -- insert_row
118 -- Purpose
119 -- To insert new row to ce_forecast_columns.
120 -- History
121 -- 07.10.96 C. Kawamoto Created
122 -- Example
123 -- CE_FORECAST_COLUMNS_PKG.Insert_Row(...)
124 -- Notes
125 --
126
127 PROCEDURE Insert_Row(
128 X_rowid IN OUT NOCOPY VARCHAR2,
129 X_forecast_column_id IN OUT NOCOPY NUMBER,
130 X_forecast_header_id NUMBER,
131 X_column_number NUMBER,
132 X_days_from NUMBER,
133 X_days_to NUMBER,
134 X_developer_column_num NUMBER,
135 X_created_by NUMBER,
136 X_creation_date DATE,
137 X_last_updated_by NUMBER,
138 X_last_update_date DATE,
139 X_last_update_login NUMBER,
140 X_attribute_category VARCHAR2,
141 X_attribute1 VARCHAR2,
142 X_attribute2 VARCHAR2,
143 X_attribute3 VARCHAR2,
144 X_attribute4 VARCHAR2,
145 X_attribute5 VARCHAR2,
146 X_attribute6 VARCHAR2,
147 X_attribute7 VARCHAR2,
148 X_attribute8 VARCHAR2,
149 X_attribute9 VARCHAR2,
150 X_attribute10 VARCHAR2,
151 X_attribute11 VARCHAR2,
152 X_attribute12 VARCHAR2,
153 X_attribute13 VARCHAR2,
154 X_attribute14 VARCHAR2,
155 X_attribute15 VARCHAR2
156 ) IS
157 CURSOR C IS SELECT rowid FROM ce_forecast_columns
158 WHERE forecast_column_id = TO_NUMBER(X_forecast_column_id);
159 CURSOR C2 IS SELECT ce_forecast_columns_s.nextval FROM sys.dual;
160 BEGIN
161 OPEN C2;
162 FETCH C2 INTO X_forecast_column_id;
163 CLOSE C2;
164
165 INSERT INTO ce_forecast_columns(
166 forecast_column_id,
167 forecast_header_id,
168 column_number,
169 days_from,
170 days_to,
171 developer_column_num,
172 created_by,
173 creation_date,
174 last_updated_by,
175 last_update_date,
176 last_update_login,
177 attribute_category,
178 attribute1,
179 attribute2,
180 attribute3,
181 attribute4,
182 attribute5,
183 attribute6,
184 attribute7,
185 attribute8,
186 attribute9,
187 attribute10,
188 attribute11,
189 attribute12,
190 attribute13,
191 attribute14,
192 attribute15
193 ) VALUES (
194 X_forecast_column_id,
195 X_forecast_header_id,
196 X_column_number,
197 X_days_from,
198 X_days_to,
199 X_developer_column_num,
200 X_created_by,
201 X_creation_date,
202 X_last_updated_by,
203 X_last_update_date,
204 X_last_update_login,
205 X_attribute_category,
206 X_attribute1,
207 X_attribute2,
208 X_attribute3,
209 X_attribute4,
210 X_attribute5,
211 X_attribute6,
212 X_attribute7,
213 X_attribute8,
214 X_attribute9,
215 X_attribute10,
216 X_attribute11,
217 X_attribute12,
218 X_attribute13,
219 X_attribute14,
220 X_attribute15
221 );
222 OPEN C;
223 FETCH C INTO X_rowid;
224 if (C%NOTFOUND) then
225 CLOSE C;
226 Raise NO_DATA_FOUND;
227 end if;
228 CLOSE C;
229 END Insert_Row;
230 --
231 -- Procedure
232 -- lock_row
233 -- Purpose
234 -- To lock a row from ce_forecast_columns.
235 -- History
236 -- 07.10.96 C. Kawamoto Created
237 -- Example
238 -- CE_FORECAST_COLUMNS_PKG.Lock_Row(...)
239 -- Notes
240 --
241
242 PROCEDURE Lock_Row(
243 X_rowid VARCHAR2,
244 X_forecast_column_id NUMBER,
245 X_forecast_header_id NUMBER,
246 X_column_number NUMBER,
247 X_days_from NUMBER,
248 X_days_to NUMBER,
249 X_developer_column_num NUMBER,
250 X_created_by NUMBER,
251 X_creation_date DATE,
252 X_last_updated_by NUMBER,
253 X_last_update_date DATE,
254 X_last_update_login NUMBER,
255 X_attribute_category VARCHAR2,
256 X_attribute1 VARCHAR2,
257 X_attribute2 VARCHAR2,
258 X_attribute3 VARCHAR2,
259 X_attribute4 VARCHAR2,
260 X_attribute5 VARCHAR2,
261 X_attribute6 VARCHAR2,
262 X_attribute7 VARCHAR2,
263 X_attribute8 VARCHAR2,
264 X_attribute9 VARCHAR2,
265 X_attribute10 VARCHAR2,
266 X_attribute11 VARCHAR2,
267 X_attribute12 VARCHAR2,
268 X_attribute13 VARCHAR2,
269 X_attribute14 VARCHAR2,
270 X_attribute15 VARCHAR2
271 ) IS
272 CURSOR C IS
273 SELECT *
274 FROM ce_forecast_columns
275 WHERE rowid = X_rowid
276 FOR UPDATE of forecast_column_id NOWAIT;
277 Recinfo C%ROWTYPE;
278 BEGIN
279 OPEN C;
280 FETCH C INTO recinfo;
281 if (C%NOTFOUND) then
282 CLOSE C;
283 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
284 APP_EXCEPTION.Raise_Exception;
285 end if;
286 CLOSE C;
287 if (
288 (Recinfo.forecast_column_id = X_forecast_column_id)
289 AND (Recinfo.forecast_header_id = X_forecast_header_id)
290 AND (Recinfo.column_number = X_column_number)
291 AND (Recinfo.days_from = X_days_from)
292 AND (Recinfo.days_to = X_days_to)
293 AND ( (Recinfo.developer_column_num = X_developer_column_num)
294 OR ( (Recinfo.developer_column_num IS NULL)
295 AND (X_developer_column_num IS NULL)))
296 AND ( (Recinfo.attribute_category = X_attribute_category)
297 OR ( (Recinfo.attribute_category IS NULL)
298 AND (X_attribute_category IS NULL)))
299 AND ( (Recinfo.attribute1 = X_attribute1)
300 OR ( (Recinfo.attribute1 IS NULL)
301 AND (X_attribute1 IS NULL)))
302 AND ( (Recinfo.attribute2 = X_attribute2)
303 OR ( (Recinfo.attribute2 IS NULL)
304 AND (X_attribute2 IS NULL)))
305 AND ( (Recinfo.attribute3 = X_attribute3)
306 OR ( (Recinfo.attribute3 IS NULL)
307 AND (X_attribute3 IS NULL)))
308 AND ( (Recinfo.attribute4 = X_attribute4)
309 OR ( (Recinfo.attribute4 IS NULL)
310 AND (X_attribute4 IS NULL)))
311 AND ( (Recinfo.attribute5 = X_attribute5)
312 OR ( (Recinfo.attribute5 IS NULL)
313 AND (X_attribute5 IS NULL)))
314 AND ( (Recinfo.attribute6 = X_attribute6)
315 OR ( (Recinfo.attribute6 IS NULL)
316 AND (X_attribute6 IS NULL)))
317 AND ( (Recinfo.attribute7 = X_attribute7)
318 OR ( (Recinfo.attribute7 IS NULL)
319 AND (X_attribute7 IS NULL)))
320 AND ( (Recinfo.attribute8 = X_attribute8)
321 OR ( (Recinfo.attribute8 IS NULL)
322 AND (X_attribute8 IS NULL)))
323 AND ( (Recinfo.attribute9 = X_attribute9)
324 OR ( (Recinfo.attribute9 IS NULL)
325 AND (X_attribute9 IS NULL)))
326 AND ( (Recinfo.attribute10 = X_attribute10)
327 OR ( (Recinfo.attribute10 IS NULL)
328 AND (X_attribute10 IS NULL)))
329 AND ( (Recinfo.attribute11 = X_attribute11)
330 OR ( (Recinfo.attribute11 IS NULL)
331 AND (X_attribute11 IS NULL)))
332 AND ( (Recinfo.attribute12 = X_attribute12)
333 OR ( (Recinfo.attribute12 IS NULL)
334 AND (X_attribute12 IS NULL)))
335 AND ( (Recinfo.attribute13 = X_attribute13)
336 OR ( (Recinfo.attribute13 IS NULL)
337 AND (X_attribute13 IS NULL)))
338 AND ( (Recinfo.attribute14 = X_attribute14)
339 OR ( (Recinfo.attribute14 IS NULL)
340 AND (X_attribute14 IS NULL)))
341 AND ( (Recinfo.attribute15 = X_attribute15)
342 OR ( (Recinfo.attribute15 IS NULL)
343 AND (X_attribute15 IS NULL)))
344 ) then
345 return;
346 else
347 FND_MESSAGE.Set_name('FND', 'FORM_RECORD_CHANGED');
348 APP_EXCEPTION.Raise_Exception;
349 end if;
350 END Lock_Row;
351
352 --
353 -- Procedure
354 -- Update_Row
355 -- Purpose
356 -- To update ce_forecast_columns with changes made.
357 -- History
358 -- 07.10.96 C. Kawamoto Created
359 -- Example
360 -- CE_FORECAST_COLUMNS_PKG.Update_Row(...);
361 -- Notes
362 --
363 PROCEDURE Update_Row(
364 X_rowid VARCHAR2,
365 X_forecast_column_id NUMBER,
366 X_forecast_header_id NUMBER,
367 X_column_number NUMBER,
368 X_days_from NUMBER,
369 X_days_to NUMBER,
370 X_developer_column_num NUMBER,
371 X_created_by NUMBER,
372 X_creation_date DATE,
373 X_last_updated_by NUMBER,
374 X_last_update_date DATE,
375 X_last_update_login NUMBER,
376 X_attribute_category VARCHAR2,
377 X_attribute1 VARCHAR2,
378 X_attribute2 VARCHAR2,
379 X_attribute3 VARCHAR2,
380 X_attribute4 VARCHAR2,
381 X_attribute5 VARCHAR2,
382 X_attribute6 VARCHAR2,
383 X_attribute7 VARCHAR2,
384 X_attribute8 VARCHAR2,
385 X_attribute9 VARCHAR2,
386 X_attribute10 VARCHAR2,
387 X_attribute11 VARCHAR2,
388 X_attribute12 VARCHAR2,
389 X_attribute13 VARCHAR2,
390 X_attribute14 VARCHAR2,
391 X_attribute15 VARCHAR2
392 ) IS
393 BEGIN
394 UPDATE ce_forecast_columns
395 SET
396 forecast_column_id = X_forecast_column_id,
397 forecast_header_id = X_forecast_header_id,
398 column_number = X_column_number,
399 days_from = X_days_from,
400 days_to = X_days_to,
401 developer_column_num = X_developer_column_num,
402 attribute_category = X_attribute_category,
403 attribute1 = X_attribute1,
404 attribute2 = X_attribute2,
405 attribute3 = X_attribute3,
406 attribute4 = X_attribute4,
407 attribute5 = X_attribute5,
408 attribute6 = X_attribute6,
409 attribute7 = X_attribute7,
410 attribute8 = X_attribute8,
411 attribute9 = X_attribute9,
412 attribute10 = X_attribute10,
413 attribute11 = X_attribute11,
414 attribute12 = X_attribute12,
415 attribute13 = X_attribute13,
416 attribute14 = X_attribute14,
417 attribute15 = X_attribute15
418 WHERE rowid = X_rowid;
419 if (SQL%NOTFOUND) then
420 Raise NO_DATA_FOUND;
421 end if;
422 END Update_Row;
423
424 --
425 -- Procedure
426 -- Delete_Row
427 -- Purpose
428 -- To delete a row from ce_forecast_columns.
429 -- History
430 -- 07.10.96 C. Kawamoto Created
431 -- Example
432 -- CE_FORECAST_COLUMNS_PKG.Delete_Row(...);
433 -- Notes
434 --
435 PROCEDURE Delete_Row(X_rowid VARCHAR2) IS
436 BEGIN
437 DELETE FROM ce_forecast_columns
438 WHERE rowid = X_rowid;
439 if (SQL%NOTFOUND) then
440 Raise NO_DATA_FOUND;
441 end if;
442 END Delete_Row;
443
444 PROCEDURE Delete_Forecast_Cells(X_rowid VARCHAR2) IS
445 p_forecast_header_id NUMBER;
446 p_forecast_column_id NUMBER;
447 BEGIN
448 --
449 -- delete all forecast cells that belong to the template column
450 --
451 SELECT forecast_header_id, forecast_column_id
452 INTO p_forecast_header_id, p_forecast_column_id
453 FROM CE_FORECAST_COLUMNS
454 WHERE rowid = X_rowid;
455
456 DELETE FROM CE_FORECAST_CELLS
457 WHERE forecast_header_id = p_forecast_header_id AND
458 forecast_column_id = p_forecast_column_id;
459
460 END Delete_Forecast_Cells;
461
462
463 END CE_FORECAST_COLUMNS_PKG;