[Home] [Help]
PACKAGE BODY: APPS.BEN_PD_FORMULA_PKG
Source
1 Package body ben_pd_formula_pkg as
2 /* $Header: beffnpkg.pkb 120.1 2006/05/02 16:38:51 ashrivas noship $ */
3
4 ---------------------------------------------------
5
6 PROCEDURE remove_formula_from_FF
7 ( p_formula_name in varchar2) is
8
9 begin
10 DELETE FROM ff_formulas_f
11 WHERE formula_name = p_formula_name;
12 exception
13 when no_data_found then
14 -- No record found to be deleted.
15 null;
16 end;
17
18 ---------------------------------------------------
19 FUNCTION copy_formula_to_FF
20 (
21 p_business_group_id in number,
22 p_legislation_code in varchar2,
23 p_formula_id in number,
24 p_formula_type_id in number,
25 p_formula_name in varchar2,
26 p_description in varchar2,
27 p_effective_start_date in date,
28 p_effective_end_date in date,
29 p_formula_text in long ) return number is
30
31 l_formula_id number(15);
32 l_rowid varchar2(255);
33 l_formula_name varchar2(255);
34 l_lastUpdateDate date;
35
36 CURSOR ffRow IS SELECT rowId FROM ff_formulas_f
37 WHERE formula_id = p_formula_id
38 and p_effective_start_date between effective_start_date and effective_end_date;
39
40 BEGIN
41 l_formula_name := p_formula_name;
42
43 if (p_formula_id IS NULL) THEN
44 select ff_formulas_s.nextval
45 into l_formula_id
46 from sys.dual;
47
48 ff_formulas_f_pkg.insert_row(
49 x_rowid => l_rowid,
50 x_formula_id => l_formula_id,
51 x_effective_start_date => p_effective_start_date,
52 x_effective_end_date => p_effective_end_date,
53 x_business_group_id => p_business_group_id,
54 x_legislation_code => p_legislation_code,
55 x_formula_type_id => p_formula_type_id,
56 x_formula_name => l_formula_name,
57 x_description => p_description,
58 x_formula_text => p_formula_text,
59 x_sticky_flag => 'Y',
60 x_last_update_date => l_lastUpdateDate
61 );
62 else
63 l_formula_id := p_formula_id;
64 open ffRow;
65 Fetch ffRow into l_rowid;
66 close ffRow;
67
68 ff_formulas_f_pkg.update_row(
69 x_rowid => l_rowid,
70 x_formula_id => l_formula_id,
71 x_effective_start_date => p_effective_start_date,
72 x_effective_end_date => p_effective_end_date,
73 x_business_group_id => p_business_group_id,
74 x_legislation_code => p_legislation_code,
75 x_formula_type_id => p_formula_type_id,
76 x_formula_name => p_formula_name,
77 x_description => p_description,
78 x_formula_text => p_formula_text,
79 x_sticky_flag => 'Y',
80 x_last_update_date => l_lastUpdateDate
81 );
82 end if;
83
84 return l_formula_id;
85 END;
86
87 FUNCTION copy_formula_STAGE_TO_FF
88 ( p_copy_entity_result_id IN number ) return number is
89 cursor csr1 is
90 select information4 BUSINESS_GROUP_ID,
91 information11 legislation_code,
92 information161 FORMULA_TYPE_ID,
93 information111 FORMULA_NAME,
94 information151 DESCRIPTION,
95 information325 FORMULA_TEXT,
96 information1 ff_formula_id
97 from ben_copy_entity_results
98 where copy_entity_result_id = p_copy_entity_result_id;
99 l_formula_id number(15);
100 l_rowid varchar2(255);
101 l_lastUpdateDate date;
102 r_fff csr1%RowType;
103
104 begin
105 open csr1;
106 fetch csr1 into r_fff;
107 close csr1;
108
109 if (r_fff.ff_formula_id is null) then
110 select ff_formulas_s.nextval
111 into l_formula_id
112 from sys.dual;
113
114 ff_formulas_f_pkg.insert_row(
115 x_rowid => l_rowid,
116 x_formula_id => l_formula_id,
117 x_effective_start_date => sysdate,
118 x_effective_end_date => hr_general.end_of_time,
119 x_business_group_id => r_fff.business_group_id,
120 x_legislation_code => r_fff.legislation_code,
121 x_formula_type_id => r_fff.formula_type_id,
122 x_formula_name => r_fff.formula_name,
123 x_description => r_fff.description,
124 x_formula_text => r_fff.formula_text,
125 x_sticky_flag => 'Y',
126 x_last_update_date => l_lastUpdateDate
127 );
128 else -- Formula Already created, now update it instead
129 l_formula_id := r_fff.ff_formula_id;
130 ff_formulas_f_pkg.update_row(
131 x_rowid => l_rowid,
132 x_formula_id => l_formula_id,
133 x_effective_start_date => sysdate,
134 x_effective_end_date => hr_general.end_of_time,
135 x_business_group_id => r_fff.business_group_id,
136 x_legislation_code => r_fff.legislation_code,
137 x_formula_type_id => r_fff.formula_type_id,
138 x_formula_name => r_fff.formula_name,
139 x_description => r_fff.description,
140 x_formula_text => r_fff.formula_text,
141 x_sticky_flag => 'Y',
142 x_last_update_date => l_lastUpdateDate
143 );
144
145 end if;
146
147 return l_formula_id;
148 end;
149
150
151
152
153 function compile_formula(
154 p_formula_id in number,
155 p_effective_date in date ) return varchar2 is
156
157 l_retval number;
158 l_timeout number := 120;
159 l_outcome varchar2(1000);
160 l_message varchar2(1000);
161 l_return_status varchar2(10);
162 --
163 begin
164 --
165 -- Enable multi-messaging
166 hr_multi_message.enable_message_list;
167 --
168 l_retval := fnd_transaction.synchronous(
169 timeout => l_timeout,
170 outcome => l_outcome,
171 message => l_message,
172 application => 'FF',
173 program => 'FFTMSINGLECOMPILE',
174 arg_1 => to_char(p_formula_id),
175 arg_2 => fnd_date.date_to_canonical(p_effective_date)
176 );
177 --
178 -- hr_utility.trace('!!! l_retval: '||to_char(l_retval));
179 -- Return values are either 0, 1, 2 or 3
180 -- 0 Indicates success - although formula compilation may have failed
181 -- 1 Indicates timeout error
182 -- 2 Indicates no transaction manager available
183 -- 3 Indicates some other error
184 if l_retval <> 0 or l_outcome <> 'SUCCESS' then
185 --
186 if l_retval = 1 then
187 --
188 -- Timeout error
189 hr_utility.set_message(8302, 'PQH_TX_MGR_TIMEOUT_ERROR');
190 hr_utility.set_message_token('ERROR_MESSAGE', l_message);
191 if hr_multi_message.exception_add then
192 hr_utility.raise_error;
193 end if;
194 elsif l_retval = 2 then
195 --
196 -- No transaction manager error
197 hr_utility.set_message(8302, 'PQH_TX_MGR_NOTFOUND_ERROR');
198 hr_utility.set_message_token( 'ERROR_MESSAGE', l_message);
199 if hr_multi_message.exception_add then
200 hr_utility.raise_error;
201 end if;
202 elsif l_retval = 3 then
203 --
204 -- Generic error
205 -- hr_utility.trace('!!! Generic error!!!');
206 hr_utility.set_message(8302, 'PQH_TX_MGR_OTHER_ERROR');
207 hr_utility.set_message_token( 'ERROR_MESSAGE', l_message);
208 if hr_multi_message.exception_add then
209 hr_utility.raise_error;
210 end if;
211 else
212
213 --
214 -- Formula compilation error
215 -- Get compilation error details from fnd_message.get
216 hr_utility.set_message(805, 'FF_WIZ_BUILD_VERIFY_FAILURE');
217 hr_utility.set_message_token( 'ERROR_MESSAGE', l_message);
218 if hr_multi_message.exception_add then
219 fnd_message.raise_error;
220 end if;
221 end if;
222 end if;
223 --
224 -- Get the return status and disable multi-messaging
225 -- hr_utility.trace('!!! get return status and disable mult-messaging');
226 l_return_status := hr_multi_message.get_return_status_disable;
227 return l_return_status;
228 --
229 exception
230 --
231 when hr_multi_message.error_message_exist then
232 l_return_status := hr_multi_message.get_return_status_disable;
233 return l_return_status||'O';
234 --
235 when others then raise;
236 --
237 end compile_formula;
238
239 function get_formula_text (p_formula_id number, p_effective_start_date date)
240 return clob is
241 l_clob clob;
242
243 begin
244
245 delete from ben_copy_entity_results
246 where copy_entity_result_id = -999999;
247
248 insert into ben_copy_entity_results (
249 COPY_ENTITY_RESULT_ID,
250 COPY_ENTITY_TXN_ID,
251 RESULT_TYPE_CD,
252 OBJECT_VERSION_NUMBER,
253 INFORMATION325)
254 select -999999, -999999, 'COPY_FF',1, to_lob(formula_text)
255 from ff_formulas_f
256 where formula_id = p_formula_id
257 and p_effective_start_date between effective_start_date and effective_end_date;
258
259 select information325 into l_clob from ben_copy_entity_results
260 where copy_entity_result_id = -999999;
261
262 -- rollback; can't use it, the formula text get's lost.
263 return l_clob;
264 end;
265
266 --
267 FUNCTION maintain_formula(p_formula_id IN NUMBER
268 ,p_effective_date IN DATE
269 ,p_effective_start_date IN DATE
270 ,p_effective_end_date IN DATE
271 ,p_business_group_id IN NUMBER
272 ,p_legislation_code IN VARCHAR2
273 ,p_formula_type_id IN NUMBER
274 ,p_formula_name IN VARCHAR2
275 ,p_description IN VARCHAR2
276 ,p_formula_text IN LONG
277 ,p_sticky_flag IN VARCHAR2
278 ,p_compile_flag IN VARCHAR2
279 ,p_dml_operation IN VARCHAR2
280 ,p_datetrack_mode IN VARCHAR2)
281 RETURN varchar2 IS
282 --
283 /*
284 --Cursor to fetch formula details
285 CURSOR c_formula IS
286 SELECT effective_start_date, effective_end_date
287 FROM ff_formulas_f
288 WHERE formula_id = p_formula_id
289 AND business_group_id = p_business_group_id
290 AND TRUNC(p_effective_date) BETWEEN effective_start_date AND effective_end_date;
291 */
292 --
293 --Local variables
294 l_formula_id NUMBER;
295 l_dml_operation VARCHAR2(20);
296 l_datetrack_mode VARCHAR2(50);
297 l_correction BOOLEAN;
298 l_update BOOLEAN;
299 l_update_override BOOLEAN;
300 l_update_change_insert BOOLEAN;
301 -- l_effective_start_date DATE;
302 -- l_effective_end_date DATE;
303 --
304 mesg varchar2(4000);
305 BEGIN
306 --
307 IF p_formula_id IS NULL THEN
308 SELECT ff_formulas_s.nextval
309 INTO l_formula_id
310 FROM sys.dual;
311 -- l_effective_start_date := TRUNC(p_effective_date);
312 -- l_effective_end_date := HR_GENERAL.end_of_time;
313 ELSE
314 l_formula_id := p_formula_id;
315 --
316 /*
317 OPEN c_formula;
318 FETCH c_formula INTO l_effective_start_date, l_effective_end_date;
319 CLOSE c_formula;
320 */
321 --
322 END IF;
323 --
324 /*
325 insert into ns_temp values ('Formula Id: '||p_formula_id);
326 insert into ns_temp values ('p_effective_start_date Id: '||p_effective_start_date);
327 insert into ns_temp values ('p_effective_end_date Id: '||p_effective_end_date);
328 insert into ns_temp values ('p_business_group_id Id: '||p_business_group_id);
329 insert into ns_temp values ('p_legislation_code Id: '||p_legislation_code);
330 insert into ns_temp values ('p_formula_type_id Id: '||p_formula_type_id);
331 insert into ns_temp values ('p_formula_name Id: '||p_formula_name);
332 insert into ns_temp values ('p_dml_operation Id: '||p_dml_operation);
333 insert into ns_temp values ('p_datetrack_mode Id: '||p_datetrack_mode);
334 */
335
336 BEN_PD_COPY_TO_BEN_ONE.create_or_update_ff
337 (p_formula_id => l_formula_id
338 ,p_effective_start_date => p_effective_start_date
339 ,p_effective_end_date => p_effective_end_date
340 ,p_business_group_id => p_business_group_id
341 ,p_legislation_code => p_legislation_code
342 ,p_formula_type_id => p_formula_type_id
343 ,p_formula_name => p_formula_name
344 ,p_description => p_description
345 ,p_formula_text => p_formula_text
346 ,p_sticky_flag => p_sticky_flag
347 ,p_compile_flag => p_compile_flag
348 ,p_last_update_date => SYSDATE
349 ,p_last_updated_by => -1
350 ,p_last_update_login => -1
351 ,p_created_by => -1
352 ,p_creation_date => SYSDATE
353 ,p_process_date => TRUNC(p_effective_date)
354 ,p_dml_operation => p_dml_operation
355 ,p_datetrack_mode => p_datetrack_mode);
356 --
357 commit;
358 /*
359 If creating the formula, commit it irrespective verify or finish
360
361 if (p_formula_id = null) then
362 commit;
363 end if;
364 */
365
366 mesg := compile_formula(l_formula_id, p_effective_date);
367
368 /*
369 Delete formula if new formula and mode is verify
370
371 if (p_formula_id = null --AND p_mode = 'VERIFY'
372 ) then
373 declare
374 l_rowid varchar2(100);
375 begin
376 select rowid into l_rowid
377 from ff_formulas_f
378 where formula_id = p_formula_id
379 and p_effective_date between effective_start_date and effective_end_date;
380
381 ff_formulas_f_pkg.delete_row(
382 x_rowid => l_rowid,
383 x_formula_id => p_formula_id,
384 x_dt_delete_mode => 'DELETE',
385 x_validation_start_date => p_effective_date,
386 x_validation_end_date => null,
387 x_effective_date => p_effective_date
388 );
389 -- commit;
390 end;
391 end if;
392 */
393
394 return (mesg);
395 -- RETURN l_formula_id;
396 --
397 EXCEPTION
398 WHEN OTHERS THEN
399 RETURN -1;
400 END maintain_formula;
401 /*--
402 Function to copy the formula into staging table and return the primary key
403 This key will be used to execute the query in front end.
404 */
405
406 FUNCTION copy_formula_result(p_copy_entity_txn_id IN NUMBER
407 ,p_formula_id IN NUMBER
408 ,p_effective_date IN DATE
409 ,p_business_group_id IN NUMBER)
410 RETURN NUMBER IS
411 --
412 --Cursor to fetch copy_entity_result_id
413 CURSOR c_copy_result IS
414 SELECT copy_entity_result_id
415 ,dml_operation
416 ,datetrack_mode
417 FROM ben_copy_entity_results
418 WHERE copy_entity_txn_id = p_copy_entity_txn_id
419 AND information1 = p_formula_id
420 AND ( information4 = p_business_group_id OR
421 ( p_business_group_id is null AND information4 is null))
422 AND p_effective_date BETWEEN information2 AND information3
423 AND table_alias = 'FFF'
424 ORDER By copy_entity_result_id desc;
425 --
426 --Local Variables
427 l_copy_entity_result_id NUMBER;
428 l_object_version_number NUMBER;
429 l_dml_operation VARCHAR2(100);
430 l_datetrack_mode VARCHAR2(100);
431 --
432 BEGIN
433 --
434 l_copy_entity_result_id := null;
435 l_object_version_number := null;
436 --
437 --Call API
438 BEN_PLAN_DESIGN_PROGRAM_MODULE.create_formula_result
439 (p_copy_entity_result_id => l_copy_entity_result_id
440 ,p_copy_entity_txn_id => p_copy_entity_txn_id
441 ,p_formula_id => p_formula_id
442 ,p_business_group_id => p_business_group_id
443 ,p_object_version_number => l_object_version_number
444 ,p_effective_date => TRUNC(p_effective_date));
445 OPEN c_copy_result;
446 FETCH c_copy_result INTO l_copy_entity_result_id, l_dml_operation, l_datetrack_mode;
447 CLOSE c_copy_result;
448
449 update ben_copy_entity_results
450 set number_of_copies = 0,
451 status = 'INVALID'
452 where copy_entity_result_id = l_copy_entity_result_id;
453 commit;
454
455 RETURN l_copy_entity_result_id;
456 EXCEPTION
457 WHEN OTHERS THEN
458 RETURN -1;
459 END copy_formula_result;
460 --
461 FUNCTION is_formula_verified (p_formula_id IN NUMBER
462 ,p_effective_date IN DATE ) RETURN VARCHAR2 IS
463 CURSOR verif IS
464 SELECT 'Y'
465 FROM FF_COMPILED_INFO_F
466 WHERE formula_id = p_formula_id
467 AND p_effective_date between effective_start_date and effective_end_date;
468 --
469 l_verified varchar2(10);
470 --
471 BEGIN
472 IF (p_formula_id IS NULL) THEN
473 l_verified := 'N';
474 ELSE
475 OPEN verif;
476 FETCH verif INTO l_verified;
477 IF verif%NOTFOUND THEN
478 l_verified := 'N';
479 END IF;
480 CLOSE verif;
481 END IF;
482
483 return l_verified;
484
485 END is_formula_verified;
486
487 /*
488 * Procedure to check the length of the formula, if it is more than
489 * 32K, an error will be displayed.
490 */
491 PROCEDURE formula_length_check (
492 p_formula_id IN NUMBER
493 ,p_effective_date IN DATE ) IS
494 l_formula_text clob;
495 BEGIN
496 l_formula_text := get_formula_text (p_formula_id , p_effective_date );
497
498 if (dbms_lob.getlength(l_formula_text) > 32512) then
499 hr_utility.set_message(8302,'PQH_FF_TEXT_MORETHAN_32K');
500 hr_utility.raise_error;
501 end if;
502
503 END formula_length_check;
504
505
506 END; -- Package Body BEN_PD_FORMULA_PKG