[Home] [Help]
PACKAGE BODY: APPS.OKL_ACTIVATE_CONTRACT_PUB
Source
1 Package body OKL_ACTIVATE_CONTRACT_PUB AS
2 /* $Header: OKLPACOB.pls 120.26.12010000.2 2008/10/20 18:24:07 apaul ship $ */
3
4 PROCEDURE get_stream_id
5 (
6 p_khr_id IN okl_k_headers_full_v.id%TYPE,
7 p_stream_type_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_sty_id OUT NOCOPY okl_strm_type_b.ID%TYPE
10 )
11 IS
12
13 CURSOR pry_sty_csr IS
14 SELECT ID
15 FROM OKL_Strm_type_b
16 WHERE stream_type_purpose = p_stream_type_purpose;
17
18 l_sty_id NUMBER;
19
20 BEGIN
21
22 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
23
24 OPEN pry_sty_csr;
25 FETCH pry_sty_csr INTO l_sty_id;
26 IF pry_sty_csr%NOTFOUND THEN
27 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
28 p_msg_name => 'OKL_NO_PRY_STY_FOUND',
29 p_token1 => 'PURPOSE',
30 p_token1_value => p_stream_type_purpose);
31 RAISE Okl_Api.G_EXCEPTION_ERROR;
32 END IF;
33 CLOSE pry_sty_csr;
34
35 x_sty_id := l_sty_id;
36
37 EXCEPTION
38 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
39 IF pry_sty_csr%ISOPEN THEN
40 CLOSE pry_sty_csr;
41 END IF;
42 x_return_status := Okl_Api.G_RET_STS_ERROR ;
43
44 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
45 IF pry_sty_csr%ISOPEN THEN
46 CLOSE pry_sty_csr;
47 END IF;
48 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
49
50 WHEN OTHERS THEN
51 IF pry_sty_csr%ISOPEN THEN
52 CLOSE pry_sty_csr;
53 END IF;
54 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
55 --DBMS_OUTPUT.PUT_LINE(SQLERRM);
56
57 END get_stream_id;
58
59 --------------------------------------------------------------------------------
60 --Bug # 3310972
61 --Api Name : Is_Rebook_Supported
62 --Description : Local procedure will determine whether there is a change
63 -- in asset add-ons or adjustments during rebook. These changes
64 -- are not supported.
65 --Notes :
66 -- IN Parameters -
67 -- IN Parameters -
68 -- p_rbk_chr_id - contract id of rebook copy contract
69 -- p_orig_chr_id - contract id of original contract
70 -- OUT Parameter x_return_status - will return OKL_API.G_RET_STS_SUCCESS or
71 -- OKL_API.G_RET_STS_ERROR
72 --End of Comments
73 --------------------------------------------------------------------------------
74 Procedure Is_Rebook_Supported (p_rbk_chr_id IN NUMBER,
75 p_orig_chr_id IN NUMBER,
76 x_return_status OUT NOCOPY VARCHAR2) is
77
78 l_return_status varchar2(1) default OKL_API.G_RET_STS_SUCCESS;
79
80 ------------------------------------------------------
81 --cursor to find any new add-ons added during rebook
82 --asset line already had add-ons and a new one has been
83 --added during rebook
84 ------------------------------------------------------
85 cursor l_new_addon_csr(p_rbk_chr_id in NUMBER,
86 p_orig_chr_id in NUMBER) is
87 select clet.name
88 from okc_line_styles_b addon_lseb,
89 okc_k_lines_b addon_cleb,
90 okc_line_styles_b model_lseb,
91 okc_k_lines_b model_cleb,
92 okc_k_lines_tl clet,
93 okc_line_styles_b lseb,
94 okc_k_lines_b cleb
95 where
96 addon_cleb.cle_id = model_cleb.id
97 and addon_cleb.dnz_chr_id = model_cleb.dnz_chr_id
98 and addon_lseb.id = addon_cleb.lse_id
99 and addon_lseb.lty_code = 'ADD_ITEM'
100 --
101 and model_cleb.cle_id = cleb.id
102 and model_cleb.dnz_chr_id = cleb.dnz_chr_id
103 and model_lseb.id = model_cleb.lse_id
104 and model_lseb.lty_code = 'ITEM'
105 --
106 and clet.id = cleb.id
107 and clet.language = userenv('LANG')
108 and cleb.chr_id = p_rbk_chr_id --rebook copy
109 and cleb.dnz_chr_id = p_rbk_chr_id --rebook copy
110 and lseb.id = cleb.lse_id
111 and lseb.lty_code = 'FREE_FORM1'
112 --to avoid picking up new asset added during rebook
113 and cleb.orig_system_id1 is not null
114 and exists (select '1'
115 from okc_k_lines_b addon_cleb2,
116 okc_k_lines_b model_cleb2,
117 okc_k_lines_b cleb2
118 where addon_cleb2.id <> nvl(addon_cleb.orig_system_id1,-999)
119 and model_cleb2.id = nvl(model_cleb.orig_system_id1,-999)
120 and cleb2.id = nvl(cleb.orig_system_id1,-999)
121 and addon_cleb2.cle_id = model_cleb2.id
122 and addon_cleb2.dnz_chr_id = model_cleb2.dnz_chr_id
123 and model_cleb2.cle_id = cleb2.id
124 and model_cleb2.dnz_chr_id = cleb2.dnz_chr_id
125 and cleb2.chr_id = p_orig_chr_id --orig contract
126 and cleb2.dnz_chr_id = p_orig_chr_id);--orig contract
127
128 l_asset_number okc_k_lines_tl.name%TYPE default NULL;
129
130 --------------------------------------------------------------
131 --cursor to find any new add-ons added during rebook
132 --asset line had no add-ons but new one has been added
133 --during rebook
134 --------------------------------------------------------------
135 cursor l_new_addon_csr2(p_rbk_chr_id in NUMBER,
136 p_orig_chr_id in NUMBER) is
137 select clet.name
138 from okc_line_styles_b addon_lseb,
139 okc_k_lines_b addon_cleb,
140 okc_line_styles_b model_lseb,
141 okc_k_lines_b model_cleb,
142 okc_k_lines_tl clet,
143 okc_line_styles_b lseb,
144 okc_k_lines_b cleb
145 where
146 addon_cleb.cle_id = model_cleb.id
147 and addon_cleb.dnz_chr_id = model_cleb.dnz_chr_id
148 and addon_lseb.id = addon_cleb.lse_id
149 and addon_lseb.lty_code = 'ADD_ITEM'
150 --
151 and model_cleb.cle_id = cleb.id
152 and model_cleb.dnz_chr_id = cleb.dnz_chr_id
153 and model_lseb.id = model_cleb.lse_id
154 and model_lseb.lty_code = 'ITEM'
155 --
156 and clet.id = cleb.id
157 and clet.language = userenv('LANG')
158 and cleb.chr_id = p_rbk_chr_id --rebook copy
159 and cleb.dnz_chr_id = p_rbk_chr_id --rebook copy
160 and lseb.id = cleb.lse_id
161 and lseb.lty_code = 'FREE_FORM1'
162 --to avoid picking up new asset added during rebook
163 and cleb.orig_system_id1 is not null
164 and not exists (select '1'
165 from okc_k_lines_b addon_cleb2,
166 okc_k_lines_b model_cleb2,
167 okc_k_lines_b cleb2
168 where addon_cleb2.id = nvl(addon_cleb.orig_system_id1,-999)
169 and model_cleb2.id = nvl(model_cleb.orig_system_id1,-999)
170 and cleb2.id = nvl(cleb.orig_system_id1,-999)
171 and addon_cleb2.cle_id = model_cleb2.id
172 and addon_cleb2.dnz_chr_id = model_cleb2.dnz_chr_id
173 and model_cleb2.cle_id = cleb2.id
174 and model_cleb2.dnz_chr_id = cleb2.dnz_chr_id
175 and cleb2.chr_id = p_orig_chr_id --orig contract
176 and cleb2.dnz_chr_id = p_orig_chr_id); --orig contract
177
178
179 ---------------------------------------------------------------------------
180 --Cursor to determine if any add-on inventory item or cost has changed
181 --during rebook
182 ---------------------------------------------------------------------------
183 cursor l_addon_modfn_csr (p_rbk_chr_id in NUMBER,
184 p_orig_chr_id in NUMBER) is
185 select clet.name
186 from okc_k_items cim,
187 okc_line_styles_b addon_lseb,
188 okc_k_lines_b addon_cleb,
189 okc_line_styles_b model_lseb,
190 okc_k_lines_b model_cleb,
191 okc_k_lines_tl clet,
192 okc_line_styles_b lseb,
193 okc_k_lines_b cleb
194 where cim.cle_id = addon_cleb.id
195 and cim.dnz_chr_id = addon_cleb.dnz_chr_id
196 and addon_cleb.cle_id = model_cleb.id
197 and addon_cleb.dnz_chr_id = model_cleb.dnz_chr_id
198 and addon_lseb.id = addon_cleb.lse_id
199 and addon_lseb.lty_code = 'ADD_ITEM'
200 --
201 and model_cleb.cle_id = cleb.id
202 and model_cleb.dnz_chr_id = cleb.dnz_chr_id
203 and model_lseb.id = model_cleb.lse_id
204 and model_lseb.lty_code = 'ITEM'
205 --
206 and clet.id = cleb.id
207 and clet.language = userenv('LANG')
208 and cleb.chr_id = p_rbk_chr_id --rebook copy
209 and cleb.dnz_chr_id = p_rbk_chr_id --rebook copy
210 and lseb.id = cleb.lse_id
211 and lseb.lty_code = 'FREE_FORM1'
212 --to avoid picking up new asset added during rebook
213 and cleb.orig_system_id1 is not null
214 and exists (select '1'
215 from okc_k_items cim2,
216 okc_k_lines_b addon_cleb2,
217 okc_k_lines_b model_cleb2,
218 okc_k_lines_b cleb2
219 where cim2.cle_id = addon_cleb2.id
220 and cim2.dnz_chr_id = addon_cleb2.dnz_chr_id
221 and ( (cim2.object1_id1 <> cim.object1_id1)
222 OR
223 (cim2.object1_id2 <> cim.object1_id2)
224 OR
225 (addon_cleb2.price_unit <> addon_cleb.price_unit)
226 )
227 and addon_cleb2.id = nvl(addon_cleb.orig_system_id1,-999)
228 and model_cleb2.id = nvl(model_cleb.orig_system_id1,-999)
229 and cleb2.id = nvl(cleb.orig_system_id1,-999)
230 and addon_cleb2.cle_id = model_cleb2.id
231 and addon_cleb2.dnz_chr_id = model_cleb2.dnz_chr_id
232 and model_cleb2.cle_id = cleb2.id
233 and model_cleb2.dnz_chr_id = cleb2.dnz_chr_id
234 and cleb2.chr_id = p_orig_chr_id --orig contract
235 and cleb2.dnz_chr_id = p_orig_chr_id);--orig contract
236
237 ------------------------------------------------------
238 --Cursor to determine if an addon has been deleted
239 --during rebook
240 ------------------------------------------------------
241 Cursor l_deleted_addon_csr (p_rbk_chr_id in NUMBER,
242 p_orig_chr_id in NUMBER) is
243 select clet.name
244 from okc_line_styles_b addon_lseb,
245 okc_k_lines_b addon_cleb,
246 okc_line_styles_b model_lseb,
247 okc_k_lines_b model_cleb,
248 okc_k_lines_tl clet,
249 okc_line_styles_b lseb,
250 okc_k_lines_b cleb
251 where
252 addon_cleb.cle_id = model_cleb.id
253 and addon_cleb.dnz_chr_id = model_cleb.dnz_chr_id
254 and addon_lseb.id = addon_cleb.lse_id
255 and addon_lseb.lty_code = 'ADD_ITEM'
256 --
257 and model_cleb.cle_id = cleb.id
258 and model_cleb.dnz_chr_id = cleb.dnz_chr_id
259 and model_lseb.id = model_cleb.lse_id
260 and model_lseb.lty_code = 'ITEM'
261 --
262 and clet.id = cleb.id
263 and clet.language = userenv('LANG')
264 and cleb.chr_id = p_orig_chr_id --orig contract
265 and cleb.dnz_chr_id = p_orig_chr_id --orig contract
266 and lseb.id = cleb.lse_id
267 and lseb.lty_code = 'FREE_FORM1'
268 and not exists (select '1'
269 from okc_k_lines_b addon_cleb2,
270 okc_k_lines_b model_cleb2,
271 okc_k_lines_b cleb2
272 where nvl(addon_cleb2.orig_system_id1,-999) = addon_cleb.id
273 and nvl(model_cleb2.orig_system_id1,-999) = model_cleb.id
274 --and nvl(cleb2.orig_system_id1,-999) = cleb2.id
275 and nvl(cleb2.orig_system_id1,-999) = cleb.id
276 and addon_cleb2.cle_id = model_cleb2.id
277 and addon_cleb2.dnz_chr_id = model_cleb2.dnz_chr_id
278 and model_cleb2.cle_id = cleb2.id
279 and model_cleb2.dnz_chr_id = cleb2.dnz_chr_id
280 and cleb2.chr_id = p_rbk_chr_id --rebook copy
281 and cleb2.dnz_chr_id = p_rbk_chr_id --rebook copy
282 --to avoid picking up new asset added during rebook
283 and cleb2.orig_system_id1 is not null);
284
285 -------------------------------------------------------
286 --cursor to determine if there is any change in
287 --adjustments during rebook
288 -------------------------------------------------------
289 Cursor l_adj_modfn_csr (p_rbk_chr_id in NUMBER,
290 p_orig_chr_id in NUMBER) is
291 select clet.name
292 from okl_k_lines kle,
293 okc_k_lines_tl clet,
294 okc_k_lines_b cleb,
295 okc_line_styles_b lseb
296 where kle.id = cleb.id
297 and clet.id = cleb.id
298 and clet.language = userenv('LANG')
299 and cleb.chr_id = p_rbk_chr_id --rebook chr_id
300 and cleb.dnz_chr_id = p_rbk_chr_id --rebook chr id
301 and lseb.id = cleb.lse_id
302 and lseb.lty_code = 'FREE_FORM1'
303 --to avoid picking up new asset during rebook
304 and cleb.orig_system_id1 is not null
305 and exists (select '1'
306 from okl_k_lines kle2,
307 okc_k_lines_b cleb2
308 where kle2.id = cleb2.id
309 and cleb2.id = nvl(cleb.orig_system_id1,-999)
310 and (
311 -- Bug# 4721428: allow updates to trade-in
312 --(nvl(kle2.tradein_amount,-99) <> nvl(kle.tradein_amount,-99))
313 --OR
314 (nvl(kle2.capital_reduction,-99) <> nvl(kle.capital_reduction,-99))
315 OR
316 (nvl(kle2.capital_reduction_percent,-99) <> nvl(kle.capital_reduction_percent,-99))
317 )
318 and cleb2.chr_id = p_orig_chr_id --orig contract
319 and cleb2.dnz_chr_id = p_orig_chr_id); --orig_contract
320
321 l_unsupported_rebook_modfn EXCEPTION;
322
323 begin
324 x_return_status := OKL_API.G_RET_STS_SUCCESS;
325
326 l_asset_number := NULL;
327
328 --1.0 check if new addon has been added
329 open l_new_addon_csr(p_rbk_chr_id => p_rbk_chr_id,
330 p_orig_chr_id => p_orig_chr_id);
331 Fetch l_new_addon_csr into l_asset_number;
332 If l_new_addon_csr%NOTFOUND then
333 NULL;
334 End If;
335 Close l_new_addon_csr;
336
337 If nvl(l_asset_number,OKL_API.G_MISS_CHAR) <> OKL_API.G_MISS_CHAR then
338 OKL_API.set_message(
339 p_app_name => G_APP_NAME,
340 p_msg_name => 'OKL_REBOOK_UNSUPPORTED_MODFN'
341 );
342 raise l_unsupported_rebook_modfn;
343 End If;
344
345 l_asset_number := NULL;
346
347 --2.0 check if new addon has been added
348 open l_new_addon_csr2(p_rbk_chr_id => p_rbk_chr_id,
349 p_orig_chr_id => p_orig_chr_id);
350 Fetch l_new_addon_csr2 into l_asset_number;
351 If l_new_addon_csr2%NOTFOUND then
352 NULL;
353 End If;
354 Close l_new_addon_csr2;
355
356 If nvl(l_asset_number,OKL_API.G_MISS_CHAR) <> OKL_API.G_MISS_CHAR then
357 OKL_API.set_message(
358 p_app_name => G_APP_NAME,
359 p_msg_name => 'OKL_REBOOK_UNSUPPORTED_MODFN'
360 );
361 raise l_unsupported_rebook_modfn;
362 End If;
363
364 l_asset_number := NULL;
365
366 --3.0 check if addon has been modified
367 open l_addon_modfn_csr(p_rbk_chr_id => p_rbk_chr_id,
368 p_orig_chr_id => p_orig_chr_id);
369 Fetch l_addon_modfn_csr into l_asset_number;
370 If l_addon_modfn_csr%NOTFOUND then
371 NULL;
372 End If;
373 Close l_addon_modfn_csr;
374
375 If nvl(l_asset_number,OKL_API.G_MISS_CHAR) <> OKL_API.G_MISS_CHAR then
376 OKL_API.set_message(
377 p_app_name => G_APP_NAME,
378 p_msg_name => 'OKL_REBOOK_UNSUPPORTED_MODFN'
379 );
380 raise l_unsupported_rebook_modfn;
381 End If;
382
383 --4.0 check if new addon has been deleted
384 open l_deleted_Addon_csr(p_rbk_chr_id => p_rbk_chr_id,
385 p_orig_chr_id => p_orig_chr_id);
386 Fetch l_deleted_Addon_csr into l_asset_number;
387 If l_deleted_addon_csr%NOTFOUND then
388 NULL;
389 End If;
390 Close l_deleted_Addon_csr;
391
392 If nvl(l_asset_number,OKL_API.G_MISS_CHAR) <> OKL_API.G_MISS_CHAR then
393 OKL_API.set_message(
394 p_app_name => G_APP_NAME,
395 p_msg_name => 'OKL_REBOOK_UNSUPPORTED_MODFN'
396 );
397 raise l_unsupported_rebook_modfn;
398 End If;
399 l_asset_number := NULL;
400
401 --5.0 check if adjustments have been modified
402 open l_adj_modfn_csr(p_rbk_chr_id => p_rbk_chr_id,
403 p_orig_chr_id => p_orig_chr_id);
404 Fetch l_adj_modfn_csr into l_asset_number;
405 If l_adj_modfn_csr%NOTFOUND then
406 NULL;
407 End If;
408 Close l_adj_modfn_csr;
409
410 If nvl(l_asset_number,OKL_API.G_MISS_CHAR) <> OKL_API.G_MISS_CHAR then
411 OKL_API.set_message(
412 p_app_name => G_APP_NAME,
413 p_msg_name => 'OKL_REBOOK_UNSUPPORTED_MODFN'
414 );
415 raise l_unsupported_rebook_modfn;
416 End If;
417
418 EXCEPTION
419 When l_unsupported_rebook_modfn then
420 If l_new_addon_csr%ISOPEN then
421 close l_new_addon_csr;
422 End If;
423 If l_new_addon_csr2%ISOPEN then
424 close l_new_addon_csr2;
425 End If;
426 If l_addon_modfn_csr%ISOPEN then
427 close l_addon_modfn_csr;
428 End If;
429 If l_deleted_addon_csr%ISOPEN then
430 close l_deleted_addon_csr;
431 End If;
432 If l_adj_modfn_csr%ISOPEN then
433 close l_adj_modfn_csr;
434 End If;
435 x_return_status := OKL_API.G_RET_STS_ERROR;
436 When OTHERS then
437 If l_new_addon_csr%ISOPEN then
438 close l_new_addon_csr;
439 End If;
440 If l_new_addon_csr2%ISOPEN then
441 close l_new_addon_csr2;
442 End If;
443 If l_deleted_addon_csr%ISOPEN then
444 close l_deleted_addon_csr;
445 End If;
446 If l_addon_modfn_csr%ISOPEN then
447 close l_addon_modfn_csr;
448 End If;
449 If l_adj_modfn_csr%ISOPEN then
450 close l_adj_modfn_csr;
451 End If;
452 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
453
454 End Is_Rebook_Supported;
455 --Bug# 3310972
456 --------------------------------------------------------------------------------
457 --Bug # 2953906
458 --Api Name : Validate_Bill_To
459 --Description : Local procedure to validate bill to addresses on the contract
460 -- Will be called for normal booking.Will not be called for
461 -- rebooks and releases
462 --Notes :
463 -- IN Parameters -
464 -- IN Parameters -
465 -- p_chr_id - contract id
466 -- OUT Parameter x_return_status - return status
467 --End of Comments
468 --------------------------------------------------------------------------------
469 Procedure Validate_bill_To(p_chr_id IN Number,
470 x_return_status OUT NOCOPY VARCHAR2) is
471 l_return_status varchar2(1) default OKL_API.G_RET_STS_SUCCESS;
472
473 --cursor to fetch rule values for customer account and bill to at
474 --contract level and contract line level
475
476 --Bug# 3124577 :new cursors for rule migration
477 --cursor to fetch contract header level BTO and CAN
478 cursor l_chrb_csr(chrId IN NUMBER) is
479 Select chrb.bill_to_site_use_id,
480 chrb.cust_acct_id
481 from okc_k_headers_b chrb
482 where id = ChrId;
483
484 --cursor to fetch contract line level BTO and CAN
485 cursor l_cleb_csr (chrId In Number) is
486 Select cleb.bill_to_site_use_id
487 from okc_k_lines_b cleb
488 where cleb.dnz_chr_id = ChrId
489 and cleb.sts_code <> 'ABANDONED'
490 and cleb.bill_to_site_use_id is not null;
491
492 --cursor to fetch vendor billing setup BTO and CAN
493 cursor l_cplb_csr (ChrId In Number) is
494 Select cplb.bill_to_site_use_id,
495 cplb.cust_acct_id
496 from okc_k_party_roles_b cplb
497 where cplb.chr_id = ChrId
498 and cplb.dnz_chr_id = ChrId
499 and (cplb.bill_to_site_use_id is NOT NULL
500 OR
501 cplb.cust_acct_id is NOT NULL);
502
503 l_bto_id okc_rules_b.object1_id1%TYPE;
504 l_custacct_id okc_rules_b.object1_id1%TYPE;
505
506
507 --Local function to determine whether BTO is active
508 Function is_bto_active(p_bto_id IN Varchar2,
509 p_custacct_id IN Varchar2) return Varchar2 is
510 l_return_status varchar2(1) default OKL_API.G_RET_STS_SUCCESS;
511
512 --cursor to find BTO status
513 Cursor l_bto_sts_csr(btoid IN NUMBER,
514 custacctid IN NUMBER) is
515 select 'A'
516 from okx_cust_site_uses_v site_use,
517 hz_cust_acct_sites_all site
518 where site_use.id1 = btoid
519 and site_use.site_use_code = 'BILL_TO'
520 and site_use.b_status = 'A'
521 --and site_use.cust_acct_site_status = 'A'
522 and site.cust_acct_site_id = site_use.cust_acct_site_id
523 and site.status = 'A'
524 and site_use.cust_account_id = custacctid;
525
526 l_bto_id NUMBER;
527 l_custacct_id NUMBER;
528 l_bto_status VARCHAR2(1) default 'I';
529
530
531 Begin
532 ----
533 l_return_status := OKL_API.G_RET_STS_SUCCESS;
534 l_bto_status := 'I'; -- inactive
535
536 l_bto_id := to_number(p_bto_id);
537 l_custacct_id := to_number(p_custacct_id);
538
539 open l_bto_sts_csr(btoid => l_bto_id,
540 custacctid => l_custacct_id);
541 fetch l_bto_sts_csr into l_bto_status;
542 If l_bto_sts_csr%NOTFOUND then
543 null;
544 End If;
545 close l_bto_sts_csr;
546
547 if l_bto_status = 'I' then
548 l_return_status := OKL_API.G_RET_STS_ERROR;
549 end if;
550 Return(l_return_status);
551
552 exception
553 when others then
554 if l_bto_sts_csr%ISOPEN then
555 close l_bto_sts_csr;
556 end if;
557 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
558 return(l_return_status);
559 end is_bto_active;
560
561 Begin
562 -----
563 l_return_status := OKL_API.G_RET_STS_SUCCESS;
564
565 --fetch contract customer account at contract header level
566 open l_chrb_csr(ChrId => p_chr_id);
567 Fetch l_chrb_csr into l_bto_id,l_custacct_id;
568 If l_chrb_csr%NOTFOUND then
569 Null;
570 End If;
571 Close l_chrb_csr;
572
573 --check if bto is active at contract header level
574 l_return_status := is_bto_active(p_bto_id => l_bto_id,
575 p_custacct_id => l_custacct_id);
576 If l_return_status = OKL_API.G_RET_STS_ERROR then
577 --set error message
578 OKL_API.set_message(
579 p_app_name => G_APP_NAME,
580 p_msg_name => 'OKL_QA_INVALID_BILLTO'
581 );
582 Elsif l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR then
583 Null;
584 End If;
585
586
587 If l_return_status = OKL_API.G_RET_STS_SUCCESS then
588 --fetch bill to at contract line level
589 open l_cleb_csr(ChrId => p_chr_id);
590 Loop
591 Fetch l_cleb_csr into l_bto_id;
592 Exit when l_cleb_csr%NOTFOUND;
593 --check if bto is active at contract line level
594 l_return_status := is_bto_active(p_bto_id => l_bto_id,
595 p_custacct_id => l_custacct_id);
596 If l_return_status = OKL_API.G_RET_STS_ERROR then
597 --set error message
598 OKL_API.set_message(
599 p_app_name => G_APP_NAME,
600 p_msg_name => 'OKL_QA_INVALID_BILLTO'
601 );
602 Exit;
603 Elsif l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR then
604 Exit;
605 End If;
606 End Loop;
607 Close l_cleb_csr;
608 End If;
609
610
611 If l_return_status = OKL_API.G_RET_STS_SUCCESS then
612 --fetch bill to and cust account at vendor billing setup level
613 open l_cplb_csr(ChrId => p_chr_id);
614 Loop
615 Fetch l_cplb_csr into l_bto_id,l_custacct_id;
616 Exit when l_cplb_csr%NOTFOUND;
617 --check if bto is active at vendor billing setup level
618 If l_bto_id is not null and l_custacct_id is not null then
619 l_return_status := is_bto_active(p_bto_id => l_bto_id,
620 p_custacct_id => l_custacct_id);
621 If l_return_status = OKL_API.G_RET_STS_ERROR then
622 --set error message
623 OKL_API.set_message(
624 p_app_name => G_APP_NAME,
625 p_msg_name => 'OKL_QA_INVALID_BILLTO'
626 );
627 Exit;
628 Elsif l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR then
629 Exit;
630 End If;
631 End If;
632 End Loop;
633 Close l_cplb_csr;
634 End If;
635
636
637 x_return_status := l_return_Status;
638 Exception
639 When others then
640 If l_chrb_csr%ISOPEN then
641 close l_chrb_csr;
642 End If;
643 If l_cleb_csr%ISOPEN then
644 close l_cleb_csr;
645 End If;
646 If l_cplb_csr%ISOPEN then
647 close l_cplb_csr;
648 End If;
649
650 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
651 ----
652 End Validate_Bill_To;
653 --Bug# 2953906 end
654
655 --Bug # 3783518
656 --Api Name : Validate_Rebook_Date
657 --Description : Local procedure to check that if there is a change
658 -- in Unit Cost during Rebook, then the Revision Date
659 -- should be the same as Contract Start Date.
660 --Notes :
661 --
662 -- IN Parameters -
663 -- p_rbk_chr_id - contract id of rebook copy contract
664 -- p_orig_chr_id - contract id of original contract
665 -- OUT Parameter x_return_status - will return OKL_API.G_RET_STS_SUCCESS or
666 -- OKL_API.G_RET_STS_ERROR
667 --End of Comments
668 --------------------------------------------------------------------------------
669 PROCEDURE Validate_Rebook_Date
670 (p_api_version IN NUMBER,
671 p_init_msg_list IN VARCHAR2,
672 x_return_status OUT NOCOPY VARCHAR2,
673 x_msg_count OUT NOCOPY NUMBER,
674 x_msg_data OUT NOCOPY VARCHAR2,
675 p_rbk_chr_id IN NUMBER,
676 p_orig_chr_id IN NUMBER,
677 p_rebook_date IN DATE) IS
678
679 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
680 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Rebook_Date';
681 l_api_version CONSTANT NUMBER := 1.0;
682
683 CURSOR chr_csr(p_chr_id NUMBER) IS
684 select start_date
685 from okc_k_headers_b
686 where id = p_chr_id;
687
688 CURSOR cle_csr(p_rbk_chr_id NUMBER,
689 p_orig_chr_id NUMBER,
690 p_item_lty_code VARCHAR2) IS
691 select rbk_cle.price_unit rbk_price_unit,
692 orig_cle.price_unit orig_price_unit
693 from okc_k_lines_b rbk_cle,
694 okc_line_styles_b rbk_lse,
695 okc_k_lines_b orig_cle
696 where rbk_cle.dnz_chr_id = p_rbk_chr_id
697 and rbk_lse.lty_code = p_item_lty_code
698 and rbk_cle.lse_id = rbk_lse.id
699 and orig_cle.id = rbk_cle.orig_system_id1
700 and orig_cle.dnz_chr_id = p_orig_chr_id;
701
702 l_cle_rec cle_csr%rowtype;
703 l_chr_rec chr_csr%rowtype;
704 l_item_lty_code okc_line_styles_b.lty_code%type := 'ITEM';
705 BEGIN
706
707 open chr_csr(p_rbk_chr_id);
708 fetch chr_csr into l_chr_rec;
709 close chr_csr;
710
711 -- Revision Date should be the same as Contract Start Date inorder to make an
712 -- Unit Cost change
713 if (l_chr_rec.start_date <> p_rebook_date) then
714
715 open cle_csr(p_rbk_chr_id, p_orig_chr_id,l_item_lty_code);
716 loop
717 fetch cle_csr into l_cle_rec;
718 exit when cle_csr%NOTFOUND;
719
720 if l_cle_rec.rbk_price_unit <> l_cle_rec.orig_price_unit then
721 close cle_csr;
722 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
723 p_msg_name => 'OKL_LLA_VALIDATE_UNIT_COST_CHG'
724 );
725 RAISE OKL_API.G_EXCEPTION_ERROR;
726 end if;
727 end loop;
728 close cle_csr;
729 end if;
730
731 EXCEPTION
732 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
733 x_return_status := OKL_API.G_RET_STS_ERROR;
734 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
735 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
736 WHEN OTHERS THEN
737 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
738 END Validate_Rebook_Date;
739 --Bug# 3783518: end
740
741 --------------------------------------------------------------------------------
742 --Api Name : Activate Contract
743 --Description : Will call the FA Activation and IB Activation public apis
744 -- Will be called from the activate button on the booking screen
745 --Notes :
746 -- IN Parameters -
747 -- IN Parameters -
748 -- p_chr_id - contract id to be activated
749 -- p_call_mode - 'BOOK' for booking
750 -- 'REBOOK' for rebooking
751 -- 'RELEASE' for release
752 --End of Comments
753 --------------------------------------------------------------------------------
754 PROCEDURE ACTIVATE_CONTRACT(p_api_version IN NUMBER,
755 p_init_msg_list IN VARCHAR2,
756 x_return_status OUT NOCOPY VARCHAR2,
757 x_msg_count OUT NOCOPY NUMBER,
758 x_msg_data OUT NOCOPY VARCHAR2,
759 p_chrv_id IN NUMBER,
760 p_call_mode IN VARCHAR2) IS
761
762 l_return_status VARCHAR2(1) DEFAULT Okl_Api.G_RET_STS_SUCCESS;
763 l_api_name CONSTANT VARCHAR2(30) := 'ACTIVATE_CONTRACT';
764 l_api_version CONSTANT NUMBER := 1.0;
765
766 l_cimv_tbl_fa Okl_Activate_Asset_Pub.cimv_tbl_type;
767 l_cimv_tbl_ib Okl_Activate_Ib_Pub.cimv_tbl_type;
768 l_tcnv_rec Okl_Trx_Contracts_Pvt.tcnv_rec_type;
769 l_service_chr_id Number;
770
771 l_commit VARCHAR2(1) := Okl_Api.G_FALSE;
772 l_transaction_type VARCHAR2(256) default 'Booking';
773 l_draft_yn VARCHAR2(1) := Okl_Api.G_FALSE;
774
775 --Bug#2835070
776 --cursor to check if the contract is rebooked contract
777 cursor l_chk_rbk_csr(p_chr_id IN NUMBER) is
778 SELECT '!',chr.orig_system_id1, ktrx.date_transaction_occurred,ktrx.id
779 FROM okc_k_headers_b CHR,
780 okl_trx_contracts ktrx
781 WHERE ktrx.khr_id_new = chr.id
782 AND ktrx.tsu_code = 'ENTERED'
783 AND ktrx.rbr_code is NOT NULL
784 AND ktrx.tcn_type = 'TRBK'
785 AND CHR.id = p_chr_id
786 AND CHR.ORIG_SYSTEM_SOURCE_CODE = 'OKL_REBOOK'
787 AND ktrx.representation_type = 'PRIMARY'; -- MGAAP 7263041
788
789 l_rbk_khr VARCHAR2(1) DEFAULT '?';
790 l_orig_khr_id okc_k_headers_b.orig_system_id1%Type;
791 l_rebook_date OKL_TRX_CONTRACTS.date_transaction_occurred%TYPE;
792 l_transaction_id okl_trx_contracts.id%TYPE;
793 --4542290 start
794 l_trx_date OKL_TRX_CONTRACTS.date_transaction_occurred%TYPE;
795 l_pdt_parameter_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
796 l_max_bill_date DATE;
797 l_sty_id NUMBER;
798 l_accrual_date OKL_TRX_CONTRACTS.date_transaction_occurred%TYPE;
799 l_stream_name VARCHAR2(30);
800 --4542290 end
801
802 --Bug#3156924 : Cursor modified to fetch transaction date
803 --cursor to check if the contract is selected for Mass Rebook
804 CURSOR l_chk_mass_rbk_csr (p_chr_id IN NUMBER) IS
805 SELECT '!', ktrx.date_transaction_occurred,
806 --Bug# 4212626
807 ktrx.id,
808 source_trx_id -- 4542290
809 FROM okc_k_headers_b CHR,
810 okl_trx_contracts ktrx
811 where CHR.ID = p_chr_id
812 AND ktrx.KHR_ID = chr.id
813 AND ktrx.tsu_code = 'ENTERED'
814 AND ktrx.rbr_code IS NOT NULL
815 AND ktrx.tcn_type = 'TRBK'
816 AND ktrx.representation_type = 'PRIMARY' -- MGAAP 7263041
817 /*------------------------------------------------
818 --AND EXISTS (SELECT '1'
819 -- FROM okl_trx_contracts ktrx
820 -- WHERE ktrx.KHR_ID = chr.id
821 -- AND ktrx.tsu_code = 'ENTERED'
822 -- AND ktrx.rbr_code IS NOT NULL
823 -- AND ktrx.tcn_type = 'TRBK')
824 ------------------------------------------------*/
825 AND EXISTS (SELECT '1'
826 FROM okl_rbk_selected_contract rbk_khr
827 WHERE rbk_khr.KHR_ID = chr.id
828 AND rbk_khr.STATUS <> 'PROCESSED'); --check with debdip
829
830 l_mass_rbk_khr VARCHAR2(1) DEFAULT '?';
831 --Bug# 3156924
832 l_mass_rbk_date OKL_TRX_CONTRACTS.date_transaction_occurred%TYPE;
833 --Bug# 4212626
834 l_mass_rbk_trx_id OKL_TRX_CONTRACTS.id%TYPE;
835 --4542290
836 l_source_trx_id OKL_TRX_CONTRACTS.ID%TYPE;
837
838 CURSOR l_source_trx_type_csr(p_trx_id NUMBER) IS
839 SELECT TCN_TYPE
840 FROM OKL_TRX_CONTRACTS
841 WHERE ID = p_trx_id;
842
843 l_source_trx_type OKL_TRX_CONTRACTS.TCN_TYPE%TYPE;
844 --4542290
845 --cursor to check if usage line exists on the contract
846 CURSOR l_chk_usage_csr (p_chr_id IN Number) IS
847 SELECT '!'
848 FROM okc_k_headers_b CHR
849 WHERE chr.ID = p_chr_id
850 AND exists (SELECT '1'
851 FROM
852 OKC_LINE_STYLES_B lse,
853 OKC_K_LINES_B cle
854 WHERE cle.sts_code = 'APPROVED'
855 AND lse.id = cle.lse_id
856 AND lse.lty_code = 'USAGE'
857 AND cle.dnz_chr_id = chr.id);
858
859 l_usage_khr VARCHAR2(1) DEFAULT '?';
860
861 --cursor to check if contract is a re-lease contract
862 CURSOR l_chk_rel_khr_csr (p_chr_id IN Number) IS
863 SELECT '!'
864 FROM okc_k_headers_b CHR
865 where chr.ID = p_chr_id
866 AND nvl(chr.orig_system_source_code,'XXXX') = 'OKL_RELEASE';
867
868 l_rel_khr VARCHAR2(1) DEFAULT '?';
869
870 --cursor to check if contract has re-lease assets
871 CURSOR l_chk_rel_ast_csr (p_chr_id IN Number) IS
872 SELECT '!'
873 FROM okc_k_headers_b CHR
874 WHERE nvl(chr.orig_system_source_code,'XXXX') <> 'OKL_RELEASE'
875 and chr.ID = p_chr_id
876 AND exists (SELECT '1'
877 FROM OKC_RULES_B rul
878 WHERE rul.dnz_chr_id = chr.id
879 AND rul.rule_information_category = 'LARLES'
880 AND nvl(rule_information1,'N') = 'Y');
881
882 l_rel_ast VARCHAR2(1) DEFAULT '?';
883
884 --Bug#2522439 Start
885 --Cursorr to find out asset return record for re-lease
886 Cursor l_asr_csr(p_rel_chr_id IN NUMBER) IS
887 SELECT cle.cle_id finasst_id,
888 cim.object1_id1 asset_id,
889 cle_orig.cle_id orig_finasst_id,
890 asr.id asset_return_id
891 FROM OKL_ASSET_RETURNS_B asr,
892 OKC_K_LINES_B cle_orig,
893 OKC_LINE_STYLES_B lse_orig,
894 OKC_K_ITEMS cim_orig,
895 OKC_K_ITEMS cim,
896 OKC_K_LINES_B cle,
897 OKC_LINE_STYLES_B lse,
898 OKC_STATUSES_B sts,
899 OKL_TXL_ASSETS_B txl
900 WHERE asr.kle_id = cle_orig.cle_id
901 AND asr.ars_code = 'RE_LEASE'
902 AND cim.object1_id1 = cim_orig.object1_id1
903 AND cim.object1_id2 = cim_orig.object1_id2
904 AND cim.jtot_object1_code = cim_orig.jtot_object1_code
905 AND cim.id <> cim_orig.id
906 AND cle_orig.id = cim_orig.cle_id
907 AND cle_orig.dnz_chr_id = cim_orig.dnz_chr_id
908 AND cle_orig.lse_id = lse_orig.id
909 AND lse_orig.lty_code = 'FIXED_ASSET'
910 AND cim.cle_id = cle.id
911 AND cim.dnz_chr_id = cle.dnz_chr_id
912 AND cle.id = txl.kle_id
913 AND cle.dnz_chr_id = p_rel_chr_id
914 AND cle.lse_id = lse.id
915 AND lse.lty_code = 'FIXED_ASSET'
916 AND cle.sts_code = sts.code
917 AND sts.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED');
918 /*
919 -- smereddy,04/13/2006,Bug#4291242
920 -- Commented as part of bug fix
921 AND exists
922 (select trx.tas_type,
923 ttyp.name
924 from OKL_TRX_ASSETS trx,
925 OKL_TRX_TYPES_TL ttyp
926 where trx.id = txl.tas_id
927 and trx.try_id = ttyp.id
928 and ttyp.name = 'Internal Asset Creation'
929 and ttyp.language = 'US'
930 and trx.tsu_code <> 'PROCESSED'
931 --Bug# 3533936
932 and trx.tas_type = 'CRL')
933 AND txl.tal_type = 'CRL';
934 -- end,smereddy,04/13/2006,Bug#4291242
935 */
936 l_asr_rec l_asr_csr%ROWTYPE;
937
938
939 l_artv_rec okl_asset_returns_pub.artv_rec_type;
940 lx_artv_rec okl_asset_returns_pub.artv_rec_type;
941 --Bug#2522439 End
942
943 --Bug#3143522 : Subsidies enhancement
944
945 l_subsidy_exists varchar2(1) default OKL_API.G_FALSE;
946
947 -- cursor to find subsidies which have expired (if any)
948 -- sjalasut. modified cursor to remove " and nvl(subb.effective_to_date,sysdate)+nvl(subb.expire_after_days,0) < sysdate "
949 -- from the cursor to make the cursor re-usable for subsidy pools enhancement, also added subsidy_id column
950 -- the check for expiried subsidy would be done in the code, the else part will fork to the common pool transaction api
951 -- also changed the name of the cursor from l_expired_subsidy_csr as now it retrieves all subsidies
952
953 cursor l_subsidy_csr(p_chr_id in number) is
954 select nvl(subb.effective_to_date,sysdate) effective_to_date,
955 nvl(subb.expire_after_days,0) expire_after_days,
956 clet_sub.name subsidy_name,
957 clet_asst.name asset_number,
958 subb.id subsidy_id, -- added for subsidy pools enhancement
959 kle_sub.amount subsidy_amount, -- added for subsidy pools enhancement
960 kle_sub.subsidy_override_amount subsidy_override_amount, -- added for subsidy pools enhancement
961 cleb_asst.start_date asset_start_date, -- added for subsidy pools enhancement
962 cleb_asst.id asset_id -- added for subsidy pools enhancement
963 ,subb.effective_from_date effective_from_date -- added : Bug 6050165 : prasjain
964 from okl_subsidies_b subb,
965 okl_k_lines kle_sub,
966 okc_k_lines_tl clet_sub,
967 okc_k_lines_b cleb_sub,
968 okc_line_styles_b lseb_sub,
969 okc_k_lines_tl clet_asst,
970 okc_k_lines_b cleb_asst,
971 okc_line_styles_b lseb_asst
972 where subb.id = kle_sub.subsidy_id
973 --and nvl(subb.effective_to_date,sysdate)+nvl(subb.expire_after_days,0) < sysdate
974 and kle_sub.id = cleb_sub.id
975 and clet_sub.id = cleb_sub.id
976 and clet_sub.language = userenv('LANG')
977 and cleb_sub.cle_id = cleb_asst.id
978 and cleb_sub.dnz_chr_id = cleb_asst.dnz_chr_id
979 and cleb_sub.sts_code <> 'ABANDONED'
980 and lseb_sub.id = cleb_sub.lse_id
981 and lseb_sub.lty_code = 'SUBSIDY'
982 and clet_asst.id = cleb_asst.id
983 and clet_asst.language = userenv('LANG')
984 and cleb_asst.chr_id = p_chr_id
985 and cleb_asst.dnz_chr_id = p_chr_id
986 and lseb_asst.id = cleb_asst.lse_id
987 and lseb_asst.lty_code = 'FREE_FORM1'
988 and cleb_asst.sts_code <> 'ABANDONED';
989
990 l_subsidy_rec l_subsidy_csr%ROWTYPE;
991 l_subsidy_valid_status varchar2(1) default OKL_API.G_RET_STS_SUCCESS;
992
993 -- Start : Bug 6050165 : prasjain
994 --cursor to check subsidy applicability at line dates
995 cursor l_cle_csr (p_subsidy_id in number,
996 p_asset_cle_id in number) is
997 Select 'Y'
998 from okl_subsidies_b sub,
999 okc_k_lines_b cleb
1000 where sub.id = p_subsidy_id
1001 and cleb.id = p_asset_cle_id
1002 and TRUNC(cleb.start_date) between TRUNC(sub.effective_from_date)
1003 and TRUNC(nvl(sub.effective_to_date,cleb.start_date));
1004 l_applicable varchar2(1);
1005 -- End : Bug 6050165 : prasjain
1006
1007 --Bug#3143522 : 11.5.10 Subsidy enhancement
1008
1009 --BUG# 3397688 : get valid GL date for Accrual reversals during rebooks
1010 l_gl_date date;
1011
1012 -- Bug# 3541098
1013 -- cursor to get capitalize interest flag
1014 Cursor cap_interest_rul_csr (pchrid number) is
1015 Select rul.rule_information1 capitalize_interest_flag
1016 From okc_rules_b rul
1017 where rul.dnz_chr_id = pchrid
1018 and rul.rule_information_category = 'LACPLN';
1019
1020 Cursor chr_csr (pchrid number) is
1021 Select contract_number
1022 ,start_date
1023 From okc_k_headers_b chr
1024 where chr.id = pchrid;
1025
1026 --4542290
1027 CURSOR max_bill_date_csr(p_stm_id IN NUMBER) IS
1028 SELECT MAX(sel.stream_element_date) stream_element_date
1029 FROM okl_strm_elements sel
1030 WHERE sel.stm_id = p_stm_id
1031 AND sel.date_billed IS NOT NULL;
1032
1033 l_capitalize_interest_flag okc_rules_b.rule_information1%TYPE;
1034 l_amount number;
1035 l_source_id number;
1036 l_contract_number okc_k_headers_b.contract_number%type;
1037 l_start_date okc_k_headers_b.start_date%type;
1038 -- Bug# 3541098 end
1039
1040 l_ignore_flag VARCHAR2(1);
1041
1042 -- sjalasut: added local variables for subsidy pools enhancement. START
1043 lx_sub_pool_id okl_subsidy_pools_b.id%TYPE;
1044 lx_sub_pool_curr_code okl_subsidy_pools_b.currency_code%TYPE;
1045 l_sub_pool_applicable VARCHAR2(10);
1046 lv_subsidy_amount okl_k_lines.amount%TYPE;
1047 l_debug_enabled VARCHAR2(10);
1048 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_ACTIVATE_CONTRACT_PUB.ACTIVATE_CONTRACT';
1049 is_debug_statement_on BOOLEAN;
1050 -- sjalasut: added local variables for subsidy pools enhancement. END
1051
1052 -- dedey, Bug#4264314
1053 lx_trx_number OKL_TRX_CONTRACTS.trx_number%TYPE := null; -- MGAAP 7263041
1054 l_accrual_rec OKL_GENERATE_ACCRUALS_PVT.adjust_accrual_rec_type;
1055 l_stream_tbl OKL_GENERATE_ACCRUALS_PVT.stream_tbl_type;
1056 -- dedey, Bug#4264314
1057
1058 BEGIN
1059 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1060 -- Call start_activity to create savepoint, check compatibility
1061 -- and initialize message list
1062 l_return_status := Okl_Api.START_ACTIVITY(
1063 p_api_name => l_api_name,
1064 p_pkg_name => g_pkg_name,
1065 p_init_msg_list => p_init_msg_list,
1066 l_api_version => l_api_version,
1067 p_api_version => p_api_version,
1068 p_api_type => '_PUB',
1069 x_return_status => x_return_status);
1070 -- Check if activity started successfully
1071 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1072 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1073 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1074 RAISE Okc_Api.G_EXCEPTION_ERROR;
1075 END IF;
1076
1077 okl_debug_pub.logmessage('In Activate_contract: p_chrv_id =' || p_chrv_id);
1078 okl_debug_pub.logmessage('p_call_mode =' || p_call_mode);
1079 -- check if debug is enabled
1080 l_debug_enabled := okl_debug_pub.check_log_enabled;
1081 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
1082
1083 --check for mass rebook contract
1084 l_mass_rbk_khr := '?';
1085 OPEN l_chk_mass_rbk_csr (p_chr_id => p_chrv_id);
1086 --bug# 3156924
1087 --FETCH l_chk_mass_rbk_csr INTO l_mass_rbk_khr;
1088 --Bug# 4212626
1089 FETCH l_chk_mass_rbk_csr INTO
1090 l_mass_rbk_khr, l_mass_rbk_date, l_mass_rbk_trx_id, l_source_trx_id;
1091 -- 4542290
1092 IF l_chk_mass_rbk_csr%NOTFOUND THEN
1093 NULL;
1094 END IF;
1095 CLOSE l_chk_mass_rbk_csr;
1096
1097 --check for rebook contract
1098 l_rbk_khr := '?';
1099 l_orig_khr_id := null;
1100 l_transaction_id := null;
1101 OPEN l_chk_rbk_csr (p_chr_id => p_chrv_id);
1102 FETCH l_chk_rbk_csr INTO l_rbk_khr,l_orig_khr_id,l_rebook_date,l_transaction_id;
1103 IF l_chk_rbk_csr%NOTFOUND THEN
1104 NULL;
1105 END IF;
1106 CLOSE l_chk_rbk_csr;
1107
1108 l_rel_khr := '?';
1109 --check for relese contract
1110 OPEN l_chk_rel_khr_csr (p_chr_id => p_chrv_id);
1111 FETCH l_chk_rel_khr_csr INTO l_rel_khr;
1112 IF l_chk_rel_khr_csr%NOTFOUND THEN
1113 NULL;
1114 END IF;
1115 CLOSE l_chk_rel_khr_csr;
1116
1117 l_rel_ast := '?';
1118 --check for relese assets in a contract
1119 OPEN l_chk_rel_ast_csr (p_chr_id => p_chrv_id);
1120 FETCH l_chk_rel_ast_csr INTO l_rel_ast;
1121 IF l_chk_rel_ast_csr%NOTFOUND THEN
1122 NULL;
1123 END IF;
1124 CLOSE l_chk_rel_ast_csr;
1125
1126 IF l_mass_rbk_khr = '!' THEN
1127 --do mass rebook processing
1128 --bug# 3156924 :
1129 IF nvl(l_mass_rbk_date,OKL_API.G_MISS_DATE) = OKL_API.G_MISS_DATE then
1130 l_mass_rbk_date := sysdate;
1131 End IF;
1132 --Bug# 3156924
1133
1134 -- Start : Bug 6050165 : prasjain
1135 l_subsidy_exists := OKL_API.G_FALSE;
1136 okl_subsidy_process_pvt.is_contract_subsidized
1137 (p_api_version => p_api_version,
1138 p_init_msg_list => p_init_msg_list,
1139 x_return_status => x_return_status,
1140 x_msg_count => x_msg_count,
1141 x_msg_data => x_msg_data,
1142 p_chr_id => p_chrv_id,
1143 x_subsidized => l_subsidy_exists);
1144 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1145 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1146 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1147 RAISE Okl_Api.G_EXCEPTION_ERROR;
1148 END IF;
1149
1150 IF l_subsidy_exists = OKL_API.G_TRUE THEN
1151 l_subsidy_valid_status := OKL_API.G_RET_STS_SUCCESS;
1152 OPEN l_subsidy_csr (p_chr_id => p_chrv_id);
1153 LOOP
1154 FETCH l_subsidy_csr INTO l_subsidy_rec;
1155 EXIT WHEN l_subsidy_csr%NOTFOUND;
1156 l_applicable := 'N';
1157 open l_cle_csr(p_subsidy_id => l_subsidy_rec.subsidy_id,
1158 p_asset_cle_id => l_subsidy_rec.asset_id);
1159 Fetch l_cle_csr into l_applicable;
1160 If l_cle_csr%NOTFOUND then
1161 Null;
1162 End If;
1163 close l_cle_csr;
1164
1165 If (l_applicable = 'N') then
1166 Okl_Api.set_message(
1167 p_app_name => G_APP_NAME,
1168 p_msg_name => 'OKL_SUBSIDY_CRITERIA_MATCH',
1169 p_token1 => 'SUBSIDY_NAME',
1170 p_token1_value => l_subsidy_rec.subsidy_name,
1171 p_token2 => 'ASSET_NUMBER',
1172 p_token2_value => l_subsidy_rec.asset_number);
1173 l_subsidy_valid_status := OKL_API.G_RET_STS_ERROR;
1174 end if;
1175 end loop;
1176 IF (l_subsidy_valid_status = Okl_Api.G_RET_STS_ERROR) THEN
1177 RAISE Okl_Api.G_EXCEPTION_ERROR;
1178 end if;
1179 end if;
1180 -- End : Bug 6050165 : prasjain
1181
1182 -- 4542290
1183 OPEN l_source_trx_type_csr(l_source_trx_id);
1184 FETCH l_source_trx_type_csr
1185 INTO l_source_trx_type;
1186 CLOSE l_source_trx_type_csr;
1187
1188 --dbms_output.put_line('Gone in for rebook.');
1189 Okl_Activate_Asset_Pvt.MASS_REBOOK_ASSET(p_api_version => p_api_version,
1190 p_init_msg_list => p_init_msg_list,
1191 x_return_status => x_return_status,
1192 x_msg_count => x_msg_count,
1193 x_msg_data => x_msg_data,
1194 p_rbk_chr_id => p_chrv_id);
1195 --dbms_output.put_line('Gone in for rebook. '||x_return_status);
1196 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1197 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1198 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1199 RAISE Okl_Api.G_EXCEPTION_ERROR;
1200 END IF;
1201
1202 -- dedey, Bug#4264314
1203 OKL_CONTRACT_REBOOK_PVT.create_billing_adjustment(
1204 p_api_version => p_api_version,
1205 p_init_msg_list => p_init_msg_list,
1206 x_return_status => x_return_status,
1207 x_msg_count => x_msg_count,
1208 x_msg_data => x_msg_data,
1209 p_rbk_khr_id => p_chrv_id,
1210 p_orig_khr_id => p_chrv_id,
1211 p_trx_id => l_mass_rbk_trx_id,
1212 p_trx_date => sysdate); -- 4583578 passing sysdate instead of rebook date
1213
1214 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1215 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1216 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1217 RAISE Okl_Api.G_EXCEPTION_ERROR;
1218 END IF;
1219 -- dedey, Bug#4264314
1220
1221 -- 4542290
1222 -- 4752350 (added NVL as l_source_trx_type is NULL)
1223 IF (nvl(l_source_trx_type,'X') NOT IN ('ALT', 'PPD')) THEN
1224 --generate final booking JE for rebooked contract
1225 --bug# 28355070
1226 okl_la_je_pvt.generate_journal_entries(
1227 p_api_version => p_api_version,
1228 p_init_msg_list => p_init_msg_list,
1229 p_commit => l_commit,
1230 p_contract_id => p_chrv_id,
1231 p_transaction_type => l_transaction_type,
1232 --Bug# 3156924
1233 --p_transaction_date => sysdate,
1234 p_transaction_date => l_mass_rbk_date,
1235 p_draft_yn => l_draft_yn,
1236 p_memo_yn => okl_api.g_true,
1237 x_return_status => x_return_status,
1238 x_msg_count => x_msg_count,
1239 x_msg_data => x_msg_data);
1240
1241 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1242 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1243 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1244 RAISE Okl_Api.G_EXCEPTION_ERROR;
1245 END IF;
1246 END IF;
1247
1248 --bug# 2842342
1249 --call reverse accruals API
1250
1251 --Bug# 3156924
1252 l_gl_date := OKL_ACCOUNTING_UTIL.get_valid_gl_date(p_gl_date => l_mass_rbk_date);
1253
1254 -- Sales Tax Project changes Bug: 4622963 - START
1255
1256 -- Important Note: 'Mass-Rebook' is not a seeded trx type
1257 -- it is a placeholder to distinguish regular 'Rebook'
1258 -- from 'Mass Rebook' process for sales tax, because tax processing
1259 -- is different between the processes. Inside the sales tax process
1260 -- it will be replaced by trx type 'Rebook' before calling tax engine,
1261 -- as both the processes use 'Rebook' as the trx type
1262 --ebtax rebook impacts akrangan start
1263 --remove upfront tax call for rebook
1264 /*
1265 okl_la_sales_tax_pvt.process_sales_tax(
1266 p_api_version => p_api_version,
1267 p_init_msg_list => Okl_Api.G_FALSE,
1268 p_commit => Okl_Api.G_FALSE,
1269 p_contract_id => l_orig_khr_id,
1270 p_transaction_type => 'Mass-Rebook',
1271 p_transaction_id => l_mass_rbk_trx_id,
1272 p_transaction_date => l_mass_rbk_date,
1273 p_rbk_contract_id => l_orig_khr_id,
1274 x_return_status => x_return_status,
1275 x_msg_count => x_msg_count,
1276 x_msg_data => x_msg_data);
1277
1278 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1279 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1280 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1281 RAISE Okl_Api.G_EXCEPTION_ERROR;
1282 END IF;
1283 -- Sales Tax Project changes Bug: 4622963 - END
1284 */
1285 --akrangan end
1286 -- dedey,Bug#4264314
1287 -- This call moved up before JE call
1288 /*
1289
1290 --Bug# 4212626
1291 OKL_CONTRACT_REBOOK_PVT.create_billing_adjustment(
1292 p_api_version => p_api_version,
1293 p_init_msg_list => p_init_msg_list,
1294 x_return_status => x_return_status,
1295 x_msg_count => x_msg_count,
1296 x_msg_data => x_msg_data,
1297 p_rbk_khr_id => p_chrv_id,
1298 p_orig_khr_id => p_chrv_id,
1299 p_trx_id => l_mass_rbk_trx_id,
1300 p_trx_date => sysdate); -- 4583578 passing sysdate instead of rebook date
1301
1302 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1303 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1304 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1305 RAISE Okl_Api.G_EXCEPTION_ERROR;
1306 END IF;
1307 */
1308 -- dedey,Bug#4264314
1309
1310 --Bug# 4212626
1311 -- This call is moved to Okl_Mass_Rebook_Pvt(OKLRMRPB.pls)
1312 -- as the accrual adjustment api requires the Contract
1313 -- status to be 'BOOKED' before accrual adjustments can be
1314 -- generated.
1315 /*
1316 OKL_CONTRACT_REBOOK_PVT.create_accrual_adjustment(
1317 p_api_version => p_api_version,
1318 p_init_msg_list => p_init_msg_list,
1319 x_return_status => x_return_status,
1320 x_msg_count => x_msg_count,
1321 x_msg_data => x_msg_data,
1322 p_rbk_khr_id => p_chrv_id,
1323 p_orig_khr_id => p_chrv_id,
1324 p_trx_id => l_mass_rbk_trx_id,
1325 p_trx_date => l_mass_rbk_date);
1326
1327 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1328 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1329 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1330 RAISE Okl_Api.G_EXCEPTION_ERROR;
1331 END IF;
1332 */
1333
1334 --Bug# 4212626
1335 /*
1336 okl_generate_accruals_pub.REVERSE_ALL_ACCRUALS (
1337 p_api_version => p_api_version,
1338 p_init_msg_list => p_init_msg_list,
1339 p_khr_id => p_chrv_id,
1340 --Bug# 3156924
1341 --p_reverse_date => SYSDATE,
1342 p_reverse_date => l_gl_date,
1343 p_description => 'Call from Rebook API',
1344 x_return_status => x_return_status,
1345 x_msg_count => x_msg_count,
1346 x_msg_data => x_msg_data);
1347
1348 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1349 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1350 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1351 RAISE Okl_Api.G_EXCEPTION_ERROR;
1352 END IF;
1353 */
1354
1355 ELSIF l_rbk_khr = '!' THEN
1356
1357 --Bug# : check if rebook changes are supported
1358 -- check that no changes to add-ons and adjustments
1359 Is_Rebook_Supported(p_rbk_chr_id => p_chrv_id,
1360 p_orig_chr_id => l_orig_khr_id,
1361 x_return_status => x_return_status);
1362
1363 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1364 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1365 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1366 RAISE Okl_Api.G_EXCEPTION_ERROR;
1367 END IF;
1368 --Bug#: End.
1369
1370 --Bug# 3783518 : Allow update of unit cost only if the
1371 -- revision date is the same as contract start date
1372 Validate_Rebook_Date
1373 (p_api_version => p_api_version,
1374 p_init_msg_list => p_init_msg_list,
1375 x_return_status => x_return_status,
1376 x_msg_count => x_msg_count,
1377 x_msg_data => x_msg_data,
1378 p_rbk_chr_id => p_chrv_id,
1379 p_orig_chr_id => l_orig_khr_id,
1380 p_rebook_date => l_rebook_date);
1381 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1382 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1383 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1384 RAISE Okl_Api.G_EXCEPTION_ERROR;
1385 END IF;
1386 --Bug# 3783518: End
1387
1388 --Start : Bug 6050165 : prasjain
1389 l_subsidy_exists := OKL_API.G_FALSE;
1390 okl_subsidy_process_pvt.is_contract_subsidized
1391 (p_api_version => p_api_version,
1392 p_init_msg_list => p_init_msg_list,
1393 x_return_status => x_return_status,
1394 x_msg_count => x_msg_count,
1395 x_msg_data => x_msg_data,
1396 p_chr_id => p_chrv_id,
1397 x_subsidized => l_subsidy_exists);
1398 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1399 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1400 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1401 RAISE Okl_Api.G_EXCEPTION_ERROR;
1402 END IF;
1403
1404 IF l_subsidy_exists = OKL_API.G_TRUE THEN
1405 l_subsidy_valid_status := OKL_API.G_RET_STS_SUCCESS;
1406 OPEN l_subsidy_csr (p_chr_id => p_chrv_id);
1407 LOOP
1408 FETCH l_subsidy_csr INTO l_subsidy_rec;
1409 EXIT WHEN l_subsidy_csr%NOTFOUND;
1410 l_applicable := 'N';
1411 open l_cle_csr(p_subsidy_id => l_subsidy_rec.subsidy_id,
1412 p_asset_cle_id => l_subsidy_rec.asset_id);
1413 Fetch l_cle_csr into l_applicable;
1414 If l_cle_csr%NOTFOUND then
1415 Null;
1416 End If;
1417 close l_cle_csr;
1418
1419 If (l_applicable = 'N') then
1420 Okl_Api.set_message(
1421 p_app_name => G_APP_NAME,
1422 p_msg_name => 'OKL_SUBSIDY_CRITERIA_MATCH',
1423 p_token1 => 'SUBSIDY_NAME',
1424 p_token1_value => l_subsidy_rec.subsidy_name,
1425 p_token2 => 'ASSET_NUMBER',
1426 p_token2_value => l_subsidy_rec.asset_number);
1427 l_subsidy_valid_status := OKL_API.G_RET_STS_ERROR;
1428 end if;
1429 end loop;
1430 IF (l_subsidy_valid_status = Okl_Api.G_RET_STS_ERROR) THEN
1431 RAISE Okl_Api.G_EXCEPTION_ERROR;
1432 end if;
1433 end if;
1434 --End : Bug 6050165 : prasjain
1435
1436 --4542290
1437 l_trx_date := l_rebook_date;
1438 l_accrual_date := l_rebook_date;
1439
1440 okl_k_rate_params_pvt.get_product(
1441 p_api_version => p_api_version,
1442 p_init_msg_list => p_init_msg_list,
1443 x_return_status => x_return_status,
1444 x_msg_count => x_msg_count,
1445 x_msg_data => x_msg_data,
1446 p_khr_id => p_chrv_id,
1447 x_pdt_parameter_rec => l_pdt_parameter_rec);
1448
1449 IF (l_pdt_parameter_rec.interest_calculation_basis = 'REAMORT' AND
1450 l_pdt_parameter_rec.revenue_recognition_method = 'STREAMS' ) THEN
1451 -- Change l_trx_date accordingly
1452 get_stream_id
1453 (
1454 p_khr_id => p_chrv_id,
1455 p_stream_type_purpose => 'RENT',
1456 x_return_status => x_return_status,
1457 x_sty_id => l_sty_id
1458 );
1459 IF x_return_status <> 'S' THEN
1460 okl_api.set_message(p_app_name => g_app_name,
1461 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
1462 p_token1 => 'STREAM_NAME',
1463 p_token1_value => 'RENT');
1464 RAISE okl_api.g_exception_error;
1465 END IF;
1466
1467 l_max_bill_date := NULL;
1468 OPEN max_bill_date_csr(p_stm_id => l_sty_id );
1469 FETCH max_bill_date_csr INTO l_max_bill_date;
1470 CLOSE max_bill_date_csr;
1471 IF (l_max_bill_date IS NOT NULL) THEN
1472 l_trx_date := l_max_bill_date;
1473 END IF;
1474
1475 IF (l_pdt_parameter_rec.deal_type = 'LEASEOP') THEN
1476 l_stream_name := 'RENT_ACCRUAL';
1477 ELSIF (l_pdt_parameter_rec.deal_type IN ('LEASEDF','LEASEST')) THEN
1478 l_stream_name := 'LEASE_INCOME'; -- AKP: Check 'LEASE_INCOME'?
1479 ELSIF (l_pdt_parameter_rec.deal_type = 'LOAN') THEN
1480 l_stream_name := 'INTEREST_INCOME'; -- AKP: Check
1481 END IF;
1482 get_stream_id
1483 (
1484 p_khr_id => p_chrv_id,
1485 p_stream_type_purpose => l_stream_name,
1486 x_return_status => x_return_status,
1487 x_sty_id => l_sty_id
1488 );
1489 IF x_return_status <> 'S' THEN
1490 okl_api.set_message(p_app_name => g_app_name,
1491 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
1492 p_token1 => 'STREAM_NAME',
1493 p_token1_value => l_stream_name);
1494 RAISE okl_api.g_exception_error;
1495 END IF;
1496
1497 l_max_bill_date := NULL;
1498 OPEN max_bill_date_csr(p_stm_id => l_sty_id );
1499 FETCH max_bill_date_csr INTO l_max_bill_date;
1500 CLOSE max_bill_date_csr;
1501 IF (l_max_bill_date IS NOT NULL) THEN
1502 l_accrual_date := l_max_bill_date;
1503 END IF;
1504
1505 END IF;
1506 --4542290
1507
1508 --Bug# 4212626
1509 OKL_CONTRACT_REBOOK_PVT.create_billing_adjustment(
1510 p_api_version => p_api_version,
1511 p_init_msg_list => p_init_msg_list,
1512 x_return_status => x_return_status,
1513 x_msg_count => x_msg_count,
1514 x_msg_data => x_msg_data,
1515 p_rbk_khr_id => p_chrv_id,
1516 p_orig_khr_id => l_orig_khr_id,
1517 p_trx_id => l_transaction_id,
1518 p_trx_date => sysdate); -- 4583578 passing sysdate instead of rebook date
1519 --4542290 p_trx_date => l_rebook_date);
1520
1521 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1522 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1523 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1524 RAISE Okl_Api.G_EXCEPTION_ERROR;
1525 END IF;
1526
1527 -- dedey,Bug#4264314
1528 /*
1529 --Bug# 4212626
1530 OKL_CONTRACT_REBOOK_PVT.create_accrual_adjustment(
1531 p_api_version => p_api_version,
1532 p_init_msg_list => p_init_msg_list,
1533 x_return_status => x_return_status,
1534 x_msg_count => x_msg_count,
1535 x_msg_data => x_msg_data,
1536 p_rbk_khr_id => p_chrv_id,
1537 p_orig_khr_id => l_orig_khr_id,
1538 p_trx_id => l_transaction_id,
1539 p_trx_date => l_rebook_date);
1540
1541 */
1542 OKL_STREAMS_SEC_PVT.RESET_REPO_STREAMS; -- MGAAP 7263041
1543 OKL_CONTRACT_REBOOK_PVT.calc_accrual_adjustment(
1544 p_api_version => p_api_version,
1545 p_init_msg_list => p_init_msg_list,
1546 x_return_status => x_return_status,
1547 x_msg_count => x_msg_count,
1548 x_msg_data => x_msg_data,
1549 p_rbk_khr_id => p_chrv_id,
1550 p_orig_khr_id => l_orig_khr_id,
1551 p_trx_id => l_transaction_id,
1552 --4542290 p_trx_date => l_rebook_date,
1553 p_trx_date => sysdate, -- 4583578 passing sysdate instead of rebook_date
1554 x_accrual_rec => l_accrual_rec,
1555 x_stream_tbl => l_stream_tbl);
1556
1557 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1558 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1559 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1560 RAISE Okl_Api.G_EXCEPTION_ERROR;
1561 END IF;
1562
1563 IF (l_stream_tbl.COUNT > 0) THEN -- Call adjustment when required, dedey
1564 OKL_GENERATE_ACCRUALS_PVT.ADJUST_ACCRUALS (
1565 p_api_version => p_api_version,
1566 p_init_msg_list => p_init_msg_list,
1567 x_return_status => x_return_status,
1568 x_msg_count => x_msg_count,
1569 x_msg_data => x_msg_data ,
1570 x_trx_number => lx_trx_number,
1571 p_accrual_rec => l_accrual_rec,
1572 p_stream_tbl => l_stream_tbl);
1573
1574 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1575 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1576 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1577 RAISE Okl_Api.G_EXCEPTION_ERROR;
1578 END IF;
1579
1580 END IF; -- l_stream_tbl.COUNT > 0
1581
1582 -- Start MGAAP 7263041
1583 OKL_STREAMS_SEC_PVT.SET_REPO_STREAMS;
1584 OKL_CONTRACT_REBOOK_PVT.calc_accrual_adjustment(
1585 p_api_version => p_api_version,
1586 p_init_msg_list => p_init_msg_list,
1587 x_return_status => x_return_status,
1588 x_msg_count => x_msg_count,
1589 x_msg_data => x_msg_data,
1590 p_rbk_khr_id => p_chrv_id,
1591 p_orig_khr_id => l_orig_khr_id,
1592 p_trx_id => l_transaction_id,
1593 --4542290 p_trx_date => l_rebook_date,
1594 p_trx_date => sysdate, -- 4583578 passing sysdate instead of rebook_date
1595 x_accrual_rec => l_accrual_rec,
1596 x_stream_tbl => l_stream_tbl);
1597
1598 OKL_STREAMS_SEC_PVT.RESET_REPO_STREAMS;
1599 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1600 OKL_STREAMS_SEC_PVT.RESET_REPO_STREAMS;
1601 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1602 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1603 OKL_STREAMS_SEC_PVT.RESET_REPO_STREAMS;
1604 RAISE Okl_Api.G_EXCEPTION_ERROR;
1605 END IF;
1606
1607 IF (l_stream_tbl.COUNT > 0) THEN -- Call adjustment when required, dedey
1608 l_accrual_rec.trx_number := lx_trx_number;
1609 OKL_GENERATE_ACCRUALS_PVT.ADJUST_ACCRUALS (
1610 p_api_version => p_api_version,
1611 p_init_msg_list => p_init_msg_list,
1612 x_return_status => x_return_status,
1613 x_msg_count => x_msg_count,
1614 x_msg_data => x_msg_data ,
1615 x_trx_number => lx_trx_number,
1616 p_accrual_rec => l_accrual_rec,
1617 p_stream_tbl => l_stream_tbl,
1618 p_representation_type => 'SECONDARY');
1619
1620 OKL_STREAMS_SEC_PVT.RESET_REPO_STREAMS;
1621
1622 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1623 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1624 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1625 RAISE Okl_Api.G_EXCEPTION_ERROR;
1626 END IF;
1627
1628 END IF; -- l_stream_tbl.COUNT > 0
1629 OKL_STREAMS_SEC_PVT.RESET_REPO_STREAMS;
1630 -- End MGAAP 7263041
1631
1632 -- dedey,Bug#4264314
1633
1634 -- call synchronize
1635 --dbms_output.put_line('Into processing for rebooking.');
1636 Okl_Contract_Rebook_Pvt.sync_rebook_orig_contract(
1637 p_api_version => p_api_version,
1638 p_init_msg_list => p_init_msg_list,
1639 x_return_status => x_return_status,
1640 x_msg_count => x_msg_count,
1641 x_msg_data => x_msg_data,
1642 p_rebook_chr_id => p_chrv_id
1643 );
1644 --dbms_output.put_line('Hey 1 first call: '||x_return_status);
1645 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1646 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1647 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1648 RAISE Okl_Api.G_EXCEPTION_ERROR;
1649 END IF;
1650
1651
1652 --dbms_output.put_line('Status1 BEFORE call: '||x_return_status);
1653 -- call synchronize Streams
1654
1655 Okl_Contract_Rebook_Pvt.sync_rebook_stream (
1656 p_api_version => p_api_version,
1657 p_init_msg_list => p_init_msg_list,
1658 x_return_status => x_return_status,
1659 x_msg_count => x_msg_count,
1660 x_msg_data => x_msg_data,
1661 p_chr_id => p_chrv_id,
1662 p_stream_status => NULL
1663 );
1664 --dbms_output.put_line('Status1: '||x_return_status);
1665
1666 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1667 --dbms_output.put_line('Status2: '||x_return_status);
1668 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1669 ELSIF (x_return_status = Okc_Api.G_RET_STS_ERROR) THEN
1670 --dbms_output.put_line('Status3: '||x_return_status);
1671 RAISE Okl_Api.G_EXCEPTION_ERROR;
1672 END IF;
1673 --dbms_output.put_line('Status4: '||x_return_status);
1674
1675 --call rebook api
1676
1677 Okl_Activate_Asset_Pub.REBOOK_ASSET(p_api_version => p_api_version,
1678 p_init_msg_list => p_init_msg_list,
1679 x_return_status => x_return_status,
1680 x_msg_count => x_msg_count,
1681 x_msg_data => x_msg_data,
1682 p_rbk_chr_id => p_chrv_id);
1683 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1684 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1685 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1686 RAISE Okl_Api.G_EXCEPTION_ERROR;
1687 END IF;
1688
1689 ---------------------------------------------------------------------------------------
1690 --Bug# 3143522: Subsidies enhancement : Create billing transaction for 'BILL' subsidies
1691 ---------------------------------------------------------------------------------------
1692 l_subsidy_exists := OKL_API.G_FALSE;
1693 okl_subsidy_process_pvt.is_contract_subsidized
1694 (p_api_version => p_api_version,
1695 p_init_msg_list => p_init_msg_list,
1696 x_return_status => x_return_status,
1697 x_msg_count => x_msg_count,
1698 x_msg_data => x_msg_data,
1699 p_chr_id => p_chrv_id,
1700 x_subsidized => l_subsidy_exists);
1701 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1702 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1703 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1704 RAISE Okl_Api.G_EXCEPTION_ERROR;
1705 END IF;
1706
1707 If l_subsidy_exists = OKL_API.G_FALSE Then
1708 okl_subsidy_process_pvt.is_contract_subsidized
1709 (p_api_version => p_api_version,
1710 p_init_msg_list => p_init_msg_list,
1711 x_return_status => x_return_status,
1712 x_msg_count => x_msg_count,
1713 x_msg_data => x_msg_data,
1714 p_chr_id => l_orig_khr_id,
1715 x_subsidized => l_subsidy_exists);
1716 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1717 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1718 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1719 RAISE Okl_Api.G_EXCEPTION_ERROR;
1720 END IF;
1721 Elsif l_subsidy_exists = OKL_API.G_TRUE then
1722 Null;
1723 End If;
1724
1725 If l_subsidy_exists = OKL_API.G_TRUE then
1726 -- varangan - Bug#5474059 - Modified - Start
1727 -- Subsidy billing during rebook done before actual
1728 -- synchronization with main contract. This is to ensure that
1729 -- the subsidy amount differences can be used to generate invoices/
1730 -- credit memo as is the case
1731 --call process API to create Billing transactions for Subsidies ;
1732 --call process API to create Billing transactions for Subsidies ;
1733 OKL_SUBSIDY_PROCESS_PVT.CREATE_BILLING_TRX(
1734 p_api_version => p_api_version,
1735 p_init_msg_list => p_init_msg_list,
1736 x_return_status => x_return_status,
1737 x_msg_count => x_msg_count,
1738 x_msg_data => x_msg_data,
1739 p_chr_id => p_chrv_id);
1740
1741 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1742 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1743 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1744 RAISE Okl_Api.G_EXCEPTION_ERROR;
1745 END IF;
1746 -- varangan - Bug#5474059 - Modified - End
1747
1748 --do rebook synchronize for subsidies
1749 OKL_SUBSIDY_PROCESS_PVT.rebook_synchronize
1750 (p_api_version => p_api_version,
1751 p_init_msg_list => p_init_msg_list,
1752 x_return_status => x_return_status,
1753 x_msg_count => x_msg_count,
1754 x_msg_data => x_msg_data,
1755 p_rbk_chr_id => p_chrv_id,
1756 p_orig_chr_id => l_orig_khr_id);
1757
1758 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1759 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1760 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1761 RAISE Okl_Api.G_EXCEPTION_ERROR;
1762 END IF;
1763
1764
1765 End If;
1766 -------------------------------------------------------------------------------------------
1767 --Bug# 3143522: End Subsidies enhancement : Create billing transaction for 'BILL' subsidies
1768 --------------------------------------------------------------------------------------------
1769
1770 --bug# 28355070
1771 --generate final booking JE for synced original contract
1772 okl_la_je_pvt.generate_journal_entries(
1773 p_api_version => p_api_version,
1774 p_init_msg_list => p_init_msg_list,
1775 p_commit => l_commit,
1776 p_contract_id => l_orig_khr_id,
1777 p_transaction_type => l_transaction_type,
1778 p_transaction_date => l_rebook_date,
1779 p_draft_yn => l_draft_yn,
1780 p_memo_yn => okl_api.g_true,
1781 x_return_status => x_return_status,
1782 x_msg_count => x_msg_count,
1783 x_msg_data => x_msg_data);
1784
1785 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1786 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1787 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1788 RAISE Okl_Api.G_EXCEPTION_ERROR;
1789 END IF;
1790
1791 --Bug# 3397688 : Call function to get valid open period date
1792 l_gl_date := OKL_ACCOUNTING_UTIL.get_valid_gl_date(p_gl_date => l_rebook_date);
1793
1794 -- Sales Tax Project changes Bug: 4622963 - START
1795 okl_la_sales_tax_pvt.process_sales_tax(
1796 p_api_version => p_api_version,
1797 p_init_msg_list => Okl_Api.G_FALSE,
1798 p_commit => Okl_Api.G_FALSE,
1799 p_contract_id => l_orig_khr_id,
1800 p_transaction_type => 'Rebook',
1801 p_transaction_id => l_transaction_id,
1802 p_transaction_date => l_rebook_date,
1803 p_rbk_contract_id => p_chrv_id,
1804 x_return_status => x_return_status,
1805 x_msg_count => x_msg_count,
1806 x_msg_data => x_msg_data);
1807
1808 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1809 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1810 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1811 RAISE Okl_Api.G_EXCEPTION_ERROR;
1812 END IF;
1813 -- Sales Tax Project changes Bug: 4622963 - END
1814
1815 --Bug# 4212626
1816 /*
1817 --bug# 2842342
1818 --call reverse accruals API
1819 okl_generate_accruals_pub.REVERSE_ALL_ACCRUALS (
1820 p_api_version => p_api_version,
1821 p_init_msg_list => p_init_msg_list,
1822 p_khr_id => l_orig_khr_id,
1823 --Bug# 3397688 :
1824 p_reverse_date => l_gl_date,
1825 --p_reverse_date => l_rebook_date,
1826 p_description => 'Call from Rebook API',
1827 x_return_status => x_return_status,
1828 x_msg_count => x_msg_count,
1829 x_msg_data => x_msg_data);
1830
1831 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1832 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1833 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1834 RAISE Okl_Api.G_EXCEPTION_ERROR;
1835 END IF;
1836 */
1837
1838 --Bug#5955320
1839 --call insurance API
1840 OKL_INSURANCE_POLICIES_PUB.cancel_create_policies(
1841 p_api_version => p_api_version,
1842 p_init_msg_list => OKL_API.G_FALSE,
1843 x_return_status => x_return_status,
1844 x_msg_count => x_msg_count,
1845 x_msg_data => x_msg_data,
1846 p_khr_id => l_orig_khr_id,
1847 p_cancellation_date => l_rebook_date,
1848 p_transaction_id => l_transaction_id,
1849 x_ignore_flag => l_ignore_flag);
1850
1851 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1852 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1853 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1854 If (l_ignore_flag = OKL_API.G_FALSE) then
1855 RAISE Okl_Api.G_EXCEPTION_ERROR;
1856 End If;
1857 END IF;
1858
1859 --Bug#3278666 : 11.5.10 call Asset management API to invalidate all valid quotes
1860 OKL_AM_INTEGRATION_PVT.cancel_termination_quotes(
1861 p_api_version => p_api_version,
1862 p_init_msg_list => p_init_msg_list,
1863 x_return_status => x_return_status,
1864 x_msg_count => x_msg_count,
1865 x_msg_data => x_msg_data,
1866 p_khr_id => l_orig_khr_id,
1867 p_source_trx_id => NULL
1868 );
1869 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1870 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1871 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1872 RAISE Okl_Api.G_EXCEPTION_ERROR;
1873 END IF;
1874 --Bug# 3278666 : 11.5.10 call Asset management API to invalidate all valid quotes
1875
1876 -- PPD
1877 -- Cancel un-accepted PPD transaction during rebook of a contract
1878 --
1879 OKL_CS_PRINCIPAL_PAYDOWN_PUB.CANCEL_PPD(
1880 p_api_version => p_api_version
1881 ,p_init_msg_list => p_init_msg_list
1882 ,x_return_status => x_return_status
1883 ,x_msg_count => x_msg_count
1884 ,x_msg_data => x_msg_data
1885 ,p_khr_id => l_orig_khr_id
1886 );
1887
1888 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1889 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1890 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1891 RAISE Okl_Api.G_EXCEPTION_ERROR;
1892 END IF;
1893 -- End PPD Cancel
1894
1895 --update transaction status
1896 Okl_Transaction_Pub.update_trx_status(
1897 p_api_version => p_api_version,
1898 p_init_msg_list => p_init_msg_list,
1899 x_return_status => x_return_status,
1900 x_msg_count => x_msg_count,
1901 x_msg_data => x_msg_data,
1902 p_chr_id => p_chrv_id,
1903 p_status => 'PROCESSED',
1904 x_tcnv_rec => l_tcnv_rec
1905 );
1906 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1907 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1908 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1909 RAISE Okl_Api.G_EXCEPTION_ERROR;
1910 END IF;
1911
1912 --change the rebook copy contract status to 'ABANDONED'
1913 okl_contract_status_pub.update_contract_status(
1914 p_api_version => p_api_version,
1915 p_init_msg_list => p_init_msg_list,
1916 x_return_status => x_return_status,
1917 x_msg_count => x_msg_count,
1918 x_msg_data => x_msg_data,
1919 p_khr_status => 'ABANDONED',
1920 p_chr_id => p_chrv_id);
1921 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1922 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1923 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1924 RAISE Okl_Api.G_EXCEPTION_ERROR;
1925 END IF;
1926
1927 --cascade status to lines
1928 okl_contract_status_pub.cascade_lease_status(
1929 p_api_version => p_api_version,
1930 p_init_msg_list => p_init_msg_list,
1931 x_return_status => x_return_status,
1932 x_msg_count => x_msg_count,
1933 x_msg_data => x_msg_data,
1934 p_chr_id => p_chrv_id);
1935
1936 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1937 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1938 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1939 RAISE Okl_Api.G_EXCEPTION_ERROR;
1940 END IF;
1941
1942 -- Bug# 6071566 - Added Start
1943 -- Re-assess the earliest stream bill date on contract after rebook to
1944 -- take into account any changes in payment structure.
1945 OKL_BILLING_CONTROLLER_PVT.track_next_bill_date(l_orig_khr_id);
1946 -- Bug# 6071566 - Added End
1947
1948
1949 ELSIF l_rel_khr = '!' Then
1950 -- call contract release api
1951 okl_release_pub.activate_release_contract(
1952 p_api_version => p_api_version,
1953 p_init_msg_list => p_init_msg_list,
1954 x_return_status => x_return_status,
1955 x_msg_count => x_msg_count,
1956 x_msg_data => x_msg_data,
1957 p_chr_id => p_chrv_id);
1958
1959 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1960 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1961 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1962 RAISE Okl_Api.G_EXCEPTION_ERROR;
1963 END IF;
1964
1965 ELSIF l_rel_ast = '!' Then
1966 /*
1967 dcshanmu 21-Jan-2008 start bug#6688570
1968 Subsidy enhancement added as part of the bug#6688570. As per the update
1969 given by the PM *** SRAWLING 11/20/07 10:16 am ***, the subsidy needs
1970 to be billed even if the contract is for re-leased assets and the amount
1971 needs to be tracked to a subsidy pool.
1972 */
1973 ---------------------------------------------------------------------------------------
1974 --Bug# 3143522: Subsidies enhancement : Check for expired subsidies and stop activation
1975 -- Create billing transaction for 'BILL' subsidies
1976 ---------------------------------------------------------------------------------------
1977
1978 l_subsidy_exists := OKL_API.G_FALSE;
1979 okl_subsidy_process_pvt.is_contract_subsidized
1980 (p_api_version => p_api_version,
1981 p_init_msg_list => p_init_msg_list,
1982 x_return_status => x_return_status,
1983 x_msg_count => x_msg_count,
1984 x_msg_data => x_msg_data,
1985 p_chr_id => p_chrv_id,
1986 x_subsidized => l_subsidy_exists);
1987 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1988 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1989 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1990 RAISE Okl_Api.G_EXCEPTION_ERROR;
1991 END IF;
1992
1993 IF l_subsidy_exists = OKL_API.G_TRUE THEN
1994 ---------------------------------------------------
1995 --check if expired subsidies exist for the contract
1996 ---------------------------------------------------
1997 l_subsidy_valid_status := OKL_API.G_RET_STS_SUCCESS;
1998 OPEN l_subsidy_csr (p_chr_id => p_chrv_id);
1999 LOOP
2000 FETCH l_subsidy_csr INTO l_subsidy_rec;
2001 EXIT WHEN l_subsidy_csr%NOTFOUND;
2002 --shagarg bug 6032336 start
2003 l_applicable := 'N';
2004 open l_cle_csr(p_subsidy_id => l_subsidy_rec.subsidy_id,
2005 p_asset_cle_id => l_subsidy_rec.asset_id);
2006 Fetch l_cle_csr into l_applicable;
2007 If l_cle_csr%NOTFOUND then
2008 Null;
2009 End If;
2010 close l_cle_csr;
2011
2012 If (l_applicable = 'N') then
2013 Okl_Api.set_message(
2014 p_app_name => G_APP_NAME,
2015 p_msg_name => 'OKL_SUBSIDY_CRITERIA_MATCH',
2016 p_token1 => 'SUBSIDY_NAME',
2017 p_token1_value => l_subsidy_rec.subsidy_name,
2018 p_token2 => 'ASSET_NUMBER',
2019 p_token2_value => l_subsidy_rec.asset_number);
2020 l_subsidy_valid_status := OKL_API.G_RET_STS_ERROR;
2021 end if;
2022
2023 --IF((l_subsidy_rec.effective_to_date + l_subsidy_rec.expire_after_days) < TRUNC(SYSDATE))THEN
2024 if( TRUNC(sysdate) not between TRUNC(l_subsidy_rec.effective_from_date) and
2025 TRUNC(nvl(l_subsidy_rec.EFFECTIVE_TO_DATE,sysdate) + nvl(l_subsidy_rec.EXPIRE_AFTER_DAYS,0)))then
2026 --shagarg bug 6032336 end
2027 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2028 p_msg_name => 'OKL_SUBSIDY_EXPIRED',
2029 p_token1 => 'SUBSIDY',
2030 p_token1_value => l_subsidy_rec.subsidy_name,
2031 p_token2 => 'ASSET_NUMBER',
2032 p_token2_value => l_subsidy_rec.asset_number);
2033 l_subsidy_valid_status := OKL_API.G_RET_STS_ERROR;
2034
2035
2036
2037 ELSE
2038 /*
2039 * sjalasut added code for subsidy pools enhancement, the subsidy is checked for association
2040 * with the subsidy pool, if associated, the authoring transaction api is invoked for
2041 * validation and then create transaction. code logic being merged with subsidy expiration
2042 * because only when subsidy is not expired and is associated with a pool, will the pool transaction occur.
2043 * Further, it is not correct to indicate a validation failure on a subsidy pool transaction while the
2044 * subsidy is actually exipred. START
2045 */
2046 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
2047 okl_debug_pub.log_debug( FND_LOG.LEVEL_STATEMENT, l_module, 'verifying subsidy applicability over pool subsidy id '||l_subsidy_rec.subsidy_id);
2048 END IF;
2049
2050 l_sub_pool_applicable := okl_asset_subsidy_pvt.is_sub_assoc_with_pool(p_subsidy_id => l_subsidy_rec.subsidy_id
2051 ,x_subsidy_pool_id => lx_sub_pool_id
2052 ,x_sub_pool_curr_code => lx_sub_pool_curr_code
2053 );
2054
2055 IF(l_sub_pool_applicable = 'Y')THEN
2056 -- initialize for every iteration of the loop
2057 lv_subsidy_amount := 0;
2058 -- the amount for transaction is either the override amount if present or the calculated subsidy amount
2059 lv_subsidy_amount := NVL(l_subsidy_rec.subsidy_override_amount,NVL(l_subsidy_rec.subsidy_amount,0));
2060 -- write to debug log
2061 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
2062 okl_debug_pub.log_debug( FND_LOG.LEVEL_STATEMENT, l_module, 'pool applicable sub_pool_id '||lx_sub_pool_id||' amount '||lv_subsidy_amount);
2063 END IF;
2064 okl_subsidy_pool_auth_trx_pvt.create_pool_trx_khr_book(p_api_version => p_api_version
2065 ,p_init_msg_list => p_init_msg_list
2066 ,x_return_status => x_return_status
2067 ,x_msg_count => x_msg_count
2068 ,x_msg_data => x_msg_data
2069 ,p_chr_id => p_chrv_id
2070 ,p_asset_id => l_subsidy_rec.asset_id
2071 ,p_subsidy_id => l_subsidy_rec.subsidy_id
2072 ,p_subsidy_pool_id => lx_sub_pool_id
2073 ,p_trx_amount => lv_subsidy_amount
2074 );
2075 l_subsidy_valid_status := x_return_status;
2076 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
2077 okl_debug_pub.log_debug( FND_LOG.LEVEL_STATEMENT, l_module, 'x_return_status being copied into l_subsidy_valid_status '||l_subsidy_valid_status
2078 ||' x_msg_data '||x_msg_data
2079 );
2080 END IF; -- end of write to debug log
2081 END IF; -- end of l_sub_pool_applicable = 'Y'
2082 END IF; -- end of (l_subsidy_rec.effective_to_date + l_subsidy_rec.expire_after_days) < TRUNC(SYSDATE)
2083 /*
2084 * sjalasut added code for subsidy pools enhancement, the subsidy is checked for association
2085 * with the subsidy pool, if associated, the authoring transaction api is invoked for
2086 * validation and then create transaction. END
2087 */
2088 END LOOP;
2089 CLOSE l_subsidy_csr;
2090
2091 x_return_status := l_subsidy_valid_status;
2092
2093 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2094 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2095 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2096 RAISE Okl_Api.G_EXCEPTION_ERROR;
2097 END IF;
2098
2099
2100 --call process API to create Billing transactions for Subsidies ;
2101 OKL_SUBSIDY_PROCESS_PVT.CREATE_BILLING_TRX(
2102 p_api_version => p_api_version,
2103 p_init_msg_list => p_init_msg_list,
2104 x_return_status => x_return_status,
2105 x_msg_count => x_msg_count,
2106 x_msg_data => x_msg_data,
2107 p_chr_id => p_chrv_id);
2108
2109 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2110 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2111 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2112 RAISE Okl_Api.G_EXCEPTION_ERROR;
2113 END IF;
2114 End If;
2115 -------------------------------------------------------------------------
2116 --Bug#i 3143522 : End Subsidies enhancement
2117 -------------------------------------------------------------------------
2118
2119 /* dcshanmu 21-Jan-2008 end bug#6688570 */
2120
2121 -- call the asset release api
2122 okl_activate_asset_pub.RELEASE_ASSET
2123 (p_api_version => p_api_version,
2124 p_init_msg_list => p_init_msg_list,
2125 x_return_status => x_return_status,
2126 x_msg_count => x_msg_count,
2127 x_msg_data => x_msg_data,
2128 p_rel_chr_id => p_chrv_id);
2129
2130 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2131 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2132 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2133 RAISE Okl_Api.G_EXCEPTION_ERROR;
2134 END IF;
2135
2136 --Bug# 3533936 :
2137 --call the install base instance re_lease API
2138 okl_activate_ib_pvt.RELEASE_IB_INSTANCE
2139 (p_api_version => p_api_version,
2140 p_init_msg_list => p_init_msg_list,
2141 x_return_status => x_return_status,
2142 x_msg_count => x_msg_count,
2143 x_msg_data => x_msg_data,
2144 p_rel_chr_id => p_chrv_id);
2145
2146 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2147 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2148 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2149 RAISE Okl_Api.G_EXCEPTION_ERROR;
2150 END IF;
2151
2152 --Bug#2522439 Start
2153 --code added to update status in OKL_ASSET_RETURNS_B
2154 --after the release asset transaction has been processed
2155 OPEN l_asr_csr(p_chrv_id);
2156 LOOP
2157 FETCH l_asr_csr into l_asr_rec;
2158 EXIT When l_asr_csr%NOTFOUND;
2159 l_artv_rec.id := l_asr_rec.asset_return_id;
2160 l_artv_rec.ars_code := 'CANCELLED';
2161 l_artv_rec.like_kind_yn := 'N';
2162 --call to change the release asset status to 'CANCELLED' in asset return
2163 okl_asset_returns_pub.update_asset_returns(
2164 p_api_version => p_api_version
2165 ,p_init_msg_list => p_init_msg_list
2166 ,x_return_status => x_return_status
2167 ,x_msg_count => x_msg_count
2168 ,x_msg_data => x_msg_data
2169 ,p_artv_rec => l_artv_rec
2170 ,x_artv_rec => lx_artv_rec);
2171
2172 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2173 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2174 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2175 RAISE Okl_Api.G_EXCEPTION_ERROR;
2176 END IF;
2177 END LOOP;
2178 CLOSE l_asr_csr;
2179 --Bug#2522439 End
2180
2181 ELSE
2182 --bug# 2953906 :
2183 --check for billto status
2184 Validate_bill_To(p_chr_id => p_chrv_id,
2185 x_return_status => x_return_status);
2186
2187 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2188 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2189 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2190 RAISE Okl_Api.G_EXCEPTION_ERROR;
2191 END IF;
2192 --bug # 2953906 end.
2193
2194 -------------------------------------------------------------------------
2195 --Bug# 3541098: If Capitalize interest flag is set to 'NO', then
2196 -- calculate interest on pre-funding amount upto the
2197 -- booking date of the contract and create
2198 -- Billing transaction for the interest amount.
2199 ------------------------------------------------------------------------
2200 open cap_interest_rul_csr (p_chrv_id);
2201 fetch cap_interest_rul_csr into l_capitalize_interest_flag;
2202 if cap_interest_rul_csr%NOTFOUND then
2203 l_capitalize_interest_flag := 'N';
2204 end if;
2205 close cap_interest_rul_csr;
2206
2207 if NVL(l_capitalize_interest_flag,'N') = 'N' then
2208
2209 open chr_csr(p_chrv_id);
2210 fetch chr_csr into l_contract_number,l_start_date;
2211 close chr_csr;
2212
2213 okl_interest_calc_pub.calc_interest_activate
2214 (p_api_version => p_api_version,
2215 p_init_msg_list => p_init_msg_list,
2216 x_return_status => x_return_status,
2217 x_msg_count => x_msg_count,
2218 x_msg_data => x_msg_data,
2219 p_contract_number => l_contract_number,
2220 p_Activation_date => l_start_date,
2221 x_amount => l_amount,
2222 x_source_id => l_source_id);
2223
2224 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2225 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2226 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2227 RAISE Okl_Api.G_EXCEPTION_ERROR;
2228 END IF;
2229 end if;
2230 -- Bug# 3541098 end.
2231
2232 ---------------------------------------------------------------------------------------
2233 --Bug# 3143522: Subsidies enhancement : Check for expired subsidies and stop activation
2234 -- Create billing transaction for 'BILL' subsidies
2235 ---------------------------------------------------------------------------------------
2236 l_subsidy_exists := OKL_API.G_FALSE;
2237 okl_subsidy_process_pvt.is_contract_subsidized
2238 (p_api_version => p_api_version,
2239 p_init_msg_list => p_init_msg_list,
2240 x_return_status => x_return_status,
2241 x_msg_count => x_msg_count,
2242 x_msg_data => x_msg_data,
2243 p_chr_id => p_chrv_id,
2244 x_subsidized => l_subsidy_exists);
2245 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2246 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2247 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2248 RAISE Okl_Api.G_EXCEPTION_ERROR;
2249 END IF;
2250
2251 IF l_subsidy_exists = OKL_API.G_TRUE THEN
2252 ---------------------------------------------------
2253 --check if expired subsidies exist for the contract
2254 ---------------------------------------------------
2255 l_subsidy_valid_status := OKL_API.G_RET_STS_SUCCESS;
2256 OPEN l_subsidy_csr (p_chr_id => p_chrv_id);
2257 LOOP
2258 FETCH l_subsidy_csr INTO l_subsidy_rec;
2259 EXIT WHEN l_subsidy_csr%NOTFOUND;
2260 --Start : Bug 6050165 : prasjain
2261 l_applicable := 'N';
2262 open l_cle_csr(p_subsidy_id => l_subsidy_rec.subsidy_id,
2263 p_asset_cle_id => l_subsidy_rec.asset_id);
2264 Fetch l_cle_csr into l_applicable;
2265 If l_cle_csr%NOTFOUND then
2266 Null;
2267 End If;
2268 close l_cle_csr;
2269
2270 If (l_applicable = 'N') then
2271 Okl_Api.set_message(
2272 p_app_name => G_APP_NAME,
2273 p_msg_name => 'OKL_SUBSIDY_CRITERIA_MATCH',
2274 p_token1 => 'SUBSIDY_NAME',
2275 p_token1_value => l_subsidy_rec.subsidy_name,
2276 p_token2 => 'ASSET_NUMBER',
2277 p_token2_value => l_subsidy_rec.asset_number);
2278 l_subsidy_valid_status := OKL_API.G_RET_STS_ERROR;
2279 end if;
2280
2281 --IF((l_subsidy_rec.effective_to_date + l_subsidy_rec.expire_after_days) < TRUNC(SYSDATE))THEN
2282 if( TRUNC(sysdate) not between TRUNC(l_subsidy_rec.effective_from_date) and
2283 TRUNC(nvl(l_subsidy_rec.EFFECTIVE_TO_DATE,sysdate) + nvl(l_subsidy_rec.EXPIRE_AFTER_DAYS,0)))then
2284 --End : Bug 6050165 : prasjain
2285 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2286 p_msg_name => 'OKL_SUBSIDY_EXPIRED',
2287 p_token1 => 'SUBSIDY',
2288 p_token1_value => l_subsidy_rec.subsidy_name,
2289 p_token2 => 'ASSET_NUMBER',
2290 p_token2_value => l_subsidy_rec.asset_number);
2291 l_subsidy_valid_status := OKL_API.G_RET_STS_ERROR;
2292 ELSE
2293 /*
2294 * sjalasut added code for subsidy pools enhancement, the subsidy is checked for association
2295 * with the subsidy pool, if associated, the authoring transaction api is invoked for
2296 * validation and then create transaction. code logic being merged with subsidy expiration
2297 * because only when subsidy is not expired and is associated with a pool, will the pool transaction occur.
2298 * Further, it is not correct to indicate a validation failure on a subsidy pool transaction while the
2299 * subsidy is actually exipred. START
2300 */
2301 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
2302 okl_debug_pub.log_debug( FND_LOG.LEVEL_STATEMENT, l_module, 'verifying subsidy applicability over pool subsidy id '||l_subsidy_rec.subsidy_id);
2303 END IF;
2304
2305 l_sub_pool_applicable := okl_asset_subsidy_pvt.is_sub_assoc_with_pool(p_subsidy_id => l_subsidy_rec.subsidy_id
2306 ,x_subsidy_pool_id => lx_sub_pool_id
2307 ,x_sub_pool_curr_code => lx_sub_pool_curr_code
2308 );
2309
2310 IF(l_sub_pool_applicable = 'Y')THEN
2311 -- initialize for every iteration of the loop
2312 lv_subsidy_amount := 0;
2313 -- the amount for transaction is either the override amount if present or the calculated subsidy amount
2314 lv_subsidy_amount := NVL(l_subsidy_rec.subsidy_override_amount,NVL(l_subsidy_rec.subsidy_amount,0));
2315 -- write to debug log
2316 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
2317 okl_debug_pub.log_debug( FND_LOG.LEVEL_STATEMENT, l_module, 'pool applicable sub_pool_id '||lx_sub_pool_id||' amount '||lv_subsidy_amount);
2318 END IF;
2319 okl_subsidy_pool_auth_trx_pvt.create_pool_trx_khr_book(p_api_version => p_api_version
2320 ,p_init_msg_list => p_init_msg_list
2321 ,x_return_status => x_return_status
2322 ,x_msg_count => x_msg_count
2323 ,x_msg_data => x_msg_data
2324 ,p_chr_id => p_chrv_id
2325 ,p_asset_id => l_subsidy_rec.asset_id
2326 ,p_subsidy_id => l_subsidy_rec.subsidy_id
2327 ,p_subsidy_pool_id => lx_sub_pool_id
2328 ,p_trx_amount => lv_subsidy_amount
2329 );
2330 l_subsidy_valid_status := x_return_status;
2331 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
2332 okl_debug_pub.log_debug( FND_LOG.LEVEL_STATEMENT, l_module, 'x_return_status being copied into l_subsidy_valid_status '||l_subsidy_valid_status
2333 ||' x_msg_data '||x_msg_data
2334 );
2335 END IF; -- end of write to debug log
2336 END IF; -- end of l_sub_pool_applicable = 'Y'
2337 END IF; -- end of (l_subsidy_rec.effective_to_date + l_subsidy_rec.expire_after_days) < TRUNC(SYSDATE)
2338 /*
2339 * sjalasut added code for subsidy pools enhancement, the subsidy is checked for association
2340 * with the subsidy pool, if associated, the authoring transaction api is invoked for
2341 * validation and then create transaction. END
2342 */
2343 END LOOP;
2344 CLOSE l_subsidy_csr;
2345
2346 x_return_status := l_subsidy_valid_status;
2347
2348 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2349 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2350 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2351 RAISE Okl_Api.G_EXCEPTION_ERROR;
2352 END IF;
2353
2354
2355
2356
2357 --call process API to create Billing transactions for Subsidies ;
2358 OKL_SUBSIDY_PROCESS_PVT.CREATE_BILLING_TRX(
2359 p_api_version => p_api_version,
2360 p_init_msg_list => p_init_msg_list,
2361 x_return_status => x_return_status,
2362 x_msg_count => x_msg_count,
2363 x_msg_data => x_msg_data,
2364 p_chr_id => p_chrv_id);
2365
2366 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2367 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2368 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2369 RAISE Okl_Api.G_EXCEPTION_ERROR;
2370 END IF;
2371 End If;
2372 -------------------------------------------------------------------------
2373 --Bug#i 3143522 : End Subsidies enhancement
2374 -------------------------------------------------------------------------
2375
2376 --call fa activation API
2377 Okl_Activate_Asset_Pub.ACTIVATE_ASSET(p_api_version => p_api_version,
2378 p_init_msg_list => p_init_msg_list,
2379 x_return_status => x_return_status,
2380 x_msg_count => x_msg_count,
2381 x_msg_data => x_msg_data,
2382 p_chrv_id => p_chrv_id,
2383 p_call_mode => p_call_mode,
2384 x_cimv_tbl => l_cimv_tbl_fa);
2385
2386 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2387 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2388 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2389 RAISE Okl_Api.G_EXCEPTION_ERROR;
2390 END IF;
2391
2392 --Bug# 2726870 : 11.5.9 enhancment Service contracts integration
2393 --call service integration api :
2394 okl_service_integration_pub.initiate_service_booking(
2395 p_api_version => p_api_version,
2396 p_init_msg_list => p_init_msg_list,
2397 x_return_status => x_return_status,
2398 x_msg_count => x_msg_count,
2399 x_msg_data => x_msg_data,
2400 p_okl_chr_id => p_chrv_id);
2401
2402 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2403 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2404 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2405 RAISE Okl_Api.G_EXCEPTION_ERROR;
2406 END IF;
2407
2408 --call ib activation API
2409 Okl_Activate_Ib_Pub.ACTIVATE_IB_INSTANCE(p_api_version => p_api_version,
2410 p_init_msg_list => p_init_msg_list,
2411 x_return_status => x_return_status,
2412 x_msg_count => x_msg_count,
2413 x_msg_data => x_msg_data,
2414 p_chrv_id => p_chrv_id,
2415 p_call_mode => p_call_mode,
2416 x_cimv_tbl => l_cimv_tbl_ib);
2417
2418 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2419 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2420 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2421 RAISE Okl_Api.G_EXCEPTION_ERROR;
2422 END IF;
2423
2424 --check if usage line is there on the contract
2425 l_usage_khr := '?';
2426 OPEN l_chk_usage_csr (p_chr_id => p_chrv_id);
2427 FETCH l_chk_usage_csr INTO l_usage_khr;
2428 IF l_chk_usage_csr%NOTFOUND THEN
2429 NULL;
2430 END IF;
2431 CLOSE l_chk_usage_csr;
2432
2433 IF l_usage_khr = '!' THEN
2434 --call ubb api for service contracts creation
2435 okl_ubb_integration_pub.create_ubb_contract(
2436 p_api_version => p_api_version,
2437 p_init_msg_list => p_init_msg_list,
2438 x_return_status => x_return_status,
2439 x_msg_count => x_msg_count,
2440 x_msg_data => x_msg_data,
2441 p_chr_id => p_chrv_id,
2442 x_chr_id => l_service_chr_id
2443 );
2444
2445 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2446 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2447 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2448 RAISE Okl_Api.G_EXCEPTION_ERROR;
2449 END IF;
2450 End If;
2451
2452 END IF;
2453 Okl_Api.END_ACTIVITY (x_msg_count,x_msg_data );
2454
2455 EXCEPTION
2456 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2457 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(
2458 l_api_name,
2459 G_PKG_NAME,
2460 'OKL_API.G_RET_STS_ERROR',
2461 x_msg_count,
2462 x_msg_data,
2463 '_PUB');
2464 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2465 --dbms_output.put_line('Unexpected Error Routine');
2466 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS(
2467 l_api_name,
2468 G_PKG_NAME,
2469 'Okl_Api.G_RET_STS_UNEXP_ERROR',
2470 x_msg_count,
2471 x_msg_data,
2472 '_PUB');
2473 --dbms_output.put_line('2 Unexpected Error Routine');
2474 WHEN OTHERS THEN
2475 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS(
2476 l_api_name,
2477 G_PKG_NAME,
2478 'OTHERS',
2479 x_msg_count,
2480 x_msg_data,
2481 '_PUB');
2482 END ACTIVATE_CONTRACT;
2483 END OKL_ACTIVATE_CONTRACT_PUB;