1 PACKAGE igs_ss_enr_details AS
2 /* $Header: IGSSS05S.pls 120.11 2005/09/20 06:00:34 appldev ship $ */
3
4 FUNCTION get_location_bldg_room
5 (
6 p_uoo_id IN NUMBER
7 ) RETURN VARCHAR2 ;
8 FUNCTION get_instructor_day_time
9 (
10 p_uoo_id IN NUMBER
11 ) RETURN VARCHAR2 ;
12 FUNCTION get_programs
13 (
14 p_person_id IN NUMBER
15 ) RETURN VARCHAR2 ;
16 FUNCTION get_occur_desc_details
17 (
18 p_uoo_id IN NUMBER
19 ) RETURN VARCHAR2 ;
20 FUNCTION get_occur_cd_details
21 (
22 p_uoo_id IN NUMBER
23 ) RETURN VARCHAR2 ;
24
25 FUNCTION get_occur_details_no_location
26 (
27 p_uoo_id IN NUMBER
28 ) RETURN VARCHAR2 ;
29 FUNCTION get_usec_ref_cd
30 (
31 p_uoo_id IN NUMBER
32 ) RETURN VARCHAR2 ;
33 FUNCTION get_usec_occurs_ref_cd
34 (
35 p_uoo_id IN NUMBER
36 ) RETURN VARCHAR2 ;
37 FUNCTION get_unit_note
38 (
39 p_unit_cd IN VARCHAR2,
40 p_version_NUMBER IN NUMBER
41 ) RETURN VARCHAR2 ;
42 FUNCTION get_usec_note
43 (
44 p_uoo_id IN NUMBER
45 ) RETURN VARCHAR2 ;
46 /*
47 | Added for November 2001 |
48 */
49 --Purpose: To get the title and subtitle from student level. If not available
50 -- at student level then from unit section level and if not avialble at
51 -- section level then from unit level
52 -- if person id is null then only the unit section and unit level would be seen
53 -- level
54 -- To be used only in self service as then fields are seperated by <BR> tag
55 -- and this is to be used only in SS.
56 FUNCTION get_title_section
57 (
58 p_person_id IN NUMBER,
59 p_uoo_id IN NUMBER,
60 p_unit_cd IN VARCHAR2,
61 p_version_number IN NUMBER,
62 p_course_cd IN VARCHAR2
63
64 ) RETURN VARCHAR2;
65
66 FUNCTION get_title
67 (
68 p_person_id IN NUMBER,
69 p_uoo_id IN NUMBER,
70 p_unit_cd IN VARCHAR2,
71 p_version_number IN NUMBER,
72 p_course_cd IN VARCHAR2
73
74 ) RETURN VARCHAR2;
75
76 FUNCTION get_subtitle
77 (
78 p_person_id IN NUMBER,
79 p_uoo_id IN NUMBER,
80 p_unit_cd IN VARCHAR2,
81 p_version_number IN NUMBER,
82 p_course_cd IN VARCHAR2
83
84 ) RETURN VARCHAR2;
85
86 --Purpose: To get the grading schema from student level. If not available
87 -- at student level then from unit section level and if not avialble at
88 -- section level then from unit level
89 -- if person id is null then only the unit section and unit level would be seen
90 FUNCTION get_grading_schema
91 (
92 p_person_id IN NUMBER,
93 p_uoo_id IN NUMBER,
94 p_unit_cd IN VARCHAR2,
95 p_version_NUMBER IN NUMBER,
96 p_course_cd IN VARCHAR2
97 )RETURN VARCHAR2;
98
99 --Purpose: To get the grading schema version from student level. If not available
100 -- at student level then from unit section level and if not avialble at
101 -- section level then from unit level
102 -- if person id is null then only the unit section and unit level would be seen
103 FUNCTION get_grading_schema_ver
104 (
105 p_person_id IN NUMBER,
106 p_uoo_id IN NUMBER,
107 p_unit_cd IN VARCHAR2,
108 p_version_NUMBER IN NUMBER,
109 p_course_cd IN VARCHAR2
110 )RETURN NUMBER;
111
112 --Purpose: To get the grading schema description from SUA level. If not available
113 -- at SUA level then from unit section level and if not avialble at
114 -- section level then from unit level
115 -- if person id is null then only the unit section and unit level would be seen
116 FUNCTION get_grading_schema_desc
117 (
118 p_person_id IN NUMBER,
119 p_uoo_id IN NUMBER,
120 p_unit_cd IN VARCHAR2,
121 p_version_NUMBER IN NUMBER,
122 p_course_cd IN VARCHAR2
123 )RETURN VARCHAR2;
124
125
126 --Purpose: To get the grading schema code and version number from SUA level. If not available
127 -- at SUA level then from unit section level and if not avialble at
128 -- section level then from unit level
129 -- if person id is null then only the unit section and unit level would be seen
130 FUNCTION get_grading_cd_ver
131 (
132 p_person_id IN NUMBER,
133 p_uoo_id IN NUMBER,
134 p_unit_cd IN VARCHAR2,
135 p_version_NUMBER IN NUMBER,
136 p_course_cd IN VARCHAR2
137 )RETURN VARCHAR2;
138
139
140 -- Purpose: To get the overrid enrolled credit points from student level. If not available
141 -- at student level then from unit section level and if not avialble at
142 -- section level then from unit level
143 -- at unit level if the variable credit poins indicator is checked then
144 -- the achievable credit points is not null at section level then fetch that value
145 -- if that is null or the indiactor is not set then get the enrolled credit points
146 -- at the unit level
147 -- this is because it is assumed that at section level if variable credit points are
148 -- to be defined then it would be stored in achievable credit points field as enrolled
149 -- credit points are not stored at section level
150 FUNCTION get_credit_points
151 (
152 p_person_id IN NUMBER,
153 p_uoo_id IN NUMBER,
154 p_unit_cd IN VARCHAR2,
155 p_version_NUMBER IN NUMBER,
156 p_course_cd IN VARCHAR2
157 )RETURN NUMBER;
158
159 FUNCTION get_allowable_cp_range
160 (
161 p_uoo_id IN NUMBER
162 )
163 RETURN VARCHAR2;
164
165 -- to return the primary program, version and all the enrolled program
166 -- for a caerer
167
168 PROCEDURE enrp_get_prgm_for_career
169 (
170 p_primary_program OUT NOCOPY VARCHAR2,
171 p_primary_program_version OUT NOCOPY NUMBER,
172 p_programlist OUT NOCOPY VARCHAR2,
173 p_person_id IN NUMBER,
174 p_carrer IN VARCHAR2,
175 p_term_cal_type IN VARCHAR2 DEFAULT NULL,
176 p_term_sequence_number IN NUMBER DEFAULT NULL
177 );
178 -- Is subtitle modifiable by admin at sua level
179
180 FUNCTION enrp_val_subttl_chg ( p_person_id IN NUMBER,
181 p_uoo_id IN NUMBER
182 ) RETURN CHAR;
183
184 FUNCTION get_notification(
185 p_person_type VARCHAR2,
186 p_enrollment_category VARCHAR2,
187 p_comm_type VARCHAR2,
188 p_enr_method_type VARCHAR2,
189 p_step_group_type VARCHAR2,
190 p_step_type VARCHAR2,
191 p_person_id NUMBER,
192 p_message OUT NOCOPY VARCHAR2
193 ) RETURN VARCHAR2;
194
195 FUNCTION get_usec_eff_dates(
196 x_unit_cd VARCHAR2,
197 x_version NUMBER,
198 x_cal_type VARCHAR2,
199 x_ci_seq_number NUMBER,
200 x_location_cd VARCHAR2,
201 x_unit_class VARCHAR2) RETURN VARCHAR2;
202
203 PROCEDURE get_enrollment_limits(
204 p_uooid NUMBER,
205 p_unitcode VARCHAR2,
206 p_version NUMBER,
207 p_actenrolled OUT NOCOPY NUMBER,
208 p_maxlimit OUT NOCOPY NUMBER ,
209 p_minlimit OUT NOCOPY NUMBER );
210
211 /* Procedure to get the Latest Term which has some ENROLLED unit attempt for the given person/program */
212 PROCEDURE enrp_get_enr_term
213 ( p_person_id IN NUMBER,
214 p_course_cd IN VARCHAR2,
215 p_cal_type OUT NOCOPY VARCHAR2,
216 p_sequence_number OUT NOCOPY NUMBER,
217 p_term_desc OUT NOCOPY VARCHAR2
218 );
219
220 /* This Function returns Lead Instructor of a Unit Section if it exists otherwise returns NULL */
221 FUNCTION get_lead_instructor_name(
222 p_uoo_id IN NUMBER
223 ) RETURN VARCHAR2;
224
225 /*This function returns the max waitlist defined at organization level for teaching calendar */
226 FUNCTION get_max_std_wait_org_level(
227 p_owner_org_unit_cd IN VARCHAR2,
228 p_cal_type IN VARCHAR2,
229 p_sequence_number IN NUMBER
230 ) RETURN NUMBER;
231
232 /* This Function returns Instructor(s) names */
233 /* for a given uoo_id if it exists otherwise returns NULL */
234 /* created for bug 2446078 - TNATARAJ */
235 FUNCTION get_usec_instructor_names(
236 p_uoo_id IN NUMBER
237 ) RETURN VARCHAR2;
238
239
240
241
242 /*
243 --
244 -- Procedure added to get the Group Name and Group Type of the passed Unit Section,
245 -- if it belongs to any cross-listed / meet with group.
246 --
247 */
248
249 PROCEDURE Enrp_Get_Usec_Group (
250 p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
251 p_return_status OUT NOCOPY VARCHAR2,
252 p_group_type OUT NOCOPY igs_lookups_view.meaning%TYPE,
253 p_group_name OUT NOCOPY igs_ps_usec_x_grp.usec_x_listed_group_name%TYPE
254 );
255
256 /*
257 --
258 -- The following function returns the concatenated value of Enrollment Maximum and
259 -- Actual Enrollment if the passed unit section belongs to any cross-listed / meet with group.
260 --
261 */
262
263 FUNCTION Enrp_Get_Enr_Max_Act (
264 p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE
265 ) RETURN VARCHAR2;
266
267
268 /*
269 --
270 -- The following function is a wrapper to the procedure Enrp_Get_Usec_Group.
271 -- This function returns the value of Y/N deponding upon whether the unit section
272 -- belongs to any group or not. It is called from the view IGS_SS_EN_ENROLL_CART_RSLT_V.
273 --
274 */
275
276 FUNCTION Enrp_Chk_Usec_Group (
277 p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE
278 ) RETURN VARCHAR2;
279
280 --
281 -- Added as Part of EN213 Build
282 -- This Function checks whether the given unit section is a core unit or not in the
283 -- current pattern of study for the given student program attempt.
284 --
285 FUNCTION get_core_disp_unit(
286 p_person_id IN NUMBER ,
287 p_program_cd IN VARCHAR2 ,
288 p_uoo_id IN NUMBER )
289 RETURN VARCHAR2;
290
291 --
292 -- This procedure checks whether the given enrollment category step is defined in the system or not
293 --
294 PROCEDURE get_enr_cat_step(
295 p_person_id IN NUMBER ,
296 p_program_cd IN VARCHAR2 ,
297 p_enr_cat_prc_step IN VARCHAR2,
298 p_ret_status OUT NOCOPY VARCHAR2);
299
300 --
301 -- This Function returns the current YOP unit set title
302 --
303 FUNCTION get_stud_yop_unit_set(
304 p_person_id IN NUMBER ,
305 p_program_cd IN VARCHAR2 ,
306 p_term_cal_type IN VARCHAR2,
307 p_term_sequence_number IN NUMBER)
308 RETURN VARCHAR2;
309
310 --
311 -- This Function returns the title of the given program or given primary program
312 --
313 FUNCTION get_pri_prg_title(
314 p_person_id IN NUMBER ,
315 p_program_cd IN VARCHAR2 ,
316 p_term_cal_type IN VARCHAR2 DEFAULT NULL,
317 p_term_sequence_number IN NUMBER DEFAULT NULL)
318 RETURN VARCHAR2;
319
320 --
321 -- This Function returns return the concatenated titles of all the secondary programs in the same career of the given program
322 --
323 FUNCTION get_sec_prg_title(
324 p_person_id IN NUMBER ,
325 p_program_cd IN VARCHAR2,
326 p_program_version IN NUMBER ,
327 p_term_cal_type IN VARCHAR2 DEFAULT NULL,
328 p_term_sequence_number IN NUMBER DEFAULT NULL)
329 RETURN VARCHAR2;
330 --this function checks if the passed in uoo_id is superior or subordinate or none
331 FUNCTION enrf_is_sup_sub(
332 p_uoo_id IN NUMBER
333 ) RETURN VARCHAR2;
334
335 FUNCTION get_sup_sub_uoo_ids (
336 p_uoo_id IN NUMBER,
337 p_relation_type IN VARCHAR2,
338 p_sup_uoo_id IN NUMBER)
339 RETURN VARCHAR2;
340 --this function returns the relation type of the uoo_id
341 FUNCTION get_sup_sub_details (
342 p_uoo_id IN NUMBER,
343 p_sup_uoo_id IN NUMBER,
344 p_relation_type IN VARCHAR2
345 ) RETURN VARCHAR2 ;
346
347 -- Procedure to get the level at which notes is defined for give unit section.
348 -- Procedure retuns the following values in the out variable p_c_dfn_lvl.
349 -- 'UNIT_SECTION' - when the notes are defined at unit section level.
350 -- 'UNIT_OFFERING_PATTERN' - when the notes are defined at unit offering pattern level.
351 -- 'UNIT_OFFERING' - when the notes are defined at unit offering level.
352 -- 'UNIT_VERSION' - when the notes are defined at unit version level.
353 -- 'NOTES_UN_DEFINED' - when the notes are not defined at any of the above levels.
354 PROCEDURE get_notes_defn_lvl (
355 p_n_uoo_id IN NUMBER,
356 p_c_dfn_lvl OUT NOCOPY VARCHAR2);
357
358 --function to return if a Duplicate SUA can be uncheked on transfer page.
359
360 FUNCTION GET_DUP_SUA_SELECTION (
361 p_person_id IN NUMBER,
362 p_src_course_cd IN VARCHAR2,
363 p_dest_course_cd IN VARCHAR2,
364 p_uoo_id IN NUMBER
365 ) RETURN VARCHAR2;
366
367 FUNCTION get_title_for_unit(p_unit_cd IN VARCHAR2, p_version IN NUMBER) RETURN VARCHAR2;
368
369 FUNCTION get_max_waitlist_for_unit(p_uoo_id IN NUMBER, p_unit_cd IN VARCHAR2,
370 p_version IN NUMBER,
371 p_cal_type IN VARCHAR2, p_sequence_number IN NUMBER,
372 p_owner_org_unit_cd IN VARCHAR2) RETURN NUMBER;
373
374
375 FUNCTION get_enroll_max_for_unit(p_uooid IN NUMBER, p_unit_cd IN VARCHAR2,
379 p_version IN NUMBER) RETURN NUMBER;
376 p_version IN NUMBER) RETURN NUMBER;
377
378 FUNCTION get_enroll_min_for_unit(p_uooid IN NUMBER, p_unit_cd IN VARCHAR2,
380
381
382 -- Function to get alias value for the given calendar instance and date alias.
383 FUNCTION get_alias_val (p_c_cal_type IN VARCHAR2,
387
384 p_n_seq_num IN NUMBER,
385 p_c_dt_alias IN VARCHAR2) RETURN DATE ;
386
388 -- Function to check whether timeslot is open or close for a student
389 -- returns true if the timeslot is open otherwise false
390 FUNCTION stu_timeslot_open (p_n_person_id IN NUMBER,
391 p_c_person_type IN VARCHAR2,
392 p_c_program_cd IN VARCHAR2,
393 p_c_cal_type IN VARCHAR2,
394 p_n_seq_num IN NUMBER)
395 RETURN BOOLEAN;
396
397
398
399 FUNCTION get_meeting_pattern (p_n_uoo_id IN NUMBER) RETURN VARCHAR2;
400 FUNCTION get_usec_instructors(p_n_uoo_id IN NUMBER) RETURN VARCHAR2;
401 FUNCTION get_us_title(p_n_uoo_id IN NUMBER) RETURN VARCHAR2;
402 FUNCTION get_rule_text(p_rule_type IN VARCHAR2, p_n_uoo_id IN NUMBER) RETURN VARCHAR2;
403 FUNCTION get_special_audit_status(p_uoo_id IN NUMBER) RETURN VARCHAR2;
404 FUNCTION get_apor_credits(p_uoo_id IN NUMBER,
405 p_override_enrolled_cp IN NUMBER,
406 p_term_cal_type IN VARCHAR2,
407 p_term_seq_num IN NUMBER
408 ) RETURN NUMBER;
409 FUNCTION get_billable_credit_points(p_uoo_id IN IGS_PS_UNIT_OFR_OPT_ALL.uoo_id%TYPE) RETURN NUMBER;
410
411 FUNCTION get_uso_instructors(p_n_uso_id IN NUMBER) RETURN VARCHAR2;
412 FUNCTION get_meaning (p_c_lkup_type IN VARCHAR2,p_c_lkup_code IN VARCHAR2 ) RETURN VARCHAR2;
413
414 FUNCTION get_coreq_units(p_uoo_id IN NUMBER) RETURN VARCHAR2;
415
416 FUNCTION is_unit_rule_defined(p_uoo_id IN NUMBER,
417 p_rule_type IN VARCHAR2) RETURN VARCHAR2;
418
419 FUNCTION get_special_status(p_uoo_id IN NUMBER) RETURN VARCHAR2;
420
421 FUNCTION get_audit_status(p_uoo_id IN NUMBER) RETURN VARCHAR2;
422
423 FUNCTION is_audit_allowed(p_uoo_id IN NUMBER,
424 p_person_type IN VARCHAR2) RETURN VARCHAR2;
425
426 FUNCTION is_placement_allowed (p_unit_cd IN VARCHAR2, p_version_number IN NUMBER) RETURN VARCHAR2;
427
428 FUNCTION get_enrollment_capacity(p_uoo_id NUMBER) RETURN VARCHAR2;
429
430 FUNCTION get_total_plan_credits(p_personid NUMBER,
431 p_course_cd VARCHAR2,
432 p_term_cal_type VARCHAR2,
433 p_term_seq_num NUMBER) RETURN NUMBER;
434
435 FUNCTION get_enr_period_open_status( p_person_id IN NUMBER,
436 p_course_cd IN VARCHAR2,
437 p_load_calendar_type IN VARCHAR2,
438 p_load_cal_sequence_number IN NUMBER,
439 p_person_type IN VARCHAR2,
440 p_message OUT NOCOPY VARCHAR2)
441 RETURN BOOLEAN;
442
443 FUNCTION is_selection_enabled ( p_person_id IN NUMBER,
444 p_load_cal_type IN VARCHAR2,
445 p_load_seq_num IN NUMBER,
446 p_person_type IN VARCHAR2,
447 p_message OUT NOCOPY VARCHAR2
448 ) RETURN VARCHAR2;
449
450 FUNCTION get_us_subtitle (p_n_uoo_id IN NUMBER) RETURN VARCHAR2 ;
451
452 FUNCTION get_waitlist_capacity(p_uoo_id NUMBER, p_unit_cd IN VARCHAR2,
453 p_version IN NUMBER,
454 p_cal_type IN VARCHAR2, p_sequence_number IN NUMBER,
455 p_owner_org_unit_cd IN VARCHAR2) RETURN VARCHAR2;
456
457 FUNCTION get_class_day (p_n_uso_id IN NUMBER) RETURN VARCHAR2;
458
459 FUNCTION get_class_time (p_n_uso_id IN NUMBER) RETURN VARCHAR2;
460
461 FUNCTION get_calling_object ( p_person_id IN NUMBER,
462 p_course_cd IN VARCHAR2,
463 p_load_cal_type IN VARCHAR2,
464 p_load_seq_num IN NUMBER,
465 p_person_type IN VARCHAR2,
466 p_message OUT NOCOPY VARCHAR2
467 ) RETURN VARCHAR2 ;
468
469 FUNCTION can_drop (p_cal_type IN VARCHAR2,
470 p_ci_sequence_number IN NUMBER,
471 p_effective_dt IN DATE,
472 p_uoo_id IN NUMBER,
473 p_c_core IN VARCHAR2,
474 p_n_person_id IN NUMBER,
475 p_c_course_cd IN VARCHAR2
476 ) RETURN VARCHAR2 ;
477
478 FUNCTION get_tba_desc RETURN VARCHAR2 ;
479
480 FUNCTION get_occur_dates (p_n_uso_id IN NUMBER) RETURN VARCHAR2;
481
482 PROCEDURE get_definition_levels (p_n_uoo_id IN NUMBER,
483 p_c_notes_lvl OUT NOCOPY VARCHAR2,
484 p_c_ref_lvl OUT NOCOPY VARCHAR2,
485 p_c_unit_cd OUT NOCOPY VARCHAR2,
486 p_n_version OUT NOCOPY NUMBER,
487 p_n_us_ref_id OUT NOCOPY NUMBER);
488
489 FUNCTION get_sua_core_disp_unit(p_person_id IN NUMBER ,
490 p_program_cd IN VARCHAR2 ,
491 p_uoo_id IN NUMBER)RETURN VARCHAR2;
492
493 FUNCTION get_total_cart_credits(p_personid IN NUMBER,
494 p_course_cd IN VARCHAR2,
498 FUNCTION get_sca_unit_sets( p_person_id IN NUMBER ,
495 p_term_cal_type IN VARCHAR2,
496 p_term_seq_num IN NUMBER) RETURN NUMBER;
497
499 p_program_cd IN VARCHAR2 ,
500 p_term_cal_type IN VARCHAR2,
501 p_term_sequence_number IN NUMBER) RETURN VARCHAR2;
502
503 FUNCTION get_sup_sub_text (p_uoo_id IN NUMBER,
504 p_sup_uoo_id IN NUMBER,
505 p_relation_type IN VARCHAR2
506 ) RETURN VARCHAR2 ;
507
508 FUNCTION get_none_desc RETURN VARCHAR2;
509
510 FUNCTION is_enr_open(p_load_cal IN varchar2,
511 p_load_seq_num IN Number,
512 p_d_date IN DATE,
513 p_n_uoo_id IN NUMBER) RETURN VARCHAR2;
514
515 FUNCTION get_total_cart_units(p_personid NUMBER,
516 p_course_cd VARCHAR2,
517 p_term_cal_type VARCHAR2,
518 p_term_seq_num NUMBER) RETURN NUMBER;
519
520 END igs_ss_enr_details;
[Home] [Help]