1 PACKAGE BODY IGS_ST_VAL_ESS AS
2 /* $Header: IGSST05B.pls 115.5 2002/11/29 04:11:10 nsidana ship $ */
3
4 --
5 -- Validate no warnings exist for excluded pid,crs,unit records
6 FUNCTION stap_val_eswv_xandw(
7 p_snapshot_dt_time IN DATE ,
8 p_message_name OUT NOCOPY VARCHAR2)
9 RETURN BOOLEAN AS
10 gv_other_detail VARCHAR2(255);
11 BEGIN -- stap_val_eswv_xandw
12 -- This module validates that no warning (ie.'W') records exist int the
13 -- IGS_EN_ST_SNAPSHOT table for person_id, course_cd, unit_cd records
14 -- which have been excluded (ie.'X').
15 DECLARE
16 v_dummy VARCHAR2(1);
17 v_warning_found BOOLEAN DEFAULT FALSE;
18 CURSOR c_eswv_excluded IS
19 SELECT eswv_excluded.snapshot_dt_time,
20 eswv_excluded.ci_cal_type,
21 eswv_excluded.ci_sequence_number,
22 eswv_excluded.person_id,
23 eswv_excluded.course_version,
24 eswv_excluded.unit_version
25 FROM IGS_EN_ST_SNAPSHOT_WARNING_V eswv_excluded
26 WHERE eswv_excluded.snapshot_dt_time = p_snapshot_dt_time AND
27 eswv_excluded.govt_reportable_ind = 'X';
28 CURSOR c_eswv_warning (
29 cp_snapshot_dt_time
30 IGS_EN_ST_SNAPSHOT_WARNING_V.snapshot_dt_time%TYPE,
31 cp_ci_cal_type IGS_EN_ST_SNAPSHOT_WARNING_V.ci_cal_type%TYPE,
32 cp_ci_sequence_number
33 IGS_EN_ST_SNAPSHOT_WARNING_V.ci_sequence_number%TYPE,
34 cp_person_id IGS_EN_ST_SNAPSHOT_WARNING_V.person_id%TYPE,
35 cp_course_version
36 IGS_EN_ST_SNAPSHOT_WARNING_V.course_version%TYPE,
37 cp_unit_version IGS_EN_ST_SNAPSHOT_WARNING_V.unit_version%TYPE)
38 IS
39 SELECT 'X'
40 FROM IGS_EN_ST_SNAPSHOT_WARNING_V eswv_warning
41 WHERE eswv_warning.snapshot_dt_time = cp_snapshot_dt_time AND
42 eswv_warning.ci_cal_type = cp_ci_cal_type AND
43 eswv_warning.ci_sequence_number = cp_ci_sequence_number AND
44 eswv_warning.person_id = cp_person_id AND
45 eswv_warning.course_version = cp_course_version AND
46 eswv_warning.unit_version = cp_unit_version AND
47 eswv_warning.govt_reportable_ind = 'W';
48 BEGIN
49 -- Set the default message name
50 p_message_name := null;
51 FOR v_eswv_excluded IN c_eswv_excluded LOOP
52 OPEN c_eswv_warning (
53 v_eswv_excluded.snapshot_dt_time,
54 v_eswv_excluded.ci_cal_type,
55 v_eswv_excluded.ci_sequence_number,
56 v_eswv_excluded.person_id,
57 v_eswv_excluded.course_version,
58 v_eswv_excluded.unit_version);
59 FETCH c_eswv_warning INTO v_dummy;
60 IF c_eswv_warning%FOUND THEN
61 CLOSE c_eswv_warning;
62 p_message_name := 'IGS_ST_WARN_RECORD_EXISTS';
63 v_warning_found := TRUE;
64 exit;
65 END IF;
66 CLOSE c_eswv_warning;
67 END LOOP;
68 IF v_warning_found THEN
69 RETURN FALSE;
70 END IF;
71 RETURN TRUE ;
72 EXCEPTION
73 WHEN OTHERS THEN
74 IF c_eswv_excluded%ISOPEN THEN
75 CLOSE c_eswv_excluded;
76 END IF;
77 IF c_eswv_warning%ISOPEN THEN
78 CLOSE c_eswv_warning;
79 END IF;
80 RAISE;
81 END;
82 EXCEPTION
83 WHEN OTHERS THEN
84 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
85 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_VAL_ESS.stap_val_eswv_xandw');
86 IGS_GE_MSG_STACK.ADD;
87 App_Exception.Raise_Exception;
88 END stap_val_eswv_xandw;
89 END IGS_ST_VAL_ESS;
[Home] [Help]