[Home] [Help]
PACKAGE BODY: APPS.OKL_SALES_QUOTE_QA_PVT
Source
1 PACKAGE BODY OKL_SALES_QUOTE_QA_PVT AS
2 /* $Header: OKLRQQCB.pls 120.78 2008/01/04 19:39:51 rravikir noship $*/
3
4 --Global Cursors
5 CURSOR get_message_text(p_message_name VARCHAR2) IS
6 SELECT MESSAGE_TEXT
7 FROM FND_NEW_MESSAGES
8 WHERE MESSAGE_NAME = p_message_name
9 AND LANGUAGE_CODE = USERENV('LANG');
10
11
12 CURSOR c_lq_fee_rec(p_parent_object_id NUMBER,p_fee_type VARCHAR2) IS
13 select * from okl_fees_v ofv
14 WHERE ofv.parent_object_id=p_parent_object_id
15 AND ofv.fee_type=p_fee_type;
16
17 CURSOR c_lq_cfl_line(p_source_id NUMBER,p_oty_code VARCHAR2) IS
18 SELECT CFL.*
19 FROM OKL_CASH_FLOW_OBJECTS CFO,OKL_CASH_FLOWS CAF,
20 OKL_CASH_FLOW_LEVELS CFL
21 WHERE CFO.ID=CAF.CFO_ID
22 AND CAF.ID=CFL.CAF_ID
23 AND CFO.oty_code=p_oty_code
24 AND CFO.source_id=p_source_id;
25
26
27
28 CURSOR c_qq_header_rec(p_quote_id NUMBER) IS
29 SELECT t1.* FROM OKL_QUICK_QUOTES_B t1
30 WHERE t1.id = p_quote_id;
31
32 CURSOR c_lq_header_rec(p_quote_id NUMBER) IS
33 SELECT t1.* FROM OKL_LEASE_QUOTES_B t1
34 WHERE t1.id = p_quote_id;
35
36 lp_qq_header_rec c_qq_header_rec%ROWTYPE;
37 lp_lq_header_rec c_lq_header_rec%ROWTYPE;
38
39 /*------------------------------------------------------------------------------
40 -- PROCEDURE set_fnd_message
41 ------------------------------------------------------------------------------
42 -- Start of comments
43 --
44 -- Procedure Name : set_fnd_message
45 -- Description : This Procedure sets the message name and tokens in
46 fnd_message and returns the retrieved message text.
47 --
48 -- Business Rules :
49 --
50 -- Parameters : p_msg_name -- MESSAGE_NAME FROm FND_NEW_MESAGES
51
52 p_token1 -- Token Code for Message
53 p_value1 --> Value to be set in the Message for Token1
54
55 -- Version :
56 -- History :
57 -- End of comments
58 ------------------------------------------------------------------------------*/
59 PROCEDURE set_fnd_message(p_msg_name IN varchar2
60 ,p_token1 IN varchar2 DEFAULT NULL
61 ,p_value1 IN varchar2 DEFAULT NULL
62 ,p_token2 IN varchar2 DEFAULT NULL
63 ,p_value2 IN varchar2 DEFAULT NULL
64 ,p_token3 IN varchar2 DEFAULT NULL
65 ,p_value3 IN varchar2 DEFAULT NULL
66 ,p_token4 IN varchar2 DEFAULT NULL
67 ,p_value4 IN varchar2 DEFAULT NULL) IS
68
69 l_msg varchar2(2700);
70 l_module CONSTANT fnd_log_messages.module%TYPE := 'set_fnd_message';
71 l_debug_enabled varchar2(10);
72 is_debug_procedure_on boolean;
73 is_debug_statement_on boolean;
74
75 BEGIN
76 l_debug_enabled := okl_debug_pub.check_log_enabled;
77 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
78 ,fnd_log.level_procedure);
79
80 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
81 okl_debug_pub.log_debug(fnd_log.level_procedure
82 ,l_module
83 ,'begin debug OKLRECUB.pls.pls call set_fnd_message');
84 END IF;
85 fnd_message.set_name(g_app_name, p_msg_name);
86
87 IF (p_token1 IS NOT NULL) THEN
88 fnd_message.set_token(token => p_token1, value => p_value1);
89 END IF;
90
91 IF (p_token2 IS NOT NULL) THEN
92 fnd_message.set_token(token => p_token2, value => p_value2);
93 END IF;
94
95 IF (p_token3 IS NOT NULL) THEN
96 fnd_message.set_token(token => p_token3, value => p_value3);
97 END IF;
98
99 IF (p_token4 IS NOT NULL) THEN
100 fnd_message.set_token(token => p_token4, value => p_value4);
101 END IF;
102
103 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
104 okl_debug_pub.log_debug(fnd_log.level_procedure
105 ,l_module
106 ,'end debug OKLRECUB.pls.pls call set_fnd_message');
107 END IF;
108
109 END set_fnd_message;
110 /*------------------------------------------------------------------------------
111 -- PROCEDURE populate_lq_rec_values
112 ------------------------------------------------------------------------------
113 -- Start of comments
114 --
115 -- Procedure Name : populate_lq_rec_values
116
117 -- Description : This method will populate the Lease Quote Record
118 Structure in order to Pass all the Required Attributes
119 for Eligibility Criteria for Eligibility of SRT,LRS,VP,Product
120
121 -- Business Rules :Populate Header for Quick Quote for a Given Lease Quote id
122 --
123 -- Parameters : p_object_id -- Lease Quote id
124
125 x_okl_ec_rec --> Hold all the Eligibility criteria values
126 for a given Lease Quote
127
128 -- Version :
129 -- History :
130 -- End of comments
131 ------------------------------------------------------------------------------*/
132 PROCEDURE populate_lq_rec_values(p_target_id number,
133 l_okl_ec_rec_type IN OUT NOCOPY okl_ec_evaluate_pvt.okl_ec_rec_type) IS
134
135 i INTEGER;
136 l_module CONSTANT fnd_log_messages.module%TYPE := 'lrs';
137 l_debug_enabled varchar2(10);
138 is_debug_procedure_on boolean;
139 is_debug_statement_on boolean;
140 p_api_version CONSTANT number := 1.0;
141 x_return_status varchar2(1) := okl_api.g_ret_sts_success;
142 l_program_name CONSTANT VARCHAR2(30) := 'populate';
143 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
144 l_parent_object_id NUMBER;
145 l_parent_object_code VARCHAR2(30);
146 l_expected_start_date DATE;
147 l_term NUMBER;
148 l_validation_mode VARCHAR2(5):='LOV';
149 --Bug # 5050143 ssdeshpa start
150 l_deal_size NUMBER;
151 l_adj_amount NUMBER;
152 --Bug # 5050143 ssdeshpa end
153 CURSOR c_lq_rec(p_lease_quote_id NUMBER) IS
154 SELECT *
155 FROM OKL_LEASE_QUOTES_B
156 where id=p_lease_quote_id;
157 l_lq_rec c_lq_rec%ROWTYPE;
158
159 CURSOR c_lop_rec(p_parent_object_id NUMBER) IS
160 select lop.id,
161 lop.reference_number,
162 lop.prospect_id,
163 lop.prospect_address_id,
164 lop.cust_acct_id,
165 OKL_LEASE_APP_PVT.get_credit_classfication(
166 lop.prospect_id,
167 lop.cust_acct_id,
168 NULL) as customer_credit_class,
169 lop.sales_rep_id,
170 lop.sales_territory_id
171 from okl_lease_opportunities_b lop
172 where lop.id=p_parent_object_id;
173
174 l_lop_rec c_lop_rec%ROWTYPE;
175
176 CURSOR c_lapp_rec(p_parent_object_id NUMBER) IS
177 select lapp.id,
178 lapp.reference_number,
179 lapp.prospect_id,
180 lapp.prospect_address_id,
181 lapp.cust_acct_id,
182 OKL_LEASE_APP_PVT.get_credit_classfication(
183 lapp.prospect_id,
184 lapp.cust_acct_id,
185 NULL) as customer_credit_class,
186 lapp.sales_rep_id,
187 lapp.sales_territory_id
188 from okl_lease_applications_b lapp
189 where lapp.id=p_parent_object_id;
190
191 l_lapp_rec c_lapp_rec%ROWTYPE;
192 --Bug # 5050143 ssdeshpa start
193
194 --Added Cursors to get Deal Size of LQ
195 CURSOR c_deal_size_cur(p_parent_object_id NUMBER) IS
196 select SUM(OEC)
197 FROM OKL_LEASE_QUOTES_B OLQ,OKL_ASSETS_B OAB
198 where OAB.PARENT_OBJECT_ID = OLQ.ID
199 AND OAB.PARENT_OBJECT_CODE='LEASEQUOTE'
200 AND OLQ.ID= p_parent_object_id;
201 --Added Cursors to get Adjustment(DN,Trade In Subsidy) for LQ
202 cursor c_lq_cost_adj_rec(p_quote_id NUMBER,p_adj_type VARCHAR2) IS
203 SELECT SUM(VALUE)
204 FROM OKL_COST_ADJUSTMENTS_B OCA,
205 OKL_ASSETS_B OAB
206 where OAB.PARENT_OBJECT_CODE = 'LEASEQUOTE'
207 AND OCA.PARENT_OBJECT_CODE='ASSET'
208 AND OCA.PARENT_OBJECT_ID=OAB.ID
209 and ADJUSTMENT_SOURCE_TYPE =p_adj_type
210 AND OAB.PARENT_OBJECT_ID = p_quote_id;
211 --Addes cursor to get Item For LQ
212 CURSOR c_cost_comp_cur(p_quote_id NUMBER) IS
213 select OAC.INV_ITEM_ID
214 from OKL_ASSET_COMPONENTS_B OAC,
215 OKL_ASSETS_B OAB
216 WHERE OAC.ASSET_ID = OAB.ID
217 AND OAB.PARENT_OBJECT_CODE = 'LEASEQUOTE'
218 AND PRIMARY_COMPONENT='YES'
219 AND OAB.PARENT_OBJECT_ID = p_quote_id;
220 --Bug # 5050143 ssdeshpa end
221
222 BEGIN
223
224 OPEN c_lq_rec(p_target_id);
225 FETCH c_lq_rec INTO l_lq_rec;
226 CLOSE c_lq_rec;
227 l_okl_ec_rec_type.target_id := p_target_id;
228 l_okl_ec_rec_type.target_eff_from:=l_lq_rec.expected_start_date;
229 l_okl_ec_rec_type.term:=l_lq_rec.term;
230 l_okl_ec_rec_type.validation_mode := l_validation_mode;
231
232 --Bug 5050143 ssdeshpa start
233 --Get Total Down Payment For Quote
234 OPEN c_lq_cost_adj_rec(p_target_id,'DOWN_PAYMENT');
235 FETCH c_lq_cost_adj_rec INTO l_adj_amount;
236 CLOSE c_lq_cost_adj_rec;
237 l_okl_ec_rec_type.down_payment := l_adj_amount;
238 --Get Total Down Payment For Quote
239
240 --Get Total Trade In For Quote
241 OPEN c_lq_cost_adj_rec(p_target_id,'TRADEIN');
242 FETCH c_lq_cost_adj_rec INTO l_adj_amount;
243 CLOSE c_lq_cost_adj_rec;
244 l_okl_ec_rec_type.trade_in_value := l_adj_amount;
245 --Get Total Trade In For Quote
246
247 --Get Item Tables for LQ
248 i := 1;
249 FOR l_cost_comp_rec IN c_cost_comp_cur(p_target_id) LOOP
250 l_okl_ec_rec_type.item_table(i) := l_cost_comp_rec.INV_ITEM_ID;
251 i := i + 1;
252 END LOOP;
253
254 --End Get Items Table For LQ
255 --Get Deal Size For LQ
256 OPEN c_deal_size_cur(p_target_id);
257 FETCH c_deal_size_cur INTO l_deal_size;
258 CLOSE c_deal_size_cur;
259 l_okl_ec_rec_type.deal_size:=l_deal_size;
260 --Bug 5045505 ssdeshpa end
261 IF(l_lq_rec.parent_object_code = 'LEASEOPP') THEN
262 OPEN c_lop_rec(l_parent_object_id);
263 FETCH c_lop_rec INTO l_lop_rec;
264 l_okl_ec_rec_type.territory:= l_lop_rec.sales_territory_id;
265 l_okl_ec_rec_type.customer_credit_class:= l_lop_rec.customer_credit_class;
266 CLOSE c_lop_rec;
267 ELSIF(l_lq_rec.parent_object_code = 'LEASEAPP') THEN
268 OPEN c_lapp_rec(l_parent_object_id);
269 FETCH c_lapp_rec INTO l_lapp_rec;
270 l_okl_ec_rec_type.territory:= l_lapp_rec.sales_territory_id;
271 l_okl_ec_rec_type.customer_credit_class:= l_lapp_rec.customer_credit_class;
272 CLOSE c_lapp_rec;
273 END IF;
274
275 EXCEPTION
276 WHEN OTHERS THEN
277 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
278 p_msg_name => G_DB_ERROR,
279 p_token1 => G_PROG_NAME_TOKEN,
280 p_token1_value => l_api_name,
281 p_token2 => G_SQLCODE_TOKEN,
282 p_token2_value => sqlcode,
283 p_token3 => G_SQLERRM_TOKEN,
284 p_token3_value => sqlerrm);
285 END populate_lq_rec_values;
286 --------------------------------------------------------------------------------
287 /** obsolete Method **/
288 /* PROCEDURE check_srt_effective_rate(p_srt_version_id NUMBER,
289 p_quote_id NUMBER,
290 x_qa_result_tbl IN OUT NOCOPY qa_results_tbl_type) IS
291 l_msg_count NUMBER;
292 l_module CONSTANT fnd_log_messages.module%TYPE := 'srt';
293 l_debug_enabled varchar2(10);
294 is_debug_procedure_on boolean;
295 is_debug_statement_on boolean;
296 p_api_version CONSTANT number := 1.0;
297 x_return_status VARCHAR2(1);
298 l_program_name CONSTANT VARCHAR2(30) := 'p_srtlq';
299 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
300 l_okl_ec_rec_type okl_ec_evaluate_pvt.okl_ec_rec_type;
301 l_ac_rec_type okl_ec_evaluate_pvt.okl_ac_rec_type;
302 l_adj_factor NUMBER;
303 l_srt_effective_rate NUMBER;
304 l_srt_valid boolean := FALSE;
305 i INTEGER;
306 CURSOR c_srt_rec(srt_id number) IS
307 select srt.template_name as name,
308 srv.std_rate_tmpl_ver_id,
309 srv.version_number,
310 srv.effective_from_date effective_from,
311 srv.effective_to_date effective_to,
312 srv.srt_rate,
313 srv.sts_code,
314 srv.day_convention_code,
315 srv.spread spread,
316 srv.adj_mat_version_id adj_mat_version_id,
317 srv.min_adj_rate,
318 srv.max_adj_rate
319 from
320 okl_fe_std_rt_tmp_vers srv,
321 okl_fe_std_rt_tmp_v srt
322 where
323 srv.std_rate_tmpl_ver_id= p_srt_version_id
324 AND srt.std_rate_tmpl_id=srv.std_rate_tmpl_id;
325
326 l_srt_rec c_srt_rec%ROWTYPE;
327
328 BEGIN
329 OPEN c_srt_rec(p_srt_version_id);
330 FETCH c_srt_rec INTO l_srt_rec;
331 CLOSE c_srt_rec;
332 l_okl_ec_rec_type.target_id:= p_quote_id;
333 l_okl_ec_rec_type.target_type:= 'LEASEQUOTE';
334
335 -------------------------------------------------------------------------------------
336 populate_lq_rec_values(p_quote_id,l_okl_ec_rec_type);
337 --------------------------------------------------------------------------------------
338 -- Populate the Adjustment mat. rec.
339 l_ac_rec_type.src_id := l_srt_rec.adj_mat_version_id; -- Pricing adjustment matrix ID
340 l_ac_rec_type.source_name := NULL; -- NOT Mandatory Pricing Adjustment Matrix Name !
341 l_ac_rec_type.target_id := p_quote_id ; -- Quote ID
342 l_ac_rec_type.src_type := 'PAM'; -- Lookup Code
343 l_ac_rec_type.target_type := 'QUOTE'; -- Same for both Quick Quote and Standard Quote
344 l_ac_rec_type.target_eff_from := l_okl_ec_rec_type.target_eff_from; -- Quote effective From
345 l_ac_rec_type.term := l_okl_ec_rec_type.term; -- Remaining four will be from teh business object like QQ / LQ
346 l_ac_rec_type.territory := l_okl_ec_rec_type.territory;
347 l_ac_rec_type.deal_size := l_okl_ec_rec_type.deal_size;
348 l_ac_rec_type.customer_credit_class := l_okl_ec_rec_type.customer_credit_class; -- Not sure how to pass this even ..
349 -- Calling the API to get the adjustment factor ..
350 okl_ec_evaluate_pvt.get_adjustment_factor(
351 p_api_version => p_api_version,
352 p_init_msg_list => p_init_msg_list,
353 x_return_status => x_return_status,
354 x_msg_count => l_msg_count,
355 x_msg_data => x_msg_data,
356 p_okl_ac_rec => l_ac_rec_type,
357 x_adjustment_factor => l_adj_factor );
358 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
359 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
360 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
361 RAISE OKL_API.G_EXCEPTION_ERROR;
362 END IF;
363 --Calculate Effective Rate
364 l_srt_effective_rate := l_srt_rec.srt_rate + nvl(l_srt_rec.spread,0) + nvl(l_adj_factor,0); -- Rate is being stored as Percentage
365 --l_okl_srt_table(i).srt_rate := l_okl_srt_rec.srt_rate + nvl(l_okl_srt_rec.spread,0) + nvl(l_adj_factor,0); -- Rate is being stored as Percentage
366 IF(l_srt_effective_rate IS NOT NULL) THEN
367 IF(nvl(l_srt_rec.max_adj_rate,l_srt_effective_rate) < l_srt_effective_rate )THEN
368 set_fnd_message(p_msg_name => 'OKL_QA_SRT_MAX_RATE_VALID'
369 ,p_token1 => 'SRT_NAME'
370 ,p_value1 => l_srt_rec.name
371 ,p_token2 => 'EFF_RATE'
372 ,p_value2 => l_srt_effective_rate
373 ,p_token3 => 'MAX_ADJ_RATE'
374 ,p_value3 => l_srt_rec.max_adj_rate
375 ,p_token4 => NULL
376 ,p_value4 => NULL);
377 i:=x_qa_result_tbl.COUNT;
378 i:=i+1;
379 x_qa_result_tbl(i).check_code:='check_srt_effective_rate';
380 x_qa_result_tbl(i).check_meaning:='SRT_EFF_RATE_IS_INVALID';
381 x_qa_result_tbl(i).result_code:='ERROR';
382 x_qa_result_tbl(i).result_meaning:='ERROR';
383 x_qa_result_tbl(i).message_code:= 'OKL_QA_SRT_MAX_RATE_VALID';
384 x_qa_result_tbl(i).message_text:= fnd_message.get;
385
386 ELSIF(nvl(l_srt_rec.min_adj_rate,l_srt_effective_rate) > l_srt_effective_rate) THEN
387 set_fnd_message(p_msg_name => 'OKL_QA_SRT_MIN_RATE_VALID'
388 ,p_token1 => 'SRT_NAME'
389 ,p_value1 => l_srt_rec.name
390 ,p_token2 => 'EFF_RATE'
391 ,p_value2 => l_srt_effective_rate
392 ,p_token3 => 'MIN_ADJ_RATE'
393 ,p_value3 => l_srt_rec.min_adj_rate
394 ,p_token4 => NULL
395 ,p_value4 => NULL);
396 i:=x_qa_result_tbl.COUNT;
397 i:=i+1;
398 x_qa_result_tbl(i).check_code:='check_srt_effective_rate';
399 x_qa_result_tbl(i).check_meaning:='SRT_EFF_RATE_IS_INVALID';
400 x_qa_result_tbl(i).result_code:='ERROR';
401 x_qa_result_tbl(i).result_meaning:='ERROR';
402 x_qa_result_tbl(i).message_code:= 'OKL_QA_SRT_MIN_RATE_VALID';
403 x_qa_result_tbl(i).message_text:= fnd_message.get;
404 END IF;
405 END IF;
406
407 EXCEPTION
408 WHEN OTHERS THEN
409 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
410 p_msg_name => G_DB_ERROR,
411 p_token1 => G_PROG_NAME_TOKEN,
412 p_token1_value => l_api_name,
413 p_token2 => G_SQLCODE_TOKEN,
414 p_token2_value => sqlcode,
415 p_token3 => G_SQLERRM_TOKEN,
416 p_token3_value => sqlerrm);
417
418 END check_srt_effective_rate; */
419 /*------------------------------------------------------------------------------
420 -- PROCEDURE execute_system_validation
421 ------------------------------------------------------------------------------
422 -- Start of comments
423 --
424 -- Procedure Name : execute_system_validation
425 -- Description : This will executes the Validation Listed in the
426 System Level Validation Set
427 --
428 -- Business Rules :
429 --
430 -- Parameters : p_function_name -- Function Name to be Execute
431
432 x_value -- Valid Values are 0 or 1
433 If 1 function executed successfully
434 If 0 function not executed successfully
435
436 -- Version :
437 -- History :
438 -- End of comments
439 ------------------------------------------------------------------------------*/
440 PROCEDURE execute_system_validation(p_api_version IN NUMBER
441 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
442 ,x_return_status OUT NOCOPY VARCHAR2
443 ,x_msg_count OUT NOCOPY NUMBER
444 ,x_msg_data OUT NOCOPY VARCHAR2
445 ,p_function_name IN okl_data_src_fnctns_v.name%TYPE
446 ,x_value OUT NOCOPY NUMBER
447 ) IS
448
449 -- Exception declarations
450 FUNCTION_DATA_INVALID EXCEPTION;
451 FUNCTION_RETURNS_NULL EXCEPTION;
452
453 -- Local Variable Declarations
454 l_value NUMBER;
455 l_evaluated_string okl_formulae_v.formula_string%TYPE;
456 l_no_dml_message VARCHAR2(200) := 'OKL_FORMULAE_NO_DML';
457 l_function_name okl_data_src_fnctns_v.name%TYPE;
458 l_function_source okl_data_src_fnctns_v.source%TYPE;
459
460
461 l_program_name CONSTANT VARCHAR2(30) := 'exc_sys_vldtion';
462 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
463 l_flag BOOLEAN DEFAULT FALSE;
464
465 CURSOR data_src_fnctns_csr(cp_function_name IN okl_data_src_fnctns_v.name%TYPE)
466 IS
467 SELECT source
468 FROM okl_data_src_fnctns_v
469 WHERE name = cp_function_name
470 AND fnctn_code = 'VALIDATION';
471
472 BEGIN
473
474
475 l_function_name := p_function_name;
476
477 FOR l_data_src_fnctns_csr IN data_src_fnctns_csr(cp_function_name => l_function_name)
478 LOOP
479 l_flag := TRUE;
480 l_function_source := l_data_src_fnctns_csr.source;
481 EXIT;
482 END LOOP;
483
484
485 IF l_flag THEN
486 l_flag := FALSE;
487 ELSE
488 RAISE NO_DATA_FOUND;
489 END IF;
490
491 l_evaluated_string := l_function_source;
492
493 IF l_evaluated_string IS NULL THEN
494 RAISE FUNCTION_DATA_INVALID;
495 ELSE
496 l_evaluated_string := 'SELECT '||l_evaluated_string ||' FROM dual';
497 END IF;
498
499 EXECUTE IMMEDIATE l_evaluated_string
500 INTO l_value;
501
502 IF l_value IS NULL THEN
503 RAISE FUNCTION_RETURNS_NULL;
504 ELSE
505 x_return_status := OKC_API.G_RET_STS_SUCCESS;
506 x_value := l_value;
507 END IF;
508
509 EXCEPTION
510 WHEN FUNCTION_RETURNS_NULL THEN
511 OKC_API.SET_MESSAGE(p_app_name => g_app_name
512 ,p_msg_name => 'OKL_FUNCTION_RETURNS_NULL'
513 ,p_token1 => 'FUNCTION'
514 ,p_token1_value => l_function_name );
515 x_return_status := OKC_API.G_RET_STS_ERROR;
516
517 WHEN FUNCTION_DATA_INVALID THEN
518 OKC_API.SET_MESSAGE(p_app_name => g_app_name
519 ,p_msg_name => g_function_data_invalid
520 ,p_token1 => 'FUNCTION'
521 ,p_token1_value => l_function_name );
522 x_return_status := OKC_API.G_RET_STS_ERROR;
523
524 WHEN NO_DATA_FOUND THEN
525 OKC_API.SET_MESSAGE(p_app_name => g_app_name
526 ,p_msg_name => g_invalid_function
527 ,p_token1 => 'FUNCTION'
528 ,p_token1_value => l_function_name );
529 x_return_status := OKC_API.G_RET_STS_ERROR;
530
531 WHEN OTHERS THEN
532 okl_api.set_message(p_app_name => g_app_name
533 ,p_msg_name => 'OKL_SYSTEM_VALIDATION_FAILED'
534 ,p_token1 => 'NAME'
535 ,p_token1_value => l_function_name);
536
537 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
538 END execute_system_validation;
539 /*------------------------------------------------------------------------------
540 -- PROCEDURE populate_result_table
541 ------------------------------------------------------------------------------
542 -- Start of comments
543 --
544 -- Procedure Name : populate_result_table
545 -- Description : This will populate the Database with QA Results for
546 Lease Quote
547 --
548 -- Business Rules :
549 --
550 -- Parameters : p_object_id -- Lease Quote Id
551
552 p_object_type -- valid values are 'LEASEQUOTE'
553 hold which type of object this method is calling
554
555 x_qa_result_tbl --> Hold all the QA Results for Object
556
557 -- Version :
558 -- History :
559 -- End of comments
560 ------------------------------------------------------------------------------*/
561 procedure populate_result_table(p_api_version IN NUMBER
562 ,p_init_msg_list IN VARCHAR2
563 ,p_object_type IN VARCHAR2
564 ,p_object_id IN NUMBER
565 ,x_return_status OUT NOCOPY VARCHAR2
566 ,x_msg_count OUT NOCOPY NUMBER
567 ,x_msg_data OUT NOCOPY VARCHAR2
568 ,x_qa_result_tbl IN OUT NOCOPY OKL_SALES_QUOTE_QA_PVT.qa_results_tbl_type) IS
569
570 l_validation_set_id NUMBER;
571 lp_vlrv_tbl OKL_VLR_PVT.vlrv_tbl_type;
572 lx_vlrv_tbl OKL_VLR_PVT.vlrv_tbl_type;
573 i INTEGER;
574 ret boolean;
575 fun_ret number;
576 call_user boolean;
577 l_function_name okl_data_src_fnctns_v.name%TYPE;
578 l_source_name okl_data_src_fnctns_v.source%TYPE;
579 l_failure_severity VARCHAR2(30);
580 l_module CONSTANT fnd_log_messages.module%TYPE := 'OKLRQQCB.pls.run_qa';
581 l_debug_enabled varchar2(10);
582 is_debug_procedure_on boolean;
583 is_debug_statement_on boolean;
584 cursor c_sys_opt_vls IS
585 select validation_set_id
586 FROM OKL_SYSTEM_PARAMS_ALL;
587 BEGIN
588 OPEN c_sys_opt_vls;
589 FETCH c_sys_opt_vls INTO l_validation_set_id;
590 CLOSE c_sys_opt_vls;
591 IF(l_validation_set_id IS NULL) THEN
592 l_validation_set_id := -1;
593 END IF;
594 IF(x_qa_result_tbl.COUNT > 0) THEN
595 FOR i IN x_qa_result_tbl.FIRST..x_qa_result_tbl.LAST LOOP
596 IF(x_qa_result_tbl.exists(i)) THEN
597 lp_vlrv_tbl(i).parent_object_code := p_object_type;
598 lp_vlrv_tbl(i).parent_object_id := p_object_id;
599 lp_vlrv_tbl(i).validation_id := l_validation_set_id;
600 lp_vlrv_tbl(i).result_code := x_qa_result_tbl(i).result_code;
601 lp_vlrv_tbl(i).validation_text := x_qa_result_tbl(i).message_text;
602 END IF;
603 END LOOP;
604 END IF;
605 OKL_VLR_PVT.insert_row( p_api_version => p_api_version,
606 p_init_msg_list => p_init_msg_list,
607 x_return_status => x_return_status,
608 x_msg_count => x_msg_count,
609 x_msg_data => x_msg_data,
610 p_vlrv_tbl => lp_vlrv_tbl,
611 x_vlrv_tbl => lx_vlrv_tbl);
612
613 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
614 okl_debug_pub.log_debug(fnd_log.level_statement
615 ,l_module
616 ,'okl_sales_quote_pvt.populate_result_table returned with status ' ||
617 x_return_status ||
618 ' x_msg_data ' ||
619 x_msg_data);
620 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
621 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
622 RAISE okl_api.g_exception_unexpected_error;
623 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
624 RAISE okl_api.g_exception_unexpected_error;
625 END IF;
626
627 EXCEPTION
628 WHEN okl_api.g_exception_unexpected_error THEN
629 x_return_status := okl_api.g_ret_sts_unexp_error;
630 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
631 p_msg_name => G_DB_ERROR,
632 p_token1 => G_PROG_NAME_TOKEN,
633 p_token1_value => 'OKLRQQCB.pls.populate_result_table',
634 p_token2 => G_SQLCODE_TOKEN,
635 p_token2_value => sqlcode,
636 p_token3 => G_SQLERRM_TOKEN,
637 p_token3_value => sqlerrm);
638
639 WHEN OTHERS THEN
640 -- unexpected error
641 x_return_status := okl_api.g_ret_sts_unexp_error;
642 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
643 p_msg_name => G_DB_ERROR,
644 p_token1 => G_PROG_NAME_TOKEN,
645 p_token1_value => 'OKLRQQCB.pls.populate_result_table',
646 p_token2 => G_SQLCODE_TOKEN,
647 p_token2_value => sqlcode,
648 p_token3 => G_SQLERRM_TOKEN,
649 p_token3_value => sqlerrm);
650
651 END populate_result_table;
652 /*-----------------------------------------------------------------------------
653 -- FUNCTION get_msg_text
654 -----------------------------------------------------------------------------
655 -- Start of comments
656 --
657 -- Function Name : get_msg_text
658 -- Description : Return Message text for a given message code
659 If message text is null then it will return Message code
660 --
661 -- Business Rules : If message text is null then it will return Message code
662 --
663 -- Parameters :
664 -- Version :
665 -- History :
666 -- End of comments
667 ------------------------------------------------------------------------------*/
668 FUNCTION get_msg_text(p_message_name VARCHAR2) RETURN VARCHAR2 IS
669 l_msg_text VARCHAR2(2000);
670 BEGIN
671
672 OPEN get_message_text(p_message_name);
673 FETCH get_message_text INTO l_msg_text;
674 CLOSE get_message_text;
675 IF(l_msg_text IS NULL) THEN
676 l_msg_text :=p_message_name;
677 END IF;
678 RETURN l_msg_text;
679 EXCEPTION
680 WHEN OTHERS THEN
681
682 IF get_message_text%ISOPEN THEN
683 CLOSE get_message_text;
684 END IF;
685 -- unexpected error
686 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
687 p_msg_name => G_DB_ERROR,
688 p_token1 => G_PROG_NAME_TOKEN,
689 p_token1_value => 'OKLRQQCB.pls.get_msg_text',
690 p_token2 => G_SQLCODE_TOKEN,
691 p_token2_value => sqlcode,
692 p_token3 => G_SQLERRM_TOKEN,
693 p_token3_value => sqlerrm);
694 END get_msg_text;
695 --------------------------------------------------------------------------------
696 /**
697 This procedure will validate all cash flows for Asset/Quote
698 **/
699 PROCEDURE validate_cashflows(p_quote_id number
700 ,p_oty_code VARCHAR2
701 ,p_pricing_method VARCHAR2
702 ,x_qa_result_tbl IN OUT NOCOPY qa_results_tbl_type
703 ,x_return_status OUT NOCOPY VARCHAR2) IS
704 -- Variables Declarations
705 p_api_version CONSTANT NUMBER DEFAULT 1.0;
706 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'validate_cashflows';
707 i NUMBER;
708 --Bug 6359406 PAGARG initialising the counter to check cfl count, which is used
709 --to set error message if count is zero
710 l_cfl_count INTEGER := 0;
711 CURSOR c_cash_flow_cur(p_quote_id NUMBER,
712 p_oty_code VARCHAR2) IS
713 SELECT CFO.ID CFO_ID,
714 CAF.ID CAF_ID,
715 CAF.STY_ID,
716 CAF.cft_code,
717 CAF.due_arrears_yn
718 FROM OKL_CASH_FLOW_OBJECTS CFO,OKL_CASH_FLOWS CAF
719 WHERE CAF.cfo_id = CFO.ID
720 AND CFO.OTY_CODE = p_oty_code
721 AND CFO.SOURCE_ID = p_quote_id;
722
723 CURSOR c_cash_flow_level_cur(p_source_id NUMBER,p_oty_code VARCHAR2) IS
724 SELECT CFL.id,
725 CFL.amount,
726 CFL.number_of_periods,
727 CFL.stub_days,
728 CFL.stub_amount
729 FROM OKL_CASH_FLOW_OBJECTS CFO,OKL_CASH_FLOWS CAF,
730 OKL_CASH_FLOW_LEVELS CFL
731 WHERE CFO.ID=CAF.CFO_ID
732 AND CAF.ID=CFL.CAF_ID
733 AND CFO.oty_code=p_oty_code
734 AND CFO.source_id=p_source_id;
735
736 BEGIN
737
738 FOR cash_flow_cur_rec IN c_cash_flow_cur(p_quote_id,p_oty_code) LOOP
739 IF(cash_flow_cur_rec.STY_ID IS NULL) THEN
740 i:=x_qa_result_tbl.COUNT;
741 i:=i+1;
742 x_qa_result_tbl(i).check_code:='validate_payment_options';
743 x_qa_result_tbl(i).check_meaning:='ARREARS IS NOT FOUND FOR QUOTE';
744 x_qa_result_tbl(i).result_code:='ERROR';
745 x_qa_result_tbl(i).result_meaning:='ERROR';
746 x_qa_result_tbl(i).message_code:= 'OKL_QA_CFL_STRM_NOT_FOUND';
747 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_CFL_STRM_NOT_FOUND');
748 EXIT;
749 END IF;/*
750 IF(cash_flow_cur_rec.due_arrears_yn IS NULL) THEN
751 i:=x_qa_result_tbl.COUNT;
752 i:=i+1;
753 x_qa_result_tbl(i).check_code:='validate_payment_options';
754 x_qa_result_tbl(i).check_meaning:='ARREARS IS NOT FOUND FOR QUOTE';
755 x_qa_result_tbl(i).result_code:='ERROR';
756 x_qa_result_tbl(i).result_meaning:='ERROR';
757 x_qa_result_tbl(i).message_code:= 'OKL_QA_ARR_NOT_FOUND';
758 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_ARR_NOT_FOUND');
759 EXIT;
760 END IF;*/
761 END LOOP;
762 FOR cash_flow_level_rec IN c_cash_flow_level_cur(p_quote_id,p_oty_code) LOOP
763 l_cfl_count := l_cfl_count + 1;
764 IF((p_pricing_method <> 'SP') AND
765 ((cash_flow_level_rec.amount IS NULL AND cash_flow_level_rec.number_of_periods IS NULL) AND
766 (cash_flow_level_rec.stub_days IS NULL AND cash_flow_level_rec.stub_amount IS NULL))) THEN
767 i:=x_qa_result_tbl.COUNT;
768 i:=i+1;
769 x_qa_result_tbl(i).check_code:='check_payments';
770 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_FOR_QUOTE';
771 x_qa_result_tbl(i).result_code:='ERROR';
772 x_qa_result_tbl(i).result_meaning:='ERROR';
773 x_qa_result_tbl(i).message_code:= 'OKL_QA_MISSING_PAY_LEVEL';
774 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_MISSING_PAY_LEVEL');
775
776 EXIT;
777 END IF;
778
779 END LOOP;
780 IF(l_cfl_count = 0) THEN
781 i:=x_qa_result_tbl.COUNT;
782 i:=i+1;
783 x_qa_result_tbl(i).check_code:='validate_cashflows';
784 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_FOR_QUOTE';
785 x_qa_result_tbl(i).result_code:='ERROR';
786 x_qa_result_tbl(i).result_meaning:='ERROR';
787 x_qa_result_tbl(i).message_code:= 'OKL_QA_MISSING_PAY_LEVEL';
788 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_MISSING_PAY_LEVEL');
789
790 END IF;
791
792 EXCEPTION
793 WHEN OTHERS THEN
794 -- unexpected error
795 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
796 p_msg_name => G_DB_ERROR,
797 p_token1 => G_PROG_NAME_TOKEN,
798 p_token1_value => 'OKLRQQCB.pls.validate_cashflows',
799 p_token2 => G_SQLCODE_TOKEN,
800 p_token2_value => sqlcode,
801 p_token3 => G_SQLERRM_TOKEN,
802 p_token3_value => sqlerrm);
803 END validate_cashflows;
804
805 ------------------------------------------------------------------------------
806 /** This function validates Pricing Option for Quote/Asset when SP='N'
807 **/
808 PROCEDURE validate_payment_options(p_srt_version_id NUMBER,
809 p_arrears VARCHAR2,
810 p_pricing_method VARCHAR2,
811 p_periodic_amt NUMBER,
812 x_qa_result_tbl IN OUT NOCOPY qa_results_tbl_type,
813 x_return_status OUT NOCOPY VARCHAR2) IS
814
815 -- Variables Declarations
816 p_api_version CONSTANT NUMBER DEFAULT 1.0;
817 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'val_pay_options';
818 i NUMBER;
819
820 BEGIN
821 IF(p_srt_version_id IS NULL) THEN
822 i:=x_qa_result_tbl.COUNT;
823 i:=i+1;
824 x_qa_result_tbl(i).check_code:='validate_payment_options';
825 x_qa_result_tbl(i).check_meaning:='SRT IS NOT FOUND FOR QUOTE';
826 x_qa_result_tbl(i).result_code:='ERROR';
827 x_qa_result_tbl(i).result_meaning:='ERROR';
828 x_qa_result_tbl(i).message_code:= 'OKL_QA_SRT_NOT_FOUND';
829 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SRT_NOT_FOUND');
830
831 END IF;
832 -- pricing treats areears = null as N . So need to do the validation . FOr bug 5172808
833 /*
834 IF(p_pricing_method <> 'SM' AND p_arrears IS NULL) THEN
835 i:=x_qa_result_tbl.COUNT;
836 i:=i+1;
837 x_qa_result_tbl(i).check_code:='validate_payment_options';
838 x_qa_result_tbl(i).check_meaning:='ARREARS IS NOT FOUND FOR QUOTE';
839 x_qa_result_tbl(i).result_code:='ERROR';
840 x_qa_result_tbl(i).result_meaning:='ERROR';
841 x_qa_result_tbl(i).message_code:= 'OKL_QA_ARR_NOT_FOUND';
842 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_ARR_NOT_FOUND');
843
844 END IF;
845 */
846 IF((p_pricing_method <> 'SM' AND p_pricing_method <> 'SP') AND p_periodic_amt IS NULL) THEN
847 i:=x_qa_result_tbl.COUNT;
848 i:=i+1;
849 x_qa_result_tbl(i).check_code:='validate_payment_options';
850 x_qa_result_tbl(i).check_meaning:='PERIODIC AMOUNT IS NOT FOUND FOR SRT';
851 x_qa_result_tbl(i).result_code:='ERROR';
852 x_qa_result_tbl(i).result_meaning:='ERROR';
853 x_qa_result_tbl(i).message_code:= 'OKL_QA_SRT_AMT_NOT_FOUND';
854 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SRT_AMT_NOT_FOUND');
855
856 END IF;
857
858 EXCEPTION
859 -- other appropriate handlers
860 WHEN OTHERS THEN
861 -- store SQL error message on message stack
862 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
863 p_msg_name => G_DB_ERROR,
864 p_token1 => G_PROG_NAME_TOKEN,
865 p_token1_value => 'OKLRQQCB.pls.val_pay_opt',
866 p_token2 => G_SQLCODE_TOKEN,
867 p_token2_value => sqlcode,
868 p_token3 => G_SQLERRM_TOKEN,
869 p_token3_value => sqlerrm);
870 -- notify UNEXPECTED error
871 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
872 END validate_payment_options;
873 --------------------------------------------------------------------------------
874 /**
875 This function Check whether the Quote Level Payments Options Enterd or Not
876 **/
877 FUNCTION are_qte_pricing_opts_entered(p_lease_qte_rec IN lp_lq_header_rec%TYPE
878 ,p_payment_count IN NUMBER
879 ,x_return_status OUT NOCOPY VARCHAR2)
880 RETURN VARCHAR2 IS
881 -- Variables Declarations
882 p_api_version CONSTANT NUMBER DEFAULT 1.0;
883 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'qte_pr_entr';
884 l_falg VARCHAR2(3) := 'N';
885 BEGIN
886 IF p_lease_qte_rec.pricing_method = 'SY' THEN
887 IF p_payment_count <> 0 THEN
888 return 'Y';
889 END IF;
890 ELSIF p_lease_qte_rec.pricing_method = 'RC' THEN
891 IF p_lease_qte_rec.structured_pricing = 'N' AND p_lease_qte_rec.rate_card_id IS NOT NULL
892 OR p_lease_qte_rec.structured_pricing = 'Y' AND p_lease_qte_rec.lease_rate_factor IS NOT NULL
893 THEN
894 return 'Y';
895 END IF;
896 ELSIF p_lease_qte_rec.pricing_method = 'SM' THEN
897 IF (p_lease_qte_rec.structured_pricing = 'N' AND ( p_lease_qte_rec.rate_template_id IS NOT NULL OR p_payment_count <> 0 ) )
898 OR ( p_lease_qte_rec.structured_pricing = 'Y' AND p_payment_count <> 0 )
899 THEN
900 return 'Y';
901 END IF;
902 ELSIF p_lease_qte_rec.pricing_method <> 'TR' THEN
903 IF p_lease_qte_rec.structured_pricing = 'N' AND p_lease_qte_rec.rate_template_id IS NOT NULL
904 OR p_lease_qte_rec.structured_pricing = 'Y' AND p_payment_count <> 0
905 THEN
906 return 'Y';
907 END IF;
908 END IF;
909
910 x_return_status := G_RET_STS_SUCCESS;
911 RETURN 'N';
912
913 EXCEPTION
914 -- other appropriate handlers
915 WHEN OTHERS THEN
916 -- store SQL error message on message stack
917 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
918 p_msg_name => G_DB_ERROR,
919 p_token1 => G_PROG_NAME_TOKEN,
920 p_token1_value => 'OKLRQQCB.pls.are_qte_op_enter',
921 p_token2 => G_SQLCODE_TOKEN,
922 p_token2_value => sqlcode,
923 p_token3 => G_SQLERRM_TOKEN,
924 p_token3_value => sqlerrm);
925 -- notify UNEXPECTED error
926 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
927
928 END are_qte_pricing_opts_entered;
929 --------------------------------------------------------------------------------
930 /**
931 This function returns 'Y' when all the Asset on Quote Overriden Completely
932 else Returns 'N'
933 **/
934 FUNCTION are_all_lines_overriden(p_quote_id IN NUMBER
935 ,p_pricing_method IN VARCHAR2
936 ,p_line_level_pricing IN VARCHAR2
937 ,x_return_status OUT NOCOPY VARCHAR2)
938 RETURN VARCHAR2 IS
939 -- Variables Declarations
940 p_api_version CONSTANT NUMBER DEFAULT 1.0;
941 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'all_lns_ovr';
942 l_all_lines_overriden VARCHAR2(3) := 'N';
943 l_ovr_cnt NUMBER;
944 l_ast_cnt NUMBER;
945 CURSOR llo_flag_csr IS
946 SELECT count(*) overriden_assets_count
947 FROM OKL_LEASE_QUOTES_B QTE,
948 OKL_ASSETS_B AST
949 WHERE AST.PARENT_OBJECT_ID = QTE.ID
950 AND ( AST.RATE_TEMPLATE_ID IS NOT NULL
951 OR AST.STRUCTURED_PRICING = 'Y' )
952 AND QTE.ID = p_quote_id
953 AND p_line_level_pricing = 'Y';
954
955 CURSOR rc_llo_flag_csr IS
956 SELECT count(*) overriden_assets_count
957 FROM OKL_LEASE_QUOTES_B QTE,
958 OKL_ASSETS_B AST
959 WHERE AST.PARENT_OBJECT_ID = QTE.ID
960 AND ( AST.RATE_CARD_ID IS NOT NULL
961 OR AST.LEASE_RATE_FACTOR IS NOT NULL )
962 AND QTE.ID = p_quote_id
963 AND p_line_level_pricing = 'Y';
964
965 CURSOR sy_llo_flag_csr IS
966 SELECT count(*) overriden_assets_count
967 FROM OKL_LEASE_QUOTES_B QTE,
968 OKL_ASSETS_B AST
969 WHERE AST.PARENT_OBJECT_ID = QTE.ID
970 AND ( AST.STRUCTURED_PRICING = 'Y')
971 AND QTE.ID = p_quote_id
972 AND p_line_level_pricing = 'Y';
973
974 CURSOR ast_cnt_csr IS
975 SELECT count(*) assets_count
976 FROM OKL_LEASE_QUOTES_B QTE,
977 OKL_ASSETS_B AST
978 WHERE AST.PARENT_OBJECT_ID = QTE.ID
979 AND QTE.ID = p_quote_id;
980
981 BEGIN
982 IF p_pricing_method = 'SY' THEN
983 OPEN sy_llo_flag_csr;
984 FETCH sy_llo_flag_csr INTO l_ovr_cnt;
985 CLOSE sy_llo_flag_csr;
986 ELSIF p_pricing_method = 'RC' THEN
987 OPEN rc_llo_flag_csr;
988 FETCH rc_llo_flag_csr INTO l_ovr_cnt;
989 CLOSE rc_llo_flag_csr;
990 ELSIF p_pricing_method <> 'TR' THEN
991 OPEN llo_flag_csr;
992 FETCH llo_flag_csr INTO l_ovr_cnt;
993 CLOSE llo_flag_csr;
994 END IF;
995 OPEN ast_cnt_csr;
996 FETCH ast_cnt_csr INTO l_ast_cnt;
997 CLOSE ast_cnt_csr;
998 IF l_ast_cnt = 0 THEN
999 l_all_lines_overriden := 'N';
1000 ELSIF l_Ast_cnt = l_ovr_cnt THEN
1001 l_all_lines_overriden := 'Y';
1002 ELSE
1003 l_all_lines_overriden := 'N';
1004 END IF;
1005 x_return_status := G_RET_STS_SUCCESS;
1006 RETURN l_all_lines_overriden;
1007
1008 EXCEPTION
1009 -- other appropriate handlers
1010 WHEN OTHERS THEN
1011 -- store SQL error message on message stack
1012 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1013 p_msg_name => G_DB_ERROR,
1014 p_token1 => G_PROG_NAME_TOKEN,
1015 p_token1_value => 'OKLRQQCB.pls.all_lns_over',
1016 p_token2 => G_SQLCODE_TOKEN,
1017 p_token2_value => sqlcode,
1018 p_token3 => G_SQLERRM_TOKEN,
1019 p_token3_value => sqlerrm);
1020 -- notify UNEXPECTED error
1021 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1022
1023 END are_all_lines_overriden;
1024
1025 /*------------------------------------------------------------------------------
1026 -- PROCEDURE validate_cost_adjustments
1027 ------------------------------------------------------------------------------
1028 -- Start of comments
1029 --
1030 -- Procedure Name : validate_cost_adjustments
1031 -- Description : Validate the Various Objects Under Adjustment Tab
1032 Associated with Quote and distributed assets.
1033 --
1034 -- Business Rules : Validate Down Payment/Subsidy/Trade-In of Quote
1035 --
1036 -- Parameters : p_object_id -- Lease Quote
1037
1038 p_object_type -- valid values are 'LEASEQUOTE'
1039 hold which type of object this method is calling
1040
1041 x_qa_result_tbl --> Hold all the QA Results for Object
1042
1043 -- Version :
1044 -- History :
1045 -- End of comments
1046 ------------------------------------------------------------------------------*/
1047 PROCEDURE validate_cost_adjustments(p_api_version IN NUMBER
1048 ,p_init_msg_list IN VARCHAR2
1049 ,p_object_type IN VARCHAR2
1050 ,p_object_id IN NUMBER
1051 ,x_return_status OUT NOCOPY VARCHAR2
1052 ,x_msg_count OUT NOCOPY NUMBER
1053 ,x_msg_data OUT NOCOPY VARCHAR2
1054 ,x_qa_result_tbl IN OUT NOCOPY OKL_SALES_QUOTE_QA_PVT.qa_results_tbl_type
1055 ,x_qa_result IN OUT NOCOPY VARCHAR2) IS
1056 i INTEGER;
1057 x VARCHAR2(2);
1058 lp_object_type VARCHAR2(30);
1059 lp_quote_id NUMBER;
1060 lp_asset_id NUMBER;
1061 lp_asset_cost INTEGER:=0;
1062 lp_cost_adj_total INTEGER:=0;
1063 lp_qq_lines_cost INTEGER:=0;
1064 lp_rec_flag VARCHAR2(1);
1065 l_flag boolean:= FALSE;
1066 l_module CONSTANT fnd_log_messages.module%TYPE := 'val_c_ad';
1067 l_debug_enabled varchar2(10);
1068 is_debug_procedure_on boolean;
1069 is_debug_statement_on boolean;
1070 l_program_name CONSTANT VARCHAR2(30) := 'val_c_ad';
1071 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1072 --Fetch Cost Adj For a Quote
1073 CURSOR c_okl_cost_adj_rec(p_quote_id NUMBER,
1074 p_adj_source_type VARCHAR2) IS
1075 SELECT OCA.id,OCA.adjustment_source_id
1076 FROM OKL_COST_ADJUSTMENTS_B OCA,OKL_ASSETS_B OAB
1077 WHERE OCA.PARENT_OBJECT_CODE = 'ASSET'
1078 AND OCA.PARENT_OBJECT_ID = OAB.ID
1079 AND OAB.PARENT_OBJECT_CODE='LEASEQUOTE'
1080 AND OAB.PARENT_OBJECT_ID = p_quote_id
1081 AND OCA.ADJUSTMENT_SOURCE_TYPE= p_adj_source_type;
1082 lp_okl_cost_adj_rec c_okl_cost_adj_rec%ROWTYPE;
1083 BEGIN
1084 l_debug_enabled := okl_debug_pub.check_log_enabled;
1085 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1086 ,fnd_log.level_procedure);
1087
1088
1089 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1090 okl_debug_pub.log_debug(fnd_log.level_procedure
1091 ,l_module
1092 ,'begin debug OKLRQQCB.pls.validate_cost_adjustments call validate_cost_adjustments');
1093 END IF; -- check for logging on STATEMENT level
1094 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1095 ,fnd_log.level_statement);
1096
1097 -- call START_ACTIVITY to create savepoint, check compatibility
1098 -- and initialize message list
1099
1100 x_return_status := okl_api.start_activity(p_api_name=>l_api_name
1101 ,p_pkg_name=>G_PKG_NAME
1102 ,p_init_msg_list=>p_init_msg_list
1103 ,p_api_version=>p_api_version
1104 ,l_api_version=>p_api_version
1105 ,p_api_type=>G_API_TYPE
1106 ,x_return_status=>x_return_status); -- check if activity started successfully
1107
1108 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1109 RAISE okl_api.g_exception_unexpected_error;
1110 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1111 RAISE okl_api.g_exception_error;
1112 END IF;
1113 lp_object_type:= p_object_type;
1114 --start added abhsaxen for bug #5257890
1115 lp_quote_id := p_object_id;
1116 --end added abhsaxen for bug # 5257890
1117 IF(lp_quote_id IS NOT NULL) THEN
1118 IF(p_object_type='LEASEQUOTE') THEN
1119 OPEN c_lq_header_rec(lp_quote_id);
1120 FETCH c_lq_header_rec INTO lp_lq_header_rec;
1121 CLOSE c_lq_header_rec;
1122 --Check for Down Payment on a Quote Having payment Method 'SD'
1123 --It should not exist
1124 IF(lp_lq_header_rec.pricing_method = 'SD') THEN
1125 OPEN c_okl_cost_adj_rec(lp_quote_id,'DOWN_PAYMENT');
1126 FETCH c_okl_cost_adj_rec INTO lp_okl_cost_adj_rec;
1127 IF(c_okl_cost_adj_rec%FOUND) THEN
1128 i:=x_qa_result_tbl.COUNT;
1129 i:=i+1;
1130 x_qa_result_tbl(i).check_code:='validate_cost_adjustments';
1131 x_qa_result_tbl(i).check_meaning:='DOWN_PAYMENT_ADJ_SPECIFIED_FOR_QUOTE';
1132 x_qa_result_tbl(i).result_code:='ERROR';
1133 x_qa_result_tbl(i).result_meaning:='ERROR';
1134 x_qa_result_tbl(i).message_code:= 'OKL_QA_DN_ADJ_EXIST';
1135 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_DN_ADJ_EXIST');
1136 x_qa_result := okl_api.g_ret_sts_error;
1137 END IF;
1138 CLOSE c_okl_cost_adj_rec;
1139 END IF;--End of Checking for Down Payment (SD method)
1140 --Check for Trade In Adj on a Quote Having payment Method 'SI'
1141 --It should not exist
1142 IF(lp_lq_header_rec.pricing_method = 'SI') THEN
1143 OPEN c_okl_cost_adj_rec(lp_quote_id,'TRADEIN');
1144 FETCH c_okl_cost_adj_rec INTO lp_okl_cost_adj_rec;
1145 IF(c_okl_cost_adj_rec%FOUND) THEN
1146 i:=x_qa_result_tbl.COUNT;
1147 i:=i+1;
1148 x_qa_result_tbl(i).check_code:='validate_cost_adjustments';
1149 x_qa_result_tbl(i).check_meaning:='TRADE_IN_ADJ_SPECIFIED_FOR_QUOTE';
1150 x_qa_result_tbl(i).result_code:='ERROR';
1151 x_qa_result_tbl(i).result_meaning:='ERROR';
1152 x_qa_result_tbl(i).message_code:= 'OKL_QA_TRDIN_ADJ_EXIST';
1153 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_TRDIN_ADJ_EXIST');
1154 x_qa_result := okl_api.g_ret_sts_error;
1155 END IF;
1156 CLOSE c_okl_cost_adj_rec;
1157 END IF;--End of Checking for Trade In (SI method)
1158
1159 --Check for Subsidy Adj on a Quote Having payment Method 'SS'
1160 --if the Subsidy Id in okl_cost_adjustments_b is not null
1161 --then It should not exist else it can exist(Calculated by ISG)
1162 IF(lp_lq_header_rec.pricing_method = 'SS') THEN
1163 OPEN c_okl_cost_adj_rec(lp_quote_id,'SUBSIDY');
1164 FETCH c_okl_cost_adj_rec INTO lp_okl_cost_adj_rec;
1165 --start modified for bug#5257890
1166 IF(c_okl_cost_adj_rec%FOUND) THEN
1167 --end modified for bug#5257890
1168 i:=x_qa_result_tbl.COUNT;
1169 i:=i+1;
1170 x_qa_result_tbl(i).check_code:='validate_cost_adjustments';
1171 x_qa_result_tbl(i).check_meaning:='SUBSIDY_ADJ_SPECIFIED_FOR_QUOTE';
1172 x_qa_result_tbl(i).result_code:='ERROR';
1173 x_qa_result_tbl(i).result_meaning:='ERROR';
1174 x_qa_result_tbl(i).message_code:= 'OKL_QA_SUBSIDY_ADJ_EXIST';
1175 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SUBSIDY_ADJ_EXIST');
1176 x_qa_result := okl_api.g_ret_sts_error;
1177 END IF;
1178 CLOSE c_okl_cost_adj_rec;
1179 END IF;--End of Checking for Trade In (SI method)
1180 END IF;--LQ End
1181 IF(p_object_type='QUICKQUOTE') THEN
1182 null;
1183 END IF;--QQ End
1184 END IF;--If Quote id Is NOT NULL:
1185
1186 okl_api.end_activity(x_msg_count => x_msg_count
1187 ,x_msg_data => x_msg_data);
1188
1189 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1190 okl_debug_pub.log_debug(fnd_log.level_procedure
1191 ,l_module
1192 ,'end debug okl_sales_quote_qa_pvt.validate_cost_adjustments call validate_cost_adjustments');
1193 END IF;
1194
1195
1196 EXCEPTION
1197 WHEN okl_api.g_exception_error THEN
1198
1199 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
1200 ,p_pkg_name =>G_PKG_NAME
1201 ,p_exc_name =>'OKL_API.G_RET_STS_ERROR'
1202 ,x_msg_count =>x_msg_count
1203 ,x_msg_data =>x_msg_data
1204 ,p_api_type =>G_API_TYPE);
1205
1206 WHEN okl_api.g_exception_unexpected_error THEN
1207
1208 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
1209 ,p_pkg_name =>G_PKG_NAME
1210 ,p_exc_name =>'OKL_API.G_RET_STS_UNEXP_ERROR'
1211 ,x_msg_count =>x_msg_count
1212 ,x_msg_data =>x_msg_data
1213 ,p_api_type =>G_API_TYPE);
1214
1215 WHEN OTHERS THEN
1216
1217 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
1218 ,p_pkg_name =>G_PKG_NAME
1219 ,p_exc_name =>'OTHERS'
1220 ,x_msg_count =>x_msg_count
1221 ,x_msg_data =>x_msg_data
1222 ,p_api_type =>G_API_TYPE);
1223 END validate_cost_adjustments;
1224
1225 /*------------------------------------------------------------------------------
1226 -- PROCEDURE validate_system_validations
1227 ---------------------------------------------------------------------------------
1228 -- Start of comments
1229 --
1230 -- Procedure Name : validate_system_validations
1231 -- Description : This procedure will executes all the Validation under
1232 Validation Set created for Particular ORG in Setup.
1233 --
1234 -- Business Rules : Fetch all the System validation Listed under the System
1235 Validation Set and executes it
1236 --
1237 -- Parameters : p_object_id -- Lease Quote Id
1238
1239 p_object_type -- valid values are 'LEASEQUOTE'
1240 hold which type of object this method is calling
1241
1242 x_qa_result_tbl -- Hold all the QA Results for Object
1243
1244 -- Version : 1.0
1245 -- History :
1246 -- End of comments
1247 ------------------------------------------------------------------------------*/
1248 PROCEDURE validate_system_validations(p_api_version IN NUMBER
1249 ,p_init_msg_list IN VARCHAR2
1250 ,p_object_type IN VARCHAR2
1251 ,p_object_id IN NUMBER
1252 ,x_return_status OUT NOCOPY VARCHAR2
1253 ,x_msg_count OUT NOCOPY NUMBER
1254 ,x_msg_data OUT NOCOPY VARCHAR2
1255 ,x_qa_result_tbl IN OUT NOCOPY OKL_SALES_QUOTE_QA_PVT.qa_results_tbl_type
1256 ,x_qa_result IN OUT NOCOPY VARCHAR2) IS
1257
1258 i INTEGER;
1259 ret boolean;
1260 fun_ret number;
1261 call_user boolean;
1262 l_function_name okl_data_src_fnctns_v.name%TYPE;
1263 l_source_name okl_data_src_fnctns_v.source%TYPE;
1264 l_failure_severity VARCHAR2(30);
1265 l_module CONSTANT fnd_log_messages.module%TYPE := 'OKL_SALES_QUOTE_QA_PVT.validate_system_validation';
1266 l_debug_enabled varchar2(10);
1267 is_debug_procedure_on boolean;
1268 is_debug_statement_on boolean;
1269 l_message_name varchar2(2000);
1270 l_program_name CONSTANT VARCHAR2(30) := 'run_qa_qq';
1271 l_api_name CONSTANT VARCHAR2(61) := l_program_name;
1272
1273 --Cursor will get validation Set Id from a Setup table
1274 CURSOR c_sys_opt_vls IS
1275 select validation_set_id
1276 FROM OKL_SYSTEM_PARAMS;
1277 --Cursor will fetch all the Validation for a validation Set
1278 CURSOR c_validation_func(p_vls_id NUMBER) IS
1279 SELECT VLD.failure_severity,FUNCTNS.NAME,FUNCTNS.SOURCE
1280 FROM OKL_VALIDATIONS_B VLD,OKL_DATA_SRC_FNCTNS_B FUNCTNS
1281 WHERE FUNCTNS.ID=VLD.FUNCTION_ID
1282 AND VLD.VALIDATION_SET_ID =p_vls_id;
1283
1284 l_validation_set_id NUMBER;
1285
1286 BEGIN
1287 l_debug_enabled := okl_debug_pub.check_log_enabled;
1288 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1289 ,fnd_log.level_procedure);
1290
1291
1292 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1293 okl_debug_pub.log_debug(fnd_log.level_procedure
1294 ,l_module
1295 ,'begin debug OKLRQQCB.pls call validate_system_validations');
1296 END IF; -- check for logging on STATEMENT level
1297 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1298 ,fnd_log.level_statement);
1299
1300 -- call START_ACTIVITY to create savepoint, check compatibility
1301 -- and initialize message list
1302
1303 x_return_status := okl_api.start_activity(p_api_name => l_api_name
1304 ,p_pkg_name => G_PKG_NAME
1305 ,p_init_msg_list => p_init_msg_list
1306 ,p_api_version => p_api_version
1307 ,l_api_version => p_api_version
1308 ,p_api_type => G_API_TYPE
1309 ,x_return_status => x_return_status); -- check if activity started successfully
1310
1311 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1312 RAISE okl_api.g_exception_unexpected_error;
1313 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1314 RAISE okl_api.g_exception_error;
1315 END IF;
1316
1317 fun_ret := 1;
1318 OPEN c_sys_opt_vls;
1319 FETCH c_sys_opt_vls INTO l_validation_set_id;
1320 CLOSE c_sys_opt_vls;
1321
1322 IF(l_validation_set_id IS NOT NULL) THEN
1323 FOR validation_func_rec IN c_validation_func(l_validation_set_id) LOOP
1324 execute_system_validation(p_api_version => p_api_version,
1325 p_init_msg_list => p_init_msg_list,
1326 x_return_status => x_return_status,
1327 x_msg_count => x_msg_count,
1328 x_msg_data => x_msg_data,
1329 p_function_name => validation_func_rec.NAME,
1330 x_value => fun_ret);
1331 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1332 RAISE okl_api.g_exception_unexpected_error;
1333 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1334 RAISE okl_api.g_exception_error;
1335 END IF;
1336
1337 IF(fun_ret IS NULL OR fun_ret = 0) THEN
1338 i:=x_qa_result_tbl.COUNT;
1339 i:=i+1;
1340 x_qa_result_tbl(i).check_code:='validate_system_validations';
1341 x_qa_result_tbl(i).check_meaning:=validation_func_rec.name;
1342 x_qa_result_tbl(i).result_code:=nvl(validation_func_rec.failure_severity,'WARNING');
1343 x_qa_result_tbl(i).result_meaning:= nvl(validation_func_rec.failure_severity,'WARNING');
1344 x_qa_result_tbl(i).message_code:= 'OKL_QA_VLD_ERR';
1345 x_qa_result_tbl(i).message_text:= get_msg_text('OKL_QA_VLD_ERR')||validation_func_rec.name;
1346 IF(nvl(validation_func_rec.failure_severity,'WARNING')='ERROR') THEN
1347 x_qa_result := okl_api.g_ret_sts_error;
1348 ELSE
1349 x_qa_result := okl_api.g_ret_sts_warning;
1350 END IF;
1351 END IF;
1352 END LOOP;
1353 END IF;
1354 okl_api.end_activity(x_msg_count => x_msg_count
1355 ,x_msg_data => x_msg_data);
1356 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1357 okl_debug_pub.log_debug(fnd_log.level_procedure
1358 ,l_module
1359 ,'end debug okl_sales_quote_qa_pvt call validate_system_validation');
1360 END IF;
1361 EXCEPTION
1362 WHEN okl_api.g_exception_error THEN
1363 IF c_validation_func%ISOPEN THEN
1364 CLOSE c_validation_func;
1365 END IF;
1366 IF c_sys_opt_vls%ISOPEN THEN
1367 CLOSE c_sys_opt_vls;
1368 END IF;
1369
1370 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
1371 ,p_pkg_name =>G_PKG_NAME
1372 ,p_exc_name =>'OKL_API.G_RET_STS_ERROR'
1373 ,x_msg_count =>x_msg_count
1374 ,x_msg_data =>x_msg_data
1375 ,p_api_type =>G_API_TYPE);
1376
1377 WHEN okl_api.g_exception_unexpected_error THEN
1378 IF c_validation_func%ISOPEN THEN
1379 CLOSE c_validation_func;
1380 END IF;
1381 IF c_sys_opt_vls%ISOPEN THEN
1382 CLOSE c_sys_opt_vls;
1383 END IF;
1384 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
1385 ,p_pkg_name =>G_PKG_NAME
1386 ,p_exc_name =>'OKL_API.G_RET_STS_UNEXP_ERROR'
1387 ,x_msg_count =>x_msg_count
1388 ,x_msg_data =>x_msg_data
1389 ,p_api_type =>G_API_TYPE);
1390
1391 WHEN OTHERS THEN
1392 IF c_validation_func%ISOPEN THEN
1393 CLOSE c_validation_func;
1394 END IF;
1395 IF c_sys_opt_vls%ISOPEN THEN
1396 CLOSE c_sys_opt_vls;
1397 END IF;
1398 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
1399 ,p_pkg_name =>G_PKG_NAME
1400 ,p_exc_name =>'OTHERS'
1401 ,x_msg_count =>x_msg_count
1402 ,x_msg_data =>x_msg_data
1403 ,p_api_type =>G_API_TYPE);
1404
1405 END validate_system_validations;
1406 /*------------------------------------------------------------------------------
1407 -- PROCEDURE validate_pricing_values
1408 ------------------------------------------------------------------------------
1409 -- Start of comments
1410 --
1411 -- Procedure Name : validate_pricing_values
1412 -- Description : Validation Related to Pricing Values for Quote
1413 --
1414 -- Business Rules :
1415 --
1416 -- Parameters : p_object_id -- Lease Quote Id
1417
1418 p_object_type -- valid values are 'LEASEQUOTE'
1419 hold which type of object this method is calling
1420
1421 x_qa_result_tbl --> Hold all the QA Results for Object
1422
1423 -- Version :
1424 -- History :
1425 -- End of comments
1426 ------------------------------------------------------------------------------*/
1427 PROCEDURE validate_pricing_values(p_api_version IN NUMBER
1428 ,p_init_msg_list IN VARCHAR2
1429 ,p_object_type IN VARCHAR2
1430 ,p_object_id IN NUMBER
1431 ,x_return_status OUT NOCOPY VARCHAR2
1432 ,x_msg_count OUT NOCOPY NUMBER
1433 ,x_msg_data OUT NOCOPY VARCHAR2
1434 ,x_qa_result_tbl IN OUT NOCOPY OKL_SALES_QUOTE_QA_PVT.qa_results_tbl_type
1435 ,x_qa_result IN OUT NOCOPY VARCHAR2) IS
1436 lp_object_type VARCHAR2(30);
1437 lp_quote_id NUMBER;
1438 lp_asset_id NUMBER;
1439 lp_asset_cost INTEGER:=0;
1440 lp_cost_adj_total INTEGER:=0;
1441 lp_qq_lines_cost INTEGER:=0;
1442 lp_lq_lines_cost INTEGER:=0;
1443 lp_rec_flag VARCHAR2(1);
1444 i INTEGER;
1445 l_flag boolean:= FALSE;
1446 -----------------------------------------------------------------------
1447 l_program_name CONSTANT VARCHAR2(30) := 'val_pri';
1448 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1449 l_validation_mode VARCHAR2(3):='LOV';
1450 x_okl_lrs_table OKL_EC_UPTAKE_PVT.okl_lease_rate_set_tbl_type;
1451 l_debug_enabled varchar2(10);
1452 is_debug_procedure_on boolean;
1453 is_debug_statement_on boolean;
1454 cursor c_lq_asset_lines_rec(p_lease_quote_id NUMBER) IS
1455 --select SUM(NVL(OEC_PERCENTAGE,0))
1456 select oec_percentage
1457 from OKL_LEASE_QUOTES_B OLQ,OKL_ASSETS_B OAB
1458 WHERE OLQ.PRICING_METHOD= 'SF'
1459 AND OAB.parent_object_code='LEASEQUOTE'
1460 AND OAB.parent_object_id = OLQ.id
1461 AND OLQ.id= p_lease_quote_id;
1462 --Fix Bug # 4898499 Start
1463 cursor c_lq_asset_comp_rec(p_lease_quote_id NUMBER) IS
1464 select oac.unit_cost
1465 from okl_assets_b oab,
1466 okl_asset_components_b oac
1467 where oab.parent_object_code='LEASEQUOTE'
1468 AND oab.id=oac.asset_id
1469 AND oac.primary_component='YES'
1470 AND oab.parent_object_id=p_lease_quote_id;
1471 --Fix Bug # 4898499 End
1472 cursor c_qq_asset_lines_rec(p_quick_quote_id NUMBER,p_pricing_method VARCHAR2,p_type VARCHAR2) IS
1473 select t2.* from OKL_QUICK_QUOTES_B t1,OKL_QUICK_QUOTE_LINES_B t2
1474 WHERE t1.PRICING_METHOD=p_pricing_method
1475 AND t2.type=p_type
1476 AND t1.id=p_quick_quote_id
1477 AND t2.quick_quote_id=t1.id;
1478
1479 -----------------------------------------------------------------------
1480 BEGIN
1481 l_debug_enabled := okl_debug_pub.check_log_enabled;
1482 is_debug_procedure_on := okl_debug_pub.check_log_on(l_api_name
1483 ,fnd_log.level_procedure);
1484
1485
1486 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1487 okl_debug_pub.log_debug(fnd_log.level_procedure
1488 ,l_api_name
1489 ,'begin debug OKLRQQCB.pls call validating_pricing_values');
1490 END IF; -- check for logging on STATEMENT level
1491 is_debug_statement_on := okl_debug_pub.check_log_on(l_api_name
1492 ,fnd_log.level_statement);
1493
1494 -- call START_ACTIVITY to create savepoint, check compatibility
1495 -- and initialize message list
1496 x_return_status := okl_api.start_activity(p_api_name=>l_api_name
1497 ,p_pkg_name=>G_PKG_NAME
1498 ,p_init_msg_list=>p_init_msg_list
1499 ,p_api_version=>p_api_version
1500 ,l_api_version=>p_api_version
1501 ,p_api_type=>G_API_TYPE
1502 ,x_return_status=>x_return_status); -- check if activity started successfully
1503
1504 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1505 RAISE okl_api.g_exception_unexpected_error;
1506 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1507 RAISE okl_api.g_exception_error;
1508 END IF;
1509
1510 lp_quote_id:=p_object_id;
1511 lp_object_type:= p_object_type;
1512 IF(lp_quote_id IS NOT NULL) THEN
1513 IF(p_object_type='LEASEQUOTE') THEN
1514 --All the OEC % must be entered of Total Assets Cost
1515 --should be 100% for Lease Quote having pricing method 'SF'
1516
1517 FOR lp_lq_asset_lines_rec IN c_lq_asset_lines_rec(lp_quote_id) LOOP
1518 --l_flag := TRUE;
1519 if(lp_lq_asset_lines_rec.oec_percentage IS NULL) THEN
1520 l_flag := false;
1521 i:=x_qa_result_tbl.COUNT;
1522 i:=i+1;
1523 x_qa_result_tbl(i).check_code:='validate_pricing_values';
1524 x_qa_result_tbl(i).check_meaning:='TOTAL_COST_PER_NOT_MATCH ';
1525 x_qa_result_tbl(i).result_code:='ERROR';
1526 x_qa_result_tbl(i).result_meaning:='ERROR';
1527 x_qa_result_tbl(i).message_code:= 'OKL_QA_TC_PER_INVALID';
1528 x_qa_result_tbl(i).message_text:= get_msg_text('OKL_QA_TC_PER_INVALID');
1529 x_qa_result := okl_api.G_RET_STS_ERROR;
1530 EXIT;
1531 ELSE
1532 l_flag := TRUE;
1533 lp_lq_lines_cost := lp_lq_lines_cost + NVL(lp_lq_asset_lines_rec.OEC_PERCENTAGE,0);
1534 END IF;
1535 END LOOP;
1536 IF(l_flag AND lp_lq_lines_cost <> 100) THEN
1537 i:=x_qa_result_tbl.COUNT;
1538 i:=i+1;
1539 x_qa_result_tbl(i).check_code:='validate_pricing_values';
1540 x_qa_result_tbl(i).check_meaning:='TOTAL_COST_PER_NOT_MATCH ';
1541 x_qa_result_tbl(i).result_code:='ERROR';
1542 x_qa_result_tbl(i).result_meaning:='ERROR';
1543 x_qa_result_tbl(i).message_code:= 'OKL_QA_TC_PER_INVALID';
1544 x_qa_result_tbl(i).message_text:= get_msg_text('OKL_QA_TC_PER_INVALID');
1545 x_qa_result := okl_api.G_RET_STS_ERROR;
1546 END IF;
1547 --Fix Bug # 4898499 Start
1548 ---Check when Pricing Method is not SF
1549 --Then Assets Should Have the Unit Cost Defined on it
1550 OPEN c_lq_header_rec(lp_quote_id);
1551 FETCH c_lq_header_rec INTO lp_lq_header_rec;
1552 CLOSE c_lq_header_rec;
1553 IF(lp_lq_header_rec.pricing_method <> 'SF') THEN
1554 FOR lp_lq_asset_comp_rec IN c_lq_asset_comp_rec(lp_quote_id) LOOP
1555 IF(lp_lq_asset_comp_rec.unit_cost IS NULL) THEN
1556 l_flag := TRUE;
1557 EXIT;
1558 END IF;
1559 END LOOP;
1560 IF(l_flag) THEN
1561 i:=x_qa_result_tbl.COUNT;
1562 i:=i+1;
1563 x_qa_result_tbl(i).check_code:='validate_pricing_values';
1564 x_qa_result_tbl(i).check_meaning:='NO_UNIT_COST_DEFINED_FOR_ASSETS ';
1565 x_qa_result_tbl(i).result_code:='ERROR';
1566 x_qa_result_tbl(i).result_meaning:='ERROR';
1567 x_qa_result_tbl(i).message_code:= 'OKL_QA_ASS_UC_REQ';
1568 x_qa_result_tbl(i).message_text:= get_msg_text('OKL_QA_ASS_UC_REQ');
1569 x_qa_result := okl_api.G_RET_STS_ERROR;
1570 END IF;
1571 END IF;
1572 --Fix Bug # 4898499 End
1573 END IF;--LQ End
1574 IF(p_object_type='QUICKQUOTE') THEN
1575 FOR l_qq_header_rec IN c_qq_header_rec(lp_quote_id) LOOP
1576
1577 IF((l_qq_header_rec.pricing_method='TR')
1578 AND (l_qq_header_rec.target_rate_type IS NULL OR l_qq_header_rec.target_rate IS NULL)) THEN
1579 i:=x_qa_result_tbl.COUNT;
1580 i:=i+1;
1581 x_qa_result_tbl(i).check_code:='validate_pricing_values';
1582 x_qa_result_tbl(i).check_meaning:='TARGET_TYPE_AND_VALUE_NOT_SPECIFIED_FOR_QUOTE';
1583 x_qa_result_tbl(i).result_code:='ERROR';
1584 x_qa_result_tbl(i).result_meaning:='ERROR';
1585 x_qa_result_tbl(i).message_code:= 'OKL_QA_TARGET_TYPE_VALUE_REQ';
1586 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_TARGET_TYPE_VALUE_REQ');
1587 x_qa_result := okl_api.g_ret_sts_error;
1588 END IF;
1589 END LOOP;
1590 FOR lp_qq_asset_lines_rec IN c_qq_asset_lines_rec(lp_quote_id,'RC','SUBSIDY') LOOP
1591 IF((lp_qq_asset_lines_rec.basis IS NOT NULL) AND (lp_qq_asset_lines_rec.basis <> 'FIXED' AND lp_qq_asset_lines_rec.basis <> 'ASSET_COST')) THEN
1592 i:=x_qa_result_tbl.COUNT;
1593 i:=i+1;
1594 x_qa_result_tbl(i).check_code:='validate_pricing_values';
1595 x_qa_result_tbl(i).check_meaning:='SUBSIDY BASIS NOT VALID';
1596 x_qa_result_tbl(i).result_code:='ERROR';
1597 x_qa_result_tbl(i).result_meaning:='ERROR';
1598 x_qa_result_tbl(i).message_code:= 'OKL_QA_SUBSIDY_BASIS_NOT_VALID';
1599 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SUBSIDY_BASIS_NOT_VALID');
1600 x_qa_result := okl_api.g_ret_sts_error;
1601 EXIT;
1602 END IF;
1603 END LOOP;
1604 FOR lp_qq_asset_lines_rec IN c_qq_asset_lines_rec(lp_quote_id,'SF','ITEM_CATEGORY') LOOP
1605 l_flag := TRUE;
1606 lp_qq_lines_cost := lp_qq_lines_cost + NVL(lp_qq_asset_lines_rec.PERCENTAGE_OF_TOTAL_COST,0);
1607 END LOOP;
1608 IF(l_flag AND lp_qq_lines_cost <> 100) THEN
1609 i:=x_qa_result_tbl.COUNT;
1610 i:=i+1;
1611 x_qa_result_tbl(i).check_code:='validate_pricing_values';
1612 x_qa_result_tbl(i).check_meaning:='TOTAL_COST_PER_NOT_MATCH ';
1613 x_qa_result_tbl(i).result_code:='ERROR';
1614 x_qa_result_tbl(i).result_meaning:='ERROR';
1615 x_qa_result_tbl(i).message_code:= 'OKL_QA_TC_PER_INVALID';
1616 x_qa_result_tbl(i).message_text:= get_msg_text('OKL_QA_TC_PER_INVALID');
1617 x_qa_result := okl_api.G_RET_STS_ERROR;
1618 END IF;
1619
1620 END IF;--End OF QQ
1621 END IF;-----Quote ID Is not null
1622
1623 okl_api.end_activity(x_msg_count => x_msg_count
1624 ,x_msg_data => x_msg_data);
1625
1626 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1627 okl_debug_pub.log_debug(fnd_log.level_procedure
1628 ,l_api_name
1629 ,'end debug okl_sales_quote_qa_pvt call validating_pricing_values');
1630 END IF;
1631 EXCEPTION
1632 WHEN OTHERS THEN
1633 x_return_status := okl_api.G_RET_STS_ERROR;
1634 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1635 p_msg_name => G_DB_ERROR,
1636 p_token1 => G_PROG_NAME_TOKEN,
1637 p_token1_value => 'OKLRQQCB.pls.val_pric_val',
1638 p_token2 => G_SQLCODE_TOKEN,
1639 p_token2_value => sqlcode,
1640 p_token3 => G_SQLERRM_TOKEN,
1641 p_token3_value => sqlerrm);
1642 END validate_pricing_values;
1643 /*------------------------------------------------------------------------------
1644 -- PROCEDURE populate_qq_rec_values
1645 ------------------------------------------------------------------------------
1646 -- Start of comments
1647 --
1648 -- Procedure Name : populate_qq_rec_values
1649 -- Description : Populate the Header Rec for Quick Quote
1650 --
1651 -- Business Rules :Populate Header for Quick Quote for a Given Quick Quote id
1652 --
1653 -- Parameters : p_object_id -- Quick Quote id
1654
1655 lp_qq_header_rec - Header Rec structure for Quick Quote
1656
1657 x_okl_ec_rec --> Hold all the Eligibility criteria values
1658 for a given Quick Quote
1659
1660 -- Version :
1661 -- History :
1662 -- End of comments
1663 ------------------------------------------------------------------------------*/
1664 procedure populate_qq_rec_values(p_quick_quote_id NUMBER,
1665 lp_qq_header_rec OUT NOCOPY c_qq_header_rec%ROWTYPE,
1666 x_okl_ec_rec IN OUT NOCOPY okl_ec_evaluate_pvt.okl_ec_rec_type) IS
1667
1668 l_validation_mode VARCHAR2(3):='LOV';
1669 l_item_cat_id NUMBER;
1670 i INTEGER;
1671 lp_line_amt NUMBER;
1672
1673 cursor c_qq_assets_cost(p_quick_quote_id NUMBER) IS
1674 select ITEM_CATEGORY_ID
1675 FROM OKL_QUICK_QUOTE_LINES_B
1676 WHERE type='ITEM_CATEGORY'
1677 AND quick_quote_id=p_quick_quote_id;
1678
1679 cursor c_payment_lines_types(p_quick_quote_id NUMBER,p_payment_type VARCHAR2) IS
1680 select t2.value
1681 from OKL_QUICK_QUOTE_LINES_B t2
1682 WHERE t2.type = p_payment_type
1683 and t2.QUICK_QUOTE_ID = p_quick_quote_id;
1684
1685 BEGIN
1686 i := 1;
1687 OPEN c_qq_header_rec(p_quick_quote_id);
1688 FETCH c_qq_header_rec INTO lp_qq_header_rec;
1689 CLOSE c_qq_header_rec;
1690 OPEN c_qq_assets_cost(p_quick_quote_id);
1691 FETCH c_qq_assets_cost INTO l_item_cat_id;
1692 WHILE c_qq_assets_cost%FOUND LOOP
1693
1694 x_okl_ec_rec.item_categories_table(i):= l_item_cat_id;
1695 i := i+1;
1696 FETCH c_qq_assets_cost INTO l_item_cat_id;
1697 END LOOP;
1698 CLOSE c_qq_assets_cost;
1699 /*
1700 Check Source Type and value range
1701 */
1702 x_okl_ec_rec.target_id := p_quick_quote_id;
1703 x_okl_ec_rec.target_eff_from := lp_qq_header_rec.expected_start_date;
1704 x_okl_ec_rec.term := lp_qq_header_rec.term;
1705 x_okl_ec_rec.territory := lp_qq_header_rec.sales_territory_id;
1706
1707 OPEN c_payment_lines_types(p_quick_quote_id,'DOWN_PAYMENT');
1708 FETCH c_payment_lines_types INTO lp_line_amt;
1709 CLOSE c_payment_lines_types;
1710 x_okl_ec_rec.down_payment := lp_line_amt;
1711
1712 OPEN c_payment_lines_types(p_quick_quote_id,'TRADEIN');
1713 FETCH c_payment_lines_types INTO lp_line_amt;
1714 CLOSE c_payment_lines_types;
1715 x_okl_ec_rec.trade_in_value :=lp_line_amt;
1716 x_okl_ec_rec.validation_mode := l_validation_mode;
1717 EXCEPTION
1718 WHEN OTHERS THEN
1719
1720 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1721 p_msg_name => G_DB_ERROR,
1722 p_token1 => G_PROG_NAME_TOKEN,
1723 p_token1_value => 'VALIDATION_SET_QA',
1724 p_token2 => G_SQLCODE_TOKEN,
1725 p_token2_value => sqlcode,
1726 p_token3 => G_SQLERRM_TOKEN,
1727 p_token3_value => sqlerrm);
1728
1729 END populate_qq_rec_values;
1730 /*------------------------------------------------------------------------------
1731 -- PROCEDURE validate_ec_criteria
1732 ------------------------------------------------------------------------------
1733 -- Start of comments
1734 --
1735 -- Procedure Name : validate_ec_criteria
1736 -- Description : Procedure will Validate the EC Criteria for SRT/LRS/Product
1737 VPA
1738 --
1739 -- Business Rules : Inputs for EC criteria will be taken from Quote Attributes
1740 --
1741 -- Parameters : p_object_id -- Lease Quote /Quick Quote Id.
1742
1743 p_object_type -- valid values are 'LEASEQUOTE'/'QUICK QUOTE'
1744 hold which type of object this method is calling
1745
1746 x_qa_result_tbl --> Hold all the QA Results for Object
1747
1748 -- Version :
1749 -- History :
1750 -- End of comments
1751 ------------------------------------------------------------------------------*/
1752 PROCEDURE validate_ec_criteria(p_api_version IN NUMBER
1753 ,p_init_msg_list IN VARCHAR2
1754 ,p_object_type IN VARCHAR2
1755 ,p_object_id IN NUMBER
1756 ,x_return_status OUT NOCOPY VARCHAR2
1757 ,x_msg_count OUT NOCOPY NUMBER
1758 ,x_msg_data OUT NOCOPY VARCHAR2
1759 ,x_qa_result_tbl IN OUT NOCOPY OKL_SALES_QUOTE_QA_PVT.qa_results_tbl_type
1760 ,x_qa_result IN OUT NOCOPY VARCHAR2) IS
1761 lp_object_type VARCHAR2(30);
1762 lp_quote_id NUMBER;
1763 lp_asset_id NUMBER;
1764 lp_asset_cost INTEGER:=0;
1765 lp_cost_adj_total INTEGER:=0;
1766 lp_rec_flag VARCHAR2(1);
1767 i INTEGER;
1768 l_api_name CONSTANT VARCHAR2(61) := 'v_ec_crt';
1769 l_module CONSTANT fnd_log_messages.module%TYPE := 'v_ec_crt';
1770 l_debug_enabled varchar2(10);
1771 is_debug_procedure_on boolean;
1772 is_debug_statement_on boolean;
1773 l_validation_mode VARCHAR2(3):='LOV';
1774
1775 l_okl_ec_rec_type okl_ec_evaluate_pvt.okl_ec_rec_type;
1776 x_okl_ec_rec okl_ec_evaluate_pvt.okl_ec_rec_type;
1777 l_item_cat_id NUMBER;
1778 lp_vp_id NUMBER;
1779 l_check_ec_flag boolean:=FALSE;
1780 x_eligible boolean;
1781 l_obj_name okl_fe_std_rt_tmp_v.template_name%TYPE;
1782 l_message_name VARCHAR2(30);
1783 cursor c_qq_header_rec(p_quote_id NUMBER) IS
1784 SELECT t1.* FROM OKL_QUICK_QUOTES_B t1
1785 WHERE t1.id = p_quote_id;
1786 lp_qq_header_rec c_qq_header_rec%ROWTYPE;
1787
1788 cursor c_qq_assets_cost(p_quick_quote_id NUMBER) IS
1789 select ITEM_CATEGORY_ID
1790 FROM OKL_QUICK_QUOTE_LINES_B
1791 WHERE type='ITEM_CATEGORY'
1792 AND quick_quote_id=p_quick_quote_id;
1793
1794 --Bug # 5050143 ssdeshpa start
1795 cursor c_config_fee_rec(p_quote_id NUMBER) IS
1796 select ofv.id,
1797 ofv.rate_card_id,
1798 ofv.rate_template_id,
1799 'QUOTED_FEES' oty_code
1800 from okl_fees_b ofv
1801 where ofv.parent_object_code='LEASEQUOTE'
1802 AND ofv.parent_object_id=p_quote_id
1803 AND ofv.fee_type IN ('FINANCED','ROLLOVER')
1804 UNION
1805 select oab.id,
1806 oab.rate_card_id,
1807 oab.rate_template_id,
1808 'QUOTED_ASSET' oty_code
1809 from okl_assets_b oab
1810 where oab.parent_object_code='LEASEQUOTE'
1811 AND oab.parent_object_id=p_quote_id;
1812
1813 --Cursor to fetch LRS Name
1814 CURSOR c_lrs_rec(p_srt_ver_id NUMBER) IS
1815 select lrs.name
1816 from okl_ls_rt_fctr_sets_v lrs,okl_fe_rate_set_versions_v lrv
1817 where lrs.id=lrv.rate_set_id
1818 and lrv.rate_set_version_id = p_srt_ver_id;
1819 --Cursor to Fetch SRT Name
1820 CURSOR c_srt_rec(p_srt_ver_id NUMBER) IS
1821 select srt.template_name as name
1822 from
1823 okl_fe_std_rt_tmp_v srt,
1824 okl_fe_std_rt_tmp_vers srv
1825 where srt.std_rate_tmpl_id=srv.std_rate_tmpl_id
1826 AND srv.std_rate_tmpl_ver_id = p_srt_ver_id;
1827 --Bug # 5050143 ssdeshpa end
1828 BEGIN
1829 l_debug_enabled := okl_debug_pub.check_log_enabled;
1830 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1831 ,fnd_log.level_procedure);
1832
1833
1834 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1835 okl_debug_pub.log_debug(fnd_log.level_procedure
1836 ,l_module
1837 ,'begin debug OKLRQQCB.pls call validate_ec_criteria');
1838 END IF; -- check for logging on STATEMENT level
1839 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1840 ,fnd_log.level_statement);
1841
1842 -- call START_ACTIVITY to create savepoint, check compatibility
1843 -- and initialize message list
1844 x_return_status := okl_api.start_activity(p_api_name=>l_api_name
1845 ,p_pkg_name=>G_PKG_NAME
1846 ,p_init_msg_list=>p_init_msg_list
1847 ,p_api_version=>p_api_version
1848 ,l_api_version=>p_api_version
1849 ,p_api_type=>G_API_TYPE
1850 ,x_return_status=>x_return_status); -- check if activity started successfully
1851
1852 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1853 RAISE okl_api.g_exception_unexpected_error;
1854 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1855 RAISE okl_api.g_exception_error;
1856 END IF;
1857
1858 lp_object_type:= p_object_type;
1859 lp_quote_id:=p_object_id;
1860 IF(lp_quote_id IS NOT NULL) THEN
1861 IF(p_object_type='LEASEQUOTE') THEN
1862
1863 lp_vp_id := OKL_EC_UPTAKE_PVT.get_vp_id(lp_quote_id);
1864 --Bug # 5050143 ssdeshpa start
1865 populate_lq_rec_values(lp_quote_id,x_okl_ec_rec);
1866
1867 OPEN c_lq_header_rec(lp_quote_id);
1868 FETCH c_lq_header_rec INTO lp_lq_header_rec;
1869 CLOSE c_lq_header_rec;
1870 --Bug # 5050143 ssdeshpa end
1871 IF(lp_lq_header_rec.RATE_CARD_ID IS NOT NULL) THEN
1872 x_okl_ec_rec.src_id := lp_lq_header_rec.RATE_CARD_ID;
1873 x_okl_ec_rec.src_type := 'LRS';
1874 l_check_ec_flag := TRUE;
1875
1876 ELSIF (lp_lq_header_rec.RATE_TEMPLATE_ID IS NOT NULL) THEN
1877 x_okl_ec_rec.src_id := lp_lq_header_rec.RATE_TEMPLATE_ID;
1878 x_okl_ec_rec.src_type := 'SRT';
1879 l_check_ec_flag := TRUE;
1880 END IF;
1881
1882 IF(l_check_ec_flag) THEN
1883 OKL_ECC_PUB.evaluate_eligibility_criteria(p_api_version
1884 ,p_init_msg_list
1885 ,x_return_status
1886 ,x_msg_count
1887 ,x_msg_data
1888 ,x_okl_ec_rec
1889 ,x_eligible);
1890 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1891 RAISE okl_api.g_exception_unexpected_error;
1892 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1893 RAISE okl_api.g_exception_error;
1894 END IF;
1895 IF( NOT x_eligible) THEN
1896 --Set Message According to the Source Type
1897 i:=x_qa_result_tbl.COUNT;
1898 i:=i+1;
1899 x_qa_result_tbl(i).check_code:='validate_lrs_ec';
1900 x_qa_result_tbl(i).check_meaning:='LRS/SRT_IS_NOT_VALID_FOR_EC';
1901 x_qa_result_tbl(i).result_code:='ERROR';
1902 x_qa_result_tbl(i).result_meaning:='ERROR';
1903 IF( x_okl_ec_rec.src_type = 'SRT') THEN
1904 OPEN c_srt_rec(lp_lq_header_rec.RATE_TEMPLATE_ID);
1905 FETCH c_srt_rec INTO l_obj_name;
1906 CLOSE c_srt_rec;
1907 l_message_name := 'OKL_QA_SRT_TMPL_NOT_VALID';
1908 ELSIF( x_okl_ec_rec.src_type = 'LRS') THEN
1909 OPEN c_lrs_rec(lp_lq_header_rec.RATE_CARD_ID);
1910 FETCH c_lrs_rec INTO l_obj_name;
1911 CLOSE c_lrs_rec;
1912 l_message_name := 'OKL_QA_LRS_TMPL_NOT_VALID';
1913 END IF;
1914 x_qa_result_tbl(i).message_code:= l_message_name;
1915 set_fnd_message(p_msg_name => l_message_name
1916 ,p_token1 => 'NAME'
1917 ,p_value1 => l_obj_name
1918 ,p_token2 => NULL
1919 ,p_value2 => NULL
1920 ,p_token3 => NULL
1921 ,p_value3 => NULL
1922 ,p_token4 => NULL
1923 ,p_value4 => NULL);
1924 x_qa_result_tbl(i).message_text:= fnd_message.get;
1925 x_qa_result := okl_api.G_RET_STS_ERROR;
1926 END IF;
1927 END IF;
1928 --Bug # 5050143 ssdeshpa start
1929 --Check EC Criteria for Assets and Config Fees
1930 FOR l_config_fee_rec IN c_config_fee_rec(lp_quote_id) LOOP
1931 l_check_ec_flag := FALSE;
1932
1933 IF(l_config_fee_rec.RATE_CARD_ID IS NOT NULL) THEN
1934 x_okl_ec_rec.src_id := l_config_fee_rec.RATE_CARD_ID;
1935 x_okl_ec_rec.src_type := 'LRS';
1936 l_check_ec_flag := TRUE;
1937
1938 ELSIF (l_config_fee_rec.RATE_TEMPLATE_ID IS NOT NULL) THEN
1939 x_okl_ec_rec.src_id := l_config_fee_rec.RATE_TEMPLATE_ID;
1940 x_okl_ec_rec.src_type := 'SRT';
1941 l_check_ec_flag := TRUE;
1942 END IF;
1943
1944 IF(l_check_ec_flag) THEN
1945 OKL_ECC_PUB.evaluate_eligibility_criteria(p_api_version
1946 ,p_init_msg_list
1947 ,x_return_status
1948 ,x_msg_count
1949 ,x_msg_data
1950 ,x_okl_ec_rec
1951 ,x_eligible);
1952 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1953 RAISE okl_api.g_exception_unexpected_error;
1954 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1955 RAISE okl_api.g_exception_error;
1956 END IF;
1957 IF( NOT x_eligible) THEN
1958 --Set Message According to the Source Type
1959 i:=x_qa_result_tbl.COUNT;
1960 i:=i+1;
1961 x_qa_result_tbl(i).check_code:='validate_lrs_ec';
1962 x_qa_result_tbl(i).check_meaning:='LRS/SRT_IS_NOT_VALID_FOR_EC';
1963 x_qa_result_tbl(i).result_code:='ERROR';
1964 x_qa_result_tbl(i).result_meaning:='ERROR';
1965 IF( x_okl_ec_rec.src_type = 'SRT') THEN
1966 OPEN c_srt_rec(l_config_fee_rec.RATE_TEMPLATE_ID);
1967 FETCH c_srt_rec INTO l_obj_name;
1968 CLOSE c_srt_rec;
1969 l_message_name := 'OKL_QA_SRT_TMPL_NOT_VALID';
1970 ELSIF( x_okl_ec_rec.src_type = 'LRS') THEN
1971 OPEN c_lrs_rec(l_config_fee_rec.RATE_CARD_ID);
1972 FETCH c_lrs_rec INTO l_obj_name;
1973 CLOSE c_lrs_rec;
1974 l_message_name := 'OKL_QA_LRS_TMPL_NOT_VALID';
1975 END IF;
1976 x_qa_result_tbl(i).message_code:= l_message_name;
1977 set_fnd_message(p_msg_name => l_message_name
1978 ,p_token1 => 'NAME'
1979 ,p_value1 => l_obj_name
1980 ,p_token2 => NULL
1981 ,p_value2 => NULL
1982 ,p_token3 => NULL
1983 ,p_value3 => NULL
1984 ,p_token4 => NULL
1985 ,p_value4 => NULL);
1986 x_qa_result_tbl(i).message_text:= fnd_message.get;
1987 x_qa_result := okl_api.G_RET_STS_ERROR;
1988 EXIT;
1989 END IF;
1990 END IF;
1991 END LOOP;
1992 --Bug # 5050143 ssdeshpa end;
1993 IF(lp_vp_id IS NOT NULL) THEN
1994 x_okl_ec_rec.src_id := lp_vp_id;
1995 x_okl_ec_rec.src_type := 'VENDOR_PROGRAM';
1996 OKL_ECC_PUB.evaluate_eligibility_criteria(p_api_version
1997 ,p_init_msg_list
1998 ,x_return_status
1999 ,x_msg_count
2000 ,x_msg_data
2001 ,x_okl_ec_rec
2002 ,x_eligible);
2003 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2004 RAISE okl_api.g_exception_unexpected_error;
2005 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2006 RAISE okl_api.g_exception_error;
2007 END IF;
2008 IF( NOT x_eligible) THEN
2009 --Set Message According to the Source Type
2010 i:=x_qa_result_tbl.COUNT;
2011 i:=i+1;
2012 x_qa_result_tbl(i).check_code:='validate_lrs_ec';
2013 x_qa_result_tbl(i).check_meaning:='VPA_IS_NOT_VALID_FOR_EC';
2014 x_qa_result_tbl(i).result_code:='ERROR';
2015 x_qa_result_tbl(i).result_meaning:='ERROR';
2016 x_qa_result_tbl(i).message_code:= 'OKL_QA_VPA_NOT_VALID';
2017 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_VPA_NOT_VALID');
2018 x_qa_result := okl_api.G_RET_STS_ERROR;
2019 END IF;
2020 END IF;
2021
2022 IF(lp_lq_header_rec.PRODUCT_ID IS NOT NULL) THEN
2023 x_okl_ec_rec.src_id := lp_lq_header_rec.PRODUCT_ID;
2024 x_okl_ec_rec.src_type := 'PRODUCT';
2025 OKL_ECC_PUB.evaluate_eligibility_criteria(p_api_version
2026 ,p_init_msg_list
2027 ,x_return_status
2028 ,x_msg_count
2029 ,x_msg_data
2030 ,x_okl_ec_rec
2031 ,x_eligible);
2032 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2033 RAISE okl_api.g_exception_unexpected_error;
2034 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2035 RAISE okl_api.g_exception_error;
2036 END IF;
2037 IF( NOT x_eligible) THEN
2038 --Set Message According to the Source Type
2039 i:=x_qa_result_tbl.COUNT;
2040 i:=i+1;
2041 x_qa_result_tbl(i).check_code:='validate_lrs_ec';
2042 x_qa_result_tbl(i).check_meaning:='PRODUCT_IS_INVALID_FOR_EC';
2043 x_qa_result_tbl(i).result_code:='ERROR';
2044 x_qa_result_tbl(i).result_meaning:='ERROR';
2045 x_qa_result_tbl(i).message_code:= 'OKL_QA_PRODUCT_NOT_VALID';
2046 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_PRODUCT_NOT_VALID');
2047 x_qa_result := okl_api.G_RET_STS_ERROR;
2048 END IF;
2049 END IF;
2050
2051 END IF;--End OF LQ
2052 IF(p_object_type='QUICKQUOTE') THEN
2053
2054 populate_qq_rec_values(lp_quote_id,
2055 lp_qq_header_rec,
2056 x_okl_ec_rec);
2057
2058
2059 IF(lp_qq_header_rec.RATE_CARD_ID IS NOT NULL) THEN
2060 x_okl_ec_rec.src_id := lp_qq_header_rec.RATE_CARD_ID;
2061 x_okl_ec_rec.src_type := 'LRS';
2062 l_check_ec_flag := TRUE;
2063 ELSIF (lp_qq_header_rec.RATE_TEMPLATE_ID IS NOT NULL) THEN
2064 x_okl_ec_rec.src_id := lp_qq_header_rec.RATE_TEMPLATE_ID;
2065 x_okl_ec_rec.src_type := 'SRT';
2066 l_check_ec_flag := TRUE;
2067 END IF;
2068 IF(l_check_ec_flag) THEN
2069 OKL_ECC_PUB.evaluate_eligibility_criteria(p_api_version
2070 ,p_init_msg_list
2071 ,x_return_status
2072 ,x_msg_count
2073 ,x_msg_data
2074 ,x_okl_ec_rec
2075 ,x_eligible);
2076 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2077 RAISE okl_api.g_exception_unexpected_error;
2078 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2079 RAISE okl_api.g_exception_error;
2080 END IF;
2081 IF( NOT x_eligible) THEN
2082 --Set Message According to the Source Type
2083 i:=x_qa_result_tbl.COUNT;
2084 i:=i+1;
2085 x_qa_result_tbl(i).check_code:='validate_lrs_ec';
2086 x_qa_result_tbl(i).check_meaning:='LRS_IS_NOT_VALID_FOR_EC';
2087 x_qa_result_tbl(i).result_code:='ERROR';
2088 x_qa_result_tbl(i).result_meaning:='ERROR';
2089 x_qa_result_tbl(i).message_code:= 'OKL_QA_TMPL_IS_NOT_VALID';
2090 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_TMPL_IS_NOT_VALID');
2091 x_qa_result := okl_api.G_RET_STS_ERROR;
2092 END IF;
2093 END IF;
2094 IF(lp_qq_header_rec.PROGRAM_AGREEMENT_ID IS NOT NULL) THEN
2095 x_okl_ec_rec.src_id := lp_qq_header_rec.PROGRAM_AGREEMENT_ID;
2096 x_okl_ec_rec.src_type := 'VENDOR_PROGRAM';
2097 OKL_ECC_PUB.evaluate_eligibility_criteria(p_api_version
2098 ,p_init_msg_list
2099 ,x_return_status
2100 ,x_msg_count
2101 ,x_msg_data
2102 ,x_okl_ec_rec
2103 ,x_eligible);
2104 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2105 RAISE okl_api.g_exception_unexpected_error;
2106 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2107 RAISE okl_api.g_exception_error;
2108 END IF;
2109 IF(NOT x_eligible) THEN
2110 --Set Message According to the Source Type
2111 i:=x_qa_result_tbl.COUNT;
2112 i:=i+1;
2113 x_qa_result_tbl(i).check_code:='validate_lrs_ec';
2114 x_qa_result_tbl(i).check_meaning:='VPA_IS_NOT_VALID_FOR_EC';
2115 x_qa_result_tbl(i).result_code:='ERROR';
2116 x_qa_result_tbl(i).result_meaning:='ERROR';
2117 x_qa_result_tbl(i).message_code:= 'OKL_QA_VPA_NOT_VALID';
2118 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_VPA_NOT_VALID');
2119 x_qa_result := okl_api.G_RET_STS_ERROR;
2120 END IF;
2121 END IF;
2122
2123 END IF;--End OF QQ
2124 END IF;--quote_id is not null
2125 okl_api.end_activity(x_msg_count => x_msg_count
2126 ,x_msg_data => x_msg_data);
2127
2128 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2129 okl_debug_pub.log_debug(fnd_log.level_procedure
2130 ,l_module
2131 ,'end debug okl_sales_quote_qa_pvt call validate_ec_criteria');
2132 END IF;
2133 EXCEPTION
2134 WHEN okl_api.g_exception_error THEN
2135 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
2136 ,p_pkg_name =>G_PKG_NAME
2137 ,p_exc_name =>'OKL_API.G_RET_STS_ERROR'
2138 ,x_msg_count =>x_msg_count
2139 ,x_msg_data =>x_msg_data
2140 ,p_api_type =>G_API_TYPE);
2141
2142 WHEN okl_api.g_exception_unexpected_error THEN
2143
2144 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
2145 ,p_pkg_name =>G_PKG_NAME
2146 ,p_exc_name =>'OKL_API.G_RET_STS_UNEXP_ERROR'
2147 ,x_msg_count =>x_msg_count
2148 ,x_msg_data =>x_msg_data
2149 ,p_api_type =>G_API_TYPE);
2150
2151
2152
2153 END validate_ec_criteria;
2154 /*------------------------------------------------------------------------------
2155 -- PROCEDURE validate_financial_product
2156 ------------------------------------------------------------------------------
2157 -- Start of comments
2158 --
2159 -- Procedure Name : validate_financial_product
2160 -- Description : Validation Related to Product selected on a Quote
2161 --
2162 -- Business Rules :
2163 --
2164 -- Parameters : p_object_id -- Lease Quote /Quick Quote Id.
2165
2166 p_object_type -- valid values are 'LEASEQUOTE'/'QUICKQUOTE'
2167 hold which type of object this method is calling
2168
2169 x_qa_result_tbl --> Hold all the QA Results for Object
2170
2171 -- Version :
2172 -- History :
2173 -- End of comments
2174 ------------------------------------------------------------------------------*/
2175 PROCEDURE validate_financial_product(p_api_version IN NUMBER
2176 ,p_init_msg_list IN VARCHAR2
2177 ,p_object_type IN VARCHAR2
2178 ,p_object_id IN NUMBER
2179 ,x_return_status OUT NOCOPY VARCHAR2
2180 ,x_msg_count OUT NOCOPY NUMBER
2181 ,x_msg_data OUT NOCOPY VARCHAR2
2182 ,x_qa_result_tbl IN OUT NOCOPY OKL_SALES_QUOTE_QA_PVT.qa_results_tbl_type
2183 ,x_qa_result IN OUT NOCOPY VARCHAR2) IS
2184
2185 lp_quote_id NUMBER;
2186 lp_asset_id NUMBER;
2187 lp_asset_cost INTEGER:=0;
2188 lp_cost_adj_total1 INTEGER:= 0;
2189 lp_cost_adj_total2 INTEGER:= 0;
2190 lp_cost_adj_total INTEGER:= 0;
2191 lp_rec_flag VARCHAR2(1);
2192 i INTEGER;
2193
2194 cursor c_qq_header_rec(p_quick_quote_id NUMBER) IS
2195 SELECT t1.* FROM OKL_QUICK_QUOTES_B t1
2196 WHERE t1.id = p_quick_quote_id;
2197 lp_qq_header_rec c_qq_header_rec%ROWTYPE;
2198
2199 cursor c_qq_assets_cost(p_quick_quote_id NUMBER) IS
2200 SELECT SUM(NVL(VALUE,0))
2201 FROM OKL_QUICK_QUOTE_LINES_B
2202 WHERE type='ITEM_CATEGORY'
2203 AND quick_quote_id=p_quick_quote_id;
2204
2205 cursor c_qq_cost_adj_total_rec1(p_quick_quote_id NUMBER) IS
2206 select SUM(NVL(VALUE,0))
2207 from OKL_QUICK_QUOTE_LINES_B t2
2208 WHERE t2.type IN('DOWN_PAYMENT','TRADEIN','SUBSIDY')
2209 and t2.basis = 'FIXED'
2210 and t2.QUICK_QUOTE_ID=p_quick_quote_id;
2211
2212 cursor c_qq_cost_adj_total_rec2(p_quick_quote_id NUMBER) IS
2213 select SUM(NVL(VALUE,0))
2214 from OKL_QUICK_QUOTE_LINES_B t2
2215 WHERE t2.type IN('DOWN_PAYMENT','TRADEIN','SUBSIDY')
2216 and t2.basis = 'ASSET_COST'
2217 and t2.QUICK_QUOTE_ID=p_quick_quote_id;
2218
2219 cursor c_lq_cost_adj_rec(p_asset_id NUMBER) IS
2220 SELECT SUM(NVL(VALUE,0)) FROM OKL_COST_ADJUSTMENTS_B
2221 where PARENT_OBJECT_CODE='ASSET'
2222 and ADJUSTMENT_SOURCE_TYPE IN('DOWN_PAYMENT','TRADEIN','SUBSIDY')
2223 AND PARENT_OBJECT_ID=p_asset_id;
2224
2225 cursor c_lq_asset_lines_rec(p_quote_id NUMBER) IS
2226 SELECT t2.id,t2.oec
2227 FROM OKL_ASSETS_B t2
2228 where t2.parent_object_code='LEASEQUOTE'
2229 and t2.parent_object_id=p_quote_id;
2230
2231 BEGIN
2232 lp_quote_id := p_object_id;
2233 IF(lp_quote_id IS NOT NULL) THEN
2234 IF(p_object_type='LEASEQUOTE') THEN
2235 --Down Payment, Subsidy or Trade In Value cannot exceed
2236 --asset cost total (OEC)
2237 FOR lq_asset_lines_rec IN c_lq_asset_lines_rec(lp_quote_id) LOOP
2238 OPEN c_lq_cost_adj_rec(lq_asset_lines_rec.id);
2239 FETCH c_lq_cost_adj_rec INTO lp_cost_adj_total;
2240
2241 IF(lq_asset_lines_rec.oec < lp_cost_adj_total) THEN
2242 i:=x_qa_result_tbl.COUNT;
2243 i:=i+1;
2244 x_qa_result_tbl(i).check_code:='validate_fin_product';
2245 x_qa_result_tbl(i).check_meaning:='CAPITAL_REDUCTION_GREATER_THAN_OEC';
2246 x_qa_result_tbl(i).result_code:='ERROR';
2247 x_qa_result_tbl(i).result_meaning:='ERROR';
2248 x_qa_result_tbl(i).message_code:= 'OKL_QA_CPTR_GT_OEC';
2249 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_CPTR_GT_OEC');
2250 x_qa_result := okl_api.G_RET_STS_ERROR;
2251 EXIT;
2252 END IF;
2253 CLOSE c_lq_cost_adj_rec;
2254 END LOOP;
2255 IF (c_lq_asset_lines_rec%ISOPEN ) THEN
2256 CLOSE c_lq_asset_lines_rec;
2257 END IF;
2258 END IF;--End Of LQ
2259
2260 IF(p_object_type='QUICKQUOTE') THEN
2261 OPEN c_qq_header_rec(lp_quote_id);
2262 FETCH c_qq_header_rec INTO lp_qq_header_rec;
2263 CLOSE c_qq_header_rec;
2264 --Down Payment, Subsidy or Trade In Value cannot exceed
2265 --asset cost total (OEC)
2266 IF(lp_qq_header_rec.pricing_method <> 'SF') THEN
2267
2268 OPEN c_qq_assets_cost(lp_quote_id);
2269 FETCH c_qq_assets_cost INTO lp_asset_cost;
2270 CLOSE c_qq_assets_cost;
2271
2272
2273 OPEN c_qq_cost_adj_total_rec1(lp_quote_id);
2274 FETCH c_qq_cost_adj_total_rec1 INTO lp_cost_adj_total1;
2275 CLOSE c_qq_cost_adj_total_rec1;
2276
2277 OPEN c_qq_cost_adj_total_rec2(lp_quote_id);
2278 --Bug Fix 4731208 Start
2279 FETCH c_qq_cost_adj_total_rec2 INTO lp_cost_adj_total2;
2280 --Bug Fix 4731208 End
2281 CLOSE c_qq_cost_adj_total_rec2;
2282
2283 lp_cost_adj_total := lp_cost_adj_total1 + ((lp_cost_adj_total2 * lp_asset_cost) / 100 );
2284
2285 IF(lp_asset_cost < lp_cost_adj_total) THEN
2286 i:=x_qa_result_tbl.COUNT;
2287 i:=i+1;
2288 x_qa_result_tbl(i).check_code:='validate_fin_product';
2289 x_qa_result_tbl(i).check_meaning:='CAPITAL_REDUCTION_GREATER_THAN_OEC';
2290 x_qa_result_tbl(i).result_code:='ERROR';
2291 x_qa_result_tbl(i).result_meaning:='ERROR';
2292 x_qa_result_tbl(i).message_code:= 'OKL_QA_CPTR_GT_OEC';
2293 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_CPTR_GT_OEC');
2294 x_qa_result := okl_api.G_RET_STS_ERROR;
2295 END IF;
2296 END IF;--Pricing Method is Not SF
2297 END IF;--End OF QQ
2298 END IF;-- lp_quote_id is Not Null
2299 EXCEPTION
2300 WHEN OTHERS THEN
2301 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2302 p_msg_name => G_DB_ERROR,
2303 p_token1 => G_PROG_NAME_TOKEN,
2304 p_token1_value => 'OKLRQQCB.pls.val_fin_prod',
2305 p_token2 => G_SQLCODE_TOKEN,
2306 p_token2_value => sqlcode,
2307 p_token3 => G_SQLERRM_TOKEN,
2308 p_token3_value => sqlerrm);
2309 END validate_financial_product;
2310
2311 /*------------------------------------------------------------------------------
2312 -- PROCEDURE extended_validations
2313 ---------------------------------------------------------------------------------
2314 -- Start of comments
2315 --
2316 -- Procedure Name : extended_validations
2317 -- Description : Perform extended validation on Quote
2318 --
2319 -- Business Rules : Perform extended validation on Quote
2320 --
2321 -- Parameters : p_object_id -- Lease Quote/Quick Quote Id
2322
2323 p_object_type -- valid values are 'LEASEQUOTE'/QUICKQUOTE'
2324 hold which type of object this method is calling
2325
2326 x_qa_result_tbl --> Hold all the QA Results for Object
2327
2328 -- Version :
2329 -- History :
2330 -- End of comments
2331 ------------------------------------------------------------------------------*/
2332 PROCEDURE extended_validations(p_api_version IN NUMBER
2333 ,p_init_msg_list IN VARCHAR2
2334 ,p_object_type IN VARCHAR2
2335 ,p_object_id IN NUMBER
2336 ,x_return_status OUT NOCOPY VARCHAR2
2337 ,x_msg_count OUT NOCOPY NUMBER
2338 ,x_msg_data OUT NOCOPY VARCHAR2
2339 ,x_qa_result_tbl IN OUT NOCOPY OKL_SALES_QUOTE_QA_PVT.qa_results_tbl_type
2340 ,x_qa_result IN OUT NOCOPY VARCHAR2) IS
2341 lp_quote_id NUMBER;
2342 lp_asset_id NUMBER;
2343 lp_rec_flag VARCHAR2(1);
2344 x VARCHAR2(1);
2345 i INTEGER;
2346 l_module CONSTANT fnd_log_messages.module%TYPE := 'ext_val';
2347 l_debug_enabled varchar2(10);
2348 is_debug_procedure_on boolean;
2349 is_debug_statement_on boolean;
2350 l_program_name CONSTANT VARCHAR2(30) := 'ext_val';
2351 l_api_name CONSTANT VARCHAR2(61) := l_program_name;
2352
2353 CURSOR c_lq_asset_rec(p_quote_id NUMBER) IS
2354 SELECT * from okl_assets_b
2355 where PARENT_OBJECT_CODE='LEASEQUOTE'
2356 AND PARENT_OBJECT_ID=p_quote_id;
2357
2358 CURSOR c_qq_lines_rec(p_quote_id NUMBER) IS
2359 select t2.*
2360 from OKL_QUICK_QUOTE_LINES_b t2
2361 WHERE t2.quick_quote_id=p_quote_id
2362 AND t2.type='ITEM_CATEGORY';
2363 --Define Local Rec variables
2364 lp_qq_header_rec c_qq_header_rec%ROWTYPE;
2365 lp_lq_asset_rec c_lq_asset_rec%ROWTYPE;
2366 lp_qq_lines_rec c_qq_lines_rec%ROWTYPE;
2367
2368 --Bug 4713705 SSDESHPA--->Fix Start
2369 --Checking Valid Stream generation Template for Eot Selected
2370 --on a Quote
2371 CURSOR c_qq_valid_sgt_eot(p_eotv_id IN NUMBER,
2372 p_start_date IN DATE) IS
2373 SELECT 'x'
2374 FROM OKL_PRODUCTS PDT,
2375 OKL_AE_TMPT_SETS AES,
2376 OKL_ST_GEN_TMPT_SETS GTS,
2377 OKL_ST_GEN_TEMPLATES GTT,
2378 OKL_FE_EO_TERMS_V ETO,
2379 OKL_FE_EO_TERM_VERS ETV
2380 WHERE ETV.END_OF_TERM_VER_ID=p_eotv_id
2381 AND ETV.END_OF_TERM_ID = ETO.END_OF_TERM_ID
2382 AND PDT.ID = ETO.PRODUCT_ID
2383 AND PDT.AES_ID = AES.ID
2384 AND AES.GTS_ID = GTS.ID
2385 AND GTT.GTS_ID = GTS.ID
2386 AND GTT.START_DATE <= P_START_DATE
2387 AND NVL(GTT.END_DATE, P_START_DATE) >= P_START_DATE
2388 AND PDT.PRODUCT_STATUS_CODE = 'APPROVED'
2389 AND GTT.TMPT_STATUS = 'ACTIVE';
2390 --Check for valid Stream Generation Template for product for quote
2391 CURSOR c_lq_valid_sgt_prod(pdt_id IN NUMBER,
2392 p_start_date IN DATE) IS
2393 SELECT 'x'
2394 FROM OKL_PRODUCTS PDT,
2395 OKL_AE_TMPT_SETS AES,
2396 OKL_ST_GEN_TMPT_SETS GTS,
2397 OKL_ST_GEN_TEMPLATES GTT
2398 WHERE PDT.ID = pdt_id
2399 AND PDT.AES_ID = AES.ID
2400 AND AES.GTS_ID = GTS.ID
2401 AND GTT.GTS_ID = GTS.ID
2402 AND GTT.START_DATE <= p_start_date
2403 AND nvl(GTT.END_DATE, p_start_date) >= p_start_date
2404 AND PDT.PRODUCT_STATUS_CODE = 'APPROVED'
2405 AND GTT.TMPT_STATUS = 'ACTIVE';
2406 --Bug 4713705 SSDESHPA---->Fix End
2407 --Added Bug # 5647107 ssdeshpa start
2408 CURSOR l_systemparams_csr IS
2409 SELECT NVL(tax_upfront_yn,'N')
2410 FROM OKL_SYSTEM_PARAMS;
2411 l_ou_tax_upfront_yn VARCHAR2(1);
2412 l_err_msg VARCHAR2(80);
2413 --Added Bug # 5647107 ssdeshpa end
2414 BEGIN
2415 l_debug_enabled := okl_debug_pub.check_log_enabled;
2416 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
2417 ,fnd_log.level_procedure);
2418
2419
2420 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2421 okl_debug_pub.log_debug(fnd_log.level_procedure
2422 ,l_module
2423 ,'begin debug OKLRQQCB.pls call extended_validations');
2424 END IF; -- check for logging on STATEMENT level
2425 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
2426 ,fnd_log.level_statement);
2427
2428 -- call START_ACTIVITY to create savepoint, check compatibility
2429 -- and initialize message list
2430
2431 x_return_status := okl_api.start_activity(p_api_name=>l_api_name
2432 ,p_pkg_name=>G_PKG_NAME
2433 ,p_init_msg_list=> p_init_msg_list
2434 ,p_api_version=>p_api_version
2435 ,l_api_version=>p_api_version
2436 ,p_api_type=>G_API_TYPE
2437 ,x_return_status=>x_return_status); -- check if activity started successfully
2438
2439 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2440 RAISE okl_api.g_exception_unexpected_error;
2441 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2442 RAISE okl_api.g_exception_error;
2443 END IF;
2444
2445 lp_quote_id := p_object_id;
2446 IF(lp_quote_id IS NOT NULL) THEN
2447 IF(p_object_type='LEASEQUOTE') THEN
2448 OPEN c_lq_asset_rec(lp_quote_id);
2449 FETCH c_lq_asset_rec INTO lp_lq_asset_rec;
2450 WHILE c_lq_asset_rec%FOUND
2451 LOOP
2452 IF(nvl(lp_lq_asset_rec.end_of_term_value,0) > lp_lq_asset_rec.oec) THEN
2453 i:=x_qa_result_tbl.COUNT;
2454 i:=i+1;
2455 x_qa_result_tbl(i).check_code:='extended_validations';
2456 x_qa_result_tbl(i).check_meaning:='EOT_VALUE_GREATER_THAN_OEC';
2457 x_qa_result_tbl(i).result_code:='ERROR';
2458 x_qa_result_tbl(i).result_meaning:='ERROR';
2459 x_qa_result_tbl(i).message_code:= 'OKL_QA_EOT_GT_OEC';
2460 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_EOT_GT_OEC');
2461 x_qa_result := okl_api.G_RET_STS_ERROR;
2462 EXIT;
2463 END IF;
2464 FETCH c_lq_asset_rec INTO lp_lq_asset_rec;
2465 END LOOP;
2466 CLOSE c_lq_asset_rec;
2467
2468 --Bug 4713705 SSDESHPA--->Start
2469 --Check whether the Product Id selected on Quote is having
2470 --Active Stream Generation template's effective start dates between the
2471 --Quote Expected Start dates
2472 OPEN c_lq_header_rec(lp_quote_id);
2473 FETCH c_lq_header_rec INTO lp_lq_header_rec;
2474 CLOSE c_lq_header_rec;
2475
2476 OPEN c_lq_valid_sgt_prod(lp_lq_header_rec.product_id,lp_lq_header_rec.expected_start_date);
2477 FETCH c_lq_valid_sgt_prod INTO x;
2478 CLOSE c_lq_valid_sgt_prod;
2479 --Check for Validity of Product
2480 IF(nvl(x,'y') <>'x') THEN
2481 i:=x_qa_result_tbl.COUNT;
2482 i:=i+1;
2483 x_qa_result_tbl(i).check_code:='extended_validations';
2484 x_qa_result_tbl(i).check_meaning:='PRDT_SGT_NOT_ACTIVE';
2485 x_qa_result_tbl(i).result_code:='ERROR';
2486 x_qa_result_tbl(i).result_meaning:='ERROR';
2487 x_qa_result_tbl(i).message_code:= 'OKL_QA_SGT_PRD_INVALID';
2488 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SGT_PRD_INVALID');
2489 x_qa_result := okl_api.G_RET_STS_ERROR;
2490 END IF;
2491 --Bug 4713705 SSDESHPA--->End
2492 --Added for Legal Entity Validation
2493 OPEN l_systemparams_csr;
2494 FETCH l_systemparams_csr INTO l_ou_tax_upfront_yn;
2495 CLOSE l_systemparams_csr;
2496 IF(l_ou_tax_upfront_yn = 'Y' AND lp_lq_header_rec.legal_entity_id IS NULL) THEN
2497 IF(lp_lq_header_rec.parent_object_code = 'LEASEAPP') THEN
2498 l_err_msg := 'OKL_SO_LSE_APP_LE_ERR';
2499 ELSE
2500 l_err_msg := 'OKL_LEASE_QUOTE_LE_ERR';
2501 END IF;
2502 i:=x_qa_result_tbl.COUNT;
2503 i:=i+1;
2504 x_qa_result_tbl(i).check_code:='extended_validations';
2505 x_qa_result_tbl(i).check_meaning:= l_err_msg;
2506 x_qa_result_tbl(i).result_code:='ERROR';
2507 x_qa_result_tbl(i).result_meaning:='ERROR';
2508 x_qa_result_tbl(i).message_code:= l_err_msg;
2509 x_qa_result_tbl(i).message_text:=get_msg_text(l_err_msg);
2510 x_qa_result := okl_api.G_RET_STS_ERROR;
2511 END IF;
2512 END IF;---LQ
2513
2514 IF(p_object_type='QUICKQUOTE') THEN
2515 --Residual value cannot exceed OEC
2516 OPEN c_qq_header_rec(lp_quote_id);
2517 FETCH c_qq_header_rec INTO lp_qq_header_rec;
2518 CLOSE c_qq_header_rec;
2519 IF(lp_qq_header_rec.pricing_method <> 'SF') THEN
2520
2521 OPEN c_qq_lines_rec(lp_quote_id);
2522 FETCH c_qq_lines_rec INTO lp_qq_lines_rec;
2523 WHILE c_qq_lines_rec%FOUND
2524 LOOP
2525 IF(nvl(lp_qq_lines_rec.end_of_term_value,0) > lp_qq_lines_rec.value) THEN
2526 i:=x_qa_result_tbl.COUNT;
2527 i:=i+1;
2528 x_qa_result_tbl(i).check_code:='extended_validations';
2529 x_qa_result_tbl(i).check_meaning:='EOT_VALUE_GREATER_THAN_OEC';
2530 x_qa_result_tbl(i).result_code:='ERROR';
2531 x_qa_result_tbl(i).result_meaning:='ERROR';
2532 x_qa_result_tbl(i).message_code:= 'OKL_QA_EOT_GT_OEC';
2533 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_EOT_GT_OEC');
2534 x_qa_result := okl_api.G_RET_STS_ERROR;
2535 EXIT;
2536 END IF;
2537 FETCH c_qq_lines_rec INTO lp_qq_lines_rec;
2538 END LOOP;
2539 CLOSE c_qq_lines_rec;
2540 END IF;
2541
2542 --Bug fix 4713705 Start
2543 --Check whether the Product Id associated with EOT selected on Quote is having
2544 --Active Stream Generation template having effective start dates between the
2545 --Quote Expected Start dates
2546 OPEN c_qq_header_rec(lp_quote_id);
2547 FETCH c_qq_header_rec INTO lp_qq_header_rec;
2548 CLOSE c_qq_header_rec;
2549
2550 OPEN c_qq_valid_sgt_eot(lp_qq_header_rec.end_of_term_option_id,lp_qq_header_rec.expected_start_date);
2551 FETCH c_qq_valid_sgt_eot INTO x;
2552 CLOSE c_qq_valid_sgt_eot;
2553 --Check for Validity of EOT
2554 IF(nvl(x,'y') <>'x') THEN
2555 i:=x_qa_result_tbl.COUNT;
2556 i:=i+1;
2557 x_qa_result_tbl(i).check_code:='extended_validations';
2558 x_qa_result_tbl(i).check_meaning:='EOT_PRDT_SGT_NOT_ACTIVE';
2559 x_qa_result_tbl(i).result_code:='ERROR';
2560 x_qa_result_tbl(i).result_meaning:='ERROR';
2561 x_qa_result_tbl(i).message_code:= 'OKL_QA_SGT_EOT_INVALID';
2562 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SGT_EOT_INVALID');
2563 x_qa_result := okl_api.G_RET_STS_ERROR;
2564 END IF;
2565 --Bug fix 4713705 End
2566 END IF;--End OF QQ
2567
2568 END IF;---End OF If quote Id IS Not Null
2569
2570 okl_api.end_activity(x_msg_count => x_msg_count
2571 ,x_msg_data => x_msg_data);
2572
2573 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2574 okl_debug_pub.log_debug(fnd_log.level_procedure
2575 ,l_module
2576 ,'end debug okl_sales_quote_qa_pvt call extended_validations');
2577 END IF;
2578
2579 EXCEPTION
2580 WHEN okl_api.g_exception_error THEN
2581
2582 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
2583 ,p_pkg_name =>G_PKG_NAME
2584 ,p_exc_name =>'OKL_API.G_RET_STS_ERROR'
2585 ,x_msg_count =>x_msg_count
2586 ,x_msg_data =>x_msg_data
2587 ,p_api_type =>G_API_TYPE);
2588
2589 WHEN okl_api.g_exception_unexpected_error THEN
2590
2591 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
2592 ,p_pkg_name =>G_PKG_NAME
2593 ,p_exc_name =>'OKL_API.G_RET_STS_UNEXP_ERROR'
2594 ,x_msg_count =>x_msg_count
2595 ,x_msg_data =>x_msg_data
2596 ,p_api_type =>G_API_TYPE);
2597
2598 WHEN OTHERS THEN
2599
2600 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
2601 ,p_pkg_name =>G_PKG_NAME
2602 ,p_exc_name =>'OTHERS'
2603 ,x_msg_count =>x_msg_count
2604 ,x_msg_data =>x_msg_data
2605 ,p_api_type =>G_API_TYPE);
2606
2607 END extended_validations;
2608
2609 /*------------------------------------------------------------------------------
2610 -- PROCEDURE check_subsidies
2611 ------------------------------------------------------------------------------
2612 -- Start of comments
2613 --
2614 -- Procedure Name : check_subsidies
2615 -- Description : Check Subsidy Related Validations
2616 --
2617 -- Business Rules :
2618 --
2619 -- Parameters : p_object_id -- Lease Quote /Quick Quote Id.
2620
2621 p_object_type -- valid values are 'LEASEQUOTE'/'QUICKQUOTE'
2622 hold which type of object this method is calling
2623
2624 x_qa_result_tbl --> Hold all the QA Results for Object
2625
2626 -- Version :
2627 -- History :
2628 -- End of comments
2629 ------------------------------------------------------------------------------*/
2630 PROCEDURE check_subsidies(p_api_version IN NUMBER
2631 ,p_init_msg_list IN VARCHAR2
2632 ,p_object_type IN VARCHAR2
2633 ,p_object_id IN NUMBER
2634 ,x_return_status OUT NOCOPY VARCHAR2
2635 ,x_msg_count OUT NOCOPY NUMBER
2636 ,x_msg_data OUT NOCOPY VARCHAR2
2637 ,x_qa_result_tbl IN OUT NOCOPY OKL_SALES_QUOTE_QA_PVT.qa_results_tbl_type
2638 ,x_qa_result IN OUT NOCOPY VARCHAR2) IS
2639
2640 lp_quote_id NUMBER;
2641 lp_asset_id NUMBER;
2642 lp_subsidy_calc_basis VARCHAR2(30);
2643 lp_rec_flag VARCHAR2(1);
2644 i INTEGER;
2645 l_module CONSTANT fnd_log_messages.module%TYPE := 'chk_ss';
2646 l_debug_enabled varchar2(10);
2647 is_debug_procedure_on boolean;
2648 is_debug_statement_on boolean;
2649 l_program_name CONSTANT VARCHAR2(30) := 'chk_ss';
2650 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2651
2652 cursor c_qq_asset_lines_rec(p_quick_quote_id NUMBER) IS
2653 select t2.* from OKL_QUICK_QUOTES_B t1,OKL_QUICK_QUOTE_LINES_B t2
2654 WHERE t1.PRICING_METHOD='SF'
2655 AND t2.type='SUBSIDY'
2656 AND t1.id=p_quick_quote_id
2657 AND t2.quick_quote_id=t1.id;
2658
2659 lp_qq_asset_lines_rec c_qq_asset_lines_rec%ROWTYPE;
2660
2661 cursor c_lq_asset_lines_rec(p_quote_id NUMBER) IS
2662 SELECT t2.*
2663 FROM OKL_LEASE_QUOTES_B t1,OKL_ASSETS_B t2
2664 where t1.id=p_quote_id
2665 AND t1.pricing_method='SF'
2666 and t2.parent_object_code='LEASEQUOTE'
2667 and t2.parent_object_id=t1.id;
2668
2669 lp_lq_asset_lines_rec c_lq_asset_lines_rec%ROWTYPE;
2670
2671 cursor c_lq_cost_adj_rec(p_asset_id NUMBER) IS
2672 SELECT BASIS FROM OKL_COST_ADJUSTMENTS_B
2673 where PARENT_OBJECT_CODE='ASSET'
2674 and ADJUSTMENT_SOURCE_TYPE='SUBSIDY'
2675 AND PARENT_OBJECT_ID=p_asset_id;
2676
2677 lp_lq_cost_adj_rec c_lq_cost_adj_rec%ROWTYPE;
2678
2679 BEGIN
2680 l_debug_enabled := okl_debug_pub.check_log_enabled;
2681 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
2682 ,fnd_log.level_procedure);
2683
2684
2685 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2686 okl_debug_pub.log_debug(fnd_log.level_procedure
2687 ,l_module
2688 ,'begin debug OKLRQQCB.pls call check_subsidies');
2689 END IF; -- check for logging on STATEMENT level
2690 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
2691 ,fnd_log.level_statement);
2692
2693 -- call START_ACTIVITY to create savepoint, check compatibility
2694 -- and initialize message list
2695 x_return_status := okl_api.start_activity(p_api_name=>l_api_name
2696 ,p_pkg_name=>G_PKG_NAME
2697 ,p_init_msg_list=>p_init_msg_list
2698 ,p_api_version=>p_api_version
2699 ,l_api_version=>p_api_version
2700 ,p_api_type=>G_API_TYPE
2701 ,x_return_status=>x_return_status); -- check if activity started successfully
2702
2703 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2704 RAISE okl_api.g_exception_unexpected_error;
2705 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2706 RAISE okl_api.g_exception_error;
2707 END IF;
2708 lp_quote_id := p_object_id;
2709 IF(lp_quote_id IS NOT NULL) THEN
2710 IF(p_object_type='LEASEQUOTE') THEN
2711 --Check that Subsidy types must be FIXED or RATE for SFFA Pricing Methods
2712 OPEN c_lq_asset_lines_rec(lp_quote_id);
2713 FETCH c_lq_asset_lines_rec INTO lp_lq_asset_lines_rec;
2714 WHILE c_lq_asset_lines_rec%FOUND
2715 LOOP
2716 OPEN c_lq_cost_adj_rec(lp_lq_asset_lines_rec.id);
2717 FETCH c_lq_cost_adj_rec INTO lp_lq_cost_adj_rec;
2718 IF((c_lq_cost_adj_rec%FOUND) AND(lp_lq_cost_adj_rec.basis IS NOT NULL) AND (lp_lq_cost_adj_rec.basis <> 'FIXED' AND lp_lq_cost_adj_rec.basis <> 'RATE') ) THEN
2719 i:=x_qa_result_tbl.COUNT;
2720 i:=i+1;
2721 x_qa_result_tbl(i).check_code:='check_subsidies';
2722 x_qa_result_tbl(i).check_meaning:='SUBSIDY BASIS NOT VALID';
2723 x_qa_result_tbl(i).result_code:='ERROR';
2724 x_qa_result_tbl(i).result_meaning:='ERROR';
2725 x_qa_result_tbl(i).message_code:= 'OKL_QA_SUB_BASIS_NOT_VALID_SF';
2726 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SUB_BASIS_NOT_VALID_SF');
2727 x_qa_result := okl_api.G_RET_STS_ERROR;
2728 EXIT;
2729 END IF;
2730 CLOSE c_lq_cost_adj_rec;
2731 FETCH c_lq_asset_lines_rec INTO lp_lq_asset_lines_rec;
2732 END LOOP;
2733 CLOSE c_lq_asset_lines_rec;
2734 END IF;--End OF LQ
2735
2736 IF(p_object_type='QUICKQUOTE') THEN
2737 --Check that Subsidy types must be FIXED or RATE for SFFA Pricing Methods
2738 OPEN c_qq_asset_lines_rec(lp_quote_id);
2739 FETCH c_qq_asset_lines_rec INTO lp_qq_asset_lines_rec;
2740 WHILE c_qq_asset_lines_rec%FOUND
2741 LOOP
2742 IF((lp_qq_asset_lines_rec.basis IS NOT NULL) AND (lp_qq_asset_lines_rec.basis <> 'FIXED' AND lp_qq_asset_lines_rec.basis <> 'RATE')) THEN
2743 i:=x_qa_result_tbl.COUNT;
2744 i:=i+1;
2745 x_qa_result_tbl(i).check_code:='check_subsidies';
2746 x_qa_result_tbl(i).check_meaning:='SUBSIDY BASIS NOT VALID';
2747 x_qa_result_tbl(i).result_code:='ERROR';
2748 x_qa_result_tbl(i).result_meaning:='ERROR';
2749 x_qa_result_tbl(i).message_code:= 'OKL_QA_SUB_BASIS_NOT_VALID_SF';
2750 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SUB_BASIS_NOT_VALID_SF');
2751 x_qa_result := okl_api.G_RET_STS_ERROR;
2752 EXIT;
2753 END IF;
2754 FETCH c_qq_asset_lines_rec INTO lp_qq_asset_lines_rec;
2755 END LOOP;
2756 CLOSE c_qq_asset_lines_rec;
2757 END IF;--End OF QQ
2758 END IF;---End OF If quote Id IS Not Null
2759
2760 okl_api.end_activity(x_msg_count => x_msg_count
2761 ,x_msg_data => x_msg_data);
2762
2763 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2764 okl_debug_pub.log_debug(fnd_log.level_procedure
2765 ,l_module
2766 ,'end debug okl_sales_quote_qa_pvt call check_subsidies');
2767 END IF;
2768 EXCEPTION
2769 WHEN okl_api.g_exception_error THEN
2770 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
2771 ,p_pkg_name =>G_PKG_NAME
2772 ,p_exc_name =>'OKL_API.G_RET_STS_ERROR'
2773 ,x_msg_count =>x_msg_count
2774 ,x_msg_data =>x_msg_data
2775 ,p_api_type =>G_API_TYPE);
2776
2777 WHEN okl_api.g_exception_unexpected_error THEN
2778 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
2779 ,p_pkg_name =>G_PKG_NAME
2780 ,p_exc_name =>'OKL_API.G_RET_STS_UNEXP_ERROR'
2781 ,x_msg_count =>x_msg_count
2782 ,x_msg_data =>x_msg_data
2783 ,p_api_type =>G_API_TYPE);
2784
2785 WHEN OTHERS THEN
2786 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
2787 ,p_pkg_name =>G_PKG_NAME
2788 ,p_exc_name =>'OTHERS'
2789 ,x_msg_count =>x_msg_count
2790 ,x_msg_data =>x_msg_data
2791 ,p_api_type =>G_API_TYPE);
2792 END check_subsidies;
2793
2794 /*------------------------------------------------------------------------------
2795 -- PROCEDURE check_configuration
2796 ---------------------------------------------------------------------------------
2797 -- Start of comments
2798 --
2799 -- Procedure Name : check_configuration
2800 -- Description : Check Attributes for Configuration Objects(Assets/Config
2801 Fees /capitalized Fees/Rollover Fees)
2802 --
2803 -- Business Rules : Various checks related to Configuration Tab Objects.
2804 Assets/Financed Fee/Rollover Fees/Capitalized fees.
2805 --
2806 -- Parameters : p_object_id -- Lease Quote /Quick Quote Id.
2807
2808 p_object_type -- valid values are 'LEASEQUOTE'/'QUICKQUOTE'
2809 hold which type of object this method is calling
2810
2811 x_qa_result_tbl --> Hold all the QA Results for Object
2812
2813 -- Version : 1.0
2814 -- History :
2815 -- End of comments
2816 ------------------------------------------------------------------------------*/
2817 PROCEDURE check_configuration(p_api_version IN NUMBER
2818 ,p_init_msg_list IN VARCHAR2
2819 ,p_object_type IN VARCHAR2
2820 ,p_object_id IN NUMBER
2821 ,x_return_status OUT NOCOPY VARCHAR2
2822 ,x_msg_count OUT NOCOPY NUMBER
2823 ,x_msg_data OUT NOCOPY VARCHAR2
2824 ,x_qa_result_tbl IN OUT NOCOPY OKL_SALES_QUOTE_QA_PVT.qa_results_tbl_type
2825 ,x_qa_result IN OUT NOCOPY VARCHAR2) IS
2826
2827 -- bug 5048183 ssdeshpa Start
2828 -- Get all the Info abt the Assets for Quote
2829 CURSOR c_lq_asset_rec(p_quote_id NUMBER) IS
2830 SELECT ID,END_OF_TERM_VALUE_DEFAULT,END_OF_TERM_VALUE
2831 FROM OKL_ASSETS_B OLA
2832 WHERE OLA.PARENT_OBJECT_ID=p_quote_id
2833 AND OLA.PARENT_OBJECT_CODE='LEASEQUOTE';
2834 -- bug 5048183 ssdeshpa End
2835 --Fetch Quick Quote Item categories
2836 CURSOR c_qq_asset_rec(p_quote_id NUMBER) IS
2837 SELECT 'x'
2838 FROM OKL_QUICK_QUOTE_LINES_B OAL
2839 WHERE OAL.QUICK_QUOTE_ID=p_quote_id
2840 AND OAL.TYPE='ITEM_CATEGORY';
2841
2842 lp_quote_id NUMBER;
2843 lp_asset_id NUMBER;
2844 lp_rec_flag VARCHAR2(1);
2845 l_asset_count INTEGER := 0;
2846 i INTEGER;
2847 l_module CONSTANT fnd_log_messages.module%TYPE := 'chk_cnfg';
2848 l_debug_enabled varchar2(10);
2849 is_debug_procedure_on boolean;
2850 is_debug_statement_on boolean;
2851 l_program_name CONSTANT VARCHAR2(30) := 'chk_cnfg';
2852 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2853
2854 BEGIN
2855 l_debug_enabled := okl_debug_pub.check_log_enabled;
2856 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
2857 ,fnd_log.level_procedure);
2858
2859
2860 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2861 okl_debug_pub.log_debug(fnd_log.level_procedure
2862 ,l_module
2863 ,'begin debug OKLRQQCB.pls call check_configuration');
2864 END IF; -- check for logging on STATEMENT level
2865 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
2866 ,fnd_log.level_statement);
2867
2868 -- call START_ACTIVITY to create savepoint, check compatibility
2869 -- and initialize message list
2870
2871 x_return_status := okl_api.start_activity(p_api_name=>l_api_name
2872 ,p_pkg_name=>G_PKG_NAME
2873 ,p_init_msg_list=>p_init_msg_list
2874 ,p_api_version=>p_api_version
2875 ,l_api_version=>p_api_version
2876 ,p_api_type=>G_API_TYPE
2877 ,x_return_status=>x_return_status); -- check if activity started successfully
2878
2879 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2880 RAISE okl_api.g_exception_unexpected_error;
2881 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2882 RAISE okl_api.g_exception_error;
2883 END IF;
2884
2885 lp_quote_id := p_object_id;
2886 IF(lp_quote_id IS NOT NULL) THEN
2887 IF(p_object_type='LEASEQUOTE') THEN
2888 -- bug 5048183 ssdeshpa Start
2889 -- Check for EOT Value/EOT Override Value for Asset
2890 --Both can not be null for a Quote
2891 FOR lp_lq_asset_rec IN c_lq_asset_rec(p_quote_id => lp_quote_id) LOOP
2892 l_asset_count := l_asset_count + 1;
2893 IF(lp_lq_asset_rec.END_OF_TERM_VALUE_DEFAULT IS NULL AND
2894 lp_lq_asset_rec.END_OF_TERM_VALUE IS NULL) THEN
2895 i:=x_qa_result_tbl.COUNT;
2896 i:=i+1;
2897 x_qa_result_tbl(i).check_code:='check_configuration';
2898 x_qa_result_tbl(i).check_meaning:='NO_ASSETS_FOUND';
2899 x_qa_result_tbl(i).result_code:='ERROR';
2900 x_qa_result_tbl(i).result_meaning:='ERROR';
2901 x_qa_result_tbl(i).message_code:= 'OKL_QA_NO_EOT_FOR_ASSET';
2902 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_NO_EOT_FOR_ASSET');
2903 x_qa_result := okl_api.G_RET_STS_ERROR;
2904 EXIT;
2905 END IF;
2906 END LOOP;
2907 --For a Quote Atlease One Asset Should be there
2908 --At least one asset line must exist.
2909 IF(l_asset_count = 0) THEN
2910 i:=x_qa_result_tbl.COUNT;
2911 i:=i+1;
2912 x_qa_result_tbl(i).check_code:='check_configuration';
2913 x_qa_result_tbl(i).check_meaning:='NO_ASSETS_FOUND';
2914 x_qa_result_tbl(i).result_code:='ERROR';
2915 x_qa_result_tbl(i).result_meaning:='ERROR';
2916 x_qa_result_tbl(i).message_code:= 'OKL_QA_NO_ASSETS_FOUND';
2917 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_NO_ASSETS_FOUND');
2918 x_qa_result := okl_api.G_RET_STS_ERROR;
2919 END IF;
2920 END IF;--for LQ
2921 -- bug 5048183 ssdeshpa End
2922 IF(p_object_type='QUICKQUOTE') THEN
2923 OPEN c_qq_asset_rec(p_quote_id => lp_quote_id);
2924 FETCH c_qq_asset_rec INTO lp_rec_flag;
2925 CLOSE c_qq_asset_rec;
2926 --For a Quote Atlease One Asset Should be there
2927 --At least one asset line must exist.
2928 IF(lp_rec_flag IS NULL) THEN
2929 i:=x_qa_result_tbl.COUNT;
2930 i:=i+1;
2931 x_qa_result_tbl(i).check_code:='check_configuration';
2932 x_qa_result_tbl(i).check_meaning:='NO_ASSETS_FOUND';
2933 x_qa_result_tbl(i).result_code:='ERROR';
2934 x_qa_result_tbl(i).result_meaning:='ERROR';
2935 x_qa_result_tbl(i).message_code:= 'OKL_QA_NO_ASSETS_FOUND';
2936 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_NO_ASSETS_FOUND');
2937 x_qa_result := okl_api.G_RET_STS_ERROR;
2938 END IF;
2939 END IF;--End of QQ
2940 ELSE
2941 i:=x_qa_result_tbl.COUNT;
2942 i:=i+1;
2943 x_qa_result_tbl(i).check_code:='check_configuration';
2944 x_qa_result_tbl(i).check_meaning:='NO_QUOTE_FOUND';
2945 x_qa_result_tbl(i).result_code:='ERROR';
2946 x_qa_result_tbl(i).result_meaning:='ERROR';
2947 x_qa_result_tbl(i).message_code:= 'OKL_QA_NO_QUOTE_FOUND';
2948 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_NO_QUOTE_FOUND');
2949 x_qa_result := okl_api.G_RET_STS_ERROR;
2950 END IF;
2951 okl_api.end_activity(x_msg_count => x_msg_count
2952 ,x_msg_data => x_msg_data);
2953
2954 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2955 okl_debug_pub.log_debug(fnd_log.level_procedure
2956 ,l_module
2957 ,'end debug okl_sales_quote_qa_pvt call check_configuration ');
2958 END IF;
2959
2960 EXCEPTION
2961 WHEN okl_api.g_exception_error THEN
2962 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2963 ,p_pkg_name => G_PKG_NAME
2964 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
2965 ,x_msg_count => x_msg_count
2966 ,x_msg_data => x_msg_data
2967 ,p_api_type => G_API_TYPE);
2968
2969 WHEN okl_api.g_exception_unexpected_error THEN
2970
2971 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2972 ,p_pkg_name => G_PKG_NAME
2973 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
2974 ,x_msg_count => x_msg_count
2975 ,x_msg_data => x_msg_data
2976 ,p_api_type => G_API_TYPE);
2977
2978 WHEN OTHERS THEN
2979
2980 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
2981 ,p_pkg_name => G_PKG_NAME
2982 ,p_exc_name =>'OTHERS'
2983 ,x_msg_count => x_msg_count
2984 ,x_msg_data => x_msg_data
2985 ,p_api_type => G_API_TYPE);
2986
2987 END check_configuration;
2988 /*------------------------------------------------------------------------------
2989 -- PROCEDURE check_fees_and_services
2990 ------------------------------------------------------------------------------
2991 -- Start of comments
2992 --
2993 -- Procedure Name : check_fees_and_services
2994 -- Description : Check Fees and Services Associated with Quote
2995 --
2996 -- Business Rules : Fees and Services Objects associated with Quote will
2997 be checked for Validity.
2998 --
2999 -- Parameters : p_object_id -- Lease Quote /Quick Quote Id.
3000
3001 p_object_type -- valid values are 'LEASEQUOTE'/'QUICKQUOTE'
3002 hold which type of object this method is calling
3003
3004 x_qa_result_tbl --> Hold all the QA Results for Object
3005
3006 -- Version : 1.0
3007 -- History :
3008 -- End of comments
3009 ------------------------------------------------------------------------------*/
3010 PROCEDURE check_fees_and_services(p_api_version IN NUMBER
3011 ,p_init_msg_list IN VARCHAR2
3012 ,p_object_type IN VARCHAR2
3013 ,p_object_id IN NUMBER
3014 ,x_return_status OUT NOCOPY VARCHAR2
3015 ,x_msg_count OUT NOCOPY NUMBER
3016 ,x_msg_data OUT NOCOPY VARCHAR2
3017 ,x_qa_result_tbl IN OUT NOCOPY OKL_SALES_QUOTE_QA_PVT.qa_results_tbl_type
3018 ,x_qa_result IN OUT NOCOPY VARCHAR2) IS
3019 lp_quote_id NUMBER;
3020 lp_asset_id NUMBER;
3021 lp_service_line_id NUMBER;
3022 lp_capital_fee_type CONSTANT varchar2(30):='CAPITALIZED';
3023 lp_sec_dep_fee_type CONSTANT varchar2(30):='SEC_DEPOSIT';
3024 lp_rec_flag VARCHAR2(1);
3025 i INTEGER;
3026 total_line_amount INTEGER:=0;
3027 lp_fee_amount INTEGER:=0;
3028 lp_service_line_total INTEGER:=0;
3029
3030 l_module CONSTANT fnd_log_messages.module%TYPE := 'chk_fees_and_services';
3031 l_debug_enabled varchar2(10);
3032 is_debug_procedure_on boolean;
3033 is_debug_statement_on boolean;
3034 l_program_name CONSTANT VARCHAR2(30) := 'chck_fee';
3035 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3036 --Fetch all the Configuration Fees For Quote
3037 cursor c_config_fee_rec(p_quote_id NUMBER) IS
3038 select ofv.*
3039 from okl_fees_v ofv
3040 where ofv.parent_object_code='LEASEQUOTE'
3041 AND ofv.parent_object_id=p_quote_id
3042 AND ofv.fee_type IN ('FINANCED','ROLLOVER');
3043
3044 --Tune The Query and Remove Extra Columns
3045 --cursor can fetch multiple Records
3046 cursor c_lq_asset_line(p_parent_object_id NUMBER,p_fee_id NUMBER) IS
3047 select olr.*
3048 from okl_line_relationships_b olr,
3049 okl_fees_b ofv
3050 where ofv.FEE_TYPE='CAPITALIZED'
3051 AND olr.related_line_id = p_fee_id
3052 AND olr.related_line_id = ofv.id -- Added by rravikir for Bug 4736523
3053 AND olr.related_line_type = 'CAPITALIZED';
3054
3055 lp_lq_fee_rec c_lq_fee_rec%ROWTYPE;
3056 lp_lq_asset_line c_lq_asset_line%ROWTYPE;
3057 BEGIN
3058 l_debug_enabled := okl_debug_pub.check_log_enabled;
3059 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
3060 ,fnd_log.level_procedure);
3061
3062
3063 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
3064 okl_debug_pub.log_debug(fnd_log.level_procedure
3065 ,l_module
3066 ,'begin debug OKLRQQCB.pls.check_fees_and_services call ');
3067 END IF; -- check for logging on STATEMENT level
3068 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
3069 ,fnd_log.level_statement);
3070
3071 -- call START_ACTIVITY to create savepoint, check compatibility
3072 -- and initialize message list
3073
3074 x_return_status := okl_api.start_activity(p_api_name=>l_api_name
3075 ,p_pkg_name=>G_PKG_NAME
3076 ,p_init_msg_list=>p_init_msg_list
3077 ,p_api_version=>p_api_version
3078 ,l_api_version=>p_api_version
3079 ,p_api_type=>G_API_TYPE
3080 ,x_return_status=>x_return_status); -- check if activity started successfully
3081
3082 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
3083 RAISE okl_api.g_exception_unexpected_error;
3084 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
3085 RAISE okl_api.g_exception_error;
3086 END IF;
3087
3088 lp_quote_id := p_object_id;
3089 IF(lp_quote_id IS NOT NULL) THEN
3090 IF(p_object_type='LEASEQUOTE' OR p_object_type='LEASEAPP' OR p_object_type='LEASEOPP') THEN
3091 --When defining capitalized fees, the total amount applied to
3092 --each associated asset must be equal to the fee line total
3093 FOR lp_lq_fee_rec IN c_lq_fee_rec(lp_quote_id,lp_capital_fee_type) LOOP
3094 ---calculate the Total Fee Amount
3095 lp_fee_amount:=lp_lq_fee_rec.fee_amount;
3096
3097 --Bug # 6697231 :Initialized total amount
3098 total_line_amount:=0;
3099 --Bug # 6697231:End
3100 ----------
3101 OPEN c_lq_asset_line(lp_quote_id ,lp_lq_fee_rec.id);
3102 FETCH c_lq_asset_line INTO lp_lq_asset_line;
3103 IF(c_lq_asset_line%NOTFOUND) THEN
3104 i:=x_qa_result_tbl.COUNT;
3105 i:=i+1;
3106 x_qa_result_tbl(i).check_code:='check_fees_and_services';
3107 x_qa_result_tbl(i).check_meaning:='NO_ASSETS_FOUND_FOR_FEE';
3108 x_qa_result_tbl(i).result_code:='ERROR';
3109 x_qa_result_tbl(i).result_meaning:='ERROR';
3110 x_qa_result_tbl(i).message_code:= 'OKL_QA_NO_ASSETS_FOR_FEE';
3111 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_NO_ASSETS_FOR_FEE');
3112 x_qa_result := okl_api.G_RET_STS_ERROR;
3113 ELSE
3114 /* Keep fetching until no more records are FOUND */
3115 WHILE c_lq_asset_line%FOUND LOOP
3116 total_line_amount:=total_line_amount + nvl(lp_lq_asset_line.amount,0);
3117 FETCH c_lq_asset_line INTO lp_lq_asset_line;
3118 END LOOP;
3119 END IF;
3120 IF c_lq_asset_line%ISOPEN THEN
3121 CLOSE c_lq_asset_line;
3122 END IF;
3123 IF(lp_fee_amount <> total_line_amount) THEN
3124 i:=x_qa_result_tbl.COUNT;
3125 i:=i+1;
3126 x_qa_result_tbl(i).check_code:='check_fees_and_services';
3127 x_qa_result_tbl(i).check_meaning:='FEE_LINE_TOTAL_NOT_MATCH ';
3128 x_qa_result_tbl(i).result_code:='ERROR';
3129 x_qa_result_tbl(i).result_meaning:='ERROR';
3130 x_qa_result_tbl(i).message_code:= 'OKL_QA_FEE_TOTAL_NOT_MATCH';
3131 x_qa_result_tbl(i).message_text:= get_msg_text('OKL_QA_FEE_TOTAL_NOT_MATCH');
3132 x_qa_result := okl_api.G_RET_STS_ERROR;
3133 END IF;
3134 END LOOP;
3135 --Check Whether the Quote Have Only One SECURITY_DEPOSIT Fee Defined on it
3136 OPEN c_lq_fee_rec(lp_quote_id,lp_sec_dep_fee_type);
3137 i:=0;
3138 FETCH c_lq_fee_rec INTO lp_lq_fee_rec;
3139 WHILE c_lq_fee_rec%FOUND LOOP
3140 i:=i+1;
3141 FETCH c_lq_fee_rec INTO lp_lq_fee_rec;
3142 END LOOP;
3143 CLOSE c_lq_fee_rec;
3144 IF(i > 1) THEN
3145 i:=x_qa_result_tbl.COUNT;
3146 i:=i+1;
3147 x_qa_result_tbl(i).check_code:='check_fees_and_services';
3148 x_qa_result_tbl(i).check_meaning:='ONLY_ONE_SEC_DEP_FOR_QUOTE ';
3149 x_qa_result_tbl(i).result_code:='ERROR';
3150 x_qa_result_tbl(i).result_meaning:='ERROR';
3151 x_qa_result_tbl(i).message_code:= 'OKL_QA_SINGLE_SEC_DEP_REQ';
3152 x_qa_result_tbl(i).message_text:= get_msg_text('OKL_QA_SINGLE_SEC_DEP_REQ');
3153 x_qa_result := okl_api.G_RET_STS_ERROR;
3154 END IF;
3155
3156 END IF;--End For LQ
3157 IF(p_object_type='QUICKQUOTE') THEN
3158 return;
3159 END IF;--End OF QQ
3160
3161 END IF;---End OF If quote Id IS NOt Null
3162 okl_api.end_activity(x_msg_count => x_msg_count
3163 ,x_msg_data => x_msg_data);
3164
3165 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
3166 okl_debug_pub.log_debug(fnd_log.level_procedure
3167 ,l_module
3168 ,'end debug okl_sales_quote_qa_pvt.check_fees_and_services call');
3169 END IF;
3170
3171 EXCEPTION
3172 WHEN okl_api.g_exception_error THEN
3173 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
3174 ,p_pkg_name =>G_PKG_NAME
3175 ,p_exc_name =>'OKL_API.G_RET_STS_ERROR'
3176 ,x_msg_count =>x_msg_count
3177 ,x_msg_data =>x_msg_data
3178 ,p_api_type =>G_API_TYPE);
3179
3180 WHEN okl_api.g_exception_unexpected_error THEN
3181 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
3182 ,p_pkg_name =>G_PKG_NAME
3183 ,p_exc_name =>'OKL_API.G_RET_STS_UNEXP_ERROR'
3184 ,x_msg_count =>x_msg_count
3185 ,x_msg_data =>x_msg_data
3186 ,p_api_type =>G_API_TYPE);
3187
3188 WHEN OTHERS THEN
3189 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
3190 ,p_pkg_name =>G_PKG_NAME
3191 ,p_exc_name =>'OTHERS'
3192 ,x_msg_count =>x_msg_count
3193 ,x_msg_data =>x_msg_data
3194 ,p_api_type =>G_API_TYPE);
3195 END check_fees_and_services;
3196 /*------------------------------------------------------------------------------
3197 -- PROCEDURE check_payments
3198 ------------------------------------------------------------------------------
3199 -- Start of comments
3200 --
3201 -- Procedure Name : check_payments
3202 -- Description : Check the Payment/Pricing data For Quote
3203 --
3204 -- Business Rules : Check the Payment/Pricing data For Quote so that Pricing
3205 Engine go smooth without errors
3206 --
3207 -- Parameters : p_object_id -- Lease Quote /Quick Quote Id.
3208
3209 p_object_type -- valid values are 'LEASEQUOTE'/'QUICKQUOTE'
3210 hold which type of object this method is calling
3211
3212 x_qa_result_tbl --> Hold all the QA Results for Object
3213
3214 -- Version :
3215 -- History :
3216 -- End of comments
3217 ------------------------------------------------------------------------------*/
3218 PROCEDURE check_payments(p_api_version IN NUMBER
3219 ,p_init_msg_list IN VARCHAR2
3220 ,p_object_type IN VARCHAR2
3221 ,p_object_id IN NUMBER
3222 ,x_return_status OUT NOCOPY VARCHAR2
3223 ,x_msg_count OUT NOCOPY NUMBER
3224 ,x_msg_data OUT NOCOPY VARCHAR2
3225 ,x_qa_result_tbl IN OUT NOCOPY OKL_SALES_QUOTE_QA_PVT.qa_results_tbl_type
3226 ,x_qa_result IN OUT NOCOPY VARCHAR2) IS
3227 lp_quote_id NUMBER;
3228 lp_service_line_id NUMBER;
3229 lp_vp_id NUMBER;
3230 lp_sec_dep_fee_type CONSTANT varchar2(30):='SEC_DEPOSIT';
3231 lp_rec_flag VARCHAR2(1);
3232 lp_cont_start_date DATE;
3233 lp_payment_start_date DATE;
3234 i INTEGER;
3235 x varchar2(1);
3236 l_cnt NUMBER;
3237 l_found boolean := false;
3238 l_amt_null_count INTEGER := 0;
3239 l_total_caf INTEGER := 0;
3240 l_no_qte_payment boolean := false;
3241
3242 l_missing_pmts INTEGER := 0;
3243 l_cfl_count INTEGER := 0;
3244 l_no_missing_rate boolean := FALSE;
3245 l_total_missing_pmts INTEGER := 0;
3246 l_total_cfl_count INTEGER := 0;
3247 l_no_missing_rate_count INTEGER := 0;
3248
3249 l_are_all_lines_overriden VARCHAR2(1);
3250 l_qte_pric_opts_entered VARCHAR2(1);
3251 l_cashflow_count INTEGER := 0;
3252 l_fees_count INTEGER := 0;
3253 l_module CONSTANT fnd_log_messages.module%TYPE := 'chek_pay';
3254 l_debug_enabled varchar2(10);
3255 is_debug_procedure_on boolean;
3256 is_debug_statement_on boolean;
3257 l_program_name CONSTANT VARCHAR2(30) := 'chek_pay';
3258 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3259 --Get data for Configuration Fees(Financed and Rollover)
3260 cursor c_configuration_fee_rec(p_quote_id NUMBER) IS
3261 select ofv.id,
3262 ofv.rate_card_id,
3263 ofv.rate_template_id,
3264 ofv.structured_pricing,
3265 ofv.lease_rate_factor,
3266 'QUOTED_FEE' oty_code,
3267 ofv.target_arrears,
3268 ofv.target_amount,
3269 STYT.NAME NAME
3270 from okl_fees_b ofv,
3271 OKL_STRM_TYPE_B STY,
3272 OKL_STRM_TYPE_TL STYT
3273 where ofv.parent_object_code='LEASEQUOTE'
3274 AND ofv.parent_object_id=p_quote_id
3275 AND ofv.fee_type IN ('FINANCED','ROLLOVER')
3276 AND ofv.STREAM_TYPE_ID = STY.ID
3277 AND STY.ID = STYT.ID
3278 AND STYT.LANGUAGE = USERENV('LANG');
3279
3280 --Get data for Assets on Quote
3281 cursor c_configuration_asset_rec(p_quote_id NUMBER) IS
3282 select oab.id,
3283 oab.rate_card_id,
3284 oab.rate_template_id,
3285 oab.structured_pricing,
3286 oab.lease_rate_factor,
3287 'QUOTED_ASSET' oty_code,
3288 oab.target_arrears,
3289 oab.target_amount
3290 from okl_assets_b oab
3291 where oab.parent_object_code='LEASEQUOTE'
3292 AND oab.parent_object_id=p_quote_id;
3293
3294 -----------------------------
3295 cursor c_config_fee_rec(p_quote_id NUMBER) IS
3296 select ofv.id,
3297 ofv.rate_card_id,
3298 ofv.rate_template_id,
3299 ofv.structured_pricing,
3300 ofv.lease_rate_factor,
3301 'QUOTED_FEE' oty_code
3302 from okl_fees_b ofv
3303 where ofv.parent_object_code='LEASEQUOTE'
3304 AND ofv.parent_object_id=p_quote_id
3305 AND ofv.fee_type IN ('FINANCED','ROLLOVER')
3306 UNION
3307 select oab.id,
3308 oab.rate_card_id,
3309 oab.rate_template_id,
3310 oab.structured_pricing,
3311 oab.lease_rate_factor,
3312 'QUOTED_ASSET' oty_code
3313 from okl_assets_b oab
3314 where oab.parent_object_code='LEASEQUOTE'
3315 AND oab.parent_object_id=p_quote_id;
3316
3317 CURSOR c_payment_level_rec(p_quote_id NUMBER) IS
3318 SELECT TRUNC(CFL.start_date)
3319 FROM OKL_CASH_FLOWS CAF,OKL_CASH_FLOW_LEVELS CFL
3320 WHERE CAF.ID=CFL.CAF_ID
3321 AND CAF.dnz_qte_id=p_quote_id;
3322 ---Check the Date Condition
3323 CURSOR c_vp_rec(p_vp_id NUMBER,p_contract_start_date DATE) IS
3324 select 'x'
3325 from okc_k_headers_v tbl
3326 where tbl.id=p_vp_id
3327 AND p_contract_start_date between tbl.start_date and NVL(tbl.end_date,p_contract_start_date)
3328 AND tbl.SCS_CODE='PROGRAM'
3329 AND tbl.STS_CODE='ACTIVE'
3330 AND tbl.template_yn='N';
3331
3332 CURSOR is_rc_valid_csr(p_lrs_version_id IN NUMBER,p_exp_start_date IN DATE) IS
3333 SELECT 'Y'
3334 FROM okl_fe_rate_set_versions
3335 WHERE rate_set_version_id = p_lrs_version_id
3336 AND effective_from_date <= p_exp_start_date
3337 AND nvl(effective_to_date,p_exp_start_date) >= p_exp_start_date;
3338
3339 CURSOR is_srt_valid_csr(p_srt_version_id IN NUMBER,p_exp_start_date IN DATE) IS
3340 SELECT 'Y'
3341 FROM okl_fe_std_rt_tmp_vers
3342 WHERE std_rate_tmpl_ver_id = p_srt_version_id
3343 AND effective_from_date <= p_exp_start_date
3344 AND nvl(effective_to_date,p_exp_start_date) >= p_exp_start_date;
3345
3346 --Get the Cash Flow Count for Quote/Asset/Config Fees
3347 CURSOR c_cash_flow_level_count(p_source_id NUMBER,p_oty_code VARCHAR2) IS
3348 SELECT count(*) cfl_count
3349 FROM OKL_CASH_FLOW_OBJECTS CFO,OKL_CASH_FLOWS CAF,
3350 OKL_CASH_FLOW_LEVELS CFL
3351 WHERE CFO.ID=CAF.CFO_ID
3352 AND CAF.ID=CFL.CAF_ID
3353 AND CFO.oty_code=p_oty_code
3354 AND CFO.source_id=p_source_id;
3355
3356 l_valid VARCHAR2(3):= 'N';
3357 lp_lq_service_fee c_lq_fee_rec%ROWTYPE;
3358 lp_lq_cfl_line c_lq_cfl_line%ROWTYPE;
3359 lp_payment_level_rec c_payment_level_rec%ROWTYPE;
3360 BEGIN
3361 l_debug_enabled := okl_debug_pub.check_log_enabled;
3362 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
3363 ,fnd_log.level_procedure);
3364
3365
3366 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
3367 okl_debug_pub.log_debug(fnd_log.level_procedure
3368 ,l_module
3369 ,'begin debug OKLRQQCB.pls call check_payments');
3370 END IF; -- check for logging on STATEMENT level
3371 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
3372 ,fnd_log.level_statement);
3373
3374 -- call START_ACTIVITY to create savepoint, check compatibility
3375 -- and initialize message list
3376
3377 x_return_status := okl_api.start_activity(p_api_name => l_api_name
3378 ,p_pkg_name => G_PKG_NAME
3379 ,p_init_msg_list => p_init_msg_list
3380 ,p_api_version => p_api_version
3381 ,l_api_version => p_api_version
3382 ,p_api_type => G_API_TYPE
3383 ,x_return_status => x_return_status); -- check if activity started successfully
3384
3385 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
3386 RAISE okl_api.g_exception_unexpected_error;
3387 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
3388 RAISE okl_api.g_exception_error;
3389 END IF;
3390
3391 lp_quote_id:=p_object_id;
3392 IF(lp_quote_id IS NOT NULL) THEN
3393 IF(p_object_type='LEASEQUOTE' OR p_object_type='LEASEAPP' OR p_object_type='LEASEOPP') THEN
3394 lp_vp_id := OKL_EC_UPTAKE_PVT.get_vp_id(lp_quote_id);
3395 ---Exception Thrown if There are more than One Sec Deposit Defined on the Quote
3396 --Check It
3397 OPEN c_lq_fee_rec(lp_quote_id,lp_sec_dep_fee_type);
3398 i:=0;
3399 FETCH c_lq_fee_rec INTO lp_lq_service_fee;
3400 WHILE c_lq_fee_rec%FOUND LOOP
3401 i:=i+1;
3402 FETCH c_lq_fee_rec INTO lp_lq_service_fee;
3403 END LOOP;
3404 CLOSE c_lq_fee_rec;
3405 --More Than One Sec Deposit At Contract Level
3406 --Already thrown out by the Check_fees_and _services() method
3407 IF(i > 1) THEN
3408 return;
3409 END IF;
3410 IF(lp_lq_service_fee.id is NOT NULL) THEN
3411 open c_lq_cfl_line(lp_service_line_id,'QUOTED_FEE');
3412 FETCH c_lq_cfl_line INTO lp_lq_cfl_line;
3413 IF(lp_lq_cfl_line.stub_amount IS NOT NULL) THEN
3414 i:=x_qa_result_tbl.COUNT;
3415 i:=i+1;
3416 x_qa_result_tbl(i).check_code:='check_payments';
3417 x_qa_result_tbl(i).check_meaning:='SEC_DEPOSIT_HAS_STUB_AMOUNT_DEFINED';
3418 x_qa_result_tbl(i).result_code:='ERROR';
3419 x_qa_result_tbl(i).result_meaning:='ERROR';
3420 x_qa_result_tbl(i).message_code:= 'OKL_QA_SEC_DEP_STUB_AMT_DEF';
3421 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SEC_DEP_STUB_AMT_DEF');
3422 x_qa_result := okl_api.G_RET_STS_ERROR;
3423 END IF;
3424 CLOSE c_lq_cfl_line;
3425 END IF;
3426 ----------------------------------------------------------------------
3427 --get quote rec
3428 --Filter 'Check For Quote Level Payment' check for pricing methods
3429 OPEN c_lq_header_rec(lp_quote_id);
3430 FETCH c_lq_header_rec INTO lp_lq_header_rec;
3431 CLOSE c_lq_header_rec;
3432 --------------------------------------------------------------
3433 FOR l_cfl_cnt_rec IN c_cash_flow_level_count(lp_quote_id ,'LEASE_QUOTE') LOOP
3434 l_cashflow_count := l_cfl_cnt_rec.cfl_count;
3435 END LOOP;
3436 IF l_cashflow_count = 0 THEN
3437 l_no_qte_payment := TRUE;
3438 ELSE
3439 l_no_qte_payment := FALSE;
3440 END IF;
3441 --------------------------------------------------------------
3442 l_are_all_lines_overriden :=
3443 are_all_lines_overriden(p_quote_id => lp_quote_id,
3444 p_pricing_method => lp_lq_header_rec.pricing_method,
3445 p_line_level_pricing => lp_lq_header_rec.line_level_pricing,
3446 x_return_status => x_return_status);
3447
3448 --------------------------------------------------------------
3449 /* l_qte_pric_opts_entered :=
3450 are_qte_pricing_opts_entered(p_lease_qte_rec => lp_lq_header_rec
3451 ,p_payment_count => l_cashflow_count
3452 ,x_return_status => x_return_status);*/
3453 --------------------------------------------------------------
3454
3455 IF(lp_lq_header_rec.pricing_method IN('SD','SF', 'SI', 'SS','SP','SY')) THEN
3456 IF((l_are_all_lines_overriden='N') OR NVL(lp_lq_header_rec.line_level_pricing , 'N') ='N') THEN
3457 IF((NVL(lp_lq_header_rec.STRUCTURED_PRICING, 'N') = 'N')) THEN
3458 -- validate srt, arrears, and pa(not for SP) from lp_lq_header_rec
3459 validate_payment_options(lp_lq_header_rec.RATE_TEMPLATE_ID
3460 ,lp_lq_header_rec.TARGET_ARREARS_YN
3461 ,lp_lq_header_rec.PRICING_METHOD
3462 ,lp_lq_header_rec.TARGET_AMOUNT
3463 ,x_qa_result_tbl
3464 ,x_return_status);
3465
3466 ELSIF((NVL(lp_lq_header_rec.STRUCTURED_PRICING, 'N') = 'Y')) THEN
3467 --validate LEASE_QUOTE cash flows for presence of
3468 --payment type , arrears, freq and cash flow level > 0
3469 validate_cashflows(lp_lq_header_rec.ID
3470 ,'LEASE_QUOTE'
3471 ,lp_lq_header_rec.PRICING_METHOD
3472 ,x_qa_result_tbl
3473 ,x_return_status);
3474
3475 END IF;
3476 --Add Asset Level Validation When LLO='Y' and not all lines are overriden
3477 IF((l_are_all_lines_overriden='N') AND NVL(lp_lq_header_rec.line_level_pricing , 'N') ='Y') THEN
3478 FOR lp_config_asset_rec IN c_configuration_asset_rec(lp_quote_id) LOOP
3479 --is asset is overriden
3480 IF lp_config_asset_rec.STRUCTURED_PRICING IS NOT NULL THEN
3481 IF((NVL(lp_config_asset_rec.STRUCTURED_PRICING, 'N') = 'N')) THEN
3482 -- validate srt, arrears, and pa(not for SP) from lp_lq_header_rec
3483 validate_payment_options(lp_config_asset_rec.RATE_TEMPLATE_ID
3484 ,lp_config_asset_rec.target_arrears
3485 ,lp_lq_header_rec.PRICING_METHOD
3486 ,lp_config_asset_rec.TARGET_AMOUNT
3487 ,x_qa_result_tbl
3488 ,x_return_status);
3489
3490 ELSIF((NVL(lp_config_asset_rec.STRUCTURED_PRICING, 'N') = 'Y')) THEN
3491 --validate LEASE_QUOTE cash flows for presence of
3492 --payment type , arrears, freq and cash flow level > 0
3493 validate_cashflows(lp_config_asset_rec.ID
3494 ,'QUOTED_ASSET'
3495 ,lp_lq_header_rec.PRICING_METHOD
3496 ,x_qa_result_tbl
3497 ,x_return_status);
3498 END IF;
3499 END IF;
3500 END LOOP;
3501 END IF;
3502 ELSIF(l_are_all_lines_overriden='Y' AND NVL(lp_lq_header_rec.line_level_pricing , 'N') ='Y') THEN
3503 FOR lp_config_asset_rec IN c_configuration_asset_rec(lp_quote_id) LOOP
3504 IF((NVL(lp_config_asset_rec.STRUCTURED_PRICING, 'N') = 'N')) THEN
3505 -- validate srt, arrears, and pa(not for SP) from lp_lq_header_rec
3506 validate_payment_options(lp_config_asset_rec.RATE_TEMPLATE_ID
3507 ,lp_config_asset_rec.target_arrears
3508 ,lp_lq_header_rec.PRICING_METHOD
3509 ,lp_config_asset_rec.TARGET_AMOUNT
3510 ,x_qa_result_tbl
3511 ,x_return_status);
3512
3513 ELSIF((NVL(lp_config_asset_rec.STRUCTURED_PRICING, 'N') = 'Y')) THEN
3514 --validate LEASE_QUOTE cash flows for presence of
3515 --payment type , arrears, freq and cash flow level > 0
3516 validate_cashflows(lp_config_asset_rec.ID
3517 ,'QUOTED_ASSET'
3518 ,lp_lq_header_rec.PRICING_METHOD
3519 ,x_qa_result_tbl
3520 ,x_return_status);
3521 END IF;
3522 END LOOP;
3523 END IF;
3524 END IF;
3525 -------------------------------------------------------------------------------
3526
3527 --Check If Rate Type ,Rate ,Periods,Frequency is Entered for
3528 --Quote having Pricing Method 'Target Rate'
3529 IF(lp_lq_header_rec.pricing_method = 'TR') THEN
3530 IF(lp_lq_header_rec.TARGET_RATE_TYPE IS NULL OR
3531 lp_lq_header_rec.TARGET_FREQUENCY IS NULL OR
3532 lp_lq_header_rec.TARGET_RATE IS NULL OR
3533 lp_lq_header_rec.TARGET_PERIODS IS NULL ) THEN
3534 i:=x_qa_result_tbl.COUNT;
3535 i:=i+1;
3536 x_qa_result_tbl(i).check_code:='check_payments';
3537 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_FOR_QUOTE';
3538 x_qa_result_tbl(i).result_code:='ERROR';
3539 x_qa_result_tbl(i).result_meaning:='ERROR';
3540 x_qa_result_tbl(i).message_code:= 'OKL_QA_TR_MISS_PAY_ERROR';
3541 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_TR_MISS_PAY_ERROR');
3542 x_qa_result := okl_api.G_RET_STS_ERROR;
3543 END IF;
3544 END IF;
3545 --------------------------------------------------------------------------------
3546 --Check for Payment Method 'RC'
3547 IF((lp_lq_header_rec.pricing_method = 'RC') AND
3548 ( NVL(lp_lq_header_rec.LINE_LEVEL_PRICING,'N')='N')) THEN
3549 IF ( (lp_lq_header_rec.RATE_CARD_ID IS NULL) AND
3550 NVL(lp_lq_header_rec.structured_pricing , 'N') = 'N') THEN
3551 i:=x_qa_result_tbl.COUNT;
3552 i:=i+1;
3553 x_qa_result_tbl(i).check_code:='check_payments';
3554 x_qa_result_tbl(i).check_meaning:='PRICING_NOT_DONE_FOR_LINE ';
3555 x_qa_result_tbl(i).result_code:='ERROR';
3556 x_qa_result_tbl(i).result_meaning:='ERROR';
3557 x_qa_result_tbl(i).message_code:= 'OKL_QA_LRS_NOT_FOUND';
3558 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_LRS_NOT_FOUND');
3559 x_qa_result := okl_api.G_RET_STS_ERROR;
3560 ELSIF(( NVL( lp_lq_header_rec.structured_pricing, 'N') = 'Y' AND
3561 lp_lq_header_rec.LEASE_RATE_FACTOR IS NULL) ) THEN
3562 i:=x_qa_result_tbl.COUNT;
3563 i:=i+1;
3564 x_qa_result_tbl(i).check_code:='check_fees_and_services';
3565 x_qa_result_tbl(i).check_meaning:='PRICING_NOT_DONE_FOR_LINE ';
3566 x_qa_result_tbl(i).result_code:='ERROR';
3567 x_qa_result_tbl(i).result_meaning:='ERROR';
3568 x_qa_result_tbl(i).message_code:= 'OKL_QA_RC_MISS_PAY_ERROR';
3569 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_RC_MISS_PAY_ERROR');
3570 x_qa_result := okl_api.G_RET_STS_ERROR;
3571 END IF;
3572 ELSIF((lp_lq_header_rec.pricing_method = 'RC') AND
3573 ( NVL(lp_lq_header_rec.LINE_LEVEL_PRICING,'N')='Y')) THEN
3574 FOR lp_config_fee_rec IN c_config_fee_rec(lp_quote_id) LOOP
3575 IF ( (lp_config_fee_rec.RATE_CARD_ID IS NULL) AND
3576 (lp_lq_header_rec.RATE_CARD_ID IS NULL AND lp_lq_header_rec.LEASE_RATE_FACTOR IS NULL) AND
3577 NVL(lp_config_fee_rec.structured_pricing , 'N') = 'N') THEN
3578 i:=x_qa_result_tbl.COUNT;
3579 i:=i+1;
3580 x_qa_result_tbl(i).check_code:='check_payments';
3581 x_qa_result_tbl(i).check_meaning:='PRICING_NOT_DONE_FOR_LINE ';
3582 x_qa_result_tbl(i).result_code:='ERROR';
3583 x_qa_result_tbl(i).result_meaning:='ERROR';
3584 x_qa_result_tbl(i).message_code:= 'OKL_QA_LRS_NOT_FOUND';
3585 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_LRS_NOT_FOUND');
3586 x_qa_result := okl_api.G_RET_STS_ERROR;
3587 ELSIF(( NVL( lp_config_fee_rec.structured_pricing, 'N') = 'Y') AND
3588 ( lp_config_fee_rec.LEASE_RATE_FACTOR IS NULL) AND
3589 (lp_lq_header_rec.RATE_CARD_ID IS NULL AND lp_lq_header_rec.LEASE_RATE_FACTOR IS NULL)) THEN
3590 i:=x_qa_result_tbl.COUNT;
3591 i:=i+1;
3592 x_qa_result_tbl(i).check_code:='check_fees_and_services';
3593 x_qa_result_tbl(i).check_meaning:='PRICING_NOT_DONE_FOR_LINE ';
3594 x_qa_result_tbl(i).result_code:='ERROR';
3595 x_qa_result_tbl(i).result_meaning:='ERROR';
3596 x_qa_result_tbl(i).message_code:= 'OKL_QA_RC_MISS_PAY_ERROR';
3597 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_RC_MISS_PAY_ERROR');
3598 x_qa_result := okl_api.G_RET_STS_ERROR;
3599 EXIT;
3600 END IF;
3601 END LOOP;
3602 END IF;
3603 --------------------------------------------------------------------------------
3604 --check if rate card version selected is valid
3605 IF lp_lq_header_rec.pricing_method = 'RC' THEN
3606 IF NVL(lp_lq_header_rec.structured_pricing , 'N') = 'N'
3607 AND lp_lq_header_rec.RATE_CARD_ID IS NOT NULL THEN
3608 l_valid := 'N';
3609 OPEN is_rc_valid_csr(lp_lq_header_rec.RATE_CARD_ID,lp_lq_header_rec.expected_start_date);
3610 FETCH is_rc_valid_csr INTO l_valid;
3611 CLOSE is_rc_valid_csr;
3612 IF l_valid = 'N' THEN
3613 i:=x_qa_result_tbl.COUNT;
3614 i:=i+1;
3615 x_qa_result_tbl(i).check_code:='check_fees_and_services';
3616 x_qa_result_tbl(i).check_meaning:='PRICING_NOT_DONE_FOR_LINE ';
3617 x_qa_result_tbl(i).result_code:='ERROR';
3618 x_qa_result_tbl(i).result_meaning:='ERROR';
3619 x_qa_result_tbl(i).message_code:= 'OKL_QA_RCHDR_NOT_VALID';
3620 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_RCHDR_NOT_VALID');
3621 x_qa_result := okl_api.G_RET_STS_ERROR;
3622
3623 END IF;
3624 END IF;
3625 IF NVL(lp_lq_header_rec.LINE_LEVEL_PRICING,'N')='Y' THEN
3626 FOR lp_config_fee_rec IN c_config_fee_rec(lp_quote_id) LOOP
3627 IF NVL(lp_config_fee_rec.structured_pricing , 'N') = 'N'
3628 AND lp_config_fee_rec.RATE_CARD_ID IS NOT NULL THEN
3629 l_valid := 'N';
3630 OPEN is_rc_valid_csr(lp_config_fee_rec.RATE_CARD_ID,lp_lq_header_rec.expected_start_date);
3631 FETCH is_rc_valid_csr INTO l_valid;
3632 CLOSE is_rc_valid_csr;
3633 IF l_valid = 'N' THEN
3634 i:=x_qa_result_tbl.COUNT;
3635 i:=i+1;
3636 x_qa_result_tbl(i).check_code:='check_fees_and_services';
3637 x_qa_result_tbl(i).check_meaning:='PRICING_NOT_DONE_FOR_LINE ';
3638 x_qa_result_tbl(i).result_code:='ERROR';
3639 x_qa_result_tbl(i).result_meaning:='ERROR';
3640 x_qa_result_tbl(i).message_code:= 'OKL_QA_RCLINE_NOT_VALID';
3641 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_RCLINE_NOT_VALID');
3642 x_qa_result := okl_api.G_RET_STS_ERROR;
3643 EXIT;
3644 END IF;
3645 END IF;
3646 END LOOP;
3647 END IF;
3648 END IF;
3649 --check if the srt version selected is valid
3650 IF lp_lq_header_rec.pricing_method IN ('SD','SF', 'SI', 'SS','SP','SM') THEN
3651 IF NVL(lp_lq_header_rec.structured_pricing , 'N') = 'N'
3652 AND lp_lq_header_rec.RATE_TEMPLATE_ID IS NOT NULL THEN
3653 l_valid := 'N';
3654 OPEN is_srt_valid_csr(lp_lq_header_rec.RATE_TEMPLATE_ID,lp_lq_header_rec.expected_start_date);
3655 FETCH is_srt_valid_csr INTO l_valid;
3656 CLOSE is_srt_valid_csr;
3657 IF l_valid = 'N' THEN
3658 i:=x_qa_result_tbl.COUNT;
3659 i:=i+1;
3660 x_qa_result_tbl(i).check_code:='check_fees_and_services';
3661 x_qa_result_tbl(i).check_meaning:='PRICING_NOT_DONE_FOR_LINE ';
3662 x_qa_result_tbl(i).result_code:='ERROR';
3663 x_qa_result_tbl(i).result_meaning:='ERROR';
3664 x_qa_result_tbl(i).message_code:= 'OKL_QA_SRTHDR_NOT_VALID';
3665 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SRTHDR_NOT_VALID');
3666 x_qa_result := okl_api.G_RET_STS_ERROR;
3667 /*ELSE
3668 check_srt_effective_rate(lp_lq_header_rec.RATE_TEMPLATE_ID,
3669 lp_lq_header_rec.ID,
3670 x_qa_result_tbl);*/
3671 END IF;
3672 END IF;
3673 IF NVL(lp_lq_header_rec.LINE_LEVEL_PRICING,'N')='Y' THEN
3674 FOR lp_config_fee_rec IN c_config_fee_rec(lp_quote_id) LOOP
3675 IF NVL(lp_config_fee_rec.structured_pricing , 'N') = 'N'
3676 AND lp_config_fee_rec.RATE_TEMPLATE_ID IS NOT NULL THEN
3677 l_valid := 'N';
3678 OPEN is_srt_valid_csr(lp_config_fee_rec.RATE_TEMPLATE_ID,lp_lq_header_rec.expected_start_date);
3679 FETCH is_srt_valid_csr INTO l_valid;
3680 CLOSE is_srt_valid_csr;
3681 IF l_valid = 'N' THEN
3682 i:=x_qa_result_tbl.COUNT;
3683 i:=i+1;
3684 x_qa_result_tbl(i).check_code:='check_fees_and_services';
3685 x_qa_result_tbl(i).check_meaning:='PRICING_NOT_DONE_FOR_LINE ';
3686 x_qa_result_tbl(i).result_code:='ERROR';
3687 x_qa_result_tbl(i).result_meaning:='ERROR';
3688 x_qa_result_tbl(i).message_code:= 'OKL_QA_SRTLINE_NOT_VALID';
3689 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SRTLINE_NOT_VALID');
3690 x_qa_result := okl_api.G_RET_STS_ERROR;
3691 EXIT;
3692 /*ELSE
3693 check_srt_effective_rate(lp_config_fee_rec.RATE_TEMPLATE_ID,
3694 lp_lq_header_rec.ID,
3695 x_qa_result_tbl);*/
3696 END IF;
3697 END IF;
3698 END LOOP;
3699 END IF;
3700 END IF;
3701 --------------------------------------------------------------------------------
3702 IF(lp_lq_header_rec.pricing_method = 'SM' ) THEN
3703 IF((l_are_all_lines_overriden='N') OR NVL(lp_lq_header_rec.line_level_pricing , 'N') ='N') THEN
3704 IF((NVL(lp_lq_header_rec.STRUCTURED_PRICING, 'N') = 'N')) THEN
3705 FOR l_cfl_line_rec IN c_lq_cfl_line(lp_quote_id ,'LEASE_QUOTE') LOOP
3706 l_cfl_count := l_cfl_count + 1;
3707 IF ((l_cfl_line_rec.stub_days > 0 AND l_cfl_line_rec.stub_amount IS NULL ) OR
3708 ( l_cfl_line_rec.number_of_periods > 0 AND l_cfl_line_rec.amount IS NULL ))THEN
3709 l_missing_pmts := l_missing_pmts + 1;
3710 END IF;
3711 END LOOP;
3712 IF (l_missing_pmts <> 1) THEN
3713 i:=x_qa_result_tbl.COUNT;
3714 i:=i+1;
3715 x_qa_result_tbl(i).check_code:='check_payments';
3716 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
3717 x_qa_result_tbl(i).result_code:='ERROR';
3718 x_qa_result_tbl(i).result_meaning:='ERROR';
3719 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
3720 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_PAY_ERROR');
3721 x_qa_result := okl_api.G_RET_STS_ERROR;
3722 END IF;
3723 IF(l_cfl_count <= 1) THEN
3724 i:=x_qa_result_tbl.COUNT;
3725 i:=i+1;
3726 x_qa_result_tbl(i).check_code:='check_payments';
3727 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
3728 x_qa_result_tbl(i).result_code:='ERROR';
3729 x_qa_result_tbl(i).result_meaning:='ERROR';
3730 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_QTE_PAY_ERROR';
3731 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_QTE_PAY_ERROR');
3732 x_qa_result := okl_api.G_RET_STS_ERROR;
3733 END IF;
3734 IF(( lp_lq_header_rec.RATE_TEMPLATE_ID IS NULL)) THEN
3735 i:=x_qa_result_tbl.COUNT;
3736 i:=i+1;
3737 x_qa_result_tbl(i).check_code:='check_payments';
3738 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_FOR_QUOTE';
3739 x_qa_result_tbl(i).result_code:='ERROR';
3740 x_qa_result_tbl(i).result_meaning:='ERROR';
3741 x_qa_result_tbl(i).message_code:= 'OKL_QA_SRT_NOT_FOUND';
3742 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SRT_NOT_FOUND');
3743 x_qa_result := okl_api.G_RET_STS_ERROR;
3744 END IF;
3745 ELSIF((NVL(lp_lq_header_rec.STRUCTURED_PRICING, 'N') = 'Y')) THEN
3746
3747 FOR l_cfl_line_rec IN c_lq_cfl_line(lp_quote_id ,'LEASE_QUOTE') LOOP
3748 l_cfl_count := l_cfl_count + 1;
3749 IF ((l_cfl_line_rec.stub_days > 0 AND l_cfl_line_rec.stub_amount IS NULL ) OR
3750 ( l_cfl_line_rec.number_of_periods > 0 AND l_cfl_line_rec.amount IS NULL ))THEN
3751 l_missing_pmts := l_missing_pmts + 1;
3752 END IF;
3753 END LOOP;
3754 IF(l_missing_pmts <> 1) THEN
3755 i:=x_qa_result_tbl.COUNT;
3756 i:=i+1;
3757 x_qa_result_tbl(i).check_code:='check_payments';
3758 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
3759 x_qa_result_tbl(i).result_code:='ERROR';
3760 x_qa_result_tbl(i).result_meaning:='ERROR';
3761 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
3762 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_PAY_ERROR');
3763 x_qa_result := okl_api.G_RET_STS_ERROR;
3764 END IF;
3765 IF (l_cfl_count <= 1) THEN
3766 i:=x_qa_result_tbl.COUNT;
3767 i:=i+1;
3768 x_qa_result_tbl(i).check_code:='check_payments';
3769 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
3770 x_qa_result_tbl(i).result_code:='ERROR';
3771 x_qa_result_tbl(i).result_meaning:='ERROR';
3772 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_QTE_PAY_ERROR';
3773 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_QTE_PAY_ERROR');
3774 x_qa_result := okl_api.G_RET_STS_ERROR;
3775 END IF;
3776 END IF;
3777 l_total_missing_pmts := l_total_missing_pmts + l_missing_pmts;
3778 l_total_cfl_count := l_total_cfl_count + l_cfl_count;
3779 IF((l_are_all_lines_overriden='N') AND NVL(lp_lq_header_rec.line_level_pricing , 'N') ='Y') THEN
3780 l_missing_pmts := 0;
3781 l_cfl_count := 0;
3782 l_no_missing_rate := FALSE;
3783 FOR configuration_asset_rec IN c_configuration_asset_rec(lp_quote_id) LOOP
3784 --if asset is overriden
3785 IF configuration_asset_rec.STRUCTURED_PRICING IS NOT NULL THEN
3786
3787 IF((NVL(configuration_asset_rec.STRUCTURED_PRICING, 'N')) = 'N') THEN
3788 -- validate srt, arrears, and pa(not for SP) from lp_lq_header_rec
3789 l_missing_pmts := 0;
3790 l_cfl_count := 0;
3791 l_no_missing_rate := FALSE;
3792 FOR l_cfl_line_rec IN c_lq_cfl_line(configuration_asset_rec.id ,configuration_asset_rec.oty_code) LOOP
3793 l_cfl_count := l_cfl_count + 1;
3794 IF ((l_cfl_line_rec.stub_days > 0 AND l_cfl_line_rec.stub_amount IS NULL ) OR
3795 (l_cfl_line_rec.number_of_periods > 0 AND l_cfl_line_rec.amount IS NULL )) THEN
3796 l_missing_pmts := l_missing_pmts + 1;
3797 END IF;
3798 IF(l_cfl_line_rec.rate IS NOT NULL ) THEN
3799 l_no_missing_rate := TRUE;
3800 END IF;
3801 END LOOP;
3802
3803 IF(l_missing_pmts > 1) THEN
3804 i:=x_qa_result_tbl.COUNT;
3805 i:=i+1;
3806 x_qa_result_tbl(i).check_code:='check_payments';
3807 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
3808 x_qa_result_tbl(i).result_code:='ERROR';
3809 x_qa_result_tbl(i).result_meaning:='ERROR';
3810 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
3811 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_PAY_ERROR');
3812 x_qa_result := okl_api.G_RET_STS_ERROR;
3813 EXIT;
3814 END IF;
3815 IF(l_missing_pmts = 0 AND configuration_asset_rec.RATE_TEMPLATE_ID IS NOT NULL) THEN
3816 i:=x_qa_result_tbl.COUNT;
3817 i:=i+1;
3818 x_qa_result_tbl(i).check_code:='check_payments';
3819 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
3820 x_qa_result_tbl(i).result_code:='ERROR';
3821 x_qa_result_tbl(i).result_meaning:='ERROR';
3822 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_ASSET_SRT_FOUND';
3823 set_fnd_message( p_msg_name => 'OKL_QA_SM_ASSET_SRT_FOUND'
3824 ,p_token1 => 'NAME'
3825 ,p_value1 => configuration_asset_rec.ID
3826 ,p_token2 => NULL
3827 ,p_value2 => NULL
3828 ,p_token3 => NULL
3829 ,p_value3 => NULL
3830 ,p_token4 => NULL
3831 ,p_value4 => NULL);
3832 x_qa_result_tbl(i).message_text:= fnd_message.get;
3833 x_qa_result := okl_api.G_RET_STS_ERROR;
3834 END IF;
3835
3836 IF(l_missing_pmts > 0 AND (l_cfl_count = 1)) THEN
3837 i:=x_qa_result_tbl.COUNT;
3838 i:=i+1;
3839 x_qa_result_tbl(i).check_code:='check_payments';
3840 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
3841 x_qa_result_tbl(i).result_code:='ERROR';
3842 x_qa_result_tbl(i).result_meaning:='ERROR';
3843 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_CONFIG_PAY_ERROR';
3844 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_CONFIG_PAY_ERROR');
3845 x_qa_result := okl_api.G_RET_STS_ERROR;
3846 EXIT;
3847 END IF;
3848 l_total_missing_pmts := l_total_missing_pmts + l_missing_pmts;
3849 l_total_cfl_count := l_total_cfl_count + l_cfl_count;
3850
3851 ELSIF((NVL(configuration_asset_rec.STRUCTURED_PRICING, 'N') = 'Y')) THEN
3852 --validate LEASE_QUOTE cash flows for presence of
3853 --payment type , arrears, freq and cash flow level > 0
3854 l_missing_pmts := 0;
3855 l_cfl_count := 0;
3856 l_no_missing_rate := FALSE;
3857 l_no_missing_rate_count := 0;
3858 FOR l_cfl_line_rec IN c_lq_cfl_line(configuration_asset_rec.id ,configuration_asset_rec.oty_code) LOOP
3859 l_cfl_count := l_cfl_count + 1;
3860 IF ((l_cfl_line_rec.stub_days > 0 AND l_cfl_line_rec.stub_amount IS NULL ) OR
3861 (l_cfl_line_rec.number_of_periods > 0 AND l_cfl_line_rec.amount IS NULL )) THEN
3862 l_missing_pmts := l_missing_pmts + 1;
3863 END IF;
3864 IF( l_cfl_line_rec.rate IS NOT NULL ) THEN
3865 l_no_missing_rate := TRUE;
3866 l_no_missing_rate_count := l_no_missing_rate_count + 1;
3867 END IF;
3868 END LOOP;
3869 IF (l_missing_pmts = 0 AND l_no_missing_rate) THEN
3870 i:=x_qa_result_tbl.COUNT;
3871 i:=i+1;
3872 x_qa_result_tbl(i).check_code:='check_payments';
3873 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_FOR_QUOTE';
3874 x_qa_result_tbl(i).result_code:='ERROR';
3875 x_qa_result_tbl(i).result_meaning:='ERROR';
3876 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_QTE_RATE_ERROR';
3877 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_QTE_RATE_ERROR');
3878 x_qa_result := okl_api.G_RET_STS_ERROR;
3879 EXIT;
3880 END IF;
3881 IF(l_missing_pmts > 1) THEN
3882 i:=x_qa_result_tbl.COUNT;
3883 i:=i+1;
3884 x_qa_result_tbl(i).check_code:='check_payments';
3885 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
3886 x_qa_result_tbl(i).result_code:='ERROR';
3887 x_qa_result_tbl(i).result_meaning:='ERROR';
3888 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
3889 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_PAY_ERROR');
3890 x_qa_result := okl_api.G_RET_STS_ERROR;
3891 EXIT;
3892 END IF;
3893 ---- ???????
3894 IF(l_missing_pmts > 0 AND (l_cfl_count <> l_no_missing_rate_count)) THEN
3895 i:=x_qa_result_tbl.COUNT;
3896 i:=i+1;
3897 x_qa_result_tbl(i).check_code:='check_payments';
3898 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
3899 x_qa_result_tbl(i).result_code:='ERROR';
3900 x_qa_result_tbl(i).result_meaning:='ERROR';
3901 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
3902 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_RATE_ERROR');
3903 x_qa_result := okl_api.G_RET_STS_ERROR;
3904 EXIT;
3905 END IF;
3906 ---- ???????
3907 IF(l_missing_pmts > 0 AND (l_cfl_count = 1)) THEN
3908 i:=x_qa_result_tbl.COUNT;
3909 i:=i+1;
3910 x_qa_result_tbl(i).check_code:='check_payments';
3911 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
3912 x_qa_result_tbl(i).result_code:='ERROR';
3913 x_qa_result_tbl(i).result_meaning:='ERROR';
3914 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_CONFIG_PAY_ERROR';
3915 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_CONFIG_PAY_ERROR');
3916 x_qa_result := okl_api.G_RET_STS_ERROR;
3917 EXIT;
3918 END IF;
3919 l_total_missing_pmts := l_total_missing_pmts + l_missing_pmts;
3920 l_total_cfl_count := l_total_cfl_count + l_cfl_count;
3921
3922 END IF;
3923 END IF;
3924 END LOOP;
3925
3926 END IF;
3927 ELSIF (l_are_all_lines_overriden='Y' AND NVL(lp_lq_header_rec.line_level_pricing , 'N') ='Y') THEN
3928 l_missing_pmts := 0;
3929 l_cfl_count := 0;
3930 l_no_missing_rate := FALSE;
3931 FOR configuration_asset_rec IN c_configuration_asset_rec(lp_quote_id) LOOP
3932 IF((NVL(configuration_asset_rec.STRUCTURED_PRICING, 'N')) = 'N') THEN
3933 l_missing_pmts := 0;
3934 l_cfl_count := 0;
3935 l_no_missing_rate := FALSE;
3936 FOR l_cfl_line_rec IN c_lq_cfl_line(configuration_asset_rec.id ,configuration_asset_rec.oty_code) LOOP
3937 l_cfl_count := l_cfl_count + 1;
3938 IF ((l_cfl_line_rec.stub_days > 0 AND l_cfl_line_rec.stub_amount IS NULL ) OR
3939 (l_cfl_line_rec.number_of_periods > 0 AND l_cfl_line_rec.amount IS NULL )) THEN
3940 l_missing_pmts := l_missing_pmts + 1;
3941 END IF;
3942 IF(l_cfl_line_rec.rate IS NOT NULL ) THEN
3943 l_no_missing_rate := TRUE;
3944 END IF;
3945 END LOOP;
3946 IF(l_missing_pmts > 1) THEN
3947 i:=x_qa_result_tbl.COUNT;
3948 i:=i+1;
3949 x_qa_result_tbl(i).check_code:='check_payments';
3950 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
3951 x_qa_result_tbl(i).result_code:='ERROR';
3952 x_qa_result_tbl(i).result_meaning:='ERROR';
3953 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
3954 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_PAY_ERROR');
3955 x_qa_result := okl_api.G_RET_STS_ERROR;
3956 EXIT;
3957 END IF;
3958 IF(l_missing_pmts = 0 AND configuration_asset_rec.RATE_TEMPLATE_ID IS NOT NULL) THEN
3959 i:=x_qa_result_tbl.COUNT;
3960 i:=i+1;
3961 x_qa_result_tbl(i).check_code:='check_payments';
3962 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
3963 x_qa_result_tbl(i).result_code:='ERROR';
3964 x_qa_result_tbl(i).result_meaning:='ERROR';
3965 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_ASSET_SRT_FOUND';
3966 set_fnd_message( p_msg_name => 'OKL_QA_SM_ASSET_SRT_FOUND'
3967 ,p_token1 => 'NAME'
3968 ,p_value1 => configuration_asset_rec.ID
3969 ,p_token2 => NULL
3970 ,p_value2 => NULL
3971 ,p_token3 => NULL
3972 ,p_value3 => NULL
3973 ,p_token4 => NULL
3974 ,p_value4 => NULL);
3975 x_qa_result_tbl(i).message_text:= fnd_message.get;
3976 x_qa_result := okl_api.G_RET_STS_ERROR;
3977 END IF;
3978 --For line level override, if SRT is entered at the asset level, and no
3979 --missing payment is defined, There should be an error stating that the
3980 --SRT should be removed, as no missing payment is defined and vice-versa.
3981 IF(l_missing_pmts > 0 AND configuration_asset_rec.RATE_TEMPLATE_ID IS NULL ) THEN
3982 i:=x_qa_result_tbl.COUNT;
3983 i:=i+1;
3984 x_qa_result_tbl(i).check_code:='check_payments';
3985 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
3986 x_qa_result_tbl(i).result_code:='ERROR';
3987 x_qa_result_tbl(i).result_meaning:='ERROR';
3988 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_ASSET_SRT_REQ';
3989 set_fnd_message( p_msg_name => 'OKL_QA_SM_ASSET_SRT_REQ'
3990 ,p_token1 => 'NAME'
3991 ,p_value1 => configuration_asset_rec.ID
3992 ,p_token2 => NULL
3993 ,p_value2 => NULL
3994 ,p_token3 => NULL
3995 ,p_value3 => NULL
3996 ,p_token4 => NULL
3997 ,p_value4 => NULL);
3998 x_qa_result_tbl(i).message_text:= fnd_message.get;
3999 x_qa_result := okl_api.G_RET_STS_ERROR;
4000
4001 END IF;
4002 IF(l_missing_pmts > 0 AND (l_cfl_count = 1)) THEN
4003 i:=x_qa_result_tbl.COUNT;
4004 i:=i+1;
4005 x_qa_result_tbl(i).check_code:='check_payments';
4006 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4007 x_qa_result_tbl(i).result_code:='ERROR';
4008 x_qa_result_tbl(i).result_meaning:='ERROR';
4009 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_CONFIG_PAY_ERROR';
4010 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_CONFIG_PAY_ERROR');
4011 x_qa_result := okl_api.G_RET_STS_ERROR;
4012 EXIT;
4013 END IF;
4014 l_total_missing_pmts := l_total_missing_pmts + l_missing_pmts;
4015 l_total_cfl_count := l_total_cfl_count + l_cfl_count;
4016
4017 ELSIF((NVL(configuration_asset_rec.STRUCTURED_PRICING, 'N') = 'Y')) THEN
4018 --validate LEASE_QUOTE cash flows for presence of
4019 --payment type , arrears, freq and cash flow level > 0
4020 l_missing_pmts := 0;
4021 l_cfl_count := 0;
4022 l_no_missing_rate := FALSE;
4023 l_no_missing_rate_count := 0;
4024 FOR l_cfl_line_rec IN c_lq_cfl_line(configuration_asset_rec.id ,configuration_asset_rec.oty_code) LOOP
4025 l_cfl_count := l_cfl_count + 1;
4026 IF ((l_cfl_line_rec.stub_days > 0 AND l_cfl_line_rec.stub_amount IS NULL ) OR
4027 (l_cfl_line_rec.number_of_periods > 0 AND l_cfl_line_rec.amount IS NULL )) THEN
4028 l_missing_pmts := l_missing_pmts + 1;
4029 END IF;
4030 IF( l_cfl_line_rec.rate IS NOT NULL ) THEN
4031 l_no_missing_rate := TRUE;
4032 l_no_missing_rate_count := l_no_missing_rate_count + 1;
4033 END IF;
4034 END LOOP;
4035 IF (l_missing_pmts = 0 AND l_no_missing_rate) THEN
4036 i:=x_qa_result_tbl.COUNT;
4037 i:=i+1;
4038 x_qa_result_tbl(i).check_code:='check_payments';
4039 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_FOR_QUOTE';
4040 x_qa_result_tbl(i).result_code:='ERROR';
4041 x_qa_result_tbl(i).result_meaning:='ERROR';
4042 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_QTE_RATE_ERROR';
4043 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_QTE_RATE_ERROR');
4044 x_qa_result := okl_api.G_RET_STS_ERROR;
4045 EXIT;
4046 END IF;
4047 IF(l_missing_pmts > 1) THEN
4048 i:=x_qa_result_tbl.COUNT;
4049 i:=i+1;
4050 x_qa_result_tbl(i).check_code:='check_payments';
4051 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4052 x_qa_result_tbl(i).result_code:='ERROR';
4053 x_qa_result_tbl(i).result_meaning:='ERROR';
4054 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
4055 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_PAY_ERROR');
4056 x_qa_result := okl_api.G_RET_STS_ERROR;
4057 EXIT;
4058 END IF;
4059 IF(l_missing_pmts > 0 AND (l_cfl_count <> l_no_missing_rate_count)) THEN
4060 i:=x_qa_result_tbl.COUNT;
4061 i:=i+1;
4062 x_qa_result_tbl(i).check_code:='check_payments';
4063 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4064 x_qa_result_tbl(i).result_code:='ERROR';
4065 x_qa_result_tbl(i).result_meaning:='ERROR';
4066 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
4067 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_RATE_ERROR');
4068 x_qa_result := okl_api.G_RET_STS_ERROR;
4069 EXIT;
4070 END IF;
4071 /*IF(l_missing_pmts > 0 AND (l_cfl_count = 1)) THEN
4072 i:=x_qa_result_tbl.COUNT;
4073 i:=i+1;
4074 x_qa_result_tbl(i).check_code:='check_payments';
4075 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4076 x_qa_result_tbl(i).result_code:='ERROR';
4077 x_qa_result_tbl(i).result_meaning:='ERROR';
4078 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_CONFIG_PAY_ERROR';
4079 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_CONFIG_PAY_ERROR');
4080 x_qa_result := okl_api.G_RET_STS_ERROR;
4081 EXIT;
4082 END IF;*/
4083 l_total_missing_pmts := l_total_missing_pmts + l_missing_pmts;
4084 l_total_cfl_count := l_total_cfl_count + l_cfl_count;
4085
4086 END IF;
4087 END LOOP;
4088 IF(l_total_missing_pmts > 0 AND (l_total_cfl_count = 1)) THEN
4089 i:=x_qa_result_tbl.COUNT;
4090 i:=i+1;
4091 x_qa_result_tbl(i).check_code:='check_payments';
4092 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4093 x_qa_result_tbl(i).result_code:='ERROR';
4094 x_qa_result_tbl(i).result_meaning:='ERROR';
4095 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_CONFIG_PAY_ERROR';
4096 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_CONFIG_PAY_ERROR');
4097 x_qa_result := okl_api.G_RET_STS_ERROR;
4098
4099 END IF;
4100 -- ???????????????????
4101 IF(l_total_missing_pmts = 0) THEN
4102 i:=x_qa_result_tbl.COUNT;
4103 i:=i+1;
4104 x_qa_result_tbl(i).check_code:='check_payments';
4105 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4106 x_qa_result_tbl(i).result_code:='ERROR';
4107 x_qa_result_tbl(i).result_meaning:='ERROR';
4108 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
4109 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_PAY_ERROR');
4110 x_qa_result := okl_api.G_RET_STS_ERROR;
4111 END IF;
4112 -- ????????????????????
4113 /*IF(l_total_cfl_count <= 1 and l_total_missing_pmts <> 0 ) THEN
4114 i:=x_qa_result_tbl.COUNT;
4115 i:=i+1;
4116 x_qa_result_tbl(i).check_code:='check_payments';
4117 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4118 x_qa_result_tbl(i).result_code:='ERROR';
4119 x_qa_result_tbl(i).result_meaning:='ERROR';
4120 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_CONFIG_PAY_ERROR';
4121 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_CONFIG_PAY_ERROR');
4122 x_qa_result := okl_api.G_RET_STS_ERROR;
4123 END IF;*/
4124 END IF;--
4125 END IF;--Pricing Method is 'SM'
4126
4127 --------------------------------------------------------------------------------
4128 /* --Bug # 5021838 ssdeshpa start
4129 IF(l_are_all_lines_overriden='N') THEN
4130 --Bug # 5036739 ssdeshpa start
4131 IF((lp_lq_header_rec.pricing_method ='SM') AND
4132 NVL(lp_lq_header_rec.structured_pricing,'N')='N') THEN
4133 FOR l_cfl_line_rec IN c_lq_cfl_line(lp_quote_id ,'LEASE_QUOTE') LOOP
4134 l_cfl_count := l_cfl_count + 1;
4135 IF ((l_cfl_line_rec.stub_days > 0 AND l_cfl_line_rec.stub_amount IS NULL ) OR
4136 ( l_cfl_line_rec.number_of_periods > 0 AND l_cfl_line_rec.amount IS NULL ))THEN
4137 l_missing_pmts := l_missing_pmts + 1;
4138 END IF;
4139 END LOOP;
4140
4141 IF (l_missing_pmts <> 1) THEN
4142 i:=x_qa_result_tbl.COUNT;
4143 i:=i+1;
4144 x_qa_result_tbl(i).check_code:='check_payments';
4145 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4146 x_qa_result_tbl(i).result_code:='ERROR';
4147 x_qa_result_tbl(i).result_meaning:='ERROR';
4148 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
4149 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_PAY_ERROR');
4150 x_qa_result := okl_api.G_RET_STS_ERROR;
4151 END IF;
4152 IF(l_cfl_count <= 1) THEN
4153 i:=x_qa_result_tbl.COUNT;
4154 i:=i+1;
4155 x_qa_result_tbl(i).check_code:='check_payments';
4156 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4157 x_qa_result_tbl(i).result_code:='ERROR';
4158 x_qa_result_tbl(i).result_meaning:='ERROR';
4159 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_QTE_PAY_ERROR';
4160 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_QTE_PAY_ERROR');
4161 x_qa_result := okl_api.G_RET_STS_ERROR;
4162 END IF;
4163 IF(( lp_lq_header_rec.RATE_TEMPLATE_ID IS NULL)) THEN
4164 i:=x_qa_result_tbl.COUNT;
4165 i:=i+1;
4166 x_qa_result_tbl(i).check_code:='check_payments';
4167 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_FOR_QUOTE';
4168 x_qa_result_tbl(i).result_code:='ERROR';
4169 x_qa_result_tbl(i).result_meaning:='ERROR';
4170 x_qa_result_tbl(i).message_code:= 'OKL_QA_SRT_NOT_FOUND';
4171 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SRT_NOT_FOUND');
4172 x_qa_result := okl_api.G_RET_STS_ERROR;
4173 END IF;
4174 ELSIF((lp_lq_header_rec.pricing_method ='SM') AND
4175 NVL(lp_lq_header_rec.structured_pricing,'N')='Y') THEN
4176 FOR l_cfl_line_rec IN c_lq_cfl_line(lp_quote_id ,'LEASE_QUOTE') LOOP
4177 l_cfl_count := l_cfl_count + 1;
4178 IF ((l_cfl_line_rec.stub_days > 0 AND l_cfl_line_rec.stub_amount IS NULL ) OR
4179 ( l_cfl_line_rec.number_of_periods > 0 AND l_cfl_line_rec.amount IS NULL ))THEN
4180 l_missing_pmts := l_missing_pmts + 1;
4181 END IF;
4182 END LOOP;
4183 IF (l_missing_pmts <> 1) THEN
4184 i:=x_qa_result_tbl.COUNT;
4185 i:=i+1;
4186 x_qa_result_tbl(i).check_code:='check_payments';
4187 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4188 x_qa_result_tbl(i).result_code:='ERROR';
4189 x_qa_result_tbl(i).result_meaning:='ERROR';
4190 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
4191 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_PAY_ERROR');
4192 x_qa_result := okl_api.G_RET_STS_ERROR;
4193 END IF;
4194 IF (l_cfl_count <= 1) THEN
4195 i:=x_qa_result_tbl.COUNT;
4196 i:=i+1;
4197 x_qa_result_tbl(i).check_code:='check_payments';
4198 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4199 x_qa_result_tbl(i).result_code:='ERROR';
4200 x_qa_result_tbl(i).result_meaning:='ERROR';
4201 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_QTE_PAY_ERROR';
4202 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_QTE_PAY_ERROR');
4203 x_qa_result := okl_api.G_RET_STS_ERROR;
4204 END IF;
4205 END IF;--
4206 END IF;--l_are_all_lines_overriden='N'
4207
4208 --Check For Asset Level
4209 IF(( NVL(lp_lq_header_rec.line_level_pricing , 'N') ='Y') AND--Main Loop Start
4210 ( lp_lq_header_rec.pricing_method ='SM')) THEN
4211 l_missing_pmts := 0;
4212 l_cfl_count := 0;
4213 l_no_missing_rate := FALSE;
4214 FOR configuration_asset_rec IN c_configuration_asset_rec(lp_quote_id) LOOP
4215 IF ( NVL(configuration_asset_rec.STRUCTURED_PRICING, 'N') <> 'N')THEN
4216 l_missing_pmts := 0;
4217 l_cfl_count := 0;
4218 l_no_missing_rate := FALSE;
4219 l_no_missing_rate_count := 0;
4220 FOR l_cfl_line_rec IN c_lq_cfl_line(configuration_asset_rec.id ,configuration_asset_rec.oty_code) LOOP
4221 l_cfl_count := l_cfl_count + 1;
4222 IF ((l_cfl_line_rec.stub_days > 0 AND l_cfl_line_rec.stub_amount IS NULL ) OR
4223 (l_cfl_line_rec.number_of_periods > 0 AND l_cfl_line_rec.amount IS NULL )) THEN
4224 l_missing_pmts := l_missing_pmts + 1;
4225 END IF;
4226 IF( l_cfl_line_rec.rate IS NOT NULL ) THEN
4227 l_no_missing_rate := TRUE;
4228 l_no_missing_rate_count := l_no_missing_rate_count + 1;
4229 END IF;
4230 END LOOP;
4231
4232 IF (l_missing_pmts = 0 AND l_no_missing_rate) THEN
4233 i:=x_qa_result_tbl.COUNT;
4234 i:=i+1;
4235 x_qa_result_tbl(i).check_code:='check_payments';
4236 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_FOR_QUOTE';
4237 x_qa_result_tbl(i).result_code:='ERROR';
4238 x_qa_result_tbl(i).result_meaning:='ERROR';
4239 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_QTE_RATE_ERROR';
4240 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_QTE_RATE_ERROR');
4241 x_qa_result := okl_api.G_RET_STS_ERROR;
4242 EXIT;
4243 ELSIF(l_missing_pmts > 1) THEN
4244 i:=x_qa_result_tbl.COUNT;
4245 i:=i+1;
4246 x_qa_result_tbl(i).check_code:='check_payments';
4247 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4248 x_qa_result_tbl(i).result_code:='ERROR';
4249 x_qa_result_tbl(i).result_meaning:='ERROR';
4250 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
4251 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_PAY_ERROR');
4252 x_qa_result := okl_api.G_RET_STS_ERROR;
4253 EXIT;
4254 ELSIF(l_missing_pmts > 0 AND (l_cfl_count <> l_no_missing_rate_count)) THEN
4255 i:=x_qa_result_tbl.COUNT;
4256 i:=i+1;
4257 x_qa_result_tbl(i).check_code:='check_payments';
4258 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4259 x_qa_result_tbl(i).result_code:='ERROR';
4260 x_qa_result_tbl(i).result_meaning:='ERROR';
4261 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
4262 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_RATE_ERROR');
4263 x_qa_result := okl_api.G_RET_STS_ERROR;
4264 EXIT;
4265 END IF;
4266 l_total_missing_pmts := l_total_missing_pmts + l_missing_pmts;
4267 l_total_cfl_count := l_total_cfl_count + l_cfl_count;
4268
4269 ELSIF( NVL(configuration_asset_rec.structured_pricing , 'N') ='N')THEN
4270 IF((configuration_asset_rec.RATE_TEMPLATE_ID IS NULL) AND
4271 (lp_lq_header_rec.RATE_TEMPLATE_ID IS NULL AND l_no_qte_payment))
4272 THEN
4273 i:=x_qa_result_tbl.COUNT;
4274 i:=i+1;
4275 x_qa_result_tbl(i).check_code:='check_fees_and_services';
4276 x_qa_result_tbl(i).check_meaning:='PRICING_NOT_DONE_FOR_LINE ';
4277 x_qa_result_tbl(i).result_code:='ERROR';
4278 x_qa_result_tbl(i).result_meaning:='ERROR';
4279 x_qa_result_tbl(i).message_code:= 'OKL_QA_SRT_NOT_FOUND';
4280 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SRT_NOT_FOUND');
4281 x_qa_result := okl_api.G_RET_STS_ERROR;
4282 EXIT;
4283 END IF;
4284 l_missing_pmts := 0;
4285 l_cfl_count := 0;
4286 l_no_missing_rate := FALSE;
4287 FOR l_cfl_line_rec IN c_lq_cfl_line(configuration_asset_rec.id ,configuration_asset_rec.oty_code) LOOP
4288 l_cfl_count := l_cfl_count + 1;
4289 IF ((l_cfl_line_rec.stub_days > 0 AND l_cfl_line_rec.stub_amount IS NULL ) OR
4290 (l_cfl_line_rec.number_of_periods > 0 AND l_cfl_line_rec.amount IS NULL )) THEN
4291 l_missing_pmts := l_missing_pmts + 1;
4292 END IF;
4293 IF(l_cfl_line_rec.rate IS NOT NULL ) THEN
4294 l_no_missing_rate := TRUE;
4295 END IF;
4296 END LOOP;
4297
4298 IF (l_missing_pmts = 0 AND l_no_missing_rate) THEN
4299 i:=x_qa_result_tbl.COUNT;
4300 i:=i+1;
4301 x_qa_result_tbl(i).check_code:='check_payments';
4302 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_FOR_QUOTE';
4303 x_qa_result_tbl(i).result_code:='ERROR';
4304 x_qa_result_tbl(i).result_meaning:='ERROR';
4305 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_QTE_RATE_ERROR';
4306 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_QTE_RATE_ERROR');
4307 x_qa_result := okl_api.G_RET_STS_ERROR;
4308 EXIT;
4309 ELSIF(l_missing_pmts > 1) THEN
4310 i:=x_qa_result_tbl.COUNT;
4311 i:=i+1;
4312 x_qa_result_tbl(i).check_code:='check_payments';
4313 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4314 x_qa_result_tbl(i).result_code:='ERROR';
4315 x_qa_result_tbl(i).result_meaning:='ERROR';
4316 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
4317 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_PAY_ERROR');
4318 x_qa_result := okl_api.G_RET_STS_ERROR;
4319 EXIT;
4320 END IF;
4321
4322 END IF;
4323 l_total_missing_pmts := l_total_missing_pmts + l_missing_pmts;
4324 l_total_cfl_count := l_total_cfl_count + l_cfl_count;
4325 END LOOP;
4326 IF(l_total_cfl_count <= 1 and l_total_missing_pmts <> 0 ) THEN
4327 i:=x_qa_result_tbl.COUNT;
4328 i:=i+1;
4329 x_qa_result_tbl(i).check_code:='check_payments';
4330 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4331 x_qa_result_tbl(i).result_code:='ERROR';
4332 x_qa_result_tbl(i).result_meaning:='ERROR';
4333 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_CONFIG_PAY_ERROR';
4334 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_CONFIG_PAY_ERROR');
4335 x_qa_result := okl_api.G_RET_STS_ERROR;
4336
4337 END IF;
4338 END IF;
4339 --Bug # 5036739 ssdeshpa end */
4340 -------------------------------------------------------------------------------
4341
4342 -------------------------------------------------------------------------------
4343 --Bug # 5070686 ssdeshpa start
4344 /**Quote Level should only apply to configuration lines that are leasing Assets.
4345 Quote Level Payments should be distributed among Asset Lines only.
4346 Rollover and Finance Fees always require line level payments, even if Lines
4347 Override box is unchecked. Exception to this rule are in Target Rate and
4348 Solve for Payment Pricing Method, where payments are not required as inputs
4349 and where a quote level (other than a line level) Interest Rate can be used
4350 to calculate line level payments for Financed and Rollover Fees.
4351
4352 **/
4353 IF(lp_lq_header_rec.pricing_method NOT IN('SP','TR','SM','RC')) THEN
4354 FOR configuration_fee_rec IN c_configuration_fee_rec(lp_quote_id) LOOP
4355 IF((NVL(configuration_fee_rec.STRUCTURED_PRICING, 'N') = 'N')) THEN
4356 -- validate srt, arrears, and pa(not for SP) from lp_lq_header_rec
4357 validate_payment_options(configuration_fee_rec.RATE_TEMPLATE_ID
4358 ,configuration_fee_rec.target_arrears
4359 ,lp_lq_header_rec.PRICING_METHOD
4360 ,configuration_fee_rec.TARGET_AMOUNT
4361 ,x_qa_result_tbl
4362 ,x_return_status);
4363 ELSIF((NVL(configuration_fee_rec.STRUCTURED_PRICING, 'N') = 'Y')) THEN
4364 --validate LEASE_QUOTE cash flows for presence of
4365 --payment type , arrears, freq and cash flow level > 0
4366 validate_cashflows(configuration_fee_rec.ID
4367 ,'QUOTED_FEE'
4368 ,lp_lq_header_rec.PRICING_METHOD
4369 ,x_qa_result_tbl
4370 ,x_return_status);
4371 END IF;
4372 END LOOP;
4373 END IF;
4374 -----------------------------------------------------------------------------
4375 --Checking config Fees for Pricing Method='SM'
4376 IF(lp_lq_header_rec.pricing_method = 'SM') THEN
4377 l_total_missing_pmts :=0;
4378 l_total_cfl_count:=0;
4379 l_missing_pmts := 0;
4380 l_cfl_count := 0;
4381 l_no_missing_rate := FALSE;
4382 FOR configuration_fee_rec IN c_configuration_fee_rec(lp_quote_id) LOOP
4383 --Count toatal no Fin. or Rollover Fees
4384 l_fees_count := l_fees_count + 1;
4385 IF((NVL(configuration_fee_rec.STRUCTURED_PRICING, 'N') = 'Y')) THEN
4386 l_missing_pmts := 0;
4387 l_cfl_count := 0;
4388 l_no_missing_rate := FALSE;
4389 l_no_missing_rate_count := 0;
4390 FOR l_cfl_line_rec IN c_lq_cfl_line(configuration_fee_rec.id ,configuration_fee_rec.oty_code)
4391 LOOP
4392 l_cfl_count := l_cfl_count + 1;
4393 IF ((l_cfl_line_rec.stub_days > 0 AND l_cfl_line_rec.stub_amount IS NULL ) OR
4394 (l_cfl_line_rec.number_of_periods > 0 AND l_cfl_line_rec.amount IS NULL )) THEN
4395 l_missing_pmts := l_missing_pmts + 1;
4396 END IF;
4397 IF(l_cfl_line_rec.rate IS NOT NULL ) THEN
4398 l_no_missing_rate := TRUE;
4399 l_no_missing_rate_count := l_no_missing_rate_count + 1;
4400 END IF;
4401 END LOOP;
4402 IF (l_missing_pmts = 0 AND l_no_missing_rate) THEN
4403 i:=x_qa_result_tbl.COUNT;
4404 i:=i+1;
4405 x_qa_result_tbl(i).check_code:='check_payments';
4406 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_FOR_QUOTE';
4407 x_qa_result_tbl(i).result_code:='ERROR';
4408 x_qa_result_tbl(i).result_meaning:='ERROR';
4409 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_QTE_RATE_ERROR';
4410 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_QTE_RATE_ERROR');
4411 x_qa_result := okl_api.G_RET_STS_ERROR;
4412 EXIT;
4413 ELSIF(l_missing_pmts > 1) THEN
4414 i:=x_qa_result_tbl.COUNT;
4415 i:=i+1;
4416 x_qa_result_tbl(i).check_code:='check_payments';
4417 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4418 x_qa_result_tbl(i).result_code:='ERROR';
4419 x_qa_result_tbl(i).result_meaning:='ERROR';
4420 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
4421 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_PAY_ERROR');
4422 x_qa_result := okl_api.G_RET_STS_ERROR;
4423 EXIT;
4424 ELSIF(l_missing_pmts > 0 AND (l_cfl_count <> l_no_missing_rate_count)) THEN
4425 i:=x_qa_result_tbl.COUNT;
4426 i:=i+1;
4427 x_qa_result_tbl(i).check_code:='check_payments';
4428 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4429 x_qa_result_tbl(i).result_code:='ERROR';
4430 x_qa_result_tbl(i).result_meaning:='ERROR';
4431 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
4432 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_RATE_ERROR');
4433 x_qa_result := okl_api.G_RET_STS_ERROR;
4434 EXIT;
4435 /*ELSIF(l_missing_pmts > 0 AND (l_cfl_count = 1)) THEN
4436 i:=x_qa_result_tbl.COUNT;
4437 i:=i+1;
4438 x_qa_result_tbl(i).check_code:='check_payments';
4439 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4440 x_qa_result_tbl(i).result_code:='ERROR';
4441 x_qa_result_tbl(i).result_meaning:='ERROR';
4442 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_CONFIG_PAY_ERROR';
4443 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_CONFIG_PAY_ERROR');
4444 x_qa_result := okl_api.G_RET_STS_ERROR;
4445 EXIT;*/
4446 END IF;
4447 l_total_missing_pmts := l_total_missing_pmts + l_missing_pmts;
4448 l_total_cfl_count := l_total_cfl_count + l_cfl_count;
4449
4450 ELSIF( NVL(configuration_fee_rec.structured_pricing , 'N') ='N')THEN
4451 /* IF(configuration_fee_rec.RATE_TEMPLATE_ID IS NULL)THEN
4452 i:=x_qa_result_tbl.COUNT;
4453 i:=i+1;
4454 x_qa_result_tbl(i).check_code:='check_fees_and_services';
4455 x_qa_result_tbl(i).check_meaning:='PRICING_NOT_DONE_FOR_LINE ';
4456 x_qa_result_tbl(i).result_code:='ERROR';
4457 x_qa_result_tbl(i).result_meaning:='ERROR';
4458 x_qa_result_tbl(i).message_code:= 'OKL_QA_SRT_NOT_FOUND';
4459 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SRT_NOT_FOUND');
4460 x_qa_result := okl_api.G_RET_STS_ERROR;
4461 EXIT;
4462 END IF; */
4463 l_missing_pmts := 0;
4464 l_cfl_count := 0;
4465 l_no_missing_rate := FALSE;
4466 FOR l_cfl_line_rec IN c_lq_cfl_line(configuration_fee_rec.id ,configuration_fee_rec.oty_code) LOOP
4467 l_cfl_count := l_cfl_count + 1;
4468 IF ((l_cfl_line_rec.stub_days > 0 AND l_cfl_line_rec.stub_amount IS NULL ) OR
4469 (l_cfl_line_rec.number_of_periods > 0 AND l_cfl_line_rec.amount IS NULL )) THEN
4470 l_missing_pmts := l_missing_pmts + 1;
4471 END IF;
4472 IF(l_cfl_line_rec.rate IS NOT NULL ) THEN
4473 l_no_missing_rate := TRUE;
4474 END IF;
4475 END LOOP;
4476 IF (l_missing_pmts = 0 AND l_no_missing_rate) THEN
4477 i:=x_qa_result_tbl.COUNT;
4478 i:=i+1;
4479 x_qa_result_tbl(i).check_code:='check_payments';
4480 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_FOR_QUOTE';
4481 x_qa_result_tbl(i).result_code:='ERROR';
4482 x_qa_result_tbl(i).result_meaning:='ERROR';
4483 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_QTE_RATE_ERROR';
4484 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_QTE_RATE_ERROR');
4485 x_qa_result := okl_api.G_RET_STS_ERROR;
4486 EXIT;
4487 ELSIF(l_missing_pmts > 1) THEN
4488 i:=x_qa_result_tbl.COUNT;
4489 i:=i+1;
4490 x_qa_result_tbl(i).check_code:='check_payments';
4491 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4492 x_qa_result_tbl(i).result_code:='ERROR';
4493 x_qa_result_tbl(i).result_meaning:='ERROR';
4494 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
4495 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_PAY_ERROR');
4496 x_qa_result := okl_api.G_RET_STS_ERROR;
4497 EXIT;
4498 --For line level override, if SRT is entered at the asset level, and no
4499 --missing payment is defined, There should be an error stating that the
4500 --SRT should be removed, as no missing payment is defined and vice-versa.
4501 ELSIF(l_missing_pmts > 0 AND configuration_fee_rec.RATE_TEMPLATE_ID IS NULL ) THEN
4502 i:=x_qa_result_tbl.COUNT;
4503 i:=i+1;
4504 x_qa_result_tbl(i).check_code:='check_payments';
4505 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4506 x_qa_result_tbl(i).result_code:='ERROR';
4507 x_qa_result_tbl(i).result_meaning:='ERROR';
4508 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_FEE_SRT_REQ';
4509 set_fnd_message( p_msg_name => 'OKL_QA_SM_FEE_SRT_REQ'
4510 ,p_token1 => 'NAME'
4511 ,p_value1 => configuration_fee_rec.name
4512 ,p_token2 => NULL
4513 ,p_value2 => NULL
4514 ,p_token3 => NULL
4515 ,p_value3 => NULL
4516 ,p_token4 => NULL
4517 ,p_value4 => NULL);
4518 x_qa_result_tbl(i).message_text:= fnd_message.get;
4519 x_qa_result := okl_api.G_RET_STS_ERROR;
4520
4521 ELSIF(l_missing_pmts = 0 AND configuration_fee_rec.RATE_TEMPLATE_ID IS NOT NULL) THEN
4522 i:=x_qa_result_tbl.COUNT;
4523 i:=i+1;
4524 x_qa_result_tbl(i).check_code:='check_payments';
4525 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4526 x_qa_result_tbl(i).result_code:='ERROR';
4527 x_qa_result_tbl(i).result_meaning:='ERROR';
4528 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_FEE_SRT_FOUND';
4529 set_fnd_message( p_msg_name => 'OKL_QA_SM_FEE_SRT_FOUND'
4530 ,p_token1 => 'NAME'
4531 ,p_value1 => configuration_fee_rec.name
4532 ,p_token2 => NULL
4533 ,p_value2 => NULL
4534 ,p_token3 => NULL
4535 ,p_value3 => NULL
4536 ,p_token4 => NULL
4537 ,p_value4 => NULL);
4538 x_qa_result_tbl(i).message_text:= fnd_message.get;
4539 x_qa_result := okl_api.G_RET_STS_ERROR;
4540
4541 END IF;
4542 l_total_missing_pmts := l_total_missing_pmts + l_missing_pmts;
4543 l_total_cfl_count := l_total_cfl_count + l_cfl_count;
4544 END IF;
4545
4546 END LOOP;--For each config fees
4547 --check only if There are Fin. or Rollover Fees
4548 IF(l_fees_count > 0 AND l_total_missing_pmts = 0) THEN
4549 i:=x_qa_result_tbl.COUNT;
4550 i:=i+1;
4551 x_qa_result_tbl(i).check_code:='check_payments';
4552 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4553 x_qa_result_tbl(i).result_code:='ERROR';
4554 x_qa_result_tbl(i).result_meaning:='ERROR';
4555 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_MISS_PAY_ERROR';
4556 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_MISS_PAY_ERROR');
4557 x_qa_result := okl_api.G_RET_STS_ERROR;
4558 END IF;
4559 IF(l_fees_count > 0 AND l_total_missing_pmts > 0 AND (l_total_cfl_count = 1)) THEN
4560 i:=x_qa_result_tbl.COUNT;
4561 i:=i+1;
4562 x_qa_result_tbl(i).check_code:='check_payments';
4563 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4564 x_qa_result_tbl(i).result_code:='ERROR';
4565 x_qa_result_tbl(i).result_meaning:='ERROR';
4566 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_CONFIG_PAY_ERROR';
4567 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_CONFIG_PAY_ERROR');
4568 x_qa_result := okl_api.G_RET_STS_ERROR;
4569 END IF;
4570
4571 /* IF(l_fees_count > 0 AND l_total_cfl_count <= 1 and l_total_missing_pmts <> 0 ) THEN
4572 i:=x_qa_result_tbl.COUNT;
4573 i:=i+1;
4574 x_qa_result_tbl(i).check_code:='check_payments';
4575 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_NOT_FOUND';
4576 x_qa_result_tbl(i).result_code:='ERROR';
4577 x_qa_result_tbl(i).result_meaning:='ERROR';
4578 x_qa_result_tbl(i).message_code:= 'OKL_QA_SM_CONFIG_PAY_ERROR';
4579 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_SM_CONFIG_PAY_ERROR');
4580 x_qa_result := okl_api.G_RET_STS_ERROR;
4581 END IF;*/
4582 END IF;
4583
4584 --Bug # 5070686 ssdeshpa end;
4585 -------------------------------------------------------------------------------
4586 --Any Payment should not have start date earlier than the Quote Expected Start Date
4587 select TRUNC(EXPECTED_START_DATE) INTO lp_cont_start_date
4588 from OKL_LEASE_QUOTES_B
4589 WHERE ID=lp_quote_id;
4590 ---Check for the VPA Effective Dates Fall within the Expected Start Date
4591 IF(lp_vp_id IS NOT NULL) THEN
4592 OPEN c_vp_rec(lp_vp_id ,lp_cont_start_date);
4593 FETCH c_vp_rec INTO x;
4594 CLOSE c_vp_rec;
4595 IF(nvl(x,'y') <>'x') THEN
4596 i:=x_qa_result_tbl.COUNT;
4597 i:=i+1;
4598 x_qa_result_tbl(i).check_code:='check_quote_values';
4599 x_qa_result_tbl(i).check_meaning:='VENDOR_PROGRAM_AGRREMENT_IS_INVALID';
4600 x_qa_result_tbl(i).result_code:='ERROR';
4601 x_qa_result_tbl(i).result_meaning:='ERROR';
4602 x_qa_result_tbl(i).message_code:= 'OKL_QA_VPA_INVALID_DATES';
4603 x_qa_result_tbl(i).message_text:= get_msg_text('OKL_QA_VPA_INVALID_DATES');
4604 x_qa_result := okl_api.G_RET_STS_ERROR;
4605 END IF;
4606 END IF;
4607 END IF;--End OF LQ
4608
4609 IF(p_object_type='QUICKQUOTE') THEN
4610 --Check Payment For SFFA,SFY and SFS Methods
4611 SELECT count(1) INTO l_cnt
4612 from OKL_QUICK_QUOTES_B
4613 WHERE ID=lp_quote_id
4614 AND PRICING_METHOD IN('SF','SS','SY');
4615 IF(l_cnt >= 1) THEN
4616 OPEN c_lq_cfl_line(lp_quote_id ,'QUICK_QUOTE');
4617 FETCH c_lq_cfl_line INTO lp_lq_cfl_line;
4618 WHILE c_lq_cfl_line%FOUND
4619 LOOP
4620 ---Clarify what is mean by Number;
4621 -- schodava: Modified If condition to cater to stub payments
4622 IF(lp_lq_cfl_line.amount IS NULL OR lp_lq_cfl_line.number_of_periods IS NULL)
4623 AND (lp_lq_cfl_line.stub_days IS NULL OR lp_lq_cfl_line.stub_amount IS NULL)THEN
4624 i:=x_qa_result_tbl.COUNT;
4625 i:=i+1;
4626 x_qa_result_tbl(i).check_code:='check_payments';
4627 x_qa_result_tbl(i).check_meaning:='MISSING_PAYMENT_LEVEL_FOR_QUOTE';
4628 x_qa_result_tbl(i).result_code:='ERROR';
4629 x_qa_result_tbl(i).result_meaning:='ERROR';
4630 x_qa_result_tbl(i).message_code:= 'OKL_QA_MISSING_PAY_LEVEL';
4631 x_qa_result_tbl(i).message_text:=get_msg_text('OKL_QA_MISSING_PAY_LEVEL');
4632 x_qa_result := okl_api.G_RET_STS_ERROR;
4633 EXIT;
4634 END IF;
4635 FETCH c_lq_cfl_line INTO lp_lq_cfl_line;
4636 END LOOP;
4637 CLOSE c_lq_cfl_line;
4638
4639 END IF;
4640
4641 SELECT TRUNC(EXPECTED_START_DATE) INTO lp_cont_start_date
4642 FROM OKL_QUICK_QUOTES_B
4643 WHERE ID=lp_quote_id;
4644
4645 ---Check for the VPA Effective Dates Fall within the Expected Start Date
4646 select TRUNC(EXPECTED_START_DATE),PROGRAM_AGREEMENT_ID INTO lp_cont_start_date,lp_vp_id
4647 from OKL_QUICK_QUOTES_B
4648 WHERE ID=lp_quote_id;
4649
4650 IF(lp_vp_id IS NOT NULL) THEN
4651 OPEN c_vp_rec(lp_vp_id ,lp_cont_start_date);
4652 FETCH c_vp_rec INTO x;
4653 CLOSE c_vp_rec;
4654 IF(nvl(x,'y') <>'x') THEN
4655 i:=x_qa_result_tbl.COUNT;
4656 i:=i+1;
4657 x_qa_result_tbl(i).check_code:='check_quote_values';
4658 x_qa_result_tbl(i).check_meaning:='VENDOR_PROGRAM_AGRREMENT_IS_INVALID';
4659 x_qa_result_tbl(i).result_code:='ERROR';
4660 x_qa_result_tbl(i).result_meaning:='ERROR';
4661 x_qa_result_tbl(i).message_code:= 'OKL_QA_VPA_INVALID_DATES';
4662 x_qa_result_tbl(i).message_text:= get_msg_text('OKL_QA_VPA_INVALID_DATES');
4663 x_qa_result := okl_api.G_RET_STS_ERROR;
4664 END IF;
4665 END IF;
4666
4667 END IF;--End OF QQ
4668 END IF;--Quote id Not null
4669
4670 okl_api.end_activity(x_msg_count => x_msg_count
4671 ,x_msg_data => x_msg_data);
4672
4673 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
4674 okl_debug_pub.log_debug(fnd_log.level_procedure
4675 ,l_module
4676 ,'end debug okl_sales_quote_qa_pvt call check_payment');
4677 END IF;
4678
4679 EXCEPTION
4680 WHEN okl_api.g_exception_error THEN
4681 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
4682 ,p_pkg_name =>G_PKG_NAME
4683 ,p_exc_name =>'OKL_API.G_RET_STS_ERROR'
4684 ,x_msg_count =>x_msg_count
4685 ,x_msg_data =>x_msg_data
4686 ,p_api_type =>G_API_TYPE);
4687 WHEN okl_api.g_exception_unexpected_error THEN
4688 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
4689 ,p_pkg_name =>G_PKG_NAME
4690 ,p_exc_name =>'OKL_API.G_RET_STS_UNEXP_ERROR'
4691 ,x_msg_count =>x_msg_count
4692 ,x_msg_data =>x_msg_data
4693 ,p_api_type =>G_API_TYPE);
4694 WHEN OTHERS THEN
4695 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
4696 ,p_pkg_name =>G_PKG_NAME
4697 ,p_exc_name =>'OTHERS'
4698 ,x_msg_count =>x_msg_count
4699 ,x_msg_data =>x_msg_data
4700 ,p_api_type =>G_API_TYPE);
4701 END check_payments;
4702 /*------------------------------------------------------------------------------
4703 -- PROCEDURE run_qa_checker
4704 ------------------------------------------------------------------------------
4705 -- Start of comments
4706 --
4707 -- Procedure Name : run_qa_checker
4708 -- Description : This procedure will be called as a wrapper by Lease
4709 Quote and Lease App For QA Validation run_qa_checker3
4710
4711 -- Business Rules : This procedure will validate the Lease Quote and Quick
4712 Quote for General ,Configuration, Pricing parameters
4713 --
4714 -- Parameters : p_object_id -- Lease Quote /Lease App Id.
4715
4716 p_object_type -- valid values are 'LEASEQUOTE'/'LEASEAPP'
4717 hold which type of object this method is calling
4718
4719 x_qa_result_tbl --> Hold all the QA Results for Object
4720
4721 -- Version : 1.1
4722 -- History :
4723 -- End of comments
4724 ------------------------------------------------------------------------------*/
4725 PROCEDURE run_qa_checker (p_api_version IN NUMBER
4726 ,p_init_msg_list IN VARCHAR2
4727 ,p_object_type IN VARCHAR2
4728 ,p_object_id IN NUMBER
4729 ,x_return_status OUT NOCOPY VARCHAR2
4730 ,x_msg_count OUT NOCOPY NUMBER
4731 ,x_msg_data OUT NOCOPY VARCHAR2
4732 ,x_qa_result_tbl OUT NOCOPY OKL_SALES_QUOTE_QA_PVT.qa_results_tbl_type) IS
4733 l_program_name CONSTANT VARCHAR2(30) := 'run_qa';
4734 l_api_name CONSTANT VARCHAR2(61) := l_program_name;
4735 i INTEGER;
4736 l_module CONSTANT fnd_log_messages.module%TYPE := 'SALE_QA_PVT';
4737 l_debug_enabled varchar2(10);
4738 is_debug_procedure_on boolean;
4739 is_debug_statement_on boolean;
4740
4741 l_qa_results_tbl qa_results_tbl_type;
4742 lp_quote_id NUMBER; --Fix for bug #4735811
4743 lp_object_type VARCHAR2(20);
4744 x_qa_result VARCHAR2(3);
4745 b_tax_call BOOLEAN := TRUE;--Added for bug # 5647107
4746 --Added Bug # 5647107 ssdeshpa start
4747 CURSOR l_systemparams_csr IS
4748 SELECT NVL(tax_upfront_yn,'N')
4749 FROM OKL_SYSTEM_PARAMS;
4750
4751 l_ou_tax_upfront_yn VARCHAR2(1);
4752 --Added Bug # 5647107 ssdeshpa end
4753
4754 CURSOR c_get_lq_rec(p_parent_object_id NUMBER,
4755 p_object_type VARCHAR2) IS
4756 SELECT OLQ.ID
4757 FROM OKL_LEASE_QUOTES_B OLQ
4758 WHERE PARENT_OBJECT_ID=p_parent_object_id
4759 AND PARENT_OBJECT_CODE=p_object_type
4760 AND PRIMARY_QUOTE='Y';
4761
4762 BEGIN
4763 l_debug_enabled := okl_debug_pub.check_log_enabled;
4764 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
4765 ,fnd_log.level_procedure);
4766
4767
4768 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
4769 okl_debug_pub.log_debug(fnd_log.level_procedure
4770 ,l_module
4771 ,'begin debug OKLRQQCB.pls call run_qa3');
4772 END IF; -- check for logging on STATEMENT level
4773 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
4774 ,fnd_log.level_statement);
4775 -- call START_ACTIVITY to create savepoint, check compatibility
4776 -- and initialize message list
4777
4778 x_return_status := okl_api.start_activity(p_api_name=>l_api_name
4779 ,p_pkg_name=>G_PKG_NAME
4780 ,p_init_msg_list=>p_init_msg_list
4781 ,p_api_version=>p_api_version
4782 ,l_api_version=>p_api_version
4783 ,p_api_type=>G_API_TYPE
4784 ,x_return_status=>x_return_status);
4785 -- check if activity started successfully
4786
4787 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4788 RAISE okl_api.g_exception_unexpected_error;
4789 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4790 RAISE okl_api.g_exception_error;
4791 END IF;
4792
4793 ---------------Check for Valid Parent Object type----------------------
4794
4795 --Fix Bug no 4735811 Start
4796 --Validation Tab Showing the Older values for Validation Results in case of
4797 --Lease Application.
4798 --Get Lease Quote Id for Lease Application which is havinf Primary Quote as
4799 -- 'Y'
4800 lp_object_type := p_object_type;
4801 lp_quote_id := p_object_id;
4802 IF(p_object_type='LEASEAPP' OR p_object_type='LEASEOPP') THEN
4803 OPEN c_get_lq_rec(p_parent_object_id => p_object_id,
4804 p_object_type => p_object_type);
4805 FETCH c_get_lq_rec INTO lp_quote_id;
4806 CLOSE c_get_lq_rec;
4807 lp_object_type :='LEASEQUOTE';
4808 END IF;
4809
4810 --Delete Validation Results
4811 DELETE OKL_VALIDATION_RESULTS_TL
4812 WHERE ID IN (SELECT ID
4813 FROM OKL_VALIDATION_RESULTS_B
4814 WHERE PARENT_OBJECT_CODE = lp_object_type
4815 AND PARENT_OBJECT_ID = lp_quote_id);
4816
4817 DELETE OKL_VALIDATION_RESULTS_B
4818 WHERE PARENT_OBJECT_CODE = lp_object_type
4819 AND PARENT_OBJECT_ID = lp_quote_id;
4820 --Fix Bug no 4735811 End
4821
4822 -------QA Checker Starts
4823 --Check System Validation Set Validations
4824 x_qa_result := OKL_API.G_RET_STS_SUCCESS;
4825 validate_system_validations(p_api_version => p_api_version,
4826 p_init_msg_list => G_FALSE,
4827 p_object_id => lp_quote_id,
4828 p_object_type => lp_object_type,
4829 x_return_status => x_return_status,
4830 x_msg_count => x_msg_count,
4831 x_msg_data => x_msg_data,
4832 x_qa_result_tbl => l_qa_results_tbl,
4833 x_qa_result => x_qa_result);
4834 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4835 RAISE okl_api.g_exception_unexpected_error;
4836 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4837 RAISE okl_api.g_exception_error;
4838 END IF;
4839 IF(x_qa_result= OKL_API.G_RET_STS_SUCCESS) THEN
4840 i:=l_qa_results_tbl.COUNT;
4841 i:=i+1;
4842 l_qa_results_tbl(i).check_code:='check_fees_and_services';
4843 l_qa_results_tbl(i).check_meaning:='check_fees_and_services';
4844 l_qa_results_tbl(i).result_code:='SUCCESS';
4845 l_qa_results_tbl(i).result_meaning:='Passed';
4846 l_qa_results_tbl(i).message_code:= 'OKL_QA_CHK_VLS';
4847 l_qa_results_tbl(i).message_text:= get_msg_text('OKL_QA_CHK_VLS');
4848 END IF;
4849 --Check misc/extended validations
4850 x_qa_result := OKL_API.G_RET_STS_SUCCESS;
4851 extended_validations(p_api_version => p_api_version,
4852 p_init_msg_list => G_FALSE,
4853 p_object_id => lp_quote_id,
4854 p_object_type => lp_object_type,
4855 x_return_status => x_return_status,
4856 x_msg_count => x_msg_count,
4857 x_msg_data => x_msg_data,
4858 x_qa_result_tbl => l_qa_results_tbl,
4859 x_qa_result => x_qa_result);
4860 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4861 RAISE okl_api.g_exception_unexpected_error;
4862 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4863 RAISE okl_api.g_exception_error;
4864 END IF;
4865 IF(x_qa_result = OKL_API.G_RET_STS_SUCCESS) THEN
4866 i:=l_qa_results_tbl.COUNT;
4867 i:=i+1;
4868 l_qa_results_tbl(i).check_code:='extended_validation';
4869 l_qa_results_tbl(i).check_meaning:='extended_validation';
4870 l_qa_results_tbl(i).result_code:='SUCCESS';
4871 l_qa_results_tbl(i).result_meaning:='Passed';
4872 l_qa_results_tbl(i).message_code:= 'OKL_QA_CHK_QUOTE_EXTN';
4873 l_qa_results_tbl(i).message_text:= get_msg_text('OKL_QA_CHK_QUOTE_EXTN');
4874 END IF;
4875 --check Quote Configuration
4876 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4877 check_configuration(p_api_version => p_api_version,
4878 p_init_msg_list => G_FALSE,
4879 p_object_id => lp_quote_id,
4880 p_object_type => lp_object_type,
4881 x_return_status => x_return_status,
4882 x_msg_count => x_msg_count,
4883 x_msg_data => x_msg_data,
4884 x_qa_result_tbl => l_qa_results_tbl,
4885 x_qa_result => x_qa_result);
4886
4887 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4888 RAISE okl_api.g_exception_unexpected_error;
4889 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4890 RAISE okl_api.g_exception_error;
4891 END IF;
4892
4893 IF(x_qa_result = OKL_API.G_RET_STS_SUCCESS) THEN
4894 i:=l_qa_results_tbl.COUNT;
4895 i:=i+1;
4896 l_qa_results_tbl(i).check_code:='check_configuration';
4897 l_qa_results_tbl(i).check_meaning:='check_configuration';
4898 l_qa_results_tbl(i).result_code:='SUCCESS';
4899 l_qa_results_tbl(i).result_meaning:='Passed';
4900 l_qa_results_tbl(i).message_code:= 'OKL_QA_CHK_QUOTE_CONFIG';
4901 l_qa_results_tbl(i).message_text:= get_msg_text('OKL_QA_CHK_QUOTE_CONFIG');
4902 END IF;
4903 --check fees/services for quote
4904 x_qa_result := OKL_API.G_RET_STS_SUCCESS;
4905 check_fees_and_services(p_api_version => p_api_version,
4906 p_init_msg_list => G_FALSE,
4907 p_object_id => lp_quote_id,
4908 p_object_type => lp_object_type,
4909 x_return_status => x_return_status,
4910 x_msg_count => x_msg_count,
4911 x_msg_data => x_msg_data,
4912 x_qa_result_tbl => l_qa_results_tbl,
4913 x_qa_result => x_qa_result);
4914
4915 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4916 RAISE okl_api.g_exception_unexpected_error;
4917 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4918 RAISE okl_api.g_exception_error;
4919 END IF;
4920
4921 IF(x_qa_result = OKL_API.G_RET_STS_SUCCESS) THEN
4922 i:=l_qa_results_tbl.COUNT;
4923 i:=i+1;
4924 l_qa_results_tbl(i).check_code:='check_fees_and_services';
4925 l_qa_results_tbl(i).check_meaning:='check_fees_and_services';
4926 l_qa_results_tbl(i).result_code:='SUCCESS';
4927 l_qa_results_tbl(i).result_meaning:='Passed';
4928 l_qa_results_tbl(i).message_code:= 'OKL_QA_CHK_FEE_SERVICE';
4929 l_qa_results_tbl(i).message_text:= get_msg_text('OKL_QA_CHK_FEE_SERVICE');
4930 END IF;
4931 --Check for cost adjustment for Quote
4932 x_qa_result := OKL_API.G_RET_STS_SUCCESS;
4933 validate_cost_adjustments(p_api_version => p_api_version,
4934 p_init_msg_list => G_FALSE,
4935 p_object_id => lp_quote_id,
4936 p_object_type => lp_object_type,
4937 x_return_status => x_return_status,
4938 x_msg_count => x_msg_count,
4939 x_msg_data => x_msg_data,
4940 x_qa_result_tbl => l_qa_results_tbl,
4941 x_qa_result => x_qa_result);
4942 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4943 RAISE okl_api.g_exception_unexpected_error;
4944 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4945 RAISE okl_api.g_exception_error;
4946 END IF;
4947 IF(x_qa_result = OKL_API.G_RET_STS_SUCCESS) THEN
4948 i:=l_qa_results_tbl.COUNT;
4949 i:=i+1;
4950 l_qa_results_tbl(i).check_code:='validate_cost_adjustments';
4951 l_qa_results_tbl(i).check_meaning:='validate_cost_adjustments';
4952 l_qa_results_tbl(i).result_code:='SUCCESS';
4953 l_qa_results_tbl(i).result_meaning:='Passed';
4954 l_qa_results_tbl(i).message_code:= 'OKL_QA_CHK_COST_ADJ';
4955 l_qa_results_tbl(i).message_text:= get_msg_text('OKL_QA_CHK_COST_ADJ');
4956 END IF;
4957 --check_payment_options for Quote
4958 x_qa_result := OKL_API.G_RET_STS_SUCCESS;
4959 check_payments(p_api_version => p_api_version,
4960 p_init_msg_list => G_FALSE,
4961 p_object_id => lp_quote_id,
4962 p_object_type => lp_object_type,
4963 x_return_status => x_return_status,
4964 x_msg_count => x_msg_count,
4965 x_msg_data => x_msg_data,
4966 x_qa_result_tbl => l_qa_results_tbl,
4967 x_qa_result => x_qa_result);
4968 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4969 RAISE okl_api.g_exception_unexpected_error;
4970 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4971 RAISE okl_api.g_exception_error;
4972 END IF;
4973 IF(x_qa_result = OKL_API.G_RET_STS_SUCCESS) THEN
4974 i:=l_qa_results_tbl.COUNT;
4975 i:=i+1;
4976 l_qa_results_tbl(i).check_code:='check_payments';
4977 l_qa_results_tbl(i).check_meaning:='check_payments';
4978 l_qa_results_tbl(i).result_code:='SUCCESS';
4979 l_qa_results_tbl(i).result_meaning:='Passed';
4980 l_qa_results_tbl(i).message_code:= 'OKL_QA_CHK_PAYMENT';
4981 l_qa_results_tbl(i).message_text:= get_msg_text('OKL_QA_CHK_PAYMENT');
4982 END IF;
4983 --Check Subsidies for Quote
4984 x_qa_result := OKL_API.G_RET_STS_SUCCESS;
4985 check_subsidies(p_api_version => p_api_version,
4986 p_init_msg_list => G_FALSE,
4987 p_object_id => lp_quote_id,
4988 p_object_type => lp_object_type,
4989 x_return_status => x_return_status,
4990 x_msg_count => x_msg_count,
4991 x_msg_data => x_msg_data,
4992 x_qa_result_tbl => l_qa_results_tbl,
4993 x_qa_result => x_qa_result);
4994 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4995 RAISE okl_api.g_exception_unexpected_error;
4996 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4997 RAISE okl_api.g_exception_error;
4998 END IF;
4999 IF(x_qa_result = OKL_API.G_RET_STS_SUCCESS) THEN
5000 i:=l_qa_results_tbl.COUNT;
5001 i:=i+1;
5002 l_qa_results_tbl(i).check_code:='check_subsidies';
5003 l_qa_results_tbl(i).check_meaning:='check_subsidies';
5004 l_qa_results_tbl(i).result_code:='SUCCESS';
5005 l_qa_results_tbl(i).result_meaning:='Passed';
5006 l_qa_results_tbl(i).message_code:= 'OKL_QA_CHK_SUBSIDY';
5007 l_qa_results_tbl(i).message_text:= get_msg_text('OKL_QA_CHK_SUBSIDY');
5008 END IF;
5009 --check financial Product for Quote
5010 x_qa_result := OKL_API.G_RET_STS_SUCCESS;
5011 validate_financial_product(p_api_version => p_api_version,
5012 p_init_msg_list => G_FALSE,
5013 p_object_id => lp_quote_id,
5014 p_object_type => lp_object_type,
5015 x_return_status => x_return_status,
5016 x_msg_count => x_msg_count,
5017 x_msg_data => x_msg_data,
5018 x_qa_result_tbl => l_qa_results_tbl,
5019 x_qa_result => x_qa_result);
5020 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5021 RAISE okl_api.g_exception_unexpected_error;
5022 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5023 RAISE okl_api.g_exception_error;
5024 END IF;
5025 IF(x_qa_result=OKL_API.G_RET_STS_SUCCESS) THEN
5026 i:=l_qa_results_tbl.COUNT;
5027 i:=i+1;
5028 l_qa_results_tbl(i).check_code:='validate_financial_product';
5029 l_qa_results_tbl(i).check_meaning:='validate_financial_product';
5030 l_qa_results_tbl(i).result_code:='SUCCESS';
5031 l_qa_results_tbl(i).result_meaning:='Passed';
5032 l_qa_results_tbl(i).message_code:= 'OKL_QA_CHK_FIN_PRODUCT';
5033 l_qa_results_tbl(i).message_text:= get_msg_text('OKL_QA_CHK_FIN_PRODUCT');
5034 END IF;
5035 --Check pricing values for Quote
5036 x_qa_result := OKL_API.G_RET_STS_SUCCESS;
5037 validate_pricing_values(p_api_version => p_api_version,
5038 p_init_msg_list => G_FALSE,
5039 p_object_id => lp_quote_id,
5040 p_object_type => lp_object_type,
5041 x_return_status => x_return_status,
5042 x_msg_count => x_msg_count,
5043 x_msg_data => x_msg_data,
5044 x_qa_result_tbl => l_qa_results_tbl,
5045 x_qa_result => x_qa_result);
5046 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5047 RAISE okl_api.g_exception_unexpected_error;
5048 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5049 RAISE okl_api.g_exception_error;
5050 END IF;
5051 IF(x_qa_result= OKL_API.G_RET_STS_SUCCESS) THEN
5052 i:=l_qa_results_tbl.COUNT;
5053 i:=i+1;
5054 l_qa_results_tbl(i).check_code:='validate_pricing_values';
5055 l_qa_results_tbl(i).check_meaning:='validate_pricing_values';
5056 l_qa_results_tbl(i).result_code:='SUCCESS';
5057 l_qa_results_tbl(i).result_meaning:='Passed';
5058 l_qa_results_tbl(i).message_code:= 'OKL_QA_CHK_PRICING';
5059 l_qa_results_tbl(i).message_text:= get_msg_text('OKL_QA_CHK_PRICING');
5060 END IF;
5061 --Check Eligibility Criteria for Quote Attrb such as LRS ,SRT,Product VPA
5062 x_qa_result := OKL_API.G_RET_STS_SUCCESS;
5063 validate_ec_criteria(p_api_version => p_api_version,
5064 p_init_msg_list => G_FALSE,
5065 p_object_id => lp_quote_id,
5066 p_object_type => lp_object_type,
5067 x_return_status => x_return_status,
5068 x_msg_count => x_msg_count,
5069 x_msg_data => x_msg_data,
5070 x_qa_result_tbl => l_qa_results_tbl,
5071 x_qa_result => x_qa_result);
5072 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5073 RAISE okl_api.g_exception_unexpected_error;
5074 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5075 RAISE okl_api.g_exception_error;
5076 END IF;
5077 IF(x_qa_result= OKL_API.G_RET_STS_SUCCESS) THEN
5078 i:=l_qa_results_tbl.COUNT;
5079 i:=i+1;
5080 l_qa_results_tbl(i).check_code:='validate_ec_criteria';
5081 l_qa_results_tbl(i).check_meaning:='validate_ec_criteria';
5082 l_qa_results_tbl(i).result_code:='SUCCESS';
5083 l_qa_results_tbl(i).result_meaning:='Passed';
5084 l_qa_results_tbl(i).message_code:= 'OKL_QA_CHK_EC';
5085 l_qa_results_tbl(i).message_text:= get_msg_text('OKL_QA_CHK_EC');
5086 END IF;
5087 x_qa_result_tbl := l_qa_results_tbl;
5088
5089 --Added ssdeshpa Bug #5647107 start
5090 FOR i IN x_qa_result_tbl.FIRST..x_qa_result_tbl.LAST LOOP
5091 IF(x_qa_result_tbl(i).result_code ='ERROR') THEN
5092 b_tax_call := false;
5093 exit;
5094 END IF;
5095 END LOOP;
5096
5097 IF(b_tax_call AND p_object_type <> 'QUICKQUOTE') THEN -- Fix for Bug 5908845
5098 OPEN l_systemparams_csr;
5099 FETCH l_systemparams_csr INTO l_ou_tax_upfront_yn;
5100 CLOSE l_systemparams_csr;
5101 IF(l_ou_tax_upfront_yn = 'Y') THEN
5102
5103 OKL_LEASE_QUOTE_PVT.calculate_sales_tax(p_api_version => p_api_version,
5104 p_init_msg_list => G_FALSE,
5105 x_return_status => x_return_status,
5106 x_msg_count => x_msg_count,
5107 x_msg_data => x_msg_data,
5108 p_transaction_control => 'T',
5109 p_quote_id => lp_quote_id);
5110
5111 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
5112 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5113 ELSIF x_return_status = G_RET_STS_ERROR THEN
5114 RAISE OKL_API.G_EXCEPTION_ERROR;
5115 END IF;
5116 END IF;
5117 END IF;
5118 --Added Bug # 5647107 ssdeshpa end
5119 x_return_status := OKL_API.G_RET_STS_SUCCESS;
5120
5121 okl_api.end_activity(x_msg_count => x_msg_count
5122 ,x_msg_data => x_msg_data);
5123 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
5124 okl_debug_pub.log_debug(fnd_log.level_procedure
5125 ,l_module
5126 ,'end debug okl_sales_quote_qa_pvt.run_qa3 call run_qa3');
5127 END IF;
5128 EXCEPTION
5129 WHEN okl_api.g_exception_error THEN
5130
5131 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
5132 ,p_pkg_name =>G_PKG_NAME
5133 ,p_exc_name =>'OKL_API.G_RET_STS_ERROR'
5134 ,x_msg_count =>x_msg_count
5135 ,x_msg_data =>x_msg_data
5136 ,p_api_type =>G_API_TYPE);
5137
5138 WHEN okl_api.g_exception_unexpected_error THEN
5139
5140 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
5141 ,p_pkg_name =>G_PKG_NAME
5142 ,p_exc_name =>'OKL_API.G_RET_STS_UNEXP_ERROR'
5143 ,x_msg_count =>x_msg_count
5144 ,x_msg_data =>x_msg_data
5145 ,p_api_type =>G_API_TYPE);
5146
5147 WHEN OTHERS THEN
5148
5149 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
5150 ,p_pkg_name =>G_PKG_NAME
5151 ,p_exc_name =>'OTHERS'
5152 ,x_msg_count =>x_msg_count
5153 ,x_msg_data =>x_msg_data
5154 ,p_api_type =>G_API_TYPE);
5155 END run_qa_checker;
5156
5157 /*------------------------------------------------------------------------------
5158 -- PROCEDURE run_qa_checker
5159 ------------------------------------------------------------------------------
5160 -- Start of comments
5161 --
5162 -- Procedure Name : run_qa_checker
5163 -- Description : This procedure will be called a by Quick Quote
5164 For QA Validation
5165
5166 -- Business Rules : This procedure will validate the Quick Quote for
5167 General ,Configuration, Pricing parameters
5168
5169 -- Parameters : p_object_id -- LQuick Quote Id
5170
5171 p_object_type -- valid values are 'QUICKQUOTE'
5172 hold which type of object this method is calling
5173
5174 x_qa_result_tbl --> Hold all the QA Results for Object
5175
5176 x_qa_result --'E'/'S' for Overall Success/Failure for QA
5177
5178 -- Version : 1.1
5179 -- History :
5180 -- End of comments
5181 -----------------------------------------------------------------------------*/
5182 --QA Checker API Called by the Quick Quote Validate Method
5183 PROCEDURE run_qa_checker(p_api_version IN NUMBER
5184 ,p_init_msg_list IN VARCHAR2
5185 ,p_object_type IN VARCHAR2
5186 ,p_object_id IN NUMBER
5187 ,x_return_status OUT NOCOPY VARCHAR2
5188 ,x_msg_count OUT NOCOPY NUMBER
5189 ,x_msg_data OUT NOCOPY VARCHAR2
5190 ,x_qa_result OUT NOCOPY VARCHAR2
5191 ,x_qa_result_tbl IN OUT NOCOPY OKL_SALES_QUOTE_QA_PVT.qa_results_tbl_type) IS
5192
5193
5194 l_program_name CONSTANT VARCHAR2(30) := 'run_qa_qq';
5195 l_api_name CONSTANT VARCHAR2(61) := l_program_name;
5196 i INTEGER;
5197 l_module CONSTANT fnd_log_messages.module%TYPE := 'OKL_SALE_QUOTE_QA_PVT.run_qa_checker2';
5198 l_debug_enabled varchar2(10);
5199 is_debug_procedure_on boolean;
5200 is_debug_statement_on boolean;
5201 BEGIN
5202 l_debug_enabled := okl_debug_pub.check_log_enabled;
5203 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
5204 ,fnd_log.level_procedure);
5205
5206
5207 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
5208 okl_debug_pub.log_debug(fnd_log.level_procedure
5209 ,l_module
5210 ,'begin debug OKLRQQCB.pls call run_qa_checker');
5211 END IF; -- check for logging on STATEMENT level
5212 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
5213 ,fnd_log.level_statement);
5214
5215 -- call START_ACTIVITY to create savepoint, check compatibility
5216 -- and initialize message list
5217
5218 x_return_status := okl_api.start_activity(p_api_name=>l_api_name
5219 ,p_pkg_name=>G_PKG_NAME
5220 ,p_init_msg_list=>p_init_msg_list
5221 ,p_api_version=>p_api_version
5222 ,l_api_version=>p_api_version
5223 ,p_api_type=>G_API_TYPE
5224 ,x_return_status=>x_return_status);
5225 -- check if activity started successfully
5226
5227 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5228 RAISE okl_api.g_exception_unexpected_error;
5229 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5230 RAISE okl_api.g_exception_error;
5231 END IF;
5232 ---------------Check for Valid Parent Object type----------------------
5233 IF(l_debug_enabled='Y') THEN
5234 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_SALES_QUOTE_QA_PVT.run_qa_checker'
5235 ,'begin debug call run_qa_checker');
5236 END IF;
5237 --Call wrapper run_qa_checker for QA Validations
5238 run_qa_checker (p_api_version => p_api_version
5239 ,p_init_msg_list => p_init_msg_list
5240 ,p_object_type => p_object_type
5241 ,p_object_id => p_object_id
5242 ,x_return_status => x_return_status
5243 ,x_msg_count => x_msg_count
5244 ,x_msg_data => x_msg_data
5245 ,x_qa_result_tbl => x_qa_result_tbl);
5246
5247 IF(l_debug_enabled='Y') THEN
5248 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_SALES_QUOTE_QA_PVT.run_qa_checker'
5249 ,'end debug call run_qa_checker');
5250 END IF;
5251 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5252 RAISE okl_api.g_exception_unexpected_error;
5253 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5254 RAISE okl_api.g_exception_error;
5255 END IF;
5256
5257 x_qa_result := okl_api.g_ret_sts_success;
5258 FOR i IN x_qa_result_tbl.FIRST..x_qa_result_tbl.LAST LOOP
5259 IF(x_qa_result_tbl(i).result_code ='ERROR') THEN
5260 x_qa_result := OKL_API.g_ret_sts_error;
5261 exit;
5262 END IF;
5263 END LOOP;
5264
5265 x_return_status := G_RET_STS_SUCCESS;
5266
5267
5268 okl_api.end_activity(x_msg_count => x_msg_count
5269 ,x_msg_data => x_msg_data);
5270 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
5271 okl_debug_pub.log_debug(fnd_log.level_procedure
5272 ,l_module
5273 ,'end debug okl_sales_quote_qa_pvt.run_qa2 call run_qa2');
5274 END IF;
5275
5276 EXCEPTION
5277 WHEN okl_api.g_exception_error THEN
5278
5279 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
5280 ,p_pkg_name =>G_PKG_NAME
5281 ,p_exc_name =>'OKL_API.G_RET_STS_ERROR'
5282 ,x_msg_count =>x_msg_count
5283 ,x_msg_data =>x_msg_data
5284 ,p_api_type =>G_API_TYPE);
5285
5286 WHEN okl_api.g_exception_unexpected_error THEN
5287
5288 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
5289 ,p_pkg_name =>G_PKG_NAME
5290 ,p_exc_name =>'OKL_API.G_RET_STS_UNEXP_ERROR'
5291 ,x_msg_count =>x_msg_count
5292 ,x_msg_data =>x_msg_data
5293 ,p_api_type =>G_API_TYPE);
5294
5295 WHEN OTHERS THEN
5296
5297 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
5298 ,p_pkg_name =>G_PKG_NAME
5299 ,p_exc_name =>'OTHERS'
5300 ,x_msg_count =>x_msg_count
5301 ,x_msg_data =>x_msg_data
5302 ,p_api_type =>G_API_TYPE);
5303
5304
5305 END run_qa_checker;
5306
5307 /*------------------------------------------------------------------------------
5308 -- PROCEDURE run_qa_checker
5309 --------------------------------------------------------------------------------
5310 -- Start of comments
5311 --
5312 -- Procedure Name : run_qa_checker
5313 -- Description : This procedure will be called a by Lease
5314 Quote and Lease App For QA Validation
5315
5316 -- Business Rules : This procedure will validate the Lease Quote and Quick
5317 Quote for General ,Configuration, Pricing parameters
5318
5319 -- Parameters : p_object_id -- Lease Quote Id
5320
5321 p_object_type -- valid values are 'LEASEQUOTE'
5322 hold which type of object this method is calling
5323
5324 x_qa_result --'E'/'S' for Overall Success/Failure for QA
5325
5326 -- Version : 1.1
5327 -- History :
5328 -- End of comments
5329 -----------------------------------------------------------------------------*/
5330 --QA Checker API Called by the Lease Quote Validate Method
5331
5332 PROCEDURE run_qa_checker (p_api_version IN NUMBER
5333 ,p_init_msg_list IN VARCHAR2
5334 ,p_object_type IN VARCHAR2
5335 ,p_object_id IN NUMBER
5336 ,x_qa_result OUT NOCOPY VARCHAR2
5337 ,x_return_status OUT NOCOPY VARCHAR2
5338 ,x_msg_count OUT NOCOPY NUMBER
5339 ,x_msg_data OUT NOCOPY VARCHAR2) IS
5340
5341 l_program_name CONSTANT VARCHAR2(30) := 'run_qa3';
5342 l_api_name CONSTANT VARCHAR2(61) := l_program_name;
5343 i INTEGER;
5344 l_module CONSTANT fnd_log_messages.module%TYPE := 'OKLRQQCB.pls.run_qa3';
5345 l_debug_enabled varchar2(10);
5346 is_debug_procedure_on boolean;
5347 is_debug_statement_on boolean;
5348 x_qa_result_tbl qa_results_tbl_type;
5349 lp_quote_id NUMBER;
5350
5351 CURSOR get_lq_rec(p_parent_object_id NUMBER,
5352 p_object_type VARCHAR2) IS
5353 SELECT OLQ.ID
5354 FROM OKL_LEASE_QUOTES_B OLQ
5355 WHERE PARENT_OBJECT_ID=p_parent_object_id
5356 AND PARENT_OBJECT_CODE=p_object_type
5357 AND PRIMARY_QUOTE='Y';
5358
5359 BEGIN
5360
5361 l_debug_enabled := okl_debug_pub.check_log_enabled;
5362 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
5363 ,fnd_log.level_procedure);
5364
5365
5366 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
5367 okl_debug_pub.log_debug(fnd_log.level_procedure
5368 ,l_module
5369 ,'begin debug OKLRQQCB.pls call run_qa3');
5370 END IF; -- check for logging on STATEMENT level
5371 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
5372 ,fnd_log.level_statement);
5373
5374 -- call START_ACTIVITY to create savepoint, check compatibility
5375 -- and initialize message list
5376
5377 x_return_status := okl_api.start_activity(p_api_name=>l_api_name
5378 ,p_pkg_name=>G_PKG_NAME
5379 ,p_init_msg_list=>p_init_msg_list
5380 ,p_api_version=>p_api_version
5381 ,l_api_version=>p_api_version
5382 ,p_api_type=>G_API_TYPE
5383 ,x_return_status=>x_return_status); -- check if activity started successfully
5384
5385 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5386 RAISE okl_api.g_exception_unexpected_error;
5387 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5388 RAISE okl_api.g_exception_error;
5389 END IF;
5390 --------------------------------------------------------------------------------
5391 --Call QA Checker To Test
5392 --Call the wrapper run_qa_checker
5393 run_qa_checker (p_api_version => p_api_version
5394 ,p_init_msg_list => p_init_msg_list
5395 ,p_object_type => p_object_type
5396 ,p_object_id => p_object_id
5397 ,x_return_status => x_return_status
5398 ,x_msg_count => x_msg_count
5399 ,x_msg_data => x_msg_data
5400 ,x_qa_result_tbl => x_qa_result_tbl);
5401 IF(l_debug_enabled='Y') THEN
5402 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_SALES_QUOTE_QA_PVT.run_qa_checker'
5403 ,'end debug call run_qa_checker');
5404 END IF;
5405 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5406 RAISE okl_api.g_exception_unexpected_error;
5407 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5408 RAISE okl_api.g_exception_error;
5409 END IF;
5410 --Lease Quote id treated as Primary Quote on Lease Application or
5411 --Lease Opportunity should go to the Database
5412 lp_quote_id := p_object_id;
5413 IF(p_object_type='LEASEAPP' OR p_object_type='LEASEOPP') THEN
5414 OPEN get_lq_rec(p_parent_object_id => p_object_id,
5415 p_object_type => p_object_type);
5416 FETCH get_lq_rec INTO lp_quote_id;
5417 CLOSE get_lq_rec;
5418 END IF;
5419
5420 --Populate the QA table Result into Database.
5421 IF(x_qa_result_tbl IS NOT NULL) THEN
5422 IF(x_qa_result_tbl.COUNT > 0) THEN
5423 populate_result_table(p_api_version => p_api_version,
5424 p_init_msg_list => G_FALSE,
5425 p_object_id => lp_quote_id,
5426 p_object_type => 'LEASEQUOTE',
5427 x_return_status => x_return_status,
5428 x_msg_count => x_msg_count,
5429 x_msg_data => x_msg_data,
5430 x_qa_result_tbl => x_qa_result_tbl);
5431
5432 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5433 RAISE okl_api.g_exception_unexpected_error;
5434 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5435 RAISE okl_api.g_exception_error;
5436 END IF;
5437 END IF;
5438 END IF;
5439
5440 x_qa_result := okl_api.g_ret_sts_success;
5441 --See if Table contains any error
5442 FOR i IN x_qa_result_tbl.FIRST..x_qa_result_tbl.LAST LOOP
5443
5444 IF(x_qa_result_tbl.exists(i))THEN
5445 IF(x_qa_result_tbl(i).result_code ='ERROR') THEN
5446 x_qa_result := OKL_API.g_ret_sts_error;
5447 EXIT;
5448 END IF;
5449 END IF;
5450 END LOOP;
5451
5452 x_return_status := G_RET_STS_SUCCESS;
5453
5454 okl_api.end_activity(x_msg_count => x_msg_count
5455 ,x_msg_data => x_msg_data);
5456
5457 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
5458 okl_debug_pub.log_debug(fnd_log.level_procedure
5459 ,l_module
5460 ,'end debug okl_sales_quote_qa_pvt call run_qa3');
5461 END IF;
5462
5463 EXCEPTION
5464 WHEN okl_api.g_exception_error THEN
5465
5466 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
5467 ,p_pkg_name =>G_PKG_NAME
5468 ,p_exc_name =>'OKL_API.G_RET_STS_ERROR'
5469 ,x_msg_count =>x_msg_count
5470 ,x_msg_data =>x_msg_data
5471 ,p_api_type =>G_API_TYPE);
5472
5473 WHEN okl_api.g_exception_unexpected_error THEN
5474
5475 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
5476 ,p_pkg_name =>G_PKG_NAME
5477 ,p_exc_name =>'OKL_API.G_RET_STS_UNEXP_ERROR'
5478 ,x_msg_count =>x_msg_count
5479 ,x_msg_data =>x_msg_data
5480 ,p_api_type =>G_API_TYPE);
5481
5482 WHEN OTHERS THEN
5483
5484 x_return_status := okl_api.handle_exceptions(p_api_name =>l_api_name
5485 ,p_pkg_name =>G_PKG_NAME
5486 ,p_exc_name =>'OTHERS'
5487 ,x_msg_count =>x_msg_count
5488 ,x_msg_data =>x_msg_data
5489 ,p_api_type =>G_API_TYPE);
5490 END run_qa_checker;
5491 -------------------------------------------------------------------------------
5492 END OKL_SALES_QUOTE_QA_PVT;