[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_CONSOLIDATED_QTE_PVT
Source
1 PACKAGE BODY OKL_AM_CONSOLIDATED_QTE_PVT AS
2 /* $Header: OKLRCNQB.pls 120.3 2007/12/14 13:57:33 nikshah ship $ */
3
4 -- GLOBAL VARIABLES
5 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
6 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
7 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
8 G_MODULE_NAME CONSTANT VARCHAR2(500) := 'okl.am.plsql.okl_am_consolidated_qte_pvt.';
9
10 -- Start of comments
11 --
12 -- Procedure Name : get_set_database_values
13 -- Description : Gets the values of the quote from DB and if not passed
14 -- assigns them to out parameter
15 -- Business Rules :
16 -- Parameters :
17 -- Version : 1.0
18 --
19 -- End of comments
20 PROCEDURE get_and_set_database_values(
21 p_qtev_tbl IN qtev_tbl_type,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_qtev_tbl OUT NOCOPY qtev_tbl_type) IS
24
25 -- Cursor to get the quote values from the database
26 CURSOR get_quote_values_csr (p_qte_id IN NUMBER) IS
27 SELECT id,
28 qst_code,
29 qtp_code,
30 qrs_code,
31 khr_id,
32 accepted_yn,
33 consolidated_yn,
34 early_termination_yn,
35 partial_yn,
36 consolidated_qte_id,
37 date_effective_from,
38 date_effective_to,
39 quote_number
40 FROM OKL_TRX_QUOTES_V
41 WHERE id = p_qte_id;
42
43 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
44 get_quote_values_rec get_quote_values_csr%ROWTYPE;
45 i NUMBER;
46 lp_qtev_tbl qtev_tbl_type := p_qtev_tbl;
47 lx_qtev_tbl qtev_tbl_type := p_qtev_tbl;
48 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'get_and_set_database_values';
49 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
50 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
51 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
52
53
54 BEGIN
55 IF (is_debug_procedure_on) THEN
56 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
57 END IF;
58 IF (is_debug_statement_on) THEN
59 FOR i IN p_qtev_tbl.FIRST..p_qtev_tbl.LAST LOOP
60 IF (p_qtev_tbl.exists(i)) THEN
61 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').id : ' || p_qtev_tbl(i).id);
62 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').qst_code : ' || p_qtev_tbl(i).qst_code);
63 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').qtp_code : ' || p_qtev_tbl(i).qtp_code);
64 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').qrs_code : ' || p_qtev_tbl(i).qrs_code);
65 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').khr_id : ' || p_qtev_tbl(i).khr_id);
66 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').accepted_yn : ' || p_qtev_tbl(i).accepted_yn);
67 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').consolidated_yn : ' || p_qtev_tbl(i).consolidated_yn);
68 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').early_termination_yn : ' || p_qtev_tbl(i).early_termination_yn);
69 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').partial_yn : ' || p_qtev_tbl(i).partial_yn);
70 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').consolidated_qte_id : ' || p_qtev_tbl(i).consolidated_qte_id);
71 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').date_effective_from : ' || p_qtev_tbl(i).date_effective_from);
72 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').date_effective_to : ' || p_qtev_tbl(i).date_effective_to);
73 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').quote_number : ' || p_qtev_tbl(i).quote_number);
74 END IF;
75 END LOOP;
76 END IF;
77
78 i := lp_qtev_tbl.FIRST;
79
80 LOOP
81
82 OPEN get_quote_values_csr (lp_qtev_tbl(i).id);
83 FETCH get_quote_values_csr INTO get_quote_values_rec;
84 IF get_quote_values_csr%NOTFOUND OR get_quote_values_rec.id IS NULL THEN
85 -- Invalid value for id.
86 OKL_API.SET_MESSAGE(p_app_name => OKC_API.G_APP_NAME,
87 p_msg_name => OKC_API.G_INVALID_VALUE,
88 p_token1 => G_COL_NAME_TOKEN,
89 p_token1_value => 'id');
90 RAISE G_EXCEPTION_HALT_VALIDATION;
91 END IF;
92 CLOSE get_quote_values_csr;
93
94
95 IF lp_qtev_tbl(i).qst_code IS NULL
96 OR lp_qtev_tbl(i).qst_code = OKL_API.G_MISS_CHAR THEN
97 lx_qtev_tbl(i).qst_code := get_quote_values_rec.qst_code;
98 END IF;
99
100 IF lp_qtev_tbl(i).qtp_code IS NULL
101 OR lp_qtev_tbl(i).qtp_code = OKL_API.G_MISS_CHAR THEN
102 lx_qtev_tbl(i).qtp_code := get_quote_values_rec.qtp_code;
103 END IF;
104
105 IF lp_qtev_tbl(i).qrs_code IS NULL
106 OR lp_qtev_tbl(i).qrs_code = OKL_API.G_MISS_CHAR THEN
107 lx_qtev_tbl(i).qrs_code := get_quote_values_rec.qrs_code;
108 END IF;
109
110 IF lp_qtev_tbl(i).khr_id IS NULL
111 OR lp_qtev_tbl(i).khr_id = OKL_API.G_MISS_NUM THEN
112 lx_qtev_tbl(i).khr_id := get_quote_values_rec.khr_id;
113 END IF;
114
115 IF lp_qtev_tbl(i).accepted_yn IS NULL
116 OR lp_qtev_tbl(i).accepted_yn = OKL_API.G_MISS_CHAR THEN
117 lx_qtev_tbl(i).accepted_yn := get_quote_values_rec.accepted_yn;
118 END IF;
119
120 IF lp_qtev_tbl(i).consolidated_yn IS NULL
121 OR lp_qtev_tbl(i).consolidated_yn = OKL_API.G_MISS_CHAR THEN
122 lx_qtev_tbl(i).consolidated_yn := get_quote_values_rec.consolidated_yn;
123 END IF;
124
125 IF lp_qtev_tbl(i).early_termination_yn IS NULL
126 OR lp_qtev_tbl(i).early_termination_yn = OKL_API.G_MISS_CHAR THEN
127 lx_qtev_tbl(i).early_termination_yn := get_quote_values_rec.early_termination_yn;
128 END IF;
129
130 IF lp_qtev_tbl(i).partial_yn IS NULL
131 OR lp_qtev_tbl(i).partial_yn = OKL_API.G_MISS_CHAR THEN
132 lx_qtev_tbl(i).partial_yn := get_quote_values_rec.partial_yn;
133 END IF;
134
135 IF lp_qtev_tbl(i).consolidated_qte_id IS NULL
136 OR lp_qtev_tbl(i).consolidated_qte_id = OKL_API.G_MISS_NUM THEN
137 lx_qtev_tbl(i).consolidated_qte_id := get_quote_values_rec.consolidated_qte_id;
138 END IF;
139
140 IF lp_qtev_tbl(i).date_effective_from IS NULL
141 OR lp_qtev_tbl(i).date_effective_from = OKL_API.G_MISS_DATE THEN
142 lx_qtev_tbl(i).date_effective_from := get_quote_values_rec.date_effective_from;
143 END IF;
144
145 IF lp_qtev_tbl(i).date_effective_to IS NULL
146 OR lp_qtev_tbl(i).date_effective_to = OKL_API.G_MISS_DATE THEN
147 lx_qtev_tbl(i).date_effective_to := get_quote_values_rec.date_effective_to;
148 END IF;
149
150 IF lp_qtev_tbl(i).quote_number IS NULL
151 OR lp_qtev_tbl(i).quote_number = OKL_API.G_MISS_NUM THEN
152 lx_qtev_tbl(i).quote_number := get_quote_values_rec.quote_number;
153 END IF;
154
155 EXIT WHEN (i = lp_qtev_tbl.LAST);
156 i := lp_qtev_tbl.NEXT(i);
157 END LOOP;
158
159 -- Set the return status
160 x_return_status := l_return_status;
161 x_qtev_tbl := lx_qtev_tbl;
162 IF (is_debug_procedure_on) THEN
163 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
164 END IF;
165
166 EXCEPTION
167 WHEN G_EXCEPTION_HALT_VALIDATION THEN
168 IF (is_debug_exception_on) THEN
169 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_HALT_VALIDATION');
170 END IF;
171
172 IF get_quote_values_csr%ISOPEN THEN
173 CLOSE get_quote_values_csr;
174 END IF;
175
176 x_return_status := OKL_API.G_RET_STS_ERROR;
177
178 WHEN OTHERS THEN
179 IF (is_debug_exception_on) THEN
180 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
181 || sqlcode || ' , SQLERRM : ' || sqlerrm);
182 END IF;
183
184 IF get_quote_values_csr%ISOPEN THEN
185 CLOSE get_quote_values_csr;
186 END IF;
187
188 OKL_API.set_message(p_app_name => g_app_name,
189 p_msg_name => g_unexpected_error,
190 p_token1 => g_sqlcode_token,
191 p_token1_value => sqlcode,
192 p_token2 => g_sqlerrm_token,
193 p_token2_value => sqlerrm);
194
195 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
196 END get_and_set_database_values;
197
198
199 -- Start of comments
200 --
201 -- Procedure Name : set_consolidated_quote_rec
202 -- Description : Set the quote record for consolidated quote
203 -- Business Rules :
204 -- Parameters :
205 -- Version : 1.0
206 --
207 -- End of comments
208 PROCEDURE set_consolidated_quote_rec(
209 p_qtev_rec IN qtev_rec_type,
210 x_return_status OUT NOCOPY VARCHAR2,
211 x_qtev_rec OUT NOCOPY qtev_rec_type) IS
212
213 l_quote_status VARCHAR2(200) := 'DRAFTED';
214 l_quote_reason VARCHAR2(200) := 'EOT';
215 l_sys_date DATE;
216 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
217 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'set_consolidated_quote_rec';
218 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
219 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
220 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
221
222 BEGIN
223 IF (is_debug_procedure_on) THEN
224 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
225 END IF;
226 IF (is_debug_statement_on) THEN
227 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.date_effective_from: ' || p_qtev_rec.date_effective_from);
228 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.date_effective_to: ' || p_qtev_rec.date_effective_to);
229 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.date_requested: ' || p_qtev_rec.date_requested);
230 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.date_proposal: ' || p_qtev_rec.date_proposal);
231 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.qtp_code: ' || p_qtev_rec.qtp_code);
232 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.qst_code: ' || p_qtev_rec.qst_code);
233 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.qrs_code: ' || p_qtev_rec.qrs_code);
237 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.accepted_yn: ' || p_qtev_rec.accepted_yn);
234 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.early_termination_yn: ' || p_qtev_rec.early_termination_yn);
235 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.partial_yn: ' || p_qtev_rec.partial_yn);
236 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.consolidated_yn: ' || p_qtev_rec.consolidated_yn);
238 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.approved_yn: ' || p_qtev_rec.approved_yn);
239 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.preproceeds_yn: ' || p_qtev_rec.preproceeds_yn);
240 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.summary_format_yn: ' || p_qtev_rec.summary_format_yn);
241 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.payment_received_yn: ' || p_qtev_rec.payment_received_yn);
242 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.khr_id: ' || p_qtev_rec.khr_id);
243 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.currency_code: ' || p_qtev_rec.currency_code);
244 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.currency_conversion_code: ' || p_qtev_rec.currency_conversion_code);
245 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.currency_conversion_type: ' || p_qtev_rec.currency_conversion_type);
246 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.currency_conversion_rate: ' || p_qtev_rec.currency_conversion_rate);
247 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.currency_conversion_date: ' || p_qtev_rec.currency_conversion_date);
248 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.id: ' || p_qtev_rec.id);
249 END IF;
250 -- Get the sysdate
251 SELECT sysdate INTO l_sys_date FROM DUAL;
252
253 -- Set the date_effective_from to date effective from of first quote
254 x_qtev_rec.date_effective_from := p_qtev_rec.date_effective_from;
255
256 -- Set the date_effective_to to date effective to of first quote
257 x_qtev_rec.date_effective_to := p_qtev_rec.date_effective_to;
258
259 -- Set the date_requested to sysdate
260 x_qtev_rec.date_requested := l_sys_date;
261
262 -- Set the date_proposal to sysdate
263 x_qtev_rec.date_proposal := l_sys_date;
264
265 -- Set the qtp_code (quote type) to qtp of first quote
266 x_qtev_rec.qtp_code := p_qtev_rec.qtp_code;
267
268 -- Set the qst_code (quote status) to qst of first quote
269 x_qtev_rec.qst_code := p_qtev_rec.qst_code;
270
271 -- Set the qrs_code (quote reason) to qrs of first quote
272 x_qtev_rec.qrs_code := p_qtev_rec.qrs_code;
273
274 x_qtev_rec.early_termination_yn := p_qtev_rec.early_termination_yn;
275
276 x_qtev_rec.partial_yn := p_qtev_rec.partial_yn;
277
278 -- Set the requested_by to 1
279 x_qtev_rec.requested_by := 1; --***OKL_QTE_PVT.Validate_Requested_By
280
281 -- Set the consolidated_yn to YES
282 x_qtev_rec.consolidated_yn := G_YES;
283
284 -- Always NO during consolidated quote creation
285 x_qtev_rec.accepted_yn := G_NO;
286 x_qtev_rec.approved_yn := G_NO;
287 x_qtev_rec.preproceeds_yn := G_NO;
288 x_qtev_rec.summary_format_yn := G_NO;
289 x_qtev_rec.payment_received_yn := G_NO;
290
291 -- Set KHR_ID for now -- will remove later once OKL_AM_QUOTES_UV changes
292 x_qtev_rec.khr_id := p_qtev_rec.khr_id;
293
294 -- Set the return status
295 x_return_status := l_return_status;
296 IF (is_debug_procedure_on) THEN
297 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
298 END IF;
299
300 EXCEPTION
301 WHEN OTHERS THEN
302 IF (is_debug_exception_on) THEN
303 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
304 || sqlcode || ' , SQLERRM : ' || sqlerrm);
305 END IF;
306 OKL_API.set_message(p_app_name => g_app_name,
307 p_msg_name => g_unexpected_error,
308 p_token1 => g_sqlcode_token,
309 p_token1_value => sqlcode,
310 p_token2 => g_sqlerrm_token,
311 p_token2_value => sqlerrm);
312
313 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
314 END set_consolidated_quote_rec;
315
316
317
318 -- Start of comments
319 --
320 -- Procedure Name : validate_quotes
321 -- Description : Validates all the quotes passed
322 -- Business Rules :
323 -- Parameters :
324 -- Version : 1.0
325 -- History : RMUNJULU 23-DEC-02 2726739 Added code to check currency
326 -- codes match
327 -- rmunjulu 3884338, initialize all date_eff_from and date_eff_to to NULL
328 --
329 -- End of comments
330 PROCEDURE validate_quotes (
331 p_api_version IN NUMBER,
332 p_init_msg_list IN VARCHAR2,
333 x_return_status OUT NOCOPY VARCHAR2,
334 x_msg_count OUT NOCOPY NUMBER,
335 x_msg_data OUT NOCOPY VARCHAR2,
336 p_control_flag IN VARCHAR2,
337 p_qtev_tbl IN qtev_tbl_type,
338 x_qtev_tbl OUT NOCOPY qtev_tbl_type) IS
339
340 -- Cursor to get the product id of the contract for the quote
341 CURSOR prod_id_csr (p_khr_id IN NUMBER) IS
342 SELECT K.pdt_id,
343 K.contract_number
344 FROM OKL_K_HEADERS_FULL_V K
345 WHERE K.id = p_khr_id;
346
347 -- Cursor to get the recipient id of the quote
348 CURSOR recpt_id_csr (p_qte_id IN NUMBER) IS
349 SELECT Q.recipient_id
350 FROM OKL_AM_QUOTES_UV Q
351 WHERE Q.id = p_qte_id
352 AND Q.quote_party_role = 'RECIPIENT';
353
354 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
355 i NUMBER;
356 j NUMBER;
357 lp_qtev_tbl qtev_tbl_type := p_qtev_tbl;
358 l_qtev_tbl qtev_tbl_type := p_qtev_tbl;
359 l_date_effective_from DATE;
360 l_date_effective_to DATE;
361 l_quote_type VARCHAR2(200);
362 l_quote_status VARCHAR2(200);
363 l_product_id NUMBER;
364 lp_product_id NUMBER;
365 l_recipient_id NUMBER;
366 lp_recipient_id NUMBER;
367 l_khr_id_first NUMBER;
368 l_contract_number VARCHAR2(200);
369 l_qte_number_first NUMBER;
370
371 TYPE khr_rec_type IS RECORD (
372 khr_id NUMBER,
373 quote_number NUMBER);
374
375 TYPE khr_tbl_type IS TABLE OF khr_rec_type INDEX BY BINARY_INTEGER;
376
377 l_khr_tbl khr_tbl_type;
378
379
380 -- RMUNJULU 23-DEC-02 2726739 Added variables
381 l_chr_currency_code VARCHAR2(15);
382 l_contract_currency_code VARCHAR2(15);
383 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'validate_quotes';
384 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
385 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
386 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
387
388 BEGIN
389 IF (is_debug_procedure_on) THEN
390 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
391 END IF;
392 IF (is_debug_statement_on) THEN
393 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_control_flag: '||p_control_flag);
394 FOR i IN p_qtev_tbl.FIRST..p_qtev_tbl.LAST LOOP
395 IF (p_qtev_tbl.exists(i)) THEN
396 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').id : ' || p_qtev_tbl(i).id);
397 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').qst_code : ' || p_qtev_tbl(i).qst_code);
398 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').qtp_code : ' || p_qtev_tbl(i).qtp_code);
399 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').qrs_code : ' || p_qtev_tbl(i).qrs_code);
400 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').khr_id : ' || p_qtev_tbl(i).khr_id);
401 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').accepted_yn : ' || p_qtev_tbl(i).accepted_yn);
402 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').consolidated_yn : ' || p_qtev_tbl(i).consolidated_yn);
406 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').date_effective_from : ' || p_qtev_tbl(i).date_effective_from);
403 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').early_termination_yn : ' || p_qtev_tbl(i).early_termination_yn);
404 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').partial_yn : ' || p_qtev_tbl(i).partial_yn);
405 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').consolidated_qte_id : ' || p_qtev_tbl(i).consolidated_qte_id);
407 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').date_effective_to : ' || p_qtev_tbl(i).date_effective_to);
408 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').quote_number : ' || p_qtev_tbl(i).quote_number);
409 END IF;
410 END LOOP;
411 END IF;
412
413 -- Check if min of 2 quotes selected
414 -- The validation needs to go before the validation of quote id since screen
415 -- can pass g_miss_num for 1st quote if no quotes selected.
416 IF lp_qtev_tbl.COUNT < 2 THEN
417
418 -- Message: A minimum of 2 quotes needs to be selected to create a consolidated quote.
419 OKL_API.set_message(p_app_name => 'OKL',
420 p_msg_name => 'OKL_AM_QTE_TWO_MIN_ERR');
421
422 RAISE G_EXCEPTION_HALT_VALIDATION;
423
424 END IF;
425
426 -- rmunjulu 3755190, initialize all date_eff_from and date_eff_to to NULL,
427 -- as the G_MISS_DATE they are being set to is incorrect.
428 -- The proper date will then be fetched from the database in procedure
429 -- get_and_set_database_values which is called below
430 FOR k IN 1..lp_qtev_tbl.count LOOP
431
432 lp_qtev_tbl(k).date_effective_from := NULL;
433 lp_qtev_tbl(k).date_effective_to := NULL;
434
435 END LOOP;
436
437 -- Get and Set the qtev tbl
438 get_and_set_database_values(
439 p_qtev_tbl => lp_qtev_tbl,
440 x_return_status => l_return_status,
441 x_qtev_tbl => l_qtev_tbl);
442 IF (is_debug_statement_on) THEN
443 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called get_and_set_database_values , return status: ' || l_return_status);
444 END IF;
445
446 -- Raise error if this fails
447 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
448 RAISE G_EXCEPTION_HALT_VALIDATION;
449 END IF;
450
451 -- Check if first quote already accepted
452 IF l_qtev_tbl(l_qtev_tbl.FIRST).accepted_yn = G_YES
453 OR l_qtev_tbl(l_qtev_tbl.FIRST).qst_code = 'ACCEPTED' THEN
454
455 -- Message: The selected quotes cannot be consolidated because one or
456 -- more of them is already accepted.
457 OKL_API.set_message(p_app_name => 'OKL',
458 p_msg_name => 'OKL_AM_QTE_ALRDY_ACCEPTED');
459
460 RAISE G_EXCEPTION_HALT_VALIDATION;
461
462 END IF;
463
464 -- Check if first quote already consolidated
465 IF (l_qtev_tbl(l_qtev_tbl.FIRST).consolidated_yn = G_YES) THEN
466
467 -- Message: The selected quotes cannot be consolidated because one or
468 -- more of them are themselves consolidated.
469 OKL_API.set_message(p_app_name => 'OKL',
470 p_msg_name => 'OKL_AM_QTE_ALRDY_CONSOLDTD');
471
472 RAISE G_EXCEPTION_HALT_VALIDATION;
473
474 END IF;
475
476 -- Check if first quote partial
477 IF (l_qtev_tbl(l_qtev_tbl.FIRST).partial_yn = G_YES) THEN
478
479 -- Message: The selected quotes cannot be consolidated because one or
480 -- more of them are partial.
481 OKL_API.set_message(p_app_name => 'OKL',
482 p_msg_name => 'OKL_AM_QTE_ALRDY_PARTIAL');
483
484 RAISE G_EXCEPTION_HALT_VALIDATION;
485
486 END IF;
487
488 --Only if create do this check
489 -- ( since this wont happen from screen we do not need a user friendly message)
490 IF p_control_flag = 'CREATE' THEN
491 -- Check if first quote already part of another consolidated quote
492 IF (l_qtev_tbl(l_qtev_tbl.FIRST).consolidated_qte_id IS NOT NULL)
493 AND (l_qtev_tbl(l_qtev_tbl.FIRST).consolidated_qte_id <> OKL_API.G_MISS_NUM) THEN
494 -- Invalid value for consolidated_qte_id.
495 OKL_API.SET_MESSAGE(p_app_name => OKC_API.G_APP_NAME,
496 p_msg_name => OKC_API.G_INVALID_VALUE,
497 p_token1 => G_COL_NAME_TOKEN,
498 p_token1_value => 'consolidated_qte_id');
499
500 RAISE G_EXCEPTION_HALT_VALIDATION;
501
502 END IF;
503 END IF;
504
505 -- Get the contract ids of all the full quotes passed and check if more than
506 -- one full quote selected for the same contract
507 IF (l_qtev_tbl.COUNT > 1) THEN
508
509 i := l_qtev_tbl.FIRST;
510 j := 1;
511 LOOP
512
513 -- Get the contract id and qte id values into table if full quote
514 IF (l_qtev_tbl(i).partial_yn <> 'Y') THEN
515
516 l_khr_tbl(j).khr_id := l_qtev_tbl(i).khr_id;
517 l_khr_tbl(j).quote_number := l_qtev_tbl(i).quote_number;
518
519 END IF;
520
521 EXIT WHEN (i = l_qtev_tbl.LAST);
522 i := l_qtev_tbl.NEXT(i);
523 j := j+1;
524 END LOOP;
525
526 --Check if same khr_id exists more than once
527 IF (l_khr_tbl.COUNT > 1) THEN
528
529 i := l_khr_tbl.FIRST;
530 LOOP
531
532 l_khr_id_first := l_khr_tbl(i).khr_id;
533 l_qte_number_first := l_khr_tbl(i).quote_number;
534 j := i+1;
535 LOOP
536
537 -- If khr_id same for 2 quotes then get the qte numbers and set message and exit
538 IF l_khr_id_first = l_khr_tbl(j).khr_id THEN
539
540 -- Get the contract number for khr_id and set message and exit loops
541 OPEN prod_id_csr(l_khr_id_first);
542 FETCH prod_id_csr INTO l_product_id, l_contract_number;
546 -- for the same contract CONTRACT_NUMBER.
543 CLOSE prod_id_csr;
544
545 -- Cannot consolidate multiple full quotes (quote 1 = QTE_1 and quote 2 = QTE_2)
547 OKL_API.SET_MESSAGE(p_app_name => 'OKL',
548 p_msg_name => 'OKL_AM_CONS_FULL_QTE_ERR',
549 p_token1 => 'QTE_1',
550 p_token1_value => l_qte_number_first,
551 p_token2 => 'QTE_2',
552 p_token2_value => l_khr_tbl(j).quote_number,
553 p_token3 => 'CONTRACT_NUMBER',
554 p_token3_value => l_contract_number);
555
556 RAISE G_EXCEPTION_HALT_VALIDATION;
557
558 END IF;
559
560 EXIT WHEN (j = l_khr_tbl.LAST);
561 j := l_khr_tbl.NEXT(j);
562 END LOOP;
563
564 EXIT WHEN (i = l_khr_tbl.COUNT - 1);
565 i := l_khr_tbl.NEXT(i);
566 END LOOP;
567
568 END IF;
569
570 END IF;
571
572 -- if more than one quote passed
573 IF (l_qtev_tbl.COUNT > 1) THEN
574
575 -- Get the values of quote type, product, recipient, effective from,
576 -- effective to, quote status of first quote and compare with the rest
577 l_date_effective_from := TRUNC(l_qtev_tbl(l_qtev_tbl.FIRST).date_effective_from);
578 l_date_effective_to := TRUNC(l_qtev_tbl(l_qtev_tbl.FIRST).date_effective_to);
579 l_quote_type := l_qtev_tbl(l_qtev_tbl.FIRST).qtp_code;
580 l_quote_status := l_qtev_tbl(l_qtev_tbl.FIRST).qst_code;
581
582 -- RMUNJULU 23-DEC-02 2726739 Added code to get chr_currency_code for the
583 -- first quotes contract
584 -- Get the contract currency from AM_Util
585 l_contract_currency_code := OKL_AM_UTIL_PVT.get_chr_currency(l_qtev_tbl(l_qtev_tbl.FIRST).khr_id);
586
587 -- Get the product id of first quote
588 OPEN prod_id_csr(l_qtev_tbl(l_qtev_tbl.FIRST).khr_id);
589 FETCH prod_id_csr INTO l_product_id, l_contract_number;
590 CLOSE prod_id_csr;
591
592 -- Get the recipient id of first quote
593 OPEN recpt_id_csr(l_qtev_tbl(l_qtev_tbl.FIRST).id);
594 FETCH recpt_id_csr INTO l_recipient_id;
595 CLOSE recpt_id_csr;
596
597 i := l_qtev_tbl.FIRST + 1 ; -- safe to do this since we already know more than one quote exist
598 LOOP
599
600 -- Check if already accepted
601 IF l_qtev_tbl(i).accepted_yn = G_YES
602 OR l_qtev_tbl(i).qst_code IN ('ACCEPTED') THEN
603 -- Message: The selected quotes cannot be consolidated because one or
604 -- more of them is already accepted.
605 OKL_API.set_message(p_app_name => 'OKL',
606 p_msg_name => 'OKL_AM_QTE_ALRDY_ACCEPTED');
607 RAISE G_EXCEPTION_HALT_VALIDATION;
608 END IF;
609
610 -- Check if already consolidated
611 IF (l_qtev_tbl(i).consolidated_yn = G_YES) THEN
612 -- Message: The selected quotes cannot be consolidated because one or
613 -- more of them are themselves consolidated.
614 OKL_API.set_message(p_app_name => 'OKL',
615 p_msg_name => 'OKL_AM_QTE_ALRDY_CONSOLDTD');
616 RAISE G_EXCEPTION_HALT_VALIDATION;
617 END IF;
618
619 -- Check if quote partial
620 IF (l_qtev_tbl(i).partial_yn = G_YES) THEN
621 -- Message: The selected quotes cannot be consolidated because one or
622 -- more of them are partial.
623 OKL_API.set_message(p_app_name => 'OKL',
624 p_msg_name => 'OKL_AM_QTE_ALRDY_PARTIAL');
625 RAISE G_EXCEPTION_HALT_VALIDATION;
626 END IF;
627
628 --Only if create do this check
629 -- ( since this wont happen from screen we do not need a user friendly message)
630 IF p_control_flag = 'CREATE' THEN
631 -- Check if quote already part of another consolidated quote
632 IF (l_qtev_tbl(i).consolidated_qte_id IS NOT NULL)
633 AND (l_qtev_tbl(i).consolidated_qte_id <> OKL_API.G_MISS_NUM)THEN
634 -- Invalid value for consolidated_qte_id.
635 OKL_API.SET_MESSAGE(p_app_name => OKC_API.G_APP_NAME,
636 p_msg_name => OKC_API.G_INVALID_VALUE,
637 p_token1 => G_COL_NAME_TOKEN,
638 p_token1_value => 'consolidated_qte_id');
639 RAISE G_EXCEPTION_HALT_VALIDATION;
640 END IF;
641 END IF;
642
643 -- Compare this quote with first quote for date effective from
644 IF (TRUNC(l_qtev_tbl(i).date_effective_from) <> l_date_effective_from) THEN
645 -- Message: The selected quotes cannot be consolidated because the date effective from
646 -- is not the same for all of them.
647 OKL_API.set_message(p_app_name => 'OKL',
648 p_msg_name => 'OKL_AM_QTE_DATE_EFF_FRM_MSG');
649 RAISE G_EXCEPTION_HALT_VALIDATION;
650 END IF;
651
652 -- Compare this quote with first quote for date effective to
653 IF (TRUNC(l_qtev_tbl(i).date_effective_to) <> l_date_effective_to) THEN
654 -- Message: The selected quotes cannot be consolidated because the date effective to
655 -- is not the same for all of them.
656 OKL_API.set_message(p_app_name => 'OKL',
657 p_msg_name => 'OKL_AM_QTE_DATE_EFF_TO_MSG');
661 -- Compare this quote with first quote for quote type
658 RAISE G_EXCEPTION_HALT_VALIDATION;
659 END IF;
660
662 IF (l_qtev_tbl(i).qtp_code <> l_quote_type) THEN
663 -- Message: The selected quotes cannot be consolidated because the quote type
664 -- is not the same for all of them.
665 OKL_API.set_message(p_app_name => 'OKL',
666 p_msg_name => 'OKL_AM_QTE_TYPE_MSG');
667 RAISE G_EXCEPTION_HALT_VALIDATION;
668 END IF;
669
670 -- Compare this quote with first quote for quote status
671 IF (l_qtev_tbl(i).qst_code <> l_quote_status) THEN
672 -- Message: The selected quotes cannot be consolidated because the quote status
673 -- is not the same for all of them.
674 OKL_API.set_message(p_app_name => 'OKL',
675 p_msg_name => 'OKL_AM_QTE_STATUS_MSG');
676 RAISE G_EXCEPTION_HALT_VALIDATION;
677 END IF;
678
679 -- Get the product id for this quote
680 OPEN prod_id_csr(l_qtev_tbl(i).khr_id);
681 FETCH prod_id_csr INTO lp_product_id, l_contract_number;
682 CLOSE prod_id_csr;
683
684 -- Compare this quote with first quote for product
685 IF (lp_product_id <> l_product_id) THEN
686 -- Message: The selected quotes cannot be consolidated because the product type
687 -- is not the same for all of them.
688 OKL_API.set_message(p_app_name => 'OKL',
689 p_msg_name => 'OKL_AM_QTE_PDT_TYPE_MSG');
690 RAISE G_EXCEPTION_HALT_VALIDATION;
691 END IF;
692
693 -- Get the recipient id for this quote
694 OPEN recpt_id_csr(l_qtev_tbl(i).id);
695 FETCH recpt_id_csr INTO lp_recipient_id;
696 CLOSE recpt_id_csr;
697
698 -- Compare this quote with first quote for recipient
699 IF (lp_recipient_id <> l_recipient_id) THEN
700 -- Message: The selected quotes cannot be consolidated because the quote recipient
701 -- is not the same for all of them.
702 OKL_API.set_message(p_app_name => 'OKL',
703 p_msg_name => 'OKL_AM_QTE_RECIPIENT_MSG');
704 RAISE G_EXCEPTION_HALT_VALIDATION;
705 END IF;
706
707 -- RMUNJULU 23-DEC-02 2726739 Added condition and new message
708 -- Get the contract currency from AM_Util
709 l_chr_currency_code := OKL_AM_UTIL_PVT.get_chr_currency(l_qtev_tbl(i).khr_id);
710
711 -- If the contract currency of the first contract does not match
712 -- contract currency of the current contract then error
713 IF l_contract_currency_code <> l_chr_currency_code THEN
714
715 -- The selected quotes cannot be consolidated because currency
716 -- is not the same for all of them.
717 OKL_API.set_message(p_app_name => 'OKL',
718 p_msg_name => 'OKL_AM_QTE_CURRENCY_MSG');
719
720 RAISE G_EXCEPTION_HALT_VALIDATION;
721
722 END IF;
723
724 EXIT WHEN (i = l_qtev_tbl.LAST);
725 i := l_qtev_tbl.NEXT(i);
726 END LOOP;
727 END IF;
728
729 x_return_status := l_return_status;
730 x_qtev_tbl := l_qtev_tbl;
731 IF (is_debug_procedure_on) THEN
732 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
733 END IF;
734
735 EXCEPTION
736 WHEN G_EXCEPTION_HALT_VALIDATION THEN
737 IF (is_debug_exception_on) THEN
738 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_HALT_VALIDATION');
739 END IF;
740
741 IF prod_id_csr%ISOPEN THEN
742 CLOSE prod_id_csr;
743 END IF;
744
745 IF recpt_id_csr%ISOPEN THEN
746 CLOSE recpt_id_csr;
747 END IF;
748
749 x_return_status := OKL_API.G_RET_STS_ERROR;
750
751 WHEN OTHERS THEN
752 IF (is_debug_exception_on) THEN
753 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
754 || sqlcode || ' , SQLERRM : ' || sqlerrm);
755 END IF;
756
757 IF prod_id_csr%ISOPEN THEN
758 CLOSE prod_id_csr;
759 END IF;
760
761 IF recpt_id_csr%ISOPEN THEN
762 CLOSE recpt_id_csr;
763 END IF;
764
765 OKL_API.set_message(p_app_name => g_app_name,
766 p_msg_name => g_unexpected_error,
767 p_token1 => g_sqlcode_token,
768 p_token1_value => sqlcode,
769 p_token2 => g_sqlerrm_token,
770 p_token2_value => sqlerrm);
771
772 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
773 END validate_quotes;
774
775
776
777
778 -- Start of comments
779 --
780 -- Function Name : set_currency_defaults
781 -- Description : This procedure Defaults the Multi-Currency Columns for
782 -- consolidated quote
783 -- Business Rules :
784 -- Parameters : Input parameters : p_first_qtev_rec, px_qtev_rec, p_sys_date
785 -- Version : 1.0
786 -- History : 23-DEC-02 RMUNJULU 2726739 Created
787 -- End of comments
788 PROCEDURE set_currency_defaults(
789 p_first_qtev_rec IN qtev_rec_type,
790 px_qtev_rec IN OUT NOCOPY qtev_rec_type,
791 p_sys_date IN DATE,
792 x_return_status OUT NOCOPY VARCHAR2) IS
793
794 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
795 l_functional_currency_code VARCHAR2(15);
796 l_contract_currency_code VARCHAR2(15);
797
798 -- Since we do not use the conversion columns for the consolidated quote
799 -- set a hardcoded value for these columns
800 l_currency_conversion_type VARCHAR2(30) := 'User';
801 l_currency_conversion_rate NUMBER := 1;
802 l_currency_conversion_date DATE := p_sys_date;
803 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'set_currency_defaults';
804 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
805 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
806 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
807
808
809 BEGIN
810 IF (is_debug_procedure_on) THEN
811 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
812 END IF;
813 IF (is_debug_statement_on) THEN
814 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_sys_date: '||p_sys_date);
815 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_first_qtev_rec.khr_id: ' || p_first_qtev_rec.khr_id);
816 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_first_qtev_rec.currency_code: ' || p_first_qtev_rec.currency_code);
817 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_first_qtev_rec.currency_conversion_code: ' || p_first_qtev_rec.currency_conversion_code);
818 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_first_qtev_rec.currency_conversion_type: ' || p_first_qtev_rec.currency_conversion_type);
819 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_first_qtev_rec.currency_conversion_rate: ' || p_first_qtev_rec.currency_conversion_rate);
820 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_first_qtev_rec.currency_conversion_date: ' || p_first_qtev_rec.currency_conversion_date);
821 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_first_qtev_rec.id: ' || p_first_qtev_rec.id);
822 END IF;
823
824 -- Get the functional currency from AM_Util
825 l_functional_currency_code := OKL_AM_UTIL_PVT.get_functional_currency();
826
827 -- Get the contract currency from AM_Util
828 l_contract_currency_code := OKL_AM_UTIL_PVT.get_chr_currency(p_first_qtev_rec.khr_id);
829
830 px_qtev_rec.currency_code := l_contract_currency_code;
831 px_qtev_rec.currency_conversion_code := l_functional_currency_code;
832
833 -- If the functional currency is different from contract currency then set
834 -- currency conversion columns
835 IF l_functional_currency_code <> l_contract_currency_code THEN
836
837 -- Set the currency conversion columns
838 px_qtev_rec.currency_conversion_type := l_currency_conversion_type;
839 px_qtev_rec.currency_conversion_rate := l_currency_conversion_rate;
840 px_qtev_rec.currency_conversion_date := l_currency_conversion_date;
841
842 END IF;
843
844 -- Set the return status
845 x_return_status := l_return_status;
846 IF (is_debug_procedure_on) THEN
847 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
848 END IF;
849
850 EXCEPTION
851
852
853 WHEN OTHERS THEN
854 IF (is_debug_exception_on) THEN
855 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
856 || sqlcode || ' , SQLERRM : ' || sqlerrm);
857 END IF;
858
859 -- unexpected error
860 OKL_API.set_message(
861 p_app_name => 'OKC',
862 p_msg_name => g_unexpected_error,
863 p_token1 => g_sqlcode_token,
864 p_token1_value => sqlcode,
865 p_token2 => g_sqlerrm_token,
866 p_token2_value => sqlerrm);
867
868 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
869
870 END set_currency_defaults;
871
872
873
874
875 -- Start of comments
876 --
877 -- Procedure Name : create_consolidate_quote
878 -- Description : Procedure to create a consolidated quote for the quotes
879 -- Business Rules :
880 -- Parameters :
881 -- Version : 1.0
882 -- History : RMUNJULU 23-DEC-02 2726739 Added call to set_currency_defaults
883 --
884 -- End of comments
885 PROCEDURE create_consolidate_quote (
886 p_api_version IN NUMBER,
887 p_init_msg_list IN VARCHAR2,
888 x_return_status OUT NOCOPY VARCHAR2,
889 x_msg_count OUT NOCOPY NUMBER,
890 x_msg_data OUT NOCOPY VARCHAR2,
891 p_qtev_tbl IN qtev_tbl_type,
892 x_cons_rec OUT NOCOPY qtev_rec_type) IS
893
894 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
895 l_api_name CONSTANT VARCHAR2(30):= 'create_consolidate_quote';
896 l_api_version CONSTANT NUMBER := 1;
897 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
898 i NUMBER;
899 lp_consolidate_quote_rec qtev_rec_type;
900 lx_consolidate_quote_rec qtev_rec_type;
901 lp_qtev_tbl qtev_tbl_type := p_qtev_tbl;
902 lx_qtev_tbl qtev_tbl_type;
903
904 lp_quot_tbl qtev_tbl_type;
905 lx_quot_tbl qtev_tbl_type;
906
907 -- RMUNJULU 23-DEC-02 2726739 Added variable
908 l_sys_date DATE;
909 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'create_consolidate_quote';
910 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
911 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
912 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
913
914 BEGIN
915 IF (is_debug_procedure_on) THEN
916 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
917 END IF;
918 IF (is_debug_statement_on) THEN
919 FOR i IN p_qtev_tbl.FIRST..p_qtev_tbl.LAST LOOP
920 IF (p_qtev_tbl.exists(i)) THEN
921 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').id : ' || p_qtev_tbl(i).id);
922 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').qst_code : ' || p_qtev_tbl(i).qst_code);
923 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').qtp_code : ' || p_qtev_tbl(i).qtp_code);
924 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').qrs_code : ' || p_qtev_tbl(i).qrs_code);
925 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').khr_id : ' || p_qtev_tbl(i).khr_id);
926 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').accepted_yn : ' || p_qtev_tbl(i).accepted_yn);
927 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').consolidated_yn : ' || p_qtev_tbl(i).consolidated_yn);
928 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').early_termination_yn : ' || p_qtev_tbl(i).early_termination_yn);
929 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').partial_yn : ' || p_qtev_tbl(i).partial_yn);
930 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').consolidated_qte_id : ' || p_qtev_tbl(i).consolidated_qte_id);
931 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').date_effective_from : ' || p_qtev_tbl(i).date_effective_from);
932 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').date_effective_to : ' || p_qtev_tbl(i).date_effective_to);
933 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_tbl(' || i || ').quote_number : ' || p_qtev_tbl(i).quote_number);
934 END IF;
935 END LOOP;
936 END IF;
937
938 --Check API version, initialize message list and create savepoint.
939 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
940 G_PKG_NAME,
941 p_init_msg_list,
942 l_api_version,
943 p_api_version,
944 '_PVT',
945 x_return_status);
946
947 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
948 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
949 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
950 RAISE OKL_API.G_EXCEPTION_ERROR;
951 END IF;
952
953 -- If quotes passed
954 IF (p_qtev_tbl.COUNT > 0) THEN
955
956 -- RMUNJULU 23-DEC-02 2726739 Added select to get sysdate
957 SELECT SYSDATE INTO l_sys_date FROM DUAL;
958
959
960 -- Validate the quotes that are passed
961 validate_quotes (
962 p_api_version => p_api_version,
963 p_init_msg_list => OKL_API.G_FALSE,
964 x_return_status => l_return_status,
965 x_msg_count => x_msg_count,
966 x_msg_data => x_msg_data,
967 p_control_flag => 'CREATE',
968 p_qtev_tbl => lp_qtev_tbl,
969 x_qtev_tbl => lx_qtev_tbl);
970 IF (is_debug_statement_on) THEN
971 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called validate_quotes , return status: ' || l_return_status);
972 END IF;
973
974 -- Throw exception if validation of one or more of selected quotes fails
975 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
976 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
977 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
978 RAISE OKL_API.G_EXCEPTION_ERROR;
979 END IF;
980
981 -- Set the rec type for consolidated quote
982 set_consolidated_quote_rec(
983 p_qtev_rec => lx_qtev_tbl(p_qtev_tbl.FIRST),
984 x_return_status => l_return_status,
985 x_qtev_rec => lp_consolidate_quote_rec);
986 IF (is_debug_statement_on) THEN
987 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called set_consolidated_quote_rec , return status: ' || l_return_status);
988 END IF;
989
990 -- Throw exception if setting the consolidated quote record fails
991 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
992 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
993 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
994 RAISE OKL_API.G_EXCEPTION_ERROR;
995 END IF;
996
997
998 -- RMUNJULU 23-DEC-02 2726739 Added call to set_currency_defaults
999 -- Set the currency columns for the consolidated quote
1000 set_currency_defaults(
1001 p_first_qtev_rec => lx_qtev_tbl(p_qtev_tbl.FIRST),
1002 px_qtev_rec => lp_consolidate_quote_rec,
1003 p_sys_date => l_sys_date,
1004 x_return_status => l_return_status);
1005 IF (is_debug_statement_on) THEN
1006 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called set_currency_defaults , return status: ' || l_return_status);
1007 END IF;
1008
1009
1010 -- Throw exception if setting the consolidated quote record fails
1011 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1012 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1013 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1014 RAISE OKL_API.G_EXCEPTION_ERROR;
1015 END IF;
1016
1017
1018 -- Call the insert_row of tapi to insert the consolidated quote
1019 IF (is_debug_statement_on) THEN
1020 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_TRX_QUOTES_PUB.insert_trx_quotes');
1021 END IF;
1022 OKL_TRX_QUOTES_PUB.insert_trx_quotes (
1023 p_api_version => p_api_version,
1024 p_init_msg_list => OKL_API.G_FALSE,
1025 x_msg_count => x_msg_count,
1026 x_msg_data => x_msg_data,
1027 x_return_status => l_return_status,
1028 p_qtev_rec => lp_consolidate_quote_rec,
1029 x_qtev_rec => lx_consolidate_quote_rec);
1030 IF (is_debug_statement_on) THEN
1031 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_TRX_QUOTES_PUB.insert_trx_quotes , return status: ' || l_return_status);
1032 END IF;
1033
1034 -- Throw exception if inserting the consolidated quote record fails
1035 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1036 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1040
1037 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1038 RAISE OKL_API.G_EXCEPTION_ERROR;
1039 END IF;
1041 -- Update all the quotes passed to have a FKey link to this consolidate
1042 -- quote which was created
1043 i := lx_qtev_tbl.FIRST;
1044 LOOP
1045 -- Set the quote tbl
1046 lp_quot_tbl(i).id := lx_qtev_tbl(i).id;
1047 lp_quot_tbl(i).consolidated_qte_id := lx_consolidate_quote_rec.id;
1048
1049 EXIT WHEN (i = lx_qtev_tbl.LAST);
1050 i := lx_qtev_tbl.NEXT(i);
1051 END LOOP;
1052
1053 IF (is_debug_statement_on) THEN
1054 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_TRX_QUOTES_PUB.update_trx_quotes');
1055 END IF;
1056 -- For all quotes passed call update_row of tapi
1057 OKL_TRX_QUOTES_PUB.update_trx_quotes (
1058 p_api_version => p_api_version,
1059 p_init_msg_list => OKL_API.G_FALSE,
1060 x_return_status => l_return_status,
1061 x_msg_count => x_msg_count,
1062 x_msg_data => x_msg_data,
1063 p_qtev_tbl => lp_quot_tbl,
1064 x_qtev_tbl => lx_quot_tbl);
1065 IF (is_debug_statement_on) THEN
1066 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_TRX_QUOTES_PUB.update_trx_quotes , return status: ' || l_return_status);
1067 END IF;
1068
1069 -- Throw exception if updating the selected quotes to set the consolidated
1070 -- quote for anyone of them fails
1071 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1072 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1073 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1074 RAISE OKL_API.G_EXCEPTION_ERROR;
1075 END IF;
1076
1077 END IF;
1078
1079 -- Set the return status and out param
1080 x_return_status := l_return_status;
1081 x_cons_rec := lx_consolidate_quote_rec;
1082
1083 -- end the transaction
1084 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1085 IF (is_debug_procedure_on) THEN
1086 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
1087 END IF;
1088
1089 EXCEPTION
1090 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1091 IF (is_debug_exception_on) THEN
1092 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_ERROR');
1093 END IF;
1094 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1095 (
1096 l_api_name,
1097 G_PKG_NAME,
1098 'OKL_API.G_RET_STS_ERROR',
1099 x_msg_count,
1100 x_msg_data,
1101 '_PVT'
1102 );
1103 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1104 IF (is_debug_exception_on) THEN
1105 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_UNEXPECTED_ERROR');
1106 END IF;
1107 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1108 (
1109 l_api_name,
1110 G_PKG_NAME,
1111 'OKL_API.G_RET_STS_UNEXP_ERROR',
1112 x_msg_count,
1113 x_msg_data,
1114 '_PVT'
1115 );
1116 WHEN OTHERS THEN
1117 IF (is_debug_exception_on) THEN
1118 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1119 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1120 END IF;
1121 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1122 (
1123 l_api_name,
1124 G_PKG_NAME,
1125 'OTHERS',
1126 x_msg_count,
1127 x_msg_data,
1128 '_PVT'
1129 );
1130 END create_consolidate_quote;
1131
1132 -- Start of comments
1133 --
1134 -- Procedure Name : validate_consolidated_quote
1135 -- Description : Validates consolidated quote
1136 -- Business Rules :
1137 -- Parameters :
1138 -- Version : 1.0
1139 --
1140 -- End of comments
1141 PROCEDURE validate_consolidated_quote (
1142 p_api_version IN NUMBER,
1143 p_init_msg_list IN VARCHAR2,
1144 x_return_status OUT NOCOPY VARCHAR2,
1145 x_msg_count OUT NOCOPY NUMBER,
1146 x_msg_data OUT NOCOPY VARCHAR2,
1147 p_cons_rec IN qtev_rec_type) IS
1148
1149 -- Cursor to get consolidated quote DB values
1150 CURSOR get_cons_db_vals_csr (p_qte_id IN NUMBER) IS
1151 SELECT accepted_yn,
1152 date_effective_from
1153 FROM OKL_TRX_QUOTES_V
1154 WHERE id = p_qte_id;
1155
1156
1157 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1158 lp_cons_rec qtev_rec_type := p_cons_rec;
1159 get_cons_db_vals_rec get_cons_db_vals_csr%ROWTYPE;
1160 l_date_eff_from DATE;
1161 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'validate_consolidated_quote';
1162 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1163 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1164 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1165
1166 BEGIN
1167 IF (is_debug_procedure_on) THEN
1168 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
1169 END IF;
1170 IF (is_debug_statement_on) THEN
1171 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.id: ' || p_cons_rec.id);
1172 END IF;
1173
1174 OPEN get_cons_db_vals_csr(lp_cons_rec.id);
1175 FETCH get_cons_db_vals_csr INTO get_cons_db_vals_rec;
1176 CLOSE get_cons_db_vals_csr;
1177
1178 -- Check if date_effective_to is NULL
1179 IF p_cons_rec.date_effective_to IS NULL
1180 OR p_cons_rec.date_effective_to = OKL_API.G_MISS_DATE THEN
1181
1182 -- You must enter a value for PROMPT
1183 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
1184 p_msg_name => 'OKL_AM_REQ_FIELD_ERR',
1185 p_token1 => 'PROMPT',
1186 p_token1_value => OKL_AM_UTIL_PVT.get_ak_attribute('OKL_EFFECTIVE_TO'));
1187 RAISE G_EXCEPTION_HALT_VALIDATION;
1188
1189 END IF;
1190
1191 -- Get the date_eff_from from database if not passed
1192 IF (p_cons_rec.date_effective_from IS NOT NULL)
1193 AND (p_cons_rec.date_effective_from <> OKL_API.G_MISS_DATE) THEN
1194 l_date_eff_from := p_cons_rec.date_effective_from;
1195 ELSE
1196 l_date_eff_from := get_cons_db_vals_rec.date_effective_from;
1197 END IF;
1198
1199 -- Check date_eff_to > date_eff_from
1200 IF (l_date_eff_from IS NOT NULL)
1201 AND (l_date_eff_from <> OKL_API.G_MISS_DATE)
1202 AND (p_cons_rec.date_effective_to IS NOT NULL)
1203 AND (p_cons_rec.date_effective_to <> OKL_API.G_MISS_DATE) THEN
1204
1205 IF (TRUNC(p_cons_rec.date_effective_to) <= TRUNC(l_date_eff_from)) THEN
1206
1207 -- Message : Date Effective To DATE_EFFECTIVE_TO cannot be before
1208 -- Date Effective From DATE_EFFECTIVE_FROM.
1209 OKL_API.SET_MESSAGE(p_app_name => 'OKL',
1210 p_msg_name => 'OKL_AM_DATE_EFF_FROM_LESS_TO',
1211 p_token1 => 'DATE_EFFECTIVE_TO',
1212 p_token1_value => p_cons_rec.date_effective_to,
1213 p_token2 => 'DATE_EFFECTIVE_FROM',
1214 p_token2_value => l_date_eff_from);
1215
1216 RAISE G_EXCEPTION_HALT_VALIDATION;
1217 END IF;
1218 END IF;
1219
1220 -- Check if trying to change an already accepted consolidated quote
1221 IF get_cons_db_vals_rec.accepted_yn = G_YES
1222 AND lp_cons_rec.accepted_yn = G_NO THEN
1223 -- Quote QUOTE_NUMBER is already accepted.
1224 OKL_API.set_message( p_app_name => OKL_API.G_APP_NAME,
1225 p_msg_name => 'OKL_AM_QUOTE_ALREADY_ACCP',
1226 p_token1 => 'QUOTE_NUMBER',
1227 p_token1_value => lp_cons_rec.quote_number);
1228 RAISE G_EXCEPTION_HALT_VALIDATION;
1229 END IF;
1230
1231 x_return_status := l_return_status;
1232 IF (is_debug_procedure_on) THEN
1233 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
1234 END IF;
1235
1236 EXCEPTION
1237 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1238 IF (is_debug_exception_on) THEN
1239 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_HALT_VALIDATION');
1240 END IF;
1241
1242 IF get_cons_db_vals_csr%ISOPEN THEN
1243 CLOSE get_cons_db_vals_csr;
1244 END IF;
1245
1246 x_return_status := OKL_API.G_RET_STS_ERROR;
1247
1248 WHEN OTHERS THEN
1249 IF (is_debug_exception_on) THEN
1250 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1251 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1252 END IF;
1253
1254 IF get_cons_db_vals_csr%ISOPEN THEN
1255 CLOSE get_cons_db_vals_csr;
1256 END IF;
1257
1258 OKL_API.set_message(p_app_name => g_app_name,
1259 p_msg_name => g_unexpected_error,
1260 p_token1 => g_sqlcode_token,
1261 p_token1_value => sqlcode,
1262 p_token2 => g_sqlerrm_token,
1263 p_token2_value => sqlerrm);
1264
1265 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1266 END validate_consolidated_quote;
1267
1268
1269 -- Start of comments
1270 --
1271 -- Procedure Name : get_quotes_of_consolidated_qte
1272 -- Description : get_quotes_of_consolidated_qte
1273 -- Business Rules :
1274 -- Parameters :
1275 -- Version : 1.0
1276 --
1277 -- End of comments
1278 FUNCTION get_quotes_of_consolidated_qte (
1279 p_cons_rec IN qtev_rec_type) RETURN qtev_tbl_type IS
1280
1281 -- Cursor to get quote details of quotes forming part of consolidated quote
1282 CURSOR get_qtes_of_cons_qte_csr (p_qte_id IN NUMBER) IS
1283 SELECT id
1284 FROM OKL_TRX_QUOTES_V
1285 WHERE consolidated_qte_id = p_qte_id;
1286
1287 lp_qtev_tbl qtev_tbl_type;
1288 i NUMBER;
1289 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'get_quotes_of_consolidated_qte';
1290 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1291 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1292 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1293
1294 BEGIN
1295 IF (is_debug_procedure_on) THEN
1296 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
1297 END IF;
1298 IF (is_debug_statement_on) THEN
1299 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.id: ' || p_cons_rec.id);
1300 END IF;
1301
1302 i := 1;
1306 i := i + 1;
1303 FOR get_qtes_of_cons_qte_rec IN get_qtes_of_cons_qte_csr(p_cons_rec.id) LOOP
1304
1305 lp_qtev_tbl(i).id := get_qtes_of_cons_qte_rec.id;
1307
1308 END LOOP;
1309 IF (is_debug_procedure_on) THEN
1310 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
1311 END IF;
1312
1313 RETURN lp_qtev_tbl;
1314
1315 EXCEPTION
1316 WHEN OTHERS THEN
1317 IF (is_debug_exception_on) THEN
1318 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1319 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1320 END IF;
1321 OKL_API.set_message(p_app_name => g_app_name,
1322 p_msg_name => g_unexpected_error,
1323 p_token1 => g_sqlcode_token,
1324 p_token1_value => sqlcode,
1325 p_token2 => g_sqlerrm_token,
1326 p_token2_value => sqlerrm);
1327
1328 END get_quotes_of_consolidated_qte;
1329
1330
1331 -- Start of comments
1332 --
1333 -- Procedure Name : get_quotes_lines
1334 -- Description : get_quotes_lines
1335 -- Business Rules :
1336 -- Parameters :
1337 -- Version : 1.0
1338 --
1339 -- End of comments
1340 FUNCTION get_quotes_lines (
1341 p_qtev_rec IN qtev_rec_type) RETURN OKL_AM_REPURCHASE_ASSET_PUB.tqlv_tbl_type IS
1342
1343 -- Cursor to get quote details of quotes forming part of consolidated quote
1344 CURSOR get_qtes_lines_of_qte_csr (p_qte_id IN NUMBER) IS
1345 SELECT id
1346 FROM OKL_TXL_QUOTE_LINES_V
1347 WHERE qte_id = p_qte_id;
1348
1349 lp_tqlv_tbl OKL_AM_REPURCHASE_ASSET_PUB.tqlv_tbl_type;
1350 i NUMBER;
1351 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'get_quotes_lines';
1352 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1353 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1354 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1355
1356 BEGIN
1357 IF (is_debug_procedure_on) THEN
1358 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
1359 END IF;
1360 IF (is_debug_statement_on) THEN
1361 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qtev_rec.id: ' || p_qtev_rec.id);
1362 END IF;
1363
1364 i := 1;
1365 FOR get_qtes_lines_of_qte_rec IN get_qtes_lines_of_qte_csr(p_qtev_rec.id) LOOP
1366
1367 lp_tqlv_tbl(i).id := get_qtes_lines_of_qte_rec.id;
1368 i := i + 1;
1369
1370 END LOOP;
1371 IF (is_debug_procedure_on) THEN
1372 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
1373 END IF;
1374
1375 RETURN lp_tqlv_tbl;
1376
1377 EXCEPTION
1378 WHEN OTHERS THEN
1379 IF (is_debug_exception_on) THEN
1380 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1381 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1382 END IF;
1383 OKL_API.set_message(p_app_name => g_app_name,
1384 p_msg_name => g_unexpected_error,
1385 p_token1 => g_sqlcode_token,
1386 p_token1_value => sqlcode,
1387 p_token2 => g_sqlerrm_token,
1388 p_token2_value => sqlerrm);
1389
1390 END get_quotes_lines;
1391
1392 -- Start of comments
1393 --
1394 -- Procedure Name : update_consolidate_quote
1395 -- Description : Gets the values of the quote from DB and if not passed
1396 -- assigns them to out parameter
1397 -- Business Rules :
1398 -- Parameters :
1399 -- Version : 1.0
1400 -- History : RMUNJULU 04-FEB-03 2783130 Added code to get proper qtp_code
1401 --
1402 -- End of comments
1403 PROCEDURE update_consolidate_quote (
1404 p_api_version IN NUMBER,
1405 p_init_msg_list IN VARCHAR2,
1406 x_return_status OUT NOCOPY VARCHAR2,
1407 x_msg_count OUT NOCOPY NUMBER,
1408 x_msg_data OUT NOCOPY VARCHAR2,
1409 p_cons_rec IN qtev_rec_type,
1410 x_cons_rec OUT NOCOPY qtev_rec_type,
1411 x_qtev_tbl OUT NOCOPY qtev_tbl_type) IS
1412
1413 -- RMUNJULU 04-FEB-03 2783130 Added cursor to get proper qtp_code
1414 -- get the consolidated quote details
1415 CURSOR get_qte_details_csr ( p_qte_id IN NUMBER) IS
1416 SELECT qte.qtp_code
1417 FROM OKL_TRX_QUOTES_V qte
1418 WHERE qte.id = p_qte_id;
1419
1420 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1421 l_api_name CONSTANT VARCHAR2(30):= 'update_consolidate_quote';
1422 l_api_version CONSTANT NUMBER := 1;
1423 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1424 l_err_msg VARCHAR2(200);
1425 i NUMBER;
1426 l_sys_date DATE;
1427
1428 lp_qtev_tbl qtev_tbl_type;
1429 li_qtev_tbl qtev_tbl_type;
1430 lx_qtev_tbl qtev_tbl_type;
1431
1432 lp_cons_rec qtev_rec_type := p_cons_rec;
1433 lx_cons_rec qtev_rec_type;
1434
1435 lp_tqlv_tbl OKL_AM_REPURCHASE_ASSET_PUB.tqlv_tbl_type;
1436 lx_tqlv_tbl OKL_AM_REPURCHASE_ASSET_PUB.tqlv_tbl_type;
1437
1441 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1438 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'update_consolidate_quote';
1439 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1440 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1442
1443 BEGIN
1444 IF (is_debug_procedure_on) THEN
1445 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
1446 END IF;
1447 IF (is_debug_statement_on) THEN
1448 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.date_effective_from: ' || p_cons_rec.date_effective_from);
1449 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.date_effective_to: ' || p_cons_rec.date_effective_to);
1450 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.date_requested: ' || p_cons_rec.date_requested);
1451 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.date_proposal: ' || p_cons_rec.date_proposal);
1452 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.qtp_code: ' || p_cons_rec.qtp_code);
1453 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.qst_code: ' || p_cons_rec.qst_code);
1454 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.qrs_code: ' || p_cons_rec.qrs_code);
1455 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.early_termination_yn: ' || p_cons_rec.early_termination_yn);
1456 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.partial_yn: ' || p_cons_rec.partial_yn);
1457 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.consolidated_yn: ' || p_cons_rec.consolidated_yn);
1458 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.accepted_yn: ' || p_cons_rec.accepted_yn);
1459 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.approved_yn: ' || p_cons_rec.approved_yn);
1460 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.preproceeds_yn: ' || p_cons_rec.preproceeds_yn);
1461 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.summary_format_yn: ' || p_cons_rec.summary_format_yn);
1462 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.payment_received_yn: ' || p_cons_rec.payment_received_yn);
1463 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.khr_id: ' || p_cons_rec.khr_id);
1464 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.currency_code: ' || p_cons_rec.currency_code);
1465 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.currency_conversion_code: ' || p_cons_rec.currency_conversion_code);
1466 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.currency_conversion_type: ' || p_cons_rec.currency_conversion_type);
1467 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.currency_conversion_rate: ' || p_cons_rec.currency_conversion_rate);
1468 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.currency_conversion_date: ' || p_cons_rec.currency_conversion_date);
1469 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cons_rec.id: ' || p_cons_rec.id);
1470 END IF;
1471
1472 --Check API version, initialize message list and create savepoint.
1473 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1474 G_PKG_NAME,
1475 p_init_msg_list,
1476 l_api_version,
1477 p_api_version,
1478 '_PVT',
1479 x_return_status);
1480
1481 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1482 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1483 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1484 RAISE OKL_API.G_EXCEPTION_ERROR;
1485 END IF;
1486
1487 -- validate consolidated quote ( if already accepted) dont accept
1488 -- Validate the consolidated quote
1489 validate_consolidated_quote (
1490 p_api_version => p_api_version,
1491 p_init_msg_list => OKL_API.G_FALSE,
1492 x_return_status => l_return_status,
1493 x_msg_count => x_msg_count,
1494 x_msg_data => x_msg_data,
1495 p_cons_rec => lp_cons_rec);
1496 IF (is_debug_statement_on) THEN
1497 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called validate_consolidated_quote , return status: ' || l_return_status);
1498 END IF;
1499
1500 -- Throw exception if validation of consolidated quote fails
1501 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1502 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1503 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1504 RAISE OKL_API.G_EXCEPTION_ERROR;
1505 END IF;
1506
1507 -- Get the quotes which are part of consolidated quote
1508 lp_qtev_tbl := get_quotes_of_consolidated_qte(lp_cons_rec);
1509
1510 -- Validate the quotes that are part of consolidated quote
1511 validate_quotes (
1512 p_api_version => p_api_version,
1513 p_init_msg_list => OKL_API.G_FALSE,
1514 x_return_status => l_return_status,
1515 x_msg_count => x_msg_count,
1516 x_msg_data => x_msg_data,
1517 p_control_flag => 'UPDATE',
1518 p_qtev_tbl => lp_qtev_tbl,
1519 x_qtev_tbl => li_qtev_tbl);
1520 IF (is_debug_statement_on) THEN
1524 -- Throw exception if validation of one or more of selected quotes fails
1521 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called validate_quotes , return status: ' || l_return_status);
1522 END IF;
1523
1525 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1526 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1527 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1528 RAISE OKL_API.G_EXCEPTION_ERROR;
1529 END IF;
1530
1531 IF li_qtev_tbl.COUNT > 0 THEN
1532
1533 i := li_qtev_tbl.FIRST;
1534 LOOP
1535 -- Set the rec type of each quote
1536 li_qtev_tbl(i).accepted_yn := lp_cons_rec.accepted_yn;
1537 li_qtev_tbl(i).date_effective_to := lp_cons_rec.date_effective_to;
1538 li_qtev_tbl(i).comments := lp_cons_rec.comments;
1539
1540 -- RMUNJULU 04-FEB-03 2783130 Added code to get proper qtp_code
1541 -- if value for qtp_code passed use it or else get from DB
1542 IF lp_cons_rec.qtp_code IS NOT NULL
1543 AND lp_cons_rec.qtp_code <> OKL_API.G_MISS_CHAR THEN
1544
1545 li_qtev_tbl(i).qtp_code := lp_cons_rec.qtp_code;
1546
1547 ELSE
1548
1549 -- get the qtp code from database
1550 FOR get_qte_details_rec IN get_qte_details_csr(li_qtev_tbl(i).id) LOOP
1551
1552 li_qtev_tbl(i).qtp_code := get_qte_details_rec.qtp_code;
1553
1554 END LOOP;
1555
1556
1557 END IF;
1558
1559
1560 IF li_qtev_tbl(i).qtp_code LIKE 'TER%' THEN
1561
1562 IF (is_debug_statement_on) THEN
1563 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_AM_TERMNT_QUOTE_PUB.terminate_quote');
1564 END IF;
1565 -- Call terminate quote update api
1566 OKL_AM_TERMNT_QUOTE_PUB.terminate_quote(
1567 p_api_version => p_api_version,
1568 p_init_msg_list => OKL_API.G_FALSE,
1569 x_return_status => l_return_status,
1570 x_msg_count => x_msg_count,
1571 x_msg_data => x_msg_data,
1572 p_term_rec => li_qtev_tbl(i),
1573 x_term_rec => lx_qtev_tbl(i),
1574 x_err_msg => l_err_msg);
1575 IF (is_debug_statement_on) THEN
1576 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_AM_TERMNT_QUOTE_PUB.terminate_quote , return status: ' || l_return_status);
1577 END IF;
1578
1579 -- Throw exception if terminate quote update failed
1580 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1581 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1582 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1583 RAISE OKL_API.G_EXCEPTION_ERROR;
1584 END IF;
1585
1586 ELSIF li_qtev_tbl(i).qtp_code LIKE 'REP%' THEN
1587
1588 -- get the quote lines
1589 lp_tqlv_tbl := get_quotes_lines (
1590 p_qtev_rec => li_qtev_tbl(i));
1591
1592 IF (is_debug_statement_on) THEN
1593 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_AM_REPURCHASE_ASSET_PUB.update_repurchase_quote');
1594 END IF;
1595 -- Call terminate quote update api
1596 OKL_AM_REPURCHASE_ASSET_PUB.update_repurchase_quote(
1597 p_api_version => p_api_version,
1598 p_init_msg_list => OKL_API.G_FALSE,
1599 x_return_status => l_return_status,
1600 x_msg_count => x_msg_count,
1601 x_msg_data => x_msg_data,
1602 p_qtev_rec => li_qtev_tbl(i),
1603 p_tqlv_tbl => lp_tqlv_tbl,
1604 x_qtev_rec => lx_qtev_tbl(i),
1605 x_tqlv_tbl => lx_tqlv_tbl);
1606 IF (is_debug_statement_on) THEN
1607 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_AM_REPURCHASE_ASSET_PUB.update_repurchase_quote , return status: ' || l_return_status);
1608 END IF;
1609
1610 -- Throw exception if repurchase quote update failed
1611 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1612 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1613 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1614 RAISE OKL_API.G_EXCEPTION_ERROR;
1615 END IF;
1616
1617
1618 ELSIF li_qtev_tbl(i).qtp_code LIKE 'RES%' THEN
1619
1620 IF (is_debug_statement_on) THEN
1621 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_AM_RESTRUCTURE_QUOTE_PUB.update_restructure_quote');
1622 END IF;
1623 -- Call terminate quote update api
1624 OKL_AM_RESTRUCTURE_QUOTE_PUB.update_restructure_quote(
1625 p_api_version => p_api_version,
1626 p_init_msg_list => OKL_API.G_FALSE,
1627 x_return_status => l_return_status,
1628 x_msg_count => x_msg_count,
1629 x_msg_data => x_msg_data,
1630 p_quot_rec => li_qtev_tbl(i),
1631 x_quot_rec => lx_qtev_tbl(i));
1632 IF (is_debug_statement_on) THEN
1633 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_AM_RESTRUCTURE_QUOTE_PUB.update_restructure_quote , return status: ' || l_return_status);
1634 END IF;
1635
1636 -- Throw exception if restructure quote update failed
1637 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1638 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1642
1639 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1640 RAISE OKL_API.G_EXCEPTION_ERROR;
1641 END IF;
1643 END IF;
1644
1645 EXIT WHEN (i = li_qtev_tbl.LAST);
1646 i := li_qtev_tbl.NEXT(i);
1647
1648 END LOOP;
1649
1650 END IF;
1651
1652 SELECT SYSDATE INTO l_sys_date FROM DUAL;
1653
1654 -- Set the consolidated quote before updating
1655 -- Set the qst_code to ACCEPTED and date_accepted, if the quote is accepted
1656 IF (lp_cons_rec.accepted_yn = G_YES) THEN
1657 lp_cons_rec.qst_code := 'ACCEPTED';
1658 lp_cons_rec.date_accepted := l_sys_date;
1659 END IF;
1660
1661 IF (is_debug_statement_on) THEN
1662 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_TRX_QUOTES_PUB.update_trx_quotes');
1663 END IF;
1664 -- update consolidate quote
1665 OKL_TRX_QUOTES_PUB.update_trx_quotes (
1666 p_api_version => p_api_version,
1667 p_init_msg_list => OKL_API.G_FALSE,
1668 x_return_status => l_return_status,
1669 x_msg_count => x_msg_count,
1670 x_msg_data => x_msg_data,
1671 p_qtev_rec => lp_cons_rec,
1672 x_qtev_rec => lx_cons_rec);
1673 IF (is_debug_statement_on) THEN
1674 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_TRX_QUOTES_PUB.update_trx_quotes , return status: ' || l_return_status);
1675 END IF;
1676
1677 -- Throw exception if updating the consolidated quote fails
1678 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1679 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1680 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1681 RAISE OKL_API.G_EXCEPTION_ERROR;
1682 END IF;
1683
1684 -- Set the return status and out param
1685 x_return_status := l_return_status;
1686 x_cons_rec := lx_cons_rec;
1687 x_qtev_tbl := lx_qtev_tbl;
1688
1689 -- end the transaction
1690 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1691 IF (is_debug_procedure_on) THEN
1692 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
1693 END IF;
1694
1695 EXCEPTION
1696 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1697 IF (is_debug_exception_on) THEN
1698 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_ERROR');
1699 END IF;
1700 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1701 (
1702 l_api_name,
1703 G_PKG_NAME,
1704 'OKL_API.G_RET_STS_ERROR',
1705 x_msg_count,
1706 x_msg_data,
1707 '_PVT'
1708 );
1709 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1710 IF (is_debug_exception_on) THEN
1711 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_UNEXPECTED_ERROR');
1712 END IF;
1713 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1714 (
1715 l_api_name,
1716 G_PKG_NAME,
1717 'OKL_API.G_RET_STS_UNEXP_ERROR',
1718 x_msg_count,
1719 x_msg_data,
1720 '_PVT'
1721 );
1722 WHEN OTHERS THEN
1723 IF (is_debug_exception_on) THEN
1724 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1725 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1726 END IF;
1727 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1728 (
1729 l_api_name,
1730 G_PKG_NAME,
1731 'OTHERS',
1732 x_msg_count,
1733 x_msg_data,
1734 '_PVT'
1735 );
1736 END update_consolidate_quote;
1737
1738 END OKL_AM_CONSOLIDATED_QTE_PVT;