[Home] [Help]
PACKAGE BODY: APPS.CE_FORECASTS_TABLE_PKG
Source
1 PACKAGE BODY CE_FORECASTS_TABLE_PKG AS
2 /* $Header: ceforcab.pls 120.3 2003/05/12 23:14:52 sspoonen ship $ */
3 FUNCTION body_revision RETURN VARCHAR2 IS
4 BEGIN
5
6 RETURN '$Revision: 120.3 $';
7
8 END body_revision;
9
10 FUNCTION spec_revision RETURN VARCHAR2 IS
11 BEGIN
12
13 RETURN G_spec_revision;
14
15 END spec_revision;
16
17 PROCEDURE Insert_Row( X_Rowid IN OUT NOCOPY VARCHAR2,
18 X_forecast_id IN OUT NOCOPY NUMBER,
19 X_forecast_header_id NUMBER,
20 X_name VARCHAR2,
21 X_description VARCHAR2,
22 X_start_date DATE,
23 X_period_set_name VARCHAR2,
24 X_start_period VARCHAR2,
25 X_forecast_currency VARCHAR2,
26 X_currency_type VARCHAR2,
27 X_source_currency VARCHAR2,
28 X_exchange_rate_type VARCHAR2,
29 X_exchange_date DATE,
30 X_exchange_rate NUMBER,
31 X_error_status VARCHAR2,
32 X_amount_threshold NUMBER,
33 X_project_id NUMBER,
34 X_drilldown_flag VARCHAR2,
35 X_bank_balance_type VARCHAR2,
36 X_float_type VARCHAR2,
37 X_view_by VARCHAR2,
38 X_include_sub_account VARCHAR2,
39 X_factor NUMBER,
40 X_request_id NUMBER,
41 X_Created_By NUMBER,
42 X_Creation_Date DATE,
43 X_Last_Updated_By NUMBER,
44 X_Last_Update_Date DATE,
45 X_Last_Update_Login NUMBER,
46 X_attribute_category VARCHAR2,
47 X_attribute1 VARCHAR2,
48 X_attribute2 VARCHAR2,
49 X_attribute3 VARCHAR2,
50 X_attribute4 VARCHAR2,
51 X_attribute5 VARCHAR2,
52 X_attribute6 VARCHAR2,
53 X_attribute7 VARCHAR2,
54 X_attribute8 VARCHAR2,
55 X_attribute9 VARCHAR2,
56 X_attribute10 VARCHAR2,
57 X_attribute11 VARCHAR2,
58 X_attribute12 VARCHAR2,
59 X_attribute13 VARCHAR2,
60 X_attribute14 VARCHAR2,
61 X_attribute15 VARCHAR2) IS
62 CURSOR C1 IS SELECT rowid
63 FROM CE_FORECASTS
64 WHERE forecast_id = X_forecast_id;
65 CURSOR C2 IS SELECT CE_FORECASTS_S.nextval FROM sys.dual;
66 BEGIN
67 IF (X_forecast_id IS NULL) THEN
68 OPEN C2;
69 FETCH C2 INTO X_forecast_id;
70 CLOSE C2;
71 END IF;
72 INSERT INTO CE_FORECASTS(
73 forecast_id,
74 forecast_header_id,
75 name,
76 description,
77 start_date,
78 period_set_name,
79 start_period,
80 forecast_currency,
81 currency_type,
82 source_currency,
83 exchange_rate_type,
84 exchange_date,
85 exchange_rate,
86 error_status,
87 amount_threshold,
88 project_id,
89 drilldown_flag,
90 bank_balance_type,
91 float_type,
92 view_by,
93 include_sub_account,
94 factor,
95 request_id,
96 Created_By,
97 Creation_Date,
98 Last_Updated_By,
99 Last_Update_Date,
100 Last_Update_Login,
101 attribute_category,
102 attribute1,
103 attribute2,
104 attribute3,
105 attribute4,
106 attribute5,
107 attribute6,
108 attribute7,
109 attribute8,
110 attribute9,
111 attribute10,
112 attribute11,
113 attribute12,
114 attribute13,
115 attribute14,
116 attribute15)
117 VALUES
118 ( X_forecast_id,
119 X_forecast_header_id,
120 X_name,
121 X_description,
122 X_start_date,
123 X_period_set_name,
124 X_start_period,
125 X_forecast_currency,
126 X_currency_type,
127 X_source_currency,
128 X_exchange_rate_type,
129 X_exchange_date,
130 X_exchange_rate,
131 X_error_status,
132 X_amount_threshold,
133 X_project_id,
134 X_drilldown_flag,
135 X_bank_balance_type,
136 X_float_type,
137 X_view_by,
138 X_include_sub_account,
139 X_factor,
140 X_request_id,
141 X_Created_By ,
142 X_Creation_Date,
143 X_Last_Updated_By,
144 X_Last_Update_Date,
145 X_Last_Update_Login,
146 X_attribute_category,
147 X_attribute1,
148 X_attribute2,
149 X_attribute3,
150 X_attribute4,
151 X_attribute5,
152 X_attribute6,
153 X_attribute7,
154 X_attribute8,
155 X_attribute9,
156 X_attribute10,
157 X_attribute11,
158 X_attribute12,
159 X_attribute13,
160 X_attribute14,
161 X_attribute15) ;
162 OPEN C1;
163 FETCH C1 INTO X_rowid;
164 IF (C1%NOTFOUND) THEN
165 CLOSE C1;
166 Raise NO_DATA_FOUND;
167 END IF;
168 CLOSE C1;
169 END Insert_row;
170
171 PROCEDURE Update_Row( X_rowid VARCHAR2,
172 X_forecast_id NUMBER,
173 X_forecast_header_id NUMBER,
174 X_name VARCHAR2,
175 X_description VARCHAR2,
176 X_start_date DATE,
177 X_period_set_name VARCHAR2,
178 X_start_period VARCHAR2,
179 X_forecast_currency VARCHAR2,
180 X_currency_type VARCHAR2,
181 X_source_currency VARCHAR2,
182 X_exchange_rate_type VARCHAR2,
183 X_exchange_date DATE,
184 X_exchange_rate NUMBER,
185 X_error_status VARCHAR2,
186 X_amount_threshold NUMBER,
187 X_project_id NUMBER,
188 X_Last_Updated_By NUMBER,
189 X_Last_Update_Date DATE,
190 X_Last_Update_Login NUMBER,
191 X_attribute_category VARCHAR2,
192 X_attribute1 VARCHAR2,
193 X_attribute2 VARCHAR2,
194 X_attribute3 VARCHAR2,
195 X_attribute4 VARCHAR2,
196 X_attribute5 VARCHAR2,
197 X_attribute6 VARCHAR2,
198 X_attribute7 VARCHAR2,
199 X_attribute8 VARCHAR2,
200 X_attribute9 VARCHAR2,
201 X_attribute10 VARCHAR2,
202 X_attribute11 VARCHAR2,
203 X_attribute12 VARCHAR2,
204 X_attribute13 VARCHAR2,
205 X_attribute14 VARCHAR2,
206 X_attribute15 VARCHAR2) IS
207 BEGIN
208 UPDATE CE_FORECASTS
209 SET
210 forecast_id = X_forecast_id,
211 forecast_header_id = X_forecast_header_id,
212 name = X_name,
213 description = X_description,
214 start_date = X_start_date,
215 period_set_name = X_period_set_name,
216 start_period = X_start_period,
217 forecast_currency = X_forecast_currency,
218 currency_type = X_currency_type,
219 source_currency = X_source_currency,
220 exchange_rate_type = X_exchange_rate_type,
221 exchange_date = X_exchange_date,
222 exchange_rate = X_exchange_rate,
223 error_status = X_error_status,
224 amount_threshold = X_amount_threshold,
225 project_id = X_project_id,
226 Last_Updated_By = X_Last_Updated_By,
227 Last_Update_Date = X_Last_Update_Date,
228 Last_Update_Login = X_Last_Update_Login,
229 attribute_category = X_attribute_category,
230 attribute1 = X_attribute1,
231 attribute2 = X_attribute2,
232 attribute3 = X_attribute3,
233 attribute4 = X_attribute4,
234 attribute5 = X_attribute5,
235 attribute6 = X_attribute6,
236 attribute7 = X_attribute7,
237 attribute8 = X_attribute8,
238 attribute9 = X_attribute9,
239 attribute10 = X_attribute10,
240 attribute11 = X_attribute11,
241 attribute12 = X_attribute12,
242 attribute13 = X_attribute13,
243 attribute14 = X_attribute14,
244 attribute15 = X_attribute15
245 WHERE rowid = X_rowid;
246
247 IF (SQL%NOTFOUND) THEN
248 Raise NO_DATA_FOUND;
249 END IF;
250 END Update_Row;
251
252
253 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
254 p_forecast_id NUMBER;
255 BEGIN
256 --
257 -- delete all cells belongs to the forecast
258 --
259 SELECT forecast_id
260 INTO p_forecast_id
261 FROM CE_FORECASTS
262 WHERE rowid = X_Rowid;
263
264 DELETE FROM CE_FORECAST_ERRORS
265 WHERE forecast_id = p_forecast_id;
266
267 DELETE FROM CE_FORECAST_CELLS
268 WHERE forecast_id = p_forecast_id;
269
270 DELETE FROM CE_FORECASTS
271 WHERE rowid = X_Rowid;
272
273 IF (SQL%NOTFOUND) THEN
274 RAISE NO_DATA_FOUND;
275 END IF;
276 END Delete_Row;
277
278 PROCEDURE Lock_Row ( X_RowId VARCHAR2,
279 X_forecast_id NUMBER,
280 X_forecast_header_id NUMBER,
281 X_name VARCHAR2,
282 X_description VARCHAR2,
283 X_start_date DATE,
284 X_period_set_name VARCHAR2,
285 X_start_period VARCHAR2,
286 X_forecast_currency VARCHAR2,
287 X_currency_type VARCHAR2,
288 X_source_currency VARCHAR2,
289 X_exchange_rate_type VARCHAR2,
290 X_exchange_date DATE,
291 X_exchange_rate NUMBER,
292 X_error_status VARCHAR2,
293 X_amount_threshold NUMBER,
294 X_project_id NUMBER,
295 X_attribute_category VARCHAR2,
296 X_attribute1 VARCHAR2,
297 X_attribute2 VARCHAR2,
298 X_attribute3 VARCHAR2,
299 X_attribute4 VARCHAR2,
300 X_attribute5 VARCHAR2,
301 X_attribute6 VARCHAR2,
302 X_attribute7 VARCHAR2,
303 X_attribute8 VARCHAR2,
304 X_attribute9 VARCHAR2,
305 X_attribute10 VARCHAR2,
306 X_attribute11 VARCHAR2,
307 X_attribute12 VARCHAR2,
308 X_attribute13 VARCHAR2,
309 X_attribute14 VARCHAR2,
310 X_attribute15 VARCHAR2) IS
311 CURSOR C IS
312 SELECT *
313 FROM CE_FORECASTS
314 WHERE rowid = X_rowid
315 FOR UPDATE OF forecast_id NOWAIT;
316 Recinfo C%ROWTYPE;
317 BEGIN
318 OPEN C;
319 FETCH C INTO Recinfo;
320 IF (C%NOTFOUND) THEN
321 CLOSE C;
322 FND_MESSAGE.set_name('FND', 'FORM_RECORD_DELETED');
323 APP_EXCEPTION.raise_exception;
324 END IF;
325 CLOSE C;
326 IF(
327 (Recinfo.forecast_id = X_forecast_id )
328 AND (Recinfo.name = X_name )
329 AND (Recinfo.forecast_currency = X_forecast_currency )
330 AND (Recinfo.currency_type = X_currency_type )
331 AND ( ( (Recinfo.exchange_rate_type = X_exchange_rate_type )
332 OR ( (Recinfo.exchange_rate_type IS NULL)
333 AND (X_exchange_rate_type IS NULL))))
334 AND ( ( (Recinfo.exchange_date = X_exchange_date )
335 OR ( (Recinfo.exchange_date IS NULL)
336 AND (X_exchange_date IS NULL))))
337 AND (Recinfo.forecast_header_id = X_forecast_header_id )
338 AND ( ( (Recinfo.description = X_description )
339 OR ( (Recinfo.description IS NULL)
340 AND (X_description IS NULL))))
341 AND ( ( (Recinfo.start_date = X_start_date )
342 OR ( (Recinfo.start_date IS NULL)
343 AND (X_start_date IS NULL))))
344 AND ( ( (Recinfo.start_period = X_start_period )
345 OR ( (Recinfo.start_period IS NULL)
346 AND (X_start_period IS NULL))))
347 AND ( ( (Recinfo.project_id = X_project_id )
348 OR ( (Recinfo.project_id IS NULL)
349 AND (X_project_id IS NULL))))
350 AND ( ( (Recinfo.source_currency = X_source_currency )
351 OR ( (Recinfo.source_currency IS NULL)
352 AND (X_source_currency IS NULL))))
353 AND ( ( (Recinfo.error_status = X_error_status )
354 OR ( (Recinfo.error_status IS NULL)
355 AND (X_error_status IS NULL))))
356 AND ( ( (Recinfo.attribute_category = X_attribute_category )
357 OR ( (Recinfo.attribute_category IS NULL)
358 AND (X_attribute_category IS NULL))))
359 AND ( ( (Recinfo.attribute1 = X_attribute1 )
360 OR ( (Recinfo.attribute1 IS NULL)
361 AND (X_attribute1 IS NULL))))
362 AND ( ( (Recinfo.attribute2 = X_attribute2 )
363 OR ( (Recinfo.attribute2 IS NULL)
364 AND (X_attribute2 IS NULL))))
365 AND ( ( (Recinfo.attribute3 = X_attribute3 )
366 OR ( (Recinfo.attribute3 IS NULL)
367 AND (X_attribute3 IS NULL))))
368 AND ( ( (Recinfo.attribute4 = X_attribute4 )
369 OR ( (Recinfo.attribute4 IS NULL)
370 AND (X_attribute4 IS NULL))))
371 AND ( ( (Recinfo.attribute5 = X_attribute5 )
372 OR ( (Recinfo.attribute5 IS NULL)
373 AND (X_attribute5 IS NULL))))
374 AND ( ( (Recinfo.attribute6 = X_attribute6 )
375 OR ( (Recinfo.attribute6 IS NULL)
376 AND (X_attribute6 IS NULL))))
377 AND ( ( (Recinfo.attribute7 = X_attribute7 )
378 OR ( (Recinfo.attribute7 IS NULL)
379 AND (X_attribute7 IS NULL))))
380 AND ( ( (Recinfo.attribute8 = X_attribute8 )
381 OR ( (Recinfo.attribute8 IS NULL)
382 AND (X_attribute8 IS NULL))))
383 AND ( ( (Recinfo.attribute9 = X_attribute9 )
384 OR ( (Recinfo.attribute9 IS NULL)
385 AND (X_attribute9 IS NULL))))
386 AND ( ( (Recinfo.attribute10 = X_attribute10 )
387 OR ( (Recinfo.attribute10 IS NULL)
388 AND (X_attribute10 IS NULL))))
389 AND ( ( (Recinfo.attribute11 = X_attribute11 )
390 OR ( (Recinfo.attribute11 IS NULL)
391 AND (X_attribute11 IS NULL))))
392 AND ( ( (Recinfo.attribute12 = X_attribute12 )
393 OR ( (Recinfo.attribute12 IS NULL)
394 AND (X_attribute12 IS NULL))))
395 AND ( ( (Recinfo.attribute13 = X_attribute13 )
396 OR ( (Recinfo.attribute13 IS NULL)
397 AND (X_attribute13 IS NULL))))
398 AND ( ( (Recinfo.attribute14 = X_attribute14 )
399 OR ( (Recinfo.attribute14 IS NULL)
400 AND (X_attribute14 IS NULL))))
401 AND ( ( (Recinfo.attribute15 = X_attribute15 )
402 OR ( (Recinfo.attribute15 IS NULL)
403 AND (X_attribute15 IS NULL))))
404 AND ( ( (Recinfo.exchange_rate = X_exchange_rate )
405 OR ( (Recinfo.exchange_rate IS NULL)
406 AND (X_exchange_rate IS NULL))))
407 AND ( ( (Recinfo.period_set_name = X_period_set_name )
408 OR ( (Recinfo.period_set_name IS NULL)
409 AND (X_period_set_name IS NULL))))
410 AND ( ( (Recinfo.amount_threshold = X_amount_threshold )
411 OR ( (Recinfo.amount_threshold IS NULL)
412 AND (X_amount_threshold IS NULL))))
413 AND ( ( (Recinfo.project_id = X_project_id )
414 OR ( (Recinfo.project_id IS NULL)
415 AND (X_project_id IS NULL))))
416 ) THEN
417 return;
418 ELSE
419 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
420 APP_EXCEPTION.raise_exception;
421 END IF;
422 END Lock_Row;
423
424 END CE_FORECASTS_TABLE_PKG;