1 PACKAGE BODY iby_formula_pkg AS
2 /*$Header: ibyformb.pls 115.12 2002/11/19 00:20:02 jleybovi ship $*/
3
4 /*
5 ** Name : getPayeeFormula
6 ** Purpose : Retrieves the payee Formula records into o_RiskFormula
7 ** corresponding to payeeid.
8 */
9 PROCEDURE getPayeeFormulas( i_payeeid IN VARCHAR2,
10 o_RiskFormula out nocopy formula_table)
11 IS
12
13 CURSOR c_formulas(ci_payeeid iby_risk_formulas.payeeid%TYPE) is
14 SELECT risk_formula_id, formula_name, description, implicit_flag
15 FROM iby_risk_formulas
16 WHERE payeeid = ci_payeeid
17 order by creation_date;
18
19 l_count INTEGER;
20
21 BEGIN
22
23 /*
24 ** If the cursor is already open close the cursor
25 ** and then call open, otherwise SQL raises an exception.
26 */
27 IF ( c_formulas%ISOPEN ) THEN
28 CLOSE c_formulas;
29 END IF;
30
31
32 l_count := 0;
33
34 /*
35 ** open the cursor in for loop and load all the formulas
36 ** associated with the payeeid passed.
37 */
38
39 FOR i in c_formulas(i_payeeid) LOOP
40 o_RiskFormula(l_count).id := i.risk_formula_id;
41 o_RiskFormula(l_count).name := i.formula_name;
42 o_RiskFormula(l_count).description := i.description;
43 o_RiskFormula(l_count).flag := i.implicit_flag;
44 l_count := l_count + 1;
45 END LOOP;
46
47 END getPayeeFormulas;
48
49 /*
50 ** Name : createFormula
51 ** Purpose : Creates a new formula in the database for the payee Id passed
52 ** and returns back the formula Is as output parameter.
53 */
54
55 Procedure createFormula( i_payeeId in VARCHAR2,
56 i_name in VARCHAR2,
57 i_description in VARCHAR2,
58 i_flag in integer,
59 i_count in integer,
60 i_Factors in factor_table,
61 o_id out nocopy integer)
62 IS
63
64 CURSOR c_formula_id is
65 SELECT IBY_RISK_FORMULAS_S.NEXTVAL
66 FROM DUAL;
67
68 CURSOR c_factor_id( ci_factor_name VARCHAR2) is
69 SELECT risk_factor_id
70 FROM iby_risk_factors
71 WHERE risk_factor_code = ci_factor_name;
72
73 l_formula_id integer;
74 l_factor_id integer;
75 i integer;
76
77 BEGIN
78
79 /*
80 ** Check if this Formula Name already exists with that Payee
81 ** or not. if exists, then raise an exception.
82 */
83 SELECT count(*) INTO i
84 FROM iby_risk_formulas
85 WHERE formula_name = i_name
86 AND payeeid = i_payeeid;
87
88 IF ( i <> 0 ) then
89 RAISE_APPLICATION_ERROR(-20000, 'IBY_204227#FORMULANAME=' ||
90 i_name || '#' );
91 END IF;
92
93
94 /*
95 ** close the cursor, if it is already open.
96 */
97 IF ( c_formula_id%isopen ) THEN
98 CLOSE c_formula_id;
99 END IF;
100
101 /*
102 ** Get the next formula Id available from the sequence.
103 */
104 OPEN c_formula_id;
105 fetch c_formula_id into l_formula_id;
106 CLOSE c_formula_id;
107
108 -- Insert Factors.
109
110 i := i_count;
111
112 WHILE ( i > 0 ) LOOP
113 -- get Factor Id.
114 IF ( c_factor_id%ISOPEN ) THEN
115 CLOSE c_factor_id;
116 END IF;
117 OPEN c_factor_id(i_factors(i).name);
118 FETCH c_factor_id into l_factor_id;
119 -- If the factor Id is not found then raise exception.
120 IF ( c_factor_id%NOTFOUND ) then
121 CLOSE c_factor_id;
122 RAISE_APPLICATION_ERROR(-20000, 'IBY_204226#FACTORNAME='
123 || i_factors(i).name || '#');
124 END IF;
125
126 CLOSE c_factor_id;
127
128 -- insert the record into iby_risk_formula_item;
129
130 INSERT INTO iby_risk_formula_item
131 (risk_factor_id, risk_formula_id,
132 weight,object_version_number,
133 last_update_date, last_updated_by,
134 creation_date, created_by)
135 VALUES ( l_factor_id, l_formula_id,
136 i_factors(i).weight, 1,
137 sysdate, fnd_global.user_id,
138 sysdate, fnd_global.user_id);
139
140 -- decrement index.
141 i := i - 1;
142
143 END LOOP;
144
145 -- make entry in the formulas table once all the factors are
146 -- inserted.
147 INSERT INTO iby_risk_formulas
148 ( risk_formula_id, formula_name, description,
149 implicit_flag, payeeid,object_version_number,
150 last_update_date, last_updated_by,
151 creation_date, created_by)
152 VALUES ( l_formula_id, i_name, i_description,
153 i_flag, i_payeeId, 1,
154 sysdate, fnd_global.user_id,
155 sysdate, fnd_global.user_id);
156 -- commit the changes
157 commit;
158
159 END createFormula;
160
161 /*
162 ** Name : modifyFormula
163 ** Purpose : modifies the formula information corresponding to the
164 ** formulaId with the passed information in the database.
165 */
166 PROCEDURE modifyFormula( i_id IN INTEGER,
167 i_name IN VARCHAR2,
168 i_description IN VARCHAR2,
169 i_flag in integer,
170 i_count IN INTEGER,
171 i_Factors IN factor_table)
172 IS
173
174 CURSOR c_factor_id( ci_factor_name VARCHAR2) is
175 SELECT risk_factor_id
176 FROM iby_risk_factors
177 WHERE risk_factor_code = ci_factor_name;
178
179 l_formula_id integer;
180 l_factor_id integer;
181 i integer;
182
183 BEGIN
184
185 /*
186 ** check if the modified name is already exists in
187 ** the formula list that is associated with that payeeid.
188 ** The following query retrieves count of no of rows
189 ** whose formula_name is same as the name passed in, and
190 ** payee id corresponding to it is same as the one associated
191 ** with the formula id.
192 */
193 SELECT count(*) INTO i
194 FROM iby_risk_formulas
195 WHERE formula_name = i_name
196 and risk_formula_id <> i_id
197 and payeeid = ( SELECT payeeid
198 FROM iby_risk_formulas
199 WHERE risk_formula_id = i_id );
200
201 /*
202 ** if count is not zero, that means already some
203 ** row present with the same name. So, raise an exception
204 ** for violating unique name constraint.
205 */
206 IF ( i <> 0 ) then
207 RAISE_APPLICATION_ERROR(-20000, 'IBY_204227#FORMULANAME=' ||
208 i_name || '#' );
209 END IF;
210
211 -- Delete Existing Factors.
212
213 DELETE iby_risk_formula_item
214 WHERE risk_formula_id = i_id;
215
216 -- Insert new Factors.
217
218 i := i_count;
219
220 WHILE ( i > 0 ) LOOP
221 -- get Factor Id.
222 IF ( c_factor_id%ISOPEN ) THEN
223 CLOSE c_factor_id;
224 END IF;
225 OPEN c_factor_id(i_factors(i).name);
226 FETCH c_factor_id into l_factor_id;
227 IF ( c_factor_id%NOTFOUND ) then
228 CLOSE c_factor_id;
229 RAISE_APPLICATION_ERROR(-20000, 'IBY_204226#FACTORNAME='
230 || i_factors(i).name || '#');
231 END IF;
232
233 CLOSE c_factor_id;
234
235 -- insert the record into iby_risk_formula_item;
236
237 INSERT INTO iby_risk_formula_item
238 (risk_factor_id, risk_formula_id, weight, object_version_number,
239 last_update_date, last_updated_by, creation_date, created_by)
240 VALUES ( l_factor_id, i_id, i_factors(i).weight, 1,
241 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id);
242
243 -- decrement index.
244 i := i - 1;
245
246 END LOOP;
247
248 -- update the formula information.
249
250 UPDATE iby_risk_formulas
251 SET formula_name = i_name,
252 description = i_description,
253 implicit_flag = i_flag,
254 last_update_date = sysdate,
255 last_updated_by = fnd_global.user_id
256 WHERE risk_formula_id = i_id;
257
258 -- if no of rows updated is zero then raise an exception.
259
260 if ( SQL%ROWCOUNT = 0 ) then
261 raise_application_error(-20000, 'IBY_204225#');
262 end if;
263 -- commit the changes
264 commit;
265 END modifyFormula;
266
267 /*
268 ** Name : deleteFormula
269 ** Purpose : deletes the formula corresponding to the formulaId
270 ** from the database.
271 */
272 PROCEDURE deleteFOrmula( i_id in integer)
273 IS
274 BEGIN
275
276 -- delete the FAcotr items from the iby_risk_formula_item.
277 DELETE iby_risk_formula_item
278 WHERE risk_formula_id = i_id;
279
280 -- delete the Formula entry from iby_risk_formulas
281 DELETE iby_risk_formulas
282 WHERE risk_formula_id = i_id;
283
284 if ( SQL%ROWCOUNT = 0 ) then
285 raise_application_error(-20000, 'IBY_204225#');
286 end if;
287 -- commit the changes
288 commit;
289
290 END deleteFormula;
291
292 /*
293 ** Name : loadFormula
294 ** Purpose : Retrievs the Formula information and loads the
295 ** factors of the formula into o_Factors
296 ** corresponding to formulaId.
297 */
298 PROCEDURE loadFormula( i_formulaId IN INTEGER,
299 o_name out nocopy VARCHAR2,
300 o_description out nocopy VARCHAR2,
301 o_flag out nocopy integer,
302 o_Factors out nocopy factor_table)
303 IS
304
305 CURSOR c_formulas(ci_risk_formula_id iby_risk_formulas.risk_formula_id%TYPE) is
306 SELECT formula_name, description, implicit_flag
307 FROM iby_risk_formulas
308 WHERE risk_formula_id = ci_risk_formula_id;
309
310 CURSOR c_factor(ci_factorid iby_risk_formula_item.risk_formula_id%type) IS
311 SELECT risk_factor_code, weight
312 FROM iby_risk_factors irf, iby_risk_formula_item irft
313 WHERE irft.risk_formula_id = i_formulaId
314 AND irf.risk_factor_id = irft.risk_factor_id;
315
316 l_cnt integer;
317
318 BEGIN
319
320 IF ( c_formulas%ISOPEN ) THEN
321 CLOSE c_formulas;
322 END IF;
323
324 IF ( c_factor%ISOPEN ) THEN
325 CLOSE c_factor;
326 END IF;
327
328 -- load formula information first and then factors information
329 -- associated with that formula id.
330 open c_formulas(i_formulaid);
331 fetch c_formulas into o_name, o_description, o_flag;
332
333 -- if there are no risk formulas.
334 if ( c_formulas%NOTFOUND ) then
335 close c_formulas;
336 raise_application_error(-20000, 'IBY_204225#');
337 end if;
338
339 close c_formulas;
340
341 l_cnt := 0;
342
343 FOR i IN c_factor(i_formulaid) LOOP
344 o_factors(l_cnt).name := i.risk_factor_code;
345 o_factors(l_cnt).weight := i.weight;
346 l_cnt := l_cnt + 1;
347 END LOOP;
348
349 END loadFormula;
350
351 END iby_formula_pkg;
352
[Home] [Help]