[Home] [Help]
PACKAGE BODY: APPS.OKL_K_RATE_PARAMS_PVT
Source
1 PACKAGE BODY OKL_K_RATE_PARAMS_PVT AS
2 /* $Header: OKLRKRPB.pls 120.26.12010000.4 2008/11/21 09:11:58 rpillay ship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7
8 -- GLOBAL VARIABLES
9 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
10 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
11 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
12 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
13 G_BULK_SIZE CONSTANT NUMBER := 10000;
14 G_OKL_LLA_VAR_RATE_ERROR CONSTANT VARCHAR2(30) := 'OKL_LLA_VAR_RATE_ERROR';
15 G_LEASE_TYPE CONSTANT VARCHAR2(30) := 'LEASE_TYPE';
16 G_INT_BASIS CONSTANT VARCHAR2(30) := 'INT_BASIS';
17 G_OKL_LLA_VAR_RATE_MISSING CONSTANT VARCHAR2(30) := 'OKL_LLA_VAR_RATE_MISSING';
18 G_CONT_ID CONSTANT VARCHAR2(30) := 'CONT_ID';
19 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_REQUIRED_VALUE;
20 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
21
22 -- smadhava Bug#4542290 - 22-Aug-2005 - Added - Start
23 G_OKL_LLA_REBOOK_INVALID CONSTANT VARCHAR2(30) := 'OKL_LLA_REBOOK_INVALID';
24 G_OKL_LLA_RBK_INT_PROC_INVAL CONSTANT VARCHAR2(30) := 'OKL_LLA_RBK_INT_PROCESS_INVAL';
25 G_OKL_LLA_RBK_DATE_BILL_INVAL CONSTANT VARCHAR2(30) := 'OKL_LLA_RBK_DATE_BILL_INVAL';
26 G_OKL_LLA_RBK_DATE_ACCR_INVAL CONSTANT VARCHAR2(30) := 'OKL_LLA_RBK_DATE_ACCRUE_INVAL';
27
28 G_BOOK_CLASS CONSTANT VARCHAR2(30) := 'BOOK_CLASS';
29 G_INT_CALC_BASIS CONSTANT VARCHAR2(30) := 'INT_CAL';
30 G_STREAM CONSTANT VARCHAR2(30) := 'STREAM';
31
32 G_BOOK_CLASS_OP CONSTANT OKL_PRODUCT_PARAMETERS_V.DEAL_TYPE%TYPE := 'LEASEOP';
33 G_BOOK_CLASS_DF CONSTANT OKL_PRODUCT_PARAMETERS_V.DEAL_TYPE%TYPE := 'LEASEDF';
34 G_BOOK_CLASS_ST CONSTANT OKL_PRODUCT_PARAMETERS_V.DEAL_TYPE%TYPE := 'LEASEST';
35 G_BOOK_CLASS_LOAN CONSTANT OKL_PRODUCT_PARAMETERS_V.DEAL_TYPE%TYPE := 'LOAN';
36 G_BOOK_CLASS_REVLOAN CONSTANT OKL_PRODUCT_PARAMETERS_V.DEAL_TYPE%TYPE := 'LOAN-REVOLVING';
37
38 G_ICB_FIXED CONSTANT OKL_PRODUCT_PARAMETERS_V.INTEREST_CALCULATION_BASIS%TYPE := 'FIXED';
39 G_ICB_FLOAT_FACTOR CONSTANT OKL_PRODUCT_PARAMETERS_V.INTEREST_CALCULATION_BASIS%TYPE := 'FLOAT_FACTORS';
40 G_ICB_FLOAT CONSTANT OKL_PRODUCT_PARAMETERS_V.INTEREST_CALCULATION_BASIS%TYPE := 'FLOAT';
41 G_ICB_REAMORT CONSTANT OKL_PRODUCT_PARAMETERS_V.INTEREST_CALCULATION_BASIS%TYPE := 'REAMORT';
42 G_ICB_CATCHUP_CLEANUP CONSTANT OKL_PRODUCT_PARAMETERS_V.INTEREST_CALCULATION_BASIS%TYPE := 'CATCHUP/CLEANUP';
43
44 G_RRM_EST_ACTUAL CONSTANT OKL_PRODUCT_PARAMETERS_V.REVENUE_RECOGNITION_METHOD%TYPE := 'ESTIMATED_AND_ACTUAL';
45 G_RRM_ACTUAL CONSTANT OKL_PRODUCT_PARAMETERS_V.REVENUE_RECOGNITION_METHOD%TYPE := 'ACTUAL';
46
47 G_STRM_RENT CONSTANT OKL_STRM_TYPE_V.STREAM_TYPE_PURPOSE%TYPE := 'RENT';
48 G_STRM_RENT_ACCRUAL CONSTANT OKL_STRM_TYPE_V.STREAM_TYPE_PURPOSE%TYPE := 'RENT_ACCRUAL';
49 G_STRM_PRE_TAX CONSTANT OKL_STRM_TYPE_V.STREAM_TYPE_PURPOSE%TYPE := 'LEASE_INCOME';
50 G_STRM_INT_INCOME CONSTANT OKL_STRM_TYPE_V.STREAM_TYPE_PURPOSE%TYPE := 'INTEREST_INCOME';
51 G_STRM_LOAN_PAYMENT CONSTANT OKL_STRM_TYPE_V.STREAM_TYPE_PURPOSE%TYPE := 'LOAN_PAYMENT';
52 G_STRM_VAR_INT_INCOME CONSTANT OKL_STRM_TYPE_V.STREAM_TYPE_PURPOSE%TYPE := 'VARIABLE_INTEREST_INCOME';
53
54 G_COL_NAME CONSTANT VARCHAR2(30) := OKC_API.G_COL_NAME_TOKEN;
55 G_DUE_DATE CONSTANT VARCHAR2(30) := 'DUE_DATE';
56 -- smadhava Bug#4542290 - 22-Aug-2005 - Added - End
57
58
59 procedure print_krpv_rec(p_krpv_rec IN krpv_rec_type) IS
60 begin
61
62 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
63 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'KHR_ID=' || p_krpv_rec.KHR_ID);
64 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'parameter_type_code=' || p_krpv_rec.PARAMETER_TYPE_CODE);
65 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'effective_from_date=' || p_krpv_rec.EFFECTIVE_FROM_DATE);
66 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'effective_to_date=' || p_krpv_rec.EFFECTIVE_TO_DATE);
67 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'interest_index_id= '|| p_krpv_rec.INTEREST_INDEX_ID);
68 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'base_rate=' || p_krpv_rec.BASE_RATE);
69 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'interest_start_date=' || p_krpv_rec.INTEREST_START_DATE);
70 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'adder_rate='|| p_krpv_rec.ADDER_RATE);
71 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'maximum_rate=' || p_krpv_rec.MAXIMUM_RATE);
72 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'minimum_rate=' || p_krpv_rec.MINIMUM_RATE);
73 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'principal_basis_code='|| p_krpv_rec.PRINCIPAL_BASIS_CODE);
74 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'days_in_a_month_code='|| p_krpv_rec.DAYS_IN_A_MONTH_CODE);
75 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'days_in_a_year_code='|| p_krpv_rec.DAYS_IN_A_YEAR_CODE);
76 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'interest_basis_code='|| p_krpv_rec.INTEREST_BASIS_CODE);
77 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'rate_delay_code='|| p_krpv_rec.RATE_DELAY_CODE);
78 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'rate_delay_frequency=' || p_krpv_rec.RATE_DELAY_FREQUENCY);
79 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'compounding_frequency_code=' || p_krpv_rec.COMPOUNDING_FREQUENCY_CODE);
80 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'calculation_formula_id=' || p_krpv_rec.CALCULATION_FORMULA_ID);
81 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'catchup_frequency_code='|| p_krpv_rec.CATCHUP_frequency_code);
82 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'catchup_basis_code='|| p_krpv_rec.CATCHUP_BASIS_CODE);
83 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'catchup_start_date='|| p_krpv_rec.CATCHUP_START_DATE);
84 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'catchup_settlement_code='|| p_krpv_rec.CATCHUP_SETTLEMENT_CODE);
85 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'rate_change_start_date='|| p_krpv_rec.RATE_CHANGE_START_DATE);
86 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'rate_change_frequency_code=' || p_krpv_rec.RATE_CHANGE_FREQUENCY_CODE);
87 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'rate_change_value=' || p_krpv_rec.RATE_CHANGE_VALUE);
88 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'conversion_option_code=' || p_krpv_rec.CONVERSION_OPTION_CODE);
89 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'next_conversion_date=' || p_krpv_rec.NEXT_CONVERSION_DATE);
90 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'conversion_type_code=' || p_krpv_rec.CONVERSION_TYPE_CODE);
91 END IF;
92 /*print(' p_krpv_rec.ATTRIBUTE_CATEGORY,
93 print(' p_krpv_rec.ATTRIBUTE1,
94 print(' p_krpv_rec.ATTRIBUTE2,
95 print(' p_krpv_rec.ATTRIBUTE3,
96 print(' p_krpv_rec.ATTRIBUTE4,
97 print(' p_krpv_rec.ATTRIBUTE5,
98 print(' p_krpv_rec.ATTRIBUTE6,
99 print(' p_krpv_rec.ATTRIBUTE7,
100 print(' p_krpv_rec.ATTRIBUTE8,
101 print(' p_krpv_rec.ATTRIBUTE9,
102 print(' p_krpv_rec.ATTRIBUTE10,
103 print(' p_krpv_rec.ATTRIBUTE11,
104 print(' p_krpv_rec.ATTRIBUTE12,
105 print(' p_krpv_rec.ATTRIBUTE13,
106 print(' p_krpv_rec.ATTRIBUTE14,
107 print(' p_krpv_rec.ATTRIBUTE15,
108 print(' p_krpv_rec.CREATED_BY,
109 print(' p_krpv_rec.CREATION_DATE,
110 print(' p_krpv_rec.LAST_UPDATED_BY,
111 print(' p_krpv_rec.LAST_UPDATE_DATE,
112 print(' p_krpv_rec.LAST_UPDATE_LOGIN */
113 end;
114
115 FUNCTION interest_processing(p_chr_id IN NUMBER,
116 x_contract_number OUT NOCOPY VARCHAR2,
117 x_contract_start_date OUT NOCOPY DATE )
118 RETURN BOOLEAN IS
119 l_interest_date DATE;
120 l_billable_stream_exists VARCHAR2(1) := 'N';
121 l_orig_system_id1 okc_k_headers_b.orig_system_id1%type;
122 l_orig_system_source_code okc_k_headers_b.orig_system_source_code%type;
123 BEGIN
124 SELECT A.DATE_LAST_INTERIM_INTEREST_CAL,
125 B.CONTRACT_NUMBER,
126 B.START_DATE,
127 B.ORIG_SYSTEM_ID1,
128 B.ORIG_SYSTEM_SOURCE_CODE
129 INTO l_interest_date,
130 x_contract_number,
131 x_contract_start_date,
132 l_orig_system_id1,
133 l_orig_system_source_code
134 FROM OKL_K_HEADERS A,
135 OKC_K_HEADERS_B B
136 WHERE A.ID = p_chr_id
137 AND A.ID = B.ID;
138 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
139 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_interest_date=' ||l_interest_date);
140 END IF;
141
142 IF (l_interest_date IS NULL) THEN
143
144 -- Bug 4905142
145 -- Check if billable streams exists in which case billing has been run
146 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
147 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Yes, l_interest_date is NULL...');
148 END IF;
149 Begin
150 SELECT 'Y'
151 INTO l_billable_stream_exists
152 FROM OKL_STREAMS A, OKL_STRM_TYPE_B C WHERE A.KHR_ID=p_chr_id
153 AND EXISTS (
154 SELECT 'X' FROM OKL_STRM_ELEMENTS B
155 WHERE B.STM_ID = A.ID
156 AND B.DATE_BILLED IS NOT NULL)
157 AND A.STY_ID = C.ID
158 AND C.BILLABLE_YN='Y'
159 AND A.SAY_CODE <> 'HIST'
160 AND ROWNUM < 2;
161
162 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
163 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_billable_stream_exists=' || l_billable_stream_exists);
164 END IF;
165
166 Exception when no_data_found then
167
168 If (l_orig_system_source_code = 'OKL_REBOOK') Then -- 4905142
169 Begin
170 SELECT 'Y'
171 INTO l_billable_stream_exists
172 FROM OKL_STREAMS A, OKL_STRM_TYPE_B C WHERE A.KHR_ID=l_orig_system_id1
173 AND EXISTS (
174 SELECT 'X' FROM OKL_STRM_ELEMENTS B
175 WHERE B.STM_ID = A.ID
176 AND B.DATE_BILLED IS NOT NULL)
177 AND A.STY_ID = C.ID
178 AND C.BILLABLE_YN='Y'
179 AND A.SAY_CODE <> 'HIST'
180 AND ROWNUM < 2;
181
182 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
183 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'2.l_billable_stream_exists=' || l_billable_stream_exists);
184 END IF;
185 exception when others then
186 l_billable_stream_exists := 'N';
187 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
188 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Exception...sqlcode=' || sqlcode);
189 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Exception...sqlerrm=' || sqlerrm);
190 END IF;
191 End;
192 End If;
193
194 WHEN OTHERS THEN
195 l_billable_stream_exists := 'N';
196 End;
197
198 IF (NVL(l_billable_stream_exists,'N') = 'Y') THEN -- 4905142
199 RETURN(TRUE);
200 ELSE
201 RETURN(FALSE);
202 END IF;
203 ELSE
204 RETURN(TRUE);
205 END IF;
206 END;
207
208 FUNCTION Is_Rebook_Copy (p_chr_id IN NUMBER) return BOOLEAN IS
209 Cursor Rbk_Cpy_Csr(p_chr_id IN Number) is
210 Select orig_system_source_code
211 From okc_k_headers_b chr
212 --where chr.orig_system_source_code = 'OKL_REBOOK'
213 where chr.id = p_chr_id;
214
215 l_rbk_cpy BOOLEAN := FALSE;
216 l_orig_system_source_code okc_k_headers_b.orig_system_source_code%type;
217 Begin
218 Open Rbk_Cpy_Csr(p_chr_id => p_chr_id);
219 Fetch Rbk_Cpy_Csr into l_orig_system_source_code;
220 Close Rbk_Cpy_Csr;
221 IF (nvl(l_orig_system_source_code,'?') = 'OKL_REBOOK') THEN
222 l_rbk_cpy := TRUE;
223 ELSE
224 l_rbk_cpy := FALSE;
225 END IF;
226 Return (l_rbk_cpy);
227 End Is_Rebook_Copy;
228
229 PROCEDURE get_effective_from_date(
230 p_api_version IN NUMBER,
231 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
232 x_return_status OUT NOCOPY VARCHAR2,
233 x_msg_count OUT NOCOPY NUMBER,
234 x_msg_data OUT NOCOPY VARCHAR2,
235 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
236 x_effective_from_date OUT NOCOPY DATE,
237 x_no_data_found OUT NOCOPY BOOLEAN) IS
238 CURSOR effective_from_date_csr(p_id NUMBER) IS
239 SELECT MAX(EFFECTIVE_FROM_DATE)
240 FROM OKL_K_RATE_PARAMS
241 WHERE KHR_ID = p_id
242 AND EFFECTIVE_TO_DATE IS NULL;
243 BEGIN
244 x_return_status := OKL_API.G_RET_STS_SUCCESS;
245
246 OPEN effective_from_date_csr(p_chr_id);
247 FETCH effective_from_date_csr
248 INTO x_effective_from_date;
249 IF effective_from_date_csr%NOTFOUND THEN
250 x_no_data_found := TRUE;
251 ELSE
252 x_no_data_found := FALSE;
253 END IF;
254 CLOSE effective_from_date_csr;
255 EXCEPTION WHEN OTHERS THEN
256 x_return_status := OKL_API.G_RET_STS_ERROR;
257 END;
258
259 procedure get_product(
260 p_api_version IN NUMBER,
261 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
262 x_return_status OUT NOCOPY VARCHAR2,
263 x_msg_count OUT NOCOPY NUMBER,
264 x_msg_data OUT NOCOPY VARCHAR2,
265 p_khr_id IN okc_k_headers_b.id%type,
266 x_pdt_parameter_rec OUT NOCOPY OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type)
267 IS
268
269 l_api_name CONSTANT VARCHAR2(30) := 'get_product';
270 l_api_version CONSTANT NUMBER := 1.0;
271
272 p_pdtv_rec OKL_SETUPPRODUCTS_PUB.pdtv_rec_type;
273 --x_pdt_parameter_rec2 OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
274 x_no_data_found BOOLEAN;
275
276 CURSOR l_hdr_csr( chrId NUMBER ) IS
277 SELECT
278 CHR.authoring_org_id,
279 CHR.inv_organization_id,
280 khr.deal_type,
281 pdt.id pid,
282 NVL(pdt.reporting_pdt_id, -1) report_pdt_id
283 FROM okc_k_headers_b CHR,
284 okl_k_headers khr,
285 okl_products_v pdt
286 WHERE khr.id = CHR.id
287 AND CHR.id = chrId
288 AND khr.pdt_id = pdt.id;
289
290 l_hdr_rec l_hdr_csr%ROWTYPE;
291
292 begin
293 x_return_status := OKL_API.G_RET_STS_SUCCESS;
294 OPEN l_hdr_csr(p_khr_id);
295 FETCH l_hdr_csr INTO l_hdr_rec;
296 IF l_hdr_csr%NOTFOUND THEN
297 CLOSE l_hdr_csr;
298 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
299 END IF;
300 CLOSE l_hdr_csr;
301
302 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
303 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'In get_product: deal_type=' || l_hdr_rec.deal_type);
304 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'pid=' || l_hdr_rec.pid);
305 END IF;
306
307 p_pdtv_rec.id := l_hdr_rec.pid;
308
309 OKL_SETUPPRODUCTS_PUB.Getpdt_parameters(
310 p_api_version => p_api_version,
311 p_init_msg_list => p_init_msg_list,
312 x_return_status => x_return_status,
313 x_msg_count => x_msg_count,
314 x_msg_data => x_msg_data,
315 p_pdtv_rec => p_pdtv_rec,
316 x_no_data_found => x_no_data_found,
317 p_pdt_parameter_rec => x_pdt_parameter_rec);
318
319 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
320 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
321 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
322 RAISE OKL_API.G_EXCEPTION_ERROR;
323 ELSIF NVL(x_pdt_parameter_rec.Name,OKL_API.G_MISS_CHAR) = OKL_API.G_MISS_CHAR THEN
324 x_return_status := OKL_API.G_RET_STS_ERROR;
325 RAISE OKL_API.G_EXCEPTION_ERROR;
326 END IF;
327 --x_pdt_parameter_rec := x_pdt_parameter_rec2;
328 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
329 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'x_pdt_parameter_rec.name=' || x_pdt_parameter_rec.name);
330 END IF;
331 EXCEPTION WHEN OTHERS
332 THEN
333 NULL;
334
335 END;
336
337 procedure get_product2(
338 p_api_version IN NUMBER,
339 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
340 x_return_status OUT NOCOPY VARCHAR2,
341 x_msg_count OUT NOCOPY NUMBER,
342 x_msg_data OUT NOCOPY VARCHAR2,
343 p_khr_id IN okc_k_headers_b.id%type,
344 x_product_id OUT NOCOPY NUMBER,
345 x_pdt_parameter_rec OUT NOCOPY OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type)
346 IS
347
348 l_api_name CONSTANT VARCHAR2(30) := 'get_product2';
349 l_api_version CONSTANT NUMBER := 1.0;
350
351 p_pdtv_rec OKL_SETUPPRODUCTS_PUB.pdtv_rec_type;
352 --x_pdt_parameter_rec2 OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
353 x_no_data_found BOOLEAN;
354
355 CURSOR l_hdr_csr( chrId NUMBER ) IS
356 SELECT
357 CHR.authoring_org_id,
358 CHR.inv_organization_id,
359 khr.deal_type,
360 pdt.id pid,
361 NVL(pdt.reporting_pdt_id, -1) report_pdt_id
362 FROM okc_k_headers_b CHR,
363 okl_k_headers khr,
364 okl_products_v pdt
365 WHERE khr.id = CHR.id
366 AND CHR.id = chrId
367 AND khr.pdt_id = pdt.id;
368
369 l_hdr_rec l_hdr_csr%ROWTYPE;
370
371 begin
372 x_return_status := OKL_API.G_RET_STS_SUCCESS;
373 OPEN l_hdr_csr(p_khr_id);
374 FETCH l_hdr_csr INTO l_hdr_rec;
375 IF l_hdr_csr%NOTFOUND THEN
376 CLOSE l_hdr_csr;
377 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
378 END IF;
379 CLOSE l_hdr_csr;
380
381 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
382 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'In get_product2: deal_type=' || l_hdr_rec.deal_type);
383 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'pid=' || l_hdr_rec.pid);
384 END IF;
385
386 p_pdtv_rec.id := l_hdr_rec.pid;
387 x_product_id := l_hdr_rec.pid;
388
389 OKL_SETUPPRODUCTS_PUB.Getpdt_parameters(
390 p_api_version => p_api_version,
391 p_init_msg_list => p_init_msg_list,
392 x_return_status => x_return_status,
393 x_msg_count => x_msg_count,
394 x_msg_data => x_msg_data,
395 p_pdtv_rec => p_pdtv_rec,
396 x_no_data_found => x_no_data_found,
397 p_pdt_parameter_rec => x_pdt_parameter_rec);
398
399 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
400 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
401 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
402 RAISE OKL_API.G_EXCEPTION_ERROR;
403 ELSIF NVL(x_pdt_parameter_rec.Name,OKL_API.G_MISS_CHAR) = OKL_API.G_MISS_CHAR THEN
404 x_return_status := OKL_API.G_RET_STS_ERROR;
405 RAISE OKL_API.G_EXCEPTION_ERROR;
406 END IF;
407 --x_pdt_parameter_rec := x_pdt_parameter_rec2;
408 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
409 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'x_pdt_parameter_rec.name=' || x_pdt_parameter_rec.name);
410 END IF;
411 EXCEPTION WHEN OTHERS
412 THEN
413 NULL;
414
415 END;
416
417 PROCEDURE get_rate_rec(p_chr_id IN NUMBER,
418 p_parameter_type_code IN VARCHAR2,
419 p_effective_from_date IN DATE,
420 x_krpv_rec OUT NOCOPY krpv_rec_type,
421 x_no_data_found OUT NOCOPY BOOLEAN
422 ) IS
423 CURSOR okl_k_rate_params_v_u1_csr (p_effective_from_date IN DATE,
424 p_khr_id IN NUMBER,
425 p_parameter_type_code IN VARCHAR2) IS
426 SELECT
427 KHR_ID,
428 PARAMETER_TYPE_CODE,
429 EFFECTIVE_FROM_DATE,
430 EFFECTIVE_TO_DATE,
431 INTEREST_INDEX_ID,
432 BASE_RATE,
433 INTEREST_START_DATE,
434 ADDER_RATE,
435 MAXIMUM_RATE,
436 MINIMUM_RATE,
437 PRINCIPAL_BASIS_CODE,
438 DAYS_IN_A_MONTH_CODE,
439 DAYS_IN_A_YEAR_CODE,
440 INTEREST_BASIS_CODE,
441 RATE_DELAY_CODE,
442 RATE_DELAY_FREQUENCY,
443 COMPOUNDING_FREQUENCY_CODE,
444 CALCULATION_FORMULA_ID,
445 CATCHUP_BASIS_CODE,
446 CATCHUP_START_DATE,
447 CATCHUP_SETTLEMENT_CODE,
448 RATE_CHANGE_START_DATE,
449 RATE_CHANGE_FREQUENCY_CODE,
450 RATE_CHANGE_VALUE,
451 CONVERSION_OPTION_CODE,
452 NEXT_CONVERSION_DATE,
453 CONVERSION_TYPE_CODE,
454 ATTRIBUTE_CATEGORY,
455 ATTRIBUTE1,
456 ATTRIBUTE2,
457 ATTRIBUTE3,
458 ATTRIBUTE4,
459 ATTRIBUTE5,
460 ATTRIBUTE6,
461 ATTRIBUTE7,
462 ATTRIBUTE8,
463 ATTRIBUTE9,
464 ATTRIBUTE10,
465 ATTRIBUTE11,
466 ATTRIBUTE12,
467 ATTRIBUTE13,
468 ATTRIBUTE14,
469 ATTRIBUTE15,
470 CREATED_BY,
471 CREATION_DATE,
472 LAST_UPDATED_BY,
473 LAST_UPDATE_DATE,
474 LAST_UPDATE_LOGIN
475 FROM Okl_K_Rate_Params_V
476 WHERE effective_from_date = NVL(p_effective_from_date,effective_from_date)
477 AND khr_id = p_khr_id
478 AND parameter_type_code = NVL(p_parameter_type_code,parameter_type_code);
479 l_okl_k_rate_params_v_u1 okl_k_rate_params_v_u1_csr%ROWTYPE;
480 BEGIN
481 x_no_data_found := TRUE;
482 -- Get current database values
483 OPEN okl_k_rate_params_v_u1_csr (p_effective_from_date,
484 p_chr_id,
485 p_parameter_type_code);
486 FETCH okl_k_rate_params_v_u1_csr INTO
487 x_krpv_rec.khr_id,
488 x_krpv_rec.parameter_type_code,
489 x_krpv_rec.effective_from_date,
490 x_krpv_rec.effective_to_date,
491 x_krpv_rec.interest_index_id,
492 x_krpv_rec.base_rate,
493 x_krpv_rec.interest_start_date,
494 x_krpv_rec.adder_rate,
495 x_krpv_rec.maximum_rate,
496 x_krpv_rec.minimum_rate,
497 x_krpv_rec.principal_basis_code,
498 x_krpv_rec.days_in_a_month_code,
499 x_krpv_rec.days_in_a_year_code,
500 x_krpv_rec.interest_basis_code,
501 x_krpv_rec.rate_delay_code,
502 x_krpv_rec.rate_delay_frequency,
503 x_krpv_rec.compounding_frequency_code,
504 x_krpv_rec.calculation_formula_id,
505 x_krpv_rec.catchup_basis_code,
506 x_krpv_rec.catchup_start_date,
507 x_krpv_rec.catchup_settlement_code,
508 x_krpv_rec.rate_change_start_date,
509 x_krpv_rec.rate_change_frequency_code,
510 x_krpv_rec.rate_change_value,
511 x_krpv_rec.conversion_option_code,
512 x_krpv_rec.next_conversion_date,
513 x_krpv_rec.conversion_type_code,
514 x_krpv_rec.attribute_category,
515 x_krpv_rec.attribute1,
516 x_krpv_rec.attribute2,
517 x_krpv_rec.attribute3,
518 x_krpv_rec.attribute4,
519 x_krpv_rec.attribute5,
520 x_krpv_rec.attribute6,
521 x_krpv_rec.attribute7,
522 x_krpv_rec.attribute8,
523 x_krpv_rec.attribute9,
524 x_krpv_rec.attribute10,
525 x_krpv_rec.attribute11,
526 x_krpv_rec.attribute12,
527 x_krpv_rec.attribute13,
528 x_krpv_rec.attribute14,
529 x_krpv_rec.attribute15,
530 x_krpv_rec.created_by,
531 x_krpv_rec.creation_date,
532 x_krpv_rec.last_updated_by,
533 x_krpv_rec.last_update_date,
534 x_krpv_rec.last_update_login;
535 x_no_data_found := okl_k_rate_params_v_u1_csr%NOTFOUND;
536 CLOSE okl_k_rate_params_v_u1_csr;
537 --RETURN(x_krpv_rec);
538
539 END;
540
541 --Bug# 7566308
542 PROCEDURE validate_rate_params_rbk(
543 p_api_version IN NUMBER,
544 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
545 x_return_status OUT NOCOPY VARCHAR2,
546 x_msg_count OUT NOCOPY NUMBER,
547 x_msg_data OUT NOCOPY VARCHAR2,
548 p_krpv_rec IN krpv_rec_type,
549 p_orig_eff_from_date IN DATE DEFAULT NULL) IS
550
551 CURSOR txn_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
552 SELECT a.date_transaction_occurred,
553 b.date_last_interim_interest_cal
554 FROM okl_trx_contracts a,
555 okl_k_headers b
556 WHERE a.khr_id_new = p_chr_id
557 AND a.tcn_type = 'TRBK'
558 AND a.tsu_code = 'ENTERED'
559 --rkuttiya added for 12.1.1 Multi GAAP
560 AND a.representation_type = 'PRIMARY'
561 --
562 AND a.khr_id_new = b.id;
563
564 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_RATE_PARAMS_RBK';
565 l_api_version CONSTANT NUMBER := 1.0;
566 l_count NUMBER;
567 l_interest_processing_started BOOLEAN := FALSE;
568 l_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
569 l_orig_rate_rec krpv_rec_type;
570 l_no_data BOOLEAN;
571 l_rebook_date DATE;
572 l_pdt_parameter_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
573 l_deal_type VARCHAR2(30);
574 l_interest_calculation_basis VARCHAR2(30);
575 l_revenue_recognition_method VARCHAR2(30);
576 l_last_interest_calc_date DATE;
577 l_curr_effective_date DATE;
578 l_product_id NUMBER;
579 l_contract_start_date DATE;
580
581 begin
582 x_return_status := OKL_API.G_RET_STS_SUCCESS;
583
584 x_return_status := OKL_API.START_ACTIVITY(
585 p_api_name => l_api_name,
586 p_pkg_name => g_pkg_name,
587 p_init_msg_list => p_init_msg_list,
588 l_api_version => l_api_version,
589 p_api_version => p_api_version,
590 p_api_type => G_API_TYPE,
591 x_return_status => x_return_status);
592
593
594 get_product2(
595 p_api_version => p_api_version,
596 p_init_msg_list => p_init_msg_list,
597 x_return_status => x_return_status,
598 x_msg_count => x_msg_count,
599 x_msg_data => x_msg_data,
600 p_khr_id => p_krpv_rec.khr_id,
601 x_product_id => l_product_id,
602 x_pdt_parameter_rec => l_pdt_parameter_rec);
603
604 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
605 RAISE OKL_API.G_EXCEPTION_ERROR;
606 END IF;
607
608 l_deal_type := l_pdt_parameter_rec.deal_type;
609 l_interest_calculation_basis := l_pdt_parameter_rec.interest_calculation_basis;
610 l_revenue_recognition_method := l_pdt_parameter_rec.revenue_recognition_method;
611
612 l_interest_processing_started := interest_processing(
613 p_krpv_rec.khr_id,
614 l_contract_number,
615 l_contract_start_date);
616
617 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
618 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After l_interest_processing_started...');
619 END IF;
620 IF (l_interest_processing_started) THEN
621 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
622 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_interest_processing_started=TRUE');
623 END IF;
624 ELSE
625 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
626 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_interest_processing_started=FALSE');
627 END IF;
628 END IF;
629 get_rate_rec(
630 p_chr_id => p_krpv_rec.khr_id,
631 p_parameter_type_code => p_krpv_rec.parameter_type_code,
632 p_effective_from_date => p_krpv_rec.effective_from_date,
633 x_krpv_rec => l_orig_rate_rec,
634 x_no_data_found => l_no_data);
635 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
636 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After get_rate_rec...');
637 END IF;
638
639 -- If validate_rate_params_rbk is called during create flow, then we need to
640 -- check if rate parameters have been changed from the ones on the previous
641 -- open-ended rate
642 IF (l_no_data) AND (p_orig_eff_from_date IS NOT NULL )THEN
643 get_rate_rec(
644 p_chr_id => p_krpv_rec.khr_id,
645 p_parameter_type_code => p_krpv_rec.parameter_type_code,
646 p_effective_from_date => p_orig_eff_from_date,
647 x_krpv_rec => l_orig_rate_rec,
648 x_no_data_found => l_no_data);
649 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
650 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After get_rate_rec 1...');
651 END IF;
652 END IF;
653
654 IF (l_interest_processing_started) THEN
655 NULL;
656 --get_rate_rec and compare if some values changed which are not allowed
657 IF NOT(l_no_data) THEN
658 IF ((l_orig_rate_rec.BASE_RATE IS NOT NULL AND
659 l_orig_rate_rec.BASE_RATE <> G_MISS_NUM) OR
660 (p_krpv_rec.BASE_RATE IS NOT NULL AND
661 p_krpv_rec.BASE_RATE <> G_MISS_NUM)) AND
662 (l_orig_rate_rec.BASE_RATE <> p_krpv_rec.BASE_RATE) THEN
663 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
664 ,p_msg_name => 'OKL_LLA_VAR_RATE_INT_PROC'
665 ,p_token1 => 'PARAMETER_NAME'
666 ,p_token1_value => 'BASE_RATE'
667 ,p_token2 => 'CONTRACT_NUMBER'
668 ,p_token2_value => l_contract_number);
669 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
670 END IF;
671
672 IF ((l_orig_rate_rec.INTEREST_START_DATE IS NOT NULL AND
673 l_orig_rate_rec.INTEREST_START_DATE <> G_MISS_DATE) OR
674 (p_krpv_rec.INTEREST_START_DATE IS NOT NULL AND
675 p_krpv_rec.INTEREST_START_DATE <> G_MISS_DATE)) AND
676 (l_orig_rate_rec.INTEREST_START_DATE <> p_krpv_rec.INTEREST_START_DATE) THEN
677 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
678 ,p_msg_name => 'OKL_LLA_VAR_RATE_INT_PROC'
679 ,p_token1 => 'PARAMETER_NAME'
680 ,p_token1_value => 'INTEREST_START_DATE'
681 ,p_token2 => 'CONTRACT_NUMBER'
682 ,p_token2_value => l_contract_number);
683 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
684 END IF;
685
686 IF ((l_orig_rate_rec.PRINCIPAL_BASIS_CODE IS NOT NULL AND
687 l_orig_rate_rec.PRINCIPAL_BASIS_CODE <> G_MISS_CHAR) OR
688 (p_krpv_rec.PRINCIPAL_BASIS_CODE IS NOT NULL AND
689 p_krpv_rec.PRINCIPAL_BASIS_CODE <> G_MISS_CHAR)) AND
690 (l_orig_rate_rec.PRINCIPAL_BASIS_CODE <> p_krpv_rec.PRINCIPAL_BASIS_CODE) THEN
691 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
692 ,p_msg_name => 'OKL_LLA_VAR_RATE_INT_PROC'
693 ,p_token1 => 'PARAMETER_NAME'
694 ,p_token1_value => 'PRINCIPAL_BASIS_CODE'
695 ,p_token2 => 'CONTRACT_NUMBER'
696 ,p_token2_value => l_contract_number);
697 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
698 END IF;
699
700 IF ((l_orig_rate_rec.DAYS_IN_A_MONTH_CODE IS NOT NULL AND
701 l_orig_rate_rec.DAYS_IN_A_MONTH_CODE <> G_MISS_CHAR) OR
702 (p_krpv_rec.DAYS_IN_A_MONTH_CODE IS NOT NULL AND
703 p_krpv_rec.DAYS_IN_A_MONTH_CODE <> G_MISS_CHAR)) AND
704 (l_orig_rate_rec.DAYS_IN_A_MONTH_CODE <> p_krpv_rec.DAYS_IN_A_MONTH_CODE) THEN
705 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
706 ,p_msg_name => 'OKL_LLA_VAR_RATE_INT_PROC'
707 ,p_token1 => 'PARAMETER_NAME'
708 ,p_token1_value => 'DAYS_IN_A_MONTH_CODE'
709 ,p_token2 => 'CONTRACT_NUMBER'
710 ,p_token2_value => l_contract_number);
711 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
712 END IF;
713
714 IF ((l_orig_rate_rec.DAYS_IN_A_YEAR_CODE IS NOT NULL AND
715 l_orig_rate_rec.DAYS_IN_A_YEAR_CODE <> G_MISS_CHAR) OR
716 (p_krpv_rec.DAYS_IN_A_YEAR_CODE IS NOT NULL AND
717 p_krpv_rec.DAYS_IN_A_YEAR_CODE <> G_MISS_CHAR)) AND
718 (l_orig_rate_rec.DAYS_IN_A_YEAR_CODE <> p_krpv_rec.DAYS_IN_A_YEAR_CODE) THEN
719 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
720 ,p_msg_name => 'OKL_LLA_VAR_RATE_INT_PROC'
721 ,p_token1 => 'PARAMETER_NAME'
722 ,p_token1_value => 'DAYS_IN_A_YEAR_CODE'
723 ,p_token2 => 'CONTRACT_NUMBER'
724 ,p_token2_value => l_contract_number);
725 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
726 END IF;
727
728 IF ((l_orig_rate_rec.CATCHUP_START_DATE IS NOT NULL AND
729 l_orig_rate_rec.CATCHUP_START_DATE <> G_MISS_DATE) OR
730 (p_krpv_rec.CATCHUP_START_DATE IS NOT NULL AND
731 p_krpv_rec.CATCHUP_START_DATE <> G_MISS_DATE)) AND
732 (l_orig_rate_rec.CATCHUP_START_DATE <> p_krpv_rec.CATCHUP_START_DATE) THEN
733 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
734 ,p_msg_name => 'OKL_LLA_VAR_RATE_INT_PROC'
735 ,p_token1 => 'PARAMETER_NAME'
736 ,p_token1_value => 'CATCHUP_START_DATE'
737 ,p_token2 => 'CONTRACT_NUMBER'
738 ,p_token2_value => l_contract_number);
739 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
740 END IF;
741
742 IF ((l_orig_rate_rec.CATCHUP_SETTLEMENT_CODE IS NOT NULL AND
743 l_orig_rate_rec.CATCHUP_SETTLEMENT_CODE <> G_MISS_CHAR) OR
744 (p_krpv_rec.CATCHUP_SETTLEMENT_CODE IS NOT NULL AND
745 p_krpv_rec.CATCHUP_SETTLEMENT_CODE <> G_MISS_CHAR)) AND
746 (l_orig_rate_rec.CATCHUP_SETTLEMENT_CODE <> p_krpv_rec.CATCHUP_SETTLEMENT_CODE) THEN
747 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
748 ,p_msg_name => 'OKL_LLA_VAR_RATE_INT_PROC'
749 ,p_token1 => 'PARAMETER_NAME'
750 ,p_token1_value => 'CATCHUP_SETTLEMENT_CODE'
751 ,p_token2 => 'CONTRACT_NUMBER'
752 ,p_token2_value => l_contract_number);
753 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
754 END IF;
755
756 IF ((l_orig_rate_rec.RATE_CHANGE_START_DATE IS NOT NULL AND
757 l_orig_rate_rec.RATE_CHANGE_START_DATE <> G_MISS_DATE) OR
758 (p_krpv_rec.RATE_CHANGE_START_DATE IS NOT NULL AND
759 p_krpv_rec.RATE_CHANGE_START_DATE <> G_MISS_DATE)) AND
760 (l_orig_rate_rec.RATE_CHANGE_START_DATE <> p_krpv_rec.RATE_CHANGE_START_DATE) THEN
761 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
762 ,p_msg_name => 'OKL_LLA_VAR_RATE_INT_PROC'
763 ,p_token1 => 'PARAMETER_NAME'
764 ,p_token1_value => 'RATE_CHANGE_START_DATE'
765 ,p_token2 => 'CONTRACT_NUMBER'
766 ,p_token2_value => l_contract_number);
767 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
768 END IF;
769 END IF; -- no_data
770 END IF; -- rebook_copy
771
772 IF NOT(l_no_data) THEN
773 -- If any of the rates change, then check effective date
774 FOR txn_rec IN txn_csr (p_krpv_rec.khr_id)
775 LOOP
776 l_rebook_date := txn_rec.date_transaction_occurred;
777 l_last_interest_calc_date := txn_rec.date_last_interim_interest_cal;
778 END LOOP;
779 l_curr_effective_date := l_orig_rate_rec.effective_from_date;
780
781
782 IF (((l_orig_rate_rec.INTEREST_INDEX_ID IS NOT NULL AND
783 l_orig_rate_rec.INTEREST_INDEX_ID <> G_MISS_NUM) OR
784 (p_krpv_rec.INTEREST_INDEX_ID IS NOT NULL AND
785 p_krpv_rec.INTEREST_INDEX_ID <> G_MISS_NUM)) AND
786 (l_orig_rate_rec.INTEREST_INDEX_ID <> p_krpv_rec.INTEREST_INDEX_ID))
787 OR
788 (((l_orig_rate_rec.ADDER_RATE IS NOT NULL AND
789 l_orig_rate_rec.ADDER_RATE <> G_MISS_NUM) OR
790 (p_krpv_rec.ADDER_RATE IS NOT NULL AND
791 p_krpv_rec.ADDER_RATE <> G_MISS_NUM)) AND
792 (l_orig_rate_rec.ADDER_RATE <> p_krpv_rec.ADDER_RATE))
793 OR
794 (((l_orig_rate_rec.MAXIMUM_RATE IS NOT NULL AND
795 l_orig_rate_rec.MAXIMUM_RATE <> G_MISS_NUM) OR
796 (p_krpv_rec.MAXIMUM_RATE IS NOT NULL AND
797 p_krpv_rec.MAXIMUM_RATE <> G_MISS_NUM)) AND
798 (l_orig_rate_rec.MAXIMUM_RATE <> p_krpv_rec.MAXIMUM_RATE))
799 OR
800 (((l_orig_rate_rec.MINIMUM_RATE IS NOT NULL AND
801 l_orig_rate_rec.MINIMUM_RATE <> G_MISS_NUM) OR
802 (p_krpv_rec.MINIMUM_RATE IS NOT NULL AND
803 p_krpv_rec.MINIMUM_RATE <> G_MISS_NUM)) AND
804 (l_orig_rate_rec.MINIMUM_RATE <> p_krpv_rec.MINIMUM_RATE))
805 OR
806 (((l_orig_rate_rec.INTEREST_BASIS_CODE IS NOT NULL AND
807 l_orig_rate_rec.INTEREST_BASIS_CODE <> G_MISS_CHAR) OR
808 (p_krpv_rec.INTEREST_BASIS_CODE IS NOT NULL AND
809 p_krpv_rec.INTEREST_BASIS_CODE <> G_MISS_CHAR)) AND
810 (l_orig_rate_rec.INTEREST_BASIS_CODE <> p_krpv_rec.INTEREST_BASIS_CODE))
811 OR
812 (((l_orig_rate_rec.RATE_DELAY_CODE IS NOT NULL AND
813 l_orig_rate_rec.RATE_DELAY_CODE <> G_MISS_CHAR) OR
814 (p_krpv_rec.RATE_DELAY_CODE IS NOT NULL AND
815 p_krpv_rec.RATE_DELAY_CODE <> G_MISS_CHAR)) AND
816 (l_orig_rate_rec.RATE_DELAY_CODE <> p_krpv_rec.RATE_DELAY_CODE))
817 OR
818 (((l_orig_rate_rec.RATE_DELAY_FREQUENCY IS NOT NULL AND
819 l_orig_rate_rec.RATE_DELAY_FREQUENCY <> G_MISS_NUM) OR
820 (p_krpv_rec.RATE_DELAY_FREQUENCY IS NOT NULL AND
821 p_krpv_rec.RATE_DELAY_FREQUENCY <> G_MISS_NUM)) AND
822 (l_orig_rate_rec.RATE_DELAY_FREQUENCY <> p_krpv_rec.RATE_DELAY_FREQUENCY))
823 OR
824 (((l_orig_rate_rec.RATE_CHANGE_FREQUENCY_CODE IS NOT NULL AND
825 l_orig_rate_rec.RATE_CHANGE_FREQUENCY_CODE <> G_MISS_CHAR) OR
826 (p_krpv_rec.RATE_CHANGE_FREQUENCY_CODE IS NOT NULL AND
827 p_krpv_rec.RATE_CHANGE_FREQUENCY_CODE <> G_MISS_CHAR)) AND
828 (l_orig_rate_rec.RATE_CHANGE_FREQUENCY_CODE <> p_krpv_rec.RATE_CHANGE_FREQUENCY_CODE))
829 OR
830 (((l_orig_rate_rec.RATE_CHANGE_VALUE IS NOT NULL AND
831 l_orig_rate_rec.RATE_CHANGE_VALUE <> G_MISS_NUM) OR
832 (p_krpv_rec.RATE_CHANGE_VALUE IS NOT NULL AND
833 p_krpv_rec.RATE_CHANGE_VALUE <> G_MISS_NUM)) AND
834 (l_orig_rate_rec.RATE_CHANGE_VALUE <> p_krpv_rec.RATE_CHANGE_VALUE))
835 THEN
836 -- IF any of the above values changed
837 IF (l_deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST') AND
838 l_interest_calculation_basis IN ('FIXED', 'FLOAT_FACTORS')) THEN
839 IF (p_krpv_rec.EFFECTIVE_FROM_DATE < l_orig_rate_rec.EFFECTIVE_FROM_DATE) THEN
840 OKC_API.SET_MESSAGE(
841 p_app_name => G_APP_NAME
842 ,p_msg_name => 'OKL_LLA_VAR_RATE_EFF_DATE');
843 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
844 END IF;
845 ELSIF (l_deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST') AND
846 l_interest_calculation_basis = 'REAMORT' ) THEN
847 IF (p_krpv_rec.EFFECTIVE_FROM_DATE <= l_rebook_date) OR
848 (p_krpv_rec.EFFECTIVE_FROM_DATE <= l_last_interest_calc_date)
849 THEN
850 OKC_API.SET_MESSAGE(
851 p_app_name => G_APP_NAME
852 ,p_msg_name => 'OKL_LLA_VAR_RATE_RBK_DATE');
853 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
854 END IF;
855 ELSIF ((l_deal_type = 'LOAN') AND
856 l_interest_calculation_basis IN
857 ('FIXED', 'FLOAT', 'CATCHUP/CLEANUP' )) THEN
858 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
859 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_krpv_rec.effective_from_date=' || p_krpv_rec.effective_from_date);
860 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_orig_rate_rec.effective_from_date=' || l_orig_rate_rec.effective_from_date);
861 END IF;
862 IF (p_krpv_rec.EFFECTIVE_FROM_DATE < l_orig_rate_rec.EFFECTIVE_FROM_DATE) THEN
863 OKC_API.SET_MESSAGE(
864 p_app_name => G_APP_NAME
865 ,p_msg_name => 'OKL_LLA_VAR_RATE_EFF_DATE');
866 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
867 END IF;
868 ELSIF (l_deal_type = 'LOAN' AND
869 l_interest_calculation_basis = 'REAMORT') THEN
870 IF (p_krpv_rec.EFFECTIVE_FROM_DATE <= l_rebook_date) OR
871 (p_krpv_rec.EFFECTIVE_FROM_DATE <= l_last_interest_calc_date)
872 THEN
873 OKC_API.SET_MESSAGE(
874 p_app_name => G_APP_NAME
875 ,p_msg_name => 'OKL_LLA_VAR_RATE_RBK_DATE');
876 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
877 END IF;
878 ELSIF (l_deal_type = 'LOAN-REVOLVING' AND
879 l_interest_calculation_basis = 'FLOAT') THEN
880 IF (p_krpv_rec.EFFECTIVE_FROM_DATE < l_orig_rate_rec.EFFECTIVE_FROM_DATE) THEN
881 OKC_API.SET_MESSAGE(
882 p_app_name => G_APP_NAME
883 ,p_msg_name => 'OKL_LLA_VAR_RATE_EFF_DATE');
884 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
885 END IF;
886 END IF; -- if deal_type
887 END IF; -- if any rate params changed
888
889 END IF; -- if no_data
890
891 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
892
893 EXCEPTION
894 WHEN OKL_API.G_EXCEPTION_ERROR THEN
895 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
896 p_api_name => l_api_name,
897 p_pkg_name => g_pkg_name,
898 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
899 x_msg_count => x_msg_count,
900 x_msg_data => x_msg_data,
901 p_api_type => g_api_type);
902
903 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
904 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
905 p_api_name => l_api_name,
906 p_pkg_name => g_pkg_name,
907 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
908 x_msg_count => x_msg_count,
909 x_msg_data => x_msg_data,
910 p_api_type => g_api_type);
911
912 WHEN OTHERS THEN
913 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
914 p_api_name => l_api_name,
915 p_pkg_name => g_pkg_name,
916 p_exc_name => 'OTHERS',
917 x_msg_count => x_msg_count,
918 x_msg_data => x_msg_data,
919 p_api_type => g_api_type);
920 end;
921 --Bug# 7566308
922
923 /* This is to be called from contract import and UI*/
924 --Zero
925 PROCEDURE create_k_rate_params(
926 p_api_version IN NUMBER,
927 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
928 x_return_status OUT NOCOPY VARCHAR2,
929 x_msg_count OUT NOCOPY NUMBER,
930 x_msg_data OUT NOCOPY VARCHAR2,
931 p_krpv_rec IN krpv_rec_type,
932 x_krpv_rec OUT NOCOPY krpv_rec_type,
933 p_validate_flag IN VARCHAR2 DEFAULT 'Y') IS
934
935 CURSOR C1(p_id NUMBER) IS
936 SELECT start_date
937 FROM OKC_K_HEADERS_B
938 WHERE ID = p_id;
939
940 CURSOR C2(p_id NUMBER, p_parameter_type_code VARCHAR2) IS
941 SELECT COUNT(1) COUNT1
942 FROM OKL_K_RATE_PARAMS
943 WHERE KHR_ID = p_id
944 AND PARAMETER_TYPE_CODE = p_parameter_type_code
945 AND EFFECTIVE_TO_DATE IS NULL;
946
947 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_K_RATE_PARAMS';
948 l_api_version CONSTANT NUMBER := 1.0;
949
950 l_krpv_rec krpv_rec_type;
951 --x_krpv_rec krpv_rec_type;
952 l_count NUMBER;
953 l_rate_count NUMBER;
954 l_k_rate_tbl krpv_tbl_type;
955 l_pdt_parameter_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
956 l_product_id NUMBER;
957 l_contract_start_date DATE;
958
959 --Bug# 7566308
960 l_rebook_copy BOOLEAN := FALSE;
961
962 l_effective_from_date DATE;
963 l_orig_system_source_code VARCHAR2(30);
964 l_orig_system_id1 NUMBER;
965 l_orig_effective_from_date DATE;
966 l_last_interest_cal_date DATE;
967
968 CURSOR get_effective_from_date_csr(
969 p_khr_id NUMBER,
970 p_parameter_type_code VARCHAR2) IS
971 select rate.effective_from_date,
972 contract.orig_system_source_code,
973 lease.date_last_interim_interest_cal,
974 contract.orig_system_id1
975 FROM OKL_K_RATE_PARAMS rate,
976 OKC_K_HEADERS_B contract,
977 OKL_K_HEADERS lease
978 WHERE rate.khr_id = p_khr_id
979 AND rate.parameter_type_code = p_parameter_type_code
980 AND rate.effective_to_date is null
981 AND rate.khr_id = contract.id
982 AND contract.id = lease.id;
983
984 --Cursor to query the last billed due date of the stream.
985 CURSOR get_last_billed_due_date(
986 p_chr_id OKC_K_HEADERS_B.ID%TYPE
987 , p_stream_purpose OKL_STRM_TYPE_V.STREAM_TYPE_PURPOSE%TYPE) IS
988 SELECT MAX(STE.STREAM_ELEMENT_DATE) LAST_STREAM_DUE_DATE
989 FROM OKL_STRM_ELEMENTS STE
990 , OKL_STREAMS STM
991 , OKL_STRM_TYPE_V STY
992 , OKL_K_HEADERS KHR
993 , OKC_K_HEADERS_B CHR
994 WHERE STM.ID = STE.STM_ID
995 AND STY.ID = STM.STY_ID
996 AND KHR.ID = STM.KHR_ID
997 AND CHR.ID = KHR.ID
998 AND STE.DATE_BILLED IS NOT NULL
999 AND CHR.ID = p_chr_id
1000 AND STY.STREAM_TYPE_PURPOSE = p_stream_purpose;
1001
1002 l_last_billed_due_date DATE;
1003 --Bug# 7566308
1004 begin
1005
1006 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1007
1008 x_return_status := OKL_API.START_ACTIVITY(
1009 p_api_name => l_api_name,
1010 p_pkg_name => g_pkg_name,
1011 p_init_msg_list => p_init_msg_list,
1012 l_api_version => l_api_version,
1013 p_api_version => p_api_version,
1014 p_api_type => G_API_TYPE,
1015 x_return_status => x_return_status);
1016
1017 --Bug# 7566308
1018 l_rebook_copy := is_rebook_copy(p_krpv_rec.khr_id);
1019 IF (l_rebook_copy) THEN
1020
1021 l_effective_from_date := null;
1022 FOR r IN get_effective_from_date_csr(p_krpv_rec.khr_id, p_krpv_rec.parameter_type_code)
1023 LOOP
1024 l_effective_from_date := r.effective_from_date;
1025 l_orig_system_source_code := r.orig_system_source_code;
1026 l_last_interest_cal_date := r.date_last_interim_interest_cal;
1027 l_orig_system_id1 := r.orig_system_id1;
1028 END LOOP;
1029
1030 validate_rate_params_rbk(
1031 p_api_version => p_api_version,
1032 p_init_msg_list => p_init_msg_list,
1033 x_return_status => x_return_status,
1034 x_msg_count => x_msg_count,
1035 x_msg_data => x_msg_data,
1036 p_krpv_rec => p_krpv_rec,
1037 p_orig_eff_from_date => l_effective_from_date);
1038
1039 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1040 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1041 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1042 RAISE OKL_API.G_EXCEPTION_ERROR;
1043 END IF;
1044
1045 END IF;
1046 --Bug# 7566308
1047
1048 FOR r IN C1(p_krpv_rec.khr_id)
1049 LOOP
1050 l_contract_start_date := r.start_date;
1051 END LOOP;
1052 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1053 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_contract_start_date=' || l_contract_start_date);
1054 END IF;
1055
1056 --Bug# 7566308
1057 IF NOT l_rebook_copy THEN
1058 FOR r IN C2(p_krpv_rec.khr_id, p_krpv_rec.parameter_type_code)
1059 LOOP
1060 l_rate_count := r.count1;
1061 END LOOP;
1062
1063 IF (l_rate_count > 0) THEN
1064 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1065 ,p_msg_name => 'OKL_LLA_VAR_RATE_EXISTS');
1066 RAISE OKL_API.G_EXCEPTION_ERROR;
1067 END IF;
1068 END IF;
1069 --Bug# 7566308
1070
1071 l_krpv_rec.khr_id := p_krpv_rec.khr_id;
1072 l_krpv_rec.parameter_type_code := p_krpv_rec.parameter_type_code;
1073 l_krpv_rec.effective_from_date := p_krpv_rec.effective_from_Date;
1074 l_krpv_rec.interest_index_id := p_krpv_rec.interest_index_id;
1075 l_krpv_rec.base_rate := p_krpv_rec.base_rate;
1076 l_krpv_rec.interest_start_date := p_krpv_rec.interest_start_date;
1077 l_krpv_rec.adder_rate := p_krpv_rec.adder_rate;
1078 l_krpv_rec.maximum_rate := p_krpv_rec.maximum_rate;
1079 l_krpv_rec.minimum_rate := p_krpv_rec.minimum_rate;
1080 l_krpv_rec.principal_basis_code := p_krpv_rec.principal_basis_code;
1081 l_krpv_rec.days_in_a_month_code := p_krpv_rec.days_in_a_month_code;
1082 l_krpv_rec.days_in_a_year_code := p_krpv_rec.days_in_a_year_code;
1083 l_krpv_rec.interest_basis_code := p_krpv_rec.interest_basis_code;
1084
1085 l_krpv_rec.rate_delay_code := p_krpv_rec.rate_delay_code;
1086 l_krpv_rec.rate_delay_frequency := p_krpv_rec.rate_delay_frequency;
1087 l_krpv_rec.compounding_frequency_code := p_krpv_rec.compounding_frequency_code;
1088 l_krpv_rec.calculation_formula_id := p_krpv_rec.calculation_formula_id;
1089 l_krpv_rec.catchup_basis_code := p_krpv_rec.catchup_basis_code;
1090 l_krpv_rec.catchup_start_date := p_krpv_rec.catchup_start_date;
1091 l_krpv_rec.catchup_settlement_code := p_krpv_rec.catchup_settlement_code;
1092 l_krpv_rec.catchup_frequency_code := p_krpv_rec.catchup_frequency_code;
1093 l_krpv_rec.rate_change_start_date := p_krpv_rec.rate_change_start_date;
1094 l_krpv_rec.rate_change_frequency_code := p_krpv_rec.rate_change_frequency_code;
1095 l_krpv_rec.rate_change_value := p_krpv_rec.rate_change_value;
1096
1097 l_krpv_rec.conversion_option_code := p_krpv_rec.conversion_option_code;
1098 l_krpv_rec.next_conversion_date := p_krpv_rec.next_conversion_date;
1099 l_krpv_rec.conversion_type_code := p_krpv_rec.conversion_type_code;
1100 print_krpv_rec(l_krpv_rec);
1101
1102 select count(1) into l_count
1103 FROM OKL_K_RATE_PARAMS
1104 WHERE KHR_ID = p_krpv_rec.khr_id
1105 AND EFFECTIVE_FROM_DATE = p_krpv_rec.effective_from_date
1106 AND PARAMETER_TYPE_CODE = p_krpv_rec.parameter_type_code;
1107
1108 l_k_rate_tbl(1) := l_krpv_rec;
1109 --Bug# 7440232
1110 IF (p_validate_flag IN ('Y','F')) THEN
1111 get_product2(
1112 p_api_version => p_api_version,
1113 p_init_msg_list => p_init_msg_list,
1114 x_return_status => x_return_status,
1115 x_msg_count => x_msg_count,
1116 x_msg_data => x_msg_data,
1117 p_khr_id => l_krpv_rec.khr_id,
1118 x_product_id => l_product_id,
1119 x_pdt_parameter_rec => l_pdt_parameter_rec);
1120
1121 --Bug# 7440232
1122 validate_k_rate_params(
1123 p_api_version => p_api_version,
1124 p_init_msg_list => p_init_msg_list,
1125 x_return_status => x_return_status,
1126 x_msg_count => x_msg_count,
1127 x_msg_data => x_msg_data,
1128 p_product_id => l_product_id,
1129 p_k_rate_tbl => l_k_rate_tbl,
1130 p_validate_flag => p_validate_flag);
1131
1132 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1133 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1134 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1135 RAISE OKL_API.G_EXCEPTION_ERROR;
1136 END IF;
1137
1138 -- Bug 4722746
1139 IF (l_pdt_parameter_rec.interest_calculation_basis = 'CATCHUP/CLEANUP'
1140 AND l_pdt_parameter_rec.revenue_recognition_method = 'STREAMS') THEN
1141
1142 --print('Catchup :1');
1143 IF (l_krpv_rec.catchup_frequency_code is NULL OR
1144 l_krpv_rec.catchup_frequency_code = G_MISS_CHAR) AND
1145 (l_krpv_rec.catchup_start_date is NULL OR
1146 l_krpv_rec.catchup_start_date = G_MISS_DATE) AND
1147 (l_krpv_rec.catchup_settlement_code is NULL OR
1148 l_krpv_rec.catchup_settlement_code = G_MISS_CHAR) AND
1149 (l_krpv_rec.catchup_basis_code is NULL OR
1150 l_krpv_rec.catchup_basis_code = G_MISS_CHAR) THEN
1151 -- All four are null
1152 IF (l_krpv_rec.rate_change_start_date is NULL OR
1153 l_krpv_rec.rate_change_start_date = G_MISS_DATE) AND
1154 (l_krpv_rec.rate_change_value is NULL OR
1155 l_krpv_rec.rate_change_value = G_MISS_NUM) AND
1156 (l_krpv_rec.rate_change_frequency_code is NULL OR
1157 l_krpv_rec.rate_change_frequency_code = G_MISS_CHAR) AND
1158 (l_krpv_rec.compounding_frequency_code is NULL OR
1159 l_krpv_rec.compounding_frequency_code = G_MISS_CHAR) AND
1160 (l_krpv_rec.calculation_formula_id is NULL OR
1161 l_krpv_rec.calculation_formula_id = G_MISS_NUM) AND
1162 (l_krpv_rec.rate_delay_code is NULL OR
1163 l_krpv_rec.rate_delay_code = G_MISS_CHAR) AND
1164 (l_krpv_rec.rate_delay_frequency is NULL OR
1165 l_krpv_rec.rate_delay_frequency = G_MISS_NUM) THEN
1166 NULL;
1167 ELSE
1168 OKC_API.set_message(p_app_name => G_APP_NAME,
1169 p_msg_name => G_REQUIRED_VALUE,
1170 p_token1 => G_COL_NAME_TOKEN,
1171 p_token1_value => 'CATCHUP FREQUENCY');
1172 RAISE OKL_API.G_EXCEPTION_ERROR;
1173 END IF;
1174
1175 ELSE
1176 -- One of the four is not null
1177 IF (l_krpv_rec.catchup_frequency_code is NOT NULL AND
1178 l_krpv_rec.catchup_frequency_code <> G_MISS_CHAR) THEN
1179 NULL;
1180 ELSE
1181 OKC_API.set_message(p_app_name => G_APP_NAME,
1182 p_msg_name => G_REQUIRED_VALUE,
1183 p_token1 => G_COL_NAME_TOKEN,
1184 p_token1_value => 'Catchup Frequency');
1185 RAISE OKL_API.G_EXCEPTION_ERROR;
1186 END IF;
1187
1188 --print('Catchup :2');
1189 IF (l_krpv_rec.catchup_start_date is NOT NULL AND
1190 l_krpv_rec.catchup_start_date <> G_MISS_DATE) THEN
1191 NULL;
1192 ELSE
1193 -- Default from contract start date
1194 l_krpv_rec.catchup_start_date := l_contract_start_date;
1195 END IF;
1196
1197 --print('Catchup :3');
1198 IF (l_krpv_rec.catchup_settlement_code is NOT NULL AND
1199 l_krpv_rec.catchup_settlement_code <> G_MISS_CHAR) THEN
1200 NULL;
1201 ELSE
1202 OKC_API.set_message(p_app_name => G_APP_NAME,
1203 p_msg_name => G_REQUIRED_VALUE,
1204 p_token1 => G_COL_NAME_TOKEN,
1205 p_token1_value => 'Catchup Settlement');
1206 RAISE OKL_API.G_EXCEPTION_ERROR;
1207 END IF;
1208
1209 --print('Catchup :4');
1210 IF (l_krpv_rec.catchup_basis_code is NOT NULL AND
1211 l_krpv_rec.catchup_basis_code <> G_MISS_CHAR) THEN
1212 NULL;
1213 ELSE
1214 OKC_API.set_message(p_app_name => G_APP_NAME,
1215 p_msg_name => G_REQUIRED_VALUE,
1216 p_token1 => G_COL_NAME_TOKEN,
1217 p_token1_value => 'Catchup Basis');
1218 RAISE OKL_API.G_EXCEPTION_ERROR;
1219 END IF;
1220
1221
1222 END IF;
1223
1224 END IF; -- if STREAMS and CATCHUP/CLEANUP
1225
1226 --Bug# 7566308
1227 -- Moved validations on adding a new effective dated rate during rebook
1228 -- from copy_k_rate_params to here
1229 -- Start Rebook checks
1230 IF (l_rebook_copy) THEN
1231
1232 -- Bug 4999888
1233 IF (l_pdt_parameter_rec.interest_calculation_basis = 'FIXED' and l_pdt_parameter_rec.revenue_recognition_method='ACTUAL') THEN
1234 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1235 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'copy:Inside FIXED and ACTUAL...');
1236 END IF;
1237
1238 OPEN get_last_billed_due_date(l_orig_system_id1, 'LOAN_PAYMENT');
1239 FETCH get_last_billed_due_date
1240 INTO l_last_billed_due_date;
1241 CLOSE get_last_billed_due_date;
1242
1243 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1244 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_last_billed_due_date=' || l_last_billed_due_date);
1245 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_krpv_rec.effective_from_Date=' || p_krpv_rec.effective_from_Date);
1246 END IF;
1247 IF (l_last_billed_due_date IS NOT NULL AND
1248 p_krpv_rec.effective_from_Date <= l_last_billed_due_date) THEN
1249 OKC_API.set_message(p_app_name => G_APP_NAME,
1250 p_msg_name => 'OKL_LLA_VAR_RATE_INT_DATE',
1251 p_token1 => 'EFF_DATE',
1252 p_token1_value => p_krpv_rec.effective_from_Date,
1253 p_token2 => 'INTEREST_DATE',
1254 p_token2_value => l_last_billed_due_date);
1255 RAISE OKL_API.G_EXCEPTION_ERROR;
1256 END IF;
1257 END IF;
1258
1259 -- Bug 4999888
1260 IF (l_pdt_parameter_rec.interest_calculation_basis IN ('FLOAT', 'REAMORT', 'FLOAT_FACTORS', 'CATCHUP/CLEANUP')) THEN
1261 IF (l_last_interest_cal_date IS NOT NULL) THEN
1262 IF (p_krpv_rec.effective_from_Date <= l_last_interest_cal_date) THEN
1263 OKC_API.set_message(p_app_name => G_APP_NAME,
1264 p_msg_name => 'OKL_LLA_VAR_RATE_INT_DATE',
1265 p_token1 => 'EFF_DATE',
1266 p_token1_value => p_krpv_rec.effective_from_Date,
1267 p_token2 => 'INTEREST_DATE',
1268 p_token2_value => l_last_interest_cal_date);
1269 RAISE OKL_API.G_EXCEPTION_ERROR;
1270 END IF;
1271 END IF;
1272 END IF;
1273 END IF;
1274 -- End Rebook checks
1275 --Bug# 7566308
1276
1277 END IF; -- if validate_flag
1278
1279 IF (l_count > 0) THEN
1280 OKL_KRP_PVT.update_row(
1281 p_api_version,
1282 p_init_msg_list,
1283 x_return_status,
1284 x_msg_count,
1285 x_msg_data,
1286 l_krpv_rec,
1287 x_krpv_rec);
1288 ELSE
1289 OKL_KRP_PVT.insert_row(
1290 p_api_version,
1291 p_init_msg_list,
1292 x_return_status,
1293 x_msg_count,
1294 x_msg_data,
1295 l_krpv_rec,
1296 x_krpv_rec);
1297
1298 --Bug# 7566308
1299 -- End date the previous open-ended rate record
1300 -- when a new effective dated rate is added
1301 IF (l_rebook_copy) THEN
1302 FOR r IN get_effective_from_date_csr(l_orig_system_id1,
1303 p_krpv_rec.parameter_type_code)
1304 LOOP
1305 l_orig_effective_from_date := r.effective_from_date;
1306 END LOOP;
1307
1308 UPDATE OKL_K_RATE_PARAMS
1309 SET EFFECTIVE_TO_DATE = p_krpv_rec.effective_from_Date - 1
1310 WHERE KHR_ID = p_krpv_rec.khr_id
1311 AND PARAMETER_TYPE_CODE = p_krpv_rec.parameter_type_code
1312 AND EFFECTIVE_FROM_DATE = l_effective_from_date
1313 AND EFFECTIVE_TO_DATE IS NULL;
1314 END IF;
1315 --Bug# 7566308
1316 END IF;
1317
1318 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1319 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1320 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1321 RAISE OKL_API.G_EXCEPTION_ERROR;
1322 END IF;
1323
1324 okl_contract_status_pub.cascade_lease_status_edit
1325 (p_api_version => p_api_version,
1326 p_init_msg_list => p_init_msg_list,
1327 x_return_status => x_return_status,
1328 x_msg_count => x_msg_count,
1329 x_msg_data => x_msg_data,
1330 p_chr_id => l_krpv_rec.khr_id);
1331
1332 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
1333 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1334 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
1335 raise OKL_API.G_EXCEPTION_ERROR;
1336 End If;
1337
1338 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1339
1340 EXCEPTION
1341 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1342 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1343 p_api_name => l_api_name,
1344 p_pkg_name => g_pkg_name,
1345 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1346 x_msg_count => x_msg_count,
1347 x_msg_data => x_msg_data,
1348 p_api_type => g_api_type);
1349
1350 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1351 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1352 p_api_name => l_api_name,
1353 p_pkg_name => g_pkg_name,
1354 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1355 x_msg_count => x_msg_count,
1356 x_msg_data => x_msg_data,
1357 p_api_type => g_api_type);
1358
1359 WHEN OTHERS THEN
1360 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1361 p_api_name => l_api_name,
1362 p_pkg_name => g_pkg_name,
1363 p_exc_name => 'OTHERS',
1364 x_msg_count => x_msg_count,
1365 x_msg_data => x_msg_data,
1366 p_api_type => g_api_type);
1367
1368 end;
1369
1370 -- First
1371 PROCEDURE create_k_rate_params(
1372 p_api_version IN NUMBER,
1373 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1374 x_return_status OUT NOCOPY VARCHAR2,
1375 x_msg_count OUT NOCOPY NUMBER,
1376 x_msg_data OUT NOCOPY VARCHAR2,
1377 p_krpr_rec IN krpr_rec_type,
1378 x_krpr_rec OUT NOCOPY krpr_rec_type) is
1379
1380 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_K_RATE_PARAMS';
1381 l_api_version CONSTANT NUMBER := 1.0;
1382
1383 l_krpv_rec krpv_rec_type;
1384 x_krpv_rec krpv_rec_type;
1385 l_count NUMBER;
1386
1387 begin
1388
1389 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1390
1391 x_return_status := OKL_API.START_ACTIVITY(
1392 p_api_name => l_api_name,
1393 p_pkg_name => g_pkg_name,
1394 p_init_msg_list => p_init_msg_list,
1395 l_api_version => l_api_version,
1396 p_api_version => p_api_version,
1397 p_api_type => G_API_TYPE,
1398 x_return_status => x_return_status);
1399
1400
1401 l_krpv_rec.khr_id := p_krpr_rec.khr_id;
1402 l_krpv_rec.parameter_type_code := p_krpr_rec.parameter_type_code;
1403 l_krpv_rec.effective_from_date := p_krpr_rec.effective_from_Date;
1404 l_krpv_rec.interest_index_id := p_krpr_rec.interest_index_id;
1405 l_krpv_rec.base_rate := p_krpr_rec.base_rate;
1406 l_krpv_rec.interest_start_date := p_krpr_rec.interest_start_date;
1407 l_krpv_rec.adder_rate := p_krpr_rec.adder_rate;
1408 l_krpv_rec.maximum_rate := p_krpr_rec.maximum_rate;
1409 l_krpv_rec.minimum_rate := p_krpr_rec.minimum_rate;
1410 l_krpv_rec.principal_basis_code := p_krpr_rec.principal_basis_code;
1411 l_krpv_rec.days_in_a_month_code := p_krpr_rec.days_in_a_month_code;
1412 l_krpv_rec.days_in_a_year_code := p_krpr_rec.days_in_a_year_code;
1413 l_krpv_rec.interest_basis_code := p_krpr_rec.interest_basis_code;
1414
1415 select count(1) into l_count
1416 FROM OKL_K_RATE_PARAMS
1417 WHERE KHR_ID = p_krpr_rec.khr_id
1418 AND EFFECTIVE_FROM_DATE = p_krpr_rec.effective_from_date
1419 AND PARAMETER_TYPE_CODE = p_krpr_rec.parameter_type_code;
1420
1421 IF (l_count > 0) THEN
1422 OKL_KRP_PVT.update_row(
1423 p_api_version,
1424 p_init_msg_list,
1425 x_return_status,
1426 x_msg_count,
1427 x_msg_data,
1428 l_krpv_rec,
1429 x_krpv_rec);
1430 ELSE
1431 OKL_KRP_PVT.insert_row(
1432 p_api_version,
1433 p_init_msg_list,
1434 x_return_status,
1435 x_msg_count,
1436 x_msg_data,
1437 l_krpv_rec,
1438 x_krpv_rec);
1439 END IF;
1440
1441 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1442 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1443 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1444 RAISE OKL_API.G_EXCEPTION_ERROR;
1445 END IF;
1446
1447 x_krpr_rec.khr_id := x_krpv_rec.khr_id;
1448 x_krpr_rec.parameter_type_code := x_krpv_rec.parameter_type_code;
1449 x_krpr_rec.effective_from_date := x_krpv_rec.effective_from_date;
1450 x_krpr_rec.effective_to_date := x_krpv_rec.effective_to_date;
1451 x_krpr_rec.interest_index_id := x_krpv_rec.interest_index_id;
1452 x_krpr_rec.base_rate := x_krpv_rec.base_rate;
1453 x_krpr_rec.interest_start_date := x_krpv_rec.interest_start_date;
1454 x_krpr_rec.adder_rate := x_krpv_rec.adder_rate;
1455 x_krpr_rec.maximum_rate := x_krpv_rec.maximum_rate;
1456 x_krpr_rec.minimum_rate := x_krpv_rec.minimum_rate;
1457 x_krpr_rec.principal_basis_code := x_krpv_rec.principal_basis_code;
1458 x_krpr_rec.days_in_a_month_code := x_krpv_rec.days_in_a_month_code;
1459 x_krpr_rec.days_in_a_year_code := x_krpv_rec.days_in_a_year_code;
1460 x_krpr_rec.interest_basis_code := x_krpv_rec.interest_basis_code;
1461
1462 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1463
1464 EXCEPTION
1465 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1466 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1467 p_api_name => l_api_name,
1468 p_pkg_name => g_pkg_name,
1469 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1470 x_msg_count => x_msg_count,
1471 x_msg_data => x_msg_data,
1472 p_api_type => g_api_type);
1473
1474 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1475 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1476 p_api_name => l_api_name,
1477 p_pkg_name => g_pkg_name,
1478 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1479 x_msg_count => x_msg_count,
1480 x_msg_data => x_msg_data,
1481 p_api_type => g_api_type);
1482
1483 WHEN OTHERS THEN
1484 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1485 p_api_name => l_api_name,
1486 p_pkg_name => g_pkg_name,
1487 p_exc_name => 'OTHERS',
1488 x_msg_count => x_msg_count,
1489 x_msg_data => x_msg_data,
1490 p_api_type => g_api_type);
1491
1492 end;
1493
1494 -- Second
1495 PROCEDURE create_k_rate_params(
1496 p_api_version IN NUMBER,
1497 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1498 x_return_status OUT NOCOPY VARCHAR2,
1499 x_msg_count OUT NOCOPY NUMBER,
1500 x_msg_data OUT NOCOPY VARCHAR2,
1501 p_krpar_rec IN krpar_rec_type,
1502 x_krpar_rec OUT NOCOPY krpar_rec_type) IS
1503
1504 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_K_RATE_PARAMS';
1505 l_api_version CONSTANT NUMBER := 1.0;
1506 l_krpv_rec krpv_rec_type;
1507 x_krpv_rec krpv_rec_type;
1508 l_count NUMBER;
1509
1510 begin
1511 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1512
1513 x_return_status := OKL_API.START_ACTIVITY(
1514 p_api_name => l_api_name,
1515 p_pkg_name => g_pkg_name,
1516 p_init_msg_list => p_init_msg_list,
1517 l_api_version => l_api_version,
1518 p_api_version => p_api_version,
1519 p_api_type => G_API_TYPE,
1520 x_return_status => x_return_status);
1521
1522 select count(1) into l_count
1523 FROM OKL_K_RATE_PARAMS
1524 WHERE KHR_ID = p_krpar_rec.khr_id
1525 AND EFFECTIVE_FROM_DATE = p_krpar_rec.effective_from_date
1526 AND PARAMETER_TYPE_CODE = p_krpar_rec.parameter_type_code;
1527
1528
1529 l_krpv_rec.khr_id := p_krpar_rec.khr_id;
1530 l_krpv_rec.parameter_type_code := p_krpar_rec.parameter_type_code;
1531 l_krpv_rec.effective_from_date := p_krpar_rec.effective_from_date;
1532 l_krpv_rec.effective_to_date := p_krpar_rec.effective_to_date;
1533 l_krpv_rec.rate_delay_code := p_krpar_rec.rate_delay_code;
1534 l_krpv_rec.rate_delay_frequency := p_krpar_rec.rate_delay_frequency;
1535 l_krpv_rec.compounding_frequency_code := p_krpar_rec.compounding_frequency_code;
1536 l_krpv_rec.calculation_formula_id := p_krpar_rec.calculation_formula_id;
1537 l_krpv_rec.catchup_basis_code := p_krpar_rec.catchup_basis_code;
1538 l_krpv_rec.catchup_start_date := p_krpar_rec.catchup_start_date;
1539 l_krpv_rec.catchup_settlement_code := p_krpar_rec.catchup_settlement_code;
1540 l_krpv_rec.rate_change_start_date := p_krpar_rec.rate_change_start_date;
1541 l_krpv_rec.rate_change_frequency_code := p_krpar_rec.rate_change_frequency_code;
1542 l_krpv_rec.rate_change_value := p_krpar_rec.rate_change_value;
1543 IF (l_count > 0) then
1544 OKL_KRP_PVT.update_row(
1545 p_api_version,
1546 p_init_msg_list,
1547 x_return_status,
1548 x_msg_count,
1549 x_msg_data,
1550 l_krpv_rec,
1551 x_krpv_rec);
1552 ELSE
1553 OKL_KRP_PVT.insert_row(
1554 p_api_version,
1555 p_init_msg_list,
1556 x_return_status,
1557 x_msg_count,
1558 x_msg_data,
1559 l_krpv_rec,
1560 x_krpv_rec);
1561 END IF;
1562
1563 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1564 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1565 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1566 RAISE OKL_API.G_EXCEPTION_ERROR;
1567 END IF;
1568
1569 x_krpar_rec.khr_id := x_krpv_rec.khr_id;
1570 x_krpar_rec.parameter_type_code := x_krpv_rec.parameter_type_code;
1571 x_krpar_rec.effective_from_date := x_krpv_rec.effective_from_date;
1572 x_krpar_rec.effective_to_date := x_krpv_rec.effective_to_date;
1573 x_krpar_rec.rate_delay_code := x_krpv_rec.rate_delay_code;
1574 x_krpar_rec.rate_delay_frequency := x_krpv_rec.rate_delay_frequency;
1575 x_krpar_rec.compounding_frequency_code := x_krpv_rec.compounding_frequency_code;
1576 x_krpar_rec.calculation_formula_id := x_krpv_rec.calculation_formula_id;
1577 x_krpar_rec.catchup_basis_code := x_krpv_rec.catchup_basis_code;
1578 x_krpar_rec.catchup_start_date := x_krpv_rec.catchup_start_date;
1579 x_krpar_rec.catchup_settlement_code := x_krpv_rec.catchup_settlement_code;
1580 x_krpar_rec.rate_change_start_date := x_krpv_rec.rate_change_start_date;
1581 x_krpar_rec.rate_change_frequency_code := x_krpv_rec.rate_change_frequency_code;
1582 x_krpar_rec.rate_change_value := x_krpv_rec.rate_change_value;
1583
1584 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1585
1586 EXCEPTION
1587 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1588 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1589 p_api_name => l_api_name,
1590 p_pkg_name => g_pkg_name,
1591 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1592 x_msg_count => x_msg_count,
1593 x_msg_data => x_msg_data,
1594 p_api_type => g_api_type);
1595
1596 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1597 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1598 p_api_name => l_api_name,
1599 p_pkg_name => g_pkg_name,
1600 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1601 x_msg_count => x_msg_count,
1602 x_msg_data => x_msg_data,
1603 p_api_type => g_api_type);
1604
1605 WHEN OTHERS THEN
1606 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1607 p_api_name => l_api_name,
1608 p_pkg_name => g_pkg_name,
1609 p_exc_name => 'OTHERS',
1610 x_msg_count => x_msg_count,
1611 x_msg_data => x_msg_data,
1612 p_api_type => g_api_type);
1613 end;
1614
1615 -- Third
1616 PROCEDURE create_k_rate_params(
1617 p_api_version IN NUMBER,
1618 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1619 x_return_status OUT NOCOPY VARCHAR2,
1620 x_msg_count OUT NOCOPY NUMBER,
1621 x_msg_data OUT NOCOPY VARCHAR2,
1622 p_krpc_rec IN krpc_rec_type,
1623 x_krpc_rec OUT NOCOPY krpc_rec_type) IS
1624
1625 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_K_RATE_PARAMS';
1626 l_api_version CONSTANT NUMBER := 1.0;
1627 l_krpv_rec krpv_rec_type;
1628 x_krpv_rec krpv_rec_type;
1629 l_count NUMBER;
1630
1631 begin
1632 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1633
1634 x_return_status := OKL_API.START_ACTIVITY(
1635 p_api_name => l_api_name,
1636 p_pkg_name => g_pkg_name,
1637 p_init_msg_list => p_init_msg_list,
1638 l_api_version => l_api_version,
1639 p_api_version => p_api_version,
1640 p_api_type => G_API_TYPE,
1641 x_return_status => x_return_status);
1642
1643 select count(1) into l_count
1644 FROM OKL_K_RATE_PARAMS
1645 WHERE KHR_ID = p_krpc_rec.khr_id
1646 AND EFFECTIVE_FROM_DATE = p_krpc_rec.effective_from_date
1647 AND PARAMETER_TYPE_CODE = p_krpc_rec.parameter_type_code;
1648
1649 l_krpv_rec.khr_id := p_krpc_rec.khr_id;
1650 l_krpv_rec.parameter_type_code := p_krpc_rec.parameter_type_code;
1651 l_krpv_rec.effective_from_date := p_krpc_rec.effective_from_date;
1652 l_krpv_rec.effective_to_date := p_krpc_rec.effective_to_date;
1653 l_krpv_rec.conversion_option_code := p_krpc_rec.conversion_option_code;
1654 l_krpv_rec.next_conversion_date := p_krpc_rec.next_conversion_date;
1655 l_krpv_rec.conversion_type_code := p_krpc_rec.conversion_type_code;
1656
1657 IF (l_count > 0) then
1658 OKL_KRP_PVT.update_row(
1659 p_api_version,
1660 p_init_msg_list,
1661 x_return_status,
1662 x_msg_count,
1663 x_msg_data,
1664 l_krpv_rec,
1665 x_krpv_rec);
1666 ELSE
1667 OKL_KRP_PVT.insert_row(
1668 p_api_version,
1669 p_init_msg_list,
1670 x_return_status,
1671 x_msg_count,
1672 x_msg_data,
1673 l_krpv_rec,
1674 x_krpv_rec);
1675 END IF;
1676
1677 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1678 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1679 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1680 RAISE OKL_API.G_EXCEPTION_ERROR;
1681 END IF;
1682
1683 x_krpc_rec.khr_id := x_krpv_rec.khr_id;
1684 x_krpc_rec.parameter_type_code := x_krpv_rec.parameter_type_code;
1685 x_krpc_rec.effective_from_date := x_krpv_rec.effective_from_date;
1686 x_krpc_rec.effective_to_date := x_krpv_rec.effective_to_date;
1687 x_krpc_rec.conversion_option_code := x_krpv_rec.conversion_option_code;
1688 x_krpc_rec.next_conversion_date := x_krpv_rec.next_conversion_date;
1689 x_krpc_rec.conversion_type_code := x_krpv_rec.conversion_type_code;
1690
1691 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1692
1693 EXCEPTION
1694 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1695 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1696 p_api_name => l_api_name,
1697 p_pkg_name => g_pkg_name,
1698 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1699 x_msg_count => x_msg_count,
1700 x_msg_data => x_msg_data,
1701 p_api_type => g_api_type);
1702
1703 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1704 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1705 p_api_name => l_api_name,
1706 p_pkg_name => g_pkg_name,
1707 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1708 x_msg_count => x_msg_count,
1709 x_msg_data => x_msg_data,
1710 p_api_type => g_api_type);
1711
1712 WHEN OTHERS THEN
1713 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1714 p_api_name => l_api_name,
1715 p_pkg_name => g_pkg_name,
1716 p_exc_name => 'OTHERS',
1717 x_msg_count => x_msg_count,
1718 x_msg_data => x_msg_data,
1719 p_api_type => g_api_type);
1720 end;
1721
1722 /* For both UI and contract import */
1723 --Zero
1724 PROCEDURE update_k_rate_params(
1725 p_api_version IN NUMBER,
1726 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1727 x_return_status OUT NOCOPY VARCHAR2,
1728 x_msg_count OUT NOCOPY NUMBER,
1729 x_msg_data OUT NOCOPY VARCHAR2,
1730 p_krpv_rec IN krpv_rec_type,
1731 x_krpv_rec OUT NOCOPY krpv_rec_type) IS
1732
1733 CURSOR txn_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
1734 SELECT a.date_transaction_occurred,
1735 b.date_last_interim_interest_cal
1736 FROM okl_trx_contracts a,
1737 okl_k_headers b
1738 WHERE a.khr_id_new = p_chr_id
1739 AND a.tcn_type = 'TRBK'
1740 AND a.tsu_code = 'ENTERED'
1741 --rkuttiya added for 12.1.1 Multi GAAP
1742 AND a.representation_type = 'PRIMARY'
1743 --
1744 AND a.khr_id_new = b.id;
1745
1746 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_K_RATE_PARAMS';
1747 l_api_version CONSTANT NUMBER := 1.0;
1748 l_krpv_rec krpv_rec_type;
1749 --x_krpv_rec krpv_rec_type;
1750 l_count NUMBER;
1751 l_rebook_copy BOOLEAN := FALSE;
1752 l_interest_processing_started BOOLEAN := FALSE;
1753 l_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
1754 l_orig_rate_rec krpv_rec_type;
1755 l_no_data BOOLEAN;
1756 l_rebook_date DATE;
1757 l_pdt_parameter_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
1758 l_deal_type VARCHAR2(30);
1759 l_interest_calculation_basis VARCHAR2(30);
1760 l_revenue_recognition_method VARCHAR2(30);
1761 l_last_interest_calc_date DATE;
1762 l_curr_effective_date DATE;
1763 l_product_id NUMBER;
1764 l_contract_start_date DATE;
1765 l_krpv_tbl krpv_tbl_type;
1766 --l_k_rate_tbl krpv_tbl_type;
1767 begin
1768 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1769
1770 x_return_status := OKL_API.START_ACTIVITY(
1771 p_api_name => l_api_name,
1772 p_pkg_name => g_pkg_name,
1773 p_init_msg_list => p_init_msg_list,
1774 l_api_version => l_api_version,
1775 p_api_version => p_api_version,
1776 p_api_type => G_API_TYPE,
1777 x_return_status => x_return_status);
1778
1779 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1780 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Update_k_rate_params:Printing input p_krpv_rec...');
1781 END IF;
1782 print_krpv_rec(p_krpv_rec);
1783
1784 get_product2(
1785 p_api_version => p_api_version,
1786 p_init_msg_list => p_init_msg_list,
1787 x_return_status => x_return_status,
1788 x_msg_count => x_msg_count,
1789 x_msg_data => x_msg_data,
1790 p_khr_id => p_krpv_rec.khr_id,
1791 x_product_id => l_product_id,
1792 x_pdt_parameter_rec => l_pdt_parameter_rec);
1793
1794 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1795 RAISE OKL_API.G_EXCEPTION_ERROR;
1796 END IF;
1797
1798 l_deal_type := l_pdt_parameter_rec.deal_type;
1799 l_interest_calculation_basis := l_pdt_parameter_rec.interest_calculation_basis;
1800 l_revenue_recognition_method := l_pdt_parameter_rec.revenue_recognition_method;
1801
1802 --Bug# 7566308
1803 l_rebook_copy := is_rebook_copy(p_krpv_rec.khr_id);
1804 IF (l_rebook_copy) THEN
1805 validate_rate_params_rbk(
1806 p_api_version => p_api_version,
1807 p_init_msg_list => p_init_msg_list,
1808 x_return_status => x_return_status,
1809 x_msg_count => x_msg_count,
1810 x_msg_data => x_msg_data,
1811 p_krpv_rec => p_krpv_rec);
1812
1813 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1814 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1815 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1816 RAISE OKL_API.G_EXCEPTION_ERROR;
1817 END IF;
1818 END IF;
1819 --Bug# 7566308
1820
1821 l_krpv_rec.khr_id := p_krpv_rec.khr_id;
1822 l_krpv_rec.parameter_type_code := p_krpv_rec.parameter_type_code;
1823 l_krpv_rec.effective_from_date := p_krpv_rec.effective_from_date;
1824 l_krpv_rec.effective_to_date := p_krpv_rec.effective_to_date;
1825 l_krpv_rec.interest_index_id := p_krpv_rec.interest_index_id;
1826 l_krpv_rec.base_rate := p_krpv_rec.base_rate;
1827 l_krpv_rec.interest_start_date := p_krpv_rec.interest_start_date;
1828 l_krpv_rec.adder_rate := p_krpv_rec.adder_rate;
1829 l_krpv_rec.maximum_rate := p_krpv_rec.maximum_rate;
1830 l_krpv_rec.minimum_rate := p_krpv_rec.minimum_rate;
1831 l_krpv_rec.principal_basis_code := p_krpv_rec.principal_basis_code;
1832 l_krpv_rec.days_in_a_month_code := p_krpv_rec.days_in_a_month_code;
1833 l_krpv_rec.days_in_a_year_code := p_krpv_rec.days_in_a_year_code;
1834 l_krpv_rec.interest_basis_code := p_krpv_rec.interest_basis_code;
1835 l_krpv_rec.rate_delay_code := p_krpv_rec.rate_delay_code;
1836 l_krpv_rec.rate_delay_frequency := p_krpv_rec.rate_delay_frequency;
1837 l_krpv_rec.compounding_frequency_code := p_krpv_rec.compounding_frequency_code;
1838 l_krpv_rec.calculation_formula_id := p_krpv_rec.calculation_formula_id;
1839 l_krpv_rec.catchup_basis_code := p_krpv_rec.catchup_basis_code;
1840 l_krpv_rec.catchup_start_date := p_krpv_rec.catchup_start_date;
1841 l_krpv_rec.catchup_settlement_code := p_krpv_rec.catchup_settlement_code;
1842 l_krpv_rec.catchup_frequency_code := p_krpv_rec.catchup_frequency_code;
1843 l_krpv_rec.rate_change_start_date := p_krpv_rec.rate_change_start_date;
1844 l_krpv_rec.rate_change_frequency_code := p_krpv_rec.rate_change_frequency_code;
1845 l_krpv_rec.rate_change_value := p_krpv_rec.rate_change_value;
1846 l_krpv_rec.conversion_option_code := p_krpv_rec.conversion_option_code;
1847 l_krpv_rec.next_conversion_date := p_krpv_rec.next_conversion_date;
1848 l_krpv_rec.conversion_type_code := p_krpv_rec.conversion_type_code;
1849
1850 l_krpv_tbl(1) := l_krpv_rec;
1851
1852 /*get_product2(
1853 p_api_version => p_api_version,
1854 p_init_msg_list => p_init_msg_list,
1855 x_return_status => x_return_status,
1856 x_msg_count => x_msg_count,
1857 x_msg_data => x_msg_data,
1858 p_khr_id => l_krpv_rec.khr_id,
1859 x_product_id => l_product_id,
1860 x_pdt_parameter_rec => l_pdt_parameter_rec); */
1861
1862 --Bug# 7440232
1863 validate_k_rate_params(
1864 p_api_version => p_api_version,
1865 p_init_msg_list => p_init_msg_list,
1866 x_return_status => x_return_status,
1867 x_msg_count => x_msg_count,
1868 x_msg_data => x_msg_data,
1869 p_product_id => l_product_id,
1870 p_k_rate_tbl => l_krpv_tbl,
1871 p_validate_flag => 'F');
1872
1873 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1874 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1875 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1876 RAISE OKL_API.G_EXCEPTION_ERROR;
1877 END IF;
1878
1879 -- Bug 4722746
1880 IF (l_pdt_parameter_rec.interest_calculation_basis = 'CATCHUP/CLEANUP'
1881 AND l_pdt_parameter_rec.revenue_recognition_method = 'STREAMS') THEN
1882
1883 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1884 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Catchup :5.0');
1885 END IF;
1886 IF (l_krpv_rec.catchup_frequency_code is NULL OR
1887 l_krpv_rec.catchup_frequency_code = G_MISS_CHAR) AND
1888 (l_krpv_rec.catchup_start_date is NULL OR
1889 l_krpv_rec.catchup_start_date = G_MISS_DATE) AND
1890 (l_krpv_rec.catchup_settlement_code is NULL OR
1891 l_krpv_rec.catchup_settlement_code = G_MISS_CHAR) AND
1892 (l_krpv_rec.catchup_basis_code is NULL OR
1893 l_krpv_rec.catchup_basis_code = G_MISS_CHAR) THEN
1894 -- All four are null
1895 IF (l_krpv_rec.rate_change_start_date is NULL OR
1896 l_krpv_rec.rate_change_start_date = G_MISS_DATE) AND
1897 (l_krpv_rec.rate_change_value is NULL OR
1898 l_krpv_rec.rate_change_value = G_MISS_NUM) AND
1899 (l_krpv_rec.rate_change_frequency_code is NULL OR
1900 l_krpv_rec.rate_change_frequency_code = G_MISS_CHAR) AND
1901 (l_krpv_rec.compounding_frequency_code is NULL OR
1902 l_krpv_rec.compounding_frequency_code = G_MISS_CHAR) AND
1903 (l_krpv_rec.calculation_formula_id is NULL OR
1904 l_krpv_rec.calculation_formula_id = G_MISS_NUM) AND
1905 (l_krpv_rec.rate_delay_code is NULL OR
1906 l_krpv_rec.rate_delay_code = G_MISS_CHAR) AND
1907 (l_krpv_rec.rate_delay_frequency is NULL OR
1908 l_krpv_rec.rate_delay_frequency = G_MISS_NUM) THEN
1909 NULL;
1910 ELSE
1911 OKC_API.set_message(p_app_name => G_APP_NAME,
1912 p_msg_name => G_REQUIRED_VALUE,
1913 p_token1 => G_COL_NAME_TOKEN,
1914 p_token1_value => 'CATCHUP FREQUENCY');
1915 RAISE OKL_API.G_EXCEPTION_ERROR;
1916 END IF;
1917
1918 ELSE
1919 -- One of the four is not null
1920 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1921 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Catchup :5.1');
1922 END IF;
1923 IF (l_krpv_rec.catchup_frequency_code is NOT NULL AND
1924 l_krpv_rec.catchup_frequency_code <> G_MISS_CHAR) THEN
1925 NULL;
1926 ELSE
1927 OKC_API.set_message(p_app_name => G_APP_NAME,
1928 p_msg_name => G_REQUIRED_VALUE,
1929 p_token1 => G_COL_NAME_TOKEN,
1930 p_token1_value => 'Catchup Frequency');
1931 RAISE OKL_API.G_EXCEPTION_ERROR;
1932 END IF;
1933
1934 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1935 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Catchup :6');
1936 END IF;
1937 IF (l_krpv_rec.catchup_start_date is NOT NULL AND
1938 l_krpv_rec.catchup_start_date <> G_MISS_DATE) THEN
1939 NULL;
1940 ELSE
1941 -- Default from contract start date
1942 l_krpv_rec.catchup_start_date := l_contract_start_date;
1943 END IF;
1944
1945 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1946 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Catchup :7');
1947 END IF;
1948 IF (l_krpv_rec.catchup_settlement_code is NOT NULL AND
1949 l_krpv_rec.catchup_settlement_code <> G_MISS_CHAR) THEN
1950 NULL;
1951 ELSE
1952 OKC_API.set_message(p_app_name => G_APP_NAME,
1953 p_msg_name => G_REQUIRED_VALUE,
1954 p_token1 => G_COL_NAME_TOKEN,
1955 p_token1_value => 'Catchup Settlement');
1956 RAISE OKL_API.G_EXCEPTION_ERROR;
1957 END IF;
1958
1959 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1960 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Catchup :8');
1961 END IF;
1962 IF (l_krpv_rec.catchup_basis_code is NOT NULL AND
1963 l_krpv_rec.catchup_basis_code <> G_MISS_CHAR) THEN
1964 NULL;
1965 ELSE
1966 OKC_API.set_message(p_app_name => G_APP_NAME,
1967 p_msg_name => G_REQUIRED_VALUE,
1968 p_token1 => G_COL_NAME_TOKEN,
1969 p_token1_value => 'Catchup Basis');
1970 RAISE OKL_API.G_EXCEPTION_ERROR;
1971 END IF;
1972
1973
1974 END IF;
1975
1976 END IF; -- if STREAMS and CATCHUP/CLEANUP
1977
1978 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1979 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Before calling update_row() ');
1980 END IF;
1981 OKL_KRP_PVT.update_row(
1982 p_api_version,
1983 p_init_msg_list,
1984 x_return_status,
1985 x_msg_count,
1986 x_msg_data,
1987 l_krpv_rec,
1988 x_krpv_rec);
1989
1990 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1991 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After update_row() finished');
1992 END IF;
1993 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1994 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1995 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1996 RAISE OKL_API.G_EXCEPTION_ERROR;
1997 END IF;
1998
1999 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2000 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Before calling cascade_lease_status_update...');
2001 END IF;
2002 okl_contract_status_pub.cascade_lease_status_edit
2003 (p_api_version => p_api_version,
2004 p_init_msg_list => p_init_msg_list,
2005 x_return_status => x_return_status,
2006 x_msg_count => x_msg_count,
2007 x_msg_data => x_msg_data,
2008 p_chr_id => l_krpv_rec.khr_id);
2009
2010 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
2011 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2012 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
2013 raise OKL_API.G_EXCEPTION_ERROR;
2014 End If;
2015 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2016 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After cascade_lease_status_update finished ...');
2017 END IF;
2018
2019 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2020
2021 EXCEPTION
2022 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2023 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2024 p_api_name => l_api_name,
2025 p_pkg_name => g_pkg_name,
2026 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2027 x_msg_count => x_msg_count,
2028 x_msg_data => x_msg_data,
2029 p_api_type => g_api_type);
2030
2031 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2032 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2033 p_api_name => l_api_name,
2034 p_pkg_name => g_pkg_name,
2035 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2036 x_msg_count => x_msg_count,
2037 x_msg_data => x_msg_data,
2038 p_api_type => g_api_type);
2039
2040 WHEN OTHERS THEN
2041 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2042 p_api_name => l_api_name,
2043 p_pkg_name => g_pkg_name,
2044 p_exc_name => 'OTHERS',
2045 x_msg_count => x_msg_count,
2046 x_msg_data => x_msg_data,
2047 p_api_type => g_api_type);
2048 end;
2049
2050 -- First
2051 PROCEDURE update_k_rate_params(
2052 p_api_version IN NUMBER,
2053 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2054 x_return_status OUT NOCOPY VARCHAR2,
2055 x_msg_count OUT NOCOPY NUMBER,
2056 x_msg_data OUT NOCOPY VARCHAR2,
2057 p_krpr_rec IN krpr_rec_type,
2058 x_krpr_rec OUT NOCOPY krpr_rec_type) IS
2059
2060 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_K_RATE_PARAMS';
2061 l_api_version CONSTANT NUMBER := 1.0;
2062 l_krpv_rec krpv_rec_type;
2063 x_krpv_rec krpv_rec_type;
2064 l_count NUMBER;
2065
2066 begin
2067 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2068
2069 x_return_status := OKL_API.START_ACTIVITY(
2070 p_api_name => l_api_name,
2071 p_pkg_name => g_pkg_name,
2072 p_init_msg_list => p_init_msg_list,
2073 l_api_version => l_api_version,
2074 p_api_version => p_api_version,
2075 p_api_type => G_API_TYPE,
2076 x_return_status => x_return_status);
2077
2078 l_krpv_rec.khr_id := p_krpr_rec.khr_id;
2079 l_krpv_rec.parameter_type_code := p_krpr_rec.parameter_type_code;
2080 l_krpv_rec.effective_from_date := p_krpr_rec.effective_from_date;
2081 l_krpv_rec.effective_to_date := p_krpr_rec.effective_to_date;
2082 l_krpv_rec.interest_index_id := p_krpr_rec.interest_index_id;
2083 l_krpv_rec.base_rate := p_krpr_rec.base_rate;
2084 l_krpv_rec.interest_start_date := p_krpr_rec.interest_start_date;
2085 l_krpv_rec.adder_rate := p_krpr_rec.adder_rate;
2086 l_krpv_rec.maximum_rate := p_krpr_rec.maximum_rate;
2087 l_krpv_rec.minimum_rate := p_krpr_rec.minimum_rate;
2088 l_krpv_rec.principal_basis_code := p_krpr_rec.principal_basis_code;
2089 l_krpv_rec.days_in_a_month_code := p_krpr_rec.days_in_a_month_code;
2090 l_krpv_rec.days_in_a_year_code := p_krpr_rec.days_in_a_year_code;
2091 l_krpv_rec.interest_basis_code := p_krpr_rec.interest_basis_code;
2092
2093 OKL_KRP_PVT.update_row(
2094 p_api_version,
2095 p_init_msg_list,
2096 x_return_status,
2097 x_msg_count,
2098 x_msg_data,
2099 l_krpv_rec,
2100 x_krpv_rec);
2101
2102 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2103 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2104 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2105 RAISE OKL_API.G_EXCEPTION_ERROR;
2106 END IF;
2107
2108 x_krpr_rec.khr_id := x_krpv_rec.khr_id;
2109 x_krpr_rec.parameter_type_code := x_krpv_rec.parameter_type_code;
2110 x_krpr_rec.effective_from_date := x_krpv_rec.effective_from_date;
2111 x_krpr_rec.effective_to_date := x_krpv_rec.effective_to_date;
2112 x_krpr_rec.interest_index_id := x_krpv_rec.interest_index_id;
2113 x_krpr_rec.base_rate := x_krpv_rec.base_rate;
2114 x_krpr_rec.interest_start_date := x_krpv_rec.interest_start_date;
2115 x_krpr_rec.adder_rate := x_krpv_rec.adder_rate;
2116 x_krpr_rec.maximum_rate := x_krpv_rec.maximum_rate;
2117 x_krpr_rec.minimum_rate := x_krpv_rec.minimum_rate;
2118 x_krpr_rec.principal_basis_code := x_krpv_rec.principal_basis_code;
2119 x_krpr_rec.days_in_a_month_code := x_krpv_rec.days_in_a_month_code;
2120 x_krpr_rec.days_in_a_year_code := x_krpv_rec.days_in_a_year_code;
2121 x_krpr_rec.interest_basis_code := x_krpv_rec.interest_basis_code;
2122
2123 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2124
2125 EXCEPTION
2126 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2127 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2128 p_api_name => l_api_name,
2129 p_pkg_name => g_pkg_name,
2130 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2131 x_msg_count => x_msg_count,
2132 x_msg_data => x_msg_data,
2133 p_api_type => g_api_type);
2134
2135 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2136 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2137 p_api_name => l_api_name,
2138 p_pkg_name => g_pkg_name,
2139 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2140 x_msg_count => x_msg_count,
2141 x_msg_data => x_msg_data,
2142 p_api_type => g_api_type);
2143
2144 WHEN OTHERS THEN
2145 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2146 p_api_name => l_api_name,
2147 p_pkg_name => g_pkg_name,
2148 p_exc_name => 'OTHERS',
2149 x_msg_count => x_msg_count,
2150 x_msg_data => x_msg_data,
2151 p_api_type => g_api_type);
2152 end;
2153
2154 -- Second
2155 PROCEDURE update_k_rate_params(
2156 p_api_version IN NUMBER,
2157 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2158 x_return_status OUT NOCOPY VARCHAR2,
2159 x_msg_count OUT NOCOPY NUMBER,
2160 x_msg_data OUT NOCOPY VARCHAR2,
2161 p_krpar_rec IN krpar_rec_type,
2162 x_krpar_rec OUT NOCOPY krpar_rec_type) IS
2163
2164 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_K_RATE_PARAMS';
2165 l_api_version CONSTANT NUMBER := 1.0;
2166 l_krpv_rec krpv_rec_type;
2167 x_krpv_rec krpv_rec_type;
2168 l_count NUMBER;
2169
2170 begin
2171 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2172
2173 x_return_status := OKL_API.START_ACTIVITY(
2174 p_api_name => l_api_name,
2175 p_pkg_name => g_pkg_name,
2176 p_init_msg_list => p_init_msg_list,
2177 l_api_version => l_api_version,
2178 p_api_version => p_api_version,
2179 p_api_type => G_API_TYPE,
2180 x_return_status => x_return_status);
2181
2182 l_krpv_rec.khr_id := p_krpar_rec.khr_id;
2183 l_krpv_rec.parameter_type_code := p_krpar_rec.parameter_type_code;
2184 l_krpv_rec.effective_from_date := p_krpar_rec.effective_from_date;
2185 l_krpv_rec.effective_to_date := p_krpar_rec.effective_to_date;
2186 l_krpv_rec.rate_delay_code := p_krpar_rec.rate_delay_code;
2187 l_krpv_rec.rate_delay_frequency := p_krpar_rec.rate_delay_frequency;
2188 l_krpv_rec.compounding_frequency_code := p_krpar_rec.compounding_frequency_code;
2189 l_krpv_rec.calculation_formula_id := p_krpar_rec.calculation_formula_id;
2190 l_krpv_rec.catchup_basis_code := p_krpar_rec.catchup_basis_code;
2191 l_krpv_rec.catchup_start_date := p_krpar_rec.catchup_start_date;
2192 l_krpv_rec.catchup_settlement_code := p_krpar_rec.catchup_settlement_code;
2193 l_krpv_rec.rate_change_start_date := p_krpar_rec.rate_change_start_date;
2194 l_krpv_rec.rate_change_frequency_code := p_krpar_rec.rate_change_frequency_code;
2195 l_krpv_rec.rate_change_value := p_krpar_rec.rate_change_value;
2196
2197 OKL_KRP_PVT.update_row(
2198 p_api_version,
2199 p_init_msg_list,
2200 x_return_status,
2201 x_msg_count,
2202 x_msg_data,
2203 l_krpv_rec,
2204 x_krpv_rec);
2205
2206 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2207 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2208 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2209 RAISE OKL_API.G_EXCEPTION_ERROR;
2210 END IF;
2211
2212 x_krpar_rec.khr_id := x_krpv_rec.khr_id;
2213 x_krpar_rec.parameter_type_code := x_krpv_rec.parameter_type_code;
2214 x_krpar_rec.effective_from_date := x_krpv_rec.effective_from_date;
2215 x_krpar_rec.effective_to_date := x_krpv_rec.effective_to_date;
2216 x_krpar_rec.rate_delay_code := x_krpv_rec.rate_delay_code;
2217 x_krpar_rec.rate_delay_frequency := x_krpv_rec.rate_delay_frequency;
2218 x_krpar_rec.compounding_frequency_code := x_krpv_rec.compounding_frequency_code;
2219 x_krpar_rec.calculation_formula_id := x_krpv_rec.calculation_formula_id;
2220 x_krpar_rec.catchup_basis_code := x_krpv_rec.catchup_basis_code;
2221 x_krpar_rec.catchup_start_date := x_krpv_rec.catchup_start_date;
2222 x_krpar_rec.catchup_settlement_code := x_krpv_rec.catchup_settlement_code;
2223 x_krpar_rec.rate_change_start_date := x_krpv_rec.rate_change_start_date;
2224 x_krpar_rec.rate_change_frequency_code := x_krpv_rec.rate_change_frequency_code;
2225 x_krpar_rec.rate_change_value := x_krpv_rec.rate_change_value;
2226
2227 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2228
2229 EXCEPTION
2230 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2231 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2232 p_api_name => l_api_name,
2233 p_pkg_name => g_pkg_name,
2234 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2235 x_msg_count => x_msg_count,
2236 x_msg_data => x_msg_data,
2237 p_api_type => g_api_type);
2238
2239 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2240 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2241 p_api_name => l_api_name,
2242 p_pkg_name => g_pkg_name,
2243 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2244 x_msg_count => x_msg_count,
2245 x_msg_data => x_msg_data,
2246 p_api_type => g_api_type);
2247
2248 WHEN OTHERS THEN
2249 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2250 p_api_name => l_api_name,
2251 p_pkg_name => g_pkg_name,
2252 p_exc_name => 'OTHERS',
2253 x_msg_count => x_msg_count,
2254 x_msg_data => x_msg_data,
2255 p_api_type => g_api_type);
2256 end;
2257
2258 -- Third
2259 PROCEDURE update_k_rate_params(
2260 p_api_version IN NUMBER,
2261 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2262 x_return_status OUT NOCOPY VARCHAR2,
2263 x_msg_count OUT NOCOPY NUMBER,
2264 x_msg_data OUT NOCOPY VARCHAR2,
2265 p_krpc_rec IN krpc_rec_type,
2266 x_krpc_rec OUT NOCOPY krpc_rec_type) IS
2267
2268 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_K_RATE_PARAMS';
2269 l_api_version CONSTANT NUMBER := 1.0;
2270 l_krpv_rec krpv_rec_type;
2271 x_krpv_rec krpv_rec_type;
2272 l_count NUMBER;
2273
2274 begin
2275 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2276
2277 x_return_status := OKL_API.START_ACTIVITY(
2278 p_api_name => l_api_name,
2279 p_pkg_name => g_pkg_name,
2280 p_init_msg_list => p_init_msg_list,
2281 l_api_version => l_api_version,
2282 p_api_version => p_api_version,
2283 p_api_type => G_API_TYPE,
2284 x_return_status => x_return_status);
2285
2286 l_krpv_rec.khr_id := p_krpc_rec.khr_id;
2287 l_krpv_rec.parameter_type_code := p_krpc_rec.parameter_type_code;
2288 l_krpv_rec.effective_from_date := p_krpc_rec.effective_from_date;
2289 l_krpv_rec.effective_to_date := p_krpc_rec.effective_to_date;
2290 l_krpv_rec.conversion_option_code := p_krpc_rec.conversion_option_code;
2291 l_krpv_rec.next_conversion_date := p_krpc_rec.next_conversion_date;
2292 l_krpv_rec.conversion_type_code := p_krpc_rec.conversion_type_code;
2293
2294 OKL_KRP_PVT.update_row(
2295 p_api_version,
2296 p_init_msg_list,
2297 x_return_status,
2298 x_msg_count,
2299 x_msg_data,
2300 l_krpv_rec,
2301 x_krpv_rec);
2302
2303 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2304 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2305 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2306 RAISE OKL_API.G_EXCEPTION_ERROR;
2307 END IF;
2308
2309 x_krpc_rec.khr_id := x_krpv_rec.khr_id;
2310 x_krpc_rec.parameter_type_code := x_krpv_rec.parameter_type_code;
2311 x_krpc_rec.effective_from_date := x_krpv_rec.effective_from_date;
2312 x_krpc_rec.effective_to_date := x_krpv_rec.effective_to_date;
2313 x_krpc_rec.conversion_option_code := x_krpv_rec.conversion_option_code;
2314 x_krpc_rec.next_conversion_date := x_krpv_rec.next_conversion_date;
2315 x_krpc_rec.conversion_type_code := x_krpv_rec.conversion_type_code;
2316
2317 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2318
2319 EXCEPTION
2320 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2321 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2322 p_api_name => l_api_name,
2323 p_pkg_name => g_pkg_name,
2324 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2325 x_msg_count => x_msg_count,
2326 x_msg_data => x_msg_data,
2327 p_api_type => g_api_type);
2328
2329 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2330 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2331 p_api_name => l_api_name,
2332 p_pkg_name => g_pkg_name,
2333 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2334 x_msg_count => x_msg_count,
2335 x_msg_data => x_msg_data,
2336 p_api_type => g_api_type);
2337
2338 WHEN OTHERS THEN
2339 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2340 p_api_name => l_api_name,
2341 p_pkg_name => g_pkg_name,
2342 p_exc_name => 'OTHERS',
2343 x_msg_count => x_msg_count,
2344 x_msg_data => x_msg_data,
2345 p_api_type => g_api_type);
2346 end;
2347
2348 PROCEDURE delete_k_rate_params(
2349 p_api_version IN NUMBER,
2350 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2351 x_return_status OUT NOCOPY VARCHAR2,
2352 x_msg_count OUT NOCOPY NUMBER,
2353 x_msg_data OUT NOCOPY VARCHAR2,
2354 p_krpdel_tbl IN krpdel_tbl_type) IS
2355
2356 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_K_RATE_PARAMS';
2357 l_api_version CONSTANT NUMBER := 1.0;
2358 l_krpv_rec krpv_rec_type;
2359 l_krpv_rec2 krpv_rec_type;
2360 l_krpv_rec3 krpv_rec_type;
2361
2362 CURSOR get_effective_to_date_csr(
2363 p_khr_id NUMBER,
2364 p_parameter_type_code VARCHAR2,
2365 p_effective_from_date DATE) IS
2366 select rate.effective_to_date,
2367 contract.sts_code,
2368 contract.orig_system_source_code,
2369 contract.orig_system_id1
2370 FROM OKL_K_RATE_PARAMS rate,
2371 OKC_K_HEADERS_B contract
2372 WHERE rate.khr_id = p_khr_id
2373 AND rate.parameter_type_code = p_parameter_type_code
2374 AND rate.effective_from_date = p_effective_from_date
2375 AND rate.khr_id = contract.id;
2376
2377 CURSOR get_orig_effective_from_dt_csr(
2378 p_khr_id NUMBER,
2379 p_parameter_type_code VARCHAR2) IS
2380 select rate.effective_from_date
2381 FROM OKL_K_RATE_PARAMS rate
2382 WHERE rate.khr_id = p_khr_id
2383 AND rate.parameter_type_code = p_parameter_type_code
2384 AND rate.effective_to_date IS NULL;
2385
2386 l_all_rate_params_null BOOLEAN;
2387 l_parameter_type_code VARCHAR2(30);
2388 l_effective_to_date DATE;
2389 l_sts_code OKC_K_HEADERS_B.STS_CODE%TYPE;
2390 l_orig_system_source_code OKC_K_HEADERS_B.ORIG_SYSTEM_SOURCE_CODE%TYPE;
2391 l_orig_system_id1 OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE;
2392 l_orig_effective_from_date DATE;
2393 l_del_count NUMBER := 0; -- Bug 4874280
2394 l_khr_id NUMBER;
2395 begin
2396 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2397
2398 x_return_status := OKL_API.START_ACTIVITY(
2399 p_api_name => l_api_name,
2400 p_pkg_name => g_pkg_name,
2401 p_init_msg_list => p_init_msg_list,
2402 l_api_version => l_api_version,
2403 p_api_version => p_api_version,
2404 p_api_type => G_API_TYPE,
2405 x_return_status => x_return_status);
2406
2407 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2408 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'In delete_k_rate_params...');
2409 END IF;
2410 IF (p_krpdel_tbl.COUNT > 0) THEN
2411 FOR i in p_krpdel_tbl.FIRST..p_krpdel_tbl.LAST
2412 LOOP
2413 -- AKP: todo (change actual lookup values)
2414 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2415 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Delete:rate_type=' || p_krpdel_tbl(i).rate_type);
2416 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Delete:khr_id=' || p_krpdel_tbl(i).khr_id);
2417 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Delete:effective_from_date=' || p_krpdel_tbl(i).effective_from_date);
2418 END IF;
2419 l_khr_id := p_krpdel_tbl(i).khr_id;
2420 --Bug# 7440232
2421 IF (p_krpdel_tbl(i).rate_type IN ('INTEREST_RATE_PARAMS', 'CONVERSION_BASIS')) THEN
2422 l_parameter_type_code := 'ACTUAL';
2423 --Bug# 7440232
2424 ELSIF (p_krpdel_tbl(i).rate_type IN ('INTEREST_RATE_PARAMS_CONV')) THEN
2425 l_parameter_type_code := 'CONVERSION';
2426 ELSE
2427 OKC_API.set_message(p_app_name => G_APP_NAME,
2428 p_msg_name => 'OKL_LLA_VAR_RATE_INV_PARAM');
2429 RAISE OKL_API.G_EXCEPTION_ERROR;
2430 END IF;
2431
2432 FOR r IN get_effective_to_date_csr(
2433 p_krpdel_tbl(i).khr_id,
2434 l_parameter_type_code,
2435 p_krpdel_tbl(i).effective_from_date)
2436 LOOP
2437 l_effective_to_date := r.effective_to_date;
2438 l_sts_code := r.sts_code;
2439 l_orig_system_source_code := r.orig_system_source_code;
2440 l_orig_system_id1 := r.orig_system_id1;
2441 END LOOP;
2442
2443 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2444 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_orig_system_source_code=' || l_orig_system_source_code);
2445 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_orig_system_id1=' || l_orig_system_id1);
2446 END IF;
2447 IF (l_effective_to_date IS NOT NULL) THEN
2448 OKC_API.set_message(p_app_name => G_APP_NAME,
2449 p_msg_name => 'OKL_LLA_VAR_RATE_DELETE_ERR');
2450 RAISE OKL_API.G_EXCEPTION_ERROR;
2451 END IF;
2452
2453 --Bug# 7440232
2454 IF (l_sts_code = 'BOOKED') THEN
2455 OKC_API.set_message(p_app_name => G_APP_NAME,
2456 p_msg_name => 'OKL_LLA_VAR_RATE_DELETE_ERR1');
2457 RAISE OKL_API.G_EXCEPTION_ERROR;
2458 END IF;
2459
2460 IF (l_orig_system_source_code = 'OKL_REBOOK') THEN
2461
2462 FOR r IN get_orig_effective_from_dt_csr(
2463 --p_krpdel_tbl(i).khr_id,
2464 l_orig_system_id1,
2465 l_parameter_type_code)
2466 LOOP
2467 l_orig_effective_from_date := r.effective_from_date;
2468 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2469 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_orig_effective_from_date=' || l_orig_effective_from_date);
2470 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'krpdel.effective_from_date=' || p_krpdel_tbl(i).effective_from_date);
2471 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'krpdel.khr_id=' || p_krpdel_tbl(i).khr_id);
2472 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_parameter_type_code=' || l_parameter_type_code);
2473 END IF;
2474 END LOOP;
2475
2476 --Bug# 7440232
2477 IF (l_orig_effective_from_date =
2478 p_krpdel_tbl(i).effective_from_date) THEN
2479 OKC_API.set_message(p_app_name => G_APP_NAME,
2480 p_msg_name => 'OKL_LLA_VAR_RATE_DELETE_ERR2');
2481 RAISE OKL_API.G_EXCEPTION_ERROR;
2482 END IF;
2483
2484 /*UPDATE OKL_K_RATE_PARAMS
2485 SET EFFECTIVE_TO_DATE = NULL
2486 WHERE KHR_ID = p_krpdel_tbl(i).khr_id
2487 AND PARAMETER_TYPE_CODE = l_parameter_type_code
2488 AND EFFECTIVE_TO_DATE = p_krpdel_tbl(i).effective_from_date - 1;*/
2489 END IF;
2490
2491 SELECT KHR_ID, PARAMETER_TYPE_CODE, EFFECTIVE_FROM_DATE,
2492 EFFECTIVE_TO_DATE, INTEREST_INDEX_ID, BASE_RATE,
2493 INTEREST_START_DATE, ADDER_RATE, MAXIMUM_RATE,
2494 MINIMUM_RATE, PRINCIPAL_BASIS_CODE, DAYS_IN_A_MONTH_CODE,
2495 DAYS_IN_A_YEAR_CODE, INTEREST_BASIS_CODE, RATE_DELAY_CODE,
2496 RATE_DELAY_FREQUENCY, COMPOUNDING_FREQUENCY_CODE, CALCULATION_FORMULA_ID,
2497 CATCHUP_BASIS_CODE, CATCHUP_START_DATE, CATCHUP_SETTLEMENT_CODE,
2498 RATE_CHANGE_START_DATE, RATE_CHANGE_FREQUENCY_CODE, RATE_CHANGE_VALUE,
2499 CONVERSION_OPTION_CODE, NEXT_CONVERSION_DATE, CONVERSION_TYPE_CODE,
2500 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
2501 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
2502 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
2503 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
2504 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
2505 ATTRIBUTE15, created_by, creation_date,
2506 last_updated_by, last_update_date, last_update_login,
2507 CATCHUP_FREQUENCY_CODE
2508 INTO
2509 l_krpv_rec.KHR_ID, l_krpv_rec.parameter_type_code, l_krpv_rec.effective_from_date,
2510 l_krpv_rec.EFFECTIVE_TO_DATE, l_krpv_rec.INTEREST_INDEX_ID, l_krpv_rec.BASE_RATE,
2511 l_krpv_rec.INTEREST_START_DATE, l_krpv_rec.ADDER_RATE, l_krpv_rec.MAXIMUM_RATE,
2512 l_krpv_rec.MINIMUM_RATE, l_krpv_rec.PRINCIPAL_BASIS_CODE, l_krpv_rec.DAYS_IN_A_MONTH_CODE,
2513 l_krpv_rec.DAYS_IN_A_YEAR_CODE, l_krpv_rec.INTEREST_BASIS_CODE, l_krpv_rec.RATE_DELAY_CODE,
2514 l_krpv_rec.RATE_DELAY_FREQUENCY, l_krpv_rec.COMPOUNDING_FREQUENCY_CODE, l_krpv_rec.CALCULATION_FORMULA_ID,
2515 l_krpv_rec.CATCHUP_BASIS_CODE, l_krpv_rec.CATCHUP_START_DATE, l_krpv_rec.CATCHUP_SETTLEMENT_CODE,
2516 l_krpv_rec.RATE_CHANGE_START_DATE, l_krpv_rec.RATE_CHANGE_FREQUENCY_CODE, l_krpv_rec.RATE_CHANGE_VALUE,
2517 l_krpv_rec.CONVERSION_OPTION_CODE, l_krpv_rec.NEXT_CONVERSION_DATE, l_krpv_rec.CONVERSION_TYPE_CODE,
2518 l_krpv_rec.ATTRIBUTE_CATEGORY, l_krpv_rec.ATTRIBUTE1, l_krpv_rec.ATTRIBUTE2,
2519 l_krpv_rec.ATTRIBUTE3, l_krpv_rec.ATTRIBUTE4, l_krpv_rec.ATTRIBUTE5,
2520 l_krpv_rec.ATTRIBUTE6, l_krpv_rec.ATTRIBUTE7, l_krpv_rec.ATTRIBUTE8,
2521 l_krpv_rec.ATTRIBUTE9, l_krpv_rec.ATTRIBUTE10, l_krpv_rec.ATTRIBUTE11,
2522 l_krpv_rec.ATTRIBUTE12, l_krpv_rec.ATTRIBUTE13, l_krpv_rec.ATTRIBUTE14,
2523 l_krpv_rec.ATTRIBUTE15, l_krpv_rec.CREATED_BY, l_krpv_rec.CREATION_DATE,
2524 l_krpv_rec.LAST_UPDATED_BY, l_krpv_rec.LAST_UPDATE_DATE, l_krpv_rec.LAST_UPDATE_LOGIN,
2525 l_krpv_rec.CATCHUP_FREQUENCY_CODE
2526 FROM OKL_K_RATE_PARAMS
2527 WHERE KHR_ID = p_krpdel_tbl(i).khr_id
2528 AND parameter_type_code = l_parameter_type_code
2529 AND effective_from_date = p_krpdel_tbl(i).effective_from_date;
2530
2531 --Bug# 7440232
2532 IF (p_krpdel_tbl(i).rate_type IN ('INTEREST_RATE_PARAMS', 'INTEREST_RATE_PARAMS_CONV')) THEN
2533 l_krpv_rec.interest_index_id := null;
2534 l_krpv_rec.base_rate := null;
2535 l_krpv_rec.adder_rate := null;
2536 l_krpv_rec.minimum_rate := null;
2537 l_krpv_rec.maximum_rate := null;
2538 l_krpv_rec.principal_basis_code := null;
2539 l_krpv_rec.interest_basis_code := null;
2540 l_krpv_rec.interest_start_date := null;
2541 l_krpv_rec.days_in_a_month_code := null;
2542 l_krpv_rec.days_in_a_year_code := null;
2543
2544 l_krpv_rec.rate_delay_code := null;
2545 l_krpv_rec.rate_delay_frequency := null;
2546 l_krpv_rec.compounding_frequency_code := null;
2547 l_krpv_rec.calculation_formula_id := null;
2548 l_krpv_rec.catchup_frequency_code := null;
2549 l_krpv_rec.catchup_start_date := null;
2550 l_krpv_rec.catchup_settlement_code := null;
2551 l_krpv_rec.catchup_basis_code := null;
2552 l_krpv_rec.rate_change_frequency_code := null;
2553 l_krpv_rec.rate_change_start_date := null;
2554 l_krpv_rec.rate_change_value := null;
2555
2556 UPDATE OKL_K_RATE_PARAMS
2557 SET interest_index_id = null,
2558 base_rate = null,
2559 adder_rate = null,
2560 minimum_rate = null,
2561 maximum_rate = null,
2562 principal_basis_code = null,
2563 interest_basis_code = null,
2564 interest_start_date = null,
2565 days_in_a_month_code = null,
2566 days_in_a_year_code = null,
2567 rate_delay_code = null,
2568 rate_delay_frequency = null,
2569 compounding_frequency_code = null,
2570 calculation_formula_id = null,
2571 catchup_frequency_code = null,
2572 catchup_start_date = null,
2573 catchup_settlement_code = null,
2574 catchup_basis_code = null,
2575 rate_change_frequency_code = null,
2576 rate_change_start_date = null,
2577 rate_change_value = null
2578 WHERE KHR_ID = p_krpdel_tbl(i).khr_id
2579 AND parameter_type_code = l_parameter_type_code
2580 AND effective_from_date = p_krpdel_tbl(i).effective_from_date;
2581 l_del_count := l_del_count + sql%rowcount;
2582
2583 ELSIF (p_krpdel_tbl(i).rate_type = 'CONVERSION_BASIS') THEN
2584 l_krpv_rec.conversion_option_code := null;
2585 l_krpv_rec.next_conversion_date := null;
2586 l_krpv_rec.conversion_type_code := null;
2587
2588 UPDATE OKL_K_RATE_PARAMS
2589 SET conversion_option_code = null,
2590 next_conversion_date = null,
2591 conversion_type_code = null
2592 WHERE KHR_ID = p_krpdel_tbl(i).khr_id
2593 AND parameter_type_code = l_parameter_type_code
2594 AND effective_from_date = p_krpdel_tbl(i).effective_from_date;
2595 l_del_count := l_del_count + sql%rowcount;
2596 END IF;
2597
2598 -- Check if all null
2599 l_all_rate_params_null := FALSE;
2600 IF (l_krpv_rec.interest_index_id IS NULL AND
2601 l_krpv_rec.base_rate IS NULL AND
2602 l_krpv_rec.adder_rate IS NULL AND
2603 l_krpv_rec.minimum_rate IS NULL AND
2604 l_krpv_rec.maximum_rate IS NULL AND
2605 l_krpv_rec.principal_basis_code IS NULL AND
2606 l_krpv_rec.interest_basis_code IS NULL AND
2607 l_krpv_rec.interest_start_date IS NULL AND
2608 l_krpv_rec.days_in_a_month_code IS NULL AND
2609 l_krpv_rec.days_in_a_year_code IS NULL AND
2610 l_krpv_rec.rate_delay_code IS NULL AND
2611 l_krpv_rec.rate_delay_frequency IS NULL AND
2612 l_krpv_rec.compounding_frequency_code IS NULL AND
2613 l_krpv_rec.calculation_formula_id IS NULL AND
2614 l_krpv_rec.catchup_frequency_code IS NULL AND
2615 l_krpv_rec.catchup_start_date IS NULL AND
2616 l_krpv_rec.catchup_settlement_code IS NULL AND
2617 l_krpv_rec.catchup_basis_code IS NULL AND
2618 l_krpv_rec.rate_change_frequency_code IS NULL AND
2619 l_krpv_rec.rate_change_start_date IS NULL AND
2620 l_krpv_rec.rate_change_value IS NULL AND
2621 l_krpv_rec.conversion_option_code IS NULL AND
2622 l_krpv_rec.next_conversion_date IS NULL AND
2623 l_krpv_rec.conversion_type_code IS NULL) THEN
2624
2625 l_all_rate_params_null := TRUE;
2626
2627 END IF;
2628
2629 IF (l_all_rate_params_null) THEN
2630 l_krpv_rec2.khr_id := p_krpdel_tbl(i).khr_id;
2631 l_krpv_rec2.parameter_type_code := l_parameter_type_code;
2632 l_krpv_rec2.effective_from_date := p_krpdel_tbl(i).effective_from_date;
2633
2634 OKL_KRP_PVT.delete_row(
2635 p_api_version,
2636 p_init_msg_list,
2637 x_return_status,
2638 x_msg_count,
2639 x_msg_data,
2640 l_krpv_rec2);
2641
2642 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2643 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2644 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2645 RAISE OKL_API.G_EXCEPTION_ERROR;
2646 END IF;
2647
2648 IF (l_orig_system_source_code = 'OKL_REBOOK') THEN
2649
2650 /*FOR r IN get_orig_effective_from_dt_csr(
2651 p_krpdel_tbl(i).khr_id,
2652 l_parameter_type_code)
2653 LOOP
2654 l_orig_effective_from_date := r.effective_from_date;
2655 print('l_orig_effective_from_date=' || l_orig_effective_from_date);
2656 print('krpdel.effective_from_date=' || p_krpdel_tbl(i).effective_from_date);
2657 print('krpdel.khr_id=' || p_krpdel_tbl(i).khr_id);
2658 print('l_parameter_type_code=' || l_parameter_type_code);
2659 END LOOP;
2660
2661 IF (l_orig_effective_from_date =
2662 p_krpdel_tbl(i).effective_from_date) THEN
2663 OKC_API.set_message(p_app_name => G_APP_NAME,
2664 p_msg_name => 'OKL_LA_VAR_RATE_DELETE_ERR2');
2665 RAISE OKL_API.G_EXCEPTION_ERROR;
2666 END IF;*/
2667
2668 UPDATE OKL_K_RATE_PARAMS
2669 SET EFFECTIVE_TO_DATE = NULL
2670 WHERE KHR_ID = p_krpdel_tbl(i).khr_id
2671 AND PARAMETER_TYPE_CODE = l_parameter_type_code
2672 AND EFFECTIVE_TO_DATE = p_krpdel_tbl(i).effective_from_date - 1;
2673 l_del_count := l_del_count + sql%rowcount;
2674 END IF;
2675
2676 END IF;
2677 l_krpv_rec := l_krpv_rec3;
2678 l_krpv_rec2 := l_krpv_rec3;
2679
2680 END LOOP;
2681 END IF;
2682
2683 -- Bug 4874280
2684 IF (l_del_count > 0) THEN
2685 okl_contract_status_pub.cascade_lease_status_edit
2686 (p_api_version => p_api_version,
2687 p_init_msg_list => p_init_msg_list,
2688 x_return_status => x_return_status,
2689 x_msg_count => x_msg_count,
2690 x_msg_data => x_msg_data,
2691 p_chr_id => l_khr_id);
2692
2693 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
2694 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2695 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
2696 raise OKL_API.G_EXCEPTION_ERROR;
2697 End If;
2698 END IF;
2699
2700 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2701
2702 EXCEPTION
2703 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2704 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2705 p_api_name => l_api_name,
2706 p_pkg_name => g_pkg_name,
2707 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2708 x_msg_count => x_msg_count,
2709 x_msg_data => x_msg_data,
2710 p_api_type => g_api_type);
2711
2712 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2713 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2714 p_api_name => l_api_name,
2715 p_pkg_name => g_pkg_name,
2716 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2717 x_msg_count => x_msg_count,
2718 x_msg_data => x_msg_data,
2719 p_api_type => g_api_type);
2720
2721 WHEN OTHERS THEN
2722 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2723 p_api_name => l_api_name,
2724 p_pkg_name => g_pkg_name,
2725 p_exc_name => 'OTHERS',
2726 x_msg_count => x_msg_count,
2727 x_msg_data => x_msg_data,
2728 p_api_type => g_api_type);
2729 end;
2730
2731 /* For QA checker to call - stack Error messages and no raise exception*/
2732 PROCEDURE validate_k_rate_params(
2733 p_api_version IN NUMBER,
2734 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2735 x_return_status OUT NOCOPY VARCHAR2,
2736 x_msg_count OUT NOCOPY NUMBER,
2737 x_msg_data OUT NOCOPY VARCHAR2,
2738 p_khr_id IN okc_k_headers_b.id%type,
2739 p_validate_flag IN VARCHAR2 DEFAULT 'Y') IS
2740
2741 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_K_RATE_PARAMS';
2742 l_api_version CONSTANT NUMBER := 1.0;
2743
2744 p_pdtv_rec OKL_SETUPPRODUCTS_PUB.pdtv_rec_type;
2745 x_pdt_parameter_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
2746 x_no_data_found BOOLEAN;
2747 l_k_rate_tbl krpv_tbl_type;
2748 l_k_rate_tbl2 krpv_tbl_type;
2749 l_rate_counter NUMBER := 1;
2750
2751 CURSOR l_hdr_csr( chrId NUMBER ) IS
2752 SELECT
2753 CHR.authoring_org_id,
2754 CHR.inv_organization_id,
2755 khr.deal_type,
2756 pdt.id pid,
2757 NVL(pdt.reporting_pdt_id, -1) report_pdt_id
2758 FROM okc_k_headers_b CHR,
2759 okl_k_headers khr,
2760 okl_products_v pdt
2761 WHERE khr.id = CHR.id
2762 AND CHR.id = chrId
2763 AND khr.pdt_id = pdt.id(+);
2764
2765 l_hdr_rec l_hdr_csr%ROWTYPE;
2766
2767 CURSOR csr_get_rate_tbl(p_id NUMBER) IS
2768 SELECT
2769 khr_id
2770 ,parameter_type_code
2771 ,effective_from_date
2772 ,effective_to_date
2773 ,interest_index_id
2774 ,base_rate
2775 ,interest_start_date
2776 ,adder_rate
2777 ,maximum_rate
2778 ,minimum_rate
2779 ,principal_basis_code
2780 ,days_in_a_month_code
2781 ,days_in_a_year_code
2782 ,interest_basis_code
2783 ,rate_delay_code
2784 ,rate_delay_frequency
2785 ,compounding_frequency_code
2786 ,calculation_formula_id
2787 ,catchup_basis_code
2788 ,catchup_start_date
2789 ,catchup_settlement_code
2790 ,rate_change_start_date
2791 ,rate_change_frequency_code
2792 ,rate_change_value
2793 ,conversion_option_code
2794 ,next_conversion_date
2795 ,conversion_type_code
2796 ,attribute_category
2797 ,attribute1
2798 ,attribute2
2799 ,attribute3
2800 ,attribute4
2801 ,attribute5
2802 ,attribute6
2803 ,attribute7
2804 ,attribute8
2805 ,attribute9
2806 ,attribute10
2807 ,attribute11
2808 ,attribute12
2809 ,attribute13
2810 ,attribute14
2811 ,attribute15
2812 ,created_by
2813 ,creation_date
2814 ,last_updated_by
2815 ,last_update_date
2816 ,last_update_login
2817 ,catchup_frequency_code
2818 FROM okl_k_rate_params
2819 WHERE khr_id = p_id;
2820
2821 l_deal_type VARCHAR2(30);
2822 l_interest_calculation_basis VARCHAR2(30);
2823 l_revenue_recognition_method VARCHAR2(30);
2824 begin
2825 x_return_status := OKL_API.START_ACTIVITY(
2826 p_api_name => l_api_name,
2827 p_pkg_name => g_pkg_name,
2828 p_init_msg_list => p_init_msg_list,
2829 l_api_version => l_api_version,
2830 p_api_version => p_api_version,
2831 p_api_type => G_API_TYPE,
2832 x_return_status => x_return_status);
2833
2834 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2835 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'In validate_k_rate_params QA checker' || to_char(sysdate,'HH24:MI:SS') || ' with p_khr_id= ' || p_khr_id || ' ...');
2836 END IF;
2837 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2838 OPEN l_hdr_csr(p_khr_id);
2839 FETCH l_hdr_csr INTO l_hdr_rec;
2840 IF l_hdr_csr%NOTFOUND THEN
2841 CLOSE l_hdr_csr;
2842 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2843 END IF;
2844 CLOSE l_hdr_csr;
2845
2846 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2847 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'deal_type=' || l_hdr_rec.deal_type);
2848 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'pid=' || l_hdr_rec.pid);
2849 END IF;
2850
2851 p_pdtv_rec.id := l_hdr_rec.pid;
2852 OKL_SETUPPRODUCTS_PUB.Getpdt_parameters(
2853 p_api_version => p_api_version,
2854 p_init_msg_list => p_init_msg_list,
2855 x_return_status => x_return_status,
2856 x_msg_count => x_msg_count,
2857 x_msg_data => x_msg_data,
2858 p_pdtv_rec => p_pdtv_rec,
2859 x_no_data_found => x_no_data_found,
2860 p_pdt_parameter_rec => x_pdt_parameter_rec);
2861
2862 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2863 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2864 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2865 RAISE OKL_API.G_EXCEPTION_ERROR;
2866 ELSIF NVL(x_pdt_parameter_rec.Name,OKL_API.G_MISS_CHAR) = OKL_API.G_MISS_CHAR THEN
2867 x_return_status := OKL_API.G_RET_STS_ERROR;
2868 RAISE OKL_API.G_EXCEPTION_ERROR;
2869 END IF;
2870
2871 --print('Product_subclass=' || x_pdt_parameter_rec.product_subclass);
2872 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2873 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Deal_type=' || x_pdt_parameter_rec.Deal_type);
2874 END IF;
2875 --print('Tax_owner=' || x_pdt_parameter_rec.Tax_owner);
2876 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2877 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Revenue_Recognition_Method=' || x_pdt_parameter_rec.Revenue_Recognition_Method);
2878 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Interest_Calculation_Basis=' || x_pdt_parameter_rec.Interest_Calculation_Basis);
2879 END IF;
2880 l_deal_type := x_pdt_parameter_rec.deal_type;
2881 l_interest_calculation_basis := x_pdt_parameter_rec.interest_calculation_basis;
2882 l_revenue_recognition_method := x_pdt_parameter_rec.revenue_recognition_method;
2883
2884 -- AKP: Todo: Get l_k_rate_tbl from database
2885 Open csr_get_rate_tbl(p_khr_id);
2886 LOOP
2887 Fetch csr_get_rate_tbl BULK COLLECT INTO l_k_rate_tbl2 LIMIT G_BULK_SIZE;
2888 EXIT WHEN l_k_rate_tbl2.COUNT = 0;
2889 FOR i IN l_k_rate_tbl2.FIRST..l_k_rate_tbl2.LAST
2890 LOOP
2891 l_k_rate_tbl(l_rate_counter) := l_k_rate_tbl2(i);
2892 l_rate_counter := l_rate_counter + 1;
2893 END LOOP;
2894 END LOOP;
2895 CLOSE csr_get_rate_tbl;
2896
2897 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2898 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After csr_get_rate_tbl...');
2899 END IF;
2900 /* IF (((l_deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST')) AND
2901 (l_interest_calculation_basis IN ('FIXED', 'REAMORT'))) OR
2902 ((l_deal_type = 'LOAN') AND
2903 (l_interest_calculation_basis = 'REAMORT')) ) THEN
2904 IF (l_k_rate_tbl.COUNT > 0) THEN
2905 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
2906 ,p_msg_name => G_OKL_LLA_VAR_RATE_ERROR
2907 ,p_token1 => G_LEASE_TYPE
2908 ,p_token1_value => 'LEASEOP,LEASEDF,LEASEST'
2909 ,p_token2 => G_INT_BASIS
2910 ,p_token2_value => 'FIXED,REAMORT');
2911 RAISE OKL_API.G_EXCEPTION_ERROR;
2912 END IF;
2913 ELS */
2914 IF (((l_deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST')) AND
2915 (l_interest_calculation_basis IN ( 'FLOAT_FACTORS', 'REAMORT'))) OR
2916 --((l_deal_type = 'LOAN') AND
2917 --(l_interest_calculation_basis IN
2918 --('FIXED','FLOAT', 'CATCHUP/CLEANUP'))) OR
2919 (l_deal_type = 'LOAN' AND
2920 (l_interest_calculation_basis IN
2921 ('FLOAT', 'REAMORT', 'CATCHUP/CLEANUP')
2922 OR
2923 (l_interest_calculation_basis='FIXED' AND
2924 l_revenue_recognition_method = 'ACTUAL') )) OR
2925 ((l_deal_type = 'LOAN-REVOLVING') AND
2926 (l_interest_calculation_basis = 'FLOAT')) ) THEN
2927 IF (l_k_rate_tbl.COUNT < 1) THEN
2928 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
2929 ,p_msg_name => G_OKL_LLA_VAR_RATE_MISSING
2930 ,p_token1 => G_CONT_ID
2931 ,p_token1_value => p_khr_id);
2932 RAISE OKL_API.G_EXCEPTION_ERROR;
2933 END IF;
2934
2935 END IF;
2936
2937 IF (l_k_rate_tbl.COUNT > 0) THEN
2938 okl_krp_pvt.validate_row(
2939 p_api_version => p_api_version,
2940 p_init_msg_list => p_init_msg_list,
2941 x_return_status => x_return_status,
2942 x_msg_count => x_msg_count,
2943 x_msg_data => x_msg_data,
2944 p_deal_type => x_pdt_parameter_rec.deal_type,
2945 p_rev_rec_method => x_pdt_parameter_rec.Revenue_recognition_method,
2946 p_int_calc_basis => x_pdt_parameter_rec.interest_calculation_basis,
2947 p_krpv_tbl => l_k_rate_tbl,
2948 p_stack_messages => 'Y',
2949 p_validate_flag => 'F'
2950 );
2951 END IF;
2952 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2953 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After validation_row: x_return_status=' || x_return_status);
2954 END IF;
2955
2956 -- Bug 4722746
2957 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2958 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_k_rate_tbl.COUNT=' || l_k_rate_tbl.COUNT);
2959 END IF;
2960 IF (l_interest_calculation_basis = 'CATCHUP/CLEANUP'
2961 AND l_revenue_recognition_method = 'STREAMS'
2962 AND l_k_rate_tbl.COUNT > 0 ) THEN
2963
2964 FOR i IN l_k_rate_tbl.FIRST..l_k_rate_tbl.LAST
2965 LOOP
2966 --print('Catchup :1');
2967 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2968 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i=' || i || ':' || l_k_rate_tbl(i).catchup_frequency_code);
2969 END IF;
2970 IF (l_k_rate_tbl(i).catchup_frequency_code is NOT NULL AND
2971 l_k_rate_tbl(i).catchup_frequency_code <> G_MISS_CHAR) THEN
2972 NULL;
2973 ELSE
2974 OKC_API.set_message(p_app_name => G_APP_NAME,
2975 p_msg_name => G_REQUIRED_VALUE,
2976 p_token1 => G_COL_NAME_TOKEN,
2977 p_token1_value => 'Catchup Frequency');
2978 RAISE OKL_API.G_EXCEPTION_ERROR;
2979 END IF;
2980
2981 --print('Catchup :2');
2982 /*IF (l_k_rate_tbl(i).catchup_start_date is NOT NULL AND
2983 l_k_rate_tbl(i).catchup_start_date <> G_MISS_DATE) THEN
2984 NULL;
2985 ELSE
2986 l_k_rate_tbl(i).catchup_start_date := l_contract_start_date;
2987 END IF;*/
2988
2989 --print('Catchup :3');
2990 IF (l_k_rate_tbl(i).catchup_settlement_code is NOT NULL AND
2991 l_k_rate_tbl(i).catchup_settlement_code <> G_MISS_CHAR) THEN
2992 NULL;
2993 ELSE
2994 OKC_API.set_message(p_app_name => G_APP_NAME,
2995 p_msg_name => G_REQUIRED_VALUE,
2996 p_token1 => G_COL_NAME_TOKEN,
2997 p_token1_value => 'Catchup Settlement');
2998 RAISE OKL_API.G_EXCEPTION_ERROR;
2999 END IF;
3000
3001 --print('Catchup :4');
3002 IF (l_k_rate_tbl(i).catchup_basis_code is NOT NULL AND
3003 l_k_rate_tbl(i).catchup_basis_code <> G_MISS_CHAR) THEN
3004 NULL;
3005 ELSE
3006 OKC_API.set_message(p_app_name => G_APP_NAME,
3007 p_msg_name => G_REQUIRED_VALUE,
3008 p_token1 => G_COL_NAME_TOKEN,
3009 p_token1_value => 'Catchup Basis');
3010 RAISE OKL_API.G_EXCEPTION_ERROR;
3011 END IF;
3012 END LOOP;
3013
3014 END IF;
3015
3016
3017 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
3018
3019 EXCEPTION
3020 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3021 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3022 p_api_name => l_api_name,
3023 p_pkg_name => g_pkg_name,
3024 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3025 x_msg_count => x_msg_count,
3026 x_msg_data => x_msg_data,
3027 p_api_type => g_api_type);
3028
3029 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3030 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3031 p_api_name => l_api_name,
3032 p_pkg_name => g_pkg_name,
3033 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3034 x_msg_count => x_msg_count,
3035 x_msg_data => x_msg_data,
3036 p_api_type => g_api_type);
3037
3038 WHEN OTHERS THEN
3039 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3040 p_api_name => l_api_name,
3041 p_pkg_name => g_pkg_name,
3042 p_exc_name => 'OTHERS',
3043 x_msg_count => x_msg_count,
3044 x_msg_data => x_msg_data,
3045 p_api_type => g_api_type);
3046 end;
3047
3048 /* For UI/contract import to call */
3049 PROCEDURE validate_k_rate_params(
3050 p_api_version IN NUMBER,
3051 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3052 x_return_status OUT NOCOPY VARCHAR2,
3053 x_msg_count OUT NOCOPY NUMBER,
3054 x_msg_data OUT NOCOPY VARCHAR2,
3055 p_product_id IN okl_products_v.id%type,
3056 p_k_rate_tbl IN krpv_tbl_type,
3057 --Bug# 7440232
3058 p_validate_flag IN VARCHAR2 DEFAULT 'Y') IS
3059
3060 p_pdtv_rec OKL_SETUPPRODUCTS_PUB.pdtv_rec_type;
3061 x_pdt_parameter_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
3062 x_no_data_found BOOLEAN;
3063
3064 l_pdt_id number;
3065 begin
3066
3067 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3068 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'In validate_k_rate_params from UI/contract import...');
3069 END IF;
3070 /*begin
3071 select id into l_pdt_id
3072 from okl_products_v
3073 where name = p_product_name;
3074 exception when no_data_found then
3075 null;
3076 -- ToDo AKP: Set Message : Product not found. Raise error.
3077 end; */
3078 p_pdtv_rec.id := p_product_id;
3079 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3080 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_product_id=' || to_char(p_product_id));
3081 END IF;
3082 OKL_SETUPPRODUCTS_PUB.Getpdt_parameters(
3083 p_api_version => p_api_version,
3084 p_init_msg_list => p_init_msg_list,
3085 x_return_status => x_return_status,
3086 x_msg_count => x_msg_count,
3087 x_msg_data => x_msg_data,
3088 p_pdtv_rec => p_pdtv_rec,
3089 x_no_data_found => x_no_data_found,
3090 p_pdt_parameter_rec => x_pdt_parameter_rec);
3091
3092 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3093 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'x_return_status=' || x_return_status);
3094 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'name=' || x_pdt_parameter_rec.name);
3095 END IF;
3096 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3097 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3098 ELSIF ( x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3099 RAISE OKL_API.G_EXCEPTION_ERROR;
3100 /*ELSIF NVL(x_pdt_parameter_rec.Name,OKL_API.G_MISS_CHAR) = OKL_API.G_MISS_CHAR THEN
3101 x_return_status := OKL_API.G_RET_STS_ERROR;
3102 RAISE OKL_API.G_EXCEPTION_ERROR;*/
3103 END IF;
3104
3105 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3106 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Deal_type=' || x_pdt_parameter_rec.Deal_type);
3107 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Revenue_Recognition_Method=' || x_pdt_parameter_rec.Revenue_Recognition_Method);
3108 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Interest_Calculation_Basis=' || x_pdt_parameter_rec.Interest_Calculation_Basis);
3109 END IF;
3110
3111 okl_krp_pvt.validate_row(
3112 p_api_version => p_api_version,
3113 p_init_msg_list => p_init_msg_list,
3114 x_return_status => x_return_status,
3115 x_msg_count => x_msg_count,
3116 x_msg_data => x_msg_data,
3117 p_deal_type => x_pdt_parameter_rec.deal_type,
3118 p_rev_rec_method => x_pdt_parameter_rec.Revenue_recognition_method,
3119 p_int_calc_basis => x_pdt_parameter_rec.interest_calculation_basis,
3120 p_krpv_tbl => p_k_rate_tbl,
3121 --Bug# 7440232
3122 p_stack_messages => 'Y',
3123 p_validate_flag => p_validate_flag
3124 );
3125 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3126 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After validate_row: x_return_status=' || x_return_status);
3127 END IF;
3128 end;
3129
3130 -- Start of comments
3131 --
3132 -- Procedure Name : generate_rate_summary
3133 -- Description : Called through rosetta to generate summary of interest and additional interest
3134 -- rate parameters for actual and conversion records with
3135 -- different effectivities.
3136 -- Business Rules :
3137 -- Parameters :
3138 -- Version : 1.0, ramurt Created.
3139 -- End of comments
3140
3141 Procedure generate_rate_summary(
3142 p_api_version IN NUMBER,
3143 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3144 x_return_status OUT NOCOPY VARCHAR2,
3145 x_msg_count OUT NOCOPY NUMBER,
3146 x_msg_data OUT NOCOPY VARCHAR2,
3147 p_chr_id IN NUMBER,
3148 x_var_par_tbl OUT NOCOPY var_prm_tbl_type)
3149 AS
3150 l_api_version CONSTANT NUMBER := 1;
3151 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_RATE_SUMMARY';
3152 l_return_value VARCHAR2(1) := '';
3153
3154 CURSOR c_int_rate_param IS
3155 SELECT parameter_type_code,
3156 interest_index_id,
3157 base_rate,
3158 interest_start_date,
3159 adder_rate,
3160 maximum_rate,
3161 minimum_rate,
3162 principal_basis_code,
3163 days_in_a_month_code,
3164 days_in_a_year_code,
3165 interest_basis_code,
3166 effective_from_date,
3167 effective_to_date,
3168 rate_delay_code,
3169 rate_delay_frequency,
3170 compounding_frequency_code,
3171 calculation_formula_id,
3172 catchup_basis_code,
3173 catchup_start_date,
3174 catchup_settlement_code,
3175 rate_change_start_date,
3176 rate_change_frequency_code,
3177 rate_change_value,
3178 conversion_option_code,
3179 next_conversion_date,
3180 conversion_type_code
3181 FROM OKL_K_RATE_PARAMS
3182 WHERE KHR_ID = p_chr_id
3183 AND parameter_type_code IN ('ACTUAL', 'CONVERSION')
3184 ORDER BY effective_from_date, parameter_type_code;
3185
3186 CURSOR fnd_csr(p_lookup_code FND_LOOKUPS.LOOKUP_CODE%TYPE) IS
3187 SELECT meaning
3188 FROM FND_LOOKUPS
3189 WHERE lookup_type = 'OKL_VAR_RATE_PARAMS_TYPE'
3190 AND lookup_code = p_lookup_code
3191 AND nvl(enabled_flag,'N') = 'Y'
3192 AND sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate);
3193 l_meaning FND_LOOKUPS.MEANING%TYPE;
3194
3195 i NUMBER;
3196 l_int_rate_exist BOOLEAN;
3197 l_addl_int_rate_exist BOOLEAN;
3198 l_conv_basis_exist BOOLEAN;
3199
3200 BEGIN
3201 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3202
3203 x_return_status := OKL_API.START_ACTIVITY(
3204 p_api_name => l_api_name,
3205 p_pkg_name => g_pkg_name,
3206 p_init_msg_list => p_init_msg_list,
3207 l_api_version => l_api_version,
3208 p_api_version => p_api_version,
3209 p_api_type => G_API_TYPE,
3210 x_return_status => x_return_status);
3211
3212 --dbms_output.put_line('here1');
3213
3214 i := 0;
3215
3216 FOR c_int_rate_param_rec IN c_int_rate_param
3217 LOOP
3218 l_int_rate_exist := c_int_rate_param_rec.interest_index_id IS NOT NULL
3219 OR c_int_rate_param_rec.base_rate IS NOT NULL
3220 OR c_int_rate_param_rec.interest_start_date IS NOT NULL
3221 OR c_int_rate_param_rec.adder_rate IS NOT NULL
3222 OR c_int_rate_param_rec.maximum_rate IS NOT NULL
3223 OR c_int_rate_param_rec.minimum_rate IS NOT NULL
3224 OR c_int_rate_param_rec.principal_basis_code IS NOT NULL
3225 OR c_int_rate_param_rec.days_in_a_month_code IS NOT NULL
3226 OR c_int_rate_param_rec.days_in_a_year_code IS NOT NULL
3227 OR c_int_rate_param_rec.interest_basis_code IS NOT NULL;
3228
3229 l_addl_int_rate_exist := c_int_rate_param_rec.rate_delay_code IS NOT NULL
3230 OR c_int_rate_param_rec.rate_delay_frequency IS NOT NULL
3231 OR c_int_rate_param_rec.compounding_frequency_code IS NOT NULL
3232 OR c_int_rate_param_rec.calculation_formula_id IS NOT NULL
3233 OR c_int_rate_param_rec.catchup_basis_code IS NOT NULL
3234 OR c_int_rate_param_rec.catchup_start_date IS NOT NULL
3235 OR c_int_rate_param_rec.catchup_settlement_code IS NOT NULL
3236 OR c_int_rate_param_rec.rate_change_start_date IS NOT NULL
3237 OR c_int_rate_param_rec.rate_change_frequency_code IS NOT NULL
3238 OR c_int_rate_param_rec.rate_change_value IS NOT NULL;
3239
3240 l_conv_basis_exist := c_int_rate_param_rec.conversion_option_code IS NOT NULL
3241 OR c_int_rate_param_rec.next_conversion_date IS NOT NULL
3242 OR c_int_rate_param_rec.conversion_type_code IS NOT NULL;
3243
3244 -- Interest Rate Parameters and Additional Interest Rate Parameters.
3245 IF ( c_int_rate_param_rec.parameter_type_code = 'ACTUAL') THEN
3246 --Bug# 7440232
3247 IF( l_int_rate_exist ) OR ( l_addl_int_rate_exist ) THEN
3248 i := i+1;
3249 OPEN fnd_csr('INTEREST_RATE_PARAMS');
3250 FETCH fnd_csr INTO l_meaning;
3251 CLOSE fnd_csr;
3252 x_var_par_tbl(i).param_identifier_meaning := l_meaning;
3253 x_var_par_tbl(i).param_identifier := 'INTEREST_RATE_PARAMS';
3254 x_var_par_tbl(i).effective_from_date := c_int_rate_param_rec.effective_from_date;
3255 x_var_par_tbl(i).effective_to_date := c_int_rate_param_rec.effective_to_date;
3256 -- gboomina modified for Bug 5876083 - Start
3257 -- Returning Parameter Type Code which is used in the UI
3258 x_var_par_tbl(i).parameter_type_code := c_int_rate_param_rec.parameter_type_code;
3259 -- gboomina modified for Bug 5876083 - End
3260 END IF;
3261
3262 IF( l_conv_basis_exist ) THEN
3263 i := i+1;
3264 OPEN fnd_csr('CONVERSION_BASIS');
3265 FETCH fnd_csr INTO l_meaning;
3266 CLOSE fnd_csr;
3267 x_var_par_tbl(i).param_identifier_meaning := l_meaning;
3268 x_var_par_tbl(i).param_identifier := 'CONVERSION_BASIS';
3269 x_var_par_tbl(i).effective_from_date := c_int_rate_param_rec.effective_from_date;
3270 x_var_par_tbl(i).effective_to_date := c_int_rate_param_rec.effective_to_date;
3271 -- gboomina modified for Bug 5876083 - Start
3272 -- Returning Parameter Type Code which is used in the UI
3273 x_var_par_tbl(i).parameter_type_code := c_int_rate_param_rec.parameter_type_code;
3274 -- gboomina modified for Bug 5876083 - End
3275 END IF;
3276 END IF;
3277 -- Interest Rate Parameters and Additional Interest Rate Parameters for Conversion
3278 -- and parameters for Conversion Basis.
3279 IF ( c_int_rate_param_rec.parameter_type_code = 'CONVERSION') THEN
3280 --Bug# 7440232
3281 IF( l_int_rate_exist ) OR ( l_addl_int_rate_exist ) THEN
3282 i := i+1;
3283 OPEN fnd_csr('INTEREST_RATE_PARAMS_CONV');
3284 FETCH fnd_csr INTO l_meaning;
3285 CLOSE fnd_csr;
3286 x_var_par_tbl(i).param_identifier_meaning := l_meaning;
3287 x_var_par_tbl(i).param_identifier := 'INTEREST_RATE_PARAMS_CONV';
3288 x_var_par_tbl(i).effective_from_date := c_int_rate_param_rec.effective_from_date;
3289 x_var_par_tbl(i).effective_to_date := c_int_rate_param_rec.effective_to_date;
3290 -- gboomina modified for Bug 5876083 - Start
3291 -- Returning Parameter Type Code which is used in the UI
3292 x_var_par_tbl(i).parameter_type_code := c_int_rate_param_rec.parameter_type_code;
3293 -- gboomina modified for Bug 5876083 - End
3294 END IF;
3295
3296 END IF;
3297 END LOOP;
3298
3299 --dbms_output.put_line('here4');
3300 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
3301
3302 EXCEPTION
3303 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3304 --Close all cursors if open
3305 IF c_int_rate_param%ISOPEN THEN
3306 CLOSE c_int_rate_param;
3307 END IF;
3308 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3309 l_api_name,
3310 G_PKG_NAME,
3311 'OKL_API.G_RET_STS_ERROR',
3312 x_msg_count,
3313 x_msg_data,
3314 '_PVT');
3315
3316 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3317 --Close all cursors if open
3318 IF c_int_rate_param%ISOPEN THEN
3319 CLOSE c_int_rate_param;
3320 END IF;
3321 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
3322 l_api_name,
3323 G_PKG_NAME,
3324 'OKL_API.G_RET_STS_UNEXP_ERROR',
3325 x_msg_count,
3326 x_msg_data,
3327 '_PVT');
3328
3329 WHEN OTHERS THEN
3330 --Close all cursors if open
3331 IF c_int_rate_param%ISOPEN THEN
3332 CLOSE c_int_rate_param;
3333 END IF;
3334 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
3335 l_api_name,
3336 G_PKG_NAME,
3337 'OTHERS',
3338 x_msg_count,
3339 x_msg_data,
3340 '_PVT');
3341
3342 END generate_rate_summary;
3343
3344 -- default_k_rate_params
3345 -- Default the values based on business rules
3346
3347 PROCEDURE default_k_rate_params(
3348 p_api_version IN NUMBER,
3349 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3350 x_return_status OUT NOCOPY VARCHAR2,
3351 x_msg_count OUT NOCOPY NUMBER,
3352 x_msg_data OUT NOCOPY VARCHAR2,
3353 p_deal_type IN okl_product_parameters_v.deal_type%type,
3354 p_rev_rec_method IN okl_product_parameters_v.revenue_recognition_method%type,
3355 p_int_calc_basis IN okl_product_parameters_v.interest_calculation_basis%type,
3356 p_column_name IN VARCHAR2,
3357 p_krpv_rec IN OUT NOCOPY krpv_rec_type) IS
3358 BEGIN
3359 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3360
3361 IF (p_column_name = 'PRINCIPAL_BASIS_CODE' OR p_column_name = 'ALL') THEN
3362 IF (p_deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST') AND
3363 (p_int_calc_basis = 'REAMORT' AND p_rev_rec_method = 'STREAMS')) THEN
3364 p_krpv_rec.PRINCIPAL_BASIS_CODE := 'SCHEDULED';
3365 ELSIF (p_deal_type = 'LOAN' AND
3366 p_int_calc_basis = 'FLOAT' AND p_rev_rec_method = 'ACTUAL') THEN
3367 p_krpv_rec.PRINCIPAL_BASIS_CODE := 'ACTUAL';
3368 ELSIF (p_deal_type = 'LOAN' AND
3369 p_int_calc_basis = 'REAMORT' AND
3370 p_rev_rec_method IN ('STREAMS', 'ACTUAL')) THEN
3371 p_krpv_rec.PRINCIPAL_BASIS_CODE := 'SCHEDULED';
3372 ELSIF (p_deal_type = 'LOAN' AND
3373 p_int_calc_basis = 'CATCHUP/CLEANUP' AND
3374 p_rev_rec_method = 'STREAMS' ) THEN
3375 p_krpv_rec.PRINCIPAL_BASIS_CODE := 'ACTUAL';
3376 ELSIF (p_deal_type = 'LOAN-REVOLVING' AND
3377 p_int_calc_basis = 'FLOAT' AND
3378 p_rev_rec_method IN ( 'ESTIMATED_AND_BILLED', 'ACTUAL' )) THEN
3379 p_krpv_rec.PRINCIPAL_BASIS_CODE := 'ACTUAL';
3380 END IF;
3381
3382 IF (p_column_name = 'PRINCIPAL_BASIS_CODE') THEN
3383 RETURN;
3384 END IF;
3385 END IF;
3386
3387 IF (p_column_name = 'DAYS_IN_A_MONTH_CODE' OR p_column_name = 'ALL') THEN
3388 IF (p_deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST') AND
3389 (p_int_calc_basis = 'REAMORT' AND p_rev_rec_method = 'STREAMS')) THEN
3390 p_krpv_rec.DAYS_IN_A_MONTH_CODE := '30';
3391 ELSIF (p_deal_type = 'LOAN' AND
3392 p_int_calc_basis = 'REAMORT' AND
3393 p_rev_rec_method = 'STREAMS' ) THEN
3394 p_krpv_rec.DAYS_IN_A_MONTH_CODE := '30';
3395 END IF;
3396
3397 IF (p_column_name = 'DAYS_IN_A_MONTH_CODE' ) THEN
3398 RETURN;
3399 END IF;
3400 END IF;
3401
3402 IF (p_column_name = 'DAYS_IN_A_YEAR_CODE' OR p_column_name = 'ALL') THEN
3403 IF (p_deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST') AND
3404 (p_int_calc_basis = 'REAMORT' AND p_rev_rec_method = 'STREAMS')) THEN
3405 p_krpv_rec.DAYS_IN_A_YEAR_CODE := '360';
3406 ELSIF (p_deal_type = 'LOAN' AND
3407 p_int_calc_basis = 'REAMORT' AND
3408 p_rev_rec_method = 'STREAMS' ) THEN
3409 p_krpv_rec.DAYS_IN_A_YEAR_CODE := '360';
3410 END IF;
3411
3412 IF (p_column_name = 'DAYS_IN_A_YEAR_CODE' ) THEN
3413 RETURN;
3414 END IF;
3415 END IF;
3416
3417 IF (p_column_name = 'INTEREST_BASIS_CODE' OR p_column_name = 'ALL') THEN
3418 IF (p_deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST') AND
3419 (p_int_calc_basis = 'REAMORT' AND p_rev_rec_method = 'STREAMS')) THEN
3420 p_krpv_rec.INTEREST_BASIS_CODE := 'SIMPLE';
3421 ELSIF (p_deal_type = 'LOAN' AND
3422 p_int_calc_basis = 'REAMORT' AND
3423 p_rev_rec_method = 'STREAMS' ) THEN
3424 p_krpv_rec.INTEREST_BASIS_CODE := 'SIMPLE';
3425 END IF;
3426
3427 IF (p_column_name = 'INTEREST_BASIS_CODE') THEN
3428 RETURN;
3429 END IF;
3430 END IF;
3431
3432 IF (p_column_name = 'RATE_CHANGE_FREQUENCY_CODE' OR p_column_name= 'ALL') THEN
3433 IF (p_deal_type IN ('LEASEOP', 'LEASEDF', 'LEASEST') AND
3434 (p_int_calc_basis = 'REAMORT' AND p_rev_rec_method = 'STREAMS')) THEN
3435 p_krpv_rec.RATE_CHANGE_FREQUENCY_CODE := 'BILLING_DATE';
3436 ELSIF (p_deal_type = 'LOAN' AND
3437 p_int_calc_basis = 'REAMORT' AND
3438 p_rev_rec_method = 'STREAMS' ) THEN
3439 p_krpv_rec.RATE_CHANGE_FREQUENCY_CODE := 'BILLING_DATE';
3440 END IF;
3441
3442 IF (p_column_name = 'RATE_CHANGE_FREQUENCY_CODE' ) THEN
3443 RETURN;
3444 END IF;
3445 END IF;
3446
3447 IF (p_column_name = 'CATCHUP_BASIS_CODE' OR p_column_name= 'ALL') THEN
3448 IF (p_deal_type = 'LOAN' AND
3449 p_int_calc_basis = 'CATCHUP/CLEANUP' AND
3450 p_rev_rec_method = 'STREAMS' ) THEN
3451 p_krpv_rec.CATCHUP_BASIS_CODE := 'ACTUAL';
3452 END IF;
3453
3454 IF (p_column_name = 'CATCHUP_BASIS_CODE' ) THEN
3455 RETURN;
3456 END IF;
3457 END IF;
3458
3459 END;
3460
3461 PROCEDURE cascade_contract_start_date(
3462 p_api_version IN NUMBER,
3463 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3464 x_return_status OUT NOCOPY VARCHAR2,
3465 x_msg_count OUT NOCOPY NUMBER,
3466 x_msg_data OUT NOCOPY VARCHAR2,
3467 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
3468 p_new_start_date IN DATE) IS
3469 l_effective_from_date DATE;
3470 l_api_name varchar2(30) := 'cascade_cntrct_start_date';
3471 l_api_version number := 1;
3472 l_no_data_found BOOLEAN:= FALSE;
3473 l_krpv_rec krpv_rec_type;
3474 x_krpv_rec krpv_rec_type;
3475 l_pdt_parameter_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
3476 l_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
3477 l_interest_processing_started BOOLEAN;
3478 l_product_id NUMBER;
3479 l_contract_start_date DATE;
3480
3481 --Bug 4735972
3482 CURSOR rate_csr(p_id NUMBER) IS
3483 SELECT COUNT(1) COUNT1
3484 FROM OKL_K_RATE_PARAMS
3485 WHERE KHR_ID = p_id
3486 AND EFFECTIVE_TO_DATE IS NULL;
3487 l_rate_count NUMBER;
3488 l_catchup_start_date DATE;
3489
3490
3491 BEGIN
3492 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3493 x_return_status := OKL_API.START_ACTIVITY(
3494 p_api_name => l_api_name,
3495 p_pkg_name => g_pkg_name,
3496 p_init_msg_list => p_init_msg_list,
3497 l_api_version => l_api_version,
3498 p_api_version => p_api_version,
3499 p_api_type => G_API_TYPE,
3500 x_return_status => x_return_status);
3501
3502
3503 --Bug 4735972
3504 FOR r IN rate_csr(p_chr_id)
3505 LOOP
3506 l_rate_count := r.COUNT1;
3507 END LOOP;
3508
3509 --Bug 4735972
3510 IF (l_rate_count > 0) THEN
3511
3512 get_product(
3513 p_api_version => p_api_version,
3514 p_init_msg_list => p_init_msg_list,
3515 x_return_status => x_return_status,
3516 x_msg_count => x_msg_count,
3517 x_msg_data => x_msg_data,
3518 p_khr_id => p_chr_id,
3519 x_pdt_parameter_rec => l_pdt_parameter_rec);
3520
3521 l_interest_processing_started := interest_processing
3522 (p_chr_id,
3523 l_contract_number,
3524 l_contract_start_date);
3525
3526 IF (l_interest_processing_started) THEN
3527 -- Parameters RATE_CHANGE_START_DATE can not be updated because
3528 -- Interest Processing has already started for contract CONTRACT_NUMBER.
3529 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
3530 ,p_msg_name => 'OKL_LLA_VAR_RATE_INT_PROC'
3531 ,p_token1 => 'PARAMETER_NAME'
3532 --,p_token1_value => 'INTEREST_START_DATE'
3533 ,p_token1_value =>
3534 'INTEREST_START_DATE,RATE_CHANGE_START_DATE,CATCHUP_START_DATE'
3535 ,p_token2 => 'CONTRACT_NUMBER'
3536 ,p_token2_value => l_contract_number);
3537 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3538 END IF;
3539
3540 IF (l_pdt_parameter_rec.DEAL_TYPE IN ('LEASEOP', 'LEASEDF', 'LEASEST') AND
3541 l_pdt_parameter_rec.interest_calculation_basis IN
3542 ('REAMORT', 'FLOAT_FACTORS') AND
3543 l_pdt_parameter_rec.revenue_recognition_method ='STREAMS' ) OR
3544 (l_pdt_parameter_rec.DEAL_TYPE IN ('LOAN') AND
3545 l_pdt_parameter_rec.interest_calculation_basis = 'FLOAT' AND
3546 l_pdt_parameter_rec.revenue_recognition_method IN
3547 ('ESTIMATED_AND_BILLED', 'ACTUAL' ) ) OR
3548 (l_pdt_parameter_rec.DEAL_TYPE IN ('LOAN') AND
3549 l_pdt_parameter_rec.interest_calculation_basis IN
3550 ('REAMORT', 'CATCHUP/CLEANUP', 'FIXED') AND
3551 l_pdt_parameter_rec.revenue_recognition_method IN
3552 ('STREAMS', 'ACTUAL' ) ) OR
3553 (l_pdt_parameter_rec.DEAL_TYPE IN ('LOAN-REVOLVING') AND
3554 l_pdt_parameter_rec.interest_calculation_basis = 'FLOAT' AND
3555 l_pdt_parameter_rec.revenue_recognition_method IN
3556 ('ESTIMATED_AND_BILLED', 'ACTUAL' ) ) THEN
3557
3558 get_effective_from_date(
3559 p_api_version ,
3560 p_init_msg_list,
3561 x_return_status,
3562 x_msg_count ,
3563 x_msg_data ,
3564 p_chr_id ,
3565 l_effective_from_date,
3566 l_no_data_found);
3567 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3568 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3569 END IF;
3570
3571 IF NOT(l_no_data_found) THEN
3572 l_krpv_rec.khr_id := p_chr_id;
3573 l_krpv_rec.effective_from_date := l_effective_from_date;
3574 --l_krpv_rec.effective_from_date := p_new_start_date;
3575 l_krpv_rec.parameter_type_code := 'ACTUAL';
3576
3577 l_krpv_rec.interest_start_date := p_new_start_date;
3578 l_krpv_rec.rate_change_start_date := p_new_start_date;
3579
3580 IF (l_pdt_parameter_rec.DEAL_TYPE = 'LOAN' AND
3581 l_pdt_parameter_rec.interest_calculation_basis =
3582 'CATCHUP/CLEANUP' AND
3583 l_pdt_parameter_rec.revenue_recognition_method = 'STREAMS' ) THEN
3584 l_krpv_rec.catchup_start_date := p_new_start_date;
3585 l_catchup_start_date := p_new_start_date;
3586 ELSE
3587 l_catchup_start_date := null;
3588 END IF;
3589
3590 update OKL_K_RATE_PARAMS
3591 SET interest_start_date = p_new_start_date,
3592 rate_change_start_date = p_new_start_date,
3593 catchup_start_date = nvl(l_catchup_start_date,catchup_start_date)
3594 WHERE khr_id = p_chr_id
3595 AND effective_from_date = l_effective_from_date
3596 AND effective_to_date is NULL;
3597
3598 /*update_k_rate_params(
3599 p_api_version => p_api_version,
3600 p_init_msg_list => p_init_msg_list,
3601 x_return_status => x_return_status,
3602 x_msg_count => x_msg_count,
3603 x_msg_data => x_msg_data,
3604 p_krpv_rec => l_krpv_rec,
3605 x_krpv_rec => x_krpv_rec);
3606 --p_validate_flag => 'Y');
3607 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3608 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3609 END IF; */
3610
3611 update okl_k_rate_params
3612 set effective_from_date = p_new_start_date
3613 WHERE khr_id = p_chr_id
3614 AND effective_from_date = l_effective_from_date
3615 AND effective_to_date IS NULL;
3616 --AND parameter_type_code = 'ACTUAL';
3617
3618 /*ELSE
3619 update okl_k_rate_params
3620 set effective_from_date = p_new_start_date
3621 WHERE khr_id = p_chr_id
3622 AND effective_from_date = l_effective_from_date
3623 AND effective_to_date is NULL;
3624 --AND parameter_type_code = 'ACTUAL'; */
3625
3626 END IF;
3627
3628 END IF;
3629
3630 END IF;
3631
3632 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
3633
3634 EXCEPTION
3635 WHEN OTHERS THEN
3636 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3637 p_api_name => l_api_name,
3638 p_pkg_name => g_pkg_name,
3639 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3640 x_msg_count => x_msg_count,
3641 x_msg_data => x_msg_data,
3642 p_api_type => g_api_type);
3643
3644 END;
3645
3646 -- smadhava Bug#4542290 - 22-Aug-2005 - Added - Start
3647 --------------------------------------------------------------------------------
3648 --Start of Comments
3649 --API Name : Check_Rebook_Allowed
3650 --Description : Process API to check if the rebook transaction can be allowed
3651 -- for the contract.
3652 -- 1. Obtain the Product Parameters - Book Classification, Interest
3653 -- Calculation Basis and Revenue Recognition Method.
3654 -- 2. Obtain the due date for various streams accrued and billed.
3655 -- 3. Ensure that the rebook date is after the due date of streams
3656 -- billed/accrued.
3657 -- The valid combinations of Book classification and Interest Calculation Basis:
3658 -- (i) OPLease/STLease/DFLease and Fixed/Reamort(If interest has not been
3659 -- processed before)/Float Factors
3660 -- (ii) Loan and Fixed/Reamort(If interest has not been processed before)/
3661 -- Float/ Catchup-Cleanup
3662 -- (iii) Revolving Loan and Float
3663 -- Streams Used to calculate due date:
3664 -- ------------------------------------------------------------------------
3665 -- S.NO BOOK_CLASS ICB RRM ACCRUAL_STREAM BILL_STREAM
3666 -- ------------------------------------------------------------------------
3667 -- 1 LEASEOP REAMORT RENT_ACCRUAL RENT
3668 -- 2 LEASEST REAMORT LEASE_INCOME RENT
3669 -- 3 LEASEDF REAMORT LEASE_INCOME RENT
3670 -- 4 LOAN REAMORT INTEREST_INCOME RENT
3671 -- 5 LOAN FLOAT ESTIMATED VARIABLE_INTEREST VARIABLE_INTEREST
3672 -- and ACTUAL _INCOME _INCOME
3673 -- 6 LOAN FLOAT ACTUAL INTEREST_INCOME LOAN_PAYMENT
3674 -- 7 REV_LOAN FLOAT ESTIMATED VARIABLE_INTEREST VARIABLE_INTEREST
3675 -- and ACTUAL _INCOME _INCOME
3676 -- 8 REV_LOAN FLOAT ACTUAL INTEREST_INCOME LOAN_PAYMENT
3677 -- ------------------------------------------------------------------------
3678 --History :
3679 -- 22-AUG-2005 smadhava Created
3680 --End of Comments
3681 ------------------------------------------------------------------------------
3682 PROCEDURE check_rebook_allowed (
3683 p_api_version IN NUMBER,
3684 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3685 x_return_status OUT NOCOPY VARCHAR2,
3686 x_msg_count OUT NOCOPY NUMBER,
3687 x_msg_data OUT NOCOPY VARCHAR2,
3688 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
3689 p_rebook_date IN DATE) IS
3690
3691 l_api_name VARCHAR2(30) := 'check_rebook_allowed';
3692 l_api_version NUMBER := 1;
3693
3694 l_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
3695 l_book_class OKL_PRODUCT_PARAMETERS_V.DEAL_TYPE%TYPE;
3696 l_book_class_meaning OKL_PRODUCT_PARAMETERS_V.DEAL_TYPE_MEANING%TYPE;
3697 l_interest_calc_basis OKL_PRODUCT_PARAMETERS_V.INTEREST_CALCULATION_BASIS%TYPE;
3698 l_interest_calc_meaning OKL_PRODUCT_PARAMETERS_V.INTEREST_CALCULATION_MEANING%TYPE;
3699 l_rev_recog_method OKL_PRODUCT_PARAMETERS_V.REVENUE_RECOGNITION_METHOD%TYPE;
3700 --l_rev_recog_meaning OKL_PRODUCT_PARAMETERS_V.REVENUE_RECOGNITION_MEANING%TYPE;
3701 l_interest_proc_date OKL_K_HEADERS.DATE_LAST_INTERIM_INTEREST_CAL%TYPE;
3702 l_stream_name OKL_STRM_TYPE_V.STYB_PURPOSE_MEANING%TYPE;
3703
3704 l_last_accrued_due_date DATE;
3705 l_last_billed_due_date DATE;
3706
3707 l_pdt_params_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
3708
3709 -- Get the quality descriptions
3710 CURSOR get_prod_param_values(cp_name OKL_PDT_QUALITYS.NAME%TYPE
3711 , cp_value OKL_PQY_VALUES.VALUE%TYPE) IS
3712 SELECT
3713 QVE.DESCRIPTION
3714 FROM
3715 OKL_PDT_QUALITYS PQY
3716 , OKL_PQY_VALUES QVE
3717 WHERE
3718 QVE.PQY_ID = PQY.ID
3719 AND PQY.NAME = cp_name
3720 AND QVE.VALUE = cp_value;
3721
3722 -- Get the lookup meanings
3723 CURSOR get_lkp_meaning(cp_lkp_type FND_LOOKUPS.LOOKUP_TYPE%TYPE
3724 , cp_lkp_code FND_LOOKUPS.LOOKUP_CODE%TYPE) IS
3725 SELECT
3726 FNDLUP.MEANING
3727 FROM
3728 FND_LOOKUPS FNDLUP
3729 WHERE
3730 FNDLUP.LOOKUP_TYPE = cp_lkp_type
3731 AND FNDLUP.LOOKUP_CODE = cp_lkp_code
3732 AND SYSDATE BETWEEN
3733 NVL(FNDLUP.START_DATE_ACTIVE,SYSDATE)
3734 AND NVL(FNDLUP.END_DATE_ACTIVE,SYSDATE);
3735
3736 -- Cursor to query to get the Interest Processed flag
3737 CURSOR get_contract_details(cp_chr_id OKL_K_HEADERS.ID%TYPE) IS
3738 SELECT
3739 CHR.CONTRACT_NUMBER
3740 , KHR.DATE_LAST_INTERIM_INTEREST_CAL INTEREST_PROCESSED_FLAG
3741 FROM
3742 OKL_K_HEADERS KHR
3743 , OKC_K_HEADERS_B CHR
3744 WHERE
3745 CHR.ID = KHR.ID
3746 AND KHR.ID = cp_chr_id;
3747
3748 --Cursor to query the last accrued due date of the stream.
3749 CURSOR get_last_accrued_due_date(
3750 p_chr_id OKC_K_HEADERS_B.ID%TYPE
3751 , p_stream_purpose OKL_STRM_TYPE_V.STREAM_TYPE_PURPOSE%TYPE) IS
3752 SELECT
3753 MAX(STE.STREAM_ELEMENT_DATE) LAST_STREAM_DUE_DATE
3754 , STY.STYB_PURPOSE_MEANING
3755 FROM
3756 OKL_STRM_ELEMENTS STE
3757 , OKL_STREAMS STM
3758 , OKL_STRM_TYPE_V STY
3759 , OKL_K_HEADERS KHR
3760 , OKC_K_HEADERS_B CHR
3761 WHERE
3762 STM.ID = STE.STM_ID
3763 AND STY.ID = STM.STY_ID
3764 AND KHR.ID = STM.KHR_ID
3765 AND CHR.ID = KHR.ID
3766 AND STE.ACCRUED_YN ='Y'
3767 AND CHR.ID = p_chr_id
3768 AND STY.STREAM_TYPE_PURPOSE = p_stream_purpose
3769 GROUP BY STY.STREAM_TYPE_PURPOSE;
3770
3771 --Cursor to query the last billed due date of the stream.
3772 CURSOR get_last_billed_due_date(
3773 p_chr_id OKC_K_HEADERS_B.ID%TYPE
3774 , p_stream_purpose OKL_STRM_TYPE_V.STREAM_TYPE_PURPOSE%TYPE) IS
3775 SELECT
3776 MAX(STE.STREAM_ELEMENT_DATE) LAST_STREAM_DUE_DATE
3777 , STY.STYB_PURPOSE_MEANING
3778 FROM
3779 OKL_STRM_ELEMENTS STE
3780 , OKL_STREAMS STM
3781 , OKL_STRM_TYPE_V STY
3782 , OKL_K_HEADERS KHR
3783 , OKC_K_HEADERS_B CHR
3784 WHERE
3785 STM.ID = STE.STM_ID
3786 AND STY.ID = STM.STY_ID
3787 AND KHR.ID = STM.KHR_ID
3788 AND CHR.ID = KHR.ID
3789 AND STE.DATE_BILLED IS NOT NULL
3790 AND CHR.ID = p_chr_id
3791 AND STY.STREAM_TYPE_PURPOSE = p_stream_purpose
3792 GROUP BY STY.STREAM_TYPE_PURPOSE;
3793 l_product_id NUMBER;
3794 -- 4766555
3795 l_mass_rebook_flag varchar2(1) := 'N';
3796
3797 BEGIN
3798 IF (G_DEBUG_ENABLED = 'Y') THEN
3799 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
3800 END IF;
3801 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3802 -- Call start_activity to create savepoint, check compatibility
3803 -- and initialize message list
3804 x_return_status := OKL_API.START_ACTIVITY (
3805 l_api_name
3806 ,p_init_msg_list
3807 ,'_PVT'
3808 ,x_return_status);
3809 -- Check if activity started successfully
3810 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3811 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3812 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3813 RAISE OKL_API.G_EXCEPTION_ERROR;
3814 END IF;
3815
3816 -- Obtain the contract details - Book classification, Interest Calculation Basis, Revenue Recognition Method, Interest Processed flag
3817 OKL_K_RATE_PARAMS_PVT.get_product(
3818 p_api_version => p_api_version,
3819 p_init_msg_list => p_init_msg_list,
3820 x_return_status => x_return_status,
3821 x_msg_count => x_msg_count,
3822 x_msg_data => x_msg_data,
3823 p_khr_id => p_chr_id,
3824 x_pdt_parameter_rec => l_pdt_params_rec);
3825
3826 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3827 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3828 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3829 RAISE OKL_API.G_EXCEPTION_ERROR;
3830 END IF;
3831
3832 l_book_class := l_pdt_params_rec.deal_type;
3833 l_interest_calc_basis := l_pdt_params_rec.interest_calculation_basis;
3834 l_rev_recog_method := l_pdt_params_rec.revenue_recognition_method;
3835
3836 OPEN get_lkp_meaning('OKL_BOOK_CLASS',l_book_class);
3837 FETCH get_lkp_meaning INTO l_book_class_meaning;
3838 CLOSE get_lkp_meaning;
3839
3840 OPEN get_prod_param_values('INTEREST_CALCULATION_BASIS',
3841 l_interest_calc_basis);
3842 FETCH get_prod_param_values INTO l_interest_calc_meaning;
3843 CLOSE get_prod_param_values;
3844
3845 /*
3846 OPEN get_prod_param_values('REVENUE_RECOGNITION_METHOD',
3847 l_rev_recog_method);
3848 FETCH get_prod_param_values INTO l_rev_recog_meaning;
3849 CLOSE get_prod_param_values; */
3850
3851 OPEN get_contract_details(p_chr_id);
3852 FETCH get_contract_details INTO l_contract_number, l_interest_proc_date;
3853 CLOSE get_contract_details;
3854
3855 -- 4766555
3856 l_mass_rebook_flag := okl_lla_util_pvt.check_mass_rebook_contract(
3857 p_chr_id => p_chr_id);
3858 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3859 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'check_rebook_allowed:l_mass_rebook_flag=' || l_mass_rebook_flag);
3860 END IF;
3861
3862 -- Check the combinations of Book classification,
3863 -- Revenue Recognition Method Calculation Basis
3864 -- Check for Operating Lease
3865
3866 IF (l_book_class = G_BOOK_CLASS_OP) THEN
3867 IF (l_interest_calc_basis NOT IN
3868 (G_ICB_FIXED, G_ICB_FLOAT_FACTOR, G_ICB_REAMORT )) THEN
3869 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
3870 ,p_msg_name => G_OKL_LLA_REBOOK_INVALID
3871 ,p_token1 => G_BOOK_CLASS
3872 ,p_token1_value => l_book_class_meaning
3873 ,p_token2 => G_INT_CALC_BASIS
3874 ,p_token2_value => l_interest_calc_meaning);
3875 RAISE OKL_API.G_EXCEPTION_ERROR;
3876 END IF;
3877
3878 IF ( l_interest_calc_basis = G_ICB_REAMORT) THEN
3879 -- Donot allow Rebook if interest has been processed
3880 IF ( l_interest_proc_date IS NOT NULL ) THEN
3881 IF (l_mass_rebook_flag = OKL_API.G_TRUE) THEN -- 4766555
3882 NULL;
3883 ELSE
3884 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
3885 ,p_msg_name => G_OKL_LLA_RBK_INT_PROC_INVAL
3886 ,p_token1 => G_COL_NAME
3887 ,p_token1_value => l_contract_number);
3888 RAISE OKL_API.G_EXCEPTION_ERROR;
3889 END IF;
3890 ELSE
3891 -- Get the due date of the Rental stream that was last billed
3892 OPEN get_last_billed_due_date(p_chr_id, G_STRM_RENT);
3893 FETCH get_last_billed_due_date
3894 INTO l_last_billed_due_date, l_stream_name;
3895 CLOSE get_last_billed_due_date;
3896 -- Get the due date of the Rental Accrual stream that was last accrued
3897 /* -- Bug 5000110
3898 OPEN get_last_accrued_due_date(p_chr_id, G_STRM_RENT_ACCRUAL);
3899 FETCH get_last_accrued_due_date
3900 INTO l_last_accrued_due_date, l_stream_name;
3901 CLOSE get_last_accrued_due_date; */
3902
3903 IF ( l_last_billed_due_date IS NOT NULL
3904 AND p_rebook_date <= l_last_billed_due_date) THEN
3905 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
3906 ,p_msg_name => G_OKL_LLA_RBK_DATE_BILL_INVAL
3907 ,p_token1 => G_STREAM
3908 ,p_token1_value => l_stream_name
3909 ,p_token2 => G_DUE_DATE
3910 ,p_token2_value => l_last_billed_due_date);
3911 RAISE OKL_API.G_EXCEPTION_ERROR;
3912 /*ELSIF ( l_last_accrued_due_date IS NOT NULL
3913 AND p_rebook_date <= l_last_accrued_due_date) THEN
3914 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
3915 ,p_msg_name => G_OKL_LLA_RBK_DATE_ACCR_INVAL
3916 ,p_token1 => G_STREAM
3917 ,p_token1_value => l_stream_name
3918 ,p_token2 => G_DUE_DATE
3919 ,p_token2_value => l_last_accrued_due_date);
3920 RAISE OKL_API.G_EXCEPTION_ERROR; */ -- Bug 5000110
3921 END IF; -- end of check for rebook date
3922 END IF; -- end of check for interest rate processed
3923
3924 END IF; -- end of REAMORT check
3925
3926 -- Check for Sales Type Lease
3927 ELSIF (l_book_class = G_BOOK_CLASS_ST) THEN
3928 IF (l_interest_calc_basis NOT IN
3929 (G_ICB_FIXED, G_ICB_FLOAT_FACTOR, G_ICB_REAMORT )) THEN
3930 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
3931 ,p_msg_name => G_OKL_LLA_REBOOK_INVALID
3932 ,p_token1 => G_BOOK_CLASS
3933 ,p_token1_value => l_book_class_meaning
3934 ,p_token2 => G_INT_CALC_BASIS
3935 ,p_token2_value => l_interest_calc_meaning);
3936 RAISE OKL_API.G_EXCEPTION_ERROR;
3937 END IF;
3938 IF ( l_interest_calc_basis = G_ICB_REAMORT) THEN
3939 -- Donot allow Rebook if interest has been processed
3940 IF ( l_interest_proc_date IS NOT NULL ) THEN
3941 IF (l_mass_rebook_flag = OKL_API.G_TRUE) THEN -- 4766555
3942 NULL;
3943 ELSE
3944 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
3945 ,p_msg_name => G_OKL_LLA_RBK_INT_PROC_INVAL
3946 ,p_token1 => G_COL_NAME
3947 ,p_token1_value => l_contract_number);
3948 RAISE OKL_API.G_EXCEPTION_ERROR;
3949 END IF;
3950 ELSE
3951 -- Get the due date of the Rental stream that was last billed
3952 OPEN get_last_billed_due_date(p_chr_id, G_STRM_RENT);
3953 FETCH get_last_billed_due_date
3954 INTO l_last_billed_due_date, l_stream_name;
3955 CLOSE get_last_billed_due_date;
3956 -- Get the due date of the Pre-Tax Income stream that was last accrued
3957 -- Bug 5000110
3958 /*
3959 OPEN get_last_accrued_due_date(p_chr_id, G_STRM_PRE_TAX);
3960 FETCH get_last_accrued_due_date
3961 INTO l_last_accrued_due_date, l_stream_name;
3962 CLOSE get_last_accrued_due_date; */
3963
3964 IF ( l_last_billed_due_date IS NOT NULL AND
3965 p_rebook_date <= l_last_billed_due_date) THEN
3966 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
3967 ,p_msg_name => G_OKL_LLA_RBK_DATE_BILL_INVAL
3968 ,p_token1 => G_STREAM
3969 ,p_token1_value => l_stream_name
3970 ,p_token2 => G_DUE_DATE
3971 ,p_token2_value => l_last_billed_due_date);
3972 RAISE OKL_API.G_EXCEPTION_ERROR;
3973 /*ELSIF ( l_last_accrued_due_date IS NOT NULL AND
3974 p_rebook_date <= l_last_accrued_due_date) THEN
3975 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
3976 ,p_msg_name => G_OKL_LLA_RBK_DATE_ACCR_INVAL
3977 ,p_token1 => G_STREAM
3978 ,p_token1_value => l_stream_name
3979 ,p_token2 => G_DUE_DATE
3980 ,p_token2_value => l_last_accrued_due_date);
3981 RAISE OKL_API.G_EXCEPTION_ERROR; */ -- Bug 5000110
3982 END IF; -- end of check for rebook date
3983 END IF; -- end of check for interest rate processed
3984
3985 END IF; -- end of REAMORT check
3986
3987 -- Check for Direct Finance Lease
3988 ELSIF (l_book_class = G_BOOK_CLASS_DF) THEN
3989 IF (l_interest_calc_basis NOT IN
3990 (G_ICB_FIXED, G_ICB_FLOAT_FACTOR, G_ICB_REAMORT )) THEN
3991 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
3992 ,p_msg_name => G_OKL_LLA_REBOOK_INVALID
3993 ,p_token1 => G_BOOK_CLASS
3994 ,p_token1_value => l_book_class_meaning
3995 ,p_token2 => G_INT_CALC_BASIS
3996 ,p_token2_value => l_interest_calc_meaning);
3997 RAISE OKL_API.G_EXCEPTION_ERROR;
3998 END IF;
3999
4000 IF ( l_interest_calc_basis = G_ICB_REAMORT) THEN
4001 -- Donot allow Rebook if interest has been processed
4002 IF ( l_interest_proc_date IS NOT NULL ) THEN
4003 IF (l_mass_rebook_flag = OKL_API.G_TRUE) THEN -- 4766555
4004 NULL;
4005 ELSE
4006 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
4007 ,p_msg_name => G_OKL_LLA_RBK_INT_PROC_INVAL
4008 ,p_token1 => G_COL_NAME
4009 ,p_token1_value => l_contract_number);
4010 RAISE OKL_API.G_EXCEPTION_ERROR;
4011 END IF;
4012 ELSE
4013 -- Get the due date of the rent stream that was last billed
4014 OPEN get_last_billed_due_date(p_chr_id, G_STRM_RENT);
4015 FETCH get_last_billed_due_date
4016 INTO l_last_billed_due_date, l_stream_name;
4017 CLOSE get_last_billed_due_date;
4018 -- Get the due date of the Pre-Tax Income stream that was last accrued
4019 -- Bug 5000110
4020 /*
4021 OPEN get_last_accrued_due_date(p_chr_id, G_STRM_PRE_TAX);
4022 FETCH get_last_accrued_due_date
4023 INTO l_last_accrued_due_date, l_stream_name;
4024 CLOSE get_last_accrued_due_date; */
4025
4026 IF ( l_last_billed_due_date IS NOT NULL AND
4027 p_rebook_date <= l_last_billed_due_date) THEN
4028 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
4029 ,p_msg_name => G_OKL_LLA_RBK_DATE_BILL_INVAL
4030 ,p_token1 => G_STREAM
4031 ,p_token1_value => l_stream_name
4032 ,p_token2 => G_DUE_DATE
4033 ,p_token2_value => l_last_billed_due_date);
4034 RAISE OKL_API.G_EXCEPTION_ERROR;
4035 /*ELSIF ( l_last_accrued_due_date IS NOT NULL AND
4036 p_rebook_date <= l_last_accrued_due_date) THEN
4037 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
4038 ,p_msg_name => G_OKL_LLA_RBK_DATE_ACCR_INVAL
4039 ,p_token1 => G_STREAM
4040 ,p_token1_value => l_stream_name
4041 ,p_token2 => G_DUE_DATE
4042 ,p_token2_value => l_last_accrued_due_date);
4043 RAISE OKL_API.G_EXCEPTION_ERROR; */ -- Bug 5000110
4044 END IF; -- end of check for rebook date
4045 END IF; -- end of check for interest rate processed
4046
4047 END IF; -- end of REAMORT check
4048
4049 -- Check for Loan
4050 ELSIF (l_book_class = G_BOOK_CLASS_LOAN) THEN
4051 IF (l_interest_calc_basis NOT IN
4052 (G_ICB_FIXED, G_ICB_FLOAT, G_ICB_REAMORT, G_ICB_CATCHUP_CLEANUP)) THEN
4053 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
4054 ,p_msg_name => G_OKL_LLA_REBOOK_INVALID
4055 ,p_token1 => G_BOOK_CLASS
4056 ,p_token1_value => l_book_class_meaning
4057 ,p_token2 => G_INT_CALC_BASIS
4058 ,p_token2_value => l_interest_calc_meaning);
4059 RAISE OKL_API.G_EXCEPTION_ERROR;
4060 END IF;
4061
4062 IF ( l_interest_calc_basis = G_ICB_REAMORT) THEN
4063 IF ( l_interest_proc_date IS NOT NULL ) THEN
4064 IF (l_mass_rebook_flag = OKL_API.G_TRUE) THEN -- 4766555
4065 NULL;
4066 ELSE
4067 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
4068 ,p_msg_name => G_OKL_LLA_RBK_INT_PROC_INVAL
4069 ,p_token1 => G_COL_NAME
4070 ,p_token1_value => l_contract_number);
4071 RAISE OKL_API.G_EXCEPTION_ERROR;
4072 END IF;
4073 ELSE
4074 -- Get the due date of the rent stream that was last billed
4075 OPEN get_last_billed_due_date(p_chr_id, G_STRM_RENT);
4076 FETCH get_last_billed_due_date
4077 INTO l_last_billed_due_date, l_stream_name;
4078 CLOSE get_last_billed_due_date;
4079 -- Get the due date of the Interest Income stream that was last accrued
4080 -- Bug 5000110
4081 /*
4082 OPEN get_last_accrued_due_date(p_chr_id, G_STRM_INT_INCOME);
4083 FETCH get_last_accrued_due_date
4084 INTO l_last_accrued_due_date, l_stream_name;
4085 CLOSE get_last_accrued_due_date; */
4086
4087 IF ( l_last_billed_due_date IS NOT NULL
4088 AND p_rebook_date <= l_last_billed_due_date) THEN
4089 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
4090 ,p_msg_name => G_OKL_LLA_RBK_DATE_BILL_INVAL
4091 ,p_token1 => G_STREAM
4092 ,p_token1_value => l_stream_name
4093 ,p_token2 => G_DUE_DATE
4094 ,p_token2_value => l_last_billed_due_date);
4095 RAISE OKL_API.G_EXCEPTION_ERROR;
4096 /*ELSIF ( l_last_accrued_due_date IS NOT NULL AND
4097 p_rebook_date <= l_last_accrued_due_date) THEN
4098 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
4099 ,p_msg_name => G_OKL_LLA_RBK_DATE_ACCR_INVAL
4100 ,p_token1 => G_STREAM
4101 ,p_token1_value => l_stream_name
4102 ,p_token2 => G_DUE_DATE
4103 ,p_token2_value => l_last_accrued_due_date);
4104 RAISE OKL_API.G_EXCEPTION_ERROR; */ -- Bug 5000110
4105 END IF; -- end of check for rebook date
4106 END IF; -- end of check for interest rate processed
4107
4108 END IF; -- end of REAMORT check
4109
4110 IF ( l_interest_calc_basis = G_ICB_FLOAT) THEN
4111 -- Check if the Revenue Recognition Method is Estimated and Actual
4112 IF ( l_rev_recog_method = G_RRM_EST_ACTUAL) THEN
4113 -- Get the due date of the Variable Interest Income stream that was last billed
4114 OPEN get_last_billed_due_date(p_chr_id, G_STRM_VAR_INT_INCOME);
4115 FETCH get_last_billed_due_date
4116 INTO l_last_billed_due_date, l_stream_name;
4117 CLOSE get_last_billed_due_date;
4118 -- Get the due date of the Variable Interest Income stream that was last acrrued
4119 -- Bug 5000110
4120 /*
4121 OPEN get_last_accrued_due_date(p_chr_id, G_STRM_VAR_INT_INCOME);
4122 FETCH get_last_accrued_due_date
4123 INTO l_last_accrued_due_date, l_stream_name;
4124 CLOSE get_last_accrued_due_date; */
4125
4126 IF ( l_last_billed_due_date IS NOT NULL AND
4127 p_rebook_date <= l_last_billed_due_date) THEN
4128 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
4129 ,p_msg_name => G_OKL_LLA_RBK_DATE_BILL_INVAL
4130 ,p_token1 => G_STREAM
4131 ,p_token1_value => l_stream_name
4132 ,p_token2 => G_DUE_DATE
4133 ,p_token2_value => l_last_billed_due_date);
4134 RAISE OKL_API.G_EXCEPTION_ERROR;
4135 /*ELSIF ( l_last_accrued_due_date IS NOT NULL AND
4136 p_rebook_date <= l_last_accrued_due_date) THEN
4137 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
4138 ,p_msg_name => G_OKL_LLA_RBK_DATE_ACCR_INVAL
4139 ,p_token1 => G_STREAM
4140 ,p_token1_value => l_stream_name
4141 ,p_token2 => G_DUE_DATE
4142 ,p_token2_value => l_last_accrued_due_date);
4143 RAISE OKL_API.G_EXCEPTION_ERROR; */ -- Bug 5000110
4144 END IF; -- end of check for rebook date
4145 -- Check if the Revenue Recognition Method is Actual
4146 ELSIF ( l_rev_recog_method = G_RRM_ACTUAL) THEN
4147 -- Get the due date of the Variable Loan Payment stream that was last billed
4148 OPEN get_last_billed_due_date(p_chr_id, G_STRM_LOAN_PAYMENT);
4149 FETCH get_last_billed_due_date
4150 INTO l_last_billed_due_date, l_stream_name;
4151 CLOSE get_last_billed_due_date;
4152 -- Get the due date of the Interest Income stream that was last accrued
4153 -- Bug 5000110
4154 /*
4155 OPEN get_last_accrued_due_date(p_chr_id, G_STRM_INT_INCOME);
4156 FETCH get_last_accrued_due_date
4157 INTO l_last_accrued_due_date, l_stream_name;
4158 CLOSE get_last_accrued_due_date; */
4159
4160 IF ( l_last_billed_due_date IS NOT NULL AND
4161 p_rebook_date <= l_last_billed_due_date) THEN
4162 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
4163 ,p_msg_name => G_OKL_LLA_RBK_DATE_BILL_INVAL
4164 ,p_token1 => G_STREAM
4165 ,p_token1_value => l_stream_name
4166 ,p_token2 => G_DUE_DATE
4167 ,p_token2_value => l_last_billed_due_date);
4168 RAISE OKL_API.G_EXCEPTION_ERROR;
4169 /*ELSIF ( l_last_accrued_due_date IS NOT NULL AND
4170 p_rebook_date <= l_last_accrued_due_date) THEN
4171 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
4172 ,p_msg_name => G_OKL_LLA_RBK_DATE_ACCR_INVAL
4173 ,p_token1 => G_STREAM
4174 ,p_token1_value => l_stream_name
4175 ,p_token2 => G_DUE_DATE
4176 ,p_token2_value => l_last_accrued_due_date);
4177 RAISE OKL_API.G_EXCEPTION_ERROR; */ -- Bug 5000110
4178 END IF; -- end of check for rebook date
4179 END IF; -- end of check for Revenue Recognition Method
4180 END IF; -- end of check for Interest Calculation Basis=FLOAT
4181
4182 -- Check for Revolving Loans
4183 ELSIF (l_book_class = G_BOOK_CLASS_REVLOAN) THEN
4184 IF (l_interest_calc_basis <> G_ICB_FLOAT) THEN
4185 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
4186 ,p_msg_name => G_OKL_LLA_REBOOK_INVALID
4187 ,p_token1 => G_BOOK_CLASS
4188 ,p_token1_value => l_book_class_meaning
4189 ,p_token2 => G_INT_CALC_BASIS
4190 ,p_token2_value => l_interest_calc_meaning);
4191 RAISE OKL_API.G_EXCEPTION_ERROR;
4192 ELSE
4193 -- Check if the Revenue Recognition Method is Estimated and Actual
4194 IF ( l_rev_recog_method = G_RRM_EST_ACTUAL) THEN
4195 -- Get the due date of the Variable Interest Income stream that was last billed
4196 OPEN get_last_billed_due_date(p_chr_id, G_STRM_VAR_INT_INCOME);
4197 FETCH get_last_billed_due_date
4198 INTO l_last_billed_due_date, l_stream_name;
4199 CLOSE get_last_billed_due_date;
4200 -- Get the due date of the Variable Interest Income stream that was last accrued
4201 -- Bug 5000110
4202 /*
4203 OPEN get_last_accrued_due_date(p_chr_id, G_STRM_VAR_INT_INCOME);
4204 FETCH get_last_accrued_due_date
4205 INTO l_last_accrued_due_date, l_stream_name;
4206 CLOSE get_last_accrued_due_date; */
4207
4208 IF ( l_last_billed_due_date IS NOT NULL AND
4209 p_rebook_date <= l_last_billed_due_date) THEN
4210 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
4211 ,p_msg_name => G_OKL_LLA_RBK_DATE_BILL_INVAL
4212 ,p_token1 => G_STREAM
4213 ,p_token1_value => l_stream_name
4214 ,p_token2 => G_DUE_DATE
4215 ,p_token2_value => l_last_billed_due_date);
4216 RAISE OKL_API.G_EXCEPTION_ERROR;
4217 /*ELSIF ( l_last_accrued_due_date IS NOT NULL AND
4218 p_rebook_date <= l_last_accrued_due_date) THEN
4219 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
4220 ,p_msg_name => G_OKL_LLA_RBK_DATE_ACCR_INVAL
4221 ,p_token1 => G_STREAM
4222 ,p_token1_value => l_stream_name
4223 ,p_token2 => G_DUE_DATE
4224 ,p_token2_value => l_last_accrued_due_date);
4225 RAISE OKL_API.G_EXCEPTION_ERROR; */ -- Bug 5000110
4226 END IF; -- end of check for rebook date
4227 -- Check if the Revenue Recognition Method is Actual
4228 ELSIF ( l_rev_recog_method = G_RRM_ACTUAL) THEN
4229 -- Get the due date of the Variable Loan Payment stream that was last billed
4230 OPEN get_last_billed_due_date(p_chr_id, G_STRM_LOAN_PAYMENT);
4231 FETCH get_last_billed_due_date
4232 INTO l_last_billed_due_date, l_stream_name;
4233 CLOSE get_last_billed_due_date;
4234 -- Get the due date of the Interest Income stream that was last accrued
4235 -- Bug 5000110
4236 /*
4237 OPEN get_last_accrued_due_date(p_chr_id, G_STRM_INT_INCOME);
4238 FETCH get_last_accrued_due_date
4239 INTO l_last_accrued_due_date, l_stream_name;
4240 CLOSE get_last_accrued_due_date; */
4241
4242 IF ( l_last_billed_due_date IS NOT NULL AND
4243 p_rebook_date <= l_last_billed_due_date) THEN
4244 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
4245 ,p_msg_name => G_OKL_LLA_RBK_DATE_BILL_INVAL
4246 ,p_token1 => G_STREAM
4247 ,p_token1_value => l_stream_name
4248 ,p_token2 => G_DUE_DATE
4249 ,p_token2_value => l_last_billed_due_date);
4250 RAISE OKL_API.G_EXCEPTION_ERROR;
4251 /*ELSIF ( l_last_accrued_due_date IS NOT NULL AND
4252 p_rebook_date <= l_last_accrued_due_date) THEN
4253 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
4254 ,p_msg_name => G_OKL_LLA_RBK_DATE_ACCR_INVAL
4255 ,p_token1 => G_STREAM
4256 ,p_token1_value => l_stream_name
4257 ,p_token2 => G_DUE_DATE
4258 ,p_token2_value => l_last_accrued_due_date);
4259 RAISE OKL_API.G_EXCEPTION_ERROR; */ -- Bug 5000110
4260 END IF; -- end of check for rebook date
4261 END IF; -- end of check for Revenue Recognition Method
4262 END IF; -- end of check for Interest Calculation Basis=FLOAT
4263 /*-- Raise an error if the book classification is not valid.
4264 ELSE
4265 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR; */
4266 END IF;
4267
4268 OKL_API.END_ACTIVITY (x_msg_count,
4269 x_msg_data );
4270
4271 EXCEPTION
4272 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4273 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4274 l_api_name,
4275 G_PKG_NAME,
4276 'OKL_API.G_RET_STS_ERROR',
4277 x_msg_count,
4278 x_msg_data,
4279 '_PVT');
4280 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4281 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4282 l_api_name,
4283 G_PKG_NAME,
4284 'OKL_API.G_RET_STS_UNEXP_ERROR',
4285 x_msg_count,
4286 x_msg_data,
4287 '_PVT');
4288 WHEN OTHERS THEN
4289 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4290 l_api_name,
4291 G_PKG_NAME,
4292 'OTHERS',
4293 x_msg_count,
4294 x_msg_data,
4295 '_PVT');
4296 END; -- end of procedure Check_Rebook_Allowed
4297
4298 -- smadhava Bug#4542290 - 22-Aug-2005 - Added - End
4299
4300 PROCEDURE SYNC_RATE_PARAMS(
4301 p_orig_contract_id IN NUMBER,
4302 p_new_contract_id IN NUMBER) IS
4303 TYPE l_kkhr_id_type is table of okl_k_rate_params.khr_id%TYPE index by binary_integer;
4304 TYPE l_kparameter_type_code_type is table of okl_k_rate_params.parameter_type_code%TYPE index by binary_integer;
4305 TYPE l_keffective_from_date_type is table of okl_k_rate_params.effective_from_date%TYPE index by binary_integer;
4306 TYPE l_keffective_to_date_type is table of okl_k_rate_params.effective_to_date%TYPE index by binary_integer;
4307 TYPE l_kinterest_index_id_type is table of okl_k_rate_params.interest_index_id%TYPE index by binary_integer;
4308 TYPE l_kbase_rate_type is table of okl_k_rate_params.base_rate%TYPE index by binary_integer;
4309 TYPE l_kinterest_start_date_type is table of okl_k_rate_params.interest_start_date%TYPE index by binary_integer;
4310 TYPE l_kadder_rate_type is table of okl_k_rate_params.adder_rate%TYPE index by binary_integer;
4311 TYPE l_kmaximum_rate_type is table of okl_k_rate_params.maximum_rate%TYPE index by binary_integer;
4312 TYPE l_kminimum_rate_type is table of okl_k_rate_params.minimum_rate%TYPE index by binary_integer;
4313 TYPE l_kprincipal_basis_code_type is table of okl_k_rate_params.principal_basis_code%TYPE index by binary_integer;
4314 TYPE l_kdays_in_a_month_code_type is table of okl_k_rate_params.days_in_a_month_code%TYPE index by binary_integer;
4315 TYPE l_kdays_in_a_year_code_type is table of okl_k_rate_params.days_in_a_year_code%TYPE index by binary_integer;
4316 TYPE l_kinterest_basis_code_type is table of okl_k_rate_params.interest_basis_code%TYPE index by binary_integer;
4317 TYPE l_krate_delay_code_type is table of okl_k_rate_params.rate_delay_code%TYPE index by binary_integer;
4318 TYPE l_krate_delay_frequency_type is table of okl_k_rate_params.rate_delay_frequency%TYPE index by binary_integer;
4319 TYPE l_kcompounding_frequ_code_type is table of okl_k_rate_params.compounding_frequency_code%TYPE index by binary_integer;
4320 TYPE l_kcalculation_formula_id_type is table of okl_k_rate_params.calculation_formula_id%TYPE index by binary_integer;
4321 TYPE l_kcatchup_basis_code_type is table of okl_k_rate_params.catchup_basis_code%TYPE index by binary_integer;
4322 TYPE l_kcatchup_start_date_type is table of okl_k_rate_params.catchup_start_date%TYPE index by binary_integer;
4323 TYPE l_kcatchup_settlemen_code_type is table of okl_k_rate_params.catchup_settlement_code%TYPE index by binary_integer;
4324 TYPE l_krate_change_start_date_type is table of okl_k_rate_params.rate_change_start_date%TYPE index by binary_integer;
4325 TYPE l_krate_change_frequ_code_type is table of okl_k_rate_params.rate_change_frequency_code%TYPE index by binary_integer;
4326 TYPE l_krate_change_value_type is table of okl_k_rate_params.rate_change_value%TYPE index by binary_integer;
4327 TYPE l_kconversion_option_code_type is table of okl_k_rate_params.conversion_option_code%TYPE index by binary_integer;
4328 TYPE l_knext_conversion_date_type is table of okl_k_rate_params.next_conversion_date%TYPE index by binary_integer;
4329 TYPE l_kconversion_type_code_type is table of okl_k_rate_params.conversion_type_code%TYPE index by binary_integer;
4330 TYPE l_kattribute_category_type is table of okl_k_rate_params.attribute_category%TYPE index by binary_integer;
4331 TYPE l_kattribute1_type is table of okl_k_rate_params.attribute1%TYPE index by binary_integer;
4332 TYPE l_kattribute2_type is table of okl_k_rate_params.attribute2%TYPE index by binary_integer;
4333 TYPE l_kattribute3_type is table of okl_k_rate_params.attribute3%TYPE index by binary_integer;
4334 TYPE l_kattribute4_type is table of okl_k_rate_params.attribute4%TYPE index by binary_integer;
4335 TYPE l_kattribute5_type is table of okl_k_rate_params.attribute5%TYPE index by binary_integer;
4336 TYPE l_kattribute6_type is table of okl_k_rate_params.attribute6%TYPE index by binary_integer;
4337 TYPE l_kattribute7_type is table of okl_k_rate_params.attribute7%TYPE index by binary_integer;
4338 TYPE l_kattribute8_type is table of okl_k_rate_params.attribute8%TYPE index by binary_integer;
4339 TYPE l_kattribute9_type is table of okl_k_rate_params.attribute9%TYPE index by binary_integer;
4340 TYPE l_kattribute10_type is table of okl_k_rate_params.attribute10%TYPE index by binary_integer;
4341 TYPE l_kattribute11_type is table of okl_k_rate_params.attribute11%TYPE index by binary_integer;
4342 TYPE l_kattribute12_type is table of okl_k_rate_params.attribute12%TYPE index by binary_integer;
4343 TYPE l_kattribute13_type is table of okl_k_rate_params.attribute13%TYPE index by binary_integer;
4344 TYPE l_kattribute14_type is table of okl_k_rate_params.attribute14%TYPE index by binary_integer;
4345 TYPE l_kattribute15_type is table of okl_k_rate_params.attribute15%TYPE index by binary_integer;
4346 TYPE l_kcatchup_frequency_code_type is table of okl_k_rate_params.catchup_frequency_code%TYPE index by binary_integer;
4347
4348 l_kkhr_id_tab l_kkhr_id_type;
4349 l_kparameter_type_code_tab l_kparameter_type_code_type;
4350 l_keffective_from_date_tab l_keffective_from_date_type;
4351 l_keffective_to_date_tab l_keffective_to_date_type;
4352 l_kinterest_index_id_tab l_kinterest_index_id_type;
4353 l_kbase_rate_tab l_kbase_rate_type;
4354 l_kinterest_start_date_tab l_kinterest_start_date_type;
4355 l_kadder_rate_tab l_kadder_rate_type;
4356 l_kmaximum_rate_tab l_kmaximum_rate_type;
4357 l_kminimum_rate_tab l_kminimum_rate_type;
4358 l_kprincipal_basis_code_tab l_kprincipal_basis_code_type;
4359 l_kdays_in_a_month_code_tab l_kdays_in_a_month_code_type;
4360 l_kdays_in_a_year_code_tab l_kdays_in_a_year_code_type;
4361 l_kinterest_basis_code_tab l_kinterest_basis_code_type;
4362 l_krate_delay_code_tab l_krate_delay_code_type;
4363 l_krate_delay_frequency_tab l_krate_delay_frequency_type;
4364 l_kcompounding_frequ_code_tab l_kcompounding_frequ_code_type;
4365 l_kcalculation_formula_id_tab l_kcalculation_formula_id_type;
4366 l_kcatchup_basis_code_tab l_kcatchup_basis_code_type;
4367 l_kcatchup_start_date_tab l_kcatchup_start_date_type;
4368 l_kcatchup_settlemen_code_tab l_kcatchup_settlemen_code_type;
4369 l_krate_change_start_date_tab l_krate_change_start_date_type;
4370 l_krate_change_frequ_code_tab l_krate_change_frequ_code_type;
4371 l_krate_change_value_tab l_krate_change_value_type;
4372 l_kconversion_option_code_tab l_kconversion_option_code_type;
4373 l_knext_conversion_date_tab l_knext_conversion_date_type;
4374 l_kconversion_type_code_tab l_kconversion_type_code_type;
4375 l_kattribute_category_tab l_kattribute_category_type;
4376 l_kattribute1_tab l_kattribute1_type;
4377 l_kattribute2_tab l_kattribute2_type;
4378 l_kattribute3_tab l_kattribute3_type;
4379 l_kattribute4_tab l_kattribute4_type;
4380 l_kattribute5_tab l_kattribute5_type;
4381 l_kattribute6_tab l_kattribute6_type;
4382 l_kattribute7_tab l_kattribute7_type;
4383 l_kattribute8_tab l_kattribute8_type;
4384 l_kattribute9_tab l_kattribute9_type;
4385 l_kattribute10_tab l_kattribute10_type;
4386 l_kattribute11_tab l_kattribute11_type;
4387 l_kattribute12_tab l_kattribute12_type;
4388 l_kattribute13_tab l_kattribute13_type;
4389 l_kattribute14_tab l_kattribute14_type;
4390 l_kattribute15_tab l_kattribute15_type;
4391 l_kcatchup_frequency_code_tab l_kcatchup_frequency_code_type;
4392
4393 l_kkhr_id_tab2 l_kkhr_id_type;
4394 l_kparameter_type_code_tab2 l_kparameter_type_code_type;
4395 l_keffective_from_date_tab2 l_keffective_from_date_type;
4396 l_keffective_to_date_tab2 l_keffective_to_date_type;
4397 l_kinterest_index_id_tab2 l_kinterest_index_id_type;
4398 l_kbase_rate_tab2 l_kbase_rate_type;
4399 l_kinterest_start_date_tab2 l_kinterest_start_date_type;
4400 l_kadder_rate_tab2 l_kadder_rate_type;
4401 l_kmaximum_rate_tab2 l_kmaximum_rate_type;
4402 l_kminimum_rate_tab2 l_kminimum_rate_type;
4403 l_kprincipal_basis_code_tab2 l_kprincipal_basis_code_type;
4404 l_kdays_in_a_month_code_tab2 l_kdays_in_a_month_code_type;
4405 l_kdays_in_a_year_code_tab2 l_kdays_in_a_year_code_type;
4406 l_kinterest_basis_code_tab2 l_kinterest_basis_code_type;
4407 l_krate_delay_code_tab2 l_krate_delay_code_type;
4408 l_krate_delay_frequency_tab2 l_krate_delay_frequency_type;
4409 l_kcompounding_frequ_code_tab2 l_kcompounding_frequ_code_type;
4410 l_kcalculation_formula_id_tab2 l_kcalculation_formula_id_type;
4411 l_kcatchup_basis_code_tab2 l_kcatchup_basis_code_type;
4412 l_kcatchup_start_date_tab2 l_kcatchup_start_date_type;
4413 l_kcatchup_settlemen_code_tab2 l_kcatchup_settlemen_code_type;
4414 l_krate_change_start_date_tab2 l_krate_change_start_date_type;
4415 l_krate_change_frequ_code_tab2 l_krate_change_frequ_code_type;
4416 l_krate_change_value_tab2 l_krate_change_value_type;
4417 l_kconversion_option_code_tab2 l_kconversion_option_code_type;
4418 l_knext_conversion_date_tab2 l_knext_conversion_date_type;
4419 l_kconversion_type_code_tab2 l_kconversion_type_code_type;
4420 l_kattribute_category_tab2 l_kattribute_category_type;
4421 l_kattribute1_tab2 l_kattribute1_type;
4422 l_kattribute2_tab2 l_kattribute2_type;
4423 l_kattribute3_tab2 l_kattribute3_type;
4424 l_kattribute4_tab2 l_kattribute4_type;
4425 l_kattribute5_tab2 l_kattribute5_type;
4426 l_kattribute6_tab2 l_kattribute6_type;
4427 l_kattribute7_tab2 l_kattribute7_type;
4428 l_kattribute8_tab2 l_kattribute8_type;
4429 l_kattribute9_tab2 l_kattribute9_type;
4430 l_kattribute10_tab2 l_kattribute10_type;
4431 l_kattribute11_tab2 l_kattribute11_type;
4432 l_kattribute12_tab2 l_kattribute12_type;
4433 l_kattribute13_tab2 l_kattribute13_type;
4434 l_kattribute14_tab2 l_kattribute14_type;
4435 l_kattribute15_tab2 l_kattribute15_type;
4436 l_kcatchup_frequency_code_tab2 l_kcatchup_frequency_code_type;
4437
4438 l_k_rate_params_counter number := 1;
4439
4440 --CURSOR C30(p_id NUMBER, p_date DATE) IS
4441 CURSOR C30(p_id NUMBER) IS
4442 SELECT
4443 a.khr_id, a.parameter_type_code, a.effective_from_date,
4444 a.effective_to_date, a.interest_index_id, a.base_rate,
4445 a.interest_start_date, a.adder_rate, a.maximum_rate,
4446 a.minimum_rate, a.principal_basis_code, a.days_in_a_month_code,
4447 a.days_in_a_year_code, a.interest_basis_code, a.rate_delay_code,
4448 a.rate_delay_frequency, a.compounding_frequency_code, a.calculation_formula_id,
4449 a.catchup_basis_code, a.catchup_start_date, a.catchup_settlement_code,
4450 a.rate_change_start_date, a.rate_change_frequency_code, a.rate_change_value,
4451 a.conversion_option_code, a.next_conversion_date, a.conversion_type_code,
4452 a.attribute_category, a.attribute1, a.attribute2,
4453 a.attribute3, a.attribute4, a.attribute5,
4454 a.attribute6, a.attribute7, a.attribute8,
4455 a.attribute9, a.attribute10, a.attribute11,
4456 a.attribute12, a.attribute13, a.attribute14,
4457 a.attribute15, a.catchup_frequency_code
4458 FROM OKL_K_RATE_PARAMS a
4459 WHERE a.khr_id = p_id;
4460 --AND (effective_to_date is NULL OR
4461 -- effective_to_date = p_date);
4462
4463 /*CURSOR max_effective_to_date_csr(p_id NUMBER) IS
4464 SELECT max(effective_to_date) effective_to_date
4465 FROM OKL_K_RATE_PARAMS
4466 WHERE KHR_ID = p_id;
4467
4468 l_max_effective_to_date DATE;*/
4469 BEGIN
4470 IF (G_DEBUG_ENABLED = 'Y') THEN
4471 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
4472 END IF;
4473 DELETE OKL_K_RATE_PARAMS
4474 WHERE KHR_ID = p_new_contract_id;
4475 --AND effective_to_date is NULL;
4476
4477 /*FOR r IN max_effective_to_date_csr(p_orig_contract_id)
4478 LOOP
4479 l_max_effective_to_date := r.effective_to_date;
4480 END LOOP;*/
4481
4482 --OPEN C30(p_orig_contract_id, l_max_effective_to_date);
4483 OPEN C30(p_orig_contract_id);
4484 LOOP
4485 FETCH C30 BULK COLLECT INTO
4486 l_kkhr_id_tab2, l_kparameter_type_code_tab2, l_keffective_from_date_tab2,
4487 l_keffective_to_date_tab2, l_kinterest_index_id_tab2, l_kbase_rate_tab2,
4488 l_kinterest_start_date_tab2, l_kadder_rate_tab2, l_kmaximum_rate_tab2,
4489 l_kminimum_rate_tab2, l_kprincipal_basis_code_tab2, l_kdays_in_a_month_code_tab2,
4490 l_kdays_in_a_year_code_tab2, l_kinterest_basis_code_tab2, l_krate_delay_code_tab2,
4491 l_krate_delay_frequency_tab2, l_kcompounding_frequ_code_tab2, l_kcalculation_formula_id_tab2,
4492 l_kcatchup_basis_code_tab2, l_kcatchup_start_date_tab2, l_kcatchup_settlemen_code_tab2,
4493 l_krate_change_start_date_tab2, l_krate_change_frequ_code_tab2, l_krate_change_value_tab2,
4494 l_kconversion_option_code_tab2, l_knext_conversion_date_tab2, l_kconversion_type_code_tab2,
4495 l_kattribute_category_tab2, l_kattribute1_tab2, l_kattribute2_tab2,
4496 l_kattribute3_tab2, l_kattribute4_tab2, l_kattribute5_tab2,
4497 l_kattribute6_tab2, l_kattribute7_tab2, l_kattribute8_tab2,
4498 l_kattribute9_tab2, l_kattribute10_tab2, l_kattribute11_tab2,
4499 l_kattribute12_tab2, l_kattribute13_tab2, l_kattribute14_tab2,
4500 l_kattribute15_tab2, l_kcatchup_frequency_code_tab2
4501 LIMIT G_BULK_SIZE;
4502 EXIT WHEN l_kkhr_id_tab2.COUNT = 0;
4503
4504 FOR i IN l_kkhr_id_tab2.FIRST..l_kkhr_id_tab2.LAST
4505 LOOP
4506 l_kkhr_id_tab(l_k_rate_params_counter) := l_kkhr_id_tab2(i);
4507 l_kkhr_id_tab(l_k_rate_params_counter) := p_new_contract_id;
4508
4509 l_kparameter_type_code_tab(l_k_rate_params_counter) := l_kparameter_type_code_tab2(i);
4510 l_keffective_from_date_tab(l_k_rate_params_counter) := l_keffective_from_date_tab2(i);
4511 l_keffective_to_date_tab(l_k_rate_params_counter) := l_keffective_to_date_tab2(i);
4512 l_kinterest_index_id_tab(l_k_rate_params_counter) := l_kinterest_index_id_tab2(i);
4513 l_kbase_rate_tab(l_k_rate_params_counter) := l_kbase_rate_tab2(i);
4514 l_kinterest_start_date_tab(l_k_rate_params_counter) := l_kinterest_start_date_tab2(i);
4515
4516 l_kadder_rate_tab(l_k_rate_params_counter) := l_kadder_rate_tab2(i);
4517 l_kmaximum_rate_tab(l_k_rate_params_counter) := l_kmaximum_rate_tab2(i);
4518 l_kminimum_rate_tab(l_k_rate_params_counter) := l_kminimum_rate_tab2(i);
4519 l_kprincipal_basis_code_tab(l_k_rate_params_counter) := l_kprincipal_basis_code_tab2(i);
4520 l_kdays_in_a_month_code_tab(l_k_rate_params_counter) := l_kdays_in_a_month_code_tab2(i);
4521 l_kdays_in_a_year_code_tab(l_k_rate_params_counter) := l_kdays_in_a_year_code_tab2(i);
4522 l_kinterest_basis_code_tab(l_k_rate_params_counter) := l_kinterest_basis_code_tab2(i);
4523 l_krate_delay_code_tab(l_k_rate_params_counter) := l_krate_delay_code_tab2(i);
4524 l_krate_delay_frequency_tab(l_k_rate_params_counter) := l_krate_delay_frequency_tab2(i);
4525 l_kcompounding_frequ_code_tab(l_k_rate_params_counter) := l_kcompounding_frequ_code_tab2(i);
4526 l_kcalculation_formula_id_tab(l_k_rate_params_counter) := l_kcalculation_formula_id_tab2(i);
4527 l_kcatchup_basis_code_tab(l_k_rate_params_counter) := l_kcatchup_basis_code_tab2(i);
4528 l_kcatchup_start_date_tab(l_k_rate_params_counter) := l_kcatchup_start_date_tab2(i);
4529
4530 l_kcatchup_settlemen_code_tab(l_k_rate_params_counter) := l_kcatchup_settlemen_code_tab2(i);
4531 l_krate_change_start_date_tab(l_k_rate_params_counter) := l_krate_change_start_date_tab2(i);
4532
4533 l_krate_change_frequ_code_tab(l_k_rate_params_counter) := l_krate_change_frequ_code_tab2(i);
4534 l_krate_change_value_tab(l_k_rate_params_counter) := l_krate_change_value_tab2(i);
4535 l_kconversion_option_code_tab(l_k_rate_params_counter) := l_kconversion_option_code_tab2(i);
4536 l_knext_conversion_date_tab(l_k_rate_params_counter) := l_knext_conversion_date_tab2(i);
4537 l_kconversion_type_code_tab(l_k_rate_params_counter) := l_kconversion_type_code_tab2(i);
4538 l_kattribute_category_tab(l_k_rate_params_counter) := l_kattribute_category_tab2(i);
4539 l_kattribute1_tab(l_k_rate_params_counter) := l_kattribute1_tab2(i);
4540 l_kattribute2_tab(l_k_rate_params_counter) := l_kattribute2_tab2(i);
4541 l_kattribute3_tab(l_k_rate_params_counter) := l_kattribute3_tab2(i);
4542 l_kattribute4_tab(l_k_rate_params_counter) := l_kattribute4_tab2(i);
4543 l_kattribute5_tab(l_k_rate_params_counter) := l_kattribute5_tab2(i);
4544 l_kattribute6_tab(l_k_rate_params_counter) := l_kattribute6_tab2(i);
4545 l_kattribute7_tab(l_k_rate_params_counter) := l_kattribute7_tab2(i);
4546 l_kattribute8_tab(l_k_rate_params_counter) := l_kattribute8_tab2(i);
4547 l_kattribute9_tab(l_k_rate_params_counter) := l_kattribute9_tab2(i);
4548 l_kattribute10_tab(l_k_rate_params_counter) := l_kattribute10_tab2(i);
4549 l_kattribute11_tab(l_k_rate_params_counter) := l_kattribute11_tab2(i);
4550 l_kattribute12_tab(l_k_rate_params_counter) := l_kattribute12_tab2(i);
4551 l_kattribute13_tab(l_k_rate_params_counter) := l_kattribute13_tab2(i);
4552 l_kattribute14_tab(l_k_rate_params_counter) := l_kattribute14_tab2(i);
4553 l_kattribute15_tab(l_k_rate_params_counter) := l_kattribute15_tab2(i);
4554 l_kcatchup_frequency_code_tab(l_k_rate_params_counter) := l_kcatchup_frequency_code_tab2(i);
4555
4556 l_k_rate_params_counter := l_k_rate_params_counter + 1;
4557 END LOOP;
4558 END LOOP;
4559 CLOSE C30;
4560
4561 IF l_k_rate_params_counter > 1 THEN
4562 FORALL i IN l_kkhr_id_tab.FIRST..l_kkhr_id_tab.LAST
4563 INSERT INTO okl_k_rate_params (
4564 khr_id, parameter_type_code, effective_from_date,
4565 effective_to_date, interest_index_id, base_rate,
4566 interest_start_date, adder_rate, maximum_rate,
4567 minimum_rate, principal_basis_code, days_in_a_month_code,
4568 days_in_a_year_code, interest_basis_code, rate_delay_code,
4569 rate_delay_frequency,compounding_frequency_code, calculation_formula_id,
4570 catchup_basis_code, catchup_start_date, catchup_settlement_code,
4571 rate_change_start_date, rate_change_frequency_code, rate_change_value,
4572 conversion_option_code, next_conversion_date, conversion_type_code,
4573 attribute_category, attribute1, attribute2,
4574 attribute3, attribute4, attribute5,
4575 attribute6, attribute7, attribute8,
4576 attribute9, attribute10, attribute11,
4577 attribute12, attribute13, attribute14,
4578 attribute15, created_by, creation_date,
4579 last_updated_by, last_update_date, last_update_login,
4580 catchup_frequency_code
4581 ) VALUES (
4582 l_kkhr_id_tab(i), l_kparameter_type_code_tab(i), l_keffective_from_date_tab(i),
4583 l_keffective_to_date_tab(i), l_kinterest_index_id_tab(i), l_kbase_rate_tab(i),
4584 l_kinterest_start_date_tab(i), l_kadder_rate_tab(i), l_kmaximum_rate_tab(i),
4585 l_kminimum_rate_tab(i), l_kprincipal_basis_code_tab(i), l_kdays_in_a_month_code_tab(i),
4586 l_kdays_in_a_year_code_tab(i), l_kinterest_basis_code_tab(i), l_krate_delay_code_tab(i),
4587 l_krate_delay_frequency_tab(i), l_kcompounding_frequ_code_tab(i), l_kcalculation_formula_id_tab(i),
4588 l_kcatchup_basis_code_tab(i), l_kcatchup_start_date_tab(i), l_kcatchup_settlemen_code_tab(i),
4589 l_krate_change_start_date_tab(i), l_krate_change_frequ_code_tab(i), l_krate_change_value_tab(i),
4590 l_kconversion_option_code_tab(i), l_knext_conversion_date_tab(i), l_kconversion_type_code_tab(i),
4591 l_kattribute_category_tab(i),l_kattribute1_tab(i), l_kattribute2_tab(i),
4592 l_kattribute3_tab(i), l_kattribute4_tab(i), l_kattribute5_tab(i),
4593 l_kattribute6_tab(i), l_kattribute7_tab(i), l_kattribute8_tab(i),
4594 l_kattribute9_tab(i), l_kattribute10_tab(i), l_kattribute11_tab(i),
4595 l_kattribute12_tab(i), l_kattribute13_tab(i), l_kattribute14_tab(i),
4596 l_kattribute15_tab(i), fnd_global.user_id, SYSDATE,
4597 fnd_global.user_id, SYSDATE, fnd_global.login_id,
4598 l_kcatchup_frequency_code_tab(i)
4599 );
4600 END IF;
4601
4602 EXCEPTION WHEN OTHERS THEN
4603
4604 --x_return_status := OKL_API.G_RET_STS_ERROR;
4605 /* DEBUG */
4606 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4607 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'sqlcode=' || SQLCODE || ':sqlerrm=' || SQLERRM);
4608 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Printing copy_var_int_rate_params:l_kkhr_id_tab.count=' || l_kkhr_id_tab.COUNT || ' ...');
4609 END IF;
4610 IF (l_kkhr_id_tab.COUNT > 0) THEN
4611 FOR i IN l_kkhr_id_tab.first..l_kkhr_id_tab.last
4612 LOOP
4613 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4614 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'KHR_ID('||i||')=' || l_kKHR_ID_tab(i));
4615 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'PARAMETER_TYPE_CODE('||i||')=' || l_kPARAMETER_TYPE_CODE_tab(i));
4616 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'EFFECTIVE_FROM_DATE('||i||')=' || l_kEFFECTIVE_FROM_DATE_tab(i));
4617 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'EFFECTIVE_TO_DATE('||i||')=' || l_kEFFECTIVE_TO_DATE_tab(i));
4618 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'INTEREST_INDEX_ID('||i||')=' || l_kINTEREST_INDEX_ID_tab(i));
4619 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'BASE_RATE('||i||')=' || l_kBASE_RATE_tab(i));
4620 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'INTEREST_START_DATE('||i||')=' || l_kINTEREST_START_DATE_tab(i));
4621 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ADDER_RATE('||i||')=' || l_kADDER_RATE_tab(i));
4622 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'MAXIMUM_RATE('||i||')=' || l_kMAXIMUM_RATE_tab(i));
4623 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'MINIMUM_RATE('||i||')=' || l_kMINIMUM_RATE_tab(i));
4624 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'PRINCIPAL_BASIS_CODE('||i||')=' || l_kPRINCIPAL_BASIS_CODE_tab(i));
4625 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'DAYS_IN_A_MONTH_CODE('||i||')=' || l_kDAYS_IN_A_MONTH_CODE_tab(i));
4626 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'DAYS_IN_A_YEAR_CODE('||i||')=' || l_kDAYS_IN_A_YEAR_CODE_tab(i));
4627 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'INTEREST_BASIS_CODE('||i||')=' || l_kINTEREST_BASIS_CODE_tab(i));
4628 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'RATE_DELAY_CODE('||i||')=' || l_kRATE_DELAY_CODE_tab(i));
4629 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'RATE_DELAY_FREQUENCY('||i||')=' || l_kRATE_DELAY_FREQUENCY_tab(i));
4630 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'COMPOUNDING_FREQUENCY_CODE('||i||')=' || l_kCOMPOUNDING_FREQU_CODE_tab(i));
4631 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'CALCULATION_FORMULA_ID('||i||')=' || l_kCALCULATION_FORMULA_ID_tab(i));
4632 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'CATCHUP_BASIS_CODE('||i||')=' || l_kCATCHUP_BASIS_CODE_tab(i));
4633 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'CATCHUP_START_DATE('||i||')=' || l_kCATCHUP_START_DATE_tab(i));
4634 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'CATCHUP_SETTLEMENT_CODE('||i||')=' || l_kCATCHUP_SETTLEMEN_CODE_tab(i));
4635 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'RATE_CHANGE_START_DATE('||i||')=' || l_kRATE_CHANGE_START_DATE_tab(i));
4636 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'RATE_CHANGE_FREQUENCY_CODE('||i||')=' || l_kRATE_CHANGE_FREQU_CODE_tab(i));
4637 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'RATE_CHANGE_VALUE('||i||')=' || l_kRATE_CHANGE_VALUE_tab(i));
4638 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'CONVERSION_OPTION_CODE('||i||')=' || l_kCONVERSION_OPTION_CODE_tab(i));
4639 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'NEXT_CONVERSION_DATE('||i||')=' || l_kNEXT_CONVERSION_DATE_tab(i));
4640 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'CONVERSION_TYPE_CODE('||i||')=' || l_kCONVERSION_TYPE_CODE_tab(i));
4641 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ATTRIBUTE_CATEGORY('||i||')=' || l_kATTRIBUTE_CATEGORY_tab(i));
4642 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ATTRIBUTE1('||i||')=' || l_kATTRIBUTE1_tab(i));
4643 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ATTRIBUTE2('||i||')=' || l_kATTRIBUTE2_tab(i));
4644 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ATTRIBUTE3('||i||')=' || l_kATTRIBUTE3_tab(i));
4645 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ATTRIBUTE4('||i||')=' || l_kATTRIBUTE4_tab(i));
4646 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ATTRIBUTE5('||i||')=' || l_kATTRIBUTE5_tab(i));
4647 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ATTRIBUTE6('||i||')=' || l_kATTRIBUTE6_tab(i));
4648 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ATTRIBUTE7('||i||')=' || l_kATTRIBUTE7_tab(i));
4649 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ATTRIBUTE8('||i||')=' || l_kATTRIBUTE8_tab(i));
4650 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ATTRIBUTE9('||i||')=' || l_kATTRIBUTE9_tab(i));
4651 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ATTRIBUTE10('||i||')=' || l_kATTRIBUTE10_tab(i));
4652 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ATTRIBUTE11('||i||')=' || l_kATTRIBUTE11_tab(i));
4653 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ATTRIBUTE12('||i||')=' || l_kATTRIBUTE12_tab(i));
4654 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ATTRIBUTE13('||i||')=' || l_kATTRIBUTE13_tab(i));
4655 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ATTRIBUTE14('||i||')=' || l_kATTRIBUTE14_tab(i));
4656 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ATTRIBUTE15('||i||')=' || l_kATTRIBUTE15_tab(i));
4657 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'CATCHUP_FREQUENCY_CODE('||i||')=' || l_kCATCHUP_FREQUENCY_CODE_tab(i));
4658 END IF;
4659 END LOOP;
4660 END IF;
4661
4662 RAISE OKC_API.G_EXCEPTION_ERROR;
4663 END SYNC_RATE_PARAMS;
4664
4665 PROCEDURE check_base_rate(
4666 p_khr_id IN NUMBER,
4667 x_base_rate_defined OUT NOCOPY BOOLEAN,
4668 x_return_status OUT NOCOPY VARCHAR2) IS
4669 CURSOR base_rate_csr(p_id NUMBER) IS
4670 SELECT base_rate
4671 FROM OKL_K_RATE_PARAMS
4672 WHERE KHR_ID = p_id
4673 AND PARAMETER_TYPE_CODE = 'ACTUAL'
4674 AND EFFECTIVE_TO_DATE IS NULL;
4675
4676 l_base_rate NUMBER := NULL;
4677 BEGIN
4678 NULL;
4679 OPEN base_rate_csr(p_khr_id);
4680 FETCH base_rate_csr
4681 INTO l_base_rate;
4682 CLOSE base_rate_csr;
4683
4684 IF (l_base_rate IS NULL OR
4685 l_base_rate = OKL_API.G_MISS_NUM) THEN
4686 x_base_rate_defined := FALSE;
4687 ELSE
4688 x_base_rate_defined := TRUE;
4689 END IF;
4690 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4691
4692 EXCEPTION
4693 WHEN NO_DATA_FOUND THEN -- Bug 4905142
4694 x_base_rate_defined := FALSE;
4695 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4696 WHEN OTHERS THEN
4697 x_return_status := OKL_API.G_RET_STS_ERROR;
4698 x_base_rate_defined := FALSE;
4699 END;
4700
4701 PROCEDURE check_principal_payment(
4702 p_api_version IN NUMBER,
4703 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4704 x_return_status OUT NOCOPY VARCHAR2,
4705 x_msg_count OUT NOCOPY NUMBER,
4706 x_msg_data OUT NOCOPY VARCHAR2,
4707 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
4708 x_principal_payment_defined OUT NOCOPY BOOLEAN) IS
4709
4710 cursor l_hdrrl_csr( rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
4711 rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
4712 chrId NUMBER) IS
4713 SELECT crl.object1_id1
4714 FROM OKC_RULE_GROUPS_B crg,
4715 OKC_RULES_B crl
4716 WHERE crl.rgp_id = crg.id
4717 AND crg.RGD_CODE = rgcode
4718 AND crl.RULE_INFORMATION_CATEGORY = rlcat
4719 AND crg.dnz_chr_id = chrId;
4720
4721 CURSOR strm_name_csr ( styid NUMBER ) is
4722 SELECT tl.name name,
4723 stm.stream_type_purpose
4724 FROM okl_strm_type_b stm,
4725 OKL_STRM_TYPE_TL tl
4726 WHERE tl.id = stm.id
4727 AND tl.language = 'US'
4728 AND stm.id = styid;
4729
4730 l_hdrrl_rec l_hdrrl_csr%ROWTYPE;
4731 l_strm_name_rec strm_name_csr%ROWTYPE;
4732 l_api_version NUMBER := 1;
4733 l_api_name VARCHAR2(30) := 'check_principal_payment';
4734
4735 BEGIN
4736 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4737 x_principal_payment_defined := FALSE;
4738
4739 x_return_status := OKL_API.START_ACTIVITY(
4740 p_api_name => l_api_name,
4741 p_pkg_name => g_pkg_name,
4742 p_init_msg_list => p_init_msg_list,
4743 l_api_version => l_api_version,
4744 p_api_version => p_api_version,
4745 p_api_type => G_API_TYPE,
4746 x_return_status => x_return_status);
4747
4748 FOR l_hdrrl_rec in l_hdrrl_csr ( 'LALEVL', 'LASLH', p_chr_id )
4749 LOOP
4750 OPEN strm_name_csr ( l_hdrrl_rec.object1_id1 );
4751 FETCH strm_name_csr into l_strm_name_rec;
4752 IF strm_name_csr%NOTFOUND THEN
4753 CLOSE strm_name_csr;
4754 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4755 END IF;
4756 CLOSE strm_name_csr;
4757
4758 IF ( l_strm_name_rec.stream_type_purpose = 'PRINCIPAL_PAYMENT' ) Then
4759 x_principal_payment_defined := TRUE;
4760 END IF;
4761 END LOOP;
4762
4763 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
4764
4765 EXCEPTION
4766 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4767 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4768 p_api_name => l_api_name,
4769 p_pkg_name => g_pkg_name,
4770 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4771 x_msg_count => x_msg_count,
4772 x_msg_data => x_msg_data,
4773 p_api_type => g_api_type);
4774
4775 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4776 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4777 p_api_name => l_api_name,
4778 p_pkg_name => g_pkg_name,
4779 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4780 x_msg_count => x_msg_count,
4781 x_msg_data => x_msg_data,
4782 p_api_type => g_api_type);
4783
4784 WHEN OTHERS THEN
4785 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4786 p_api_name => l_api_name,
4787 p_pkg_name => g_pkg_name,
4788 p_exc_name => 'OTHERS',
4789 x_msg_count => x_msg_count,
4790 x_msg_data => x_msg_data,
4791 p_api_type => g_api_type);
4792
4793 END;
4794
4795 FUNCTION get_formula_id(p_name IN VARCHAR2) RETURN NUMBER IS
4796 l_formula_id number := NULL;
4797 begin
4798 IF (p_name is NOT NULL) THEN
4799 --RETURN(NULL);
4800 --ELSE
4801 select id
4802 INTO l_formula_id
4803 FROM OKL_FORMULAE_B
4804 WHERE NAME = p_name;
4805
4806 END IF;
4807 return(l_formula_id);
4808 end;
4809
4810 PROCEDURE copy_k_rate_params(
4811 p_api_version IN NUMBER,
4812 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4813 x_return_status OUT NOCOPY VARCHAR2,
4814 x_msg_count OUT NOCOPY NUMBER,
4815 x_msg_data OUT NOCOPY VARCHAR2,
4816 p_khr_id IN NUMBER,
4817 p_effective_from_date IN DATE,
4818 p_rate_type IN VARCHAR2,
4819 x_krpv_rec OUT NOCOPY krpv_rec_type) IS
4820
4821 l_effective_from_date DATE;
4822 l_parameter_type_code VARCHAR2(30);
4823 l_orig_system_source_code VARCHAR2(30);
4824 l_orig_system_id1 NUMBER;
4825 l_orig_effective_from_date DATE;
4826 l_last_interest_cal_date DATE;
4827 l_api_name varchar2(240) := 'copy_k_rate_params';
4828 l_krpv_rec krpv_rec_type;
4829
4830 CURSOR get_effective_from_date_csr(
4831 p_khr_id NUMBER,
4832 p_parameter_type_code VARCHAR2) IS
4833 select rate.effective_from_date,
4834 contract.orig_system_source_code,
4835 lease.date_last_interim_interest_cal,
4836 contract.orig_system_id1
4837 FROM OKL_K_RATE_PARAMS rate,
4838 OKC_K_HEADERS_B contract,
4839 OKL_K_HEADERS lease
4840 WHERE rate.khr_id = p_khr_id
4841 AND rate.parameter_type_code = p_parameter_type_code
4842 AND rate.effective_to_date is null
4843 AND rate.khr_id = contract.id
4844 AND contract.id = lease.id;
4845
4846 -- Bug 4999888
4847 l_interest_calc_basis OKL_PRODUCT_PARAMETERS_V.INTEREST_CALCULATION_BASIS%TYPE;
4848 l_rev_recog_method OKL_PRODUCT_PARAMETERS_V.REVENUE_RECOGNITION_METHOD%TYPE;
4849 l_pdt_params_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
4850
4851 --Cursor to query the last billed due date of the stream.
4852 CURSOR get_last_billed_due_date(
4853 p_chr_id OKC_K_HEADERS_B.ID%TYPE
4854 , p_stream_purpose OKL_STRM_TYPE_V.STREAM_TYPE_PURPOSE%TYPE) IS
4855 SELECT
4856 MAX(STE.STREAM_ELEMENT_DATE) LAST_STREAM_DUE_DATE
4857 --, STY.STYB_PURPOSE_MEANING
4858 FROM
4859 OKL_STRM_ELEMENTS STE
4860 , OKL_STREAMS STM
4861 , OKL_STRM_TYPE_V STY
4862 , OKL_K_HEADERS KHR
4863 , OKC_K_HEADERS_B CHR
4864 WHERE
4865 STM.ID = STE.STM_ID
4866 AND STY.ID = STM.STY_ID
4867 AND KHR.ID = STM.KHR_ID
4868 AND CHR.ID = KHR.ID
4869 AND STE.DATE_BILLED IS NOT NULL
4870 AND CHR.ID = p_chr_id
4871 AND STY.STREAM_TYPE_PURPOSE = p_stream_purpose;
4872 --GROUP BY STY.STREAM_TYPE_PURPOSE;
4873
4874 l_last_billed_due_date DATE;
4875 BEGIN
4876
4877 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4878
4879 x_return_status := OKL_API.START_ACTIVITY(
4880 p_api_name => l_api_name,
4881 p_pkg_name => g_pkg_name,
4882 p_init_msg_list => p_init_msg_list,
4883 l_api_version => 1,
4884 p_api_version => p_api_version,
4885 p_api_type => G_API_TYPE,
4886 x_return_status => x_return_status);
4887
4888 OKL_K_RATE_PARAMS_PVT.get_product(
4889 p_api_version => p_api_version,
4890 p_init_msg_list => p_init_msg_list,
4891 x_return_status => x_return_status,
4892 x_msg_count => x_msg_count,
4893 x_msg_data => x_msg_data,
4894 p_khr_id => p_khr_id,
4895 x_pdt_parameter_rec => l_pdt_params_rec);
4896
4897 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4898 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4899 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4900 RAISE OKL_API.G_EXCEPTION_ERROR;
4901 END IF;
4902
4903 l_interest_calc_basis := l_pdt_params_rec.interest_calculation_basis;
4904 l_rev_recog_method := l_pdt_params_rec.revenue_recognition_method;
4905
4906 -- AKP: todo (change actual lookup values)
4907 IF (p_rate_type IN ('INTEREST_RATE_PARAMS', 'ADDL_INTEREST_RATE_PARAMS', 'CONVERSION_BASIS')) THEN
4908 l_parameter_type_code := 'ACTUAL';
4909 ELSIF (p_rate_type IN ('INTEREST_RATE_PARAMS_CONV', 'ADDL_INTEREST_RATE_PARAMS_CONV')) THEN
4910 l_parameter_type_code := 'CONVERSION';
4911 ELSE
4912 OKC_API.set_message(p_app_name => G_APP_NAME,
4913 p_msg_name => 'OKL_LLA_VAR_RATE_INV_PARAM');
4914 RAISE OKL_API.G_EXCEPTION_ERROR;
4915 END IF;
4916
4917 l_effective_from_date := null;
4918 FOR r IN get_effective_from_date_csr(p_khr_id, l_parameter_type_code)
4919 LOOP
4920 l_effective_from_date := r.effective_from_date;
4921 l_orig_system_source_code := r.orig_system_source_code;
4922 l_last_interest_cal_date := r.date_last_interim_interest_cal;
4923 l_orig_system_id1 := r.orig_system_id1;
4924 END LOOP;
4925
4926 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4927 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'copy:p_khr_id=' || p_khr_id);
4928 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'copy:l_parameter_type_code=' || l_parameter_type_code);
4929 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'copy:l_effective_from_date=' || l_effective_from_date);
4930 END IF;
4931 IF (l_effective_from_date is NULL) THEN
4932 l_krpv_rec.effective_from_date := NULL;
4933 x_krpv_rec := l_krpv_rec;
4934 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
4935 x_msg_data => x_msg_data);
4936 return;
4937 END IF;
4938
4939 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4940 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'copy:l_orig_system_source_code=' || l_orig_system_source_code);
4941 END IF;
4942 IF (l_orig_system_source_code <> 'OKL_REBOOK') THEN
4943 OKC_API.set_message(p_app_name => G_APP_NAME,
4944 p_msg_name => 'OKL_LLA_VAR_RATE_COPY_NA');
4945 RAISE OKL_API.G_EXCEPTION_ERROR;
4946 END IF;
4947
4948 -- Bug 4999888
4949 IF (l_interest_calc_basis = 'FIXED' and l_rev_recog_method='ACTUAL') THEN
4950 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4951 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'copy:Inside FIXED and ACTUAL...');
4952 END IF;
4953 OPEN get_last_billed_due_date(l_orig_system_id1, 'LOAN_PAYMENT');
4954 FETCH get_last_billed_due_date
4955 INTO l_last_billed_due_date;
4956 CLOSE get_last_billed_due_date;
4957
4958 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4959 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_last_billed_due_date=' || l_last_billed_due_date);
4960 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_effective_from_date=' || p_effective_from_date);
4961 END IF;
4962 IF (l_last_billed_due_date IS NOT NULL AND
4963 p_effective_from_date <= l_last_billed_due_date) THEN
4964 OKC_API.set_message(p_app_name => G_APP_NAME,
4965 p_msg_name => 'OKL_LLA_VAR_RATE_INT_DATE',
4966 p_token1 => 'EFF_DATE',
4967 p_token1_value => p_effective_from_date,
4968 p_token2 => 'INTEREST_DATE',
4969 p_token2_value => l_last_billed_due_date);
4970 RAISE OKL_API.G_EXCEPTION_ERROR;
4971 END IF;
4972 END IF;
4973
4974 -- Bug 4999888
4975 IF (l_interest_calc_basis IN
4976 ('FLOAT', 'REAMORT', 'FLOAT_FACTORS', 'CATCHUP/CLEANUP')) THEN
4977 IF (l_last_interest_cal_date IS NOT NULL) THEN
4978 IF (p_effective_from_date <= l_last_interest_cal_date) THEN
4979 OKC_API.set_message(p_app_name => G_APP_NAME,
4980 p_msg_name => 'OKL_LLA_VAR_RATE_INT_DATE',
4981 p_token1 => 'EFF_DATE',
4982 p_token1_value => p_effective_from_date,
4983 p_token2 => 'INTEREST_DATE',
4984 p_token2_value => l_last_interest_cal_date);
4985 RAISE OKL_API.G_EXCEPTION_ERROR;
4986 END IF;
4987 END IF;
4988 END IF;
4989
4990 FOR r IN get_effective_from_date_csr(l_orig_system_id1,
4991 l_parameter_type_code)
4992 LOOP
4993 l_orig_effective_from_date := r.effective_from_date;
4994 END LOOP;
4995
4996 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4997 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'copy:l_orig_system_id1=' || l_orig_system_id1);
4998 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'copy:l_effective_from_date=' || l_effective_from_date);
4999 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'copy:l_orig_effective_from_date=' || l_orig_effective_from_date);
5000 END IF;
5001 IF (l_orig_effective_from_date <> l_effective_from_date) THEN
5002 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5003 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'copy:Yes, inside...');
5004 END IF;
5005 x_return_status := OKL_API.G_RET_STS_ERROR;
5006 OKC_API.set_message(p_app_name => G_APP_NAME,
5007 p_msg_name => 'OKL_LLA_VAR_RATE_EXISTS');
5008 RAISE OKL_API.G_EXCEPTION_ERROR;
5009 END IF;
5010
5011 SELECT
5012 KHR_ID, l_parameter_type_code, p_effective_from_date,
5013 EFFECTIVE_TO_DATE, INTEREST_INDEX_ID, BASE_RATE,
5014 INTEREST_START_DATE, ADDER_RATE, MAXIMUM_RATE,
5015 MINIMUM_RATE, PRINCIPAL_BASIS_CODE, DAYS_IN_A_MONTH_CODE,
5016 DAYS_IN_A_YEAR_CODE, INTEREST_BASIS_CODE, RATE_DELAY_CODE,
5017 RATE_DELAY_FREQUENCY, COMPOUNDING_FREQUENCY_CODE, CALCULATION_FORMULA_ID,
5018 CATCHUP_BASIS_CODE, CATCHUP_START_DATE, CATCHUP_SETTLEMENT_CODE,
5019 RATE_CHANGE_START_DATE, RATE_CHANGE_FREQUENCY_CODE, RATE_CHANGE_VALUE,
5020 CONVERSION_OPTION_CODE, NEXT_CONVERSION_DATE, CONVERSION_TYPE_CODE,
5021 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
5022 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
5023 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
5024 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
5025 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
5026 ATTRIBUTE15, fnd_global.user_id, SYSDATE,
5027 fnd_global.user_id, SYSDATE, fnd_global.user_id,
5028 CATCHUP_FREQUENCY_CODE
5029 INTO
5030 x_krpv_rec.KHR_ID, x_krpv_rec.parameter_type_code, x_krpv_rec.effective_from_date,
5031 x_krpv_rec.EFFECTIVE_TO_DATE, x_krpv_rec.INTEREST_INDEX_ID, x_krpv_rec.BASE_RATE,
5032 x_krpv_rec.INTEREST_START_DATE, x_krpv_rec.ADDER_RATE, x_krpv_rec.MAXIMUM_RATE,
5033 x_krpv_rec.MINIMUM_RATE, x_krpv_rec.PRINCIPAL_BASIS_CODE, x_krpv_rec.DAYS_IN_A_MONTH_CODE,
5034 x_krpv_rec.DAYS_IN_A_YEAR_CODE, x_krpv_rec.INTEREST_BASIS_CODE, x_krpv_rec.RATE_DELAY_CODE,
5035 x_krpv_rec.RATE_DELAY_FREQUENCY, x_krpv_rec.COMPOUNDING_FREQUENCY_CODE, x_krpv_rec.CALCULATION_FORMULA_ID,
5036 x_krpv_rec.CATCHUP_BASIS_CODE, x_krpv_rec.CATCHUP_START_DATE, x_krpv_rec.CATCHUP_SETTLEMENT_CODE,
5037 x_krpv_rec.RATE_CHANGE_START_DATE, x_krpv_rec.RATE_CHANGE_FREQUENCY_CODE, x_krpv_rec.RATE_CHANGE_VALUE,
5038 x_krpv_rec.CONVERSION_OPTION_CODE, x_krpv_rec.NEXT_CONVERSION_DATE, x_krpv_rec.CONVERSION_TYPE_CODE,
5039 x_krpv_rec.ATTRIBUTE_CATEGORY, x_krpv_rec.ATTRIBUTE1, x_krpv_rec.ATTRIBUTE2,
5040 x_krpv_rec.ATTRIBUTE3, x_krpv_rec.ATTRIBUTE4, x_krpv_rec.ATTRIBUTE5,
5041 x_krpv_rec.ATTRIBUTE6, x_krpv_rec.ATTRIBUTE7, x_krpv_rec.ATTRIBUTE8,
5042 x_krpv_rec.ATTRIBUTE9, x_krpv_rec.ATTRIBUTE10, x_krpv_rec.ATTRIBUTE11,
5043 x_krpv_rec.ATTRIBUTE12, x_krpv_rec.ATTRIBUTE13, x_krpv_rec.ATTRIBUTE14,
5044 x_krpv_rec.ATTRIBUTE15, x_krpv_rec.CREATED_BY, x_krpv_rec.CREATION_DATE,
5045 x_krpv_rec.LAST_UPDATED_BY, x_krpv_rec.LAST_UPDATE_DATE, x_krpv_rec.LAST_UPDATE_LOGIN,
5046 x_krpv_rec.CATCHUP_FREQUENCY_CODE
5047 FROM OKL_K_RATE_PARAMS
5048 WHERE KHR_ID = p_khr_id
5049 AND PARAMETER_TYPE_CODE = l_parameter_type_code
5050 AND EFFECTIVE_FROM_DATE = l_effective_from_date
5051 AND EFFECTIVE_TO_DATE IS NULL;
5052
5053 INSERT INTO OKL_K_RATE_PARAMS (
5054 KHR_ID, PARAMETER_TYPE_CODE, EFFECTIVE_FROM_DATE,
5055 EFFECTIVE_TO_DATE, INTEREST_INDEX_ID, BASE_RATE,
5056 INTEREST_START_DATE, ADDER_RATE, MAXIMUM_RATE,
5057 MINIMUM_RATE, PRINCIPAL_BASIS_CODE, DAYS_IN_A_MONTH_CODE,
5058 DAYS_IN_A_YEAR_CODE, INTEREST_BASIS_CODE, RATE_DELAY_CODE,
5059 RATE_DELAY_FREQUENCY, COMPOUNDING_FREQUENCY_CODE, CALCULATION_FORMULA_ID,
5060 CATCHUP_BASIS_CODE, CATCHUP_START_DATE, CATCHUP_SETTLEMENT_CODE,
5061 RATE_CHANGE_START_DATE, RATE_CHANGE_FREQUENCY_CODE, RATE_CHANGE_VALUE,
5062 CONVERSION_OPTION_CODE, NEXT_CONVERSION_DATE, CONVERSION_TYPE_CODE,
5063 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
5064 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
5065 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
5066 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
5067 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
5068 ATTRIBUTE15, CREATED_BY, CREATION_DATE,
5069 LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
5070 CATCHUP_FREQUENCY_CODE
5071 )
5072 VALUES (
5073 x_krpv_rec.KHR_ID, x_krpv_rec.parameter_type_code, x_krpv_rec.effective_from_date,
5074 x_krpv_rec.EFFECTIVE_TO_DATE, x_krpv_rec.INTEREST_INDEX_ID, x_krpv_rec.BASE_RATE,
5075 x_krpv_rec.INTEREST_START_DATE, x_krpv_rec.ADDER_RATE, x_krpv_rec.MAXIMUM_RATE,
5076 x_krpv_rec.MINIMUM_RATE, x_krpv_rec.PRINCIPAL_BASIS_CODE, x_krpv_rec.DAYS_IN_A_MONTH_CODE,
5077 x_krpv_rec.DAYS_IN_A_YEAR_CODE, x_krpv_rec.INTEREST_BASIS_CODE, x_krpv_rec.RATE_DELAY_CODE,
5078 x_krpv_rec.RATE_DELAY_FREQUENCY, x_krpv_rec.COMPOUNDING_FREQUENCY_CODE, x_krpv_rec.CALCULATION_FORMULA_ID,
5079 x_krpv_rec.CATCHUP_BASIS_CODE, x_krpv_rec.CATCHUP_START_DATE, x_krpv_rec.CATCHUP_SETTLEMENT_CODE,
5080 x_krpv_rec.RATE_CHANGE_START_DATE, x_krpv_rec.RATE_CHANGE_FREQUENCY_CODE, x_krpv_rec.RATE_CHANGE_VALUE,
5081 x_krpv_rec.CONVERSION_OPTION_CODE, x_krpv_rec.NEXT_CONVERSION_DATE, x_krpv_rec.CONVERSION_TYPE_CODE,
5082 x_krpv_rec.ATTRIBUTE_CATEGORY, x_krpv_rec.ATTRIBUTE1, x_krpv_rec.ATTRIBUTE2,
5083 x_krpv_rec.ATTRIBUTE3, x_krpv_rec.ATTRIBUTE4, x_krpv_rec.ATTRIBUTE5,
5084 x_krpv_rec.ATTRIBUTE6, x_krpv_rec.ATTRIBUTE7, x_krpv_rec.ATTRIBUTE8,
5085 x_krpv_rec.ATTRIBUTE9, x_krpv_rec.ATTRIBUTE10, x_krpv_rec.ATTRIBUTE11,
5086 x_krpv_rec.ATTRIBUTE12, x_krpv_rec.ATTRIBUTE13, x_krpv_rec.ATTRIBUTE14,
5087 x_krpv_rec.ATTRIBUTE15, x_krpv_rec.CREATED_BY, x_krpv_rec.CREATION_DATE,
5088 x_krpv_rec.LAST_UPDATED_BY, x_krpv_rec.LAST_UPDATE_DATE, x_krpv_rec.LAST_UPDATE_LOGIN,
5089 x_krpv_rec.CATCHUP_FREQUENCY_CODE);
5090
5091 UPDATE OKL_K_RATE_PARAMS
5092 SET EFFECTIVE_TO_DATE = p_effective_from_date - 1
5093 WHERE KHR_ID = p_khr_id
5094 AND PARAMETER_TYPE_CODE = l_parameter_type_code
5095 AND EFFECTIVE_FROM_DATE = l_effective_from_date
5096 AND EFFECTIVE_TO_DATE IS NULL;
5097
5098 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
5099
5100 EXCEPTION
5101 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5102 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
5103 p_api_name => l_api_name,
5104 p_pkg_name => g_pkg_name,
5105 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
5106 x_msg_count => x_msg_count,
5107 x_msg_data => x_msg_data,
5108 p_api_type => g_api_type);
5109
5110 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5111 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
5112 p_api_name => l_api_name,
5113 p_pkg_name => g_pkg_name,
5114 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
5115 x_msg_count => x_msg_count,
5116 x_msg_data => x_msg_data,
5117 p_api_type => g_api_type);
5118
5119 WHEN OTHERS THEN
5120 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
5121 p_api_name => l_api_name,
5122 p_pkg_name => g_pkg_name,
5123 p_exc_name => 'OTHERS',
5124 x_msg_count => x_msg_count,
5125 x_msg_data => x_msg_data,
5126 p_api_type => g_api_type);
5127
5128 END;
5129
5130 -- Bug 4917614
5131 PROCEDURE SYNC_BASE_RATE(
5132 p_api_version IN NUMBER,
5133 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5134 x_return_status OUT NOCOPY VARCHAR2,
5135 x_msg_count OUT NOCOPY NUMBER,
5136 x_msg_data OUT NOCOPY VARCHAR2,
5137 p_khr_id IN NUMBER) IS
5138 CURSOR implicit_interest_rate_csr(p_id NUMBER) IS
5139 SELECT IMPLICIT_INTEREST_RATE
5140 FROM OKL_K_HEADERS
5141 WHERE ID = p_id;
5142
5143 l_base_count NUMBER;
5144 l_api_name varchar2(240) := 'sync_base_rate';
5145 l_implicit_interest_rate NUMBER;
5146 BEGIN
5147 NULL;
5148
5149 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5150 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'In Sync_base_rate procedure...');
5151 END IF;
5152 x_return_status := OKL_API.START_ACTIVITY(
5153 p_api_name => l_api_name,
5154 p_pkg_name => g_pkg_name,
5155 p_init_msg_list => p_init_msg_list,
5156 l_api_version => 1,
5157 p_api_version => p_api_version,
5158 p_api_type => G_API_TYPE,
5159 x_return_status => x_return_status);
5160
5161 SELECT COUNT(1)
5162 INTO l_base_count
5163 FROM OKL_K_RATE_PARAMS
5164 WHERE khr_id = p_khr_id
5165 AND PARAMETER_TYPE_CODE = 'ACTUAL'
5166 AND EFFECTIVE_TO_DATE IS NULL
5167 AND BASE_RATE IS NULL;
5168
5169 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5170 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_base_count=' || l_base_count);
5171 END IF;
5172 IF (l_base_count > 0) THEN
5173 OPEN implicit_interest_rate_csr(p_khr_id);
5174 FETCH implicit_interest_rate_csr INTO l_implicit_interest_rate;
5175 CLOSE implicit_interest_rate_csr;
5176
5177 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5178 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_implicit_interest_rate=' || l_implicit_interest_rate);
5179 END IF;
5180 UPDATE OKL_K_RATE_PARAMS
5181 SET BASE_RATE = l_implicit_interest_rate
5182 WHERE khr_id = p_khr_id
5183 AND PARAMETER_TYPE_CODE = 'ACTUAL'
5184 AND EFFECTIVE_TO_DATE IS NULL
5185 AND BASE_RATE IS NULL;
5186 END IF;
5187
5188 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
5189
5190 EXCEPTION
5191 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5192 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
5193 p_api_name => l_api_name,
5194 p_pkg_name => g_pkg_name,
5195 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
5196 x_msg_count => x_msg_count,
5197 x_msg_data => x_msg_data,
5198 p_api_type => g_api_type);
5199
5200 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5201 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
5202 p_api_name => l_api_name,
5203 p_pkg_name => g_pkg_name,
5204 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
5205 x_msg_count => x_msg_count,
5206 x_msg_data => x_msg_data,
5207 p_api_type => g_api_type);
5208
5209 WHEN OTHERS THEN
5210 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
5211 p_api_name => l_api_name,
5212 p_pkg_name => g_pkg_name,
5213 p_exc_name => 'OTHERS',
5214 x_msg_count => x_msg_count,
5215 x_msg_data => x_msg_data,
5216 p_api_type => g_api_type);
5217
5218 END;
5219
5220 END OKL_K_RATE_PARAMS_PVT;