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