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