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