1 PACKAGE BODY IGS_PS_VAL_CAL AS
2 /* $Header: IGSPS14B.pls 115.5 2002/11/29 02:56:45 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 28-AUG-2001 Bug No. 1956374 .The function genp_val_strt_end_dt removed
7 -------------------------------------------------------------------------------------------
8
9 -- Validate there are IGS_PS_COURSE annual load IGS_PS_UNIT links to copy.
10 FUNCTION crsp_val_calul_copy(
11 p_course_cd IN VARCHAR2 ,
12 p_version_number IN NUMBER ,
13 p_yr_num IN NUMBER ,
14 p_effective_start_dt IN DATE )
15 RETURN BOOLEAN AS
16 gv_effective_start_dt IGS_PS_ANL_LOAD.effective_start_dt%TYPE DEFAULT NULL;
17 gv_result CHAR;
18 CURSOR c_find_cal IS
19 SELECT MAX(effective_start_dt)
20 FROM IGS_PS_ANL_LOAD
21 WHERE course_cd = p_course_cd AND
22 version_number = p_version_number AND
23 yr_num = p_yr_num AND
24 effective_start_dt < p_effective_start_dt;
25 CURSOR c_find_calul IS
26 SELECT 'x'
27 FROM IGS_PS_ANL_LOAD_U_LN
28 WHERE course_cd = p_course_cd AND
29 crv_version_number = p_version_number AND
30 yr_num = p_yr_num AND
31 effective_start_dt = gv_effective_start_dt;
32 BEGIN
33 --- This cursor gets the most recent effective start date. If one is not found
34 --- then return FALSE.
35 OPEN c_find_cal;
36 FETCH c_find_cal INTO gv_effective_start_dt;
37 IF gv_effective_start_dt IS NULL THEN
38 CLOSE c_find_cal;
39 RETURN FALSE;
40 END IF;
41 CLOSE c_find_cal;
42 --- This is cursor checks for the existance of a IGS_PS_COURSE annual load IGS_PS_UNIT link
43 --- based on the most recent effective start date found previously and returns
44 --- TRUE if at least one is retrieved.
45 OPEN c_find_calul;
46 FETCH c_find_calul INTO gv_result;
47 IF c_find_calul%NOTFOUND THEN
48 CLOSE c_find_calul;
49 RETURN FALSE;
50 ELSE
51 CLOSE c_find_calul;
52 RETURN TRUE;
53 END IF;
54 EXCEPTION
55 WHEN OTHERS THEN
56 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
57 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CAL.crsp_val_calul_copy');
58 IGS_GE_MSG_STACK.ADD;
59 App_Exception.Raise_Exception;
60 END crsp_val_calul_copy;
61
62
63 -- Validate the IGS_PS_COURSE annual load end date.
64 FUNCTION crsp_val_cal_end_dt(
65 p_course_cd IN VARCHAR2 ,
66 p_version_number IN NUMBER ,
67 p_yr_num IN NUMBER ,
68 p_effective_start_dt IN DATE ,
69 p_message_name OUT NOCOPY VARCHAR2 )
70 RETURN BOOLEAN AS
71 v_check CHAR;
72 CURSOR c_get_course_annual_load_rec IS
73 SELECT 'x'
74 FROM IGS_PS_ANL_LOAD
75 WHERE course_cd = p_course_cd AND
76 version_number = p_version_number AND
77 yr_num = p_yr_num AND
78 effective_start_dt <> p_effective_start_dt AND
79 effective_end_dt IS NULL;
80 BEGIN
81 -- validate that no other open ended IGS_PS_COURSE annual load
82 -- exists for the IGS_PS_COURSE version with the same yr_desc.
83 OPEN c_get_course_annual_load_rec;
84 FETCH c_get_course_annual_load_rec INTO v_check;
85 IF (c_get_course_annual_load_rec%NOTFOUND) THEN
86 p_message_name := NULL;
87 RETURN TRUE;
88 ELSE
89 p_message_name := 'IGS_PS_END_DATE_MUSTBE_SET';
90 RETURN FALSE;
91 END IF;
92 EXCEPTION
93 WHEN OTHERS THEN
94 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
95 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CAL.crsp_val_cal_end_dt');
96 IGS_GE_MSG_STACK.ADD;
97 App_Exception.Raise_Exception;
98 END crsp_val_cal_end_dt;
99
100 END IGS_PS_VAL_CAL;