1 PACKAGE BODY igs_fi_val_uft AS
2 /* $Header: IGSFI42B.pls 115.8 2003/10/28 05:57:53 uudayapr ship $ */
3
4 -- uudayapr 14-oct-2003
5 -- AS part of the Enh#3117341 added cst_audit in function finp_val_uft_ins
6 -- and also modified the If Condition to allow the Audit as a valid
7 -- unit fee category.
8 -- As part of the bug# 2690024 removed the function crsp_val_uv_sys_sts
9 -- As part of the bug# 2690024 removed the function crsp_val_ucl_closed
10 -- As part of the bug# 2690024 removed the function crsp_val_uv_active
11 -- Ensure unit fee triggers can be created.
12 FUNCTION finp_val_uft_ins(
13 p_fee_type IN VARCHAR2 ,
14 p_message_name OUT NOCOPY VARCHAR2)
15 RETURN BOOLEAN AS
16 gv_other_detail VARCHAR2(255);
17 BEGIN -- finp_val_uft_ins
18 -- Validate IGS_FI_UNIT_FEE_TRG.IGS_FI_FEE_TYPE.s_fee_trigger_cat = IGS_PS_UNIT or COMPOSITE
19 -- otherwise IGS_PS_UNIT fee triggers cannot be defined.
20 DECLARE
21 CURSOR c_ft(
22 cp_fee_type IGS_FI_FEE_TYPE.fee_type%TYPE) IS
23 SELECT s_fee_trigger_cat
24 FROM IGS_FI_FEE_TYPE
25 WHERE fee_type = cp_fee_type;
26 v_ft_rec c_ft%ROWTYPE;
27 cst_unit CONSTANT VARCHAR2(10) := 'UNIT';
28 cst_composite CONSTANT VARCHAR2(10) := 'COMPOSITE';
29 --Enh#3117341 added this for checking for AUDIT System fee trigger category.
30 cst_audit CONSTANT VARCHAR2(10) := 'AUDIT';
31 BEGIN
32 -- Set the default message number
33 p_message_name := null;
34 -- Check parameters
35 IF p_fee_type IS NULL THEN
36 RETURN TRUE;
37 END IF;
38 -- Get the system fee trigger category of the fee_type.
39 OPEN c_ft (p_fee_type);
40 FETCH c_ft INTO v_ft_rec;
41 IF c_ft%NOTFOUND THEN
42 CLOSE c_ft;
43 RETURN TRUE;
44 END IF;
45 CLOSE c_ft;
46 --Enh# 3117341 Added 'AUDIT' also as a valid system fee trigger category for
47 --creating the unit fee trigger category.
48 IF v_ft_rec.s_fee_trigger_cat <> cst_unit AND
49 v_ft_rec.s_fee_trigger_cat <> cst_composite AND
50 v_ft_rec.s_fee_trigger_cat <> cst_audit
51 THEN
52 p_message_name:= 'IGS_FI_UNIT_FEETRG_UNIT_COMPO';
53 RETURN FALSE;
54 END IF;
55 -- Return the default value
56 RETURN TRUE;
57 END;
58 END finp_val_uft_ins;
59 --
60 -- Validate IGS_PS_UNIT fee trigger can belong to a fee trigger group.
61 FUNCTION finp_val_uft_ftg(
62 p_fee_type IN IGS_FI_FEE_TYPE_ALL.fee_type%TYPE ,
63 p_fee_trigger_group_num IN NUMBER ,
64 p_message_name OUT NOCOPY VARCHAR2)
65 RETURN BOOLEAN AS
66 gv_other_detail VARCHAR2(255);
67 BEGIN -- finp_val_uft_ftg
68 -- Validate IGS_FI_UNIT_FEE_TRG can belong to a IGS_FI_FEE_TRG_GRP
69 DECLARE
70 CURSOR c_ft(
71 cp_fee_type IGS_FI_FEE_TYPE.fee_type%TYPE) IS
72 SELECT s_fee_trigger_cat
73 FROM IGS_FI_FEE_TYPE
74 WHERE fee_type = cp_fee_type;
75 v_ft_rec c_ft%ROWTYPE;
76 cst_composite CONSTANT VARCHAR2(10) := 'COMPOSITE';
77 BEGIN
78 p_message_name := null;
79 IF(p_fee_type IS NULL OR
80 p_fee_trigger_group_num IS NULL) THEN
81 RETURN TRUE;
82 END IF;
83 -- Get the system fee trigger category of the fee_type.
84 OPEN c_ft (p_fee_type);
85 FETCH c_ft INTO v_ft_rec;
86 IF c_ft%NOTFOUND THEN
87 CLOSE c_ft;
88 RETURN TRUE;
89 END IF;
90 CLOSE c_ft;
91 IF v_ft_rec.s_fee_trigger_cat <> cst_composite THEN
92 p_message_name:= 'IGS_FI_UNIT_FEE_TRG_COMPOSITE';
93 RETURN FALSE;
94 END IF;
95 RETURN TRUE;
96 END;
97 END finp_val_uft_ftg;
98 --
99 -- Ensure only one open IGS_FI_UNIT_FEE_TRG record exists.
100 FUNCTION finp_val_uft_open(
101 p_fee_cat IN IGS_FI_UNIT_FEE_TRG.fee_cat%TYPE ,
102 p_fee_cal_type IN IGS_FI_UNIT_FEE_TRG.fee_cal_type%TYPE ,
103 p_fee_ci_sequence_number IN NUMBER ,
104 p_fee_type IN IGS_FI_UNIT_FEE_TRG.fee_type%TYPE ,
105 p_unit_cd IN IGS_FI_UNIT_FEE_TRG.unit_cd%TYPE ,
106 p_sequence_number IN NUMBER ,
107 p_version_number IN IGS_FI_UNIT_FEE_TRG.version_number%TYPE ,
108 p_cal_type IN IGS_FI_UNIT_FEE_TRG.cal_type%TYPE ,
109 p_ci_sequence_number IN NUMBER ,
110 p_unit_class IN IGS_FI_UNIT_FEE_TRG.unit_class%TYPE ,
111 p_location_cd IN IGS_FI_UNIT_FEE_TRG.location_cd%TYPE ,
112 p_create_dt IN IGS_FI_UNIT_FEE_TRG.create_dt%TYPE ,
113 p_fee_trigger_group_number IN NUMBER ,
114 p_message_name OUT NOCOPY VARCHAR2)
115 RETURN BOOLEAN AS
116 gv_other_detail VARCHAR2(255);
117 BEGIN -- finp_val_uft_open
118 -- Validate that there are no other "open" IGS_FI_UNIT_FEE_TRG records for
119 -- the nominated unit_cd details and the same parent IGS_FI_F_CAT_FEE_LBL.
120 DECLARE
121 CURSOR c_uft IS
122 SELECT 'x'
123 FROM IGS_FI_UNIT_FEE_TRG uft
124 WHERE uft.fee_cat = p_fee_cat AND
125 uft.fee_cal_type = p_fee_cal_type AND
126 uft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
127 uft.fee_type = p_fee_type AND
128 uft.unit_cd = p_unit_cd AND
129 uft.sequence_number <> NVL(p_sequence_number,0) AND
130 NVL(uft.version_number,0) = NVL(p_version_number,0) AND
131 NVL(uft.cal_type,'NULL') = NVL(p_cal_type,'NULL') AND
132 NVL(uft.ci_sequence_number,0) = NVL(p_ci_sequence_number,0) AND
133 NVL(uft.location_cd,'NULL') = NVL(p_location_cd,'NULL') AND
134 NVL(uft.unit_class,'NULL') = NVL(p_unit_class,'NULL') AND
135 uft.create_dt <> p_create_dt AND
136 NVL(uft.fee_trigger_group_number,0) = NVL(p_fee_trigger_group_number,0) AND
137 uft.logical_delete_dt IS NULL;
138 v_check CHAR;
139 BEGIN
140 --- Set the default message number
141 p_message_name := null;
142 IF p_fee_cat IS NULL OR
143 p_fee_cal_type IS NULL OR
144 p_fee_ci_sequence_number IS NULL OR
145 p_fee_type IS NULL OR
146 p_unit_cd IS NULL OR
147 p_create_dt IS NULL THEN
148 RETURN TRUE;
149 END IF;
150 OPEN c_uft;
151 FETCH c_uft INTO v_check;
152 IF (c_uft%FOUND) THEN
153 CLOSE c_uft;
154 p_message_name:= 'IGS_GE_DUPLICATE_VALUE';
155 RETURN FALSE;
156 END IF;
157 CLOSE c_uft;
158 RETURN TRUE;
159 END;
160 END finp_val_uft_open;
161 --
162 -- Warn if no IGS_PS_UNIT offering option exists for the specified options.
163 FUNCTION finp_val_uft_uoo(
164 p_unit_cd IN VARCHAR2 ,
165 p_cal_type IN VARCHAR2 ,
166 p_ci_sequence_number IN NUMBER ,
167 p_location_cd IN VARCHAR2 ,
168 p_unit_class IN VARCHAR2 ,
169 p_message_name OUT NOCOPY VARCHAR2)
170 RETURN BOOLEAN AS
171 gv_other_detail VARCHAR2(255);
172 BEGIN
173 DECLARE
174 v_dummy VARCHAR2(1);
175 CURSOR c_uoo IS
176 SELECT 'X'
177 FROM IGS_PS_UNIT_OFR_OPT uoo
178 WHERE uoo.unit_cd = p_unit_cd AND
179 (p_cal_type IS NULL OR
180 uoo.cal_type = p_cal_type) AND
181 (p_ci_sequence_number IS NULL OR
182 (uoo.cal_type = p_cal_type AND
183 uoo.ci_sequence_number = p_ci_sequence_number)) AND
184 (p_location_cd IS NULL OR
185 uoo.location_cd = p_location_cd) AND
186 (p_unit_class IS NULL OR
187 uoo.unit_class = p_unit_class);
188 BEGIN
189 -- 1. Check parameters
190 IF (p_unit_cd IS NULL) THEN
191 p_message_name := null;
192 RETURN TRUE;
193 END IF;
194 -- 2. Check for IGS_PS_UNIT_OFR_OPT records for the unit_cd,
195 -- location_cd, unit_class and cal_type
196 OPEN c_uoo;
197 FETCH c_uoo INTO v_dummy;
198 IF (c_uoo%NOTFOUND) THEN
199 CLOSE c_uoo;
200 p_message_name:= 'IGS_FI_UOO_NOT_EXISTS';
201 RETURN FALSE;
202 END IF;
203 CLOSE c_uoo;
204 p_message_name := null;
205 RETURN TRUE;
206 EXCEPTION
207 WHEN OTHERS THEN
208 IF (c_uoo%ISOPEN) THEN
209 CLOSE c_uoo;
210 END IF;
211 RAISE;
212 END;
213 END finp_val_uft_uoo;
214
215 END igs_fi_val_uft;
[Home] [Help]