[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_USEC_OCCURS_PKG
Source
1 PACKAGE BODY igs_ps_usec_occurs_pkg AS
2 /* $Header: IGSPI0WB.pls 120.2 2005/09/22 00:58:08 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ps_usec_occurs_ALL%RowType;
5 new_references igs_ps_usec_occurs_ALL%RowType;
6
7 PROCEDURE BeforeRowInsertUpdatedelete1( x_rowid IN VARCHAR2) IS
8 /*************************************************************
9 Created By : pradhakr
10 Date Created By : 2000/06/12
11 Purpose :
12 Know limitations, enhancements or remarks
13 Change History
14 Who When What
15 (reverse chronological order - newest change first)
16 vvutukur 05-Nov-2002 Enh#2613933.Modified the cursor cur_ref_cd to add join with igs_ps_usec_occurs_all.
17 ***************************************************************/
18 CURSOR cur_ref_cd IS
19 SELECT reference_code_type
20 FROM igs_ps_usec_ocur_ref_v A,
21 igs_ps_usec_occurs_all B
22 WHERE A.unit_section_occurrence_id = B.unit_section_occurrence_id
23 AND B.rowid = x_rowid;
24
25 l_ref_cd igs_ps_usec_ocur_ref_v.reference_code_type%TYPE;
26
27 BEGIN
28
29 OPEN cur_ref_cd;
30 FETCH cur_ref_cd INTO l_ref_cd;
31 IF cur_ref_cd%FOUND THEN
32 IF igs_ps_val_atl.chk_mandatory_ref_cd(l_ref_cd) THEN
33 CLOSE cur_ref_cd;
34 FND_MESSAGE.SET_NAME( 'IGS', 'IGS_PS_REF_CD_MANDATORY');
35 IGS_GE_MSG_STACK.ADD;
36 app_exception.raise_exception;
37 END IF;
38 END IF;
39 CLOSE cur_ref_cd;
40
41 END BeforeRowInsertUpdatedelete1;
42
43 PROCEDURE Dflt_usec_occur_ref_code(p_n_usec_occur_id IN NUMBER, p_c_message_name OUT NOCOPY VARCHAR2) IS
44 /*************************************************************
45 Created By : pradhakr
46 Date Created By : 2000/06/12
47 Purpose :
48 Know limitations, enhancements or remarks
49 Change History
50 Who When What
51 sarakshi 7-May-203 Enh#2858431,added restricted_flag,closed_id check in cursor cur_ref_code_type, also inside loop nullified variable l_rowid and l_usec_ref_id
52 (reverse chronological order - newest change first)
53 ***************************************************************/
54
55 CURSOR cur_ref_code_type IS
56 SELECT reference_cd_type
57 FROM igs_ge_ref_cd_type
58 WHERE unit_section_occurrence_flag ='Y' AND
59 restricted_flag='Y' AND
60 closed_ind ='N' AND
61 mandatory_flag ='Y' ;
62 l_ref_cd_type igs_ge_ref_cd_type.reference_cd_type%TYPE;
63
64 CURSOR cur_ref_code( ref_cd_type IN igs_ge_ref_cd.reference_cd_type%TYPE) IS
65 SELECT reference_cd,description
66 FROM igs_ge_ref_cd
67 WHERE default_flag ='Y' AND
68 reference_cd_type = ref_cd_type ;
69 l_ref_cd cur_ref_code%ROWTYPE;
70 l_usec_ref_id NUMBER;
71 l_rowid VARCHAR2(20);
72
73 BEGIN
74
75 FOR ref_cd_count IN cur_ref_code_type LOOP
76 OPEN cur_ref_code(ref_cd_count.reference_cd_type);
77 FETCH cur_ref_code INTO l_ref_cd;
78 IF cur_ref_code%FOUND THEN
79 BEGIN
80 l_rowid:=NULL;
81 l_usec_ref_id:=NULL;
82 igs_ps_usec_ocur_ref_pkg.insert_row(
83 X_Mode => 'R',
84 X_RowId => l_rowid,
85 X_Unit_Sec_Occur_Reference_Id => l_usec_ref_id,
86 X_Unit_Section_Occurrence_Id => p_n_usec_occur_id,
87 X_Reference_Code_Type => ref_cd_count.reference_cd_type,
88 X_Reference_Code => l_ref_cd.reference_cd,
89 X_reference_code_desc => l_ref_cd.description
90 );
91 EXCEPTION
92 -- The failure of insertion of reference code should not stop the creation of new Unit Section.
93 -- Hence any exception raised by the TBH is trapped and the current processing is allowed to proceed.
94 WHEN OTHERS THEN
95 NULL;
96 END;
97 END IF;
98 CLOSE cur_ref_code;
99 END LOOP;
100
101 END Dflt_usec_occur_ref_code;
102
103
104 PROCEDURE Set_Column_Values (
105 p_action IN VARCHAR2,
106 x_rowid IN VARCHAR2 ,
107 x_unit_section_occurrence_id IN NUMBER ,
108 x_uoo_id IN NUMBER ,
109 x_monday IN VARCHAR2 ,
110 x_tuesday IN VARCHAR2 ,
111 x_wednesday IN VARCHAR2 ,
112 x_thursday IN VARCHAR2 ,
113 x_friday IN VARCHAR2 ,
114 x_saturday IN VARCHAR2 ,
115 x_sunday IN VARCHAR2 ,
116 x_start_time IN DATE ,
117 x_end_time IN DATE ,
118 x_building_code IN NUMBER ,
119 x_room_code IN NUMBER ,
120 x_schedule_status IN VARCHAR2 ,
121 x_status_last_updated IN DATE ,
122 x_instructor_id IN NUMBER ,
123 x_attribute_category IN VARCHAR2 ,
124 x_attribute1 IN VARCHAR2 ,
125 x_attribute2 IN VARCHAR2 ,
126 x_attribute3 IN VARCHAR2 ,
127 x_attribute4 IN VARCHAR2 ,
128 x_attribute5 IN VARCHAR2 ,
129 x_attribute6 IN VARCHAR2 ,
130 x_attribute7 IN VARCHAR2 ,
131 x_attribute8 IN VARCHAR2 ,
132 x_attribute9 IN VARCHAR2 ,
133 x_attribute10 IN VARCHAR2 ,
134 x_attribute11 IN VARCHAR2 ,
135 x_attribute12 IN VARCHAR2 ,
136 x_attribute13 IN VARCHAR2 ,
137 x_attribute14 IN VARCHAR2 ,
138 x_attribute15 IN VARCHAR2 ,
139 x_attribute16 IN VARCHAR2 ,
140 x_attribute17 IN VARCHAR2 ,
141 x_attribute18 IN VARCHAR2 ,
142 x_attribute19 IN VARCHAR2 ,
143 x_attribute20 IN VARCHAR2 ,
144 x_error_text IN VARCHAR2 ,
145 x_creation_date IN DATE ,
146 x_created_by IN NUMBER ,
147 x_last_update_date IN DATE ,
148 x_last_updated_by IN NUMBER ,
149 x_last_update_login IN NUMBER ,
150 x_org_id in NUMBER ,
151 x_start_date in DATE ,
152 x_end_date in DATE ,
153 x_to_be_announced in VARCHAR2 ,
154 x_inst_notify_ind IN VARCHAR2 ,
155 x_notify_status IN VARCHAR2 ,
156 x_dedicated_building_code IN NUMBER ,
157 x_dedicated_room_code IN NUMBER ,
158 x_preferred_building_code IN NUMBER ,
159 x_preferred_room_code IN NUMBER,
160 x_preferred_region_code IN VARCHAR2,
161 x_no_set_day_ind IN VARCHAR2,
162 x_cancel_flag IN VARCHAR2 ,
163 x_occurrence_identifier IN VARCHAR2,
164 x_abort_flag IN VARCHAR2
165
166 ) AS
167
168 /*************************************************************
169 Created By : venagara
170 Date Created By : 2000/05/12
171 Purpose :
172 Know limitations, enhancements or remarks
173 Change History
174 Who When What
175 smvk 02-Sep-2002 Removed the Default value in the parameters to overcome File.Pkg.22 gscc warnings.
176 As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
177 rgangara 09-May-2001 Added x_notify_status and x_inst_notify_ind args
178 Sreenivas.Bonam 28-Aug-2000 Added x_error_text argument
179 (reverse chronological order - newest change first)
180 ***************************************************************/
181
182 CURSOR cur_old_ref_values IS
183 SELECT *
184 FROM IGS_PS_USEC_OCCURS_ALL
185 WHERE rowid = x_rowid;
186
187 BEGIN
188
189 l_rowid := x_rowid;
190
191 -- Code for setting the Old and New Reference Values.
192 -- Populate Old Values.
193 Open cur_old_ref_values;
194 Fetch cur_old_ref_values INTO old_references;
195 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
196 Close cur_old_ref_values;
197 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
198 IGS_GE_MSG_STACK.ADD;
199 App_Exception.Raise_Exception;
200 Return;
201 END IF;
202 Close cur_old_ref_values;
203
204 -- Populate New Values.
205 new_references.unit_section_occurrence_id := x_unit_section_occurrence_id;
206 new_references.uoo_id := x_uoo_id;
207 new_references.monday := x_monday;
208 new_references.tuesday := x_tuesday;
209 new_references.wednesday := x_wednesday;
210 new_references.thursday := x_thursday;
211 new_references.friday := x_friday;
212 new_references.saturday := x_saturday;
213 new_references.sunday := x_sunday;
214 new_references.start_time := x_start_time;
215 new_references.end_time := x_end_time;
216 new_references.building_code := x_building_code;
217 new_references.room_code := x_room_code;
218 new_references.schedule_status := x_schedule_status;
219 new_references.status_last_updated := x_status_last_updated;
220 new_references.instructor_id := x_instructor_id;
221 new_references.attribute_category := x_attribute_category;
222 new_references.attribute1 := x_attribute1;
223 new_references.attribute2 := x_attribute2;
224 new_references.attribute3 := x_attribute3;
225 new_references.attribute4 := x_attribute4;
226 new_references.attribute5 := x_attribute5;
227 new_references.attribute6 := x_attribute6;
228 new_references.attribute7 := x_attribute7;
229 new_references.attribute8 := x_attribute8;
230 new_references.attribute9 := x_attribute9;
231 new_references.attribute10 := x_attribute10;
232 new_references.attribute11 := x_attribute11;
233 new_references.attribute12 := x_attribute12;
234 new_references.attribute13 := x_attribute13;
235 new_references.attribute14 := x_attribute14;
236 new_references.attribute15 := x_attribute15;
237 new_references.attribute16 := x_attribute16;
238 new_references.attribute17 := x_attribute17;
239 new_references.attribute18 := x_attribute18;
240 new_references.attribute19 := x_attribute19;
241 new_references.attribute20 := x_attribute20;
242 new_references.error_text := x_error_text;
243 new_references.org_id := x_org_id;
244 new_references.start_date := x_start_date;
245 new_references.end_date := x_end_date;
246 new_references.to_be_announced := x_to_be_announced;
247 IF (p_action = 'UPDATE') THEN
248 new_references.creation_date := old_references.creation_date;
249 new_references.created_by := old_references.created_by;
250 ELSE
251 new_references.creation_date := x_creation_date;
252 new_references.created_by := x_created_by;
253 END IF;
254 new_references.last_update_date := x_last_update_date;
255 new_references.last_updated_by := x_last_updated_by;
256 new_references.last_update_login := x_last_update_login;
257 new_references.inst_notify_ind := x_inst_notify_ind;
258 new_references.notify_status := x_notify_status;
259 new_references.dedicated_building_code := x_dedicated_building_code;
260 new_references.dedicated_room_code := x_dedicated_room_code;
261 new_references.preferred_building_code := x_preferred_building_code;
262 new_references.preferred_room_code := x_preferred_room_code;
263 new_references.preferred_region_code := x_preferred_region_code;
264 new_references.no_set_day_ind := x_no_set_day_ind;
265 new_references.cancel_flag := x_cancel_flag;
266 new_references.occurrence_identifier := x_occurrence_identifier;
267 new_references.abort_flag := x_abort_flag;
268
269 END Set_Column_Values;
270
271 PROCEDURE Check_Constraints (
272 Column_Name IN VARCHAR2 ,
273 Column_Value IN VARCHAR2 ) AS
274 /*************************************************************
275 Created By : venagara
276 Date Created By : 2000/05/12
277 Purpose :
278 Know limitations, enhancements or remarks
279 Change History
280 Who When What
281 shtatiko 20-NOV-2002 Added checks for to_be_announced and all days.
282 (Bug# 2649028)
283 smvk 02-Sep-2002 Removed the Default value in the parameters to overcome File.Pkg.22 gscc warnings.
284 As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
285 (reverse chronological order - newest change first)
286 ***************************************************************/
287
288 BEGIN
289
290 IF column_name IS NULL THEN
291 NULL;
292 ELSIF UPPER ( column_name ) = 'NO_SET_DAY_IND' THEN
293 new_references.no_set_day_ind := column_value;
294 ELSIF UPPER ( column_name ) = 'TO_BE_ANNOUNCED' THEN
295 new_references.to_be_announced := column_value;
296 ELSIF UPPER ( column_name ) = 'MONDAY' THEN
297 new_references.monday := column_value;
298 ELSIF UPPER ( column_name ) = 'TUESDAY' THEN
299 new_references.tuesday := column_value;
300 ELSIF UPPER ( column_name ) = 'WEDNESDAY' THEN
301 new_references.wednesday := column_value;
302 ELSIF UPPER ( column_name ) = 'THURSDAY' THEN
303 new_references.thursday := column_value;
304 ELSIF UPPER ( column_name ) = 'FRIDAY' THEN
305 new_references.friday := column_value;
306 ELSIF UPPER ( column_name ) = 'SATURDAY' THEN
307 new_references.saturday := column_value;
308 ELSIF UPPER ( column_name ) = 'SUNDAY' THEN
309 new_references.sunday := column_value;
310 ELSIF UPPER ( column_name ) = 'CANCEL_FLAG' THEN
311 new_references.sunday := column_value;
312 ELSIF UPPER ( column_name ) = 'ABORT_FLAG' THEN
313 new_references.abort_flag := column_value;
314 END IF;
315
316 IF UPPER(column_name)='NO_SET_DAY_IND' OR column_name IS NULL Then
317 IF new_references.no_set_day_ind NOT IN ( 'Y' , 'N' ) Then
318 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
319 IGS_GE_MSG_STACK.ADD;
320 app_exception.raise_exception;
321 END IF;
322 END IF;
323
324 IF UPPER(column_name)='TO_BE_ANNOUNCED' OR column_name IS NULL Then
325 IF new_references.to_be_announced NOT IN ( 'Y' , 'N' ) Then
326 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
327 IGS_GE_MSG_STACK.ADD;
328 app_exception.raise_exception;
329 END IF;
330 END IF;
331
332 IF UPPER(column_name)='MONDAY' OR column_name IS NULL Then
333 IF new_references.monday NOT IN ( 'Y' , 'N' ) Then
334 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
335 IGS_GE_MSG_STACK.ADD;
336 app_exception.raise_exception;
337 END IF;
338 END IF;
339
340 IF UPPER(column_name)='TUESDAY' OR column_name IS NULL Then
341 IF new_references.tuesday NOT IN ( 'Y' , 'N' ) Then
342 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
343 IGS_GE_MSG_STACK.ADD;
344 app_exception.raise_exception;
345 END IF;
346 END IF;
347
348 IF UPPER(column_name)='WEDNESDAY' OR column_name IS NULL Then
349 IF new_references.wednesday NOT IN ( 'Y' , 'N' ) Then
350 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
351 IGS_GE_MSG_STACK.ADD;
352 app_exception.raise_exception;
353 END IF;
354 END IF;
355
356 IF UPPER(column_name)='THURSDAY' OR column_name IS NULL Then
357 IF new_references.thursday NOT IN ( 'Y' , 'N' ) Then
358 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
359 IGS_GE_MSG_STACK.ADD;
360 app_exception.raise_exception;
361 END IF;
362 END IF;
363
364 IF UPPER(column_name)='FRIDAY' OR column_name IS NULL Then
365 IF new_references.friday NOT IN ( 'Y' , 'N' ) Then
366 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
367 IGS_GE_MSG_STACK.ADD;
368 app_exception.raise_exception;
369 END IF;
370 END IF;
371
372 IF UPPER(column_name)='SATURDAY' OR column_name IS NULL Then
373 IF new_references.saturday NOT IN ( 'Y' , 'N' ) Then
374 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
375 IGS_GE_MSG_STACK.ADD;
376 app_exception.raise_exception;
377 END IF;
378 END IF;
379
380 IF UPPER(column_name)='SUNDAY' OR column_name IS NULL Then
381 IF new_references.sunday NOT IN ( 'Y' , 'N' ) Then
382 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
383 IGS_GE_MSG_STACK.ADD;
384 app_exception.raise_exception;
385 END IF;
386 END IF;
387
388 IF UPPER(column_name)='CANCEL_FLAG' OR column_name IS NULL Then
389 IF new_references.no_set_day_ind NOT IN ( 'Y' , 'N' ) Then
390 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
391 IGS_GE_MSG_STACK.ADD;
392 app_exception.raise_exception;
393 END IF;
394 END IF;
395
396 IF UPPER(column_name)='ABORT_FLAG' OR column_name IS NULL THEN
397 IF new_references.abort_flag NOT IN ( 'Y' , 'N' ) Then
398 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
399 IGS_GE_MSG_STACK.ADD;
400 app_exception.raise_exception;
401 END IF;
402 END IF;
403
404
405 END Check_Constraints;
406
407
408
409 PROCEDURE Check_Uniqueness AS
410 /*************************************************************
411 Created By : sarakshi
412 Date Created By : 19-May-2005
413 Purpose :
414 Know limitations, enhancements or remarks
415 Change History
416 Who When What
417
418 (reverse chronological order - newest change first)
419 ***************************************************************/
420
421 BEGIN
422 IF Get_Uk_For_Validation (
423 new_references.uoo_id
424 ,new_references.occurrence_identifier
425 ) THEN
426 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
427 IGS_GE_MSG_STACK.ADD;
428 app_exception.raise_exception;
429 END IF;
430 END Check_Uniqueness ;
431
432 PROCEDURE Check_Parent_Existance AS
433 /*************************************************************
434 Created By : venagara
435 Date Created By : 2000/05/12
436 Purpose :
437 Know limitations, enhancements or remarks
438 Change History
439 Who When What
440 jbegum 21-APR-2003 Added call to Igs_Lookups_View_Pkg.Get_PK_For_Validation
441 for IGS_OR_LOC_REGION llokup_type.
442 Sreenivas.Bonam 28-Aug-2000 Added check for IGS_LOOKUPS_VIEW
443 (reverse chronological order - newest change first)
444 ***************************************************************/
445
446 BEGIN
447
448 IF (((old_references.building_code = new_references.building_code)) OR
449 ((new_references.building_code IS NULL))) THEN
450 NULL;
451 ELSIF NOT Igs_Ad_Building_Pkg.Get_PK_For_Validation (
452 new_references.building_code,
453 'N'
454 ) THEN
455 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
456 IGS_GE_MSG_STACK.ADD;
457 App_Exception.Raise_Exception;
458 END IF;
459
460 IF (((old_references.room_code = new_references.room_code)) OR
461 ((new_references.room_code IS NULL))) THEN
462 NULL;
463 ELSIF NOT Igs_Ad_Room_Pkg.Get_PK_For_Validation (
464 new_references.room_code,
465 'N'
466 ) THEN
467 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
468 IGS_GE_MSG_STACK.ADD;
469 App_Exception.Raise_Exception;
470 END IF;
471
472 IF (((old_references.uoo_id = new_references.uoo_id)) OR
473 ((new_references.uoo_id IS NULL))) THEN
474 NULL;
475 ELSIF NOT Igs_Ps_Unit_Ofr_Opt_Pkg.Get_UK_For_Validation (
476 new_references.uoo_id
477 ) THEN
478 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
479 IGS_GE_MSG_STACK.ADD;
480 App_Exception.Raise_Exception;
481 END IF;
482
483 IF (((old_references.schedule_status = new_references.schedule_status)) OR
484 ((new_references.schedule_status IS NULL))) THEN
485 NULL;
486 ELSIF NOT Igs_Lookups_View_Pkg.Get_PK_For_Validation (
487 'SCHEDULE_TYPE', new_references.schedule_status
488 ) THEN
489 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
490 IGS_GE_MSG_STACK.ADD;
491 App_Exception.Raise_Exception;
492 END IF;
493
494 IF (((old_references.preferred_region_code = new_references.preferred_region_code)) OR
495 ((new_references.preferred_region_code IS NULL))) THEN
496 NULL;
497 ELSIF NOT Igs_Lookups_View_Pkg.Get_PK_For_Validation (
498 'IGS_OR_LOC_REGION', new_references.preferred_region_code
499 ) THEN
500 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
501 IGS_GE_MSG_STACK.ADD;
502 App_Exception.Raise_Exception;
503 END IF;
504
505 END Check_Parent_Existance;
506
507 PROCEDURE Check_Child_Existance IS
508 /*************************************************************
509 Created By : venagara
510 Date Created By : 2000/05/12
511 Purpose :
512 Know limitations, enhancements or remarks
513 Change History
514 Who When What
515 sarakshi 28-oct-2002 ENh#2613933,removed the call of igs_ps_uso_clas_meet_pkg.get_FK_Igs_Ps_Usec_Occur
516
517 (reverse chronological order - newest change first)
518 ***************************************************************/
519
520 BEGIN
521
522 Igs_Ps_Usec_Ocur_Ref_Pkg.Get_FK_Igs_Ps_Usec_Occurs (
523 old_references.unit_section_occurrence_id
524 );
525
526
527 igs_ps_uso_facility_pkg.get_FK_Igs_Ps_Usec_Occurs (
528 old_references.unit_section_occurrence_id
529 );
530
531 igs_ps_uso_instrctrs_pkg.get_FK_Igs_Ps_Usec_Occurs (
532 old_references.unit_section_occurrence_id
533 );
534
535 --Bug 3199686
536 --Created IGS_AS_USEC_SESSNS table
537 igs_as_usec_sessns_pkg.get_fk_igs_ps_usec_occurs( old_references.unit_section_occurrence_id );
538
539 END Check_Child_Existance;
540
541 FUNCTION Get_PK_For_Validation (
542 x_unit_section_occurrence_id IN NUMBER
543 ) RETURN BOOLEAN AS
544
545 /*************************************************************
546 Created By : venagara
547 Date Created By : 2000/05/12
548 Purpose :
549 Know limitations, enhancements or remarks
550 Change History
551 Who When What
552
553 (reverse chronological order - newest change first)
554 ***************************************************************/
555
556 CURSOR cur_rowid IS
557 SELECT rowid
558 FROM igs_ps_usec_occurs_ALL
559 WHERE unit_section_occurrence_id = x_unit_section_occurrence_id
560 FOR UPDATE NOWAIT;
561
562 lv_rowid cur_rowid%RowType;
563
564 BEGIN
565
566 Open cur_rowid;
567 Fetch cur_rowid INTO lv_rowid;
568 IF (cur_rowid%FOUND) THEN
569 Close cur_rowid;
570 Return(TRUE);
571 ELSE
572 Close cur_rowid;
573 Return(FALSE);
574 END IF;
575 END Get_PK_For_Validation;
576
577 FUNCTION Get_UK_For_Validation (
578 x_uoo_id IN NUMBER,
579 x_occurrence_identifier IN VARCHAR2
580 ) RETURN BOOLEAN AS
581
582 /*************************************************************
583 Created By : sarakshi
584 Date Created On : 19-MAY-2005
585 Purpose : Creation of TBH
586 Know limitations, enhancements or remarks
587 Change History
588 Who When What
589
590 (reverse chronological order - newest change first)
591 ***************************************************************/
592
593 CURSOR cur_rowid IS
594 SELECT rowid
595 FROM igs_ps_usec_occurs_all
596 WHERE uoo_id = x_uoo_id
597 AND occurrence_identifier = x_occurrence_identifier
598 AND ((l_rowid is null) or (rowid <> l_rowid));
599 lv_rowid cur_rowid%ROWTYPE;
600
601 BEGIN
602
603 OPEN cur_rowid;
604 FETCH cur_rowid INTO lv_rowid;
605 IF (cur_rowid%FOUND) THEN
606 CLOSE cur_rowid;
607 RETURN (TRUE);
608 ELSE
609 CLOSE cur_rowid;
610 RETURN(FALSE);
611 END IF;
612
613 END Get_UK_For_Validation ;
614
615 PROCEDURE Get_FK_Igs_Ad_Building (
616 x_building_id IN NUMBER
617 ) AS
618
619 /*************************************************************
620 Created By : 19-May-2000
621 Date Created By : venagara
622 Purpose :
623 Know limitations, enhancements or remarks
624 Change History
625 Who When What
626
627 (reverse chronological order - newest change first)
628 ***************************************************************/
629
630 CURSOR cur_rowid IS
631 SELECT rowid
632 FROM igs_ps_usec_occurs_ALL
633 WHERE building_code = x_building_id ;
634
635 lv_rowid cur_rowid%RowType;
636
637 BEGIN
638
639 Open cur_rowid;
640 Fetch cur_rowid INTO lv_rowid;
641 IF (cur_rowid%FOUND) THEN
642 Close cur_rowid;
643 Fnd_Message.Set_Name ('IGS', 'IGS_PS_USO_BLDG_FK');
644 IGS_GE_MSG_STACK.ADD;
645 App_Exception.Raise_Exception;
646 Return;
647 END IF;
648 Close cur_rowid;
649
650 END Get_FK_Igs_Ad_Building;
651
652
653 PROCEDURE Get_FK_Igs_Ad_Room (
654 x_room_id IN NUMBER
655 ) AS
656
657 /*************************************************************
658 Created By : 19-May-2000
659 Date Created By : venagara
660 Purpose :
661 Know limitations, enhancements or remarks
662 Change History
663 Who When What
664
665 (reverse chronological order - newest change first)
666 ***************************************************************/
667
668 CURSOR cur_rowid IS
669 SELECT rowid
670 FROM igs_ps_usec_occurs_ALL
671 WHERE room_code = x_room_id ;
672
673 lv_rowid cur_rowid%RowType;
674
675 BEGIN
676
677 Open cur_rowid;
678 Fetch cur_rowid INTO lv_rowid;
679 IF (cur_rowid%FOUND) THEN
680 Close cur_rowid;
681 Fnd_Message.Set_Name ('IGS', 'IGS_PS_USO_ROOM_FK');
682 IGS_GE_MSG_STACK.ADD;
683 App_Exception.Raise_Exception;
684 Return;
685 END IF;
686 Close cur_rowid;
687
688 END Get_FK_Igs_Ad_Room;
689
690
691 PROCEDURE Get_UFK_Igs_Ps_Unit_Ofr_Opt (
692 x_uoo_id IN NUMBER
693 ) AS
694
695 /*************************************************************
696 Created By : venagara
697 Date Created By : 2000/05/12
698 Purpose :
699 Know limitations, enhancements or remarks
700 Change History
701 Who When What
702
703 (reverse chronological order - newest change first)
704 ***************************************************************/
705
706 CURSOR cur_rowid IS
707 SELECT rowid
708 FROM igs_ps_usec_occurs_ALL
709 WHERE uoo_id = x_uoo_id ;
710
711 lv_rowid cur_rowid%RowType;
712
713 BEGIN
714
715 Open cur_rowid;
716 Fetch cur_rowid INTO lv_rowid;
717 IF (cur_rowid%FOUND) THEN
718 Close cur_rowid;
719 Fnd_Message.Set_Name ('IGS', 'IGS_PS_USO_UOO_UFK');
720 IGS_GE_MSG_STACK.ADD;
721 App_Exception.Raise_Exception;
722 Return;
723 END IF;
724 Close cur_rowid;
725
726 END Get_UFK_Igs_Ps_Unit_Ofr_Opt;
727
728 PROCEDURE Before_DML (
729 p_action IN VARCHAR2,
730 x_rowid IN VARCHAR2 ,
731 x_unit_section_occurrence_id IN NUMBER ,
732 x_uoo_id IN NUMBER ,
733 x_monday IN VARCHAR2 ,
734 x_tuesday IN VARCHAR2 ,
735 x_wednesday IN VARCHAR2 ,
736 x_thursday IN VARCHAR2 ,
737 x_friday IN VARCHAR2 ,
738 x_saturday IN VARCHAR2 ,
739 x_sunday IN VARCHAR2 ,
740 x_start_time IN DATE ,
741 x_end_time IN DATE ,
742 x_building_code IN NUMBER ,
743 x_room_code IN NUMBER ,
744 x_schedule_status IN VARCHAR2 ,
745 x_status_last_updated IN DATE ,
746 x_instructor_id IN NUMBER ,
747 x_attribute_category IN VARCHAR2 ,
748 x_attribute1 IN VARCHAR2 ,
749 x_attribute2 IN VARCHAR2 ,
750 x_attribute3 IN VARCHAR2 ,
751 x_attribute4 IN VARCHAR2 ,
752 x_attribute5 IN VARCHAR2 ,
753 x_attribute6 IN VARCHAR2 ,
754 x_attribute7 IN VARCHAR2 ,
755 x_attribute8 IN VARCHAR2 ,
756 x_attribute9 IN VARCHAR2 ,
757 x_attribute10 IN VARCHAR2 ,
758 x_attribute11 IN VARCHAR2 ,
759 x_attribute12 IN VARCHAR2 ,
760 x_attribute13 IN VARCHAR2 ,
761 x_attribute14 IN VARCHAR2 ,
762 x_attribute15 IN VARCHAR2 ,
763 x_attribute16 IN VARCHAR2 ,
764 x_attribute17 IN VARCHAR2 ,
765 x_attribute18 IN VARCHAR2 ,
766 x_attribute19 IN VARCHAR2 ,
767 x_attribute20 IN VARCHAR2 ,
768 x_error_text IN VARCHAR2 ,
769 x_creation_date IN DATE ,
770 x_created_by IN NUMBER ,
771 x_last_update_date IN DATE ,
772 x_last_updated_by IN NUMBER ,
773 x_last_update_login IN NUMBER ,
774 x_org_id IN NUMBER ,
775 x_start_date IN DATE ,
776 x_end_date In DATE ,
777 x_to_be_announced IN VARCHAR2 ,
778 x_inst_notify_ind IN VARCHAR2 ,
779 x_notify_status IN VARCHAR2 ,
780 x_dedicated_building_code IN NUMBER ,
781 x_dedicated_room_code IN NUMBER ,
782 x_preferred_building_code IN NUMBER ,
783 x_preferred_room_code IN NUMBER,
784 x_preferred_region_code IN VARCHAR2,
785 x_no_set_day_ind IN VARCHAR2,
786 x_cancel_flag IN VARCHAR2 ,
787 x_occurrence_identifier IN VARCHAR2,
788 x_abort_flag IN VARCHAR2
789 ) AS
790 /*************************************************************
791 Created By : venagara
792 Date Created By : 2000/05/12
793 Purpose :
794 Know limitations, enhancements or remarks
795 Change History
796 Who When What
797 smvk 02-Sep-2002 Removed the Default value in the parameters to overcome File.Pkg.22 gscc warnings.
798 As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
799 rgangara 09-May-2001 x_notify_status, x_inst_notify_ind arguments added
800 Sreenivas.Bonam 28-Aug-2000 x_error_text argument added
801 (reverse chronological order - newest change first)
802 ***************************************************************/
803
804 BEGIN
805
806 Set_Column_Values (
807 p_action,
808 x_rowid,
809 x_unit_section_occurrence_id,
810 x_uoo_id,
811 x_monday,
812 x_tuesday,
813 x_wednesday,
814 x_thursday,
815 x_friday,
816 x_saturday,
817 x_sunday,
818 x_start_time,
819 x_end_time,
820 x_building_code,
821 x_room_code,
822 x_schedule_status,
823 x_status_last_updated,
824 x_instructor_id,
825 x_attribute_category ,
826 x_attribute1 ,
827 x_attribute2 ,
828 x_attribute3 ,
829 x_attribute4 ,
830 x_attribute5 ,
831 x_attribute6 ,
832 x_attribute7 ,
833 x_attribute8 ,
834 x_attribute9 ,
835 x_attribute10,
836 x_attribute11,
837 x_attribute12,
838 x_attribute13,
839 x_attribute14,
840 x_attribute15,
841 x_attribute16,
842 x_attribute17,
843 x_attribute18,
844 x_attribute19,
845 x_attribute20,
846 x_error_text,
847 x_creation_date,
848 x_created_by,
849 x_last_update_date,
850 x_last_updated_by,
851 x_last_update_login,
852 x_org_id,
853 x_start_date,
854 x_end_date,
855 x_to_be_announced,
856 x_inst_notify_ind,
857 x_notify_status,
858 x_dedicated_building_code,
859 x_dedicated_room_code,
860 x_preferred_building_code,
861 x_preferred_room_code,
862 x_preferred_region_code,
863 x_no_set_day_ind,
864 x_cancel_flag,
865 x_occurrence_identifier,
866 x_abort_flag
867 );
868
869 IF (p_action = 'INSERT') THEN
870 -- Call all the procedures related to Before Insert.
871
872 IF Get_Pk_For_Validation(
873 new_references.unit_section_occurrence_id) THEN
874 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
875 IGS_GE_MSG_STACK.ADD;
876 App_Exception.Raise_Exception;
877 END IF;
878
879 Check_Uniqueness;
880 Check_Constraints;
881 Check_Parent_Existance;
882 ELSIF (p_action = 'UPDATE') THEN
883 -- Call all the procedures related to Before Update.
884 Check_Uniqueness;
885 Check_Constraints;
886 Check_Parent_Existance;
887 ELSIF (p_action = 'DELETE') THEN
888 -- Call all the procedures related to Before Delete.
889 Check_Child_Existance;
890 ELSIF (p_action = 'VALIDATE_INSERT') THEN
891 -- Call all the procedures related to Before Insert.
892 IF Get_PK_For_Validation (
893 new_references.unit_section_occurrence_id) THEN
894 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
895 IGS_GE_MSG_STACK.ADD;
896 App_Exception.Raise_Exception;
897 END IF;
898 Check_Uniqueness;
899 Check_Constraints;
900 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
901 Check_Uniqueness;
902 Check_Constraints;
903 ELSIF (p_action = 'VALIDATE_DELETE') THEN
904 Check_Child_Existance;
905 END IF;
906
907 END Before_DML;
908
909 PROCEDURE After_DML (
910 p_action IN VARCHAR2,
911 x_rowid IN VARCHAR2
912 ) IS
913 /*************************************************************
914 Created By : venagara
915 Date Created By : 2000/05/12
916 Purpose :
917 Know limitations, enhancements or remarks
918 Change History
919 Who When What
920
921 (reverse chronological order - newest change first)
922 ***************************************************************/
923
924 CURSOR cur_ref_id IS
925 SELECT unit_section_occurrence_id
926 FROM igs_ps_usec_occurs_v
927 WHERE row_id = x_rowid;
928 l_ref_id igs_ps_usec_occurs_v.unit_section_occurrence_id%TYPE;
929 l_error_message VARCHAR2(300);
930
931 BEGIN
932
933 l_rowid := x_rowid;
934
935 IF (p_action = 'INSERT') THEN
936 -- Call all the procedures related to After Insert.
937 -- Null;
938 OPEN cur_ref_id;
939 FETCH cur_ref_id INTO l_ref_id;
940 IF cur_ref_id%FOUND THEN
941 Dflt_usec_occur_ref_code(l_ref_id, l_error_message);
942 CLOSE cur_ref_id;
943 ELSE
944 CLOSE cur_ref_id;
945 END IF;
946
947 ELSIF (p_action = 'UPDATE') THEN
948 -- Call all the procedures related to After Update.
949 Null;
950 ELSIF (p_action = 'DELETE') THEN
951 -- Call all the procedures related to After Delete.
952 Null;
953 END IF;
954
955 l_rowid := NULL;
956
957 END After_DML;
958
959 procedure INSERT_ROW (
960 X_ROWID in out NOCOPY VARCHAR2,
961 x_UNIT_SECTION_OCCURRENCE_ID IN OUT NOCOPY NUMBER,
962 x_UOO_ID IN NUMBER,
963 x_MONDAY IN VARCHAR2,
964 x_TUESDAY IN VARCHAR2,
965 x_WEDNESDAY IN VARCHAR2,
966 x_THURSDAY IN VARCHAR2,
967 x_FRIDAY IN VARCHAR2,
968 x_SATURDAY IN VARCHAR2,
969 x_SUNDAY IN VARCHAR2,
970 x_START_TIME IN DATE,
971 x_END_TIME IN DATE,
972 x_BUILDING_CODE IN NUMBER,
973 x_ROOM_CODE IN NUMBER,
974 x_SCHEDULE_STATUS IN VARCHAR2,
975 x_STATUS_LAST_UPDATED IN DATE,
976 x_INSTRUCTOR_ID IN NUMBER,
977 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
978 X_ATTRIBUTE1 IN VARCHAR2 ,
979 X_ATTRIBUTE2 IN VARCHAR2 ,
980 X_ATTRIBUTE3 IN VARCHAR2 ,
981 X_ATTRIBUTE4 IN VARCHAR2 ,
982 X_ATTRIBUTE5 IN VARCHAR2 ,
983 X_ATTRIBUTE6 IN VARCHAR2 ,
984 X_ATTRIBUTE7 IN VARCHAR2 ,
985 X_ATTRIBUTE8 IN VARCHAR2 ,
986 X_ATTRIBUTE9 IN VARCHAR2 ,
987 X_ATTRIBUTE10 IN VARCHAR2 ,
988 X_ATTRIBUTE11 IN VARCHAR2 ,
989 X_ATTRIBUTE12 IN VARCHAR2 ,
990 X_ATTRIBUTE13 IN VARCHAR2 ,
991 X_ATTRIBUTE14 IN VARCHAR2 ,
992 X_ATTRIBUTE15 IN VARCHAR2 ,
993 X_ATTRIBUTE16 IN VARCHAR2 ,
994 X_ATTRIBUTE17 IN VARCHAR2 ,
995 X_ATTRIBUTE18 IN VARCHAR2 ,
996 X_ATTRIBUTE19 IN VARCHAR2 ,
997 X_ATTRIBUTE20 IN VARCHAR2 ,
998 x_ERROR_TEXT IN VARCHAR2,
999 X_MODE in VARCHAR2 ,
1000 x_org_id in NUMBER,
1001 x_start_date IN DATE,
1002 x_end_date IN DATE,
1003 x_to_be_announced IN VARCHAR2 ,
1004 X_INST_NOTIFY_IND IN VARCHAR2 ,
1005 X_NOTIFY_STATUS IN VARCHAR2 ,
1006 x_dedicated_building_code IN NUMBER ,
1007 x_dedicated_room_code IN NUMBER ,
1008 x_preferred_building_code IN NUMBER ,
1009 x_preferred_room_code IN NUMBER,
1010 x_preferred_region_code IN VARCHAR2,
1011 x_no_set_day_ind IN VARCHAR2,
1012 x_cancel_flag IN VARCHAR2 ,
1013 x_occurrence_identifier IN VARCHAR2,
1014 x_abort_flag IN VARCHAR2
1015 ) AS
1016 /*************************************************************
1017 Created By : venagara
1018 Date Created By : 2000/05/12
1019 Purpose :
1020 Know limitations, enhancements or remarks
1021 Change History
1022 Who When What
1023 smvk 02-Sep-2002 Removed the Default value in the parameters to overcome File.Pkg.22 gscc warnings.
1024 As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
1025 rgangara 09-May-2001 Added x_notify_status, x_inst_notify_ind arguments
1026 Sreenivas.Bonam 28-Aug-2000 Added x_ERROR_TEXT argument
1027 (reverse chronological order - newest change first)
1028 ***************************************************************/
1029
1030 cursor C is select ROWID from IGS_PS_USEC_OCCURS_ALL
1031 where UNIT_SECTION_OCCURRENCE_ID= X_UNIT_SECTION_OCCURRENCE_ID
1032 ;
1033 X_LAST_UPDATE_DATE DATE ;
1034 X_LAST_UPDATED_BY NUMBER ;
1035 X_LAST_UPDATE_LOGIN NUMBER ;
1036 X_REQUEST_ID NUMBER;
1037 X_PROGRAM_ID NUMBER;
1038 X_PROGRAM_APPLICATION_ID NUMBER;
1039 X_PROGRAM_UPDATE_DATE DATE;
1040
1041 begin
1042 X_LAST_UPDATE_DATE := SYSDATE;
1043 if(X_MODE = 'I') then
1044 X_LAST_UPDATED_BY := 1;
1045 X_LAST_UPDATE_LOGIN := 0;
1046 elsif (X_MODE = 'R') then
1047 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1048 if X_LAST_UPDATED_BY is NULL then
1049 X_LAST_UPDATED_BY := -1;
1050 end if;
1051 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1052 if X_LAST_UPDATE_LOGIN is NULL then
1053 X_LAST_UPDATE_LOGIN := -1;
1054 end if;
1055 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1056 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1057 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1058 IF ( X_REQUEST_ID = -1 ) THEN
1059 X_REQUEST_ID := NULL;
1060 X_PROGRAM_ID := NULL;
1061 X_PROGRAM_APPLICATION_ID := NULL;
1062 X_PROGRAM_UPDATE_DATE := NULL;
1063 ELSE
1064 X_PROGRAM_UPDATE_DATE := SYSDATE;
1065 END IF;
1066 else
1067 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1068 IGS_GE_MSG_STACK.ADD;
1069 app_exception.raise_exception;
1070 end if;
1071 SELECT
1072 igs_ps_usec_occurs_s.nextval
1073 INTO
1074 x_UNIT_SECTION_OCCURRENCE_ID
1075 FROM dual;
1076 Before_DML(
1077 p_action=>'INSERT',
1078 x_rowid=>X_ROWID,
1079 x_unit_section_occurrence_id=>X_UNIT_SECTION_OCCURRENCE_ID,
1080 x_uoo_id=>X_UOO_ID,
1081 x_monday=>X_MONDAY,
1082 x_tuesday=>X_TUESDAY,
1083 x_wednesday=>X_WEDNESDAY,
1084 x_thursday=>X_THURSDAY,
1085 x_friday=>X_FRIDAY,
1086 x_saturday=>X_SATURDAY,
1087 x_sunday=>X_SUNDAY,
1088 x_start_time=>X_START_TIME,
1089 x_end_time=>X_END_TIME,
1090 x_building_code=>X_BUILDING_CODE,
1091 x_room_code=>X_ROOM_CODE,
1092 x_schedule_status=>X_SCHEDULE_STATUS,
1093 x_status_last_updated=>X_STATUS_LAST_UPDATED,
1094 x_instructor_id=>X_INSTRUCTOR_ID,
1095 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
1096 x_attribute1=>X_ATTRIBUTE1,
1097 x_attribute2=>X_ATTRIBUTE2,
1098 x_attribute3=>X_ATTRIBUTE3,
1099 x_attribute4=>X_ATTRIBUTE4,
1100 x_attribute5=>X_ATTRIBUTE5,
1101 x_attribute6=>X_ATTRIBUTE6,
1102 x_attribute7=>X_ATTRIBUTE7,
1103 x_attribute8=>X_ATTRIBUTE8,
1104 x_attribute9=>X_ATTRIBUTE9,
1105 x_attribute10=>X_ATTRIBUTE10,
1106 x_attribute11=>X_ATTRIBUTE11,
1107 x_attribute12=>X_ATTRIBUTE12,
1108 x_attribute13=>X_ATTRIBUTE13,
1109 x_attribute14=>X_ATTRIBUTE14,
1110 x_attribute15=>X_ATTRIBUTE15,
1111 x_attribute16=>X_ATTRIBUTE16,
1112 x_attribute17=>X_ATTRIBUTE17,
1113 x_attribute18=>X_ATTRIBUTE18,
1114 x_attribute19=>X_ATTRIBUTE19,
1115 x_attribute20=>X_ATTRIBUTE20,
1116 x_error_text=>X_ERROR_TEXT,
1117 x_creation_date=>X_LAST_UPDATE_DATE,
1118 x_created_by=>X_LAST_UPDATED_BY,
1119 x_last_update_date=>X_LAST_UPDATE_DATE,
1120 x_last_updated_by=>X_LAST_UPDATED_BY,
1121 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1122 x_org_id=>igs_ge_gen_003.get_org_id,
1123 x_start_date => X_START_DATE,
1124 x_end_date => X_END_DATE,
1125 x_to_be_announced => X_TO_BE_ANNOUNCED,
1126 x_inst_notify_ind => X_INST_NOTIFY_IND,
1127 x_notify_status => X_NOTIFY_STATUS,
1128 x_dedicated_building_code => x_dedicated_building_code,
1129 x_dedicated_room_code => x_dedicated_room_code,
1130 x_preferred_building_code => x_preferred_building_code,
1131 x_preferred_room_code => x_preferred_room_code,
1132 x_preferred_region_code => x_preferred_region_code,
1133 x_no_set_day_ind => x_no_set_day_ind,
1134 x_cancel_flag => x_cancel_flag,
1135 x_occurrence_identifier => x_occurrence_identifier,
1136 x_abort_flag => x_abort_flag
1137
1138 );
1139 insert into IGS_PS_USEC_OCCURS_ALL (
1140 UNIT_SECTION_OCCURRENCE_ID
1141 ,UOO_ID
1142 ,MONDAY
1143 ,TUESDAY
1144 ,WEDNESDAY
1145 ,THURSDAY
1146 ,FRIDAY
1147 ,SATURDAY
1148 ,SUNDAY
1149 ,START_TIME
1150 ,END_TIME
1151 ,BUILDING_CODE
1152 ,ROOM_CODE
1153 ,SCHEDULE_STATUS
1154 ,STATUS_LAST_UPDATED
1155 ,INSTRUCTOR_ID
1156 ,ATTRIBUTE_CATEGORY
1157 ,ATTRIBUTE1
1158 ,ATTRIBUTE2
1159 ,ATTRIBUTE3
1160 ,ATTRIBUTE4
1161 ,ATTRIBUTE5
1162 ,ATTRIBUTE6
1163 ,ATTRIBUTE7
1164 ,ATTRIBUTE8
1165 ,ATTRIBUTE9
1166 ,ATTRIBUTE10
1167 ,ATTRIBUTE11
1168 ,ATTRIBUTE12
1169 ,ATTRIBUTE13
1170 ,ATTRIBUTE14
1171 ,ATTRIBUTE15
1172 ,ATTRIBUTE16
1173 ,ATTRIBUTE17
1174 ,ATTRIBUTE18
1175 ,ATTRIBUTE19
1176 ,ATTRIBUTE20
1177 ,ERROR_TEXT
1178 ,CREATION_DATE
1179 ,CREATED_BY
1180 ,LAST_UPDATE_DATE
1181 ,LAST_UPDATED_BY
1182 ,LAST_UPDATE_LOGIN
1183 ,REQUEST_ID
1184 ,PROGRAM_ID
1185 ,PROGRAM_APPLICATION_ID
1186 ,PROGRAM_UPDATE_DATE
1187 ,ORG_ID
1188 ,START_DATE
1189 ,END_DATE
1190 ,TO_BE_ANNOUNCED
1191 ,INST_NOTIFY_IND
1192 ,NOTIFY_STATUS
1193 ,dedicated_building_code,
1194 dedicated_room_code,
1195 preferred_building_code,
1196 preferred_room_code,
1197 preferred_region_code,
1198 no_set_day_ind,
1199 cancel_flag,
1200 occurrence_identifier,
1201 abort_flag
1202
1203 ) values (
1204 NEW_REFERENCES.UNIT_SECTION_OCCURRENCE_ID
1205 ,NEW_REFERENCES.UOO_ID
1206 ,NEW_REFERENCES.MONDAY
1207 ,NEW_REFERENCES.TUESDAY
1208 ,NEW_REFERENCES.WEDNESDAY
1209 ,NEW_REFERENCES.THURSDAY
1210 ,NEW_REFERENCES.FRIDAY
1211 ,NEW_REFERENCES.SATURDAY
1212 ,NEW_REFERENCES.SUNDAY
1213 ,NEW_REFERENCES.START_TIME
1214 ,NEW_REFERENCES.END_TIME
1215 ,NEW_REFERENCES.BUILDING_CODE
1216 ,NEW_REFERENCES.ROOM_CODE
1217 ,NEW_REFERENCES.SCHEDULE_STATUS
1218 ,NEW_REFERENCES.STATUS_LAST_UPDATED
1219 ,NEW_REFERENCES.INSTRUCTOR_ID
1220 ,NEW_REFERENCES.ATTRIBUTE_CATEGORY
1221 ,NEW_REFERENCES.ATTRIBUTE1
1222 ,NEW_REFERENCES.ATTRIBUTE2
1223 ,NEW_REFERENCES.ATTRIBUTE3
1224 ,NEW_REFERENCES.ATTRIBUTE4
1225 ,NEW_REFERENCES.ATTRIBUTE5
1226 ,NEW_REFERENCES.ATTRIBUTE6
1227 ,NEW_REFERENCES.ATTRIBUTE7
1228 ,NEW_REFERENCES.ATTRIBUTE8
1229 ,NEW_REFERENCES.ATTRIBUTE9
1230 ,NEW_REFERENCES.ATTRIBUTE10
1231 ,NEW_REFERENCES.ATTRIBUTE11
1232 ,NEW_REFERENCES.ATTRIBUTE12
1233 ,NEW_REFERENCES.ATTRIBUTE13
1234 ,NEW_REFERENCES.ATTRIBUTE14
1235 ,NEW_REFERENCES.ATTRIBUTE15
1236 ,NEW_REFERENCES.ATTRIBUTE16
1237 ,NEW_REFERENCES.ATTRIBUTE17
1238 ,NEW_REFERENCES.ATTRIBUTE18
1239 ,NEW_REFERENCES.ATTRIBUTE19
1240 ,NEW_REFERENCES.ATTRIBUTE20
1241 ,NEW_REFERENCES.ERROR_TEXT
1242 ,X_LAST_UPDATE_DATE
1243 ,X_LAST_UPDATED_BY
1244 ,X_LAST_UPDATE_DATE
1245 ,X_LAST_UPDATED_BY
1246 ,X_LAST_UPDATE_LOGIN
1247 ,X_REQUEST_ID
1248 ,X_PROGRAM_ID
1249 ,X_PROGRAM_APPLICATION_ID
1250 ,X_PROGRAM_UPDATE_DATE
1251 ,NEW_REFERENCES.ORG_ID
1252 ,NEW_REFERENCES.START_DATE
1253 ,NEW_REFERENCES.END_DATE
1254 ,NEW_REFERENCES.TO_BE_ANNOUNCED
1255 ,NEW_REFERENCES.INST_NOTIFY_IND
1256 ,NEW_REFERENCES.NOTIFY_STATUS
1257 ,new_references.dedicated_building_code
1258 ,new_references.dedicated_room_code
1259 ,new_references.preferred_building_code
1260 ,new_references.preferred_room_code
1261 ,new_references.preferred_region_code
1262 ,new_references.no_set_day_ind
1263 ,new_references.cancel_flag
1264 ,new_references.occurrence_identifier
1265 ,new_references.abort_flag
1266
1267 );
1268 open c;
1269 fetch c into X_ROWID;
1270 if (c%notfound) then
1271 close c;
1272 raise no_data_found;
1273 end if;
1274 close c;
1275 After_DML (
1276 p_action => 'INSERT' ,
1277 x_rowid => X_ROWID );
1278 end INSERT_ROW;
1279
1280 procedure LOCK_ROW (
1281 X_ROWID in VARCHAR2,
1282 x_UNIT_SECTION_OCCURRENCE_ID IN NUMBER,
1283 x_UOO_ID IN NUMBER,
1284 x_MONDAY IN VARCHAR2,
1285 x_TUESDAY IN VARCHAR2,
1286 x_WEDNESDAY IN VARCHAR2,
1287 x_THURSDAY IN VARCHAR2,
1288 x_FRIDAY IN VARCHAR2,
1289 x_SATURDAY IN VARCHAR2,
1290 x_SUNDAY IN VARCHAR2,
1291 x_START_TIME IN DATE,
1292 x_END_TIME IN DATE,
1293 x_BUILDING_CODE IN NUMBER,
1294 x_ROOM_CODE IN NUMBER,
1295 x_SCHEDULE_STATUS IN VARCHAR2,
1296 x_STATUS_LAST_UPDATED IN DATE,
1297 x_INSTRUCTOR_ID IN NUMBER,
1298 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
1299 X_ATTRIBUTE1 IN VARCHAR2 ,
1300 X_ATTRIBUTE2 IN VARCHAR2 ,
1301 X_ATTRIBUTE3 IN VARCHAR2 ,
1302 X_ATTRIBUTE4 IN VARCHAR2 ,
1303 X_ATTRIBUTE5 IN VARCHAR2 ,
1304 X_ATTRIBUTE6 IN VARCHAR2 ,
1305 X_ATTRIBUTE7 IN VARCHAR2 ,
1306 X_ATTRIBUTE8 IN VARCHAR2 ,
1307 X_ATTRIBUTE9 IN VARCHAR2 ,
1308 X_ATTRIBUTE10 IN VARCHAR2 ,
1309 X_ATTRIBUTE11 IN VARCHAR2 ,
1310 X_ATTRIBUTE12 IN VARCHAR2 ,
1311 X_ATTRIBUTE13 IN VARCHAR2 ,
1312 X_ATTRIBUTE14 IN VARCHAR2 ,
1313 X_ATTRIBUTE15 IN VARCHAR2 ,
1314 X_ATTRIBUTE16 IN VARCHAR2 ,
1315 X_ATTRIBUTE17 IN VARCHAR2 ,
1316 X_ATTRIBUTE18 IN VARCHAR2 ,
1317 X_ATTRIBUTE19 IN VARCHAR2 ,
1318 X_ATTRIBUTE20 IN VARCHAR2 ,
1319 x_ERROR_TEXT IN VARCHAR2,
1320 X_START_DATE IN DATE,
1321 X_END_DATE IN DATE,
1322 X_TO_BE_ANNOUNCED IN VARCHAR2,
1323 X_INST_NOTIFY_IND IN VARCHAR2 ,
1324 X_NOTIFY_STATUS IN VARCHAR2 ,
1325 x_dedicated_building_code IN NUMBER ,
1326 x_dedicated_room_code IN NUMBER ,
1327 x_preferred_building_code IN NUMBER ,
1328 x_preferred_room_code IN NUMBER,
1329 x_preferred_region_code IN VARCHAR2,
1330 x_no_set_day_ind IN VARCHAR2,
1331 x_cancel_flag IN VARCHAR2,
1332 x_occurrence_identifier IN VARCHAR2,
1333 x_abort_flag IN VARCHAR2
1334
1335 ) AS
1336
1337 /*************************************************************
1338 Created By : venagara
1339 Date Created By : 2000/05/12
1340 Purpose :
1341 Know limitations, enhancements or remarks
1342 Change History
1343 Who When What
1344 smvk 02-Sep-2002 Removed the Default value in the parameters to overcome File.Pkg.22 gscc warnings.
1345 As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
1346 smvk 27-Jun-2002 Truncating status_last_updated as a part of bug # 2427627
1347 rgangara 09-May-2001 Added x_NOTIFY_STATUS, x_INST_NOTIFY_IND args
1348 Sreenivas.Bonam 28-Aug-2000 Added x_ERROR_TEXT argument
1349 (reverse chronological order - newest change first)
1350 ***************************************************************/
1351
1352 cursor c1 is select
1353 UOO_ID
1354 , MONDAY
1355 , TUESDAY
1356 , WEDNESDAY
1357 , THURSDAY
1358 , FRIDAY
1359 , SATURDAY
1360 , SUNDAY
1361 , START_TIME
1362 , END_TIME
1363 , BUILDING_CODE
1364 , ROOM_CODE
1365 , SCHEDULE_STATUS
1366 , STATUS_LAST_UPDATED
1367 , INSTRUCTOR_ID
1368 , ATTRIBUTE_CATEGORY
1369 , ATTRIBUTE1
1370 , ATTRIBUTE2
1371 , ATTRIBUTE3
1372 , ATTRIBUTE4
1373 , ATTRIBUTE5
1374 , ATTRIBUTE6
1375 , ATTRIBUTE7
1376 , ATTRIBUTE8
1377 , ATTRIBUTE9
1378 , ATTRIBUTE10
1379 , ATTRIBUTE11
1380 , ATTRIBUTE12
1381 , ATTRIBUTE13
1382 , ATTRIBUTE14
1383 , ATTRIBUTE15
1384 , ATTRIBUTE16
1385 , ATTRIBUTE17
1386 , ATTRIBUTE18
1387 , ATTRIBUTE19
1388 , ATTRIBUTE20
1389 , ERROR_TEXT
1390 , START_DATE
1391 , END_DATE
1392 , TO_BE_ANNOUNCED
1393 , INST_NOTIFY_IND
1394 , NOTIFY_STATUS,
1395 dedicated_building_code,
1396 dedicated_room_code,
1397 preferred_building_code,
1398 preferred_room_code,
1399 preferred_region_code,
1400 no_set_day_ind,
1401 cancel_flag,
1402 occurrence_identifier,
1403 abort_flag
1404 from IGS_PS_USEC_OCCURS_ALL
1405 where ROWID = X_ROWID
1406 for update nowait;
1407 tlinfo c1%rowtype;
1408 begin
1409 open c1;
1410 fetch c1 into tlinfo;
1411 if (c1%notfound) then
1412 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1413 IGS_GE_MSG_STACK.ADD;
1414 close c1;
1415 app_exception.raise_exception;
1416 return;
1417 end if;
1418 close c1;
1419 if (( tlinfo.UOO_ID = X_UOO_ID)
1420 AND ((tlinfo.MONDAY = X_MONDAY)
1421 OR ((tlinfo.MONDAY is null)
1422 AND (X_MONDAY is null)))
1423 AND ((tlinfo.TUESDAY = X_TUESDAY)
1424 OR ((tlinfo.TUESDAY is null)
1425 AND (X_TUESDAY is null)))
1426 AND ((tlinfo.WEDNESDAY = X_WEDNESDAY)
1427 OR ((tlinfo.WEDNESDAY is null)
1428 AND (X_WEDNESDAY is null)))
1429 AND ((tlinfo.THURSDAY = X_THURSDAY)
1430 OR ((tlinfo.THURSDAY is null)
1431 AND (X_THURSDAY is null)))
1432 AND ((tlinfo.FRIDAY = X_FRIDAY)
1433 OR ((tlinfo.FRIDAY is null)
1434 AND (X_FRIDAY is null)))
1435 AND ((tlinfo.SATURDAY = X_SATURDAY)
1436 OR ((tlinfo.SATURDAY is null)
1437 AND (X_SATURDAY is null)))
1438 AND ((tlinfo.SUNDAY = X_SUNDAY)
1439 OR ((tlinfo.SUNDAY is null)
1440 AND (X_SUNDAY is null)))
1441 AND ((tlinfo.START_TIME = X_START_TIME)
1442 OR ((tlinfo.START_TIME is null)
1443 AND (X_START_TIME is null)))
1444 AND ((tlinfo.END_TIME = X_END_TIME)
1445 OR ((tlinfo.END_TIME is null)
1446 AND (X_END_TIME is null)))
1447 AND ((tlinfo.BUILDING_CODE = X_BUILDING_CODE)
1448 OR ((tlinfo.BUILDING_CODE is null)
1449 AND (X_BUILDING_CODE is null)))
1450 AND ((tlinfo.ROOM_CODE = X_ROOM_CODE)
1451 OR ((tlinfo.ROOM_CODE is null)
1452 AND (X_ROOM_CODE is null)))
1453 AND ((tlinfo.SCHEDULE_STATUS = X_SCHEDULE_STATUS)
1454 OR ((tlinfo.SCHEDULE_STATUS is null)
1455 AND (X_SCHEDULE_STATUS is null)))
1456 AND ((trunc(tlinfo.STATUS_LAST_UPDATED) = trunc(X_STATUS_LAST_UPDATED))
1457 OR ((tlinfo.STATUS_LAST_UPDATED is null)
1458 AND (X_STATUS_LAST_UPDATED is null)))
1459 AND ((tlinfo.ERROR_TEXT = X_ERROR_TEXT)
1460 OR ((tlinfo.ERROR_TEXT is null)
1461 AND (X_ERROR_TEXT is null)))
1462 AND ((tlinfo.INSTRUCTOR_ID = X_INSTRUCTOR_ID)
1463 OR ((tlinfo.INSTRUCTOR_ID is null)
1464 AND (X_INSTRUCTOR_ID is null)))
1465 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
1466 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
1467 AND (X_ATTRIBUTE_CATEGORY is null)))
1468 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
1469 OR ((tlinfo.ATTRIBUTE1 is null)
1470 AND (X_ATTRIBUTE1 is null)))
1471 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
1472 OR ((tlinfo.ATTRIBUTE2 is null)
1473 AND (X_ATTRIBUTE2 is null)))
1474 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
1475 OR ((tlinfo.ATTRIBUTE3 is null)
1476 AND (X_ATTRIBUTE3 is null)))
1477 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
1478 OR ((tlinfo.ATTRIBUTE4 is null)
1479 AND (X_ATTRIBUTE4 is null)))
1480 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
1481 OR ((tlinfo.ATTRIBUTE5 is null)
1482 AND (X_ATTRIBUTE5 is null)))
1483 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
1484 OR ((tlinfo.ATTRIBUTE6 is null)
1485 AND (X_ATTRIBUTE6 is null)))
1486 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
1487 OR ((tlinfo.ATTRIBUTE7 is null)
1488 AND (X_ATTRIBUTE7 is null)))
1489 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
1490 OR ((tlinfo.ATTRIBUTE8 is null)
1491 AND (X_ATTRIBUTE8 is null)))
1492 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
1493 OR ((tlinfo.ATTRIBUTE9 is null)
1494 AND (X_ATTRIBUTE9 is null)))
1495 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
1496 OR ((tlinfo.ATTRIBUTE10 is null)
1497 AND (X_ATTRIBUTE10 is null)))
1498 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
1499 OR ((tlinfo.ATTRIBUTE11 is null)
1500 AND (X_ATTRIBUTE11 is null)))
1501 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
1502 OR ((tlinfo.ATTRIBUTE12 is null)
1503 AND (X_ATTRIBUTE12 is null)))
1504 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
1505 OR ((tlinfo.ATTRIBUTE13 is null)
1506 AND (X_ATTRIBUTE13 is null)))
1507 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
1508 OR ((tlinfo.ATTRIBUTE14 is null)
1509 AND (X_ATTRIBUTE14 is null)))
1510 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
1511 OR ((tlinfo.ATTRIBUTE15 is null)
1512 AND (X_ATTRIBUTE15 is null)))
1513 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
1514 OR ((tlinfo.ATTRIBUTE16 is null)
1515 AND (X_ATTRIBUTE16 is null)))
1516 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
1517 OR ((tlinfo.ATTRIBUTE17 is null)
1518 AND (X_ATTRIBUTE17 is null)))
1519 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
1520 OR ((tlinfo.ATTRIBUTE18 is null)
1521 AND (X_ATTRIBUTE18 is null)))
1522 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
1523 OR ((tlinfo.ATTRIBUTE19 is null)
1524 AND (X_ATTRIBUTE19 is null)))
1525 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
1526 OR ((tlinfo.ATTRIBUTE20 is null)
1527 AND (X_ATTRIBUTE20 is null)))
1528 AND ((tlinfo.ERROR_TEXT = X_ERROR_TEXT)
1529 OR ((tlinfo.ERROR_TEXT is null)
1530 AND (X_ERROR_TEXT is null)))
1531 AND ((trunc(tlinfo.START_DATE) = trunc(X_START_DATE))
1532 OR ((tlinfo.START_DATE is null)
1533 AND (X_START_DATE is null)))
1534 AND ((trunc(tlinfo.END_DATE) = trunc(X_END_DATE))
1535 OR ((tlinfo.END_DATE is null)
1536 AND (X_END_DATE is null)))
1537 AND ((tlinfo.TO_BE_ANNOUNCED = X_TO_BE_ANNOUNCED)
1538 OR ((tlinfo.TO_BE_ANNOUNCED is null)
1539 AND (X_TO_BE_ANNOUNCED is null)))
1540 AND ((tlinfo.NOTIFY_STATUS = X_NOTIFY_STATUS)
1541 OR ((tlinfo.NOTIFY_STATUS is null)
1542 AND (X_NOTIFY_STATUS is null)))
1543 AND ((tlinfo.INST_NOTIFY_IND = X_INST_NOTIFY_IND)
1544 OR ((tlinfo.INST_NOTIFY_IND is null)
1545 AND (X_INST_NOTIFY_IND is null)))
1546 AND ((tlinfo.dedicated_building_code = X_dedicated_building_code)
1547 OR ((tlinfo.dedicated_building_code is null)
1548 AND (X_dedicated_building_code is null)))
1549 AND ((tlinfo.dedicated_room_code = X_dedicated_room_code)
1550 OR ((tlinfo.dedicated_room_code is null)
1551 AND (X_dedicated_room_code is null)))
1552 AND ((tlinfo.preferred_building_code = X_preferred_building_code)
1553 OR ((tlinfo.preferred_building_code is null)
1554 AND (X_preferred_building_code is null)))
1555 AND ((tlinfo.preferred_room_code = X_preferred_room_code)
1556 OR ((tlinfo.preferred_room_code is null)
1557 AND (X_preferred_room_code is null)))
1558 AND ((tlinfo.preferred_region_code = X_preferred_region_code)
1559 OR ((tlinfo.preferred_region_code is null)
1560 AND (X_preferred_region_code is null)))
1561 AND ((tlinfo.no_set_day_ind = X_no_set_day_ind)
1562 OR ((tlinfo.no_set_day_ind is null)
1563 AND (X_no_set_day_ind is null)))
1564 AND ((tlinfo.cancel_flag = X_cancel_flag)
1565 OR ((tlinfo.cancel_flag is null)
1566 AND (X_cancel_flag is null)))
1567
1568 AND ( tlinfo.occurrence_identifier = x_occurrence_identifier)
1569 AND ( tlinfo.abort_flag = x_abort_flag)
1570
1571 ) then
1572 null;
1573 else
1574 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1575 IGS_GE_MSG_STACK.ADD;
1576 app_exception.raise_exception;
1577 end if;
1578 return;
1579 end LOCK_ROW;
1580 Procedure UPDATE_ROW (
1581 X_ROWID in VARCHAR2,
1582 x_UNIT_SECTION_OCCURRENCE_ID IN NUMBER,
1583 x_UOO_ID IN NUMBER,
1584 x_MONDAY IN VARCHAR2,
1585 x_TUESDAY IN VARCHAR2,
1586 x_WEDNESDAY IN VARCHAR2,
1587 x_THURSDAY IN VARCHAR2,
1588 x_FRIDAY IN VARCHAR2,
1589 x_SATURDAY IN VARCHAR2,
1590 x_SUNDAY IN VARCHAR2,
1591 x_START_TIME IN DATE,
1592 x_END_TIME IN DATE,
1593 x_BUILDING_CODE IN NUMBER,
1594 x_ROOM_CODE IN NUMBER,
1595 x_SCHEDULE_STATUS IN VARCHAR2,
1596 x_STATUS_LAST_UPDATED IN DATE,
1597 x_INSTRUCTOR_ID IN NUMBER,
1598 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
1599 X_ATTRIBUTE1 IN VARCHAR2 ,
1600 X_ATTRIBUTE2 IN VARCHAR2 ,
1601 X_ATTRIBUTE3 IN VARCHAR2 ,
1602 X_ATTRIBUTE4 IN VARCHAR2 ,
1603 X_ATTRIBUTE5 IN VARCHAR2 ,
1604 X_ATTRIBUTE6 IN VARCHAR2 ,
1605 X_ATTRIBUTE7 IN VARCHAR2 ,
1606 X_ATTRIBUTE8 IN VARCHAR2 ,
1607 X_ATTRIBUTE9 IN VARCHAR2 ,
1608 X_ATTRIBUTE10 IN VARCHAR2 ,
1609 X_ATTRIBUTE11 IN VARCHAR2 ,
1610 X_ATTRIBUTE12 IN VARCHAR2 ,
1611 X_ATTRIBUTE13 IN VARCHAR2 ,
1612 X_ATTRIBUTE14 IN VARCHAR2 ,
1613 X_ATTRIBUTE15 IN VARCHAR2 ,
1614 X_ATTRIBUTE16 IN VARCHAR2 ,
1615 X_ATTRIBUTE17 IN VARCHAR2 ,
1616 X_ATTRIBUTE18 IN VARCHAR2 ,
1617 X_ATTRIBUTE19 IN VARCHAR2 ,
1618 X_ATTRIBUTE20 IN VARCHAR2 ,
1619 x_ERROR_TEXT IN VARCHAR2,
1620 X_MODE in VARCHAR2 ,
1621 X_START_DATE IN DATE,
1622 X_END_DATE IN DATE,
1623 X_TO_BE_ANNOUNCED IN VARCHAR2,
1624 X_INST_NOTIFY_IND IN VARCHAR2 ,
1625 X_NOTIFY_STATUS IN VARCHAR2 ,
1626 x_dedicated_building_code IN NUMBER ,
1627 x_dedicated_room_code IN NUMBER ,
1628 x_preferred_building_code IN NUMBER ,
1629 x_preferred_room_code IN NUMBER ,
1630 x_preferred_region_code IN VARCHAR2,
1631 x_no_set_day_ind IN VARCHAR2,
1632 x_cancel_flag IN VARCHAR2 ,
1633 x_occurrence_identifier IN VARCHAR2,
1634 x_abort_flag IN VARCHAR2
1635
1636 ) AS
1637 /*************************************************************
1638 Created By : venagara
1639 Date Created By : 2000/05/12
1640 Purpose :
1641 Know limitations, enhancements or remarks
1642 Change History
1643 Who When What
1644 sarakshi 22-Nov-2005 Bug#4589690, error text to be nullified if the status is not ERROR
1645 smvk 02-Sep-2002 Removed the Default value in the parameters to overcome File.Pkg.22 gscc warnings.
1646 As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
1647 rgangara 09-May-2001 Added x_NOTIFY_STATUS, x_INST_NOTIFY_IND argument
1648 Sreenivas.Bonam 28-Aug-2000 Added x_ERROR_TEXT argument
1649 (reverse chronological order - newest change first)
1650 ***************************************************************/
1651
1652 X_LAST_UPDATE_DATE DATE ;
1653 X_LAST_UPDATED_BY NUMBER ;
1654 X_LAST_UPDATE_LOGIN NUMBER ;
1655 X_REQUEST_ID NUMBER;
1656 X_PROGRAM_ID NUMBER;
1657 X_PROGRAM_APPLICATION_ID NUMBER;
1658 X_PROGRAM_UPDATE_DATE DATE;
1659 begin
1660 X_LAST_UPDATE_DATE := SYSDATE;
1661 if(X_MODE = 'I') then
1662 X_LAST_UPDATED_BY := 1;
1663 X_LAST_UPDATE_LOGIN := 0;
1664 elsif (X_MODE = 'R') then
1665 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1666 if X_LAST_UPDATED_BY is NULL then
1667 X_LAST_UPDATED_BY := -1;
1668 end if;
1669 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1670 if X_LAST_UPDATE_LOGIN is NULL then
1671 X_LAST_UPDATE_LOGIN := -1;
1672 end if;
1673 else
1674 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1675 IGS_GE_MSG_STACK.ADD;
1676 app_exception.raise_exception;
1677 end if;
1678 Before_DML(
1679 p_action=>'UPDATE',
1680 x_rowid=>X_ROWID,
1681 x_unit_section_occurrence_id=>X_UNIT_SECTION_OCCURRENCE_ID,
1682 x_uoo_id=>X_UOO_ID,
1683 x_monday=>X_MONDAY,
1684 x_tuesday=>X_TUESDAY,
1685 x_wednesday=>X_WEDNESDAY,
1686 x_thursday=>X_THURSDAY,
1687 x_friday=>X_FRIDAY,
1688 x_saturday=>X_SATURDAY,
1689 x_sunday=>X_SUNDAY,
1690 x_start_time=>X_START_TIME,
1691 x_end_time=>X_END_TIME,
1692 x_building_code=>X_BUILDING_CODE,
1693 x_room_code=>X_ROOM_CODE,
1694 x_schedule_status=>X_SCHEDULE_STATUS,
1695 x_status_last_updated=>X_STATUS_LAST_UPDATED,
1696 x_instructor_id=>X_INSTRUCTOR_ID,
1697 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
1698 x_attribute1=>X_ATTRIBUTE1,
1699 x_attribute2=>X_ATTRIBUTE2,
1700 x_attribute3=>X_ATTRIBUTE3,
1701 x_attribute4=>X_ATTRIBUTE4,
1702 x_attribute5=>X_ATTRIBUTE5,
1703 x_attribute6=>X_ATTRIBUTE6,
1704 x_attribute7=>X_ATTRIBUTE7,
1705 x_attribute8=>X_ATTRIBUTE8,
1706 x_attribute9=>X_ATTRIBUTE9,
1707 x_attribute10=>X_ATTRIBUTE10,
1708 x_attribute11=>X_ATTRIBUTE11,
1709 x_attribute12=>X_ATTRIBUTE12,
1710 x_attribute13=>X_ATTRIBUTE13,
1711 x_attribute14=>X_ATTRIBUTE14,
1712 x_attribute15=>X_ATTRIBUTE15,
1713 x_attribute16=>X_ATTRIBUTE16,
1714 x_attribute17=>X_ATTRIBUTE17,
1715 x_attribute18=>X_ATTRIBUTE18,
1716 x_attribute19=>X_ATTRIBUTE19,
1717 x_attribute20=>X_ATTRIBUTE20,
1718 x_error_text=>X_ERROR_TEXT,
1719 x_creation_date=>X_LAST_UPDATE_DATE,
1720 x_created_by=>X_LAST_UPDATED_BY,
1721 x_last_update_date=>X_LAST_UPDATE_DATE,
1722 x_last_updated_by=>X_LAST_UPDATED_BY,
1723 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1724 x_start_date => X_START_DATE,
1725 x_end_date => X_END_DATE,
1726 x_to_be_announced => X_TO_BE_ANNOUNCED,
1727 x_inst_notify_ind => X_INST_NOTIFY_IND,
1728 x_notify_status => X_NOTIFY_STATUS,
1729 x_dedicated_building_code => x_dedicated_building_code,
1730 x_dedicated_room_code => x_dedicated_room_code,
1731 x_preferred_building_code => x_preferred_building_code,
1732 x_preferred_room_code => x_preferred_room_code,
1733 x_preferred_region_code => x_preferred_region_code,
1734 x_no_set_day_ind => x_no_set_day_ind,
1735 x_cancel_flag => x_cancel_flag,
1736 x_occurrence_identifier =>x_occurrence_identifier,
1737 x_abort_flag => x_abort_flag
1738 );
1739
1740 IF ( X_MODE = 'R' ) THEN
1741 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1742 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1743 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1744 IF ( X_REQUEST_ID = -1 ) THEN
1745 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1746 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
1747 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1748 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1749 ELSE
1750 X_PROGRAM_UPDATE_DATE := SYSDATE;
1751 END IF;
1752 END IF;
1753
1754 update IGS_PS_USEC_OCCURS_ALL set
1755 UOO_ID = NEW_REFERENCES.UOO_ID,
1756 MONDAY = NEW_REFERENCES.MONDAY,
1757 TUESDAY = NEW_REFERENCES.TUESDAY,
1758 WEDNESDAY = NEW_REFERENCES.WEDNESDAY,
1759 THURSDAY = NEW_REFERENCES.THURSDAY,
1760 FRIDAY = NEW_REFERENCES.FRIDAY,
1761 SATURDAY = NEW_REFERENCES.SATURDAY,
1762 SUNDAY = NEW_REFERENCES.SUNDAY,
1763 START_TIME = NEW_REFERENCES.START_TIME,
1764 END_TIME = NEW_REFERENCES.END_TIME,
1765 BUILDING_CODE = NEW_REFERENCES.BUILDING_CODE,
1766 ROOM_CODE = NEW_REFERENCES.ROOM_CODE,
1767 SCHEDULE_STATUS = NEW_REFERENCES.SCHEDULE_STATUS,
1768 STATUS_LAST_UPDATED = NEW_REFERENCES.STATUS_LAST_UPDATED,
1769 INSTRUCTOR_ID = NEW_REFERENCES.INSTRUCTOR_ID,
1770 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1771 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1772 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1773 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1774 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1775 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1776 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1777 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1778 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1779 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1780 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1781 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
1782 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
1783 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
1784 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
1785 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
1786 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
1787 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
1788 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
1789 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
1790 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
1791 ERROR_TEXT = (SELECT DECODE(NEW_REFERENCES.SCHEDULE_STATUS,'ERROR',NEW_REFERENCES.ERROR_TEXT,NULL) FROM DUAL),
1792 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1793 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1794 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1795 REQUEST_ID = X_REQUEST_ID,
1796 PROGRAM_ID = X_PROGRAM_ID,
1797 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1798 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
1799 START_DATE = X_START_DATE,
1800 END_DATE = X_END_DATE,
1801 TO_BE_ANNOUNCED = X_TO_BE_ANNOUNCED,
1802 INST_NOTIFY_IND = NEW_REFERENCES.INST_NOTIFY_IND,
1803 NOTIFY_STATUS = NEW_REFERENCES.NOTIFY_STATUS,
1804 dedicated_building_code = new_references.dedicated_building_code,
1805 dedicated_room_code = new_references.dedicated_room_code,
1806 preferred_building_code = new_references.preferred_building_code,
1807 preferred_room_code = new_references.preferred_room_code,
1808 preferred_region_code = new_references.preferred_region_code,
1809 no_set_day_ind = new_references.no_set_day_ind,
1810 cancel_flag = new_references.cancel_flag,
1811 occurrence_identifier = new_references.occurrence_identifier ,
1812 abort_flag = new_references.abort_flag
1813
1814 where ROWID = X_ROWID;
1815 if (sql%notfound) then
1816 raise no_data_found;
1817 end if;
1818
1819 After_DML (
1820 p_action => 'UPDATE' ,
1821 x_rowid => X_ROWID
1822 );
1823 end UPDATE_ROW;
1824 procedure ADD_ROW (
1825 X_ROWID in out NOCOPY VARCHAR2,
1826 x_UNIT_SECTION_OCCURRENCE_ID IN OUT NOCOPY NUMBER,
1827 x_UOO_ID IN NUMBER,
1828 x_MONDAY IN VARCHAR2,
1829 x_TUESDAY IN VARCHAR2,
1830 x_WEDNESDAY IN VARCHAR2,
1831 x_THURSDAY IN VARCHAR2,
1832 x_FRIDAY IN VARCHAR2,
1833 x_SATURDAY IN VARCHAR2,
1834 x_SUNDAY IN VARCHAR2,
1835 x_START_TIME IN DATE,
1836 x_END_TIME IN DATE,
1837 x_BUILDING_CODE IN NUMBER,
1838 x_ROOM_CODE IN NUMBER,
1839 x_SCHEDULE_STATUS IN VARCHAR2,
1840 x_STATUS_LAST_UPDATED IN DATE,
1841 x_INSTRUCTOR_ID IN NUMBER,
1842 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
1843 X_ATTRIBUTE1 IN VARCHAR2 ,
1844 X_ATTRIBUTE2 IN VARCHAR2 ,
1845 X_ATTRIBUTE3 IN VARCHAR2 ,
1846 X_ATTRIBUTE4 IN VARCHAR2 ,
1847 X_ATTRIBUTE5 IN VARCHAR2 ,
1848 X_ATTRIBUTE6 IN VARCHAR2 ,
1849 X_ATTRIBUTE7 IN VARCHAR2 ,
1850 X_ATTRIBUTE8 IN VARCHAR2 ,
1851 X_ATTRIBUTE9 IN VARCHAR2 ,
1852 X_ATTRIBUTE10 IN VARCHAR2 ,
1853 X_ATTRIBUTE11 IN VARCHAR2 ,
1854 X_ATTRIBUTE12 IN VARCHAR2 ,
1855 X_ATTRIBUTE13 IN VARCHAR2 ,
1856 X_ATTRIBUTE14 IN VARCHAR2 ,
1857 X_ATTRIBUTE15 IN VARCHAR2 ,
1858 X_ATTRIBUTE16 IN VARCHAR2 ,
1859 X_ATTRIBUTE17 IN VARCHAR2 ,
1860 X_ATTRIBUTE18 IN VARCHAR2 ,
1861 X_ATTRIBUTE19 IN VARCHAR2 ,
1862 X_ATTRIBUTE20 IN VARCHAR2 ,
1863 x_ERROR_TEXT IN VARCHAR2,
1864 X_MODE in VARCHAR2,
1865 X_ORG_ID IN NUMBER ,
1866 X_START_DATE IN DATE,
1867 X_END_DATE IN DATE,
1868 X_TO_BE_ANNOUNCED In VARCHAR2 ,
1869 X_INST_NOTIFY_IND IN VARCHAR2 ,
1870 X_NOTIFY_STATUS IN VARCHAR2 ,
1871 x_dedicated_building_code IN NUMBER ,
1872 x_dedicated_room_code IN NUMBER ,
1873 x_preferred_building_code IN NUMBER ,
1874 x_preferred_room_code IN NUMBER ,
1875 x_preferred_region_code IN VARCHAR2,
1876 x_no_set_day_ind IN VARCHAR2,
1877 x_cancel_flag IN VARCHAR2 ,
1878 x_occurrence_identifier IN VARCHAR2,
1879 x_abort_flag IN VARCHAR2
1880
1881 ) AS
1882 /*************************************************************
1883 Created By : venagara
1884 Date Created By : 2000/05/12
1885 Purpose :
1886 Know limitations, enhancements or remarks
1887 Change History
1888 Who When What
1889 smvk 02-Sep-2002 Removed the Default value in the parameters to overcome File.Pkg.22 gscc warnings.
1890 As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
1891 rgangara 09-May-2001 Added x_notify_status, x_inst_notify_ind args
1892 Sreenivas.Bonam 28-Aug-2000 Added x_error_text argument
1893 (reverse chronological order - newest change first)
1894 ***************************************************************/
1895
1896 cursor c1 is select ROWID from IGS_PS_USEC_OCCURS_ALL
1897 where UNIT_SECTION_OCCURRENCE_ID= X_UNIT_SECTION_OCCURRENCE_ID
1898 ;
1899 begin
1900 open c1;
1901 fetch c1 into X_ROWID;
1902 if (c1%notfound) then
1903 close c1;
1904 INSERT_ROW (
1905 X_ROWID,
1906 X_UNIT_SECTION_OCCURRENCE_ID,
1907 X_UOO_ID,
1908 X_MONDAY,
1909 X_TUESDAY,
1910 X_WEDNESDAY,
1911 X_THURSDAY,
1912 X_FRIDAY,
1913 X_SATURDAY,
1914 X_SUNDAY,
1915 X_START_TIME,
1916 X_END_TIME,
1917 X_BUILDING_CODE,
1918 X_ROOM_CODE,
1919 X_SCHEDULE_STATUS,
1920 X_STATUS_LAST_UPDATED,
1921 X_INSTRUCTOR_ID,
1922 X_ATTRIBUTE_CATEGORY,
1923 X_ATTRIBUTE1,
1924 X_ATTRIBUTE2,
1925 X_ATTRIBUTE3,
1926 X_ATTRIBUTE4,
1927 X_ATTRIBUTE5,
1928 X_ATTRIBUTE6,
1929 X_ATTRIBUTE7,
1930 X_ATTRIBUTE8,
1931 X_ATTRIBUTE9,
1932 X_ATTRIBUTE10,
1933 X_ATTRIBUTE11,
1934 X_ATTRIBUTE12,
1935 X_ATTRIBUTE13,
1936 X_ATTRIBUTE14,
1937 X_ATTRIBUTE15,
1938 X_ATTRIBUTE16,
1939 X_ATTRIBUTE17,
1940 X_ATTRIBUTE18,
1941 X_ATTRIBUTE19,
1942 X_ATTRIBUTE20,
1943 X_ERROR_TEXT,
1944 X_MODE,
1945 X_ORG_ID,
1946 X_START_DATE,
1947 X_END_DATE,
1948 X_TO_BE_ANNOUNCED,
1949 X_INST_NOTIFY_IND,
1950 X_NOTIFY_STATUS,
1951 x_dedicated_building_code,
1952 x_dedicated_room_code,
1953 x_preferred_building_code,
1954 x_preferred_room_code ,
1955 x_preferred_region_code,
1956 x_no_set_day_ind,
1957 x_cancel_flag,
1958 x_occurrence_identifier ,
1959 x_abort_flag
1960
1961 );
1962 return;
1963 end if;
1964 close c1;
1965 UPDATE_ROW (
1966 X_ROWID,
1967 X_UNIT_SECTION_OCCURRENCE_ID,
1968 X_UOO_ID,
1969 X_MONDAY,
1970 X_TUESDAY,
1971 X_WEDNESDAY,
1972 X_THURSDAY,
1973 X_FRIDAY,
1974 X_SATURDAY,
1975 X_SUNDAY,
1976 X_START_TIME,
1977 X_END_TIME,
1978 X_BUILDING_CODE,
1979 X_ROOM_CODE,
1980 X_SCHEDULE_STATUS,
1981 X_STATUS_LAST_UPDATED,
1982 X_INSTRUCTOR_ID,
1983 X_ATTRIBUTE_CATEGORY,
1984 X_ATTRIBUTE1,
1985 X_ATTRIBUTE2,
1986 X_ATTRIBUTE3,
1987 X_ATTRIBUTE4,
1988 X_ATTRIBUTE5,
1989 X_ATTRIBUTE6,
1990 X_ATTRIBUTE7,
1991 X_ATTRIBUTE8,
1992 X_ATTRIBUTE9,
1993 X_ATTRIBUTE10,
1994 X_ATTRIBUTE11,
1995 X_ATTRIBUTE12,
1996 X_ATTRIBUTE13,
1997 X_ATTRIBUTE14,
1998 X_ATTRIBUTE15,
1999 X_ATTRIBUTE16,
2000 X_ATTRIBUTE17,
2001 X_ATTRIBUTE18,
2002 X_ATTRIBUTE19,
2003 X_ATTRIBUTE20,
2004 X_ERROR_TEXT,
2005 X_MODE,
2006 X_START_DATE,
2007 X_END_DATE,
2008 X_TO_BE_ANNOUNCED,
2009 X_INST_NOTIFY_IND,
2010 X_NOTIFY_STATUS,
2011 x_dedicated_building_code,
2012 x_dedicated_room_code,
2013 x_preferred_building_code,
2014 x_preferred_room_code,
2015 x_preferred_region_code,
2016 x_no_set_day_ind,
2017 x_cancel_flag,
2018 x_occurrence_identifier ,
2019 x_abort_flag
2020 );
2021 end ADD_ROW;
2022 procedure DELETE_ROW (
2023 X_ROWID in VARCHAR2
2024 ) AS
2025 /*************************************************************
2026 Created By : venagara
2027 Date Created By : 2000/05/12
2028 Purpose :
2029 Know limitations, enhancements or remarks
2030 Change History
2031 Who When What
2032
2033 (reverse chronological order - newest change first)
2034 ***************************************************************/
2035
2036 begin
2037 Before_DML (
2038 p_action => 'DELETE',
2039 x_rowid => X_ROWID
2040 );
2041
2042 BeforeRowInsertUpdatedelete1(X_ROWID);
2043 delete from IGS_PS_USEC_OCCURS_ALL
2044 where ROWID = X_ROWID;
2045 if (sql%notfound) then
2046 raise no_data_found;
2047 end if;
2048 After_DML (
2049 p_action => 'DELETE',
2050 x_rowid => X_ROWID
2051 );
2052 end DELETE_ROW;
2053 END igs_ps_usec_occurs_pkg;