1 PACKAGE OKL_STREAM_GENERATOR_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKLRSGPS.pls 120.17.12010000.2 2009/08/14 05:23:52 sechawla ship $ */
3
4 -----------------------------------------------------------------------------
5 -- PACKAGE SPECIFIC CONSTANTS
6 -----------------------------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKL_STREAM_GENERATOR_PVT';
8
9 -----------------------------------------------------------------------------
10 -- APPLICATION GLOBAL CONSTANTS
11 -----------------------------------------------------------------------------
12 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
13 G_API_VERSION CONSTANT NUMBER := 1;
14 G_FALSE CONSTANT VARCHAR2(1) := OKL_API.G_FALSE;
15 G_TRUE CONSTANT VARCHAR2(1) := OKL_API.G_TRUE;
16 G_DB_ERROR CONSTANT VARCHAR2(12) := 'OKL_DB_ERROR';
17 G_PROG_NAME_TOKEN CONSTANT VARCHAR2(9) := 'PROG_NAME';
18 G_SQLCODE_TOKEN CONSTANT VARCHAR2(7) := 'SQLCODE';
19 G_SQLERRM_TOKEN CONSTANT VARCHAR2(7) := 'SQLERRM';
20 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
21 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_UNEXP_ERROR;
22 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
23
24 TYPE cash_flow_rec IS RECORD (cf_date DATE, cf_amount NUMBER, cf_frequency VARCHAR2(1));
25 TYPE cash_flow_tbl IS TABLE OF cash_flow_rec INDEX BY BINARY_INTEGER;
26
27 TYPE payment_rec_type IS RECORD (sll_id NUMBER,
28 start_date DATE,
29 periods NUMBER,
30 frequency VARCHAR2(1),
31 structure VARCHAR2(1),
32 arrears_yn VARCHAR2(1),
33 amount NUMBER,
34 stub_days NUMBER,
35 stub_amount NUMBER,
36 rate NUMBER);
37
38 TYPE payment_tbl_type IS TABLE OF payment_rec_type INDEX BY BINARY_INTEGER;
39
40 TYPE rate_rec_type IS RECORD ( PRE_TAX_IRR NUMBER,
41 IMPLICIT_INTEREST_RATE NUMBER,
42 SUB_IMPL_INTEREST_RATE NUMBER,
43 SUB_PRE_TAX_IRR NUMBER,
44 SUB_PRE_TAX_YIELD NUMBER,
45 PRE_TAX_YIELD NUMBER);
46
47 CURSOR top_svc_csr ( chrId NUMBER, linkId NUMBER ) IS
48 select to_char(kle1.id) top_svc_id,
49 kle1.amount top_amount,
50 kle.amount link_amount
51 from okl_k_lines_full_v kle,
52 okl_k_lines_full_v kle1,
53 okc_line_styles_b lse,
54 okc_statuses_b sts
55 where KLE1.LSE_ID = LSE.ID
56 and ((lse.lty_code = 'SOLD_SERVICE') OR (lse.lty_code = 'FEE'and kle1.fee_type ='PASSTHROUGH')) -- linked fees on passthrus.
57 and kle.dnz_chr_id = chrId
58 and kle1.dnz_chr_id = kle.dnz_chr_id
59 and sts.code = kle1.sts_code
60 and kle.id = linkId
61 and kle.cle_id = kle1.id
62 and sts.ste_code not in ('HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
63
64 ---------------------------------------------------------------------------
65 -- PROGRAM UNITS
66 ---------------------------------------------------------------------------
67 -- Bug 4590581: Start
68 FUNCTION get_months_factor( p_frequency IN VARCHAR2,
69 x_return_status OUT NOCOPY VARCHAR2)
70 RETURN NUMBER;
71
72 PROCEDURE add_months_new( p_start_date IN DATE,
73 p_months_after IN NUMBER,
74 x_date OUT NOCOPY DATE,
75 x_return_status OUT NOCOPY VARCHAR2);
76
77 --Added parameter p_recurrence_date by djanaswa for bug 6007644
78 -- Added parameter p_arrears_pay_dates_option DJANASWA ER6274342
79 PROCEDURE get_sel_date( p_start_date IN DATE,
80 p_advance_or_arrears IN VARCHAR2,
81 p_periods_after IN NUMBER,
82 p_months_per_period IN NUMBER,
83 x_date OUT NOCOPY DATE,
84 x_return_status OUT NOCOPY VARCHAR2,
85 p_recurrence_date IN DATE,
86 p_arrears_pay_dates_option IN VARCHAR2 DEFAULT 'LAST_DAY_OF_PERIOD');
87 -- Bug 4590581: End
88
89 PROCEDURE get_sty_details (p_sty_id IN NUMBER DEFAULT NULL,
90 p_sty_name IN VARCHAR2 DEFAULT NULL,
91 x_sty_id OUT NOCOPY NUMBER,
92 x_sty_name OUT NOCOPY VARCHAR2,
93 x_return_status OUT NOCOPY VARCHAR2);
94
95 FUNCTION get_day_count (p_start_date IN DATE,
96 p_end_date IN DATE,
97 p_arrears IN VARCHAR2,
98 x_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER;
99
100 PROCEDURE get_stream_header(p_purpose_code IN VARCHAR2,
101 p_khr_id IN NUMBER,
102 p_kle_id IN NUMBER,
103 p_sty_id IN NUMBER,
104 x_stmv_rec OUT NOCOPY okl_stm_pvt.stmv_rec_type,
105 x_return_status OUT NOCOPY VARCHAR2);
106
107 --Added parameter p_recurrence_date by djanaswa for bug 6007644
108 PROCEDURE get_stream_elements( p_start_date IN DATE,
109 p_periods IN NUMBER,
110 p_frequency IN VARCHAR2,
111 p_structure IN VARCHAR2,
112 p_advance_or_arrears IN VARCHAR2,
113 p_amount IN NUMBER,
114 p_stub_days IN NUMBER,
115 p_stub_amount IN NUMBER,
116 p_currency_code IN VARCHAR2,
117 p_khr_id IN NUMBER,
118 p_kle_id IN NUMBER,
119 p_purpose_code IN VARCHAR2,
120 x_selv_tbl OUT NOCOPY okl_sel_pvt.selv_tbl_type,
121 x_pt_tbl OUT NOCOPY okl_sel_pvt.selv_tbl_type,
122 x_return_status OUT NOCOPY VARCHAR2,
123 x_msg_count OUT NOCOPY NUMBER,
124 x_msg_data OUT NOCOPY VARCHAR2,
125 p_recurrence_date IN DATE);
126
127 -- gboomina Bug 4746189 - Added - Start
128 --Added parameter p_recurrence_date by djanaswa for bug 6007644
129 PROCEDURE get_stream_elements( p_start_date IN DATE,
130 p_periods IN NUMBER,
131 p_frequency IN VARCHAR2,
132 p_structure IN VARCHAR2,
133 p_advance_or_arrears IN VARCHAR2,
134 p_amount IN NUMBER,
135 p_stub_days IN NUMBER,
136 p_stub_amount IN NUMBER,
137 p_currency_code IN VARCHAR2,
138 p_khr_id IN NUMBER,
139 p_kle_id IN NUMBER,
140 p_purpose_code IN VARCHAR2,
141 x_selv_tbl OUT NOCOPY okl_sel_pvt.selv_tbl_type,
142 x_pt_tbl OUT NOCOPY okl_sel_pvt.selv_tbl_type,
143 x_pt_pro_fee_tbl OUT NOCOPY okl_sel_pvt.selv_tbl_type,
144 x_return_status OUT NOCOPY VARCHAR2,
145 x_msg_count OUT NOCOPY NUMBER,
146 x_msg_data OUT NOCOPY VARCHAR2,
147 p_recurrence_date IN DATE);
148 -- gboomina Bug 4746189 - Added - End
149
150 PROCEDURE get_accrual_elements (p_start_date IN DATE,
151 p_periods IN NUMBER,
152 p_frequency IN VARCHAR2,
153 p_structure IN NUMBER,
154 p_advance_or_arrears IN VARCHAR2,
155 p_amount IN NUMBER,
156 p_stub_days IN NUMBER,
157 p_stub_amount IN NUMBER,
158 p_currency_code IN VARCHAR2,
159 p_day_convention_month IN VARCHAR2 DEFAULT '30',
160 p_day_convention_year IN VARCHAR2 DEFAULT '360',
161 x_selv_tbl OUT NOCOPY okl_streams_pub.selv_tbl_type,
162 x_return_status OUT NOCOPY VARCHAR2);
163
164 PROCEDURE generate_cash_flows(
165 p_api_version IN NUMBER,
166 p_init_msg_list IN VARCHAR2,
167 p_khr_id IN NUMBER,
168 p_kle_id IN NUMBER,
169 p_sty_id IN NUMBER,
170 p_payment_tbl IN payment_tbl_type,
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2,
174 x_payment_count OUT NOCOPY BINARY_INTEGER);
175
176 -- Added output parameter x_se_id by prasjain for bug 5474827
177 PROCEDURE generate_cash_flows( p_api_version IN NUMBER,
178 p_init_msg_list IN VARCHAR2,
179 p_khr_id IN NUMBER,
180 p_generation_type IN VARCHAR2,
181 p_reporting_book_class IN VARCHAR2,
182 x_payment_count OUT NOCOPY BINARY_INTEGER,
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_msg_count OUT NOCOPY NUMBER,
185 x_msg_data OUT NOCOPY VARCHAR2,
186 x_se_id OUT NOCOPY NUMBER);
187
188 PROCEDURE generate_streams( p_api_version IN NUMBER,
189 p_init_msg_list IN VARCHAR2,
190 p_khr_id IN NUMBER,
191 p_compute_rates IN VARCHAR2,
192 p_generation_type IN VARCHAR2,
193 p_reporting_book_class IN VARCHAR2,
194 x_contract_rates OUT NOCOPY rate_rec_type,
195 x_return_status OUT NOCOPY VARCHAR2,
196 x_msg_count OUT NOCOPY NUMBER,
197 x_msg_data OUT NOCOPY VARCHAR2);
198
199 PROCEDURE generate_streams( p_api_version IN NUMBER,
200 p_init_msg_list IN VARCHAR2,
201 p_khr_id IN NUMBER,
202 p_compute_irr IN VARCHAR2,
203 p_generation_type IN VARCHAR2,
204 p_reporting_book_class IN VARCHAR2,
205 x_pre_tax_irr OUT NOCOPY NUMBER,
206 x_return_status OUT NOCOPY VARCHAR2,
207 x_msg_count OUT NOCOPY NUMBER,
208 x_msg_data OUT NOCOPY VARCHAR2);
209
210 PROCEDURE GEN_VAR_INT_SCHEDULE( p_api_version IN NUMBER,
211 p_init_msg_list IN VARCHAR2,
212 p_khr_id IN NUMBER,
213 p_purpose_code IN VARCHAR2,
214 x_return_status OUT NOCOPY VARCHAR2,
215 x_msg_count OUT NOCOPY NUMBER,
216 x_msg_data OUT NOCOPY VARCHAR2);
217
218 PROCEDURE create_pv_streams(p_api_version IN NUMBER,
219 p_init_msg_list IN VARCHAR2,
220 p_agreement_id IN NUMBER,
221 p_pool_status IN VARCHAR2 DEFAULT 'NEW',
222 /* sosharma 14-12-2007 , added for passing mode as Pending
223 for contents added on active pool*/
224 p_mode IN VARCHAR2 DEFAULT NULL,
225 x_return_status OUT NOCOPY VARCHAR2,
226 x_msg_count OUT NOCOPY NUMBER,
227 x_msg_data OUT NOCOPY VARCHAR2);
228
229 PROCEDURE create_pv_streams(p_api_version IN NUMBER,
230 p_init_msg_list IN VARCHAR2,
231 p_contract_id IN NUMBER,
232 p_pool_status IN VARCHAR2 DEFAULT 'ACTIVE',
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_msg_count OUT NOCOPY NUMBER,
235 x_msg_data OUT NOCOPY VARCHAR2);
236
237
238 PROCEDURE create_disb_streams(p_api_version IN NUMBER,
239 p_init_msg_list IN VARCHAR2,
240 p_agreement_id IN NUMBER,
241 p_pool_status IN VARCHAR2 DEFAULT 'NEW',
242 /* sosharma 14-12-2007 , added for passing mode as Pending
243 for contents added on active pool*/
244 p_mode IN VARCHAR2 DEFAULT NULL,
245 x_return_status OUT NOCOPY VARCHAR2,
246 x_msg_count OUT NOCOPY NUMBER,
247 x_msg_data OUT NOCOPY VARCHAR2);
248
249 PROCEDURE create_disb_streams(p_api_version IN NUMBER,
250 p_init_msg_list IN VARCHAR2,
251 p_contract_id IN NUMBER,
252 p_pool_status IN VARCHAR2 DEFAULT 'ACTIVE',
253 x_return_status OUT NOCOPY VARCHAR2,
254 x_msg_count OUT NOCOPY NUMBER,
255 x_msg_data OUT NOCOPY VARCHAR2);
256
257 PROCEDURE get_sched_principal_bal( p_api_version IN NUMBER,
258 p_init_msg_list IN VARCHAR2,
259 p_khr_id IN NUMBER,
260 p_kle_id IN NUMBER DEFAULT NULL,
261 p_date IN DATE,
262 x_principal_balance OUT NOCOPY NUMBER,
263 x_accumulated_int OUT NOCOPY NUMBER,
264 x_return_status OUT NOCOPY VARCHAR2,
265 x_msg_count OUT NOCOPY NUMBER,
266 x_msg_data OUT NOCOPY VARCHAR2);
267
268
269 PROCEDURE get_next_billing_date( p_api_version IN NUMBER,
270 p_init_msg_list IN VARCHAR2,
271 p_khr_id IN NUMBER,
272 p_billing_date IN DATE DEFAULT NULL,
273 x_next_due_date OUT NOCOPY DATE,
274 x_next_period_start_date OUT NOCOPY DATE,
275 x_next_period_end_date OUT NOCOPY DATE,
276 x_return_status OUT NOCOPY VARCHAR2,
277 x_msg_count OUT NOCOPY NUMBER,
278 x_msg_data OUT NOCOPY VARCHAR2);
279
280
281 PROCEDURE get_present_value( p_api_version IN NUMBER,
282 p_init_msg_list IN VARCHAR2,
283 p_amount_date IN DATE,
284 p_amount IN NUMBER,
285 p_frequency IN VARCHAR2 DEFAULT 'M',
286 p_rate IN NUMBER,
287 p_pv_date IN DATE,
288 p_day_convention_month IN VARCHAR2 DEFAULT '30',
289 p_day_convention_year IN VARCHAR2 DEFAULT '360',
290 x_pv_amount OUT NOCOPY NUMBER,
291 x_return_status OUT NOCOPY VARCHAR2,
292 x_msg_count OUT NOCOPY NUMBER,
293 x_msg_data OUT NOCOPY VARCHAR2);
294
295 PROCEDURE get_present_value( p_api_version IN NUMBER,
296 p_init_msg_list IN VARCHAR2,
297 p_cash_flow_tbl IN cash_flow_tbl,
298 p_rate IN NUMBER,
299 p_pv_date IN DATE,
300 p_day_convention_month IN VARCHAR2 DEFAULT '30',
301 p_day_convention_year IN VARCHAR2 DEFAULT '360',
302 x_pv_amount OUT NOCOPY NUMBER,
303 x_return_status OUT NOCOPY VARCHAR2,
304 x_msg_count OUT NOCOPY NUMBER,
305 x_msg_data OUT NOCOPY VARCHAR2);
306
307 PROCEDURE generate_quote_streams(
308 p_api_version IN NUMBER,
309 p_init_msg_list IN VARCHAR2,
310 p_khr_id IN NUMBER,
311 p_kle_id IN NUMBER,
312 x_return_status OUT NOCOPY VARCHAR2,
313 x_msg_count OUT NOCOPY NUMBER,
314 x_msg_data OUT NOCOPY VARCHAR2);
315
316 -- gboomina added for Bug 6763287 - Start
317 PROCEDURE generate_streams_for_IA( p_api_version IN NUMBER
318 , p_init_msg_list IN VARCHAR2
319 , p_khr_id IN NUMBER
320 , x_return_status OUT NOCOPY VARCHAR2
321 , x_msg_count OUT NOCOPY NUMBER
322 , x_msg_data OUT NOCOPY VARCHAR2
323 );
324 -- gboomina added for Bug 6763287 - End
325
326
327 --sechawla 10-aug-09 : PRB ESG enhancements : added procedure to spec
328 -- This will be called from okl_la_stream_pvt.adjust_passthrough_streams
329 -- to support PRB for passthru accrual streams
330 PROCEDURE prosp_adj_acc_strms(
331 p_api_version IN NUMBER
332 ,p_init_msg_list IN VARCHAR2
333 ,p_rebook_type IN VARCHAR2
334 ,p_rebook_date IN DATE
335 ,p_khr_id IN NUMBER
336 ,p_deal_type IN VARCHAR2
337 ,p_currency_code IN VARCHAR2
338 ,p_start_date IN DATE
339 ,p_end_date IN DATE
340 ,p_context IN VARCHAR2
341 ,p_purpose_code IN VARCHAR2
342 ,x_return_status OUT NOCOPY VARCHAR2
343 ,x_msg_count OUT NOCOPY NUMBER
344 ,x_msg_data OUT NOCOPY VARCHAR2);
345
346 END OKL_STREAM_GENERATOR_PVT;
[Home] [Help]