1 PACKAGE BODY OKL_AM_INTEGRATION_PVT AS
2 /* $Header: OKLRKRBB.pls 120.3.12010000.3 2008/10/03 18:17:41 rkuttiya ship $ */
3
4 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := 'OKL_REQUIRED_VALUE';
5 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
6
7 -- Start of comments
8 --
9 -- Procedure Name : cancel_termination_quotes
10 -- Description : Invalidates all the termination quotes
11 -- : for the contract
12 -- Business Rules :
13 -- Parameters : contract_id and quote_id which caused the rebook process
14 -- Version : 1.0
15 -- History : SPILLAIP -- Created
16 -- : SECHAWLA 23-OCT-03 -- Changed p_quote_id parameter to p_source_trx_id
17 -- SECHAWLA 10-NOV-03 3248212 -- Changed quote status COMPLETED to COMPLETE
18 -- End of comments
19
20
21 PROCEDURE cancel_termination_quotes (p_api_version IN NUMBER,
22 p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE,
23 p_khr_id IN NUMBER,
24 p_source_trx_id IN NUMBER,
25 p_source IN VARCHAR2 DEFAULT NULL, -- rmunjulu bug 4508497
26 x_return_status OUT NOCOPY VARCHAR2,
27 x_msg_count OUT NOCOPY NUMBER,
28 x_msg_data OUT NOCOPY VARCHAR2) IS
29
30 lp_source_trx_id NUMBER := p_source_trx_id;
31 lp_khr_id NUMBER := p_khr_id;
32 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
33 l_program_name CONSTANT VARCHAR2(61) := 'cancel_termination_quotes';
34 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
35 lx_quote_tbl OKL_AM_UTIL_PVT.quote_tbl_type;
36 lp_canceled_qtev_rec OKL_QTE_PVT.qtev_rec_type;
37 lx_canceled_qtev_rec OKL_QTE_PVT.qtev_rec_type;
38 l_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE := NULL;
39 i NUMBER;
40 l_quote_id NUMBER;
41 -- cursor to check whether contract is valid or not
42 CURSOR is_khr_exists_csr IS
43 SELECT contract_number
44 FROM OKC_K_HEADERS_B
45 WHERE id = p_khr_id;
46
47 -- SECHAWLA 23-OCT-03 : Added this cursor
48 -- get the quote id from the termination transaction
49 CURSOR l_trxcontracts_csr(cp_trx_id IN NUMBER) IS
50 SELECT qte_id
51 FROM OKL_TRX_CONTRACTS
52 WHERE id = cp_trx_id;
53
54 -- rmunjulu bug 4508497 added to check the setup
55 CURSOR l_sys_prms_csr IS
56 SELECT NVL(upper(CANCEL_QUOTES_YN), 'N') CANCEL_QUOTES
57 FROM OKL_SYSTEM_PARAMS;
58
59 -- rmunjulu bug 4508497
60 l_keep_existing_quotes_yn VARCHAR2(3);
61 BEGIN
62
63 SAVEPOINT l_program_name;
64
65 IF(lp_khr_id IS NULL OR lp_khr_id = OKL_API.G_MISS_NUM) THEN
66 -- set the message and raise an exception if contract id is passed as null
67 OKL_API.set_message(p_app_name => G_APP_NAME,
68 p_msg_name => G_REQUIRED_VALUE,
69 p_token1 => G_COL_NAME_TOKEN,
70 p_token1_value => 'chr_id');
71 RAISE OKL_API.G_EXCEPTION_ERROR;
72 END IF;
73
74 -- cursor to check whether the contract id exists or not by retrieving the contract number
75 FOR l_khr_exists_rec IN is_khr_exists_csr
76 LOOP
77 l_contract_number := l_khr_exists_rec.contract_number;
78 END LOOP;
79
80 IF(l_contract_number IS NULL) THEN
81 -- set the message and raise an exception if contract id is passed as null
82 --SECHAWLA 28-JUL-04 3789019 : Use OKL application ans OKL_message instead of OKC
83 /*OKL_API.set_message(p_app_name => g_app_name,
84 p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
85 p_token1 => 'COL_NAME',
86 p_token1_value => 'CONTRACT_NUMBER');
87 */
88 OKL_API.set_message(p_app_name => 'OKL',
89 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
90 p_token1 => 'COL_NAME',
91 p_token1_value => 'CONTRACT_NUMBER');
92 END IF;
93
94 -- rmunjulu bug 4508497 ++++++++ start ++++++++++++++++++++++
95 l_keep_existing_quotes_yn := 'N';
96
97 IF nvl(p_source,'*') = 'EVERGREEN' THEN
98
99 l_keep_existing_quotes_yn := 'N'; -- always cancel quotes whatever be the setup
100
101 ELSIF nvl(p_source,'*') = 'ALT' THEN -- source is rebook partial termination
102
103 -- Check system option
104 OPEN l_sys_prms_csr;
105 FETCH l_sys_prms_csr INTO l_keep_existing_quotes_yn;
106 IF l_sys_prms_csr%NOTFOUND THEN
107 l_keep_existing_quotes_yn := 'N';
108 END IF;
109 CLOSE l_sys_prms_csr;
110
111 ELSIF nvl(p_source,'*') = 'PPD' THEN -- source is rebook Principal Paydown
112
113 l_keep_existing_quotes_yn := 'N'; -- always cancel quotes whatever be the setup
114
115 ELSIF nvl(p_source,'*') <> '*' THEN -- source is some other transaction
116
117 l_keep_existing_quotes_yn := 'N'; -- always cancel quotes whatever be the setup
118
119 ELSIF p_source IS NULL THEN -- no value passed means online rebook
120
121 -- Check system option
122 OPEN l_sys_prms_csr;
123 FETCH l_sys_prms_csr INTO l_keep_existing_quotes_yn;
124 IF l_sys_prms_csr%NOTFOUND THEN
125 l_keep_existing_quotes_yn := 'N';
126 END IF;
127 CLOSE l_sys_prms_csr;
128
129 END IF;
130 -- rmunjulu bug 4508497 ++++++++ start ++++++++++++++++++++++
131
132 -- rmunjulu bug 4508497 -- added condition -- only if keep existing quotes is NO
133 IF nvl(l_keep_existing_quotes_yn,'N') = 'N' THEN
134
135 -- Get all quotes for the contract
136 OKL_AM_UTIL_PVT.get_all_term_qte_for_contract(p_khr_id => lp_khr_id,
137 x_quote_tbl => lx_quote_tbl,
138 x_return_status => l_return_status);
139
140 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
141 -- Loop thru the quotes for the contract
142 IF lx_quote_tbl.COUNT > 0 THEN
143 -- SECHAWLA 23-OCT-03 Added the following piece of code to get the quote id if a termination transaction
144 -- exists and was created thru a quote.
145 IF lp_source_trx_id IS NOT NULL THEN
146 OPEN l_trxcontracts_csr(lp_source_trx_id);
147 FETCH l_trxcontracts_csr INTO l_quote_id;
148 IF l_trxcontracts_csr%NOTFOUND THEN
149 OKC_API.set_message( p_app_name => 'OKC',
150 p_msg_name => G_INVALID_VALUE,
151 p_token1 => G_COL_NAME_TOKEN,
152 p_token1_value => 'SOURCE_TRX_ID');
153
154 RAISE OKL_API.G_EXCEPTION_ERROR;
155 END IF;
156 CLOSE l_trxcontracts_csr;
157 END IF;
158 -- SECHAWLA 23-OCT-03 : End new code
159
160 i := lx_quote_tbl.FIRST;
161 LOOP
162 -- if the quote id different and quote not consolidated and not
163 -- completed/canceled then cancel it
164 -- do not cancel the quote that initiated the rebook
165 -- l_quote_id will be null if the termination is not initiated thru a quote
166 IF ((l_quote_id IS NULL OR lx_quote_tbl(i).id <> l_quote_id)
167 AND NVL(lx_quote_tbl(i).consolidated_yn,'N') <> 'Y'
168 -- SECHAWLA 28-JUL-04 3788993 : Added check for ACCEPTED quotes
169 AND lx_quote_tbl(i).qst_code NOT IN('COMPLETE','CANCELLED','ACCEPTED')) THEN -- 3248212 Changed COMPLETED to COMPLETE
170
171 -- set the canceled qtev rec
172 lp_canceled_qtev_rec.id := lx_quote_tbl(i).id;
173 lp_canceled_qtev_rec.qst_code := 'CANCELLED';
174
175 -- update the quote to canceled
176 OKL_TRX_QUOTES_PUB.update_trx_quotes( p_api_version => p_api_version,
177 p_init_msg_list => OKL_API.G_FALSE,
178 x_return_status => l_return_status,
179 x_msg_count => x_msg_count,
180 x_msg_data => x_msg_data,
181 p_qtev_rec => lp_canceled_qtev_rec,
182 x_qtev_rec => lx_canceled_qtev_rec);
183
184 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
185 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
186 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
187 RAISE OKL_API.G_EXCEPTION_ERROR;
188 END IF;
189 END IF;
190 EXIT WHEN i = lx_quote_tbl.LAST; -- exit when the last record is processed
191 i := lx_quote_tbl.NEXT(i);
192 END LOOP;
193 END IF;
194 ELSE
195 -- set the message if OKL_AM_UTIL_PVT.get_all_term_quotes_for_contract throws error
196 -- with contract number as token value
197 OKL_API.SET_MESSAGE(p_app_name => 'OKL',
198 p_msg_name => 'OKL_AM_ERR_RET_QTES',
199 p_token1 => 'CONTRACT_NUMBER',
200 p_token1_value => l_contract_number);
201 --message("Error retrieving existing quotes for this contract for cancellation");
202 RAISE OKL_API.G_EXCEPTION_ERROR;
203 END IF;
204 END IF;
205 x_return_status := G_RET_STS_SUCCESS;
206
207 EXCEPTION
208 -- roll back and return the status as error(E)
209 WHEN OKL_API.G_EXCEPTION_ERROR THEN
210 ROLLBACK TO l_program_name;
211 x_return_status := G_RET_STS_ERROR;
212 -- roll back and return the status as un expected error(U)
213 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
214 ROLLBACK TO l_program_name;
215 x_return_status := G_RET_STS_UNEXP_ERROR;
216 -- set the message, roll back and return the status as un expected error(U)
217 WHEN OTHERS THEN
218 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
219 p_msg_name => G_DB_ERROR,
220 p_token1 => G_PROG_NAME_TOKEN,
221 p_token1_value => l_api_name,
222 p_token2 => G_SQLCODE_TOKEN,
223 p_token2_value => sqlcode,
224 p_token3 => G_SQLERRM_TOKEN,
225 p_token3_value => sqlerrm);
226 ROLLBACK TO l_program_name;
227 x_return_status := G_RET_STS_UNEXP_ERROR;
228
229 END cancel_termination_quotes; -- end of the procedure
230
231 END OKL_AM_INTEGRATION_PVT;
[Home] [Help]