[Home] [Help]
PACKAGE BODY: APPS.OKL_VAR_INT_FLOAT_FACTOR_PVT
Source
1 PACKAGE BODY OKL_VAR_INT_FLOAT_FACTOR_PVT AS
2 /* $Header: OKLRVFFB.pls 120.0.12020000.2 2012/11/23 08:59:19 bkatraga noship $ */
3
4 ------------------------------------------------------------------------------
5
6 -- Start of Comments
7 -- Created By: Ramesh Seela
8 -- Procedure Name variable_interest_float_factor
9 -- Description: This procedure is used to derive the interest and generate streams and invoice for
10 -- contracts with interest calculation basis of FLOAT FACTOR
11 -- Inputs :
12 -- Output :
13 -- Dependencies:
14 -- Parameters:
15 -- Version: 1.0
16 -- End of Comments
17
18 ------------------------------------------------------------------------------
19 PROCEDURE variable_interest_float_factor(
20 p_api_version IN NUMBER,
21 p_init_msg_list IN VARCHAR2,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_msg_count OUT NOCOPY NUMBER,
24 x_msg_data OUT NOCOPY VARCHAR2,
25 p_contract_id IN NUMBER,
26 p_from_date IN DATE,
27 p_to_date IN DATE) IS
28
29 l_api_version CONSTANT NUMBER := 1.0;
30 l_api_name CONSTANT VARCHAR2(30) := 'VARIABLE_INTEREST_FLOAT_FACTOR';
31 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
32 l_stream_type_purpose OKL_STRM_TYPE_V.stream_type_purpose%TYPE;
33 l_invoice_amt NUMBER := 0;
34 l_formula_id OKL_FORMULAE_V.id%TYPE;
35 l_formula_name OKL_FORMULAE_V.name%TYPE;
36 l_line_id OKC_K_LINES_B.id%TYPE;
37 l_stream_element_date DATE;
38 l_invoice_id NUMBER;
39 l_stream_element_id OKL_STRM_ELEMENTS_V.id%TYPE;
40 var_int_float_factor_failed EXCEPTION;
41
42 Cursor formula_name_csr (p_formula_id NUMBER) IS
43 SELECT fml.name
44 FROM okl_formulae_v fml
45 WHERE fml.id = p_formula_id;
46
47 Cursor asset_billed_streams_csr (p_contract_id NUMBER, p_from_date DATE, p_to_date DATE) IS
48 SELECT chrb.id contract_id, cleb.id kle_id,
49 selb.stream_element_date, selb.amount amount
50 FROM okc_k_headers_b chrb, okc_k_lines_b cleb,
51 okc_line_styles_b lseb, okl_strm_type_b styb,
52 okl_strm_elements selb, okl_streams stmb,
53 okc_statuses_b sts
54 WHERE cleb.dnz_chr_id = chrb.id
55 AND cleb.chr_id = chrb.id
56 AND chrb.id = p_contract_id
57 AND cleb.lse_id = lseb.id
58 AND lseb.lty_code = 'FREE_FORM1'
59 AND stmb.khr_id = chrb.id
60 AND stmb.kle_id = cleb.id
61 AND stmb.sty_id = styb.id
62 AND selb.stm_id = stmb.id
63 AND chrb.id = stmb.khr_id
64 AND stmb.say_code = 'CURR'
65 AND stmb.active_yn = 'Y'
66 AND styb.stream_type_purpose = 'RENT'
67 AND cleb.sts_code = sts.code
68 AND sts.ste_code NOT IN ('TERMINATED','CANCELLED')
69 AND (selb.stream_element_date > p_from_date AND selb.stream_element_date <= NVL(p_to_date,trunc(SYSDATE)))
70 ORDER BY selb.stream_element_date, cleb.id; -- 4904798
71
72 BEGIN
73 ------------------------------------------------------------
74 -- Start processing
75 ------------------------------------------------------------
76
77 x_return_status := OKL_API.G_RET_STS_SUCCESS;
78
79 OKL_VARIABLE_INTEREST_PVT.print_debug('Executing procedure VARIABLE_INTEREST_FLOAT_FACTOR using following parameters : ');
80 OKL_VARIABLE_INTEREST_PVT.print_debug('contract ID : '|| p_contract_id);
81 OKL_VARIABLE_INTEREST_PVT.print_debug('g_contract_id : '|| OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_ID);
82 OKL_VARIABLE_INTEREST_PVT.print_debug('From date : '|| p_from_date);
83 OKL_VARIABLE_INTEREST_PVT.print_Debug('To date : '|| p_to_date);
84
85 OKL_VARIABLE_INTEREST_PVT.print_debug ('G_CALCULATION_FORMULA_ID : '|| OKL_VARIABLE_INTEREST_PVT.G_CALCULATION_FORMULA_ID);
86
87 OPEN formula_name_csr (OKL_VARIABLE_INTEREST_PVT.G_CALCULATION_FORMULA_ID);
88 FETCH formula_name_csr INTO l_formula_name;
89 IF (formula_name_csr%NOTFOUND) THEN
90 CLOSE formula_name_csr;
91 OKL_VARIABLE_INTEREST_PVT.Print_Debug( 'Unable to find formula for formula id :' || OKL_VARIABLE_INTEREST_PVT.G_CALCULATION_FORMULA_ID);
92 OKL_VARIABLE_INTEREST_PVT.print_error_message('Interest Params cursor did not return any records for formula ID: '|| OKL_VARIABLE_INTEREST_PVT.G_CALCULATION_FORMULA_ID);
93 RAISE var_int_float_factor_failed;
94 END IF;
95 CLOSE formula_name_csr;
96
97 OKL_VARIABLE_INTEREST_PVT.Print_Debug( 'Formula Name : '|| l_formula_name);
98
99 FOR current_stream IN asset_billed_streams_csr (p_contract_id, p_from_date, p_to_date)
100 LOOP
101 l_line_id := current_stream.kle_id;
102 l_invoice_amt := 0;
103 l_stream_element_date := current_stream.stream_element_date;
104
105 OKL_VARIABLE_INTEREST_PVT.Print_debug( 'line id : '|| l_line_id);
106 OKL_VARIABLE_INTEREST_PVT.Print_debug('Stream Element Date: '|| l_stream_element_date);
107
108 Okl_Execute_Formula_Pub.G_ADDITIONAL_PARAMETERS(1).NAME := 'DUE_DATE';
109 Okl_Execute_Formula_Pub.G_ADDITIONAL_PARAMETERS(1).VALUE := l_stream_element_date;
110
111 -- Apply FLoat factor formula
112 Okl_Execute_Formula_Pub.EXECUTE(
113 p_api_version => p_api_version,
114 p_init_msg_list => OKL_API.G_TRUE,
115 x_return_status => x_return_status,
116 x_msg_count => x_msg_count,
117 x_msg_data => x_msg_data,
118 p_formula_name => l_formula_name,
119 p_contract_id => p_contract_id,
120 p_line_id => l_line_id,
121 x_value => l_invoice_amt
122 );
123
124 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
125 OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to OKL_EXECUTE_FORMULA_PUB.EXECUTE');
126 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
127 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
128 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to OKL_EXECUTE_FORMULA_PUB.EXECUTE');
129 RAISE var_int_float_factor_failed;
130 END IF;
131
132 OKL_VARIABLE_INTEREST_PVT.Print_debug ('Formula executed successfully');
133 OKL_VARIABLE_INTEREST_PVT.Print_debug (' Invoice Amount : '|| l_invoice_amt);
134
135 l_stream_type_purpose := 'FLOAT_FACTOR_ADJUSTMENT';
136
137 IF (l_invoice_amt <> 0) THEN
138 OKL_VR_DOCUMENT_PVT.Create_Stream_Invoice (
139 p_api_version => p_api_version,
140 p_init_msg_list => p_init_msg_list,
141 x_return_status => x_return_status,
142 x_msg_count => x_msg_count,
143 x_msg_data => x_msg_data,
144 p_contract_id => p_contract_id,
145 p_line_id => l_line_id,
146 p_amount => l_invoice_amt,
147 p_due_date => l_stream_element_date,
148 p_stream_type_purpose => l_stream_type_purpose,
149 p_create_invoice_flag => OKL_API.G_TRUE,
150 p_parent_strm_element_id => NULL,
151 x_invoice_id => l_invoice_id,
152 x_stream_element_id => l_stream_element_id);
153 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
154 OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to CREATE_STREAM_INVOICE');
155 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
156 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
157 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to CREATE_STREAM_INVOICE');
158 RAISE var_int_float_factor_failed;
159 END IF;
160 END IF;
161
162 OKL_VR_DATA_HANDLER_PVT.UPDATE_LAST_INT_CAL_DATE(
163 p_api_version => p_api_version,
164 p_init_msg_list => p_init_msg_list,
165 x_return_status => x_return_status,
166 x_msg_count => x_msg_count,
167 x_msg_data => x_msg_data,
168 p_contract_id => p_contract_id,
169 p_last_interest_cal_date => l_stream_element_date);
170 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
171 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
172 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
173 RAISE OKL_API.G_EXCEPTION_ERROR;
174 END IF;
175
176 COMMIT;
177
178 END LOOP;
179
180 EXCEPTION
181 WHEN var_int_float_factor_failed THEN
182 OKL_VARIABLE_INTEREST_PVT.print_error_message('Exception var_int_float_factor_failed raised in procedure VARIABLE_INTEREST_FLOAT_FACTOR');
183 x_return_status := OKL_API.G_RET_STS_ERROR;
184 WHEN OTHERS THEN
185 OKL_VARIABLE_INTEREST_PVT.print_error_message('Exception raised in procedure VARIABLE_INTEREST_FLOAT_FACTOR');
186 Okl_Api.SET_MESSAGE(
187 p_app_name => G_APP_NAME,
188 p_msg_name => G_UNEXPECTED_ERROR,
189 p_token1 => G_SQLCODE_TOKEN,
190 p_token1_value => SQLCODE,
191 p_token2 => G_SQLERRM_TOKEN,
192 p_token2_value => SQLERRM);
193
194 x_return_status := OKL_API.G_RET_STS_ERROR;
195
196 END variable_interest_float_factor;
197
198 -----------------------------------------------------------------
199 PROCEDURE process_interest_float_factor(
200 p_api_version IN NUMBER,
201 p_init_msg_list IN VARCHAR2,
202 x_return_status OUT NOCOPY VARCHAR2,
203 x_msg_count OUT NOCOPY NUMBER,
204 x_msg_data OUT NOCOPY VARCHAR2,
205 p_contract_id IN NUMBER,
206 p_to_date IN DATE,
207 p_last_interest_cal_date IN DATE,
208 p_print_lead_days IN NUMBER
209 )
210
211 IS
212 ------------------------------------------------------------
213 -- Declare variables required by APIs
214 ------------------------------------------------------------
215
216 l_api_version CONSTANT NUMBER := 1.0;
217 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_INTEREST_FLOAT_FACTOR';
218 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
219 l_msg_data VARCHAR2(2000);
220
221 l_from_date DATE;
222 l_to_date DATE;
223
224 process_int_float_fctr_failed EXCEPTION;
225
226 BEGIN
227
228 ------------------------------------------------------------
229 -- Start processing
230 ------------------------------------------------------------
231
232 l_from_date := nvl(p_last_interest_cal_date,OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_START_DATE);
233 l_to_date := nvl(p_to_date, trunc(SYSDATE));
234 --change on 15 Nov 2005 by pgomes for bug fix 4740293
235 --considering print lead days for obtaining end date of range
236 IF (l_to_date > trunc(SYSDATE) + p_print_lead_days) THEN
237 l_to_date := trunc(SYSDATE) + p_print_lead_days;
238 END IF;
239
240 OKL_VARIABLE_INTEREST_PVT.print_debug ('contract id : '|| p_contract_id );
241 OKL_VARIABLE_INTEREST_PVT.print_debug ('From Date : '|| l_from_date );
242 OKL_VARIABLE_INTEREST_PVT.print_debug ('To Date : '|| l_to_date);
243
244 variable_interest_float_factor(
245 p_api_version => 1.0,
246 p_init_msg_list => OKL_API.G_TRUE,
247 x_return_status => x_return_status,
248 x_msg_count => x_msg_count,
249 x_msg_data => x_msg_data,
250 p_contract_id => p_contract_id,
251 p_from_date => l_from_date,
252 p_to_date => l_to_date);
253
254 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
255 OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to VARIABLE_INTEREST_FLOAT_FACTOR');
256 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
257 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
258 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to VARIABLE_INTEREST_FLOAT_FACTOR');
259 RAISE process_int_float_fctr_failed;
260 END IF;
261
262 OKL_VARIABLE_INTEREST_PVT.print_debug('Variable interest calculation completed successfully for contract id : '|| p_contract_id);
263
264 EXCEPTION
265
266 WHEN process_int_float_fctr_failed THEN
267 OKL_VARIABLE_INTEREST_PVT.print_error_message ('Exception process_int_float_fctr_failed raised in procedure PROCESS_INTEREST_FLOAT_FACTOR');
268 x_return_status := OKL_API.G_RET_STS_ERROR;
269 WHEN OTHERS THEN
270 OKL_VARIABLE_INTEREST_PVT.print_error_message ('Exception raised in procedure PROCESS_INTEREST_FLOAT_FACTOR');
271 Okl_Api.SET_MESSAGE(
272 p_app_name => G_APP_NAME,
273 p_msg_name => G_UNEXPECTED_ERROR,
274 p_token1 => G_SQLCODE_TOKEN,
275 p_token1_value => SQLCODE,
276 p_token2 => G_SQLERRM_TOKEN,
277 p_token2_value => SQLERRM);
278 x_return_status := OKL_API.G_RET_STS_ERROR;
279
280 END process_interest_float_factor;
281 END;