[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_RESTRUCTURE_QUOTE_PVT
Source
1 PACKAGE BODY OKL_AM_RESTRUCTURE_QUOTE_PVT AS
2 /* $Header: OKLRRTQB.pls 120.4 2006/11/22 13:38:37 zrehman noship $ */
3
4 -- Start of comments
5 --
6 -- Procedure Name : set_quote_defaults
7 -- Description : Default the values of parameters if the values are not
8 -- passed to this API. This assumption is necessary because
9 -- this API can either be called from a screen or from some
10 -- other process api
11 -- Business Rules :
12 -- Parameters : quot_rec, return_status
13 -- Version : 1.0
14 --
15 -- End of comments
16 PROCEDURE set_quote_defaults(
17 px_quot_rec IN OUT NOCOPY quot_rec_type,
18 x_return_status OUT NOCOPY VARCHAR2) IS
19 l_quote_eff_days NUMBER;
20 l_quote_eff_max_days NUMBER;
21 l_quote_status VARCHAR2(200) := 'IN_PROCESS';
22 l_quote_reason VARCHAR2(200) := 'EOT';
23 l_db_date DATE;
24 l_khr_end_date DATE;
25 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
26
27 -- Cursor to get the contract end_date
28 CURSOR l_khr_csr (p_khr_id IN NUMBER) IS
29 SELECT K.end_date
30 FROM OKL_K_HEADERS_FULL_V K
31 WHERE K.id = p_khr_id;
32
33 BEGIN
34 -- Get the sysdate
35 SELECT SYSDATE INTO l_db_date FROM DUAL;
36
37 -- Set the date_effective_from if null
38 IF ((px_quot_rec.date_effective_from IS NULL) OR
39 (px_quot_rec.date_effective_from = OKL_API.G_MISS_DATE)) THEN
40 px_quot_rec.date_effective_from := l_db_date ;
41 END IF;
42
43 -- for LE Uptake project 08-11-2006
44 IF ((px_quot_rec.legal_entity_id IS NULL) OR
45 (px_quot_rec.legal_entity_id = OKL_API.G_MISS_NUM)) THEN
46 px_quot_rec.legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(px_quot_rec.khr_id);
47 END IF;
48 -- for LE Uptake project 08-11-2006
49
50 -- Set the date_effective_to if null
51 IF ((px_quot_rec.date_effective_to IS NULL) OR
52 (px_quot_rec.date_effective_to = OKL_API.G_MISS_DATE)) THEN
53
54 -- set the date eff to using rules
55 OKL_AM_CREATE_QUOTE_PVT.quote_effectivity(
56 p_quot_rec => px_quot_rec,
57 x_quote_eff_days => l_quote_eff_days,
58 x_quote_eff_max_days => l_quote_eff_max_days,
59 x_return_status => l_return_status);
60
61 -- If error then above api will set the message, so exit now
62 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
63 RAISE G_EXCEPTION_HALT_VALIDATION;
64 END IF;
65
66 px_quot_rec.date_effective_to := px_quot_rec.date_effective_from
67 + l_quote_eff_days;
68 END IF;
69
70 -- Set the qst_code if null
71 IF ((px_quot_rec.qst_code IS NULL) OR
72 (px_quot_rec.qst_code = OKL_API.G_MISS_CHAR)) THEN
73 px_quot_rec.qst_code := l_quote_status;
74 END IF;
75
76 -- Set the qrs_code if null
77 IF ((px_quot_rec.qrs_code IS NULL) OR
78 (px_quot_rec.qrs_code = OKL_API.G_MISS_CHAR)) THEN
79 px_quot_rec.qrs_code := l_quote_reason;
80 END IF;
81
82 -- Set the preproceeds_yn if null
83 IF ((px_quot_rec.preproceeds_yn IS NULL) OR
84 (px_quot_rec.preproceeds_yn = OKL_API.G_MISS_CHAR)) THEN
85 px_quot_rec.preproceeds_yn := G_NO;
86 END IF;
87
88 -- Set the summary_format_yn if null
89 IF ((px_quot_rec.summary_format_yn IS NULL) OR
90 (px_quot_rec.summary_format_yn = OKL_API.G_MISS_CHAR)) THEN
91 px_quot_rec.summary_format_yn := G_NO;
92 END IF;
93
94 -- Set the consolidated_yn if null
95 IF ((px_quot_rec.consolidated_yn IS NULL) OR
96 (px_quot_rec.consolidated_yn = OKL_API.G_MISS_CHAR)) THEN
97 px_quot_rec.consolidated_yn := G_NO;
98 END IF;
99
100 -- Set the approved_yn if null
101 IF ((px_quot_rec.approved_yn IS NULL) OR
102 (px_quot_rec.approved_yn = OKL_API.G_MISS_CHAR)) THEN
103 px_quot_rec.approved_yn := G_NO;
104 END IF;
105
106 -- Set the payment_received_yn if null
107 IF ((px_quot_rec.payment_received_yn IS NULL) OR
108 (px_quot_rec.payment_received_yn = OKL_API.G_MISS_CHAR)) THEN
109 px_quot_rec.payment_received_yn := G_NO;
110 END IF;
111
112 -- Set the date_requested if null
113 IF ((px_quot_rec.date_requested IS NULL) OR
114 (px_quot_rec.date_requested = OKL_API.G_MISS_DATE)) THEN
115 px_quot_rec.date_requested := l_db_date;
116 END IF;
117
118 -- Set the date_proposal if null
119 IF ((px_quot_rec.date_proposal IS NULL) OR
120 (px_quot_rec.date_proposal = OKL_API.G_MISS_DATE)) THEN
121 px_quot_rec.date_proposal := l_db_date;
122 END IF;
123
124 -- Set the requested_by if null
125 IF ((px_quot_rec.requested_by IS NULL) OR
126 (px_quot_rec.requested_by = OKL_API.G_MISS_NUM)) THEN
127 px_quot_rec.requested_by := 1;
128 END IF;
129
130 -- Always NO during quote creation
131 px_quot_rec.accepted_yn := G_NO;
132 -- Always NO during RESTRUCTURE quote creation
133 px_quot_rec.early_termination_yn := G_NO;
134 px_quot_rec.partial_yn := G_NO;
135 -- For now *** -- OKL_QTE_PVT.Validate_Trn_Code expects a value for trn_code
136 px_quot_rec.trn_code := 'EXP';
137
138 IF ((px_quot_rec.date_restructure_start IS NULL) OR
139 (px_quot_rec.date_restructure_start = OKL_API.G_MISS_DATE)) THEN
140 px_quot_rec.date_restructure_start := l_db_date;
141 END IF;
142
143 IF ((px_quot_rec.date_restructure_end IS NULL) OR
144 (px_quot_rec.date_restructure_end = OKL_API.G_MISS_DATE)) THEN
145
146 OPEN l_khr_csr (px_quot_rec.khr_id);
147 FETCH l_khr_csr INTO l_khr_end_date;
148 CLOSE l_khr_csr;
149
150 IF l_khr_end_date IS NOT NULL THEN
151 IF ((px_quot_rec.term IS NULL) OR
152 (px_quot_rec.term = OKL_API.G_MISS_NUM)) THEN
153 px_quot_rec.date_restructure_end := l_khr_end_date;
154 ELSE
155 px_quot_rec.date_restructure_end := ADD_MONTHS (l_khr_end_date, px_quot_rec.term);
156 END IF;
157 END IF;
158
159 END IF;
160
161 x_return_status := l_return_status;
162
163 EXCEPTION
164 WHEN G_EXCEPTION_HALT_VALIDATION THEN
165 IF l_khr_csr%ISOPEN THEN
166 CLOSE l_khr_csr;
167 END IF;
168 x_return_status := OKL_API.G_RET_STS_ERROR;
169 WHEN OTHERS THEN
170 IF l_khr_csr%ISOPEN THEN
171 CLOSE l_khr_csr;
172 END IF;
173 OKL_API.set_message(p_app_name => g_app_name,
174 p_msg_name => g_unexpected_error,
175 p_token1 => g_sqlcode_token,
176 p_token1_value => sqlcode,
177 p_token2 => g_sqlerrm_token,
178 p_token2_value => sqlerrm);
179 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
180 END set_quote_defaults;
181
182 -- Start of comments
183 --
184 -- Procedure Name : quote_type_check
185 -- Description : checks if quote is of Restructure type
186 -- Business Rules :
187 -- Parameters : quote type, return_status
188 -- Version : 1.0
189 --
190 -- End of comments
191 PROCEDURE quote_type_check(
192 p_qtp_code IN VARCHAR2,
193 x_return_status OUT NOCOPY VARCHAR2) IS
194 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
195 BEGIN
196 IF (p_qtp_code IS NOT NULL AND p_qtp_code <> OKL_API.G_MISS_CHAR) AND
197 (p_qtp_code NOT LIKE 'RES%') THEN
198 l_return_status := OKL_API.G_RET_STS_ERROR;
199 END IF;
200 x_return_status := l_return_status;
201 EXCEPTION
202 WHEN OTHERS THEN
203 OKL_API.set_message(p_app_name => g_app_name,
204 p_msg_name => g_unexpected_error,
205 p_token1 => g_sqlcode_token,
206 p_token1_value => sqlcode,
207 p_token2 => g_sqlerrm_token,
208 p_token2_value => sqlerrm);
209 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
210 END quote_type_check;
211
212
213
214
215 -- Start of comments
216 --
217 -- Function Name : set_currency_defaults
218 -- Description : This procedure Defaults the Multi-Currency Columns
219 -- Business Rules :
220 -- Parameters : Input parameters : px_quot_rec, p_sys_date
221 -- Version : 1.0
222 -- History : 23-DEC-02 RMUNJULU 2726739 Created
223 -- : 30-DEC-02 RMUNJULU 2699412 Added msg
224 --
225 -- End of comments
226 PROCEDURE set_currency_defaults(
227 px_quot_rec IN OUT NOCOPY quot_rec_type,
228 p_sys_date IN DATE,
229 x_return_status OUT NOCOPY VARCHAR2) IS
230
231 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
232 l_functional_currency_code VARCHAR2(15);
233 l_contract_currency_code VARCHAR2(15);
234 l_currency_conversion_type VARCHAR2(30);
235 l_currency_conversion_rate NUMBER;
236 l_currency_conversion_date DATE;
237
238 l_converted_amount NUMBER;
239
240 -- Since we do not use the amount or converted amount in TRX_Quotes table
241 -- set a hardcoded value for the amount (and pass to to
242 -- OKL_ACCOUNTING_UTIL.convert_to_functional_currency and get back
243 -- conversion values )
244 l_hard_coded_amount NUMBER := 100;
245
246
247
248 BEGIN
249
250 -- Get the functional currency from AM_Util
251 -- RMUNJULU 30-DEC-02 2699412 changed to call right function
252 l_functional_currency_code := OKL_AM_UTIL_PVT.get_functional_currency;
253
254
255 -- Get the contract currency details from ACCOUNTING_Util
256 OKL_ACCOUNTING_UTIL.convert_to_functional_currency(
257 p_khr_id => px_quot_rec.khr_id,
258 p_to_currency => l_functional_currency_code,
259 p_transaction_date => p_sys_date,
260 p_amount => l_hard_coded_amount,
261 x_return_status => l_return_status,
262 x_contract_currency => l_contract_currency_code,
263 x_currency_conversion_type => l_currency_conversion_type,
264 x_currency_conversion_rate => l_currency_conversion_rate,
265 x_currency_conversion_date => l_currency_conversion_date,
266 x_converted_amount => l_converted_amount);
267
268 -- RMUNJULU 30-DEC-02 2699412 Added msg
269 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
270
271 -- The currency conversion rate could not be identified for specified currency.
272 OKL_API.set_message(
273 p_app_name => 'OKL',
274 p_msg_name => 'OKL_CONV_RATE_NOT_FOUND');
275
276 END IF;
277
278 -- raise exception if error
279 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
280 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
281 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
282 RAISE OKL_API.G_EXCEPTION_ERROR;
283 END IF;
284
285 px_quot_rec.currency_code := l_contract_currency_code;
286 px_quot_rec.currency_conversion_code := l_functional_currency_code;
287
288 -- If the functional currency is different from contract currency then set
289 -- currency conversion columns
290 IF l_functional_currency_code <> l_contract_currency_code THEN
291
292 -- Set the currency conversion columns
293 px_quot_rec.currency_conversion_type := l_currency_conversion_type;
294 px_quot_rec.currency_conversion_rate := l_currency_conversion_rate;
295 px_quot_rec.currency_conversion_date := l_currency_conversion_date;
296
297 END IF;
298
299 -- Set the return status
300 x_return_status := l_return_status;
301
302 EXCEPTION
303
304 WHEN OKL_API.G_EXCEPTION_ERROR THEN
305
306 x_return_status := OKL_API.G_RET_STS_ERROR;
307
308 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
309
310 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
311
312 WHEN OTHERS THEN
313
314 -- unexpected error
315 OKL_API.set_message(
316 p_app_name => 'OKC',
317 p_msg_name => g_unexpected_error,
318 p_token1 => g_sqlcode_token,
319 p_token1_value => sqlcode,
320 p_token2 => g_sqlerrm_token,
321 p_token2_value => sqlerrm);
322
323 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
324
325 END set_currency_defaults;
326
327
328
329 -- Start of comments
330 --
331 -- Procedure Name : validate_quote
332 -- Description : checks the validity of the quote
333 -- Business Rules :
334 -- Parameters : quote rec, return_status
335 -- Version : 1.0
336 -- History : RMUNJULU 17-DEC-02 2484327 Changed the Accepted quote
337 -- exists and unproccessed transaction exists logic
338 --
339 -- End of comments
340 PROCEDURE validate_quote(
341 p_api_version IN NUMBER,
342 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
343 x_return_status OUT NOCOPY VARCHAR2,
344 x_msg_count OUT NOCOPY NUMBER,
345 x_msg_data OUT NOCOPY VARCHAR2,
346 p_quot_rec IN quot_rec_type,
347 p_call_flag IN VARCHAR2) IS
348
349 -- Cursor to get the khr_id for the quote
350 CURSOR k_details_for_qte_csr (p_qte_id IN NUMBER) IS
351 SELECT K.id,
352 K.contract_number
353 FROM OKL_TRX_QUOTES_V Q,
354 OKL_K_HEADERS_FULL_V K
355 WHERE Q.id = p_qte_id
356 AND Q.khr_id = K.id;
357
358 -- Get the accepted_yn flag
359 CURSOR l_acpt_csr ( p_qte_id IN NUMBER) IS
360 SELECT accepted_yn
361 FROM OKL_TRX_QUOTES_B
362 WHERE id = p_qte_id;
363
364 -- MDOKAL Bug 3577018
365 CURSOR k_deal_type (p_khr_id IN NUMBER) IS
366 SELECT DEAL_TYPE
367 FROM OKL_K_HEADERS
368 WHERE id = p_khr_id;
369
370 l_khr_id NUMBER;
371 l_no_of_assets NUMBER := 0;
372 l_k_end_date DATE;
373 l_rule_found BOOLEAN := FALSE;
374 lx_contract_status VARCHAR2(200);
375 l_control_flag_create VARCHAR2(200) := 'RESTR_QUOTE_CREATE';
376 l_control_flag_update VARCHAR2(200) := 'RESTR_QUOTE_UPDATE';
377 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
378 l_quote_number NUMBER;
379 l_qtp_code VARCHAR2(30);
380 l_quote_type VARCHAR2(200);
381 l_contract_number VARCHAR2(200);
382 db_accepted_yn VARCHAR2(1);
383
384
385 -- RMUNJULU 17-DEC-02 Bug # 2484327 -- Added variables for checking
386 -- related to asset level termination
387 lx_quote_tbl OKL_AM_UTIL_PVT.quote_tbl_type;
388 lx_trn_tbl OKL_AM_UTIL_PVT.trn_tbl_type;
389
390 -- MDOKAL Bug 3577018
391 l_deal_type VARCHAR2(200);
392
393 BEGIN
394
395 x_return_status := OKL_API.G_RET_STS_SUCCESS;
396
397 -- Get the database khr details
398 OPEN k_details_for_qte_csr(p_quot_rec.id);
399 FETCH k_details_for_qte_csr INTO l_khr_id, l_contract_number;
400 CLOSE k_details_for_qte_csr;
401
402 -- Based on the call to validate from create or update api
403 -- call validate_contract
404 IF (p_call_flag = 'CREATE') THEN
405
406 -- MDOKAL Bug 3577018 Get the Deal Type
407 OPEN k_deal_type(p_quot_rec.khr_id);
408 FETCH k_deal_type INTO l_deal_type;
409 CLOSE k_deal_type;
410
411 -- MDOKAL Bug 3577018 - Ensure only Lease Products are processed.
412 IF l_deal_type IN ('LOAN','LOAN-REVOLVING') THEN
413 OKL_API.set_message (
414 p_app_name => G_APP_NAME,
415 p_msg_name => 'OKL_AM_REST_LOAN_ERROR');
416 RAISE G_EXCEPTION_HALT_VALIDATION;
417 END IF;
418
419 -- Call the validate contract to check contract status
420 OKL_AM_LEASE_LOAN_TRMNT_PUB.validate_contract(
421 p_api_version => p_api_version,
422 p_init_msg_list => p_init_msg_list,
423 x_return_status => l_return_status,
424 x_msg_count => x_msg_count,
425 x_msg_data => x_msg_data,
426 p_contract_id => p_quot_rec.khr_id,
427 p_control_flag => l_control_flag_create,
428 x_contract_status => lx_contract_status);
429
430 -- If error then above api will set the message, so exit now
431 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
432 RAISE G_EXCEPTION_HALT_VALIDATION;
433 END IF;
434
435
436 -- RMUNJULU 17-DEC-02 Bug # 2484327 -- START --
437
438 -- *****************
439 -- IF accepted quote with no trn exists for contract then error
440 -- *****************
441
442 -- Get accepted quote for contract with no trn
443 OKL_AM_UTIL_PVT.get_non_trn_contract_quotes (
444 p_khr_id => l_khr_id,
445 x_quote_tbl => lx_quote_tbl,
446 x_return_status => l_return_status);
447
448 -- Check the return status
449 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
450
451 -- Error occured in util proc, message set by util proc raise exp
452 RAISE G_EXCEPTION_HALT_VALIDATION;
453
454 END IF;
455
456 -- Check if accepted quote exists for the contract
457 IF lx_quote_tbl.COUNT > 0 THEN
458
459 l_quote_type := OKL_AM_UTIL_PVT.get_lookup_meaning(
460 'OKL_QUOTE_TYPE',
461 lx_quote_tbl(lx_quote_tbl.FIRST).qtp_code,
462 'Y');
463
464 -- Accepted quote QUOTE_NUMBER of quote type QUOTE_TYPE exists for
465 -- contract CONTRACT_NUMBER. Cannot create another quote for the same contract.
466 OKL_API.set_message (
467 p_app_name => G_APP_NAME,
468 p_msg_name => 'OKL_AM_QTE_ACC_EXISTS_MSG',
469 p_token1 => 'QUOTE_NUMBER',
470 p_token1_value => lx_quote_tbl(lx_quote_tbl.FIRST).quote_number,
471 p_token2 => 'QUOTE_TYPE',
472 p_token2_value => l_quote_type,
473 p_token3 => 'CONTRACT_NUMBER',
474 p_token3_value => l_contract_number);
475
476 RAISE G_EXCEPTION_HALT_VALIDATION;
477
478 END IF;
479
480
481
482 -- *****************
483 -- IF unprocessed termination trn exists for the contract then error
484 -- *****************
485
486 -- Get all the unprocessed transactions for the contract
487 OKL_AM_UTIL_PVT.get_contract_transactions (
488 p_khr_id => l_khr_id,
489 x_trn_tbl => lx_trn_tbl,
490 x_return_status => l_return_status);
491
492 -- Check the return status
493 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
494
495 -- Error occured in util proc, message set by util proc raise exp
496 RAISE G_EXCEPTION_HALT_VALIDATION;
497
498 END IF;
499
500 -- Check if termination transaction exists for the asset
501 IF lx_trn_tbl.COUNT > 0 THEN
502
503 -- A termination transaction for the contract CONTRACT_NUMBER
504 -- is already in progress.
505 OKL_API.set_message (
506 p_app_name => G_APP_NAME,
507 p_msg_name => 'OKL_AM_K_PENDING_TRN_ERROR',
508 p_token1 => 'CONTRACT_NUMBER',
509 p_token1_value => l_contract_number);
510
511 RAISE G_EXCEPTION_HALT_VALIDATION;
512
513 END IF;
514
515 -- RMUNJULU 17-DEC-02 Bug # 2484327 -- END --
516
517
518
519 -- Check date_eff_to >= date_eff_from
520 IF (p_quot_rec.date_effective_from IS NOT NULL)
521 AND (p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE)
522 AND (p_quot_rec.date_effective_to IS NOT NULL)
523 AND (p_quot_rec.date_effective_to <> OKL_API.G_MISS_DATE) THEN
524
525 IF (TRUNC(p_quot_rec.date_effective_to) <= TRUNC(p_quot_rec.date_effective_from)) THEN
526
527 -- Message : Date Effective To DATE_EFFECTIVE_TO cannot be before
528 -- Date Effective From DATE_EFFECTIVE_FROM.
529 OKL_API.SET_MESSAGE(p_app_name => 'OKL',
530 p_msg_name => 'OKL_AM_DATE_EFF_FROM_LESS_TO',
531 p_token1 => 'DATE_EFFECTIVE_TO',
532 p_token1_value => p_quot_rec.date_effective_to,
533 p_token2 => 'DATE_EFFECTIVE_FROM',
534 p_token2_value => p_quot_rec.date_effective_from);
535
536 RAISE G_EXCEPTION_HALT_VALIDATION;
537 END IF;
538 END IF;
539
540 ELSIF (p_call_flag = 'UPDATE') THEN
541
542 IF p_quot_rec.khr_id IS NOT NULL AND p_quot_rec.khr_id <> OKL_API.G_MISS_NUM THEN
543 l_khr_id := p_quot_rec.khr_id;
544 END IF;
545
546 OPEN l_acpt_csr( p_quot_rec.id);
547 FETCH l_acpt_csr INTO db_accepted_yn ;
548 CLOSE l_acpt_csr;
549
550 -- Call the validate contract to check contract status only if not accepted
551 IF db_accepted_yn <> 'Y' THEN
552
553 OKL_AM_LEASE_LOAN_TRMNT_PUB.validate_contract(
554 p_api_version => p_api_version,
555 p_init_msg_list => p_init_msg_list,
556 x_return_status => l_return_status,
557 x_msg_count => x_msg_count,
558 x_msg_data => x_msg_data,
559 p_contract_id => l_khr_id,
560 p_control_flag => l_control_flag_update,
561 x_contract_status => lx_contract_status);
562
563 -- If error then above api will set the message, so exit now
564 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
565 RAISE G_EXCEPTION_HALT_VALIDATION;
566 END IF;
567 END IF;
568 END IF;
569
570
571 -- check if quote type is valid
572 quote_type_check(
573 p_qtp_code => p_quot_rec.qtp_code,
574 x_return_status => l_return_status);
575
576 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
577 -- Message: Please select a valid Quote Type.
578 OKL_API.set_message( p_app_name => G_APP_NAME,
579 p_msg_name =>'OKL_AM_QTP_CODE_INVALID');
580 RAISE G_EXCEPTION_HALT_VALIDATION;
581 END IF;
582
583
584
585 -- Check Term is passed for Rent Restructure
586 IF p_quot_rec.qtp_code = 'RES_SOLVE_RENT' THEN
587
588 IF p_quot_rec.term IS NULL
589 OR p_quot_rec.term = OKL_API.G_MISS_NUM THEN
590 l_return_status := OKL_API.G_RET_STS_ERROR;
591 OKC_API.SET_MESSAGE (
592 p_app_name => G_OKC_APP_NAME,
593 p_msg_name => G_REQUIRED_VALUE,
594 p_token1 => G_COL_NAME_TOKEN,
595 p_token1_value => 'Term Extension');
596 RAISE G_EXCEPTION_HALT_VALIDATION;
597 END IF;
598
599 IF p_quot_rec.pop_code_end IS NULL
600 OR p_quot_rec.pop_code_end = OKL_API.G_MISS_CHAR THEN
601 l_return_status := OKL_API.G_RET_STS_ERROR;
602 OKC_API.SET_MESSAGE (
603 p_app_name => G_OKC_APP_NAME,
604 p_msg_name => G_REQUIRED_VALUE,
605 p_token1 => G_COL_NAME_TOKEN,
606 p_token1_value => 'Purchase Option');
607 RAISE G_EXCEPTION_HALT_VALIDATION;
608 END IF;
609
610 END IF;
611
612 -- Check Purchase_Amount and Purchase_Formula are mutually exclusive
613 IF ( ( p_quot_rec.purchase_formula IS NULL
614 OR p_quot_rec.purchase_formula = OKL_API.G_MISS_CHAR)
615 AND ( p_quot_rec.purchase_amount IS NULL
616 OR p_quot_rec.purchase_amount = OKL_API.G_MISS_NUM))
617 OR ( ( p_quot_rec.purchase_formula IS NOT NULL
618 AND p_quot_rec.purchase_formula <> OKL_API.G_MISS_CHAR)
619 AND ( p_quot_rec.purchase_amount IS NOT NULL
620 AND p_quot_rec.purchase_amount <> OKL_API.G_MISS_NUM))
621 THEN
622 l_return_status := OKL_API.G_RET_STS_ERROR;
623 OKC_API.SET_MESSAGE (
624 p_app_name => G_OKC_APP_NAME,
625 p_msg_name => 'OKC_POPULATE_ONLY_ONE',
626 p_token1 => 'COL_NAME1',
627 p_token1_value => 'Purchase Amount',
628 p_token2 => 'COL_NAME2',
629 p_token2_value => 'Purchase Formula');
630 RAISE G_EXCEPTION_HALT_VALIDATION;
631 END IF;
632
633
634 EXCEPTION
635 WHEN G_EXCEPTION_HALT_VALIDATION THEN
636 IF k_details_for_qte_csr%ISOPEN THEN
637 CLOSE k_details_for_qte_csr;
638 END IF;
639 IF (l_acpt_csr%ISOPEN) THEN
640 CLOSE l_acpt_csr;
641 END IF;
642 x_return_status := OKL_API.G_RET_STS_ERROR;
643
644 WHEN OTHERS THEN
645 IF k_details_for_qte_csr%ISOPEN THEN
646 CLOSE k_details_for_qte_csr;
647 END IF;
648 IF (l_acpt_csr%ISOPEN) THEN
649 CLOSE l_acpt_csr;
650 END IF;
651 OKL_API.set_message(p_app_name => g_app_name,
652 p_msg_name => g_unexpected_error,
653 p_token1 => g_sqlcode_token,
654 p_token1_value => sqlcode,
655 p_token2 => g_sqlerrm_token,
656 p_token2_value => sqlerrm);
657 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
658
659 END validate_quote;
660
661
662
663 -- Start of comments
664 --
665 -- Procedure Name : validate_updated_quote
666 -- Description : checks if updating values ok, called from update_res_quote
667 -- Business Rules :
668 -- Parameters :
669 -- Version : 1.0
670 -- History : RMUNJULU 17-DEC-02 2484327 Changed the Accepted quote
671 -- exists and unproccessed transaction exists logic for
672 -- quote when accepted
673 --
674 -- End of comments
675 PROCEDURE validate_updated_quote(
676 p_api_version IN NUMBER,
677 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
678 x_return_status OUT NOCOPY VARCHAR2,
679 x_msg_count OUT NOCOPY NUMBER,
680 x_msg_data OUT NOCOPY VARCHAR2,
681 p_quot_rec IN quot_rec_type) IS
682
683 -- Cursor to get the database values for the quote being updated
684 CURSOR k_dets_csr ( p_id NUMBER) IS
685 SELECT OTQV.khr_id,
686 OTQV.accepted_yn,
687 OTQV.date_effective_from,
688 OTQV.date_effective_to,
689 OKHV.contract_number,
690 OTQV.quote_number,
691 OTQV.id qte_id,
692 OTQV.qtp_code
693 FROM OKL_TRX_QUOTES_V OTQV,
694 OKL_K_HEADERS_FULL_V OKHV
695 WHERE OTQV.id = p_id
696 AND OTQV.khr_id = OKHV.id;
697
698
699 -- Cursor to get the contract number
700 CURSOR get_k_num_csr ( p_khr_id IN NUMBER) IS
701 SELECT contract_number
702 FROM OKL_K_HEADERS_FULL_V K
703 WHERE K.id = p_khr_id;
704
705 lp_quot_rec quot_rec_type := p_quot_rec;
706 l_q_eff_quot_rec quot_rec_type;
707 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
708 l_quote_eff_to_dt DATE;
709 db_accepted_yn VARCHAR2(200);
710 db_date_effective_from DATE;
711 db_date_effective_to DATE;
712 db_contract_id NUMBER;
713 db_sysdate DATE;
714 db_contract_number VARCHAR2(2000);
715 db_quote_number NUMBER;
716 db_qte_id NUMBER;
717 db_qtp_code VARCHAR2(200);
718 l_quote_eff_days NUMBER;
719 l_quote_eff_max_days NUMBER;
720 l_quote_number NUMBER;
721 l_qtp_code VARCHAR2(30);
722 l_quote_type VARCHAR2(200);
723 l_trn_exists VARCHAR2(1) := '?';
724 l_contract_id NUMBER;
725 l_contract_number VARCHAR2(200);
726 l_date_eff_from DATE;
727 lx_contract_status VARCHAR2(200);
728
729 -- RMUNJULU 17-DEC-02 Bug # 2484327 -- Added variables for checking
730 -- related to asset level termination
731 lx_quote_tbl OKL_AM_UTIL_PVT.quote_tbl_type;
732 lx_trn_tbl OKL_AM_UTIL_PVT.trn_tbl_type;
733
734 BEGIN
735
736 -- Get the db_sys_date
737 SELECT SYSDATE INTO db_sysdate FROM DUAL;
738
739 -- Get the DB values for the quote being updated
740 OPEN k_dets_csr(lp_quot_rec.id);
741 FETCH k_dets_csr INTO db_contract_id,
742 db_accepted_yn,
743 db_date_effective_from,
744 db_date_effective_to,
745 db_contract_number,
746 db_quote_number,
747 db_qte_id,
748 db_qtp_code;
749 IF k_dets_csr%NOTFOUND THEN
750 RAISE G_EXCEPTION_HALT_VALIDATION;
751 END IF;
752 CLOSE k_dets_csr;
753
754 -- Check if quote id passed is valid
755 IF db_qte_id IS NULL OR db_qte_id = OKL_API.G_MISS_NUM THEN
756
757 OKL_API.set_message( p_app_name => OKC_API.G_APP_NAME,
758 p_msg_name => OKC_API.G_INVALID_VALUE,
759 p_token1 => OKC_API.G_COL_NAME_TOKEN,
760 p_token1_value => 'Quote id');
761
762 RAISE G_EXCEPTION_HALT_VALIDATION;
763
764 END IF;
765
766 -- check if quote valid
767 validate_quote(
768 p_api_version => p_api_version,
769 p_init_msg_list => p_init_msg_list,
770 x_return_status => l_return_status,
771 x_msg_count => x_msg_count,
772 x_msg_data => x_msg_data,
773 p_quot_rec => lp_quot_rec,
774 p_call_flag => 'UPDATE');
775
776 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
777 RAISE G_EXCEPTION_HALT_VALIDATION;
778 END IF;
779
780 -- if Khr_Id not passed get from DB
781 IF lp_quot_rec.khr_id IS NULL OR lp_quot_rec.khr_id = OKL_API.G_MISS_NUM THEN
782 l_contract_id := db_contract_id;
783 l_contract_number := db_contract_number;
784 ELSE
785 l_contract_id := lp_quot_rec.khr_id;
786 OPEN get_k_num_csr (l_contract_id);
787 FETCH get_k_num_csr INTO l_contract_number;
788 CLOSE get_k_num_csr;
789 END IF;
790
791 -- if qtp_code is null then get from db and check
792 IF (lp_quot_rec.qtp_code IS NULL
793 OR lp_quot_rec.qtp_code = OKL_API.G_MISS_CHAR)
794 AND db_qtp_code NOT LIKE 'RES%' THEN
795
796 -- Please select a valid Quote Type.
797 OKL_API.set_message( p_app_name => OKL_API.G_APP_NAME,
798 p_msg_name => 'OKL_AM_QTP_CODE_INVALID');
799
800 RAISE G_EXCEPTION_HALT_VALIDATION;
801
802 END IF;
803
804 -- Check if date_effective_to is NULL
805 IF lp_quot_rec.date_effective_to IS NULL
806 OR lp_quot_rec.date_effective_to = OKL_API.G_MISS_DATE THEN
807
808 -- You must enter a value for PROMPT
809 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
810 p_msg_name => 'OKL_AM_REQ_FIELD_ERR',
811 p_token1 => 'PROMPT',
812 p_token1_value => OKL_AM_UTIL_PVT.get_ak_attribute('OKL_EFFECTIVE_TO'));
813
814 RAISE G_EXCEPTION_HALT_VALIDATION;
815
816 END IF;
817
818 -- Get the date_eff_from from database if not passed
819 IF (lp_quot_rec.date_effective_from IS NOT NULL)
820 AND (lp_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE) THEN
821 l_date_eff_from := lp_quot_rec.date_effective_from;
822 ELSE
823 l_date_eff_from := db_date_effective_from;
824 END IF;
825
826 -- Check date_eff_to > date_eff_from
827 IF (l_date_eff_from IS NOT NULL)
828 AND (l_date_eff_from <> OKL_API.G_MISS_DATE)
829 AND (lp_quot_rec.date_effective_to IS NOT NULL)
830 AND (lp_quot_rec.date_effective_to <> OKL_API.G_MISS_DATE) THEN
831
832 IF (TRUNC(lp_quot_rec.date_effective_to) <= TRUNC(l_date_eff_from)) THEN
833
834 -- Message : Date Effective To DATE_EFFECTIVE_TO cannot be before
835 -- Date Effective From DATE_EFFECTIVE_FROM.
836 OKL_API.SET_MESSAGE(p_app_name => 'OKL',
837 p_msg_name => 'OKL_AM_DATE_EFF_FROM_LESS_TO',
838 p_token1 => 'DATE_EFFECTIVE_TO',
839 p_token1_value => lp_quot_rec.date_effective_to,
840 p_token2 => 'DATE_EFFECTIVE_FROM',
841 p_token2_value => l_date_eff_from);
842
843 RAISE G_EXCEPTION_HALT_VALIDATION;
844 END IF;
845 END IF;
846
847 -- if quote eff to date changed then
848 IF (lp_quot_rec.date_effective_to IS NOT NULL)
849 AND (lp_quot_rec.date_effective_to <> OKL_API.G_MISS_DATE)
850 AND (lp_quot_rec.date_effective_to <> db_date_effective_to) THEN
851
852 -- get the date eff to from rules
853 l_q_eff_quot_rec.khr_id := l_contract_id;
854 l_q_eff_quot_rec.qtp_code := lp_quot_rec.qtp_code;
855 OKL_AM_CREATE_QUOTE_PVT.quote_effectivity(
856 p_quot_rec => l_q_eff_quot_rec,
857 x_quote_eff_days => l_quote_eff_days,
858 x_quote_eff_max_days => l_quote_eff_max_days,
859 x_return_status => l_return_status);
860
861 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
862 RAISE G_EXCEPTION_HALT_VALIDATION;
863 END IF;
864
865 -- get max date eff to from rules is
866 l_quote_eff_to_dt := db_date_effective_from + l_quote_eff_max_days;
867
868 -- if max quote eff to date is less than sysdate then error
869 IF (TRUNC(l_quote_eff_to_dt) < TRUNC(db_sysdate)) THEN
870 --Message : Quote QUOTE_NUMBER is already expired.
871 OKC_API.set_message( p_app_name => OKL_API.G_APP_NAME,
872 p_msg_name => 'OKL_AM_QUOTE_ALREADY_EXP',
873 p_token1 => 'QUOTE_NUMBER',
874 p_token1_value => db_quote_number);
875 RAISE G_EXCEPTION_HALT_VALIDATION;
876 END IF;
877
878 -- if quote eff to date is less than sysdate then error
879 IF TRUNC(lp_quot_rec.date_effective_to) < TRUNC(db_sysdate) THEN
880 -- Please enter an Effective To date that occurs after the
881 -- current system date.
882 OKL_API.set_message( p_app_name => G_APP_NAME,
883 p_msg_name => 'OKL_AM_DATE_EFF_TO_PAST');
884 RAISE G_EXCEPTION_HALT_VALIDATION;
885 END IF;
886
887 -- if eff_to date > eff to date from rule then err
888 IF TRUNC(lp_quot_rec.date_effective_to) > TRUNC(l_quote_eff_to_dt) THEN
889 -- Please enter Effective To date before DATE_EFF_TO_MAX.
890 OKL_API.set_message( p_app_name => G_APP_NAME,
891 p_msg_name => 'OKL_AM_DATE_EFF_TO_ERR',
892 p_token1 => 'DATE_EFF_TO_MAX',
893 p_token1_value => l_quote_eff_to_dt);
894 RAISE G_EXCEPTION_HALT_VALIDATION;
895 END IF;
896
897 END IF;
898
899
900 -- RMUNJULU 17-DEC-02 2484327 -- START --
901
902 -- ACCEPTED YN VALIDATION -- QUOTE ACCEPTED NOW --
903
904 IF lp_quot_rec.accepted_yn IS NOT NULL
905 AND lp_quot_rec.accepted_yn <> OKL_API.G_MISS_CHAR
906 AND db_accepted_yn = G_NO
907 AND lp_quot_rec.accepted_yn = G_YES THEN
908
909
910 -- *****************
911 -- IF quote expired then error
912 -- *****************
913
914
915 -- If date_eff_to is not passed
916 IF ((lp_quot_rec.date_effective_to IS NULL) OR
917 (lp_quot_rec.date_effective_to = OKL_API.G_MISS_DATE)) THEN
918
919 --if quote expired
920 IF TRUNC(db_sysdate) > TRUNC(db_date_effective_to) THEN
921 --Message : Quote QUOTE_NUMBER is already expired.
922 OKL_API.set_message( p_app_name => G_APP_NAME,
923 p_msg_name => 'OKL_AM_QUOTE_ALREADY_EXP',
924 p_token1 => 'QUOTE_NUMBER',
925 p_token1_value => db_quote_number);
926
927 RAISE G_EXCEPTION_HALT_VALIDATION;
928 END IF;
929 END IF;
930
931
932 -- *****************
933 -- IF accepted quote with no trn exists for contract then error
934 -- *****************
935
936 -- Get accepted quote for contract with no trn
937 OKL_AM_UTIL_PVT.get_non_trn_contract_quotes (
938 p_khr_id => l_contract_id,
939 x_quote_tbl => lx_quote_tbl,
940 x_return_status => l_return_status);
941
942 -- Check the return status
943 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
944
945 -- Error occured in util proc, message set by util proc raise exp
946 RAISE G_EXCEPTION_HALT_VALIDATION;
947
948 END IF;
949
950 -- Check if accepted quote exists for the contract
951 IF lx_quote_tbl.COUNT > 0 THEN
952
953 l_quote_type := OKL_AM_UTIL_PVT.get_lookup_meaning(
954 'OKL_QUOTE_TYPE',
955 lx_quote_tbl(lx_quote_tbl.FIRST).qtp_code,
956 'Y');
957
958 -- Accepted quote QUOTE_NUMBER of quote type QUOTE_TYPE exists for
959 -- contract CONTRACT_NUMBER. Cannot accept multiple quotes for the same contract.
960 OKL_API.set_message (
961 p_app_name => G_APP_NAME,
962 p_msg_name => 'OKL_AM_QTE_ACC_EXISTS_ERR',
963 p_token1 => 'QUOTE_NUMBER',
964 p_token1_value => lx_quote_tbl(lx_quote_tbl.FIRST).quote_number,
965 p_token2 => 'QUOTE_TYPE',
966 p_token2_value => l_quote_type,
967 p_token3 => 'CONTRACT_NUMBER',
968 p_token3_value => l_contract_number);
969
970 RAISE G_EXCEPTION_HALT_VALIDATION;
971
972 END IF;
973
974 -- *****************
975 -- IF unprocessed termination trn exists for the contract then error
976 -- *****************
977
978 -- Get all the unprocessed transactions for the contract
979 OKL_AM_UTIL_PVT.get_contract_transactions (
980 p_khr_id => l_contract_id,
981 x_trn_tbl => lx_trn_tbl,
982 x_return_status => l_return_status);
983
984 -- Check the return status
985 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
986
987 -- Error occured in util proc, message set by util proc raise exp
988 RAISE G_EXCEPTION_HALT_VALIDATION;
989
990 END IF;
991
992 -- Check if termination transaction exists for the asset
993 IF lx_trn_tbl.COUNT > 0 THEN
994
995 -- A termination transaction for the contract CONTRACT_NUMBER
996 -- is already in progress.
997 OKL_API.set_message (
998 p_app_name => G_APP_NAME,
999 p_msg_name => 'OKL_AM_K_PENDING_TRN_ERROR',
1000 p_token1 => 'CONTRACT_NUMBER',
1001 p_token1_value => l_contract_number);
1002
1003 RAISE G_EXCEPTION_HALT_VALIDATION;
1004
1005 END IF;
1006
1007 ELSIF lp_quot_rec.accepted_yn IS NOT NULL
1008 AND lp_quot_rec.accepted_yn <> OKL_API.G_MISS_CHAR
1009 AND db_accepted_yn = G_YES
1010 AND lp_quot_rec.accepted_yn = G_NO THEN
1011
1012 -- Quote QUOTE_NUMBER is already accepted.
1013 OKL_API.set_message( p_app_name => G_APP_NAME,
1014 p_msg_name => 'OKL_AM_QUOTE_ALREADY_ACCP',
1015 p_token1 => 'QUOTE_NUMBER',
1016 p_token1_value => db_quote_number);
1017
1018 RAISE G_EXCEPTION_HALT_VALIDATION;
1019
1020 END IF;
1021
1022 -- RMUNJULU 17-DEC-02 2484327 -- END --
1023
1024
1025 x_return_status := l_return_status;
1026
1027 EXCEPTION
1028 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1029 IF k_dets_csr%ISOPEN THEN
1030 CLOSE k_dets_csr;
1031 END IF;
1032 IF get_k_num_csr%ISOPEN THEN
1033 CLOSE get_k_num_csr;
1034 END IF;
1035
1036 x_return_status := OKL_API.G_RET_STS_ERROR;
1037
1038 WHEN OTHERS THEN
1039 IF k_dets_csr%ISOPEN THEN
1040 CLOSE k_dets_csr;
1041 END IF;
1042 IF get_k_num_csr%ISOPEN THEN
1043 CLOSE get_k_num_csr;
1044 END IF;
1045
1046 OKL_API.set_message(p_app_name => g_app_name,
1047 p_msg_name => g_unexpected_error,
1048 p_token1 => g_sqlcode_token,
1049 p_token1_value => sqlcode,
1050 p_token2 => g_sqlerrm_token,
1051 p_token2_value => sqlerrm);
1052
1053 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1054
1055 END validate_updated_quote;
1056
1057
1058
1059 -- Start of comments
1060 --
1061 -- Procedure Name : create_restructure_quote
1062 -- Description : create the restructure quote
1063 -- Business Rules :
1064 -- Parameters :
1065 -- Version : 1.0
1066 -- History : RMUNJULU 23-DEC-02 2726739 Multi-currency changes, default
1067 -- currency columns
1068 -- rmunjulu bug 3884338, default all date fields to NULL so that correct date is stamped.
1069 -- issue occurs when g_miss_date passed from rossetta layer does not match okl_api.g_miss_date
1070 --
1071 --
1072 -- End of comments
1073 PROCEDURE create_restructure_quote(
1074 p_api_version IN NUMBER,
1075 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1076 x_return_status OUT NOCOPY VARCHAR2,
1077 x_msg_count OUT NOCOPY NUMBER,
1078 x_msg_data OUT NOCOPY VARCHAR2,
1079 p_quot_rec IN quot_rec_type,
1080 x_quot_rec OUT NOCOPY quot_rec_type) AS
1081
1082 lp_quot_rec quot_rec_type := p_quot_rec;
1083 lx_quot_rec quot_rec_type;
1084 l_api_version CONSTANT NUMBER := 1;
1085 l_api_name CONSTANT VARCHAR2(30) := 'create_restructure_quote';
1086 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1087
1088 l_request_id NUMBER;
1089 l_trans_status VARCHAR2(30);
1090
1091 -- RMUNJULU 23-DEC-02 2726739 Added variable
1092 l_sys_date DATE;
1093
1094 BEGIN
1095 --Check API version, initialize message list and create savepoint.
1096 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1097 G_PKG_NAME,
1098 p_init_msg_list,
1099 l_api_version,
1100 p_api_version,
1101 '_PVT',
1102 x_return_status);
1103
1104 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1105 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1106 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1107 RAISE OKL_API.G_EXCEPTION_ERROR;
1108 END IF;
1109
1110 -- RMUNJULU 23-DEC-02 2726739 Added select
1111 SELECT SYSDATE INTO l_sys_date FROM DUAL;
1112
1113 -- rmunjulu bug 3695751, default all date fields to NULL so that correct date is stamped.
1114 -- issue occurs when g_miss_date passed from rossetta layer does not match okl_api.g_miss_date
1115
1116 lp_quot_rec.date_effective_from := NULL;
1117 lp_quot_rec.date_effective_to := NULL;
1118 lp_quot_rec.date_requested := NULL;
1119 lp_quot_rec.date_proposal := NULL;
1120 lp_quot_rec.date_restructure_start := NULL;
1121 lp_quot_rec.date_restructure_end := NULL;
1122 lp_quot_rec.date_due := NULL;
1123 lp_quot_rec.date_approved := NULL;
1124 lp_quot_rec.date_accepted := NULL;
1125 lp_quot_rec.date_payment_received := NULL;
1126 lp_quot_rec.currency_conversion_date := NULL;
1127
1128 -- check if quote valid
1129 validate_quote(
1130 p_api_version => p_api_version,
1131 p_init_msg_list => p_init_msg_list,
1132 x_return_status => l_return_status,
1133 x_msg_count => x_msg_count,
1134 x_msg_data => x_msg_data,
1135 p_quot_rec => lp_quot_rec,
1136 p_call_flag => 'CREATE');
1137
1138 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1139 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1140 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1141 RAISE OKL_API.G_EXCEPTION_ERROR;
1142 END IF;
1143
1144 -- Set the quote defaults
1145 set_quote_defaults(
1146 px_quot_rec => lp_quot_rec,
1147 x_return_status => l_return_status);
1148
1149 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1150 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1151 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1152 RAISE OKL_API.G_EXCEPTION_ERROR;
1153 END IF;
1154
1155
1156 -- RMUNJULU 23-DEC-02 2726739 Multi-currency changes
1157 -- Default the Multi-Currency Columns
1158 set_currency_defaults(
1159 px_quot_rec => lp_quot_rec,
1160 p_sys_date => l_sys_date,
1161 x_return_status => l_return_status);
1162
1163
1164 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1165 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1166 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1167 RAISE OKL_API.G_EXCEPTION_ERROR;
1168 END IF;
1169
1170 -- call the pub tapi insert
1171 OKL_TRX_QUOTES_PUB.insert_trx_quotes (
1172 p_api_version => p_api_version,
1173 p_init_msg_list => p_init_msg_list,
1174 x_msg_count => x_msg_count,
1175 x_msg_data => x_msg_data,
1176 p_qtev_rec => lp_quot_rec,
1177 x_qtev_rec => lx_quot_rec,
1178 x_return_status => l_return_status);
1179
1180 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1181 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1182 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1183 RAISE OKL_API.G_EXCEPTION_ERROR;
1184 END IF;
1185
1186 -- Request Rent Restructure
1187 OKL_AM_RESTRUCTURE_RENTS_PVT.initiate_request (
1188 p_api_version => p_api_version,
1189 p_init_msg_list => p_init_msg_list,
1190 x_msg_count => x_msg_count,
1191 x_msg_data => x_msg_data,
1192 p_quote_id => lx_quot_rec.id,
1193 x_request_id => l_request_id,
1194 x_trans_status => l_trans_status,
1195 x_return_status => l_return_status);
1196
1197 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1198 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1199 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1200 RAISE OKL_API.G_EXCEPTION_ERROR;
1201 END IF;
1202
1203 -- set the return status and out variables
1204 x_return_status := l_return_status;
1205 x_quot_rec := lx_quot_rec;
1206
1207 -- end the transaction
1208 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1209
1210 EXCEPTION
1211 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1212 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1213 (
1214 l_api_name,
1215 G_PKG_NAME,
1216 'OKL_API.G_RET_STS_ERROR',
1217 x_msg_count,
1218 x_msg_data,
1219 '_PVT'
1220 );
1221
1222 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1223 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1224 (
1225 l_api_name,
1226 G_PKG_NAME,
1227 'OKL_API.G_RET_STS_UNEXP_ERROR',
1228 x_msg_count,
1229 x_msg_data,
1230 '_PVT'
1231 );
1232
1233 WHEN OTHERS THEN
1234 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1235 (
1236 l_api_name,
1237 G_PKG_NAME,
1238 'OTHERS',
1239 x_msg_count,
1240 x_msg_data,
1241 '_PVT'
1242 );
1243
1244 END create_restructure_quote;
1245
1246 -- Start of comments
1247 --
1248 -- Procedure Name : create_restructure_quote
1249 -- Description : Create multiple restructure quotes
1250 -- Business Rules :
1251 -- Parameters :
1252 -- Version : 1.0
1253 --
1254 -- End of comments
1255 PROCEDURE create_restructure_quote(
1256 p_api_version IN NUMBER,
1257 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1258 x_return_status OUT NOCOPY VARCHAR2,
1259 x_msg_count OUT NOCOPY NUMBER,
1260 x_msg_data OUT NOCOPY VARCHAR2,
1261 p_quot_tbl IN quot_tbl_type,
1262 x_quot_tbl OUT NOCOPY quot_tbl_type) AS
1263
1264 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1265 i NUMBER := 0;
1266 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1267 l_api_name CONSTANT VARCHAR2(30) := 'create_restructure_quote';
1268 l_api_version CONSTANT NUMBER := 1;
1269
1270 BEGIN
1271
1272 --Check API version, initialize message list and create savepoint.
1273 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1274 G_PKG_NAME,
1275 p_init_msg_list,
1276 l_api_version,
1277 p_api_version,
1278 '_PVT',
1279 x_return_status);
1280 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1281 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1282 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1283 RAISE OKL_API.G_EXCEPTION_ERROR;
1284 END IF;
1285
1286 -- Make sure PL/SQL table has records in it before passing
1287 IF (p_quot_tbl.COUNT > 0) THEN
1288 i := p_quot_tbl.FIRST;
1289 LOOP
1290 create_restructure_quote (
1291 p_api_version => p_api_version,
1292 p_init_msg_list => p_init_msg_list,
1293 x_return_status => l_return_status,
1294 x_msg_count => x_msg_count,
1295 x_msg_data => x_msg_data,
1296 p_quot_rec => p_quot_tbl(i),
1297 x_quot_rec => x_quot_tbl(i));
1298
1299 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1300 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1301 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1302 RAISE OKL_API.G_EXCEPTION_ERROR;
1303 END IF;
1304
1305 EXIT WHEN (i = p_quot_tbl.LAST);
1306 i := p_quot_tbl.NEXT(i);
1307 END LOOP;
1308 END IF;
1309
1310 x_return_status := l_return_status;
1311
1312 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1313 EXCEPTION
1314 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1315 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1316 (
1317 l_api_name,
1318 G_PKG_NAME,
1319 'OKL_API.G_RET_STS_ERROR',
1320 x_msg_count,
1321 x_msg_data,
1322 '_PVT'
1323 );
1324 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1325 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1326 (
1327 l_api_name,
1328 G_PKG_NAME,
1329 'OKL_API.G_RET_STS_UNEXP_ERROR',
1330 x_msg_count,
1331 x_msg_data,
1332 '_PVT'
1333 );
1334 WHEN OTHERS THEN
1335 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1336 (
1337 l_api_name,
1338 G_PKG_NAME,
1339 'OTHERS',
1340 x_msg_count,
1341 x_msg_data,
1342 '_PVT'
1343 );
1344 END create_restructure_quote;
1345
1346 -- Start of comments
1347 --
1348 -- Procedure Name : update_restructure_quote
1349 -- Description : Update restructure quote
1350 -- Business Rules :
1351 -- Parameters :
1352 -- Version : 1.0
1353 -- History : RMUNJULU 30-DEC-02 2699412 Multi-currency changes, default
1354 -- currency columns
1355 -- RMUNJULU 31-JAN-03 2779255 Added code to get khr_id if not
1356 -- passed to get the currency code
1357 --
1358 -- End of comments
1359 PROCEDURE update_restructure_quote(
1360 p_api_version IN NUMBER,
1361 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1362 x_return_status OUT NOCOPY VARCHAR2,
1363 x_msg_count OUT NOCOPY NUMBER,
1364 x_msg_data OUT NOCOPY VARCHAR2,
1365 p_quot_rec IN quot_rec_type,
1366 x_quot_rec OUT NOCOPY quot_rec_type) AS
1367
1368 --Get the database value of the updating quote
1369 -- RMUNJULU 31-JAN-03 2779255 Added khr_id
1370 CURSOR okl_get_qte_db_vals_csr ( p_qte_id IN NUMBER) IS
1371 SELECT QTE.accepted_yn,
1372 QTE.khr_id
1373 FROM OKL_TRX_QUOTES_V QTE
1374 WHERE QTE.id = p_qte_id;
1375
1376 lp_quot_rec quot_rec_type := p_quot_rec;
1377 lx_quot_rec quot_rec_type;
1378 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1379 l_api_name CONSTANT VARCHAR2(30) := 'update_restructure_quote';
1380 l_api_version CONSTANT NUMBER := 1;
1381 db_accepted_yn VARCHAR2(1);
1382 db_sysdate DATE;
1383 l_event_name VARCHAR2(200) := 'oracle.apps.okl.am.acceptrestquote';
1384 l_event_desc VARCHAR2(2000);
1385
1386
1387 -- RMUNJULU 31-JAN-03 2779255 Added variable
1388 db_khr_id NUMBER;
1389
1390 BEGIN
1391
1392 --Check API version, initialize message list and create savepoint.
1393 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1394 G_PKG_NAME,
1395 p_init_msg_list,
1396 l_api_version,
1397 p_api_version,
1398 '_PVT',
1399 x_return_status);
1400
1401 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1402 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1403 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1404 RAISE OKL_API.G_EXCEPTION_ERROR;
1405 END IF;
1406
1407 -- initialize return variables
1408 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1409
1410 SELECT SYSDATE INTO db_sysdate FROM DUAL;
1411
1412 -- Check the values being updated
1413 validate_updated_quote(
1414 p_api_version => p_api_version,
1415 p_init_msg_list => p_init_msg_list,
1416 x_return_status => l_return_status,
1417 x_msg_count => x_msg_count,
1418 x_msg_data => x_msg_data,
1419 p_quot_rec => lp_quot_rec);
1420
1421 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1422 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1423 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1424 RAISE OKL_API.G_EXCEPTION_ERROR;
1425 END IF;
1426
1427 -- RMUNJULU 31-JAN-03 2779255 get khr_id from DB
1428 OPEN okl_get_qte_db_vals_csr(lp_quot_rec.id);
1429 FETCH okl_get_qte_db_vals_csr INTO db_accepted_yn, db_khr_id;
1430 CLOSE okl_get_qte_db_vals_csr;
1431
1432 -- Set the qst_code to ACCEPTED if the quote is accepted now
1433 IF (lp_quot_rec.accepted_yn = G_YES AND db_accepted_yn = G_NO) THEN
1434
1435 -- RMUNJULU 31-JAN-03 2779255 get khr_id if not passed
1436 IF lp_quot_rec.khr_id IS NULL
1437 OR lp_quot_rec.khr_id = OKL_API.G_MISS_NUM THEN
1438
1439 lp_quot_rec.khr_id := db_khr_id;
1440
1441 END IF;
1442
1443 -- RMUNJULU 30-DEC-02 2699412 Multi-currency changes
1444 -- Default the Multi-Currency Columns
1445 set_currency_defaults(
1446 px_quot_rec => lp_quot_rec,
1447 p_sys_date => db_sysdate,
1448 x_return_status => l_return_status);
1449
1450
1451 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1452 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1453 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1454 RAISE OKL_API.G_EXCEPTION_ERROR;
1455 END IF;
1456
1457 lp_quot_rec.qst_code := 'ACCEPTED';
1458 lp_quot_rec.date_accepted := db_sysdate;
1459
1460 END IF;
1461
1462 -- update the quote
1463 OKL_TRX_QUOTES_PUB.update_trx_quotes(
1464 p_api_version => p_api_version,
1465 p_init_msg_list => p_init_msg_list,
1466 x_return_status => l_return_status,
1467 x_msg_count => x_msg_count,
1468 x_msg_data => x_msg_data,
1469 p_qtev_rec => lp_quot_rec,
1470 x_qtev_rec => lx_quot_rec);
1471
1472 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1473 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1474 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1475 RAISE OKL_API.G_EXCEPTION_ERROR;
1476 END IF;
1477
1478 -- Raise WF if the quote is accepted now
1479 IF (lp_quot_rec.accepted_yn = G_YES AND db_accepted_yn = G_NO) THEN
1480
1481 -- Raise Restructure Quote WorkFlow event
1482 OKL_AM_WF.raise_business_event (
1483 p_transaction_id => lp_quot_rec.id,
1484 p_event_name => l_event_name);
1485
1486 -- Get the event name
1487 l_event_desc := OKL_AM_UTIL_PVT.get_wf_event_name(
1488 p_wf_process_type => 'OKLAMARQ',
1489 p_wf_process_name => 'QUOTE_ACCEPTANCE_PROC',
1490 x_return_status => l_return_status);
1491
1492 -- raise exception if error
1493 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1494 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1495 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1496 RAISE OKL_API.G_EXCEPTION_ERROR;
1497 END IF;
1498
1499 -- Set message on stack
1500 -- Workflow event EVENT_NAME has been requested.
1501 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
1502 p_msg_name => 'OKL_AM_WF_EVENT_MSG',
1503 p_token1 => 'EVENT_NAME',
1504 p_token1_value => l_event_desc);
1505
1506
1507 -- Save message from stack into transaction message table
1508 OKL_AM_UTIL_PVT.process_messages(
1509 p_trx_source_table => 'OKL_TRX_QUOTES_V',
1510 p_trx_id => lp_quot_rec.id,
1511 x_return_status => l_return_status);
1512
1513 -- raise exception if error
1514 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1515 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1516 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1517 RAISE OKL_API.G_EXCEPTION_ERROR;
1518 END IF;
1519
1520 END IF;
1521
1522
1523 -- set return variables
1524 x_return_status := l_return_status;
1525 x_quot_rec := lx_quot_rec;
1526
1527 -- end the transaction
1528 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1529
1530 EXCEPTION
1531 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1532 IF okl_get_qte_db_vals_csr%ISOPEN THEN
1533 CLOSE okl_get_qte_db_vals_csr;
1534 END IF;
1535 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1536 (
1537 l_api_name,
1538 G_PKG_NAME,
1539 'OKL_API.G_RET_STS_ERROR',
1540 x_msg_count,
1541 x_msg_data,
1542 '_PVT'
1543 );
1544
1545 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1546 IF okl_get_qte_db_vals_csr%ISOPEN THEN
1547 CLOSE okl_get_qte_db_vals_csr;
1548 END IF;
1549 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1550 (
1551 l_api_name,
1552 G_PKG_NAME,
1553 'OKL_API.G_RET_STS_UNEXP_ERROR',
1554 x_msg_count,
1555 x_msg_data,
1556 '_PVT'
1557 );
1558
1559 WHEN OTHERS THEN
1560 IF okl_get_qte_db_vals_csr%ISOPEN THEN
1561 CLOSE okl_get_qte_db_vals_csr;
1562 END IF;
1563 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1564 (
1565 l_api_name,
1566 G_PKG_NAME,
1567 'OTHERS',
1568 x_msg_count,
1569 x_msg_data,
1570 '_PVT'
1571 );
1572
1573 END update_restructure_quote;
1574
1575 -- Start of comments
1576 --
1577 -- Procedure Name : update_restructure_quote
1578 -- Description : Update multiple restructure quotes
1579 -- Business Rules :
1580 -- Parameters :
1581 -- Version : 1.0
1582 --
1583 -- End of comments
1584 PROCEDURE update_restructure_quote(
1585 p_api_version IN NUMBER,
1586 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1587 x_return_status OUT NOCOPY VARCHAR2,
1588 x_msg_count OUT NOCOPY NUMBER,
1589 x_msg_data OUT NOCOPY VARCHAR2,
1590 p_quot_tbl IN quot_tbl_type,
1591 x_quot_tbl OUT NOCOPY quot_tbl_type) AS
1592
1593 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1594 i NUMBER := 0;
1595 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1596 l_api_name CONSTANT VARCHAR2(30) := 'update_restructure_quote';
1597 l_api_version CONSTANT NUMBER := 1;
1598 BEGIN
1599
1600 --Check API version, initialize message list and create savepoint.
1601 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1602 G_PKG_NAME,
1603 p_init_msg_list,
1604 l_api_version,
1605 p_api_version,
1606 '_PVT',
1607 x_return_status);
1608
1609 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1610 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1611 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1612 RAISE OKL_API.G_EXCEPTION_ERROR;
1613 END IF;
1614
1615 -- Make sure PL/SQL table has records in it before passing
1616 IF (p_quot_tbl.COUNT > 0) THEN
1617 i := p_quot_tbl.FIRST;
1618 LOOP
1619 update_restructure_quote (
1620 p_api_version => p_api_version,
1621 p_init_msg_list => p_init_msg_list,
1622 x_return_status => l_return_status,
1623 x_msg_count => x_msg_count,
1624 x_msg_data => x_msg_data,
1625 p_quot_rec => p_quot_tbl(i),
1626 x_quot_rec => x_quot_tbl(i));
1627
1628 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1629 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1630 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1631 RAISE OKL_API.G_EXCEPTION_ERROR;
1632 END IF;
1633
1634 EXIT WHEN (i = p_quot_tbl.LAST);
1635 i := p_quot_tbl.NEXT(i);
1636 END LOOP;
1637 END IF;
1638
1639 x_return_status := l_return_status;
1640
1641 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1642
1643 EXCEPTION
1644
1645 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1646 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1647 (
1648 l_api_name,
1649 G_PKG_NAME,
1650 'OKL_API.G_RET_STS_ERROR',
1651 x_msg_count,
1652 x_msg_data,
1653 '_PVT'
1654 );
1655
1656 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1657 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1658 (
1659 l_api_name,
1660 G_PKG_NAME,
1661 'OKL_API.G_RET_STS_UNEXP_ERROR',
1662 x_msg_count,
1663 x_msg_data,
1664 '_PVT'
1665 );
1666
1667 WHEN OTHERS THEN
1668 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1669 (
1670 l_api_name,
1671 G_PKG_NAME,
1672 'OTHERS',
1673 x_msg_count,
1674 x_msg_data,
1675 '_PVT'
1676 );
1677
1678 END update_restructure_quote;
1679
1680 END OKL_AM_RESTRUCTURE_QUOTE_PVT;