[Home] [Help]
PACKAGE BODY: APPS.OKL_POOL_PUB
Source
1 PACKAGE BODY OKL_POOL_PUB AS
2 /* $Header: OKLPSZPB.pls 120.0 2007/07/27 14:03:02 ankushar noship $ */
3
4 PROCEDURE create_pool(
5 p_api_version IN NUMBER
6 ,p_init_msg_list IN VARCHAR2
7 ,x_return_status OUT NOCOPY VARCHAR2
8 ,x_msg_count OUT NOCOPY NUMBER
9 ,x_msg_data OUT NOCOPY VARCHAR2
10 ,p_polv_rec IN polv_rec_type
11 ,x_polv_rec OUT NOCOPY polv_rec_type
12 ) IS
13
14 l_api_name CONSTANT VARCHAR2(30) := 'create_pool';
15 l_api_version NUMBER ;
16 l_init_msg_list VARCHAR2(1) ;
17 l_return_status VARCHAR2(1);
18 l_msg_count NUMBER ;
19 l_msg_data VARCHAR2(2000);
20
21 l_polv_rec polv_rec_type ;
22 lx_polv_rec polv_rec_type ;
23
24 BEGIN
25 -- Set API savepoint
26 SAVEPOINT trx_create_pool;
27
28 x_return_status := OKL_API.G_RET_STS_SUCCESS;
29
30 l_api_version := p_api_version ;
31 l_init_msg_list := p_init_msg_list ;
32 l_return_status := x_return_status ;
33 l_msg_count := x_msg_count ;
34 l_msg_data := x_msg_data ;
35 l_polv_rec := p_polv_rec;
36
37 Okl_Pool_Pvt.create_pool(
38 p_api_version => l_api_version,
39 p_init_msg_list => l_init_msg_list,
40 x_return_status => l_return_status,
41 x_msg_count => l_msg_count,
42 x_msg_data => l_msg_data,
43 p_polv_rec => l_polv_rec,
44 x_polv_rec => lx_polv_rec);
45
46 IF ( l_return_status = OKL_API.G_RET_STS_ERROR ) THEN
47 RAISE OKL_API.G_EXCEPTION_ERROR;
48 ELSIF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR ) THEN
49 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
50 END IF;
51
52 x_return_status := l_return_status ;
53 x_msg_count := l_msg_count ;
54 x_msg_data := l_msg_data ;
55 x_polv_rec := lx_polv_rec;
56
57
58 EXCEPTION
59 WHEN OKL_API.G_EXCEPTION_ERROR THEN
60 ROLLBACK TO trx_create_pool;
61 x_return_status := OKL_API.G_RET_STS_ERROR;
62 x_msg_count := l_msg_count ;
63 x_msg_data := l_msg_data ;
64 FND_MSG_PUB.count_and_get(
65 p_count => x_msg_count
66 ,p_data => x_msg_data);
67 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
68 ROLLBACK TO trx_create_pool;
69 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
70 x_msg_count := l_msg_count ;
71 x_msg_data := l_msg_data ;
72 FND_MSG_PUB.count_and_get(
73 p_count => x_msg_count
74 ,p_data => x_msg_data);
75 WHEN OTHERS THEN
76 ROLLBACK TO trx_create_pool;
77 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
78 x_msg_count := l_msg_count ;
79 x_msg_data := l_msg_data ;
80 FND_MSG_PUB.ADD_EXC_MSG('OKL_POOL_PUB','create_pool');
81 FND_MSG_PUB.count_and_get(
82 p_count => x_msg_count
83 ,p_data => x_msg_data);
84
85
86 END create_pool;
87
88 PROCEDURE update_pool(
89 p_api_version IN NUMBER
90 ,p_init_msg_list IN VARCHAR2
91 ,x_return_status OUT NOCOPY VARCHAR2
92 ,x_msg_count OUT NOCOPY NUMBER
93 ,x_msg_data OUT NOCOPY VARCHAR2
94 ,p_polv_rec IN polv_rec_type
95 ,x_polv_rec OUT NOCOPY polv_rec_type
96 ) IS
97
98 l_api_version NUMBER ;
99 l_init_msg_list VARCHAR2(1) ;
100 l_return_status VARCHAR2(1);
101 l_msg_count NUMBER ;
102 l_msg_data VARCHAR2(2000);
103
104 l_polv_rec polv_rec_type ;
105 lx_polv_rec polv_rec_type ;
106 l_api_name CONSTANT VARCHAR2(30) := 'update_pool';
107
108 BEGIN
109 -- Set API savepoint
110 SAVEPOINT trx_update_pool;
111
112 l_api_version := p_api_version ;
113 l_init_msg_list := p_init_msg_list ;
114 l_return_status := x_return_status ;
115 l_msg_count := x_msg_count ;
116 l_msg_data := x_msg_data ;
117
118 l_polv_rec.id := p_polv_rec.id;
119 l_polv_rec.description := p_polv_rec.description ;
120 l_polv_rec.short_description := p_polv_rec.short_description;
121 l_polv_rec.display_in_lease_center := p_polv_rec.display_in_lease_center ;
122
123
124 OKL_POOL_PVT.validate_pool(
125 p_api_version => l_api_version,
126 p_init_msg_list => l_init_msg_list,
127 p_api_name => l_api_name,
128 p_polv_rec => l_polv_rec,
129 p_action => 'update_pool',
130 x_return_status => l_return_status,
131 x_msg_count => l_msg_count,
132 x_msg_data => l_msg_data
133 );
134
135 IF ( l_return_status = OKL_API.G_RET_STS_ERROR ) THEN
136 RAISE OKL_API.G_EXCEPTION_ERROR;
137 ELSIF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR ) THEN
138 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
139 END IF;
140
141 Okl_Pool_Pvt.update_pool(
142 p_api_version => l_api_version,
143 p_init_msg_list => l_init_msg_list,
144 x_return_status => l_return_status,
145 x_msg_count => l_msg_count,
146 x_msg_data => l_msg_data,
147 p_polv_rec => l_polv_rec,
148 x_polv_rec => lx_polv_rec);
149
150 IF ( l_return_status = OKL_API.G_RET_STS_ERROR ) THEN
151 RAISE OKL_API.G_EXCEPTION_ERROR;
152 ELSIF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR ) THEN
153 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
154 END IF;
155
156 --Assign value to OUT variables
157 x_return_status := l_return_status ;
158 x_msg_count := l_msg_count ;
159 x_msg_data := l_msg_data ;
160 x_polv_rec := lx_polv_rec;
161
162
163 EXCEPTION
164 WHEN OKL_API.G_EXCEPTION_ERROR THEN
165 ROLLBACK TO trx_update_pool;
166 x_return_status := OKL_API.G_RET_STS_ERROR;
167 x_msg_count := l_msg_count ;
168 x_msg_data := l_msg_data ;
169 FND_MSG_PUB.count_and_get(
170 p_count => x_msg_count
171 ,p_data => x_msg_data);
172 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
173 ROLLBACK TO trx_update_pool;
174 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
175 x_msg_count := l_msg_count ;
176 x_msg_data := l_msg_data ;
177 FND_MSG_PUB.count_and_get(
178 p_count => x_msg_count
179 ,p_data => x_msg_data);
180 WHEN OTHERS THEN
181 ROLLBACK TO trx_update_pool;
182 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
183 x_msg_count := l_msg_count ;
184 x_msg_data := l_msg_data ;
185 FND_MSG_PUB.ADD_EXC_MSG('OKL_POOL_PUB','update_pool');
186 FND_MSG_PUB.count_and_get(
187 p_count => x_msg_count
188 ,p_data => x_msg_data);
189
190 END update_pool;
191
192
193 PROCEDURE cleanup_pool_contents(
194 p_api_version IN NUMBER
195 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
196 ,x_return_status OUT NOCOPY VARCHAR2
197 ,x_msg_count OUT NOCOPY NUMBER
198 ,x_msg_data OUT NOCOPY VARCHAR2
199 ,p_pol_id IN NUMBER
200 ,p_currency_code IN VARCHAR2
201 ,p_cust_object1_id1 IN NUMBER DEFAULT NULL
202 ,p_sic_code IN VARCHAR2 DEFAULT NULL
203 ,p_khr_id IN NUMBER DEFAULT NULL
204 ,p_pre_tax_yield_from IN NUMBER DEFAULT NULL
205 ,p_pre_tax_yield_to IN NUMBER DEFAULT NULL
206 ,p_book_classification IN VARCHAR2 DEFAULT NULL
207 ,p_tax_owner IN VARCHAR2 DEFAULT NULL
208 ,p_pdt_id IN NUMBER DEFAULT NULL
209 ,p_start_from_date IN DATE DEFAULT NULL
210 ,p_start_to_date IN DATE DEFAULT NULL
211 ,p_end_from_date IN DATE DEFAULT NULL
212 ,p_end_to_date IN DATE DEFAULT NULL
213 ,p_stream_type_subclass IN VARCHAR2 DEFAULT NULL
214 ,p_streams_from_date IN DATE DEFAULT NULL
215 ,p_streams_to_date IN DATE DEFAULT NULL
216 ,x_poc_uv_tbl OUT NOCOPY poc_uv_tbl_type) IS
217
218
219 lx_poc_uv_tbl poc_uv_tbl_type;
220 l_api_version NUMBER ;
221 l_init_msg_list VARCHAR2(1) ;
222 l_return_status VARCHAR2(1) ;
223 l_msg_count NUMBER;
224
225 l_msg_data VARCHAR2(2000);
226 l_api_name CONSTANT VARCHAR2(40) := 'cleanup_pool_contents';
227 l_polv_rec polv_rec_type ;
228 lx_polv_rec polv_rec_type ;
229
230 BEGIN
231 -- Set API savepoint
232 SAVEPOINT trx_cleanup_pool_contents;
233
234 l_api_version := p_api_version ;
235 l_init_msg_list := p_init_msg_list ;
236 l_return_status := x_return_status ;
237 l_msg_count := x_msg_count ;
238 l_msg_data := x_msg_data ;
239
240 l_polv_rec.id := p_pol_id;
241
242 OKL_POOL_PVT.validate_pool(
243 p_api_version => l_api_version,
244 p_init_msg_list => l_init_msg_list,
245 p_api_name => l_api_name,
246 p_polv_rec => l_polv_rec,
247 p_action => 'cleanup_pool_contents',
248 x_return_status => l_return_status,
249 x_msg_count => l_msg_count,
250 x_msg_data => l_msg_data
251 );
252
253 IF ( l_return_status = OKL_API.G_RET_STS_ERROR ) THEN
254 RAISE OKL_API.G_EXCEPTION_ERROR;
255 ELSIF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR ) THEN
256 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
257 END IF;
258
259
260 Okl_Pool_Pvt.cleanup_pool_contents( p_api_version => l_api_version
261 ,p_init_msg_list => l_init_msg_list
262 ,x_return_status => l_return_status
263 ,x_msg_count => l_msg_count
264 ,x_msg_data => l_msg_data
265 ,p_multi_org => NULL
266 ,p_currency_code => NULL
267 ,p_pol_id => p_pol_id
268 ,p_cust_object1_id1 => p_cust_object1_id1
269 ,p_sic_code => p_sic_code
270 ,p_dnz_chr_id => p_khr_id
271 ,p_pre_tax_yield_from => p_pre_tax_yield_from
272 ,p_pre_tax_yield_to => p_pre_tax_yield_to
273 ,p_book_classification => p_book_classification
274 ,p_tax_owner => p_tax_owner
275 ,p_pdt_id => p_pdt_id
276 ,p_start_from_date => p_start_from_date
277 ,p_start_to_date => p_start_to_date
278 ,p_end_from_date => p_end_from_date
279 ,p_end_to_date => p_end_to_date
280 ,p_asset_id => NULL
281 ,p_item_id1 => NULL
282 ,p_model_number => NULL
283 ,p_manufacturer_name => NULL
284 ,p_vendor_id1 => NULL
285 ,p_oec_from => NULL
286 ,p_oec_to => NULL
287 ,p_residual_percentage => NULL
288 ,p_sty_id => NULL
289 ,p_stream_type_subclass => p_stream_type_subclass
290 ,p_streams_from_date => p_streams_from_date
291 ,p_streams_to_date => p_streams_to_date
292 ,p_action_code => Okl_Pool_Pvt.G_ACTION_REMOVE
293 ,x_poc_uv_tbl => lx_poc_uv_tbl);
294
295 IF ( l_return_status = OKL_API.G_RET_STS_ERROR ) THEN
296 RAISE OKL_API.G_EXCEPTION_ERROR;
297 ELSIF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR ) THEN
298 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
299 END IF;
300
301
302 --Assign value to OUT variables
303 x_return_status := l_return_status ;
304 x_msg_count := l_msg_count ;
305 x_msg_data := l_msg_data ;
306 x_poc_uv_tbl := lx_poc_uv_tbl;
307
308
309 EXCEPTION
310 WHEN OKL_API.G_EXCEPTION_ERROR THEN
311 ROLLBACK TO trx_cleanup_pool_contents;
312 x_return_status := OKL_API.G_RET_STS_ERROR;
313 x_msg_count := l_msg_count ;
314 x_msg_data := l_msg_data ;
315 FND_MSG_PUB.count_and_get(
316 p_count => x_msg_count
317 ,p_data => x_msg_data);
318 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
319 ROLLBACK TO trx_cleanup_pool_contents;
320 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
321 x_msg_count := l_msg_count ;
322 x_msg_data := l_msg_data ;
323 FND_MSG_PUB.count_and_get(
324 p_count => x_msg_count
325 ,p_data => x_msg_data);
326 WHEN OTHERS THEN
327 ROLLBACK TO trx_cleanup_pool_contents;
328 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
329 x_msg_count := l_msg_count ;
330 x_msg_data := l_msg_data ;
331 FND_MSG_PUB.ADD_EXC_MSG('OKL_POOL_PUB','cleanup_pool_contents');
332 FND_MSG_PUB.count_and_get(
333 p_count => x_msg_count
334 ,p_data => x_msg_data);
335
336 END cleanup_pool_contents;
337
338
339 PROCEDURE add_pool_contents(
340 p_api_version IN NUMBER
341 ,p_init_msg_list IN VARCHAR2
342 ,x_return_status OUT NOCOPY VARCHAR2
343 ,x_msg_count OUT NOCOPY NUMBER
344 ,x_msg_data OUT NOCOPY VARCHAR2
345 ,x_row_count OUT NOCOPY NUMBER
346 ,p_pol_id IN NUMBER
347 ,p_currency_code IN VARCHAR2
348 ,p_cust_object1_id1 IN NUMBER
349 ,p_sic_code IN VARCHAR2
350 ,p_khr_id IN NUMBER
351 ,p_pre_tax_yield_from IN NUMBER
352 ,p_pre_tax_yield_to IN NUMBER
353 ,p_book_classification IN VARCHAR2
354 ,p_tax_owner IN VARCHAR2
355 ,p_pdt_id IN NUMBER
356 ,p_start_from_date IN DATE
357 ,p_start_to_date IN DATE
358 ,p_end_from_date IN DATE
359 ,p_end_to_date IN DATE
360 ,p_stream_type_subclass IN VARCHAR2
361 ,p_stream_element_from_date IN DATE
362 ,p_stream_element_to_date IN DATE
363 ,p_log_message IN VARCHAR2 DEFAULT 'Y'
364 ) IS
365 lx_poc_uv_tbl poc_uv_tbl_type;
366 --l_api_name VARCHAR2(40) ;
367 l_api_name CONSTANT VARCHAR2(40) := 'add_pool_contents';
368 l_api_version NUMBER ;
369 l_init_msg_list VARCHAR2(1) ;
370 l_return_status VARCHAR2(1) ;
371 l_msg_count NUMBER;
372
373 l_msg_data VARCHAR2(2000);
374 l_row_count NUMBER:=0;
375
376 -- l_currency_code okl_pools.currency_code%TYPE;
377 l_polv_rec polv_rec_type ;
378 lx_polv_rec polv_rec_type ;
379
380 BEGIN
381 -- Set API savepoint
382 SAVEPOINT trx_add_pool_contents;
383
384 l_api_version := p_api_version ;
385 l_init_msg_list := p_init_msg_list ;
386 l_return_status := x_return_status ;
387 l_msg_count := x_msg_count ;
388 l_msg_data := x_msg_data ;
389 l_row_count:=x_row_count;
390
391 l_polv_rec.id := p_pol_id;
392 l_polv_rec.currency_code:=p_currency_code;
393 OKL_POOL_PVT.validate_pool(
394 p_api_version => l_api_version,
395 p_init_msg_list => l_init_msg_list,
396 p_api_name => l_api_name,
397 p_polv_rec => l_polv_rec,
398 p_action => 'add_pool_contents',
399 x_return_status => l_return_status,
400 x_msg_count => l_msg_count,
401 x_msg_data => l_msg_data
402 );
403
404 IF ( l_return_status = OKL_API.G_RET_STS_ERROR ) THEN
405 RAISE OKL_API.G_EXCEPTION_ERROR;
406 ELSIF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR ) THEN
407 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
408 END IF;
409
410 OKL_POOL_PVT.add_pool_contents(
411 p_api_version =>l_api_version
412 ,p_init_msg_list =>l_init_msg_list
413 ,x_return_status =>l_return_status
414 ,x_msg_count =>l_msg_count
415 ,x_msg_data =>l_msg_data
416 ,x_row_count =>l_row_count
417 ,p_currency_code =>p_currency_code
418 ,p_pol_id => p_pol_id
419 ,p_multi_org => NULL
420 ,p_cust_object1_id1 => p_cust_object1_id1
421 ,p_sic_code => p_sic_code
422 ,p_khr_id =>p_khr_id
423 ,p_pre_tax_yield_from =>p_pre_tax_yield_from
424 ,p_pre_tax_yield_to =>p_pre_tax_yield_to
425 ,p_book_classification =>p_book_classification
426 ,p_tax_owner =>p_tax_owner
427 ,p_pdt_id =>p_pdt_id
428 ,p_start_date_from =>p_start_from_date
429 ,p_start_date_to =>p_start_to_date
430 ,p_end_date_from =>p_end_from_date
431 ,p_end_date_to =>p_end_to_date
432 ,p_asset_id =>NULL
433 ,p_item_id1 =>NULL
434 ,p_model_number =>NULL
435 ,p_manufacturer_name =>NULL
436 ,p_vendor_id1 =>NULL
437 ,p_oec_from =>NULL
438 ,p_oec_to =>NULL
439 ,p_residual_percentage =>NULL
440 ,p_sty_id1 =>NULL
441 ,p_sty_id2 =>NULL
442 ,p_stream_type_subclass =>p_stream_type_subclass
443 ,p_stream_element_from_date =>p_stream_element_from_date
444 ,p_stream_element_to_date =>p_stream_element_to_date
445 ,p_stream_element_payment_freq =>NULL
446 ,p_log_message =>p_log_message
447 );
448
449 IF ( l_return_status = OKL_API.G_RET_STS_ERROR ) THEN
450 RAISE OKL_API.G_EXCEPTION_ERROR;
451 ELSIF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR ) THEN
452 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
453 END IF;
454
455
456 --Assign value to OUT variables
457 x_return_status := l_return_status ;
458 x_msg_count := l_msg_count ;
459 x_msg_data := l_msg_data ;
460 x_row_count:=l_row_count;
461
462
463 EXCEPTION
464 WHEN OKL_API.G_EXCEPTION_ERROR THEN
465 ROLLBACK TO trx_add_pool_contents;
466 x_return_status := OKL_API.G_RET_STS_ERROR;
467 x_msg_count := l_msg_count ;
468 x_msg_data := l_msg_data ;
469 FND_MSG_PUB.count_and_get(
470 p_count => x_msg_count
471 ,p_data => x_msg_data);
472 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
473 ROLLBACK TO trx_add_pool_contents;
474 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
475 x_msg_count := l_msg_count ;
476 x_msg_data := l_msg_data ;
477 FND_MSG_PUB.count_and_get(
478 p_count => x_msg_count
479 ,p_data => x_msg_data);
480 WHEN OTHERS THEN
481 ROLLBACK TO trx_add_pool_contents;
482 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
483 x_msg_count := l_msg_count ;
484 x_msg_data := l_msg_data ;
485 FND_MSG_PUB.ADD_EXC_MSG('OKL_POOL_PUB','add_pool_contents');
486 FND_MSG_PUB.count_and_get(
487 p_count => x_msg_count
488 ,p_data => x_msg_data);
489
490 END add_pool_contents;
491
492 END Okl_Pool_PUB;