[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_OFR_UNIT_SET_PKG
Source
1 package body IGS_PS_OFR_UNIT_SET_PKG AS
2 /* $Header: IGSPI26B.pls 120.1 2006/05/29 07:39:18 sarakshi noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_OFR_UNIT_SET%RowType;
6 new_references IGS_PS_OFR_UNIT_SET%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_course_cd IN VARCHAR2 DEFAULT NULL,
12 x_crv_version_number IN NUMBER DEFAULT NULL,
13 x_cal_type IN VARCHAR2 DEFAULT NULL,
14 x_unit_set_cd IN VARCHAR2 DEFAULT NULL,
15 x_us_version_number IN NUMBER DEFAULT NULL,
16 x_override_title IN VARCHAR2 DEFAULT NULL,
17 x_only_as_sub_ind IN VARCHAR2 DEFAULT NULL,
18 x_show_on_official_ntfctn_ind IN VARCHAR2 DEFAULT NULL,
19 x_creation_date IN DATE DEFAULT NULL,
20 x_created_by IN NUMBER DEFAULT NULL,
21 x_last_update_date IN DATE DEFAULT NULL,
22 x_last_updated_by IN NUMBER DEFAULT NULL,
23 x_last_update_login IN NUMBER DEFAULT NULL
24 ) AS
25
26 CURSOR cur_old_ref_values IS
27 SELECT *
28 FROM IGS_PS_OFR_UNIT_SET
29 WHERE rowid = x_rowid;
30
31 BEGIN
32
33 l_rowid := x_rowid;
34
35 -- Code for setting the Old and New Reference Values.
36 -- Populate Old Values.
37 Open cur_old_ref_values;
38 Fetch cur_old_ref_values INTO old_references;
39 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
40 Close cur_old_ref_values;
41 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
42 IGS_GE_MSG_STACK.ADD;
43 App_Exception.Raise_Exception;
44 Return;
45 END IF;
46 Close cur_old_ref_values;
47
48 -- Populate New Values.
49 new_references.course_cd := x_course_cd;
50 new_references.crv_version_number := x_crv_version_number;
51 new_references.cal_type := x_cal_type;
52 new_references.unit_set_cd := x_unit_set_cd;
53 new_references.us_version_number := x_us_version_number;
54 new_references.override_title := x_override_title;
55 new_references.only_as_sub_ind := x_only_as_sub_ind;
56 new_references.show_on_official_ntfctn_ind := x_show_on_official_ntfctn_ind;
57 IF (p_action = 'UPDATE') THEN
58 new_references.creation_date := old_references.creation_date;
59 new_references.created_by := old_references.created_by;
60 ELSE
61 new_references.creation_date := x_creation_date;
62 new_references.created_by := x_created_by;
63 END IF;
64 new_references.last_update_date := x_last_update_date;
65 new_references.last_updated_by := x_last_updated_by;
66 new_references.last_update_login := x_last_update_login;
67
68 END Set_Column_Values;
69
70 -- Trigger description :-
71 -- "OSS_TST".trg_cous_br_iu
72 -- BEFORE INSERT OR UPDATE
73 -- ON IGS_PS_OFR_UNIT_SET
74 -- FOR EACH ROW
75
76 PROCEDURE BeforeRowInsertUpdate1(
77 p_inserting IN BOOLEAN DEFAULT FALSE,
78 p_updating IN BOOLEAN DEFAULT FALSE,
79 p_deleting IN BOOLEAN DEFAULT FALSE
80 ) AS
81 v_message_name VARCHAR2(30);
82 BEGIN
83 -- Validate that inserts
84 IF p_inserting THEN
85 -- <cous1>
86 -- Can only create against ACTIVE or PLANNED IGS_PS_COURSE versions
87 IF IGS_PS_VAL_CRS.crsp_val_iud_crv_dtl (
88 new_references.course_cd,
89 new_references.crv_version_number,
90 v_message_name) = FALSE THEN
91 Fnd_Message.Set_Name('IGS',v_message_name);
92 IGS_GE_MSG_STACK.ADD;
93 App_Exception.Raise_Exception;
94 END IF;
95 -- <cous2>
96 -- Can only create against ACTIVE or PLANNED IGS_PS_UNIT sets
97 IF IGS_PS_VAL_COUSR.crsp_val_iud_us_dtl (
98 new_references.unit_set_cd,
99 new_references.us_version_number,
100 v_message_name) = FALSE THEN
101 Fnd_Message.Set_Name('IGS',v_message_name);
102 IGS_GE_MSG_STACK.ADD;
103 App_Exception.Raise_Exception;
104 END IF;
105 END IF;
106 -- Validate that inserts/updates are allowed
107 IF p_inserting OR p_updating THEN
108 -- <cous3>
109 -- Can only link to courses which do not breach the IGS_PS_COURSE type restrictions
110 IF IGS_PS_VAL_COus.crsp_val_cous_usctv (
111 new_references.course_cd,
112 new_references.crv_version_number,
113 new_references.unit_set_cd,
114 new_references.us_version_number,
115 v_message_name) = FALSE THEN
116 Fnd_Message.Set_Name('IGS',v_message_name);
117 IGS_GE_MSG_STACK.ADD;
118 App_Exception.Raise_Exception;
119 END IF;
120 -- <cous5>, <cous6>
121 -- Validate the 'only as subordinate indicator'
122 IF IGS_PS_VAL_COus.crsp_val_cous_subind (
123 new_references.course_cd,
124 new_references.crv_version_number,
125 new_references.cal_type,
126 new_references.unit_set_cd,
127 new_references.us_version_number,
128 old_references.only_as_sub_ind,
129 new_references.only_as_sub_ind,
130 v_message_name) = FALSE THEN
131 Fnd_Message.Set_Name('IGS',v_message_name);
132 IGS_GE_MSG_STACK.ADD;
133 App_Exception.Raise_Exception;
134 END IF;
135 END IF;
136
137
138 END BeforeRowInsertUpdate1;
139
140 PROCEDURE Check_Constraints (
141 Column_Name IN VARCHAR2 DEFAULT NULL,
142 Column_Value IN VARCHAR2 DEFAULT NULL
143 )
144 AS
145 BEGIN
146 IF column_name is null THEN
147 NULL;
148 ELSIF upper(column_name) = 'ONLY_AS_SUB_IND' THEN
149 new_references.only_as_sub_ind := column_value;
150 ELSIF upper(column_name) = 'SHOW_ON_OFFICIAL_NTFCTN_IND' THEN
151 new_references.show_on_official_ntfctn_ind := column_value;
152 ELSIF upper(column_name) = 'CAL_TYPE' THEN
153 new_references.cal_type:= column_value;
154 ELSIF upper(column_name) = 'COURSE_CD' THEN
155 new_references.course_cd := column_value;
156 ELSIF upper(column_name) = 'UNIT_SET_CD' THEN
157 new_references.unit_set_cd:= column_value;
158 END IF;
159
160 IF upper(column_name)= 'CAL_TYPE' OR
161 column_name is null THEN
162 IF new_references.cal_type <> UPPER(new_references.cal_type)
163 THEN
164 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
165 IGS_GE_MSG_STACK.ADD;
166 App_Exception.Raise_Exception;
167 END IF;
168 END IF;
169
170 IF upper(column_name)= 'COURSE_CD' OR
171 column_name is null THEN
172 IF new_references.course_cd <> UPPER(new_references.course_cd)
173 THEN
174 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
175 IGS_GE_MSG_STACK.ADD;
176 App_Exception.Raise_Exception;
177 END IF;
178 END IF;
179
180 IF upper(column_name)= 'UNIT_SET_CD' OR
181 column_name is null THEN
182 IF new_references.unit_set_cd <> UPPER(new_references.unit_set_cd)
183 THEN
184 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
185 IGS_GE_MSG_STACK.ADD;
186 App_Exception.Raise_Exception;
187 END IF;
188 END IF;
189
190 IF upper(column_name)= 'ONLY_AS_SUB_IND' OR
191 column_name is null THEN
192 IF new_references.only_as_sub_ind NOT IN ( 'Y' , 'N' )
193 THEN
194 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
195 IGS_GE_MSG_STACK.ADD;
196 App_Exception.Raise_Exception;
197 END IF;
198 END IF;
199
200 IF upper(column_name)= 'SHOW_ON_OFFICIAL_NTFCTN_IND' OR
201 column_name is null THEN
202 IF new_references.show_on_official_ntfctn_ind NOT IN ( 'Y' , 'N' )
203 THEN
204 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
205 IGS_GE_MSG_STACK.ADD;
206 App_Exception.Raise_Exception;
207 END IF;
208 END IF;
209 END Check_Constraints;
210
211
212 PROCEDURE Check_Parent_Existance AS
213 BEGIN
214
215 IF (((old_references.course_cd = new_references.course_cd) AND
216 (old_references.crv_version_number = new_references.crv_version_number) AND
217 (old_references.cal_type = new_references.cal_type)) OR
218 ((new_references.course_cd IS NULL) OR
219 (new_references.crv_version_number IS NULL) OR
220 (new_references.cal_type IS NULL))) THEN
221 NULL;
222 ELSE
223 IF NOT IGS_PS_OFR_PKG.Get_PK_For_Validation (
224 new_references.course_cd,
225 new_references.crv_version_number,
226 new_references.cal_type
227 )THEN
228 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
229 IGS_GE_MSG_STACK.ADD;
230 App_Exception.Raise_Exception;
231 END IF;
232 END IF;
233
234 IF (((old_references.unit_set_cd = new_references.unit_set_cd) AND
235 (old_references.us_version_number = new_references.us_version_number)) OR
236 ((new_references.unit_set_cd IS NULL) OR
237 (new_references.us_version_number IS NULL))) THEN
238 NULL;
239 ELSE
240 IF NOT IGS_EN_UNIT_SET_PKG.Get_PK_For_Validation (
241 new_references.unit_set_cd,
242 new_references.us_version_number
243 )THEN
244 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
245 IGS_GE_MSG_STACK.ADD;
246 App_Exception.Raise_Exception;
247 END IF;
248 END IF;
249
250 END Check_Parent_Existance;
251
252 PROCEDURE Check_Child_Existance AS
253 /*************************************************************
254 Created By :sarakshi
255 Date Created By :27-APR-2006
256 Purpose :
257 Know limitations, enhancements or remarks
258 Change History
259 Who When What
260
261 (reverse chronological order - newest change first)
262 sarakshi 27-APR-2006 Bug#5165619, added child existance for IGS_PS_ENT_PT_REF_CD and IGS_PS_COO_AD_UNIT_S
263 ***************************************************************/
264 BEGIN
265
266 IGS_PS_OF_OPT_UNT_ST_PKG.GET_FK_IGS_PS_OFR_UNIT_SET (
267 old_references.course_cd,
268 old_references.crv_version_number,
269 old_references.cal_type,
270 old_references.unit_set_cd,
271 old_references.us_version_number
272 );
273 IGS_PS_OF_UNT_SET_RL_PKG.GET_FK_IGS_PS_OFR_UNIT_SET (
274 old_references.course_cd,
275 old_references.crv_version_number,
276 old_references.cal_type,
277 old_references.unit_set_cd,
278 old_references.us_version_number
279 );
280
281 --Added following child table check for bug#5165619
282 --IGS_PS_ENT_PT_REF_CD (program entry point reference codes)
283 IGS_PS_ENT_PT_REF_CD_PKG.GET_FK_IGS_PS_OFR_UNIT_SET (
284 old_references.course_cd,
285 old_references.crv_version_number,
286 old_references.cal_type,
287 old_references.unit_set_cd,
288 old_references.us_version_number
289 );
290
291
292 --IGS_PS_COO_AD_UNIT_S (Program Offering option admission categories)
293 IGS_PS_COO_AD_UNIT_S_PKG.GET_FK_IGS_PS_OFR_UNIT_SET (
294 old_references.course_cd,
295 old_references.crv_version_number,
296 old_references.cal_type,
297 old_references.unit_set_cd,
298 old_references.us_version_number
299 );
300
301 --IGS_HE_POOUS_ALL
302 IGS_HE_POOUS_ALL_PKG.GET_FK_IGS_PS_OFR_UNIT_SET (
303 old_references.course_cd,
304 old_references.crv_version_number,
305 old_references.cal_type,
306 old_references.unit_set_cd,
307 old_references.us_version_number
308 );
309
310 --IGS_HE_POOUS_OU
311 IGS_HE_POOUS_OU_ALL_PKG.GET_FK_IGS_PS_OFR_UNIT_SET (
312 old_references.course_cd,
313 old_references.crv_version_number,
314 old_references.cal_type,
315 old_references.unit_set_cd,
316 old_references.us_version_number
317 );
318
319 --IGS_HE_POOUS_OU
320 IGS_AS_SU_SETATMPT_PKG.GET_FK_IGS_PS_OFR_UNIT_SET (
321 old_references.course_cd,
322 old_references.crv_version_number,
323 old_references.cal_type,
324 old_references.unit_set_cd,
325 old_references.us_version_number
326 );
327
328
329
330 END Check_Child_Existance;
331
332 FUNCTION Get_PK_For_Validation (
333 x_course_cd IN VARCHAR2,
334 x_crv_version_number IN NUMBER,
335 x_cal_type IN VARCHAR2,
336 x_unit_set_cd IN VARCHAR2,
337 x_us_version_number IN NUMBER
338 ) RETURN BOOLEAN AS
339
340 CURSOR cur_rowid IS
341 SELECT rowid
342 FROM IGS_PS_OFR_UNIT_SET
343 WHERE course_cd = x_course_cd
344 AND crv_version_number = x_crv_version_number
345 AND cal_type = x_cal_type
346 AND unit_set_cd = x_unit_set_cd
347 AND us_version_number = x_us_version_number
348 FOR UPDATE NOWAIT;
349
350 lv_rowid cur_rowid%RowType;
351
352 BEGIN
353
354 Open cur_rowid;
355 Fetch cur_rowid INTO lv_rowid;
356 IF (cur_rowid%FOUND) THEN
357 Close cur_rowid;
358 Return(TRUE);
359 ELSE
360 Close cur_rowid;
361 Return(FALSE);
362 END IF;
363
364 END Get_PK_For_Validation;
365
366 PROCEDURE GET_FK_IGS_PS_OFR (
367 x_course_cd IN VARCHAR2,
368 x_version_number IN NUMBER,
369 x_cal_type IN VARCHAR2
370 ) AS
371
372 CURSOR cur_rowid IS
373 SELECT rowid
374 FROM IGS_PS_OFR_UNIT_SET
375 WHERE course_cd = x_course_cd
376 AND crv_version_number = x_version_number
377 AND cal_type = x_cal_type ;
378
379 lv_rowid cur_rowid%RowType;
380
381 BEGIN
382
383 Open cur_rowid;
384 Fetch cur_rowid INTO lv_rowid;
385 IF (cur_rowid%FOUND) THEN
386 Close cur_rowid;
387 Fnd_Message.Set_Name ('IGS', 'IGS_PS_COUS_CO_FK');
388 IGS_GE_MSG_STACK.ADD;
389 App_Exception.Raise_Exception;
390 Return;
391 END IF;
392 Close cur_rowid;
393
394 END GET_FK_IGS_PS_OFR;
395
396 PROCEDURE GET_FK_IGS_EN_UNIT_SET (
397 x_unit_set_cd IN VARCHAR2,
398 x_version_number IN NUMBER
399 ) AS
400
401 CURSOR cur_rowid IS
402 SELECT rowid
403 FROM IGS_PS_OFR_UNIT_SET
404 WHERE unit_set_cd = x_unit_set_cd
405 AND us_version_number = x_version_number ;
406
407 lv_rowid cur_rowid%RowType;
408
409 BEGIN
410
411 Open cur_rowid;
412 Fetch cur_rowid INTO lv_rowid;
413 IF (cur_rowid%FOUND) THEN
414 Close cur_rowid;
415 Fnd_Message.Set_Name ('IGS', 'IGS_PS_COUS_US_FK');
416 IGS_GE_MSG_STACK.ADD;
417 App_Exception.Raise_Exception;
418 Return;
419 END IF;
420 Close cur_rowid;
421
422 END GET_FK_IGS_EN_UNIT_SET;
423
424 PROCEDURE Before_DML (
425 p_action IN VARCHAR2,
426 x_rowid IN VARCHAR2 DEFAULT NULL,
427 x_course_cd IN VARCHAR2 DEFAULT NULL,
428 x_crv_version_number IN NUMBER DEFAULT NULL,
429 x_cal_type IN VARCHAR2 DEFAULT NULL,
430 x_unit_set_cd IN VARCHAR2 DEFAULT NULL,
431 x_us_version_number IN NUMBER DEFAULT NULL,
432 x_override_title IN VARCHAR2 DEFAULT NULL,
433 x_only_as_sub_ind IN VARCHAR2 DEFAULT NULL,
434 x_show_on_official_ntfctn_ind IN VARCHAR2 DEFAULT NULL,
435 x_creation_date IN DATE DEFAULT NULL,
436 x_created_by IN NUMBER DEFAULT NULL,
437 x_last_update_date IN DATE DEFAULT NULL,
438 x_last_updated_by IN NUMBER DEFAULT NULL,
439 x_last_update_login IN NUMBER DEFAULT NULL
440 ) AS
441 BEGIN
442
443 Set_Column_Values (
444 p_action,
445 x_rowid,
446 x_course_cd,
447 x_crv_version_number,
448 x_cal_type,
449 x_unit_set_cd,
450 x_us_version_number,
451 x_override_title,
452 x_only_as_sub_ind,
453 x_show_on_official_ntfctn_ind,
454 x_creation_date,
455 x_created_by,
456 x_last_update_date,
457 x_last_updated_by,
458 x_last_update_login
459 );
460
461 IF (p_action = 'INSERT') THEN
462 -- Call all the procedures related to Before Insert.
463 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
464 IF Get_PK_For_Validation(
465 new_references.course_cd ,
466 new_references.crv_version_number ,
467 new_references.cal_type ,
468 new_references.unit_set_cd ,
469 new_references.us_version_number
470 ) THEN
471 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
472 IGS_GE_MSG_STACK.ADD;
473 App_Exception.Raise_Exception;
474 END IF;
475 Check_Constraints;
476 Check_Parent_Existance;
477 ELSIF (p_action = 'UPDATE') THEN
478 -- Call all the procedures related to Before Update.
479 BeforeRowInsertUpdate1 ( p_updating => TRUE );
480 Check_Constraints;
481 Check_Parent_Existance;
482 ELSIF (p_action = 'DELETE') THEN
483 -- Call all the procedures related to Before Delete.
484 BeforeRowInsertUpdate1 ( p_deleting => TRUE );
485 Check_Child_Existance;
486 ELSIF (p_action = 'VALIDATE_INSERT') THEN
487 IF Get_PK_For_Validation(
488 new_references.course_cd ,
489 new_references.crv_version_number ,
490 new_references.cal_type ,
491 new_references.unit_set_cd ,
492 new_references.us_version_number
493 ) THEN
494 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
495 IGS_GE_MSG_STACK.ADD;
496 App_Exception.Raise_Exception;
497 END IF;
498 Check_Constraints;
499 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
500 Check_Constraints;
501 ELSIF (p_action = 'VALIDATE_DELETE') THEN
502 Check_Child_Existance;
503 END IF;
504
505
506 END Before_DML;
507
508 PROCEDURE After_DML (
509 p_action IN VARCHAR2,
510 x_rowid IN VARCHAR2
511 ) AS
512 BEGIN
513
514 l_rowid := x_rowid;
515
516
517 END After_DML;
518
519 procedure INSERT_ROW (
520 X_ROWID in out NOCOPY VARCHAR2,
521 X_COURSE_CD in VARCHAR2,
522 X_CRV_VERSION_NUMBER in NUMBER,
523 X_CAL_TYPE in VARCHAR2,
524 X_UNIT_SET_CD in VARCHAR2,
525 X_US_VERSION_NUMBER in NUMBER,
526 X_OVERRIDE_TITLE in VARCHAR2,
527 X_ONLY_AS_SUB_IND in VARCHAR2,
528 X_SHOW_ON_OFFICIAL_NTFCTN_IND in VARCHAR2,
529 X_MODE in VARCHAR2 default 'R'
530 ) AS
531 cursor C is select ROWID from IGS_PS_OFR_UNIT_SET
532 where COURSE_CD = X_COURSE_CD
533 and CRV_VERSION_NUMBER = X_CRV_VERSION_NUMBER
534 and CAL_TYPE = X_CAL_TYPE
535 and UNIT_SET_CD = X_UNIT_SET_CD
536 and US_VERSION_NUMBER = X_US_VERSION_NUMBER;
537 X_LAST_UPDATE_DATE DATE;
538 X_LAST_UPDATED_BY NUMBER;
539 X_LAST_UPDATE_LOGIN NUMBER;
540 begin
541 X_LAST_UPDATE_DATE := SYSDATE;
542 if(X_MODE = 'I') then
543 X_LAST_UPDATED_BY := 1;
544 X_LAST_UPDATE_LOGIN := 0;
545 elsif (X_MODE = 'R') then
546 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
547 if X_LAST_UPDATED_BY is NULL then
548 X_LAST_UPDATED_BY := -1;
549 end if;
550 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
551 if X_LAST_UPDATE_LOGIN is NULL then
552 X_LAST_UPDATE_LOGIN := -1;
553 end if;
554 else
555 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
556 IGS_GE_MSG_STACK.ADD;
557 app_exception.raise_exception;
558 end if;
559 Before_DML (
560 p_action => 'INSERT',
561 x_rowid => X_ROWID,
562 x_course_cd => X_COURSE_CD,
563 x_crv_version_number => X_CRV_VERSION_NUMBER,
564 x_cal_type => X_CAL_TYPE,
565 x_unit_set_cd => X_UNIT_SET_CD,
566 x_us_version_number => X_US_VERSION_NUMBER,
567 x_override_title => X_OVERRIDE_TITLE,
568 x_only_as_sub_ind => NVL(X_ONLY_AS_SUB_IND,'N'),
569 x_show_on_official_ntfctn_ind => NVL(X_SHOW_ON_OFFICIAL_NTFCTN_IND,'Y'),
570 x_creation_date => X_LAST_UPDATE_DATE ,
571 x_created_by => X_LAST_UPDATED_BY ,
572 x_last_update_date => X_LAST_UPDATE_DATE ,
573 x_last_updated_by => X_LAST_UPDATED_BY ,
574 x_last_update_login => X_LAST_UPDATE_LOGIN
575 );
576
577 insert into IGS_PS_OFR_UNIT_SET (
578 COURSE_CD,
579 CRV_VERSION_NUMBER,
580 CAL_TYPE,
581 UNIT_SET_CD,
582 US_VERSION_NUMBER,
583 OVERRIDE_TITLE,
584 ONLY_AS_SUB_IND,
585 SHOW_ON_OFFICIAL_NTFCTN_IND,
586 CREATION_DATE,
587 CREATED_BY,
588 LAST_UPDATE_DATE,
589 LAST_UPDATED_BY,
590 LAST_UPDATE_LOGIN
591 ) values (
592 NEW_REFERENCES.COURSE_CD,
593 NEW_REFERENCES.CRV_VERSION_NUMBER,
594 NEW_REFERENCES.CAL_TYPE,
595 NEW_REFERENCES.UNIT_SET_CD,
596 NEW_REFERENCES.US_VERSION_NUMBER,
597 NEW_REFERENCES.OVERRIDE_TITLE,
598 NEW_REFERENCES.ONLY_AS_SUB_IND,
599 NEW_REFERENCES.SHOW_ON_OFFICIAL_NTFCTN_IND,
600 X_LAST_UPDATE_DATE,
601 X_LAST_UPDATED_BY,
602 X_LAST_UPDATE_DATE,
603 X_LAST_UPDATED_BY,
604 X_LAST_UPDATE_LOGIN
605 );
606
607 open c;
608 fetch c into X_ROWID;
609 if (c%notfound) then
610 close c;
611 raise no_data_found;
612 end if;
613 close c;
614 After_DML (
615 p_action => 'INSERT',
616 x_rowid => X_ROWID
617 );
618 end INSERT_ROW;
619
620 procedure LOCK_ROW (
621 X_ROWID IN VARCHAR2,
622 X_COURSE_CD in VARCHAR2,
623 X_CRV_VERSION_NUMBER in NUMBER,
624 X_CAL_TYPE in VARCHAR2,
625 X_UNIT_SET_CD in VARCHAR2,
626 X_US_VERSION_NUMBER in NUMBER,
627 X_OVERRIDE_TITLE in VARCHAR2,
628 X_ONLY_AS_SUB_IND in VARCHAR2,
629 X_SHOW_ON_OFFICIAL_NTFCTN_IND in VARCHAR2
630 ) AS
631 cursor c1 is select
632 OVERRIDE_TITLE,
633 ONLY_AS_SUB_IND,
634 SHOW_ON_OFFICIAL_NTFCTN_IND
635 from IGS_PS_OFR_UNIT_SET
636 where ROWID = X_ROWID
637 for update nowait;
638 tlinfo c1%rowtype;
639
640 begin
641 open c1;
642 fetch c1 into tlinfo;
643 if (c1%notfound) then
644 close c1;
645 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
646 IGS_GE_MSG_STACK.ADD;
647 app_exception.raise_exception;
648 return;
649 end if;
650 close c1;
651
652 if ( ((tlinfo.OVERRIDE_TITLE = X_OVERRIDE_TITLE)
653 OR ((tlinfo.OVERRIDE_TITLE is null)
654 AND (X_OVERRIDE_TITLE is null)))
655 AND (tlinfo.ONLY_AS_SUB_IND = X_ONLY_AS_SUB_IND)
656 AND (tlinfo.SHOW_ON_OFFICIAL_NTFCTN_IND = X_SHOW_ON_OFFICIAL_NTFCTN_IND)
657 ) then
658 null;
659 else
660 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
661 IGS_GE_MSG_STACK.ADD;
662 app_exception.raise_exception;
663 end if;
664 return;
665 end LOCK_ROW;
666
667 procedure UPDATE_ROW (
668 X_ROWID IN VARCHAR2,
669 X_COURSE_CD in VARCHAR2,
670 X_CRV_VERSION_NUMBER in NUMBER,
671 X_CAL_TYPE in VARCHAR2,
672 X_UNIT_SET_CD in VARCHAR2,
673 X_US_VERSION_NUMBER in NUMBER,
674 X_OVERRIDE_TITLE in VARCHAR2,
675 X_ONLY_AS_SUB_IND in VARCHAR2,
676 X_SHOW_ON_OFFICIAL_NTFCTN_IND in VARCHAR2,
677 X_MODE in VARCHAR2 default 'R'
678 ) AS
679 X_LAST_UPDATE_DATE DATE;
680 X_LAST_UPDATED_BY NUMBER;
681 X_LAST_UPDATE_LOGIN NUMBER;
682 begin
683 X_LAST_UPDATE_DATE := SYSDATE;
684 if(X_MODE = 'I') then
685 X_LAST_UPDATED_BY := 1;
686 X_LAST_UPDATE_LOGIN := 0;
687 elsif (X_MODE = 'R') then
688 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
689 if X_LAST_UPDATED_BY is NULL then
690 X_LAST_UPDATED_BY := -1;
691 end if;
692 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
693 if X_LAST_UPDATE_LOGIN is NULL then
694 X_LAST_UPDATE_LOGIN := -1;
695 end if;
696 else
697 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
698 IGS_GE_MSG_STACK.ADD;
699 app_exception.raise_exception;
700 end if;
701 Before_DML (
702 p_action => 'UPDATE',
703 x_rowid => X_ROWID,
704 x_course_cd => X_COURSE_CD,
705 x_crv_version_number => X_CRV_VERSION_NUMBER,
706 x_cal_type => X_CAL_TYPE,
707 x_unit_set_cd => X_UNIT_SET_CD,
708 x_us_version_number => X_US_VERSION_NUMBER,
709 x_override_title => X_OVERRIDE_TITLE,
710 x_only_as_sub_ind => X_ONLY_AS_SUB_IND,
711 x_show_on_official_ntfctn_ind => X_SHOW_ON_OFFICIAL_NTFCTN_IND ,
712 x_creation_date => X_LAST_UPDATE_DATE ,
713 x_created_by => X_LAST_UPDATED_BY ,
714 x_last_update_date => X_LAST_UPDATE_DATE ,
715 x_last_updated_by => X_LAST_UPDATED_BY ,
716 x_last_update_login => X_LAST_UPDATE_LOGIN
717 );
718 update IGS_PS_OFR_UNIT_SET set
719 OVERRIDE_TITLE = NEW_REFERENCES.OVERRIDE_TITLE,
720 ONLY_AS_SUB_IND = NEW_REFERENCES.ONLY_AS_SUB_IND,
721 SHOW_ON_OFFICIAL_NTFCTN_IND = NEW_REFERENCES.SHOW_ON_OFFICIAL_NTFCTN_IND,
722 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
723 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
724 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
725 where ROWID = X_ROWID
726 ;
727 if (sql%notfound) then
728 raise no_data_found;
729 end if;
730 After_DML (
731 p_action => 'UPDATE',
732 x_rowid => X_ROWID
733 );
734 end UPDATE_ROW;
735
736 procedure ADD_ROW (
737 X_ROWID in out NOCOPY VARCHAR2,
738 X_COURSE_CD in VARCHAR2,
739 X_CRV_VERSION_NUMBER in NUMBER,
740 X_CAL_TYPE in VARCHAR2,
741 X_UNIT_SET_CD in VARCHAR2,
742 X_US_VERSION_NUMBER in NUMBER,
743 X_OVERRIDE_TITLE in VARCHAR2,
744 X_ONLY_AS_SUB_IND in VARCHAR2,
745 X_SHOW_ON_OFFICIAL_NTFCTN_IND in VARCHAR2,
746 X_MODE in VARCHAR2 default 'R'
747 ) AS
748 cursor c1 is select rowid from IGS_PS_OFR_UNIT_SET
749 where COURSE_CD = X_COURSE_CD
750 and CRV_VERSION_NUMBER = X_CRV_VERSION_NUMBER
751 and CAL_TYPE = X_CAL_TYPE
752 and UNIT_SET_CD = X_UNIT_SET_CD
753 and US_VERSION_NUMBER = X_US_VERSION_NUMBER
754 ;
755 begin
756 open c1;
757 fetch c1 into X_ROWID;
758 if (c1%notfound) then
759 close c1;
760 INSERT_ROW (
761 X_ROWID,
762 X_COURSE_CD,
763 X_CRV_VERSION_NUMBER,
764 X_CAL_TYPE,
765 X_UNIT_SET_CD,
766 X_US_VERSION_NUMBER,
767 X_OVERRIDE_TITLE,
768 X_ONLY_AS_SUB_IND,
769 X_SHOW_ON_OFFICIAL_NTFCTN_IND,
770 X_MODE);
771 return;
772 end if;
773 close c1;
774 UPDATE_ROW (
775 X_ROWID,
776 X_COURSE_CD,
777 X_CRV_VERSION_NUMBER,
778 X_CAL_TYPE,
779 X_UNIT_SET_CD,
780 X_US_VERSION_NUMBER,
781 X_OVERRIDE_TITLE,
782 X_ONLY_AS_SUB_IND,
783 X_SHOW_ON_OFFICIAL_NTFCTN_IND,
784 X_MODE);
785 end ADD_ROW;
786
787 procedure DELETE_ROW (
788 X_ROWID in VARCHAR2
789 ) AS
790 begin
791 Before_DML (
792 p_action => 'DELETE',
793 x_rowid => X_ROWID
794 );
795 delete from IGS_PS_OFR_UNIT_SET
796 where ROWID = X_ROWID;
797 if (sql%notfound) then
798 raise no_data_found;
799 end if;
800 After_DML (
801 p_action => 'DELETE',
802 x_rowid => X_ROWID
803 );
804 end DELETE_ROW;
805
806 end IGS_PS_OFR_UNIT_SET_PKG;