[Home] [Help]
PACKAGE BODY: APPS.OKL_LA_ASSET_PVT
Source
1 Package body OKL_LA_ASSET_PVT as
2 /* $Header: OKLRLAAB.pls 120.19.12020000.3 2013/03/08 12:52:23 racheruv ship $ */
3 -------------------------------------------------------------------------------------------------
4 -- GLOBAL MESSAGE CONSTANTS
5 -------------------------------------------------------------------------------------------------
6 G_NO_MATCHING_RECORD CONSTANT VARCHAR2(200) := 'OKL_LLA_NO_MATCHING_RECORD';
7 G_INVALID_CRITERIA CONSTANT VARCHAR2(200) := 'OKL_LLA_INVALID_CRITERIA';
8 G_COPY_HEADER CONSTANT VARCHAR2(200) := 'OKL_LLA_COPY_HEADER';
9 G_COPY_LINE CONSTANT VARCHAR2(200) := 'OKL_LLA_COPY_LINE';
10 G_FND_APP CONSTANT VARCHAR2(200) := OKL_API.G_FND_APP;
11 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := 'OKL_REQUIRED_VALUE';
12 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_INVALID_VALUE;
13 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
14 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
15 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
16 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
17 -------------------------------------------------------------------------------------------------
18 -- GLOBAL EXCEPTION
19 -------------------------------------------------------------------------------------------------
20 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
21 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
22 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
23 G_API_VERSION CONSTANT NUMBER := 1.0;
24 G_SCOPE CONSTANT VARCHAR2(4) := '_PVT';
25 -------------------------------------------------------------------------------------------------
26 -- GLOBAL VARIABLES
27 -------------------------------------------------------------------------------------------------
28 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_LA_ASSET_PVT';
29 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
30 G_FIN_LINE_LTY_CODE OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'FREE_FORM1';
31 G_FA_LINE_LTY_CODE OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'FIXED_ASSET';
32 G_INST_LINE_LTY_CODE OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'FREE_FORM2';
33 G_IB_LINE_LTY_CODE OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'INST_ITEM';
34 G_LEASE_SCS_CODE OKC_K_HEADERS_V.SCS_CODE%TYPE := 'LEASE';
35 G_LOAN_SCS_CODE OKC_K_HEADERS_V.SCS_CODE%TYPE := 'LOAN';
36 G_TLS_TYPE OKC_LINE_STYLES_V.LSE_TYPE%TYPE := 'TLS';
37 G_SLS_TYPE OKC_LINE_STYLES_V.LSE_TYPE%TYPE := 'SLS';
38 -------------------------------------------------------------------------------------------------
39 -- cklee
40 G_BULK_BATCH_SIZE CONSTANT NUMBER := 10000;
41 -------------------------------------------------------------------------------------------------
42 -- cklee
43 TYPE r_las_rec_type IS RECORD (asset_number FA_ADDITIONS_B.ASSET_NUMBER%TYPE,
44 year_manufactured NUMBER := OKL_API.G_MISS_NUM,
45 manufacturer_name FA_ADDITIONS_B.MANUFACTURER_NAME%TYPE,
46 description FA_ADDITIONS_TL.DESCRIPTION%TYPE,
47 current_units NUMBER := OKL_API.G_MISS_NUM,
48 oec NUMBER := OKL_API.G_MISS_NUM,
49 vendor_name PO_VENDORS.VENDOR_NAME%TYPE,
50 residual_value NUMBER := OKL_API.G_MISS_NUM,
51 start_date OKC_K_LINES_B.START_DATE%TYPE,
52 date_terminated OKC_K_LINES_B.DATE_TERMINATED%TYPE,
53 end_date OKC_K_LINES_B.END_DATE%TYPE,
54 sts_code OKC_K_LINES_B.STS_CODE%TYPE,
55 location_id VARCHAR(1995),
56 parent_line_id NUMBER := OKL_API.G_MISS_NUM,
57 dnz_chr_id NUMBER := OKL_API.G_MISS_NUM);
58
59 TYPE las_loc_rec_type IS RECORD (location_id VARCHAR(1995),
60 parent_line_id NUMBER := OKL_API.G_MISS_NUM,
61 dnz_chr_id NUMBER := OKL_API.G_MISS_NUM);
62
63 TYPE las_loc_tbl_type IS TABLE OF las_loc_rec_type
64 INDEX BY BINARY_INTEGER;
65
66 TYPE fin_line_tab_type IS TABLE OF NUMBER;
67 TYPE loc_id_tab_type IS TABLE OF VARCHAR(1995);
68
69 l_pre_asset_name okc_k_lines_tl.name%type;
70 l_cur_asset_name okc_k_lines_tl.name%type;
71 l_unique_asset_flag boolean := false;
72
73 -- cklee
74
75 ----------------------------------------------------------------------------------
76 -- Start of comments
77 --
78 -- Procedure Name : search_loc
79 -- Description : Search financial asset location using binary search
80 -- Business Rules : 1. p_fin_line_tbl must be ascending order
81 -- 2. p_fin_line_tbl must be continue without
82 -- null node
83 -- Parameters :
84 -- Version : 1.0
85 -- End of comments
86 ----------------------------------------------------------------------------------
87
88 FUNCTION search_loc(
89 fin_line_id in number,
90 p_fin_line_tbl in fin_line_tab_type,
91 tot_count in number) return number
92 is
93
94 low number;
95 high number;
96 mid number;
97
98 begin
99
100 -- stop search if total count = 0
101 if (tot_count = 0) then
102 return null;
103 end if;
104
105 low := p_fin_line_tbl.FIRST;
106 high := tot_count;
107 while (low <= high) loop
108 --START:| 08-Sept-2004 cklee Fixed bug#4705572 |
109 -- mid := (low+high) / 2;
110 mid := ROUND((low+high) / 2);
111 --END :| 08-Sept-2004 cklee Fixed bug#4705572 |
112 if (fin_line_id < p_fin_line_tbl(mid)) then
113 high := mid - 1;
114 elsif (fin_line_id > p_fin_line_tbl(mid)) then
115 low := mid + 1;
116 else
117 exit;
118 end if;
119 end loop;
120
121 return mid;
122
123 exception
124 when others then
125 return null;
126 end search_loc;
127
128 ----------------------------------------------------------------------------------
129 -- Start of comments
130 --
131 -- Procedure Name : copy_asset_rec
132 -- Description : copy asset record and remove duplicated asset number
133 -- copy location if it's an active contract
134 -- Business Rules :
135 -- Parameters :
136 -- Version : 1.0
137 -- End of comments
138 ----------------------------------------------------------------------------------
139
140 PROCEDURE copy_asset_rec(
141 p_api_version IN NUMBER
142 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
143 ,x_return_status OUT NOCOPY VARCHAR2
144 ,x_msg_count OUT NOCOPY NUMBER
145 ,x_msg_data OUT NOCOPY VARCHAR2
146 ,p_las_rec IN r_las_rec_type
147 ,p_idx IN NUMBER
148 ,p_active_contract IN BOOLEAN default false
149 ,p_fin_line_ids IN fin_line_tab_type default null
150 ,p_loc_ids IN loc_id_tab_type default null
151 ,p_fin_line_2_ids IN fin_line_tab_type default null
152 ,p_loc_2_ids IN loc_id_tab_type default null
153 ,x_las_rec OUT NOCOPY las_rec_type
154 )
155 is
156 l_api_name CONSTANT VARCHAR2(30) := 'copy_asset_rec';
157 l_api_version CONSTANT NUMBER := 1.0;
158 i NUMBER;
159 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
160 l_location_id VARCHAR2(1995) := NULL;
161 l_loc_idx NUMBER := NULL;
162
163 --START:| 01-Mar-2006 cklee Fixed bug#4728228 |
164 l_sub_tot NUMBER;
165
166 cursor l_sub_tot_csr(p_asset_cle_id in number) is
167 select nvl(sum(nvl(sub_kle.subsidy_override_amount,nvl(sub_kle.amount,0))),0)
168 from okl_subsidies_b subb,
169 okl_k_lines sub_kle,
170 okc_k_lines_b sub_cle,
171 okc_line_styles_b sub_lse
172 where subb.id = sub_kle.subsidy_id
173 and subb.accounting_method_code = 'NET'
174 and sub_kle.id = sub_cle.id
175 and sub_cle.cle_id = p_asset_cle_id
176 and sub_cle.lse_id = sub_lse.id
177 and sub_lse.lty_code = 'SUBSIDY';
178 --END:| 01-Mar-2006 cklee Fixed bug#4728228 |
179
180 begin
181 -- Set API savepoint
182 SAVEPOINT copy_asset_rec;
183
184 -- Check for call compatibility
185 IF (NOT FND_API.Compatible_API_Call (l_api_version,
186 p_api_version,
187 l_api_name,
188 G_PKG_NAME ))
189 THEN
190 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
191 END IF;
192
193 -- Initialize message list if requested
194 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
195 FND_MSG_PUB.initialize;
196 END IF;
197
198 -- Initialize API status to success
199 x_return_status := OKL_API.G_RET_STS_SUCCESS;
200
201
202 --*** Begin API body ****************************************************
203
204 -- cklee: remove duplicated rows for asset
205 -- initialization
206
207 if (p_idx = 0) then
208 l_cur_asset_name := p_las_rec.asset_number;
209 l_unique_asset_flag := true;
210 -- Compare if duplicated asset number found
211 else
212 l_pre_asset_name := l_cur_asset_name;
213 l_cur_asset_name := p_las_rec.asset_number;
214 if (l_pre_asset_name = l_cur_asset_name) then
215 l_unique_asset_flag := false;
216 else
217 l_unique_asset_flag := true;
218 end if;
219 end if;
220
221 if (l_unique_asset_flag = true) then
222
223 x_las_rec.asset_number := p_las_rec.asset_number;
224 x_las_rec.year_manufactured := p_las_rec.year_manufactured;
225 x_las_rec.manufacturer_name := p_las_rec.manufacturer_name;
226 x_las_rec.description := p_las_rec.description;
227 x_las_rec.current_units := p_las_rec.current_units;
228 x_las_rec.from_oec := p_las_rec.oec;
229 --START:| 01-Mar-2006 cklee Fixed bug#4728228 |
230 -- x_las_rec.to_oec := NVL((p_las_rec.oec -
231 -- OKL_SEEDED_FUNCTIONS_PVT.line_discount(p_las_rec.dnz_chr_id, p_las_rec.parent_line_id)),
232 -- p_las_rec.oec);
233 open l_sub_tot_csr(p_las_rec.parent_line_id);
234 fetch l_sub_tot_csr into l_sub_tot;
235 close l_sub_tot_csr;
236 x_las_rec.to_oec := NVL((p_las_rec.oec - l_sub_tot), p_las_rec.oec);
237
238 --END:| 01-Mar-2006 cklee Fixed bug#4728228 |
239 x_las_rec.vendor_name := p_las_rec.vendor_name;
240 x_las_rec.from_residual_value := p_las_rec.residual_value;
241 x_las_rec.from_start_date := p_las_rec.start_date;
242 x_las_rec.from_end_date := p_las_rec.end_date;
243 x_las_rec.from_date_terminated := p_las_rec.date_terminated;
244 x_las_rec.sts_code := p_las_rec.sts_code;
245 x_las_rec.location_id := p_las_rec.location_id;
246 x_las_rec.parent_line_id := p_las_rec.parent_line_id;
247 x_las_rec.dnz_chr_id := p_las_rec.dnz_chr_id;
248
249 end if;
250
251 -- get location from FA if it's an active contract
252 IF p_active_contract THEN
253
254 -- search the 1st location set
255 l_loc_idx := search_loc(fin_line_id => p_las_rec.parent_line_id,
256 p_fin_line_tbl => p_fin_line_ids,
257 tot_count => p_fin_line_ids.COUNT);
258 IF l_loc_idx IS NULL THEN
259 -- search the 2nd location set
260 l_loc_idx := search_loc(fin_line_id => p_las_rec.parent_line_id,
261 p_fin_line_tbl => p_fin_line_2_ids,
262 tot_count => p_fin_line_2_ids.COUNT);
263 IF l_loc_idx IS NOT NULL THEN
264 l_location_id := p_loc_2_ids(l_loc_idx);
265 END IF;
266 ELSE
267 l_location_id := p_loc_ids(l_loc_idx);
268 END IF;
269
270 ELSE -- non active contract
271
272 -- search the location set
273 l_loc_idx := search_loc(fin_line_id => p_las_rec.parent_line_id,
274 p_fin_line_tbl => p_fin_line_ids,
275 tot_count => p_fin_line_ids.COUNT);
276 IF l_loc_idx IS NOT NULL THEN
277 l_location_id := p_loc_ids(l_loc_idx);
278 END IF;
279
280 END IF;
281
282 x_las_rec.location_id := l_location_id;
283
284
285 --*** End API body ******************************************************
286
287 -- Get message count and if count is 1, get message info
288 FND_MSG_PUB.Count_And_Get
289 (p_count => x_msg_count,
290 p_data => x_msg_data);
291
292 EXCEPTION
293 WHEN OKL_API.G_EXCEPTION_ERROR THEN
294 ROLLBACK TO copy_asset_rec;
295 x_return_status := OKL_API.G_RET_STS_ERROR;
296 FND_MSG_PUB.Count_And_Get
297 (p_count => x_msg_count,
298 p_data => x_msg_data);
299
300 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
301 ROLLBACK TO copy_asset_rec;
302 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
303 FND_MSG_PUB.Count_And_Get
304 (p_count => x_msg_count,
305 p_data => x_msg_data);
306
307 WHEN OTHERS THEN
308 ROLLBACK TO copy_asset_rect;
309 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
310 OKL_API.Set_Message(p_app_name => G_APP_NAME,
311 p_msg_name => G_UNEXPECTED_ERROR,
312 p_token1 => G_SQLCODE_TOKEN,
313 p_token1_value => SQLCODE,
314 p_token2 => G_SQLERRM_TOKEN,
315 p_token2_value => SQLERRM);
316 FND_MSG_PUB.Count_And_Get
317 (p_count => x_msg_count,
318 p_data => x_msg_data);
319
320 end copy_asset_rec;
321
322 ----------------------------------------------------------------------------------
323 -- Start of comments
324 --
325 -- Procedure Name : validate_asset_rec
326 -- Description : validate asset search criteria
327 -- Business Rules :
328 -- Parameters :
329 -- Version : 1.0
330 -- End of comments
331 ----------------------------------------------------------------------------------
332
333 PROCEDURE validate_asset_rec(
334 p_api_version IN NUMBER
335 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
336 ,x_return_status OUT NOCOPY VARCHAR2
337 ,x_msg_count OUT NOCOPY NUMBER
338 ,x_msg_data OUT NOCOPY VARCHAR2
339 ,p_las_rec IN las_rec_type
340 ,x_las_rec OUT NOCOPY las_rec_type
341 )
342 is
343 l_api_name CONSTANT VARCHAR2(30) := 'validate_asset_rec';
344 l_api_version CONSTANT NUMBER := 1.0;
345 i NUMBER;
346 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
347 l_las_rec las_rec_type := p_las_rec;
348
349 begin
350 -- Set API savepoint
351 SAVEPOINT validate_asset_rec;
352
353 -- Check for call compatibility
354 IF (NOT FND_API.Compatible_API_Call (l_api_version,
355 p_api_version,
356 l_api_name,
357 G_PKG_NAME ))
358 THEN
359 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
360 END IF;
361
362 -- Initialize message list if requested
363 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
364 FND_MSG_PUB.initialize;
365 END IF;
366
367 -- Initialize API status to success
368 x_return_status := OKL_API.G_RET_STS_SUCCESS;
369
370
371 --*** Begin API body ****************************************************
372
373 -- initial copy record
374 x_las_rec := l_las_rec;
375
376 IF l_las_rec.dnz_chr_id IS NULL OR
377 l_las_rec.dnz_chr_id = OKL_API.G_MISS_NUM THEN
378 RAISE OKL_API.G_EXCEPTION_ERROR;
379 END IF;
380 IF (l_las_rec.year_manufactured = OKL_API.G_MISS_NUM OR
381 l_las_rec.year_manufactured IS NULL) THEN
382 x_las_rec.year_manufactured := null;
383 END IF;
384 IF (l_las_rec.current_units = OKL_API.G_MISS_NUM OR
385 l_las_rec.current_units IS NULL) THEN
386 x_las_rec.current_units := null;
387 END IF;
388 IF (l_las_rec.from_oec = OKL_API.G_MISS_NUM OR
389 l_las_rec.from_oec IS NULL) THEN
390 x_las_rec.from_oec := null;
391 END IF;
392 IF (l_las_rec.to_oec = OKL_API.G_MISS_NUM OR
393 l_las_rec.to_oec IS NULL) THEN
394 x_las_rec.to_oec := null;
395 END IF;
396 IF (l_las_rec.from_residual_value = OKL_API.G_MISS_NUM OR
397 l_las_rec.from_residual_value IS NULL) THEN
398 x_las_rec.from_residual_value := null;
399 END IF;
400 IF (l_las_rec.to_residual_value = OKL_API.G_MISS_NUM OR
401 l_las_rec.to_residual_value IS NULL) THEN
402 x_las_rec.to_residual_value := null;
403 END IF;
404 IF (l_las_rec.parent_line_id = OKL_API.G_MISS_NUM OR
405 l_las_rec.parent_line_id IS NULL) THEN
406 x_las_rec.parent_line_id := null;
407 END IF;
408 IF (l_las_rec.from_start_date = OKL_API.G_MISS_DATE OR
409 l_las_rec.from_start_date IS NULL) THEN
410 x_las_rec.from_start_date := null;
411 END IF;
412 IF (l_las_rec.to_start_date = OKL_API.G_MISS_DATE OR
413 l_las_rec.to_start_date IS NULL) THEN
414 x_las_rec.to_start_date := null;
415 END IF;
416 IF (l_las_rec.from_end_date = OKL_API.G_MISS_DATE OR
417 l_las_rec.from_end_date IS NULL) THEN
418 x_las_rec.from_end_date := null;
419 END IF;
420 IF (l_las_rec.to_end_date = OKL_API.G_MISS_DATE OR
421 l_las_rec.to_end_date IS NULL) THEN
422 x_las_rec.to_end_date := null;
423 END IF;
424 IF (l_las_rec.from_date_terminated = OKL_API.G_MISS_DATE OR
425 l_las_rec.from_date_terminated IS NULL) THEN
426 x_las_rec.from_date_terminated := null;
427 END IF;
428 IF (l_las_rec.to_date_terminated = OKL_API.G_MISS_DATE OR
429 l_las_rec.to_date_terminated IS NULL) THEN
430 x_las_rec.to_date_terminated := null;
431 END IF;
432 IF (l_las_rec.asset_number = OKL_API.G_MISS_CHAR OR
433 l_las_rec.asset_number IS NULL) THEN
434 x_las_rec.asset_number := null;
435 END IF;
436 IF (l_las_rec.manufacturer_name = OKL_API.G_MISS_CHAR OR
437 l_las_rec.manufacturer_name IS NULL) THEN
438 x_las_rec.manufacturer_name := null;
439 END IF;
440 IF (l_las_rec.description = OKL_API.G_MISS_CHAR OR
441 l_las_rec.description IS NULL) THEN
442 x_las_rec.description := null;
443 END IF;
444 IF (l_las_rec.sts_code = OKL_API.G_MISS_CHAR OR
445 l_las_rec.sts_code IS NULL) THEN
446 x_las_rec.sts_code := null;
447 END IF;
448 IF (l_las_rec.vendor_name = OKL_API.G_MISS_CHAR OR
449 l_las_rec.vendor_name IS NULL) THEN
450 x_las_rec.vendor_name := null;
451 END IF;
452 IF (l_las_rec.location_id = OKL_API.G_MISS_CHAR OR
453 l_las_rec.location_id IS NULL) THEN
454 x_las_rec.location_id := null;
455 END IF;
456
457 --*** End API body ******************************************************
458
459 -- Get message count and if count is 1, get message info
460 FND_MSG_PUB.Count_And_Get
461 (p_count => x_msg_count,
462 p_data => x_msg_data);
463
464 EXCEPTION
465 WHEN OKL_API.G_EXCEPTION_ERROR THEN
466 ROLLBACK TO validate_asset_rec;
467 x_return_status := OKL_API.G_RET_STS_ERROR;
468 FND_MSG_PUB.Count_And_Get
469 (p_count => x_msg_count,
470 p_data => x_msg_data);
471
472 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
473 ROLLBACK TO validate_asset_rec;
474 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
475 FND_MSG_PUB.Count_And_Get
476 (p_count => x_msg_count,
477 p_data => x_msg_data);
478
479 WHEN OTHERS THEN
480 ROLLBACK TO validate_asset_rec;
481 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
482 OKL_API.Set_Message(p_app_name => G_APP_NAME,
483 p_msg_name => G_UNEXPECTED_ERROR,
484 p_token1 => G_SQLCODE_TOKEN,
485 p_token1_value => SQLCODE,
486 p_token2 => G_SQLERRM_TOKEN,
487 p_token2_value => SQLERRM);
488 FND_MSG_PUB.Count_And_Get
489 (p_count => x_msg_count,
490 p_data => x_msg_data);
491
492 end validate_asset_rec;
493
494 -----------------------------------------------------------------------------------------------------------
495
496 FUNCTION isReleaseAssetContract(p_dnz_chr_id IN OKL_K_HEADERS_FULL_V.ID%TYPE DEFAULT OKL_API.G_MISS_NUM)
497 RETURN BOOLEAN IS
498 l_status_active BOOLEAN := FALSE;
499 l_return_value VARCHAR2(1) := '';
500 --cursor to check if contract has re-lease assets
501 CURSOR l_chk_rel_ast_csr (p_chr_id IN Number) IS
502 -- Check for Re-Lease Asset contract
503 -- Bug# 15992711 : The re-leased flag is no longer checked at contract header
504 -- Hence the cursor is modified to select 'X' only for a pure
505 -- released asset contract
506 /* SELECT 'x'
507 FROM okc_k_headers_b CHR
508 WHERE nvl(chr.orig_system_source_code,'XXXX') <> 'OKL_RELEASE'
509 AND chr.ID = p_chr_id
510 AND exists (SELECT '1'
511 FROM OKC_RULES_B rul
512 WHERE rul.dnz_chr_id = chr.id
513 AND rul.rule_information_category = 'LARLES'
514 AND nvl(rule_information1,'N') = 'Y');
515 */
516 SELECT 'x'
517 FROM okc_k_headers_b CHR
518 WHERE nvl(chr.orig_system_source_code,'XXXX') <> 'OKL_RELEASE'
519 AND chr.ID = p_chr_id
520 AND exists (SELECT '1'
521 FROM okc_k_lines_b CLEB
522 ,okl_k_lines KLE
523 WHERE CLEB.id = KLE.id
524 AND CLEB.dnz_chr_id = p_chr_id
525 AND CLEB.chr_id = p_chr_id
526 AND CLEB.LSE_ID = 33 --for asset line
527 AND NVL(KLE.re_lease_yn,'N')= 'Y')
528 AND NOT EXISTS (SELECT '1'
529 FROM okc_k_lines_b CLEB
530 ,okl_k_lines KLE
531 WHERE CLEB.id = KLE.id
532 AND CLEB.dnz_chr_id = p_chr_id
533 AND CLEB.chr_id = p_chr_id
534 AND CLEB.LSE_ID = 33 --for asset line
535 AND NVL(KLE.re_lease_yn,'N')= 'N');
536
537 -- Bug# 15992711 : End
538
539 BEGIN
540
541 -- Check the deal type of the contract
542 -- If it is not LOAN or LOAN_REVOLVING, check the cursors c_check_assets_in_fa
543 -- and c_check_assets_in_txl
544 OPEN l_chk_rel_ast_csr(p_dnz_chr_id);
545 FETCH l_chk_rel_ast_csr INTO l_return_value;
546 l_status_active := l_chk_rel_ast_csr%FOUND;
547 CLOSE l_chk_rel_ast_csr;
548
549 IF (l_status_active) THEN
550 return TRUE;
551 ELSE
552 return FALSE;
553 END IF;
554 END isReleaseAssetContract;
555
556 -------------------------------------------------------------------------------------------------
557
558 /*
559 This check is made for the following.
560 If the Contract has gone through processes (ex: Mass Rebook) after booking,
561 and failed. Say, the contract status remained as 'APPROVED', when it failed.
562 Now, the asset screens display information from TXL, as the contract status
563 is 'APPROVED'. As the above mentioned transaction failed, the line level
564 transaction invoked doesn't complete. Asset summary picks up the duplicate
565 assets. To avoid this, we are checking for object1_id1, object1_id2 values in
566 OKC_K_ITEMS. If they are populated, we treat is as a active contract else an
567 inactive one.
568
569 This function checks whether the asset information of the contract exists in
570 FA, IB and TXL. If the information exists in FA, IB, then it is treated as a
571 active contract and the information will be picked up from FA, else if it
572 exists in TXL, the information should be retrieved from there. If it exists at
573 both places, it is error.
574 */
575
576 FUNCTION isContractActive(p_dnz_chr_id IN OKL_K_HEADERS_FULL_V.ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
577 p_deal_type IN OKL_K_HEADERS_FULL_V.DEAL_TYPE%TYPE,
578 p_sts_code IN OKL_K_HEADERS_FULL_V.STS_CODE%TYPE)
579 RETURN BOOLEAN IS
580 l_active_status BOOLEAN := FALSE;
581 l_inactive_status BOOLEAN := FALSE;
582 l_release_asset_contract BOOLEAN := FALSE;
583 l_status_active BOOLEAN := FALSE;
584 l_return_value VARCHAR2(1) := '';
585 CURSOR c_check_assets_in_fa(p_dnz_chr_id OKL_K_HEADERS_FULL_V.ID%TYPE) IS
586 SELECT 'x'
587 /* FROM dual
588 WHERE exists
589 (SELECT (1) */
590 FROM OKC_K_LINES_B cle,
591 OKC_K_ITEMS itm,
592 OKC_LINE_STYLES_B lse,
593 OKC_STATUSES_B sts
594 WHERE cle.dnz_chr_id = p_dnz_chr_id
595 AND itm.dnz_chr_id = cle.dnz_chr_id
596 AND itm.cle_id = cle.id
597 AND lse.id = cle.lse_id
598 AND lse.lty_code = 'FIXED_ASSET'
599 AND itm.object1_id1 is not null
600 AND itm.object1_id2 is not null
601 AND sts.code = cle.sts_code;
602 --START:| 14-Mar-2006 cklee Fixed bug#4905107 |
603 -- AND sts.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED');--);
604 --END:| 14-Mar-2006 cklee Fixed bug#4905107 |
605
606 CURSOR c_check_info_in_ib(p_dnz_chr_id OKL_K_HEADERS_FULL_V.ID%TYPE) IS
607 SELECT 'x'
608 /* FROM dual
609 WHERE exists
610 (SELECT (1) */
611 FROM OKC_K_LINES_B cle,
612 OKC_K_ITEMS itm,
613 OKC_LINE_STYLES_B lse,
614 OKC_STATUSES_B sts
615 WHERE cle.dnz_chr_id = p_dnz_chr_id
616 AND itm.dnz_chr_id = cle.dnz_chr_id
617 AND itm.cle_id = cle.id
618 AND lse.id = cle.lse_id
619 AND lse.lty_code = 'INST_ITEM'
620 AND itm.object1_id1 is not null
621 AND itm.object1_id2 is not null
622 AND sts.code = cle.sts_code;
623 --START:| 14-Mar-2006 cklee Fixed bug#4905107 |
624 -- AND sts.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED');--);
625 --END:| 14-Mar-2006 cklee Fixed bug#4905107 |
626
627 CURSOR c_check_assets_in_txl(p_dnz_chr_id OKL_K_HEADERS_FULL_V.ID%TYPE) IS
628 SELECT 'x'
629 /* FROM dual
630 WHERE exists
631 (SELECT (1) */
632 FROM OKC_K_LINES_B cle,
633 OKC_K_ITEMS itm,
634 OKC_LINE_STYLES_B lse,
635 OKC_STATUSES_B sts
636 WHERE cle.dnz_chr_id = p_dnz_chr_id
637 AND itm.dnz_chr_id = cle.dnz_chr_id
638 AND itm.cle_id = cle.id
639 AND lse.id = cle.lse_id
640 AND lse.lty_code = 'FIXED_ASSET'
641 AND itm.object1_id1 is null
642 AND itm.object1_id2 is null
643 AND sts.code = cle.sts_code;
644 --START:| 14-Mar-2006 cklee Fixed bug#4905107 |
645 -- AND sts.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED');--);
646 --END:| 14-Mar-2006 cklee Fixed bug#4905107 |
647
648 BEGIN
649
650
651 l_release_asset_contract := isReleaseAssetContract(p_dnz_chr_id);
652
653 IF (p_sts_code IS NOT NULL AND (p_sts_code = 'BOOKED' OR
654 p_sts_code = 'TERMINATED' OR
655 p_sts_code = 'AMENDED' OR
656 p_sts_code = 'EXPIRED' OR
657 p_sts_code = 'ACTIVE')) THEN
658 l_status_active := TRUE;
659 END IF;
660
661 IF (l_release_asset_contract AND l_status_active) THEN
662 RETURN TRUE;
663 ELSIF (l_release_asset_contract AND NOT l_status_active) THEN
664 RETURN FALSE;
665 END IF;
666
667
668 -- Check the deal type of the contract
669 -- If it is not LOAN or LOAN_REVOLVING, check the cursors c_check_assets_in_fa
670 -- and c_check_assets_in_txl
671 IF (p_deal_type IS NOT NULL AND (p_deal_type <> 'LOAN' AND
672 p_deal_type <> 'LOAN-REVOLVING')) THEN
673
674 OPEN c_check_assets_in_fa(p_dnz_chr_id);
675 FETCH c_check_assets_in_fa INTO l_return_value;
676 l_active_status := c_check_assets_in_fa%FOUND;
677 CLOSE c_check_assets_in_fa;
678
679 OPEN c_check_assets_in_txl(p_dnz_chr_id);
680 FETCH c_check_assets_in_txl INTO l_return_value;
681 l_inactive_status := c_check_assets_in_txl%FOUND;
682 CLOSE c_check_assets_in_txl;
683
684 IF (l_active_status AND NOT l_inactive_status) THEN
685 return TRUE;
686 ELSIF (NOT l_active_status AND l_inactive_status) THEN
687 return FALSE;
688 -- Following case is handled at the Asset Summary screen UI itself,
689 -- thus allowing the user not to access the screen, by displaying
690 -- the respective message.
691 /* ELSIF (l_active_status AND l_inactive_status) THEN
692 return FALSE;
693 OKL_API.set_message(G_APP_NAME,
694 G_UNEXPECTED_ERROR,
695 G_SQLCODE_TOKEN,
696 SQLCODE,
697 G_SQLERRM_TOKEN,
698 SQLERRM);
699 RAISE G_EXCEPTION_STOP_VALIDATION;*/
700 END IF;
701 ELSE -- Contract is either 'LOAN' or 'LOAN-REVOLVING'
702 OPEN c_check_assets_in_fa(p_dnz_chr_id);
703 FETCH c_check_assets_in_fa INTO l_return_value;
704 l_active_status := c_check_assets_in_fa%FOUND;
705 CLOSE c_check_assets_in_fa;
706
707 IF (l_active_status) THEN
708 return TRUE; -- Info exists in FA
709 ELSE
710 IF (l_status_active) THEN
711 return TRUE; -- Deduced basing on contract status
712 ELSE
713 OPEN c_check_info_in_ib(p_dnz_chr_id);
714 FETCH c_check_info_in_ib INTO l_return_value;
715 l_active_status := c_check_info_in_ib%FOUND;
716 CLOSE c_check_info_in_ib;
717
718 IF (l_active_status) THEN
719 return TRUE; -- Info exists in IB
720 ELSE
721 return FALSE;
722 END IF;
723 END IF;
724 END IF;
725 END IF;
726 return FALSE;
727 END isContractActive;
728 -------------------------------------------------------------------------------------------------
729
730 -- Procedure generate_asset_summary(
731 -------------------------------------------------------------------------------------------------
732
733 Procedure generate_asset_summary(
734 p_api_version IN NUMBER,
735 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
736 x_return_status OUT NOCOPY VARCHAR2,
737 x_msg_count OUT NOCOPY NUMBER,
738 x_msg_data OUT NOCOPY VARCHAR2,
739 p_las_rec IN las_rec_type,
740 x_las_tbl OUT NOCOPY las_tbl_type)
741 AS
742 l_api_version CONSTANT NUMBER := 1;
743 l_api_name CONSTANT VARCHAR2(30) := 'VERSION_CONTRACT';
744 lv_sts_code OKC_K_HEADERS_V.STS_CODE%TYPE;
745 lv_deal_type OKL_K_HEADERS_FULL_V.DEAL_TYPE%TYPE;
746 i NUMBER := 0;
747 l_las_rec las_rec_type := p_las_rec;
748 l_active_contract BOOLEAN := FALSE;
749 l_info_exists BOOLEAN := FALSE;
750 l_return_value VARCHAR2(1) := '';
751
752 -- cklee
753 l_fin_line_ids fin_line_tab_type;
754 l_loc_ids loc_id_tab_type;
755 l_fin_line_2_ids fin_line_tab_type;
756 l_loc_2_ids loc_id_tab_type;
757 lx_las_rec las_rec_type;
758
759
760 CURSOR c_get_old_asset_loc(p_dnz_chr_id number)
761 IS
762 -- start abhaxen modiyied for SQL Performance
763
764 select cle_fin.id fin_line_id,
765 substr(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,hl.address3, hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,null,hl.country,null, null,null,null,null,null,null,'n','n',80,1,1),1,80) location_id
766 from hz_locations hl,
767 csi_item_instances csi,
768 okc_k_items cim_ib,
769 okc_line_styles_b lse_ib,
770 okc_k_lines_b cle_ib,
771 okc_line_styles_b lse_inst,
772 okc_k_lines_b cle_inst,
773 okc_line_styles_b lse_fin,
774 okc_k_lines_b cle_fin
775 where cle_fin.cle_id is null
776 and cle_fin.chr_id = cle_fin.dnz_chr_id
777 and lse_fin.id = cle_fin.lse_id
778 and lse_fin.lty_code = 'FREE_FORM1'
779 and cle_inst.cle_id = cle_fin.id
780 and cle_inst.dnz_chr_id = cle_fin.dnz_chr_id
781 and cle_inst.lse_id = lse_inst.id
782 and lse_inst.lty_code = 'FREE_FORM2'
783 and cle_ib.cle_id = cle_inst.id
784 and cle_ib.dnz_chr_id = cle_inst.dnz_chr_id
785 and cle_ib.lse_id = lse_ib.id
786 and lse_ib.lty_code = 'INST_ITEM'
787 and cim_ib.cle_id = cle_ib.id
788 and cim_ib.dnz_chr_id = cle_ib.dnz_chr_id
789 and cim_ib.object1_id1 = csi.instance_id
790 and cim_ib.object1_id2 = '#'
791 and cim_ib.jtot_object1_code = 'OKX_IB_ITEM'
792 -- and csi.location_type_code = 'HZ_LOCATIONS'
793 -- and csi.location_id = hl.location_id
794 and csi.install_location_type_code = 'HZ_LOCATIONS' -- cklee
795 and csi.install_location_id = hl.location_id -- cklee
796 and cle_fin.dnz_chr_id = p_dnz_chr_id
797 order by cle_fin.id asc;
798 -- end abhaxen modiyied for SQL Performance
799
800 CURSOR c_get_old_asset_party_loc(p_dnz_chr_id number)
801 IS
802 -- start abhaxen modiyied for SQL Performance
803
804 select cle_fin.id fin_line_id,
805 substr(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,hl.address3, hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,null,hl.country,null, null,null,null,null,null,null,'n','n',80,1,1),1,80) location_id
806 from hz_locations hl,
807 hz_party_sites hps,
808 --Bug# 3569441 :
809 --hz_party_site_uses hpsu,
810 csi_item_instances csi,
811 okc_k_items cim_ib,
812 okc_line_styles_b lse_ib,
813 okc_k_lines_b cle_ib,
814 okc_line_styles_b lse_inst,
815 okc_k_lines_b cle_inst,
816 okc_line_styles_b lse_fin,
817 okc_k_lines_b cle_fin
818 where cle_fin.cle_id is null
819 and cle_fin.chr_id = cle_fin.dnz_chr_id
820 and lse_fin.id = cle_fin.lse_id
821 and lse_fin.lty_code = 'FREE_FORM1'
822 and cle_inst.cle_id = cle_fin.id
823 and cle_inst.dnz_chr_id = cle_fin.dnz_chr_id
824 and cle_inst.lse_id = lse_inst.id
825 and lse_inst.lty_code = 'FREE_FORM2'
826 and cle_ib.cle_id = cle_inst.id
827 and cle_ib.dnz_chr_id = cle_inst.dnz_chr_id
828 and cle_ib.lse_id = lse_ib.id
829 and lse_ib.lty_code = 'INST_ITEM'
830 and cim_ib.cle_id = cle_ib.id
831 and cim_ib.dnz_chr_id = cle_ib.dnz_chr_id
832 and cim_ib.object1_id1 = csi.instance_id
833 and cim_ib.object1_id2 = '#'
834 and cim_ib.jtot_object1_code = 'OKX_IB_ITEM'
835 --Bug# 3569441 :
836 --and csi.install_location_id = hpsu.party_site_use_id
837 and csi.install_location_id = hps.party_site_id
838 and csi.install_location_type_code = 'HZ_PARTY_SITES'
839 --and hpsu.site_use_type = 'INSTALL_AT'
840 --and hpsu.party_site_id = hps.party_site_id
841 and hps.location_id = hl.location_id
842 and cle_fin.dnz_chr_id = p_dnz_chr_id
843 order by cle_fin.id asc;
844 -- end abhaxen modiyied for SQL Performance
845
846 -- cklee
847 --
848 CURSOR c_get_new_asset_loc(p_dnz_chr_id number)
849 IS
850 -- start abhaxen modiyied for SQL Performance
851 select cle_fin.id fin_line_id,
852 substr(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,hl.address3, hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,null,hl.country,null, null,null,null,null,null,null,'n','n',80,1,1),1,80) location_id
853 from hz_locations hl,
854 hz_party_sites hps,
855 hz_party_site_uses hpsu,
856 okl_txl_itm_insts iti,
857 okc_line_styles_b lse_ib,
858 okc_k_lines_b cle_ib,
859 okc_line_styles_b lse_inst,
860 okc_k_lines_b cle_inst,
861 okc_line_styles_b lse_fin,
862 okc_k_lines_b cle_fin
863 where cle_fin.cle_id is null
864 and cle_fin.chr_id = cle_fin.dnz_chr_id
865 and lse_fin.id = cle_fin.lse_id
866 and lse_fin.lty_code = 'FREE_FORM1'
867 and cle_inst.cle_id = cle_fin.id
868 and cle_inst.dnz_chr_id = cle_fin.dnz_chr_id
869 and cle_inst.lse_id = lse_inst.id
870 and lse_inst.lty_code = 'FREE_FORM2'
871 and cle_ib.cle_id = cle_inst.id
872 and cle_ib.dnz_chr_id = cle_inst.dnz_chr_id
873 and cle_ib.lse_id = lse_ib.id
874 and lse_ib.lty_code = 'INST_ITEM'
875 and iti.kle_id = cle_ib.id
876 and iti.object_id1_new = hpsu.party_site_use_id
877 and iti.object_id2_new = '#'
878 and hpsu.party_site_id = hps.party_site_id
879 and hps.location_id = hl.location_id
880 and cle_fin.dnz_chr_id = p_dnz_chr_id
881 order by cle_fin.id asc;
882 -- end abhaxen modiyied for SQL Performance
883 --
884
885
886 CURSOR c_get_new_asset_desc(p_las_rec IN las_rec_type)
887 IS
888 select nast.asset_number,
889 nast.year_manufactured,
890 nast.manufacturer_name,
891 nast.description,
892 nast.current_units,
893 nast.oec,
894 nast.vendor_name,
895 nast.residual_value,
896 nast.start_date,
897 nast.end_date,
898 nast.date_terminated,
899 nast.sts_code,
900 nast.location_id,
901 -- sts.meaning sts_code,
902 -- nalc.location_id location_id,
903 -- nast.fin_line_id parent_line_id,
904 nast.parent_line_id,
905 nast.dnz_chr_id
906 from okl_new_assets_uv nast
907 where nast.dnz_chr_id = p_las_rec.dnz_chr_id
908 and upper(nast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(nast.asset_number))
909 and nvl(upper(nast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(nast.vendor_name),'x'))
910 and nast.oec between nvl(p_las_rec.from_oec,nast.oec) and nvl(p_las_rec.to_oec,nast.oec)
911 and nvl(nast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(nast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(nast.residual_value,0))
912 and upper(nast.description) like nvl(upper(p_las_rec.description),upper(nast.description))
913 and nast.sts_code like nvl(p_las_rec.sts_code,nast.sts_code)
914 and nvl(nast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(nast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(nast.start_date,to_date('1111','yyyy')))
915 and nvl(nast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(nast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(nast.end_date,to_date('1111','yyyy')))
916 and nvl(nast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(nast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(nast.date_terminated,to_date('1111','yyyy')))
917 -- and nvl(upper(nalc.location_id),'x') like nvl(upper(p_las_rec.location_id),nvl(upper(nalc.location_id),'x'))
918 -- and nast.dnz_chr_id = p_las_rec.dnz_chr_id
919 -- and nast.sts_code <> 'ABANDONED'
920 -- and nast.sts_code = sts.code
921 -- and sts.LANGUAGE = userenv('LANG')
922 --bug# 4202325 : added following condition
923 and nast.ASSET_STATUS_CODE <> 'ABANDONED'
924
925 order by decode(p_las_rec.p_order_by
926 ,'AST',asset_number
927 ,'YRMF',year_manufactured
928 ,'MFNM',manufacturer_name
929 --Bug# 2747693
930 --,'DESC',description
931 ,'DESC',4
932 ,'QTY',current_units
933 ,'OEC',oec
934 ,'VEDN',vendor_name
935 ,'RESV',residual_value
936 ,'STDT',to_char(start_date,'dd-mon-yyyy')
937 ,'ETDT',to_char(end_date,'dd-mon-yyyy')
938 ,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
939 ,'STS',sts_code
940 ,'LOC',location_id
941 ,asset_number) desc;
942
943 CURSOR c_get_old_asset_desc(p_las_rec IN las_rec_type)
944 IS
945 select oast.asset_number,
946 oast.year_manufactured,
947 oast.manufacturer_name,
948 oast.description,
949 oast.current_units,
950 oast.oec,
951 oast.vendor_name,
952 oast.residual_value,
953 oast.start_date,
954 oast.end_date,
955 oast.date_terminated,
956 oast.sts_code,
957 oast.location_id,
958 -- sts.meaning sts_code,
959 -- oalc.location_id location_id,
960 -- oast.fin_line_id parent_line_id,
961 oast.parent_line_id,
962 oast.dnz_chr_id
963 from okl_old_assets_uv oast
964 where oast.dnz_chr_id = p_las_rec.dnz_chr_id
965 and upper(oast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(oast.asset_number))
966 and nvl(upper(oast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(oast.vendor_name),'x'))
967 and oast.oec between nvl(p_las_rec.from_oec,oast.oec) and nvl(p_las_rec.to_oec,oast.oec)
968 and nvl(oast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(oast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(oast.residual_value,0))
969 and upper(oast.description) like nvl(upper(p_las_rec.description),upper(oast.description))
970 and oast.sts_code like nvl(p_las_rec.sts_code,oast.sts_code)
971 and nvl(oast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(oast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(oast.start_date,to_date('1111','yyyy')))
972 and nvl(oast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(oast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(oast.end_date,to_date('1111','yyyy')))
973 and nvl(oast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy')))
974 -- and nvl(upper(oalc.location_id),'x') like nvl(upper(p_las_rec.location_id),nvl(upper(oalc.location_id),'x'))
975 -- and oast.dnz_chr_id = p_las_rec.dnz_chr_id
976 -- and oast.sts_code <> 'ABANDONED'
977 -- and oast.sts_code = sts.code
978 -- and sts.LANGUAGE = userenv('LANG')
979 and oast.ASSET_STATUS_CODE <> 'ABANDONED'
980 order by decode(p_las_rec.p_order_by
981 ,'AST',asset_number
982 ,'YRMF',year_manufactured
983 ,'MFNM',manufacturer_name
984 --Bug# 2747693
985 --,'DESC',description
986 ,'DESC',4
987 ,'QTY',current_units
988 ,'OEC',oec
989 ,'VEDN',vendor_name
990 ,'RESV',residual_value
991 ,'STDT',to_char(start_date,'dd-mon-yyyy')
992 ,'ETDT',to_char(end_date,'dd-mon-yyyy')
993 ,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
994 ,'STS',sts_code
995 ,'LOC',location_id
996 ,asset_number) desc;
997
998 CURSOR c_get_new_asset_asc(p_las_rec IN las_rec_type)
999 IS
1000 select nast.asset_number,
1001 nast.year_manufactured,
1002 nast.manufacturer_name,
1003 nast.description,
1004 nast.current_units,
1005 nast.oec,
1006 nast.vendor_name,
1007 nast.residual_value,
1008 nast.start_date,
1009 nast.end_date,
1010 nast.date_terminated,
1011 nast.sts_code,
1012 nast.location_id,
1013 -- sts.meaning sts_code,
1014 -- nalc.location_id location_id,
1015 -- nast.fin_line_id parent_line_id,
1016 nast.parent_line_id,
1017 nast.dnz_chr_id
1018 from okl_new_assets_uv nast
1019 where nast.dnz_chr_id = p_las_rec.dnz_chr_id
1020 and upper(nast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(nast.asset_number))
1021 and nvl(upper(nast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(nast.vendor_name),'x'))
1022 and nast.oec between nvl(p_las_rec.from_oec,nast.oec) and nvl(p_las_rec.to_oec,nast.oec)
1023 and nvl(nast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(nast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(nast.residual_value,0))
1024 and upper(nast.description) like nvl(upper(p_las_rec.description),upper(nast.description))
1025 and nast.sts_code like nvl(p_las_rec.sts_code,nast.sts_code)
1026 and nvl(nast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(nast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(nast.start_date,to_date('1111','yyyy')))
1027 and nvl(nast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(nast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(nast.end_date,to_date('1111','yyyy')))
1028 and nvl(nast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(nast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(nast.date_terminated,to_date('1111','yyyy')))
1029 -- and nvl(upper(nalc.location_id),'x') like nvl(upper(p_las_rec.location_id),nvl(upper(nalc.location_id),'x'))
1030 -- and nast.dnz_chr_id = p_las_rec.dnz_chr_id
1031 -- and nast.sts_code <> 'ABANDONED'
1032 -- and nast.sts_code = sts.code
1033 -- and sts.LANGUAGE = userenv('LANG')
1034 --bug# 4202325 : added following condition
1035 and nast.ASSET_STATUS_CODE <> 'ABANDONED'
1036 order by decode(p_las_rec.p_order_by
1037 ,'AST',asset_number
1038 ,'YRMF',year_manufactured
1039 ,'MFNM',manufacturer_name
1040 --Bug# 2747693
1041 --,'DESC',description
1042 ,'DESC',4
1043 ,'QTY',current_units
1044 ,'OEC',oec
1045 ,'VEDN',vendor_name
1046 ,'RESV',residual_value
1047 ,'STDT',to_char(start_date,'dd-mon-yyyy')
1048 ,'ETDT',to_char(end_date,'dd-mon-yyyy')
1049 ,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
1050 ,'STS',sts_code
1051 ,'LOC',location_id
1052 ,asset_number) asc;
1053
1054 CURSOR c_get_old_asset_asc(p_las_rec IN las_rec_type)
1055 IS
1056 select oast.asset_number,
1057 oast.year_manufactured,
1058 oast.manufacturer_name,
1059 oast.description,
1060 oast.current_units,
1061 oast.oec,
1062 oast.vendor_name,
1063 oast.residual_value,
1064 oast.start_date,
1065 oast.end_date,
1066 oast.date_terminated,
1067 oast.sts_code,
1068 oast.location_id,
1069 -- sts.meaning sts_code,
1070 -- oalc.location_id location_id,
1071 -- oast.fin_line_id parent_line_id,
1072 oast.parent_line_id,
1073 oast.dnz_chr_id
1074 from okl_old_assets_uv oast
1075 where oast.dnz_chr_id = p_las_rec.dnz_chr_id
1076 and upper(oast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(oast.asset_number))
1077 and nvl(upper(oast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(oast.vendor_name),'x'))
1078 and oast.oec between nvl(p_las_rec.from_oec,oast.oec) and nvl(p_las_rec.to_oec,oast.oec)
1079 and nvl(oast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(oast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(oast.residual_value,0))
1080 and upper(oast.description) like nvl(upper(p_las_rec.description),upper(oast.description))
1081 and oast.sts_code like nvl(p_las_rec.sts_code,oast.sts_code)
1082 and nvl(oast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(oast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(oast.start_date,to_date('1111','yyyy')))
1083 and nvl(oast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(oast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(oast.end_date,to_date('1111','yyyy')))
1084 and nvl(oast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy')))
1085 -- and nvl(upper(oalc.location_id),'x') like nvl(upper(p_las_rec.location_id),nvl(upper(oalc.location_id),'x'))
1086 -- and oast.dnz_chr_id = p_las_rec.dnz_chr_id
1087 -- and oast.sts_code <> 'ABANDONED'
1088 -- and oast.sts_code = sts.code
1089 -- and sts.LANGUAGE = userenv('LANG')
1090 and oast.ASSET_STATUS_CODE <> 'ABANDONED'
1091 order by decode(p_las_rec.p_order_by
1092 ,'AST',asset_number
1093 ,'YRMF',year_manufactured
1094 ,'MFNM',manufacturer_name
1095 --Bug# 2747693
1096 --,'DESC',description
1097 ,'DESC',4
1098 ,'QTY',current_units
1099 ,'OEC',oec
1100 ,'VEDN',vendor_name
1101 ,'RESV',residual_value
1102 ,'STDT',to_char(start_date,'dd-mon-yyyy')
1103 ,'ETDT',to_char(end_date,'dd-mon-yyyy')
1104 ,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
1105 ,'STS',sts_code
1106 ,'LOC',location_id
1107 ,asset_number) asc;
1108 CURSOR c_get_sts_code(p_chr_id OKC_K_HEADERS_V.ID%TYPE)
1109 IS
1110 SELECT st.ste_code,
1111 khr.deal_type
1112 -- FROM OKL_K_HEADERS_FULL_V chr,
1113 FROM okc_k_headers_b chr,
1114 okl_k_headers khr,
1115 okc_statuses_b st
1116 WHERE khr.id = chr.id
1117 and chr.id = p_chr_id
1118 and st.code = chr.sts_code;
1119
1120 CURSOR c_check_assets_in_fa(p_dnz_chr_id OKL_K_HEADERS_FULL_V.ID%TYPE) IS
1121 SELECT 'x'
1122 /*FROM dual
1123 WHERE exists
1124 (SELECT (1)*/
1125 FROM OKC_K_LINES_B cle,
1126 OKC_K_ITEMS itm,
1127 OKC_LINE_STYLES_B lse,
1128 OKC_STATUSES_B sts
1129 WHERE cle.dnz_chr_id = p_dnz_chr_id
1130 AND itm.dnz_chr_id = cle.dnz_chr_id
1131 AND itm.cle_id = cle.id
1132 AND lse.id = cle.lse_id
1133 AND lse.lty_code = 'FIXED_ASSET'
1134 AND itm.object1_id1 is not null
1135 AND itm.object1_id2 is not null
1136 AND sts.code = cle.sts_code
1137 AND sts.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED');--);
1138
1139 -- Start --> Cursors for Loan Contracts
1140 CURSOR c_get_old_loan_asset_desc(p_las_rec IN las_rec_type)
1141 IS
1142 select oast.asset_number,
1143 oast.year_manufactured,
1144 oast.manufacturer_name,
1145 oast.description,
1146 oast.current_units,
1147 oast.oec,
1148 oast.vendor_name,
1149 oast.residual_value,
1150 oast.start_date,
1151 oast.end_date,
1152 oast.date_terminated,
1153 oast.sts_code,
1154 oast.location_id,
1155 -- sts.meaning sts_code,
1156 -- oalc.location_id location_id,
1157 -- oast.fin_line_id parent_line_id,
1158 oast.parent_line_id,
1159 oast.dnz_chr_id
1160 from okl_old_loan_assets_uv oast
1161 where oast.dnz_chr_id = p_las_rec.dnz_chr_id
1162 and upper(oast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(oast.asset_number))
1163 and nvl(upper(oast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(oast.vendor_name),'x'))
1164 and oast.oec between nvl(p_las_rec.from_oec,oast.oec) and nvl(p_las_rec.to_oec,oast.oec)
1165 and nvl(oast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(oast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(oast.residual_value,0))
1166 and upper(oast.description) like nvl(upper(p_las_rec.description),upper(oast.description))
1167 and oast.sts_code like nvl(p_las_rec.sts_code,oast.sts_code)
1168 and nvl(oast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(oast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(oast.start_date,to_date('1111','yyyy')))
1169 and nvl(oast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(oast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(oast.end_date,to_date('1111','yyyy')))
1170 and nvl(oast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy')))
1171 -- and oast.dnz_chr_id = p_las_rec.dnz_chr_id
1172 -- and oast.sts_code <> 'ABANDONED'
1173 -- and oast.sts_code = sts.code
1174 -- and sts.LANGUAGE = userenv('LANG')
1175 and oast.ASSET_STATUS_CODE <> 'ABANDONED'
1176 order by decode(p_las_rec.p_order_by
1177 ,'AST',asset_number
1178 ,'YRMF',year_manufactured
1179 ,'MFNM',manufacturer_name
1180 ,'DESC',4
1181 ,'QTY',current_units
1182 ,'OEC',oec
1183 ,'VEDN',vendor_name
1184 ,'RESV',residual_value
1185 ,'STDT',to_char(start_date,'dd-mon-yyyy')
1186 ,'ETDT',to_char(end_date,'dd-mon-yyyy')
1187 ,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
1188 ,'STS',sts_code
1189 ,'LOC',location_id
1190 ,asset_number) desc;
1191
1192 CURSOR c_get_old_loan_asset_asc(p_las_rec IN las_rec_type)
1193 IS
1194 select oast.asset_number,
1195 oast.year_manufactured,
1196 oast.manufacturer_name,
1197 oast.description,
1198 oast.current_units,
1199 oast.oec,
1200 oast.vendor_name,
1201 oast.residual_value,
1202 oast.start_date,
1203 oast.end_date,
1204 oast.date_terminated,
1205 oast.sts_code,
1206 oast.location_id,
1207 -- sts.meaning sts_code,
1208 -- oalc.location_id location_id,
1209 -- oast.fin_line_id parent_line_id,
1210 oast.parent_line_id,
1211 oast.dnz_chr_id
1212 from okl_old_loan_assets_uv oast
1213 where oast.dnz_chr_id = p_las_rec.dnz_chr_id
1214 and upper(oast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(oast.asset_number))
1215 and nvl(upper(oast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(oast.vendor_name),'x'))
1216 and oast.oec between nvl(p_las_rec.from_oec,oast.oec) and nvl(p_las_rec.to_oec,oast.oec)
1217 and nvl(oast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(oast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(oast.residual_value,0))
1218 and upper(oast.description) like nvl(upper(p_las_rec.description),upper(oast.description))
1219 and oast.sts_code like nvl(p_las_rec.sts_code,oast.sts_code)
1220 and nvl(oast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(oast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(oast.start_date,to_date('1111','yyyy')))
1221 and nvl(oast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(oast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(oast.end_date,to_date('1111','yyyy')))
1222 and nvl(oast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy')))
1223 -- and oast.dnz_chr_id = p_las_rec.dnz_chr_id
1224 -- and oast.sts_code <> 'ABANDONED'
1225 -- and oast.sts_code = sts.code
1226 -- and sts.LANGUAGE = userenv('LANG')
1227 and oast.ASSET_STATUS_CODE <> 'ABANDONED'
1228 order by decode(p_las_rec.p_order_by
1229 ,'AST',asset_number
1230 ,'YRMF',year_manufactured
1231 ,'MFNM',manufacturer_name
1232 ,'DESC',4
1233 ,'QTY',current_units
1234 ,'OEC',oec
1235 ,'VEDN',vendor_name
1236 ,'RESV',residual_value
1237 ,'STDT',to_char(start_date,'dd-mon-yyyy')
1238 ,'ETDT',to_char(end_date,'dd-mon-yyyy')
1239 ,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
1240 ,'STS',sts_code
1241 ,'LOC',location_id
1242 ,asset_number) asc;
1243
1244 CURSOR c_get_new_loan_asset_desc(p_las_rec IN las_rec_type)
1245 IS
1246 select nast.asset_number,
1247 nast.year_manufactured,
1248 nast.manufacturer_name,
1249 nast.description,
1250 nast.current_units,
1251 nast.oec,
1252 nast.vendor_name,
1253 nast.residual_value,
1254 nast.start_date,
1255 nast.end_date,
1256 nast.date_terminated,
1257 nast.sts_code,
1258 nast.location_id,
1259 -- sts.meaning sts_code,
1260 -- nalc.location_id location_id,
1261 -- nast.fin_line_id parent_line_id,
1262 nast.parent_line_id,
1263 nast.dnz_chr_id
1264 from okl_new_loan_assets_uv nast
1265 where nast.dnz_chr_id = p_las_rec.dnz_chr_id
1266 and upper(nast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(nast.asset_number))
1267 and nvl(upper(nast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(nast.vendor_name),'x'))
1268 and nast.oec between nvl(p_las_rec.from_oec,nast.oec) and nvl(p_las_rec.to_oec,nast.oec)
1269 and nvl(nast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(nast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(nast.residual_value,0))
1270 and upper(nast.description) like nvl(upper(p_las_rec.description),upper(nast.description))
1271 and nast.sts_code like nvl(p_las_rec.sts_code,nast.sts_code)
1272 and nvl(nast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(nast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(nast.start_date,to_date('1111','yyyy')))
1273 and nvl(nast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(nast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(nast.end_date,to_date('1111','yyyy')))
1274 and nvl(nast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(nast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(nast.date_terminated,to_date('1111','yyyy')))
1275 -- and nast.dnz_chr_id = p_las_rec.dnz_chr_id
1276 -- and nast.sts_code <> 'ABANDONED'
1277 -- and nast.sts_code = sts.code
1278 -- and sts.LANGUAGE = userenv('LANG')
1279 --bug#4202325 Added following condition
1280 and nast.ASSET_STATUS_CODE <> 'ABANDONED'
1281 order by decode(p_las_rec.p_order_by
1282 ,'AST',asset_number
1283 ,'YRMF',year_manufactured
1284 ,'MFNM',manufacturer_name
1285 ,'DESC',4
1286 ,'QTY',current_units
1287 ,'OEC',oec
1288 ,'VEDN',vendor_name
1289 ,'RESV',residual_value
1290 ,'STDT',to_char(start_date,'dd-mon-yyyy')
1291 ,'ETDT',to_char(end_date,'dd-mon-yyyy')
1292 ,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
1293 ,'STS',sts_code
1294 ,'LOC',location_id
1295 ,asset_number) desc;
1296
1297 CURSOR c_get_new_loan_asset_asc(p_las_rec IN las_rec_type)
1298 IS
1299 select nast.asset_number,
1300 nast.year_manufactured,
1301 nast.manufacturer_name,
1302 nast.description,
1303 nast.current_units,
1304 nast.oec,
1305 nast.vendor_name,
1306 nast.residual_value,
1307 nast.start_date,
1308 nast.end_date,
1309 nast.date_terminated,
1310 nast.sts_code,
1311 nast.location_id,
1312 -- sts.meaning sts_code,
1313 -- nalc.location_id location_id,
1314 -- nast.fin_line_id parent_line_id,
1315 nast.parent_line_id,
1316 nast.dnz_chr_id
1317 from okl_new_loan_assets_uv nast
1318 where nast.dnz_chr_id = p_las_rec.dnz_chr_id
1319 and upper(nast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(nast.asset_number))
1320 and nvl(upper(nast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(nast.vendor_name),'x'))
1321 and nast.oec between nvl(p_las_rec.from_oec,nast.oec) and nvl(p_las_rec.to_oec,nast.oec)
1322 and nvl(nast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(nast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(nast.residual_value,0))
1323 and upper(nast.description) like nvl(upper(p_las_rec.description),upper(nast.description))
1324 and nast.sts_code like nvl(p_las_rec.sts_code,nast.sts_code)
1325 and nvl(nast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(nast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(nast.start_date,to_date('1111','yyyy')))
1326 and nvl(nast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(nast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(nast.end_date,to_date('1111','yyyy')))
1327 and nvl(nast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(nast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(nast.date_terminated,to_date('1111','yyyy')))
1328 -- and nast.dnz_chr_id = p_las_rec.dnz_chr_id
1329 -- and nast.sts_code <> 'ABANDONED'
1330 -- and nast.sts_code = sts.code
1331 -- and sts.LANGUAGE = userenv('LANG')
1332 --bug#4202325 Added following condition
1333 and nast.ASSET_STATUS_CODE <> 'ABANDONED'
1334 order by decode(p_las_rec.p_order_by
1335 ,'AST',asset_number
1336 ,'YRMF',year_manufactured
1337 ,'MFNM',manufacturer_name
1338 ,'DESC',4
1339 ,'QTY',current_units
1340 ,'OEC',oec
1341 ,'VEDN',vendor_name
1342 ,'RESV',residual_value
1343 ,'STDT',to_char(start_date,'dd-mon-yyyy')
1344 ,'ETDT',to_char(end_date,'dd-mon-yyyy')
1345 ,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
1346 ,'STS',sts_code
1347 ,'LOC',location_id
1348 ,asset_number) asc;
1349
1350 -- End --> Cursors for Loan Contracts
1351 --Bug# 4202325: Added cursor for split asset -start
1352
1353 CURSOR c_get_old_splt_asset_desc(p_las_rec IN las_rec_type)
1354 IS
1355 select oast.asset_number,
1356 oast.year_manufactured,
1357 oast.manufacturer_name,
1358 oast.description,
1359 oast.current_units,
1360 oast.oec,
1361 oast.vendor_name,
1362 oast.residual_value,
1363 oast.start_date,
1364 oast.end_date,
1365 oast.date_terminated,
1366 oast.sts_code,
1367 oast.location_id,
1368 oast.parent_line_id,
1369 oast.dnz_chr_id
1370 from okl_old_assets_uv oast
1371 where oast.dnz_chr_id = p_las_rec.dnz_chr_id
1372 and upper(oast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(oast.asset_number))
1373 and nvl(upper(oast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(oast.vendor_name),'x'))
1374 and oast.oec between nvl(p_las_rec.from_oec,oast.oec) and nvl(p_las_rec.to_oec,oast.oec)
1375 and nvl(oast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(oast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(oast.residual_value,0))
1376 and upper(oast.description) like nvl(upper(p_las_rec.description),upper(oast.description))
1377 and oast.sts_code like nvl(p_las_rec.sts_code,oast.sts_code)
1378 and nvl(oast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(oast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(oast.start_date,to_date('1111','yyyy')))
1379 and nvl(oast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(oast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(oast.end_date,to_date('1111','yyyy')))
1380 and nvl(oast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy')))
1381 and ( oast.ASSET_STATUS_CODE <> 'ABANDONED'
1382 OR (
1383 oast.ASSET_STATUS_CODE = 'ABANDONED'
1384 and exists (
1385 select 1
1386 FROM okl_txl_assets_b a, okl_trx_assets b, okl_txd_assets_b c,okl_trx_types_tl d
1387 where a.tas_id = b.id
1388 and b.tsu_code = 'PROCESSED'
1389 and c.tal_id = a.id
1390 and c.split_percent is not null
1391 and a.kle_id =(
1392 SELECT cle.id
1393 FROM OKC_K_LINES_B cle,
1394 OKC_LINE_STYLES_B lse
1395 WHERE cle.dnz_chr_id = oast.dnz_chr_id
1396 AND lse.id = cle.lse_id
1397 AND lse.lty_code = 'FIXED_ASSET'
1398 and cle_id= oast.parent_line_id
1399 )
1400 AND b.try_id = D.ID
1401 AND D.LANGUAGE = 'US'
1402 AND D.NAME = 'Split Asset'
1403 )
1404 )
1405 )
1406 order by decode(p_las_rec.p_order_by
1407 ,'AST',asset_number
1408 ,'YRMF',year_manufactured
1409 ,'MFNM',manufacturer_name
1410 --Bug# 2747693
1411 --,'DESC',description
1412 ,'DESC',4
1413 ,'QTY',current_units
1414 ,'OEC',oec
1415 ,'VEDN',vendor_name
1416 ,'RESV',residual_value
1417 ,'STDT',to_char(start_date,'dd-mon-yyyy')
1418 ,'ETDT',to_char(end_date,'dd-mon-yyyy')
1419 ,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
1420 ,'STS',sts_code
1421 ,'LOC',location_id
1422 ,asset_number) desc;
1423
1424 CURSOR c_get_old_splt_asset_asc(p_las_rec IN las_rec_type)
1425 IS
1426 select oast.asset_number,
1427 oast.year_manufactured,
1428 oast.manufacturer_name,
1429 oast.description,
1430 oast.current_units,
1431 oast.oec,
1432 oast.vendor_name,
1433 oast.residual_value,
1434 oast.start_date,
1435 oast.end_date,
1436 oast.date_terminated,
1437 oast.sts_code,
1438 oast.location_id,
1439 -- sts.meaning sts_code,
1440 -- oalc.location_id location_id,
1441 -- oast.fin_line_id parent_line_id,
1442 oast.parent_line_id,
1443 oast.dnz_chr_id
1444 from okl_old_assets_uv oast
1445 where oast.dnz_chr_id = p_las_rec.dnz_chr_id
1446 and upper(oast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(oast.asset_number))
1447 and nvl(upper(oast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(oast.vendor_name),'x'))
1448 and oast.oec between nvl(p_las_rec.from_oec,oast.oec) and nvl(p_las_rec.to_oec,oast.oec)
1449 and nvl(oast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(oast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(oast.residual_value,0))
1450 and upper(oast.description) like nvl(upper(p_las_rec.description),upper(oast.description))
1451 and oast.sts_code like nvl(p_las_rec.sts_code,oast.sts_code)
1452 and nvl(oast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(oast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(oast.start_date,to_date('1111','yyyy')))
1453 and nvl(oast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(oast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(oast.end_date,to_date('1111','yyyy')))
1454 and nvl(oast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy')))
1455 and ( oast.ASSET_STATUS_CODE <> 'ABANDONED'
1456 OR (
1457 oast.ASSET_STATUS_CODE = 'ABANDONED'
1458 and exists (
1459 select 1
1460 FROM okl_txl_assets_b a, okl_trx_assets b, okl_txd_assets_b c,okl_trx_types_tl d
1461 where a.tas_id = b.id
1462 and b.tsu_code = 'PROCESSED'
1463 and c.tal_id = a.id
1464 and c.split_percent is not null
1465 and a.kle_id =(
1466 SELECT cle.id
1467 FROM OKC_K_LINES_B cle,
1468 OKC_LINE_STYLES_B lse
1469 WHERE cle.dnz_chr_id = oast.dnz_chr_id
1470 AND lse.id = cle.lse_id
1471 AND lse.lty_code = 'FIXED_ASSET'
1472 and cle_id= oast.parent_line_id
1473 )
1474 AND b.try_id = D.ID
1475 AND D.LANGUAGE = 'US'
1476 AND D.NAME = 'Split Asset'
1477 )
1478 )
1479 )
1480 order by decode(p_las_rec.p_order_by
1481 ,'AST',asset_number
1482 ,'YRMF',year_manufactured
1483 ,'MFNM',manufacturer_name
1484 --Bug# 2747693
1485 --,'DESC',description
1486 ,'DESC',4
1487 ,'QTY',current_units
1488 ,'OEC',oec
1489 ,'VEDN',vendor_name
1490 ,'RESV',residual_value
1491 ,'STDT',to_char(start_date,'dd-mon-yyyy')
1492 ,'ETDT',to_char(end_date,'dd-mon-yyyy')
1493 ,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
1494 ,'STS',sts_code
1495 ,'LOC',location_id
1496 ,asset_number) asc;
1497
1498
1499
1500 CURSOR c_get_old_splt_loan_asset_desc(p_las_rec IN las_rec_type)
1501 IS
1502 select oast.asset_number,
1503 oast.year_manufactured,
1504 oast.manufacturer_name,
1505 oast.description,
1506 oast.current_units,
1507 oast.oec,
1508 oast.vendor_name,
1509 oast.residual_value,
1510 oast.start_date,
1511 oast.end_date,
1512 oast.date_terminated,
1513 oast.sts_code,
1514 oast.location_id,
1515 oast.parent_line_id,
1516 oast.dnz_chr_id
1517 from okl_old_loan_assets_uv oast
1518 where oast.dnz_chr_id = p_las_rec.dnz_chr_id
1519 and upper(oast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(oast.asset_number))
1520 and nvl(upper(oast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(oast.vendor_name),'x'))
1521 and oast.oec between nvl(p_las_rec.from_oec,oast.oec) and nvl(p_las_rec.to_oec,oast.oec)
1522 and nvl(oast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(oast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(oast.residual_value,0))
1523 and upper(oast.description) like nvl(upper(p_las_rec.description),upper(oast.description))
1524 and oast.sts_code like nvl(p_las_rec.sts_code,oast.sts_code)
1525 and nvl(oast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(oast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(oast.start_date,to_date('1111','yyyy')))
1526 and nvl(oast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(oast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(oast.end_date,to_date('1111','yyyy')))
1527 and nvl(oast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy')))
1528 and ( oast.ASSET_STATUS_CODE <> 'ABANDONED'
1529 OR (
1530 oast.ASSET_STATUS_CODE = 'ABANDONED'
1531 and exists (
1532 select 1
1533 FROM okl_txl_assets_b a, okl_trx_assets b, okl_txd_assets_b c,okl_trx_types_tl d
1534 where a.tas_id = b.id
1535 and b.tsu_code = 'PROCESSED'
1536 and c.tal_id = a.id
1537 and c.split_percent is not null
1538 and a.kle_id =(
1539 SELECT cle.id
1540 FROM OKC_K_LINES_B cle,
1541 OKC_LINE_STYLES_B lse
1542 WHERE cle.dnz_chr_id = oast.dnz_chr_id
1543 AND lse.id = cle.lse_id
1544 AND lse.lty_code = 'FIXED_ASSET'
1545 and cle_id= oast.parent_line_id
1546 )
1547 AND b.try_id = D.ID
1548 AND D.LANGUAGE = 'US'
1549 AND D.NAME = 'Split Asset'
1550 )
1551 )
1552 )
1553 order by decode(p_las_rec.p_order_by
1554 ,'AST',asset_number
1555 ,'YRMF',year_manufactured
1556 ,'MFNM',manufacturer_name
1557 ,'DESC',4
1558 ,'QTY',current_units
1559 ,'OEC',oec
1560 ,'VEDN',vendor_name
1561 ,'RESV',residual_value
1562 ,'STDT',to_char(start_date,'dd-mon-yyyy')
1563 ,'ETDT',to_char(end_date,'dd-mon-yyyy')
1564 ,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
1565 ,'STS',sts_code
1566 ,'LOC',location_id
1567 ,asset_number) desc;
1568
1569 CURSOR c_get_old_splt_loan_asset_asc(p_las_rec IN las_rec_type)
1570 IS
1571 select oast.asset_number,
1572 oast.year_manufactured,
1573 oast.manufacturer_name,
1574 oast.description,
1575 oast.current_units,
1576 oast.oec,
1577 oast.vendor_name,
1578 oast.residual_value,
1579 oast.start_date,
1580 oast.end_date,
1581 oast.date_terminated,
1582 oast.sts_code,
1583 oast.location_id,
1584 oast.parent_line_id,
1585 oast.dnz_chr_id
1586 from okl_old_loan_assets_uv oast
1587 where oast.dnz_chr_id = p_las_rec.dnz_chr_id
1588 and upper(oast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(oast.asset_number))
1589 and nvl(upper(oast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(oast.vendor_name),'x'))
1590 and oast.oec between nvl(p_las_rec.from_oec,oast.oec) and nvl(p_las_rec.to_oec,oast.oec)
1591 and nvl(oast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(oast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(oast.residual_value,0))
1592 and upper(oast.description) like nvl(upper(p_las_rec.description),upper(oast.description))
1593 and oast.sts_code like nvl(p_las_rec.sts_code,oast.sts_code)
1594 and nvl(oast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(oast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(oast.start_date,to_date('1111','yyyy')))
1595 and nvl(oast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(oast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(oast.end_date,to_date('1111','yyyy')))
1596 and nvl(oast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy')))
1597 and ( oast.ASSET_STATUS_CODE <> 'ABANDONED'
1598 OR (
1599 oast.ASSET_STATUS_CODE = 'ABANDONED'
1600 and exists (
1601 select 1
1602 FROM okl_txl_assets_b a, okl_trx_assets b, okl_txd_assets_b c,okl_trx_types_tl d
1603 where a.tas_id = b.id
1604 and b.tsu_code = 'PROCESSED'
1605 and c.tal_id = a.id
1606 and c.split_percent is not null
1607 and a.kle_id =(
1608 SELECT cle.id
1609 FROM OKC_K_LINES_B cle,
1610 OKC_LINE_STYLES_B lse
1611 WHERE cle.dnz_chr_id = oast.dnz_chr_id
1612 AND lse.id = cle.lse_id
1613 AND lse.lty_code = 'FIXED_ASSET'
1614 and cle_id= oast.parent_line_id
1615 )
1616 AND b.try_id = D.ID
1617 AND D.LANGUAGE = 'US'
1618 AND D.NAME = 'Split Asset'
1619 )
1620 )
1621 )
1622 order by decode(p_las_rec.p_order_by
1623 ,'AST',asset_number
1624 ,'YRMF',year_manufactured
1625 ,'MFNM',manufacturer_name
1626 ,'DESC',4
1627 ,'QTY',current_units
1628 ,'OEC',oec
1629 ,'VEDN',vendor_name
1630 ,'RESV',residual_value
1631 ,'STDT',to_char(start_date,'dd-mon-yyyy')
1632 ,'ETDT',to_char(end_date,'dd-mon-yyyy')
1633 ,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
1634 ,'STS',sts_code
1635 ,'LOC',location_id
1636 ,asset_number) asc;
1637
1638
1639 CURSOR c_get_new_splt_asset_asc(p_las_rec IN las_rec_type)
1640 IS
1641 SELECT nast.asset_number,
1642 nast.year_manufactured,
1643 nast.manufacturer_name,
1644 nast.description,
1645 nast.current_units,
1646 nast.oec,
1647 nast.vendor_name,
1648 nast.residual_value,
1649 nast.start_date,
1650 nast.end_date,
1651 nast.date_terminated,
1652 nast.sts_code,
1653 nast.location_id,
1654 nast.parent_line_id,
1655 nast.dnz_chr_id
1656 FROM okl_new_assets_uv nast
1657 WHERE nast.dnz_chr_id = p_las_rec.dnz_chr_id
1658 AND UPPER(nast.asset_number) LIKE NVL(UPPER(p_las_rec.asset_number),UPPER(nast.asset_number))
1659 AND NVL(UPPER(nast.vendor_name),'x') LIKE NVL(UPPER(p_las_rec.vendor_name),NVL(UPPER(nast.vendor_name),'x'))
1660 AND nast.oec BETWEEN NVL(p_las_rec.from_oec,nast.oec) AND NVL(p_las_rec.to_oec,nast.oec)
1661 AND NVL(nast.residual_value,0) BETWEEN NVL(p_las_rec.from_residual_value,NVL(nast.residual_value,0)) AND NVL(p_las_rec.to_residual_value,NVL(nast.residual_value,0))
1662 AND UPPER(nast.description) LIKE NVL(UPPER(p_las_rec.description),UPPER(nast.description))
1663 AND nast.sts_code LIKE NVL(p_las_rec.sts_code,nast.sts_code)
1664 AND NVL(nast.start_date,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_start_date,NVL(nast.start_date,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_start_date,NVL(nast.start_date,TO_DATE('1111','yyyy')))
1665 AND NVL(nast.end_date,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_end_date,NVL(nast.end_date,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_end_date,NVL(nast.end_date,TO_DATE('1111','yyyy')))
1666 AND NVL(nast.date_terminated,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_date_terminated,NVL(nast.date_terminated,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_date_terminated,NVL(nast.date_terminated,TO_DATE('1111','yyyy')))
1667 --bug# 4202325 : added following condition
1668 AND ( nast.ASSET_STATUS_CODE <> 'ABANDONED'
1669 OR (
1670 nast.ASSET_STATUS_CODE = 'ABANDONED'
1671 and exists (
1672 select 1
1673 FROM okl_txl_assets_b a, okl_trx_assets b, okl_txd_assets_b c,okl_trx_types_tl d
1674 where a.tas_id = b.id
1675 and b.tsu_code = 'PROCESSED'
1676 and c.tal_id = a.id
1677 and c.split_percent is not null
1678 and a.kle_id =(
1679 SELECT cle.id
1680 FROM OKC_K_LINES_B cle,
1681 OKC_LINE_STYLES_B lse
1682 WHERE cle.dnz_chr_id = nast.dnz_chr_id
1683 AND lse.id = cle.lse_id
1684 AND lse.lty_code = 'FIXED_ASSET'
1685 and cle_id= nast.parent_line_id
1686 )
1687 AND b.try_id = D.ID
1688 AND D.LANGUAGE = 'US'
1689 AND D.NAME = 'Split Asset'
1690 )
1691 )
1692 )
1693 ORDER BY DECODE(p_las_rec.p_order_by
1694 ,'AST',asset_number
1695 ,'YRMF',year_manufactured
1696 ,'MFNM',manufacturer_name
1697 --Bug# 2747693
1698 --,'DESC',description
1699 ,'DESC',4
1700 ,'QTY',current_units
1701 ,'OEC',oec
1702 ,'VEDN',vendor_name
1703 ,'RESV',residual_value
1704 ,'STDT',TO_CHAR(start_date,'dd-mon-yyyy')
1705 ,'ETDT',TO_CHAR(end_date,'dd-mon-yyyy')
1706 ,'TRDT',TO_CHAR(date_terminated,'dd-mon-yyyy')
1707 ,'STS',sts_code
1708 ,'LOC',location_id
1709 ,asset_number) ASC;
1710
1711 CURSOR c_get_new_splt_asset_desc(p_las_rec IN las_rec_type)
1712 IS
1713 SELECT nast.asset_number,
1714 nast.year_manufactured,
1715 nast.manufacturer_name,
1716 nast.description,
1717 nast.current_units,
1718 nast.oec,
1719 nast.vendor_name,
1720 nast.residual_value,
1721 nast.start_date,
1722 nast.end_date,
1723 nast.date_terminated,
1724 nast.sts_code,
1725 nast.location_id,
1726 nast.parent_line_id,
1727 nast.dnz_chr_id
1728 FROM okl_new_assets_uv nast
1729 WHERE nast.dnz_chr_id = p_las_rec.dnz_chr_id
1730 AND UPPER(nast.asset_number) LIKE NVL(UPPER(p_las_rec.asset_number),UPPER(nast.asset_number))
1731 AND NVL(UPPER(nast.vendor_name),'x') LIKE NVL(UPPER(p_las_rec.vendor_name),NVL(UPPER(nast.vendor_name),'x'))
1732 AND nast.oec BETWEEN NVL(p_las_rec.from_oec,nast.oec) AND NVL(p_las_rec.to_oec,nast.oec)
1733 AND NVL(nast.residual_value,0) BETWEEN NVL(p_las_rec.from_residual_value,NVL(nast.residual_value,0)) AND NVL(p_las_rec.to_residual_value,NVL(nast.residual_value,0))
1734 AND UPPER(nast.description) LIKE NVL(UPPER(p_las_rec.description),UPPER(nast.description))
1735 AND nast.sts_code LIKE NVL(p_las_rec.sts_code,nast.sts_code)
1736 AND NVL(nast.start_date,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_start_date,NVL(nast.start_date,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_start_date,NVL(nast.start_date,TO_DATE('1111','yyyy')))
1737 AND NVL(nast.end_date,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_end_date,NVL(nast.end_date,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_end_date,NVL(nast.end_date,TO_DATE('1111','yyyy')))
1738 AND NVL(nast.date_terminated,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_date_terminated,NVL(nast.date_terminated,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_date_terminated,NVL(nast.date_terminated,TO_DATE('1111','yyyy')))
1739 --bug# 4202325 : added following condition
1740 AND ( nast.ASSET_STATUS_CODE <> 'ABANDONED'
1741 OR (
1742 nast.ASSET_STATUS_CODE = 'ABANDONED'
1743 and exists (
1744 select 1
1745 FROM okl_txl_assets_b a, okl_trx_assets b, okl_txd_assets_b c,okl_trx_types_tl d
1746 where a.tas_id = b.id
1747 and b.tsu_code = 'PROCESSED'
1748 and c.tal_id = a.id
1749 and c.split_percent is not null
1750 and a.kle_id =(
1751 SELECT cle.id
1752 FROM OKC_K_LINES_B cle,
1753 OKC_LINE_STYLES_B lse
1754 WHERE cle.dnz_chr_id = nast.dnz_chr_id
1755 AND lse.id = cle.lse_id
1756 AND lse.lty_code = 'FIXED_ASSET'
1757 and cle_id= nast.parent_line_id
1758 )
1759 AND b.try_id = D.ID
1760 AND D.LANGUAGE = 'US'
1761 AND D.NAME = 'Split Asset'
1762 )
1763 )
1764 )
1765 ORDER BY DECODE(p_las_rec.p_order_by
1766 ,'AST',asset_number
1767 ,'YRMF',year_manufactured
1768 ,'MFNM',manufacturer_name
1769 ,'DESC',4
1770 ,'QTY',current_units
1771 ,'OEC',oec
1772 ,'VEDN',vendor_name
1773 ,'RESV',residual_value
1774 ,'STDT',TO_CHAR(start_date,'dd-mon-yyyy')
1775 ,'ETDT',TO_CHAR(end_date,'dd-mon-yyyy')
1776 ,'TRDT',TO_CHAR(date_terminated,'dd-mon-yyyy')
1777 ,'STS',sts_code
1778 ,'LOC',location_id
1779 ,asset_number) DESC;
1780
1781 CURSOR c_get_new_splt_loan_asset_asc(p_las_rec IN las_rec_type)
1782 IS
1783 SELECT nast.asset_number,
1784 nast.year_manufactured,
1785 nast.manufacturer_name,
1786 nast.description,
1787 nast.current_units,
1788 nast.oec,
1789 nast.vendor_name,
1790 nast.residual_value,
1791 nast.start_date,
1792 nast.end_date,
1793 nast.date_terminated,
1794 nast.sts_code,
1795 nast.location_id,
1796 nast.parent_line_id,
1797 nast.dnz_chr_id
1798 FROM okl_new_loan_assets_uv nast
1799 WHERE nast.dnz_chr_id = p_las_rec.dnz_chr_id
1800 AND UPPER(nast.asset_number) LIKE NVL(UPPER(p_las_rec.asset_number),UPPER(nast.asset_number))
1801 AND NVL(UPPER(nast.vendor_name),'x') LIKE NVL(UPPER(p_las_rec.vendor_name),NVL(UPPER(nast.vendor_name),'x'))
1802 AND nast.oec BETWEEN NVL(p_las_rec.from_oec,nast.oec) AND NVL(p_las_rec.to_oec,nast.oec)
1803 AND NVL(nast.residual_value,0) BETWEEN NVL(p_las_rec.from_residual_value,NVL(nast.residual_value,0)) AND NVL(p_las_rec.to_residual_value,NVL(nast.residual_value,0))
1804 AND UPPER(nast.description) LIKE NVL(UPPER(p_las_rec.description),UPPER(nast.description))
1805 AND nast.sts_code LIKE NVL(p_las_rec.sts_code,nast.sts_code)
1806 AND NVL(nast.start_date,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_start_date,NVL(nast.start_date,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_start_date,NVL(nast.start_date,TO_DATE('1111','yyyy')))
1807 AND NVL(nast.end_date,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_end_date,NVL(nast.end_date,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_end_date,NVL(nast.end_date,TO_DATE('1111','yyyy')))
1808 AND NVL(nast.date_terminated,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_date_terminated,NVL(nast.date_terminated,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_date_terminated,NVL(nast.date_terminated,TO_DATE('1111','yyyy')))
1809 AND ( nast.ASSET_STATUS_CODE <> 'ABANDONED'
1810 OR (
1811 nast.ASSET_STATUS_CODE = 'ABANDONED'
1812 and exists (
1813 select 1
1814 FROM okl_txl_assets_b a, okl_trx_assets b, okl_txd_assets_b c,okl_trx_types_tl d
1815 where a.tas_id = b.id
1816 and b.tsu_code = 'PROCESSED'
1817 and c.tal_id = a.id
1818 and c.split_percent is not null
1819 and a.kle_id =(
1820 SELECT cle.id
1821 FROM OKC_K_LINES_B cle,
1822 OKC_LINE_STYLES_B lse
1823 WHERE cle.dnz_chr_id = nast.dnz_chr_id
1824 AND lse.id = cle.lse_id
1825 AND lse.lty_code = 'FIXED_ASSET'
1826 and cle_id= nast.parent_line_id
1827 )
1828 AND b.try_id = D.ID
1829 AND D.LANGUAGE = 'US'
1830 AND D.NAME = 'Split Asset'
1831 )
1832 )
1833 )
1834 ORDER BY DECODE(p_las_rec.p_order_by
1835 ,'AST',asset_number
1836 ,'YRMF',year_manufactured
1837 ,'MFNM',manufacturer_name
1838 ,'DESC',4
1839 ,'QTY',current_units
1840 ,'OEC',oec
1841 ,'VEDN',vendor_name
1842 ,'RESV',residual_value
1843 ,'STDT',TO_CHAR(start_date,'dd-mon-yyyy')
1844 ,'ETDT',TO_CHAR(end_date,'dd-mon-yyyy')
1845 ,'TRDT',TO_CHAR(date_terminated,'dd-mon-yyyy')
1846 ,'STS',sts_code
1847 ,'LOC',location_id
1848 ,asset_number) ASC;
1849
1850
1851 CURSOR c_get_new_splt_loan_asset_desc(p_las_rec IN las_rec_type)
1852 IS
1853 SELECT nast.asset_number,
1854 nast.year_manufactured,
1855 nast.manufacturer_name,
1856 nast.description,
1857 nast.current_units,
1858 nast.oec,
1859 nast.vendor_name,
1860 nast.residual_value,
1861 nast.start_date,
1862 nast.end_date,
1863 nast.date_terminated,
1864 nast.sts_code,
1865 nast.location_id,
1866 nast.parent_line_id,
1867 nast.dnz_chr_id
1868 FROM okl_new_loan_assets_uv nast
1869 WHERE nast.dnz_chr_id = p_las_rec.dnz_chr_id
1870 AND UPPER(nast.asset_number) LIKE NVL(UPPER(p_las_rec.asset_number),UPPER(nast.asset_number))
1871 AND NVL(UPPER(nast.vendor_name),'x') LIKE NVL(UPPER(p_las_rec.vendor_name),NVL(UPPER(nast.vendor_name),'x'))
1872 AND nast.oec BETWEEN NVL(p_las_rec.from_oec,nast.oec) AND NVL(p_las_rec.to_oec,nast.oec)
1873 AND NVL(nast.residual_value,0) BETWEEN NVL(p_las_rec.from_residual_value,NVL(nast.residual_value,0)) AND NVL(p_las_rec.to_residual_value,NVL(nast.residual_value,0))
1874 AND UPPER(nast.description) LIKE NVL(UPPER(p_las_rec.description),UPPER(nast.description))
1875 AND nast.sts_code LIKE NVL(p_las_rec.sts_code,nast.sts_code)
1876 AND NVL(nast.start_date,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_start_date,NVL(nast.start_date,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_start_date,NVL(nast.start_date,TO_DATE('1111','yyyy')))
1877 AND NVL(nast.end_date,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_end_date,NVL(nast.end_date,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_end_date,NVL(nast.end_date,TO_DATE('1111','yyyy')))
1878 AND NVL(nast.date_terminated,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_date_terminated,NVL(nast.date_terminated,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_date_terminated,NVL(nast.date_terminated,TO_DATE('1111','yyyy')))
1879 AND ( nast.ASSET_STATUS_CODE <> 'ABANDONED'
1880 OR (
1881 nast.ASSET_STATUS_CODE = 'ABANDONED'
1882 and exists (
1883 select 1
1884 FROM okl_txl_assets_b a, okl_trx_assets b, okl_txd_assets_b c,okl_trx_types_tl d
1885 where a.tas_id = b.id
1886 and b.tsu_code = 'PROCESSED'
1887 and c.tal_id = a.id
1888 and c.split_percent is not null
1889 and a.kle_id =(
1890 SELECT cle.id
1891 FROM OKC_K_LINES_B cle,
1892 OKC_LINE_STYLES_B lse
1893 WHERE cle.dnz_chr_id = nast.dnz_chr_id
1894 AND lse.id = cle.lse_id
1895 AND lse.lty_code = 'FIXED_ASSET'
1896 and cle_id= nast.parent_line_id
1897 )
1898 AND b.try_id = D.ID
1899 AND D.LANGUAGE = 'US'
1900 AND D.NAME = 'Split Asset'
1901 )
1902 )
1903 )
1904 ORDER BY DECODE(p_las_rec.p_order_by
1905 ,'AST',asset_number
1906 ,'YRMF',year_manufactured
1907 ,'MFNM',manufacturer_name
1908 ,'DESC',4
1909 ,'QTY',current_units
1910 ,'OEC',oec
1911 ,'VEDN',vendor_name
1912 ,'RESV',residual_value
1913 ,'STDT',TO_CHAR(start_date,'dd-mon-yyyy')
1914 ,'ETDT',TO_CHAR(end_date,'dd-mon-yyyy')
1915 ,'TRDT',TO_CHAR(date_terminated,'dd-mon-yyyy')
1916 ,'STS',sts_code
1917 ,'LOC',location_id
1918 ,asset_number) DESC;
1919
1920
1921 --Bug# 4202325: Added cursor for split asset - end
1922
1923
1924
1925 BEGIN
1926 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1927 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
1928 G_PKG_NAME,
1929 p_init_msg_list,
1930 l_api_version,
1931 p_api_version,
1932 '_PVT',
1933 x_return_status);
1934 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1935 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1936 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1937 RAISE OKL_API.G_EXCEPTION_ERROR;
1938 END IF;
1939
1940 -- cklee
1941 validate_asset_rec(p_api_version => p_api_version,
1942 p_init_msg_list => p_init_msg_list,
1943 x_return_status => x_return_status,
1944 x_msg_count => x_msg_count,
1945 x_msg_data => x_msg_data,
1946 p_las_rec => l_las_rec,
1947 x_las_rec => lx_las_rec);
1948
1949 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1950 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1951 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1952 RAISE OKL_API.G_EXCEPTION_ERROR;
1953 END IF;
1954
1955 -- copy record back
1956 l_las_rec := lx_las_rec;
1957 -- cklee
1958
1959 -- Get the sts code since we can version only active contract
1960 OPEN c_get_sts_code(l_las_rec.dnz_chr_id);
1961 IF c_get_sts_code%NOTFOUND THEN
1962 OKL_API.set_message(p_app_name => G_APP_NAME,
1963 p_msg_name => G_NO_MATCHING_RECORD,
1964 p_token1 => G_COL_NAME_TOKEN,
1965 p_token1_value => 'OKC_K_HEADERS_V.STS_CODE');
1966 RAISE OKL_API.G_EXCEPTION_ERROR;
1967 END IF;
1968 FETCH c_get_sts_code INTO lv_sts_code, lv_deal_type;
1969 CLOSE c_get_sts_code;
1970
1971 --cklee
1972 -- Check if the info to be extracted from FA or Transaction tables.
1973 l_active_contract := isContractActive(l_las_rec.dnz_chr_id,
1974 lv_deal_type,
1975 lv_sts_code);
1976
1977 IF (l_active_contract) THEN
1978
1979 --cklee: get entire old asset locations for a contract
1980 OPEN c_get_old_asset_loc(l_las_rec.dnz_chr_id);
1981 FETCH c_get_old_asset_loc BULK COLLECT INTO l_fin_line_ids, l_loc_ids LIMIT G_BULK_BATCH_SIZE;
1982 CLOSE c_get_old_asset_loc;
1983
1984 OPEN c_get_old_asset_party_loc(l_las_rec.dnz_chr_id);
1985 FETCH c_get_old_asset_party_loc BULK COLLECT INTO l_fin_line_2_ids, l_loc_2_ids LIMIT G_BULK_BATCH_SIZE;
1986 CLOSE c_get_old_asset_party_loc;
1987
1988 ELSE
1989
1990 --cklee: get entire new asset locations for a contract
1991 OPEN c_get_new_asset_loc(l_las_rec.dnz_chr_id);
1992 FETCH c_get_new_asset_loc BULK COLLECT INTO l_fin_line_ids, l_loc_ids LIMIT G_BULK_BATCH_SIZE;
1993 CLOSE c_get_new_asset_loc;
1994
1995 END IF;
1996 --cklee
1997
1998 -- Check for Loan and Loan Revolving contracts.
1999 IF (lv_deal_type IN ('LOAN', 'LOAN-REVOLVING')) THEN
2000
2001 IF (l_active_contract) THEN
2002 OPEN c_check_assets_in_fa(l_las_rec.dnz_chr_id);
2003 FETCH c_check_assets_in_fa INTO l_return_value;
2004 l_info_exists := c_check_assets_in_fa%FOUND;
2005 CLOSE c_check_assets_in_fa;
2006
2007 IF (l_info_exists) THEN
2008 IF l_las_rec.p_sort_by = 'DESC' THEN
2009 -- FOR r_get_old_asset_desc IN c_get_old_asset_desc(l_las_rec) LOOP
2010 IF l_las_rec.include_split_yn='Y' THEN
2011 FOR r_las_rec IN c_get_old_splt_asset_desc(l_las_rec) LOOP
2012 IF c_get_old_splt_asset_desc%NOTFOUND THEN
2013 RAISE OKL_API.G_EXCEPTION_ERROR;
2014 END IF;
2015 copy_asset_rec(p_api_version => p_api_version,
2016 p_init_msg_list => p_init_msg_list,
2017 x_return_status => x_return_status,
2018 x_msg_count => x_msg_count,
2019 x_msg_data => x_msg_data,
2020 p_las_rec => r_las_rec,
2021 p_idx => i,
2022 p_active_contract => l_active_contract,
2023 p_fin_line_ids => l_fin_line_ids,
2024 p_loc_ids => l_loc_ids,
2025 p_fin_line_2_ids => l_fin_line_2_ids,
2026 p_loc_2_ids => l_loc_2_ids,
2027 x_las_rec => x_las_tbl(i));
2028
2029 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2030 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2031 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2032 RAISE OKL_API.G_EXCEPTION_ERROR;
2033 END IF;
2034
2035 i := i + 1;
2036 END LOOP;
2037 ELSE
2038 FOR r_las_rec IN c_get_old_asset_desc(l_las_rec) LOOP
2039 IF c_get_old_asset_desc%NOTFOUND THEN
2040 RAISE OKL_API.G_EXCEPTION_ERROR;
2041 END IF;
2042
2043 copy_asset_rec(p_api_version => p_api_version,
2044 p_init_msg_list => p_init_msg_list,
2045 x_return_status => x_return_status,
2046 x_msg_count => x_msg_count,
2047 x_msg_data => x_msg_data,
2048 p_las_rec => r_las_rec,
2049 p_idx => i,
2050 p_active_contract => l_active_contract,
2051 p_fin_line_ids => l_fin_line_ids,
2052 p_loc_ids => l_loc_ids,
2053 p_fin_line_2_ids => l_fin_line_2_ids,
2054 p_loc_2_ids => l_loc_2_ids,
2055 x_las_rec => x_las_tbl(i));
2056
2057 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2058 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2059 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2060 RAISE OKL_API.G_EXCEPTION_ERROR;
2061 END IF;
2062
2063 i := i + 1;
2064 END LOOP;
2065 END IF;
2066 ELSE
2067 -- FOR r_get_old_asset_asc IN c_get_old_asset_asc(l_las_rec) LOOP
2068
2069 IF l_las_rec.include_split_yn='Y' THEN
2070
2071 FOR r_las_rec IN c_get_old_splt_asset_asc(l_las_rec) LOOP
2072 IF c_get_old_splt_asset_asc%NOTFOUND THEN
2073 RAISE OKL_API.G_EXCEPTION_ERROR;
2074 END IF;
2075
2076 copy_asset_rec(p_api_version => p_api_version,
2077 p_init_msg_list => p_init_msg_list,
2078 x_return_status => x_return_status,
2079 x_msg_count => x_msg_count,
2080 x_msg_data => x_msg_data,
2081 p_las_rec => r_las_rec,
2082 p_idx => i,
2083 p_active_contract => l_active_contract,
2084 p_fin_line_ids => l_fin_line_ids,
2085 p_loc_ids => l_loc_ids,
2086 p_fin_line_2_ids => l_fin_line_2_ids,
2087 p_loc_2_ids => l_loc_2_ids,
2088 x_las_rec => x_las_tbl(i));
2089
2090 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2091 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2092 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2093 RAISE OKL_API.G_EXCEPTION_ERROR;
2094 END IF;
2095
2096 i := i + 1;
2097 END LOOP;
2098
2099 ELSE
2100 FOR r_las_rec IN c_get_old_asset_asc(l_las_rec) LOOP
2101 IF c_get_old_asset_asc%NOTFOUND THEN
2102 RAISE OKL_API.G_EXCEPTION_ERROR;
2103 END IF;
2104
2105 copy_asset_rec(p_api_version => p_api_version,
2106 p_init_msg_list => p_init_msg_list,
2107 x_return_status => x_return_status,
2108 x_msg_count => x_msg_count,
2109 x_msg_data => x_msg_data,
2110 p_las_rec => r_las_rec,
2111 p_idx => i,
2112 p_active_contract => l_active_contract,
2113 p_fin_line_ids => l_fin_line_ids,
2114 p_loc_ids => l_loc_ids,
2115 p_fin_line_2_ids => l_fin_line_2_ids,
2116 p_loc_2_ids => l_loc_2_ids,
2117 x_las_rec => x_las_tbl(i));
2118
2119 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2120 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2121 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2122 RAISE OKL_API.G_EXCEPTION_ERROR;
2123 END IF;
2124
2125 i := i + 1;
2126 END LOOP;
2127 END IF;
2128 END IF;
2129 ELSE -- Active New Query
2130 IF l_las_rec.p_sort_by = 'DESC' THEN
2131 -- FOR r_get_old_asset_desc IN c_get_old_loan_asset_desc(l_las_rec) LOOP
2132 IF l_las_rec.include_split_yn='Y' THEN
2133 FOR r_las_rec IN c_get_old_splt_loan_asset_desc(l_las_rec) LOOP
2134 IF c_get_old_splt_loan_asset_desc%NOTFOUND THEN
2135 RAISE OKL_API.G_EXCEPTION_ERROR;
2136 END IF;
2137
2138 copy_asset_rec(p_api_version => p_api_version,
2139 p_init_msg_list => p_init_msg_list,
2140 x_return_status => x_return_status,
2141 x_msg_count => x_msg_count,
2142 x_msg_data => x_msg_data,
2143 p_las_rec => r_las_rec,
2144 p_idx => i,
2145 p_active_contract => l_active_contract,
2146 p_fin_line_ids => l_fin_line_ids,
2147 p_loc_ids => l_loc_ids,
2148 p_fin_line_2_ids => l_fin_line_2_ids,
2149 p_loc_2_ids => l_loc_2_ids,
2150 x_las_rec => x_las_tbl(i));
2151
2152 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2153 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2154 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2155 RAISE OKL_API.G_EXCEPTION_ERROR;
2156 END IF;
2157
2158 i := i + 1;
2159 END LOOP;
2160
2161 ELSE
2162 FOR r_las_rec IN c_get_old_loan_asset_desc(l_las_rec) LOOP
2163 IF c_get_old_loan_asset_desc%NOTFOUND THEN
2164 RAISE OKL_API.G_EXCEPTION_ERROR;
2165 END IF;
2166
2167 copy_asset_rec(p_api_version => p_api_version,
2168 p_init_msg_list => p_init_msg_list,
2169 x_return_status => x_return_status,
2170 x_msg_count => x_msg_count,
2171 x_msg_data => x_msg_data,
2172 p_las_rec => r_las_rec,
2173 p_idx => i,
2174 p_active_contract => l_active_contract,
2175 p_fin_line_ids => l_fin_line_ids,
2176 p_loc_ids => l_loc_ids,
2177 p_fin_line_2_ids => l_fin_line_2_ids,
2178 p_loc_2_ids => l_loc_2_ids,
2179 x_las_rec => x_las_tbl(i));
2180
2181 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2182 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2183 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2184 RAISE OKL_API.G_EXCEPTION_ERROR;
2185 END IF;
2186
2187 i := i + 1;
2188 END LOOP;
2189 END IF; --split asset end if
2190 ELSE
2191 -- FOR r_get_old_asset_asc IN c_get_old_loan_asset_asc(l_las_rec) LOOP
2192 IF l_las_rec.include_split_yn='Y' THEN
2193 FOR r_las_rec IN c_get_old_splt_loan_asset_asc(l_las_rec) LOOP
2194 IF c_get_old_splt_loan_asset_asc%NOTFOUND THEN
2195 RAISE OKL_API.G_EXCEPTION_ERROR;
2196 END IF;
2197
2198 copy_asset_rec(p_api_version => p_api_version,
2199 p_init_msg_list => p_init_msg_list,
2200 x_return_status => x_return_status,
2201 x_msg_count => x_msg_count,
2202 x_msg_data => x_msg_data,
2203 p_las_rec => r_las_rec,
2204 p_idx => i,
2205 p_active_contract => l_active_contract,
2206 p_fin_line_ids => l_fin_line_ids,
2207 p_loc_ids => l_loc_ids,
2208 p_fin_line_2_ids => l_fin_line_2_ids,
2209 p_loc_2_ids => l_loc_2_ids,
2210 x_las_rec => x_las_tbl(i));
2211
2212 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2213 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2214 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2215 RAISE OKL_API.G_EXCEPTION_ERROR;
2216 END IF;
2217
2218 i := i + 1;
2219 END LOOP;
2220
2221 ELSE
2222 FOR r_las_rec IN c_get_old_loan_asset_asc(l_las_rec) LOOP
2223 IF c_get_old_loan_asset_asc%NOTFOUND THEN
2224 RAISE OKL_API.G_EXCEPTION_ERROR;
2225 END IF;
2226
2227 copy_asset_rec(p_api_version => p_api_version,
2228 p_init_msg_list => p_init_msg_list,
2229 x_return_status => x_return_status,
2230 x_msg_count => x_msg_count,
2231 x_msg_data => x_msg_data,
2232 p_las_rec => r_las_rec,
2233 p_idx => i,
2234 p_active_contract => l_active_contract,
2235 p_fin_line_ids => l_fin_line_ids,
2236 p_loc_ids => l_loc_ids,
2237 p_fin_line_2_ids => l_fin_line_2_ids,
2238 p_loc_2_ids => l_loc_2_ids,
2239 x_las_rec => x_las_tbl(i));
2240
2241 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2242 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2243 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2244 RAISE OKL_API.G_EXCEPTION_ERROR;
2245 END IF;
2246
2247 i := i + 1;
2248 END LOOP;
2249
2250 END IF; -- split end if
2251
2252 END IF;
2253 END IF;
2254 ELSE -- Contract is not active.
2255 IF l_las_rec.p_sort_by = 'DESC' THEN
2256 -- FOR r_get_new_asset_desc IN c_get_new_loan_asset_desc(l_las_rec) LOOP
2257 IF l_las_rec.include_split_yn='Y' THEN
2258 FOR r_las_rec IN c_get_new_splt_loan_asset_desc(l_las_rec) LOOP
2259 IF c_get_new_splt_loan_asset_desc%NOTFOUND THEN
2260 RAISE OKL_API.G_EXCEPTION_ERROR;
2261 END IF;
2262 copy_asset_rec(p_api_version => p_api_version,
2263 p_init_msg_list => p_init_msg_list,
2264 x_return_status => x_return_status,
2265 x_msg_count => x_msg_count,
2266 x_msg_data => x_msg_data,
2267 p_las_rec => r_las_rec,
2268 p_idx => i,
2269 p_active_contract => l_active_contract,
2270 p_fin_line_ids => l_fin_line_ids,
2271 p_loc_ids => l_loc_ids,
2272 p_fin_line_2_ids => l_fin_line_2_ids,
2273 p_loc_2_ids => l_loc_2_ids,
2274 x_las_rec => x_las_tbl(i));
2275
2276 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2277 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2278 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2279 RAISE OKL_API.G_EXCEPTION_ERROR;
2280 END IF;
2281 i := i + 1;
2282 END LOOP;
2283
2284 ELSE --split else
2285 FOR r_las_rec IN c_get_new_loan_asset_desc(l_las_rec) LOOP
2286 IF c_get_new_loan_asset_desc%NOTFOUND THEN
2287 RAISE OKL_API.G_EXCEPTION_ERROR;
2288 END IF;
2289
2290 copy_asset_rec(p_api_version => p_api_version,
2291 p_init_msg_list => p_init_msg_list,
2292 x_return_status => x_return_status,
2293 x_msg_count => x_msg_count,
2294 x_msg_data => x_msg_data,
2295 p_las_rec => r_las_rec,
2296 p_idx => i,
2297 p_active_contract => l_active_contract,
2298 p_fin_line_ids => l_fin_line_ids,
2299 p_loc_ids => l_loc_ids,
2300 p_fin_line_2_ids => l_fin_line_2_ids,
2301 p_loc_2_ids => l_loc_2_ids,
2302 x_las_rec => x_las_tbl(i));
2303
2304 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2305 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2306 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2307 RAISE OKL_API.G_EXCEPTION_ERROR;
2308 END IF;
2309
2310 i := i + 1;
2311 END LOOP;
2312 END IF; --split end if
2313 ELSE
2314 -- FOR r_get_new_asset_asc IN c_get_new_loan_asset_asc(l_las_rec) LOOP
2315 IF l_las_rec.include_split_yn='Y' THEN
2316 FOR r_las_rec IN c_get_new_splt_loan_asset_asc(l_las_rec) LOOP
2317 IF c_get_new_splt_loan_asset_asc%NOTFOUND THEN
2318 RAISE OKL_API.G_EXCEPTION_ERROR;
2319 END IF;
2320
2321 copy_asset_rec(p_api_version => p_api_version,
2322 p_init_msg_list => p_init_msg_list,
2323 x_return_status => x_return_status,
2324 x_msg_count => x_msg_count,
2325 x_msg_data => x_msg_data,
2326 p_las_rec => r_las_rec,
2327 p_idx => i,
2328 p_active_contract => l_active_contract,
2329 p_fin_line_ids => l_fin_line_ids,
2330 p_loc_ids => l_loc_ids,
2331 p_fin_line_2_ids => l_fin_line_2_ids,
2332 p_loc_2_ids => l_loc_2_ids,
2333 x_las_rec => x_las_tbl(i));
2334
2335 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2336 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2337 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2338 RAISE OKL_API.G_EXCEPTION_ERROR;
2339 END IF;
2340
2341 i := i + 1;
2342 END LOOP;
2343
2344 ELSE --split Else
2345 FOR r_las_rec IN c_get_new_loan_asset_asc(l_las_rec) LOOP
2346 IF c_get_new_loan_asset_asc%NOTFOUND THEN
2347 RAISE OKL_API.G_EXCEPTION_ERROR;
2348 END IF;
2349
2350 copy_asset_rec(p_api_version => p_api_version,
2351 p_init_msg_list => p_init_msg_list,
2352 x_return_status => x_return_status,
2353 x_msg_count => x_msg_count,
2354 x_msg_data => x_msg_data,
2355 p_las_rec => r_las_rec,
2356 p_idx => i,
2357 p_active_contract => l_active_contract,
2358 p_fin_line_ids => l_fin_line_ids,
2359 p_loc_ids => l_loc_ids,
2360 p_fin_line_2_ids => l_fin_line_2_ids,
2361 p_loc_2_ids => l_loc_2_ids,
2362 x_las_rec => x_las_tbl(i));
2363
2364 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2365 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2366 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2367 RAISE OKL_API.G_EXCEPTION_ERROR;
2368 END IF;
2369
2370 i := i + 1;
2371 END LOOP;
2372
2373 END IF; --split END IF
2374
2375 END IF;
2376 END IF;
2377 ELSE -- Contract is neither 'LOAN' nor 'LOAN-REVOLVING'
2378
2379 IF (l_active_contract) THEN
2380 IF l_las_rec.p_sort_by = 'DESC' THEN
2381 -- FOR r_get_old_asset_desc IN c_get_old_asset_desc(l_las_rec) LOOP
2382 IF l_las_rec.include_split_yn='Y' THEN
2383 FOR r_las_rec IN c_get_old_splt_asset_desc(l_las_rec) LOOP
2384 IF c_get_old_splt_asset_desc%NOTFOUND THEN
2385 RAISE OKL_API.G_EXCEPTION_ERROR;
2386 END IF;
2387
2388 copy_asset_rec(p_api_version => p_api_version,
2389 p_init_msg_list => p_init_msg_list,
2390 x_return_status => x_return_status,
2391 x_msg_count => x_msg_count,
2392 x_msg_data => x_msg_data,
2393 p_las_rec => r_las_rec,
2394 p_idx => i,
2395 p_active_contract => l_active_contract,
2396 p_fin_line_ids => l_fin_line_ids,
2397 p_loc_ids => l_loc_ids,
2398 p_fin_line_2_ids => l_fin_line_2_ids,
2399 p_loc_2_ids => l_loc_2_ids,
2400 x_las_rec => x_las_tbl(i));
2401
2402 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2403 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2404 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2405 RAISE OKL_API.G_EXCEPTION_ERROR;
2406 END IF;
2407
2408 i := i + 1;
2409 END LOOP;
2410 ELSE
2411 FOR r_las_rec IN c_get_old_asset_desc(l_las_rec) LOOP
2412 IF c_get_old_asset_desc%NOTFOUND THEN
2413 RAISE OKL_API.G_EXCEPTION_ERROR;
2414 END IF;
2415
2416 copy_asset_rec(p_api_version => p_api_version,
2417 p_init_msg_list => p_init_msg_list,
2418 x_return_status => x_return_status,
2419 x_msg_count => x_msg_count,
2420 x_msg_data => x_msg_data,
2421 p_las_rec => r_las_rec,
2422 p_idx => i,
2423 p_active_contract => l_active_contract,
2424 p_fin_line_ids => l_fin_line_ids,
2425 p_loc_ids => l_loc_ids,
2426 p_fin_line_2_ids => l_fin_line_2_ids,
2427 p_loc_2_ids => l_loc_2_ids,
2428 x_las_rec => x_las_tbl(i));
2429
2430 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2431 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2432 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2433 RAISE OKL_API.G_EXCEPTION_ERROR;
2434 END IF;
2435
2436 i := i + 1;
2437 END LOOP;
2438 END IF;
2439
2440 ELSE
2441 -- FOR r_get_old_asset_asc IN c_get_old_asset_asc(l_las_rec) LOOP
2442
2443 IF l_las_rec.include_split_yn='Y' THEN
2444 FOR r_las_rec IN c_get_old_splt_asset_asc(l_las_rec) LOOP
2445 IF c_get_old_splt_asset_asc%NOTFOUND THEN
2446 RAISE OKL_API.G_EXCEPTION_ERROR;
2447 END IF;
2448
2449 copy_asset_rec(p_api_version => p_api_version,
2450 p_init_msg_list => p_init_msg_list,
2451 x_return_status => x_return_status,
2452 x_msg_count => x_msg_count,
2453 x_msg_data => x_msg_data,
2454 p_las_rec => r_las_rec,
2455 p_idx => i,
2456 p_active_contract => l_active_contract,
2457 p_fin_line_ids => l_fin_line_ids,
2458 p_loc_ids => l_loc_ids,
2459 p_fin_line_2_ids => l_fin_line_2_ids,
2460 p_loc_2_ids => l_loc_2_ids,
2461 x_las_rec => x_las_tbl(i));
2462
2463 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2464 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2465 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2466 RAISE OKL_API.G_EXCEPTION_ERROR;
2467 END IF;
2468
2469 i := i + 1;
2470 END LOOP;
2471
2472 ELSE
2473
2474 FOR r_las_rec IN c_get_old_asset_asc(l_las_rec) LOOP
2475 IF c_get_old_asset_asc%NOTFOUND THEN
2476 RAISE OKL_API.G_EXCEPTION_ERROR;
2477 END IF;
2478
2479 copy_asset_rec(p_api_version => p_api_version,
2480 p_init_msg_list => p_init_msg_list,
2481 x_return_status => x_return_status,
2482 x_msg_count => x_msg_count,
2483 x_msg_data => x_msg_data,
2484 p_las_rec => r_las_rec,
2485 p_idx => i,
2486 p_active_contract => l_active_contract,
2487 p_fin_line_ids => l_fin_line_ids,
2488 p_loc_ids => l_loc_ids,
2489 p_fin_line_2_ids => l_fin_line_2_ids,
2490 p_loc_2_ids => l_loc_2_ids,
2491 x_las_rec => x_las_tbl(i));
2492
2493 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2494 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2495 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2496 RAISE OKL_API.G_EXCEPTION_ERROR;
2497 END IF;
2498
2499 i := i + 1;
2500 END LOOP;
2501
2502
2503 END IF;
2504
2505 END IF;
2506 ELSE
2507 IF l_las_rec.p_sort_by = 'DESC' THEN
2508 -- FOR r_get_new_asset_desc IN c_get_new_asset_desc(l_las_rec) LOOP
2509 IF l_las_rec.include_split_yn='Y' THEN
2510 FOR r_las_rec IN c_get_new_splt_asset_desc(l_las_rec) LOOP
2511 IF c_get_new_splt_asset_desc%NOTFOUND THEN
2512 RAISE OKL_API.G_EXCEPTION_ERROR;
2513 END IF;
2514
2515 copy_asset_rec(p_api_version => p_api_version,
2516 p_init_msg_list => p_init_msg_list,
2517 x_return_status => x_return_status,
2518 x_msg_count => x_msg_count,
2519 x_msg_data => x_msg_data,
2520 p_las_rec => r_las_rec,
2521 p_idx => i,
2522 p_active_contract => l_active_contract,
2523 p_fin_line_ids => l_fin_line_ids,
2524 p_loc_ids => l_loc_ids,
2525 p_fin_line_2_ids => l_fin_line_2_ids,
2526 p_loc_2_ids => l_loc_2_ids,
2527 x_las_rec => x_las_tbl(i));
2528
2529 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2530 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2531 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2532 RAISE OKL_API.G_EXCEPTION_ERROR;
2533 END IF;
2534
2535 i := i + 1;
2536 END LOOP;
2537
2538 ELSE --split else
2539 FOR r_las_rec IN c_get_new_asset_desc(l_las_rec) LOOP
2540 IF c_get_new_asset_desc%NOTFOUND THEN
2541 RAISE OKL_API.G_EXCEPTION_ERROR;
2542 END IF;
2543
2544 copy_asset_rec(p_api_version => p_api_version,
2545 p_init_msg_list => p_init_msg_list,
2546 x_return_status => x_return_status,
2547 x_msg_count => x_msg_count,
2548 x_msg_data => x_msg_data,
2549 p_las_rec => r_las_rec,
2550 p_idx => i,
2551 p_active_contract => l_active_contract,
2552 p_fin_line_ids => l_fin_line_ids,
2553 p_loc_ids => l_loc_ids,
2554 p_fin_line_2_ids => l_fin_line_2_ids,
2555 p_loc_2_ids => l_loc_2_ids,
2556 x_las_rec => x_las_tbl(i));
2557
2558 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2559 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2560 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2561 RAISE OKL_API.G_EXCEPTION_ERROR;
2562 END IF;
2563
2564 i := i + 1;
2565 END LOOP;
2566
2567 END IF; --split end if
2568
2569 ELSE
2570 -- FOR r_get_new_asset_asc IN c_get_new_asset_asc(l_las_rec) LOOP
2571 IF l_las_rec.include_split_yn='Y' THEN
2572 FOR r_las_rec IN c_get_new_splt_asset_asc(l_las_rec) LOOP
2573 IF c_get_new_splt_asset_asc%NOTFOUND THEN
2574 RAISE OKL_API.G_EXCEPTION_ERROR;
2575 END IF;
2576 copy_asset_rec(p_api_version => p_api_version,
2577 p_init_msg_list => p_init_msg_list,
2578 x_return_status => x_return_status,
2579 x_msg_count => x_msg_count,
2580 x_msg_data => x_msg_data,
2581 p_las_rec => r_las_rec,
2582 p_idx => i,
2583 p_active_contract => l_active_contract,
2584 p_fin_line_ids => l_fin_line_ids,
2585 p_loc_ids => l_loc_ids,
2586 p_fin_line_2_ids => l_fin_line_2_ids,
2587 p_loc_2_ids => l_loc_2_ids,
2588 x_las_rec => x_las_tbl(i));
2589
2590 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2591 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2592 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2593 RAISE OKL_API.G_EXCEPTION_ERROR;
2594 END IF;
2595
2596 i := i + 1;
2597 END LOOP;
2598
2599 ELSE --split else
2600 FOR r_las_rec IN c_get_new_asset_asc(l_las_rec) LOOP
2601 IF c_get_new_asset_asc%NOTFOUND THEN
2602 RAISE OKL_API.G_EXCEPTION_ERROR;
2603 END IF;
2604
2605 copy_asset_rec(p_api_version => p_api_version,
2606 p_init_msg_list => p_init_msg_list,
2607 x_return_status => x_return_status,
2608 x_msg_count => x_msg_count,
2609 x_msg_data => x_msg_data,
2610 p_las_rec => r_las_rec,
2611 p_idx => i,
2612 p_active_contract => l_active_contract,
2613 p_fin_line_ids => l_fin_line_ids,
2614 p_loc_ids => l_loc_ids,
2615 p_fin_line_2_ids => l_fin_line_2_ids,
2616 p_loc_2_ids => l_loc_2_ids,
2617 x_las_rec => x_las_tbl(i));
2618
2619 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2620 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2621 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2622 RAISE OKL_API.G_EXCEPTION_ERROR;
2623 END IF;
2624
2625 i := i + 1;
2626 END LOOP;
2627 END IF; --split end
2628
2629 END IF;
2630 END IF;
2631 END IF;
2632
2633 OKL_API.END_ACTIVITY (x_msg_count,
2634 x_msg_data );
2635
2636 EXCEPTION
2637 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2638 IF c_get_new_asset_desc%ISOPEN THEN
2639 CLOSE c_get_new_asset_desc;
2640 END IF;
2641 IF c_get_old_asset_desc%ISOPEN THEN
2642 CLOSE c_get_old_asset_desc;
2643 END IF;
2644 IF c_get_new_asset_asc%ISOPEN THEN
2645 CLOSE c_get_new_asset_asc;
2646 END IF;
2647 IF c_get_old_asset_asc%ISOPEN THEN
2648 CLOSE c_get_old_asset_asc;
2649 END IF;
2650 IF c_get_sts_code%ISOPEN THEN
2651 CLOSE c_get_sts_code;
2652 END IF;
2653 --cklee
2654 IF c_get_old_asset_loc%ISOPEN THEN
2655 CLOSE c_get_old_asset_loc;
2656 END IF;
2657 IF c_get_new_asset_loc%ISOPEN THEN
2658 CLOSE c_get_new_asset_loc;
2659 END IF;
2660 IF c_get_old_asset_party_loc%ISOPEN THEN
2661 CLOSE c_get_old_asset_party_loc;
2662 END IF;
2663 --cklee
2664 --Bug#4402325 start
2665 IF c_get_old_splt_asset_desc%ISOPEN THEN
2666 CLOSE c_get_old_splt_asset_desc;
2667 END IF;
2668 IF c_get_old_splt_asset_asc%ISOPEN THEN
2669 CLOSE c_get_old_splt_asset_asc;
2670 END IF;
2671
2672 --Bug#4402325 end
2673
2674
2675 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2676 l_api_name,
2677 G_PKG_NAME,
2678 'OKL_API.G_RET_STS_ERROR',
2679 x_msg_count,
2680 x_msg_data,
2681 '_PVT');
2682 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2683 IF c_get_new_asset_desc%ISOPEN THEN
2684 CLOSE c_get_new_asset_desc;
2685 END IF;
2686 IF c_get_old_asset_desc%ISOPEN THEN
2687 CLOSE c_get_old_asset_desc;
2688 END IF;
2689 IF c_get_new_asset_asc%ISOPEN THEN
2690 CLOSE c_get_new_asset_asc;
2691 END IF;
2692 IF c_get_old_asset_asc%ISOPEN THEN
2693 CLOSE c_get_old_asset_asc;
2694 END IF;
2695 IF c_get_sts_code%ISOPEN THEN
2696 CLOSE c_get_sts_code;
2697 END IF;
2698 --cklee
2699 IF c_get_old_asset_loc%ISOPEN THEN
2700 CLOSE c_get_old_asset_loc;
2701 END IF;
2702 IF c_get_new_asset_loc%ISOPEN THEN
2703 CLOSE c_get_new_asset_loc;
2704 END IF;
2705 IF c_get_old_asset_party_loc%ISOPEN THEN
2706 CLOSE c_get_old_asset_party_loc;
2707 END IF;
2708 --cklee
2709 --Bug#4402325 start
2710 IF c_get_old_splt_asset_desc%ISOPEN THEN
2711 CLOSE c_get_old_splt_asset_desc;
2712 END IF;
2713 IF c_get_old_splt_asset_asc%ISOPEN THEN
2714 CLOSE c_get_old_splt_asset_asc;
2715 END IF;
2716
2717 --Bug#4402325 end
2718
2719
2720 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2721 l_api_name,
2722 G_PKG_NAME,
2723 'OKL_API.G_RET_STS_UNEXP_ERROR',
2724 x_msg_count,
2725 x_msg_data,
2726 '_PVT');
2727 WHEN OTHERS THEN
2728 IF c_get_new_asset_desc%ISOPEN THEN
2729 CLOSE c_get_new_asset_desc;
2730 END IF;
2731 IF c_get_old_asset_desc%ISOPEN THEN
2732 CLOSE c_get_old_asset_desc;
2733 END IF;
2734 IF c_get_new_asset_asc%ISOPEN THEN
2735 CLOSE c_get_new_asset_asc;
2736 END IF;
2737 IF c_get_old_asset_asc%ISOPEN THEN
2738 CLOSE c_get_old_asset_asc;
2739 END IF;
2740 IF c_get_sts_code%ISOPEN THEN
2741 CLOSE c_get_sts_code;
2742 END IF;
2743 --cklee
2744 IF c_get_old_asset_loc%ISOPEN THEN
2745 CLOSE c_get_old_asset_loc;
2746 END IF;
2747 IF c_get_new_asset_loc%ISOPEN THEN
2748 CLOSE c_get_new_asset_loc;
2749 END IF;
2750 IF c_get_old_asset_party_loc%ISOPEN THEN
2751 CLOSE c_get_old_asset_party_loc;
2752 END IF;
2753 --cklee
2754 --Bug#4402325 start
2755 IF c_get_old_splt_asset_desc%ISOPEN THEN
2756 CLOSE c_get_old_splt_asset_desc;
2757 END IF;
2758 IF c_get_old_splt_asset_asc%ISOPEN THEN
2759 CLOSE c_get_old_splt_asset_asc;
2760 END IF;
2761
2762 --Bug#4402325 end
2763
2764
2765 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2766 l_api_name,
2767 G_PKG_NAME,
2768 'OTHERS',
2769 x_msg_count,
2770 x_msg_data,
2771 '_PVT');
2772 END generate_asset_summary;
2773
2774
2775 -- Start of comments
2776 --
2777 -- Procedure Name : update_contract_line
2778 -- Description : updates contract line
2779 -- Business Rules :
2780 -- Parameters :
2781 -- Version : 1.0
2782 -- End of comments
2783 PROCEDURE update_contract_line(
2784 p_api_version IN NUMBER,
2785 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2786 x_return_status OUT NOCOPY VARCHAR2,
2787 x_msg_count OUT NOCOPY NUMBER,
2788 x_msg_data OUT NOCOPY VARCHAR2,
2789 p_id IN NUMBER,
2790 p_date_delivery_expected IN DATE,
2791 p_date_funding_expected IN DATE,
2792 p_org_id IN NUMBER,
2793 p_organization_id IN NUMBER
2794 ) AS
2795
2796 l_api_name VARCHAR2(30) := 'update_contract_line';
2797 l_api_version CONSTANT NUMBER := 1.0;
2798
2799 lp_clev_rec okl_okc_migration_pvt.clev_rec_type;
2800 lp_klev_rec OKL_CONTRACT_PUB.klev_rec_type;
2801 lx_clev_rec okl_okc_migration_pvt.clev_rec_type;
2802 lx_klev_rec OKL_CONTRACT_PUB.klev_rec_type;
2803
2804 l_template_yn OKC_K_HEADERS_B.TEMPLATE_YN%TYPE;
2805 l_chr_type OKC_K_HEADERS_B.CHR_TYPE%TYPE;
2806 BEGIN
2807 /*
2808 OKL_CONTEXT.SET_OKL_ORG_CONTEXT(
2809 p_org_id => p_org_id,
2810 p_organization_id => p_organization_id);
2811 */
2812 x_return_status := OKC_API.START_ACTIVITY(
2813 p_api_name => l_api_name,
2814 p_pkg_name => g_pkg_name,
2815 p_init_msg_list => p_init_msg_list,
2816 l_api_version => l_api_version,
2817 p_api_version => p_api_version,
2818 p_api_type => g_api_type,
2819 x_return_status => x_return_status);
2820
2821 -- check if activity started successfully
2822 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2823 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2824 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2825 RAISE OKC_API.G_EXCEPTION_ERROR;
2826 END IF;
2827
2828 lp_clev_rec.id := p_id;
2829 lp_klev_rec.id := p_id;
2830 lp_klev_rec.date_delivery_expected := p_date_delivery_expected;
2831 lp_klev_rec.date_funding_expected := p_date_funding_expected;
2832
2833 OKL_CONTRACT_PUB.update_contract_line(
2834 p_api_version => p_api_version,
2835 p_init_msg_list => p_init_msg_list,
2836 x_return_status => x_return_status,
2837 x_msg_count => x_msg_count,
2838 x_msg_data => x_msg_data,
2839 p_clev_rec => lp_clev_rec,
2840 p_klev_rec => lp_klev_rec,
2841 p_edit_mode => 'N',
2842 x_clev_rec => lx_clev_rec,
2843 x_klev_rec => lx_klev_rec);
2844
2845 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2846 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2847 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2848 RAISE OKC_API.G_EXCEPTION_ERROR;
2849 END IF;
2850
2851 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2852 x_msg_data => x_msg_data);
2853 EXCEPTION
2854 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2855 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2856 p_api_name => l_api_name,
2857 p_pkg_name => g_pkg_name,
2858 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2859 x_msg_count => x_msg_count,
2860 x_msg_data => x_msg_data,
2861 p_api_type => g_api_type);
2862
2863 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2864 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2865 p_api_name => l_api_name,
2866 p_pkg_name => g_pkg_name,
2867 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2868 x_msg_count => x_msg_count,
2869 x_msg_data => x_msg_data,
2870 p_api_type => g_api_type);
2871
2872 WHEN OTHERS THEN
2873 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2874 p_api_name => l_api_name,
2875 p_pkg_name => g_pkg_name,
2876 p_exc_name => 'OTHERS',
2877 x_msg_count => x_msg_count,
2878 x_msg_data => x_msg_data,
2879 p_api_type => g_api_type);
2880 END;
2881
2882 -- Start of comments
2883 --
2884 -- Procedure Name : update_contract_line
2885 -- Description : updates contract line
2886 -- Business Rules :
2887 -- Parameters :
2888 -- Version : 1.0
2889 -- End of comments
2890 PROCEDURE update_contract_line(
2891 p_api_version IN NUMBER,
2892 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2893 x_return_status OUT NOCOPY VARCHAR2,
2894 x_msg_count OUT NOCOPY NUMBER,
2895 x_msg_data OUT NOCOPY VARCHAR2,
2896 p_id IN NUMBER,
2897 p_chr_id IN NUMBER,
2898 p_manufacturer_name IN VARCHAR2,
2899 p_model_number IN VARCHAR2,
2900 p_year_of_manufacture IN VARCHAR2,
2901 p_vendor_name IN VARCHAR2,
2902 p_vendor_id IN VARCHAR2,
2903 p_cpl_id IN NUMBER,
2904 p_notes IN VARCHAR2
2905 ) AS
2906
2907 l_api_name VARCHAR2(30) := 'update_contract_line';
2908 l_api_version CONSTANT NUMBER := 1.0;
2909
2910 lp_clev_rec okl_okc_migration_pvt.clev_rec_type;
2911 lp_klev_rec OKL_CONTRACT_PUB.klev_rec_type;
2912 lx_clev_rec okl_okc_migration_pvt.clev_rec_type;
2913 lx_klev_rec OKL_CONTRACT_PUB.klev_rec_type;
2914
2915 lp_vndr_cplv_rec OKL_OKC_MIGRATION_PVT.cplv_rec_type;
2916 lx_vndr_cplv_rec OKL_OKC_MIGRATION_PVT.cplv_rec_type;
2917
2918 l_template_yn OKC_K_HEADERS_B.TEMPLATE_YN%TYPE;
2919 l_chr_type OKC_K_HEADERS_B.CHR_TYPE%TYPE;
2920
2921 l_vendor_id NUMBER:= null;
2922 --start modifying abhsaxen cursor is no longer used
2923 -- CURSOR okx_vendor_id1_csr IS
2924 --end modifying abhsaxen cursor is no longer used
2925
2926 BEGIN
2927 /*
2928 OKL_CONTEXT.SET_OKL_ORG_CONTEXT(
2929 p_org_id => p_org_id,
2930 p_organization_id => p_organization_id);
2931 */
2932 x_return_status := OKC_API.START_ACTIVITY(
2933 p_api_name => l_api_name,
2934 p_pkg_name => g_pkg_name,
2935 p_init_msg_list => p_init_msg_list,
2936 l_api_version => l_api_version,
2937 p_api_version => p_api_version,
2938 p_api_type => g_api_type,
2939 x_return_status => x_return_status);
2940
2941 -- check if activity started successfully
2942 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2943 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2944 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2945 RAISE OKC_API.G_EXCEPTION_ERROR;
2946 END IF;
2947
2948 lp_clev_rec.id := p_id;
2949 lp_klev_rec.id := p_id;
2950 lp_klev_rec.manufacturer_name := p_manufacturer_name;
2951 lp_klev_rec.model_number := p_model_number;
2952 lp_klev_rec.year_of_manufacture := p_year_of_manufacture;
2953 lp_clev_rec.comments := p_notes;
2954
2955 OKL_CONTRACT_PUB.update_contract_line(
2956 p_api_version => p_api_version,
2957 p_init_msg_list => p_init_msg_list,
2958 x_return_status => x_return_status,
2959 x_msg_count => x_msg_count,
2960 x_msg_data => x_msg_data,
2961 p_clev_rec => lp_clev_rec,
2962 p_klev_rec => lp_klev_rec,
2963 p_edit_mode => 'N',
2964 x_clev_rec => lx_clev_rec,
2965 x_klev_rec => lx_klev_rec);
2966
2967 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2968 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2969 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2970 RAISE OKC_API.G_EXCEPTION_ERROR;
2971 END IF;
2972
2973
2974 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2975
2976 EXCEPTION
2977 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2978 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2979 p_api_name => l_api_name,
2980 p_pkg_name => g_pkg_name,
2981 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2982 x_msg_count => x_msg_count,
2983 x_msg_data => x_msg_data,
2984 p_api_type => g_api_type);
2985
2986 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2987 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2988 p_api_name => l_api_name,
2989 p_pkg_name => g_pkg_name,
2990 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2991 x_msg_count => x_msg_count,
2992 x_msg_data => x_msg_data,
2993 p_api_type => g_api_type);
2994
2995 WHEN OTHERS THEN
2996 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2997 p_api_name => l_api_name,
2998 p_pkg_name => g_pkg_name,
2999 p_exc_name => 'OTHERS',
3000 x_msg_count => x_msg_count,
3001 x_msg_data => x_msg_data,
3002 p_api_type => g_api_type);
3003 END;
3004
3005 -- Start of comments
3006 --
3007 -- Procedure Name : update_fin_cap_cost
3008 -- Description : updates fin cap cost
3009 -- Business Rules :
3010 -- Parameters :
3011 -- Version : 1.0
3012 -- End of comments
3013 PROCEDURE update_fin_cap_cost(
3014 p_api_version IN NUMBER,
3015 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3016 x_return_status OUT NOCOPY VARCHAR2,
3017 x_msg_count OUT NOCOPY NUMBER,
3018 x_msg_data OUT NOCOPY VARCHAR2,
3019 P_new_yn IN VARCHAR2,
3020 p_asset_number IN VARCHAR2,
3021 p_top_line_id IN NUMBER,
3022 p_dnz_chr_id IN NUMBER,
3023 p_capital_reduction IN NUMBER,
3024 p_capital_reduction_percent IN NUMBER,
3025 p_oec IN NUMBER,
3026 p_cap_down_pay_yn IN VARCHAR2,
3027 p_down_payment_receiver IN VARCHAR2
3028 ) AS
3029
3030 l_api_name VARCHAR2(30) := 'update_fin_cap_cost';
3031 l_api_version CONSTANT NUMBER := 1.0;
3032
3033 lp_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
3034 lp_klev_rec OKL_CONTRACT_PUB.klev_rec_type;
3035 lx_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
3036 lx_klev_rec OKL_CONTRACT_PUB.klev_rec_type;
3037
3038 --Bug#5495504
3039 CURSOR c_get_tradein_amt
3040 IS
3041 SELECT kle.tradein_amount
3042 FROM okl_k_lines kle
3043 WHERE kle.id = p_top_line_id;
3044
3045 BEGIN
3046 /*
3047 OKL_CONTEXT.SET_OKL_ORG_CONTEXT(
3048 p_org_id => p_org_id,
3049 p_organization_id => p_organization_id);
3050 */
3051 x_return_status := OKC_API.START_ACTIVITY(
3052 p_api_name => l_api_name,
3053 p_pkg_name => g_pkg_name,
3054 p_init_msg_list => p_init_msg_list,
3055 l_api_version => l_api_version,
3056 p_api_version => p_api_version,
3057 p_api_type => g_api_type,
3058 x_return_status => x_return_status);
3059
3060 -- check if activity started successfully
3061 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3062 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3063 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3064 RAISE OKC_API.G_EXCEPTION_ERROR;
3065 END IF;
3066
3067 lp_clev_rec.id := p_top_line_id;
3068 lp_clev_rec.line_number := '1';
3069 lp_clev_rec.dnz_chr_id := p_dnz_chr_id;
3070 lp_clev_rec.display_sequence := 1;
3071 lp_clev_rec.exception_yn := 'N';
3072
3073 lp_klev_rec.id := p_top_line_id;
3074 lp_klev_rec.capital_reduction := p_capital_reduction;
3075 lp_klev_rec.capital_reduction_percent := p_capital_reduction_percent;
3076 lp_klev_rec.oec := p_oec;
3077 lp_klev_rec.tradein_amount := null;
3078 lp_klev_rec.CAPITALIZE_DOWN_PAYMENT_YN := p_cap_down_pay_yn;
3079 lp_klev_rec.DOWN_PAYMENT_RECEIVER_CODE := p_down_payment_receiver;
3080
3081 --Bug#5495504
3082 open c_get_tradein_amt;
3083 fetch c_get_tradein_amt into lp_klev_rec.tradein_amount;
3084 close c_get_tradein_amt;
3085
3086 IF((p_down_payment_receiver IS NOT NULL) AND
3087 (p_down_payment_receiver = 'VENDOR' OR p_down_payment_receiver = 'LESSOR')) THEN
3088 IF(p_cap_down_pay_yn IS NOT NULL AND p_cap_down_pay_yn = 'N' AND p_down_payment_receiver = 'VENDOR') THEN
3089 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
3090 p_msg_name => 'OKL_INVALID_COMBINATION');
3091 x_return_status := OKC_API.g_ret_sts_error;
3092 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3093 END IF;
3094 END IF;
3095
3096 OKL_CREATE_KLE_PUB.update_fin_cap_cost(
3097 p_api_version => p_api_version,
3098 p_init_msg_list => p_init_msg_list,
3099 x_return_status => x_return_status,
3100 x_msg_count => x_msg_count,
3101 x_msg_data => x_msg_data,
3102 p_new_yn => p_new_yn,
3103 p_asset_number => p_asset_number,
3104 p_clev_rec => lp_clev_rec,
3105 p_klev_rec => lp_klev_rec,
3106 x_clev_rec => lx_clev_rec,
3107 x_klev_rec => lx_klev_rec);
3108
3109 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3110 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3111 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3112 RAISE OKC_API.G_EXCEPTION_ERROR;
3113 END IF;
3114
3115 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
3116
3117 EXCEPTION
3118 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3119 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3120 p_api_name => l_api_name,
3121 p_pkg_name => g_pkg_name,
3122 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3123 x_msg_count => x_msg_count,
3124 x_msg_data => x_msg_data,
3125 p_api_type => g_api_type);
3126
3127 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3128 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3129 p_api_name => l_api_name,
3130 p_pkg_name => g_pkg_name,
3131 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3132 x_msg_count => x_msg_count,
3133 x_msg_data => x_msg_data,
3134 p_api_type => g_api_type);
3135
3136 WHEN OTHERS THEN
3137 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3138 p_api_name => l_api_name,
3139 p_pkg_name => g_pkg_name,
3140 p_exc_name => 'OTHERS',
3141 x_msg_count => x_msg_count,
3142 x_msg_data => x_msg_data,
3143 p_api_type => g_api_type);
3144 END;
3145
3146 PROCEDURE update_fin_cap_cost(
3147 p_api_version IN NUMBER,
3148 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3149 x_return_status OUT NOCOPY VARCHAR2,
3150 x_msg_count OUT NOCOPY NUMBER,
3151 x_msg_data OUT NOCOPY VARCHAR2,
3152 p_fin_adj_tbl IN fin_adj_tbl_type
3153 ) AS
3154
3155 l_api_name VARCHAR2(30) := 'update_fin_cap_cost_tbl';
3156 l_api_version CONSTANT NUMBER := 1.0;
3157 l_fin_adj_rec fin_adj_rec_type;
3158 lp_asset_number VARCHAR2(50);
3159 lp_new_yn VARCHAR2(10);
3160
3161 BEGIN
3162 x_return_status := OKC_API.START_ACTIVITY(
3163 p_api_name => l_api_name,
3164 p_pkg_name => g_pkg_name,
3165 p_init_msg_list => p_init_msg_list,
3166 l_api_version => l_api_version,
3167 p_api_version => p_api_version,
3168 p_api_type => g_api_type,
3169 x_return_status => x_return_status);
3170
3171 -- check if activity started successfully
3172 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3173 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3174 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3175 RAISE OKC_API.G_EXCEPTION_ERROR;
3176 END IF;
3177
3178 FOR l_fin_adj_rec_index IN p_fin_adj_tbl.FIRST .. p_fin_adj_tbl.LAST
3179 LOOP
3180 lp_asset_number := NULL;
3181 lp_new_yn := NULL;
3182 l_fin_adj_rec := NULL;
3183 l_fin_adj_rec := p_fin_adj_tbl(l_fin_adj_rec_index);
3184 lp_asset_number := l_fin_adj_rec.p_asset_number;
3185 lp_new_yn := l_fin_adj_rec.p_new_yn;
3186
3187 IF((l_fin_adj_rec.p_down_payment_receiver IS NOT NULL) AND
3188 (l_fin_adj_rec.p_down_payment_receiver = 'VENDOR' OR l_fin_adj_rec.p_down_payment_receiver = 'LESSOR')) THEN
3189 IF(l_fin_adj_rec.p_cap_down_pay_yn IS NOT NULL AND l_fin_adj_rec.p_cap_down_pay_yn = 'N' AND l_fin_adj_rec.p_down_payment_receiver = 'VENDOR') THEN
3190 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
3191 p_msg_name => 'OKL_INVALID_COMBINATION');
3192 x_return_status := OKC_API.g_ret_sts_error;
3193 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3194 END IF;
3195 END IF;
3196
3197 update_fin_cap_cost(
3198 p_api_version =>p_api_version,
3199 p_init_msg_list => p_init_msg_list,
3200 x_return_status => x_return_status,
3201 x_msg_count => x_msg_count,
3202 x_msg_data => x_msg_data,
3203 P_new_yn => lp_new_yn,
3204 p_asset_number => lp_asset_number,
3205 p_top_line_id => l_fin_adj_rec.p_top_line_id,
3206 p_dnz_chr_id => l_fin_adj_rec.p_dnz_chr_id,
3207 p_capital_reduction => l_fin_adj_rec.p_capital_reduction,
3208 p_capital_reduction_percent => l_fin_adj_rec.p_capital_reduction_percent,
3209 p_oec => l_fin_adj_rec.p_oec,
3210 p_cap_down_pay_yn => l_fin_adj_rec.p_cap_down_pay_yn,
3211 p_down_payment_receiver => l_fin_adj_rec.p_down_payment_receiver);
3212
3213 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3214 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3215 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3216 RAISE OKC_API.G_EXCEPTION_ERROR;
3217 END IF;
3218
3219 END LOOP;
3220 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
3221 EXCEPTION
3222 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3223 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3224 p_api_name => l_api_name,
3225 p_pkg_name => g_pkg_name,
3226 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3227 x_msg_count => x_msg_count,
3228 x_msg_data => x_msg_data,
3229 p_api_type => g_api_type);
3230
3231 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3232 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3233 p_api_name => l_api_name,
3234 p_pkg_name => g_pkg_name,
3235 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3236 x_msg_count => x_msg_count,
3237 x_msg_data => x_msg_data,
3238 p_api_type => g_api_type);
3239
3240 WHEN OTHERS THEN
3241 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3242 p_api_name => l_api_name,
3243 p_pkg_name => g_pkg_name,
3244 p_exc_name => 'OTHERS',
3245 x_msg_count => x_msg_count,
3246 x_msg_data => x_msg_data,
3247 p_api_type => g_api_type);
3248 END;
3249
3250 End OKL_LA_ASSET_PVT;