1 PACKAGE BODY IGS_ST_VAL_GSC AS
2 /* $Header: IGSST08B.pls 115.5 2002/11/29 04:12:00 nsidana ship $ */
3 --
4
5 -- Validate the government snapshot control snapshot date time.
6 FUNCTION stap_val_gsc_sdt(
7 p_submission_yr IN NUMBER ,
8 p_snapshot_dt_time IN DATE ,
9 p_message_name OUT NOCOPY VARCHAR2 )
10 RETURN BOOLEAN AS
11 BEGIN
12 DECLARE
13 v_delete_snapshot_ind IGS_EN_ST_SPSHT_CTL.delete_snapshot_ind%TYPE;
14 v_submission_yr IGS_ST_GVT_SPSHT_CTL.submission_yr%TYPE;
15 CURSOR c_essc(
16 cp_snapshot_dt_time IGS_ST_GVT_SPSHT_CTL.ess_snapshot_dt_time%TYPE) IS
17 SELECT essc.delete_snapshot_ind
18 FROM IGS_EN_ST_SPSHT_CTL essc
19 WHERE essc.snapshot_dt_time = cp_snapshot_dt_time;
20 CURSOR c_gsc(
21 cp_snapshot_dt_time IGS_ST_GVT_SPSHT_CTL.ess_snapshot_dt_time%TYPE,
22 cp_submission_yr IGS_ST_GVT_SPSHT_CTL.submission_yr%TYPE) IS
23 SELECT gsc.submission_yr
24 FROM IGS_ST_GVT_SPSHT_CTL gsc
25 WHERE gsc.ess_snapshot_dt_time = cp_snapshot_dt_time AND
26 gsc.submission_yr <> cp_submission_yr;
27 BEGIN
28 -- Validate the IGS_ST_GVT_SPSHT_CTL.snapshot_dt_time.
29 -- An Enrolment Statistics Snapshot which is marked for delete cannot
30 -- be used by a Government Snapshot.
31 -- An Enrolment Statistics Snapshot which is used by another Government
32 -- Snapshot submission in another year cannot be used by a Government
33 -- Snapshot.
34 p_message_name := NULL;
35 -- Retrieve the Enrolment Statistics Snapshot Control data.
36 OPEN c_essc(
37 p_snapshot_dt_time);
38 FETCH c_essc INTO v_delete_snapshot_ind;
39 IF(c_essc%FOUND = TRUE) THEN
40 -- Validate the Enrolment Statistics Snapshot.
41 IF(v_delete_snapshot_ind = 'Y') THEN
42 CLOSE c_essc;
43 p_message_name := 'IGS_ST_CANT_USE_ENRL_STATIST';
44 RETURN FALSE;
45 END IF;
46 END IF;
47 CLOSE c_essc;
48 -- Retrieve the Government Snapshot Control data.
49 OPEN c_gsc(
50 p_snapshot_dt_time,
51 p_submission_yr);
52 FETCH c_gsc INTO v_submission_yr;
53 IF(c_gsc%FOUND = TRUE) THEN
54 CLOSE c_gsc;
55 p_message_name := 'IGS_ST_ENRL_STAT_SNAP_IN_USE';
56 RETURN FALSE;
57 END IF;
58 CLOSE c_gsc;
59 RETURN TRUE;
60 EXCEPTION
61 WHEN OTHERS THEN
62 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
63 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_VAL_GSC.stap_val_gsc_sdt');
64 IGS_GE_MSG_STACK.ADD;
65 App_Exception.Raise_Exception;
66 END;
67 END stap_val_gsc_sdt;
68 --
69 -- Validate the update of government snapshot control snapshot date time.
70 FUNCTION stap_val_gsc_sdt_upd(
71 p_submission_yr IN NUMBER ,
72 p_submission_number IN NUMBER ,
73 p_message_name OUT NOCOPY VARCHAR2 )
74 RETURN BOOLEAN AS
75 BEGIN
76 DECLARE
77 v_submission_yr IGS_ST_GVT_SPSHT_CTL.submission_yr%TYPE;
78 CURSOR c_gsc(
79 cp_submission_yr IGS_ST_GVT_SPSHT_CTL.submission_yr%TYPE,
80 cp_submission_number IGS_ST_GVT_SPSHT_CTL.submission_number%TYPE) IS
81 SELECT gsc.submission_yr
82 FROM IGS_ST_GVT_SPSHT_CTL gsc
83 WHERE gsc.submission_yr = cp_submission_yr AND
84 gsc.submission_number = cp_submission_number AND
85 gsc.completion_dt IS NOT NULL;
86 BEGIN
87 -- Validate update of the IGS_ST_GVT_SPSHT_CTL.ess_snapshot_dt_time.
88 -- The IGS_ST_GVT_SPSHT_CTL.ess_snapshot_dt_time cannot be updated if the
89 -- govt_snapshot_ctlcompletion_dt is set.
90 p_message_name := NULL;
91 -- Retrieve the Government Snapshot control data.
92 OPEN c_gsc(
93 p_submission_yr,
94 p_submission_number);
95 FETCH c_gsc INTO v_submission_yr;
96 IF(c_gsc%FOUND = TRUE) THEN
97 CLOSE c_gsc;
98 p_message_name := 'IGS_ST_GOVT_SNAPSHOT_COMPLETE';
99 RETURN FALSE;
100 END IF;
101 CLOSE c_gsc;
102 RETURN TRUE;
103 EXCEPTION
104 WHEN OTHERS THEN
105 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
106 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_VAL_GSC.stap_val_gsc_sdt_upd');
107 IGS_GE_MSG_STACK.ADD;
108 App_Exception.Raise_Exception;
109 END;
110 END stap_val_gsc_sdt_upd;
111 END IGS_ST_VAL_GSC;
[Home] [Help]