1 package IGS_PR_STDNT_PR_OU_PKG AS
2 /* $Header: IGSQI15S.pls 120.0 2005/07/05 11:21:59 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_PERSON_ID in NUMBER,
6 X_COURSE_CD in VARCHAR2,
7 X_SEQUENCE_NUMBER in NUMBER,
8 X_PRG_CAL_TYPE in VARCHAR2,
9 X_PRG_CI_SEQUENCE_NUMBER in NUMBER,
10 X_RULE_CHECK_DT in DATE,
11 X_PROGRESSION_RULE_CAT in VARCHAR2,
12 X_PRA_SEQUENCE_NUMBER in NUMBER,
13 X_PRO_SEQUENCE_NUMBER in NUMBER,
14 X_PROGRESSION_OUTCOME_TYPE in VARCHAR2,
15 X_DURATION in NUMBER,
16 X_DURATION_TYPE in VARCHAR2,
17 X_DECISION_STATUS in VARCHAR2,
18 X_DECISION_DT in DATE,
19 X_DECISION_ORG_UNIT_CD in VARCHAR2,
20 X_DECISION_OU_START_DT in DATE,
21 X_APPLIED_DT in DATE,
22 X_SHOW_CAUSE_EXPIRY_DT in DATE,
23 X_SHOW_CAUSE_DT in DATE,
24 X_SHOW_CAUSE_OUTCOME_DT in DATE,
25 X_SHOW_CAUSE_OUTCOME_TYPE in VARCHAR2,
26 X_APPEAL_EXPIRY_DT in DATE,
27 X_APPEAL_DT in DATE,
28 X_APPEAL_OUTCOME_DT in DATE,
29 X_APPEAL_OUTCOME_TYPE in VARCHAR2,
30 X_ENCMB_COURSE_GROUP_CD in VARCHAR2,
31 X_RESTRICTED_ENROLMENT_CP in NUMBER,
32 X_RESTRICTED_ATTENDANCE_TYPE in VARCHAR2,
33 X_COMMENTS in VARCHAR2,
34 X_SHOW_CAUSE_COMMENTS in VARCHAR2,
35 X_APPEAL_COMMENTS in VARCHAR2,
36 X_EXPIRY_DT in DATE,
37 X_PRO_PRA_SEQUENCE_NUMBER in NUMBER,
38 X_MODE in VARCHAR2 default 'R',
39 X_ORG_ID in NUMBER
40 );
41 procedure LOCK_ROW (
42 X_ROWID in VARCHAR2,
43 X_PERSON_ID in NUMBER,
44 X_COURSE_CD in VARCHAR2,
45 X_SEQUENCE_NUMBER in NUMBER,
46 X_PRG_CAL_TYPE in VARCHAR2,
47 X_PRG_CI_SEQUENCE_NUMBER in NUMBER,
48 X_RULE_CHECK_DT in DATE,
49 X_PROGRESSION_RULE_CAT in VARCHAR2,
50 X_PRA_SEQUENCE_NUMBER in NUMBER,
51 X_PRO_SEQUENCE_NUMBER in NUMBER,
52 X_PROGRESSION_OUTCOME_TYPE in VARCHAR2,
53 X_DURATION in NUMBER,
54 X_DURATION_TYPE in VARCHAR2,
55 X_DECISION_STATUS in VARCHAR2,
56 X_DECISION_DT in DATE,
57 X_DECISION_ORG_UNIT_CD in VARCHAR2,
58 X_DECISION_OU_START_DT in DATE,
59 X_APPLIED_DT in DATE,
60 X_SHOW_CAUSE_EXPIRY_DT in DATE,
61 X_SHOW_CAUSE_DT in DATE,
62 X_SHOW_CAUSE_OUTCOME_DT in DATE,
63 X_SHOW_CAUSE_OUTCOME_TYPE in VARCHAR2,
64 X_APPEAL_EXPIRY_DT in DATE,
65 X_APPEAL_DT in DATE,
66 X_APPEAL_OUTCOME_DT in DATE,
67 X_APPEAL_OUTCOME_TYPE in VARCHAR2,
68 X_ENCMB_COURSE_GROUP_CD in VARCHAR2,
69 X_RESTRICTED_ENROLMENT_CP in NUMBER,
70 X_RESTRICTED_ATTENDANCE_TYPE in VARCHAR2,
71 X_COMMENTS in VARCHAR2,
72 X_SHOW_CAUSE_COMMENTS in VARCHAR2,
73 X_APPEAL_COMMENTS in VARCHAR2,
74 X_EXPIRY_DT in DATE,
75 X_PRO_PRA_SEQUENCE_NUMBER in NUMBER
76 );
77 procedure UPDATE_ROW (
78 X_ROWID in VARCHAR2,
79 X_PERSON_ID in NUMBER,
80 X_COURSE_CD in VARCHAR2,
81 X_SEQUENCE_NUMBER in NUMBER,
82 X_PRG_CAL_TYPE in VARCHAR2,
83 X_PRG_CI_SEQUENCE_NUMBER in NUMBER,
84 X_RULE_CHECK_DT in DATE,
85 X_PROGRESSION_RULE_CAT in VARCHAR2,
86 X_PRA_SEQUENCE_NUMBER in NUMBER,
87 X_PRO_SEQUENCE_NUMBER in NUMBER,
88 X_PROGRESSION_OUTCOME_TYPE in VARCHAR2,
89 X_DURATION in NUMBER,
90 X_DURATION_TYPE in VARCHAR2,
91 X_DECISION_STATUS in VARCHAR2,
92 X_DECISION_DT in DATE,
93 X_DECISION_ORG_UNIT_CD in VARCHAR2,
94 X_DECISION_OU_START_DT in DATE,
95 X_APPLIED_DT in DATE,
96 X_SHOW_CAUSE_EXPIRY_DT in DATE,
97 X_SHOW_CAUSE_DT in DATE,
98 X_SHOW_CAUSE_OUTCOME_DT in DATE,
99 X_SHOW_CAUSE_OUTCOME_TYPE in VARCHAR2,
100 X_APPEAL_EXPIRY_DT in DATE,
101 X_APPEAL_DT in DATE,
102 X_APPEAL_OUTCOME_DT in DATE,
103 X_APPEAL_OUTCOME_TYPE in VARCHAR2,
104 X_ENCMB_COURSE_GROUP_CD in VARCHAR2,
105 X_RESTRICTED_ENROLMENT_CP in NUMBER,
106 X_RESTRICTED_ATTENDANCE_TYPE in VARCHAR2,
107 X_COMMENTS in VARCHAR2,
108 X_SHOW_CAUSE_COMMENTS in VARCHAR2,
109 X_APPEAL_COMMENTS in VARCHAR2,
110 X_EXPIRY_DT in DATE,
111 X_PRO_PRA_SEQUENCE_NUMBER in NUMBER,
112 X_MODE in VARCHAR2 default 'R'
113 );
114 procedure ADD_ROW (
115 X_ROWID in out NOCOPY VARCHAR2,
116 X_PERSON_ID in NUMBER,
117 X_COURSE_CD in VARCHAR2,
118 X_SEQUENCE_NUMBER in NUMBER,
119 X_PRG_CAL_TYPE in VARCHAR2,
120 X_PRG_CI_SEQUENCE_NUMBER in NUMBER,
121 X_RULE_CHECK_DT in DATE,
122 X_PROGRESSION_RULE_CAT in VARCHAR2,
123 X_PRA_SEQUENCE_NUMBER in NUMBER,
124 X_PRO_SEQUENCE_NUMBER in NUMBER,
125 X_PROGRESSION_OUTCOME_TYPE in VARCHAR2,
126 X_DURATION in NUMBER,
127 X_DURATION_TYPE in VARCHAR2,
128 X_DECISION_STATUS in VARCHAR2,
129 X_DECISION_DT in DATE,
130 X_DECISION_ORG_UNIT_CD in VARCHAR2,
131 X_DECISION_OU_START_DT in DATE,
132 X_APPLIED_DT in DATE,
133 X_SHOW_CAUSE_EXPIRY_DT in DATE,
134 X_SHOW_CAUSE_DT in DATE,
135 X_SHOW_CAUSE_OUTCOME_DT in DATE,
136 X_SHOW_CAUSE_OUTCOME_TYPE in VARCHAR2,
137 X_APPEAL_EXPIRY_DT in DATE,
138 X_APPEAL_DT in DATE,
139 X_APPEAL_OUTCOME_DT in DATE,
140 X_APPEAL_OUTCOME_TYPE in VARCHAR2,
141 X_ENCMB_COURSE_GROUP_CD in VARCHAR2,
142 X_RESTRICTED_ENROLMENT_CP in NUMBER,
143 X_RESTRICTED_ATTENDANCE_TYPE in VARCHAR2,
144 X_COMMENTS in VARCHAR2,
145 X_SHOW_CAUSE_COMMENTS in VARCHAR2,
146 X_APPEAL_COMMENTS in VARCHAR2,
147 X_EXPIRY_DT in DATE,
148 X_PRO_PRA_SEQUENCE_NUMBER in NUMBER,
149 X_MODE in VARCHAR2 default 'R',
150 X_ORG_ID in NUMBER
151 );
152 procedure DELETE_ROW (
153 X_ROWID in VARCHAR2,
154 x_mode IN VARCHAR2 DEFAULT 'R'
155 );
156
157
158 FUNCTION Get_PK_For_Validation (
159 x_person_id IN NUMBER,
160 x_course_cd IN VARCHAR2,
161 x_sequence_number IN NUMBER
162 ) RETURN BOOLEAN;
163
164 PROCEDURE GET_FK_IGS_EN_ATD_TYPE (
165 x_attendance_type IN varchar2
166 );
167
168 PROCEDURE GET_FK_IGS_OR_UNIT (
169 x_org_unit_cd IN VARCHAR2,
170 x_start_dt IN DATE
171 );
172
173 PROCEDURE GET_FK_IGS_PR_OU_TYPE (
174 x_progression_outcome_type IN VARCHAR2
175 );
176
177 PROCEDURE GET_FK_IGS_PR_RU_OU (
178 x_progression_rule_cat IN VARCHAR2,
179 x_pra_sequence_number IN NUMBER,
180 x_sequence_number IN NUMBER
181 );
182
183 PROCEDURE GET_FK_IGS_PR_SDT_PR_RU_CK (
184 x_person_id IN NUMBER,
185 x_course_cd IN VARCHAR2,
186 x_prg_cal_type IN VARCHAR2,
187 x_prg_ci_sequence_number IN NUMBER,
188 x_progression_rule_cat IN VARCHAR2,
189 x_pra_sequence_number IN NUMBER,
190 x_rule_check_dt IN DATE
191 );
192 PROCEDURE Before_DML (
193 p_action IN VARCHAR2,
194 x_rowid IN VARCHAR2 DEFAULT NULL,
195 x_prg_cal_type IN VARCHAR2 DEFAULT NULL,
196 x_prg_ci_sequence_number IN NUMBER DEFAULT NULL,
197 x_rule_check_dt IN DATE DEFAULT NULL,
198 x_progression_rule_cat IN VARCHAR2 DEFAULT NULL,
199 x_pra_sequence_number IN NUMBER DEFAULT NULL,
200 x_pro_sequence_number IN NUMBER DEFAULT NULL,
201 x_progression_outcome_type IN VARCHAR2 DEFAULT NULL,
202 x_duration IN NUMBER DEFAULT NULL,
203 x_duration_type IN VARCHAR2 DEFAULT NULL,
204 x_decision_status IN VARCHAR2 DEFAULT NULL,
205 x_decision_dt IN DATE DEFAULT NULL,
206 x_decision_org_unit_cd IN VARCHAR2 DEFAULT NULL,
207 x_decision_ou_start_dt IN DATE DEFAULT NULL,
208 x_applied_dt IN DATE DEFAULT NULL,
209 x_show_cause_expiry_dt IN DATE DEFAULT NULL,
210 x_show_cause_dt IN DATE DEFAULT NULL,
211 x_show_cause_outcome_dt IN DATE DEFAULT NULL,
212 x_show_cause_outcome_type IN VARCHAR2 DEFAULT NULL,
213 x_appeal_expiry_dt IN DATE DEFAULT NULL,
214 x_appeal_dt IN DATE DEFAULT NULL,
215 x_appeal_outcome_dt IN DATE DEFAULT NULL,
216 x_appeal_outcome_type IN VARCHAR2 DEFAULT NULL,
217 x_encmb_course_group_cd IN VARCHAR2 DEFAULT NULL,
218 x_restricted_enrolment_cp IN NUMBER DEFAULT NULL,
219 x_restricted_attendance_type IN VARCHAR2 DEFAULT NULL,
220 x_comments IN VARCHAR2 DEFAULT NULL,
221 x_show_cause_comments IN VARCHAR2 DEFAULT NULL,
222 x_appeal_comments IN VARCHAR2 DEFAULT NULL,
223 x_person_id IN NUMBER DEFAULT NULL,
224 x_course_cd IN VARCHAR2 DEFAULT NULL,
225 x_sequence_number IN NUMBER DEFAULT NULL,
226 x_expiry_dt IN DATE DEFAULT NULL,
227 x_pro_pra_sequence_number IN NUMBER DEFAULT NULL,
228 x_creation_date IN DATE DEFAULT NULL,
229 x_created_by IN NUMBER DEFAULT NULL,
230 x_last_update_date IN DATE DEFAULT NULL,
231 x_last_updated_by IN NUMBER DEFAULT NULL,
232 x_last_update_login IN NUMBER DEFAULT NULL,
233 x_org_id IN NUMBER DEFAULT NULL
234 );
235 PROCEDURE Check_Constraints(
236 Column_Name IN VARCHAR2 DEFAULT NULL,
237 Column_Value IN VARCHAR2 DEFAULT NULL
238 );
239
240
241
242 end IGS_PR_STDNT_PR_OU_PKG;