[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_ELGB_OVR_UOO_PKG
Source
1 PACKAGE BODY igs_en_elgb_ovr_uoo_pkg AS
2 /* $Header: IGSEI69B.pls 120.10 2006/05/25 10:29:50 amuthu ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_elgb_ovr_uoo%ROWTYPE;
6 new_references igs_en_elgb_ovr_uoo%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_elgb_ovr_step_uoo_id IN NUMBER,
12 x_elgb_ovr_step_id IN NUMBER,
13 x_unit_cd IN VARCHAR2,
14 x_version_number IN NUMBER,
15 x_uoo_id IN NUMBER,
16 x_step_override_limit IN NUMBER,
17 x_creation_date IN DATE,
18 x_created_by IN NUMBER,
19 x_last_update_date IN DATE,
20 x_last_updated_by IN NUMBER,
21 x_last_update_login IN NUMBER
22 ) AS
23 /*
24 || Created By : [email protected]
25 || Created On : 15-MAY-2003
26 || Purpose : Initialises the Old and New references for the columns of the table.
27 || Known limitations, enhancements or remarks :
28 || Change History :
29 || Who When What
30 || (reverse chronological order - newest change first)
31 */
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM igs_en_elgb_ovr_uoo
36 WHERE rowid = x_rowid;
37
38 BEGIN
39
40 l_rowid := x_rowid;
41
42 -- Code for setting the Old and New Reference Values.
43 -- Populate Old Values.
44 OPEN cur_old_ref_values;
45 FETCH cur_old_ref_values INTO old_references;
46 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
47 CLOSE cur_old_ref_values;
48 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
49 igs_ge_msg_stack.add;
50 app_exception.raise_exception;
51 RETURN;
52 END IF;
53 CLOSE cur_old_ref_values;
54
55 -- Populate New Values.
56 new_references.elgb_ovr_step_uoo_id := x_elgb_ovr_step_uoo_id;
57 new_references.elgb_ovr_step_id := x_elgb_ovr_step_id;
58 new_references.unit_cd := x_unit_cd;
59 new_references.version_number := x_version_number;
60 new_references.uoo_id := x_uoo_id;
61 new_references.step_override_limit := x_step_override_limit;
62
63 IF (p_action = 'UPDATE') THEN
64 new_references.creation_date := old_references.creation_date;
65 new_references.created_by := old_references.created_by;
66 ELSE
67 new_references.creation_date := x_creation_date;
68 new_references.created_by := x_created_by;
69 END IF;
70
71 new_references.last_update_date := x_last_update_date;
72 new_references.last_updated_by := x_last_updated_by;
73 new_references.last_update_login := x_last_update_login;
74
75 END set_column_values;
76 --
77 --Added As part of ENCR013 DLD
78 PROCEDURE enrp_val_appr_cr_pt AS
79 /******************************************************************
80 Created By : knaraset
81 Date Created By : 12-Nov-2001
82 Purpose : This procedure updates Enrolled_Cp and achieveable_Cp in SUA record
83 when Approved Credit Points is created.
84 Known limitations,
85 enhancements,
86 remarks :
87 Change History
88 Who When What
89 jbegum 25-Jun-2003 BUG#2930935 - Modified cursor cur_unit_ver.
90 Added the parameter p_uoo_id in the call to IGS_EN_PRC_LOAD.ENRP_CLC_SUA_LOAD
91 rvivekan 18-Jun-2003 added reenroll step as per Reenrollmen and repeat enhacement.Modified cur_note_flag
92 cursor to accept step_group_type as a prameter too.
93 smanglm 22-Jan-2003 call igs_en_gen_017.enrp_get_enr_method to decide enrollment method type
94 svenkata 8-Jan-2003 Modified the message handling for eval_min_cp.
95 Nishikant 01NOV2002 SEVIS Build. Enh Bug#2641905. notification flag was
96 being fetched from cursor, now modified to get it by
97 calling the function igs_ss_enr_details.get_notification.
98 Nishikant 18-OCT-2002 The call to the function Igs_En_Elgbl_Program.eval_min_cp got modified since the signatue
99 got modified. Enrl Elgbl and Validation Build. Bug#2616692.
100 mesriniv 12-sep-2002 Added a new parameter waitlist_manual_ind in update row of IGS_EN_SU_ATTEMPT
101 for Bug 2554109 MINI Waitlist Build for Jan 03 Release
102 ayedubat 11-APR-2002 Changed the cursor,cur_note_flag to add an extra 'OR'
103 condition(eru.s_student_comm_type = 'ALL') for s_student_comm_type as part of the bug fix: 2315245
104 Nishikant 10-Oct-2001 Added the column session_id in all Tbh calls of IGS_EN_SU_ATTEMPT_PKG
105 as a part of the bug 2172380.
106 myoganat 16-JUN-2003 Bug# 2855870 Modified ENRP_VAL_APPR_CR_PT procedure by removing the IF clause
107 before the call to EVAL_MIN_CP and EVAL_MAX_CP. Profile IGS_EN_INCL_AUDIT_CP
108 was made obsolete. In the call to igs_en_val_sua.enrp_val_sua_ovrd_cp, the value of
109 no_assessment_ind was hardcoded to 'N'.
110 rvangala 07-OCT-03 Passing core_indicator_code to IGS_EN_SUA-API.UPDATE_UNIT_ATTEMPT added as part of Prevent Dropping Core
111 Units. Enh Bug# 3052432
112 stutta 21-NOV-2003 Replaced a program attempt cursor with a terms api function to return program version.
113 vkarthik 22-Jul-2004 Added three dummy variables l_audit_cp, l_billing_cp, l_enrolled_cp for all the calls to
114 igs_en_prc_load.enrp_clc_sua_load towards EN308 Billable credit points build Enh#3782329
115
116 ******************************************************************/
117
118 CURSOR cur_unit_ver(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE)IS
119 SELECT NVL(cps.enrolled_credit_points,uv.enrolled_credit_points)
120 FROM igs_ps_unit_ver uv ,igs_ps_unit_ofr_opt uoo,igs_ps_usec_cps cps
121 WHERE uoo.uoo_id = cps.uoo_id(+) AND
122 uoo.unit_cd = uv.unit_cd AND
123 uoo.version_number = uv.version_number AND
124 uoo.uoo_id = cp_uoo_id;
125
126 CURSOR cur_step_cal IS
127 SELECT person_id,cal_type,ci_sequence_number
128 FROM igs_en_elgb_ovr eo ,igs_en_elgb_ovr_step eos
129 WHERE eos.elgb_override_id = eo.elgb_override_id AND
130 eos.elgb_ovr_step_id = new_references.elgb_ovr_step_id;
131
132 l_cur_step_cal_rec cur_step_cal%ROWTYPE;
133
134 l_prg_ver igs_en_stdnt_ps_att.version_number%TYPE;
135 -- Cursor to check whether the passed calendar is Teaching or not
136 CURSOR cur_cal_cat(cp_cal_type VARCHAR2) IS
137 SELECT 'x'
138 FROM igs_ca_inst ci,
139 igs_ca_type ct
140 WHERE ci.cal_type = cp_cal_type AND
141 ci.cal_type = ct.cal_type AND
142 ct.s_cal_cat = 'TEACHING';
143 l_cal_cat_rec cur_cal_cat%ROWTYPE;
144 -- Cursor to fetch most recent active Term Calendar corresponding to the given teaching calendar
145 CURSOR cur_teach_term(cp_cal_type VARCHAR2,cp_sequence_number NUMBER) IS
146 SELECT load_cal_type,load_ci_sequence_number
147 FROM igs_ca_teach_to_load_v
148 WHERE teach_cal_type = cp_cal_type AND
149 teach_ci_sequence_number = cp_sequence_number AND
150 load_end_dt >= TRUNC(SYSDATE)
151 ORDER BY load_start_dt ;
152 l_teach_term_rec cur_teach_term%ROWTYPE;
153
154 TYPE cur_sua_def IS REF CURSOR;
155
156 -- bmerugu added for 4433428
157 -- Cursor to get the coo_id of the student.
158 CURSOR cur_coo_id (cp_person_id igs_en_stdnt_ps_att.person_id%TYPE, cp_program_cd igs_en_stdnt_ps_att.course_cd%TYPE) IS
159 SELECT coo_id coo_id
160 FROM igs_en_stdnt_ps_att
161 WHERE person_id = cp_person_id
162 AND course_cd = cp_program_cd ;
163
164 l_attendance_type_reach BOOLEAN := TRUE;
165 l_cur_coo_id cur_coo_id%ROWTYPE;
166 l_attendance_types VARCHAR2(100); -- As returned from the function igs_en_val_sca.enrp_val_coo_att
167
168 l_deny_warn_min_cp VARCHAR2(10) DEFAULT NULL;
169 l_deny_warn_max_cp VARCHAR2(10) DEFAULT NULL;
170 l_deny_warn_cross_loc VARCHAR2(10) DEFAULT NULL;
171 l_deny_warn_cross_fac VARCHAR2(10) DEFAULT NULL;
172 l_deny_warn_cross_mod VARCHAR2(10) DEFAULT NULL;
173 l_deny_warn_att_type VARCHAR2(100) DEFAULT NULL;
174 l_deny_warn_att_type1 VARCHAR2(100) DEFAULT NULL;
175 l_deny_warn_reenroll VARCHAR2(100) DEFAULT NULL;
176
177 -- bmerugu added for 4433428 planning sheet variables
178 TYPE cur_plan_def IS REF CURSOR;
179 cur_plan_def_var cur_sua_def; -- REF cursor variable
180 cursor c_dummy is SELECT plan.rowid, plan.*
181 FROM igs_en_plan_units plan
182 WHERE plan.person_id = plan.person_id;
183 cur_plan_def_var_rec c_dummy%ROWTYPE;
184 l_plan_def_query VARCHAR2(4000);
185
186 cur_sua_def_var cur_sua_def; -- REF cursor variable
187
188 CURSOR c_dummy_cur (cp_person_id igs_en_su_attempt_all.person_id%TYPE
189 , cp_course_cd igs_en_su_attempt_all.course_cd%TYPE
190 , cp_uoo_id igs_en_su_attempt_all.uoo_id%TYPE)IS
191 SELECT sua.rowid row_id, sua.*
192 FROM igs_en_su_attempt_all sua
193 WHERE person_id = cp_person_id
194 and course_cd = cp_course_cd
195 and uoo_id = cp_uoo_id;
196 cur_sua_def_var_rec c_dummy_cur%ROWTYPE;
197 l_upd_credit_cp NUMBER := NULL;
198 l_min_upd_credit_cp NUMBER := NULL;
199 l_unit_cp igs_en_su_attempt.override_enrolled_cp%TYPE;
200 l_message VARCHAR2(2000);
201 l_sua_def_query VARCHAR2(4000);
202 l_return_val BOOLEAN := FALSE;
203 l_ret_status VARCHAR2(10);
204 l_enr_meth_type igs_en_method_type.ENR_METHOD_TYPE%type;
205 l_enr_comm igs_en_cpd_ext.s_student_comm_type%type;
206 l_enr_cat igs_en_cpd_ext.enrolment_cat%type;
207 l_enr_cal_type igs_ca_inst.cal_type%type;
208 l_enr_ci_seq igs_ca_inst.sequence_number%type;
209 cst_mincp varchar2(10) := 'FMAX_CRDT';
210 cst_maxcp varchar2(10) := 'FMIN_CRDT';
211 cst_fatd varchar2(10) := 'FATD_TYPE';
212 cst_crossfac varchar2(10) := 'CROSS_FAC';
213 cst_crossmod varchar2(10) := 'CROSS_MOD';
214 cst_crossloc varchar2(10) := 'CROSS_LOC';
215 cst_reenroll varchar2(10) := 'REENROLL';
216 cst_program varchar2(10) :='PROGRAM';
217 cst_unit varchar2(10) :='UNIT';
218 l_acad_cal_type igs_ca_inst.cal_type%type;
219 l_acad_ci_sequence_number igs_ca_inst.sequence_number%type;
220 l_acad_start_dt igs_ca_inst.start_dt%type;
221 l_acad_end_dt igs_ca_inst.end_dt%type;
222 l_alternate_code igs_ca_inst.alternate_code%type;
223 l_acad_message varchar2(100);
224 l_load_cal_type igs_ca_inst.cal_type%TYPE;
225 l_load_sequence_number igs_ca_inst.sequence_number%TYPE;
226 l_unit_incurred_cp NUMBER;
227 l_dummy NUMBER;
228 l_dummy_c VARCHAR2(2000);
229 l_over_incurred_cp NUMBER;
230 l_lim_incurred_cp NUMBER;
231 -- Below local variable added as part of Enrl Elgbl and Validation Build. Bug#2616692
232 l_min_credit_point igs_en_config_enr_cp.min_cp_per_term%TYPE := NULL;
233 l_person_type igs_pe_person_types.person_type_code%TYPE;--added by nishikant
234 --dummy variable to pick up audit, billing, enrolled credit points
235 --due to signature change by EN308 Billing credit hours #3782329
236 l_audit_cp IGS_PS_USEC_CPS.billing_credit_points%TYPE;
237 l_billing_cp IGS_PS_USEC_CPS.billing_hrs%TYPE;
238 l_enrolled_cp IGS_PS_UNIT_VER.enrolled_credit_points%TYPE;
239
240 BEGIN
241
242 --
243 --
244 OPEN cur_step_cal;
245 FETCH cur_step_cal INTO l_cur_step_cal_rec;
246 CLOSE cur_step_cal;
247
248 -- Override record defined at Unit Section level
249 IF new_references.uoo_id <>-1 THEN
250 -- fetch all Unit Atempts where sua.uoo_id = new_references.uoo_id
251 l_sua_def_query := 'SELECT sua.rowid row_id, sua.*
252 FROM igs_en_su_attempt_all sua
253 WHERE unit_attempt_status IN (''ENROLLED'',''WAITLISTED'') AND uoo_id = :1 AND person_id = :2 AND no_assessment_ind<>''Y''';
254
255 OPEN cur_sua_def_var FOR l_sua_def_query USING new_references.uoo_id, l_cur_step_cal_rec.person_id;
256
257 -- Override record defined at Unit level
258 ELSIF new_references.unit_cd IS NOT NULL THEN
259 -- fetch all Unit Atempts where sua.unit_cd = new_references.unit_cd
260 l_sua_def_query := 'SELECT sua.rowid row_id, sua.*
261 FROM igs_en_su_attempt_all sua
262 WHERE sua.unit_attempt_status IN (''ENROLLED'',''WAITLISTED'') AND sua.unit_cd = :1 AND sua.version_number = :2 AND person_id = :3 AND
263 ((sua.cal_type = :4 AND
264 sua.ci_sequence_number = :5 ) OR
265 ((sua.cal_type,sua.ci_sequence_number) IN
266 (SELECT teach_cal_type,teach_ci_sequence_number
267 FROM igs_ca_load_to_teach_v
268 WHERE load_cal_type = :6 AND load_ci_sequence_number = :7 )))
269 AND no_assessment_ind<>''Y''';
270
271 OPEN cur_sua_def_var FOR l_sua_def_query USING new_references.unit_cd, new_references.version_number, l_cur_step_cal_rec.person_id,
272 l_cur_step_cal_rec.cal_type, l_cur_step_cal_rec.ci_sequence_number,
273 l_cur_step_cal_rec.cal_type, l_cur_step_cal_rec.ci_sequence_number;
274
275 -- Override record defined at Teaching or Term calendar level
276 ELSE
277
278 -- fetch all Unit Atempts where sua teach cal type is equal to or sub ordinate to the Cal Type
279 -- where Override record is defined.
280 l_sua_def_query := 'SELECT sua.rowid row_id, sua.*
281 FROM igs_en_su_attempt_all sua
282 WHERE sua.unit_attempt_status IN (''ENROLLED'',''WAITLISTED'') AND sua.person_id = :1 AND
283 ((sua.cal_type = :2 AND
284 sua.ci_sequence_number = :3 ) OR
285 ((sua.cal_type,sua.ci_sequence_number) IN
286 (SELECT teach_cal_type,teach_ci_sequence_number
287 FROM igs_ca_load_to_teach_v
288 WHERE load_cal_type = :4 AND load_ci_sequence_number = :5 ))) AND no_assessment_ind<>''Y''';
289
290 OPEN cur_sua_def_var FOR l_sua_def_query USING l_cur_step_cal_rec.person_id, l_cur_step_cal_rec.cal_type, l_cur_step_cal_rec.ci_sequence_number,
291 l_cur_step_cal_rec.cal_type, l_cur_step_cal_rec.ci_sequence_number;
292
293 END IF;
294
295
296 <<loop_sua_rec >> -- loop lable
297 LOOP
298 FETCH cur_sua_def_var INTO cur_sua_def_var_rec;
299 EXIT WHEN cur_sua_def_var%NOTFOUND;
300
301 -- Derive the Unit's CP
302 OPEN cur_unit_ver(cur_sua_def_var_rec.uoo_id);
303 FETCH cur_unit_ver INTO l_unit_cp;
304 CLOSE cur_unit_ver;
305
306 -- As the step can be overriden at Load as well as Teach calendars.
307 OPEN cur_cal_cat(cur_sua_def_var_rec.cal_type);
308 FETCH cur_cal_cat INTO l_cal_cat_rec;
309 IF cur_cal_cat%FOUND THEN
310 -- If defined at Teach level then deriving the Load Calendar as below.
311 OPEN cur_teach_term(cur_sua_def_var_rec.cal_type,cur_sua_def_var_rec.ci_sequence_number);
312 fetch cur_teach_term INTO l_teach_term_rec;
313 CLOSE cur_teach_term;
314 l_load_cal_type := l_teach_term_rec.load_cal_type;
315 l_load_sequence_number := l_teach_term_rec.load_ci_sequence_number;
316 ELSE
317 -- Defined at Load Calendar level.
318 l_load_cal_type := cur_sua_def_var_rec.cal_type;
319 l_load_sequence_number := cur_sua_def_var_rec.ci_sequence_number;
320 END IF;
321 CLOSE cur_cal_cat;
322
323 -- Calculate the value of the Parameter to be passed to Min_Cp/Max_Cp functions
324 IF new_references.step_override_limit IS NULL THEN
325 IF cur_sua_def_var_rec.override_enrolled_cp IS NOT NULL THEN
326 -- When Approved Credit Points made NULL from NOT NULL
327 -- calculate CP incurred in the given Load calendar for Enrolled credit points.
328 -- These changes are done as part of bug 2401891.
329 l_unit_incurred_cp := Igs_En_Prc_Load.enrp_clc_sua_load(
330 p_unit_cd => cur_sua_def_var_rec.unit_cd,
331 p_version_number => cur_sua_def_var_rec.version_number,
332 p_cal_type => cur_sua_def_var_rec.cal_type,
333 p_ci_sequence_number => cur_sua_def_var_rec.ci_sequence_number,
334 p_load_cal_type => l_load_cal_type,
335 p_load_ci_sequence_number => l_load_sequence_number,
336 p_override_enrolled_cp => l_unit_cp,
337 p_override_eftsu => NULL,
338 p_return_eftsu => l_dummy,
339 p_uoo_id => cur_sua_def_var_rec.uoo_id,
340 -- anilk, Audit special fee build
341 p_include_as_audit => 'N',
342 p_audit_cp => l_audit_cp,
343 p_billing_cp => l_billing_cp,
344 p_enrolled_cp => l_enrolled_cp);
345
346 -- calculate CP incurred in the given Load calendar for Override Enrolled credit points.
347 l_over_incurred_cp := Igs_En_Prc_Load.enrp_clc_sua_load(
348 p_unit_cd => cur_sua_def_var_rec.unit_cd,
349 p_version_number => cur_sua_def_var_rec.version_number,
350 p_cal_type => cur_sua_def_var_rec.cal_type,
351 p_ci_sequence_number => cur_sua_def_var_rec.ci_sequence_number,
352 p_load_cal_type => l_load_cal_type,
353 p_load_ci_sequence_number => l_load_sequence_number,
354 p_override_enrolled_cp => cur_sua_def_var_rec.override_enrolled_cp,
355 p_override_eftsu => NULL,
356 p_return_eftsu => l_dummy,
357 p_uoo_id => cur_sua_def_var_rec.uoo_id,
358 -- anilk, Audit special fee build
359 p_include_as_audit => 'N',
360 p_audit_cp => l_audit_cp,
361 p_billing_cp => l_billing_cp,
362 p_enrolled_cp => l_enrolled_cp);
363
364 -- l_upd_credit_cp := l_unit_cp - cur_sua_def_var_rec.override_enrolled_cp;
365 l_upd_credit_cp := l_unit_incurred_cp - l_over_incurred_cp;
366 END IF;
367 ELSE
368 -- calculate CP incurred in the given Load calendar for Step Override Limit.
369 l_lim_incurred_cp := Igs_En_Prc_Load.enrp_clc_sua_load(
370 p_unit_cd => cur_sua_def_var_rec.unit_cd,
371 p_version_number => cur_sua_def_var_rec.version_number,
372 p_cal_type => cur_sua_def_var_rec.cal_type,
373 p_ci_sequence_number => cur_sua_def_var_rec.ci_sequence_number,
374 p_load_cal_type => l_load_cal_type,
375 p_load_ci_sequence_number => l_load_sequence_number,
376 p_override_enrolled_cp => new_references.step_override_limit,
377 p_override_eftsu => NULL,
378 p_return_eftsu => l_dummy,
379 p_uoo_id => cur_sua_def_var_rec.uoo_id,
380 -- anilk, Audit special fee build
381 p_include_as_audit => 'N',
382 p_audit_cp => l_audit_cp,
383 p_billing_cp => l_billing_cp,
384 p_enrolled_cp => l_enrolled_cp);
385
386 -- calculate CP incurred in the given Load calendar for Override/Enrolled credit points.
387 l_over_incurred_cp := Igs_En_Prc_Load.enrp_clc_sua_load(
388 p_unit_cd => cur_sua_def_var_rec.unit_cd,
389 p_version_number => cur_sua_def_var_rec.version_number,
390 p_cal_type => cur_sua_def_var_rec.cal_type,
391 p_ci_sequence_number => cur_sua_def_var_rec.ci_sequence_number,
392 p_load_cal_type => l_load_cal_type,
393 p_load_ci_sequence_number => l_load_sequence_number,
394 p_override_enrolled_cp => NVL(cur_sua_def_var_rec.override_enrolled_cp,l_unit_cp),
395 p_override_eftsu => NULL,
396 p_return_eftsu => l_dummy,
397 p_uoo_id => cur_sua_def_var_rec.uoo_id,
398 -- anilk, Audit special fee build
399 p_include_as_audit => 'N',
400 p_audit_cp => l_audit_cp,
401 p_billing_cp => l_billing_cp,
402 p_enrolled_cp => l_enrolled_cp);
403
404 --l_upd_credit_cp := new_references.step_override_limit - NVL(cur_sua_def_var_rec.override_enrolled_cp,l_unit_cp);
405 l_upd_credit_cp := l_lim_incurred_cp - l_over_incurred_cp;
406 END IF;
407 --
408 -- Get the version of the program in context
409 l_prg_ver := igs_en_spa_terms_api.get_spat_program_version( p_person_id => cur_sua_def_var_rec.person_id,
410 p_program_cd => cur_sua_def_var_rec.course_cd,
411 p_term_cal_type => l_load_cal_type,
412 p_term_sequence_number => l_load_sequence_number);
413 -- added below logic to get the Academic Calendar which is used by method enrp_get_enr_cat
414 --
415 -- get the academic calendar of the given Load Calendar
416 --
417 l_alternate_code := Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd(
418 p_cal_type => l_cur_step_cal_rec.cal_type,
419 p_ci_sequence_number => l_cur_step_cal_rec.ci_sequence_number,
420 p_acad_cal_type => l_acad_cal_type,
421 p_acad_ci_sequence_number => l_acad_ci_sequence_number,
422 p_acad_ci_start_dt => l_acad_start_dt,
423 p_acad_ci_end_dt => l_acad_end_dt,
424 p_message_name => l_acad_message );
425
426 IF l_acad_message IS NOT NULL THEN
427 Fnd_Message.Set_Name ('IGS',l_acad_message );
428 IGS_GE_MSG_STACK.ADD;
429 App_Exception.Raise_Exception;
430 END IF;
431
432 --
433 -- Get Enrollment Category, Commencement Type and Enrollemnt Method
434 --
435 l_dummy_c := NULL;
436 l_enr_cat := igs_en_gen_003.enrp_get_enr_cat(
437 cur_sua_def_var_rec.person_id,
438 cur_sua_def_var_rec.course_cd,
439 l_acad_cal_type,
440 l_acad_ci_sequence_number,
441 NULL,
442 l_enr_cal_type,
443 l_enr_ci_seq,
444 l_enr_comm,
445 l_dummy_c);
446
447 IF l_enr_comm = 'BOTH' THEN
448 l_enr_comm :='ALL';
449 END IF;
450
451 -- call igs_en_gen_017.enrp_get_enr_method to decide enrollment method type
452 igs_en_gen_017.enrp_get_enr_method(
453 p_enr_method_type => l_enr_meth_type,
454 p_error_message => l_message,
455 p_ret_status => l_ret_status);
456 l_person_type := Igs_En_Gen_008.enrp_get_person_type(NULL);
457
458 l_message := NULL;
459 l_deny_warn_min_cp := igs_ss_enr_details.get_notification(
460 p_person_type => l_person_type,
461 p_enrollment_category => l_enr_cat,
462 p_comm_type => l_enr_comm,
463 p_enr_method_type => l_enr_meth_type,
464 p_step_group_type => cst_program,
465 p_step_type => cst_mincp,
466 p_person_id => cur_sua_def_var_rec.person_id,
467 p_message => l_message);
468 IF l_message IS NOT NULL THEN
469 fnd_message.set_name ('IGS', l_message);
470 igs_ge_msg_stack.add;
471 app_exception.raise_exception;
472 END IF;
473 l_min_upd_credit_cp := l_upd_credit_cp;
474 -- The Four parameters p_enrollment_category, p_comm_type, p_method_type, p_min_credit_point
475 -- added in the below function call, as part of Enrl Elgbl and Validation Build. Bug#2616692
476 IF l_deny_warn_min_cp ='DENY' AND NOT igs_en_elgbl_program.eval_min_cp(p_person_id => cur_sua_def_var_rec.person_id,
477 p_load_calendar_type => l_cur_step_cal_rec.cal_type,
478 p_load_cal_sequence_number => l_cur_step_cal_rec.ci_sequence_number,
479 p_uoo_id => cur_sua_def_var_rec.uoo_id,
480 p_program_cd => cur_sua_def_var_rec.course_cd,
481 p_program_version => l_prg_ver,
482 p_message => l_message,
483 p_deny_warn => l_deny_warn_min_cp,
484 p_credit_points => l_min_upd_credit_cp ,
485 p_enrollment_category => l_enr_cat,
486 p_comm_type => l_enr_comm,
487 p_method_type => l_enr_meth_type,
488 p_min_credit_point => l_min_credit_point,
489 p_calling_obj => 'JOB'
490 ) AND l_message = 'IGS_SS_DENY_MIN_CP_REACHED' THEN
491 Fnd_Message.Set_Name ('IGS',l_message );
492 IGS_GE_MSG_STACK.ADD;
493 App_Exception.Raise_Exception;
494 END IF;
495
496 l_message := NULL;
497 l_deny_warn_max_cp := igs_ss_enr_details.get_notification(
498 p_person_type => l_person_type,
499 p_enrollment_category => l_enr_cat,
500 p_comm_type => l_enr_comm,
501 p_enr_method_type => l_enr_meth_type,
502 p_step_group_type => cst_program,
503 p_step_type => cst_maxcp,
504 p_person_id => cur_sua_def_var_rec.person_id,
505 p_message => l_message);
506 IF l_message IS NOT NULL THEN
507 fnd_message.set_name ('IGS', l_message);
508 igs_ge_msg_stack.add;
509 app_exception.raise_exception;
510 END IF;
511 IF l_deny_warn_max_cp ='DENY' AND NOT igs_en_elgbl_program.eval_max_cp (
512 p_person_id => cur_sua_def_var_rec.person_id,
513 p_load_calendar_type => l_cur_step_cal_rec.cal_type,
514 p_load_cal_sequence_number => l_cur_step_cal_rec.ci_sequence_number,
515 p_uoo_id => cur_sua_def_var_rec.uoo_id,
516 p_program_cd => cur_sua_def_var_rec.course_cd,
517 p_program_version => l_prg_ver,
518 p_message => l_message,
519 p_deny_warn => l_deny_warn_max_cp,
520 p_upd_cp => l_upd_credit_cp,
521 p_calling_obj => 'JOB') THEN
522 Fnd_Message.Set_Name ('IGS',l_message );
523 IGS_GE_MSG_STACK.ADD;
524 App_Exception.Raise_Exception;
525 END IF;
526 --------------------------------------------------------------------------------------------------------------------------------------------
527 l_message := NULL;
528 l_deny_warn_reenroll := igs_ss_enr_details.get_notification(
529 p_person_type => l_person_type,
530 p_enrollment_category => l_enr_cat,
531 p_comm_type => l_enr_comm,
532 p_enr_method_type => l_enr_meth_type,
533 p_step_group_type => cst_unit,
534 p_step_type => cst_reenroll,
535 p_person_id => cur_sua_def_var_rec.person_id,
536 p_message => l_message);
537 IF l_message IS NOT NULL THEN
538 fnd_message.set_name ('IGS', l_message);
539 igs_ge_msg_stack.add;
540 app_exception.raise_exception;
541 END IF;
542 IF l_deny_warn_reenroll ='DENY' AND NOT igs_en_elgbl_unit.eval_unit_reenroll (
543 p_person_id => cur_sua_def_var_rec.person_id,
544 p_load_cal_type => l_cur_step_cal_rec.cal_type,
545 p_load_cal_seq_number => l_cur_step_cal_rec.ci_sequence_number,
546 p_uoo_id => cur_sua_def_var_rec.uoo_id,
547 p_program_cd => cur_sua_def_var_rec.course_cd,
548 p_program_version => l_prg_ver,
549 p_message => l_message,
550 p_deny_warn => l_deny_warn_reenroll,
551 p_upd_cp => new_references.step_override_limit - NVL(cur_sua_def_var_rec.override_enrolled_cp,l_unit_cp),
552 p_val_level => 'CREDIT_POINT',
553 p_calling_obj => 'JOB') THEN
554 Fnd_Message.Set_Name ('IGS',l_message );
555 IGS_GE_MSG_STACK.ADD;
556 App_Exception.Raise_Exception;
557 END IF;
558
559 -- smaddali added the check that message <> IGS_EN_OVERRIDE_EFTSU_VALUES for EN317 build
560 -- because credit approval can be provided even for a unit which doesnt allow overriding of cp
561 IF NOT igs_en_val_sua.enrp_val_sua_ovrd_cp(
562 p_unit_cd => cur_sua_def_var_rec.unit_cd,
563 p_version_number => cur_sua_def_var_rec.version_number ,
564 p_override_enrolled_cp => new_references.step_override_limit ,
565 p_override_achievable_cp => NULL ,
566 p_override_eftsu => NULL ,
567 p_message_name => l_message,
568 p_uoo_id => cur_sua_def_var_rec.uoo_id,
569 p_no_assessment_ind => 'N' ) AND l_message <> 'IGS_EN_OVERRIDE_EFTSU_VALUES' THEN
570 Fnd_Message.Set_Name ('IGS',l_message );
571 IGS_GE_MSG_STACK.ADD;
572 App_Exception.Raise_Exception;
573 END IF;
574
575 -- bmerugu added for 4433428
576 l_message := NULL;
577 l_deny_warn_att_type := igs_ss_enr_details.get_notification(
578 p_person_type => l_person_type,
579 p_enrollment_category => l_enr_cat,
580 p_comm_type => l_enr_comm,
581 p_enr_method_type => l_enr_meth_type,
582 p_step_group_type => cst_program,
583 p_step_type => cst_fatd,
584 p_person_id => cur_sua_def_var_rec.person_id,
585 p_message => l_message
586 ) ;
587 IF l_message IS NOT NULL THEN
588 fnd_message.set_name ('IGS', l_message);
589 igs_ge_msg_stack.add;
590 app_exception.raise_exception;
591 END IF;
592
593 l_message := NULL;
594 l_deny_warn_cross_loc := igs_ss_enr_details.get_notification(
595 p_person_type => l_person_type,
596 p_enrollment_category => l_enr_cat,
597 p_comm_type => l_enr_comm,
598 p_enr_method_type => l_enr_meth_type,
599 p_step_group_type => cst_program,
600 p_step_type => cst_crossloc,
601 p_person_id => cur_sua_def_var_rec.person_id,
602 p_message => l_message
603 ) ;
604 IF l_message IS NOT NULL THEN
605 fnd_message.set_name ('IGS', l_message);
606 igs_ge_msg_stack.add;
607 app_exception.raise_exception;
608 END IF;
609
610 l_message := NULL;
611 l_deny_warn_cross_mod := igs_ss_enr_details.get_notification(
612 p_person_type => l_person_type,
613 p_enrollment_category => l_enr_cat,
614 p_comm_type => l_enr_comm,
615 p_enr_method_type => l_enr_meth_type,
616 p_step_group_type => cst_program,
617 p_step_type => cst_crossmod,
618 p_person_id => cur_sua_def_var_rec.person_id,
619 p_message => l_message
620 ) ;
621 IF l_message IS NOT NULL THEN
622 fnd_message.set_name ('IGS', l_message);
623 igs_ge_msg_stack.add;
624 app_exception.raise_exception;
625 END IF;
626
627 l_message := NULL;
628 l_deny_warn_cross_fac := igs_ss_enr_details.get_notification(
629 p_person_type => l_person_type,
630 p_enrollment_category => l_enr_cat,
631 p_comm_type => l_enr_comm,
632 p_enr_method_type => l_enr_meth_type,
633 p_step_group_type => cst_program,
634 p_step_type => cst_crossfac,
635 p_person_id => cur_sua_def_var_rec.person_id,
636 p_message => l_message
637 ) ;
638 IF l_message IS NOT NULL THEN
639 fnd_message.set_name ('IGS', l_message);
640 igs_ge_msg_stack.add;
641 app_exception.raise_exception;
642 END IF;
643
644 IF l_deny_warn_att_type ='DENY' THEN
645 -- bmerugu added for 4433428
646 OPEN cur_coo_id(cur_sua_def_var_rec.person_id, cur_sua_def_var_rec.course_cd);
647 FETCH cur_coo_id INTO l_cur_coo_id;
648 CLOSE cur_coo_id;
649 l_message := NULL;
650
651 --Modified as a part of bug#5191592
652 -- Check if the Forced Attendance Type has already been reached for the Student before transferring .
653 l_attendance_type_reach := igs_en_val_sca.enrp_val_coo_att(p_person_id => cur_sua_def_var_rec.person_id,
654 p_coo_id => l_cur_coo_id.coo_id,
655 p_cal_type => l_acad_cal_type,
656 p_ci_sequence_number => l_acad_ci_sequence_number,
657 p_message_name => l_message,
658 p_attendance_types => l_attendance_types,
659 p_load_or_teach_cal_type => cur_sua_def_var_rec.cal_type,
660 p_load_or_teach_seq_number =>cur_sua_def_var_rec.ci_sequence_number);
661
662 -- Assign values to the parameter p_deny_warn_att based on if Attendance Type has not been already reached or not.
663 IF l_attendance_type_reach THEN
664 l_deny_warn_att_type1 := 'AttTypReached' ;
665 ELSE
666 l_deny_warn_att_type1 := 'AttTypNotReached' ;
667 END IF ;
668 END IF;
669
670 igs_en_sua_api.update_unit_attempt (
671 X_ROWID => cur_sua_def_var_rec.row_id ,
672 X_PERSON_ID => cur_sua_def_var_rec.person_id ,
673 X_COURSE_CD => cur_sua_def_var_rec.course_cd ,
674 X_UNIT_CD => cur_sua_def_var_rec.unit_cd ,
675 X_CAL_TYPE => cur_sua_def_var_rec.cal_type ,
676 X_CI_SEQUENCE_NUMBER => cur_sua_def_var_rec.ci_sequence_number ,
677 X_VERSION_NUMBER => cur_sua_def_var_rec.version_number ,
678 X_LOCATION_CD => cur_sua_def_var_rec.location_cd ,
679 X_UNIT_CLASS => cur_sua_def_var_rec.unit_class ,
680 X_CI_START_DT => cur_sua_def_var_rec.ci_start_dt ,
681 X_CI_END_DT => cur_sua_def_var_rec.ci_end_dt ,
682 X_UOO_ID => cur_sua_def_var_rec.uoo_id ,
683 X_ENROLLED_DT => cur_sua_def_var_rec.enrolled_dt ,
684 X_UNIT_ATTEMPT_STATUS => cur_sua_def_var_rec.unit_attempt_status ,
685 X_ADMINISTRATIVE_UNIT_STATUS => cur_sua_def_var_rec.administrative_unit_status ,
686 X_DISCONTINUED_DT => cur_sua_def_var_rec.discontinued_dt ,
687 X_RULE_WAIVED_DT => cur_sua_def_var_rec.rule_waived_dt ,
688 X_RULE_WAIVED_PERSON_ID => cur_sua_def_var_rec.rule_waived_person_id ,
689 X_NO_ASSESSMENT_IND => cur_sua_def_var_rec.no_assessment_ind ,
690 X_SUP_UNIT_CD => cur_sua_def_var_rec.sup_unit_cd ,
691 X_SUP_VERSION_NUMBER => cur_sua_def_var_rec.sup_version_number ,
692 X_EXAM_LOCATION_CD => cur_sua_def_var_rec.exam_location_cd ,
693 X_ALTERNATIVE_TITLE => cur_sua_def_var_rec.alternative_title ,
694 X_OVERRIDE_ENROLLED_CP => new_references.step_override_limit ,
695 X_OVERRIDE_EFTSU => cur_sua_def_var_rec.override_eftsu ,
696 -- as part of ENCR026 Changed the below col value. The new value was being
697 -- set to the override limit, now setting to the original value in the db and not changing it
698 X_OVERRIDE_ACHIEVABLE_CP => cur_sua_def_var_rec.override_achievable_cp ,
699 X_OVERRIDE_OUTCOME_DUE_DT => cur_sua_def_var_rec.override_outcome_due_dt ,
700 X_OVERRIDE_CREDIT_REASON => cur_sua_def_var_rec.override_credit_reason ,
701 X_ADMINISTRATIVE_PRIORITY => cur_sua_def_var_rec.administrative_priority ,
702 X_WAITLIST_DT => cur_sua_def_var_rec.waitlist_dt ,
703 X_DCNT_REASON_CD => cur_sua_def_var_rec.dcnt_reason_cd ,
704 X_MODE => 'R' ,
705 X_GS_VERSION_NUMBER => cur_sua_def_var_rec.gs_version_number ,
706 X_ENR_METHOD_TYPE => cur_sua_def_var_rec.enr_method_type ,
707 X_FAILED_UNIT_RULE => cur_sua_def_var_rec.failed_unit_rule ,
708 X_CART => cur_sua_def_var_rec.cart ,
709 X_RSV_SEAT_EXT_ID => cur_sua_def_var_rec.RSV_SEAT_EXT_ID ,
710 X_ORG_UNIT_CD => cur_sua_def_var_rec.ORG_UNIT_CD,
711 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
712 X_SESSION_ID => cur_sua_def_var_rec.session_id,
713 X_DEG_AUD_DETAIL_ID => cur_sua_def_var_rec.deg_aud_detail_id,
714 X_GRADING_SCHEMA_CODE => cur_sua_def_var_rec.grading_schema_code,
715 X_STUDENT_CAREER_STATISTICS => cur_sua_def_var_rec.student_career_statistics,
716 X_STUDENT_CAREER_TRANSCRIPT => cur_sua_def_var_rec.student_career_transcript,
717 X_SUBTITLE => cur_sua_def_var_rec.subtitle ,
718 X_ATTRIBUTE_CATEGORY => cur_sua_def_var_rec.attribute_category,
719 X_ATTRIBUTE1 => cur_sua_def_var_rec.attribute1,
720 X_ATTRIBUTE2 => cur_sua_def_var_rec.attribute2,
721 X_ATTRIBUTE3 => cur_sua_def_var_rec.attribute3,
722 X_ATTRIBUTE4 => cur_sua_def_var_rec.attribute4,
723 X_ATTRIBUTE5 => cur_sua_def_var_rec.attribute5,
724 X_ATTRIBUTE6 => cur_sua_def_var_rec.attribute6,
725 X_ATTRIBUTE7 => cur_sua_def_var_rec.attribute7,
726 X_ATTRIBUTE8 => cur_sua_def_var_rec.attribute8,
727 X_ATTRIBUTE9 => cur_sua_def_var_rec.attribute9,
728 X_ATTRIBUTE10 => cur_sua_def_var_rec.attribute10,
729 X_ATTRIBUTE11 => cur_sua_def_var_rec.attribute11,
730 X_ATTRIBUTE12 => cur_sua_def_var_rec.attribute12,
731 X_ATTRIBUTE13 => cur_sua_def_var_rec.attribute13,
732 X_ATTRIBUTE14 => cur_sua_def_var_rec.attribute14,
733 X_ATTRIBUTE15 => cur_sua_def_var_rec.attribute15,
734 X_ATTRIBUTE16 => cur_sua_def_var_rec.attribute16,
735 X_ATTRIBUTE17 => cur_sua_def_var_rec.attribute17,
736 X_ATTRIBUTE18 => cur_sua_def_var_rec.attribute18,
737 X_ATTRIBUTE19 => cur_sua_def_var_rec.attribute19,
738 X_ATTRIBUTE20 => cur_sua_def_var_rec.attribute20,
739 X_WAITLIST_MANUAL_IND => cur_sua_def_var_rec.waitlist_manual_ind ,--Added by mesriniv for Bug 2554109 Mini Waitlist Build.,
740 X_WLST_PRIORITY_WEIGHT_NUM => cur_sua_def_var_rec.wlst_priority_weight_num,
741 X_WLST_PREFERENCE_WEIGHT_NUM => cur_sua_def_var_rec.wlst_preference_weight_num,
742 -- CORE_INDICATOR_CODE --added by rvangala 07-OCT-2003. Enh Bug# 3052432
743 X_CORE_INDICATOR_CODE => cur_sua_def_var_rec.core_indicator_code
744 ) ;
745
746
747 l_message := NULL;
748 IF l_deny_warn_att_type='DENY' AND NOT igs_en_elgbl_program.eval_unit_forced_type(
749 p_person_id => cur_sua_def_var_rec.person_id,
750 p_load_calendar_type => l_load_cal_type,
751 p_load_cal_sequence_number => l_load_sequence_number,
752 p_uoo_id => cur_sua_def_var_rec.uoo_id,
753 p_course_cd => cur_sua_def_var_rec.course_cd,
754 p_course_version => l_prg_ver,
755 p_message => l_message,
756 p_deny_warn => l_deny_warn_att_type1 ,
757 p_enrollment_category => l_enr_cat,
758 p_comm_type => l_enr_comm,
759 p_method_type => l_enr_meth_type,
760 p_calling_obj => 'JOB' ) THEN
761
762 Fnd_Message.Set_Name ('IGS',l_message );
763 IGS_GE_MSG_STACK.ADD;
764 App_Exception.Raise_Exception;
765 END IF ;
766
767 l_message := NULL;
768 IF l_deny_warn_cross_fac ='DENY' AND NOT igs_en_elgbl_program.eval_cross_validation (
769 p_person_id => cur_sua_def_var_rec.person_id,
770 p_load_cal_type => l_load_cal_type,
771 p_load_ci_sequence_number => l_load_sequence_number,
772 p_uoo_id => cur_sua_def_var_rec.uoo_id,
773 p_course_cd => cur_sua_def_var_rec.course_cd,
774 p_program_version => l_prg_ver,
775 p_message => l_message,
776 p_deny_warn => l_deny_warn_cross_fac,
777 p_upd_cp => NULL ,
778 p_eligibility_step_type => cst_crossfac,
779 p_calling_obj => 'JOB' ) THEN
780 Fnd_Message.Set_Name ('IGS',l_message );
781 IGS_GE_MSG_STACK.ADD;
782 App_Exception.Raise_Exception;
783 END IF ;
784
785 l_message := NULL;
786 IF l_deny_warn_cross_mod ='DENY' AND NOT igs_en_elgbl_program.eval_cross_validation (
787 p_person_id => cur_sua_def_var_rec.person_id,
788 p_load_cal_type => l_load_cal_type,
789 p_load_ci_sequence_number => l_load_sequence_number,
790 p_uoo_id => cur_sua_def_var_rec.uoo_id,
791 p_course_cd => cur_sua_def_var_rec.course_cd,
792 p_program_version => l_prg_ver,
793 p_message => l_message,
794 p_deny_warn => l_deny_warn_cross_mod,
795 p_upd_cp => NULL ,
796 p_eligibility_step_type => cst_crossmod,
797 p_calling_obj => 'JOB' ) THEN
798
799 Fnd_Message.Set_Name ('IGS',l_message );
800 IGS_GE_MSG_STACK.ADD;
801 App_Exception.Raise_Exception;
802 END IF ;
803
804 l_message := NULL;
805 IF l_deny_warn_cross_loc ='DENY' AND NOT igs_en_elgbl_program.eval_cross_validation (
806 p_person_id => cur_sua_def_var_rec.person_id,
807 p_load_cal_type => l_load_cal_type,
808 p_load_ci_sequence_number => l_load_sequence_number,
809 p_uoo_id => cur_sua_def_var_rec.uoo_id,
810 p_course_cd => cur_sua_def_var_rec.course_cd,
811 p_program_version => l_prg_ver,
812 p_message => l_message,
813 p_deny_warn => l_deny_warn_cross_loc,
814 p_upd_cp => NULL ,
815 p_eligibility_step_type => cst_crossloc,
816 p_calling_obj => 'JOB') THEN
817
818 Fnd_Message.Set_Name ('IGS',l_message );
819 IGS_GE_MSG_STACK.ADD;
820 App_Exception.Raise_Exception;
821 END IF ;
822
823 END LOOP loop_sua_rec;
824
825 -- bmerugu added for 4433428
826 -- update all the active planning sheet records for this person with the approved cp
827 -- Override record defined at Unit Section level
828 IF new_references.uoo_id <> -1 THEN
829 -- fetch all plan unit Atempts where plan.uoo_id = new_references.uoo_id
830 l_plan_def_query := 'SELECT plan.rowid, plan.*
831 FROM igs_en_plan_units plan
832 WHERE plan.uoo_id = :1 AND plan.person_id = :2
833 AND plan.no_assessment_ind <> ''Y''
834 AND EXISTS(SELECT ''x'' FROM igs_en_spa_terms spa
835 where spa.person_id=plan.person_id
836 and spa.program_cd=plan.course_cd
837 and spa.term_cal_type=plan.term_cal_type
838 and spa.term_sequence_number=plan.term_ci_sequence_number
839 and spa.plan_sht_status IN (''PLAN'',''NONE''))
840 ';
841 OPEN cur_plan_def_var FOR l_plan_def_query USING new_references.uoo_id, l_cur_step_cal_rec.person_id;
842 -- Override record defined at Unit level
843 ELSIF new_references.unit_cd IS NOT NULL THEN
844 -- fetch all plan unit attempts for the student with matching unit_cd
845 l_plan_def_query := 'SELECT plan.rowid,plan.*
846 FROM igs_en_plan_units plan , igs_ps_unit_ofr_opt_all b
847 WHERE plan.person_id = :1
848 AND plan.uoo_id = b.uoo_id
849 AND b.unit_cd = :2 AND b.version_number = :3
850 AND plan.no_assessment_ind <> ''Y''
851 AND ((plan.term_cal_type = :4 AND
852 plan.term_ci_sequence_number = :5 ) OR
853 ((plan.term_cal_type,plan.term_ci_sequence_number) IN
854 (SELECT load_cal_type,load_ci_sequence_number
855 FROM igs_ca_teach_to_load_v
856 WHERE teach_cal_type = :6 AND teach_ci_sequence_number = :7 )))
857 AND EXISTS(SELECT ''x'' FROM igs_en_spa_terms spa
858 where spa.person_id=plan.person_id
859 and spa.program_cd=plan.course_cd
860 and spa.term_cal_type=plan.term_cal_type
861 and spa.term_sequence_number=plan.term_ci_sequence_number
862 and spa.plan_sht_status IN (''PLAN'',''NONE''))
863 ';
864 OPEN cur_plan_def_var FOR l_plan_def_query USING l_cur_step_cal_rec.person_id,new_references.unit_cd, new_references.version_number,
865 l_cur_step_cal_rec.cal_type, l_cur_step_cal_rec.ci_sequence_number,
866 l_cur_step_cal_rec.cal_type, l_cur_step_cal_rec.ci_sequence_number;
867 -- Override record defined at Teaching or Term calendar level
868 ELSE
869 -- fetch all plan Unit Atempts where sua teach cal type is equal to or sub ordinate to the Cal Type
870 -- where Override record is defined.
871 l_plan_def_query := 'SELECT DISTINCT plan.rowid, plan.*
872 FROM igs_en_plan_units plan
873 WHERE plan.person_id = :1
874 AND plan.no_assessment_ind <> ''Y''
875 AND ((plan.term_cal_type = :2 AND
876 plan.term_ci_sequence_number = :3 ) OR
877 ((plan.term_cal_type,plan.term_ci_sequence_number) IN
878 (SELECT load_cal_type,load_ci_sequence_number
879 FROM igs_ca_teach_to_load_v
880 WHERE teach_cal_type = :4 AND teach_ci_sequence_number = :5 )))
881 AND EXISTS(SELECT ''x'' FROM igs_en_spa_terms spa
882 WHERE spa.person_id=plan.person_id
883 and spa.program_cd=plan.course_cd
884 and spa.term_cal_type=plan.term_cal_type
885 and spa.term_sequence_number=plan.term_ci_sequence_number
886 and spa.plan_sht_status IN (''PLAN'',''NONE''))
887 ';
888 OPEN cur_plan_def_var FOR l_plan_def_query USING l_cur_step_cal_rec.person_id, l_cur_step_cal_rec.cal_type, l_cur_step_cal_rec.ci_sequence_number,
889 l_cur_step_cal_rec.cal_type, l_cur_step_cal_rec.ci_sequence_number;
890 END IF;
891 <<loop_plan_rec >> -- loop lable
892 LOOP
893 FETCH cur_plan_def_var INTO cur_plan_def_var_rec;
894 EXIT WHEN cur_plan_def_var%NOTFOUND;
895 -- call IGS_EN_PLAN_UNITS_PKG.UPDATE_ROW
896 igs_en_plan_units_pkg.update_row(
897 x_rowid => cur_plan_def_var_rec.rowid,
898 x_person_id => cur_plan_def_var_rec.person_id,
899 x_course_cd => cur_plan_def_var_rec.course_cd,
900 x_uoo_id => cur_plan_def_var_rec.uoo_id,
901 x_term_cal_type => cur_plan_def_var_rec.term_cal_type,
902 x_term_ci_sequence_number => cur_plan_def_var_rec.term_ci_sequence_number,
903 x_no_assessment_ind => cur_plan_def_var_rec.no_assessment_ind,
904 x_sup_uoo_id => cur_plan_def_var_rec.sup_uoo_id,
905 x_override_enrolled_cp => new_references.step_override_limit,
906 x_grading_schema_code => cur_plan_def_var_rec.grading_schema_code,
907 x_gs_version_number => cur_plan_def_var_rec.gs_version_number,
908 x_core_indicator_code => cur_plan_def_var_rec.core_indicator_code,
909 x_alternative_title => cur_plan_def_var_rec.alternative_title,
910 x_cart_error_flag => cur_plan_def_var_rec.cart_error_flag,
911 x_session_id => cur_plan_def_var_rec.session_id,
912 x_mode => 'R'
913 );
914 END LOOP loop_plan_rec;
915
916 END enrp_val_appr_cr_pt;
917
918 PROCEDURE check_uniqueness AS
919 /*
920 || Created By : [email protected]
921 || Created On : 15-MAY-2003
922 || Purpose : Handles the Unique Constraint logic defined for the columns.
923 || Known limitations, enhancements or remarks :
924 || Change History :
925 || Who When What
926 || (reverse chronological order - newest change first)
927 */
928 BEGIN
929
930 IF ( get_uk_for_validation (
931 new_references.elgb_ovr_step_id,
932 new_references.unit_cd,
933 new_references.version_number,
934 new_references.uoo_id
935 )
936 ) THEN
937 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
938 igs_ge_msg_stack.add;
939 app_exception.raise_exception;
940 END IF;
941
942 END check_uniqueness;
943
944 PROCEDURE AfterRowInsertUpdate(
945 p_inserting IN BOOLEAN ,
946 p_updating IN BOOLEAN
947 ) AS
948
949 CURSOR get_step_type is
950 SELECT eos.step_override_type
951 FROM igs_en_elgb_ovr_step eos
952 WHERE eos.elgb_ovr_step_id = NEW_REFERENCES.elgb_ovr_step_id ;
953
954 l_step_override_type igs_en_elgb_ovr_step.step_override_type%TYPE := NULL;
955
956 BEGIN
957
958 IF p_inserting OR p_updating THEN
959 OPEN get_step_type ;
960 FETCH get_step_type INTO l_step_override_type ;
961 CLOSE get_step_type ;
962
963 IF l_step_override_type = 'VAR_CREDIT_APPROVAL' AND
964 NVL(NEW_REFERENCES.step_override_limit,-1) <> NVL(OLD_REFERENCES.step_override_limit,-1) Then
965 enrp_val_appr_cr_pt;
966 END IF;
967 END IF;
968 END ;
969
970 PROCEDURE check_parent_existance AS
971 /*
972 || Created By : [email protected]
973 || Created On : 29-JUN-2001
974 || Purpose : Checks for the existance of Parent records.
975 || Known limitations, enhancements or remarks :
976 || Change History :
977 || Who When What
978 || (reverse chronological order - newest change first)
979 */
980 BEGIN
981
982 IF (((old_references.elgb_ovr_step_id = new_references.elgb_ovr_step_id)) OR
983 ((new_references.elgb_ovr_step_id IS NULL))) THEN
984 NULL;
985 ELSIF NOT igs_en_elgb_ovr_step_pkg.get_pk_for_validation (
986 new_references.elgb_ovr_step_id
987 ) THEN
988 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
989 igs_ge_msg_stack.add;
990 app_exception.raise_exception;
991 END IF;
992
993 IF (old_references.uoo_id = new_references.uoo_id) OR
994 (new_references.uoo_id IS NULL) OR (new_references.uoo_id = -1 ) THEN
995 NULL;
996 ELSIF NOT igs_ps_unit_ofr_opt_pkg.get_uk_For_validation (
997 new_references.uoo_id
998 ) THEN
999 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
1000 igs_ge_msg_stack.add;
1001 app_exception.raise_exception;
1002 END IF;
1003
1004 IF (((old_references.unit_cd = new_references.unit_cd) AND
1005 (old_references.version_number = new_references.version_number)) OR
1006 ((new_references.unit_cd IS NULL) OR
1007 (new_references.version_number IS NULL))) THEN
1008 NULL;
1009 ELSIF NOT igs_ps_unit_ver_pkg.get_pk_for_validation (
1010 new_references.unit_cd,
1011 new_references.version_number
1012 ) THEN
1013 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
1014 igs_ge_msg_stack.add;
1015 app_exception.raise_exception;
1016 END IF;
1017
1018 END check_parent_existance;
1019
1020
1021 PROCEDURE get_fk_igs_en_elgb_ovr_step (
1022 x_elgb_ovr_step_id IN NUMBER
1023 ) AS
1024 /*
1025 || Created By : [email protected]
1026 || Created On : 15-MAY-2003
1027 || Purpose : Initialises the Old and New references for the columns of the table.
1028 || Known limitations, enhancements or remarks :
1029 || Change History :
1030 || Who When What
1031 || (reverse chronological order - newest change first)
1032 */
1033
1034 CURSOR cur_rowid IS
1035 SELECT rowid
1036 FROM igs_en_elgb_ovr_uoo
1037 WHERE ((elgb_ovr_step_id = x_elgb_ovr_step_id ));
1038
1039 lv_rowid cur_rowid%RowType;
1040
1041 BEGIN
1042
1043 OPEN cur_rowid;
1044 FETCH cur_rowid INTO lv_rowid;
1045 IF (cur_rowid%FOUND) THEN
1046 CLOSE cur_rowid;
1047 fnd_message.set_name ('IGS', 'IGS_EN_ELGB_STEP_UNIT_FK');
1048 igs_ge_msg_stack.add;
1049 app_exception.raise_exception;
1050 RETURN;
1051 END IF;
1052 CLOSE cur_rowid;
1053
1054 END get_fk_igs_en_elgb_ovr_step;
1055
1056
1057 PROCEDURE get_ufk_igs_ps_unit_ofr_opt (
1058 x_uoo_id IN NUMBER
1059 ) AS
1060 /*
1061 || Created By : [email protected]
1062 || Created On : 15-MAY-2003
1063 || Purpose : Initialises the Old and New references for the columns of the table.
1064 || Known limitations, enhancements or remarks :
1065 || Change History :
1066 || Who When What
1067 || (reverse chronological order - newest change first)
1068 */
1069
1070 CURSOR cur_rowid IS
1071 SELECT rowid
1072 FROM igs_en_elgb_ovr_step
1073 WHERE ((uoo_id = x_uoo_id));
1074
1075 lv_rowid cur_rowid%RowType;
1076
1077 BEGIN
1078
1079 OPEN cur_rowid;
1080 FETCH cur_rowid INTO lv_rowid;
1081 IF (cur_rowid%FOUND) THEN
1082 CLOSE cur_rowid;
1083 fnd_message.set_name ('IGS', 'IGS_EN_EOS_UOO_FK');
1084 igs_ge_msg_stack.add;
1085 app_exception.raise_exception;
1086 RETURN;
1087 END IF;
1088 CLOSE cur_rowid;
1089
1090 END get_ufk_igs_ps_unit_ofr_opt;
1091
1092
1093 PROCEDURE get_fk_igs_ps_unit_ver (
1094 x_unit_cd IN VARCHAR2,
1095 x_version_number IN NUMBER
1096 ) AS
1097 /*
1098 || Created By : [email protected]
1099 || Created On : 15-MAY-2003
1100 || Purpose : Initialises the Old and New references for the columns of the table.
1101 || Known limitations, enhancements or remarks :
1102 || Change History :
1103 || Who When What
1104 || (reverse chronological order - newest change first)
1105 */
1106
1107 CURSOR cur_rowid IS
1108 SELECT rowid
1109 FROM igs_en_elgb_ovr_step
1110 WHERE ((unit_cd = x_unit_cd) AND
1111 (version_number = x_version_number));
1112
1113 lv_rowid cur_rowid%RowType;
1114
1115 BEGIN
1116
1117 OPEN cur_rowid;
1118 FETCH cur_rowid INTO lv_rowid;
1119 IF (cur_rowid%FOUND) THEN
1120 CLOSE cur_rowid;
1121 fnd_message.set_name ('IGS', 'IGS_EN_EOS_UV_FK');
1122 igs_ge_msg_stack.add;
1123 app_exception.raise_exception;
1124 RETURN;
1125 END IF;
1126 CLOSE cur_rowid;
1127
1128 END get_fk_igs_ps_unit_ver;
1129
1130
1131 FUNCTION get_pk_for_validation (
1132 x_elgb_ovr_step_uoo_id IN NUMBER
1133 ) RETURN BOOLEAN AS
1134 /*
1135 || Created By : [email protected]
1136 || Created On : 15-MAY-2003
1137 || Purpose : Validates the Primary Key of the table.
1138 || Known limitations, enhancements or remarks :
1139 || Change History :
1140 || Who When What
1141 || (reverse chronological order - newest change first)
1142 */
1143 CURSOR cur_rowid IS
1144 SELECT rowid
1145 FROM igs_en_elgb_ovr_uoo
1146 WHERE elgb_ovr_step_uoo_id = x_elgb_ovr_step_uoo_id
1147 FOR UPDATE NOWAIT;
1148
1149 lv_rowid cur_rowid%RowType;
1150
1151 BEGIN
1152
1153 OPEN cur_rowid;
1154 FETCH cur_rowid INTO lv_rowid;
1155 IF (cur_rowid%FOUND) THEN
1156 CLOSE cur_rowid;
1157 RETURN(TRUE);
1158 ELSE
1159 CLOSE cur_rowid;
1160 RETURN(FALSE);
1161 END IF;
1162
1163 END get_pk_for_validation;
1164
1165
1166 FUNCTION get_uk_for_validation (
1167 x_elgb_ovr_step_id IN NUMBER,
1168 x_unit_cd IN VARCHAR2,
1169 x_version_number IN NUMBER,
1170 x_uoo_id IN NUMBER
1171 ) RETURN BOOLEAN AS
1172 /*
1173 || Created By : [email protected]
1174 || Created On : 15-MAY-2003
1175 || Purpose : Validates the Unique Keys of the table.
1176 || Known limitations, enhancements or remarks :
1177 || Change History :
1178 || Who When What
1179 || (reverse chronological order - newest change first)
1180 */
1181 CURSOR cur_rowid IS
1182 SELECT rowid
1183 FROM igs_en_elgb_ovr_uoo
1184 WHERE elgb_ovr_step_id = x_elgb_ovr_step_id
1185 AND unit_cd = x_unit_cd
1186 AND version_number = x_version_number
1187 AND ( uoo_id = x_uoo_id or ( x_uoo_id IS NULL AND uoo_id = -1))
1188 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
1189
1190 lv_rowid cur_rowid%RowType;
1191
1192 BEGIN
1193
1194 OPEN cur_rowid;
1195 FETCH cur_rowid INTO lv_rowid;
1196 IF (cur_rowid%FOUND) THEN
1197 CLOSE cur_rowid;
1198 RETURN (true);
1199 ELSE
1200 CLOSE cur_rowid;
1201 RETURN(FALSE);
1202 END IF;
1203
1204 END get_uk_for_validation ;
1205
1206
1207 PROCEDURE before_dml (
1208 p_action IN VARCHAR2,
1209 x_rowid IN VARCHAR2,
1210 x_elgb_ovr_step_uoo_id IN NUMBER,
1211 x_elgb_ovr_step_id IN NUMBER,
1212 x_unit_cd IN VARCHAR2,
1213 x_version_number IN NUMBER,
1214 x_uoo_id IN NUMBER,
1215 x_step_override_limit IN NUMBER,
1216 x_creation_date IN DATE,
1217 x_created_by IN NUMBER,
1218 x_last_update_date IN DATE,
1219 x_last_updated_by IN NUMBER,
1220 x_last_update_login IN NUMBER
1221 ) AS
1222 /*
1223 || Created By : [email protected]
1224 || Created On : 15-MAY-2003
1225 || Purpose : Initialises the columns, Checks Constraints, Calls the
1226 || Trigger Handlers for the table, before any DML operation.
1227 || Known limitations, enhancements or remarks :
1228 || Change History :
1229 || Who When What
1230 || (reverse chronological order - newest change first)
1231 */
1232 BEGIN
1233
1234 set_column_values (
1235 p_action,
1236 x_rowid,
1237 x_elgb_ovr_step_uoo_id,
1238 x_elgb_ovr_step_id,
1239 x_unit_cd,
1240 x_version_number,
1241 x_uoo_id,
1242 x_step_override_limit,
1243 x_creation_date,
1244 x_created_by,
1245 x_last_update_date,
1246 x_last_updated_by,
1247 x_last_update_login
1248 );
1249
1250 IF (p_action = 'INSERT') THEN
1251 -- Call all the procedures related to Before Insert.
1252 IF ( get_pk_for_validation(
1253 new_references.elgb_ovr_step_uoo_id
1254 )
1255 ) THEN
1256 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
1257 igs_ge_msg_stack.add;
1258 app_exception.raise_exception;
1259 END IF;
1260 check_uniqueness;
1261 check_parent_existance ;
1262 ELSIF (p_action = 'UPDATE') THEN
1263 -- Call all the procedures related to Before Update.
1264 check_uniqueness;
1265 check_parent_existance ;
1266 ELSIF (p_action = 'VALIDATE_INSERT') THEN
1267 -- Call all the procedures related to Before Insert.
1268 IF ( get_pk_for_validation (
1269 new_references.elgb_ovr_step_uoo_id
1270 )
1271 ) THEN
1272 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
1273 igs_ge_msg_stack.add;
1274 app_exception.raise_exception;
1275 END IF;
1276 check_uniqueness;
1277 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
1278 check_uniqueness;
1279 END IF;
1280
1281 END before_dml;
1282
1283 PROCEDURE After_DML (
1284 p_action IN VARCHAR2,
1285 x_rowid IN VARCHAR2
1286 ) AS
1287 BEGIN
1288 l_rowid := x_rowid;
1289 IF (p_action = 'INSERT') THEN
1290 -- Call all the procedures related to After Insert.
1291 AfterRowInsertUpdate( p_inserting => TRUE ,p_updating=>FALSE);
1292 ELSIF (p_action = 'UPDATE') THEN
1293 -- Call all the procedures related to After Update.
1294 AfterRowInsertUpdate( p_inserting=>FALSE,p_updating => TRUE );
1295 ELSIF (p_action = 'DELETE') THEN
1296 null;
1297 END IF;
1298 END After_DML;
1299
1300
1301 PROCEDURE insert_row (
1302 x_rowid IN OUT NOCOPY VARCHAR2,
1303 x_elgb_ovr_step_uoo_id IN OUT NOCOPY NUMBER,
1304 x_elgb_ovr_step_id IN NUMBER,
1305 x_unit_cd IN VARCHAR2,
1306 x_version_number IN NUMBER,
1307 x_uoo_id IN NUMBER,
1308 x_step_override_limit IN NUMBER,
1309 x_mode IN VARCHAR2
1310 ) AS
1311 /*
1312 || Created By : [email protected]
1313 || Created On : 15-MAY-2003
1314 || Purpose : Handles the INSERT DML logic for the table.
1315 || Known limitations, enhancements or remarks :
1316 || Change History :
1317 || Who When What
1318 || (reverse chronological order - newest change first)
1319 */
1320
1321 x_last_update_date DATE;
1322 x_last_updated_by NUMBER;
1323 x_last_update_login NUMBER;
1324
1325 BEGIN
1326
1327 x_last_update_date := SYSDATE;
1328 IF (x_mode = 'I') THEN
1329 x_last_updated_by := 1;
1330 x_last_update_login := 0;
1331 ELSIF (x_mode = 'R') THEN
1332 x_last_updated_by := fnd_global.user_id;
1333 IF (x_last_updated_by IS NULL) THEN
1334 x_last_updated_by := -1;
1335 END IF;
1336 x_last_update_login := fnd_global.login_id;
1337 IF (x_last_update_login IS NULL) THEN
1338 x_last_update_login := -1;
1339 END IF;
1340 ELSE
1341 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
1342 fnd_message.set_token ('ROUTINE', 'IGS_EN_ELGB_OVR_UOO_PKG.INSERT_ROW');
1343 igs_ge_msg_stack.add;
1344 app_exception.raise_exception;
1345 END IF;
1346
1347 x_elgb_ovr_step_uoo_id := NULL;
1348
1349 before_dml(
1350 p_action => 'INSERT',
1351 x_rowid => x_rowid,
1352 x_elgb_ovr_step_uoo_id => x_elgb_ovr_step_uoo_id,
1353 x_elgb_ovr_step_id => x_elgb_ovr_step_id,
1354 x_unit_cd => x_unit_cd,
1355 x_version_number => x_version_number,
1356 x_uoo_id => x_uoo_id,
1357 x_step_override_limit => x_step_override_limit,
1358 x_creation_date => x_last_update_date,
1359 x_created_by => x_last_updated_by,
1360 x_last_update_date => x_last_update_date,
1361 x_last_updated_by => x_last_updated_by,
1362 x_last_update_login => x_last_update_login
1363 );
1364
1365 IF new_references.uoo_id IS NULL THEN
1366 new_references.uoo_id := -1 ;
1367 END IF ;
1368
1369 INSERT INTO igs_en_elgb_ovr_uoo (
1370 elgb_ovr_step_uoo_id,
1371 elgb_ovr_step_id,
1372 unit_cd,
1373 version_number,
1374 uoo_id,
1375 step_override_limit,
1376 creation_date,
1377 created_by,
1378 last_update_date,
1379 last_updated_by,
1380 last_update_login
1381 ) VALUES (
1382 igs_en_elgb_ovr_step_uoo_s.NEXTVAL,
1383 new_references.elgb_ovr_step_id,
1384 new_references.unit_cd,
1385 new_references.version_number,
1386 new_references.uoo_id,
1387 new_references.step_override_limit,
1388 x_last_update_date,
1389 x_last_updated_by,
1390 x_last_update_date,
1391 x_last_updated_by,
1392 x_last_update_login
1393 ) RETURNING ROWID, elgb_ovr_step_uoo_id INTO x_rowid, x_elgb_ovr_step_uoo_id;
1394
1395 After_DML(
1396 p_action => 'INSERT',
1397 x_rowid => X_ROWID
1398 );
1399
1400 END insert_row;
1401
1402
1403 PROCEDURE lock_row (
1404 x_rowid IN VARCHAR2,
1405 x_elgb_ovr_step_uoo_id IN NUMBER,
1406 x_elgb_ovr_step_id IN NUMBER,
1407 x_unit_cd IN VARCHAR2,
1408 x_version_number IN NUMBER,
1409 x_uoo_id IN NUMBER,
1410 x_step_override_limit IN NUMBER
1411 ) AS
1412 /*
1413 || Created By : [email protected]
1414 || Created On : 15-MAY-2003
1415 || Purpose : Handles the LOCK mechanism for the table.
1416 || Known limitations, enhancements or remarks :
1417 || Change History :
1418 || Who When What
1419 || (reverse chronological order - newest change first)
1420 */
1421 CURSOR c1 IS
1422 SELECT
1423 elgb_ovr_step_id,
1424 unit_cd,
1425 version_number,
1426 uoo_id,
1427 step_override_limit
1428 FROM igs_en_elgb_ovr_uoo
1429 WHERE rowid = x_rowid
1430 FOR UPDATE NOWAIT;
1431
1432 tlinfo c1%ROWTYPE;
1433
1434 BEGIN
1435
1436 OPEN c1;
1437 FETCH c1 INTO tlinfo;
1438 IF (c1%notfound) THEN
1439 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1440 igs_ge_msg_stack.add;
1441 CLOSE c1;
1442 app_exception.raise_exception;
1443 RETURN;
1444 END IF;
1445 CLOSE c1;
1446
1447 IF (
1448 (tlinfo.elgb_ovr_step_id = x_elgb_ovr_step_id)
1449 AND (tlinfo.unit_cd = x_unit_cd)
1450 AND (tlinfo.version_number = x_version_number)
1451 AND (tlinfo.uoo_id = x_uoo_id)
1452 AND ((tlinfo.step_override_limit = x_step_override_limit) OR ((tlinfo.step_override_limit IS NULL) AND (X_step_override_limit IS NULL)))
1453 ) THEN
1454 NULL;
1455 ELSE
1456 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1457 igs_ge_msg_stack.add;
1458 app_exception.raise_exception;
1459 END IF;
1460
1461 RETURN;
1462
1463 END lock_row;
1464
1465
1466 PROCEDURE update_row (
1467 x_rowid IN VARCHAR2,
1468 x_elgb_ovr_step_uoo_id IN NUMBER,
1469 x_elgb_ovr_step_id IN NUMBER,
1470 x_unit_cd IN VARCHAR2,
1471 x_version_number IN NUMBER,
1472 x_uoo_id IN NUMBER,
1473 x_step_override_limit IN NUMBER,
1474 x_mode IN VARCHAR2
1475 ) AS
1476 /*
1477 || Created By : [email protected]
1478 || Created On : 15-MAY-2003
1479 || Purpose : Handles the UPDATE DML logic for the table.
1480 || Known limitations, enhancements or remarks :
1481 || Change History :
1482 || Who When What
1483 || (reverse chronological order - newest change first)
1484 */
1485 x_last_update_date DATE ;
1486 x_last_updated_by NUMBER;
1487 x_last_update_login NUMBER;
1488
1489 BEGIN
1490
1491 x_last_update_date := SYSDATE;
1492 IF (X_MODE = 'I') THEN
1493 x_last_updated_by := 1;
1494 x_last_update_login := 0;
1495 ELSIF (x_mode = 'R') THEN
1496 x_last_updated_by := fnd_global.user_id;
1497 IF x_last_updated_by IS NULL THEN
1498 x_last_updated_by := -1;
1499 END IF;
1500 x_last_update_login := fnd_global.login_id;
1501 IF (x_last_update_login IS NULL) THEN
1502 x_last_update_login := -1;
1503 END IF;
1504 ELSE
1505 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1506 fnd_message.set_token ('ROUTINE', 'IGS_EN_ELGB_OVR_UOO_PKG.UPDATE_ROW');
1507 igs_ge_msg_stack.add;
1508 app_exception.raise_exception;
1509 END IF;
1510
1511
1512
1513 before_dml(
1514 p_action => 'UPDATE',
1515 x_rowid => x_rowid,
1516 x_elgb_ovr_step_uoo_id => x_elgb_ovr_step_uoo_id,
1517 x_elgb_ovr_step_id => x_elgb_ovr_step_id,
1518 x_unit_cd => x_unit_cd,
1519 x_version_number => x_version_number,
1520 x_uoo_id => x_uoo_id,
1521 x_step_override_limit => x_step_override_limit,
1522 x_creation_date => x_last_update_date,
1523 x_created_by => x_last_updated_by,
1524 x_last_update_date => x_last_update_date,
1525 x_last_updated_by => x_last_updated_by,
1526 x_last_update_login => x_last_update_login
1527 );
1528
1529 IF new_references.uoo_id IS NULL THEN
1530 new_references.uoo_id := -1 ;
1531 END IF ;
1532
1533 UPDATE igs_en_elgb_ovr_uoo
1534 SET
1535 elgb_ovr_step_id = new_references.elgb_ovr_step_id,
1536 unit_cd = new_references.unit_cd,
1537 version_number = new_references.version_number,
1538 uoo_id = new_references.uoo_id,
1539 step_override_limit = new_references.step_override_limit,
1540 last_update_date = x_last_update_date,
1541 last_updated_by = x_last_updated_by,
1542 last_update_login = x_last_update_login
1543 WHERE rowid = x_rowid;
1544
1545 IF (SQL%NOTFOUND) THEN
1546 RAISE NO_DATA_FOUND;
1547 END IF;
1548
1549 After_DML(
1550 p_action => 'UPDATE',
1551 x_rowid => X_ROWID );
1552
1553 END update_row;
1554
1555
1556 PROCEDURE add_row (
1557 x_rowid IN OUT NOCOPY VARCHAR2,
1558 x_elgb_ovr_step_uoo_id IN OUT NOCOPY NUMBER,
1559 x_elgb_ovr_step_id IN NUMBER,
1560 x_unit_cd IN VARCHAR2,
1561 x_version_number IN NUMBER,
1562 x_uoo_id IN NUMBER,
1563 x_step_override_limit IN NUMBER,
1564 x_mode IN VARCHAR2
1565 ) AS
1566 /*
1567 || Created By : [email protected]
1568 || Created On : 15-MAY-2003
1569 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1570 || Known limitations, enhancements or remarks :
1571 || Change History :
1572 || Who When What
1573 || (reverse chronological order - newest change first)
1574 */
1575 CURSOR c1 IS
1576 SELECT rowid
1577 FROM igs_en_elgb_ovr_uoo
1578 WHERE elgb_ovr_step_uoo_id = x_elgb_ovr_step_uoo_id;
1579
1580 BEGIN
1581
1582 OPEN c1;
1583 FETCH c1 INTO x_rowid;
1584 IF (c1%NOTFOUND) THEN
1585 CLOSE c1;
1586
1587 insert_row (
1588 x_rowid,
1589 x_elgb_ovr_step_uoo_id,
1590 x_elgb_ovr_step_id,
1591 x_unit_cd,
1592 x_version_number,
1593 x_uoo_id,
1594 x_step_override_limit,
1595 x_mode
1596 );
1597 RETURN;
1598 END IF;
1599 CLOSE c1;
1600
1601 update_row (
1602 x_rowid,
1603 x_elgb_ovr_step_uoo_id,
1604 x_elgb_ovr_step_id,
1605 x_unit_cd,
1606 x_version_number,
1607 x_uoo_id,
1608 x_step_override_limit,
1609 x_mode
1610 );
1611
1612 END add_row;
1613
1614
1615 PROCEDURE delete_row (
1616 x_rowid IN VARCHAR2
1617 ) AS
1618 /*
1619 || Created By : [email protected]
1620 || Created On : 15-MAY-2003
1621 || Purpose : Handles the DELETE DML logic for the table.
1622 || Known limitations, enhancements or remarks :
1623 || Change History :
1624 || Who When What
1625 || (reverse chronological order - newest change first)
1626 */
1627 BEGIN
1628
1629 before_dml (
1630 p_action => 'DELETE',
1631 x_rowid => x_rowid
1632 );
1633
1634 DELETE FROM igs_en_elgb_ovr_uoo
1635 WHERE rowid = x_rowid;
1636
1637 IF (SQL%NOTFOUND) THEN
1638 RAISE NO_DATA_FOUND;
1639 END IF;
1640
1641 After_DML(
1642 p_action => 'DELETE',
1643 x_rowid => X_ROWID );
1644
1645 END delete_row;
1646
1647 END igs_en_elgb_ovr_uoo_pkg;