[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_GRP_PKG
Source
1 package body IGS_PS_GRP_PKG AS
2 /* $Header: IGSPI15B.pls 115.9 2003/02/20 10:03:27 shtatiko ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_GRP_ALL%RowType;
6 new_references IGS_PS_GRP_ALL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_course_group_cd IN VARCHAR2 DEFAULT NULL,
12 x_description IN VARCHAR2 DEFAULT NULL,
13 x_responsible_org_unit_cd IN VARCHAR2 DEFAULT NULL,
14 x_responsible_ou_start_dt IN DATE DEFAULT NULL,
15 x_course_group_type IN VARCHAR2 DEFAULT NULL,
16 x_closed_ind IN VARCHAR2 DEFAULT NULL,
17 x_creation_date IN DATE DEFAULT NULL,
18 x_created_by IN NUMBER DEFAULT NULL,
19 x_last_update_date IN DATE DEFAULT NULL,
20 x_last_updated_by IN NUMBER DEFAULT NULL,
21 x_last_update_login IN NUMBER DEFAULT NULL,
22 x_ORG_ID IN NUMBER DEFAULT NULL
23 ) AS
24
25 CURSOR cur_old_ref_values IS
26 SELECT *
27 FROM IGS_PS_GRP_ALL
28 WHERE rowid = x_rowid;
29
30 BEGIN
31
32 l_rowid := x_rowid;
33
34 -- Code for setting the Old and New Reference Values.
35 -- Populate Old Values.
36 Open cur_old_ref_values;
37 Fetch cur_old_ref_values INTO old_references;
38 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
39 Close cur_old_ref_values;
40 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
41 IGS_GE_MSG_STACK.ADD;
42 App_Exception.Raise_Exception;
43 Return;
44 END IF;
45 Close cur_old_ref_values;
46
47 -- Populate New Values.
48 new_references.course_group_cd := x_course_group_cd;
49 new_references.description := x_description;
50 new_references.responsible_org_unit_cd := x_responsible_org_unit_cd;
51 new_references.responsible_ou_start_dt := x_responsible_ou_start_dt;
52 new_references.course_group_type := x_course_group_type;
53 new_references.closed_ind := x_closed_ind;
54 new_references.org_id:=x_org_id;
55 IF (p_action = 'UPDATE') THEN
56 new_references.creation_date := old_references.creation_date;
57 new_references.created_by := old_references.created_by;
58 ELSE
59 new_references.creation_date := x_creation_date;
60 new_references.created_by := x_created_by;
61 END IF;
62 new_references.last_update_date := x_last_update_date;
63 new_references.last_updated_by := x_last_updated_by;
64 new_references.last_update_login := x_last_update_login;
65
66 END Set_Column_Values;
67
68 -- Trigger description :-
69 -- "OSS_TST".trg_cgr_br_iu
70 -- BEFORE INSERT OR UPDATE
71 -- ON IGS_PS_GRP
72 -- FOR EACH ROW
73
74 PROCEDURE BeforeRowInsertUpdate1(
75 p_inserting IN BOOLEAN DEFAULT FALSE,
76 p_updating IN BOOLEAN DEFAULT FALSE,
77 p_deleting IN BOOLEAN DEFAULT FALSE
78 ) AS
79 v_message_name varchar2(30);
80 BEGIN
81 -- Validate IGS_PS_COURSE group type.
82 IF p_inserting OR
83 (p_updating AND (old_references.course_group_type <> new_references.course_group_type)) THEN
84 IF IGS_PS_VAL_CGR.crsp_val_cgr_type (
85 new_references.course_group_type,
86 v_message_name) = FALSE THEN
87 Fnd_Message.Set_Name('IGS',v_message_name);
88 IGS_GE_MSG_STACK.ADD;
89 App_Exception.Raise_Exception;
90 END IF;
91 END IF;
92 -- Validate the responsible org IGS_PS_UNIT.
93 IF p_inserting OR
94 (p_updating AND
95 ((NVL(old_references.responsible_org_unit_cd, 'NULL') <>
96 NVL(new_references.responsible_org_unit_cd, 'NULL')) OR
97 (NVL(SUBSTR(old_references.responsible_ou_start_dt,1,10),'1900/01/01') <>
98 NVL(SUBSTR(new_references.responsible_ou_start_dt,1,10),'1900/01/01')))) THEN
99 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_CGR.crsp_val_ou_sys_sts
100 IF IGS_PS_VAL_CRV.crsp_val_ou_sys_sts (
101 new_references.responsible_org_unit_cd,
102 new_references.responsible_ou_start_dt,
103 v_message_name) = FALSE THEN
104 Fnd_Message.Set_Name('IGS',v_message_name);
105 IGS_GE_MSG_STACK.ADD;
106 App_Exception.Raise_Exception;
107 END IF;
108 END IF;
109
110
111 END BeforeRowInsertUpdate1;
112
113 PROCEDURE Check_Constraints (
114 Column_Name IN VARCHAR2 DEFAULT NULL,
115 Column_Value IN VARCHAR2 DEFAULT NULL
116 )
117 AS
118 BEGIN
119
120 IF column_name is null then
121 NULL;
122 ELSIF upper(Column_name) = 'CLOSED_IND' then
123 new_references.closed_ind := column_value;
124 ELSIF upper(Column_name) = 'COURSE_GROUP_CD' then
125 new_references.course_group_cd := column_value;
126 ELSIF upper(Column_name) = 'COURSE_GROUP_TYPE' then
127 new_references.course_group_type := column_value;
128 END IF;
129
130 IF upper(column_name) = 'CLOSED_IND' OR
131 column_name is null Then
132 IF ( new_references.closed_ind NOT IN ( 'Y' , 'N' )) Then
133 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
134 IGS_GE_MSG_STACK.ADD;
135 App_Exception.Raise_Exception;
136 END IF;
137 END IF;
138
139 IF upper(column_name) = 'COURSE_GROUP_CD' OR
140 column_name is null Then
141 IF ( new_references.course_group_cd <> UPPER(new_references.course_group_cd) ) Then
142 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
143 IGS_GE_MSG_STACK.ADD;
144 App_Exception.Raise_Exception;
145 END IF;
146 END IF;
147
148 IF upper(column_name) = 'COURSE_GROUP_TYPE' OR
149 column_name is null Then
150 IF ( new_references.course_group_type <> UPPER(new_references.course_group_type) ) Then
151 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
152 IGS_GE_MSG_STACK.ADD;
153 App_Exception.Raise_Exception;
154 END IF;
155 END IF;
156
157 END Check_Constraints;
158
159 PROCEDURE Check_Parent_Existance AS
160 BEGIN
161
162 IF (((old_references.course_group_type = new_references.course_group_type)) OR
163 ((new_references.course_group_type IS NULL))) THEN
164 NULL;
165 ELSE
166 IF NOT IGS_PS_GRP_TYPE_PKG.Get_PK_For_Validation (
167 new_references.course_group_type
168 ) THEN
169 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
170 IGS_GE_MSG_STACK.ADD;
171 App_Exception.Raise_Exception;
172 END IF;
173 END IF;
174
175 IF (((old_references.responsible_org_unit_cd = new_references.responsible_org_unit_cd) AND
176 (old_references.responsible_ou_start_dt = new_references.responsible_ou_start_dt)) OR
177 ((new_references.responsible_org_unit_cd IS NULL) OR
178 (new_references.responsible_ou_start_dt IS NULL))) THEN
179 NULL;
180 ELSE
181 IF NOT IGS_OR_UNIT_PKG.Get_PK_For_Validation (
182 new_references.responsible_org_unit_cd,
183 new_references.responsible_ou_start_dt
184 ) THEN
185 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
186 IGS_GE_MSG_STACK.ADD;
187 App_Exception.Raise_Exception;
188 END IF;
189 END IF;
190
191 END Check_Parent_Existance;
192
193 FUNCTION Get_PK_For_Validation (
194 x_course_group_cd IN VARCHAR2
195 )
196 RETURN BOOLEAN AS
197
198 -- Removed FOR UPDATE NOWAIT clause from the following cursor, Enh# 2797116.
199
200 CURSOR cur_rowid IS
201 SELECT rowid
202 FROM IGS_PS_GRP_ALL
203 WHERE course_group_cd = x_course_group_cd;
204
205 lv_rowid cur_rowid%RowType;
206
207 BEGIN
208
209 Open cur_rowid;
210 Fetch cur_rowid INTO lv_rowid;
211 IF (cur_rowid%FOUND) THEN
212 Close cur_rowid;
213 Return (TRUE);
214 ELSE
215 Close cur_rowid;
216 Return (FALSE);
217 END IF;
218
219 END Get_PK_For_Validation;
220
221 PROCEDURE GET_FK_IGS_PS_GRP_TYPE (
222 x_course_group_type IN VARCHAR2
223 ) AS
224
225 CURSOR cur_rowid IS
226 SELECT rowid
227 FROM IGS_PS_GRP_ALL
228 WHERE course_group_type = x_course_group_type ;
229
230 lv_rowid cur_rowid%RowType;
231
232 BEGIN
233
234 Open cur_rowid;
235 Fetch cur_rowid INTO lv_rowid;
236 IF (cur_rowid%FOUND) THEN
237 Close cur_rowid;
238 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CGR_CGT_FK');
239 IGS_GE_MSG_STACK.ADD;
240 App_Exception.Raise_Exception;
241 Return;
242 END IF;
243 Close cur_rowid;
244
245 END GET_FK_IGS_PS_GRP_TYPE;
246
247 PROCEDURE GET_FK_IGS_OR_UNIT (
248 x_org_unit_cd IN VARCHAR2,
249 x_start_dt IN VARCHAR2
250 ) AS
251
252 CURSOR cur_rowid IS
253 SELECT rowid
254 FROM IGS_PS_GRP_ALL
255 WHERE responsible_org_unit_cd = x_org_unit_cd
256 AND responsible_ou_start_dt = x_start_dt ;
257
258 lv_rowid cur_rowid%RowType;
259
260 BEGIN
261
262 Open cur_rowid;
263 Fetch cur_rowid INTO lv_rowid;
264 IF (cur_rowid%FOUND) THEN
265 Close cur_rowid;
266 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CGR_OU_FK');
267 IGS_GE_MSG_STACK.ADD;
268 App_Exception.Raise_Exception;
269 Return;
270 END IF;
271 Close cur_rowid;
272
273 END GET_FK_IGS_OR_UNIT;
274
275 PROCEDURE Before_DML (
276 p_action IN VARCHAR2,
277 x_rowid IN VARCHAR2 DEFAULT NULL,
278 x_course_group_cd IN VARCHAR2 DEFAULT NULL,
279 x_description IN VARCHAR2 DEFAULT NULL,
280 x_responsible_org_unit_cd IN VARCHAR2 DEFAULT NULL,
281 x_responsible_ou_start_dt IN DATE DEFAULT NULL,
282 x_course_group_type IN VARCHAR2 DEFAULT NULL,
283 x_closed_ind IN VARCHAR2 DEFAULT NULL,
284 x_creation_date IN DATE DEFAULT NULL,
285 x_created_by IN NUMBER DEFAULT NULL,
286 x_last_update_date IN DATE DEFAULT NULL,
287 x_last_updated_by IN NUMBER DEFAULT NULL,
288 x_last_update_login IN NUMBER DEFAULT NULL,
289 x_org_id in NUMBER DEFAULT NULL
290 ) AS
291 /*****************************************************************************
292 WHO WHEN WHAT
293 shtatiko 18-FEB-2003 Enh# 2797116, Removed cases of p_action = 'DELETE'
297
294 and 'VALIDATE_DELETE'.
295 *****************************************************************************/
296 BEGIN
298 Set_Column_Values (
299 p_action,
300 x_rowid,
301 x_course_group_cd,
302 x_description,
303 x_responsible_org_unit_cd,
304 x_responsible_ou_start_dt,
305 x_course_group_type,
306 x_closed_ind,
307 x_creation_date,
308 x_created_by,
309 x_last_update_date,
310 x_last_updated_by,
311 x_last_update_login,
312 x_org_id
313 );
314
315 IF (p_action = 'INSERT') THEN
316 -- Call all the procedures related to Before Insert.
317 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
318 IF Get_PK_For_Validation (
319 new_references.course_group_cd) THEN
320 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
321 IGS_GE_MSG_STACK.ADD;
322 App_Exception.Raise_Exception;
323 END IF;
324 Check_Constraints;
325 Check_Parent_Existance;
326 ELSIF (p_action = 'UPDATE') THEN
327 -- Call all the procedures related to Before Update.
328 BeforeRowInsertUpdate1 ( p_updating => TRUE );
329 Check_Constraints;
330 Check_Parent_Existance;
331 ELSIF (p_action = 'VALIDATE_INSERT') THEN
332 IF Get_PK_For_Validation (
333 new_references.course_group_cd) THEN
334 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
335 IGS_GE_MSG_STACK.ADD;
336 App_Exception.Raise_Exception;
337 END IF;
338 Check_Constraints;
339 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
340 Check_Constraints;
341 END IF;
342
343 END Before_DML;
344
345 PROCEDURE After_DML (
346 p_action IN VARCHAR2,
347 x_rowid IN VARCHAR2
348 ) AS
349 BEGIN
350
351 l_rowid := x_rowid;
352
353
354 END After_DML;
355
356 procedure INSERT_ROW (
357 X_ROWID in out NOCOPY VARCHAR2,
358 X_COURSE_GROUP_CD in VARCHAR2,
359 X_DESCRIPTION in VARCHAR2,
360 X_RESPONSIBLE_ORG_UNIT_CD in VARCHAR2,
361 X_RESPONSIBLE_OU_START_DT in DATE,
362 X_COURSE_GROUP_TYPE in VARCHAR2,
363 X_CLOSED_IND in VARCHAR2,
364 X_MODE in VARCHAR2 default 'R',
365 x_ORG_ID in NUMBER
366 ) AS
367 cursor C is select ROWID from IGS_PS_GRP_ALL
368 where COURSE_GROUP_CD = X_COURSE_GROUP_CD;
372 begin
369 X_LAST_UPDATE_DATE DATE;
370 X_LAST_UPDATED_BY NUMBER;
371 X_LAST_UPDATE_LOGIN NUMBER;
373 X_LAST_UPDATE_DATE := SYSDATE;
374 if(X_MODE = 'I') then
375 X_LAST_UPDATED_BY := 1;
376 X_LAST_UPDATE_LOGIN := 0;
377 elsif (X_MODE = 'R') then
378 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
379 if X_LAST_UPDATED_BY is NULL then
380 X_LAST_UPDATED_BY := -1;
381 end if;
382 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
383 if X_LAST_UPDATE_LOGIN is NULL then
384 X_LAST_UPDATE_LOGIN := -1;
385 end if;
386 else
387 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
388 IGS_GE_MSG_STACK.ADD;
389 app_exception.raise_exception;
390 end if;
391 Before_DML (
392 p_action => 'INSERT',
393 x_rowid => X_ROWID,
394 x_course_group_cd => X_COURSE_GROUP_CD,
395 x_description => X_DESCRIPTION ,
396 x_responsible_org_unit_cd => X_RESPONSIBLE_ORG_UNIT_CD ,
397 x_responsible_ou_start_dt => X_RESPONSIBLE_OU_START_DT,
398 x_course_group_type => X_COURSE_GROUP_TYPE ,
399 x_closed_ind => NVL(X_CLOSED_IND,'N') ,
400 x_creation_date => X_LAST_UPDATE_DATE ,
401 x_created_by => X_LAST_UPDATED_BY ,
402 x_last_update_date => X_LAST_UPDATE_DATE ,
403 x_last_updated_by => X_LAST_UPDATED_BY ,
404 x_last_update_login => X_LAST_UPDATE_LOGIN,
405 x_org_id=>igs_ge_gen_003.get_org_id
406
407 );
408 insert into IGS_PS_GRP_ALL (
409 COURSE_GROUP_CD,
410 DESCRIPTION,
411 RESPONSIBLE_ORG_UNIT_CD,
412 RESPONSIBLE_OU_START_DT,
413 COURSE_GROUP_TYPE,
414 CLOSED_IND,
415 CREATION_DATE,
416 CREATED_BY,
417 LAST_UPDATE_DATE,
418 LAST_UPDATED_BY,
419 LAST_UPDATE_LOGIN,
420 ORG_ID
421 ) values (
422 NEW_REFERENCES.COURSE_GROUP_CD,
423 NEW_REFERENCES.DESCRIPTION,
424 NEW_REFERENCES.RESPONSIBLE_ORG_UNIT_CD,
425 NEW_REFERENCES.RESPONSIBLE_OU_START_DT,
426 NEW_REFERENCES.COURSE_GROUP_TYPE,
427 NEW_REFERENCES.CLOSED_IND,
428 X_LAST_UPDATE_DATE,
429 X_LAST_UPDATED_BY,
430 X_LAST_UPDATE_DATE,
431 X_LAST_UPDATED_BY,
432 X_LAST_UPDATE_LOGIN,
433 NEW_REFERENCES.ORG_ID
434 );
435
436 open c;
437 fetch c into X_ROWID;
438 if (c%notfound) then
439 close c;
440 raise no_data_found;
441 end if;
442 close c;
443 After_DML (
444 p_action => 'INSERT',
445 x_rowid => X_ROWID
446 );
447 end INSERT_ROW;
448
449 procedure LOCK_ROW (
450 X_ROWID IN VARCHAR2,
451 X_COURSE_GROUP_CD in VARCHAR2,
452 X_DESCRIPTION in VARCHAR2,
453 X_RESPONSIBLE_ORG_UNIT_CD in VARCHAR2,
454 X_RESPONSIBLE_OU_START_DT in DATE,
455 X_COURSE_GROUP_TYPE in VARCHAR2,
456 X_CLOSED_IND in VARCHAR2
457
458 ) AS
459 cursor c1 is select
460 DESCRIPTION,
461 RESPONSIBLE_ORG_UNIT_CD,
462 RESPONSIBLE_OU_START_DT,
463 COURSE_GROUP_TYPE,
464 CLOSED_IND
465 from IGS_PS_GRP_ALL
466 where ROWID = X_ROWID
467 for update nowait;
468 tlinfo c1%rowtype;
469
470 begin
471 open c1;
472 fetch c1 into tlinfo;
473 if (c1%notfound) then
474 close c1;
475 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
476 IGS_GE_MSG_STACK.ADD;
477 app_exception.raise_exception;
478 return;
479 end if;
480 close c1;
481
482 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
483 AND ((tlinfo.RESPONSIBLE_ORG_UNIT_CD = X_RESPONSIBLE_ORG_UNIT_CD)
484 OR ((tlinfo.RESPONSIBLE_ORG_UNIT_CD is null)
485 AND (X_RESPONSIBLE_ORG_UNIT_CD is null)))
486 AND ((tlinfo.RESPONSIBLE_OU_START_DT = X_RESPONSIBLE_OU_START_DT)
487 OR ((tlinfo.RESPONSIBLE_OU_START_DT is null)
488 AND (X_RESPONSIBLE_OU_START_DT is null)))
489 AND (tlinfo.COURSE_GROUP_TYPE = X_COURSE_GROUP_TYPE)
490 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
491 ) then
492 null;
493 else
494 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
495 IGS_GE_MSG_STACK.ADD;
496 app_exception.raise_exception;
497 end if;
498 return;
499 end LOCK_ROW;
500
501 procedure UPDATE_ROW (
502 X_ROWID IN VARCHAR2,
503 X_COURSE_GROUP_CD in VARCHAR2,
504 X_DESCRIPTION in VARCHAR2,
505 X_RESPONSIBLE_ORG_UNIT_CD in VARCHAR2,
506 X_RESPONSIBLE_OU_START_DT in DATE,
507 X_COURSE_GROUP_TYPE in VARCHAR2,
508 X_CLOSED_IND in VARCHAR2,
509 X_MODE in VARCHAR2 default 'R'
510
511 ) AS
512 X_LAST_UPDATE_DATE DATE;
513 X_LAST_UPDATED_BY NUMBER;
514 X_LAST_UPDATE_LOGIN NUMBER;
515 begin
516 X_LAST_UPDATE_DATE := SYSDATE;
517 if(X_MODE = 'I') then
518 X_LAST_UPDATED_BY := 1;
519 X_LAST_UPDATE_LOGIN := 0;
520 elsif (X_MODE = 'R') then
521 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
522 if X_LAST_UPDATED_BY is NULL then
523 X_LAST_UPDATED_BY := -1;
524 end if;
525 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
526 if X_LAST_UPDATE_LOGIN is NULL then
527 X_LAST_UPDATE_LOGIN := -1;
528 end if;
529 else
530 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
531 IGS_GE_MSG_STACK.ADD;
532 app_exception.raise_exception;
533 end if;
534
535 Before_DML (
536 p_action => 'UPDATE',
537 x_rowid => X_ROWID,
538 x_course_group_cd => X_COURSE_GROUP_CD,
539 x_description => X_DESCRIPTION ,
540 x_responsible_org_unit_cd => X_RESPONSIBLE_ORG_UNIT_CD ,
541 x_responsible_ou_start_dt => X_RESPONSIBLE_OU_START_DT,
542 x_course_group_type => X_COURSE_GROUP_TYPE ,
543 x_closed_ind => X_CLOSED_IND ,
544 x_creation_date => X_LAST_UPDATE_DATE ,
545 x_created_by => X_LAST_UPDATED_BY ,
546 x_last_update_date => X_LAST_UPDATE_DATE ,
547 x_last_updated_by => X_LAST_UPDATED_BY ,
548 x_last_update_login => X_LAST_UPDATE_LOGIN
549 );
550
551 update IGS_PS_GRP_ALL set
552 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
553 RESPONSIBLE_ORG_UNIT_CD = NEW_REFERENCES.RESPONSIBLE_ORG_UNIT_CD,
554 RESPONSIBLE_OU_START_DT = NEW_REFERENCES.RESPONSIBLE_OU_START_DT,
555 COURSE_GROUP_TYPE = NEW_REFERENCES.COURSE_GROUP_TYPE,
556 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
557 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
558 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
559 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
560 where ROWID = X_ROWID
561 ;
562 if (sql%notfound) then
563 raise no_data_found;
564 end if;
565 After_DML (
566 p_action => 'UPDATE',
567 x_rowid => X_ROWID
568 );
569 end UPDATE_ROW;
570
571 procedure ADD_ROW (
572 X_ROWID in out NOCOPY VARCHAR2,
573 X_COURSE_GROUP_CD in VARCHAR2,
574 X_DESCRIPTION in VARCHAR2,
575 X_RESPONSIBLE_ORG_UNIT_CD in VARCHAR2,
576 X_RESPONSIBLE_OU_START_DT in DATE,
577 X_COURSE_GROUP_TYPE in VARCHAR2,
578 X_CLOSED_IND in VARCHAR2,
579 X_MODE in VARCHAR2 default 'R',
580 X_ORG_ID IN NUMBER
581 ) AS
582 cursor c1 is select rowid from IGS_PS_GRP_ALL
583 where COURSE_GROUP_CD = X_COURSE_GROUP_CD
584 ;
585 begin
586 open c1;
587 fetch c1 into X_ROWID;
588 if (c1%notfound) then
589 close c1;
590 INSERT_ROW (
591 X_ROWID,
592 X_COURSE_GROUP_CD,
593 X_DESCRIPTION,
594 X_RESPONSIBLE_ORG_UNIT_CD,
595 X_RESPONSIBLE_OU_START_DT,
596 X_COURSE_GROUP_TYPE,
597 X_CLOSED_IND,
598 X_MODE,
599 X_ORG_ID);
600 return;
601 end if;
602 close c1;
603 UPDATE_ROW (
604 X_ROWID,
605 X_COURSE_GROUP_CD,
606 X_DESCRIPTION,
607 X_RESPONSIBLE_ORG_UNIT_CD,
608 X_RESPONSIBLE_OU_START_DT,
609 X_COURSE_GROUP_TYPE,
610 X_CLOSED_IND,
611 X_MODE
612 );
613 end ADD_ROW;
614
615 end IGS_PS_GRP_PKG;