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