1 PACKAGE Igs_En_Prc_Load AS
2 /* $Header: IGSEN18S.pls 120.3 2005/12/04 22:36:57 appldev ship $ */
3
4 /**************************************************************************************
5 Who When What
6 stutta 17-NOV-2003 Added two new parameters for the function enrp_clc_key_prog as part
7 part of Term Records Build.
8 knaraset 04-Nov-2003 Added two functions enrp_get_inst_attendance enrp_get_inst_cp as part
9 of build EN212, bug 3198180
10 sarakshi 27-Jun-2003 Enh#2930935, added parameter uoo_id to teh function ENRP_CLC_SUA_LOAD
11 pradhakr 15-Jan-2003 Added one more parameter no_assessment_ind to the
12 procedure ENRP_CLC_SUA_EFTSU and ENRP_GET_LOAD_INCUR.
13 Changes wrt ENCR026. Bug# 2743459
14 amuthu 15-NOV-2002 Modified as per the SS Worksheet Redesign TD
15 vkarthik 21-Jul-2004 Added two parameters to enrp_clc_sua_load for EN308 Billable credit points build #3782329
16 vijrajag 07-Jul-2005 Added function get_term_credits
17 ********************************************************************************************/
18 -- To calculate the EFTSU total for a Unit attempt across all load cals
19 FUNCTION ENRP_CLC_SUA_EFTSUT(
20 P_PERSON_ID IN NUMBER ,
21 P_COURSE_CD IN VARCHAR2 ,
22 P_CRV_VERSION_NUMBER IN NUMBER ,
23 P_UNIT_CD IN VARCHAR2 ,
24 P_UNIT_VERSION_NUMBER IN NUMBER ,
25 P_TEACH_CAL_TYPE IN VARCHAR2 ,
26 P_TEACH_SEQUENCE_NUMBER IN NUMBER ,
27 p_uoo_id IN NUMBER ,
28 p_override_enrolled_cp IN NUMBER ,
29 p_override_eftsu IN NUMBER ,
30 p_sca_cp_total IN NUMBER ,
31 p_original_eftsu OUT NOCOPY NUMBER )
32 RETURN NUMBER;
33 PRAGMA RESTRICT_REFERENCES (ENRP_CLC_SUA_EFTSUT,WNDS,WNPS);
34 --
35 -- To calc the total EFTSU figure for a SCA within a load cal instance
36 FUNCTION enrp_clc_eftsu_total(
37 p_person_id IN NUMBER ,
38 p_course_cd IN VARCHAR2 ,
39 p_acad_cal_type IN VARCHAR2 ,
40 p_acad_sequence_number IN NUMBER ,
41 p_load_cal_type IN VARCHAR2 ,
42 p_load_sequence_number IN NUMBER ,
43 p_truncate_ind IN VARCHAR2 DEFAULT 'N',
44 p_include_research_ind IN VARCHAR2 ,
45 p_key_course_cd IN igs_en_su_attempt.course_cd%TYPE,
46 p_key_version_number IN igs_en_su_attempt.version_number%TYPE,
47 p_credit_points OUT NOCOPY NUMBER )
48 RETURN NUMBER;
49 PRAGMA RESTRICT_REFERENCES (ENRP_CLC_EFTSU_TOTAL,WNDS,WNPS);
50 --
51 -- To calculate the EFTSU value of a student unit attempt
52 FUNCTION enrp_clc_sua_eftsu(
53 p_person_id IN NUMBER ,
54 p_course_cd IN VARCHAR2 ,
55 p_crv_version_number IN NUMBER ,
56 p_unit_cd IN VARCHAR2 ,
57 p_unit_version_number IN NUMBER ,
58 p_teach_cal_type IN VARCHAR2 ,
59 p_teach_sequence_number IN NUMBER ,
60 p_uoo_id IN NUMBER ,
61 p_load_cal_type IN VARCHAR2 ,
62 p_load_sequence_number IN NUMBER ,
63 p_override_enrolled_cp IN NUMBER ,
64 p_override_eftsu IN NUMBER ,
65 p_truncate_ind IN VARCHAR2 DEFAULT 'N',
66 p_sca_cp_total IN NUMBER ,
67 p_key_course_cd IN igs_en_su_attempt.course_cd%TYPE,
68 p_key_version_number IN igs_en_su_attempt.version_number%TYPE,
69 p_credit_points OUT NOCOPY NUMBER ,
70 -- anilk, Audit special fee build
71 p_include_audit IN VARCHAR2 DEFAULT 'N')
72 RETURN NUMBER;
73 PRAGMA RESTRICT_REFERENCES (ENRP_CLC_SUA_EFTSU,WNDS,WNPS);
74 --
75 -- To calculate the WEFTSU for a student unit attempt
76 FUNCTION ENRP_CLC_SUA_WEFTSU(
77 p_unit_cd IN VARCHAR2 ,
78 p_version_number IN NUMBER ,
79 p_discipline_group_cd IN VARCHAR2 ,
80 p_org_unit_cd IN VARCHAR2 ,
81 p_sua_eftsu IN NUMBER ,
82 p_local_ins_deakin_ind IN VARCHAR2 )
83 RETURN NUMBER;
84 --PRAGMA RESTRICT_REFERENCES (ENRP_CLC_SUA_WEFTSU,WNDS);
85 --
86 -- To calculate the truncated EFTSU figure according to DEETYA rules
87 FUNCTION ENRP_CLC_EFTSU_TRUNC(
88 p_unit_cd IN VARCHAR2 ,
89 p_version_number IN NUMBER ,
90 p_uoo_id IN NUMBER ,
91 p_eftsu IN NUMBER )
92 RETURN NUMBER;
93 PRAGMA RESTRICT_REFERENCES (ENRP_CLC_EFTSU_TRUNC,WNDS,WNPS);
94 --
95 -- To get the annual load for a IGS_PS_UNIT attempt within a Course
96 FUNCTION ENRP_GET_ANN_LOAD(
97 p_person_id IN NUMBER ,
98 p_course_cd IN VARCHAR2 ,
99 p_version_number IN NUMBER ,
100 p_unit_cd IN VARCHAR2 ,
101 p_unit_version_number IN NUMBER ,
102 p_cal_type IN VARCHAR2 ,
103 p_ci_sequence_number IN NUMBER ,
104 p_sca_cp_total IN NUMBER )
105 RETURN NUMBER;
106 PRAGMA RESTRICT_REFERENCES (ENRP_GET_ANN_LOAD,WNDS,WNPS);
107 --
108 -- To calc the total load for an SCA for a load period
109 FUNCTION ENRP_CLC_LOAD_TOTAL(
110 p_person_id IN NUMBER ,
111 p_course_cd IN VARCHAR2 ,
112 p_acad_cal_type IN VARCHAR2 ,
113 p_acad_sequence_number IN NUMBER ,
114 p_load_cal_type IN VARCHAR2 ,
115 p_load_sequence_number IN NUMBER )
116 RETURN NUMBER;
117
118 --PRAGMA RESTRICT_REFERENCES (ENRP_CLC_LOAD_TOTAL,WNDS);
119 --
120 -- To calculate the load for a sua (optionally within a load calendar)
121 FUNCTION ENRP_CLC_SUA_LOAD(
122 p_unit_cd IN VARCHAR2 ,
123 p_version_number IN NUMBER ,
124 p_cal_type IN VARCHAR2 ,
125 p_ci_sequence_number IN NUMBER ,
126 p_load_cal_type IN VARCHAR2 ,
127 p_load_ci_sequence_number IN NUMBER ,
128 p_override_enrolled_cp IN NUMBER ,
129 p_override_eftsu IN NUMBER ,
130 p_return_eftsu OUT NOCOPY NUMBER ,
131 p_uoo_id IN NUMBER,
132 -- anilk, Audit special fee build
133 p_include_as_audit IN VARCHAR2 DEFAULT 'N',
134 -- added for EN308 Billable credit point hours build
135 p_audit_cp OUT NOCOPY NUMBER,
136 p_billing_cp OUT NOCOPY NUMBER,
137 p_enrolled_cp OUT NOCOPY NUMBER)
138 RETURN NUMBER;
139 PRAGMA RESTRICT_REFERENCES (ENRP_CLC_SUA_LOAD,WNDS,WNPS);
140 --
141 -- To get the attendance type of load within a nominated load calendar
142 FUNCTION ENRP_GET_LOAD_ATT(
143 p_load_cal_type IN VARCHAR2 ,
144 p_load_figure IN NUMBER )
145 RETURN VARCHAR2;
146 PRAGMA RESTRICT_REFERENCES (ENRP_GET_LOAD_ATT,WNDS,WNPS);
147 --
148 -- To get whether a UA incurs load within a nominated load calendar
149 FUNCTION ENRP_GET_LOAD_INCUR(
150 p_cal_type IN VARCHAR2 ,
151 p_sequence_number IN NUMBER ,
152 p_discontinued_dt IN DATE ,
153 p_administrative_unit_status IN VARCHAR2 ,
154 p_unit_attempt_status IN VARCHAR2 ,
155 p_no_assessment_ind IN VARCHAR2 DEFAULT 'N',
156 p_load_cal_type IN VARCHAR2 ,
157 p_load_sequence_number IN NUMBER,
158 p_uoo_id IN NUMBER DEFAULT NULL,
159 -- anilk, Audit special fee build
160 p_include_audit IN VARCHAR2 DEFAULT 'N' )
161 RETURN VARCHAR2;
162 PRAGMA RESTRICT_REFERENCES (ENRP_GET_LOAD_INCUR,WNDS,WNPS);
163 --
164 -- To get whether a load applies to a UA within a nominated load calendar
165 FUNCTION ENRP_GET_LOAD_APPLY(
166 p_teach_cal_type IN VARCHAR2 ,
167 p_teach_sequence_number IN NUMBER ,
168 p_discontinued_dt IN DATE ,
169 p_administrative_unit_status IN VARCHAR2 ,
170 p_unit_attempt_status IN VARCHAR2 ,
171 p_no_assessment_ind IN VARCHAR2 DEFAULT 'N',
172 p_load_cal_type IN VARCHAR2 ,
173 p_load_sequence_number IN NUMBER,
174 -- anilk, Audit special fee build
175 p_include_audit IN VARCHAR2 DEFAULT 'N' )
176 RETURN VARCHAR2;
177 PRAGMA RESTRICT_REFERENCES (ENRP_GET_LOAD_APPLY,WNDS,WNPS);
178
179 -- Function to get the Key Programs for a Particular Person
180
181 FUNCTION enrp_clc_key_prog
182 (
183 p_person_id IN hz_parties.party_id%TYPE,
184 p_version_number OUT NOCOPY igs_en_su_attempt.version_number%TYPE,
185 p_term_cal_type IN VARCHAR2 DEFAULT NULL,
186 p_term_sequence_number IN NUMBER DEFAULT NULL
187 )
188 RETURN igs_en_su_attempt.course_cd%TYPE;
189
190
191 -- Function to calculate the Institutional Level Attendance Type
192
193 PROCEDURE enrp_get_inst_latt
194 (
195 p_person_id IN hz_parties.party_id%TYPE,
196 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
197 p_load_seq_number IN igs_ca_inst.sequence_number%TYPE,
198 p_attendance OUT NOCOPY igs_en_atd_type_load.attendance_type%TYPE,
199 p_credit_points OUT NOCOPY igs_en_su_attempt.override_achievable_cp%TYPE,
200 p_fte OUT NOCOPY igs_en_su_attempt.override_achievable_cp%TYPE
201 );
202
203 -- anilk, Bug# 3046897
204 -- Function to calculate the Institutional Level Attendance Type, called from View Academic History Page
205 PROCEDURE enrp_get_inst_latt_fte
206 (
207 p_person_id IN hz_parties.party_id%TYPE,
208 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
209 p_load_seq_number IN igs_ca_inst.sequence_number%TYPE,
210 p_attendance OUT NOCOPY igs_en_atd_type_load.attendance_type%TYPE,
211 p_credit_points OUT NOCOPY igs_en_su_attempt.override_achievable_cp%TYPE,
212 p_fte OUT NOCOPY igs_en_su_attempt.override_achievable_cp%TYPE
213 );
214
215 --Procedure to calculate the total credit points with in a given load calendar
216 PROCEDURE enrp_clc_cp_upto_tp_start_dt
217 (
218 p_person_id IN NUMBER,
219 p_load_cal_type IN VARCHAR2,
220 p_load_sequence_number IN NUMBER,
221 p_include_research_ind IN VARCHAR2,
222 p_tp_sd_cut_off_date IN DATE DEFAULT SYSDATE,
223 p_credit_points OUT NOCOPY NUMBER
224 );
225 --Function to get attendace type for person and course and load or adac cal
226 FUNCTION enrp_get_prg_att_type
227 (
228 p_person_id IN NUMBER,
229 p_course_cd IN VARCHAR2,
230 p_cal_type IN VARCHAR2,
231 p_sequence_number IN NUMBER
232 ) RETURN VARCHAR2;
233
234 --Procedure to get latest load for an acad calendar
235 PROCEDURE get_latest_load_for_acad_cal
236 (
237 p_acad_cal_type IN igs_ca_inst.cal_type%TYPE,
238 p_acad_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
239 p_load_cal_type OUT NOCOPY igs_ca_inst.cal_type%TYPE,
240 p_load_ci_sequence_number OUT NOCOPY igs_ca_inst.sequence_number%TYPE
241 )
242 ;
243
244
245 Function ENRP_GET_PRG_LOAD_CP(p_person_id IN NUMBER,
246 p_course_cd IN VARCHAR2,
247 p_cal_type IN VARCHAR2,
248 p_sequence_number IN NUMBER) RETURN VARCHAR2;
249
250
251 --Procedure to get EFTSU and CP for person and course and load or adac cal
252 Procedure enrp_get_prg_eftsu_cp
253 (
254 p_person_id IN NUMBER,
255 p_course_cd IN VARCHAR2,
256 p_cal_type IN VARCHAR2,
257 p_sequence_number IN NUMBER,
258 P_EFTSU_TOTAL OUT NOCOPY NUMBER,
259 P_CREDIT_POINTS OUT NOCOPY NUMBER
260 ) ;
261
262 -- Function to calculate the Institutional Level Attendance Type
263
264 FUNCTION enrp_get_inst_attendance(
265 p_person_id IN hz_parties.party_id%TYPE,
266 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
267 p_load_seq_number IN igs_ca_inst.sequence_number%TYPE
268 ) RETURN VARCHAR2;
269
270 -- Function to calculate the Institutional Level Attendance Type
271 FUNCTION enrp_get_inst_cp(
272 p_person_id IN hz_parties.party_id%TYPE,
273 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
274 p_load_seq_number IN igs_ca_inst.sequence_number%TYPE
275 ) RETURN VARCHAR2;
276
277 -- get_term_credits: Gets the total credits for the given person, program and term.
278 FUNCTION get_term_credits (p_n_person_id IN NUMBER,
279 p_c_program IN VARCHAR2,
280 p_c_load_cal IN VARCHAR2,
281 p_n_load_seq_num IN NUMBER,
282 p_c_acad_cal IN VARCHAR2,
283 p_c_acad_seq_num IN NUMBER) RETURN NUMBER;
284
285 END Igs_En_Prc_Load;