1 PACKAGE BODY IGS_FI_VAL_ERR AS
2 /* $Header: IGSFI21B.pls 115.3 2002/11/29 00:19:01 nsidana ship $ */
3 --
4 -- Ensure elements range rate can be created.
5 FUNCTION finp_val_err_create(
6 p_fee_type IN VARCHAR2 ,
7 p_fee_cal_type IN VARCHAR2 ,
8 p_fee_ci_sequence_number IN NUMBER ,
9 p_s_relation_type IN VARCHAR2 ,
10 p_fee_cat IN VARCHAR2 ,
11 p_range_number IN NUMBER ,
12 p_rate_number IN NUMBER ,
13 p_message_name OUT NOCOPY VARCHAR2 )
14 RETURN BOOLEAN AS
15 gv_other_detail VARCHAR2(255);
16 BEGIN -- finp_val_err_create
17 -- Validate that the IGS_FI_ELM_RANGE_RT record can be created.
18 -- Unable to be created if the IGS_FI_ELM_RANGE or IGS_FI_FEE_AS_RATE
19 -- records it is based on, are logically deleted.
20 DECLARE
21 CURSOR c_er IS
22 SELECT 'x'
23 FROM IGS_FI_ELM_RANGE er
24 WHERE er.fee_type = p_fee_type AND
25 er.fee_cal_type = p_fee_cal_type AND
26 er.fee_ci_sequence_number = p_fee_ci_sequence_number AND
27 er.s_relation_type = p_s_relation_type AND
28 NVL(er.fee_cat, 'NULL') = NVL(p_fee_cat, 'NULL') AND
29 er.range_number = p_range_number AND
30 er.logical_delete_dt IS NOT NULL;
31 CURSOR c_far IS
32 SELECT 'x'
33 FROM IGS_FI_FEE_AS_RATE far
34 WHERE far.fee_type = p_fee_type AND
35 far.fee_cal_type = p_fee_cal_type AND
36 far.fee_ci_sequence_number = p_fee_ci_sequence_number AND
37 far.s_relation_type = p_s_relation_type AND
38 NVL(far.fee_cat, 'NULL') = NVL(p_fee_cat, 'NULL') AND
39 far.rate_number = p_rate_number AND
40 far.logical_delete_dt IS NOT NULL;
41 v_far_exists VARCHAR2(1);
42 v_er_exists VARCHAR2(1);
43 BEGIN
44 -- Set the default message number
45 p_message_name := Null;
46 -- 1. Check parameters :
47 IF p_fee_type IS NULL OR
48 p_fee_cal_type IS NULL OR
49 p_fee_ci_sequence_number IS NULL OR
50 p_s_relation_type IS NULL OR
51 p_range_number IS NULL OR
52 p_rate_number IS NULL THEN
53 RETURN TRUE;
54 END IF;
55 -- 2. Check that the parent IGS_FI_ELM_RANGE record is not logically deleted
56 OPEN c_er;
57 FETCH c_er INTO v_er_exists;
58 IF c_er%FOUND THEN
59 CLOSE c_er;
60 p_message_name := 'IGS_FI_ELERNG_RATE_NOTCREATED';
61 RETURN FALSE;
62 END IF;
63 CLOSE c_er;
64 -- 3. Check that the parent IGS_FI_FEE_AS_RATE record is not logically deleted
65 OPEN c_far;
66 FETCH c_far INTO v_far_exists;
67 IF c_far%FOUND THEN
68 CLOSE c_far;
69 p_message_name := 'IGS_FI_ELERNG_RATE_FEEASS_RAT';
70 RETURN FALSE;
71 END IF;
72 CLOSE c_far;
73 -- 4. Return no error
74 RETURN TRUE;
75 EXCEPTION
76 WHEN OTHERS THEN
77 IF c_er%ISOPEN THEN
78 CLOSE c_er;
79 END IF;
80 IF c_far%ISOPEN THEN
81 CLOSE c_far;
82 END IF;
83 RAISE;
84 END;
85 END finp_val_err_create;
86 --
87 -- Ensure only one elements range rate is active.
88 FUNCTION finp_val_err_active(
89 p_fee_type IN VARCHAR2 ,
90 p_fee_cal_type IN VARCHAR2 ,
91 p_fee_ci_sequence_number IN NUMBER ,
92 p_fee_cat IN VARCHAR2 ,
93 p_range_number IN NUMBER ,
94 p_rate_number IN NUMBER ,
95 p_s_relation_type IN VARCHAR2 ,
96 p_create_dt IN DATE ,
97 p_message_name OUT NOCOPY VARCHAR2 )
98 RETURN BOOLEAN AS
99 gv_other_detail VARCHAR2(255);
100 BEGIN -- finp_val_err_active
101 -- Validate that there are no other "open-ended" element_range_rate records
102 -- for the nominated IGS_FI_ELM_RANGE record
103 DECLARE
104 v_err_exists CHAR;
105 v_ret_val BOOLEAN DEFAULT TRUE;
106 CURSOR c_err IS
107 SELECT 'x'
108 FROM IGS_FI_ELM_RANGE_RT err
109 WHERE err.fee_type = p_fee_type AND
110 err.fee_cal_type = p_fee_cal_type AND
111 err.fee_ci_sequence_number = p_fee_ci_sequence_number AND
112 err.s_relation_type = p_s_relation_type AND
113 NVL(err.fee_cat, 'NULL')= NVL(p_fee_cat, 'NULL')AND
114 err.range_number = p_range_number AND
115 err.rate_number = p_rate_number AND
116 err.create_dt <> p_create_dt AND
117 err.logical_delete_dt IS NULL;
118 BEGIN
119 p_message_name := Null;
120 -- Check parameters.
121 IF ( p_fee_type IS NULL OR
122 p_fee_cal_type IS NULL OR
123 p_fee_ci_sequence_number IS NULL OR
124 p_range_number IS NULL OR
125 p_rate_number IS NULL OR
126 p_s_relation_type IS NULL OR
127 p_create_dt IS NULL) THEN
128 RETURN TRUE;
129 END IF;
130 OPEN c_err;
131 FETCH c_err INTO v_err_exists;
132 IF (c_err%FOUND) THEN
133 p_message_name := 'IGS_FI_ELERNG_RATE_ACTIVE';
134 v_ret_val := FALSE;
135 END IF;
136 CLOSE c_err;
137 RETURN v_ret_val;
138 END;
139 END finp_val_err_active;
140 --
141 -- Ensure elements range rate can be created.
142 FUNCTION finp_val_err_ins(
143 p_fee_type IN VARCHAR2 ,
144 p_fee_cal_type IN VARCHAR2 ,
145 p_fee_ci_sequence_number IN NUMBER ,
146 p_s_relation_type IN VARCHAR2 ,
147 p_rate_number IN NUMBER ,
148 p_message_name OUT NOCOPY VARCHAR2 )
149 RETURN BOOLEAN AS
150 gv_other_detail VARCHAR2(255);
151 BEGIN -- finp_val_err_ins
152 -- validate IGS_FI_ELM_RANGE_RT records can only be matched to a IGS_FI_FEE_AS_RATE
153 -- defined at
154 -- the same level as the IGS_FI_ELM_RANGE on which it is based
155 DECLARE
156 CURSOR c_far IS
157 SELECT far.rate_number
158 FROM IGS_FI_FEE_AS_RATE far
159 WHERE far.fee_type = p_fee_type AND
160 far.fee_cal_type = p_fee_cal_type AND
161 far.fee_ci_sequence_number = p_fee_ci_sequence_number AND
162 far.s_relation_type = p_s_relation_type AND
163 far.rate_number = p_rate_number AND
164 far.logical_delete_dt IS NULL;
165 v_rate_number IGS_FI_ELM_RANGE_RT.rate_number%TYPE;
166 BEGIN
167 --- Set the default message number
168 p_message_name := Null;
169 IF p_fee_type IS NULL OR
170 p_fee_cal_type IS NULL OR
171 p_fee_ci_sequence_number IS NULL OR
172 p_s_relation_type IS NULL OR
173 p_rate_number IS NULL THEN
174 RETURN TRUE;
175 END IF;
176 OPEN c_far;
177 FETCH c_far INTO v_rate_number;
178 IF (c_far%NOTFOUND) THEN
179 p_message_name := 'IGS_FI_FEEASSRATE_NOT_DENF';
180 CLOSE c_far;
181 RETURN FALSE;
182 END IF;
183 CLOSE c_far;
184 RETURN TRUE;
185 END;
186 END finp_val_err_ins;
187 END IGS_FI_VAL_ERR;
[Home] [Help]