[Home] [Help]
PACKAGE BODY: APPS.OKL_CONTRACT_LINE_ITEM_PVT
Source
1 package body okl_contract_line_item_pvt as
2 /* $Header: OKLRCLIB.pls 120.26 2007/10/31 04:53:18 rpillay noship $ */
3
4 -- Start of comments
5 --
6 -- Procedure Name : create_contract_line
7 -- Description : creates contract line for shadowed contract
8 -- Business Rules :
9 -- Parameters :
10 -- Version : 1.0
11 -- End of comments
12
13 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
14
15 /*
16 -- vthiruva, 09/01/2004
17 -- Added Constants to enable Business Event
18 */
19 G_WF_EVT_ASSET_FEE_REMOVED CONSTANT VARCHAR2(60) := 'oracle.apps.okl.la.lease_contract.remove_asset_fee';
20 G_WF_ITM_CONTRACT_ID CONSTANT VARCHAR2(20) := 'CONTRACT_ID';
21 G_WF_ITM_FEE_LINE_ID CONSTANT VARCHAR2(20) := 'FEE_LINE_ID';
22 G_WF_ITM_ASSET_ID CONSTANT VARCHAR2(20) := 'ASSET_ID';
23
24 /*
25 * sjalasut: aug 18, 04 added constants used in raising business event. BEGIN
26 */
27 G_WF_EVT_ASSET_SERV_FEE_RMVD CONSTANT VARCHAR2(65) := 'oracle.apps.okl.la.lease_contract.remove_asset_service_fee';
28 G_WF_ITM_SERV_LINE_ID CONSTANT VARCHAR2(30) := 'SERVICE_LINE_ID';
29 G_WF_ITM_SERV_CHR_ID CONSTANT VARCHAR2(30) := 'SERVICE_CONTRACT_ID';
30 G_WF_ITM_SERV_CLE_ID CONSTANT VARCHAR2(30) := 'SERVICE_CONTRACT_LINE_ID';
31 G_WF_ITM_CONTRACT_PROCESS CONSTANT VARCHAR2(30) := 'CONTRACT_PROCESS';
32 /*
33 * sjalasut: aug 18, 04 added constants used in raising business event. END
34 */
35
36 --For Object code 'OKL_STRMTYP'
37
38 CURSOR okl_strmtyp_csr(p_name VARCHAR2,p_id1 VARCHAR2 ,p_id2 VARCHAR2) IS
39 SELECT ssv.id1,
40 ssv.id2,
41 ssv.name,
42 ssv.description
43 FROM okl_strmtyp_source_v ssv
44 WHERE ssv.status = 'A'
45 AND ssv.name = NVL(p_name,ssv.name)
46 AND ssv.id1 = NVL(p_id1,ssv.id1)
47 AND ssv.id2 = NVL(p_id2,ssv.id2)
48 ORDER BY ssv.name;
49
50 -- FOr Object COde 'OKL_USAGE'
51
52 CURSOR okl_usage_csr(p_name VARCHAR2,p_id1 VARCHAR2 ,p_id2 VARCHAR2,p_chr_id NUMBER) IS
53 select cle.id ID1,
54 '#' ID2,
55 tl.name NAME,
56 tl.item_description DESCRIPTION
57 from OKC_K_LINES_B CLE,
58 OKC_K_LINES_TL TL,
59 OKC_LINE_STYLES_B LSE,
60 OKC_K_HEADERS_B CHR
61 where
62 cle.lse_id = lse.id
63 and lse.lty_code = 'USAGE'
64 and cle.chr_id = chr.id
65 and tl.id = cle.id
66 and tl.language = userenv('LANG')
67 and chr.scs_code = 'SERVICE'
68 and tl.name = nvl(p_name,tl.name)
69 and cle.id = nvl(p_id1,cle.id )
70 and '#' = nvl(p_id2,'#')
71 and cle.dnz_chr_id=p_chr_id
72 order by tl.name;
73
74 /* commented for performance issue bug#5484903
75 SELECT ulv.id1,
76 ulv.id2,
77 ulv.name,
78 ulv.description
79 FROM okl_usage_lines_v ulv
80 WHERE ulv.name = NVL(p_name,ulv.name)
81 AND ulv.id1 = NVL(p_id1,ulv.id1)
82 AND ulv.id2 = NVL(p_id2,ulv.id2)
83 ORDER BY ulv.name; */
84
85 -- For Object Code 'OKX_ASSET'
86
87 CURSOR okx_asset_csr(p_name VARCHAR2,p_id1 VARCHAR2 ,p_id2 VARCHAR2) IS
88 SELECT asv.id1,
89 asv.id2,
90 asv.name,
91 asv.description
92 FROM okx_assets_v asv
93 WHERE asv.name = p_name --for performance issue bug#5484903
94 AND asv.id1 = NVL(p_id1,asv.id1)
95 AND asv.id2 = NVL(p_id2,asv.id2)
96 ORDER BY asv.name;
97
98 -- For Object Code 'OKX_COVASST'
99 -- Updated the cursor for performance fix bug#5484903
100 CURSOR okx_covasst_csr(p_dnz_chr_id NUMBER,p_name VARCHAR2,p_id1 VARCHAR2 ,p_id2 VARCHAR2) IS
101 SELECT cas.id1,
102 cas.id2,
103 cas.name,
104 cas.description
105 FROM OKX_COVERED_ASSET_V cas
106 WHERE cas.okc_line_status NOT IN ('EXPIRED','TERMINATED','CANCELLED','ABANDONED')
107 AND cas.dnz_chr_id = p_dnz_chr_id -- included for bug#5484903
108 AND cas.name = NVL(p_name,cas.name)
109 AND cas.id1 = NVL(p_id1,cas.id1)
110 AND cas.id2 = NVL(p_id2,cas.id2)
111 ORDER BY cas.name;
112
113 -- For Object Code 'OKX_IB_ITEM'
114
115 CURSOR okx_ib_item_csr(p_name VARCHAR2,p_id1 VARCHAR2 ,p_id2 VARCHAR2) IS
116 SELECT itv.id1,
117 itv.id2,
118 itv.name,
119 itv.description
120 FROM OKX_INSTALL_ITEMS_V itv
121 WHERE itv.name =p_name --for performance issue bug#5484903
122 AND itv.id1 = NVL(p_id1,itv.id1)
123 AND itv.id2 = NVL(p_id2,itv.id2)
124 ORDER BY itv.name;
125
126
127
128
129 -- For Object Code 'OKX_LEASE'
130 -- removed the cursor okx_lease_csr for try_code 'SHARED'
131 --since it is not needed --fixed as part of the performance issue bug#5484903
132
133 -- For Object Code 'OKX_SERVICE'
134
135 CURSOR okx_service_csr(p_name VARCHAR2,p_id1 VARCHAR2 ,p_id2 VARCHAR2) IS
136 SELECT syi.id1,
137 syi.id2,
138 syi.name,
139 syi.description
140 FROM OKX_SYSTEM_ITEMS_V syi
141 WHERE syi.VENDOR_WARRANTY_FLAG='N'
142 AND syi.SERVICE_ITEM_FLAG='Y'
143 AND syi.ORGANIZATION_ID = SYS_CONTEXT('OKC_CONTEXT','ORGANIZATION_ID')
144 AND syi.name = NVL(p_name,syi.name)
145 AND syi.id1 = NVL(p_id1,syi.id1)
146 AND syi.id2 = NVL(p_id2,syi.id2)
147 ORDER BY syi.NAME;
148
149 -- For Object Code 'OKX_SYSITEM'
150
151 CURSOR okx_sysitem_csr(p_name VARCHAR2,p_id1 VARCHAR2 ,p_id2 VARCHAR2) IS
152 SELECT syi.id1,
153 syi.id2,
154 syi.name,
155 syi.description
156 FROM OKX_SYSTEM_ITEMS_V syi
157 WHERE syi.ORGANIZATION_ID = SYS_CONTEXT('OKC_CONTEXT','ORGANIZATION_ID')
158 AND syi.name = NVL(p_name,syi.name)
159 AND syi.id1 = NVL(p_id1,syi.id1)
160 AND syi.id2 = NVL(p_id2,syi.id2)
161 ORDER BY syi.NAME;
162
163 /*
164 -- vthiruva, 09/01/2004
165 -- START, Added PROCEDURE to enable Business Event
166 */
167 -- Start of comments
168 --
169 -- Procedure Name : raise_business_event
170 -- Description : local_procedure, raises business event by making a call to
171 -- okl_wf_pvt.raise_event
172 -- Business Rules :
173 -- Parameters :
174 -- Version : 1.0
175 -- End of comments
176 --
177 PROCEDURE raise_business_event(
178 p_api_version IN NUMBER,
179 p_init_msg_list IN VARCHAR2,
180 x_return_status OUT NOCOPY VARCHAR2,
181 x_msg_count OUT NOCOPY NUMBER,
182 x_msg_data OUT NOCOPY VARCHAR2,
183 p_event_name IN WF_EVENTS.NAME%TYPE,
184 p_event_param_list IN WF_PARAMETER_LIST_T) IS
185
186 l_event_parameter_list wf_parameter_list_t := p_event_param_list;
187 l_contract_process VARCHAR2(20);
188 BEGIN
189 x_return_status := OKL_API.G_RET_STS_SUCCESS;
190 -- wrapper API to get contract process. this API determines in which status the
191 -- contract in question is.
192 l_contract_process := okl_lla_util_pvt.get_contract_process(
193 p_chr_id => wf_event.GetValueForParameter(G_WF_ITM_CONTRACT_ID, l_event_parameter_list)
194 );
195 -- add the contract status to the event parameter list
196 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_PROCESS, l_contract_process, l_event_parameter_list);
197
198 OKL_WF_PVT.raise_event(p_api_version => p_api_version,
199 p_init_msg_list => p_init_msg_list,
200 x_return_status => x_return_status,
201 x_msg_count => x_msg_count,
202 x_msg_data => x_msg_data,
203 p_event_name => p_event_name,
204 p_parameters => l_event_parameter_list);
205
206 EXCEPTION
207 WHEN OTHERS THEN
208 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
209 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
210 END raise_business_event;
211
212 /*
213 -- vthiruva, 09/01/2004
214 -- END, PROCEDURE to enable Business Event
215 */
216
217 FUNCTION GET_AK_PROMPT(p_ak_region IN VARCHAR2, p_ak_attribute IN VARCHAR2)
218 RETURN VARCHAR2 IS
219
220 CURSOR ak_prompt_csr(p_ak_region VARCHAR2, p_ak_attribute VARCHAR2) IS
221
222 -- Changed the sql for performance issue #5484903
223 Select AAT.attribute_label_long
224 FROM FND_APPLICATION_VL FAV ,
225 AK_ATTRIBUTES_TL AAT ,
226 AK_ATTRIBUTES AA
227 WHERE AA.ATTRIBUTE_APPLICATION_ID = AAT.ATTRIBUTE_APPLICATION_ID
228 AND AA.ATTRIBUTE_CODE = AAT.ATTRIBUTE_CODE
229 AND AAT.LANGUAGE = USERENV('LANG')
230 AND AA.ATTRIBUTE_APPLICATION_ID = FAV.APPLICATION_ID
231 and aa.attribute_code = p_ak_attribute
232 and exists (select 'x' from ak_regions r
233 where r.region_code = p_ak_region)
234 and exists (select 'x' from ak_region_items ri
235 where ri.region_code = p_ak_region
236 and ri.attribute_code = aa.attribute_code );
237
238 /* --commented for performance issue bug#5484903
239 SELECT a.attribute_label_long
240 FROM ak_region_items ri, AK_REGIONS r, AK_ATTRIBUTES_vL a
241 WHERE ri.region_code = r.region_code
242 AND ri.attribute_code = a.attribute_code
243 AND ri.region_code = p_ak_region
244 AND ri.attribute_code = p_ak_attribute; */
245
246 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
247 BEGIN
248 OPEN ak_prompt_csr(p_ak_region, p_ak_attribute);
249 FETCH ak_prompt_csr INTO l_ak_prompt;
250 CLOSE ak_prompt_csr;
251 return(l_ak_prompt);
252 END;
253
254 --Start of Comments
255 --Procedure : Validate_Item
256 --Description : Returns Name, Description for a given role or all the roles
257 -- attached to a contract
258 --End of Comments
259 Procedure Validate_Link_Asset (p_api_version IN NUMBER,
260 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
261 x_return_status OUT NOCOPY VARCHAR2,
262 x_msg_count OUT NOCOPY NUMBER,
263 x_msg_data OUT NOCOPY VARCHAR2,
264 p_chr_id IN NUMBER,
265 p_parent_cle_id IN NUMBER,
266 p_id1 IN OUT NOCOPY VARCHAR2,
267 p_id2 IN OUT NOCOPY VARCHAR2,
268 p_name IN VARCHAR2,
269 p_object_code IN VARCHAR2
270 ) is
271 l_select_clause varchar2(2000) default null;
272 l_from_clause varchar2(2000) default null;
273 l_where_clause varchar2(2000) default null;
274 l_order_by_clause varchar2(2000) default null;
275 l_query_string varchar2(2000) default null;
276
277 l_id1 OKC_K_ITEMS_V.OBJECT1_ID1%TYPE default Null;
278 l_id2 OKC_K_ITEMS_V.OBJECT1_ID2%TYPE default Null;
279 l_name VARCHAR2(250) Default Null;
280 l_description VARCHAR2(250) Default Null;
281 l_object_code VARCHAR2(30) Default Null;
282
283 l_id11 OKC_K_PARTY_ROLES_V.OBJECT1_ID1%TYPE default Null;
284 l_id22 OKC_K_PARTY_ROLES_V.OBJECT1_ID2%TYPE default Null;
285
286 type item_curs_type is REF CURSOR;
287 item_curs item_curs_type;
288
289 row_count Number default 0;
290
291 l_chr_id okl_k_headers.id%type;
292 l_parent_cle_id okl_k_lines.id%type;
293 l_cle_id okl_k_lines.id%type;
294 l_lty_code okc_line_styles_b.lty_code%type;
295 l_id okl_k_lines.id%type;
296
297 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Link_Asset';
298 l_api_version CONSTANT NUMBER := 1.0;
299 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
300
301 CURSOR get_item_csr(p_chr_id NUMBER, p_parent_cle_id NUMBER, p_link_ast_id1 VARCHAR2, p_link_ast_id2 VARCHAR2) IS
302 SELECT link_ast.id
303 from okl_k_lines_full_v sub_line, okc_k_items_v link_ast
304 where sub_line.id = link_ast.cle_id
305 and link_ast.object1_id1 = p_link_ast_id1
306 and link_ast.object1_id2 = p_link_ast_id2
307 and sub_line.cle_id = p_parent_cle_id
308 and sub_line.dnz_chr_id = p_chr_id;
309
310 -- Cursor to fetch the lty_code
311 CURSOR get_lty_code_csr(p_chr_id NUMBER,
312 p_cle_id NUMBER) IS
313 SELECT lty_code
314 FROM okc_line_styles_b lse,
315 okc_k_lines_b cle
316 WHERE lse.lse_parent_id = cle.lse_id
317 AND cle.id = p_cle_id
318 AND cle.chr_id = p_chr_id;
319
320
321 Begin
322
323 l_chr_id := p_chr_id;
324 If okl_context.get_okc_org_id is null then
325 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
326 End If;
327
328 If ( p_chr_id is null or p_chr_id = OKC_API.G_MISS_NUM)
329 Then
330 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
331 ElsIf ( p_parent_cle_id is null or p_parent_cle_id = OKC_API.G_MISS_NUM)
332 Then
333 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
334 ElsIf ( p_name is null or p_name = OKC_API.G_MISS_CHAR)
335 Then
336 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
337 End If;
338
339 OPEN get_lty_code_csr(p_chr_id => p_chr_id,
340 p_cle_id => p_parent_cle_id );
341
342 FETCH get_lty_code_csr INTO l_lty_code;
343
344 If get_lty_code_csr%NotFound Then
345
346 x_return_status := OKC_API.g_ret_sts_error;
347 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED');
348 raise OKC_API.G_EXCEPTION_ERROR;
349
350 End If;
351
352 CLOSE get_lty_code_csr;
353
354 --Added by kthiruva 23-Sep-2003 Bug No.3156265
355
356 --For Object code 'OKL_STRMTYP'
357
358 IF (l_lty_code = 'FEE') THEN
359
360 OPEN okl_strmtyp_csr(p_name => p_name,
361 p_id1 => l_id1,
362 p_id2 => l_id2);
363
364
365 l_id1 := Null;
366 l_id2 := Null;
367 l_name := Null;
368 l_description := Null;
369
370 FETCH okl_strmtyp_csr into l_id1,l_id2,l_name,l_description;
371
372 If okl_strmtyp_csr%NotFound Then
373 x_return_status := OKC_API.g_ret_sts_error;
374 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED');
375 raise OKC_API.G_EXCEPTION_ERROR;
376 End If;
377
378 l_id11 := l_id1;
379 l_id22 := l_id2;
380
381 FETCH okl_strmtyp_csr into l_id1,l_id2,l_name,l_description;
382 If okl_strmtyp_csr%Found Then
383
384 If( p_id1 is null or p_id1 = OKC_API.G_MISS_CHAR) then
385 x_return_status := OKC_API.g_ret_sts_error;
386 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset from lov
387 raise OKC_API.G_EXCEPTION_ERROR;
388 End If;
389
390 If( p_id2 is null or p_id2 = OKC_API.G_MISS_CHAR) then
391 x_return_status := OKC_API.g_ret_sts_error;
392 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset from lov
393 raise OKC_API.G_EXCEPTION_ERROR;
394 End If;
395
396 Loop
397 If(l_id1 = p_id1 and l_id2 = p_id2) Then
398 l_id11 := l_id1;
399 l_id22 := l_id2;
400 row_count := 1;
401 Exit;
402 End If;
403 Fetch okl_strmtyp_csr into l_id1,l_id2,l_name,l_description;
404 Exit When okl_strmtyp_csr%NotFound;
405 End Loop;
406
407 If row_count <> 1 Then
408 x_return_status := OKC_API.g_ret_sts_error;
409 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset
410 raise OKC_API.G_EXCEPTION_ERROR;
411 End If;
412
413 End If;
414
415 p_id1 := l_id11;
416 p_id2 := l_id22;
417
418 CLOSE okl_strmtyp_csr;
419 END IF;
420
421 -- For Object Code 'OKL_USAGE'
422
423 IF (l_lty_code = 'USAGE') THEN
424
425
426 OPEN okl_usage_csr(p_name => p_name,
427 p_id1 => l_id1,
428 p_id2 => l_id2,
429 p_chr_id=>p_chr_id); -- added for performance issue bug#5484903
430
431
432 l_id1 := Null;
433 l_id2 := Null;
434 l_name := Null;
435 l_description := Null;
436
437 FETCH okl_usage_csr into l_id1,l_id2,l_name,l_description;
438
439 If okl_usage_csr%NotFound Then
440 x_return_status := OKC_API.g_ret_sts_error;
441 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED');
442 raise OKC_API.G_EXCEPTION_ERROR;
443 End If;
444
445 l_id11 := l_id1;
446 l_id22 := l_id2;
447
448 FETCH okl_usage_csr into l_id1,l_id2,l_name,l_description;
449 If okl_usage_csr%Found Then
450
451 If( p_id1 is null or p_id1 = OKC_API.G_MISS_CHAR) then
452 x_return_status := OKC_API.g_ret_sts_error;
453 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset from lov
454 raise OKC_API.G_EXCEPTION_ERROR;
455 End If;
456
457 If( p_id2 is null or p_id2 = OKC_API.G_MISS_CHAR) then
458 x_return_status := OKC_API.g_ret_sts_error;
459 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset from lov
460 raise OKC_API.G_EXCEPTION_ERROR;
461 End If;
462
463 Loop
464 If(l_id1 = p_id1 and l_id2 = p_id2) Then
465 l_id11 := l_id1;
466 l_id22 := l_id2;
467 row_count := 1;
468 Exit;
469 End If;
470 Fetch okl_usage_csr into l_id1,l_id2,l_name,l_description;
471 Exit When okl_usage_csr%NotFound;
472 End Loop;
473
474 If row_count <> 1 Then
475 x_return_status := OKC_API.g_ret_sts_error;
476 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset
477 raise OKC_API.G_EXCEPTION_ERROR;
478 End If;
479
480 End If;
481
482 p_id1 := l_id11;
483 p_id2 := l_id22;
484
485 CLOSE okl_usage_csr;
486
487 END IF;
488
489 -- For Object Code ' OKX_ASSET '
490
491 IF (l_lty_code = 'FIXED_ASSET') THEN
492
493 OPEN okx_asset_csr(p_name => p_name,
494 p_id1 => l_id1,
495 p_id2 => l_id2);
496
497 l_id1 := Null;
498 l_id2 := Null;
499 l_name := Null;
500 l_description := Null;
501
502 FETCH okx_asset_csr into l_id1,l_id2,l_name,l_description;
503
504 If okx_asset_csr%NotFound Then
505 x_return_status := OKC_API.g_ret_sts_error;
506 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED');
507 raise OKC_API.G_EXCEPTION_ERROR;
508 End If;
509
510 l_id11 := l_id1;
511 l_id22 := l_id2;
512
513 FETCH okx_asset_csr into l_id1,l_id2,l_name,l_description;
514 If okx_asset_csr%Found Then
515
516 If( p_id1 is null or p_id1 = OKC_API.G_MISS_CHAR) then
517 x_return_status := OKC_API.g_ret_sts_error;
518 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset from lov
519 raise OKC_API.G_EXCEPTION_ERROR;
520 End If;
521
522 If( p_id2 is null or p_id2 = OKC_API.G_MISS_CHAR) then
523 x_return_status := OKC_API.g_ret_sts_error;
524 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset from lov
525 raise OKC_API.G_EXCEPTION_ERROR;
526 End If;
527
528 Loop
529 If(l_id1 = p_id1 and l_id2 = p_id2) Then
530 l_id11 := l_id1;
531 l_id22 := l_id2;
532 row_count := 1;
533 Exit;
534 End If;
535 Fetch okx_asset_csr into l_id1,l_id2,l_name,l_description;
536 Exit When okx_asset_csr%NotFound;
537 End Loop;
538
539 If row_count <> 1 Then
540 x_return_status := OKC_API.g_ret_sts_error;
541 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset
542 raise OKC_API.G_EXCEPTION_ERROR;
543 End If;
544
545 End If;
546
547 p_id1 := l_id11;
548 p_id2 := l_id22;
549
550 CLOSE okx_asset_csr;
551
552 END IF;
553
554 -- For Object Code 'OKX_COVASST'
555
556 -- IF (l_lty_code ='LINK_SERV_ASSET' OR l_lty_code = 'LINK_USAGE_ASSET') THEN
557 IF (l_lty_code = 'LINK_USAGE_ASSET') THEN
558
559
560 OPEN okx_covasst_csr(p_dnz_chr_id => p_chr_id,
561 p_name => p_name,
562 p_id1 => l_id1,
563 p_id2 => l_id2);
564
565 l_id1 := Null;
566 l_id2 := Null;
567 l_name := Null;
568 l_description := Null;
569
570 FETCH okx_covasst_csr into l_id1,l_id2,l_name,l_description;
571
572 If okx_covasst_csr%NotFound Then
573 x_return_status := OKC_API.g_ret_sts_error;
574 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED');
575 raise OKC_API.G_EXCEPTION_ERROR;
576 End If;
577
578 l_id11 := l_id1;
579 l_id22 := l_id2;
580
581 FETCH okx_covasst_csr into l_id1,l_id2,l_name,l_description;
582 If okx_covasst_csr%Found Then
583
584 If( p_id1 is null or p_id1 = OKC_API.G_MISS_CHAR) then
585 x_return_status := OKC_API.g_ret_sts_error;
586 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset from lov
587 raise OKC_API.G_EXCEPTION_ERROR;
588 End If;
589
590 If( p_id2 is null or p_id2 = OKC_API.G_MISS_CHAR) then
591 x_return_status := OKC_API.g_ret_sts_error;
592 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset from lov
593 raise OKC_API.G_EXCEPTION_ERROR;
594 End If;
595
596 Loop
597 If(l_id1 = p_id1 and l_id2 = p_id2) Then
598 l_id11 := l_id1;
599 l_id22 := l_id2;
600 row_count := 1;
601 Exit;
602 End If;
603 Fetch okx_covasst_csr into l_id1,l_id2,l_name,l_description;
604 Exit When okx_covasst_csr%NotFound;
605 End Loop;
606
607 If row_count <> 1 Then
608 x_return_status := OKC_API.g_ret_sts_error;
609 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset
610 raise OKC_API.G_EXCEPTION_ERROR;
611 End If;
612
613 End If;
614
615 p_id1 := l_id11;
616 p_id2 := l_id22;
617
618 CLOSE okx_covasst_csr;
619
620 END IF;
621
622 -- For Object Code 'OKX_IB_ITEM'
623
624 IF (l_lty_code = 'INST_ITEM') THEN
625
626 OPEN okx_ib_item_csr(p_name => p_name,
627 p_id1 => l_id1,
628 p_id2 => l_id2);
629
630 l_id1 := Null;
631 l_id2 := Null;
632 l_name := Null;
633 l_description := Null;
634
635 FETCH okx_ib_item_csr into l_id1,l_id2,l_name,l_description;
636
637 If okx_ib_item_csr%NotFound Then
638 x_return_status := OKC_API.g_ret_sts_error;
639 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED');
640 raise OKC_API.G_EXCEPTION_ERROR;
641 End If;
642
643 l_id11 := l_id1;
644 l_id22 := l_id2;
645
646 FETCH okx_ib_item_csr into l_id1,l_id2,l_name,l_description;
647 If okx_ib_item_csr%Found Then
648
649 If( p_id1 is null or p_id1 = OKC_API.G_MISS_CHAR) then
650 x_return_status := OKC_API.g_ret_sts_error;
651 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset from lov
652 raise OKC_API.G_EXCEPTION_ERROR;
653 End If;
654
655 If( p_id2 is null or p_id2 = OKC_API.G_MISS_CHAR) then
656 x_return_status := OKC_API.g_ret_sts_error;
657 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset from lov
658 raise OKC_API.G_EXCEPTION_ERROR;
659 End If;
660
661 Loop
662 If(l_id1 = p_id1 and l_id2 = p_id2) Then
663 l_id11 := l_id1;
664 l_id22 := l_id2;
665 row_count := 1;
666 Exit;
667 End If;
668 Fetch okx_ib_item_csr into l_id1,l_id2,l_name,l_description;
669 Exit When okx_ib_item_csr%NotFound;
670 End Loop;
671
672 If row_count <> 1 Then
673 x_return_status := OKC_API.g_ret_sts_error;
674 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset
675 raise OKC_API.G_EXCEPTION_ERROR;
676 End If;
677
678 End If;
679
680 p_id1 := l_id11;
681 p_id2 := l_id22;
682
683 CLOSE okx_ib_item_csr;
684
685 END IF;
686
687 -- For Object Code 'OKX_LEASE'
688 -- Removed the code for -- IF (l_lty_code = 'SHARED') THEN -- since it is not needed anymore.
689 -- pls. refer the earlier version of this file from ARCS for referrence.
690 -- Fixed as part of performance bug#5484903-- varangan - 25-9-06
691
692
693
694 -- For Object Code 'OKX_SERVICE'
695
696 IF (l_lty_code ='SOLD_SERVICE') THEN
697
698 OPEN okx_service_csr(p_name => p_name,
699 p_id1 => l_id1,
700 p_id2 => l_id2 );
701
702 l_id1 := Null;
703 l_id2 := Null;
704 l_name := Null;
705 l_description := Null;
706
707 FETCH okx_service_csr into l_id1,l_id2,l_name,l_description;
708
709 If okx_service_csr%NotFound Then
710 x_return_status := OKC_API.g_ret_sts_error;
711 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED');
712 raise OKC_API.G_EXCEPTION_ERROR;
713 End If;
714
715 l_id11 := l_id1;
716 l_id22 := l_id2;
717
718 FETCH okx_service_csr into l_id1,l_id2,l_name,l_description;
719 If okx_service_csr%Found Then
720
721 If( p_id1 is null or p_id1 = OKC_API.G_MISS_CHAR) then
722 x_return_status := OKC_API.g_ret_sts_error;
723 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset from lov
724 raise OKC_API.G_EXCEPTION_ERROR;
725 End If;
726
727 If( p_id2 is null or p_id2 = OKC_API.G_MISS_CHAR) then
728 x_return_status := OKC_API.g_ret_sts_error;
729 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset from lov
730 raise OKC_API.G_EXCEPTION_ERROR;
731 End If;
732
733 Loop
734 If(l_id1 = p_id1 and l_id2 = p_id2) Then
735 l_id11 := l_id1;
736 l_id22 := l_id2;
737 row_count := 1;
738 Exit;
739 End If;
740 Fetch okx_service_csr into l_id1,l_id2,l_name,l_description;
741 Exit When okx_service_csr%NotFound;
742 End Loop;
743
744 If row_count <> 1 Then
745 x_return_status := OKC_API.g_ret_sts_error;
746 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset
747 raise OKC_API.G_EXCEPTION_ERROR;
748 End If;
749
750 End If;
751
752 p_id1 := l_id11;
753 p_id2 := l_id22;
754
755 CLOSE okx_service_csr;
756
757 END IF;
758
759 -- For Object Code 'OKX_SYSITEM'
760
761 IF (l_lty_code = 'ITEM' or l_lty_code ='ADD_ITEM') THEN
762
763 OPEN okx_sysitem_csr(p_name => p_name,
764 p_id1 => l_id1,
765 p_id2 => l_id2);
766
767 l_id1 := Null;
768 l_id2 := Null;
769 l_name := Null;
770 l_description := Null;
771
772 FETCH okx_sysitem_csr into l_id1,l_id2,l_name,l_description;
773
774 If okx_sysitem_csr%NotFound Then
775 x_return_status := OKC_API.g_ret_sts_error;
776 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED');
777 raise OKC_API.G_EXCEPTION_ERROR;
778 End If;
779
780 l_id11 := l_id1;
781 l_id22 := l_id2;
782
783 FETCH okx_sysitem_csr into l_id1,l_id2,l_name,l_description;
784 If okx_sysitem_csr%Found Then
785
786 If( p_id1 is null or p_id1 = OKC_API.G_MISS_CHAR) then
787 x_return_status := OKC_API.g_ret_sts_error;
788 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset from lov
789 raise OKC_API.G_EXCEPTION_ERROR;
790 End If;
791
792 If( p_id2 is null or p_id2 = OKC_API.G_MISS_CHAR) then
793 x_return_status := OKC_API.g_ret_sts_error;
794 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset from lov
795 raise OKC_API.G_EXCEPTION_ERROR;
796 End If;
797
798 Loop
799 If(l_id1 = p_id1 and l_id2 = p_id2) Then
800 l_id11 := l_id1;
801 l_id22 := l_id2;
802 row_count := 1;
803 Exit;
804 End If;
805 Fetch okx_sysitem_csr into l_id1,l_id2,l_name,l_description;
806 Exit When okx_sysitem_csr%NotFound;
807 End Loop;
808
809 If row_count <> 1 Then
810 x_return_status := OKC_API.g_ret_sts_error;
811 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED'); -- duplicate values found; pl. select an asset
812 raise OKC_API.G_EXCEPTION_ERROR;
813 End If;
814
815 End If;
816
817 p_id1 := l_id11;
818 p_id2 := l_id22;
819
820 CLOSE okx_sysitem_csr;
821
822 END IF;
823
824 -- check for the link asset in the subline
825 open get_item_csr(p_chr_id, p_parent_cle_id, p_id1, p_id2);
826 Fetch get_item_csr into l_id;
827
828 If get_item_csr%Found Then
829 Close get_item_csr;
830 x_return_status := OKC_API.g_ret_sts_error;
831 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_NUMBER'); -- duplicate values found; pl. select an asset
832 raise OKC_API.G_EXCEPTION_ERROR;
833 End If;
834
835 Close get_item_csr;
836
837 x_return_status := OKC_API.G_RET_STS_SUCCESS;
838
839
840 EXCEPTION
841 when OKC_API.G_EXCEPTION_ERROR then
842 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
843 p_api_name => l_api_name,
844 p_pkg_name => g_pkg_name,
845 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
846 x_msg_count => x_msg_count,
847 x_msg_data => x_msg_data,
848 p_api_type => g_api_type);
849
850 IF okl_strmtyp_csr%ISOPEN THEN
851 CLOSE okl_strmtyp_csr;
852 END IF;
853
854 IF okl_usage_csr%ISOPEN THEN
855 CLOSE okl_usage_csr;
856 END IF;
857
858 IF okx_asset_csr%ISOPEN THEN
859 CLOSE okx_asset_csr;
860 END IF;
861
862 IF okx_covasst_csr%ISOPEN THEN
863 CLOSE okx_covasst_csr;
864 END IF;
865
866 IF okx_ib_item_csr%ISOPEN THEN
867 CLOSE okx_ib_item_csr;
868 END IF;
869
870 IF okx_service_csr%ISOPEN THEN
871 CLOSE okx_service_csr;
872 END IF;
873
874 IF okx_sysitem_csr%ISOPEN THEN
875 CLOSE okx_sysitem_csr;
876 END IF;
877
878 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
879 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
880 p_api_name => l_api_name,
881 p_pkg_name => g_pkg_name,
882 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
883 x_msg_count => x_msg_count,
884 x_msg_data => x_msg_data,
885 p_api_type => g_api_type);
886
887 IF okl_strmtyp_csr%ISOPEN THEN
888 CLOSE okl_strmtyp_csr;
889 END IF;
890
891 IF okl_usage_csr%ISOPEN THEN
892 CLOSE okl_usage_csr;
893 END IF;
894
895 IF okx_asset_csr%ISOPEN THEN
896 CLOSE okx_asset_csr;
897 END IF;
898
899 IF okx_covasst_csr%ISOPEN THEN
900 CLOSE okx_covasst_csr;
901 END IF;
902
903 IF okx_ib_item_csr%ISOPEN THEN
904 CLOSE okx_ib_item_csr;
905 END IF;
906
907 IF okx_service_csr%ISOPEN THEN
908 CLOSE okx_service_csr;
909 END IF;
910
911 IF okx_sysitem_csr%ISOPEN THEN
912 CLOSE okx_sysitem_csr;
913 END IF;
914
915 when OTHERS then
916 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
917 p_api_name => l_api_name,
918 p_pkg_name => g_pkg_name,
919 p_exc_name => 'OTHERS',
920 x_msg_count => x_msg_count,
921 x_msg_data => x_msg_data,
922 p_api_type => g_api_type);
923 IF okl_strmtyp_csr%ISOPEN THEN
924 CLOSE okl_strmtyp_csr;
925 END IF;
926
927 IF okl_usage_csr%ISOPEN THEN
928 CLOSE okl_usage_csr;
929 END IF;
930
931 IF okx_asset_csr%ISOPEN THEN
932 CLOSE okx_asset_csr;
933 END IF;
934
935 IF okx_covasst_csr%ISOPEN THEN
936 CLOSE okx_covasst_csr;
937 END IF;
938
939 IF okx_ib_item_csr%ISOPEN THEN
940 CLOSE okx_ib_item_csr;
941 END IF;
942
943 IF okx_service_csr%ISOPEN THEN
944 CLOSE okx_service_csr;
945 END IF;
946
947 IF okx_sysitem_csr%ISOPEN THEN
948 CLOSE okx_sysitem_csr;
949 END IF;
950
951 End Validate_Link_Asset;
952
953 PROCEDURE delete_contract_line_item(
954 p_api_version IN NUMBER,
955 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
956 x_return_status OUT NOCOPY VARCHAR2,
957 x_msg_count OUT NOCOPY NUMBER,
958 x_msg_data OUT NOCOPY VARCHAR2,
959 p_line_item_tbl IN line_item_tbl_type
960 ) IS
961
962 l_api_name CONSTANT VARCHAR2(30) := 'delete_contract_line_item';
963 l_api_version CONSTANT NUMBER := 1.0;
964 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
965 i NUMBER;
966 l_chr_id NUMBER;
967
968 lp_clev_tbl OKL_OKC_MIGRATION_PVT.clev_tbl_type;
969 lp_cimv_tbl OKL_OKC_MIGRATION_PVT.cimv_tbl_type;
970 lp_klev_tbl OKL_KLE_PVT.klev_tbl_type;
971
972 lx_clev_tbl OKL_OKC_MIGRATION_PVT.clev_tbl_type;
973 lx_cimv_tbl OKL_OKC_MIGRATION_PVT.cimv_tbl_type;
974 lx_klev_tbl OKL_KLE_PVT.klev_tbl_type;
975
976 BEGIN
977
978 x_return_status := OKC_API.START_ACTIVITY(
979 p_api_name => l_api_name,
980 p_pkg_name => g_pkg_name,
981 p_init_msg_list => p_init_msg_list,
982 l_api_version => l_api_version,
983 p_api_version => p_api_version,
984 p_api_type => g_api_type,
985 x_return_status => x_return_status);
986
987 -- check if activity started successfully
988 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
989 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
990 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
991 raise OKC_API.G_EXCEPTION_ERROR;
992 End If;
993
994 If (p_line_item_tbl.COUNT > 0) Then
995
996 i := p_line_item_tbl.FIRST;
997
998 --Bug# 4959361
999 OKL_LLA_UTIL_PVT.check_line_update_allowed
1000 (p_api_version => p_api_version,
1001 p_init_msg_list => p_init_msg_list,
1002 x_return_status => x_return_status,
1003 x_msg_count => x_msg_count,
1004 x_msg_data => x_msg_data,
1005 p_cle_id => p_line_item_tbl(i).cle_id);
1006
1007 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1008 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1009 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1010 RAISE OKL_API.G_EXCEPTION_ERROR;
1011 END IF;
1012 --Bug# 4959361
1013
1014 l_chr_id := p_line_item_tbl(i).chr_id;
1015 If okl_context.get_okc_org_id is null then
1016 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
1017 End If;
1018
1019 LOOP
1020 lp_clev_tbl(i).cle_id := p_line_item_tbl(i).parent_cle_id;
1021 lp_clev_tbl(i).dnz_chr_id := p_line_item_tbl(i).chr_id;
1022 lp_clev_tbl(i).id := p_line_item_tbl(i).cle_id;
1023
1024 lp_klev_tbl(i).kle_id := p_line_item_tbl(i).cle_id;
1025
1026 lp_cimv_tbl(i).id := p_line_item_tbl(i).item_id;
1027 lp_cimv_tbl(i).cle_id := p_line_item_tbl(i).cle_id;
1028 lp_cimv_tbl(i).dnz_chr_id := p_line_item_tbl(i).chr_id;
1029
1030
1031 EXIT WHEN (i = p_line_item_tbl.LAST);
1032 i := p_line_item_tbl.NEXT(i);
1033 END LOOP;
1034
1035 End If;
1036
1037 delete_contract_line_item(
1038 p_api_version => p_api_version,
1039 p_init_msg_list => p_init_msg_list,
1040 x_return_status => x_return_status,
1041 x_msg_count => x_msg_count,
1042 x_msg_data => x_msg_data,
1043 p_clev_tbl => lp_clev_tbl,
1044 p_klev_tbl => lp_klev_tbl,
1045 p_cimv_tbl => lp_cimv_tbl);
1046
1047 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1048 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1049 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1050 raise OKC_API.G_EXCEPTION_ERROR;
1051 End If;
1052
1053 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1054
1055 EXCEPTION
1056 when OKC_API.G_EXCEPTION_ERROR then
1057 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1058 p_api_name => l_api_name,
1059 p_pkg_name => g_pkg_name,
1060 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1061 x_msg_count => x_msg_count,
1062 x_msg_data => x_msg_data,
1063 p_api_type => g_api_type);
1064
1065 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1066 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1067 p_api_name => l_api_name,
1068 p_pkg_name => g_pkg_name,
1069 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1070 x_msg_count => x_msg_count,
1071 x_msg_data => x_msg_data,
1072 p_api_type => g_api_type);
1073
1074 when OTHERS then
1075 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1076 p_api_name => l_api_name,
1077 p_pkg_name => g_pkg_name,
1078 p_exc_name => 'OTHERS',
1079 x_msg_count => x_msg_count,
1080 x_msg_data => x_msg_data,
1081 p_api_type => g_api_type);
1082
1083 END;
1084
1085 PROCEDURE create_contract_line_item(
1086 p_api_version IN NUMBER,
1087 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1088 x_return_status OUT NOCOPY VARCHAR2,
1089 x_msg_count OUT NOCOPY NUMBER,
1090 x_msg_data OUT NOCOPY VARCHAR2,
1091 p_line_item_tbl IN line_item_tbl_type,
1092 x_line_item_tbl OUT NOCOPY line_item_tbl_type
1093 ) IS
1094
1095 l_api_name CONSTANT VARCHAR2(30) := 'create_contract_line_item';
1096 l_api_version CONSTANT NUMBER := 1.0;
1097 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1098 i NUMBER;
1099 l_chr_id NUMBER;
1100 l_parent_cle_id NUMBER;
1101 l_cnt number := 0;
1102
1103 l_currency_code okl_k_headers_full_v.currency_code%type := null;
1104 l_sts_code okl_k_headers_full_v.sts_code%type := null;
1105 l_lse_id okc_line_styles_b.id%type := null;
1106 l_lty_code okc_line_styles_b.lty_code%type := null;
1107 l_item_id1 okc_k_items.object1_id1%type := null;
1108 l_item_id2 okc_k_items.object1_id2%type := null;
1109 l_amount number := null;
1110
1111 CURSOR get_link_fee_asset_csr(p_chr_id number, p_cle_id number, p_name varchar2) IS
1112 SELECT ID1, ID2
1113 FROM OKL_LA_FEE_COVERED_ASSET_UV
1114 WHERE DNZ_CHR_ID = p_chr_id
1115 AND CLE_ID = p_cle_id
1116 AND NAME = p_name;
1117
1118 CURSOR get_link_service_asset_csr(p_chr_id number, p_cle_id number, p_name varchar2) IS
1119 SELECT ID1,id2
1120 FROM OKL_LA_SRV_COV_AST_UV
1121 WHERE CHR_ID = p_chr_id
1122 and name = p_name;
1123
1124 CURSOR is_serv_contract_csr(p_cle_id number) IS
1125 select count(1)
1126 from okc_k_rel_objs rel
1127 where rel.cle_id = p_cle_id and
1128 rel.rty_code = 'OKLSRV';
1129
1130 CURSOR get_k_info_csr(p_chr_id number) IS
1131 SELECT currency_code,sts_code
1132 from okc_k_headers_v chr
1133 where chr.id = p_chr_id;
1134
1135 CURSOR get_lse_id_csr(p_chr_id number, p_cle_id number) IS
1136 select lse.id,lse.lty_code
1137 from okc_line_styles_b lse
1138 , okc_k_lines_b cle
1139 where lse.lse_parent_id = cle.lse_id
1140 and cle.id = p_cle_id
1141 and cle.dnz_chr_id = p_chr_id;
1142
1143 CURSOR get_fee_type_csr(p_chr_id number, p_cle_id number) IS
1144 select cle.fee_type
1145 from okl_k_lines_full_v cle
1146 where cle.id = p_cle_id
1147 and cle.dnz_chr_id = p_chr_id;
1148
1149 lp_clev_tbl OKL_OKC_MIGRATION_PVT.clev_tbl_type;
1150 lp_cimv_tbl OKL_OKC_MIGRATION_PVT.cimv_tbl_type;
1151 lp_klev_tbl OKL_KLE_PVT.klev_tbl_type;
1152 lp_srv_cov_tbl okl_service_integration_pvt.srv_cov_tbl_type;
1153
1154 lx_clev_tbl OKL_OKC_MIGRATION_PVT.clev_tbl_type;
1155 lx_cimv_tbl OKL_OKC_MIGRATION_PVT.cimv_tbl_type;
1156 lx_klev_tbl OKL_KLE_PVT.klev_tbl_type;
1157
1158 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
1159
1160 --Bug# 3877032
1161 l_fin_clev_rec okl_okc_migration_pvt.clev_rec_type;
1162 l_fin_klev_rec okl_contract_pub.klev_rec_type;
1163 lx_fin_clev_rec okl_okc_migration_pvt.clev_rec_type;
1164 lx_fin_klev_rec okl_contract_pub.klev_rec_type;
1165
1166 --cursor to find out the fee type from okl_k_lines
1167 cursor l_fee_type_csr (p_cle_id in number) is
1168 select fee_type
1169 from okl_k_lines kle
1170 where kle.id = p_cle_id;
1171
1172 l_fee_type okl_k_lines.fee_type%TYPE;
1173 --Bug#3877032
1174
1175 BEGIN
1176
1177 x_return_status := OKC_API.START_ACTIVITY(
1178 p_api_name => l_api_name,
1179 p_pkg_name => g_pkg_name,
1180 p_init_msg_list => p_init_msg_list,
1181 l_api_version => l_api_version,
1182 p_api_version => p_api_version,
1183 p_api_type => g_api_type,
1184 x_return_status => x_return_status);
1185
1186 -- check if activity started successfully
1187 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1188 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1189 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1190 raise OKC_API.G_EXCEPTION_ERROR;
1191 End If;
1192
1193
1194 If (p_line_item_tbl.COUNT > 0) Then
1195 i := p_line_item_tbl.FIRST;
1196
1197 --Bug# 4959361
1198 OKL_LLA_UTIL_PVT.check_line_update_allowed
1199 (p_api_version => p_api_version,
1200 p_init_msg_list => p_init_msg_list,
1201 x_return_status => x_return_status,
1202 x_msg_count => x_msg_count,
1203 x_msg_data => x_msg_data,
1204 p_cle_id => p_line_item_tbl(i).parent_cle_id);
1205
1206 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1207 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1208 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1209 RAISE OKL_API.G_EXCEPTION_ERROR;
1210 END IF;
1211 --Bug# 4959361
1212
1213 l_chr_id := p_line_item_tbl(i).chr_id;
1214 If okl_context.get_okc_org_id is null then
1215 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
1216 End If;
1217
1218 open get_k_info_csr(p_line_item_tbl(i).chr_id);
1219 fetch get_k_info_csr into l_currency_code,l_sts_code;
1220 close get_k_info_csr;
1221
1222 l_parent_cle_id := p_line_item_tbl(i).parent_cle_id;
1223 l_lty_code := null;
1224 open get_lse_id_csr(l_chr_id,l_parent_cle_id);
1225 fetch get_lse_id_csr into l_lse_id,l_lty_code;
1226 close get_lse_id_csr;
1227
1228 l_fee_type := null;
1229 open get_fee_type_csr(l_chr_id,l_parent_cle_id);
1230 fetch get_fee_type_csr into l_fee_type;
1231 close get_fee_type_csr;
1232 LOOP
1233 -- Not null Validation for Asset Number
1234 IF (p_line_item_tbl(i).name = OKC_API.G_MISS_CHAR OR p_line_item_tbl(i).name IS NULL) THEN
1235 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1236 p_msg_name => 'OKL_LLA_ASSET_REQUIRED');
1237 x_return_status := OKC_API.g_ret_sts_error;
1238 RAISE OKC_API.G_EXCEPTION_ERROR;
1239 END IF;
1240
1241 IF( l_fee_type is not null and l_fee_type = 'CAPITALIZED') Then
1242
1243 -- Not null Validation for capital Amount
1244 IF (p_line_item_tbl(i).capital_amount IS NULL OR p_line_item_tbl(i).capital_amount = OKC_API.G_MISS_NUM) THEN
1245 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_AMOUNT');
1246 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1247 , p_msg_name => 'OKL_AMOUNT_FORMAT'
1248 , p_token1 => 'COL_NAME'
1249 , p_token1_value => l_ak_prompt
1250 );
1251 x_return_status := OKC_API.g_ret_sts_error;
1252 RAISE OKC_API.G_EXCEPTION_ERROR;
1253 END IF;
1254
1255 --Bug#4664176
1256 --Bug#4635028
1257 -- ELSIF(l_fee_type is not null and l_fee_type in ('ROLLOVER','FINANCED')) Then
1258 ELSIF(l_fee_type is not null and l_fee_type <> 'CAPITALIZED') Then
1259
1260 IF( p_line_item_tbl(i).capital_amount IS NOT NULL ) Then
1261 l_amount := p_line_item_tbl(i).capital_amount;
1262 END IF;
1263
1264 -- Not null Validation for Amount
1265 IF(l_amount IS NULL OR l_amount = OKC_API.G_MISS_NUM) THEN
1266 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_AMOUNT');
1267 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1268 , p_msg_name => 'OKL_AMOUNT_FORMAT'
1269 , p_token1 => 'COL_NAME'
1270 , p_token1_value => l_ak_prompt
1271 );
1272 x_return_status := OKC_API.g_ret_sts_error;
1273 RAISE OKC_API.G_EXCEPTION_ERROR;
1274
1275 END IF;
1276 END IF;
1277
1278 If(l_lty_code = 'LINK_FEE_ASSET') Then -- do the validation for fee link asset
1279 l_item_id1 := null;
1280 l_item_id2 := null;
1281 open get_link_fee_asset_csr(l_chr_id, l_parent_cle_id, p_line_item_tbl(i).name);
1282 fetch get_link_fee_asset_csr into l_item_id1, l_item_id2;
1283 close get_link_fee_asset_csr;
1284
1285 If(l_item_id1 is null or l_item_id2 is null) Then -- through error message
1286 x_return_status := OKC_API.g_ret_sts_error;
1287 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_ASSET');
1288 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
1289 , p_token1 => 'COL_NAME'
1290 , p_token1_value => l_ak_prompt
1291 );
1292 raise OKC_API.G_EXCEPTION_ERROR;
1293 End If;
1294
1295 lp_cimv_tbl(i).object1_id1 := l_item_id1;
1296 lp_cimv_tbl(i).object1_id2 := l_item_id2;
1297
1298 ElsIf(l_lty_code = 'LINK_SERV_ASSET') Then -- do the validation for service link asset
1299 lp_klev_tbl(i).capital_amount := p_line_item_tbl(i).capital_amount;
1300 l_cnt := 0;
1301 open is_serv_contract_csr(l_parent_cle_id);
1302 fetch is_serv_contract_csr into l_cnt;
1303 close is_serv_contract_csr;
1304 If( l_cnt = 0) Then -- service contract not attached
1305 l_item_id1 := null;
1306 l_item_id2 := null;
1307 open get_link_service_asset_csr(l_chr_id, l_parent_cle_id, p_line_item_tbl(i).name);
1308 fetch get_link_service_asset_csr into l_item_id1, l_item_id2;
1309 close get_link_service_asset_csr;
1310 If(l_item_id1 is null or l_item_id2 is null) Then -- through error message
1311 x_return_status := OKC_API.g_ret_sts_error;
1312 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_ASSET');
1313 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
1314 , p_token1 => 'COL_NAME'
1315 , p_token1_value => l_ak_prompt
1316
1317 );
1318 raise OKC_API.G_EXCEPTION_ERROR;
1319 End If;
1320 lp_cimv_tbl(i).object1_id1 := l_item_id1;
1321 lp_cimv_tbl(i).object1_id2 := l_item_id2;
1322 Else
1323 lp_cimv_tbl(i).object1_id1 := p_line_item_tbl(i).item_id1;
1324 lp_cimv_tbl(i).object1_id2 := p_line_item_tbl(i).item_id2;
1325
1326 -- sjalasut, bug 4648686. START
1327 -- l_item_id1 is being assigned to lp_cimv_tbl(i).object1_id1 further down from here
1328 -- if none of the if conditions above satisfy, these local variables would have null values
1329 -- to prevent such a scenario, assigning p_line_item_tbl(i).item_id1 and p_line_item_tbl(i).item_id2 to them
1330 l_item_id1 := p_line_item_tbl(i).item_id1;
1331 l_item_id2 := p_line_item_tbl(i).item_id2;
1332 -- sjalasut, bug 4648686. END
1333 End If;
1334 End If;
1335
1336
1337 lp_clev_tbl(i).line_number := '1';
1338 lp_clev_tbl(i).exception_yn := 'N';
1339 lp_clev_tbl(i).display_sequence := 1;
1340 lp_clev_tbl(i).chr_id := null;
1341 lp_clev_tbl(i).cle_id := p_line_item_tbl(i).parent_cle_id;
1342 lp_clev_tbl(i).dnz_chr_id := p_line_item_tbl(i).chr_id;
1343 lp_clev_tbl(i).name := p_line_item_tbl(i).name;
1344 -- lp_clev_tbl(i).item_description := p_line_item_tbl(i).item_description;
1345 lp_clev_tbl(i).id := p_line_item_tbl(i).cle_id;
1346 lp_clev_tbl(i).currency_code := l_currency_code;
1347 lp_clev_tbl(i).sts_code := l_sts_code;
1348 lp_clev_tbl(i).lse_id := l_lse_id;
1349
1350 --Bug#4664176
1351 --Bug#4635028
1352 --IF(l_fee_type is not null and l_fee_type in ('ROLLOVER','FINANCED')) Then
1353 IF(l_fee_type is not null and l_fee_type <> 'CAPITALIZED') Then
1354 lp_klev_tbl(i).amount := l_amount;
1355 lp_klev_tbl(i).capital_amount := null;
1356
1357 ELSIF(l_fee_type is not null and l_fee_type = 'CAPITALIZED') Then
1358 lp_klev_tbl(i).capital_amount := p_line_item_tbl(i).capital_amount;
1359 lp_klev_tbl(i).amount := null;
1360
1361 END IF;
1362
1363 lp_klev_tbl(i).kle_id := p_line_item_tbl(i).cle_id;
1364
1365 lp_cimv_tbl(i).id := p_line_item_tbl(i).item_id;
1366 lp_cimv_tbl(i).cle_id := p_line_item_tbl(i).cle_id;
1367 lp_cimv_tbl(i).cle_id_for := null;
1368 lp_cimv_tbl(i).chr_id := null;
1369 lp_cimv_tbl(i).exception_yn := 'N';
1370 lp_cimv_tbl(i).number_of_items := 1;
1371 lp_cimv_tbl(i).dnz_chr_id := p_line_item_tbl(i).chr_id;
1372 lp_cimv_tbl(i).object1_id1 := l_item_id1;
1373 lp_cimv_tbl(i).object1_id2 := l_item_id2;
1374 lp_cimv_tbl(i).jtot_object1_code := p_line_item_tbl(i).item_object1_code;
1375
1376 lp_srv_cov_tbl(i).oks_cov_prod_line_id := p_line_item_tbl(i).serv_cov_prd_id;
1377
1378 EXIT WHEN (i = p_line_item_tbl.LAST);
1379 i := p_line_item_tbl.NEXT(i);
1380 END LOOP;
1381 End If;
1382
1383 If l_lty_code = 'LINK_SERV_ASSET' Then
1384 If( l_cnt = 0) Then -- service contract not attached
1385 create_contract_line_item(
1386 p_api_version => p_api_version,
1387 p_init_msg_list => p_init_msg_list,
1388 x_return_status => x_return_status,
1389 x_msg_count => x_msg_count,
1390 x_msg_data => x_msg_data,
1391 p_clev_tbl => lp_clev_tbl,
1392 p_klev_tbl => lp_klev_tbl,
1393 p_cimv_tbl => lp_cimv_tbl,
1394 x_clev_tbl => lx_clev_tbl,
1395 x_klev_tbl => lx_klev_tbl,
1396 x_cimv_tbl => lx_cimv_tbl);
1397
1398 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1399 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1400 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1401 raise OKC_API.G_EXCEPTION_ERROR;
1402 END IF;
1403 ELSE
1404
1405 okl_service_integration_pub.create_cov_asset_line(
1406 p_api_version => p_api_version,
1407 p_init_msg_list => p_init_msg_list,
1408 x_return_status => x_return_status,
1409 x_msg_count => x_msg_count,
1410 x_msg_data => x_msg_data,
1411 p_clev_tbl => lp_clev_tbl,
1412 p_klev_tbl => lp_klev_tbl,
1413 p_cimv_tbl => lp_cimv_tbl,
1414 p_cov_tbl => lp_srv_cov_tbl,
1415 x_clev_tbl => lx_clev_tbl,
1416 x_klev_tbl => lx_klev_tbl,
1417 x_cimv_tbl => lx_cimv_tbl);
1418
1419 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1420 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1421 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1422 raise OKC_API.G_EXCEPTION_ERROR;
1423 End If;
1424 End If;
1425 Elsif l_lty_code = 'LINK_FEE_ASSET' Then
1426 create_contract_line_item(
1427 p_api_version => p_api_version,
1428 p_init_msg_list => p_init_msg_list,
1429 x_return_status => x_return_status,
1430 x_msg_count => x_msg_count,
1431 x_msg_data => x_msg_data,
1432 p_clev_tbl => lp_clev_tbl,
1433 p_klev_tbl => lp_klev_tbl,
1434 p_cimv_tbl => lp_cimv_tbl,
1435 x_clev_tbl => lx_clev_tbl,
1436 x_klev_tbl => lx_klev_tbl,
1437 x_cimv_tbl => lx_cimv_tbl);
1438
1439 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1440 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1441 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1442 raise OKC_API.G_EXCEPTION_ERROR;
1443 End If;
1444
1445 --Bug# 3877032
1446 --update capital amount field
1447 If lx_cimv_tbl.COUNT > 0 then
1448 For i in lx_cimv_tbl.FIRST..lx_cimv_tbl.LAST Loop
1449 l_fee_type := null;
1450 open l_fee_type_csr(p_cle_id => lx_clev_tbl(i).cle_id);
1451 fetch l_fee_type_csr into l_fee_type;
1452 close l_fee_type_csr;
1453 if nvl(l_fee_type,'GENERAL') = 'CAPITALIZED' then
1454 l_fin_clev_rec.id := lx_cimv_tbl(i).object1_id1;
1455 l_fin_klev_rec.id := lx_cimv_tbl(i).object1_id1;
1456 OKL_EXECUTE_FORMULA_PUB.execute(p_api_version => p_api_version,
1457 p_init_msg_list => p_init_msg_list,
1458 x_return_status => x_return_status,
1459 x_msg_count => x_msg_count,
1460 x_msg_data => x_msg_data,
1461 p_formula_name => 'LINE_CAP_AMNT',
1462 p_contract_id => lx_cimv_tbl(i).dnz_chr_id,
1463 p_line_id => lx_cimv_tbl(i).object1_id1,
1464 x_value => l_fin_klev_rec.capital_amount);
1465 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1466 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1467 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1468 raise OKC_API.G_EXCEPTION_ERROR;
1469 End If;
1470
1471 okl_contract_pub.update_contract_line(p_api_version => p_api_version,
1472 p_init_msg_list => p_init_msg_list,
1473 x_return_status => x_return_status,
1474 x_msg_count => x_msg_count,
1475 x_msg_data => x_msg_data,
1476 p_clev_rec => l_fin_clev_rec,
1477 p_klev_rec => l_fin_klev_rec,
1478 x_clev_rec => lx_fin_clev_rec,
1479 x_klev_rec => lx_fin_klev_rec);
1480 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1481 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1482 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1483 raise OKC_API.G_EXCEPTION_ERROR;
1484 End If;
1485
1486 --Bug# 4899328
1487 -- Recalculate Asset depreciation cost when there
1488 -- is a change to Capitalized Fee
1489 okl_activate_asset_pvt.recalculate_asset_cost
1490 (p_api_version => p_api_version,
1491 p_init_msg_list => p_init_msg_list,
1492 x_return_status => x_return_status,
1493 x_msg_count => x_msg_count,
1494 x_msg_data => x_msg_data,
1495 p_chr_id => lx_cimv_tbl(i).dnz_chr_id,
1496 p_cle_id => TO_NUMBER(lx_cimv_tbl(i).object1_id1)
1497 );
1498
1499 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1500 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1501 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1502 RAISE OKL_API.G_EXCEPTION_ERROR;
1503 END IF;
1504 --Bug# 4899328
1505
1506 End If;
1507 End Loop;
1508 End If;
1509 --Bug# 3877032
1510 End If;
1511
1512
1513 If (p_line_item_tbl.COUNT > 0) Then
1514 i := p_line_item_tbl.FIRST;
1515 LOOP
1516 x_line_item_tbl(i).chr_id := p_line_item_tbl(i).chr_id;
1517 x_line_item_tbl(i).parent_cle_id := p_line_item_tbl(i).parent_cle_id;
1518 x_line_item_tbl(i).cle_id := lx_clev_tbl(i).id;
1519 x_line_item_tbl(i).item_id := lx_cimv_tbl(i).id;
1520 x_line_item_tbl(i).item_id1 := lx_cimv_tbl(i).object1_id1;
1521 x_line_item_tbl(i).item_id2 := lx_cimv_tbl(i).object1_id2;
1522 x_line_item_tbl(i).item_object1_code := lx_cimv_tbl(i).jtot_object1_code;
1523 x_line_item_tbl(i).item_description := p_line_item_tbl(i).item_description;
1524 x_line_item_tbl(i).name := p_line_item_tbl(i).name;
1525 x_line_item_tbl(i).capital_amount := p_line_item_tbl(i).capital_amount;
1526 x_line_item_tbl(i).serv_cov_prd_id := p_line_item_tbl(i).serv_cov_prd_id;
1527
1528 EXIT WHEN (i = p_line_item_tbl.LAST);
1529 i := p_line_item_tbl.NEXT(i);
1530 END LOOP;
1531 End If;
1532
1533
1534 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1535
1536 EXCEPTION
1537 when OKC_API.G_EXCEPTION_ERROR then
1538 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1539 p_api_name => l_api_name,
1540 p_pkg_name => g_pkg_name,
1541 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1542 x_msg_count => x_msg_count,
1543 x_msg_data => x_msg_data,
1544 p_api_type => g_api_type);
1545
1546 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1547 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1548 p_api_name => l_api_name,
1549 p_pkg_name => g_pkg_name,
1550 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1551 x_msg_count => x_msg_count,
1552 x_msg_data => x_msg_data,
1553 p_api_type => g_api_type);
1554
1555 when OTHERS then
1556 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1557 p_api_name => l_api_name,
1558 p_pkg_name => g_pkg_name,
1559 p_exc_name => 'OTHERS',
1560 x_msg_count => x_msg_count,
1561 x_msg_data => x_msg_data,
1562 p_api_type => g_api_type);
1563
1564 END;
1565
1566 PROCEDURE update_contract_line_item(
1567 p_api_version IN NUMBER,
1568 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1569 x_return_status OUT NOCOPY VARCHAR2,
1570 x_msg_count OUT NOCOPY NUMBER,
1571 x_msg_data OUT NOCOPY VARCHAR2,
1572 p_line_item_tbl IN line_item_tbl_type,
1573 x_line_item_tbl OUT NOCOPY line_item_tbl_type
1574 ) IS
1575
1576 l_api_name CONSTANT VARCHAR2(30) := 'update_contract_line_item';
1577 l_api_version CONSTANT NUMBER := 1.0;
1578 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1579 i NUMBER;
1580 l_chr_id NUMBER;
1581 l_parent_cle_id NUMBER;
1582 l_cnt number := 0;
1583 l_fee_type okl_k_lines_full_v.fee_type%type := null;
1584 l_amount number := null;
1585
1586 l_currency_code okl_k_headers_full_v.currency_code%type := null;
1587 l_sts_code okl_k_headers_full_v.sts_code%type := null;
1588 l_lse_id okc_line_styles_b.id%type := null;
1589 l_lty_code okc_line_styles_b.lty_code%type := null;
1590 l_item_id1 okc_k_items.object1_id1%type := null;
1591 l_item_id2 okc_k_items.object1_id2%type := null;
1592
1593 CURSOR get_link_fee_asset_csr(p_chr_id number, p_cle_id number, p_name varchar2) IS
1594 SELECT ID1, ID2
1595 FROM OKL_LA_FEE_COVERED_ASSET_UV
1596 WHERE DNZ_CHR_ID = p_chr_id
1597 AND CLE_ID = p_cle_id
1598 AND NAME = p_name;
1599
1600 CURSOR get_link_service_asset_csr(p_chr_id number, p_cle_id number, p_name varchar2) IS
1601 SELECT ID1,id2
1602 FROM OKL_LA_SRV_COV_AST_UV
1603 WHERE CHR_ID = p_chr_id
1604 and name = p_name;
1605
1606 CURSOR is_serv_contract_csr(p_cle_id number) IS
1607 select count(1)
1608 from okc_k_rel_objs rel
1609 where rel.cle_id = p_cle_id and
1610 rel.rty_code = 'OKLSRV';
1611
1612 CURSOR get_k_info_csr(p_chr_id number) IS
1613 SELECT currency_code,sts_code
1614 from okc_k_headers_v chr
1615 where chr.id = p_chr_id;
1616
1617 CURSOR get_lse_id_csr(p_chr_id number, p_cle_id number) IS
1618 select lse.id,lse.lty_code
1619 from okc_line_styles_b lse
1620 , okc_k_lines_b cle
1621 where lse.lse_parent_id = cle.lse_id
1622 and cle.id = p_cle_id
1623 and cle.dnz_chr_id = p_chr_id;
1624
1625 lp_clev_tbl OKL_OKC_MIGRATION_PVT.clev_tbl_type;
1626 lp_cimv_tbl OKL_OKC_MIGRATION_PVT.cimv_tbl_type;
1627 lp_klev_tbl OKL_KLE_PVT.klev_tbl_type;
1628 lp_srv_cov_tbl okl_service_integration_pvt.srv_cov_tbl_type;
1629
1630 lx_clev_tbl OKL_OKC_MIGRATION_PVT.clev_tbl_type;
1631 lx_cimv_tbl OKL_OKC_MIGRATION_PVT.cimv_tbl_type;
1632 lx_klev_tbl OKL_KLE_PVT.klev_tbl_type;
1633
1634 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
1635
1636 --Bug# 3877032
1637 l_fin_clev_rec okl_okc_migration_pvt.clev_rec_type;
1638 l_fin_klev_rec okl_contract_pub.klev_rec_type;
1639 lx_fin_clev_rec okl_okc_migration_pvt.clev_rec_type;
1640 lx_fin_klev_rec okl_contract_pub.klev_rec_type;
1641
1642 --cursor to find out the fee type from okl_k_lines
1643 cursor l_fee_type_csr (p_cle_id in number) is
1644 select fee_type
1645 from okl_k_lines kle
1646 where kle.id = p_cle_id;
1647
1648 --cursor to get old asset id
1649 cursor l_old_ast_csr (p_cim_id in number) is
1650 select object1_id1, dnz_chr_id
1651 from okc_k_items
1652 where id = p_cim_id;
1653
1654 l_fin_clev_tbl okl_okc_migration_pvt.clev_tbl_type;
1655 l_fin_klev_tbl okl_contract_pub.klev_tbl_type;
1656 lx_fin_clev_tbl okl_okc_migration_pvt.clev_tbl_type;
1657 lx_fin_klev_tbl okl_contract_pub.klev_tbl_type;
1658 j number;
1659 --Bug# 3877032
1660
1661 CURSOR get_fee_type_csr(p_chr_id number, p_cle_id number) IS
1662 select cle.fee_type
1663 from okl_k_lines_full_v cle
1664 where cle.id = p_cle_id
1665 and cle.dnz_chr_id = p_chr_id;
1666
1667 BEGIN
1668
1669
1670 x_return_status := OKC_API.START_ACTIVITY(
1671 p_api_name => l_api_name,
1672 p_pkg_name => g_pkg_name,
1673 p_init_msg_list => p_init_msg_list,
1674 l_api_version => l_api_version,
1675 p_api_version => p_api_version,
1676 p_api_type => g_api_type,
1677 x_return_status => x_return_status);
1678
1679 -- check if activity started successfully
1680 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1681 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1682 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1683 raise OKC_API.G_EXCEPTION_ERROR;
1684 End If;
1685
1686 If (p_line_item_tbl.COUNT > 0) Then
1687
1688 i := p_line_item_tbl.FIRST;
1689
1690 --Bug# 4959361
1691 OKL_LLA_UTIL_PVT.check_line_update_allowed
1692 (p_api_version => p_api_version,
1693 p_init_msg_list => p_init_msg_list,
1694 x_return_status => x_return_status,
1695 x_msg_count => x_msg_count,
1696 x_msg_data => x_msg_data,
1697 p_cle_id => p_line_item_tbl(i).cle_id);
1698
1699 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1700 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1701 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1702 RAISE OKL_API.G_EXCEPTION_ERROR;
1703 END IF;
1704 --Bug# 4959361
1705
1706 l_chr_id := p_line_item_tbl(i).chr_id;
1707 If okl_context.get_okc_org_id is null then
1708 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
1709 End If;
1710
1711 open get_k_info_csr(l_chr_id);
1712 fetch get_k_info_csr into l_currency_code,l_sts_code;
1713 close get_k_info_csr;
1714
1715 l_parent_cle_id := p_line_item_tbl(i).parent_cle_id;
1716 open get_lse_id_csr(l_chr_id,l_parent_cle_id);
1717 fetch get_lse_id_csr into l_lse_id,l_lty_code;
1718 close get_lse_id_csr;
1719
1720 l_fee_type := null;
1721 open get_fee_type_csr(l_chr_id,l_parent_cle_id);
1722 fetch get_fee_type_csr into l_fee_type;
1723 close get_fee_type_csr;
1724
1725 LOOP
1726
1727
1728 -- Not null Validation for Asset Number
1729 IF (p_line_item_tbl(i).name = OKC_API.G_MISS_CHAR OR p_line_item_tbl(i).name IS NULL) THEN
1730
1731 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1732 p_msg_name => 'OKL_LLA_ASSET_REQUIRED');
1733 x_return_status := OKC_API.g_ret_sts_error;
1734 RAISE OKC_API.G_EXCEPTION_ERROR;
1735
1736 END IF;
1737
1738 IF(l_fee_type is not null and l_fee_type = 'CAPITALIZED') Then
1739
1740 -- Not null Validation for capitol Amount
1741 IF (p_line_item_tbl(i).capital_amount IS NULL OR p_line_item_tbl(i).capital_amount = OKC_API.G_MISS_NUM) THEN
1742
1743 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_AMOUNT');
1744 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1745 , p_msg_name => 'OKL_AMOUNT_FORMAT'
1746 , p_token1 => 'COL_NAME'
1747 , p_token1_value => l_ak_prompt
1748 );
1749 x_return_status := OKC_API.g_ret_sts_error;
1750 RAISE OKC_API.G_EXCEPTION_ERROR;
1751
1752 END IF;
1753
1754 --Bug#4664176
1755 --Bug#4635028
1756 --ELSIF(l_fee_type is not null and l_fee_type in ('ROLLOVER','FINANCED')) Then
1757 ELSIF(l_fee_type is not null and l_fee_type <> 'CAPITALIZED') Then
1758
1759 IF( p_line_item_tbl(i).capital_amount IS NOT NULL ) Then
1760 l_amount := p_line_item_tbl(i).capital_amount;
1761 END IF;
1762
1763 -- Not null Validation for Amount
1764 IF (l_amount IS NULL OR l_amount = OKC_API.G_MISS_NUM) THEN
1765
1766 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_AMOUNT');
1767 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1768 , p_msg_name => 'OKL_AMOUNT_FORMAT'
1769 , p_token1 => 'COL_NAME'
1770 , p_token1_value => l_ak_prompt
1771 );
1772 x_return_status := OKC_API.g_ret_sts_error;
1773 RAISE OKC_API.G_EXCEPTION_ERROR;
1774
1775 END IF;
1776
1777 END IF;
1778
1779 If(l_lty_code = 'LINK_FEE_ASSET') Then -- do the validation for fee link asset
1780
1781 l_item_id1 := null;
1782 l_item_id2 := null;
1783 open get_link_fee_asset_csr(l_chr_id, l_parent_cle_id, p_line_item_tbl(i).name);
1784 fetch get_link_fee_asset_csr into l_item_id1, l_item_id2;
1785 close get_link_fee_asset_csr;
1786
1787 If(l_item_id1 is null or l_item_id2 is null) Then -- through error message
1788 x_return_status := OKC_API.g_ret_sts_error;
1789 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_ASSET');
1790 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
1791 , p_token1 => 'COL_NAME'
1792 , p_token1_value => l_ak_prompt
1793
1794 );
1795 raise OKC_API.G_EXCEPTION_ERROR;
1796 End If;
1797
1798 ElsIf(l_lty_code = 'LINK_SERV_ASSET') Then -- do the validation for service link asset
1799
1800 lp_klev_tbl(i).capital_amount := p_line_item_tbl(i).capital_amount;
1801
1802 l_cnt := 0;
1803 open is_serv_contract_csr(l_parent_cle_id);
1804 fetch is_serv_contract_csr into l_cnt;
1805 close is_serv_contract_csr;
1806
1807 If( l_cnt = 0) Then -- service contract not attached
1808
1809 l_item_id1 := null;
1810 l_item_id2 := null;
1811 open get_link_service_asset_csr(l_chr_id, l_parent_cle_id, p_line_item_tbl(i).name);
1812 fetch get_link_service_asset_csr into l_item_id1, l_item_id2;
1813 close get_link_service_asset_csr;
1814
1815 If(l_item_id1 is null or l_item_id2 is null) Then -- through error message
1816
1817 x_return_status := OKC_API.g_ret_sts_error;
1818 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_ASSET');
1819 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
1820 , p_token1 => 'COL_NAME'
1821 , p_token1_value => l_ak_prompt
1822
1823 );
1824 raise OKC_API.G_EXCEPTION_ERROR;
1825
1826 End If;
1827
1828 End If;
1829
1830 End If;
1831
1832 lp_clev_tbl(i).line_number := '1';
1833 lp_clev_tbl(i).exception_yn := 'N';
1834 lp_clev_tbl(i).display_sequence := 1;
1835 lp_clev_tbl(i).chr_id := null;
1836 lp_clev_tbl(i).cle_id := p_line_item_tbl(i).parent_cle_id;
1837 lp_clev_tbl(i).dnz_chr_id := p_line_item_tbl(i).chr_id;
1838 lp_clev_tbl(i).currency_code := l_currency_code;
1839 lp_clev_tbl(i).sts_code := l_sts_code;
1840 lp_clev_tbl(i).lse_id := l_lse_id;
1841 lp_clev_tbl(i).name := p_line_item_tbl(i).name;
1842 -- lp_clev_tbl(i).item_description := p_line_item_tbl(i).item_description;
1843 lp_clev_tbl(i).id := p_line_item_tbl(i).cle_id;
1844
1845 --Bug#4664176
1846 --Bug#4635028
1847 --IF(l_fee_type is not null and l_fee_type in ('ROLLOVER','FINANCED')) Then
1848 IF(l_fee_type is not null and l_fee_type <> 'CAPITALIZED') Then
1849
1850 lp_klev_tbl(i).amount := l_amount;
1851 lp_klev_tbl(i).capital_amount := null;
1852
1853 ELSIF(l_fee_type is not null and l_fee_type = 'CAPITALIZED') Then
1854
1855 lp_klev_tbl(i).capital_amount := p_line_item_tbl(i).capital_amount;
1856 lp_klev_tbl(i).amount := null;
1857
1858 END IF;
1859 lp_klev_tbl(i).kle_id := p_line_item_tbl(i).cle_id;
1860
1861 lp_cimv_tbl(i).id := p_line_item_tbl(i).item_id;
1862 lp_cimv_tbl(i).cle_id := p_line_item_tbl(i).cle_id;
1863 lp_cimv_tbl(i).cle_id_for := null;
1864 lp_cimv_tbl(i).chr_id := null;
1865 lp_cimv_tbl(i).exception_yn := 'N';
1866 lp_cimv_tbl(i).number_of_items := 1;
1867 lp_cimv_tbl(i).dnz_chr_id := p_line_item_tbl(i).chr_id;
1868 lp_cimv_tbl(i).object1_id1 := l_item_id1;
1869 lp_cimv_tbl(i).object1_id2 := l_item_id2;
1870 lp_cimv_tbl(i).jtot_object1_code := p_line_item_tbl(i).item_object1_code;
1871
1872 lp_srv_cov_tbl(i).oks_cov_prod_line_id := p_line_item_tbl(i).serv_cov_prd_id;
1873
1874
1875 EXIT WHEN (i = p_line_item_tbl.LAST);
1876 i := p_line_item_tbl.NEXT(i);
1877 END LOOP;
1878 End If;
1879
1880 If l_lty_code = 'LINK_SERV_ASSET' Then
1881
1882 l_cnt := 0;
1883 open is_serv_contract_csr(l_parent_cle_id);
1884 fetch is_serv_contract_csr into l_cnt;
1885 close is_serv_contract_csr;
1886
1887 If( l_cnt = 0) Then -- service contract not attached
1888
1889 UPDATE_CONTRACT_LINE_ITEM (
1890 p_api_version => p_api_version,
1891 p_init_msg_list => OKL_API.G_FALSE,
1892 x_return_status => x_return_status,
1893 x_msg_count => x_msg_count,
1894 x_msg_data => x_msg_data,
1895 p_clev_tbl => lp_clev_tbl,
1896 p_klev_tbl => lp_klev_tbl,
1897 p_cimv_tbl => lp_cimv_tbl,
1898 x_clev_tbl => lx_clev_tbl,
1899 x_klev_tbl => lx_klev_tbl,
1900 x_cimv_tbl => lx_cimv_tbl
1901 );
1902
1903 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1904 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1905 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1906 raise OKC_API.G_EXCEPTION_ERROR;
1907 END IF;
1908
1909 ELSE
1910
1911 okl_service_integration_pub.update_cov_asset_line(
1912 p_api_version => p_api_version,
1913 p_init_msg_list => p_init_msg_list,
1914 x_return_status => x_return_status,
1915 x_msg_count => x_msg_count,
1916 x_msg_data => x_msg_data,
1917 p_clev_tbl => lp_clev_tbl,
1918 p_klev_tbl => lp_klev_tbl,
1919 p_cimv_tbl => lp_cimv_tbl,
1920 p_cov_tbl => lp_srv_cov_tbl,
1921 x_clev_tbl => lx_clev_tbl,
1922 x_klev_tbl => lx_klev_tbl,
1923 x_cimv_tbl => lx_cimv_tbl);
1924
1925 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1926 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1927 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1928 raise OKC_API.G_EXCEPTION_ERROR;
1929 End If;
1930
1931 End If;
1932
1933 Elsif l_lty_code = 'LINK_FEE_ASSET' Then
1934
1935 --Bug# 3877032
1936 j := 0;
1937 For l_old_ast_rec in l_old_ast_csr(p_cim_id => lp_cimv_tbl(i).id)
1938 Loop
1939 If nvl(l_old_ast_rec.object1_id1,okl_api.g_miss_char) <> nvl(lp_cimv_tbl(i).object1_id1,okl_api.g_miss_char) then
1940 l_fin_clev_tbl(j).id := to_number(l_old_ast_rec.object1_id1);
1941 l_fin_klev_tbl(j).id := to_number(l_old_ast_rec.object1_id1);
1942 l_fin_clev_tbl(j).dnz_chr_id := l_old_ast_rec.dnz_chr_id;
1943 End If;
1944 End Loop;
1945
1946 update_contract_line_item(
1947 p_api_version => p_api_version,
1948 p_init_msg_list => p_init_msg_list,
1949 x_return_status => x_return_status,
1950 x_msg_count => x_msg_count,
1951 x_msg_data => x_msg_data,
1952 p_clev_tbl => lp_clev_tbl,
1953 p_klev_tbl => lp_klev_tbl,
1954 p_cimv_tbl => lp_cimv_tbl,
1955 x_clev_tbl => lx_clev_tbl,
1956 x_klev_tbl => lx_klev_tbl,
1957 x_cimv_tbl => lx_cimv_tbl);
1958
1959 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1960 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1961 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1962 raise OKC_API.G_EXCEPTION_ERROR;
1963 End If;
1964
1965 --Bug# 3877032
1966 --update capital amount field
1967 If lx_cimv_tbl.COUNT > 0 then
1968 For i in lx_cimv_tbl.FIRST..lx_cimv_tbl.LAST
1969 Loop
1970
1971 l_fee_type := null;
1972 open l_fee_type_csr(p_cle_id => lx_clev_tbl(i).cle_id);
1973 fetch l_fee_type_csr into l_fee_type;
1974 close l_fee_type_csr;
1975 if nvl(l_fee_type,'GENERAL') = 'CAPITALIZED' then
1976 l_fin_clev_rec.id := to_number(lx_cimv_tbl(i).object1_id1);
1977 l_fin_klev_rec.id := to_number(lx_cimv_tbl(i).object1_id1);
1978 OKL_EXECUTE_FORMULA_PUB.execute(p_api_version => p_api_version,
1979 p_init_msg_list => p_init_msg_list,
1980 x_return_status => x_return_status,
1981 x_msg_count => x_msg_count,
1982 x_msg_data => x_msg_data,
1983 p_formula_name => 'LINE_CAP_AMNT',
1984 p_contract_id => lx_cimv_tbl(i).dnz_chr_id,
1985 p_line_id => to_number(lx_cimv_tbl(i).object1_id1),
1986 x_value => l_fin_klev_rec.capital_amount);
1987 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1988 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1989 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1990 raise OKC_API.G_EXCEPTION_ERROR;
1991 End If;
1992
1993 okl_contract_pub.update_contract_line(p_api_version => p_api_version,
1994 p_init_msg_list => p_init_msg_list,
1995 x_return_status => x_return_status,
1996 x_msg_count => x_msg_count,
1997 x_msg_data => x_msg_data,
1998 p_clev_rec => l_fin_clev_rec,
1999 p_klev_rec => l_fin_klev_rec,
2000 x_clev_rec => lx_fin_clev_rec,
2001 x_klev_rec => lx_fin_klev_rec);
2002 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2003 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2004 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
2005 raise OKC_API.G_EXCEPTION_ERROR;
2006 End If;
2007
2008 --Bug# 4899328
2009 -- Recalculate Asset depreciation cost when there
2010 -- is a change to Capitalized Fee
2011 okl_activate_asset_pvt.recalculate_asset_cost
2012 (p_api_version => p_api_version,
2013 p_init_msg_list => p_init_msg_list,
2014 x_return_status => x_return_status,
2015 x_msg_count => x_msg_count,
2016 x_msg_data => x_msg_data,
2017 p_chr_id => lx_cimv_tbl(i).dnz_chr_id,
2018 p_cle_id => TO_NUMBER(lx_cimv_tbl(i).object1_id1)
2019 );
2020
2021 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2022 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2023 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2024 RAISE OKL_API.G_EXCEPTION_ERROR;
2025 END IF;
2026 --Bug# 4899328
2027
2028 End If;
2029 End Loop;
2030 End If;
2031
2032 --Bug# 3877032
2033 -- : for old assets
2034 If l_fin_klev_tbl.COUNT > 0 then
2035 For j in l_fin_klev_tbl.FIRST..l_fin_klev_tbl.LAST
2036 Loop
2037 OKL_EXECUTE_FORMULA_PUB.execute(p_api_version => p_api_version,
2038 p_init_msg_list => p_init_msg_list,
2039 x_return_status => x_return_status,
2040 x_msg_count => x_msg_count,
2041 x_msg_data => x_msg_data,
2042 p_formula_name => 'LINE_CAP_AMNT',
2043 p_contract_id => l_fin_clev_tbl(j).dnz_chr_id,
2044 p_line_id => l_fin_clev_tbl(j).id,
2045 x_value => l_fin_klev_tbl(j).capital_amount);
2046 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2047 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2048 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
2049 raise OKC_API.G_EXCEPTION_ERROR;
2050 End If;
2051 End Loop;
2052
2053 okl_contract_pub.update_contract_line(p_api_version => p_api_version,
2054 p_init_msg_list => p_init_msg_list,
2055 x_return_status => x_return_status,
2056 x_msg_count => x_msg_count,
2057 x_msg_data => x_msg_data,
2058 p_clev_tbl => l_fin_clev_tbl,
2059 p_klev_tbl => l_fin_klev_tbl,
2060 x_clev_tbl => lx_fin_clev_tbl,
2061 x_klev_tbl => lx_fin_klev_tbl);
2062
2063 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2064 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2065 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
2066 raise OKC_API.G_EXCEPTION_ERROR;
2067 End If;
2068
2069 --Bug# 4899328
2070 -- Recalculate Asset depreciation cost when there
2071 -- is a change to Capitalized Fee
2072 For j in l_fin_klev_tbl.FIRST..l_fin_klev_tbl.LAST
2073 Loop
2074 okl_activate_asset_pvt.recalculate_asset_cost
2075 (p_api_version => p_api_version,
2076 p_init_msg_list => p_init_msg_list,
2077 x_return_status => x_return_status,
2078 x_msg_count => x_msg_count,
2079 x_msg_data => x_msg_data,
2080 p_chr_id => l_fin_clev_tbl(j).dnz_chr_id,
2081 p_cle_id => l_fin_clev_tbl(j).id
2082 );
2083
2084 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2085 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2086 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2087 RAISE OKL_API.G_EXCEPTION_ERROR;
2088 END IF;
2089 End Loop;
2090 --Bug# 4899328
2091
2092 End If;
2093 --Bug# 3877032
2094
2095
2096 End If;
2097
2098 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2099
2100 EXCEPTION
2101 when OKC_API.G_EXCEPTION_ERROR then
2102 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2103 p_api_name => l_api_name,
2104 p_pkg_name => g_pkg_name,
2105 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2106 x_msg_count => x_msg_count,
2107 x_msg_data => x_msg_data,
2108 p_api_type => g_api_type);
2109
2110 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2111 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2112 p_api_name => l_api_name,
2113 p_pkg_name => g_pkg_name,
2114 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2115 x_msg_count => x_msg_count,
2116 x_msg_data => x_msg_data,
2117 p_api_type => g_api_type);
2118
2119 when OTHERS then
2120 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2121 p_api_name => l_api_name,
2122 p_pkg_name => g_pkg_name,
2123 p_exc_name => 'OTHERS',
2124 x_msg_count => x_msg_count,
2125 x_msg_data => x_msg_data,
2126 p_api_type => g_api_type);
2127
2128 END;
2129
2130 PROCEDURE create_contract_line_item(
2131 p_api_version IN NUMBER,
2132 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2133 x_return_status OUT NOCOPY VARCHAR2,
2134 x_msg_count OUT NOCOPY NUMBER,
2135 x_msg_data OUT NOCOPY VARCHAR2,
2136 p_clev_rec IN clev_rec_type,
2137 p_klev_rec IN klev_rec_type,
2138 p_cimv_rec IN cimv_rec_type,
2139 x_clev_rec OUT NOCOPY clev_rec_type,
2140 x_klev_rec OUT NOCOPY klev_rec_type,
2141 x_cimv_rec OUT NOCOPY cimv_rec_type) IS
2142
2143 l_clev_rec clev_rec_type;
2144 l_klev_rec klev_rec_type;
2145 l_cimv_rec cimv_rec_type;
2146
2147 l_chr_id l_clev_rec.dnz_chr_id%type;
2148 l_amt_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
2149
2150 l_api_name CONSTANT VARCHAR2(30) := 'create_contract_line_item';
2151 l_api_version CONSTANT NUMBER := 1.0;
2152 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2153
2154 CURSOR get_k_dates_csr(l_id NUMBER) IS
2155 select chr.start_date, chr.end_date
2156 from okc_k_lines_b chr
2157 where id = l_id;
2158
2159 l_start_date okc_k_lines_b.start_date%type := null;
2160 l_end_date okc_k_lines_b.end_date%type := null;
2161 l_amount number;
2162 l_fee_type okl_k_lines_full_v.fee_type%type := null;
2163
2164 CURSOR get_fee_type_csr(p_chr_id number, p_cle_id number) IS
2165 select cle.fee_type
2166 from okl_k_lines_full_v cle
2167 where cle.id = p_cle_id
2168 and cle.dnz_chr_id = p_chr_id;
2169
2170 BEGIN
2171
2172 -- udhenuko Modification Start. Moving the initialization of records before checking context.
2173 l_klev_rec := p_klev_rec;
2174 l_clev_rec := p_clev_rec;
2175 l_cimv_rec := p_cimv_rec;
2176 l_chr_id := l_clev_rec.dnz_chr_id;
2177
2178 If okl_context.get_okc_org_id is null then
2179 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
2180 End If;
2181 -- udhenuko Modification End.
2182 /*
2183 -- call START_ACTIVITY to create savepoint, check compatibility
2184 -- and initialize message list
2185 x_return_status := OKC_API.START_ACTIVITY(
2186 p_api_name => l_api_name,
2187 p_pkg_name => g_pkg_name,
2188 p_init_msg_list => p_init_msg_list,
2189 l_api_version => l_api_version,
2190 p_api_version => p_api_version,
2191 p_api_type => g_api_type,
2192 x_return_status => x_return_status);
2193
2194 -- check if activity started successfully
2195 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2196 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2197 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2198 raise OKC_API.G_EXCEPTION_ERROR;
2199 End If;
2200 */
2201
2202 IF( l_fee_type is not null and l_fee_type = 'CAPITALIZED') Then
2203
2204 -- Not null Validation for Asset Number
2205 IF ((l_clev_rec.name = OKC_API.G_MISS_CHAR OR l_clev_rec.name IS NULL) AND
2206 (l_klev_rec.capital_amount IS NOT NULL OR l_klev_rec.capital_amount <> OKC_API.G_MISS_NUM)) THEN
2207 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED');
2208 x_return_status := OKC_API.g_ret_sts_error;
2209 RAISE OKC_API.G_EXCEPTION_ERROR;
2210 END IF;
2211
2212 -- Not null Validation for Amount
2213 IF ((l_clev_rec.name <> OKC_API.G_MISS_CHAR OR l_clev_rec.name IS NOT NULL) AND
2214 (l_klev_rec.capital_amount IS NULL OR l_klev_rec.capital_amount = OKC_API.G_MISS_NUM)) THEN
2215
2216 l_amt_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_AMOUNT');
2217 OKC_API.SET_MESSAGE( p_app_name => g_app_name
2218 , p_msg_name => 'OKL_AMOUNT_FORMAT'
2219 , p_token1 => 'COL_NAME'
2220 , p_token1_value => l_amt_ak_prompt
2221 );
2222 x_return_status := OKC_API.g_ret_sts_error;
2223 RAISE OKC_API.G_EXCEPTION_ERROR;
2224 END IF;
2225
2226 --Bug#4664176
2227 --Bug#4635028
2228 --ELSIF( l_fee_type is not null and l_fee_type in ('ROLLOVER','FINANCED')) Then
2229 ELSIF( l_fee_type is not null and l_fee_type <> 'CAPITALIZED') Then
2230
2231 IF( l_klev_rec.capital_amount IS NOT NULL ) Then
2232 l_amount := l_klev_rec.capital_amount;
2233 ELSIF( l_klev_rec.amount IS NOT NULL ) Then
2234 l_amount := l_klev_rec.amount;
2235 END IF;
2236
2237 -- Not null Validation for Asset Number
2238 IF ((l_clev_rec.name = OKC_API.G_MISS_CHAR OR l_clev_rec.name IS NULL) AND
2239 (l_amount IS NOT NULL OR l_amount <> OKC_API.G_MISS_NUM)) THEN
2240
2241 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED');
2242 x_return_status := OKC_API.g_ret_sts_error;
2243 RAISE OKC_API.G_EXCEPTION_ERROR;
2244
2245 END IF;
2246
2247 -- Not null Validation for Amount
2248 IF ((l_clev_rec.name <> OKC_API.G_MISS_CHAR OR l_clev_rec.name IS NOT NULL) AND
2249 (l_amount IS NULL OR l_amount = OKC_API.G_MISS_NUM)) THEN
2250
2251 l_amt_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_AMOUNT');
2252 OKC_API.SET_MESSAGE( p_app_name => g_app_name
2253 , p_msg_name => 'OKL_AMOUNT_FORMAT'
2254 , p_token1 => 'COL_NAME'
2255 , p_token1_value => l_amt_ak_prompt
2256 );
2257 x_return_status := OKC_API.g_ret_sts_error;
2258 RAISE OKC_API.G_EXCEPTION_ERROR;
2259
2260 END IF;
2261
2262 END IF;
2263
2264
2265 Validate_Link_Asset(
2266 p_api_version => p_api_version,
2267 p_init_msg_list => p_init_msg_list,
2268 x_return_status => x_return_status,
2269 x_msg_count => x_msg_count,
2270 x_msg_data => x_msg_data,
2271 p_chr_id => l_clev_rec.dnz_chr_id,
2272 p_parent_cle_id => l_clev_rec.cle_id,
2273 p_id1 => l_cimv_rec.object1_id1,
2274 p_id2 => l_cimv_rec.object1_id2,
2275 p_name => l_clev_rec.name,
2276 p_object_code => l_cimv_rec.jtot_object1_code
2277 );
2278
2279 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2280 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2281 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2282 raise OKC_API.G_EXCEPTION_ERROR;
2283 END IF;
2284
2285 open get_k_dates_csr(l_clev_rec.cle_id);
2286 fetch get_k_dates_csr into l_start_date, l_end_date;
2287 close get_k_dates_csr;
2288
2289 l_clev_rec.start_date := l_start_date;
2290 l_clev_rec.end_date := l_end_date;
2291
2292 okl_contract_pvt.create_contract_line(
2293 p_api_version => p_api_version,
2294 p_init_msg_list => p_init_msg_list,
2295 x_return_status => x_return_status,
2296 x_msg_count => x_msg_count,
2297 x_msg_data => x_msg_data,
2298 p_clev_rec => l_clev_rec,
2299 p_klev_rec => l_klev_rec,
2300 x_clev_rec => x_clev_rec,
2301 x_klev_rec => x_klev_rec);
2302
2303 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2304 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2305 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2306 raise OKC_API.G_EXCEPTION_ERROR;
2307 END IF;
2308
2309 l_cimv_rec.cle_id := x_clev_rec.id;
2310
2311 --
2312 -- call procedure in complex API
2313 --
2314 okl_okc_migration_pvt.create_contract_item(
2315 p_api_version => p_api_version,
2316 p_init_msg_list => p_init_msg_list,
2317 x_return_status => x_return_status,
2318 x_msg_count => x_msg_count,
2319 x_msg_data => x_msg_data,
2320 p_cimv_rec => l_cimv_rec,
2321 x_cimv_rec => x_cimv_rec);
2322
2323 -- check return status
2324 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2325 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2326 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
2327 raise OKC_API.G_EXCEPTION_ERROR;
2328 End If;
2329 /*
2330 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2331 */
2332
2333 EXCEPTION
2334 when OKC_API.G_EXCEPTION_ERROR then
2335 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2336 p_api_name => l_api_name,
2337 p_pkg_name => g_pkg_name,
2338 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2339 x_msg_count => x_msg_count,
2340 x_msg_data => x_msg_data,
2341 p_api_type => g_api_type);
2342
2343 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2344 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2345 p_api_name => l_api_name,
2346 p_pkg_name => g_pkg_name,
2347 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2348 x_msg_count => x_msg_count,
2349 x_msg_data => x_msg_data,
2350 p_api_type => g_api_type);
2351
2352 when OTHERS then
2353 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2354 p_api_name => l_api_name,
2355 p_pkg_name => g_pkg_name,
2356 p_exc_name => 'OTHERS',
2357 x_msg_count => x_msg_count,
2358 x_msg_data => x_msg_data,
2359 p_api_type => g_api_type);
2360 END create_contract_line_item;
2361
2362 -- Start of comments
2363 --
2364 -- Procedure Name : create_contract_line_item
2365 -- Description : creates contract line for shadowed contract
2366 -- Business Rules :
2367 -- Parameters :
2368 -- Version : 1.0
2369 -- End of comments
2370 PROCEDURE create_contract_line_item(
2371 p_api_version IN NUMBER,
2372 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2373 x_return_status OUT NOCOPY VARCHAR2,
2374 x_msg_count OUT NOCOPY NUMBER,
2375 x_msg_data OUT NOCOPY VARCHAR2,
2376 p_clev_tbl IN clev_tbl_type,
2377 p_klev_tbl IN klev_tbl_type,
2378 p_cimv_tbl IN cimv_tbl_type,
2379 x_clev_tbl OUT NOCOPY clev_tbl_type,
2380 x_klev_tbl OUT NOCOPY klev_tbl_type,
2381 x_cimv_tbl OUT NOCOPY cimv_tbl_type) IS
2382
2383 l_api_name CONSTANT VARCHAR2(30) := 'create_contract_line_item';
2384 l_api_version CONSTANT NUMBER := 1.0;
2385 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2386 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2387 i NUMBER;
2388 l_klev_tbl klev_tbl_type := p_klev_tbl;
2389 l_cimv_tbl cimv_tbl_type := p_cimv_tbl;
2390 BEGIN
2391
2392 /*
2393 -- call START_ACTIVITY to create savepoint, check compatibility
2394 -- and initialize message list
2395 x_return_status := OKC_API.START_ACTIVITY(
2396 p_api_name => l_api_name,
2397 p_pkg_name => g_pkg_name,
2398 p_init_msg_list => p_init_msg_list,
2399 l_api_version => l_api_version,
2400 p_api_version => p_api_version,
2401 p_api_type => g_api_type,
2402 x_return_status => x_return_status);
2403
2404 -- check if activity started successfully
2405 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2406 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2407 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2408 raise OKC_API.G_EXCEPTION_ERROR;
2409 End If;
2410 */
2411
2412 If (p_clev_tbl.COUNT > 0) Then
2413 i := p_clev_tbl.FIRST;
2414 LOOP
2415 -- call procedure in complex API for a record
2416 create_contract_line_item(
2417 p_api_version => p_api_version,
2418 p_init_msg_list => p_init_msg_list,
2419 x_return_status => x_return_status,
2420 x_msg_count => x_msg_count,
2421 x_msg_data => x_msg_data,
2422 p_clev_rec => p_clev_tbl(i),
2423 p_klev_rec => l_klev_tbl(i),
2424 p_cimv_rec => l_cimv_tbl(i),
2425 x_clev_rec => x_clev_tbl(i),
2426 x_klev_rec => x_klev_tbl(i),
2427 x_cimv_rec => x_cimv_tbl(i));
2428
2429 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2430 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2431 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2432 raise OKC_API.G_EXCEPTION_ERROR;
2433 End If;
2434
2435 EXIT WHEN (i = p_clev_tbl.LAST);
2436 i := p_clev_tbl.NEXT(i);
2437 END LOOP;
2438
2439 End If;
2440
2441 /*
2442 OKC_API.END_ACTIVITY( x_msg_count => x_msg_count,
2443 x_msg_data => x_msg_data);
2444 */
2445
2446 EXCEPTION
2447 when OKC_API.G_EXCEPTION_ERROR then
2448 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2449 p_api_name => l_api_name,
2450 p_pkg_name => g_pkg_name,
2451 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2452 x_msg_count => x_msg_count,
2453 x_msg_data => x_msg_data,
2454 p_api_type => g_api_type);
2455
2456 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2457 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2458 p_api_name => l_api_name,
2459 p_pkg_name => g_pkg_name,
2460 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2461 x_msg_count => x_msg_count,
2462 x_msg_data => x_msg_data,
2463 p_api_type => g_api_type);
2464
2465 when OTHERS then
2466 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2467 p_api_name => l_api_name,
2468 p_pkg_name => g_pkg_name,
2469 p_exc_name => 'OTHERS',
2470 x_msg_count => x_msg_count,
2471 x_msg_data => x_msg_data,
2472 p_api_type => g_api_type);
2473
2474 END create_contract_line_item;
2475
2476
2477 -- Start of comments
2478 --
2479 -- Procedure Name : update_contract_line_item
2480 -- Description : updates contract line for shadowed contract
2481 -- Business Rules :
2482 -- Parameters :
2483 -- Version : 1.0
2484 -- End of comments
2485 PROCEDURE update_contract_line_item(
2486 p_api_version IN NUMBER,
2487 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2488 x_return_status OUT NOCOPY VARCHAR2,
2489 x_msg_count OUT NOCOPY NUMBER,
2490 x_msg_data OUT NOCOPY VARCHAR2,
2491 p_clev_rec IN clev_rec_type,
2492 p_klev_rec IN klev_rec_type,
2493 p_cimv_rec IN cimv_rec_type,
2494 x_clev_rec OUT NOCOPY clev_rec_type,
2495 x_klev_rec OUT NOCOPY klev_rec_type,
2496 x_cimv_rec OUT NOCOPY cimv_rec_type) IS
2497
2498 l_clev_rec clev_rec_type;
2499 l_klev_rec klev_rec_type;
2500 l_cimv_rec cimv_rec_type;
2501
2502 l_chr_id l_clev_rec.dnz_chr_id%type;
2503 l_amt_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
2504
2505 l_api_name CONSTANT VARCHAR2(30) := 'update_contract_line_item';
2506 l_api_version CONSTANT NUMBER := 1.0;
2507 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2508
2509 CURSOR get_k_dates_csr(l_id NUMBER) IS
2510 select chr.start_date, chr.end_date
2511 from okc_k_lines_b chr
2512 where id = l_id;
2513
2514 l_fee_type okl_k_lines.fee_type%type := null;
2515 l_amount NUMBER := null;
2516 l_start_date okc_k_lines_b.start_date%type := null;
2517 l_end_date okc_k_lines_b.end_date%type := null;
2518
2519 CURSOR get_fee_type_csr(p_chr_id number, p_cle_id number) IS
2520 select cle.fee_type
2521 from okl_k_lines_full_v cle
2522 where cle.id = p_cle_id
2523 and cle.dnz_chr_id = p_chr_id;
2524
2525 -- Bug# 6598350
2526 CURSOR c_orig_cle_csr(p_cle_id IN NUMBER) IS
2527 SELECT cle.start_date
2528 FROM okc_k_lines_b cle
2529 WHERE cle.id = p_cle_id;
2530
2531 l_orig_cle_rec c_orig_cle_csr%ROWTYPE;
2532 BEGIN
2533
2534 -- udhenuko Modification Start. Moving the initialization of records before checking context.
2535 l_klev_rec := p_klev_rec;
2536 l_clev_rec := p_clev_rec;
2537 l_cimv_rec := p_cimv_rec;
2538 l_chr_id := l_clev_rec.dnz_chr_id;
2539
2540 If okl_context.get_okc_org_id is null then
2541 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
2542 End If;
2543 -- udhenuko Modification End.
2544 /*
2545 -- call START_ACTIVITY to create savepoint, check compatibility
2546 -- and initialize message list
2547 x_return_status := OKC_API.START_ACTIVITY(
2548 p_api_name => l_api_name,
2549 p_pkg_name => g_pkg_name,
2550 p_init_msg_list => p_init_msg_list,
2551 l_api_version => l_api_version,
2552 p_api_version => p_api_version,
2553 p_api_type => g_api_type,
2554 x_return_status => x_return_status);
2555
2556 -- check if activity started successfully
2557 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2558 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2559 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2560 raise OKC_API.G_EXCEPTION_ERROR;
2561 End If;
2562 */
2563
2564 l_fee_type := null;
2565 open get_fee_type_csr(l_chr_id,p_clev_rec.cle_id);
2566 fetch get_fee_type_csr into l_fee_type;
2567 close get_fee_type_csr;
2568
2569 IF( l_fee_type is not null and l_fee_type = 'CAPITALIZED') Then
2570
2571 -- Not null Validation for Asset Number
2572 IF ((l_clev_rec.name = OKC_API.G_MISS_CHAR OR l_clev_rec.name IS NULL) AND
2573 (l_klev_rec.capital_amount IS NOT NULL OR l_klev_rec.capital_amount <> OKC_API.G_MISS_NUM)) THEN
2574 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED');
2575 x_return_status := OKC_API.g_ret_sts_error;
2576 RAISE OKC_API.G_EXCEPTION_ERROR;
2577 END IF;
2578
2579 -- Not null Validation for Amount
2580 IF ((l_clev_rec.name <> OKC_API.G_MISS_CHAR OR l_clev_rec.name IS NOT NULL) AND
2581 (l_klev_rec.capital_amount IS NULL OR l_klev_rec.capital_amount = OKC_API.G_MISS_NUM)) THEN
2582
2583 l_amt_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_AMOUNT');
2584 OKC_API.SET_MESSAGE( p_app_name => g_app_name
2585 , p_msg_name => 'OKL_AMOUNT_FORMAT'
2586 , p_token1 => 'COL_NAME'
2587 , p_token1_value => l_amt_ak_prompt
2588 );
2589 x_return_status := OKC_API.g_ret_sts_error;
2590 RAISE OKC_API.G_EXCEPTION_ERROR;
2591 END IF;
2592
2593 --Bug#4664176
2594 --Bug#4635028
2595 --ELSIF( l_fee_type is not null and l_fee_type in ('ROLLOVER','FINANCED')) Then
2596 ELSIF( l_fee_type is not null and l_fee_type <> 'CAPITALIZED') Then
2597
2598 IF( l_klev_rec.capital_amount IS NOT NULL ) Then
2599 l_amount := l_klev_rec.capital_amount;
2600 ELSIF( l_klev_rec.amount IS NOT NULL ) Then
2601 l_amount := l_klev_rec.amount;
2602 END IF;
2603
2604 -- Not null Validation for Asset Number
2605 IF ((l_clev_rec.name = OKC_API.G_MISS_CHAR OR l_clev_rec.name IS NULL) AND
2606 (l_amount IS NOT NULL OR l_amount <> OKC_API.G_MISS_NUM)) THEN
2607
2608 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_LLA_ASSET_REQUIRED');
2609 x_return_status := OKC_API.g_ret_sts_error;
2610 RAISE OKC_API.G_EXCEPTION_ERROR;
2611
2612 END IF;
2613
2614 -- Not null Validation for Amount
2615 IF ((l_clev_rec.name <> OKC_API.G_MISS_CHAR OR l_clev_rec.name IS NOT NULL) AND
2616 (l_amount IS NULL OR l_amount = OKC_API.G_MISS_NUM)) THEN
2617
2618 l_amt_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_AMOUNT');
2619 OKC_API.SET_MESSAGE( p_app_name => g_app_name
2620 , p_msg_name => 'OKL_AMOUNT_FORMAT'
2621 , p_token1 => 'COL_NAME'
2622 , p_token1_value => l_amt_ak_prompt
2623 );
2624 x_return_status := OKC_API.g_ret_sts_error;
2625 RAISE OKC_API.G_EXCEPTION_ERROR;
2626
2627 END IF;
2628
2629 END IF;
2630 /*
2631 Validate_Link_Asset(
2632 p_api_version => p_api_version,
2633 p_init_msg_list => p_init_msg_list,
2634 x_return_status => x_return_status,
2635 x_msg_count => x_msg_count,
2636 x_msg_data => x_msg_data,
2637 p_chr_id => l_clev_rec.dnz_chr_id,
2638 p_parent_cle_id => l_clev_rec.cle_id,
2639 p_id1 => l_cimv_rec.object1_id1,
2640 p_id2 => l_cimv_rec.object1_id2,
2641 p_name => l_clev_rec.name,
2642 p_object_code => l_cimv_rec.jtot_object1_code
2643 );
2644
2645 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2646 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2647 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2648 raise OKC_API.G_EXCEPTION_ERROR;
2649 END IF;
2650 */
2651
2652 -- Bug# 6598350
2653 -- Fetch covered asset line start date for checking
2654 -- whether start date has been changed
2655 -- This check needs to be done only for
2656 -- Service lines
2657 IF l_fee_type IS NULL THEN
2658 OPEN c_orig_cle_csr(p_cle_id => l_clev_rec.id);
2659 FETCH c_orig_cle_csr INTO l_orig_cle_rec;
2660 CLOSE c_orig_cle_csr;
2661 END IF;
2662
2663 open get_k_dates_csr(l_clev_rec.cle_id);
2664 fetch get_k_dates_csr into l_start_date, l_end_date;
2665 close get_k_dates_csr;
2666
2667 l_clev_rec.start_date := l_start_date;
2668 l_clev_rec.end_date := l_end_date;
2669
2670 okl_contract_pvt.update_contract_line(
2671 p_api_version => p_api_version,
2672 p_init_msg_list => p_init_msg_list,
2673 x_return_status => x_return_status,
2674 x_msg_count => x_msg_count,
2675 x_msg_data => x_msg_data,
2676 p_clev_rec => l_clev_rec,
2677 p_klev_rec => l_klev_rec,
2678 x_clev_rec => x_clev_rec,
2679 x_klev_rec => x_klev_rec
2680 );
2681
2682 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2683 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2684 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2685 raise OKC_API.G_EXCEPTION_ERROR;
2686 END IF;
2687
2688 --
2689 -- call procedure in complex API
2690 --
2691 okl_okc_migration_pvt.update_contract_item(
2692 p_api_version => p_api_version,
2693 p_init_msg_list => p_init_msg_list,
2694 x_return_status => x_return_status,
2695 x_msg_count => x_msg_count,
2696 x_msg_data => x_msg_data,
2697 p_cimv_rec => l_cimv_rec,
2698 x_cimv_rec => x_cimv_rec);
2699
2700 -- check return status
2701 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2702 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2703 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
2704 raise OKC_API.G_EXCEPTION_ERROR;
2705 End If;
2706
2707 -- Bug# 6598350
2708 -- When the service line start date is changed, update the
2709 -- start dates for all service sub-line payments based on
2710 -- the new line start date
2711 IF l_fee_type IS NULL THEN
2712 IF (x_clev_rec.start_date <> l_orig_cle_rec.start_date) THEN
2713
2714 OKL_LA_PAYMENTS_PVT.update_pymt_start_date
2715 (p_api_version => p_api_version,
2716 p_init_msg_list => p_init_msg_list,
2717 x_return_status => x_return_status,
2718 x_msg_count => x_msg_count,
2719 x_msg_data => x_msg_data,
2720 p_chr_id => x_clev_rec.dnz_chr_id,
2721 p_cle_id => x_clev_rec.id);
2722
2723 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
2724 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2725 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
2726 raise OKL_API.G_EXCEPTION_ERROR;
2727 End If;
2728
2729 END IF;
2730 END IF;
2731 -- Bug# 6598350
2732
2733 /*
2734 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2735 */
2736
2737 EXCEPTION
2738 when OKC_API.G_EXCEPTION_ERROR then
2739 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2740 p_api_name => l_api_name,
2741 p_pkg_name => g_pkg_name,
2742 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2743 x_msg_count => x_msg_count,
2744 x_msg_data => x_msg_data,
2745 p_api_type => g_api_type);
2746
2747 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2748 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2749 p_api_name => l_api_name,
2750 p_pkg_name => g_pkg_name,
2751 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2752 x_msg_count => x_msg_count,
2753 x_msg_data => x_msg_data,
2754 p_api_type => g_api_type);
2755
2756 when OTHERS then
2757 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2758 p_api_name => l_api_name,
2759 p_pkg_name => g_pkg_name,
2760 p_exc_name => 'OTHERS',
2761 x_msg_count => x_msg_count,
2762 x_msg_data => x_msg_data,
2763 p_api_type => g_api_type);
2764 END update_contract_line_item;
2765
2766
2767 -- Start of comments
2768 --
2769 -- Procedure Name : update_contract_line_item
2770 -- Description : updates contract line for shadowed contract
2771 -- Business Rules :
2772 -- Parameters :
2773 -- Version : 1.0
2774 -- End of comments
2775 PROCEDURE update_contract_line_item(
2776 p_api_version IN NUMBER,
2777 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2778 x_return_status OUT NOCOPY VARCHAR2,
2779 x_msg_count OUT NOCOPY NUMBER,
2780 x_msg_data OUT NOCOPY VARCHAR2,
2781 p_clev_tbl IN clev_tbl_type,
2782 p_klev_tbl IN klev_tbl_type,
2783 p_cimv_tbl IN cimv_tbl_type,
2784 x_clev_tbl OUT NOCOPY clev_tbl_type,
2785 x_klev_tbl OUT NOCOPY klev_tbl_type,
2786 x_cimv_tbl OUT NOCOPY cimv_tbl_type) IS
2787
2788 l_api_name CONSTANT VARCHAR2(30) := 'update_contract_line_item';
2789 l_api_version CONSTANT NUMBER := 1.0;
2790 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2791 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2792 i NUMBER;
2793 l_klev_tbl klev_tbl_type := p_klev_tbl;
2794 l_cimv_tbl cimv_tbl_type := p_cimv_tbl;
2795 BEGIN
2796 /*
2797 -- call START_ACTIVITY to create savepoint, check compatibility
2798 -- and initialize message list
2799 x_return_status := OKC_API.START_ACTIVITY(
2800 p_api_name => l_api_name,
2801 p_pkg_name => g_pkg_name,
2802 p_init_msg_list => p_init_msg_list,
2803 l_api_version => l_api_version,
2804 p_api_version => p_api_version,
2805 p_api_type => g_api_type,
2806 x_return_status => x_return_status);
2807
2808 -- check if activity started successfully
2809 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2810 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2811 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
2812 raise OKC_API.G_EXCEPTION_ERROR;
2813 End If;
2814 */
2815
2816 If (p_clev_tbl.COUNT > 0) Then
2817 i := p_clev_tbl.FIRST;
2818 LOOP
2819 -- call procedure in complex API for a record
2820 update_contract_line_item(
2821 p_api_version => p_api_version,
2822 p_init_msg_list => p_init_msg_list,
2823 x_return_status => x_return_status,
2824 x_msg_count => x_msg_count,
2825 x_msg_data => x_msg_data,
2826 p_clev_rec => p_clev_tbl(i),
2827 p_klev_rec => l_klev_tbl(i),
2828 p_cimv_rec => l_cimv_tbl(i),
2829 x_clev_rec => x_clev_tbl(i),
2830 x_klev_rec => x_klev_tbl(i),
2831 x_cimv_rec => x_cimv_tbl(i));
2832
2833 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2834 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2835 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
2836 raise OKC_API.G_EXCEPTION_ERROR;
2837 End If;
2838
2839 EXIT WHEN (i = p_clev_tbl.LAST);
2840 i := p_clev_tbl.NEXT(i);
2841 END LOOP;
2842
2843 End If;
2844
2845 /*
2846 OKC_API.END_ACTIVITY( x_msg_count => x_msg_count,
2847 x_msg_data => x_msg_data);
2848 */
2849
2850 EXCEPTION
2851 when OKC_API.G_EXCEPTION_ERROR then
2852 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2853 p_api_name => l_api_name,
2854 p_pkg_name => g_pkg_name,
2855 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2856 x_msg_count => x_msg_count,
2857 x_msg_data => x_msg_data,
2858 p_api_type => g_api_type);
2859
2860 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2861 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2862 p_api_name => l_api_name,
2863 p_pkg_name => g_pkg_name,
2864 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2865 x_msg_count => x_msg_count,
2866 x_msg_data => x_msg_data,
2867 p_api_type => g_api_type);
2868
2869 when OTHERS then
2870 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2871 p_api_name => l_api_name,
2872 p_pkg_name => g_pkg_name,
2873 p_exc_name => 'OTHERS',
2874 x_msg_count => x_msg_count,
2875 x_msg_data => x_msg_data,
2876 p_api_type => g_api_type);
2877
2878 END update_contract_line_item;
2879
2880
2881 PROCEDURE delete_contract_line_item(
2882 p_api_version IN NUMBER,
2883 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2884 x_return_status OUT NOCOPY VARCHAR2,
2885 x_msg_count OUT NOCOPY NUMBER,
2886 x_msg_data OUT NOCOPY VARCHAR2,
2887 p_clev_rec IN clev_rec_type,
2888 p_klev_rec IN klev_rec_type,
2889 p_cimv_rec IN cimv_rec_type) IS
2890
2891 /*
2892 -- vthiruva, 09/01/2004
2893 -- START, Code change to enable Business Event
2894 */
2895 --cursor to fetch the line style code for line style id of the record.
2896 CURSOR lty_code_csr(p_id okc_k_lines_b.id%TYPE) IS
2897 SELECT lse.lty_code lty_code, items.object1_id1 asset_id
2898 FROM okc_line_styles_b lse, okc_k_lines_b lines, okc_k_items items
2899 WHERE lines.id = p_id
2900 AND lse.id = lines.lse_id
2901 AND items.cle_id = lines.id;
2902
2903 CURSOR get_serv_chr_from_serv(p_chr_id okc_k_headers_b.id%TYPE,
2904 p_line_id okc_k_lines_b.id%TYPE) IS
2905 SELECT rlobj.object1_id1
2906 FROM okc_k_rel_objs_v rlobj
2907 WHERE rlobj.chr_id = p_chr_id
2908 AND rlobj.cle_id = p_line_id
2909 AND rlobj.rty_code = 'OKLSRV'
2910 AND rlobj.jtot_object1_code = 'OKL_SERVICE_LINE';
2911
2912 l_service_top_line_id okc_k_lines_b.id%TYPE;
2913
2914 CURSOR get_serv_cle_from_serv (p_serv_top_line_id okc_k_lines_b.id%TYPE) IS
2915 SELECT dnz_chr_id
2916 FROM okc_k_lines_b
2917 WHERE id = p_serv_top_line_id;
2918
2919 l_serv_contract_id okc_k_headers_b.id%TYPE;
2920
2921 l_lty_code okc_line_styles_b.lty_code%TYPE;
2922 l_asset_id okc_k_lines_b.id%TYPE;
2923 l_raise_business_event VARCHAR2(1) := OKL_API.G_FALSE;
2924 l_business_event_name WF_EVENTS.NAME%TYPE;
2925 l_parameter_list WF_PARAMETER_LIST_T;
2926
2927 /*
2928 -- vthiruva, 09/01/2004
2929 -- END, Code change to enable Business Event
2930 */
2931
2932 l_clev_rec clev_rec_type;
2933 l_klev_rec klev_rec_type;
2934 l_cimv_rec cimv_rec_type;
2935
2936 l_api_name CONSTANT VARCHAR2(30) := 'delete_contract_line_item';
2937 l_api_version CONSTANT NUMBER := 1.0;
2938 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2939
2940 --Bug# 3877032 : cursor to determine if linked asset line
2941 -- corresponding to CAPITALIZED fee is being deleted
2942 cursor l_cap_fee_asst_csr (p_cle_id in number,
2943 p_cim_id in number) is
2944 select cim.object1_id1,
2945 cim.dnz_chr_id
2946 from okc_k_items cim,
2947 okc_k_lines_b lnk_fee_cleb,
2948 okc_line_styles_b lnk_fee_lseb,
2949 okc_k_lines_b fee_cleb,
2950 okl_k_lines fee_kle
2951 where cim.id = p_cim_id
2952 and lnk_fee_cleb.id = p_cle_id
2953 and cim.dnz_chr_id = lnk_fee_cleb.dnz_chr_id
2954 and lnk_fee_cleb.lse_id = lnk_fee_lseb.id
2955 and lnk_fee_lseb.lty_code = 'LINK_FEE_ASSET'
2956 and fee_cleb.id = lnk_fee_cleb.cle_id
2957 and fee_cleb.dnz_chr_id = lnk_fee_cleb.dnz_chr_id
2958 and fee_kle.id = fee_cleb.id
2959 and nvl(fee_kle.fee_type,'GENERAL') = 'CAPITALIZED';
2960
2961 l_cap_fee_asst_rec l_cap_fee_asst_csr%ROWTYPE;
2962
2963 l_fin_clev_tbl okl_okc_migration_pvt.clev_tbl_type;
2964 l_fin_klev_tbl okl_contract_pub.klev_tbl_type;
2965 lx_fin_clev_tbl okl_okc_migration_pvt.clev_tbl_type;
2966 lx_fin_klev_tbl okl_contract_pub.klev_tbl_type;
2967 i number;
2968 --Bug# 3877032
2969
2970 --Bug# 4899328
2971 --cursor to check if contract is a rebook copy contract
2972 Cursor l_rbk_asst_csr(p_cle_id IN NUMBER) is
2973 Select 'Y' rbk_asst_flag
2974 from okc_k_lines_b cleb,
2975 okc_k_headers_b chrb
2976 where chrb.id = cleb.dnz_chr_id
2977 and chrb.scs_code = 'LEASE'
2978 and chrb.orig_system_source_code = 'OKL_REBOOK'
2979 and cleb.id = p_cle_id
2980 and cleb.orig_system_id1 is not NULL
2981 and exists (select '1'
2982 from okc_k_headers_b orig_chrb,
2983 okc_k_lines_b orig_cleb
2984 where orig_chrb.id = chrb.orig_system_id1
2985 and orig_cleb.id = cleb.orig_system_id1
2986 and orig_cleb.sts_code <> 'ABANDONED'
2987 and orig_cleb.dnz_chr_id = orig_chrb.id);
2988
2989 l_rbk_asst_rec l_rbk_asst_csr%ROWTYPE;
2990 --Bug# 4899328
2991
2992 BEGIN
2993 /*
2994 -- call START_ACTIVITY to create savepoint, check compatibility
2995 -- and initialize message list
2996 x_return_status := OKC_API.START_ACTIVITY(
2997 p_api_name => l_api_name,
2998 p_pkg_name => g_pkg_name,
2999 p_init_msg_list => p_init_msg_list,
3000 l_api_version => l_api_version,
3001 p_api_version => p_api_version,
3002 p_api_type => g_api_type,
3003 x_return_status => x_return_status);
3004
3005 -- check if activity started successfully
3006 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3007 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3008 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3009 raise OKC_API.G_EXCEPTION_ERROR;
3010 End If;
3011 */
3012
3013 --Bug# 4899328
3014 --check if user is trying to delete a covered asset line on a lease rebook copy
3015 l_rbk_asst_rec := Null;
3016 For l_rbk_asst_rec in l_rbk_asst_csr(p_cle_id => p_clev_rec.id)
3017 Loop
3018 If NVL(l_rbk_asst_rec.rbk_asst_flag,'N') = 'Y' then
3019 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
3020 p_msg_name => 'OKL_LA_RBK_COV_ASSET_DELETE');
3021 x_return_status := OKL_API.G_RET_STS_ERROR;
3022 RAISE OKL_API.G_EXCEPTION_ERROR;
3023 End If;
3024 End Loop;
3025 --Bug# 4899328
3026
3027 --Bug# 3877032
3028 i := 0;
3029 For l_cap_fee_asst_rec in l_cap_fee_asst_csr (p_cle_id => p_clev_rec.id,
3030 p_cim_id => p_cimv_rec.id)
3031 Loop
3032 i := i+1;
3033 l_fin_clev_tbl(i).id := to_number(l_cap_fee_asst_rec.object1_id1);
3034 l_fin_klev_tbl(i).id := to_number(l_cap_fee_asst_rec.object1_id1);
3035 l_fin_clev_tbl(i).dnz_chr_id := l_cap_fee_asst_rec.dnz_chr_id;
3036 End Loop;
3037 --Bug# 3877032
3038
3039
3040 l_klev_rec := p_klev_rec;
3041 l_clev_rec := p_clev_rec;
3042 l_cimv_rec := p_cimv_rec;
3043
3044 /*
3045 -- vthiruva, 09/01/2004
3046 -- START, Code change to enable Business Event
3047 */
3048 --fetch the line style code for the record
3049 Open lty_code_csr(p_id => p_clev_rec.id);
3050 Fetch lty_code_csr into l_lty_code, l_asset_id;
3051 Close lty_code_csr;
3052 /*
3053 -- vthiruva, 09/01/2004
3054 -- END, Code change to enable Business Event
3055 */
3056
3057 --
3058 -- call procedure in complex API
3059 --
3060 okl_okc_migration_pvt.delete_contract_item(
3061 p_api_version => p_api_version,
3062 p_init_msg_list => p_init_msg_list,
3063 x_return_status => x_return_status,
3064 x_msg_count => x_msg_count,
3065 x_msg_data => x_msg_data,
3066 p_cimv_rec => l_cimv_rec);
3067
3068 -- check return status
3069 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
3070 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3071 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
3072 raise OKC_API.G_EXCEPTION_ERROR;
3073 End If;
3074
3075 okl_contract_pvt.delete_contract_line(
3076 p_api_version => p_api_version,
3077 p_init_msg_list => p_init_msg_list,
3078 x_return_status => x_return_status,
3079 x_msg_count => x_msg_count,
3080 x_msg_data => x_msg_data,
3081 p_clev_rec => l_clev_rec,
3082 p_klev_rec => l_klev_rec
3083 );
3084
3085 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3086 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3087 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3088 raise OKC_API.G_EXCEPTION_ERROR;
3089 END IF;
3090
3091 --Bug# 3877032
3092 If l_fin_klev_tbl.COUNT > 0 then
3093 For i in l_fin_klev_tbl.FIRST..l_fin_klev_tbl.LAST
3094 Loop
3095 OKL_EXECUTE_FORMULA_PUB.execute(p_api_version => p_api_version,
3096 p_init_msg_list => p_init_msg_list,
3097 x_return_status => x_return_status,
3098 x_msg_count => x_msg_count,
3099 x_msg_data => x_msg_data,
3100 p_formula_name => 'LINE_CAP_AMNT',
3101 p_contract_id => l_fin_clev_tbl(i).dnz_chr_id,
3102 p_line_id => l_fin_clev_tbl(i).id,
3103 x_value => l_fin_klev_tbl(i).capital_amount);
3104 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
3105 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3106 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
3107 raise OKC_API.G_EXCEPTION_ERROR;
3108 End If;
3109 End Loop;
3110
3111 okl_contract_pub.update_contract_line(p_api_version => p_api_version,
3112 p_init_msg_list => p_init_msg_list,
3113 x_return_status => x_return_status,
3114 x_msg_count => x_msg_count,
3115 x_msg_data => x_msg_data,
3116 p_clev_tbl => l_fin_clev_tbl,
3117 p_klev_tbl => l_fin_klev_tbl,
3118 x_clev_tbl => lx_fin_clev_tbl,
3119 x_klev_tbl => lx_fin_klev_tbl);
3120
3121 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
3122 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3123 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
3124 raise OKC_API.G_EXCEPTION_ERROR;
3125 End If;
3126
3127 --Bug# 4899328
3128 -- Recalculate Asset depreciation cost when there
3129 -- is a change to Capitalized Fee
3130 For i in l_fin_klev_tbl.FIRST..l_fin_klev_tbl.LAST
3131 Loop
3132 okl_activate_asset_pvt.recalculate_asset_cost
3133 (p_api_version => p_api_version,
3134 p_init_msg_list => p_init_msg_list,
3135 x_return_status => x_return_status,
3136 x_msg_count => x_msg_count,
3137 x_msg_data => x_msg_data,
3138 p_chr_id => l_fin_clev_tbl(i).dnz_chr_id,
3139 p_cle_id => l_fin_clev_tbl(i).id
3140 );
3141
3142 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3143 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3144 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3145 RAISE OKL_API.G_EXCEPTION_ERROR;
3146 END IF;
3147 End Loop;
3148 --Bug# 4899328
3149
3150 End If;
3151 --Bug# 3877032
3152
3153 /*
3154 -- vthiruva, 09/01/2004
3155 -- START, Code change to enable Business Event
3156 */
3157 IF(l_lty_code = 'LINK_FEE_ASSET')THEN
3158 l_raise_business_event := OKL_API.G_TRUE;
3159 l_business_event_name := G_WF_EVT_ASSET_FEE_REMOVED;
3160 wf_event.AddParameterToList(G_WF_ITM_FEE_LINE_ID, l_clev_rec.cle_id, l_parameter_list);
3161 ELSIF(l_lty_code = 'LINK_SERV_ASSET')THEN
3162 l_raise_business_event := OKL_API.G_TRUE;
3163 l_business_event_name := G_WF_EVT_ASSET_SERV_FEE_RMVD;
3164 wf_event.AddParameterToList(G_WF_ITM_SERV_LINE_ID, l_clev_rec.cle_id, l_parameter_list);
3165 -- check if the service line in context has a service contract associated with it
3166 -- if so, pass the service contract id and service contract line id as parameters
3167 OPEN get_serv_chr_from_serv(l_clev_rec.dnz_chr_id, l_clev_rec.cle_id);
3168 FETCH get_serv_chr_from_serv INTO l_service_top_line_id;
3169 CLOSE get_serv_chr_from_serv;
3170 IF(l_service_top_line_id IS NOT NULL)THEN
3171 OPEN get_serv_cle_from_serv(l_service_top_line_id);
3172 FETCH get_serv_cle_from_serv INTO l_serv_contract_id;
3173 CLOSE get_serv_cle_from_serv;
3174 wf_event.AddParameterToList(G_WF_ITM_SERV_CHR_ID, l_serv_contract_id, l_parameter_list);
3175 wf_event.AddParameterToList(G_WF_ITM_SERV_CLE_ID, l_service_top_line_id, l_parameter_list);
3176 END IF;
3177 END IF;
3178
3179 IF(l_raise_business_event = OKL_API.G_TRUE AND l_business_event_name IS NOT NULL AND
3180 OKL_LLA_UTIL_PVT.is_lease_contract(l_clev_rec.dnz_chr_id)= OKL_API.G_TRUE)THEN
3181 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_ID, l_clev_rec.dnz_chr_id, l_parameter_list);
3182 wf_event.AddParameterToList(G_WF_ITM_ASSET_ID, l_asset_id, l_parameter_list);
3183 raise_business_event(p_api_version => p_api_version,
3184 p_init_msg_list => p_init_msg_list,
3185 x_return_status => x_return_status,
3186 x_msg_count => x_msg_count,
3187 x_msg_data => x_msg_data,
3188 p_event_name => l_business_event_name,
3189 p_event_param_list => l_parameter_list);
3190 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3191 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3192 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3193 RAISE OKL_API.G_EXCEPTION_ERROR;
3194 END IF;
3195 END IF;
3196
3197 /*
3198 -- vthiruva, 09/01/2004
3199 -- END, Code change to enable Business Event
3200 */
3201
3202 /*
3203 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
3204 */
3205
3206 EXCEPTION
3207 when OKC_API.G_EXCEPTION_ERROR then
3208 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3209 p_api_name => l_api_name,
3210 p_pkg_name => g_pkg_name,
3211 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
3212 x_msg_count => x_msg_count,
3213 x_msg_data => x_msg_data,
3214 p_api_type => g_api_type);
3215
3216 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
3217 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3218 p_api_name => l_api_name,
3219 p_pkg_name => g_pkg_name,
3220 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
3221 x_msg_count => x_msg_count,
3222 x_msg_data => x_msg_data,
3223 p_api_type => g_api_type);
3224
3225 when OTHERS then
3226 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3227 p_api_name => l_api_name,
3228 p_pkg_name => g_pkg_name,
3229 p_exc_name => 'OTHERS',
3230 x_msg_count => x_msg_count,
3231 x_msg_data => x_msg_data,
3232 p_api_type => g_api_type);
3233 END delete_contract_line_item;
3234
3235 -- Start of comments
3236 --
3237 -- Procedure Name : delete_contract_line_item
3238 -- Description : deletes contract line for shadowed contract
3239 -- Business Rules : line can be deleted only if there is no sublines attached
3240 -- Parameters :
3241 -- Version : 1.0
3242 -- End of comments
3243 PROCEDURE delete_contract_line_item(
3244 p_api_version IN NUMBER,
3245 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3246 x_return_status OUT NOCOPY VARCHAR2,
3247 x_msg_count OUT NOCOPY NUMBER,
3248 x_msg_data OUT NOCOPY VARCHAR2,
3249 p_clev_tbl IN clev_tbl_type,
3250 p_klev_tbl IN klev_tbl_type,
3251 p_cimv_tbl IN cimv_tbl_type) IS
3252
3253 l_api_name CONSTANT VARCHAR2(30) := 'delete_contract_line_item';
3254 l_api_version CONSTANT NUMBER := 1.0;
3255 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3256 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3257 i NUMBER;
3258 l_klev_tbl klev_tbl_type := p_klev_tbl;
3259 l_cimv_tbl cimv_tbl_type := p_cimv_tbl;
3260 BEGIN
3261 /*
3262 -- call START_ACTIVITY to create savepoint, check compatibility
3263 -- and initialize message list
3264 x_return_status := OKC_API.START_ACTIVITY(
3265 p_api_name => l_api_name,
3266 p_pkg_name => g_pkg_name,
3267 p_init_msg_list => p_init_msg_list,
3268 l_api_version => l_api_version,
3269 p_api_version => p_api_version,
3270 p_api_type => g_api_type,
3271 x_return_status => x_return_status);
3272
3273 -- check if activity started successfully
3274 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3275 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3276 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
3277 raise OKC_API.G_EXCEPTION_ERROR;
3278 End If;
3279 */
3280
3281 If (p_clev_tbl.COUNT > 0) Then
3282 i := p_clev_tbl.FIRST;
3283 LOOP
3284 -- call procedure in complex API for a record
3285 delete_contract_line_item(
3286 p_api_version => p_api_version,
3287 p_init_msg_list => p_init_msg_list,
3288 x_return_status => x_return_status,
3289 x_msg_count => x_msg_count,
3290 x_msg_data => x_msg_data,
3291 p_clev_rec => p_clev_tbl(i),
3292 p_klev_rec => l_klev_tbl(i),
3293 p_cimv_rec => l_cimv_tbl(i));
3294
3295 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
3296 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3297 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
3298 raise OKC_API.G_EXCEPTION_ERROR;
3299 End If;
3300
3301 EXIT WHEN (i = p_clev_tbl.LAST);
3302 i := p_clev_tbl.NEXT(i);
3303 END LOOP;
3304
3305 End If;
3306
3307 /*
3308 OKC_API.END_ACTIVITY( x_msg_count => x_msg_count,
3309 x_msg_data => x_msg_data);
3310 */
3311
3312 EXCEPTION
3313 when OKC_API.G_EXCEPTION_ERROR then
3314 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3315 p_api_name => l_api_name,
3316 p_pkg_name => g_pkg_name,
3317 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
3318 x_msg_count => x_msg_count,
3319 x_msg_data => x_msg_data,
3320 p_api_type => g_api_type);
3321
3322 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
3323 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3324 p_api_name => l_api_name,
3325 p_pkg_name => g_pkg_name,
3326 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
3327 x_msg_count => x_msg_count,
3328 x_msg_data => x_msg_data,
3329 p_api_type => g_api_type);
3330
3331 when OTHERS then
3332 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3333 p_api_name => l_api_name,
3334 p_pkg_name => g_pkg_name,
3335 p_exc_name => 'OTHERS',
3336 x_msg_count => x_msg_count,
3337 x_msg_data => x_msg_data,
3338 p_api_type => g_api_type);
3339
3340 END delete_contract_line_item;
3341
3342 END OKL_CONTRACT_LINE_ITEM_PVT;