[Home] [Help]
PACKAGE BODY: APPS.OKL_LEASE_QUOTE_ASSET_PVT
Source
1 PACKAGE BODY OKL_LEASE_QUOTE_ASSET_PVT AS
2 /* $Header: OKLRQUAB.pls 120.46 2010/11/09 18:12:07 sechawla ship $ */
3
4 ------------------------------------------------------------------------------
5 -- GLOBAL OKL MESSAGES
6 ------------------------------------------------------------------------------
7 G_UNITS_VALUE CONSTANT VARCHAR2(200) := 'OKL_LLA_ITEM_RECORD';
8
9 --Bug#7291307 :Adding start
10 -----------------------------------
11 -- PROCEDURE sync_tradein_description
12 -----------------------------------
13
14 PROCEDURE sync_tradein_description(p_api_version IN NUMBER,
15 p_init_msg_list IN VARCHAR2,
16 x_msg_count OUT NOCOPY NUMBER,
17 x_msg_data OUT NOCOPY VARCHAR2,
18 x_return_status OUT NOCOPY VARCHAR2,
19 p_quote_id IN NUMBER,
20 p_description IN VARCHAR2
21 ) IS
22
23 l_api_version CONSTANT NUMBER := 1;
24 l_api_name CONSTANT VARCHAR2(30) := 'sync_tradein_description';
25
26 cursor adj_csr is
27 SELECT costadj.id
28 FROM okl_cost_adjustments_b costadj,
29 okl_assets_b asset
30 WHERE costadj.adjustment_source_type = 'TRADEIN'
31 AND costadj.parent_object_id = asset.id
32 AND costadj.parent_object_code = 'ASSET'
33 AND asset.parent_object_id = p_quote_id
34 AND asset.parent_object_code = 'LEASEQUOTE';
35
36 type rowid_tbl is table of okl_cost_adjustments_b.ID%TYPE
37 index by binary_integer ;
38 l_rowid_tbl rowid_tbl;
39 L_FETCH_SIZE NUMBER := 5000;
40 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
41
42 begin
43 ------------------------------------------------------------
44 -- Start processing
45 ------------------------------------------------------------
46
47 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
48
49 l_return_status := Okl_Api.START_ACTIVITY(
50 p_api_name => l_api_name,
51 p_pkg_name => G_PKG_NAME,
52 p_init_msg_list => p_init_msg_list,
53 l_api_version => l_api_version,
54 p_api_version => p_api_version,
55 p_api_type => '_PVT',
56 x_return_status => l_return_status);
57
58 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
59 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
60 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
61 RAISE Okl_Api.G_EXCEPTION_ERROR;
62 END IF;
63
64 OPEN adj_csr;
65 LOOP
66 EXIT WHEN adj_csr%NOTFOUND;
67 l_rowid_tbl.delete;
68 FETCH adj_csr BULK COLLECT INTO l_rowid_tbl limit L_FETCH_SIZE;
69
70 IF l_rowid_tbl.count > 0 then
71
72 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
73 UPDATE OKL_COST_ADJUSTMENTS_TL
74 SET
75 description = p_description
76 WHERE ID = l_rowid_tbl(indx)
77 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
78 end if;
79 end loop;
80
81 Okl_Api.END_ACTIVITY (
82 x_msg_count => x_msg_count,
83 x_msg_data => x_msg_data);
84
85 EXCEPTION
86 WHEN OKL_API.G_EXCEPTION_ERROR THEN
87 x_return_status := G_RET_STS_ERROR;
88 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
89 x_return_status := G_RET_STS_UNEXP_ERROR;
90 WHEN OTHERS THEN
91 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
92 p_msg_name => G_DB_ERROR,
93 p_token1 => G_PROG_NAME_TOKEN,
94 p_token1_value => 'OKLRQUAB.sync_tradein',
95 p_token2 => G_SQLCODE_TOKEN,
96 p_token2_value => sqlcode,
97 p_token3 => G_SQLERRM_TOKEN,
98 p_token3_value => sqlerrm);
99
100 x_return_status := G_RET_STS_UNEXP_ERROR;
101 END sync_tradein_description;
102 --Bug#7291307 :Adding end
103
104 --Bug # 5142940 ssdeshpa start
105 -----------------------------------
106 -- PROCEDURE process_adj_cashflows
107 -----------------------------------
108 PROCEDURE process_adj_cashflows(p_cdjv_rec IN okl_cdj_pvt.cdjv_rec_type
109 ,p_event_mode IN VARCHAR2
110 ,x_msg_count OUT NOCOPY NUMBER
111 ,x_msg_data OUT NOCOPY VARCHAR2
112 ,x_return_status OUT NOCOPY VARCHAR2) IS
113
114 l_p_id NUMBER;
115 l_start_date DATE;
116 l_cf_hdr_rec cashflow_hdr_rec_type;
117 l_cashflow_level_tbl cashflow_level_tbl_type;
118 l_adj_assets_rec okl_cdj_pvt.cdjv_rec_type;
119 lv_parent_object_code okl_lease_quotes_b.parent_object_code%TYPE;
120
121 CURSOR get_quote_rec(p_quote_id NUMBER) IS
122 SELECT qte.id, qte.expected_start_date, qte.parent_object_code
123 FROM okl_assets_b ast,
124 okl_lease_quotes_b qte
125 WHERE qte.id = ast.parent_object_id
126 AND ast.id= p_quote_id;
127
128 BEGIN
129 l_adj_assets_rec := p_cdjv_rec;
130 OPEN get_quote_rec(l_adj_assets_rec.parent_object_id);
131 FETCH get_quote_rec INTO l_p_id, l_start_date, lv_parent_object_code;
132 CLOSE get_quote_rec;
133 IF(p_event_mode ='delete') THEN
134 IF(l_adj_assets_rec.adjustment_source_type='DOWN_PAYMENT') THEN
135 OKL_LEASE_QUOTE_CASHFLOW_PVT.delete_cashflows (
136 p_api_version => G_API_VERSION
137 ,p_init_msg_list => G_FALSE
138 ,p_transaction_control => 'T'
139 ,p_source_object_code => 'QUOTED_ASSET_DOWN_PAYMENT'
140 ,p_source_object_id => l_adj_assets_rec.parent_object_id
141 ,x_return_status => x_return_status
142 ,x_msg_count => x_msg_count
143 ,x_msg_data => x_msg_data);
144
145 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
146 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
147 ELSIF x_return_status = G_RET_STS_ERROR THEN
148 RAISE OKL_API.G_EXCEPTION_ERROR;
149 END IF;
150 END IF;
151 ELSIF(p_event_mode ='create') THEN
152 --Create Rec Structure for Cash flows
153
154 l_cf_hdr_rec.type_code :='INFLOW'; -- mandatory. Allowable values: 'INFLOW' 'OUTFLOW'
155 l_cf_hdr_rec.stream_type_id := l_adj_assets_rec.stream_type_id;-- optional for quick quotes only
156 l_cf_hdr_rec.status_code :='CURRENT'; -- status code for cashflow
157 l_cf_hdr_rec.arrears_flag :='N';-- mandatory
158 l_cf_hdr_rec.frequency_code :='M';-- mandatory
159 --,dnz_periods VARCHAR2(80) -- used for possible display in lease quote UI (TBD)
160 --,dnz_periodic_amount VARCHAR2(80) -- used for possible display in lease quote UI (TBD)
161 l_cf_hdr_rec.parent_object_code := 'QUOTED_ASSET_DOWN_PAYMENT';-- mandatory (see 'insert_rows' procedure for possible values)
162 l_cf_hdr_rec.parent_object_id := l_adj_assets_rec.parent_object_id; -- mandatory
163
164 IF (lv_parent_object_code = 'LEASEOPP') THEN
165 l_cf_hdr_rec.quote_type_code := 'LQ'; -- mandatory Allowable values: 'LQ' 'QQ' 'LA'
166 ELSIF (lv_parent_object_code = 'LEASEAPP') THEN
167 l_cf_hdr_rec.quote_type_code := 'LA'; -- mandatory Allowable values: 'LQ' 'QQ' 'LA'
168 END IF;
169
170 l_cf_hdr_rec.quote_id := l_p_id;-- mandatory
171 --Create end
172 l_cashflow_level_tbl(1).start_date := l_start_date;
173 l_cashflow_level_tbl(1).periods := 1;
174 l_cashflow_level_tbl(1).periodic_amount := l_adj_assets_rec.value;
175 l_cashflow_level_tbl(1).record_mode := 'CREATE';
176 --Create Cash Flow for Asset
177 OKL_LEASE_QUOTE_CASHFLOW_PVT.create_cashflow (
178 p_api_version => G_API_VERSION
179 ,p_init_msg_list => G_FALSE
180 ,p_transaction_control => 'T'
181 ,p_cashflow_header_rec => l_cf_hdr_rec
182 ,p_cashflow_level_tbl => l_cashflow_level_tbl
183 ,x_return_status => x_return_status
184 ,x_msg_count => x_msg_count
185 ,x_msg_data => x_msg_data);
186
187 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
188 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
189 ELSIF x_return_status = G_RET_STS_ERROR THEN
190 RAISE OKL_API.G_EXCEPTION_ERROR;
191 END IF;
192
193 ELSIF(p_event_mode ='update') THEN
194 IF(l_adj_assets_rec.adjustment_source_type='DOWN_PAYMENT') THEN
195 OKL_LEASE_QUOTE_CASHFLOW_PVT.delete_cashflows (
196 p_api_version => G_API_VERSION
197 ,p_init_msg_list => G_FALSE
198 ,p_transaction_control => 'T'
199 ,p_source_object_code => 'QUOTED_ASSET_DOWN_PAYMENT'
200 ,p_source_object_id => l_adj_assets_rec.parent_object_id
201 ,x_return_status => x_return_status
202 ,x_msg_count => x_msg_count
203 ,x_msg_data => x_msg_data);
204
205 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
206 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
207 ELSIF x_return_status = G_RET_STS_ERROR THEN
208 RAISE OKL_API.G_EXCEPTION_ERROR;
209 END IF;
210 END IF;
211 IF(l_adj_assets_rec.adjustment_source_type='DOWN_PAYMENT' AND
212 l_adj_assets_rec.processing_type='BILL' AND
213 l_adj_assets_rec.stream_type_id IS NOT NULL) THEN
214
215 --Create Rec Structure for Cash flows
216 l_cf_hdr_rec.type_code :='INFLOW'; -- mandatory. Allowable values: 'INFLOW' 'OUTFLOW'
217 l_cf_hdr_rec.stream_type_id := l_adj_assets_rec.stream_type_id;-- optional for quick quotes only
218 l_cf_hdr_rec.status_code :='CURRENT'; -- status code for cashflow
219 l_cf_hdr_rec.arrears_flag :='N';-- mandatory
220 l_cf_hdr_rec.frequency_code :='M';-- mandatory
221
222 l_cf_hdr_rec.parent_object_code := 'QUOTED_ASSET_DOWN_PAYMENT';-- mandatory (see 'insert_rows' procedure for possible values)
223 l_cf_hdr_rec.parent_object_id := l_adj_assets_rec.parent_object_id; -- mandatory
224
225
226 IF (lv_parent_object_code = 'LEASEOPP') THEN
227 l_cf_hdr_rec.quote_type_code := 'LQ'; -- mandatory Allowable values: 'LQ' 'QQ' 'LA'
228 ELSIF (lv_parent_object_code = 'LEASEAPP') THEN
229 l_cf_hdr_rec.quote_type_code := 'LA'; -- mandatory Allowable values: 'LQ' 'QQ' 'LA'
230 END IF;
231
232 l_cf_hdr_rec.quote_id := l_p_id;-- mandatory
233 --Create end
234 l_cashflow_level_tbl(1).start_date := l_start_date;
235 l_cashflow_level_tbl(1).periods := 1;
236 l_cashflow_level_tbl(1).periodic_amount := l_adj_assets_rec.value;
237 l_cashflow_level_tbl(1).record_mode := 'CREATE';
238 --Create Cash Flow for Down Payment
239 OKL_LEASE_QUOTE_CASHFLOW_PVT.create_cashflow (
240 p_api_version => G_API_VERSION
241 ,p_init_msg_list => G_FALSE
242 ,p_transaction_control => 'T'
243 ,p_cashflow_header_rec => l_cf_hdr_rec
244 ,p_cashflow_level_tbl => l_cashflow_level_tbl
245 ,x_return_status => x_return_status
246 ,x_msg_count => x_msg_count
247 ,x_msg_data => x_msg_data);
248
249 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
250 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
251 ELSIF x_return_status = G_RET_STS_ERROR THEN
252 RAISE OKL_API.G_EXCEPTION_ERROR;
253 END IF;
254
255 END IF;
256
257 END IF;
258 EXCEPTION
259 WHEN OKL_API.G_EXCEPTION_ERROR THEN
260 x_return_status := G_RET_STS_ERROR;
261 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
262 x_return_status := G_RET_STS_UNEXP_ERROR;
263 WHEN OTHERS THEN
264 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
265 p_msg_name => G_DB_ERROR,
266 p_token1 => G_PROG_NAME_TOKEN,
267 p_token1_value => 'OKLRQUAB.pr_adj_cfl',
268 p_token2 => G_SQLCODE_TOKEN,
269 p_token2_value => sqlcode,
270 p_token3 => G_SQLERRM_TOKEN,
271 p_token3_value => sqlerrm);
272
273 x_return_status := G_RET_STS_UNEXP_ERROR;
274 END process_adj_cashflows;
275
276 -----------------------------------
277 -- FUNCTION is_pricing_method_equal
278 -----------------------------------
279 FUNCTION is_pricing_method_equal(p_source_quote_id IN NUMBER,
280 p_target_quote_id IN NUMBER)
281 RETURN VARCHAR2 IS
282
283 lv_source_pricing_type VARCHAR2(15);
284 lv_target_pricing_type VARCHAR2(15);
285 BEGIN
286 select pricing_method
287 into lv_source_pricing_type
288 from okl_lease_quotes_b
289 where id = p_source_quote_id;
290
291 select pricing_method
292 into lv_target_pricing_type
293 from okl_lease_quotes_b
294 where id = p_target_quote_id;
295
296 IF (lv_source_pricing_type = lv_target_pricing_type) THEN
297 RETURN 'Y';
298 ELSE
299 RETURN 'N';
300 END IF;
301 END is_pricing_method_equal;
302
303 ---------------------------------------
304 -- PROCEDURE process_link_asset_amounts
305 ---------------------------------------
306 --Fixing Bug # 4735811 Start
307 PROCEDURE process_link_asset_amounts (
308 p_adj_amount IN NUMBER,
309 p_assoc_assets_tbl IN OUT NOCOPY asset_adjustment_tbl_type,
310 x_return_status OUT NOCOPY VARCHAR2) IS
311
312 l_program_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'process_link_asset_amounts';
313 l_line_amount NUMBER;
314 l_assoc_assets_tbl asset_adjustment_tbl_type;
315 l_assoc_amount NUMBER;
316 l_assoc_total NUMBER;
317 l_currency_code VARCHAR2(15);
318 lv_parent_object_code VARCHAR2(30);
319 l_compare_amt NUMBER;
320 l_diff NUMBER;
321 l_adj_rec BINARY_INTEGER;
322 lx_return_status VARCHAR2(1);
323 lv_pricing_method OKL_LEASE_QUOTES_B.PRICING_METHOD%TYPE;
324 l_quote_id NUMBER;
325
326 ln_index NUMBER;
327
328 CURSOR c_get_quote_id(p_asset_id NUMBER) IS
329 SELECT PARENT_OBJECT_ID
330 FROM OKL_ASSETS_B
331 WHERE ID = p_asset_id
332 AND PARENT_OBJECT_CODE='LEASEQUOTE';
333
334 CURSOR c_get_parent_object_code(p_quote_id NUMBER) IS
335 SELECT parent_object_code
336 FROM okl_lease_quotes_b
337 WHERE id = p_quote_id;
338 BEGIN
339
340 l_assoc_total := 0;
341 l_assoc_assets_tbl := p_assoc_assets_tbl;
342
343 ln_index := l_assoc_assets_tbl.FIRST;
344
345 OPEN c_get_quote_id(l_assoc_assets_tbl(ln_index).parent_object_id);
346 FETCH c_get_quote_id INTO l_quote_id;
347 CLOSE c_get_quote_id;
348
349 IF(l_quote_id IS NOT NULL) THEN
350 OPEN c_get_parent_object_code(l_quote_id);
351 FETCH c_get_parent_object_code INTO lv_parent_object_code;
352 CLOSE c_get_parent_object_code;
353 ELSE
354 RETURN;
355 END IF;
356 IF (lv_parent_object_code = 'LEASEOPP') THEN
357 SELECT currency_code
358 INTO l_currency_code
359 FROM okl_lease_opportunities_b lop,
360 okl_lease_quotes_b lsq
361 WHERE lsq.parent_object_code = lv_parent_object_code
362 AND lsq.parent_object_id = lop.id
363 AND lsq.id = l_quote_id;
364 ELSIF (lv_parent_object_code = 'LEASEAPP') THEN
365 SELECT currency_code
366 INTO l_currency_code
367 FROM okl_lease_applications_b lap,
368 okl_lease_quotes_b lsq
369 WHERE lsq.parent_object_code = lv_parent_object_code
370 AND lsq.parent_object_id = lap.id
371 AND lsq.id = l_quote_id;
372 END IF;
373
374 l_line_amount := p_adj_amount;
375
376 ----------------------------------------------------------------------------
377 -- 2. Loop through to determine associated amounts and round off the amounts
378 ----------------------------------------------------------------------------
379 FOR i IN l_assoc_assets_tbl.FIRST .. l_assoc_assets_tbl.LAST LOOP
380
381 IF l_assoc_assets_tbl.EXISTS(i) THEN
382 --Fixing Bug # 4735811 Start
383 --GIVING NPE when Quick Allocate
384 IF (l_assoc_assets_tbl(i).value IS NULL) THEN
385 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
386 p_msg_name => 'OKL_LINKASSET_NULL_FOUND');
387 RAISE OKL_API.G_EXCEPTION_ERROR;
388 END IF;
389 --Fixing Bug # 4735811 End
390 l_assoc_amount := l_assoc_assets_tbl(i).value;
391
392 l_assoc_amount := okl_accounting_util.round_amount(p_amount => l_assoc_amount,
393 p_currency_code => l_currency_code);
394
395 l_assoc_assets_tbl(i).value := l_assoc_amount;
396
397 l_assoc_total := l_assoc_total + l_assoc_amount;
398
399 END IF;
400
401 END LOOP;
402
403 ----------------------------------------------------------------------------------------------------
404 -- 3. Adjust associated amount if associated total does not tally up with line amount after rounding
405 ----------------------------------------------------------------------------------------------------
406 IF l_assoc_total <> l_line_amount THEN
407
408 l_diff := ABS(l_assoc_total - l_line_amount);
409
410 FOR i IN l_assoc_assets_tbl.FIRST .. l_assoc_assets_tbl.LAST LOOP
411
412 IF l_assoc_assets_tbl.EXISTS(i) THEN
413
414 -- if the total split amount is less than line amount add the difference amount to the
415 -- asset with less amount and if the total split amount is greater than the line amount
416 -- than subtract the difference amount from the asset with highest amount
417
418 IF i = l_assoc_assets_tbl.FIRST THEN
419
420 l_adj_rec := i;
421 l_compare_amt := l_assoc_assets_tbl(i).value;
422
423 ELSIF (l_assoc_total < l_line_amount) AND (l_assoc_assets_tbl(i).value <= l_compare_amt) OR
424 (l_assoc_total > l_line_amount) AND (l_assoc_assets_tbl(i).value >= l_compare_amt) THEN
425
426 l_adj_rec := i;
427 l_compare_amt := l_assoc_assets_tbl(i).value;
428
429 END IF;
430
431 END IF;
432
433 END LOOP;
434
435 IF l_assoc_total < l_line_amount THEN
436
437 l_assoc_assets_tbl(l_adj_rec).value := l_assoc_assets_tbl(l_adj_rec).value + l_diff;
438
439 ELSE
440
441 l_assoc_assets_tbl(l_adj_rec).value := l_assoc_assets_tbl(l_adj_rec).value - l_diff;
442
443 END IF;
444
445 END IF;
446
447 p_assoc_assets_tbl := l_assoc_assets_tbl;
448 x_return_status := G_RET_STS_SUCCESS;
449
450 EXCEPTION
451 WHEN OKL_API.G_EXCEPTION_ERROR THEN
452 x_return_status := G_RET_STS_ERROR;
453
454 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
455 x_return_status := G_RET_STS_UNEXP_ERROR;
456
457 WHEN OTHERS THEN
458 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
459 p_msg_name => G_DB_ERROR,
460 p_token1 => G_PROG_NAME_TOKEN,
461 p_token1_value => l_program_name,
462 p_token2 => G_SQLCODE_TOKEN,
463 p_token2_value => sqlcode,
464 p_token3 => G_SQLERRM_TOKEN,
465 p_token3_value => sqlerrm);
466
467 x_return_status := G_RET_STS_UNEXP_ERROR;
468
469 END process_link_asset_amounts;
470 --Fixing Bug # 4735811 End
471
472 ------------------------------------------------------------------------------
473 --Fixing Bug # 4759578 Start
474 PROCEDURE process_link_assets(p_api_version IN NUMBER,
475 p_init_msg_list IN VARCHAR2,
476 p_transaction_control IN VARCHAR2,
477 p_asset_adj_tbl IN asset_adjustment_tbl_type,
478 x_asset_adj_tbl OUT NOCOPY asset_adjustment_tbl_type,
479 x_return_status OUT NOCOPY VARCHAR2,
480 x_msg_count OUT NOCOPY NUMBER,
481 x_msg_data OUT NOCOPY VARCHAR2) IS
482
483 l_program_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'process_link_asset_amounts';
484 l_line_amount NUMBER;
485 l_asset_adj_tbl asset_adjustment_tbl_type;
486 l_assoc_amount NUMBER;
487 l_assoc_total NUMBER;
488 l_currency_code VARCHAR2(15);
489 lv_parent_object_code VARCHAR2(30);
490 l_compare_amt NUMBER;
491 l_diff NUMBER;
492 l_adj_rec BINARY_INTEGER;
493 lx_return_status VARCHAR2(1);
494 lv_pricing_method OKL_LEASE_QUOTES_B.PRICING_METHOD%TYPE;
495 l_quote_id NUMBER;
496 ln_index NUMBER;
497
498 BEGIN
499 IF p_transaction_control = G_TRUE THEN
500 SAVEPOINT l_program_name;
501 END IF;
502
503 IF p_init_msg_list = G_TRUE THEN
504 FND_MSG_PUB.initialize;
505 END IF;
506
507 l_asset_adj_tbl := p_asset_adj_tbl;
508
509 -- Validate Adjustment assets amount
510 ln_index := l_asset_adj_tbl.FIRST;
511 IF (l_asset_adj_tbl IS NOT NULL AND l_asset_adj_tbl.COUNT>0 ) THEN
512
513 process_link_asset_amounts(p_adj_amount => l_asset_adj_tbl(ln_index).adjustment_amount,
514 p_assoc_assets_tbl => l_asset_adj_tbl,
515 x_return_status => x_return_status);
516
517 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
518 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
519 ELSIF x_return_status = G_RET_STS_ERROR THEN
520 RAISE OKL_API.G_EXCEPTION_ERROR;
521 END IF;
522 END IF;
523 x_asset_adj_tbl := l_asset_adj_tbl;
524 x_return_status := G_RET_STS_SUCCESS;
525
526 EXCEPTION
527
528 WHEN OKL_API.G_EXCEPTION_ERROR THEN
529
530 x_return_status := G_RET_STS_ERROR;
531
532 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
533
534 x_return_status := G_RET_STS_UNEXP_ERROR;
535
536 WHEN OTHERS THEN
537
538 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
539 p_msg_name => G_DB_ERROR,
540 p_token1 => G_PROG_NAME_TOKEN,
541 p_token1_value => l_program_name,
542 p_token2 => G_SQLCODE_TOKEN,
543 p_token2_value => sqlcode,
544 p_token3 => G_SQLERRM_TOKEN,
545 p_token3_value => sqlerrm);
546
547 x_return_status := G_RET_STS_UNEXP_ERROR;
548 END process_link_assets;
549 --Fixing Bug # 4759578 End
550 ---------------------------------------------------------------------------------------------------
551 -------------------------------------------
552 -- PROCEDURE validate_subsidy_applicability
553 -------------------------------------------
554 FUNCTION validate_subsidy_applicability(p_inv_item_id IN NUMBER,
555 p_subsidy_id IN NUMBER,
556 p_exp_start_date IN DATE,
557 p_inv_org_id IN NUMBER,
558 p_currency_code IN VARCHAR2,
559 p_authoring_org_id IN NUMBER,
560 p_cust_acct_id IN NUMBER,
561 p_product_id IN NUMBER,
562 p_sales_rep_id IN NUMBER)
563 RETURN VARCHAR2 IS
564
565 lv_asset_applicable VARCHAR2(1);
566 halt_validation EXCEPTION;
567
568 BEGIN
569 lv_asset_applicable := okl_asset_subsidy_pvt.validate_subsidy_applicability
570 (p_subsidy_id => p_subsidy_id,
571 p_start_date => p_exp_start_date,
572 p_inv_item_id => p_inv_item_id,
573 p_inv_org_id => p_inv_org_id,
574 p_currency_code => p_currency_code,
575 p_authoring_org_id => p_authoring_org_id,
576 p_cust_account_id => p_cust_acct_id,
577 p_pdt_id => p_product_id,
578 p_sales_rep_id => p_sales_rep_id);
579
580 RETURN lv_asset_applicable;
581 EXCEPTION
582 When halt_validation then
583 Return(lv_asset_applicable);
584 When others then
585 lv_asset_applicable := 'N';
586 Return(lv_asset_applicable);
587 END validate_subsidy_applicability;
588
589 -----------------------------------
590 -- PROCEDURE validate_subsidy_usage
591 -----------------------------------
592 PROCEDURE validate_subsidy_usage(p_asset_id IN NUMBER,
593 p_input_adj_tbl IN asset_adj_tbl_type,
594 x_return_status OUT NOCOPY VARCHAR2) IS
595
596 l_program_name CONSTANT VARCHAR2(30) := 'validate_subsidy_usage';
597 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
598
599 ld_start_date DATE;
600 lv_currency_code VARCHAR2(15);
601 ln_product_id NUMBER;
602 ln_cust_acct_id NUMBER;
603 ln_sales_rep_id NUMBER;
604 ln_org_id NUMBER;
605 ln_inv_org_id NUMBER;
606 ln_inv_item_id NUMBER;
607
608 lv_parent_code VARCHAR2(30);
609 ln_total_subsidy_amount NUMBER := 0;
610 ln_subsidy_amount NUMBER;
611 ln_subsidy_id NUMBER;
612 lv_asset_number okl_assets_b.asset_number%TYPE;
613 lv_asset_sub_applicable VARCHAR2(1);
614
615 CURSOR c_get_quote_lop_info IS
616 SELECT quote.expected_start_date,
617 assetcomp.INV_ITEM_ID,
618 leaseopp.inv_org_id,
619 leaseopp.currency_code,
620 leaseopp.ORG_ID,
621 leaseopp.CUST_ACCT_ID,
622 quote.product_id,
623 leaseopp.SALES_REP_ID
624 FROM
625 okl_assets_b asset,
626 okl_asset_components_b assetcomp,
627 okl_lease_quotes_b quote,
628 okl_lease_opportunities_b leaseopp
629 WHERE
630 asset.id = p_asset_id
631 AND asset.id = assetcomp.asset_id
632 AND assetcomp.PRIMARY_COMPONENT = 'YES'
633 AND asset.parent_object_id = quote.id
634 AND asset.parent_object_code = 'LEASEQUOTE'
635 AND quote.parent_object_id = leaseopp.id;
636
637 CURSOR c_get_quote_lap_info IS
638 SELECT quote.expected_start_date,
639 assetcomp.INV_ITEM_ID,
640 leaseapp.inv_org_id,
641 leaseapp.currency_code,
642 leaseapp.ORG_ID,
643 leaseapp.CUST_ACCT_ID,
644 quote.product_id,
645 leaseapp.SALES_REP_ID
646 FROM
647 okl_assets_b asset,
648 okl_asset_components_b assetcomp,
649 okl_lease_quotes_b quote,
650 okl_lease_applications_b leaseapp
651 WHERE
652 asset.id = p_asset_id
653 AND asset.id = assetcomp.asset_id
654 AND assetcomp.PRIMARY_COMPONENT = 'YES'
655 AND asset.parent_object_id = quote.id
656 AND asset.parent_object_code = 'LEASEQUOTE'
657 AND quote.parent_object_id = leaseapp.id;
658
659 CURSOR c_get_asset_number(p_asset_id IN NUMBER) IS
660 SELECT asset_number
661 FROM okl_assets_b
662 WHERE id = p_asset_id;
663
664 l_input_adj_tbl asset_adj_tbl_type;
665
666 BEGIN
667 l_input_adj_tbl := p_input_adj_tbl;
668
669 SELECT lsq.parent_object_code
670 INTO lv_parent_code
671 FROM okl_assets_b ast, okl_lease_quotes_b lsq
672 where ast.parent_object_id = lsq.id
673 and ast.parent_object_code = 'LEASEQUOTE'
674 and ast.id = p_asset_id;
675
676 IF (lv_parent_code = 'LEASEOPP') THEN
677 OPEN c_get_quote_lop_info;
678 FETCH c_get_quote_lop_info into ld_start_date, ln_inv_item_id, ln_inv_org_id,
679 lv_currency_code, ln_org_id, ln_cust_acct_id,
680 ln_product_id, ln_sales_rep_id;
681 CLOSE c_get_quote_lop_info;
682 ELSIF (lv_parent_code = 'LEASEAPP') THEN
683 OPEN c_get_quote_lap_info;
684 FETCH c_get_quote_lap_info into ld_start_date, ln_inv_item_id, ln_inv_org_id,
685 lv_currency_code, ln_org_id, ln_cust_acct_id,
686 ln_product_id, ln_sales_rep_id;
687 CLOSE c_get_quote_lap_info;
688 END IF;
689
690 FOR i IN l_input_adj_tbl.FIRST .. l_input_adj_tbl.LAST LOOP
691 IF l_input_adj_tbl.EXISTS(i) THEN
692
693 --Added IF condition by bkatraga for bug 10265739
694 IF(l_input_adj_tbl(i).adjustment_source_type = 'SUBSIDY') THEN
695
696
697 OPEN c_get_asset_number(p_asset_id => l_input_adj_tbl(i).parent_object_id);
698 FETCH c_get_asset_number INTO lv_asset_number;
699 CLOSE c_get_asset_number;
700
701 IF (l_input_adj_tbl(i).value IS NOT NULL) THEN
702 ln_total_subsidy_amount := ln_total_subsidy_amount + l_input_adj_tbl(i).value;
703 ln_subsidy_amount := l_input_adj_tbl(i).value;
704 ELSE
705 ln_total_subsidy_amount := ln_total_subsidy_amount + l_input_adj_tbl(i).default_subsidy_amount;
706 ln_subsidy_amount := l_input_adj_tbl(i).default_subsidy_amount;
707 END IF;
708
709 ln_subsidy_id := l_input_adj_tbl(i).adjustment_source_id;
710
711 -- Check for Subsidy Pool Usage
712 lv_asset_sub_applicable := okl_asset_subsidy_pvt.validate_subsidy_applicability
713 (p_subsidy_id => ln_subsidy_id,
714 p_start_date => ld_start_date,
715 p_inv_item_id => ln_inv_item_id,
716 p_inv_org_id => ln_inv_org_id,
717 p_currency_code => lv_currency_code,
718 p_authoring_org_id => ln_org_id,
719 p_cust_account_id => ln_cust_acct_id,
720 p_pdt_id => ln_product_id,
721 p_sales_rep_id => ln_sales_rep_id,
722 p_tot_subsidy_amount => ln_total_subsidy_amount,
723 p_subsidy_amount => ln_subsidy_amount,
724 p_filter_flag => 'N',
725 p_dnz_asset_number => lv_asset_number);
726
727 IF (lv_asset_sub_applicable = 'N') THEN
728 RAISE OKL_API.G_EXCEPTION_ERROR;
729 END IF;
730 END IF; --Added by bkatraga for bug 10265739
731 END IF;
732 END LOOP;
733
734 x_return_status := G_RET_STS_SUCCESS;
735 EXCEPTION
736
737 WHEN OKL_API.G_EXCEPTION_ERROR THEN
738 x_return_status := G_RET_STS_ERROR;
739
740 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
741 x_return_status := G_RET_STS_UNEXP_ERROR;
742
743 WHEN OTHERS THEN
744 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
745 p_msg_name => G_DB_ERROR,
746 p_token1 => G_PROG_NAME_TOKEN,
747 p_token1_value => l_program_name,
748 p_token2 => G_SQLCODE_TOKEN,
749 p_token2_value => sqlcode,
750 p_token3 => G_SQLERRM_TOKEN,
751 p_token3_value => sqlerrm);
752
753 x_return_status := G_RET_STS_UNEXP_ERROR;
754
755 END validate_subsidy_usage;
756
757 -----------------------------------
758 -- PROCEDURE validate_subsidy_usage
759 -----------------------------------
760 PROCEDURE validate_subsidy_usage(p_asset_id IN NUMBER,
761 p_total_subsidy_amount IN NUMBER,
762 p_input_adj_rec IN okl_cdj_pvt.cdjv_rec_type,
763 x_return_status OUT NOCOPY VARCHAR2) IS
764
765 l_program_name CONSTANT VARCHAR2(30) := 'validate_subsidy_usage';
766 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
767
768 ld_start_date DATE;
769 lv_currency_code VARCHAR2(15);
770 ln_product_id NUMBER;
771 ln_cust_acct_id NUMBER;
772 ln_sales_rep_id NUMBER;
773 ln_org_id NUMBER;
774 ln_inv_org_id NUMBER;
775 ln_inv_item_id NUMBER;
776
777 lv_parent_code VARCHAR2(30);
778 ln_total_subsidy_amount NUMBER := 0;
779 ln_subsidy_amount NUMBER;
780 ln_subsidy_id NUMBER;
781 lv_asset_number okl_assets_b.asset_number%TYPE;
782 lv_asset_sub_applicable VARCHAR2(1);
783
784 CURSOR c_get_quote_lop_info IS
785 SELECT quote.expected_start_date,
786 assetcomp.INV_ITEM_ID,
787 leaseopp.inv_org_id,
788 leaseopp.currency_code,
789 leaseopp.ORG_ID,
790 leaseopp.CUST_ACCT_ID,
791 quote.product_id,
792 leaseopp.SALES_REP_ID
793 FROM
794 okl_assets_b asset,
795 okl_asset_components_b assetcomp,
796 okl_lease_quotes_b quote,
797 okl_lease_opportunities_b leaseopp
798 WHERE
799 asset.id = p_asset_id
800 AND asset.id = assetcomp.asset_id
801 AND assetcomp.PRIMARY_COMPONENT = 'YES'
802 AND asset.parent_object_id = quote.id
803 AND asset.parent_object_code = 'LEASEQUOTE'
804 AND quote.parent_object_id = leaseopp.id;
805
806 CURSOR c_get_quote_lap_info IS
807 SELECT quote.expected_start_date,
808 assetcomp.INV_ITEM_ID,
809 leaseapp.inv_org_id,
810 leaseapp.currency_code,
811 leaseapp.ORG_ID,
812 leaseapp.CUST_ACCT_ID,
813 quote.product_id,
814 leaseapp.SALES_REP_ID
815 FROM
816 okl_assets_b asset,
817 okl_asset_components_b assetcomp,
818 okl_lease_quotes_b quote,
819 okl_lease_applications_b leaseapp
820 WHERE
821 asset.id = p_asset_id
822 AND asset.id = assetcomp.asset_id
823 AND assetcomp.PRIMARY_COMPONENT = 'YES'
824 AND asset.parent_object_id = quote.id
825 AND asset.parent_object_code = 'LEASEQUOTE'
826 AND quote.parent_object_id = leaseapp.id;
827
828 CURSOR c_get_asset_number(p_asset_id IN NUMBER) IS
829 SELECT asset_number
830 FROM okl_assets_b
831 WHERE id = p_asset_id;
832
833 l_input_adj_rec okl_cdj_pvt.cdjv_rec_type;
834
835 BEGIN
836 l_input_adj_rec := p_input_adj_rec;
837
838 SELECT lsq.parent_object_code
839 INTO lv_parent_code
840 FROM okl_assets_b ast, okl_lease_quotes_b lsq
841 where ast.parent_object_id = lsq.id
842 and ast.parent_object_code = 'LEASEQUOTE'
843 and ast.id = p_asset_id;
844
845 IF (lv_parent_code = 'LEASEOPP') THEN
846 OPEN c_get_quote_lop_info;
847 FETCH c_get_quote_lop_info into ld_start_date, ln_inv_item_id, ln_inv_org_id,
848 lv_currency_code, ln_org_id, ln_cust_acct_id,
849 ln_product_id, ln_sales_rep_id;
850 CLOSE c_get_quote_lop_info;
851 ELSIF (lv_parent_code = 'LEASEAPP') THEN
852 OPEN c_get_quote_lap_info;
853 FETCH c_get_quote_lap_info into ld_start_date, ln_inv_item_id, ln_inv_org_id,
854 lv_currency_code, ln_org_id, ln_cust_acct_id,
855 ln_product_id, ln_sales_rep_id;
856 CLOSE c_get_quote_lap_info;
857 END IF;
858
859 IF (l_input_adj_rec.parent_object_id IS NOT NULL) THEN
860
861 OPEN c_get_asset_number(p_asset_id => l_input_adj_rec.parent_object_id);
862 FETCH c_get_asset_number INTO lv_asset_number;
863 CLOSE c_get_asset_number;
864
865 IF (l_input_adj_rec.value IS NOT NULL) THEN
866 ln_total_subsidy_amount := ln_total_subsidy_amount + l_input_adj_rec.value;
867 ln_subsidy_amount := l_input_adj_rec.value;
868 ELSE
869 ln_total_subsidy_amount := ln_total_subsidy_amount + l_input_adj_rec.default_subsidy_amount;
870 ln_subsidy_amount := l_input_adj_rec.default_subsidy_amount;
871 END IF;
872
873 ln_subsidy_id := l_input_adj_rec.adjustment_source_id;
874
875 -- Check for Subsidy Pool Usage
876 lv_asset_sub_applicable := okl_asset_subsidy_pvt.validate_subsidy_applicability
877 (p_subsidy_id => ln_subsidy_id,
878 p_start_date => ld_start_date,
879 p_inv_item_id => ln_inv_item_id,
880 p_inv_org_id => ln_inv_org_id,
881 p_currency_code => lv_currency_code,
882 p_authoring_org_id => ln_org_id,
883 p_cust_account_id => ln_cust_acct_id,
884 p_pdt_id => ln_product_id,
885 p_sales_rep_id => ln_sales_rep_id,
886 p_tot_subsidy_amount => p_total_subsidy_amount,
887 p_subsidy_amount => ln_subsidy_amount,
888 p_filter_flag => 'N',
889 p_dnz_asset_number => lv_asset_number);
890
891 IF (lv_asset_sub_applicable = 'N') THEN
892 RAISE OKL_API.G_EXCEPTION_ERROR;
893 END IF;
894
895 END IF;
896
897 x_return_status := G_RET_STS_SUCCESS;
898 EXCEPTION
899
900 WHEN OKL_API.G_EXCEPTION_ERROR THEN
901 x_return_status := G_RET_STS_ERROR;
902
903 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
904 x_return_status := G_RET_STS_UNEXP_ERROR;
905
906 WHEN OTHERS THEN
907 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
908 p_msg_name => G_DB_ERROR,
909 p_token1 => G_PROG_NAME_TOKEN,
910 p_token1_value => l_program_name,
911 p_token2 => G_SQLCODE_TOKEN,
912 p_token2_value => sqlcode,
913 p_token3 => G_SQLERRM_TOKEN,
914 p_token3_value => sqlerrm);
915
916 x_return_status := G_RET_STS_UNEXP_ERROR;
917
918 END validate_subsidy_usage;
919
920 -----------------------------------
921 -- PROCEDURE sync_asset_comp_values
922 -----------------------------------
923 PROCEDURE sync_asset_comp_values(x_asset_comp_tbl IN OUT NOCOPY component_tbl_type,
924 p_input_comp_tbl IN asset_component_tbl_type,
925 x_return_status OUT NOCOPY VARCHAR2) IS
926
927 l_program_name CONSTANT VARCHAR2(30) := 'sync_asset_comp_values';
928 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
929
930 l_input_comp_tbl asset_component_tbl_type;
931 l_output_comp_tbl component_tbl_type;
932
933 BEGIN
934
935 l_input_comp_tbl := p_input_comp_tbl;
936 l_output_comp_tbl := x_asset_comp_tbl;
937
938 FOR i IN l_input_comp_tbl.FIRST .. l_input_comp_tbl.LAST LOOP
939 IF l_input_comp_tbl.EXISTS(i) THEN
940 FOR j IN l_output_comp_tbl.FIRST .. l_output_comp_tbl.LAST LOOP
941 IF l_output_comp_tbl.EXISTS(j) THEN
942 IF (l_output_comp_tbl(j).id = l_input_comp_tbl(i).id) THEN
943
944 IF (l_input_comp_tbl(i).unit_cost IS NOT NULL) THEN
945 l_output_comp_tbl(j).unit_cost := l_input_comp_tbl(i).unit_cost;
946 END IF;
947
948 IF (l_input_comp_tbl(i).number_of_units IS NOT NULL) THEN
949 l_output_comp_tbl(j).number_of_units := l_input_comp_tbl(i).number_of_units;
950 END IF;
951
952 END IF;
953 END IF;
954 END LOOP;
955 END IF;
956 END LOOP;
957
958 x_asset_comp_tbl := l_output_comp_tbl;
959
960 x_return_status := G_RET_STS_SUCCESS;
961
962 EXCEPTION
963
964 WHEN OKL_API.G_EXCEPTION_ERROR THEN
965 x_return_status := G_RET_STS_ERROR;
966
967 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
968 x_return_status := G_RET_STS_UNEXP_ERROR;
969
970 WHEN OTHERS THEN
971 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
972 p_msg_name => G_DB_ERROR,
973 p_token1 => G_PROG_NAME_TOKEN,
974 p_token1_value => l_program_name,
975 p_token2 => G_SQLCODE_TOKEN,
976 p_token2_value => sqlcode,
977 p_token3 => G_SQLERRM_TOKEN,
978 p_token3_value => sqlerrm);
979
980 x_return_status := G_RET_STS_UNEXP_ERROR;
981
982 END sync_asset_comp_values;
983
984 ------------------------------
985 -- PROCEDURE sync_asset_values
986 ------------------------------
987 PROCEDURE sync_asset_values(x_asset_rec IN OUT NOCOPY asset_rec_type,
988 p_input_rec IN asset_rec_type,
989 x_return_status OUT NOCOPY VARCHAR2) IS
990
991 l_program_name CONSTANT VARCHAR2(30) := 'sync_asset_values';
992 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
993
994 BEGIN
995
996 IF (p_input_rec.id IS NOT NULL) THEN
997 x_asset_rec.id := p_input_rec.id;
998 END IF;
999
1000 IF (p_input_rec.object_version_number IS NOT NULL) THEN
1001 x_asset_rec.object_version_number := p_input_rec.object_version_number;
1002 END IF;
1003
1004 IF (p_input_rec.parent_object_id IS NOT NULL) THEN
1005 x_asset_rec.parent_object_id := p_input_rec.parent_object_id;
1006 END IF;
1007
1008 IF (p_input_rec.parent_object_code IS NOT NULL) THEN
1009 x_asset_rec.parent_object_code := p_input_rec.parent_object_code;
1010 END IF;
1011
1012 IF (p_input_rec.rate_card_id IS NOT NULL) THEN
1013 x_asset_rec.rate_card_id := p_input_rec.rate_card_id;
1014 END IF;
1015
1016 IF (p_input_rec.rate_template_id IS NOT NULL) THEN
1017 x_asset_rec.rate_template_id := p_input_rec.rate_template_id;
1018 END IF;
1019
1020 IF (p_input_rec.structured_pricing IS NOT NULL) THEN
1021 x_asset_rec.structured_pricing := p_input_rec.structured_pricing;
1022 END IF;
1023
1024 IF (p_input_rec.target_arrears IS NOT NULL) THEN
1025 x_asset_rec.target_arrears := p_input_rec.target_arrears;
1026 END IF;
1027
1028 IF (p_input_rec.oec IS NOT NULL) THEN
1029 x_asset_rec.oec := p_input_rec.oec;
1030 END IF;
1031
1032 IF (p_input_rec.oec_percentage IS NOT NULL) THEN
1033 x_asset_rec.oec_percentage := p_input_rec.oec_percentage;
1034 END IF;
1035
1036 IF (p_input_rec.lease_rate_factor IS NOT NULL) THEN
1037 x_asset_rec.lease_rate_factor := p_input_rec.lease_rate_factor;
1038 END IF;
1039
1040 x_return_status := G_RET_STS_SUCCESS;
1041
1042 EXCEPTION
1043
1044 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1045 x_return_status := G_RET_STS_ERROR;
1046
1047 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1048 x_return_status := G_RET_STS_UNEXP_ERROR;
1049
1050 WHEN OTHERS THEN
1051 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1052 p_msg_name => G_DB_ERROR,
1053 p_token1 => G_PROG_NAME_TOKEN,
1054 p_token1_value => l_program_name,
1055 p_token2 => G_SQLCODE_TOKEN,
1056 p_token2_value => sqlcode,
1057 p_token3 => G_SQLERRM_TOKEN,
1058 p_token3_value => sqlerrm);
1059
1060 x_return_status := G_RET_STS_UNEXP_ERROR;
1061
1062 END sync_asset_values;
1063
1064 ----------------------------------
1065 -- PROCEDURE get_eot_default_value
1066 ----------------------------------
1067 FUNCTION get_eot_default_value(p_asset_rec IN asset_rec_type,
1068 p_asset_comp_tbl IN asset_component_tbl_type,
1069 x_return_status OUT NOCOPY VARCHAR2)
1070 RETURN NUMBER IS
1071
1072 ln_eot_default_value NUMBER := null;
1073 halt_validation EXCEPTION;
1074 l_asset_comp_tbl asset_component_tbl_type;
1075 lv_eot_type_code VARCHAR2(30);
1076 ln_cost NUMBER;
1077 ln_number_of_units NUMBER;
1078 l_asset_rec asset_rec_type;
1079
1080 CURSOR c_get_eot_category_code(p_quote_id IN NUMBER)
1081 IS
1082 SELECT
1083 EOT.EOT_TYPE_CODE
1084 FROM OKL_FE_EO_TERMS_ALL_B EOT,
1085 OKL_FE_EO_TERM_VERS EOT_VER,
1086 OKL_LEASE_QUOTES_B QTE
1087 WHERE
1088 EOT_VER.END_OF_TERM_VER_ID = QTE.END_OF_TERM_OPTION_ID
1089 AND EOT_VER.END_OF_TERM_ID = EOT.END_OF_TERM_ID
1090 AND QTE.ID = p_quote_id;
1091
1092 BEGIN
1093 OPEN c_get_eot_category_code(p_quote_id => p_asset_rec.parent_object_id);
1094 FETCH c_get_eot_category_code INTO lv_eot_type_code;
1095 CLOSE c_get_eot_category_code;
1096
1097 l_asset_comp_tbl := p_asset_comp_tbl;
1098 l_asset_rec := p_asset_rec;
1099
1100 -- Get the Inv Item Id of asset
1101 FOR i IN l_asset_comp_tbl.FIRST .. l_asset_comp_tbl.LAST LOOP
1102 IF l_asset_comp_tbl.EXISTS(i) THEN
1103 IF (l_asset_comp_tbl(i).primary_component = 'YES') THEN
1104 ln_cost := l_asset_comp_tbl(i).unit_cost * l_asset_comp_tbl(i).number_of_units;
1105 ln_number_of_units := l_asset_comp_tbl(i).number_of_units;
1106 END IF;
1107 END IF;
1108 END LOOP;
1109
1110 -- Calculate the EOT value
1111 IF (lv_eot_type_code IN ('PERCENT', 'RESIDUAL_PERCENT')) THEN
1112 ln_eot_default_value := l_asset_rec.end_of_term_value_default;
1113 ELSIF (lv_eot_type_code IN ('AMOUNT', 'RESIDUAL_AMOUNT')) THEN
1114 IF (l_asset_rec.end_of_term_value_default IS NOT NULL) THEN
1115 ln_eot_default_value := l_asset_rec.end_of_term_value_default * ln_number_of_units;
1116 END IF;
1117 END IF;
1118
1119 RETURN ln_eot_default_value;
1120
1121 EXCEPTION
1122 When halt_validation then
1123 Return(ln_eot_default_value);
1124 When others then
1125 ln_eot_default_value := null;
1126 Return(ln_eot_default_value);
1127 END get_eot_default_value;
1128
1129 -----------------------------
1130 -- PROCEDURE validate_subsidy
1131 -----------------------------
1132 FUNCTION validate_subsidy(p_quote_id IN NUMBER,
1133 p_subsidy_id IN NUMBER)
1134 RETURN VARCHAR2 IS
1135
1136 ld_start_date DATE;
1137 lv_currency_code VARCHAR2(15);
1138 ln_product_id NUMBER;
1139 ln_cust_acct_id NUMBER;
1140 ln_sales_rep_id NUMBER;
1141 ln_org_id NUMBER;
1142 ln_inv_org_id NUMBER;
1143 ln_inv_item_id NUMBER;
1144
1145 lv_asset_applicable VARCHAR2(1);
1146 lb_asset_applicable BOOLEAN := FALSE;
1147 lv_parent_code VARCHAR2(30);
1148 halt_validation EXCEPTION;
1149
1150 CURSOR c_get_quote_lop_info IS
1151 SELECT quote.expected_start_date,
1152 leaseopp.inv_org_id,
1153 leaseopp.currency_code,
1154 leaseopp.ORG_ID,
1155 leaseopp.CUST_ACCT_ID,
1156 quote.product_id,
1157 leaseopp.SALES_REP_ID
1158 FROM
1159 okl_lease_quotes_b quote,
1160 okl_lease_opportunities_b leaseopp
1161 WHERE
1162 quote.id = p_quote_id
1163 AND quote.parent_object_id = leaseopp.id
1164 AND quote.parent_object_code = 'LEASEOPP';
1165
1166 CURSOR c_get_quote_lap_info IS
1167 SELECT quote.expected_start_date,
1168 leaseapp.inv_org_id,
1169 leaseapp.currency_code,
1170 leaseapp.ORG_ID,
1171 leaseapp.CUST_ACCT_ID,
1172 quote.product_id,
1173 leaseapp.SALES_REP_ID
1174 FROM
1175 okl_lease_quotes_b quote,
1176 okl_lease_applications_b leaseapp
1177 WHERE
1178 quote.id = p_quote_id
1179 AND quote.parent_object_id = leaseapp.id
1180 AND quote.parent_object_code = 'LEASEAPP';
1181
1182 CURSOR c_get_inv_items IS
1183 SELECT assetcomp.INV_ITEM_ID
1184 FROM okl_assets_b asset,
1185 okl_asset_components_b assetcomp
1186 WHERE
1187 asset.id = assetcomp.asset_id
1188 AND assetcomp.PRIMARY_COMPONENT = 'YES'
1189 AND asset.parent_object_id = p_quote_id
1190 AND asset.parent_object_code = 'LEASEQUOTE';
1191
1192 BEGIN
1193 SELECT parent_object_code
1194 INTO lv_parent_code
1195 FROM okl_lease_quotes_b
1196 where id = p_quote_id;
1197
1198 IF (lv_parent_code = 'LEASEOPP') THEN
1199 OPEN c_get_quote_lop_info;
1200 FETCH c_get_quote_lop_info into ld_start_date, ln_inv_org_id,
1201 lv_currency_code, ln_org_id, ln_cust_acct_id,
1202 ln_product_id, ln_sales_rep_id;
1203 CLOSE c_get_quote_lop_info;
1204 ELSIF (lv_parent_code = 'LEASEAPP') THEN
1205 OPEN c_get_quote_lap_info;
1206 FETCH c_get_quote_lap_info into ld_start_date, ln_inv_org_id,
1207 lv_currency_code, ln_org_id, ln_cust_acct_id,
1208 ln_product_id, ln_sales_rep_id;
1209 CLOSE c_get_quote_lap_info;
1210 END IF;
1211
1212 FOR l_get_inv_items IN c_get_inv_items LOOP
1213
1214 lv_asset_applicable := okl_asset_subsidy_pvt.validate_subsidy_applicability
1215 (p_subsidy_id => p_subsidy_id,
1216 p_start_date => ld_start_date,
1217 p_inv_item_id => l_get_inv_items.inv_item_id,
1218 p_inv_org_id => ln_inv_org_id,
1219 p_currency_code => lv_currency_code,
1220 p_authoring_org_id => ln_org_id,
1221 p_cust_account_id => ln_cust_acct_id,
1222 p_pdt_id => ln_product_id,
1223 p_sales_rep_id => ln_sales_rep_id);
1224
1225
1226 IF (lv_asset_applicable = 'Y') THEN
1227 lb_asset_applicable := TRUE;
1228 END IF;
1229
1230 EXIT WHEN (lb_asset_applicable = TRUE);
1231 END LOOP;
1232
1233 RETURN lv_asset_applicable;
1234 EXCEPTION
1235 When halt_validation then
1236 Return(lv_asset_applicable);
1237 When others then
1238 lv_asset_applicable := 'N';
1239 Return(lv_asset_applicable);
1240 END validate_subsidy;
1241
1242 -------------------------------------------
1243 -- PROCEDURE validate_subsidy_applicability
1244 -------------------------------------------
1245 FUNCTION validate_subsidy_applicability(p_asset_id IN NUMBER,
1246 p_subsidy_id IN NUMBER)
1247 RETURN VARCHAR2 IS
1248
1249 ld_start_date DATE;
1250 lv_currency_code VARCHAR2(15);
1251 ln_product_id NUMBER;
1252 ln_cust_acct_id NUMBER;
1253 ln_sales_rep_id NUMBER;
1254 ln_org_id NUMBER;
1255 ln_inv_org_id NUMBER;
1256 ln_inv_item_id NUMBER;
1257
1258 lv_asset_applicable VARCHAR2(1);
1259 lv_parent_code VARCHAR2(30);
1260 halt_validation EXCEPTION;
1261
1262 CURSOR c_get_quote_lop_info IS
1263 SELECT quote.expected_start_date,
1264 assetcomp.INV_ITEM_ID,
1265 leaseopp.inv_org_id,
1266 leaseopp.currency_code,
1267 leaseopp.ORG_ID,
1268 leaseopp.CUST_ACCT_ID,
1269 quote.product_id,
1270 leaseopp.SALES_REP_ID
1271 FROM
1272 okl_assets_b asset,
1273 okl_asset_components_b assetcomp,
1274 okl_lease_quotes_b quote,
1275 okl_lease_opportunities_b leaseopp
1276 WHERE
1277 asset.id = p_asset_id
1278 AND asset.id = assetcomp.asset_id
1279 AND assetcomp.PRIMARY_COMPONENT = 'YES'
1280 AND asset.parent_object_id = quote.id
1281 AND asset.parent_object_code = 'LEASEQUOTE'
1282 AND quote.parent_object_id = leaseopp.id;
1283
1284 CURSOR c_get_quote_lap_info IS
1285 SELECT quote.expected_start_date,
1286 assetcomp.INV_ITEM_ID,
1287 leaseapp.inv_org_id,
1288 leaseapp.currency_code,
1289 leaseapp.ORG_ID,
1290 leaseapp.CUST_ACCT_ID,
1291 quote.product_id,
1292 leaseapp.SALES_REP_ID
1293 FROM
1294 okl_assets_b asset,
1295 okl_asset_components_b assetcomp,
1296 okl_lease_quotes_b quote,
1297 okl_lease_applications_b leaseapp
1298 WHERE
1299 asset.id = p_asset_id
1300 AND asset.id = assetcomp.asset_id
1301 AND assetcomp.PRIMARY_COMPONENT = 'YES'
1302 AND asset.parent_object_id = quote.id
1303 AND asset.parent_object_code = 'LEASEQUOTE'
1304 AND quote.parent_object_id = leaseapp.id;
1305
1306 BEGIN
1307 SELECT lsq.parent_object_code
1308 INTO lv_parent_code
1309 FROM okl_assets_b ast, okl_lease_quotes_b lsq
1310 where ast.parent_object_id = lsq.id
1311 and ast.parent_object_code = 'LEASEQUOTE'
1312 and ast.id = p_asset_id;
1313
1314 IF (lv_parent_code = 'LEASEOPP') THEN
1315 OPEN c_get_quote_lop_info;
1316 FETCH c_get_quote_lop_info into ld_start_date, ln_inv_item_id, ln_inv_org_id,
1317 lv_currency_code, ln_org_id, ln_cust_acct_id,
1318 ln_product_id, ln_sales_rep_id;
1319 CLOSE c_get_quote_lop_info;
1320 ELSIF (lv_parent_code = 'LEASEAPP') THEN
1321 OPEN c_get_quote_lap_info;
1322 FETCH c_get_quote_lap_info into ld_start_date, ln_inv_item_id, ln_inv_org_id,
1323 lv_currency_code, ln_org_id, ln_cust_acct_id,
1324 ln_product_id, ln_sales_rep_id;
1325 CLOSE c_get_quote_lap_info;
1326 END IF;
1327
1328 lv_asset_applicable := okl_asset_subsidy_pvt.validate_subsidy_applicability
1329 (p_subsidy_id => p_subsidy_id,
1330 p_start_date => ld_start_date,
1331 p_inv_item_id => ln_inv_item_id,
1332 p_inv_org_id => ln_inv_org_id,
1333 p_currency_code => lv_currency_code,
1334 p_authoring_org_id => ln_org_id,
1335 p_cust_account_id => ln_cust_acct_id,
1336 p_pdt_id => ln_product_id,
1337 p_sales_rep_id => ln_sales_rep_id);
1338
1339 RETURN lv_asset_applicable;
1340 EXCEPTION
1341 When halt_validation then
1342 Return(lv_asset_applicable);
1343 When others then
1344 lv_asset_applicable := 'N';
1345 Return(lv_asset_applicable);
1346 END validate_subsidy_applicability;
1347
1348
1349 -------------------------------
1350 -- PROCEDURE get_adjust_tbl
1351 -------------------------------
1352 PROCEDURE get_adjust_tbl (p_source_quote_id IN NUMBER
1353 ,x_adjust_tbl OUT NOCOPY cdj_tbl_type
1354 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
1355
1356 l_program_name CONSTANT VARCHAR2(30) := 'get_adjust_tbl';
1357 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1358 i BINARY_INTEGER := 0;
1359
1360 CURSOR c_db_adjust IS
1361 SELECT
1362 attribute_category
1363 ,attribute1
1364 ,attribute2
1365 ,attribute3
1366 ,attribute4
1367 ,attribute5
1368 ,attribute6
1369 ,attribute7
1370 ,attribute8
1371 ,attribute9
1372 ,attribute10
1373 ,attribute11
1374 ,attribute12
1375 ,attribute13
1376 ,attribute14
1377 ,attribute15
1378 ,parent_object_code
1379 ,parent_object_id
1380 ,adjustment_source_type
1381 ,adjustment_source_id
1382 ,basis
1383 ,value
1384 ,processing_type
1385 ,supplier_id
1386 ,default_subsidy_amount
1387 --Bug # 5142940 ssdeshpa start
1388 ,stream_type_id
1389 --Bug # 5142940 ssdeshpa start
1390 ,short_description
1391 ,description
1392 ,comments
1393 from okl_cost_adjustments_v
1394 where parent_object_code = 'ASSET'
1395 and adjustment_source_type in ('DOWN_PAYMENT', 'TRADEIN', 'SUBSIDY')
1396 and parent_object_id IN (select id
1397 from okl_assets_b
1398 where parent_object_id = p_source_quote_id
1399 and parent_object_code = 'LEASEQUOTE');
1400
1401 BEGIN
1402 FOR l_db_adjust IN c_db_adjust LOOP
1403 x_adjust_tbl(i).attribute_category := l_db_adjust.attribute_category;
1404 x_adjust_tbl(i).attribute1 := l_db_adjust.attribute1;
1405 x_adjust_tbl(i).attribute2 := l_db_adjust.attribute2;
1406 x_adjust_tbl(i).attribute3 := l_db_adjust.attribute3;
1407 x_adjust_tbl(i).attribute4 := l_db_adjust.attribute4;
1408 x_adjust_tbl(i).attribute5 := l_db_adjust.attribute5;
1409 x_adjust_tbl(i).attribute6 := l_db_adjust.attribute6;
1410 x_adjust_tbl(i).attribute7 := l_db_adjust.attribute7;
1411 x_adjust_tbl(i).attribute8 := l_db_adjust.attribute8;
1412 x_adjust_tbl(i).attribute9 := l_db_adjust.attribute9;
1413 x_adjust_tbl(i).attribute10 := l_db_adjust.attribute10;
1414 x_adjust_tbl(i).attribute11 := l_db_adjust.attribute11;
1415 x_adjust_tbl(i).attribute12 := l_db_adjust.attribute12;
1416 x_adjust_tbl(i).attribute13 := l_db_adjust.attribute13;
1417 x_adjust_tbl(i).attribute14 := l_db_adjust.attribute14;
1418 x_adjust_tbl(i).attribute15 := l_db_adjust.attribute15;
1419 x_adjust_tbl(i).parent_object_code := l_db_adjust.parent_object_code;
1420 x_adjust_tbl(i).parent_object_id := l_db_adjust.parent_object_id;
1421 x_adjust_tbl(i).adjustment_source_type := l_db_adjust.adjustment_source_type;
1422 x_adjust_tbl(i).adjustment_source_id := l_db_adjust.adjustment_source_id;
1423 x_adjust_tbl(i).basis := l_db_adjust.basis;
1424 x_adjust_tbl(i).value := l_db_adjust.value;
1425 x_adjust_tbl(i).processing_type := l_db_adjust.processing_type;
1426 x_adjust_tbl(i).supplier_id := l_db_adjust.supplier_id;
1427 x_adjust_tbl(i).default_subsidy_amount := l_db_adjust.default_subsidy_amount;
1428 --Bug # 5142940 ssdeshpa start
1429 x_adjust_tbl(i).stream_type_id := l_db_adjust.stream_type_id;
1430 --Bug # 5142940 ssdeshpa start
1431 x_adjust_tbl(i).short_description := l_db_adjust.short_description;
1432 x_adjust_tbl(i).description := l_db_adjust.description;
1433 x_adjust_tbl(i).comments := l_db_adjust.comments;
1434 i := i + 1;
1435 END LOOP;
1436
1437 x_return_status := G_RET_STS_SUCCESS;
1438 EXCEPTION
1439
1440 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1441 x_return_status := G_RET_STS_ERROR;
1442
1443 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1444 x_return_status := G_RET_STS_UNEXP_ERROR;
1445
1446 WHEN OTHERS THEN
1447 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1448 p_msg_name => G_DB_ERROR,
1449 p_token1 => G_PROG_NAME_TOKEN,
1450 p_token1_value => l_program_name,
1451 p_token2 => G_SQLCODE_TOKEN,
1452 p_token2_value => sqlcode,
1453 p_token3 => G_SQLERRM_TOKEN,
1454 p_token3_value => sqlerrm);
1455
1456 x_return_status := G_RET_STS_UNEXP_ERROR;
1457
1458 END get_adjust_tbl;
1459
1460 -------------------------------
1461 -- PROCEDURE get_deleted_addons
1462 -------------------------------
1463 PROCEDURE get_deleted_addons (p_asset_id IN NUMBER,
1464 p_component_tbl IN asset_component_tbl_type,
1465 x_deleted_addon_tbl OUT NOCOPY component_tbl_type,
1466 x_return_status OUT NOCOPY VARCHAR2) IS
1467
1468 l_program_name CONSTANT VARCHAR2(30) := 'get_deleted_addons';
1469 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1470
1471 CURSOR c_db_addons IS
1472 SELECT id
1473 FROM okl_asset_components_b
1474 WHERE asset_id = p_asset_id
1475 AND primary_component = 'NO';
1476
1477 l_addon_tbl component_tbl_type;
1478 l_delete_flag VARCHAR2(1);
1479 i BINARY_INTEGER := 0;
1480
1481 BEGIN
1482
1483 FOR l_db_addon IN c_db_addons LOOP
1484
1485 l_delete_flag := 'Y';
1486
1487 FOR j IN p_component_tbl.FIRST .. p_component_tbl.LAST LOOP
1488 IF p_component_tbl.EXISTS(j) THEN
1489 IF l_db_addon.id = p_component_tbl(j).id THEN
1490 l_delete_flag := 'N';
1491 END IF;
1492 END IF;
1493 END LOOP;
1494
1495 IF l_delete_flag = 'Y' THEN
1496 l_addon_tbl(i).id := l_db_addon.id;
1497 i := i + 1;
1498 END IF;
1499
1500 END LOOP;
1501
1502 x_deleted_addon_tbl := l_addon_tbl;
1503
1504 EXCEPTION
1505
1506 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1507
1508 x_return_status := G_RET_STS_ERROR;
1509
1510 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1511
1512 x_return_status := G_RET_STS_UNEXP_ERROR;
1513
1514 WHEN OTHERS THEN
1515
1516 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1517 p_msg_name => G_DB_ERROR,
1518 p_token1 => G_PROG_NAME_TOKEN,
1519 p_token1_value => l_api_name,
1520 p_token2 => G_SQLCODE_TOKEN,
1521 p_token2_value => sqlcode,
1522 p_token3 => G_SQLERRM_TOKEN,
1523 p_token3_value => sqlerrm);
1524
1525 x_return_status := G_RET_STS_UNEXP_ERROR;
1526
1527 END get_deleted_addons;
1528
1529 --------------------------------------
1530 -- PROCEDURE is_asset_adj_defined
1531 --------------------------------------
1532 FUNCTION is_asset_adj_defined(p_assoc_assets_tbl IN asset_adjustment_tbl_type,
1533 x_asset_id OUT NOCOPY VARCHAR2)
1534 RETURN VARCHAR2 IS
1535
1536 l_program_name CONSTANT VARCHAR2(30) := 'is_asset_adj_defined';
1537 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1538
1539 CURSOR c_check_asset_adj (p_asset_id IN NUMBER, p_adj_type IN VARCHAR2) IS
1540 SELECT 'Y'
1541 FROM OKL_COST_ADJUSTMENTS_B
1542 WHERE PARENT_OBJECT_ID = p_asset_id
1543 AND PARENT_OBJECT_CODE = 'ASSET'
1544 AND ADJUSTMENT_SOURCE_TYPE = p_adj_type;
1545
1546 c_check_flag VARCHAR2(1) := 'N';
1547
1548 BEGIN
1549
1550 IF p_assoc_assets_tbl.COUNT > 0 THEN
1551 FOR i IN p_assoc_assets_tbl.FIRST .. p_assoc_assets_tbl.LAST LOOP
1552 IF p_assoc_assets_tbl.EXISTS(i) THEN
1553 OPEN c_check_asset_adj(p_asset_id => p_assoc_assets_tbl(i).parent_object_id,
1554 p_adj_type => p_assoc_assets_tbl(i).adjustment_source_type);
1555 FETCH c_check_asset_adj INTO c_check_flag;
1556 CLOSE c_check_asset_adj;
1557
1558 IF (c_check_flag = 'Y') THEN
1559 x_asset_id := p_assoc_assets_tbl(i).parent_object_id;
1560 RETURN c_check_flag;
1561 END IF;
1562
1563 END IF;
1564 END LOOP;
1565 END IF;
1566
1567 RETURN c_check_flag;
1568
1569 END is_asset_adj_defined;
1570
1571 -----------------------------------
1572 -- PROCEDURE validate_adjust_assets
1573 -----------------------------------
1574 PROCEDURE validate_adjust_assets (p_adj_amount IN NUMBER,
1575 p_assoc_assets_tbl IN asset_adjustment_tbl_type,
1576 x_return_status OUT NOCOPY VARCHAR2) IS
1577
1578 l_program_name CONSTANT VARCHAR2(30) := 'validate_adjust_assets';
1579 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1580
1581 l_link_exists_yn VARCHAR2(1);
1582 l_assoc_total NUMBER := 0;
1583 l_null_found VARCHAR2(1) := 'N';
1584 l_amt_flag VARCHAR2(1) := 'N';
1585 BEGIN
1586
1587 IF p_assoc_assets_tbl.COUNT > 0 THEN
1588 FOR i IN p_assoc_assets_tbl.FIRST .. p_assoc_assets_tbl.LAST LOOP
1589 IF p_assoc_assets_tbl.EXISTS(i) THEN
1590
1591 IF p_assoc_assets_tbl(i).value IS NOT NULL THEN
1592 l_amt_flag := 'Y';
1593 l_assoc_total := l_assoc_total + p_assoc_assets_tbl(i).value;
1594 ELSE
1595 l_null_found := 'Y';
1596 END IF;
1597
1598 IF (p_assoc_assets_tbl(i).value IS NULL) AND l_amt_flag = 'Y' THEN
1599 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1600 p_msg_name => 'OKL_LINKASSET_NULL_FOUND');
1601
1602 RAISE OKL_API.G_EXCEPTION_ERROR;
1603 ELSIF (l_null_found = 'Y' AND l_amt_flag = 'Y') THEN
1604 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1605 p_msg_name => 'OKL_LINKASSET_NULL_FOUND');
1606 RAISE OKL_API.G_EXCEPTION_ERROR;
1607 END IF;
1608 END IF;
1609 END LOOP;
1610 IF p_assoc_assets_tbl.exists(p_assoc_assets_tbl.FIRST) THEN
1611 IF p_assoc_assets_tbl(p_assoc_assets_tbl.FIRST).basis = 'FIXED' THEN
1612 IF l_amt_flag = 'Y' AND l_assoc_total <> p_adj_amount THEN
1613 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1614 p_msg_name => 'OKL_SALES_ADJ_AMT_MISMATCH',
1615 p_token1 => 'LINE_COST',
1616 p_token1_value => p_adj_amount,
1617 p_token2 => 'ASSOC_TOTAL',
1618 p_token2_value => l_assoc_total);
1619
1620 RAISE OKL_API.G_EXCEPTION_ERROR;
1621 END IF;
1622 END IF;
1623 END IF;
1624 END IF;
1625
1626 x_return_status := G_RET_STS_SUCCESS;
1627
1628 EXCEPTION
1629
1630 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1631
1632 x_return_status := G_RET_STS_ERROR;
1633
1634 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1635
1636 x_return_status := G_RET_STS_UNEXP_ERROR;
1637
1638 WHEN OTHERS THEN
1639
1640 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1641 p_msg_name => G_DB_ERROR,
1642 p_token1 => G_PROG_NAME_TOKEN,
1643 p_token1_value => l_program_name,
1644 p_token2 => G_SQLCODE_TOKEN,
1645 p_token2_value => sqlcode,
1646 p_token3 => G_SQLERRM_TOKEN,
1647 p_token3_value => sqlerrm);
1648
1649 x_return_status := G_RET_STS_UNEXP_ERROR;
1650
1651 END validate_adjust_assets;
1652
1653 ----------------------------------------
1654 -- PROCEDURE get_deleted_adjusted_assets
1655 ----------------------------------------
1656 PROCEDURE get_deleted_adjusted_assets (p_adj_type IN VARCHAR2,
1657 p_quote_id IN NUMBER,
1658 p_adjustment_tbl IN asset_adjustment_tbl_type,
1659 x_deleted_adjust_tbl OUT NOCOPY asset_adj_tbl_type,
1660 x_return_status OUT NOCOPY VARCHAR2) IS
1661
1662 l_program_name CONSTANT VARCHAR2(30) := 'get_deleted_adjusted_assets';
1663 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1664
1665 CURSOR c_db_adj_assets IS
1666 SELECT costadj.id, costadj.parent_object_id
1667 FROM okl_cost_adjustments_b costadj,
1668 okl_assets_b asset
1669 WHERE costadj.adjustment_source_type = p_adj_type
1670 AND costadj.parent_object_id = asset.id
1671 AND costadj.parent_object_code = 'ASSET'
1672 AND asset.parent_object_id = p_quote_id
1673 AND asset.parent_object_code = 'LEASEQUOTE';
1674
1675 CURSOR c_db_subs_assets(p_adj_source_id IN NUMBER) IS
1676 SELECT costadj.id, costadj.parent_object_id
1677 FROM okl_cost_adjustments_b costadj,
1678 okl_assets_b asset
1679 WHERE costadj.adjustment_source_type = p_adj_type
1680 AND costadj.adjustment_source_id = p_adj_source_id
1681 AND costadj.parent_object_id = asset.id
1682 AND costadj.parent_object_code = 'ASSET'
1683 AND asset.parent_object_id = p_quote_id
1684 AND asset.parent_object_code = 'LEASEQUOTE';
1685
1686 l_asset_adj_tbl asset_adj_tbl_type;
1687 l_delete_flag VARCHAR2(1);
1688 i BINARY_INTEGER := 0;
1689 ln_index NUMBER;
1690
1691 BEGIN
1692
1693 IF (p_adj_type IN ('DOWN_PAYMENT', 'TRADEIN')) THEN
1694
1695 FOR l_db_adj_assets IN c_db_adj_assets LOOP
1696
1697 l_delete_flag := 'Y';
1698
1699 FOR j IN p_adjustment_tbl.FIRST .. p_adjustment_tbl.LAST LOOP
1700 IF p_adjustment_tbl.EXISTS(j) THEN
1701 IF l_db_adj_assets.id = p_adjustment_tbl(j).id THEN
1702 l_delete_flag := 'N';
1703 END IF;
1704 END IF;
1705 END LOOP;
1706
1707 IF l_delete_flag = 'Y' THEN
1708 l_asset_adj_tbl(i).id := l_db_adj_assets.id;
1709 i := i + 1;
1710 END IF;
1711
1712 END LOOP;
1713 ELSIF (p_adj_type = 'SUBSIDY') THEN
1714
1715 ln_index := p_adjustment_tbl.FIRST;
1716
1717 IF (p_adjustment_tbl.COUNT > 0) THEN
1718 FOR l_db_subs_assets IN c_db_subs_assets(p_adj_source_id => p_adjustment_tbl(ln_index).adjustment_source_id) LOOP
1719
1720 l_delete_flag := 'Y';
1721
1722 FOR j IN p_adjustment_tbl.FIRST .. p_adjustment_tbl.LAST LOOP
1723 IF p_adjustment_tbl.EXISTS(j) THEN
1724 IF l_db_subs_assets.id = p_adjustment_tbl(j).id THEN
1725 l_delete_flag := 'N';
1726 END IF;
1727 END IF;
1728 END LOOP;
1729
1730 IF l_delete_flag = 'Y' THEN
1731 l_asset_adj_tbl(i).id := l_db_subs_assets.id;
1732 i := i + 1;
1733 END IF;
1734 END LOOP;
1735 END IF;
1736 END IF;
1737
1738 x_deleted_adjust_tbl := l_asset_adj_tbl;
1739
1740 EXCEPTION
1741
1742 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1743
1744 x_return_status := G_RET_STS_ERROR;
1745
1746 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1747
1748 x_return_status := G_RET_STS_UNEXP_ERROR;
1749
1750 WHEN OTHERS THEN
1751
1752 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1753 p_msg_name => G_DB_ERROR,
1754 p_token1 => G_PROG_NAME_TOKEN,
1755 p_token1_value => l_api_name,
1756 p_token2 => G_SQLCODE_TOKEN,
1757 p_token2_value => sqlcode,
1758 p_token3 => G_SQLERRM_TOKEN,
1759 p_token3_value => sqlerrm);
1760
1761 x_return_status := G_RET_STS_UNEXP_ERROR;
1762
1763 END get_deleted_adjusted_assets;
1764
1765 -------------------------
1766 -- PROCEDURE set_defaults
1767 -------------------------
1768 PROCEDURE set_defaults (p_asset_rec IN OUT NOCOPY asset_rec_type,
1769 p_component_tbl IN OUT NOCOPY asset_component_tbl_type,
1770 x_return_status OUT NOCOPY VARCHAR2) IS
1771
1772 l_program_name CONSTANT VARCHAR2(30) := 'set_defaults';
1773 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1774
1775 l_units PLS_INTEGER;
1776 l_currency_code VARCHAR2(15);
1777 l_oec NUMBER;
1778 lv_parent_object_code VARCHAR2(30);
1779
1780 CURSOR c_get_parent_object_code(parent_object_id NUMBER) IS
1781 SELECT parent_object_code
1782 FROM okl_lease_quotes_b
1783 WHERE id = p_asset_rec.parent_object_id;
1784
1785 BEGIN
1786
1787 FOR i IN p_component_tbl.FIRST .. p_component_tbl.LAST LOOP
1788 IF p_component_tbl.EXISTS(i) THEN
1789 IF p_component_tbl(i).primary_component = 'YES' THEN
1790 l_units := p_component_tbl(i).number_of_units;
1791 END IF;
1792 END IF;
1793 END LOOP;
1794
1795 l_oec := 0;
1796 FOR i IN p_component_tbl.FIRST .. p_component_tbl.LAST LOOP
1797 IF p_component_tbl.EXISTS(i) THEN
1798 l_oec := l_oec + p_component_tbl(i).unit_cost * l_units;
1799 END IF;
1800 END LOOP;
1801
1802 IF l_oec = 0 THEN
1803 p_asset_rec.oec := 0;
1804 ELSE
1805 OPEN c_get_parent_object_code(parent_object_id => p_asset_rec.parent_object_id);
1806 FETCH c_get_parent_object_code INTO lv_parent_object_code;
1807 CLOSE c_get_parent_object_code;
1808
1809 IF (lv_parent_object_code = 'LEASEOPP') THEN
1810 SELECT currency_code
1811 INTO l_currency_code
1812 FROM okl_lease_opportunities_b lop,
1813 okl_lease_quotes_b lsq
1814 WHERE lsq.parent_object_code = lv_parent_object_code
1815 AND lsq.parent_object_id = lop.id
1816 AND lsq.id = p_asset_rec.parent_object_id;
1817 ELSIF (lv_parent_object_code = 'LEASEAPP') THEN
1818 SELECT currency_code
1819 INTO l_currency_code
1820 FROM okl_lease_applications_b lap,
1821 okl_lease_quotes_b lsq
1822 WHERE lsq.parent_object_code = lv_parent_object_code
1823 AND lsq.parent_object_id = lap.id
1824 AND lsq.id = p_asset_rec.parent_object_id;
1825 END IF;
1826
1827 p_asset_rec.oec := okl_accounting_util.validate_amount(p_amount => l_oec, p_currency_code => l_currency_code);
1828 END IF;
1829
1830 EXCEPTION
1831
1832 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1833
1834 x_return_status := G_RET_STS_ERROR;
1835
1836 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1837
1838 x_return_status := G_RET_STS_UNEXP_ERROR;
1839
1840 WHEN OTHERS THEN
1841
1842 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1843 p_msg_name => G_DB_ERROR,
1844 p_token1 => G_PROG_NAME_TOKEN,
1845 p_token1_value => l_api_name,
1846 p_token2 => G_SQLCODE_TOKEN,
1847 p_token2_value => sqlcode,
1848 p_token3 => G_SQLERRM_TOKEN,
1849 p_token3_value => sqlerrm);
1850
1851 x_return_status := G_RET_STS_UNEXP_ERROR;
1852
1853 END set_defaults;
1854
1855 -------------------------------------
1856 -- PROCEDURE duplicate_adjustments
1857 -------------------------------------
1858 PROCEDURE duplicate_adjustments(p_api_version IN NUMBER,
1859 p_init_msg_list IN VARCHAR2,
1860 p_source_quote_id IN NUMBER,
1861 p_target_quote_id IN NUMBER,
1862 x_msg_count OUT NOCOPY NUMBER,
1863 x_msg_data OUT NOCOPY VARCHAR2,
1864 x_return_status OUT NOCOPY VARCHAR2) IS
1865
1866 l_program_name CONSTANT VARCHAR2(30) := 'duplicate_adjustments';
1867 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1868
1869 l_adjust_tbl cdj_tbl_type;
1870 lx_adjust_tbl cdj_tbl_type;
1871
1872 CURSOR c_get_new_asset_id (p_source_asset_id IN NUMBER) IS
1873 SELECT id
1874 FROM OKL_ASSETS_B
1875 WHERE ORIG_ASSET_ID = p_source_asset_id
1876 AND PARENT_OBJECT_ID = p_target_quote_id;
1877
1878 BEGIN
1879
1880 get_adjust_tbl (p_source_quote_id => p_source_quote_id
1881 ,x_adjust_tbl => l_adjust_tbl
1882 ,x_return_status => x_return_status);
1883 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1884 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1885 ELSIF x_return_status = G_RET_STS_ERROR THEN
1886 RAISE OKL_API.G_EXCEPTION_ERROR;
1887 END IF;
1888
1889 IF (l_adjust_tbl.COUNT > 0) THEN
1890 FOR i IN l_adjust_tbl.FIRST .. l_adjust_tbl.LAST LOOP
1891 IF l_adjust_tbl.EXISTS(i) THEN
1892 OPEN c_get_new_asset_id(p_source_asset_id => l_adjust_tbl(i).parent_object_id);
1893 FETCH c_get_new_asset_id INTO l_adjust_tbl(i).parent_object_id;
1894 CLOSE c_get_new_asset_id;
1895 END IF;
1896 END LOOP;
1897
1898 -- Validate Subsidy Usage
1899 IF (l_adjust_tbl(0).adjustment_source_type = 'SUBSIDY') THEN
1900 validate_subsidy_usage(p_asset_id => l_adjust_tbl(0).parent_object_id,
1901 p_input_adj_tbl => l_adjust_tbl,
1902 x_return_status => x_return_status);
1903 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1904 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1905 ELSIF x_return_status = G_RET_STS_ERROR THEN
1906 RAISE OKL_API.G_EXCEPTION_ERROR;
1907 END IF;
1908 END IF;
1909
1910 okl_cdj_pvt.insert_row (p_api_version => G_API_VERSION
1911 ,p_init_msg_list => G_FALSE
1912 ,x_return_status => x_return_status
1913 ,x_msg_count => x_msg_count
1914 ,x_msg_data => x_msg_data
1915 ,p_cdjv_tbl => l_adjust_tbl
1916 ,x_cdjv_tbl => lx_adjust_tbl );
1917 --Bug # 5142940 ssdeshpa start
1918 l_adjust_tbl := lx_adjust_tbl;
1919 IF (l_adjust_tbl.COUNT > 0) THEN
1920 FOR i IN l_adjust_tbl.FIRST .. l_adjust_tbl.LAST LOOP
1921 IF l_adjust_tbl.EXISTS(i) THEN
1922 IF(l_adjust_tbl(i).adjustment_source_type='DOWN_PAYMENT' AND
1923 l_adjust_tbl(i).processing_type='BILL' AND
1924 l_adjust_tbl(i).stream_type_id IS NOT NULL) THEN
1925 --Create Rec Structure for Cash flows
1926 process_adj_cashflows(p_cdjv_rec => l_adjust_tbl(i)
1927 ,p_event_mode => 'create'
1928 ,x_msg_count => x_msg_count
1929 ,x_msg_data => x_msg_data
1930 ,x_return_status => x_return_status);
1931
1932 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1933 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1934 ELSIF x_return_status = G_RET_STS_ERROR THEN
1935 RAISE OKL_API.G_EXCEPTION_ERROR;
1936 END IF;
1937 END IF;
1938 END IF;
1939 END LOOP;
1940 END IF;
1941
1942 --Bug # 5142940 ssdeshpa end
1943 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1944 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1945 ELSIF x_return_status = G_RET_STS_ERROR THEN
1946 RAISE OKL_API.G_EXCEPTION_ERROR;
1947 END IF;
1948 END IF;
1949
1950 x_return_status := G_RET_STS_SUCCESS;
1951
1952 EXCEPTION
1953
1954 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1955
1956 x_return_status := G_RET_STS_ERROR;
1957
1958 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1959
1960 x_return_status := G_RET_STS_UNEXP_ERROR;
1961
1962 WHEN OTHERS THEN
1963
1964 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1965 p_msg_name => G_DB_ERROR,
1966 p_token1 => G_PROG_NAME_TOKEN,
1967 p_token1_value => l_api_name,
1968 p_token2 => G_SQLCODE_TOKEN,
1969 p_token2_value => sqlcode,
1970 p_token3 => G_SQLERRM_TOKEN,
1971 p_token3_value => sqlerrm);
1972
1973 x_return_status := G_RET_STS_UNEXP_ERROR;
1974 END duplicate_adjustments;
1975
1976 ---------------------
1977 -- PROCEDURE validate
1978 ---------------------
1979 PROCEDURE validate (p_asset_rec IN OUT NOCOPY asset_rec_type,
1980 p_component_tbl IN OUT NOCOPY asset_component_tbl_type,
1981 x_return_status OUT NOCOPY VARCHAR2) IS
1982
1983 l_program_name CONSTANT VARCHAR2(30) := 'validate';
1984 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1985
1986 lv_pricing_method OKL_LEASE_QUOTES_B.PRICING_METHOD%TYPE;
1987 ln_check_pec_percentage NUMBER;
1988 ln_sum_oec_percentage NUMBER;
1989
1990 BEGIN
1991 FOR i IN p_component_tbl.FIRST .. p_component_tbl.LAST LOOP
1992 IF p_component_tbl.EXISTS(i) THEN
1993 IF p_component_tbl(i).primary_component = 'YES' THEN
1994 IF (TRUNC(p_component_tbl(i).number_of_units) <> p_component_tbl(i).number_of_units) OR
1995 (p_component_tbl(i).number_of_units <= 0) THEN
1996 OKL_API.set_message(p_app_name => G_APP_NAME,
1997 p_msg_name => G_UNITS_VALUE);
1998 RAISE OKL_API.G_EXCEPTION_ERROR;
1999 END IF;
2000
2001 -- Validate Asset percentage in case of 'Solve for Financed Amount'
2002 -- pricng method scenario
2003 SELECT PRICING_METHOD
2004 INTO lv_pricing_method
2005 FROM OKL_LEASE_QUOTES_B
2006 WHERE ID = p_asset_rec.parent_object_id;
2007
2008 IF (lv_pricing_method = 'SF') THEN
2009
2010 -- Check if the Asset OEC percentage is less than '0' and greater
2011 -- than '100'
2012 IF (p_asset_rec.oec_percentage <= 0 OR
2013 p_asset_rec.oec_percentage > 100) THEN
2014 OKL_API.set_message(p_app_name => G_APP_NAME,
2015 p_msg_name => 'OKL_CHECK_ASSET_PERCENTAGE');
2016 RAISE OKL_API.G_EXCEPTION_ERROR;
2017 END IF;
2018
2019 -- Check if the sum of all Asset OEC percentages greater than '100'
2020 SELECT SUM(OEC_PERCENTAGE)
2021 INTO ln_sum_oec_percentage
2022 FROM OKL_ASSETS_B
2023 WHERE PARENT_OBJECT_ID = p_asset_rec.parent_object_id
2024 AND ID NOT IN (SELECT ID FROM OKL_ASSETS_B WHERE ID = p_asset_rec.id);
2025
2026 IF (ln_sum_oec_percentage IS NULL) THEN
2027 ln_sum_oec_percentage := 0;
2028 END IF;
2029
2030 ln_check_pec_percentage :=
2031 ln_sum_oec_percentage + p_asset_rec.oec_percentage;
2032
2033 IF (ln_check_pec_percentage > 100) THEN
2034 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME
2035 ,p_msg_name => 'OKL_CHECK_TOTAL_ASSET_PERCENT'
2036 ,p_token1 => 'TOTAL'
2037 ,p_token1_value => ln_check_pec_percentage );
2038 RAISE OKL_API.G_EXCEPTION_ERROR;
2039 END IF;
2040 END IF;
2041 -- End
2042
2043 END IF;
2044 END IF;
2045 END LOOP;
2046
2047 EXCEPTION
2048
2049 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2050
2051 x_return_status := G_RET_STS_ERROR;
2052
2053 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2054
2055 x_return_status := G_RET_STS_UNEXP_ERROR;
2056
2057 WHEN OTHERS THEN
2058
2059 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2060 p_msg_name => G_DB_ERROR,
2061 p_token1 => G_PROG_NAME_TOKEN,
2062 p_token1_value => l_api_name,
2063 p_token2 => G_SQLCODE_TOKEN,
2064 p_token2_value => sqlcode,
2065 p_token3 => G_SQLERRM_TOKEN,
2066 p_token3_value => sqlerrm);
2067
2068 x_return_status := G_RET_STS_UNEXP_ERROR;
2069
2070 END validate;
2071
2072
2073 ------------------------------
2074 -- PROCEDURE create_adjustment
2075 ------------------------------
2076 PROCEDURE create_adjustment (p_api_version IN NUMBER,
2077 p_init_msg_list IN VARCHAR2,
2078 p_transaction_control IN VARCHAR2,
2079 p_asset_adj_tbl IN asset_adjustment_tbl_type,
2080 x_return_status OUT NOCOPY VARCHAR2,
2081 x_msg_count OUT NOCOPY NUMBER,
2082 x_msg_data OUT NOCOPY VARCHAR2) IS
2083
2084 l_program_name CONSTANT VARCHAR2(30) := 'create_adjustment';
2085 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2086
2087 l_asset_adj_tbl asset_adjustment_tbl_type;
2088 l_adj_assets_tbl asset_adj_tbl_type;
2089 lx_adj_assets_tbl asset_adj_tbl_type;
2090 l_p_id NUMBER;
2091 l_p_code VARCHAR2(30);
2092 lb_validate_subsidy_usage BOOLEAN;
2093
2094 asset_adj_defined VARCHAR2(1) := 'N';
2095 x_asset_id okl_cost_adjustments_b.adjustment_source_id%type;
2096 lv_asset_number okl_assets_b.asset_number%TYPE;
2097 ln_index NUMBER;
2098
2099 --Bug 7291307 : Adding starts
2100 cursor get_quote_id_csr(p_asset_id NUMBER) IS
2101 select PARENT_OBJECT_ID
2102 from okl_assets_b
2103 where id=p_asset_id
2104 and parent_object_code='LEASEQUOTE';
2105
2106 l_cost_adj_desc okl_cost_adjustments_tl.description%TYPE;
2107 l_sync_desc VARCHAR2(1):='N';
2108 l_quote_id okl_lease_quotes_b.id%TYPE;
2109 --Bug 7291307 : Adding end
2110
2111 BEGIN
2112 IF p_transaction_control = G_TRUE THEN
2113 SAVEPOINT l_program_name;
2114 END IF;
2115
2116 IF p_init_msg_list = G_TRUE THEN
2117 FND_MSG_PUB.initialize;
2118 END IF;
2119
2120 l_asset_adj_tbl := p_asset_adj_tbl;
2121
2122 ln_index := l_asset_adj_tbl.FIRST;
2123 -- Validate Adjustment assets amount
2124 IF (l_asset_adj_tbl(ln_index).adjustment_source_type <> 'SUBSIDY') THEN
2125 ----Fixing Bug # 4735811 ssdeshpa Start
2126 /*
2127 process_link_asset_amounts(p_adj_amount => l_asset_adj_tbl(ln_index).adjustment_amount,
2128 p_assoc_assets_tbl => l_asset_adj_tbl,
2129 x_return_status => x_return_status);
2130
2131 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2132 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2133 ELSIF x_return_status = G_RET_STS_ERROR THEN
2134 RAISE OKL_API.G_EXCEPTION_ERROR;
2135 END IF;
2136 */
2137 --Fixing Bug # 4735811 ssdeshpa End
2138 validate_adjust_assets(p_adj_amount => l_asset_adj_tbl(ln_index).adjustment_amount,
2139 p_assoc_assets_tbl => l_asset_adj_tbl,
2140 x_return_status => x_return_status);
2141 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2142 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2143 ELSIF x_return_status = G_RET_STS_ERROR THEN
2144 RAISE OKL_API.G_EXCEPTION_ERROR;
2145 END IF;
2146
2147 -- Check not to create multiple Down payment or Trade-in for an Asset
2148 -- Bug 5028117
2149 asset_adj_defined := is_asset_adj_defined(p_assoc_assets_tbl => l_asset_adj_tbl,
2150 x_asset_id => x_asset_id);
2151
2152 IF (asset_adj_defined = 'Y') THEN
2153
2154 SELECT asset_number
2155 INTO lv_asset_number
2156 FROM okl_assets_b
2157 where id = x_asset_id;
2158
2159 IF (l_asset_adj_tbl(ln_index).adjustment_source_type = 'DOWN_PAYMENT') THEN
2160 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2161 p_msg_name => 'OKL_SALES_DP_DUP_ENTRY',
2162 p_token1 => 'ASSET_NUMBER',
2163 p_token1_value => lv_asset_number);
2164 RAISE OKL_API.G_EXCEPTION_ERROR;
2165 ELSIF (l_asset_adj_tbl(ln_index).adjustment_source_type = 'TRADEIN') THEN
2166 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2167 p_msg_name => 'OKL_SALES_TI_DUP_ENTRY',
2168 p_token1 => 'ASSET_NUMBER',
2169 p_token1_value => lv_asset_number);
2170 RAISE OKL_API.G_EXCEPTION_ERROR;
2171 END IF;
2172 END IF;
2173 -- End fix for Bug 5028117
2174
2175 END IF;
2176 lb_validate_subsidy_usage := TRUE;
2177 FOR i IN l_asset_adj_tbl.FIRST .. l_asset_adj_tbl.LAST LOOP
2178 IF l_asset_adj_tbl.EXISTS(i) THEN
2179 --asawanka bug 5025239 fix starts
2180
2181 IF (l_asset_adj_tbl(i).value IS NOT NULL AND l_asset_adj_tbl(i).value < 0) THEN
2182 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2183 p_msg_name => 'OKL_ADJ_AMOUNT_NEGATIVE');
2184 RAISE OKL_API.G_EXCEPTION_ERROR;
2185 END IF;
2186 --asawanka bug 5025239 fix ends
2187 l_adj_assets_tbl(i).parent_object_code := l_asset_adj_tbl(i).parent_object_code;
2188 l_adj_assets_tbl(i).parent_object_id := l_asset_adj_tbl(i).parent_object_id;
2189 l_adj_assets_tbl(i).adjustment_source_type := l_asset_adj_tbl(i).adjustment_source_type;
2190 l_adj_assets_tbl(i).adjustment_source_id := l_asset_adj_tbl(i).adjustment_source_id;
2191 l_adj_assets_tbl(i).basis := l_asset_adj_tbl(i).basis;
2192 l_adj_assets_tbl(i).value := l_asset_adj_tbl(i).value;
2193 l_adj_assets_tbl(i).default_subsidy_amount := l_asset_adj_tbl(i).default_subsidy_amount;
2194 l_adj_assets_tbl(i).processing_type := l_asset_adj_tbl(i).processing_type;
2195 l_adj_assets_tbl(i).supplier_id := l_asset_adj_tbl(i).supplier_id;
2196 --Bug # 5142940 ssdeshpa start
2197 l_adj_assets_tbl(i).stream_type_id := l_asset_adj_tbl(i).stream_type_id;
2198 --Bug # 5142940 ssdeshpa end
2199 l_adj_assets_tbl(i).short_description := l_asset_adj_tbl(i).short_description;
2200 l_adj_assets_tbl(i).description := l_asset_adj_tbl(i).description;
2201 l_adj_assets_tbl(i).comments := l_asset_adj_tbl(i).comments;
2202 l_adj_assets_tbl(i).percent_basis_value := l_asset_adj_tbl(i).percent_basis_value;
2203 IF l_asset_adj_tbl(i).adjustment_source_id IS NULL THEN
2204 lb_validate_subsidy_usage := FALSE;
2205 END IF;
2206 --Bug 7291307 : get the description-adding start
2207 IF (l_asset_adj_tbl(i).adjustment_source_type = 'TRADEIN')
2208 and (l_sync_desc<>'Y') THEN
2209 l_cost_adj_desc :=l_asset_adj_tbl(i).description;
2210 l_sync_desc:='Y';
2211
2212 open get_quote_id_csr(l_adj_assets_tbl(i).parent_object_id);
2213 fetch get_quote_id_csr into l_quote_id;
2214 close get_quote_id_csr;
2215 END IF;
2216 --Bug 7291307 : get the description-adding end
2217 END IF;
2218 END LOOP;
2219
2220
2221 -- Validate Subsidy Usage
2222 IF (l_adj_assets_tbl.COUNT > 0) THEN
2223
2224 ln_index := l_adj_assets_tbl.FIRST;
2225
2226 IF (l_adj_assets_tbl(ln_index).adjustment_source_type = 'SUBSIDY' AND lb_validate_subsidy_usage) THEN
2227 validate_subsidy_usage(p_asset_id => l_adj_assets_tbl(ln_index).parent_object_id,
2228 p_input_adj_tbl => l_adj_assets_tbl,
2229 x_return_status => x_return_status);
2230 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2231 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2232 ELSIF x_return_status = G_RET_STS_ERROR THEN
2233 RAISE OKL_API.G_EXCEPTION_ERROR;
2234 END IF;
2235 END IF;
2236
2237 okl_cdj_pvt.insert_row (p_api_version => G_API_VERSION
2238 ,p_init_msg_list => G_FALSE
2239 ,x_return_status => x_return_status
2240 ,x_msg_count => x_msg_count
2241 ,x_msg_data => x_msg_data
2242 ,p_cdjv_tbl => l_adj_assets_tbl
2243 ,x_cdjv_tbl => lx_adj_assets_tbl );
2244
2245 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2246 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2247 ELSIF x_return_status = G_RET_STS_ERROR THEN
2248 RAISE OKL_API.G_EXCEPTION_ERROR;
2249 END IF;
2250
2251 END IF;
2252 --Bug # 5142940 ssdeshpa start
2253 l_adj_assets_tbl := lx_adj_assets_tbl;
2254 IF (l_adj_assets_tbl.COUNT > 0) THEN
2255 FOR i IN l_adj_assets_tbl.FIRST .. l_adj_assets_tbl.LAST LOOP
2256 IF l_adj_assets_tbl.EXISTS(i) THEN
2257 IF(l_adj_assets_tbl(i).adjustment_source_type='DOWN_PAYMENT' AND
2258 l_adj_assets_tbl(i).processing_type='BILL' AND
2259 l_adj_assets_tbl(i).stream_type_id IS NOT NULL) THEN
2260 --Create Rec Structure for Cash flows
2261 process_adj_cashflows(p_cdjv_rec => l_adj_assets_tbl(i)
2262 ,p_event_mode => 'create'
2263 ,x_msg_count => x_msg_count
2264 ,x_msg_data => x_msg_data
2265 ,x_return_status => x_return_status);
2266
2267 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2268 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2269 ELSIF x_return_status = G_RET_STS_ERROR THEN
2270 RAISE OKL_API.G_EXCEPTION_ERROR;
2271 END IF;
2272 END IF;
2273 END IF;
2274 END LOOP;
2275 END IF;
2276
2277 --Bug # 5142940 ssdeshpa end
2278
2279 /*SELECT qte.parent_object_id,qte.parent_object_code
2280 INTO l_p_id,l_p_code
2281 FROM okl_assets_b ast,okl_lease_quotes_b qte
2282 WHERE qte.id = ast.parent_object_id
2283 AND ast.id= l_asset_adj_tbl(1).parent_object_id;
2284
2285
2286 OKL_LEASE_QUOTE_PRICING_PVT.handle_parent_object_status(
2287 p_api_version => G_API_VERSION
2288 ,p_init_msg_list => G_FALSE
2289 ,x_return_status => x_return_status
2290 ,x_msg_count => x_msg_count
2291 ,x_msg_data => x_msg_data
2292 ,p_parent_object_code => l_p_code
2293 ,p_parent_object_id => l_p_id
2294 );
2295
2296 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2297 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2298 ELSIF x_return_status = G_RET_STS_ERROR THEN
2299 RAISE OKL_API.G_EXCEPTION_ERROR;
2300 END IF;*/
2301
2302 --Bug 7291307 :Adding start
2303 IF l_sync_desc='Y' and
2304 l_quote_id is not null THEN
2305 sync_tradein_description ( p_api_version => G_API_VERSION,
2306 p_init_msg_list => G_FALSE,
2307 x_msg_count => x_msg_count,
2308 x_msg_data => x_msg_data,
2309 x_return_status => x_return_status,
2310 p_quote_id => l_quote_id,
2311 p_description => l_cost_adj_desc
2312 );
2313 END IF;
2314
2315 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2316 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2317 ELSIF x_return_status = G_RET_STS_ERROR THEN
2318 RAISE OKL_API.G_EXCEPTION_ERROR;
2319 END IF;
2320 --Bug 7291307 :Adding end
2321
2322
2323 x_return_status := G_RET_STS_SUCCESS;
2324 EXCEPTION
2325
2326 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2327
2328 IF p_transaction_control = G_TRUE THEN
2329 ROLLBACK TO l_program_name;
2330 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2331 END IF;
2332
2333 x_return_status := G_RET_STS_ERROR;
2334
2335 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2336
2337 IF p_transaction_control = G_TRUE THEN
2338 ROLLBACK TO l_program_name;
2339 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2340 END IF;
2341
2342 x_return_status := G_RET_STS_UNEXP_ERROR;
2343
2344 WHEN OTHERS THEN
2345
2346 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2347 p_msg_name => G_DB_ERROR,
2348 p_token1 => G_PROG_NAME_TOKEN,
2349 p_token1_value => l_api_name,
2350 p_token2 => G_SQLCODE_TOKEN,
2351 p_token2_value => sqlcode,
2352 p_token3 => G_SQLERRM_TOKEN,
2353 p_token3_value => sqlerrm);
2354
2355 IF p_transaction_control = G_TRUE THEN
2356 ROLLBACK TO l_program_name;
2357 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2358 END IF;
2359
2360 x_return_status := G_RET_STS_UNEXP_ERROR;
2361 END;
2362
2363 ------------------------------
2364 -- PROCEDURE update_adjustment
2365 ------------------------------
2366 PROCEDURE update_adjustment (p_api_version IN NUMBER,
2367 p_init_msg_list IN VARCHAR2,
2368 p_transaction_control IN VARCHAR2,
2369 p_asset_adj_tbl IN asset_adjustment_tbl_type,
2370 x_return_status OUT NOCOPY VARCHAR2,
2371 x_msg_count OUT NOCOPY NUMBER,
2372 x_msg_data OUT NOCOPY VARCHAR2) IS
2373
2374 l_program_name CONSTANT VARCHAR2(30) := 'update_adjustment';
2375 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2376
2377 l_asset_adj_tbl asset_adjustment_tbl_type;
2378 l_adj_assets_tbl asset_adj_tbl_type;
2379 lx_adj_assets_tbl asset_adj_tbl_type;
2380 l_deleted_adjust_tbl asset_adj_tbl_type;
2381 l_p_id NUMBER;
2382 l_p_code VARCHAR2(30);
2383 ln_total_subsidy_amount NUMBER := 0;
2384 ln_index NUMBER;
2385 --Bug 7291307 : Adding start
2386 l_cost_adj_desc okl_cost_adjustments_tl.description%TYPE;
2387 l_sync_desc VARCHAR2(1):='N';
2388 l_quote_id okl_lease_quotes_b.id%TYPE;
2389 --Bug 7291307 : Adding end
2390
2391 BEGIN
2392 IF p_transaction_control = G_TRUE THEN
2393 SAVEPOINT l_program_name;
2394 END IF;
2395
2396 IF p_init_msg_list = G_TRUE THEN
2397 FND_MSG_PUB.initialize;
2398 END IF;
2399
2400 l_asset_adj_tbl := p_asset_adj_tbl;
2401
2402 ln_index := l_asset_adj_tbl.FIRST;
2403 /*
2404 -- Validate Adjustment assets amount
2405 IF (l_asset_adj_tbl(ln_index).adjustment_source_type <> 'SUBSIDY') THEN
2406 --Fixing Bug # 4735811 ssdeshpa Start
2407
2408 process_link_asset_amounts(p_adj_amount => l_asset_adj_tbl(ln_index).adjustment_amount,
2409 p_assoc_assets_tbl => l_asset_adj_tbl,
2410 x_return_status => x_return_status);
2411
2412 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2413 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2414 ELSIF x_return_status = G_RET_STS_ERROR THEN
2415 RAISE OKL_API.G_EXCEPTION_ERROR;
2416 END IF;
2417
2418 --Fixing Bug # 4735811 ssdeshpa End
2419 validate_adjust_assets(p_adj_amount => l_asset_adj_tbl(ln_index).adjustment_amount,
2420 p_assoc_assets_tbl => l_asset_adj_tbl,
2421 x_return_status => x_return_status);
2422 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2423 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2424 ELSIF x_return_status = G_RET_STS_ERROR THEN
2425 RAISE OKL_API.G_EXCEPTION_ERROR;
2426 END IF;
2427 END IF;
2428 */
2429 IF (l_asset_adj_tbl(ln_index).adjustment_source_type = 'SUBSIDY') THEN
2430 get_deleted_adjusted_assets (p_adj_type => l_asset_adj_tbl(ln_index).adjustment_source_type,
2431 p_quote_id => l_asset_adj_tbl(ln_index).quote_id,
2432 p_adjustment_tbl => l_asset_adj_tbl,
2433 x_deleted_adjust_tbl => l_deleted_adjust_tbl,
2434 x_return_status => x_return_status );
2435
2436 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2437 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2438 ELSIF x_return_status = G_RET_STS_ERROR THEN
2439 RAISE OKL_API.G_EXCEPTION_ERROR;
2440 END IF;
2441
2442 IF l_deleted_adjust_tbl.COUNT > 0 THEN
2443 okl_cdj_pvt.delete_row (p_api_version => G_API_VERSION
2444 ,p_init_msg_list => G_FALSE
2445 ,x_return_status => x_return_status
2446 ,x_msg_count => x_msg_count
2447 ,x_msg_data => x_msg_data
2448 ,p_cdjv_tbl => l_deleted_adjust_tbl );
2449
2450 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2451 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2452 ELSIF x_return_status = G_RET_STS_ERROR THEN
2453 RAISE OKL_API.G_EXCEPTION_ERROR;
2454 END IF;
2455 END IF;
2456 END IF;
2457 IF (l_asset_adj_tbl.COUNT > 0 AND l_asset_adj_tbl(ln_index).adjustment_source_type = 'SUBSIDY') THEN
2458 FOR j IN l_asset_adj_tbl.FIRST .. l_asset_adj_tbl.LAST LOOP
2459 IF l_asset_adj_tbl.EXISTS(j) THEN
2460 IF (l_asset_adj_tbl(j).value IS NOT NULL) THEN
2461 ln_total_subsidy_amount := ln_total_subsidy_amount + l_asset_adj_tbl(j).value;
2462 ELSE
2463 ln_total_subsidy_amount := ln_total_subsidy_amount + l_asset_adj_tbl(j).default_subsidy_amount;
2464 END IF;
2465 END IF;
2466 END LOOP;
2467 END IF;
2468
2469 FOR i IN l_asset_adj_tbl.FIRST .. l_asset_adj_tbl.LAST LOOP
2470 IF l_asset_adj_tbl.EXISTS(i) THEN
2471 IF l_asset_adj_tbl(i).record_mode = 'create' THEN
2472 --asawanka bug 5025239 fix starts
2473 IF (l_asset_adj_tbl(i).value IS NOT NULL AND l_asset_adj_tbl(i).value < 0) THEN
2474 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2475 p_msg_name => 'OKL_ADJ_AMOUNT_NEGATIVE');
2476 RAISE OKL_API.G_EXCEPTION_ERROR;
2477 END IF;
2478 --asawanka bug 5025239 fix ends
2479 l_adj_assets_tbl(i).parent_object_code := l_asset_adj_tbl(i).parent_object_code;
2480 l_adj_assets_tbl(i).parent_object_id := l_asset_adj_tbl(i).parent_object_id;
2481 l_adj_assets_tbl(i).adjustment_source_type := l_asset_adj_tbl(i).adjustment_source_type;
2482 l_adj_assets_tbl(i).adjustment_source_id := l_asset_adj_tbl(i).adjustment_source_id;
2483 l_adj_assets_tbl(i).basis := l_asset_adj_tbl(i).basis;
2484 l_adj_assets_tbl(i).value := l_asset_adj_tbl(i).value;
2485 l_adj_assets_tbl(i).default_subsidy_amount := l_asset_adj_tbl(i).default_subsidy_amount;
2486 l_adj_assets_tbl(i).processing_type := l_asset_adj_tbl(i).processing_type;
2487 l_adj_assets_tbl(i).supplier_id := l_asset_adj_tbl(i).supplier_id;
2488 --bug # 5142940 ssdeshpa start
2489 l_adj_assets_tbl(i).stream_type_id := l_asset_adj_tbl(i).stream_type_id;
2490 --bug # 5142940 ssdeshpa end
2491 l_adj_assets_tbl(i).short_description := l_asset_adj_tbl(i).short_description;
2492 l_adj_assets_tbl(i).description := l_asset_adj_tbl(i).description;
2493 l_adj_assets_tbl(i).comments := l_asset_adj_tbl(i).comments;
2494 l_adj_assets_tbl(i).percent_basis_value := l_asset_adj_tbl(i).percent_basis_value;
2495 IF (l_asset_adj_tbl(i).adjustment_source_type = 'SUBSIDY') THEN
2496 validate_subsidy_usage(p_asset_id => l_adj_assets_tbl(i).parent_object_id,
2497 p_total_subsidy_amount => ln_total_subsidy_amount,
2498 p_input_adj_rec => l_adj_assets_tbl(i),
2499 x_return_status => x_return_status);
2500 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2501 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2502 ELSIF x_return_status = G_RET_STS_ERROR THEN
2503 RAISE OKL_API.G_EXCEPTION_ERROR;
2504 END IF;
2505 END IF;
2506
2507 okl_cdj_pvt.insert_row (p_api_version => G_API_VERSION
2508 ,p_init_msg_list => G_FALSE
2509 ,x_return_status => x_return_status
2510 ,x_msg_count => x_msg_count
2511 ,x_msg_data => x_msg_data
2512 ,p_cdjv_rec => l_adj_assets_tbl(i)
2513 ,x_cdjv_rec => lx_adj_assets_tbl(i));
2514
2515 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2516 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2517 ELSIF x_return_status = G_RET_STS_ERROR THEN
2518 RAISE OKL_API.G_EXCEPTION_ERROR;
2519 END IF;
2520 --Bug # 5142940 ssdeshpa start
2521 IF(lx_adj_assets_tbl(i).adjustment_source_type='DOWN_PAYMENT' AND
2522 lx_adj_assets_tbl(i).processing_type='BILL' AND
2523 lx_adj_assets_tbl(i).stream_type_id IS NOT NULL) THEN
2524 --Create Rec Structure for Cash flows
2525 process_adj_cashflows(p_cdjv_rec => lx_adj_assets_tbl(i)
2526 ,p_event_mode => 'create'
2527 ,x_msg_count => x_msg_count
2528 ,x_msg_data => x_msg_data
2529 ,x_return_status => x_return_status);
2530
2531 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2532 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2533 ELSIF x_return_status = G_RET_STS_ERROR THEN
2534 RAISE OKL_API.G_EXCEPTION_ERROR;
2535 END IF;
2536 END IF;
2537 ELSIF l_asset_adj_tbl(i).record_mode = 'update' THEN
2538
2539 --asawanka bug 5025239 fix starts
2540 IF (l_asset_adj_tbl(i).value IS NULL) THEN
2541 IF l_asset_adj_tbl(i).adjustment_source_type = 'SUBSIDY' THEN
2542 IF l_asset_adj_tbl(i).default_subsidy_amount IS NULL THEN
2543 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2544 p_msg_name => 'OKL_LINKASSET_NULL_FOUND');
2545 RAISE OKL_API.G_EXCEPTION_ERROR;
2546 END IF;
2547 ELSE
2548 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2549 p_msg_name => 'OKL_LINKASSET_NULL_FOUND');
2550 RAISE OKL_API.G_EXCEPTION_ERROR;
2551 END IF;
2552 END IF;
2553 IF (l_asset_adj_tbl(i).value IS NOT NULL AND l_asset_adj_tbl(i).value < 0) THEN
2554 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2555 p_msg_name => 'OKL_ADJ_AMOUNT_NEGATIVE');
2556 RAISE OKL_API.G_EXCEPTION_ERROR;
2557 END IF;
2558
2559 --Bug 7291307 : Adding start
2560 IF (l_asset_adj_tbl(i).adjustment_source_type = 'TRADEIN')
2561 and (l_sync_desc<>'Y') THEN
2562 l_cost_adj_desc :=l_asset_adj_tbl(i).description;
2563 l_sync_desc:='Y';
2564 l_quote_id:=l_asset_adj_tbl(i).quote_id;
2565 END IF;
2566 --Bug 7291307 : Adding end
2567
2568 --asawanka bug 5025239 fix ends
2569 l_adj_assets_tbl(i).id := l_asset_adj_tbl(i).id;
2570 l_adj_assets_tbl(i).object_version_number := l_asset_adj_tbl(i).object_version_number;
2571 l_adj_assets_tbl(i).parent_object_code := l_asset_adj_tbl(i).parent_object_code;
2572 l_adj_assets_tbl(i).parent_object_id := l_asset_adj_tbl(i).parent_object_id;
2573 l_adj_assets_tbl(i).adjustment_source_type := l_asset_adj_tbl(i).adjustment_source_type;
2574 l_adj_assets_tbl(i).adjustment_source_id := l_asset_adj_tbl(i).adjustment_source_id;
2575 l_adj_assets_tbl(i).basis := l_asset_adj_tbl(i).basis;
2576 l_adj_assets_tbl(i).value := l_asset_adj_tbl(i).value;
2577 l_adj_assets_tbl(i).default_subsidy_amount := l_asset_adj_tbl(i).default_subsidy_amount;
2578 l_adj_assets_tbl(i).processing_type := l_asset_adj_tbl(i).processing_type;
2579 l_adj_assets_tbl(i).supplier_id := l_asset_adj_tbl(i).supplier_id;
2580 --bug # 5142940 ssdeshpa start
2581 l_adj_assets_tbl(i).stream_type_id := l_asset_adj_tbl(i).stream_type_id;
2582 --bug # 5142940 ssdeshpa end
2583 l_adj_assets_tbl(i).short_description := l_asset_adj_tbl(i).short_description;
2584 l_adj_assets_tbl(i).description := l_asset_adj_tbl(i).description;
2585 l_adj_assets_tbl(i).comments := l_asset_adj_tbl(i).comments;
2586 l_adj_assets_tbl(i).percent_basis_value := l_asset_adj_tbl(i).percent_basis_value;
2587
2588 IF (l_asset_adj_tbl(i).adjustment_source_type = 'SUBSIDY') THEN
2589 validate_subsidy_usage(p_asset_id => l_adj_assets_tbl(i).parent_object_id,
2590 p_total_subsidy_amount => ln_total_subsidy_amount,
2591 p_input_adj_rec => l_adj_assets_tbl(i),
2592 x_return_status => x_return_status);
2593 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2594 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2595 ELSIF x_return_status = G_RET_STS_ERROR THEN
2596 RAISE OKL_API.G_EXCEPTION_ERROR;
2597 END IF;
2598 END IF;
2599
2600 okl_cdj_pvt.update_row (p_api_version => G_API_VERSION
2601 ,p_init_msg_list => G_FALSE
2602 ,x_return_status => x_return_status
2603 ,x_msg_count => x_msg_count
2604 ,x_msg_data => x_msg_data
2605 ,p_cdjv_rec => l_adj_assets_tbl(i)
2606 ,x_cdjv_rec => lx_adj_assets_tbl(i));
2607 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2608 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2609 ELSIF x_return_status = G_RET_STS_ERROR THEN
2610 RAISE OKL_API.G_EXCEPTION_ERROR;
2611 END IF;
2612
2613 process_adj_cashflows(p_cdjv_rec => lx_adj_assets_tbl(i)
2614 ,p_event_mode => 'update'
2615 ,x_msg_count => x_msg_count
2616 ,x_msg_data => x_msg_data
2617 ,x_return_status => x_return_status);
2618
2619 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2620 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2621 ELSIF x_return_status = G_RET_STS_ERROR THEN
2622 RAISE OKL_API.G_EXCEPTION_ERROR;
2623 END IF;
2624
2625
2626 END IF;--l_asset_adj_tbl(i).record_mode = 'create'
2627 END IF;
2628 END LOOP;
2629
2630 --Bug 7291307 - Adding start
2631 IF l_sync_desc='Y' and
2632 l_quote_id is not null THEN
2633 sync_tradein_description ( p_api_version => G_API_VERSION,
2634 p_init_msg_list => G_FALSE,
2635 x_msg_count => x_msg_count,
2636 x_msg_data => x_msg_data,
2637 x_return_status => x_return_status,
2638 p_quote_id => l_quote_id,
2639 p_description => l_cost_adj_desc
2640 );
2641 END IF;
2642 --Bug 7291307 - Adding end
2643
2644 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2645 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2646 ELSIF x_return_status = G_RET_STS_ERROR THEN
2647 RAISE OKL_API.G_EXCEPTION_ERROR;
2648 END IF;
2649
2650 /*SELECT qte.parent_object_id,qte.parent_object_code
2651 INTO l_p_id,l_p_code
2652 FROM okl_assets_b ast,okl_lease_quotes_b qte
2653 WHERE qte.id = ast.parent_object_id
2654 AND ast.id= l_asset_adj_tbl(1).parent_object_id;
2655
2656
2657 OKL_LEASE_QUOTE_PRICING_PVT.handle_parent_object_status(
2658 p_api_version => G_API_VERSION
2659 ,p_init_msg_list => G_FALSE
2660 ,x_return_status => x_return_status
2661 ,x_msg_count => x_msg_count
2662 ,x_msg_data => x_msg_data
2663 ,p_parent_object_code => l_p_code
2664 ,p_parent_object_id => l_p_id
2665 );
2666
2667 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2668 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2669 ELSIF x_return_status = G_RET_STS_ERROR THEN
2670 RAISE OKL_API.G_EXCEPTION_ERROR;
2671 END IF;*/
2672
2673
2674 x_return_status := G_RET_STS_SUCCESS;
2675 EXCEPTION
2676
2677 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2678
2679 IF p_transaction_control = G_TRUE THEN
2680 ROLLBACK TO l_program_name;
2681 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2682 END IF;
2683
2684 x_return_status := G_RET_STS_ERROR;
2685
2686 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2687
2688 IF p_transaction_control = G_TRUE THEN
2689 ROLLBACK TO l_program_name;
2690 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2691 END IF;
2692
2693 x_return_status := G_RET_STS_UNEXP_ERROR;
2694
2695 WHEN OTHERS THEN
2696
2697 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2698 p_msg_name => G_DB_ERROR,
2699 p_token1 => G_PROG_NAME_TOKEN,
2700 p_token1_value => l_api_name,
2701 p_token2 => G_SQLCODE_TOKEN,
2702 p_token2_value => sqlcode,
2703 p_token3 => G_SQLERRM_TOKEN,
2704 p_token3_value => sqlerrm);
2705
2706 IF p_transaction_control = G_TRUE THEN
2707 ROLLBACK TO l_program_name;
2708 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2709 END IF;
2710
2711 x_return_status := G_RET_STS_UNEXP_ERROR;
2712 END;
2713
2714 ------------------------------
2715 -- PROCEDURE delete_adjustment
2716 ------------------------------
2717 PROCEDURE delete_adjustment (p_api_version IN NUMBER,
2718 p_init_msg_list IN VARCHAR2,
2719 p_transaction_control IN VARCHAR2,
2720 p_adjustment_type IN VARCHAR2,
2721 p_adjustment_id IN NUMBER,
2722 p_quote_id IN NUMBER,
2723 x_return_status OUT NOCOPY VARCHAR2,
2724 x_msg_count OUT NOCOPY NUMBER,
2725 x_msg_data OUT NOCOPY VARCHAR2) IS
2726
2727 l_program_name CONSTANT VARCHAR2(30) := 'delete_adjustment';
2728 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2729
2730 l_adj_assets_tbl asset_adj_tbl_type;
2731 i BINARY_INTEGER := 0;
2732 l_p_id NUMBER;
2733 l_p_code VARCHAR2(30);
2734
2735 CURSOR c_db_adj_assets IS
2736 SELECT costadj.id, costadj.adjustment_source_id
2737 FROM okl_cost_adjustments_b costadj,
2738 okl_assets_b asset
2739 WHERE costadj.adjustment_source_type = p_adjustment_type
2740 AND costadj.parent_object_id = asset.id
2741 AND costadj.parent_object_code = 'ASSET'
2742 AND asset.parent_object_id = p_quote_id
2743 AND asset.parent_object_code = 'LEASEQUOTE';
2744
2745 CURSOR c_db_adj_asset(p_asset_id IN NUMBER) IS
2746 SELECT costadj.id,costadj.adjustment_source_type,costadj.processing_type,costadj.stream_type_id
2747 FROM okl_cost_adjustments_b costadj
2748 WHERE costadj.adjustment_source_type = p_adjustment_type
2749 AND costadj.parent_object_id = p_asset_id
2750 AND costadj.parent_object_code = 'ASSET';
2751 BEGIN
2752 IF p_transaction_control = G_TRUE THEN
2753 SAVEPOINT l_program_name;
2754 END IF;
2755
2756 IF p_init_msg_list = G_TRUE THEN
2757 FND_MSG_PUB.initialize;
2758 END IF;
2759 --Bug # 5142940 ssdeshpa start
2760 --Fix Bug # 4894844 Start
2761 IF p_adjustment_type = 'SUBSIDY' THEN
2762 IF p_adjustment_id IS NULL THEN
2763 FOR l_db_adj_assets IN c_db_adj_assets LOOP
2764 l_adj_assets_tbl(i).id := l_db_adj_assets.id;
2765 i := i + 1;
2766 END LOOP;
2767 ELSE
2768 FOR l_db_adj_assets IN c_db_adj_assets LOOP
2769 IF l_db_adj_assets.adjustment_source_id = p_adjustment_id THEN
2770 l_adj_assets_tbl(i).id := l_db_adj_assets.id;
2771 i := i + 1;
2772 END IF;
2773 END LOOP;
2774 END IF;
2775 ELSIF(p_adjustment_type IN ('DOWN_PAYMENT', 'TRADEIN')) THEN
2776 OPEN c_db_adj_asset(p_asset_id => p_adjustment_id);
2777 FETCH c_db_adj_asset INTO l_adj_assets_tbl(i).id , l_adj_assets_tbl(i).adjustment_source_type, l_adj_assets_tbl(i).processing_type,l_adj_assets_tbl(i).stream_type_id;
2778 CLOSE c_db_adj_asset;
2779 --Bug # 5142940 ssdeshpa start
2780 l_adj_assets_tbl(i).parent_object_id := p_adjustment_id;
2781 --Bug # 5142940 ssdeshpa start
2782 END IF;
2783 --Fix Bug # 4894844 end
2784 --Bug # 5142940 ssdeshpa end
2785 IF l_adj_assets_tbl.COUNT > 0 THEN
2786 okl_cdj_pvt.delete_row (p_api_version => G_API_VERSION
2787 ,p_init_msg_list => G_FALSE
2788 ,x_return_status => x_return_status
2789 ,x_msg_count => x_msg_count
2790 ,x_msg_data => x_msg_data
2791 ,p_cdjv_tbl => l_adj_assets_tbl);
2792 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2793 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2794 ELSIF x_return_status = G_RET_STS_ERROR THEN
2795 RAISE OKL_API.G_EXCEPTION_ERROR;
2796 END IF;
2797 --Bug # 5142940 ssdeshpa start
2798 FOR i IN l_adj_assets_tbl.FIRST .. l_adj_assets_tbl.LAST LOOP
2799 IF l_adj_assets_tbl.EXISTS(i) THEN
2800 IF(l_adj_assets_tbl(i).adjustment_source_type='DOWN_PAYMENT' AND
2801 l_adj_assets_tbl(i).processing_type='BILL' AND
2802 l_adj_assets_tbl(i).stream_type_id IS NOT NULL) THEN
2803 --Delete Rec Structure for Cash flows
2804 process_adj_cashflows(p_cdjv_rec => l_adj_assets_tbl(i)
2805 ,p_event_mode => 'delete'
2806 ,x_msg_count => x_msg_count
2807 ,x_msg_data => x_msg_data
2808 ,x_return_status => x_return_status);
2809
2810 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2811 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2812 ELSIF x_return_status = G_RET_STS_ERROR THEN
2813 RAISE OKL_API.G_EXCEPTION_ERROR;
2814 END IF;
2815 END IF;
2816 END IF;
2817 END LOOP;
2818 --Bug # 5142940 ssdeshpa start
2819 END IF;
2820
2821 /*SELECT parent_object_id,parent_object_code INTO l_p_id,l_p_code
2822 FROM okl_lease_quotes_b where ID = p_quote_id;
2823
2824 OKL_LEASE_QUOTE_PRICING_PVT.handle_parent_object_status(
2825 p_api_version => G_API_VERSION
2826 ,p_init_msg_list => G_FALSE
2827 ,x_return_status => x_return_status
2828 ,x_msg_count => x_msg_count
2829 ,x_msg_data => x_msg_data
2830 ,p_parent_object_code => l_p_code
2831 ,p_parent_object_id => l_p_id
2832 );
2833
2834 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2835 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2836 ELSIF x_return_status = G_RET_STS_ERROR THEN
2837 RAISE OKL_API.G_EXCEPTION_ERROR;
2838 END IF;*/
2839
2840
2841 x_return_status := G_RET_STS_SUCCESS;
2842 EXCEPTION
2843
2844 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2845
2846 IF p_transaction_control = G_TRUE THEN
2847 ROLLBACK TO l_program_name;
2848 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2849 END IF;
2850
2851 x_return_status := G_RET_STS_ERROR;
2852
2853 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2854
2855 IF p_transaction_control = G_TRUE THEN
2856 ROLLBACK TO l_program_name;
2857 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2858 END IF;
2859
2860 x_return_status := G_RET_STS_UNEXP_ERROR;
2861
2862 WHEN OTHERS THEN
2863
2864 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2865 p_msg_name => G_DB_ERROR,
2866 p_token1 => G_PROG_NAME_TOKEN,
2867 p_token1_value => l_api_name,
2868 p_token2 => G_SQLCODE_TOKEN,
2869 p_token2_value => sqlcode,
2870 p_token3 => G_SQLERRM_TOKEN,
2871 p_token3_value => sqlerrm);
2872
2873 IF p_transaction_control = G_TRUE THEN
2874 ROLLBACK TO l_program_name;
2875 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2876 END IF;
2877
2878 x_return_status := G_RET_STS_UNEXP_ERROR;
2879 END;
2880
2881 --------------------------------------
2882 -- PROCEDURE duplicate_asset_cashflows
2883 --------------------------------------
2884 PROCEDURE duplicate_asset_cashflows (
2885 p_api_version IN NUMBER
2886 ,p_init_msg_list IN VARCHAR2
2887 ,p_transaction_control IN VARCHAR2
2888 ,p_source_object IN VARCHAR2
2889 ,p_source_id IN NUMBER
2890 ,p_target_id IN NUMBER
2891 ,p_quote_id IN NUMBER
2892 ,x_return_status OUT NOCOPY VARCHAR2
2893 ,x_msg_count OUT NOCOPY NUMBER
2894 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
2895
2896 l_program_name CONSTANT VARCHAR2(30) := 'duplicate_asset_cashflows';
2897 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2898
2899 l_cashflow_header_rec cashflow_hdr_rec_type;
2900 l_cashflow_level_tbl cashflow_level_tbl_type;
2901
2902 l_return_status VARCHAR2(1);
2903 l_cashflow_object_id NUMBER;
2904 lv_cft_code VARCHAR2(30);
2905 lv_frq_code VARCHAR2(30);
2906 lv_stream_type_purpose VARCHAR2(150);
2907 i BINARY_INTEGER := 0;
2908 j BINARY_INTEGER := 0;
2909 lv_parent_object_code VARCHAR2(30);
2910 --Fix Bug # 5021724 ssdeshpa start
2911 --Cursor is not fetching the correct source cashflows;added where conditions.
2912 CURSOR c_get_cashflow_object_info(p_src_id OKL_CASH_FLOW_OBJECTS.SOURCE_ID%TYPE)
2913 IS
2914 SELECT ID
2915 FROM OKL_CASH_FLOW_OBJECTS
2916 WHERE SOURCE_ID = p_src_id
2917 AND OTY_CODE = 'QUOTED_ASSET'
2918 AND SOURCE_TABLE='OKL_ASSETS_B';
2919 --Fix Bug # 5021724 ssdeshpa end
2920
2921 CURSOR c_get_cashflow_info(p_cfo_id OKL_CASH_FLOWS.CFO_ID%TYPE)
2922 IS
2923 SELECT CFLOW.ID, CFLOW.STY_ID, CFLOW.DUE_ARREARS_YN, CFLOW.CFT_CODE, STRMTYP.STREAM_TYPE_PURPOSE
2924 FROM OKL_CASH_FLOWS CFLOW,
2925 OKL_STRMTYP_SOURCE_V STRMTYP
2926 WHERE CFO_ID = p_cfo_id
2927 AND CFLOW.STY_ID = STRMTYP.ID1;
2928
2929 CURSOR c_get_cashflow_levels(p_caf_id OKL_CASH_FLOWS.ID%TYPE)
2930 IS
2931 SELECT AMOUNT, NUMBER_OF_PERIODS, FQY_CODE, STUB_DAYS, STUB_AMOUNT
2932 FROM OKL_CASH_FLOW_LEVELS
2933 WHERE CAF_ID = p_caf_id;
2934
2935 BEGIN
2936 IF p_transaction_control = G_TRUE THEN
2937 SAVEPOINT l_program_name;
2938 END IF;
2939
2940 IF p_init_msg_list = G_TRUE THEN
2941 FND_MSG_PUB.initialize;
2942 END IF;
2943
2944 FOR l_get_cashflow_object_info IN c_get_cashflow_object_info(p_src_id => p_source_id) LOOP
2945 l_cashflow_object_id := l_get_cashflow_object_info.id;
2946
2947 OPEN c_get_cashflow_info(p_cfo_id => l_cashflow_object_id);
2948 FETCH c_get_cashflow_info INTO l_cashflow_header_rec.cashflow_header_id,
2949 l_cashflow_header_rec.stream_type_id,
2950 l_cashflow_header_rec.arrears_flag,
2951 lv_cft_code, lv_stream_type_purpose;
2952 CLOSE c_get_cashflow_info;
2953
2954 IF (lv_stream_type_purpose = 'RENT') THEN
2955 l_cashflow_header_rec.parent_object_code := p_source_object;
2956 l_cashflow_header_rec.parent_object_id := p_target_id;
2957 l_cashflow_header_rec.quote_id := p_quote_id;
2958
2959 IF (lv_cft_code = 'PAYMENT_SCHEDULE') THEN
2960 l_cashflow_header_rec.type_code := 'INFLOW';
2961 ELSIF (lv_cft_code = 'OUTFLOW_SCHEDULE') THEN
2962 l_cashflow_header_rec.type_code := 'OUTFLOW';
2963 END IF;
2964
2965 SELECT parent_object_code
2966 INTO lv_parent_object_code
2967 FROM okl_lease_quotes_b
2968 where id = p_quote_id;
2969
2970 IF (lv_parent_object_code = 'LEASEOPP') THEN
2971 l_cashflow_header_rec.quote_type_code := 'LQ';
2972 ELSIF (lv_parent_object_code = 'LEASEAPP') THEN
2973 l_cashflow_header_rec.quote_type_code := 'LA';
2974 END IF;
2975
2976 FOR l_get_cashflow_levels IN c_get_cashflow_levels(p_caf_id => l_cashflow_header_rec.cashflow_header_id) LOOP
2977 l_cashflow_level_tbl(j).record_mode := 'create';
2978 l_cashflow_level_tbl(j).periods := l_get_cashflow_levels.number_of_periods;
2979 l_cashflow_level_tbl(j).periodic_amount := l_get_cashflow_levels.amount;
2980 l_cashflow_level_tbl(j).stub_days := l_get_cashflow_levels.stub_days;
2981 l_cashflow_level_tbl(j).stub_amount := l_get_cashflow_levels.stub_amount;
2982 lv_frq_code := l_get_cashflow_levels.fqy_code;
2983 j := j + 1;
2984 END LOOP;
2985
2986 l_cashflow_header_rec.frequency_code := lv_frq_code;
2987
2988 okl_lease_quote_cashflow_pvt.create_cashflow ( p_api_version => p_api_version
2989 ,p_init_msg_list => p_init_msg_list
2990 ,p_transaction_control => p_transaction_control
2991 ,p_cashflow_header_rec => l_cashflow_header_rec
2992 ,p_cashflow_level_tbl => l_cashflow_level_tbl
2993 ,x_return_status => l_return_status
2994 ,x_msg_count => x_msg_count
2995 ,x_msg_data => x_msg_data);
2996 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2997 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2998 ELSIF l_return_status = G_RET_STS_ERROR THEN
2999 RAISE OKL_API.G_EXCEPTION_ERROR;
3000 END IF;
3001 END IF;
3002
3003 i := i + 1;
3004 END LOOP;
3005
3006 x_return_status := G_RET_STS_SUCCESS;
3007 EXCEPTION
3008
3009 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3010
3011 IF p_transaction_control = G_TRUE THEN
3012 ROLLBACK TO l_program_name;
3013 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3014 END IF;
3015
3016 x_return_status := G_RET_STS_ERROR;
3017
3018 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3019
3020 IF p_transaction_control = G_TRUE THEN
3021 ROLLBACK TO l_program_name;
3022 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3023 END IF;
3024
3025 x_return_status := G_RET_STS_UNEXP_ERROR;
3026
3027 WHEN OTHERS THEN
3028
3029 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
3030 p_msg_name => G_DB_ERROR,
3031 p_token1 => G_PROG_NAME_TOKEN,
3032 p_token1_value => l_api_name,
3033 p_token2 => G_SQLCODE_TOKEN,
3034 p_token2_value => sqlcode,
3035 p_token3 => G_SQLERRM_TOKEN,
3036 p_token3_value => sqlerrm);
3037
3038 IF p_transaction_control = G_TRUE THEN
3039 ROLLBACK TO l_program_name;
3040 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3041 END IF;
3042
3043 x_return_status := G_RET_STS_UNEXP_ERROR;
3044 END duplicate_asset_cashflows;
3045
3046 --------------------------
3047 -- PROCEDURE get_asset_rec
3048 --------------------------
3049 PROCEDURE get_asset_rec (
3050 p_asset_id IN NUMBER
3051 ,x_asset_rec OUT NOCOPY asset_rec_type
3052 ,x_return_status OUT NOCOPY VARCHAR2) IS
3053
3054 l_program_name CONSTANT VARCHAR2(30) := 'get_asset_rec';
3055 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3056
3057 BEGIN
3058
3059 SELECT
3060 attribute_category
3061 ,attribute1
3062 ,attribute2
3063 ,attribute3
3064 ,attribute4
3065 ,attribute5
3066 ,attribute6
3067 ,attribute7
3068 ,attribute8
3069 ,attribute9
3070 ,attribute10
3071 ,attribute11
3072 ,attribute12
3073 ,attribute13
3074 ,attribute14
3075 ,attribute15
3076 ,object_version_number
3077 ,asset_number
3078 ,parent_object_id
3079 ,parent_object_code
3080 ,install_site_id
3081 ,rate_card_id
3082 ,rate_template_id
3083 ,oec
3084 ,end_of_term_value_default
3085 ,end_of_term_value
3086 ,oec_percentage
3087 ,structured_pricing
3088 ,target_arrears
3089 ,lease_rate_factor
3090 ,target_amount
3091 ,target_frequency
3092 ,short_description
3093 ,description
3094 ,comments
3095 INTO
3096 x_asset_rec.attribute_category
3097 ,x_asset_rec.attribute1
3098 ,x_asset_rec.attribute2
3099 ,x_asset_rec.attribute3
3100 ,x_asset_rec.attribute4
3101 ,x_asset_rec.attribute5
3102 ,x_asset_rec.attribute6
3103 ,x_asset_rec.attribute7
3104 ,x_asset_rec.attribute8
3105 ,x_asset_rec.attribute9
3106 ,x_asset_rec.attribute10
3107 ,x_asset_rec.attribute11
3108 ,x_asset_rec.attribute12
3109 ,x_asset_rec.attribute13
3110 ,x_asset_rec.attribute14
3111 ,x_asset_rec.attribute15
3112 ,x_asset_rec.object_version_number
3113 ,x_asset_rec.asset_number
3114 ,x_asset_rec.parent_object_id
3115 ,x_asset_rec.parent_object_code
3116 ,x_asset_rec.install_site_id
3117 ,x_asset_rec.rate_card_id
3118 ,x_asset_rec.rate_template_id
3119 ,x_asset_rec.oec
3120 ,x_asset_rec.end_of_term_value_default
3121 ,x_asset_rec.end_of_term_value
3122 ,x_asset_rec.oec_percentage
3123 ,x_asset_rec.structured_pricing
3124 ,x_asset_rec.target_arrears
3125 ,x_asset_rec.lease_rate_factor
3126 ,x_asset_rec.target_amount
3127 ,x_asset_rec.target_frequency
3128 ,x_asset_rec.short_description
3129 ,x_asset_rec.description
3130 ,x_asset_rec.comments
3131 FROM okl_assets_v
3132 WHERE id = p_asset_id;
3133
3134 x_return_status := G_RET_STS_SUCCESS;
3135
3136 EXCEPTION
3137
3138 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3139 x_return_status := G_RET_STS_ERROR;
3140
3141 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3142 x_return_status := G_RET_STS_UNEXP_ERROR;
3143
3144 WHEN OTHERS THEN
3145 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
3146 p_msg_name => G_DB_ERROR,
3147 p_token1 => G_PROG_NAME_TOKEN,
3148 p_token1_value => l_program_name,
3149 p_token2 => G_SQLCODE_TOKEN,
3150 p_token2_value => sqlcode,
3151 p_token3 => G_SQLERRM_TOKEN,
3152 p_token3_value => sqlerrm);
3153
3154 x_return_status := G_RET_STS_UNEXP_ERROR;
3155
3156 END get_asset_rec;
3157
3158 -----------------------------------
3159 -- PROCEDURE populate_asset_attribs
3160 -----------------------------------
3161 PROCEDURE populate_asset_attribs (
3162 p_source_asset_id IN NUMBER
3163 ,x_asset_rec IN OUT NOCOPY asset_rec_type
3164 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
3165
3166 l_program_name CONSTANT VARCHAR2(30) := 'populate_asset_attribs';
3167 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3168
3169 l_asset_rec asset_rec_type;
3170
3171 BEGIN
3172
3173 SELECT
3174 rate_card_id
3175 ,rate_template_id
3176 ,structured_pricing
3177 ,target_arrears
3178 ,lease_rate_factor
3179 ,target_amount
3180 ,target_frequency
3181 INTO
3182 x_asset_rec.rate_card_id
3183 ,x_asset_rec.rate_template_id
3184 ,x_asset_rec.structured_pricing
3185 ,x_asset_rec.target_arrears
3186 ,x_asset_rec.lease_rate_factor
3187 ,x_asset_rec.target_amount
3188 ,x_asset_rec.target_frequency
3189 FROM okl_assets_v
3190 WHERE id = p_source_asset_id;
3191
3192 x_return_status := G_RET_STS_SUCCESS;
3193
3194 EXCEPTION
3195
3196 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3197 x_return_status := G_RET_STS_ERROR;
3198
3199 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3200 x_return_status := G_RET_STS_UNEXP_ERROR;
3201
3202 WHEN OTHERS THEN
3203 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
3204 p_msg_name => G_DB_ERROR,
3205 p_token1 => G_PROG_NAME_TOKEN,
3206 p_token1_value => l_program_name,
3207 p_token2 => G_SQLCODE_TOKEN,
3208 p_token2_value => sqlcode,
3209 p_token3 => G_SQLERRM_TOKEN,
3210 p_token3_value => sqlerrm);
3211
3212 x_return_status := G_RET_STS_UNEXP_ERROR;
3213
3214 END populate_asset_attribs;
3215
3216 -------------------------------
3217 -- PROCEDURE get_asset_comp_tbl
3218 -------------------------------
3219 PROCEDURE get_asset_comp_tbl (
3220 p_asset_id IN NUMBER
3221 ,x_asset_comp_tbl OUT NOCOPY component_tbl_type
3222 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
3223
3224 l_program_name CONSTANT VARCHAR2(30) := 'get_asset_comp_tbl';
3225 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3226 i BINARY_INTEGER := 0;
3227
3228 CURSOR c_db_asset_comp IS
3229 SELECT
3230 id
3231 ,attribute_category
3232 ,attribute1
3233 ,attribute2
3234 ,attribute3
3235 ,attribute4
3236 ,attribute5
3237 ,attribute6
3238 ,attribute7
3239 ,attribute8
3240 ,attribute9
3241 ,attribute10
3242 ,attribute11
3243 ,attribute12
3244 ,attribute13
3245 ,attribute14
3246 ,attribute15
3247 ,asset_id
3248 ,object_version_number
3249 ,inv_item_id
3250 ,supplier_id
3251 ,primary_component
3252 ,unit_cost
3253 ,number_of_units
3254 ,manufacturer_name
3255 ,year_manufactured
3256 ,model_number
3257 ,short_description
3258 ,description
3259 ,comments
3260 FROM okl_asset_components_v
3261 WHERE asset_id = p_asset_id;
3262 BEGIN
3263 FOR l_db_asset_comp IN c_db_asset_comp LOOP
3264 x_asset_comp_tbl(i).id := l_db_asset_comp.id;
3265 x_asset_comp_tbl(i).attribute_category := l_db_asset_comp.attribute_category;
3266 x_asset_comp_tbl(i).attribute1 := l_db_asset_comp.attribute1;
3267 x_asset_comp_tbl(i).attribute2 := l_db_asset_comp.attribute2;
3268 x_asset_comp_tbl(i).attribute3 := l_db_asset_comp.attribute3;
3269 x_asset_comp_tbl(i).attribute4 := l_db_asset_comp.attribute4;
3270 x_asset_comp_tbl(i).attribute5 := l_db_asset_comp.attribute5;
3271 x_asset_comp_tbl(i).attribute6 := l_db_asset_comp.attribute6;
3272 x_asset_comp_tbl(i).attribute7 := l_db_asset_comp.attribute7;
3273 x_asset_comp_tbl(i).attribute8 := l_db_asset_comp.attribute8;
3274 x_asset_comp_tbl(i).attribute9 := l_db_asset_comp.attribute9;
3275 x_asset_comp_tbl(i).attribute10 := l_db_asset_comp.attribute10;
3276 x_asset_comp_tbl(i).attribute11 := l_db_asset_comp.attribute11;
3277 x_asset_comp_tbl(i).attribute12 := l_db_asset_comp.attribute12;
3278 x_asset_comp_tbl(i).attribute13 := l_db_asset_comp.attribute13;
3279 x_asset_comp_tbl(i).attribute14 := l_db_asset_comp.attribute14;
3280 x_asset_comp_tbl(i).attribute15 := l_db_asset_comp.attribute15;
3281 x_asset_comp_tbl(i).asset_id := l_db_asset_comp.asset_id;
3282 x_asset_comp_tbl(i).object_version_number := l_db_asset_comp.object_version_number;
3283 x_asset_comp_tbl(i).inv_item_id := l_db_asset_comp.inv_item_id;
3284 x_asset_comp_tbl(i).supplier_id := l_db_asset_comp.supplier_id;
3285 x_asset_comp_tbl(i).primary_component := l_db_asset_comp.primary_component;
3286 x_asset_comp_tbl(i).unit_cost := l_db_asset_comp.unit_cost;
3287 x_asset_comp_tbl(i).number_of_units := l_db_asset_comp.number_of_units;
3288 x_asset_comp_tbl(i).manufacturer_name := l_db_asset_comp.manufacturer_name;
3289 x_asset_comp_tbl(i).year_manufactured := l_db_asset_comp.year_manufactured;
3290 x_asset_comp_tbl(i).model_number := l_db_asset_comp.model_number;
3291 x_asset_comp_tbl(i).short_description := l_db_asset_comp.short_description;
3292 x_asset_comp_tbl(i).description := l_db_asset_comp.description;
3293 x_asset_comp_tbl(i).comments := l_db_asset_comp.comments;
3294 i := i + 1;
3295 END LOOP;
3296
3297 x_return_status := G_RET_STS_SUCCESS;
3298 EXCEPTION
3299
3300 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3301 x_return_status := G_RET_STS_ERROR;
3302
3303 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3304 x_return_status := G_RET_STS_UNEXP_ERROR;
3305
3306 WHEN OTHERS THEN
3307 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
3308 p_msg_name => G_DB_ERROR,
3309 p_token1 => G_PROG_NAME_TOKEN,
3310 p_token1_value => l_program_name,
3311 p_token2 => G_SQLCODE_TOKEN,
3312 p_token2_value => sqlcode,
3313 p_token3 => G_SQLERRM_TOKEN,
3314 p_token3_value => sqlerrm);
3315
3316 x_return_status := G_RET_STS_UNEXP_ERROR;
3317
3318 END get_asset_comp_tbl;
3319
3320 ----------------------------
3321 -- PROCEDURE duplicate_asset
3322 ----------------------------
3323 PROCEDURE duplicate_asset (
3324 p_api_version IN NUMBER
3325 ,p_init_msg_list IN VARCHAR2
3326 ,p_transaction_control IN VARCHAR2
3327 ,p_source_asset_id IN NUMBER
3328 ,p_target_quote_id IN NUMBER
3329 ,x_target_asset_id OUT NOCOPY NUMBER
3330 ,x_return_status OUT NOCOPY VARCHAR2
3331 ,x_msg_count OUT NOCOPY NUMBER
3332 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
3333
3334 l_program_name CONSTANT VARCHAR2(30) := 'duplicate_asset';
3335 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3336
3337 l_asset_rec asset_rec_type;
3338 lx_asset_rec asset_rec_type;
3339
3340 l_component_tbl component_tbl_type;
3341 lx_component_tbl component_tbl_type;
3342
3343 lv_pricing_type_equal VARCHAR2(1) := 'Y';
3344 lb_copy_cashflow BOOLEAN := TRUE;
3345 lb_default_eot BOOLEAN := TRUE;
3346 lb_dup_asset BOOLEAN := TRUE;
3347 ld_src_start_date DATE;
3348 ld_tgt_start_date DATE;
3349 ln_src_pdt_id NUMBER;
3350 ln_tgt_pdt_id NUMBER;
3351 lv_src_pricing_type VARCHAR2(30);
3352 lv_tgt_pricing_type VARCHAR2(30);
3353 l_parent_object_code okl_lease_quotes_b.parent_object_code%TYPE;
3354 ln_src_eot_id NUMBER;
3355 ln_tgt_eot_id NUMBER;
3356
3357 BEGIN
3358
3359 IF p_transaction_control = G_TRUE THEN
3360 SAVEPOINT l_program_name;
3361 END IF;
3362
3363 IF p_init_msg_list = G_TRUE THEN
3364 FND_MSG_PUB.initialize;
3365 END IF;
3366
3367 -- Validation to check if the product and expected start date for source
3368 -- and target contracts are equal, if not cash flows are not copied.
3369 SELECT quote.expected_start_date,
3370 quote.product_id,
3371 quote.pricing_method,
3372 quote.end_of_term_option_id
3373 INTO ld_src_start_date, ln_src_pdt_id, lv_src_pricing_type, ln_src_eot_id
3374 FROM
3375 okl_assets_b asset,
3376 okl_lease_quotes_b quote
3377 WHERE
3378 asset.id = p_source_asset_id
3379 AND asset.parent_object_id = quote.id
3380 AND asset.parent_object_code = 'LEASEQUOTE';
3381
3382 SELECT expected_start_date,
3383 product_id,
3384 pricing_method,
3385 parent_object_code,
3386 end_of_term_option_id
3387 INTO ld_tgt_start_date, ln_tgt_pdt_id, lv_tgt_pricing_type,l_parent_object_code, ln_tgt_eot_id
3388 FROM
3389 okl_lease_quotes_b
3390 WHERE
3391 id = p_target_quote_id;
3392
3393 IF ((ld_src_start_date <> ld_tgt_start_date) OR (ln_src_pdt_id <> ln_tgt_pdt_id)) THEN
3394 lb_copy_cashflow := FALSE;
3395 lb_default_eot := FALSE;
3396 END IF;
3397
3398 IF (ln_src_eot_id <> ln_tgt_eot_id) THEN
3399 lb_dup_asset := FALSE;
3400 END IF;
3401
3402 IF (lb_dup_asset) THEN -- Duplicate Asset
3403
3404 -- Fetch Asset Header
3405 get_asset_rec (
3406 p_asset_id => p_source_asset_id
3407 ,x_asset_rec => l_asset_rec
3408 ,x_return_status => x_return_status);
3409
3410 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
3411 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3412 ELSIF x_return_status = G_RET_STS_ERROR THEN
3413 RAISE OKL_API.G_EXCEPTION_ERROR;
3414 END IF;
3415
3416 -- Check for duplicate from/to 'Solve for Financed Amount' quote
3417 IF (lv_src_pricing_type <> 'SF' AND lv_tgt_pricing_type = 'SF') THEN
3418 l_asset_rec.oec := null;
3419 ELSIF (lv_src_pricing_type = 'SF' AND lv_tgt_pricing_type <> 'SF') THEN
3420 l_asset_rec.oec_percentage := null;
3421 END IF;
3422 -- End
3423
3424 -- Check if the Source and Target Quote's Pricing type are equal
3425 -- if not cash flows are not copied.
3426 IF (lb_copy_cashflow) THEN
3427 lv_pricing_type_equal := is_pricing_method_equal(p_source_quote_id => l_asset_rec.parent_object_id,
3428 p_target_quote_id => p_target_quote_id);
3429
3430 IF (l_parent_object_code <> 'LEASEAPP' AND lv_pricing_type_equal = 'N') THEN
3431 lb_copy_cashflow := FALSE;
3432
3433 -- Nullify Pricing Params when the Pricing method is changed
3434 l_asset_rec.structured_pricing := null;
3435 l_asset_rec.target_arrears := null;
3436 l_asset_rec.lease_rate_factor := null;
3437 l_asset_rec.target_amount := null;
3438 l_asset_rec.target_frequency := null;
3439 --Bug # 5021937
3440 --Duplicate Copying the Pricing Parameters
3441 l_asset_rec.rate_card_id := null;
3442 l_asset_rec.rate_template_id := null;
3443 --Bug #5021937
3444 END IF;
3445 END IF;
3446 -- End
3447
3448 -- Generate the Asset number from the sequence
3449 SELECT okl_qua_ref_seq.nextval INTO l_asset_rec.asset_number FROM DUAL;
3450 l_asset_rec.parent_object_id := p_target_quote_id;
3451 l_asset_rec.orig_asset_id := p_source_asset_id;
3452
3453 -- If product or start date changed for target quote, do not default the
3454 -- end of term option for the asset, as it may not be valid.
3455 IF (NOT lb_default_eot) THEN
3456 l_asset_rec.end_of_term_value_default := NULL;
3457 END IF;
3458 --bug 5172808
3459 IF(lv_tgt_pricing_type = 'SY') THEN
3460 l_asset_rec.structured_pricing := 'Y';
3461 END IF;
3462 -- Duplicate Asset header
3463 okl_ass_pvt.insert_row (
3464 p_api_version => G_API_VERSION
3465 ,p_init_msg_list => G_FALSE
3466 ,x_return_status => x_return_status
3467 ,x_msg_count => x_msg_count
3468 ,x_msg_data => x_msg_data
3469 ,p_assv_rec => l_asset_rec
3470 ,x_assv_rec => lx_asset_rec );
3471
3472 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
3473 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3474 ELSIF x_return_status = G_RET_STS_ERROR THEN
3475 RAISE OKL_API.G_EXCEPTION_ERROR;
3476 END IF;
3477
3478 x_target_asset_id := lx_asset_rec.id;
3479
3480 -- Fetch Asset Components
3481 get_asset_comp_tbl (
3482 p_asset_id => p_source_asset_id
3483 ,x_asset_comp_tbl => l_component_tbl
3484 ,x_return_status => x_return_status);
3485
3486 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
3487 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3488 ELSIF x_return_status = G_RET_STS_ERROR THEN
3489 RAISE OKL_API.G_EXCEPTION_ERROR;
3490 END IF;
3491
3492 -- Update the Asset Components table with the created asset_id
3493 FOR i IN l_component_tbl.FIRST .. l_component_tbl.LAST LOOP
3494 IF l_component_tbl.EXISTS(i) THEN
3495 l_component_tbl(i).asset_id := x_target_asset_id;
3496 l_component_tbl(i).id := null;
3497
3498 IF (l_component_tbl(i).primary_component = 'YES') THEN
3499 -- Check for duplicate from/to 'Solve for Financed Amount' quote
3500 IF (lv_src_pricing_type <> 'SF' AND lv_tgt_pricing_type = 'SF') THEN
3501 l_component_tbl(i).unit_cost := null;
3502 END IF;
3503 -- End
3504 END IF;
3505 END IF;
3506 END LOOP;
3507
3508 -- Component table must contain at least 1 row for Asset
3509 okl_aso_pvt.insert_row (
3510 p_api_version => G_API_VERSION
3511 ,p_init_msg_list => G_FALSE
3512 ,x_return_status => x_return_status
3513 ,x_msg_count => x_msg_count
3514 ,x_msg_data => x_msg_data
3515 ,p_asov_tbl => l_component_tbl
3516 ,x_asov_tbl => lx_component_tbl);
3517
3518 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
3519 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3520 ELSIF x_return_status = G_RET_STS_ERROR THEN
3521 RAISE OKL_API.G_EXCEPTION_ERROR;
3522 END IF;
3523
3524 IF (lb_copy_cashflow) THEN
3525 okl_lease_quote_cashflow_pvt.duplicate_cashflows (
3526 p_api_version => p_api_version
3527 ,p_init_msg_list => p_init_msg_list
3528 ,p_transaction_control => p_transaction_control
3529 ,p_source_object_code => 'QUOTED_ASSET'
3530 ,p_source_object_id => p_source_asset_id
3531 ,p_target_object_id => x_target_asset_id
3532 ,p_quote_id => p_target_quote_id
3533 ,x_return_status => x_return_status
3534 ,x_msg_count => x_msg_count
3535 ,x_msg_data => x_msg_data );
3536
3537 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
3538 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3539 ELSIF x_return_status = G_RET_STS_ERROR THEN
3540 RAISE OKL_API.G_EXCEPTION_ERROR;
3541 END IF;
3542 END IF;
3543 END IF; -- Duplicate Asset
3544
3545 x_return_status := G_RET_STS_SUCCESS;
3546
3547 EXCEPTION
3548
3549 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3550
3551 IF p_transaction_control = G_TRUE THEN
3552 ROLLBACK TO l_program_name;
3553 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3554 END IF;
3555
3556 x_return_status := G_RET_STS_ERROR;
3557
3558 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3559
3560 IF p_transaction_control = G_TRUE THEN
3561 ROLLBACK TO l_program_name;
3562 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3563 END IF;
3564
3565 x_return_status := G_RET_STS_UNEXP_ERROR;
3566
3567 WHEN OTHERS THEN
3568
3569 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
3570 p_msg_name => G_DB_ERROR,
3571 p_token1 => G_PROG_NAME_TOKEN,
3572 p_token1_value => l_api_name,
3573 p_token2 => G_SQLCODE_TOKEN,
3574 p_token2_value => sqlcode,
3575 p_token3 => G_SQLERRM_TOKEN,
3576 p_token3_value => sqlerrm);
3577
3578 IF p_transaction_control = G_TRUE THEN
3579 ROLLBACK TO l_program_name;
3580 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3581 END IF;
3582
3583 x_return_status := G_RET_STS_UNEXP_ERROR;
3584
3585 END duplicate_asset;
3586
3587 ----------------------------
3588 -- PROCEDURE duplicate_asset
3589 ----------------------------
3590 PROCEDURE duplicate_asset (p_api_version IN NUMBER,
3591 p_init_msg_list IN VARCHAR2,
3592 p_transaction_control IN VARCHAR2,
3593 p_source_asset_id IN NUMBER,
3594 p_asset_rec IN asset_rec_type,
3595 p_component_tbl IN asset_component_tbl_type,
3596 p_cf_hdr_rec IN cashflow_hdr_rec_type,
3597 p_cf_level_tbl IN cashflow_level_tbl_type,
3598 x_return_status OUT NOCOPY VARCHAR2,
3599 x_msg_count OUT NOCOPY NUMBER,
3600 x_msg_data OUT NOCOPY VARCHAR2) IS
3601
3602 l_program_name CONSTANT VARCHAR2(30) := 'duplicate_asset';
3603 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3604
3605 l_asset_rec asset_rec_type;
3606 lx_asset_rec asset_rec_type;
3607
3608 l_component_tbl component_tbl_type;
3609 lx_component_tbl component_tbl_type;
3610
3611 l_asset_comp_tbl asset_component_tbl_type;
3612
3613 l_cf_hdr_rec cashflow_hdr_rec_type;
3614 l_cashflow_level_tbl cashflow_level_tbl_type;
3615
3616 l_return_status VARCHAR2(1);
3617
3618 BEGIN
3619
3620 IF p_transaction_control = G_TRUE THEN
3621 SAVEPOINT l_program_name;
3622 END IF;
3623
3624 IF p_init_msg_list = G_TRUE THEN
3625 FND_MSG_PUB.initialize;
3626 END IF;
3627
3628 l_asset_rec := p_asset_rec;
3629 l_asset_comp_tbl := p_component_tbl;
3630 l_cf_hdr_rec := p_cf_hdr_rec;
3631 l_cashflow_level_tbl := p_cf_level_tbl;
3632
3633 -- Generate the Asset number from the sequence
3634 SELECT okl_qua_ref_seq.nextval INTO l_asset_rec.asset_number FROM DUAL;
3635
3636 set_defaults ( p_asset_rec => l_asset_rec
3637 ,p_component_tbl => l_asset_comp_tbl
3638 ,x_return_status => l_return_status );
3639
3640 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3641 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3642 ELSIF l_return_status = G_RET_STS_ERROR THEN
3643 RAISE OKL_API.G_EXCEPTION_ERROR;
3644 END IF;
3645
3646 validate ( p_asset_rec => l_asset_rec
3647 ,p_component_tbl => l_asset_comp_tbl
3648 ,x_return_status => l_return_status );
3649
3650 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3651 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3652 ELSIF l_return_status = G_RET_STS_ERROR THEN
3653 RAISE OKL_API.G_EXCEPTION_ERROR;
3654 END IF;
3655
3656 -- Assign EOT default value
3657 l_asset_rec.end_of_term_value_default := get_eot_default_value(
3658 p_asset_rec => l_asset_rec,
3659 p_asset_comp_tbl => l_asset_comp_tbl,
3660 x_return_status => l_return_status);
3661 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3662 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3663 ELSIF l_return_status = G_RET_STS_ERROR THEN
3664 RAISE OKL_API.G_EXCEPTION_ERROR;
3665 END IF;
3666 -- End
3667
3668 -- This populates other asset attributes which are not visible from the
3669 -- duplicate asset page
3670 populate_asset_attribs(p_source_asset_id => p_source_asset_id,
3671 x_asset_rec => l_asset_rec,
3672 x_return_status => l_return_status);
3673 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3674 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3675 ELSIF l_return_status = G_RET_STS_ERROR THEN
3676 RAISE OKL_API.G_EXCEPTION_ERROR;
3677 END IF;
3678
3679 okl_ass_pvt.insert_row (
3680 p_api_version => G_API_VERSION
3681 ,p_init_msg_list => G_FALSE
3682 ,x_return_status => l_return_status
3683 ,x_msg_count => x_msg_count
3684 ,x_msg_data => x_msg_data
3685 ,p_assv_rec => l_asset_rec
3686 ,x_assv_rec => lx_asset_rec
3687 );
3688
3689 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3690 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3691 ELSIF l_return_status = G_RET_STS_ERROR THEN
3692 RAISE OKL_API.G_EXCEPTION_ERROR;
3693 END IF;
3694
3695 -- Update the Asset Components table with the created asset_id
3696 FOR i IN l_asset_comp_tbl.FIRST .. l_asset_comp_tbl.LAST LOOP
3697 IF l_asset_comp_tbl.EXISTS(i) THEN
3698 l_component_tbl(i).asset_id := lx_asset_rec.id;
3699 l_component_tbl(i).inv_item_id := l_asset_comp_tbl(i).inv_item_id;
3700 l_component_tbl(i).supplier_id := l_asset_comp_tbl(i).supplier_id;
3701 l_component_tbl(i).primary_component := l_asset_comp_tbl(i).primary_component;
3702 l_component_tbl(i).unit_cost := l_asset_comp_tbl(i).unit_cost;
3703 l_component_tbl(i).number_of_units := l_asset_comp_tbl(i).number_of_units;
3704 l_component_tbl(i).manufacturer_name := l_asset_comp_tbl(i).manufacturer_name;
3705 l_component_tbl(i).year_manufactured := l_asset_comp_tbl(i).year_manufactured;
3706 l_component_tbl(i).model_number := l_asset_comp_tbl(i).model_number;
3707 l_component_tbl(i).short_description := l_asset_comp_tbl(i).short_description;
3708 l_component_tbl(i).description := l_asset_comp_tbl(i).description;
3709 l_component_tbl(i).comments := l_asset_comp_tbl(i).comments;
3710 END IF;
3711 END LOOP;
3712
3713 -- Component table must contain at least 1 row for Asset
3714 okl_aso_pvt.insert_row (
3715 p_api_version => G_API_VERSION
3716 ,p_init_msg_list => G_FALSE
3717 ,x_return_status => l_return_status
3718 ,x_msg_count => x_msg_count
3719 ,x_msg_data => x_msg_data
3720 ,p_asov_tbl => l_component_tbl
3721 ,x_asov_tbl => lx_component_tbl );
3722
3723 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3724 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3725 ELSIF l_return_status = G_RET_STS_ERROR THEN
3726 RAISE OKL_API.G_EXCEPTION_ERROR;
3727 END IF;
3728
3729 duplicate_asset_cashflows (
3730 p_api_version => p_api_version
3731 ,p_init_msg_list => p_init_msg_list
3732 ,p_transaction_control => p_transaction_control
3733 ,p_source_object => 'QUOTED_ASSET'
3734 ,p_source_id => p_source_asset_id
3735 ,p_target_id => lx_asset_rec.id
3736 ,p_quote_id => lx_asset_rec.parent_object_id
3737 ,x_return_status => x_return_status
3738 ,x_msg_count => x_msg_count
3739 ,x_msg_data => x_msg_data );
3740
3741 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
3742 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3743 ELSIF x_return_status = G_RET_STS_ERROR THEN
3744 RAISE OKL_API.G_EXCEPTION_ERROR;
3745 END IF;
3746
3747 -- Estimated Property Tax Payment
3748 IF ((l_cashflow_level_tbl.COUNT > 0 AND l_cf_hdr_rec.stream_type_id IS NULL) OR
3749 (l_cf_hdr_rec.stream_type_id IS NOT NULL AND l_cashflow_level_tbl.COUNT = 0 )) THEN
3750 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
3751 p_msg_name => 'OKL_EPT_PAYMENT_NA');
3752 RAISE OKL_API.G_EXCEPTION_ERROR;
3753 ELSIF (l_cashflow_level_tbl.COUNT > 0 AND l_cf_hdr_rec.stream_type_id IS NOT NULL) THEN
3754 l_cf_hdr_rec.parent_object_id := lx_asset_rec.id;
3755 OKL_LEASE_QUOTE_CASHFLOW_PVT.create_cashflow (
3756 p_api_version => G_API_VERSION
3757 ,p_init_msg_list => G_FALSE
3758 ,p_transaction_control => 'T'
3759 ,p_cashflow_header_rec => l_cf_hdr_rec
3760 ,p_cashflow_level_tbl => l_cashflow_level_tbl
3761 ,x_return_status => l_return_status
3762 ,x_msg_count => x_msg_count
3763 ,x_msg_data => x_msg_data);
3764
3765 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3766 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3767 ELSIF l_return_status = G_RET_STS_ERROR THEN
3768 RAISE OKL_API.G_EXCEPTION_ERROR;
3769 END IF;
3770 END IF;
3771
3772 x_return_status := G_RET_STS_SUCCESS;
3773 EXCEPTION
3774
3775 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3776
3777 IF p_transaction_control = G_TRUE THEN
3778 ROLLBACK TO l_program_name;
3779 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3780 END IF;
3781
3782 x_return_status := G_RET_STS_ERROR;
3783
3784 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3785
3786 IF p_transaction_control = G_TRUE THEN
3787 ROLLBACK TO l_program_name;
3788 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3789 END IF;
3790
3791 x_return_status := G_RET_STS_UNEXP_ERROR;
3792
3793 WHEN OTHERS THEN
3794
3795 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
3796 p_msg_name => G_DB_ERROR,
3797 p_token1 => G_PROG_NAME_TOKEN,
3798 p_token1_value => l_api_name,
3799 p_token2 => G_SQLCODE_TOKEN,
3800 p_token2_value => sqlcode,
3801 p_token3 => G_SQLERRM_TOKEN,
3802 p_token3_value => sqlerrm);
3803
3804 IF p_transaction_control = G_TRUE THEN
3805 ROLLBACK TO l_program_name;
3806 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3807 END IF;
3808
3809 x_return_status := G_RET_STS_UNEXP_ERROR;
3810
3811 END duplicate_asset;
3812
3813 -------------------------
3814 -- PROCEDURE create_asset
3815 -------------------------
3816 PROCEDURE create_asset (p_api_version IN NUMBER,
3817 p_init_msg_list IN VARCHAR2,
3818 p_transaction_control IN VARCHAR2,
3819 p_asset_rec IN asset_rec_type,
3820 p_component_tbl IN asset_component_tbl_type,
3821 p_cf_hdr_rec IN cashflow_hdr_rec_type,
3822 p_cf_level_tbl IN cashflow_level_tbl_type,
3823 x_return_status OUT NOCOPY VARCHAR2,
3824 x_msg_count OUT NOCOPY NUMBER,
3825 x_msg_data OUT NOCOPY VARCHAR2) IS
3826
3827 l_program_name CONSTANT VARCHAR2(30) := 'create_asset';
3828 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3829
3830 l_asset_rec asset_rec_type;
3831 lx_asset_rec asset_rec_type;
3832
3833 l_component_tbl component_tbl_type;
3834 lx_component_tbl component_tbl_type;
3835
3836 l_asset_comp_tbl asset_component_tbl_type;
3837
3838 l_cf_hdr_rec cashflow_hdr_rec_type;
3839 l_cashflow_level_tbl cashflow_level_tbl_type;
3840
3841 l_return_status VARCHAR2(1);
3842 l_p_id NUMBER;
3843 l_p_code VARCHAR2(30);
3844
3845 BEGIN
3846
3847 IF p_transaction_control = G_TRUE THEN
3848 SAVEPOINT l_program_name;
3849 END IF;
3850
3851 IF p_init_msg_list = G_TRUE THEN
3852 FND_MSG_PUB.initialize;
3853 END IF;
3854
3855 l_asset_rec := p_asset_rec;
3856 l_asset_comp_tbl := p_component_tbl;
3857 l_cf_hdr_rec := p_cf_hdr_rec;
3858 l_cashflow_level_tbl := p_cf_level_tbl;
3859
3860 -- Generate the Asset number from the sequence
3861 SELECT okl_qua_ref_seq.nextval INTO l_asset_rec.asset_number FROM DUAL;
3862
3863 set_defaults (p_asset_rec => l_asset_rec
3864 ,p_component_tbl => l_asset_comp_tbl
3865 ,x_return_status => l_return_status );
3866
3867 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3868 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3869 ELSIF l_return_status = G_RET_STS_ERROR THEN
3870 RAISE OKL_API.G_EXCEPTION_ERROR;
3871 END IF;
3872
3873 validate (p_asset_rec => l_asset_rec
3874 ,p_component_tbl => l_asset_comp_tbl
3875 ,x_return_status => l_return_status );
3876
3877 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3878 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3879 ELSIF l_return_status = G_RET_STS_ERROR THEN
3880 RAISE OKL_API.G_EXCEPTION_ERROR;
3881 END IF;
3882
3883 -- Assign EOT default value
3884 l_asset_rec.end_of_term_value_default := get_eot_default_value(
3885 p_asset_rec => l_asset_rec,
3886 p_asset_comp_tbl => l_asset_comp_tbl,
3887 x_return_status => l_return_status);
3888 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3889 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3890 ELSIF l_return_status = G_RET_STS_ERROR THEN
3891 RAISE OKL_API.G_EXCEPTION_ERROR;
3892 END IF;
3893 -- End
3894
3895 okl_ass_pvt.insert_row (p_api_version => G_API_VERSION
3896 ,p_init_msg_list => G_FALSE
3897 ,x_return_status => l_return_status
3898 ,x_msg_count => x_msg_count
3899 ,x_msg_data => x_msg_data
3900 ,p_assv_rec => l_asset_rec
3901 ,x_assv_rec => lx_asset_rec );
3902
3903 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3904 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3905 ELSIF l_return_status = G_RET_STS_ERROR THEN
3906 RAISE OKL_API.G_EXCEPTION_ERROR;
3907 END IF;
3908
3909 -- Update the Asset Components table with the created asset_id
3910 FOR i IN l_asset_comp_tbl.FIRST .. l_asset_comp_tbl.LAST LOOP
3911 IF l_asset_comp_tbl.EXISTS(i) THEN
3912 l_component_tbl(i).asset_id := lx_asset_rec.id;
3913 l_component_tbl(i).inv_item_id := l_asset_comp_tbl(i).inv_item_id;
3914 l_component_tbl(i).supplier_id := l_asset_comp_tbl(i).supplier_id;
3915 l_component_tbl(i).primary_component := l_asset_comp_tbl(i).primary_component;
3916 l_component_tbl(i).unit_cost := l_asset_comp_tbl(i).unit_cost;
3917 l_component_tbl(i).number_of_units := l_asset_comp_tbl(i).number_of_units;
3918 l_component_tbl(i).manufacturer_name := l_asset_comp_tbl(i).manufacturer_name;
3919 l_component_tbl(i).year_manufactured := l_asset_comp_tbl(i).year_manufactured;
3920 l_component_tbl(i).model_number := l_asset_comp_tbl(i).model_number;
3921 l_component_tbl(i).short_description := l_asset_comp_tbl(i).short_description;
3922 l_component_tbl(i).description := l_asset_comp_tbl(i).description;
3923 l_component_tbl(i).comments := l_asset_comp_tbl(i).comments;
3924 END IF;
3925 END LOOP;
3926
3927 -- Component table must contain at least 1 row for Asset
3928 okl_aso_pvt.insert_row (p_api_version => G_API_VERSION
3929 ,p_init_msg_list => G_FALSE
3930 ,x_return_status => l_return_status
3931 ,x_msg_count => x_msg_count
3932 ,x_msg_data => x_msg_data
3933 ,p_asov_tbl => l_component_tbl
3934 ,x_asov_tbl => lx_component_tbl );
3935
3936 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3937 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3938 ELSIF l_return_status = G_RET_STS_ERROR THEN
3939 RAISE OKL_API.G_EXCEPTION_ERROR;
3940 END IF;
3941
3942 -- Estimated Property Tax Payment
3943 IF ((l_cashflow_level_tbl.COUNT > 0 AND l_cf_hdr_rec.stream_type_id IS NULL) OR
3944 (l_cf_hdr_rec.stream_type_id IS NOT NULL AND l_cashflow_level_tbl.COUNT = 0 )) THEN
3945 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
3946 p_msg_name => 'OKL_EPT_PAYMENT_NA');
3947 RAISE OKL_API.G_EXCEPTION_ERROR;
3948 ELSIF (l_cf_hdr_rec.stream_type_id IS NOT NULL AND l_cashflow_level_tbl.COUNT > 0 ) THEN
3949 l_cf_hdr_rec.parent_object_id := lx_asset_rec.id;
3950 OKL_LEASE_QUOTE_CASHFLOW_PVT.create_cashflow (
3951 p_api_version => G_API_VERSION
3952 ,p_init_msg_list => G_FALSE
3953 ,p_transaction_control => 'T'
3954 ,p_cashflow_header_rec => l_cf_hdr_rec
3955 ,p_cashflow_level_tbl => l_cashflow_level_tbl
3956 ,x_return_status => l_return_status
3957 ,x_msg_count => x_msg_count
3958 ,x_msg_data => x_msg_data);
3959
3960 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3961 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3962 ELSIF l_return_status = G_RET_STS_ERROR THEN
3963 RAISE OKL_API.G_EXCEPTION_ERROR;
3964 END IF;
3965 END IF;
3966
3967 /*SELECT parent_object_id,parent_object_code INTO l_p_id,l_p_code
3968 FROM okl_lease_quotes_b where ID = l_asset_rec.parent_object_id;
3969
3970 OKL_LEASE_QUOTE_PRICING_PVT.handle_parent_object_status(
3971 p_api_version => G_API_VERSION
3972 ,p_init_msg_list => G_FALSE
3973 ,x_return_status => x_return_status
3974 ,x_msg_count => x_msg_count
3975 ,x_msg_data => x_msg_data
3976 ,p_parent_object_code => l_p_code
3977 ,p_parent_object_id => l_p_id
3978 );
3979
3980 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
3981 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3982 ELSIF x_return_status = G_RET_STS_ERROR THEN
3983 RAISE OKL_API.G_EXCEPTION_ERROR;
3984 END IF;*/
3985
3986 x_return_status := G_RET_STS_SUCCESS;
3987 EXCEPTION
3988
3989 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3990
3991 IF p_transaction_control = G_TRUE THEN
3992 ROLLBACK TO l_program_name;
3993 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3994 END IF;
3995
3996 x_return_status := G_RET_STS_ERROR;
3997
3998 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3999
4000 IF p_transaction_control = G_TRUE THEN
4001 ROLLBACK TO l_program_name;
4002 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4003 END IF;
4004
4005 x_return_status := G_RET_STS_UNEXP_ERROR;
4006
4007 WHEN OTHERS THEN
4008
4009 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
4010 p_msg_name => G_DB_ERROR,
4011 p_token1 => G_PROG_NAME_TOKEN,
4012 p_token1_value => l_api_name,
4013 p_token2 => G_SQLCODE_TOKEN,
4014 p_token2_value => sqlcode,
4015 p_token3 => G_SQLERRM_TOKEN,
4016 p_token3_value => sqlerrm);
4017
4018 IF p_transaction_control = G_TRUE THEN
4019 ROLLBACK TO l_program_name;
4020 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4021 END IF;
4022
4023 x_return_status := G_RET_STS_UNEXP_ERROR;
4024
4025 END create_asset;
4026
4027
4028 -------------------------
4029 -- PROCEDURE delete_asset
4030 -------------------------
4031 PROCEDURE delete_asset (p_api_version IN NUMBER,
4032 p_init_msg_list IN VARCHAR2,
4033 p_transaction_control IN VARCHAR2,
4034 p_asset_id IN NUMBER,
4035 x_return_status OUT NOCOPY VARCHAR2,
4036 x_msg_count OUT NOCOPY NUMBER,
4037 x_msg_data OUT NOCOPY VARCHAR2) IS
4038
4039 l_program_name CONSTANT VARCHAR2(30) := 'delete_asset';
4040 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
4041
4042 l_component_tbl component_tbl_type;
4043 l_cashflow_object_rec cf_object_rec_type;
4044 l_cashflow_hdr_rec cf_header_rec_type;
4045 l_cashflow_level_tbl cf_level_tbl_type;
4046 l_asset_rec asset_rec_type;
4047
4048 l_return_status VARCHAR2(1);
4049 i BINARY_INTEGER := 0;
4050 l_asset_id OKL_ASSETS_B.ID%TYPE;
4051 l_p_id NUMBER;
4052 l_p_code VARCHAR2(30);
4053
4054 -- Cursors to check the existence of asset information
4055 CURSOR c_get_asset_components(p_asset_id OKL_ASSETS_B.ID%TYPE)
4056 IS
4057 SELECT ID
4058 FROM OKL_ASSET_COMPONENTS_B
4059 WHERE ASSET_ID = p_asset_id;
4060
4061 CURSOR c_get_line_relationships(p_asset_id OKL_ASSETS_B.ID%TYPE)
4062 IS
4063 SELECT ID
4064 FROM OKL_LINE_RELATIONSHIPS_B
4065 WHERE SOURCE_LINE_ID = p_asset_id;
4066
4067 BEGIN
4068 IF p_transaction_control = G_TRUE THEN
4069 SAVEPOINT l_program_name;
4070 END IF;
4071
4072 IF p_init_msg_list = G_TRUE THEN
4073 FND_MSG_PUB.initialize;
4074 END IF;
4075
4076 l_asset_id := p_asset_id;
4077
4078 -- Asset primary component and addon information
4079 FOR l_asset_components IN c_get_asset_components(p_asset_id => l_asset_id) LOOP
4080 l_component_tbl(i).id := l_asset_components.id;
4081 i := i + 1;
4082 END LOOP;
4083
4084 IF l_component_tbl.COUNT > 0 THEN
4085 okl_aso_pvt.delete_row (
4086 p_api_version => G_API_VERSION
4087 ,p_init_msg_list => G_FALSE
4088 ,x_return_status => l_return_status
4089 ,x_msg_count => x_msg_count
4090 ,x_msg_data => x_msg_data
4091 ,p_asov_tbl => l_component_tbl);
4092 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4093 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4094 ELSIF l_return_status = G_RET_STS_ERROR THEN
4095 RAISE OKL_API.G_EXCEPTION_ERROR;
4096 END IF;
4097 END IF;
4098 -- End
4099
4100 -- Cash Flow information
4101 okl_lease_quote_cashflow_pvt.delete_cashflows (
4102 p_api_version => G_API_VERSION
4103 ,p_init_msg_list => G_FALSE
4104 ,p_transaction_control => G_FALSE
4105 ,p_source_object_code => 'QUOTED_ASSET'
4106 ,p_source_object_id => p_asset_id
4107 ,x_return_status => l_return_status
4108 ,x_msg_count => x_msg_count
4109 ,x_msg_data => x_msg_data );
4110
4111 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4112 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4113 ELSIF l_return_status = G_RET_STS_ERROR THEN
4114 RAISE OKL_API.G_EXCEPTION_ERROR;
4115 END IF;
4116 -- End
4117
4118 -- Delete Asset information
4119 l_asset_rec.id := l_asset_id;
4120 OKL_ASS_PVT.delete_row (
4121 p_api_version => G_API_VERSION
4122 ,p_init_msg_list => G_FALSE
4123 ,x_return_status => l_return_status
4124 ,x_msg_count => x_msg_count
4125 ,x_msg_data => x_msg_data
4126 ,p_assv_rec => l_asset_rec);
4127
4128 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4129 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4130 ELSIF l_return_status = G_RET_STS_ERROR THEN
4131 RAISE OKL_API.G_EXCEPTION_ERROR;
4132 END IF;
4133 -- End
4134
4135 /*SELECT qte.parent_object_id,qte.parent_object_code
4136 INTO l_p_id,l_p_code
4137 FROM okl_lease_quotes_b qte,
4138 okl_assets_b ast
4139 WHERE ast.parent_object_id = qte.id
4140 AND ast.ID = l_asset_id;
4141
4142 OKL_LEASE_QUOTE_PRICING_PVT.handle_parent_object_status(
4143 p_api_version => G_API_VERSION
4144 ,p_init_msg_list => G_FALSE
4145 ,x_return_status => x_return_status
4146 ,x_msg_count => x_msg_count
4147 ,x_msg_data => x_msg_data
4148 ,p_parent_object_code => l_p_code
4149 ,p_parent_object_id => l_p_id
4150 );
4151
4152 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
4153 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4154 ELSIF x_return_status = G_RET_STS_ERROR THEN
4155 RAISE OKL_API.G_EXCEPTION_ERROR;
4156 END IF;*/
4157
4158
4159 x_return_status := G_RET_STS_SUCCESS;
4160 EXCEPTION
4161 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4162
4163 IF p_transaction_control = G_TRUE THEN
4164 ROLLBACK TO l_program_name;
4165 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4166 END IF;
4167
4168 x_return_status := G_RET_STS_ERROR;
4169
4170 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4171
4172 IF p_transaction_control = G_TRUE THEN
4173 ROLLBACK TO l_program_name;
4174 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4175 END IF;
4176
4177 x_return_status := G_RET_STS_UNEXP_ERROR;
4178
4179 WHEN OTHERS THEN
4180
4181 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
4182 p_msg_name => G_DB_ERROR,
4183 p_token1 => G_PROG_NAME_TOKEN,
4184 p_token1_value => l_api_name,
4185 p_token2 => G_SQLCODE_TOKEN,
4186 p_token2_value => sqlcode,
4187 p_token3 => G_SQLERRM_TOKEN,
4188 p_token3_value => sqlerrm);
4189
4190 IF p_transaction_control = G_TRUE THEN
4191 ROLLBACK TO l_program_name;
4192 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4193 END IF;
4194
4195 x_return_status := G_RET_STS_UNEXP_ERROR;
4196
4197 END delete_asset;
4198
4199 -------------------------
4200 -- PROCEDURE update_asset
4201 -------------------------
4202 PROCEDURE update_asset (p_api_version IN NUMBER,
4203 p_init_msg_list IN VARCHAR2,
4204 p_transaction_control IN VARCHAR2,
4205 p_asset_rec IN asset_rec_type,
4206 p_component_tbl IN asset_component_tbl_type,
4207 p_cf_hdr_rec IN cashflow_hdr_rec_type,
4208 p_cf_level_tbl IN cashflow_level_tbl_type,
4209 x_return_status OUT NOCOPY VARCHAR2,
4210 x_msg_count OUT NOCOPY NUMBER,
4211 x_msg_data OUT NOCOPY VARCHAR2) IS
4212
4213 l_program_name CONSTANT VARCHAR2(30) := 'update_asset';
4214 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
4215
4216 l_asset_rec asset_rec_type;
4217 lx_asset_rec asset_rec_type;
4218 l_copy_asset_rec asset_rec_type;
4219
4220 l_component_tbl component_tbl_type;
4221 lx_component_tbl component_tbl_type;
4222 l_deleted_addon_tbl component_tbl_type;
4223
4224 l_asset_comp_tbl asset_component_tbl_type;
4225 l_copy_asset_comp_tbl component_tbl_type;
4226
4227 l_cf_hdr_rec cashflow_hdr_rec_type;
4228 l_cashflow_level_tbl cashflow_level_tbl_type;
4229
4230 l_return_status VARCHAR2(1);
4231 lv_cash_flow_exists VARCHAR2(3);
4232
4233 l_p_id NUMBER;
4234 l_p_code VARCHAR2(30);
4235
4236 CURSOR c_check_cash_flow(p_asset_id OKL_ASSETS_B.ID%TYPE)
4237 IS
4238 SELECT 'YES'
4239 FROM OKL_CASH_FLOW_OBJECTS
4240 WHERE OTY_CODE = 'QUOTED_ASSET'
4241 AND SOURCE_TABLE = 'OKL_ASSETS_B'
4242 AND SOURCE_ID = p_asset_id;
4243
4244 BEGIN
4245
4246 IF p_transaction_control = G_TRUE THEN
4247 SAVEPOINT l_program_name;
4248 END IF;
4249
4250 IF p_init_msg_list = G_TRUE THEN
4251 FND_MSG_PUB.initialize;
4252 END IF;
4253
4254 -- Check if the asset is modified through process other than asset ui
4255 IF (p_asset_rec.parent_object_code IS NULL OR
4256 p_asset_rec.object_version_number IS NULL) THEN
4257
4258 -- Fetch Asset Info
4259 get_asset_rec ( p_asset_id => p_asset_rec.id,
4260 x_asset_rec => l_copy_asset_rec,
4261 x_return_status => l_return_status );
4262 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4263 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4264 ELSIF l_return_status = G_RET_STS_ERROR THEN
4265 RAISE OKL_API.G_EXCEPTION_ERROR;
4266 END IF;
4267
4268 -- Sync Asset Info
4269 sync_asset_values(x_asset_rec => l_copy_asset_rec,
4270 p_input_rec => p_asset_rec,
4271 x_return_status => l_return_status);
4272
4273 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4274 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4275 ELSIF l_return_status = G_RET_STS_ERROR THEN
4276 RAISE OKL_API.G_EXCEPTION_ERROR;
4277 END IF;
4278
4279 l_asset_rec := l_copy_asset_rec;
4280
4281 -- Fetch Asset Components info
4282 IF (p_component_tbl.COUNT > 0) THEN
4283 get_asset_comp_tbl (p_asset_id => p_asset_rec.id
4284 ,x_asset_comp_tbl => l_copy_asset_comp_tbl
4285 ,x_return_status => l_return_status);
4286 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4287 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4288 ELSIF l_return_status = G_RET_STS_ERROR THEN
4289 RAISE OKL_API.G_EXCEPTION_ERROR;
4290 END IF;
4291
4292 -- Sync Asset Components Info
4293 sync_asset_comp_values(x_asset_comp_tbl => l_copy_asset_comp_tbl,
4294 p_input_comp_tbl => p_component_tbl,
4295 x_return_status => l_return_status);
4296 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4297 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4298 ELSIF l_return_status = G_RET_STS_ERROR THEN
4299 RAISE OKL_API.G_EXCEPTION_ERROR;
4300 END IF;
4301
4302 FOR i IN l_copy_asset_comp_tbl.FIRST .. l_copy_asset_comp_tbl.LAST LOOP
4303 IF l_copy_asset_comp_tbl.EXISTS(i) THEN
4304 l_asset_comp_tbl(i).id := l_copy_asset_comp_tbl(i).id;
4305 l_asset_comp_tbl(i).object_version_number := l_copy_asset_comp_tbl(i).object_version_number;
4306 l_asset_comp_tbl(i).asset_id := l_copy_asset_comp_tbl(i).asset_id;
4307 l_asset_comp_tbl(i).inv_item_id := l_copy_asset_comp_tbl(i).inv_item_id;
4308 l_asset_comp_tbl(i).supplier_id := l_copy_asset_comp_tbl(i).supplier_id;
4309 l_asset_comp_tbl(i).primary_component := l_copy_asset_comp_tbl(i).primary_component;
4310 l_asset_comp_tbl(i).unit_cost := l_copy_asset_comp_tbl(i).unit_cost;
4311 l_asset_comp_tbl(i).number_of_units := l_copy_asset_comp_tbl(i).number_of_units;
4312 l_asset_comp_tbl(i).manufacturer_name := l_copy_asset_comp_tbl(i).manufacturer_name;
4313 l_asset_comp_tbl(i).year_manufactured := l_copy_asset_comp_tbl(i).year_manufactured;
4314 l_asset_comp_tbl(i).model_number := l_copy_asset_comp_tbl(i).model_number;
4315 l_asset_comp_tbl(i).short_description := l_copy_asset_comp_tbl(i).short_description;
4316 l_asset_comp_tbl(i).description := l_copy_asset_comp_tbl(i).description;
4317 l_asset_comp_tbl(i).comments := l_copy_asset_comp_tbl(i).comments;
4318 l_asset_comp_tbl(i).record_mode := 'update';
4319 END IF;
4320 END LOOP;
4321 END IF;
4322
4323 ELSE
4324 l_asset_rec := p_asset_rec;
4325 l_asset_comp_tbl := p_component_tbl;
4326 END IF;
4327 -- end
4328
4329 l_cf_hdr_rec := p_cf_hdr_rec;
4330 l_cashflow_level_tbl := p_cf_level_tbl;
4331
4332 IF (l_asset_comp_tbl.COUNT > 0) THEN
4333 get_deleted_addons (p_asset_id => l_asset_rec.id
4334 ,p_component_tbl => l_asset_comp_tbl
4335 ,x_deleted_addon_tbl => l_deleted_addon_tbl
4336 ,x_return_status => l_return_status );
4337
4338 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4339 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4340 ELSIF l_return_status = G_RET_STS_ERROR THEN
4341 RAISE OKL_API.G_EXCEPTION_ERROR;
4342 END IF;
4343
4344 IF l_deleted_addon_tbl.COUNT > 0 THEN
4345 okl_aso_pvt.delete_row (p_api_version => G_API_VERSION
4346 ,p_init_msg_list => G_FALSE
4347 ,x_return_status => l_return_status
4348 ,x_msg_count => x_msg_count
4349 ,x_msg_data => x_msg_data
4350 ,p_asov_tbl => l_deleted_addon_tbl );
4351
4352 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4353 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4354 ELSIF l_return_status = G_RET_STS_ERROR THEN
4355 RAISE OKL_API.G_EXCEPTION_ERROR;
4356 END IF;
4357 END IF;
4358
4359 set_defaults (p_asset_rec => l_asset_rec
4360 ,p_component_tbl => l_asset_comp_tbl
4361 ,x_return_status => l_return_status );
4362
4363 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4364 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4365 ELSIF l_return_status = G_RET_STS_ERROR THEN
4366 RAISE OKL_API.G_EXCEPTION_ERROR;
4367 END IF;
4368
4369 validate (p_asset_rec => l_asset_rec
4370 ,p_component_tbl => l_asset_comp_tbl
4371 ,x_return_status => l_return_status );
4372
4373 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4374 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4375 ELSIF l_return_status = G_RET_STS_ERROR THEN
4376 RAISE OKL_API.G_EXCEPTION_ERROR;
4377 END IF;
4378
4379 -- Assign EOT default value
4380 l_asset_rec.end_of_term_value_default := get_eot_default_value(
4381 p_asset_rec => l_asset_rec,
4382 p_asset_comp_tbl => l_asset_comp_tbl,
4383 x_return_status => l_return_status);
4384 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4385 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4386 ELSIF l_return_status = G_RET_STS_ERROR THEN
4387 RAISE OKL_API.G_EXCEPTION_ERROR;
4388 END IF;
4389 -- End
4390
4391 END IF;
4392
4393 okl_ass_pvt.update_row (p_api_version => G_API_VERSION
4394 ,p_init_msg_list => G_FALSE
4395 ,x_return_status => l_return_status
4396 ,x_msg_count => x_msg_count
4397 ,x_msg_data => x_msg_data
4398 ,p_assv_rec => l_asset_rec
4399 ,x_assv_rec => lx_asset_rec );
4400 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4401 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4402 ELSIF l_return_status = G_RET_STS_ERROR THEN
4403 RAISE OKL_API.G_EXCEPTION_ERROR;
4404 END IF;
4405
4406 IF (l_asset_comp_tbl.COUNT > 0) THEN
4407 -- Update or Create Add-ons basing on the record mode
4408 FOR i IN l_asset_comp_tbl.FIRST .. l_asset_comp_tbl.LAST LOOP
4409 IF l_asset_comp_tbl.EXISTS(i) THEN
4410 IF l_asset_comp_tbl(i).record_mode = 'create' THEN
4411
4412 l_component_tbl(i).asset_id := l_asset_comp_tbl(i).asset_id;
4413 l_component_tbl(i).inv_item_id := l_asset_comp_tbl(i).inv_item_id;
4414 l_component_tbl(i).supplier_id := l_asset_comp_tbl(i).supplier_id;
4415 l_component_tbl(i).primary_component := l_asset_comp_tbl(i).primary_component;
4416 l_component_tbl(i).unit_cost := l_asset_comp_tbl(i).unit_cost;
4417 l_component_tbl(i).number_of_units := l_asset_comp_tbl(i).number_of_units;
4418 l_component_tbl(i).manufacturer_name := l_asset_comp_tbl(i).manufacturer_name;
4419 l_component_tbl(i).year_manufactured := l_asset_comp_tbl(i).year_manufactured;
4420 l_component_tbl(i).model_number := l_asset_comp_tbl(i).model_number;
4421 l_component_tbl(i).short_description := l_asset_comp_tbl(i).short_description;
4422 l_component_tbl(i).description := l_asset_comp_tbl(i).description;
4423 l_component_tbl(i).comments := l_asset_comp_tbl(i).comments;
4424
4425 okl_aso_pvt.insert_row ( p_api_version => G_API_VERSION
4426 ,p_init_msg_list => G_FALSE
4427 ,x_return_status => l_return_status
4428 ,x_msg_count => x_msg_count
4429 ,x_msg_data => x_msg_data
4430 ,p_asov_rec => l_component_tbl(i)
4431 ,x_asov_rec => lx_component_tbl(i));
4432 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4433 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4434 ELSIF l_return_status = G_RET_STS_ERROR THEN
4435 RAISE OKL_API.G_EXCEPTION_ERROR;
4436 END IF;
4437 ELSIF l_asset_comp_tbl(i).record_mode = 'update' THEN
4438
4439 l_component_tbl(i).id := l_asset_comp_tbl(i).id;
4440 l_component_tbl(i).object_version_number := l_asset_comp_tbl(i).object_version_number;
4441 l_component_tbl(i).asset_id := l_asset_comp_tbl(i).asset_id;
4442 l_component_tbl(i).inv_item_id := l_asset_comp_tbl(i).inv_item_id;
4443 l_component_tbl(i).supplier_id := l_asset_comp_tbl(i).supplier_id;
4444 l_component_tbl(i).primary_component := l_asset_comp_tbl(i).primary_component;
4445 l_component_tbl(i).unit_cost := l_asset_comp_tbl(i).unit_cost;
4446 l_component_tbl(i).number_of_units := l_asset_comp_tbl(i).number_of_units;
4447 l_component_tbl(i).manufacturer_name := l_asset_comp_tbl(i).manufacturer_name;
4448 l_component_tbl(i).year_manufactured := l_asset_comp_tbl(i).year_manufactured;
4449 l_component_tbl(i).model_number := l_asset_comp_tbl(i).model_number;
4450 l_component_tbl(i).short_description := l_asset_comp_tbl(i).short_description;
4451 l_component_tbl(i).description := l_asset_comp_tbl(i).description;
4452 l_component_tbl(i).comments := l_asset_comp_tbl(i).comments;
4453
4454 okl_aso_pvt.update_row ( p_api_version => G_API_VERSION
4455 ,p_init_msg_list => G_FALSE
4456 ,x_return_status => l_return_status
4457 ,x_msg_count => x_msg_count
4458 ,x_msg_data => x_msg_data
4459 ,p_asov_rec => l_component_tbl(i)
4460 ,x_asov_rec => lx_component_tbl(i));
4461 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4462 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4463 ELSIF l_return_status = G_RET_STS_ERROR THEN
4464 RAISE OKL_API.G_EXCEPTION_ERROR;
4465 END IF;
4466 END IF;
4467 END IF;
4468 END LOOP;
4469 END IF;
4470
4471 -- Estimated Property Tax Payment
4472 IF ((l_cashflow_level_tbl.COUNT > 0 AND l_cf_hdr_rec.stream_type_id IS NULL) OR
4473 (l_cf_hdr_rec.stream_type_id IS NOT NULL AND l_cashflow_level_tbl.COUNT = 0 )) THEN
4474 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
4475 p_msg_name => 'OKL_EPT_PAYMENT_NA');
4476 RAISE OKL_API.G_EXCEPTION_ERROR;
4477 ELSIF (l_cf_hdr_rec.stream_type_id IS NOT NULL AND l_cashflow_level_tbl.COUNT > 0 ) THEN
4478 -- Check if the Cash flows already exists
4479 OPEN c_check_cash_flow(p_asset_id => lx_asset_rec.id);
4480 FETCH c_check_cash_flow into lv_cash_flow_exists;
4481 CLOSE c_check_cash_flow;
4482
4483 l_cf_hdr_rec.parent_object_id := lx_asset_rec.id;
4484 IF (lv_cash_flow_exists = 'YES') THEN
4485 OKL_LEASE_QUOTE_CASHFLOW_PVT.update_cashflow (
4486 p_api_version => G_API_VERSION
4487 ,p_init_msg_list => G_FALSE
4488 ,p_transaction_control => 'T'
4489 ,p_cashflow_header_rec => l_cf_hdr_rec
4490 ,p_cashflow_level_tbl => l_cashflow_level_tbl
4491 ,x_return_status => l_return_status
4492 ,x_msg_count => x_msg_count
4493 ,x_msg_data => x_msg_data);
4494
4495 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4496 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4497 ELSIF l_return_status = G_RET_STS_ERROR THEN
4498 RAISE OKL_API.G_EXCEPTION_ERROR;
4499 END IF;
4500 ELSE
4501 OKL_LEASE_QUOTE_CASHFLOW_PVT.create_cashflow (
4502 p_api_version => G_API_VERSION
4503 ,p_init_msg_list => G_FALSE
4504 ,p_transaction_control => 'T'
4505 ,p_cashflow_header_rec => l_cf_hdr_rec
4506 ,p_cashflow_level_tbl => l_cashflow_level_tbl
4507 ,x_return_status => l_return_status
4508 ,x_msg_count => x_msg_count
4509 ,x_msg_data => x_msg_data);
4510
4511 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4512 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4513 ELSIF l_return_status = G_RET_STS_ERROR THEN
4514 RAISE OKL_API.G_EXCEPTION_ERROR;
4515 END IF;
4516 END IF;
4517 END IF;
4518
4519 /*SELECT parent_object_id,parent_object_code INTO l_p_id,l_p_code
4520 FROM okl_lease_quotes_b where ID = l_asset_rec.parent_object_id;
4521
4522 OKL_LEASE_QUOTE_PRICING_PVT.handle_parent_object_status(
4523 p_api_version => G_API_VERSION
4524 ,p_init_msg_list => G_FALSE
4525 ,x_return_status => x_return_status
4526 ,x_msg_count => x_msg_count
4527 ,x_msg_data => x_msg_data
4528 ,p_parent_object_code => l_p_code
4529 ,p_parent_object_id => l_p_id
4530 );
4531
4532 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
4533 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4534 ELSIF x_return_status = G_RET_STS_ERROR THEN
4535 RAISE OKL_API.G_EXCEPTION_ERROR;
4536 END IF;*/
4537
4538
4539 x_return_status := G_RET_STS_SUCCESS;
4540
4541 EXCEPTION
4542
4543 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4544
4545 IF p_transaction_control = G_TRUE THEN
4546 ROLLBACK TO l_program_name;
4547 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4548 END IF;
4549
4550 x_return_status := G_RET_STS_ERROR;
4551
4552 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4553
4554 IF p_transaction_control = G_TRUE THEN
4555 ROLLBACK TO l_program_name;
4556 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4557 END IF;
4558
4559 x_return_status := G_RET_STS_UNEXP_ERROR;
4560
4561 WHEN OTHERS THEN
4562
4563 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
4564 p_msg_name => G_DB_ERROR,
4565 p_token1 => G_PROG_NAME_TOKEN,
4566 p_token1_value => l_api_name,
4567 p_token2 => G_SQLCODE_TOKEN,
4568 p_token2_value => sqlcode,
4569 p_token3 => G_SQLERRM_TOKEN,
4570 p_token3_value => sqlerrm);
4571
4572 IF p_transaction_control = G_TRUE THEN
4573 ROLLBACK TO l_program_name;
4574 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4575 END IF;
4576
4577 x_return_status := G_RET_STS_UNEXP_ERROR;
4578
4579 END update_asset;
4580
4581 -------------------------------------------
4582 -- PROCEDURE create_assets_with_adjustments
4583 -------------------------------------------
4584 PROCEDURE create_assets_with_adjustments (p_api_version IN NUMBER,
4585 p_init_msg_list IN VARCHAR2,
4586 p_transaction_control IN VARCHAR2,
4587 p_asset_tbl IN asset_tbl_type,
4588 p_component_tbl IN asset_component_tbl_type,
4589 p_asset_adj_tbl IN asset_adjustment_tbl_type,
4590 x_return_status OUT NOCOPY VARCHAR2,
4591 x_msg_count OUT NOCOPY NUMBER,
4592 x_msg_data OUT NOCOPY VARCHAR2) IS
4593
4594 l_program_name CONSTANT VARCHAR2(30) := 'create_assets_adj';
4595 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
4596
4597 l_asset_tbl asset_tbl_type;
4598 lx_asset_tbl asset_tbl_type;
4599
4600 l_component_tbl component_tbl_type;
4601 lx_component_tbl component_tbl_type;
4602
4603 l_asset_comp_tbl asset_component_tbl_type;
4604
4605 l_asset_adj_tbl asset_adjustment_tbl_type;
4606 l_adj_assets_tbl asset_adj_tbl_type;
4607 lx_adj_assets_tbl asset_adj_tbl_type;
4608 l_supplier_id NUMBER := NULL;
4609
4610 l_return_status VARCHAR2(1);
4611 ln_index NUMBER;
4612
4613 CURSOR c_get_supplier_id(p_qte_id IN OKL_LEASE_QUOTES_B.ID%TYPE) IS
4614 SELECT LOP.SUPPLIER_ID supplier_id
4615 FROM OKL_LEASE_OPPORTUNITIES_B LOP,
4616 OKL_LEASE_QUOTES_B QTE
4617 WHERE LOP.ID = QTE.parent_object_id
4618 AND qte.id = p_qte_id;
4619
4620 BEGIN
4621
4622 IF p_transaction_control = G_TRUE THEN
4623 SAVEPOINT l_program_name;
4624 END IF;
4625
4626 IF p_init_msg_list = G_TRUE THEN
4627 FND_MSG_PUB.initialize;
4628 END IF;
4629
4630 l_asset_tbl := p_asset_tbl;
4631 l_asset_comp_tbl := p_component_tbl;
4632 l_asset_adj_tbl := p_asset_adj_tbl;
4633
4634 okl_ass_pvt.insert_row (p_api_version => G_API_VERSION
4635 ,p_init_msg_list => G_FALSE
4636 ,x_return_status => l_return_status
4637 ,x_msg_count => x_msg_count
4638 ,x_msg_data => x_msg_data
4639 ,p_assv_tbl => l_asset_tbl
4640 ,x_assv_tbl => lx_asset_tbl );
4641
4642 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4643 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4644 ELSIF l_return_status = G_RET_STS_ERROR THEN
4645 RAISE OKL_API.G_EXCEPTION_ERROR;
4646 END IF;
4647 IF l_asset_comp_tbl.EXISTS(l_asset_comp_tbl.FIRST) THEN
4648 FOR l_sup_rec IN c_get_supplier_id(l_asset_tbl(l_asset_tbl.FIRST).parent_object_id) LOOP
4649 l_supplier_id := l_sup_rec.supplier_id;
4650 END LOOP;
4651 END IF;
4652 -- Update the Asset Components table with the created asset_id
4653 FOR i IN l_asset_comp_tbl.FIRST .. l_asset_comp_tbl.LAST LOOP
4654 IF l_asset_comp_tbl.EXISTS(i) THEN
4655 l_component_tbl(i).asset_id := lx_asset_tbl(i).id;
4656 l_component_tbl(i).inv_item_id := l_asset_comp_tbl(i).inv_item_id;
4657 l_component_tbl(i).supplier_id := l_asset_comp_tbl(i).supplier_id;
4658 l_component_tbl(i).primary_component := l_asset_comp_tbl(i).primary_component;
4659 l_component_tbl(i).unit_cost := l_asset_comp_tbl(i).unit_cost;
4660 l_component_tbl(i).number_of_units := l_asset_comp_tbl(i).number_of_units;
4661 l_component_tbl(i).manufacturer_name := l_asset_comp_tbl(i).manufacturer_name;
4662 l_component_tbl(i).year_manufactured := l_asset_comp_tbl(i).year_manufactured;
4663 l_component_tbl(i).model_number := l_asset_comp_tbl(i).model_number;
4664 l_component_tbl(i).short_description := l_asset_comp_tbl(i).short_description;
4665 l_component_tbl(i).description := l_asset_comp_tbl(i).description;
4666 l_component_tbl(i).comments := l_asset_comp_tbl(i).comments;
4667 l_component_tbl(i).supplier_id := l_supplier_id;
4668 END IF;
4669 END LOOP;
4670
4671 -- Component table must contain at least 1 row for Asset
4672 okl_aso_pvt.insert_row (p_api_version => G_API_VERSION
4673 ,p_init_msg_list => G_FALSE
4674 ,x_return_status => l_return_status
4675 ,x_msg_count => x_msg_count
4676 ,x_msg_data => x_msg_data
4677 ,p_asov_tbl => l_component_tbl
4678 ,x_asov_tbl => lx_component_tbl );
4679
4680 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4681 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4682 ELSIF l_return_status = G_RET_STS_ERROR THEN
4683 RAISE OKL_API.G_EXCEPTION_ERROR;
4684 END IF;
4685 IF l_asset_adj_tbl.COUNT > 0 THEN
4686 FOR i IN l_asset_adj_tbl.FIRST .. l_asset_adj_tbl.LAST LOOP
4687 IF l_asset_adj_tbl.EXISTS(i) THEN
4688 --asawanka bug 5025239 fix starts
4689 IF (l_asset_adj_tbl(i).value IS NOT NULL AND l_asset_adj_tbl(i).value < 0) THEN
4690 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
4691 p_msg_name => 'OKL_ADJ_AMOUNT_NEGATIVE');
4692 RAISE OKL_API.G_EXCEPTION_ERROR;
4693 END IF;
4694 --asawanka bug 5025239 fix ends
4695 l_adj_assets_tbl(i).parent_object_code := l_asset_adj_tbl(i).parent_object_code;
4696 l_adj_assets_tbl(i).parent_object_id := l_asset_adj_tbl(i).parent_object_id;
4697 l_adj_assets_tbl(i).adjustment_source_type := l_asset_adj_tbl(i).adjustment_source_type;
4698 l_adj_assets_tbl(i).adjustment_source_id := l_asset_adj_tbl(i).adjustment_source_id;
4699 l_adj_assets_tbl(i).basis := l_asset_adj_tbl(i).basis;
4700 l_adj_assets_tbl(i).value := l_asset_adj_tbl(i).value;
4701 l_adj_assets_tbl(i).default_subsidy_amount := l_asset_adj_tbl(i).default_subsidy_amount;
4702 l_adj_assets_tbl(i).processing_type := l_asset_adj_tbl(i).processing_type;
4703 l_adj_assets_tbl(i).supplier_id := l_asset_adj_tbl(i).supplier_id;
4704 --bug # 5142940 ssdeshpa start
4705 l_adj_assets_tbl(i).stream_type_id := l_asset_adj_tbl(i).stream_type_id;
4706 --bug # 5142940 ssdeshpa end
4707 l_adj_assets_tbl(i).short_description := l_asset_adj_tbl(i).short_description;
4708 l_adj_assets_tbl(i).description := l_asset_adj_tbl(i).description;
4709 l_adj_assets_tbl(i).comments := l_asset_adj_tbl(i).comments;
4710 l_adj_assets_tbl(i).percent_basis_value := l_asset_adj_tbl(i).percent_basis_value;
4711 END IF;
4712 END LOOP;
4713
4714
4715 -- Validate Subsidy Usage
4716 ln_index := l_adj_assets_tbl.FIRST;
4717
4718 IF (l_adj_assets_tbl(ln_index).adjustment_source_type = 'SUBSIDY') THEN
4719 validate_subsidy_usage(p_asset_id => l_adj_assets_tbl(ln_index).parent_object_id,
4720 p_input_adj_tbl => l_adj_assets_tbl,
4721 x_return_status => x_return_status);
4722 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
4723 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4724 ELSIF x_return_status = G_RET_STS_ERROR THEN
4725 RAISE OKL_API.G_EXCEPTION_ERROR;
4726 END IF;
4727 END IF;
4728
4729 okl_cdj_pvt.insert_row (p_api_version => G_API_VERSION
4730 ,p_init_msg_list => G_FALSE
4731 ,x_return_status => x_return_status
4732 ,x_msg_count => x_msg_count
4733 ,x_msg_data => x_msg_data
4734 ,p_cdjv_tbl => l_adj_assets_tbl
4735 ,x_cdjv_tbl => lx_adj_assets_tbl );
4736
4737 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
4738 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4739 ELSIF x_return_status = G_RET_STS_ERROR THEN
4740 RAISE OKL_API.G_EXCEPTION_ERROR;
4741 END IF;
4742 END IF;
4743 --Bug # 5142940 ssdeshpa start
4744 --Insert Cash flows for Adjustment for Type 'DOWN_APYMENT'
4745 l_adj_assets_tbl := lx_adj_assets_tbl;
4746 IF (l_adj_assets_tbl.COUNT > 0) THEN
4747 FOR i IN l_adj_assets_tbl.FIRST .. l_adj_assets_tbl.LAST LOOP
4748 IF l_adj_assets_tbl.EXISTS(i) THEN
4749 IF(l_adj_assets_tbl(i).adjustment_source_type='DOWN_PAYMENT' AND
4750 l_adj_assets_tbl(i).processing_type='BILL' AND
4751 l_adj_assets_tbl(i).stream_type_id IS NOT NULL) THEN
4752 --Create Rec Structure for Cash flows
4753 process_adj_cashflows(p_cdjv_rec => l_adj_assets_tbl(i)
4754 ,p_event_mode => 'create'
4755 ,x_msg_count => x_msg_count
4756 ,x_msg_data => x_msg_data
4757 ,x_return_status => x_return_status);
4758
4759 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
4760 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4761 ELSIF x_return_status = G_RET_STS_ERROR THEN
4762 RAISE OKL_API.G_EXCEPTION_ERROR;
4763 END IF;
4764 END IF;
4765 END IF;
4766 END LOOP;
4767 END IF;
4768 --Bug # 5142940 ssdeshpa end
4769
4770 x_return_status := G_RET_STS_SUCCESS;
4771 EXCEPTION
4772
4773 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4774
4775 IF p_transaction_control = G_TRUE THEN
4776 ROLLBACK TO l_program_name;
4777 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4778 END IF;
4779
4780 x_return_status := G_RET_STS_ERROR;
4781
4782 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4783
4784 IF p_transaction_control = G_TRUE THEN
4785 ROLLBACK TO l_program_name;
4786 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4787 END IF;
4788
4789 x_return_status := G_RET_STS_UNEXP_ERROR;
4790
4791 WHEN OTHERS THEN
4792
4793 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
4794 p_msg_name => G_DB_ERROR,
4795 p_token1 => G_PROG_NAME_TOKEN,
4796 p_token1_value => l_api_name,
4797 p_token2 => G_SQLCODE_TOKEN,
4798 p_token2_value => sqlcode,
4799 p_token3 => G_SQLERRM_TOKEN,
4800 p_token3_value => sqlerrm);
4801
4802 IF p_transaction_control = G_TRUE THEN
4803 ROLLBACK TO l_program_name;
4804 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4805 END IF;
4806
4807 END create_assets_with_adjustments;
4808
4809 --veramach bug 6622178 start
4810 ----------------------------------------------------------------------------------------------------
4811 -- Start of Comments
4812 -- Created By: veramach
4813 -- Procedure Name: calculate_subsidy_amount
4814 -- Description: returns the subsidy amount for given asset on a lease sales quote when subsidy calculation
4815 -- basis is Financed Amount
4816 -- Dependencies:
4817 -- Parameters: p_asset_id - the asset id for which to calculate subsidy amount
4818 -- Parameters: p_subsidy_id - the subsidy id
4819 -- Version: 1.0
4820 -- End of Commnets
4821 ----------------------------------------------------------------------------------------------------
4822 PROCEDURE calculate_subsidy_amount(
4823 p_api_version IN NUMBER,
4824 p_init_msg_list IN VARCHAR2,
4825 x_return_status OUT NOCOPY VARCHAR2,
4826 x_msg_count OUT NOCOPY NUMBER,
4827 x_msg_data OUT NOCOPY VARCHAR2,
4828 p_asset_id IN NUMBER,
4829 p_subsidy_id IN NUMBER,
4830 x_subsidy_amount OUT NOCOPY NUMBER) is
4831
4832 CURSOR sub_dtls_csr IS
4833 Select PERCENT, MAXIMUM_FINANCED_AMOUNT, MAXIMUM_SUBSIDY_AMOUNT
4834 FROM okl_subsidies_b
4835 WHERE id = p_subsidy_id;
4836
4837 sub_dtls_rec sub_dtls_csr%ROWTYPE;
4838 l_prog_name VARCHAR2(61) := 'OKL_LEASE_QUOTE_ASSET_PVT.calculate_subsidy_amount';
4839 l_additional_parameters OKL_EXECUTE_FORMULA_PUB.ctxt_val_tbl_type;
4840 l_financed_amount NUMBER;
4841 l_subsidy_amount NUMBER;
4842
4843 BEGIN
4844
4845 IF p_asset_id IS NULL THEN
4846 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4847 END IF;
4848
4849 l_additional_parameters(1).name := 'ASSET_ID';
4850 l_additional_parameters(1).value := p_asset_id;
4851 OKL_EXECUTE_FORMULA_PUB.execute(p_api_version => p_api_version,
4852 p_init_msg_list => p_init_msg_list,
4853 x_return_status => x_return_status,
4854 x_msg_count => x_msg_count,
4855 x_msg_data => x_msg_data,
4856 p_formula_name => 'FRONT_END_FINANCED_AMOUNT',
4857 p_contract_id => null,
4858 p_line_id => null,
4859 p_additional_parameters => l_additional_parameters,
4860 x_value => l_financed_amount);
4861
4862 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4863 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4864 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4865 RAISE OKL_API.G_EXCEPTION_ERROR;
4866 END IF;
4867
4868 OPEN sub_dtls_csr;
4869 FETCH sub_dtls_csr into sub_dtls_rec;
4870 CLOSE sub_dtls_csr;
4871
4872 IF (sub_dtls_rec.MAXIMUM_FINANCED_AMOUNT IS NOT NULL) THEN
4873 IF (l_financed_amount > sub_dtls_rec.MAXIMUM_FINANCED_AMOUNT) THEN
4874 l_financed_amount := sub_dtls_rec.MAXIMUM_FINANCED_AMOUNT;
4875 END IF;
4876 END IF;
4877
4878 l_subsidy_amount := l_financed_amount * (sub_dtls_rec.PERCENT/100);
4879
4880 IF (sub_dtls_rec.MAXIMUM_SUBSIDY_AMOUNT IS NOT NULL) THEN
4881 IF (l_subsidy_amount > sub_dtls_rec.MAXIMUM_SUBSIDY_AMOUNT) THEN
4882 l_subsidy_amount := sub_dtls_rec.MAXIMUM_SUBSIDY_AMOUNT;
4883 END IF;
4884 END IF;
4885
4886 x_subsidy_amount := l_subsidy_amount;
4887
4888 EXCEPTION
4889
4890 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4891 RAISE OKL_API.G_EXCEPTION_ERROR;
4892 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4893 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4894 WHEN OTHERS THEN
4895 OKL_API.SET_MESSAGE (p_app_name => 'OKL',
4896 p_msg_name => 'OKL_DB_ERROR',
4897 p_token1 => 'PROG_NAME',
4898 p_token1_value => l_prog_name,
4899 p_token2 => 'SQLCODE',
4900 p_token2_value => sqlcode,
4901 p_token3 => 'SQLERRM',
4902 p_token3_value => sqlerrm);
4903
4904 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4905 END calculate_subsidy_amount;
4906 --veramach bug 6622178 end
4907
4908 END OKL_LEASE_QUOTE_ASSET_PVT;