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