[Home] [Help]
PACKAGE: APPS.OKL_POOL_PVT
Source
1 PACKAGE Okl_Pool_Pvt AUTHID CURRENT_USER AS
2 /* $Header: OKLRSZPS.pls 120.9 2007/12/26 10:14:52 sosharma ship $ */
3 ----------------------------------------------------------------------------
4 -- GLOBAL VARIABLES
5 ----------------------------------------------------------------------------
6 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_POOL_PVT';
7 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
8
9 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
10 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_UNEXP_ERROR;
11 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
12 G_EXCEPTION_ERROR EXCEPTION;
13 G_EXCEPTION_UNEXPECTED_ERROR EXCEPTION;
14
15 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(30) := 'OKL_UNEXPECTED_ERROR';
16 G_SQLERRM_TOKEN CONSTANT VARCHAR2(30) := 'OKL_SQLERRM';
17 G_SQLCODE_TOKEN CONSTANT VARCHAR2(30) := 'OKL_SQLCODE';
18
19 -- mvasudev
20 G_EXC_NAME_ERROR CONSTANT VARCHAR2(50) := 'OKL_API.G_RET_STS_ERROR';
21 G_EXC_NAME_UNEXP_ERROR CONSTANT VARCHAR2(50) := 'OKL_API.G_RET_STS_UNEXP_ERROR';
22 G_EXC_NAME_OTHERS CONSTANT VARCHAR2(6) := 'OTHERS';
23 G_API_TYPE CONSTANT VARCHAR(4) := '_PVT';
24
25 -- cklee
26 G_NET_INVESTMENT_DF CONSTANT VARCHAR2(50) := 'CONTRACT_NET_INVESTMENT_DF';
27 G_NET_INVESTMENT_LOAN CONSTANT VARCHAR2(50) := 'CONTRACT_NET_INVESTMENT_LOAN';
28 G_NET_INVESTMENT_OP CONSTANT VARCHAR2(50) := 'CONTRACT_NET_INVESTMENT_OP';
29 G_NET_INVESTMENT_OTHERS CONSTANT VARCHAR2(50) := 'OTHERS';
30
31 G_DEAL_TYPE_LEASEDF CONSTANT VARCHAR2(30) := 'LEASEDF';
32 G_DEAL_TYPE_LEASEOP CONSTANT VARCHAR2(30) := 'LEASEOP';
33 G_DEAL_TYPE_LEASEST CONSTANT VARCHAR2(30) := 'LEASEST';
34 G_DEAL_TYPE_LOAN CONSTANT VARCHAR2(30) := 'LOAN';
35
36 -- mvasudev
37 G_DEFAULT_NUM CONSTANT NUMBER := 0;
38 G_DEFAULT_CHAR CONSTANT VARCHAR2(1) := 'X';
39 G_DEFAULT_DATE CONSTANT DATE := TO_DATE('1111','YYYY');
40 G_FINAL_DATE CONSTANT DATE := TO_DATE('1','j') + 5300000;
41
42 G_ACTION_REPORT CONSTANT VARCHAR2(3) := 'REP';
43 G_ACTION_REMOVE CONSTANT VARCHAR2(3) := 'REM';
44
45 /* ankushar 26-JUL-2007
46 Bug#6000531
47 start changes
48 */
49 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_REQUIRED_VALUE;
50 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
51 /* ankushar end changes 26-Jul-2007*/
52
53 -- Bug#2829983
54 G_POL_STS_NEW CONSTANT VARCHAR2(3) := 'NEW';
55 G_POL_STS_ACTIVE CONSTANT VARCHAR2(6) := 'ACTIVE';
56 G_POL_STS_INACTIVE CONSTANT VARCHAR2(8) := 'INACTIVE';
57 -- cklee 04/15/03
58 G_POL_STS_EXPIRED CONSTANT VARCHAR2(10) := 'EXPIRED';
59
60 -- cklee 04/15/03
61 G_POC_STS_NEW CONSTANT VARCHAR2(3) := 'NEW';
62 G_POC_STS_ACTIVE CONSTANT VARCHAR2(6) := 'ACTIVE';
63 G_POC_STS_INACTIVE CONSTANT VARCHAR2(8) := 'INACTIVE';
64 G_POC_STS_EXPIRED CONSTANT VARCHAR2(10) := 'EXPIRED';
65 --Added by kthiruva for Bug 6640050
66 G_POC_STS_PENDING CONSTANT VARCHAR2(10) := 'PENDING';
67 /*
68 Direct Finance Lease and Sales Type Lease - CONTRACT_NET_INVESTMENT_DF
69
70 Loan contracts - CONTRACT_NET_INVESTMENT_LOAN
71
72 Operating Leases - CONTRACT_NET_INVESTMENT_OP
73 */
74 ----------------------------------------------------------------------------
75 -- Data Structures
76 ----------------------------------------------------------------------------
77 SUBTYPE polv_rec_type IS OKL_POL_PVT.polv_rec_type;
78 SUBTYPE polv_tbl_type IS OKL_POL_PVT.polv_tbl_type;
79 SUBTYPE pocv_rec_type IS OKL_POC_PVT.pocv_rec_type;
80 SUBTYPE pocv_tbl_type IS OKL_POC_PVT.pocv_tbl_type;
81 SUBTYPE poxv_rec_type IS OKL_POX_PVT.poxv_rec_type;
82 SUBTYPE poxv_tbl_type IS OKL_POX_PVT.poxv_tbl_type;
83
84 TYPE polsrch_rec_type IS RECORD (
85 CUST_OBJECT1_ID1 hz_parties.PARTY_ID%TYPE := NULL --NUMBER(15)
86 ,LESSEE hz_parties.PARTY_NAME%TYPE := NULL --VARCHAR2(360)
87 ,SIC_CODE hz_parties.SIC_CODE%TYPE := NULL --VARCHAR2(30)
88 ,DNZ_CHR_ID OKC_K_HEADERS_B.ID%TYPE := NULL --NUMBER
89 ,CONTRACT_NUMBER OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE := NULL --VARCHAR2(120)
90 ,PRE_TAX_YIELD_FROM OKL_K_HEADERS.PRE_TAX_YIELD%TYPE := NULL--NUMBER
91 ,PRE_TAX_YIELD_TO OKL_K_HEADERS.PRE_TAX_YIELD%TYPE := NULL--NUMBER
92 ,BOOK_CLASSIFICATION OKL_K_HEADERS.DEAL_TYPE%TYPE := NULL --VARCHAR2(30)
93 ,PDT_ID OKL_K_HEADERS.PDT_ID%TYPE := NULL--NUMBER
94 ,START_FROM_DATE OKC_K_HEADERS_B.START_DATE%TYPE := NULL --DATE
95 ,START_TO_DATE OKC_K_HEADERS_B.START_DATE%TYPE := NULL --DATE
96 ,END_FROM_DATE OKC_K_HEADERS_B.END_DATE%TYPE := NULL --DATE
97 ,END_TO_DATE OKC_K_HEADERS_B.END_DATE%TYPE := NULL --DATE
98 ,OPERATING_UNIT OKC_K_HEADERS_B.AUTHORING_ORG_ID%TYPE := NULL--NUMBER
99 ,CURRENCY_CODE OKC_K_HEADERS_B.CURRENCY_CODE%TYPE := NULL --VARCHAR2(15)
100 ,TAX_OWNER okc_rules_b.RULE_INFORMATION1%TYPE := NULL --VARCHAR2(450)
101 ,KLE_ID okc_k_lines_b.ID%TYPE := NULL--NUMBER
102 ,ASSET_ID okx_assets_v.ASSET_ID%TYPE := NULL--NUMBER(15)
103 ,ASSET_NUMBER okx_assets_v.ASSET_NUMBER%TYPE := NULL --VARCHAR2(15)
104 ,MODEL_NUMBER okx_assets_v.MODEL_NUMBER%TYPE := NULL --VARCHAR2(40)
105 ,MANUFACTURER_NAME okx_assets_v.MANUFACTURER_NAME%TYPE := NULL --VARCHAR2(30)
106 ,LOCATION_ID okx_ast_dst_hst_v.LOCATION_ID%TYPE := NULL--NUMBER(15)
107 ,ITEM_ID1 okx_system_items_v.ID1%TYPE := NULL--NUMBER
108 ,VENDOR_ID1 okx_vendors_v.ID1%TYPE := NULL--NUMBER
109 ,OEC_FROM okl_k_lines.OEC%TYPE := NULL--NUMBER(14,3)
110 ,OEC_TO okl_k_lines.OEC%TYPE := NULL--NUMBER(14,3)
111 ,RESIDUAL_PERCENTAGE okl_k_lines.residual_percentage%TYPE := NULL--NUMBER(5,2)
112 ,STY_ID okl_streams.STY_ID%TYPE := NULL--NUMBER
113 ,STREAM_TYPE_CODE okl_strm_type_v.CODE%TYPE := NULL --VARCHAR2(150)
114 ,STREAM_TYPE_NAME okl_strm_type_v.NAME%TYPE := NULL --VARCHAR2(150)
115 ,STREAM_SAY_CODE okl_streams.SAY_CODE%TYPE := NULL --VARCHAR2(30)
116 ,STREAM_ACTIVE_YN okl_streams.ACTIVE_YN%TYPE := NULL --VARCHAR2(3)
117 ,STREAM_ELEMENT_FROM_DATE okl_strm_elements.STREAM_ELEMENT_DATE%TYPE := NULL --DATE
118 ,STREAM_ELEMENT_TO_DATE okl_strm_elements.STREAM_ELEMENT_DATE%TYPE := NULL --DATE
119 ,STREAM_ELEMENT_AMOUNT okl_strm_elements.AMOUNT%TYPE := NULL--NUMBER(14,3)
120 ,POL_ID OKL_POOL_CONTENTS.POL_ID%TYPE --NUMBER
121 -- use the following column STREAMS_FROM_DATE, STREAMS_TO_DATE for the okl_pool_contents_uv.STREAMS_FROM_DATE range search
122 ,STREAMS_FROM_DATE OKL_POOL_CONTENTS.STREAMS_FROM_DATE%TYPE := NULL --DATE
123 ,STREAMS_TO_DATE OKL_POOL_CONTENTS.STREAMS_TO_DATE%TYPE := NULL --DATE
124 ,STREAM_ELEMENT_PAYMENT_FREQ okl_time_units_v.NAME%TYPE := NULL -- VARCHAR2(80)
125 ,CUST_CRD_CLF_CODE AR_LOOKUPS.LOOKUP_CODE%TYPE := NULL --VARCHAR2(80)
126 );
127
128 TYPE polsrch_tbl_type IS TABLE OF polsrch_rec_type
129 INDEX BY BINARY_INTEGER;
130
131 TYPE poc_uv_rec_type IS RECORD
132 (
133 poc_id NUMBER := OKC_API.G_MISS_NUM
134 ,contract_number OKL_POOL_CONTENTS_UV.CONTRACT_NUMBER%TYPE := OKC_API.G_MISS_CHAR
135 ,asset_number OKL_POOL_CONTENTS_UV.ASSET_NUMBER%TYPE := OKC_API.G_MISS_CHAR
136 ,lessee OKL_POOL_CONTENTS_UV.LESSEE%TYPE := OKC_API.G_MISS_CHAR
137 ,stream_type_name OKL_POOL_CONTENTS_UV.STREAM_TYPE_NAME%TYPE := OKC_API.G_MISS_CHAR
138 ,pool_amount NUMBER := OKC_API.G_MISS_NUM
139 ,sty_subclass_code OKL_POOL_CONTENTS_UV.STY_SUBCLASS_CODE%TYPE := OKC_API.G_MISS_CHAR
140 ,sty_subclass OKL_POOL_CONTENTS_UV.STY_SUBCLASS%TYPE := OKC_API.G_MISS_CHAR
141 -- mvasudev, 09/28/2004, Bug#3909240
142 ,stream_type_purpose OKL_POOL_CONTENTS_UV.STREAM_TYPE_PURPOSE%TYPE := OKC_API.G_MISS_CHAR
143 );
144
145 TYPE poc_uv_tbl_type IS TABLE OF poc_uv_rec_type
146 INDEX BY BINARY_INTEGER;
147
148 ----------------------------------------------------------------------------
149 -- Global Exception
150 ----------------------------------------------------------------------------
151 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
152
153 ----------------------------------------------------------------------------
154 -- Procedures and Functions
155 ------------------------------------------------------------------------------
156 ----------------------------------------------------------------------------------
157 -- Start of comments
158 --
159 -- Procedure Name : create_pool
160 -- Description : wrapper api for create pool
161 -- Business Rules :
162 -- Parameters :
163 -- Version : 1.0
164 -- End of comments
165 ----------------------------------------------------------------------------------
166 PROCEDURE create_pool(
167 p_api_version IN NUMBER
168 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
169 ,x_return_status OUT NOCOPY VARCHAR2
170 ,x_msg_count OUT NOCOPY NUMBER
171 ,x_msg_data OUT NOCOPY VARCHAR2
172 ,p_polv_rec IN polv_rec_type
173 ,x_polv_rec OUT NOCOPY polv_rec_type
174 );
175 ----------------------------------------------------------------------------------
176 -- Start of comments
177 --
178 -- Procedure Name : update_pool
179 -- Description : wrapper api for update pool
180 -- Business Rules :
181 -- Parameters :
182 -- Version : 1.0
183 -- End of comments
184 ----------------------------------------------------------------------------------
185 PROCEDURE update_pool(
186 p_api_version IN NUMBER
187 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
188 ,x_return_status OUT NOCOPY VARCHAR2
189 ,x_msg_count OUT NOCOPY NUMBER
190 ,x_msg_data OUT NOCOPY VARCHAR2
191 ,p_polv_rec IN polv_rec_type
192 ,x_polv_rec OUT NOCOPY polv_rec_type
193 );
194 ----------------------------------------------------------------------------------
195 -- Start of comments
196 --
197 -- Procedure Name : delete_pool
198 -- Description : wrapper api for delete pool
199 -- Business Rules :
200 -- Parameters :
201 -- Version : 1.0
202 -- End of comments
203 ----------------------------------------------------------------------------------
204 PROCEDURE delete_pool(
205 p_api_version IN NUMBER
206 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
207 ,x_return_status OUT NOCOPY VARCHAR2
208 ,x_msg_count OUT NOCOPY NUMBER
209 ,x_msg_data OUT NOCOPY VARCHAR2
210 ,p_polv_rec IN polv_rec_type
211 );
212
213 ----------------------------------------------------------------------------------
214 -- Start of comments
215 --
216 -- Procedure Name : create_pool_contents
217 -- Description : wrapper api for create pool contents
218 -- Business Rules :
219 -- Parameters :
220 -- Version : 1.0
221 -- End of comments
222 ----------------------------------------------------------------------------------
223 PROCEDURE create_pool_contents(
224 p_api_version IN NUMBER
225 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
226 ,x_return_status OUT NOCOPY VARCHAR2
227 ,x_msg_count OUT NOCOPY NUMBER
228 ,x_msg_data OUT NOCOPY VARCHAR2
229 ,p_pocv_rec IN pocv_rec_type
230 ,x_pocv_rec OUT NOCOPY pocv_rec_type
231 );
232 ----------------------------------------------------------------------------------
233 -- Start of comments
234 --
235 -- Procedure Name : create_pool_contents
236 -- Description : wrapper api for create pool contents
237 -- Business Rules :
238 -- Parameters :
239 -- Version : 1.0
240 -- End of comments
241 ----------------------------------------------------------------------------------
242 PROCEDURE create_pool_contents(
243 p_api_version IN NUMBER
244 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
245 ,x_return_status OUT NOCOPY VARCHAR2
246 ,x_msg_count OUT NOCOPY NUMBER
247 ,x_msg_data OUT NOCOPY VARCHAR2
248 ,p_pocv_tbl IN pocv_tbl_type
249 ,x_pocv_tbl OUT NOCOPY pocv_tbl_type
250 );
251
252 ----------------------------------------------------------------------------------
253 -- Start of comments
254 --
255 -- Procedure Name : update_pool_contents
256 -- Description : wrapper api for update pool contents
257 -- Business Rules :
258 -- Parameters :
259 -- Version : 1.0
260 -- End of comments
261 ----------------------------------------------------------------------------------
262 PROCEDURE update_pool_contents(
263 p_api_version IN NUMBER
264 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
265 ,x_return_status OUT NOCOPY VARCHAR2
266 ,x_msg_count OUT NOCOPY NUMBER
267 ,x_msg_data OUT NOCOPY VARCHAR2
268 ,p_pocv_rec IN pocv_rec_type
269 ,x_pocv_rec OUT NOCOPY pocv_rec_type
270 );
271 ----------------------------------------------------------------------------------
272 -- Start of comments
273 --
274 -- Procedure Name : update_pool_contents
275 -- Description : wrapper api for update pool contents
276 -- Business Rules :
277 -- Parameters :
278 -- Version : 1.0
279 -- End of comments
280 ----------------------------------------------------------------------------------
281 PROCEDURE update_pool_contents(
282 p_api_version IN NUMBER
283 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
284 ,x_return_status OUT NOCOPY VARCHAR2
285 ,x_msg_count OUT NOCOPY NUMBER
286 ,x_msg_data OUT NOCOPY VARCHAR2
287 ,p_pocv_tbl IN pocv_tbl_type
288 ,x_pocv_tbl OUT NOCOPY pocv_tbl_type
289 );
290
291
292 ----------------------------------------------------------------------------------
293 -- Start of comments
294 --
295 -- Procedure Name : delete_pool_contents
296 -- Description : wrapper api for delele pool contents
297 -- Business Rules :
298 -- Parameters :
299 -- Version : 1.0
300 -- End of comments
301 ----------------------------------------------------------------------------------
302 PROCEDURE delete_pool_contents(
303 p_api_version IN NUMBER
304 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
305 ,x_return_status OUT NOCOPY VARCHAR2
306 ,x_msg_count OUT NOCOPY NUMBER
307 ,x_msg_data OUT NOCOPY VARCHAR2
308 ,p_pocv_rec IN pocv_rec_type
309 );
310 ----------------------------------------------------------------------------------
311 -- Start of comments
312 --
313 -- Procedure Name : delete_pool_contents
314 -- Description : wrapper api for delele pool contents
315 -- Business Rules :
316 -- Parameters :
317 -- Version : 1.0
318 -- End of comments
319 ----------------------------------------------------------------------------------
320
321 PROCEDURE delete_pool_contents(
322 p_api_version IN NUMBER
323 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
324 ,x_return_status OUT NOCOPY VARCHAR2
325 ,x_msg_count OUT NOCOPY NUMBER
326 ,x_msg_data OUT NOCOPY VARCHAR2
327 ,p_pocv_tbl IN pocv_tbl_type
328 );
329
330 ----------------------------------------------------------------------------------
331 -- Start of comments
332 --
333 -- Procedure Name : create_pool_transaction
334 -- Description : wrapper api for create pool transaction
335 -- Business Rules :
336 -- Parameters :
337 -- Version : 1.0
338 -- End of comments
339 ----------------------------------------------------------------------------------
340 PROCEDURE create_pool_transaction(
341 p_api_version IN NUMBER
342 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
343 ,x_return_status OUT NOCOPY VARCHAR2
344 ,x_msg_count OUT NOCOPY NUMBER
345 ,x_msg_data OUT NOCOPY VARCHAR2
346 ,p_poxv_rec IN poxv_rec_type
347 ,x_poxv_rec OUT NOCOPY poxv_rec_type
348 );
349
350 ----------------------------------------------------------------------------------
351 -- Start of comments
352 --
353 -- Procedure Name : update_pool_transaction
354 -- Description : wrapper api for update pool transaction
355 -- Business Rules :
356 -- Parameters :
357 -- Version : 1.0
358 -- End of comments
359 ----------------------------------------------------------------------------------
360 PROCEDURE update_pool_transaction(
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_poxv_rec IN poxv_rec_type
367 ,x_poxv_rec OUT NOCOPY poxv_rec_type
368 );
369
370 ----------------------------------------------------------------------------------
371 -- Start of comments
372 --
373 -- Procedure Name : delete_pool_transaction
374 -- Description : wrapper api for delete pool transaction
375 -- Business Rules :
376 -- Parameters :
377 -- Version : 1.0
378 -- End of comments
379 ----------------------------------------------------------------------------------
380 PROCEDURE delete_pool_transaction(
381 p_api_version IN NUMBER
382 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
383 ,x_return_status OUT NOCOPY VARCHAR2
384 ,x_msg_count OUT NOCOPY NUMBER
385 ,x_msg_data OUT NOCOPY VARCHAR2
386 ,p_poxv_rec IN poxv_rec_type
387 );
388
389 ----------------------------------------------------------------------------------
390
391 ----------------------------------------------------------------------------------
392 -- Start of comments
393 --
394 -- Procedure Name : get_pool_stream_amout
395 -- Description : get stream elements amount from pool contents by okl_pool_contents.id
396 -- Business Rules :
397 -- Parameters :
398 -- Version : 1.0
399 -- End of comments
400 ----------------------------------------------------------------------------------
401 FUNCTION get_pool_stream_amout(
402 p_poc_id IN okl_pool_contents.id%TYPE
403 ) RETURN NUMBER;
404 PRAGMA RESTRICT_REFERENCES (get_pool_stream_amout, TRUST);
405 ----------------------------------------------------------------------------------
406 -- Start of comments
407 --
408 -- Procedure Name : get_tot_receivable_amt
409 -- Description : get stream elements amount from pool contents by okl_pools.id
410 -- Business Rules :
411 -- Parameters :
412 -- Version : 1.0
413 -- End of comments
414 ----------------------------------------------------------------------------------
415 FUNCTION get_tot_receivable_amt(
416 p_pol_id IN okl_pools.id%TYPE
417 ) RETURN NUMBER;
418 PRAGMA RESTRICT_REFERENCES (get_tot_receivable_amt, TRUST);
419
420 ----------------------------------------------------------------------------------
421 -- Start of comments
422 --
423 -- Procedure Name : get_tot_receivable_amt
424 -- Description : wrapper api for get_tot_receivable_amt
425 -- Business Rules :
426 -- Parameters :
427 -- Version : 1.0
428 -- End of comments
429 ----------------------------------------------------------------------------------
430 PROCEDURE get_tot_recei_amt(
431 p_api_version IN NUMBER
432 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
433 ,x_return_status OUT NOCOPY VARCHAR2
434 ,x_msg_count OUT NOCOPY NUMBER
435 ,x_msg_data OUT NOCOPY VARCHAR2
436 ,x_value OUT NOCOPY NUMBER
437 ,p_pol_id IN okl_pools.id%TYPE
438 );
439 ----------------------------------------------------------------------------------
440 -- Start of comments
441 --
442 -- Procedure Name : get_tot_receivable_amt
443 -- Description : wrapper api for get_tot_receivable_amt by investor agreement ID
444 -- Business Rules :
445 -- Parameters :
446 -- Version : 1.0
447 -- End of comments
448 ----------------------------------------------------------------------------------
449 PROCEDURE get_tot_receivable_amt(
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 ,x_value OUT NOCOPY NUMBER
456 ,p_khr_id IN okc_k_headers_b.id%TYPE
457 );
458 ----------------------------------------------------------------------------------
459 -- Start of comments
460 --
461 -- Procedure Name : get_tot_principal_amt
462 -- Description : get asset principal amount from pool contents by okl_pools.id
463 -- Business Rules :
464 -- Parameters :
465 -- Version : 1.0
466 -- End of comments
467 ----------------------------------------------------------------------------------
468 FUNCTION get_tot_principal_amt(
469 p_pol_id IN okl_pools.id%TYPE
470 ) RETURN NUMBER;
471 PRAGMA RESTRICT_REFERENCES (get_tot_principal_amt, TRUST);
472 ----------------------------------------------------------------------------------
473 -- Start of comments
474 --
475 -- Procedure Name : recal_tot_principal_amt
476 -- Description : wrapper api for get_tot_principal_amt
477 -- Business Rules :
478 -- Parameters :
479 -- Version : 1.0
480 -- End of comments
481 ----------------------------------------------------------------------------------
482 PROCEDURE recal_tot_princ_amt(
483 p_api_version IN NUMBER
484 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
485 ,x_return_status OUT NOCOPY VARCHAR2
486 ,x_msg_count OUT NOCOPY NUMBER
487 ,x_msg_data OUT NOCOPY VARCHAR2
488 ,x_value OUT NOCOPY NUMBER
489 ,p_pol_id IN okl_pools.id%TYPE
490 );
491 ----------------------------------------------------------------------------------
492 -- Start of comments
493 --
494 -- Procedure Name : recal_tot_principal_amt
495 -- Description : wrapper api for get_tot_principal_amt by investor agreement ID
496 -- Business Rules :
497 -- Parameters :
498 -- Version : 1.0
499
500 -- End of comments
501 ----------------------------------------------------------------------------------
502 PROCEDURE recal_tot_principal_amt(
503 p_api_version IN NUMBER
504 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
505 ,x_return_status OUT NOCOPY VARCHAR2
506 ,x_msg_count OUT NOCOPY NUMBER
507 ,x_msg_data OUT NOCOPY VARCHAR2
508 ,x_value OUT NOCOPY NUMBER
509 ,p_khr_id IN okc_k_headers_b.id%TYPE
510 );
511
512 ----------------------------------------------------------------------------------
513 -- Start of comments
514 --
515 -- Procedure Name : add_pool_contents
516 -- Description : creates pool contents based on passed in search criteria
517 -- Business Rules :
518 -- Parameters :
519 -- Version : 1.0
520 -- End of comments
521 ----------------------------------------------------------------------------------
522 -- Create by Search Criteria: Query Streams from contracts + Create
523
524 PROCEDURE add_pool_contents(
525 p_api_version IN NUMBER
526 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
527 ,x_return_status OUT NOCOPY VARCHAR2
528 ,x_msg_count OUT NOCOPY NUMBER
529 ,x_msg_data OUT NOCOPY VARCHAR2
530 ,x_row_count OUT NOCOPY NUMBER
531 ,p_currency_code IN VARCHAR2
532 ,p_pol_id IN NUMBER
533 ,p_multi_org IN VARCHAR2 DEFAULT okl_api.g_true
534 ,p_cust_object1_id1 IN NUMBER DEFAULT NULL
535 ,p_sic_code IN VARCHAR2 DEFAULT NULL
536 ,p_khr_id IN NUMBER DEFAULT NULL
537 ,p_pre_tax_yield_from IN NUMBER DEFAULT NULL
538 ,p_pre_tax_yield_to IN NUMBER DEFAULT NULL
539 ,p_book_classification IN VARCHAR2 DEFAULT NULL
540 ,p_tax_owner IN VARCHAR2 DEFAULT NULL
541 ,p_pdt_id IN NUMBER DEFAULT NULL
542 ,p_start_date_from IN DATE DEFAULT NULL
543 ,p_start_date_to IN DATE DEFAULT NULL
544 ,p_end_date_from IN DATE DEFAULT NULL
545 ,p_end_date_to IN DATE DEFAULT NULL
546 ,p_asset_id IN NUMBER DEFAULT NULL
547 ,p_item_id1 IN NUMBER DEFAULT NULL
548 ,p_model_number IN VARCHAR2 DEFAULT NULL
549 ,p_manufacturer_name IN VARCHAR2 DEFAULT NULL
550 ,p_vendor_id1 IN NUMBER DEFAULT NULL
551 ,p_oec_from IN NUMBER DEFAULT NULL
552 ,p_oec_to IN NUMBER DEFAULT NULL
553 ,p_residual_percentage IN NUMBER DEFAULT NULL
554 ,p_sty_id1 IN NUMBER DEFAULT NULL
555 ,p_sty_id2 IN NUMBER DEFAULT NULL
556 -- start added by cklee 08/06/03
557 ,p_stream_type_subclass IN VARCHAR2 DEFAULT NULL
558 -- end added by cklee 08/06/03
559 ,p_stream_element_from_date IN DATE DEFAULT NULL
560 ,p_stream_element_to_date IN DATE DEFAULT NULL
561 ,p_stream_element_payment_freq IN VARCHAR2 DEFAULT NULL
562 /* ankushar 26-JUL-2007 Bug#6000531 start changes */
563 ,p_log_message IN VARCHAR2 DEFAULT 'Y'
564 /* ankushar end changes 26-Jul-2007*/
565 ,p_cust_crd_clf_code IN VARCHAR2 DEFAULT NULL);
566 ----------------------------------------------------------------------------------
567 -- Start of comments
568 --
569 -- Procedure Name : cleanup_pool_contents
570 -- Description : removes pool contents based on passed in search criteria
571 -- Business Rules :
572 -- Parameters :
573 -- Version : 1.0
574 -- End of comments
575 ----------------------------------------------------------------------------------
576 -- Create by Search Criteria: Query Streams from contracts + Create
577
578 PROCEDURE cleanup_pool_contents(
579 p_api_version IN NUMBER
580 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
581 ,x_return_status OUT NOCOPY VARCHAR2
582 ,x_msg_count OUT NOCOPY NUMBER
583 ,x_msg_data OUT NOCOPY VARCHAR2
584 ,p_currency_code IN VARCHAR2
585 ,p_pol_id IN NUMBER
586 ,p_multi_org IN VARCHAR2 DEFAULT okl_api.g_true
587 ,p_cust_object1_id1 IN NUMBER DEFAULT NULL
588 ,p_sic_code IN VARCHAR2 DEFAULT NULL
589 ,p_dnz_chr_id IN NUMBER DEFAULT NULL
590 ,p_pre_tax_yield_from IN NUMBER DEFAULT NULL
591 ,p_pre_tax_yield_to IN NUMBER DEFAULT NULL
592 ,p_book_classification IN VARCHAR2 DEFAULT NULL
593 ,p_tax_owner IN VARCHAR2 DEFAULT NULL
594 ,p_pdt_id IN NUMBER DEFAULT NULL
595 ,p_start_from_date IN DATE DEFAULT NULL
596 ,p_start_to_date IN DATE DEFAULT NULL
597 ,p_end_from_date IN DATE DEFAULT NULL
598 ,p_end_to_date IN DATE DEFAULT NULL
599 ,p_asset_id IN NUMBER DEFAULT NULL
600 ,p_item_id1 IN NUMBER DEFAULT NULL
601 ,p_model_number IN VARCHAR2 DEFAULT NULL
602 ,p_manufacturer_name IN VARCHAR2 DEFAULT NULL
603 ,p_vendor_id1 IN NUMBER DEFAULT NULL
604 ,p_oec_from IN NUMBER DEFAULT NULL
605 ,p_oec_to IN NUMBER DEFAULT NULL
606 ,p_residual_percentage IN NUMBER DEFAULT NULL
607 ,p_sty_id IN NUMBER DEFAULT NULL
608 -- start added by cklee 08/06/03
609 ,p_stream_type_subclass IN VARCHAR2 DEFAULT NULL
610 -- end added by cklee 08/06/03
611 ,p_streams_from_date IN DATE DEFAULT NULL
612 ,p_streams_to_date IN DATE DEFAULT NULL
613 ,p_action_code IN VARCHAR2 DEFAULT G_ACTION_REPORT
614 ,x_poc_uv_tbl OUT NOCOPY poc_uv_tbl_type
615 ,p_cust_crd_clf_code IN VARCHAR2 DEFAULT NULL);
616 ----------------------------------------------------------------------------------
617 -- Start of comments
618 -- mvasudev
619 -- Procedure Name : reconcile_contents
620 -- Description : reconcile pool contents based on the pool
621 -- Business Rules :
622 -- Parameters :
623 -- Version : 1.0
624 -- End of comments
625 ----------------------------------------------------------------------------------
626 PROCEDURE reconcile_contents(p_api_version IN NUMBER
627 ,p_init_msg_list IN VARCHAR2
628 ,p_pol_id IN NUMBER
629 ,p_mode IN VARCHAR2 DEFAULT NULL
630 ,x_return_status OUT NOCOPY VARCHAR2
631 ,x_msg_count OUT NOCOPY NUMBER
632 ,x_msg_data OUT NOCOPY VARCHAR2
633 ,x_reconciled OUT NOCOPY VARCHAR2);
634
635 ----------------------------------------------------------------------------------
636 -- Start of comments
637 --
638 -- Procedure Name : update_pool_status_active
639 -- Description : updates a pool header, and contents' status.
640 -- Business Rules :
641 -- Parameters :
642 -- Version : 1.0
643 -- End of comments
644 ----------------------------------------------------------------------------------
645 PROCEDURE update_pool_status_active(
646 p_api_version IN NUMBER
647 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
648 ,x_return_status OUT NOCOPY VARCHAR2
649 ,x_msg_count OUT NOCOPY NUMBER
650 ,x_msg_data OUT NOCOPY VARCHAR2
651 ,p_pol_id IN okl_pools.id%TYPE);
652
653 ----------------------------------------------------------------------------------
654 -- Start of comments
655 --
656 -- Procedure Name : update_pool_status_expired
657 -- Description : updates a pool header, and contents' status.
658 -- Business Rules :
659 -- Parameters :
660 -- Version : 1.0
661 -- End of comments
662 ----------------------------------------------------------------------------------
663 PROCEDURE update_pool_status_expired(
664 p_api_version IN NUMBER
665 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
666 ,x_return_status OUT NOCOPY VARCHAR2
667 ,x_msg_count OUT NOCOPY NUMBER
668 ,x_msg_data OUT NOCOPY VARCHAR2
669 ,p_pol_id IN okl_pools.id%TYPE);
670
671 ----------------------------------------------------------------------------------
672 -- Start of comments
673 -- mvasudev
674 -- Procedure Name : get_total_stream_amount
675 -- Description : Gets the Total Stream Amount for a given POC using the stm_id
676 -- regardless of its status
677 -- Business Rules :
678 -- Parameters :
679 -- Version : 1.0
680 -- End of comments
681 ----------------------------------------------------------------------------------
682 PROCEDURE get_total_stream_amount(
683 p_api_version IN NUMBER
684 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
685 ,x_return_status OUT NOCOPY VARCHAR2
686 ,x_msg_count OUT NOCOPY NUMBER
687 ,x_msg_data OUT NOCOPY VARCHAR2
688 ,p_poc_id IN okl_pool_contents.id%TYPE
689 ,p_stm_id IN okl_streams.id%TYPE
690 ,x_amount OUT NOCOPY NUMBER
691 );
692
693 ----------------------------------------------------------------------------------
694 -- Start of comments
695 -- ankushar
696 -- Procedure Name : validate_pool
697 -- Description : Validates the pool when called from the public API OKL_POOL_PUB
698 -- Business Rules :
699 -- Parameters :
700 -- Version : 1.0
701 -- End of comments
702 ----------------------------------------------------------------------------------
703 /**
704
705 /* ankushar 26-JUL-2007
706 Bug#6000531
707 start changes
708 */
709 PROCEDURE validate_pool(
710 p_api_version IN NUMBER
711 ,p_init_msg_list IN VARCHAR2
712 ,p_api_name IN VARCHAR2
713 ,p_polv_rec IN polv_rec_type
714 ,p_action IN VARCHAR2
715 ,x_return_status OUT NOCOPY VARCHAR2
716 ,x_msg_count OUT NOCOPY NUMBER
717 ,x_msg_data OUT NOCOPY VARCHAR2
718 );
719 /* ankushar end changes 26-Jul-2007*/
720
721 ----------------------------------------------------------------------------------
722 -- Start of comments
723 --
724 -- Procedure Name : get_tot_recei_amt_pend
725 -- Description : wrapper api for get_tot_receivable_amt_for_pend by investor agreement ID
726 -- Business Rules :
727 -- Parameters :
728 -- Version : 1.0
729 -- End of comments
730 ----------------------------------------------------------------------------------
731 PROCEDURE get_tot_recei_amt_pend(
732 p_api_version IN NUMBER
733 ,p_init_msg_list IN VARCHAR2
734 ,x_return_status OUT NOCOPY VARCHAR2
735 ,x_msg_count OUT NOCOPY NUMBER
736 ,x_msg_data OUT NOCOPY VARCHAR2
737 ,x_value OUT NOCOPY NUMBER
738 ,p_pol_id IN okl_pools.id%TYPE
739 );
740 ----------------------------------------------------------------------------------
741 -- Start of comments
742 --
743 -- Procedure Name : get_tot_recv_amt_for_pend
744 -- Description : get asset principal amount from pool contents by okl_pools.id
745 -- Business Rules :
746 -- Parameters :
747 -- Version : 1.0
748 -- End of comments
749 ----------------------------------------------------------------------------------
750 FUNCTION get_tot_recv_amt_for_pend(
751 p_pol_id IN okl_pools.id%TYPE
752 ) RETURN NUMBER;
753 PRAGMA RESTRICT_REFERENCES (get_tot_recv_amt_for_pend, TRUST);
754
755 END Okl_Pool_Pvt;