1 PACKAGE BODY xnp_msg_schema AS
2 /* $Header: XNPMBLVB.pls 120.2 2006/02/13 07:51:31 dputhiye ship $ */
3
4 PROCEDURE check_source(p_element_id IN NUMBER,
5 p_element_name IN VARCHAR2,
6 p_source_type IN VARCHAR2,
7 p_data_source IN VARCHAR2,
8 p_data_ref IN VARCHAR2,
9 x_error_code OUT NOCOPY NUMBER,
10 x_error_message OUT NOCOPY VARCHAR2);
11
12 FUNCTION check_exists(p_msg_code IN VARCHAR2 ) RETURN NUMBER;
13
14 FUNCTION check_parameter_pool
15 (p_element_name IN VARCHAR2,
16 p_reference IN VARCHAR2) RETURN NUMBER;
17
18 FUNCTION check_if_parent(p_element_id NUMBER) RETURN NUMBER;
19
20 PROCEDURE validate (
21 p_msg_code IN VARCHAR2,
22 x_error_code OUT NOCOPY NUMBER,
23 x_error_message OUT NOCOPY VARCHAR2
24 )
25
26 IS
27
28 CURSOR get_msg_data IS
29 SELECT met.name,
30 met.msg_element_id,
31 met.parameter_flag,
32 mse.data_source,
33 mse.data_source_type,
34 mse.data_source_reference
35 FROM xnp_msg_elements met, xnp_msg_structures mse
36 WHERE met.msg_code = mse.msg_code
37 AND met.msg_code = p_msg_code
38 -- skilaru 03/27/2001
39 -- AND met.msg_element_id IN( mse.child_element_id);
40 AND met.msg_element_id = mse.child_element_id;
41
42
43
44 l_exists NUMBER;
45 l_parent NUMBER;
46
47 BEGIN
48
49 x_error_code := 0;
50 x_error_message := NULL;
51
52 --check to see if a database object exists with the
53 --same name as the message code
54
55 l_exists := check_exists(p_msg_code) ;
56
57 IF (l_exists <> 0) THEN
58 fnd_message.set_name('XNP', 'DUPLICATE_OBJECT');
59 fnd_message.set_token('NAME',p_msg_code);
60 x_error_message := fnd_message.get ;
61 x_error_code := xnp_errors.g_duplicate_object;
62 RETURN ;
63 END IF ;
64
65 FOR rec IN get_msg_data LOOP
66
67 -- check if the element has a parameter
68 -- or alternate data source
69
70 IF ((rec.parameter_flag = 'N') AND
71 (rec.data_source_type IS NULL) AND
72 (rec.data_source IS NULL) AND
73 (rec.data_source_reference IS NULL))
74 THEN
75
76
77 l_parent := 0;
78
79 l_parent := check_if_parent(rec.msg_element_id) ;
80
81 IF (l_parent = 0) THEN
82
83 -- No data source has been defined for the leaf element
84 -- But the Message code element is an exception
85
86 IF (rec.name <> p_msg_code) THEN
87 fnd_message.set_name ('XNP','NO_DATA_SOURCE') ;
88 fnd_message.set_token ('NAME',rec.name) ;
89 x_error_message := fnd_message.get;
90 x_error_code := xnp_errors.g_no_data_source;
91 RETURN;
92 END IF;
93
94 END IF;
95
96 ELSE
97 IF (rec.parameter_flag = 'N') THEN
98 check_source(p_element_id=>rec.msg_element_id,
99 p_element_name=>rec.name,
100 p_source_type => rec.data_source_type,
101 p_data_source => rec.data_source,
102 p_data_ref => rec.data_source_reference,
103 x_error_code => x_error_code,
104 x_error_message => x_error_message);
105 IF (x_error_code <> 0) THEN
106 RETURN;
107 END IF;
108 END IF;
109
110 END IF;
111
112 END LOOP;
113
114 EXCEPTION
115 WHEN OTHERS THEN
116 x_error_code := SQLCODE;
117 x_error_message := SQLERRM;
118
119
120 END validate;
121
122 /***********************************************************************/
123
124 PROCEDURE check_source(p_element_id IN NUMBER,
125 p_element_name IN VARCHAR2,
126 p_source_type IN VARCHAR2,
127 p_data_source IN VARCHAR2,
128 p_data_ref IN VARCHAR2,
129 x_error_code OUT NOCOPY NUMBER,
130 x_error_message OUT NOCOPY VARCHAR2)
131
132 IS
133
134 l_exists NUMBER;
135 l_parent NUMBER;
136 l_data_source VARCHAR2(4000) ;
137
138 BEGIN
139
140 x_error_code := 0;
141 x_error_message := NULL;
142
143 IF (p_source_type = 'SQL') THEN
144 IF (p_data_source IS NULL) THEN
145 fnd_message.set_name('XNP', 'NULL_SQL_SOURCE');
146 fnd_message.set_token('NAME', p_element_name);
147 x_error_message := fnd_message.get ;
148 x_error_code := xnp_errors.g_null_sql_source;
149 RETURN;
150 END IF;
151
152 --check for semicolon
153
154 l_data_source := RTRIM(p_data_source) ;
155
156 l_exists := INSTR(l_data_source, ';') ;
157
158 IF (l_exists <> 0) THEN
159 fnd_message.set_name('XNP', 'SEMI_COLON_ERROR');
160 x_error_message := fnd_message.get ;
161 x_error_code := xnp_errors.g_semi_colon_error;
162 RETURN;
163 END IF;
164
165 -- check if leaf element and see if there is a data reference
166
167 l_parent := check_if_parent(p_element_id) ;
168
169 IF (l_parent = 0) AND (p_data_ref IS NULL) THEN
170 fnd_message.set_name('XNP', 'NO_DATA_REFERENCE');
171 fnd_message.set_token('NAME',p_element_name) ;
172 x_error_message := fnd_message.get ;
173 x_error_code := xnp_errors.g_no_data_reference;
174 RETURN;
175 END IF;
176
177 END IF;
178
179 IF (p_source_type = 'PROCEDURE') THEN
180
181 -- check if procedure name is defined
182
183 IF (p_data_ref IS NULL) THEN
184 fnd_message.set_name('XNP', 'UNDEFINED_FUNCTION');
185 fnd_message.set_token('NAME', p_element_name);
186 x_error_message := fnd_message.get ;
187 x_error_code := xnp_errors.g_undefined_function;
188 RETURN;
189 END IF;
190
191 END IF;
192
193 IF (p_source_type = 'SDP_WI') THEN
194
195 -- check if parameter exists in parameter pool
196
197 l_exists := check_parameter_pool(
198 p_element_name => p_element_name,
199 p_reference => p_data_ref) ;
200
201 IF (l_exists = 0) THEN
202 fnd_message.set_name('XNP', 'UNDEFINED_WI_PARAMETER');
203 IF (p_data_ref IS NULL) THEN
204 fnd_message.set_token('NAME',p_element_name);
205 ELSE
206 fnd_message.set_token('NAME',p_data_ref);
207 END IF;
208 x_error_message := fnd_message.get ;
209 x_error_code := xnp_errors.g_undefined_wi_parameter;
210 RETURN;
211 END IF;
212
213 END IF;
214
215
216 IF (p_source_type = 'ORDER') THEN
217 -- rnyberg, 09/26/2001. Removed check for Order Parameters in pool
218 -- by putting section below within comments.
219 -- Order Parameters do not have to exist in a pool.
220 NULL;
221 /*
222
223 -- check if parameter exists in parameter pool
224
225 l_exists := check_parameter_pool(
226 p_element_name => p_element_name,
227 p_reference => p_data_ref) ;
228
229 IF (l_exists = 0) THEN
230 fnd_message.set_name('XNP', 'UNDEFINED_ORDER_PARAMETER');
231 IF (p_data_ref IS NULL) THEN
232 fnd_message.set_token('NAME',p_element_name);
233 ELSE
234 fnd_message.set_token('NAME',p_data_ref);
235 END IF;
236 x_error_message := fnd_message.get ;
237 x_error_code := xnp_errors.g_undefined_order_parameter;
238 RETURN;
239 END IF;
240 */
241 END IF;
242
243 IF (p_source_type = 'SDP_FA') THEN
244
245 -- rnyberg, 09/26/2001. Removed check for FA Parameters in pool
246 -- by putting section below within comments.
247 -- As of R11.5.6, FA Parameters are not stored in a pool.
248 NULL;
249 /*
250 -- check if parameter exists in parameter pool
251
252 l_exists := check_parameter_pool(
253 p_element_name => p_element_name,
254 p_reference => p_data_ref) ;
255
256 IF (l_exists = 0) THEN
257 fnd_message.set_name('XNP', 'UNDEFINED_FA_PARAMETER');
258 IF (p_data_ref IS NULL) THEN
259 fnd_message.set_token('NAME',p_element_name);
260 ELSE
261 fnd_message.set_token('NAME',p_data_ref);
262 END IF;
263 x_error_message := fnd_message.get ;
264 x_error_code := xnp_errors.g_undefined_fa_parameter;
265 RETURN;
266 END IF;
267 */
268 END IF;
269
270 EXCEPTION
271 WHEN OTHERS THEN
272 x_error_code := SQLCODE;
273 x_error_message := SQLERRM;
274
275 END check_source;
276
277 /*******************************************************************/
278
279
280
281 FUNCTION check_exists(p_msg_code IN VARCHAR2 ) RETURN NUMBER
282
283 IS
284
285 CURSOR get_object IS
286 SELECT object_name FROM user_objects
287 WHERE object_name = p_msg_code
288 AND OBJECT_TYPE <> 'SYNONYM';
289
290 l_object_name VARCHAR2(128);
291
292 BEGIN
293
294 OPEN get_object;
295 FETCH get_object INTO l_object_name;
296
297 IF (get_object%NOTFOUND) THEN
298 CLOSE get_object;
299 RETURN 0;
300
301 ELSE
302 CLOSE get_object;
303 RETURN 1;
304 END IF ;
305
306 END check_exists ;
307
308 /***********************************************************************/
309
310 FUNCTION check_if_parent(p_element_id NUMBER) RETURN NUMBER
311
312 IS
313
314 CURSOR get_parent IS
315 --skilaru 03/27/2001
316 --SELECT * FROM xnp_msg_structures
317 SELECT 'Y' FROM xnp_msg_structures
318 WHERE parent_element_id = p_element_id ;
319 --skilaru 03/27/2001
320 --l_row xnp_msg_structures%ROWTYPE ;
321 l_row VARCHAR2(1) ;
322
323 BEGIN
324
325 OPEN get_parent;
326 FETCH get_parent INTO l_row;
327
328 IF (get_parent%NOTFOUND) THEN
329 CLOSE get_parent;
330 RETURN 0;
331
332 ELSE
333 CLOSE get_parent;
334 RETURN 1;
335 END IF ;
336
337 END check_if_parent ;
338
339
340 /***********************************************************************/
341
342 FUNCTION check_parameter_pool
343 (p_element_name IN VARCHAR2,
344 p_reference IN VARCHAR2) RETURN NUMBER
345
346 IS
347
348 -- rnyberg, 09/26/2001. Replaced CSI_EXT_ with CSI_EXTEND_
349 -- Also replaced FND_LOOKUPS with CSI_LOOKUPS
350 CURSOR get_parameter (l_parameter_name IN VARCHAR2) IS
351 SELECT lookup_code FROM CSI_LOOKUPS
352 WHERE lookup_code = l_parameter_name
353 AND lookup_type = 'CSI_EXTEND_ATTRIB_POOL';
354
355 l_parameter VARCHAR2(80);
356
357 BEGIN
358
359 IF (p_reference IS NOT NULL) THEN
360 OPEN get_parameter(p_reference) ;
361 ELSE
362 OPEN get_parameter(p_element_name) ;
363 END IF;
364
365 FETCH get_parameter INTO l_parameter;
366
367 IF (get_parameter%NOTFOUND) THEN
368 CLOSE get_parameter;
369 RETURN 0;
370
371 ELSE
372 CLOSE get_parameter;
373 RETURN 1;
374 END IF ;
375
376 END check_parameter_pool ;
377
378
379 END xnp_msg_schema;
[Home] [Help]