[Home] [Help]
PACKAGE BODY: APPS.CN_SEAS_SCHEDULES_PVT
Source
1 PACKAGE BODY cn_seas_schedules_PVT AS
2 -- $Header: cnvsschb.pls 115.6.115100.2 2004/05/20 22:14:03 sbadami ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SEAS_SCHEDULES_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvsschb.pls';
6
7
8 -- Start of comments
9 -- API name : Create_Seas_Schedule
10 -- Type : Private.
11 -- Function :
12 -- Pre-reqs : None.
13 -- Parameters :
14 -- IN : p_api_version IN NUMBER Required
15 -- p_init_msg_list IN VARCHAR2 Optional
16 -- Default = FND_API.G_FALSE
17 -- p_commit IN VARCHAR2 Optional
18 -- Default = FND_API.G_FALSE
19 -- p_validation_level IN NUMBER Optional
20 -- Default = FND_API.G_VALID_LEVEL_FULL
21 -- p_seas_schedules_tbl_type IN seas_schedules_tbl_type
22 -- OUT : x_return_status OUT VARCHAR2(1)
23 -- x_msg_count OUT NUMBER
24 -- x_msg_data OUT VARCHAR2(2000)
25 --
26 -- Version : Current version 1.0
27 --
28 --
29 --
30 -- Notes : This procedure uses the table handler CN_SEAS_SCHEDULES_PKG
31 -- and CN_SEASONALITIES_PKG to insert rows into CN_SEAS_SCHEDULES
32 -- and CN_SEASONALITIES after some validations.
33 --
34 -- End of comments
35
36
37 PROCEDURE Create_Seas_Schedule
38 ( p_api_version IN NUMBER ,
39 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
40 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
41 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
42 p_seas_schedules_rec_type IN seas_schedules_rec_type,
43 x_seas_schedule_id OUT NOCOPY NUMBER,
44 x_return_status OUT NOCOPY VARCHAR2 ,
45 x_msg_count OUT NOCOPY NUMBER ,
46 x_msg_data OUT NOCOPY VARCHAR2
47 ) IS
48 l_api_name CONSTANT VARCHAR2(30) := 'Create_Seas_Schedule';
49 l_api_version CONSTANT NUMBER := 1.0;
50 l_error_code NUMBER;
51 l_count NUMBER;
52 l_start_date DATE;
53 l_end_date DATE;
54 l_seas_schedule_id NUMBER;
55 l_cal_int_types NUMBER;
56
57 CURSOR cn_seasonalities_cur(p_period_year NUMBER) IS
58 SELECT cp.period_id,cp.period_name,ccpit.interval_number,ccpit.cal_per_int_type_id
59 FROM cn_period_statuses cp,cn_cal_per_int_types ccpit
60 WHERE period_year = p_period_year and cp.period_id = ccpit.cal_period_id and interval_type_id = -1002;
61
62
63 BEGIN
64 --DBMS_OUTPUT.PUT_LINE('Starting Create_Seas_Schedule ...');
65 -- Standard Start of API savepoint
66 SAVEPOINT Create_Seas_Schedule;
67 -- Standard call to check for call compatibility.
68 IF NOT FND_API.compatible_api_call
69 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
70 THEN
71 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
72 END IF;
73
74 -- Initialize message list if p_init_msg_list is set to TRUE.
75 IF FND_API.to_Boolean( p_init_msg_list ) THEN
76 FND_MSG_PUB.initialize;
77 END IF;
78
79 -- Initialize API return status to success
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81 -- API body
82
83 -- ***********************
84 -- VALIDATIONS
85 -- ***********************
86
87 -- Check if the inputs are valid.
88
89 -- Check the SEASONALITY SCHEDULE NAME
90 IF ( p_seas_schedules_rec_type.name is NULL ) OR
91 ( p_seas_schedules_rec_type.name = fnd_api.g_miss_char )
92 THEN
93 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
94 FND_MESSAGE.SET_NAME ('CN' , 'CN_REQ_PAR_MISSING');
95 FND_MSG_PUB.Add;
96 END IF;
97 RAISE FND_API.G_EXC_ERROR ;
98 END IF;
99
100 -- Check the SEASONALITY SCHEDULE DESCRIPTION
101 IF ( p_seas_schedules_rec_type.DESCRIPTION is NULL ) OR
102 ( p_seas_schedules_rec_type.DESCRIPTION = fnd_api.g_miss_char )
103 THEN
104 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
105 FND_MESSAGE.SET_NAME ('CN' , 'CN_REQ_PAR_MISSING');
106 FND_MSG_PUB.Add;
107 END IF;
108 RAISE FND_API.G_EXC_ERROR ;
109 END IF;
110
111 -- Check already if this seas_schedule_name exists
112 SELECT COUNT(*) INTO l_count FROM CN_SEAS_SCHEDULES WHERE UPPER(NAME) LIKE UPPER(p_seas_schedules_rec_type.name);
113 -- Insert
114 IF l_count > 0 THEN
115 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
116 FND_MESSAGE.SET_NAME ('CN' , 'CN_SEAS_NAME_EXISTS');
117 FND_MSG_PUB.Add;
118 END IF;
119 RAISE FND_API.G_EXC_ERROR ;
120 END IF;
121
122 -- INSERT TO BEGIN TO CN_SEAS_SCHEDULES
123 -- Get the Max Date of End Date and Min of Start Date for this period Year.
124 select min(start_date),max(end_date) into l_start_date,l_end_date
125 from cn_period_statuses
126 where period_year = p_seas_schedules_rec_type.period_year
127 group by period_year;
128
129 -- We can put some more error conditon checks for Start Date and End Date.
130 -- Call the table handler to insert the row into CN_SEAS_SCHEDULES Table.
131 CN_SEAS_SCHEDULES_pkg.insert_row (
132 P_SEAS_SCHEDULE_ID => p_seas_schedules_rec_type.seas_schedule_id,
133 P_NAME => p_seas_schedules_rec_type.name,
134 P_DESCRIPTION => p_seas_schedules_rec_type.description,
135 P_PERIOD_YEAR => p_seas_schedules_rec_type.period_year,
136 P_START_DATE => l_start_date,
137 P_END_DATE => l_end_date,
138 P_VALIDATION_STATUS => 'INVALID'
139 );
140
141 -- Select SEAS_SCHEDULE_ID just created to use it for insertion of
142 -- rows into CN_SEASONALITIES.
143
144 SELECT SEAS_SCHEDULE_ID INTO l_seas_schedule_id
145 FROM CN_SEAS_SCHEDULES WHERE NAME like p_seas_schedules_rec_type.name;
146
147 x_seas_schedule_id := l_seas_schedule_id;
148 --DBMS_OUTPUT.PUT_LINE('Inserted Row and SEAS SCHEDULE ID is : ' || l_seas_schedule_id || 'About to get rows for ' || p_seas_schedules_rec_type.period_year);
149
150
151 SELECT COUNT(*) INTO l_cal_int_types
152 FROM cn_period_statuses cp,cn_cal_per_int_types ccpit
153 WHERE period_year = p_seas_schedules_rec_type.period_year and cp.period_id = ccpit.cal_period_id and interval_type_id = -1002;
154
155 IF (l_cal_int_types = 0) THEN
156 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
157 FND_MESSAGE.SET_NAME ('CN' , 'CN_SEAS_INTTYPE_NOTEXIST');
158 FND_MSG_PUB.Add;
159 END IF;
160 RAISE FND_API.G_EXC_ERROR ;
161 END IF;
162
163
164 -- Create a CURSOR OF VALUES FOR the Period and use table handler
165 -- to insert rows into the CN_SEASONALITIES.
166 FOR l_seas_sch IN cn_seasonalities_cur(p_seas_schedules_rec_type.period_year) LOOP
167 --DBMS_OUTPUT.PUT_LINE('In the Loop');
168 CN_SEASONALITIES_pkg.insert_row(
169 P_SEASONALITY_ID => -99,
170 P_SEAS_SCHEDULE_ID => l_seas_schedule_id,
171 P_CAL_PER_INT_TYPE_ID => l_seas_sch.CAL_PER_INT_TYPE_ID,
172 P_PERIOD_ID => l_seas_sch.PERIOD_ID,
173 P_PCT_SEASONALITY => 0.0
174 );
175 END LOOP;
176
177 --DBMS_OUTPUT.PUT_LINE('Created rows successfully in CN_SEASONALITIES');
178 -- End of API body.
179 << end_Create_Seas_Schedule >>
180 NULL;
181
182 -- Standard check of p_commit.
183 IF FND_API.To_Boolean( p_commit ) THEN
184 COMMIT WORK;
185 END IF;
186 -- Standard call to get message count and if count is 1, get message info.
187 FND_MSG_PUB.Count_And_Get
188 (
189 p_count => x_msg_count ,
190 p_data => x_msg_data ,
191 p_encoded => FND_API.G_FALSE
192 );
193
194 EXCEPTION
195 WHEN FND_API.G_EXC_ERROR THEN
196 ROLLBACK TO Create_Seas_Schedule ;
197 x_return_status := FND_API.G_RET_STS_ERROR ;
198 FND_MSG_PUB.Count_And_Get
199 (
200 p_count => x_msg_count ,
201 p_data => x_msg_data ,
202 p_encoded => FND_API.G_FALSE
203 );
204
205 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
206 ROLLBACK TO Create_Seas_Schedule ;
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
208 FND_MSG_PUB.Count_And_Get
209 (
210 p_count => x_msg_count ,
211 p_data => x_msg_data ,
212 p_encoded => FND_API.G_FALSE
213 );
214 WHEN OTHERS THEN
215 ROLLBACK TO Create_Seas_Schedule ;
216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
217 l_error_code := SQLCODE;
218 IF l_error_code = -54 THEN
219 x_return_status := FND_API.G_RET_STS_ERROR ;
220 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
221 THEN
222 FND_MESSAGE.SET_NAME ('CN' , 'CN_LOCK_FAIL');
223 FND_MSG_PUB.Add;
224 END IF;
225 ELSE
226 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
227 THEN
228 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
229 END IF;
230 END IF;
231 FND_MSG_PUB.Count_And_Get
232 (
233 p_count => x_msg_count ,
234 p_data => x_msg_data ,
235 p_encoded => FND_API.G_FALSE
236 );
237 END Create_Seas_Schedule;
238
239
240 -- Start of comments
241 -- API name : Update_Seas_Schedule
242 -- Type : Private.
243 -- Function :
244 -- Pre-reqs : None.
245 -- Parameters :
246 -- IN : p_api_version IN NUMBER Required
247 -- p_init_msg_list IN VARCHAR2 Optional
248 -- Default = FND_API.G_FALSE
249 -- p_commit IN VARCHAR2 Optional
250 -- Default = FND_API.G_FALSE
251 -- p_validation_level IN NUMBER Optional
252 -- Default = FND_API.G_VALID_LEVEL_FULL
253 -- p_seas_schedules_tbl_type IN seas_schedules_tbl_type
254 -- OUT : x_return_status OUT VARCHAR2(1)
255 -- x_msg_count OUT NUMBER
256 -- x_msg_data OUT VARCHAR2(2000)
257 --
258 -- Version : Current version 1.0
259 --
260 --
261 --
262 -- Notes : This procedure uses the table handler CN_SEAS_SCHEDULES_PKG
263 -- to update rows into CN_SEAS_SCHEDULES after some validations.
264 --
265 -- End of comments
266
267 PROCEDURE Update_Seas_Schedule
268 ( p_api_version IN NUMBER ,
269 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
270 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
271 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
272 p_seas_schedules_rec_type IN seas_schedules_rec_type,
273 x_return_status OUT NOCOPY VARCHAR2 ,
274 x_msg_count OUT NOCOPY NUMBER ,
275 x_msg_data OUT NOCOPY VARCHAR2
276 ) IS
277 l_api_name CONSTANT VARCHAR2(30) := 'Update_Seas_Schedule';
278 l_api_version CONSTANT NUMBER := 1.0;
279 l_error_code NUMBER;
280 l_count NUMBER;
281 l_validation_status VARCHAR2(30);
282 l_start_date date;
283 l_end_date date;
284 l_srp_count NUMBER;
285
286 BEGIN
287 --DBMS_OUTPUT.PUT_LINE('Update in progress');
288 -- Standard Start of API savepoint
289 SAVEPOINT Update_Seas_Schedule;
290 -- Standard call to check for call compatibility.
291 IF NOT FND_API.compatible_api_call
292 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
293 THEN
294 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
295 END IF;
296
297 -- Initialize message list if p_init_msg_list is set to TRUE.
298 IF FND_API.to_Boolean( p_init_msg_list ) THEN
299 FND_MSG_PUB.initialize;
300 END IF;
301 -- Initialize API return status to success
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303 -- API body
304
305 select COUNT(*) INTO l_srp_count from cn_srp_role_dtls t1,cn_role_quota_cates t2,cn_srp_roles t3
306 where t1.srp_role_id = t3.srp_role_id
307 and t2.role_id = t3.role_id
308 and t1.status not in ('PENDING')
309 and t2.seas_schedule_id = p_seas_schedules_rec_type.seas_schedule_id;
310
311
312 IF (l_srp_count > 0) THEN
313 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
314 FND_MESSAGE.SET_NAME ('CN' , 'CN_SEASONALITY_IN_USE');
315 FND_MSG_PUB.Add;
316 END IF;
317 RAISE FND_API.G_EXC_ERROR ;
318 END IF;
319
320 -- ****************************
321 -- VALIDATIONS/CHECK FOR NULLS
322 -- ****************************
323 -- Check the SEASONALITY SCHEDULE NAME
324 IF ( p_seas_schedules_rec_type.name is NULL ) OR
325 ( p_seas_schedules_rec_type.name = fnd_api.g_miss_char )
326 THEN
327 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
328 FND_MESSAGE.SET_NAME ('CN' , 'CN_REQ_PAR_MISSING');
329 FND_MSG_PUB.Add;
330 END IF;
331 RAISE FND_API.G_EXC_ERROR ;
332 END IF;
333
334 -- Check the SEASONALITY SCHEDULE DESCRIPTION
335 IF ( p_seas_schedules_rec_type.DESCRIPTION is NULL ) OR
336 ( p_seas_schedules_rec_type.DESCRIPTION = fnd_api.g_miss_char )
337 THEN
338 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
339 FND_MESSAGE.SET_NAME ('CN' , 'CN_REQ_PAR_MISSING');
340 FND_MSG_PUB.Add;
341 END IF;
342 RAISE FND_API.G_EXC_ERROR ;
343 END IF;
344
345 -- Check already if this seas_schedule_name exists
346 SELECT COUNT(*) INTO l_count FROM CN_SEAS_SCHEDULES WHERE UPPER(NAME) LIKE UPPER(p_seas_schedules_rec_type.name) AND SEAS_SCHEDULE_ID <> p_seas_schedules_rec_type.seas_schedule_id;
347 -- Insert
348 IF l_count > 0 THEN
349 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
350 FND_MESSAGE.SET_NAME ('CN' , 'CN_SEAS_NAME_EXISTS');
351 FND_MSG_PUB.Add;
352 END IF;
353 RAISE FND_API.G_EXC_ERROR ;
354 END IF;
355 --DBMS_OUTPUT.PUT_LINE('Update Validations done.');
356 -- CALL THE UPDATE API
357 -- l_validation_status := FND_API.G_MISS_CHAR;
358 l_validation_status := p_seas_schedules_rec_type.validation_status;
359 if ((l_validation_status <> 'VALID') AND (l_validation_status <> 'INVALID')) THEN
360 l_validation_status := FND_API.G_MISS_CHAR;
361 END IF;
362
363 l_start_date := FND_API.G_MISS_DATE;
364 l_end_date := FND_API.G_MISS_DATE;
365
366 --DBMS_OUTPUT.PUT_LINE('SEAS ID : ' || p_seas_schedules_rec_type.seas_schedule_id);
367 --DBMS_OUTPUT.PUT_LINE('NAME : ' || p_seas_schedules_rec_type.name);
368 --DBMS_OUTPUT.PUT_LINE('DESCRIPTION : ' || p_seas_schedules_rec_type.description);
369 --DBMS_OUTPUT.PUT_LINE('PERIOD YEAR : ' || p_seas_schedules_rec_type.period_year);
370 --DBMS_OUTPUT.PUT_LINE('START DATE : ' || p_seas_schedules_rec_type.start_date);
371 --DBMS_OUTPUT.PUT_LINE('END DATE : ' || p_seas_schedules_rec_type.end_date);
372 --DBMS_OUTPUT.PUT_LINE('OVN : ' || p_seas_schedules_rec_type.object_version_number);
373 --DBMS_OUTPUT.PUT_LINE('VALIDATION STATUS : ' || l_validation_status);
374
375 CN_SEAS_SCHEDULES_pkg.update_row
376 (
377 P_SEAS_SCHEDULE_ID => p_seas_schedules_rec_type.seas_schedule_id,
378 P_NAME => p_seas_schedules_rec_type.name,
379 P_DESCRIPTION => p_seas_schedules_rec_type.description,
380 P_PERIOD_YEAR => p_seas_schedules_rec_type.period_year,
381 P_START_DATE => l_start_date,
382 P_END_DATE => l_end_date,
383 P_VALIDATION_STATUS => l_validation_status,
384 p_object_version_number => p_seas_schedules_rec_type.object_version_number
385 );
386
387
388 -- End of API body.
389 << end_Update_Seas_Schedule >>
390 NULL;
391
392 -- Standard check of p_commit.
393 IF FND_API.To_Boolean( p_commit ) THEN
394 COMMIT WORK;
395 END IF;
396 -- Standard call to get message count and if count is 1, get message info.
397 FND_MSG_PUB.Count_And_Get
398 (
399 p_count => x_msg_count ,
400 p_data => x_msg_data ,
401 p_encoded => FND_API.G_FALSE
402 );
403
404 EXCEPTION
405 WHEN FND_API.G_EXC_ERROR THEN
406 ROLLBACK TO Update_Seas_Schedule ;
407 x_return_status := FND_API.G_RET_STS_ERROR ;
408 FND_MSG_PUB.Count_And_Get
409 (
410 p_count => x_msg_count ,
411 p_data => x_msg_data ,
412 p_encoded => FND_API.G_FALSE
413 );
414
415 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
416 ROLLBACK TO Update_Seas_Schedule ;
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
418 FND_MSG_PUB.Count_And_Get
419 (
420 p_count => x_msg_count ,
421 p_data => x_msg_data ,
422 p_encoded => FND_API.G_FALSE
423 );
424 WHEN OTHERS THEN
425 ROLLBACK TO Update_Seas_Schedule ;
426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
427 l_error_code := SQLCODE;
428 IF l_error_code = -54 THEN
429 x_return_status := FND_API.G_RET_STS_ERROR ;
430 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
431 THEN
432 FND_MESSAGE.SET_NAME ('CN' , 'CN_LOCK_FAIL');
433 FND_MSG_PUB.Add;
434 END IF;
435 ELSE
436 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
437 THEN
438 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
439 END IF;
440 END IF;
441 FND_MSG_PUB.Count_And_Get
442 (
443 p_count => x_msg_count ,
444 p_data => x_msg_data ,
445 p_encoded => FND_API.G_FALSE
446 );
447 END Update_Seas_Schedule;
448
449
450 -- Start of comments
451 -- API name : Delete_Seas_Schedule
452 -- Type : Private.
453 -- Function :
454 -- Pre-reqs : None.
455 -- Parameters :
456 -- IN : p_api_version IN NUMBER Required
457 -- p_init_msg_list IN VARCHAR2 Optional
458 -- Default = FND_API.G_FALSE
459 -- p_commit IN VARCHAR2 Optional
460 -- Default = FND_API.G_FALSE
461 -- p_validation_level IN NUMBER Optional
462 -- Default = FND_API.G_VALID_LEVEL_FULL
463 -- P_SEAS_SCHEDULE_ID IN NUMBER Required
464 --
465 -- OUT : x_return_status OUT VARCHAR2(1)
466 -- x_msg_count OUT NUMBER
467 -- x_msg_data OUT VARCHAR2(2000)
468 --
469 --
470 -- Version : Current version 1.0
471 --
472 --
473 --
474 -- Notes : This procedure uses the table handler CN_SEAS_SCHEDULES_PKG
475 -- and CN_SEASONALITIES_PKG to delete rows into CN_SEAS_SCHEDULES
476 -- and CN_SEASONALITIES after the validations are done.
477 --
478 -- End of comments
479
480 PROCEDURE Delete_Seas_Schedule
481 ( p_api_version IN NUMBER ,
482 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
483 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
484 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
485 P_SEAS_SCHEDULE_ID IN cn_seas_schedules.seas_schedule_id%TYPE,
486 x_return_status OUT NOCOPY VARCHAR2 ,
487 x_msg_count OUT NOCOPY NUMBER ,
488 x_msg_data OUT NOCOPY VARCHAR2
489 ) IS
490 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Seas_Schedule';
491 l_api_version CONSTANT NUMBER := 1.0;
492 l_error_code NUMBER;
493 l_seas_sch_id NUMBER;
494 l_role_quota_cate_count NUMBER;
495 BEGIN
496 --DBMS_OUTPUT.PUT_LINE('Delete in progress');
497 -- Standard Start of API savepoint
498 SAVEPOINT Delete_Seas_Schedule;
499 -- Standard call to check for call compatibility.
500 IF NOT FND_API.compatible_api_call
501 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
502 THEN
503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
504 END IF;
505
506 -- Initialize message list if p_init_msg_list is set to TRUE.
507 IF FND_API.to_Boolean( p_init_msg_list ) THEN
508 FND_MSG_PUB.initialize;
509 END IF;
510 -- Initialize API return status to success
511 x_return_status := FND_API.G_RET_STS_SUCCESS;
512
513 -- API body
514 l_seas_sch_id := P_SEAS_SCHEDULE_ID;
515 -- ********************
516 -- VALIDATION
517 -- ********************
518 -- Validations to be checked before we decide to delete the entries
519 -- from CN_SEAS_SCHEDULES and CN_SEASONALITIES.
520 SELECT COUNT(role_id) INTO l_role_quota_cate_count FROM CN_ROLE_QUOTA_CATES WHERE seas_schedule_id = P_SEAS_SCHEDULE_ID;
521
522 IF (l_role_quota_cate_count > 0) THEN
523 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
524 FND_MESSAGE.SET_NAME ('CN' , 'CN_SEAS_DEL_FAILED');
525 FND_MSG_PUB.Add;
526 END IF;
527 RAISE FND_API.G_EXC_ERROR ;
528 END IF;
529
530 -- Delete entries from CN_SEAS_SCHEDULES
531 CN_SEAS_SCHEDULES_pkg.delete_row (P_SEAS_SCHEDULE_ID => l_seas_sch_id);
532
533 -- Delete entries from CN_SEASONALITIES.
534 CN_SEASONALITIES_pkg.delete_row (P_SEAS_SCHEDULE_ID => l_seas_sch_id);
535
536 -- End of API body.
537 << end_Delete_Seas_Schedule >>
538 NULL;
539
540 -- Standard check of p_commit.
541 IF FND_API.To_Boolean( p_commit ) THEN
542 COMMIT WORK;
543 END IF;
544 -- Standard call to get message count and if count is 1, get message info.
545 FND_MSG_PUB.Count_And_Get
546 (
547 p_count => x_msg_count ,
548 p_data => x_msg_data ,
549 p_encoded => FND_API.G_FALSE
550 );
551
552 EXCEPTION
553 WHEN FND_API.G_EXC_ERROR THEN
554 ROLLBACK TO Delete_Seas_Schedule ;
555 x_return_status := FND_API.G_RET_STS_ERROR ;
556 FND_MSG_PUB.Count_And_Get
557 (
558 p_count => x_msg_count ,
559 p_data => x_msg_data ,
560 p_encoded => FND_API.G_FALSE
561 );
562
563 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
564 ROLLBACK TO Delete_Seas_Schedule ;
565 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
566 FND_MSG_PUB.Count_And_Get
567 (
568 p_count => x_msg_count ,
569 p_data => x_msg_data ,
570 p_encoded => FND_API.G_FALSE
571 );
572 WHEN OTHERS THEN
573 ROLLBACK TO Delete_Seas_Schedule ;
574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
575 l_error_code := SQLCODE;
576 IF l_error_code = -54 THEN
577 x_return_status := FND_API.G_RET_STS_ERROR ;
578 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
579 THEN
580 FND_MESSAGE.SET_NAME ('CN' , 'CN_LOCK_FAIL');
581 FND_MSG_PUB.Add;
582 END IF;
583 ELSE
584 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
585 THEN
586 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
587 END IF;
588 END IF;
589 FND_MSG_PUB.Count_And_Get
590 (
591 p_count => x_msg_count ,
592 p_data => x_msg_data ,
593 p_encoded => FND_API.G_FALSE
594 );
595 END Delete_Seas_Schedule;
596
597 -- Start of comments
598 -- API name : Sum_Seas_Schedule
599 -- Type : Private.
600 -- Function :
601 -- Pre-reqs : None.
602 -- Parameters :
603 -- IN : p_api_version IN NUMBER Required
604 -- p_init_msg_list IN VARCHAR2 Optional
605 -- Default = FND_API.G_FALSE
606 -- p_commit IN VARCHAR2 Optional
607 -- Default = FND_API.G_FALSE
608 -- p_validation_level IN NUMBER Optional
609 -- Default = FND_API.G_VALID_LEVEL_FULL
610 -- P_SEAS_SCHEDULE_ID IN NUMBER Required
611 --
612 -- OUT : x_seas_schedule_sum OUT NUMBER,
613 -- x_return_status OUT VARCHAR2(1)
614 -- x_msg_count OUT NUMBER
615 -- x_msg_data OUT VARCHAR2(2000)
616 --
617 --
618 -- Version : Current version 1.0
619 --
620 --
621 --
622 -- Notes : This procedures find the sum of seasonalities
623 -- End of comments
624
625 PROCEDURE Sum_Seas_Schedule
626 ( p_api_version IN NUMBER ,
627 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
628 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
629 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
630 p_seas_schedules_id IN NUMBER,
631 x_seas_schedule_sum OUT NOCOPY NUMBER,
632 x_return_status OUT NOCOPY VARCHAR2 ,
633 x_msg_count OUT NOCOPY NUMBER ,
634 x_msg_data OUT NOCOPY VARCHAR2
635 ) IS
636 l_api_name CONSTANT VARCHAR2(30) := 'Sum_Seas_Schedule';
637 l_api_version CONSTANT NUMBER := 1.0;
638 l_error_code NUMBER;
639 l_seas_schedule_id NUMBER;
640 l_pct_seasonality NUMBER;
641
642 BEGIN
643 -- Standard Start of API savepoint
644 SAVEPOINT Sum_Seas_Schedule;
645 -- Standard call to check for call compatibility.
646 IF NOT FND_API.compatible_api_call
647 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
648 THEN
649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
650 END IF;
651
652 -- Initialize message list if p_init_msg_list is set to TRUE.
653 IF FND_API.to_Boolean( p_init_msg_list ) THEN
654 FND_MSG_PUB.initialize;
655 END IF;
656 -- Initialize API return status to success
657 x_return_status := FND_API.G_RET_STS_SUCCESS;
658
659 -- API body
660 select seas_schedule_id,sum(pct_seasonality) into l_seas_schedule_id,l_pct_seasonality
661 from cn_seasonalities
662 where seas_schedule_id = p_seas_schedules_id group by seas_schedule_id;
663
664 x_seas_schedule_sum := l_pct_seasonality;
665
666 -- End of API body.
667 << end_Delete_Seas_Schedule >>
668 NULL;
669
670 -- Standard check of p_commit.
671 IF FND_API.To_Boolean( p_commit ) THEN
672 COMMIT WORK;
673 END IF;
674 -- Standard call to get message count and if count is 1, get message info.
675 FND_MSG_PUB.Count_And_Get
676 (
677 p_count => x_msg_count ,
678 p_data => x_msg_data ,
679 p_encoded => FND_API.G_FALSE
680 );
681
682 EXCEPTION
683 WHEN FND_API.G_EXC_ERROR THEN
684 ROLLBACK TO Sum_Seas_Schedule ;
685 x_return_status := FND_API.G_RET_STS_ERROR ;
686 FND_MSG_PUB.Count_And_Get
687 (
688 p_count => x_msg_count ,
689 p_data => x_msg_data ,
690 p_encoded => FND_API.G_FALSE
691 );
692
693 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
694 ROLLBACK TO Sum_Seas_Schedule ;
695 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
696 FND_MSG_PUB.Count_And_Get
697 (
698 p_count => x_msg_count ,
699 p_data => x_msg_data ,
700 p_encoded => FND_API.G_FALSE
701 );
702 WHEN OTHERS THEN
703 ROLLBACK TO Sum_Seas_Schedule ;
704 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
705 l_error_code := SQLCODE;
706 IF l_error_code = -54 THEN
707 x_return_status := FND_API.G_RET_STS_ERROR ;
708 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
709 THEN
710 FND_MESSAGE.SET_NAME ('CN' , 'CN_LOCK_FAIL');
711 FND_MSG_PUB.Add;
712 END IF;
713 ELSE
714 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
715 THEN
716 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
717 END IF;
718 END IF;
719 FND_MSG_PUB.Count_And_Get
720 (
721 p_count => x_msg_count ,
722 p_data => x_msg_data ,
723 p_encoded => FND_API.G_FALSE
724 );
725 END Sum_Seas_Schedule;
726
727 END cn_seas_schedules_pvt;