1 PACKAGE BODY IGS_PS_VAL_CEPRC AS
2 /* $Header: IGSPS18B.pls 120.1 2006/01/31 01:54:15 sommukhe noship $ */
3
4 /***********************************************************************************************
5 Created By:
6 Date Created By:
7 Purpose:
8
9 Known limitations,enhancements,remarks:
10
11 Change History
12 Who When What
13 sommukhe 16-Jan-2006 Bug #4926548 changed the cursor definition for c_uss in the function crsp_val_ceprc_coous
14 and added cursor c_uss1
15 sarakshi 20-Apr-2002 Removed the function crsp_val_ceprc_uref as a part of bug#2146753
16 which validates the reference type uniqueness across program offering
17 option
18 ********************************************************************************************** */
19
20 -- Validate unique combination of IGS_PS_UNIT set and IGS_PS_COURSE offerning option
21 FUNCTION crsp_val_ceprc_uniq(
22 p_coo_id IN NUMBER ,
23 p_reference_cd_type IN VARCHAR2 ,
24 p_sequence_number IN NUMBER ,
25 p_unit_set_cd IN VARCHAR2 ,
26 p_us_version_number IN NUMBER ,
27 p_message_name OUT NOCOPY VARCHAR2 )
28 RETURN BOOLEAN AS
29 BEGIN
30 -- crsp_val_ceprc_uniq
31 -- Validate that the IGS_PS_UNIT set is unique in combination with the IGS_PS_COURSE
32 -- offering option.
33 DECLARE
34 v_dummy VARCHAR2(1);
35 CURSOR c_ceprc IS
36 SELECT 'X'
37 FROM IGS_PS_ENT_PT_REF_CD ceprc
38 WHERE ceprc.coo_id = p_coo_id AND
39 NVL(ceprc.unit_set_cd, 'NONE') = NVL(p_unit_set_cd, 'NONE') AND
40 NVL(ceprc.us_version_number, 0) = NVL(p_us_version_number, 0) AND
41 ceprc.reference_cd_type = p_reference_cd_type AND
42 ceprc.sequence_number <> p_sequence_number;
43 BEGIN
44 p_message_name := NULL;
45 OPEN c_ceprc;
46 FETCH c_ceprc INTO v_dummy;
47 IF (c_ceprc%FOUND) THEN
48 CLOSE c_ceprc;
49 p_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
50 RETURN FALSE;
51 ELSE
52 CLOSE c_ceprc;
53 END IF;
54 RETURN TRUE;
55 EXCEPTION
56 WHEN OTHERS THEN
57 IF (c_ceprc%ISOPEN) THEN
58 CLOSE c_ceprc;
59 END IF;
60 App_Exception.Raise_Exception;
61 END;
62 END crsp_val_ceprc_uniq;
63
64 --obsoleted the function crsp_val_ceprc_uref, bug#2146753
65
66 -- Validate crs entry point IGS_PS_UNIT set against crs offer option IGS_PS_UNIT set
67 FUNCTION crsp_val_ceprc_coous(
68 p_coo_id IN NUMBER ,
69 p_unit_set_cd IN VARCHAR2 ,
70 p_us_version_number IN NUMBER ,
71 p_message_name OUT NOCOPY VARCHAR2)
72 RETURN VARCHAR2 AS
73 BEGIN
74 -- crsp_val_ceprc_coous
75 -- This module validates the IGS_PS_UNIT set of the IGS_PS_COURSE entry
76 -- point of reference code can be used for this IGS_PS_COURSE
77 -- offering option.
78 DECLARE
79 v_uss_found VARCHAR2(1);
80 cst_false CONSTANT VARCHAR2(5) := 'FALSE';
81 cst_true CONSTANT VARCHAR2(5) := 'TRUE';
82 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
83 CURSOR c_uss IS
84 SELECT 'X'
85 FROM IGS_PS_OFR_UNIT_SET cous,IGS_PS_OFR_OPT coo,igs_en_unit_set_stat uss1,IGS_EN_UNIT_SET_ALL US
86 WHERE coo.coo_id = p_coo_id
87 AND us.version_number = p_us_version_number
88 AND us.unit_set_cd = p_unit_set_cd
89 AND us.expiry_dt IS NULL
90 AND coo.course_cd = cous.course_cd
91 AND coo.version_number = cous.crv_version_number
92 AND coo.CAL_TYPE = cous.CAL_TYPE
93 AND us.unit_set_cd = cous.unit_set_cd
94 AND us.version_number = cous.us_version_number
95 AND us.unit_set_status = uss1.unit_set_status
96 AND uss1.s_unit_set_status =cst_active
97 AND NOT EXISTS (SELECT 1
98 FROM IGS_PS_OF_OPT_UNT_ST coous
99 WHERE coous.course_cd = cous.course_cd
100 AND coous.crv_version_number = cous.crv_version_number
101 AND coous.CAL_TYPE = cous.CAL_TYPE
102 AND coous.unit_set_cd = cous.unit_set_cd
103 AND coous.us_version_number = cous.us_version_number
104 );
105 CURSOR c_uss1 IS
106 SELECT 'X'
107 FROM IGS_PS_OF_OPT_UNT_ST coous,IGS_EN_UNIT_SET_ALL US,igs_en_unit_set_stat uss1
108 WHERE coous.coo_id = p_coo_id
109 AND us.version_number = p_us_version_number
110 AND us.unit_set_cd = p_unit_set_cd
111 AND us.expiry_dt IS NULL
112 AND us.unit_set_cd = coous.unit_set_cd
113 AND us.version_number = coous.us_version_number
114 AND us.unit_set_status = uss1.unit_set_status
115 AND uss1.s_unit_set_status = cst_active;
116 BEGIN
117 p_message_name := NULL;
118 OPEN c_uss;
119 FETCH c_uss INTO v_uss_found;
120 IF (c_uss%FOUND) THEN
121 CLOSE c_uss;
122 RETURN cst_true;
123 ELSE
124 OPEN c_uss1;
125 FETCH c_uss1 INTO v_uss_found;
126 IF (c_uss1%FOUND) THEN
127 CLOSE c_uss1;CLOSE c_uss;
128 RETURN cst_true;
129 ELSE
130 p_message_name := 'IGS_PS_UNITSET_NOT_VALID';
131 CLOSE c_uss1;CLOSE c_uss;
132 RETURN cst_false;
133 END IF;
134 CLOSE c_uss; RETURN cst_true;
135 END IF;
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 IF (c_uss%ISOPEN) THEN
140 CLOSE c_uss;
141 END IF;
142 App_Exception.Raise_Exception;
143 END;
144 END crsp_val_ceprc_coous;
145 END IGS_PS_VAL_CEPRC;
[Home] [Help]