[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_CAT_PRC_STEP_PKG
Source
1 package body IGS_EN_CAT_PRC_STEP_PKG AS
2 /* $Header: IGSEI25B.pls 115.8 2003/06/11 06:28:29 rnirwani ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 24-AUG-2001 Bug No. 1956374 .The call to igs_ge_gen_004.genp_val_sdtt_sess
7 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess
8 -------------------------------------------------------------------------------------------
9 l_rowid VARCHAR2(25);
10 old_references IGS_EN_CAT_PRC_STEP_ALL%RowType;
11 new_references IGS_EN_CAT_PRC_STEP_ALL%RowType;
12
13 PROCEDURE Set_Column_Values (
14 p_action IN VARCHAR2,
15 x_rowid IN VARCHAR2 DEFAULT NULL,
16 x_enrolment_cat IN VARCHAR2 DEFAULT NULL,
17 x_s_student_comm_type IN VARCHAR2 DEFAULT NULL,
18 x_enr_method_type IN VARCHAR2 DEFAULT NULL,
19 x_s_enrolment_step_type IN VARCHAR2 DEFAULT NULL,
20 x_step_order_num IN NUMBER DEFAULT NULL,
21 x_creation_date IN DATE DEFAULT NULL,
22 x_created_by IN NUMBER DEFAULT NULL,
23 x_last_update_date IN DATE DEFAULT NULL,
24 x_last_updated_by IN NUMBER DEFAULT NULL,
25 x_last_update_login IN NUMBER DEFAULT NULL,
26 x_org_id IN NUMBER DEFAULT NULL
27 ) AS
28
29 CURSOR cur_old_ref_values IS
30 SELECT *
31 FROM IGS_EN_CAT_PRC_STEP_ALL
32 WHERE rowid = x_rowid;
33
34 BEGIN
35
36 l_rowid := x_rowid;
37
38 -- Code for setting the Old and New Reference Values.
39 -- Populate Old Values.
40 Open cur_old_ref_values;
41 Fetch cur_old_ref_values INTO old_references;
42 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
43 Close cur_old_ref_values;
44 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
45 IGS_GE_MSG_STACK.ADD;
46 App_Exception.Raise_Exception;
47 Return;
48 END IF;
49 Close cur_old_ref_values;
50
51 -- Populate New Values.
52 new_references.enrolment_cat := x_enrolment_cat;
53 new_references.s_student_comm_type := x_s_student_comm_type;
54 new_references.enr_method_type := x_enr_method_type;
55 new_references.s_enrolment_step_type := x_s_enrolment_step_type;
56 new_references.step_order_num := x_step_order_num;
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 new_references.org_id := x_org_id ;
68 END Set_Column_Values;
69
70 -- Trigger description :-
71 -- "OSS_TST".trg_ecps_br_i
72 -- BEFORE INSERT
73 -- ON IGS_EN_CAT_PRC_STEP
74 -- FOR EACH ROW
75
76 PROCEDURE BeforeRowInsert1(
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 -- If trigger has not been disabled, perform required processing
84 IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_EN_CAT_PRC_STEP_ALL') THEN
85 IF p_inserting THEN
86 -- Validate the system enrolment step type
87 IF IGS_EN_VAL_ECPS.enrp_val_ecps_sest(
88 new_references.s_enrolment_step_type,
89 v_message_name) = FALSE THEN
90 fnd_message.set_name('IGS',v_message_name);
91 IGS_GE_MSG_STACK.ADD;
92 app_exception.raise_exception;
93 END IF;
94 END IF;
95 END IF;
96
97
98 END BeforeRowInsert1;
99
100 procedure Check_constraints(
101 column_name IN VARCHAR2 DEFAULT NULL,
102 column_value IN VARCHAR2 DEFAULT NULL
103 ) AS
104 begin
105 IF column_name is null then
106 NULL;
107 ELSIF upper(column_name) = 'ENROLMENT_CAT' then
108 new_references.enrolment_cat := column_value;
109 ELSIF upper(column_name) = 'ENR_METHOD_TYPE' then
110 new_references.enr_method_type := column_value;
111 ELSIF upper(column_name) = 'MANDATORY_STEP_IND' then
112 new_references.mandatory_step_ind := column_value;
113 ELSIF upper(column_name) = 'S_ENROLMENT_STEP_TYPE' then
114 new_references.s_enrolment_step_type := column_value;
115 ELSIF upper(column_name) = 'S_STUDENT_COMM_TYPE' then
116 new_references.s_student_comm_type := column_value;
117 end if;
118
119 IF upper(column_name) = 'ENROLMENT_CAT' OR
120 column_name is null then
121 if new_references.enrolment_cat <> upper(new_references.enrolment_cat) then
122 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
123 IGS_GE_MSG_STACK.ADD;
124 App_Exception.Raise_Exception;
125 end if;
126 end if;
127 IF upper(column_name) = 'ENR_METHOD_TYPE' OR
128 column_name is null then
129 if new_references.enr_method_type <>upper(new_references.enr_method_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 IF upper(column_name) = 'MANDATORY_STEP_IND' OR
136 column_name is null then
137 if new_references.mandatory_step_ind <> upper(new_references.mandatory_step_ind) OR
138 new_references.mandatory_step_ind NOT IN ('Y','N') 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 IF upper(column_name) = 'S_ENROLMENT_STEP_TYPE' OR
145 column_name is null then
146 if new_references.s_enrolment_step_type <> upper(new_references.s_enrolment_step_type) then
147 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
148 IGS_GE_MSG_STACK.ADD;
149 App_Exception.Raise_Exception;
150 end if;
151 end if;
152 IF upper(column_name) = 'S_STUDENT_COMM_TYPE' OR
153 column_name is null then
154 if new_references.s_student_comm_type <> upper(new_references.s_student_comm_type) then
155 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
156 IGS_GE_MSG_STACK.ADD;
157 App_Exception.Raise_Exception;
158 end if;
159 end if;
160 END check_constraints;
161
162 PROCEDURE Check_Parent_Existance AS
163 BEGIN
164
165 IF (((old_references.enrolment_cat = new_references.enrolment_cat) AND
166 (old_references.s_student_comm_type = new_references.s_student_comm_type) AND
167 (old_references.enr_method_type = new_references.enr_method_type)) OR
168 ((new_references.enrolment_cat IS NULL) OR
169 (new_references.s_student_comm_type IS NULL) OR
170 (new_references.enr_method_type IS NULL))) THEN
171 NULL;
172 ELSE
173 if not IGS_EN_CAT_PRC_DTL_PKG.Get_PK_For_Validation (
174 new_references.enrolment_cat,
175 new_references.s_student_comm_type,
176 new_references.enr_method_type
177 ) then
178 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
179 IGS_GE_MSG_STACK.ADD;
180 App_Exception.Raise_Exception;
181 end if;
182 END IF;
183
184 IF (((old_references.s_enrolment_step_type = new_references.s_enrolment_step_type)) OR
185 ((new_references.s_enrolment_step_type IS NULL))) THEN
186 NULL;
187 ELSE
188 if not IGS_LOOKUPS_VIEW_Pkg.Get_PK_For_Validation (
189 'ENROLMENT_STEP_TYPE',
190 new_references.s_enrolment_step_type
191 ) then
192 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
193 IGS_GE_MSG_STACK.ADD;
194 App_Exception.Raise_Exception;
195 end if;
196 END IF;
197
198 END Check_Parent_Existance;
199
200 FUNCTION Get_PK_For_Validation (
201 x_enrolment_cat IN VARCHAR2,
202 x_s_student_comm_type IN VARCHAR2,
203 x_enr_method_type IN VARCHAR2,
204 x_s_enrolment_step_type IN VARCHAR2
205 ) RETURN BOOLEAN AS
206
207 CURSOR cur_rowid IS
208 SELECT rowid
209 FROM IGS_EN_CAT_PRC_STEP_ALL
210 WHERE enrolment_cat = x_enrolment_cat
211 AND s_student_comm_type = x_s_student_comm_type
212 AND enr_method_type = x_enr_method_type
213 AND s_enrolment_step_type = x_s_enrolment_step_type
214 FOR UPDATE NOWAIT;
215
216 lv_rowid cur_rowid%RowType;
217
218 BEGIN
219
220 Open cur_rowid;
221 Fetch cur_rowid INTO lv_rowid;
222 IF (cur_rowid%FOUND) THEN
223 Close cur_rowid;
224 return(TRUE);
225 else
226 Close cur_rowid;
227 Return(FALSE);
228 END IF;
229
230 END Get_PK_For_Validation;
231
232 PROCEDURE GET_FK_IGS_EN_CAT_PRC_DTL (
233 x_enrolment_cat IN VARCHAR2,
234 x_s_student_comm_type IN VARCHAR2,
235 x_enr_method_type IN VARCHAR2
236 ) AS
237
238 CURSOR cur_rowid IS
239 SELECT rowid
240 FROM IGS_EN_CAT_PRC_STEP_ALL
241 WHERE enrolment_cat = x_enrolment_cat
242 AND s_student_comm_type = x_s_student_comm_type
243 AND enr_method_type = x_enr_method_type ;
244
245 lv_rowid cur_rowid%RowType;
246
247 BEGIN
248
249 Open cur_rowid;
250 Fetch cur_rowid INTO lv_rowid;
251 IF (cur_rowid%FOUND) THEN
252 Close cur_rowid;
253 Fnd_Message.Set_Name ('IGS', 'IGS_EN_ECPS_ECPD_FK');
254 IGS_GE_MSG_STACK.ADD;
255 App_Exception.Raise_Exception;
256 Return;
257 END IF;
258 Close cur_rowid;
259
260 END GET_FK_IGS_EN_CAT_PRC_DTL;
261
262 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW (
263 x_s_enrolment_step_type IN VARCHAR2
264 ) AS
265
266 CURSOR cur_rowid IS
267 SELECT rowid
268 FROM IGS_EN_CAT_PRC_STEP_ALL
269 WHERE s_enrolment_step_type = x_s_enrolment_step_type ;
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 Fnd_Message.Set_Name ('IGS', 'IGS_EN_ECPS_LKUPV_FK');
280 IGS_GE_MSG_STACK.ADD;
281 App_Exception.Raise_Exception;
282 Return;
283 END IF;
284 Close cur_rowid;
285
286 END GET_FK_IGS_LOOKUPS_VIEW;
287
288 PROCEDURE Before_DML (
289 p_action IN VARCHAR2,
290 x_rowid IN VARCHAR2 DEFAULT NULL,
291 x_enrolment_cat IN VARCHAR2 DEFAULT NULL,
292 x_s_student_comm_type IN VARCHAR2 DEFAULT NULL,
293 x_enr_method_type IN VARCHAR2 DEFAULT NULL,
294 x_s_enrolment_step_type IN VARCHAR2 DEFAULT NULL,
295 x_step_order_num IN NUMBER DEFAULT NULL,
296 x_creation_date IN DATE DEFAULT NULL,
297 x_created_by IN NUMBER DEFAULT NULL,
298 x_last_update_date IN DATE DEFAULT NULL,
299 x_last_updated_by IN NUMBER DEFAULT NULL,
300 x_last_update_login IN NUMBER DEFAULT NULL,
301 x_org_id IN NUMBER DEFAULT NULL
302 ) AS
303 BEGIN
304
305 Set_Column_Values (
306 p_action,
307 x_rowid,
308 x_enrolment_cat,
309 x_s_student_comm_type,
310 x_enr_method_type,
311 x_s_enrolment_step_type,
312 x_step_order_num,
313 x_creation_date,
314 x_created_by,
315 x_last_update_date,
316 x_last_updated_by,
317 x_last_update_login ,
318 x_org_id
319 );
320
321 IF (p_action = 'INSERT') THEN
322 -- Call all the procedures related to Before Insert.
323 BeforeRowInsert1 ( p_inserting => TRUE );
324 IF Get_PK_For_Validation (
325 new_references.enrolment_cat,
326 new_references.s_student_comm_type,
327 new_references.enr_method_type,
328 new_references.s_enrolment_step_type
329 ) then
330 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
331 IGS_GE_MSG_STACK.ADD;
332 App_Exception.Raise_Exception;
333 end if;
334 Check_constraints;
335 Check_Parent_Existance;
336 ELSIF (p_action = 'UPDATE') THEN
337 -- Call all the procedures related to Before Update.
338 Null;
339 Check_constraints;
340 Check_Parent_Existance;
341 ELSIF (p_action = 'DELETE') THEN
342 -- Call all the procedures related to Before Delete.
343 Null;
344 ELSIF (p_action = 'VALIDATE_INSERT') then
345 IF Get_PK_For_Validation (
346 new_references.enrolment_cat,
347 new_references.s_student_comm_type,
348 new_references.enr_method_type,
349 new_references.s_enrolment_step_type
350 ) then
351 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
352 IGS_GE_MSG_STACK.ADD;
353 App_Exception.Raise_Exception;
354 end if;
355 Check_constraints;
356 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
357 Check_constraints;
358 ELSIF (p_action = 'VALIDATE_DELETE') THEN
359 null;
360 END IF;
361
362 END Before_DML;
363
364 PROCEDURE After_DML (
365 p_action IN VARCHAR2,
366 x_rowid IN VARCHAR2
367 ) AS
368 BEGIN
369
370 l_rowid := x_rowid;
371
372 IF (p_action = 'INSERT') THEN
373 -- Call all the procedures related to After Insert.
374 Null;
375 ELSIF (p_action = 'UPDATE') THEN
376 -- Call all the procedures related to After Update.
377 Null;
378 ELSIF (p_action = 'DELETE') THEN
379 -- Call all the procedures related to After Delete.
380 Null;
381 END IF;
382
383 END After_DML;
384 procedure INSERT_ROW (
385 X_ROWID in out NOCOPY VARCHAR2,
386 X_ENROLMENT_CAT in VARCHAR2,
387 X_S_STUDENT_COMM_TYPE in VARCHAR2,
388 X_ENR_METHOD_TYPE in VARCHAR2,
389 X_S_ENROLMENT_STEP_TYPE in VARCHAR2,
390 X_STEP_ORDER_NUM in NUMBER,
391 X_MODE in VARCHAR2 default 'R',
392 x_org_id IN NUMBER
393 ) AS
394 cursor C is select ROWID from IGS_EN_CAT_PRC_STEP_ALL
395 where ENROLMENT_CAT = X_ENROLMENT_CAT
396 and S_STUDENT_COMM_TYPE = X_S_STUDENT_COMM_TYPE
397 and ENR_METHOD_TYPE = X_ENR_METHOD_TYPE
398 and S_ENROLMENT_STEP_TYPE = X_S_ENROLMENT_STEP_TYPE;
399 X_LAST_UPDATE_DATE DATE;
400 X_LAST_UPDATED_BY NUMBER;
401 X_LAST_UPDATE_LOGIN NUMBER;
402 begin
403 X_LAST_UPDATE_DATE := SYSDATE;
404 if(X_MODE = 'I') then
405 X_LAST_UPDATED_BY := 1;
406 X_LAST_UPDATE_LOGIN := 0;
407 elsif (X_MODE = 'R') then
408 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
409 if X_LAST_UPDATED_BY is NULL then
410 X_LAST_UPDATED_BY := -1;
411 end if;
412 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
413 if X_LAST_UPDATE_LOGIN is NULL then
414 X_LAST_UPDATE_LOGIN := -1;
415 end if;
416 else
417 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
418 IGS_GE_MSG_STACK.ADD;
419 app_exception.raise_exception;
420 end if;
421 Before_DML (
422 p_action => 'INSERT',
423 x_rowid => X_ROWID,
424 x_enrolment_cat => X_ENROLMENT_CAT,
425 x_s_student_comm_type => X_S_STUDENT_COMM_TYPE,
426 x_enr_method_type => X_ENR_METHOD_TYPE,
427 x_s_enrolment_step_type => X_S_ENROLMENT_STEP_TYPE,
428 x_step_order_num => X_STEP_ORDER_NUM,
429 x_creation_date => X_LAST_UPDATE_DATE,
433 x_last_update_login => X_LAST_UPDATE_LOGIN,
430 x_created_by => X_LAST_UPDATED_BY,
431 x_last_update_date => X_LAST_UPDATE_DATE,
432 x_last_updated_by => X_LAST_UPDATED_BY,
434 x_org_id => igs_ge_gen_003.get_org_id
435 );
436 insert into IGS_EN_CAT_PRC_STEP_ALL (
437 ENROLMENT_CAT,
438 S_STUDENT_COMM_TYPE,
439 ENR_METHOD_TYPE,
440 S_ENROLMENT_STEP_TYPE,
441 STEP_ORDER_NUM,
442 MANDATORY_STEP_IND,
443 CREATION_DATE,
444 CREATED_BY,
445 LAST_UPDATE_DATE,
446 LAST_UPDATED_BY,
447 LAST_UPDATE_LOGIN,
448 org_id
449 ) values (
450 NEW_REFERENCES.ENROLMENT_CAT,
451 NEW_REFERENCES.S_STUDENT_COMM_TYPE,
452 NEW_REFERENCES.ENR_METHOD_TYPE,
453 NEW_REFERENCES.S_ENROLMENT_STEP_TYPE,
454 NEW_REFERENCES.STEP_ORDER_NUM,
455 NEW_REFERENCES.MANDATORY_STEP_IND,
456 X_LAST_UPDATE_DATE,
457 X_LAST_UPDATED_BY,
458 X_LAST_UPDATE_DATE,
459 X_LAST_UPDATED_BY,
460 X_LAST_UPDATE_LOGIN,
461 NEW_REFERENCES.org_id
462 );
463
464 open c;
465 fetch c into X_ROWID;
466 if (c%notfound) then
467 close c;
468 raise no_data_found;
469 end if;
470 close c;
471 After_DML (
472 p_action => 'INSERT',
473 x_rowid => X_ROWID
474 );
475 end INSERT_ROW;
476
477 procedure LOCK_ROW (
478 X_ROWID in VARCHAR2,
479 X_ENROLMENT_CAT in VARCHAR2,
480 X_S_STUDENT_COMM_TYPE in VARCHAR2,
481 X_ENR_METHOD_TYPE in VARCHAR2,
482 X_S_ENROLMENT_STEP_TYPE in VARCHAR2,
483 X_STEP_ORDER_NUM in NUMBER
484 ) AS
485 cursor c1 is select
486 STEP_ORDER_NUM,
487 MANDATORY_STEP_IND
488 from IGS_EN_CAT_PRC_STEP_ALL
489 where ROWID = X_ROWID for update nowait;
490 tlinfo c1%rowtype;
491
492 begin
493 open c1;
494 fetch c1 into tlinfo;
495 if (c1%notfound) then
496 close c1;
497 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
498 IGS_GE_MSG_STACK.ADD;
499 app_exception.raise_exception;
500 return;
501 end if;
502 close c1;
503
504 if ( ((tlinfo.STEP_ORDER_NUM = X_STEP_ORDER_NUM)
505 OR ((tlinfo.STEP_ORDER_NUM is null)
506 AND (X_STEP_ORDER_NUM is null)))
507 ) then
508 null;
509 else
510 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
511 IGS_GE_MSG_STACK.ADD;
512 app_exception.raise_exception;
513 end if;
514 return;
515 end LOCK_ROW;
516
517 procedure UPDATE_ROW (
518 X_ROWID in VARCHAR2,
519 X_ENROLMENT_CAT in VARCHAR2,
520 X_S_STUDENT_COMM_TYPE in VARCHAR2,
521 X_ENR_METHOD_TYPE in VARCHAR2,
522 X_S_ENROLMENT_STEP_TYPE in VARCHAR2,
523 X_STEP_ORDER_NUM in NUMBER,
524 X_MODE in VARCHAR2 default 'R'
525 ) AS
526 X_LAST_UPDATE_DATE DATE;
527 X_LAST_UPDATED_BY NUMBER;
528 X_LAST_UPDATE_LOGIN NUMBER;
529 begin
530 X_LAST_UPDATE_DATE := SYSDATE;
531 if(X_MODE = 'I') then
532 X_LAST_UPDATED_BY := 1;
533 X_LAST_UPDATE_LOGIN := 0;
534 elsif (X_MODE = 'R') then
535 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
536 if X_LAST_UPDATED_BY is NULL then
537 X_LAST_UPDATED_BY := -1;
538 end if;
539 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
540 if X_LAST_UPDATE_LOGIN is NULL then
541 X_LAST_UPDATE_LOGIN := -1;
542 end if;
543 else
544 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
545 IGS_GE_MSG_STACK.ADD;
546 app_exception.raise_exception;
547 end if;
548 Before_DML (
549 p_action => 'UPDATE',
550 x_rowid => X_ROWID,
551 x_enrolment_cat => X_ENROLMENT_CAT,
552 x_s_student_comm_type => X_S_STUDENT_COMM_TYPE,
553 x_enr_method_type => X_ENR_METHOD_TYPE,
554 x_s_enrolment_step_type => X_S_ENROLMENT_STEP_TYPE,
555 x_step_order_num => X_STEP_ORDER_NUM,
556 x_creation_date => X_LAST_UPDATE_DATE,
557 x_created_by => X_LAST_UPDATED_BY,
558 x_last_update_date => X_LAST_UPDATE_DATE,
559 x_last_updated_by => X_LAST_UPDATED_BY,
560 x_last_update_login => X_LAST_UPDATE_LOGIN
561 );
562 update IGS_EN_CAT_PRC_STEP_ALL set
563 STEP_ORDER_NUM = NEW_REFERENCES.STEP_ORDER_NUM,
564 MANDATORY_STEP_IND = NEW_REFERENCES.MANDATORY_STEP_IND,
565 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
566 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
567 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
568 where ROWID = X_ROWID;
569 if (sql%notfound) then
570 raise no_data_found;
571 end if;
572 After_DML (
573 p_action => 'UPDATE',
574 x_rowid => X_ROWID
575 );
576 end UPDATE_ROW;
577
578 procedure ADD_ROW (
579 X_ROWID in out NOCOPY VARCHAR2,
580 X_ENROLMENT_CAT in VARCHAR2,
581 X_S_STUDENT_COMM_TYPE in VARCHAR2,
582 X_ENR_METHOD_TYPE in VARCHAR2,
583 X_S_ENROLMENT_STEP_TYPE in VARCHAR2,
584 X_STEP_ORDER_NUM in NUMBER,
585 X_MODE in VARCHAR2 default 'R',
586 x_org_id IN NUMBER
587 ) AS
588 cursor c1 is select rowid from IGS_EN_CAT_PRC_STEP_ALL
589 where ENROLMENT_CAT = X_ENROLMENT_CAT
590 and S_STUDENT_COMM_TYPE = X_S_STUDENT_COMM_TYPE
591 and ENR_METHOD_TYPE = X_ENR_METHOD_TYPE
592 and S_ENROLMENT_STEP_TYPE = X_S_ENROLMENT_STEP_TYPE
593 ;
594
595 begin
596 open c1;
597 fetch c1 into X_ROWID;
598 if (c1%notfound) then
599 close c1;
600 INSERT_ROW (
601 X_ROWID,
602 X_ENROLMENT_CAT,
603 X_S_STUDENT_COMM_TYPE,
604 X_ENR_METHOD_TYPE,
605 X_S_ENROLMENT_STEP_TYPE,
606 X_STEP_ORDER_NUM,
607 X_MODE,
608 x_org_id );
609 return;
610 end if;
611 close c1;
612 UPDATE_ROW (
613 X_ROWID,
614 X_ENROLMENT_CAT,
615 X_S_STUDENT_COMM_TYPE,
616 X_ENR_METHOD_TYPE,
617 X_S_ENROLMENT_STEP_TYPE,
618 X_STEP_ORDER_NUM,
619 X_MODE );
620 end ADD_ROW;
621
622 procedure DELETE_ROW (
623 X_ROWID in VARCHAR2
624 )AS
625 begin
626 Before_DML (
627 p_action => 'DELETE',
628 x_rowid => X_ROWID
629 );
630 delete from IGS_EN_CAT_PRC_STEP_ALL
631 where ROWID = X_ROWID;
632 if (sql%notfound) then
633 raise no_data_found;
634 end if;
635 After_DML (
636 p_action => 'DELETE',
637 x_rowid => X_ROWID
638 );
639 end DELETE_ROW;
640
641 end IGS_EN_CAT_PRC_STEP_PKG;