1 PACKAGE BODY IGS_PS_VAL_COus AS
2 /* $Header: IGSPS28B.pls 115.5 2002/11/29 03:00:29 nsidana ship $ */
3
4 ----------------------------------------------------------------------------
5 -- Change History :
6 -- Who When What
7 -- avenkatr 30-AUG-2001 Bug No 1956374. Removed procedure "crsp_val_iud_crv_dtl"
8 -- avenkatr 30-AUG-2001 Bug No 1956374. Removed procedure "crsp_val_iud_uv_dtl"
9 ----------------------------------------------------------------------------
10 --
11 -- Validate crs off IGS_PS_UNIT sets against IGS_PS_UNIT set IGS_PS_COURSE type restrictions
12 FUNCTION crsp_val_cous_usctv(
13 p_course_cd IN VARCHAR2 ,
14 p_crv_version_number IN NUMBER ,
15 p_unit_set_cd IN VARCHAR2 ,
16 p_us_version_number IN NUMBER ,
17 p_message_name OUT NOCOPY VARCHAR2 )
18 RETURN BOOLEAN AS
19 BEGIN -- crsp_val_cous_usctv
20 -- This module validates the IGS_PS_OFR_UNIT_SET can only be linked
21 -- to IGS_PS_COURSE offerings which don't breach the IGS_EN_UNITSETPSTYPE
22 -- restrictions. That is, either no restrictions exist for the IGS_EN_UNIT_SET or
23 -- the IGS_PS_COURSE is of a type which is in the defined set.
24 DECLARE
25 v_course_type IGS_PS_VER.course_type%TYPE;
26 CURSOR c_crv IS
27 SELECT crv.course_type
28 FROM IGS_PS_VER crv
29 WHERE crv.course_cd = p_course_cd AND
30 crv.version_number = p_crv_version_number;
31 CURSOR c_usctv (
32 cp_course_type IGS_PS_VER.course_type%TYPE) IS
33 SELECT usctv.course_type
34 FROM IGS_EN_UNIT_SET_COURSE_TYPE_V usctv
35 WHERE usctv.course_type = cp_course_type AND
36 usctv.unit_set_cd = p_unit_set_cd AND
37 usctv.version_number = p_us_version_number;
38 BEGIN
39 -- 1. Fetch the IGS_PS_COURSE type for the IGS_PS_OFR_UNIT_SET record
40 -- from its parent record
41 OPEN c_crv;
42 FETCH c_crv INTO v_course_type;
43 CLOSE c_crv;
44 -- 2. Check to see if IGS_PS_COURSE type is valid for the IGS_PS_UNIT set
45 OPEN c_usctv(
46 v_course_type);
47 FETCH c_usctv INTO v_course_type;
48 IF (c_usctv%FOUND) THEN
49 CLOSE c_usctv;
50 p_message_name := NULL;
51 RETURN TRUE;
52 END IF;
53 CLOSE c_usctv;
54 p_message_name := 'IGS_PS_PRG_TYPE_INVALID';
55 RETURN FALSE;
56 EXCEPTION
57 WHEN OTHERS THEN
58 IF (c_crv%ISOPEN) THEN
59 CLOSE c_crv;
60 END IF;
61 IF (c_usctv%ISOPEN) THEN
62 CLOSE c_usctv;
63 END IF;
64 App_Exception.Raise_Exception;
65 END;
66 END crsp_val_cous_usctv;
67 --
68 -- Validate crs off IGS_PS_UNIT set 'only as subordinate' indicator
69 FUNCTION crsp_val_cous_subind(
70 p_course_cd IN VARCHAR2 ,
71 p_crv_version_number IN NUMBER ,
72 p_cal_type IN VARCHAR2 ,
73 p_unit_set_cd IN VARCHAR2 ,
74 p_us_version_number IN NUMBER ,
75 p_old_only_as_sub_ind IN VARCHAR2 DEFAULT 'N',
76 p_new_only_as_sub_ind IN VARCHAR2 DEFAULT 'N',
77 p_message_name OUT NOCOPY VARCHAR2 )
78 RETURN BOOLEAN AS
79 BEGIN -- crsp_val_cous_subind
80 -- This module validates the only_as_sub_ind for a IGS_PS_OFR_UNIT_SET.
81 -- * It ensures that the indicator can not be changed from 'N' to 'Y' once
82 -- IGS_PS_OF_UNT_SET_RL records exist with the
83 -- IGS_PS_OFR_UNIT_SET as the superior.
84 -- * It ensures that the indicator can not be changed from 'N' to 'Y' once
85 -- IGS_PS_COO_AD_UNIT_S records exist for a IGS_PS_OF_OPT_AD_CAT for
86 -- a IGS_PS_OFR_OPT
87 DECLARE
88 v_x VARCHAR2(1);
89 CURSOR c_cousr IS
90 SELECT 'x'
91 FROM IGS_PS_OF_UNT_SET_RL cousr
92 WHERE cousr.course_cd = p_course_cd AND
93 cousr.crv_version_number = p_crv_version_number AND
94 cousr.cal_type = p_cal_type AND
95 cousr.sup_unit_set_cd = p_unit_set_cd AND
96 cousr.sup_us_version_number = p_us_version_number;
97 CURSOR c_cacus IS
98 SELECT 'x'
99 FROM IGS_PS_COO_AD_UNIT_S cacus
100 WHERE cacus.course_cd = p_course_cd AND
101 cacus.crv_version_number = p_crv_version_number AND
102 cacus.cal_type = p_cal_type AND
103 cacus.unit_set_cd = p_unit_set_cd AND
104 cacus.us_version_number = p_us_version_number;
105 BEGIN
106 -- Set the default message number
107 p_message_name := NULL;
108 IF p_old_only_as_sub_ind = 'N' AND
109 p_new_only_as_sub_ind = 'Y' THEN
110 -- Validate against IGS_PS_OF_UNT_SET_RL
111 OPEN c_cousr;
112 FETCH c_cousr INTO v_x;
113 IF (c_cousr%FOUND) THEN
114 p_message_name := 'IGS_PS_SUBORD_INDICATOR_Y';
115 CLOSE c_cousr;
116 RETURN FALSE;
117 END IF;
118 CLOSE c_cousr;
119 -- Validate against IGS_PS_COO_AD_UNIT_S
120 OPEN c_cacus;
121 FETCH c_cacus INTO v_x;
122 IF (c_cacus%FOUND) THEN
123 CLOSE c_cacus;
124 p_message_name := 'IGS_PS_SUBORD_IND_NOTCHG_Y';
125 RETURN FALSE;
126 END IF;
127 CLOSE c_cacus;
128 END IF;
129 RETURN TRUE;
130 EXCEPTION
131 WHEN OTHERS THEN
132 IF (c_cousr%ISOPEN) THEN
133 CLOSE c_cousr;
134 END IF;
135 IF (c_cacus%ISOPEN) THEN
136 CLOSE c_cacus;
137 END IF;
138 App_Exception.Raise_Exception;
139 END;
140 END crsp_val_cous_subind;
141 END IGS_PS_VAL_COus;
[Home] [Help]