1 PACKAGE BODY IGS_PS_VAL_CRFC AS
2 /* $Header: IGSPS32B.pls 115.3 2002/11/29 03:01:38 nsidana ship $ */
3 --
4 -- Validate the reference code type
5 FUNCTION crsp_val_ref_cd_type(
6 p_reference_cd_type IN VARCHAR2 ,
7 p_message_name OUT NOCOPY VARCHAR2 )
8 RETURN BOOLEAN AS
9 v_closed_ind IGS_GE_REF_CD_TYPE.closed_ind%TYPE;
10 CURSOR c_reference_cd_type IS
11 SELECT closed_ind
12 FROM IGS_GE_REF_CD_TYPE
13 WHERE reference_cd_type = p_reference_cd_type;
14 BEGIN
15 OPEN c_reference_cd_type;
16 FETCH c_reference_cd_type INTO v_closed_ind;
17 IF c_reference_cd_type%NOTFOUND THEN
18 p_message_name := NULL;
19 CLOSE c_reference_cd_type;
20 RETURN TRUE;
21 ELSIF (v_closed_ind = 'N') THEN
22 p_message_name := NULL;
23 CLOSE c_reference_cd_type;
24 RETURN TRUE;
25 ELSE
26 p_message_name := 'IGS_PS_REFCD_TYPE_CLOSED';
27 CLOSE c_reference_cd_type;
28 RETURN FALSE;
29 END IF;
30 EXCEPTION
31 WHEN OTHERS THEN
32 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
33 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CRFC.crsp_val_ref_cd_type');
34 IGS_GE_MSG_STACK.ADD;
35 APP_EXCEPTION.RAISE_EXCEPTION;
36 END crsp_val_ref_cd_type;
37 --
38 -- Validate that only one open reference code type exists
39 FUNCTION crsp_val_crfc_rct(
40 p_course_cd IN IGS_PS_REF_CD.course_cd%TYPE ,
41 p_version_number IN IGS_PS_REF_CD.version_number%TYPE ,
42 p_reference_cd_type IN VARCHAR2 ,
43 p_reference_cd IN IGS_PS_REF_CD.reference_cd%TYPE ,
44 p_message_name OUT NOCOPY VARCHAR2 )
45 RETURN BOOLEAN AS
46 BEGIN -- crsp_val_crfc_rct
47 -- This module validates whether an open Reference Code Type
48 -- already exists for the System Reference Code Type.
49 DECLARE
50 v_s_reference_cd_type IGS_GE_REF_CD_TYPE.s_reference_cd_type%TYPE;
51 v_count NUMBER DEFAULT 0;
52 CURSOR c_rct IS
53 SELECT rct.s_reference_cd_type
54 FROM IGS_GE_REF_CD_TYPE rct
55 WHERE rct.reference_cd_type = p_reference_cd_type;
56 CURSOR c_crfc_rct (
57 cp_s_reference_cd_type IGS_GE_REF_CD_TYPE.s_reference_cd_type%TYPE) IS
58 SELECT COUNT(*)
59 FROM IGS_PS_REF_CD crfc,
60 IGS_GE_REF_CD_TYPE rct
61 WHERE crfc.course_cd = p_course_cd AND
62 crfc.version_number = p_version_number AND
63 (crfc.reference_cd_type <> p_reference_cd_type OR
64 crfc.reference_cd <> p_reference_cd) AND
65 rct.reference_cd_type = crfc.reference_cd_type AND
66 rct.s_reference_cd_type = cp_s_reference_cd_type AND
67 rct.closed_ind = 'N';
68 BEGIN
69 OPEN c_rct;
70 FETCH c_rct INTO v_s_reference_cd_type;
71 CLOSE c_rct;
72 OPEN c_crfc_rct(v_s_reference_cd_type);
73 FETCH c_crfc_rct INTO v_count;
74 CLOSE c_crfc_rct;
75 IF v_count > 0 THEN
76 p_message_name := 'IGS_PS_OPEN_REFCDTYPE_EXIST';
77 RETURN FALSE;
78 END IF;
79 p_message_name := NULL;
80 RETURN TRUE;
81 EXCEPTION
82 WHEN OTHERS THEN
83 IF (c_rct%ISOPEN) THEN
84 CLOSE c_rct;
85 END IF;
86 IF (c_crfc_rct%ISOPEN) THEN
87 CLOSE c_crfc_rct;
88 END IF;
89 APP_EXCEPTION.RAISE_EXCEPTION;
90 END;
91 EXCEPTION
92 WHEN OTHERS THEN
93 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
94 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CRFC.crsp_val_crfc_rct');
95 IGS_GE_MSG_STACK.ADD;
96 APP_EXCEPTION.RAISE_EXCEPTION;
97 END crsp_val_crfc_rct;
98 END IGS_PS_VAL_CRFC;
[Home] [Help]