[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPFMACONSTRAINTS_PVT
Source
1 PACKAGE BODY OKL_SETUPFMACONSTRAINTS_PVT AS
2 /* $Header: OKLRSFCB.pls 120.1.12010000.2 2008/11/20 20:14:32 cklee ship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 L_MODULE VARCHAR2(40) := 'LEASE.SETUP.FORMULAS';
5 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 L_LEVEL_PROCEDURE NUMBER;
7 IS_DEBUG_PROCEDURE_ON BOOLEAN;
8 -- End of wraper code generated automatically by Debug code generator
9
10 ---------------------------------------------------------------------------
11 -- PROCEDURE get_rec for: OKL_FMLA_OPRNDS_V
12 ---------------------------------------------------------------------------
13 PROCEDURE get_rec (
14 p_fodv_rec IN fodv_rec_type,
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_no_data_found OUT NOCOPY BOOLEAN,
17 x_fodv_rec OUT NOCOPY fodv_rec_type
18 ) IS
19 CURSOR okl_fodv_pk_csr (p_id IN NUMBER) IS
20 SELECT
21 ID,
22 OBJECT_VERSION_NUMBER,
23 OPD_ID,
24 FMA_ID,
25 LABEL,
26 CREATED_BY,
27 CREATION_DATE,
28 LAST_UPDATED_BY,
29 LAST_UPDATE_DATE,
30 NVL(LAST_UPDATE_LOGIN, OKL_API.G_MISS_NUM) LAST_UPDATE_LOGIN
31 FROM Okl_Fmla_Oprnds_V
32 WHERE okl_Fmla_Oprnds_V.id = p_id;
33 l_okl_fodv_pk okl_fodv_pk_csr%ROWTYPE;
34 l_fodv_rec fodv_rec_type;
35 BEGIN
36 x_return_status := OKL_API.G_RET_STS_SUCCESS;
37 x_no_data_found := TRUE;
38
39 -- Get current database values
40 OPEN okl_fodv_pk_csr (p_fodv_rec.id);
41 FETCH okl_fodv_pk_csr INTO
42 l_fodv_rec.ID,
43 l_fodv_rec.OBJECT_VERSION_NUMBER,
44 l_fodv_rec.OPD_ID,
45 l_fodv_rec.FMA_ID,
46 l_fodv_rec.LABEL,
47 l_fodv_rec.CREATED_BY,
48 l_fodv_rec.CREATION_DATE,
49 l_fodv_rec.LAST_UPDATED_BY,
50 l_fodv_rec.LAST_UPDATE_DATE,
51 l_fodv_rec.LAST_UPDATE_LOGIN;
52 x_no_data_found := okl_fodv_pk_csr%NOTFOUND;
53 CLOSE okl_fodv_pk_csr;
54 x_fodv_rec := l_fodv_rec;
55 EXCEPTION
56 WHEN OTHERS THEN
57 -- store SQL error message on message stack
58 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
59 p_msg_name => G_UNEXPECTED_ERROR,
60 p_token1 => G_SQLCODE_TOKEN,
61 p_token1_value => sqlcode,
62 p_token2 => G_SQLERRM_TOKEN,
63 p_token2_value => sqlerrm);
64 -- notify UNEXPECTED error for calling API.
65 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
66
67 IF (okl_fodv_pk_csr%ISOPEN) THEN
68 CLOSE okl_fodv_pk_csr;
69 END IF;
70
71 END get_rec;
72
73 ---------------------------------------------------------------------------
74 -- PROCEDURE check_operands for: OKL_FMLA_OPRNDS_V
75 -- To verify whether the dates are valid for both formula and operands
76 -- attached to it
77 ---------------------------------------------------------------------------
78 PROCEDURE check_operands (
79 p_fmav_rec IN fmav_rec_type,
80 p_fodv_rec IN fodv_rec_type,
81 x_return_status OUT NOCOPY VARCHAR2,
82 x_valid OUT NOCOPY BOOLEAN
83 ) IS
84 CURSOR okl_opdv_pk_csr (p_opd_id IN Okl_Operands_V.id%TYPE,
85 p_fma_id IN Okl_Formulae_V.id%TYPE
86 ) IS
87 SELECT '1'
88 FROM Okl_Operands_V opd,
89 Okl_Formulae_V fma
90 WHERE opd.ID = p_opd_id
91 AND fma.ID = p_fma_id
92 AND (opd.START_DATE > fma.START_DATE OR
93 NVL(opd.END_DATE, NVL(fma.END_DATE, OKL_API.G_MISS_DATE)) < NVL(fma.END_DATE, OKL_API.G_MISS_DATE));
94
95 l_check VARCHAR2(1) := '?';
96 l_row_not_found BOOLEAN := FALSE;
97 BEGIN
98 x_valid := TRUE;
99 x_return_status := OKL_API.G_RET_STS_SUCCESS;
100
101 -- Check for operand dates
102 OPEN okl_opdv_pk_csr (p_fodv_rec.opd_id,
103 p_fodv_rec.fma_id);
104 FETCH okl_opdv_pk_csr INTO l_check;
105 l_row_not_found := okl_opdv_pk_csr%NOTFOUND;
106 CLOSE okl_opdv_pk_csr;
107
108 IF l_row_not_found = FALSE then
109 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
110 p_msg_name => G_DATES_MISMATCH,
111 p_token1 => G_PARENT_TABLE_TOKEN,
112 p_token1_value => 'Okl_Operands_V',
113 p_token2 => G_CHILD_TABLE_TOKEN,
114 p_token2_value => 'Okl_Fmla_Oprnds_V');
115 x_valid := FALSE;
116 x_return_status := OKL_API.G_RET_STS_ERROR;
117 END IF;
118
119 EXCEPTION
120 WHEN OTHERS THEN
121 -- store SQL error message on message stack
122 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
123 p_msg_name => G_UNEXPECTED_ERROR,
124 p_token1 => G_SQLCODE_TOKEN,
125 p_token1_value => sqlcode,
126 p_token2 => G_SQLERRM_TOKEN,
127 p_token2_value => sqlerrm);
128 x_valid := FALSE;
129 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
130
131 IF (okl_opdv_pk_csr%ISOPEN) THEN
132 CLOSE okl_opdv_pk_csr;
133 END IF;
134
135
136 END check_operands;
137
138 ---------------------------------------------------------------------------
139 -- PROCEDURE insert_fmaconstraints for: OKL_FMLA_OPRNDS_V
140 ---------------------------------------------------------------------------
141 PROCEDURE insert_fmaconstraints(p_api_version IN NUMBER,
142 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
143 x_return_status OUT NOCOPY VARCHAR2,
144 x_msg_count OUT NOCOPY NUMBER,
145 x_msg_data OUT NOCOPY VARCHAR2,
146 p_fmav_rec IN fmav_rec_type,
147 p_fodv_rec IN fodv_rec_type,
148 x_fodv_rec OUT NOCOPY fodv_rec_type
149 ) IS
150 l_api_version CONSTANT NUMBER := 1;
151 l_api_name CONSTANT VARCHAR2(30) := 'insert_fmaconstraints';
152 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
153 l_valid BOOLEAN;
154 l_fmav_rec fmav_rec_type;
155 l_fodv_rec fodv_rec_type;
156 BEGIN
157 x_return_status := OKC_API.G_RET_STS_SUCCESS;
158 l_return_status := OKC_API.START_ACTIVITY(p_api_name => l_api_name,
159 p_pkg_name => G_PKG_NAME,
160 p_init_msg_list => p_init_msg_list,
161 l_api_version => l_api_version,
162 p_api_version => p_api_version,
163 p_api_type => '_PVT',
164 x_return_status => l_return_status);
165 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
166 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
167 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
168 RAISE OKC_API.G_EXCEPTION_ERROR;
169 END IF;
170
171 l_fmav_rec := p_fmav_rec;
172 l_fodv_rec := p_fodv_rec;
173
174
175 /* call check_operands to check the validity of this relationship */
176 check_operands(p_fmav_rec => l_fmav_rec,
177 p_fodv_rec => l_fodv_rec,
178 x_return_status => l_return_status,
179 x_valid => l_valid);
180 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
181 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
182 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
183 (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
184 l_valid <> TRUE) THEN
185 x_return_status := OKL_API.G_RET_STS_ERROR;
186 RAISE OKC_API.G_EXCEPTION_ERROR;
187 END IF;
188
189 /* public api to insert formulae constraints */
190 -- Start of wraper code generated automatically by Debug code generator for okl_fmla_oprnds_pub.insert_fmla_oprnds
191 IF(L_DEBUG_ENABLED='Y') THEN
192 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
193 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
194 END IF;
195 IF(IS_DEBUG_PROCEDURE_ON) THEN
196 BEGIN
197 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSFCB.pls call okl_fmla_oprnds_pub.insert_fmla_oprnds ');
198 END;
199 END IF;
200 okl_fmla_oprnds_pub.insert_fmla_oprnds(p_api_version => p_api_version,
201 p_init_msg_list => p_init_msg_list,
202 x_return_status => l_return_status,
203 x_msg_count => x_msg_count,
204 x_msg_data => x_msg_data,
205 p_fodv_rec => l_fodv_rec,
206 x_fodv_rec => x_fodv_rec);
207 IF(IS_DEBUG_PROCEDURE_ON) THEN
208 BEGIN
209 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSFCB.pls call okl_fmla_oprnds_pub.insert_fmla_oprnds ');
210 END;
211 END IF;
212 -- End of wraper code generated automatically by Debug code generator for okl_fmla_oprnds_pub.insert_fmla_oprnds
213
214 IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
215 RAISE OKC_API.G_EXCEPTION_ERROR;
216 ELSIF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
217 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
218 END IF;
219
220 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
221 x_msg_data => x_msg_data);
222 EXCEPTION
223 WHEN OKC_API.G_EXCEPTION_ERROR THEN
224 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
225 p_pkg_name => G_PKG_NAME,
226 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
227 x_msg_count => x_msg_count,
228 x_msg_data => x_msg_data,
229 p_api_type => '_PVT');
230 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
231 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
232 p_pkg_name => G_PKG_NAME,
233 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
234 x_msg_count => x_msg_count,
235 x_msg_data => x_msg_data,
236 p_api_type => '_PVT');
237 WHEN OTHERS THEN
238 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
239 p_pkg_name => G_PKG_NAME,
240 p_exc_name => 'OTHERS',
241 x_msg_count => x_msg_count,
242 x_msg_data => x_msg_data,
243 p_api_type => '_PVT');
244
245 END insert_fmaconstraints;
246
247 ---------------------------------------------------------------------------
248 -- PROCEDURE update_fmaconstraints for: OKL_FMLA_OPRNDS_V
249 ---------------------------------------------------------------------------
250 PROCEDURE update_fmaconstraints(p_api_version IN NUMBER,
251 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
252 x_return_status OUT NOCOPY VARCHAR2,
253 x_msg_count OUT NOCOPY NUMBER,
254 x_msg_data OUT NOCOPY VARCHAR2,
255 p_fmav_rec IN fmav_rec_type,
256 p_fodv_rec IN fodv_rec_type,
257 x_fodv_rec OUT NOCOPY fodv_rec_type
258 ) IS
259 l_api_version CONSTANT NUMBER := 1;
260 l_api_name CONSTANT VARCHAR2(30) := 'update_fmaconstraints';
261 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
262 l_valid BOOLEAN;
263 l_fmav_rec fmav_rec_type;
264 l_fodv_rec fodv_rec_type;
265 BEGIN
266 x_return_status := OKC_API.G_RET_STS_SUCCESS;
267 l_return_status := OKC_API.START_ACTIVITY(p_api_name => l_api_name,
268 p_pkg_name => G_PKG_NAME,
269 p_init_msg_list => p_init_msg_list,
270 l_api_version => l_api_version,
271 p_api_version => p_api_version,
272 p_api_type => '_PVT',
273 x_return_status => l_return_status);
274 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
275 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
276 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
277 RAISE OKC_API.G_EXCEPTION_ERROR;
278 END IF;
279
280 l_fmav_rec := p_fmav_rec;
281 l_fodv_rec := p_fodv_rec;
282
283 /* call check_operands to check the validity of this relationship */
284 check_operands(p_fmav_rec => l_fmav_rec,
285 p_fodv_rec => l_fodv_rec,
286 x_return_status => l_return_status,
287 x_valid => l_valid);
288 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
289 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
290 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
291 (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
292 l_valid <> TRUE) THEN
293 x_return_status := OKL_API.G_RET_STS_ERROR;
294 RAISE OKC_API.G_EXCEPTION_ERROR;
295 END IF;
296
297 /* public api to insert formulae constraints */
298 -- Start of wraper code generated automatically by Debug code generator for okl_fmla_oprnds_pub.update_fmla_oprnds
299 IF(L_DEBUG_ENABLED='Y') THEN
300 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
301 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
302 END IF;
303 IF(IS_DEBUG_PROCEDURE_ON) THEN
304 BEGIN
305 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSFCB.pls call okl_fmla_oprnds_pub.update_fmla_oprnds ');
306 END;
307 END IF;
308 okl_fmla_oprnds_pub.update_fmla_oprnds(p_api_version => p_api_version,
309 p_init_msg_list => p_init_msg_list,
310 x_return_status => l_return_status,
311 x_msg_count => x_msg_count,
312 x_msg_data => x_msg_data,
313 p_fodv_rec => l_fodv_rec,
314 x_fodv_rec => x_fodv_rec);
315 IF(IS_DEBUG_PROCEDURE_ON) THEN
316 BEGIN
317 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSFCB.pls call okl_fmla_oprnds_pub.update_fmla_oprnds ');
318 END;
319 END IF;
320 -- End of wraper code generated automatically by Debug code generator for okl_fmla_oprnds_pub.update_fmla_oprnds
321
322 IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
323 RAISE OKC_API.G_EXCEPTION_ERROR;
324 ELSIF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
325 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
326 END IF;
327
328 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
329 x_msg_data => x_msg_data);
330 EXCEPTION
331 WHEN OKC_API.G_EXCEPTION_ERROR THEN
332 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
333 p_pkg_name => G_PKG_NAME,
334 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
335 x_msg_count => x_msg_count,
336 x_msg_data => x_msg_data,
337 p_api_type => '_PVT');
338 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
339 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
340 p_pkg_name => G_PKG_NAME,
341 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
342 x_msg_count => x_msg_count,
343 x_msg_data => x_msg_data,
344 p_api_type => '_PVT');
345 WHEN OTHERS THEN
346 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
347 p_pkg_name => G_PKG_NAME,
348 p_exc_name => 'OTHERS',
349 x_msg_count => x_msg_count,
350 x_msg_data => x_msg_data,
351 p_api_type => '_PVT');
352
353 END update_fmaconstraints;
354
355
356 ---------------------------------------------------------------------------
357 -- PROCEDURE delete_fmaconstraints for: OKL_FMLA_OPRNDS_V
358 -- This allows the user to delete table of records
359 ---------------------------------------------------------------------------
360 PROCEDURE delete_fmaconstraints(
361 p_api_version IN NUMBER
362 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
363 ,x_return_status OUT NOCOPY VARCHAR2
364 ,x_msg_count OUT NOCOPY NUMBER
365 ,x_msg_data OUT NOCOPY VARCHAR2
366 ,p_fodv_tbl IN fodv_tbl_type
367 ) IS
368 l_api_version CONSTANT NUMBER := 1;
369 l_fodv_tbl fodv_tbl_type;
370 l_api_name CONSTANT VARCHAR2(30) := 'delete_fmaconstraints';
371 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
372
373 BEGIN
374 x_return_status := OKC_API.G_RET_STS_SUCCESS;
375 l_return_status := OKC_API.START_ACTIVITY(p_api_name => l_api_name,
376 p_pkg_name => G_PKG_NAME,
377 p_init_msg_list => p_init_msg_list,
378 l_api_version => l_api_version,
379 p_api_version => p_api_version,
380 p_api_type => '_PVT',
381 x_return_status => l_return_status);
382 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
383 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
384 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
385 RAISE OKC_API.G_EXCEPTION_ERROR;
386 END IF;
387
388 l_fodv_tbl := p_fodv_tbl;
389
390 /* delete formulae constraints */
391 -- Start of wraper code generated automatically by Debug code generator for okl_fmla_oprnds_pub.delete_fmla_oprnds
392 IF(L_DEBUG_ENABLED='Y') THEN
393 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
394 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
395 END IF;
396 IF(IS_DEBUG_PROCEDURE_ON) THEN
397 BEGIN
398 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSFCB.pls call okl_fmla_oprnds_pub.delete_fmla_oprnds ');
399 END;
400 END IF;
401 okl_fmla_oprnds_pub.delete_fmla_oprnds(p_api_version => p_api_version,
402 p_init_msg_list => p_init_msg_list,
403 x_return_status => l_return_status,
404 x_msg_count => x_msg_count,
405 x_msg_data => x_msg_data,
406 p_fodv_tbl => l_fodv_tbl);
407 IF(IS_DEBUG_PROCEDURE_ON) THEN
408 BEGIN
409 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSFCB.pls call okl_fmla_oprnds_pub.delete_fmla_oprnds ');
410 END;
411 END IF;
412 -- End of wraper code generated automatically by Debug code generator for okl_fmla_oprnds_pub.delete_fmla_oprnds
413
414 IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
415 RAISE OKC_API.G_EXCEPTION_ERROR;
416 ELSIF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
417 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
418 END IF;
419
420 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
421 x_msg_data => x_msg_data);
422 EXCEPTION
423 WHEN OKC_API.G_EXCEPTION_ERROR THEN
424 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
425 p_pkg_name => G_PKG_NAME,
426 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
427 x_msg_count => x_msg_count,
428 x_msg_data => x_msg_data,
429 p_api_type => '_PVT');
430 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
431 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
432 p_pkg_name => G_PKG_NAME,
433 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
434 x_msg_count => x_msg_count,
435 x_msg_data => x_msg_data,
436 p_api_type => '_PVT');
437 WHEN OTHERS THEN
438 x_return_status := OKC_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
439 p_pkg_name => G_PKG_NAME,
440 p_exc_name => 'OTHERS',
441 x_msg_count => x_msg_count,
442 x_msg_data => x_msg_data,
443 p_api_type => '_PVT');
444
445 END delete_fmaconstraints;
446
447 -- rirawat 03-Feb-05 4149748: Added the following procedure to insert into OKL_FMLA_OPRNDS - TBL : begin
448
449 PROCEDURE insert_fmaconstraints(
450 p_api_version IN NUMBER
451 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
452 ,x_return_status OUT NOCOPY VARCHAR2
453 ,x_msg_count OUT NOCOPY NUMBER
454 ,x_msg_data OUT NOCOPY VARCHAR2
455 ,p_fmav_rec IN fmav_rec_type
456 ,p_fodv_tbl IN fodv_tbl_type
457 ,x_fodv_tbl OUT NOCOPY fodv_tbl_type
458 ) IS
459 l_fodv_tbl fodv_tbl_type;
460 l_data VARCHAR2(100);
461 l_count NUMBER ;
462 l_api_name CONSTANT VARCHAR2(30) := 'insert_fmaconstraints';
463 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
464 l_overall_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
465 i NUMBER;
466
467 BEGIN
468 x_return_status := FND_API.G_RET_STS_SUCCESS;
469 SAVEPOINT insert_fmaconstraints;
470 l_fodv_tbl := p_fodv_tbl;
471
472
473
474 IF (p_fodv_tbl.COUNT > 0) THEN
475 i := p_fodv_tbl.FIRST;
476
477 LOOP
478 insert_fmaconstraints (
479 p_api_version => p_api_version
480 ,p_init_msg_list => p_init_msg_list
481 ,x_return_status => x_return_status
482 ,x_msg_count => x_msg_count
483 ,x_msg_data => x_msg_data
484 ,p_fmav_rec => p_fmav_rec
485 ,p_fodv_rec => p_fodv_tbl(i)
486 ,x_fodv_rec => x_fodv_tbl(i)
487 );
488
489 -- store the highest degree of error
490 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
491 IF l_overall_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
492 l_overall_status := x_return_status;
493 END IF;
494 END IF;
495 EXIT WHEN (i = p_fodv_tbl.LAST);
496
497 i := p_fodv_tbl.NEXT(i);
498
499 END LOOP;
500
501 -- return overall status
502 x_return_status := l_overall_status;
503 END IF;
504
505 l_return_status := x_return_status;
506
507 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
508 RAISE FND_API.G_EXC_ERROR;
509 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
510 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
511 END IF;
512
513 /* re-assign local table structure using output table from pvt api */
514 l_fodv_tbl := x_fodv_tbl;
515
516 EXCEPTION
517 WHEN FND_API.G_EXC_ERROR THEN
518 ROLLBACK TO insert_fmaconstraints;
519 x_return_status := FND_API.G_RET_STS_ERROR;
520
521 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
522 ,p_count => x_msg_count
523 ,p_data => x_msg_data);
524
525 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
526 ROLLBACK TO insert_fmaconstraints;
527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
528 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
529 ,p_count => x_msg_count
530 ,p_data => x_msg_data);
531
532 WHEN OTHERS THEN
533 FND_MSG_PUB.ADD_EXC_MSG('OKL_fmaconstraints_PUB','insert_fmaconstraints');
534 -- store SQL error message on message stack for caller
535 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
536 ,p_count => x_msg_count
537 ,p_data => x_msg_data);
538 -- notify caller of an UNEXPECTED error
539 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
540 END insert_fmaconstraints;
541 -- rirawat 03-Feb-05 4149748: Added the following procedure to insert into OKL_FMLA_OPRNDS - TBL : end
542
543 -- rirawat 03-Feb-05 4149748: Added the following procedure to update into OKL_FMLA_OPRNDS - TBL : begin
544 PROCEDURE update_fmaconstraints(
545 p_api_version IN NUMBER
546 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
547 ,x_return_status OUT NOCOPY VARCHAR2
548 ,x_msg_count OUT NOCOPY NUMBER
549 ,x_msg_data OUT NOCOPY VARCHAR2
550 ,p_fmav_rec IN fmav_rec_type
551 ,p_fodv_tbl IN fodv_tbl_type
552 ,x_fodv_tbl OUT NOCOPY fodv_tbl_type
553 ) IS
554 l_fodv_tbl fodv_tbl_type;
555 l_data VARCHAR2(100);
556 l_count NUMBER ;
557 l_api_name CONSTANT VARCHAR2(30) := 'update_fmaconstraints';
558 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
559 l_overall_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
560 i NUMBER;
561
562 BEGIN
563 x_return_status := FND_API.G_RET_STS_SUCCESS;
564 SAVEPOINT update_fmaconstraints;
565 l_fodv_tbl := p_fodv_tbl;
566
567
568
569 IF (p_fodv_tbl.COUNT > 0) THEN
570 i := p_fodv_tbl.FIRST;
571
572 LOOP
573 update_fmaconstraints (
574 p_api_version => p_api_version
575 --start:| 20-Nov-08 cklee/smadhava Bug# 7439737/7298457
576 -- ,p_init_msg_list => p_init_msg_list
577 -- Bug# 7298457 - Donot reset message list- passing 'T' overwrites the error messages of previous operands
578 ,p_init_msg_list => OKL_API.G_FALSE
579 --end:| 20-Nov-08 cklee/smadhava Bug# 7439737/7298457
580 ,x_return_status => x_return_status
581 ,x_msg_count => x_msg_count
582 ,x_msg_data => x_msg_data
583 ,p_fmav_rec => p_fmav_rec
584 ,p_fodv_rec => p_fodv_tbl(i)
585 ,x_fodv_rec => x_fodv_tbl(i)
586 );
587
588 -- store the highest degree of error
589 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
590 IF l_overall_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
591 l_overall_status := x_return_status;
592 END IF;
593 END IF;
594 EXIT WHEN (i = p_fodv_tbl.LAST);
595
596 i := p_fodv_tbl.NEXT(i);
597
598 END LOOP;
599
600 -- return overall status
601 x_return_status := l_overall_status;
602 END IF;
603
604 l_return_status := x_return_status;
605
606 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
607 RAISE FND_API.G_EXC_ERROR;
608 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
609 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
610 END IF;
611
612 /* re-assign local table structure using output table from pvt api */
613 l_fodv_tbl := x_fodv_tbl;
614
615
616
617 EXCEPTION
618 WHEN FND_API.G_EXC_ERROR THEN
619 ROLLBACK TO update_fmaconstraints;
620 x_return_status := FND_API.G_RET_STS_ERROR;
621
622 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
623 ,p_count => x_msg_count
624 ,p_data => x_msg_data);
625
626 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
627 ROLLBACK TO update_fmaconstraints;
628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
629 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
630 ,p_count => x_msg_count
631 ,p_data => x_msg_data);
632
633 WHEN OTHERS THEN
634 FND_MSG_PUB.ADD_EXC_MSG('OKL_fmaconstraints_PUB','update_fmaconstraints');
635 -- store SQL error message on message stack for caller
636 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
637 ,p_count => x_msg_count
638 ,p_data => x_msg_data);
639 -- notify caller of an UNEXPECTED error
640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
641 END update_fmaconstraints;
642 -- rirawat 03-Feb-05 4149748: Added the following procedure to update into OKL_FMLA_OPRNDS - TBL : end
643
644
645 END OKL_SETUPFMACONSTRAINTS_PVT;