[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUP_PRD_PRCTEMPL_PUB
Source
1 PACKAGE BODY OKL_SETUP_PRD_PRCTEMPL_PUB AS
2 /* $Header: OKLPPPEB.pls 115.5 2004/04/13 10:58:04 rnaik noship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE insert_prd_price_tmpls for: OKL_PRD_PRC_TMPLS_V
6 -- Public Wrapper for insert_prd_price_tmpls Process API
7 ---------------------------------------------------------------------------
8 PROCEDURE insert_prd_price_tmpls( p_api_version IN NUMBER,
9 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_msg_count OUT NOCOPY NUMBER,
12 x_msg_data OUT NOCOPY VARCHAR2,
13 p_pitv_rec IN pitv_rec_type,
14 x_pitv_rec OUT NOCOPY pitv_rec_type
15 ) IS
16 l_api_name CONSTANT VARCHAR2(30) := 'insert_prd_price_tmpls';
17 l_api_version CONSTANT NUMBER := 1;
18 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
19 l_pitv_rec pitv_rec_type;
20 l_data VARCHAR2(100);
21 l_count NUMBER ;
22
23 BEGIN
24 l_return_status := G_RET_STS_SUCCESS;
25
26 l_pitv_rec := p_pitv_rec;
27 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
28 p_pkg_name => G_PKG_NAME,
29 p_init_msg_list => p_init_msg_list,
30 l_api_version => l_api_version,
31 p_api_version => p_api_version,
32 p_api_type => G_API_TYPE,
33 x_return_status => l_return_status);
34 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
35 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
36 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
37 RAISE G_EXCEPTION_ERROR;
38 END IF;
39
40
41
42 -- call process api to insert_prd_price_tmpls
43 OKL_SETUP_PRD_PRCTEMPL_PVT.insert_prd_price_tmpls(p_api_version => p_api_version,
44 p_init_msg_list => p_init_msg_list,
45 x_return_status => l_return_status,
46 x_msg_count => x_msg_count,
47 x_msg_data => x_msg_data,
48 p_pitv_rec => l_pitv_rec,
49 x_pitv_rec => x_pitv_rec);
50
51 IF l_return_status = G_RET_STS_ERROR THEN
52 RAISE G_EXCEPTION_ERROR;
53 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
54 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
55 END IF;
56
57 /* re-assign local record structure using output record from pvt api */
58 l_pitv_rec := x_pitv_rec;
59
60
61 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
62 x_msg_data => x_msg_data);
63 x_return_status := l_return_status;
64 EXCEPTION
65 WHEN G_EXCEPTION_ERROR THEN
66 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
67 p_pkg_name => G_PKG_NAME,
68 p_exc_name => G_EXC_NAME_ERROR,
69 x_msg_count => x_msg_count,
70 x_msg_data => x_msg_data,
71 p_api_type => G_API_TYPE);
72 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
73 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
74 p_pkg_name => G_PKG_NAME,
75 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
76 x_msg_count => x_msg_count,
77 x_msg_data => x_msg_data,
78 p_api_type => G_API_TYPE);
79 WHEN OTHERS THEN
80 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
81 p_pkg_name => G_PKG_NAME,
82 p_exc_name => G_EXC_NAME_OTHERS,
83 x_msg_count => x_msg_count,
84 x_msg_data => x_msg_data,
85 p_api_type => G_API_TYPE);
86 END insert_prd_price_tmpls;
87
88 ---------------------------------------------------------------------------
89 -- PROCEDURE update_prd_price_tmpls for: OKL_PRD_PRC_TMPLS_V
90 -- Public Wrapper for Process API
91 ---------------------------------------------------------------------------
92 PROCEDURE update_prd_price_tmpls(p_api_version IN NUMBER,
93 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
94 x_return_status OUT NOCOPY VARCHAR2,
95 x_msg_count OUT NOCOPY NUMBER,
96 x_msg_data OUT NOCOPY VARCHAR2,
97 p_pitv_rec IN pitv_rec_type,
98 x_pitv_rec OUT NOCOPY pitv_rec_type
99 ) IS
100
101 l_api_name CONSTANT VARCHAR2(30) := 'update_prd_price_tmpls';
102 l_api_version CONSTANT NUMBER := 1;
103 l_pitv_rec pitv_rec_type;
104 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
105 l_data VARCHAR2(100);
106 l_count NUMBER ;
107
108 BEGIN
109 l_return_status := G_RET_STS_SUCCESS;
110
111 l_pitv_rec := p_pitv_rec;
112
113 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
114 p_pkg_name => G_PKG_NAME,
115 p_init_msg_list => p_init_msg_list,
116 l_api_version => l_api_version,
117 p_api_version => p_api_version,
118 p_api_type => G_API_TYPE,
119 x_return_status => l_return_status);
120 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
121 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
122 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
123 RAISE G_EXCEPTION_ERROR;
124 END IF;
125
126
127
128 -- call process api to update formulae
129 OKL_SETUP_PRD_PRCTEMPL_PVT.update_prd_price_tmpls(p_api_version => p_api_version,
130 p_init_msg_list => p_init_msg_list,
131 x_return_status => l_return_status,
132 x_msg_count => x_msg_count,
133 x_msg_data => x_msg_data,
134 p_pitv_rec => l_pitv_rec,
135 x_pitv_rec => x_pitv_rec);
136
137 IF l_return_status = G_RET_STS_ERROR THEN
138 RAISE G_EXCEPTION_ERROR;
139 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
140 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
141 END IF;
142
143 /* re-assign local record structure using output record from pvt api */
144 l_pitv_rec := x_pitv_rec;
145
146
147 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
148 x_msg_data => x_msg_data);
149 x_return_status := l_return_status;
150 EXCEPTION
151 WHEN G_EXCEPTION_ERROR THEN
152 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
153 p_pkg_name => G_PKG_NAME,
154 p_exc_name => G_EXC_NAME_ERROR,
155 x_msg_count => x_msg_count,
156 x_msg_data => x_msg_data,
157 p_api_type => G_API_TYPE);
158 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
159 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
160 p_pkg_name => G_PKG_NAME,
161 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
162 x_msg_count => x_msg_count,
163 x_msg_data => x_msg_data,
164 p_api_type => G_API_TYPE);
165 WHEN OTHERS THEN
166 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
167 p_pkg_name => G_PKG_NAME,
168 p_exc_name => G_EXC_NAME_OTHERS,
169 x_msg_count => x_msg_count,
170 x_msg_data => x_msg_data,
171 p_api_type => G_API_TYPE);
172 END update_prd_price_tmpls;
173
174 PROCEDURE insert_prd_price_tmpls(
175 p_api_version IN NUMBER,
176 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
177 x_return_status OUT NOCOPY VARCHAR2,
178 x_msg_count OUT NOCOPY NUMBER,
179 x_msg_data OUT NOCOPY VARCHAR2,
180 p_pitv_tbl IN pitv_tbl_type,
181 x_pitv_tbl OUT NOCOPY pitv_tbl_type)
182 IS
183 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
184 l_api_name CONSTANT VARCHAR2(30) := 'insert_prd_price_tmpls_tbl';
185 rec_num INTEGER := 0;
186 l_api_version CONSTANT NUMBER := 1;
187 BEGIN
188
189 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
190 p_pkg_name => G_PKG_NAME,
191 p_init_msg_list => p_init_msg_list,
192 l_api_version => l_api_version,
193 p_api_version => p_api_version,
194 p_api_type => G_API_TYPE,
195 x_return_status => l_return_status);
196 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
197 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
198 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
199 RAISE G_EXCEPTION_ERROR;
200 END IF;
201
202 FOR rec_num IN 1..p_pitv_tbl.COUNT
203 LOOP
204 insert_prd_price_tmpls(
205 p_api_version => p_api_version,
206 p_init_msg_list => p_init_msg_list,
207 x_return_status => l_return_status,
208 x_msg_count => x_msg_count,
209 x_msg_data => x_msg_data,
210 p_pitv_rec => p_pitv_tbl(rec_num),
211 x_pitv_rec => x_pitv_tbl(rec_num) );
212
213 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
214 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
215 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
216 RAISE G_EXCEPTION_ERROR;
217 END IF;
218 END LOOP;
219 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
220 x_msg_data => x_msg_data);
221 x_return_status := l_return_status;
222 EXCEPTION
223 WHEN G_EXCEPTION_ERROR THEN
224 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
225 p_pkg_name => G_PKG_NAME,
226 p_exc_name => G_EXC_NAME_ERROR,
227 x_msg_count => x_msg_count,
228 x_msg_data => x_msg_data,
229 p_api_type => G_API_TYPE);
230 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
231 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
232 p_pkg_name => G_PKG_NAME,
233 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
234 x_msg_count => x_msg_count,
235 x_msg_data => x_msg_data,
236 p_api_type => G_API_TYPE);
237 WHEN OTHERS THEN
238 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
239 p_pkg_name => G_PKG_NAME,
240 p_exc_name => G_EXC_NAME_OTHERS,
241 x_msg_count => x_msg_count,
242 x_msg_data => x_msg_data,
243 p_api_type => G_API_TYPE);
244
245 END insert_prd_price_tmpls;
246
247
248 PROCEDURE update_prd_price_tmpls(
249 p_api_version IN NUMBER,
250 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
251 x_return_status OUT NOCOPY VARCHAR2,
252 x_msg_count OUT NOCOPY NUMBER,
253 x_msg_data OUT NOCOPY VARCHAR2,
254 p_pitv_tbl IN pitv_tbl_type,
255 x_pitv_tbl OUT NOCOPY pitv_tbl_type)
256 IS
257 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
258 l_api_name CONSTANT VARCHAR2(30) := 'update_prd_price_tmpls_tbl';
259 rec_num INTEGER := 0;
260 l_api_version CONSTANT NUMBER := 1;
261 BEGIN
262
263 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
264 p_pkg_name => G_PKG_NAME,
265 p_init_msg_list => p_init_msg_list,
266 l_api_version => l_api_version,
267 p_api_version => p_api_version,
268 p_api_type => G_API_TYPE,
269 x_return_status => l_return_status);
270 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
271 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
272 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
273 RAISE G_EXCEPTION_ERROR;
274 END IF;
275
276 FOR rec_num IN 1..p_pitv_tbl.COUNT
277 LOOP
278 update_prd_price_tmpls(
279 p_api_version => p_api_version,
280 p_init_msg_list => p_init_msg_list,
281 x_return_status => l_return_status,
282 x_msg_count => x_msg_count,
283 x_msg_data => x_msg_data,
284 p_pitv_rec => p_pitv_tbl(rec_num),
285 x_pitv_rec => x_pitv_tbl(rec_num) );
286
287 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
288 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
289 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
290 RAISE G_EXCEPTION_ERROR;
291 END IF;
292
293 END LOOP;
294
295 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
296 x_msg_data => x_msg_data);
297 x_return_status := l_return_status;
298 EXCEPTION
299 WHEN G_EXCEPTION_ERROR THEN
300 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
301 p_pkg_name => G_PKG_NAME,
302 p_exc_name => G_EXC_NAME_ERROR,
303 x_msg_count => x_msg_count,
304 x_msg_data => x_msg_data,
305 p_api_type => G_API_TYPE);
306 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
307 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
308 p_pkg_name => G_PKG_NAME,
309 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
310 x_msg_count => x_msg_count,
311 x_msg_data => x_msg_data,
312 p_api_type => G_API_TYPE);
313 WHEN OTHERS THEN
314 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
315 p_pkg_name => G_PKG_NAME,
316 p_exc_name => G_EXC_NAME_OTHERS,
317 x_msg_count => x_msg_count,
318 x_msg_data => x_msg_data,
319 p_api_type => G_API_TYPE);
320
321 END update_prd_price_tmpls;
322
323 PROCEDURE check_product_constraints(
324 p_api_version IN NUMBER,
325 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
326 x_return_status OUT NOCOPY VARCHAR2,
327 x_msg_count OUT NOCOPY NUMBER,
328 x_msg_data OUT NOCOPY VARCHAR2,
329 p_pdtv_rec IN pdtv_rec_type,
330 x_validated OUT NOCOPY VARCHAR2)
331 IS
332 l_api_name CONSTANT VARCHAR2(30) := 'check_product_constraints';
333 l_api_version CONSTANT NUMBER := 1;
334 l_pdtv_rec pdtv_rec_type;
335 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
336 l_data VARCHAR2(100);
337 l_count NUMBER ;
338 l_validated VARCHAR2(1);
339 l_valid BOOLEAN := TRUE;
340 BEGIN
341
342
343 l_pdtv_rec := p_pdtv_rec;
344 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
345 p_pkg_name => G_PKG_NAME,
346 p_init_msg_list => p_init_msg_list,
347 l_api_version => l_api_version,
348 p_api_version => p_api_version,
349 p_api_type => G_API_TYPE,
350 x_return_status => l_return_status);
351 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
352 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
353 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
354 RAISE G_EXCEPTION_ERROR;
355 END IF;
356
357
358
359 -- call process api to update formulae
360 OKL_SETUP_PRD_PRCTEMPL_PVT.check_product_constraints(p_api_version => p_api_version,
361 p_init_msg_list => p_init_msg_list,
362 x_return_status => l_return_status,
363 x_msg_count => x_msg_count,
364 x_msg_data => x_msg_data,
365 p_pdtv_rec => l_pdtv_rec,
366 x_validated => l_validated);
367 l_valid := FND_API.TO_BOOLEAN(l_validated);
368 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
369 RAISE G_EXCEPTION_ERROR;
370 ELSIF (l_return_status = G_RET_STS_ERROR) OR
371 (l_return_status = G_RET_STS_SUCCESS AND
372 l_valid <> TRUE) THEN
373 RAISE G_EXCEPTION_ERROR;
374 END IF;
375
376
377 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
378 x_msg_data => x_msg_data);
379 x_return_status := l_return_status;
380 EXCEPTION
381 WHEN G_EXCEPTION_ERROR THEN
382 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
383 p_pkg_name => G_PKG_NAME,
384 p_exc_name => G_EXC_NAME_ERROR,
385 x_msg_count => x_msg_count,
386 x_msg_data => x_msg_data,
387 p_api_type => G_API_TYPE);
388 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
389 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
390 p_pkg_name => G_PKG_NAME,
391 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
392 x_msg_count => x_msg_count,
393 x_msg_data => x_msg_data,
394 p_api_type => G_API_TYPE);
395 WHEN OTHERS THEN
396 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
397 p_pkg_name => G_PKG_NAME,
398 p_exc_name => G_EXC_NAME_OTHERS,
399 x_msg_count => x_msg_count,
400 x_msg_data => x_msg_data,
401 p_api_type => G_API_TYPE);
402
403 END check_product_constraints;
404
405 PROCEDURE check_product_constraints(
406 p_api_version IN NUMBER,
407 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
408 x_return_status OUT NOCOPY VARCHAR2,
409 x_msg_count OUT NOCOPY NUMBER,
410 x_msg_data OUT NOCOPY VARCHAR2,
411 p_pdtv_tbl IN pdtv_tbl_type,
412 x_validated OUT NOCOPY VARCHAR2)
413 IS
414 l_api_name CONSTANT VARCHAR2(30) := 'check_product_constraints_tbl';
415 rec_num INTEGER := 0;
416 l_validated VARCHAR2(1);
417 l_valid BOOLEAN := TRUE;
418 l_api_version CONSTANT NUMBER := 1;
419 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
420 BEGIN
421
422 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
423 p_pkg_name => G_PKG_NAME,
424 p_init_msg_list => p_init_msg_list,
425 l_api_version => l_api_version,
426 p_api_version => p_api_version,
427 p_api_type => G_API_TYPE,
428 x_return_status => l_return_status);
429 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
430 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
431 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
432 RAISE G_EXCEPTION_ERROR;
433 END IF;
434
435 FOR rec_num IN 1..p_pdtv_tbl.COUNT
436 LOOP
437 check_product_constraints(
438 p_api_version => p_api_version,
439 p_init_msg_list => p_init_msg_list,
440 x_return_status => l_return_status,
441 x_msg_count => x_msg_count,
442 x_msg_data => x_msg_data,
443 p_pdtv_rec => p_pdtv_tbl(rec_num),
444 x_validated => l_validated );
445 l_valid := FND_API.TO_BOOLEAN(l_validated);
446 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
447 RAISE G_EXCEPTION_ERROR;
448 ELSIF (l_return_status = G_RET_STS_ERROR) OR
449 (l_return_status = G_RET_STS_SUCCESS AND
450 l_valid <> TRUE) THEN
451 RAISE G_EXCEPTION_ERROR;
452 END IF;
453
454 END LOOP;
455 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
456 x_msg_data => x_msg_data);
457 x_return_status := l_return_status;
458 EXCEPTION
459 WHEN G_EXCEPTION_ERROR THEN
460 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
461 p_pkg_name => G_PKG_NAME,
462 p_exc_name => G_EXC_NAME_ERROR,
463 x_msg_count => x_msg_count,
464 x_msg_data => x_msg_data,
465 p_api_type => G_API_TYPE);
466 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
467 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
468 p_pkg_name => G_PKG_NAME,
469 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
470 x_msg_count => x_msg_count,
471 x_msg_data => x_msg_data,
472 p_api_type => G_API_TYPE);
473 WHEN OTHERS THEN
474 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
475 p_pkg_name => G_PKG_NAME,
476 p_exc_name => G_EXC_NAME_OTHERS,
477 x_msg_count => x_msg_count,
478 x_msg_data => x_msg_data,
479 p_api_type => G_API_TYPE);
480
481 END check_product_constraints;
482
483 END OKL_SETUP_PRD_PRCTEMPL_PUB;