1 package body wms_atf_reg_cust_apis as
2 /* $Header: WMSARCAB.pls 120.1 2011/03/24 23:24:29 bvanjaku ship $ */
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 --
511 l_dynamic_spec_cursor := dbms_sql.open_cursor;
512 if (l_debug = 1) then
513 trace(l_proc ||' Entering:'|| l_proc, 1);
514 trace(l_proc ||' g_string => ' || g_string, 4);
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
521 if ad_zd.get_edition('PATCH') is not null then
522 -- an online patch is in progress, return error
523 fnd_message.set_name('FND', 'AD_ZD_DISABLED_FEATURE');
524 raise_application_error ('-20000', fnd_message.get);
525 end if;
526
527 dbms_sql.parse(l_dynamic_spec_cursor, g_string, dbms_sql.native);
528
529 l_progress := 20;
530 l_execute_spec := dbms_sql.execute(l_dynamic_spec_cursor);
531
532 if (l_debug = 1) then
533 trace(l_proc ||'l_execute_spec = ' || l_execute_spec, 4);
534 end if;
535
536 l_progress := 30;
537 dbms_sql.close_cursor(l_dynamic_spec_cursor);
538
539 if (l_debug = 1) then
540 trace(l_proc ||' Finished Generating Spec...:'|| l_proc, 1);
541 end if;
542
543 if (l_debug = 1) then
544 trace(l_proc ||' Leaving:'|| l_proc);
545 end if;
546 exception
547 --
548 -- In case of an unexpected error close the dynamic cursor
549 -- if it was successfully opened.
550 --
551 when others then
552 if (l_debug = 1) then
553 trace(l_proc ||' Error message within "When Others" exception ' || sqlerrm(sqlcode) || ' Progress : ' || l_progress || ' ' || l_proc, 1);
554 end if;
555
556 if (dbms_sql.is_open(l_dynamic_spec_cursor)) then
557 if (l_debug = 1) then
558 trace(l_proc ||' Closing Cursor ....');
559 end if;
560 dbms_sql.close_cursor(l_dynamic_spec_cursor);
561 end if;
562 end execute_source;
563 --
564 --
565 --
566 -- -------------------------------------------------------------------------------------------
567 -- |---------------------------< create_system_package >--------------------------------------|
568 -- -------------------------------------------------------------------------------------------
569 -- {Start Of Comments}
570 --
571 -- Description:
572 -- This procedure creates the spec and body string for the system generated
573 -- package. These strings may be used later to generate the spec and the body
574 -- for the system package.
575 --
576 -- Prerequisites:
577 -- None
578 --
579 -- In Parameters:
580 -- Name Reqd Type Description
581 -- ---------------- ---- -------- -------------------
582 -- p_module_hook_id Yes varchar2 Module Hook ID.
583 -- x_return_status Yes number Return Status
584 -- x_msg_count Yes number Message Stack Count.
585 -- x_msg_data Yes number Message Stack Data.
586 --
587 --
588 -- Post success:
589 -- A system package spec is created in the database.
590 --
591 -- Post Failure:
592 -- Unexpected Oracle errors and serious application errors will be raised
593 -- as a PL/SQL exception. When these errors are raised this procedure will
594 -- abort the processing.
595 --
596 -- Access Status:
597 -- Internal Development Use Only.
598 --
599 -- {End Of Comments}
600 Procedure create_wms_system_objects(
601 x_retcode out nocopy number
602 , x_errbuf out nocopy varchar2
603 ) is
604
605 l_number_of_parameters number;
606 l_outer_loop number;
607 l_inner_loop number;
608 l_middle_loop number;
609 l_return_status varchar2(240);
610 l_msg_count number;
611 l_msg_data varchar2(240);
612 l_current_package_cntr number;
613
614 -- This variable indicates the number of packages to be created.
615 l_no_of_packages number;
616 l_selected_cntr number;
617
618 -- Required for droppping packages..
619 l_csr_sql integer;
620 l_rows integer;
621 l_package_name_drop varchar2(100);
622
623 l_proc varchar2(72) := 'create_wms_system_objects :';
624 l_progress number;
625
626 cursor c_api_hooked_entities is
627 select wahe.module_hook_id, wahe.module_type_id, wahe.business_process_id,
628 wahe.short_name_id, wahe.sysgen_custom_package, wahe.sysgen_custom_procedure,
629 wahe.hooked_package, wahe.hooked_procedure, wahe.current_package_cntr
630 from wms_api_hooked_entities wahe;
631
632 cursor c_api_hook_calls(l_module_hook_id number) is
633 select hook_call_id, enabled_flag, called_package, called_procedure,
634 effective_from_date, effective_to_date
635 from wms_api_hook_calls
636 where module_hook_id = l_module_hook_id
637 and enabled_flag = 'Y'
638 and (effective_to_date >= sysdate or effective_to_date is null)
639 order by hook_call_id;
640
641 cursor c_drop_sysgen_packages(l_module_hook_id number) is
642 select sysgen_custom_package, current_package_cntr
643 from wms_api_hooked_entities
644 where module_hook_id = l_module_hook_id;
645
646 begin
647
648 l_outer_loop := 0;
649 for v_api_hooked_entities in c_api_hooked_entities
650 loop
651 l_outer_loop := l_outer_loop + 1;
652 if (l_debug = 1) then
653 trace(l_proc ||' Iteration No ' || l_outer_loop || ' in the outer loop');
654 trace(l_proc ||' ***module hook id ' ||v_api_hooked_entities.module_hook_id|| ' ***');
655 trace(l_proc ||' ***short name ' ||v_api_hooked_entities.short_name_id|| ' ***');
656 end if;
657
658 -- Check which file currently being used. The idea here is to generate 2 files
659 -- simultaneously at the time of generation so that when one package is being used
660 -- the other one can be updated.
661 if v_api_hooked_entities.current_package_cntr is null then
662 l_current_package_cntr := 1;
663 l_no_of_packages := 2;
664 elsif v_api_hooked_entities.current_package_cntr = 1 then
665 l_current_package_cntr := 2;
666 l_no_of_packages := 1;
667 elsif v_api_hooked_entities.current_package_cntr = 2 then
668 l_current_package_cntr := 1;
669 l_no_of_packages := 1;
670 end if;
671
672 if (l_debug = 1) then
673 trace(l_proc ||' *** l_current_package_cntr ' || l_current_package_cntr);
674 trace(l_proc ||' *** module hook id ' || v_api_hooked_entities.module_hook_id);
675 end if;
676
677
678 l_middle_loop := 0;
679 -- Loop to determine the number of set of packages to be created.
680 -- If the current_package_cntr is null then, 2 sets of packages have to be created.
681 -- In all other cses, only one set needs to be updated.
682 for i in 1..l_no_of_packages
683 loop
684 l_middle_loop := l_middle_loop + 1;
685 if (l_debug = 1) then
686 trace(l_proc ||' Iteration No ' || l_middle_loop || ' in the middle loop');
687 end if;
688
689 -- This is not incremented for the first iteration. The numebr of interations
690 -- is restricted by the l_no_of_packages in the for loop and so the max value the
691 -- l_current_package_cntr can have is 2.
692 if (i <> 1) then
693 l_current_package_cntr := l_current_package_cntr + 1;
694 if (l_debug = 1) then
695 trace(l_proc ||' *** Inside the l_current_package_cntr incrementer if.. end if***');
696 end if;
697 end if;
698
699 if i = 1 then
700 -- Populate the parameter table with the signature definition for every iteration of the
701 -- outer loop. This will be used to compare the signature of the call
702 -- procedure which is intended to be registered.
703 populate_parameter_table(
704 p_module_hook_id => v_api_hooked_entities.module_hook_id
705 , p_parameter_table => g_hook_parameter_table
706 );
707
708 If (g_hook_parameter_table.count = 0) then
709 -- Parameter table is empty
710 x_retcode := 2;
711 x_errbuf := 'Error';
712 return;
713 else
714 -- Variable to keep count of number of parameters in the parent signature.
715 -- This is used in the code later.
716 l_number_of_parameters := g_hook_parameter_table.count;
717 end if;
718 end if;
719
720 -- Initialise the variables at start.
721 -- Since the intent here is to create a new package spec/body for every
722 -- unique module_hook_id, the variables are reset fro each iteration.
723 g_spec_string := null;
724 g_body_string := null;
725 g_sysgen_custom_package := v_api_hooked_entities.sysgen_custom_package ||'_'||l_current_package_cntr;
726 g_sysgen_custom_procedure := v_api_hooked_entities.sysgen_custom_procedure;
727
728 if (l_debug = 1) then
729 trace(l_proc ||' ***sysgen package name ' ||v_api_hooked_entities.sysgen_custom_package|| ' ***');
730 trace(l_proc ||' ***sysgen procedure name ' ||v_api_hooked_entities.sysgen_custom_procedure|| ' ***');
731 end if;
732
733 -- Call to routine to construct the header string for the spec and the body.
734 -- Two separate global string variables are being populated one each for the
735 -- spec and the body.
736 -- The idea here it to create a new spec/body for every unique module_hook_id.
737 -- Every unique module_hook_id will have a unique signature for most cases.
738 create_package_header(
739 p_module_hook_id => v_api_hooked_entities.module_hook_id
740 , p_parameter_table => g_hook_parameter_table
741 );
742
743 l_inner_loop := 0;
744 for v_api_hook_calls in c_api_hook_calls(v_api_hooked_entities.module_hook_id)
745 loop
746 l_inner_loop := l_inner_loop + 1;
747 if (l_debug = 1) then
748 trace(l_proc ||' Iteration No ' || l_inner_loop || ' in the outer loop');
749 trace(l_proc ||' ***hook call id ' ||v_api_hook_calls.hook_call_id|| ' ***');
750 trace(l_proc ||' ***call package name ' ||v_api_hook_calls.called_package|| ' ***');
751 trace(l_proc ||' ***call procedure name ' ||v_api_hook_calls.called_procedure|| ' ***');
752 end if;
753
754 -- Call to routine to construct the if ...else clause in the package body
755 -- to call cll package/procedure registered foe a specific module_hook_id.
756 create_package_body(
757 p_called_package => v_api_hook_calls.called_package
758 , p_called_procedure => v_api_hook_calls.called_procedure
759 , p_hook_call_id => v_api_hook_calls.hook_call_id
760 , p_parameter_table => g_hook_parameter_table
761 , p_iteration => l_inner_loop
762 );
763
764 if (l_debug = 1) then
765 trace(l_proc ||' End of Iteration Number ' || l_inner_loop || ' in the Outer Loop');
766 end if;
767 end loop;
768
769 if l_inner_loop = 0 then
770 -- no records found in wms_api_hook_calls table
771 -- Hence insert a null between the begin and end in the body so that the
772 -- package generation will not fail. There can be cases where all relationships
773 -- for a parent may be disabled and this cursor will not return records.
774 add_to_string(c_new_line, 'B');
775 add_to_string('null;', 'B');
776 add_to_string(c_new_line, 'B');
777 else
778 add_to_string(c_new_line, 'B');
779 add_to_string('end if; '|| c_new_line, 'B');
780 end if;
781
782 add_to_string('end; '|| c_new_line, 'B');
783 add_to_string(c_new_line, 'B');
784 add_to_string('end '||g_sysgen_custom_package||';'|| c_new_line, 'B');
785
786 --
787 -- Drop the current package counter to be recreated. Do not drop both the packages because
788 -- the other one may be in use.
789 --
790 for v_drop_sysgen_packages in c_drop_sysgen_packages(v_api_hooked_entities.module_hook_id)
791 loop
792 begin
793 if (l_debug = 1) then
794 trace(l_proc ||' Sysgen_custom_package : ' ||v_drop_sysgen_packages.sysgen_custom_package);
795 trace(l_proc ||' Current Pkg Counter : ' ||v_drop_sysgen_packages.current_package_cntr);
796 end if;
797
798 l_package_name_drop := v_drop_sysgen_packages.sysgen_custom_package ||'_'||l_current_package_cntr;
799
800 if (l_debug = 1) then
801 trace(l_proc ||' drop package name constructed : ' || l_package_name_drop);
802 end if;
803
804
805 if ad_zd.get_edition('PATCH') is not null then
806 -- an online patch is in progress, return error
807 fnd_message.set_name('FND', 'AD_ZD_DISABLED_FEATURE');
808 raise_application_error ('-20000', fnd_message.get);
809 end if;
810
811 l_csr_sql := dbms_sql.open_cursor;
812 dbms_sql.parse
813 (l_csr_sql
814 ,'DROP PACKAGE BODY ' || l_package_name_drop
815 ,dbms_sql.native
816 );
817 l_rows := dbms_sql.execute( l_csr_sql );
818 dbms_sql.close_cursor( l_csr_sql );
819 exception
820 when others then
821 --
822 -- Drop package failed.
823 --
824 if (l_debug = 1) then
825 trace(l_proc ||' Drop package statement failed to drop package');
826 trace(l_proc ||' Drop Package Error Code = ' || sqlcode);
827 trace(l_proc ||' Drop Package Error Message = ' || sqlerrm);
828 end if;
829
830 if dbms_sql.is_open( l_csr_sql ) then
831 dbms_sql.close_cursor( l_csr_sql );
832 end if;
833 end;
834 end loop;
835
836 if (l_debug = 1) then
837 trace(l_proc ||' g_spec_string : ' || c_new_line || g_spec_string);
838 trace(l_proc ||' g_body_string : ' || c_new_line || g_body_string);
839 end if;
840
841 -- Generate the spec and body for each iteration of the module_hook_id and its associated
842 -- call package(s)/procedure(s)
843 execute_source(g_spec_string);
844 execute_source(g_body_string);
845
846 -- Update wms_api_hooked_entities to indicate which is the current package in use
847 -- to avoid being updated when in use.
848 begin
849 update wms_api_hooked_entities
850 set current_package_cntr = l_current_package_cntr
851 where module_hook_id = v_api_hooked_entities.module_hook_id;
852
853 commit;
854 exception
855 when others then
856 if (l_debug = 1) then
857 trace(l_proc ||' Update wms_api_hooked_entities failed with error = ' || sqlerrm(sqlcode));
858 end if;
859 x_retcode := 2;
860 x_errbuf := 'Error';
861 return;
862 end;
863
864 if (l_debug = 1) then
865 trace(l_proc ||' End of Iteration Number ' || l_outer_loop || ' in the Outer Loop');
866 end if;
867 end loop;
868 end loop;
869
870 if (l_debug = 1) then
871 trace(l_proc ||' Final Number of Outer Loops : ' || l_outer_loop);
872 trace(l_proc ||' Final Number of Middle Loops : ' || l_middle_loop);
873 trace(l_proc ||' Final Number of Inner Loops : ' || l_inner_loop);
874 end if;
875
876 end create_wms_system_objects;
877 --
878 --
879 -- -------------------------------------------------------------------------------------------
880 -- |---------------------< chk_param_in_hook_proc_call >--------------------------------------|
881 -- -------------------------------------------------------------------------------------------
882 -- {Start Of Comments}
883 --
884 -- Description:
885 -- This procedure is responsible for validating the eligibility of a
886 -- package.procedure to be hooked to a parent package.procedure. Checks are
887 -- to ensure that the signature of the custom(in fact called) API conforms
888 -- to the signature registered witht eh aprent record.
889 -- If the parameter should be on a procedure checks the call is not to a
890 -- function. If an error is found AOL error details are set but a PL/SQL
891 -- exception is not raised.
892 --
893 -- Prerequisites:
894 -- p_number_of_parameters, p_hook_parameter_names and
895 -- p_hook_parameter_datatypes are set with details of the hook package
896 -- procedure parameter details.
897 --
898 -- In Parameters:
899 -- Name Reqd Type Description
900 -- --------------------- ---- -------- ---------------------------------------------
901 -- p_call_parameter_name Yes varchar2 Parameter in the procedure to be called.
902 -- p_call_parameter_datatype Yes number The internal code for the parameter datatype.
903 -- p_call_parameter_in_out Yes number The internal code for the parameter IN/OUT type.
904 -- p_call_parameter_overload Yes number The overload number for the call procedure parameter.
905 -- p_previous_overload Yes number The overload number for the previous parameter on the
906 -- call procedure.
907 -- p_param_valid Yes boolean Indicates if the parameter is valid.
908 --
909 -- Post Success:
910 -- Returns true.
911 --
912 -- Post Failure:
913 -- Details of the error are added to the AOL message stack. When this
914 -- function returns false the error has not been raised. It is up to the
915 -- calling logic to raise or process the error.
916 --
917 -- Access Status:
918 -- Internal Development Use Only.
919 --
920 -- {End Of Comments}
921 --
922 Procedure chk_param_in_hook_proc_call
923 ( p_call_parameter_name in varchar2
924 , p_call_parameter_datatype in number
925 , p_call_parameter_in_out in number
926 , p_call_parameter_overload in number
927 , p_previous_overload in number
928 , p_parameter_position in number
929 , p_param_valid out nocopy boolean
930 , x_retcode out nocopy number
931 , x_errbuf out nocopy varchar2
932 ) is
933 --
934 -- Variables to store converted values for the paramater table elements.
935 --
936 l_parameter_type number;
937 l_parameter_in_out number;
938 l_number_of_parameters number:= g_parameter_table.count;
939 --
940 --
941 --
942 l_loop number; -- Loop counter
943 l_param_found boolean; -- Indicates if the parameter has been
944 -- found in the hook parameter list.
945 l_param_valid boolean; -- Indicates if parameter is valid.
946
947 l_proc varchar2(72) := 'chk_param_in_hook_proc_call :';
948 begin
949 if (l_debug =1 ) then
950 trace('Entering Procedure '|| l_proc ||':' || to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
951 trace(l_proc||'p_call_parameter_name = ' || p_call_parameter_name);
952 trace(l_proc||'p_call_parameter_datatype = ' || p_call_parameter_datatype);
953 trace(l_proc||'p_call_parameter_in_out = ' || p_call_parameter_in_out);
954 trace(l_proc||'p_call_parameter_overload = ' || p_call_parameter_overload);
955 trace(l_proc||'p_previous_overload = ' || p_previous_overload);
956 trace(l_proc||'l_number_of_parameters = '|| l_number_of_parameters);
957 end if;
958 --
959 -- Assume the parameter is valid until an error is found
960 --
961 l_param_valid := true;
962 --
963 -- Validate the call does not have any overload versions by
964 -- checking that the overload number for the current parameter is the
965 -- same as the previous parameter.
966 --
967 if p_call_parameter_overload <> p_previous_overload then
968 -- Error: A call package procedure cannot have any PL/SQL overloaded
969 -- versions. 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_overload <> p_previous_overload');
972 trace(l_proc ||' Illegal for Custom procedure to have an overloaded signature');
973 end if;
974 l_param_valid := false;
975 return;
976 --
977 -- Check the argument name has been set. If it is not set the entry
978 -- returned from describe_procedure is for a function
979 -- return value. Package functions should not be called.
980 --
981 elsif p_call_parameter_name is null then
982 -- Error: A package function cannot be called. Only package procedures
983 -- can be called. Code to carry out this hook call has not been created.
984 if (l_debug =1 ) then
985 trace(l_proc ||' Within p_call_parameter_name is null');
986 trace(l_proc ||' Illegal to call package function,Only package procedures can be hooked as Custom Calls');
987 end if;
988 l_param_valid := false;
989 return;
990 else
991 if (l_debug =1 ) then
992 trace(l_proc ||' Within else - before start of while loop for comparison');
993 end if;
994
995 --
996 if (l_debug = 1) then
997 trace(l_proc||' p_parameter_position passed in => ' || p_parameter_position);
998 end if;
999 l_param_found := false;
1000 l_loop := 0;
1001 --trace('l_param_found = '|| l_param_found);
1002 --trace('l_loop = '|| l_loop);
1003 --
1004 -- Keep searching through the parameter names table until the parameter
1005 -- name is found or the end of the list has been reached.
1006 -- If a match is found, then set the parameter_flag for the PL/SQL record
1007 -- to 'Y' so that the if condition within the next iteration of the while
1008 -- loop goes through only thoise records for which a match is not yet found.
1009 --
1010 while (not l_param_found) and (l_loop < l_number_of_parameters) loop
1011 l_loop := l_loop + 1;
1012
1013 if (l_debug =1 ) then
1014 trace(l_proc||'Within While loop, Iteration number ' ||l_loop);
1015 trace(l_proc||'Parameter name in the global table ' ||g_parameter_table(l_loop).parameter_name);
1016 trace(l_proc||'Parameter flag in the global table ' ||g_parameter_table(l_loop).parameter_flag);
1017 trace(l_proc||'Parameter name in call signature ' ||p_call_parameter_name);
1018 end if;
1019
1020 if (l_debug =1 ) then
1021 trace(l_proc||' upper(g_parameter_table(l_loop).parameter_name) => '|| upper(g_parameter_table(l_loop).parameter_name));
1022 trace(l_proc||' upper(p_call_parameter_name) => '|| upper(p_call_parameter_name));
1023 trace(l_proc||' (g_parameter_table(l_loop).parameter_flag => ' || g_parameter_table(l_loop).parameter_flag);
1024 end if;
1025
1026 if (upper(g_parameter_table(l_loop).parameter_name) = upper(p_call_parameter_name)
1027 and (g_parameter_table(l_loop).parameter_flag = 'N')
1028 and l_loop = p_parameter_position )
1029 then
1030 if (l_debug =1 ) then
1031 trace(l_proc||' Within check if parameter name passed in matches global table parameter name and flag is N is true ');
1032 end if;
1033 g_parameter_table(l_loop).parameter_flag := 'Y';
1034
1035 if (l_debug =1 ) then
1036 trace(l_proc||'Parameter flag in the global table after setting...' ||g_parameter_table(l_loop).parameter_flag);
1037 end if;
1038 --l_number_of_parameters := l_number_of_parameters - 1;
1039 --trace('l_number_of_parameters ' || l_number_of_parameters);
1040 l_param_found := true;
1041 else
1042 if (l_debug =1 ) then
1043 trace(l_proc||' Within check if parameter name passed in matches global table parameter name and flag is N is false ');
1044 end if;
1045 l_param_found := false;
1046 if not l_param_found then
1047 trace(l_proc||' l_param_found is set to false...');
1048 else
1049 trace(l_proc||' l_param_found is set to true...');
1050 end if;
1051
1052 -- p_param_valid := false;
1053 -- x_retcode := 2;
1054 -- x_errbuf := 'Error';
1055 -- return;
1056 end if;
1057 end loop;
1058
1059 --
1060 -- If the parameter has been found carry out further parameter checks
1061 --
1062 if (l_param_found) then
1063 trace(l_proc||' Now that the parameter has been found......');
1064 --
1065 -- Check the datatype of the parameter is the same
1066 -- as the parameter in the hook package.
1067 --
1068 --
1069 -- Convert the parameter type to its appropriate number value.
1070 -- This is required since the p_call_parameter_datatype is of type
1071 -- number.
1072 if g_parameter_table(l_loop).parameter_type = c_dtype_varchar2 then
1073 l_parameter_type := 1;
1074 elsif g_parameter_table(l_loop).parameter_type = c_dtype_number then
1075 l_parameter_type := 2;
1076 elsif g_parameter_table(l_loop).parameter_type = c_dtype_long then
1077 l_parameter_type := 8;
1078 elsif g_parameter_table(l_loop).parameter_type = c_dtype_date then
1079 l_parameter_type := 12;
1080 elsif g_parameter_table(l_loop).parameter_type = c_dtype_boolean then
1081 l_parameter_type := 252;
1082 end if;
1083 --
1084 -- Convert the parameter in_out to its appropriate number value.
1085 -- This is required since the p_call_parameter_datatype is of type
1086 -- number.
1087 if g_parameter_table(l_loop).parameter_in_out = c_ptype_in then
1088 l_parameter_in_out := 0;
1089 elsif g_parameter_table(l_loop).parameter_in_out = c_ptype_out then
1090 l_parameter_in_out := 1;
1091 elsif g_parameter_table(l_loop).parameter_in_out = c_ptype_in_out then
1092 l_parameter_in_out := 256;
1093 end if;
1094
1095 if (l_debug =1 ) then
1096 trace(l_proc||'Global Parameter Type ' || l_parameter_type);
1097 trace(l_proc||'Call Parameter Type ' || p_call_parameter_datatype);
1098 trace(l_proc||'Global Parameter in/out ' || l_parameter_in_out);
1099 trace(l_proc||'Call Parameter in/out ' || p_call_parameter_in_out);
1100 end if;
1101
1102 if l_parameter_type <> p_call_parameter_datatype then
1103 -- Error: The *PARAMETER parameter to the call procedure must
1104 -- have the same datatype as the value available at the hook.
1105 -- Code to carry out this hook call has not been created.
1106 if (l_debug =1 ) then
1107 trace(l_proc||' Parameter types dont match ');
1108 end if;
1109 l_param_valid := false;
1110 --
1111 -- Check that the parameter to the call
1112 -- package procedure is of type IN
1113 --
1114 elsif l_parameter_in_out <> p_call_parameter_in_out then
1115 -- Error: At least one OUT or IN/OUT parameter has been specified
1116 -- on the call procedure. You can only use IN parameters. Code to
1117 -- carry out this hook call has not been created.
1118 if (l_debug =1 ) then
1119 trace(l_proc||' Parameter in_out dont match ');
1120 end if;
1121 l_param_valid := false;
1122 else
1123 -- Both the Call paramater data type and parameter in/out match
1124 -- and hence this is an exact match.
1125 l_param_valid := true;
1126 end if;
1127 else
1128 --
1129 -- The parameter in the call package procedure could not be
1130 -- found in the hook package procedure parameter list.
1131 --
1132 -- Error: There is a parameter to the call procedure which is not
1133 -- available at this hook. Check your call procedure parameters.
1134 -- Code to carry out this hook call has not been created.
1135 --if (l_debug =1 ) then
1136 -- trace(l_proc||' Parameter ' || p_call_parameter_name || ' has not been found......');
1137 --end if;
1138
1139 l_param_valid := false;
1140 if not l_param_valid then
1141 trace(l_proc||' l_param_valid is set to false...');
1142 else
1143 trace(l_proc||' l_param_valid is set to true...');
1144 end if;
1145
1146 end if;
1147 end if;
1148 --
1149 -- Return the parameter status
1150 --
1151 if l_param_valid then
1152 p_param_valid := true;
1153 x_retcode := 1;
1154 else
1155 p_param_valid := false;
1156 x_retcode := 2;
1157 x_errbuf := 'Error';
1158
1159 if (l_debug =1 ) then
1160 trace(l_proc||' After setting p_param_valid to false......');
1161 trace(l_proc||' x_retcode => ' || x_retcode);
1162 trace(l_proc||' x_errbuf => '|| x_errbuf);
1163 end if;
1164
1165 end if;
1166 --
1167 if (l_debug =1 ) then
1168 trace(l_proc||' Leaving:'||l_proc);
1169 end if;
1170
1171 exception
1172 when others then
1173 if (l_debug =1 ) then
1174 trace(l_proc||' Error Message in when others of validate_call_signature = ' || sqlerrm(sqlcode));
1175 end if;
1176 end chk_param_in_hook_proc_call;
1177 --
1178 --
1179 -- -------------------------------------------------------------------------------------------
1180 -- |-----------------------< validate_call_signature >----------------------------------------|
1181 -- -------------------------------------------------------------------------------------------
1182 -- {Start Of Comments}
1183 --
1184 -- Description:
1185 -- Validates if the call package-procedure signature matches the parent hook's
1186 -- signature.
1187 --
1188 -- Prerequisites:
1189 --
1190 --
1191 --
1192 -- In Parameters:
1193 -- Name Reqd Type Description
1194 -- --------------------- ---- -------- ---------------------------
1195 -- p_module_hook_id Yes number ID of the module/hook call.
1196 -- p_call_package_name Yes varchar2 Name of the package to call.
1197 -- p_call_procedure_name Yes varchar2 Name of the procedure within
1198 -- p_call_package_name to call.
1199 -- x_signature_valid No boolean True when signature matches
1200 -- false for all other cases.
1201 -- if invalid code should be
1202 -- x_return_status Yes number Return Status
1203 -- x_msg_count Yes number Message Stack Count.
1204 -- x_msg_data Yes number Message Stack Data.
1205 --
1206 -- Post Success:
1207 -- Validates and returns true .Creates source code for one package procedure call.
1208 --
1209 -- Post Failure:
1210 -- Returns false.
1211 --
1212 -- Access Status:
1213 -- Internal Development Use Only.
1214 --
1215 -- {End Of Comments}
1216 --
1217 Procedure validate_call_signature(
1218 p_module_hook_id in number
1219 , p_call_package_name in varchar2
1220 , p_call_procedure_name in varchar2
1221 , x_signature_valid out nocopy boolean
1222 , x_retcode out nocopy number
1223 , x_errbuf out nocopy varchar2
1224 ) is
1225
1226 --
1227 -- Local variables to catch the values returned from
1228 -- describe_procedure
1229 --
1230 l_overload dbms_describe.number_table;
1231 l_position dbms_describe.number_table;
1232 l_level dbms_describe.number_table;
1233 l_argument_name dbms_describe.varchar2_table;
1234 l_datatype dbms_describe.number_table;
1235 l_default_value dbms_describe.number_table;
1236 l_in_out dbms_describe.number_table;
1237 l_length dbms_describe.number_table;
1238 l_precision dbms_describe.number_table;
1239 l_scale dbms_describe.number_table;
1240 l_radix dbms_describe.number_table;
1241 l_spare dbms_describe.number_table;
1242
1243 --
1244 -- Variable to store the parameter table passed out from the call to
1245 -- populate_parameter_table
1246 l_return_status varchar2(100);
1247 l_number_of_parameters number;
1248 l_called_package varchar2(100);
1249 l_called_procedure varchar2(100);
1250 l_error_code number;
1251 l_error_message varchar2(240);
1252 l_object_name varchar2(240);
1253
1254 --
1255 -- Other local variables
1256 --
1257 l_loop binary_integer; -- Loop counter.
1258 l_loop_describe binary_integer; -- Loop counter
1259 l_param_details varchar2(80); -- Used to construct the user descriptions
1260 -- for the parameters.
1261 l_datatype_str varchar2(20); -- String equivalent of the parameter
1262 -- datatype.
1263 l_in_out_str varchar2(20); -- String equivalent of the parameter in/out.
1264 l_pre_overload number; -- Overload number for the previous
1265 -- parameter.
1266 l_param_valid boolean := true; -- Indicates if the current
1267 -- parameter is valid for this hook.
1268 l_describe_error boolean := false; -- Indicates if the
1269 -- describe_procedure raised an
1270 -- error for the call package
1271 -- procedure.
1272 l_encoded_err_text varchar2(2000); -- Set to the encoded error text
1273 -- when an error is written to the
1274 -- WMS_API_HOOK_CALLS table. Not in
1275 -- patchset 'J'.
1276 l_call_code varchar2(32767) := null;
1277 l_proc varchar2(72) := 'VALIDATE_CALL_SIGNATURE :';
1278 l_prog float;
1279
1280 begin
1281 -- Initialize API return code to success
1282 x_retcode := 1;
1283 x_errbuf := null;
1284
1285 l_prog := 53.1;
1286 if (l_debug = 1) then
1287 trace(l_proc||' Passed Progress '|| l_prog);
1288 trace(l_proc|| ' Module Hook ID : ' || p_module_hook_id);
1289 end if;
1290
1291 if (l_debug = 1) then
1292 trace(l_proc||' Passed Progress :'|| l_prog);
1293 trace(l_proc||' Before Calling populate_parameter_table...', 4);
1294 end if;
1295 -- Populate the global parameter table with the signature definition of the parent
1296 -- in the begining. This will be used to compare the signature of the call
1297 -- procedure which is intended to be registered.
1298 populate_parameter_table(
1299 p_module_hook_id => p_module_hook_id
1300 , p_parameter_table => g_parameter_table
1301 );
1302
1303 l_prog := 53.2;
1304 if (l_debug = 1) then
1305 trace(l_proc||' Passed Progress :'|| l_prog);
1306 trace(l_proc||' After Calling populate_parameter_table...', 4);
1307 end if;
1308
1309 -- Variable to keep count of number of parameters in the parent signature.
1310 -- This is used in the code later.
1311 l_number_of_parameters := g_parameter_table.count;
1312
1313 if (l_debug = 1) then
1314 trace(l_proc||' Passed Progress :'|| l_prog);
1315 trace(l_proc||' After Calling populate_parameter_table...', 4);
1316 trace(l_proc||' No of parameters in the parameter table ' || l_number_of_parameters, 4);
1317 end if;
1318
1319 --
1320 -- Call an custom RDMS procedure to obtain the list of parameters to the call
1321 -- package procedure. A separate begin ... end block has been specified so
1322 -- that errors raised by custom_describe_procedure can be trapped and
1323 -- handled locally.
1324 --
1325 l_prog := 53.3;
1326 begin
1327 if (l_debug = 1) then
1328 trace(l_proc||' Passed Progress :'|| l_prog);
1329 trace(l_proc||' Call Package Name : ' || p_call_package_name);
1330 trace(l_proc||' Call Procedure Name : ' || p_call_procedure_name);
1331 end if;
1332
1333 --
1334 -- Create the <package>.<procedure> name..
1335 --
1336 l_object_name := p_call_package_name || '.' || p_call_procedure_name;
1337
1338 if (l_debug = 1) then
1339 trace(l_proc||' Object Name : ' || l_object_name);
1340 end if;
1341
1342 if (l_debug = 1) then
1343 trace(l_proc||' Passed Progress :'|| l_prog);
1344 trace(l_proc||' Before Calling dbms_describe.describe_procedure...', 4);
1345 end if;
1346
1347 l_prog := 53.4;
1348
1349 dbms_describe.describe_procedure(
1350 object_name => l_object_name
1351 , reserved1 => null
1352 , reserved2 => null
1353 , overload => l_overload
1354 , position => l_position
1355 , level => l_level
1356 , argument_name => l_argument_name
1357 , datatype => l_datatype
1358 , default_value => l_default_value
1359 , in_out => l_in_out
1360 , length => l_length
1361 , precision => l_precision
1362 , scale => l_scale
1363 , radix => l_radix
1364 , spare => l_spare
1365 );
1366
1367 --
1368 -- Loop through the values which have been returned.
1369 --
1370 begin
1371 --
1372 -- There is separate PL/SQL block for reading from the PL/SQL
1373 -- tables. We do not know how many parameter exist. So we have to
1374 -- keep reading from the tables until PL/SQL finds a row when has
1375 -- not been initialised and raises a NO_DATA_FOUND exception.
1376 --
1377 l_loop_describe := 1;
1378 <<step_through_param_list>>
1379 loop
1380 --
1381 -- Work out the string name of the parameter datatype code
1382 --
1383 if l_datatype(l_loop_describe) = 1 then
1384 l_datatype_str := 'VARCHAR2';
1385 elsif l_datatype(l_loop_describe) = 2 then
1386 l_datatype_str := 'NUMBER';
1387 elsif l_datatype(l_loop_describe) = 12 then
1388 l_datatype_str := 'DATE';
1389 elsif l_datatype(l_loop_describe) = 252 then
1390 l_datatype_str := 'BOOLEAN';
1391 elsif l_datatype(l_loop_describe) = 8 then
1392 l_datatype_str := 'LONG';
1393 end if;
1394
1395 if l_in_out(l_loop_describe) = 0 then
1396 l_in_out_str := 'IN';
1397 elsif l_in_out(l_loop_describe) = 1 then
1398 l_in_out_str := 'OUT';
1399 elsif l_in_out(l_loop_describe) = 12 then
1400 l_in_out_str := 'IN/OUT';
1401 end if;
1402
1403 --
1404 -- Construct parameter details to output
1405 --
1406 l_param_details := ' ' || rpad(l_argument_name(l_loop_describe), 31) || l_datatype_str
1407 ||' '|| l_in_out_str ||' '|| l_length(l_loop_describe)
1408 ||' '|| l_precision(l_loop_describe)||' '|| l_scale(l_loop_describe);
1409
1410 if (l_debug = 1) then
1411 trace(l_proc||' l_param_details=' ||l_param_details);
1412 end if;
1413
1414
1415 l_loop_describe := l_loop_describe + 1;
1416 end loop step_through_param_list;
1417 end;
1418
1419 l_prog := 53.5;
1420 if (l_debug = 1) then
1421 trace(l_proc|| ' Passed Progress :'|| l_prog);
1422 trace(l_proc|| ' After Calling dbms_describe.describe_procedure...', 4);
1423 end if;
1424 exception
1425 when Package_Not_Exists then
1426 -- Error: The call_package does not exist in the database. Code to
1427 -- carry out this hook call has not been created.
1428 if (l_debug = 1) then
1429 trace(l_proc|| ' Passed Progress :'|| l_prog);
1430 trace(l_proc|| ' Call_package does not exist in the database');
1431 end if;
1432 l_describe_error := true;
1433 x_retcode := 2;
1434 x_errbuf := 'Error';
1435 return;
1436
1437 when Proc_Not_In_Package then
1438 -- Error: The call_procedure does not exist in the call_package.
1439 -- Code to carry out this hook call has not been created.
1440 if (l_debug = 1) then
1441 trace(l_proc|| ' Passed Progress :'|| l_prog);
1442 trace( l_proc|| ' Called Procedure does not exist in the Called Package');
1443 end if;
1444 l_describe_error := true;
1445 l_describe_error := true;
1446 x_retcode := 2;
1447 x_errbuf := 'Error';
1448 return;
1449
1450 when Remote_Object then
1451 -- Error: Remote objects cannot be called from API User Hooks.
1452 -- Code to carry out this hook call has not been created.
1453 if (l_debug = 1) then
1454 trace(l_proc|| ' Passed Progress :'|| l_prog);
1455 trace(l_proc|| ' Remote objects cannot be called from API User Hooks');
1456 end if;
1457 l_describe_error := true;
1458 l_describe_error := true;
1459 x_retcode := 2;
1460 x_errbuf := 'Error';
1461 return;
1462
1463 when Invalid_Package then
1464 -- Error: The call_package code in the database is invalid.
1465 -- 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|| ' Called Package code in the database is Invalid');
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 Invalid_Object_Name then
1477 -- Error: An error has occurred while attempting to parse the name of
1478 -- the call package and call procedure. Check the package and procedure
1479 -- names. Code to carry out this hook call has not been created.
1480 if (l_debug = 1) then
1481 trace(l_proc|| ' Passed Progress :'|| l_prog);
1482 trace(l_proc|| ' Error occurred while attempting to compile call package and call procedure');
1483 end if;
1484 l_describe_error := true;
1485 l_describe_error := true;
1486 x_retcode := 2;
1487 x_errbuf := 'Error';
1488 return;
1489
1490 when others then
1491 if (l_debug = 1) then
1492 trace(l_proc||' In others');
1493 trace(l_proc||' User error code = ' || sqlcode);
1494 trace(l_proc||' User error message = ' || sqlerrm);
1495 end if;
1496 --l_describe_error := true;
1497 if (l_debug = 1) then
1498 trace(l_proc||' l_loop_describe value after the describe loop => ' || l_loop_describe);
1499 end if;
1500
1501 --x_retcode := 2;
1502 --x_errbuf := 'Error';
1503 --return;
1504 end;
1505
1506 --
1507 -- Only carry out the parameter validation if custom_describe_procedure did not raise an error.
1508 --
1509 if not l_describe_error
1510 then
1511 l_prog := 53.6;
1512 if (l_debug = 1) then
1513 trace(l_proc||' Passed Progress :'|| l_prog);
1514 trace(l_proc||' Within not l_describe_error');
1515 end if;
1516 --
1517 -- Search through the tables returned to validate the parameter list
1518 --
1519 l_loop := 1;
1520 l_pre_overload := l_overload(1);
1521 begin
1522 if (l_debug = 1) then
1523 trace(l_proc||' Within begin within not l_describe_error');
1524 trace(l_proc||' l_number_of_parameters =>'|| l_number_of_parameters);
1525 end if;
1526 --
1527 -- There is separate PL/SQL block for reading from the PL/SQL tables.
1528 -- We do not know how many parameters exist. So we have to keep reading
1529 -- from the tables until PL/SQL finds a row when has not been
1530 -- initialised and raises a NO_DATA_FOUND exception or an invalid
1531 -- parameter is found.
1532 --
1533 l_loop := 1;
1534
1535 --while l_param_valid and (l_loop <= l_number_of_parameters) loop
1536 while l_param_valid and (l_loop < l_loop_describe) loop
1537 --l_loop := l_loop + 1;
1538
1539 if (l_debug = 1) then
1540 trace(l_proc||' Within the while loop... l_loop => '|| l_loop);
1541 end if;
1542 --
1543 -- Check that the parameter to the package procedure to be
1544 -- called exists on the hook package procedure, it is of the same
1545 -- datatype, the code to call is not a function and there are no
1546 -- overload versions.
1547 --
1548 l_prog := 53.6;
1549 if (l_debug = 1) then
1550 trace(l_proc||' Passed Progress :'|| l_prog);
1551 trace(l_proc||' Before calling procedure chk_param_in_hook_proc_call... ');
1552 trace(l_proc||' l_argument_name = ' || l_argument_name(l_loop));
1553 trace(l_proc||' l_datatype = ' || l_datatype(l_loop));
1554 trace(l_proc||' l_in_out = ' || l_in_out(l_loop));
1555 trace(l_proc||' l_overload = ' || l_overload(l_loop));
1556 trace(l_proc||' l_pre_overload = ' || l_pre_overload);
1557 end if;
1558
1559 chk_param_in_hook_proc_call(
1560 p_call_parameter_name => l_argument_name(l_loop)
1561 , p_call_parameter_datatype => l_datatype(l_loop)
1562 , p_call_parameter_in_out => l_in_out(l_loop)
1563 , p_call_parameter_overload => l_overload(l_loop)
1564 , p_previous_overload => l_pre_overload
1565 , p_parameter_position => l_loop
1566 , p_param_valid => l_param_valid
1567 , x_retcode => x_retcode
1568 , x_errbuf => x_errbuf
1569 );
1570
1571 if x_retcode <> 1 then
1572 trace(l_proc||' call to chk_param_in_hook_proc_call returned return code of error....');
1573
1574 l_param_valid := false;
1575 x_retcode := 2;
1576 x_errbuf := 'Error';
1577 exit;
1578 end if;
1579 l_prog := 53.7;
1580 if (l_debug = 1) then
1581 trace(l_proc||' Passed Progress :'|| l_prog);
1582 trace(l_proc||' After calling procedure chk_param_in_hook_proc_call for each parameter... ');
1583 end if;
1584
1585 --
1586 -- Prepare loop variables for the next iteration
1587 --
1588 l_pre_overload := l_overload(l_loop);
1589 l_loop := l_loop + 1;
1590 end loop; -- end of while loop
1591
1592 -- Check to make sure that the number of parameters in the param table and the signature match.
1593 --if l_loop <> l_number_of_parameters then
1594 -- if (l_debug = 1) then
1595 -- trace(l_proc||' Incorrect number of parameters in Signature.....', 4);
1596 -- end if;
1597 -- x_retcode := 2;
1598 -- x_errbuf := 'Error';
1599 -- return;
1600 --end if;
1601
1602
1603 l_prog := 53.8;
1604 if (l_debug = 1) then
1605 trace(l_proc||' Passed Progress :'|| l_prog);
1606 trace(l_proc||' Out of the While loop');
1607 end if;
1608 end;
1609 end if;
1610
1611 -- l_param_valid = true means that the signature matches. If the signature
1612 -- doesn't match at any point in the iteration cycle, the l_param_valid will
1613 -- come out with l_param_valid = false.
1614 l_prog := 53.9;
1615 if l_param_valid then
1616 x_signature_valid := true;
1617 if (l_debug = 1) then
1618 trace(l_proc||' Passed Progress :'|| l_prog);
1619 trace(l_proc||' Setting Signature to Valid');
1620 end if;
1621 x_retcode := 1;
1622 x_errbuf := null;
1623 return;
1624 else
1625 if (l_debug = 1) then
1626 --trace(l_proc|| 'Check paramater => '|| p_call_parameter_name);
1627 trace(l_proc||' Invalid parameter found or signature is missing all the required parameters.....');
1628 end if;
1629
1630 x_signature_valid := false;
1631 if (l_debug = 1) then
1632 trace(l_proc||' Passed Progress :'|| l_prog);
1633 trace(l_proc||' Setting Signature to Invalid');
1634 end if;
1635 x_retcode := 2;
1636 x_errbuf := 'Error';
1637 return;
1638 end if;
1639
1640 exception
1641 when others then
1642 if (l_debug = 1) then
1643 trace(l_proc||' Error Message in when others of validate_call_signature = ' || sqlerrm(sqlcode));
1644 end if;
1645 x_retcode := 2;
1646 x_errbuf := 'Error';
1647 return;
1648 end validate_call_signature;
1649 --
1650 --
1651 -- -------------------------------------------------------------------------------------------
1652 -- |----------------------------< create_delete_api_call >------------------------------------|
1653 -- -------------------------------------------------------------------------------------------
1654 -- {Start Of Comments}
1655 --
1656 -- Description:
1657 -- Populate the global PL/SQL structure(hook_parameter_table_type) with the
1658 -- parameters of the signature for the Parent Module/Business Process/ PL/SQL
1659 -- Package-Procedure combination
1660 --
1661 -- Prerequisites:
1662 -- p_module_hook_id is set with the proper value.
1663 --
1664 --
1665 -- In Parameters:
1666 -- Name Reqd Type Description
1667 -- -------------------- ---- -------- -------------------------------------
1668 -- p_hook_short_name_id Yes varchar2 Short name for parent Module/Business
1669 -- Process/ PL/SQL Package-Procedure
1670 -- combination.
1671 -- p_call_package Yes varchar2 Call package to be registered -- p_call_procedure Yes varchar2 Call procedure to be registered
1672 -- p_effective_to_date Yes varchar2 Effective To Date.
1673 -- p_mode Yes varchar2 Valid Modes are Insert, Update and
1674 -- Disable.
1675 -- Post Success:
1676 -- Returns true. Returns a PL/SQL of type hook_parameter_table_type.
1677 --
1678 -- Post Failure:
1679 -- Details of the error are added to the AOL message stack. When this
1680 -- function returns false the error has not been raised. It is up to the
1681 -- calling logic to raise or process the error.
1682 --
1683 -- Access Status:
1684 -- Internal Development Use Only.
1685 --
1686 -- {End Of Comments}
1687 --
1688 -- Inserting should check for the following :
1689 -- 1. Check to make sure that the combination does not already exist.
1690 -- 2. If the combination does not exist, then make sure that the application_id
1691 -- matches the one on the parent record.
1692 -- 3. Make sure that the effective date is not less that the system date when
1693 -- the registrtaion program is run.
1694 -- 4. Make sure that
1695 --
1696 Procedure create_delete_api_call(
1697 p_hook_short_name_id in number
1698 , p_call_package in varchar2
1699 , p_call_procedure in varchar2
1700 , p_call_description in varchar2
1701 , p_effective_to_date in date
1702 , p_mode in varchar2
1703 , x_retcode out nocopy number
1704 , x_errbuf out nocopy varchar2
1705 ) is
1706
1707 l_module_hook_id number;
1708 l_hooked_package varchar2(100);
1709 l_hooked_procedure varchar2(100);
1710 l_sysgen_custom_package varchar2(100);
1711 l_sysgen_custom_procedure varchar2(100);
1712 l_application_id number;
1713 l_called_package varchar2(100);
1714 l_called_procedure varchar2(100);
1715 l_hook_call_id number;
1716 l_hook_call_id_seq number;
1717 l_status varchar2(100);
1718 l_return_status varchar2(100);
1719 l_sign_valid boolean;
1720 l_enabled_flag varchar2(1);
1721
1722 l_msg_count number;
1723 l_msg_data varchar2(100);
1724
1725 l_package varchar2(128);
1726 l_dotpos number;
1727 compile boolean := false;
1728 l_csr_sql integer;
1729 l_rows integer;
1730
1731 l_seed_flag varchar2(1);
1732
1733 --
1734 -- Local variables to catch the values returned from
1735 -- describe_procedure
1736 --
1737 l_overload dbms_describe.number_table;
1738 l_position dbms_describe.number_table;
1739 l_level dbms_describe.number_table;
1740 l_argument_name dbms_describe.varchar2_table;
1741 l_datatype dbms_describe.number_table;
1742 l_default_value dbms_describe.number_table;
1743 l_in_out dbms_describe.number_table;
1744 l_length dbms_describe.number_table;
1745 l_precision dbms_describe.number_table;
1746 l_scale dbms_describe.number_table;
1747 l_radix dbms_describe.number_table;
1748 l_spare dbms_describe.number_table;
1749
1750 l_proc varchar2(72) := 'CREATE_DELETE_API_CALL :';
1751 l_prog float;
1752
1753
1754 -- This cursor should only return one record always.
1755 -- The short name for the parent record will be maintained as an mfg_lookup.
1756 cursor c_call_hook_status is
1757 select wahe.module_hook_id, wahe.hooked_package, wahe.hooked_procedure,
1758 wahe.sysgen_custom_package, wahe.sysgen_custom_procedure,
1759 wahc.called_package, wahc.called_procedure, wahc.hook_call_id,
1760 wahc.enabled_flag, wahc.seed_flag
1761 from wms_api_hooked_entities wahe,
1762 wms_api_hook_calls wahc
1763 where wahe.module_hook_id = wahc.module_hook_id(+)
1764 and wahe.short_name_id = p_hook_short_name_id
1765 and wahc.called_package(+) = p_call_package
1766 and wahc.called_procedure(+) = p_call_procedure;
1767
1768 begin
1769 -- Initialize API return code to success
1770 x_retcode := 1;
1771 x_errbuf := null;
1772
1773 l_prog := 10;
1774 if (l_debug = 1) then
1775 trace(l_proc||' Passed Progress '|| l_prog);
1776 trace(l_proc||' Parameter Values.........');
1777 trace(l_proc||' Short Name ID passed in '|| p_hook_short_name_id);
1778 trace(l_proc||' Call Package passed in ' || p_call_package);
1779 trace(l_proc||' Call Procedure passed in ' || p_call_procedure);
1780 end if;
1781
1782 l_prog := 20;
1783 open c_call_hook_status;
1784
1785 l_prog := 30;
1786 fetch c_call_hook_status
1787 into l_module_hook_id, l_hooked_package, l_hooked_procedure,
1788 l_sysgen_custom_package, l_sysgen_custom_procedure,
1789 l_called_package, l_called_procedure, l_hook_call_id,
1790 l_enabled_flag, l_seed_flag;
1791
1792 if (l_debug = 1) then
1793 trace(l_proc||' Passed Progress '|| l_prog);
1794 trace(l_proc||' Derived Values...');
1795 trace(l_proc||' Module Hook ID derived : '|| l_module_hook_id);
1796 trace(l_proc||' Hooked Package derived : '|| l_hooked_package);
1797 trace(l_proc||' Hooked Procedure/function derived : '|| l_hooked_procedure);
1798 trace(l_proc||' System Generated Package derived : '|| l_sysgen_custom_package);
1799 trace(l_proc||' System Generated Prodcedure derived : '|| l_sysgen_custom_procedure);
1800 trace(l_proc||' Application ID derived : '|| l_application_id);
1801 trace(l_proc||' Called Package : ' || l_called_package);
1802 trace(l_proc||' Called Procedure ' || l_called_procedure);
1803 end if;
1804
1805 if c_call_hook_status%FOUND then
1806 l_prog := 40;
1807 --
1808 -- Delete Section. Separated from Create on August 18th 2003. Makes it more simpler.
1809 --
1810 if (l_called_package = p_call_package and l_called_procedure = p_call_procedure) then
1811 l_prog := 41;
1812 if (l_debug = 1) then
1813 trace(l_proc||' Passed Progress '|| l_prog);
1814 trace(l_proc||' Within the if condition where the called package/procedure derived and passed in matches');
1815 end if;
1816 --
1817 -- Check mode to take appropriate action.
1818 --
1819 if p_mode = c_create_mode
1820 then
1821 l_prog := 42;
1822 if (l_debug = 1) then
1823 trace(l_proc||' Passed Progress :'|| l_prog);
1824 trace(l_proc||' Mode is :' || c_create_mode);
1825 trace(l_proc||' This combination is already registered for the given mode ' || p_mode, 4);
1826 end if;
1827 close c_call_hook_status;
1828 x_retcode := 2;
1829 x_errbuf := 'Error';
1830 return;
1831
1832 elsif (p_mode = c_delete_mode and l_enabled_flag = 'Y' and l_seed_flag = 'Y') then
1833 --
1834 -- Seeded Hook Calls are not allowed to be deleted...
1835 --
1836 l_prog := 43;
1837 if (l_debug = 1) then
1838 trace(l_proc||' Passed Progress :'|| l_prog);
1839 trace(l_proc||' Mode is :' || c_delete_mode);
1840 trace(l_proc||' Delete prohibited, Attempted to Delete Seeded Call.. Aborting ' || p_mode ||' Module Hook ID :' || l_module_hook_id);
1841 trace(l_proc||' Hook Call ID :' || l_hook_call_id, 4);
1842 end if;
1843 x_retcode := 2;
1844 x_errbuf := 'Error';
1845 return;
1846
1847 elsif (p_mode = c_delete_mode and l_enabled_flag = 'Y' and l_seed_flag <> 'Y')
1848 then
1849 --
1850 -- For deletion, the combination should pre-exist.
1851 --
1852 l_prog := 44;
1853 if (l_debug = 1) then
1854 trace(l_proc||' Passed Progress :'|| l_prog);
1855 trace(l_proc||' Preparing to ' || p_mode);
1856 trace(l_proc||' Module Hook ID ' || l_module_hook_id);
1857 trace(l_proc||' Hook Call ID ' || l_hook_call_id);
1858 end if;
1859
1860 --
1861 -- Delete records in the WMS_API_HOOK_CALLS table..
1862 --
1863 l_prog := 45;
1864 delete from wms_api_hook_calls
1865 where module_hook_id = l_module_hook_id
1866 and hook_call_id = l_hook_call_id
1867 and called_package = p_call_package
1868 and called_procedure = p_call_procedure;
1869
1870 commit;
1871
1872 if (l_debug = 1) then
1873 trace(l_proc||' Passed Progress :'|| l_prog);
1874 trace('Deleting Relationship Completed...');
1875 end if;
1876 --
1877 -- Call the package generation API.
1878 --
1879 l_prog := 46;
1880 if (l_debug = 1) then
1881 trace(l_proc||' Passed Progress :'|| l_prog);
1882 trace(l_proc||' Before Calling create_wms_system_objects within DELETE mode', 4);
1883 end if;
1884
1885 --
1886 -- Calling procedure create_wms_system_objects
1887 --
1888 create_wms_system_objects(
1889 x_retcode => x_retcode
1890 , x_errbuf => x_errbuf
1891 );
1892
1893 l_prog := 47;
1894 if (l_debug = 1) then
1895 trace(l_proc||' Passed Progress :'|| l_prog);
1896 trace(l_proc||' After Calling create_wms_system_objects within DELETE mode', 4);
1897 end if;
1898
1899 if l_return_status <> 'S' then
1900 if (l_debug = 1) then
1901 trace(l_proc||' Package Generation Failed after Delete', 4);
1902 x_retcode := 2;
1903 x_errbuf := 'Error';
1904 end if;
1905 else
1906 if (l_debug = 1) then
1907 trace(l_proc||' Package Generation Successfull after Delete ', 4);
1908 end if;
1909 end if;
1910
1911 return;
1912 end if;
1913 return;
1914 end if;
1915
1916 --
1917 -- Create Section. Separated from Delete on August 18th 2003. Makes it more simpler.
1918 --
1919 l_prog := 50;
1920 if ((l_called_package is null and l_called_procedure is null) and p_mode = c_create_mode)
1921 then
1922 if (l_debug = 1) then
1923 trace(l_proc||' Passed Progress :'|| l_prog);
1924 trace(l_proc||' Within the if condition where the called package/procedure derived and passed in does not match...');
1925 trace(l_proc||' This combination does not exist and hence proceed with the Registration process.......', 4);
1926 trace(l_proc||' Before Calling dbms_describe.describe_procedure...', 4);
1927 end if;
1928
1929 --
1930 -- Check if the call procedure exists in the call package in the database
1931 -- and if the package is valid. if the call package is invalid, try compiling
1932 -- it once, If successful proceed with the registration otherwise abort operation.
1933 l_prog := 51;
1934 dbms_describe.describe_procedure(
1935 object_name => p_call_package || '.' || p_call_procedure
1936 , reserved1 => null
1937 , reserved2 => null
1938 , overload => l_overload
1939 , position => l_position
1940 , level => l_level
1941 , argument_name => l_argument_name
1942 , datatype => l_datatype
1943 , default_value => l_default_value
1944 , in_out => l_in_out
1945 , length => l_length
1946 , precision => l_precision
1947 , scale => l_scale
1948 , radix => l_radix
1949 , spare => l_spare
1950 );
1951
1952 --
1953 -- Attempt to compile the invalid package.
1954 --
1955
1956 if ad_zd.get_edition('PATCH') is not null then
1957 -- an online patch is in progress, return error
1958 fnd_message.set_name('FND', 'AD_ZD_DISABLED_FEATURE');
1959 raise_application_error ('-20000', fnd_message.get);
1960 end if;
1961
1962 l_prog := 52;
1963 if compile then
1964 begin
1965 l_csr_sql := dbms_sql.open_cursor;
1966 dbms_sql.parse(
1967 l_csr_sql
1968 , 'ALTER PACKAGE ' || p_call_package || ' COMPILE SPECIFICATION'
1969 , dbms_sql.native
1970 );
1971 l_rows := dbms_sql.execute( l_csr_sql );
1972 dbms_sql.close_cursor( l_csr_sql );
1973 exception
1974 when others then
1975 if dbms_sql.is_open( l_csr_sql ) then
1976 dbms_sql.close_cursor( l_csr_sql );
1977
1978 if (l_debug = 1) then
1979 trace(l_proc||' Compilation of package ' || p_call_package || ' Failed.... ', 4);
1980 end if;
1981 end if;
1982
1983 if (l_debug = 1) then
1984 trace(l_proc||' Package does not exist... ' || sqlerrm(sqlcode), 4);
1985 end if;
1986
1987 x_retcode := 2;
1988 x_errbuf := 'Error';
1989 --
1990 -- Compilation failed so the package is still invalid.
1991 --
1992 raise Invalid_package;
1993 end;
1994 --
1995 -- DBMS_DESCRIBE.DESCRIBE_PROCEDURE succeeded so exit the loop.
1996 --
1997 end if;
1998
1999 -- Validate the signature of the call procedure before inserting records in the
2000 -- wms_api_hooks_table.
2001 l_prog := 53;
2002
2003 if (l_debug = 1) then
2004 trace(l_proc||' Passed Progress :'|| l_prog);
2005 trace(l_proc||' Before Calling validate_call_signature within CREATE mode...', 4);
2006 end if;
2007
2008
2009 validate_call_signature(
2010 p_module_hook_id => l_module_hook_id
2011 , p_call_package_name => p_call_package
2012 , p_call_procedure_name => p_call_procedure
2013 , x_signature_valid => l_sign_valid
2014 , x_retcode => x_retcode
2015 , x_errbuf => x_errbuf
2016 );
2017
2018 l_prog := 54;
2019 if (x_retcode <> 2) then
2020 if (l_debug = 1) then
2021 trace(l_proc||' Passed Progress :'|| l_prog
2022 ||' After Calling validate_call_signature within CREATE mode successfully....', 4);
2023 end if;
2024 else
2025 x_retcode := 2;
2026 x_errbuf := 'Error';
2027 return;
2028 end if;
2029
2030 --
2031 -- Signature Validity check...
2032 --
2033 if l_sign_valid then
2034 -- Now that all the checks have been done we are ready to create
2035 -- a record in the wms_api_hook_calls.
2036 l_prog := 55;
2037 select wms_api_hook_calls_s.nextval
2038 into l_hook_call_id_seq
2039 from dual;
2040
2041 if (l_debug = 1) then
2042 trace(l_proc||' Passed Progress :'|| l_prog);
2043 trace(l_proc||' Hook Call ID sequence to be inserted : ' || l_hook_call_id_seq);
2044 trace(l_proc||' Inserting records into the wms_api_hook_calls table....', 4);
2045 end if;
2046
2047
2048 insert into wms_api_hook_calls(
2049 hook_call_id
2050 , module_hook_id
2051 , enabled_flag
2052 , called_package
2053 , called_procedure
2054 , effective_from_date
2055 , effective_to_date
2056 , last_updated_by
2057 , last_update_date
2058 , last_update_login
2059 , creation_date
2060 , created_by
2061 , description
2062 , seed_flag)
2063 values(
2064 l_hook_call_id_seq
2065 , l_module_hook_id
2066 , 'Y'
2067 , p_call_package
2068 , p_call_procedure
2069 , sysdate
2070 , p_effective_to_date
2071 , 1
2072 , sysdate
2073 , 1
2074 , sysdate
2075 , 1
2076 , p_call_description
2077 , 'N');
2078
2079 l_prog := 57;
2080 if (l_debug = 1) then
2081 trace(l_proc||' Passed Progress :'|| l_prog);
2082 trace(l_proc||' After Calling Insert into wms_api_hook_calls..', 4);
2083 end if;
2084
2085 if (l_debug = 1) then
2086 trace(l_proc||' Record Inserted into wms_api_hook_calls successfully.....', 4);
2087 end if;
2088
2089 l_prog := 58;
2090 if (l_debug = 1) then
2091 trace(l_proc||' Passed Progress :'|| l_prog);
2092 trace(l_proc||' Before Committing record...', 4);
2093 end if;
2094 --
2095 -- Committing Record...
2096 --
2097 commit;
2098
2099 l_prog := 59;
2100 if (l_debug = 1) then
2101 trace(l_proc||' Commit Complete...');
2102 trace(l_proc||' Passed Progress :'|| l_prog);
2103 trace(l_proc||' After Committing record...', 4);
2104 end if;
2105 else
2106 if (l_debug = 1) then
2107 trace(l_proc||' Signatures do not match. Registration Aborted....', 4);
2108 x_retcode := 2;
2109 x_errbuf := 'Error';
2110 end if;
2111 return;
2112 end if;
2113 end if;
2114
2115 --
2116 -- Taking care of Other Miscellaneous Delete Situations...
2117 --
2118 if (l_called_package is null and l_called_procedure is null) and p_mode = c_delete_mode then
2119 --
2120 -- Takes care of deleting a non-existent relationship.
2121 --
2122 l_prog := 70;
2123 if (l_debug = 1) then
2124 trace(l_proc||' Passed Progress :'|| l_prog);
2125 trace(l_proc||' This relationship is non-existent...', 4);
2126 end if;
2127 x_retcode := 2;
2128 x_errbuf := 'Error';
2129 return;
2130 elsif l_enabled_flag = 'N' and p_mode = c_delete_mode then
2131 --
2132 -- Relationship is already disabled.
2133 --
2134 l_prog := 80;
2135 if (l_debug = 1) then
2136 trace(l_proc||' Passed Progress :'|| l_prog);
2137 trace(l_proc||' This relationship has been already disabled...', 4);
2138 end if;
2139 x_retcode := 2;
2140 x_errbuf := 'Error';
2141 return;
2142 end if;
2143 end if;
2144 close c_call_hook_status;
2145
2146 --
2147 -- Call the package generation process.
2148 --
2149 l_prog := 60;
2150 if (l_debug = 1) then
2151 trace(l_proc||' Passed Progress :'|| l_prog);
2152 trace(l_proc||' Before Calling create_wms_system_objects...within CREATE...', 4);
2153 end if;
2154
2155 create_wms_system_objects(
2156 x_retcode => x_retcode
2157 , x_errbuf => x_errbuf
2158 );
2159
2160 l_prog := 61;
2161 if (l_debug = 1) then
2162 trace(l_proc||' Passed Progress :'|| l_prog);
2163 trace(l_proc||' After Calling create_wms_system_objects...within CREATE...', 4);
2164 end if;
2165
2166 if l_return_status <> 'S' then
2167 if (l_debug = 1) then
2168 trace(l_proc|| ' Create Package Failed', 4);
2169 end if;
2170 x_retcode := 2;
2171 x_errbuf := 'Error';
2172 else
2173 if (l_debug = 1) then
2174 trace(l_proc|| ' Package Created... ', 4);
2175 end if;
2176 end if;
2177 exception
2178 when Proc_Not_In_Package then
2179 if ((l_debug = 1) and (l_prog = 51))then
2180 trace(l_proc||' Invalid package/procedure combination ', 4);
2181 end if;
2182 x_retcode := 2;
2183 x_errbuf := 'Error';
2184 return;
2185 when Invalid_package then
2186 if not compile then
2187 compile := true;
2188 end if;
2189 if (l_debug = 1) then
2190 if (l_prog = 51) then
2191 trace(l_proc||' Invalid package/procedure combination ', 4);
2192 end if;
2193 end if;
2194 x_retcode := 2;
2195 x_errbuf := 'Error';
2196 return;
2197 when others then
2198 if (l_debug = 1) then
2199 if (l_prog = 45) then
2200 trace(l_proc||' Error Deleting WMS_API_HOOK_CALLS table due to error: ' || sqlerrm(sqlcode), 4);
2201 end if;
2202
2203 if (l_prog = 56) then
2204 trace(l_proc||' Insert into wms_api_hook_calls failed with ' || sqlerrm(sqlcode), 4);
2205 end if;
2206
2207 if (l_prog = 55) then
2208 trace(l_proc||' Select from Sequence wms_api_hook_calls_s failed with ' || sqlerrm(sqlcode), 4);
2209 end if;
2210
2211 if (l_prog = 51) then
2212 trace(l_proc||' Invalid package/procedure combination ', 4);
2213 end if;
2214
2215 end if;
2216 x_retcode := 2;
2217 x_errbuf := 'Error';
2218 return;
2219 end create_delete_api_call;
2220
2221 end wms_atf_reg_cust_apis;
[Home] [Help]