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