[Home] [Help]
PACKAGE BODY: APPS.OKL_EC_UPTAKE_PVT
Source
1 PACKAGE BODY OKL_EC_UPTAKE_PVT AS
2 /* $Header: OKLRECXB.pls 120.25 2006/09/26 07:24:23 varangan noship $*/
3 ---------------------------------------------------------------------------
4 --Added by ssdeshpa to have EC uptakes on LRs,SRT of Lease Quote
5 ---------------------------------------------------------------------------
6
7
8 PROCEDURE populate_lq_attributes(l_okl_ec_rec_type IN OUT NOCOPY okl_ec_evaluate_pvt.okl_ec_rec_type ,
9 p_target_id number
10 ,x_return_status OUT NOCOPY VARCHAR2) IS
11
12 i INTEGER;
13 l_msg_count NUMBER;
14 l_module CONSTANT fnd_log_messages.module%TYPE := 'ECUPTAKE';
15 l_debug_enabled varchar2(10);
16 is_debug_procedure_on boolean;
17 is_debug_statement_on boolean;
18 x_msg_data VARCHAR2(2000);
19 l_api_version CONSTANT number := 1.0;
20 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
21 l_program_name CONSTANT VARCHAR2(30) := 'populate';
22 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
23 l_init_msg_list VARCHAR2(3);
24 l_parent_object_id NUMBER;
25 l_parent_object_code VARCHAR2(30);
26 l_expected_start_date DATE;
27 l_term NUMBER;
28 l_deal_size NUMBER;
29 l_adj_amount NUMBER;
30
31 CURSOR c_lq_rec(p_lease_quote_id NUMBER) IS
32 SELECT PARENT_OBJECT_ID,PARENT_OBJECT_CODE,EXPECTED_START_DATE,TERM
33 FROM OKL_LEASE_QUOTES_V
34 where id=p_lease_quote_id;
35
36 CURSOR c_lop_rec(p_parent_object_id NUMBER) IS
37 select lop.id,
38 lop.reference_number,
39 lop.prospect_id,
40 lop.prospect_address_id,
41 lop.cust_acct_id,
42 OKL_LEASE_APP_PVT.get_credit_classfication(
43 lop.prospect_id,
44 lop.cust_acct_id,
45 NULL) as customer_credit_class,
46 lop.sales_rep_id,
47 lop.sales_territory_id,
48 lop.currency_code
49 from okl_lease_opportunities_v lop
50 where lop.id=p_parent_object_id;
51
52 l_lop_rec c_lop_rec%ROWTYPE;
53
54 CURSOR c_lapp_rec(p_parent_object_id NUMBER) IS
55 select lapp.id,
56 lapp.reference_number,
57 lapp.prospect_id,
58 lapp.prospect_address_id,
59 lapp.cust_acct_id,
60 OKL_LEASE_APP_PVT.get_credit_classfication(
61 lapp.prospect_id,
62 lapp.cust_acct_id,
63 NULL) as customer_credit_class,
64 lapp.sales_rep_id,
65 lapp.sales_territory_id,
66 lapp.currency_code
67 from okl_lease_applications_v lapp
68 where lapp.id=p_parent_object_id;
69
70 l_lapp_rec c_lapp_rec%ROWTYPE;
71 --Bug 5045505 ssdeshpa start
72 --Added Cursors to get Deal Size of LQ
73 CURSOR c_deal_size_cur(p_parent_object_id NUMBER) IS
74 select SUM(OEC)
75 FROM OKL_LEASE_QUOTES_B OLQ,OKL_ASSETS_B OAB
76 where OAB.PARENT_OBJECT_ID = OLQ.ID
77 AND OAB.PARENT_OBJECT_CODE='LEASEQUOTE'
78 AND OLQ.ID= p_parent_object_id;
79
80 cursor c_lq_cost_adj_rec(p_quote_id NUMBER,p_adj_type VARCHAR2) IS
81 SELECT SUM(NVL(VALUE,0))
82 FROM OKL_COST_ADJUSTMENTS_B OCA,
83 OKL_ASSETS_B OAB
84 where OAB.PARENT_OBJECT_CODE = 'LEASEQUOTE'
85 AND OCA.PARENT_OBJECT_CODE='ASSET'
86 AND OCA.PARENT_OBJECT_ID=OAB.ID
87 and ADJUSTMENT_SOURCE_TYPE =p_adj_type
88 AND OAB.PARENT_OBJECT_ID = p_quote_id;
89
90 CURSOR c_cost_comp_cur(p_quote_id NUMBER) IS
91 select OAC.INV_ITEM_ID
92 from OKL_ASSET_COMPONENTS_B OAC,
93 OKL_ASSETS_B OAB
94 WHERE OAC.ASSET_ID = OAB.ID
95 AND OAB.PARENT_OBJECT_CODE = 'LEASEQUOTE'
96 AND PRIMARY_COMPONENT='YES'
97 AND OAB.PARENT_OBJECT_ID = p_quote_id;
98 --Bug 5045505 ssdeshpa start
99 BEGIN
100 l_debug_enabled := okl_debug_pub.check_log_enabled;
101 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
102 ,fnd_log.level_procedure);
103
104
105 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
106 okl_debug_pub.log_debug(fnd_log.level_procedure
107 ,l_module
108 ,'begin debug OKLRECXB.pls call populate_lq_attributes');
109 END IF; -- check for logging on STATEMENT level
110 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
111 ,fnd_log.level_statement);
112 -- call START_ACTIVITY to create savepoint, check compatibility
113 -- and initialize message list
114 l_return_status := okl_api.start_activity(p_api_name => l_api_name
115 ,p_pkg_name => G_PKG_NAME
116 ,p_init_msg_list => l_init_msg_list
117 ,l_api_version => l_api_version
118 ,p_api_version => l_api_version
119 ,p_api_type => G_API_TYPE
120 ,x_return_status => x_return_status); -- check if activity started successfully
121
122 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
123 RAISE okl_api.g_exception_unexpected_error;
124 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
125 RAISE okl_api.g_exception_error;
126 END IF;
127
128 OPEN c_lq_rec(p_target_id);
129 FETCH c_lq_rec INTO l_parent_object_id,l_parent_object_code,l_expected_start_date,l_term;
130 CLOSE c_lq_rec;
131 l_okl_ec_rec_type.target_eff_from:=l_expected_start_date;
132 l_okl_ec_rec_type.term:=l_term;
133 --Bug 5045505 ssdeshpa start
134 --Get Total Down Payment For Quote
135 OPEN c_lq_cost_adj_rec(p_target_id,'DOWN_PAYMENT');
136 FETCH c_lq_cost_adj_rec INTO l_adj_amount;
137 CLOSE c_lq_cost_adj_rec;
138 l_okl_ec_rec_type.down_payment := l_adj_amount;
139 --Get Total Down Payment For Quote
140
141 --Get Total Trade In For Quote
142 OPEN c_lq_cost_adj_rec(p_target_id,'TRADEIN');
143 FETCH c_lq_cost_adj_rec INTO l_adj_amount;
144 CLOSE c_lq_cost_adj_rec;
145 l_okl_ec_rec_type.trade_in_value := l_adj_amount;
146 --Get Total Trade In For Quote
147
148 --Get Item Tables for LQ
149 i := 1;
150 FOR l_cost_comp_rec IN c_cost_comp_cur(p_target_id) LOOP
151 l_okl_ec_rec_type.item_table(i) := l_cost_comp_rec.INV_ITEM_ID;
152 i := i + 1;
153 END LOOP;
154
155 --End Get Items Table For LQ
156 --Get Deal Size For LQ
157 OPEN c_deal_size_cur(p_target_id);
158 FETCH c_deal_size_cur INTO l_deal_size;
159 CLOSE c_deal_size_cur;
160 l_okl_ec_rec_type.deal_size:=l_deal_size;
161 --Bug 5045505 ssdeshpa end
162 if(l_parent_object_code='LEASEOPP') THEN
163
164 OPEN c_lop_rec(l_parent_object_id);
165 FETCH c_lop_rec INTO l_lop_rec;
166 l_okl_ec_rec_type.territory:= l_lop_rec.sales_territory_id;
167 l_okl_ec_rec_type.customer_credit_class:= l_lop_rec.customer_credit_class;
168 l_okl_ec_rec_type.currency_code := l_lop_rec.currency_code;
169 CLOSE c_lop_rec;
170 ELSIF(l_parent_object_code='LEASEAPP') THEN
171 OPEN c_lapp_rec(l_parent_object_id);
172 FETCH c_lapp_rec INTO l_lapp_rec;
173 l_okl_ec_rec_type.territory:= l_lapp_rec.sales_territory_id;
174 l_okl_ec_rec_type.customer_credit_class:= l_lapp_rec.customer_credit_class;
175 l_okl_ec_rec_type.currency_code := l_lapp_rec.currency_code;
176 CLOSE c_lapp_rec;
177 END IF;
178
179 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
180 okl_debug_pub.log_debug(fnd_log.level_statement
181 ,l_module
182 ,'okl_ec_uptake_pvt.populate_lq_attributes returned with status ' ||
183 l_return_status ||
184 ' x_msg_data ' ||
185 x_msg_data);
186 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
187
188 IF (l_return_status = okl_api.g_ret_sts_error) THEN
189 RAISE okl_api.g_exception_error;
190 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
191 RAISE okl_api.g_exception_unexpected_error;
192 END IF;
193 okl_api.end_activity(x_msg_count => l_msg_count
194 ,x_msg_data => x_msg_data);
195
196 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
197 okl_debug_pub.log_debug(fnd_log.level_procedure
198 ,l_module
199 ,'end debug OKL_EC_UPTAKE_PVT.pls call populate_lq_attributes');
200 END IF;
201 EXCEPTION
202 WHEN okl_api.g_exception_error THEN
203
204 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
205 ,p_pkg_name => G_PKG_NAME
206 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
207 ,x_msg_count => l_msg_count
208 ,x_msg_data => x_msg_data
209 ,p_api_type => G_API_TYPE);
210
211 WHEN okl_api.g_exception_unexpected_error THEN
212
213 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
214 ,p_pkg_name => G_PKG_NAME
215 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
216 ,x_msg_count => l_msg_count
217 ,x_msg_data => x_msg_data
218 ,p_api_type => G_API_TYPE);
219
220 WHEN OTHERS THEN
221
222 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
223 ,p_pkg_name => G_PKG_NAME
224 ,p_exc_name => 'OTHERS'
225 ,x_msg_count => l_msg_count
226 ,x_msg_data => x_msg_data
227 ,p_api_type => G_API_TYPE);
228
229 END populate_lq_attributes;
230
231
232 FUNCTION get_vp_id(p_target_id number) RETURN NUMBER IS
233
234 l_vendor_prog_id NUMBER;
235 l_module CONSTANT fnd_log_messages.module%TYPE := 'lrs';
236 l_debug_enabled varchar2(10);
237 is_debug_procedure_on boolean;
238 is_debug_statement_on boolean;
239 l_api_version CONSTANT number := 1.0;
240 l_program_name CONSTANT VARCHAR2(30) := 'get_vp_id';
241 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
242 l_parent_object_id NUMBER;
243 l_parent_object_code VARCHAR2(30);
244
245 CURSOR c_lq_rec(p_lease_quote_id NUMBER) IS
246 SELECT PARENT_OBJECT_ID,PARENT_OBJECT_CODE
247 FROM OKL_LEASE_QUOTES_b
248 where id=p_lease_quote_id;
249 CURSOR c_lop_rec(p_object_id NUMBER) IS
250 select PROGRAM_AGREEMENT_ID
251 from okl_lease_opportunities_b
252 where id=p_object_id;
253 CURSOR c_lap_rec(p_object_id NUMBER) IS
254 select PROGRAM_AGREEMENT_ID
255 from okl_lease_applications_b
256 where id=p_object_id;
257
258 BEGIN
259 OPEN c_lq_rec(p_target_id);
260 FETCH c_lq_rec INTO l_parent_object_id,l_parent_object_code;
261 CLOSE c_lq_rec;
262 IF(l_parent_object_id IS NOT NULL AND l_parent_object_code IS NOT NULL) THEN
263 if(l_parent_object_code='LEASEOPP') THEN
264 OPEN c_lop_rec(l_parent_object_id);
265 FETCH c_lop_rec INTO l_vendor_prog_id;
266 CLOSE c_lop_rec;
267 ELSIF(l_parent_object_code='LEASEAPP') THEN
268 OPEN c_lap_rec(l_parent_object_id);
269 FETCH c_lap_rec INTO l_vendor_prog_id;
270 CLOSE c_lap_rec;
271 END IF;
272 END IF;
273 return l_vendor_prog_id;
274 EXCEPTION
275 WHEN OTHERS THEN
276 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
277 p_msg_name => G_DB_ERROR,
278 p_token1 => G_PROG_NAME_TOKEN,
279 p_token1_value => l_api_name,
280 p_token2 => G_SQLCODE_TOKEN,
281 p_token2_value => sqlcode,
282 p_token3 => G_SQLERRM_TOKEN,
283 p_token3_value => sqlerrm);
284 END get_vp_id;
285 -------------------------------------------------------------------------------
286 --Populate Lease Rate Set For Quick Quote
287 --------------------------------------------------------------------------------
288 PROCEDURE populate_lease_rate_set(p_api_version IN NUMBER,
289 p_init_msg_list IN VARCHAR2,
290 p_target_id IN NUMBER,
291 p_target_type VARCHAR2,
292 p_target_eff_from DATE,
293 p_term NUMBER,
294 p_territory VARCHAR2,
295 p_deal_size NUMBER,
296 p_customer_credit_class VARCHAR2,
297 p_down_payment NUMBER,
298 p_advance_rent NUMBER,
299 p_trade_in_value NUMBER,
300 --Bug # 5045505 ssdeshpa start
301 p_currency_code VARCHAR2,
302 --Bug # 5045505 ssdeshpa End
303 p_item_table okl_number_table_type,
304 p_item_categories_table okl_number_table_type,
305 x_okl_lrs_table OUT NOCOPY okl_lease_rate_set_tbl_type,
306 x_return_status OUT NOCOPY VARCHAR2,
307 x_msg_count OUT NOCOPY NUMBER,
308 x_msg_data OUT NOCOPY VARCHAR2) IS
309
310 --l_return_status VARCHAR2(1);
311 l_program_name CONSTANT VARCHAR2(30) := 'p_lrs';
312 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
313 l_okl_lrs_rec okl_lease_rate_set_rec_type;
314 l_okl_ec_rec_type okl_ec_evaluate_pvt.okl_ec_rec_type;
315 l_okl_lrs_table okl_lease_rate_set_tbl_type;
316 i INTEGER;
317 l_module CONSTANT fnd_log_messages.module%TYPE := 'lrs1';
318 l_debug_enabled varchar2(10);
319 is_debug_procedure_on boolean;
320 is_debug_statement_on boolean;
321 l_api_version CONSTANT number := 1.0;
322 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
323 x_eligible boolean;
324 l_validation_mode VARCHAR2(30);
325 --Bug # 5045505 ssdeshpa Start
326 --Modified Cursor in order to filtered the LRS on currency Code
327 CURSOR c_lrs_rec(effective_start_date DATE,
328 p_currency_code VARCHAR2) IS
329 select lrs.id,
330 lrv.rate_set_version_id,
331 lrv.version_number,
332 lrs.name,
333 lrs.description,
334 lrv.effective_from_date effective_from,
335 lrv.effective_to_date effective_to,
336 --Bug # 5050143 start
337 nvl(lrv.lrs_rate,lrv.standard_rate) lrs_rate,
338 --Bug # 5050143 End
339 lrv.sts_code,
340 lrs.frq_code
341 from okl_ls_rt_fctr_sets_v lrs,okl_fe_rate_set_versions_v lrv
342 where effective_start_date between lrv.effective_from_date and NVL(lrv.effective_to_date,effective_start_date+1)
343 and lrs.id=lrv.rate_set_id
344 and lrv.sts_code='ACTIVE'
345 and lrs.lrs_type_code='LEVEL'
346 AND lrs.currency_code = p_currency_code;
347 -- Bug # 5045505 ssdeshpa End;
348
349 BEGIN
350 l_debug_enabled := okl_debug_pub.check_log_enabled;
351 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
352 ,fnd_log.level_procedure);
353
354
355 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
356 okl_debug_pub.log_debug(fnd_log.level_procedure
357 ,l_module
358 ,'begin debug OKLRECXB.pls call populate_lrs1');
359 END IF; -- check for logging on STATEMENT level
360 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
361 ,fnd_log.level_statement);
362
363 -- call START_ACTIVITY to create savepoint, check compatibility
364 -- and initialize message list
365
366 l_return_status := okl_api.start_activity(p_api_name => l_api_name
367 ,p_pkg_name => G_PKG_NAME
368 ,p_init_msg_list => p_init_msg_list
369 ,l_api_version => l_api_version
370 ,p_api_version => p_api_version
371 ,p_api_type => G_API_TYPE
372 ,x_return_status => x_return_status); -- check if activity started successfully
373
374 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
375 RAISE okl_api.g_exception_unexpected_error;
376 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
377 RAISE okl_api.g_exception_error;
378 END IF;
379 --Populate the Record Structure for the OKL_ECC_PUB.validate method
380 --l_okl_ec_rec_type.target
381 l_okl_ec_rec_type.src_type:='LRS';
382 l_okl_ec_rec_type.target_id:= p_target_id;
383 l_okl_ec_rec_type.target_type:= p_target_type;
384 l_okl_ec_rec_type.target_eff_from:= P_target_eff_from;
385 l_okl_ec_rec_type.term:= p_term;
386 l_okl_ec_rec_type.territory:= p_territory;
387 l_okl_ec_rec_type.deal_size:= p_deal_size;
388 l_okl_ec_rec_type.customer_credit_class:= p_customer_credit_class;
389 l_okl_ec_rec_type.down_payment:= p_down_payment;
390 l_okl_ec_rec_type.advance_rent:= p_advance_rent;
391 l_okl_ec_rec_type.trade_in_value:= p_trade_in_value;
392 l_okl_ec_rec_type.validation_mode:= 'LOV';
393 --Bug # 5045505 ssdeshpa start
394 l_okl_ec_rec_type.currency_code := p_currency_code;
395 --Bug # 5045505 ssdeshpa End
396 --Bug # 5050143 ssdeshpa start
397 FOR i IN p_item_categories_table.FIRST..p_item_categories_table.LAST LOOP
398 IF p_item_categories_table.EXISTS(i) THEN
399 l_okl_ec_rec_type.item_categories_table(i) := p_item_categories_table(i);
400 END IF;
401 END LOOP;
402 --Bug # 5050143 ssdeshpa end
403 i:=1;
404 FOR l_okl_lrs_rec IN c_lrs_rec(p_target_eff_from,p_currency_code)LOOP
405 l_okl_ec_rec_type.src_id:=l_okl_lrs_rec.rate_set_version_id;
406 l_okl_ec_rec_type.source_name:=l_okl_lrs_rec.name;
407
408 OKL_ECC_PUB.evaluate_eligibility_criteria(p_api_version
409 ,p_init_msg_list
410 ,x_return_status
411 ,x_msg_count
412 ,x_msg_data
413 ,l_okl_ec_rec_type
414 ,x_eligible);
415
416 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
417 RAISE okl_api.g_exception_unexpected_error;
418 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
419 RAISE okl_api.g_exception_error;
420 END IF;
421 IF(x_eligible) THEN
422 l_okl_lrs_table(i).id:=l_okl_lrs_rec.id;
423 l_okl_lrs_table(i).name:=l_okl_lrs_rec.name;
424 l_okl_lrs_table(i).rate_set_version_id:=l_okl_lrs_rec.rate_set_version_id;
425 l_okl_lrs_table(i).version_number:=l_okl_lrs_rec.version_number;
426 l_okl_lrs_table(i).description:=l_okl_lrs_rec.description;
427 l_okl_lrs_table(i).effective_from:=l_okl_lrs_rec.effective_from;
428 l_okl_lrs_table(i).effective_to:=l_okl_lrs_rec.effective_to;
429 l_okl_lrs_table(i).lrs_rate:=l_okl_lrs_rec.lrs_rate;
430 l_okl_lrs_table(i).sts_code:=l_okl_lrs_rec.sts_code;
431 l_okl_lrs_table(i).frq_code:=l_okl_lrs_rec.frq_code;
432 l_okl_lrs_table(i).frq_meaning:= OKL_ACCOUNTING_UTIL.get_lookup_meaning('OKL_FREQUENCY',l_okl_lrs_rec.frq_code);
433 i:=i+1;
434 END IF;
435 END LOOP;
436
437 x_okl_lrs_table:=l_okl_lrs_table;
438
439 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
440 okl_debug_pub.log_debug(fnd_log.level_statement
441 ,l_module
442 ,'okl_ec_uptake_pvt.populate_lease_rate_set returned with status ' ||
443 l_return_status ||
444 ' x_msg_data ' ||
445 x_msg_data);
446 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
447
448 IF (l_return_status = okl_api.g_ret_sts_error) THEN
449 RAISE okl_api.g_exception_error;
450 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
451 RAISE okl_api.g_exception_unexpected_error;
452 END IF; --Copy value of OUT variable in the IN rvldrd type
453 okl_api.end_activity(x_msg_count => x_msg_count
454 ,x_msg_data => x_msg_data);
455
456 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
457 okl_debug_pub.log_debug(fnd_log.level_procedure
458 ,l_module
459 ,'end debug OKL_EC_UPTAKE_PVT.pls call populate_lease_rate_set');
460 END IF;
461 EXCEPTION
462
463 WHEN okl_api.g_exception_error THEN
464
465 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
466 ,p_pkg_name => G_PKG_NAME
467 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
468 ,x_msg_count => x_msg_count
469 ,x_msg_data => x_msg_data
470 ,p_api_type => G_API_TYPE);
471
472 WHEN okl_api.g_exception_unexpected_error THEN
473
474 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
475 ,p_pkg_name => G_PKG_NAME
476 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
477 ,x_msg_count => x_msg_count
478 ,x_msg_data => x_msg_data
479 ,p_api_type => G_API_TYPE);
480
481 WHEN OTHERS THEN
482
483 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
484 ,p_pkg_name => G_PKG_NAME
485 ,p_exc_name => 'OTHERS'
486 ,x_msg_count => x_msg_count
487 ,x_msg_data => x_msg_data
488 ,p_api_type => G_API_TYPE);
489 END populate_lease_rate_set;
490
491
492 -------------------------------------------------------------------------------
493 --Populate Standard Rate Template For Quick Quote
494 --------------------------------------------------------------------------------
495 PROCEDURE populate_std_rate_tmpl(p_api_version IN NUMBER,
496 p_init_msg_list IN VARCHAR2,
497 p_target_id IN NUMBER,
498 p_target_type VARCHAR2,
499 P_target_eff_from DATE,
500 p_term NUMBER,
501 p_territory VARCHAR2,
502 p_deal_size NUMBER,
503 p_customer_credit_class VARCHAR2,
504 p_down_payment NUMBER,
505 p_advance_rent NUMBER,
506 p_trade_in_value NUMBER,
507 --Bug # 5045505 ssdeshpa start
508 p_currency_code VARCHAR2,
509 --Bug # 5045505 ssdeshpa End
510 p_item_table okl_number_table_type,
511 p_item_categories_table okl_number_table_type,
512 x_okl_srt_table OUT NOCOPY okl_std_rate_tmpl_tbl_type,
513 x_return_status OUT NOCOPY VARCHAR2,
514 x_msg_count OUT NOCOPY NUMBER,
515 x_msg_data OUT NOCOPY VARCHAR2) IS
516
517 l_program_name CONSTANT VARCHAR2(30) := 'p_srt';
518 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
519 l_okl_srt_rec okl_std_rate_tmpl_rec_type;
520 l_okl_ec_rec_type okl_ec_evaluate_pvt.okl_ec_rec_type;
521 l_okl_srt_table okl_std_rate_tmpl_tbl_type;
522 l_api_version CONSTANT number := 1.0;
523 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
524 l_module CONSTANT fnd_log_messages.module%TYPE := 'lrs';
525 l_debug_enabled varchar2(10);
526 is_debug_procedure_on boolean;
527 is_debug_statement_on boolean;
528 i INTEGER;
529 x_eligible boolean;
530 l_ac_rec_type okl_ec_evaluate_pvt.okl_ac_rec_type;
531 l_adj_factor NUMBER;
532 l_srt_effective_rate NUMBER;
533
534 -- SCHODAVA Bug #4747677
535 -- Modified cursor to pick up SRTs which are created
536 -- with Rate_Card_Yn flag as No.
537 --Fixed # 5047718 ssdeshpa Start
538 --Modified SRT selection To include Valid Index Rate Type SRT
539 --Bug # 5045505 ssdeshpa start
540 --Modified To inlude more Attribute Selection
541 --Issue # 9 Modified to Filter SRT on the Basis of Currency Code Passed
542 CURSOR c_srt_rec(effective_start_date DATE,
543 p_currency_code VARCHAR2) IS
544 select srt.std_rate_tmpl_id as id,
545 srv.std_rate_tmpl_ver_id,
546 srv.version_number,
547 srt.template_name as name,
548 srt.template_desc as description,
549 srt.frequency_code as frq_code,
550 srv.effective_from_date effective_from,
551 srv.effective_to_date effective_to,
552 srv.srt_rate,
553 srv.sts_code,
554 srv.day_convention_code,
555 ----------
556 srt.pricing_engine_code pricing_engine_code,
557 srt.rate_type_code rate_type_code,
558 srt.index_id index_id,
559 srv.spread spread,
560 srt.frequency_code frequency_code,
561 srv.adj_mat_version_id adj_mat_version_id,
562 srv.max_adj_rate,
563 srv.min_adj_rate
564 ---
565 from
566 okl_fe_std_rt_tmp_v srt,
567 okl_fe_std_rt_tmp_vers srv
568 where
569 effective_start_date between srv.effective_from_date and NVL(srv.effective_to_date,effective_start_date+1)
570 AND srt.std_rate_tmpl_id=srv.std_rate_tmpl_id
571 AND srv.sts_code='ACTIVE'
572 AND srt.rate_card_yn = 'N'
573 AND srt.RATE_TYPE_CODE = 'BASE_RATE'
574 AND srt.currency_code = p_currency_code
575 UNION
576 select srt.std_rate_tmpl_id as id,
577 srv.std_rate_tmpl_ver_id,
578 srv.version_number,
579 srt.template_name as name,
580 srt.template_desc as description,
581 srt.frequency_code as frq_code,
582 srv.effective_from_date effective_from,
583 srv.effective_to_date effective_to,
584 srv.srt_rate,
585 srv.sts_code,
586 srv.day_convention_code,
587 ----------
588 srt.pricing_engine_code pricing_engine_code,
589 srt.rate_type_code rate_type_code,
590 srt.index_id index_id,
591 srv.spread spread,
592 srt.frequency_code frequency_code,
593 srv.adj_mat_version_id adj_mat_version_id,
594 srv.max_adj_rate,
595 srv.min_adj_rate
596 ---
597 from
598 okl_fe_std_rt_tmp_v srt,
599 okl_fe_std_rt_tmp_vers srv,
600 okl_index_values oiv
601 where
602 effective_start_date between srv.effective_from_date and NVL(srv.effective_to_date,effective_start_date+1)
603 and srt.std_rate_tmpl_id=srv.std_rate_tmpl_id
604 and srv.sts_code='ACTIVE'
605 AND srt.rate_card_yn = 'N'
606 AND srt.index_id = oiv.idx_id
607 AND effective_start_date BETWEEN oiv.datetime_valid AND nvl(oiv.datetime_invalid, effective_start_date + 1)
608 AND srt.RATE_TYPE_CODE = 'INDEX_RATE'
609 AND srt.currency_code = p_currency_code;
610
611 --Fixed # 5047718 ssdeshpa End
612 --Bug # 5045505 ssdeshpa End
613
614 BEGIN
615
616 l_debug_enabled := okl_debug_pub.check_log_enabled;
617 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
618 ,fnd_log.level_procedure);
619
620 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
621 okl_debug_pub.log_debug(fnd_log.level_procedure
622 ,l_module
623 ,'begin debug OKLRECXB.pls call populate_srt');
624 END IF; -- check for logging on STATEMENT level
625 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
626 ,fnd_log.level_statement);
627 -- call START_ACTIVITY to create savepoint, check compatibility
628 -- and initialize message list
629
630 l_return_status := okl_api.start_activity(p_api_name => l_api_name
631 ,p_pkg_name => G_PKG_NAME
632 ,p_init_msg_list => p_init_msg_list
633 ,l_api_version => l_api_version
634 ,p_api_version => p_api_version
635 ,p_api_type => G_API_TYPE
636 ,x_return_status => x_return_status); -- check if activity started successfully
637
638 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
639 RAISE okl_api.g_exception_unexpected_error;
640 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
641 RAISE okl_api.g_exception_error;
642 END IF;
643 --Populate the Record Structure for the OKL_ECC_PUB.validate method
644 --l_okl_ec_rec_type.target
645 l_okl_ec_rec_type.src_type:='SRT';
646 l_okl_ec_rec_type.target_id:= p_target_id;
647 l_okl_ec_rec_type.target_type:= p_target_type;
648 l_okl_ec_rec_type.target_eff_from:= P_target_eff_from;
649 l_okl_ec_rec_type.term:= p_term;
650 l_okl_ec_rec_type.territory:= p_territory;
651 l_okl_ec_rec_type.deal_size:= p_deal_size;
652 l_okl_ec_rec_type.customer_credit_class:= p_customer_credit_class;
653 l_okl_ec_rec_type.down_payment:= p_down_payment;
654 l_okl_ec_rec_type.advance_rent:= p_advance_rent;
655 l_okl_ec_rec_type.trade_in_value:= p_trade_in_value;
656 l_okl_ec_rec_type.validation_mode:='LOV';
657 --Bug # 5045505 ssdeshpa start
658 l_okl_ec_rec_type.currency_code := p_currency_code;
659 --Bug # 5045505 ssdeshpa End
660 --Bug # 5050143 ssdeshpa start
661 FOR i IN p_item_categories_table.FIRST..p_item_categories_table.LAST LOOP
662 IF p_item_categories_table.EXISTS(i) THEN
663 l_okl_ec_rec_type.item_categories_table(i) := p_item_categories_table(i);
664 END IF;
665 END LOOP;
666 --Bug # 5050143 ssdeshpa end
667 i:=1;
668 FOR l_okl_srt_rec IN c_srt_rec(p_target_eff_from,p_currency_code)
669 LOOP
670 l_okl_ec_rec_type.src_id:=l_okl_srt_rec.std_rate_tmpl_ver_id;
671 l_okl_ec_rec_type.source_name:=l_okl_srt_rec.name;
672 OKL_ECC_PUB.evaluate_eligibility_criteria(p_api_version
673 ,p_init_msg_list
674 ,x_return_status
675 ,x_msg_count
676 ,x_msg_data
677 ,l_okl_ec_rec_type
678 ,x_eligible);
679
680 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
681 RAISE okl_api.g_exception_unexpected_error;
682 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
683 RAISE okl_api.g_exception_error;
684 END IF;
685 IF(x_eligible) THEN
686 ----------------------------------------------------------------
687 --Bug # 5045505 ssdeshpa start
688 -- Populate the Adjustment mat. rec.
689 l_ac_rec_type.src_id := l_okl_srt_rec.adj_mat_version_id; -- Pricing adjustment matrix ID
690 l_ac_rec_type.source_name := NULL; -- NOT Mandatory Pricing Adjustment Matrix Name !
691 l_ac_rec_type.target_id := p_target_id ; -- Quote ID
692 l_ac_rec_type.src_type := 'PAM'; -- Lookup Code
693 l_ac_rec_type.target_type := 'QUOTE'; -- Same for both Quick Quote and Standard Quote
694 l_ac_rec_type.target_eff_from := P_target_eff_from; -- Quote effective From
695 l_ac_rec_type.term := p_term; -- Remaining four will be from teh business object like QQ / LQ
696 l_ac_rec_type.territory := p_territory;
697 l_ac_rec_type.deal_size := p_deal_size;
698 l_ac_rec_type.customer_credit_class := p_customer_credit_class; -- Not sure how to pass this even ..
699 -- Calling the API to get the adjustment factor ..
700 okl_ec_evaluate_pvt.get_adjustment_factor(
701 p_api_version => p_api_version,
702 p_init_msg_list => p_init_msg_list,
703 x_return_status => x_return_status,
704 x_msg_count => x_msg_count,
705 x_msg_data => x_msg_data,
706 p_okl_ac_rec => l_ac_rec_type,
707 x_adjustment_factor => l_adj_factor );
708 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
709 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
710 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
711 RAISE OKL_API.G_EXCEPTION_ERROR;
712 END IF;
713 --Calculate Effective Rate
714 l_srt_effective_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
715 --Bug # 5045505
716 /*If the calculated Rate is below the Minimum Rate, the Minimum Rate becomes
717 the Effective Rate that must be displayed in QQ and Sales Quote and be used
718 for pricing. Conversely, if the calculated rate is above the Maximum Rate,
719 the Maximum Rate becomes the Effective Rate.
720 */
721 IF(l_okl_srt_rec.max_adj_rate IS NOT NULL AND l_okl_srt_rec.max_adj_rate < l_srt_effective_rate) THEN
722 l_okl_srt_table(i).srt_rate := l_okl_srt_rec.max_adj_rate;
723 ELSIF(l_okl_srt_rec.min_adj_rate IS NOT NULL AND l_okl_srt_rec.min_adj_rate > l_srt_effective_rate) THEN
724 l_okl_srt_table(i).srt_rate := l_okl_srt_rec.min_adj_rate;
725 ELSE
726 l_okl_srt_table(i).srt_rate := l_srt_effective_rate;
727 END IF;
728 l_okl_srt_table(i).id:=l_okl_srt_rec.id;
729 l_okl_srt_table(i).name:=l_okl_srt_rec.name;
730 l_okl_srt_table(i).description:=l_okl_srt_rec.description;
731 l_okl_srt_table(i).frq_code:=l_okl_srt_rec.frq_code;
732 l_okl_srt_table(i).frq_meaning:= OKL_ACCOUNTING_UTIL.get_lookup_meaning('OKL_FREQUENCY',l_okl_srt_rec.frq_code);
733 l_okl_srt_table(i).std_rate_tmpl_ver_id:=l_okl_srt_rec.std_rate_tmpl_ver_id;
734 l_okl_srt_table(i).version_number:=l_okl_srt_rec.version_number;
735 l_okl_srt_table(i).effective_from:=l_okl_srt_rec.effective_from;
736 l_okl_srt_table(i).effective_to:=l_okl_srt_rec.effective_to;
737 l_okl_srt_table(i).sts_code:=l_okl_srt_rec.sts_code;
738 l_okl_srt_table(i).day_convention_code:=l_okl_srt_rec.day_convention_code;
739 i:=i+1;
740 END IF;
741 END LOOP;
742 --Bug # 5045505 ssdeshpa end
743 x_okl_srt_table:=l_okl_srt_table;
744 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
745 okl_debug_pub.log_debug(fnd_log.level_statement
746 ,l_module
747 ,'okl_ec_uptake_pvt.populate_standard_rate_template returned with status ' ||
748 l_return_status ||
749 ' x_msg_data ' ||
750 x_msg_data);
751 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
752
753 IF (l_return_status = okl_api.g_ret_sts_error) THEN
754 RAISE okl_api.g_exception_error;
755 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
756 RAISE okl_api.g_exception_unexpected_error;
757 END IF; --Copy value of OUT variable in the IN rvldrd type
758 okl_api.end_activity(x_msg_count => x_msg_count
759 ,x_msg_data => x_msg_data);
760
761 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
762 okl_debug_pub.log_debug(fnd_log.level_procedure
763 ,l_module
764 ,'end debug OKL_EC_UPTAKE_PVT.pls call populate_standard_rate_template');
765 END IF;
766 EXCEPTION
767 WHEN okl_api.g_exception_error THEN
768
769 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
770 ,p_pkg_name => G_PKG_NAME
771 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
772 ,x_msg_count => x_msg_count
773 ,x_msg_data => x_msg_data
774 ,p_api_type => G_API_TYPE);
775
776 WHEN okl_api.g_exception_unexpected_error THEN
777
778 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
779 ,p_pkg_name => G_PKG_NAME
780 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
781 ,x_msg_count => x_msg_count
782 ,x_msg_data => x_msg_data
783 ,p_api_type => G_API_TYPE);
784
785 WHEN OTHERS THEN
786
787 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
788 ,p_pkg_name => G_PKG_NAME
789 ,p_exc_name => 'OTHERS'
790 ,x_msg_count => x_msg_count
791 ,x_msg_data => x_msg_data
792 ,p_api_type => G_API_TYPE);
793 END populate_std_rate_tmpl;
794 -----------------------------------------------------------------------------------------------------
795 -----------------------------------------------------------------------------------------------------
796 --Populate Lease Rate Set For Lease Quote
797
798 PROCEDURE populate_lease_rate_set(p_api_version IN NUMBER,
799 p_init_msg_list IN VARCHAR2,
800 p_target_id number,
801 p_target_type IN varchar2,
802 x_okl_lrs_table OUT NOCOPY okl_lease_rate_set_tbl_type,
803 x_return_status OUT NOCOPY VARCHAR2,
804 x_msg_count OUT NOCOPY NUMBER,
805 x_msg_data OUT NOCOPY VARCHAR2)IS
806
807 l_return_status VARCHAR2(1);
808 l_program_name CONSTANT VARCHAR2(30) := 'p_lrslq';
809 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
810 l_okl_lrs_rec okl_lease_rate_set_rec_type;
811 l_okl_ec_rec_type okl_ec_evaluate_pvt.okl_ec_rec_type;
812 l_okl_lrs_table okl_lease_rate_set_tbl_type;
813 i INTEGER;
814 x_eligible boolean;
815 l_validation_mode VARCHAR2(30);
816 l_module CONSTANT fnd_log_messages.module%TYPE := 'lrs';
817 l_debug_enabled varchar2(10);
818 is_debug_procedure_on boolean;
819 is_debug_statement_on boolean;
820 l_api_version CONSTANT number := 1.0;
821 -- Bug # 5045505 ssdeshpa start
822 --Filtering the LRS on the Basis of Currency_code
823 CURSOR c_lrs_rec(effective_start_date DATE,
824 p_currency_code VARCHAR2) IS
825 select lrs.id,
826 lrv.rate_set_version_id,
827 lrv.version_number,
828 lrs.name,
829 lrs.description,
830 lrv.effective_from_date effective_from,
831 lrv.effective_to_date effective_to,
832 --Bug # 5050143 start
833 nvl(lrv.lrs_rate,lrv.standard_rate) lrs_rate,
834 --Bug # 5050143 start
835 lrv.sts_code,
836 lrs.frq_code
837 from okl_ls_rt_fctr_sets_v lrs,okl_fe_rate_set_versions_v lrv
838 where
839 effective_start_date between lrv.effective_from_date and NVL(lrv.effective_to_date,effective_start_date+1)
840 AND lrs.id=lrv.rate_set_id
841 and lrv.sts_code='ACTIVE'
842 AND lrs.currency_code = p_currency_code;
843
844 BEGIN
845 l_debug_enabled := okl_debug_pub.check_log_enabled;
846 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
847 ,fnd_log.level_procedure);
848
849
850 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
851 okl_debug_pub.log_debug(fnd_log.level_procedure
852 ,l_module
853 ,'begin debug OKLRECXB.pls.pls call populate_lease_rate_set2');
854 END IF; -- check for logging on STATEMENT level
855 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
856 ,fnd_log.level_statement);
857
858 -- call START_ACTIVITY to create savepoint, check compatibility
859 -- and initialize message list
860
861 l_return_status := okl_api.start_activity(p_api_name => l_api_name
862 ,p_pkg_name => G_PKG_NAME
863 ,p_init_msg_list => p_init_msg_list
864 ,l_api_version => l_api_version
865 ,p_api_version => p_api_version
866 ,p_api_type => G_API_TYPE
867 ,x_return_status => l_return_status); -- check if activity started successfully
868
869 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
870 RAISE okl_api.g_exception_unexpected_error;
871 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
872 RAISE okl_api.g_exception_error;
873 END IF;
874 --Populate the Record Structure for the OKL_ECC_PUB.validate method
875 --l_okl_ec_rec_type.target
876 l_okl_ec_rec_type.src_type := 'LRS';
877 l_okl_ec_rec_type.target_id := p_target_id;
878 l_okl_ec_rec_type.target_type := p_target_type;
879 l_okl_ec_rec_type.validation_mode := 'LOV';
880
881 -------------------------------------------------------------------------------------
882 populate_lq_attributes(l_okl_ec_rec_type
883 ,p_target_id
884 ,x_return_status);
885 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
886 RAISE okl_api.g_exception_unexpected_error;
887 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
888 RAISE okl_api.g_exception_error;
889 END IF;
890 --------------------------------------------------------------------------------------
891 i := 1;
892 FOR l_okl_lrs_rec IN c_lrs_rec(l_okl_ec_rec_type.target_eff_from,l_okl_ec_rec_type.currency_code)LOOP
893 l_okl_ec_rec_type.src_id := l_okl_lrs_rec.rate_set_version_id;
894 l_okl_ec_rec_type.source_name := l_okl_lrs_rec.name;
895 OKL_ECC_PUB.evaluate_eligibility_criteria(p_api_version
896 ,p_init_msg_list
897 ,x_return_status
898 ,x_msg_count
899 ,x_msg_data
900 ,l_okl_ec_rec_type
901 ,x_eligible);
902 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
903 RAISE okl_api.g_exception_unexpected_error;
904 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
905 RAISE okl_api.g_exception_error;
906 END IF;
907
908 IF(x_eligible) THEN
909 l_okl_lrs_table(i).id:=l_okl_lrs_rec.id;
910 l_okl_lrs_table(i).name:=l_okl_lrs_rec.name;
911 l_okl_lrs_table(i).rate_set_version_id:=l_okl_lrs_rec.rate_set_version_id;
912 l_okl_lrs_table(i).version_number:=l_okl_lrs_rec.version_number;
913 l_okl_lrs_table(i).description:=l_okl_lrs_rec.description;
914 l_okl_lrs_table(i).effective_from:=l_okl_lrs_rec.effective_from;
915 l_okl_lrs_table(i).effective_to:=l_okl_lrs_rec.effective_to;
916 l_okl_lrs_table(i).lrs_rate:=l_okl_lrs_rec.lrs_rate;
917 l_okl_lrs_table(i).sts_code:=l_okl_lrs_rec.sts_code;
918 l_okl_lrs_table(i).frq_code:= l_okl_lrs_rec.frq_code;
919 l_okl_lrs_table(i).frq_meaning:= OKL_ACCOUNTING_UTIL.get_lookup_meaning('OKL_FREQUENCY',l_okl_lrs_rec.frq_code);
920 i:=i+1;
921 END IF;
922 END LOOP;
923 x_okl_lrs_table:=l_okl_lrs_table;
924 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
925 okl_debug_pub.log_debug(fnd_log.level_statement
926 ,l_module
927 ,'OKLRECXB.pls.pls call populate_lease_rate_set2 returned with status ' ||
928 l_return_status ||
929 ' x_msg_data ' ||
930 x_msg_data);
931 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
932
933 IF (l_return_status = okl_api.g_ret_sts_error) THEN
934 RAISE okl_api.g_exception_error;
935 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
936 RAISE okl_api.g_exception_unexpected_error;
937 END IF; --Copy value of OUT variable in the IN rvldrd type
938 okl_api.end_activity(x_msg_count => x_msg_count
939 ,x_msg_data => x_msg_data);
940
941 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
942 okl_debug_pub.log_debug(fnd_log.level_procedure
943 ,l_module
944 ,'end debug OKLRECXB.pls call populate_lease_rate_set2');
945 END IF;
946 EXCEPTION
947 WHEN okl_api.g_exception_error THEN
948
949 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
950 ,p_pkg_name => G_PKG_NAME
951 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
952 ,x_msg_count => x_msg_count
953 ,x_msg_data => x_msg_data
954 ,p_api_type => G_API_TYPE);
955
956 WHEN okl_api.g_exception_unexpected_error THEN
957
958 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
959 ,p_pkg_name => G_PKG_NAME
960 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
961 ,x_msg_count => x_msg_count
962 ,x_msg_data => x_msg_data
963 ,p_api_type => G_API_TYPE);
964
965 WHEN OTHERS THEN
966
967 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
968 ,p_pkg_name => G_PKG_NAME
969 ,p_exc_name => 'OTHERS'
970 ,x_msg_count => x_msg_count
971 ,x_msg_data => x_msg_data
972 ,p_api_type => G_API_TYPE);
973 END populate_lease_rate_set;
974 --------------------------------------------------------------------------------------------------------
975
976 -------------------------------------------------------------------------------------------------
977
978 --To Populate the SRT for Lease Quote
979 PROCEDURE populate_std_rate_tmpl(p_api_version IN NUMBER,
980 p_init_msg_list IN VARCHAR2,
981 p_target_id IN NUMBER,
982 p_target_type VARCHAR2,
983 x_okl_srt_table OUT NOCOPY okl_std_rate_tmpl_tbl_type,
984 x_return_status OUT NOCOPY VARCHAR2,
985 x_msg_count OUT NOCOPY NUMBER,
986 x_msg_data OUT NOCOPY VARCHAR2) IS
987
988 l_return_status VARCHAR2(1);
989 l_program_name CONSTANT VARCHAR2(30) := 'p_srtlq';
990 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
991 l_okl_srt_rec okl_std_rate_tmpl_rec_type;
992 l_okl_ec_rec_type okl_ec_evaluate_pvt.okl_ec_rec_type;
993 l_okl_srt_table okl_std_rate_tmpl_tbl_type;
994 l_validation_mode VARCHAR2(30);
995 l_module CONSTANT fnd_log_messages.module%TYPE := 'srt';
996 l_debug_enabled varchar2(10);
997 is_debug_procedure_on boolean;
998 is_debug_statement_on boolean;
999 l_api_version CONSTANT number := 1.0;
1000 i INTEGER;
1001 x_eligible boolean;
1002 l_ac_rec_type okl_ec_evaluate_pvt.okl_ac_rec_type;
1003 l_adj_factor NUMBER;
1004 l_srt_effective_rate NUMBER;
1005 -- SCHODAVA Bug #4747677
1006 -- Modified cursor to pick up SRTs which are created
1007 -- with Rate_Card_Yn flag as No.
1008 --Fixed # 5047718 ssdeshpa Start
1009 --Modified SRT selection To include Valid Index Rate Type SRT
1010 --Bug # 5045505 ssdeshpa start
1011 --Modified To inlude more Attribute Selection
1012 CURSOR c_srt_rec(effective_start_date DATE,
1013 p_currency_code VARCHAR2) IS
1014 select srt.std_rate_tmpl_id as id,
1015 srv.std_rate_tmpl_ver_id,
1016 srv.version_number,
1017 srt.template_name as name,
1018 srt.template_desc as description,
1019 srt.frequency_code as frq_code,
1020 srv.effective_from_date effective_from,
1021 srv.effective_to_date effective_to,
1022 srv.srt_rate,
1023 srv.sts_code,
1024 srv.day_convention_code,
1025 ----------
1026 srt.pricing_engine_code pricing_engine_code,
1027 srt.rate_type_code rate_type_code,
1028 srt.index_id index_id,
1029 srv.spread spread,
1030 srt.frequency_code frequency_code,
1031 srv.adj_mat_version_id adj_mat_version_id,
1032 srv.max_adj_rate,
1033 srv.min_adj_rate
1034 ---
1035 from
1036 okl_fe_std_rt_tmp_v srt,
1037 okl_fe_std_rt_tmp_vers srv
1038 where
1039 effective_start_date between srv.effective_from_date and NVL(srv.effective_to_date,effective_start_date+1)
1040 AND srt.std_rate_tmpl_id=srv.std_rate_tmpl_id
1041 AND srv.sts_code='ACTIVE'
1042 AND srt.rate_card_yn = 'N'
1043 AND srt.RATE_TYPE_CODE = 'BASE_RATE'
1044 AND srt.currency_code = p_currency_code
1045 UNION
1046 select srt.std_rate_tmpl_id as id,
1047 srv.std_rate_tmpl_ver_id,
1048 srv.version_number,
1049 srt.template_name as name,
1050 srt.template_desc as description,
1051 srt.frequency_code as frq_code,
1052 srv.effective_from_date effective_from,
1053 srv.effective_to_date effective_to,
1054 srv.srt_rate,
1055 srv.sts_code,
1056 srv.day_convention_code,
1057 ----------
1058 srt.pricing_engine_code pricing_engine_code,
1059 srt.rate_type_code rate_type_code,
1060 srt.index_id index_id,
1061 srv.spread spread,
1062 srt.frequency_code frequency_code,
1063 srv.adj_mat_version_id adj_mat_version_id,
1064 srv.max_adj_rate,
1065 srv.min_adj_rate
1066 ---
1067 from
1068 okl_fe_std_rt_tmp_v srt,
1069 okl_fe_std_rt_tmp_vers srv,
1070 okl_index_values oiv
1071 where
1072 effective_start_date between srv.effective_from_date and NVL(srv.effective_to_date,effective_start_date+1)
1073 and srt.std_rate_tmpl_id=srv.std_rate_tmpl_id
1074 and srv.sts_code='ACTIVE'
1075 AND srt.rate_card_yn = 'N'
1076 AND srt.index_id = oiv.idx_id
1077 AND effective_start_date BETWEEN oiv.datetime_valid AND nvl(oiv.datetime_invalid, effective_start_date + 1)
1078 AND srt.RATE_TYPE_CODE = 'INDEX_RATE'
1079 AND srt.currency_code = p_currency_code;
1080
1081 --Fixed # 5047718 ssdeshpa End
1082 --Bug # 5045505 ssdeshpa End
1083
1084
1085 BEGIN
1086 l_debug_enabled := okl_debug_pub.check_log_enabled;
1087 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1088 ,fnd_log.level_procedure);
1089
1090
1091 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1092 okl_debug_pub.log_debug(fnd_log.level_procedure
1093 ,l_module
1094 ,'begin debug OKLRECXB.pls call populate_standard_rate_template2');
1095 END IF; -- check for logging on STATEMENT level
1096 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1097 ,fnd_log.level_statement);
1098
1099 -- call START_ACTIVITY to create savepoint, check compatibility
1100 -- and initialize message list
1101
1102 l_return_status := okl_api.start_activity(p_api_name => l_api_name
1103 ,p_pkg_name => G_PKG_NAME
1104 ,p_init_msg_list => p_init_msg_list
1105 ,l_api_version => l_api_version
1106 ,p_api_version => p_api_version
1107 ,p_api_type => G_API_TYPE
1108 ,x_return_status => x_return_status); -- check if activity started successfully
1109
1110 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1111 RAISE okl_api.g_exception_unexpected_error;
1112 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1113 RAISE okl_api.g_exception_error;
1114 END IF;
1115 --Populate the Record Structure for the OKL_ECC_PUB.validate method
1116 --l_okl_ec_rec_type.target
1117 l_okl_ec_rec_type.src_type:='SRT';
1118 l_okl_ec_rec_type.target_id:= p_target_id;
1119 l_okl_ec_rec_type.target_type:= p_target_type;
1120 l_okl_ec_rec_type.validation_mode:='LOV';
1121
1122 -------------------------------------------------------------------------------------
1123 populate_lq_attributes(l_okl_ec_rec_type
1124 ,p_target_id
1125 ,x_return_status);
1126 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1127 RAISE okl_api.g_exception_unexpected_error;
1128 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1129 RAISE okl_api.g_exception_error;
1130 END IF;
1131 --------------------------------------------------------------------------------------
1132
1133 i := 1;
1134 FOR l_okl_srt_rec IN c_srt_rec(l_okl_ec_rec_type.target_eff_from,l_okl_ec_rec_type.currency_code)
1135 LOOP
1136 l_okl_ec_rec_type.src_id:=l_okl_srt_rec.std_rate_tmpl_ver_id;
1137 l_okl_ec_rec_type.source_name:=l_okl_srt_rec.name;
1138 OKL_ECC_PUB.evaluate_eligibility_criteria(p_api_version
1139 ,p_init_msg_list
1140 ,x_return_status
1141 ,x_msg_count
1142 ,x_msg_data
1143 ,l_okl_ec_rec_type
1144 ,x_eligible);
1145 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1146 RAISE okl_api.g_exception_unexpected_error;
1147 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1148 RAISE okl_api.g_exception_error;
1149 END IF;
1150
1151 IF(x_eligible) THEN
1152 ----------------------------------------------------------------
1153 --Bug # 5045505 ssdeshpa start
1154 -- Populate the Adjustment mat. rec.
1155 l_ac_rec_type.src_id := l_okl_srt_rec.adj_mat_version_id; -- Pricing adjustment matrix ID
1156 l_ac_rec_type.source_name := NULL; -- NOT Mandatory Pricing Adjustment Matrix Name !
1157 l_ac_rec_type.target_id := p_target_id ; -- Quote ID
1158 l_ac_rec_type.src_type := 'PAM'; -- Lookup Code
1159 l_ac_rec_type.target_type := 'QUOTE'; -- Same for both Quick Quote and Standard Quote
1160 l_ac_rec_type.target_eff_from := l_okl_ec_rec_type.target_eff_from; -- Quote effective From
1161 l_ac_rec_type.term := l_okl_ec_rec_type.term; -- Remaining four will be from teh business object like QQ / LQ
1162 l_ac_rec_type.territory := l_okl_ec_rec_type.territory;
1163 l_ac_rec_type.deal_size := l_okl_ec_rec_type.deal_size;
1164 l_ac_rec_type.customer_credit_class := l_okl_ec_rec_type.customer_credit_class; -- Not sure how to pass this even ..
1165 -- Calling the API to get the adjustment factor ..
1166 okl_ec_evaluate_pvt.get_adjustment_factor(
1167 p_api_version => p_api_version,
1168 p_init_msg_list => p_init_msg_list,
1169 x_return_status => x_return_status,
1170 x_msg_count => x_msg_count,
1171 x_msg_data => x_msg_data,
1172 p_okl_ac_rec => l_ac_rec_type,
1173 x_adjustment_factor => l_adj_factor );
1174 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1175 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1176 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1177 RAISE OKL_API.G_EXCEPTION_ERROR;
1178 END IF;
1179 --Calculate Effective Rate
1180
1181 l_srt_effective_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
1182 --Bug # 5045505
1183 /*If the calculated Rate is below the Minimum Rate, the Minimum Rate becomes
1184 the Effective Rate that must be displayed in QQ and Sales Quote and be used
1185 for pricing. Conversely, if the calculated rate is above the Maximum Rate,
1186 the Maximum Rate becomes the Effective Rate.
1187 */
1188 IF(l_okl_srt_rec.max_adj_rate IS NOT NULL AND l_okl_srt_rec.max_adj_rate < l_srt_effective_rate) THEN
1189 l_okl_srt_table(i).srt_rate := l_okl_srt_rec.max_adj_rate;
1190 ELSIF(l_okl_srt_rec.min_adj_rate IS NOT NULL AND l_okl_srt_rec.min_adj_rate > l_srt_effective_rate) THEN
1191 l_okl_srt_table(i).srt_rate := l_okl_srt_rec.min_adj_rate;
1192 ELSE
1193 l_okl_srt_table(i).srt_rate := l_srt_effective_rate;
1194 END IF;
1195
1196 l_okl_srt_table(i).id:=l_okl_srt_rec.id;
1197 l_okl_srt_table(i).name:=l_okl_srt_rec.name;
1198 l_okl_srt_table(i).description:=l_okl_srt_rec.description;
1199 l_okl_srt_table(i).frq_code:= l_okl_srt_rec.frq_code;
1200 l_okl_srt_table(i).frq_meaning:= OKL_ACCOUNTING_UTIL.get_lookup_meaning('OKL_FREQUENCY',l_okl_srt_rec.frq_code);
1201 l_okl_srt_table(i).std_rate_tmpl_ver_id:=l_okl_srt_rec.std_rate_tmpl_ver_id;
1202 l_okl_srt_table(i).version_number:=l_okl_srt_rec.version_number;
1203 l_okl_srt_table(i).effective_from:=l_okl_srt_rec.effective_from;
1204 l_okl_srt_table(i).effective_to:=l_okl_srt_rec.effective_to;
1205 l_okl_srt_table(i).sts_code:=l_okl_srt_rec.sts_code;
1206 l_okl_srt_table(i).day_convention_code:=l_okl_srt_rec.day_convention_code;
1207 i := i+1;
1208 END IF;
1209 --Bug # 5045505 ssdeshpa End;
1210 END LOOP;
1211 x_okl_srt_table:=l_okl_srt_table;
1212 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1213 okl_debug_pub.log_debug(fnd_log.level_statement
1214 ,l_module
1215 ,'OKL_EC_UPTAKE_PVT.populate_standard_rate_template2 returned with status ' ||
1216 l_return_status ||
1217 ' x_msg_data ' ||
1218 x_msg_data);
1219 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1220
1221 IF (l_return_status = okl_api.g_ret_sts_error) THEN
1222 RAISE okl_api.g_exception_error;
1223 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1224 RAISE okl_api.g_exception_unexpected_error;
1225 END IF; --Copy value of OUT variable in the IN rvldrd type
1226 okl_api.end_activity(x_msg_count => x_msg_count
1227 ,x_msg_data => x_msg_data);
1228
1229 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1230 okl_debug_pub.log_debug(fnd_log.level_procedure
1231 ,l_module
1232 ,'end debug OKL_EC_UPTAKE_PVT.populate_standard_rate_template2 call create_vls');
1233 END IF;
1234 EXCEPTION
1235
1236 WHEN okl_api.g_exception_error THEN
1237
1238 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1239 ,p_pkg_name => G_PKG_NAME
1240 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1241 ,x_msg_count => x_msg_count
1242 ,x_msg_data => x_msg_data
1243 ,p_api_type => G_API_TYPE);
1244
1245 WHEN okl_api.g_exception_unexpected_error THEN
1246
1247 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1248 ,p_pkg_name => G_PKG_NAME
1249 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
1250 ,x_msg_count => x_msg_count
1251 ,x_msg_data => x_msg_data
1252 ,p_api_type => G_API_TYPE);
1253
1254 WHEN OTHERS THEN
1255
1256 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1257 ,p_pkg_name => G_PKG_NAME
1258 ,p_exc_name => 'OTHERS'
1259 ,x_msg_count => x_msg_count
1260 ,x_msg_data => x_msg_data
1261 ,p_api_type => G_API_TYPE);
1262 END populate_std_rate_tmpl;
1263 -------------------------------------------------------------------------------------
1264
1265 PROCEDURE populate_product(p_api_version IN NUMBER,
1266 p_init_msg_list IN VARCHAR2,
1267 p_target_id NUMBER,
1268 p_target_type IN VARCHAR2,
1269 x_okl_prod_table OUT NOCOPY okl_prod_tbl_type,
1270 x_return_status OUT NOCOPY VARCHAR2,
1271 x_msg_count OUT NOCOPY NUMBER,
1272 x_msg_data OUT NOCOPY VARCHAR2) IS
1273 l_vendor_prog_id NUMBER;
1274 l_return_status VARCHAR2(1);
1275 l_program_name CONSTANT VARCHAR2(30) := 'p_pdt';
1276 l_module CONSTANT fnd_log_messages.module%TYPE := 'lrs';
1277 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1278 l_okl_prod_rec okl_prod_rec_type;
1279 l_okl_ec_rec_type okl_ec_evaluate_pvt.okl_ec_rec_type;
1280 l_okl_prod_table okl_prod_tbl_type;
1281 i INTEGER;
1282 x_eligible boolean;
1283 l_debug_enabled varchar2(10);
1284 is_debug_procedure_on boolean;
1285 is_debug_statement_on boolean;
1286 l_api_version CONSTANT number := 1.0;
1287
1288 CURSOR c_product_rec1(effective_start_date DATE) IS
1289 -- Updated the sql for performance issue bug#5484903
1290 -- varangan - 26-9-06
1291 SELECT pdt.id ID,
1292 pdt.name NAME,
1293 pqy.name PRODUCT_SUBCLASS,
1294 pdt.version VERSION,
1295 pdt.description DESCRIPTION,
1296 pdt.PRODUCT_STATUS_CODE PRODUCT_STATUS_CODE,
1297 qve.VALUE Deal_Type,
1298 Okl_Accounting_Util.Get_Lookup_Meaning('OKL_SECURITIZATION_TYPE', qve.VALUE) Deal_Type_meaning
1299 FROM OKL_PRODUCTS PDT,
1300 OKL_PQY_VALUES qve,
1301 OKL_PDT_QUALITYS PQY,
1302 OKL_PDT_PQY_VALS PQV
1303 where
1304 pdt.id = pqv.pdt_id
1305 and qve.pqy_id = pqy.id
1306 AND pqv.qve_id = qve.id
1307 AND pqy.name In ('INVESTOR','LEASE')
1308 and effective_start_date BETWEEN pdt.from_date
1309 and NVL(pdt.to_date,SYSDATE)
1310 and pdt.product_status_code='APPROVED';
1311
1312 CURSOR c_product_rec2(l_vendor_prog_id number) IS
1313 select prod.id,
1314 prod.name,
1315 prod.product_subclass,
1316 prod.version,
1317 prod.description,
1318 prod.product_status_code,
1319 prod.deal_type,
1320 prod.deal_type_meaning
1321 from okl_product_parameters_v prod,OKL_VP_ASSOCIATIONS vp
1322 where
1323 --effective_start_date BETWEEN prod.from_date and NVL(prod.to_date,SYSDATE)
1324 --and
1325 vp.ASSOC_OBJECT_TYPE_CODE='LA_FINANCIAL_PRODUCT'
1326 and prod.id =vp.ASSOC_OBJECT_ID
1327 and vp.chr_id=l_vendor_prog_id
1328 AND prod.product_status_code='APPROVED';
1329
1330
1331
1332 BEGIN
1333 l_debug_enabled := okl_debug_pub.check_log_enabled;
1334 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1335 ,fnd_log.level_procedure);
1336
1337
1338 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1339 okl_debug_pub.log_debug(fnd_log.level_procedure
1340 ,l_module
1341 ,'begin debug OKLRECXB.pls call populate_product');
1342 END IF; -- check for logging on STATEMENT level
1343 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1344 ,fnd_log.level_statement);
1345
1346 -- call START_ACTIVITY to create savepoint, check compatibility
1347 -- and initialize message list
1348
1349 l_return_status := okl_api.start_activity(p_api_name => l_api_name
1350 ,p_pkg_name => G_PKG_NAME
1351 ,p_init_msg_list => p_init_msg_list
1352 ,l_api_version => l_api_version
1353 ,p_api_version => p_api_version
1354 ,p_api_type => G_API_TYPE
1355 ,x_return_status => x_return_status); -- check if activity started successfully
1356
1357 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1358 RAISE okl_api.g_exception_unexpected_error;
1359 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1360 RAISE okl_api.g_exception_error;
1361 END IF;
1362 --Populate the Record Structure for the OKL_ECC_PUB.validate method
1363 --l_okl_ec_rec_type.target
1364 l_okl_ec_rec_type.src_type:='PRODUCT';
1365 l_okl_ec_rec_type.target_id:= p_target_id;
1366 l_okl_ec_rec_type.target_type:= p_target_type;
1367 l_okl_ec_rec_type.validation_mode:='LOV';
1368
1369 populate_lq_attributes(l_okl_ec_rec_type
1370 ,p_target_id
1371 ,x_return_status);
1372 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1373 RAISE okl_api.g_exception_unexpected_error;
1374 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1375 RAISE okl_api.g_exception_error;
1376 END IF;
1377 ------------------------------------------------------------------------
1378 l_vendor_prog_id:=get_vp_id(p_target_id);
1379 ------------------------------------------------------------------------
1380 i:=1;
1381 if(l_vendor_prog_id IS NULL) THEN
1382 FOR l_okl_prod_rec IN c_product_rec1(l_okl_ec_rec_type.target_eff_from)
1383 LOOP
1384 l_okl_ec_rec_type.src_id:=l_okl_prod_rec.id;
1385 l_okl_ec_rec_type.source_name:=l_okl_prod_rec.name;
1386 OKL_ECC_PUB.evaluate_eligibility_criteria(p_api_version
1387 ,p_init_msg_list
1388 ,x_return_status
1389 ,x_msg_count
1390 ,x_msg_data
1391 ,l_okl_ec_rec_type
1392 ,x_eligible);
1393
1394 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1395 RAISE okl_api.g_exception_unexpected_error;
1396 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1397 RAISE okl_api.g_exception_error;
1398 END IF;
1399 IF(x_eligible) THEN
1400 l_okl_prod_table(i).id:=l_okl_prod_rec.id;
1401 l_okl_prod_table(i).name:=l_okl_prod_rec.name;
1402 i:=i+1;
1403 END IF;
1404 END LOOP;
1405
1406 else
1407 FOR l_okl_prod_rec IN c_product_rec2(l_vendor_prog_id)
1408 LOOP
1409 l_okl_ec_rec_type.src_id:=l_okl_prod_rec.id;
1410 l_okl_ec_rec_type.source_name:=l_okl_prod_rec.name;
1411 OKL_ECC_PUB.evaluate_eligibility_criteria(p_api_version
1412 ,p_init_msg_list
1413 ,x_return_status
1414 ,x_msg_count
1415 ,x_msg_data
1416 ,l_okl_ec_rec_type
1417 ,x_eligible);
1418 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1419 RAISE okl_api.g_exception_unexpected_error;
1420 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1421 RAISE okl_api.g_exception_error;
1422 END IF;
1423 IF(x_eligible) THEN
1424 l_okl_prod_table(i).id:=l_okl_prod_rec.id;
1425 l_okl_prod_table(i).name:=l_okl_prod_rec.name;
1426 i:=i+1;
1427 END IF;
1428 END LOOP;
1429
1430 end if;
1431 x_okl_prod_table:=l_okl_prod_table;
1432
1433 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1434 okl_debug_pub.log_debug(fnd_log.level_statement
1435 ,l_module
1436 ,'OKL_EC_UPTAKE_PVT.populate_product returned with status ' ||
1437 l_return_status ||
1438 ' x_msg_data ' ||
1439 x_msg_data);
1440 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1441
1442 IF (l_return_status = okl_api.g_ret_sts_error) THEN
1443 RAISE okl_api.g_exception_error;
1444 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1445 RAISE okl_api.g_exception_unexpected_error;
1446 END IF;
1447 okl_api.end_activity(x_msg_count => x_msg_count
1448 ,x_msg_data => x_msg_data);
1449
1450 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1451 okl_debug_pub.log_debug(fnd_log.level_procedure
1452 ,l_module
1453 ,'end debug OKL_EC_UPTAKE_PVT.populate_product ');
1454 END IF;
1455 EXCEPTION
1456 WHEN okl_api.g_exception_error THEN
1457
1458 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1459 ,p_pkg_name => G_PKG_NAME
1460 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1461 ,x_msg_count => x_msg_count
1462 ,x_msg_data => x_msg_data
1463 ,p_api_type => G_API_TYPE);
1464
1465 WHEN okl_api.g_exception_unexpected_error THEN
1466
1467 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1468 ,p_pkg_name => G_PKG_NAME
1469 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
1470 ,x_msg_count => x_msg_count
1471 ,x_msg_data => x_msg_data
1472 ,p_api_type => G_API_TYPE);
1473
1474 WHEN OTHERS THEN
1475
1476 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1477 ,p_pkg_name => G_PKG_NAME
1478 ,p_exc_name => 'OTHERS'
1479 ,x_msg_count => x_msg_count
1480 ,x_msg_data => x_msg_data
1481 ,p_api_type => G_API_TYPE); END populate_product;
1482
1483 ----------------------------------------------------------------------------------------
1484 PROCEDURE populate_vendor_program(p_api_version IN NUMBER,
1485 p_init_msg_list IN VARCHAR2,
1486 p_target_id number,
1487 p_target_type IN varchar2,
1488 p_target_eff_from date,
1489 p_term NUMBER,
1490 p_territory VARCHAR2,
1491 p_deal_size number,
1492 p_customer_credit_class VARCHAR2,
1493 p_down_payment number,
1494 p_advance_rent number,
1495 p_trade_in_value number,
1496 p_item_table okl_number_table_type,
1497 p_item_categories_table okl_number_table_type,
1498 x_okl_vp_table OUT NOCOPY okl_vp_tbl_type,
1499 x_return_status OUT NOCOPY VARCHAR2,
1500 x_msg_count OUT NOCOPY NUMBER,
1501 x_msg_data OUT NOCOPY VARCHAR2) IS
1502
1503
1504 l_program_name CONSTANT VARCHAR2(30) := 'vp_p';
1505 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1506 l_okl_vp_rec okl_vp_rec_type;
1507 l_okl_ec_rec_type okl_ec_evaluate_pvt.okl_ec_rec_type;
1508 l_okl_vp_table okl_vp_tbl_type;
1509 i INTEGER;
1510 l_api_version CONSTANT number := 1.0;
1511 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1512 x_eligible boolean;
1513 l_debug_enabled varchar2(10);
1514 is_debug_procedure_on boolean;
1515 is_debug_statement_on boolean;
1516
1517 l_module CONSTANT fnd_log_messages.module%TYPE := 'VPA';
1518
1519
1520
1521 CURSOR c_vp_rec(effective_start_date DATE) IS
1522 select tbl.id,
1523 tbl.contract_number,
1524 tbl.start_date,
1525 tbl.end_date
1526 from okc_k_headers_v tbl
1527 where effective_start_date between tbl.start_date and NVL(tbl.end_date,effective_start_date+1)
1528 AND tbl.SCS_CODE='PROGRAM'
1529 AND tbl.STS_CODE='ACTIVE'
1530 AND tbl.template_yn='N';
1531
1532 BEGIN
1533 l_debug_enabled := okl_debug_pub.check_log_enabled;
1534 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1535 ,fnd_log.level_procedure);
1536
1537
1538 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1539 okl_debug_pub.log_debug(fnd_log.level_procedure
1540 ,l_module
1541 ,'begin debug OKLRECXB.pls call populate_vendor_program');
1542 END IF; -- check for logging on STATEMENT level
1543 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1544 ,fnd_log.level_statement);
1545
1546 -- call START_ACTIVITY to create savepoint, check compatibility
1547 -- and initialize message list
1548
1549 l_return_status := okl_api.start_activity(p_api_name => l_api_name
1550 ,p_pkg_name => G_PKG_NAME
1551 ,p_init_msg_list => p_init_msg_list
1552 ,l_api_version => l_api_version
1553 ,p_api_version => p_api_version
1554 ,p_api_type => G_API_TYPE
1555 ,x_return_status => x_return_status); -- check if activity started successfully
1556
1557 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1558 RAISE okl_api.g_exception_unexpected_error;
1559 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1560 RAISE okl_api.g_exception_error;
1561 END IF;
1562 --Populate the Record Structure for the OKL_ECC_PUB.validate method
1563 --l_okl_ec_rec_type.target
1564 l_okl_ec_rec_type.src_type:='VENDOR_PROGRAM';
1565 l_okl_ec_rec_type.target_id:= p_target_id;
1566 l_okl_ec_rec_type.target_type:= p_target_type;
1567 l_okl_ec_rec_type.target_eff_from:= P_target_eff_from;
1568 l_okl_ec_rec_type.term:= p_term;
1569 l_okl_ec_rec_type.territory:= p_territory;
1570 l_okl_ec_rec_type.deal_size:= p_deal_size;
1571 l_okl_ec_rec_type.customer_credit_class:= p_customer_credit_class;
1572 l_okl_ec_rec_type.down_payment:= p_down_payment;
1573 l_okl_ec_rec_type.advance_rent:= p_advance_rent;
1574 l_okl_ec_rec_type.trade_in_value:= p_trade_in_value;
1575 l_okl_ec_rec_type.validation_mode:='LOV';
1576
1577 i := 1;
1578 FOR l_okl_vp_rec IN c_vp_rec(p_target_eff_from)LOOP
1579 l_okl_ec_rec_type.src_id:=l_okl_vp_rec.id;
1580 l_okl_ec_rec_type.source_name:=l_okl_vp_rec.contract_number;
1581
1582 OKL_ECC_PUB.evaluate_eligibility_criteria(p_api_version
1583 ,p_init_msg_list
1584 ,x_return_status
1585 ,x_msg_count
1586 ,x_msg_data
1587 ,l_okl_ec_rec_type
1588 ,x_eligible);
1589
1590 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1591 RAISE okl_api.g_exception_unexpected_error;
1592 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1593 RAISE okl_api.g_exception_error;
1594 END IF;
1595 IF(x_eligible) THEN
1596 l_okl_vp_table(i).id:=l_okl_vp_rec.id;
1597 l_okl_vp_table(i).contract_number:=l_okl_vp_rec.contract_number;
1598 i:=i+1;
1599 END IF;
1600 END LOOP;
1601 x_okl_vp_table:=l_okl_vp_table;
1602 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1603 okl_debug_pub.log_debug(fnd_log.level_statement
1604 ,l_module
1605 ,'OKL_EC_UPTAKE_PVT.populate_vpa returned with status ' ||
1606 l_return_status ||
1607 ' x_msg_data ' ||
1608 x_msg_data);
1609 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1610
1611 IF (l_return_status = okl_api.g_ret_sts_error) THEN
1612 RAISE okl_api.g_exception_error;
1613 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1614 RAISE okl_api.g_exception_unexpected_error;
1615 END IF;
1616 okl_api.end_activity(x_msg_count => x_msg_count
1617 ,x_msg_data => x_msg_data);
1618
1619 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1620 okl_debug_pub.log_debug(fnd_log.level_procedure
1621 ,l_module
1622 ,'end debug OKL_EC_UPTAKE_PVT.populate_vpa ');
1623 END IF;
1624
1625 EXCEPTION
1626 WHEN okl_api.g_exception_error THEN
1627
1628 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1629 ,p_pkg_name => G_PKG_NAME
1630 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1631 ,x_msg_count => x_msg_count
1632 ,x_msg_data => x_msg_data
1633 ,p_api_type => G_API_TYPE);
1634
1635 WHEN okl_api.g_exception_unexpected_error THEN
1636
1637 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1638 ,p_pkg_name => G_PKG_NAME
1639 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
1640 ,x_msg_count => x_msg_count
1641 ,x_msg_data => x_msg_data
1642 ,p_api_type => G_API_TYPE);
1643
1644 WHEN OTHERS THEN
1645
1646 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1647 ,p_pkg_name => G_PKG_NAME
1648 ,p_exc_name => 'OTHERS'
1649 ,x_msg_count => x_msg_count
1650 ,x_msg_data => x_msg_data
1651 ,p_api_type => G_API_TYPE);
1652 END populate_vendor_program;
1653 ------------------------------------------------------------------------------------
1654 END OKL_EC_UPTAKE_PVT;