[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_OWN_PKG
Source
1 package body IGS_PS_OWN_PKG AS
2 /* $Header: IGSPI27B.pls 120.0 2005/06/01 22:10:53 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_PS_OWN%RowType;
5 new_references IGS_PS_OWN%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
11 x_ou_start_dt IN DATE DEFAULT NULL,
12 x_percentage IN NUMBER DEFAULT NULL,
13 x_course_cd IN VARCHAR2 DEFAULT NULL,
14 x_version_number IN NUMBER DEFAULT NULL,
15 x_creation_date IN DATE DEFAULT NULL,
16 x_created_by IN NUMBER DEFAULT NULL,
17 x_last_update_date IN DATE DEFAULT NULL,
18 x_last_updated_by IN NUMBER DEFAULT NULL,
19 x_last_update_login IN NUMBER DEFAULT NULL
20 ) AS
21
22 CURSOR cur_old_ref_values IS
23 SELECT *
24 FROM IGS_PS_OWN
25 WHERE rowid = x_rowid;
26
27 BEGIN
28
29 l_rowid := x_rowid;
30
31 -- Code for setting the Old and New Reference Values.
32 -- Populate Old Values.
33 Open cur_old_ref_values;
34 Fetch cur_old_ref_values INTO old_references;
35 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
36 Close cur_old_ref_values;
37 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38 IGS_GE_MSG_STACK.ADD;
39 App_Exception.Raise_Exception;
40 Return;
41 END IF;
42 Close cur_old_ref_values;
43
44 -- Populate New Values.
45 new_references.org_unit_cd := x_org_unit_cd;
46 new_references.ou_start_dt := x_ou_start_dt;
47 new_references.percentage := x_percentage;
48 new_references.course_cd := x_course_cd;
49 new_references.version_number := x_version_number;
50 IF (p_action = 'UPDATE') THEN
51 new_references.creation_date := old_references.creation_date;
52 new_references.created_by := old_references.created_by;
53 ELSE
54 new_references.creation_date := x_creation_date;
55 new_references.created_by := x_created_by;
56 END IF;
57 new_references.last_update_date := x_last_update_date;
58 new_references.last_updated_by := x_last_updated_by;
59 new_references.last_update_login := x_last_update_login;
60
61 END Set_Column_Values;
62
63
64 PROCEDURE Check_Child_Existance AS
65 /*
66 || Created By : [email protected]
67 || Created On : 12-JAN-2005
68 || Purpose : Checks for the existance of Child records.
69 || Known limitations, enhancements or remarks :
70 || Change History :
71 || Who When What
72 || (reverse chronological order - newest change first)
73 */
74 BEGIN
75
76 IGS_HE_PROG_OU_CC_PKG.get_fk_igs_ps_own (
77 old_references.course_cd,
78 old_references.version_number,
79 old_references.org_unit_cd
80 );
81
82 END check_child_existance;
83
84
85 -- Trigger description :-
86 -- "OSS_TST".trg_cow_br_iud
87 -- BEFORE INSERT OR DELETE OR UPDATE
88 -- ON IGS_PS_OWN
89 -- FOR EACH ROW
90
91 PROCEDURE BeforeRowInsertUpdateDelete1(
92 p_inserting IN BOOLEAN DEFAULT FALSE,
93 p_updating IN BOOLEAN DEFAULT FALSE,
94 p_deleting IN BOOLEAN DEFAULT FALSE
95 ) AS
96 v_message_name VARCHAR2(30);
97 v_course_cd IGS_PS_OWN.course_cd%TYPE;
98 v_version_number IGS_PS_OWN.version_number%TYPE;
99 v_percentage IGS_PS_OWN.percentage%TYPE;
100 BEGIN
101 -- Set variables.
102 IF p_deleting THEN
103 v_course_cd := old_references.course_cd;
104 v_version_number := old_references.version_number;
105 ELSE -- p_inserting or p_updating
106 v_course_cd := new_references.course_cd;
107 v_version_number := new_references.version_number;
108 END IF;
109 -- Validate the insert/update/delete.
110 IF IGS_PS_VAL_CRS.crsp_val_iud_crv_dtl (
111 v_course_cd,
112 v_version_number,
113 v_message_name) = FALSE THEN
114 Fnd_Message.Set_Name('IGS',v_message_name);
115 IGS_GE_MSG_STACK.ADD;
116 App_Exception.Raise_Exception;
117 END IF;
118 -- Validate the org IGS_PS_UNIT. Org IGS_PS_UNIT is not updateable.
119 IF p_inserting THEN
120 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_COw.crsp_val_ou_sys_sts
121 IF IGS_PS_VAL_CRV.crsp_val_ou_sys_sts (
122 new_references.org_unit_cd,
123 new_references.ou_start_dt,
124 v_message_name) = FALSE THEN
125 Fnd_Message.Set_Name('IGS',v_message_name);
126 IGS_GE_MSG_STACK.ADD;
127 App_Exception.Raise_Exception;
128 END IF;
129 END IF;
130 -- Insert history record on update.
131 IF p_updating THEN
132 IF old_references.percentage <> new_references.percentage THEN
133 SELECT DECODE(old_references.percentage,new_references.percentage,NULL,old_references.percentage)
134 INTO v_percentage
135 FROM dual;
136 -- Create history record for update
137 IGS_PS_GEN_007.CRSP_INS_COW_HIST(
138 old_references.course_cd,
139 old_references.version_number,
140 old_references.org_unit_cd,
141 old_references.ou_start_dt,
142 old_references.last_update_date,
143 new_references.last_update_date,
144 old_references.last_updated_by,
145 v_percentage);
146 END IF;
147 END IF;
148 IF p_deleting THEN
149
150 -- Added for HE355 - Org Unit Cost Center Link to check for child existance before deleting record
151 Check_Child_Existance;
152
153 -- Create history record for deletion
154 IGS_PS_GEN_007.CRSP_INS_COW_HIST(
155 old_references.course_cd,
156 old_references.version_number,
157 old_references.org_unit_cd,
158 old_references.ou_start_dt,
159 old_references.last_update_date,
160 SYSDATE,
161 old_references.last_updated_by,
162 old_references.percentage);
163 END IF;
164
165
166 END BeforeRowInsertUpdateDelete1;
167
168 PROCEDURE Check_Constraints (
169 Column_Name IN VARCHAR2 DEFAULT NULL,
170 Column_Value IN VARCHAR2 DEFAULT NULL
171 )
172 AS
173 BEGIN
174 IF column_name is null THEN
175 NULL;
176 ELSIF upper(column_name) = 'COURSE_CD' THEN
177 new_references.course_cd := column_value;
178 ELSIF upper(column_name) = 'PERCENTAGE' THEN
179 new_references.percentage := IGS_GE_NUMBER.TO_NUM(column_value);
180 END IF;
181 IF upper(column_name)= 'COURSE_CD' OR
182 column_name is null THEN
183 IF new_references.course_cd <> UPPER(new_references.course_cd)
184 THEN
185 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
186 IGS_GE_MSG_STACK.ADD;
187 App_Exception.Raise_Exception;
188 END IF;
189 END IF;
190
191 IF upper(column_name)= 'PERCENTAGE' OR
192 column_name is null THEN
193 IF (new_references.percentage < 0 OR new_references.percentage > 100 )
194 THEN
195 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
196 IGS_GE_MSG_STACK.ADD;
197 App_Exception.Raise_Exception;
198 END IF;
199 END IF;
200 END Check_Constraints;
201
202 PROCEDURE Check_Parent_Existance AS
203 BEGIN
204
205 IF (((old_references.course_cd = new_references.course_cd) AND
206 (old_references.version_number = new_references.version_number)) OR
207 ((new_references.course_cd IS NULL) OR
208 (new_references.version_number IS NULL))) THEN
209 NULL;
210 ELSE
211 IF NOT IGS_PS_VER_PKG.Get_PK_For_Validation (
212 new_references.course_cd,
213 new_references.version_number
214 )THEN
215 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
216 IGS_GE_MSG_STACK.ADD;
217 App_Exception.Raise_Exception;
218 END IF;
219 END IF;
220
221
222 IF (((old_references.org_unit_cd = new_references.org_unit_cd) AND
223 (old_references.ou_start_dt = new_references.ou_start_dt)) OR
224 ((new_references.org_unit_cd IS NULL) OR
225 (new_references.ou_start_dt IS NULL))) THEN
226 NULL;
227 ELSE
228 IF NOT IGS_OR_UNIT_PKG.Get_PK_For_Validation (
229 new_references.org_unit_cd,
230 new_references.ou_start_dt
231 )THEN
232 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
233 IGS_GE_MSG_STACK.ADD;
234 App_Exception.Raise_Exception;
235 END IF;
236 END IF;
237
238 END Check_Parent_Existance;
239
240
241 FUNCTION Get_PK_For_Validation (
242 x_course_cd IN VARCHAR2,
243 x_version_number IN NUMBER,
244 x_org_unit_cd IN VARCHAR2,
245 x_ou_start_dt IN DATE
246 ) RETURN BOOLEAN AS
247
248 CURSOR cur_rowid IS
249 SELECT rowid
250 FROM IGS_PS_OWN
251 WHERE course_cd = x_course_cd
252 AND version_number = x_version_number
253 AND org_unit_cd = x_org_unit_cd
254 AND ou_start_dt = x_ou_start_dt
255 FOR UPDATE NOWAIT;
256
257 lv_rowid cur_rowid%RowType;
258
259 BEGIN
260
261 Open cur_rowid;
262 Fetch cur_rowid INTO lv_rowid;
263 IF (cur_rowid%FOUND) THEN
264 Close cur_rowid;
265 Return(TRUE);
266 ELSE
267 Close cur_rowid;
268 Return(FALSE);
269 END IF;
270 END Get_PK_For_Validation;
271
272 FUNCTION Get_PK_For_Validation (
273 x_course_cd IN VARCHAR2,
274 x_version_number IN NUMBER,
275 x_org_unit_cd IN VARCHAR2
276 ) RETURN BOOLEAN AS
277 /*
278 || Created By : [email protected]
279 || Created On : 26-JAN-2005
280 || Purpose : Validates the Primary Keys of the table.
281 || Known limitations, enhancements or remarks :
282 || Change History :
283 || Who When What
284 || (reverse chronological order - newest change first)
285 */
286 CURSOR cur_rowid IS
287 SELECT rowid
288 FROM IGS_PS_OWN
289 WHERE course_cd = x_course_cd
290 AND version_number = x_version_number
291 AND org_unit_cd = x_org_unit_cd
292 FOR UPDATE NOWAIT;
293
294 lv_rowid cur_rowid%RowType;
295
296 BEGIN
297
298 Open cur_rowid;
299 Fetch cur_rowid INTO lv_rowid;
300 IF (cur_rowid%FOUND) THEN
301 Close cur_rowid;
302 Return(TRUE);
303 ELSE
304 Close cur_rowid;
305 Return(FALSE);
306 END IF;
307 END Get_PK_For_Validation;
308
309 PROCEDURE GET_FK_IGS_PS_VER (
310 x_course_cd IN VARCHAR2,
311 x_version_number IN NUMBER
312 ) AS
313
314
315 CURSOR cur_rowid IS
316 SELECT rowid
317 FROM IGS_PS_OWN
318 WHERE course_cd = x_course_cd
319 AND version_number = x_version_number ;
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_COW_CRV_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_VER;
337
338 PROCEDURE GET_FK_IGS_OR_UNIT (
339 x_org_unit_cd IN VARCHAR2,
340 x_start_dt IN VARCHAR2
341 ) AS
342
343 CURSOR cur_rowid IS
344 SELECT rowid
345 FROM IGS_PS_OWN
346 WHERE org_unit_cd = x_org_unit_cd
347 AND ou_start_dt = x_start_dt ;
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_COW_OU_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_OR_UNIT;
365
366 PROCEDURE Before_DML (
367 p_action IN VARCHAR2,
368 x_rowid IN VARCHAR2 DEFAULT NULL,
369 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
370 x_ou_start_dt IN DATE DEFAULT NULL,
371 x_percentage IN NUMBER DEFAULT NULL,
372 x_course_cd IN VARCHAR2 DEFAULT NULL,
373 x_version_number IN NUMBER DEFAULT NULL,
374 x_creation_date IN DATE DEFAULT NULL,
375 x_created_by IN NUMBER DEFAULT NULL,
376 x_last_update_date IN DATE DEFAULT NULL,
377 x_last_updated_by IN NUMBER DEFAULT NULL,
378 x_last_update_login IN NUMBER DEFAULT NULL
379 ) AS
380 BEGIN
381
382 Set_Column_Values (
383 p_action,
384 x_rowid,
385 x_org_unit_cd,
386 x_ou_start_dt,
387 x_percentage,
388 x_course_cd,
389 x_version_number,
390 x_creation_date,
391 x_created_by,
392 x_last_update_date,
393 x_last_updated_by,
394 x_last_update_login
395 );
396
397 IF (p_action = 'INSERT') THEN
398 -- Call all the procedures related to Before Insert.
399 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
400 IF Get_PK_For_Validation(
401 new_references.course_cd ,
402 new_references.version_number ,
403 new_references.org_unit_cd ,
404 new_references.ou_start_dt
405 ) THEN
406 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
407 IGS_GE_MSG_STACK.ADD;
408 App_Exception.Raise_Exception;
409 END IF;
410 Check_Constraints;
411 Check_Parent_Existance;
412 ELSIF (p_action = 'UPDATE') THEN
413 -- Call all the procedures related to Before Update.
414 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
415 Check_Constraints;
416 Check_Parent_Existance;
417 ELSIF (p_action = 'DELETE') THEN
418 -- Call all the procedures related to Before Delete.
419 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
420 ELSIF (p_action = 'VALIDATE_INSERT') THEN
421 IF Get_PK_For_Validation(
422 new_references.course_cd ,
423 new_references.version_number ,
424 new_references.org_unit_cd ,
425 new_references.ou_start_dt
426 ) THEN
427 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
428 IGS_GE_MSG_STACK.ADD;
429 App_Exception.Raise_Exception;
430 END IF;
431 Check_Constraints;
432 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
433 Check_Constraints;
434 ELSIF (p_action = 'VALIDATE_DELETE') THEN
435 -- Added for HE355 - Org Unit Cost Center Link to check for child existance before deleting record
436 Check_Child_Existance;
437 END IF;
438 END Before_DML;
439
440 PROCEDURE After_DML (
441 p_action IN VARCHAR2,
442 x_rowid IN VARCHAR2
443 ) AS
444 BEGIN
445
446 l_rowid := x_rowid;
447
448
449 END After_DML;
450
451 procedure INSERT_ROW (
452 X_ROWID in out NOCOPY VARCHAR2,
453 X_COURSE_CD in VARCHAR2,
454 X_OU_START_DT in DATE,
455 X_ORG_UNIT_CD in VARCHAR2,
456 X_VERSION_NUMBER in NUMBER,
457 X_PERCENTAGE in NUMBER,
458 X_MODE in VARCHAR2 default 'R'
459 ) AS
460 cursor C is select ROWID from IGS_PS_OWN
461 where COURSE_CD = X_COURSE_CD
462 and OU_START_DT = X_OU_START_DT
463 and ORG_UNIT_CD = X_ORG_UNIT_CD
464 and VERSION_NUMBER = X_VERSION_NUMBER;
465 X_LAST_UPDATE_DATE DATE;
466 X_LAST_UPDATED_BY NUMBER;
467 X_LAST_UPDATE_LOGIN NUMBER;
468 begin
469 X_LAST_UPDATE_DATE := SYSDATE;
470 IF (X_MODE = 'I') then
471 X_LAST_UPDATED_BY := 1;
472 X_LAST_UPDATE_LOGIN := 0;
473 elsif (X_MODE = 'R') then
474 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
475 if X_LAST_UPDATED_BY is NULL then
476 X_LAST_UPDATED_BY := -1;
477 end if;
478 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
479 if X_LAST_UPDATE_LOGIN is NULL then
480 X_LAST_UPDATE_LOGIN := -1;
481 end if;
482 else
483 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
484 IGS_GE_MSG_STACK.ADD;
485 app_exception.raise_exception;
486 end if;
487 Before_DML (
488 p_action => 'INSERT',
489 x_rowid => X_ROWID,
490 x_org_unit_cd => X_ORG_UNIT_CD,
491 x_ou_start_dt => X_OU_START_DT,
492 x_percentage => X_PERCENTAGE,
493 x_course_cd => X_COURSE_CD,
494 x_version_number => X_VERSION_NUMBER,
495 x_creation_date => X_LAST_UPDATE_DATE ,
496 x_created_by => X_LAST_UPDATED_BY ,
497 x_last_update_date => X_LAST_UPDATE_DATE ,
498 x_last_updated_by => X_LAST_UPDATED_BY ,
499 x_last_update_login => X_LAST_UPDATE_LOGIN
500 );
501 insert into IGS_PS_OWN (
502 COURSE_CD,
503 VERSION_NUMBER,
504 ORG_UNIT_CD,
505 OU_START_DT,
506 PERCENTAGE,
507 CREATION_DATE,
508 CREATED_BY,
509 LAST_UPDATE_DATE,
510 LAST_UPDATED_BY,
511 LAST_UPDATE_LOGIN
512 ) values (
513 NEW_REFERENCES.COURSE_CD,
514 NEW_REFERENCES.VERSION_NUMBER,
515 NEW_REFERENCES.ORG_UNIT_CD,
516 NEW_REFERENCES.OU_START_DT,
517 NEW_REFERENCES.PERCENTAGE,
518 X_LAST_UPDATE_DATE,
519 X_LAST_UPDATED_BY,
520 X_LAST_UPDATE_DATE,
521 X_LAST_UPDATED_BY,
522 X_LAST_UPDATE_LOGIN
523 );
524
525 open c;
526 fetch c into X_ROWID;
527 if (c%notfound) then
528 close c;
529 raise no_data_found;
530 end if;
531 close c;
532 After_DML (
533 p_action => 'INSERT',
534 x_rowid => X_ROWID
535 );
536 end INSERT_ROW;
537
538 procedure LOCK_ROW (
539 X_ROWID IN VARCHAR2,
540 X_COURSE_CD in VARCHAR2,
541 X_OU_START_DT in DATE,
542 X_ORG_UNIT_CD in VARCHAR2,
543 X_VERSION_NUMBER in NUMBER,
544 X_PERCENTAGE in NUMBER
545 ) AS
546 cursor c1 is select
547 PERCENTAGE
548 from IGS_PS_OWN
549 where ROWID = X_ROWID
550 for update nowait;
551 tlinfo c1%rowtype;
552
553 begin
554 open c1;
555 fetch c1 into tlinfo;
556 if (c1%notfound) then
557 close c1;
558 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
559 IGS_GE_MSG_STACK.ADD;
560 app_exception.raise_exception;
561 return;
562 end if;
563 close c1;
564
565 if ( (tlinfo.PERCENTAGE = X_PERCENTAGE)
566 ) then
567 null;
568 else
569 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
570 IGS_GE_MSG_STACK.ADD;
571 app_exception.raise_exception;
572 end if;
573 return;
574 end LOCK_ROW;
575
576 procedure UPDATE_ROW (
577 X_ROWID IN VARCHAR2,
578 X_COURSE_CD in VARCHAR2,
579 X_OU_START_DT in DATE,
580 X_ORG_UNIT_CD in VARCHAR2,
581 X_VERSION_NUMBER in NUMBER,
582 X_PERCENTAGE in NUMBER,
583 X_MODE in VARCHAR2 default 'R'
584 ) AS
585 X_LAST_UPDATE_DATE DATE;
586 X_LAST_UPDATED_BY NUMBER;
587 X_LAST_UPDATE_LOGIN NUMBER;
588 begin
589 X_LAST_UPDATE_DATE := SYSDATE;
590 if (X_MODE = 'I') then
591 X_LAST_UPDATED_BY := 1;
592 X_LAST_UPDATE_LOGIN := 0;
593 elsif (X_MODE = 'R') then
594 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
595 if X_LAST_UPDATED_BY is NULL then
596 X_LAST_UPDATED_BY := -1;
597 end if;
598 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
599 if X_LAST_UPDATE_LOGIN is NULL then
600 X_LAST_UPDATE_LOGIN := -1;
601 end if;
602 else
603 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
604 IGS_GE_MSG_STACK.ADD;
605 app_exception.raise_exception;
606 end if;
607 Before_DML (
608 p_action => 'UPDATE',
609 x_rowid => X_ROWID,
610 x_org_unit_cd => X_ORG_UNIT_CD,
611 x_ou_start_dt => X_OU_START_DT,
612 x_percentage => X_PERCENTAGE,
613 x_course_cd => X_COURSE_CD,
614 x_version_number => X_VERSION_NUMBER,
615 x_creation_date => X_LAST_UPDATE_DATE ,
616 x_created_by => X_LAST_UPDATED_BY ,
617 x_last_update_date => X_LAST_UPDATE_DATE ,
618 x_last_updated_by => X_LAST_UPDATED_BY ,
619 x_last_update_login => X_LAST_UPDATE_LOGIN
620 );
621 update IGS_PS_OWN set
622 PERCENTAGE = NEW_REFERENCES.PERCENTAGE,
623 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
624 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
625 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
626 where ROWID = X_ROWID
627 ;
628 if (sql%notfound) then
629 raise no_data_found;
630 end if;
631 After_DML (
632 p_action => 'UPDATE',
633 x_rowid => X_ROWID
634 );
635 end UPDATE_ROW;
636
637 procedure ADD_ROW (
638 X_ROWID in out NOCOPY VARCHAR2,
639 X_COURSE_CD in VARCHAR2,
640 X_OU_START_DT in DATE,
641 X_ORG_UNIT_CD in VARCHAR2,
642 X_VERSION_NUMBER in NUMBER,
643 X_PERCENTAGE in NUMBER,
644 X_MODE in VARCHAR2 default 'R'
645 ) AS
646 cursor c1 is select rowid from IGS_PS_OWN
647 where COURSE_CD = X_COURSE_CD
648 and OU_START_DT = X_OU_START_DT
649 and ORG_UNIT_CD = X_ORG_UNIT_CD
650 and VERSION_NUMBER = X_VERSION_NUMBER
651 ;
652 begin
653 open c1;
654 fetch c1 into X_ROWID;
655 if (c1%notfound) then
656 close c1;
657 INSERT_ROW (
658 X_ROWID,
659 X_COURSE_CD,
660 X_OU_START_DT,
661 X_ORG_UNIT_CD,
662 X_VERSION_NUMBER,
663 X_PERCENTAGE,
664 X_MODE);
665 return;
666 end if;
667 close c1;
668 UPDATE_ROW (
669 X_ROWID,
670 X_COURSE_CD,
671 X_OU_START_DT,
672 X_ORG_UNIT_CD,
673 X_VERSION_NUMBER,
674 X_PERCENTAGE,
675 X_MODE);
676 end ADD_ROW;
677
678 procedure DELETE_ROW (
679 X_ROWID IN VARCHAR2
680 ) AS
681 begin
682 Before_DML (
683 p_action => 'DELETE',
684 x_rowid => X_ROWID
685 );
686 delete from IGS_PS_OWN
687 where ROWID = X_ROWID;
688 if (sql%notfound) then
689 raise no_data_found;
690 end if;
691 After_DML (
692 p_action => 'DELETE',
693 x_rowid => X_ROWID
694 );
695 end DELETE_ROW;
696
697 end IGS_PS_OWN_PKG;