1 package body wms_atf_reg_cust_apis as
2 /* $Header: WMSARCAB.pls 115.10 2004/05/04 00:28:57 joabraha noship $ */
3 --
4 --
5 --
6 -- Oracle Internal DataType, Parameter, Default Codes and New Line Constants
7 --
8 c_dtype_undefined constant number default 0;
9 c_dtype_varchar2 constant varchar2(10) default 'VARCHAR2';
10 c_dtype_number constant varchar2(10) default 'NUMBER';
11 c_dtype_long constant varchar2(10) default 'LONG';
12 c_dtype_date constant varchar2(10) default 'DATE';
13 c_dtype_boolean constant varchar2(10) default 'BOOLEAN';
14 --
15 c_ptype_in constant varchar2(10) default 'IN';
16 c_ptype_out constant varchar2(10) default 'OUT';
17 c_ptype_in_out constant varchar2(10) default 'IN/OUT';
18 --
19 --
20 c_default_defined constant number default 1;
21 --
22 --
23 -- Error Exceptions which can be raised by dbms_describe.describe_procedure
24 --
25 --
26 -- Specified Object does not exist
27 --
28 Object_Not_Exists exception;
29 Pragma Exception_Init(Object_Not_Exists, -4043);
30 --
31 -- Package does not exist in the database
32 --
33 Package_Not_Exists exception;
34 Pragma Exception_Init(Package_Not_Exists, -6564);
35 --
36 -- Procedure does not exist in the package
37 --
38 Proc_Not_In_Package exception;
39 Pragma Exception_Init(Proc_Not_In_Package, -20001);
40 --
41 -- Object is remote
42 --
43 Remote_Object exception;
44 Pragma Exception_Init(Remote_Object, -20002);
45 --
46 -- Package is invalid
47 --
48 Invalid_Package exception;
49 Pragma Exception_Init(Invalid_Package, -20003);
50 --
51 -- Invalid Object Name
52 --
53 Invalid_Object_Name exception;
54 Pragma Exception_Init(Invalid_Object_Name, -20004);
55 --
56 --
57 -- Oracle Internal DataType, Parameter, Default Codes and New Line Constants
58 --
59 c_valid_varchar2 constant varchar2(10) default 'VALID';
60 c_invalid_varchar2 constant varchar2(10) default 'INVALID';
61 c_create_mode constant varchar2(10) default 'CREATE';
62 c_delete_mode constant varchar2(10) default 'DELETE';
63 --
64 --
65 -- Package Variables
66 --
67 g_spec_string varchar2(32767) := null;
68 g_body_string varchar2(32767) := null;
69 --
70 --
71 g_hook_parameter_table hook_parameter_table_type;
72 --
73 -- Global variable to hold parameter table information.
74 --
75 g_parameter_table hook_parameter_table_type;
76 --
77 --
78 g_sysgen_custom_package varchar2(240);
79 g_sysgen_custom_procedure varchar2(240);
80 --
81 --
82 -- Oracle Internal DataType, Parameter, Default Codes and New Line Constants
83 --
84 --c_dtype_undefined constant number default 0;
85 --c_dtype_varchar2 constant number default 1;
86 --c_dtype_number constant number default 2;
87 --c_dtype_long constant number default 8;
88 --c_dtype_date constant number default 12;
89 --c_dtype_boolean constant number default 252;
90 --
91 --c_ptype_in constant number default 0;
92 --c_ptype_out constant number default 1;
93 --c_ptype_in_out constant number default 12;
94 --
95 --
96 -- Other Error Exceptions
97 --
98 Plsql_Value_Error exception;
99 Pragma Exception_Init(Plsql_Value_Error, -6502);
100 --
101 -- New line variable.
102 c_new_line constant varchar2(1) default '
103 ';
104 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
105 --
106 --
107 -- -------------------------------------------------------------------------------------------
108 -- |--------------------------< trace utility >-----------------------------------------------|
109 -- -------------------------------------------------------------------------------------------
110 -- {Start Of Comments}
111 --
112 -- Description:
113 -- Wrapper around the tracing utility.
114 --
115 -- Prerequisites:
116 -- None
117 --
118 -- In Parameters:
119 -- Name Reqd Type Description
120 -- --------- ----- -------- --------------------------------------------
121 -- p_message Yes varchar2 Message to be displayed in the log file.
122 -- p_level No number Level default to the lowest(4) if not specified.
123 --
124 -- Post Success:
125 -- None.
126 --
127 -- Post Failure:
128 -- None
129 --
130 -- Access Status:
131 -- Internal Development Use Only.
132 --
133 -- {End Of Comments}
134 --
135 Procedure trace(
136 p_message in varchar2
137 , p_level in number
138 ) is
139 begin
140 INV_LOG_UTIL.trace(p_message, 'WMS_ATF_REG_CUST_APIS', p_level);
141 end trace;
142
143 -- -------------------------------------------------------------------------------------------
144 -- |------------------------< populate_paramater_table >--------------------------------------|
145 -- -------------------------------------------------------------------------------------------
146 -- {Start Of Comments}
147 --
148 -- Description:
149 -- Populate the PL/SQL structure(hook_parameter_table_type) with the
150 -- parameters of the signature for the Parent Module/Business Process/ PL/SQL
151 -- Package-Procedure combination
152 --
153 -- Prerequisites:
154 -- p_module_hook_id is set with the proper value.
155 --
156 --
157 -- In Parameters:
158 -- Name Reqd Type Description
159 -- ------------------ ---- -------- ----------------------------------
160 -- p_module_hook_id Yes varchar2 Unique record identifier for
161 -- the parent Module/Business
162 -- Process/ PL/SQL Package-Procedure
163 -- combination.
164 -- p_parameter_table Yes table This PL/SQL table contains the
165 -- type signature information for the
166 -- p_module_hook_id.
167 -- x_return_status Yes number Return Status
168 -- x_msg_count Yes number Message Stack Count.
169 -- x_msg_data Yes number Message Stack Data.
170 --
171 -- Post Success:
172 -- Returns true. Returns a PL/SQL of type hook_parameter_table_type.
173 --
174 -- Post Failure:
175 -- Details of the error are added to the AOL message stack. When this
176 -- function returns false the error has not been raised. It is up to the
177 -- calling logic to raise or process the error.
178 --
179 -- Access Status:
180 -- Internal Development Use Only.
181 --
182 -- {End Of Comments}
183 --
184 Procedure populate_parameter_table(
185 p_module_hook_id in number
186 , p_parameter_table out nocopy hook_parameter_table_type
187 ) is
188
189 l_loop number:= 0; -- Loop counter
190 l_proc varchar2(72) := 'populate_parameter_table :';
191
192 cursor c_get_signature is
193 select parameter_name, parameter_in_out, parameter_type
194 from wms_api_hook_signatures
195 where module_hook_id = p_module_hook_id;
196
197 begin
198 if (l_debug = 1) then
199 trace(' Entering:'|| l_proc, 1);
200 trace(' p_module_hook_id => ' || p_module_hook_id, 4);
201 end if;
202
203 for v_get_signature in c_get_signature
204 loop
205 l_loop := l_loop + 1;
206 p_parameter_table(l_loop).parameter_name := v_get_signature.parameter_name;
207 p_parameter_table(l_loop).parameter_in_out := v_get_signature.parameter_in_out;
208 p_parameter_table(l_loop).parameter_type := v_get_signature.parameter_type;
209
210 -- ### This flag will be used to indicate if a matching parameter has been found
211 -- ### in the signature of the call package.procedure. If found, this will be set
212 -- ### to 'Y' and hence the comparison while loop in the chk_param_in_hook_proc_call
213 -- ### will not traverese this record in the PL/SQL table.
214 p_parameter_table(l_loop).parameter_flag := 'N';
215 end Loop;
216
217 if (l_debug = 1) then
218 trace(' Leaving:'||l_proc, 1);
219 for i in 1..p_parameter_table.count
220 loop
221 trace(' parameter_name'||'('||i||')'||' = '|| p_parameter_table(i).parameter_name
222 ||' parameter_type'||'('||i||')'||' = '|| p_parameter_table(i).parameter_type
223 ||' parameter_in_out'||'('||i||')'||' = '|| p_parameter_table(i).parameter_in_out
224 ||' parameter_flag'||'('||i||')'||' = '|| p_parameter_table(i).parameter_flag, 4);
225 end loop;
226 end if;
227 end populate_parameter_table;
228 --
229 --
230 --
231 --
232 -- -------------------------------------------------------------------------------------------
233 -- |------------------------------< add_to_string >------------------------------------------|
234 -- -------------------------------------------------------------------------------------------
235 -- {Start Of Comments}
236 --
237 -- Description:
238 -- Appends the specified string to the end of the existing string. The intent
239 -- here is the create separate strings for the spec and the body. Within this
240 -- routine, based on the string type ('S' or 'B') passed in, seperate strings will be appended.
241 -- Mode of 'SB' is the string which is required to be appended to both the global
242 -- variables
243 -- Prerequisites:
244 -- None
245 --
246 -- In Parameters:
247 -- Name Reqd Type Description
248 -- ------ ---- -------- ----------------------------------------------------------
249 -- p_text Yes varchar2 Source string to add to the existing string
250 -- .
251 -- p_type Yes varchar2 String type being passed in. This determines if the string
252 -- passed in should be appended to the spec string or the body
253 -- string.
254 --
255 -- Post Success:
256 -- The extra source string is added to the existing string.
257 --
258 -- Post Failure:
259 -- If the source code size limit is exceeded then an application error
260 -- message is raised.
261 --
262 -- Access Status:
263 -- Internal Development Use Only.
264 --
265 -- {End Of Comments}
266 --
267 Procedure add_to_string(
268 p_text in varchar2
269 , p_type in varchar2
270 ) is
271
272 l_proc varchar2(72) := g_package||' add_to_string';
273 begin
274 --trace('Entering:'|| l_proc);
275 if p_type = 'S' then
276 g_spec_string := g_spec_string || p_text;
277 elsif p_type = 'B' then
278 g_body_string := g_body_string || p_text;
279 elsif p_type = 'SB' then
280 g_spec_string := g_spec_string || p_text;
281 g_body_string := g_body_string || p_text;
282 end if;
283
284 --trace(' Leaving:'||l_proc);
285 end add_to_string;
286 --
287 --
288 -- -------------------------------------------------------------------------------------------
289 -- |---------------------------< create_package_header >--------------------------------------|
290 -- -------------------------------------------------------------------------------------------
291 -- {Start Of Comments}
292 --
293 -- Description:
294 -- This procedure creates the package header and procedure string common to the spec and the .
295 -- body.
296 --
297 -- Prerequisites:
298 -- None
299 --
300 -- In Parameters:
301 -- Name Reqd Type Description
302 -- ----------------- ---- -------- --------------
303 -- p_module_hook_id Yes varchar2 Module Hook ID..
304 -- p_parameter_table Yes varchar2 Table containing the signature
305 -- definition.
306 --
307 -- Post success:
308 -- The sommon system package header is created in the database.
309 --
310 -- Post Failure:
311 -- None
312 --
313 -- Unexpected Oracle errors and serious application errors will be raised
314 -- as a PL/SQL exception. When these errors are raised this procedure will
315 -- abort the processing.
316 --
317 -- Access Status:
318 -- Internal Development Use Only.
319 --
320 -- {End Of Comments}
321 Procedure create_package_header(
322 p_module_hook_id in number
323 , p_parameter_table in hook_parameter_table_type
324 ) is
325 --
326 l_proc varchar2(72) := g_package||'create_package_header';
327 begin
328 --trace('Entering:'|| l_proc);
329 --
330 -- Building comments at the start of the package body
331 --
332 add_to_string('/*******************************************************************/', 'SB');
333 add_to_string(c_new_line, 'SB');
334 add_to_string('-- Code generated by the Oracle WMS Custom API Registration Processor', 'SB');
335 add_to_string(c_new_line, 'SB');
336 add_to_string('-- No user defined procedures allowed in this package.', 'SB');
337 add_to_string(c_new_line, 'SB');
338 add_to_string('-- Created on ' || to_char(sysdate, 'YY/MM/DD HH24:MI:SS'), 'SB');
339 add_to_string(' (YY/MM/DD HH:MM:SS)' || c_new_line, 'SB');
340 add_to_string('/*******************************************************************/', 'SB');
341 add_to_string(c_new_line, 'SB');
342 add_to_string(c_new_line, 'SB');
343 --
344 -- Building code for the Procedure of the system package
345 --
346 add_to_string('create or replace package ' || g_sysgen_custom_package, 'S');
347 add_to_string(' as' || c_new_line, 'S');
348 add_to_string('create or replace package body ' || g_sysgen_custom_package, 'B');
349 add_to_string(' as' || c_new_line, 'B');
350
351 add_to_string('-- Procedure for Module Hook ID ' || p_module_hook_id, 'SB');
352 add_to_string('-- ' || c_new_line, 'SB');
353 --
354 -- Creating Procedure definition.
355 add_to_string(c_new_line, 'SB');
356 add_to_string('Procedure ' || g_sysgen_custom_procedure || '(', 'SB');
357 add_to_string(c_new_line, 'SB');
358
359 for i in 1..p_parameter_table.count
360 loop
361 add_to_string(' '|| rpad(p_parameter_table(i).parameter_name, 31), 'SB');
362 add_to_string(' '|| p_parameter_table(i).parameter_in_out, 'SB');
363 -- Adding mandatory nocopy to the OUT parameters.
364 -- Added August 21st 2003
365 if (p_parameter_table(i).parameter_in_out in ('OUT', 'out')) then
366 add_to_string(' NOCOPY', 'SB');
367 end if;
368 add_to_string(' '|| p_parameter_table(i).parameter_type, 'SB');
369 add_to_string(','||c_new_line, 'SB');
370 end Loop;
371 -- Add the mandatory 'IN' paramter to the end of the list of paramters.
372 --
373 add_to_string(' '|| rpad('p_hook_call_id', 31), 'SB');
374 add_to_string(' IN', 'SB');
375 add_to_string(' NUMBER', 'SB');
376 add_to_string(c_new_line, 'SB');
377
378 add_to_string(' );'|| c_new_line, 'S');
379 add_to_string(' ) is', 'B');
380 add_to_string('end '||g_sysgen_custom_package||';'|| c_new_line, 'S');
381 add_to_string(c_new_line, 'B');
382 add_to_string('begin '|| c_new_line, 'B');
383
384 --trace(' Leaving:'||l_proc);
385 end create_package_header;
386 --
387 --
388 -- -------------------------------------------------------------------------------------------
389 -- |---------------------------< create_package_body >----------------------------------------|
390 -- -------------------------------------------------------------------------------------------
391 -- {Start Of Comments}
392 --
393 -- Description:
394 -- This procedure creates the package body string.
395 --
396 -- Prerequisites:
397 -- None
398 --
399 -- In Parameters:
400 -- Name Reqd Type Description
401 -- ------------------ ---- -------- -----------------------------------------------
402 -- p_called_package Yes varchar2 Call package Name.
403 -- p_called_procedure Yes varchar2 Call procedure Name.
404 -- p_module_hook_id Yes varchar2 Module Hook ID.
405 -- p_parameter_table Yes table type Signature table of type hook_parameter_table_type
406 -- p_iteration Yes number Iteration counter
407 --
408 --
409 -- Post success:
410 -- A system package spec is created in the database.
411 --
412 -- Post Failure:
413 -- None
414 --
415 -- Unexpected Oracle errors and serious application errors will be raised
416 -- as a PL/SQL exception. When these errors are raised this procedure will
417 -- abort the processing.
418 --
419 -- Access Status:
420 -- Internal Development Use Only.
421 --
422 -- {End Of Comments}
423 Procedure create_package_body(
424 p_called_package in varchar2
425 , p_called_procedure in varchar2
426 , p_hook_call_id in number
427 , p_parameter_table in hook_parameter_table_type
428 , p_iteration in number
429 ) is
430 --
431 l_proc varchar2(72) := g_package||'create_package_body';
432 begin
433 --trace('Entering:'|| l_proc);
434
435 -- For the very first iteration of the loop, start with a 'if.. then' and every subsequent
436 -- iteration is an elsif...then for further iterations.
437 if p_iteration = 1 then
438 add_to_string('If p_hook_call_id = ' || p_hook_call_id || ' then'|| c_new_line, 'B');
439 else
440 add_to_string(c_new_line, 'B');
441 add_to_string('elsif p_hook_call_id = ' || p_hook_call_id || ' then'|| c_new_line, 'B');
442 end if;
443
444 add_to_string(' '||p_called_package||'.'||p_called_procedure||'('|| c_new_line, 'B');
445
446 for i in 1..p_parameter_table.count
447 loop
448 add_to_string(' '|| rpad(p_parameter_table(i).parameter_name, 31), 'B');
449 add_to_string(' => '|| p_parameter_table(i).parameter_name , 'B');
450
451 if i <> p_parameter_table.count then
452 add_to_string(','||c_new_line, 'B');
453 else
454 add_to_string(c_new_line, 'B');
455 end if;
456
457 end loop;
458 add_to_string(' );', 'B');
459
460 --trace(' Leaving:'||l_proc);
461 end create_package_body;
462 --
463 --
464 --
465 --
466 -- -------------------------------------------------------------------------------------------
467 -- |----------------------------< execute_source >--------------------------------------------|
468 -- -------------------------------------------------------------------------------------------
469 -- {Start Of Comments}
470 --
471 -- Description:
472 -- Executes the 'create or replace package body...' statement which has
473 -- been built-up in in the g_spec_string string.
474 --
475 -- Prerequisites:
476 -- The complete valid package body source code has been placed in the source
477 -- store by calling the 'add_to_string' procedure one or more times.
478 --
479 -- In Parameters:
480 -- None
481 --
482 -- Post Success:
483 -- None
484 --
485 -- Post Failure:
486 -- None
487 --
488 -- Access Status:
489 -- Internal Development Use Only.
490 --
491 -- {End Of Comments}
492 --
493 Procedure execute_source(
494 g_string in varchar2
495 ) is
496
497 l_dynamic_spec_cursor integer; -- Dynamic sql cursor
498 l_execute_spec integer; -- Value returned by
499 -- dbms_sql.execute
500 l_dynamic_body_cursor integer; -- Dynamic sql cursor
501 l_execute_body integer; -- Value returned by
502 -- dbms_sql.execute
503
504 l_proc varchar2(72) := g_package||'execute_source';
505 l_progress number;
506 begin
507 --
508 -- The whole of the new package body code has now been built,
509 -- use dynamic SQL to execute the create or replace package statement
510 --
514 trace(l_proc ||' g_string => ' || g_string, 4);
511 l_dynamic_spec_cursor := dbms_sql.open_cursor;
512 if (l_debug = 1) then
513 trace(l_proc ||' Entering:'|| l_proc, 1);
515 trace(l_proc ||' Starting Generation.....'|| l_proc, 4);
516 trace(l_proc ||' l_dynamic_spec_cursor = ' || l_dynamic_spec_cursor, 4);
517 end if;
518
519 l_progress := 10;
520 dbms_sql.parse(l_dynamic_spec_cursor, g_string, dbms_sql.native);
521
522 l_progress := 20;
523 l_execute_spec := dbms_sql.execute(l_dynamic_spec_cursor);
524
525 if (l_debug = 1) then
526 trace(l_proc ||'l_execute_spec = ' || l_execute_spec, 4);
527 end if;
528
529 l_progress := 30;
530 dbms_sql.close_cursor(l_dynamic_spec_cursor);
531
532 if (l_debug = 1) then
533 trace(l_proc ||' Finished Generating Spec...:'|| l_proc, 1);
534 end if;
535
536 if (l_debug = 1) then
537 trace(l_proc ||' Leaving:'|| l_proc);
538 end if;
539 exception
540 --
541 -- In case of an unexpected error close the dynamic cursor
542 -- if it was successfully opened.
543 --
544 when others then
545 if (l_debug = 1) then
546 trace(l_proc ||' Error message within "When Others" exception ' || sqlerrm(sqlcode) || ' Progress : ' || l_progress || ' ' || l_proc, 1);
547 end if;
548
549 if (dbms_sql.is_open(l_dynamic_spec_cursor)) then
550 if (l_debug = 1) then
551 trace(l_proc ||' Closing Cursor ....');
552 end if;
553 dbms_sql.close_cursor(l_dynamic_spec_cursor);
554 end if;
555 end execute_source;
556 --
557 --
558 --
559 -- -------------------------------------------------------------------------------------------
560 -- |---------------------------< create_system_package >--------------------------------------|
561 -- -------------------------------------------------------------------------------------------
562 -- {Start Of Comments}
563 --
564 -- Description:
565 -- This procedure creates the spec and body string for the system generated
566 -- package. These strings may be used later to generate the spec and the body
567 -- for the system package.
568 --
569 -- Prerequisites:
570 -- None
571 --
572 -- In Parameters:
573 -- Name Reqd Type Description
574 -- ---------------- ---- -------- -------------------
575 -- p_module_hook_id Yes varchar2 Module Hook ID.
576 -- x_return_status Yes number Return Status
577 -- x_msg_count Yes number Message Stack Count.
578 -- x_msg_data Yes number Message Stack Data.
579 --
580 --
581 -- Post success:
582 -- A system package spec is created in the database.
583 --
584 -- Post Failure:
585 -- Unexpected Oracle errors and serious application errors will be raised
586 -- as a PL/SQL exception. When these errors are raised this procedure will
587 -- abort the processing.
588 --
589 -- Access Status:
590 -- Internal Development Use Only.
591 --
592 -- {End Of Comments}
593 Procedure create_wms_system_objects(
594 x_retcode out nocopy number
595 , x_errbuf out nocopy varchar2
596 ) is
597
598 l_number_of_parameters number;
599 l_outer_loop number;
600 l_inner_loop number;
601 l_middle_loop number;
602 l_return_status varchar2(240);
603 l_msg_count number;
604 l_msg_data varchar2(240);
605 l_current_package_cntr number;
606
607 -- This variable indicates the number of packages to be created.
608 l_no_of_packages number;
609 l_selected_cntr number;
610
611 -- Required for droppping packages..
612 l_csr_sql integer;
613 l_rows integer;
614 l_package_name_drop varchar2(100);
615
616 l_proc varchar2(72) := 'create_wms_system_objects :';
617 l_progress number;
618
619 cursor c_api_hooked_entities is
620 select wahe.module_hook_id, wahe.module_type_id, wahe.business_process_id,
621 wahe.short_name_id, wahe.sysgen_custom_package, wahe.sysgen_custom_procedure,
622 wahe.hooked_package, wahe.hooked_procedure, wahe.current_package_cntr
623 from wms_api_hooked_entities wahe;
624
625 cursor c_api_hook_calls(l_module_hook_id number) is
626 select hook_call_id, enabled_flag, called_package, called_procedure,
627 effective_from_date, effective_to_date
628 from wms_api_hook_calls
629 where module_hook_id = l_module_hook_id
630 and enabled_flag = 'Y'
631 and (effective_to_date >= sysdate or effective_to_date is null)
632 order by hook_call_id;
633
634 cursor c_drop_sysgen_packages(l_module_hook_id number) is
635 select sysgen_custom_package, current_package_cntr
636 from wms_api_hooked_entities
637 where module_hook_id = l_module_hook_id;
638
639 begin
640
641 l_outer_loop := 0;
642 for v_api_hooked_entities in c_api_hooked_entities
643 loop
644 l_outer_loop := l_outer_loop + 1;
645 if (l_debug = 1) then
646 trace(l_proc ||' Iteration No ' || l_outer_loop || ' in the outer loop');
647 trace(l_proc ||' ***module hook id ' ||v_api_hooked_entities.module_hook_id|| ' ***');
648 trace(l_proc ||' ***short name ' ||v_api_hooked_entities.short_name_id|| ' ***');
649 end if;
650
651 -- Check which file currently being used. The idea here is to generate 2 files
652 -- simultaneously at the time of generation so that when one package is being used
653 -- the other one can be updated.
654 if v_api_hooked_entities.current_package_cntr is null then
655 l_current_package_cntr := 1;
656 l_no_of_packages := 2;
657 elsif v_api_hooked_entities.current_package_cntr = 1 then
661 l_current_package_cntr := 1;
658 l_current_package_cntr := 2;
659 l_no_of_packages := 1;
660 elsif v_api_hooked_entities.current_package_cntr = 2 then
662 l_no_of_packages := 1;
663 end if;
664
665 if (l_debug = 1) then
666 trace(l_proc ||' *** l_current_package_cntr ' || l_current_package_cntr);
667 trace(l_proc ||' *** module hook id ' || v_api_hooked_entities.module_hook_id);
668 end if;
669
670
671 l_middle_loop := 0;
672 -- Loop to determine the number of set of packages to be created.
673 -- If the current_package_cntr is null then, 2 sets of packages have to be created.
674 -- In all other cses, only one set needs to be updated.
675 for i in 1..l_no_of_packages
676 loop
677 l_middle_loop := l_middle_loop + 1;
678 if (l_debug = 1) then
679 trace(l_proc ||' Iteration No ' || l_middle_loop || ' in the middle loop');
680 end if;
681
682 -- This is not incremented for the first iteration. The numebr of interations
683 -- is restricted by the l_no_of_packages in the for loop and so the max value the
684 -- l_current_package_cntr can have is 2.
685 if (i <> 1) then
686 l_current_package_cntr := l_current_package_cntr + 1;
687 if (l_debug = 1) then
688 trace(l_proc ||' *** Inside the l_current_package_cntr incrementer if.. end if***');
689 end if;
690 end if;
691
692 if i = 1 then
693 -- Populate the parameter table with the signature definition for every iteration of the
694 -- outer loop. This will be used to compare the signature of the call
695 -- procedure which is intended to be registered.
696 populate_parameter_table(
697 p_module_hook_id => v_api_hooked_entities.module_hook_id
698 , p_parameter_table => g_hook_parameter_table
699 );
700
701 If (g_hook_parameter_table.count = 0) then
702 -- Parameter table is empty
703 x_retcode := 2;
704 x_errbuf := 'Error';
705 return;
706 else
707 -- Variable to keep count of number of parameters in the parent signature.
708 -- This is used in the code later.
709 l_number_of_parameters := g_hook_parameter_table.count;
710 end if;
711 end if;
712
713 -- Initialise the variables at start.
714 -- Since the intent here is to create a new package spec/body for every
715 -- unique module_hook_id, the variables are reset fro each iteration.
716 g_spec_string := null;
717 g_body_string := null;
718 g_sysgen_custom_package := v_api_hooked_entities.sysgen_custom_package ||'_'||l_current_package_cntr;
719 g_sysgen_custom_procedure := v_api_hooked_entities.sysgen_custom_procedure;
720
721 if (l_debug = 1) then
722 trace(l_proc ||' ***sysgen package name ' ||v_api_hooked_entities.sysgen_custom_package|| ' ***');
723 trace(l_proc ||' ***sysgen procedure name ' ||v_api_hooked_entities.sysgen_custom_procedure|| ' ***');
724 end if;
725
726 -- Call to routine to construct the header string for the spec and the body.
727 -- Two separate global string variables are being populated one each for the
728 -- spec and the body.
729 -- The idea here it to create a new spec/body for every unique module_hook_id.
730 -- Every unique module_hook_id will have a unique signature for most cases.
731 create_package_header(
732 p_module_hook_id => v_api_hooked_entities.module_hook_id
733 , p_parameter_table => g_hook_parameter_table
734 );
735
736 l_inner_loop := 0;
737 for v_api_hook_calls in c_api_hook_calls(v_api_hooked_entities.module_hook_id)
738 loop
739 l_inner_loop := l_inner_loop + 1;
740 if (l_debug = 1) then
741 trace(l_proc ||' Iteration No ' || l_inner_loop || ' in the outer loop');
742 trace(l_proc ||' ***hook call id ' ||v_api_hook_calls.hook_call_id|| ' ***');
743 trace(l_proc ||' ***call package name ' ||v_api_hook_calls.called_package|| ' ***');
744 trace(l_proc ||' ***call procedure name ' ||v_api_hook_calls.called_procedure|| ' ***');
745 end if;
746
747 -- Call to routine to construct the if ...else clause in the package body
748 -- to call cll package/procedure registered foe a specific module_hook_id.
749 create_package_body(
750 p_called_package => v_api_hook_calls.called_package
751 , p_called_procedure => v_api_hook_calls.called_procedure
752 , p_hook_call_id => v_api_hook_calls.hook_call_id
753 , p_parameter_table => g_hook_parameter_table
754 , p_iteration => l_inner_loop
755 );
756
757 if (l_debug = 1) then
758 trace(l_proc ||' End of Iteration Number ' || l_inner_loop || ' in the Outer Loop');
759 end if;
760 end loop;
761
762 if l_inner_loop = 0 then
763 -- no records found in wms_api_hook_calls table
764 -- Hence insert a null between the begin and end in the body so that the
765 -- package generation will not fail. There can be cases where all relationships
766 -- for a parent may be disabled and this cursor will not return records.
767 add_to_string(c_new_line, 'B');
768 add_to_string('null;', 'B');
769 add_to_string(c_new_line, 'B');
773 end if;
770 else
771 add_to_string(c_new_line, 'B');
772 add_to_string('end if; '|| c_new_line, 'B');
774
775 add_to_string('end; '|| c_new_line, 'B');
776 add_to_string(c_new_line, 'B');
777 add_to_string('end '||g_sysgen_custom_package||';'|| c_new_line, 'B');
778
779 --
780 -- Drop the current package counter to be recreated. Do not drop both the packages because
781 -- the other one may be in use.
782 --
783 for v_drop_sysgen_packages in c_drop_sysgen_packages(v_api_hooked_entities.module_hook_id)
784 loop
785 begin
786 if (l_debug = 1) then
787 trace(l_proc ||' Sysgen_custom_package : ' ||v_drop_sysgen_packages.sysgen_custom_package);
788 trace(l_proc ||' Current Pkg Counter : ' ||v_drop_sysgen_packages.current_package_cntr);
789 end if;
790
791 l_package_name_drop := v_drop_sysgen_packages.sysgen_custom_package ||'_'||l_current_package_cntr;
792
793 if (l_debug = 1) then
794 trace(l_proc ||' drop package name constructed : ' || l_package_name_drop);
795 end if;
796
797 l_csr_sql := dbms_sql.open_cursor;
798 dbms_sql.parse
799 (l_csr_sql
800 ,'DROP PACKAGE BODY ' || l_package_name_drop
801 ,dbms_sql.native
802 );
803 l_rows := dbms_sql.execute( l_csr_sql );
804 dbms_sql.close_cursor( l_csr_sql );
805 exception
806 when others then
807 --
808 -- Drop package failed.
809 --
810 if (l_debug = 1) then
811 trace(l_proc ||' Drop package statement failed to drop package');
812 trace(l_proc ||' Drop Package Error Code = ' || sqlcode);
813 trace(l_proc ||' Drop Package Error Message = ' || sqlerrm);
814 end if;
815
816 if dbms_sql.is_open( l_csr_sql ) then
817 dbms_sql.close_cursor( l_csr_sql );
818 end if;
819 end;
820 end loop;
821
822 if (l_debug = 1) then
823 trace(l_proc ||' g_spec_string : ' || c_new_line || g_spec_string);
824 trace(l_proc ||' g_body_string : ' || c_new_line || g_body_string);
825 end if;
826
827 -- Generate the spec and body for each iteration of the module_hook_id and its associated
828 -- call package(s)/procedure(s)
829 execute_source(g_spec_string);
830 execute_source(g_body_string);
831
832 -- Update wms_api_hooked_entities to indicate which is the current package in use
833 -- to avoid being updated when in use.
834 begin
835 update wms_api_hooked_entities
836 set current_package_cntr = l_current_package_cntr
837 where module_hook_id = v_api_hooked_entities.module_hook_id;
838
839 commit;
840 exception
841 when others then
842 if (l_debug = 1) then
843 trace(l_proc ||' Update wms_api_hooked_entities failed with error = ' || sqlerrm(sqlcode));
844 end if;
845 x_retcode := 2;
846 x_errbuf := 'Error';
847 return;
848 end;
849
850 if (l_debug = 1) then
851 trace(l_proc ||' End of Iteration Number ' || l_outer_loop || ' in the Outer Loop');
852 end if;
853 end loop;
854 end loop;
855
856 if (l_debug = 1) then
857 trace(l_proc ||' Final Number of Outer Loops : ' || l_outer_loop);
858 trace(l_proc ||' Final Number of Middle Loops : ' || l_middle_loop);
859 trace(l_proc ||' Final Number of Inner Loops : ' || l_inner_loop);
860 end if;
861
862 end create_wms_system_objects;
863 --
864 --
865 -- -------------------------------------------------------------------------------------------
866 -- |---------------------< chk_param_in_hook_proc_call >--------------------------------------|
867 -- -------------------------------------------------------------------------------------------
868 -- {Start Of Comments}
869 --
870 -- Description:
871 -- This procedure is responsible for validating the eligibility of a
872 -- package.procedure to be hooked to a parent package.procedure. Checks are
873 -- to ensure that the signature of the custom(in fact called) API conforms
874 -- to the signature registered witht eh aprent record.
875 -- If the parameter should be on a procedure checks the call is not to a
876 -- function. If an error is found AOL error details are set but a PL/SQL
877 -- exception is not raised.
878 --
879 -- Prerequisites:
880 -- p_number_of_parameters, p_hook_parameter_names and
881 -- p_hook_parameter_datatypes are set with details of the hook package
882 -- procedure parameter details.
883 --
884 -- In Parameters:
885 -- Name Reqd Type Description
886 -- --------------------- ---- -------- ---------------------------------------------
887 -- p_call_parameter_name Yes varchar2 Parameter in the procedure to be called.
888 -- p_call_parameter_datatype Yes number The internal code for the parameter datatype.
889 -- p_call_parameter_in_out Yes number The internal code for the parameter IN/OUT type.
890 -- p_call_parameter_overload Yes number The overload number for the call procedure parameter.
891 -- p_previous_overload Yes number The overload number for the previous parameter on the
892 -- call procedure.
896 -- Returns true.
893 -- p_param_valid Yes boolean Indicates if the parameter is valid.
894 --
895 -- Post Success:
897 --
898 -- Post Failure:
899 -- Details of the error are added to the AOL message stack. When this
900 -- function returns false the error has not been raised. It is up to the
901 -- calling logic to raise or process the error.
902 --
903 -- Access Status:
904 -- Internal Development Use Only.
905 --
906 -- {End Of Comments}
907 --
908 Procedure chk_param_in_hook_proc_call
909 ( p_call_parameter_name in varchar2
910 , p_call_parameter_datatype in number
911 , p_call_parameter_in_out in number
912 , p_call_parameter_overload in number
913 , p_previous_overload in number
914 , p_parameter_position in number
915 , p_param_valid out nocopy boolean
916 , x_retcode out nocopy number
917 , x_errbuf out nocopy varchar2
918 ) is
919 --
920 -- Variables to store converted values for the paramater table elements.
921 --
922 l_parameter_type number;
923 l_parameter_in_out number;
924 l_number_of_parameters number:= g_parameter_table.count;
925 --
926 --
927 --
928 l_loop number; -- Loop counter
929 l_param_found boolean; -- Indicates if the parameter has been
930 -- found in the hook parameter list.
931 l_param_valid boolean; -- Indicates if parameter is valid.
932
933 l_proc varchar2(72) := 'chk_param_in_hook_proc_call :';
934 begin
935 if (l_debug =1 ) then
936 trace('Entering Procedure '|| l_proc ||':' || to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
937 trace(l_proc||'p_call_parameter_name = ' || p_call_parameter_name);
938 trace(l_proc||'p_call_parameter_datatype = ' || p_call_parameter_datatype);
939 trace(l_proc||'p_call_parameter_in_out = ' || p_call_parameter_in_out);
940 trace(l_proc||'p_call_parameter_overload = ' || p_call_parameter_overload);
941 trace(l_proc||'p_previous_overload = ' || p_previous_overload);
942 trace(l_proc||'l_number_of_parameters = '|| l_number_of_parameters);
943 end if;
944 --
945 -- Assume the parameter is valid until an error is found
946 --
947 l_param_valid := true;
948 --
949 -- Validate the call does not have any overload versions by
950 -- checking that the overload number for the current parameter is the
951 -- same as the previous parameter.
952 --
953 if p_call_parameter_overload <> p_previous_overload then
954 -- Error: A call package procedure cannot have any PL/SQL overloaded
955 -- versions. Code to carry out this hook call has not been created.
956 if (l_debug =1 ) then
957 trace(l_proc ||' Within p_call_parameter_overload <> p_previous_overload');
958 trace(l_proc ||' Illegal for Custom procedure to have an overloaded signature');
959 end if;
960 l_param_valid := false;
961 return;
962 --
963 -- Check the argument name has been set. If it is not set the entry
964 -- returned from describe_procedure is for a function
965 -- return value. Package functions should not be called.
966 --
967 elsif p_call_parameter_name is null then
968 -- Error: A package function cannot be called. Only package procedures
969 -- can be called. Code to carry out this hook call has not been created.
970 if (l_debug =1 ) then
971 trace(l_proc ||' Within p_call_parameter_name is null');
972 trace(l_proc ||' Illegal to call package function,Only package procedures can be hooked as Custom Calls');
973 end if;
974 l_param_valid := false;
975 return;
976 else
977 if (l_debug =1 ) then
978 trace(l_proc ||' Within else - before start of while loop for comparison');
979 end if;
980
981 --
982 if (l_debug = 1) then
983 trace(l_proc||' p_parameter_position passed in => ' || p_parameter_position);
984 end if;
985 l_param_found := false;
986 l_loop := 0;
987 --trace('l_param_found = '|| l_param_found);
988 --trace('l_loop = '|| l_loop);
989 --
990 -- Keep searching through the parameter names table until the parameter
991 -- name is found or the end of the list has been reached.
992 -- If a match is found, then set the parameter_flag for the PL/SQL record
993 -- to 'Y' so that the if condition within the next iteration of the while
994 -- loop goes through only thoise records for which a match is not yet found.
995 --
996 while (not l_param_found) and (l_loop < l_number_of_parameters) loop
997 l_loop := l_loop + 1;
998
999 if (l_debug =1 ) then
1000 trace(l_proc||'Within While loop, Iteration number ' ||l_loop);
1001 trace(l_proc||'Parameter name in the global table ' ||g_parameter_table(l_loop).parameter_name);
1002 trace(l_proc||'Parameter flag in the global table ' ||g_parameter_table(l_loop).parameter_flag);
1003 trace(l_proc||'Parameter name in call signature ' ||p_call_parameter_name);
1004 end if;
1005
1006 if (l_debug =1 ) then
1007 trace(l_proc||' upper(g_parameter_table(l_loop).parameter_name) => '|| upper(g_parameter_table(l_loop).parameter_name));
1008 trace(l_proc||' upper(p_call_parameter_name) => '|| upper(p_call_parameter_name));
1009 trace(l_proc||' (g_parameter_table(l_loop).parameter_flag => ' || g_parameter_table(l_loop).parameter_flag);
1010 end if;
1011
1012 if (upper(g_parameter_table(l_loop).parameter_name) = upper(p_call_parameter_name)
1013 and (g_parameter_table(l_loop).parameter_flag = 'N')
1014 and l_loop = p_parameter_position )
1015 then
1019 g_parameter_table(l_loop).parameter_flag := 'Y';
1016 if (l_debug =1 ) then
1017 trace(l_proc||' Within check if parameter name passed in matches global table parameter name and flag is N is true ');
1018 end if;
1020
1021 if (l_debug =1 ) then
1022 trace(l_proc||'Parameter flag in the global table after setting...' ||g_parameter_table(l_loop).parameter_flag);
1023 end if;
1024 --l_number_of_parameters := l_number_of_parameters - 1;
1025 --trace('l_number_of_parameters ' || l_number_of_parameters);
1026 l_param_found := true;
1027 else
1028 if (l_debug =1 ) then
1029 trace(l_proc||' Within check if parameter name passed in matches global table parameter name and flag is N is false ');
1030 end if;
1031 l_param_found := false;
1032 if not l_param_found then
1033 trace(l_proc||' l_param_found is set to false...');
1034 else
1035 trace(l_proc||' l_param_found is set to true...');
1036 end if;
1037
1038 -- p_param_valid := false;
1039 -- x_retcode := 2;
1040 -- x_errbuf := 'Error';
1041 -- return;
1042 end if;
1043 end loop;
1044
1045 --
1046 -- If the parameter has been found carry out further parameter checks
1047 --
1048 if (l_param_found) then
1049 trace(l_proc||' Now that the parameter has been found......');
1050 --
1051 -- Check the datatype of the parameter is the same
1052 -- as the parameter in the hook package.
1053 --
1054 --
1055 -- Convert the parameter type to its appropriate number value.
1056 -- This is required since the p_call_parameter_datatype is of type
1057 -- number.
1058 if g_parameter_table(l_loop).parameter_type = c_dtype_varchar2 then
1059 l_parameter_type := 1;
1060 elsif g_parameter_table(l_loop).parameter_type = c_dtype_number then
1061 l_parameter_type := 2;
1062 elsif g_parameter_table(l_loop).parameter_type = c_dtype_long then
1063 l_parameter_type := 8;
1064 elsif g_parameter_table(l_loop).parameter_type = c_dtype_date then
1065 l_parameter_type := 12;
1066 elsif g_parameter_table(l_loop).parameter_type = c_dtype_boolean then
1067 l_parameter_type := 252;
1068 end if;
1069 --
1070 -- Convert the parameter in_out to its appropriate number value.
1071 -- This is required since the p_call_parameter_datatype is of type
1072 -- number.
1073 if g_parameter_table(l_loop).parameter_in_out = c_ptype_in then
1074 l_parameter_in_out := 0;
1075 elsif g_parameter_table(l_loop).parameter_in_out = c_ptype_out then
1076 l_parameter_in_out := 1;
1077 elsif g_parameter_table(l_loop).parameter_in_out = c_ptype_in_out then
1078 l_parameter_in_out := 256;
1079 end if;
1080
1081 if (l_debug =1 ) then
1082 trace(l_proc||'Global Parameter Type ' || l_parameter_type);
1083 trace(l_proc||'Call Parameter Type ' || p_call_parameter_datatype);
1084 trace(l_proc||'Global Parameter in/out ' || l_parameter_in_out);
1085 trace(l_proc||'Call Parameter in/out ' || p_call_parameter_in_out);
1086 end if;
1087
1088 if l_parameter_type <> p_call_parameter_datatype then
1089 -- Error: The *PARAMETER parameter to the call procedure must
1090 -- have the same datatype as the value available at the hook.
1091 -- Code to carry out this hook call has not been created.
1092 if (l_debug =1 ) then
1093 trace(l_proc||' Parameter types dont match ');
1094 end if;
1095 l_param_valid := false;
1096 --
1097 -- Check that the parameter to the call
1098 -- package procedure is of type IN
1099 --
1100 elsif l_parameter_in_out <> p_call_parameter_in_out then
1101 -- Error: At least one OUT or IN/OUT parameter has been specified
1102 -- on the call procedure. You can only use IN parameters. Code to
1103 -- carry out this hook call has not been created.
1104 if (l_debug =1 ) then
1105 trace(l_proc||' Parameter in_out dont match ');
1106 end if;
1107 l_param_valid := false;
1108 else
1109 -- Both the Call paramater data type and parameter in/out match
1110 -- and hence this is an exact match.
1111 l_param_valid := true;
1112 end if;
1113 else
1114 --
1115 -- The parameter in the call package procedure could not be
1116 -- found in the hook package procedure parameter list.
1117 --
1118 -- Error: There is a parameter to the call procedure which is not
1119 -- available at this hook. Check your call procedure parameters.
1120 -- Code to carry out this hook call has not been created.
1121 --if (l_debug =1 ) then
1122 -- trace(l_proc||' Parameter ' || p_call_parameter_name || ' has not been found......');
1123 --end if;
1124
1125 l_param_valid := false;
1126 if not l_param_valid then
1127 trace(l_proc||' l_param_valid is set to false...');
1128 else
1129 trace(l_proc||' l_param_valid is set to true...');
1130 end if;
1131
1132 end if;
1133 end if;
1134 --
1135 -- Return the parameter status
1136 --
1137 if l_param_valid then
1138 p_param_valid := true;
1139 x_retcode := 1;
1140 else
1141 p_param_valid := false;
1142 x_retcode := 2;
1143 x_errbuf := 'Error';
1144
1145 if (l_debug =1 ) then
1146 trace(l_proc||' After setting p_param_valid to false......');
1147 trace(l_proc||' x_retcode => ' || x_retcode);
1148 trace(l_proc||' x_errbuf => '|| x_errbuf);
1149 end if;
1153 if (l_debug =1 ) then
1150
1151 end if;
1152 --
1154 trace(l_proc||' Leaving:'||l_proc);
1155 end if;
1156
1157 exception
1158 when others then
1159 if (l_debug =1 ) then
1160 trace(l_proc||' Error Message in when others of validate_call_signature = ' || sqlerrm(sqlcode));
1161 end if;
1162 end chk_param_in_hook_proc_call;
1163 --
1164 --
1165 -- -------------------------------------------------------------------------------------------
1166 -- |-----------------------< validate_call_signature >----------------------------------------|
1167 -- -------------------------------------------------------------------------------------------
1168 -- {Start Of Comments}
1169 --
1170 -- Description:
1171 -- Validates if the call package-procedure signature matches the parent hook's
1172 -- signature.
1173 --
1174 -- Prerequisites:
1175 --
1176 --
1177 --
1178 -- In Parameters:
1179 -- Name Reqd Type Description
1180 -- --------------------- ---- -------- ---------------------------
1181 -- p_module_hook_id Yes number ID of the module/hook call.
1182 -- p_call_package_name Yes varchar2 Name of the package to call.
1183 -- p_call_procedure_name Yes varchar2 Name of the procedure within
1184 -- p_call_package_name to call.
1185 -- x_signature_valid No boolean True when signature matches
1186 -- false for all other cases.
1187 -- if invalid code should be
1188 -- x_return_status Yes number Return Status
1189 -- x_msg_count Yes number Message Stack Count.
1190 -- x_msg_data Yes number Message Stack Data.
1191 --
1192 -- Post Success:
1193 -- Validates and returns true .Creates source code for one package procedure call.
1194 --
1195 -- Post Failure:
1196 -- Returns false.
1197 --
1198 -- Access Status:
1199 -- Internal Development Use Only.
1200 --
1201 -- {End Of Comments}
1202 --
1203 Procedure validate_call_signature(
1204 p_module_hook_id in number
1205 , p_call_package_name in varchar2
1206 , p_call_procedure_name in varchar2
1207 , x_signature_valid out nocopy boolean
1208 , x_retcode out nocopy number
1209 , x_errbuf out nocopy varchar2
1210 ) is
1211
1212 --
1213 -- Local variables to catch the values returned from
1214 -- describe_procedure
1215 --
1216 l_overload dbms_describe.number_table;
1217 l_position dbms_describe.number_table;
1218 l_level dbms_describe.number_table;
1219 l_argument_name dbms_describe.varchar2_table;
1220 l_datatype dbms_describe.number_table;
1221 l_default_value dbms_describe.number_table;
1222 l_in_out dbms_describe.number_table;
1223 l_length dbms_describe.number_table;
1224 l_precision dbms_describe.number_table;
1225 l_scale dbms_describe.number_table;
1226 l_radix dbms_describe.number_table;
1227 l_spare dbms_describe.number_table;
1228
1229 --
1230 -- Variable to store the parameter table passed out from the call to
1231 -- populate_parameter_table
1232 l_return_status varchar2(100);
1233 l_number_of_parameters number;
1234 l_called_package varchar2(100);
1235 l_called_procedure varchar2(100);
1236 l_error_code number;
1237 l_error_message varchar2(240);
1238 l_object_name varchar2(240);
1239
1240 --
1241 -- Other local variables
1242 --
1243 l_loop binary_integer; -- Loop counter.
1244 l_loop_describe binary_integer; -- Loop counter
1245 l_param_details varchar2(80); -- Used to construct the user descriptions
1246 -- for the parameters.
1247 l_datatype_str varchar2(20); -- String equivalent of the parameter
1248 -- datatype.
1249 l_in_out_str varchar2(20); -- String equivalent of the parameter in/out.
1250 l_pre_overload number; -- Overload number for the previous
1251 -- parameter.
1252 l_param_valid boolean := true; -- Indicates if the current
1253 -- parameter is valid for this hook.
1254 l_describe_error boolean := false; -- Indicates if the
1255 -- describe_procedure raised an
1256 -- error for the call package
1257 -- procedure.
1258 l_encoded_err_text varchar2(2000); -- Set to the encoded error text
1259 -- when an error is written to the
1260 -- WMS_API_HOOK_CALLS table. Not in
1261 -- patchset 'J'.
1262 l_call_code varchar2(32767) := null;
1263 l_proc varchar2(72) := 'VALIDATE_CALL_SIGNATURE :';
1264 l_prog float;
1265
1266 begin
1267 -- Initialize API return code to success
1268 x_retcode := 1;
1269 x_errbuf := null;
1270
1271 l_prog := 53.1;
1272 if (l_debug = 1) then
1273 trace(l_proc||' Passed Progress '|| l_prog);
1274 trace(l_proc|| ' Module Hook ID : ' || p_module_hook_id);
1275 end if;
1276
1277 if (l_debug = 1) then
1278 trace(l_proc||' Passed Progress :'|| l_prog);
1279 trace(l_proc||' Before Calling populate_parameter_table...', 4);
1280 end if;
1281 -- Populate the global parameter table with the signature definition of the parent
1285 p_module_hook_id => p_module_hook_id
1282 -- in the begining. This will be used to compare the signature of the call
1283 -- procedure which is intended to be registered.
1284 populate_parameter_table(
1286 , p_parameter_table => g_parameter_table
1287 );
1288
1289 l_prog := 53.2;
1290 if (l_debug = 1) then
1291 trace(l_proc||' Passed Progress :'|| l_prog);
1292 trace(l_proc||' After Calling populate_parameter_table...', 4);
1293 end if;
1294
1295 -- Variable to keep count of number of parameters in the parent signature.
1296 -- This is used in the code later.
1297 l_number_of_parameters := g_parameter_table.count;
1298
1299 if (l_debug = 1) then
1300 trace(l_proc||' Passed Progress :'|| l_prog);
1301 trace(l_proc||' After Calling populate_parameter_table...', 4);
1302 trace(l_proc||' No of parameters in the parameter table ' || l_number_of_parameters, 4);
1303 end if;
1304
1305 --
1306 -- Call an custom RDMS procedure to obtain the list of parameters to the call
1307 -- package procedure. A separate begin ... end block has been specified so
1308 -- that errors raised by custom_describe_procedure can be trapped and
1309 -- handled locally.
1310 --
1311 l_prog := 53.3;
1312 begin
1313 if (l_debug = 1) then
1314 trace(l_proc||' Passed Progress :'|| l_prog);
1315 trace(l_proc||' Call Package Name : ' || p_call_package_name);
1316 trace(l_proc||' Call Procedure Name : ' || p_call_procedure_name);
1317 end if;
1318
1319 --
1320 -- Create the <package>.<procedure> name..
1321 --
1322 l_object_name := p_call_package_name || '.' || p_call_procedure_name;
1323
1324 if (l_debug = 1) then
1325 trace(l_proc||' Object Name : ' || l_object_name);
1326 end if;
1327
1328 if (l_debug = 1) then
1329 trace(l_proc||' Passed Progress :'|| l_prog);
1330 trace(l_proc||' Before Calling dbms_describe.describe_procedure...', 4);
1331 end if;
1332
1333 l_prog := 53.4;
1334
1335 dbms_describe.describe_procedure(
1336 object_name => l_object_name
1337 , reserved1 => null
1338 , reserved2 => null
1339 , overload => l_overload
1340 , position => l_position
1341 , level => l_level
1342 , argument_name => l_argument_name
1343 , datatype => l_datatype
1344 , default_value => l_default_value
1345 , in_out => l_in_out
1346 , length => l_length
1347 , precision => l_precision
1348 , scale => l_scale
1349 , radix => l_radix
1350 , spare => l_spare
1351 );
1352
1353 --
1354 -- Loop through the values which have been returned.
1355 --
1356 begin
1357 --
1358 -- There is separate PL/SQL block for reading from the PL/SQL
1359 -- tables. We do not know how many parameter exist. So we have to
1360 -- keep reading from the tables until PL/SQL finds a row when has
1361 -- not been initialised and raises a NO_DATA_FOUND exception.
1362 --
1363 l_loop_describe := 1;
1364 <<step_through_param_list>>
1365 loop
1366 --
1367 -- Work out the string name of the parameter datatype code
1368 --
1369 if l_datatype(l_loop_describe) = 1 then
1370 l_datatype_str := 'VARCHAR2';
1371 elsif l_datatype(l_loop_describe) = 2 then
1372 l_datatype_str := 'NUMBER';
1373 elsif l_datatype(l_loop_describe) = 12 then
1374 l_datatype_str := 'DATE';
1375 elsif l_datatype(l_loop_describe) = 252 then
1376 l_datatype_str := 'BOOLEAN';
1377 elsif l_datatype(l_loop_describe) = 8 then
1378 l_datatype_str := 'LONG';
1379 end if;
1380
1381 if l_in_out(l_loop_describe) = 0 then
1382 l_in_out_str := 'IN';
1383 elsif l_in_out(l_loop_describe) = 1 then
1384 l_in_out_str := 'OUT';
1385 elsif l_in_out(l_loop_describe) = 12 then
1386 l_in_out_str := 'IN/OUT';
1387 end if;
1388
1389 --
1390 -- Construct parameter details to output
1391 --
1392 l_param_details := ' ' || rpad(l_argument_name(l_loop_describe), 31) || l_datatype_str
1393 ||' '|| l_in_out_str ||' '|| l_length(l_loop_describe)
1394 ||' '|| l_precision(l_loop_describe)||' '|| l_scale(l_loop_describe);
1395
1396 if (l_debug = 1) then
1397 trace(l_proc||' l_param_details=' ||l_param_details);
1398 end if;
1399
1400
1401 l_loop_describe := l_loop_describe + 1;
1402 end loop step_through_param_list;
1403 end;
1404
1405 l_prog := 53.5;
1406 if (l_debug = 1) then
1407 trace(l_proc|| ' Passed Progress :'|| l_prog);
1408 trace(l_proc|| ' After Calling dbms_describe.describe_procedure...', 4);
1409 end if;
1410 exception
1411 when Package_Not_Exists then
1412 -- Error: The call_package does not exist in the database. Code to
1413 -- carry out this hook call has not been created.
1414 if (l_debug = 1) then
1415 trace(l_proc|| ' Passed Progress :'|| l_prog);
1416 trace(l_proc|| ' Call_package does not exist in the database');
1417 end if;
1418 l_describe_error := true;
1419 x_retcode := 2;
1420 x_errbuf := 'Error';
1421 return;
1422
1423 when Proc_Not_In_Package then
1424 -- Error: The call_procedure does not exist in the call_package.
1425 -- Code to carry out this hook call has not been created.
1429 end if;
1426 if (l_debug = 1) then
1427 trace(l_proc|| ' Passed Progress :'|| l_prog);
1428 trace( l_proc|| ' Called Procedure does not exist in the Called Package');
1430 l_describe_error := true;
1431 l_describe_error := true;
1432 x_retcode := 2;
1433 x_errbuf := 'Error';
1434 return;
1435
1436 when Remote_Object then
1437 -- Error: Remote objects cannot be called from API User Hooks.
1438 -- Code to carry out this hook call has not been created.
1439 if (l_debug = 1) then
1440 trace(l_proc|| ' Passed Progress :'|| l_prog);
1441 trace(l_proc|| ' Remote objects cannot be called from API User Hooks');
1442 end if;
1443 l_describe_error := true;
1444 l_describe_error := true;
1445 x_retcode := 2;
1446 x_errbuf := 'Error';
1447 return;
1448
1449 when Invalid_Package then
1450 -- Error: The call_package code in the database is invalid.
1451 -- Code to carry out this hook call has not been created.
1452 if (l_debug = 1) then
1453 trace(l_proc|| ' Passed Progress :'|| l_prog);
1454 trace(l_proc|| ' Called Package code in the database is Invalid');
1455 end if;
1456 l_describe_error := true;
1457 l_describe_error := true;
1458 x_retcode := 2;
1459 x_errbuf := 'Error';
1460 return;
1461
1462 when Invalid_Object_Name then
1463 -- Error: An error has occurred while attempting to parse the name of
1464 -- the call package and call procedure. Check the package and procedure
1465 -- names. Code to carry out this hook call has not been created.
1466 if (l_debug = 1) then
1467 trace(l_proc|| ' Passed Progress :'|| l_prog);
1468 trace(l_proc|| ' Error occurred while attempting to compile call package and call procedure');
1469 end if;
1470 l_describe_error := true;
1471 l_describe_error := true;
1472 x_retcode := 2;
1473 x_errbuf := 'Error';
1474 return;
1475
1476 when others then
1477 if (l_debug = 1) then
1478 trace(l_proc||' In others');
1479 trace(l_proc||' User error code = ' || sqlcode);
1480 trace(l_proc||' User error message = ' || sqlerrm);
1481 end if;
1482 --l_describe_error := true;
1483 if (l_debug = 1) then
1484 trace(l_proc||' l_loop_describe value after the describe loop => ' || l_loop_describe);
1485 end if;
1486
1487 --x_retcode := 2;
1488 --x_errbuf := 'Error';
1489 --return;
1490 end;
1491
1492 --
1493 -- Only carry out the parameter validation if custom_describe_procedure did not raise an error.
1494 --
1495 if not l_describe_error
1496 then
1497 l_prog := 53.6;
1498 if (l_debug = 1) then
1499 trace(l_proc||' Passed Progress :'|| l_prog);
1500 trace(l_proc||' Within not l_describe_error');
1501 end if;
1502 --
1503 -- Search through the tables returned to validate the parameter list
1504 --
1505 l_loop := 1;
1506 l_pre_overload := l_overload(1);
1507 begin
1508 if (l_debug = 1) then
1509 trace(l_proc||' Within begin within not l_describe_error');
1510 trace(l_proc||' l_number_of_parameters =>'|| l_number_of_parameters);
1511 end if;
1512 --
1513 -- There is separate PL/SQL block for reading from the PL/SQL tables.
1514 -- We do not know how many parameters exist. So we have to keep reading
1515 -- from the tables until PL/SQL finds a row when has not been
1516 -- initialised and raises a NO_DATA_FOUND exception or an invalid
1517 -- parameter is found.
1518 --
1519 l_loop := 1;
1520
1521 --while l_param_valid and (l_loop <= l_number_of_parameters) loop
1522 while l_param_valid and (l_loop < l_loop_describe) loop
1523 --l_loop := l_loop + 1;
1524
1525 if (l_debug = 1) then
1526 trace(l_proc||' Within the while loop... l_loop => '|| l_loop);
1527 end if;
1528 --
1529 -- Check that the parameter to the package procedure to be
1530 -- called exists on the hook package procedure, it is of the same
1531 -- datatype, the code to call is not a function and there are no
1532 -- overload versions.
1533 --
1534 l_prog := 53.6;
1535 if (l_debug = 1) then
1536 trace(l_proc||' Passed Progress :'|| l_prog);
1537 trace(l_proc||' Before calling procedure chk_param_in_hook_proc_call... ');
1538 trace(l_proc||' l_argument_name = ' || l_argument_name(l_loop));
1539 trace(l_proc||' l_datatype = ' || l_datatype(l_loop));
1540 trace(l_proc||' l_in_out = ' || l_in_out(l_loop));
1541 trace(l_proc||' l_overload = ' || l_overload(l_loop));
1542 trace(l_proc||' l_pre_overload = ' || l_pre_overload);
1543 end if;
1544
1545 chk_param_in_hook_proc_call(
1546 p_call_parameter_name => l_argument_name(l_loop)
1547 , p_call_parameter_datatype => l_datatype(l_loop)
1548 , p_call_parameter_in_out => l_in_out(l_loop)
1549 , p_call_parameter_overload => l_overload(l_loop)
1550 , p_previous_overload => l_pre_overload
1551 , p_parameter_position => l_loop
1552 , p_param_valid => l_param_valid
1553 , x_retcode => x_retcode
1554 , x_errbuf => x_errbuf
1555 );
1556
1557 if x_retcode <> 1 then
1558 trace(l_proc||' call to chk_param_in_hook_proc_call returned return code of error....');
1559
1563 exit;
1560 l_param_valid := false;
1561 x_retcode := 2;
1562 x_errbuf := 'Error';
1564 end if;
1565 l_prog := 53.7;
1566 if (l_debug = 1) then
1567 trace(l_proc||' Passed Progress :'|| l_prog);
1568 trace(l_proc||' After calling procedure chk_param_in_hook_proc_call for each parameter... ');
1569 end if;
1570
1571 --
1572 -- Prepare loop variables for the next iteration
1573 --
1574 l_pre_overload := l_overload(l_loop);
1575 l_loop := l_loop + 1;
1576 end loop; -- end of while loop
1577
1578 -- Check to make sure that the number of parameters in the param table and the signature match.
1579 --if l_loop <> l_number_of_parameters then
1580 -- if (l_debug = 1) then
1581 -- trace(l_proc||' Incorrect number of parameters in Signature.....', 4);
1582 -- end if;
1583 -- x_retcode := 2;
1584 -- x_errbuf := 'Error';
1585 -- return;
1586 --end if;
1587
1588
1589 l_prog := 53.8;
1590 if (l_debug = 1) then
1591 trace(l_proc||' Passed Progress :'|| l_prog);
1592 trace(l_proc||' Out of the While loop');
1593 end if;
1594 end;
1595 end if;
1596
1597 -- l_param_valid = true means that the signature matches. If the signature
1598 -- doesn't match at any point in the iteration cycle, the l_param_valid will
1599 -- come out with l_param_valid = false.
1600 l_prog := 53.9;
1601 if l_param_valid then
1602 x_signature_valid := true;
1603 if (l_debug = 1) then
1604 trace(l_proc||' Passed Progress :'|| l_prog);
1605 trace(l_proc||' Setting Signature to Valid');
1606 end if;
1607 x_retcode := 1;
1608 x_errbuf := null;
1609 return;
1610 else
1611 if (l_debug = 1) then
1612 --trace(l_proc|| 'Check paramater => '|| p_call_parameter_name);
1613 trace(l_proc||' Invalid parameter found or signature is missing all the required parameters.....');
1614 end if;
1615
1616 x_signature_valid := false;
1617 if (l_debug = 1) then
1618 trace(l_proc||' Passed Progress :'|| l_prog);
1619 trace(l_proc||' Setting Signature to Invalid');
1620 end if;
1621 x_retcode := 2;
1622 x_errbuf := 'Error';
1623 return;
1624 end if;
1625
1626 exception
1627 when others then
1628 if (l_debug = 1) then
1629 trace(l_proc||' Error Message in when others of validate_call_signature = ' || sqlerrm(sqlcode));
1630 end if;
1631 x_retcode := 2;
1632 x_errbuf := 'Error';
1633 return;
1634 end validate_call_signature;
1635 --
1636 --
1637 -- -------------------------------------------------------------------------------------------
1638 -- |----------------------------< create_delete_api_call >------------------------------------|
1639 -- -------------------------------------------------------------------------------------------
1640 -- {Start Of Comments}
1641 --
1642 -- Description:
1643 -- Populate the global PL/SQL structure(hook_parameter_table_type) with the
1644 -- parameters of the signature for the Parent Module/Business Process/ PL/SQL
1645 -- Package-Procedure combination
1646 --
1647 -- Prerequisites:
1648 -- p_module_hook_id is set with the proper value.
1649 --
1650 --
1651 -- In Parameters:
1652 -- Name Reqd Type Description
1653 -- -------------------- ---- -------- -------------------------------------
1654 -- p_hook_short_name_id Yes varchar2 Short name for parent Module/Business
1655 -- Process/ PL/SQL Package-Procedure
1656 -- combination.
1657 -- p_call_package Yes varchar2 Call package to be registered -- p_call_procedure Yes varchar2 Call procedure to be registered
1658 -- p_effective_to_date Yes varchar2 Effective To Date.
1659 -- p_mode Yes varchar2 Valid Modes are Insert, Update and
1660 -- Disable.
1661 -- Post Success:
1662 -- Returns true. Returns a PL/SQL of type hook_parameter_table_type.
1663 --
1664 -- Post Failure:
1665 -- Details of the error are added to the AOL message stack. When this
1666 -- function returns false the error has not been raised. It is up to the
1667 -- calling logic to raise or process the error.
1668 --
1669 -- Access Status:
1670 -- Internal Development Use Only.
1671 --
1672 -- {End Of Comments}
1673 --
1674 -- Inserting should check for the following :
1675 -- 1. Check to make sure that the combination does not already exist.
1676 -- 2. If the combination does not exist, then make sure that the application_id
1677 -- matches the one on the parent record.
1678 -- 3. Make sure that the effective date is not less that the system date when
1679 -- the registrtaion program is run.
1680 -- 4. Make sure that
1681 --
1682 Procedure create_delete_api_call(
1683 p_hook_short_name_id in number
1684 , p_call_package in varchar2
1685 , p_call_procedure in varchar2
1686 , p_call_description in varchar2
1687 , p_effective_to_date in date
1688 , p_mode in varchar2
1689 , x_retcode out nocopy number
1690 , x_errbuf out nocopy varchar2
1691 ) is
1692
1693 l_module_hook_id number;
1694 l_hooked_package varchar2(100);
1695 l_hooked_procedure varchar2(100);
1696 l_sysgen_custom_package varchar2(100);
1697 l_sysgen_custom_procedure varchar2(100);
1698 l_application_id number;
1702 l_hook_call_id_seq number;
1699 l_called_package varchar2(100);
1700 l_called_procedure varchar2(100);
1701 l_hook_call_id number;
1703 l_status varchar2(100);
1704 l_return_status varchar2(100);
1705 l_sign_valid boolean;
1706 l_enabled_flag varchar2(1);
1707
1708 l_msg_count number;
1709 l_msg_data varchar2(100);
1710
1711 l_package varchar2(128);
1712 l_dotpos number;
1713 compile boolean := false;
1714 l_csr_sql integer;
1715 l_rows integer;
1716
1717 l_seed_flag varchar2(1);
1718
1719 --
1720 -- Local variables to catch the values returned from
1721 -- describe_procedure
1722 --
1723 l_overload dbms_describe.number_table;
1724 l_position dbms_describe.number_table;
1725 l_level dbms_describe.number_table;
1726 l_argument_name dbms_describe.varchar2_table;
1727 l_datatype dbms_describe.number_table;
1728 l_default_value dbms_describe.number_table;
1729 l_in_out dbms_describe.number_table;
1730 l_length dbms_describe.number_table;
1731 l_precision dbms_describe.number_table;
1732 l_scale dbms_describe.number_table;
1733 l_radix dbms_describe.number_table;
1734 l_spare dbms_describe.number_table;
1735
1736 l_proc varchar2(72) := 'CREATE_DELETE_API_CALL :';
1737 l_prog float;
1738
1739
1740 -- This cursor should only return one record always.
1741 -- The short name for the parent record will be maintained as an mfg_lookup.
1742 cursor c_call_hook_status is
1743 select wahe.module_hook_id, wahe.hooked_package, wahe.hooked_procedure,
1744 wahe.sysgen_custom_package, wahe.sysgen_custom_procedure,
1745 wahc.called_package, wahc.called_procedure, wahc.hook_call_id,
1746 wahc.enabled_flag, wahc.seed_flag
1747 from wms_api_hooked_entities wahe,
1748 wms_api_hook_calls wahc
1749 where wahe.module_hook_id = wahc.module_hook_id(+)
1750 and wahe.short_name_id = p_hook_short_name_id
1751 and wahc.called_package(+) = p_call_package
1752 and wahc.called_procedure(+) = p_call_procedure;
1753
1754 begin
1755 -- Initialize API return code to success
1756 x_retcode := 1;
1757 x_errbuf := null;
1758
1759 l_prog := 10;
1760 if (l_debug = 1) then
1761 trace(l_proc||' Passed Progress '|| l_prog);
1762 trace(l_proc||' Parameter Values.........');
1763 trace(l_proc||' Short Name ID passed in '|| p_hook_short_name_id);
1764 trace(l_proc||' Call Package passed in ' || p_call_package);
1765 trace(l_proc||' Call Procedure passed in ' || p_call_procedure);
1766 end if;
1767
1768 l_prog := 20;
1769 open c_call_hook_status;
1770
1771 l_prog := 30;
1772 fetch c_call_hook_status
1773 into l_module_hook_id, l_hooked_package, l_hooked_procedure,
1774 l_sysgen_custom_package, l_sysgen_custom_procedure,
1775 l_called_package, l_called_procedure, l_hook_call_id,
1776 l_enabled_flag, l_seed_flag;
1777
1778 if (l_debug = 1) then
1779 trace(l_proc||' Passed Progress '|| l_prog);
1780 trace(l_proc||' Derived Values...');
1781 trace(l_proc||' Module Hook ID derived : '|| l_module_hook_id);
1782 trace(l_proc||' Hooked Package derived : '|| l_hooked_package);
1783 trace(l_proc||' Hooked Procedure/function derived : '|| l_hooked_procedure);
1784 trace(l_proc||' System Generated Package derived : '|| l_sysgen_custom_package);
1785 trace(l_proc||' System Generated Prodcedure derived : '|| l_sysgen_custom_procedure);
1786 trace(l_proc||' Application ID derived : '|| l_application_id);
1787 trace(l_proc||' Called Package : ' || l_called_package);
1788 trace(l_proc||' Called Procedure ' || l_called_procedure);
1789 end if;
1790
1791 if c_call_hook_status%FOUND then
1792 l_prog := 40;
1793 --
1794 -- Delete Section. Separated from Create on August 18th 2003. Makes it more simpler.
1795 --
1796 if (l_called_package = p_call_package and l_called_procedure = p_call_procedure) then
1797 l_prog := 41;
1798 if (l_debug = 1) then
1799 trace(l_proc||' Passed Progress '|| l_prog);
1800 trace(l_proc||' Within the if condition where the called package/procedure derived and passed in matches');
1801 end if;
1802 --
1803 -- Check mode to take appropriate action.
1804 --
1805 if p_mode = c_create_mode
1806 then
1807 l_prog := 42;
1808 if (l_debug = 1) then
1809 trace(l_proc||' Passed Progress :'|| l_prog);
1810 trace(l_proc||' Mode is :' || c_create_mode);
1811 trace(l_proc||' This combination is already registered for the given mode ' || p_mode, 4);
1812 end if;
1813 close c_call_hook_status;
1814 x_retcode := 2;
1815 x_errbuf := 'Error';
1816 return;
1817
1818 elsif (p_mode = c_delete_mode and l_enabled_flag = 'Y' and l_seed_flag = 'Y') then
1819 --
1820 -- Seeded Hook Calls are not allowed to be deleted...
1821 --
1822 l_prog := 43;
1823 if (l_debug = 1) then
1824 trace(l_proc||' Passed Progress :'|| l_prog);
1825 trace(l_proc||' Mode is :' || c_delete_mode);
1826 trace(l_proc||' Delete prohibited, Attempted to Delete Seeded Call.. Aborting ' || p_mode ||' Module Hook ID :' || l_module_hook_id);
1830 x_errbuf := 'Error';
1827 trace(l_proc||' Hook Call ID :' || l_hook_call_id, 4);
1828 end if;
1829 x_retcode := 2;
1831 return;
1832
1833 elsif (p_mode = c_delete_mode and l_enabled_flag = 'Y' and l_seed_flag <> 'Y')
1834 then
1835 --
1836 -- For deletion, the combination should pre-exist.
1837 --
1838 l_prog := 44;
1839 if (l_debug = 1) then
1840 trace(l_proc||' Passed Progress :'|| l_prog);
1841 trace(l_proc||' Preparing to ' || p_mode);
1842 trace(l_proc||' Module Hook ID ' || l_module_hook_id);
1843 trace(l_proc||' Hook Call ID ' || l_hook_call_id);
1844 end if;
1845
1846 --
1847 -- Delete records in the WMS_API_HOOK_CALLS table..
1848 --
1849 l_prog := 45;
1850 delete from wms_api_hook_calls
1851 where module_hook_id = l_module_hook_id
1852 and hook_call_id = l_hook_call_id
1853 and called_package = p_call_package
1854 and called_procedure = p_call_procedure;
1855
1856 commit;
1857
1858 if (l_debug = 1) then
1859 trace(l_proc||' Passed Progress :'|| l_prog);
1860 trace('Deleting Relationship Completed...');
1861 end if;
1862 --
1863 -- Call the package generation API.
1864 --
1865 l_prog := 46;
1866 if (l_debug = 1) then
1867 trace(l_proc||' Passed Progress :'|| l_prog);
1868 trace(l_proc||' Before Calling create_wms_system_objects within DELETE mode', 4);
1869 end if;
1870
1871 --
1872 -- Calling procedure create_wms_system_objects
1873 --
1874 create_wms_system_objects(
1875 x_retcode => x_retcode
1876 , x_errbuf => x_errbuf
1877 );
1878
1879 l_prog := 47;
1880 if (l_debug = 1) then
1881 trace(l_proc||' Passed Progress :'|| l_prog);
1882 trace(l_proc||' After Calling create_wms_system_objects within DELETE mode', 4);
1883 end if;
1884
1885 if l_return_status <> 'S' then
1886 if (l_debug = 1) then
1887 trace(l_proc||' Package Generation Failed after Delete', 4);
1888 x_retcode := 2;
1889 x_errbuf := 'Error';
1890 end if;
1891 else
1892 if (l_debug = 1) then
1893 trace(l_proc||' Package Generation Successfull after Delete ', 4);
1894 end if;
1895 end if;
1896
1897 return;
1898 end if;
1899 return;
1900 end if;
1901
1902 --
1903 -- Create Section. Separated from Delete on August 18th 2003. Makes it more simpler.
1904 --
1905 l_prog := 50;
1906 if ((l_called_package is null and l_called_procedure is null) and p_mode = c_create_mode)
1907 then
1908 if (l_debug = 1) then
1909 trace(l_proc||' Passed Progress :'|| l_prog);
1910 trace(l_proc||' Within the if condition where the called package/procedure derived and passed in does not match...');
1911 trace(l_proc||' This combination does not exist and hence proceed with the Registration process.......', 4);
1912 trace(l_proc||' Before Calling dbms_describe.describe_procedure...', 4);
1913 end if;
1914
1915 --
1916 -- Check if the call procedure exists in the call package in the database
1917 -- and if the package is valid. if the call package is invalid, try compiling
1918 -- it once, If successful proceed with the registration otherwise abort operation.
1919 l_prog := 51;
1920 dbms_describe.describe_procedure(
1921 object_name => p_call_package || '.' || p_call_procedure
1922 , reserved1 => null
1923 , reserved2 => null
1924 , overload => l_overload
1925 , position => l_position
1926 , level => l_level
1927 , argument_name => l_argument_name
1928 , datatype => l_datatype
1929 , default_value => l_default_value
1930 , in_out => l_in_out
1931 , length => l_length
1932 , precision => l_precision
1933 , scale => l_scale
1934 , radix => l_radix
1935 , spare => l_spare
1936 );
1937
1938 --
1939 -- Attempt to compile the invalid package.
1940 --
1941 l_prog := 52;
1942 if compile then
1943 begin
1944 l_csr_sql := dbms_sql.open_cursor;
1945 dbms_sql.parse(
1946 l_csr_sql
1947 , 'ALTER PACKAGE ' || p_call_package || ' COMPILE SPECIFICATION'
1948 , dbms_sql.native
1949 );
1950 l_rows := dbms_sql.execute( l_csr_sql );
1951 dbms_sql.close_cursor( l_csr_sql );
1952 exception
1953 when others then
1954 if dbms_sql.is_open( l_csr_sql ) then
1955 dbms_sql.close_cursor( l_csr_sql );
1956
1957 if (l_debug = 1) then
1958 trace(l_proc||' Compilation of package ' || p_call_package || ' Failed.... ', 4);
1959 end if;
1960 end if;
1961
1962 if (l_debug = 1) then
1963 trace(l_proc||' Package does not exist... ' || sqlerrm(sqlcode), 4);
1964 end if;
1965
1966 x_retcode := 2;
1967 x_errbuf := 'Error';
1968 --
1972 end;
1969 -- Compilation failed so the package is still invalid.
1970 --
1971 raise Invalid_package;
1973 --
1974 -- DBMS_DESCRIBE.DESCRIBE_PROCEDURE succeeded so exit the loop.
1975 --
1976 end if;
1977
1978 -- Validate the signature of the call procedure before inserting records in the
1979 -- wms_api_hooks_table.
1980 l_prog := 53;
1981
1982 if (l_debug = 1) then
1983 trace(l_proc||' Passed Progress :'|| l_prog);
1984 trace(l_proc||' Before Calling validate_call_signature within CREATE mode...', 4);
1985 end if;
1986
1987
1988 validate_call_signature(
1989 p_module_hook_id => l_module_hook_id
1990 , p_call_package_name => p_call_package
1991 , p_call_procedure_name => p_call_procedure
1992 , x_signature_valid => l_sign_valid
1993 , x_retcode => x_retcode
1994 , x_errbuf => x_errbuf
1995 );
1996
1997 l_prog := 54;
1998 if (x_retcode <> 2) then
1999 if (l_debug = 1) then
2000 trace(l_proc||' Passed Progress :'|| l_prog
2001 ||' After Calling validate_call_signature within CREATE mode successfully....', 4);
2002 end if;
2003 else
2004 x_retcode := 2;
2005 x_errbuf := 'Error';
2006 return;
2007 end if;
2008
2009 --
2010 -- Signature Validity check...
2011 --
2012 if l_sign_valid then
2013 -- Now that all the checks have been done we are ready to create
2014 -- a record in the wms_api_hook_calls.
2015 l_prog := 55;
2016 select wms_api_hook_calls_s.nextval
2017 into l_hook_call_id_seq
2018 from dual;
2019
2020 if (l_debug = 1) then
2021 trace(l_proc||' Passed Progress :'|| l_prog);
2022 trace(l_proc||' Hook Call ID sequence to be inserted : ' || l_hook_call_id_seq);
2023 trace(l_proc||' Inserting records into the wms_api_hook_calls table....', 4);
2024 end if;
2025
2026
2027 insert into wms_api_hook_calls(
2028 hook_call_id
2029 , module_hook_id
2030 , enabled_flag
2031 , called_package
2032 , called_procedure
2033 , effective_from_date
2034 , effective_to_date
2035 , last_updated_by
2036 , last_update_date
2037 , last_update_login
2038 , creation_date
2039 , created_by
2040 , description
2041 , seed_flag)
2042 values(
2043 l_hook_call_id_seq
2044 , l_module_hook_id
2045 , 'Y'
2046 , p_call_package
2047 , p_call_procedure
2048 , sysdate
2049 , p_effective_to_date
2050 , 1
2051 , sysdate
2052 , 1
2053 , sysdate
2054 , 1
2055 , p_call_description
2056 , 'N');
2057
2058 l_prog := 57;
2059 if (l_debug = 1) then
2060 trace(l_proc||' Passed Progress :'|| l_prog);
2061 trace(l_proc||' After Calling Insert into wms_api_hook_calls..', 4);
2062 end if;
2063
2064 if (l_debug = 1) then
2065 trace(l_proc||' Record Inserted into wms_api_hook_calls successfully.....', 4);
2066 end if;
2067
2068 l_prog := 58;
2069 if (l_debug = 1) then
2070 trace(l_proc||' Passed Progress :'|| l_prog);
2071 trace(l_proc||' Before Committing record...', 4);
2072 end if;
2073 --
2074 -- Committing Record...
2075 --
2076 commit;
2077
2078 l_prog := 59;
2079 if (l_debug = 1) then
2080 trace(l_proc||' Commit Complete...');
2081 trace(l_proc||' Passed Progress :'|| l_prog);
2082 trace(l_proc||' After Committing record...', 4);
2083 end if;
2084 else
2085 if (l_debug = 1) then
2086 trace(l_proc||' Signatures do not match. Registration Aborted....', 4);
2087 x_retcode := 2;
2088 x_errbuf := 'Error';
2089 end if;
2090 return;
2091 end if;
2092 end if;
2093
2094 --
2095 -- Taking care of Other Miscellaneous Delete Situations...
2096 --
2097 if (l_called_package is null and l_called_procedure is null) and p_mode = c_delete_mode then
2098 --
2099 -- Takes care of deleting a non-existent relationship.
2100 --
2101 l_prog := 70;
2102 if (l_debug = 1) then
2103 trace(l_proc||' Passed Progress :'|| l_prog);
2104 trace(l_proc||' This relationship is non-existent...', 4);
2105 end if;
2106 x_retcode := 2;
2107 x_errbuf := 'Error';
2108 return;
2109 elsif l_enabled_flag = 'N' and p_mode = c_delete_mode then
2110 --
2111 -- Relationship is already disabled.
2112 --
2113 l_prog := 80;
2114 if (l_debug = 1) then
2115 trace(l_proc||' Passed Progress :'|| l_prog);
2116 trace(l_proc||' This relationship has been already disabled...', 4);
2117 end if;
2118 x_retcode := 2;
2119 x_errbuf := 'Error';
2120 return;
2121 end if;
2122 end if;
2123 close c_call_hook_status;
2124
2125 --
2126 -- Call the package generation process.
2127 --
2131 trace(l_proc||' Before Calling create_wms_system_objects...within CREATE...', 4);
2128 l_prog := 60;
2129 if (l_debug = 1) then
2130 trace(l_proc||' Passed Progress :'|| l_prog);
2132 end if;
2133
2134 create_wms_system_objects(
2135 x_retcode => x_retcode
2136 , x_errbuf => x_errbuf
2137 );
2138
2139 l_prog := 61;
2140 if (l_debug = 1) then
2141 trace(l_proc||' Passed Progress :'|| l_prog);
2142 trace(l_proc||' After Calling create_wms_system_objects...within CREATE...', 4);
2143 end if;
2144
2145 if l_return_status <> 'S' then
2146 if (l_debug = 1) then
2147 trace(l_proc|| ' Create Package Failed', 4);
2148 end if;
2149 x_retcode := 2;
2150 x_errbuf := 'Error';
2151 else
2152 if (l_debug = 1) then
2153 trace(l_proc|| ' Package Created... ', 4);
2154 end if;
2155 end if;
2156 exception
2157 when Proc_Not_In_Package then
2158 if ((l_debug = 1) and (l_prog = 51))then
2159 trace(l_proc||' Invalid package/procedure combination ', 4);
2160 end if;
2161 x_retcode := 2;
2162 x_errbuf := 'Error';
2163 return;
2164 when Invalid_package then
2165 if not compile then
2166 compile := true;
2167 end if;
2168 if (l_debug = 1) then
2169 if (l_prog = 51) then
2170 trace(l_proc||' Invalid package/procedure combination ', 4);
2171 end if;
2172 end if;
2173 x_retcode := 2;
2174 x_errbuf := 'Error';
2175 return;
2176 when others then
2177 if (l_debug = 1) then
2178 if (l_prog = 45) then
2179 trace(l_proc||' Error Deleting WMS_API_HOOK_CALLS table due to error: ' || sqlerrm(sqlcode), 4);
2180 end if;
2181
2182 if (l_prog = 56) then
2183 trace(l_proc||' Insert into wms_api_hook_calls failed with ' || sqlerrm(sqlcode), 4);
2184 end if;
2185
2186 if (l_prog = 55) then
2187 trace(l_proc||' Select from Sequence wms_api_hook_calls_s failed with ' || sqlerrm(sqlcode), 4);
2188 end if;
2189
2190 if (l_prog = 51) then
2191 trace(l_proc||' Invalid package/procedure combination ', 4);
2192 end if;
2193
2194 end if;
2195 x_retcode := 2;
2196 x_errbuf := 'Error';
2197 return;
2198 end create_delete_api_call;
2199
2200 end wms_atf_reg_cust_apis;
[Home] [Help]