[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_PRCS_CAT_STEP_PKG
Source
1 package body IGS_AD_PRCS_CAT_STEP_PKG AS
2 /* $Header: IGSAI37B.pls 115.23 2003/10/30 13:20:14 rghosh ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AD_PRCS_CAT_STEP_ALL%RowType;
5 new_references IGS_AD_PRCS_CAT_STEP_ALL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_org_id IN NUMBER,
11 x_admission_cat IN VARCHAR2 ,
12 x_s_admission_process_type IN VARCHAR2 ,
13 x_s_admission_step_type IN VARCHAR2 ,
14 x_mandatory_step_ind IN VARCHAR2 ,
15 x_step_type_restriction_num IN NUMBER ,
16 x_step_order_num IN NUMBER ,
17 x_step_group_type IN VARCHAR2 ,
18 x_creation_date IN DATE ,
19 x_created_by IN NUMBER ,
20 x_last_update_date IN DATE ,
21 x_last_updated_by IN NUMBER ,
22 x_last_update_login IN NUMBER
23 ) AS
24
25 CURSOR cur_old_ref_values IS
26 SELECT *
27 FROM IGS_AD_PRCS_CAT_STEP_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.org_id := x_org_id;
49 new_references.admission_cat := x_admission_cat;
50 new_references.s_admission_process_type := x_s_admission_process_type;
51 new_references.s_admission_step_type := x_s_admission_step_type;
52 new_references.mandatory_step_ind := x_mandatory_step_ind;
53 new_references.step_type_restriction_num := x_step_type_restriction_num;
54 new_references.step_order_num := x_step_order_num;
55 new_references.step_group_type := x_step_group_type;
56 IF (p_action = 'UPDATE') THEN
57 new_references.creation_date := old_references.creation_date;
58 new_references.created_by := old_references.created_by;
59 ELSE
60 new_references.creation_date := x_creation_date;
61 new_references.created_by := x_created_by;
62 END IF;
63 new_references.last_update_date := x_last_update_date;
64 new_references.last_updated_by := x_last_updated_by;
65 new_references.last_update_login := x_last_update_login;
66
67 END Set_Column_Values;
68
69 PROCEDURE BeforeRowInsertUpdate(
70 p_inserting IN BOOLEAN DEFAULT FALSE,
71 p_updating IN BOOLEAN DEFAULT FALSE
72 ) as
73 v_message_name VARCHAR2(30);
74 BEGIN
75 IF (p_inserting OR (p_updating AND (old_references.s_admission_step_type <> new_references.s_admission_step_type))) THEN
76 IF NOT IGS_TR_VAL_TRI.TRKP_VAL_TRI_TYPE (new_references.s_admission_step_type,
77 v_message_name) THEN
78 Fnd_Message.Set_Name('IGS', v_message_name);
79 IGS_GE_MSG_STACK.ADD;
80 App_Exception.Raise_Exception;
81 END IF;
82 END IF;
83 END BeforeRowInsertUpdate;
84
85 -- Trigger description :-
86 -- "OSS_TST".trg_apcs_br_iud
87 -- BEFORE INSERT OR DELETE OR UPDATE
88 -- ON IGS_AD_PRCS_CAT_STEP
89 -- FOR EACH ROW
90
91 PROCEDURE BeforeRowInsertUpdateDelete1(
92 p_inserting IN BOOLEAN ,
93 p_updating IN BOOLEAN ,
94 p_deleting IN BOOLEAN
95 ) AS
96 v_admission_cat IGS_AD_PRCS_CAT.admission_cat%TYPE;
97 v_message_name VARCHAR2(30);
98 BEGIN
99
100 IF NVL(new_references.step_group_type,'TRACK') <> 'TRACK' THEN
101 IF NVL(p_inserting, FALSE) THEN
102 -- Validate the system admission step type closed indicator.
103 IF IGS_AD_VAL_APCS.admp_val_sasty_clsd (
104 new_references.s_admission_step_type,
105 v_message_name) = FALSE THEN
106 Fnd_Message.Set_Name('IGS',v_message_name);
107 IGS_GE_MSG_STACK.ADD;
108 App_Exception.Raise_Exception;
109 END IF;
110 END IF;
111
112 END IF;
113 -- Set the Admission Category value.
114 IF NVL(p_deleting, FALSE) THEN
115 v_admission_cat := old_references.admission_cat;
116 ELSE
117 v_admission_cat := new_references.admission_cat;
118 END IF;
119 -- Validate the admission category closed indicator.
120 IF IGS_AD_VAL_ACCT.admp_val_ac_closed (
121 v_admission_cat,
122 v_message_name) = FALSE THEN
123 Fnd_Message.Set_Name('IGS',v_message_name);
124 IGS_GE_MSG_STACK.ADD;
125 App_Exception.Raise_Exception;
126 END IF;
127
128 IF NVL(new_references.step_group_type,'TRACK') <> 'TRACK' THEN
129
130 -- If the step_group_type is not 'TRACK' only then perform the
131 -- following checks per bug 2431650 as these are not required for
132 -- step_group_type of 'TRACK'
133
134 -- Validate the Mandatory Step Indicator.
135 IF NVL(p_inserting, FALSE) OR
136 ( NVL(p_updating, FALSE) AND
137 old_references.mandatory_step_ind <> new_references.mandatory_step_ind) THEN
138 IF IGS_AD_VAL_APCS.admp_val_apcs_mndtry (
139 new_references.s_admission_step_type,
140 new_references.mandatory_step_ind,
141 v_message_name) = FALSE THEN
142 Fnd_Message.Set_Name('IGS',v_message_name);
143 IGS_GE_MSG_STACK.ADD;
144 App_Exception.Raise_Exception;
145 END IF;
146 END IF;
147 -- Validate the Step Type Restriction Number.
148 IF NVL(p_inserting, FALSE) OR
149 (NVL(p_updating, FALSE) AND
150 NVL(old_references.step_type_restriction_num, -1) <>
151 NVL(new_references.step_type_restriction_num, -1)) THEN
152 IF IGS_AD_VAL_APCS.admp_val_apcs_rstrct (
153 new_references.s_admission_step_type,
154 new_references.step_type_restriction_num,
155 v_message_name) = FALSE THEN
156 Fnd_Message.Set_Name('IGS',v_message_name);
157 IGS_GE_MSG_STACK.ADD;
158 App_Exception.Raise_Exception;
159 END IF;
160 END IF;
161 -- Validate the Step Order Number.
162 IF NVL(p_inserting, FALSE) OR
163 (NVL(p_updating, FALSE) AND
164 NVL(old_references.step_order_num, -1) <>
165 NVL(new_references.step_order_num, -1)) THEN
166 IF IGS_AD_VAL_APCS.admp_val_apcs_order (
167 new_references.s_admission_step_type,
168 new_references.step_order_num,
169 v_message_name) = FALSE THEN
170 Fnd_Message.Set_Name('IGS',v_message_name);
171 IGS_GE_MSG_STACK.ADD;
172 App_Exception.Raise_Exception;
173 END IF;
174 END IF;
175
176 END IF;
177
178
179 END BeforeRowInsertUpdateDelete1;
180
181 PROCEDURE Check_Constraints (
182 Column_Name IN VARCHAR2,
183 Column_Value IN VARCHAR2
184 )
185 AS
186 BEGIN
187 IF column_name is null then
188 NULL;
189 ELSIF upper(Column_name) = 'MANDATORY_STEP_IND' then
190 new_references.mandatory_step_ind := column_value;
191 ELSIF upper(Column_name) = 'STEP_TYPE_RESTRICTION_NUM' then
192 new_references.step_type_restriction_num := igs_ge_number.to_num(column_value);
193 ELSIF upper(Column_name) = 'STEP_ORDER_NUM' then
194 new_references.step_order_num := igs_ge_number.to_num(column_value);
195 ELSIF upper(Column_name) = 'ADMISSION_CAT' then
196 new_references.admission_cat := column_value;
197 ELSIF upper(Column_name) = 'S_ADMISSION_PROCESS_TYPE' then
198 new_references.s_admission_process_type := column_value;
199 ELSIF upper(Column_name) = 'S_ADMISSION_STEP_TYPE' then
200 new_references.s_admission_step_type := column_value;
201 ELSIF upper(Column_name) = 'STEP_GROUP_TYPE' then
202 new_references.step_group_type := column_value;
203
204 END IF;
205
206
207 IF upper(column_name) = 'MANDATORY_STEP_IND' OR
208 column_name is null Then
209 IF new_references.mandatory_step_ind NOT IN ('Y','N') Then
210 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
211 IGS_GE_MSG_STACK.ADD;
212 App_Exception.Raise_Exception;
213 END IF;
214 END IF;
215
216 IF upper(column_name) = 'STEP_TYPE_RESTRICTION_NUM' OR
217 column_name is null Then
218 IF new_references.step_type_restriction_num < 1 OR
219 new_references.step_type_restriction_num > 99 Then
220 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
221 IGS_GE_MSG_STACK.ADD;
222 App_Exception.Raise_Exception;
223 END IF;
224 END IF;
225
226 IF upper(column_name) = 'STEP_ORDER_NUM' OR
227 column_name is null Then
228 IF new_references.step_order_num < 1 OR
229 new_references.step_order_num > 999 Then
230 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
231 IGS_GE_MSG_STACK.ADD;
232 App_Exception.Raise_Exception;
233 END IF;
234 END IF;
235 IF upper(column_name) = 'ADMISSION_CAT' OR
236 column_name is null Then
237 IF new_references.admission_cat <>
238 UPPER(new_references.admission_cat) Then
239 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
240 IGS_GE_MSG_STACK.ADD;
241 App_Exception.Raise_Exception;
242 END IF;
243 END IF;
244 IF upper(column_name) = 'MANDATORY_STEP_IND' OR
245 column_name is null Then
246 IF new_references.mandatory_step_ind <>
247 UPPER(new_references.mandatory_step_ind) Then
248 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
249 IGS_GE_MSG_STACK.ADD;
250 App_Exception.Raise_Exception;
251 END IF;
252 END IF;
253 IF upper(column_name) = 'S_ADMISSION_PROCESS_TYPE' OR
254 column_name is null Then
255 IF new_references.s_admission_process_type <>
256 UPPER(new_references.s_admission_process_type) Then
257 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
258 IGS_GE_MSG_STACK.ADD;
259 App_Exception.Raise_Exception;
260 END IF;
261 END IF;
262 IF upper(column_name) = 'S_ADMISSION_STEP_TYPE' OR
263 column_name is null Then
264 IF new_references.s_admission_step_type <>
265 UPPER(new_references.s_admission_step_type) Then
266 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
267 IGS_GE_MSG_STACK.ADD;
268 App_Exception.Raise_Exception;
269 END IF;
270 END IF;
271
272 IF upper(column_name) = 'STEP_GROUP_TYPE' OR
273 column_name is null Then
274 IF new_references.step_group_type <>
275 UPPER(new_references.step_group_type) Then
276 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
277 IGS_GE_MSG_STACK.ADD;
278 App_Exception.Raise_Exception;
279 END IF;
280 END IF;
281
282 IF upper(column_name) = 'STEP_GROUP_TYPE' OR
283 column_name is null Then
284 IF new_references.step_group_type IS NULL Then
285 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MANDATORY_FLD');
286 IGS_GE_MSG_STACK.ADD;
287 App_Exception.Raise_Exception;
288 END IF;
289 END IF;
290
291
292 END Check_Constraints;
293
294
295
296 PROCEDURE Check_Parent_Existance AS
297 CURSOR cur_s_tracking_type IS
298 SELECT S_TRACKING_TYPE
299 FROM IGS_TR_TYPE
300 WHERE TRACKING_TYPE = new_references.s_admission_step_type;
301
302 s_tracking_type_rec cur_s_tracking_type%ROWTYPE;
303 BEGIN
304 /*=======================================================================+
305 --
306 -- HISTORY
307 -- nsinha 03-Aug-2001 BUG Enh No : 1905651 Added
308 -- cur_s_tracking_type check.
309 *=======================================================================*/
310
311 IF (((old_references.admission_cat = new_references.admission_cat) AND
312 (old_references.s_admission_process_type = new_references.s_admission_process_type)) OR
313 ((new_references.admission_cat IS NULL) OR
314 (new_references.s_admission_process_type IS NULL))) THEN
315 NULL;
316 ELSE
317 IF NOT IGS_AD_PRCS_CAT_PKG.Get_PK_For_Validation (
318 new_references.admission_cat,
319 new_references.s_admission_process_type,
320 'N'
321 ) THEN
322 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
323 IGS_GE_MSG_STACK.ADD;
324 App_Exception.Raise_Exception;
325 END IF;
326 END IF;
327
328 IF (((old_references.s_admission_step_type = new_references.s_admission_step_type)) OR
329 ((new_references.s_admission_step_type IS NULL))) THEN
330 NULL;
331 ELSE
332 OPEN cur_s_tracking_type;
333 FETCH cur_s_tracking_type INTO s_tracking_type_rec;
334
335 IF cur_s_tracking_type%NOTFOUND THEN
336 -- Record is not for TRACKING.
337 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation(
338 'ADMISSION_STEP_TYPE',
339 new_references.s_admission_step_type
340 ) THEN
341 CLOSE cur_s_tracking_type;
342 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
343 IGS_GE_MSG_STACK.ADD;
344 App_Exception.Raise_Exception;
345 END IF;
346 END IF;
347 CLOSE cur_s_tracking_type;
348 END IF;
349 END Check_Parent_Existance;
350
351 FUNCTION Get_PK_For_Validation (
352 x_admission_cat IN VARCHAR2,
353 x_s_admission_process_type IN VARCHAR2,
354 x_s_admission_step_type IN VARCHAR2,
355 x_step_group_type IN VARCHAR2
356 )
357 RETURN BOOLEAN
358 AS
359
360 CURSOR cur_rowid IS
361 SELECT rowid
362 FROM IGS_AD_PRCS_CAT_STEP_ALL
363 WHERE admission_cat = x_admission_cat
364 AND s_admission_process_type = x_s_admission_process_type
365 AND s_admission_step_type = x_s_admission_step_type
366 AND step_group_type = x_step_group_type
367 FOR UPDATE NOWAIT;
368
369 lv_rowid cur_rowid%RowType;
370
371 BEGIN
372
373 Open cur_rowid;
374 Fetch cur_rowid INTO lv_rowid;
375 IF (cur_rowid%FOUND) THEN
376 Close cur_rowid;
377 Return (TRUE);
378 ELSE
379 Close cur_rowid;
380 Return (FALSE);
381 END IF;
382 END Get_PK_For_Validation;
383
384 PROCEDURE GET_FK_IGS_AD_PRCS_CAT (
385 x_admission_cat IN VARCHAR2,
386 x_s_admission_process_type IN VARCHAR2
387 ) AS
388
389 CURSOR cur_rowid IS
390 SELECT rowid
391 FROM IGS_AD_PRCS_CAT_STEP_ALL
392 WHERE admission_cat = x_admission_cat
393 AND s_admission_process_type = x_s_admission_process_type ;
394
395 lv_rowid cur_rowid%RowType;
396
397 BEGIN
398
399 Open cur_rowid;
400 Fetch cur_rowid INTO lv_rowid;
401 IF (cur_rowid%FOUND) THEN
402 Close cur_rowid;
403 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCS_APC_FK');
404 IGS_GE_MSG_STACK.ADD;
405 App_Exception.Raise_Exception;
406 Return;
407 END IF;
408 Close cur_rowid;
409
410 END GET_FK_IGS_AD_PRCS_CAT;
411
412 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW(
413 x_s_admission_step_type IN VARCHAR2
414 ) AS
415
416 CURSOR cur_rowid IS
417 SELECT rowid
418 FROM IGS_AD_PRCS_CAT_STEP_ALL
419 WHERE s_admission_step_type = x_s_admission_step_type
420 AND step_group_type <> 'TRACK';
421
422 lv_rowid cur_rowid%RowType;
423
424 BEGIN
425
426 Open cur_rowid;
427 Fetch cur_rowid INTO lv_rowid;
428 IF (cur_rowid%FOUND) THEN
429 Close cur_rowid;
430 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCS_SLV_FK');
431 IGS_GE_MSG_STACK.ADD;
432 App_Exception.Raise_Exception;
433 Return;
434 END IF;
435 Close cur_rowid;
436
437 END GET_FK_IGS_LOOKUPS_VIEW;
438
439 PROCEDURE Before_DML (
440 p_action IN VARCHAR2,
441 x_rowid IN VARCHAR2 ,
442 x_org_id IN NUMBER,
443 x_admission_cat IN VARCHAR2 ,
444 x_s_admission_process_type IN VARCHAR2 ,
445 x_s_admission_step_type IN VARCHAR2 ,
446 x_mandatory_step_ind IN VARCHAR2 ,
447 x_step_type_restriction_num IN NUMBER ,
448 x_step_order_num IN NUMBER ,
449 x_step_group_type IN VARCHAR2 ,
450 x_creation_date IN DATE ,
451 x_created_by IN NUMBER ,
452 x_last_update_date IN DATE ,
453 x_last_updated_by IN NUMBER ,
454 x_last_update_login IN NUMBER
455 ) AS
456 BEGIN
457
458 Set_Column_Values (
459 p_action,
460 x_rowid,
461 x_org_id,
462 x_admission_cat,
463 x_s_admission_process_type,
464 x_s_admission_step_type,
465 x_mandatory_step_ind,
466 x_step_type_restriction_num,
467 x_step_order_num,
468 x_step_group_type,
469 x_creation_date,
470 x_created_by,
471 x_last_update_date,
472 x_last_updated_by,
473 x_last_update_login
474 );
475
476 IF (p_action = 'INSERT') THEN
477 BeforeRowInsertUpdate(p_inserting => TRUE);
478 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE, p_updating => FALSE, p_deleting => FALSE );
479 IF Get_PK_For_Validation (
480 new_references.admission_cat,
481 new_references.s_admission_process_type,
482 new_references.s_admission_step_type,
483 new_references.step_group_type
484 ) THEN
485 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
486 IGS_GE_MSG_STACK.ADD;
487 App_Exception.Raise_Exception;
488 END IF;
489 Check_Constraints;
490 Check_Parent_Existance;
491 ELSIF (p_action = 'UPDATE') THEN
492 BeforeRowInsertUpdate(p_updating => TRUE);
493 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE, p_inserting => FALSE, p_deleting => FALSE );
494 Check_Constraints;
495 Check_Parent_Existance;
496 ELSIF (p_action = 'DELETE') THEN
497 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE,p_inserting => FALSE,p_updating => FALSE);
498 ELSIF (p_action = 'VALIDATE_INSERT') THEN
499 IF Get_PK_For_Validation (
500 new_references.admission_cat,
501 new_references.s_admission_process_type,
502 new_references.s_admission_step_type,
503 new_references.step_group_type
504 ) THEN
505 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
506 IGS_GE_MSG_STACK.ADD;
507 App_Exception.Raise_Exception;
508 END IF;
509 Check_Constraints;
510 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
511 Check_Constraints;
512 END IF;
513 END Before_DML;
514
515 PROCEDURE After_DML (
516 p_action IN VARCHAR2,
517 x_rowid IN VARCHAR2
518 ) AS
519 BEGIN
520
521 l_rowid := x_rowid;
522
523 END After_DML;
524
525 procedure INSERT_ROW (
526 X_ROWID in out NOCOPY VARCHAR2,
527 X_ORG_ID IN NUMBER,
528 X_ADMISSION_CAT in VARCHAR2,
529 X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
530 X_S_ADMISSION_STEP_TYPE in VARCHAR2,
531 X_MANDATORY_STEP_IND in VARCHAR2,
532 X_STEP_TYPE_RESTRICTION_NUM in NUMBER,
533 X_STEP_ORDER_NUM in NUMBER,
534 X_STEP_GROUP_TYPE in VARCHAR2,
535 X_MODE in VARCHAR2
536 ) AS
537 /*-----------------------------------------------------------------------------------
538 --History
539 --Who When What
540 --sbaliga 12-feb-2002 Modified call to before_dml by assigning to x_org_id
541 -- the value of function igs_ge_gen_003.get_org_id
542 -- as part of SWCR006 build.
543 ---------------------------------------------------------------------------------------*/
544
545 cursor C is select ROWID from IGS_AD_PRCS_CAT_STEP_ALL
546 where ADMISSION_CAT = X_ADMISSION_CAT
547 and S_ADMISSION_PROCESS_TYPE = X_S_ADMISSION_PROCESS_TYPE
548 and S_ADMISSION_STEP_TYPE = X_S_ADMISSION_STEP_TYPE
549 and STEP_GROUP_TYPE = X_STEP_GROUP_TYPE;
550 X_LAST_UPDATE_DATE DATE;
551 X_LAST_UPDATED_BY NUMBER;
552 X_LAST_UPDATE_LOGIN NUMBER;
553 begin
554 X_LAST_UPDATE_DATE := SYSDATE;
555 if(X_MODE = 'I') then
556 X_LAST_UPDATED_BY := 1;
557 X_LAST_UPDATE_LOGIN := 0;
558 elsif (X_MODE = 'R') then
559 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
560 if X_LAST_UPDATED_BY is NULL then
561 X_LAST_UPDATED_BY := -1;
562 end if;
563 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
564 if X_LAST_UPDATE_LOGIN is NULL then
565 X_LAST_UPDATE_LOGIN := -1;
566 end if;
567 else
568 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
569 IGS_GE_MSG_STACK.ADD;
570 app_exception.raise_exception;
571 end if;
572 Before_DML(p_action =>'INSERT',
573 x_rowid =>X_ROWID,
574 x_org_id => igs_ge_gen_003.get_org_id,
575 x_admission_cat => X_ADMISSION_CAT,
576 x_s_admission_process_type => X_S_ADMISSION_PROCESS_TYPE,
577 x_s_admission_step_type => X_S_ADMISSION_STEP_TYPE,
578 x_mandatory_step_ind => NVL(X_MANDATORY_STEP_IND,'Y'),
579 x_step_type_restriction_num => X_STEP_TYPE_RESTRICTION_NUM,
580 x_step_order_num => X_STEP_ORDER_NUM,
581 x_step_group_type => X_STEP_GROUP_TYPE,
582 x_creation_date => X_LAST_UPDATE_DATE,
583 x_created_by => X_LAST_UPDATED_BY,
584 x_last_update_date => X_LAST_UPDATE_DATE,
585 x_last_updated_by => X_LAST_UPDATED_BY,
586 x_last_update_login => X_LAST_UPDATE_LOGIN
587 );
588
589 insert into IGS_AD_PRCS_CAT_STEP_ALL (
590 ORG_ID,
591 ADMISSION_CAT,
592 S_ADMISSION_PROCESS_TYPE,
593 S_ADMISSION_STEP_TYPE,
594 MANDATORY_STEP_IND,
595 STEP_TYPE_RESTRICTION_NUM,
596 STEP_ORDER_NUM,
597 STEP_GROUP_TYPE,
598 CREATION_DATE,
599 CREATED_BY,
600 LAST_UPDATE_DATE,
601 LAST_UPDATED_BY,
602 LAST_UPDATE_LOGIN
603 ) values (
604 NEW_REFERENCES.ORG_ID,
605 NEW_REFERENCES.ADMISSION_CAT,
606 NEW_REFERENCES.S_ADMISSION_PROCESS_TYPE,
607 NEW_REFERENCES.S_ADMISSION_STEP_TYPE,
608 NEW_REFERENCES.MANDATORY_STEP_IND,
609 NEW_REFERENCES.STEP_TYPE_RESTRICTION_NUM,
610 NEW_REFERENCES.STEP_ORDER_NUM,
611 NEW_REFERENCES.STEP_GROUP_TYPE,
612 X_LAST_UPDATE_DATE,
613 X_LAST_UPDATED_BY,
614 X_LAST_UPDATE_DATE,
615 X_LAST_UPDATED_BY,
616 X_LAST_UPDATE_LOGIN
617 );
618
619 open c;
620 fetch c into X_ROWID;
621 if (c%notfound) then
622 close c;
623 raise no_data_found;
624 end if;
625 close c;
626 After_DML(
627 p_action =>'INSERT',
628 x_rowid => X_ROWID
629 );
630 end INSERT_ROW;
631
632 procedure LOCK_ROW (
633 X_ROWID in VARCHAR2,
634 X_ADMISSION_CAT in VARCHAR2,
635 X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
636 X_S_ADMISSION_STEP_TYPE in VARCHAR2,
637 X_MANDATORY_STEP_IND in VARCHAR2,
638 X_STEP_TYPE_RESTRICTION_NUM in NUMBER,
639 X_STEP_ORDER_NUM in NUMBER,
640 X_STEP_GROUP_TYPE in VARCHAR2
641 ) AS
642 cursor c1 is select
643 MANDATORY_STEP_IND,
644 STEP_TYPE_RESTRICTION_NUM,
645 STEP_ORDER_NUM
646 from IGS_AD_PRCS_CAT_STEP_ALL
647 where ROWID = X_ROWID for update nowait;
648 tlinfo c1%rowtype;
649
650 begin
651 open c1;
652 fetch c1 into tlinfo;
653 if (c1%notfound) then
654 close c1;
655 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
656 IGS_GE_MSG_STACK.ADD;
657 app_exception.raise_exception;
658 return;
659 end if;
660 close c1;
661
662 if ( (tlinfo.MANDATORY_STEP_IND = X_MANDATORY_STEP_IND)
663 AND ((tlinfo.STEP_TYPE_RESTRICTION_NUM = X_STEP_TYPE_RESTRICTION_NUM)
664 OR ((tlinfo.STEP_TYPE_RESTRICTION_NUM is null)
665 AND (X_STEP_TYPE_RESTRICTION_NUM is null)))
666 AND ((tlinfo.STEP_ORDER_NUM = X_STEP_ORDER_NUM)
667 OR ((tlinfo.STEP_ORDER_NUM is null)
668 AND (X_STEP_ORDER_NUM is null)))
669 ) then
670 null;
671 else
672 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
673 IGS_GE_MSG_STACK.ADD;
674 app_exception.raise_exception;
675 end if;
676 return;
677 end LOCK_ROW;
678
679 procedure UPDATE_ROW (
680 X_ROWID in VARCHAR2,
681 X_ADMISSION_CAT in VARCHAR2,
682 X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
683 X_S_ADMISSION_STEP_TYPE in VARCHAR2,
684 X_MANDATORY_STEP_IND in VARCHAR2,
685 X_STEP_TYPE_RESTRICTION_NUM in NUMBER,
686 X_STEP_ORDER_NUM in NUMBER,
687 X_STEP_GROUP_TYPE in VARCHAR2,
688 X_MODE in VARCHAR2
689 ) AS
690 /*-----------------------------------------------------------------------------------
691 --History
692 --Who When What
693 --sbaliga 12-feb-2002 Modified call to before_dml by assigning to x_org_id
694 -- the value of function igs_ge_gen_003.get_org_id
695 -- as part of SWCR006 build.
696 ---------------------------------------------------------------------------------------*/
697 X_LAST_UPDATE_DATE DATE;
698 X_LAST_UPDATED_BY NUMBER;
699 X_LAST_UPDATE_LOGIN NUMBER;
700 begin
701 X_LAST_UPDATE_DATE := SYSDATE;
702 if(X_MODE = 'I') then
703 X_LAST_UPDATED_BY := 1;
704 X_LAST_UPDATE_LOGIN := 0;
705 elsif (X_MODE = 'R') then
706 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
707 if X_LAST_UPDATED_BY is NULL then
708 X_LAST_UPDATED_BY := -1;
709 end if;
710 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
711 if X_LAST_UPDATE_LOGIN is NULL then
712 X_LAST_UPDATE_LOGIN := -1;
713 end if;
714 else
715 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
716 IGS_GE_MSG_STACK.ADD;
717 app_exception.raise_exception;
718 end if;
719 Before_DML(p_action =>'UPDATE',
720 x_rowid =>X_ROWID,
721 x_org_id => igs_ge_gen_003.get_org_id,
722 x_admission_cat => X_ADMISSION_CAT,
723 x_s_admission_process_type => X_S_ADMISSION_PROCESS_TYPE,
724 x_s_admission_step_type => X_S_ADMISSION_STEP_TYPE,
725 x_mandatory_step_ind => X_MANDATORY_STEP_IND,
726 x_step_type_restriction_num => X_STEP_TYPE_RESTRICTION_NUM,
727 x_step_order_num => X_STEP_ORDER_NUM,
728 x_step_group_type => X_STEP_GROUP_TYPE,
729 x_creation_date => X_LAST_UPDATE_DATE,
730 x_created_by => X_LAST_UPDATED_BY,
731 x_last_update_date => X_LAST_UPDATE_DATE,
732 x_last_updated_by => X_LAST_UPDATED_BY,
733 x_last_update_login => X_LAST_UPDATE_LOGIN
734 );
735
736 update IGS_AD_PRCS_CAT_STEP_ALL set
737 MANDATORY_STEP_IND = NEW_REFERENCES.MANDATORY_STEP_IND,
738 STEP_TYPE_RESTRICTION_NUM = NEW_REFERENCES.STEP_TYPE_RESTRICTION_NUM,
739 STEP_ORDER_NUM = NEW_REFERENCES.STEP_ORDER_NUM,
740 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
741 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
742 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
743 where ROWID = X_ROWID
744 ;
745 if (sql%notfound) then
746 raise no_data_found;
747 end if;
748 After_DML(
749 p_action =>'UPDATE',
750 x_rowid => X_ROWID
751 );
752 end UPDATE_ROW;
753
754 procedure ADD_ROW (
755 X_ROWID in out NOCOPY VARCHAR2,
756 X_ORG_ID in NUMBER,
757 X_ADMISSION_CAT in VARCHAR2,
758 X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
759 X_S_ADMISSION_STEP_TYPE in VARCHAR2,
760 X_MANDATORY_STEP_IND in VARCHAR2,
761 X_STEP_TYPE_RESTRICTION_NUM in NUMBER,
762 X_STEP_ORDER_NUM in NUMBER,
763 X_STEP_GROUP_TYPE in VARCHAR2,
764 X_MODE in VARCHAR2
765 ) AS
766 cursor c1 is select rowid from IGS_AD_PRCS_CAT_STEP_ALL
767 where ADMISSION_CAT = X_ADMISSION_CAT
768 and S_ADMISSION_PROCESS_TYPE = X_S_ADMISSION_PROCESS_TYPE
769 and S_ADMISSION_STEP_TYPE = X_S_ADMISSION_STEP_TYPE
770 and step_group_Type = X_STEP_GROUP_TYPE ;
771 begin
772 open c1;
773 fetch c1 into X_ROWID;
774 if (c1%notfound) then
775 close c1;
776 INSERT_ROW (
777 X_ROWID,
778 X_ORG_ID,
779 X_ADMISSION_CAT,
780 X_S_ADMISSION_PROCESS_TYPE,
781 X_S_ADMISSION_STEP_TYPE,
782 X_MANDATORY_STEP_IND,
783 X_STEP_TYPE_RESTRICTION_NUM,
784 X_STEP_ORDER_NUM,
785 X_STEP_GROUP_TYPE,
786 X_MODE);
787 return;
788 end if;
789 close c1;
790 UPDATE_ROW (
791 X_ROWID,
792 X_ADMISSION_CAT,
793 X_S_ADMISSION_PROCESS_TYPE,
794 X_S_ADMISSION_STEP_TYPE,
795 X_MANDATORY_STEP_IND,
796 X_STEP_TYPE_RESTRICTION_NUM,
797 X_STEP_ORDER_NUM,
798 X_MODE);
799 end ADD_ROW;
800
801 procedure DELETE_ROW (
802 X_ROWID in VARCHAR2
803 ) AS
804 begin
805 Before_DML(
806 p_action =>'DELETE',
807 x_rowid => X_ROWID,
808 x_org_id => NULL
809 );
810
811 delete from IGS_AD_PRCS_CAT_STEP_ALL
812 where ROWID = X_ROWID;
813 if (sql%notfound) then
814 raise no_data_found;
815 end if;
816 After_DML(
817 p_action =>'DELETE',
818 x_rowid => X_ROWID
819 );
820 end DELETE_ROW;
821
822 end IGS_AD_PRCS_CAT_STEP_PKG;