1 PACKAGE BODY IGS_PS_SCH_INT_API_PUB AS
2 /* $Header: IGSPS80B.pls 120.1 2005/06/29 04:06:28 appldev ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'IGS_PS_SCH_INT_API_PUB';
5 g_n_user_id igs_ps_unit_ver_all.created_by%TYPE := NVL(fnd_global.user_id,-1); -- Stores the User Id
6 g_n_login_id igs_ps_unit_ver_all.last_update_login%TYPE := NVL(fnd_global.login_id,-1); -- Stores the Login Id
7
8
9 PROCEDURE Insert_schedule( p_api_version IN NUMBER,
10 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
11 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
12 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_msg_count OUT NOCOPY NUMBER,
15 x_msg_data OUT NOCOPY VARCHAR2,
16 p_transaction_type IN VARCHAR2,
17 p_cal_type IN VARCHAR2,
18 p_sequence_number IN NUMBER,
19 p_cal_start_dt IN DATE,
20 p_cal_end_dt IN DATE,
21 p_uoo_id IN NUMBER,
22 p_unit_section_occurrence_id IN NUMBER,
23 p_start_time IN DATE,
24 p_end_time IN DATE,
25 p_building_id IN NUMBER,
26 p_room_id IN NUMBER,
27 p_schedule_status IN VARCHAR2,
28 p_error_text IN VARCHAR2,
29 p_org_id IN NUMBER,
30 p_uso_start_date IN DATE,
31 p_uso_end_date IN DATE,
32 p_sunday IN VARCHAR2,
33 p_monday IN VARCHAR2,
34 p_tuesday IN VARCHAR2,
35 p_wednesday IN VARCHAR2,
36 p_thursday IN VARCHAR2,
37 p_friday IN VARCHAR2,
38 p_saturday IN VARCHAR2
39 ) AS
40
41 /***********************************************************************************************
42 Created By: schodava
43 Date Created By: 12-06-2001
44 Purpose: This procedure is used to insert records in the Scheduling interface tables.
45 Known limitations,enhancements,remarks:
46 Change History
47 Who When What
48 jbegum 22-APR-2003 Enh bug#2833850
49 Added following parameters
50 p_uso_start_date,p_uso_end_date,p_sunday,p_monday,p_tuesday,p_wednesday,p_thursday,p_friday,p_saturday
51 smvk 10-Feb-2003 Bug # 2803385. Modified the variable buiding_code and room_code as building_id and
52 room_id respectively.
53 ********************************************************************************************** */
54
55 l_api_name CONSTANT VARCHAR2(30) := 'Insert_sch';
56 l_api_version CONSTANT NUMBER := 1.0;
57
58 -- Cursor added as part of Enh bug#2833850
59 CURSOR cur_usec(cp_n_usec_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
60 SELECT *
61 FROM igs_ps_usec_occurs_all
62 WHERE unit_section_occurrence_id = cp_n_usec_id;
63
64 -- Cursor added as part of Enh bug#2833850
65 CURSOR c_cal(cp_c_cal_type igs_ca_inst.cal_type%TYPE,
66 cp_n_sequence_number igs_ca_inst.sequence_number%TYPE) IS
67 SELECT start_dt ,end_dt
68 FROM igs_ca_inst
69 WHERE cal_type = cp_c_cal_type AND
70 sequence_number = cp_n_sequence_number;
71
72 -- Cursor added as part of Enh bug#2833850
73 CURSOR c_bld(cp_n_building_id igs_ad_building_all.building_id%TYPE) IS
74 SELECT 'x'
75 FROM igs_ad_building_all
76 WHERE building_id = cp_n_building_id;
77
78 -- Cursor added as part of Enh bug#2833850
79 CURSOR c_room(cp_n_room_id igs_ad_room_all.room_id%TYPE) IS
80 SELECT 'x'
81 FROM igs_ad_room_all
82 WHERE room_id = cp_n_room_id;
83
84 -- Cursor added as part of Enh bug#2833850
85 CURSOR c_bld_room(cp_n_room_id igs_ad_room_all.room_id%TYPE,
86 cp_n_building_id igs_ad_building_all.building_id%TYPE) IS
87
88 SELECT 'x'
89 FROM igs_ad_room_all
90 WHERE room_id = cp_n_room_id AND
91 building_id = cp_n_building_id;
92
93 -- Local variables added as part of Enh bug#2833850
94 rec_bld c_bld%ROWTYPE;
95 rec_room c_room%ROWTYPE;
96 rec_bld_room c_bld_room%ROWTYPE;
97 rec_cal c_cal%ROWTYPE;
98
99 BEGIN
100
101 -- Savepoint
102 SAVEPOINT Insert_schedule_pub;
103
104 -- Check if the API call is compatible
105 IF NOT FND_API.Compatible_API_Call( l_api_version,
106 p_api_version,
107 l_api_name,
108 g_pkg_name) THEN
109
110 -- If the call is incompatible, then raise the unexpected error
111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
112
113 END IF;
114
115 -- If the p_init_msg_list is T, i.e. the calling program wants to initialise
116 -- the message list, then the message list is initialised using the API call
117 IF FND_API.To_Boolean(p_init_msg_list) THEN
118 FND_MSG_PUB.Initialize;
119 END IF;
120
121 -- Set the return status as success for the api
122 x_return_status := FND_API.G_RET_STS_SUCCESS;
123
124
125 -- Enh bug#2833850
126 -- Deriving teaching period start date and end date from the parameters p_cal_type , p_sequence_number
127
128 OPEN c_cal(p_cal_type,p_sequence_number);
129 FETCH c_cal INTO rec_cal;
130 CLOSE c_cal;
131
132 -- Enh bug#2833850
133 -- Following validations have been added to validate the parameter values passed to insert scheduling api by third party software.
134 -- These validations will ensure that the interface records inserted by the third party software thru insert scheduling api will
135 -- get sucessfully imported to the production table of OSS
136
137
138 FOR cur_usec_rec IN cur_usec(p_unit_section_occurrence_id) LOOP
139
140 -- Validation 0
141 -- Transaction type should have a valid value
142 IF p_transaction_type IS NULL OR
143 p_transaction_type NOT IN ('REQUEST','UPDATE','CANCEL') THEN
144 FND_MESSAGE.Set_Name('IGS','IGS_FI_INVALID_TXN_TYPE');
145 FND_MESSAGE.Set_Token('TXN_TYPE',p_transaction_type);
146 FND_MSG_PUB.Add;
147 x_return_status := FND_API.G_RET_STS_ERROR;
148 END IF;
149
150 -- Validation 1
151 -- Error text is mandatory for unit section occurrence scheduled as Error.
152
153 IF p_schedule_status = 'ERROR' AND p_error_text IS NULL THEN
154 FND_MESSAGE.Set_Name('IGS','IGS_PS_SCH_ERR_TEXT_NULL');
155 FND_MSG_PUB.Add;
156 x_return_status := FND_API.G_RET_STS_ERROR;
157 END IF;
158
159 -- Validation 2
160 -- Building identifier are mandatory for successfully scheduled unit section occurrence
161
162 IF p_schedule_status = 'OK' AND
163 (p_building_id IS NULL) AND
164 (p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
165 FND_MESSAGE.Set_Name('IGS','IGS_PS_SCH_BD_OR_RM_NULL');
166 FND_MSG_PUB.Add;
167 x_return_status := FND_API.G_RET_STS_ERROR;
168 END IF;
169
170 -- Validation 3
171 -- Building and room identifier should be null for successfully cancelled unit section occurrence
172
173 IF p_schedule_status = 'OK' AND
174 (p_building_id IS NOT NULL OR p_room_id IS NOT NULL) AND
175 p_transaction_type = 'CANCEL' THEN
176 FND_MESSAGE.Set_Name('IGS','IGS_PS_SCH_BD_OR_RM_NOT_NULL');
177 FND_MSG_PUB.Add;
178 x_return_status := FND_API.G_RET_STS_ERROR;
179 END IF;
180
181
182 -- Validation 4
183 -- Checking if the scheduling done through third party software is conflicting with the existing schedule
184
185 IF cur_usec_rec.schedule_status IN ('PROCESSING','USER_UPDATE','USER_CANCEL') THEN
186 FND_MESSAGE.Set_Name('IGS','IGS_PS_CONFLICT_SCHD');
187 FND_MSG_PUB.Add;
188 x_return_status := FND_API.G_RET_STS_ERROR;
189 END IF;
190
191
192 -- Validation 5
193 -- Unit Section Occurrence start date must be lesser than or equal to Unit Section Occurrence end date
194
195 IF p_schedule_status = 'OK' AND
196 p_uso_start_date IS NOT NULL AND
197 p_uso_end_date IS NOT NULL AND
198 p_uso_start_date > p_uso_end_date AND
199 cur_usec_rec.to_be_announced = 'Y' AND
200 (p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
201 FND_MESSAGE.Set_Name('IGS','IGS_PE_EDT_LT_SDT');
202 FND_MSG_PUB.Add;
203 x_return_status := FND_API.G_RET_STS_ERROR;
204 END IF;
205
206 -- Validation 6
207 -- Unit Section Occurrence Start Date should be greater than or equal to Unit Section Start Date
208
209 IF p_schedule_status = 'OK' AND
210 p_uso_start_date IS NOT NULL AND
211 p_uso_start_date < p_cal_start_dt AND
212 cur_usec_rec.to_be_announced = 'Y' AND
213 (p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
214 FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_STDT_GE_US_STDT');
215 FND_MSG_PUB.Add;
216 x_return_status := FND_API.G_RET_STS_ERROR;
217 END IF;
218
219 -- Validation 7
220 -- Unit Section Occurrence Start Date should be greater than or equal to Teaching Period Start Date
221
222 IF p_schedule_status = 'OK' AND
223 p_uso_start_date IS NOT NULL AND
224 p_cal_start_dt IS NULL AND
225 p_uso_start_date < rec_cal.start_dt AND
226 cur_usec_rec.to_be_announced = 'Y' AND
227 (p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
228 FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_STDT_GE_TP_STDT');
229 FND_MSG_PUB.Add;
230 x_return_status := FND_API.G_RET_STS_ERROR;
231 END IF;
232
233 -- Validation 8
234 -- Unit Section Occurrence Start Date must be less than or equal to Unit Section End Date
235
236 IF p_schedule_status = 'OK' AND
237 p_uso_start_date IS NOT NULL AND
238 p_uso_start_date > p_cal_end_dt AND
239 cur_usec_rec.to_be_announced = 'Y' AND
240 (p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
241 FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_ST_DT_UOO_END_DT');
242 FND_MSG_PUB.Add;
243 x_return_status := FND_API.G_RET_STS_ERROR;
244 END IF;
245
246 -- Validation 9
247 -- Unit Section Occurrence Start Date must be less than or equal to Teaching Period End Date
248
249 IF p_schedule_status = 'OK' AND
250 p_uso_start_date IS NOT NULL AND
251 p_cal_end_dt IS NULL AND
252 p_uso_start_date > rec_cal.end_dt AND
253 cur_usec_rec.to_be_announced = 'Y' AND
254 (p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
255 FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_ST_DT_TP_END_DT');
256 FND_MSG_PUB.Add;
257 x_return_status := FND_API.G_RET_STS_ERROR;
258 END IF;
259
260 -- Validation 10
261 -- Unit Section Occurrence End Date should be greater than or equal to Unit Section Start Date
262
263 IF p_schedule_status = 'OK' AND
264 p_uso_end_date IS NOT NULL AND
265 p_uso_end_date < p_cal_start_dt AND
266 cur_usec_rec.to_be_announced = 'Y' AND
267 (p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
268 FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_END_DT_UOO_ST_DT');
269 FND_MSG_PUB.Add;
270 x_return_status := FND_API.G_RET_STS_ERROR;
271 END IF;
272
273 -- Validation 11
274 -- Unit Section Occurrence End Date should be greater than or equal to Teaching Period Start Date
275
276 IF p_schedule_status = 'OK' AND
277 p_uso_end_date IS NOT NULL AND
278 p_cal_start_dt IS NULL AND
279 p_uso_end_date < rec_cal.start_dt AND
280 cur_usec_rec.to_be_announced = 'Y' AND
281 (p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
282 FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_END_DT_TP_ST_DT');
283 FND_MSG_PUB.Add;
284 x_return_status := FND_API.G_RET_STS_ERROR;
285 END IF;
286
287 -- Validation 12
288 -- Unit Section Occurrence End Date must be less than or equal to Unit Section End Date
289
290 IF p_schedule_status = 'OK' AND
291 p_uso_end_date IS NOT NULL AND
292 p_uso_end_date > p_cal_end_dt AND
293 cur_usec_rec.to_be_announced = 'Y' AND
294 (p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
295 FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_ENDT_LE_US_ENDT');
296 FND_MSG_PUB.Add;
297 x_return_status := FND_API.G_RET_STS_ERROR;
298 END IF;
299
300 -- Validation 13
301 -- Unit Section Occurrence end Date must be less than or equal to Teaching Period End Date
302
303 IF p_schedule_status = 'OK' AND
304 p_uso_end_date IS NOT NULL AND
305 p_cal_end_dt IS NULL AND
306 p_uso_end_date > rec_cal.end_dt AND
307 cur_usec_rec.to_be_announced = 'Y' AND
308 (p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
309 FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_ENDT_LE_TP_ENDT');
310 FND_MSG_PUB.Add;
311 x_return_status := FND_API.G_RET_STS_ERROR;
312 END IF;
313
314 -- Validation 14
315 -- Invalid Scheduled Building Identifier
316
317 IF p_schedule_status = 'OK' AND
318 p_building_id IS NOT NULL AND
319 (p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
320 OPEN c_bld(p_building_id);
321 FETCH c_bld INTO rec_bld;
322 IF c_bld%NOTFOUND THEN
323 FND_MESSAGE.Set_Name('IGS','IGS_PS_BUILDING_ID_INVALID');
324 FND_MSG_PUB.Add;
325 x_return_status := FND_API.G_RET_STS_ERROR;
326 END IF;
327 CLOSE c_bld;
328 END IF;
329
330 -- Validation 15
331 -- Invalid Scheduled Room Identifier
332
333 IF p_schedule_status = 'OK' AND
334 p_room_id IS NOT NULL AND
335 (p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
336 OPEN c_room(p_room_id);
337 FETCH c_room INTO rec_room;
338 IF c_room%NOTFOUND THEN
339 FND_MESSAGE.Set_Name('IGS','IGS_PS_ROOM_ID_INVALID');
340 FND_MSG_PUB.Add;
341 x_return_status := FND_API.G_RET_STS_ERROR;
342 END IF;
343 CLOSE c_room;
344 END IF;
345
346 -- Validation 16
347 -- Scheduled room does not belong to scheduled building
348
349 IF p_schedule_status = 'OK' AND
350 p_building_id IS NOT NULL AND
351 p_room_id IS NOT NULL AND
352 (p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
356 FND_MESSAGE.Set_Name('IGS','IGS_PS_ROOM_INV_FOR_BLD');
353 OPEN c_bld_room(p_room_id,p_building_id);
354 FETCH c_bld_room INTO rec_bld_room;
355 IF c_bld_room%NOTFOUND THEN
357 FND_MSG_PUB.Add;
358 x_return_status := FND_API.G_RET_STS_ERROR;
359 END IF;
360 CLOSE c_bld_room;
361
362 END IF;
363
364 -- Validation 17
365 -- At least one day of the week should be checked
366
367 IF p_schedule_status = 'OK' AND
368 NVL(p_sunday,'N') = 'N' AND
369 NVL(p_monday,'N') = 'N' AND
370 NVL(p_tuesday,'N') = 'N' AND
371 NVL(p_wednesday,'N') = 'N' AND
372 NVL(p_thursday,'N') = 'N' AND
373 NVL(p_friday,'N') = 'N' AND
374 NVL(p_saturday,'N') = 'N' AND
375 cur_usec_rec.to_be_announced = 'Y' AND
376 (p_transaction_type = 'UPDATE' OR p_transaction_type = 'REQUEST') THEN
377 FND_MESSAGE.Set_Name('IGS','IGS_PS_ATLEAST_ONE_DAY_CHECK');
378 FND_MSG_PUB.Add;
379 x_return_status := FND_API.G_RET_STS_ERROR;
380 END IF;
381
382 -- Raising an exception if any any of the above validations fail
383
384 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
385 RAISE FND_API.G_EXC_ERROR;
386 END IF;
387
388 -- Records have to be inserted into the Schedule Interface Tables.
389
390 DECLARE
391 l_trans_id igs_ps_sch_hdr_int.transaction_id%TYPE;
392 l_int_pat_id NUMBER;
393 l_int_usec_id igs_ps_sch_usec_int_all.int_usec_id%TYPE ;
394 BEGIN
395 -- create a header record for the process.
396 BEGIN
397 INSERT INTO igs_ps_sch_hdr_int (
398 transaction_id ,
399 originator ,
400 request_date ,
401 org_id
402 )
403 VALUES (
404 IGS_PS_SCH_HDR_INT_S.NEXTVAL,
405 'EXTERNAL',
406 SYSDATE,
407 p_org_id ) RETURNING transaction_id INTO l_trans_id;
408 END;
409
410 DECLARE
411 CURSOR cur_cal(cp_cal_type IN VARCHAR2,
412 cp_sequence_number IN NUMBER) IS
413 SELECT *
414 FROM igs_ca_inst_all
415 WHERE cal_type=cp_cal_type
416 AND sequence_number=cp_sequence_number;
417 l_cur_cal cur_cal%ROWTYPE;
418
419 CURSOR cur_pat(cp_uoo_id IN NUMBER) IS
420 SELECT unit_cd,version_number
421 FROM igs_ps_unit_ofr_opt_all
422 WHERE uoo_id=cp_uoo_id;
423 l_cur_pat cur_pat%ROWTYPE;
424
425 BEGIN
426
427 OPEN cur_cal(p_cal_type,p_sequence_number);
428 FETCH cur_cal INTO l_cur_cal;
429 CLOSE cur_cal;
430
431 OPEN cur_pat(cur_usec_rec.uoo_id);
432 FETCH cur_pat INTO l_cur_pat;
433 CLOSE cur_pat;
434
435 --Insert into pattern interface table
436 INSERT INTO IGS_PS_SCH_PAT_INT
437 (int_pat_id ,
438 transaction_id ,
439 calendar_type ,
440 sequence_number ,
441 teaching_cal_alternate_code ,
442 start_date ,
443 end_date ,
444 unit_cd ,
445 version_number ,
446 enrollment_expected ,
447 enrollment_maximum ,
448 override_enrollment_maximum ,
449 unit_status ,
450 abort_flag ,
451 import_done_flag ,
452 created_by ,
453 creation_date ,
454 last_updated_by ,
455 last_update_date ,
456 last_update_login)
457 VALUES(
458 IGS_PS_SCH_PAT_INT_S.NEXTVAL,
459 l_trans_id,
460 p_cal_type,
461 p_sequence_number,
462 l_cur_cal.alternate_code,
463 l_cur_cal.start_dt,
464 l_cur_cal.end_dt,
465 l_cur_pat.unit_cd,
466 l_cur_pat.version_number,
467 NULL,
468 NULL,
469 NULL,
470 NULL,
471 'N',
472 'N',
473 g_n_user_id,
474 SYSDATE,
475 g_n_user_id,
476 SYSDATE,
477 g_n_login_id
478 ) RETURNING int_pat_id INTO l_int_pat_id;
479 END ;
480
481
482
483 -- Insert Unit Section Interface Records (IGS_PS_SCH_USEC_INT_ALL)
484 BEGIN
485 INSERT INTO igs_ps_sch_usec_int_all (
486 int_usec_id ,
487 calendar_type ,
488 sequence_number ,
489 unit_cd ,
490 version_number ,
491 unit_title ,
492 owner_org_unit_cd ,
493 unit_class ,
494 unit_section_start_date ,
495 unit_section_end_date ,
496 unit_section_status ,
497 enrollment_maximum ,
498 enrollment_actual ,
499 enrollment_expected ,
503 cal_end_dt ,
500 override_enrollment_max ,
501 location_cd ,
502 cal_start_dt ,
504 uoo_id ,
505 transaction_id ,
506 org_id ,
507 ABORT_FLAG ,
508 IMPORT_DONE_FLAG ,
509 CALL_NUMBER ,
510 SUBTITLE ,
511 ORG_UNIT_DESCRIPTION ,
512 TEACHING_CAL_ALTERNATE_CODE ,
513 INT_PAT_ID ,
514 created_by ,
515 creation_date ,
516 last_updated_by ,
517 last_update_date ,
518 last_update_login
519 )
520 VALUES (
521 IGS_PS_SCH_USEC_INT_S.NEXTVAL,
522 p_cal_type,
523 p_sequence_number,
524 NULL,
525 NULL,
526 NULL,
527 NULL,
528 NULL,
529 NULL,
530 NULL,
531 NULL,
532 NULL,
533 NULL,
534 NULL,
535 NULL,
536 NULL,
537 NVL(p_cal_start_dt,rec_cal.start_dt),
538 NVL(p_cal_end_dt,rec_cal.end_dt),
539 p_uoo_id,
540 l_trans_id,
541 p_org_id,
542 'N',
543 'N',
544 NULL,
545 NULL,
546 NULL,
547 NULL,
548 l_int_pat_id,
549 g_n_user_id,
550 SYSDATE,
551 g_n_user_id,
552 SYSDATE,
553 g_n_login_id
554 ) RETURNING int_usec_id INTO l_int_usec_id;
555 END;
556
557
558 BEGIN
559 -- Insert records in the Igs_ps_sch_int_all table
560
561 -- Enh bug#2833850
562 -- Passing the values stored in the input parameters to the columns monday,tuesday,wednesday,thursday,friday,saturday
563 -- uso_start_date,uso_end_date in the call to igs_ps_sch_int_pkg.insert_row.
564 -- Added the column preferred_region_code in the call to igs_ps_sch_int_pkg.insert_row
565 INSERT INTO igs_ps_sch_int_all (
566 CALENDAR_TYPE ,
567 SEQUENCE_NUMBER ,
568 TRANSACTION_TYPE ,
569 UNIT_SECTION_OCCURRENCE_ID ,
570 UNIT_CD ,
571 VERSION_NUMBER ,
572 UNIT_TITLE ,
573 OWNER_ORG_UNIT_CD ,
574 UNIT_CLASS ,
575 MONDAY ,
576 TUESDAY ,
577 WEDNESDAY ,
578 THURSDAY ,
579 FRIDAY ,
580 SATURDAY ,
581 SUNDAY ,
582 UNIT_SECTION_START_DATE ,
583 UNIT_SECTION_END_DATE ,
584 START_TIME ,
585 END_TIME ,
586 ENROLLMENT_MAXIMUM ,
587 ENROLLMENT_ACTUAL ,
588 INSTRUCTOR_ID ,
589 SURNAME ,
590 BUILDING_ID ,
591 ROOM_ID ,
592 LOCATION_CD ,
593 UNIT_SEC_CROSS_UNIT_SEC_ID ,
594 UOO_ID ,
595 SCHEDULE_STATUS ,
596 ERROR_TEXT ,
597 TRANSACTION_ID ,
598 INT_OCCURS_ID ,
599 INT_USEC_ID ,
600 GIVEN_NAMES ,
601 MIDDLE_NAME ,
602 ORG_ID ,
603 DEDICATED_BUILDING_ID ,
604 DEDICATED_ROOM_ID ,
605 PREFERRED_BUILDING_ID ,
606 PREFERRED_ROOM_ID ,
607 PREFERRED_REGION_CODE ,
608 TBA_STATUS ,
609 USO_START_DATE ,
610 USO_END_DATE ,
611 abort_flag ,
612 import_done_flag ,
613 occurrence_identifier ,
614 created_by ,
615 creation_date ,
616 last_updated_by ,
617 last_update_date ,
618 last_update_login
619 )
620 VALUES (
621 NULL,
622 NULL,
623 p_transaction_type,
624 p_unit_section_occurrence_id,
625 NULL,
626 NULL,
627 NULL,
628 NULL,
629 NULL,
630 p_monday,
631 p_tuesday,
632 p_wednesday,
633 p_thursday,
634 p_friday,
635 p_saturday,
636 p_sunday,
637 NULL,
638 NULL,
639 p_start_time,
640 p_end_time,
641 NULL,
642 NULL,
643 NULL,
644 NULL,
645 p_building_id,
646 p_room_id,
647 NULL,
648 NULL,
649 NULL,
650 p_schedule_status,
651 p_error_text,
655 NULL,
652 NULL,
653 IGS_PS_SCH_INT_S.NEXTVAL,
654 l_int_usec_id,
656 NULL,
657 p_org_id,
658 NULL,
659 NULL,
660 NULL,
661 NULL,
662 NULL,
663 NULL,
664 p_uso_start_date,
665 p_uso_end_date,
666 'N',
667 'N',
668 cur_usec_rec.occurrence_identifier,
669 g_n_user_id,
670 SYSDATE,
671 g_n_user_id,
672 SYSDATE,
673 g_n_login_id
674 ) ;
675 END;
676
677 END;
678
679 END LOOP;
680
681 -- If the p_commit parameter is set to True and no errors have been raised by the
682 -- then commit
683
684 IF FND_API.To_Boolean(p_commit) THEN
685 COMMIT WORK;
686 END IF;
687
688 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
689 p_data => x_msg_data);
690
691
692 EXCEPTION
693 WHEN FND_API.G_EXC_ERROR THEN
694 ROLLBACK TO Insert_schedule_pub;
695 x_return_status := FND_API.G_RET_STS_ERROR;
696 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
697 p_data => x_msg_data);
698
699 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
700 ROLLBACK TO Insert_schedule_pub;
701 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
702 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
703 p_data => x_msg_data);
704
705 WHEN OTHERS THEN
706 ROLLBACK TO Insert_schedule_pub;
707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
708
709 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
710 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,
711 l_api_name);
712 END IF;
713 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
714 p_data => x_msg_data);
715
716 END insert_schedule;
717
718
719 PROCEDURE update_schedule( p_api_version IN NUMBER,
720 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
721 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
722 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
723 x_return_status OUT NOCOPY VARCHAR2,
724 x_msg_count OUT NOCOPY NUMBER,
725 x_msg_data OUT NOCOPY VARCHAR2,
726 p_int_occurs_id IN NUMBER,
727 p_start_time IN DATE ,
728 p_end_time IN DATE ,
729 p_building_id IN NUMBER ,
730 p_room_id IN NUMBER ,
731 p_schedule_status IN VARCHAR2,
732 p_error_text IN VARCHAR2,
733 p_org_id IN NUMBER,
734 p_uso_start_date IN DATE,
735 p_uso_end_date IN DATE,
736 p_sunday IN VARCHAR2,
737 p_monday IN VARCHAR2,
738 p_tuesday IN VARCHAR2,
739 p_wednesday IN VARCHAR2,
740 p_thursday IN VARCHAR2,
741 p_friday IN VARCHAR2,
742 p_saturday IN VARCHAR2
743 ) AS
744
745 /***********************************************************************************************
746 Created By: schodava
747 Date Created By: 12-06-2001
748 Purpose: This procedure is used to update records in the Scheduling interface tables.
749 Known limitations,enhancements,remarks:
750 Change History
751 Who When What
752 jbegum 18-APR-2003 Enh bug#2833850
753 Added following parameters
754 p_uso_start_date,p_uso_end_date,p_sunday,p_monday,p_tuesday,p_wednesday,p_thursday,p_friday,p_saturday
755 smvk 10-Feb-2003 Bug # 2803385. Modified the variable buiding_code and room_code as building_id and
756 room_id respectively.
757 *************************************************************************************************/
758
759 l_api_name CONSTANT VARCHAR2(30) := 'Update_sch';
760 l_api_version CONSTANT NUMBER := 1.0;
761
762 BEGIN
763
764 -- Savepoint
765 SAVEPOINT Update_Schedule_pub;
766
767 -- Check if the API call is compatible
768 IF NOT FND_API.Compatible_API_Call( l_api_version,
769 p_api_version,
770 l_api_name,
774 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
771 g_pkg_name) THEN
772
773 -- If the call is incompatible, then raise the unexpected error
775
776 END IF;
777
778 -- If the p_init_msg_list is T, i.e. the calling program wants to initialise
779 -- the message list, then the message list is initialised using the API call
780 IF FND_API.To_Boolean(p_init_msg_list) THEN
781 FND_MSG_PUB.Initialize;
782 END IF;
783
784 -- Set the return status as success for the api
785 x_return_status := FND_API.G_RET_STS_SUCCESS;
786
787 -- Schedule Interface Tables have to be updated with the Scheduled fields.
788 -- Cursor for selecting the Unit Section Occurrence data from the Schedule Interface Table
789
790 DECLARE
791
792 l_int_occurs_id NUMBER ;
793 l_building_id igs_ps_sch_int_all.building_id%TYPE;
794 l_room_id igs_ps_sch_int_all.room_id%TYPE;
795 l_start_time igs_ps_sch_int_all.start_time%TYPE;
796 l_end_time igs_ps_sch_int_all.end_time%TYPE;
797 l_uso_start_date igs_ps_sch_int_all.uso_start_date%TYPE;
798 l_uso_end_date igs_ps_sch_int_all.uso_end_date%TYPE;
799 l_sunday igs_ps_sch_int_all.sunday%TYPE;
800 l_monday igs_ps_sch_int_all.monday%TYPE;
801 l_tuesday igs_ps_sch_int_all.tuesday%TYPE;
802 l_wednesday igs_ps_sch_int_all.wednesday%TYPE;
803 l_thursday igs_ps_sch_int_all.thursday%TYPE;
804 l_friday igs_ps_sch_int_all.friday%TYPE;
805 l_saturday igs_ps_sch_int_all.saturday%TYPE;
806
807 CURSOR cur_usec(cp_int_occurs_id IN NUMBER) IS
808 SELECT psi.rowid,
809 psi.*,
810 ps.unit_section_start_date us_start_date,
811 ps.unit_section_end_date us_end_date
812 FROM IGS_PS_SCH_INT_ALL psi,
813 IGS_PS_SCH_USEC_INT ps
814 WHERE psi.int_occurs_id = cp_int_occurs_id AND
815 psi.int_usec_id = ps.int_usec_id;
816
817 -- Cursor added as part of Enh bug#2833850
818 CURSOR c_bld(cp_n_building_id igs_ad_building_all.building_id%TYPE) IS
819 SELECT 'x'
820 FROM igs_ad_building_all
821 WHERE building_id = cp_n_building_id;
822
823 -- Cursor added as part of Enh bug#2833850
824 CURSOR c_room(cp_n_room_id igs_ad_room_all.room_id%TYPE) IS
825 SELECT 'x'
826 FROM igs_ad_room_all
827 WHERE room_id = cp_n_room_id;
828
829 -- Cursor added as part of Enh bug#2833850
830 CURSOR c_bld_room(cp_n_room_id igs_ad_room_all.room_id%TYPE,
831 cp_n_building_id igs_ad_building_all.building_id%TYPE) IS
832 SELECT 'x'
833 FROM igs_ad_room_all
834 WHERE room_id = cp_n_room_id AND
835 building_id = cp_n_building_id;
836
837 -- Local variables added as part of Enh bug#2833850
838 rec_bld c_bld%ROWTYPE;
839 rec_room c_room%ROWTYPE;
840 rec_bld_room c_bld_room%ROWTYPE;
841
842 BEGIN
843
844 FOR cur_usec_rec IN cur_usec(p_int_occurs_id) LOOP
845
846 -- Enh bug#2833850
847 -- Following validations have been added to validate the parameter values passed to update scheduling api by third party software.
848 -- These validations will ensure that the interface records updated by the third party software thru update scheduling api will
849 -- get sucessfully imported to the production table of OSS
850
851 -- Validation 1
852 -- Error text is mandatory for unit section occurrence scheduled as Error.
853
854 IF p_schedule_status = 'ERROR' AND p_error_text IS NULL THEN
855 FND_MESSAGE.Set_Name('IGS','IGS_PS_SCH_ERR_TEXT_NULL');
856 FND_MSG_PUB.Add;
857 x_return_status := FND_API.G_RET_STS_ERROR;
858 END IF;
859
860 -- Validation 2
861 -- Building identifier are mandatory for successfully scheduled unit section occurrence
862
863 IF p_schedule_status = 'OK' AND
864 (p_building_id IS NULL) AND
865 (cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') THEN
866 FND_MESSAGE.Set_Name('IGS','IGS_PS_SCH_BD_OR_RM_NULL');
867 FND_MSG_PUB.Add;
868 x_return_status := FND_API.G_RET_STS_ERROR;
869 END IF;
870
871 -- Validation 3
872 -- Building and room identifier should be null for successfully cancelled unit section occurrence
873
874 IF p_schedule_status = 'OK' AND
875 (p_building_id IS NOT NULL OR p_room_id IS NOT NULL) AND
876 cur_usec_rec.transaction_type = 'CANCEL' THEN
877 FND_MESSAGE.Set_Name('IGS','IGS_PS_SCH_BD_OR_RM_NOT_NULL');
878 FND_MSG_PUB.Add;
879 x_return_status := FND_API.G_RET_STS_ERROR;
880 END IF;
881
882 -- Validation 4
883 -- Unit Section Occurrence start date must be lesser than or equal to Unit Section Occurrence end date
884
885 IF p_schedule_status = 'OK' AND
886 p_uso_start_date IS NOT NULL AND
887 p_uso_end_date IS NOT NULL AND
888 p_uso_start_date > p_uso_end_date AND
889 (cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') AND
893 FND_MSG_PUB.Add;
890 cur_usec_rec.tba_status = 'Y' AND
891 cur_usec_rec.uso_start_date IS NULL AND cur_usec_rec.uso_end_date IS NULL THEN
892 FND_MESSAGE.Set_Name('IGS','IGS_PE_EDT_LT_SDT');
894 x_return_status := FND_API.G_RET_STS_ERROR;
895 END IF;
896
897 -- Validation 5
898 -- Unit Section Occurrence Start Date should be greater than or equal to Unit Section Start Date
899
900 IF p_schedule_status = 'OK' AND
901 p_uso_start_date IS NOT NULL AND
902 p_uso_start_date < cur_usec_rec.us_start_date AND
903 (cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') AND
904 cur_usec_rec.tba_status = 'Y' AND
905 cur_usec_rec.uso_start_date IS NULL THEN
906 FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_STDT_GE_US_STDT');
907 FND_MSG_PUB.Add;
908 x_return_status := FND_API.G_RET_STS_ERROR;
909 END IF;
910
911 -- Validation 6
912 -- Unit Section Occurrence Start Date must be less than or equal to Unit Section End Date
913
914 IF p_schedule_status = 'OK' AND
915 p_uso_start_date IS NOT NULL AND
916 p_uso_start_date > cur_usec_rec.us_end_date AND
917 (cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') AND
918 cur_usec_rec.tba_status = 'Y' AND
919 cur_usec_rec.uso_start_date IS NULL THEN
920 FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_ST_DT_UOO_END_DT');
921 FND_MSG_PUB.Add;
922 x_return_status := FND_API.G_RET_STS_ERROR;
923 END IF;
924
925 -- Validation 7
926 -- Unit Section Occurrence End Date must be greater than or equal to Unit Section Start Date
927
928 IF p_schedule_status = 'OK' AND
929 p_uso_end_date IS NOT NULL AND
930 p_uso_end_date < cur_usec_rec.us_start_date AND
931 (cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') AND
932 cur_usec_rec.tba_status = 'Y' AND
933 cur_usec_rec.uso_end_date IS NULL THEN
934 FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_END_DT_UOO_ST_DT');
935 FND_MSG_PUB.Add;
936 x_return_status := FND_API.G_RET_STS_ERROR;
937 END IF;
938
939 -- Validation 8
940 -- Unit Section Occurrence End Date must be lesser than or equal to Unit Section End Date
941
942 IF p_schedule_status = 'OK' AND
943 p_uso_end_date IS NOT NULL AND
944 p_uso_end_date > cur_usec_rec.us_end_date AND
945 (cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') AND
946 cur_usec_rec.tba_status = 'Y' AND
947 cur_usec_rec.uso_end_date IS NULL THEN
948 FND_MESSAGE.Set_Name('IGS','IGS_PS_USO_ENDT_LE_US_ENDT');
949 FND_MSG_PUB.Add;
950 x_return_status := FND_API.G_RET_STS_ERROR;
951 END IF;
952
953 -- Validation 9
954 -- Invalid Scheduled Building Identifier
955
956 IF p_schedule_status = 'OK' AND
957 p_building_id IS NOT NULL AND
958 (cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') THEN
959
960 OPEN c_bld(p_building_id);
961 FETCH c_bld INTO rec_bld;
962 IF c_bld%NOTFOUND THEN
963 FND_MESSAGE.Set_Name('IGS','IGS_PS_BUILDING_ID_INVALID');
964 FND_MSG_PUB.Add;
965 x_return_status := FND_API.G_RET_STS_ERROR;
966 END IF;
967 CLOSE c_bld;
968
969 END IF;
970
971 -- Validation 10
972 -- Invalid Scheduled Room Identifier
973
974 IF p_schedule_status = 'OK' AND
975 p_room_id IS NOT NULL AND
976 (cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') THEN
977
978 OPEN c_room(p_room_id);
979 FETCH c_room INTO rec_room;
980 IF c_room%NOTFOUND THEN
981 FND_MESSAGE.Set_Name('IGS','IGS_PS_ROOM_ID_INVALID');
982 FND_MSG_PUB.Add;
983 x_return_status := FND_API.G_RET_STS_ERROR;
984 END IF;
985 CLOSE c_room;
986
987 END IF;
988
989 -- Validation 11
990 -- Scheduled room does not belong to scheduled building
991
992 IF p_schedule_status = 'OK' AND
993 p_building_id IS NOT NULL AND
994 p_room_id IS NOT NULL AND
995 (cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') THEN
996
997 OPEN c_bld_room(p_room_id,p_building_id);
998 FETCH c_bld_room INTO rec_bld_room;
999 IF c_bld_room%NOTFOUND THEN
1000 FND_MESSAGE.Set_Name('IGS','IGS_PS_ROOM_INV_FOR_BLD');
1001 FND_MSG_PUB.Add;
1002 x_return_status := FND_API.G_RET_STS_ERROR;
1003 END IF;
1004 CLOSE c_bld_room;
1005
1006 END IF;
1007
1008 -- Validation 12
1009 -- At least one day of the week should be checked
1010
1011 IF p_schedule_status = 'OK' AND
1012 NVL(p_sunday,'N') = 'N' AND
1013 NVL(p_monday,'N') = 'N' AND
1017 NVL(p_friday,'N') = 'N' AND
1014 NVL(p_tuesday,'N') = 'N' AND
1015 NVL(p_wednesday,'N') = 'N' AND
1016 NVL(p_thursday,'N') = 'N' AND
1018 NVL(p_saturday,'N') = 'N' AND
1019 cur_usec_rec.tba_status = 'Y' AND
1020 (cur_usec_rec.transaction_type = 'UPDATE' OR cur_usec_rec.transaction_type = 'REQUEST') THEN
1021
1022 FND_MESSAGE.Set_Name('IGS','IGS_PS_ATLEAST_ONE_DAY_CHECK');
1023 FND_MSG_PUB.Add;
1024 x_return_status := FND_API.G_RET_STS_ERROR;
1025 END IF;
1026
1027 -- Raising an exception if any any of the above validations fail
1028
1029 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1030 RAISE FND_API.G_EXC_ERROR;
1031 END IF;
1032
1033 l_int_occurs_id := cur_usec_rec.int_occurs_id;
1034
1035 IF p_building_id IS NULL THEN
1036 l_building_id := cur_usec_rec.building_id;
1037 ELSE
1038 l_building_id := p_building_id;
1039 END IF;
1040
1041 IF p_room_id IS NULL THEN
1042 l_room_id := cur_usec_rec.room_id;
1043 ELSE
1044 l_room_id := p_room_id;
1045 END IF;
1046
1047 IF p_start_time IS NULL THEN
1048 l_start_time := cur_usec_rec.start_time;
1049 ELSE
1050 l_start_time := p_start_time;
1051 END IF;
1052
1053 IF p_end_time IS NULL THEN
1054 l_end_time := cur_usec_rec.end_time;
1055 ELSE
1056 l_end_time := p_end_time;
1057 END IF;
1058
1059 -- Enh bug#2833850
1060 -- Added following Null check for the newly added parameters.
1061
1062 IF p_uso_start_date IS NOT NULL AND cur_usec_rec.uso_start_date IS NULL THEN
1063 l_uso_start_date := p_uso_start_date;
1064 ELSE
1065 l_uso_start_date := cur_usec_rec.uso_start_date;
1066 END IF;
1067
1068 IF p_uso_end_date IS NOT NULL AND cur_usec_rec.uso_end_date IS NULL THEN
1069 l_uso_end_date := p_uso_end_date;
1070 ELSE
1071 l_uso_end_date := cur_usec_rec.uso_end_date;
1072 END IF;
1073
1074 IF p_sunday IS NULL THEN
1075 l_sunday := cur_usec_rec.sunday;
1076 ELSE
1077 l_sunday := p_sunday;
1078 END IF;
1079
1080 IF p_monday IS NULL THEN
1081 l_monday := cur_usec_rec.monday;
1082 ELSE
1083 l_monday := p_monday;
1084 END IF;
1085
1086 IF p_tuesday IS NULL THEN
1087 l_tuesday := cur_usec_rec.tuesday;
1088 ELSE
1089 l_tuesday := p_tuesday;
1090 END IF;
1091
1092 IF p_wednesday IS NULL THEN
1093 l_wednesday := cur_usec_rec.wednesday;
1094 ELSE
1095 l_wednesday := p_wednesday;
1096 END IF;
1097
1098 IF p_thursday IS NULL THEN
1099 l_thursday := cur_usec_rec.thursday;
1100 ELSE
1101 l_thursday := p_thursday;
1102 END IF;
1103
1104 IF p_friday IS NULL THEN
1105 l_friday := cur_usec_rec.friday;
1106 ELSE
1107 l_friday := p_friday;
1108 END IF;
1109
1110 IF p_saturday IS NULL THEN
1111 l_saturday := cur_usec_rec.saturday;
1112 ELSE
1113 l_saturday := p_saturday;
1114 END IF;
1115
1116 -- Enh bug#2833850
1117 -- Passing the values stored in the local variables to the columns monday,tuesday,wednesday,thursday,friday,saturday
1118 -- uso_start_date,uso_end_date in the call to igs_ps_sch_int_pkg.update_row.
1119 -- Added the column preferred_region_code in the call to igs_ps_sch_int_pkg.update_row
1120
1121 UPDATE igs_ps_sch_int_all SET
1122 TRANSACTION_TYPE = cur_usec_rec.transaction_type,
1123 UNIT_SECTION_OCCURRENCE_ID = cur_usec_rec.unit_section_occurrence_id,
1124 MONDAY = l_monday,
1125 TUESDAY = l_tuesday,
1126 WEDNESDAY = l_wednesday,
1127 THURSDAY = l_thursday,
1128 FRIDAY = l_friday,
1129 SATURDAY = l_saturday,
1130 SUNDAY = l_sunday,
1131 START_TIME = l_start_time,
1132 END_TIME = l_end_time,
1133 INSTRUCTOR_ID = cur_usec_rec.instructor_id,
1134 SURNAME = cur_usec_rec.surname,
1135 BUILDING_ID = l_building_id,
1136 ROOM_ID = l_room_id,
1137 SCHEDULE_STATUS = p_schedule_status,
1138 ERROR_TEXT = p_error_text,
1139 INT_OCCURS_ID = l_int_occurs_id,
1140 INT_USEC_ID = cur_usec_rec.int_usec_id,
1141 GIVEN_NAMES = cur_usec_rec.given_names,
1142 MIDDLE_NAME = cur_usec_rec.middle_name,
1143 DEDICATED_BUILDING_id = cur_usec_rec.dedicated_building_id,
1144 DEDICATED_ROOM_id = cur_usec_rec.dedicated_room_id,
1145 PREFERRED_BUILDING_id = cur_usec_rec.preferred_building_id,
1146 PREFERRED_ROOM_id = cur_usec_rec.preferred_room_id,
1147 TBA_STATUS = cur_usec_rec.tba_status,
1148 USO_START_DATE = l_uso_start_date,
1149 USO_END_DATE = l_uso_end_date,
1150 PREFERRED_REGION_CODE = cur_usec_rec.preferred_region_code,
1151 last_updated_by = g_n_user_id,
1152 last_update_date = SYSDATE,
1153 last_update_login = g_n_login_id
1154 WHERE INT_OCCURS_ID= l_int_occurs_id;
1155
1156 END LOOP;
1157
1158 END;
1159
1160 -- If the p_commit parameter is set to True and no errors have been raised
1161 -- then commit
1162 IF FND_API.To_Boolean(p_commit) THEN
1163 COMMIT WORK;
1164 END IF;
1165
1166 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1167 p_data => x_msg_data);
1168
1169
1170 EXCEPTION
1171 WHEN FND_API.G_EXC_ERROR THEN
1172 ROLLBACK TO Update_Schedule_pub;
1173 x_return_status := FND_API.G_RET_STS_ERROR;
1174 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1175 p_data => x_msg_data);
1176
1177 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1178 ROLLBACK TO Update_Schedule_pub;
1179 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1180 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1181 p_data => x_msg_data);
1182
1183 WHEN OTHERS THEN
1184 ROLLBACK TO Update_Schedule_pub;
1185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1186
1187 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1188
1189 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,
1190 l_api_name);
1191
1192 END IF;
1193 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1194 p_data => x_msg_data);
1195
1196 END update_schedule;
1197
1198 END IGS_PS_SCH_INT_API_PUB;
[Home] [Help]