[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_COO_AD_UNIT_S_PKG
Source
1 package body IGS_PS_COO_AD_UNIT_S_PKG AS
2 /* $Header: IGSPI02B.pls 120.1 2006/05/29 07:35:15 sarakshi noship $ */
3
4 --Who When What
5 --sarakshi 27-Apr-2006 Bug#5165619, modified check_parent_existance and created procedure GET_FK_IGS_PS_OFR_UNIT_SET
6
7 l_rowid VARCHAR2(25);
8 old_references IGS_PS_COO_AD_UNIT_S%RowType;
9 new_references IGS_PS_COO_AD_UNIT_S%RowType;
10
11 PROCEDURE Set_Column_Values (
12 p_action IN VARCHAR2,
13 x_rowid IN VARCHAR2 DEFAULT NULL,
14 x_course_cd IN VARCHAR2 DEFAULT NULL,
15 x_crv_version_number IN NUMBER DEFAULT NULL,
16 x_cal_type IN VARCHAR2 DEFAULT NULL,
17 x_location_cd IN VARCHAR2 DEFAULT NULL,
18 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
19 x_attendance_type IN VARCHAR2 DEFAULT NULL,
20 x_admission_cat IN VARCHAR2 DEFAULT NULL,
21 x_unit_set_cd IN VARCHAR2 DEFAULT NULL,
22 x_us_version_number IN NUMBER DEFAULT NULL,
23 x_creation_date IN DATE DEFAULT NULL,
24 x_created_by IN NUMBER DEFAULT NULL,
25 x_last_update_date IN DATE DEFAULT NULL,
26 x_last_updated_by IN NUMBER DEFAULT NULL,
27 x_last_update_login IN NUMBER DEFAULT NULL
28 ) AS
29
30 CURSOR cur_old_ref_values IS
31 SELECT *
32 FROM IGS_PS_COO_AD_UNIT_S
33 WHERE rowid = x_rowid;
34
35 BEGIN
36
37 l_rowid := x_rowid;
38
39 -- Code for setting the Old and New Reference Values.
40 -- Populate Old Values.
41 Open cur_old_ref_values;
42 Fetch cur_old_ref_values INTO old_references;
43 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
44 Close cur_old_ref_values;
45 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
46 IGS_GE_MSG_STACK.ADD;
47 App_Exception.Raise_Exception;
48 Return;
49 END IF;
50 Close cur_old_ref_values;
51
52 -- Populate New Values.
53 new_references.course_cd := x_course_cd;
54 new_references.crv_version_number := x_crv_version_number;
55 new_references.cal_type := x_cal_type;
56 new_references.location_cd := x_location_cd;
57 new_references.attendance_mode := x_attendance_mode;
58 new_references.attendance_type := x_attendance_type;
59 new_references.admission_cat := x_admission_cat;
60 new_references.unit_set_cd := x_unit_set_cd;
61 new_references.us_version_number := x_us_version_number;
62 IF (p_action = 'UPDATE') THEN
63 new_references.creation_date := old_references.creation_date;
64 new_references.created_by := old_references.created_by;
65 ELSE
66 new_references.creation_date := x_creation_date;
67 new_references.created_by := x_created_by;
68 END IF;
69 new_references.last_update_date := x_last_update_date;
70 new_references.last_updated_by := x_last_updated_by;
71 new_references.last_update_login := x_last_update_login;
72
73 END Set_Column_Values;
74
75 PROCEDURE Check_Constraints (
76 Column_Name IN VARCHAR2 DEFAULT NULL,
77 Column_Value IN VARCHAR2 DEFAULT NULL
78 )
79 AS
80 BEGIN
81
82 IF column_name is null then
83 NULL;
84 ELSIF upper(Column_name) = 'ADMISSION_CAT' then
85 new_references.admission_cat := column_value;
86 ELSIF upper(Column_name) = 'ATTENDANCE_MODE' then
87 new_references.attendance_mode := column_value;
88 ELSIF upper(Column_name) = 'ATTENDANCE_TYPE' then
89 new_references.attendance_type := column_value;
90 ELSIF upper(Column_name) = 'CAL_TYPE' then
91 new_references.cal_type := column_value;
92 ELSIF upper(Column_name) = 'COURSE_CD' then
93 new_references.course_cd := column_value;
94 ELSIF upper(Column_name) = 'LOCATION_CD' then
95 new_references.location_cd := column_value;
96 ELSIF upper(Column_name) = 'UNIT_SET_CD' then
97 new_references.unit_set_cd := column_value;
98 END IF;
99
100 IF upper(column_name) = 'ADMISSION_CAT' OR
101 column_name is null Then
102 IF ( new_references.admission_cat <> UPPER(new_references.admission_cat) ) Then
103 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
104 IGS_GE_MSG_STACK.ADD;
105 App_Exception.Raise_Exception;
106 END IF;
107 END IF;
108
109 IF upper(column_name) = 'ATTENDANCE_MODE' OR
110 column_name is null Then
111 IF ( new_references.attendance_mode <> UPPER(new_references.attendance_mode) ) Then
112 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
113 IGS_GE_MSG_STACK.ADD;
114 App_Exception.Raise_Exception;
115 END IF;
116 END IF;
117
118 IF upper(column_name) = 'ATTENDANCE_TYPE' OR
119 column_name is null Then
120 IF ( new_references.attendance_type <> UPPER(new_references.attendance_type) ) Then
121 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
122 IGS_GE_MSG_STACK.ADD;
123 App_Exception.Raise_Exception;
124 END IF;
125 END IF;
126
127 IF upper(column_name) = 'CAL_TYPE' OR
128 column_name is null Then
129 IF ( new_references.cal_type <> UPPER(new_references.cal_type) ) Then
130 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
131 IGS_GE_MSG_STACK.ADD;
132 App_Exception.Raise_Exception;
133 END IF;
134 END IF;
135
136 IF upper(column_name) = 'COURSE_CD' OR
137 column_name is null Then
138 IF ( new_references.course_cd <> UPPER(new_references.course_cd) ) Then
139 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
140 IGS_GE_MSG_STACK.ADD;
141 App_Exception.Raise_Exception;
142 END IF;
143 END IF;
144
145 IF upper(column_name) = 'LOCATION_CD' OR
146 column_name is null Then
147 IF ( new_references.location_cd <> UPPER(new_references.location_cd) ) Then
148 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
149 IGS_GE_MSG_STACK.ADD;
150 App_Exception.Raise_Exception;
151 END IF;
152 END IF;
153
154 IF upper(column_name) = 'UNIT_SET_CD' OR
155 column_name is null Then
156 IF ( new_references.unit_set_cd <> UPPER(new_references.unit_set_cd) ) Then
157 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
158 IGS_GE_MSG_STACK.ADD;
159 App_Exception.Raise_Exception;
160 END IF;
161 END IF;
162
163 END Check_Constraints;
164
165 PROCEDURE Check_Parent_Existance AS
166
167 CURSOR cur_check(cp_course_cd igs_ps_ofr_opt_unit_set_v.course_cd%TYPE,
168 cp_crv_version_number igs_ps_ofr_opt_unit_set_v.crv_version_number%TYPE,
169 cp_cal_type igs_ps_ofr_opt_unit_set_v.cal_type%TYPE,
170 cp_location_cd igs_ps_ofr_opt_unit_set_v.location_cd%TYPE,
171 cp_attendance_mode igs_ps_ofr_opt_unit_set_v.attendance_mode%TYPE,
172 cp_attendance_type igs_ps_ofr_opt_unit_set_v.attendance_type%TYPE,
173 cp_unit_set_cd igs_ps_ofr_opt_unit_set_v.unit_set_cd%TYPE,
174 cp_us_version_number igs_ps_ofr_opt_unit_set_v.us_version_number%TYPE) IS
175 SELECT 'X'
176 FROM igs_ps_ofr_opt_unit_set_v
177 WHERE course_cd=cp_course_cd
178 AND crv_version_number=cp_crv_version_number
179 AND cal_type = cp_cal_type
180 AND location_cd = cp_location_cd
181 AND attendance_mode = cp_attendance_mode
182 AND attendance_type = cp_attendance_type
183 AND unit_set_cd = cp_unit_set_cd
184 AND us_version_number = cp_us_version_number;
185 l_c_var VARCHAR2(1);
186
187 BEGIN
188
189 IF (((old_references.course_cd = new_references.course_cd) AND
190 (old_references.crv_version_number = new_references.crv_version_number) AND
191 (old_references.cal_type = new_references.cal_type) AND
192 (old_references.location_cd = new_references.location_cd) AND
193 (old_references.attendance_mode = new_references.attendance_mode) AND
194 (old_references.attendance_type = new_references.attendance_type) AND
195 (old_references.admission_cat = new_references.admission_cat)) OR
196 ((new_references.course_cd IS NULL) OR
197 (new_references.crv_version_number IS NULL) OR
198 (new_references.cal_type IS NULL) OR
199 (new_references.location_cd IS NULL) OR
200 (new_references.attendance_mode IS NULL) OR
201 (new_references.attendance_type IS NULL) OR
202 (new_references.admission_cat IS NULL))) THEN
203 NULL;
204 ELSE
205 IF NOT IGS_PS_OF_OPT_AD_CAT_PKG.Get_PK_For_Validation (
206 new_references.course_cd,
207 new_references.crv_version_number,
208 new_references.cal_type,
209 new_references.location_cd,
210 new_references.attendance_mode,
211 new_references.attendance_type,
212 new_references.admission_cat
213 ) THEN
214 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
215 IGS_GE_MSG_STACK.ADD;
216 App_Exception.Raise_Exception;
217 END IF;
218 END IF;
219
220 IF (((old_references.unit_set_cd = new_references.unit_set_cd) AND
221 (old_references.us_version_number = new_references.us_version_number)) OR
222 ((new_references.unit_set_cd IS NULL) OR
223 (new_references.us_version_number IS NULL))) THEN
224 NULL;
225 ELSE
226 IF NOT IGS_EN_UNIT_SET_PKG.Get_PK_For_Validation (
227 new_references.unit_set_cd,
228 new_references.us_version_number
229 ) THEN
230 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
231 IGS_GE_MSG_STACK.ADD;
232 App_Exception.Raise_Exception;
233 END IF;
234
235 --Added the check parent existance for the IGS_PS_OFR_UNIT_SET and IGS_PS_OF_OPT_UNT_ST
236 OPEN cur_check( new_references.course_cd,
237 new_references.crv_version_number,
238 new_references.cal_type,
239 new_references.location_cd,
240 new_references.attendance_mode,
241 new_references.attendance_type,
242 new_references.unit_set_cd,
243 new_references.us_version_number);
244 FETCH cur_check INTO l_c_var;
245 IF cur_check%NOTFOUND THEN
246 CLOSE cur_check;
247 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
248 IGS_GE_MSG_STACK.ADD;
249 App_Exception.Raise_Exception;
250 END IF;
251 CLOSE cur_check;
252
253 END IF;
254
255 END Check_Parent_Existance;
256
257 FUNCTION Get_PK_For_Validation (
258 x_course_cd IN VARCHAR2,
259 x_crv_version_number IN NUMBER,
260 x_cal_type IN VARCHAR2,
261 x_location_cd IN VARCHAR2,
262 x_attendance_mode IN VARCHAR2,
263 x_attendance_type IN VARCHAR2,
264 x_admission_cat IN VARCHAR2,
265 x_unit_set_cd IN VARCHAR2,
266 x_us_version_number IN NUMBER
267 )
268 RETURN BOOLEAN AS
269
270 CURSOR cur_rowid IS
271 SELECT rowid
272 FROM IGS_PS_COO_AD_UNIT_S
273 WHERE course_cd = x_course_cd
274 AND crv_version_number = x_crv_version_number
275 AND cal_type = x_cal_type
276 AND location_cd = x_location_cd
277 AND attendance_mode = x_attendance_mode
278 AND attendance_type = x_attendance_type
279 AND admission_cat = x_admission_cat
280 AND unit_set_cd = x_unit_set_cd
281 AND us_version_number = x_us_version_number
282 FOR UPDATE NOWAIT;
283
284 lv_rowid cur_rowid%RowType;
285
286 BEGIN
287
288 Open cur_rowid;
289 Fetch cur_rowid INTO lv_rowid;
290 IF (cur_rowid%FOUND) THEN
291 Close cur_rowid;
292 Return (TRUE);
293 ELSE
294 Close cur_rowid;
295 Return (FALSE);
296 END IF;
297
298 END Get_PK_For_Validation;
299
300 PROCEDURE GET_FK_IGS_PS_OF_OPT_AD_CAT (
301 x_course_cd IN VARCHAR2,
302 x_version_number IN NUMBER,
303 x_cal_type IN VARCHAR2,
304 x_location_cd IN VARCHAR2,
305 x_attendance_mode IN VARCHAR2,
306 x_attendance_type IN VARCHAR2,
307 x_admission_cat IN VARCHAR2
308 ) AS
309
310 CURSOR cur_rowid IS
311 SELECT rowid
312 FROM IGS_PS_COO_AD_UNIT_S
313 WHERE course_cd = x_course_cd
314 AND crv_version_number = x_version_number
315 AND cal_type = x_cal_type
316 AND location_cd = x_location_cd
317 AND attendance_mode = x_attendance_mode
318 AND attendance_type = x_attendance_type
319 AND admission_cat = x_admission_cat ;
320
321 lv_rowid cur_rowid%RowType;
322
323 BEGIN
324
325 Open cur_rowid;
326 Fetch cur_rowid INTO lv_rowid;
327 IF (cur_rowid%FOUND) THEN
328 Close cur_rowid;
329 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CACUS_COOAC_FK');
330 IGS_GE_MSG_STACK.ADD;
331 App_Exception.Raise_Exception;
332 Return;
333 END IF;
334 Close cur_rowid;
335
336 END GET_FK_IGS_PS_OF_OPT_AD_CAT;
337
338 PROCEDURE GET_FK_IGS_EN_UNIT_SET (
339 x_unit_set_cd IN VARCHAR2,
340 x_version_number IN NUMBER
341 ) AS
342
343 CURSOR cur_rowid IS
344 SELECT rowid
345 FROM IGS_PS_COO_AD_UNIT_S
346 WHERE unit_set_cd = x_unit_set_cd
347 AND us_version_number = x_version_number ;
348
349 lv_rowid cur_rowid%RowType;
350
351 BEGIN
352
353 Open cur_rowid;
354 Fetch cur_rowid INTO lv_rowid;
355 IF (cur_rowid%FOUND) THEN
356 Close cur_rowid;
357 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CACUS_US_FK');
358 IGS_GE_MSG_STACK.ADD;
359 App_Exception.Raise_Exception;
360 Return;
361 END IF;
362 Close cur_rowid;
363
364 END GET_FK_IGS_EN_UNIT_SET;
365
366
367 PROCEDURE GET_FK_IGS_PS_OFR_UNIT_SET (
368 x_course_cd IN VARCHAR2,
369 x_version_number IN NUMBER,
370 x_cal_type IN VARCHAR2,
371 x_unit_set_cd IN VARCHAR2,
372 x_us_version_number IN NUMBER
373 ) AS
374 /*************************************************************
375 Created By :sarakshi
376 Date Created By :27-APR-2006
377 Purpose :
378 Know limitations, enhancements or remarks
379 Change History
380 Who When What
381
382 (reverse chronological order - newest change first)
383 ***************************************************************/
384 CURSOR cur_rowid IS
385 SELECT rowid
386 FROM IGS_PS_COO_AD_UNIT_S
387 WHERE course_cd = x_course_cd
388 AND crv_version_number = x_version_number
389 AND cal_type = x_cal_type
390 AND unit_set_cd = x_unit_set_cd
391 AND us_version_number = x_us_version_number ;
392
393 lv_rowid cur_rowid%RowType;
394
395 BEGIN
396
397 Open cur_rowid;
398 Fetch cur_rowid INTO lv_rowid;
399 IF (cur_rowid%FOUND) THEN
400 Close cur_rowid;
401 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CACUS_US_FK');
402 IGS_GE_MSG_STACK.ADD;
403 App_Exception.Raise_Exception;
404 Return;
405 END IF;
406 Close cur_rowid;
407
408 END GET_FK_IGS_PS_OFR_UNIT_SET;
409
410
411
412 PROCEDURE Before_DML (
413 p_action IN VARCHAR2,
414 x_rowid IN VARCHAR2 DEFAULT NULL,
415 x_course_cd IN VARCHAR2 DEFAULT NULL,
416 x_crv_version_number IN NUMBER DEFAULT NULL,
417 x_cal_type IN VARCHAR2 DEFAULT NULL,
418 x_location_cd IN VARCHAR2 DEFAULT NULL,
419 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
420 x_attendance_type IN VARCHAR2 DEFAULT NULL,
421 x_admission_cat IN VARCHAR2 DEFAULT NULL,
422 x_unit_set_cd IN VARCHAR2 DEFAULT NULL,
423 x_us_version_number IN NUMBER DEFAULT NULL,
424 x_creation_date IN DATE DEFAULT NULL,
425 x_created_by IN NUMBER DEFAULT NULL,
426 x_last_update_date IN DATE DEFAULT NULL,
427 x_last_updated_by IN NUMBER DEFAULT NULL,
428 x_last_update_login IN NUMBER DEFAULT NULL
429 ) AS
430 BEGIN
431
432 Set_Column_Values (
433 p_action,
434 x_rowid,
435 x_course_cd,
436 x_crv_version_number,
437 x_cal_type,
438 x_location_cd,
439 x_attendance_mode,
440 x_attendance_type,
441 x_admission_cat,
442 x_unit_set_cd,
443 x_us_version_number,
444 x_creation_date,
445 x_created_by,
446 x_last_update_date,
447 x_last_updated_by,
448 x_last_update_login
449 );
450
451 IF (p_action = 'INSERT') THEN
452 -- Call all the procedures related to Before Insert.
453 IF Get_PK_For_Validation (
454 new_references.course_cd,
455 new_references.crv_version_number,
456 new_references.cal_type,
457 new_references.location_cd,
461 new_references.unit_set_cd,
458 new_references.attendance_mode,
459 new_references.attendance_type,
460 new_references.admission_cat,
462 new_references.us_version_number) THEN
463 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
464 IGS_GE_MSG_STACK.ADD;
465 App_Exception.Raise_Exception;
466 END IF;
467 Check_Constraints;
468 Check_Parent_Existance;
469 ELSIF (p_action = 'UPDATE') THEN
470 -- Call all the procedures related to Before Update.
471 Check_Constraints;
472 Check_Parent_Existance;
473 ELSIF (p_action = 'VALIDATE_INSERT') THEN
474 IF Get_PK_For_Validation (
475 new_references.course_cd,
476 new_references.crv_version_number,
477 new_references.cal_type,
478 new_references.location_cd,
479 new_references.attendance_mode,
480 new_references.attendance_type,
481 new_references.admission_cat,
482 new_references.unit_set_cd,
483 new_references.us_version_number) THEN
484 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
485 IGS_GE_MSG_STACK.ADD;
486 App_Exception.Raise_Exception;
487 END IF;
488 Check_Constraints;
489 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
490 Check_Constraints;
491 END IF;
492
493 END Before_DML;
494
495 PROCEDURE After_DML (
496 p_action IN VARCHAR2,
497 x_rowid IN VARCHAR2
498 ) AS
499 BEGIN
500
501 l_rowid := x_rowid;
502
503 END After_DML;
504
505 procedure INSERT_ROW (
506 X_ROWID in out NOCOPY VARCHAR2,
507 X_COURSE_CD in VARCHAR2,
508 X_CRV_VERSION_NUMBER in NUMBER,
509 X_CAL_TYPE in VARCHAR2,
510 X_LOCATION_CD in VARCHAR2,
511 X_ATTENDANCE_MODE in VARCHAR2,
512 X_ATTENDANCE_TYPE in VARCHAR2,
513 X_ADMISSION_CAT in VARCHAR2,
514 X_UNIT_SET_CD in VARCHAR2,
515 X_US_VERSION_NUMBER in NUMBER,
516 X_MODE in VARCHAR2 default 'R'
517 ) as
518 cursor C is select ROWID from IGS_PS_COO_AD_UNIT_S
519 where COURSE_CD = X_COURSE_CD
520 and CRV_VERSION_NUMBER = X_CRV_VERSION_NUMBER
521 and CAL_TYPE = X_CAL_TYPE
522 and LOCATION_CD = X_LOCATION_CD
523 and ATTENDANCE_MODE = X_ATTENDANCE_MODE
524 and ATTENDANCE_TYPE = X_ATTENDANCE_TYPE
525 and ADMISSION_CAT = X_ADMISSION_CAT
526 and UNIT_SET_CD = X_UNIT_SET_CD
527 and US_VERSION_NUMBER = X_US_VERSION_NUMBER;
528 X_LAST_UPDATE_DATE DATE;
529 X_LAST_UPDATED_BY NUMBER;
530 X_LAST_UPDATE_LOGIN NUMBER;
531 begin
532 X_LAST_UPDATE_DATE := SYSDATE;
533 if(X_MODE = 'I') then
534 X_LAST_UPDATED_BY := 1;
535 X_LAST_UPDATE_LOGIN := 0;
536 elsif (X_MODE = 'R') then
537 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
538 if X_LAST_UPDATED_BY is NULL then
539 X_LAST_UPDATED_BY := -1;
540 end if;
541 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
542 if X_LAST_UPDATE_LOGIN is NULL then
543 X_LAST_UPDATE_LOGIN := -1;
544 end if;
545 else
546 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
547 IGS_GE_MSG_STACK.ADD;
548 app_exception.raise_exception;
549 end if;
550
551 Before_DML (
552 p_action => 'INSERT',
553 x_rowid => X_ROWID,
554 x_course_cd => X_COURSE_CD ,
555 x_crv_version_number => X_CRV_VERSION_NUMBER,
556 x_cal_type => X_CAL_TYPE,
557 x_location_cd => X_LOCATION_CD,
558 x_attendance_mode => X_ATTENDANCE_MODE,
559 x_attendance_type => X_ATTENDANCE_TYPE,
560 x_admission_cat => X_ADMISSION_CAT,
561 x_unit_set_cd => X_UNIT_SET_CD,
562 x_us_version_number => X_US_VERSION_NUMBER,
563 x_creation_date => X_LAST_UPDATE_DATE ,
564 x_created_by => X_LAST_UPDATED_BY ,
565 x_last_update_date => X_LAST_UPDATE_DATE ,
566 x_last_updated_by => X_LAST_UPDATED_BY ,
567 x_last_update_login => X_LAST_UPDATE_LOGIN
568 );
569
570 insert into IGS_PS_COO_AD_UNIT_S (
571 COURSE_CD,
572 CRV_VERSION_NUMBER,
573 CAL_TYPE,
574 LOCATION_CD,
575 ATTENDANCE_MODE,
576 ATTENDANCE_TYPE,
577 ADMISSION_CAT,
578 UNIT_SET_CD,
579 US_VERSION_NUMBER,
580 CREATION_DATE,
581 CREATED_BY,
582 LAST_UPDATE_DATE,
583 LAST_UPDATED_BY,
584 LAST_UPDATE_LOGIN
585 ) values (
586 NEW_REFERENCES.COURSE_CD,
587 NEW_REFERENCES.CRV_VERSION_NUMBER,
588 NEW_REFERENCES.CAL_TYPE,
589 NEW_REFERENCES.LOCATION_CD,
590 NEW_REFERENCES.ATTENDANCE_MODE,
591 NEW_REFERENCES.ATTENDANCE_TYPE,
592 NEW_REFERENCES.ADMISSION_CAT,
593 NEW_REFERENCES.UNIT_SET_CD,
594 NEW_REFERENCES.US_VERSION_NUMBER,
595 X_LAST_UPDATE_DATE,
596 X_LAST_UPDATED_BY,
597 X_LAST_UPDATE_DATE,
598 X_LAST_UPDATED_BY,
599 X_LAST_UPDATE_LOGIN
600 );
601
602 open c;
603 fetch c into X_ROWID;
604 if (c%notfound) then
605 close c;
606 raise no_data_found;
607 end if;
608 close c;
609
610 After_DML (
611 p_action => 'INSERT',
612 x_rowid => X_ROWID
613 );
614
615 end INSERT_ROW;
616
617 procedure LOCK_ROW (
618 X_ROWID IN VARCHAR2,
619 X_COURSE_CD in VARCHAR2,
620 X_CRV_VERSION_NUMBER in NUMBER,
621 X_CAL_TYPE in VARCHAR2,
622 X_LOCATION_CD in VARCHAR2,
623 X_ATTENDANCE_MODE in VARCHAR2,
624 X_ATTENDANCE_TYPE in VARCHAR2,
625 X_ADMISSION_CAT in VARCHAR2,
626 X_UNIT_SET_CD in VARCHAR2,
627 X_US_VERSION_NUMBER in NUMBER
628 ) as
629 cursor c1 is select ROWID
630 from IGS_PS_COO_AD_UNIT_S
631 where ROWID = X_ROWID
632 for update nowait;
633 tlinfo c1%rowtype;
634
635 begin
636 open c1;
637 fetch c1 into tlinfo;
638 if (c1%notfound) then
639 close c1;
640 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
641 IGS_GE_MSG_STACK.ADD;
642 app_exception.raise_exception;
643 return;
644 end if;
645 close c1;
646
647 return;
648 end LOCK_ROW;
649
650 procedure DELETE_ROW (
651 X_ROWID in VARCHAR2
652 ) as
653 begin
654
655 Before_DML (
656 p_action => 'DELETE',
657 x_rowid => X_ROWID
658 );
659
660 delete from IGS_PS_COO_AD_UNIT_S
661 where ROWID = X_ROWID;
662 if (sql%notfound) then
663 raise no_data_found;
664 end if;
665
666 After_DML (
667 p_action => 'DELETE',
668 x_rowid => X_ROWID
669 );
670
671 end DELETE_ROW;
672
673 end IGS_PS_COO_AD_UNIT_S_PKG;