1 PACKAGE m4u_xml_extn_utils AS
2 /* $Header: M4UXUTLS.pls 120.0 2006/05/25 12:52:45 bsaratna noship $ */
3
4 -- Constant values used in XML generation program
5 -- mapping type - CONSTANT/VARIABLE/VIEW
6 c_maptyp_const CONSTANT VARCHAR2(30) := 'CONSTANT';
7 c_maptyp_var CONSTANT VARCHAR2(30) := 'VARIABLE';
8 c_maptyp_view CONSTANT VARCHAR2(30) := 'VIEW';
9
10 -- element type - ATTRIBUTE/ELEMENT/CONTAINER
11 c_nodetyp_attr CONSTANT VARCHAR2(30) := 'ATTRIBUTE';
12 c_nodetyp_cont CONSTANT VARCHAR2(30) := 'CONTAINER';
13 c_nodetyp_elmt CONSTANT VARCHAR2(30) := 'ELEMENT';
14
15 -- valdn type - DTD/XSD/NONE
16 c_valdtn_dtd CONSTANT VARCHAR2(30) := 'DTD';
17 c_valdtn_xsd CONSTANT VARCHAR2(30) := 'XSD';
18 c_valdtn_none CONSTANT VARCHAR2(30) := 'NONE';
19
20 -- GLOBAL variables passed to map as event parameter list
21 TYPE glb_var_typ IS TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(60);
22 g_glb_var_tab glb_var_typ;
23
24 -- Header data -- m4u_xml_extensions
25 -- Header identifier, validation data
26 TYPE hdr_rec_typ IS RECORD
27 (
28 extn_name m4u_xml_extensions.extn_name%TYPE,
29 tp_id m4u_xml_extensions.trading_partner_id%TYPE,
30 valdtn_typ m4u_xml_extensions.validation_type%TYPE,
31 dtd_schma_loc m4u_xml_extensions.dtd_or_schema_location%TYPE,
32 dtd_base_dir m4u_xml_extensions.base_dir%TYPE,
33 xml_root_node m4u_xml_extensions.root_node%TYPE
34
35 );
36 g_hdr_rec hdr_rec_typ;
37
38 -- Corresponds to rows from m4u_element_mapping table
39 -- node identified, type, hierachy, mapping information
40 TYPE elmnt_rec_typ IS RECORD
41 (
42 id m4u_element_mappings.node_id%TYPE,
43 seq m4u_element_mappings.node_sequence%TYPE,
44 name m4u_element_mappings.node_name%TYPE,
45 type m4u_element_mappings.node_type%TYPE,
46 lvl_id m4u_element_mappings.level_id%TYPE,
47 parent_id m4u_element_mappings.parent_node_id%TYPE,
48 map_typ m4u_element_mappings.mapping_type%TYPE,
49 view_nam m4u_element_mappings.view_name%TYPE,
50 col m4u_element_mappings.column_name%TYPE,
51 view_lvl m4u_element_mappings.view_level_id%TYPE,
52 var m4u_element_mappings.variable_name%TYPE,
53 const m4u_element_mappings.constant_val%TYPE
54 );
55 -- Element for below table need to be processed in sequnce for generating XML
56 TYPE g_elmnt_map_typ IS TABLE OF elmnt_rec_typ INDEX BY BINARY_INTEGER;
57 g_elmnt_map g_elmnt_map_typ;
58 g_elmnt_count NUMBER; -- no of elements in above varray
59
60 -- Recrod for each bind variable
61 -- bind variable
62 -- View/Global variable from which bind value to be fetched
63 TYPE bind_rec_typ IS RECORD
64 (
65 nam m4u_view_binding.bind_variable%TYPE,
66 typ m4u_view_binding.bind_type%TYPE,
67 src_var m4u_view_binding.source_var%TYPE,
68 src_view m4u_view_binding.source_view%TYPE,
69 src_col m4u_view_binding.source_column%TYPE,
70 src_lvl_id m4u_view_binding.source_level_id%TYPE
71 );
72 -- Table of bind variable associate with each view
73 TYPE bind_tab_typ IS TABLE OF bind_rec_typ INDEX BY BINARY_INTEGER;
74 -- Table of column names associated with each view
75 TYPE col_tab_typ IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
76
77 -- Each view to be queried from the database.
78 -- View name, column listing, where clause, bind variable listing
79 -- number of rows fetched for the view
80 TYPE view_rec_typ IS RECORD
81 (
82 view_nam m4u_element_mappings.view_name%TYPE,
83 whr_claus VARCHAR2(4000),
84 bind_tab bind_tab_typ,
85 bind_count NUMBER,
86 exec_sql VARCHAR2(4000),
87 col_tab col_tab_typ,
88 col_count NUMBER,
89 rowcount NUMBER
90 );
91 TYPE view_tab_typ IS TABLE OF view_rec_typ INDEX BY BINARY_INTEGER;
92
93 -- Table of number indexed by integer
94 -- Used as base data-type for all stacks
95 TYPE g_list_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
96 -- Values which go into the XML.
97 -- Index is rownum
98 TYPE g_vals_typ IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
99
100 -- to fetch 3rd row value of viewname.column name do
101 -- lvl_rec.vals(viewname.column_nam)(3)
102 TYPE col_val_tab_typ IS TABLE of g_vals_typ INDEX BY VARCHAR2(200);
103
104 -- Record for each level
105 -- id,view_tab,view_count,is_mapped - are initialized a loading
106 -- rpt_count,end_tag_stk,end_tag_stk_ptr,cntr - hold stack information
107 TYPE lvl_rec_typ IS RECORD
108 (
109 id m4u_level_views.level_id%TYPE,
110 view_tab view_tab_typ,
111 view_count NUMBER,
112 rpt_count NUMBER,
113 is_mapped BOOLEAN,
114 end_tag_stk_ptr NUMBER,
115 end_tag_stk g_list_typ,
116 vals col_val_tab_typ,
117 cntr NUMBER
118 );
119 TYPE lvl_tab_typ IS TABLE OF lvl_rec_typ INDEX BY BINARY_INTEGER;
120
121 -- table indexing level records
122 g_lvl_rec_tab lvl_tab_typ;
123
124 -- stack of level. current level in on top and parent levels are stacked below
125 g_lvl_stk g_list_typ;
126 g_lvl_stk_ptr NUMBER := 0;
127
128
129 -- Procedure to init map
130 -- loads all map information into memory
131 PROCEDURE init_map(
132 a_extn_name IN VARCHAR2,
133 a_tp_id IN VARCHAR2,
134 a_dflt_tp IN BOOLEAN,
135 a_param_list IN wf_parameter_list_t,
136 a_log_lvl IN NUMBER,
137 x_ret_sts OUT NOCOPY VARCHAR2,
138 x_ret_msg OUT NOCOPY VARCHAR2);
139
140 -- called by init map, loads header info to memory
141 PROCEDURE init_hdr(
142 a_extn_name IN VARCHAR2,
143 a_tp_id IN VARCHAR2,
144 a_dflt_tp IN BOOLEAN,
145 x_ret_sts OUT NOCOPY VARCHAR2,
146 x_ret_msg OUT NOCOPY VARCHAR2);
147
148 -- called by init map, loads elmnt_mapping info to memory
149 PROCEDURE load_elmnt_mapping(
150 x_ret_sts OUT NOCOPY VARCHAR2,
151 x_ret_msg OUT NOCOPY VARCHAR2);
152
153 -- called by init map, loads level info to memory
154 PROCEDURE load_lvls(
155 x_ret_sts OUT NOCOPY VARCHAR2,
156 x_ret_msg OUT NOCOPY VARCHAR2);
157
158 -- load list of columns to be fetched for each view
159 -- create exec_sql dynamically
160 PROCEDURE load_view_cols(
161 a_lvl_id IN NUMBER,
162 x_view_rec IN OUT NOCOPY view_rec_typ,
163 x_ret_sts OUT NOCOPY VARCHAR2,
164 x_ret_msg OUT NOCOPY VARCHAR2);
165
166 -- load list of bind variable to be supplied for each where clause
167 -- load mapping info for each bind variable
168 PROCEDURE load_bindings(
169 a_lvl_id IN NUMBER,
170 x_view_rec IN OUT NOCOPY view_rec_typ,
171 x_ret_sts OUT NOCOPY VARCHAR2,
172 x_ret_msg OUT NOCOPY VARCHAR2);
173
174
175 -- Code to initialize a level
176 -- Executes views sequels and stores data in memory
177 -- of level record
178 -- initializes counter, repeat count of level
179 PROCEDURE init_level(a_lvl_id IN VARCHAR2,
180 x_lvl_rec OUT NOCOPY lvl_rec_typ,
181 x_ret_sts OUT NOCOPY VARCHAR2,
182 x_ret_msg OUT NOCOPY VARCHAR2);
183
184 -- bundle unexpected error handling code
185 PROCEDURE handle_exception(
186 a_sql_code IN NUMBER,
187 a_sql_errm IN VARCHAR2,
188 a_actn IN VARCHAR2,
189 a_proc IN VARCHAR2,
190 x_ret_sts OUT NOCOPY VARCHAR2,
191 x_ret_msg OUT NOCOPY VARCHAR2);
192
193 -- lookup view, column value from run-time level stack
194 FUNCTION lookup_view_value( a_view IN VARCHAR2 ,
195 a_col IN VARCHAR2 ,
196 a_lvl IN NUMBER ,
197 x_ret_sts OUT NOCOPY VARCHAR2,
198 x_ret_msg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2;
199
200 -- pushes input level into runtime level stack
201 PROCEDURE push_lvl_stack(l_lvl_id IN NUMBER,
202 x_ret_sts OUT NOCOPY VARCHAR2,
203 x_ret_msg OUT NOCOPY VARCHAR2);
204
205 -- pops input level from runtime level stack
206 FUNCTION pop_lvl_stack( x_ret_sts OUT NOCOPY VARCHAR2,
207 x_ret_msg OUT NOCOPY VARCHAR2)
208 RETURN NUMBER;
209
210 -- called to validate XML fragment
211 -- Validation parameters are supplied by init_hdr
212 PROCEDURE validate(
213 a_xml IN VARCHAR2,
214 x_valdtn_sts OUT NOCOPY VARCHAR2,
215 x_valdtn_msg OUT NOCOPY VARCHAR2,
216 x_api_ret_sts OUT NOCOPY VARCHAR2,
217 x_api_ret_msg OUT NOCOPY VARCHAR2);
218
219
220 -- Helper procedure to log XML document into file
221 -- for debug/logging purpose
222 PROCEDURE log_xml(a_xml IN VARCHAR2,
223 x_ret_sts OUT NOCOPY VARCHAR2,
224 x_ret_msg OUT NOCOPY VARCHAR2);
225
226 -- cleans up run-time info of level
230 PROCEDURE un_init_level(a_lvl_id IN NUMBER,
227 -- this is important
228 -- since same level can be brought back onto stack multiple times
229 -- so make sure data from previous run of level is not carried
231 x_ret_sts OUT NOCOPY VARCHAR2,
232 x_ret_msg OUT NOCOPY VARCHAR2);
233
234
235 -- cleans up any memory held by map
236 PROCEDURE un_init_map(x_ret_sts OUT NOCOPY VARCHAR2,
237 x_ret_msg OUT NOCOPY VARCHAR2);
238
239 -- escape predefined entities from charter strings
240 FUNCTION escape_entities(a_val IN varchar2)
241 RETURN VARCHAR2;
242 END m4u_xml_extn_utils;