1 PACKAGE BODY OKS_CHANGE_STATUS_PVT as
2 /* $Header: OKSVCSTB.pls 120.38.12020000.2 2012/07/03 05:42:35 spingali ship $ */
3 ---------------------------------------------------------------------------
4 -- GLOBAL MESSAGE CONSTANTS
5 ---------------------------------------------------------------------------
6 G_FND_APP CONSTANT VARCHAR2(200) := 'OKS';
7 ---------------------------------------------------------------------------
8 -- GLOBAL CONSTANTS
9 ---------------------------------------------------------------------------
10 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKS_CHANGE_STATUS_PVT';
11 G_MODULE CONSTANT VARCHAR2(200) := 'oks.plsql.'||G_PKG_NAME||'.';
12 G_APP_NAME CONSTANT VARCHAR2(3) := 'OKS';
13 ------------------------------------------------------------------------------
14 -- GLOBAL CONSTANTS
15 ------------------------------------------------------------------------------
16 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
17 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
18
19 g_api_version constant number :=1;
20 g_init_msg_list varchar2(1) := 'T';
21 g_msg_count NUMBER;
22 g_msg_data varchar2(240);
23 p_count number := 0;
24 ------------------------------------------------------------------------------
25 -- GLOBAL EXCEPTIONS
26 ------------------------------------------------------------------------------
27 E_Resource_Busy EXCEPTION;
28 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
29
30 /*
31 ** This procedure accepts contract_id and the new_sts_code and
32 ** changes the status the Contract, the lines and sub-lines are
33 ** also updated to the same status.
34 ** If the Contract has to be Cancelled then the source for the
35 ** cancel action needs to be passed (i.e, MANUAL or IBTRANSFER). -- made change from 'TRANSFER' to 'IBTRANSFER'
36 ** In cancellation case the amount for the Header and Lines is
37 ** updated to reflect the cancel action.
38 */
39
40 procedure Update_header_status(x_return_status OUT NOCOPY VARCHAR2,
41 x_msg_data OUT NOCOPY VARCHAR2,
42 x_msg_count OUT NOCOPY NUMBER,
43 p_init_msg_list in varchar2,
44 p_id in number,
45 p_new_sts_code in varchar2,
46 p_canc_reason_code in varchar2,
47 p_old_sts_code in varchar2,
48 p_comments in varchar2,
49 p_term_cancel_source in varchar2,
50 p_date_cancelled in date,
51 p_validate_status in varchar2) is
52
53
54 l_chr_id number;
55 l_new_ste_code varchar2(30);
56 l_old_ste_code varchar2(30);
57 l_old_sts_code varchar2(30);
58 l_chrv_tbl chrv_tbl_type;
59 l_new_sts_code varchar2(30);
60 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_HEADER_STATUS';
61
62 h_new_sts_code VARCHAR2(100); ---13024593
63 x_hook NUMBER; ---13024593
64 /* ---13024593*/
65 cursor get_ste_code_hook(p_code in varchar2) is
66 Select ste_code from okc_statuses_b where code = p_code;
67 /* ---13024593*/
68
69 begin
70
71 OKC_CVM_PVT.clear_g_transaction_id; /*Added for bug6418582*/
72
73 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
74 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
75 '100: Entered UPDATE_HEADER_STATUS');
76 END IF;
77
78 if ((p_new_sts_code is NULL) OR (p_id is null) OR (p_canc_reason_code is NULL)) then
79 raise FND_API.G_EXC_ERROR;
80 end if;
81
82 if (p_init_msg_list = FND_API.G_TRUE) then
83 fnd_msg_pub.initialize();
84 end if;
85
86 l_new_sts_code := p_new_sts_code;
87 l_old_sts_code := p_old_sts_code;
88
89 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
90 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
91 '110: Parameter Values ' ||
92 'contract_id - '|| p_id ||
93 'new_sts_code - '||l_new_sts_code ||
94 'old_sts_code - '||l_old_sts_code );
95 END IF;
96
97 /*Added for bug 13024593*/
98
99 ---call the custom hook
100 oks_code_hook.get_custom_status(p_id => p_id,
101 p_canc_reason_code => p_canc_reason_code,
102 p_term_cancel_source => p_term_cancel_source,
103 p_new_sts_code => h_new_sts_code,
104 x_hook => x_hook);
105
106 IF x_hook =1 then
107 l_new_sts_code :=h_new_sts_code;
108 open get_ste_code_hook(l_new_sts_code);
109 fetch get_ste_code_hook into l_new_ste_code;
110 If get_ste_code_hook%notfound then
111 l_new_sts_code := p_new_sts_code;
112 l_new_ste_code := null;
113 End if;
114 close get_ste_code_hook;
115 ELSIF x_hook = -1 THEN
116 raise FND_API.G_EXC_ERROR;
117 END IF;
118
119 /*Added for bug 13024593*/
120
121 OKS_CHANGE_STATUS_PVT.check_allowed_status(x_return_status => x_return_status,
122 x_msg_count => x_msg_count,
123 x_msg_data => x_msg_data,
124 p_id => p_id,
125 p_old_sts_code => l_old_sts_code,
126 p_new_sts_code => p_new_sts_code,
127 p_old_ste_code => l_old_ste_code,
128 p_new_ste_code => l_new_ste_code,
129 p_cle_id => NULL);
130
131
132 if (x_return_status = FND_API.G_RET_STS_ERROR) then
133 raise FND_API.G_EXC_ERROR;
134 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
135 raise FND_API.G_EXC_UNEXPECTED_ERROR;
136 end if;
137
138 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
139 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
140 '120: Completed check_allowed_status() ');
141 END IF;
142
143 l_chrv_tbl(1).id := p_id;
144 l_chrv_tbl(1).old_sts_code := l_old_sts_code;
145 l_chrv_tbl(1).old_ste_code := l_old_ste_code;
146 l_chrv_tbl(1).new_sts_code := l_new_sts_code;
147 l_chrv_tbl(1).new_ste_code := l_new_ste_code;
148
149 if (l_new_ste_code = 'CANCELLED') then
150 l_chrv_tbl(1).datetime_cancelled := p_date_cancelled;
151 l_chrv_tbl(1).trn_code := p_canc_reason_code;
152 l_chrv_tbl(1).term_cancel_source := p_term_cancel_source;
153 elsif (l_new_ste_code = 'ENTERED') then
154 l_chrv_tbl(1).datetime_cancelled := NULL;
155 l_chrv_tbl(1).trn_code := NULL;
156 l_chrv_tbl(1).term_cancel_source := NULL;
157 end if;
158
159 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
160 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
161 '130: Calling Update_Header_status with chrv_tbl populated');
162 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
163 '140: l_chrv_tbl(1).id '|| l_chrv_tbl(1).id);
164 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
165 '150: l_chrv_tbl(1).old_sts_code '|| l_chrv_tbl(1).old_sts_code);
166 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
167 '160: l_chrv_tbl(1).new_sts_code '|| l_chrv_tbl(1).new_sts_code);
168 END IF;
169
170 Update_header_status( x_return_status => x_return_status,
171 x_msg_data => x_msg_data,
172 x_msg_count => x_msg_count,
173 p_init_msg_list => FND_API.G_FALSE,
174 p_chrv_tbl => l_chrv_tbl,
175 p_canc_reason_code => p_canc_reason_code,
176 p_comments => p_comments,
177 p_term_cancel_source => p_term_cancel_source,
178 p_date_cancelled => p_date_cancelled);
179
180 if (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
181 raise FND_API.G_EXC_UNEXPECTED_ERROR;
182 elsif (x_return_status = FND_API.G_RET_STS_ERROR) then
183 raise FND_API.G_EXC_ERROR;
184 end if;
185
186 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
187 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
188 '170: Completed Update_header_status succesfully');
189 END IF;
190
191 x_return_status := FND_API.G_RET_STS_SUCCESS;
192
193 Exception
194 WHEN FND_API.G_EXC_ERROR THEN
195 x_return_status := FND_API.G_RET_STS_ERROR;
196
197 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
198 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'180: Leaving OKS_CHANGE_STATUS_PVT, one or more mandatory parameters missing :FND_API.G_EXC_ERROR');
199 END IF;
200
201 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
203
204 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
205 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'190: Leaving OKS_CHANGE_STATUS_PVT: FND_API.G_EXC_UNEXPECTED_ERROR '|| SQLERRM);
206 END IF;
207
208 WHEN OTHERS THEN
209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
210
211 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
212 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'200: Leaving OKS_CHANGE_STATUS_PVT because of EXCEPTION: '||sqlerrm);
213 END IF;
214
215 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
216 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name, SQLERRM );
217 END IF;
218 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
219 end;
220
221 /*
222 ** This procedure accepts multiple contracts for status change.
223 ** The plsql table chrv_tbl should be passed to the API containing
224 ** the contract which needs status change.
225 */
226 procedure Update_header_status(x_return_status OUT NOCOPY VARCHAR2,
227 x_msg_data OUT NOCOPY VARCHAR2,
228 x_msg_count OUT NOCOPY NUMBER,
229 p_init_msg_list in varchar2,
230 p_chrv_tbl in OUT NOCOPY chrv_tbl_type,
231 p_canc_reason_code in varchar2,
232 p_comments in varchar2,
233 p_term_cancel_source in varchar2,
234 p_date_cancelled in date,
235 p_validate_status in varchar2) is
236
237 p_control_rec okc_util.okc_control_rec_type;
238 l_chrv_tbl chrv_tbl_type;
239 l_new_ste_code varchar2(30);
240 l_old_ste_code varchar2(30);
241 l_ste_code1 varchar2(30);
242 l_return_status boolean;
243 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_HEADER_STATUS';
244 l_line_update varchar2(1);
245 i number := 0;
246 l_return_status boolean;
247 l_cle_id number := NULL;
248 l_chr_id number;
249 l_init_msg_list varchar2(1) := 'N';
250 l_wf_attr_details wf_attr_details;
251 l_wf_item_key oks_k_headers_b.wf_item_key%type;
252 l_valid_flag varchar2(1) := 'Y';
253
254 CURSOR csr_k_item_key(p_contract_id in number) IS
255 SELECT wf_item_key
256 FROM oks_k_headers_b
257 WHERE chr_id = p_contract_id;
258
259 begin
260 p_count := p_chrv_tbl.count;
261
262 if (p_init_msg_list = FND_API.G_TRUE) then
263 fnd_msg_pub.initialize();
264 end if;
265
266 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
267 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
268 '400: Entere Update_Header_Status');
269 END IF;
270
271 for i in p_chrv_tbl.first .. p_chrv_tbl.last
272 Loop
273 populate_table(p_chrv_tbl, i); -- p_chrv_tbl;
274
275 if (p_chrv_tbl(i).new_ste_code = 'CANCELLED') then
276 p_chrv_tbl(i).datetime_cancelled := p_date_cancelled;
277 p_chrv_tbl(i).trn_code := p_canc_reason_code;
278 p_chrv_tbl(i).term_cancel_source := p_term_cancel_source;
279 elsif (p_chrv_tbl(i).new_ste_code = 'ENTERED') then
280 p_chrv_tbl(i).datetime_cancelled := NULL;
281 p_chrv_tbl(i).trn_code := NULL;
282 p_chrv_tbl(i).term_cancel_source := NULL;
283 end if;
284
285 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
286 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
287 '410: Calling validate_Status p_chrv_tbl(i).id - '||p_chrv_tbl(i).id);
288 END IF;
289
290 if (p_validate_status = 'Y') then
291 Begin
292 Validate_Status(x_return_status,
293 x_msg_count,
294 x_msg_data,
295 p_chrv_tbl(i).id,
296 p_chrv_tbl(i).new_ste_code,
297 p_chrv_tbl(i).old_ste_code,
298 p_chrv_tbl(i).new_sts_code,
299 p_chrv_tbl(i).old_sts_code,
300 l_cle_id,
301 p_validate_status);
302
303 if (x_return_status = FND_API.G_RET_STS_ERROR) then
304 raise FND_API.G_EXC_ERROR;
305 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
306 raise FND_API.G_EXC_UNEXPECTED_ERROR;
307 end if;
308 l_valid_flag := 'Y';
309
310 EXCEPTION
311 WHEN FND_API.G_EXC_ERROR then
312 l_valid_flag := 'N';
313 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
314 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name, FND_MSG_PUB.Get('F', 1));
315 END IF;
316 End;
317 end if; -- p_validate_status
318
319 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
320 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
321 '420: l_valid_flag - '|| l_valid_flag );
322 END IF;
323
324 if l_valid_flag = 'Y' then
325 /*Added for bug 14137343*/
326 If p_term_cancel_source ='MANUAL' THEN
327 p_control_rec.flag := 'Y';
328 Else
329 p_control_rec.flag :='N';
330 End If;
331 /*Added for bug 14137343*/
332
333 p_control_rec.code := p_canc_reason_code;
334 p_control_rec.comments := p_comments;
335
336 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
337 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
338 '430: calling okc_contract_pub.update_contract_header');
339 END IF;
340
341 --bug 5710909
342 -- Added the following code to place a lock on contract header.
343 okc_contract_pub.lock_contract_header(
344 p_api_version => 1.0,
345 p_init_msg_list => 'T',
346 x_return_status => x_return_status,
347 x_msg_count => g_msg_count,
348 x_msg_data => g_msg_data,
349 p_chrv_rec => p_chrv_tbl(i));
350
351 if (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) Then
352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
353 elsif (x_return_status = FND_API.G_RET_STS_ERROR) Then
354 RAISE FND_API.G_EXC_ERROR;
355 end if;
356
357 --end of 5710909
358
359 OKC_CONTRACT_PUB.update_contract_header(
360 p_api_version => g_api_version,
361 P_INIT_MSG_LIST => 'F',
362 x_return_status => x_return_status,
363 x_msg_count => g_msg_count,
364 x_msg_data => g_msg_data,
365 p_restricted_update => 'T',
366 p_chrv_rec => p_chrv_tbl(i),
367 p_control_rec => p_control_rec,
368 x_chrv_rec => l_chrv_tbl(i));
369
370
371 if (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) Then
372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
373 elsif (x_return_status = FND_API.G_RET_STS_ERROR) Then
374 RAISE FND_API.G_EXC_ERROR;
375 end if;
376
377 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
378 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
379 '440: Calling oks_change_status_pvt.update_line_status ');
380 END IF;
381
382 -- cgopinee bugfix for 9259068
383 IF (p_chrv_tbl(i).new_sts_code<>p_chrv_tbl(i).old_sts_code) THEN
384 OKS_CHANGE_STATUS_PVT.G_HEADER_STATUS_CHANGED :='Y';
385 END if;
386
387 OKS_CHANGE_STATUS_PVT.update_line_status(
388 x_return_status => x_return_status,
389 x_msg_data => x_msg_data,
390 x_msg_count => x_msg_count,
391 p_init_msg_list => l_init_msg_list,
392 p_id => p_chrv_tbl(i).id,
393 p_cle_id => l_cle_id,
394 p_new_sts_code => p_chrv_tbl(i).new_sts_code,
395 p_canc_reason_code => p_canc_reason_code,
396 p_old_sts_code => p_chrv_tbl(i).old_sts_code,
397 p_old_ste_code => p_chrv_tbl(i).old_ste_code,
398 p_new_ste_code => p_chrv_tbl(i).new_ste_code,
399 p_term_cancel_source => p_term_cancel_source,
400 p_date_cancelled => p_date_cancelled,
401 p_comments => p_comments,
402 p_validate_status => 'N');
403
404 If (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
405 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
406 elsIf (x_return_status = FND_API.G_RET_STS_ERROR) then
407 RAISE FND_API.G_EXC_ERROR;
408 End if;
409
410 -- Call API for Cleaning the Renewal links which are due to
411 -- cancel action on the contract.
412 if (p_chrv_tbl(i).old_ste_code = 'ENTERED' and p_chrv_tbl(i).new_ste_code = 'CANCELLED') then
413
414 -- Check if the contract is a renewed contract
415 IF (Renewed_YN(p_chrv_tbl(i).id)) then
416 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
417 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
418 '450: Calling okc_contract_pvt.clean_ren_links, Entered => Canceled ');
419 END IF;
420
421 OKC_CONTRACT_PVT.CLEAN_REN_LINKS(p_target_chr_id => p_chrv_tbl(i).id,
422 p_api_version => g_api_version,
423 p_init_msg_list => 'F',
424 x_return_status => x_return_status,
425 x_msg_count => x_msg_count,
426 x_msg_data => x_msg_data);
427
428 if (x_return_status = FND_API.G_RET_STS_ERROR) then
429 Raise FND_API.G_EXC_ERROR;
430 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
431 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
432 end if;
433 END IF; -- Renewed_YN
434
435 -- Abort the Renewal workflow process as the contract is getting
436 -- cancelled.
437 open csr_k_item_key(p_chrv_tbl(i).id);
438 fetch csr_k_item_key into l_wf_item_key;
439 close csr_k_item_key;
440
441 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
442 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
443 '460: Calling oks_wf_k_process_pvt.cancel_contract
444 for l_wf_item_key - ' || l_wf_item_key);
445 END IF;
446
447 -- Changed by MKS/SKK
448 OKS_WF_K_PROCESS_PVT.cancel_contract
449 (
450 p_api_version => 1.0,
451 p_init_msg_list => 'F',
452 p_contract_id => p_chrv_tbl(i).id,
453 p_item_key => l_wf_item_key,
454 p_cancellation_reason => p_canc_reason_code,
455 p_cancellation_date => p_date_cancelled,
456 p_cancel_source => p_term_cancel_source,
457 p_comments => p_comments,
458 x_return_status => x_return_status,
459 x_msg_count => x_msg_count,
460 x_msg_data => x_msg_data);
461
462 if (x_return_status = FND_API.G_RET_STS_ERROR) then
463 Raise FND_API.G_EXC_ERROR;
464 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
465 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
466 end if;
467
468 -- End of Changed by MKS/SKK
469 elsif (p_chrv_tbl(i).old_ste_code = 'CANCELLED' and p_chrv_tbl(i).new_ste_code = 'ENTERED') then
470
471 -- Check if the contract is renewed
472 IF (Renewed_YN(p_chrv_tbl(i).id)) then
473 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
474 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
475 '470: okc_contract_pvt.relink_renew, Canceled => Entered ');
476 END IF;
477 -- Reinstantiate the renewal link as the contract is getting into 'Entererd' status
478
479 OKC_CONTRACT_PVT.RELINK_RENEW(p_target_chr_id => p_chrv_tbl(i).id,
480 p_api_version => g_api_version,
481 P_INIT_MSG_LIST => 'F',
482 x_return_status => x_return_status,
483 x_msg_count => x_msg_count,
484 x_msg_data => x_msg_data);
485
486 if (x_return_status = FND_API.G_RET_STS_ERROR) then
487 Raise FND_API.G_EXC_ERROR;
488 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
489 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
490 end if;
491
492 END IF; -- Renewed_YN
493
494 -- if the above call is succesful then reinstantiate the renewal workflow process
495
496 -- Changed by MKS/SKK
497 l_wf_attr_details.CONTRACT_ID := l_chrv_tbl(i).id;
498 l_wf_attr_details.NEGOTIATION_STATUS := 'DRAFT';
499 l_wf_attr_details.PROCESS_TYPE := 'MANUAL';
500 l_wf_attr_details.IRR_FLAG := 'Y';
501 --
502 -- MKS: Commented below as we want wf launch process to generate the id AND update the oks_k_headers table with the item key and
503 -- Negotiation Status
504 --
505 -- l_wf_attr_details.ITEM_KEY := l_chrv_tbl(i).id || to_char(sysdate, 'YYYYMMDDHH24MISS');
506
507 -- End of Changed by MKS/SKK
508
509 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
510 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
511 '480: Launching workflow process ');
512 END IF;
513
514 OKS_WF_K_PROCESS_PVT.launch_k_process_wf (
515 p_api_version => 1.0,
516 p_init_msg_list => 'F',
517 p_wf_attributes => l_wf_attr_details,
518 x_return_status => x_return_status,
519 x_msg_count => x_msg_count,
520 x_msg_data => x_msg_data);
521
522 if (x_return_status = FND_API.G_RET_STS_ERROR) then
523 Raise FND_API.G_EXC_ERROR;
524 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
525 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
526 end if;
527
528 end if;
529 end if; -- l_validate_flag
530 end loop;
531
532 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
533 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
534 '485: exiting Update_header_status ');
535 END IF;
536 x_return_status := FND_API.G_RET_STS_SUCCESS;
537
538 EXCEPTION
539 WHEN FND_API.G_EXC_ERROR THEN
540 x_return_status := FND_API.G_RET_STS_ERROR ;
541
542 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
543 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'490: Leaving OKS_CHANGE_STATUS_PVT: FND_API.G_EXC_ERROR');
544 END IF;
545
546 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
547 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
548
549 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
550 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving OKS_CHANGE_STATUS_PVT: FND_API.G_EXC_UNEXPECTED_ERROR '||SQLERRM);
551 END IF;
552
553 WHEN OTHERS THEN
554 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
555
556 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
557 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'510: Leaving OKS_CHANGE_STATUS_PVT because of EXCEPTION: '||sqlerrm);
558 END IF;
559
560 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
561 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name, SQLERRM );
562 END IF;
563 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
564 end;
565
566
567 PROCEDURE populate_table(x_chrv_tbl in out NOCOPY chrv_tbl_type, i in number) IS
568 l_object_version_number NUMBER;
569 l_new_sts_code VARCHAR2(100);
570
571 CURSOR c_obj_ver(p_id NUMBER) IS
572 SELECT object_version_number, decode(
573 NVL(sign(months_between(START_DATE,sysdate+1)),1),-1,decode(
574 NVL(sign(months_between(END_DATE,sysdate-1)),1),1,'ACTIVE','EXPIRED'),'SIGNED' )
575 FROM okc_k_headers_b
576 WHERE id = p_id;
577 BEGIN
578 x_chrv_tbl(i).VALIDATE_YN := 'N';
579 Open c_obj_ver(x_chrv_tbl(i).id);
580 Fetch c_obj_ver Into l_object_version_number, l_new_sts_code;
581 Close c_obj_ver;
582
583 x_chrv_tbl(1).object_version_number := l_object_version_number;
584 x_chrv_tbl(1).STS_CODE := x_chrv_tbl(i).new_sts_code;
585
586 If p_count = 1 Then
587 x_chrv_tbl(i).STS_CODE := x_chrv_tbl(i).new_sts_code;
588 Else
589 If x_chrv_tbl(i).old_ste_code = 'HOLD' Then -- old sts cdoe
590 If x_chrv_tbl(i).new_ste_code IN ('ACTIVE', 'SIGNED', 'EXPIRED') Then -- new ste code
591 x_chrv_tbl(i).STS_CODE :=l_new_sts_code;
592 End If;
593 End If;
594 End If;
595
596 END;
597
598 -- This procedure validates the status change and throws error
599 -- if the status change is not allowed due to Renewal links.
600
601 procedure VALIDATE_STATUS(x_return_status OUT NOCOPY varchar2,
602 x_msg_count OUT NOCOPY number,
603 x_msg_data OUT NOCOPY varchar2,
604 p_id in number,
605 p_new_ste_code in varchar2,
606 p_old_ste_code in varchar2,
607 p_new_sts_code in varchar2,
608 p_old_sts_code in varchar2,
609 p_cle_id in number,
610 p_validate_status in varchar2
611 )
612 is
613 l_chr_id number := p_id;
614 l_validate varchar2(1) := 'Y';
615 l_return_status varchar2(1);
616 l_api_name varchar2(100) := 'Validate Status';
617 begin
618 IF RENEWED_YN(l_chr_id) THEN
619 -- Changing from 'Canceled' to 'Entered' Status
620 If p_old_ste_code ='CANCELLED' and p_new_ste_code = 'ENTERED' then
621 If target_exists(l_chr_id, p_cle_id) then
622 If Is_Entered(l_chr_id, p_cle_id) then
623 fnd_message.set_name('OKC', 'OKC_CONT_CAN_ENT_1');
624 fnd_message.set_token('PARENT_K',get_source_list(l_chr_id, p_cle_id));
625 fnd_message.set_token('RENEW_K',get_target_list(l_chr_id, p_cle_id));
626 fnd_msg_pub.add;
627 Raise FND_API.G_EXC_ERROR;
628 Elsif Is_Not_Entered_Cancelled(l_chr_id, p_cle_id) then
629 l_validate := 'N';
630 fnd_message.set_name('OKC', 'OKC_CONT_CAN_ENT_2');
631 fnd_message.set_token('PARENT_K',get_source_list(l_chr_id, p_cle_id));
632 fnd_message.set_token('RENEW_K',get_target_list(l_chr_id, p_cle_id));
633 fnd_msg_pub.add;
634 Raise FND_API.G_EXC_ERROR;
635 Else
636 fnd_message.set_name('OKC', 'OKC_CONT_CAN_ENT_3');
637 fnd_message.set_token('PARENT_K',get_source_list(l_chr_id, p_cle_id));
638 fnd_msg_pub.add;
639 Raise OKC_API.G_EXC_WARNING;
640 End If; -- Is_entered
641 -- Even if any other target contract does not exist, the contract is still being resurrected
642 Else -- target_exists
643 fnd_message.set_name('OKC', 'OKC_CONT_CAN_ENT_3');
644 fnd_message.set_token('PARENT_K',get_source_list(l_chr_id,p_cle_id));
645 fnd_msg_pub.add;
646 Raise OKC_API.G_EXC_WARNING;
647 End If; -- target_exists
648 End If; -- p_cancelled to entered
649 END IF; -- Renewed_YN
650
651 -- This should be only for mass status change need to put if clause
652 If (p_old_sts_code = 'QA_HOLD') OR (p_old_sts_code = p_new_sts_code) Then
653 x_return_status := FND_API.G_RET_STS_ERROR;
654 End If;
655
656 x_return_status := FND_API.G_RET_STS_SUCCESS;
657 ------------------------------
658 EXCEPTION
659 WHEN FND_API.G_EXC_ERROR THEN
660 x_return_status := FND_API.G_RET_STS_ERROR ;
661 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
662 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'300: Leaving OKS_CHANGE_STATUS_PVT : FND_API.G_EXC_ERROR');
663 END IF;
664 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
665
666 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
668 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
669 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'310: Leaving OKS_CHANGE_STATUS_PVT : FND_API.G_EXC_UNEXPECTED_ERROR '||SQLERRM);
670 END IF;
671 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
672
673 WHEN OKC_API.G_EXC_WARNING THEN
674 x_return_status := OKC_API.G_RET_STS_WARNING;
675 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
676 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'320: Leaving OKS_CHANGE_STATUS_PVT : OKC_API.G_EXC_WARNING');
677 END IF;
678
679 WHEN OTHERS THEN
680 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
681 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'330: Leaving OKS_CHANGE_STATUS_PVT because of EXCEPTION: '||sqlerrm);
682 END IF;
683
684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
685 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
686 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name, SQLERRM );
687 END IF;
688
689 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
690 end;
691
692 function RENEWED_YN(p_id in number) return boolean is
693 l_chr_id number := p_id;
694 l_renewed varchar2(1):= 'N';
695 begin
696 select 'Y'
697 into l_renewed
698 from okc_operation_instances OIE,
699 okc_class_operations COP
700 where OIE.cop_id=COP.id
701 and COP.opn_code in ('RENEWAL', 'REN_CON')
702 and target_chr_id = l_chr_id
703 and rownum = 1;
704 --
705 if l_renewed = 'Y' then
706 return(TRUE);
707 else
708 return(FALSE);
709 end if;
710 exception
711 when no_data_found then
712 return(FALSE);
713 end;
714 --
715
716 function get_source_list(p_id in number, p_cle_id in number) return varchar2 is
717 l_chr_id number := p_id;
718 l_source_chr varchar2(1000);
719
720 -- building a list of source contract for the renewed contract being resurrected
721 cursor C1 is
722 select distinct contract_number, contract_number_modifier,
723 contract_number||decode(contract_number_modifier, NULL,'','-'||contract_number_modifier) contracts
724 from okc_k_headers_b CHR,
725 okc_operation_lines OLI,
726 okc_operation_instances OIE, --**
727 okc_class_operations COP --**
728 where OLI.subject_chr_id = l_chr_id
729 and OLI.object_chr_id = chr.id
730 and OLI.oie_id = OIE.id --**
731 and OIE.cop_id = COP.id --**
732 and COP.opn_code in ('RENEWAL', 'REN_CON') --**
733 and OLI.subject_cle_id > 0;
734
735 -- Line Level Check, introduced for LLC
736
737 Cursor C2 is
738 select contract_number, contract_number_modifier,
739 contract_number||decode(contract_number_modifier, NULL,'','-'||contract_number_modifier) contracts
740 from okc_k_headers_b CHR,
741 okc_operation_lines OLI,
742 okc_operation_instances OIE, --**
743 okc_class_operations COP --**
744 where OLI.subject_chr_id = l_chr_id
745 and OLI.subject_cle_id = p_cle_id
746 and OLI.object_chr_id = chr.id
747 and OLI.oie_id = OIE.id --**
748 and OIE.cop_id = COP.id --**
749 and COP.opn_code in ('RENEWAL', 'REN_CON') --**
750 and OLI.subject_cle_id > 0;
751 begin
752 if (p_cle_id is NULL) then
753 for C1_rec in C1 loop
754 l_source_chr := l_source_chr||','||C1_rec.contracts;
755 end loop;
756 else
757 for C2_rec in C2 Loop
758 l_source_chr := l_source_chr||','||C2_rec.contracts;
759 end loop;
760 end if;
761 return(l_source_chr);
762 end;
763 --
764
765 function get_target_list(p_id in number, p_cle_id in number) return varchar2 is
766 l_chr_id number := p_id;
767 l_target_chr varchar2(1000);
768
769 -- building list of target contracts for the same source contracts
770 CURSOR C1 IS
771 SELECT distinct contract_number, contract_number_modifier,
772 contract_number||decode(contract_number_modifier, NULL,'','-'||contract_number_modifier) contracts
773 FROM okc_k_headers_b CHR,
774 okc_operation_lines OLI1,
775 okc_operation_lines OLI2,
776 okc_operation_instances OIE1,
777 okc_class_operations COP1,
778 okc_operation_instances OIE2,
779 okc_class_operations COP2
780 WHERE CHR.id = OLI1.subject_chr_id
781 and OLI1.object_chr_id = OLI2.object_chr_id
782 and OLI1.oie_id = OIE1.id
783 and OIE1.cop_id = COP1.id
784 and COP1.opn_code in ('RENEWAL', 'REN_CON')
785 and OLI2.oie_id = OIE2.id
786 and OIE2.cop_id = COP2.id
787 and COP2.opn_code in ('RENEWAL', 'REN_CON')
788 and OLI2.subject_chr_id = l_chr_id
789 and OLI1.subject_chr_id <> l_chr_id
790 and OLI2.subject_cle_id > 0
791 and OLI1.subject_cle_id > 0;
792 --
793 -- Line Level Check added as part of LLC
794 CURSOR C2 is
795 SELECT contract_number, contract_number_modifier,
796 contract_number||decode(contract_number_modifier, NULL,'','-'||contract_number_modifier) contracts
797 FROM okc_k_headers_b CHR,
798 okc_k_lines_b CLE,
799 okc_statuses_b STE,
800 okc_operation_lines OLI1,
801 okc_operation_lines OLI2,
802 okc_operation_instances OIE1,
803 okc_class_operations COP1,
804 okc_operation_instances OIE2,
805 okc_class_operations COP2
806 WHERE CHR.id = OLI1.subject_chr_id
807 and OLI1.object_chr_id = OLI2.object_chr_id
808 and OLI1.oie_id = OIE1.id
809 and OIE1.cop_id = COP1.id
810 and COP1.opn_code in ('RENEWAL', 'REN_CON')
811 and OLI2.oie_id = OIE2.id
812 and OIE2.cop_id = COP2.id
813 and COP2.opn_code in ('RENEWAL', 'REN_CON')
814 and CHR.id = CLE.dnz_chr_id
815 and CLE.sts_code = STE.Code
816 and STE.STE_CODE = 'ENTERED' -- this is a retrictive condn.
817 and CLE.id = OLI1.subject_cle_id
818 and OLI2.subject_chr_id = l_chr_id
819 and OLI1.subject_chr_id <> l_chr_id
820 and OLI2.subject_cle_id = p_cle_id
821 and OLI1.object_cle_id = OLI2.object_cle_id
822 and OLI1.subject_cle_id <> p_cle_id;
823
824 begin
825 if (p_cle_id is NULL) then
826 for C1_rec in C1 loop
827 l_target_chr := l_target_chr||','||C1_rec.contracts;
828 end loop;
829 else
830 for C2_rec in C2 loop
831 l_target_chr := l_target_chr||','||C2_rec.contracts;
832 end loop;
833 end if;
834 return(l_target_chr);
835 end;
836 --
837 --
838
839 function target_cancelled(p_id in Number, p_cle_id in number) return boolean is
840 l_chr_id number := p_id;
841 l_cle_cncl varchar2(1);
842 l_chr_cncl varchar2(1);
843 --
844 -- Following two statements will verify if all other contracts renewed using
845 -- the same header/lines as used for the target contract being resurected are
846 -- CANCELLED status (DATE_RENEWED is NULL).
847 --
848 cursor c1 is
849 select distinct 'Y'
850 from OKC_K_HEADERS_B CHR,
851 okc_operation_lines OLI,
852 okc_operation_instances OIE,
853 okc_class_operations COP
854 where chr.id = oli.object_chr_id
855 and OLI.oie_id = OIE.id
856 and OIE.cop_id = COP.id
857 and COP.opn_code in ('RENEWAL', 'REN_CON')
858 and oli.subject_chr_id = l_chr_id
859 and CHR.date_renewed is NOT NULL;
860
861 cursor c2 is
862 select distinct 'Y'
863 from OKC_K_LINES_B CLE,
864 okc_operation_lines OLI,
865 okc_operation_instances OIE,
866 okc_class_operations COP
867 where cle.id = oli.object_cle_id
868 and OLI.oie_id = OIE.id
869 and OIE.cop_id = COP.id
870 and COP.opn_code in ('RENEWAL', 'REN_CON')
871 and oli.subject_chr_id = l_chr_id
872 and Cle.date_renewed is NOT NULL;
873 begin
874 open c1;
875 fetch c1 into l_chr_cncl;
876 close c1;
877 --
878 open c1;
879 fetch c1 into l_cle_cncl;
880 close c1;
881 --
882 if (l_chr_cncl = 'Y' and l_cle_cncl = 'Y') then
883 return(TRUE);
884 else
885 return(FALSE);
886 end if;
887 end;
888
889
890 function Is_Entered(p_id in Number, p_cle_id in Number) return boolean is
891 l_chr_id number := p_id;
892 l_status varchar2(1);
893 CURSOR c1 IS
894 SELECT distinct 'Y'
895 FROM okc_k_headers_b CHR,
896 okc_statuses_b STS,
897 okc_operation_lines OLI1,
898 okc_operation_lines OLI2,
899 okc_operation_instances OIE1, --**
900 okc_class_operations COP1, --**
901 okc_operation_instances OIE2, --**
902 okc_class_operations COP2 --**
903 WHERE CHR.id = OLI1.subject_chr_id
904 and OLI1.oie_id = OIE1.id --**
905 and OIE1.cop_id = COP1.id --**
906 and COP1.opn_code in ('RENEWAL', 'REN_CON') --**
907 and OLI2.oie_id = OIE2.id --**
908 and OIE2.cop_id = COP2.id --**
909 and COP2.opn_code in ('RENEWAL', 'REN_CON') --**
910 AND OLI1.object_chr_id = OLI2.object_chr_id
911 AND OLI2.subject_chr_id = l_chr_id
912 AND OLI1.subject_cle_id IS NULL
913 AND OLI2.subject_cle_id IS NULL
914 AND CHR.sts_code = STS.code
915 AND STS.ste_code = 'ENTERED'
916 AND OLI1.active_yn = 'Y'
917 AND OLI1.process_flag = 'P'
918 AND OLI2.process_flag = 'P';
919
920 -- Line Level Check added as part of LLC
921
922 CURSOR c2 IS
923 SELECT distinct 'Y'
924 FROM okc_k_headers_b CHR,
925 okc_k_lines_b CLE,
926 okc_statuses_b STS,
927 okc_operation_lines OLI1,
928 okc_operation_lines OLI2,
929 okc_operation_instances OIE1, --**
930 okc_class_operations COP1, --**
931 okc_operation_instances OIE2, --**
932 okc_class_operations COP2 --**
933 WHERE CHR.id = OLI1.subject_chr_id
934 and OLI1.oie_id = OIE1.id --**
935 and OIE1.cop_id = COP1.id --**
936 and COP1.opn_code in ('RENEWAL', 'REN_CON') --**
937 and OLI2.oie_id = OIE2.id --**
938 and OIE2.cop_id = COP2.id --**
939 and COP2.opn_code in ('RENEWAL', 'REN_CON') --**
940 AND OLI1.object_chr_id = OLI2.object_chr_id
941 AND OLI2.subject_chr_id = l_chr_id
942 AND OLI1.subject_chr_id <> l_chr_id
943 AND OLI1.subject_cle_id <> p_cle_id
944 AND OLI2.subject_cle_id = p_cle_id
945 AND CLE.sts_code = STS.code
946 AND STS.ste_code <> 'ENTERED'
947 AND CHR.id = CLE.DNZ_CHR_ID
948 AND CLE.ID = OLI1.subject_cle_id
949 AND OLI1.object_cle_id = OLI2.object_cle_id
950 AND OLI1.active_yn = 'Y'
951 AND OLI1.process_flag = 'P'
952 AND OLI2.process_flag = 'P';
953 --
954 begin
955 if (p_cle_id is NULL) then
956 open c1;
957 fetch c1 into l_status;
958 close c1;
959 else
960 open c2;
961 fetch c2 into l_status;
962 close c2;
963 end if;
964
965 if l_status = 'Y' then
966 return(TRUE);
967 else
968 return(FALSE);
969 end if;
970 end;
971 --
972 --
973
974 function Is_Not_Entered_Cancelled(p_id in Number, p_cle_id in Number) return boolean is
975 l_chr_id number := p_id;
976 l_status varchar2(1);
977 --
978 --
979 CURSOR c1 IS
980 SELECT distinct 'Y'
981 FROM okc_k_headers_b CHR,
982 okc_statuses_b STS,
983 okc_operation_lines OLI1,
984 okc_operation_lines OLI2,
985 okc_operation_instances OIE1, --**
986 okc_class_operations COP1, --**
987 okc_operation_instances OIE2, --**
988 okc_class_operations COP2 --**
989 WHERE CHR.id = OLI1.subject_chr_id
990 and OLI1.oie_id = OIE1.id --**
991 and OIE1.cop_id = COP1.id --**
992 and COP1.opn_code in ('RENEWAL', 'REN_CON') --**
993 and OLI2.oie_id = OIE2.id --**
994 and OIE2.cop_id = COP2.id --**
995 and COP2.opn_code in ('RENEWAL', 'REN_CON') --**
996 AND OLI1.object_chr_id = OLI2.object_chr_id
997 AND OLI2.subject_chr_id = l_chr_id
998 AND OLI1.subject_cle_id IS NULL
999 AND OLI2.subject_cle_id IS NULL
1000 AND CHR.STS_CODE = STS.CODE
1001 AND STS.ste_code not in ('ENTERED', 'CANCELLED')
1002 AND OLI1.active_yn = 'Y'
1003 AND OLI1.process_flag = 'P'
1004 AND OLI2.process_flag = 'P';
1005 --
1006 -- Line Level Check added as part of LLC
1007
1008 CURSOR c2 IS
1009 SELECT distinct 'Y'
1010 FROM okc_k_headers_b CHR,
1011 okc_k_lines_b CLE,
1012 okc_statuses_b STS,
1013 okc_operation_lines OLI1,
1014 okc_operation_lines OLI2,
1015 okc_operation_instances OIE1, --**
1016 okc_class_operations COP1, --**
1017 okc_operation_instances OIE2, --**
1018 okc_class_operations COP2 --**
1019 WHERE CHR.id = OLI1.subject_chr_id
1020 and CHR.id = CLE.dnz_chr_id
1021 and OLI1.oie_id = OIE1.id --**
1022 and OIE1.cop_id = COP1.id --**
1023 and COP1.opn_code in ('RENEWAL', 'REN_CON') --**
1024 and OLI2.oie_id = OIE2.id --**
1025 and OIE2.cop_id = COP2.id --**
1026 and COP2.opn_code in ('RENEWAL', 'REN_CON') --**
1027 AND OLI1.object_chr_id = OLI2.object_chr_id
1028 AND OLI2.subject_chr_id = l_chr_id
1029 AND OLI1.subject_cle_id IS NULL
1030 AND OLI2.subject_cle_id IS NULL
1031 AND CLE.STS_CODE = STS.CODE
1032 AND STS.ste_code not in ('ENTERED', 'CANCELLED')
1033 AND OLI1.object_cle_id = OLI2.object_cle_id
1034 AND OLI2.subject_cle_id = p_cle_id;
1035
1036 begin
1037 if (p_cle_id is NULL) then
1038 open c1;
1039 fetch c1 into l_status;
1040 close c1;
1041 else
1042 open c2;
1043 fetch c2 into l_status;
1044 close c2;
1045 end if;
1046
1047 if l_status = 'Y' then
1048 return(TRUE);
1049 else
1050 return(FALSE);
1051 end if;
1052
1053 exception
1054 when no_data_found then
1055 return(FALSE);
1056 end;
1057
1058
1059 function TARGET_EXISTS(p_id in Number, p_cle_id in Number) return boolean is
1060 l_chr_id number := p_id;
1061 l_target_exists varchar2(1);
1062 --
1063 -- Following cursor will check if there are any other contracts renewed using
1064 -- the same header/lines as used for the target contract being resurected
1065
1066 --** Additional check added as we are only interested in 'Renewal and Renewal Consolidation'
1067
1068 cursor C1 is
1069 select distinct 'Y'
1070 from okc_operation_lines OLI1,
1071 okc_operation_lines OLI2,
1072 okc_operation_instances OIE1, --**
1073 okc_class_operations COP1, --**
1074 okc_operation_instances OIE2, --**
1075 okc_class_operations COP2 --**
1076 where OLI1.object_chr_id = OLI2.object_chr_id
1077 and OLI1.oie_id = OIE1.id --**
1078 and OIE1.cop_id = COP1.id --**
1079 and COP1.opn_code in ('RENEWAL', 'REN_CON') --**
1080 and OLI2.oie_id = OIE2.id --**
1081 and OIE2.cop_id = COP2.id --**
1082 and COP2.opn_code in ('RENEWAL', 'REN_CON') --**
1083 and OLI1.subject_chr_id <> OLI2.subject_chr_id
1084 and OLI1.subject_chr_id = l_chr_id
1085 and OLI2.subject_cle_id > 0
1086 and OLI1.subject_cle_id > 0;
1087
1088 -- Line Level Check added as part of LLC
1089 Cursor C2 is
1090 select distinct 'Y'
1091 from okc_operation_lines OLI1,
1092 okc_operation_lines OLI2,
1093 okc_operation_instances OIE1, --**
1094 okc_class_operations COP1, --**
1095 okc_operation_instances OIE2, --**
1096 okc_class_operations COP2 --**
1097 where OLI1.object_chr_id = OLI2.object_chr_id
1098 and OLI1.oie_id = OIE1.id --**
1099 and OIE1.cop_id = COP1.id --**
1100 and COP1.opn_code in ('RENEWAL', 'REN_CON') --**
1101 and OLI2.oie_id = OIE2.id --**
1102 and OIE2.cop_id = COP2.id --**
1103 and COP2.opn_code in ('RENEWAL', 'REN_CON') --**
1104 and OLI1.subject_chr_id <> OLI2.subject_chr_id
1105 and OLI1.subject_chr_id = l_chr_id
1106 and OLI2.subject_cle_id <> OLI1.subject_cle_id
1107 and OLI1.subject_cle_id = p_cle_id;
1108 --
1109 begin
1110
1111 if (p_cle_id is NULL) then
1112 open C1;
1113 fetch C1 into l_target_exists;
1114 close C1;
1115 else
1116 open C2;
1117 fetch c2 into l_target_exists;
1118 close C2;
1119 end if;
1120
1121 if l_target_exists = 'Y' then
1122 return(TRUE);
1123 else
1124 return(FALSE);
1125 end if;
1126 end;
1127
1128 -- This procedure updates the line/sub-line status
1129 -- the only possible options for this would be
1130 -- 'Entered' and 'Cancelled', as all other statuses
1131 -- are applicable to Contract Header.
1132 -- This procedure also takes care of updating the
1133 -- header and line amountsi, adjusting the billing
1134 -- schedules and maintaining the renewal links when
1135 -- the status of the line is changed.
1136 --
1137 procedure Update_line_status (x_return_status OUT NOCOPY VARCHAR2,
1138 x_msg_data OUT NOCOPY VARCHAR2,
1139 x_msg_count OUT NOCOPY NUMBER,
1140 p_init_msg_list in varchar2,
1141 p_id in number,
1142 p_cle_id in number,
1143 p_new_sts_code in varchar2,
1144 p_canc_reason_code in varchar2,
1145 p_old_sts_code in varchar2,
1146 p_old_ste_code in varchar2,
1147 p_new_ste_code in varchar2,
1148 p_term_cancel_source in varchar2,
1149 p_date_cancelled in Date,
1150 p_comments in varchar2,
1151 p_validate_status in varchar2) is
1152
1153 l_api_name Varchar2(100) := 'UPDATE_LINE_STATUS';
1154 x_num number;
1155 l_clev_tbl clev_tbl_type;
1156 l1_clev_tbl clev_tbl_type;
1157 l_code_Tbl VC30_Tbl_Type;
1158 l_id_Tbl Num_Tbl_Type;
1159 l_obj_ver_tbl Num_Tbl_Type;
1160 l_lse_tbl Num_Tbl_Type;
1161 l_type Varchar2(30);
1162 l_line_update Varchar2(1) := 'Y';
1163 l_hstv_rec OKC_K_HISTORY_PVT.hstv_rec_type;
1164 x_hstv_rec OKC_K_HISTORY_PVT.hstv_rec_type;
1165 l_version VARCHAR2(24); --Changed datatype from NUMBER TO VARCHAR2(24)
1166
1167 l_scs_code varchar2(30);
1168 --
1169 cursor c_type is
1170 select ste_code
1171 from okc_statuses_v
1172 where code=p_new_sts_code;
1173
1174 -- bug#6144856 --
1175
1176 cursor c_old_type is
1177 select ste_code
1178 from okc_statuses_v
1179 where code=p_old_sts_code;
1180
1181 l_old_type varchar2(30);
1182
1183 -- end of bug#6144856 --
1184
1185 cursor c_top_line_chk is
1186 select id
1187 from okc_k_lines_b
1188 where id=p_cle_id
1189 and cle_id is null;
1190
1191 l_top_line number;
1192 --
1193 l_signed varchar2(30);
1194 --
1195 cursor c_signed is
1196 select code
1197 from okc_statuses_v
1198 where ste_code='SIGNED'
1199 and default_yn='Y';
1200 --
1201 l_expired varchar2(30);
1202 --
1203 cursor c_expired is
1204 select code
1205 from okc_statuses_v
1206 where ste_code='EXPIRED'
1207 and default_yn='Y';
1208 --
1209
1210 CURSOR version_csr(p_chr_id NUMBER) IS
1211 SELECT to_char (major_version)||'.'||to_char(minor_version)
1212 FROM okc_k_vers_numbers
1213 WHERE chr_id=p_chr_id;
1214
1215 --
1216
1217 CURSOR get_scs_code_csr IS
1218 SELECT scs_code
1219 FROM okc_k_headers_b
1220 WHERE id= p_id;
1221
1222 --
1223
1224 Type c_lines_cur is REF CURSOR;
1225 c_lines c_lines_cur;
1226
1227 PROCEDURE init_table(x_clev_tbl out NOCOPY clev_tbl_type) IS
1228 g_clev_tbl clev_tbl_type;
1229 BEGIN
1230 x_clev_tbl := g_clev_tbl;
1231 END;
1232
1233 Begin
1234
1235 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1236 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1237 '600: Entered UPDATE_LINE_STATUS');
1238 END IF;
1239
1240 if (p_init_msg_list = FND_API.G_TRUE) then
1241 fnd_msg_pub.initialize();
1242 end if;
1243
1244 if (p_count = 0) then
1245 p_count := 1;
1246 end if;
1247
1248 open c_type;
1249 fetch c_type into l_type;
1250 close c_type;
1251
1252 open c_old_type;
1253 fetch c_old_type into l_old_type;
1254 close c_old_type;
1255
1256 OPEN c_top_line_chk;
1257 FETCH c_top_line_chk INTO l_top_line;
1258 CLOSE c_top_line_chk;
1259
1260 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1261 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1262 '610: value of l_type - '|| l_type );
1263 END IF;
1264
1265 If (l_type='ACTIVE') then
1266 open c_signed;
1267 fetch c_signed into l_signed;
1268 close c_signed;
1269 open c_expired;
1270 fetch c_expired into l_expired;
1271 close c_expired;
1272 End If;
1273 --
1274 x_num := 0;
1275 init_table(l_clev_tbl);
1276
1277
1278 if (p_cle_id is null) then
1279
1280 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1281 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1282 '620: p_cle_id is null');
1283 END IF;
1284 -- Added the following IF ELSE condition and modified the cursor for bug # 6144856 --
1285 IF ( l_type='HOLD' or l_type='CANCELLED') THEN
1286
1287 open c_lines for select L.id, decode(l_type,'ACTIVE',
1288 decode(sign(months_between(sysdate-1,NVL(L.end_date,sysdate))),-1,
1289 decode(sign(months_between(L.start_date-1,sysdate)),-1,p_new_sts_code,l_signed)
1290 ,l_expired)
1291 ,p_new_sts_code) code, L.lse_id,
1292 L.object_version_number
1293 from okc_k_lines_v L, okc_statuses_v ls
1294 where L.dnz_chr_id = p_id
1295 and ls.code = L.sts_code
1296 and ls.ste_code in (l_old_type,'SIGNED')
1297 and NVL(L.term_cancel_source,'MANUAL') NOT IN ('IBTRANSFER', 'IBRETURN', 'IBTERMINATE', 'IBREPLACE'); --To ignore lines/sublines due to IBTRANSFER, IBRETURN, IBTERMINATE, IBREPLACE
1298
1299 ELSE
1300
1301 open c_lines for select L.id, decode(l_type,'ACTIVE',
1302 decode(sign(months_between(sysdate-1,NVL(L.end_date,sysdate))),-1,
1303 decode(sign(months_between(L.start_date-1,sysdate)),-1,p_new_sts_code,l_signed)
1304 ,l_expired)
1305 ,p_new_sts_code) code, L.lse_id,
1306 L.object_version_number
1307 from okc_k_lines_v L, okc_statuses_v ls
1308 where L.dnz_chr_id = p_id
1309 and ls.code = L.sts_code
1310 and ls.ste_code in (l_type,l_old_type)
1311 and NVL(L.term_cancel_source,'MANUAL') NOT IN ('IBTRANSFER', 'IBRETURN', 'IBTERMINATE', 'IBREPLACE'); --To ignore lines/sublines due to IBTRANSFER, IBRETURN, IBTERMINATE, IBREPLACE
1312
1313 END IF;
1314 -- end of bug# 6144856 --
1315
1316 else --No need of checking the status here as we know this action is possible only for 'Entered' or 'Cancelled' lines
1317
1318 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1319 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1320 '630: p_cle_id is NOT null - '||p_cle_id );
1321 END IF;
1322
1323 -- Added the following IF ELSE condition and modified the cursor for bug # 6144856 --
1324
1325 IF (l_top_line is not null) THEN
1326 IF (l_type = 'ENTERED' and l_old_type = 'CANCELLED' ) THEN
1327 --Query modified as part of bugfix for 6525864
1328 open c_lines for select L.id,p_new_sts_code code, L.lse_id,L.object_version_number
1329 from okc_k_lines_b L
1330 where L.dnz_chr_id = p_id
1331 and (L.id = p_cle_id or
1332 L.cle_id = p_cle_id)
1333 and EXISTS (select 'x'
1334 from okc_statuses_b
1335 where code = l.sts_code
1336 and ste_code = 'CANCELLED')
1337 and NVL(L.term_cancel_source,'MANUAL') NOT IN ('IBTRANSFER', 'IBRETURN', 'IBTERMINATE', 'IBREPLACE'); --To ignore lines/sublines due to IBTRANSFER, IBRETURN, IBTERMINATE, IBREPLACE
1338
1339 ELSE
1340 --Query modified as part of bugfix for 6525864
1341 open c_lines for select L.id, p_new_sts_code code, L.lse_id,L.object_version_number
1342 from okc_k_lines_b L
1343 where L.dnz_chr_id = p_id
1344 and (L.id = p_cle_id or
1345 L.cle_id = p_cle_id)
1346 and EXISTS( select 'x'
1347 from OKC_STATUSES_B
1348 where code = l.sts_code
1349 and ste_code = l_old_type)
1350 and NVL(L.term_cancel_source,'MANUAL') NOT IN ('IBTRANSFER', 'IBRETURN', 'IBTERMINATE', 'IBREPLACE'); --To ignore lines/sublines due to IBTRANSFER, IBRETURN, IBTERMINATE, IBREPLACE
1351
1352
1353 END IF;
1354 ELSE
1355 open c_lines for select L.id, p_new_sts_code code, L.lse_id,L.object_version_number
1356 from okc_k_lines_b L
1357 where L.id = p_cle_id
1358 and NVL(L.term_cancel_source,'MANUAL') NOT IN ('IBTRANSFER', 'IBRETURN', 'IBTERMINATE', 'IBREPLACE'); --To ignore lines/sublines due to IBTRANSFER, IBRETURN, IBTERMINATE, IBREPLACE
1359 END IF;
1360
1361 -- end of bug# 6144856 --
1362
1363 end if;
1364
1365 LOOP
1366
1367 FETCH c_lines BULK COLLECT INTO
1368 l_id_Tbl, l_code_tbl, l_lse_tbl,l_obj_ver_tbl
1369 LIMIT 1000 ;
1370 IF (l_id_Tbl.COUNT < 1) THEN
1371 EXIT;
1372 END IF;
1373
1374 IF (l_id_Tbl.COUNT > 0) THEN
1375
1376 FOR i IN l_id_Tbl.FIRST .. l_id_Tbl.LAST LOOP
1377
1378 l_clev_tbl(x_num).id := l_id_Tbl(i);
1379 l_clev_tbl(x_num).sts_code := l_code_Tbl(i);
1380 l_clev_tbl(x_num).lse_id := l_lse_tbl(i);
1381 l_clev_tbl(x_num).object_version_number := l_obj_ver_tbl(i); -- for bug 5710909
1382 -- To prevent Action Assembler from being called. Changes is line status
1383 -- will always result from a change in the header status
1384 --
1385 l_clev_tbl(x_num).VALIDATE_YN := 'N';
1386
1387 l_clev_tbl(x_num).call_action_asmblr := 'N';
1388
1389 --CGOPINEE BUGFIX FOR BUG9259068
1390 IF OKS_CHANGE_STATUS_PVT.G_HEADER_STATUS_CHANGED <> 'Y' THEN
1391 l_clev_tbl(x_num).call_action_asmblr := 'Y';
1392 END IF;
1393
1394 if((p_validate_status = 'Y') and (p_cle_id IS NOT NULL)) then
1395
1396 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1397 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1398 '640: Calling validate_status ' || l_clev_tbl(x_num).id);
1399 END IF;
1400
1401 VALIDATE_STATUS( x_return_status,
1402 x_msg_count,
1403 x_msg_data,
1404 p_id,
1405 p_new_ste_code,
1406 p_old_ste_code,
1407 p_new_sts_code,
1408 l_clev_tbl(x_num).sts_code,
1409 l_clev_tbl(x_num).id,
1410 p_validate_status);
1411
1412 if (x_return_status = FND_API.G_RET_STS_ERROR) then
1413 Raise FND_API.G_EXC_ERROR;
1414 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1415 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1416 elsif (x_return_status = OKC_API.G_RET_STS_WARNING) then
1417 Raise OKC_API.G_EXC_WARNING;
1418 end if;
1419 end if; -- l_clev_tbl(x_num).lse_id
1420
1421 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1422 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1423 '640: Completed validate_status ' || l_clev_tbl(x_num).id);
1424 END IF;
1425
1426 l_clev_tbl(x_num).new_ste_code := p_new_ste_code ;
1427 l_clev_tbl(x_num).new_sts_code := p_new_sts_code;
1428 l_clev_tbl(x_num).old_ste_code := p_old_ste_code ;
1429 l_clev_tbl(x_num).old_sts_code := p_old_sts_code;
1430
1431 If p_new_ste_code = 'CANCELLED' then
1432
1433 l_clev_tbl(x_num).date_cancelled := p_date_cancelled;
1434 l_clev_tbl(x_num).trn_code := p_canc_reason_code;
1435 l_clev_tbl(x_num).term_cancel_source := p_term_cancel_source;
1436
1437 Elsif p_new_ste_code = 'ENTERED' THEN
1438
1439 l_clev_tbl(x_num).date_cancelled := NULL;
1440 l_clev_tbl(x_num).trn_code := NULL;
1441 l_clev_tbl(x_num).term_cancel_source := NULL;
1442
1443 End if;
1444
1445 x_num :=x_num+1;
1446
1447 END LOOP;
1448 END IF;
1449 exit when c_lines%NOTFOUND;
1450
1451 END LOOP;
1452 close c_lines;
1453
1454 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1455 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1456 '650: Calling OKC_CONTRACT_PUB.update_contract_line ');
1457 END IF;
1458
1459 --bug 5710909
1460 -- Added the following code to place a lock on contract lines.
1461 OKC_CONTRACT_PVT.lock_contract_line(
1462 p_api_version => 1.0,
1463 p_init_msg_list => 'T',
1464 x_return_status => x_return_status,
1465 x_msg_count => x_msg_count,
1466 x_msg_data => x_msg_data,
1467 p_clev_tbl => l_clev_tbl);
1468
1469 If (x_return_status = FND_API.G_RET_STS_ERROR) then
1470 Raise FND_API.G_EXC_ERROR;
1471 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1472 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1473 End if;
1474 -- end of bug 5710909
1475
1476 -- Call the API to update the contract line with
1477 -- new status.
1478 OKC_CONTRACT_PUB.update_contract_line(
1479 p_api_version => 1,
1480 P_INIT_MSG_LIST => 'T',
1481 p_restricted_update => 'T',
1482 x_return_status => x_return_status,
1483 x_msg_count => x_msg_count,
1484 x_msg_data => x_msg_data,
1485 p_clev_tbl => l_clev_tbl,
1486 x_clev_tbl => l1_clev_tbl);
1487
1488 If (x_return_status = FND_API.G_RET_STS_ERROR) then
1489 Raise FND_API.G_EXC_ERROR;
1490 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1491 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1492 End if;
1493
1494 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1495 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1496 '660: Succesfully completed OKC_CONTRACT_PUB.update_contract_line ');
1497 END IF;
1498
1499 -- Calling the API to Clean OR Relink the Contract Line if it was
1500 -- a renewed contract.
1501
1502 if(p_old_ste_code = 'ENTERED' and p_new_ste_code = 'CANCELLED') then
1503
1504 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1505 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1506 '670: calling okc_contract_pvt.Line_renewal_links, ENTERED => CANCELLED ');
1507 END IF;
1508
1509 OKC_CONTRACT_PVT.Line_Renewal_links (
1510 p_api_version => 1,
1511 x_return_status => x_return_status,
1512 x_msg_count => x_msg_count,
1513 x_msg_data => x_msg_data,
1514 p_target_chr_id => p_id,
1515 p_target_line_id => p_cle_id,
1516 clean_relink_flag => 'CLEAN') ;
1517
1518 If (x_return_status = FND_API.G_RET_STS_ERROR) then
1519 Raise FND_API.G_EXC_ERROR;
1520 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1521 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1522 End if;
1523
1524 elsif (p_old_ste_code = 'CANCELLED' and p_new_ste_code = 'ENTERED') then
1525
1526 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1527 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1528 '680: calling okc_contract_pvt.Line_renewal_links, CANCELLED => ENTERED ');
1529 END IF;
1530
1531 OKC_CONTRACT_PVT.Line_Renewal_links (
1532 p_api_version => 1,
1533 x_return_status => x_return_status,
1534 x_msg_count => x_msg_count,
1535 x_msg_data => x_msg_data,
1536 p_target_chr_id => p_id,
1537 p_target_line_id => p_cle_id,
1538 clean_relink_flag => 'RELINK');
1539
1540 If (x_return_status = FND_API.G_RET_STS_ERROR) then
1541 Raise FND_API.G_EXC_ERROR;
1542 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1543 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1544 End if;
1545
1546 end if; -- p_old_ste_code and p_new_ste_code
1547
1548 -- Call API to update the contract amount when status is changed from
1549 -- Entered to Cancel or vice versa
1550
1551 -- Code to find if the contract belongs to SERVICE CONTRACT (of type SERVICE, SUBSCRIPTION, WARRANTY)
1552 -- If contract doesn't belong to service contract (as in case of OKL and OKE)
1553 -- procedure update_contract_amount is not called for lines/sublines of the contract
1554
1555 Open get_scs_code_csr;
1556 Fetch get_scs_code_csr Into l_scs_code;
1557 Close get_scs_code_csr;
1558
1559 IF (l_scs_code IN ('SERVICE', 'SUBSCRIPTION', 'WARRANTY') ) THEN
1560
1561 if (p_cle_id is NOT NULL) then
1562 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1563 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1564 '690: calling okc_contract_pvt.update_contract_amount, p_cle_id is not null '||p_cle_id);
1565 END IF;
1566
1567 OKC_CONTRACT_PVT.Update_contract_amount(
1568 p_api_version => 1,
1569 p_init_msg_list => 'F',
1570 p_id => p_id,
1571 p_from_ste_code => p_old_ste_code,
1572 p_to_ste_code => p_new_ste_code,
1573 p_cle_id => p_cle_id,
1574 x_return_status => x_return_status,
1575 x_msg_count => x_msg_count,
1576 x_msg_data => x_msg_data);
1577
1578
1579 If (x_return_status = FND_API.G_RET_STS_ERROR) then
1580 Raise FND_API.G_EXC_ERROR;
1581 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1582 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1583 End if;
1584
1585 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1586 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1587 '695: calling update_contract_tax_amount, p_cle_id is not null '||p_cle_id);
1588 END IF;
1589
1590 UPDATE_CONTRACT_TAX_AMOUNT(
1591 p_api_version => 1,
1592 p_init_msg_list => 'F',
1593 p_id => p_id,
1594 p_from_ste_code => p_old_ste_code,
1595 p_to_ste_code => p_new_ste_code,
1596 p_cle_id => p_cle_id,
1597 x_return_status => x_return_status,
1598 x_msg_count => x_msg_count,
1599 x_msg_data => x_msg_data);
1600
1601
1602 If (x_return_status = FND_API.G_RET_STS_ERROR) then
1603 Raise FND_API.G_EXC_ERROR;
1604 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1605 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1606 End if;
1607
1608 else
1609
1610 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1611 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1612 '700: calling okc_contract_pvt.update_contract_amount, p_cle_id is null ');
1613 END IF;
1614
1615 OKC_CONTRACT_PVT.Update_contract_amount(
1616 p_api_version => 1,
1617 p_init_msg_list => 'F',
1618 p_id => p_id,
1619 p_from_ste_code => p_old_ste_code,
1620 p_to_ste_code => p_new_ste_code,
1621 p_cle_id => NULL,
1622 x_return_status => x_return_status,
1623 x_msg_count => x_msg_count,
1624 x_msg_data => x_msg_data);
1625
1626 If (x_return_status = FND_API.G_RET_STS_ERROR) then
1627 Raise FND_API.G_EXC_ERROR;
1628 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1629 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1630 End if;
1631
1632
1633 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1634 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1635 '705: calling update_contract_tax_amount, p_cle_id is null');
1636 END IF;
1637
1638 UPDATE_CONTRACT_TAX_AMOUNT (
1639 p_api_version => 1,
1640 p_init_msg_list => 'F',
1641 p_id => p_id,
1642 p_from_ste_code => p_old_ste_code,
1643 p_to_ste_code => p_new_ste_code,
1644 p_cle_id => NULL,
1645 x_return_status => x_return_status,
1646 x_msg_count => x_msg_count,
1647 x_msg_data => x_msg_data);
1648
1649 If (x_return_status = FND_API.G_RET_STS_ERROR) then
1650 Raise FND_API.G_EXC_ERROR;
1651 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1652 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1653 End if;
1654
1655
1656 end if;
1657
1658 -- Call API to update the Billing schedule if it exists, this
1659 -- will update the schedule only if the status change action
1660 -- is taken on the subline.
1661
1662 if (p_cle_id is NOT NULL) then
1663
1664 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1665 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1666 '710: calling oks_bill_sch.sts_change_subline_lvl_rule');
1667 END IF;
1668
1669 OKS_BILL_SCH.Sts_change_subline_lvl_rule(
1670 p_cle_id => p_cle_id,
1671 p_from_ste_code => p_old_ste_code,
1672 p_to_ste_code => p_new_ste_code,
1673 x_return_status => x_return_status,
1674 x_msg_count => x_msg_count,
1675 x_msg_data => x_msg_count);
1676
1677 If (x_return_status = FND_API.G_RET_STS_ERROR) then
1678 Raise FND_API.G_EXC_ERROR;
1679 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1680 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1681 End if;
1682
1683
1684
1685
1686
1687
1688
1689 end if;
1690
1691 END IF; -- (l_scs_code IN ('SERVICE', 'SUBSCRIPTION', 'WARRANTY') )
1692
1693 -- Call API to Create History record for the
1694 -- Lines or Sublines when change status action
1695 -- is taken.
1696
1697 if (p_cle_id Is NOT NULL) then
1698 l_hstv_rec.chr_id := p_id;
1699 l_hstv_rec.cle_id := p_cle_id;
1700 l_hstv_rec.sts_code_from := p_old_sts_code;
1701 l_hstv_rec.sts_code_to := p_new_sts_code;
1702 l_hstv_rec.reason_code := p_canc_reason_code;
1703 l_hstv_rec.opn_code := 'STS_CHG';
1704 l_hstv_rec.comments := p_comments;
1705 /*Added for bug 14137343 */
1706 If p_term_cancel_source ='MANUAL' then
1707 l_hstv_rec.manual_yn :='Y' ;
1708 End If;
1709 /*Added for bug 14137343 */
1710
1711
1712 open version_csr(p_id);
1713 fetch version_csr into l_version;
1714 close version_csr;
1715
1716 l_hstv_rec.contract_version := l_version;
1717
1718 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1719 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1720 '720: calling okc_k_history_pvt.create_k_history, l_version - '||l_version);
1721 END IF;
1722
1723 OKC_K_HISTORY_PVT.create_k_history(
1724 p_api_version => 1,
1725 p_init_msg_list => 'F',
1726 x_return_status => x_return_status,
1727 x_msg_count => x_msg_count,
1728 x_msg_data => x_msg_data,
1729 p_hstv_rec => l_hstv_rec,
1730 x_hstv_rec => x_hstv_rec);
1731
1732 If (x_return_status = FND_API.G_RET_STS_ERROR) then
1733 Raise FND_API.G_EXC_ERROR;
1734 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1735 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1736 End if;
1737 end if; -- call history API
1738
1739 x_return_status := FND_API.G_RET_STS_SUCCESS;
1740
1741 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1742 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1743 '730: succesfully complete update_line_status ');
1744 END IF;
1745
1746 EXCEPTION
1747 WHEN FND_API.G_EXC_ERROR THEN
1748 x_return_status := FND_API.G_RET_STS_ERROR;
1749 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1750 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'740: Leaving OKS_CHANGE_STATUS_PVT : FND_API.G_EXC_ERROR');
1751 END IF;
1752 if (c_lines%ISOPEN) then
1753 close c_lines;
1754 end if;
1755
1756 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1757 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1758
1759 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1760 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'750: Leaving OKS_CHANGE_STATUS_PVT : FND_API.G_EXC_UNEXPECTED_ERROR '||SQLERRM);
1761 END IF;
1762
1763 if (c_lines%ISOPEN) then
1764 close c_lines;
1765 end if;
1766
1767 WHEN OKC_API.G_EXC_WARNING then
1768 x_return_status := OKC_API.G_RET_STS_WARNING;
1769
1770 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1771 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'760: Leaving OKS_CHANGE_STATUS_PVT : OKC_API.G_EXC_WARNING');
1772 END IF;
1773
1774 if(c_lines%ISOPEN) then
1775 close c_lines;
1776 end if;
1777
1778 WHEN OTHERS THEN
1779 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1780 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'770: Leaving OKS_CHANGE_STATUS_PVT because of EXCEPTION: '||sqlerrm);
1781 END IF;
1782
1783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1784 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1785 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1786 END IF;
1787 if (c_lines%ISOPEN) then
1788 close c_lines;
1789 end if;
1790 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1791 end;
1792
1793 procedure check_allowed_status( x_return_status OUT NOCOPY VARCHAR2,
1794 x_msg_count OUT NOCOPY NUMBER,
1795 x_msg_data OUT NOCOPY VARCHAR2,
1796 p_id IN NUMBER,
1797 p_cle_id IN NUMBER,
1798 p_new_sts_code IN VARCHAR2,
1799 p_old_sts_code IN OUT NOCOPY VARCHAR2,
1800 p_old_ste_code IN OUT NOCOPY VARCHAR2,
1801 p_new_ste_code IN OUT NOCOPY VARCHAR2)
1802 IS
1803
1804 l_new_ste_code varchar2(30);
1805 l_old_ste_code varchar2(30);
1806 l_old_sts_code varchar2(30);
1807 l_new_sts_code varchar2(30);
1808 l_start_date Date;
1809 l_end_date Date;
1810 l_allowed_status Varchar2(1) := 'N';
1811 l_api_name Varchar2(100) := 'Check_Allowed_Status';
1812
1813 cursor get_ste_code(p_code in varchar2) is
1814 Select ste_code from okc_statuses_b where code = p_code;
1815
1816 cursor get_k_hdr_cur (p_id in number) is
1817 Select sts_code, start_date, end_date from okc_k_headers_b where id = p_id;
1818
1819 cursor get_k_line_cur(p_cle_id in number) is
1820 Select sts_code, start_date, end_date from okc_k_lines_b where id = p_id;
1821
1822 begin
1823
1824 l_new_sts_code := p_new_sts_code;
1825
1826 if (p_cle_id is NULL) then
1827 open get_k_hdr_cur(p_id);
1828 fetch get_k_hdr_cur into l_old_sts_code, l_start_date, l_end_date;
1829 close get_k_hdr_cur;
1830 else
1831 open get_k_line_cur(p_cle_id);
1832 fetch get_k_line_cur into l_old_sts_code, l_start_date, l_end_date;
1833 close get_k_line_cur;
1834 end if;
1835
1836 if (p_old_sts_code is NOT NULL) then
1837 l_old_sts_code := p_old_sts_code;
1838 end if;
1839
1840 if (p_old_ste_code is NULL) then
1841 open get_ste_code (l_old_sts_code);
1842 fetch get_ste_code into l_old_ste_code;
1843 close get_ste_code;
1844 p_old_ste_code := l_old_ste_code;
1845 else
1846 l_old_ste_code :=p_old_ste_code;
1847 end if;
1848
1849
1850
1851 if (p_new_ste_code is NULL) then
1852 open get_ste_code (l_new_sts_code);
1853 fetch get_ste_code into l_new_ste_code;
1854 close get_ste_code;
1855 p_new_ste_code := l_new_ste_code;
1856 else
1857 l_new_ste_code := p_new_ste_code;
1858 end if;
1859
1860 /* skuchima bug12418261 */
1861 If ( p_old_ste_code = 'EXPIRED' AND l_new_ste_code <> 'EXPIRED' ) then
1862 fnd_message.set_name('OKS','OKS_CHANGE_STAT_NOT_ALLOWED'); -- Add a message name here and throw a exception
1863 fnd_msg_pub.add();
1864 raise FND_API.G_EXC_ERROR;
1865 end if;
1866
1867 select 'Y' INTO l_allowed_status from dual where (l_new_sts_code,l_new_ste_code) in
1868 (select
1869 S.CODE STATUS_CODE
1870 ,S.STE_CODE STE_CODE
1871 from
1872 okc_statuses_v S
1873 ,fnd_lookups ST
1874 where
1875 S.STE_CODE in
1876 (
1877 NVL(l_old_ste_code,'ENTERED')
1878 ,decode(l_old_ste_code,
1879 NULL, 'CANCELLED',
1880 'ENTERED','CANCELLED',
1881 'ACTIVE','HOLD',
1882 'SIGNED','HOLD',
1883 'HOLD',decode(
1884 NVL(sign(months_between
1885 (l_start_date,sysdate+1)),1),
1886 -1,decode(
1887 NVL(sign(months_between(l_end_date,sysdate-1)),
1888 1),1,'ACTIVE'
1889 ,'EXPIRED'),'SIGNED')))
1890 and sysdate between s.start_date and nvl(s.end_date,sysdate)
1891 and st.lookup_type='OKC_STATUS_TYPE'
1892 and st.lookup_code=s.ste_code
1893 and sysdate between st.start_date_active and
1894 nvl(st.end_date_active,sysdate)
1895 and ST.enabled_flag='Y'
1896 and S.code<>NVL(l_old_sts_code,'ENTERED')
1897 and l_old_sts_code not like 'QA%HOLD'
1898 and S.code not like 'QA%HOLD'
1899 AND l_old_ste_code <> 'CANCELLED'
1900 UNION ALL
1901 SELECT S.CODE STATUS_CODE
1902 ,S.STE_CODE STE_CODE1
1903 FROM OKC_STATUSES_V S
1904 ,FND_LOOKUPS ST
1905 WHERE S.STE_CODE in ('ENTERED', 'CANCELLED')
1906 AND SYSDATE BETWEEN S.START_DATE AND NVL(S.END_DATE, SYSDATE)
1907 AND ST.LOOKUP_TYPE = 'OKC_STATUS_TYPE'
1908 AND ST.LOOKUP_CODE=S.STE_CODE
1909 AND SYSDATE BETWEEN ST.START_DATE_ACTIVE AND NVL(ST.END_DATE_ACTIVE, SYSDATE)
1910 AND ST.ENABLED_FLAG = 'Y'
1911 AND S.code <> l_old_sts_code
1912 AND l_old_ste_code='CANCELLED');
1913
1914 x_return_status := FND_API.G_RET_STS_SUCCESS;
1915
1916 exception
1917
1918 WHEN FND_API.G_EXC_ERROR then
1919 x_return_status := FND_API.G_RET_STS_ERROR;
1920 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1921 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving OKS_CHANGE_STATUS_PVT : FND_API.G_EXC_ERROR');
1922 END IF;
1923
1924 WHEN NO_DATA_FOUND then
1925 x_return_status := FND_API.G_RET_STS_ERROR;
1926 FND_MESSAGE.set_name('OKS','OKS_CHANGE_STAT_NOT_ALLOWED'); -- set the message here
1927 fnd_msg_pub.add;
1928
1929 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1930 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'510: Leaving OKS_CHANGE_STATUS_PVT : NO_DATA_FOUND');
1931 END IF;
1932
1933 WHEN OTHERS then
1934 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1935 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1936 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'520: Leaving OKS_CHANGE_STATUS_PVT because of EXCEPTION: '||sqlerrm);
1937 END IF;
1938
1939 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1940 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name, SQLERRM );
1941 END IF;
1942
1943 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1944
1945 end check_allowed_status;
1946
1947
1948 --[llc] Update Contract Tax Amount
1949
1950 /*
1951 The Header and Line Tax Amounts should be updated when Change Status action is taken
1952 at the header/line/subline level. This is to ensure that the new calcualated Tax Amount
1953 ignores cancelled top lines/sublines.
1954
1955 A new procedure Update_Contract_Tax_Amount is created which will be called when Change Status
1956 action is taken on the header/line/subline level and after Update_Contract_Amount has
1957 been called for the same.
1958
1959 */
1960
1961
1962 PROCEDURE UPDATE_CONTRACT_TAX_AMOUNT (
1963 p_api_version IN NUMBER,
1964 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1965 p_id IN NUMBER,
1966 p_from_ste_code IN VARCHAR2,
1967 p_to_ste_code IN VARCHAR2,
1968 p_cle_id IN NUMBER,
1969 x_return_status OUT NOCOPY VARCHAR2,
1970 x_msg_count OUT NOCOPY NUMBER,
1971 x_msg_data OUT NOCOPY VARCHAR2 )
1972
1973 IS
1974
1975 l_cle_id Number := NULL;
1976 l_sub_line_tax_amt Number := NULL;
1977 l_lse_id Number := NULL;
1978 l_hdr_tax_amt Number := NULL;
1979
1980 l_msg_count NUMBER;
1981 l_msg_data VARCHAR2(2000);
1982 l_return_status VARCHAR2(1):='S';
1983
1984 g_rail_rec OKS_TAX_UTIL_PVT.ra_rec_type;
1985 l_Tax_Value g_rail_rec.TAX_VALUE%TYPE;
1986
1987 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_TAX_AMOUNT';
1988 g_cle_id Number; /*Added for bug:8775250*/
1989
1990
1991
1992 --Cursor to get topline id for a particular subline; For a topline this will return NULL
1993
1994 Cursor get_line_lvl_csr is
1995 Select cle_id
1996 from okc_k_lines_b
1997 where id = p_cle_id
1998 and dnz_chr_id = p_id;
1999
2000
2001 --Cursor to fetch tax_amount for a particular subline
2002
2003 Cursor get_subline_tax_amt_csr (p_cle_id NUMBER) IS
2004 Select sle.tax_amount
2005 from okc_k_lines_b cle, oks_k_lines_b sle
2006 where cle.id = p_cle_id
2007 and cle.dnz_chr_id = p_id
2008 and cle.id = sle.cle_id
2009 and cle.dnz_chr_id = sle.dnz_chr_id;
2010
2011 --Cursor to add tax_amount of all the toplines
2012
2013 Cursor get_hdr_tax_amt_csr IS
2014 select nvl(sum(nvl(tax_amount,0)),0)
2015 from okc_k_lines_b cle, oks_k_lines_b sle
2016 where cle.dnz_chr_id = p_id
2017 and cle.lse_id in (1, 12, 14, 19, 46)
2018 and cle.cle_id is null
2019 and cle.id = sle.cle_id
2020 and cle.dnz_chr_id = sle.dnz_chr_id;
2021
2022
2023 --Cursor to fectch lse_id of topline
2024
2025 Cursor get_lse_id_csr (p_cle_id NUMBER) IS
2026 select lse_id
2027 from okc_k_lines_b
2028 where id=p_cle_id;
2029
2030
2031 --/*Added for bug:8775250*/
2032 Cursor get_sub_tax_amt_csr (p_cle_id NUMBER) IS
2033 Select cle.id,sle.tax_amount
2034 from okc_k_lines_b cle, oks_k_lines_b sle
2035 where cle.cle_id = p_cle_id
2036 and cle.dnz_chr_id = p_id
2037 and cle.lse_id in (7, 8, 9, 10, 11, 13, 18, 25, 35)
2038 and cle.term_cancel_source IN ('MANUAL','CUSTOMER') ---modified condition for bug 12956286
2039 and cle.id = sle.cle_id
2040 and cle.dnz_chr_id = sle.dnz_chr_id;
2041
2042 Cursor get_subline_csr(p_cle_id NUMBER) IS
2043 Select okslb.id,okslb.cancelled_amount
2044 from okc_k_lines_b cle,
2045 okc_k_lines_b okslb
2046 where cle.id = p_cle_id
2047 and okslb.lse_id in (7, 8, 9, 10, 11, 13, 18, 25, 35)
2048 and okslb.cle_id =cle.id
2049 and okslb.date_cancelled is null;
2050
2051 Cursor get_lines_id(p_id number) IS
2052 select oklb.id,oklb.price_negotiated
2053 from okc_k_lines_b oklb,
2054 okc_k_headers_all_b okhb
2055 where oklb.chr_id = okhb.id
2056 and okhb.id = p_id
2057 and oklb.lse_id in (1,12,14,19,46);
2058
2059
2060 BEGIN
2061
2062 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2063 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2064 '800: Entered UPDATE_CONTRACT_TAX_AMOUNT ');
2065 END IF;
2066
2067 IF ((p_from_ste_code is NULL) OR (p_to_ste_code is NULL) OR (p_id is null)) THEN
2068 raise FND_API.G_EXC_ERROR;
2069 END IF;
2070
2071 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2072 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2073 '810: Parameter Values ' ||
2074 'p_id - '|| p_id ||
2075 'p_from_ste_code - '||p_from_ste_code ||
2076 'p_to_ste_code - '||p_to_ste_code ||
2077 'p_cle_id- '||p_cle_id );
2078 END IF;
2079
2080 IF (p_cle_id is NOT NULL) THEN -- implies line or sub-line level
2081
2082 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2083 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2084 '900: p_cle_id is not null; Change Status called from line/subline level ');
2085 END IF;
2086
2087 Open get_line_lvl_csr;
2088 Fetch get_line_lvl_csr into l_cle_id;
2089 Close get_line_lvl_csr;
2090
2091 IF (l_cle_id is NOT NULL) THEN --p_cle_id is a subline
2092
2093 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2094 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2095 '910: Updating tax_amount of topline of the subline due to status change of the subline ');
2096 END IF;
2097
2098 Open get_subline_tax_amt_csr(p_cle_id);
2099 Fetch get_subline_tax_amt_csr into l_sub_line_tax_amt;
2100 Close get_subline_tax_amt_csr;
2101
2102 IF ((p_from_ste_code = 'ENTERED' ) AND (p_to_ste_code = 'CANCELLED')) THEN
2103
2104 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2105 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2106 '920: ENTERED -> CANCELLED; Updating tax_amount of the topline of the subline ');
2107 END IF;
2108
2109 -- updating tax_amount for the topline (l_cle_id) of this subline (p_cle_id)
2110
2111 Update oks_k_lines_b
2112 set tax_amount= nvl(tax_amount, 0) - l_sub_line_tax_amt
2113 where dnz_chr_id = p_id
2114 and cle_id = l_cle_id;
2115
2116 --Bug:6765336 Updating the subline when it is cancelled
2117 Update oks_k_lines_b
2118 set tax_amount= Nvl(tax_amount, 0) - l_sub_line_tax_amt
2119 Where cle_id = p_cle_id
2120 and dnz_chr_id = p_id;
2121 --Bug:6765336
2122
2123 ELSIF ((p_from_ste_code = 'CANCELLED' ) AND (p_to_ste_code = 'ENTERED')) THEN
2124
2125 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2126 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2127 '930: CANCELLED -> ENTERED; Updating tax_amount of the topline of the subline ');
2128 END IF;
2129 /*Added for bug:8775250*/
2130 g_cle_id :=p_cle_id;
2131
2132 OKS_TAX_UTIL_PVT.Get_Tax
2133 (
2134 p_api_version => 1.0,
2135 p_init_msg_list => OKC_API.G_TRUE,
2136 p_chr_id => p_id,
2137 p_cle_id => g_cle_id,
2138 px_rail_rec => g_rail_rec,
2139 x_msg_count => x_msg_count,
2140 x_msg_data => x_msg_data,
2141 x_return_status => x_return_status
2142 );
2143
2144 Update oks_k_lines_b
2145 set tax_amount= nvl(g_rail_rec.TAX_VALUE,0)
2146 where dnz_chr_id = p_id
2147 and cle_id = g_cle_id;
2148
2149 -- updating tax_amount for the topline (l_cle_id) of this subline (p_cle_id)
2150
2151 Update oks_k_lines_b
2152 set tax_amount= nvl(tax_amount, 0) + nvl(g_rail_rec.TAX_VALUE,0)
2153 where dnz_chr_id = p_id
2154 and cle_id = l_cle_id;
2155
2156 END IF; -- p_to_ste_code ='CANCELLED'
2157
2158 ELSE --l_cle_id is NULL --p_cle_id is a top line
2159
2160
2161 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2162 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2163 '1000: Updating tax_amount of the topline');
2164 END IF;
2165
2166 IF ((p_from_ste_code = 'ENTERED') AND (p_to_ste_code = 'CANCELLED')) THEN
2167
2168 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2169 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2170 '1010: ENTERED -> CANCELLED; Updating tax_amount of topline ');
2171 END IF;
2172
2173 -- updating tax_amount for the topline (p_cle_id)
2174
2175 Update oks_k_lines_b
2176 set tax_amount= 0
2177 where dnz_chr_id = p_id
2178 and cle_id = p_cle_id;
2179
2180 FOR get_sub_tax_amt_csr_rec IN get_sub_tax_amt_csr(p_cle_id)
2181 LOOP
2182 Update oks_k_lines_b
2183 set tax_amount= Nvl(tax_amount, 0) - get_sub_tax_amt_csr_rec.tax_amount
2184 Where cle_id = get_sub_tax_amt_csr_rec.id
2185 and dnz_chr_id = p_id;
2186 END LOOP;
2187
2188
2189 ELSIF ((p_from_ste_code = 'CANCELLED' ) AND (p_to_ste_code = 'ENTERED')) THEN
2190
2191 -- Opening cursor to get the lse_id of the top line
2192
2193 Open get_lse_id_csr (p_cle_id);
2194 Fetch get_lse_id_csr into l_lse_id;
2195 Close get_lse_id_csr;
2196
2197 IF (l_lse_id = 46 ) THEN --Checking if top line is of SUBSCRIPTION type
2198
2199 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2200 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2201 '1020: Calling FUNCTION get_tax_for_subs_line: Contract ID ' || p_id || ' Topline ID '|| p_cle_id);
2202 END IF;
2203
2204 -- Calling function get_tax_for_subs_line to get tax_amount of this subscription line
2205
2206 l_Tax_Value := get_tax_for_subs_line (p_id, p_cle_id);
2207
2208 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2209 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2210 '1030: Successfully called FUNCTION get_tax_for_subs_line ');
2211 END IF;
2212
2213
2214 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2215 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2216 '1040: CANCELLED -> ENTERED; Updating tax_amount for SUBSCRIPTION topline ');
2217 END IF;
2218
2219 -- updating tax_amount for top line for SUBSCRIPTION line type due to change in the status of top line
2220
2221 Update oks_k_lines_b
2222 set tax_amount= l_Tax_Value
2223 where dnz_chr_id = p_id
2224 and cle_id = p_cle_id;
2225
2226
2227 ELSE -- top line is not of SUBSCRIPTION type
2228
2229 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2230 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2231 '1050: CANCELLED -> ENTERED; Updating tax_amount for NON-SUBSCRIPTION topline ');
2232 END IF;
2233
2234 FOR get_subline_csr_rec IN get_subline_csr(p_cle_id)
2235 LOOP
2236 g_rail_rec.amount:=Null;
2237 g_cle_id := get_subline_csr_rec.id;
2238
2239 OKS_TAX_UTIL_PVT.Get_Tax
2240 (
2241 p_api_version => 1.0,
2242 p_init_msg_list => OKC_API.G_TRUE,
2243 p_chr_id => p_id,
2244 p_cle_id => g_cle_id,
2245 px_rail_rec => g_rail_rec,
2246 x_msg_count => x_msg_count,
2247 x_msg_data => x_msg_data,
2248 x_return_status => x_return_status
2249 );
2250
2251 Update oks_k_lines_b
2252 set tax_amount= Nvl(tax_amount, 0)+nvl(g_rail_rec.TAX_VALUE,0)
2253 Where cle_id = get_subline_csr_rec.id
2254 and dnz_chr_id = p_id;
2255 END LOOP;
2256 -- updating tax_amount for top line which is not of SUBSCRIPTION type
2257
2258 Update oks_k_lines_b
2259 set tax_amount=
2260 (Select nvl(sum(nvl(tax_amount,0)),0)
2261 from okc_k_lines_b cle, oks_k_lines_b sle
2262 where cle.cle_id = p_cle_id
2263 and cle.lse_id in (7,8,9,10,11,18,25,35)
2264 and cle.dnz_chr_id = p_id
2265 and cle.id = sle.cle_id
2266 and cle.dnz_chr_id = sle.dnz_chr_id
2267 and cle.date_cancelled is NULL -- Bug 5474479
2268 )
2269 where dnz_chr_id = p_id
2270 and cle_id = p_cle_id;
2271
2272
2273 END IF; -- l_lse_id = 46
2274
2275 END IF; -- (p_from_ste_code = 'ENTERED') AND (p_to_ste_code = 'CANCELLED')
2276
2277 END IF; -- l_cle_id is NOT NULL
2278
2279 ELSE -- p_cle_id is NULL --implies Change Status action is taken at header Level
2280
2281 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2282 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2283 '1100: Updating Header ');
2284 END IF;
2285
2286 IF ((p_from_ste_code = 'ENTERED') AND (p_to_ste_code = 'CANCELLED')) THEN
2287
2288 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2289 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2290 '1110: ENTERED -> CANCELLED; Updating tax_amount for all toplines of contract ');
2291 END IF;
2292
2293 FOR get_lines_id_rec IN get_lines_id(p_id)
2294 LOOP
2295 FOR get_sub_tax_amt_csr_rec IN get_sub_tax_amt_csr(get_lines_id_rec.ID)
2296 LOOP
2297 Update oks_k_lines_b
2298 set tax_amount= 0
2299 Where cle_id = get_sub_tax_amt_csr_rec.id
2300 and dnz_chr_id = p_id;
2301 END LOOP;
2302 END LOOP;
2303 -- updating tax_amount for all the top lines of the contract
2304
2305 update oks_k_lines_b
2306 set tax_amount = 0
2307 where dnz_chr_id = p_id
2308 and cle_id IN
2309 (select id
2310 from okc_k_lines_b
2311 where cle_id is null
2312 and dnz_chr_id = p_id
2313 and lse_id in (1, 12, 14, 19, 46)
2314 );
2315
2316 ELSIF ((p_from_ste_code = 'CANCELLED' ) AND (p_to_ste_code = 'ENTERED')) THEN
2317
2318 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2319 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2320 '1120: CANCELLED -> ENTERED; Updating tax_amount for all toplines of contract ');
2321 END IF;
2322
2323 FOR get_lines_id_rec IN get_lines_id(p_id)
2324 LOOP
2325 FOR get_subline_csr_rec IN get_subline_csr(get_lines_id_rec.ID)
2326 LOOP
2327 g_rail_rec.amount := Null;
2328 g_cle_id := get_subline_csr_rec.id;
2329
2330 OKS_TAX_UTIL_PVT.Get_Tax
2331 (
2332 p_api_version => 1.0,
2333 p_init_msg_list => OKC_API.G_TRUE,
2334 p_chr_id => p_id,
2335 p_cle_id => g_cle_id,
2336 px_rail_rec => g_rail_rec,
2337 x_msg_count => x_msg_count,
2338 x_msg_data => x_msg_data,
2339 x_return_status => x_return_status
2340 );
2341 Update oks_k_lines_b
2342 set tax_amount= Nvl(tax_amount, 0)+nvl(g_rail_rec.TAX_VALUE,0)
2343 Where cle_id = get_subline_csr_rec.id
2344 and dnz_chr_id = p_id;
2345
2346 END LOOP;
2347 END LOOP;
2348 -- updating tax_amount for all the top lines of the contract which are not of SUBSCRIPTION type
2349
2350 update oks_k_lines_b oks1
2351 set oks1.tax_amount =
2352 (
2353 select nvl(sum(nvl(tax_amount,0)),0)
2354 from oks_k_lines_b oks2, okc_k_lines_b okc2
2355 where oks2.cle_id = okc2.id
2356 and oks2.dnz_chr_id = okc2.dnz_chr_id
2357 and okc2.dnz_chr_id = p_id
2358 and okc2.cle_id = oks1.cle_id
2359 and okc2.date_cancelled IS NULL -- Bug 5474479.
2360 )
2361 where oks1.dnz_chr_id = p_id
2362 and oks1.cle_id IN
2363 (select id
2364 from okc_k_lines_b okc1
2365 where okc1.cle_id is null
2366 and okc1.lse_id in (1, 12, 14, 19) --removed 46 (subscription type)
2367 and okc1.dnz_chr_id = p_id
2368 and okc1.date_cancelled IS NULL -- Bug 5474479.
2369 );
2370
2371 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2372 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2373 '1130: Calling UPDATE_SUBSCRIPTION_TAX_AMOUNT ' || p_id);
2374 END IF;
2375
2376 --Calling procudure to update tax_amount for subscription line type of the contract, if any
2377
2378 UPDATE_SUBSCRIPTION_TAX_AMOUNT(
2379 p_api_version => 1,
2380 p_init_msg_list => 'F',
2381 p_id => p_id,
2382 x_return_status => l_return_status,
2383 x_msg_count => l_msg_count,
2384 x_msg_data => l_msg_data);
2385
2386 If (x_return_status = FND_API.G_RET_STS_ERROR) then
2387 Raise FND_API.G_EXC_ERROR;
2388 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
2389 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2390 End if;
2391
2392 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2393 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2394 '1140: Succesfully completed UPDATE_SUBSCRIPTION_TAX_AMOUNT ');
2395 END IF;
2396
2397 END IF; --(p_from_ste_code = 'ENTERED') AND (p_to_ste_code = 'CANCELLED')
2398
2399 END IF; --p_cle_id is NULL
2400
2401 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2402 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2403 '1200: Updating header tax_amount ');
2404 END IF;
2405
2406 -- updating tax_amount for header level of the contract due to change in the status of line/subline/contract
2407
2408 Open get_hdr_tax_amt_csr;
2409 Fetch get_hdr_tax_amt_csr Into l_hdr_tax_amt;
2410 Close get_hdr_tax_amt_csr;
2411
2412 Update OKS_K_headers_b
2413 set tax_amount = l_hdr_tax_amt
2414 Where chr_id = p_id;
2415
2416 ---
2417
2418 x_return_status := FND_API.G_RET_STS_SUCCESS;
2419
2420 ---
2421 Exception
2422
2423 WHEN FND_API.G_EXC_ERROR THEN
2424 x_return_status := FND_API.G_RET_STS_ERROR;
2425
2426 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2427 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1300: Leaving OKS_CHANGE_STATUS_PVT, one or more mandatory parameters missing :FND_API.G_EXC_ERROR');
2428 END IF;
2429
2430 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2431 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2432
2433 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2434 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1310: Leaving OKS_CHANGE_STATUS_PVT: FND_API.G_EXC_UNEXPECTED_ERROR '|| SQLERRM);
2435 END IF;
2436
2437 WHEN OTHERS THEN
2438 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2439
2440 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2441 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1320: Leaving OKS_CHANGE_STATUS_PVT because of EXCEPTION: '||sqlerrm);
2442 END IF;
2443
2444 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2445 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name, SQLERRM );
2446 END IF;
2447
2448 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2449
2450 END UPDATE_CONTRACT_TAX_AMOUNT;
2451
2452
2453 ---[llc] UPDATE_SUBSCRIPTION_TAX_AMOUNT updates all the tax_amount of toplines which are of type subscription
2454
2455 PROCEDURE UPDATE_SUBSCRIPTION_TAX_AMOUNT(
2456 p_api_version IN NUMBER,
2457 p_init_msg_list IN varchar2 default FND_API.G_FALSE,
2458 p_id IN NUMBER,
2459 x_return_status OUT NOCOPY VARCHAR2,
2460 x_msg_count OUT NOCOPY NUMBER,
2461 x_msg_data OUT NOCOPY VARCHAR2)
2462
2463 IS
2464
2465 Cursor get_K_subscription_lines IS
2466 select id
2467 from okc_k_lines_b
2468 where cle_id is null
2469 and lse_id = 46
2470 and dnz_chr_id = p_id;
2471
2472
2473 l_id_Tbl Num_Tbl_Type;
2474 l_subs_tax_Tbl Num_Tbl_Type;
2475 l_sub_tax_amt Number;
2476
2477 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SUBSCRIPTION_TAX_AMOUNT';
2478 --
2479 BEGIN
2480
2481 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2482 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2483 '1150: Entered UPDATE_SUBSCRIPTION_TAX_AMOUNT ');
2484 END IF;
2485
2486 Open get_K_subscription_lines;
2487
2488 LOOP
2489
2490 FETCH get_K_subscription_lines BULK COLLECT INTO
2491 l_id_Tbl LIMIT 1000 ;
2492
2493 IF (l_id_Tbl.COUNT < 1) THEN
2494 EXIT;
2495 END IF;
2496
2497 IF (l_id_Tbl.COUNT > 0) THEN
2498
2499 FOR i IN l_id_Tbl.FIRST .. l_id_Tbl.LAST
2500
2501 LOOP
2502
2503 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2504 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2505 '1155: Callling procedure get_tax_for_subs_line for topline '|| l_id_Tbl(i));
2506 END IF;
2507
2508 l_subs_tax_Tbl(i) := get_tax_for_subs_line (p_id, l_id_Tbl(i));
2509
2510
2511 END LOOP;
2512 END IF;
2513
2514 exit when get_K_subscription_lines%NOTFOUND;
2515
2516 END LOOP;
2517
2518 Close get_K_subscription_lines;
2519
2520 --
2521 IF (l_id_Tbl.COUNT > 0) THEN
2522
2523 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2524 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2525 '1160: Trying to bulk updat tax_amount of toplines of type subscription of contract '|| p_id);
2526 END IF;
2527
2528 FORALL I IN l_id_Tbl.FIRST .. l_id_Tbl.LAST
2529
2530 Update oks_k_lines_b
2531 set tax_amount= l_subs_tax_Tbl(I)
2532 where dnz_chr_id = p_id
2533 and cle_id = l_id_Tbl(I);
2534
2535 END IF;
2536
2537 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2538 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2539 '1165: Successfully bulk updated tax_amount of toplines of type subscription of contract '|| p_id);
2540 END IF;
2541
2542 ---
2543
2544 x_return_status := FND_API.G_RET_STS_SUCCESS;
2545
2546 ---
2547 Exception
2548
2549 WHEN FND_API.G_EXC_ERROR THEN
2550 x_return_status := FND_API.G_RET_STS_ERROR;
2551
2552 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2553 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1170: Leaving OKS_CHANGE_STATUS_PVT, one or more mandatory parameters missing :FND_API.G_EXC_ERROR');
2554 END IF;
2555
2556 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2558
2559 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2560 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1180: Leaving OKS_CHANGE_STATUS_PVT: FND_API.G_EXC_UNEXPECTED_ERROR '|| SQLERRM);
2561 END IF;
2562
2563 WHEN OTHERS THEN
2564 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2565
2566 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2567 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1190: Leaving OKS_CHANGE_STATUS_PVT because of EXCEPTION: '||sqlerrm);
2568 END IF;
2569
2570 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2571 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name, SQLERRM );
2572 END IF;
2573
2574 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2575
2576
2577 END UPDATE_SUBSCRIPTION_TAX_AMOUNT;
2578 ---
2579
2580 --[llc] get_tax_for_subs_line
2581
2582
2583 FUNCTION get_tax_for_subs_line (p_id in number, p_cle_id in number ) return NUMBER
2584 IS
2585
2586 x_msg_count NUMBER;
2587 x_msg_data varchar2(2000);
2588 l_return_status VARCHAR2(1);
2589
2590 g_rail_rec OKS_TAX_UTIL_PVT.ra_rec_type;
2591 l_Tax_Value g_rail_rec.TAX_VALUE%TYPE;
2592
2593 BEGIN
2594
2595
2596 OKS_TAX_UTIL_PVT.Get_Tax(
2597 p_api_version => 1.0,
2598 p_init_msg_list => 'F',
2599 p_chr_id => p_id,
2600 p_cle_id => p_cle_id,
2601 px_rail_rec => g_rail_rec,
2602 x_msg_count => x_msg_count,
2603 x_msg_data => x_msg_data,
2604 x_return_status => l_return_status);
2605
2606 If (l_return_status = FND_API.G_RET_STS_ERROR) then
2607 Raise FND_API.G_EXC_ERROR;
2608 elsif (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
2609 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2610 End if;
2611
2612
2613 If (l_return_status = 'S') then
2614 return nvl(g_rail_rec.TAX_VALUE,0);
2615 else
2616 return 0;
2617 End If;
2618
2619 END get_tax_for_subs_line;
2620
2621
2622 ---
2623
2624 end oks_change_status_pvt;
[Home] [Help]