1 PACKAGE BODY IGS_AD_VAL_OSE AS
2 /* $Header: IGSAD64B.pls 115.3 2002/11/28 21:38:19 nsidana ship $ */
3
4 --
5 -- Validate the Overseas Scndry Education Qualification closed indicator.
6 FUNCTION ADMP_VAL_OSEQ_CLOSED(
7 p_os_scndry_edu_qualification IN VARCHAR2 ,
8 p_message_name OUT NOCOPY VARCHAR2 )
9 RETURN BOOLEAN AS
10 BEGIN -- admp_val_oseq_closed
11 -- Validate if IGS_AD_OS_SEC_EDU.os_scndry_edu_qualification is closed.
12 DECLARE
13 v_oseq_closed_exist VARCHAR2(1);
14 CURSOR c_oseq_closed IS
15 SELECT 'x'
16 FROM IGS_AD_OS_SEC_EDU_QF oseq
17 WHERE oseq.os_scndry_edu_qualification=p_os_scndry_edu_qualification AND
18 closed_ind = 'Y';
19 BEGIN
20 -- Set the default message number
21 p_message_name := null;
22 OPEN c_oseq_closed;
23 FETCH c_oseq_closed INTO v_oseq_closed_exist;
24 IF c_oseq_closed%FOUND THEN
25 CLOSE c_oseq_closed;
26 p_message_name := 'IGS_AD_OVERSEAS_EDU_CLOSED';
27 RETURN FALSE;
28 END IF;
29 CLOSE c_oseq_closed;
30 RETURN TRUE;
31 EXCEPTION
32 WHEN OTHERS THEN
33 IF c_oseq_closed%ISOPEN THEN
34 CLOSE c_oseq_closed;
35 END IF;
36 App_Exception.Raise_Exception;
37 END;
38 EXCEPTION
39 WHEN OTHERS THEN
40 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
41 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_OSE.admp_val_oseq_closed');
42 IGS_GE_MSG_STACK.ADD;
43 App_Exception.Raise_Exception;
44 END admp_val_oseq_closed;
45 --
46 -- Validate the Overseas Scndry Education Qualification Country Code.
47 FUNCTION ADMP_VAL_OSE_QCNTRY(
48 p_os_scndry_edu_qualification IN VARCHAR2 ,
49 p_country_cd IN VARCHAR2 ,
50 p_message_name OUT NOCOPY VARCHAR2 )
51 RETURN BOOLEAN AS
52 BEGIN -- admp_val_ose_qcntry
53 -- Validate that the IGS_AD_OS_SEC_EDU_QF.country_cd is the same as the
54 -- IGS_AD_OS_SEC_EDU.country_cd or that the
55 -- IGS_AD_OS_SEC_EDU_QF.country_cd is null.
56 DECLARE
57 v_country_cd IGS_AD_OS_SEC_EDU_QF.country_cd%TYPE;
58 CURSOR c_oseq IS
59 SELECT COUNTRY_CD
60 FROM IGS_AD_OS_SEC_EDU_QF
61 WHERE os_scndry_edu_qualification = p_os_scndry_edu_qualification;
62 BEGIN
63 p_message_name := null;
64 IF (p_os_scndry_edu_qualification IS NOT NULL) THEN
65 OPEN c_oseq;
66 FETCH c_oseq INTO v_country_cd;
67 CLOSE c_oseq;
68 IF (v_country_cd IS NOT NULL) THEN
69 IF (v_country_cd <> p_country_cd) THEN
70 p_message_name := 'IGS_AD_OVERSEAS_EDU_COUNTCD';
71 RETURN FALSE;
72 END IF;
73 END IF;
74 END IF;
75 RETURN TRUE;
76 END;
77 EXCEPTION
78 WHEN OTHERS THEN
79 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
80 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_OSE.admp_val_ose_qcntry');
81 IGS_GE_MSG_STACK.ADD;
82 App_Exception.Raise_Exception;
83 END admp_val_ose_qcntry;
84 END IGS_AD_VAL_OSE;