[Home] [Help]
PACKAGE: APPS.OKL_PRICING_UTILS_PVT
Source
1 PACKAGE OKL_PRICING_UTILS_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKLRPIUS.pls 120.11.12020000.2 2012/11/23 13:32:01 rpillay ship $ */
3
4 -----------------------------------------------------------------------------
5 -- Constants Declaration
6 -----------------------------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKL_PRICING_UTILS_PVT';
8 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
9 G_API_VERSION CONSTANT NUMBER := 1;
10 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_INVALID_VALUE;
11 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
12 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
13 G_FALSE CONSTANT VARCHAR2(1) := OKL_API.G_FALSE;
14 G_TRUE CONSTANT VARCHAR2(1) := OKL_API.G_TRUE;
15 G_DB_ERROR CONSTANT VARCHAR2(12) := 'OKL_DB_ERROR';
16 G_PROG_NAME_TOKEN CONSTANT VARCHAR2(9) := 'PROG_NAME';
17 G_SQLCODE_TOKEN CONSTANT VARCHAR2(7) := 'SQLCODE';
18 G_SQLERRM_TOKEN CONSTANT VARCHAR2(7) := 'SQLERRM';
19 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
20 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_UNEXP_ERROR;
21 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
22 -- Constants representing the Quick Quote Financial Adjustments
23 G_DOWNPAYMENT_TYPE CONSTANT VARCHAR2(30) := 'DOWN_PAYMENT';
24 G_SUBSIDY_TYPE CONSTANT VARCHAR2(30) := 'SUBSIDY';
25 G_TRADEIN_TYPE CONSTANT VARCHAR2(30) := 'TRADEIN';
26 G_ITEMCATEGORY_TYPE CONSTANT VARCHAR2(30) := 'ITEM_CATEGORY';
27 -- Constants representing the Quick Quote Fees and Services Details
28 G_QQ_FEE_EXPENSE CONSTANT VARCHAR2(30) := 'FEE_EXPENSE';
29 G_QQ_FEE_PAYMENT CONSTANT VARCHAR2(30) := 'FEE_PAYMENT';
30 G_QQ_INSURANCE CONSTANT VARCHAR2(30) := 'INSURANCE';
31 G_QQ_SERVICE CONSTANT VARCHAR2(30) := 'SERVICE';
32 G_QQ_TAX CONSTANT VARCHAR2(30) := 'TAX';
33 -- Constants representing the possible Basis types for Quick Quotes
34 G_QQ_ASSET_COST_BASIS CONSTANT VARCHAR2(30) := 'ASSET_COST';
35 G_QQ_RENT_BASIS CONSTANT VARCHAR2(30) := 'RENT';
36 G_FIXED_BASIS CONSTANT VARCHAR2(30) := 'FIXED';
37 G_QQ_SRT_RATE_TYPE CONSTANT VARCHAR2(30) := 'INDEX_RATE';
38 -- Constants representing the various source types for Cash flows
39 G_CF_SOURCE_QQ CONSTANT VARCHAR2(30) := 'OKL_QUICK_QUOTES_B';
40 G_CF_SOURCE_LQ CONSTANT VARCHAR2(30) := 'OKL_LEASE_QUOTES_B';
41 G_CF_SOURCE_LQ_ASS CONSTANT VARCHAR2(30) := 'OKL_ASSETS_B';
42 G_CF_SOURCE_LQ_FEE CONSTANT VARCHAR2(30) := 'OKL_FEES_B';
43 -----------------------------------------------------------------------------
44 -- Global Data Structures Declaration
45 -----------------------------------------------------------------------------
46 TYPE interim_interest_rec_type IS RECORD (cf_days NUMBER, cf_amount NUMBER, cf_dpp NUMBER);
47 TYPE interim_interest_tbl_type IS TABLE OF interim_interest_rec_type
48 INDEX BY BINARY_INTEGER;
49
50 -- Record to store the Header level information
51 TYPE so_hdr_rec_type IS RECORD
52 (
53 so_type VARCHAR2(30), -- Quick Quote/Standard Quote ...
54 id NUMBER ,
55 reference_number VARCHAR2(150),
56 expected_start_date DATE ,
57 currency_code VARCHAR2(15) ,
58 term NUMBER ,
59 sales_territory_id NUMBER ,
60 end_of_term_option_id NUMBER ,
61 pricing_method VARCHAR2(30),
62 structured_pricing VARCHAR2(30),
63 line_level_pricing VARCHAR2(30),
64 lease_rate_factor NUMBER,
65 rate_card_id NUMBER,
66 rate_template_id NUMBER,
67 target_rate_type VARCHAR2(30),
68 target_rate NUMBER,
69 target_amount NUMBER,
70 target_frequency VARCHAR2(30),
71 target_arrears VARCHAR2(3),
72 target_periods NUMBER
73 );
74 TYPE subsidy_basis_tbl_type IS TABLE OF VARCHAR2(30)
75 INDEX BY BINARY_INTEGER;
76 TYPE subsidy_value_tbl_type IS TABLE OF NUMBER
77 INDEX BY BINARY_INTEGER;
78
79 -- Record to store the Down Payment, Subsidy, Trade in, Item Category Costs .. Amounts
80 -- Note: As per the FDD, QQ can have more than one subsidy financial adjustment defined !
81 TYPE so_amt_details_rec_type IS RECORD
82 (
83 down_payment_amount NUMBER,
84 subsidy_amount NUMBER,
85 tradein_amount NUMBER,
86 down_payment_basis VARCHAR2(30),
87 down_payment_value NUMBER,
88 tradein_basis VARCHAR2(30),
89 tradein_value NUMBER,
90 subsidy_basis_tbl subsidy_basis_tbl_type,
91 subsidy_value_tbl subsidy_value_tbl_type
92 );
93
94 -- Record to store the Item Category Cost and the Residual Value
95 TYPE so_asset_details_rec_type IS RECORD
96 (
97 asset_cost NUMBER,
98 end_of_term_amount NUMBER,
99 basis VARCHAR2(30),
100 value NUMBER,
101 percentage_of_total_cost NUMBER
102 );
103 -- Table representing the Item Category Details entered by user for the Quick Quote
104 TYPE so_asset_details_tbl_type IS TABLE OF so_asset_details_rec_type
105 INDEX BY BINARY_INTEGER;
106
107 -- Record representing the Cash Flow
108 TYPE so_cash_flows_rec_type IS RECORD
109 (
110 caf_id NUMBER,
111 khr_id NUMBER,
112 qte_id NUMBER,
113 cfo_id NUMBER,
114 sts_code VARCHAR2(30),
115 sty_id NUMBER,
116 cft_code VARCHAR2(30),
117 due_arrears_yn VARCHAR2(3),
118 start_date DATE,
119 number_of_advance_periods NUMBER
120 );
121
122 -- Record representing cash flow level object
123 TYPE so_cash_flow_details_rec_type IS RECORD
124 (
125 cfl_id NUMBER,
126 caf_id NUMBER,
127 fqy_code VARCHAR2(30),
128 rate NUMBER,
129 stub_days NUMBER,
130 stub_amount NUMBER,
131 number_of_periods NUMBER,
132 amount NUMBER,
133 start_date DATE,
134 is_stub VARCHAR2(1) DEFAULT 'N',
135 locked_amt VARCHAR2(1) DEFAULT 'N',
136 ratio NUMBER
137 );
138 -- Cash Flow Levels Table
139 TYPE so_cash_flow_details_tbl_type IS TABLE OF so_cash_flow_details_rec_type
140 INDEX BY BINARY_INTEGER;
141
142 -- Record structure representing the Financial Adjustments like
143 -- Expenses, Fee Payments, Services
144 TYPE item_cat_cf_rec_type IS RECORD
145 (
146 line_id NUMBER,
147 item_category_id NUMBER,
148 financed_amount NUMBER,
149 subsidy NUMBER,
150 down_payment NUMBER,
151 trade_in NUMBER,
152 eot_amount NUMBER,
153 cash_flow_rec so_cash_flows_rec_type,
154 cash_flow_level_tbl so_cash_flow_details_tbl_type
155 );
156 -- Table representing the various financial adjustment line types
157 -- along with the Cash flow and Cash flow details table
158 TYPE item_cat_cf_tbl_type IS TABLE OF item_cat_cf_rec_type
159 INDEX BY BINARY_INTEGER;
160
161 -- Record structure representing the Financial Adjustments like
162 -- Expenses, Fee Payments, Services
163 TYPE so_fee_srv_rec_type IS RECORD
164 (
165 type VARCHAR2(30),
166 basis VARCHAR2(30),
167 value NUMBER,
168 amount NUMBER,
169 cash_flow_rec so_cash_flows_rec_type,
170 cash_flow_level_tbl so_cash_flow_details_tbl_type
171 );
172 -- Table representing the various financial adjustment line types
173 -- along with the Cash flow and Cash flow details table
174 TYPE so_fee_srv_tbl_type IS TABLE OF so_fee_srv_rec_type
175 INDEX BY BINARY_INTEGER;
176
177 TYPE cash_inflows_rec_type IS RECORD
178 (
179 line_number NUMBER,
180 cf_amount NUMBER,
181 cf_date DATE,
182 cf_purpose VARCHAR2(10),
183 cf_dpp NUMBER,
184 cf_ppy NUMBER,
185 cf_days NUMBER,
186 cf_rate NUMBER, -- Can be used as cf_iir
187 cf_miss_pay VARCHAR2(30),
188 is_stub VARCHAR2(1), -- Stub Flag
189 is_arrears VARCHAR2(1), -- Arrears Flag
190 cf_period_start_end_date DATE, -- Can be useful in compute_irr
191 locked_amt VARCHAR2(1) DEFAULT 'N', -- Useful in compute_irr TR pricing
192 cf_ratio NUMBER
193 );
194 TYPE cash_inflows_tbl_type IS TABLE OF cash_inflows_rec_type
195 INDEX BY BINARY_INTEGER;
196
197 TYPE pricing_parameter_rec_type IS RECORD
198 (
199 line_type VARCHAR2(256),
200 line_start_date DATE, -- Start date of the corresponding line ..
201 line_end_date DATE, -- End date of the corresponding line ..
202 payment_type VARCHAR2(256),
203 financed_amount NUMBER,
204 trade_in NUMBER,
205 down_payment NUMBER,
206 subsidy NUMBER,
207 residual_inflows cash_inflows_tbl_type,
208 cash_inflows cash_inflows_tbl_type,
209 cap_fee_amount NUMBER,
210 cfo_id NUMBER, -- Quote Streams ER: 7440199
211 link_asset_id NUMBER -- Quote Streams ER: 7440199
212 );
213
214 TYPE pricing_parameter_tbl_type IS TABLE OF pricing_parameter_rec_type
215 INDEX BY BINARY_INTEGER;
216
217 TYPE pricing_results_rec_type IS RECORD
218 (
219 line_type VARCHAR2(256),
220 line_id NUMBER,
221 item_category_id NUMBER,
222 financed_amount NUMBER,
223 trade_in NUMBER,
224 down_payment NUMBER,
225 subsidy NUMBER,
226 cash_flow_rec so_cash_flows_rec_type,
227 cash_flow_level_tbl so_cash_flow_details_tbl_type
228 );
229 TYPE pricing_results_tbl_type IS TABLE OF pricing_results_rec_type
230 INDEX BY BINARY_INTEGER;
231
232 -- Lease Rate Factors Header n Version Details Record
233 TYPE lrs_details_rec_type IS RECORD (
234 header_id NUMBER,
235 version_id NUMBER,
236 name okl_ls_rt_fctr_sets_v.name%type,
237 lrs_type_code okl_ls_rt_fctr_sets_v.lrs_type_code%type,
238 frq_code okl_ls_rt_fctr_sets_v.frq_code%type,
239 currency_code okl_ls_rt_fctr_sets_v.currency_code%type,
240 sts_code okl_fe_rate_set_versions_v.sts_code%type,
241 effective_from_date okl_fe_rate_set_versions_v.effective_from_date%type,
242 effective_to_date okl_fe_rate_set_versions_v.effective_to_date%type,
243 arrears_yn okl_fe_rate_set_versions_v.arrears_yn%type,
244 end_of_term_ver_id NUMBER,
245 std_rate_tmpl_ver_id NUMBER,
246 adj_mat_version_id NUMBER,
247 version_number okl_fe_rate_set_versions_v.version_number%type,
248 lrs_version_rate NUMBER,
249 rate_tolerance NUMBER,
250 residual_tolerance NUMBER,
251 deferred_pmts NUMBER,
252 advance_pmts NUMBER);
253
254 -- Lease Rate Factors Record Type
255 TYPE lrs_factor_rec_type IS RECORD (
256 factor_id NUMBER,
257 term_in_months NUMBER,
258 residual_value_percent NUMBER );
259
260 -- Lease Rate Factor Levels Record Type
261 TYPE lrs_levels_rec_type IS RECORD (
262 sequence_number NUMBER, -- order by seq_num ascending
263 periods NUMBER,
264 lease_rate_factor NUMBER);
265
266 TYPE lrs_levels_tbl_type IS TABLE OF lrs_levels_rec_type
267 INDEX BY BINARY_INTEGER;
268
269 TYPE srt_details_rec_type IS RECORD (
270 srt_header_id NUMBER,
271 srt_version_id NUMBER,
272 template_name OKL_FE_STD_RT_TMP_V.TEMPLATE_NAME%TYPE,
273 currency_code OKL_FE_STD_RT_TMP_V.CURRENCY_CODE%TYPE,
274 version_number OKL_FE_STD_RT_TMP_VERS.VERSION_NUMBER%TYPE,
275 effective_from_date OKL_FE_STD_RT_TMP_VERS.EFFECTIVE_FROM_DATE%TYPE,
276 effective_to_date OKL_FE_STD_RT_TMP_VERS.EFFECTIVE_TO_DATE%TYPE,
277 sts_code OKL_FE_STD_RT_TMP_VERS.STS_CODE%TYPE,
278 pricing_engine_code OKL_FE_STD_RT_TMP_V.PRICING_ENGINE_CODE%TYPE,
279 rate_type_code OKL_FE_STD_RT_TMP_V.RATE_TYPE_CODE%TYPE,
280 srt_rate NUMBER,
281 index_id NUMBER,
282 spread NUMBER,
283 day_convention_code OKL_FE_STD_RT_TMP_VERS.DAY_CONVENTION_CODE%TYPE,
284 frequency_code OKL_FE_STD_RT_TMP_V.FREQUENCY_CODE%TYPE,
285 adj_mat_version_id NUMBER,
286 min_adj_rate OKL_FE_STD_RT_TMP_VERS.MIN_ADJ_RATE%TYPE,
287 max_adj_rate OKL_FE_STD_RT_TMP_VERS.MAX_ADJ_RATE%TYPE);
288
289 TYPE adj_mat_cat_rec IS RECORD (
290 target_eff_from date,
291 term number,
292 territory varchar2(240),
293 deal_size number,
294 customer_credit_class varchar2(240)
295 );
296
297
298 -- Record representing the Yields
299 TYPE yields_rec IS RECORD (
300 pre_tax_irr NUMBER,
301 after_tax_irr NUMBER,
302 bk_yield NUMBER,
303 iir NUMBER,
304 pre_tax_irr_flag VARCHAR2(1) DEFAULT 'N',
305 after_tax_irr_flag VARCHAR2(1) DEFAULT 'N',
306 bk_yield_flag VARCHAR2(1) DEFAULT 'N',
307 iir_flag VARCHAR2(1) DEFAULT 'N'
308 );
309
310 --------------------------------------------------------------------------------
311 -- Start of Commnets
312 -- Procedure Name : get_so_hdr
313 -- Description : Wrapper API to fetch the Header level information
314 -- Business Rules :
315 -- Parameters :
316 -- p_so_id - Id of Qucik Quote/Standard Quote
317 -- p_so_type - QQ for Quick Quote/ SQ for Lease Quotes
318 -- Version : 1.0
319 -- History : rgooty 15-May-2005 - created
320 -- End of Commnets
321 --------------------------------------------------------------------------------
322 PROCEDURE get_so_hdr(
323 p_api_version IN NUMBER,
324 p_init_msg_list IN VARCHAR2,
325 x_return_status OUT NOCOPY VARCHAR2,
326 x_msg_count OUT NOCOPY NUMBER,
327 x_msg_data OUT NOCOPY VARCHAR2,
328 p_so_id IN NUMBER,
329 p_so_type IN VARCHAR2,
330 x_so_hdr_rec OUT NOCOPY so_hdr_rec_type);
331
332 --------------------------------------------------------------------------------
333 -- Start of Commnets
334 -- Procedure Name : get_qq_fin_adjustments
335 -- Description : API to fetch the Financial Adjustments Information like
336 -- Down Payment, Subsidy, Trade in, Item Category Amount
337 -- Business Rules :
338 -- Parameters : p_qq_id - Id of the Quick Quote
339 -- Version : 1.0
340 -- History : rgooty 15-May-2005 - created
341 -- End of Commnets
342 --------------------------------------------------------------------------------
343 PROCEDURE get_qq_fin_adj_details(
344 p_api_version IN NUMBER,
345 p_init_msg_list IN VARCHAR2,
346 x_return_status OUT NOCOPY VARCHAR2,
347 x_msg_count OUT NOCOPY NUMBER,
348 x_msg_data OUT NOCOPY VARCHAR2,
349 p_qq_id IN NUMBER,
350 p_pricing_method IN VARCHAR2,
351 p_item_category_amount IN NUMBER,
352 x_all_amounts_rec OUT NOCOPY so_amt_details_rec_type);
353
354 --------------------------------------------------------------------------------
358 -- Business Rules :
355 -- Start of Commnets
356 -- Procedure Name : get_qq_sgt_day_convention
357 -- Description : API to fetch the day convention from the SGT assosiated
359 -- Parameters : p_qq_id - Id of the Quick Quote
360 -- Version : 1.0
361 -- History : rgooty 15-May-2005 - created
362 -- End of Commnets
363 --------------------------------------------------------------------------------
364 PROCEDURE get_qq_sgt_day_convention(
365 p_api_version IN NUMBER,
366 p_init_msg_list IN VARCHAR2,
367 x_return_status OUT NOCOPY VARCHAR2,
368 x_msg_count OUT NOCOPY NUMBER,
369 x_msg_data OUT NOCOPY VARCHAR2,
370 p_qq_id IN NUMBER,
371 x_days_in_month OUT NOCOPY VARCHAR2,
372 x_days_in_year OUT NOCOPY VARCHAR2);
373
374 --------------------------------------------------------------------------------
375 -- Start of Commnets
376 -- Procedure Name : get_qq_cash_flows
377 -- Description : API to fetch the Cash Flows, Cash Flow during
378 -- Structured Pricing !
379 -- Business Rules :
380 -- Parameters : p_qq_id - Id of the Quick Quote
381 -- Version : 1.0
382 -- History : rgooty 8-June-2005 - created
383 -- End of Commnets
384 --------------------------------------------------------------------------------
385 PROCEDURE get_qq_cash_flows(
386 p_api_version IN NUMBER,
387 p_init_msg_list IN VARCHAR2,
388 x_return_status OUT NOCOPY VARCHAR2,
389 x_msg_count OUT NOCOPY NUMBER,
390 x_msg_data OUT NOCOPY VARCHAR2,
391 p_cf_source_type IN VARCHAR2,
392 p_qq_id IN NUMBER,
393 x_days_in_month OUT NOCOPY VARCHAR2,
394 x_days_in_year OUT NOCOPY VARCHAR2,
395 x_cash_flow_rec OUT NOCOPY so_cash_flows_rec_type,
396 x_cash_flow_det_tbl OUT NOCOPY so_cash_flow_details_tbl_type);
397
398 --------------------------------------------------------------------------------
399 -- Start of Commnets
400 -- Procedure Name : get_qq_cash_flows
401 -- Description : API to fetch/build the Cash Flows, Cash Flow Levels
402 -- during Structured Pricing/LRS/SRT !
403 -- Business Rules :
404 -- Parameters : p_qq_id - Id of the Quick Quote
405 -- Version : 1.0
406 -- History : rgooty 8-June-2005 - created
407 -- End of Commnets
408 --------------------------------------------------------------------------------
409 PROCEDURE get_qq_cash_flows(
410 p_api_version IN NUMBER,
411 p_init_msg_list IN VARCHAR2,
412 x_return_status OUT NOCOPY VARCHAR2,
413 x_msg_count OUT NOCOPY NUMBER,
414 x_msg_data OUT NOCOPY VARCHAR2,
415 p_qq_hdr_rec IN so_hdr_rec_type,
416 p_eot_percentage IN NUMBER,
417 p_oec IN NUMBER,
418 x_days_in_month OUT NOCOPY VARCHAR2,
419 x_days_in_year OUT NOCOPY VARCHAR2,
420 x_cash_flow_rec OUT NOCOPY so_cash_flows_rec_type,
421 x_cash_flow_det_tbl OUT NOCOPY so_cash_flow_details_tbl_type);
422
423 --------------------------------------------------------------------------------
424 -- Start of Commnets
425 -- Procedure Name : gen_so_cash_inflows_strms
426 -- Description : API to generate Cash inflows based on the Cash Flows
427 -- inputted.
428 -- Business Rules :
429 -- Parameters : p_qq_id - Id of the Quick Quote
430 -- Version : 1.0
431 -- History : rgooty 15-May-2005 - created
432 -- End of Commnets
433 --------------------------------------------------------------------------------
434 PROCEDURE gen_so_cf_strms(
435 p_api_version IN NUMBER,
436 p_init_msg_list IN VARCHAR2,
437 x_return_status OUT NOCOPY VARCHAR2,
438 x_msg_count OUT NOCOPY NUMBER,
439 x_msg_data OUT NOCOPY VARCHAR2,
440 p_cash_flow_rec IN so_cash_flows_rec_type,
441 p_cf_details_tbl IN so_cash_flow_details_tbl_type,
442 x_cash_inflow_strms_tbl OUT NOCOPY cash_inflows_tbl_type);
443
444 --------------------------------------------------------------------------------
445 -- Start of Commnets
446 -- Procedure Name : get_day_count
447 -- Description : Counts the number of days between start and end Dates.
448 -- Business Rules : Based on a profile, the months are treated either normal
449 -- or 30 days month.
450 -- Parameters : p_start_date - Start Date
451 -- p_end_date - End Date
452 -- p_arrears - Arrears/Advance Flag
453 -- p_conversion_factor - Conversion Factor when days in month is 30
454 -- Version : 1.0
455 -- History : rgooty 15-May-2005 - created
456 -- rpillay 23-Nov-2012 - Bug# 14165508 Added parameter
457 -- p_conversion_factor
458 -- End of Commnets
459 --------------------------------------------------------------------------------
460 FUNCTION GET_DAY_COUNT(
461 p_days_in_month IN VARCHAR2,
462 p_days_in_year IN VARCHAR2,
463 p_start_date IN DATE,
464 p_end_date IN DATE,
465 p_arrears IN VARCHAR2,
466 x_return_status OUT NOCOPY VARCHAR2,
467 p_conversion_factor IN NUMBER DEFAULT NULL)
468 RETURN NUMBER;
469
470 --------------------------------------------------------------------------------
471 -- Start of Commnets
472 -- Procedure Name : get_day_convention
473 -- Description : Get the day convention either from OKL_K_RATE_PARAMS or
474 -- reach the SGT and fetch the day conventions
475 -- Business Rules :
476 -- Parameters :
477 -- Version : 1.0
478 -- History : rgooty - created
479 -- End of Commnets
480 --------------------------------------------------------------------------------
481 PROCEDURE get_day_convention(
482 p_id IN NUMBER, -- ID of the contract/quote
483 p_source IN VARCHAR2, -- 'ESG'/'ISG' are acceptable values
484 x_days_in_month OUT NOCOPY VARCHAR2,
485 x_days_in_year OUT NOCOPY VARCHAR2,
486 x_return_status OUT NOCOPY VARCHAR2);
487
488 --------------------------------------------------------------------------------
489 -- Start of Commnets
490 -- Procedure Name : compute_irr
491 -- Description :
492 -- Business Rules :
493 -- Parameters :
494 -- Version : 1.0
495 -- History : rgooty 15-May-2005 - created
496 -- End of Commnets
497 --------------------------------------------------------------------------------
498 PROCEDURE compute_irr(
499 p_api_version IN NUMBER,
500 p_init_msg_list IN VARCHAR2,
501 x_return_status OUT NOCOPY VARCHAR2,
502 x_msg_count OUT NOCOPY NUMBER,
503 x_msg_data OUT NOCOPY VARCHAR2,
504 p_start_date IN DATE,
505 p_day_count_method IN VARCHAR2,
506 p_currency_code IN VARCHAR2,
507 p_pricing_method IN VARCHAR2,
508 p_initial_guess IN NUMBER,
509 px_pricing_parameter_tbl IN OUT NOCOPY pricing_parameter_tbl_type,
510 px_irr IN OUT NOCOPY NUMBER,
511 x_payment OUT NOCOPY NUMBER);
512
513 --------------------------------------------------------------------------------
514 -- Start of Commnets
515 -- Procedure Name : compute_iir
516 -- Description :
517 -- Business Rules :
518 -- Parameters :
519 -- Version : 1.0
520 -- History : rgooty 15-May-2005 - created
521 -- End of Commnets
522 --------------------------------------------------------------------------------
523 PROCEDURE compute_iir(
524 p_api_version IN NUMBER,
525 p_init_msg_list IN VARCHAR2,
526 x_return_status OUT NOCOPY VARCHAR2,
527 x_msg_count OUT NOCOPY NUMBER,
528 x_msg_data OUT NOCOPY VARCHAR2,
529 p_start_date IN DATE,
530 p_day_count_method IN VARCHAR2,
531 p_pricing_method IN VARCHAR2,
532 p_initial_guess IN NUMBER,
533 px_pricing_parameter_rec IN OUT NOCOPY pricing_parameter_rec_type,
534 px_iir IN OUT NOCOPY NUMBER,
535 x_payment OUT NOCOPY NUMBER);
536
537 --------------------------------------------------------------------------------
538 -- Start of Commnets
539 -- Procedure Name : compute_iir
540 -- Description :
541 -- Business Rules :
542 -- Parameters :
543 -- Version : 1.0
544 -- History : rgooty 18-FEB-2006 Created
545 -- End of Commnets
546 --------------------------------------------------------------------------------
547 PROCEDURE compute_iir_sfp(
548 p_api_version IN NUMBER,
549 p_init_msg_list IN VARCHAR2,
550 x_return_status OUT NOCOPY VARCHAR2,
554 p_day_count_method IN VARCHAR2,
551 x_msg_count OUT NOCOPY NUMBER,
552 x_msg_data OUT NOCOPY VARCHAR2,
553 p_start_date IN DATE,
555 p_pricing_method IN VARCHAR2,
556 p_initial_guess IN NUMBER,
557 px_pricing_parameter_rec IN OUT NOCOPY pricing_parameter_rec_type,
558 px_iir IN OUT NOCOPY NUMBER,
559 x_closing_balance OUT NOCOPY NUMBER,
560 x_residual_percent OUT NOCOPY NUMBER,
561 x_residual_int_factor OUT NOCOPY NUMBER);
562 --------------------------------------------------------------------------------
563 -- Start of Commnets
564 -- Procedure Name : get_lease_rate_factors
565 -- Description :
566 -- Business Rules :
567 -- Parameters :
568 -- Version : 1.0
569 -- History : rgooty 6-June-2005 - created
570 -- End of Commnets
571 --------------------------------------------------------------------------------
572 PROCEDURE get_lease_rate_factors(
573 p_api_version IN NUMBER,
574 p_init_msg_list IN VARCHAR2,
575 x_return_status OUT NOCOPY VARCHAR2,
576 x_msg_count OUT NOCOPY NUMBER,
577 x_msg_data OUT NOCOPY VARCHAR2,
578 p_lrt_id IN NUMBER, -- Assuming LRS Version ID
579 p_start_date IN DATE,
580 p_term_in_months IN NUMBER,
581 p_eot_percentage IN NUMBER,
582 x_lrs_details OUT NOCOPY lrs_details_rec_type,
583 x_lrs_factor OUT NOCOPY lrs_factor_rec_type,
584 x_lrs_levels OUT NOCOPY lrs_levels_tbl_type);
585
586 --------------------------------------------------------------------------------
587 -- Start of Commnets
588 -- Procedure Name : get_standard_rates
589 -- Description :
590 -- Business Rules :
591 -- Parameters :
592 -- Version : 1.0
593 -- History : rgooty 6-June-2005 - created
594 -- End of Commnets
595 --------------------------------------------------------------------------------
596 PROCEDURE get_standard_rates(
597 p_api_version IN NUMBER,
598 p_init_msg_list IN VARCHAR2,
599 x_return_status OUT NOCOPY VARCHAR2,
600 x_msg_count OUT NOCOPY NUMBER,
601 x_msg_data OUT NOCOPY VARCHAR2,
602 p_srt_id IN NUMBER, -- Version ID
603 p_start_date IN DATE,
604 x_srt_details OUT NOCOPY srt_details_rec_type);
605
606
607 --------------------------------------------------------------------------------
608 -- Start of Commnets
609 -- Procedure Name : compute_bk_yield
610 -- Description :
611 -- Business Rules :
612 -- Parameters :
613 -- Version : 1.0
614 -- History : rgooty 20-June-2005 - created
615 -- End of Commnets
616 --------------------------------------------------------------------------------
617 PROCEDURE compute_bk_yield(
618 p_api_version IN NUMBER,
619 p_init_msg_list IN VARCHAR2,
620 x_return_status OUT NOCOPY VARCHAR2,
621 x_msg_count OUT NOCOPY NUMBER,
622 x_msg_data OUT NOCOPY VARCHAR2,
623 p_start_date IN DATE,
624 p_day_count_method IN VARCHAR2,
625 p_pricing_method IN VARCHAR2,
626 p_initial_guess IN NUMBER,
627 p_term IN NUMBER,
628 px_pricing_parameter_rec IN OUT NOCOPY pricing_parameter_rec_type,
629 x_bk_yield IN OUT NOCOPY NUMBER,
630 x_termination_tbl OUT NOCOPY cash_inflows_tbl_type,
631 x_pre_tax_inc_tbl OUT NOCOPY cash_inflows_tbl_type
632 -- Parameters for Prospective Rebooking
633 ,p_prosp_rebook_flag IN VARCHAR2
634 ,p_rebook_date IN DATE
635 ,p_orig_income_streams IN cash_inflows_tbl_type
636 );
637
638 --------------------------------------------------------------------------------
639 -- Start of Commnets
640 -- Procedure Name : price_quick_quote
641 -- Description :
642 -- Business Rules :
643 -- Parameters :
644 -- Version : 1.0
645 -- History : rgooty 6-June-2005 - created
646 -- End of Commnets
647 --------------------------------------------------------------------------------
648 PROCEDURE price_quick_quote(
649 p_api_version IN NUMBER,
650 p_init_msg_list IN VARCHAR2,
651 x_return_status OUT NOCOPY VARCHAR2,
652 x_msg_count OUT NOCOPY NUMBER,
653 x_msg_data OUT NOCOPY VARCHAR2,
657 x_pricing_results_tbl OUT NOCOPY pricing_results_tbl_type);
654 p_qq_id IN NUMBER,
655 x_yileds_rec OUT NOCOPY yields_rec,
656 x_subsidized_yileds_rec OUT NOCOPY yields_rec,
658 --------------------------------------------------------------------------------
659 -- Start of Commnets
660 -- Procedure Name : get_days_per_annum
661 -- Description :
662 -- Business Rules :
663 -- Parameters :
664 -- Version : 1.0
665 -- History : rgooty 6-June-2005 - created
666 -- End of Commnets
667 --------------------------------------------------------------------------------
668 FUNCTION get_days_per_annum(
669 p_day_convention IN VARCHAR2,
670 p_start_date IN DATE,
671 p_cash_inflow_date IN DATE,
672 x_return_status OUT NOCOPY VARCHAR2 )
673 RETURN NUMBER;
674
675 --------------------------------------------------------------------------------
676 -- Start of Commnets
677 -- Procedure Name : get_qq_asset_oec
678 -- Description : Calculates the OEC
679 -- Business Rules :
680 -- Parameters :
681 -- Version : 1.0
682 -- History : rgooty 26-July-2005 - created
683 -- End of Commnets
684 --------------------------------------------------------------------------------
685 PROCEDURE get_qq_asset_oec (
686 p_api_version IN NUMBER,
687 p_init_msg_list IN VARCHAR2,
688 x_return_status OUT NOCOPY VARCHAR2,
689 x_msg_count OUT NOCOPY NUMBER,
690 x_msg_data OUT NOCOPY VARCHAR2,
691 p_asset_cost IN NUMBER,
692 p_fin_adj_det_rec IN so_amt_details_rec_type,
693 x_oec OUT NOCOPY NUMBER);
694
695 --------------------------------------------------------------------------------
696 -- Start of Commnets
697 -- Procedure Name : get_lq_cash_flows
698 -- Description :
699 -- Business Rules :
700 -- Parameters :
701 -- Version : 1.0
702 -- History : rgooty 3-Aug-2005 - created
703 -- End of Commnets
704 --------------------------------------------------------------------------------
705 PROCEDURE get_lq_cash_flows(
706 p_api_version IN NUMBER,
707 p_init_msg_list IN VARCHAR2,
708 x_return_status OUT NOCOPY VARCHAR2,
709 x_msg_count OUT NOCOPY NUMBER,
710 x_msg_data OUT NOCOPY VARCHAR2,
711 p_id IN NUMBER,
712 p_lq_srt_id IN NUMBER,
713 p_cf_source IN VARCHAR2,
714 p_adj_mat_cat_rec IN adj_mat_cat_rec,
715 p_pricing_method IN VARCHAR2,
716 x_days_in_month OUT NOCOPY VARCHAR2,
717 x_days_in_year OUT NOCOPY VARCHAR2,
718 x_cash_flow_rec OUT NOCOPY so_cash_flows_rec_type,
719 x_cash_flow_det_tbl OUT NOCOPY so_cash_flow_details_tbl_type);
720
721 --------------------------------------------------------------------------------
722 -- Start of Commnets
723 -- Procedure Name : distribute_fin_amount_lq
724 -- Description :
725 -- Business Rules :
726 -- Parameters :
727 -- Version : 1.0
728 -- History : rgooty 13-Aug-2005 - created
729 -- End of Commnets
730 --------------------------------------------------------------------------------
731 FUNCTION is_asset_overriding( p_qte_id IN NUMBER,
732 p_ast_id IN NUMBER,
733 p_lq_line_level_pricing IN VARCHAR2,
734 p_lq_srt_id IN NUMBER,
735 p_ast_srt_id IN NUMBER,
736 p_lq_struct_pricing IN VARCHAR2,
737 p_ast_struct_pricing IN VARCHAR2,
738 p_lq_arrears_yn IN VARCHAR2,
739 p_ast_arrears_yn IN VARCHAR2,
740 x_return_status OUT NOCOPY VARCHAR2)
741 RETURN BOOLEAN;
742
743 --------------------------------------------------------------------------------
744 -- Start of Commnets
745 -- Procedure Name : distribute_fin_amount_lq
746 -- Description :
747 -- Business Rules :
748 -- Parameters :
749 -- Version : 1.0
750 -- History : rgooty 13-Aug-2005 - created
751 -- End of Commnets
752 --------------------------------------------------------------------------------
753 PROCEDURE distribute_fin_amount_lq(
754 p_api_version IN NUMBER,
755 p_init_msg_list IN VARCHAR2,
756 x_return_status OUT NOCOPY VARCHAR2,
757 x_msg_count OUT NOCOPY NUMBER,
758 x_msg_data OUT NOCOPY VARCHAR2,
759 p_lq_id IN NUMBER,
760 p_tot_fin_amount IN NUMBER);
761
762 --------------------------------------------------------------------------------
763 -- Start of Commnets
764 -- Procedure Name : Price_Standard_Quote
765 -- Description :
766 -- Business Rules :
767 -- Parameters :
768 -- Version : 1.0
769 -- History : ssiruvol 22-May-2005 - created
770 -- End of Commnets
771 --------------------------------------------------------------------------------
772 PROCEDURE price_standard_quote_asset(
773 x_return_status OUT NOCOPY VARCHAR2,
774 x_msg_count OUT NOCOPY NUMBER,
775 x_msg_data OUT NOCOPY VARCHAR2,
776 p_api_version IN NUMBER,
777 p_init_msg_list IN VARCHAR2,
778 p_qte_id IN NUMBER,
779 p_ast_id IN NUMBER,
780 p_price_at_lq_level IN BOOLEAN,
781 p_target_rate IN NUMBER,
782 p_line_type IN VARCHAR2,
783 x_pricing_parameter_rec IN OUT NOCOPY pricing_parameter_rec_type);
784 --------------------------------------------------------------------------------
785 -- Start of Commnets
786 -- Procedure Name : Price_Standard_Quote
787 -- Description :
788 -- Business Rules :
789 -- Parameters :
790 -- Version : 1.0
791 -- History : ssiruvol 22-May-2005 - created
792 -- End of Commnets
793 --------------------------------------------------------------------------------
794 PROCEDURE price_standard_quote(x_return_status OUT NOCOPY VARCHAR2,
795 x_msg_count OUT NOCOPY NUMBER,
796 x_msg_data OUT NOCOPY VARCHAR2,
797 p_api_version IN NUMBER,
798 p_init_msg_list IN VARCHAR2,
799 p_qte_id IN NUMBER);
800
801 --------------------------------------------------------------------------------
802 -- Start of Commnets
803 -- Procedure Name : get_day_count_method
804 -- Description :
805 -- Business Rules :
806 -- Parameters :
807 -- Version : 1.0
808 -- History : rgooty 20-Feb-2009 - Published in the Spec
809 -- End of Commnets
810 --------------------------------------------------------------------------------
811 PROCEDURE get_day_count_method(
812 p_days_in_month IN VARCHAR2,
813 p_days_in_year IN VARCHAR2,
814 x_day_count_method OUT NOCOPY VARCHAR2,
815 x_return_status OUT NOCOPY VARCHAR2 );
816 END OKL_PRICING_UTILS_PVT;