1 PACKAGE BODY GMA_VALID_GRP AS
2 --$Header: GMAGVALB.pls 115.1 1999/11/11 08:49:15 pkm ship $
3 -- Body start of comments
4 --+==========================================================================+
5 --| Copyright (c) 1998 Oracle Corporation |
6 --| Redwood Shores, CA, USA |
7 --| All rights reserved. |
8 --+==========================================================================+
9 --| FILE NAME |
10 --| GMAGVALB.pls |
11 --| |
12 --| PACKAGE NAME |
13 --| GMA_VALID_GRP |
14 --| |
15 --| DESCRIPTION |
16 --| This package contains various validation functions |
17 --| |
18 --| CONTENTS |
19 --| NumRangeCheck |
20 --| Validate_um |
21 --| Validate_Reason_Code |
22 --| Validate_Orgn_code |
23 --| Validate_Co_Code |
24 --| Validate_Orgn_For_Company |
25 --| Validate_Doc_No |
26 --| Validate_Type |
27 --| |
28 --| HISTORY |
29 --| 28-OCT-1999 H.Verdding Bug 1042739 Added Extra Parameter |
30 --| p_orgn_code To Validate_Doc_No |
31 --| |
32 --+==========================================================================+
33 -- Body end of comments
34
35 -- Func start of comments
36 --+==========================================================================+
37 --| FUNCTION NAME |
38 --| NumRangeCheck |
39 --| |
40 --| USAGE |
41 --| Validates parameter is within given numeric range |
42 --| |
43 --| DESCRIPTION |
44 --| This generic function checks that a numeric parameter is within |
45 --| a given numeric range |
46 --| |
47 --| PARAMETERS |
48 --| p_min IN NUMBER - Minimum value |
49 --| p_max IN NUMBER - Maximum value |
50 --| p_value IN NUMBER - Value to be checked |
51 --| |
52 --| RETURNS |
53 --| TRUE - If p_value falls within the specified range |
54 --| FALSE - If p_value does not fall within the specified range |
55 --| |
56 --| HISTORY |
57 --| |
58 --+==========================================================================+
59 -- Func end of comments
60 FUNCTION NumRangeCheck
61 ( p_min IN NUMBER
62 , p_max IN NUMBER
63 , p_value IN NUMBER
64 )
65 RETURN BOOLEAN
66 IS
67
68 BEGIN
69
70 IF (p_value < p_min OR p_value > p_max)
71 THEN
72 RETURN FALSE;
73 ELSE
74 RETURN TRUE;
75 END IF;
76
77 END NumRangeCheck;
78
79 -- Func start of comments
80 --+==========================================================================+
81 --| FUNCTION NAME |
82 --| Validate_Um |
83 --| |
84 --| USAGE |
85 --| Validates Unit Of Measure exists |
86 --| |
87 --| DESCRIPTION |
88 --| This generic function validates that the Unit Of Measure supplied |
89 --| exists on sy_uoms_mst |
90 --| |
91 --| PARAMETERS |
92 --| p_um IN VARCHAR2 - Unit Of Measure Code |
93 --| |
94 --| RETURNS |
95 --| TRUE - If UoM Code exists |
96 --| FALSE - If UoM Code does not exist |
97 --| |
98 --| HISTORY |
99 --| |
100 --+==========================================================================+
101 -- Func end of comments
102 FUNCTION Validate_Um
103 ( p_um IN sy_uoms_mst.um_code%TYPE
104 )
105 RETURN BOOLEAN
106 IS
107 l_um_desc SY_UOMS_MST.um_desc%TYPE;
108 CURSOR sy_uoms_mst_c1 IS
109 SELECT
110 um_desc
111 FROM
112 sy_uoms_mst
113 WHERE
114 um_code = p_um
115 AND delete_mark = 0;
116
117 BEGIN
118
119 OPEN sy_uoms_mst_c1;
120
121 FETCH sy_uoms_mst_c1 INTO l_um_desc;
122 IF (sy_uoms_mst_c1%NOTFOUND)
123 THEN
124 CLOSE sy_uoms_mst_c1;
125 RETURN FALSE;
126 ELSE
127 CLOSE sy_uoms_mst_c1;
128 RETURN TRUE;
129 END IF;
130
131 EXCEPTION
132 WHEN OTHERS THEN
133 RAISE;
134
135 END Validate_Um;
136
137 -- Func start of comments
138 --+==========================================================================+
139 --| FUNCTION NAME |
140 --| Validate_Reason_Code |
141 --| |
142 --| USAGE |
143 --| Validates Reason Code |
144 --| |
145 --| DESCRIPTION |
146 --| This generic function validates that the Reason Code passed exists |
147 --| on sy_reas_cds |
148 --| |
149 --| PARAMETERS |
150 --| p_reason_code IN VARCHAR2 - Reason Code |
151 --| |
152 --| RETURNS |
153 --| TRUE - If Reason Code is valid |
154 --| FALSE - If Reason Code is not valid |
155 --| |
156 --| HISTORY |
157 --| |
158 --+==========================================================================+
159 -- Func end of comments
160 FUNCTION Validate_reason_code
161 (p_reason_code IN sy_reas_cds.reason_code%TYPE
162 )
163 RETURN BOOLEAN
164 IS
165 l_reason_code VARCHAR2(4);
166 CURSOR sy_reas_cds_c1 IS
167 SELECT
168 reason_code
169 FROM
170 sy_reas_cds
171 WHERE
172 reason_code = p_reason_code
173 AND delete_mark = 0;
174
175 BEGIN
176 OPEN sy_reas_cds_c1;
177 FETCH sy_reas_cds_c1 INTO l_reason_code;
178 IF (sy_reas_cds_c1%NOTFOUND)
179 THEN
180 CLOSE sy_reas_cds_c1;
181 RETURN FALSE;
182 ELSE
183 CLOSE sy_reas_cds_c1;
184 RETURN TRUE;
185 END IF;
186
187 EXCEPTION
188 WHEN OTHERS THEN
189 RAISE;
190
191 END Validate_Reason_Code;
192
193 -- Func start of comments
194 --+==========================================================================+
195 --| FUNCTION NAME |
196 --| Validate_Orgn_Code |
197 --| |
198 --| USAGE |
199 --| Validates Organization Code |
200 --| |
201 --| DESCRIPTION |
202 --| This generic function validates that the Organization Code exists |
203 --| on sy_orgn_mst |
204 --| |
205 --| PARAMETERS |
206 --| p_orgn_code IN VARCHAR2 - Organization Code |
207 --| |
208 --| RETURNS |
209 --| TRUE - If Organization Code is valid |
210 --| FALSE - If Organization Code is not valid |
211 --| |
212 --| HISTORY |
213 --| |
214 --+==========================================================================+
215 -- Func end of comments
216 FUNCTION Validate_Orgn_Code
217 (p_orgn_code IN sy_orgn_mst.orgn_code%TYPE
218 )
219 RETURN BOOLEAN
220 IS
221 l_orgn_code VARCHAR2(4);
222 CURSOR sy_orgn_mst_c1 IS
223 SELECT
224 orgn_code
225 FROM
226 sy_orgn_mst
227 WHERE
228 orgn_code = p_orgn_code
229 AND delete_mark = 0;
230
231 BEGIN
232 OPEN sy_orgn_mst_c1;
233 FETCH sy_orgn_mst_c1 INTO l_orgn_code;
234 IF (sy_orgn_mst_c1%NOTFOUND)
235 THEN
236 CLOSE sy_orgn_mst_c1;
237 RETURN FALSE;
238 ELSE
239 CLOSE sy_orgn_mst_c1;
240 RETURN TRUE;
241 END IF;
242
243 EXCEPTION
244 WHEN OTHERS THEN
245 RAISE;
246
247 END Validate_Orgn_Code;
248
249 -- Func start of comments
250 --+==========================================================================+
251 --| FUNCTION NAME |
252 --| Validate_Co_Code |
253 --| |
254 --| USAGE |
258 --| This generic function validates that the Organization Code exists |
255 --| Validates Company Code |
256 --| |
257 --| DESCRIPTION |
259 --| on sy_orgn_mst and is defined as a company |
260 --| |
261 --| PARAMETERS |
262 --| p_co_code IN VARCHAR2 - Company Code |
263 --| |
264 --| RETURNS |
265 --| TRUE - If Comapny Code is valid |
266 --| FALSE - If Company Code is not valid |
267 --| |
268 --| HISTORY |
269 --| |
270 --+==========================================================================+
271 -- Func end of comments
272 FUNCTION Validate_Co_Code
273 (p_co_code IN sy_orgn_mst.orgn_code%TYPE
274 )
275 RETURN BOOLEAN
276 IS
277 l_co_code VARCHAR2(4);
278 CURSOR sy_orgn_mst_c1 IS
279 SELECT
280 co_code
281 FROM
282 sy_orgn_mst
283 WHERE
284 orgn_code = p_co_code
285 AND co_code = p_co_code
286 AND delete_mark = 0;
287
288 BEGIN
289 OPEN sy_orgn_mst_c1;
290 FETCH sy_orgn_mst_c1 INTO l_co_code;
291 IF (sy_orgn_mst_c1%NOTFOUND)
292 THEN
293 CLOSE sy_orgn_mst_c1;
294 RETURN FALSE;
295 ELSE
296 CLOSE sy_orgn_mst_c1;
297 RETURN TRUE;
298 END IF;
299
300 EXCEPTION
301 WHEN OTHERS THEN
302 RAISE;
303
304 END Validate_Co_Code;
305
306 -- Func start of comments
307 --+==========================================================================+
308 --| FUNCTION NAME |
309 --| Validate_Orgn_For_Company |
310 --| |
311 --| USAGE |
312 --| Validates Organization for Company |
313 --| |
314 --| DESCRIPTION |
315 --| This generic function validates that the Organization Code belongs to |
316 --| to the given company |
317 --| |
318 --| PARAMETERS |
319 --| p_orgn_code IN VARCHAR2 - Organization Code |
320 --| p_co_code IN VARCHAR2 - Company Code |
321 --| |
322 --| RETURNS |
323 --| TRUE - If Organization belongs to the Company |
324 --| FALSE - If Organization does not belongs to the Company |
325 --| |
326 --| HISTORY |
327 --| |
328 --+==========================================================================+
329 -- Func end of comments
330 FUNCTION Validate_Orgn_For_Company
331 ( p_orgn_code IN sy_orgn_mst.orgn_code%TYPE
332 , p_co_code IN sy_orgn_mst.orgn_code%TYPE
333 )
334 RETURN BOOLEAN
335 IS
336 l_co_code VARCHAR2(4);
337 CURSOR sy_orgn_mst_c1 IS
338 SELECT
339 co_code
340 FROM
341 sy_orgn_mst
342 WHERE
343 orgn_code = p_orgn_code
344 AND co_code = p_co_code
345 AND delete_mark = 0;
346
347 BEGIN
348 OPEN sy_orgn_mst_c1;
349 FETCH sy_orgn_mst_c1 INTO l_co_code;
350 IF (sy_orgn_mst_c1%NOTFOUND)
351 THEN
352 CLOSE sy_orgn_mst_c1;
353 RETURN FALSE;
354 ELSE
355 CLOSE sy_orgn_mst_c1;
356 RETURN TRUE;
357 END IF;
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 RAISE;
362
363 END Validate_Orgn_For_Company;
364
365 -- Func start of comments
366 --+==========================================================================+
367 --| FUNCTION NAME |
368 --| Validate_Doc_No |
369 --| |
370 --| USAGE |
371 --| Validates Document Number |
372 --| |
373 --| DESCRIPTION |
374 --| This generic function validates that the Document number for the |
375 --| passed document type is valid. If manual numbering then any non NULL |
376 --| or non BLANK value is allowed. If Automatic numbering then value |
377 --| must be BLANK or NULL. |
378 --| |
379 --| PARAMETERS |
380 --| p_doc_type IN VARCHAR2 - Document Type |
381 --| p_doc_no IN VARCHAR2 - Document Number |
382 --| |
383 --| RETURNS |
384 --| TRUE - If Document Number is valid |
385 --| FALSE - If Document Number is not valid |
386 --| |
387 --| HISTORY |
388 --| 28-OCT-1999 H.Verdding Bug 1042739 Added Extra Parameter |
389 --| p_orgn_code To Validate_Doc_No |
390 --| |
391 --+==========================================================================+
392 -- Func end of comments
393 FUNCTION Validate_Doc_No
394 ( p_doc_type IN sy_docs_seq.doc_type%TYPE
395 , p_doc_no IN VARCHAR2
396 , p_orgn_code IN sy_orgn_mst.orgn_code%TYPE
397 )
398 RETURN BOOLEAN
399 IS
400 l_assignment_type sy_docs_seq.assignment_type%TYPE;
401 CURSOR sy_docs_seq_c1 IS
402 SELECT
403 assignment_type
404 FROM
405 sy_docs_seq
406 WHERE
407 doc_type = p_doc_type
408 AND orgn_code = p_orgn_code
409 AND delete_mark = 0;
410
411 BEGIN
412
413 OPEN sy_docs_seq_c1;
414 FETCH sy_docs_seq_c1 INTO l_assignment_type;
415 IF (sy_docs_seq_c1%NOTFOUND)
416 THEN
417 CLOSE sy_docs_seq_c1;
418 RETURN FALSE;
419 ELSE
420 CLOSE sy_docs_seq_c1;
421 IF (l_assignment_type = 1)
422 THEN
423 IF (p_doc_no = ' ' OR p_doc_no IS NULL)
424 THEN
425 RETURN FALSE;
426 ELSE
427 RETURN TRUE;
428 END IF;
429 ELSIF (l_assignment_type = 2)
430 THEN
431 IF (p_doc_no = ' ' OR p_doc_no IS NULL)
432 THEN
433 RETURN TRUE;
434 ELSE
435 RETURN FALSE;
436 END IF;
437 ELSE
438 RETURN TRUE;
439 END IF;
440 END IF;
441
442 EXCEPTION
443 WHEN OTHERS THEN
444 RAISE;
445
446 END Validate_Doc_No;
447
448 --+==========================================================================+
449 --| FUNCTION NAME |
450 --| Validate_Type |
451 --| |
452 --| USAGE |
453 --| Validates type fields |
454 --| |
455 --| DESCRIPTION |
456 --| This generic function validates that the type value passed is |
457 --| valid for table and field name |
458 --| |
459 --| PARAMETERS |
460 --| p_lookup_type IN VARCHAR2 - field name of type field |
461 --| p_lookup_code IN VARCHAR2 - field value |
462 --| |
463 --| RETURNS |
464 --| TRUE - If type value is valid |
465 --| FALSE - If type value is not valid |
466 --| |
467 --| HISTORY |
468 --| |
469 --+==========================================================================+
470 -- Func end of comments
471 FUNCTION Validate_Type
472 ( p_lookup_type IN gem_lookups.lookup_type%TYPE
473 , p_lookup_code IN gem_lookups.lookup_code%TYPE
474 )
475 RETURN BOOLEAN
476 IS
477 l_lookup_type gem_lookups.lookup_type%TYPE;
478 CURSOR gem_lookups_c1 IS
479 SELECT
480 lookup_type
481 FROM
482 gem_lookups
483 WHERE
484 lookup_type = p_lookup_type
485 AND lookup_code = p_lookup_code;
486
487 BEGIN
488 OPEN gem_lookups_c1;
489 FETCH gem_lookups_c1 INTO l_lookup_type;
490 IF (gem_lookups_c1%NOTFOUND)
491 THEN
492 CLOSE gem_lookups_c1;
493 RETURN FALSE;
494 ELSE
495 CLOSE gem_lookups_c1;
496 RETURN TRUE;
497 END IF;
498
499 EXCEPTION
500 WHEN OTHERS THEN
501 RAISE;
502
503 END Validate_Type;
504
505 END GMA_VALID_GRP;
[Home] [Help]