1 PACKAGE IGS_AV_VAL_ASU AS
2 /* $Header: IGSAV04S.pls 120.0 2005/07/05 13:04:09 appldev noship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 28-AUG-2001 Bug No. 1956374 .The function genp_val_staff_prsn removed
7 -- skoppula 15-SEP-2001 Enhancement Bug for Academic Records Maintenance DLD
8 -- To change the credit_percentage logic to include advance standing credit points
9 -- nalkumar 11-Sep-2001 Added Parameter 'p_adv_stnd_trans' in advp_val_as_dates, advp_val_expiry_dt
10 -- advp_val_status_dts functions.
11 -- These changes has been done as per the Career Impact DLD.
12 -- Bug# 2027984.
13 --nalkumar 05-June-2002 Replaced the referances of the igs_av_stnd_unit/unit_lvl.(PREV_UNIT_CD and TEST_DETAILS_ID) columns
14 -- to igs_av_stnd_unit/unit_lvl.(unit_details_id and tst_rslt_dtls_id) columns. This is as per Bug# 2401170
15 --
16 -- nalkumar 10-Dec-2003 Bug# 3270446 RECR50 Build; Obsoleted the IGS_AV_STND_UNIT.CREDIT_PERCENTAGE column.
17 --
18 -------------------------------------------------------------------------------------------
19 /***** Bug No : 1956374
20 Task : Duplicated Procedures and functions
21 PROCEDURE advp_val_prclde_unit is removed
22 msrinivi 24-AUG-2001 Bug No. 1956374 .The function genp_val_prsn_id removed
23 *****/
24 -- Bug #1956374
25 -- As part of the bug# 1956374 removed the function crsp_val_uv_sys_sts
26 -- As part of the bug# 1956374 removed the function crsp_val_uv_exists , advp_val_alt_unit
27
28 -- To validate the advanced standing basis IGS_OR_INSTITUTION code.
29
30 FUNCTION advp_val_asu_inst(
31 p_exempt_inst IN VARCHAR2 ,
32 p_message_name OUT NOCOPY VARCHAR2 )
33 RETURN BOOLEAN;
34
35 --
36 -- To validate the granting of advanced standing (form level only)
37 FUNCTION advp_val_as_frm_grnt(
38 p_person_id IN NUMBER ,
39 p_course_cd IN VARCHAR2 ,
40 p_version_number IN NUMBER ,
41 p_s_adv_stnd_granting_status IN VARCHAR2 ,
42 p_message_name OUT NOCOPY VARCHAR2 )
43 RETURN BOOLEAN;
44
45 --
46 -- To validate the various dates of advanced standing units or levels.
47 FUNCTION advp_val_as_dates(
48 p_advanced_standing_dt IN DATE ,
49 p_date_type IN VARCHAR2 ,
50 p_message_name OUT NOCOPY VARCHAR2,
51 p_adv_stnd_trans IN VARCHAR2 DEFAULT 'N') -- This parameter has been added for Career Impact DLD.
52 RETURN BOOLEAN;
53
54 --
55 -- Validate the AS recognition type closed indicator.
56 FUNCTION advp_val_asrt_closed(
57 p_recognition_type IN VARCHAR2 ,
58 p_message_name OUT NOCOPY VARCHAR2 )
59 RETURN BOOLEAN;
60
61
62 --
63 -- To validate the status dates of advanced standing units or levels.
64 FUNCTION advp_val_status_dts(
65 p_granting_status IN VARCHAR2 ,
66 p_related_dt IN DATE ,
67 p_message_name OUT NOCOPY VARCHAR2,
68 p_adv_stnd_trans IN VARCHAR2 DEFAULT 'N') -- This parameter has been added for Career Impact DLD.
69 RETURN BOOLEAN;
70
71 --
72 -- Routine to save rowids in a PL/SQL TABLE for the current commit.
73 -- To validate the approved date of advanced standing units or levels.
74 FUNCTION advp_val_as_aprvd_dt(
75 p_approved_dt IN DATE ,
76 p_related_dt IN DATE ,
77 p_message_name OUT NOCOPY VARCHAR2 )
78 RETURN BOOLEAN;
79
80
81 -- To validate the approved date of advanced standing units or levels.
82 FUNCTION advp_val_approved_dt(
83 p_approved_dt IN DATE ,
84 p_expiry_dt IN DATE ,
85 p_message_name OUT NOCOPY VARCHAR2 )
86 RETURN BOOLEAN;
87
88 --
89 -- To validate the expiry date of advanced standing units or levels.
90 FUNCTION advp_val_expiry_dt(
91 p_expiry_dt IN DATE ,
92 p_message_name OUT NOCOPY VARCHAR2,
93 p_adv_stnd_trans IN VARCHAR2 DEFAULT 'N') -- This parameter has been added for Career Impact DLD.
94 RETURN BOOLEAN;
95
96
97 -- To validate the credit percentage of advanced standing units.
98 FUNCTION advp_val_credit_perc(
99 p_percentage IN NUMBER ,
100 p_message_name OUT NOCOPY VARCHAR2 )
101 RETURN BOOLEAN;
102
103 -- To validate internal/external advanced standing IGS_PS_COURSE limits.
104
105
106
107 FUNCTION advp_val_as_totals(
108 p_person_id IN NUMBER ,
109 p_course_cd IN VARCHAR2 ,
110 p_version_number IN NUMBER ,
111 p_include_approved IN BOOLEAN ,
112 p_asu_unit_cd IN VARCHAR2 ,
113 p_asu_version_number IN NUMBER ,
114 p_asu_advstnd_granting_status IN VARCHAR2 ,
115 p_asul_unit_level IN VARCHAR2 ,
116 p_asul_exmptn_institution_cd IN VARCHAR2 ,
117 p_asul_advstnd_granting_status IN VARCHAR2 ,
118 p_total_exmptn_approved OUT NOCOPY NUMBER ,
119 p_total_exmptn_granted OUT NOCOPY NUMBER ,
120 p_total_exmptn_perc_grntd OUT NOCOPY NUMBER ,
121 p_message_name OUT NOCOPY VARCHAR2,
122 p_unit_details_id IN NUMBER DEFAULT NULL,
123 p_tst_rslt_dtls_id IN NUMBER DEFAULT NULL,
124 p_asu_exmptn_institution_cd IN VARCHAR2 DEFAULT NULL)
125 RETURN BOOLEAN;
126
127 --
128 -- To get whether delete of student IGS_PS_UNIT attempt is allowed.
129 FUNCTION advp_get_ua_del_alwd(
130 p_cal_type IN VARCHAR2 ,
131 p_ci_sequence_number IN NUMBER ,
132 p_effective_dt IN DATE )
133 RETURN BOOLEAN;
134
135 --
136 -- To validate the granting of advanced standing.
137 FUNCTION advp_val_as_grant(
138 p_person_id IN NUMBER ,
139 p_course_cd IN VARCHAR2 ,
140 p_version_number IN NUMBER ,
141 p_s_adv_stnd_granting_status IN VARCHAR2 ,
142 p_message_name OUT NOCOPY VARCHAR2 )
143 RETURN BOOLEAN;
144
145
146 -- Record Type for the ref cursor that fetches the advance standing details.
147 TYPE adv_cr_rec IS RECORD (credit_percentage igs_av_stnd_unit_all.credit_percentage%TYPE,
148 exemption_institution_cd igs_av_stnd_unit_all.exemption_institution_cd%TYPE,
149 s_adv_stnd_granting_status igs_av_stnd_unit_all.s_adv_stnd_granting_status%TYPE,
150 unit_cd igs_av_stnd_unit_all.unit_cd%TYPE,
151 version_number igs_av_stnd_unit_all.version_number%TYPE,
152 achievable_credit_points igs_av_stnd_unit_all.achievable_credit_points%TYPE);
153
154 g_advcrrec adv_cr_rec;
155
156 -- Ref cursor that fetches the advance standing details dynamically depending on the values of previous_unit_cd,
157 -- test segment id.
158 TYPE adv_cp_cur IS REF CURSOR RETURN g_advcrrec%TYPE;
159
160 -- Procedure to compute advance standing credit points.
161 -- Intorduced as part of Academic Records Maintenance DLD
162 PROCEDURE advp_get_adv_credit_pts(
163 p_person_id IN NUMBER ,
164 p_course_cd IN VARCHAR2 ,
165 p_version_number IN NUMBER ,
166 p_s_adv_stnd_granting_status IN VARCHAR2 ,
167 p_unit_cd IN VARCHAR2,
168 p_unit_version IN NUMBER,
169 p_unit_details_id IN NUMBER DEFAULT NULL,
170 p_tst_rslt_dtls_id IN NUMBER DEFAULT NULL,
171 p_credit_points OUT NOCOPY NUMBER,
172 p_inst_credit_points OUT NOCOPY NUMBER,
173 p_exemption_institution_cd IN VARCHAR2);
174
175 -- Function to return advance standing credit points for a program attempt,if any
176 FUNCTION granted_Adv_standing(p_person_id IN NUMBER,
177 p_asu_course_cd IN VARCHAR2 ,
178 p_asu_version_number IN NUMBER ,
179 p_unit_cd IN VARCHAR2,
180 p_version_number IN NUMBER,
181 p_s_adv_stnd_granting_status IN VARCHAR2,
182 p_effective_dt IN DATE
183 ) RETURN VARCHAR2;
184
185 pragma restrict_references(granted_adv_standing,wnds,wnps);
186
187 -- Overloaded Function to return advance standing credit points for an
188 -- unit with in an advance standing program attempt
189 FUNCTION adv_credit_pts(p_person_id IN NUMBER,
190 p_asu_course_cd IN VARCHAR2 ,
191 p_asu_version_number IN NUMBER ,
192 p_unit_cd IN VARCHAR2,
193 p_version_number IN NUMBER,
194 p_s_adv_stnd_granting_status IN VARCHAR2,
195 p_effective_dt IN DATE,
196 p_cr_points OUT NOCOPY NUMBER,
197 p_adv_grant_status OUT NOCOPY VARCHAR2,
198 p_msg OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
199
200 END IGS_AV_VAL_ASU;