[Home] [Help]
PACKAGE BODY: APPS.OKL_CONTRACT_TOP_LINE_PVT
Source
1 package body okl_contract_top_line_pvt as
2 /* $Header: OKLRKTLB.pls 120.10.12010000.2 2009/06/11 04:27:57 rpillay ship $ */
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 G_INVALID_NO_OF_PAYMENTS CONSTANT VARCHAR2(200) := 'OKL_INVALID_NO_OF_PAYMNTS';
15 G_FT_FINANCED CONSTANT VARCHAR2(200) := 'FINANCED';
16 G_FT_ABSORBED CONSTANT VARCHAR2(200) := 'ABSORBED';
17
18 FUNCTION GET_AK_PROMPT(p_ak_region IN VARCHAR2, p_ak_attribute IN VARCHAR2)
19 RETURN VARCHAR2 IS
20
21 CURSOR ak_prompt_csr(p_ak_region VARCHAR2, p_ak_attribute VARCHAR2) IS
22 SELECT a.attribute_label_long
23 FROM ak_region_items ri, AK_REGIONS r, AK_ATTRIBUTES_vL a
24 WHERE ri.region_code = r.region_code
25 AND ri.attribute_code = a.attribute_code
26 AND ri.region_code = p_ak_region
27 AND ri.attribute_code = p_ak_attribute;
28
29 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
30 BEGIN
31 OPEN ak_prompt_csr(p_ak_region, p_ak_attribute);
32 FETCH ak_prompt_csr INTO l_ak_prompt;
33 CLOSE ak_prompt_csr;
34 return(l_ak_prompt);
35 END;
36
37 -- Start of comments
38 --
39 -- Procedure Name : create_contract_link_serv
40 -- Description : link service contract to lease
41 -- Business Rules :
42 -- Parameters :
43 -- Version : 1.0
44 -- End of comments
45
46 PROCEDURE create_contract_link_serv (
47 p_api_version IN NUMBER,
48 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_msg_count OUT NOCOPY NUMBER,
51 x_msg_data OUT NOCOPY VARCHAR2,
52 p_chr_id IN NUMBER,
53 p_contract_number IN VARCHAR2,
54 p_item_name IN VARCHAR2,
55 p_supplier_name IN VARCHAR2,
56 x_cle_id OUT NOCOPY NUMBER ) IS
57
58 CURSOR l_serv_contract_csr IS
59 select id
60 from okc_k_headers_b k
61 where contract_number = p_contract_number
62 and scs_code = 'SERVICE';
63
64 CURSOR l_rel_vers_csr IS
65 Select id
66 from okc_class_operations
67 where cls_code = 'SERVICE'
68 and opn_code = 'CHECK_RULE';
69
70 CURSOR l_serv_contract9_csr IS
71 select id
72 from okc_k_headers_b k
73 where contract_number = p_contract_number
74 and scs_code = 'SERVICE';
75
76 CURSOR l_serv_line_csr IS
77 select to_number(cle_id)
78 from okl_la_link_service_uv
79 where contract_number = p_contract_number
80 and item_name = p_item_name;
81
82 CURSOR l_supp_name_csr IS
83 select id1
84 from okx_vendors_v
85 where name = p_supplier_name;
86
87 CURSOR l_k_sts_csr IS
88 select k.sts_code, sts.ste_code
89 from OKC_K_HEADERS_B K,
90 OKC_STATUSES_B STS
91 where STS.CODE = K.STS_CODE
92 and K.ID = p_chr_id;
93
94 Cursor l_strmtyp_id_csr IS
95 SELECT sty_id
96 FROM okl_strm_tmpt_full_uv
97 WHERE STY_PURPOSE = 'SERVICE_PAYMENT'
98 and exists (SELECT 1
99 FROM OKC_K_HEADERS_B chr,
100 OKL_K_HEADERS khr
101 WHERE chr.id = khr.id
102 AND khr.pdt_id = okl_strm_tmpt_full_uv.PDT_ID
103 AND trunc(chr.start_date) BETWEEN trunc(okl_strm_tmpt_full_uv.START_DATE) AND nvl(trunc(okl_strm_tmpt_full_uv.END_DATE),chr.start_date+1)
104 AND chr.id = p_chr_id)
105 and sty_name = p_item_name;
106
107 l_chr_id okc_k_headers_b.id%type := null;
108 l_s_chr_id okc_k_headers_b.id%type := null;
109 l_s_cle_id okc_k_lines_b.id%type := null;
110 l_supp_id okx_vendors_v.id1%type := null;
111 x_okl_cle_id okc_k_lines_b.id%type := null;
112 l_rel_vers_id number := null;
113 l_strmtyp_id okl_strm_tmpt_full_uv.sty_id%type := null;
114 l_sts_code OKC_K_HEADERS_B.sts_code%type:= null;
115 l_ste_code OKC_STATUSES_B.STE_CODE%type:= null;
116
117 l_api_name CONSTANT VARCHAR2(30) := 'create_contract_link_serv';
118 l_api_version CONSTANT NUMBER := 1.0;
119 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
120 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
121
122 l_auth number := null;
123 l_inv number := null;
124
125 BEGIN
126
127
128 l_chr_id := p_chr_id;
129
130 If okl_context.get_okc_org_id is null then
131 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
132 End If;
133
134 -- call START_ACTIVITY to create savepoint, check compatibility
135 -- and initialize message list
136 l_return_status := OKC_API.START_ACTIVITY(
137 p_api_name => l_api_name,
138 p_pkg_name => g_pkg_name,
139 p_init_msg_list => p_init_msg_list,
140 l_api_version => l_api_version,
141 p_api_version => p_api_version,
142 p_api_type => g_api_type,
143 x_return_status => x_return_status);
144
145 -- check if activity started successfully
146 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
147 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
148 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
149 raise OKC_API.G_EXCEPTION_ERROR;
150 End If;
151
152 If(p_contract_number is null) Then
153 x_return_status := OKC_API.g_ret_sts_error;
154 l_ak_prompt := GET_AK_PROMPT('OKL_LA_DEAL_CREAT', 'OKL_CONTRACT_NUMBER');
155 OKC_API.SET_MESSAGE( p_app_name => g_app_name
156 , p_msg_name => 'OKL_REQUIRED_VALUE'
157 , p_token1 => 'COL_NAME'
158 , p_token1_value => l_ak_prompt
159 -- , p_token1_value => 'a'||l_chr_id
160 );
161 raise OKC_API.G_EXCEPTION_ERROR;
162 End If;
163
164 l_rel_vers_id := null;
165 open l_rel_vers_csr;
166 fetch l_rel_vers_csr into l_rel_vers_id;
167 close l_rel_vers_csr;
168
169 If(l_rel_vers_id is null) Then
170
171 open l_serv_contract9_csr;
172 fetch l_serv_contract9_csr into l_s_chr_id;
173 close l_serv_contract9_csr;
174
175 Else
176
177 open l_serv_contract_csr;
178 fetch l_serv_contract_csr into l_s_chr_id;
179 close l_serv_contract_csr;
180
181 End If;
182
183 If l_s_chr_id is null Then
184 x_return_status := OKC_API.g_ret_sts_error;
185 l_ak_prompt := GET_AK_PROMPT('OKL_LA_DEAL_CREAT', 'OKL_CONTRACT_NUMBER');
186 OKC_API.SET_MESSAGE( p_app_name => g_app_name
187 , p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
188 , p_token1 => 'COL_NAME'
189 , p_token1_value => l_ak_prompt
190 );
191 raise OKC_API.G_EXCEPTION_ERROR;
192 End If;
193
194 If(p_item_name is null) Then
195 x_return_status := OKC_API.g_ret_sts_error;
196 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE');
197 OKC_API.SET_MESSAGE( p_app_name => g_app_name
198 , p_msg_name => 'OKL_REQUIRED_VALUE'
199 , p_token1 => 'COL_NAME'
200 , p_token1_value => l_ak_prompt
201 );
202 raise OKC_API.G_EXCEPTION_ERROR;
203 End If;
204
205 -- If(NOT(p_item_name is not null and p_item_name = 'RELINK_SERV_INTGR')) Then
206
207 l_strmtyp_id := null;
208 open l_strmtyp_id_csr;
209 fetch l_strmtyp_id_csr into l_strmtyp_id;
210 close l_strmtyp_id_csr;
211
212 If(l_strmtyp_id is null) Then
213 x_return_status := OKC_API.g_ret_sts_error;
214 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_PAYMENT_TYPE');
215 If(l_ak_prompt is null) Then
216 l_ak_prompt := 'Payment Type';
217 End If;
218 OKC_API.SET_MESSAGE( p_app_name => g_app_name
219 , p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
220 , p_token1 => 'COL_NAME'
221 , p_token1_value => l_ak_prompt
222 );
223 raise OKC_API.G_EXCEPTION_ERROR;
224 End If;
225
226 -- End If;
227
228 If(p_supplier_name is null) Then
229 x_return_status := OKC_API.g_ret_sts_error;
230 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_SUPPLIER');
231 OKC_API.SET_MESSAGE( p_app_name => g_app_name
232 , p_msg_name => 'OKL_REQUIRED_VALUE'
233 , p_token1 => 'COL_NAME'
234 , p_token1_value => l_ak_prompt
235 );
236 raise OKC_API.G_EXCEPTION_ERROR;
237 End If;
238
239 open l_supp_name_csr;
240 fetch l_supp_name_csr into l_supp_id;
241 close l_supp_name_csr;
242
243 If(l_supp_id is null) Then
244 x_return_status := OKC_API.g_ret_sts_error;
245 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_SUPPLIER');
246 OKC_API.SET_MESSAGE( p_app_name => g_app_name
247 , p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
248 , p_token1 => 'COL_NAME'
249 , p_token1_value => l_ak_prompt
250 );
251 raise OKC_API.G_EXCEPTION_ERROR;
252 End If;
253
254 l_sts_code := null;
255 l_ste_code := null;
256 open l_k_sts_csr;
257 fetch l_k_sts_csr into l_sts_code, l_ste_code;
258 close l_k_sts_csr;
259
260 --If(p_item_name is not null and p_item_name = 'RELINK_SERV_INTGR') Then
261 If(NOT( l_ste_code is not null AND (l_ste_code = 'ENTERED' OR l_ste_code = 'SIGNED'))) Then
262
263 OKL_SERVICE_INTEGRATION_PVT.relink_service_contract(
264 p_api_version => p_api_version,
265 p_init_msg_list => p_init_msg_list,
266 x_return_status => x_return_status,
267 x_msg_count => x_msg_count,
268 x_msg_data => x_msg_data,
269 p_okl_chr_id => l_chr_id,
270 p_oks_chr_id => l_s_chr_id, -- Service Contract Header ID
271 p_supplier_id => l_supp_id,
272 p_sty_id => l_strmtyp_id, -- payment type
273 x_okl_service_line_id => x_okl_cle_id -- Returns Contract Service TOP Line ID
274 );
275
276 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
277 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
278 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
279 raise OKC_API.G_EXCEPTION_ERROR;
280 End If;
281
282 Else
283
284 OKL_SERVICE_INTEGRATION_PVT.create_service_from_oks(
285 p_api_version => p_api_version,
286 p_init_msg_list => p_init_msg_list,
287 x_return_status => x_return_status,
288 x_msg_count => x_msg_count,
289 x_msg_data => x_msg_data,
290 p_okl_chr_id => l_chr_id,
291 p_oks_chr_id => l_s_chr_id, -- Service Contract Header ID
292 p_supplier_id => l_supp_id, -- supplier id
293 p_sty_id => l_strmtyp_id, -- payment type
294 x_okl_service_line_id => x_okl_cle_id -- Returns Contract Service TOP Line ID
295 );
296
297 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
298 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
299 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
300 raise OKC_API.G_EXCEPTION_ERROR;
301 End If;
302
303 End If;
304
305 x_cle_id := x_okl_cle_id;
306
307 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
308
309 EXCEPTION
310 when OKC_API.G_EXCEPTION_ERROR then
311 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
312 p_api_name => l_api_name,
313 p_pkg_name => g_pkg_name,
314 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
315 x_msg_count => x_msg_count,
316 x_msg_data => x_msg_data,
317 p_api_type => g_api_type);
318
319 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
320 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
321 p_api_name => l_api_name,
322 p_pkg_name => g_pkg_name,
323 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
324 x_msg_count => x_msg_count,
325 x_msg_data => x_msg_data,
326 p_api_type => g_api_type);
327
328 when OTHERS then
329 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
330 p_api_name => l_api_name,
331 p_pkg_name => g_pkg_name,
332 p_exc_name => 'OTHERS',
333 x_msg_count => x_msg_count,
334 x_msg_data => x_msg_data,
335 p_api_type => g_api_type);
336 END create_contract_link_serv;
337
338 -- Start of comments
339 --
340 -- Procedure Name : create_contract_link_serv
341 -- Description : link service contract to lease
342 -- Business Rules :
343 -- Parameters :
344 -- Version : 1.0
345 -- End of comments
346
347 PROCEDURE update_contract_link_serv (
348 p_api_version IN NUMBER,
349 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
350 x_return_status OUT NOCOPY VARCHAR2,
351 x_msg_count OUT NOCOPY NUMBER,
352 x_msg_data OUT NOCOPY VARCHAR2,
353 p_chr_id IN NUMBER,
354 p_cle_id IN NUMBER,
355 p_contract_number IN VARCHAR2,
356 p_item_name IN VARCHAR2,
357 p_supplier_name IN VARCHAR2,
358 x_cle_id OUT NOCOPY NUMBER ) IS
359
360 CURSOR l_serv_contract_csr IS
361 select id
362 from okc_k_headers_b k
363 where contract_number = p_contract_number
364 and scs_code = 'SERVICE';
365
366 CURSOR l_rel_vers_csr IS
367 Select id
368 from okc_class_operations
369 where cls_code = 'SERVICE'
370 and opn_code = 'CHECK_RULE';
371
372 CURSOR l_serv_contract9_csr IS
373 select id
374 from okc_k_headers_b k
375 where contract_number = p_contract_number
376 and scs_code = 'SERVICE';
377
378 CURSOR l_serv_line_csr IS
379 select to_number(cle_id)
380 from okl_la_link_service_uv
381 where contract_number = p_contract_number
382 and item_name = p_item_name;
383
384 CURSOR l_supp_name_csr IS
385 select id1
386 from okx_vendors_v
387 where name = p_supplier_name;
388
389 l_chr_id okc_k_headers_b.id%type := null;
390 l_s_chr_id okc_k_headers_b.id%type := null;
391 l_s_cle_id okc_k_lines_b.id%type := null;
392 l_supp_id okx_vendors_v.id1%type := null;
393 x_okl_cle_id okc_k_lines_b.id%type := null;
394 l_rel_vers_id number := null;
395
396 lp_clev_rec OKL_SERVICE_INTEGRATION_PUB.clev_rec_type;
397 lp_klev_rec OKL_SERVICE_INTEGRATION_PUB.klev_rec_type;
398
399
400 l_api_name CONSTANT VARCHAR2(30) := 'update_contract_link_serv';
401 l_api_version CONSTANT NUMBER := 1.0;
402 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
403 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
404
405 BEGIN
406
407 l_chr_id := p_chr_id;
408 If okl_context.get_okc_org_id is null then
409 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
410 End If;
411
412 -- call START_ACTIVITY to create savepoint, check compatibility
413 -- and initialize message list
414 l_return_status := OKC_API.START_ACTIVITY(
415 p_api_name => l_api_name,
416 p_pkg_name => g_pkg_name,
417 p_init_msg_list => p_init_msg_list,
418 l_api_version => l_api_version,
419 p_api_version => p_api_version,
420 p_api_type => g_api_type,
421 x_return_status => x_return_status);
422
423 -- check if activity started successfully
424 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
425 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
426 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
427 raise OKC_API.G_EXCEPTION_ERROR;
428 End If;
429
430 If(p_contract_number is null) Then
431 x_return_status := OKC_API.g_ret_sts_error;
432 l_ak_prompt := GET_AK_PROMPT('OKL_LA_DEAL_CREAT', 'OKL_CONTRACT_NUMBER');
433 OKC_API.SET_MESSAGE( p_app_name => g_app_name
434 , p_msg_name => 'OKL_REQUIRED_VALUE'
435 , p_token1 => 'COL_NAME'
436 , p_token1_value => l_ak_prompt
437 );
438 raise OKC_API.G_EXCEPTION_ERROR;
439 End If;
440
441 l_rel_vers_id := null;
442 open l_rel_vers_csr;
443 fetch l_rel_vers_csr into l_rel_vers_id;
444 close l_rel_vers_csr;
445
446 If(l_rel_vers_id is null) Then
447
448 open l_serv_contract9_csr;
449 fetch l_serv_contract9_csr into l_s_chr_id;
450 close l_serv_contract9_csr;
451
452 Else
453
454 open l_serv_contract_csr;
455 fetch l_serv_contract_csr into l_s_chr_id;
456 close l_serv_contract_csr;
457
458 End If;
459
460 If l_s_chr_id is null Then
461 x_return_status := OKC_API.g_ret_sts_error;
462 l_ak_prompt := GET_AK_PROMPT('OKL_LA_DEAL_CREAT', 'OKL_CONTRACT_NUMBER');
463 OKC_API.SET_MESSAGE( p_app_name => g_app_name
464 , p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
465 , p_token1 => 'COL_NAME'
466 , p_token1_value => l_ak_prompt
467 );
468 raise OKC_API.G_EXCEPTION_ERROR;
469 End If;
470
471 /*
472 If(p_item_name is null) Then
473 x_return_status := OKC_API.g_ret_sts_error;
474 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE');
475 OKC_API.SET_MESSAGE( p_app_name => g_app_name
476 , p_msg_name => 'OKL_REQUIRED_VALUE'
477 , p_token1 => 'COL_NAME'
478 , p_token1_value => l_ak_prompt
479 );
480 raise OKC_API.G_EXCEPTION_ERROR;
481 End If;
482
483
484 open l_serv_line_csr;
485 fetch l_serv_line_csr into l_s_cle_id;
486 close l_serv_line_csr;
487
488 If l_s_cle_id is null Then
489 x_return_status := OKC_API.g_ret_sts_error;
490 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE');
491 OKC_API.SET_MESSAGE( p_app_name => g_app_name
492 , p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
493 , p_token1 => 'COL_NAME'
494 , p_token1_value => l_ak_prompt
495 );
496 raise OKC_API.G_EXCEPTION_ERROR;
497 End If;
498 */
499
500 If(p_supplier_name is null) Then
501 x_return_status := OKC_API.g_ret_sts_error;
502 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_SUPPLIER');
503 OKC_API.SET_MESSAGE( p_app_name => g_app_name
504 , p_msg_name => 'OKL_REQUIRED_VALUE'
505 , p_token1 => 'COL_NAME'
506 , p_token1_value => l_ak_prompt
507 );
508 raise OKC_API.G_EXCEPTION_ERROR;
509 End If;
510
511 open l_supp_name_csr;
512 fetch l_supp_name_csr into l_supp_id;
513 close l_supp_name_csr;
514
515 If(l_supp_id is null) Then
516 x_return_status := OKC_API.g_ret_sts_error;
517 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_LA_SERVICE_SUPPLIER');
518 OKC_API.SET_MESSAGE( p_app_name => g_app_name
519 , p_msg_name => 'OKL_LLA_INVALID_LOV_VALUE'
520 , p_token1 => 'COL_NAME'
521 , p_token1_value => l_ak_prompt
522 );
523 raise OKC_API.G_EXCEPTION_ERROR;
524 End If;
525
526 lp_clev_rec.dnz_chr_id := l_chr_id;
527 lp_clev_rec.id := p_cle_id;
528 lp_klev_rec.id := p_cle_id;
529
530
531 OKL_SERVICE_INTEGRATION_PUB.update_service_line(
532 p_api_version => p_api_version,
533 p_init_msg_list => p_init_msg_list,
534 x_return_status => x_return_status,
535 x_msg_count => x_msg_count,
536 x_msg_data => x_msg_data,
537 p_okl_chr_id => l_chr_id,
538 p_oks_chr_id => l_s_chr_id, -- Service Contract Header ID
539 p_oks_service_line_id => l_s_cle_id, -- Service Contract Service Top Line ID
540 p_supplier_id => l_supp_id,
541 p_clev_rec => lp_clev_rec,
542 p_klev_rec => lp_klev_rec,
543 x_okl_service_line_id => x_okl_cle_id -- Returns Contract Service TOP Line ID
544 );
545
546 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
547 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
548 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
549 raise OKC_API.G_EXCEPTION_ERROR;
550 End If;
551
552 x_cle_id := x_okl_cle_id;
553
554 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
555
556 EXCEPTION
557 when OKC_API.G_EXCEPTION_ERROR then
558 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
559 p_api_name => l_api_name,
560 p_pkg_name => g_pkg_name,
561 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
562 x_msg_count => x_msg_count,
563 x_msg_data => x_msg_data,
564 p_api_type => g_api_type);
565
566 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
567 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
568 p_api_name => l_api_name,
569 p_pkg_name => g_pkg_name,
570 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
571 x_msg_count => x_msg_count,
572 x_msg_data => x_msg_data,
573 p_api_type => g_api_type);
574
575 when OTHERS then
576 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
577 p_api_name => l_api_name,
578 p_pkg_name => g_pkg_name,
579 p_exc_name => 'OTHERS',
580 x_msg_count => x_msg_count,
581 x_msg_data => x_msg_data,
582 p_api_type => g_api_type);
583 END update_contract_link_serv;
584
585
586 PROCEDURE create_contract_top_line(
587 p_api_version IN NUMBER,
588 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
589 x_return_status OUT NOCOPY VARCHAR2,
590 x_msg_count OUT NOCOPY NUMBER,
591 x_msg_data OUT NOCOPY VARCHAR2,
592 p_clev_rec IN clev_rec_type,
593 p_klev_rec IN klev_rec_type,
594 p_cimv_rec IN cimv_rec_type,
595 p_cplv_rec IN cplv_rec_type,
596 x_clev_rec OUT NOCOPY clev_rec_type,
597 x_klev_rec OUT NOCOPY klev_rec_type,
598 x_cimv_rec OUT NOCOPY cimv_rec_type,
599 x_cplv_rec OUT NOCOPY cplv_rec_type) IS
600
601 l_clev_rec clev_rec_type := p_clev_rec;
602 l_klev_rec klev_rec_type := p_klev_rec;
603 l_cimv_rec cimv_rec_type := p_cimv_rec;
604 l_cplv_rec cplv_rec_type := p_cplv_rec;
605
606 l_chr_id l_clev_rec.dnz_chr_id%type;
607
608 l_api_name CONSTANT VARCHAR2(30) := 'create_contract_top_line';
609 l_api_version CONSTANT NUMBER := 1.0;
610 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
611
612 CURSOR get_k_dates_csr(l_id NUMBER) IS
613 select chr.start_date, chr.end_date
614 from okc_k_headers_b chr
615 where chr.id = l_id;
616
617 l_start_date okc_k_headers_b.start_date%type := null;
618 l_end_date okc_k_headers_b.end_date%type := null;
619
620 l_cap_yn OKL_STRMTYP_SOURCE_V.CAPITALIZE_YN%type := null;
621 l_lty_code OKC_LINE_STYLES_V.LTY_CODE%type := null;
622
623 CURSOR get_capitalize_yn_csr(cap_yn VARCHAR2) IS
624 SELECT CAPITALIZE_YN
625 FROM OKL_STRMTYP_SOURCE_V OKL_STRMTYP
626 WHERE OKL_STRMTYP.NAME = cap_yn
627 AND OKL_STRMTYP.STATUS = 'A';
628
629 CURSOR get_lty_code_csr(lse_id NUMBER) IS
630 select lty_code
631 from okc_line_styles_v
632 where id = lse_id;
633
634
635 BEGIN
636
637 l_chr_id := l_clev_rec.dnz_chr_id;
638 If okl_context.get_okc_org_id is null then
639 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
640 End If;
641
642 -- call START_ACTIVITY to create savepoint, check compatibility
643 -- and initialize message list
644 x_return_status := OKC_API.START_ACTIVITY(
645 p_api_name => l_api_name,
646 p_pkg_name => g_pkg_name,
647 p_init_msg_list => p_init_msg_list,
648 l_api_version => l_api_version,
649 p_api_version => p_api_version,
650 p_api_type => g_api_type,
651 x_return_status => x_return_status);
652
653 -- check if activity started successfully
654 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
655 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
656 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
657 raise OKC_API.G_EXCEPTION_ERROR;
658 End If;
659
660 l_clev_rec.name := l_clev_rec.item_description;
661
662 open get_lty_code_csr(l_clev_rec.lse_id);
663 fetch get_lty_code_csr into l_lty_code;
664 close get_lty_code_csr;
665
666 If (l_lty_code is not null and l_lty_code = 'FEE') Then
667
668 open get_capitalize_yn_csr(l_clev_rec.item_description);
669 fetch get_capitalize_yn_csr into l_cap_yn;
670 close get_capitalize_yn_csr;
671 If (l_cap_yn is not null and l_cap_yn = 'Y') Then
672 l_klev_rec.capital_amount := p_klev_rec.amount;
673 Else
674 l_klev_rec.capital_amount := null;
675 End If;
676
677 End If;
678
679
680 If ( (l_clev_rec.start_date is null or l_clev_rec.start_date = OKC_API.G_MISS_DATE)
681 or (l_clev_rec.end_date is null or l_clev_rec.end_date = OKC_API.G_MISS_DATE) )then
682
683 open get_k_dates_csr(l_clev_rec.dnz_chr_id);
684 fetch get_k_dates_csr into l_start_date, l_end_date;
685 close get_k_dates_csr;
686
687 If ( l_clev_rec.start_date is null or l_clev_rec.start_date = OKC_API.G_MISS_DATE) then
688 l_clev_rec.start_date := l_start_date;
689 End If;
690
691 If ( l_clev_rec.end_date is null or l_clev_rec.end_date = OKC_API.G_MISS_DATE) then
692 l_clev_rec.end_date := l_end_date;
693 End If;
694
695 End If;
696
697 --Bug# 4558486
698 -- To validate DFF data for Service Line
699 l_klev_rec.validate_dff_yn := 'Y';
700
701 okl_contract_pvt.create_contract_line(
702 p_api_version => p_api_version,
703 p_init_msg_list => p_init_msg_list,
704 x_return_status => x_return_status,
705 x_msg_count => x_msg_count,
706 x_msg_data => x_msg_data,
707 p_clev_rec => l_clev_rec,
708 p_klev_rec => l_klev_rec,
709 x_clev_rec => x_clev_rec,
710 x_klev_rec => x_klev_rec);
711
712 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
713 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
714 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
715 raise OKC_API.G_EXCEPTION_ERROR;
716 END IF;
717
718 l_cimv_rec.cle_id := x_clev_rec.id;
719
720 okl_okc_migration_pvt.create_contract_item(
721 p_api_version => p_api_version,
722 p_init_msg_list => p_init_msg_list,
723 x_return_status => x_return_status,
724 x_msg_count => x_msg_count,
725 x_msg_data => x_msg_data,
726 p_cimv_rec => l_cimv_rec,
727 x_cimv_rec => x_cimv_rec);
728
729 -- check return status
730 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
731 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
732 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
733 raise OKC_API.G_EXCEPTION_ERROR;
734 End If;
735
736
737 l_cplv_rec.cle_id := x_clev_rec.id;
738
739 --Murthy commented out supplier is not created at line creation.
740 /* if ( l_cplv_rec.object1_id1 is not null and l_cplv_rec.object1_id2 is not null) then
741
742 okl_okc_migration_pvt.create_k_party_role(
743 p_api_version => p_api_version,
744 p_init_msg_list => p_init_msg_list,
745 x_return_status => x_return_status,
746 x_msg_count => x_msg_count,
747 x_msg_data => x_msg_data,
748 p_cplv_rec => l_cplv_rec,
749 x_cplv_rec => x_cplv_rec);
750
751 -- check return status
752 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
753 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
754 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
755 raise OKC_API.G_EXCEPTION_ERROR;
756 End If;
757
758 end if;*/
759
760 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
761 EXCEPTION
762 when OKC_API.G_EXCEPTION_ERROR then
763 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
764 p_api_name => l_api_name,
765 p_pkg_name => g_pkg_name,
766 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
767 x_msg_count => x_msg_count,
768 x_msg_data => x_msg_data,
769 p_api_type => g_api_type);
770
771 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
772 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
773 p_api_name => l_api_name,
774 p_pkg_name => g_pkg_name,
775 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
776 x_msg_count => x_msg_count,
777 x_msg_data => x_msg_data,
778 p_api_type => g_api_type);
779
780 when OTHERS then
781 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
782 p_api_name => l_api_name,
783 p_pkg_name => g_pkg_name,
784 p_exc_name => 'OTHERS',
785 x_msg_count => x_msg_count,
786 x_msg_data => x_msg_data,
787 p_api_type => g_api_type);
788 END create_contract_top_line;
789
790 -- Start of comments
791 --
792 -- Procedure Name : create_contract_top_line
793 -- Description : creates contract line for shadowed contract
794 -- Business Rules :
795 -- Parameters :
796 -- Version : 1.0
797 -- End of comments
798 PROCEDURE create_contract_top_line(
799 p_api_version IN NUMBER,
800 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
801 x_return_status OUT NOCOPY VARCHAR2,
802 x_msg_count OUT NOCOPY NUMBER,
803 x_msg_data OUT NOCOPY VARCHAR2,
804 p_clev_tbl IN clev_tbl_type,
805 p_klev_tbl IN klev_tbl_type,
806 p_cimv_tbl IN cimv_tbl_type,
807 p_cplv_tbl IN cplv_tbl_type,
808 x_clev_tbl OUT NOCOPY clev_tbl_type,
809 x_klev_tbl OUT NOCOPY klev_tbl_type,
810 x_cimv_tbl OUT NOCOPY cimv_tbl_type,
811 x_cplv_tbl OUT NOCOPY cplv_tbl_type) IS
812
813 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_contract_top_line';
814 l_api_version CONSTANT NUMBER := 1.0;
815 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
816 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
817 i NUMBER;
818 l_klev_tbl klev_tbl_type := p_klev_tbl;
819 l_cimv_tbl cimv_tbl_type := p_cimv_tbl;
820 l_cplv_tbl cplv_tbl_type := p_cplv_tbl;
821 BEGIN
822
823 /*
824 -- call START_ACTIVITY to create savepoint, check compatibility
825 -- and initialize message list
826 l_return_status := OKC_API.START_ACTIVITY(
827 p_api_name => l_api_name,
828 p_pkg_name => g_pkg_name,
829 p_init_msg_list => p_init_msg_list,
830 l_api_version => l_api_version,
831 p_api_version => p_api_version,
832 p_api_type => g_api_type,
833 x_return_status => x_return_status);
834
835 -- check if activity started successfully
836 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
837 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
838 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
839 raise OKC_API.G_EXCEPTION_ERROR;
840 End If;
841 */
842
843 If (p_clev_tbl.COUNT > 0) Then
844 i := p_clev_tbl.FIRST;
845 LOOP
846 -- call procedure in complex API for a record
847 create_contract_top_line(
848 p_api_version => p_api_version,
849 p_init_msg_list => p_init_msg_list,
850 x_return_status => x_return_status,
851 x_msg_count => x_msg_count,
852 x_msg_data => x_msg_data,
853 p_clev_rec => p_clev_tbl(i),
854 p_klev_rec => l_klev_tbl(i),
855 p_cimv_rec => l_cimv_tbl(i),
856 p_cplv_rec => l_cplv_tbl(i),
857 x_clev_rec => x_clev_tbl(i),
858 x_klev_rec => x_klev_tbl(i),
859 x_cimv_rec => x_cimv_tbl(i),
860 x_cplv_rec => x_cplv_tbl(i));
861
862 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
863 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
864 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
865 raise OKC_API.G_EXCEPTION_ERROR;
866 End If;
867
868 EXIT WHEN (i = p_clev_tbl.LAST);
869 i := p_clev_tbl.NEXT(i);
870 END LOOP;
871
872 End If;
873
874 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
875 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
876 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
877 raise OKC_API.G_EXCEPTION_ERROR;
878 End If;
879 /*
880 OKC_API.END_ACTIVITY( x_msg_count => x_msg_count,
881 x_msg_data => x_msg_data);
882 */
883 EXCEPTION
884 when OKC_API.G_EXCEPTION_ERROR then
885 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
886 p_api_name => l_api_name,
887 p_pkg_name => g_pkg_name,
888 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
889 x_msg_count => x_msg_count,
890 x_msg_data => x_msg_data,
891 p_api_type => g_api_type);
892
893 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
894 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
895 p_api_name => l_api_name,
896 p_pkg_name => g_pkg_name,
897 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
898 x_msg_count => x_msg_count,
899 x_msg_data => x_msg_data,
900 p_api_type => g_api_type);
901
902 when OTHERS then
903 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
904 p_api_name => l_api_name,
905 p_pkg_name => g_pkg_name,
906 p_exc_name => 'OTHERS',
907 x_msg_count => x_msg_count,
908 x_msg_data => x_msg_data,
909 p_api_type => g_api_type);
910
911 END create_contract_top_line;
912
913
914 -- Start of comments
915 --
916 -- Procedure Name : update_contract_top_line
917 -- Description : updates contract line for shadowed contract
918 -- Business Rules :
919 -- Parameters :
920 -- Version : 1.0
921 -- End of comments
922 PROCEDURE update_contract_top_line(
923 p_api_version IN NUMBER,
924 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
925 x_return_status OUT NOCOPY VARCHAR2,
926 x_msg_count OUT NOCOPY NUMBER,
927 x_msg_data OUT NOCOPY VARCHAR2,
928 p_clev_rec IN clev_rec_type,
929 p_klev_rec IN klev_rec_type,
930 p_cimv_rec IN cimv_rec_type,
931 p_cplv_rec IN cplv_rec_type,
932 x_clev_rec OUT NOCOPY clev_rec_type,
933 x_klev_rec OUT NOCOPY klev_rec_type,
934 x_cimv_rec OUT NOCOPY cimv_rec_type,
935 x_cplv_rec OUT NOCOPY cplv_rec_type) IS
936
937 l_clev_rec clev_rec_type := p_clev_rec;
938 l_klev_rec klev_rec_type := p_klev_rec;
939 l_cimv_rec cimv_rec_type := p_cimv_rec;
940 l_cplv_rec cplv_rec_type := p_cplv_rec;
941
942 l_chr_id l_clev_rec.dnz_chr_id%type;
943
944 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_contract_top_line';
945 l_api_version CONSTANT NUMBER := 1.0;
946 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
947
948 CURSOR get_k_dates_csr(l_id number) IS
949 select chr.start_date, chr.end_date
950 from okc_k_headers_b chr
951 where chr.id = l_id;
952
953 l_start_date okc_k_headers_b.start_date%type := null;
954 l_end_date okc_k_headers_b.end_date%type := null;
955
956 l_cap_yn OKL_STRMTYP_SOURCE_V.CAPITALIZE_YN%type := null;
957 l_lty_code OKC_LINE_STYLES_V.LTY_CODE%type := null;
958
959 CURSOR get_capitalize_yn_csr(cap_yn VARCHAR2) IS
960 SELECT CAPITALIZE_YN
961 FROM OKL_STRMTYP_SOURCE_V OKL_STRMTYP
962 WHERE OKL_STRMTYP.NAME = cap_yn
963 AND OKL_STRMTYP.STATUS = 'A';
964
965 CURSOR get_lty_code_csr(lse_id NUMBER) IS
966 select lty_code
967 from okc_line_styles_v
968 where id = lse_id;
969
970 -- Bug# 6438785
971 CURSOR c_orig_cle_csr(p_cle_id IN NUMBER) IS
972 SELECT cle.start_date
973 FROM okc_k_lines_b cle
974 WHERE cle.id = p_cle_id;
975
976 l_orig_cle_rec c_orig_cle_csr%ROWTYPE;
977
978 -- added below cursor for bug 7323444 -- start
979 CURSOR service_subline_csr (p_cle_id IN NUMBER,
980 p_chr_id IN NUMBER) IS
981 SELECT cle.id,
982 cle.start_date,
983 cle.end_date
984 FROM okc_k_lines_b cle
985 WHERE cle.cle_id = p_cle_id
986 AND cle.dnz_chr_id = p_chr_id;
987
988 l_sub_clev_rec okl_okc_migration_pvt.clev_rec_type;
989 l_sub_klev_rec okl_kle_pvt.klev_rec_type;
990
991 x_sub_clev_rec okl_okc_migration_pvt.clev_rec_type;
992 x_sub_klev_rec okl_kle_pvt.klev_rec_type;
993
994 -- added above cursor for bug 7323444 -- end
995
996 BEGIN
997
998 l_chr_id := l_clev_rec.dnz_chr_id;
999 If okl_context.get_okc_org_id is null then
1000 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
1001 End If;
1002
1003 -- call START_ACTIVITY to create savepoint, check compatibility
1004 -- and initialize message list
1005 x_return_status := OKC_API.START_ACTIVITY(
1006 p_api_name => l_api_name,
1007 p_pkg_name => g_pkg_name,
1008 p_init_msg_list => p_init_msg_list,
1009 l_api_version => l_api_version,
1010 p_api_version => p_api_version,
1011 p_api_type => g_api_type,
1012 x_return_status => x_return_status);
1013
1014 -- check if activity started successfully
1015 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1016 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1017 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1018 raise OKC_API.G_EXCEPTION_ERROR;
1019 End If;
1020
1021 l_clev_rec.name := l_clev_rec.item_description;
1022
1023 open get_lty_code_csr(l_clev_rec.lse_id);
1024 fetch get_lty_code_csr into l_lty_code;
1025 close get_lty_code_csr;
1026
1027 If (l_lty_code is not null and l_lty_code = 'FEE') Then
1028
1029 open get_capitalize_yn_csr(l_clev_rec.item_description);
1030 fetch get_capitalize_yn_csr into l_cap_yn;
1031 close get_capitalize_yn_csr;
1032 If (l_cap_yn is not null and l_cap_yn = 'Y') Then
1033 l_klev_rec.capital_amount := p_klev_rec.amount;
1034 Else
1035 l_klev_rec.capital_amount := null;
1036 End If;
1037
1038 End If;
1039
1040
1041 If ( (l_clev_rec.start_date is null or l_clev_rec.start_date = OKC_API.G_MISS_DATE )
1042 or (l_clev_rec.end_date is null or l_clev_rec.end_date = OKC_API.G_MISS_DATE) )then
1043
1044 open get_k_dates_csr(l_clev_rec.dnz_chr_id);
1045 fetch get_k_dates_csr into l_start_date, l_end_date;
1046 close get_k_dates_csr;
1047
1048 If ( l_clev_rec.start_date is null or l_clev_rec.start_date = OKC_API.G_MISS_DATE) then
1049 l_clev_rec.start_date := l_start_date;
1050 End If;
1051
1052 If ( l_clev_rec.end_date is null or l_clev_rec.end_date = OKC_API.G_MISS_DATE) then
1053 l_clev_rec.end_date := l_end_date;
1054 End If;
1055
1056 End If;
1057
1058 --Bug# 4558486
1059 -- To validate DFF data for Service Line
1060 l_klev_rec.validate_dff_yn := 'Y';
1061
1062 -- Bug# 6438785
1063 -- Fetch original service line start date for checking
1064 -- whether start date has been changed
1065 OPEN c_orig_cle_csr(p_cle_id => l_clev_rec.id);
1066 FETCH c_orig_cle_csr INTO l_orig_cle_rec;
1067 CLOSE c_orig_cle_csr;
1068
1069 okl_contract_pvt.update_contract_line(
1070 p_api_version => p_api_version,
1071 p_init_msg_list => p_init_msg_list,
1072 x_return_status => x_return_status,
1073 x_msg_count => x_msg_count,
1074 x_msg_data => x_msg_data,
1075 p_clev_rec => l_clev_rec,
1076 p_klev_rec => l_klev_rec,
1077 x_clev_rec => x_clev_rec,
1078 x_klev_rec => x_klev_rec
1079 );
1080
1081 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1082 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1083 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1084 raise OKC_API.G_EXCEPTION_ERROR;
1085 END IF;
1086
1087 -- added below for bug 7323444 -- start
1088 For service_subline_rec In service_subline_csr(p_cle_id => l_clev_rec.id,
1089 p_chr_id => l_clev_rec.dnz_chr_id) Loop
1090
1091 If ( (NVL(l_clev_rec.start_date,OKL_API.G_MISS_DATE) <> OKL_API.G_MISS_DATE AND
1092 service_subline_rec.start_date <> l_clev_rec.start_date) OR
1093 (NVL(l_clev_rec.end_date,OKL_API.G_MISS_DATE) <> OKL_API.G_MISS_DATE AND
1094 service_subline_rec.end_date <> l_clev_rec.end_date) ) Then
1095
1096 l_sub_clev_rec.id := service_subline_rec.id;
1097 l_sub_klev_rec.id := service_subline_rec.id;
1098 l_sub_clev_rec.start_date :=l_clev_rec.start_date;
1099 l_sub_clev_rec.end_date :=l_clev_rec.end_date;
1100
1101 OKL_CONTRACT_PVT.update_contract_line(
1102 p_api_version => p_api_version,
1103 p_init_msg_list => p_init_msg_list,
1104 x_return_status => x_return_status,
1105 x_msg_count => x_msg_count,
1106 x_msg_data => x_msg_data,
1107 p_clev_rec => l_sub_clev_rec,
1108 p_klev_rec => l_sub_klev_rec,
1109 x_clev_rec => x_sub_clev_rec,
1110 x_klev_rec => x_sub_klev_rec
1111 );
1112
1113 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1114 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1115 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
1116 RAISE OKL_API.G_EXCEPTION_ERROR;
1117 END IF;
1118 End If;
1119 End Loop;
1120
1121 l_cimv_rec.cle_id := x_clev_rec.id;
1122 -- added above for bug 7323444 -- end
1123
1124 --
1125 -- call procedure in complex API
1126 --
1127 okl_okc_migration_pvt.update_contract_item(
1128 p_api_version => p_api_version,
1129 p_init_msg_list => p_init_msg_list,
1130 x_return_status => x_return_status,
1131 x_msg_count => x_msg_count,
1132 x_msg_data => x_msg_data,
1133 p_cimv_rec => l_cimv_rec,
1134 x_cimv_rec => x_cimv_rec);
1135
1136 -- check return status
1137 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1138 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1139 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1140 raise OKC_API.G_EXCEPTION_ERROR;
1141 End If;
1142
1143 -- Bug# 6438785
1144 -- When the service line start date is changed, update the
1145 -- start dates for all service and sub-line payments based on
1146 -- the new line start date
1147 IF (x_clev_rec.start_date <> l_orig_cle_rec.start_date) THEN
1148
1149 OKL_LA_PAYMENTS_PVT.update_pymt_start_date
1150 (p_api_version => p_api_version,
1151 p_init_msg_list => p_init_msg_list,
1152 x_return_status => x_return_status,
1153 x_msg_count => x_msg_count,
1154 x_msg_data => x_msg_data,
1155 p_chr_id => x_clev_rec.dnz_chr_id,
1156 p_cle_id => x_clev_rec.id);
1157
1158 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
1159 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1160 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
1161 raise OKL_API.G_EXCEPTION_ERROR;
1162 End If;
1163
1164 END IF;
1165 -- Bug# 6438785
1166
1167 --Murthy commented out supplier information is not created at line creation time.
1168 /* if ( l_cplv_rec.object1_id1 is not null and l_cplv_rec.object1_id2 is not null) then
1169
1170 if ( l_cplv_rec.id is null ) then
1171
1172 okl_okc_migration_pvt.create_k_party_role(
1173 p_api_version => p_api_version,
1174 p_init_msg_list => p_init_msg_list,
1175 x_return_status => x_return_status,
1176 x_msg_count => x_msg_count,
1177 x_msg_data => x_msg_data,
1178 p_cplv_rec => l_cplv_rec,
1179 x_cplv_rec => x_cplv_rec);
1180
1181 -- check return status
1182 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1183 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1184 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1185 raise OKC_API.G_EXCEPTION_ERROR;
1186 End If;
1187
1188 else
1189
1190 okl_okc_migration_pvt.update_k_party_role(
1191 p_api_version => p_api_version,
1192 p_init_msg_list => p_init_msg_list,
1193 x_return_status => x_return_status,
1194 x_msg_count => x_msg_count,
1195 x_msg_data => x_msg_data,
1196 p_cplv_rec => l_cplv_rec,
1197 x_cplv_rec => x_cplv_rec);
1198
1199 -- check return status
1200 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1201 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1202 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1203 raise OKC_API.G_EXCEPTION_ERROR;
1204 End If;
1205
1206 end if;
1207
1208 Elsif ( l_cplv_rec.id is not null ) then
1209
1210 -- delete party
1211 okl_okc_migration_pvt.delete_k_party_role(
1212 p_api_version => p_api_version,
1213 p_init_msg_list => p_init_msg_list,
1214 x_return_status => x_return_status,
1215 x_msg_count => x_msg_count,
1216 x_msg_data => x_msg_data,
1217 p_cplv_rec => l_cplv_rec);
1218
1219 -- check return status
1220 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1221 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1222 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1223 raise OKC_API.G_EXCEPTION_ERROR;
1224 End If;
1225 end if;*/
1226
1227 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
1228 x_msg_data => x_msg_data);
1229
1230 EXCEPTION
1231 when OKC_API.G_EXCEPTION_ERROR then
1232 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1233 p_api_name => l_api_name,
1234 p_pkg_name => g_pkg_name,
1235 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1236 x_msg_count => x_msg_count,
1237 x_msg_data => x_msg_data,
1238 p_api_type => g_api_type);
1239
1240 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1241 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1242 p_api_name => l_api_name,
1243 p_pkg_name => g_pkg_name,
1244 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1245 x_msg_count => x_msg_count,
1246 x_msg_data => x_msg_data,
1247 p_api_type => g_api_type);
1248
1249 when OTHERS then
1250 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1251 p_api_name => l_api_name,
1252 p_pkg_name => g_pkg_name,
1253 p_exc_name => 'OTHERS',
1254 x_msg_count => x_msg_count,
1255 x_msg_data => x_msg_data,
1256 p_api_type => g_api_type);
1257 END update_contract_top_line;
1258
1259
1260 -- Start of comments
1261 --
1262 -- Procedure Name : update_contract_top_line
1263 -- Description : updates contract line for shadowed contract
1264 -- Business Rules :
1265 -- Parameters :
1266 -- Version : 1.0
1267 -- End of comments
1268 PROCEDURE update_contract_top_line(
1269 p_api_version IN NUMBER,
1270 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1271 x_return_status OUT NOCOPY VARCHAR2,
1272 x_msg_count OUT NOCOPY NUMBER,
1273 x_msg_data OUT NOCOPY VARCHAR2,
1274 p_clev_tbl IN clev_tbl_type,
1275 p_klev_tbl IN klev_tbl_type,
1276 p_cimv_tbl IN cimv_tbl_type,
1277 p_cplv_tbl IN cplv_tbl_type,
1278 x_clev_tbl OUT NOCOPY clev_tbl_type,
1279 x_klev_tbl OUT NOCOPY klev_tbl_type,
1280 x_cimv_tbl OUT NOCOPY cimv_tbl_type,
1281 x_cplv_tbl OUT NOCOPY cplv_tbl_type) IS
1282
1283 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_contract_top_line';
1284 l_api_version CONSTANT NUMBER := 1.0;
1285 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1286 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1287 i NUMBER;
1288 l_klev_tbl klev_tbl_type := p_klev_tbl;
1289 l_cimv_tbl cimv_tbl_type := p_cimv_tbl;
1290 l_cplv_tbl cplv_tbl_type := p_cplv_tbl;
1291 BEGIN
1292 /*
1293 -- call START_ACTIVITY to create savepoint, check compatibility
1294 -- and initialize message list
1295 l_return_status := OKC_API.START_ACTIVITY(
1296 p_api_name => l_api_name,
1297 p_pkg_name => g_pkg_name,
1298 p_init_msg_list => p_init_msg_list,
1299 l_api_version => l_api_version,
1300 p_api_version => p_api_version,
1301 p_api_type => g_api_type,
1302 x_return_status => x_return_status);
1303
1304 -- check if activity started successfully
1305 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1306 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1307 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
1308 raise OKC_API.G_EXCEPTION_ERROR;
1309 End If;
1310 */
1311
1312 If (p_clev_tbl.COUNT > 0) Then
1313 i := p_clev_tbl.FIRST;
1314 LOOP
1315 -- call procedure in complex API for a record
1316 update_contract_top_line(
1317 p_api_version => p_api_version,
1318 p_init_msg_list => p_init_msg_list,
1319 x_return_status => x_return_status,
1320 x_msg_count => x_msg_count,
1321 x_msg_data => x_msg_data,
1322 p_clev_rec => p_clev_tbl(i),
1323 p_klev_rec => l_klev_tbl(i),
1324 p_cimv_rec => l_cimv_tbl(i),
1325 p_cplv_rec => l_cplv_tbl(i),
1326 x_clev_rec => x_clev_tbl(i),
1327 x_klev_rec => x_klev_tbl(i),
1328 x_cimv_rec => x_cimv_tbl(i),
1329 x_cplv_rec => x_cplv_tbl(i));
1330
1331 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1332 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1333 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1334 raise OKC_API.G_EXCEPTION_ERROR;
1335 End If;
1336
1337 EXIT WHEN (i = p_clev_tbl.LAST);
1338 i := p_clev_tbl.NEXT(i);
1339 END LOOP;
1340
1341 End If;
1342
1343 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1344 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1345 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1346 raise OKC_API.G_EXCEPTION_ERROR;
1347 End If;
1348 /*
1349 OKC_API.END_ACTIVITY( x_msg_count => x_msg_count,
1350 x_msg_data => x_msg_data);
1351 */
1352 EXCEPTION
1353 when OKC_API.G_EXCEPTION_ERROR then
1354 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1355 p_api_name => l_api_name,
1356 p_pkg_name => g_pkg_name,
1357 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1358 x_msg_count => x_msg_count,
1359 x_msg_data => x_msg_data,
1360 p_api_type => g_api_type);
1361
1362 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1363 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1364 p_api_name => l_api_name,
1365 p_pkg_name => g_pkg_name,
1366 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1367 x_msg_count => x_msg_count,
1368 x_msg_data => x_msg_data,
1369 p_api_type => g_api_type);
1370
1371 when OTHERS then
1372 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1373 p_api_name => l_api_name,
1374 p_pkg_name => g_pkg_name,
1375 p_exc_name => 'OTHERS',
1376 x_msg_count => x_msg_count,
1377 x_msg_data => x_msg_data,
1378 p_api_type => g_api_type);
1379
1380 END update_contract_top_line;
1381
1382
1383 PROCEDURE delete_contract_line(
1384 p_api_version IN NUMBER,
1385 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1386 x_return_status OUT NOCOPY VARCHAR2,
1387 x_msg_count OUT NOCOPY NUMBER,
1388 x_msg_data OUT NOCOPY VARCHAR2,
1389 p_chr_id IN number,
1390 p_cle_id IN number) IS
1391
1392 lp_clev_rec OKL_SERVICE_INTEGRATION_PUB.clev_rec_type;
1393 lp_klev_rec OKL_SERVICE_INTEGRATION_PUB.klev_rec_type;
1394
1395 l_api_name CONSTANT VARCHAR2(30) := 'delete_contract_line';
1396 l_api_version CONSTANT NUMBER := 1.0;
1397 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1398
1399 l_chr_id number := null;
1400
1401 BEGIN
1402
1403 l_chr_id := p_chr_id;
1404 If okl_context.get_okc_org_id is null then
1405 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
1406 End If;
1407
1408 -- call START_ACTIVITY to create savepoint, check compatibility
1409 -- and initialize message list
1410 l_return_status := OKC_API.START_ACTIVITY(
1411 p_api_name => l_api_name,
1412 p_pkg_name => g_pkg_name,
1413 p_init_msg_list => p_init_msg_list,
1414 l_api_version => l_api_version,
1415 p_api_version => p_api_version,
1416 p_api_type => g_api_type,
1417 x_return_status => x_return_status);
1418
1419 -- check if activity started successfully
1420 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1421 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1422 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
1423 raise OKC_API.G_EXCEPTION_ERROR;
1424 End If;
1425
1426 lp_clev_rec.dnz_chr_id := l_chr_id;
1427 lp_clev_rec.id := p_cle_id;
1428 lp_klev_rec.id := p_cle_id;
1429
1430 OKL_SERVICE_INTEGRATION_PUB.delete_service_line(
1431 p_api_version => p_api_version,
1432 p_init_msg_list => p_init_msg_list,
1433 x_return_status => x_return_status,
1434 x_msg_count => x_msg_count,
1435 x_msg_data => x_msg_data,
1436 p_clev_rec => lp_clev_rec,
1437 p_klev_rec => lp_klev_rec
1438 );
1439
1440 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1441 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1442 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1443 raise OKC_API.G_EXCEPTION_ERROR;
1444 END IF;
1445
1446 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1447
1448 EXCEPTION
1449 when OKC_API.G_EXCEPTION_ERROR then
1450 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1451 p_api_name => l_api_name,
1452 p_pkg_name => g_pkg_name,
1453 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1454 x_msg_count => x_msg_count,
1455 x_msg_data => x_msg_data,
1456 p_api_type => g_api_type);
1457
1458 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1459 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1460 p_api_name => l_api_name,
1461 p_pkg_name => g_pkg_name,
1462 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1463 x_msg_count => x_msg_count,
1464 x_msg_data => x_msg_data,
1465 p_api_type => g_api_type);
1466
1467 when OTHERS then
1468 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1469 p_api_name => l_api_name,
1470 p_pkg_name => g_pkg_name,
1471 p_exc_name => 'OTHERS',
1472 x_msg_count => x_msg_count,
1473 x_msg_data => x_msg_data,
1474 p_api_type => g_api_type);
1475 END delete_contract_line;
1476
1477
1478 PROCEDURE delete_contract_top_line(
1479 p_api_version IN NUMBER,
1480 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1481 x_return_status OUT NOCOPY VARCHAR2,
1482 x_msg_count OUT NOCOPY NUMBER,
1483 x_msg_data OUT NOCOPY VARCHAR2,
1484 p_clev_rec IN clev_rec_type,
1485 p_klev_rec IN klev_rec_type,
1486 p_cimv_rec IN cimv_rec_type,
1487 p_cplv_rec IN cplv_rec_type) IS
1488
1489 l_clev_rec clev_rec_type := p_clev_rec;
1490 l_klev_rec klev_rec_type := p_klev_rec;
1491 l_cimv_rec cimv_rec_type := p_cimv_rec;
1492 l_cplv_rec cplv_rec_type := p_cplv_rec;
1493
1494 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_contract_top_line';
1495 l_api_version CONSTANT NUMBER := 1.0;
1496 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1497
1498 --Bug# 4558486
1499 l_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
1500
1501 BEGIN
1502
1503 -- call START_ACTIVITY to create savepoint, check compatibility
1504 -- and initialize message list
1505 l_return_status := OKC_API.START_ACTIVITY(
1506 p_api_name => l_api_name,
1507 p_pkg_name => g_pkg_name,
1508 p_init_msg_list => p_init_msg_list,
1509 l_api_version => l_api_version,
1510 p_api_version => p_api_version,
1511 p_api_type => g_api_type,
1512 x_return_status => x_return_status);
1513
1514 -- check if activity started successfully
1515 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1516 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1517 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
1518 raise OKC_API.G_EXCEPTION_ERROR;
1519 End If;
1520
1521 okl_contract_pvt.delete_contract_line(
1522 p_api_version => p_api_version,
1523 p_init_msg_list => p_init_msg_list,
1524 x_return_status => x_return_status,
1525 x_msg_count => x_msg_count,
1526 x_msg_data => x_msg_data,
1527 p_clev_rec => l_clev_rec,
1528 p_klev_rec => l_klev_rec
1529 );
1530
1531 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1532 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1533 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1534 raise OKC_API.G_EXCEPTION_ERROR;
1535 END IF;
1536
1537 --
1538 -- call procedure in complex API
1539 --
1540 okl_okc_migration_pvt.delete_contract_item(
1541 p_api_version => p_api_version,
1542 p_init_msg_list => p_init_msg_list,
1543 x_return_status => x_return_status,
1544 x_msg_count => x_msg_count,
1545 x_msg_data => x_msg_data,
1546 p_cimv_rec => l_cimv_rec);
1547
1548 -- check return status
1549 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1550 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1551 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1552 raise OKC_API.G_EXCEPTION_ERROR;
1553 End If;
1554
1555 --Bug# 4558486: Changed call to okl_k_party_roles_pvt api
1556 -- to delete records in tables
1557 -- okc_k_party_roles_b and okl_k_party_roles
1558 /*
1559 okl_okc_migration_pvt.delete_k_party_role(
1560 p_api_version => p_api_version,
1561 p_init_msg_list => p_init_msg_list,
1562 x_return_status => x_return_status,
1563 x_msg_count => x_msg_count,
1564 x_msg_data => x_msg_data,
1565 p_cplv_rec => l_cplv_rec);
1566 */
1567
1568 l_kplv_rec.id := p_cplv_rec.id;
1569 okl_k_party_roles_pvt.delete_k_party_role(
1570 p_api_version => p_api_version,
1571 p_init_msg_list => p_init_msg_list,
1572 x_return_status => x_return_status,
1573 x_msg_count => x_msg_count,
1574 x_msg_data => x_msg_data,
1575 p_cplv_rec => l_cplv_rec,
1576 p_kplv_rec => l_kplv_rec);
1577
1578 -- check return status
1579 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1580 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1581 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1582 raise OKC_API.G_EXCEPTION_ERROR;
1583 End If;
1584
1585 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
1586 x_msg_data => x_msg_data);
1587 EXCEPTION
1588 when OKC_API.G_EXCEPTION_ERROR then
1589 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1590 p_api_name => l_api_name,
1591 p_pkg_name => g_pkg_name,
1592 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1593 x_msg_count => x_msg_count,
1594 x_msg_data => x_msg_data,
1595 p_api_type => g_api_type);
1596
1597 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1598 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1599 p_api_name => l_api_name,
1600 p_pkg_name => g_pkg_name,
1601 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1602 x_msg_count => x_msg_count,
1603 x_msg_data => x_msg_data,
1604 p_api_type => g_api_type);
1605
1606 when OTHERS then
1607 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1608 p_api_name => l_api_name,
1609 p_pkg_name => g_pkg_name,
1610 p_exc_name => 'OTHERS',
1611 x_msg_count => x_msg_count,
1612 x_msg_data => x_msg_data,
1613 p_api_type => g_api_type);
1614 END delete_contract_top_line;
1615
1616 -- Start of comments
1617 --
1618 -- Procedure Name : delete_contract_top_line
1619 -- Description : deletes contract line for shadowed contract
1620 -- Business Rules : line can be deleted only if there is no sublines attached
1621 -- Parameters :
1622 -- Version : 1.0
1623 -- End of comments
1624 PROCEDURE delete_contract_top_line(
1625 p_api_version IN NUMBER,
1626 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1627 x_return_status OUT NOCOPY VARCHAR2,
1628 x_msg_count OUT NOCOPY NUMBER,
1629 x_msg_data OUT NOCOPY VARCHAR2,
1630 p_clev_tbl IN clev_tbl_type,
1631 p_klev_tbl IN klev_tbl_type,
1632 p_cimv_tbl IN cimv_tbl_type,
1633 p_cplv_tbl IN cplv_tbl_type
1634 ) IS
1635
1636 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_contract_top_line';
1637 l_api_version CONSTANT NUMBER := 1.0;
1638 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1639 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1640 i NUMBER;
1641 l_klev_tbl klev_tbl_type := p_klev_tbl;
1642 l_cimv_tbl cimv_tbl_type := p_cimv_tbl;
1643 l_cplv_tbl cplv_tbl_type := p_cplv_tbl;
1644 BEGIN
1645 /*
1646 -- call START_ACTIVITY to create savepoint, check compatibility
1647 -- and initialize message list
1648 l_return_status := OKC_API.START_ACTIVITY(
1649 p_api_name => l_api_name,
1650 p_pkg_name => g_pkg_name,
1651 p_init_msg_list => p_init_msg_list,
1652 l_api_version => l_api_version,
1653 p_api_version => p_api_version,
1654 p_api_type => g_api_type,
1655 x_return_status => x_return_status);
1656
1657 -- check if activity started successfully
1658 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1659 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1660 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
1661 raise OKC_API.G_EXCEPTION_ERROR;
1662 End If;
1663 */
1664 If (p_clev_tbl.COUNT > 0) Then
1665 i := p_clev_tbl.FIRST;
1666 LOOP
1667 -- call procedure in complex API for a record
1668 delete_contract_top_line(
1669 p_api_version => p_api_version,
1670 p_init_msg_list => p_init_msg_list,
1671 x_return_status => x_return_status,
1672 x_msg_count => x_msg_count,
1673 x_msg_data => x_msg_data,
1674 p_clev_rec => p_clev_tbl(i),
1675 p_klev_rec => l_klev_tbl(i),
1676 p_cimv_rec => l_cimv_tbl(i),
1677 p_cplv_rec => l_cplv_tbl(i)
1678 );
1679
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 EXIT WHEN (i = p_clev_tbl.LAST);
1687 i := p_clev_tbl.NEXT(i);
1688 END LOOP;
1689
1690 End If;
1691
1692 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1693 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1694 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1695 raise OKC_API.G_EXCEPTION_ERROR;
1696 End If;
1697 /*
1698 OKC_API.END_ACTIVITY( x_msg_count => x_msg_count,
1699 x_msg_data => x_msg_data);
1700 */
1701 EXCEPTION
1702 when OKC_API.G_EXCEPTION_ERROR then
1703 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1704 p_api_name => l_api_name,
1705 p_pkg_name => g_pkg_name,
1706 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1707 x_msg_count => x_msg_count,
1708 x_msg_data => x_msg_data,
1709 p_api_type => g_api_type);
1710
1711 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1712 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1713 p_api_name => l_api_name,
1714 p_pkg_name => g_pkg_name,
1715 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1716 x_msg_count => x_msg_count,
1717 x_msg_data => x_msg_data,
1718 p_api_type => g_api_type);
1719
1720 when OTHERS then
1721 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1722 p_api_name => l_api_name,
1723 p_pkg_name => g_pkg_name,
1724 p_exc_name => 'OTHERS',
1725 x_msg_count => x_msg_count,
1726 x_msg_data => x_msg_data,
1727 p_api_type => g_api_type);
1728
1729 END delete_contract_top_line;
1730 -- Start of comments
1731 --
1732 -- Procedure Name : validate_fee_expense_rule
1733 -- Description : validates expense rules at FEE line
1734 -- Business Rules :
1735 -- Parameters :
1736 -- Version : 1.0
1737 -- End of comments
1738
1739 PROCEDURE validate_fee_expense_rule(
1740 p_api_version IN NUMBER,
1741 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1742 x_return_status OUT NOCOPY VARCHAR2,
1743 x_msg_count OUT NOCOPY NUMBER,
1744 x_msg_data OUT NOCOPY VARCHAR2,
1745 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
1746 p_line_id IN OKC_K_LINES_V.ID%TYPE,
1747 p_no_of_period IN NUMBER,
1748 p_frequency IN VARCHAR2,
1749 p_amount_per_period IN NUMBER
1750 ) IS
1751
1752 l_api_name VARCHAR2(35) := 'validate_fee_expense_rule';
1753 l_proc_name VARCHAR2(35) := 'VALIDATE_FEE_EXPENSE_RULE';
1754 l_api_version NUMBER := 1.0;
1755
1756 l_id1 VARCHAR2(30);
1757 scscode OKC_K_HEADERS_B.SCS_CODE%TYPE ;
1758
1759 CURSOR contract_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
1760 p_line_id OKC_K_LINES_V.ID%TYPE) IS
1761 SELECT line.start_date,
1762 line.end_date,
1763 line.amount,
1764 line.capital_amount,
1765 style.lty_code,
1766 line.initial_direct_cost,
1767 line.fee_type
1768 FROM okc_k_headers_b head,
1769 okl_k_lines_full_v line,
1770 okc_line_styles_b style
1771 WHERE head.id = line.dnz_chr_id
1772 AND line.lse_id = style.id
1773 AND head.id = p_chr_id
1774 AND line.id = p_line_id;
1775 CURSOR scscode_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE ) IS
1776 SELECT head.scs_code
1777 FROM okc_k_headers_b head
1778 WHERE head.id = p_chr_id ;
1779
1780 CURSOR strm_cap_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE,
1781 p_line_id OKC_K_LINES_B.ID%TYPE) IS
1782 SELECT stream.capitalize_yn
1783 FROM okl_k_lines_full_v line,
1784 okc_k_items_v item,
1785 okl_strmtyp_source_v stream
1786 WHERE line.id = p_line_id
1787 AND line.dnz_chr_id = p_chr_id
1788 AND line.id = item.cle_id
1789 AND item.object1_id1 = stream.id1;
1790
1791 CURSOR freq_csr (p_frequency VARCHAR2) IS
1792 SELECT id1
1793 FROM okl_time_units_v
1794 WHERE name = p_frequency
1795 AND status = 'A'
1796 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) AND
1797 NVL(TRUNC(end_date_active), TRUNC(SYSDATE));
1798
1799 l_start_date OKC_K_lineS_B.START_DATE%TYPE;
1800 l_end_date OKC_K_lineS_B.END_DATE%TYPE;
1801 l_amount NUMBER;
1802 l_capital_amount NUMBER;
1803 l_line_type OKC_LINE_STYLES_B.LTY_CODE%TYPE;
1804 l_fee_type OKL_K_LINES.FEE_TYPE%TYPE := null;
1805 l_cap_yn VARCHAR2(3);
1806 l_mult_factor NUMBER;
1807 l_line_amount NUMBER;
1808 l_initial_direct_cost okl_k_lines.initial_direct_cost%type := null;
1809 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
1810
1811 l_clev_rec clev_rec_type ;
1812 l_klev_rec klev_rec_type ;
1813 x_clev_rec clev_rec_type ;
1814 x_klev_rec klev_rec_type ;
1815
1816 BEGIN
1817
1818 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1819
1820 -- call START_ACTIVITY to create savepoint, check compatibility
1821 -- and initialize message list
1822 x_return_status := OKL_API.START_ACTIVITY(
1823 p_api_name => l_api_name,
1824 p_pkg_name => G_PKG_NAME,
1825 p_init_msg_list => p_init_msg_list,
1826 l_api_version => l_api_version,
1827 p_api_version => p_api_version,
1828 p_api_type => G_API_TYPE,
1829 x_return_status => x_return_status);
1830
1831 -- check if activity started successfully
1832 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1833 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1834 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
1835 raise OKL_API.G_EXCEPTION_ERROR;
1836 END IF;
1837
1838 If(p_chr_id is null or p_chr_id = OKL_API.G_MISS_NUM) Then
1839 x_return_status := OKL_API.g_ret_sts_error;
1840 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1841 , p_msg_name => 'OKL_INVALID_VALUE'
1842 , p_token1 => 'COL_NAME'
1843 , p_token1_value => 'CHR_ID'
1844 );
1845 raise OKL_API.G_EXCEPTION_ERROR;
1846 End If;
1847
1848
1849 If(p_line_id is null or p_line_id = OKL_API.G_MISS_NUM) Then
1850 x_return_status := OKL_API.g_ret_sts_error;
1851 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1852 , p_msg_name => 'OKL_INVALID_VALUE'
1853 , p_token1 => 'COL_NAME'
1854 , p_token1_value => 'CLE_ID'
1855 );
1856 raise OKL_API.G_EXCEPTION_ERROR;
1857 End If;
1858
1859 OPEN contract_csr (p_chr_id,
1860 p_line_id);
1861 FETCH contract_csr INTO l_start_date,
1862 l_end_date,
1863 l_amount,
1864 l_capital_amount,
1865 l_line_type,
1866 l_initial_direct_cost,
1867 l_fee_type;
1868 CLOSE contract_csr;
1869 OPEN scscode_csr (p_chr_id);
1870 FETCH scscode_csr INTO scscode;
1871 CLOSE scscode_csr ;
1872 if(scscode='INVESTOR' and p_amount_per_period < 0) then
1873
1874 x_return_status := OKL_API.g_ret_sts_error;
1875 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1876 , p_msg_name => 'OKL_PRDAMNT_CHCK'
1877 );
1878 raise OKL_API.G_EXCEPTION_ERROR;
1879 End If;
1880
1881 IF (l_line_type = 'FEE') THEN
1882
1883 If(p_no_of_period is null or p_no_of_period = OKL_API.G_MISS_NUM) Then
1884 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_NO_OF_PERIOD');
1885 x_return_status := OKL_API.g_ret_sts_error;
1886 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1887 , p_msg_name => 'OKL_REQUIRED_VALUE'
1888 , p_token1 => 'COL_NAME'
1889 , p_token1_value => l_ak_prompt
1890 );
1891 raise OKL_API.G_EXCEPTION_ERROR;
1892 End If;
1893
1894 If(p_amount_per_period is null or p_amount_per_period = OKL_API.G_MISS_NUM) Then
1895 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_AMT_PER_PERIOD');
1896 x_return_status := OKL_API.g_ret_sts_error;
1897 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1898 , p_msg_name => 'OKL_REQUIRED_VALUE'
1899 , p_token1 => 'COL_NAME'
1900 , p_token1_value => l_ak_prompt
1901 );
1902 raise OKL_API.G_EXCEPTION_ERROR;
1903 End If;
1904
1905 If(p_frequency is null or p_frequency = OKL_API.G_MISS_CHAR) Then
1906 l_ak_prompt := GET_AK_PROMPT('OKL_LA_SERVICE_LINE', 'OKL_FREEQUENCY');
1907 x_return_status := OKL_API.g_ret_sts_error;
1908 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1909 , p_msg_name => 'OKL_REQUIRED_VALUE'
1910 , p_token1 => 'COL_NAME'
1911 , p_token1_value => l_ak_prompt
1912 );
1913 raise OKL_API.G_EXCEPTION_ERROR;
1914 End If;
1915
1916 End If;
1917
1918 -- Fee Expense rules are only valid for FEE and SERVICE line type
1919 IF (l_line_type IN ('FEE', 'SOLD_SERVICE')) THEN
1920
1921 --Bug# 4959361
1922 OKL_LLA_UTIL_PVT.check_line_update_allowed
1923 (p_api_version => p_api_version,
1924 p_init_msg_list => p_init_msg_list,
1925 x_return_status => x_return_status,
1926 x_msg_count => x_msg_count,
1927 x_msg_data => x_msg_data,
1928 p_cle_id => p_line_id);
1929
1930 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1931 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1932 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1933 RAISE OKL_API.G_EXCEPTION_ERROR;
1934 END IF;
1935 --Bug# 4959361
1936
1937 l_id1 := '?';
1938 IF (p_frequency IS NOT NULL) THEN
1939
1940 OPEN freq_csr (p_frequency);
1941 FETCH freq_csr INTO l_id1;
1942 CLOSE freq_csr;
1943
1944 IF (l_id1 = '?') THEN
1945 okl_api.set_message(
1946 G_APP_NAME,
1947 G_INVALID_VALUE,
1948 'COL_NAME',
1949 'FREQUENCY'
1950 );
1951
1952 x_return_status := OKL_API.G_RET_STS_ERROR;
1953 END IF;
1954
1955 IF (p_no_of_period IS NULL) THEN
1956 okl_api.set_message(
1957 G_APP_NAME,
1958 G_INCOMPLETE_RULE,
1959 'COL_NAME',
1960 'NO_OF_PERIOD'
1961 );
1962
1963 x_return_status := OKL_API.G_RET_STS_ERROR;
1964 ELSE
1965 IF (p_amount_per_period IS NULL) THEN
1966 okl_api.set_message(
1967 G_APP_NAME,
1968 G_INCOMPLETE_RULE,
1969 'COL_NAME',
1970 'AMOUNT_PER_PERIOD'
1971 );
1972
1973 x_return_status := OKL_API.G_RET_STS_ERROR;
1974 END IF;
1975 END IF;
1976 ELSE
1977 okl_api.set_message(
1978 G_APP_NAME,
1979 G_INCOMPLETE_RULE,
1980 'COL_NAME',
1981 'FREQUENCY'
1982 );
1983
1984 x_return_status := OKL_API.G_RET_STS_ERROR;
1985 END IF;
1986
1987 ELSIF (p_no_of_period IS NOT NULL
1988 OR
1989 p_frequency IS NOT NULL
1990 OR
1991 p_amount_per_period IS NOT NULL
1992 ) THEN
1993 okl_api.set_message(
1994 G_APP_NAME,
1995 G_INVALID_LINE_RULE,
1996 'VALUE',
1997 'FEE_NO_OF_PERIOD, FREQUENCY OR AMOUNT_PER_PERIOD',
1998 'LINE_TYPE',
1999 l_line_type,
2000 'ACT_LINE_TYPE',
2001 'FEE or SERVICE'
2002 );
2003
2004 x_return_status := OKL_API.G_RET_STS_ERROR;
2005
2006 END IF;
2007
2008 -- cross validation
2009
2010 IF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
2011
2012 OPEN strm_cap_csr (p_chr_id,
2013 p_line_id);
2014 FETCH strm_cap_csr INTO l_cap_yn;
2015 CLOSE strm_cap_csr;
2016
2017 IF (l_cap_yn = 'Y') THEN
2018 l_line_amount := l_capital_amount;
2019 ELSE
2020 l_line_amount := l_amount;
2021 END IF;
2022
2023 IF (l_id1 = 'M') THEN
2024 l_mult_factor := 1;
2025 ELSIF (l_id1 = 'Q') THEN
2026 l_mult_factor := 3;
2027 ELSIF (l_id1 = 'S') THEN
2028 l_mult_factor := 6;
2029 ELSIF (l_id1 = 'A') THEN
2030 l_mult_factor := 12;
2031 ELSE
2032 okl_api.set_message(
2033 G_APP_NAME,
2034 G_UOM_SETUP_ERROR,
2035 'COL_VALUE',
2036 l_id1
2037 );
2038 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2039 END IF;
2040
2041 IF (l_end_date < (ADD_MONTHS(l_start_date, p_no_of_period * l_mult_factor)-1)) THEN
2042 okl_api.set_message(
2043 G_APP_NAME,
2044 G_INVALID_PERIOD,
2045 'START_DATE',
2046 l_start_date,
2047 'END_DATE',
2048 l_end_date
2049 );
2050 RAISE OKL_API.G_EXCEPTION_ERROR;
2051 END IF;
2052
2053 If(l_fee_type is not null and (l_fee_type = G_FT_ABSORBED or l_fee_type = G_FT_FINANCED) and p_no_of_period <> 1) Then
2054 okl_api.set_message(
2055 G_APP_NAME,
2056 G_INVALID_NO_OF_PAYMENTS
2057 );
2058 RAISE OKL_API.G_EXCEPTION_ERROR;
2059 End If;
2060
2061 -- this validation is commented for summing recurring exp amt to service/fee line
2062 /*
2063 IF (l_line_amount <> (p_amount_per_period * p_no_of_period)) THEN
2064 okl_api.set_message(
2065 G_APP_NAME,
2066 G_INVALID_EXP_AMOUNT
2067 );
2068 RAISE OKL_API.G_EXCEPTION_ERROR;
2069 END IF;
2070 */
2071 -- update line amount with recurring exp amount
2072
2073 IF (l_line_type IN ('FEE', 'SOLD_SERVICE')) THEN
2074
2075 l_clev_rec.id := p_line_id;
2076 l_klev_rec.id := p_line_id;
2077 l_clev_rec.dnz_chr_id := p_chr_id;
2078 l_clev_rec.chr_id := p_chr_id;
2079
2080 l_line_amount := p_amount_per_period * p_no_of_period;
2081 l_klev_rec.amount := l_line_amount;
2082 IF (l_cap_yn = 'Y') THEN
2083 l_klev_rec.capital_amount := l_line_amount;
2084 END IF;
2085
2086 IF (l_line_type = 'FEE' and l_initial_direct_cost is not null and l_initial_direct_cost > l_klev_rec.amount) THEN
2087 x_return_status := OKL_API.g_ret_sts_error;
2088 OKL_API.SET_MESSAGE( p_app_name => g_app_name
2089 , p_msg_name => 'OKL_LLA_IDC_FEE'
2090 );
2091 raise OKL_API.G_EXCEPTION_ERROR;
2092 END IF;
2093
2094 okl_contract_pvt.update_contract_line(
2095 p_api_version => p_api_version,
2096 p_init_msg_list => p_init_msg_list,
2097 x_return_status => x_return_status,
2098 x_msg_count => x_msg_count,
2099 x_msg_data => x_msg_data,
2100 p_clev_rec => l_clev_rec,
2101 p_klev_rec => l_klev_rec,
2102 x_clev_rec => x_clev_rec,
2103 x_klev_rec => x_klev_rec
2104 );
2105
2106 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2107 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2108 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2109 raise OKL_API.G_EXCEPTION_ERROR;
2110 END IF;
2111
2112 END IF;
2113
2114 END IF;
2115
2116 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2117
2118
2119 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2120 x_msg_data => x_msg_data);
2121
2122 EXCEPTION
2123
2124 when OKL_API.G_EXCEPTION_ERROR then
2125 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2126 p_api_name => l_api_name,
2127 p_pkg_name => G_PKG_NAME,
2128 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2129 x_msg_count => x_msg_count,
2130 x_msg_data => x_msg_data,
2131 p_api_type => G_API_TYPE);
2132
2133 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
2134 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2135 p_api_name => l_api_name,
2136 p_pkg_name => G_PKG_NAME,
2137 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2138 x_msg_count => x_msg_count,
2139 x_msg_data => x_msg_data,
2140 p_api_type => G_API_TYPE);
2141
2142 when OTHERS then
2143 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2144 p_api_name => l_api_name,
2145 p_pkg_name => G_PKG_NAME,
2146 p_exc_name => 'OTHERS',
2147 x_msg_count => x_msg_count,
2148 x_msg_data => x_msg_data,
2149 p_api_type => G_API_TYPE);
2150
2151 END validate_fee_expense_rule;
2152
2153
2154 -- Start of comments
2155 --
2156 -- Procedure Name : validate_passthru_rule
2157 -- Description : validates Passthru rules at SERVICE and FEE line
2158 -- Business Rules :
2159 -- Parameters :
2160 -- Version : 1.0
2161 -- End of comments
2162 PROCEDURE validate_passthru_rule(
2163 p_api_version IN NUMBER,
2164 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2165 x_return_status OUT NOCOPY VARCHAR2,
2166 x_msg_count OUT NOCOPY NUMBER,
2167 x_msg_data OUT NOCOPY VARCHAR2,
2168 p_line_id IN OKC_K_LINES_V.ID%TYPE,
2169 p_vendor_id IN NUMBER,
2170 p_payment_term IN VARCHAR2,
2171 p_payment_term_id IN NUMBER,
2172 p_pay_to_site IN VARCHAR2,
2173 p_pay_to_site_id IN NUMBER,
2174 p_payment_method_code IN VARCHAR2,
2175 x_payment_term_id1 OUT NOCOPY VARCHAR2,
2176 x_pay_site_id1 OUT NOCOPY VARCHAR2,
2177 x_payment_method_id1 OUT NOCOPY VARCHAR2
2178 ) IS
2179
2180 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_CONTRACT_TOP_LINE_PVT';
2181 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
2182 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
2183
2184 G_INVALID_VALUE CONSTANT VARCHAR2(200) := 'OKL_INVALID_VALUE';
2185 G_INVALID_LINE_RULE CONSTANT VARCHAR2(200) := 'OKL_LLA_INVALID_LINE_RULE';
2186
2187
2188 l_api_name VARCHAR2(35) := 'validate_passthru_rule';
2189 l_proc_name VARCHAR2(35) := 'VALIDATE_PASSTHRU_RULE';
2190 l_api_version NUMBER := 1.0;
2191
2192 CURSOR line_style_csr (p_line_id OKC_K_LINES_V.ID%TYPE) IS
2193 SELECT style.lty_code
2194 FROM okc_k_lines_b line,
2195 okc_line_styles_b style
2196 WHERE line.lse_id = style.id
2197 AND line.id = p_line_id;
2198
2199 CURSOR pay_term_csr (p_id NUMBER,
2200 p_name VARCHAR2) IS
2201 SELECT id1
2202 FROM okx_payables_terms_v
2203 WHERE (name = p_name
2204 AND
2205 p_name IS NOT NULL)
2206 OR (id1 = p_id
2207 AND
2208 p_id IS NOT NULL)
2209 AND status = 'A'
2210 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) AND
2211 NVL(TRUNC(end_date_active), TRUNC(SYSDATE));
2212
2213 CURSOR pay_site_csr (p_id NUMBER,
2214 p_name VARCHAR2,
2215 p_vendor_id NUMBER) IS
2216
2217 SELECT id1
2218 FROM okx_vendor_sites_v
2219 WHERE (name = p_name
2220 AND
2221 p_name IS NOT NULL)
2222 OR (id1 = p_id
2223 AND
2224 p_id IS NOT NULL)
2225 AND status = 'A'
2226 AND vendor_id = p_vendor_id
2227 AND TRUNC(SYSDATE) >= NVL(TRUNC(start_date_active), TRUNC(SYSDATE));
2228
2229 CURSOR pay_method_csr (p_code VARCHAR2) IS
2230 SELECT lookup_code
2231 FROM fnd_lookup_values
2232 WHERE lookup_type = 'PAYMENT METHOD'
2233 AND nvl(enabled_flag,'N') = 'Y'
2234 AND lookup_code = p_code
2235 AND trunc(nvl(start_date_active,sysdate)) <= trunc(sysdate)
2236 AND trunc(nvl(end_date_active,sysdate+1)) > trunc(sysdate);
2237
2238 l_id1 VARCHAR2(30);
2239 l_line_type OKC_LINE_STYLES_B.LTY_CODE%TYPE;
2240
2241 BEGIN
2242
2243 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2244
2245 -- call START_ACTIVITY to create savepoint, check compatibility
2246 -- and initialize message list
2247 x_return_status := OKL_API.START_ACTIVITY(
2248 p_api_name => l_api_name,
2249 p_pkg_name => G_PKG_NAME,
2250 p_init_msg_list => p_init_msg_list,
2251 l_api_version => l_api_version,
2252 p_api_version => p_api_version,
2253 p_api_type => G_API_TYPE,
2254 x_return_status => x_return_status);
2255
2256 -- check if activity started successfully
2257 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2258 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2259 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2260 raise OKL_API.G_EXCEPTION_ERROR;
2261 END IF;
2262
2263
2264 OPEN line_style_csr (p_line_id);
2265 FETCH line_style_csr INTO l_line_type;
2266 CLOSE line_style_csr;
2267
2268
2269 -- Passthru rules are only for SERVICE and FEE line
2270 IF (l_line_type IN ('SOLD_SERVICE', 'FEE')) THEN
2271
2272 l_id1 := '?';
2273 IF (p_payment_term IS NOT NULL) THEN
2274
2275 OPEN pay_term_csr (p_payment_term_id,
2276 p_payment_term);
2277 FETCH pay_term_csr INTO l_id1;
2278 CLOSE pay_term_csr;
2279
2280 IF (l_id1 = '?') THEN
2281 okl_api.set_message(
2282 G_APP_NAME,
2283 G_INVALID_VALUE,
2284 'COL_NAME',
2285 'PAYMENT_TERM'
2286 );
2287
2288 x_return_status := OKL_API.G_RET_STS_ERROR;
2289 ELSE
2290 x_payment_term_id1 := l_id1;
2291 END IF;
2292
2293 END IF;
2294
2295 l_id1 := '?';
2296 IF (p_pay_to_site IS NOT NULL
2297 OR
2298 p_pay_to_site_id IS NOT NULL) THEN
2299
2300 OPEN pay_site_csr (p_pay_to_site_id,
2301 p_pay_to_site,
2302 p_vendor_id);
2303 FETCH pay_site_csr INTO l_id1;
2304 CLOSE pay_site_csr;
2305
2306 IF (l_id1 = '?') THEN
2307 okl_api.set_message(
2308 G_APP_NAME,
2309 G_INVALID_VALUE,
2310 'COL_NAME',
2311 'PAY_TO_SITE'
2312 );
2313
2314 x_return_status := OKL_API.G_RET_STS_ERROR;
2315 ELSE
2316 x_pay_site_id1 := l_id1;
2317 END IF;
2318
2319 END IF;
2320
2321 l_id1 := '?';
2322 IF (p_payment_method_code IS NOT NULL) THEN
2323
2324 OPEN pay_method_csr (p_payment_method_code);
2325 FETCH pay_method_csr INTO l_id1;
2326 CLOSE pay_method_csr;
2327
2328 IF (l_id1 = '?') THEN
2329 okl_api.set_message(
2330 G_APP_NAME,
2331 G_INVALID_VALUE,
2332 'COL_NAME',
2333 'PAYMENT_METHOD_CODE'
2334 );
2335
2336 x_return_status := OKL_API.G_RET_STS_ERROR;
2337 ELSE
2338 x_payment_method_id1 := l_id1;
2339 END IF;
2340 END IF;
2341
2342 ELSIF (p_payment_term IS NOT NULL
2343 OR
2344 p_payment_term_id IS NOT NULL
2345 OR
2346 p_pay_to_site IS NOT NULL
2347 OR
2348 p_pay_to_site_id IS NOT NULL
2349 OR
2350 p_payment_method_code IS NOT NULL
2351 ) THEN
2352 okl_api.set_message(
2353 G_APP_NAME,
2354 G_INVALID_LINE_RULE,
2355 'VALUE',
2356 'PAYMENT_TERM, PAY_TO_SITE, PAYMENT_METHOD',
2357 'LINE_TYPE',
2358 l_line_type,
2359 'ACT_LINE_TYPE',
2360 'SERVICE or FEE'
2361 );
2362
2363 x_return_status := OKL_API.G_RET_STS_ERROR;
2364
2365 END IF;
2366
2367 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2368 x_msg_data => x_msg_data);
2369
2370
2371 RETURN;
2372
2373 EXCEPTION
2374 when OKL_API.G_EXCEPTION_ERROR then
2375 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2376 p_api_name => l_api_name,
2377 p_pkg_name => G_PKG_NAME,
2378 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2379 x_msg_count => x_msg_count,
2380 x_msg_data => x_msg_data,
2381 p_api_type => G_API_TYPE);
2382
2383 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
2384 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2385 p_api_name => l_api_name,
2386 p_pkg_name => G_PKG_NAME,
2387 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2388 x_msg_count => x_msg_count,
2389 x_msg_data => x_msg_data,
2390 p_api_type => G_API_TYPE);
2391
2392 when OTHERS then
2393 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2394 p_api_name => l_api_name,
2395 p_pkg_name => G_PKG_NAME,
2396 p_exc_name => 'OTHERS',
2397 x_msg_count => x_msg_count,
2398 x_msg_data => x_msg_data,
2399 p_api_type => G_API_TYPE);
2400
2401 END validate_passthru_rule;
2402 END OKL_CONTRACT_TOP_LINE_PVT;