[Home] [Help]
PACKAGE BODY: APPS.OKL_FORMULAVALIDATE_PVT
Source
1 PACKAGE BODY OKL_FORMULAVALIDATE_PVT AS
2 /* $Header: OKLRVALB.pls 115.8 2002/12/18 12:52:06 kjinger noship $ */
3
4 -- Start of comments
5 --
6 -- Function Name : VAL_GetAllFunctionParameters
7 -- Description : Function fetches all the data source function parameter
8 -- for a given formula identifier.
9 -- Business Rules :
10 -- Parameters : p_fma_id - Formula identifier.
11 -- Returns l_fncpmr_tbl - List of data source function ids.
12 -- Version : 1.0
13 --
14 -- End of comments
15
16 FUNCTION VAL_GetAllFunctionParameters(
17 p_fma_id IN NUMBER )
18 RETURN CtxParameter_tbl
19 IS
20 CURSOR fmapmr_cur
21 IS
22 SELECT
23 pmrv.id
24 FROM
25 okl_fnctn_prmtrs_v fprv,
26 okl_parameters_v pmrv
27 WHERE
28 fprv.dsf_id IN
29 ( SELECT
30 dsfv.id
31 FROM
32 okl_formulae_v fmav,
33 okl_operands_v opdv,
34 okl_fmla_oprnds_v fodv,
35 okl_data_src_fnctns_v dsfv
36 WHERE
37 fmav.id = p_fma_id
38 AND fmav.id = fodv.fma_id
39 AND fodv.opd_id = opdv.id
40 AND opdv.opd_type = 'FCNT'
41 AND opdv.dsf_id = dsfv.id )
42 AND fprv.pmr_id = pmrv.id;
43
44 l_dsf_parameter_tbl CtxParameter_tbl;
45 l_Count NUMBER := 1;
46
47 BEGIN
48
49 FOR fmapmr_rec IN fmapmr_cur
50 LOOP
51 l_dsf_parameter_tbl(l_Count).parameter_id := fmapmr_rec.id;
52 l_Count := l_Count + 1;
53 END LOOP;
54
55 RETURN l_dsf_parameter_tbl;
56 END VAL_GetAllFunctionParameters;
57
58 -- Start of comments
59 --
60 -- Function Name : VAL_CompareCtxPrmWithFncPrm
61 -- Description : Function compares context parameter with data source
62 -- function parameter.
63 -- Business Rules :
64 -- Parameters : p_ctx_parameter_tbl - Context Parameter table.
65 -- p_fnc_parameter_tbl - Function Parameter table.
66 -- Returns boolean - true/false.
67 -- Version : 1.0
68 --
69 -- End of comments
70
71 FUNCTION VAL_CompareCtxPrmWithFncPrm(
72 p_ctx_parameter_tbl IN CtxParameter_tbl
73 ,p_fnc_parameter_tbl IN CtxParameter_tbl )
74 RETURN BOOLEAN
75 IS
76 l_Match BOOLEAN := FALSE;
77 l_outerCount NUMBER;
78 l_innerCount NUMBER;
79 BEGIN
80
81 -- Added by Santonyr 29-Jul-2002
82 -- Return TRUE if both the counts are zero.
83
84 IF p_ctx_parameter_tbl.COUNT = 0 AND p_fnc_parameter_tbl.COUNT = 0 THEN
85 RETURN TRUE;
86 END IF;
87
88 FOR l_outerCount IN 1 .. p_fnc_parameter_tbl.count
89 LOOP
90 l_Match := FALSE;
91 FOR l_innerCount in 1 .. p_ctx_parameter_tbl.count
92 LOOP
93 IF ( p_fnc_parameter_tbl(l_outerCount).parameter_id =
94 p_ctx_parameter_tbl(l_innerCount).parameter_id )
95 THEN
96 l_Match := TRUE;
97 EXIT;
98 END IF;
99 END LOOP;
100
101 IF ( l_Match = FALSE )
102 THEN
103 EXIT;
104 END IF;
105 END LOOP;
106
107 RETURN l_Match;
108
109 END VAL_CompareCtxPrmWithFncPrm;
110
111 -- Start of comments
112 --
113 -- Function Name : VAL_IsRecursive
114 -- Description : Function checks for recursion in a given 2 PL/SQL tables
115 -- it takes the first table as master and compares it with
116 -- the 2nd one.
117 -- Business Rules :
118 -- Parameters : p_fma_id - Formula identifier.
119 -- Returns l_fncpmr_tbl - List of data source function ids.
120 -- Version : 1.0
121 --
122 -- End of comments
123
124 FUNCTION VAL_IsRecursive(
125 p_allfmaopd_tbl IN FmaOpd_tbl
126 ,p_newfmaopd_tbl IN FmaOpd_tbl )
127 RETURN BOOLEAN
128 IS
129 l_outerCount NUMBER;
130 l_innerCount NUMBER;
131 l_bRet BOOLEAN := FALSE;
132 BEGIN
133
134 FOR l_outerCount in 1 .. p_newfmaopd_tbl.count
135 LOOP
136 l_bRet := FALSE;
137 FOR l_innerCount in 1 .. p_allfmaopd_tbl.count
138 LOOP
139 IF p_allfmaopd_tbl(l_innerCount).id =
140 p_newfmaopd_tbl(l_outerCount).id
141 THEN
142 l_bRet := TRUE;
143 EXIT;
144 END IF;
145 END LOOP;
146
147 IF ( l_bRet = TRUE )
148 THEN
149 EXIT;
150 END IF;
151
152 END LOOP;
153 RETURN l_bRet;
154 END VAL_IsRecursive;
155
156 -- Start of comments
157 --
158 -- Function Name : VAL_GetOperandsOfTypeFma
159 -- Description : Function fetches all the formula used by the given
160 -- formula identifier, also returns a boolean value for
161 -- recursion.
162 -- Business Rules :
163 -- Parameters : p_fma_id - Formula identifier.
164 -- x_fma_ids - Array of formula identifier.
165 -- Returns boolean - true/false.
166 -- Version : 1.0
167 --
168 -- End of comments
169
170 FUNCTION VAL_GetOperandsOfTypeFma(
171 p_fma_id IN NUMBER,
172 x_fmaopd_tbl OUT NOCOPY fmaopd_tbl )
173 RETURN BOOLEAN
174 IS
175 CURSOR fmaopd_cur(p_l_fma_id IN NUMBER)
176 IS
177 SELECT opdv.fma_id
178 FROM
179 okl_formulae_v fmav,
180 okl_fmla_oprnds_v fodv,
181 okl_operands_v opdv
182 WHERE
183 fmav.id = p_l_fma_id
184 AND fodv.fma_id = fmav.id
185 AND opdv.id = fodv.opd_id
186 AND opdv.opd_type = 'FMLA';
187
188 l_fma_id NUMBER;
189 l_fmaopd_tbl FmaOpd_tbl;
190 l_newfmaopd_tbl FmaOpd_tbl;
191 l_Count NUMBER;
192 l_xCount NUMBER;
193 l_newCount NUMBER;
194 l_bRet BOOLEAN := TRUE;
195 l_ProceedFlag BOOLEAN := TRUE;
196
197 BEGIN
198
199 l_Count := 1;
200 l_xCount := 1;
201 l_newCount := 1;
202
203 l_fmaopd_tbl(l_Count).id := p_fma_id;
204
205 WHILE ( l_ProceedFlag = TRUE )
206 LOOP
207 FOR l_Count IN 1 .. l_fmaopd_tbl.count
208 LOOP
209 x_fmaopd_tbl(l_xCount).id :=
210 l_fmaopd_tbl(l_Count).id;
211
212 l_newCount := 0;
213 FOR fmaopd_rec in
214 fmaopd_cur(
215 l_fmaopd_tbl(l_Count).id )
216 LOOP
217 l_newCount := l_newCount + 1;
218 l_newfmaopd_tbl(l_newCount).id :=
219 fmaopd_rec.fma_id;
220
221 /** SBALASHA001 - **/
222 END LOOP;
223 l_xCount := l_xCount + 1;
224 IF ( l_newCount = 0 )
225 THEN
226 l_ProceedFlag := FALSE;
227 END IF;
228 END LOOP;
229 /** SBALASHA001
230 INFO: Check for recursion **/
231 l_bRet :=
232 VAL_IsRecursive(p_allfmaopd_tbl => x_fmaopd_tbl,
233 p_newfmaopd_tbl => l_newfmaopd_tbl);
234 IF ( l_bRet = TRUE )
235 THEN
236 /** SBALASHA001
237 INFO: Recursion found. **/
238 EXIT;
239 END IF;
240 l_fmaopd_tbl := l_newfmaopd_tbl;
241
242 /** SBALASHA001 -
243 INFO: Delete PL/SQL table entries. **/
244 l_newfmaopd_tbl.delete;
245 END LOOP;
246 RETURN l_bRet;
247
248 END VAL_GetOperandsOfTypeFma;
249
250 -- Start of comments
251 --
252 -- Procedure Name : VAL_ValidateFormula
253 -- Description :
254 -- It does the following validation;
255 -- 1) Check for recursion.
256 -- 2) Check against Context group parameter with
257 -- data source function parameter.
258 -- Business Rules :
259 -- Parameters :
260 -- p_api_version - API Version.
261 -- p_init_msg_list - FND message initializer flag.
262 -- x_return_status - Return Status.
263 -- x_msg_count - FND message count.
264 -- x_msg_data - FND message data.
265 -- p_fma_id - Formula identifier.
266 -- p_cgr_id - Context group identifier.
267 -- Version : 1.0
268 --
269 -- End of comments
270
271 PROCEDURE VAL_ValidateFormula(
272 p_api_version IN NUMBER
273 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
274 ,x_return_status OUT NOCOPY VARCHAR2
275 ,x_msg_count OUT NOCOPY NUMBER
276 ,x_msg_data OUT NOCOPY VARCHAR2
277 ,x_validate_status OUT NOCOPY VARCHAR2
278 ,p_fma_id IN NUMBER
279 ,p_cgr_id IN NUMBER )
280 IS
281 l_ctx_parameter_tbl CtxParameter_tbl;
282 l_dsf_parameter_tbl CtxParameter_tbl;
283 l_fmaopd_tbl FmaOpd_tbl;
284 l_FormulaExists BOOLEAN := FALSE;
285 l_Match BOOLEAN := FALSE;
286 l_bRecursion BOOLEAN;
287
288 l_Count NUMBER;
289 BEGIN
290 x_return_status := OKL_API.G_RET_STS_SUCCESS;
291
292 /** SBALASHA001 -
293 INFO: Get all the formula operands for the given
294 formula identifier. **/
295 l_bRecursion :=
296 VAL_GetOperandsOfTypeFma( p_fma_id => p_fma_id,
297 x_fmaopd_tbl => l_fmaopd_tbl );
298
299
300 IF ( l_bRecursion = TRUE )
301 THEN
302 /** SBALASHA001 -
303 INFO: Recursion exception **/
304 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
305 p_msg_name => G_FMA_RECURSION,
306 p_token1 => G_RECURSION_TOKEN,
307 p_token1_value => p_fma_id );
308 -- notify error for calling API
309 x_return_status := OKL_API.G_RET_STS_ERROR;
310 -- notify error for calling API to override the regular exception handling.
311 x_validate_status := G_RET_STS_RECURSION_ERROR;
312 END IF;
313
314
315 IF ( x_return_status = OKL_API.G_RET_STS_SUCCESS )
316 THEN
317 /** SBALASHA001 -
318 INFO: Get all the context parameters for the
319 given formula. **/
320 l_count := OKL_FORMULAEVALUATE_PVT.EVA_GetParameterIDs(
321 p_fma_id => p_fma_id,
322 p_ctx_parameter_tbl => l_ctx_parameter_tbl );
323 END IF;
324
325 -- Commented by Santonyr 29-Jul-2002
326 -- Commenting this as the validation is not necessary.
327 /*
328 IF ( l_ctx_parameter_tbl.count = 0 )
329 THEN
330 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
331 p_msg_name => G_CTX_GROUP_NOTFOUND,
332 p_token1 => G_CTX_GROUP_TOKEN,
333 p_token1_value => p_cgr_id );
334 -- notify error for calling API
335 x_return_status := OKL_API.G_RET_STS_ERROR;
336 END IF;
337 */
338
339
340 IF ( x_return_status = OKL_API.G_RET_STS_SUCCESS )
341 THEN
342 /** SBALASHA001 -
343 INFO: Loop thru' the operand that are formula
344 and get all function parameters attached
345 to it.
346 **/
347 FOR l_Count IN 1 .. l_fmaopd_tbl.count
348 LOOP
349 /** SBALASHA001 -
350 INFO: Get all the function parameter for
351 a given formula indentifier **/
352 l_dsf_parameter_tbl :=
353 VAL_GetAllFunctionParameters(
354 p_fma_id => l_fmaopd_tbl(l_Count).id );
355
356
357 /** SBALASHA001 -
358 INFO: Compare context parameter with
359 data source function parameter. **/
360 l_Match := VAL_CompareCtxPrmWithFncPrm(
361 p_ctx_parameter_tbl => l_ctx_parameter_tbl,
362 p_fnc_parameter_tbl => l_dsf_parameter_tbl);
363 IF ( l_Match = FALSE )
364 THEN
365 /** SBALASHA001 -
366 INFO: Parameter mismatch exception. **/
367 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
368 p_msg_name => G_PRM_MISMATCH,
369 p_token1 => G_PRM_MISMATCH_TOKEN,
370 p_token1_value => p_fma_id );
371 -- notify error for calling API
372 x_return_status := OKL_API.G_RET_STS_ERROR;
373 -- notify error for calling API to override the regular exception handling.
374 x_validate_status := G_RET_STS_PRM_MISMATCH_ERROR;
375 EXIT;
376 END IF;
377 END LOOP;
378 END IF;
379
380 EXCEPTION
381 WHEN G_EXCEPTION_HALT_VALIDATION THEN
382 -- no processing necessary;
383 null;
384 WHEN OTHERS THEN
385 -- store SQL error message on message stack
386 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
387 p_msg_name => g_unexpected_error,
388 p_token1 => g_sqlcode_token,
389 p_token1_value => sqlcode,
390 p_token2 => g_sqlerrm_token,
391 p_token2_value => sqlerrm);
392 -- notify UNEXPECTED error for calling API.
393 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
394 END VAL_ValidateFormula;
395
396
397
398 END OKL_FORMULAVALIDATE_PVT;