1 PACKAGE BODY OKC_RENEW_PVT AS
2 /* $Header: OKCRRENB.pls 120.2 2006/08/02 17:58:30 skekkar noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 --this is the default value for the renewal rule on lines
7 g_def_cle_ren varchar2(3):='FUL';
8
9 -- this g_cached_cle_id will help this program to remember which cle_id it had processed last time
10 -- when called from renew consolidation. This can help in saving the effort to build the
11 -- tree upwards for LRT rule when that rule is not defined on the line itself
12 g_cached_cle_id NUMBER := -100;
13 -- this g_cached_chr_id will help this program to remember which chr_id it had processed last time when
14 -- it was called. Thus if the same chr_id is called again as in renew consolidation, this
15 --will save the extra overhead of remaking the tables that are made of same values again and again
16 g_cached_chr_id NUMBER := OKC_API.G_MISS_NUM;
17
18
19 TYPE IdTab is table of okc_k_lines_b.id%type;
20 g_parent_id_tbl idTab;--used to cache in values of parents of a given line from renconsol
21 TYPE CLE_DATES_REC_TYPE IS RECORD(
22 ID NUMBER:=OKC_API.G_MISS_NUM,
23 orig_start_date DATE:=OKC_API.G_MISS_DATE,
24 orig_end_date DATE:=OKC_API.G_MISS_DATE,
25 start_date DATE:=OKC_API.G_MISS_DATE,
26 end_date DATE:=OKC_API.G_MISS_DATE
27 );
28
29 --TYPE CLE_DATES_TBL_TYPE IS TABLE OF CLE_DATES_REC_TYPE
30 -- INDEX BY BINARY_INTEGER;
31
32 -- g_cle_dates_tbl CLE_DATES_TBL_TYPE;
33
34 CURSOR cur_time_values(p_chr_id number) is
35 SELECT id,object_version_number,uom_code,duration,tve_id_started,tve_id_ended,
36 tve_id_limited,tve_type
37 FROM okc_timevalues
38 WHERE DNZ_CHR_ID = p_chr_id;
39 tve_rec cur_time_values%rowtype;
40 TYPE time_tbl_type is table of cur_time_values%rowtype
41 INDEX BY BINARY_INTEGER;
42 g_time_tbl time_tbl_type;
43
44
45 CURSOR cur_rules(p_chr_id number) is
46 SELECT nvl(rgp.cle_id,rgp.chr_id) comp_id ,nvl(rul.rule_information1,g_def_cle_ren) rule_type
47 FROM okc_rules_b rul,okc_rule_groups_b rgp
48 WHERE rgp.dnz_chr_id = p_chr_id
49 and rgp.id=rul.rgp_id
50 -- and rgp.rgd_code='RENEW'
51 and rul.rule_information_category='LRT' order by rgp.cle_id;
52
53 cursor cur_line(p_chr_id number) is
54 select id comp_id,nvl(line_renewal_type_code,g_def_cle_ren) rule_type
55 from okc_k_lines_b
56 where dnz_chr_id = p_chr_id;
57
58 cursor cur_headers(p_chr_id number) is
59 select application_id from okc_k_headers_b where id = p_chr_id;
60
61 p_appl_id1 number;
62 cur_lines_rec cur_line%rowtype;
63
64
65 Type l_rules_tbl_type is table of cur_rules%rowtype
66 Index By Binary_Integer;
67 g_rules_tbl l_rules_tbl_type;
68
69 -- /striping/
70 p_rule_code OKC_RULE_DEFS_B.rule_code%TYPE;
71 p_appl_id OKC_RULE_DEFS_B.application_id%TYPE;
72 p_dff_name OKC_RULE_DEFS_B.descriptive_flexfield_name%TYPE;
73
74 --------------------------------------------------------------------------------------------
75 -- Auto renew is a concurrent program initiated to renew the contracts automatically for
76 -- which auto_renew_days is given
77 --------------------------------------------------------------------------------------------
78 -- PROCEDURE Auto_Renew (errbuf out varchar2, retcode out varchar2) is
79 PROCEDURE Auto_Renew(errbuf out nocopy varchar2,
80 retcode out nocopy varchar2,
81 p_chr_id IN Number ,
82 p_duration IN Number ,
83 p_uom_code IN Varchar2 ,
84 p_renewal_called_from_ui IN VARCHAR2 ,
85 p_contract_number IN Varchar2 ,
86 p_contract_number_modifier IN VARCHAR2
87 ) is
88 CURSOR cur_auto_renew is
89 SELECT k.id,k.contract_number,k.contract_number_modifier,k.start_date,k.END_date,k.object_version_number
90 FROM okc_k_headers_b k,
91 okc_statuses_b sts
92 WHERE k.date_renewed is null
93 and k.sts_code = sts.code
94 and sts.ste_code in ('ACTIVE','EXPIRED','SIGNED')
95 and k.date_terminated is null
96 and k.template_yn = 'N'
97 and k.id = p_chr_id ;
98 -- bug 5017286
99 -- or (p_chr_id is null
100 -- and (k.END_date-k.auto_renew_days) <= trunc(sysdate)));
101
102 --san rencol take out later p_auto_renew_rec renew_in_parameters_rec;
103 p_auto_renew_rec okc_renew_pvt.renew_in_parameters_rec;
104
105 l_api_name constant VARCHAR2(30) := 'Auto_Renew';
106 l_return_status varchar2(1) := okc_api.g_ret_sts_success;
107 l_chr_id number;
108 l_timeunit okx_units_of_measure_v.uom_code%type;
109 l_duration number;
110 l_contract_number okc_k_headers_b.contract_number%TYPE;
111 l_msg_count Number;
112 l_msg Varchar2(2000);
113
114 x_return_status varchar2(1) := okc_api.g_ret_sts_success;
115 x_msg_count number := okc_api.g_miss_num;
116 x_msg_data varchar2(2000) := okc_api.g_miss_char;
117 l_date Date := Sysdate;
118
119 BEGIN
120
121 IF (l_debug = 'Y') THEN
122 okc_debug.Set_Indentation('OKC_RENEW_PVT');
123 okc_debug.log('100: Entered Auto_Renew', 2);
124 END IF;
125
126 retcode := '0';
127 fnd_msg_pub.initialize;
128 /* FND_FILE.PUT_LINE( FND_FILE.LOG, 'Start Auto Renew');
129 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Id ' || to_char(p_chr_id));
130 FND_FILE.PUT_LINE( FND_FILE.LOG, 'timeunit ' || p_uom_code);
131 FND_FILE.PUT_LINE( FND_FILE.LOG, 'duration ' || to_char(p_duration)); */
132
133 FOR auto_renew_rec IN cur_auto_renew
134 LOOP
135 l_contract_number := auto_renew_rec.contract_number;
136 IF (l_debug = 'Y') THEN
137 okc_debug.log('200: Contract Number ' || l_contract_number);
138 END IF;
139 If p_chr_id Is Not Null Then
140 l_duration := p_duration;
141 l_timeunit := p_uom_code;
142 l_return_status := OKC_API.G_RET_STS_SUCCESS;
143 Else
144
145 OKC_TIME_UTIL_PUB.get_duration(Auto_renew_rec.start_date,
146 auto_renew_rec.END_date,
147 l_duration,
148 l_timeunit,
149 l_return_status);
150
151 End If;
152
153 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
154 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
155 x_return_status := l_return_status;
156 raise G_EXCEPTION_HALT_VALIDATION;
157 ELSE
158 x_return_status := l_return_status;
159
160 FND_MESSAGE.set_name('OKC','OKC_AUTO_RENEW_FAILURE');
161 FND_MESSAGE.set_token('NUMBER',auto_renew_rec.contract_number);
162 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
163
164 FND_MESSAGE.set_name('OKC','OKC_GET_DURATION_ERROR');
165 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
166 END IF;
167 ELSE
168
169 p_auto_renew_rec.p_contract_id := auto_renew_rec.id;
170 p_auto_renew_rec.p_orig_start_date := auto_renew_rec.start_date;
171 p_auto_renew_rec.p_orig_END_date := auto_renew_rec.END_date;
172 p_auto_renew_rec.p_start_date := auto_renew_rec.END_date + 1;
173 p_auto_renew_rec.p_uom_code := l_timeunit;
174 p_auto_renew_rec.p_duration := l_duration;
175 p_auto_renew_rec.p_new_contract_number := Nvl(p_contract_number,auto_renew_rec.contract_number);
176 p_auto_renew_rec.p_new_contract_modifier := Nvl(p_contract_number_modifier, fnd_profile.value('OKC_CONTRACT_IDENTIFIER')|| l_date || To_char(l_date,' HH24:MI:SS'));
177 p_auto_renew_rec.p_object_version_number := auto_renew_rec.object_version_number;
178 p_auto_renew_rec.p_contract_number := auto_renew_rec.contract_number;
179 p_auto_renew_rec.p_contract_modifier := auto_renew_rec.contract_number_modifier;
180 p_auto_renew_rec.p_perpetual_flag := OKC_API.G_FALSE;
181
182 -- FND_FILE.PUT_LINE( FND_FILE.LOG, 'Before Pre_Renew');
183 IF (l_debug = 'Y') THEN
184 okc_debug.log('300: Before Pre_Renew');
185 END IF;
186 OKC_RENEW_PUB.PRE_Renew(p_api_version => 1,
187 p_init_msg_list => OKC_API.G_TRUE,
188 x_return_status => l_return_status,
189 x_msg_count => x_msg_count,
190 x_msg_data => x_msg_data,
191 x_contract_id => l_chr_id,
192 p_renew_in_parameters_rec => p_auto_renew_rec,
193 p_do_commit => OKC_API.G_TRUE ,
194 p_renewal_called_from_ui => p_renewal_called_from_ui);
195 IF (l_debug = 'Y') THEN
196 okc_debug.log('400: After Pre_Renew');
197 END IF;
198
199 -- FND_FILE.PUT_LINE( FND_FILE.LOG, 'After Pre_Renew');
200 -- FND_FILE.PUT_LINE( FND_FILE.LOG, 'l_return_status ' || l_return_status)
201 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
202 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
203 x_return_status := l_return_status;
204 raise G_EXCEPTION_HALT_VALIDATION;
205 ELSE
206 x_return_status := l_return_status;
207
208 fnd_msg_pub.count_and_get(p_count => l_msg_count,
209 p_data => l_msg);
210 For I In 1..l_msg_count LOOP
211 l_msg := FND_MSG_PUB.Get(p_msg_index => i,
212 p_encoded => 'F');
213 Fnd_File.Put_Line(FND_FILE.LOG, l_msg);
214
215 END LOOP;
216
217 FND_MESSAGE.set_name('OKC','OKC_AUTO_RENEW_FAILURE');
218 FND_MESSAGE.set_token('NUMBER',auto_renew_rec.contract_number);
219 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
220 END IF;
221 ELSE
222 FND_MESSAGE.set_name('OKC','OKC_AUTO_RENEW_SUCCESS');
223 FND_MESSAGE.set_token('NUMBER',auto_renew_rec.contract_number);
224 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
225 END IF;
226
227 END IF;
228
229 END LOOP;
230
231 IF (l_debug = 'Y') THEN
232 okc_debug.log('800: Leaving Auto_Renew', 2);
233 okc_debug.Reset_Indentation;
234 END IF;
235
236 EXCEPTION
237 WHEN G_EXCEPTION_HALT_VALIDATION THEN
238
239 IF (l_debug = 'Y') THEN
240 okc_debug.log('500: Exiting Auto_Renew:G_EXCEPTION_HALT_VALIDATION Exception', 2);
241 okc_debug.Reset_Indentation;
242 END IF;
243
244 retcode := '2';
245
246 FND_MESSAGE.set_name('OKC','OKC_AUTO_RENEW_FAILURE');
247 FND_MESSAGE.set_token('NUMBER',l_contract_number);
248 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
249
250 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
251 FND_FILE.PUT_LINE( FND_FILE.LOG,(FND_MSG_PUB.Get(I,p_encoded =>FND_API.G_FALSE )));
252 END LOOP;
253
254 WHEN OTHERS THEN
255
256 IF (l_debug = 'Y') THEN
257 okc_debug.log('700: Exiting Auto_Renew:OTHERS Exception', 2);
258 okc_debug.Reset_Indentation;
259 END IF;
260
261 retcode := '2';
262 OKC_API.set_message(p_app_name => g_app_name,
263 p_msg_name => g_unexpected_error,
264 p_token1 => g_sqlcode_token,
265 p_token1_value => sqlcode,
266 p_token2 => g_sqlerrm_token,
267 p_token2_value => sqlerrm);
268 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
269 FND_FILE.PUT_LINE( FND_FILE.LOG,(FND_MSG_PUB.Get(I,p_encoded =>FND_API.G_FALSE )));
270 END LOOP;
271
272 END Auto_Renew;
273 --------------------------------------------------------------------------------------------
274 --called from launchpad, it runs through certain validations and returns status
275 --------------------------------------------------------------------------------------------
276 FUNCTION is_renew_allowed(p_chr_id IN NUMBER,p_sts_code IN VARCHAR2) RETURN BOOLEAN IS
277
278 /*p_sts_code is not being used right now as not sure if the refresh from launchpad
279 will take place everytime a change happens to a contract or not. That is
280 If the status of the contract showing in launchpad would at all times be in sync
281 with database.It might not happen due to performance reasons of launchpad. So the current approach.
282 But if this sync is assured then we could use p_sts_code as well*/
283
284 l_status VARCHAR2(100) := '1';
285 l_cls_code VARCHAR2(100);
286 l_template_yn VARCHAR2(10);
287 l_code VARCHAR2(100);
288 l_app_id okc_k_headers_b.application_id%TYPE;
289 l_scs_code okc_k_headers_b.scs_code%TYPE;
290 --l_k okc_k_headers_b.contract_number%TYPE;
291 l_k VARCHAR2(255);
292 l_mod okc_k_headers_b.contract_number_modifier%TYPE ;
293 l_end_date okc_k_headers_b.end_date%TYPE;
294 l_date_terminated okc_k_headers_b.date_terminated%TYPE;
295 l_start_date okc_k_headers_b.start_date%TYPE;
296 x_msg_count number;
297 x_msg_data VARCHAR2(3000);
298 l_rnrl_rec OKS_RENEW_UTIL_PVT.RNRL_REC_TYPE;
299 x_rnrl_rec OKS_RENEW_UTIL_PVT.RNRL_REC_TYPE;
300
301
302 l_api_name Varchar2(30) := 'is_renew_allowed';
303 l_return_status Varchar2(1);
304
305 CURSOR c_chr IS
306 SELECT sts_code,template_yn, application_id, scs_code,contract_number,
307 contract_number_modifier,end_date,date_terminated, start_date
308 FROM okc_k_headers_b
309 WHERE id = p_chr_id;
310
311 CURSOR cur_status(p_code IN VARCHAR2) IS
312 SELECT ste_code
313 FROM okc_statuses_v
314 WHERE code = p_code;
315
316 BEGIN
317
318 IF (l_debug = 'Y') THEN
319 okc_debug.Set_Indentation('OKC_RENEW_PVT');
320 okc_debug.log('900: Entered is_renew_allowed', 2);
321 END IF;
322
323 OPEN c_chr;
324 FETCH c_chr INTO l_code,l_template_yn,l_app_id,l_scs_code,l_k,l_mod,
325 l_end_date,l_date_terminated, l_start_date;
326 CLOSE c_chr;
327
328 IF (l_mod is not null) and (l_mod <> OKC_API.G_MISS_CHAR) then
329 l_k := l_k ||'-'||l_mod;
330 END IF;
331
332 IF l_template_yn = 'Y' then
333 IF (l_debug = 'Y') THEN
334 okc_debug.log('1000: Templates non-renewable !!');
335 END IF;
336 OKC_API.set_message(p_app_name => g_app_name,
337 p_msg_name => 'OKC_K_TEMPLATE',
338 p_token1 => 'NUMBER',
339 p_token1_value => l_k);
340
341 IF (l_debug = 'Y') THEN
342 okc_debug.log('1010: Leaving is_renew_allowed ', 2);
343 okc_debug.Reset_Indentation;
344 END IF;
345 RETURN(FALSE);
346 END IF;
347
348 -- A perpetual cannot be renewed further !!
349 IF l_end_date Is Null then
350 IF (l_debug = 'Y') THEN
351 okc_debug.log('1011: Perpetual Contracts non-renewable !!');
352 END IF;
353 OKC_API.set_message(p_app_name => g_app_name,
354 p_msg_name => 'OKC_NO_PERPETUAL',
355 p_token1 => 'component',
356 p_token1_value => l_k);
357 IF (l_debug = 'Y') THEN
358 okc_debug.log('1012: Leaving is_renew_allowed ', 2);
359 okc_debug.Reset_Indentation;
360 END IF;
361 RETURN(FALSE);
362 END IF;
363
364 Open cur_status(l_code);
365 Fetch cur_status into l_status;
366 close cur_status;
367
368 --Bug 3431436 Future terminated contracts are non-renewable
369 IF (l_status in ('ACTIVE','EXPIRED','SIGNED') and l_date_terminated is not null)then
370 IF (l_debug = 'Y') THEN
371 okc_debug.log('1013: Future Terminated Contracts non-renewable !!');
372 END IF;
373 OKC_API.set_message(p_app_name => g_app_name,
374 p_msg_name => 'OKC_FUTURE_TERMINATED_K',
375 p_token1 => 'NUMBER',
376 p_token1_value => l_k );
377 IF (l_debug = 'Y') THEN
378 okc_debug.log('1014: Leaving is_renew_allowed ', 2);
379 okc_debug.Reset_Indentation;
380 END IF;
381 return(false);
382 END IF;
383
384 If okc_util.Get_All_K_Access_Level(p_application_id => l_app_id,
385 p_chr_id => p_chr_id,
386 p_scs_code => l_scs_code) <> 'U' Then
387 IF (l_debug = 'Y') THEN
388 okc_debug.log('1015: Secured Contracts non-renewable !!');
389 END IF;
390 OKC_API.set_message(p_app_name => g_app_name,
391 p_msg_name => 'OKC_NO_UPDATE',
392 p_token1 => 'CHR',
393 p_token1_value => l_k);
394 IF (l_debug = 'Y') THEN
395 okc_debug.log('1016: Leaving is_renew_allowed ', 2);
396 okc_debug.Reset_Indentation;
397 END IF;
398 Return(FALSE);
399 End If;
400
401 --OPEN c_sts(l_code);
402 --FETCH c_sts INTO l_sts_code;
403 --CLOSE c_sts;
404
405 -- commenting for Bug 3118707
406 -- IF l_sts_code IN ('ACTIVE','EXPIRED','SIGNED') then
407 --okc_debug.log('1017: Leaving is_renew_allowed ', 2);
408 --okc_debug.Reset_Indentation;
409 -- return(TRUE);
410
411
412 -- invoke OKS procedure to check renew eligibility for service class
413 If l_scs_code in ('SERVICE','WARRANTY','SUBSCRIPTION') THEN
414 OKS_RENEW_UTIL_PUB.GET_RENEW_RULES (
415 p_api_version => 1.0,
416 p_init_msg_list => OKC_API.G_FALSE,
417 x_return_status => l_return_status,
418 x_msg_count => x_msg_count,
419 x_msg_data => x_msg_data,
420 P_Chr_Id => p_chr_id,
421 P_PARTY_ID => NULL,
422 P_ORG_ID => NULL,
423 P_Date => l_start_date,
424 P_RNRL_Rec => l_rnrl_rec,
425 X_RNRL_Rec => x_rnrl_rec
426 );
427 IF x_rnrl_rec.renewal_type = 'DNR' THEN
428 OKC_API.set_message(
429 G_APP_NAME,
430 G_DNR_MSG);
431 okc_debug.log('1000: Leaving is_renew_allowed ', 2);
432 okc_debug.Reset_Indentation;
433 return(FALSE);
434 End If;
435 IF (OKC_OKS_PUB.Is_Renew_Allowed(p_chr_id => p_chr_id,
436 x_return_status => l_return_status)) Then
437 IF (l_debug = 'Y') THEN
438 okc_debug.log('1018: Leaving is_renew_allowed ', 2);
439 okc_debug.Reset_Indentation;
440 END IF;
441 return(TRUE);
442 ELSE
443 -- Bug 3280617
444 /*OKC_API.set_message(p_app_name => g_app_name,
445 p_msg_name => 'OKC_INVALID_STS',
446 p_token1 => 'component',
447 p_token1_value => l_k);*/
448
449 IF (l_debug = 'Y') THEN
450 okc_debug.log('1000: Leaving is_renew_allowed ', 2);
451 okc_debug.Reset_Indentation;
452 END IF;
453 return(FALSE);
454 END IF;
455
456 End If;
457
458 RETURN (TRUE);
459
460 END is_renew_allowed;
461
462 PROCEDURE VALIDATE(p_api_version IN NUMBER,
463 p_init_msg_list IN VARCHAR2 ,
464 x_return_status OUT NOCOPY VARCHAR2,
465 x_msg_count OUT NOCOPY NUMBER,
466 x_msg_data OUT NOCOPY VARCHAR2,
467 p_renew_in_parameters_rec IN Renew_in_parameters_rec,
468 p_renewal_called_from_ui IN VARCHAR2 /* Added for bugfix 2093117 */
469 ) is
470
471 CURSOR cur_k_header is
472 SELECT ID,
473 STS_CODE,
474 CONTRACT_NUMBER,
475 CONTRACT_NUMBER_MODIFIER,
476 TEMPLATE_YN,
477 DATE_TERMINATED,
478 DATE_RENEWED,
479 END_DATE
480 FROM okc_k_headers_b
481 WHERE id = p_renew_in_parameters_rec.p_contract_id;
482
483 CURSOR cur_status(p_sts_code varchar2) is
484 SELECT ste_code,meaning
485 FROM okc_statuses_v
486 WHERE code = p_sts_code;
487
488 CURSOR cur_uom is
489 SELECT 'x'
490 FROM okc_time_code_units_b
491 WHERE uom_code = p_renew_in_parameters_rec.p_uom_code;
492
493 -- Cursor to check if a Quote is cerated for renewal of
494 -- contract.
495
496 /*
497 Commented out nocopy as per Bug#1938017 as renewal of contract from quote is desupported from 11.5.6
498 CURSOR cur_qte(p_chr_id number) IS
499 SELECT qte.quote_number
500 FROM okc_k_rel_objs_v rel,
501 aso_quote_headers_all_v qte
502 WHERE jtot_object1_code = okc_oc_int_qtk_pvt.g_jtot_qte_hdr
503 AND chr_id = p_chr_id
504 AND rty_code = okc_oc_int_qtk_pvt.g_k2q_ren
505 AND rel.object1_id1 = qte.quote_header_id;
506 */
507
508 l_status varchar2(30):='1';
509 l_meaning okc_statuses_v.meaning%TYPE;
510 l_dummy varchar2(1);
511 k_header_rec cur_k_header%rowtype;
512 -- qte_rec cur_qte%rowtype;
513 l_api_name Varchar2(30) := 'Validate';
514 l_msg_name Varchar2(1);
515
516 BEGIN
517
518 IF (l_debug = 'Y') THEN
519 okc_debug.Set_Indentation('OKC_RENEW_PVT');
520 okc_debug.log('1400: Entered VALIDATE', 2);
521 END IF;
522
523 okc_api.init_msg_list(p_init_msg_list);
524
525 x_return_status := okc_api.g_ret_sts_success;
526
527 If p_renew_in_parameters_rec.p_perpetual_flag = OKC_API.G_FALSE OR
528 p_renew_in_parameters_rec.p_perpetual_flag IS NULL
529 Then
530 if ( p_renew_in_parameters_rec.p_END_date is null
531 and
532 (
533 p_renew_in_parameters_rec.p_uom_code is null
534 or
535 p_renew_in_parameters_rec.p_duration is null
536 )
537 )
538 then
539
540 OKC_API.set_message(p_app_name => g_app_name,
541 p_msg_name => 'OKC_INVALID_PARAMETERS');
542
543 x_return_status := OKC_API.G_RET_STS_ERROR;
544 RAISE g_exception_halt_validation;
545 END if;
546 END IF; -- End perpetual_flag = OKC_API.G_FALSE
547
548 if ( p_renew_in_parameters_rec.p_start_date is not null and
549 p_renew_in_parameters_rec.p_orig_end_date is not null ) then
550 if ( p_renew_in_parameters_rec.p_start_date <= p_renew_in_parameters_rec.p_orig_end_date) then
551
552 OKC_API.set_message(p_app_name => g_app_name,
553 p_msg_name => 'OKC_NEW_START_MORE_END');
554
555 x_return_status := OKC_API.G_RET_STS_ERROR;
556 RAISE g_exception_halt_validation;
557 END IF;
558 END if;
559
560 if p_renew_in_PARAMETERs_rec.p_uom_code is not null then
561
562 open cur_uom;
563 fetch cur_uom into l_dummy;
564
565 if cur_uom%notfound then
566
567 OKC_API.set_message(p_app_name => g_app_name,
568 p_msg_name => 'OKC_INVALID_UOM',
569 p_token1 => 'UOM_CODE',
570 p_token1_value => p_renew_in_parameters_rec.p_uom_code);
571
572 x_return_status := OKC_API.G_RET_STS_ERROR;
573 close cur_uom;
574 RAISE g_exception_halt_validation;
575 END if;
576
577 close cur_uom;
578
579 END if;
580
581 open cur_k_header;
582 fetch cur_k_header into k_header_rec;
583
584 if cur_k_header%notfound then -- contract header_id is wrong
585
586 OKC_API.set_message(p_app_name => g_app_name,
587 p_msg_name => 'OKC_K_HEADER_NOT_FOUND',
588 p_token1 => 'NUMBER',
589 p_token1_value => p_renew_in_parameters_rec.p_contract_number);
590
591 x_return_status := OKC_API.G_RET_STS_ERROR;
592 close cur_k_header;
593 RAISE g_exception_halt_validation;
594
595 END if;
596
597 close cur_k_header;
598
599 -- A perpetual cannot be renewed further !!
600 IF k_header_rec.end_date Is Null then
601 OKC_API.set_message(p_app_name => g_app_name,
602 p_msg_name => 'OKC_NO_PERPETUAL',
603 p_token1 => 'component',
604 p_token1_value => p_renew_in_parameters_rec.p_contract_number);
605 x_return_status := okc_api.g_ret_sts_error;
606 RAISE g_exception_halt_validation;
607 END IF;
608
609 If k_header_rec.template_Yn = 'Y' then
610
611 OKC_API.set_message(p_app_name => g_app_name,
612 p_msg_name => 'OKC_K_TEMPLATE',
613 p_token1 => 'NUMBER',
614 p_token1_value => p_renew_in_parameters_rec.p_contract_number);
615
616 x_return_status := okc_api.g_ret_sts_error;
617 RAISE g_exception_halt_validation;
618
619 END if;
620
621 open cur_status(k_header_rec.sts_code);
622 fetch cur_status into l_status,l_meaning;
623 close cur_status;
624
625 IF l_status='1' then
626 OKC_API.set_message(p_app_name => g_app_name,
627 p_msg_name => 'OKC_INVALID_K_STATUS',
628 p_token1 => 'NUMBER',
629 p_token1_value => k_header_rec.contract_number,
630 p_token2 => 'MODIFIER',
631 p_token2_value => k_header_rec.contract_number_modifier,
632 p_token3 => 'STATUS',
633 p_token3_value => k_header_rec.sts_code);
634 RAISE g_exception_halt_validation;
635 END IF;
636
637 IF l_status NOT IN ('ACTIVE','EXPIRED','SIGNED') then
638 x_return_status := OKC_API.G_RET_STS_ERROR;
639
640 OKC_API.set_message(p_app_name => g_app_name,
641 p_msg_name => 'OKC_INVALID_K_STATUS',
642 p_token1 => 'NUMBER',
643 p_token1_value => k_header_rec.contract_number,
644 p_token2 => 'MODIFIER',
645 p_token2_value => k_header_rec.contract_number_modifier,
646 p_token3 => 'STATUS',
647 p_token3_value => l_meaning);
648
649 RAISE g_exception_halt_validation;
650 ELSIF k_header_rec.date_terminated is not null then
651 x_return_status := OKC_API.G_RET_STS_ERROR;
652
653 OKC_API.set_message(p_app_name => g_app_name,
654 p_msg_name => 'OKC_FUTURE_TERMINATED_K',
655 p_token1 => 'NUMBER',
656 p_token1_value => k_header_rec.contract_number );
657
658 RAISE g_exception_halt_validation;
659
660 /* Modified the following logic for bugfix 2093117 */
661
662 ELSIF (( k_header_rec.date_renewed is not null) OR (p_renewal_called_from_ui = 'N' )) THEN
663 --Bug 3560988 Passing p_renewal_called_from_ui to the is_already_not_renewed function
664 IF (is_already_not_renewed(k_header_rec.id,k_header_rec.contract_number, l_msg_name, p_renewal_called_from_ui) = OKC_API.G_FALSE) THEN
665 x_return_status := OKC_API.G_RET_STS_ERROR;
666 END IF;
667 END IF;
668 /* added for bug 3005039 */
669 if p_renewal_called_from_UI = 'N' then
670 IF (validate_line(p_renew_in_parameters_rec.p_contract_id) = OKC_API.G_FALSE) THEN
671 x_return_status := OKC_API.G_RET_STS_ERROR;
672 end if;
673 end if;
674 /* added for bug 3005039 */
675 /*
676 Commented out nocopy as per Bug#1938017 as renewal of contract from quote is desupported from 11.5.6
677 -- For on-line renewals check if a quote is created for contract
678 -- renewal. If a quote is created, then only the quote should be
679 -- used to renew the contract.
680 -- Also make sure to prevent creation of another Quote( for renewal) if
681 -- one already exists.
682 --
683 IF ( p_renew_in_parameters_rec.p_context = OKC_API.G_MISS_CHAR
684 OR
685 p_renew_in_parameters_rec.p_context IS NULL
686 OR
687 p_renew_in_parameters_rec.p_context = okc_oc_int_qtk_pvt.g_k2q_ren ) THEN
688
689 -- on-line renewal / duplicate quote renewal case
690
691 OPEN cur_qte(k_header_rec.id);
692 FETCH cur_qte INTO qte_rec;
693
694 IF cur_qte%FOUND THEN
695 --
696 -- i.e Found a quote that should be used for renewal of
697 -- contract. So do not allow on-line renewal
698
699 OKC_API.set_message( p_app_name => g_app_name,
700 p_msg_name =>'OKC_RENEWAL_QUOTE',
701 p_token1 => 'QUOTE',
702 p_token1_value => qte_rec.quote_number,
703 p_token2 =>'NUMBER',
704 p_token2_value => k_header_rec.contract_number );
705
706 x_return_status := OKC_API.G_RET_STS_ERROR;
707 CLOSE cur_qte;
708 RAISE g_exception_halt_validation;
709
710 END IF; -- End cur_qte%FOUND
711
712 CLOSE cur_qte;
713 END IF; -- end p_context = G_miss_char
714 */
715
716 IF (l_debug = 'Y') THEN
717 okc_debug.log('1500: Leaving VALIDATE', 2);
718 okc_debug.Reset_Indentation;
719 END IF;
720
721 EXCEPTION
722 WHEN G_EXCEPTION_HALT_VALIDATION THEN
723
724 IF (l_debug = 'Y') THEN
725 okc_debug.log('1600: Exiting VALIDATE:G_EXCEPTION_HALT_VALIDATION Exception', 2);
726 okc_debug.Reset_Indentation;
727 END IF;
728
729 IF (l_debug = 'Y') THEN
730 okc_debug.Reset_Indentation;
731 END IF;
732
733 -- NULL;
734 WHEN OTHERS THEN
735
736 IF (l_debug = 'Y') THEN
737 okc_debug.log('1700: Exiting VALIDATE:OTHERS Exception', 2);
738 okc_debug.Reset_Indentation;
739 END IF;
740
741 OKC_API.set_message(p_app_name => g_app_name,
742 p_msg_name => g_unexpected_error,
743 p_token1 => g_sqlcode_token,
744 p_token1_value => sqlcode,
745 p_token2 => g_sqlerrm_token,
746 p_token2_value => sqlerrm);
747
748 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
749
750 END VALIDATE;
751
752 FUNCTION VALIDATE_LINE(p_contract_id IN NUMBER) RETURN VARCHAR2
753 IS
754 CURSOR cur_k_lines is
755 SELECT ID
756 FROM okc_k_lines_b l, okc_statuses_b sts
757 WHERE sts.code = l.sts_code
758 and sts.ste_code in ('ACTIVE','EXPIRED','SIGNED')
759 and l.dnz_chr_id = p_contract_id
760 and l.date_renewed is null
761 and l.date_terminated is null;
762
763 k_lines_rec cur_k_lines%rowtype;
764 l_return_flag varchar2(1) := OKC_API.G_TRUE;
765 BEGIN
766 open cur_k_lines;
767 fetch cur_k_lines into k_lines_rec;
768 if cur_k_lines%notfound then
769 OKC_API.set_message(p_app_name => g_app_name,
770 p_msg_name => 'OKC_LINES_SUBLINES_TERMINATED');
771 close cur_k_lines;
772 RETURN(OKC_API.G_FALSE);
773 END IF;
774
775 -- Bug 3584224 Invoking validate_oks_lines to check if all the sublines have been terminated.
776 l_return_flag := OKC_OKS_PUB.VALIDATE_OKS_LINES(p_contract_id);
777 return l_return_flag;
778
779 END VALIDATE_LINE;
780
781
782 --updates the dates and timevalues in all the rules for the renew copy
783 /*
784 * bug 5438257
785 * stubbed
786 */
787 PROCEDURE update_rules(
788 p_api_version IN NUMBER,
789 p_init_msg_list IN VARCHAR2 ,
790 x_return_status OUT NOCOPY VARCHAR2,
791 x_msg_count OUT NOCOPY NUMBER,
792 x_msg_data OUT NOCOPY VARCHAR2,
793 p_chr_id IN number
794 ) is
795 BEGIN
796 NULL;
797 END update_rules;
798 /*
799 * bug 5438257
800 PROCEDURE update_rules(
801 p_api_version IN NUMBER,
802 p_init_msg_list IN VARCHAR2 ,
803 x_return_status OUT NOCOPY VARCHAR2,
804 x_msg_count OUT NOCOPY NUMBER,
805 x_msg_data OUT NOCOPY VARCHAR2,
806 p_chr_id IN number
807 ) is
808
809
810 CURSOR cur_rules(p_chr_id number) is
811 SELECT nvl(rg.cle_id,rg.chr_id) parent_ID,ru.id id,ru.object_version_number,
812 ru.comments,ru.rule_information_category from
813 okc_rules_v ru,okc_rule_groups_b rg
814 WHERE rgp_id=rg.id and rg.DNZ_CHR_ID = p_chr_id for update of ru.id nowait;
815
816 CURSOR cur_ia(p_tve_id number) is
817 SELECT start_date ,end_date FROM okc_time_ia_startend_val_v
818 WHERE id = p_tve_id for update of id nowait;
819
820 l_col_vals OKC_TIME_UTIL_PUB.T_COL_VALS;
821 tmp_rulv_rec OKC_RULE_PUB.rulv_rec_type;
822 l_no_of_cols number;
823
824 E_Resource_Busy EXCEPTION;
825 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
826
827 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
828
829 l_api_name constant varchar2(30) := 'update_rules';
830 l_string varchar2(2000);
831 l_uom_code okx_units_of_measure_v.uom_code%type;
832 l_duration number;
833 l_new_date date;
834 -- The following data types are included for rule comments
835
836 j number;
837
838 l_tve_id number;
839
840 l_cle_dates_rec cle_dates_rec_type;
841
842
843 Function get_date_rec(p_id number) Return cle_dates_rec_type is
844 l_rec cle_dates_rec_type;
845 i BINARY_INTEGER;
846 l_api_name Varchar2(30) := 'get_date_rec';
847
848 CURSOR c_get_date_rec(b_id IN NUMBER) IS
849 SELECT id, orig_start_date, orig_end_date, start_date, end_date
850 FROM okc_cle_dates_tmp
851 WHERE id = b_id;
852 -- AND chr_or_cle='CLE'; -- need to check if this is needed.
853
854 Begin
855
856 -- okc_debug.Set_Indentation('get_date_rec');
857 -- okc_debug.log('1800: Entered get_date_rec', 2);
858
859 l_rec.id:=OKC_API.G_MISS_NUM;
860
861 OPEN c_get_date_rec(p_id);
862 FETCH c_get_date_rec INTO l_rec;
863 CLOSE c_get_date_rec;
864
865 RETURN l_rec;
866
867 END;
868
869
870 Function get_tve_rec(p_tve_id number) Return cur_time_values%rowtype is
871 l_rec cur_time_values%rowtype;
872 i BINARY_INTEGER;
873 l_api_name Varchar2(30) := 'get_tve_rec';
874 Begin
875
876 -- okc_debug.Set_Indentation('get_tve_rec');
877 -- okc_debug.log('2000: Entered get_tve_rec', 2);
878
879 l_rec.id:=OKC_API.G_MISS_NUM;
880 If g_time_tbl.count >0 then
881 i:=g_time_tbl.first;
882 LOOP
883 If g_time_tbl(i).id=p_tve_id then
884 -- okc_debug.log('2100: Exiting get_tve_rec', 2);
885
886 return g_time_tbl(i);
887 END IF;
888 Exit when i=g_time_tbl.last;
889 i:=g_time_tbl.next(i);
890 END LOOP;
891 END IF;
892
893 -- okc_debug.log('1860: Leaving Function Get_TVE_Rec ', 2);
894 -- okc_debug.Reset_Indentation;
895
896 return l_rec;
897 END;
898
899 FUNCTION check_ia(p_tve_id number,p_obj_no number , p_rule_rec cur_rules%rowtype , p_date_rec cle_dates_rec_type)
900 Return varchar2 is
901 l_return_status varchar2(1):=OKC_API.G_RET_STS_SUCCESS;
902 l_tve_start date :=OKC_API.G_MISS_DATE;
903 l_tve_end date :=OKC_API.G_MISS_DATE;
904 l_isev_ext_rec_type OKC_TIME_PUB.isev_ext_rec_type;
905 i_isev_ext_rec_type OKC_TIME_PUB.isev_ext_rec_type;
906 i_rulv_rec OKC_RULE_PUB.rulv_rec_type;
907 l_rulv_rec OKC_RULE_PUB.rulv_rec_type;
908 l_exception_stop exception;
909 l_orig_start_date date :=OKC_API.G_MISS_DATE;
910 l_orig_end_date date :=OKC_API.G_MISS_DATE;
911 l_new_start_date date :=OKC_API.G_MISS_DATE;
912 l_new_end_date date :=OKC_API.G_MISS_DATE;
913 l_api_name Varchar2(30) := 'check_ia';
914 BEGIN
915
916 -- okc_debug.Set_Indentation('check_ia');
917 -- okc_debug.log('2200: Entered check_ia', 2);
918
919 --get dates
920 l_orig_start_date:=p_date_rec.orig_start_date;
921 l_orig_end_date:=p_date_rec.orig_end_date;
922 l_new_start_date:=p_date_rec.start_date;
923 l_new_end_date:=p_date_rec.end_date;
924
925 open cur_ia(p_tve_id);
926 Fetch cur_ia into l_tve_start,l_tve_end;
927 close cur_ia;
928 If l_tve_start <>OKC_API.G_MISS_DATE and l_tve_end <>OKC_API.G_MISS_DATE then
929 if l_tve_start = l_orig_start_date and l_tve_end = l_orig_end_date then
930 l_isev_ext_rec_type.id:=p_tve_id;
931 l_isev_ext_rec_type.object_version_number:=p_obj_no;
932 l_isev_ext_rec_type.start_date:=l_new_start_date;
933 l_isev_ext_rec_type.end_date:=l_new_end_date;
934 --san comment update the ia
935 -- okc_debug.log('2300: Before update_ia_startend');
936 OKC_TIME_PUB.UPDATE_IA_STARTEND(
937 p_api_version=> p_api_version,
938 p_init_msg_list=> okc_api.g_false,
939 x_return_status=> l_return_status,
940 x_msg_count=> x_msg_count,
941 x_msg_data=> x_msg_data,
942 p_isev_ext_rec=> l_isev_ext_rec_type,
943 x_isev_ext_rec=> i_isev_ext_rec_type) ;
944 -- okc_debug.log('2400: After update_ia_startend');
945 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
946 RAISE l_exception_stop;
947 END IF;
948
949 l_rulv_rec.id := p_rule_rec.id;
950 l_rulv_rec.object_version_number := p_rule_rec.object_version_number;
951 l_rulv_rec.comments := replace(p_rule_rec.comments,
952 to_char(l_orig_start_date,'DD-MM-YYYY HH24:MI:SS'),
953 to_char(l_new_start_date,'DD-MM-YYYY HH24:MI:SS'));
954 l_rulv_rec.comments := replace(l_rulv_rec.comments,
955 to_char(l_orig_end_date,'DD-MM-YYYY HH24:MI:SS'),
956 to_char(l_new_end_date,'DD-MM-YYYY HH24:MI:SS'));
957 -- okc_debug.log('2500: Before update_rule');
958 OKC_RULE_PUB.UPDATE_RULE( p_api_version => 1,
959 p_init_msg_list => OKC_API.G_FALSE,
960 x_return_status => l_return_status,
961 x_msg_count => x_msg_count,
962 x_msg_data => x_msg_data,
963 p_rulv_rec => l_rulv_rec,
964 x_rulv_rec => i_rulv_rec
965 );
966 -- okc_debug.log('2600: After update_rule');
967 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
968 RAISE l_exception_stop;
969 END IF;
970 else
971 --san comment disable the rule;
972 null;
973 end if;
974 END IF;
975
976 -- okc_debug.log('2700: Leaving check_ia', 2);
977 -- okc_debug.Reset_Indentation;
978
979 return l_return_status;
980
981 EXCEPTION
982 WHEN l_exception_stop then
983
984 -- okc_debug.log('2800: Exiting check_ia:l_exception_stop Exception', 2);
985 -- okc_debug.Reset_Indentation;
986
987 return l_return_status;
988 when others then
989
990 -- okc_debug.log('2900: Exiting check_ia:others Exception', 2);
991 -- okc_debug.Reset_Indentation;
992
993 OKC_API.set_message(p_app_name => g_app_name,
994 p_msg_name => g_unexpected_error,
995 p_token1 => g_sqlcode_token,
996 p_token1_value => sqlcode,
997 p_token2 => g_sqlerrm_token,
998 p_token2_value => sqlerrm);
999 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1000
1001 return (l_return_status);
1002
1003 END check_ia;
1004 BEGIN
1005
1006 IF (l_debug = 'Y') THEN
1007 okc_debug.Set_Indentation('Update_Rules');
1008 okc_debug.log('3000: Entered Update_Rules', 2);
1009 END IF;
1010
1011 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1012
1013 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1014 p_init_msg_list,
1015 '_PROCESS',
1016 x_return_status);
1017
1018 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1019 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1020 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1021 raise OKC_API.G_EXCEPTION_ERROR;
1022 END IF;
1023 j := 1;
1024
1025 --store the current time values in g_time_tbl again if current chr id is different from the
1026 --one that called this program last time.
1027 IF g_cached_chr_id <> p_chr_id then
1028 FOR time_rec in cur_time_values(p_chr_id) loop
1029 g_time_tbl(J) := time_rec;
1030 j := j + 1;
1031 END loop;
1032 END IF;
1033
1034 --for all the rules defined for the contract
1035 IF (l_debug = 'Y') THEN
1036 okc_debug.log('3100: Before cur_rules Cursor');
1037 END IF;
1038 FOR rule_rec in cur_rules(p_chr_id) loop
1039 IF (l_debug = 'Y') THEN
1040 okc_debug.set_trace_off;
1041 okc_debug.log('3200: Before get_date_rec');
1042 END IF;
1043 l_cle_dates_rec:=get_date_rec(rule_rec.parent_id);
1044 -- okc_debug.log('3300: After get_date_rec');
1045 IF l_cle_dates_rec.id <> OKC_API.G_MISS_NUM then
1046
1047 okc_renew_pvt.g_rulv_rec.id := rule_rec.id;
1048 okc_renew_pvt.g_rulv_rec.object_version_number := rule_rec.object_version_number;
1049
1050 -- /striping/
1051 p_appl_id := okc_rld_pvt.get_appl_id(rule_rec.rule_information_category);
1052 p_dff_name := okc_rld_pvt.get_dff_name(rule_rec.rule_information_category);
1053
1054 -- okc_debug.log('3400: Before get_dff_column_values');
1055 -- okc_time_util_pub.get_dff_column_values( p_app_id => 510, -- /striping/
1056 okc_time_util_pub.get_dff_column_values( p_app_id => p_appl_id,
1057 -- p_dff_name => 'OKC Rule Developer DF', -- /striping/
1058 p_dff_name => p_dff_name,
1059 p_rdf_code => rule_rec.rule_information_category,
1060 p_fvs_name => 'FND_STANDARD_DATE',
1061 p_rule_id => rule_rec.id,
1062 p_col_vals => l_col_vals,
1063 p_no_of_cols => l_no_of_cols );
1064 IF (l_debug = 'Y') THEN
1065 okc_debug.set_trace_off;
1066 END IF;
1067 -- okc_debug.log('3500: After get_dff_column_values');
1068
1069 if nvl(l_no_of_cols,0) >= 1 then
1070
1071 --san comment disable the rule here later
1072 null;
1073 end if; -- the rule has absolute dates
1074
1075 -- /striping/
1076 p_appl_id := okc_rld_pvt.get_appl_id(rule_rec.rule_information_category);
1077 p_dff_name := okc_rld_pvt.get_dff_name(rule_rec.rule_information_category);
1078
1079 -- okc_debug.log('3600: Before get_dff_column_values');
1080 -- okc_time_util_pub.get_dff_column_values( p_app_id => 510, -- /striping/
1081 okc_time_util_pub.get_dff_column_values( p_app_id => p_appl_id,
1082 -- p_dff_name => 'OKC Rule Developer DF', -- /striping/
1083 p_dff_name => p_dff_name,
1084 p_rdf_code => rule_rec.rule_information_category,
1085 p_fvs_name => 'OKC_TIMEVALUES',
1086 p_rule_id => rule_rec.id,
1087 p_col_vals => l_col_vals,
1088 p_no_of_cols => l_no_of_cols );
1089 IF (l_debug = 'Y') THEN
1090 okc_debug.set_trace_off;
1091 END IF;
1092 -- okc_debug.log('3700: After get_dff_column_values');
1093 if nvl(l_no_of_cols,0) >= 1 then
1094
1095 -- Implies that there are some rule attribute categories for the rule which have time values
1096 -- okc_debug.log('3800: Before For Loop');
1097 for i in l_col_vals.first..l_col_vals.last
1098 loop
1099 if l_col_vals.exists(i) then
1100 tve_rec:=get_tve_rec(l_col_vals(i).col_value);
1101 IF tve_rec.id <> OKC_API.G_MISS_NUM then
1102 -- in the below type checks we are not checking for effectivity limits on cover times
1103 --and react_intervals as they are always bound by the effectivity of lines
1104 -- to which they are attached in OKS. But in case this changes, then check and
1105 -- adjust the rules and tve_id's effectivities in these tables as well
1106 If tve_rec.tve_type = 'TAV' then
1107 --san comment later disable the rule;
1108 null;
1109 ELSIF tve_rec.tve_type = 'ISE' then --ise
1110 --fetch the dates
1111 x_return_status:=check_ia(tve_rec.id,tve_rec.object_version_number,rule_rec,l_cle_dates_rec);
1112 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1113 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1114 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
1115 RAISE OKC_API.G_EXCEPTION_ERROR;
1116 END IF;
1117 ELSIF tve_rec.tve_id_limited is not null then
1118 --this means that there is some effectivity specified on this time value
1119 tve_rec:=get_tve_rec(tve_rec.tve_id_limited);
1120 --fetch the dates
1121 x_return_status:=check_ia(tve_rec.id,tve_rec.object_version_number,rule_rec,l_cle_dates_rec);
1122 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1123 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1124 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
1125 RAISE OKC_API.G_EXCEPTION_ERROR;
1126 END IF;
1127 END IF; --tve_type
1128 END IF; -- tve_rec is OKC_API.G_MISS_NUM
1129 END IF; -- if col_vals exists
1130 END loop;
1131 -- okc_debug.log('3900: After For Loop');
1132 END IF; -- if col_vals>=1
1133 -- okc_debug.log('4000: After EndIf');
1134 END IF; -- id not found in dates_rec table
1135 -- okc_debug.log('41000: After EndIf');
1136 END loop;
1137 IF (l_debug = 'Y') THEN
1138 okc_debug.set_trace_on;
1139 okc_debug.log('4200: After cur_rules Cursor');
1140 END IF;
1141 --reset the dates table
1142 -- g_cle_dates_tbl.delete;
1143
1144 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1145
1146 IF (l_debug = 'Y') THEN
1147 okc_debug.log('4300: Leaving Update_Rules', 2);
1148 okc_debug.Reset_Indentation;
1149 END IF;
1150
1151 EXCEPTION
1152 WHEN E_Resource_Busy THEN
1153 IF (l_debug = 'Y') THEN
1154 okc_debug.set_trace_on;
1155 okc_debug.log('4400: Exiting Update_Rules:E_Resource_Busy Exception', 2);
1156 okc_debug.Reset_Indentation;
1157 END IF;
1158
1159
1160 IF (cur_time_values%ISOPEN) THEN
1161 CLOSE cur_time_values;
1162 END IF;
1163 x_return_status := okc_api.g_ret_sts_error;
1164 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1165
1166 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1167
1168 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1169 IF (l_debug = 'Y') THEN
1170 okc_debug.set_trace_on;
1171 okc_debug.log('4500: Exiting Update_Rules:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1172 okc_debug.Reset_Indentation;
1173 END IF;
1174
1175 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1176 (l_api_name,
1177 G_PKG_NAME,
1178 'OKC_API.G_RET_STS_ERROR',
1179 x_msg_count,
1180 x_msg_data,
1181 '_PROCESS');
1182
1183 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1184 IF (l_debug = 'Y') THEN
1185 okc_debug.set_trace_on;
1186 okc_debug.log('4600: Exiting Update_Rules:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1187 okc_debug.Reset_Indentation;
1188 END IF;
1189
1190 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1191 (l_api_name,
1192 G_PKG_NAME,
1193 'OKC_API.G_RET_STS_UNEXP_ERROR',
1194 x_msg_count,
1195 x_msg_data,
1196 '_PROCESS');
1197
1198 WHEN OTHERS THEN
1199 IF (l_debug = 'Y') THEN
1200 okc_debug.set_trace_on;
1201 okc_debug.log('4700: Exiting Update_Rules:OTHERS Exception', 2);
1202 okc_debug.Reset_Indentation;
1203 END IF;
1204
1205 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1206 (l_api_name,
1207 G_PKG_NAME,
1208 'OTHERS',
1209 x_msg_count,
1210 x_msg_data,
1211 '_PROCESS');
1212
1213
1214 END Update_Rules;
1215 */
1216
1217 PROCEDURE update_condition_headers (
1218 p_api_version IN NUMBER,
1219 p_init_msg_list IN VARCHAR2 ,
1220 x_return_status OUT NOCOPY VARCHAR2,
1221 x_msg_count OUT NOCOPY NUMBER,
1222 x_msg_data OUT NOCOPY VARCHAR2,
1223 p_chr_id IN NUMBER,
1224 p_orig_start_date IN DATE,
1225 p_orig_end_date IN DATE,
1226 p_new_start_date IN DATE,
1227 p_new_end_date IN DATE ) is
1228
1229 CURSOR cur_condition_headers is
1230 SELECT cnh.id,cnh.object_version_number,cnh.date_active,cnh.date_inactive
1231 FROM okc_condition_headers_b cnh
1232 WHERE dnz_chr_id = p_chr_id;
1233
1234
1235 l_cnh_rec okc_conditions_pub.cnhv_rec_type;
1236 i_cnh_rec okc_conditions_pub.cnhv_rec_type;
1237
1238 E_Resource_Busy EXCEPTION;
1239 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1240
1241 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1242 l_api_name constant varchar2(30) := 'update_headers';
1243 l_duration number;
1244 l_uom_code okx_units_of_measure_v.uom_code%type;
1245 BEGIN
1246
1247 IF (l_debug = 'Y') THEN
1248 okc_debug.Set_Indentation('OKC_RENEW_PVT');
1249 okc_debug.log('4800: Entered update_condition_headers', 2);
1250 END IF;
1251
1252 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1253
1254 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1255 p_init_msg_list,
1256 '_PROCESS',
1257 x_return_status);
1258
1259 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1260 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1261 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1262 raise OKC_API.G_EXCEPTION_ERROR;
1263 END IF;
1264
1265 IF (l_debug = 'Y') THEN
1266 okc_debug.log('4900: Before cur_condition_headers Cursor');
1267 END IF;
1268 FOR conditions_rec in cur_condition_headers loop
1269
1270 l_cnh_rec.id := conditions_rec.id;
1271 l_cnh_rec.object_version_number := conditions_rec.object_version_number;
1272
1273 l_cnh_rec.date_active := p_new_start_date;
1274 l_cnh_rec.date_inactive := null;
1275
1276 If conditions_rec.date_inactive is not null then
1277 If conditions_rec.date_inactive = p_orig_end_date AND
1278 conditions_rec.date_active = p_orig_start_date then
1279
1280 l_cnh_rec.date_inactive := p_new_end_date;
1281
1282 ELSE
1283 l_cnh_rec.date_inactive := p_new_start_date;
1284
1285 END IF;
1286 ELSE
1287 IF conditions_rec.date_active <> p_orig_start_date then
1288 l_cnh_rec.date_inactive := p_new_start_date;
1289
1290 END IF;
1291
1292 END if;
1293
1294
1295 IF (l_debug = 'Y') THEN
1296 okc_debug.log('5000: Before update_cond_hdrs');
1297 END IF;
1298 okc_conditions_pub.update_cond_hdrs(
1299 p_api_version => 1,
1300 p_init_msg_list => OKC_API.G_FALSE,
1301 x_return_status => l_return_status,
1302 x_msg_count => x_msg_count,
1303 x_msg_data => x_msg_data,
1304 p_cnhv_rec => l_cnh_rec,
1305 x_cnhv_rec => i_cnh_rec );
1306 IF (l_debug = 'Y') THEN
1307 okc_debug.log('5100: After update_cond_hdrs');
1308 END IF;
1309
1310 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1311 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1312 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1313 RAISE OKC_API.G_EXCEPTION_ERROR;
1314 END IF;
1315
1316 END loop;
1317
1318 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1319
1320 IF (l_debug = 'Y') THEN
1321 okc_debug.log(' 5200: Leaving update_condition_headers', 2);
1322 okc_debug.Reset_Indentation;
1323 END IF;
1324
1325 EXCEPTION
1326 WHEN E_Resource_Busy THEN
1327
1328 IF (l_debug = 'Y') THEN
1329 okc_debug.log('5300: Exiting update_condition_headers:E_Resource_Busy Exception', 2);
1330 okc_debug.Reset_Indentation;
1331 END IF;
1332
1333
1334 x_return_status := okc_api.g_ret_sts_error;
1335 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1336
1337 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1338
1339 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1340
1341 IF (l_debug = 'Y') THEN
1342 okc_debug.log('5400: Exiting update_condition_headers:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1343 okc_debug.Reset_Indentation;
1344 END IF;
1345
1346 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1347 (l_api_name,
1348 G_PKG_NAME,
1349 'OKC_API.G_RET_STS_ERROR',
1350 x_msg_count,
1351 x_msg_data,
1352 '_PROCESS');
1353
1354 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1355
1356 IF (l_debug = 'Y') THEN
1357 okc_debug.log('5500: Exiting update_condition_headers:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1358 okc_debug.Reset_Indentation;
1359 END IF;
1360
1361 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1362 (l_api_name,
1363 G_PKG_NAME,
1364 'OKC_API.G_RET_STS_UNEXP_ERROR',
1365 x_msg_count,
1366 x_msg_data,
1367 '_PROCESS');
1368
1369 WHEN OTHERS THEN
1370
1371 IF (l_debug = 'Y') THEN
1372 okc_debug.log('5600: Exiting update_condition_headers:OTHERS Exception', 2);
1373 okc_debug.Reset_Indentation;
1374 END IF;
1375
1376 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1377 (l_api_name,
1378 G_PKG_NAME,
1379 'OTHERS',
1380 x_msg_count,
1381 x_msg_data,
1382 '_PROCESS');
1383
1384
1385 END update_condition_headers;
1386
1387 PROCEDURE update_old_contract(
1388 p_api_version IN NUMBER,
1389 p_init_msg_list IN VARCHAR2 ,
1390 x_return_status OUT NOCOPY VARCHAR2,
1391 x_msg_count OUT NOCOPY NUMBER,
1392 x_msg_data OUT NOCOPY VARCHAR2,
1393 p_new_header IN number,
1394 p_chr_id IN number) is
1395
1396 CURSOR cur_header(p_chr_id number) is
1397 SELECT id,object_version_number
1398 FROM okc_k_headers_b
1399 WHERE id = p_chr_id;
1400
1401 CURSOR cur_lines(p_chr_id number) is
1402 SELECT a.id,b.id cle_id_ren_to ,a.object_version_number
1403 FROM okc_k_lines_b a,okc_k_lines_b b
1404 WHERE a.dnz_chr_id = p_chr_id and a.id=b.cle_id_renewed and b.dnz_chr_id=p_new_header;
1405
1406 TYPE objTab is table of okc_k_lines_b.object_version_number%type;
1407
1408 l_id_tbl idTab;
1409 l_obj_tbl objtab;
1410 l_ren_id_tbl idTab;
1411
1412 header_rec cur_header%rowtype;
1413
1414 l_chr_rec okc_contract_pub.chrv_rec_type;
1415 i_chr_rec okc_contract_pub.chrv_rec_type;
1416
1417 l_cle_tbl okc_contract_pub.clev_tbl_type;
1418 x_cle_tbl okc_contract_pub.clev_tbl_type;
1419 i number;
1420
1421 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1422 l_api_name constant VARCHAR2(30) := 'update_old_contract';
1423 l_date DATE := trunc(sysdate);
1424 BEGIN
1425 IF (l_debug = 'Y') THEN
1426 okc_debug.Set_Indentation('OKC_RENEW_PVT');
1427 okc_debug.log('5700: Entered update_old_contract', 2);
1428 END IF;
1429
1430 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1431
1432 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1433 p_init_msg_list,
1434 '_PROCESS',
1435 x_return_status);
1436
1437 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1438 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1439 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1440 raise OKC_API.G_EXCEPTION_ERROR;
1441 END IF;
1442
1443 open cur_header(p_chr_id);
1444 fetch cur_header into header_rec;
1445 close cur_header;
1446
1447 l_chr_rec.id := header_rec.id;
1448 l_chr_rec.object_version_number := header_rec.object_version_number;
1449 l_chr_rec.date_renewed := l_date;
1450 --san rencol
1451
1452 IF (l_debug = 'Y') THEN
1453 okc_debug.log('5800: Before lock_contract_header');
1454 END IF;
1455 okc_contract_pub.lock_contract_header(
1456 p_api_version => 1,
1457 p_init_msg_list => OKC_API.G_FALSE,
1458 x_return_status => l_return_status,
1459 x_msg_count => x_msg_count,
1460 x_msg_data => x_msg_data,
1461 p_chrv_rec => l_chr_rec );
1462 IF (l_debug = 'Y') THEN
1463 okc_debug.log('5900: After lock_contract_header');
1464 END IF;
1465
1466 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1467 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1468 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1469 RAISE OKC_API.G_EXCEPTION_ERROR;
1470 END IF;
1471
1472 IF (l_debug = 'Y') THEN
1473 okc_debug.log('6000: Before update_contract_header');
1474 END IF;
1475 okc_contract_pub.update_contract_header (
1476 p_api_version => 1,
1477 p_init_msg_list => OKC_API.G_FALSE,
1478 x_return_status => l_return_status,
1479 x_msg_count => x_msg_count,
1480 x_msg_data => x_msg_data,
1481 p_restricted_update => okc_api.g_true,
1482 p_chrv_rec => l_chr_rec,
1483 x_chrv_rec => i_chr_rec );
1484 IF (l_debug = 'Y') THEN
1485 okc_debug.log('6100: After update_contract_header');
1486 END IF;
1487
1488 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1489 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1490 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1491 RAISE OKC_API.G_EXCEPTION_ERROR;
1492 END IF;
1493
1494 --san rencol
1495 SELECT l.id,l.object_version_number
1496 bulk collect into l_id_tbl,l_obj_tbl
1497 FROM okc_k_lines_b l, okc_operation_lines a
1498 where a.object_chr_id=p_chr_id and
1499 a.subject_chr_id=p_new_header and
1500 a.active_yn='Y'
1501 and l.id=a.object_cle_id and
1502 a.subject_cle_id is not null and a.object_cle_id is not null;
1503
1504 If l_id_tbl.count>0 then
1505 i:=l_id_tbl.first;
1506 LOOP
1507 l_cle_tbl(i).id:=l_id_tbl(i);
1508 l_cle_tbl(i).object_version_number:=l_obj_tbl(i);
1509 l_cle_tbl(i).date_renewed:= l_date;
1510
1511 EXIT WHEN i=l_id_tbl.last;
1512 i:=l_id_tbl.next(i);
1513 END LOOP;
1514 okc_contract_pub.lock_contract_line(
1515 p_api_version => 1,
1516 p_init_msg_list => OKC_API.G_FALSE,
1517 x_return_status => l_return_status,
1518 x_msg_count => x_msg_count,
1519 x_msg_data => x_msg_data,
1520 p_clev_tbl => l_cle_tbl );
1521
1522 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1523 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1524 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1525 RAISE OKC_API.G_EXCEPTION_ERROR;
1526 END IF;
1527
1528 okc_contract_pub.update_contract_line (
1529 p_api_version => 1,
1530 p_init_msg_list => OKC_API.G_FALSE,
1531 x_return_status => l_return_status,
1532 x_msg_count => x_msg_count,
1533 x_msg_data => x_msg_data,
1534 p_restricted_update => okc_api.g_true,
1535 p_clev_tbl => l_cle_tbl,
1536 x_clev_tbl => x_cle_tbl );
1537
1538 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1539 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1540 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1541 RAISE OKC_API.G_EXCEPTION_ERROR;
1542 END IF;
1543 END IF; -- if some lines are there that renewed
1544
1545
1546 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1547 --dbms_output.put_line('old (-)');
1548
1549 IF (l_debug = 'Y') THEN
1550 okc_debug.log('6200: Leaving update_old_contract', 2);
1551 okc_debug.Reset_Indentation;
1552 END IF;
1553
1554 EXCEPTION
1555 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1556
1557 IF (l_debug = 'Y') THEN
1558 okc_debug.log('6300: Exiting update_old_contract:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1559 okc_debug.Reset_Indentation;
1560 END IF;
1561
1562 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1563 (l_api_name,
1564 G_PKG_NAME,
1565 'OKC_API.G_RET_STS_ERROR',
1566 x_msg_count,
1567 x_msg_data,
1568 '_PROCESS');
1569
1570 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1571
1572 IF (l_debug = 'Y') THEN
1573 okc_debug.log('6400: Exiting update_old_contract:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1574 okc_debug.Reset_Indentation;
1575 END IF;
1576
1577 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1578 (l_api_name,
1579 G_PKG_NAME,
1580 'OKC_API.G_RET_STS_UNEXP_ERROR',
1581 x_msg_count,
1582 x_msg_data,
1583 '_PROCESS');
1584
1585 WHEN OTHERS THEN
1586
1587 IF (l_debug = 'Y') THEN
1588 okc_debug.log('6500: Exiting update_old_contract:OTHERS Exception', 2);
1589 okc_debug.Reset_Indentation;
1590 END IF;
1591
1592 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1593 (l_api_name,
1594 G_PKG_NAME,
1595 'OTHERS',
1596 x_msg_count,
1597 x_msg_data,
1598 '_PROCESS');
1599 END update_old_contract;
1600
1601 -------------------------------------------------------------------------------------------
1602 --Adjust the dates in the renew copy as per new start and end dates
1603 -------------------------------------------------------------------------------------------
1604 /*
1605 * bug 5438257
1606 * stubbed
1607 *
1608 */
1609 PROCEDURE update_renewal_dates(p_api_version IN NUMBER,
1610 p_init_msg_list IN VARCHAR2 ,
1611 x_return_status OUT NOCOPY VARCHAR2,
1612 x_msg_count OUT NOCOPY NUMBER,
1613 x_msg_data OUT NOCOPY VARCHAR2,
1614 p_chr_id IN NUMBER,
1615 p_parent_cle_id IN NUMBER ,
1616 p_parent_new_st_dt IN DATE,
1617 p_parent_new_end_dt IN DATE,
1618 p_parent_old_st_dt IN DATE,
1619 p_cle_id IN NUMBER ,
1620 p_rencon_yn IN VARCHAR2 ) is
1621 BEGIN
1622 null;
1623 END update_renewal_dates;
1624 /*
1625 * bug 5438257
1626 PROCEDURE update_renewal_dates(p_api_version IN NUMBER,
1627 p_init_msg_list IN VARCHAR2 ,
1628 x_return_status OUT NOCOPY VARCHAR2,
1629 x_msg_count OUT NOCOPY NUMBER,
1630 x_msg_data OUT NOCOPY VARCHAR2,
1631 p_chr_id IN NUMBER,
1632 p_parent_cle_id IN NUMBER ,
1633 p_parent_new_st_dt IN DATE,
1634 p_parent_new_end_dt IN DATE,
1635 p_parent_old_st_dt IN DATE,
1636 p_cle_id IN NUMBER ,
1637 p_rencon_yn IN VARCHAR2 ) is
1638
1639 --/Rules Migration/-added application id
1640 CURSOR cur_headers is
1641 Select start_date,end_date,object_version_number,application_id
1642 from okc_k_headers_b
1643 where id = p_chr_id;
1644
1645 CURSOR cur_lines(p_id number) is
1646 SELECT id,object_version_number,start_date,end_date,level,cle_id
1647 FROM okc_k_lines_b
1648 where dnz_chr_id=p_id
1649 start with (chr_id=p_id)
1650 connect by prior id=cle_id;
1651
1652 -- Modiifed for Bug 2084147
1653 -- Added new field price_negotiated in the SELECT
1654 Cursor l_rencon_n_cur(l_chr_id number) is
1655 SELECT id,object_version_number,start_date,END_date,level,cle_id,price_negotiated
1656 FROM okc_k_lines_b
1657 where dnz_chr_id=l_chr_id
1658 start with (chr_id=l_chr_id)
1659 connect by prior id=cle_id;
1660
1661 -- Modiifed for Bug 2084147
1662 -- Added new field price_negotiated in the SELECT
1663 Cursor l_rencon_y_cur(l_chr_id number,l_cle_id number) is
1664 SELECT id,object_version_number,start_date,END_date,level,cle_id,price_negotiated
1665 FROM okc_k_lines_b
1666 where dnz_chr_id=l_chr_id
1667 start with (id=l_cle_id)
1668 connect by prior id=cle_id;
1669
1670 -- Modified for bug 2084147
1671 -- Added new field price_negotiated in the record definition
1672 TYPE CLE_REC_TYPE IS RECORD(
1673 ID NUMBER:=OKC_API.G_MISS_NUM,
1674 object_version_number NUMBER:=OKC_API.G_MISS_NUM,
1675 start_date DATE:=OKC_API.G_MISS_DATE,
1676 end_date DATE:=OKC_API.G_MISS_DATE,
1677 level NUMBER:=OKC_API.G_MISS_NUM,
1678 CLE_ID NUMBER:=OKC_API.G_MISS_NUM,
1679 price_negotiated NUMBER:=OKC_API.G_MISS_NUM
1680 );
1681 lines_rec CLE_REC_TYPE;
1682
1683 l_num number;
1684
1685 header_rec cur_headers%rowtype;
1686 rule_rec cur_rules%rowtype;
1687
1688 l_chr_rec okc_contract_pub.chrv_rec_type;
1689 i_chr_rec okc_contract_pub.chrv_rec_type;
1690
1691 l_cle_rec okc_contract_pub.clev_rec_type;
1692 i_cle_rec okc_contract_pub.clev_rec_type;
1693
1694 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1695 l_api_name constant VARCHAR2(30) := 'update_renewal_dates';
1696 l_uom_code okx_units_of_measure_v.uom_code%type;
1697 l_duration number;
1698
1699 l_parent_orig_start_date date;
1700 l_parent_end_date date;
1701 l_parent_start_date date;
1702 l_parent_level number;
1703 l_DNR_LEVEL number:=0;
1704 l_cle_renew_type OKC_RULES_V.rule_information1%type;
1705 l_cle_tbl okc_contract_pub.clev_tbl_type;
1706 l_cle BINARY_INTEGER:=0;
1707 j BINARY_INTEGER;
1708
1709 k_dates number :=0;
1710 TYPE CLE_PARENT_REC_TYPE IS RECORD(
1711 ID NUMBER:=OKC_API.G_MISS_NUM,
1712 orig_start_date DATE:=OKC_API.G_MISS_DATE,
1713 start_date DATE:=OKC_API.G_MISS_DATE,
1714 end_date DATE:=OKC_API.G_MISS_DATE,
1715 lrt_type okc_rules_v.rule_information_category%TYPE:=OKC_API.G_MISS_CHAR
1716 );
1717
1718 TYPE CLE_PARENT_TBL_TYPE IS TABLE OF CLE_PARENT_REC_TYPE
1719 INDEX BY BINARY_INTEGER;
1720
1721 l_cle_parent_rec CLE_PARENT_REC_TYPE;
1722 l_parent_cle_tbl CLE_PARENT_TBL_TYPE;
1723 l_additional_days NUMBER;
1724
1725 Function get_parent(p_cle_id number) Return cle_parent_rec_type is
1726 l_rec CLE_PARENT_REC_TYPE;
1727 i BINARY_INTEGER;
1728 l_api_name Varchar2(30) := 'get_parent';
1729 Begin
1730
1731 IF (l_debug = 'Y') THEN
1732 okc_debug.Set_Indentation('get_parent');
1733 okc_debug.log('6500: Entered Get_Parent', 2);
1734 END IF;
1735
1736 If l_parent_cle_tbl.count >0 then
1737 i:=l_parent_cle_tbl.first;
1738 LOOP
1739 If l_parent_cle_tbl(i).id=p_cle_id then
1740 -- MKS: Following line is introduced to fix Bug#2106425
1741 IF (l_debug = 'Y') THEN
1742 okc_debug.Reset_Indentation;
1743 END IF;
1744 return l_parent_cle_tbl(i);
1745 END IF;
1746 Exit when i=l_parent_cle_tbl.last;
1747 i:=l_parent_cle_tbl.next(i);
1748 END LOOP;
1749 END IF;
1750
1751 IF (l_debug = 'Y') THEN
1752 okc_debug.log('6510: Leaving Function Get_Parent ', 2);
1753 okc_debug.Reset_Indentation;
1754 END IF;
1755
1756 return l_rec;
1757 END;
1758
1759 Function get_cle_renew_type(p_comp_id number) Return varchar2 is
1760 l_return OKC_RULES_V.rule_information1%type:=g_def_cle_ren;
1761 i BINARY_INTEGER;
1762 l_rule okc_rules_v.rule_information1%type;
1763 l_api_name Varchar2(30) := 'get_cle_renew_type';
1764 Begin
1765
1766 IF (l_debug = 'Y') THEN
1767 okc_debug.Set_Indentation('get_cle_renew_type');
1768 okc_debug.log('6600: Entered get_cle_renew_type', 2);
1769 END IF;
1770
1771
1772 l_rule:=OKC_API.G_MISS_CHAR;
1773 If g_rules_tbl.count>0 then
1774 i:=g_rules_tbl.first;
1775 loop
1776 If g_rules_tbl(i).comp_id = p_comp_id then
1777 l_rule:=g_rules_tbl(i).rule_type;
1778 -- MKS: Following line is introduced to fix Bug#2106425
1779 IF (l_debug = 'Y') THEN
1780 okc_debug.Reset_Indentation;
1781 END IF;
1782 return l_rule;
1783 End If;
1784 Exit when i = g_rules_tbl.last;
1785 i:=g_rules_tbl.next(i);
1786 End Loop;
1787 End If;
1788
1789 IF (l_debug = 'Y') THEN
1790 okc_debug.log('6610: Leaving Function Get_Cle_Renew_Type ', 2);
1791 okc_debug.Reset_Indentation;
1792 END IF;
1793
1794 return l_rule;
1795 End;
1796
1797 BEGIN
1798
1799 IF (l_debug = 'Y') THEN
1800 okc_debug.Set_Indentation('OKC_RENEW_PVT');
1801 okc_debug.log('6700: Entered Update_Renewal_Dates', 2);
1802 END IF;
1803
1804 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1805
1806 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1807 p_init_msg_list,
1808 '_PROCESS',
1809 x_return_status);
1810
1811 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1812 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1813 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1814 raise OKC_API.G_EXCEPTION_ERROR;
1815 END IF;
1816
1817 --reset global table for dates
1818 -- g_cle_dates_tbl.delete;
1819
1820 --/Rules migration/ moved cursor from below.
1821 -- Get the info about the header unconditionally
1822 Open cur_headers;
1823 Fetch cur_headers into header_rec;
1824 Close cur_headers;
1825
1826 --san added for renconsolidation as for calls from oks line renewal, header reneewal is not needed
1827 If p_rencon_yn='N' then
1828
1829 --/Rules Migration/ Moved above
1830 -- Get the info about the header and update dates
1831 -- open cur_headers;
1832 -- fetch cur_headers into header_rec;
1833 -- close cur_headers;
1834
1835 l_chr_rec.id := p_chr_id;
1836 l_chr_rec.object_version_number := header_rec.object_version_number;
1837 l_chr_rec.start_date := p_parent_new_st_dt;
1838 l_chr_rec.END_date := p_parent_new_end_dt;
1839
1840
1841 IF (l_debug = 'Y') THEN
1842 okc_debug.log('6800: Before update_contract_header');
1843 END IF;
1844 l_chr_rec.start_date := TO_DATE(TO_CHAR(l_chr_rec.start_date, 'dd/mm/yyYY') || TO_CHAR(header_rec.start_date, 'hh24:mi:ss'), 'dd/mm/yyYYhh24:mi:ss');
1845 if l_chr_rec.end_date is not null then -- Added for Bugfix 2803674 to avoid Perpetual
1846 l_chr_rec.end_date := TO_DATE(TO_CHAR(l_chr_rec.end_date, 'dd/mm/yyYY') || TO_CHAR(header_rec.end_date, 'hh24:mi:ss'), 'dd/mm/yyYYhh24:mi:ss');
1847 end if;
1848
1849 okc_contract_pub.update_contract_header (
1850 p_api_version => 1,
1851 p_init_msg_list => OKC_API.G_FALSE,
1852 x_return_status => l_return_status,
1853 x_msg_count => x_msg_count,
1854 x_msg_data => x_msg_data,
1855 p_chrv_rec => l_chr_rec,
1856 x_chrv_rec => i_chr_rec );
1857
1858
1859 IF (l_debug = 'Y') THEN
1860 okc_debug.log('6900: After update_contract_header');
1861 END IF;
1862
1863 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1864 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1865 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1866 RAISE OKC_API.G_EXCEPTION_ERROR;
1867 END IF;
1868
1869 -- added 19-MAR-2002 by rgalipo -- performance bug
1870 -- added call to cursor on temporary table
1871 INSERT INTO okc_cle_dates_tmp(ID, ORIG_START_DATE, ORIG_END_DATE,
1872 START_DATE, END_DATE)
1873 VALUES (
1874 p_chr_id, header_rec.start_date, header_rec.end_date,
1875 p_parent_new_st_dt, p_parent_new_end_dt);
1876
1877 -- added 19-MAR-2002 by rgalipo -- performance bug
1878 -- removed dependency on pl/sql tables
1879 -- use temporary table to store data
1880
1881 --put headers dates in global dates table
1882 -- k_dates:=1;
1883 -- g_cle_dates_tbl(k_dates).id:=p_chr_id;
1884 -- g_cle_dates_tbl(k_dates).orig_start_date:=header_rec.start_date;
1885 -- g_cle_dates_tbl(k_dates).orig_end_date:=header_rec.end_date;
1886 -- g_cle_dates_tbl(k_dates).start_date:=p_parent_new_st_dt;
1887 -- g_cle_dates_tbl(k_dates).end_date:=p_parent_new_end_dt;
1888
1889 END IF; --rencon_yn
1890
1891 -- Make a table of 'LRT' rule values defined for the lines of the new contract
1892 -- again if current chr id is different from the one that called this program last time.
1893 IF g_cached_chr_id <> p_chr_id then
1894 j:=1;
1895
1896 --/Rules migration/
1897 If header_rec.application_id in(510,871) Then
1898 Open cur_rules(p_chr_id);
1899 Loop
1900 Fetch cur_rules into rule_rec;
1901
1902 Exit when cur_rules%notfound;
1903 g_rules_tbl(j).comp_id:=rule_rec.comp_id;
1904 g_rules_tbl(j).rule_type:=rule_rec.rule_type;
1905 j:=j+1;
1906 End Loop;
1907 If cur_rules%isopen then
1908 close cur_rules;
1909 End If;
1910 Else
1911 For cur_lines_rec in cur_line(p_chr_id) Loop
1912 g_rules_tbl(j).comp_id:=cur_lines_rec.comp_id;
1913 g_rules_tbl(j).rule_type:=cur_lines_rec.rule_type;
1914 j:=j+1;
1915 End Loop;
1916 End If;
1917 --
1918
1919 END IF;
1920 --Now for all the lines, adjust dates based on the rule value 'LRT' for them
1921 IF p_rencon_yn='N' then
1922 -- then build a heirarchy for all lines for this contract
1923 OPEN l_rencon_n_cur(p_chr_id);
1924 ELSE
1925 --else if renew consolidation, build a heirarchy of lines starting from line sent in
1926 OPEN l_rencon_y_cur(p_chr_id,p_cle_id);
1927
1928 END IF; --p_rencon_yn
1929
1930 loop
1931 -- Modified for bug 2084147
1932 -- Fetching new field price_negotiated
1933 IF p_rencon_yn='N' then
1934 FETCH l_rencon_n_cur into lines_rec.id,lines_rec.object_version_number,lines_rec.start_date,
1935 lines_rec.end_date,lines_rec.level,lines_rec.cle_id,lines_rec.price_negotiated ;
1936 EXIT WHEN l_rencon_n_cur%NOTFOUND;
1937 ELSE
1938 FETCH l_rencon_y_cur into lines_rec.id,lines_rec.object_version_number,lines_rec.start_date,
1939 lines_rec.end_date,lines_rec.level,lines_rec.cle_id,lines_rec.price_negotiated ;
1940 EXIT WHEN l_rencon_y_cur%NOTFOUND;
1941 END IF;
1942
1943 -- check if the parent of the line had rule type 'DNR'
1944 IF (l_dnr_level=0) OR ( l_dnr_level > 0 and l_dnr_level >= lines_rec.level) then
1945 --reset DNR level
1946 l_DNR_level:=0;
1947 -- check the renewal type of line
1948 l_cle_renew_type:=get_cle_renew_type(lines_rec.id);
1949
1950 -- the below code added in place of the commented code to fix bug 1398533
1951 If lines_rec.cle_id is null then
1952 If l_cle_renew_type = OKC_API.G_MISS_CHAR then
1953 l_cle_renew_type:=get_cle_renew_type(p_chr_id);
1954 IF l_cle_renew_type = OKC_API.G_MISS_CHAR then
1955 l_cle_renew_type:=g_def_cle_ren;
1956 END IF;
1957 END IF;
1958 --san rencon
1959 ELSIF (p_rencon_yn='Y' and lines_rec.id=p_cle_id) then
1960 -- that means that this line is a renew consol line sent in and not a top line
1961 If l_cle_renew_type = OKC_API.G_MISS_CHAR then
1962 If g_cached_cle_id <> p_parent_cle_id then
1963 -- renew_rule not defined for this line, we will have to get it from its parent
1964 -- so collect a tree of its parents
1965 SELECT id
1966 bulk collect into g_parent_id_tbl
1967 FROM okc_k_lines_b
1968 where dnz_chr_id=p_chr_id
1969 start with (id=p_parent_cle_id)
1970 connect by prior cle_id=id;
1971
1972 IF p_parent_cle_id<> OKC_API.G_MISS_NUM then
1973 g_cached_cle_id:=p_parent_cle_id;
1974 END IF;
1975 END IF;
1976
1977 l_num:=g_parent_id_tbl.FIRST;
1978 LOOP
1979 EXIT when (l_cle_renew_type <> OKC_API.G_MISS_CHAR) OR l_num=g_parent_id_tbl.last;
1980 --exit when either renew rule is found or none of its parents have any renew rule
1981 --defined. In the second case we will check the header for rule
1982 l_cle_renew_type:= get_cle_renew_type(g_parent_id_tbl(l_num));
1983 l_num:=g_parent_id_tbl.next(l_num);
1984 END LOOP;
1985 If l_cle_renew_type = OKC_API.G_MISS_CHAR then
1986 -- if the renew rule could not be found on any of the parents, get from header
1987 l_cle_renew_type:=get_cle_renew_type(p_chr_id);
1988 IF l_cle_renew_type = OKC_API.G_MISS_CHAR then
1989 -- if even the header doesnot have the rule, take the default.
1990 l_cle_renew_type:=g_def_cle_ren;
1991 END IF;
1992 END IF;
1993
1994 END IF;
1995 ELSE
1996 --get the values for parent rec
1997 l_cle_parent_rec:=get_parent(lines_rec.cle_id);
1998 If l_cle_renew_type = OKC_API.G_MISS_CHAR then
1999 l_cle_renew_type:=l_cle_parent_rec.lrt_type;
2000 END IF;
2001
2002 END IF;
2003 IF (l_debug = 'Y') THEN
2004 okc_debug.log('7000: l_cle_renew_type ' || l_cle_renew_type);
2005 END IF;
2006
2007 IF l_cle_renew_type='DNR' then
2008 l_cle:=l_cle+1;
2009 l_cle_tbl(l_cle).id:=lines_rec.id;
2010 l_cle_tbl(l_cle).object_version_number:=lines_rec.object_version_number;
2011 -- Adding for bug 2084147
2012 l_cle_tbl(l_cle).price_negotiated:=lines_rec.price_negotiated;
2013 l_DNR_level:=lines_rec.level;
2014 ELSE
2015 --populate the parent start dates.
2016 --IF lines_rec.level=1 then
2017 --IF lines_rec.cle_id is null then
2018 --san rencon
2019 IF lines_rec.cle_id is null or lines_rec.id=p_cle_id then
2020 l_parent_end_date:=p_parent_new_end_dt;
2021 l_parent_start_date:=p_parent_new_st_dt;
2022 l_parent_orig_start_date :=p_parent_old_st_dt;
2023 l_parent_cle_tbl.delete;
2024 j:=0;
2025 -- when sub line of line last processed
2026 --ELSIF lines_rec.level > l_parent_level AND lines_rec.level > l_cle_rec.level then
2027 ELSE
2028 -- not needed to reget the parent rec here as we already did it once above while getting renewal type
2029 --get the values for parent rec
2030 -- l_cle_parent_rec:=get_parent(lines_rec.cle_id);
2031 l_parent_orig_start_date :=l_cle_parent_rec.orig_start_date;
2032 l_parent_end_date:=l_cle_parent_rec.end_date;
2033 l_parent_start_date:=l_cle_parent_rec.start_date;
2034 END IF;
2035
2036 l_cle_rec.id := lines_rec.id;
2037 l_cle_rec.object_version_number := lines_rec.object_version_number;
2038 IF l_cle_renew_type = 'FUL' then
2039 l_cle_rec.end_date:=l_parent_end_date;
2040 --san rencon the below line commented and added a new one below
2041 --l_cle_rec.start_date:=l_parent_start_date;
2042 -- the logic below added for renconsolidation but also valid for us as for us the
2043 -- new start date will be the start_date of the parent most of the times.
2044 IF lines_rec.cle_id is null then
2045 -- that means this is a top line so it starts at the same time as header
2046 l_cle_rec.start_date:=l_parent_start_date;
2047 else
2048 -- for sublines
2049 -- that means the new start date has to be old_end_date+1 or parent start
2050 --date whichever is greater
2051 l_cle_rec.start_date:=greatest(l_parent_start_date,lines_rec.end_date+1);
2052 END IF; -- cle_id is null
2053 ELSIF l_cle_renew_type = 'KEP' then
2054 -- Bug#2249285: Replaced get_duration with oracle_months_and_days
2055 --
2056 -- okc_time_util_pub.get_duration(l_parent_orig_start_date,lines_rec.start_date, l_duration,l_uom_code,l_return_status);
2057 okc_time_util_pvt.get_oracle_months_and_days(l_parent_orig_start_date,lines_rec.start_date, l_duration,l_additional_days,l_return_status);
2058
2059 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
2060 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2061 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
2062 OKC_API.set_message(p_app_name => g_app_name,
2063 p_msg_name => 'OKC_GET_DURATION_ERROR');
2064
2065 RAISE OKC_API.G_EXCEPTION_ERROR;
2066 END IF;
2067
2068 -- Bug#2249285: Changed to add_months + days for offset calculations.
2069 --
2070 -- l_cle_rec.start_date := okc_time_util_pub.get_ENDdate(l_parent_start_date,l_uom_code,l_duration);
2071 --
2072 l_cle_rec.start_date := ADD_MONTHS(l_parent_Start_date, l_duration) + l_additional_days;
2073
2074 -- For perpetual contracts, the parent may not have any end date
2075 IF l_cle_rec.start_date <= nvl(l_parent_end_date,l_cle_rec.start_date) then
2076 -- Bug#2249285: Replaced get_duration with oracle_months_and_days
2077 -- okc_time_util_pub.get_duration(l_parent_orig_start_date,
2078 -- lines_rec.end_date,l_duration,l_uom_code,l_return_status);
2079 l_duration := 0;
2080 l_additional_days := 0;
2081 okc_time_util_pvt.get_oracle_months_and_days(l_parent_orig_start_date,lines_rec.end_date, l_duration,l_additional_days,l_return_status);
2082
2083 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
2084 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2085 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
2086 OKC_API.set_message(p_app_name => g_app_name,
2087 p_msg_name => 'OKC_GET_DURATION_ERROR');
2088 RAISE OKC_API.G_EXCEPTION_ERROR;
2089 END IF;
2090 -- Bug#2249285: Changed to add_months + days for offset calculations.
2091
2092 --
2093 -- l_cle_rec.END_date := okc_time_util_pub.get_ENDdate(l_parent_start_date, l_uom_code,l_duration);
2094
2095 l_cle_rec.end_date := add_months(l_parent_Start_date, l_duration) + l_additional_days;
2096
2097 IF l_cle_rec.end_date > nvl(l_parent_end_date,l_cle_rec.end_date) then
2098 l_cle_rec.end_date:=l_parent_end_date;
2099 END IF;
2100 ELSE
2101 l_cle_rec.start_date:=l_parent_start_date;
2102 l_cle_rec.end_date:=l_parent_start_date;
2103 END IF; -- new_start_date < parent_start_date
2104 END IF;-- renew type FUL or KEP
2105
2106
2107 IF (l_debug = 'Y') THEN
2108 okc_debug.log('7100: Before update_contract_line');
2109 END IF;
2110
2111 --Added decode for Bug 2911298
2112 SELECT TO_DATE(TO_CHAR(l_cle_rec.start_date, 'dd/mm/yyYY') || TO_CHAR(start_date, 'hh24:mi:ss'), 'dd/mm/yyYYhh24:mi:ss'),
2113 decode(l_cle_rec.end_date, null, null, TO_DATE(TO_CHAR(l_cle_rec.end_date, 'dd/mm/yyYY') || TO_CHAR(end_date, 'hh24:mi:ss'), 'dd/mm/yyYYhh24:mi:ss'))
2114 INTO l_cle_rec.start_date, l_cle_rec.end_date
2115 FROM okc_k_lines_b
2116 WHERE id = (SELECT object_cle_id FROM okc_operation_lines WHERE subject_cle_id = l_cle_rec.id);
2117
2118 okc_contract_pub.update_contract_line (
2119 p_api_version => 1,
2120 p_init_msg_list => OKC_API.G_FALSE,
2121 x_return_status => l_return_status,
2122 x_msg_count => x_msg_count,
2123 x_msg_data => x_msg_data,
2124 p_clev_rec => l_cle_rec,
2125 x_clev_rec => i_cle_rec );
2126 IF (l_debug = 'Y') THEN
2127 okc_debug.log('7200: After update_contract_line');
2128 END IF;
2129
2130 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
2131 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2132 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
2133 RAISE OKC_API.G_EXCEPTION_ERROR;
2134 END IF;
2135
2136 --add the line in parent table
2137 j:=j+1;
2138 l_parent_cle_tbl(j).id:=l_cle_rec.id;
2139 l_parent_cle_tbl(j).orig_start_date:=lines_rec.start_date;
2140 l_parent_cle_tbl(j).start_date:=l_cle_rec.start_date;
2141 l_parent_cle_tbl(j).end_date:=l_cle_rec.end_date;
2142 l_parent_cle_tbl(j).lrt_type:=l_cle_renew_type;
2143
2144
2145 -- added 19-MAR-2002 by rgalipo -- performance bug
2146 -- insert data into temporary table
2147 -- in order to remove dependency on pl/sql tables
2148 INSERT INTO okc_cle_dates_tmp (ID, ORIG_START_DATE, ORIG_END_DATE,
2149 START_DATE, END_DATE)
2150 VALUES (
2151 l_cle_rec.id, lines_rec.start_date, lines_rec.end_date,
2152 l_cle_rec.start_date, l_cle_rec.end_date);
2153
2154 -- removed dependency on pl/sql tables
2155 -- use temporary table for better performance
2156 --put line dates in dates table
2157 -- k_dates:=k_dates+1;
2158 -- g_cle_dates_tbl(k_dates).id:=l_cle_rec.id;
2159 -- g_cle_dates_tbl(k_dates).orig_start_date:=lines_rec.start_date;
2160 -- g_cle_dates_tbl(k_dates).orig_end_date:=lines_rec.end_date;
2161 -- g_cle_dates_tbl(k_dates).start_date:=l_cle_rec.start_date;
2162 -- g_cle_dates_tbl(k_dates).end_date:=l_cle_rec.end_date;
2163
2164
2165
2166
2167 END IF; -- when the renew rule type is 'DNR'
2168 END IF; -- dnr_level
2169 END loop;-- main loop
2170
2171 IF l_rencon_n_cur%ISOPEN then
2172 close l_rencon_n_cur;
2173 END IF;
2174 IF l_rencon_y_cur%ISOPEN then
2175 close l_rencon_y_cur;
2176 END IF;
2177
2178 IF l_cle_tbl.count>0 then
2179 j:=l_cle_tbl.first;
2180 loop
2181 OKC_CONTRACT_PUB.Delete_Contract_Line(
2182 p_api_version => 1,
2183 p_init_msg_list => OKC_API.G_FALSE,
2184 x_return_status => l_return_status,
2185 x_msg_count => x_msg_count,
2186 x_msg_data => x_msg_data,
2187 p_line_id => l_cle_tbl(j).id);
2188 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
2189 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2190 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
2191 RAISE OKC_API.G_EXCEPTION_ERROR;
2192 END IF;
2193 -- Adding for bug 2084147 - Start
2194 -- IF Line Rule is DONOT RENEW then price_negotiated should be substracted from header
2195 -- level estimated amount
2196 -- Not calling Update Contract api for performance reason as well as it is a process api
2197 -- donot need to do all the validation for updating the price
2198
2199 UPDATE OKC_K_HEADERS_B
2200 set estimated_amount = estimated_amount - l_cle_tbl(j).price_negotiated
2201 WHERE id = p_chr_id;
2202 -- Adding for bug 2084147 - End
2203
2204 exit when j = l_cle_tbl.last;
2205 j:=l_cle_tbl.next(j);
2206 END loop;
2207 END IF;
2208
2209
2210 --update rules has to be called after update_startend as update_start... sets dates table for rules
2211
2212 --update the the dates and timevalues in the rules defined for renew copy
2213
2214 IF (l_debug = 'Y') THEN
2215 okc_debug.log('7300: Before update_rules');
2216 END IF;
2217 update_rules(p_api_version => 1,
2218 p_init_msg_list => OKC_API.G_FALSE,
2219 x_return_status => l_return_status,
2220 x_msg_count => x_msg_count,
2221 x_msg_data => x_msg_data,
2222 p_chr_id => p_chr_id
2223 );
2224 IF (l_debug = 'Y') THEN
2225 okc_debug.log('7400: After update_rules');
2226 END IF;
2227
2228 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
2229 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2230 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
2231 RAISE OKC_API.G_EXCEPTION_ERROR;
2232 END IF;
2233
2234 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2235
2236
2237 IF (l_debug = 'Y') THEN
2238 okc_debug.log('7500: Leaving Update_Renewal_Dates', 2);
2239 okc_debug.Reset_Indentation;
2240 END IF;
2241
2242 EXCEPTION
2243 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2244
2245 IF (l_debug = 'Y') THEN
2246 okc_debug.log('7600: Exiting Update_Renewal_Dates:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2247 okc_debug.Reset_Indentation;
2248 END IF;
2249
2250 IF l_rencon_n_cur%ISOPEN then
2251 close l_rencon_n_cur;
2252 END IF;
2253 IF l_rencon_y_cur%ISOPEN then
2254 close l_rencon_y_cur;
2255 END IF;
2256 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2257 (l_api_name,
2258 G_PKG_NAME,
2259 'OKC_API.G_RET_STS_ERROR',
2260 x_msg_count,
2261 x_msg_data,
2262 '_PROCESS');
2263
2264 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2265
2266 IF (l_debug = 'Y') THEN
2267 okc_debug.log('7700: Exiting Update_Renewal_Dates:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2268 okc_debug.Reset_Indentation;
2269 END IF;
2270
2271 IF l_rencon_n_cur%ISOPEN then
2272 close l_rencon_n_cur;
2273 END IF;
2274 IF l_rencon_y_cur%ISOPEN then
2275 close l_rencon_y_cur;
2276 END IF;
2277 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2278 (l_api_name,
2279 G_PKG_NAME,
2280 'OKC_API.G_RET_STS_UNEXP_ERROR',
2281 x_msg_count,
2282 x_msg_data,
2283 '_PROCESS');
2284
2285 WHEN OTHERS THEN
2286
2287 IF (l_debug = 'Y') THEN
2288 okc_debug.log('7800: Exiting Update_Renewal_Dates:OTHERS Exception', 2);
2289 okc_debug.Reset_Indentation;
2290 END IF;
2291
2292 IF l_rencon_n_cur%ISOPEN then
2293 close l_rencon_n_cur;
2294 END IF;
2295 IF l_rencon_y_cur%ISOPEN then
2296 close l_rencon_y_cur;
2297 END IF;
2298 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2299 (l_api_name,
2300 G_PKG_NAME,
2301 'OTHERS',
2302 x_msg_count,
2303 x_msg_data,
2304 '_PROCESS');
2305
2306 END Update_Renewal_Dates;
2307 */
2308
2309 -- this procedure sets the notification for post_renew
2310 PROCEDURE set_notify(
2311 x_return_status OUT NOCOPY VARCHAR2 ,
2312 p_old_k IN VARCHAR2 ,
2313 p_old_mod IN VARCHAR2 ,
2314 p_new_k IN VARCHAR2 ,
2315 p_new_mod IN VARCHAR2 ,
2316 p_qa_stat IN VARCHAR2 ,
2317 p_wf_found IN VARCHAR2 ,
2318 p_subj_msg IN VARCHAR2 ,
2319 p_ren_type IN VARCHAR2 ) IS
2320 l_oldk Varchar2(255);
2321 l_newk Varchar2(255);
2322 l_api_name Varchar2(30) := 'set_notify';
2323 BEGIN
2324
2325 IF (l_debug = 'Y') THEN
2326 okc_debug.Set_Indentation('OKC_RENEW_PVT');
2327 okc_debug.log('7900: Entered set_notify', 2);
2328 END IF;
2329
2330 x_return_status :=OKC_API.G_RET_STS_SUCCESS;
2331
2332 If (p_old_mod Is Null) Or (p_old_mod = OKC_API.G_MISS_CHAR) Then
2333 l_oldk := p_old_k;
2334 Else
2335 l_oldk := p_old_k || '-' || p_old_mod;
2336 End If;
2337 If (p_new_mod Is Null) Or (p_new_mod = OKC_API.G_MISS_CHAR) Then
2338 l_newk := p_new_k;
2339 Else
2340 l_newk := p_new_k || '-' || p_new_mod;
2341 End If;
2342
2343 If p_subj_msg <> OKC_API.G_MISS_CHAR then
2344 OKC_API.set_message(p_app_name => g_app_name,
2345 p_msg_name => p_subj_msg);
2346 ELSE
2347 OKC_API.set_message(p_app_name => g_app_name,
2348 p_msg_name => 'OKC_REN_SUBJECT',
2349 p_token1 => 'NUMBER',
2350 p_token1_value => l_oldk);
2351 END IF;
2352 If p_ren_type = 'NSR' then
2353 OKC_API.set_message(p_app_name => g_app_name,
2354 p_msg_name => 'OKC_REN_NSR',
2355 p_token1 => 'OLDK',
2356 p_token1_value => l_oldk,
2357 p_token2 => 'NEWK',
2358 p_token2_value => l_newk);
2359 ELSE
2360 IF p_qa_stat = 'S' then
2361 OKC_API.set_message(p_app_name => g_app_name,
2362 p_msg_name => 'OKC_QA_FAILED');
2363 IF p_ren_type='SFA' then
2364 OKC_API.set_message(p_app_name => g_app_name,
2365 p_msg_name => 'OKC_REN_NO_SFA',
2366 p_token1 => 'OLDK',
2367 p_token1_value => l_oldk,
2368 p_token2 => 'NEWK',
2369 p_token2_value => l_newk);
2370 ELSIF p_ren_type='EVN' then
2371 OKC_API.set_message(p_app_name => g_app_name,
2372 p_msg_name => 'OKC_REN_NO_EVN',
2373 p_token1 => 'OLDK',
2374 p_token1_value => l_oldk,
2375 p_token2 => 'NEWK',
2376 p_token2_value => l_newk);
2377 END IF;
2378 ELSE
2379 IF p_qa_stat = 'W' then
2380 OKC_API.set_message(p_app_name => g_app_name,
2381 p_msg_name => 'OKC_QA_WARNINGS');
2382 END IF;
2383 IF p_ren_type='SFA' then
2384 If p_wf_found='F' then
2385 OKC_API.set_message(p_app_name => g_app_name,
2386 p_msg_name => 'OKC_REN_NO_WF',
2387 p_token1 => 'OLDK',
2388 p_token1_value => l_oldk,
2389 p_token2 => 'NEWK',
2390 p_token2_value => l_newk);
2391
2392 ELSE
2393 OKC_API.set_message(p_app_name => g_app_name,
2394 p_msg_name => 'OKC_REN_SFA',
2395 p_token1 => 'OLDK',
2396 p_token1_value => l_oldk,
2397 p_token2 => 'NEWK',
2398 p_token2_value => l_newk);
2399 END IF;
2400 ELSIF p_ren_type='EVN' then
2401 OKC_API.set_message(p_app_name => g_app_name,
2402 p_msg_name => 'OKC_REN_EVN',
2403 p_token1 => 'OLDK',
2404 p_token1_value => l_oldk,
2405 p_token2 => 'NEWK',
2406 p_token2_value => l_newk);
2407 END IF;
2408
2409 END IF;
2410
2411 END IF;
2412
2413 IF (l_debug = 'Y') THEN
2414 okc_debug.log('8000: Leaving Set_Notify', 2);
2415 okc_debug.Reset_Indentation;
2416 END IF;
2417 END Set_Notify;
2418
2419 --------------------------------------------------------------------------------------------
2420 --called from renew procedure in public, this procedure sends the notifiactions
2421 -- and a few other things depending on the renew rule defined on the contract
2422 -- This procedure should be called after creating a renewed contract to decide what
2423 --should be the final state of the renewed contract
2424 --------------------------------------------------------------------------------------------
2425 PROCEDURE post_renewed_contract(
2426 p_api_version IN NUMBER,
2427 p_init_msg_list IN VARCHAR2 ,
2428 x_return_status OUT NOCOPY VARCHAR2,
2429 x_msg_count OUT NOCOPY NUMBER,
2430 x_msg_data OUT NOCOPY VARCHAR2,
2431 p_renew_chr_id IN number,
2432 p_renew_in_parameters_rec IN Renew_in_parameters_rec,
2433 p_ren_type IN VARCHAR2 ,
2434 p_contact IN VARCHAR2 ) is
2435
2436 CURSOR cur_rules(p_chr_id number) is
2437 SELECT nvl(rul.rule_information1,OKC_API.G_MISS_CHAR) renew_type,
2438 nvl(rul.rule_information2,OKC_API.G_MISS_CHAR) contact
2439 FROM okc_rules_b rul,okc_rule_groups_b rgp
2440 WHERE rgp.dnz_chr_id = p_chr_id
2441 and rgp.id=rul.rgp_id
2442 --and rgp.rgd_code='RENEW'
2443 and rul.rule_information_category='REN' ;
2444
2445 --/Rules migration/ replaced by cursor below
2446 /*
2447 CURSOR cur_qa(p_chr_id number) is select nvl(qcl_id,OKC_API.G_MISS_NUM)
2448 from OKC_K_HEADERS_b
2449 where id=p_chr_id;
2450 */
2451
2452
2453 CURSOR cur_header(p_chr_id number) is
2454 select nvl(qcl_id,OKC_API.G_MISS_NUM) qcl_id,
2455 nvl(renewal_type_code,OKC_API.G_MISS_CHAR) renewal_type_code,renewal_notify_to,
2456 application_id
2457 from OKC_K_HEADERS_b
2458 where id=p_chr_id;
2459
2460 CURSOR cur_wf(p_chr_id number)
2461 is select pdf_id from okc_k_processes kp, okc_process_defs_b pd where
2462 kp.chr_id=p_chr_id and kp.pdf_id=pd.id and pd.usage='APPROVE';
2463
2464 CURSOR cur_user(p_user_id number)
2465 is select fnd.user_name from okx_resources_v res, fnd_user fnd where
2466 fnd.user_id=res.user_id and res.id1=p_user_id;
2467
2468 CURSOR cur_header_aa IS
2469 SELECT k.estimated_amount,k.scs_code,scs.cls_code,k.sts_code
2470 FROM OKC_K_HEADERS_B K,
2471 OKC_SUBCLASSES_B SCS
2472 WHERE k.id = p_renew_in_parameters_rec.p_contract_id
2473 AND k.scs_code = scs.code;
2474
2475 CURSOR cur_startend(p_chr_id number) is
2476 select start_date,end_date from okc_k_headers_b where
2477 id=p_chr_id;
2478
2479 l_scs_code okc_subclasses_v.code%type;
2480 l_cls_code okc_subclasses_v.cls_code%type;
2481 l_k_status_code okc_k_headers_v.sts_code%type;
2482 l_end_date date;
2483 l_start_date date;
2484 l_estimated_amount number;
2485 --l_wf_msg_tbl okc_async_pvt.par_tbl_typ;
2486 l_pdf_id number :=OKC_API.G_MISS_NUM;
2487 rule_rec cur_rules%rowtype;
2488 l_ren_type okc_rules_v.rule_information1%type:=OKC_API.G_MISS_CHAR;
2489 l_contact okc_rules_v.rule_information2%type:=OKC_API.G_MISS_CHAR;
2490 l_qcl_id number;
2491 l_msg_tbl OKC_QA_CHECK_PUB.MSG_TBL_TYPE;
2492 i BINARY_INTEGER;
2493 l_max_severity varchar2(1):='I';
2494 l_wf_found varchar2(1):='T';
2495 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
2496 l_api_name constant VARCHAR2(30) := 'post_renewed_contract';
2497 l_lead_id Number;
2498 cur_header_rec cur_header%rowtype;
2499
2500 --FUNCTION notify(p_msg_tbl okc_async_pvt.par_tbl_typ,p_user varchar2) RETURN varchar2 IS
2501 FUNCTION notify(p_user varchar2) RETURN varchar2 IS
2502 l_user_name fnd_user.user_name%type :=OKC_API.G_MISS_CHAR;
2503 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
2504 l_exception_stop exception;
2505 l_proc varchar2(4000);
2506 l_api_name Varchar2(30) := 'notify';
2507 BEGIN
2508
2509 IF (l_debug = 'Y') THEN
2510 okc_debug.Set_Indentation('OKC_RENEW_PVT');
2511 okc_debug.log('8100: Entered notify', 2);
2512 END IF;
2513
2514 IF p_ren_type = OKC_API.G_MISS_CHAR then
2515 open cur_user(to_number(p_user));
2516 FETCH cur_user into l_user_name;
2517 close cur_user;
2518 ELSE
2519 l_user_name := p_user;
2520 END IF;
2521
2522 IF l_user_name <> OKC_API.G_MISS_CHAR AND l_user_name is not null then
2523
2524 l_proc:='begin OKC_RENEW_PVT.SET_NOTIFY(x_return_status=>:1,p_old_k=>'''||
2525 p_renew_in_parameters_rec.p_contract_number ||'''';
2526 IF p_renew_in_parameters_rec.p_contract_modifier <> OKC_API.G_MISS_CHAR then
2527 l_proc:= l_proc||',p_old_mod=>'''||p_renew_in_parameters_rec.p_contract_modifier ||'''';
2528 END IF;
2529
2530 l_proc:= l_proc||',p_new_k=>'''||p_renew_in_parameters_rec.p_new_contract_number ||'''';
2531
2532 IF p_renew_in_parameters_rec.p_new_contract_modifier <> OKC_API.G_MISS_CHAR then
2533 l_proc:= l_proc||',p_new_mod=>'''||p_renew_in_parameters_rec.p_new_contract_modifier ||'''';
2534 END IF;
2535
2536 l_proc:= l_proc||',p_qa_stat=>'''||l_max_severity||
2537 ''',p_wf_found=>'''||l_wf_found||
2538 ''',p_ren_type=>'''||l_ren_type||''' ); end;';
2539
2540
2541 IF (l_debug = 'Y') THEN
2542 okc_debug.log('8200: Before proc_msg_call');
2543 END IF;
2544 OKC_ASYNC_PUB.proc_msg_call(
2545 p_api_version => 1,
2546 x_return_status => l_return_status,
2547 x_msg_count => x_msg_count,
2548 x_msg_data => x_msg_data,
2549 p_proc => l_proc,
2550 p_s_recipient => l_user_name,
2551 p_e_recipient => l_user_name,
2552 p_contract_id => p_renew_chr_id
2553 );
2554 IF (l_debug = 'Y') THEN
2555 okc_debug.log('8300: After proc_msg_call');
2556 END IF;
2557
2558
2559 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2560 RAISE l_exception_stop;
2561 END IF;
2562
2563 END IF;
2564
2565 IF (l_debug = 'Y') THEN
2566 okc_debug.log('8400: Leaving notify', 2);
2567 okc_debug.Reset_Indentation;
2568 END IF;
2569
2570 return (l_return_status);
2571
2572 EXCEPTION
2573 WHEN l_exception_stop then
2574
2575 IF (l_debug = 'Y') THEN
2576 okc_debug.log('8500: Exiting notify:l_exception_stop Exception', 2);
2577 okc_debug.Reset_Indentation;
2578 END IF;
2579
2580 return l_return_status;
2581 when others then
2582
2583 IF (l_debug = 'Y') THEN
2584 okc_debug.log('8600: Exiting notify:others Exception', 2);
2585 okc_debug.Reset_Indentation;
2586 END IF;
2587
2588 OKC_API.set_message(p_app_name => g_app_name,
2589 p_msg_name => g_unexpected_error,
2590 p_token1 => g_sqlcode_token,
2591 p_token1_value => sqlcode,
2592 p_token2 => g_sqlerrm_token,
2593 p_token2_value => sqlerrm);
2594 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2595
2596 return (l_return_status);
2597 END notify;
2598
2599 BEGIN
2600
2601 IF (l_debug = 'Y') THEN
2602 okc_debug.Set_Indentation('OKC_RENEW_PVT');
2603 okc_debug.log('8700: Entered post_renewed_contract', 2);
2604 END IF;
2605
2606 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2607
2608 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2609 p_init_msg_list,
2610 '_PROCESS',
2611 x_return_status);
2612
2613 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2614 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2615 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2616 raise OKC_API.G_EXCEPTION_ERROR;
2617 END IF;
2618
2619 --/Rules migration/ added
2620 open cur_header(p_renew_chr_id);
2621 fetch cur_header into cur_header_rec;
2622 close cur_header;
2623
2624 IF p_ren_type = OKC_API.G_MISS_CHAR then
2625 If cur_header_rec.application_id in (510,871) Then
2626 open cur_rules(p_renew_chr_id);
2627 fetch cur_rules into rule_rec;
2628 close cur_rules;
2629 l_ren_type:=rule_rec.renew_type;
2630 l_contact:=rule_rec.contact;
2631 Else
2632 l_ren_type := cur_header_rec.renewal_type_code;
2633 If cur_header_rec.renewal_notify_to is null Then
2634 l_contact := OKC_API.G_MISS_CHAR;
2635 Else
2636 l_contact :=cur_header_rec.renewal_notify_to;
2637 End If;
2638 End If;
2639
2640 ELSE
2641 l_ren_type:=p_ren_type;
2642 l_contact :=p_contact;
2643
2644 END IF;
2645 IF (l_debug = 'Y') THEN
2646 okc_debug.log('8800: l_ren_type ' || l_ren_type);
2647 END IF;
2648
2649 IF l_ren_type in ('SFA','EVN','NSR') then
2650 If l_contact = OKC_API.G_MISS_CHAR then
2651 OKC_API.set_message(p_app_name => g_app_name,
2652 p_msg_name => 'OKC_NO_RENEW_CONTACT');
2653 RAISE OKC_API.G_EXCEPTION_ERROR;
2654 END IF;
2655 IF l_ren_type in ('SFA','EVN') then
2656 --do qa check
2657 --/Rules migration/ Use cur_header_rec
2658
2659 /* Open cur_qa(p_renewed_chr_id);
2660 Fetch cur_qa into l_qcl_id;
2661 Close cur_qa;
2662 */
2663 l_qcl_id := cur_header_rec.qcl_id;
2664 --
2665 If l_qcl_id <> OKC_API.G_MISS_NUM then
2666 IF (l_debug = 'Y') THEN
2667 okc_debug.log('8900: Before QA Check');
2668 END IF;
2669 OKC_QA_CHECK_PUB.execute_qa_check_list(
2670 p_api_version =>p_api_version
2671 ,p_init_msg_list=>okc_api.g_false
2672 ,x_return_status=>x_return_status
2673 ,x_msg_count=>x_msg_count
2674 ,x_msg_data=>x_msg_data
2675 ,p_qcl_id=>l_qcl_id
2676 ,p_chr_id=>p_renew_chr_id
2677 ,x_msg_tbl=>l_msg_tbl
2678 );
2679 IF (l_debug = 'Y') THEN
2680 okc_debug.log('9000: After QA Check');
2681 END IF;
2682 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
2683 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2684 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
2685 RAISE OKC_API.G_EXCEPTION_ERROR;
2686 END IF;
2687 l_max_severity:='I';
2688 If l_msg_tbl.count >0 then
2689 i:=l_msg_tbl.first;
2690 LOOP
2691 --dbms_output.put_line('san dbms in loop severity level'||i||' '||l_msg_tbl(i).severity);
2692 --dbms_output.put_line('san dbms in loop severity error status'||i||' '||l_msg_tbl(i).error_status);
2693 --dbms_output.put_line('san dbms in loop severity name '||i||' '||l_msg_tbl(i).name);
2694 --dbms_output.put_line('san dbms in loop severity package_name'||i||' '||l_msg_tbl(i).package_name);
2695 --dbms_output.put_line('san dbms in loop severity procedure_name'||i||' '||l_msg_tbl(i).procedure_name);
2696 --dbms_output.put_line('san dbms in loop severity descriptio'||i||' '||l_msg_tbl(i).description);
2697 If l_msg_tbl(i).severity='W' and l_msg_tbl(i).error_status='E' then
2698 l_max_severity:='W';
2699 --san comment later on make the message with warnings
2700 -- l_wf_msg_tbl(2).par_type := 'C';
2701 -- l_wf_msg_tbl(2).par_name := 'MESSAGE1';
2702 -- l_wf_msg_tbl(2).par_value := 'QA returned with warnings';
2703 ELSIF l_msg_tbl(i).severity='S' and l_msg_tbl(i).error_status='E' then
2704 l_max_severity:='S';
2705 --san comment later on make the message with stop error
2706 -- l_wf_msg_tbl(2).par_type := 'C';
2707 -- l_wf_msg_tbl(2).par_name := 'MESSAGE1';
2708 -- l_wf_msg_tbl(2).par_value := 'QA returned with errors. Post renewal stopped';
2709 exit;
2710 END IF;
2711 exit when i=l_msg_tbl.last;
2712 i:=l_msg_tbl.next(i);
2713 END LOOP;
2714 END IF;--table count
2715
2716 END IF; -- if qcl_id found
2717 -- start appropriate workflows
2718
2719 IF l_max_severity <> 'S' then
2720 IF l_ren_type = 'SFA' then
2721
2722 --san comment start contract approval workflow
2723 open cur_wf(p_renew_chr_id);
2724 Fetch cur_wf into l_pdf_id;
2725 close cur_wf;
2726 IF l_pdf_id <> OKC_API.G_MISS_NUM then
2727 IF (l_debug = 'Y') THEN
2728 okc_debug.log('9100: Before k_approval_start');
2729 END IF;
2730 OKC_CONTRACT_APPROVAL_PUB.k_approval_start(
2731 p_api_version=>p_api_version,
2732 p_init_msg_list=>okc_api.g_false,
2733 x_return_status=>x_return_status,
2734 x_msg_count=>x_msg_count,
2735 x_msg_data=>x_msg_data,
2736 p_contract_id=>p_renew_chr_id,
2737 p_process_id=>l_pdf_id,
2738 p_do_commit=>okc_api.g_false,
2739 p_access_level=>'Y'
2740 );
2741 IF (l_debug = 'Y') THEN
2742 okc_debug.log('9200: After k_approval_start');
2743 END IF;
2744 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
2745 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2746 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
2747 RAISE OKC_API.G_EXCEPTION_ERROR;
2748 END IF;
2749
2750 -- l_wf_msg_tbl(2).par_type := 'C';
2751 -- l_wf_msg_tbl(2).par_name := 'MESSAGE1';
2752 -- l_wf_msg_tbl(2).par_value := 'Contract renewed and sent for approval';
2753 ELSE
2754 l_wf_found:='F';
2755 -- l_wf_msg_tbl(2).par_type := 'C';
2756 -- l_wf_msg_tbl(2).par_name := 'MESSAGE1';
2757 -- l_wf_msg_tbl(2).par_value := 'Contract renewed but no workflow to activate';
2758 END IF;
2759 ELSE
2760 -- call alex api to update status and resolve time values
2761
2762 -- update the date approved of the contract
2763 IF (l_debug = 'Y') THEN
2764 okc_debug.log('9300: Before k_approved');
2765 END IF;
2766 OKC_CONTRACT_APPROVAL_PUB.k_approved(
2767 p_contract_id=>p_renew_chr_id,
2768 x_return_status=>x_return_status
2769 );
2770 IF (l_debug = 'Y') THEN
2771 okc_debug.log('9400: After k_approved');
2772 END IF;
2773 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
2774 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2775 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
2776 RAISE OKC_API.G_EXCEPTION_ERROR;
2777 END IF;
2778 -- sign the contract
2779 IF (l_debug = 'Y') THEN
2780 okc_debug.log('9500: Before k_signed');
2781 END IF;
2782 OKC_CONTRACT_APPROVAL_PUB.k_signed(
2783 p_contract_id=>p_renew_chr_id,
2784 x_return_status=>x_return_status
2785 );
2786 IF (l_debug = 'Y') THEN
2787 okc_debug.log('9600: After k_signed');
2788 END IF;
2789 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
2790 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2791 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
2792 RAISE OKC_API.G_EXCEPTION_ERROR;
2793 END IF;
2794
2795 END IF;
2796
2797 ELSE
2798 null;
2799
2800 END IF;--severity check
2801 ELSE
2802 null;
2803
2804 END IF;-- if ren type sfa or evn
2805 -- start notify workflow
2806
2807 IF (l_debug = 'Y') THEN
2808 okc_debug.log('9700: Before notify');
2809 END IF;
2810 --x_return_status := notify(l_wf_msg_tbl,l_contact);
2811 x_return_status := notify(l_contact);
2812 IF (l_debug = 'Y') THEN
2813 okc_debug.log('9800: After notify');
2814 END IF;
2815 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
2816 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2817 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
2818 RAISE OKC_API.G_EXCEPTION_ERROR;
2819 END IF;
2820 END IF;-- if ren type sfa,evn or nsr
2821 --call action assembler here.
2822
2823 IF Nvl(l_ren_type, '*') <> 'DNR' then
2824 open cur_header_aa;
2825 fetch cur_header_aa into l_estimated_amount,l_scs_code,l_cls_code,l_k_status_code;
2826 close cur_header_aa;
2827
2828 l_end_date:=p_renew_in_parameters_rec.p_end_date;
2829 l_start_date:=p_renew_in_parameters_rec.p_start_date;
2830
2831 IF l_end_date is null or l_start_date is null then
2832 open cur_startend(p_renew_chr_id);
2833 fetch cur_startend into l_start_date,l_end_date;
2834 close cur_startend;
2835 END IF;
2836
2837 IF (l_debug = 'Y') THEN
2838 okc_debug.log('9900: Before acn_assemble');
2839 END IF;
2840 OKC_K_RENEW_ASMBLR_PVT.acn_assemble(
2841 p_api_version => 1,
2842 p_init_msg_list => OKC_API.G_FALSE,
2843 x_return_status => l_return_status,
2844 x_msg_count => x_msg_count,
2845 x_msg_data => x_msg_data,
2846 p_k_nbr_mod => p_renew_in_parameters_rec.p_contract_modifier,
2847 p_new_k_END_date => l_END_date,
2848 p_k_number => p_renew_in_parameters_rec.p_new_contract_number,
2849 p_new_k_id => p_renew_chr_id,
2850 p_new_k_start_date => l_start_date,
2851 p_original_k_END_date => p_renew_in_parameters_rec.p_orig_END_date,
2852 p_original_kid => p_renew_in_parameters_rec.p_contract_id,
2853 p_original_k_start_date => p_renew_in_parameters_rec.p_orig_start_date,
2854 p_k_class => l_cls_code,
2855 p_k_subclass => l_scs_code,
2856 p_k_status_code => l_k_status_code,
2857 p_estimated_amount => l_estimated_amount);
2858 IF (l_debug = 'Y') THEN
2859 okc_debug.log('10000: After acn_assemble');
2860 END IF;
2861
2862 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
2863 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2864 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
2865 RAISE OKC_API.G_EXCEPTION_ERROR;
2866 END IF;
2867
2868 -- Opportunity Integration starts from here. See bug 1865416 for more details.
2869 -- Create opportunity for this renewed contract only if it is allowed.
2870 IF (l_debug = 'Y') THEN
2871 okc_debug.log('11000: Before is_opp_creation_allowed');
2872 END IF;
2873 okc_opportunity_pub.is_opp_creation_allowed(
2874 p_context => 'RENEW',
2875 p_contract_id => p_renew_chr_id,
2876 x_return_status => l_return_status);
2877 IF (l_debug = 'Y') THEN
2878 okc_debug.log('10100: After is_opp_creation_allowed');
2879 okc_debug.log('10200: Return Status of opp_creation_allowed - ' || l_return_status);
2880 END IF;
2881 -- In case of unexpected error, raise the exception, however if it is
2882 -- a normal error, it means opportunity cannot be created and success
2883 -- should be returned from this procedure.
2884 If l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2885 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2886 /* Elsif l_return_status = OKC_API.G_RET_STS_ERROR Then
2887 Raise OKC_API.G_EXCEPTION_ERROR; */
2888 End If;
2889
2890 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2891 IF (l_debug = 'Y') THEN
2892 okc_debug.log('10300: Before Create_Opportunity');
2893 END IF;
2894
2895 okc_opportunity_pub.create_opportunity(
2896 p_api_version => 1,
2897 p_context => 'RENEW',
2898 p_contract_id => p_renew_chr_id,
2899 p_win_probability => Null,
2900 p_expected_close_days => Null,
2901 x_lead_id => l_lead_id,
2902 p_init_msg_list => OKC_API.G_FALSE,
2903 x_msg_data => x_msg_data,
2904 x_msg_count => x_msg_count,
2905 x_return_status => l_return_status);
2906
2907 IF (l_debug = 'Y') THEN
2908 okc_debug.log('10400: After Create_Opportunity');
2909 okc_debug.log('10500: Return Status of Create_Opportunity - ' || l_return_status);
2910 okc_debug.log('10600: Lead ID - ' || To_Char(l_lead_id));
2911 END IF;
2912
2913 If l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2914 Raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2915 /* Elsif l_return_status = OKC_API.G_RET_STS_ERROR Then
2916 Raise OKC_API.G_EXCEPTION_ERROR; */
2917 End If;
2918 End If;
2919
2920 END IF; -- ren_type not 'DNR'
2921 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2922
2923 IF (l_debug = 'Y') THEN
2924 okc_debug.log('10700: Leaving post_renewed_contract', 2);
2925 okc_debug.Reset_Indentation;
2926 END IF;
2927
2928 EXCEPTION
2929 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2930
2931 IF (l_debug = 'Y') THEN
2932 okc_debug.log('10800: Exiting post_renewed_contract:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2933 okc_debug.Reset_Indentation;
2934 END IF;
2935
2936 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2937 (l_api_name,
2938 G_PKG_NAME,
2939 'OKC_API.G_RET_STS_ERROR',
2940 x_msg_count,
2941 x_msg_data,
2942 '_PROCESS');
2943
2944 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2945
2946 IF (l_debug = 'Y') THEN
2947 okc_debug.log('10900: Exiting post_renewed_contract:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2948 okc_debug.Reset_Indentation;
2949 END IF;
2950
2951 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2952 (l_api_name,
2953 G_PKG_NAME,
2954 'OKC_API.G_RET_STS_UNEXP_ERROR',
2955 x_msg_count,
2956 x_msg_data,
2957 '_PROCESS');
2958
2959 WHEN OTHERS THEN
2960
2961 IF (l_debug = 'Y') THEN
2962 okc_debug.log('11000: Exiting post_renewed_contract:OTHERS Exception', 2);
2963 okc_debug.Reset_Indentation;
2964 END IF;
2965
2966 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2967 (l_api_name,
2968 G_PKG_NAME,
2969 'OTHERS',
2970 x_msg_count,
2971 x_msg_data,
2972 '_PROCESS');
2973
2974
2975 END post_renewed_contract;
2976
2977
2978 --------------------------------------------------------------------------------------------
2979 --called from renew procedure in public, this procedure actually creates a copy of
2980 --of the contract to be renewed and adjust the dates on the various components of this
2981 --copy
2982 --------------------------------------------------------------------------------------------
2983 PROCEDURE Create_Renewed_Contract
2984 (p_api_version IN NUMBER,
2985 p_init_msg_list IN VARCHAR2 ,
2986 x_return_status OUT NOCOPY VARCHAR2,
2987 x_msg_count OUT NOCOPY NUMBER,
2988 x_msg_data OUT NOCOPY VARCHAR2,
2989 x_contract_id OUT NOCOPY NUMBER,
2990 p_renew_in_parameters_rec IN Renew_in_parameters_rec,
2991 x_renew_in_parameters_rec OUT NOCOPY Renew_in_parameters_rec,
2992 p_ren_type IN varchar2 ,
2993 p_renewal_called_from_ui IN VARCHAR2
2994 ) is
2995
2996 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
2997 l_renew_in_parameters_rec renew_in_parameters_rec := p_renew_in_parameters_rec;
2998 l_ren_type okc_rules_v.rule_information1%type;
2999 l_ultimate_end_date Date;
3000
3001 l_api_name constant VARCHAR2(30) := 'CREATE_RENEWD_CONTRACT';
3002
3003
3004 -- Cursor modified for Bug 2292300
3005 CURSOR cur_rules(p_chr_id number) is
3006 SELECT nvl(rul.rule_information1, OKC_API.G_MISS_CHAR) renew_type,
3007 fnd_date.Canonical_To_Date(rul.rule_information3) ultimate_end_date
3008 --To_Date(rul.rule_information3, 'YYYY/MM/DD') ultimate_end_date
3009 FROM okc_rules_b rul,
3010 okc_rule_groups_b rgp
3011 WHERE rgp.dnz_chr_id = p_chr_id
3012 and rgp.id = rul.rgp_id
3013 -- and rgp.rgd_code = 'RENEW'
3014 and rul.rule_information_category = 'REN';
3015
3016 --commented /rules migration/
3017 --replaced by cur_header
3018 /*cursor cur_org is select authoring_org_id,inv_organization_id
3019 from okc_k_headers_b
3020 where id = p_renew_in_parameters_rec.p_contract_id;*/
3021
3022 -- /rules migration/
3023 Cursor cur_header is
3024 select authoring_org_id,inv_organization_id,application_id,
3025 nvl(renewal_type_code,OKC_API.G_MISS_CHAR) renewal_type_code,
3026 renewal_end_date
3027 from okc_k_headers_b
3028 where id = p_renew_in_parameters_rec.p_contract_id;
3029
3030 cur_header_rec cur_header%rowtype;
3031 --
3032 r_org OKC_K_HEADERS_B.authoring_org_id%type;
3033 r_inv_org OKC_K_HEADERS_B.inv_organization_id%type;
3034
3035 FUNCTION set_attributes(p_renew_in_rec IN OUT NOCOPY renew_in_parameters_rec) RETURN varchar2 is
3036
3037 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
3038 l_api_name Varchar2(30) := 'set_attributes';
3039 --
3040 Procedure Set_Evergreen_Date_Uom(p_return_status OUT NOCOPY Varchar2) Is
3041 l_return_status Varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
3042 Begin
3043
3044 IF (l_debug = 'Y') THEN
3045 okc_debug.Set_Indentation('OKC_RENEW_PVT');
3046 okc_debug.log('11100: Entered Set_Evergreen_Date_Uom', 2);
3047 END IF;
3048
3049 p_return_status := l_return_status;
3050 --
3051 -- In case of evergreen contract, the contract end date must be less than
3052 -- the renewal rule ultimate end date. If it not so, set the end date to
3053 -- ultimate end date.
3054 If l_ren_type = 'EVN' Then
3055 If l_ultimate_end_date Is Not Null Then
3056 --
3057 -- Make sure that contract has not already reached its ultimate
3058 -- end date
3059 If l_ultimate_end_date <= p_renew_in_rec.p_start_date Then
3060 OKC_API.set_message(p_app_name => g_app_name,
3061 p_msg_name => 'OKC_ULTIMATE_END_REACHED');
3062 p_return_status := okc_api.g_ret_sts_error;
3063 Else
3064 If l_ultimate_end_date < p_renew_in_rec.p_end_date Then
3065 p_renew_in_rec.p_end_date := l_ultimate_end_date;
3066 --
3067 -- Recalculate the UOM Code and the duration based on this
3068 -- new date.
3069 okc_time_util_pub.get_duration(p_renew_in_rec.p_start_date,
3070 p_renew_in_rec.p_end_date,
3071 p_renew_in_rec.p_duration,
3072 p_renew_in_rec.p_uom_code,
3073 l_return_status);
3074 if l_return_status <> okc_api.g_ret_sts_success then
3075 OKC_API.set_message(p_app_name => g_app_name,
3076 p_msg_name => 'OKC_GET_DURATION_ERROR');
3077 p_return_status := l_return_status;
3078 End If;
3079 End If;
3080 End If;
3081 End If;
3082 End If;
3083
3084 IF (l_debug = 'Y') THEN
3085 okc_debug.log('11150: Leaving Set_Evergreen_Date_Uom', 2);
3086 okc_debug.Reset_Indentation;
3087 END IF;
3088 End Set_Evergreen_Date_Uom;
3089 BEGIN
3090
3091 IF (l_debug = 'Y') THEN
3092 okc_debug.Set_Indentation('OKC_RENEW_PVT');
3093 okc_debug.log('11200: Entered set_attributes', 2);
3094 END IF;
3095
3096 -- calculate new start date if not provided
3097 If p_renew_in_rec.p_start_date is null then
3098
3099 p_renew_in_rec.p_start_date := p_renew_in_rec.p_orig_end_date + 1;
3100 End if;
3101
3102 -- calculate new end date if not provided else calculate duration and uom code
3103 If p_renew_in_rec.p_perpetual_flag = OKC_API.G_FALSE OR
3104 p_renew_in_parameters_rec.p_perpetual_flag IS NULL Then
3105 If p_renew_in_rec.p_end_date is null then
3106 p_renew_in_rec.p_end_date := okc_time_util_pub.get_enddate
3107 (p_renew_in_rec.p_start_date,
3108 p_renew_in_rec.p_uom_code,
3109 p_renew_in_rec.p_duration);
3110 --
3111 -- Bug 1787600 - For Evergreen Contracts, if ultimate end date has
3112 -- been supplied, make sure this is less than the contract end date
3113 -- otherwise use the ultimate end date as the contract end date
3114 --
3115 Set_Evergreen_Date_Uom(l_return_status);
3116 If l_return_status <> okc_api.g_ret_sts_success then
3117 return (l_return_status);
3118 End If;
3119 Else
3120 Set_Evergreen_Date_Uom(l_return_status);
3121 If l_return_status <> okc_api.g_ret_sts_success then
3122 return (l_return_status);
3123 End If;
3124 If p_renew_in_rec.p_uom_code is null OR p_renew_in_rec.p_duration is null then
3125 okc_time_util_pub.get_duration(p_renew_in_rec.p_start_date,
3126 p_renew_in_rec.p_end_date,
3127 p_renew_in_rec.p_duration,
3128 p_renew_in_rec.p_uom_code,
3129 l_return_status);
3130
3131 if l_return_status <> okc_api.g_ret_sts_success then
3132 OKC_API.set_message(p_app_name => g_app_name,
3133 p_msg_name => 'OKC_GET_DURATION_ERROR');
3134 return (l_return_status);
3135 End If;
3136 End If;
3137 End If;
3138 Else -- if perpetual contract
3139 p_renew_in_rec.p_end_date := Null;
3140 p_renew_in_rec.p_duration := Null;
3141 p_renew_in_Rec.p_uom_code := Null;
3142 End If; -- end perpetual flag = OKC_API.G_FALSE
3143
3144 IF (l_debug = 'Y') THEN
3145 okc_debug.log('11300: Leaving set_attributes', 2);
3146 okc_debug.Reset_Indentation;
3147 END IF;
3148
3149 Return (l_return_status);
3150
3151
3152 EXCEPTION
3153 when others then
3154
3155 IF (l_debug = 'Y') THEN
3156 okc_debug.log('11400: Exiting set_attributes:others Exception', 2);
3157 okc_debug.Reset_Indentation;
3158 END IF;
3159
3160 OKC_API.set_message(p_app_name => g_app_name,
3161 p_msg_name => g_unexpected_error,
3162 p_token1 => g_sqlcode_token,
3163 p_token1_value => sqlcode,
3164 p_token2 => g_sqlerrm_token,
3165 p_token2_value => sqlerrm);
3166 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3167
3168 return (l_return_status);
3169 END Set_Attributes;
3170 BEGIN
3171
3172 IF (l_debug = 'Y') THEN
3173 okc_debug.Set_Indentation('OKC_RENEW_PVT');
3174 okc_debug.log('11500: Entered Create_Renewed_Contract', 2);
3175 END IF;
3176
3177 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3178
3179 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3180 p_init_msg_list,
3181 '_PROCESS',
3182 x_return_status);
3183
3184 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3185 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3186 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3187 raise OKC_API.G_EXCEPTION_ERROR;
3188 END IF;
3189
3190 -- get the renew type defined for the rule. If not passed in as parameter already
3191 -- get it from the rules table
3192 --/Rules migration/
3193 --For oko/okc contracts use rules, for other contracts use rules colunns
3194 open cur_header;
3195 fetch cur_header into cur_header_rec;
3196 close cur_header;
3197
3198 If cur_header_rec.application_id in (510,871) then
3199
3200 open cur_rules(p_renew_in_parameters_rec.p_contract_id);
3201 fetch cur_rules into l_ren_type, l_ultimate_end_date;
3202 close cur_rules;
3203
3204 Else
3205 l_ren_type := cur_header_rec.renewal_type_code;
3206 l_ultimate_end_date := cur_header_rec.renewal_end_date;
3207 End If;
3208 --
3209
3210 IF p_ren_type Is Not Null And
3211 p_ren_type <> OKC_API.G_MISS_CHAR then
3212 l_ren_type := p_ren_type;
3213 END IF;
3214 IF (l_debug = 'Y') THEN
3215 okc_debug.log('11600: l_ren_type - ' || l_ren_type);
3216 END IF;
3217
3218 --Renew is not allowed when renew_type is DNR
3219 If l_ren_type = 'DNR' then
3220 OKC_API.set_message(p_app_name => g_app_name,
3221 p_msg_name => 'OKC_RENEW_NOT_ALLOWED');
3222 RAISE OKC_API.G_EXCEPTION_ERROR;
3223 END IF;
3224
3225 -- validate the contract to be renewed
3226 IF (l_debug = 'Y') THEN
3227 okc_debug.log('11700: Before validate');
3228 END IF;
3229 validate(p_api_version => 1,
3230 p_init_msg_list => 'F',
3231 x_return_status =>l_return_status,
3232 x_msg_count => x_msg_count,
3233 x_msg_data => x_msg_data,
3234 p_renew_in_parameters_rec => p_renew_in_parameters_rec,
3235 p_renewal_called_from_ui => p_renewal_called_from_ui);
3236 IF (l_debug = 'Y') THEN
3237 okc_debug.log('11800: After validate');
3238 END IF;
3239
3240
3241 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3242 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3243 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3244 raise OKC_API.G_EXCEPTION_ERROR;
3245 END IF;
3246
3247 l_return_status := set_attributes(l_renew_in_parameters_rec);
3248
3249 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
3250 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3251 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
3252 RAISE OKC_API.G_EXCEPTION_ERROR;
3253 END IF;
3254
3255 --/Rules migration/ use cur_header_rec
3256 /*open cur_org;
3257 fetch cur_org into r_org,r_inv_org;
3258 close cur_org;*/
3259
3260 r_org := cur_header_rec.authoring_org_id;
3261 r_inv_org := cur_header_rec.inv_organization_id;
3262 ----
3263
3264 IF (l_debug = 'Y') THEN
3265 okc_debug.log('119900: Before copy_contract');
3266 END IF;
3267 okc_copy_contract_pub.copy_contract(
3268 p_api_version => 1,
3269 p_init_msg_list => 'F',
3270 x_return_status => l_return_status,
3271 x_msg_count => x_msg_count,
3272 x_msg_data => x_msg_data,
3273 p_commit => 'F',
3274 p_chr_id => l_renew_in_parameters_rec.p_contract_id,
3275 p_contract_number => l_renew_in_parameters_rec.p_new_contract_number,
3276 p_contract_number_modifier => l_renew_in_PARAMETERs_rec.p_new_contract_modifier,
3277 p_to_template_yn => 'N',
3278 p_renew_ref_yn => 'Y',
3279 --san comment dbms - take out the following 2 paramater
3280 p_override_org => 'Y',
3281 p_copy_lines_yn => 'Y',
3282 --san comment dbms - take out the above 2 paramater
3283 x_chr_id => x_contract_id);
3284
3285 IF (l_debug = 'Y') THEN
3286 okc_debug.log('12000: After copy_contract');
3287 END IF;
3288
3289 IF l_return_status = 'W' then
3290 x_return_status := 'W';
3291 END IF;
3292 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
3293 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3294 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
3295 RAISE OKC_API.G_EXCEPTION_ERROR;
3296 END IF;
3297
3298 --update the start and end dates for the header and lines in the renew copy
3299 IF (l_debug = 'Y') THEN
3300 okc_debug.log('12100: Before update_renewal_dates');
3301 END IF;
3302 update_renewal_dates(p_api_version => 1,
3303 p_init_msg_list => OKC_API.G_FALSE,
3304 x_return_status => l_return_status,
3305 x_msg_count => x_msg_count,
3306 x_msg_data => x_msg_data,
3307 p_chr_id => x_contract_id,
3308 p_parent_new_st_dt => l_renew_in_parameters_rec.p_start_date,
3309 p_parent_new_end_dt => l_renew_in_parameters_rec.p_end_date,
3310 p_parent_old_st_dt => l_renew_in_parameters_rec.p_orig_start_date);
3311
3312 IF (l_debug = 'Y') THEN
3313 okc_debug.log('12200: After update_renewal_dates');
3314 END IF;
3315
3316
3317 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
3318 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3319 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
3320 RAISE OKC_API.G_EXCEPTION_ERROR;
3321 END IF;
3322
3323
3324 --update date_active and date_inactive in the condition headers for the renew copy
3325
3326 IF (l_debug = 'Y') THEN
3327 okc_debug.log('123300: Before update_condition_headers');
3328 END IF;
3329 update_condition_headers (p_api_version => 1,
3330 p_init_msg_list => OKC_API.G_FALSE,
3331 x_return_status => l_return_status,
3332 x_msg_count => x_msg_count,
3333 x_msg_data => x_msg_data,
3334 p_chr_id => x_contract_id,
3335 p_new_start_date => l_renew_in_parameters_rec.p_start_date,
3336 p_new_end_date => l_renew_in_parameters_rec.p_end_date,
3337 p_orig_start_date => l_renew_in_parameters_rec.p_orig_start_date,
3338 p_orig_end_date => l_renew_in_parameters_rec.p_orig_end_date);
3339
3340 IF (l_debug = 'Y') THEN
3341 okc_debug.log('12400: After update_condition_headers');
3342 END IF;
3343
3344 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
3345 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3346 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
3347 RAISE OKC_API.G_EXCEPTION_ERROR;
3348 END IF;
3349
3350 --update the old contract - update date_renewed and links to renew copy
3351
3352 IF (l_debug = 'Y') THEN
3353 okc_debug.log('12500: Before update_old_contract');
3354 END IF;
3355 update_old_contract(p_api_version => 1,
3356 p_init_msg_list => OKC_API.G_FALSE,
3357 x_return_status => l_return_status,
3358 x_msg_count => x_msg_count,
3359 x_msg_data => x_msg_data,
3360 p_new_header => x_contract_id ,
3361 p_chr_id => l_renew_in_parameters_rec.p_contract_id );
3362
3363 IF (l_debug = 'Y') THEN
3364 okc_debug.log('12600: After update_old_contract');
3365 END IF;
3366
3367 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
3368 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3369 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
3370 RAISE OKC_API.G_EXCEPTION_ERROR;
3371 END IF;
3372
3373
3374 -- If this contract is being renewed from Quote, then update
3375 -- the contrcat with Quote information( like price, Ship-to etc.)
3376 -- The p_context is not null then this contract is being renewed
3377 -- by quote, so call the integration API that will update K with
3378 -- quote information.
3379
3380 IF ( p_renew_in_parameters_rec.p_context IS NOT NULL
3381 AND
3382 p_renew_in_parameters_rec.p_context <> OKC_API.G_MISS_CHAR ) THEN
3383 IF (l_debug = 'Y') THEN
3384 okc_debug.log('12700: Before create_k_from_q');
3385 END IF;
3386 okc_oc_int_qtk_pvt.create_k_from_q(x_return_status => l_return_status
3387 ,p_context => p_renew_in_parameters_rec.p_context
3388 ,p_chr_id => x_contract_id);
3389
3390
3391 IF (l_debug = 'Y') THEN
3392 okc_debug.log('12800: After create_k_from_q');
3393 END IF;
3394 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
3395 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3396 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
3397 RAISE OKC_API.G_EXCEPTION_ERROR;
3398 END IF;
3399
3400 END IF;
3401 x_renew_in_parameters_rec:=l_renew_in_parameters_rec;
3402 g_cached_chr_id := x_contract_id;
3403 --san comment remove later from here
3404
3405 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3406 --dbms_output.put_line('renew(-)');
3407
3408 IF (l_debug = 'Y') THEN
3409 okc_debug.log('12900: Leaving Create_Renewed_Contract', 2);
3410 okc_debug.Reset_Indentation;
3411 END IF;
3412
3413 EXCEPTION
3414
3415 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3416
3417 IF (l_debug = 'Y') THEN
3418 okc_debug.log('13000: Exiting Create_Renewed_Contract:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3419 okc_debug.Reset_Indentation;
3420 END IF;
3421
3422 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3423 (l_api_name,
3424 G_PKG_NAME,
3425 'OKC_API.G_RET_STS_ERROR',
3426 x_msg_count,
3427 x_msg_data,
3428 '_PROCESS');
3429
3430 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3431
3432 IF (l_debug = 'Y') THEN
3433 okc_debug.log('13100: Exiting Create_Renewed_Contract:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3434 okc_debug.Reset_Indentation;
3435 END IF;
3436
3437 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3438 (l_api_name,
3439 G_PKG_NAME,
3440 'OKC_API.G_RET_STS_UNEXP_ERROR',
3441 x_msg_count,
3442 x_msg_data,
3443 '_PROCESS');
3444
3445 WHEN OTHERS THEN
3446
3447 IF (l_debug = 'Y') THEN
3448 okc_debug.log('13200: Exiting Create_Renewed_Contract:OTHERS Exception', 2);
3449 okc_debug.Reset_Indentation;
3450 END IF;
3451
3452 OKC_API.set_message(p_app_name => g_app_name,
3453 p_msg_name => g_unexpected_error,
3454 p_token1 => g_sqlcode_token,
3455 p_token1_value => sqlcode,
3456 p_token2 => g_sqlerrm_token,
3457 p_token2_value => sqlerrm);
3458 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3459
3460 END Create_Renewed_Contract;
3461
3462 ------------------------------------------------------------------------------
3463 -- This procedure updates Date_Renewed field in headers and lines table
3464 -- The Date_Renewed field for a line is updated when the Date_Renewed field
3465 -- of all its sublines is not null and is updated by the maximum Date_Renewed
3466 -- of all its sublines. True for all the levels. The contract header
3467 -- Date_Renewed is updated when all it's top lines Date_Renewed is not null
3468 -- and is updated by by the maximum Date_Renewed of all the top lines
3469 ------------------------------------------------------------------------------
3470
3471 Procedure Update_Parents_Date_Renewed( p_api_version IN NUMBER,
3472 p_init_msg_list IN VARCHAR2 ,
3473 x_return_status OUT NOCOPY VARCHAR2,
3474 x_msg_count OUT NOCOPY NUMBER,
3475 x_msg_data OUT NOCOPY VARCHAR2,
3476 p_chr_id IN NUMBER
3477 ) is
3478 l_top_count NUMBER;
3479 l_invalid_chr_id NUMBER;
3480 l_cle_id_ascendant NUMBER;
3481 l_level NUMBER;
3482 l_line_count NUMBER;
3483 l_date_count NUMBER;
3484 l_top_date_count NUMBER;
3485 l_no_lines_count NUMBER;
3486 Contract_Not_Found EXCEPTION;
3487 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
3488 l_api_name constant VARCHAR2(30) := 'Update_Parents_Date_Renewed';
3489
3490 -- To get the distinct cle_id_ascendant for that particular level
3491 CURSOR ancestry_cle_id( l_level NUMBER) IS
3492 SELECT distinct(cle_id_ascendant)
3493 FROM OKC_UPD_REN_TEMP
3494 WHERE level_sequence = l_level;
3495 Begin
3496
3497 IF (l_debug = 'Y') THEN
3498 okc_debug.Set_Indentation('OKC_RENEW_PVT');
3499 okc_debug.log('13300: Entered Update_Parents_Date_Renewed', 2);
3500 END IF;
3501
3502 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3503
3504 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3505 p_init_msg_list,
3506 '_PROCESS',
3507 x_return_status);
3508
3509 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3510 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3511 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3512 raise OKC_API.G_EXCEPTION_ERROR;
3513 END IF;
3514 -- Raising an exception when the contract header id is invalid
3515 select count(*) into l_invalid_chr_id
3516 from okc_k_headers_b
3517 where id = p_chr_id;
3518
3519 if (l_invalid_chr_id = 0) Then
3520 RAISE Contract_Not_Found;
3521 end if;
3522
3523 -- Inserting into temporary table
3524 IF (l_debug = 'Y') THEN
3525 okc_debug.log('13400: Before Insert Into OKC_UPD_REN_TEMP');
3526 END IF;
3527 insert into OKC_UPD_REN_TEMP(DNZ_CHR_ID,
3528 CLE_ID,
3529 CLE_ID_ASCENDANT,
3530 LEVEL_SEQUENCE)
3531 select line.dnz_chr_id, ans.cle_id,
3532 ans.cle_id_ascendant, ans.level_sequence
3533 from okc_k_lines_b line, okc_ancestrys ans
3534 where line.id = ans.cle_id
3535 and line.dnz_chr_id = p_chr_id;
3536
3537 -- To check whether any sublines are present
3538 select count(*) into l_top_count
3539 from okc_k_lines_b line, okc_ancestrys ans
3540 where line.id = ans.cle_id
3541 and line.dnz_chr_id = p_chr_id;
3542 -- If sublines are present
3543 If l_top_count > 0 Then
3544 -- Get the maximum level sequence
3545 select MAX(level_sequence) into l_level
3546 from OKC_UPD_REN_TEMP
3547 where dnz_chr_id = p_chr_id;
3548
3549 While (l_level > 0)
3550 Loop
3551 open ancestry_cle_id(l_level);
3552 Loop
3553 -- To get the cle_id_ascendant
3554 fetch ancestry_cle_id into l_cle_id_ascendant;
3555 exit when ancestry_cle_id%NOTFOUND;
3556 -- To check whether any sublines are present for this line
3557 select count(*) into l_line_count
3558 from okc_k_lines_b
3559 where cle_id = l_cle_id_ascendant;
3560
3561 If l_line_count > 0 Then
3562 -- To check the Date_Renewed field for all sublines
3563 select count(*) into l_date_count
3564 from okc_k_lines_b
3565 where cle_id = l_cle_id_ascendant
3566 and date_renewed IS NULL;
3567 -- If Date_Renewed is not null for all the sublines
3568 If l_date_count = 0 Then
3569 -- Update Date_Renewed field of parent line
3570 update okc_k_lines_b
3571 set date_renewed = ( select MAX(date_renewed )
3572 from okc_k_lines_b
3573 where cle_id = l_cle_id_ascendant )
3574 where id = l_cle_id_ascendant;
3575 End If;
3576 End If;
3577 End Loop;
3578 -- Go to next level
3579 l_level := l_level - 1;
3580 close ancestry_cle_id;
3581 End Loop;
3582 End If;
3583
3584 -- To check whether the header has any top line
3585 select count(*) into l_no_lines_count
3586 from okc_k_lines_b
3587 where chr_id = p_chr_id;
3588 -- If header has a top line
3589 If l_no_lines_count > 0 Then
3590 -- To check the Date_Renewed field for all top lines
3591 select count(*) into l_top_date_count
3592 from okc_k_lines_b
3593 where chr_id = p_chr_id
3594 and date_renewed IS NULL;
3595
3596 -- If Date_Renewed is not null for all the top lines
3597 If l_top_date_count = 0 Then
3598 -- Update Date_Renewed field of contract header
3599 update okc_k_headers_b
3600 set date_renewed = ( select MAX(date_renewed)
3601 from okc_k_lines_b
3602 where chr_id = p_chr_id )
3603 where id = p_chr_id;
3604 End If;
3605 End If;
3606
3607 IF (l_debug = 'Y') THEN
3608 okc_debug.log('13500: Leaving Update_Parents_Date_Renewed', 2);
3609 okc_debug.Reset_Indentation;
3610 END IF;
3611 EXCEPTION
3612 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3613
3614 IF (l_debug = 'Y') THEN
3615 okc_debug.log('13600: Exiting Update_Parents_Date_Renewed:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3616 okc_debug.Reset_Indentation;
3617 END IF;
3618
3619 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3620 (l_api_name,
3621 G_PKG_NAME,
3622 'OKC_API.G_RET_STS_ERROR',
3623 x_msg_count,
3624 x_msg_data,
3625 '_PROCESS');
3626
3627 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3628
3629 IF (l_debug = 'Y') THEN
3630 okc_debug.log('13700: Exiting Update_Parents_Date_Renewed:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3631 okc_debug.Reset_Indentation;
3632 END IF;
3633
3634 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3635 (l_api_name,
3636 G_PKG_NAME,
3637 'OKC_API.G_RET_STS_UNEXP_ERROR',
3638 x_msg_count,
3639 x_msg_data,
3640 '_PROCESS');
3641
3642 WHEN Contract_Not_Found THEN
3643
3644 IF (l_debug = 'Y') THEN
3645 okc_debug.log('13800: Exiting Update_Parents_Date_Renewed:Contract_Not_Found Exception', 2);
3646 okc_debug.Reset_Indentation;
3647 END IF;
3648
3649 x_return_status := OKC_API.G_RET_STS_ERROR;
3650 OKC_API.set_message(G_APP_NAME, 'OKC_CONTRACT_NOT_FOUND');
3651
3652 WHEN OTHERS THEN
3653
3654 IF (l_debug = 'Y') THEN
3655 okc_debug.log('13900: Exiting Update_Parents_Date_Renewed:OTHERS Exception', 2);
3656 okc_debug.Reset_Indentation;
3657 END IF;
3658
3659 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3660 (l_api_name,
3661 G_PKG_NAME,
3662 'OTHERS',
3663 x_msg_count,
3664 x_msg_data,
3665 '_PROCESS');
3666
3667 End Update_Parents_Date_Renewed;
3668
3669 /* Added the following function for bugfix 2093117 */
3670
3671 FUNCTION is_already_not_renewed(p_chr_id IN NUMBER, p_contract_number IN VARCHAR2, x_msg_name OUT NOCOPY VARCHAR2, p_renewal_called_from_ui IN VARCHAR2) RETURN VARCHAR2 IS
3672 Cursor cur_opn(p_chr_id number) is
3673 SELECT a.subject_chr_id,
3674 a.process_flag -- bugfix 2952330, selecting Process_flag to check the value 'A'
3675 FROM okc_operation_lines a,okc_operation_instances b, okc_class_operations c
3676 where a.object_chr_id=p_chr_id and
3677 c.id=b.cop_id and
3678 c.opn_code in ('RENEWAL', 'REN_CON')
3679 and b.id=a.oie_id and
3680 -- a.active_yn='Y' and /* Commented out to fix the bug 2108667 */
3681 a.subject_cle_id is null and
3682 a.object_cle_id is null
3683 order by a.active_yn desc,a.process_flag desc; /* Added this order by to get the 'Entered' contract
3684 first so that correct message (OKC_RENCOPY_ENTERED)
3685 can be given from the logic below, otherwise
3686 the cusrsor will fetch the 'Cancelled' contarct
3687 and the wrong message OKC_SHOULD_NOT_COME will
3688 be displayed. */
3689
3690 Cursor cur_ren(p_chr_id number) is
3691 SELECT CONTRACT_NUMBER,CONTRACT_NUMBER_MODIFIER,ste_code
3692 FROM okc_k_headers_b k,okc_statuses_b s
3693 where k.id=p_chr_id and k.sts_code=s.code;
3694
3695 k_ren_rec cur_ren%rowtype;
3696 l_id number:=OKC_API.G_MISS_NUM;
3697 l_process_flag varchar2(1):=OKC_API.G_MISS_CHAR;
3698 l_k varchar2(250):=' ';
3699 l_already_renewed varchar2(1) := OKC_API.G_FALSE;
3700
3701 BEGIN
3702 x_msg_name := 'O'; -- Bug 3386577 Assigning some intial value
3703 open cur_opn(p_chr_id);
3704 fetch cur_opn into l_id,l_process_flag;
3705 IF cur_opn%NOTFOUND THEN
3706 close cur_opn;
3707 RETURN OKC_API.G_TRUE;
3708 END IF;
3709 close cur_opn;
3710
3711 -- Bug 3560988 If the renewal is called from event
3712 -- then check if the contract has been renewed atleast once
3713 If p_renewal_called_from_ui = 'N' then
3714 For k_cur_opn_rec in cur_opn(p_chr_id)
3715 Loop
3716 if k_cur_opn_rec.process_flag = 'P' then
3717 l_already_renewed := OKC_API.G_TRUE;
3718 l_id := k_cur_opn_rec.subject_chr_id;
3719 exit;
3720 End if;
3721 End Loop;
3722 End if;
3723
3724
3725 /* Bugfix 2952330, if Process_Flag is 'A'(OKS requirement), the renewal continues */
3726 -- Bug 3560988 Added l_already_renewed to the If clause condition
3727 if l_process_flag = 'A' and l_already_renewed <> OKC_API.G_TRUE then
3728 RETURN OKC_API.G_TRUE;
3729 end if;
3730 /* End bugfix 2952330 */
3731
3732 If l_id <> OKC_API.G_MISS_NUM then
3733 open cur_ren(l_id);
3734 --san rencol
3735 --open cur_ren(k_header_rec.chr_id_renewed_to);
3736 fetch cur_ren into k_ren_rec;
3737 If cur_ren%NOTFOUND then
3738 OKC_API.set_message( p_app_name => g_app_name,
3739 p_msg_name =>'OKC_RENEWED_CONTRACT',
3740 p_token1 => 'NUMBER',
3741 p_token1_value => p_contract_number );
3742
3743 close cur_ren;
3744 RAISE g_exception_halt_validation;
3745 END IF;
3746 close cur_ren;
3747 l_k := k_ren_rec.contract_number;
3748 IF k_ren_rec.contract_number_modifier is not null then
3749 l_k := l_k ||' '|| k_ren_rec.contract_number_modifier;
3750 END IF;
3751 If k_ren_rec.ste_code = 'ENTERED' then
3752 OKC_API.set_message( p_app_name => g_app_name,
3753 p_msg_name =>'OKC_RENCOPY_ENTERED',
3754 p_token1 => 'NUMBER',
3755 p_token1_value => p_contract_number,
3756 p_token2 =>'RENCOPY',
3757 -- p_token2_value => k_ren_rec.contract_number );
3758 p_token2_value => l_k );
3759 /* The following ELSIF for CANCELLED is added to display the correct
3760 message in the log file when the Renew is run from SRS and the
3761 contract has already been renewed more than once. Before this code,
3762 OKC_SHOULD_NOT_COME was being displayed which was not correct */
3763 ELSIF k_ren_rec.ste_code = 'CANCELLED' then
3764 OKC_API.set_message( p_app_name => g_app_name,
3765 p_msg_name =>'OKC_ALREADY_NOT_RENEWED');
3766 x_msg_name := 'C'; -- Cancelled --Bug 3386577
3767 ELSIF k_ren_rec.ste_code not in ('ENTERED','CANCELLED') then
3768 OKC_API.set_message( p_app_name => g_app_name,
3769 p_msg_name =>'OKC_RENCOPY_APPROVE',
3770 p_token1 => 'NUMBER',
3771 p_token1_value => p_contract_number,
3772 p_token2 =>'RENCOPY',
3773 p_token2_value => l_k );
3774 ELSE
3775 OKC_API.set_message( p_app_name => g_app_name,
3776 p_msg_name =>'OKC_SHOULD_NOT_COME');
3777 END IF; --ste_code
3778 ELSE
3779 OKC_API.set_message( p_app_name => g_app_name,
3780 p_msg_name =>'OKC_RENEWED_CONTRACT',
3781 p_token1 => 'XXXXXX',
3782 p_token1_value => p_contract_number );
3783
3784 RAISE g_exception_halt_validation;
3785 END IF; --l_id
3786 RETURN OKC_API.G_FALSE;
3787
3788 END is_already_not_renewed;
3789
3790 END OKC_RENEW_PVT;
[Home] [Help]