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