1 PACKAGE BODY IGS_FI_VAL_PFE AS
2 /* $Header: IGSFI37B.pls 115.7 2002/11/29 00:22:48 nsidana ship $ */
3 --
4 -- nalkumar 30-Nov-2001 Removed the function finp_val_pfe_status and finp_val_pfes_closed from this package.
5 -- This is as per the SFCR015-HOLDS DLD. Bug:2126091
6 --
7 --msrinivi Bug 1956374 Removed finp_val_encmb_eff
8 --bayadav 20-DEC-2001 Removed the function finp_val_sca_status from this package.
9 -- This is as per the SFCR015-HOLDS DLD. Bug:2126091
10 --
11 -- Validate the IGS_PE_PERSON does not have an active encumbrance of this type.
12 FUNCTION finp_val_prsn_encmb(
13 p_person_id IN NUMBER ,
14 p_encumbrance_type IN VARCHAR2 ,
15 p_fee_encumbrance_dt IN DATE ,
16 p_message_name OUT NOCOPY VARCHAR2)
17 RETURN BOOLEAN AS
18 gv_other_detail VARCHAR2(255);
19 BEGIN -- finp_val_prsn_encmb
20 -- Validates that IGS_PE_PERSON does not currently have an active IGS_PE_PERS_ENCUMB
21 -- record matching that which will be applied if the IGS_PE_PND_FEE_ENCUM
22 -- is authorised
23 DECLARE
24 cst_academic CONSTANT VARCHAR2(8) := 'ACADEMIC';
25 v_rec_found BOOLEAN DEFAULT FALSE;
26 v_s_encmb_cat IGS_FI_ENCMB_TYPE.s_encumbrance_cat%TYPE;
27 v_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE;
28 CURSOR c_encmb_type (
29 cp_encumbrance_type IGS_FI_ENCMB_TYPE.encumbrance_type%TYPE) IS
30 SELECT et.s_encumbrance_cat
31 FROM IGS_FI_ENCMB_TYPE et
32 WHERE et.encumbrance_type = cp_encumbrance_type;
33 CURSOR c_prsn_encmb (
34 cp_person_id IGS_PE_PERS_ENCUMB.person_id%TYPE,
35 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
36 cp_fee_encumbrance_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE) IS
37 SELECT 'x'
38 FROM IGS_PE_PERS_ENCUMB pe
39 WHERE pe.person_id = cp_person_id AND
40 pe.encumbrance_type = cp_encumbrance_type AND
41 trunc(pe.start_dt) <=
42 trunc(cp_fee_encumbrance_dt) AND
43 (pe.expiry_dt IS NULL OR
44 trunc(pe.expiry_dt) >
45 trunc(cp_fee_encumbrance_dt));
46 CURSOR c_prsn_encmb_1 (
47 cp_person_id IGS_PE_PERS_ENCUMB.person_id%TYPE,
48 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
49 cp_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE) IS
50 SELECT 'x'
51 FROM IGS_PE_PERS_ENCUMB pe
52 WHERE pe.person_id = cp_person_id AND
53 pe.ENCUMBRANCE_TYPE = cp_encumbrance_type AND
54 trunc(pe.start_dt)= trunc(cp_start_dt);
55 BEGIN
56 p_message_name := null;
57 -- Check parameters
58 IF(p_person_id IS NULL OR
59 p_encumbrance_type IS NULL OR
60 p_fee_encumbrance_dt IS NULL) THEN
61 RETURN TRUE;
62 END IF;
63 -- Evaluate the fee_encumbrance_dt.
64 -- If it is less then the current date, set it to the current date.
65 -- This is necessary as it is used as the start date value in the
66 -- IGS_PE_PERS_ENCUMB record and if less than the current date,
67 -- an error will result.
68 IF(p_fee_encumbrance_dt < sysdate) THEN
69 v_start_dt := SYSDATE;
70 ELSE
71 v_start_dt := p_fee_encumbrance_dt;
72 END IF;
73 -- Check the encumbrance category for the encumbrance type parameter
74 -- If it is of type 'ACADEMIC' then no further processing is required as
75 -- encumbrances which fall into this category are permitted to exist in
76 -- multiple open records but not with the same start date
77 OPEN c_encmb_type(
78 p_encumbrance_type);
79 FETCH c_encmb_type INTO v_s_encmb_cat;
80 IF(v_s_encmb_cat <> cst_academic) THEN
81 -- Check if an active encumbrance exists
82 FOR v_prsn_encmb_rec IN c_prsn_encmb(
83 p_person_id,
84 p_encumbrance_type,
85 v_start_dt) LOOP
86 v_rec_found := TRUE;
87 END LOOP;
88 IF(v_rec_found = TRUE) THEN
89 -- IGS_PE_PERSON has an encumbrance of the same type which is active
90 CLOSE c_encmb_type;
91 p_message_name:= 'IGS_FI_STUD_ACTIVE_ENRPRG';
92 RETURN FALSE;
93 ELSE
94 CLOSE c_encmb_type;
95 v_rec_found := FALSE;
96 -- Check that if created, the encumbrance will not cause a duplicate
97 -- record conflict by having the same start date as an existing record.
98 FOR v_prsn_encmb_rec IN c_prsn_encmb_1(
99 p_person_id,
100 p_encumbrance_type,
101 v_start_dt) LOOP
102 v_rec_found := TRUE;
103 END LOOP;
104 IF(v_rec_found = TRUE) THEN
105 p_message_name:= 'IGS_AD_NOTBE_NOTQUALIF_OFRMAD';
106 RETURN FALSE;
107 ELSE
108 RETURN TRUE;
109 END IF;
110 END IF;
111 ELSE
112 -- Having identified the encumbrance category as ?ACADEMIC? check that
113 -- if created it will not cause a duplicate record conflict by having the
114 -- same start date as an existing record.
115 FOR v_prsn_encmb_rec IN c_prsn_encmb_1(
116 p_person_id,
117 p_encumbrance_type,
118 v_start_dt) LOOP
119 v_rec_found := TRUE;
120 END LOOP;
121 IF(v_rec_found = TRUE) THEN
122 CLOSE c_encmb_type;
123 p_message_name:= 'IGS_AD_PREF_NOTALLOW_ADMAPL';
124 RETURN FALSE;
125 ELSE
126 CLOSE c_encmb_type;
127 RETURN TRUE;
128 END IF;
129 END IF;
130 RETURN TRUE;
131 END;
132 END finp_val_prsn_encmb;
133
134 -- Removed the function finp_val_sca_status from this package
135 -- as per the SFCR015-HOLDS DLD. Bug:2126091
136
137 END IGS_FI_VAL_PFE;
[Home] [Help]