[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_USEC_REF_PKG
Source
1 PACKAGE BODY igs_ps_usec_ref_pkg AS
2 /* $Header: IGSPI1GB.pls 115.11 2003/01/10 11:23:46 smvk ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ps_usec_ref%RowType;
5 new_references igs_ps_usec_ref%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_unit_section_reference_id IN NUMBER DEFAULT NULL,
11 x_uoo_id IN NUMBER DEFAULT NULL,
12 x_short_title IN VARCHAR2 DEFAULT NULL,
13 x_subtitle IN VARCHAR2 DEFAULT NULL,
14 x_subtitle_modifiable_flag IN VARCHAR2 DEFAULT NULL,
15 x_class_sched_exclusion_flag IN VARCHAR2 DEFAULT NULL,
16 x_registration_exclusion_flag IN VARCHAR2 DEFAULT NULL,
17 x_attribute_category IN VARCHAR2 DEFAULT NULL,
18 x_attribute1 IN VARCHAR2 DEFAULT NULL,
19 x_attribute2 IN VARCHAR2 DEFAULT NULL,
20 x_attribute3 IN VARCHAR2 DEFAULT NULL,
21 x_attribute4 IN VARCHAR2 DEFAULT NULL,
22 x_attribute5 IN VARCHAR2 DEFAULT NULL,
23 x_attribute6 IN VARCHAR2 DEFAULT NULL,
24 x_attribute7 IN VARCHAR2 DEFAULT NULL,
25 x_attribute8 IN VARCHAR2 DEFAULT NULL,
26 x_attribute9 IN VARCHAR2 DEFAULT NULL,
27 x_attribute10 IN VARCHAR2 DEFAULT NULL,
28 x_attribute11 IN VARCHAR2 DEFAULT NULL,
29 x_attribute12 IN VARCHAR2 DEFAULT NULL,
30 x_attribute13 IN VARCHAR2 DEFAULT NULL,
31 x_attribute14 IN VARCHAR2 DEFAULT NULL,
32 x_attribute15 IN VARCHAR2 DEFAULT NULL,
33 x_attribute16 IN VARCHAR2 DEFAULT NULL,
34 x_attribute17 IN VARCHAR2 DEFAULT NULL,
35 x_attribute18 IN VARCHAR2 DEFAULT NULL,
36 x_attribute19 IN VARCHAR2 DEFAULT NULL,
37 x_attribute20 IN VARCHAR2 DEFAULT NULL,
38 x_RECORD_EXCLUSION_FLAG IN VARCHAR2 DEFAULT NULL,
39 x_TITLE IN VARCHAR2 DEFAULT NULL,
40 x_SUBTITLE_ID IN NUMBER DEFAULT NULL,
41 x_creation_date IN DATE DEFAULT NULL,
42 x_created_by IN NUMBER DEFAULT NULL,
43 x_last_update_date IN DATE DEFAULT NULL,
44 x_last_updated_by IN NUMBER DEFAULT NULL,
45 x_last_update_login IN NUMBER DEFAULT NULL
46 ) AS
47
48 /*************************************************************
49 Created By :
50 Date Created By :
51 Purpose :
52 Know limitations, enhancements or remarks
53 Change History
54 Who When What
55
56 (reverse chronological order - newest change first)
57 ***************************************************************/
58
59 CURSOR cur_old_ref_values IS
60 SELECT *
61 FROM IGS_PS_USEC_REF
62 WHERE rowid = x_rowid;
63
64 BEGIN
65
66 l_rowid := x_rowid;
67
68 -- Code for setting the Old and New Reference Values.
69 -- Populate Old Values.
70 Open cur_old_ref_values;
71 Fetch cur_old_ref_values INTO old_references;
72 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
73 Close cur_old_ref_values;
74 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
75 IGS_GE_MSG_STACK.ADD;
76 App_Exception.Raise_Exception;
77 Return;
78 END IF;
79 Close cur_old_ref_values;
80
81 -- Populate New Values.
82 new_references.unit_section_reference_id := x_unit_section_reference_id;
83 new_references.uoo_id := x_uoo_id;
84 new_references.short_title := x_short_title;
85 new_references.subtitle := NULL;
86 new_references.subtitle_modifiable_flag := x_subtitle_modifiable_flag;
87 new_references.class_schedule_exclusion_flag := x_class_sched_exclusion_flag;
88 new_references.registration_exclusion_flag := NULL;
89 new_references.attribute_category := x_attribute_category;
90 new_references.attribute1 := x_attribute1;
91 new_references.attribute2 := x_attribute2;
92 new_references.attribute3 := x_attribute3;
93 new_references.attribute4 := x_attribute4;
94 new_references.attribute5 := x_attribute5;
95 new_references.attribute6 := x_attribute6;
96 new_references.attribute7 := x_attribute7;
97 new_references.attribute8 := x_attribute8;
98 new_references.attribute9 := x_attribute9;
99 new_references.attribute10 := x_attribute10;
100 new_references.attribute11 := x_attribute11;
101 new_references.attribute12 := x_attribute12;
102 new_references.attribute13 := x_attribute13;
103 new_references.attribute14 := x_attribute14;
104 new_references.attribute15 := x_attribute15;
105 new_references.attribute16 := x_attribute16;
106 new_references.attribute17 := x_attribute17;
107 new_references.attribute18 := x_attribute18;
108 new_references.attribute19 := x_attribute19;
109 new_references.attribute20 := x_attribute20;
110 new_references.record_exclusion_flag := x_record_exclusion_flag;
111 new_references.title := x_title;
112 new_references.subtitle_id := x_subtitle_id;
113 IF (p_action = 'UPDATE') THEN
114 new_references.creation_date := old_references.creation_date;
115 new_references.created_by := old_references.created_by;
116 ELSE
117 new_references.creation_date := x_creation_date;
118 new_references.created_by := x_created_by;
119 END IF;
120 new_references.last_update_date := x_last_update_date;
121 new_references.last_updated_by := x_last_updated_by;
122 new_references.last_update_login := x_last_update_login;
123
124 END Set_Column_Values;
125
126 PROCEDURE Check_Constraints (
127 Column_Name IN VARCHAR2 DEFAULT NULL,
128 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
129 /*************************************************************
130 Created By :
131 Date Created By :
132 Purpose :
133 Know limitations, enhancements or remarks
134 Change History
135 Who When What
136
137 (reverse chronological order - newest change first)
138 ***************************************************************/
139
140 BEGIN
141
142 IF Column_Name IS NULL Then
143 NULL;
144 ELSIF Upper(Column_Name)='SUBTITLE_MODIFIABLE_FLAG' Then
145 New_References.subtitle_modifiable_flag := Column_Value;
146 ELSIF Upper(Column_Name)='CLASS_SCHEDULE_EXCLUSION_FLAG' Then
147 New_References.class_schedule_exclusion_flag := Column_Value;
148 ELSIF Upper(Column_Name)='RECORD_EXCLUSION_FLAG' Then
149 New_References.record_exclusion_flag := Column_Value;
150 END IF;
151
152 IF Upper(Column_Name)='SUBTITLE_MODIFIABLE_FLAG' OR Column_Name IS NULL Then
153 IF New_References.subtitle_modifiable_flag NOT IN ( 'Y' , 'N' ) Then
154 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
155 IGS_GE_MSG_STACK.ADD;
156 App_Exception.Raise_Exception;
157 END IF;
158 END IF;
159
160 IF Upper(Column_Name)='CLASS_SCHEDULE_EXCLUSION_FLAG' OR Column_Name IS NULL Then
161 IF New_References.class_schedule_exclusion_flag NOT IN ( 'Y' , 'N' ) Then
162 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
163 IGS_GE_MSG_STACK.ADD;
164 App_Exception.Raise_Exception;
165 END IF;
166 END IF;
167
168 IF Upper(Column_Name)='RECORD_EXCLUSION_FLAG' OR Column_Name IS NULL Then
169 IF New_References.record_exclusion_flag NOT IN ( 'Y' , 'N' ) Then
170 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
171 IGS_GE_MSG_STACK.ADD;
172 App_Exception.Raise_Exception;
173 END IF;
174 END IF;
175
176 END Check_Constraints;
177
178 PROCEDURE Check_Uniqueness AS
179 /*************************************************************
180 Created By :
181 Date Created By :
182 Purpose :
183 Know limitations, enhancements or remarks
184 Change History
185 Who When What
186
187 (reverse chronological order - newest change first)
188 ***************************************************************/
189
190 begin
191 IF Get_Uk_For_Validation (
192 new_references.uoo_id
193 ) THEN
194 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
195 IGS_GE_MSG_STACK.ADD;
196 app_exception.raise_exception;
197 END IF;
198 END Check_Uniqueness ;
199 PROCEDURE Check_Parent_Existance AS
200 /*************************************************************
201 Created By :
202 Date Created By :
203 Purpose :
204 Know limitations, enhancements or remarks
205 Change History
206 Who When What
207
208 (reverse chronological order - newest change first)
209 ***************************************************************/
210
211 BEGIN
212
213 IF (((old_references.uoo_id = new_references.uoo_id)) OR
214 ((new_references.uoo_id IS NULL))) THEN
215 NULL;
216 ELSIF NOT Igs_Ps_Unit_Ofr_Opt_Pkg.Get_UK_For_Validation (
217 new_references.uoo_id
218 ) THEN
219 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
220 IGS_GE_MSG_STACK.ADD;
221 App_Exception.Raise_Exception;
222 END IF;
223
224 END Check_Parent_Existance;
225
226 PROCEDURE Check_Child_Existance IS
227 /*************************************************************
228 Created By :
229 Date Created By :
230 Purpose :
231 Know limitations, enhancements or remarks
232 Change History
233 Who When What
234
235 (reverse chronological order - newest change first)
236 ***************************************************************/
237
238 BEGIN
239
240 Igs_Ps_Usec_Ref_Cd_Pkg.Get_FK_Igs_Ps_Usec_Ref (
241 old_references.unit_section_reference_id
242 );
243
244 igs_ps_us_req_ref_cd_pkg.Get_FK_Igs_Ps_Usec_Ref (
245 old_references.unit_section_reference_id
246 );
247
248 END Check_Child_Existance;
249
250 FUNCTION Get_PK_For_Validation (
251 x_unit_section_reference_id IN NUMBER
252 ) RETURN BOOLEAN AS
253
254 /*************************************************************
255 Created By :
256 Date Created By :
257 Purpose :
258 Know limitations, enhancements or remarks
259 Change History
260 Who When What
261
262 (reverse chronological order - newest change first)
263 ***************************************************************/
264
265 CURSOR cur_rowid IS
266 SELECT rowid
267 FROM igs_ps_usec_ref
268 WHERE unit_section_reference_id = x_unit_section_reference_id
269 FOR UPDATE NOWAIT;
270
271 lv_rowid cur_rowid%RowType;
272
273 BEGIN
274
275 Open cur_rowid;
276 Fetch cur_rowid INTO lv_rowid;
277 IF (cur_rowid%FOUND) THEN
278 Close cur_rowid;
279 Return(TRUE);
280 ELSE
281 Close cur_rowid;
282 Return(FALSE);
283 END IF;
284 END Get_PK_For_Validation;
285
286 FUNCTION Get_UK_For_Validation (
287 x_uoo_id IN NUMBER
288 ) RETURN BOOLEAN AS
289
290 /*************************************************************
291 Created By :
292 Date Created By :
293 Purpose :
294 Know limitations, enhancements or remarks
295 Change History
296 Who When What
297
298 (reverse chronological order - newest change first)
299 ***************************************************************/
300
301 CURSOR cur_rowid IS
302 SELECT rowid
303 FROM igs_ps_usec_ref
304 WHERE uoo_id = x_uoo_id and ((l_rowid is null) or (rowid <> l_rowid))
305
306 ;
307 lv_rowid cur_rowid%RowType;
308
309 BEGIN
310
311 Open cur_rowid;
312 Fetch cur_rowid INTO lv_rowid;
313 IF (cur_rowid%FOUND) THEN
314 Close cur_rowid;
315 return (true);
316 ELSE
317 close cur_rowid;
318 return(false);
319 END IF;
320 END Get_UK_For_Validation ;
321 PROCEDURE Get_UFK_Igs_Ps_Unit_Ofr_Opt (
322 x_uoo_id IN NUMBER
323 ) AS
324
325 /*************************************************************
326 Created By :
327 Date Created By :
328 Purpose :
329 Know limitations, enhancements or remarks
330 Change History
331 Who When What
332
333 (reverse chronological order - newest change first)
334 ***************************************************************/
335
336 CURSOR cur_rowid IS
337 SELECT rowid
338 FROM igs_ps_usec_ref
339 WHERE uoo_id = x_uoo_id ;
340
341 lv_rowid cur_rowid%RowType;
342
343 BEGIN
344
345 Open cur_rowid;
346 Fetch cur_rowid INTO lv_rowid;
347 IF (cur_rowid%FOUND) THEN
348 Close cur_rowid;
349 Fnd_Message.Set_Name ('IGS', 'IGS_PS_USREF_UOO_UFK');
350 IGS_GE_MSG_STACK.ADD;
351 App_Exception.Raise_Exception;
352 Return;
353 END IF;
354 Close cur_rowid;
355
356 END Get_UFK_Igs_Ps_Unit_Ofr_Opt;
357
358 PROCEDURE Before_DML (
359 p_action IN VARCHAR2,
360 x_rowid IN VARCHAR2 DEFAULT NULL,
361 x_unit_section_reference_id IN NUMBER DEFAULT NULL,
362 x_uoo_id IN NUMBER DEFAULT NULL,
363 x_short_title IN VARCHAR2 DEFAULT NULL,
364 x_subtitle IN VARCHAR2 DEFAULT NULL,
365 x_subtitle_modifiable_flag IN VARCHAR2 DEFAULT NULL,
366 x_class_sched_exclusion_flag IN VARCHAR2 DEFAULT NULL,
367 x_registration_exclusion_flag IN VARCHAR2 DEFAULT NULL,
368 x_attribute_category IN VARCHAR2 DEFAULT NULL,
369 x_attribute1 IN VARCHAR2 DEFAULT NULL,
370 x_attribute2 IN VARCHAR2 DEFAULT NULL,
371 x_attribute3 IN VARCHAR2 DEFAULT NULL,
372 x_attribute4 IN VARCHAR2 DEFAULT NULL,
373 x_attribute5 IN VARCHAR2 DEFAULT NULL,
374 x_attribute6 IN VARCHAR2 DEFAULT NULL,
375 x_attribute7 IN VARCHAR2 DEFAULT NULL,
376 x_attribute8 IN VARCHAR2 DEFAULT NULL,
377 x_attribute9 IN VARCHAR2 DEFAULT NULL,
378 x_attribute10 IN VARCHAR2 DEFAULT NULL,
379 x_attribute11 IN VARCHAR2 DEFAULT NULL,
380 x_attribute12 IN VARCHAR2 DEFAULT NULL,
381 x_attribute13 IN VARCHAR2 DEFAULT NULL,
382 x_attribute14 IN VARCHAR2 DEFAULT NULL,
383 x_attribute15 IN VARCHAR2 DEFAULT NULL,
384 x_attribute16 IN VARCHAR2 DEFAULT NULL,
385 x_attribute17 IN VARCHAR2 DEFAULT NULL,
386 x_attribute18 IN VARCHAR2 DEFAULT NULL,
387 x_attribute19 IN VARCHAR2 DEFAULT NULL,
388 x_attribute20 IN VARCHAR2 DEFAULT NULL,
389 x_RECORD_EXCLUSION_FLAG IN VARCHAR2 DEFAULT NULL,
390 x_TITLE IN VARCHAR2 DEFAULT NULL,
391 x_SUBTITLE_ID IN NUMBER DEFAULT NULL,
392 x_creation_date IN DATE DEFAULT NULL,
393 x_created_by IN NUMBER DEFAULT NULL,
394 x_last_update_date IN DATE DEFAULT NULL,
395 x_last_updated_by IN NUMBER DEFAULT NULL,
396 x_last_update_login IN NUMBER DEFAULT NULL
397 ) AS
398 /*************************************************************
399 Created By :
400 Date Created By :
401 Purpose :
402 Know limitations, enhancements or remarks
403 Change History
404 Who When What
405
406 (reverse chronological order - newest change first)
407 ***************************************************************/
408
409 BEGIN
410
411 Set_Column_Values (
412 p_action,
413 x_rowid,
414 x_unit_section_reference_id,
415 x_uoo_id,
416 x_short_title,
417 x_subtitle,
418 x_subtitle_modifiable_flag,
419 x_class_sched_exclusion_flag,
420 x_registration_exclusion_flag,
421 x_attribute_category,
422 x_attribute1,
423 x_attribute2,
424 x_attribute3,
425 x_attribute4,
426 x_attribute5,
427 x_attribute6,
428 x_attribute7,
429 x_attribute8,
430 x_attribute9,
431 x_attribute10,
432 x_attribute11,
433 x_attribute12,
434 x_attribute13,
435 x_attribute14,
436 x_attribute15,
437 x_attribute16,
438 x_attribute17,
439 x_attribute18,
440 x_attribute19,
441 x_attribute20,
442 x_record_exclusion_flag,
443 x_title,
444 x_subtitle_id,
445 x_creation_date,
446 x_created_by,
447 x_last_update_date,
448 x_last_updated_by,
449 x_last_update_login
450 );
451
452 IF (p_action = 'INSERT') THEN
453 -- Call all the procedures related to Before Insert.
454 Null;
455 IF Get_Pk_For_Validation(
456 new_references.unit_section_reference_id) THEN
457 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
458 IGS_GE_MSG_STACK.ADD;
459 App_Exception.Raise_Exception;
460 END IF;
461 Check_Uniqueness;
462 Check_Constraints;
463 Check_Parent_Existance;
464 ELSIF (p_action = 'UPDATE') THEN
465 -- Call all the procedures related to Before Update.
466 Null;
467 Check_Uniqueness;
468 Check_Constraints;
469 Check_Parent_Existance;
470 ELSIF (p_action = 'DELETE') THEN
471 -- Call all the procedures related to Before Delete.
472 Null;
473 Check_Child_Existance;
474 ELSIF (p_action = 'VALIDATE_INSERT') THEN
475 -- Call all the procedures related to Before Insert.
476 IF Get_PK_For_Validation (
477 new_references.unit_section_reference_id) THEN
478 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
479 IGS_GE_MSG_STACK.ADD;
480 App_Exception.Raise_Exception;
481 END IF;
482 Check_Uniqueness;
483 Check_Constraints;
484 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
485 Check_Uniqueness;
486 Check_Constraints;
487 ELSIF (p_action = 'VALIDATE_DELETE') THEN
488 Check_Child_Existance;
489 END IF;
490
491 END Before_DML;
492
493 PROCEDURE After_DML (
494 p_action IN VARCHAR2,
495 x_rowid IN VARCHAR2
496 ) IS
497 /*************************************************************
498 Created By :
499 Date Created By :
500 Purpose :
501 Know limitations, enhancements or remarks
502 Change History
503 Who When What
504
505 (reverse chronological order - newest change first)
506 ***************************************************************/
507
508 BEGIN
509
510 l_rowid := x_rowid;
511
512 IF (p_action = 'INSERT') THEN
513 -- Call all the procedures related to After Insert.
514 Null;
515 ELSIF (p_action = 'UPDATE') THEN
516 -- Call all the procedures related to After Update.
517 Null;
518 ELSIF (p_action = 'DELETE') THEN
519 -- Call all the procedures related to After Delete.
520 Null;
521 END IF;
522
523 l_rowid := null;
524 END After_DML;
525
526 procedure INSERT_ROW (
527 X_ROWID in out NOCOPY VARCHAR2,
528 x_UNIT_SECTION_REFERENCE_ID IN OUT NOCOPY NUMBER,
529 x_UOO_ID IN NUMBER,
530 x_SHORT_TITLE IN VARCHAR2,
531 x_SUBTITLE IN VARCHAR2,
532 x_SUBTITLE_MODIFIABLE_FLAG IN VARCHAR2,
533 x_class_sched_exclusion_flag IN VARCHAR2,
534 x_REGISTRATION_EXCLUSION_FLAG IN VARCHAR2,
535 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
536 x_ATTRIBUTE1 IN VARCHAR2,
537 x_ATTRIBUTE2 IN VARCHAR2,
538 x_ATTRIBUTE3 IN VARCHAR2,
539 x_ATTRIBUTE4 IN VARCHAR2,
540 x_ATTRIBUTE5 IN VARCHAR2,
541 x_ATTRIBUTE6 IN VARCHAR2,
542 x_ATTRIBUTE7 IN VARCHAR2,
543 x_ATTRIBUTE8 IN VARCHAR2,
544 x_ATTRIBUTE9 IN VARCHAR2,
545 x_ATTRIBUTE10 IN VARCHAR2,
546 x_ATTRIBUTE11 IN VARCHAR2,
547 x_ATTRIBUTE12 IN VARCHAR2,
548 x_ATTRIBUTE13 IN VARCHAR2,
549 x_ATTRIBUTE14 IN VARCHAR2,
550 x_ATTRIBUTE15 IN VARCHAR2,
551 x_ATTRIBUTE16 IN VARCHAR2,
552 x_ATTRIBUTE17 IN VARCHAR2,
553 x_ATTRIBUTE18 IN VARCHAR2,
554 x_ATTRIBUTE19 IN VARCHAR2,
555 x_ATTRIBUTE20 IN VARCHAR2,
556 x_RECORD_EXCLUSION_FLAG IN VARCHAR2 DEFAULT NULL,
557 x_TITLE IN VARCHAR2 DEFAULT NULL,
558 x_SUBTITLE_ID IN NUMBER DEFAULT NULL,
559 X_MODE in VARCHAR2 default 'R'
560 ) AS
561 /*************************************************************
562 Created By :
563 Date Created By :
564 Purpose :
565 Know limitations, enhancements or remarks
566 Change History
567 Who When What
568
569 (reverse chronological order - newest change first)
570 ***************************************************************/
571
572 cursor C is select ROWID from IGS_PS_USEC_REF
573 where UNIT_SECTION_REFERENCE_ID= X_UNIT_SECTION_REFERENCE_ID
574 ;
575 X_LAST_UPDATE_DATE DATE ;
576 X_LAST_UPDATED_BY NUMBER ;
577 X_LAST_UPDATE_LOGIN NUMBER ;
578 begin
579 X_LAST_UPDATE_DATE := SYSDATE;
580 if(X_MODE = 'I') then
581 X_LAST_UPDATED_BY := 1;
582 X_LAST_UPDATE_LOGIN := 0;
583 elsif (X_MODE = 'R') then
584 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
585 if X_LAST_UPDATED_BY is NULL then
586 X_LAST_UPDATED_BY := -1;
587 end if;
588 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
589 if X_LAST_UPDATE_LOGIN is NULL then
590 X_LAST_UPDATE_LOGIN := -1;
591 end if;
592 else
593 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
594 IGS_GE_MSG_STACK.ADD;
595 app_exception.raise_exception;
596 end if;
597 SELECT IGS_PS_USEC_REF_S.nextval
598 INTO x_unit_section_reference_id
599 FROM dual;
600 Before_DML(
601 p_action=>'INSERT',
602 x_rowid=>X_ROWID,
603 x_unit_section_reference_id=>X_UNIT_SECTION_REFERENCE_ID,
604 x_uoo_id=>X_UOO_ID,
605 x_short_title=>X_SHORT_TITLE,
606 x_subtitle=>X_SUBTITLE,
607 x_subtitle_modifiable_flag=>X_SUBTITLE_MODIFIABLE_FLAG,
608 x_class_sched_exclusion_flag=>x_class_sched_exclusion_flag,
609 x_registration_exclusion_flag=>X_REGISTRATION_EXCLUSION_FLAG,
610 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
611 x_attribute1=>X_ATTRIBUTE1,
612 x_attribute2=>X_ATTRIBUTE2,
613 x_attribute3=>X_ATTRIBUTE3,
614 x_attribute4=>X_ATTRIBUTE4,
615 x_attribute5=>X_ATTRIBUTE5,
616 x_attribute6=>X_ATTRIBUTE6,
617 x_attribute7=>X_ATTRIBUTE7,
618 x_attribute8=>X_ATTRIBUTE8,
619 x_attribute9=>X_ATTRIBUTE9,
620 x_attribute10=>X_ATTRIBUTE10,
621 x_attribute11=>X_ATTRIBUTE11,
622 x_attribute12=>X_ATTRIBUTE12,
623 x_attribute13=>X_ATTRIBUTE13,
624 x_attribute14=>X_ATTRIBUTE14,
625 x_attribute15=>X_ATTRIBUTE15,
626 x_attribute16=>X_ATTRIBUTE16,
627 x_attribute17=>X_ATTRIBUTE17,
628 x_attribute18=>X_ATTRIBUTE18,
629 x_attribute19=>X_ATTRIBUTE19,
630 x_attribute20=>X_ATTRIBUTE20,
631 x_record_exclusion_flag => X_RECORD_EXCLUSION_FLAG,
632 x_title => X_TITLE,
633 x_subtitle_id => X_SUBTITLE_ID,
634 x_creation_date=>X_LAST_UPDATE_DATE,
635 x_created_by=>X_LAST_UPDATED_BY,
636 x_last_update_date=>X_LAST_UPDATE_DATE,
637 x_last_updated_by=>X_LAST_UPDATED_BY,
638 x_last_update_login=>X_LAST_UPDATE_LOGIN);
639 insert into IGS_PS_USEC_REF (
640 UNIT_SECTION_REFERENCE_ID
641 ,UOO_ID
642 ,SHORT_TITLE
643 ,SUBTITLE_MODIFIABLE_FLAG
644 ,CLASS_SCHEDULE_EXCLUSION_FLAG
645 ,ATTRIBUTE_CATEGORY
646 ,ATTRIBUTE1
647 ,ATTRIBUTE2
648 ,ATTRIBUTE3
649 ,ATTRIBUTE4
650 ,ATTRIBUTE5
651 ,ATTRIBUTE6
652 ,ATTRIBUTE7
653 ,ATTRIBUTE8
654 ,ATTRIBUTE9
655 ,ATTRIBUTE10
656 ,ATTRIBUTE11
657 ,ATTRIBUTE12
658 ,ATTRIBUTE13
659 ,ATTRIBUTE14
660 ,ATTRIBUTE15
661 ,ATTRIBUTE16
662 ,ATTRIBUTE17
663 ,ATTRIBUTE18
664 ,ATTRIBUTE19
665 ,ATTRIBUTE20
666 ,RECORD_EXCLUSION_FLAG
667 ,TITLE
668 ,SUBTITLE_ID
669 ,CREATION_DATE
670 ,CREATED_BY
671 ,LAST_UPDATE_DATE
672 ,LAST_UPDATED_BY
673 ,LAST_UPDATE_LOGIN
674 ) values (
675 NEW_REFERENCES.UNIT_SECTION_REFERENCE_ID
676 ,NEW_REFERENCES.UOO_ID
677 ,NEW_REFERENCES.SHORT_TITLE
678 ,NEW_REFERENCES.SUBTITLE_MODIFIABLE_FLAG
679 ,NEW_REFERENCES.CLASS_SCHEDULE_EXCLUSION_FLAG
680 ,NEW_REFERENCES.ATTRIBUTE_CATEGORY
681 ,NEW_REFERENCES.ATTRIBUTE1
682 ,NEW_REFERENCES.ATTRIBUTE2
683 ,NEW_REFERENCES.ATTRIBUTE3
684 ,NEW_REFERENCES.ATTRIBUTE4
685 ,NEW_REFERENCES.ATTRIBUTE5
686 ,NEW_REFERENCES.ATTRIBUTE6
687 ,NEW_REFERENCES.ATTRIBUTE7
688 ,NEW_REFERENCES.ATTRIBUTE8
689 ,NEW_REFERENCES.ATTRIBUTE9
690 ,NEW_REFERENCES.ATTRIBUTE10
691 ,NEW_REFERENCES.ATTRIBUTE11
692 ,NEW_REFERENCES.ATTRIBUTE12
693 ,NEW_REFERENCES.ATTRIBUTE13
694 ,NEW_REFERENCES.ATTRIBUTE14
695 ,NEW_REFERENCES.ATTRIBUTE15
696 ,NEW_REFERENCES.ATTRIBUTE16
697 ,NEW_REFERENCES.ATTRIBUTE17
698 ,NEW_REFERENCES.ATTRIBUTE18
699 ,NEW_REFERENCES.ATTRIBUTE19
700 ,NEW_REFERENCES.ATTRIBUTE20
701 ,NEW_REFERENCES.RECORD_EXCLUSION_FLAG
702 ,NEW_REFERENCES.TITLE
703 ,NEW_REFERENCES.SUBTITLE_ID
704 ,X_LAST_UPDATE_DATE
705 ,X_LAST_UPDATED_BY
706 ,X_LAST_UPDATE_DATE
707 ,X_LAST_UPDATED_BY
708 ,X_LAST_UPDATE_LOGIN
709 );
710 open c;
711 fetch c into X_ROWID;
712 if (c%notfound) then
713 close c;
714 raise no_data_found;
715 end if;
716 close c;
717 After_DML (
718 p_action => 'INSERT' ,
719 x_rowid => X_ROWID );
720 end INSERT_ROW;
721 procedure LOCK_ROW (
722 X_ROWID in VARCHAR2,
723 x_UNIT_SECTION_REFERENCE_ID IN NUMBER,
724 x_UOO_ID IN NUMBER,
725 x_SHORT_TITLE IN VARCHAR2,
726 x_SUBTITLE IN VARCHAR2,
727 x_SUBTITLE_MODIFIABLE_FLAG IN VARCHAR2,
728 x_class_sched_exclusion_flag IN VARCHAR2,
729 x_REGISTRATION_EXCLUSION_FLAG IN VARCHAR2,
730 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
731 x_ATTRIBUTE1 IN VARCHAR2,
732 x_ATTRIBUTE2 IN VARCHAR2,
733 x_ATTRIBUTE3 IN VARCHAR2,
734 x_ATTRIBUTE4 IN VARCHAR2,
735 x_ATTRIBUTE5 IN VARCHAR2,
736 x_ATTRIBUTE6 IN VARCHAR2,
737 x_ATTRIBUTE7 IN VARCHAR2,
738 x_ATTRIBUTE8 IN VARCHAR2,
739 x_ATTRIBUTE9 IN VARCHAR2,
740 x_ATTRIBUTE10 IN VARCHAR2,
741 x_ATTRIBUTE11 IN VARCHAR2,
742 x_ATTRIBUTE12 IN VARCHAR2,
743 x_ATTRIBUTE13 IN VARCHAR2,
744 x_ATTRIBUTE14 IN VARCHAR2,
745 x_ATTRIBUTE15 IN VARCHAR2,
746 x_ATTRIBUTE16 IN VARCHAR2,
747 x_ATTRIBUTE17 IN VARCHAR2,
748 x_ATTRIBUTE18 IN VARCHAR2,
749 x_ATTRIBUTE19 IN VARCHAR2,
750 x_ATTRIBUTE20 IN VARCHAR2,
751 x_RECORD_EXCLUSION_FLAG IN VARCHAR2 DEFAULT NULL,
752 x_TITLE IN VARCHAR2 DEFAULT NULL,
753 x_SUBTITLE_ID IN NUMBER DEFAULT NULL ) AS
754 /*************************************************************
755 Created By :
756 Date Created By :
757 Purpose :
758 Know limitations, enhancements or remarks
759 Change History
760 Who When What
761
762 (reverse chronological order - newest change first)
763 ***************************************************************/
764
765 cursor c1 is select
766 UOO_ID
767 , SHORT_TITLE
768 , SUBTITLE_MODIFIABLE_FLAG
769 , CLASS_SCHEDULE_EXCLUSION_FLAG
770 , ATTRIBUTE_CATEGORY
771 , ATTRIBUTE1
772 , ATTRIBUTE2
773 , ATTRIBUTE3
774 , ATTRIBUTE4
775 , ATTRIBUTE5
776 , ATTRIBUTE6
777 , ATTRIBUTE7
778 , ATTRIBUTE8
779 , ATTRIBUTE9
780 , ATTRIBUTE10
781 , ATTRIBUTE11
782 , ATTRIBUTE12
783 , ATTRIBUTE13
784 , ATTRIBUTE14
785 , ATTRIBUTE15
786 , ATTRIBUTE16
787 , ATTRIBUTE17
788 , ATTRIBUTE18
789 , ATTRIBUTE19
790 , ATTRIBUTE20
791 , RECORD_EXCLUSION_FLAG
792 , TITLE
793 , SUBTITLE_ID
794 from IGS_PS_USEC_REF
795 where ROWID = X_ROWID
796 for update nowait;
797 tlinfo c1%rowtype;
798 begin
799 open c1;
800 fetch c1 into tlinfo;
801 if (c1%notfound) then
802 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
803 IGS_GE_MSG_STACK.ADD;
804 close c1;
805 app_exception.raise_exception;
806 return;
807 end if;
808 close c1;
809 if ( ( tlinfo.UOO_ID = X_UOO_ID)
810 AND ((tlinfo.SHORT_TITLE = X_SHORT_TITLE)
811 OR ((tlinfo.SHORT_TITLE is null)
812 AND (X_SHORT_TITLE is null)))
813 AND ((tlinfo.SUBTITLE_MODIFIABLE_FLAG = X_SUBTITLE_MODIFIABLE_FLAG)
814 OR ((tlinfo.SUBTITLE_MODIFIABLE_FLAG is null)
815 AND (X_SUBTITLE_MODIFIABLE_FLAG is null)))
816 AND ((tlinfo.CLASS_SCHEDULE_EXCLUSION_FLAG = x_class_sched_exclusion_flag)
817 OR ((tlinfo.CLASS_SCHEDULE_EXCLUSION_FLAG is null)
818 AND (x_class_sched_exclusion_flag is null)))
819 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
820 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
821 AND (X_ATTRIBUTE_CATEGORY is null)))
822 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
823 OR ((tlinfo.ATTRIBUTE1 is null)
824 AND (X_ATTRIBUTE1 is null)))
825 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
826 OR ((tlinfo.ATTRIBUTE2 is null)
827 AND (X_ATTRIBUTE2 is null)))
828 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
829 OR ((tlinfo.ATTRIBUTE3 is null)
830 AND (X_ATTRIBUTE3 is null)))
831 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
832 OR ((tlinfo.ATTRIBUTE4 is null)
833 AND (X_ATTRIBUTE4 is null)))
834 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
835 OR ((tlinfo.ATTRIBUTE5 is null)
836 AND (X_ATTRIBUTE5 is null)))
837 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
838 OR ((tlinfo.ATTRIBUTE6 is null)
839 AND (X_ATTRIBUTE6 is null)))
840 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
841 OR ((tlinfo.ATTRIBUTE7 is null)
842 AND (X_ATTRIBUTE7 is null)))
843 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
844 OR ((tlinfo.ATTRIBUTE8 is null)
845 AND (X_ATTRIBUTE8 is null)))
846 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
847 OR ((tlinfo.ATTRIBUTE9 is null)
848 AND (X_ATTRIBUTE9 is null)))
849 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
850 OR ((tlinfo.ATTRIBUTE10 is null)
851 AND (X_ATTRIBUTE10 is null)))
852 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
853 OR ((tlinfo.ATTRIBUTE11 is null)
854 AND (X_ATTRIBUTE11 is null)))
855 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
856 OR ((tlinfo.ATTRIBUTE12 is null)
857 AND (X_ATTRIBUTE12 is null)))
858 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
859 OR ((tlinfo.ATTRIBUTE13 is null)
860 AND (X_ATTRIBUTE13 is null)))
861 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
862 OR ((tlinfo.ATTRIBUTE14 is null)
863 AND (X_ATTRIBUTE14 is null)))
864 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
865 OR ((tlinfo.ATTRIBUTE15 is null)
866 AND (X_ATTRIBUTE15 is null)))
867 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
868 OR ((tlinfo.ATTRIBUTE16 is null)
869 AND (X_ATTRIBUTE16 is null)))
870 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
871 OR ((tlinfo.ATTRIBUTE17 is null)
872 AND (X_ATTRIBUTE17 is null)))
873 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
874 OR ((tlinfo.ATTRIBUTE18 is null)
875 AND (X_ATTRIBUTE18 is null)))
876 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
877 OR ((tlinfo.ATTRIBUTE19 is null)
878 AND (X_ATTRIBUTE19 is null)))
879 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
880 OR ((tlinfo.ATTRIBUTE20 is null)
881 AND (X_ATTRIBUTE20 is null)))
882 AND ((tlinfo.RECORD_EXCLUSION_FLAG = X_RECORD_EXCLUSION_FLAG)
883 OR ((tlinfo.RECORD_EXCLUSION_FLAG is null)
884 AND (X_RECORD_EXCLUSION_FLAG is null)))
885 AND ((tlinfo.TITLE = X_TITLE)
886 OR ((tlinfo.TITLE is null)
887 AND (X_TITLE is null)))
888 AND ((tlinfo.SUBTITLE_ID = X_SUBTITLE_ID)
889 OR ((tlinfo.SUBTITLE_ID is null)
890 AND (X_SUBTITLE_ID is null)))
891 ) then
892 null;
893 else
894 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
895 IGS_GE_MSG_STACK.ADD;
896 app_exception.raise_exception;
897 end if;
898 return;
899 end LOCK_ROW;
900 Procedure UPDATE_ROW (
901 X_ROWID in VARCHAR2,
902 x_UNIT_SECTION_REFERENCE_ID IN NUMBER,
903 x_UOO_ID IN NUMBER,
904 x_SHORT_TITLE IN VARCHAR2,
905 x_SUBTITLE IN VARCHAR2,
906 x_SUBTITLE_MODIFIABLE_FLAG IN VARCHAR2,
907 x_class_sched_exclusion_flag IN VARCHAR2,
908 x_REGISTRATION_EXCLUSION_FLAG IN VARCHAR2,
909 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
910 x_ATTRIBUTE1 IN VARCHAR2,
911 x_ATTRIBUTE2 IN VARCHAR2,
912 x_ATTRIBUTE3 IN VARCHAR2,
913 x_ATTRIBUTE4 IN VARCHAR2,
914 x_ATTRIBUTE5 IN VARCHAR2,
915 x_ATTRIBUTE6 IN VARCHAR2,
916 x_ATTRIBUTE7 IN VARCHAR2,
917 x_ATTRIBUTE8 IN VARCHAR2,
918 x_ATTRIBUTE9 IN VARCHAR2,
919 x_ATTRIBUTE10 IN VARCHAR2,
920 x_ATTRIBUTE11 IN VARCHAR2,
921 x_ATTRIBUTE12 IN VARCHAR2,
922 x_ATTRIBUTE13 IN VARCHAR2,
923 x_ATTRIBUTE14 IN VARCHAR2,
924 x_ATTRIBUTE15 IN VARCHAR2,
925 x_ATTRIBUTE16 IN VARCHAR2,
926 x_ATTRIBUTE17 IN VARCHAR2,
927 x_ATTRIBUTE18 IN VARCHAR2,
928 x_ATTRIBUTE19 IN VARCHAR2,
929 x_ATTRIBUTE20 IN VARCHAR2,
930 x_RECORD_EXCLUSION_FLAG IN VARCHAR2 DEFAULT NULL,
931 x_TITLE IN VARCHAR2 DEFAULT NULL,
932 x_SUBTITLE_ID IN NUMBER DEFAULT NULL,
933 X_MODE in VARCHAR2 default 'R'
934 ) AS
935 /*************************************************************
936 Created By :
937 Date Created By :
938 Purpose :
939 Know limitations, enhancements or remarks
940 Change History
941 Who When What
942
943 (reverse chronological order - newest change first)
944 ***************************************************************/
945
946 X_LAST_UPDATE_DATE DATE ;
947 X_LAST_UPDATED_BY NUMBER ;
948 X_LAST_UPDATE_LOGIN NUMBER ;
949 begin
950 X_LAST_UPDATE_DATE := SYSDATE;
951 if(X_MODE = 'I') then
952 X_LAST_UPDATED_BY := 1;
953 X_LAST_UPDATE_LOGIN := 0;
954 elsif (X_MODE = 'R') then
955 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
956 if X_LAST_UPDATED_BY is NULL then
957 X_LAST_UPDATED_BY := -1;
958 end if;
959 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
960 if X_LAST_UPDATE_LOGIN is NULL then
961 X_LAST_UPDATE_LOGIN := -1;
962 end if;
963 else
964 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
965 IGS_GE_MSG_STACK.ADD;
966 app_exception.raise_exception;
967 end if;
968 Before_DML(
969 p_action=>'UPDATE',
970 x_rowid=>X_ROWID,
971 x_unit_section_reference_id=>X_UNIT_SECTION_REFERENCE_ID,
972 x_uoo_id=>X_UOO_ID,
973 x_short_title=>X_SHORT_TITLE,
974 x_subtitle=>X_SUBTITLE,
975 x_subtitle_modifiable_flag=>X_SUBTITLE_MODIFIABLE_FLAG,
976 x_class_sched_exclusion_flag=>x_class_sched_exclusion_flag,
977 x_registration_exclusion_flag=>X_REGISTRATION_EXCLUSION_FLAG,
978 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
979 x_attribute1=>X_ATTRIBUTE1,
980 x_attribute2=>X_ATTRIBUTE2,
981 x_attribute3=>X_ATTRIBUTE3,
982 x_attribute4=>X_ATTRIBUTE4,
983 x_attribute5=>X_ATTRIBUTE5,
984 x_attribute6=>X_ATTRIBUTE6,
985 x_attribute7=>X_ATTRIBUTE7,
986 x_attribute8=>X_ATTRIBUTE8,
987 x_attribute9=>X_ATTRIBUTE9,
988 x_attribute10=>X_ATTRIBUTE10,
989 x_attribute11=>X_ATTRIBUTE11,
990 x_attribute12=>X_ATTRIBUTE12,
991 x_attribute13=>X_ATTRIBUTE13,
992 x_attribute14=>X_ATTRIBUTE14,
993 x_attribute15=>X_ATTRIBUTE15,
994 x_attribute16=>X_ATTRIBUTE16,
995 x_attribute17=>X_ATTRIBUTE17,
996 x_attribute18=>X_ATTRIBUTE18,
997 x_attribute19=>X_ATTRIBUTE19,
998 x_attribute20=>X_ATTRIBUTE20,
999 x_record_exclusion_flag => X_RECORD_EXCLUSION_FLAG,
1000 x_title => X_TITLE,
1001 x_subtitle_id => X_SUBTITLE_ID,
1002 x_creation_date=>X_LAST_UPDATE_DATE,
1003 x_created_by=>X_LAST_UPDATED_BY,
1004 x_last_update_date=>X_LAST_UPDATE_DATE,
1005 x_last_updated_by=>X_LAST_UPDATED_BY,
1006 x_last_update_login=>X_LAST_UPDATE_LOGIN);
1007 update IGS_PS_USEC_REF set
1008 UOO_ID = NEW_REFERENCES.UOO_ID,
1009 SHORT_TITLE = NEW_REFERENCES.SHORT_TITLE,
1010 SUBTITLE_MODIFIABLE_FLAG = NEW_REFERENCES.SUBTITLE_MODIFIABLE_FLAG,
1011 CLASS_SCHEDULE_EXCLUSION_FLAG = NEW_REFERENCES.CLASS_SCHEDULE_EXCLUSION_FLAG,
1012 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1013 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1014 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1015 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1016 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1017 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1018 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1019 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1020 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1021 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1022 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1023 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
1024 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
1025 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
1026 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
1027 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
1028 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
1029 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
1030 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
1031 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
1032 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
1033 RECORD_EXCLUSION_FLAG = NEW_REFERENCES.RECORD_EXCLUSION_FLAG,
1034 TITLE = NEW_REFERENCES.TITLE,
1035 SUBTITLE_ID = NEW_REFERENCES.SUBTITLE_ID,
1036 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1037 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1038 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1039 where ROWID = X_ROWID;
1040 if (sql%notfound) then
1041 raise no_data_found;
1042 end if;
1043
1044 After_DML (
1045 p_action => 'UPDATE' ,
1046 x_rowid => X_ROWID
1047 );
1048 end UPDATE_ROW;
1049 procedure ADD_ROW (
1050 X_ROWID in out NOCOPY VARCHAR2,
1051 x_UNIT_SECTION_REFERENCE_ID IN OUT NOCOPY NUMBER,
1052 x_UOO_ID IN NUMBER,
1053 x_SHORT_TITLE IN VARCHAR2,
1054 x_SUBTITLE IN VARCHAR2,
1055 x_SUBTITLE_MODIFIABLE_FLAG IN VARCHAR2,
1056 x_class_sched_exclusion_flag IN VARCHAR2,
1057 x_REGISTRATION_EXCLUSION_FLAG IN VARCHAR2,
1058 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1059 x_ATTRIBUTE1 IN VARCHAR2,
1060 x_ATTRIBUTE2 IN VARCHAR2,
1061 x_ATTRIBUTE3 IN VARCHAR2,
1062 x_ATTRIBUTE4 IN VARCHAR2,
1063 x_ATTRIBUTE5 IN VARCHAR2,
1064 x_ATTRIBUTE6 IN VARCHAR2,
1065 x_ATTRIBUTE7 IN VARCHAR2,
1066 x_ATTRIBUTE8 IN VARCHAR2,
1067 x_ATTRIBUTE9 IN VARCHAR2,
1068 x_ATTRIBUTE10 IN VARCHAR2,
1069 x_ATTRIBUTE11 IN VARCHAR2,
1070 x_ATTRIBUTE12 IN VARCHAR2,
1071 x_ATTRIBUTE13 IN VARCHAR2,
1072 x_ATTRIBUTE14 IN VARCHAR2,
1073 x_ATTRIBUTE15 IN VARCHAR2,
1074 x_ATTRIBUTE16 IN VARCHAR2,
1075 x_ATTRIBUTE17 IN VARCHAR2,
1076 x_ATTRIBUTE18 IN VARCHAR2,
1077 x_ATTRIBUTE19 IN VARCHAR2,
1078 x_ATTRIBUTE20 IN VARCHAR2,
1079 x_RECORD_EXCLUSION_FLAG IN VARCHAR2 DEFAULT NULL,
1080 x_TITLE IN VARCHAR2 DEFAULT NULL,
1081 x_SUBTITLE_ID IN NUMBER DEFAULT NULL ,
1082 X_MODE in VARCHAR2 default 'R'
1083 ) AS
1084 /*************************************************************
1085 Created By :
1086 Date Created By :
1087 Purpose :
1088 Know limitations, enhancements or remarks
1089 Change History
1090 Who When What
1091
1092 (reverse chronological order - newest change first)
1093 ***************************************************************/
1094
1095 cursor c1 is select ROWID from IGS_PS_USEC_REF
1096 where UNIT_SECTION_REFERENCE_ID= X_UNIT_SECTION_REFERENCE_ID
1097 ;
1098 begin
1099 open c1;
1100 fetch c1 into X_ROWID;
1101 if (c1%notfound) then
1102 close c1;
1103 INSERT_ROW (
1104 X_ROWID,
1105 X_UNIT_SECTION_REFERENCE_ID,
1106 X_UOO_ID,
1107 X_SHORT_TITLE,
1108 X_SUBTITLE,
1109 X_SUBTITLE_MODIFIABLE_FLAG,
1110 x_class_sched_exclusion_flag,
1111 X_REGISTRATION_EXCLUSION_FLAG,
1112 X_ATTRIBUTE_CATEGORY,
1113 X_ATTRIBUTE1,
1114 X_ATTRIBUTE2,
1115 X_ATTRIBUTE3,
1116 X_ATTRIBUTE4,
1117 X_ATTRIBUTE5,
1118 X_ATTRIBUTE6,
1119 X_ATTRIBUTE7,
1120 X_ATTRIBUTE8,
1121 X_ATTRIBUTE9,
1122 X_ATTRIBUTE10,
1123 X_ATTRIBUTE11,
1124 X_ATTRIBUTE12,
1125 X_ATTRIBUTE13,
1126 X_ATTRIBUTE14,
1127 X_ATTRIBUTE15,
1128 X_ATTRIBUTE16,
1129 X_ATTRIBUTE17,
1130 X_ATTRIBUTE18,
1131 X_ATTRIBUTE19,
1132 X_ATTRIBUTE20,
1133 X_RECORD_EXCLUSION_FLAG,
1134 X_TITLE,
1135 X_SUBTITLE_ID,
1136 X_MODE );
1137 return;
1138 end if;
1139 close c1;
1140 UPDATE_ROW (
1141 X_ROWID,
1142 X_UNIT_SECTION_REFERENCE_ID,
1143 X_UOO_ID,
1144 X_SHORT_TITLE,
1145 X_SUBTITLE,
1146 X_SUBTITLE_MODIFIABLE_FLAG,
1147 x_class_sched_exclusion_flag,
1148 X_REGISTRATION_EXCLUSION_FLAG,
1149 X_ATTRIBUTE_CATEGORY,
1150 X_ATTRIBUTE1,
1151 X_ATTRIBUTE2,
1152 X_ATTRIBUTE3,
1153 X_ATTRIBUTE4,
1154 X_ATTRIBUTE5,
1155 X_ATTRIBUTE6,
1156 X_ATTRIBUTE7,
1157 X_ATTRIBUTE8,
1158 X_ATTRIBUTE9,
1159 X_ATTRIBUTE10,
1160 X_ATTRIBUTE11,
1161 X_ATTRIBUTE12,
1162 X_ATTRIBUTE13,
1163 X_ATTRIBUTE14,
1164 X_ATTRIBUTE15,
1165 X_ATTRIBUTE16,
1166 X_ATTRIBUTE17,
1167 X_ATTRIBUTE18,
1168 X_ATTRIBUTE19,
1169 X_ATTRIBUTE20,
1170 X_RECORD_EXCLUSION_FLAG,
1171 X_TITLE,
1172 X_SUBTITLE_ID,
1173 X_MODE );
1174 end ADD_ROW;
1175 procedure DELETE_ROW (
1176 X_ROWID in VARCHAR2
1177 ) AS
1178 /*************************************************************
1179 Created By :
1180 Date Created By :
1181 Purpose :
1182 Know limitations, enhancements or remarks
1183 Change History
1184 Who When What
1185
1186 (reverse chronological order - newest change first)
1187 ***************************************************************/
1188
1189 begin
1190 Before_DML (
1191 p_action => 'DELETE',
1192 x_rowid => X_ROWID
1193 );
1194 delete from IGS_PS_USEC_REF
1195 where ROWID = X_ROWID;
1196 if (sql%notfound) then
1197 raise no_data_found;
1198 end if;
1199 After_DML (
1200 p_action => 'DELETE',
1201 x_rowid => X_ROWID
1202 );
1203 end DELETE_ROW;
1204 END igs_ps_usec_ref_pkg;