[Home] [Help]
PACKAGE BODY: APPS.OKC_RENEW_PUB
Source
1 package body OKC_RENEW_PUB as
2 /* $Header: OKCPRENB.pls 120.4 2005/12/05 15:16:54 skekkar noship $ */
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4
5 /* Procedure Auto_Renew( errbuf out nocopy varchar2,retcode out varchar2 ) is
6 begin
7
8 OKC_RENEW_PVT.AUTO_RENEW(errbuf,retcode);
9
10 end; */
11
12 Procedure Auto_Renew(errbuf out nocopy varchar2,
13 retcode out nocopy varchar2,
14 p_chr_id IN Number ,
15 p_duration IN Number,
16 p_uom_code IN Varchar2 ,
17 p_renewal_called_from_ui IN VARCHAR2 ,
18 p_contract_number IN Varchar2 ,
19 p_contract_number_modifier IN Varchar2
20 ) is
21
22 begin
23
24 OKC_RENEW_PVT.AUTO_RENEW(errbuf, retcode, p_chr_id, p_duration, p_uom_code,p_renewal_called_from_ui, p_contract_number, p_contract_number_modifier); /* p_renewal_called_from_ui added for bugfix 2093117 */
25
26 end;
27
28 FUNCTION is_renew_allowed(p_chr_id IN NUMBER,p_sts_code IN VARCHAR2) RETURN BOOLEAN IS
29 BEGIN
30 return OKC_RENEW_PVT.is_renew_allowed(p_chr_id,p_sts_code);
31 END is_renew_allowed;
32
33 PROCEDURE Renew(p_api_version IN NUMBER,
34 p_init_msg_list IN VARCHAR2 ,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2,
38 p_renew_in_parameters_tbl IN Renew_in_parameters_tbl,
39 x_renew_out_parameters_tbl OUT NOCOPY Renew_out_parameters_tbl,
40 p_do_commit IN VARCHAR2 ,
41 p_renewal_called_from_ui IN VARCHAR2 /* added for bugfix 2093117 */
42 ) is
43
44 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
45 l_chr_id number;
46 l_api_name constant varchar2(30) := 'Renew';
47
48 begin
49
50 okc_api.init_msg_list(p_init_msg_list);
51
52 x_return_status := OKC_API.G_RET_STS_SUCCESS;
53
54 for i in p_renew_in_parameters_tbl.first..p_renew_in_parameters_tbl.last loop
55
56 if p_renew_in_parameters_tbl.exists(i) then
57
58 OKC_RENEW_PUB.RENEW(p_api_version => 1,
59 p_init_msg_list => OKC_API.G_FALSE,
60 x_return_status => l_return_status,
61 x_msg_count => x_msg_count,
62 x_msg_data => x_msg_data,
63 x_contract_id => l_chr_id,
64 p_renew_in_parameters_rec => p_renew_in_parameters_tbl(i) ,
65 p_do_commit => OKC_API.G_FALSE, --p_do_commit,
66 p_renewal_called_from_ui => p_renewal_called_from_ui /* added for bugfix 2093117 */
67 );
68
69 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
70 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
71 x_return_status := l_return_status;
72 raise G_EXCEPTION_HALT_VALIDATION;
73 ELSE
74 x_return_status := l_return_status;
75 END IF;
76 ELSE
77 x_renew_out_parameters_tbl(i).p_old_contract_id := p_renew_in_parameters_tbl(i).p_contract_id;
78 x_renew_out_parameters_tbl(i).p_new_contract_id := l_chr_id;
79 END IF;
80
81 end if; -- end if exists
82
83 end loop;
84
85 IF p_do_commit = okc_api.g_true then
86 commit;
87 END IF;
88 EXCEPTION
89 WHEN G_EXCEPTION_HALT_VALIDATION THEN
90 null;
91 WHEN OTHERS THEN
92 OKC_API.set_message(p_app_name => g_app_name,
93 p_msg_name => g_unexpected_error,
94 p_token1 => g_sqlcode_token,
95 p_token1_value => sqlcode,
96 p_token2 => g_sqlerrm_token,
97 p_token2_value => sqlerrm);
98 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
99 end;
100
101
102 FUNCTION Validate_line(p_contract_id IN NUMBER) RETURN VARCHAR2
103 IS
104 BEGIN
105 return OKC_RENEW_PVT.Validate_line(p_contract_id);
106 END Validate_line;
107
108 PROCEDURE PRE_Renew(p_api_version IN NUMBER,
109 p_init_msg_list IN VARCHAR2 ,
110 x_return_status OUT NOCOPY VARCHAR2,
111 x_msg_count OUT NOCOPY NUMBER,
112 x_msg_data OUT NOCOPY VARCHAR2,
113 p_renew_in_parameters_tbl IN Renew_in_parameters_tbl,
114 x_renew_out_parameters_tbl OUT NOCOPY Renew_out_parameters_tbl,
115 p_do_commit IN VARCHAR2 ,
116 p_renewal_called_from_ui IN VARCHAR2 /* added for bugfix 2093117 */
117 ) is
118
119 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
120 l_chr_id number;
121 l_api_name constant varchar2(30) := 'PRE_Renew';
122
123 begin
124
125 okc_api.init_msg_list(p_init_msg_list);
126
127 x_return_status := OKC_API.G_RET_STS_SUCCESS;
128
129 for i in p_renew_in_parameters_tbl.first..p_renew_in_parameters_tbl.last loop
130
131 if p_renew_in_parameters_tbl.exists(i) then
132
133 OKC_RENEW_PUB.PRE_RENEW(p_api_version => 1,
134 p_init_msg_list => OKC_API.G_FALSE,
135 x_return_status => l_return_status,
136 x_msg_count => x_msg_count,
137 x_msg_data => x_msg_data,
138 x_contract_id => l_chr_id,
139 p_renew_in_parameters_rec => p_renew_in_parameters_tbl(i) ,
140 p_do_commit => OKC_API.G_FALSE, --p_do_commit,
141 p_renewal_called_from_ui => p_renewal_called_from_ui /* added for bugfix 2093117 */
142 );
143 /* commented because of bug 4569585
144 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
145 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
146 x_return_status := l_return_status;
147 raise G_EXCEPTION_HALT_VALIDATION;
148 ELSE
149 x_return_status := l_return_status;
150 END IF;
151 ELSE
152 x_renew_out_parameters_tbl(i).p_old_contract_id := p_renew_in_parameters_tbl(i).p_contract_id;
153 x_renew_out_parameters_tbl(i).p_new_contract_id := l_chr_id;
154 END IF;
155 */
156
157 --begin bug 4569585 changes
158 IF (l_return_status not in(OKC_API.G_RET_STS_SUCCESS, OKC_API.G_RET_STS_WARNING)) THEN
159 --can only be E (error) or U (Unexpected error), we stop in both cases
160 IF ((l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
161 OR (l_return_status = OKC_API.G_RET_STS_ERROR))THEN
162 x_return_status := l_return_status;
163 ELSE
164 --any other unrecognized status will be be treated as U
165 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
166 END IF;
167 raise G_EXCEPTION_HALT_VALIDATION;
168 ELSE
169 --if S (Success) or W (warning) continue
170 x_renew_out_parameters_tbl(i).p_old_contract_id := p_renew_in_parameters_tbl(i).p_contract_id;
171 x_renew_out_parameters_tbl(i).p_new_contract_id := l_chr_id;
172 --if warning make the return status as warning
173 IF (l_return_status = OKC_API.G_RET_STS_WARNING) THEN
174 x_return_status := l_return_status;
175 END IF;
176 END IF;
177 --end bug 4569585 changes
178
179 end if; -- end if exists
180
181 -- Bug 3658108
182 OKC_CVM_PVT.clear_g_transaction_id;
183
184 end loop;
185
186 IF p_do_commit = okc_api.g_true then
187 commit;
188 END IF;
189 EXCEPTION
190 WHEN G_EXCEPTION_HALT_VALIDATION THEN
191 null;
192 WHEN OTHERS THEN
193 OKC_API.set_message(p_app_name => g_app_name,
194 p_msg_name => g_unexpected_error,
195 p_token1 => g_sqlcode_token,
196 p_token1_value => sqlcode,
197 p_token2 => g_sqlerrm_token,
198 p_token2_value => sqlerrm);
199 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
200 end;
201
202
203 PROCEDURE PRE_Renew( p_api_version IN NUMBER,
204 p_init_msg_list IN VARCHAR2 ,
205 x_return_status OUT NOCOPY VARCHAR2,
206 x_msg_count OUT NOCOPY NUMBER,
207 x_msg_data OUT NOCOPY VARCHAR2,
208 x_contract_id OUT NOCOPY number,
209 p_renew_in_parameters_rec IN Renew_in_parameters_rec,
210 p_do_commit IN VARCHAR2 ,
211 p_renewal_called_from_ui IN VARCHAR2 /* added for bugfix 2093117 */
212 ) is
213
214 l_cls_code OKC_SUBCLASSES_B.CLS_CODE%TYPE:=OKC_API.G_MISS_CHAR;
215 l_renew_in_parameters_rec Renew_in_parameters_rec; -- this has to be empty
216 l_proc varchar2(4000);
217
218 -- bug 4777431 , skekkar
219 CURSOR cur_scs(p_chr_id number) is
220 select cls_code
221 from okc_k_headers_all_b,okc_subclasses_b -- bug 4777431
222 WHERE id=p_chr_id and code=scs_code;
223 l_renew_not_found exception;
224 PRAGMA EXCEPTION_INIT(l_Renew_Not_found, -6550);
225 begin
226 okc_api.init_msg_list(p_init_msg_list);
227
228 x_return_status := OKC_API.G_RET_STS_SUCCESS;
229
230 Open cur_scs(p_renew_in_parameters_rec.p_contract_id);
231 fetch cur_scs into l_cls_code;
232 close cur_scs;
233
234 g_prerenew_in_parameters_rec :=l_renew_in_parameters_rec;
235 g_prerenew_in_parameters_rec :=p_renew_in_parameters_rec;
236
237
238 If l_cls_code = 'SERVICE' then
239 -- dbms_output.put_line('san dbms in pre ren if');
240 BEGIN
241
242 -- bug 4777431
243 -- set org context
244 okc_context.set_okc_org_context(p_chr_id => p_renew_in_parameters_rec.p_contract_id);
245
246 -- change dynamic sql to static sql call (skekkar)
247 oks_renew_pub.renew
248 (p_api_version => 1,
249 p_init_msg_list => OKC_API.G_FALSE,
250 x_return_status => x_return_status,
251 x_msg_count => x_msg_count,
252 x_msg_data => x_msg_data,
253 x_contract_id => x_contract_id,
254 p_do_commit => OKC_API.G_FALSE,
255 p_renewal_called_from_ui => p_renewal_called_from_ui
256 );
257
258 /*
259 -- bug 4777431 , change dynamic sql call to static (skekkar)
260 --dbms_output.put_line('san dbms start immediate');
261 l_proc:='BEGIN OKS_RENEW_PUB.RENEW(:a ,:b ,:c,:d,:e,:f,:g,:h); END; ';
262
263
264 EXECUTE IMMEDIATE l_proc
265 using 1,OKC_API.G_FALSE, OUT x_return_status, OUT x_msg_count,
266 OUT x_msg_data, OUT x_contract_id , OKC_API.G_FALSE, p_renewal_called_from_ui;
267 */
268
269
270 --dbms_output.put_line('san dbms end immeditae');
271 g_prerenew_in_parameters_rec :=l_renew_in_parameters_rec;
272 EXCEPTION
273
274 --anjkumar from R12, if OKS_RENEW_PUB.RENEW is not defined/compiled
275 --throw the error as it is, do not attempt to use OKC_RENEW_PUB.RENEW
276 /*
277 WHEN l_renew_not_found then
278 --dbms_output.put_line('san dbms l_renew_not_found');
279 IF instr(UPPER(sqlerrm),'PLS-00302') <> 0 OR instr(UPPER(sqlerrm),'PLS-00201') <> 0 then
280 --dbms_output.put_line('san dbms going to ren in exception');
281
282 OKC_RENEW_PUB.RENEW(p_api_version => 1,
283 p_init_msg_list => OKC_API.G_FALSE,
284 x_return_status => x_return_status,
285 x_msg_count => x_msg_count,
286 x_msg_data => x_msg_data,
287 x_contract_id => x_contract_id,
288 p_renew_in_parameters_rec => p_renew_in_parameters_rec,
289 p_do_commit => p_do_commit,
290 p_renewal_called_from_ui => p_renewal_called_from_ui
291 );
292 ELSE
293 --dbms_output.put_line('san dbms else in pls error');
294 RAISE l_renew_not_found;
295 END IF;
296
297 */
298 WHEN OTHERS THEN
299 --dbms_output.put_line('san dbms others 1');
300 OKC_API.set_message(p_app_name => g_app_name,
301 p_msg_name => g_unexpected_error,
302 p_token1 => g_sqlcode_token,
303 p_token1_value => sqlcode,
304 p_token2 => g_sqlerrm_token,
305 p_token2_value => sqlerrm);
306
307 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
308 END;
309 ELSE
310 --dbms_output.put_line('san dbms in pre ren else');
311 OKC_RENEW_PUB.RENEW(p_api_version => 1,
312 p_init_msg_list => OKC_API.G_FALSE,
313 x_return_status => x_return_status,
314 x_msg_count => x_msg_count,
315 x_msg_data => x_msg_data,
316 x_contract_id => x_contract_id,
317 p_renew_in_parameters_rec => p_renew_in_parameters_rec,
318 p_do_commit => OKC_API.G_FALSE, --p_do_commit,
319 p_renewal_called_from_ui => p_renewal_called_from_ui /* added for bugfix 2093117 */
320 );
321
322
323 END IF;
324
325 IF p_do_commit = okc_api.g_true then
326 commit;
327 END IF;
328
329 EXCEPTION
330 WHEN OTHERS THEN
331 --dbms_output.put_line('san dbms others 2');
332 OKC_API.set_message(p_app_name => g_app_name,
333 p_msg_name => g_unexpected_error,
334 p_token1 => g_sqlcode_token,
335 p_token1_value => sqlcode,
336 p_token2 => g_sqlerrm_token,
337 p_token2_value => sqlerrm);
338
339 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
340 end;
341
342
343 PROCEDURE Renew( p_api_version IN NUMBER,
344 p_init_msg_list IN VARCHAR2 ,
345 x_return_status OUT NOCOPY VARCHAR2,
346 x_msg_count OUT NOCOPY NUMBER,
347 x_msg_data OUT NOCOPY VARCHAR2,
348 x_contract_id OUT NOCOPY number,
349 p_renew_in_parameters_rec IN Renew_in_parameters_rec,
350 p_do_commit IN VARCHAR2 ,
351 p_renewal_called_from_ui IN VARCHAR2 /* added for bugfix 2093117 */
352 ) is
353
354
355
356 CURSOR cur_rules(p_chr_id number) is
357 SELECT nvl(rul.rule_information1,OKC_API.G_MISS_CHAR) renew_type,
358 nvl(rul.rule_information2,OKC_API.G_MISS_CHAR) contact
359 FROM okc_rules_b rul,okc_rule_groups_b rgp
360 WHERE rgp.dnz_chr_id = p_chr_id
361 and rgp.id=rul.rgp_id
362 --and rgp.rgd_code='RENEW'
363 and rul.rule_information_category='REN' ;
364
365 --rules migration
366 CURSOR cur_header(p_chr_id number) is
367 SELECT * from okc_k_headers_b
368 where id = p_chr_id;
369 --
370
371
372 CURSOR cur_user(p_user_id number)
373 is select fnd.user_name from okx_resources_v res, fnd_user fnd
374 where fnd.user_id=res.user_id and res.id1=p_user_id;
375
376 l_ren_type okc_rules_v.rule_information1%type:=OKC_API.G_MISS_CHAR;
377 l_contact okc_rules_v.rule_information2%type:=OKC_API.G_MISS_CHAR;
378
379 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
380 l_renew_in_parameters_rec renew_in_parameters_rec := p_renew_in_parameters_rec;
381 lx_renew_in_parameters_rec renew_in_parameters_rec ;
382 l_api_name constant varchar2(30) := 'Renew_rec';
383 l_chr_id number;
384 cur_header_rec cur_header%rowtype;
385 begin
386
387
388 --dbms_output.put_line('san dbms in 1');
389 x_return_status := OKC_API.G_RET_STS_SUCCESS;
390
391 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
392 p_init_msg_list,
393 '_PUB',
394 x_return_status);
395
396 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
397 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
398 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
399 raise OKC_API.G_EXCEPTION_ERROR;
400 END IF;
401
402 ---/rules migration/
403 Open cur_header(p_renew_in_parameters_rec.p_contract_id);
404 Fetch cur_header into cur_header_rec;
405 Close cur_header;
406
407 If cur_header_rec.application_id in (510,871) then
408 open cur_rules(p_renew_in_parameters_rec.p_contract_id);
409 fetch cur_rules into l_ren_type,l_contact;
410 close cur_rules;
411 Else
412 l_ren_type := nvl(cur_header_rec.renewal_type_code,OKC_API.G_MISS_CHAR);
413 If cur_header_rec.renewal_notify_to is not null Then
414 l_contact := cur_header_rec.renewal_notify_to;
415 Else
416 l_contact := OKC_API.G_MISS_CHAR;
417 End If;
418 End If;
419
420 ---/rules migration/
421
422 If l_ren_type = 'DNR' then
423 OKC_API.set_message(p_app_name => g_app_name,
424 p_msg_name => 'OKC_RENEW_NOT_ALLOWED');
425 RAISE OKC_API.G_EXCEPTION_ERROR;
426 END IF;
427 IF l_contact <> OKC_API.G_MISS_CHAR then
428 open cur_user(to_number(l_contact));
429 FETCH cur_user into l_contact;
430 close cur_user;
431 END IF;
432 --dbms_output.put_line('san dbms in 2');
433
434 -- Call user hook for BEFORE
435 g_renew_in_parameters_rec := l_renew_in_parameters_rec;
436
437 okc_util.call_user_hook(l_return_status, g_pkg_name, l_api_name, 'B');
438
439 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
440 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
441 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
442 raise OKC_API.G_EXCEPTION_ERROR;
443 END IF;
444
445 l_renew_in_parameters_rec := g_renew_in_parameters_rec;
446 l_renew_in_parameters_rec.p_contract_id := p_renew_in_parameters_rec.p_contract_id;
447
448
449 --dbms_output.put_line('san dbms in 3');
450 OKC_RENEW_PUB.CREATE_RENEWED_CONTRACT( p_api_version => 1,
451 p_init_msg_list => OKC_API.G_FALSE,
452 x_return_status => l_return_status,
453 x_msg_count => x_msg_count,
454 x_msg_data => x_msg_data,
455 x_contract_id => x_contract_id,
456 p_renew_in_parameters_rec => l_renew_in_parameters_rec,
457 x_renew_in_parameters_rec => lx_renew_in_parameters_rec ,
458 p_ren_type => l_ren_type ,
459 p_renewal_called_from_ui => p_renewal_called_from_ui /* added for bugfix 2093117 */
460 );
461 /* IF l_return_status = 'W' then
462 x_return_status := 'W';
463 END IF;
464 */
465
466 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
467 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
468 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR OR l_return_status = 'W' THEN
469 RAISE OKC_API.G_EXCEPTION_ERROR;
470 END IF;
471
472 --dbms_output.put_line('san dbms in 4');
473 OKC_RENEW_PUB.POST_RENEWED_CONTRACT( p_api_version => 1,
474 p_init_msg_list => OKC_API.G_FALSE,
475 x_return_status => l_return_status,
476 x_msg_count => x_msg_count,
477 x_msg_data => x_msg_data,
478 p_renew_chr_id => x_contract_id,
479 p_renew_in_parameters_rec => lx_renew_in_parameters_rec ,
480 p_ren_type => l_ren_type ,
481 p_contact => l_contact );
482 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
483 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
484 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
485 RAISE OKC_API.G_EXCEPTION_ERROR;
486 END IF;
487
488 -- Call user hook for AFTER
489 g_new_contract_id := x_contract_id;
490
491 okc_util.call_user_hook(l_return_status, g_pkg_name, l_api_name, 'A');
492
493 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
494 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
495 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
496 raise OKC_API.G_EXCEPTION_ERROR;
497 END IF;
498 IF p_do_commit = okc_api.g_true then
499 commit;
500 END IF;
501 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
502
503 EXCEPTION
504 WHEN OKC_API.G_EXCEPTION_ERROR THEN
505 x_return_status := OKC_API.HANDLE_EXCEPTIONS
506 (l_api_name,
507 G_PKG_NAME,
508 'OKC_API.G_RET_STS_ERROR',
509 x_msg_count,
510 x_msg_data,
511 '_PUB');
512 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
513
514 x_return_status := OKC_API.HANDLE_EXCEPTIONS
515 (l_api_name,
516 G_PKG_NAME,
517 'OKC_API.G_RET_STS_UNEXP_ERROR',
518 x_msg_count,
519 x_msg_data,
520 '_PUB');
521 WHEN OTHERS THEN
522 x_return_status := OKC_API.HANDLE_EXCEPTIONS
523 (l_api_name,
524 G_PKG_NAME,
525 'OTHERS',
526 x_msg_count,
527 x_msg_data,
528 '_PUB');
529 end;
530
531 PROCEDURE Create_Renewed_contract
532 ( p_api_version IN NUMBER,
533 p_init_msg_list IN VARCHAR2 ,
534 x_return_status OUT NOCOPY VARCHAR2,
535 x_msg_count OUT NOCOPY NUMBER,
536 x_msg_data OUT NOCOPY VARCHAR2,
537 x_contract_id OUT NOCOPY number,
538 p_renew_in_parameters_rec IN Renew_in_parameters_rec,
539 x_renew_in_parameters_rec OUT NOCOPY Renew_in_parameters_rec,
540 p_ren_type IN varchar2 ,
541 p_renewal_called_from_ui IN VARCHAR2 /* added for bugfix 2093117 */
542 ) is
543
544 l_renew_in_parameters_rec renew_in_parameters_rec := p_renew_in_parameters_rec;
545 l_api_name constant varchar2(30) := 'Create_renewed_contract';
546 l_chr_id number;
547 begin
548 okc_api.init_msg_list(p_init_msg_list);
549 x_return_status := OKC_API.G_RET_STS_SUCCESS;
550 OKC_RENEW_PVT.CREATE_RENEWED_CONTRACT( p_api_version => 1,
551 p_init_msg_list => OKC_API.G_FALSE,
552 x_return_status => x_return_status,
553 x_msg_count => x_msg_count,
554 x_msg_data => x_msg_data,
555 x_contract_id => x_contract_id,
556 p_renew_in_parameters_rec => l_renew_in_parameters_rec ,
557 x_renew_in_parameters_rec => x_renew_in_parameters_rec ,
558 p_ren_type => p_ren_type,
559 p_renewal_called_from_ui => p_renewal_called_from_ui /* added for bugfix 2093117 */
560 );
561
562 EXCEPTION
563 WHEN OTHERS THEN
564 OKC_API.set_message(p_app_name => g_app_name,
565 p_msg_name => g_unexpected_error,
566 p_token1 => g_sqlcode_token,
567 p_token1_value => sqlcode,
568 p_token2 => g_sqlerrm_token,
569 p_token2_value => sqlerrm);
570
571 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
572 end;
573
574 PROCEDURE Post_Renewed_Contract
575 ( p_api_version IN NUMBER,
576 p_init_msg_list IN VARCHAR2 ,
577 x_return_status OUT NOCOPY VARCHAR2,
578 x_msg_count OUT NOCOPY NUMBER,
579 x_msg_data OUT NOCOPY VARCHAR2,
580 p_renew_chr_id IN number,
581 p_renew_in_parameters_rec IN Renew_in_parameters_rec,
582 p_ren_type IN VARCHAR2 ,
583 p_contact IN VARCHAR2
584 ) is
585
586 l_api_name constant varchar2(30) := 'Post_renewed_contract';
587 begin
588 okc_api.init_msg_list(p_init_msg_list);
589 x_return_status := OKC_API.G_RET_STS_SUCCESS;
590 OKC_RENEW_PVT.POST_RENEWED_CONTRACT( p_api_version => 1,
591 p_init_msg_list => OKC_API.G_FALSE,
592 x_return_status => x_return_status,
593 x_msg_count => x_msg_count,
594 x_msg_data => x_msg_data,
595 p_renew_chr_id => p_renew_chr_id,
596 p_renew_in_parameters_rec=> p_renew_in_parameters_rec,
597 p_ren_type => p_ren_type,
598 p_contact => p_contact
599 );
600
601 EXCEPTION
602 WHEN OTHERS THEN
603 OKC_API.set_message(p_app_name => g_app_name,
604 p_msg_name => g_unexpected_error,
605 p_token1 => g_sqlcode_token,
606 p_token1_value => sqlcode,
607 p_token2 => g_sqlerrm_token,
608 p_token2_value => sqlerrm);
609
610 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
611 end;
612
613
614 PROCEDURE validate( p_api_version IN NUMBER,
615 p_init_msg_list IN VARCHAR2 ,
616 x_return_status OUT NOCOPY VARCHAR2,
617 x_msg_count OUT NOCOPY NUMBER,
618 x_msg_data OUT NOCOPY VARCHAR2,
619 p_renew_in_parameters_tbl IN Renew_in_parameters_tbl,
620 p_renewal_called_from_ui IN VARCHAR2 /* added for bugfix 2093117 */
621 ) is
622
623 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
624 begin
625
626 okc_api.init_msg_list(p_init_msg_list);
627
628 x_return_status := OKC_API.G_RET_STS_SUCCESS;
629
630 for i in p_renew_in_parameters_tbl.first..p_renew_in_parameters_tbl.last loop
631
632 if p_renew_in_parameters_tbl.exists(i) then
633
634 OKC_RENEW_PUB.validate( p_api_version => p_api_version,
635 p_init_msg_list => OKC_API.G_FALSE,
636 x_return_status => l_return_status,
637 x_msg_count => x_msg_count,
638 x_msg_data => x_msg_data,
639 p_renew_in_parameters_rec => p_renew_in_parameters_tbl(i) ,
640 p_renewal_called_from_ui => p_renewal_called_from_ui /* added for bugfix 2093117 */
641 );
642
643 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
644 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
645 x_return_status := l_return_status;
646 raise G_EXCEPTION_HALT_VALIDATION;
647 ELSE
648 x_return_status := l_return_status;
649 END IF;
650 END IF;
651 end if;
652 end loop;
653 EXCEPTION
654 WHEN G_EXCEPTION_HALT_VALIDATION THEN
655 NULL;
656 WHEN OTHERS THEN
657 OKC_API.set_message(p_app_name => g_app_name,
658 p_msg_name => g_unexpected_error,
659 p_token1 => g_sqlcode_token,
660 p_token1_value => sqlcode,
661 p_token2 => g_sqlerrm_token,
662 p_token2_value => sqlerrm);
663 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
664 end;
665
666 PROCEDURE VALIDATE( p_api_version IN NUMBER,
667 p_init_msg_list IN VARCHAR2 ,
668 x_return_status OUT NOCOPY VARCHAR2,
669 x_msg_count OUT NOCOPY NUMBER,
670 x_msg_data OUT NOCOPY VARCHAR2,
671 p_renew_in_parameters_rec IN Renew_in_parameters_rec ,
672 p_renewal_called_from_ui IN VARCHAR2 /* added for bugfix 2093117 */
673 ) is
674
675 begin
676
677 okc_api.init_msg_list(p_init_msg_list);
678 OKC_RENEW_PVT.validate( p_api_version => p_api_version,
679 p_init_msg_list => OKC_API.G_FALSE,
680 x_return_status => x_return_status,
681 x_msg_count => x_msg_count,
682 x_msg_data => x_msg_data,
683 p_renew_in_parameters_rec => p_renew_in_parameters_rec ,
684 p_renewal_called_from_ui => p_renewal_called_from_ui /* added for bugfix 2093117 */
685 );
686
687 EXCEPTION
688 WHEN OTHERS THEN
689 OKC_API.set_message(p_app_name => g_app_name,
690 p_msg_name => g_unexpected_error,
691 p_token1 => g_sqlcode_token,
692 p_token1_value => sqlcode,
693 p_token2 => g_sqlerrm_token,
694 p_token2_value => sqlerrm);
695
696 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
697 end;
698
699 Procedure Update_Parents_Date_Renewed( p_api_version IN NUMBER,
700 p_init_msg_list IN VARCHAR2 ,
701 x_return_status OUT NOCOPY VARCHAR2,
702 x_msg_count OUT NOCOPY NUMBER,
703 x_msg_data OUT NOCOPY VARCHAR2,
704 p_chr_id IN NUMBER
705 ) is
706
707 l_api_name constant varchar2(30) := 'upd_parents_date_rnwd';
708 begin
709 okc_api.init_msg_list(p_init_msg_list);
710 x_return_status := OKC_API.G_RET_STS_SUCCESS;
711 OKC_RENEW_PVT.UPDATE_PARENTS_DATE_RENEWED( p_api_version => 1,
712 p_init_msg_list => OKC_API.G_FALSE,
713 x_return_status => x_return_status,
714 x_msg_count => x_msg_count,
715 x_msg_data => x_msg_data,
716 p_chr_id => p_chr_id
717 );
718
719 EXCEPTION
720 WHEN OTHERS THEN
721 OKC_API.set_message(p_app_name => g_app_name,
722 p_msg_name => g_unexpected_error,
723 p_token1 => g_sqlcode_token,
724 p_token1_value => sqlcode,
725 p_token2 => g_sqlerrm_token,
726 p_token2_value => sqlerrm);
727
728 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
729 end;
730
731 -- Bug 3580442 Overloaded is_already_not_renewed to accept only two parameters. This API is used in some events.
732 FUNCTION is_already_not_renewed(p_chr_id IN NUMBER, p_contract_number IN VARCHAR2) RETURN VARCHAR2 IS
733 l_msg_name VARCHAR2(1);
734 l_condition VARCHAR2(1);
735 BEGIN
736 l_condition := OKC_RENEW_PVT.is_already_not_renewed(p_chr_id,p_contract_number,l_msg_name, 'N');
737 --x_msg_name := l_msg_name;
738 return l_condition;
739 END is_already_not_renewed;
740
741 -- Bug 3386577 Added an OUT parameter
742 FUNCTION is_already_not_renewed(p_chr_id IN NUMBER,p_contract_number IN VARCHAR2, x_msg_name OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
743 l_msg_name VARCHAR2(1);
744 l_condition VARCHAR2(1);
745 BEGIN
746
747 l_condition := OKC_RENEW_PVT.is_already_not_renewed(p_chr_id, p_contract_number,l_msg_name);
748 x_msg_name := l_msg_name;
749 return l_condition;
750
751 END is_already_not_renewed;
752
753 end OKC_RENEW_PUB;