[Home] [Help]
PACKAGE BODY: APPS.FF_WRAPPER_SUP_PKG
Source
1 package body ff_wrapper_sup_pkg as
2 /* $Header: ffwrpsup.pkb 120.0 2005/05/27 23:26:08 appldev noship $ */
3 ----------------
4 -- Data types --
5 ----------------
6 type t_range is record
7 (start_index binary_integer
8 ,end_index binary_integer
9 );
10 type t_range_tbl is table of t_range index by binary_integer;
11 type t_ff_info is record
12 (start_date date
13 ,std_pkg_name varchar2(256)
14 ,wrap_pkg_name varchar2(256)
15 );
16 type t_ff_info_tbl is table of t_ff_info index by binary_integer;
17 ---------------
18 -- Constants --
19 ---------------
20 c_newline constant varchar2(1) := '
21 ';
22 c_date_format constant varchar2(10) := 'FXDDMMYYYY';
23 c_wrap_pkg_prefix constant varchar2(10) := 'FFW';
24 c_std_pkg_prefix constant varchar2(10) := 'FFP';
25 c_package constant varchar2(32) := 'PACKAGE';
26 c_package_body constant varchar2(32) := 'PACKAGE BODY';
27 c_object_invalid constant varchar2(32) := 'INVALID';
28 c_object_valid constant varchar2(32) := 'VALID';
29 c_name_replace constant varchar2(32) := '<NAME>';
30 c_wrapper_main constant varchar2(32) := 'FF_WRAPPER_MAIN_PKG';
31 c_wrapper_name constant varchar2(32) := 'FF_WRAPPER_PKG';
32
33 -- ******************************************************************** --
34 -- ** WARNING : DO NOT PUT ANY GLOBAL DATA IN THE GENERATED PACKAGE ** --
35 -- ** HEADERS AND BODIES ** --
36 -- ******************************************************************** --
37
38 ------------------------------------------------------------------------
39 -- Package state causes problems with package invalidation if the --
40 -- formula wrapper is regenerated. --
41 ------------------------------------------------------------------------
42
43 ------------------------------------------------------------------------------
44 -- Common package and package body start and end text fragments.
45 -- 1. CREATE OR REPLACE PACKAGE <NAME> AS
46 -- 2. CREATE OR REPLACE PACKAGE BODY <NAME> AS
47 -- 3. -- Generated by Oracle FastFormula - do not delete.
48 -- -- $Header: ffwrpsup.pkb 120.0 2005/05/27 23:26 appldev noship $
49 -- 4. END <NAME>
50 ------------------------------------------------------------------------------
51 c_package_head_start varchar2(256) :=
52 'CREATE OR REPLACE PACKAGE <NAME> AS' || c_newline;
53 c_package_body_start varchar2(256) :=
54 'CREATE OR REPLACE PACKAGE BODY <NAME> AS' || c_newline;
55 c_package_comments varchar2(256) :=
56 '-- Generated by Oracle FastFormula - do not delete.' || c_newline ||
57 '-- $Header: ffwrpsup.pkb 120.0 2005/05/27 23:26 appldev noship $' ||
58 c_newline;
59 c_package_end varchar2(256) := 'END <NAME>';
60 ------------------------------------------------------------------------------
61 -- Procedure begin and end for header and body.
62 -- 1. ;
63 -- 2. END FORMULA;
64 ------------------------------------------------------------------------------
65 c_proc_head_end varchar2(256) := ';' || c_newline;
66 c_formula_body_end varchar2(256) := 'END FORMULA;' || c_newline;
67 ------------------------------------------------------------------------------
68 -- FF_WRAPPER_PKG<X> header.
69 -- CREATE OR REPLACE PACKAGE <NAME> AS
70 -- -- Generated by Oracle FastFormula - do not delete.
71 -- -- $Header: ffwrpsup.pkb 120.0 2005/05/27 23:26 appldev noship $
72 -- PROCEDURE FORMULA
73 -- (P_FORMULA_NAME IN VARCHAR2
74 -- ,P_FF_PACKAGE_NAME IN VARCHAR2
75 -- ,P_D IN OUT NOCOPY FF_WRAPPER_PKG.T_DATE
76 -- ,P_N IN OUT NOCOPY FF_WRAPPER_PKG.T_NUMBER
77 -- ,P_T IN OUT NOCOPY FF_WRAPPER_PKG.T_TEXT
78 -- ,P_I IN OUT NOCOPY FF_WRAPPER_PKG.T_NUMBER
79 -- ,P_FFERLN IN OUT NOCOPY NUMBER
80 -- ,P_FFERCD IN OUT NOCOPY NUMBER
81 -- ,P_FFERMT OUT NOCOPY VARCHAR2
82 -- );
83 -- END <NAME>
84 ------------------------------------------------------------------------------
85 c_wrapper_formula1 varchar2(256) :=
86 ' PROCEDURE FORMULA' || c_newline ||
87 ' (P_FORMULA_NAME IN VARCHAR2' || c_newline ||
88 ' ,P_FF_PACKAGE_NAME IN VARCHAR2' || c_newline;
89 c_wrapper_formula2 varchar2(256) :=
90 ' ,P_D IN OUT NOCOPY FF_WRAPPER_PKG.T_DATE' || c_newline ||
91 ' ,P_N IN OUT NOCOPY FF_WRAPPER_PKG.T_NUMBER' || c_newline ||
92 ' ,P_T IN OUT NOCOPY FF_WRAPPER_PKG.T_TEXT' || c_newline;
93 c_wrapper_formula3 varchar2(256) :=
94 ' ,P_I IN OUT NOCOPY FF_WRAPPER_PKG.T_NUMBER' || c_newline ||
95 ' ,P_FFERLN OUT NOCOPY NUMBER' || c_newline ||
96 ' ,P_FFERCD IN OUT NOCOPY NUMBER' || c_newline;
97 c_wrapper_formula4 varchar2(256) :=
98 ' ,P_FFERMT OUT NOCOPY VARCHAR2' || c_newline ||
99 ' )';
100 ------------------------------------------------------------------------------
101 -- Start code chunks.
102 -- CREATE OR REPLACE PACKAGE BODY <NAME> AS
103 -- -- Generated by Oracle FastFormula - do not delete.
104 -- -- $Header: ffwrpsup.pkb 120.0 2005/05/27 23:26 appldev noship $
105 -- BAD_PLSQL EXCEPTION;
106 -- PRAGMA EXCEPTION_INIT(BAD_PLSQL,-6550);
107 -- CANNOT_FIND_PROG_UNIT EXCEPTION;
108 -- PRAGMA EXCEPTION_INIT(CANNOT_FIND_PROG_UNIT, -6508);
109 -- PROCEDURE FORMULA
110 -- (P_FORMULA_NAME IN VARCHAR2
111 -- ,P_FF_PACKAGE_NAME IN VARCHAR2
112 -- ,P_D IN OUT NOCOPY FF_WRAPPER_PKG.T_DATE
113 -- ,P_N IN OUT NOCOPY FF_WRAPPER_PKG.T_NUMBER
114 -- ,P_T IN OUT NOCOPY FF_WRAPPER_PKG.T_TEXT
115 -- ,P_I IN OUT NOCOPY FF_WRAPPER_PKG.T_NUMBER
116 -- ,P_FFERLN IN OUT NOCOPY NUMBER
117 -- ,P_FFERCD IN OUT NOCOPY NUMBER
118 -- ,P_FFERMT OUT NOCOPY VARCHAR2
119 -- ) IS
120 -- L_DEBUG BOOLEAN:=HR_UTILITY.DEBUG_ENABLED;
121 -- BEGIN
122 ------------------------------------------------------------------------------
123 c_wrapper_body1 varchar2(256) :=
124 ' BAD_PLSQL EXCEPTION;' || c_newline ||
125 ' PRAGMA EXCEPTION_INIT(BAD_PLSQL,-6550);' || c_newline ||
126 ' CANNOT_FIND_PROG_UNIT EXCEPTION;' || c_newline ||
127 ' PRAGMA EXCEPTION_INIT(CANNOT_FIND_PROG_UNIT, -6508);' || c_newline;
128 c_wrapper_body2 varchar2(256) :=
129 ' IS' || c_newline ||
130 ' L_DEBUG BOOLEAN:=HR_UTILITY.DEBUG_ENABLED;' || c_newline ||
131 ' BEGIN' || c_newline
132 ;
133 ------------------------------------------------------------------------------
134 -- No call if-block.
135 -- Allows a valid wrapper package even when there were no specific formula
136 -- calls added.
137 -- IF 1 = 2 THEN
138 -- NULL;
139 ------------------------------------------------------------------------------
140 c_nocall_if_block varchar2(256) :=
141 ' IF 1=2 THEN' || c_newline ||
142 ' NULL;' || c_newline;
143 ------------------------------------------------------------------------------
144 -- End code chunks.
145 -- This is the attempt to handle a formula that was not compiled
146 -- when the wrapper was last generated.
147 --
148 -- ELSE
149 -- GOTO FF_DYNAMIC_SQL;
150 -- END IF;
151 -- RETURN;
152 -- <<FF_DYNAMIC_SQL>>
153 -- FF_WRAPPER_PKG.G_D:=P_D;
154 -- FF_WRAPPER_PKG.G_N:=P_N;
155 -- FF_WRAPPER_PKG.G_T:=P_T;
156 -- FF_WRAPPER_PKG.G_I:=P_I;
157 -- IF L_DEBUG THEN
158 -- HR_UTILITY.TRACE
159 -- ('<NAME> DYNAMIC SQL FOR '||P_FF_PACKAGE_NAME);
160 -- END IF;
161 -- EXECUTE IMMEDIATE 'BEGIN ' || P_FF_PACKAGE_NAME || '.FORMULA;END;';
162 -- P_D:=FF_WRAPPER_PKG.G_D;
163 -- P_N:=FF_WRAPPER_PKG.G_N;
164 -- P_T:=FF_WRAPPER_PKG.G_T;
165 -- P_I:=FF_WRAPPER_PKG.G_I;
166 -- P_FFERCD:=FF_WRAPPER_PKG.G_FFERCD;
167 -- P_FFERLN:=FF_WRAPPER_PKG.G_FFERLN;
168 -- P_FFERMT:=FF_WRAPPER_PKG.G_FFERMT;
169 -- EXCEPTION
170 -- WHEN BAD_PLSQL THEN
171 -- HR_UTILITY.SET_MESSAGE(801,'FFX22J_FORMULA_NOT_FOUND');
172 -- HR_UTILITY.SET_MESSAGE_TOKEN('1',P_FORMULA_NAME);
173 -- HR_UTILITY.RAISE_ERROR;
174 -- WHEN CANNOT_FIND_PROG_UNIT THEN
175 -- HR_UTILITY.SET_MESSAGE(802, 'FF_33186_GENERATE_WRAPPER');
176 -- HR_UTILITY.SET_MESSAGE_TOKEN('1',P_FORMULA_NAME);
177 -- HR_UTILITY.RAISE_ERROR;
178 -- END FORMULA;
179 -- END <NAME>
180 ------------------------------------------------------------------------------
181 c_end1 varchar2(256) :=
182 ' ELSE' || c_newline ||
183 ' IF L_DEBUG THEN' || c_newline ||
184 ' HR_UTILITY.TRACE(''ELSE:END'');' || c_newline ||
185 ' END IF;' || c_newline ||
186 ' GOTO FF_DYNAMIC_SQL;' || c_newline ||
187 ' END IF;' || c_newline ||
188 ' RETURN;' || c_newline ||
189 ' <<FF_DYNAMIC_SQL>>' || c_newline;
190 c_end2 varchar2(256) :=
191 ' FF_WRAPPER_PKG.G_D:=P_D;' || c_newline ||
192 ' FF_WRAPPER_PKG.G_N:=P_N;' || c_newline ||
193 ' FF_WRAPPER_PKG.G_T:=P_T;' || c_newline ||
194 ' FF_WRAPPER_PKG.G_I:=P_I;' || c_newline;
195 c_end3 varchar2(256) :=
196 ' IF L_DEBUG THEN' || c_newline ||
197 ' HR_UTILITY.TRACE' ||
198 '(''<NAME> DYNAMIC SQL FOR ''||P_FF_PACKAGE_NAME);' ||
199 c_newline || ' END IF;' || c_newline;
200 c_end4 varchar2(256) :=
201 ' EXECUTE IMMEDIATE ''BEGIN '' || P_FF_PACKAGE_NAME || ''.FORMULA;END;'';' ||
202 c_newline;
203 c_end5 varchar2(256) :=
204 ' P_D:=FF_WRAPPER_PKG.G_D;' || c_newline ||
205 ' P_N:=FF_WRAPPER_PKG.G_N;' || c_newline ||
206 ' P_T:=FF_WRAPPER_PKG.G_T;' || c_newline ||
207 ' P_I:=FF_WRAPPER_PKG.G_I;' || c_newline;
208 c_end6 varchar2(256) :=
209 ' P_FFERCD:=FF_WRAPPER_PKG.G_FFERCD;' || c_newline ||
210 ' P_FFERLN:=FF_WRAPPER_PKG.G_FFERLN;' || c_newline ||
211 ' P_FFERMT:=FF_WRAPPER_PKG.G_FFERMT;' || c_newline ||
212 ' EXCEPTION' || c_newline;
213 c_end7 varchar2(256) :=
214 ' WHEN BAD_PLSQL THEN' || c_newline ||
215 ' HR_UTILITY.SET_MESSAGE(801,''FFX22J_FORMULA_NOT_FOUND'');'||c_newline||
216 ' HR_UTILITY.SET_MESSAGE_TOKEN(''1'',P_FORMULA_NAME);' ||c_newline;
217 c_end8 varchar2(256) :=
218 ' HR_UTILITY.RAISE_ERROR;' || c_newline ||
219 ' WHEN CANNOT_FIND_PROG_UNIT THEN' || c_newline ||
220 ' HR_UTILITY.SET_MESSAGE(802,''FF_33186_GENERATE_WRAPPER'');'||c_newline;
221 c_end9 varchar2(256) :=
222 ' HR_UTILITY.SET_MESSAGE_TOKEN(''1'',P_FORMULA_NAME);' || c_newline ||
223 ' HR_UTILITY.RAISE_ERROR;' || c_newline ||
224 ' END FORMULA;' || c_newline;
225 ------------------------------------------------------------------------------
226 -- FFW package header.
227 -- CREATE OR REPLACE PACKAGE <PKGNAME> AS
228 -- -- Generated by Oracle FastFormula - do not delete.
229 -- -- $Header: ffwrpsup.pkb 120.0 2005/05/27 23:26 appldev noship $
230 -- PROCEDURE FORMULA
231 -- (P_D IN OUT NOCOPY FF_WRAPPER_PKG.T_DATE
232 -- ,P_N IN OUT NOCOPY FF_WRAPPER_PKG.T_NUMBER
233 -- ,P_T IN OUT NOCOPY FF_WRAPPER_PKG.T_TEXT
234 -- ,P_I IN OUT NOCOPY FF_WRAPPER_PKG.T_NUMBER
235 -- ,P_FFERLN IN OUT NOCOPY NUMBER
236 -- ,P_FFERCD IN OUT NOCOPY NUMBER
237 -- ,P_FFERMT OUT NOCOPY VARCHAR2
238 -- );
239 -- END <PKGNAME>
240 --
241 -- FFW package body.
242 -- CREATE OR REPLACE PACKAGE BODY <FFWNAME> AS
243 -- -- Generated by Oracle FastFormula - do not delete.
244 -- -- $Header: ffwrpsup.pkb 120.0 2005/05/27 23:26 appldev noship $
245 -- PROCEDURE FORMULA
246 -- (P_D IN OUT NOCOPY FF_WRAPPER_PKG.T_DATE
247 -- ,P_N IN OUT NOCOPY FF_WRAPPER_PKG.T_NUMBER
248 -- ,P_T IN OUT NOCOPY FF_WRAPPER_PKG.T_TEXT
249 -- ,P_I IN OUT NOCOPY FF_WRAPPER_PKG.T_NUMBER
250 -- ,P_FFERLN IN OUT NOCOPY NUMBER
251 -- ,P_FFERCD IN OUT NOCOPY NUMBER
252 -- ,P_FFERMT OUT NOCOPY VARCHAR2
253 -- ) IS
254 -- BEGIN
255 -- <FFPNAME>.FORMULA
256 -- (P_D,P_N,P_T,P_I,P_FFERLN,P_FFERCD,P_FFERMT);
257 -- END FORMULA;
258 -- END <FFWNAME>
259 ------------------------------------------------------------------------------
260 c_ffw_formula constant varchar2(512) :=
261 ' PROCEDURE FORMULA' || c_newline ||
262 ' (P_D IN OUT NOCOPY FF_WRAPPER_PKG.T_DATE' || c_newline ||
263 ' ,P_N IN OUT NOCOPY FF_WRAPPER_PKG.T_NUMBER' || c_newline ||
264 ' ,P_T IN OUT NOCOPY FF_WRAPPER_PKG.T_TEXT' || c_newline ||
265 ' ,P_I IN OUT NOCOPY FF_WRAPPER_PKG.T_NUMBER' || c_newline ||
266 ' ,P_FFERLN IN OUT NOCOPY NUMBER' || c_newline ||
267 ' ,P_FFERCD IN OUT NOCOPY NUMBER' || c_newline ||
268 ' ,P_FFERMT OUT NOCOPY VARCHAR2' || c_newline ||
269 ' )';
270 c_ffw_formula_body constant varchar2(256) :=
271 ' IS' || c_newline || ' BEGIN' || c_newline || ' <NAME>.FORMULA' ||
272 c_newline || ' (P_D,P_N,P_T,P_I,P_FFERLN,P_FFERCD,P_FFERMT);' ||
273 c_newline;
274
275 -- ******************************************************************** --
276 -- ** WARNING : DO NOT PUT ANY GLOBAL DATA IN THE GENERATED PACKAGE ** --
277 -- ** HEADERS AND BODIES ** --
278 -- ******************************************************************** --
279
280 ----------------------
281 -- Global Variables --
282 ----------------------
283 --
284 -- Max number of formulas per wrapper (should be a square number).
285 --
286 g_formula_limit number := 576;
287 ---------------------------- start_wrapper_body ------------------------------
288 --
289 -- Begin the wrapper body.
290 --
291 procedure start_wrapper_body
292 (p_pkg_name in varchar2
293 ,p_chunks in out nocopy dbms_sql.varchar2s
294 ) is
295 l_index binary_integer;
296 begin
297 l_index := p_chunks.count + 1;
298 p_chunks(l_index) :=
299 replace(c_package_body_start, c_name_replace, p_pkg_name);
300 l_index := l_index + 1;
301 p_chunks(l_index) := c_package_comments;
302 l_index := l_index + 1;
303 p_chunks(l_index) := c_wrapper_body1;
304 l_index := l_index + 1;
305 p_chunks(l_index) := c_wrapper_formula1;
306 l_index := l_index + 1;
307 p_chunks(l_index) := c_wrapper_formula2;
308 l_index := l_index + 1;
309 p_chunks(l_index) := c_wrapper_formula3;
310 l_index := l_index + 1;
311 p_chunks(l_index) := c_wrapper_formula4;
312 l_index := l_index + 1;
313 p_chunks(l_index) := c_wrapper_body2;
314 end start_wrapper_body;
315 ---------------------------- end_wrapper_body --------------------------------
316 --
317 -- End the wrapper body.
318 --
319 procedure end_wrapper_body
320 (p_package_name in varchar2
321 ,p_calls_added in boolean
322 ,p_chunks in out nocopy dbms_sql.varchar2s
323 ) is
324 l_index binary_integer;
325 begin
326 l_index := p_chunks.count + 1;
327 --
328 -- If no calls were added then it's necessary to add the no-call if-block.
329 --
330 if not p_calls_added then
331 p_chunks(l_index) := c_nocall_if_block;
332 l_index := l_index + 1;
333 end if;
334 p_chunks(l_index) := c_end1;
335 l_index := l_index + 1;
336 p_chunks(l_index) := c_end2;
337 l_index := l_index + 1;
338 p_chunks(l_index) :=
339 replace(c_end3, c_name_replace, p_package_name);
340 l_index := l_index + 1;
341 p_chunks(l_index) := c_end4;
342 l_index := l_index + 1;
343 p_chunks(l_index) := c_end5;
344 l_index := l_index + 1;
345 p_chunks(l_index) := c_end6;
346 l_index := l_index + 1;
347 p_chunks(l_index) := c_end7;
348 l_index := l_index + 1;
349 p_chunks(l_index) := c_end8;
350 l_index := l_index + 1;
351 p_chunks(l_index) := c_end9;
355 end end_wrapper_body;
352 l_index := l_index + 1;
353 p_chunks(l_index) :=
354 replace(c_package_end, c_name_replace, p_package_name);
356 ----------------------------- drop_package -----------------------------------
357 --
358 -- Drop a formula package.
359 --
360 procedure drop_package
361 (p_package_name in varchar2
362 ,p_body in boolean default false
363 ) is
364 l_body varchar2(32) := ' ';
365 begin
366 if p_body then
367 l_body := ' BODY ';
368 end if;
369 execute immediate 'DROP PACKAGE' || l_body || p_package_name;
370 end drop_package;
371 -------------------------- core_create_ffw_body ------------------------------
372 procedure core_create_ffw_body
373 (p_wrapper_pkg_name in varchar2
374 ,p_standard_pkg_name in varchar2
375 ) is
376 l_text varchar2(8192);
377 begin
378 l_text :=
379 replace(c_package_body_start, c_name_replace, p_wrapper_pkg_name) ||
380 c_package_comments ||
381 c_ffw_formula ||
382 replace(c_ffw_formula_body, c_name_replace, p_standard_pkg_name) ||
383 replace(c_formula_body_end, c_name_replace, p_wrapper_pkg_name) ||
384 replace(c_package_end, c_name_replace, p_wrapper_pkg_name);
385 execute immediate l_text;
386 end core_create_ffw_body;
387 -------------------------- create_specific_wrapper ---------------------------
388 --
389 -- Create the formula-specific FFW wrapper package.
390 --
391 procedure create_specific_wrapper
392 (p_wrapper_pkg_name in varchar2
393 ,p_standard_pkg_name in varchar2
394 ) is
395 l_text varchar2(8192);
396 l_header boolean := false;
397 l_status varchar2(32);
398 --
399 -- Cursor to check the status of the package body.
400 --
401 cursor csr_pkg_status(p_package_name in varchar2) is
402 select status
403 from user_objects o
404 where o.object_name = p_package_name
405 and o.object_type = c_package_body
406 ;
407 begin
408 --
409 -- 0. Check if the wrapper needs to be generated.
410 --
411 open csr_pkg_status(p_package_name => p_wrapper_pkg_name);
412 fetch csr_pkg_status
413 into l_status
414 ;
415 if l_status = c_object_valid then
416 --
417 -- The package body is valid so there is no need to generate it again.
418 --
419 close csr_pkg_status;
420 return;
421 end if;
422 close csr_pkg_status;
423 --
424 -- 1. Build and generate the header.
425 --
426 l_text :=
427 replace(c_package_head_start, c_name_replace, p_wrapper_pkg_name) ||
428 c_package_comments ||
429 c_ffw_formula ||
430 c_proc_head_end ||
431 replace(c_package_end, c_name_replace, p_wrapper_pkg_name);
432 execute immediate l_text;
433 l_header := true;
434 --
435 -- 2. Build and generate the body.
436 --
437 core_create_ffw_body
438 (p_wrapper_pkg_name => p_wrapper_pkg_name
439 ,p_standard_pkg_name => p_standard_pkg_name
440 );
441 exception
442 when others then
443 if csr_pkg_status%isopen then
444 close csr_pkg_status;
445 end if;
446 --
447 if l_header then
448 drop_package(p_package_name => p_wrapper_pkg_name);
449 end if;
450 hr_utility.set_message(800, 'FF_34999_WRAPPER_GEN_ERROR');
451 hr_utility.set_message_token('PACKAGE', p_wrapper_pkg_name);
452 hr_utility.raise_error;
453 end create_specific_wrapper;
454 --------------------------- create_ffw_body ----------------------------------
455 procedure create_ffw_body
456 (p_wrapper_pkg_name in varchar2
457 ,p_standard_pkg_name in varchar2
458 ,p_keep_package in varchar2 default 'N'
459 ) is
460 l_header boolean := false;
461 l_body boolean := false;
462 l_dummy varchar2(1);
463 --
464 -- Cursor to check the status of the package.
465 --
466 cursor csr_pkg_status(p_package_name in varchar2) is
467 select o.status
468 , o.object_type
469 from user_objects o
470 where o.object_name = p_package_name
471 ;
472 --
473 -- Cursor to check if the package body exists.
474 --
475 cursor csr_body_exists(p_package_name in varchar2) is
476 select null
477 from user_objects o
478 where o.object_name = p_package_name
479 and o.object_type = c_package_body
480 ;
481 begin
482 --
483 -- See if the ffw package already exists.
484 --
485 for crec in csr_pkg_status(p_package_name => p_wrapper_pkg_name) loop
486 --
487 -- Looking for a valid header.
488 --
489 if crec.status = c_object_valid and crec.object_type = c_package then
490 l_header := true;
491 --
492 -- Just acknowledge the presence of the package body. It is bound to be
493 -- invalid in this session if the formula just has been recompiled. Unless
494 -- there is a problem with the wrapper code itself there is no need to
495 -- regenerate the package body or run ALTER PACKAGE ... COMPILE BODY
496 -- on it. In such a case, it's best for the wrapper to get regenerated.
497 --
498 elsif crec.object_type = c_package_body then
499 l_body := true;
500 end if;
501 end loop;
502 --
506 --
503 -- Create the body only if there is a valid header, but no valid body.
504 -- This corresponds to the case where the packages were dropped due
505 -- to an earlier compilation error.
507 if l_header and not l_body then
508 begin
509 core_create_ffw_body
510 (p_wrapper_pkg_name => p_wrapper_pkg_name
511 ,p_standard_pkg_name => p_standard_pkg_name
512 );
513 exception
514 when others then
515 --
516 -- Drop the package body if it was created with problems.
517 --
518 if upper(p_keep_package) = 'N' then
519 open csr_body_exists(p_package_name => p_wrapper_pkg_name);
520 fetch csr_body_exists into l_dummy;
521 if csr_body_exists%found then
522 drop_package(p_package_name => p_wrapper_pkg_name, p_body => true);
523 end if;
524 close csr_body_exists;
525 end if;
526 raise;
527 end;
528 end if;
529 exception
530 when others then
531 if csr_body_exists%isopen then
532 close csr_body_exists;
533 end if;
534 raise;
535 end create_ffw_body;
536 ------------------------------ genintercall ----------------------------------
537 --
538 -- Generate table-based interface FORMULA call for a given formula package.
539 --
540 procedure genintercall
541 (p_package_name in varchar2
542 ,p_chunk2 out nocopy varchar2
543 ,p_chunk1 out nocopy varchar2
544 ) is
545 l_prefix varchar2(32);
546 begin
547 l_prefix := ' ';
548 --
549 p_chunk1 :=
550 l_prefix || 'IF L_DEBUG THEN' || c_newline ||
551 l_prefix || ' HR_UTILITY.TRACE(''CALL INTO ' || p_package_name || ''');'
552 || c_newline ||
553 l_prefix || 'END IF;' || c_newline;
554 p_chunk2 :=
555 l_prefix || p_package_name || '.FORMULA' || c_newline ||
556 l_prefix || '(P_FORMULA_NAME,P_FF_PACKAGE_NAME,P_D,P_N,P_T,P_I,P_FFERLN' ||
557 c_newline || l_prefix || ',P_FFERCD,P_FFERMT);' || c_newline;
558 end genintercall;
559 -------------------------------- gencall -------------------------------------
560 --
561 -- Generate table-based interface FORMULA call for a given formula package.
562 --
563 procedure gencall
564 (p_first in boolean
565 ,p_indent in boolean default false
566 ,p_std_pkg in varchar2
567 ,p_wrap_pkg in varchar2
568 ,p_chunk1 out nocopy varchar2
569 ,p_chunk2 out nocopy varchar2
570 ) is
571 l_prefix varchar2(32);
572 l_spaces varchar2(10);
573 begin
574 if p_first then
575 l_prefix := ' IF ';
576 else
577 l_prefix := ' ELSIF ';
578 end if;
579 --
580 if p_indent then
581 l_spaces := ' ';
582 end if;
583 --
584 p_chunk1 := l_spaces ||
585 l_prefix || 'P_FF_PACKAGE_NAME=''' || p_std_pkg || ''' THEN' || c_newline;
586 p_chunk2 := l_spaces ||
587 ' ' || p_wrap_pkg || '.FORMULA' || c_newline || l_spaces ||
588 ' (P_D,P_N,P_T,P_I,P_FFERLN,P_FFERCD,P_FFERMT);' || c_newline;
589 end gencall;
590 ------------------------------ gencallend ------------------------------------
591 --
592 -- Generate table-based the end of IF-statement for gencallend.
593 --
594 procedure gencallend
595 (p_where in number
596 ,p_chunk out nocopy varchar2
597 ) is
598 begin
599 p_chunk :=
600 ' ELSE' || c_newline ||
601 ' IF L_DEBUG THEN' || c_newline ||
602 ' HR_UTILITY.TRACE(''ELSE:' || p_where || ''');' || c_newline ||
603 ' END IF;' || c_newline ||
604 ' GOTO FF_DYNAMIC_SQL;' || c_newline ||
605 ' END IF;' || c_newline;
606 end gencallend;
607 ----------------------------- genrangeif ------------------------------------
608 --
609 -- Generate table-based interface FORMULA call for a given formula package.
610 --
611 procedure genrangeif
612 (p_first in boolean
613 ,p_range_start in varchar2
614 ,p_range_end in varchar2
615 ,p_chunk out nocopy varchar2
616 ) is
617 l_prefix varchar2(32);
618 begin
619 if p_first then
620 l_prefix := ' IF ';
621 else
622 l_prefix := ' ELSIF ';
623 end if;
624 --
625 p_chunk :=
626 l_prefix || 'P_FF_PACKAGE_NAME BETWEEN' || c_newline ||
627 ' ''' || p_range_start || ''' AND ''' || p_range_end || ''' THEN' ||
628 c_newline;
629 end genrangeif;
630 --------------------------- check_std_package --------------------------------
631 --
632 -- Check if the standard (FFP) formula package's body is valid. If not then
633 -- set P_ADD_TO_TABLE to FALSE. Otherwise, set P_ADD_TO_TABLE to TRUE.
634 --
635 procedure check_std_package
636 (p_package_name in varchar2
637 ,p_add_to_table in out nocopy boolean
638 ) is
639 l_status varchar2(32);
640 --
641 cursor csr_pkg_status(p_package_name in varchar2) is
642 select status
643 from user_objects o
644 where o.object_name = p_package_name
648 open csr_pkg_status(p_package_name => p_package_name);
645 and o.object_type = c_package_body
646 ;
647 begin
649 fetch csr_pkg_status into l_status;
650 --
651 -- No longer dropping the package body.
652 --
653 close csr_pkg_status;
654 p_add_to_table := (l_status = c_object_valid);
655 end check_std_package;
656 ----------------------------- check_compile ----------------------------------
657 --
658 -- Check that the compiled package body is valid.
659 --
660 procedure check_compile
661 (p_package_name in varchar2
662 ) is
663 cursor csr_check_compile
664 (p_package_name in varchar2
665 ) is
666 select status
667 from user_objects uo
668 where uo.object_name = p_package_name
669 and uo.object_type = c_package_body;
670 l_status varchar2(64);
671 begin
672 open csr_check_compile(p_package_name);
673 fetch csr_check_compile into l_status;
674 close csr_check_compile;
675 if upper(l_status) <> c_object_valid then
676 hr_utility.set_message(800, 'FF_34999_WRAPPER_GEN_ERROR');
677 hr_utility.set_message_token('PACKAGE', p_package_name);
678 hr_utility.raise_error;
679 end if;
680 exception
681 when others then
682 if csr_check_compile%isopen then
683 close csr_check_compile;
684 end if;
685 raise;
686 end check_compile;
687 ------------------------------ generate_body --------------------------------
688 --
689 -- Generate the body for a wrapper package
690 -- FF_WRAPPER_MAIN_PKG, FF_WRAPPER_PKG<N>
691 --
692 procedure generate_body
693 (p_wrapper_name in varchar2
694 ,p_ff_info_tbl in t_ff_info_tbl
695 ,p_wrap_info in t_range_tbl
696 ,p_wrapper_num in varchar2
697 ,p_intermediates in boolean
698 ) is
699 l_debug boolean := hr_utility.debug_enabled;
700 l_chunks dbms_sql.varchar2s;
701 l_chunk_index binary_integer := 1;
702 l_range_start boolean;
703 l_range_size number;
704 l_end_range number;
705 l_branches number;
706 l_start_index binary_integer;
707 l_end_index binary_integer;
708 l_saved_count binary_integer;
709 l_indent boolean;
710 l_csr_sql integer;
711 l_rows number;
712 l_generated boolean := false;
713 begin
714 if l_debug then
715 hr_utility.trace('Enter:generate_body:'||p_wrapper_name);
716 end if;
717
718 --------------------------------
719 -- A. Start the wrapper body. --
720 --------------------------------
721 start_wrapper_body
722 (p_pkg_name => p_wrapper_name
723 ,p_chunks => l_chunks
724 );
725 l_saved_count := l_chunks.count;
726
727 -----------------------------------------
728 -- B. Package body with intermediates. --
729 -----------------------------------------
730 l_chunk_index := l_chunks.count + 1;
731 if p_intermediates then
732 if l_debug then
733 hr_utility.trace
734 ('Generating calls to intermediate wrapper packages.');
735 end if;
736
737 for i in 1 .. p_wrap_info.count loop
738 --
739 -- 1. Generate the range IF-statement.
740 --
741 if l_debug then
742 hr_utility.trace
743 ('Range:'||p_wrap_info(i).start_index||'-'||p_wrap_info(i).end_index);
744 end if;
745
746 genrangeif
747 (p_first => i = 1
748 ,p_range_start =>
749 p_ff_info_tbl(p_wrap_info(i).start_index).std_pkg_name
750 ,p_range_end =>
751 p_ff_info_tbl(p_wrap_info(i).end_index).std_pkg_name
752 ,p_chunk => l_chunks(l_chunk_index)
753 );
754 l_chunk_index := l_chunk_index + 1;
755
756 --
757 -- 2. Generate the intermediate package call.
758 --
759 if l_debug then
760 hr_utility.trace('Call:'||c_wrapper_name||to_char(i));
761 end if;
762
763 genintercall
764 (p_package_name => c_wrapper_name || to_char(i)
765 ,p_chunk1 => l_chunks(l_chunk_index)
766 ,p_chunk2 => l_chunks(l_chunk_index + 1)
767 );
768 l_chunk_index := l_chunk_index + 2;
769 end loop;
770
771 --------------------------------------------
772 -- C. Package body without intermediates. --
773 --------------------------------------------
774 elsif p_ff_info_tbl.count <> 0 then
775 -------------------------------------------------
776 -- 1. Work out the range size for indirection. --
777 -------------------------------------------------
778 if l_debug then
779 hr_utility.trace('Generating direct calls to formula packages.');
780 end if;
781
782 l_start_index := p_wrap_info(p_wrapper_num).start_index;
783 l_end_index := p_wrap_info(p_wrapper_num).end_index;
784 l_branches := 1 + l_end_index - l_start_index;
785 --
786 -- Too few formulas to bother with indirection.
787 --
788 if l_branches < 9 then
789 l_range_size := 1;
790 --
791 -- Use a range size of SQRT(# of branches).
792 --
793 else
794 l_range_size := trunc(sqrt(l_branches));
795 if l_range_size * l_range_size <> l_branches then
799
796 l_range_size := l_range_size + 1;
797 end if;
798 end if;
800 if l_debug then
801 hr_utility.trace
802 ('Branches:'||l_branches||' Range Size:'||l_range_size);
803 hr_utility.trace
804 ('Generating for formulas:'||l_start_index||'-'||l_end_index);
805 end if;
806
807 -----------------------------------------------------------------
808 -- 2. Loop through the formulas to generate the IF-statements. --
809 -----------------------------------------------------------------
810 for i in l_start_index .. l_end_index loop
811
812 -----------------------------------------------------------
813 -- Common case i.e. where the outer IF-statement will be --
814 -- generated. --
815 -----------------------------------------------------------
816 if l_range_size <> 1 then
817 --
818 -- Generate the outer IF-statement for the start of a range.
819 --
820 l_range_start := mod(i - l_start_index + 1, l_range_size) = 1;
821 if l_range_start then
822 --
823 -- Where does the range end ?
824 --
825 l_end_range := i + l_range_size - 1;
826 if l_end_range > l_end_index then
827 l_end_range := l_end_index;
828 end if;
829
830 if l_debug then
831 hr_utility.trace('Outer IF-range:'||i||'-'||l_end_range);
832 end if;
833
834 --
835 -- Generate the IF-statement.
836 --
837 genrangeif
838 (p_first => i = l_start_index
839 ,p_range_start => p_ff_info_tbl(i).std_pkg_name
840 ,p_range_end => p_ff_info_tbl(l_end_range).std_pkg_name
841 ,p_chunk => l_chunks(l_chunk_index)
842 );
843 l_chunk_index := l_chunk_index + 1;
844 end if;
845
846 --
847 -- Indentation required for two-level IF-statement.
848 --
849 l_indent := true;
850
851 ------------------------------------------------------
852 -- With a range size of 1, just generating a single --
853 -- IF-statement for all formulas. --
854 ------------------------------------------------------
855 else
856 l_range_start := i = l_start_index;
857 l_indent := false;
858 end if;
859
860 -----------------------------------------
861 -- Generate the formula-specific call. --
862 -----------------------------------------
863 gencall
864 (p_first => l_range_start
865 ,p_indent => l_indent
866 ,p_std_pkg => p_ff_info_tbl(i).std_pkg_name
867 ,p_wrap_pkg => p_ff_info_tbl(i).wrap_pkg_name
868 ,p_chunk1 => l_chunks(l_chunk_index)
869 ,p_chunk2 => l_chunks(l_chunk_index + 1)
870 );
871 l_chunk_index := l_chunk_index + 2;
872
873 -----------------------------------------------------
874 -- Terminate inner part of two-level IF-statement. --
875 -----------------------------------------------------
876 if l_range_size <> 1 then
877 if i = l_end_range then
878 gencallend
879 (p_where => ceil(l_end_range / l_range_size)
880 ,p_chunk => l_chunks(l_chunk_index)
881 );
882 l_chunk_index := l_chunk_index + 1;
883 end if;
884 end if;
885
886 end loop;
887 end if;
888
889 -------------------------------------------------------
890 -- D. Generate the rest of the main wrapper package. --
891 -------------------------------------------------------
892 end_wrapper_body
893 (p_package_name => p_wrapper_name
894 ,p_calls_added => l_saved_count <> l_chunks.count
895 ,p_chunks => l_chunks
896 );
897
898 -----------------------------------------------------------
899 -- E. Execute the generated SQL. Don't insert linefeeds. --
900 -----------------------------------------------------------
901 if l_debug then
902 hr_utility.trace('Text complete, create package body...');
903 end if;
904
905 begin
906 l_csr_sql := dbms_sql.open_cursor;
907 dbms_sql.parse
908 (l_csr_sql, l_chunks, 1, l_chunks.count, false, dbms_sql.native);
909 l_rows := dbms_sql.execute(l_csr_sql);
910 dbms_sql.close_cursor(l_csr_sql);
911 exception
912 when others then
913 if dbms_sql.is_open(l_csr_sql) then
914 dbms_sql.close_cursor(l_csr_sql);
915 end if;
916 raise;
917 end;
918 l_generated := true;
919
920 ------------------------------------
921 -- F. Check the compiled package. --
922 ------------------------------------
923 check_compile(p_package_name => p_wrapper_name);
924
925 if l_debug then
926 hr_utility.trace('Leaving:generate_body');
927 end if;
928
929 exception
930 when others then
931 if l_debug then
932 hr_utility.trace
933 ('Leaving generate_body with error:' || sqlcode || ':' || sqlerrm);
934 end if;
935
936 if l_generated then
937 drop_package(p_package_name => p_wrapper_name, p_body => true);
938 end if;
939 raise;
940 end generate_body;
941 ---------------------------- process_compiled --------------------------------
942 --
943 -- Process the compiled formulas. For each compiled formula with a valid
944 -- standard (FFP) formula package the following are done:
945 -- * Entries created in the range and information tables.
946 -- * The specific (FFW) formula package is created.
947 -- * The text for the main (FF_WRAPPER_MAIN_PKG) wrapper package is generated.
948 -- * The intermediate (FF_WRAPPER_PKG<N>) wrapper package headers and bodies
949 -- are generated.
950 --
951 procedure process_compiled
952 (p_range_tbl in out nocopy t_range_tbl
953 ,p_ff_info_tbl in out nocopy t_ff_info_tbl
954 ) is
955 l_debug boolean := hr_utility.debug_enabled;
956 l_info_index number := 1;
957 l_add_to_tbl boolean;
958 l_wrap_info t_range_tbl;
959 l_wrapper_num number := 0;
960 l_text varchar2(8192);
961 l_wrap_name varchar2(256);
962 l_header_done boolean;
963 l_wrap_delno number;
964 l_dummy varchar2(1);
965 --
966 -- FF information cursor for compiled formulas. The query is ordered by the
967 -- FFP package name. This ordering is necessary because the generated IF-block
968 -- will look BETWEEN a start and end package name.
969 --
970 cursor csr_get_ff_info is
971 select ff.formula_id
972 , ff.effective_start_date start_date
973 , c_std_pkg_prefix || to_char(ff.formula_id) || '_' ||
974 to_char(ff.effective_start_date, c_date_format) std_pkg_name
975 , c_wrap_pkg_prefix || to_char(ff.formula_id) || '_' ||
976 to_char(ff.effective_start_date, c_date_format) wrap_pkg_name
977 from ff_formulas_f ff
978 , ff_compiled_info_f fci
979 where fci.formula_id = ff.formula_id
980 and fci.effective_start_date = ff.effective_start_date
981 order by 3
982 ;
983 --
984 -- Confirm that a package exists and is valid.
985 --
986 cursor csr_package_exists(p_package_name in varchar2) is
987 select null
988 from user_objects uo
989 where uo.object_name = p_package_name
990 and uo.object_type = c_package
991 and uo.status = c_object_valid
992 ;
993 --
994 -- Cursor to get the name of the wrapper packages.
995 --
996 cursor csr_wrap_pkgs(p_wrapper_name in varchar2) is
997 select uo.object_name
998 from user_objects uo
999 where uo.object_name like p_wrapper_name
1000 and uo.object_type = c_package
1001 ;
1002 begin
1003 if l_debug then
1004 hr_utility.trace('Entering:process_compiled');
1005 end if;
1006 --------------------------------------------------
1007 -- A. Read in the compiled formula information. --
1008 --------------------------------------------------
1009 if l_debug then
1010 hr_utility.trace('Set-up formula information tables...');
1011 end if;
1012
1013 for crec in csr_get_ff_info loop
1014 l_add_to_tbl := true;
1015 --
1016 -- Has the formula got a valid standard (FFP) package ?
1017 --
1018 check_std_package
1019 (p_package_name => crec.std_pkg_name
1020 ,p_add_to_table => l_add_to_tbl
1021 );
1022 if l_add_to_tbl then
1023 --
1024 -- 1. Update range and information tables.
1025 --
1029 --
1026 if not p_range_tbl.exists(crec.formula_id) then
1027 p_range_tbl(crec.formula_id).start_index := l_info_index;
1028 end if;
1030 p_range_tbl(crec.formula_id).end_index := l_info_index;
1031 p_ff_info_tbl(l_info_index).start_date := crec.start_date;
1032 p_ff_info_tbl(l_info_index).std_pkg_name := crec.std_pkg_name;
1033 p_ff_info_tbl(l_info_index).wrap_pkg_name := crec.wrap_pkg_name;
1034 --
1035 -- 2. Create formula-specific wrapper package.
1036 --
1037 create_specific_wrapper
1038 (p_wrapper_pkg_name => crec.wrap_pkg_name
1039 ,p_standard_pkg_name => crec.std_pkg_name
1040 );
1041 --
1042 -- 3. Update the wrapper package information.
1043 --
1044 if mod(l_info_index, g_formula_limit) = 1 then
1045 l_wrapper_num := l_wrapper_num + 1;
1046 l_wrap_info(l_wrapper_num).start_index := l_info_index;
1047 end if;
1048 l_wrap_info(l_wrapper_num).end_index := l_info_index;
1049 --
1050 -- 4. Increment index for the information table.
1051 --
1052 l_info_index := l_info_index + 1;
1053 end if;
1054 end loop;
1055
1056 if l_debug then
1057 hr_utility.trace('Formula information tables set-up complete.');
1058 end if;
1059
1060 ---------------------------------------------------------------
1061 -- B. Generate the main wrapper body, and, if necessary, the --
1062 -- intermediate wrapper packages. --
1063 ---------------------------------------------------------------
1064 --
1065 -- If only one wrapper is sufficient then just generate the
1066 -- main wrapper body.
1067 --
1068 if l_wrap_info.count > 1 then
1069 if l_debug then
1070 hr_utility.trace('Intermediate wrappers need to be generated.');
1071 end if;
1072
1073 for i in 1 .. l_wrap_info.count loop
1074 l_header_done := false;
1075 l_wrap_name := c_wrapper_name || to_char(i);
1076
1077 if l_debug then
1078 hr_utility.trace('Generating intermediate wrapper:' || l_wrap_name);
1079 end if;
1080
1081 --
1082 -- Generate the package headers.
1083 --
1084 open csr_package_exists(p_package_name => l_wrap_name);
1085 fetch csr_package_exists into l_dummy;
1086
1090 end if;
1087 if csr_package_exists%notfound then
1088 if l_debug then
1089 hr_utility.trace('Generate header:' || l_wrap_name);
1091
1092 l_text :=
1093 replace(c_package_head_start, c_name_replace, l_wrap_name) ||
1094 c_package_comments ||
1095 c_wrapper_formula1 || c_wrapper_formula2 || c_wrapper_formula3 ||
1096 c_wrapper_formula4 ||
1097 c_proc_head_end ||
1098 replace(c_package_end, c_name_replace, l_wrap_name);
1099 execute immediate l_text;
1100 l_header_done := true;
1101 else
1102 if l_debug then
1103 hr_utility.trace('Did not generate header:' || l_wrap_name);
1104 end if;
1105 end if;
1106
1107 close csr_package_exists;
1108 --
1109 -- Generate the package bodies.
1110 --
1111 generate_body
1112 (p_wrapper_name => l_wrap_name
1113 ,p_ff_info_tbl => p_ff_info_tbl
1114 ,p_wrap_info => l_wrap_info
1115 ,p_wrapper_num => i
1116 ,p_intermediates => false
1117 );
1118 end loop;
1119
1120 l_header_done := false;
1121 l_wrap_delno := l_wrap_info.count + 1;
1122
1123 --
1124 -- Generate the main package body with intermediates.
1125 --
1126 if l_debug then
1127 hr_utility.trace
1128 ('Generate main wrapper body with intermediate wrapper calls.');
1129 end if;
1130
1131 generate_body
1132 (p_wrapper_name => c_wrapper_main
1133 ,p_ff_info_tbl => p_ff_info_tbl
1134 ,p_wrap_info => l_wrap_info
1135 ,p_wrapper_num => null
1136 ,p_intermediates => true
1137 );
1138 else
1139 --
1140 -- Generate the main package body without intermediates.
1141 --
1142 if l_debug then
1143 hr_utility.trace('Generate main wrapper body with direct formula calls.');
1144 end if;
1145
1146 generate_body
1147 (p_wrapper_name => c_wrapper_main
1148 ,p_ff_info_tbl => p_ff_info_tbl
1149 ,p_wrap_info => l_wrap_info
1150 ,p_wrapper_num => 1
1151 ,p_intermediates => false
1152 );
1153
1154 l_wrap_delno := 1;
1155 end if;
1156
1157 --------------------------------------------
1158 -- C. Delete extraneous wrapper packages. --
1159 --------------------------------------------
1160 for crec in csr_wrap_pkgs(p_wrapper_name => c_wrapper_name || '%') loop
1161 begin
1162 l_wrapper_num :=
1163 to_number(replace(crec.object_name,c_wrapper_name,null));
1164 if l_wrapper_num >= l_wrap_delno then
1165 if l_debug then
1166 hr_utility.trace
1167 ('Dropping unused wrapper package:' || crec.object_name);
1168 end if;
1169 drop_package(p_package_name => crec.object_name);
1170 end if;
1171 exception
1172 --
1173 -- This package name could not have been in the correct format.
1174 --
1175 when others then
1176 null;
1177 end;
1178 end loop;
1179
1180 if l_debug then
1181 hr_utility.trace('Leaving:process_compiled');
1182 end if;
1183 exception
1184 when others then
1185 if l_debug then
1186 hr_utility.trace
1187 ('Leaving process_compiled with error:' || sqlcode || ':' || sqlerrm);
1188 end if;
1189
1190 if l_header_done then
1191 drop_package(p_package_name => l_wrap_name);
1192 end if;
1193
1194 if csr_package_exists%isopen then
1195 close csr_package_exists;
1196 end if;
1197
1198 raise;
1199 end process_compiled;
1200 ---------------------------- parse_pkg_name ----------------------------------
1201 -- Parse a potential formula-generated package name to extract the formula_id
1202 -- and start date. Format being parsed is:
1203 -- FFP<formula_id>_<start_date in DDMMYYYY format>
1204 --
1205 procedure parse_pkg_name
1206 (p_package_name in varchar2
1207 ,p_is_ff_name out nocopy boolean
1208 ,p_formula_id out nocopy number
1209 ,p_start_date out nocopy date
1210 ) is
1211 l_underscore_pos number;
1212 begin
1213 --
1214 -- 1. Look for the '_', the search starts after the initial FFP. Check
1215 -- that there is no possibility of a null formula_id or short date part.
1216 --
1217 l_underscore_pos := instr(p_package_name, '_', 1, 1);
1218 if l_underscore_pos <= 4 or l_underscore_pos > length(p_package_name) - 8 then
1219 p_is_ff_name := false;
1220 return;
1221 end if;
1222 --
1223 -- 2. Get the formula_id.
1224 --
1225 begin
1226 p_formula_id := to_number(substr(p_package_name, 4, l_underscore_pos - 4));
1227 exception
1228 when others then
1229 p_is_ff_name := false;
1230 return;
1231 end;
1232 --
1233 -- 3. Get the date part.
1234 --
1235 begin
1236 p_start_date :=
1237 to_date(substr(p_package_name, l_underscore_pos + 1), c_date_format);
1238 exception
1239 when others then
1240 p_is_ff_name := false;
1241 return;
1242 end;
1243 --
1244 -- It is an FF package name.
1245 --
1246 p_is_ff_name := true;
1247 end parse_pkg_name;
1248 ---------------------------- has_compiled_info -------------------------------
1249 --
1250 -- Check whether or not a particular formula can be found in the compiled
1251 -- information tables.
1252 --
1253 function has_compiled_info
1254 (p_formula_id in number
1255 ,p_start_date in date
1256 ,p_range_tbl in t_range_tbl
1260 l_has_compiled_info varchar2(10);
1257 ,p_ff_info_tbl in t_ff_info_tbl
1258 ) return boolean is
1259 --
1261 --
1262 cursor csr_compiled_info
1263 (p_formula_id in number
1264 ,p_start_date in date
1265 ) is
1266 select 'Y'
1267 from ff_compiled_info_f fci
1268 where fci.formula_id = p_formula_id
1269 and fci.effective_start_date = p_start_date
1270 ;
1271 begin
1272 if p_range_tbl.exists(p_formula_id) then
1273 for i in p_range_tbl(p_formula_id).start_index ..
1274 p_range_tbl(p_formula_id).end_index loop
1275 if p_start_date = p_ff_info_tbl(i).start_date then
1276 return true;
1277 end if;
1278 end loop;
1279 end if;
1280 --
1281 -- Now check to see if there was compiled information present but the
1282 -- formula was omitted from the range table because the package was
1283 -- invalid. Don't delete such formulas for the sake of consistency.
1284 --
1285 open csr_compiled_info
1286 (p_formula_id => p_formula_id
1287 ,p_start_date => p_start_date
1288 );
1289 fetch csr_compiled_info into l_has_compiled_info;
1290 if csr_compiled_info%notfound then
1291 l_has_compiled_info := 'N';
1292 end if;
1293 close csr_compiled_info;
1294 return l_has_compiled_info = 'Y';
1295 end has_compiled_info;
1296 ---------------------------- process_orphans ---------------------------------
1297 --
1298 -- Go through the FF standard (FFP) and wrapper (FFW) packages looking for
1299 -- orphans. Orphaned packages are those that cannot be in the compiled
1300 -- information tables and are dropped.
1301 --
1302 procedure process_orphans
1303 (p_range_tbl in t_range_tbl
1304 ,p_ff_info_tbl in t_ff_info_tbl
1305 ) is
1306 c_wrap_package constant number := 1;
1307 c_std_package constant number := 2;
1308 l_debug boolean := hr_utility.debug_enabled;
1309 l_prefix varchar2(16);
1310 l_formula_id number;
1311 l_is_ff_name boolean;
1312 l_start_date date;
1313 --
1314 cursor csr_ff_package_names(p_prefix in varchar2) is
1315 select uo.object_name
1316 from user_objects uo
1317 where uo.object_name like p_prefix
1318 and uo.object_type = c_package
1319 order by uo.object_name;
1320 begin
1321 if l_debug then
1322 hr_utility.trace('Entering:process_orphans');
1323 end if;
1324
1325 for package_type in c_wrap_package .. c_std_package loop
1326 --
1327 -- Iteration 1: FFW
1328 -- Iteration 2: FFP
1329 --
1330 if package_type = c_std_package then
1331 l_prefix := c_std_pkg_prefix || '%';
1332 else
1333 l_prefix := c_wrap_pkg_prefix || '%';
1334 end if;
1335
1336 for crec in csr_ff_package_names(p_prefix => l_prefix) loop
1337 --
1338 -- 1. Is it a formula package ?
1339 --
1340 parse_pkg_name
1341 (p_package_name => crec.object_name
1342 ,p_is_ff_name => l_is_ff_name
1343 ,p_formula_id => l_formula_id
1344 ,p_start_date => l_start_date
1345 );
1346 if l_is_ff_name then
1347 --
1348 -- 2. Is it an orphan ?
1349 --
1350 if not has_compiled_info
1351 (p_formula_id => l_formula_id
1352 ,p_start_date => l_start_date
1353 ,p_range_tbl => p_range_tbl
1354 ,p_ff_info_tbl => p_ff_info_tbl
1355 )
1356 then
1357 --
1358 -- 3. Drop orphaned package.
1359 --
1360 if l_debug then
1361 hr_utility.trace
1362 ('Deleting orphaned FF package:' || crec.object_name);
1363 end if;
1364
1365 drop_package(p_package_name => crec.object_name);
1366 end if;
1367 end if;
1368 end loop;
1369 end loop;
1370
1371 if l_debug then
1372 hr_utility.trace('Leaving:process_orphans');
1373 end if;
1374 end process_orphans;
1375 ----------------------------- create_wrapper ---------------------------------
1376 procedure create_wrapper is
1377 l_debug boolean := hr_utility.debug_enabled;
1378 l_range_tbl t_range_tbl;
1379 l_ff_info_tbl t_ff_info_tbl;
1380 l_chunks dbms_sql.varchar2s;
1381 l_csr_sql integer;
1382 l_rows number;
1383 begin
1384 if l_debug then
1385 hr_utility.trace('Entering:create_wrapper');
1386 end if;
1387
1388 --
1389 -- 1. Process the compiled formulas.
1390 --
1391 process_compiled
1392 (p_range_tbl => l_range_tbl
1393 ,p_ff_info_tbl => l_ff_info_tbl
1394 );
1395 --
1396 -- 2. Look for orphaned FF packages.
1397 --
1398 process_orphans
1399 (p_range_tbl => l_range_tbl
1400 ,p_ff_info_tbl => l_ff_info_tbl
1401 );
1402
1403 if l_debug then
1404 hr_utility.trace('Leaving:create_wrapper');
1405 end if;
1406 end create_wrapper;
1407 ----------------------------- create_wrapper ---------------------------------
1408 procedure create_wrapper
1409 (errbuf out nocopy varchar2
1410 ,retcode out nocopy number
1411 ) is
1412 begin
1413 retcode := 0;
1414 create_wrapper;
1415 exception
1416 when others then
1417 retcode := 2;
1418 errbuf := substrb(sqlerrm, 1, 240);
1419 end create_wrapper;
1420 end ff_wrapper_sup_pkg;