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