1 PACKAGE igs_ps_validate_lgcy_pkg AS
2 /* $Header: IGSPS86S.pls 120.4 2005/08/04 03:47:28 appldev ship $ */
3
4 /***********************************************************************************************
5 Created By : Sanjeeb Rakshit, Shirish Tatiko, Saravana Kumar
6 Date Created By: 11-NOV-2002
7 Purpose : This package has the some validation function which will be called from sub processes,
8 in IGS_PS_UNIG_LGCY_PKG package.
9 This Package also few generic utility function like set_msg, get_lkup_meaning..
10
11 Known limitations,enhancements,remarks:
12 Change History (in reverse chronological order)
13 Who When What
14 smvk 28-Jul-2004 Bug # 3793580. Created utility procedure get_uso_id.
15 sarakshi 12-Apr-2004 bug#3555871, Removed the function get_call_number
16 smvk 10-Sep-2003 Bug # 3052445. Added the utilitiy function is_waitlist_allowed.
17 smvk 23-Sep-2003 Bug # 3121311, Removed the utility procedures uso_effective_dates and validate_instructor.
18 sarakshi 11-Sep-2003 Enh#3052452,added one parameter to procedure validate_uoo
19 SMVK 27-Jun-2003 Bug # 2999888. Created procedure validate_unit_reference.
20 jbegum 02-June-2003 Bug # 2972950. Created procedure validate_usec_el and uso_effective_dates. Functions post_uso_ins_busi
21 and validate_instructor as mentioned in TD.
22 jbegum 02-June-2003 Added three procedures (post_uso_ins_busi, uso_effective_dates , post_uso_ins ) and a function
23 validate_instructor, As per PSP Legacy Enhancements TD. Bug #2972950
24 smvk 26-Dec-2002 Added a generic procedure (get_party_id) and a function (validate_staff_person)
25 As a part of Bug # 2721495
26 ********************************************************************************************** */
27
28 -- This function does validations before inserting Unit Version Records
29 PROCEDURE unit_version(p_unit_ver_rec IN OUT NOCOPY igs_ps_generic_pub.unit_ver_rec_type,
30 p_coord_person_id IN igs_ps_unit_ver_all.coord_person_id%TYPE);
31
32 -- This function does validations after inserting Teaching Responsibility Records
33 FUNCTION post_teach_resp ( p_tab_teach_resp IN OUT NOCOPY igs_ps_generic_pub.unit_tr_tbl_type ) RETURN BOOLEAN;
34
35 -- This function does validations after inserting Unit Discipline Records
36 FUNCTION post_unit_discip ( p_tab_unit_dscp IN OUT NOCOPY igs_ps_generic_pub.unit_dscp_tbl_type ) RETURN BOOLEAN;
37
38 -- This procedure validates before inserting Unit Grading Schema records
39 PROCEDURE validate_unit_grd_sch ( p_unit_gs_rec IN OUT NOCOPY igs_ps_generic_pub.unit_gs_rec_type );
40
41 -- This function does validations after inserting Unit Grading Schema Records
42 FUNCTION post_unit_grd_sch ( p_tab_grd_sch IN OUT NOCOPY igs_ps_generic_pub.unit_gs_tbl_type ) RETURN BOOLEAN;
43
44 -- Validate Unit Offer Option Records before inserting them
45 PROCEDURE validate_uoo ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,
46 p_c_cal_type IN igs_ca_type.cal_type%TYPE,
47 p_n_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
48 p_n_sup_uoo_id IN OUT NOCOPY igs_ps_unit_ofr_opt_all.sup_uoo_id%TYPE,
49 p_insert_update VARCHAR2,
50 p_conc_flag OUT NOCOPY BOOLEAN);
51
52 -- Validate Unit Section Credit Points Records before inserting them
53 PROCEDURE validate_cps ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,
54 p_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
55 p_insert_update VARCHAR2) ;
56
57 -- Validate Unit Section Referece Records before inserting them
58 PROCEDURE validate_ref ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,
59 p_n_subtitle_id OUT NOCOPY igs_ps_unit_subtitle.subtitle_id%TYPE,
60 p_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
61 p_insert_update VARCHAR2);
62
63 -- This procedure validates before inserting Unit Section Grading Schema records
64 PROCEDURE validate_usec_grd_sch ( p_usec_gs_rec IN OUT NOCOPY igs_ps_generic_pub.usec_gs_rec_type,
65 p_n_uoo_id IN NUMBER);
66
67 -- This function does validations after inserting Unit Section Grading Schema Records
68 FUNCTION post_usec_grd_sch ( p_tab_usec_gs IN OUT NOCOPY igs_ps_generic_pub.usec_gs_tbl_type,
69 p_tab_uoo IN igs_ps_create_generic_pkg.uoo_tbl_type) RETURN BOOLEAN ;
70
71 -- This procedure validates before inserting records of Unit Section Occurrence.
72 PROCEDURE validate_usec_occurs ( p_uso_rec IN OUT NOCOPY igs_ps_generic_pub.uso_rec_type,
73 p_n_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
74 p_d_start_date IN igs_ca_inst_all.start_dt%TYPE,
75 p_d_end_date IN igs_ca_inst_all.end_dt%TYPE,
76 p_n_building_code IN NUMBER,
77 p_n_room_code IN NUMBER,
78 p_n_dedicated_building_code IN NUMBER,
79 p_n_dedicated_room_code IN NUMBER,
80 p_n_preferred_building_code IN NUMBER,
81 p_n_preferred_room_code IN NUMBER,
82 p_n_uso_id IN NUMBER,
83 p_insert IN VARCHAR2,
84 p_calling_context IN VARCHAR2,
85 p_notify_status OUT NOCOPY VARCHAR2,
86 p_schedule_status IN OUT NOCOPY VARCHAR2
87 );
88
89 -- This procedure does the business validation for Unit / Unit Section / Unit Section Occurrence reference codes.
90 PROCEDURE validate_unit_reference(p_unit_ref_rec IN OUT NOCOPY igs_ps_generic_pub.unit_ref_rec_type,
91 p_n_uoo_id IN NUMBER,
92 p_n_uso_id IN NUMBER,
93 p_calling_context IN VARCHAR2);
94
95 -- This procedure sets the message in the message stack
96 PROCEDURE set_msg( p_c_msg_name IN VARCHAR2,
97 p_c_token IN VARCHAR2 DEFAULT NULL,
98 p_c_lkup_type IN VARCHAR2 DEFAULT NULL,
99 p_b_delete_flag IN BOOLEAN DEFAULT FALSE
100 );
101
102 -- This generic function gets the meaning for the given lookup_code and lookup_type
103 FUNCTION get_lkup_meaning(p_c_lkup_cd IN VARCHAR2,
104 p_c_lkup_type IN VARCHAR2
105 ) RETURN VARCHAR2 ;
106
107
108 -- This generic function gets the Unit Offering Options Identifier (Unit Section identifier)
109 PROCEDURE get_uoo_id( p_unit_cd IN VARCHAR2,
110 p_ver_num IN NUMBER,
111 p_cal_type IN VARCHAR2,
112 p_seq_num IN NUMBER,
113 p_loc_cd IN VARCHAR2,
114 p_unit_class IN VARCHAR2,
115 p_uoo_id OUT NOCOPY NUMBER,
116 p_message OUT NOCOPY VARCHAR2
117 );
118
119 -- This Function will validate whether waitlisting is allowed for given organization unit code.
120 FUNCTION validate_waitlist_allowed ( p_c_cal_type IN igs_ca_type.cal_type%TYPE,
121 p_n_seq_num IN igs_ca_inst_all.sequence_number%TYPE ) RETURN BOOLEAN;
122
123
124 -- This function checks whether given Grading schema is of type p_c_gs_type or not.
125 FUNCTION validate_gs_type ( p_c_gs_cd IN VARCHAR2, p_n_gs_ver IN NUMBER, p_c_gs_type IN VARCHAR2 DEFAULT 'UNIT' ) RETURN BOOLEAN;
126
127 -- Validates calendar type whether its of give category or not
128 FUNCTION validate_cal_cat ( p_c_cal_type IN igs_ca_inst_all.cal_type%TYPE,
129 p_c_cal_cat IN igs_ca_type.s_cal_cat%TYPE DEFAULT 'TEACHING' ) RETURN BOOLEAN;
130
131 -- Validate Orgaization Unit Code
132 FUNCTION validate_org_unit_cd ( p_c_org_unit_cd IN igs_ps_unit_ver_all.owner_org_unit_cd%TYPE,
133 p_c_object_name IN VARCHAR2 ) RETURN BOOLEAN;
134
135 PROCEDURE get_party_id(p_c_person_number IN hz_parties.party_number%TYPE,
136 p_n_person_id OUT NOCOPY hz_parties.party_id%TYPE) ;
137 --
138 PROCEDURE validate_usec_el(p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,
139 p_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
140 p_insert_update VARCHAR2);
141
142 PROCEDURE post_usec_limits(p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,
143 p_calling_context IN VARCHAR2,
144 p_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
145 p_insert_update VARCHAR2);
146
147 -- Procedure to do the post unit section occurrence instructor validation.
148 PROCEDURE post_uso_ins( p_n_ins_id IN igs_ps_uso_instrctrs.instructor_id%TYPE,
149 p_n_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
150 p_uso_ins_rec IN OUT NOCOPY igs_ps_generic_pub.uso_ins_rec_type,
151 p_n_index IN NUMBER);
152
153 -- Procedure to do validate the unit section section teaching responsibility record after importing unit section occurrence instructors.
154 FUNCTION post_uso_ins_busi( p_tab_uso_ins IN OUT NOCOPY igs_ps_generic_pub.uso_ins_tbl_type) RETURN BOOLEAN;
155
156 -- This Function will returns true if waitlist is allowed otherwise false.
157 FUNCTION is_waitlist_allowed RETURN BOOLEAN;
158
159 -- Procedure to derive unit section occurrence identifier.
160 PROCEDURE get_uso_id( p_uoo_id IN NUMBER,
161 p_occurrence_identifier IN VARCHAR2,
162 p_uso_id OUT NOCOPY NUMBER,
163 p_message OUT NOCOPY VARCHAR2
164 );
165 --Function to validate if a person is a staff/faculty
166 FUNCTION validate_staff_faculty (p_person_id IN NUMBER ) RETURN BOOLEAN ;
167
168 --Function to validate whether a uoo_id exists in the pl/sql table p_tab_uoo
169 FUNCTION isexists(p_n_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
170 p_tab_uoo IN igs_ps_create_generic_pkg.uoo_tbl_type) RETURN BOOLEAN ;
171
172 --Function to check whether import is allowed in context to scheduling (overlodad function)
173 FUNCTION check_import_allowed( p_unit_cd IN VARCHAR2,
174 p_version_number IN NUMBER,
175 p_alternate_code IN VARCHAR2,
176 p_location_cd IN VARCHAR2,
177 p_unit_class IN VARCHAR2,
178 p_uso_id IN NUMBER) RETURN BOOLEAN;
179
180 --Function to check whether import is allowed in context to scheduling (overlodad function)
181 FUNCTION check_import_allowed( p_uoo_id IN NUMBER,p_uso_id IN NUMBER) RETURN BOOLEAN;
182
183 --Function to check whether a unit section status is NOT_OFFERED
184 FUNCTION check_not_offered_usec_status(p_uoo_id IN NUMBER ) RETURN BOOLEAN;
185
186 FUNCTION boundary_check_number( p_n_value IN NUMBER,p_n_int_part IN NUMBER,p_n_dec_part IN NUMBER) RETURN BOOLEAN;
187
188 END igs_ps_validate_lgcy_pkg;