1 PACKAGE BODY IGS_RE_VAL_CSC AS
2 /* $Header: IGSRE07B.pls 115.4 2002/11/29 03:28:14 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 25-AUG-2001 Bug No. 1956374 .Thed function GENP_VAL_SDTT_SESS removed.
7 -------------------------------------------------------------------------------------------
8 /*
9 || Bug ID 1956374 - Removal of Duplicate Program Units from OSS.
10 || Removed program unit (RESP_VAL_CA_CHILDUPD) - from the spec and body. -- kdande
11 */
12 --
13 -- Validate IGS_RE_CANDIDATURE socio-economic classification code percentage.
14 FUNCTION resp_val_csc_perc(
15 p_person_id IN NUMBER ,
16 p_ca_sequence_number IN NUMBER ,
17 p_message_name OUT NOCOPY VARCHAR2 )
18 RETURN BOOLEAN AS
19 BEGIN -- resp_val_csc_perc
20 -- This module validates IGS_RE_CAND_SEO_CLS.percentage. Validations are:
21 -- Total percentage for research IGS_RE_CANDIDATURE must be 100.
22 DECLARE
23 v_total_percentage IGS_RE_CAND_SEO_CLS.percentage%TYPE;
24 CURSOR c_csc IS
25 SELECT NVL(sum(csc.percentage),0)
26 FROM IGS_RE_CAND_SEO_CLS csc
27 WHERE csc.person_id = p_person_id AND
28 csc.ca_sequence_number = p_ca_sequence_number;
29 BEGIN
30 p_message_name := null;
31 OPEN c_csc;
32 FETCH c_csc INTO v_total_percentage;
33 CLOSE c_csc;
34 IF v_total_percentage = 0 THEN
35 -- no values for research IGS_RE_CANDIDATURE field of study entered yet
36 RETURN TRUE;
37 ELSIF v_total_percentage <> 100 THEN
38 p_message_name := 'IGS_RE_CAND_SOCIO_ECO_CLASS';
39 RETURN FALSE;
40 END IF;
41 RETURN TRUE;
42 EXCEPTION
43 WHEN OTHERS THEN
44 IF (c_csc%ISOPEN) THEN
45 CLOSE c_csc;
46 END IF;
47 RAISE;
48 END;
49 EXCEPTION
50 WHEN OTHERS THEN
51 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
52 IGS_GE_MSG_STACK.ADD;
53 App_Exception.Raise_Exception;
54 END resp_val_csc_perc;
55 --
56 -- Validate IGS_RE_CANDIDATURE socio-economic classification code.
57 FUNCTION resp_val_csc_seocc(
58 p_person_id IN NUMBER ,
59 p_ca_sequence_number IN NUMBER ,
60 p_seo_class_cd IN VARCHAR2 ,
61 p_message_name OUT NOCOPY VARCHAR2 )
62 RETURN BOOLEAN AS
63 BEGIN -- resp_val_csc_seocc
64 -- Description: This module validate cand_seo_class.seo_class_cd.
65 -- Validations are:
66 -- *IGS_RE_SEO_CLASS_CD is not closed.
67 -- *IGS_RE_GV_SEO_CLS_CD.res_fcd_class_ind is the same for all
68 -- IGS_RE_CAND_SEO_CLS for a research IGS_RE_CANDIDATURE.
69 DECLARE
70 CURSOR c_seocc_gscc IS
71 SELECT gscc.res_fcd_class_ind
72 FROM IGS_RE_SEO_CLASS_CD seocc,
73 IGS_RE_GV_SEO_CLS_CD gscc
74 WHERE seocc.seo_class_cd = p_seo_class_cd AND
75 seocc.govt_seo_class_cd = gscc.govt_seo_class_cd;
76 v_res_fcd_class_ind IGS_RE_GV_SEO_CLS_CD.res_fcd_class_ind%TYPE;
77 CURSOR c_csc_seocc_gscc(
78 cp_res_fcd_class_ind IGS_RE_GV_SEO_CLS_CD.res_fcd_class_ind%TYPE)IS
79 SELECT 'X'
80 FROM IGS_RE_CAND_SEO_CLS csc,
81 IGS_RE_SEO_CLASS_CD seocc,
82 IGS_RE_GV_SEO_CLS_CD gscc
83 WHERE csc.person_id = p_person_id AND
84 csc.ca_sequence_number = p_ca_sequence_number AND
85 csc.seo_class_cd <>p_seo_class_cd AND
86 csc.seo_class_cd = seocc.seo_class_cd AND
87 seocc.govt_seo_class_cd = gscc.govt_seo_class_cd AND
88 gscc.res_fcd_class_ind <>cp_res_fcd_class_ind;
89 v_dummy_exists VARCHAR2(1);
90 BEGIN
91 p_message_name := null;
92 IF NOT IGS_RE_VAL_CSC.resp_val_seocc_clsd(
93 p_seo_class_cd,
94 p_message_name) THEN
95 RETURN FALSE;
96 END IF;
97 OPEN c_seocc_gscc;
98 FETCH c_seocc_gscc INTO v_res_fcd_class_ind;
99 IF (c_seocc_gscc%NOTFOUND) THEN
100 CLOSE c_seocc_gscc;
101 RETURN TRUE;
102 END IF;
103 OPEN c_csc_seocc_gscc(
104 v_res_fcd_class_ind);
105 FETCH c_csc_seocc_gscc INTO v_dummy_exists;
106 IF (c_csc_seocc_gscc%FOUND) THEN
107 CLOSE c_csc_seocc_gscc;
108 p_message_name := 'IGS_RE_CHK_SOCIO_ECO_CLASSIF';
109 RETURN FALSE;
110 END IF;
111 CLOSE c_seocc_gscc;
112 CLOSE c_csc_seocc_gscc;
113 RETURN TRUE;
114 END;
115 EXCEPTION
116 WHEN OTHERS THEN
117 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
118 IGS_GE_MSG_STACK.ADD;
119 App_Exception.Raise_Exception;
120 END resp_val_csc_seocc;
121 --
122 -- Validate if Socio-Economic Classification Code is closed.
123 FUNCTION resp_val_seocc_clsd(
124 p_seo_class_cd IN VARCHAR2 ,
125 p_message_name OUT NOCOPY VARCHAR2 )
126 RETURN BOOLEAN AS
127 BEGIN -- resp_val_seocc_clsd
128 -- Description: Validate if seo_class_cd.seo_class_cd is closed.
129 DECLARE
130 v_seocc_exists VARCHAR2(1);
131 CURSOR c_seocc IS
132 SELECT 'X'
133 FROM IGS_RE_SEO_CLASS_CD seocc
134 WHERE seocc.seo_class_cd = p_seo_class_cd AND
135 seocc.closed_ind = 'Y';
136 BEGIN
137 p_message_name := null;
138 OPEN c_seocc;
139 FETCH c_seocc INTO v_seocc_exists;
140 IF (c_seocc%FOUND) THEN
141 CLOSE c_seocc;
142 p_message_name := 'IGS_RE_SOCIO_ECO_OBJ_CLOSED';
143 RETURN FALSE;
144 END IF;
145 CLOSE c_seocc;
146 RETURN TRUE;
147 END;
148 EXCEPTION
149 WHEN OTHERS THEN
150 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
151 IGS_GE_MSG_STACK.ADD;
152 App_Exception.Raise_Exception;
153 END resp_val_seocc_clsd;
154 END IGS_RE_VAL_CSC;
[Home] [Help]