[Home] [Help]
PACKAGE BODY: APPS.OZF_ACTFORECAST_PVT
Source
1 PACKAGE BODY OZF_ACTFORECAST_PVT AS
2 /* $Header: ozfvfctb.pls 120.2 2005/07/29 02:53:12 appldev ship $ */
3
4 ---------------------------------------------------------------------------------------------------
5 --
6 -- NAME
7 -- OZF_ActForecast_PVT
8 --
9 -- HISTORY
10 -- 20-May-2000 tdonohoe@us Created package.
11 -- 15-Jun-2000 tdonohoe@us Modified package to include new FORECAST_TYPE column.
12 -- 15-Jun-2000 tdonohoe@us Modified package to check for valid FORECAST_UOM_CODE values.
13 -- 11-Jul-2005 inanaiah R12 changes for non-baseline basis
14 ---------------------------------------------------------------------------------------------------
15
16 --
17 -- Global variables and constants.
18
19 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_ACTFORECAST_PVT'; -- Name of the current package.
20 G_DEBUG_FLAG VARCHAR2(1) := 'N';
21
22 OZF_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
23 OZF_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
24 OZF_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
25
26
27 ---------------------------------------------------------------------
28 -- PROCEDURE
29 -- Init_ActForecast_rec
30 --
31 -- HISTORY
32 -- 05/15/2000 tdonohoe Created.
33 ---------------------------------------------------------------------
34 PROCEDURE Init_ActForecast_Rec(
35 x_actforecast_rec OUT NOCOPY act_forecast_rec_type
36 )
37 IS
38 BEGIN
39
40 x_actforecast_rec.forecast_id := FND_API.g_miss_num;
41 x_actforecast_rec.arc_act_fcast_used_by := FND_API.g_miss_char;
42 x_actforecast_rec.act_fcast_used_by_id := FND_API.g_miss_num;
43 x_actforecast_rec.creation_date := FND_API.g_miss_date;
44 x_actforecast_rec.created_from := FND_API.g_miss_char;
45 x_actforecast_rec.created_by := FND_API.g_miss_num;
46 x_actforecast_rec.last_update_date := FND_API.g_miss_date;
47 x_actforecast_rec.last_updated_by := FND_API.g_miss_num;
48 x_actforecast_rec.last_update_login := FND_API.g_miss_num;
49 x_actforecast_rec.program_application_id := FND_API.g_miss_num;
50 x_actforecast_rec.program_id := FND_API.g_miss_num;
51 x_actforecast_rec.program_update_date := FND_API.g_miss_date;
52 x_actforecast_rec.request_id := FND_API.g_miss_num;
53 x_actforecast_rec.object_version_number := FND_API.g_miss_num;
54 x_actforecast_rec.hierarchy := FND_API.g_miss_char;
55 x_actforecast_rec.hierarchy_level := FND_API.g_miss_char;
56 x_actforecast_rec.level_value := FND_API.g_miss_char;
57 x_actforecast_rec.forecast_calendar := FND_API.g_miss_char;
58 x_actforecast_rec.period_level := FND_API.g_miss_char;
59 x_actforecast_rec.forecast_period_id := FND_API.g_miss_num;
60 x_actforecast_rec.forecast_date := FND_API.g_miss_date;
61 x_actforecast_rec.forecast_uom_code := FND_API.g_miss_char;
62 x_actforecast_rec.forecast_quantity := FND_API.g_miss_num;
63 x_actforecast_rec.forward_buy_quantity := FND_API.g_miss_num;
64 x_actforecast_rec.forward_buy_period := FND_API.g_miss_char;
65 x_actforecast_rec.cumulation_period_choice := FND_API.g_miss_char;
66 x_actforecast_rec.base_quantity := FND_API.g_miss_num;
67 x_actforecast_rec.context := FND_API.g_miss_char;
68 x_actforecast_rec.attribute_category := FND_API.g_miss_char;
69 x_actforecast_rec.attribute1 := FND_API.g_miss_char;
70 x_actforecast_rec.attribute2 := FND_API.g_miss_char;
71 x_actforecast_rec.attribute3 := FND_API.g_miss_char;
72 x_actforecast_rec.attribute4 := FND_API.g_miss_char;
73 x_actforecast_rec.attribute5 := FND_API.g_miss_char;
74 x_actforecast_rec.attribute6 := FND_API.g_miss_char;
75 x_actforecast_rec.attribute7 := FND_API.g_miss_char;
76 x_actforecast_rec.attribute8 := FND_API.g_miss_char;
77 x_actforecast_rec.attribute9 := FND_API.g_miss_char;
78 x_actforecast_rec.attribute10 := FND_API.g_miss_char;
79 x_actforecast_rec.attribute11 := FND_API.g_miss_char;
80 x_actforecast_rec.attribute12 := FND_API.g_miss_char;
81 x_actforecast_rec.attribute13 := FND_API.g_miss_char;
82 x_actforecast_rec.attribute14 := FND_API.g_miss_char;
83 x_actforecast_rec.attribute15 := FND_API.g_miss_char;
84 x_actforecast_rec.org_id := FND_API.g_miss_num;
85 x_actforecast_rec.forecast_remaining_quantity := FND_API.g_miss_num;
86 x_actforecast_rec.forecast_remaining_percent := FND_API.g_miss_num;
87 x_actforecast_rec.base_quantity_type := FND_API.g_miss_char;
88 x_actforecast_rec.forecast_spread_type := FND_API.g_miss_char;
89 x_actforecast_rec.dimention1 := FND_API.g_miss_char;
90 x_actforecast_rec.dimention2 := FND_API.g_miss_char;
91 x_actforecast_rec.dimention3 := FND_API.g_miss_char;
92 x_actforecast_rec.last_scenario_id := FND_API.g_miss_num;
93 x_actforecast_rec.freeze_flag := FND_API.g_miss_char;
94 x_actforecast_rec.comments := FND_API.g_miss_char;
95 x_actforecast_rec.price_list_id := FND_API.g_miss_num;
96 x_actforecast_rec.base_quantity_start_date := FND_API.g_miss_date;
97 x_actforecast_rec.base_quantity_end_date := FND_API.g_miss_date;
98 x_actforecast_rec.base_quantity_ref := FND_API.g_miss_char;
99 x_actforecast_rec.offer_code:= FND_API.g_miss_char;
100
101 END;
102
103
104 -- Start of comments
105 -- NAME
106 -- Default_ActForecast
107 --
108 --
109 -- PURPOSE
110 -- Defaults the Activty Forecast.
111 --
112 -- NOTES
113 --
114 -- HISTORY
115 -- 27-Apr-2000 tdonohoe Created.
116 --
117 -- End of comments
118
119
120 PROCEDURE Default_ActForecast(
121 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
122 p_act_forecast_rec IN act_forecast_rec_type,
123 p_validation_mode IN VARCHAR2 ,
124 x_complete_rec OUT NOCOPY act_forecast_rec_type,
125 x_return_status OUT NOCOPY VARCHAR2,
126 x_msg_count OUT NOCOPY NUMBER,
127 x_msg_data OUT NOCOPY VARCHAR2
128 )
129 IS
130
131 BEGIN
132 --
133 -- Initialize message list if p_init_msg_list is set to TRUE.
134 --
135 IF FND_API.To_Boolean (p_init_msg_list) THEN
136 FND_MSG_PUB.Initialize;
137 END IF;
138
139 --
140 -- Initialize API return status to success.
141 --
142 x_return_status := FND_API.G_RET_STS_SUCCESS;
143
144 x_complete_rec := p_act_forecast_rec;
145
146 -- Insert Mode
147 IF ((p_validation_mode = JTF_PLSQL_API.g_create) OR (p_validation_mode = JTF_PLSQL_API.g_update)) THEN
148
149 IF p_act_forecast_rec.forecast_calendar IS NULL THEN
150 x_complete_rec.forecast_calendar := 'NONE';
151 END IF;
152
153 IF p_act_forecast_rec.base_quantity_type IS NULL THEN
154 x_complete_rec.base_quantity_type := 'LAST_YEAR_SAME_PERIOD';
155 END IF;
156
157 IF p_act_forecast_rec.forecast_spread_type IS NULL THEN
158 x_complete_rec.forecast_spread_type := 'BASELINE_RATIO';
159 END IF;
160
161 IF p_act_forecast_rec.forecast_type IS NULL THEN
162 x_complete_rec.forecast_type := 'NONE';
163 END IF;
164
165 IF p_act_forecast_rec.freeze_flag IS NULL THEN
166 x_complete_rec.freeze_flag := 'N';
167 END IF;
168
169 END IF;
170
171 END Default_ActForecast ;
172
173 -- Start of comments
174 -- NAME
175 -- Create_ActForecast
176 --
177 --
178 -- PURPOSE
179 -- Creates an Activity Forecast.
180
181 --
182 -- NOTES
183 --
184 -- HISTORY
185 -- 18-Apr-2000 tdonohoe@us Created.
186 -- 15-Jun-2000 tdonohoe Modified to include new column FORECAST_TYPE.
187 --
188 -- End of comments
189
190 PROCEDURE Create_ActForecast (
191 p_api_version IN NUMBER,
192 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
193 p_commit IN VARCHAR2 := FND_API.G_FALSE,
194 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
195
196 x_return_status OUT NOCOPY VARCHAR2,
197 x_msg_count OUT NOCOPY NUMBER,
198 x_msg_data OUT NOCOPY VARCHAR2,
199
200 p_act_forecast_rec IN act_forecast_rec_type,
201 x_forecast_id OUT NOCOPY NUMBER
202 )
203 IS
204 --
205 -- Standard API information constants.
206 --
207 L_API_VERSION CONSTANT NUMBER := 1.0;
208 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_ACTFORECAST';
209 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
210
211
212 l_return_status VARCHAR2(1); -- Return value from procedures.
213 l_act_forecast_rec act_forecast_rec_type := p_act_forecast_rec;
214 l_act_forecast_count NUMBER ;
215
216 l_sql_err_msg varchar2(4000);
217
218 CURSOR c_act_forecast_count(l_forecast_id IN NUMBER) IS
219 SELECT count(*)
220 FROM ozf_act_forecasts_all
221 WHERE forecast_id = l_forecast_id;
222
223 CURSOR c_act_forecast_id IS
224 SELECT ozf_act_forecasts_all_s.NEXTVAL
225 FROM dual;
226
227 BEGIN
228 --
229 -- Initialize savepoint.
230 --
231
232 SAVEPOINT Create_ActForecast_Pvt;
233
234 IF (OZF_DEBUG_HIGH_ON) THEN
235
236
237
238 OZF_Utility_PVT.Debug_Message(l_full_name||': start');
239
240 END IF;
241
242 --
243 -- Initialize message list if p_init_msg_list is set to TRUE.
244 --
245 IF FND_API.To_Boolean (p_init_msg_list) THEN
246 FND_MSG_PUB.Initialize;
247 END IF;
248
249 --
250 -- Standard check for API version compatibility.
251 --
252 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
253 p_api_version,
254 L_API_NAME,
255 G_PKG_NAME)
256 THEN
257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
258 END IF;
259
260 --
261 -- Initialize API return status to success.
262 --
263 x_return_status := FND_API.G_RET_STS_SUCCESS;
264
265 --
266 -- Begin API Body.
267 --
268
269 Default_ActForecast
270 ( p_init_msg_list => p_init_msg_list,
271 p_act_forecast_rec => p_act_forecast_rec,
272 p_validation_mode => JTF_PLSQL_API.g_create,
273 x_complete_rec => l_act_forecast_rec,
274 x_return_status => l_return_status,
275 x_msg_count => x_msg_count,
276 x_msg_data => x_msg_data ) ;
277
278
279
280 -- If any errors happen abort API.
281 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
282 RAISE FND_API.G_EXC_ERROR;
283 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
284 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
285 END IF;
286
287
288
289 --
290 -- Validate the record before inserting.
291 --
292
293
294 IF l_act_forecast_rec.forecast_id IS NULL THEN
295 LOOP
296 --
297 -- Set the value for the PK.
298 OPEN c_act_forecast_id;
299 FETCH c_act_forecast_id INTO l_act_forecast_rec.forecast_id;
300 CLOSE c_act_forecast_id;
301
302 OPEN c_act_forecast_count(l_act_forecast_rec.forecast_id);
303 FETCH c_act_forecast_count INTO l_act_forecast_count ;
304 CLOSE c_act_forecast_count ;
305
306 EXIT WHEN l_act_forecast_count = 0 ;
307 END LOOP ;
308 END IF;
309
310
311
312 Validate_ActForecast (
313 p_api_version => l_api_version,
314 p_init_msg_list => p_init_msg_list,
315 p_validation_level => p_validation_level,
316 x_msg_count => x_msg_count,
317 x_msg_data => x_msg_data,
318 x_return_status => l_return_status,
319 p_act_forecast_rec => l_act_forecast_rec
320 );
321
322 -- If any errors happen abort API.
323 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
324 RAISE FND_API.G_EXC_ERROR;
325 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
327 END IF;
328
329
330 --
331 -- Debug message.
332 --
333 IF (OZF_DEBUG_HIGH_ON) THEN
334
335 OZF_Utility_PVT.debug_message(l_full_name ||': insert');
336 END IF;
337
338
339 --
340 -- Insert into the base table.
341 --
342
343 INSERT INTO ozf_act_forecasts_all
344 (forecast_id
345 ,forecast_type
346 ,arc_act_fcast_used_by
347 ,act_fcast_used_by_id
348 ,creation_date
349 ,created_from
350 ,created_by
351 ,last_update_date
352 ,last_updated_by
353 ,last_update_login
354 ,program_application_id
355 ,program_id
356 ,program_update_date
357 ,request_id
358 ,object_version_number
359 ,hierarchy
360 ,hierarchy_level
361 ,level_value
362 ,forecast_calendar
363 ,period_level
364 ,forecast_period_id
365 ,forecast_date
366 ,forecast_uom_code
367 ,forecast_quantity
368 ,forward_buy_quantity
369 ,forward_buy_period
370 ,cumulation_period_choice
371 ,base_quantity
372 ,context
373 ,attribute_category
374 ,attribute1
375 ,attribute2
376 ,attribute3
377 ,attribute4
378 ,attribute5
379 ,attribute6
380 ,attribute7
381 ,attribute8
382 ,attribute9
383 ,attribute10
384 ,attribute11
388 ,attribute15
385 ,attribute12
386 ,attribute13
387 ,attribute14
389 ,org_id
390 ,forecast_remaining_quantity
391 ,forecast_remaining_percent
392 ,base_quantity_type
393 ,forecast_spread_type
394 ,dimention1
395 ,dimention2
396 ,dimention3
397 ,last_scenario_id
398 ,freeze_flag
399 ,comments
400 ,price_list_id
401 ,base_quantity_start_date
402 ,base_quantity_end_date
403 ,base_quantity_ref
404 ,offer_code
405 )
406 VALUES( l_act_forecast_rec.forecast_id
407 ,l_act_forecast_rec.forecast_type
408 ,l_act_forecast_rec.arc_act_fcast_used_by
409 ,l_act_forecast_rec.act_fcast_used_by_id
410 ,SYSDATE
411 ,l_act_forecast_rec.created_from
412 ,FND_GLOBAL.User_ID
413 ,SYSDATE
414 ,FND_GLOBAL.User_ID
415 ,FND_GLOBAL.Conc_Login_ID
416 ,l_act_forecast_rec.program_application_id
417 ,l_act_forecast_rec.program_id
418 ,l_act_forecast_rec.program_update_date
419 ,l_act_forecast_rec.request_id
420 ,1 -- object_version_number
421 ,l_act_forecast_rec.hierarchy
422 ,l_act_forecast_rec.hierarchy_level
423 ,l_act_forecast_rec.level_value
424 ,l_act_forecast_rec.forecast_calendar
425 ,l_act_forecast_rec.period_level
426 ,l_act_forecast_rec.forecast_period_id
427 ,l_act_forecast_rec.forecast_date
428 ,l_act_forecast_rec.forecast_uom_code
429 ,l_act_forecast_rec.forecast_quantity
430 ,l_act_forecast_rec.forward_buy_quantity
431 ,l_act_forecast_rec.forward_buy_period
432 ,l_act_forecast_rec.cumulation_period_choice
433 ,l_act_forecast_rec.base_quantity
434 ,l_act_forecast_rec.context
435 ,l_act_forecast_rec.attribute_category
436 ,l_act_forecast_rec.attribute1
437 ,l_act_forecast_rec.attribute2
438 ,l_act_forecast_rec.attribute3
439 ,l_act_forecast_rec.attribute4
440 ,l_act_forecast_rec.attribute5
441 ,l_act_forecast_rec.attribute6
442 ,l_act_forecast_rec.attribute7
443 ,l_act_forecast_rec.attribute8
444 ,l_act_forecast_rec.attribute9
445 ,l_act_forecast_rec.attribute10
446 ,l_act_forecast_rec.attribute11
447 ,l_act_forecast_rec.attribute12
448 ,l_act_forecast_rec.attribute13
449 ,l_act_forecast_rec.attribute14
450 ,l_act_forecast_rec.attribute15
451 ,TO_NUMBER (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10)) -- org_id
452 ,l_act_forecast_rec.forecast_remaining_quantity
453 ,l_act_forecast_rec.forecast_remaining_percent
454 ,l_act_forecast_rec.base_quantity_type
455 ,l_act_forecast_rec.forecast_spread_type
456 ,l_act_forecast_rec.dimention1
457 ,l_act_forecast_rec.dimention2
458 ,l_act_forecast_rec.dimention3
459 ,l_act_forecast_rec.last_scenario_id
460 ,l_act_forecast_rec.freeze_flag
461 ,l_act_forecast_rec.comments
462 ,l_act_forecast_rec.price_list_id
463 ,l_act_forecast_rec.base_quantity_start_date
464 ,l_act_forecast_rec.base_quantity_end_date
465 ,l_act_forecast_rec.base_quantity_ref
466 ,l_act_forecast_rec.offer_code);
467
468
469 -- If any errors happen abort API.
470 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
471 RAISE FND_API.G_EXC_ERROR;
472 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
474 END IF;
475
476
477 -- finish
478
479 --
480 -- Set OUT NOCOPY value.
481 --
482 x_forecast_id := l_act_forecast_rec.forecast_id;
483
484 --
485 -- End API Body.
486 --
487
488 --
489 -- Standard check for commit request.
490 --
491 IF FND_API.To_Boolean (p_commit) THEN
492 COMMIT WORK;
493 END IF;
494
495 --
496 -- Standard API to get message count, and if 1,
497 -- set the message data OUT NOCOPY variable.
498 --
499 FND_MSG_PUB.Count_And_Get (
500 p_count => x_msg_count,
501 p_data => x_msg_data,
502 p_encoded => FND_API.G_FALSE
503 );
504
505 --
506 -- Add success message to message list.
507 --
508
509 IF (OZF_DEBUG_HIGH_ON) THEN
510
511
512
513 OZF_Utility_PVT.debug_message(l_full_name ||': end Success');
514
515 END IF;
516
517
518
519
520 EXCEPTION
521 WHEN FND_API.G_EXC_ERROR THEN
522
523
524 ROLLBACK TO Create_ActForecast_Pvt;
525 x_return_status := FND_API.G_RET_STS_ERROR;
526 FND_MSG_PUB.Count_And_Get (
527 p_count => x_msg_count,
528 p_data => x_msg_data
529 );
530 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
531
532
533
534 ROLLBACK TO Create_ActForecast_Pvt;
535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
536 FND_MSG_PUB.Count_And_Get (
537 p_count => x_msg_count,
541
538 p_data => x_msg_data
539 );
540 WHEN OTHERS THEN
542
543 ROLLBACK TO Create_ActForecast_Pvt;
544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
545 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
546 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
547 END IF;
548 FND_MSG_PUB.Count_And_Get (
549 p_count => x_msg_count,
550 p_data => x_msg_data
551 );
552 END Create_ActForecast;
553
554
555 -- Start of comments
556 -- NAME
557 -- Update_ActForecast
558 --
559 -- PURPOSE
560 -- Updates an entry in the OZF_ACT_FORECASTS_ALL table
561 --
562 -- NOTES
563 --
564 -- HISTORY
565 -- 18-Apr-2000 tdonohoe Created.
566 -- 15-Jun-2000 tdonohoe Modified to include new column FORECAST_TYPE.
567 --
568 -- End of comments
569
570 PROCEDURE Update_ActForecast (
571 p_api_version IN NUMBER,
572 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
573 p_commit IN VARCHAR2 := FND_API.G_FALSE,
574 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
575
576 x_return_status OUT NOCOPY VARCHAR2,
577 x_msg_count OUT NOCOPY NUMBER,
578 x_msg_data OUT NOCOPY VARCHAR2,
579
580 p_act_forecast_rec IN act_forecast_rec_type
581 )
582 IS
583 L_API_VERSION CONSTANT NUMBER := 1.0;
584 L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_ACTFORECAST';
585 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
586
587
588 l_return_status VARCHAR2(1);
589 l_act_forecast_rec act_forecast_rec_type := p_act_forecast_rec;
590 l_temp_act_forecast_rec act_forecast_rec_type;
591
592 BEGIN
593
594 --
595 -- Initialize savepoint.
596 --
597 SAVEPOINT Update_ActForecast_Pvt;
598
599 --
600 -- Output debug message.
601 --
602 IF (OZF_DEBUG_HIGH_ON) THEN
603
604 OZF_Utility_PVT.debug_message(l_full_name||': start');
605 END IF;
606
607 --
608 -- Initialize message list if p_init_msg_list is set to TRUE.
609 --
610 IF FND_API.To_Boolean (p_init_msg_list) THEN
611 FND_MSG_PUB.Initialize;
612 END IF;
613
614 --
615 -- Standard check for API version compatibility.
616 --
617 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
618 p_api_version,
619 L_API_NAME,
620 G_PKG_NAME)
621 THEN
622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
623 END IF;
624
625 --
626 -- Initialize API return status to success.
627 --
628 x_return_status := FND_API.G_RET_STS_SUCCESS;
629
630 --
631 -- Begin API Body
632 --
633 -- Debug Message
634
635
636 Default_ActForecast
637 ( p_init_msg_list => p_init_msg_list,
638 p_act_forecast_rec => p_act_forecast_rec,
639 p_validation_mode => JTF_PLSQL_API.G_UPDATE,
640 x_complete_rec => l_act_forecast_rec,
641 x_return_status => l_return_status,
642 x_msg_count => x_msg_count,
643 x_msg_data => x_msg_data ) ;
644
645 -- If any errors happen abort API.
646 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
647 RAISE FND_API.G_EXC_ERROR;
648 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
650 END IF;
651
652
653 IF (OZF_DEBUG_HIGH_ON) THEN
654
655
656
657
658
659 OZF_Utility_PVT.debug_message(l_full_name ||': validate ');
660
661
662 END IF;
663
664
665
666 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
667 IF (OZF_DEBUG_HIGH_ON) THEN
668
669 OZF_Utility_PVT.debug_message(l_full_name ||' 1 ' || l_act_forecast_rec.forecast_id);
670 END IF;
671 Validate_ActFcst_Items(
672 p_act_forecast_rec => l_act_forecast_rec,
673 p_validation_mode => JTF_PLSQL_API.g_update,
674 x_return_status => l_return_status
675 );
676 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
677 RAISE FND_API.g_exc_unexpected_error;
678 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
679 IF (OZF_DEBUG_HIGH_ON) THEN
680
681 OZF_Utility_PVT.debug_message(l_full_name ||' 2 ' || p_act_forecast_rec.forecast_id);
682 END IF;
683 RAISE FND_API.g_exc_error;
684 END IF;
685 END IF;
686
687 -- replace g_miss_char/num/date with current column values
688
689 l_temp_act_forecast_rec := l_act_forecast_rec ;
690
691 Complete_ActFcst_Rec(p_act_forecast_rec => l_temp_act_forecast_rec,
692 x_complete_fcst_rec => l_act_forecast_rec);
693
694
695 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
696
697 Validate_ActFcst_Rec(
698 p_act_forecast_rec => p_act_forecast_rec,
699 p_complete_fcst_rec => l_act_forecast_rec,
700 x_return_status => l_return_status );
701
702 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
703 RAISE FND_API.g_exc_unexpected_error;
704 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
705 RAISE FND_API.g_exc_error;
709
706 END IF;
707
708 END IF;
710
711 IF (OZF_DEBUG_HIGH_ON) THEN
712
713
714
715
716
717 OZF_Utility_PVT.debug_message(l_full_name ||': Update Activity Metric Facts Table');
718
719
720 END IF;
721
722
723 UPDATE ozf_act_forecasts_all SET
724 object_version_number = object_version_number +1,
725 forecast_type = l_act_forecast_rec.forecast_type,
726 arc_act_fcast_used_by = l_act_forecast_rec.arc_act_fcast_used_by,
727 act_fcast_used_by_id = l_act_forecast_rec.act_fcast_used_by_id,
728 created_from = l_act_forecast_rec.created_from,
729 hierarchy = l_act_forecast_rec.hierarchy,
730 hierarchy_level = l_act_forecast_rec.hierarchy_level,
731 level_value = l_act_forecast_rec.level_value,
732 forecast_calendar = l_act_forecast_rec.forecast_calendar,
733 period_level = l_act_forecast_rec.period_level,
734 forecast_period_id = l_act_forecast_rec.forecast_period_id ,
735 forecast_date = l_act_forecast_rec.forecast_date,
736 forecast_uom_code = l_act_forecast_rec.forecast_uom_code,
737 forecast_quantity = l_act_forecast_rec.forecast_quantity,
738 forward_buy_quantity = l_act_forecast_rec.forward_buy_quantity,
739 forward_buy_period = l_act_forecast_rec.forward_buy_period,
740 cumulation_period_choice = l_act_forecast_rec.cumulation_period_choice,
741 base_quantity = l_act_forecast_rec.base_quantity,
742 context = l_act_forecast_rec.context,
743 attribute_category = l_act_forecast_rec.attribute_category,
744 attribute1 = l_act_forecast_rec.attribute1,
745 attribute2 = l_act_forecast_rec.attribute2,
746 attribute3 = l_act_forecast_rec.attribute3,
747 attribute4 = l_act_forecast_rec.attribute4,
748 attribute5 = l_act_forecast_rec.attribute5,
749 attribute6 = l_act_forecast_rec.attribute6,
750 attribute7 = l_act_forecast_rec.attribute7,
751 attribute8 = l_act_forecast_rec.attribute8,
752 attribute9 = l_act_forecast_rec.attribute9,
753 attribute10 = l_act_forecast_rec.attribute10,
754 attribute11 = l_act_forecast_rec.attribute11,
755 attribute12 = l_act_forecast_rec.attribute12,
756 attribute13 = l_act_forecast_rec.attribute13,
757 attribute14 = l_act_forecast_rec.attribute14,
758 attribute15 = l_act_forecast_rec.attribute15,
759 org_id = l_act_forecast_rec.org_id,
760 forecast_remaining_quantity = l_act_forecast_rec.forecast_remaining_quantity,
761 forecast_remaining_percent = l_act_forecast_rec.forecast_remaining_percent,
762 base_quantity_type = l_act_forecast_rec.base_quantity_type,
763 forecast_spread_type = l_act_forecast_rec.forecast_spread_type,
764 dimention1 = l_act_forecast_rec.dimention1,
765 dimention2 = l_act_forecast_rec.dimention2,
766 dimention3 = l_act_forecast_rec.dimention3,
767 last_scenario_id = l_act_forecast_rec.last_scenario_id,
768 freeze_flag = l_act_forecast_rec.freeze_flag,
769 comments = l_act_forecast_rec.comments,
770 price_list_id = l_act_forecast_rec.price_list_id
771 Where forecast_id = l_act_forecast_rec.forecast_id
772 And object_version_number = l_act_forecast_rec.object_version_number;
773
774
775 IF (SQL%NOTFOUND)
776 THEN
777 --
778 -- Add error message to API message list.
779 --
780 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
781 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
782 FND_MSG_PUB.add;
783 END IF;
784 RAISE FND_API.g_exc_error;
785 END IF;
786
787
788 --
789 -- End API Body
790 --
791
792 IF FND_API.to_boolean(p_commit) THEN
793 COMMIT;
794 END IF;
795
796 --
797 -- Standard API to get message count, and if 1,
798 -- set the message data OUT NOCOPY variable.
799 --
800 FND_MSG_PUB.Count_And_Get (
801 p_count => x_msg_count,
802 p_data => x_msg_data,
803 p_encoded => FND_API.G_FALSE
804 );
805
806 --
807 -- Debug message.
808 --
809 IF (OZF_DEBUG_HIGH_ON) THEN
810
811 OZF_Utility_PVT.debug_message(l_full_name ||': end');
812 END IF;
813
814
815 EXCEPTION
816 WHEN FND_API.G_EXC_ERROR THEN
817
818 ROLLBACK TO Update_ActForecast_pvt;
819
820 x_return_status := FND_API.G_RET_STS_ERROR;
821 FND_MSG_PUB.Count_And_Get (
822 p_count => x_msg_count,
823 p_data => x_msg_data
824 );
825 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
826
827 ROLLBACK TO Update_ActForecast_pvt;
828 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
829 FND_MSG_PUB.Count_And_Get (
830 p_count => x_msg_count,
831 p_data => x_msg_data
832 );
833 WHEN OTHERS THEN
834
838 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
835 ROLLBACK TO Update_ActForecast_pvt;
836 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
837 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
839 END IF;
840 FND_MSG_PUB.Count_And_Get (
841 p_count => x_msg_count,
842 p_data => x_msg_data
843 );
844 END Update_ActForecast;
845
846 -- Start of comments
847 -- NAME
848 -- Delete_ActForecast
849 --
850 -- PURPOSE
851 -- Deletes an entry in the ozf_act_forecasts_all table.
852 --
853 -- NOTES
854 --
855 -- HISTORY
856 -- 24-Apr-2000 tdonohoe Created.
857 --
858 -- End of comments
859
860 PROCEDURE Delete_ActForecast (
861 p_api_version IN NUMBER,
862 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
863 p_commit IN VARCHAR2 := FND_API.G_FALSE,
864
865 x_return_status OUT NOCOPY VARCHAR2,
866 x_msg_count OUT NOCOPY NUMBER,
867 x_msg_data OUT NOCOPY VARCHAR2,
868
869 p_forecast_id IN NUMBER,
870 p_object_version_number IN NUMBER
871 )
872 IS
873 L_API_VERSION CONSTANT NUMBER := 1.0;
874 L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_ACTFORECAST';
875 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
876
877 l_return_status VARCHAR2(1);
878
879 BEGIN
880 --
881 -- Initialize savepoint.
882 --
883 SAVEPOINT Delete_ActForecast_pvt;
884
885 --
886 -- Output debug message.
887 --
888 IF (OZF_DEBUG_HIGH_ON) THEN
889
890 OZF_Utility_PVT.debug_message(l_full_name||': start');
891 END IF;
892
893 --
894 -- Initialize message list if p_init_msg_list is set to TRUE.
895 --
896 IF FND_API.To_Boolean (p_init_msg_list) THEN
897 FND_MSG_PUB.Initialize;
898 END IF;
899
900 --
901 -- Standard check for API version compatibility.
902 --
903 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
904 p_api_version,
905 L_API_NAME,
906 G_PKG_NAME)
907 THEN
908 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
909 END IF;
910
911 --
912 -- Initialize API return status to success.
913 --
914 x_return_status := FND_API.G_RET_STS_SUCCESS;
915
916 --
917 -- Begin API Body.
918 --
919
920 -- Debug message.
921 IF (OZF_DEBUG_HIGH_ON) THEN
922
923 OZF_Utility_PVT.debug_message(l_full_name ||': delete with Validation');
924 END IF;
925
926
927 IF (OZF_DEBUG_HIGH_ON) THEN
928
929
930
931
932
933 OZF_Utility_PVT.debug_message('forecast id '||to_char(p_forecast_id));
934
935
936 END IF;
937 IF (OZF_DEBUG_HIGH_ON) THEN
938
939 OZF_Utility_PVT.debug_message('object version number '||to_char(p_object_version_number));
940 END IF;
941
942 DELETE from ozf_act_metric_facts_all
943 WHERE act_metric_used_by_id = p_forecast_id
944 AND arc_act_metric_used_by = 'FCST';
945
946 DELETE from ozf_act_metrics_all
947 WHERE act_metric_used_by_id = p_forecast_id
948 AND arc_act_metric_used_by = 'FCST';
949
950 DELETE from ozf_act_forecasts_all
951 WHERE forecast_id = p_forecast_id;
952
953 IF (SQL%NOTFOUND) THEN
954 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
955 THEN
956
957 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
958 FND_MSG_PUB.add;
959 RAISE FND_API.g_exc_error;
960 END IF;
961 END IF;
962
963
964
965 --
966 -- End API Body.
967 --
968
969 IF FND_API.To_Boolean (p_commit) THEN
970 COMMIT WORK;
971 END IF;
972
973 --
974 -- Debug message.
975 --
976 IF (OZF_DEBUG_HIGH_ON) THEN
977
978 OZF_Utility_PVT.debug_message(l_full_name ||': End');
979 END IF;
980
981
982 --
983 -- Standard API to get message count, and if 1,
984 -- set the message data OUT NOCOPY variable.
985 --
986 FND_MSG_PUB.Count_And_Get (
987 p_count => x_msg_count,
988 p_data => x_msg_data,
989 p_encoded => FND_API.G_FALSE
990 );
991
992 EXCEPTION
993 WHEN FND_API.G_EXC_ERROR THEN
994 ROLLBACK TO Delete_ActForecast_pvt;
995 x_return_status := FND_API.G_RET_STS_ERROR;
996 FND_MSG_PUB.Count_And_Get (
997 p_count => x_msg_count,
998 p_data => x_msg_data
999 );
1000 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1001 ROLLBACK TO Delete_ActForecast_pvt;
1002 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1003 FND_MSG_PUB.Count_And_Get (
1004 p_count => x_msg_count,
1005 p_data => x_msg_data
1006 );
1007 WHEN OTHERS THEN
1008 ROLLBACK TO Delete_ActForecast_pvt;
1009 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1010 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1011 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1012 END IF;
1016 );
1013 FND_MSG_PUB.Count_And_Get (
1014 p_count => x_msg_count,
1015 p_data => x_msg_data
1017 END Delete_ActForecast;
1018
1019
1020 -- Start of comments
1021 -- NAME
1022 -- Lock_ActForecast
1023 --
1024 -- PURPOSE
1025 -- Lock the given row in OZF_ACT_FORECASTS_ALL table.
1026 --
1027 -- NOTES
1028 --
1029 -- HISTORY
1030 -- 19-Apr-2000 tdonohoe Created.
1031 --
1032 -- End of comments
1033
1034 PROCEDURE Lock_ActForecast (
1035 p_api_version IN NUMBER,
1036 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1037
1038 x_return_status OUT NOCOPY VARCHAR2,
1039 x_msg_count OUT NOCOPY NUMBER,
1040 x_msg_data OUT NOCOPY VARCHAR2,
1041
1042 p_forecast_id IN NUMBER,
1043 p_object_version_number IN NUMBER
1044 )
1045 IS
1046 L_API_VERSION CONSTANT NUMBER := 1.0;
1047 L_API_NAME CONSTANT VARCHAR2(30) := 'LOCK_ACTFORECAST';
1048 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1049
1050 l_forecast_id NUMBER;
1051
1052 CURSOR c_act_forecast_info IS
1053 SELECT forecast_id
1054 FROM ozf_act_forecasts_all
1055 WHERE forecast_id = p_forecast_id
1056 AND object_version_number = p_object_version_number
1057 FOR UPDATE OF forecast_id NOWAIT;
1058
1059 BEGIN
1060 --
1061 -- Output debug message.
1062 --
1063 IF (OZF_DEBUG_HIGH_ON) THEN
1064
1065 OZF_Utility_PVT.debug_message(l_full_name||': start');
1066 END IF;
1067
1068 --
1069 -- Initialize message list if p_init_msg_list is set to TRUE.
1070 --
1071 IF FND_API.To_Boolean (p_init_msg_list) THEN
1072 FND_MSG_PUB.Initialize;
1073 END IF;
1074
1075 --
1076 -- Standard check for API version compatibility.
1077 --
1078 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
1079 p_api_version,
1080 L_API_NAME,
1081 G_PKG_NAME)
1082 THEN
1083 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1084 END IF;
1085
1086 --
1087 -- Initialize API return status to success.
1088 --
1089 x_return_status := FND_API.G_RET_STS_SUCCESS;
1090
1091 --
1092 -- Begin API Body
1093 --
1094 IF (OZF_DEBUG_HIGH_ON) THEN
1095
1096 OZF_Utility_PVT.debug_message(l_full_name||': lock');
1097 END IF;
1098
1099 OPEN c_act_forecast_info;
1100 FETCH c_act_forecast_info INTO l_forecast_id;
1101 IF (c_act_forecast_info%NOTFOUND)
1102 THEN
1103 CLOSE c_act_forecast_info;
1104 -- Error, check the msg level and added an error message to the
1105 -- API message list
1106 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1107 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1108 FND_MSG_PUB.add;
1109 END IF;
1110 RAISE FND_API.g_exc_error;
1111 END IF;
1112 CLOSE c_act_forecast_info;
1113
1114
1115 --
1116 -- Standard API to get message count, and if 1,
1117 -- set the message data OUT NOCOPY variable.
1118 --
1119 FND_MSG_PUB.Count_And_Get (
1120 p_count => x_msg_count,
1121 p_data => x_msg_data,
1122 p_encoded => FND_API.G_FALSE
1123 );
1124
1125 --
1126 -- Debug message.
1127 --
1128 IF (OZF_DEBUG_HIGH_ON) THEN
1129
1130 OZF_Utility_PVT.debug_message(l_full_name ||': end');
1131 END IF;
1132
1133
1134 EXCEPTION
1135 WHEN FND_API.G_EXC_ERROR THEN
1136 x_return_status := FND_API.G_RET_STS_ERROR;
1137 FND_MSG_PUB.Count_And_Get (
1138 p_count => x_msg_count,
1139 p_data => x_msg_data
1140 );
1141 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1143 FND_MSG_PUB.Count_And_Get (
1144 p_count => x_msg_count,
1145 p_data => x_msg_data
1146 );
1147 WHEN OZF_Utility_PVT.RESOURCE_LOCKED THEN
1148 x_return_status := FND_API.G_RET_STS_ERROR ;
1149
1150 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1151 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCE_LOCKED');
1152 FND_MSG_PUB.add;
1153 END IF;
1154
1155 FND_MSG_PUB.Count_And_Get (
1156 p_count => x_msg_count,
1157 p_data => x_msg_data,
1158 p_encoded => FND_API.G_FALSE
1159 );
1160 WHEN OTHERS THEN
1161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1162 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1163 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1164 END IF;
1165 FND_MSG_PUB.Count_And_Get (
1166 p_count => x_msg_count,
1167 p_data => x_msg_data,
1168 p_encoded => FND_API.G_FALSE
1169 );
1170 END Lock_ActForecast;
1171
1172
1173
1174
1175 -- Start of comments
1176 -- NAME
1177 -- Validate_ActForecast
1178 --
1179 -- PURPOSE
1180 -- Validation API for Activity metric facts table.
1181 --
1182
1183 -- NOTES
1184 --
1185 -- HISTORY
1186 -- 24-Apr-2000 tdonohoe Created.
1187
1188 --
1189 -- End of comments
1190
1191 PROCEDURE Validate_ActForecast (
1195
1192 p_api_version IN NUMBER,
1193 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1194 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
1196 x_return_status OUT NOCOPY VARCHAR2,
1197 x_msg_count OUT NOCOPY NUMBER,
1198 x_msg_data OUT NOCOPY VARCHAR2,
1199
1200 p_act_forecast_rec IN act_forecast_rec_type
1201 )
1202 IS
1203 L_API_VERSION CONSTANT NUMBER := 1.0;
1204 L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_ACTFORECAST';
1205 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1206
1207
1208 l_return_status VARCHAR2(1);
1209
1210 BEGIN
1211 --
1212 -- Output debug message.
1213 --
1214 IF (OZF_DEBUG_HIGH_ON) THEN
1215
1216 OZF_Utility_PVT.debug_message(l_full_name||': start');
1217 END IF;
1218
1219 --
1220 -- Initialize message list if p_init_msg_list is set to TRUE.
1221 --
1222 IF FND_API.To_Boolean (p_init_msg_list) THEN
1223 FND_MSG_PUB.Initialize;
1224 END IF;
1225
1226 --
1227 -- Standard check for API version compatibility.
1228 --
1229 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
1230 p_api_version,
1231 L_API_NAME,
1232 G_PKG_NAME)
1233 THEN
1234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1235 END IF;
1236
1237 --
1238 -- Initialize API return status to success.
1239 --
1240 x_return_status := FND_API.G_RET_STS_SUCCESS;
1241
1242 --
1243 -- Begin API Body.
1244 --
1245
1246 IF (OZF_DEBUG_HIGH_ON) THEN
1247
1248
1249
1250 OZF_Utility_PVT.debug_message(l_full_name||': Validate items');
1251
1252 END IF;
1253
1254 -- Validate required items in the record.
1255 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1256
1257 Validate_ActFcst_Items(
1258 p_act_forecast_rec => p_act_forecast_rec,
1259 p_validation_mode => JTF_PLSQL_API.g_create,
1260 x_return_status => l_return_status
1261 );
1262
1263 -- If any errors happen abort API.
1264 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1266 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1267 RAISE FND_API.G_EXC_ERROR;
1268 END IF;
1269 END IF;
1270
1271 IF (OZF_DEBUG_HIGH_ON) THEN
1272
1273
1274
1275 OZF_Utility_PVT.debug_message(l_full_name||': check record');
1276
1277 END IF;
1278
1279 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1280 Validate_ActFcst_Rec(
1281 p_act_forecast_rec => p_act_forecast_rec,
1282 p_complete_fcst_rec => NULL,
1283 x_return_status => l_return_status
1284 );
1285
1286 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1287 IF (OZF_DEBUG_HIGH_ON) THEN
1288
1289 OZF_Utility_PVT.debug_message(l_full_name||': error in check record');
1290 END IF;
1291 RAISE FND_API.g_exc_unexpected_error;
1292 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1293 IF (OZF_DEBUG_HIGH_ON) THEN
1294
1295 OZF_Utility_PVT.debug_message(l_full_name||': error in check record');
1296 END IF;
1297 RAISE FND_API.g_exc_error;
1298 END IF;
1299 END IF;
1300
1301 IF (OZF_DEBUG_HIGH_ON) THEN
1302
1303
1304
1305 OZF_Utility_PVT.debug_message(l_full_name||': after check record');
1306
1307 END IF;
1308
1309
1310 --
1311 -- End API Body.
1312 --
1313
1314 --
1315 -- Standard API to get message count, and if 1,
1316 -- set the message data OUT NOCOPY variable.
1317 --
1318 FND_MSG_PUB.Count_And_Get (
1319 p_count => x_msg_count,
1320 p_data => x_msg_data,
1321 p_encoded => FND_API.G_FALSE
1322 );
1323
1324 IF (OZF_DEBUG_HIGH_ON) THEN
1325
1326
1327
1328 OZF_Utility_PVT.debug_message(l_full_name ||': end');
1329
1330 END IF;
1331
1332
1333 EXCEPTION
1334 WHEN FND_API.G_EXC_ERROR THEN
1335 x_return_status := FND_API.G_RET_STS_ERROR;
1336 FND_MSG_PUB.Count_And_Get (
1337 p_count => x_msg_count,
1338 p_data => x_msg_data
1339 );
1340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1342 FND_MSG_PUB.Count_And_Get (
1343 p_count => x_msg_count,
1344 p_data => x_msg_data
1345 );
1346 WHEN OTHERS THEN
1347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1348 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1349 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1350 END IF;
1351 FND_MSG_PUB.Count_And_Get (
1352 p_count => x_msg_count,
1353 p_data => x_msg_data
1354 );
1355 END Validate_ActForecast;
1356
1357
1358 --
1359 -- NAME
1360 -- Complete_Forecast_Rec
1361 --
1362 -- PURPOSE
1363 -- Returns the Initialized Activity Forecast Record
1364 --
1365 -- NOTES
1366 --
1367 -- HISTORY
1368 -- 21-Apr-2000 tdonohoe Created.
1372 p_act_forecast_rec IN act_forecast_rec_type,
1369 -- 15-Jun-2000 tdonohoe Modified to include new column FORECAST_TYPE.
1370 --
1371 PROCEDURE Complete_ActFcst_Rec(
1373 x_complete_fcst_rec OUT NOCOPY act_forecast_rec_type
1374 )
1375 IS
1376 CURSOR c_act_forecast IS
1377 SELECT *
1378 FROM ozf_act_forecasts_all
1379 WHERE forecast_id = p_act_forecast_rec.forecast_id;
1380
1381 l_act_forecast_rec c_act_forecast%ROWTYPE;
1382 BEGIN
1383
1384 x_complete_fcst_rec := p_act_forecast_rec;
1385
1386 IF (OZF_DEBUG_HIGH_ON) THEN
1387
1388
1389
1390 OZF_Utility_PVT.debug_message('forecast_id before exception : ' || p_act_forecast_rec.forecast_id);
1391
1392 END IF;
1393 IF (OZF_DEBUG_HIGH_ON) THEN
1394
1395 OZF_Utility_PVT.debug_message('forecast_id before exception : ' || x_complete_fcst_rec.forecast_id);
1396 END IF;
1397
1398 OPEN c_act_forecast;
1399 FETCH c_act_forecast INTO l_act_forecast_rec;
1400 IF c_act_forecast%NOTFOUND THEN
1401 CLOSE c_act_forecast;
1402 IF (OZF_DEBUG_HIGH_ON) THEN
1403
1404 OZF_Utility_PVT.debug_message('forecast_id before raising exception : ' || x_complete_fcst_rec.forecast_id);
1405 END IF;
1406 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1407 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1408 FND_MSG_PUB.add;
1409 END IF;
1410 RAISE FND_API.g_exc_error;
1411 END IF;
1412 CLOSE c_act_forecast;
1413
1414 IF (OZF_DEBUG_HIGH_ON) THEN
1415
1416
1417
1418 OZF_Utility_PVT.debug_message('forecast_id before raising : ' || p_act_forecast_rec.forecast_id);
1419
1420 END IF;
1421
1422
1423 IF p_act_forecast_rec.forecast_id = FND_API.G_MISS_NUM THEN
1424 x_complete_fcst_rec.forecast_id := l_act_forecast_rec.forecast_id;
1425 END IF;
1426
1427 IF p_act_forecast_rec.forecast_type = FND_API.G_MISS_CHAR THEN
1428 x_complete_fcst_rec.forecast_type := l_act_forecast_rec.forecast_type;
1429 END IF;
1430
1431 IF p_act_forecast_rec.arc_act_fcast_used_by = FND_API.G_MISS_CHAR THEN
1432 x_complete_fcst_rec.arc_act_fcast_used_by := l_act_forecast_rec.arc_act_fcast_used_by;
1433 END IF;
1434
1435 IF p_act_forecast_rec.act_fcast_used_by_id = FND_API.G_MISS_NUM THEN
1436 x_complete_fcst_rec.act_fcast_used_by_id := l_act_forecast_rec.act_fcast_used_by_id ;
1437 END IF;
1438
1439 IF p_act_forecast_rec.creation_date = FND_API.G_MISS_DATE THEN
1440 x_complete_fcst_rec.creation_date := l_act_forecast_rec.creation_date;
1441 END IF;
1442
1443 IF p_act_forecast_rec.created_from = FND_API.G_MISS_CHAR THEN
1444 x_complete_fcst_rec.created_from := l_act_forecast_rec.created_from;
1445 END IF;
1446
1447 IF p_act_forecast_rec.created_by = FND_API.G_MISS_NUM THEN
1448 x_complete_fcst_rec.created_by := l_act_forecast_rec.created_by;
1449 END IF;
1450
1451 IF p_act_forecast_rec.last_update_date = FND_API.G_MISS_DATE THEN
1452 x_complete_fcst_rec.last_update_date := l_act_forecast_rec.last_update_date;
1453 END IF;
1454
1455 IF p_act_forecast_rec.last_updated_by = FND_API.G_MISS_NUM THEN
1456 x_complete_fcst_rec.last_updated_by := l_act_forecast_rec.last_updated_by ;
1457 END IF;
1458
1459 IF p_act_forecast_rec.last_update_login = FND_API.G_MISS_NUM THEN
1460 x_complete_fcst_rec.last_update_login := l_act_forecast_rec.last_update_login;
1461 END IF;
1462
1463 IF p_act_forecast_rec.program_application_id = FND_API.G_MISS_NUM THEN
1464 x_complete_fcst_rec.program_application_id := l_act_forecast_rec.program_application_id;
1465 END IF;
1466
1467 IF p_act_forecast_rec.program_id = FND_API.G_MISS_NUM THEN
1468 x_complete_fcst_rec.program_id := l_act_forecast_rec.program_id;
1469 END IF;
1470
1471 IF p_act_forecast_rec.program_update_date = FND_API.G_MISS_DATE THEN
1472 x_complete_fcst_rec.program_update_date := l_act_forecast_rec.program_update_date;
1473 END IF;
1474
1475 IF p_act_forecast_rec.request_id = FND_API.G_MISS_NUM THEN
1476 x_complete_fcst_rec.request_id := l_act_forecast_rec.request_id ;
1477 END IF;
1478
1479 IF p_act_forecast_rec.hierarchy = FND_API.G_MISS_CHAR THEN
1480 x_complete_fcst_rec.hierarchy := l_act_forecast_rec.hierarchy;
1481 END IF;
1482
1483 IF p_act_forecast_rec.hierarchy_level = FND_API.G_MISS_CHAR THEN
1484 x_complete_fcst_rec.hierarchy_level := l_act_forecast_rec.hierarchy_level;
1485 END IF;
1486
1487 IF p_act_forecast_rec.level_value = FND_API.G_MISS_CHAR THEN
1488 x_complete_fcst_rec.level_value := l_act_forecast_rec.level_value;
1489 END IF;
1490
1491 IF p_act_forecast_rec.forecast_calendar = FND_API.G_MISS_CHAR THEN
1492 x_complete_fcst_rec.forecast_calendar := l_act_forecast_rec.forecast_calendar;
1493 END IF;
1494
1495 IF p_act_forecast_rec.period_level = FND_API.G_MISS_CHAR THEN
1496 x_complete_fcst_rec.period_level := l_act_forecast_rec.period_level;
1497 END IF;
1498
1499 IF p_act_forecast_rec.forecast_period_id = FND_API.G_MISS_NUM THEN
1500 x_complete_fcst_rec.forecast_period_id := l_act_forecast_rec.forecast_period_id;
1501 END IF;
1502
1503 IF p_act_forecast_rec.forecast_date = FND_API.G_MISS_DATE THEN
1504 x_complete_fcst_rec.forecast_date := l_act_forecast_rec.forecast_date;
1505 END IF;
1506
1507 IF p_act_forecast_rec.forecast_uom_code = FND_API.G_MISS_CHAR THEN
1508 x_complete_fcst_rec.forecast_uom_code := l_act_forecast_rec.forecast_uom_code;
1509 END IF;
1510
1511 IF p_act_forecast_rec.forecast_quantity = FND_API.G_MISS_NUM THEN
1512 x_complete_fcst_rec.forecast_quantity := l_act_forecast_rec.forecast_quantity;
1516 x_complete_fcst_rec.forward_buy_quantity := l_act_forecast_rec.forward_buy_quantity;
1513 END IF;
1514
1515 IF p_act_forecast_rec.forward_buy_quantity = FND_API.G_MISS_NUM THEN
1517 END IF;
1518
1519 IF p_act_forecast_rec.forward_buy_period = FND_API.G_MISS_CHAR THEN
1520 x_complete_fcst_rec.forward_buy_period := l_act_forecast_rec.forward_buy_period;
1521 END IF;
1522
1523 IF p_act_forecast_rec.cumulation_period_choice = FND_API.G_MISS_CHAR THEN
1524 x_complete_fcst_rec.cumulation_period_choice := l_act_forecast_rec.cumulation_period_choice;
1525 END IF;
1526
1527 IF p_act_forecast_rec.base_quantity = FND_API.G_MISS_NUM THEN
1528 x_complete_fcst_rec.base_quantity := l_act_forecast_rec.base_quantity ;
1529 END IF;
1530
1531 IF p_act_forecast_rec.context = FND_API.G_MISS_CHAR THEN
1532 x_complete_fcst_rec.context := l_act_forecast_rec.context;
1533 END IF;
1534
1535 IF p_act_forecast_rec.attribute_category = FND_API.G_MISS_CHAR THEN
1536 x_complete_fcst_rec.attribute_category := l_act_forecast_rec.attribute_category;
1537 END IF;
1538
1539 IF p_act_forecast_rec.attribute1 = FND_API.G_MISS_CHAR THEN
1540 x_complete_fcst_rec.attribute1 := l_act_forecast_rec.attribute1;
1541 END IF;
1542
1543 IF p_act_forecast_rec.attribute2 = FND_API.G_MISS_CHAR THEN
1544 x_complete_fcst_rec.attribute2 := l_act_forecast_rec.attribute2;
1545 END IF;
1546
1547 IF p_act_forecast_rec.attribute3 = FND_API.G_MISS_CHAR THEN
1548 x_complete_fcst_rec.attribute3 := l_act_forecast_rec.attribute3;
1549 END IF;
1550
1551 IF p_act_forecast_rec.attribute4 = FND_API.G_MISS_CHAR THEN
1552 x_complete_fcst_rec.attribute4 := l_act_forecast_rec.attribute4;
1553 END IF;
1554
1555 IF p_act_forecast_rec.attribute5 = FND_API.G_MISS_CHAR THEN
1556 x_complete_fcst_rec.attribute5 := l_act_forecast_rec.attribute5;
1557 END IF;
1558
1559 IF p_act_forecast_rec.attribute6 = FND_API.G_MISS_CHAR THEN
1560 x_complete_fcst_rec.attribute6 := l_act_forecast_rec.attribute6;
1561 END IF;
1562
1563 IF p_act_forecast_rec.attribute7 = FND_API.G_MISS_CHAR THEN
1564 x_complete_fcst_rec.attribute7 := l_act_forecast_rec.attribute7;
1565 END IF;
1566
1567 IF p_act_forecast_rec.attribute8 = FND_API.G_MISS_CHAR THEN
1568 x_complete_fcst_rec.attribute8 := l_act_forecast_rec.attribute8;
1569 END IF;
1570
1571 IF p_act_forecast_rec.attribute9 = FND_API.G_MISS_CHAR THEN
1572 x_complete_fcst_rec.attribute9 := l_act_forecast_rec.attribute9;
1573 END IF;
1574
1575 IF p_act_forecast_rec.attribute10 = FND_API.G_MISS_CHAR THEN
1576 x_complete_fcst_rec.attribute10 := l_act_forecast_rec.attribute10;
1577 END IF;
1578
1579 IF p_act_forecast_rec.attribute11 = FND_API.G_MISS_CHAR THEN
1580 x_complete_fcst_rec.attribute11 := l_act_forecast_rec.attribute11;
1581 END IF;
1582
1583 IF p_act_forecast_rec.attribute12 = FND_API.G_MISS_CHAR THEN
1584 x_complete_fcst_rec.attribute12 := l_act_forecast_rec.attribute12;
1585 END IF;
1586
1587 IF p_act_forecast_rec.attribute13 = FND_API.G_MISS_CHAR THEN
1588 x_complete_fcst_rec.attribute13 := l_act_forecast_rec.attribute13;
1589 END IF;
1590
1591 IF p_act_forecast_rec.attribute14 = FND_API.G_MISS_CHAR THEN
1592 x_complete_fcst_rec.attribute14 := l_act_forecast_rec.attribute14;
1593 END IF;
1594
1595 IF p_act_forecast_rec.attribute15 = FND_API.G_MISS_CHAR THEN
1596 x_complete_fcst_rec.attribute15 := l_act_forecast_rec.attribute15;
1597 END IF;
1598
1599 IF p_act_forecast_rec.org_id = FND_API.G_MISS_NUM THEN
1600 x_complete_fcst_rec.org_id := l_act_forecast_rec.org_id;
1601 END IF;
1602
1603 IF p_act_forecast_rec.forecast_remaining_quantity = FND_API.G_MISS_NUM THEN
1604 x_complete_fcst_rec.forecast_remaining_quantity := l_act_forecast_rec.forecast_remaining_quantity;
1605 END IF;
1606
1607 IF p_act_forecast_rec.forecast_remaining_percent = FND_API.G_MISS_NUM THEN
1608 x_complete_fcst_rec.forecast_remaining_percent := l_act_forecast_rec.forecast_remaining_percent;
1609 END IF;
1610
1611 IF p_act_forecast_rec.base_quantity_type = FND_API.G_MISS_CHAR THEN
1612 x_complete_fcst_rec.base_quantity_type := l_act_forecast_rec.base_quantity_type;
1613 END IF;
1614
1615 IF p_act_forecast_rec.forecast_spread_type = FND_API.G_MISS_CHAR THEN
1616 x_complete_fcst_rec.forecast_spread_type := l_act_forecast_rec.forecast_spread_type;
1617 END IF;
1618
1619 IF p_act_forecast_rec.dimention1 = FND_API.G_MISS_CHAR THEN
1620 x_complete_fcst_rec.dimention1 := l_act_forecast_rec.dimention1;
1621 END IF;
1622
1623 IF p_act_forecast_rec.dimention2 = FND_API.G_MISS_CHAR THEN
1624 x_complete_fcst_rec.dimention2 := l_act_forecast_rec.dimention2;
1625 END IF;
1626
1627 IF p_act_forecast_rec.dimention3 = FND_API.G_MISS_CHAR THEN
1628 x_complete_fcst_rec.dimention3 := l_act_forecast_rec.dimention3;
1629 END IF;
1630
1631 IF p_act_forecast_rec.last_scenario_id = FND_API.G_MISS_NUM THEN
1632 x_complete_fcst_rec.last_scenario_id := l_act_forecast_rec.last_scenario_id;
1633 END IF;
1634
1635 IF p_act_forecast_rec.freeze_flag = FND_API.G_MISS_CHAR THEN
1636 x_complete_fcst_rec.freeze_flag := l_act_forecast_rec.freeze_flag;
1637 END IF;
1638
1639 IF p_act_forecast_rec.comments = FND_API.G_MISS_CHAR THEN
1640 x_complete_fcst_rec.comments := l_act_forecast_rec.comments;
1641 END IF;
1642
1643 IF p_act_forecast_rec.price_list_id = FND_API.G_MISS_NUM THEN
1644 x_complete_fcst_rec.price_list_id := l_act_forecast_rec.price_list_id ;
1645 END IF;
1646
1647 IF p_act_forecast_rec.base_quantity_start_date = FND_API.G_MISS_DATE THEN
1651 x_complete_fcst_rec.base_quantity_end_date := l_act_forecast_rec.base_quantity_end_date;
1648 x_complete_fcst_rec.base_quantity_start_date := l_act_forecast_rec.base_quantity_start_date;
1649 END IF;
1650 IF p_act_forecast_rec.base_quantity_end_date = FND_API.G_MISS_DATE THEN
1652 END IF;
1653 IF p_act_forecast_rec.base_quantity_ref = FND_API.G_MISS_CHAR THEN
1654 x_complete_fcst_rec.base_quantity_ref := l_act_forecast_rec.base_quantity_ref;
1655 END IF;
1656 IF p_act_forecast_rec.offer_code= FND_API.G_MISS_CHAR THEN
1657 x_complete_fcst_rec.offer_code := l_act_forecast_rec.offer_code;
1658 END IF;
1659
1660 END Complete_ActFcst_Rec ;
1661
1662
1663 -- Start of comments.
1664 --
1665 -- NAME
1666 -- Check_Req_ActFcst_Items
1667 --
1668 -- PURPOSE
1669 -- Validate required forecast items.
1670 --
1671 -- NOTES
1672 --
1673 -- HISTORY
1674 -- 24-Apr-2000 tdonohoe Created.
1675 -- 15-Jun-2000 tdonohoe Modified to include new column FORECAST_TYPE.
1676 --
1677 -- End of comments.
1678
1679 PROCEDURE Check_Req_ActFcst_Items (
1680 p_act_forecast_rec IN act_forecast_rec_type,
1681 x_return_status OUT NOCOPY VARCHAR2
1682 )
1683 IS
1684 BEGIN
1685 -- Initialize return status to success.
1686 x_return_status := FND_API.G_RET_STS_SUCCESS;
1687
1688 --FORECAST_TYPE
1689
1690 IF p_act_forecast_rec.forecast_type IS NULL
1691 THEN
1692 -- missing required fields
1693 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1694 THEN -- MMSG
1695 FND_MESSAGE.Set_Name('OZF', 'OZF_FCST_MISSING_TYPE');
1696 FND_MSG_PUB.Add;
1697 END IF;
1698
1699 x_return_status := FND_API.G_RET_STS_ERROR;
1700
1701 -- If any error happens abort API.
1702 RETURN;
1703 END IF;
1704
1705 --ARC_ACT_FCAST_USED_BY
1706
1707 IF p_act_forecast_rec.arc_act_fcast_used_by IS NULL
1708 THEN
1709 -- missing required fields
1710 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1711 THEN -- MMSG
1712 FND_MESSAGE.Set_Name('OZF', 'OZF_FCST_MISSING_ARC_USED_FOR');
1713 FND_MSG_PUB.Add;
1714 END IF;
1715
1716 x_return_status := FND_API.G_RET_STS_ERROR;
1717
1718 -- If any error happens abort API.
1719 RETURN;
1720 END IF;
1721
1722 --ACT_FCAST_USED_BY_ID
1723
1724 IF p_act_forecast_rec.act_fcast_used_by_id IS NULL
1725 THEN
1726 -- missing required fields
1727 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1728 THEN -- MMSG
1729 FND_MESSAGE.Set_Name('OZF', 'OZF_FCST_MISSING_ARC_USED_FOR');
1730 FND_MSG_PUB.Add;
1731 END IF;
1732
1733 x_return_status := FND_API.G_RET_STS_ERROR;
1734
1735 -- If any error happens abort API.
1736 RETURN;
1737 END IF;
1738
1739 --FORECAST_CALENDAR
1740
1741 IF p_act_forecast_rec.forecast_calendar IS NULL
1742 THEN
1743 -- missing required fields
1744 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1745 THEN -- MMSG
1746 FND_MESSAGE.Set_Name('OZF', 'OZF_FCST_MISSING_CALENDAR');
1747 FND_MSG_PUB.Add;
1748 END IF;
1749
1750 x_return_status := FND_API.G_RET_STS_ERROR;
1751
1752 -- If any error happens abort API.
1753 RETURN;
1754 END IF;
1755
1756 -- FORECAST_UOM_CODE
1757
1758 IF p_act_forecast_rec.forecast_uom_code IS NULL
1759 THEN
1760 -- missing required fields
1761 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1762 THEN -- MMSG
1763 FND_MESSAGE.Set_Name('OZF', 'OZF_FCST_MISSING_UOM_CODE');
1764 FND_MSG_PUB.Add;
1765 END IF;
1766
1767 x_return_status := FND_API.G_RET_STS_ERROR;
1768
1769 -- If any error happens abort API.
1770 RETURN;
1771 END IF;
1772
1773
1774
1775 EXCEPTION
1776 WHEN OTHERS THEN
1777 RAISE;
1778 END Check_Req_ActFcst_Items;
1779
1780
1781 --
1782 -- Start of comments.
1783 --
1784 -- NAME
1785 -- Check_ActFcst_UK_Items
1786 --
1787 -- PURPOSE
1788 -- Perform Uniqueness check for Activity metric facts.
1789 --
1790 -- NOTES
1791 --
1792 -- HISTORY
1793 -- 24-Apr-2000 tdonohoe Created.
1794 --
1795 -- End of comments.
1796
1797
1798 PROCEDURE Check_ActFcst_UK_Items(
1799 p_act_forecast_rec IN act_forecast_rec_type,
1800 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1801 x_return_status OUT NOCOPY VARCHAR2
1802 )
1803 IS
1804 l_where_clause VARCHAR2(2000); -- Used By Check_Uniqueness
1805
1806 BEGIN
1807
1808 x_return_status := FND_API.g_ret_sts_success;
1809
1810 -- For Create_ActForecast, check if a Forecast already exists
1811 -- For the given object
1812
1813 IF p_validation_mode = JTF_PLSQL_API.g_create
1814 THEN
1815
1816 l_where_clause := ' arc_act_fcast_used_by = '||p_act_forecast_rec.arc_act_fcast_used_by ;
1817 l_where_clause := l_where_clause ||
1818 ' act_fcast_used_by_id = '||p_act_forecast_rec.act_fcast_used_by_id ;
1819 l_where_clause := l_where_clause ||
1820 ' last_scenario_id = '||p_act_forecast_rec.last_scenario_id ;
1821
1822 IF OZF_Utility_PVT.Check_Uniqueness(
1823 p_table_name => 'ozf_act_forecasts_all',
1824 p_where_clause => l_where_clause
1825 ) = FND_API.g_false
1826 THEN
1830 FND_MSG_PUB.add;
1827 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1828 THEN
1829 FND_MESSAGE.set_name('OZF', 'OZF_FCST_DUP_ID');
1831 END IF;
1832 x_return_status := FND_API.g_ret_sts_error;
1833 RETURN;
1834 END IF;
1835 END IF;
1836
1837 END Check_ActFcst_Uk_Items;
1838
1839
1840 --
1841 -- Start of comments.
1842 --
1843 -- NAME
1844 -- Check_ActFcst_Items
1845 --
1846 -- PURPOSE
1847 -- Perform item level validation for Activity metric facts.
1848 --
1849 -- NOTES
1850 --
1851 -- HISTORY
1852 -- 24-Apr-2000 tdonohoe Created.
1853 --
1854 -- End of comments.
1855
1856 PROCEDURE Check_ActFcst_Items (
1857 p_act_forecast_rec IN act_forecast_rec_type,
1858 x_return_status OUT NOCOPY VARCHAR2
1859 )
1860 IS
1861 l_item_name VARCHAR2(30); -- Used to standardize error messages.
1862 l_act_forecast_rec act_forecast_rec_type := p_act_forecast_rec;
1863 l_return_status VARCHAR2(1);
1864
1865
1866 l_table_name VARCHAR2(30);
1867 l_pk_name VARCHAR2(30);
1868 l_pk_value VARCHAR2(30);
1869 l_pk_data_type VARCHAR2(30);
1870 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
1871 l_lookup_type VARCHAR2(30);
1872
1873
1874
1875 BEGIN
1876 -- Initialize return status to success.
1877 x_return_status := FND_API.G_RET_STS_SUCCESS;
1878
1879 --FORECAST_TYPE
1880 IF l_act_forecast_rec.forecast_type <> FND_API.G_MISS_CHAR THEN
1881
1882 IF OZF_Utility_PVT.check_lookup_exists(
1883 p_lookup_type => 'OZF_FCAST_TYPE',
1884 p_lookup_code => l_act_forecast_rec.forecast_type
1885 ) = FND_API.g_false
1886 THEN
1887 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1888 THEN
1889 FND_MESSAGE.set_name('OZF', 'OZF_FCST_INVALID_TYPE');
1890 FND_MSG_PUB.add;
1891 END IF;
1892
1893 x_return_status := FND_API.g_ret_sts_error;
1894 RETURN;
1895 END IF;
1896
1897 END IF;
1898
1899 -- ARC_ACT_FCAST_USED_BY
1900
1901 IF l_act_forecast_rec.arc_act_fcast_used_by <> FND_API.G_MISS_CHAR THEN
1902 IF l_act_forecast_rec.arc_act_fcast_used_by not in ( 'CAMP','OFFR')
1903
1904 THEN
1905 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1906 FND_MESSAGE.Set_Name ('OZF', 'OZF_FCST_INVALID_USED_BY');
1907 FND_MSG_PUB.Add;
1908 END IF;
1909
1910 x_return_status := FND_API.G_RET_STS_ERROR;
1911 RETURN;
1912 END IF;
1913 END IF;
1914
1915 --BASE_QUANTITY_TYPE
1916 IF l_act_forecast_rec.base_quantity_type <> FND_API.G_MISS_CHAR AND l_act_forecast_rec.base_quantity_type IS NOT NULL THEN
1917
1918 IF OZF_Utility_PVT.check_lookup_exists(
1919 p_lookup_type => 'OZF_FCAST_BASE_VOL_SOURCE',
1920 p_lookup_code => l_act_forecast_rec.base_quantity_type
1921 ) = FND_API.g_false
1922 THEN
1923 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1924 THEN
1925 FND_MESSAGE.set_name('OZF', 'OZF_FSCT_INVALID_BASE_QTY');
1926 FND_MSG_PUB.add;
1927 END IF;
1928
1929 x_return_status := FND_API.g_ret_sts_error;
1930 RETURN;
1931 END IF;
1932
1933 END IF;
1934
1935
1936 --FORECAST_SPREAD_TYPE
1937 IF l_act_forecast_rec.forecast_spread_type <> FND_API.G_MISS_CHAR AND l_act_forecast_rec.forecast_spread_type IS NOT NULL THEN
1938
1939 IF OZF_Utility_PVT.check_lookup_exists(
1940 p_lookup_type => 'OZF_FCAST_SPREAD',
1941 p_lookup_code => l_act_forecast_rec.forecast_spread_type
1942 ) = FND_API.g_false
1943 THEN
1944 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1945 THEN
1946 FND_MESSAGE.set_name('OZF', 'OZF_FSCT_INVALID_SPREAD_TYPE');
1947 FND_MSG_PUB.add;
1948 END IF;
1949
1950 x_return_status := FND_API.g_ret_sts_error;
1951 RETURN;
1952 END IF;
1953
1954
1955 END IF;
1956
1957
1958 --FORECAST_UOM_CODE
1959 /* -- This value is being validated in the jsp page thru a LOV
1960 IF l_act_forecast_rec.forecast_uom_code <> FND_API.G_MISS_CHAR THEN
1961
1962 IF OZF_Utility_PVT.check_lookup_exists(
1963 p_lookup_type => 'OZF_FCAST_UOM',
1964 p_lookup_code => l_act_forecast_rec.forecast_uom_code
1965 ) = FND_API.g_false
1966 THEN
1967 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1968 THEN
1969 FND_MESSAGE.set_name('OZF', 'OZF_FSCT_INVALID_UOM_CODE');
1970 FND_MSG_PUB.add;
1971 END IF;
1972
1973 x_return_status := FND_API.g_ret_sts_error;
1974 RETURN;
1975 END IF;
1976
1977 END IF;
1978
1979 */
1980
1981 EXCEPTION
1982 WHEN OTHERS THEN
1983 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1984 END Check_ActFcst_Items;
1985
1986 --
1987 -- Start of comments.
1988 --
1989 -- NAME
1990 -- Validate_ActFcst_Rec
1991 --
1992 -- PURPOSE
1993 -- Perform Record Level and Other business validations for forecasts.
1994 --
1995 -- NOTES
1996 --
1997 -- HISTORY
1998 -- 24-Apr-2000 tdonohoe Created.
1999 --
2000 -- End of comments.
2001
2005 x_return_status OUT NOCOPY VARCHAR2
2002 PROCEDURE Validate_ActFcst_rec(
2003 p_act_forecast_rec IN act_forecast_rec_type,
2004 p_complete_fcst_rec IN act_forecast_rec_type,
2006 )
2007 IS
2008
2009 l_act_forecast_rec act_forecast_rec_type := p_act_forecast_rec;
2010
2011 l_table_name VARCHAR2(30);
2012 l_pk_name VARCHAR2(30);
2013 l_pk_value VARCHAR2(30);
2014 l_pk_data_type VARCHAR2(30);
2015 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
2016
2017 l_return_status VARCHAR2(1);
2018
2019
2020 BEGIN
2021
2022 x_return_status := FND_API.g_ret_sts_success;
2023
2024 IF l_act_forecast_rec.arc_act_fcast_used_by <> FND_API.G_MISS_CHAR THEN
2025
2026 -- Get table_name and pk_name for the ARC qualifier.
2027 OZF_Utility_PVT.Get_Qual_Table_Name_And_PK (
2028 p_sys_qual => l_act_forecast_rec.arc_act_fcast_used_by,
2029 x_return_status => l_return_status,
2030 x_table_name => l_table_name,
2031 x_pk_name => l_pk_name
2032 );
2033
2034
2035 l_pk_value := l_act_forecast_rec.act_fcast_used_by_id;
2036 l_pk_data_type := OZF_Utility_PVT.G_NUMBER;
2037 l_additional_where_clause := NULL;
2038
2039 IF OZF_Utility_PVT.Check_FK_Exists (
2040 p_table_name => l_table_name
2041 ,p_pk_name => l_pk_name
2042 ,p_pk_value => l_pk_value
2043 ,p_pk_data_type => l_pk_data_type
2044 ,p_additional_where_clause => NULL
2045 ) = FND_API.G_FALSE
2046 THEN
2047 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2048 FND_MESSAGE.Set_Name ('OZF', 'OZF_FCST_INVALID_USED_BY');
2049 FND_MSG_PUB.Add;
2050 END IF;
2051
2052 x_return_status := FND_API.G_RET_STS_ERROR;
2053 RETURN;
2054 END IF;
2055 END IF;
2056
2057 EXCEPTION
2058 WHEN OTHERS THEN
2059 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2060
2061 END Validate_ActFcst_Rec;
2062
2063
2064 --
2065 -- Start of comments.
2066 --
2067 -- NAME
2068 -- Validate_ActFcst_Items
2069 --
2070 -- PURPOSE
2071 -- Perform All Item level validation for Activity metric facts.
2072 --
2073 -- NOTES
2074 --
2075 -- HISTORY
2076 -- 24-Apr-2000 tdonohoe Created.
2077 --
2078 -- End of comments.
2079
2080 PROCEDURE Validate_ActFcst_Items (
2081 p_act_forecast_rec IN act_forecast_rec_type,
2082 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2083 x_return_status OUT NOCOPY VARCHAR2
2084 )
2085 IS
2086 BEGIN
2087
2088
2089
2090 /*
2091 ****
2092 -- Need not check for req items since
2093 -- Default_ActForecast takes care of all req items
2094
2095 Check_Req_ActFcst_Items(
2096 p_act_forecast_rec => p_act_forecast_rec,
2097 x_return_status => x_return_status
2098 );
2099 IF x_return_status <> FND_API.g_ret_sts_success THEN
2100 RETURN;
2101 END IF;
2102 ****
2103 */
2104
2105
2106 Check_ActFcst_Uk_Items(
2107 p_act_forecast_rec => p_act_forecast_rec,
2108 p_validation_mode => p_validation_mode,
2109 x_return_status => x_return_status
2110 );
2111
2112 IF x_return_status <> FND_API.g_ret_sts_success THEN
2113 RETURN;
2114 END IF;
2115
2116
2117 /*
2118 ****
2119 -- Default_ActForecast takes care of all req items
2120
2121 Check_ActFcst_Items(
2122 p_act_forecast_rec => p_act_forecast_rec,
2123 x_return_status => x_return_status
2124 );
2125
2126 IF x_return_status <> FND_API.g_ret_sts_success THEN
2127 RETURN;
2128 END IF;
2129 ****
2130 */
2131
2132 END Validate_ActFcst_Items;
2133
2134
2135
2136 END OZF_ActForecast_PVT;