1 PACKAGE BODY igs_ps_usec_cps_pkg AS
2 /* $Header: IGSPI1AB.pls 120.2 2006/05/15 00:48:09 sarakshi ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ps_usec_cps%RowType;
5 new_references igs_ps_usec_cps%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_unit_sec_credit_points_id IN NUMBER DEFAULT NULL,
11 x_uoo_id IN NUMBER DEFAULT NULL,
12 x_minimum_credit_points IN NUMBER DEFAULT NULL,
13 x_maximum_credit_points IN NUMBER DEFAULT NULL,
14 x_variable_increment IN NUMBER DEFAULT NULL,
15 x_lecture_credit_points IN NUMBER DEFAULT NULL,
16 x_lab_credit_points IN NUMBER DEFAULT NULL,
17 x_other_credit_points IN NUMBER DEFAULT NULL,
18 x_clock_hours IN NUMBER DEFAULT NULL,
19 x_work_load_cp_lecture IN NUMBER DEFAULT NULL,
20 x_work_load_cp_lab IN NUMBER DEFAULT NULL,
21 x_continuing_education_units IN NUMBER DEFAULT NULL,
22 x_WORK_LOAD_OTHER IN NUMBER DEFAULT NULL,
23 x_CONTACT_HRS_LECTURE IN NUMBER DEFAULT NULL,
24 x_CONTACT_HRS_LAB IN NUMBER DEFAULT NULL,
25 x_CONTACT_HRS_OTHER IN NUMBER DEFAULT NULL,
26 x_NON_SCHD_REQUIRED_HRS IN NUMBER DEFAULT NULL,
27 x_EXCLUDE_FROM_MAX_CP_LIMIT IN VARCHAR2 DEFAULT NULL,
28 x_creation_date IN DATE DEFAULT NULL,
29 x_created_by IN NUMBER DEFAULT NULL,
30 x_last_update_date IN DATE DEFAULT NULL,
31 x_last_updated_by IN NUMBER DEFAULT NULL,
32 x_last_update_login IN NUMBER DEFAULT NULL ,
33 x_claimable_hours IN NUMBER DEFAULT NULL,
34 x_achievable_credit_points IN NUMBER DEFAULT NULL,
35 x_enrolled_credit_points IN NUMBER DEFAULT NULL,
36 x_billing_credit_points IN NUMBER,
37 x_billing_hrs IN NUMBER
38 ) AS
39
40 /*************************************************************
41 Created By : shgeorge
42 Date Created By : 10-May-2000
43 Purpose :
44 Know limitations, enhancements or remarks
45 Change History
46 Who When What
47
48 (reverse chronological order - newest change first)
49 ***************************************************************/
50
51 CURSOR cur_old_ref_values IS
52 SELECT *
53 FROM IGS_PS_USEC_CPS
54 WHERE rowid = x_rowid;
55
56 BEGIN
57
58 l_rowid := x_rowid;
59
60 -- Code for setting the Old and New Reference Values.
61 -- Populate Old Values.
62 Open cur_old_ref_values;
63 Fetch cur_old_ref_values INTO old_references;
64 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
65 Close cur_old_ref_values;
66 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
67 IGS_GE_MSG_STACK.ADD;
68 App_Exception.Raise_Exception;
69 Return;
70 END IF;
71 Close cur_old_ref_values;
72
73 -- Populate New Values.
74 new_references.unit_sec_credit_points_id := x_unit_sec_credit_points_id;
75 new_references.uoo_id := x_uoo_id;
76 new_references.minimum_credit_points := x_minimum_credit_points;
77 new_references.maximum_credit_points := x_maximum_credit_points;
78 new_references.variable_increment := x_variable_increment;
79 new_references.lecture_credit_points := x_lecture_credit_points;
80 new_references.lab_credit_points := x_lab_credit_points;
81 new_references.other_credit_points := x_other_credit_points;
82 new_references.clock_hours := x_clock_hours;
83 new_references.work_load_cp_lecture := x_work_load_cp_lecture;
84 new_references.work_load_cp_lab := x_work_load_cp_lab;
85 new_references.continuing_education_units := x_continuing_education_units;
86 new_references.work_load_other := x_work_load_other;
87 new_references.contact_hrs_lecture := x_contact_hrs_lecture ;
88 new_references.contact_hrs_lab := x_contact_hrs_lab;
89 new_references.contact_hrs_other := x_contact_hrs_other;
90 new_references.non_schd_required_hrs := x_non_schd_required_hrs;
91 new_references.exclude_from_max_cp_limit := x_exclude_from_max_cp_limit;
92 new_references.claimable_hours := x_claimable_hours;
93 new_references.achievable_credit_points := x_achievable_credit_points;
94 new_references.enrolled_credit_points := x_enrolled_credit_points;
95 new_references.billing_credit_points := x_billing_credit_points;
96 new_references.billing_hrs := x_billing_hrs;
97
98 IF (p_action = 'UPDATE') THEN
99 new_references.creation_date := old_references.creation_date;
100 new_references.created_by := old_references.created_by;
101 ELSE
102 new_references.creation_date := x_creation_date;
103 new_references.created_by := x_created_by;
104 END IF;
105 new_references.last_update_date := x_last_update_date;
106 new_references.last_updated_by := x_last_updated_by;
107 new_references.last_update_login := x_last_update_login;
108
109 END Set_Column_Values;
110
111 PROCEDURE AfterRowUpdate1(
112 p_inserting IN BOOLEAN DEFAULT FALSE,
113 p_updating IN BOOLEAN DEFAULT FALSE,
114 p_deleting IN BOOLEAN DEFAULT FALSE
115 ) AS
116
117 -- Select the teaching responsibilites record which has only workload percentage defined and not
118 -- other workload values defined.
119 CURSOR c_teach_resp(p_uoo_id NUMBER) IS
120 SELECT rowid,iputr.*
121 FROM igs_ps_usec_tch_resp iputr
122 WHERE iputr.uoo_id = p_uoo_id AND
123 iputr.percentage_allocation IS NOT NULL AND
124 iputr.instructional_load_lab IS NULL AND
125 iputr.instructional_load_lecture IS NULL AND
126 iputr.instructional_load IS NULL;
127
128 l_new_lab igs_ps_usec_tch_resp_v.instructional_load_lab%TYPE;
129 l_new_lecture igs_ps_usec_tch_resp_v.instructional_load_lecture%TYPE;
130 l_new_other igs_ps_usec_tch_resp_v.instructional_load%TYPE;
131
132 BEGIN
133 -- if the workload value are updated
134 IF (p_updating = TRUE AND ( NVL(new_references.work_load_other,-1) <> NVL(old_references.work_load_other,-1) OR
135 NVL(new_references.work_load_cp_lecture ,-1) <> NVL(old_references.work_load_cp_lecture ,-1) OR
136 NVL(new_references.work_load_cp_lab,-1) <> NVL(old_references.work_load_cp_lab,-1)
137 )) OR p_inserting = TRUE THEN
138 -- Re-calculating the values in Worload lecture,Laboratory and Other in Teaching Responsibilities as these points are modified at Unit Section level
139
140 FOR c_teach_resp_rec in c_teach_resp(new_references.uoo_id)
141 LOOP
142 igs_ps_fac_credt_wrkload.calculate_teach_work_load(c_teach_resp_rec.uoo_id,c_teach_resp_rec.percentage_allocation,l_new_lab,l_new_lecture,l_new_other);
143 igs_ps_usec_tch_resp_pkg.update_row (
144 x_mode => 'R',
145 x_rowid => c_teach_resp_rec.rowid,
146 x_unit_section_teach_resp_id => c_teach_resp_rec.unit_section_teach_resp_id,
147 x_instructor_id => c_teach_resp_rec.instructor_id,
148 x_confirmed_flag => c_teach_resp_rec.confirmed_flag ,
149 x_percentage_allocation => c_teach_resp_rec.percentage_allocation,
150 x_instructional_load => l_new_other ,
151 x_lead_instructor_flag => c_teach_resp_rec.lead_instructor_flag,
152 x_uoo_id => c_teach_resp_rec.uoo_id,
153 x_instructional_load_lab => l_new_lab,
154 x_instructional_load_lecture => l_new_lecture
155 );
156 END LOOP;
157 END IF;
158 END AfterRowUpdate1;
159
160 PROCEDURE Check_Constraints (
161 Column_Name IN VARCHAR2 DEFAULT NULL,
162 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
163 /*************************************************************
164 Created By : shgeorge
165 Date Created By : 10-May-2000
166 Purpose :
167 Know limitations, enhancements or remarks
168 Change History
169 Who When What
170 sarakshi 15-May-2006 Bug#3064563, modified the format mask(work_load_cp_lecture,work_load_cp_lab) as specified in the bug.
171 sarakhsi 16-Jan-2003 Bug#2753280,minimum value for continuing_education_units and clock_hours
172 changed from 1 to 0 and maximum value from 999 to 999.999
173 (reverse chronological order - newest change first)
174 ***************************************************************/
175
176 BEGIN
177
178 IF column_name IS NULL THEN
179 NULL;
180 ELSIF Upper(Column_Name)='MINIMUM_CREDIT_POINTS' Then
181 New_References.minimum_credit_points := Column_Value;
182 ELSIF Upper(Column_Name)='MAXIMUM_CREDIT_POINTS' Then
183 New_References.maximum_credit_points := Column_Value;
184 ELSIF Upper(Column_Name)='VARIABLE_INCREMENT' Then
185 New_References.variable_increment := Column_Value;
186 ELSIF Upper(Column_Name)='LECTURE_CREDIT_POINTS' Then
187 New_References.lecture_credit_points := Column_Value;
188 ELSIF Upper(Column_Name)='LAB_CREDIT_POINTS' Then
189 New_References.lab_credit_points := Column_Value;
190 ELSIF Upper(Column_Name)='OTHER_CREDIT_POINTS' Then
191 New_References.other_credit_points := Column_Value;
192 ELSIF Upper(Column_Name)='CLOCK_HOURS' Then
193 New_References.clock_hours := Column_Value;
194 ELSIF Upper(Column_Name)='WORK_LOAD_CP_LECTURE' Then
195 New_References.work_load_cp_lecture := Column_Value;
196 ELSIF Upper(Column_Name)='WORK_LOAD_CP_LAB' Then
197 New_References.work_load_cp_lab := Column_Value;
198 ELSIF Upper(Column_Name)='CONTINUING_EDUCATION_UNITS' Then
199 New_References.continuing_education_units := Column_Value;
200 ELSIF Upper(Column_Name)='WORK_LOAD_OTHER' Then
201 New_References.work_load_other := Column_Value;
202 ELSIF Upper(Column_Name)='CONTACT_HRS_LECTURE' Then
203 New_References.contact_hrs_lecture := Column_Value;
204 ELSIF Upper(Column_Name)='CONTACT_HRS_LAB' Then
205 New_References.contact_hrs_lab := Column_Value;
206 ELSIF Upper(Column_Name)='CONTACT_HRS_OTHER' Then
207 New_References.contact_hrs_other := Column_Value;
208 ELSIF Upper(Column_Name)='NON_SCHD_REQUIRED_HRS' Then
209 New_References.non_schd_required_hrs := Column_Value;
210 ELSIF Upper(Column_Name)='EXCLUDE_FROM_MAX_CP_LIMIT' Then
211 New_References.exclude_from_max_cp_limit := Column_Value;
212 ELSIF Upper(Column_Name)='CLAIMABLE_HOURS' Then
213 New_References.claimable_hours := Column_Value;
214 ELSIF Upper(Column_Name)='ACHIEVABLE_CREDIT_POINTS' Then
215 New_References.achievable_credit_points := Column_Value;
216 ELSIF Upper(Column_Name)='ENROLLED_CREDIT_POINTS' Then
217 New_References.enrolled_credit_points := Column_Value;
218 ELSIF Upper(Column_Name)='BILLING_CREDIT_POINTS' Then
219 New_References.billing_credit_points := Column_Value;
220 ELSIF Upper(Column_Name)='BILLING_HRS' Then
221 New_References.billing_hrs := Column_Value;
222 END IF;
223
224 IF Upper(Column_Name)='MINIMUM_CREDIT_POINTS' OR Column_Name IS NULL Then
225 IF New_References.minimum_credit_points < 0 OR New_References.minimum_credit_points > 999.999 Then
226 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
227 IGS_GE_MSG_STACK.ADD;
228 App_Exception.Raise_Exception;
229 END IF;
230 END IF;
231
232 IF Upper(Column_Name)='MAXIMUM_CREDIT_POINTS' OR Column_Name IS NULL Then
233 IF New_References.maximum_credit_points < 0 OR New_References.maximum_credit_points > 999.999 Then
234 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
235 IGS_GE_MSG_STACK.ADD;
236 App_Exception.Raise_Exception;
237 END IF;
238 END IF;
239
240 IF Upper(Column_Name)='VARIABLE_INCREMENT' OR Column_Name IS NULL Then
241 IF New_References.variable_increment < 0 OR New_References.variable_increment > 999.999 Then
242 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
243 IGS_GE_MSG_STACK.ADD;
244 App_Exception.Raise_Exception;
245 END IF;
246 END IF;
247
248 IF Upper(Column_Name)='LECTURE_CREDIT_POINTS' OR Column_Name IS NULL Then
249 IF New_References.lecture_credit_points < 0 OR New_References.lecture_credit_points > 999.999 Then
250 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
251 IGS_GE_MSG_STACK.ADD;
252 App_Exception.Raise_Exception;
253 END IF;
254 END IF;
255
256 IF Upper(Column_Name)='LAB_CREDIT_POINTS' OR Column_Name IS NULL Then
257 IF New_References.lab_credit_points < 0 OR New_References.lab_credit_points > 999.999 Then
258 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
259 IGS_GE_MSG_STACK.ADD;
260 App_Exception.Raise_Exception;
261 END IF;
262 END IF;
263
264 IF Upper(Column_Name)='OTHER_CREDIT_POINTS' OR Column_Name IS NULL Then
265 IF New_References.other_credit_points < 0 OR New_References.other_credit_points > 999.999 Then
266 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
267 IGS_GE_MSG_STACK.ADD;
268 App_Exception.Raise_Exception;
269 END IF;
270 END IF;
271
272 IF Upper(Column_Name)='CLOCK_HOURS' OR Column_Name IS NULL Then
273 IF New_References.clock_hours < 0 OR New_References.clock_hours > 999.999 Then
274 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
275 IGS_GE_MSG_STACK.ADD;
276 App_Exception.Raise_Exception;
277 END IF;
278 END IF;
279
280 IF Upper(Column_Name)='WORK_LOAD_CP_LECTURE' OR Column_Name IS NULL Then
281 IF New_References.work_load_cp_lecture < 0 OR New_References.work_load_cp_lecture > 999.99 Then
282 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
283 IGS_GE_MSG_STACK.ADD;
284 App_Exception.Raise_Exception;
285 END IF;
286 END IF;
287
288 IF Upper(Column_Name)='WORK_LOAD_CP_LAB' OR Column_Name IS NULL Then
289 IF New_References.work_load_cp_lab < 0 OR New_References.work_load_cp_lab > 999.99 Then
290 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
291 IGS_GE_MSG_STACK.ADD;
292 App_Exception.Raise_Exception;
293 END IF;
294 END IF;
295
296 IF Upper(Column_Name)='CONTINUING_EDUCATION_UNITS' OR Column_Name IS NULL Then
297 IF New_References.continuing_education_units < 0 OR New_References.continuing_education_units > 999.999 Then
298 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
299 IGS_GE_MSG_STACK.ADD;
300 App_Exception.Raise_Exception;
301 END IF;
302 END IF;
303
304 IF Upper(Column_Name)='WORK_LOAD_OTHER' OR Column_Name IS NULL Then
305 IF New_References.work_load_other < 0 OR New_References.WORK_LOAD_OTHER > 999.99 Then
306 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
307 IGS_GE_MSG_STACK.ADD;
308 App_Exception.Raise_Exception;
309 END IF;
310 END IF;
311
312 IF Upper(Column_Name)='CONTACT_HRS_LECTURE' OR Column_Name IS NULL Then
313 IF New_References.contact_hrs_lecture < 0 OR New_References.contact_hrs_lecture > 999.99 Then
314 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
315 IGS_GE_MSG_STACK.ADD;
316 App_Exception.Raise_Exception;
317 END IF;
318 END IF;
319
320 IF Upper(Column_Name)='CONTACT_HRS_LAB' OR Column_Name IS NULL Then
321 IF New_References.contact_hrs_lab < 0 OR New_References.contact_hrs_lab > 999.99 Then
322 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
323 IGS_GE_MSG_STACK.ADD;
324 App_Exception.Raise_Exception;
325 END IF;
326 END IF;
327
328 IF Upper(Column_Name)='CONTACT_HRS_OTHER' OR Column_Name IS NULL Then
329 IF New_References.contact_hrs_other < 0 OR New_References.contact_hrs_other > 999.99 Then
330 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
331 IGS_GE_MSG_STACK.ADD;
332 App_Exception.Raise_Exception;
333 END IF;
334 END IF;
335
336 IF Upper(Column_Name)='NON_SCHD_REQUIRED_HRS' OR Column_Name IS NULL Then
337 IF New_References.non_schd_required_hrs < 0 OR New_References.non_schd_required_hrs > 999.99 Then
338 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
339 IGS_GE_MSG_STACK.ADD;
340 App_Exception.Raise_Exception;
341 END IF;
342 END IF;
343
344 IF Upper(Column_Name)= 'EXCLUDE_FROM_MAX_CP_LIMIT' OR Column_Name IS NULL Then
345 IF New_References.exclude_from_max_cp_limit NOT IN ( 'Y' , 'N' ) Then
346 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
347 IGS_GE_MSG_STACK.ADD;
348 App_Exception.Raise_Exception;
349 END IF;
350 END IF;
351
352 IF Upper(Column_Name)='CLAIMABLE_HOURS' OR Column_Name IS NULL Then
353 IF New_References.claimable_hours < 0 OR New_References.claimable_hours > 99999.99 Then
354 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
355 IGS_GE_MSG_STACK.ADD;
356 App_Exception.Raise_Exception;
357 END IF;
358 END IF;
359
360 IF Upper(Column_Name)='ACHIEVABLE_CREDIT_POINTS' OR Column_Name IS NULL Then
361 IF New_References.achievable_credit_points < 0 OR New_References.achievable_credit_points > 999.999 Then
362 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
363 IGS_GE_MSG_STACK.ADD;
364 App_Exception.Raise_Exception;
365 END IF;
366 END IF;
367
368 IF Upper(Column_Name)='ENROLLED_CREDIT_POINTS' OR Column_Name IS NULL Then
369 IF New_References.enrolled_credit_points < 0 OR New_References.enrolled_credit_points > 999.999 Then
370 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
371 IGS_GE_MSG_STACK.ADD;
372 App_Exception.Raise_Exception;
373 END IF;
374 END IF;
375
376 IF Upper(Column_Name)='BILLING_CREDIT_POINTS' OR Column_Name IS NULL Then
377 IF New_References.billing_credit_points < 0 OR New_References.billing_credit_points > 999.999 Then
378 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
379 IGS_GE_MSG_STACK.ADD;
380 App_Exception.Raise_Exception;
381 END IF;
382 END IF;
383
384 IF Upper(Column_Name)='BILLING_HRS' OR Column_Name IS NULL Then
385 IF New_References.billing_hrs < 0 OR New_References.billing_hrs > 999.999 Then
386 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
387 IGS_GE_MSG_STACK.ADD;
388 App_Exception.Raise_Exception;
389 END IF;
390 END IF;
391
392 END Check_Constraints;
393
394 PROCEDURE Check_Uniqueness AS
395 /*************************************************************
396 Created By : shgeroge
397 Date Created By : 10-May-2000
398 Purpose :
399 Know limitations, enhancements or remarks
400 Change History
401 Who When What
402
403 (reverse chronological order - newest change first)
404 ***************************************************************/
405
406 begin
407 IF Get_Uk_For_Validation (
408 new_references.uoo_id
409 ) THEN
410 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
411 IGS_GE_MSG_STACK.ADD;
412 app_exception.raise_exception;
413 END IF;
414 END Check_Uniqueness ;
415 PROCEDURE Check_Parent_Existance AS
416 /*************************************************************
417 Created By : shgeroge
418 Date Created By : 10-May-2000
419 Purpose :
420 Know limitations, enhancements or remarks
421 Change History
422 Who When What
423
424 (reverse chronological order - newest change first)
425 ***************************************************************/
426
427 BEGIN
428
429 IF (((old_references.uoo_id = new_references.uoo_id)) OR
430 ((new_references.uoo_id IS NULL))) THEN
431 NULL;
432 ELSIF NOT Igs_Ps_Unit_Ofr_Opt_Pkg.Get_UK_For_Validation (
433 new_references.uoo_id
434 ) THEN
435 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
436 IGS_GE_MSG_STACK.ADD;
437 App_Exception.Raise_Exception;
438 END IF;
439
440 END Check_Parent_Existance;
441
442 FUNCTION Get_PK_For_Validation (
443 x_unit_sec_credit_points_id IN NUMBER
444 ) RETURN BOOLEAN AS
445
446 /*************************************************************
447 Created By : shgeroge
448 Date Created By : 10-May-2000
449 Purpose :
450 Know limitations, enhancements or remarks
451 Change History
452 Who When What
453
454 (reverse chronological order - newest change first)
455 ***************************************************************/
456
457 CURSOR cur_rowid IS
458 SELECT rowid
459 FROM igs_ps_usec_cps
460 WHERE unit_sec_credit_points_id = x_unit_sec_credit_points_id
461 FOR UPDATE NOWAIT;
462
463 lv_rowid cur_rowid%RowType;
464
465 BEGIN
466
467 Open cur_rowid;
468 Fetch cur_rowid INTO lv_rowid;
469 IF (cur_rowid%FOUND) THEN
470 Close cur_rowid;
471 Return(TRUE);
472 ELSE
473 Close cur_rowid;
474 Return(FALSE);
475 END IF;
476 END Get_PK_For_Validation;
477
478 FUNCTION Get_UK_For_Validation (
479 x_uoo_id IN NUMBER
480 ) RETURN BOOLEAN AS
481
482 /*************************************************************
483 Created By : shgeorge
484 Date Created By : 10-May-2000
485 Purpose :
486 Know limitations, enhancements or remarks
487 Change History
488 Who When What
489
490 (reverse chronological order - newest change first)
491 ***************************************************************/
492
493 CURSOR cur_rowid IS
494 SELECT rowid
495 FROM igs_ps_usec_cps
496 WHERE uoo_id = x_uoo_id and ((l_rowid is null) or (rowid <> l_rowid))
497
498 ;
499 lv_rowid cur_rowid%RowType;
500
501 BEGIN
502
503 Open cur_rowid;
504 Fetch cur_rowid INTO lv_rowid;
505 IF (cur_rowid%FOUND) THEN
506 Close cur_rowid;
507 return (true);
508 ELSE
509 close cur_rowid;
510 return(false);
511 END IF;
512 END Get_UK_For_Validation ;
513 PROCEDURE Get_UFK_Igs_Ps_Unit_Ofr_Opt (
514 x_uoo_id IN NUMBER
515 ) AS
516
517 /*************************************************************
518 Created By : shgeorge
519 Date Created By : 10-May-2000
520 Purpose :
521 Know limitations, enhancements or remarks
522 Change History
523 Who When What
524
525 (reverse chronological order - newest change first)
526 ***************************************************************/
527
528 CURSOR cur_rowid IS
529 SELECT rowid
530 FROM igs_ps_usec_cps
531 WHERE uoo_id = x_uoo_id ;
532
533 lv_rowid cur_rowid%RowType;
534
535 BEGIN
536
537 Open cur_rowid;
538 Fetch cur_rowid INTO lv_rowid;
539 IF (cur_rowid%FOUND) THEN
540 Close cur_rowid;
541 Fnd_Message.Set_Name ('IGS', 'IGS_PS_USCP_UOO_UFK');
542 IGS_GE_MSG_STACK.ADD;
543 App_Exception.Raise_Exception;
544 Return;
545 END IF;
546 Close cur_rowid;
547
548 END Get_UFK_Igs_Ps_Unit_Ofr_Opt;
549
550 PROCEDURE Before_DML (
551 p_action IN VARCHAR2,
552 x_rowid IN VARCHAR2 DEFAULT NULL,
553 x_unit_sec_credit_points_id IN NUMBER DEFAULT NULL,
554 x_uoo_id IN NUMBER DEFAULT NULL,
555 x_minimum_credit_points IN NUMBER DEFAULT NULL,
556 x_maximum_credit_points IN NUMBER DEFAULT NULL,
557 x_variable_increment IN NUMBER DEFAULT NULL,
558 x_lecture_credit_points IN NUMBER DEFAULT NULL,
559 x_lab_credit_points IN NUMBER DEFAULT NULL,
560 x_other_credit_points IN NUMBER DEFAULT NULL,
561 x_clock_hours IN NUMBER DEFAULT NULL,
562 x_work_load_cp_lecture IN NUMBER DEFAULT NULL,
563 x_work_load_cp_lab IN NUMBER DEFAULT NULL,
564 x_continuing_education_units IN NUMBER DEFAULT NULL,
565 x_WORK_LOAD_OTHER IN NUMBER DEFAULT NULL,
566 x_CONTACT_HRS_LECTURE IN NUMBER DEFAULT NULL,
567 x_CONTACT_HRS_LAB IN NUMBER DEFAULT NULL,
568 x_CONTACT_HRS_OTHER IN NUMBER DEFAULT NULL,
569 x_NON_SCHD_REQUIRED_HRS IN NUMBER DEFAULT NULL,
570 x_EXCLUDE_FROM_MAX_CP_LIMIT IN VARCHAR2 DEFAULT NULL,
571 x_creation_date IN DATE DEFAULT NULL,
572 x_created_by IN NUMBER DEFAULT NULL,
573 x_last_update_date IN DATE DEFAULT NULL,
574 x_last_updated_by IN NUMBER DEFAULT NULL,
575 x_last_update_login IN NUMBER DEFAULT NULL,
576 x_claimable_hours IN NUMBER DEFAULT NULL,
577 x_achievable_credit_points IN NUMBER ,
578 x_enrolled_credit_points IN NUMBER ,
579 x_billing_credit_points IN NUMBER,
580 x_billing_hrs IN NUMBER
581 ) AS
582 /*************************************************************
583 Created By : shgeorge
584 Date Created By : 10-May-2000
585 Purpose :
586 Know limitations, enhancements or remarks
587 Change History
588 Who When What
589
590 (reverse chronological order - newest change first)
591 ***************************************************************/
592
593 BEGIN
594
595 Set_Column_Values (
596 p_action,
597 x_rowid,
598 x_unit_sec_credit_points_id,
599 x_uoo_id,
600 x_minimum_credit_points,
601 x_maximum_credit_points,
602 x_variable_increment,
603 x_lecture_credit_points,
604 x_lab_credit_points,
605 x_other_credit_points,
606 x_clock_hours,
607 x_work_load_cp_lecture,
608 x_work_load_cp_lab,
609 x_continuing_education_units,
610 x_work_load_other,
611 x_contact_hrs_lecture,
612 x_contact_hrs_lab,
613 x_contact_hrs_other,
614 x_non_schd_required_hrs,
615 x_exclude_from_max_cp_limit,
616 x_creation_date,
617 x_created_by,
618 x_last_update_date,
619 x_last_updated_by,
620 x_last_update_login ,
621 x_claimable_hours,
622 x_achievable_credit_points,
623 x_enrolled_credit_points ,
624 x_billing_credit_points,
625 x_billing_hrs
626 );
627
628 IF (p_action = 'INSERT') THEN
629 -- Call all the procedures related to Before Insert.
630 Null;
631 IF Get_Pk_For_Validation(
632 new_references.unit_sec_credit_points_id) THEN
633 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
634 IGS_GE_MSG_STACK.ADD;
635 App_Exception.Raise_Exception;
636 END IF;
637 Check_Uniqueness;
638 Check_Constraints;
639 Check_Parent_Existance;
640 ELSIF (p_action = 'UPDATE') THEN
641 -- Call all the procedures related to Before Update.
642 Null;
643 Check_Uniqueness;
644 Check_Constraints;
645 Check_Parent_Existance;
646 ELSIF (p_action = 'DELETE') THEN
647 -- Call all the procedures related to Before Delete.
648 Null;
649 ELSIF (p_action = 'VALIDATE_INSERT') THEN
650 -- Call all the procedures related to Before Insert.
651 IF Get_PK_For_Validation (
652 new_references.unit_sec_credit_points_id) THEN
653 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
654 IGS_GE_MSG_STACK.ADD;
655 App_Exception.Raise_Exception;
656 END IF;
657 Check_Uniqueness;
658 Check_Constraints;
659 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
660 Check_Uniqueness;
661 Check_Constraints;
662 ELSIF (p_action = 'VALIDATE_DELETE') THEN
663 Null;
664 END IF;
665
666 END Before_DML;
667
668 PROCEDURE After_DML (
669 p_action IN VARCHAR2,
670 x_rowid IN VARCHAR2
671 ) IS
672 /*************************************************************
673 Created By : shgeorge
674 Date Created By : 10-May-2000
675 Purpose :
676 Know limitations, enhancements or remarks
677 Change History
678 Who When What
679
680 (reverse chronological order - newest change first)
681 ***************************************************************/
682
683 BEGIN
684
685 l_rowid := x_rowid;
686
687 IF (p_action = 'INSERT') THEN
688 -- Call all the procedures related to After Insert.
689 AfterRowUpdate1 ( p_inserting => TRUE );
690 ELSIF (p_action = 'UPDATE') THEN
691 -- Call all the procedures related to After Update.
692 AfterRowUpdate1 ( p_updating => TRUE );
693 ELSIF (p_action = 'DELETE') THEN
694 -- Call all the procedures related to After Delete.
695 Null;
696 END IF;
697
698 l_rowid := null;
699 END After_DML;
700
701 procedure INSERT_ROW (
702 X_ROWID in out NOCOPY VARCHAR2,
703 x_UNIT_SEC_CREDIT_POINTS_ID IN OUT NOCOPY NUMBER,
704 x_UOO_ID IN NUMBER,
705 x_MINIMUM_CREDIT_POINTS IN NUMBER,
706 x_MAXIMUM_CREDIT_POINTS IN NUMBER,
707 x_VARIABLE_INCREMENT IN NUMBER,
708 x_LECTURE_CREDIT_POINTS IN NUMBER,
709 x_LAB_CREDIT_POINTS IN NUMBER,
710 x_OTHER_CREDIT_POINTS IN NUMBER,
711 x_CLOCK_HOURS IN NUMBER,
712 x_WORK_LOAD_CP_LECTURE IN NUMBER,
713 x_WORK_LOAD_CP_LAB IN NUMBER,
714 x_CONTINUING_EDUCATION_UNITS IN NUMBER,
715 x_WORK_LOAD_OTHER IN NUMBER DEFAULT NULL,
716 x_CONTACT_HRS_LECTURE IN NUMBER DEFAULT NULL,
717 x_CONTACT_HRS_LAB IN NUMBER DEFAULT NULL,
718 x_CONTACT_HRS_OTHER IN NUMBER DEFAULT NULL,
719 x_NON_SCHD_REQUIRED_HRS IN NUMBER DEFAULT NULL,
720 x_EXCLUDE_FROM_MAX_CP_LIMIT IN VARCHAR2 DEFAULT NULL,
721 X_MODE in VARCHAR2 default 'R' ,
722 x_claimable_hours IN NUMBER DEFAULT NULL,
723 x_achievable_credit_points IN NUMBER ,
724 x_enrolled_credit_points IN NUMBER ,
725 x_billing_credit_points IN NUMBER,
726 x_billing_hrs IN NUMBER
727 ) AS
728 /*************************************************************
729 Created By : shgeorge
730 Date Created By : 10-May-2000
731 Purpose :
732 Know limitations, enhancements or remarks
733 Change History
734 Who When What
735
736 (reverse chronological order - newest change first)
737 ***************************************************************/
738
739 cursor C is select ROWID from IGS_PS_USEC_CPS
740 where UNIT_SEC_CREDIT_POINTS_ID= X_UNIT_SEC_CREDIT_POINTS_ID
741 ;
742 X_LAST_UPDATE_DATE DATE ;
743 X_LAST_UPDATED_BY NUMBER ;
744 X_LAST_UPDATE_LOGIN NUMBER ;
745 begin
746 X_LAST_UPDATE_DATE := SYSDATE;
747 if(X_MODE = 'I') then
748 X_LAST_UPDATED_BY := 1;
749 X_LAST_UPDATE_LOGIN := 0;
750 elsif (X_MODE = 'R') then
751 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
752 if X_LAST_UPDATED_BY is NULL then
753 X_LAST_UPDATED_BY := -1;
754 end if;
755 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
756 if X_LAST_UPDATE_LOGIN is NULL then
757 X_LAST_UPDATE_LOGIN := -1;
758 end if;
759 else
760 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
761 IGS_GE_MSG_STACK.ADD;
762 app_exception.raise_exception;
763 end if;
764 SELECT
765 IGS_PS_USEC_CPS_S.NextVal
766 INTO
767 x_UNIT_SEC_CREDIT_POINTS_ID
768 FROM
769 dual;
770 Before_DML(
771 p_action=>'INSERT',
772 x_rowid=>X_ROWID,
773 x_unit_sec_credit_points_id=>X_UNIT_SEC_CREDIT_POINTS_ID,
774 x_uoo_id=>X_UOO_ID,
775 x_minimum_credit_points=>X_MINIMUM_CREDIT_POINTS,
776 x_maximum_credit_points=>X_MAXIMUM_CREDIT_POINTS,
777 x_variable_increment=>X_VARIABLE_INCREMENT,
778 x_lecture_credit_points=>X_LECTURE_CREDIT_POINTS,
779 x_lab_credit_points=>X_LAB_CREDIT_POINTS,
780 x_other_credit_points=>X_OTHER_CREDIT_POINTS,
781 x_clock_hours=>X_CLOCK_HOURS,
782 x_work_load_cp_lecture=>X_WORK_LOAD_CP_LECTURE,
783 x_work_load_cp_lab=>X_WORK_LOAD_CP_LAB,
784 x_continuing_education_units=>X_CONTINUING_EDUCATION_UNITS,
785 x_work_load_other => X_WORK_LOAD_OTHER,
786 x_contact_hrs_lecture => X_CONTACT_HRS_LECTURE,
787 x_contact_hrs_lab => X_CONTACT_HRS_LAB,
788 x_contact_hrs_other => X_CONTACT_HRS_OTHER,
789 x_non_schd_required_hrs => X_NON_SCHD_REQUIRED_HRS,
790 x_exclude_from_max_cp_limit => X_EXCLUDE_FROM_MAX_CP_LIMIT,
791 x_creation_date=>X_LAST_UPDATE_DATE,
792 x_created_by=>X_LAST_UPDATED_BY,
793 x_last_update_date=>X_LAST_UPDATE_DATE,
794 x_last_updated_by=>X_LAST_UPDATED_BY,
795 x_last_update_login=>X_LAST_UPDATE_LOGIN,
796 x_claimable_hours => x_claimable_hours,
797 x_achievable_credit_points => x_achievable_credit_points,
798 x_enrolled_credit_points => x_enrolled_credit_points,
799 x_billing_credit_points => x_billing_credit_points,
800 x_billing_hrs => x_billing_hrs
801 );
802
803 insert into IGS_PS_USEC_CPS (
804 UNIT_SEC_CREDIT_POINTS_ID
805 ,UOO_ID
806 ,MINIMUM_CREDIT_POINTS
807 ,MAXIMUM_CREDIT_POINTS
808 ,VARIABLE_INCREMENT
809 ,LECTURE_CREDIT_POINTS
810 ,LAB_CREDIT_POINTS
811 ,OTHER_CREDIT_POINTS
812 ,CLOCK_HOURS
813 ,WORK_LOAD_CP_LECTURE
814 ,WORK_LOAD_CP_LAB
815 ,CONTINUING_EDUCATION_UNITS
816 ,WORK_LOAD_OTHER
817 ,CONTACT_HRS_LECTURE
818 ,CONTACT_HRS_LAB
819 ,CONTACT_HRS_OTHER
820 ,NON_SCHD_REQUIRED_HRS
821 ,EXCLUDE_FROM_MAX_CP_LIMIT
822 ,CREATION_DATE
823 ,CREATED_BY
824 ,LAST_UPDATE_DATE
825 ,LAST_UPDATED_BY
826 ,LAST_UPDATE_LOGIN
827 ,claimable_hours
828 ,achievable_credit_points
829 ,enrolled_credit_points
830 ,billing_credit_points
831 ,billing_hrs
832 ) values (
833 NEW_REFERENCES.UNIT_SEC_CREDIT_POINTS_ID
834 ,NEW_REFERENCES.UOO_ID
835 ,NEW_REFERENCES.MINIMUM_CREDIT_POINTS
836 ,NEW_REFERENCES.MAXIMUM_CREDIT_POINTS
837 ,NEW_REFERENCES.VARIABLE_INCREMENT
838 ,NEW_REFERENCES.LECTURE_CREDIT_POINTS
839 ,NEW_REFERENCES.LAB_CREDIT_POINTS
840 ,NEW_REFERENCES.OTHER_CREDIT_POINTS
841 ,NEW_REFERENCES.CLOCK_HOURS
842 ,NEW_REFERENCES.WORK_LOAD_CP_LECTURE
843 ,NEW_REFERENCES.WORK_LOAD_CP_LAB
844 ,NEW_REFERENCES.CONTINUING_EDUCATION_UNITS
845 ,NEW_REFERENCES.WORK_LOAD_OTHER
846 ,NEW_REFERENCES.CONTACT_HRS_LECTURE
847 ,NEW_REFERENCES.CONTACT_HRS_LAB
848 ,NEW_REFERENCES.CONTACT_HRS_OTHER
849 ,NEW_REFERENCES.NON_SCHD_REQUIRED_HRS
850 ,NEW_REFERENCES.EXCLUDE_FROM_MAX_CP_LIMIT
851 ,X_LAST_UPDATE_DATE
852 ,X_LAST_UPDATED_BY
853 ,X_LAST_UPDATE_DATE
854 ,X_LAST_UPDATED_BY
855 ,X_LAST_UPDATE_LOGIN
856 ,new_references.claimable_hours
857 ,new_references.achievable_credit_points
858 ,new_references.enrolled_credit_points
859 ,new_references.billing_credit_points
860 ,new_references.billing_hrs);
861 open c;
862 fetch c into X_ROWID;
863 if (c%notfound) then
864 close c;
865 raise no_data_found;
866 end if;
867 close c;
868 After_DML (
869 p_action => 'INSERT' ,
870 x_rowid => X_ROWID );
871 end INSERT_ROW;
872
873 procedure LOCK_ROW (
874 X_ROWID in VARCHAR2,
875 x_UNIT_SEC_CREDIT_POINTS_ID IN NUMBER,
876 x_UOO_ID IN NUMBER,
877 x_MINIMUM_CREDIT_POINTS IN NUMBER,
878 x_MAXIMUM_CREDIT_POINTS IN NUMBER,
879 x_VARIABLE_INCREMENT IN NUMBER,
880 x_LECTURE_CREDIT_POINTS IN NUMBER,
881 x_LAB_CREDIT_POINTS IN NUMBER,
882 x_OTHER_CREDIT_POINTS IN NUMBER,
883 x_CLOCK_HOURS IN NUMBER,
884 x_WORK_LOAD_CP_LECTURE IN NUMBER,
885 x_WORK_LOAD_CP_LAB IN NUMBER,
886 x_CONTINUING_EDUCATION_UNITS IN NUMBER,
887 x_WORK_LOAD_OTHER IN NUMBER DEFAULT NULL,
888 x_CONTACT_HRS_LECTURE IN NUMBER DEFAULT NULL,
889 x_CONTACT_HRS_LAB IN NUMBER DEFAULT NULL,
890 x_CONTACT_HRS_OTHER IN NUMBER DEFAULT NULL,
891 x_NON_SCHD_REQUIRED_HRS IN NUMBER DEFAULT NULL,
892 x_EXCLUDE_FROM_MAX_CP_LIMIT IN VARCHAR2 DEFAULT NULL,
893 x_claimable_hours IN NUMBER DEFAULT NULL,
894 x_achievable_credit_points IN NUMBER ,
895 x_enrolled_credit_points IN NUMBER ,
896 x_billing_credit_points IN NUMBER,
897 x_billing_hrs IN NUMBER ) AS
898 /*************************************************************
899 Created By : shgeorge
900 Date Created By : 10-May-2000
901 Purpose :
902 Know limitations, enhancements or remarks
903 Change History
904 Who When What
905
906 (reverse chronological order - newest change first)
907 ***************************************************************/
908
909 cursor c1 is select
910 UOO_ID
911 , MINIMUM_CREDIT_POINTS
912 , MAXIMUM_CREDIT_POINTS
913 , VARIABLE_INCREMENT
914 , LECTURE_CREDIT_POINTS
915 , LAB_CREDIT_POINTS
916 , OTHER_CREDIT_POINTS
917 , CLOCK_HOURS
918 , WORK_LOAD_CP_LECTURE
919 , WORK_LOAD_CP_LAB
920 , CONTINUING_EDUCATION_UNITS
921 , WORK_LOAD_OTHER
922 , CONTACT_HRS_LECTURE
923 , CONTACT_HRS_LAB
924 , CONTACT_HRS_OTHER
925 , NON_SCHD_REQUIRED_HRS
926 , EXCLUDE_FROM_MAX_CP_LIMIT
927 , claimable_hours
928 , achievable_credit_points
929 , enrolled_credit_points
930 , billing_credit_points
931 , billing_hrs
932 from IGS_PS_USEC_CPS
933 where ROWID = X_ROWID
934 for update nowait;
935 tlinfo c1%rowtype;
936 begin
937 open c1;
938 fetch c1 into tlinfo;
939 if (c1%notfound) then
940 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
941 IGS_GE_MSG_STACK.ADD;
942 close c1;
943 app_exception.raise_exception;
944 return;
945 end if;
946 close c1;
947 if ( ( tlinfo.UOO_ID = X_UOO_ID)
948 AND ((tlinfo.MINIMUM_CREDIT_POINTS = X_MINIMUM_CREDIT_POINTS)
949 OR ((tlinfo.MINIMUM_CREDIT_POINTS is null)
950 AND (X_MINIMUM_CREDIT_POINTS is null)))
951 AND ((tlinfo.MAXIMUM_CREDIT_POINTS = X_MAXIMUM_CREDIT_POINTS)
952 OR ((tlinfo.MAXIMUM_CREDIT_POINTS is null)
953 AND (X_MAXIMUM_CREDIT_POINTS is null)))
954 AND ((tlinfo.VARIABLE_INCREMENT = X_VARIABLE_INCREMENT)
955 OR ((tlinfo.VARIABLE_INCREMENT is null)
956 AND (X_VARIABLE_INCREMENT is null)))
957 AND ((tlinfo.LECTURE_CREDIT_POINTS = X_LECTURE_CREDIT_POINTS)
958 OR ((tlinfo.LECTURE_CREDIT_POINTS is null)
959 AND (X_LECTURE_CREDIT_POINTS is null)))
960 AND ((tlinfo.LAB_CREDIT_POINTS = X_LAB_CREDIT_POINTS)
961 OR ((tlinfo.LAB_CREDIT_POINTS is null)
962 AND (X_LAB_CREDIT_POINTS is null)))
963 AND ((tlinfo.OTHER_CREDIT_POINTS = X_OTHER_CREDIT_POINTS)
964 OR ((tlinfo.OTHER_CREDIT_POINTS is null)
965 AND (X_OTHER_CREDIT_POINTS is null)))
966 AND ((tlinfo.CLOCK_HOURS = X_CLOCK_HOURS)
967 OR ((tlinfo.CLOCK_HOURS is null)
968 AND (X_CLOCK_HOURS is null)))
969 AND ((tlinfo.WORK_LOAD_CP_LECTURE = X_WORK_LOAD_CP_LECTURE)
970 OR ((tlinfo.WORK_LOAD_CP_LECTURE is null)
971 AND (X_WORK_LOAD_CP_LECTURE is null)))
972 AND ((tlinfo.WORK_LOAD_CP_LAB = X_WORK_LOAD_CP_LAB)
973 OR ((tlinfo.WORK_LOAD_CP_LAB is null)
974 AND (X_WORK_LOAD_CP_LAB is null)))
975 AND ((tlinfo.CONTINUING_EDUCATION_UNITS = X_CONTINUING_EDUCATION_UNITS)
976 OR ((tlinfo.CONTINUING_EDUCATION_UNITS is null)
977 AND (X_CONTINUING_EDUCATION_UNITS is null)))
978 AND ((tlinfo.WORK_LOAD_OTHER = X_WORK_LOAD_OTHER)
979 OR ((tlinfo.WORK_LOAD_OTHER is null)
980 AND (X_WORK_LOAD_OTHER is null)))
981 AND ((tlinfo.CONTACT_HRS_LECTURE = X_CONTACT_HRS_LECTURE)
982 OR ((tlinfo.CONTACT_HRS_LECTURE is null)
983 AND (X_CONTACT_HRS_LECTURE is null)))
984 AND ((tlinfo.CONTACT_HRS_LAB = X_CONTACT_HRS_LAB)
985 OR ((tlinfo.CONTACT_HRS_LAB is null)
986 AND (X_CONTACT_HRS_LAB is null)))
987 AND ((tlinfo.CONTACT_HRS_OTHER = X_CONTACT_HRS_OTHER)
988 OR ((tlinfo.CONTACT_HRS_OTHER is null)
989 AND (X_CONTACT_HRS_OTHER is null)))
990 AND ((tlinfo.NON_SCHD_REQUIRED_HRS = X_NON_SCHD_REQUIRED_HRS)
991 OR ((tlinfo.NON_SCHD_REQUIRED_HRS is null)
992 AND (X_NON_SCHD_REQUIRED_HRS is null)))
993 AND ((tlinfo.EXCLUDE_FROM_MAX_CP_LIMIT = X_EXCLUDE_FROM_MAX_CP_LIMIT)
994 OR ((tlinfo.EXCLUDE_FROM_MAX_CP_LIMIT is null)
995 AND (X_EXCLUDE_FROM_MAX_CP_LIMIT is null)))
996 AND ((tlinfo.claimable_hours = x_claimable_hours)
997 OR ((tlinfo.claimable_hours is null)
998 AND (x_claimable_hours is null)))
999 AND ((tlinfo.achievable_credit_points = x_achievable_credit_points)
1000 OR ((tlinfo.achievable_credit_points is null)
1001 AND (x_achievable_credit_points is null)))
1002 AND ((tlinfo.enrolled_credit_points = x_enrolled_credit_points)
1003 OR ((tlinfo.enrolled_credit_points is null)
1004 AND (x_enrolled_credit_points is null)))
1005 AND ((tlinfo.billing_credit_points = x_billing_credit_points)
1006 OR ((tlinfo.billing_credit_points is null)
1007 AND (x_billing_credit_points is null)))
1008 AND ((tlinfo.BILLING_HRS= X_BILLING_HRS)
1009 OR ((tlinfo.BILLING_HRS IS NULL)
1010 AND (X_BILLING_HRS IS NULL)))
1011 ) then
1012 null;
1013 else
1014 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1015 IGS_GE_MSG_STACK.ADD;
1016 app_exception.raise_exception;
1017 end if;
1018 return;
1019 end LOCK_ROW;
1020
1021 Procedure UPDATE_ROW (
1022 X_ROWID in VARCHAR2,
1023 x_UNIT_SEC_CREDIT_POINTS_ID IN NUMBER,
1024 x_UOO_ID IN NUMBER,
1025 x_MINIMUM_CREDIT_POINTS IN NUMBER,
1026 x_MAXIMUM_CREDIT_POINTS IN NUMBER,
1027 x_VARIABLE_INCREMENT IN NUMBER,
1028 x_LECTURE_CREDIT_POINTS IN NUMBER,
1029 x_LAB_CREDIT_POINTS IN NUMBER,
1030 x_OTHER_CREDIT_POINTS IN NUMBER,
1031 x_CLOCK_HOURS IN NUMBER,
1032 x_WORK_LOAD_CP_LECTURE IN NUMBER,
1033 x_WORK_LOAD_CP_LAB IN NUMBER,
1034 x_CONTINUING_EDUCATION_UNITS IN NUMBER,
1035 x_WORK_LOAD_OTHER IN NUMBER DEFAULT NULL,
1036 x_CONTACT_HRS_LECTURE IN NUMBER DEFAULT NULL,
1037 x_CONTACT_HRS_LAB IN NUMBER DEFAULT NULL,
1038 x_CONTACT_HRS_OTHER IN NUMBER DEFAULT NULL,
1039 x_NON_SCHD_REQUIRED_HRS IN NUMBER DEFAULT NULL,
1040 x_EXCLUDE_FROM_MAX_CP_LIMIT IN VARCHAR2 DEFAULT NULL,
1041 X_MODE in VARCHAR2 default 'R',
1042 x_claimable_hours IN NUMBER DEFAULT NULL,
1043 x_achievable_credit_points IN NUMBER,
1044 x_enrolled_credit_points IN NUMBER ,
1045 x_billing_credit_points IN NUMBER ,
1046 x_billing_hrs IN NUMBER
1047 ) AS
1048 /*************************************************************
1049 Created By : shgeorge
1050 Date Created By : 10-May-2000
1051 Purpose :
1052 Know limitations, enhancements or remarks
1053 Change History
1054 Who When What
1055
1056 (reverse chronological order - newest change first)
1057 ***************************************************************/
1058
1059 X_LAST_UPDATE_DATE DATE ;
1060 X_LAST_UPDATED_BY NUMBER ;
1061 X_LAST_UPDATE_LOGIN NUMBER ;
1062 begin
1063 X_LAST_UPDATE_DATE := SYSDATE;
1064 if(X_MODE = 'I') then
1065 X_LAST_UPDATED_BY := 1;
1066 X_LAST_UPDATE_LOGIN := 0;
1067 elsif (X_MODE = 'R') then
1068 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1069 if X_LAST_UPDATED_BY is NULL then
1070 X_LAST_UPDATED_BY := -1;
1071 end if;
1072 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1073 if X_LAST_UPDATE_LOGIN is NULL then
1074 X_LAST_UPDATE_LOGIN := -1;
1075 end if;
1076 else
1077 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1078 IGS_GE_MSG_STACK.ADD;
1079 app_exception.raise_exception;
1080 end if;
1081 Before_DML(
1082 p_action=>'UPDATE',
1083 x_rowid=>X_ROWID,
1084 x_unit_sec_credit_points_id=>X_UNIT_SEC_CREDIT_POINTS_ID,
1085 x_uoo_id=>X_UOO_ID,
1086 x_minimum_credit_points=>X_MINIMUM_CREDIT_POINTS,
1087 x_maximum_credit_points=>X_MAXIMUM_CREDIT_POINTS,
1088 x_variable_increment=>X_VARIABLE_INCREMENT,
1089 x_lecture_credit_points=>X_LECTURE_CREDIT_POINTS,
1090 x_lab_credit_points=>X_LAB_CREDIT_POINTS,
1091 x_other_credit_points=>X_OTHER_CREDIT_POINTS,
1092 x_clock_hours=>X_CLOCK_HOURS,
1093 x_work_load_cp_lecture=>X_WORK_LOAD_CP_LECTURE,
1094 x_work_load_cp_lab=>X_WORK_LOAD_CP_LAB,
1095 x_continuing_education_units=>X_CONTINUING_EDUCATION_UNITS,
1096 x_work_load_other => X_WORK_LOAD_OTHER,
1097 x_contact_hrs_lecture => X_CONTACT_HRS_LECTURE,
1098 x_contact_hrs_lab => X_CONTACT_HRS_LAB,
1099 x_contact_hrs_other => X_CONTACT_HRS_OTHER,
1100 x_non_schd_required_hrs => X_NON_SCHD_REQUIRED_HRS,
1101 x_exclude_from_max_cp_limit => X_EXCLUDE_FROM_MAX_CP_LIMIT,
1102 x_creation_date=>X_LAST_UPDATE_DATE,
1103 x_created_by=>X_LAST_UPDATED_BY,
1104 x_last_update_date=>X_LAST_UPDATE_DATE,
1105 x_last_updated_by=>X_LAST_UPDATED_BY,
1106 X_last_update_login=>X_LAST_UPDATE_LOGIN,
1107 x_claimable_hours=>x_claimable_hours,
1108 x_achievable_credit_points=>x_achievable_credit_points,
1109 x_enrolled_credit_points=>x_enrolled_credit_points,
1110 x_billing_credit_points =>x_billing_credit_points,
1111 x_billing_hrs => x_billing_hrs
1112 );
1113
1114
1115 update IGS_PS_USEC_CPS set
1116 UOO_ID = NEW_REFERENCES.UOO_ID,
1117 MINIMUM_CREDIT_POINTS = NEW_REFERENCES.MINIMUM_CREDIT_POINTS,
1118 MAXIMUM_CREDIT_POINTS = NEW_REFERENCES.MAXIMUM_CREDIT_POINTS,
1119 VARIABLE_INCREMENT = NEW_REFERENCES.VARIABLE_INCREMENT,
1120 LECTURE_CREDIT_POINTS = NEW_REFERENCES.LECTURE_CREDIT_POINTS,
1121 LAB_CREDIT_POINTS = NEW_REFERENCES.LAB_CREDIT_POINTS,
1122 OTHER_CREDIT_POINTS = NEW_REFERENCES.OTHER_CREDIT_POINTS,
1123 CLOCK_HOURS = NEW_REFERENCES.CLOCK_HOURS,
1124 WORK_LOAD_CP_LECTURE = NEW_REFERENCES.WORK_LOAD_CP_LECTURE,
1125 WORK_LOAD_CP_LAB = NEW_REFERENCES.WORK_LOAD_CP_LAB,
1126 CONTINUING_EDUCATION_UNITS = NEW_REFERENCES.CONTINUING_EDUCATION_UNITS,
1127 WORK_LOAD_OTHER = NEW_REFERENCES.WORK_LOAD_OTHER,
1128 CONTACT_HRS_LECTURE = NEW_REFERENCES.CONTACT_HRS_LECTURE,
1129 CONTACT_HRS_LAB = NEW_REFERENCES.CONTACT_HRS_LAB,
1130 CONTACT_HRS_OTHER = NEW_REFERENCES.CONTACT_HRS_OTHER,
1131 NON_SCHD_REQUIRED_HRS = NEW_REFERENCES.NON_SCHD_REQUIRED_HRS,
1132 EXCLUDE_FROM_MAX_CP_LIMIT = NEW_REFERENCES.EXCLUDE_FROM_MAX_CP_LIMIT,
1133 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1134 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1135 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1136 claimable_hours = new_references.claimable_hours,
1137 achievable_credit_points = new_references.achievable_credit_points,
1138 enrolled_credit_points = new_references.enrolled_credit_points ,
1139 billing_credit_points = new_references.billing_credit_points,
1140 billing_hrs = new_references.billing_hrs
1141 where ROWID = X_ROWID;
1142 if (sql%notfound) then
1143 raise no_data_found;
1144 end if;
1145
1146 After_DML (
1147 p_action => 'UPDATE' ,
1148 x_rowid => X_ROWID
1149 );
1150 end UPDATE_ROW;
1151
1152 procedure ADD_ROW (
1153 X_ROWID in out NOCOPY VARCHAR2,
1154 x_UNIT_SEC_CREDIT_POINTS_ID IN OUT NOCOPY NUMBER,
1155 x_UOO_ID IN NUMBER,
1156 x_MINIMUM_CREDIT_POINTS IN NUMBER,
1157 x_MAXIMUM_CREDIT_POINTS IN NUMBER,
1158 x_VARIABLE_INCREMENT IN NUMBER,
1159 x_LECTURE_CREDIT_POINTS IN NUMBER,
1160 x_LAB_CREDIT_POINTS IN NUMBER,
1161 x_OTHER_CREDIT_POINTS IN NUMBER,
1162 x_CLOCK_HOURS IN NUMBER,
1163 x_WORK_LOAD_CP_LECTURE IN NUMBER,
1164 x_WORK_LOAD_CP_LAB IN NUMBER,
1165 x_CONTINUING_EDUCATION_UNITS IN NUMBER,
1166 x_WORK_LOAD_OTHER IN NUMBER DEFAULT NULL,
1167 x_CONTACT_HRS_LECTURE IN NUMBER DEFAULT NULL,
1168 x_CONTACT_HRS_LAB IN NUMBER DEFAULT NULL,
1169 x_CONTACT_HRS_OTHER IN NUMBER DEFAULT NULL,
1170 x_NON_SCHD_REQUIRED_HRS IN NUMBER DEFAULT NULL,
1171 x_EXCLUDE_FROM_MAX_CP_LIMIT IN VARCHAR2 DEFAULT NULL,
1172 X_MODE in VARCHAR2 default 'R' ,
1173 x_claimable_hours IN NUMBER DEFAULT NULL,
1174 x_achievable_credit_points IN NUMBER,
1175 x_enrolled_credit_points IN NUMBER ,
1176 x_billing_credit_points IN NUMBER,
1177 x_billing_hrs IN NUMBER
1178 ) AS
1179 /*************************************************************
1180 Created By : shgeorge
1181 Date Created By : 10-May-2000
1182 Purpose :
1183 Know limitations, enhancements or remarks
1184 Change History
1185 Who When What
1186
1187 (reverse chronological order - newest change first)
1188 ***************************************************************/
1189
1190 cursor c1 is select ROWID from IGS_PS_USEC_CPS
1191 where UNIT_SEC_CREDIT_POINTS_ID= X_UNIT_SEC_CREDIT_POINTS_ID
1192 ;
1193 begin
1194 open c1;
1195 fetch c1 into X_ROWID;
1196 if (c1%notfound) then
1197 close c1;
1198 INSERT_ROW (
1199 X_ROWID,
1200 X_UNIT_SEC_CREDIT_POINTS_ID,
1201 X_UOO_ID,
1202 X_MINIMUM_CREDIT_POINTS,
1203 X_MAXIMUM_CREDIT_POINTS,
1204 X_VARIABLE_INCREMENT,
1205 X_LECTURE_CREDIT_POINTS,
1206 X_LAB_CREDIT_POINTS,
1207 X_OTHER_CREDIT_POINTS,
1208 X_CLOCK_HOURS,
1209 X_WORK_LOAD_CP_LECTURE,
1210 X_WORK_LOAD_CP_LAB,
1211 X_CONTINUING_EDUCATION_UNITS,
1212 x_WORK_LOAD_OTHER ,
1213 x_CONTACT_HRS_LECTURE ,
1214 x_CONTACT_HRS_LAB ,
1215 x_CONTACT_HRS_OTHER ,
1216 x_NON_SCHD_REQUIRED_HRS ,
1217 x_EXCLUDE_FROM_MAX_CP_LIMIT ,
1218 X_MODE,
1219 x_claimable_hours,
1220 x_achievable_credit_points,
1221 x_enrolled_credit_points,
1222 x_billing_credit_points,
1223 x_billing_hrs );
1224 return;
1225 end if;
1226 close c1;
1227 UPDATE_ROW (
1228 X_ROWID,
1229 X_UNIT_SEC_CREDIT_POINTS_ID,
1230 X_UOO_ID,
1231 X_MINIMUM_CREDIT_POINTS,
1232 X_MAXIMUM_CREDIT_POINTS,
1233 X_VARIABLE_INCREMENT,
1234 X_LECTURE_CREDIT_POINTS,
1235 X_LAB_CREDIT_POINTS,
1236 X_OTHER_CREDIT_POINTS,
1237 X_CLOCK_HOURS,
1238 X_WORK_LOAD_CP_LECTURE,
1239 X_WORK_LOAD_CP_LAB,
1240 X_CONTINUING_EDUCATION_UNITS,
1241 x_WORK_LOAD_OTHER ,
1242 x_CONTACT_HRS_LECTURE ,
1243 x_CONTACT_HRS_LAB ,
1244 x_CONTACT_HRS_OTHER ,
1245 x_NON_SCHD_REQUIRED_HRS ,
1246 x_EXCLUDE_FROM_MAX_CP_LIMIT ,
1247 X_MODE,
1248 x_claimable_hours,
1249 x_achievable_credit_points,
1250 x_enrolled_credit_points,
1251 x_billing_credit_points,
1252 x_billing_hrs);
1253 end ADD_ROW;
1254
1255 procedure DELETE_ROW (
1256 X_ROWID in VARCHAR2
1257 ) AS
1258 /*************************************************************
1259 Created By : shgeorge
1260 Date Created By : 10-May-2000
1261 Purpose :
1262 Know limitations, enhancements or remarks
1263 Change History
1264 Who When What
1265
1266 (reverse chronological order - newest change first)
1267 ***************************************************************/
1268
1269 begin
1270 Before_DML (
1271 p_action => 'DELETE',
1272 x_rowid => X_ROWID
1273 );
1274 delete from IGS_PS_USEC_CPS
1275 where ROWID = X_ROWID;
1276 if (sql%notfound) then
1277 raise no_data_found;
1278 end if;
1279 After_DML (
1280 p_action => 'DELETE',
1281 x_rowid => X_ROWID
1282 );
1283 END DELETE_ROW;
1284 END igs_ps_usec_cps_pkg;
[Home] [Help]