[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_SBMAO_FN_UITT_PKG
Source
1 package body IGS_AD_SBMAO_FN_UITT_PKG as
2 /* $Header: IGSAI62B.pls 115.6 2003/10/30 13:21:20 rghosh ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AD_SBMAO_FN_UITT%RowType;
5 new_references IGS_AD_SBMAO_FN_UITT%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_submission_yr IN NUMBER DEFAULT NULL,
11 x_submission_number IN NUMBER DEFAULT NULL,
12 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
13 x_ou_start_dt IN DATE DEFAULT NULL,
14 x_funding_source IN VARCHAR2 DEFAULT NULL,
15 x_unit_int_course_level_cd IN VARCHAR2 DEFAULT NULL,
16 x_intake_target_type IN VARCHAR2 DEFAULT NULL,
17 x_target IN NUMBER DEFAULT NULL,
18 x_max_target IN NUMBER DEFAULT NULL,
19 x_override_s_amount_type IN VARCHAR2 DEFAULT NULL,
20 x_actual_enrolment IN NUMBER DEFAULT NULL,
21 x_actual_enr_effective_dt IN DATE DEFAULT NULL,
22 x_creation_date IN DATE DEFAULT NULL,
23 x_created_by IN NUMBER DEFAULT NULL,
24 x_last_update_date IN DATE DEFAULT NULL,
25 x_last_updated_by IN NUMBER DEFAULT NULL,
26 x_last_update_login IN NUMBER DEFAULT NULL
27 ) AS
28
29 CURSOR cur_old_ref_values IS
30 SELECT *
31 FROM IGS_AD_SBMAO_FN_UITT
32 WHERE rowid = x_rowid;
33
34 BEGIN
35
36 l_rowid := x_rowid;
37
38 -- Code for setting the Old and New Reference Values.
39 -- Populate Old Values.
40 Open cur_old_ref_values;
41 Fetch cur_old_ref_values INTO old_references;
42 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
43 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
44 IGS_GE_MSG_STACK.ADD;
45 App_Exception.Raise_Exception;
46 Close cur_old_ref_values;
47 Return;
48 END IF;
49 Close cur_old_ref_values;
50
51 -- Populate New Values.
52 new_references.submission_yr := x_submission_yr;
53 new_references.submission_number := x_submission_number;
54 new_references.org_unit_cd := x_org_unit_cd;
55 new_references.ou_start_dt := TRUNC(x_ou_start_dt);
56 new_references.funding_source := x_funding_source;
57 new_references.unit_int_course_level_cd := x_unit_int_course_level_cd;
58 new_references.intake_target_type := x_intake_target_type;
59 new_references.target := x_target;
60 new_references.max_target := x_max_target;
61 new_references.override_s_amount_type := x_override_s_amount_type;
62 new_references.actual_enrolment := x_actual_enrolment;
63 new_references.actual_enr_effective_dt := TRUNC(x_actual_enr_effective_dt);
64 IF (p_action = 'UPDATE') THEN
65 new_references.creation_date := old_references.creation_date;
66 new_references.created_by := old_references.created_by;
67 ELSE
68 new_references.creation_date := x_creation_date;
69 new_references.created_by := x_created_by;
70 END IF;
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 PROCEDURE BeforeRowInsertUpdate1(
78 p_inserting IN BOOLEAN DEFAULT FALSE,
79 p_updating IN BOOLEAN DEFAULT FALSE,
80 p_deleting IN BOOLEAN DEFAULT FALSE
81 ) AS
82 v_message_name VARCHAR2(30);
83 v_s_amount_type IGS_AD_INTAK_TRG_TYP.s_amount_type%TYPE;
84 BEGIN
85 -- Validate Funding Source closed ind.
86 IF p_inserting OR
87 (old_references.funding_source <> new_references.funding_source) THEN
88 IF IGS_AD_VAL_SAFT.crsp_val_fs_closed(
89 new_references.funding_source,
90 v_message_name) = FALSE THEN
91 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
92 FND_MESSAGE.SET_NAME('IGS',v_message_name);
93 IGS_GE_MSG_STACK.ADD;
94 APP_EXCEPTION.RAISE_EXCEPTION;
95 END IF;
96 END IF;
97 -- Validate org IGS_PS_UNIT is active and at the local IGS_OR_INSTITUTION.
98 IF p_inserting OR
99 (old_references.org_unit_cd <> new_references.org_unit_cd) OR
100 (TRUNC(old_references.ou_start_dt) <> new_references.ou_start_dt) THEN
101 IF IGS_AD_VAL_SAFT.admp_val_trgt_aou(
102 new_references.org_unit_cd,
103 new_references.ou_start_dt,
104 v_message_name) = FALSE THEN
105 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
106 FND_MESSAGE.SET_NAME('IGS',v_message_name);
107 IGS_GE_MSG_STACK.ADD;
108 APP_EXCEPTION.RAISE_EXCEPTION;
109 END IF;
110 END IF;
111 -- Validate Intake target Type closed ind.
112 IF p_inserting OR
113 (old_references.unit_int_course_level_cd <> new_references.unit_int_course_level_cd) THEN
114 IF IGS_AD_VAL_SAFT.crsp_val_uicl_closed(
115 new_references.unit_int_course_level_cd,
116 v_message_name) = FALSE THEN
117 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
118 FND_MESSAGE.SET_NAME('IGS',v_message_name);
119 IGS_GE_MSG_STACK.ADD;
120 APP_EXCEPTION.RAISE_EXCEPTION;
121 END IF;
122 END IF;
123 -- Validate Intake target Type closed ind.
124 IF p_inserting OR
125 (old_references.intake_target_type <> new_references.intake_target_type) THEN
126 IF IGS_AD_VAL_SIT.admp_val_itt_closed(
127 new_references.intake_target_type,
128 v_message_name) = FALSE THEN
129 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
130 FND_MESSAGE.SET_NAME('IGS',v_message_name);
131 IGS_GE_MSG_STACK.ADD;
132 APP_EXCEPTION.RAISE_EXCEPTION;
133 END IF;
134 END IF;
135 -- Validate override system amount type does not equal the system amount type
136 -- of the intake target type
137 IF p_inserting OR
138 (NVL(old_references.override_s_amount_type, 'NULL') <>
139 NVL(new_references.override_s_amount_type, 'NULL')) THEN
140 v_s_amount_type := IGS_AD_GEN_006.ADMP_GET_ITT_AMTTYP (new_references.intake_target_type);
141 IF IGS_AD_VAL_SIT.admp_val_trgt_amttyp(
142 v_s_amount_type,
143 new_references.override_s_amount_type,
144 v_message_name) = FALSE THEN
145 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
146 FND_MESSAGE.SET_NAME('IGS',v_message_name);
147 IGS_GE_MSG_STACK.ADD;
148 APP_EXCEPTION.RAISE_EXCEPTION;
149 END IF;
150 END IF;
151 -- Validate target and maximum target against amount type
152 IF p_inserting OR
153 (NVL(old_references.override_s_amount_type, 'NULL')
154 <> NVL(new_references.override_s_amount_type, 'NULL')) OR
155 (old_references.target <> new_references.target) OR
156 (old_references.max_target <> new_references.max_target) THEN
157 IF new_references.override_s_amount_type IS NULL THEN
158 v_s_amount_type := IGS_AD_GEN_006.ADMP_GET_ITT_AMTTYP (new_references.intake_target_type);
159 IF IGS_AD_VAL_SIT.admp_val_trgt_amt(
160 v_s_amount_type,
161 new_references.target,
162 new_references.max_target,
163 v_message_name) = FALSE THEN
164 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
165 FND_MESSAGE.SET_NAME('IGS',v_message_name);
166 IGS_GE_MSG_STACK.ADD;
167 APP_EXCEPTION.RAISE_EXCEPTION;
168 END IF;
169 ELSE
170 IF IGS_AD_VAL_SIT.admp_val_trgt_amt(
171 new_references.override_s_amount_type,
172 new_references.target,
173 new_references.max_target,
174 v_message_name) = FALSE THEN
175 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
176 FND_MESSAGE.SET_NAME('IGS',v_message_name);
177 IGS_GE_MSG_STACK.ADD;
178 APP_EXCEPTION.RAISE_EXCEPTION;
179 END IF;
180 END IF;
181 END IF;
182
183
184 END BeforeRowInsertUpdate1;
185
186 PROCEDURE Check_Parent_Existance AS
187 BEGIN
188
189 IF (((old_references.funding_source = new_references.funding_source)) OR
190 ((new_references.funding_source IS NULL))) THEN
191 NULL;
192 ELSE
193 IF NOT IGS_FI_FUND_SRC_PKG.Get_PK_For_Validation (
194 new_references.funding_source
195 )THEN
196 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
197 IGS_GE_MSG_STACK.ADD;
198 APP_EXCEPTION.RAISE_EXCEPTION;
199 END IF;
200 END IF;
201
202 IF (((old_references.submission_yr = new_references.submission_yr) AND
203 (old_references.submission_number = new_references.submission_number)) OR
204 ((new_references.submission_yr IS NULL) OR
205 (new_references.submission_number IS NULL))) THEN
206 NULL;
207 ELSE
208 IF NOT IGS_ST_GVT_SPSHT_CTL_PKG.Get_PK_For_Validation (
209 new_references.submission_yr,
210 new_references.submission_number
211 )THEN
212 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
213 IGS_GE_MSG_STACK.ADD;
214 APP_EXCEPTION.RAISE_EXCEPTION;
215 END IF;
216 END IF;
217
218 IF (((old_references.intake_target_type = new_references.intake_target_type)) OR
219 ((new_references.intake_target_type IS NULL))) THEN
220 NULL;
221 ELSE
222 IF NOT IGS_AD_INTAK_TRG_TYP_PKG.Get_PK_For_Validation (
223 new_references.intake_target_type,
224 'N'
225 )THEN
226 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
227 IGS_GE_MSG_STACK.ADD;
228 APP_EXCEPTION.RAISE_EXCEPTION;
229 END IF;
230 END IF;
231
232 IF (((old_references.org_unit_cd = new_references.org_unit_cd) AND
233 (TRUNC(old_references.ou_start_dt) = new_references.ou_start_dt)) OR
234 ((new_references.org_unit_cd IS NULL) OR
235 (new_references.ou_start_dt IS NULL))) THEN
236 NULL;
237 ELSE
238 IF NOT IGS_OR_UNIT_PKG.Get_PK_For_Validation (
239 new_references.org_unit_cd,
240 new_references.ou_start_dt
241 )THEN
242 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
243 IGS_GE_MSG_STACK.ADD;
244 APP_EXCEPTION.RAISE_EXCEPTION;
245 END IF;
246 END IF;
247
248 IF (((old_references.unit_int_course_level_cd = new_references.unit_int_course_level_cd)) OR
249 ((new_references.unit_int_course_level_cd IS NULL))) THEN
250 NULL;
251 ELSE
252 IF NOT IGS_PS_UNIT_INT_LVL_PKG.Get_PK_For_Validation (
253 new_references.unit_int_course_level_cd
254 )THEN
255 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
256 IGS_GE_MSG_STACK.ADD;
257 APP_EXCEPTION.RAISE_EXCEPTION;
258 END IF;
259 END IF;
260
261 IF (((old_references.override_s_amount_type = new_references.override_s_amount_type)) OR
262 ((new_references.override_s_amount_type IS NULL))) THEN
263 NULL;
264 ELSE
265 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation (
266 'AMOUNT_TYPE',
267 new_references.override_s_amount_type
268 ) THEN
269 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
270 IGS_GE_MSG_STACK.ADD;
271 App_Exception.Raise_Exception;
272 END IF;
273 END IF;
274
275 END Check_Parent_Existance;
276
277 FUNCTION Get_PK_For_Validation (
278 x_submission_yr IN NUMBER,
279 x_submission_number IN NUMBER,
280 x_org_unit_cd IN VARCHAR2,
281 x_ou_start_dt IN DATE,
282 x_funding_source IN VARCHAR2,
283 x_unit_int_course_level_cd IN VARCHAR2,
284 x_intake_target_type IN VARCHAR2
285 ) RETURN BOOLEAN AS
286
287 CURSOR cur_rowid IS
288 SELECT rowid
289 FROM IGS_AD_SBMAO_FN_UITT
290 WHERE submission_yr = x_submission_yr
291 AND submission_number = x_submission_number
292 AND org_unit_cd = x_org_unit_cd
293 AND TRUNC(ou_start_dt) = TRUNC(x_ou_start_dt)
294 AND funding_source = x_funding_source
295 AND unit_int_course_level_cd = x_unit_int_course_level_cd
296 AND intake_target_type = x_intake_target_type
297 FOR UPDATE NOWAIT;
298
299 lv_rowid cur_rowid%RowType;
300
301 BEGIN
302
303 Open cur_rowid;
304 Fetch cur_rowid INTO lv_rowid;
305 IF (cur_rowid%FOUND) THEN
306 Close cur_rowid;
307 Return TRUE;
308 ELSE
309 Close cur_rowid;
310 Return FALSE;
311 END IF;
312
313 END Get_PK_For_Validation;
314
315 PROCEDURE GET_FK_IGS_FI_FUND_SRC (
316 x_funding_source IN VARCHAR2
317 ) AS
318
319 CURSOR cur_rowid IS
320 SELECT rowid
321 FROM IGS_AD_SBMAO_FN_UITT
322 WHERE funding_source = x_funding_source ;
323
324 lv_rowid cur_rowid%RowType;
325
326 BEGIN
327
328 Open cur_rowid;
329 Fetch cur_rowid INTO lv_rowid;
330 IF (cur_rowid%FOUND) THEN
331 Close cur_rowid;
332 Fnd_Message.Set_Name ('IGS', 'IGS_AD_SAFUT_FS_FK');
333 IGS_GE_MSG_STACK.ADD;
334 App_Exception.Raise_Exception;
335 Return;
336 END IF;
337 Close cur_rowid;
338
339 END GET_FK_IGS_FI_FUND_SRC;
340
341 PROCEDURE GET_FK_IGS_ST_GVT_SPSHT_CTL (
342 x_submission_yr IN NUMBER,
343 x_submission_number IN NUMBER
344 ) AS
345
346 CURSOR cur_rowid IS
347 SELECT rowid
348 FROM IGS_AD_SBMAO_FN_UITT
349 WHERE submission_yr = x_submission_yr
350 AND submission_number = x_submission_number ;
351
352 lv_rowid cur_rowid%RowType;
353
354 BEGIN
355
356 Open cur_rowid;
357 Fetch cur_rowid INTO lv_rowid;
358 IF (cur_rowid%FOUND) THEN
359 Close cur_rowid;
360 Fnd_Message.Set_Name ('IGS', 'IGS_AD_SAFUT_GSC_FK');
361 IGS_GE_MSG_STACK.ADD;
362 App_Exception.Raise_Exception;
363 Return;
364 END IF;
365 Close cur_rowid;
366
367 END GET_FK_IGS_ST_GVT_SPSHT_CTL;
368
369 PROCEDURE GET_FK_IGS_AD_INTAK_TRG_TYP(
370 x_intake_target_type IN VARCHAR2
371 ) AS
372
373 CURSOR cur_rowid IS
374 SELECT rowid
375 FROM IGS_AD_SBMAO_FN_UITT
376 WHERE intake_target_type = x_intake_target_type ;
377
378 lv_rowid cur_rowid%RowType;
379
380 BEGIN
381
382 Open cur_rowid;
383 Fetch cur_rowid INTO lv_rowid;
384 IF (cur_rowid%FOUND) THEN
385 Close cur_rowid;
386 Fnd_Message.Set_Name ('IGS', 'IGS_AD_SAFUT_ITT_FK');
387 IGS_GE_MSG_STACK.ADD;
388 App_Exception.Raise_Exception;
389 Return;
390 END IF;
391 Close cur_rowid;
392
393 END GET_FK_IGS_AD_INTAK_TRG_TYP;
394
395 PROCEDURE GET_FK_IGS_OR_UNIT (
396 x_org_unit_cd IN VARCHAR2,
397 x_start_dt IN DATE
398 ) AS
399
400 CURSOR cur_rowid IS
401 SELECT rowid
402 FROM IGS_AD_SBMAO_FN_UITT
403 WHERE org_unit_cd = x_org_unit_cd
404 AND TRUNC(ou_start_dt) = TRUNC(x_start_dt) ;
405
406 lv_rowid cur_rowid%RowType;
407
408 BEGIN
409
410 Open cur_rowid;
411 Fetch cur_rowid INTO lv_rowid;
412 IF (cur_rowid%FOUND) THEN
413 Close cur_rowid;
414 Fnd_Message.Set_Name ('IGS', 'IGS_AD_SAFUT_OU_FK');
415 IGS_GE_MSG_STACK.ADD;
416 App_Exception.Raise_Exception;
417 Return;
418 END IF;
419 Close cur_rowid;
420
421 END GET_FK_IGS_OR_UNIT;
422
423 PROCEDURE GET_FK_IGS_PS_UNIT_INT_LVL (
424 x_unit_int_course_level_cd IN VARCHAR2
425 ) AS
426
427 CURSOR cur_rowid IS
428 SELECT rowid
429 FROM IGS_AD_SBMAO_FN_UITT
430 WHERE unit_int_course_level_cd = x_unit_int_course_level_cd ;
431
432 lv_rowid cur_rowid%RowType;
433
434 BEGIN
435
436 Open cur_rowid;
437 Fetch cur_rowid INTO lv_rowid;
438 IF (cur_rowid%FOUND) THEN
439 Close cur_rowid;
440 Fnd_Message.Set_Name ('IGS', 'IGS_AD_SAFUT_UICL_FK');
441 IGS_GE_MSG_STACK.ADD;
442 App_Exception.Raise_Exception;
443 Return;
444 END IF;
445 Close cur_rowid;
446
447 END GET_FK_IGS_PS_UNIT_INT_LVL;
448
449 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW(
450 x_override_s_amount_type IN VARCHAR2
451 ) as
452
453 CURSOR cur_rowid IS
454 SELECT rowid
455 FROM IGS_AD_SBMAO_FN_UITT
456 WHERE override_s_amount_type = x_override_s_amount_type ;
457
458 lv_rowid cur_rowid%RowType;
459
460 BEGIN
461
462 Open cur_rowid;
463 Fetch cur_rowid INTO lv_rowid;
464 IF (cur_rowid%FOUND) THEN
465 Close cur_rowid;
466 Fnd_Message.Set_Name ('IGS', 'IGS_AD_OVR_AMT_SLV_FK');
467 IGS_GE_MSG_STACK.ADD;
468 App_Exception.Raise_Exception;
469 Return;
470 END IF;
471 Close cur_rowid;
472
473 END GET_FK_IGS_LOOKUPS_VIEW;
474
475 -- procedure to check constraints
476 PROCEDURE CHECK_CONSTRAINTS(
477 column_name IN VARCHAR2 DEFAULT NULL,
478 column_value IN VARCHAR2 DEFAULT NULL
479 ) as
480 BEGIN
481 IF column_name is null THEN
482 NULL;
483 ELSIF upper(column_name) = 'TARGET' THEN
484 new_references.target := igs_ge_number.to_num(column_value);
485 ELSIF upper(column_name) = 'MAX_TARGET' THEN
486 new_references.max_target := igs_ge_number.to_num(column_value);
487 ELSIF upper(column_name) = 'ACTUAL_ENROLMENT' THEN
488 new_references.actual_enrolment := igs_ge_number.to_num(column_value);
489 ELSIF upper(column_name) = 'INTAKE_TARGET_TYPE' THEN
490 new_references.intake_target_type := column_value;
491 ELSIF upper(column_name) = 'FUNDING_SOURCE' THEN
492 new_references.funding_source := column_value;
493 ELSIF upper(column_name) = 'ORG_UNIT_CD' THEN
494 new_references.org_unit_cd := column_value;
495 ELSIF upper(column_name) = 'UNIT_INT_COURSE_LEVEL_CD' THEN
496 new_references.unit_int_course_level_cd := column_value;
497 END IF;
498
499 IF upper(column_name) = 'TARGET' OR column_name IS NULL THEN
500 IF new_references.target < 00000.000 OR new_references.target > 99999.999 THEN
501 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
502 IGS_GE_MSG_STACK.ADD;
503 APP_EXCEPTION.RAISE_EXCEPTION;
504 END IF;
505 END IF;
506 IF upper(column_name) = 'MAX_TARGET' OR column_name IS NULL THEN
507 IF new_references.max_target < 00000.000 OR new_references.max_target > 99999.999 THEN
508 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
509 IGS_GE_MSG_STACK.ADD;
510 APP_EXCEPTION.RAISE_EXCEPTION;
511 END IF;
512 END IF;
513 IF upper(column_name) = 'ACTUAL_ENROLMENT' OR column_name IS NULL THEN
514 IF new_references.actual_enrolment < 00000.000 OR new_references.actual_enrolment > 99999.999 THEN
515 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
516 IGS_GE_MSG_STACK.ADD;
517 APP_EXCEPTION.RAISE_EXCEPTION;
518 END IF;
519 END IF;
520 IF upper(column_name) = 'FUNDING_SOURCE' OR column_name IS NULL THEN
521 IF new_references.funding_source <> UPPER(new_references.funding_source) THEN
522 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
523 IGS_GE_MSG_STACK.ADD;
524 APP_EXCEPTION.RAISE_EXCEPTION;
525 END IF;
526 END IF;
527 IF upper(column_name) = 'INTAKE_TARGET_TYPE' OR column_name IS NULL THEN
528 IF new_references.intake_target_type <> UPPER(new_references.intake_target_type) THEN
529 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
530 IGS_GE_MSG_STACK.ADD;
531 APP_EXCEPTION.RAISE_EXCEPTION;
532 END IF;
533 END IF;
534 IF upper(column_name) = 'OVERRIDE_S_AMOUNT_TYPE' OR column_name IS NULL THEN
535 IF new_references.override_s_amount_type <> UPPER(new_references.override_s_amount_type) THEN
536 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
537 IGS_GE_MSG_STACK.ADD;
538 APP_EXCEPTION.RAISE_EXCEPTION;
539 END IF;
540 END IF;
541 IF upper(column_name) = 'UNIT_INT_COURSE_LEVEL_CD' OR column_name IS NULL THEN
542 IF new_references.unit_int_course_level_cd <> UPPER(new_references.unit_int_course_level_cd) THEN
543 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
544 IGS_GE_MSG_STACK.ADD;
545 APP_EXCEPTION.RAISE_EXCEPTION;
546 END IF;
547 END IF;
548 END CHECK_CONSTRAINTS;
549
550
551 PROCEDURE Before_DML (
552 p_action IN VARCHAR2,
553 x_rowid IN VARCHAR2 DEFAULT NULL,
554 x_submission_yr IN NUMBER DEFAULT NULL,
555 x_submission_number IN NUMBER DEFAULT NULL,
556 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
557 x_ou_start_dt IN DATE DEFAULT NULL,
558 x_funding_source IN VARCHAR2 DEFAULT NULL,
559 x_unit_int_course_level_cd IN VARCHAR2 DEFAULT NULL,
560 x_intake_target_type IN VARCHAR2 DEFAULT NULL,
561 x_target IN NUMBER DEFAULT NULL,
562 x_max_target IN NUMBER DEFAULT NULL,
563 x_override_s_amount_type IN VARCHAR2 DEFAULT NULL,
564 x_actual_enrolment IN NUMBER DEFAULT NULL,
565 x_actual_enr_effective_dt IN DATE DEFAULT NULL,
566 x_creation_date IN DATE DEFAULT NULL,
567 x_created_by IN NUMBER DEFAULT NULL,
568 x_last_update_date IN DATE DEFAULT NULL,
569 x_last_updated_by IN NUMBER DEFAULT NULL,
570 x_last_update_login IN NUMBER DEFAULT NULL
571 ) AS
572 BEGIN
573
574 Set_Column_Values (
575 p_action,
576 x_rowid,
577 x_submission_yr,
578 x_submission_number,
579 x_org_unit_cd,
580 x_ou_start_dt,
581 x_funding_source,
582 x_unit_int_course_level_cd,
583 x_intake_target_type,
584 x_target,
585 x_max_target,
586 x_override_s_amount_type,
587 x_actual_enrolment,
588 x_actual_enr_effective_dt,
589 x_creation_date,
590 x_created_by,
591 x_last_update_date,
592 x_last_updated_by,
593 x_last_update_login
594 );
595
596 IF (p_action = 'INSERT') THEN
597 -- Call all the procedures related to Before Insert.
598 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
599 IF GET_PK_FOR_VALIDATION(
600 new_references.submission_yr,
601 new_references.submission_number,
602 new_references.org_unit_cd,
603 new_references.ou_start_dt,
604 new_references.funding_source,
605 new_references.unit_int_course_level_cd,
606 new_references.intake_target_type
607 )THEN
608 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
609 IGS_GE_MSG_STACK.ADD;
610 APP_EXCEPTION.RAISE_EXCEPTION;
611 END IF;
612 Check_Constraints;
613 Check_Parent_Existance;
614 ELSIF (p_action = 'UPDATE') THEN
615 -- Call all the procedures related to Before Update.
616 BeforeRowInsertUpdate1 ( p_updating => TRUE );
617 Check_Constraints;
618 Check_Parent_Existance;
619 ELSIF (p_action = 'DELETE') THEN
620 -- Call all the procedures related to Before Delete.
621 Null;
622 ELSIF (p_action = 'VALIDATE_INSERT') THEN
623 -- Call all the procedures related to Before Delete.
624 IF GET_PK_FOR_VALIDATION(
625 new_references.submission_yr,
626 new_references.submission_number,
627 new_references.org_unit_cd,
628 new_references.ou_start_dt,
629 new_references.funding_source,
630 new_references.unit_int_course_level_cd,
631 new_references.intake_target_type
632 )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_Constraints;
638 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
639 -- Call all the procedures related to Before Delete.
640 check_constraints;
641 ELSIF (p_action = 'VALIDATE_DELETE') THEN
642 -- Call all the procedures related to Before Delete.
643 Null;
644 END IF;
645
646 END Before_DML;
647
648 PROCEDURE After_DML (
649 p_action IN VARCHAR2,
650 x_rowid IN VARCHAR2
651 ) AS
652 BEGIN
653
654 l_rowid := x_rowid;
655
656 END After_DML;
657
658 procedure INSERT_ROW (
659 X_ROWID in out NOCOPY VARCHAR2,
660 X_SUBMISSION_YR in NUMBER,
661 X_SUBMISSION_NUMBER in NUMBER,
662 X_ORG_UNIT_CD in VARCHAR2,
663 X_OU_START_DT in DATE,
664 X_FUNDING_SOURCE in VARCHAR2,
665 X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
666 X_INTAKE_TARGET_TYPE in VARCHAR2,
667 X_TARGET in NUMBER,
668 X_MAX_TARGET in NUMBER,
669 X_OVERRIDE_S_AMOUNT_TYPE in VARCHAR2,
670 X_ACTUAL_ENROLMENT in NUMBER,
671 X_ACTUAL_ENR_EFFECTIVE_DT in DATE,
672 X_MODE in VARCHAR2 default 'R'
673 ) as
674 cursor C is select ROWID from IGS_AD_SBMAO_FN_UITT
675 where SUBMISSION_YR = X_SUBMISSION_YR
676 and SUBMISSION_NUMBER = X_SUBMISSION_NUMBER
677 and ORG_UNIT_CD = X_ORG_UNIT_CD
678 and OU_START_DT = X_OU_START_DT
679 and FUNDING_SOURCE = X_FUNDING_SOURCE
680 and UNIT_INT_COURSE_LEVEL_CD = X_UNIT_INT_COURSE_LEVEL_CD
681 and INTAKE_TARGET_TYPE = X_INTAKE_TARGET_TYPE;
682 X_LAST_UPDATE_DATE DATE;
683 X_LAST_UPDATED_BY NUMBER;
684 X_LAST_UPDATE_LOGIN NUMBER;
685 begin
686 X_LAST_UPDATE_DATE := SYSDATE;
687 if(X_MODE = 'I') then
688 X_LAST_UPDATED_BY := 1;
689 X_LAST_UPDATE_LOGIN := 0;
690 elsif (X_MODE = 'R') then
691 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
692 if X_LAST_UPDATED_BY is NULL then
693 X_LAST_UPDATED_BY := -1;
694 end if;
695 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
696 if X_LAST_UPDATE_LOGIN is NULL then
697 X_LAST_UPDATE_LOGIN := -1;
698 end if;
699 else
700 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
701 IGS_GE_MSG_STACK.ADD;
702 app_exception.raise_exception;
703 end if;
704
705 Before_DML (
706 p_action =>'INSERT',
707 x_rowid =>X_ROWID,
708 x_submission_yr => X_SUBMISSION_YR,
709 x_submission_number => X_SUBMISSION_NUMBER,
710 x_org_unit_cd => X_ORG_UNIT_CD,
711 x_ou_start_dt => X_OU_START_DT,
712 x_funding_source => X_FUNDING_SOURCE,
713 x_unit_int_course_level_cd => X_UNIT_INT_COURSE_LEVEL_CD,
714 x_intake_target_type => X_INTAKE_TARGET_TYPE,
715 x_target => X_TARGET,
716 x_max_target => X_MAX_TARGET,
717 x_override_s_amount_type => X_OVERRIDE_S_AMOUNT_TYPE,
718 x_actual_enrolment => X_ACTUAL_ENROLMENT,
719 x_actual_enr_effective_dt => X_ACTUAL_ENR_EFFECTIVE_DT,
720 x_creation_date =>X_LAST_UPDATE_DATE,
721 x_created_by =>X_LAST_UPDATED_BY,
722 x_last_update_date =>X_LAST_UPDATE_DATE,
723 x_last_updated_by =>X_LAST_UPDATED_BY,
724 x_last_update_login =>X_LAST_UPDATE_LOGIN
725 );
726
727
728 insert into IGS_AD_SBMAO_FN_UITT (
729 SUBMISSION_YR,
730 SUBMISSION_NUMBER,
731 ORG_UNIT_CD,
732 OU_START_DT,
733 FUNDING_SOURCE,
734 UNIT_INT_COURSE_LEVEL_CD,
735 INTAKE_TARGET_TYPE,
736 TARGET,
737 MAX_TARGET,
738 OVERRIDE_S_AMOUNT_TYPE,
739 ACTUAL_ENROLMENT,
740 ACTUAL_ENR_EFFECTIVE_DT,
741 CREATION_DATE,
742 CREATED_BY,
743 LAST_UPDATE_DATE,
744 LAST_UPDATED_BY,
745 LAST_UPDATE_LOGIN
746 ) values (
747 NEW_REFERENCES.SUBMISSION_YR,
748 NEW_REFERENCES.SUBMISSION_NUMBER,
749 NEW_REFERENCES.ORG_UNIT_CD,
750 NEW_REFERENCES.OU_START_DT,
751 NEW_REFERENCES.FUNDING_SOURCE,
752 NEW_REFERENCES.UNIT_INT_COURSE_LEVEL_CD,
753 NEW_REFERENCES.INTAKE_TARGET_TYPE,
754 NEW_REFERENCES.TARGET,
755 NEW_REFERENCES.MAX_TARGET,
756 NEW_REFERENCES.OVERRIDE_S_AMOUNT_TYPE,
757 NEW_REFERENCES.ACTUAL_ENROLMENT,
758 NEW_REFERENCES.ACTUAL_ENR_EFFECTIVE_DT,
759 X_LAST_UPDATE_DATE,
760 X_LAST_UPDATED_BY,
761 X_LAST_UPDATE_DATE,
762 X_LAST_UPDATED_BY,
763 X_LAST_UPDATE_LOGIN
764 );
765
766 open c;
767 fetch c into X_ROWID;
768 if (c%notfound) then
769 close c;
770 raise no_data_found;
771 end if;
772 close c;
773
774 After_DML(
775 p_action =>'INSERT',
776 x_rowid => X_ROWID
777 );
778
779 end INSERT_ROW;
780
781 procedure LOCK_ROW (
782 X_ROWID in VARCHAR2,
783 X_SUBMISSION_YR in NUMBER,
784 X_SUBMISSION_NUMBER in NUMBER,
785 X_ORG_UNIT_CD in VARCHAR2,
786 X_OU_START_DT in DATE,
787 X_FUNDING_SOURCE in VARCHAR2,
788 X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
789 X_INTAKE_TARGET_TYPE in VARCHAR2,
790 X_TARGET in NUMBER,
791 X_MAX_TARGET in NUMBER,
792 X_OVERRIDE_S_AMOUNT_TYPE in VARCHAR2,
793 X_ACTUAL_ENROLMENT in NUMBER,
794 X_ACTUAL_ENR_EFFECTIVE_DT in DATE
795 ) as
796 cursor c1 is select
797 TARGET,
798 MAX_TARGET,
799 OVERRIDE_S_AMOUNT_TYPE,
800 ACTUAL_ENROLMENT,
801 ACTUAL_ENR_EFFECTIVE_DT
802 from IGS_AD_SBMAO_FN_UITT
803 where ROWID = X_ROWID
804 for update nowait;
805 tlinfo c1%rowtype;
806
807 begin
808 open c1;
809 fetch c1 into tlinfo;
810 if (c1%notfound) then
811 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
812 IGS_GE_MSG_STACK.ADD;
813 app_exception.raise_exception;
814 close c1;
815 return;
816 end if;
817 close c1;
818
819 if ( (tlinfo.TARGET = X_TARGET)
820 AND ((tlinfo.MAX_TARGET = X_MAX_TARGET)
821 OR ((tlinfo.MAX_TARGET is null)
822 AND (X_MAX_TARGET is null)))
823 AND ((tlinfo.OVERRIDE_S_AMOUNT_TYPE = X_OVERRIDE_S_AMOUNT_TYPE)
824 OR ((tlinfo.OVERRIDE_S_AMOUNT_TYPE is null)
825 AND (X_OVERRIDE_S_AMOUNT_TYPE is null)))
826 AND ((tlinfo.ACTUAL_ENROLMENT = X_ACTUAL_ENROLMENT)
827 OR ((tlinfo.ACTUAL_ENROLMENT is null)
828 AND (X_ACTUAL_ENROLMENT is null)))
829 AND ((TRUNC(tlinfo.ACTUAL_ENR_EFFECTIVE_DT) = TRUNC(X_ACTUAL_ENR_EFFECTIVE_DT))
830 OR ((tlinfo.ACTUAL_ENR_EFFECTIVE_DT is null)
831 AND (X_ACTUAL_ENR_EFFECTIVE_DT is null)))
832 ) then
833 null;
834 else
835 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
836 IGS_GE_MSG_STACK.ADD;
837 app_exception.raise_exception;
838 end if;
839 return;
840 end LOCK_ROW;
841
842 procedure UPDATE_ROW (
843 X_ROWID in VARCHAR2,
844 X_SUBMISSION_YR in NUMBER,
845 X_SUBMISSION_NUMBER in NUMBER,
846 X_ORG_UNIT_CD in VARCHAR2,
847 X_OU_START_DT in DATE,
848 X_FUNDING_SOURCE in VARCHAR2,
849 X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
850 X_INTAKE_TARGET_TYPE in VARCHAR2,
851 X_TARGET in NUMBER,
852 X_MAX_TARGET in NUMBER,
853 X_OVERRIDE_S_AMOUNT_TYPE in VARCHAR2,
854 X_ACTUAL_ENROLMENT in NUMBER,
855 X_ACTUAL_ENR_EFFECTIVE_DT in DATE,
856 X_MODE in VARCHAR2 default 'R'
857 ) as
858 X_LAST_UPDATE_DATE DATE;
859 X_LAST_UPDATED_BY NUMBER;
860 X_LAST_UPDATE_LOGIN NUMBER;
861 begin
862 X_LAST_UPDATE_DATE := SYSDATE;
863 if(X_MODE = 'I') then
864 X_LAST_UPDATED_BY := 1;
865 X_LAST_UPDATE_LOGIN := 0;
866 elsif (X_MODE = 'R') then
867 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
868 if X_LAST_UPDATED_BY is NULL then
869 X_LAST_UPDATED_BY := -1;
870 end if;
871 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
872 if X_LAST_UPDATE_LOGIN is NULL then
873 X_LAST_UPDATE_LOGIN := -1;
874 end if;
875 else
876 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
877 IGS_GE_MSG_STACK.ADD;
878 app_exception.raise_exception;
879 end if;
880
881 Before_DML (
882 p_action =>'UPDATE',
883 x_rowid =>X_ROWID,
884 x_submission_yr => X_SUBMISSION_YR,
885 x_submission_number => X_SUBMISSION_NUMBER,
886 x_org_unit_cd => X_ORG_UNIT_CD,
887 x_ou_start_dt => X_OU_START_DT,
888 x_funding_source => X_FUNDING_SOURCE,
889 x_unit_int_course_level_cd => X_UNIT_INT_COURSE_LEVEL_CD,
890 x_intake_target_type => X_INTAKE_TARGET_TYPE,
891 x_target => X_TARGET,
892 x_max_target => X_MAX_TARGET,
893 x_override_s_amount_type => X_OVERRIDE_S_AMOUNT_TYPE,
894 x_actual_enrolment => X_ACTUAL_ENROLMENT,
895 x_actual_enr_effective_dt => X_ACTUAL_ENR_EFFECTIVE_DT,
896 x_creation_date =>X_LAST_UPDATE_DATE,
897 x_created_by =>X_LAST_UPDATED_BY,
898 x_last_update_date =>X_LAST_UPDATE_DATE,
899 x_last_updated_by =>X_LAST_UPDATED_BY,
900 x_last_update_login =>X_LAST_UPDATE_LOGIN
901 );
902
903 update IGS_AD_SBMAO_FN_UITT set
904 TARGET = NEW_REFERENCES.TARGET,
905 MAX_TARGET = NEW_REFERENCES.MAX_TARGET,
906 OVERRIDE_S_AMOUNT_TYPE = NEW_REFERENCES.OVERRIDE_S_AMOUNT_TYPE,
907 ACTUAL_ENROLMENT = NEW_REFERENCES.ACTUAL_ENROLMENT,
908 ACTUAL_ENR_EFFECTIVE_DT = NEW_REFERENCES.ACTUAL_ENR_EFFECTIVE_DT,
909 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
910 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
911 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
912 where ROWID = X_ROWID
913 ;
914 if (sql%notfound) then
915 raise no_data_found;
916 end if;
917
918 After_DML(
919 p_action =>'UPDATE',
920 x_rowid => X_ROWID
921 );
922
923 end UPDATE_ROW;
924
925 procedure ADD_ROW (
926 X_ROWID in out NOCOPY VARCHAR2,
927 X_SUBMISSION_YR in NUMBER,
928 X_SUBMISSION_NUMBER in NUMBER,
929 X_ORG_UNIT_CD in VARCHAR2,
930 X_OU_START_DT in DATE,
931 X_FUNDING_SOURCE in VARCHAR2,
932 X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
933 X_INTAKE_TARGET_TYPE in VARCHAR2,
934 X_TARGET in NUMBER,
935 X_MAX_TARGET in NUMBER,
936 X_OVERRIDE_S_AMOUNT_TYPE in VARCHAR2,
937 X_ACTUAL_ENROLMENT in NUMBER,
938 X_ACTUAL_ENR_EFFECTIVE_DT in DATE,
939 X_MODE in VARCHAR2 default 'R'
940 ) as
941 cursor c1 is select rowid from IGS_AD_SBMAO_FN_UITT
942 where SUBMISSION_YR = X_SUBMISSION_YR
943 and SUBMISSION_NUMBER = X_SUBMISSION_NUMBER
944 and ORG_UNIT_CD = X_ORG_UNIT_CD
945 and OU_START_DT = X_OU_START_DT
946 and FUNDING_SOURCE = X_FUNDING_SOURCE
947 and UNIT_INT_COURSE_LEVEL_CD = X_UNIT_INT_COURSE_LEVEL_CD
948 and INTAKE_TARGET_TYPE = X_INTAKE_TARGET_TYPE
949 ;
950 begin
951 open c1;
952 fetch c1 into X_ROWID;
953 if (c1%notfound) then
954 close c1;
955 INSERT_ROW (
956 X_ROWID,
957 X_SUBMISSION_YR,
958 X_SUBMISSION_NUMBER,
959 X_ORG_UNIT_CD,
960 X_OU_START_DT,
961 X_FUNDING_SOURCE,
962 X_UNIT_INT_COURSE_LEVEL_CD,
963 X_INTAKE_TARGET_TYPE,
964 X_TARGET,
965 X_MAX_TARGET,
966 X_OVERRIDE_S_AMOUNT_TYPE,
967 X_ACTUAL_ENROLMENT,
968 X_ACTUAL_ENR_EFFECTIVE_DT,
969 X_MODE);
970 return;
971 end if;
972 close c1;
973 UPDATE_ROW (
974 X_ROWID,
975 X_SUBMISSION_YR,
976 X_SUBMISSION_NUMBER,
977 X_ORG_UNIT_CD,
978 X_OU_START_DT,
979 X_FUNDING_SOURCE,
980 X_UNIT_INT_COURSE_LEVEL_CD,
981 X_INTAKE_TARGET_TYPE,
982 X_TARGET,
983 X_MAX_TARGET,
984 X_OVERRIDE_S_AMOUNT_TYPE,
985 X_ACTUAL_ENROLMENT,
986 X_ACTUAL_ENR_EFFECTIVE_DT,
987 X_MODE);
988 end ADD_ROW;
989
990 procedure DELETE_ROW (
991 X_ROWID in VARCHAR2
992 ) as
993 begin
994
995 Before_DML(
996 p_action =>'DELETE',
997 x_rowid => X_ROWID
998 );
999
1000 delete from IGS_AD_SBMAO_FN_UITT
1001 where ROWID = X_ROWID;
1002 if (sql%notfound) then
1003 raise no_data_found;
1004 end if;
1005
1006 After_DML(
1007 p_action =>'DELETE',
1008 x_rowid => X_ROWID
1009 );
1010
1011 end DELETE_ROW;
1012
1013 end IGS_AD_SBMAO_FN_UITT_PKG;