[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_PAT_STUDY_UNT_PKG
Source
1 package body IGS_PS_PAT_STUDY_UNT_PKG as
2 /* $Header: IGSPI63B.pls 120.0 2005/06/01 20:09:40 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_PS_PAT_STUDY_UNT%RowType;
5 new_references IGS_PS_PAT_STUDY_UNT%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_course_cd IN VARCHAR2 ,
11 x_version_number IN NUMBER ,
12 x_cal_type IN VARCHAR2 ,
13 x_pos_sequence_number IN NUMBER ,
14 x_posp_sequence_number IN NUMBER ,
15 x_sequence_number IN NUMBER ,
16 x_unit_cd IN VARCHAR2 ,
17 x_unit_location_cd IN VARCHAR2 ,
18 x_unit_class IN VARCHAR2 ,
19 x_description IN VARCHAR2 ,
20 x_creation_date IN DATE ,
21 x_created_by IN NUMBER ,
22 x_last_update_date IN DATE ,
23 x_last_updated_by IN NUMBER ,
24 x_last_update_login IN NUMBER ,
25 X_CORE_IND IN VARCHAR2
26 ) AS
27
28 CURSOR cur_old_ref_values IS
29 SELECT *
30 FROM IGS_PS_PAT_STUDY_UNT
31 WHERE rowid = x_rowid;
32
33 BEGIN
34
35 l_rowid := x_rowid;
36
37 -- Code for setting the Old and New Reference Values.
38 -- Populate Old Values.
39 Open cur_old_ref_values;
40 Fetch cur_old_ref_values INTO old_references;
41 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
42 Close cur_old_ref_values;
43 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
44 IGS_GE_MSG_STACK.ADD;
45 App_Exception.Raise_Exception;
46 Return;
47 END IF;
48 Close cur_old_ref_values;
49
50 -- Populate New Values.
51 new_references.course_cd := x_course_cd;
52 new_references.version_number := x_version_number;
53 new_references.cal_type := x_cal_type;
54 new_references.pos_sequence_number := x_pos_sequence_number;
55 new_references.posp_sequence_number := x_posp_sequence_number;
56 new_references.sequence_number := x_sequence_number;
57 new_references.unit_cd := x_unit_cd;
58 new_references.unit_location_cd := x_unit_location_cd;
59 new_references.unit_class := x_unit_class;
60 new_references.description := x_description;
61 new_references.core_ind := x_core_ind;
62 IF (p_action = 'UPDATE') THEN
63 new_references.creation_date := old_references.creation_date;
64 new_references.created_by := old_references.created_by;
65 ELSE
66 new_references.creation_date := x_creation_date;
67 new_references.created_by := x_created_by;
68 END IF;
69 new_references.last_update_date := x_last_update_date;
70 new_references.last_updated_by := x_last_updated_by;
71 new_references.last_update_login := x_last_update_login;
72
73 END Set_Column_Values;
74
75 PROCEDURE BeforeRowInsertUpdateDelete1(
76 p_inserting IN BOOLEAN,
77 p_updating IN BOOLEAN,
78 p_deleting IN BOOLEAN
79 ) AS
80 v_message_name VARCHAR2(30);
81 BEGIN
82 -- Validate the insert/update/delete
83 IF p_inserting OR p_updating THEN
84 IF IGS_PS_VAL_CRS.crsp_val_iud_crv_dtl(
85 new_references.course_cd,
86 new_references.version_number,
87 v_message_name) = FALSE THEN
88 Fnd_Message.Set_Name('IGS', v_message_name);
89 IGS_GE_MSG_STACK.ADD;
90 App_Exception.Raise_Exception;
91 END IF;
92 ELSE
93 IF IGS_PS_VAL_CRS.crsp_val_iud_crv_dtl(
94 old_references.course_cd,
95 old_references.version_number,
96 v_message_name) = FALSE THEN
97 Fnd_Message.Set_Name('IGS', v_message_name);
98 IGS_GE_MSG_STACK.ADD;
99 App_Exception.Raise_Exception;
100 END IF;
101 END IF;
102 -- Validate the insert/update
103 IF p_inserting OR p_updating THEN
104 -- Validate the UnitCode
105 IF (new_references.unit_cd IS NOT NULL AND (p_inserting OR
106 (p_updating AND new_references.unit_cd <> old_references.unit_cd))) THEN
107 IF IGS_PS_VAL_POSu.crsp_val_uv_active (
108 new_references.unit_cd,
109 v_message_name) = FALSE THEN
110 Fnd_Message.Set_Name('IGS', v_message_name);
111 IGS_GE_MSG_STACK.ADD;
112 App_Exception.Raise_Exception;
113 END IF;
114 END IF;
115 -- Validate the IGS_PS_UNIT IGS_AD_LOCATION Code
116 IF (new_references.unit_location_cd IS NOT NULL AND (p_inserting OR
117 (p_updating AND new_references.unit_location_cd <> old_references.unit_location_cd))) THEN
118
119 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_POSu.crsp_val_loc_cd
120 IF IGS_PS_VAL_UOO.crsp_val_loc_cd (
121 new_references.unit_location_cd,
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 END IF;
128 -- Validate the IGS_PS_UNIT Class
129 IF (new_references.unit_class IS NOT NULL AND (p_inserting OR
130 (p_updating AND new_references.unit_class<> old_references.unit_class))) THEN
131 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_POSu.crsp_val_ucl_closed
132 IF IGS_AS_VAL_UAI.crsp_val_ucl_closed (
133 new_references.unit_class,
134 v_message_name) = FALSE THEN
135 Fnd_Message.Set_Name('IGS', v_message_name);
136 IGS_GE_MSG_STACK.ADD;
137 App_Exception.Raise_Exception;
138 END IF;
139 END IF;
140 -- Validate the record has the required data
141 IF IGS_PS_VAL_POSu.crsp_val_posu_rqrd (
142 new_references.unit_cd,
143 new_references.unit_location_cd,
144 new_references.unit_class,
145 new_references.description,
146 v_message_name) = FALSE THEN
147 Fnd_Message.Set_Name('IGS', v_message_name);
148 IGS_GE_MSG_STACK.ADD;
149 App_Exception.Raise_Exception;
150 END IF;
151 END IF;
152
153
154 END BeforeRowInsertUpdateDelete1;
155
156 PROCEDURE AfterRowInsertUpdate2(
157 p_inserting IN BOOLEAN,
158 p_updating IN BOOLEAN,
159 p_deleting IN BOOLEAN
160 ) AS
161 v_message_name VARCHAR2(30);
162 cst_error VARCHAR2(1);
163 v_return_type VARCHAR2(1);
164 BEGIN
165 cst_error := 'E';
166 -- Validate the pattern of study record
167 IF IGS_PS_VAL_POSu.crsp_val_posu_iu(
168 new_references.course_cd,
169 new_references.version_number,
170 new_references.cal_type,
171 new_references.pos_sequence_number,
172 new_references.posp_sequence_number,
173 new_references.sequence_number,
174 new_references.unit_cd,
175 v_return_type,
176 v_message_name,
177 new_references.unit_location_cd,
178 new_references.unit_class) = FALSE THEN
179 IF v_return_type = cst_error THEN
180 Fnd_Message.Set_Name('IGS', v_message_name);
181 IGS_GE_MSG_STACK.ADD;
182 App_Exception.Raise_Exception;
183 END IF;
184 END IF;
185
186 END AfterRowInsertUpdate2;
187
188 PROCEDURE Check_Constraints (
189 Column_Name IN VARCHAR2,
190 Column_Value IN VARCHAR2
191 )
192 AS
193 BEGIN
194
195 IF column_name is null then
196 NULL;
197 ELSIF upper(Column_name) = 'POS_SEQUENCE_NUMBER' then
198 new_references.pos_sequence_number :=IGS_GE_NUMBER.TO_NUM(column_value);
199 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' then
200 new_references.sequence_number := IGS_GE_NUMBER.TO_NUM(column_value);
201 ELSIF upper(Column_name) = 'POSP_SEQUENCE_NUMBER' then
202 new_references.posp_sequence_number := IGS_GE_NUMBER.TO_NUM(column_value);
203 ELSIF upper(Column_name) = 'VERSION_NUMBER' then
204 new_references.version_number :=IGS_GE_NUMBER.TO_NUM(column_value);
205 ELSIF upper(Column_name) = 'CAL_TYPE' then
206 new_references.cal_type := column_value;
207 ELSIF upper(Column_name) = 'COURSE_CD' then
208 new_references.course_cd := column_value;
209 ELSIF upper(Column_name) = 'UNIT_CD' then
210 new_references.unit_cd := column_value;
211 ELSIF upper(Column_name) = 'UNIT_CLASS' then
212 new_references.unit_class := column_value;
213 ELSIF upper(Column_name) = 'UNIT_LOCATION_CD' then
214 new_references.unit_location_cd := column_value;
215 ELSIF upper(Column_name) = 'CORE_IND' then
216 new_references.core_ind := column_value;
217 END IF;
218
219 IF upper(column_name) = 'POS_SEQUENCE_NUMBER' OR
220 column_name is null Then
221 IF new_references.pos_sequence_number < 0 OR new_references.pos_sequence_number > 999999 Then
222 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
223 IGS_GE_MSG_STACK.ADD;
224 App_Exception.Raise_Exception;
225 END IF;
226 END IF;
227
228 IF upper(column_name) = 'SEQUENCE_NUMBER' OR
229 column_name is null Then
230 IF new_references.sequence_number < 0 OR new_references.sequence_number > 999999 Then
231 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
232 IGS_GE_MSG_STACK.ADD;
233 App_Exception.Raise_Exception;
234 END IF;
235 END IF;
236
237 IF upper(column_name) = 'POSP_SEQUENCE_NUMBER' OR
238 column_name is null Then
239 IF new_references.posp_sequence_number < 0 OR new_references.posp_sequence_number > 999999 Then
240 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
241 IGS_GE_MSG_STACK.ADD;
242 App_Exception.Raise_Exception;
243 END IF;
244 END IF;
245
246 IF upper(column_name) = 'VERSION_NUMBER' OR
247 column_name is null Then
248 IF new_references.version_number < 0 OR new_references.version_number > 999 Then
249 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
250 IGS_GE_MSG_STACK.ADD;
251 App_Exception.Raise_Exception;
252 END IF;
253 END IF;
254
255 IF upper(column_name) = 'CAL_TYPE' OR
256 column_name is null Then
257 IF new_references.cal_type <> UPPER(new_references.cal_type) Then
258 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
259 IGS_GE_MSG_STACK.ADD;
260 App_Exception.Raise_Exception;
261 END IF;
262 END IF;
263
264 IF upper(column_name) = 'COURSE_CD' OR
265 column_name is null Then
266 IF new_references.course_cd <> UPPER(new_references.course_cd) Then
267 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
268 IGS_GE_MSG_STACK.ADD;
269 App_Exception.Raise_Exception;
270 END IF;
271 END IF;
272
273 IF upper(column_name) = 'UNIT_CD' OR
274 column_name is null Then
275 IF new_references.unit_cd <> UPPER(new_references.unit_cd) 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) = 'UNIT_CLASS' OR
283 column_name is null Then
284 IF new_references.unit_class <> UPPER(new_references.unit_class) Then
285 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
286 IGS_GE_MSG_STACK.ADD;
287 App_Exception.Raise_Exception;
288 END IF;
289 END IF;
290
291 IF upper(column_name) = 'UNIT_LOCATION_CD' OR
292 column_name is null Then
293 IF new_references.unit_location_cd <> UPPER(new_references.unit_location_cd) Then
294 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
295 IGS_GE_MSG_STACK.ADD;
296 App_Exception.Raise_Exception;
297 END IF;
298 END IF;
299
300 IF upper(column_name) = 'CORE_IND' OR
301 column_name is null Then
302 IF new_references.core_ind NOT IN ('Y','N') Then
303 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
304 IGS_GE_MSG_STACK.ADD;
305 App_Exception.Raise_Exception;
306 END IF;
307 END IF;
308
309 END check_constraints;
310
311
312 PROCEDURE Check_Parent_Existance AS
313 BEGIN
314
315 IF (((old_references.unit_location_cd = new_references.unit_location_cd)) OR
316 ((new_references.unit_location_cd IS NULL))) THEN
317 NULL;
318 ELSE
319 IF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
320 new_references.unit_location_cd ,
321 'N'
322 ) THEN
323 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
324 IGS_GE_MSG_STACK.ADD;
325 App_Exception.Raise_Exception;
326 END IF;
327 END IF;
328
329 IF (((old_references.course_cd = new_references.course_cd) AND
330 (old_references.version_number = new_references.version_number) AND
331 (old_references.cal_type = new_references.cal_type) AND
332 (old_references.pos_sequence_number = new_references.pos_sequence_number) AND
333 (old_references.posp_sequence_number = new_references.posp_sequence_number)) OR
334 ((new_references.course_cd IS NULL) OR
335 (new_references.version_number IS NULL) OR
336 (new_references.cal_type IS NULL) OR
337 (new_references.pos_sequence_number IS NULL) OR
338 (new_references.posp_sequence_number IS NULL))) THEN
339 NULL;
340 ELSE
341 IF NOT IGS_PS_PAT_STUDY_PRD_PKG.Get_PK_For_Validation (
342 new_references.course_cd,
343 new_references.version_number,
344 new_references.cal_type,
345 new_references.pos_sequence_number,
346 new_references.posp_sequence_number
347 ) THEN
348 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
349 IGS_GE_MSG_STACK.ADD;
350 App_Exception.Raise_Exception;
351 END IF;
352 END IF;
353
354 IF (((old_references.unit_class = new_references.unit_class)) OR
355 ((new_references.unit_class IS NULL))) THEN
356 NULL;
357 ELSE
358 IF NOT IGS_AS_UNIT_CLASS_PKG.Get_PK_For_Validation (
359 new_references.unit_class
360 ) THEN
361 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
362 IGS_GE_MSG_STACK.ADD;
363 App_Exception.Raise_Exception;
364 END IF;
365 END IF;
366
367 IF (((old_references.unit_cd = new_references.unit_cd)) OR
368 ((new_references.unit_cd IS NULL))) THEN
369 NULL;
370 ELSE
371 IF NOT IGS_PS_UNIT_PKG.Get_PK_For_Validation (
372 new_references.unit_cd
373 ) THEN
374 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
375 IGS_GE_MSG_STACK.ADD;
376 App_Exception.Raise_Exception;
377 END IF;
378 END IF;
379 END Check_Parent_Existance;
380
381 FUNCTION Get_PK_For_Validation (
382 x_course_cd IN VARCHAR2,
383 x_version_number IN NUMBER,
384 x_cal_type IN VARCHAR2,
385 x_pos_sequence_number IN NUMBER,
386 x_posp_sequence_number IN NUMBER,
387 x_sequence_number IN NUMBER
388 ) RETURN BOOLEAN AS
389
390 CURSOR cur_rowid IS
391 SELECT rowid
392 FROM IGS_PS_PAT_STUDY_UNT
393 WHERE course_cd = x_course_cd
394 AND version_number = x_version_number
395 AND cal_type = x_cal_type
396 AND pos_sequence_number = x_pos_sequence_number
397 AND posp_sequence_number = x_posp_sequence_number
398 AND sequence_number = x_sequence_number
399 FOR UPDATE NOWAIT;
400
401 lv_rowid cur_rowid%RowType;
402
403 BEGIN
404
405 Open cur_rowid;
406 Fetch cur_rowid INTO lv_rowid;
407 IF (cur_rowid%FOUND) THEN
408 Close cur_rowid;
409 Return (TRUE);
410 ELSE
411 Close cur_rowid;
412 Return (FALSE);
413 END IF;
414 END Get_PK_For_Validation;
415
416 PROCEDURE GET_FK_IGS_AD_LOCATION (
417 x_location_cd IN VARCHAR2
418 ) AS
419
420 CURSOR cur_rowid IS
421 SELECT rowid
422 FROM IGS_PS_PAT_STUDY_UNT
423 WHERE unit_location_cd = x_location_cd ;
424
425 lv_rowid cur_rowid%RowType;
426
427 BEGIN
428
429 Open cur_rowid;
430 Fetch cur_rowid INTO lv_rowid;
431 IF (cur_rowid%FOUND) THEN
432 Close cur_rowid;
433 Fnd_Message.Set_Name ('IGS', 'IGS_PS_POSU_LOC_FK');
434 IGS_GE_MSG_STACK.ADD;
435 App_Exception.Raise_Exception;
436 Return;
437 END IF;
438 Close cur_rowid;
439
440 END GET_FK_IGS_AD_LOCATION;
441
442 PROCEDURE GET_FK_IGS_PS_PAT_STUDY_PRD (
443 x_course_cd IN VARCHAR2,
444 x_version_number IN NUMBER,
445 x_cal_type IN VARCHAR2,
446 x_pos_sequence_number IN NUMBER,
447 x_sequence_number IN NUMBER
448 ) AS
449
450 CURSOR cur_rowid IS
451 SELECT rowid
452 FROM IGS_PS_PAT_STUDY_UNT
453 WHERE course_cd = x_course_cd
454 AND version_number = x_version_number
455 AND cal_type = x_cal_type
456 AND pos_sequence_number = x_pos_sequence_number
457 AND posp_sequence_number = x_sequence_number ;
458
459 lv_rowid cur_rowid%RowType;
460
461 BEGIN
462
463 Open cur_rowid;
464 Fetch cur_rowid INTO lv_rowid;
465 IF (cur_rowid%FOUND) THEN
466 Close cur_rowid;
467 Fnd_Message.Set_Name ('IGS', 'IGS_PS_POSU_POSP_FK');
468 IGS_GE_MSG_STACK.ADD;
469 App_Exception.Raise_Exception;
470 Return;
471 END IF;
472 Close cur_rowid;
473
474 END GET_FK_IGS_PS_PAT_STUDY_PRD;
475
476 PROCEDURE GET_FK_IGS_PS_UNIT (
477 x_unit_cd IN VARCHAR2
478 ) AS
479
480 CURSOR cur_rowid IS
481 SELECT rowid
482 FROM IGS_PS_PAT_STUDY_UNT
483 WHERE unit_cd = x_unit_cd ;
484
485 lv_rowid cur_rowid%RowType;
486
487 BEGIN
488
489 Open cur_rowid;
490 Fetch cur_rowid INTO lv_rowid;
491 IF (cur_rowid%FOUND) THEN
492 Close cur_rowid;
493 Fnd_Message.Set_Name ('IGS', 'IGS_PS_POSU_UN_FK');
494 IGS_GE_MSG_STACK.ADD;
495 App_Exception.Raise_Exception;
496 Return;
497 END IF;
498 Close cur_rowid;
499
500 END GET_FK_IGS_PS_UNIT;
501
502 PROCEDURE Before_DML (
503 p_action IN VARCHAR2,
504 x_rowid IN VARCHAR2 ,
505 x_course_cd IN VARCHAR2 ,
506 x_version_number IN NUMBER ,
507 x_cal_type IN VARCHAR2 ,
508 x_pos_sequence_number IN NUMBER ,
509 x_posp_sequence_number IN NUMBER ,
510 x_sequence_number IN NUMBER ,
511 x_unit_cd IN VARCHAR2 ,
512 x_unit_location_cd IN VARCHAR2 ,
513 x_unit_class IN VARCHAR2 ,
514 x_description IN VARCHAR2 ,
515 x_creation_date IN DATE ,
516 x_created_by IN NUMBER ,
517 x_last_update_date IN DATE ,
518 x_last_updated_by IN NUMBER ,
519 x_last_update_login IN NUMBER ,
520 x_core_ind IN VARCHAR2
521 ) AS
522 BEGIN
523
524 Set_Column_Values (
525 p_action,
526 x_rowid,
527 x_course_cd,
528 x_version_number,
529 x_cal_type,
530 x_pos_sequence_number,
531 x_posp_sequence_number,
532 x_sequence_number,
533 x_unit_cd,
534 x_unit_location_cd,
535 x_unit_class,
536 x_description,
537 x_creation_date,
538 x_created_by,
539 x_last_update_date,
540 x_last_updated_by,
541 x_last_update_login,
542 x_core_ind
543 );
544
545 IF (p_action = 'INSERT') THEN
546 -- Call all the procedures related to Before Insert.
547 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE,
548 p_updating => FALSE,
549 p_deleting => FALSE );
550 IF Get_PK_For_Validation (
551 new_references.course_cd,
552 new_references.version_number,
553 new_references.cal_type,
554 new_references.pos_sequence_number,
555 new_references.posp_sequence_number,
556 new_references.sequence_number
557 ) THEN
558 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
559 IGS_GE_MSG_STACK.ADD;
560 App_Exception.Raise_Exception;
561 END IF;
562 Check_Constraints;
563 Check_Parent_Existance;
564 ELSIF (p_action = 'UPDATE') THEN
565 -- Call all the procedures related to Before Update.
566 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE,
567 p_inserting => FALSE,
568 p_deleting => FALSE );
569 Check_Constraints;
570 Check_Parent_Existance;
571 ELSIF (p_action = 'DELETE') THEN
572 -- Call all the procedures related to Before Delete.
573 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE,
574 p_inserting => FALSE,
575 p_updating => FALSE );
576 ELSIF (p_action = 'VALIDATE_INSERT') THEN
577 IF Get_PK_For_Validation (
578 new_references.course_cd,
579 new_references.version_number,
580 new_references.cal_type,
581 new_references.pos_sequence_number,
582 new_references.posp_sequence_number,
583 new_references.sequence_number
584 ) THEN
585 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
586 IGS_GE_MSG_STACK.ADD;
587 App_Exception.Raise_Exception;
588 END IF;
589 Check_Constraints;
590 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
591 Check_Constraints;
592 END IF;
593
594 END Before_DML;
595
596 PROCEDURE After_DML (
597 p_action IN VARCHAR2,
598 x_rowid IN VARCHAR2
599 ) AS
600 BEGIN
601
602 l_rowid := x_rowid;
603
604 IF (p_action = 'INSERT') THEN
605 -- Call all the procedures related to After Insert.
606 AfterRowInsertUpdate2 ( p_inserting => TRUE,
607 p_updating => FALSE,
608 p_deleting => FALSE );
609 ELSIF (p_action = 'UPDATE') THEN
610 -- Call all the procedures related to After Update.
611 AfterRowInsertUpdate2 ( p_updating => TRUE,
612 p_inserting => FALSE,
613 p_deleting => FALSE );
614
615 END IF;
616
617 END After_DML;
618
619 procedure INSERT_ROW (
620 X_ROWID in out NOCOPY VARCHAR2,
621 X_COURSE_CD in VARCHAR2,
622 X_VERSION_NUMBER in NUMBER,
623 X_POS_SEQUENCE_NUMBER in NUMBER,
624 X_SEQUENCE_NUMBER in NUMBER,
625 X_POSP_SEQUENCE_NUMBER in NUMBER,
626 X_CAL_TYPE in VARCHAR2,
627 X_UNIT_CD in VARCHAR2,
628 X_UNIT_LOCATION_CD in VARCHAR2,
629 X_UNIT_CLASS in VARCHAR2,
630 X_DESCRIPTION in VARCHAR2,
631 X_MODE in VARCHAR2,
632 X_CORE_IND IN VARCHAR2
633 ) as
634 cursor C is select ROWID from IGS_PS_PAT_STUDY_UNT
635 where COURSE_CD = X_COURSE_CD
636 and VERSION_NUMBER = X_VERSION_NUMBER
637 and POS_SEQUENCE_NUMBER = X_POS_SEQUENCE_NUMBER
638 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
639 and POSP_SEQUENCE_NUMBER = X_POSP_SEQUENCE_NUMBER
640 and CAL_TYPE = X_CAL_TYPE;
641 X_LAST_UPDATE_DATE DATE;
642 X_LAST_UPDATED_BY NUMBER;
643 X_LAST_UPDATE_LOGIN NUMBER;
644 begin
645 X_LAST_UPDATE_DATE := SYSDATE;
646 if(X_MODE = 'I') then
647 X_LAST_UPDATED_BY := 1;
648 X_LAST_UPDATE_LOGIN := 0;
649 elsif (X_MODE = 'R') then
650 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
651 if X_LAST_UPDATED_BY is NULL then
652 X_LAST_UPDATED_BY := -1;
653 end if;
654 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
655 if X_LAST_UPDATE_LOGIN is NULL then
656 X_LAST_UPDATE_LOGIN := -1;
657 end if;
658 else
659 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
660 IGS_GE_MSG_STACK.ADD;
661 app_exception.raise_exception;
662 end if;
663
664 Before_DML( p_action => 'INSERT',
665 x_rowid => X_ROWID,
666 x_course_cd => X_COURSE_CD,
667 x_version_number => X_VERSION_NUMBER,
668 x_cal_type => X_CAL_TYPE,
669 x_pos_sequence_number => X_POS_SEQUENCE_NUMBER,
670 x_posp_sequence_number => X_POSP_SEQUENCE_NUMBER,
671 x_sequence_number => X_SEQUENCE_NUMBER,
672 x_unit_cd => X_UNIT_CD,
673 x_unit_location_cd => X_UNIT_LOCATION_CD,
674 x_unit_class => X_UNIT_CLASS,
675 x_description => X_DESCRIPTION,
676 x_creation_date => X_LAST_UPDATE_DATE,
677 x_created_by => X_LAST_UPDATED_BY,
678 x_last_update_date => X_LAST_UPDATE_DATE,
679 x_last_updated_by => X_LAST_UPDATED_BY,
680 x_last_update_login => X_LAST_UPDATE_LOGIN,
681 x_core_ind => NVL(X_CORE_IND,'N')
682 );
683 insert into IGS_PS_PAT_STUDY_UNT (
684 COURSE_CD,
685 VERSION_NUMBER,
686 CAL_TYPE,
687 POS_SEQUENCE_NUMBER,
688 POSP_SEQUENCE_NUMBER,
689 SEQUENCE_NUMBER,
690 UNIT_CD,
691 UNIT_LOCATION_CD,
692 UNIT_CLASS,
693 DESCRIPTION,
694 CREATION_DATE,
695 CREATED_BY,
696 LAST_UPDATE_DATE,
697 LAST_UPDATED_BY,
698 LAST_UPDATE_LOGIN,
699 CORE_IND
700 ) values (
701 NEW_REFERENCES.COURSE_CD,
702 NEW_REFERENCES.VERSION_NUMBER,
703 NEW_REFERENCES.CAL_TYPE,
704 NEW_REFERENCES.POS_SEQUENCE_NUMBER,
705 NEW_REFERENCES.POSP_SEQUENCE_NUMBER,
706 NEW_REFERENCES.SEQUENCE_NUMBER,
707 NEW_REFERENCES.UNIT_CD,
708 NEW_REFERENCES.UNIT_LOCATION_CD,
709 NEW_REFERENCES.UNIT_CLASS,
710 NEW_REFERENCES.DESCRIPTION,
711 X_LAST_UPDATE_DATE,
712 X_LAST_UPDATED_BY,
713 X_LAST_UPDATE_DATE,
714 X_LAST_UPDATED_BY,
715 X_LAST_UPDATE_LOGIN,
716 NEW_REFERENCES.CORE_IND
717 );
718
719 open c;
720 fetch c into X_ROWID;
721 if (c%notfound) then
722 close c;
723 raise no_data_found;
724 end if;
725 close c;
726 After_DML(
727 p_action => 'INSERT',
728 x_rowid => X_ROWID
729 );
730 end INSERT_ROW;
731
732 procedure LOCK_ROW (
733 X_ROWID in VARCHAR2,
734 X_COURSE_CD in VARCHAR2,
735 X_VERSION_NUMBER in NUMBER,
736 X_POS_SEQUENCE_NUMBER in NUMBER,
737 X_SEQUENCE_NUMBER in NUMBER,
738 X_POSP_SEQUENCE_NUMBER in NUMBER,
739 X_CAL_TYPE in VARCHAR2,
740 X_UNIT_CD in VARCHAR2,
741 X_UNIT_LOCATION_CD in VARCHAR2,
742 X_UNIT_CLASS in VARCHAR2,
743 X_DESCRIPTION in VARCHAR2,
744 X_CORE_IND IN VARCHAR2
745 ) as
746 cursor c1 is select
747 UNIT_CD,
748 UNIT_LOCATION_CD,
749 UNIT_CLASS,
750 DESCRIPTION,
751 CORE_IND
752 from IGS_PS_PAT_STUDY_UNT
753 where ROWID = X_ROWID for update nowait;
754 tlinfo c1%rowtype;
755
756 begin
757 open c1;
758 fetch c1 into tlinfo;
759 if (c1%notfound) then
760 close c1;
761 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
762 IGS_GE_MSG_STACK.ADD;
763 app_exception.raise_exception;
764 return;
765 end if;
766 close c1;
767
768 if ( ((tlinfo.UNIT_CD = X_UNIT_CD)
769 OR ((tlinfo.UNIT_CD is null)
770 AND (X_UNIT_CD is null)))
771 AND ((tlinfo.UNIT_LOCATION_CD = X_UNIT_LOCATION_CD)
772 OR ((tlinfo.UNIT_LOCATION_CD is null)
773 AND (X_UNIT_LOCATION_CD is null)))
774 AND ((tlinfo.UNIT_CLASS = X_UNIT_CLASS)
775 OR ((tlinfo.UNIT_CLASS is null)
776 AND (X_UNIT_CLASS is null)))
777 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
778 OR ((tlinfo.DESCRIPTION is null)
779 AND (X_DESCRIPTION is null)))
780 AND ((tlinfo.CORE_IND = X_CORE_IND)
781 OR ((tlinfo.CORE_IND is null)
782 AND (X_CORE_IND is null)))
783 ) then
784 null;
785 else
786 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
787 IGS_GE_MSG_STACK.ADD;
788 app_exception.raise_exception;
789 end if;
790 return;
791 end LOCK_ROW;
792
793 procedure UPDATE_ROW (
794 X_ROWID in VARCHAR2,
795 X_COURSE_CD in VARCHAR2,
796 X_VERSION_NUMBER in NUMBER,
797 X_POS_SEQUENCE_NUMBER in NUMBER,
798 X_SEQUENCE_NUMBER in NUMBER,
799 X_POSP_SEQUENCE_NUMBER in NUMBER,
800 X_CAL_TYPE in VARCHAR2,
801 X_UNIT_CD in VARCHAR2,
802 X_UNIT_LOCATION_CD in VARCHAR2,
803 X_UNIT_CLASS in VARCHAR2,
804 X_DESCRIPTION in VARCHAR2,
805 X_MODE in VARCHAR2,
806 X_CORE_IND IN VARCHAR2
807 ) as
808 X_LAST_UPDATE_DATE DATE;
809 X_LAST_UPDATED_BY NUMBER;
810 X_LAST_UPDATE_LOGIN NUMBER;
811 begin
812 X_LAST_UPDATE_DATE := SYSDATE;
813 if(X_MODE = 'I') then
814 X_LAST_UPDATED_BY := 1;
815 X_LAST_UPDATE_LOGIN := 0;
816 elsif (X_MODE = 'R') then
817 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
818 if X_LAST_UPDATED_BY is NULL then
819 X_LAST_UPDATED_BY := -1;
820 end if;
821 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
822 if X_LAST_UPDATE_LOGIN is NULL then
823 X_LAST_UPDATE_LOGIN := -1;
824 end if;
825 else
826 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
827 IGS_GE_MSG_STACK.ADD;
828 app_exception.raise_exception;
829 end if;
830
831 Before_DML( p_action => 'UPDATE',
832 x_rowid => X_ROWID,
833 x_course_cd => X_COURSE_CD,
834 x_version_number => X_VERSION_NUMBER,
835 x_cal_type => X_CAL_TYPE,
836 x_pos_sequence_number => X_POS_SEQUENCE_NUMBER,
837 x_posp_sequence_number => X_POSP_SEQUENCE_NUMBER,
838 x_sequence_number => X_SEQUENCE_NUMBER,
839 x_unit_cd => X_UNIT_CD,
840 x_unit_location_cd => X_UNIT_LOCATION_CD,
841 x_unit_class => X_UNIT_CLASS,
842 x_description => X_DESCRIPTION,
843 x_creation_date => X_LAST_UPDATE_DATE,
844 x_created_by => X_LAST_UPDATED_BY,
845 x_last_update_date => X_LAST_UPDATE_DATE,
846 x_last_updated_by => X_LAST_UPDATED_BY,
847 x_last_update_login => X_LAST_UPDATE_LOGIN,
848 x_core_ind => NVL(X_CORE_IND,'N')
849 );
850
851 update IGS_PS_PAT_STUDY_UNT set
852 UNIT_CD = NEW_REFERENCES.UNIT_CD,
853 UNIT_LOCATION_CD = NEW_REFERENCES.UNIT_LOCATION_CD,
854 UNIT_CLASS = NEW_REFERENCES.UNIT_CLASS,
855 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
856 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
857 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
858 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
859 CORE_IND = new_references.CORE_IND
860 where ROWID = X_ROWID
861 ;
862 if (sql%notfound) then
863 raise no_data_found;
864 end if;
865 After_DML(
866 p_action => 'UPDATE',
867 x_rowid => X_ROWID
868 );
869 end UPDATE_ROW;
870
871 procedure ADD_ROW (
872 X_ROWID in out NOCOPY VARCHAR2,
873 X_COURSE_CD in VARCHAR2,
874 X_VERSION_NUMBER in NUMBER,
875 X_POS_SEQUENCE_NUMBER in NUMBER,
876 X_SEQUENCE_NUMBER in NUMBER,
877 X_POSP_SEQUENCE_NUMBER in NUMBER,
878 X_CAL_TYPE in VARCHAR2,
879 X_UNIT_CD in VARCHAR2,
880 X_UNIT_LOCATION_CD in VARCHAR2,
881 X_UNIT_CLASS in VARCHAR2,
882 X_DESCRIPTION in VARCHAR2,
883 X_MODE in VARCHAR2,
884 X_CORE_IND IN VARCHAR2
885 ) as
886 cursor c1 is select rowid from IGS_PS_PAT_STUDY_UNT
887 where COURSE_CD = X_COURSE_CD
888 and VERSION_NUMBER = X_VERSION_NUMBER
889 and POS_SEQUENCE_NUMBER = X_POS_SEQUENCE_NUMBER
890 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
891 and POSP_SEQUENCE_NUMBER = X_POSP_SEQUENCE_NUMBER
892 and CAL_TYPE = X_CAL_TYPE
893 ;
894 begin
895 open c1;
896 fetch c1 into X_ROWID;
897 if (c1%notfound) then
898 close c1;
899 INSERT_ROW (
900 X_ROWID,
901 X_COURSE_CD,
902 X_VERSION_NUMBER,
903 X_POS_SEQUENCE_NUMBER,
904 X_SEQUENCE_NUMBER,
905 X_POSP_SEQUENCE_NUMBER,
906 X_CAL_TYPE,
907 X_UNIT_CD,
908 X_UNIT_LOCATION_CD,
909 X_UNIT_CLASS,
910 X_DESCRIPTION,
911 X_MODE,
912 X_CORE_IND);
913 return;
914 end if;
915 close c1;
916 UPDATE_ROW (
917 X_ROWID,
918 X_COURSE_CD,
919 X_VERSION_NUMBER,
920 X_POS_SEQUENCE_NUMBER,
921 X_SEQUENCE_NUMBER,
922 X_POSP_SEQUENCE_NUMBER,
923 X_CAL_TYPE,
924 X_UNIT_CD,
925 X_UNIT_LOCATION_CD,
926 X_UNIT_CLASS,
927 X_DESCRIPTION,
928 X_MODE,
929 X_CORE_IND);
930 end ADD_ROW;
931
932 procedure DELETE_ROW (
933 X_ROWID in VARCHAR2
934 ) as
935 begin
936 Before_DML( p_action => 'DELETE',
937 x_rowid => X_ROWID
938 );
939 delete from IGS_PS_PAT_STUDY_UNT
940 where ROWID = X_ROWID;
941 if (sql%notfound) then
942 raise no_data_found;
943 end if;
944 After_DML(
945 p_action => 'DELETE',
946 x_rowid => X_ROWID
947 );
948
949 end DELETE_ROW;
950
951 end IGS_PS_PAT_STUDY_UNT_PKG;