[Home] [Help]
PACKAGE BODY: APPS.OKL_SPLIT_CONTRACT_PVT
Source
1 PACKAGE BODY OKL_SPLIT_CONTRACT_PVT AS
2 /* $Header: OKLRSKHB.pls 120.19.12010000.2 2008/10/01 22:40:27 rkuttiya ship $ */
3 -------------------------------------------------------------------------------------------------
4 -- GLOBAL MESSAGE CONSTANTS
5 -------------------------------------------------------------------------------------------------
6 G_NO_MATCHING_RECORD CONSTANT VARCHAR2(200) := 'OKL_LLA_NO_MATCHING_RECORD';
7 G_INVALID_CRITERIA CONSTANT VARCHAR2(200) := 'OKL_LLA_INVALID_CRITERIA';
8 G_COPY_HEADER CONSTANT VARCHAR2(200) := 'OKL_LLA_COPY_HEADER';
9 G_COPY_LINE CONSTANT VARCHAR2(200) := 'OKL_LLA_COPY_LINE';
10 G_FND_APP CONSTANT VARCHAR2(200) := OKL_API.G_FND_APP;
11 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := 'OKL_REQUIRED_VALUE';
12 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_INVALID_VALUE;
13 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
14 G_TOKEN_K_NUM CONSTANT VARCHAR2(200) := 'Contract Number';
15 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
16 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
17 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
18 G_ERROR_NAL_SPK CONSTANT VARCHAR2(200) := 'OKL_LLA_ERROR_NAL_SPK';
19 G_ERROR_QA_CHECK CONSTANT VARCHAR2(200) := 'OKL_LLA_SPK_QA_CHECK';
20 G_ERROR_CLEAN_SPK CONSTANT VARCHAR2(200) := 'OKL_LLA_CLEANUP_SPK';
21 G_ERROR_STR_GEN CONSTANT VARCHAR2(200) := 'OKL_LLA_STRMS_REQ_FLD';
22 G_CNT_REC CONSTANT VARCHAR2(200) := 'OKL_LLA_CNT_REC';
23 G_INVALID_CONTRACT CONSTANT VARCHAR2(200) := 'OKL_LLA_CHR_ID';
24 -------------------------------------------------------------------------------------------------
25 -- GLOBAL EXCEPTION
26 -------------------------------------------------------------------------------------------------
27 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
28 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
29 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
30 G_API_VERSION CONSTANT NUMBER := 1.0;
31 G_SCOPE CONSTANT VARCHAR2(4) := '_PVT';
32 -------------------------------------------------------------------------------------------------
33 -- GLOBAL VARIABLES
34 -------------------------------------------------------------------------------------------------
35 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_SPLIT_CONTRACT_PVT';
36 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
37 G_FIN_LINE_LTY_CODE OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'FREE_FORM1';
38 G_FA_LINE_LTY_CODE OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'FIXED_ASSET';
39 G_INST_LINE_LTY_CODE OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'FREE_FORM2';
40 G_IB_LINE_LTY_CODE OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'INST_ITEM';
41 G_SER_LINE_LTY_CODE OKC_LINE_STYLES_B.LTY_CODE%TYPE := 'SOLD_SERVICE';
42 G_SRL_LINE_LTY_CODE OKC_LINE_STYLES_B.LTY_CODE%TYPE := 'LINK_SERV_ASSET';
43 G_FEE_LINE_LTY_CODE OKC_LINE_STYLES_B.LTY_CODE%TYPE := 'FEE';
44 G_FEL_LINE_LTY_CODE OKC_LINE_STYLES_B.LTY_CODE%TYPE := 'LINK_FEE_ASSET';
45 G_USG_LINE_LTY_CODE OKC_LINE_STYLES_B.LTY_CODE%TYPE := 'USAGE';
46 G_USL_LINE_LTY_CODE OKC_LINE_STYLES_B.LTY_CODE%TYPE := 'LINK_USAGE_ASSET';
47
48 G_LEASE_SCS_CODE OKC_K_HEADERS_V.SCS_CODE%TYPE := 'LEASE';
49 G_LOAN_SCS_CODE OKC_K_HEADERS_V.SCS_CODE%TYPE := 'LOAN';
50 G_TLS_TYPE OKC_LINE_STYLES_V.LSE_TYPE%TYPE := 'TLS';
51 G_DEBUG_SPLIT BOOLEAN := FALSE;
52 ----------------------------------------------------------------------------------------------------
53 SUBTYPE cimv_rec_type IS OKL_OKC_MIGRATION_PVT.cimv_rec_type;
54 SUBTYPE clev_rec_type IS OKL_OKC_MIGRATION_PVT.clev_rec_type;
55 SUBTYPE chrv_rec_type IS OKL_OKC_MIGRATION_PVT.CHRV_REC_TYPE;
56 SUBTYPE khrv_rec_type IS OKL_CONTRACT_PUB.KHRV_REC_TYPE;
57 SUBTYPE klev_rec_type IS OKL_CONTRACT_PUB.klev_rec_type;
58 SUBTYPE trxv_rec_type IS OKL_TRX_ASSETS_PUB.thpv_rec_type;
59 SUBTYPE trxv_tbl_type IS OKL_TRX_ASSETS_PUB.thpv_tbl_type;
60 SUBTYPE talv_rec_type IS OKL_TXL_ASSETS_PUB.tlpv_rec_type;
61 SUBTYPE talv_tbl_type IS OKL_TXL_ASSETS_PUB.tlpv_tbl_type;
62 SUBTYPE txdv_tbl_type IS OKL_TXD_ASSETS_PUB.adpv_tbl_type;
63 SUBTYPE txdv_rec_type IS OKL_TXD_ASSETS_PUB.adpv_rec_type;
64 SUBTYPE itiv_rec_type IS OKL_TXL_ITM_INSTS_PUB.iipv_rec_type;
65 SUBTYPE itiv_tbl_type IS OKL_TXL_ITM_INSTS_PUB.iipv_tbl_type;
66 SUBTYPE rulv_rec_type IS OKL_RULE_PUB.rulv_rec_type;
67 TYPE g_chr_sts_rec IS RECORD (chr_id NUMBER := OKL_API.G_MISS_NUM,
68 sts_code OKC_K_LINES_B.STS_CODE%TYPE);
69 TYPE g_chr_sts_tbl IS TABLE OF g_chr_sts_rec
70 INDEX BY BINARY_INTEGER;
71
72 TYPE g_cle_amt_rec IS RECORD (cle_id NUMBER := OKL_API.G_MISS_NUM,
73 amount NUMBER := OKL_API.G_MISS_NUM,
74 orig_cle_id NUMBER := OKL_API.G_MISS_NUM);
75 TYPE g_cle_amt_tbl IS TABLE OF g_cle_amt_rec
76 INDEX BY BINARY_INTEGER;
77 lt_chr_sts_tbl g_chr_sts_tbl;
78 lt_new_cle_amt_tbl g_cle_amt_tbl;
79 lt_old_cle_amt_tbl g_cle_amt_tbl;
80
81 /*
82 -- mvasudev, 08/23/2004
83 -- Added Constants to enable Business Event
84 */
85 G_WF_EVT_KHR_SPLIT_COMPLETED CONSTANT VARCHAR2(58) := 'oracle.apps.okl.la.lease_contract.split_contract_completed';
86
87 G_WF_ITM_SRC_CONTRACT_ID CONSTANT VARCHAR2(20) := 'SOURCE_CONTRACT_ID';
88 G_WF_ITM_REVISION_DATE CONSTANT VARCHAR2(15) := 'REVISION_DATE';
89 G_WF_ITM_DEST_CONTRACT_ID_1 CONSTANT VARCHAR2(25) := 'DESTINATION_CONTRACT_ID1';
90 G_WF_ITM_DEST_CONTRACT_ID_2 CONSTANT VARCHAR2(25) := 'DESTINATION_CONTRACT_ID2';
91
92 -------------------------------------------------------------------------------------------------
93 -- Start of Commnets
94 -- Badrinath Kuchibholta
95 -- Function Name : get_tasv_rec
96 -- Description : Get Transaction Header Record
97 -- Business Rules :
98 -- Parameters :
99 -- Version :
100 -- End of Commnets
101 FUNCTION get_tasv_rec(p_tas_id IN NUMBER,
102 x_trxv_rec OUT NOCOPY trxv_rec_type)
103 RETURN VARCHAR2
104 IS
105 x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
106 CURSOR c_trxv_rec(p_tas_id NUMBER)
107 IS
108 SELECT ID,
109 OBJECT_VERSION_NUMBER,
110 ICA_ID,
111 ATTRIBUTE_CATEGORY,
112 ATTRIBUTE1,
113 ATTRIBUTE2,
114 ATTRIBUTE3,
115 ATTRIBUTE4,
116 ATTRIBUTE5,
117 ATTRIBUTE6,
118 ATTRIBUTE7,
119 ATTRIBUTE8,
120 ATTRIBUTE9,
121 ATTRIBUTE10,
122 ATTRIBUTE11,
123 ATTRIBUTE12,
124 ATTRIBUTE13,
125 ATTRIBUTE14,
126 ATTRIBUTE15,
127 TAS_TYPE,
128 CREATED_BY,
129 CREATION_DATE,
130 LAST_UPDATED_BY,
131 LAST_UPDATE_DATE,
132 LAST_UPDATE_LOGIN,
133 TSU_CODE,
134 TRY_ID,
135 DATE_TRANS_OCCURRED,
136 TRANS_NUMBER,
137 COMMENTS,
138 REQ_ASSET_ID,
139 TOTAL_MATCH_AMOUNT
140 FROM OKL_TRX_ASSETS
141 WHERE id = p_tas_id;
142 BEGIN
143 OPEN c_trxv_rec(p_tas_id);
144 FETCH c_trxv_rec INTO
145 x_trxv_rec.ID,
146 x_trxv_rec.OBJECT_VERSION_NUMBER,
147 x_trxv_rec.ICA_ID,
148 x_trxv_rec.ATTRIBUTE_CATEGORY,
149 x_trxv_rec.ATTRIBUTE1,
150 x_trxv_rec.ATTRIBUTE2,
151 x_trxv_rec.ATTRIBUTE3,
152 x_trxv_rec.ATTRIBUTE4,
153 x_trxv_rec.ATTRIBUTE5,
154 x_trxv_rec.ATTRIBUTE6,
155 x_trxv_rec.ATTRIBUTE7,
156 x_trxv_rec.ATTRIBUTE8,
157 x_trxv_rec.ATTRIBUTE9,
158 x_trxv_rec.ATTRIBUTE10,
159 x_trxv_rec.ATTRIBUTE11,
160 x_trxv_rec.ATTRIBUTE12,
161 x_trxv_rec.ATTRIBUTE13,
162 x_trxv_rec.ATTRIBUTE14,
163 x_trxv_rec.ATTRIBUTE15,
164 x_trxv_rec.TAS_TYPE,
165 x_trxv_rec.CREATED_BY,
166 x_trxv_rec.CREATION_DATE,
167 x_trxv_rec.LAST_UPDATED_BY,
168 x_trxv_rec.LAST_UPDATE_DATE,
169 x_trxv_rec.LAST_UPDATE_LOGIN,
170 x_trxv_rec.TSU_CODE,
171 x_trxv_rec.TRY_ID,
172 x_trxv_rec.DATE_TRANS_OCCURRED,
173 x_trxv_rec.TRANS_NUMBER,
174 x_trxv_rec.COMMENTS,
175 x_trxv_rec.REQ_ASSET_ID,
176 x_trxv_rec.TOTAL_MATCH_AMOUNT;
177 IF c_trxv_rec%NOTFOUND THEN
178 x_return_status := OKL_API.G_RET_STS_ERROR;
179 END IF;
180 CLOSE c_trxv_rec;
181 RETURN(x_return_status);
182 EXCEPTION
183 WHEN OTHERS THEN
184 -- store SQL error message on message stack for caller
185 OKL_API.set_message(
186 G_APP_NAME,
187 G_UNEXPECTED_ERROR,
188 G_SQLCODE_TOKEN,
189 SQLCODE,
190 G_SQLERRM_TOKEN,
191 SQLERRM);
192 IF c_trxv_rec%ISOPEN THEN
193 CLOSE c_trxv_rec;
194 END IF;
195 -- notify caller of an UNEXPECTED error
196 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
197 RETURN(x_return_status);
198 END get_tasv_rec;
199 ----------------------------------------------------------------------------
200 -- FUNCTION get_rec for: OKC_K_ITEMS_V
201 ---------------------------------------------------------------------------
202 FUNCTION get_rec_cimv(p_cle_id IN OKC_K_ITEMS_V.CLE_ID%TYPE,
203 p_dnz_chr_id IN OKC_K_ITEMS_V.DNZ_CHR_ID%TYPE,
204 x_cimv_rec OUT NOCOPY cimv_rec_type)
205 RETURN VARCHAR2 IS
206 CURSOR okc_cimv_pk_csr(p_cle_id OKC_K_ITEMS_V.CLE_ID%TYPE,
207 p_dnz_chr_id OKC_K_ITEMS_V.DNZ_CHR_ID%TYPE) IS
208 SELECT CIM.ID,
209 CIM.OBJECT_VERSION_NUMBER,
210 CIM.CLE_ID,
211 CIM.CHR_ID,
212 CIM.CLE_ID_FOR,
213 CIM.DNZ_CHR_ID,
214 CIM.OBJECT1_ID1,
215 CIM.OBJECT1_ID2,
216 CIM.JTOT_OBJECT1_CODE,
217 CIM.UOM_CODE,
218 CIM.EXCEPTION_YN,
219 CIM.NUMBER_OF_ITEMS,
220 CIM.UPG_ORIG_SYSTEM_REF,
221 CIM.UPG_ORIG_SYSTEM_REF_ID,
222 CIM.PRICED_ITEM_YN,
223 CIM.CREATED_BY,
224 CIM.CREATION_DATE,
225 CIM.LAST_UPDATED_BY,
226 CIM.LAST_UPDATE_DATE,
227 CIM.LAST_UPDATE_LOGIN
228 FROM okc_k_items_v cim
229 WHERE cim.dnz_chr_id = p_dnz_chr_id
230 AND cim.cle_id = p_cle_id;
231 l_okc_cimv_pk okc_cimv_pk_csr%ROWTYPE;
232 x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
233 BEGIN
234 OPEN okc_cimv_pk_csr(p_cle_id,
235 p_dnz_chr_id);
236 FETCH okc_cimv_pk_csr INTO
237 x_cimv_rec.ID,
238 x_cimv_rec.OBJECT_VERSION_NUMBER,
239 x_cimv_rec.CLE_ID,
240 x_cimv_rec.CHR_ID,
241 x_cimv_rec.CLE_ID_FOR,
242 x_cimv_rec.DNZ_CHR_ID,
243 x_cimv_rec.OBJECT1_ID1,
244 x_cimv_rec.OBJECT1_ID2,
245 x_cimv_rec.JTOT_OBJECT1_CODE,
246 x_cimv_rec.UOM_CODE,
247 x_cimv_rec.EXCEPTION_YN,
248 x_cimv_rec.NUMBER_OF_ITEMS,
249 x_cimv_rec.UPG_ORIG_SYSTEM_REF,
250 x_cimv_rec.UPG_ORIG_SYSTEM_REF_ID,
251 x_cimv_rec.PRICED_ITEM_YN,
252 x_cimv_rec.CREATED_BY,
253 x_cimv_rec.CREATION_DATE,
254 x_cimv_rec.LAST_UPDATED_BY,
255 x_cimv_rec.LAST_UPDATE_DATE,
256 x_cimv_rec.LAST_UPDATE_LOGIN;
257 IF okc_cimv_pk_csr%NOTFOUND THEN
258 x_return_status := OKL_API.G_RET_STS_ERROR;
259 END IF;
260 IF (okc_cimv_pk_csr%ROWCOUNT > 1) THEN
261 x_return_status := OKL_API.G_RET_STS_ERROR;
262 END IF;
263 CLOSE okc_cimv_pk_csr;
264 RETURN(x_return_status);
265 EXCEPTION
266 WHEN OTHERS THEN
267 -- store SQL error message on message stack for caller
268 OKL_API.set_message(
269 G_APP_NAME,
270 G_UNEXPECTED_ERROR,
271 G_SQLCODE_TOKEN,
272 SQLCODE,
273 G_SQLERRM_TOKEN,
274 SQLERRM);
275 -- notify caller of an UNEXPECTED error
276 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
277 -- if the cursor is open
278 IF okc_cimv_pk_csr%ISOPEN THEN
279 CLOSE okc_cimv_pk_csr;
280 END IF;
281 RETURN(x_return_status);
282 END get_rec_cimv;
283 ---------------------------------------------------------------------------------------
284 FUNCTION get_rec_chrv (p_id IN OKC_K_HEADERS_V.ID%TYPE,
285 x_chrv_rec OUT NOCOPY chrv_rec_type)
286 RETURN VARCHAR2 IS
287 CURSOR okc_chrv_pk_csr(p_id OKC_K_HEADERS_V.ID%TYPE) IS
288 SELECT ID,
289 OBJECT_VERSION_NUMBER,
290 SFWT_FLAG,
291 CHR_ID_RESPONSE,
292 CHR_ID_AWARD,
293 INV_ORGANIZATION_ID,
294 STS_CODE,
295 QCL_ID,
296 SCS_CODE,
297 CONTRACT_NUMBER,
298 CURRENCY_CODE,
299 CONTRACT_NUMBER_MODIFIER,
300 ARCHIVED_YN,
301 DELETED_YN,
302 CUST_PO_NUMBER_REQ_YN,
303 PRE_PAY_REQ_YN,
304 CUST_PO_NUMBER,
305 SHORT_DESCRIPTION,
306 COMMENTS,
307 DESCRIPTION,
308 DPAS_RATING,
309 COGNOMEN,
310 TEMPLATE_YN,
311 TEMPLATE_USED,
312 DATE_APPROVED,
313 DATETIME_CANCELLED,
314 AUTO_RENEW_DAYS,
315 DATE_ISSUED,
316 DATETIME_RESPONDED,
317 NON_RESPONSE_REASON,
318 NON_RESPONSE_EXPLAIN,
319 RFP_TYPE,
320 CHR_TYPE,
321 KEEP_ON_MAIL_LIST,
322 SET_ASIDE_REASON,
323 SET_ASIDE_PERCENT,
324 RESPONSE_COPIES_REQ,
325 DATE_CLOSE_PROJECTED,
326 DATETIME_PROPOSED,
327 DATE_SIGNED,
328 DATE_TERMINATED,
329 DATE_RENEWED,
330 TRN_CODE,
331 START_DATE,
332 END_DATE,
333 AUTHORING_ORG_ID,
334 BUY_OR_SELL,
335 ISSUE_OR_RECEIVE,
336 ESTIMATED_AMOUNT,
337 ESTIMATED_AMOUNT_RENEWED,
338 CURRENCY_CODE_RENEWED,
339 UPG_ORIG_SYSTEM_REF,
340 UPG_ORIG_SYSTEM_REF_ID,
341 APPLICATION_ID,
342 ORIG_SYSTEM_SOURCE_CODE,
343 ORIG_SYSTEM_ID1,
344 ORIG_SYSTEM_REFERENCE1,
345 ATTRIBUTE_CATEGORY,
346 ATTRIBUTE1,
347 ATTRIBUTE2,
348 ATTRIBUTE3,
349 ATTRIBUTE4,
350 ATTRIBUTE5,
351 ATTRIBUTE6,
352 ATTRIBUTE7,
353 ATTRIBUTE8,
354 ATTRIBUTE9,
355 ATTRIBUTE10,
356 ATTRIBUTE11,
357 ATTRIBUTE12,
358 ATTRIBUTE13,
359 ATTRIBUTE14,
360 ATTRIBUTE15,
361 CREATED_BY,
362 CREATION_DATE,
363 LAST_UPDATED_BY,
364 LAST_UPDATE_DATE,
365 LAST_UPDATE_LOGIN
366 FROM okc_k_headers_v chrv
367 WHERE chrv.id = p_id;
368 x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
369 BEGIN
370 OPEN okc_chrv_pk_csr (p_id);
371 FETCH okc_chrv_pk_csr INTO
372 x_chrv_rec.ID,
373 x_chrv_rec.OBJECT_VERSION_NUMBER,
374 x_chrv_rec.SFWT_FLAG,
375 x_chrv_rec.CHR_ID_RESPONSE,
376 x_chrv_rec.CHR_ID_AWARD,
377 x_chrv_rec.INV_ORGANIZATION_ID,
378 x_chrv_rec.STS_CODE,
379 x_chrv_rec.QCL_ID,
380 x_chrv_rec.SCS_CODE,
381 x_chrv_rec.CONTRACT_NUMBER,
382 x_chrv_rec.CURRENCY_CODE,
383 x_chrv_rec.CONTRACT_NUMBER_MODIFIER,
384 x_chrv_rec.ARCHIVED_YN,
385 x_chrv_rec.DELETED_YN,
386 x_chrv_rec.CUST_PO_NUMBER_REQ_YN,
387 x_chrv_rec.PRE_PAY_REQ_YN,
388 x_chrv_rec.CUST_PO_NUMBER,
389 x_chrv_rec.SHORT_DESCRIPTION,
390 x_chrv_rec.COMMENTS,
391 x_chrv_rec.DESCRIPTION,
392 x_chrv_rec.DPAS_RATING,
393 x_chrv_rec.COGNOMEN,
394 x_chrv_rec.TEMPLATE_YN,
395 x_chrv_rec.TEMPLATE_USED,
396 x_chrv_rec.DATE_APPROVED,
397 x_chrv_rec.DATETIME_CANCELLED,
398 x_chrv_rec.AUTO_RENEW_DAYS,
399 x_chrv_rec.DATE_ISSUED,
400 x_chrv_rec.DATETIME_RESPONDED,
401 x_chrv_rec.NON_RESPONSE_REASON,
402 x_chrv_rec.NON_RESPONSE_EXPLAIN,
403 x_chrv_rec.RFP_TYPE,
404 x_chrv_rec.CHR_TYPE,
405 x_chrv_rec.KEEP_ON_MAIL_LIST,
406 x_chrv_rec.SET_ASIDE_REASON,
407 x_chrv_rec.SET_ASIDE_PERCENT,
408 x_chrv_rec.RESPONSE_COPIES_REQ,
409 x_chrv_rec.DATE_CLOSE_PROJECTED,
410 x_chrv_rec.DATETIME_PROPOSED,
411 x_chrv_rec.DATE_SIGNED,
412 x_chrv_rec.DATE_TERMINATED,
413 x_chrv_rec.DATE_RENEWED,
414 x_chrv_rec.TRN_CODE,
415 x_chrv_rec.START_DATE,
416 x_chrv_rec.END_DATE,
417 x_chrv_rec.AUTHORING_ORG_ID,
418 x_chrv_rec.BUY_OR_SELL,
419 x_chrv_rec.ISSUE_OR_RECEIVE,
420 x_chrv_rec.ESTIMATED_AMOUNT,
421 x_chrv_rec.ESTIMATED_AMOUNT_RENEWED,
422 x_chrv_rec.CURRENCY_CODE_RENEWED,
423 x_chrv_rec.UPG_ORIG_SYSTEM_REF,
424 x_chrv_rec.UPG_ORIG_SYSTEM_REF_ID,
425 x_chrv_rec.APPLICATION_ID,
426 x_chrv_rec.ORIG_SYSTEM_SOURCE_CODE,
427 x_chrv_rec.ORIG_SYSTEM_ID1,
428 x_chrv_rec.ORIG_SYSTEM_REFERENCE1,
429 x_chrv_rec.ATTRIBUTE_CATEGORY,
430 x_chrv_rec.ATTRIBUTE1,
431 x_chrv_rec.ATTRIBUTE2,
432 x_chrv_rec.ATTRIBUTE3,
433 x_chrv_rec.ATTRIBUTE4,
434 x_chrv_rec.ATTRIBUTE5,
435 x_chrv_rec.ATTRIBUTE6,
436 x_chrv_rec.ATTRIBUTE7,
437 x_chrv_rec.ATTRIBUTE8,
438 x_chrv_rec.ATTRIBUTE9,
439 x_chrv_rec.ATTRIBUTE10,
440 x_chrv_rec.ATTRIBUTE11,
441 x_chrv_rec.ATTRIBUTE12,
442 x_chrv_rec.ATTRIBUTE13,
443 x_chrv_rec.ATTRIBUTE14,
444 x_chrv_rec.ATTRIBUTE15,
445 x_chrv_rec.CREATED_BY,
446 x_chrv_rec.CREATION_DATE,
447 x_chrv_rec.LAST_UPDATED_BY,
448 x_chrv_rec.LAST_UPDATE_DATE,
449 x_chrv_rec.LAST_UPDATE_LOGIN;
450 IF okc_chrv_pk_csr%NOTFOUND THEN
451 x_return_status := OKL_API.G_RET_STS_ERROR;
452 END IF;
453 CLOSE okc_chrv_pk_csr;
454 RETURN(x_return_status);
455 EXCEPTION
456 WHEN OTHERS THEN
457 -- store SQL error message on message stack for caller
458 OKL_API.set_message(
459 G_APP_NAME,
460 G_UNEXPECTED_ERROR,
461 G_SQLCODE_TOKEN,
462 SQLCODE,
463 G_SQLERRM_TOKEN,
464 SQLERRM);
465 -- notify caller of an UNEXPECTED error
466 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
467 -- if the cursor is open
468 IF okc_chrv_pk_csr%ISOPEN THEN
469 CLOSE okc_chrv_pk_csr;
470 END IF;
471 RETURN(x_return_status);
472 END get_rec_chrv;
473 ------------------------------------------------------------------------------------------------
474 FUNCTION get_rec_clev(p_id IN OKC_K_LINES_V.ID%TYPE,
475 x_clev_rec OUT NOCOPY clev_rec_type)
476 RETURN VARCHAR2 IS
477 CURSOR okc_clev_pk_csr (p_cle_id NUMBER) IS
478 SELECT ID,
479 OBJECT_VERSION_NUMBER,
480 SFWT_FLAG,
481 CHR_ID,
482 CLE_ID,
483 LSE_ID,
484 LINE_NUMBER,
485 STS_CODE,
486 DISPLAY_SEQUENCE,
487 TRN_CODE,
488 DNZ_CHR_ID,
489 COMMENTS,
490 ITEM_DESCRIPTION,
491 OKE_BOE_DESCRIPTION,
492 COGNOMEN,
493 HIDDEN_IND,
494 PRICE_UNIT,
495 PRICE_UNIT_PERCENT,
496 PRICE_NEGOTIATED,
497 PRICE_NEGOTIATED_RENEWED,
498 PRICE_LEVEL_IND,
499 INVOICE_LINE_LEVEL_IND,
500 DPAS_RATING,
501 BLOCK23TEXT,
502 EXCEPTION_YN,
503 TEMPLATE_USED,
504 DATE_TERMINATED,
505 NAME,
506 START_DATE,
507 END_DATE,
508 DATE_RENEWED,
509 UPG_ORIG_SYSTEM_REF,
510 UPG_ORIG_SYSTEM_REF_ID,
511 ORIG_SYSTEM_SOURCE_CODE,
512 ORIG_SYSTEM_ID1,
513 ORIG_SYSTEM_REFERENCE1,
514 REQUEST_ID,
515 PROGRAM_APPLICATION_ID,
516 PROGRAM_ID,
517 PROGRAM_UPDATE_DATE,
518 PRICE_LIST_ID,
519 PRICING_DATE,
520 PRICE_LIST_LINE_ID,
521 LINE_LIST_PRICE,
522 ITEM_TO_PRICE_YN,
523 PRICE_BASIS_YN,
524 CONFIG_HEADER_ID,
525 CONFIG_REVISION_NUMBER,
526 CONFIG_COMPLETE_YN,
527 CONFIG_VALID_YN,
528 CONFIG_TOP_MODEL_LINE_ID,
529 CONFIG_ITEM_TYPE,
530 CONFIG_ITEM_ID ,
531 ATTRIBUTE_CATEGORY,
532 ATTRIBUTE1,
533 ATTRIBUTE2,
534 ATTRIBUTE3,
535 ATTRIBUTE4,
536 ATTRIBUTE5,
537 ATTRIBUTE6,
538 ATTRIBUTE7,
539 ATTRIBUTE8,
540 ATTRIBUTE9,
541 ATTRIBUTE10,
542 ATTRIBUTE11,
543 ATTRIBUTE12,
544 ATTRIBUTE13,
545 ATTRIBUTE14,
546 ATTRIBUTE15,
547 CREATED_BY,
548 CREATION_DATE,
549 LAST_UPDATED_BY,
550 LAST_UPDATE_DATE,
551 PRICE_TYPE,
552 CURRENCY_CODE,
553 CURRENCY_CODE_RENEWED,
554 LAST_UPDATE_LOGIN
555 FROM Okc_K_Lines_V
556 WHERE okc_k_lines_v.id = p_cle_id;
557 l_okc_clev_pk okc_clev_pk_csr%ROWTYPE;
558 x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
559 BEGIN
560 -- Get current database values
561 OPEN okc_clev_pk_csr (p_id);
562 FETCH okc_clev_pk_csr INTO
563 x_clev_rec.ID,
564 x_clev_rec.OBJECT_VERSION_NUMBER,
565 x_clev_rec.SFWT_FLAG,
566 x_clev_rec.CHR_ID,
567 x_clev_rec.CLE_ID,
568 x_clev_rec.LSE_ID,
569 x_clev_rec.LINE_NUMBER,
570 x_clev_rec.STS_CODE,
571 x_clev_rec.DISPLAY_SEQUENCE,
572 x_clev_rec.TRN_CODE,
573 x_clev_rec.DNZ_CHR_ID,
574 x_clev_rec.COMMENTS,
575 x_clev_rec.ITEM_DESCRIPTION,
576 x_clev_rec.OKE_BOE_DESCRIPTION,
577 x_clev_rec.COGNOMEN,
578 x_clev_rec.HIDDEN_IND,
579 x_clev_rec.PRICE_UNIT,
580 x_clev_rec.PRICE_UNIT_PERCENT,
581 x_clev_rec.PRICE_NEGOTIATED,
582 x_clev_rec.PRICE_NEGOTIATED_RENEWED,
583 x_clev_rec.PRICE_LEVEL_IND,
584 x_clev_rec.INVOICE_LINE_LEVEL_IND,
585 x_clev_rec.DPAS_RATING,
586 x_clev_rec.BLOCK23TEXT,
587 x_clev_rec.EXCEPTION_YN,
588 x_clev_rec.TEMPLATE_USED,
589 x_clev_rec.DATE_TERMINATED,
590 x_clev_rec.NAME,
591 x_clev_rec.START_DATE,
592 x_clev_rec.END_DATE,
593 x_clev_rec.DATE_RENEWED,
594 x_clev_rec.UPG_ORIG_SYSTEM_REF,
595 x_clev_rec.UPG_ORIG_SYSTEM_REF_ID,
596 x_clev_rec.ORIG_SYSTEM_SOURCE_CODE,
597 x_clev_rec.ORIG_SYSTEM_ID1,
598 x_clev_rec.ORIG_SYSTEM_REFERENCE1,
599 x_clev_rec.request_id,
600 x_clev_rec.program_application_id,
601 x_clev_rec.program_id,
602 x_clev_rec.program_update_date,
603 x_clev_rec.price_list_id,
604 x_clev_rec.pricing_date,
605 x_clev_rec.price_list_line_id,
606 x_clev_rec.line_list_price,
607 x_clev_rec.item_to_price_yn,
608 x_clev_rec.price_basis_yn,
609 x_clev_rec.config_header_id,
610 x_clev_rec.config_revision_number,
611 x_clev_rec.config_complete_yn,
612 x_clev_rec.config_valid_yn,
613 x_clev_rec.config_top_model_line_id,
614 x_clev_rec.config_item_type,
615 x_clev_rec.CONFIG_ITEM_ID ,
616 x_clev_rec.ATTRIBUTE_CATEGORY,
617 x_clev_rec.ATTRIBUTE1,
618 x_clev_rec.ATTRIBUTE2,
619 x_clev_rec.ATTRIBUTE3,
620 x_clev_rec.ATTRIBUTE4,
621 x_clev_rec.ATTRIBUTE5,
622 x_clev_rec.ATTRIBUTE6,
623 x_clev_rec.ATTRIBUTE7,
624 x_clev_rec.ATTRIBUTE8,
625 x_clev_rec.ATTRIBUTE9,
626 x_clev_rec.ATTRIBUTE10,
627 x_clev_rec.ATTRIBUTE11,
628 x_clev_rec.ATTRIBUTE12,
629 x_clev_rec.ATTRIBUTE13,
630 x_clev_rec.ATTRIBUTE14,
631 x_clev_rec.ATTRIBUTE15,
632 x_clev_rec.CREATED_BY,
633 x_clev_rec.CREATION_DATE,
634 x_clev_rec.LAST_UPDATED_BY,
635 x_clev_rec.LAST_UPDATE_DATE,
636 x_clev_rec.PRICE_TYPE,
637 x_clev_rec.CURRENCY_CODE,
638 x_clev_rec.CURRENCY_CODE_RENEWED,
639 x_clev_rec.LAST_UPDATE_LOGIN;
640 IF okc_clev_pk_csr%NOTFOUND THEN
641 x_return_status := OKL_API.G_RET_STS_ERROR;
642 END IF;
643 CLOSE okc_clev_pk_csr;
644 RETURN(x_return_status);
645 EXCEPTION
646 WHEN OTHERS THEN
647 -- store SQL error message on message stack for caller
648 OKL_API.set_message(G_APP_NAME,
649 G_UNEXPECTED_ERROR,
650 G_SQLCODE_TOKEN,
651 SQLCODE,
652 G_SQLERRM_TOKEN,
653 SQLERRM);
654 -- notify caller of an UNEXPECTED error
655 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
656 -- if the cursor is open
657 IF okc_clev_pk_csr%ISOPEN THEN
658 CLOSE okc_clev_pk_csr;
659 END IF;
660 RETURN(x_return_status);
661 END get_rec_clev;
662 -----------------------------------------------------------------------------------------------
663 FUNCTION get_rec_klev(p_id IN OKL_K_LINES_V.ID%TYPE,
664 x_klev_rec OUT NOCOPY klev_rec_type)
665 RETURN VARCHAR2 IS
666 CURSOR okl_k_lines_v_pk_csr (p_kle_id OKL_K_LINES_V.ID%TYPE) IS
667 SELECT ID,
668 OBJECT_VERSION_NUMBER,
669 KLE_ID,
670 STY_ID,
671 PRC_CODE,
672 FCG_CODE,
673 NTY_CODE,
674 ESTIMATED_OEC,
675 LAO_AMOUNT,
676 TITLE_DATE,
677 FEE_CHARGE,
678 LRS_PERCENT,
679 INITIAL_DIRECT_COST,
680 PERCENT_STAKE,
681 PERCENT,
682 EVERGREEN_PERCENT,
683 AMOUNT_STAKE,
684 OCCUPANCY,
685 COVERAGE,
686 RESIDUAL_PERCENTAGE,
687 DATE_LAST_INSPECTION,
688 DATE_SOLD,
689 LRV_AMOUNT,
690 CAPITAL_REDUCTION,
691 DATE_NEXT_INSPECTION_DUE,
692 DATE_RESIDUAL_LAST_REVIEW,
693 DATE_LAST_REAMORTISATION,
694 VENDOR_ADVANCE_PAID,
695 WEIGHTED_AVERAGE_LIFE,
696 TRADEIN_AMOUNT,
697 BOND_EQUIVALENT_YIELD,
698 TERMINATION_PURCHASE_AMOUNT,
699 REFINANCE_AMOUNT,
700 YEAR_BUILT,
701 DELIVERED_DATE,
702 CREDIT_TENANT_YN,
703 DATE_LAST_CLEANUP,
704 YEAR_OF_MANUFACTURE,
705 COVERAGE_RATIO,
706 REMARKETED_AMOUNT,
707 GROSS_SQUARE_FOOTAGE,
708 PRESCRIBED_ASSET_YN,
709 DATE_REMARKETED,
710 NET_RENTABLE,
711 REMARKET_MARGIN,
712 DATE_LETTER_ACCEPTANCE,
713 REPURCHASED_AMOUNT,
714 DATE_COMMITMENT_EXPIRATION,
715 DATE_REPURCHASED,
716 DATE_APPRAISAL,
717 RESIDUAL_VALUE,
718 APPRAISAL_VALUE,
719 SECURED_DEAL_YN,
720 GAIN_LOSS,
721 FLOOR_AMOUNT,
722 RE_LEASE_YN,
723 PREVIOUS_CONTRACT,
724 TRACKED_RESIDUAL,
725 DATE_TITLE_RECEIVED,
726 AMOUNT,
727 ATTRIBUTE_CATEGORY,
728 ATTRIBUTE1,
729 ATTRIBUTE2,
730 ATTRIBUTE3,
731 ATTRIBUTE4,
732 ATTRIBUTE5,
733 ATTRIBUTE6,
734 ATTRIBUTE7,
735 ATTRIBUTE8,
736 ATTRIBUTE9,
737 ATTRIBUTE10,
738 ATTRIBUTE11,
739 ATTRIBUTE12,
740 ATTRIBUTE13,
741 ATTRIBUTE14,
742 ATTRIBUTE15,
743 STY_ID_FOR,
744 CLG_ID,
745 CREATED_BY,
746 CREATION_DATE,
747 LAST_UPDATED_BY,
748 LAST_UPDATE_DATE,
749 LAST_UPDATE_LOGIN,
750 DATE_FUNDING,
751 DATE_FUNDING_REQUIRED,
752 DATE_ACCEPTED,
753 DATE_DELIVERY_EXPECTED,
754 OEC,
755 CAPITAL_AMOUNT,
756 RESIDUAL_GRNTY_AMOUNT,
757 RESIDUAL_CODE,
758 RVI_PREMIUM,
759 CREDIT_NATURE,
760 CAPITALIZED_INTEREST,
761 CAPITAL_REDUCTION_PERCENT,
762 FEE_TYPE
763 FROM OKL_K_LINES_V
764 WHERE OKL_K_LINES_V.id = p_kle_id;
765 l_okl_k_lines_v_pk okl_k_lines_v_pk_csr%ROWTYPE;
766 x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
767 BEGIN
768 -- Get current database values
769 OPEN okl_k_lines_v_pk_csr (p_id);
770 FETCH okl_k_lines_v_pk_csr INTO
771 x_klev_rec.ID,
772 x_klev_rec.OBJECT_VERSION_NUMBER,
773 x_klev_rec.KLE_ID,
774 x_klev_rec.STY_ID,
775 x_klev_rec.PRC_CODE,
776 x_klev_rec.FCG_CODE,
777 x_klev_rec.NTY_CODE,
778 x_klev_rec.ESTIMATED_OEC,
779 x_klev_rec.LAO_AMOUNT,
780 x_klev_rec.TITLE_DATE,
781 x_klev_rec.FEE_CHARGE,
782 x_klev_rec.LRS_PERCENT,
783 x_klev_rec.INITIAL_DIRECT_COST,
784 x_klev_rec.PERCENT_STAKE,
785 x_klev_rec.PERCENT,
786 x_klev_rec.EVERGREEN_PERCENT,
787 x_klev_rec.AMOUNT_STAKE,
788 x_klev_rec.OCCUPANCY,
789 x_klev_rec.COVERAGE,
790 x_klev_rec.RESIDUAL_PERCENTAGE,
791 x_klev_rec.DATE_LAST_INSPECTION,
792 x_klev_rec.DATE_SOLD,
793 x_klev_rec.LRV_AMOUNT,
794 x_klev_rec.CAPITAL_REDUCTION,
795 x_klev_rec.DATE_NEXT_INSPECTION_DUE,
796 x_klev_rec.DATE_RESIDUAL_LAST_REVIEW,
797 x_klev_rec.DATE_LAST_REAMORTISATION,
798 x_klev_rec.VENDOR_ADVANCE_PAID,
799 x_klev_rec.WEIGHTED_AVERAGE_LIFE,
800 x_klev_rec.TRADEIN_AMOUNT,
801 x_klev_rec.BOND_EQUIVALENT_YIELD,
802 x_klev_rec.TERMINATION_PURCHASE_AMOUNT,
803 x_klev_rec.REFINANCE_AMOUNT,
804 x_klev_rec.YEAR_BUILT,
805 x_klev_rec.DELIVERED_DATE,
806 x_klev_rec.CREDIT_TENANT_YN,
807 x_klev_rec.DATE_LAST_CLEANUP,
808 x_klev_rec.YEAR_OF_MANUFACTURE,
809 x_klev_rec.COVERAGE_RATIO,
810 x_klev_rec.REMARKETED_AMOUNT,
811 x_klev_rec.GROSS_SQUARE_FOOTAGE,
812 x_klev_rec.PRESCRIBED_ASSET_YN,
813 x_klev_rec.DATE_REMARKETED,
814 x_klev_rec.NET_RENTABLE,
815 x_klev_rec.REMARKET_MARGIN,
816 x_klev_rec.DATE_LETTER_ACCEPTANCE,
817 x_klev_rec.REPURCHASED_AMOUNT,
818 x_klev_rec.DATE_COMMITMENT_EXPIRATION,
819 x_klev_rec.DATE_REPURCHASED,
820 x_klev_rec.DATE_APPRAISAL,
821 x_klev_rec.RESIDUAL_VALUE,
822 x_klev_rec.APPRAISAL_VALUE,
823 x_klev_rec.SECURED_DEAL_YN,
824 x_klev_rec.GAIN_LOSS,
825 x_klev_rec.FLOOR_AMOUNT,
826 x_klev_rec.RE_LEASE_YN,
827 x_klev_rec.PREVIOUS_CONTRACT,
828 x_klev_rec.TRACKED_RESIDUAL,
829 x_klev_rec.DATE_TITLE_RECEIVED,
830 x_klev_rec.AMOUNT,
831 x_klev_rec.ATTRIBUTE_CATEGORY,
832 x_klev_rec.ATTRIBUTE1,
833 x_klev_rec.ATTRIBUTE2,
834 x_klev_rec.ATTRIBUTE3,
835 x_klev_rec.ATTRIBUTE4,
836 x_klev_rec.ATTRIBUTE5,
837 x_klev_rec.ATTRIBUTE6,
838 x_klev_rec.ATTRIBUTE7,
839 x_klev_rec.ATTRIBUTE8,
840 x_klev_rec.ATTRIBUTE9,
841 x_klev_rec.ATTRIBUTE10,
842 x_klev_rec.ATTRIBUTE11,
843 x_klev_rec.ATTRIBUTE12,
844 x_klev_rec.ATTRIBUTE13,
845 x_klev_rec.ATTRIBUTE14,
846 x_klev_rec.ATTRIBUTE15,
847 x_klev_rec.STY_ID_FOR,
848 x_klev_rec.CLG_ID,
849 x_klev_rec.CREATED_BY,
850 x_klev_rec.CREATION_DATE,
851 x_klev_rec.LAST_UPDATED_BY,
852 x_klev_rec.LAST_UPDATE_DATE,
853 x_klev_rec.LAST_UPDATE_LOGIN,
854 x_klev_rec.DATE_FUNDING,
855 x_klev_rec.DATE_FUNDING_REQUIRED,
856 x_klev_rec.DATE_ACCEPTED,
857 x_klev_rec.DATE_DELIVERY_EXPECTED,
858 x_klev_rec.OEC,
859 x_klev_rec.CAPITAL_AMOUNT,
860 x_klev_rec.RESIDUAL_GRNTY_AMOUNT,
861 x_klev_rec.RESIDUAL_CODE,
862 x_klev_rec.RVI_PREMIUM,
863 x_klev_rec.CREDIT_NATURE,
864 x_klev_rec.CAPITALIZED_INTEREST,
865 x_klev_rec.CAPITAL_REDUCTION_PERCENT,
866 x_klev_rec.FEE_TYPE;
867 IF okl_k_lines_v_pk_csr%NOTFOUND THEN
868 x_return_status := OKL_API.G_RET_STS_ERROR;
869 END IF;
870 CLOSE okl_k_lines_v_pk_csr;
871 RETURN(x_return_status);
872 EXCEPTION
873 WHEN OTHERS THEN
874 -- store SQL error message on message stack for caller
875 OKL_API.set_message(G_APP_NAME,
876 G_UNEXPECTED_ERROR,
877 G_SQLCODE_TOKEN,
878 SQLCODE,
879 G_SQLERRM_TOKEN,
880 SQLERRM);
881 -- notify caller of an UNEXPECTED error
882 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
883 -- if the cursor is open
884 IF okl_k_lines_v_pk_csr%ISOPEN THEN
885 CLOSE okl_k_lines_v_pk_csr;
886 END IF;
887 RETURN(x_return_status);
888 END get_rec_klev;
889 -------------------------------------------------------------------------------------------------
890 -- Start of Commnets
891 -- RaviKiran Addanki
892 -- Procedure Name : check_split_process
893 -- Description : Check the completion state of Split Contract.
894 -- Business Rules :
895 -- Parameters :
896 -- Version :
897 -- End of Commnets
898 PROCEDURE check_split_process (p_api_version IN NUMBER,
899 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
900 x_return_status OUT NOCOPY VARCHAR2,
901 x_msg_count OUT NOCOPY NUMBER,
902 x_msg_data OUT NOCOPY VARCHAR2,
903 x_process_action OUT NOCOPY VARCHAR2,
904 x_transaction_id OUT NOCOPY OKL_TRX_CONTRACTS.ID%TYPE,
905 x_child_chrid1 OUT NOCOPY OKC_K_HEADERS_B.ID%TYPE,
906 x_child_chrid2 OUT NOCOPY OKC_K_HEADERS_B.ID%TYPE,
907 p_contract_id IN OKC_K_HEADERS_V.ID%TYPE) IS
908
909 l_api_name CONSTANT VARCHAR2(30) := 'check_split_process';
910 ln_split_contract1 OKC_K_HEADERS_B.ID%TYPE := NULL;
911 ln_split_contract2 OKC_K_HEADERS_B.ID%TYPE := NULL;
912 ln_transaction_id OKL_TRX_CONTRACTS.ID%TYPE;
913 ln_contract_id NUMBER := 0;
914 lb_value BOOLEAN := FALSE;
915
916
917 -- To check the existence of the contract.
918 CURSOR c_get_contract(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
919 SELECT 1
920 FROM DUAL
921 WHERE EXISTS (SELECT '1'
922 FROM OKC_K_HEADERS_B
923 WHERE ID = p_chr_id);
924
925 -- To get the transaction created for the contract.
926 CURSOR c_get_transaction_id(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
927 SELECT id
928 FROM okl_trx_contracts
929 WHERE khr_id = p_chr_id
930 AND tcn_type = 'SPLC'
931 --rkuttiya added for 12.1.1 Multi GAAP
932 AND representation_type = 'PRIMARY'
933 --
934 AND tsu_code IN ('ENTERED','WORKING','WAITING','SUBMITTED');
935
936 -- To get the contracts created during Split process
937 CURSOR c_get_split_contracts(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
938 SELECT id
939 FROM okc_k_headers_b
940 WHERE orig_system_id1 = p_chr_id
941 AND orig_system_source_code = 'OKL_SPLIT'
942 ORDER BY CREATION_DATE;
943
944 BEGIN
945 x_return_status := OKL_API.G_RET_STS_SUCCESS;
946
947 IF (p_contract_id = OKL_API.G_MISS_NUM OR p_contract_id IS NULL) THEN
948 x_return_status := OKL_API.G_RET_STS_ERROR;
949 OKL_API.set_message(p_app_name => G_APP_NAME,
950 p_msg_name => G_REQUIRED_VALUE,
951 p_token1 => G_COL_NAME_TOKEN,
952 p_token1_value => 'OKC_K_HEADERS_B.ID');
953 -- halt validation as it is a required field
954 RAISE G_EXCEPTION_STOP_VALIDATION;
955 END IF;
956
957 -- Validate the Contract ID.
958 OPEN c_get_contract(p_contract_id);
959 FETCH c_get_contract INTO ln_contract_id;
960 IF c_get_contract%NOTFOUND THEN
961 x_return_status := OKL_API.G_RET_STS_ERROR;
962 OKL_API.set_message(p_app_name => G_APP_NAME,
963 p_msg_name => G_INVALID_CONTRACT);
964 RAISE G_EXCEPTION_HALT_VALIDATION;
965 END IF;
966 CLOSE c_get_contract;
967
968 -- Get the Split transaction Id.
969 OPEN c_get_transaction_id(p_chr_id => p_contract_id);
970 FETCH c_get_transaction_id INTO ln_transaction_id;
971 IF c_get_transaction_id%FOUND THEN
972 FOR r_get_split_contracts IN c_get_split_contracts(p_chr_id => p_contract_id) LOOP
973 IF (NOT lb_value) THEN
974 ln_split_contract1 := r_get_split_contracts.id;
975 END IF;
976 IF (lb_value) THEN
977 ln_split_contract2 := r_get_split_contracts.id;
978 END IF;
979 lb_value := TRUE;
980 END LOOP;
981
982 IF (ln_split_contract1 IS NULL OR ln_split_contract1 = OKL_API.G_MISS_NUM) AND
983 (ln_split_contract2 IS NULL OR ln_split_contract2 = OKL_API.G_MISS_NUM) THEN
984 -- Split transaction created with no contracts. Creation of both Split
985 -- contracts should be possible.
986 x_process_action := 'PROCESS_BOTH';
987 x_transaction_id := ln_transaction_id;
988 x_return_status := OKL_API.G_RET_STS_SUCCESS;
989 ELSIF (ln_split_contract1 IS NOT NULL OR ln_split_contract1 <> OKL_API.G_MISS_NUM) AND
990 (ln_split_contract2 IS NULL OR ln_split_contract2 = OKL_API.G_MISS_NUM) THEN
991 -- One split contract already got created. Split contract process should
992 -- proceed from there.
993 x_process_action := 'PROCESS_SECOND';
994 x_transaction_id := ln_transaction_id;
995 x_child_chrid1 := ln_split_contract1;
996 x_return_status := OKL_API.G_RET_STS_SUCCESS;
997 ELSIF (ln_split_contract1 IS NOT NULL OR ln_split_contract1 <> OKL_API.G_MISS_NUM) AND
998 (ln_split_contract2 IS NOT NULL OR ln_split_contract2 <> OKL_API.G_MISS_NUM) THEN
999 -- Both split contracts are created, direct the user to Summary screen.
1000 x_process_action := 'PROCESS_REVIEW';
1001 x_transaction_id := ln_transaction_id;
1002 x_child_chrid1 := ln_split_contract1;
1003 x_child_chrid2 := ln_split_contract2;
1004 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1005 END IF;
1006 ELSIF c_get_transaction_id%NOTFOUND THEN
1007 OKL_API.set_message(p_app_name => G_APP_NAME,
1008 p_msg_name => G_REQUIRED_VALUE,
1009 p_token1 => G_COL_NAME_TOKEN,
1010 p_token1_value => 'OKL_TRX_CONTRACTS.ID');
1011 -- halt validation as it is a required field
1012 RAISE G_EXCEPTION_STOP_VALIDATION;
1013 END IF;
1014 CLOSE c_get_transaction_id;
1015 EXCEPTION
1016 WHEN G_EXCEPTION_STOP_VALIDATION THEN
1017 IF c_get_transaction_id%ISOPEN THEN
1018 CLOSE c_get_transaction_id;
1019 END IF;
1020 IF c_get_split_contracts%ISOPEN THEN
1021 CLOSE c_get_split_contracts;
1022 END IF;
1023 IF c_get_contract%ISOPEN THEN
1024 CLOSE c_get_contract;
1025 END IF;
1026 x_return_status := OKL_API.G_RET_STS_ERROR;
1027
1028 WHEN OTHERS THEN
1029 IF c_get_transaction_id%ISOPEN THEN
1030 CLOSE c_get_transaction_id;
1031 END IF;
1032 IF c_get_split_contracts%ISOPEN THEN
1033 CLOSE c_get_split_contracts;
1034 END IF;
1035 IF c_get_contract%ISOPEN THEN
1036 CLOSE c_get_contract;
1037 END IF;
1038 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1039 l_api_name,
1040 G_PKG_NAME,
1041 'OTHERS',
1042 x_msg_count,
1043 x_msg_data,
1044 '_PVT');
1045 END check_split_process;
1046 -------------------------------------------------------------------------------------------------
1047 -- Start of Commnets
1048 -- Badrinath Kuchibholta
1049 -- Procedure Name : l_update_contract_header
1050 -- Description : Update Contract Header
1051 -- Business Rules :
1052 -- Parameters :
1053 -- Version :
1054 -- End of Commnets
1055 PROCEDURE l_update_contract_header(p_api_version IN NUMBER,
1056 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1057 x_return_status OUT NOCOPY VARCHAR2,
1058 x_msg_count OUT NOCOPY NUMBER,
1059 x_msg_data OUT NOCOPY VARCHAR2,
1060 p_restricted_update IN VARCHAR2 DEFAULT 'F',
1061 p_chrv_rec IN chrv_rec_type,
1062 p_khrv_rec IN khrv_rec_type,
1063 x_chrv_rec OUT NOCOPY chrv_rec_type,
1064 x_khrv_rec OUT NOCOPY khrv_rec_type) IS
1065 l_api_name CONSTANT VARCHAR2(30) := 'L_UPDATE_CONTRACT_HEADER';
1066 l_chrv_rec chrv_rec_type;
1067 l_khrv_rec khrv_rec_type;
1068 BEGIN
1069 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1070 -- Call start_activity to create savepoint, check compatibility
1071 -- and initialize message list
1072 x_return_status := OKL_API.START_ACTIVITY (
1073 l_api_name
1074 ,p_init_msg_list
1075 ,'_PVT'
1076 ,x_return_status);
1077 -- Check if activity started successfully
1078 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1079 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1080 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1081 RAISE OKL_API.G_EXCEPTION_ERROR;
1082 END IF;
1083 -- get rec for Contract Header
1084 x_return_status := get_rec_chrv(p_id => p_chrv_rec.id,
1085 x_chrv_rec => l_chrv_rec);
1086 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1087 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1088 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1089 RAISE OKL_API.G_EXCEPTION_ERROR;
1090 END IF;
1091 l_chrv_rec.orig_system_source_code := 'OKL_SPLIT';
1092 -- evaluate conditions, build outcomes for true conditions and
1093 -- put them on outcome queue
1094 OKL_CONTRACT_PUB.update_contract_header(p_api_version => p_api_version,
1095 p_init_msg_list => p_init_msg_list,
1096 x_return_status => x_return_status,
1097 x_msg_count => x_msg_count,
1098 x_msg_data => x_msg_data,
1099 p_restricted_update => p_restricted_update,
1100 p_chrv_rec => l_chrv_rec,
1101 p_khrv_rec => l_khrv_rec,
1102 x_chrv_rec => x_chrv_rec,
1103 x_khrv_rec => x_khrv_rec);
1104 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1105 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1106 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1107 RAISE OKL_API.G_EXCEPTION_ERROR;
1108 END IF;
1109 OKL_API.END_ACTIVITY (x_msg_count,
1110 x_msg_data );
1111 EXCEPTION
1112 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1113 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1114 l_api_name,
1115 G_PKG_NAME,
1116 'OKL_API.G_RET_STS_ERROR',
1117 x_msg_count,
1118 x_msg_data,
1119 '_PVT');
1120 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1121 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1122 l_api_name,
1123 G_PKG_NAME,
1124 'OKL_API.G_RET_STS_UNEXP_ERROR',
1125 x_msg_count,
1126 x_msg_data,
1127 '_PVT');
1128 WHEN OTHERS THEN
1129 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1130 l_api_name,
1131 G_PKG_NAME,
1132 'OTHERS',
1133 x_msg_count,
1134 x_msg_data,
1135 '_PVT');
1136 END l_update_contract_header;
1137 -------------------------------------------------------------------------------------------------
1138 -- Start of Commnets
1139 -- Badrinath Kuchibholta
1140 -- Procedure Name : validate_chr_cle_id
1141 -- Description : validation with OKC_K_LINES_V
1142 -- Business Rules :
1143 -- Parameters :
1144 -- Version : 1.0
1145 -- End of Commnets
1146
1147 PROCEDURE validate_chr_cle_id(p_dnz_chr_id IN OKC_K_LINES_V.DNZ_CHR_ID%TYPE,
1148 p_top_line_id IN OKC_K_LINES_V.ID%TYPE,
1149 x_return_status OUT NOCOPY VARCHAR2) IS
1150 ln_dummy NUMBER := 0;
1151 CURSOR c_chr_cle_id_validate(p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE,
1152 p_top_line_id OKC_K_LINES_V.ID%TYPE) IS
1153 SELECT 1
1154 FROM DUAL
1155 WHERE EXISTS (SELECT 1
1156 FROM OKC_SUBCLASS_TOP_LINE stl,
1157 OKC_LINE_STYLES_V lse,
1158 OKC_K_LINES_V cle
1159 WHERE cle.id = p_top_line_id
1160 AND cle.dnz_chr_id = p_dnz_chr_id
1161 AND cle.cle_id IS NULL
1162 AND cle.chr_id = cle.dnz_chr_id
1163 AND cle.lse_id = lse.id
1164 AND lse.lty_code = G_FIN_LINE_LTY_CODE
1165 AND lse.lse_type = G_TLS_TYPE
1166 AND lse.lse_parent_id IS NULL
1167 AND lse.id = stl.lse_id
1168 AND stl.scs_code IN (G_LEASE_SCS_CODE,G_LOAN_SCS_CODE));
1169 BEGIN
1170 -- initialize return status
1171 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1172 -- data is required
1173 IF (p_dnz_chr_id = OKL_API.G_MISS_NUM OR
1174 p_dnz_chr_id IS NULL) AND
1175 (p_top_line_id = OKL_API.G_MISS_NUM OR
1176 p_top_line_id IS NULL) THEN
1177 -- store SQL error message on message stack
1178 OKL_API.set_message(p_app_name => G_APP_NAME,
1179 p_msg_name => G_REQUIRED_VALUE,
1180 p_token1 => G_COL_NAME_TOKEN,
1181 p_token1_value => 'p_dnz_chr_id and p_top_line_id');
1182 -- halt validation as it is a required field
1183 RAISE G_EXCEPTION_STOP_VALIDATION;
1184 ELSIF (p_dnz_chr_id = OKL_API.G_MISS_NUM OR
1185 p_dnz_chr_id IS NULL) OR
1186 (p_top_line_id = OKL_API.G_MISS_NUM OR
1187 p_top_line_id IS NULL) THEN
1188 -- store SQL error message on message stack
1189 OKL_API.set_message(p_app_name => G_APP_NAME,
1190 p_msg_name => G_REQUIRED_VALUE,
1191 p_token1 => G_COL_NAME_TOKEN,
1192 p_token1_value => 'p_dnz_chr_id and p_top_line_id');
1193 -- halt validation as it is a required field
1194 RAISE G_EXCEPTION_STOP_VALIDATION;
1195 END IF;
1196 -- Combination of dnz_chr_id and Top line id should be valid one
1197 OPEN c_chr_cle_id_validate(p_dnz_chr_id => p_dnz_chr_id,
1198 p_top_line_id => p_top_line_id);
1199 IF c_chr_cle_id_validate%NOTFOUND THEN
1200 OKL_API.set_message(p_app_name => G_APP_NAME,
1201 p_msg_name => G_NO_MATCHING_RECORD,
1202 p_token1 => G_COL_NAME_TOKEN,
1203 p_token1_value => 'p_dnz_chr_id and p_top_line_id');
1204 -- halt validation as it has no parent record
1205 RAISE G_EXCEPTION_HALT_VALIDATION;
1206 END IF;
1207 FETCH c_chr_cle_id_validate INTO ln_dummy;
1208 CLOSE c_chr_cle_id_validate;
1209 IF (ln_dummy = 0) THEN
1210 OKL_API.set_message(p_app_name => G_APP_NAME,
1211 p_msg_name => G_NO_MATCHING_RECORD,
1212 p_token1 => G_COL_NAME_TOKEN,
1213 p_token1_value => 'p_dnz_chr_id and p_top_line_id');
1214 -- halt validation as it has no parent record
1215 RAISE G_EXCEPTION_HALT_VALIDATION;
1216 END IF;
1217 EXCEPTION
1218 WHEN G_EXCEPTION_STOP_VALIDATION THEN
1219 -- We are here since the field is required
1220 -- Notify Error
1221 x_return_status := OKL_API.G_RET_STS_ERROR;
1222 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1223 -- If the cursor is open then it has to be closed
1224 IF c_chr_cle_id_validate%ISOPEN THEN
1225 CLOSE c_chr_cle_id_validate;
1226 END IF;
1227 x_return_status := OKL_API.G_RET_STS_ERROR;
1228 WHEN OTHERS THEN
1229 -- store SQL error message on message stack
1230 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1231 p_msg_name => G_UNEXPECTED_ERROR,
1232 p_token1 => G_SQLCODE_TOKEN,
1233 p_token1_value => SQLCODE,
1234 p_token2 => G_SQLERRM_TOKEN,
1235 p_token2_value => SQLERRM);
1236 -- If the cursor is open then it has to be closed
1237 IF c_chr_cle_id_validate%ISOPEN THEN
1238 CLOSE c_chr_cle_id_validate;
1239 END IF;
1240 -- notify caller of an error as UNEXPETED error
1241 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1242 END validate_chr_cle_id;
1243 ----------------------------------------------------------------------------------------------------
1244 -- Start of Commnets
1245 -- Badrinath Kuchibholta
1246 -- Procedure Name : validate_chr_id
1247 -- Description : validation with OKC_K_LINES_V
1248 -- Business Rules :
1249 -- Parameters :
1250 -- Version : 1.0
1251 -- End of Commnets
1252
1253 PROCEDURE validate_chr_id(p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
1254 x_contract_number OUT NOCOPY OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE,
1255 x_return_status OUT NOCOPY VARCHAR2)
1256 IS
1257 CURSOR get_k_number(p_chr_id IN OKC_K_HEADERS_B.ID%TYPE)
1258 IS
1259 SELECT CHR.contract_number
1260 FROM OKC_K_HEADERS_B CHR
1261 WHERE CHR.id = p_chr_id;
1262 BEGIN
1263 -- initialize return status
1264 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1265 -- data is required
1266 IF (p_chr_id = OKL_API.G_MISS_NUM) OR
1267 (p_chr_id IS NULL) THEN
1268 -- store SQL error message on message stack
1269 OKL_API.set_message(p_app_name => G_APP_NAME,
1270 p_msg_name => G_REQUIRED_VALUE,
1271 p_token1 => G_COL_NAME_TOKEN,
1272 p_token1_value => 'OKC_K_HEADERS_V.ID');
1273 -- halt validation as it is a required field
1274 RAISE G_EXCEPTION_STOP_VALIDATION;
1275 END IF;
1276 OPEN get_k_number(p_chr_id);
1277 FETCH get_k_number INTO x_contract_number;
1278 IF get_k_number%NOTFOUND THEN
1279 OKL_API.set_message(p_app_name => G_APP_NAME,
1280 p_msg_name => G_NO_MATCHING_RECORD,
1281 p_token1 => G_COL_NAME_TOKEN,
1282 p_token1_value => 'p_chr_id');
1283 -- halt validation as it has no parent record
1284 RAISE G_EXCEPTION_HALT_VALIDATION;
1285 END IF;
1286 CLOSE get_k_number;
1287 IF (x_contract_number IS NULL) OR
1288 (x_contract_number = OKL_API.G_MISS_CHAR)THEN
1289 OKL_API.set_message(p_app_name => G_APP_NAME,
1290 p_msg_name => G_NO_MATCHING_RECORD,
1291 p_token1 => G_COL_NAME_TOKEN,
1292 p_token1_value => 'p_chr_id');
1293 -- halt validation as it has no parent record
1294 RAISE G_EXCEPTION_HALT_VALIDATION;
1295 END IF;
1296 EXCEPTION
1297 WHEN G_EXCEPTION_STOP_VALIDATION THEN
1298 -- We are here since the field is required
1299 -- Notify Error
1300 x_return_status := OKL_API.G_RET_STS_ERROR;
1301 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1302 -- If the cursor is open then it has to be closed
1303 IF get_k_number%ISOPEN THEN
1304 CLOSE get_k_number;
1305 END IF;
1306 x_return_status := OKL_API.G_RET_STS_ERROR;
1307 WHEN OTHERS THEN
1308 -- store SQL error message on message stack
1309 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1310 p_msg_name => G_UNEXPECTED_ERROR,
1311 p_token1 => G_SQLCODE_TOKEN,
1312 p_token1_value => SQLCODE,
1313 p_token2 => G_SQLERRM_TOKEN,
1314 p_token2_value => SQLERRM);
1315 -- If the cursor is open then it has to be closed
1316 IF get_k_number%ISOPEN THEN
1317 CLOSE get_k_number;
1318 END IF;
1319 -- notify caller of an error as UNEXPETED error
1320 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1321 END validate_chr_id;
1322 --------------------------------------------------------------------------------------------------
1323 FUNCTION get_qcl_id(p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
1324 x_qcl_id OUT NOCOPY NUMBER)
1325 RETURN VARCHAR2 IS
1326 x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
1327 ln_qcl_id OKC_K_HEADERS_B.QCL_ID%TYPE;
1328 ln_chr_id OKC_K_HEADERS_B.ID%TYPE;
1329 CURSOR get_qcl_id_name(p_qcl_name VARCHAR2) IS
1330 SELECT id
1331 FROM okc_qa_check_lists_v
1332 WHERE name = p_qcl_name;
1333
1334 CURSOR get_qcl_id_chr (p_chr_id NUMBER) IS
1335 SELECT NVL(qcl_id,0)
1336 FROM okc_k_headers_b
1337 WHERE id = p_chr_id;
1338 BEGIN
1339 OPEN get_qcl_id_chr(p_chr_id => p_chr_id);
1340 FETCH get_qcl_id_chr INTO ln_qcl_id;
1341 IF (get_qcl_id_chr%NOTFOUND) OR
1342 (ln_qcl_id = 0) THEN
1343 OPEN get_qcl_id_name(p_qcl_name => 'OKL LA QA CHECK LIST');
1344 FETCH get_qcl_id_name INTO ln_qcl_id;
1345 CLOSE get_qcl_id_name;
1346 END IF;
1347 CLOSE get_qcl_id_chr;
1348 x_qcl_id := ln_qcl_id;
1349 RETURN(x_return_status);
1350 EXCEPTION
1351 WHEN OTHERS THEN
1352 -- store SQL error message on message stack for caller
1353 OKL_API.set_message(
1354 G_APP_NAME,
1355 G_UNEXPECTED_ERROR,
1356 G_SQLCODE_TOKEN,
1357 SQLCODE,
1358 G_SQLERRM_TOKEN,
1359 SQLERRM);
1360 IF get_qcl_id_name%ISOPEN THEN
1361 CLOSE get_qcl_id_name;
1362 END IF;
1363 IF get_qcl_id_chr%ISOPEN THEN
1364 CLOSE get_qcl_id_chr;
1365 END IF;
1366 -- notify caller of an UNEXPECTED error
1367 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1368 RETURN(x_return_status);
1369 END get_qcl_id;
1370 -------------------------------------------------------------------------------------------------
1371 -- Start of Commnets
1372 -- Badrinath Kuchibholta
1373 -- Procedure Name : l_copy_contract_header
1374 -- Description : Copy of the contract Header
1375 -- Business Rules :
1376 -- Parameters :
1377 -- Version :
1378 -- End of Commnets
1379 PROCEDURE l_copy_contract_header(p_api_version IN NUMBER,
1380 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1381 x_return_status OUT NOCOPY VARCHAR2,
1382 x_msg_count OUT NOCOPY NUMBER,
1383 x_msg_data OUT NOCOPY VARCHAR2,
1384 p_commit IN VARCHAR2 DEFAULT 'F',
1385 p_old_chr_id IN NUMBER,
1386 p_new_contract_number IN VARCHAR2 DEFAULT NULL,
1387 x_new_header_id OUT NOCOPY OKC_K_HEADERS_V.ID%TYPE) IS
1388 l_api_name CONSTANT VARCHAR2(30) := 'LOCAL_COPY_CONTRACT';
1389 BEGIN
1390 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1391 -- Call start_activity to create savepoint, check compatibility
1392 -- and initialize message list
1393 x_return_status := OKL_API.START_ACTIVITY (
1394 l_api_name
1395 ,p_init_msg_list
1396 ,'_PVT'
1397 ,x_return_status);
1398 -- Check if activity started successfully
1399 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1400 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1401 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1402 RAISE OKL_API.G_EXCEPTION_ERROR;
1403 END IF;
1404 -- TO copy the Copy the contract first
1405 OKL_COPY_CONTRACT_PUB.copy_contract(p_api_version => p_api_version,
1406 p_init_msg_list => p_init_msg_list,
1407 x_return_status => x_return_status,
1408 x_msg_count => x_msg_count,
1409 x_msg_data => x_msg_data,
1410 p_commit => OKL_API.G_FALSE,
1411 p_chr_id => p_old_chr_id,
1412 p_contract_number => p_new_contract_number,
1413 p_contract_number_modifier => NULL,
1414 p_to_template_yn => 'N',
1415 p_renew_ref_yn => 'N',
1416 p_copy_lines_yn => 'N',
1417 p_override_org => 'N',
1418 x_chr_id => x_new_header_id);
1419 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1420 OKL_API.set_message(p_app_name => G_APP_NAME,
1421 p_msg_name => G_COPY_HEADER);
1422 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1423 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1424 OKL_API.set_message(p_app_name => G_APP_NAME,
1425 p_msg_name => G_COPY_HEADER);
1426 RAISE OKL_API.G_EXCEPTION_ERROR;
1427 END IF;
1428 OKL_API.END_ACTIVITY (x_msg_count,
1429 x_msg_data );
1430 EXCEPTION
1431 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1432 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1433 l_api_name,
1434 G_PKG_NAME,
1435 'OKL_API.G_RET_STS_ERROR',
1436 x_msg_count,
1437 x_msg_data,
1438 '_PVT');
1439 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1440 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1441 l_api_name,
1442 G_PKG_NAME,
1443 'OKL_API.G_RET_STS_UNEXP_ERROR',
1444 x_msg_count,
1445 x_msg_data,
1446 '_PVT');
1447 WHEN OTHERS THEN
1448 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1449 l_api_name,
1450 G_PKG_NAME,
1451 'OTHERS',
1452 x_msg_count,
1453 x_msg_data,
1454 '_PVT');
1455 END l_copy_contract_header;
1456 -------------------------------------------------------------------------------------------------
1457 -- Start of Commnets
1458 -- Badrinath Kuchibholta
1459 -- Procedure Name : l_copy_contract_line
1460 -- Description : Copy of the contract Line
1461 -- Business Rules :
1462 -- Parameters :
1463 -- Version :
1464 -- End of Commnets
1465 PROCEDURE l_copy_contract_line(p_api_version IN NUMBER,
1466 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1467 x_return_status OUT NOCOPY VARCHAR2,
1468 x_msg_count OUT NOCOPY NUMBER,
1469 x_msg_data OUT NOCOPY VARCHAR2,
1470 p_commit IN VARCHAR2 DEFAULT 'F',
1471 p_old_k_top_line IN NUMBER,
1472 p_new_header_id IN VARCHAR2,
1473 x_new_k_top_id OUT NOCOPY NUMBER) IS
1474
1475 l_api_name CONSTANT VARCHAR2(30) := 'LOCAL_COPY_CONTRACT';
1476 BEGIN
1477 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1478 -- Call start_activity to create savepoint, check compatibility
1479 -- and initialize message list
1480 x_return_status := OKL_API.START_ACTIVITY (
1481 l_api_name
1482 ,p_init_msg_list
1483 ,'_PVT'
1484 ,x_return_status);
1485 -- Check if activity started successfully
1486 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1487 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1488 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1489 RAISE OKL_API.G_EXCEPTION_ERROR;
1490 END IF;
1491 -- Now the Copy the Asset lines
1492 OKL_COPY_ASSET_PUB.COPY_ASSET_LINES(p_api_version => p_api_version,
1493 p_init_msg_list => p_init_msg_list,
1494 x_return_status => x_return_status,
1495 x_msg_count => x_msg_count,
1496 x_msg_data => x_msg_data,
1497 P_from_cle_id => p_old_k_top_line,
1498 p_to_cle_id => NULL,
1499 p_to_chr_id => p_new_header_id,
1500 p_to_template_yn => 'N',
1501 p_copy_reference => 'COPY',
1502 p_trans_type => 'CSP',
1503 p_copy_line_party_yn => 'Y',
1504 p_renew_ref_yn => 'N',
1505 x_cle_id => x_new_k_top_id);
1506 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1507 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1508 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1509 RAISE OKL_API.G_EXCEPTION_ERROR;
1510 END IF;
1511 OKL_API.END_ACTIVITY (x_msg_count,
1512 x_msg_data );
1513 EXCEPTION
1514 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1515 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1516 l_api_name,
1517 G_PKG_NAME,
1518 'OKL_API.G_RET_STS_ERROR',
1519 x_msg_count,
1520 x_msg_data,
1521 '_PVT');
1522 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1523 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1524 l_api_name,
1525 G_PKG_NAME,
1526 'OKL_API.G_RET_STS_UNEXP_ERROR',
1527 x_msg_count,
1528 x_msg_data,
1529 '_PVT');
1530 WHEN OTHERS THEN
1531 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1532 l_api_name,
1533 G_PKG_NAME,
1534 'OTHERS',
1535 x_msg_count,
1536 x_msg_data,
1537 '_PVT');
1538 END l_copy_contract_line;
1539 -------------------------------------------------------------------------------------------------
1540 -- Start of Commnets
1541 -- Badrinath Kuchibholta
1542 -- Procedure Name : l_delete_contract_line
1543 -- Description : delete of the contract Line
1544 -- Business Rules :
1545 -- Parameters :
1546 -- Version :
1547 -- End of Commnets
1548 PROCEDURE l_delete_contract_line(p_api_version IN NUMBER,
1549 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1550 x_return_status OUT NOCOPY VARCHAR2,
1551 x_msg_count OUT NOCOPY NUMBER,
1552 x_msg_data OUT NOCOPY VARCHAR2,
1553 p_contract_id IN OKC_K_HEADERS_V.ID%TYPE) IS
1554
1555 l_api_name CONSTANT VARCHAR2(30) := 'LOCAL_DEL_CONTRACT_LINE';
1556 l_chrv_rec chrv_rec_type;
1557 l_khrv_rec khrv_rec_type;
1558 l_tcnv_rec OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
1559 lx_tcnv_rec OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
1560 ln_orig_system_id1 OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE;
1561 l_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
1562 r_tcnv_rec OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
1563
1564 CURSOR c_get_k_stream(p_khr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
1565 SELECT id stm_id
1566 FROM OKL_STREAMS
1567 WHERE khr_id = p_khr_id;
1568
1569 CURSOR c_get_je_trans(p_khr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
1570 SELECT id trx_id
1571 FROM OKL_TRX_CONTRACTS
1572 WHERE khr_id = p_khr_id
1573 --rkuttiya added for 12.1.1 Multi GAAP
1574 AND representation_type = 'PRIMARY';
1575
1576 CURSOR c_get_source_id(p_khr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
1577 SELECT id
1578 FROM OKL_TXL_CNTRCT_LNS
1579 WHERE khr_id = p_khr_id;
1580
1581 CURSOR c_get_k_top_line(p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
1582 SELECT cle.id top_line
1583 FROM okc_line_styles_b lse,
1584 okc_k_lines_b cle
1585 WHERE cle.dnz_chr_id = p_dnz_chr_id
1586 AND cle.cle_id IS NULL
1587 AND cle.chr_id = cle.dnz_chr_id
1588 AND cle.lse_id = lse.id
1589 AND lse.lse_parent_id IS NULL
1590 AND lse.lse_type = G_TLS_TYPE;
1591
1592 -- To get the orig system id for p_chr_id
1593 CURSOR get_orig_sys_id1(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
1594 SELECT orig_system_id1
1595 FROM okc_k_headers_b
1596 WHERE id = p_chr_id;
1597
1598 -- To get status of splited transaction Chr id
1599 CURSOR get_trx_id(p_org_sys_id OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE) IS
1600 SELECT trx.id trx_id
1601 FROM okl_trx_contracts trx,
1602 okl_trx_types_tl try,
1603 okc_k_headers_b CHR
1604 WHERE try.name = 'Split Contract'
1605 AND try.LANGUAGE = 'US'
1606 AND trx.try_id = try.id
1607 AND trx.tsu_code = 'ENTERED'
1608 AND trx.khr_id = CHR.orig_system_id1
1609 --rkuttiya added for 12.1.1 Multi GAAP Project
1610 AND trx.representation_type = 'PRIMARY'
1611 --
1612 AND CHR.orig_system_source_code = 'OKL_SPLIT'
1613 AND CHR.orig_system_id1= p_org_sys_id;
1614
1615 BEGIN
1616 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1617 -- Call start_activity to create savepoint, check compatibility
1618 -- and initialize message list
1619 x_return_status := OKL_API.START_ACTIVITY (
1620 l_api_name
1621 ,p_init_msg_list
1622 ,'_PVT'
1623 ,x_return_status);
1624 -- Check if activity started successfully
1625 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1626 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1627 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1628 RAISE OKL_API.G_EXCEPTION_ERROR;
1629 END IF;
1630 -- Get the Orig system id1
1631 OPEN get_orig_sys_id1(p_chr_id => p_contract_id);
1632 FETCH get_orig_sys_id1 INTO ln_orig_system_id1;
1633 IF get_orig_sys_id1%NOTFOUND THEN
1634 OKL_API.set_message(p_app_name => G_APP_NAME,
1635 p_msg_name => G_NO_MATCHING_RECORD,
1636 p_token1 => G_COL_NAME_TOKEN,
1637 p_token1_value => 'p_chr_id');
1638 RAISE OKL_API.G_EXCEPTION_ERROR;
1639 END IF;
1640 CLOSE get_orig_sys_id1;
1641 -- get Trx id
1642 OPEN get_trx_id(p_org_sys_id => ln_orig_system_id1);
1643 FETCH get_trx_id INTO l_tcnv_rec.id;
1644 IF get_trx_id%FOUND THEN
1645 -- Process the okl_trx_contracts
1646 l_tcnv_rec.tsu_code := 'CANCELED';
1647 Okl_Trx_Contracts_Pub.update_trx_contracts(
1648 p_api_version => p_api_version,
1649 p_init_msg_list => p_init_msg_list,
1650 x_return_status => x_return_status,
1651 x_msg_count => x_msg_count,
1652 x_msg_data => x_msg_data,
1653 p_tcnv_rec => l_tcnv_rec,
1654 x_tcnv_rec => lx_tcnv_rec);
1655 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1656 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1657 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1658 RAISE OKL_API.G_EXCEPTION_ERROR;
1659 END IF;
1660 END IF;
1661 CLOSE get_trx_id;
1662 -- Since we cannot delete contract line which is already booked
1663 -- So We need to change the status of the contract to INCOMPLETE
1664 OKL_CONTRACT_STATUS_PUB.update_contract_status(
1665 p_api_version => p_api_version,
1666 p_init_msg_list => p_init_msg_list,
1667 x_return_status => x_return_status,
1668 x_msg_count => x_msg_count,
1669 x_msg_data => x_msg_data,
1670 p_khr_status => 'INCOMPLETE',
1671 p_chr_id => p_contract_id);
1672 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1673 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1674 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1675 RAISE OKL_API.G_EXCEPTION_ERROR;
1676 END IF;
1677 -- We need to change the status of the Lines for the contract
1678 OKL_CONTRACT_STATUS_PUB.cascade_lease_status(
1679 p_api_version => p_api_version,
1680 p_init_msg_list => p_init_msg_list,
1681 x_return_status => x_return_status,
1682 x_msg_count => x_msg_count,
1683 x_msg_data => x_msg_data,
1684 p_chr_id => p_contract_id);
1685 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1686 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1687 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1688 RAISE OKL_API.G_EXCEPTION_ERROR;
1689 END IF;
1690 -- Deleting the Draft journal Entries
1691 FOR r_get_source_id IN c_get_source_id(p_khr_id => p_contract_id) LOOP
1692 OKL_ACCOUNT_DIST_PUB.DELETE_ACCT_ENTRIES(
1693 p_api_version => p_api_version,
1694 p_init_msg_list => p_init_msg_list,
1695 x_return_status => x_return_status,
1696 x_msg_count => x_msg_count,
1697 x_msg_data => x_msg_data,
1698 p_source_id => r_get_source_id.id,
1699 p_source_table => 'OKL_TXL_CNTRCT_LNS');
1700 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1701 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
1702 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1703 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
1704 END IF;
1705 END LOOP;
1706 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1707 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1708 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1709 RAISE OKL_API.G_EXCEPTION_ERROR;
1710 END IF;
1711 -- Deleting the transctions of the journal Entries
1712 FOR r_get_je_trans IN c_get_je_trans(p_khr_id => p_contract_id) LOOP
1713 r_tcnv_rec.id := r_get_je_trans.trx_id;
1714 OKL_TRX_CONTRACTS_PUB.delete_trx_contracts(
1715 p_api_version => p_api_version,
1716 p_init_msg_list => p_init_msg_list,
1717 x_return_status => x_return_status,
1718 x_msg_count => x_msg_count,
1719 x_msg_data => x_msg_data,
1720 p_tcnv_rec => r_tcnv_rec);
1721 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1722 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
1723 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1724 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
1725 END IF;
1726 END LOOP;
1727 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1728 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1729 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1730 RAISE OKL_API.G_EXCEPTION_ERROR;
1731 END IF;
1732 -- Delete the streams for the contract
1733 FOR r_get_k_stream IN c_get_k_stream(p_khr_id => p_contract_id) LOOP
1734 l_stmv_rec.id := r_get_k_stream.stm_id;
1735 OKL_STREAMS_PUB.delete_streams(
1736 p_api_version => p_api_version,
1737 p_init_msg_list => p_init_msg_list,
1738 x_return_status => x_return_status,
1739 x_msg_count => x_msg_count,
1740 x_msg_data => x_msg_data,
1741 p_stmv_rec => l_stmv_rec);
1742 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1743 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
1744 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1745 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
1746 END IF;
1747 END LOOP;
1748 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1749 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1750 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1751 RAISE OKL_API.G_EXCEPTION_ERROR;
1752 END IF;
1753 -- Delete the contract lines
1754 FOR r_get_k_top_line IN c_get_k_top_line(p_dnz_chr_id => p_contract_id) LOOP
1755 OKL_CONTRACT_PUB.delete_contract_line(
1756 p_api_version => p_api_version,
1757 p_init_msg_list => p_init_msg_list,
1758 x_return_status => x_return_status,
1759 x_msg_count => x_msg_count,
1760 x_msg_data => x_msg_data,
1761 p_line_id => r_get_k_top_line.top_line);
1762 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1763 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
1764 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1765 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
1766 END IF;
1767 END LOOP;
1768 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1769 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1770 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1771 RAISE OKL_API.G_EXCEPTION_ERROR;
1772 END IF;
1773 -- Now the Delete the Header
1774 l_chrv_rec.id := p_contract_id;
1775 l_khrv_rec.id := p_contract_id;
1776 OKL_CONTRACT_PUB.delete_contract_header(
1777 p_api_version => p_api_version,
1778 p_init_msg_list => p_init_msg_list,
1779 x_return_status => x_return_status,
1780 x_msg_count => x_msg_count,
1781 x_msg_data => x_msg_data,
1782 p_chrv_rec => l_chrv_rec,
1783 p_khrv_rec => l_khrv_rec);
1784 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1785 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1786 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1787 RAISE OKL_API.G_EXCEPTION_ERROR;
1788 END IF;
1789 OKL_API.END_ACTIVITY (x_msg_count,
1790 x_msg_data );
1791 -- since we need to do this beacuse we need to delete to the contract
1792 COMMIT;
1793 EXCEPTION
1794 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1795 IF c_get_k_top_line%ISOPEN THEN
1796 CLOSE c_get_k_top_line;
1797 END IF;
1798 IF get_orig_sys_id1%ISOPEN THEN
1799 CLOSE get_orig_sys_id1;
1800 END IF;
1801 IF get_trx_id%ISOPEN THEN
1802 CLOSE get_trx_id;
1803 END IF;
1804 IF c_get_k_stream%ISOPEN THEN
1805 CLOSE c_get_k_stream;
1806 END IF;
1807 IF c_get_je_trans%ISOPEN THEN
1808 CLOSE c_get_je_trans;
1809 END IF;
1810 IF c_get_source_id%ISOPEN THEN
1811 CLOSE c_get_source_id;
1812 END IF;
1813 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1814 l_api_name,
1815 G_PKG_NAME,
1816 'OKL_API.G_RET_STS_ERROR',
1817 x_msg_count,
1818 x_msg_data,
1819 '_PVT');
1820 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1821 IF c_get_k_top_line%ISOPEN THEN
1822 CLOSE c_get_k_top_line;
1823 END IF;
1824 IF get_orig_sys_id1%ISOPEN THEN
1825 CLOSE get_orig_sys_id1;
1826 END IF;
1827 IF get_trx_id%ISOPEN THEN
1828 CLOSE get_trx_id;
1829 END IF;
1830 IF c_get_k_stream%ISOPEN THEN
1831 CLOSE c_get_k_stream;
1832 END IF;
1833 IF c_get_je_trans%ISOPEN THEN
1834 CLOSE c_get_je_trans;
1835 END IF;
1836 IF c_get_source_id%ISOPEN THEN
1837 CLOSE c_get_source_id;
1838 END IF;
1839 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1840 l_api_name,
1841 G_PKG_NAME,
1842 'OKL_API.G_RET_STS_UNEXP_ERROR',
1843 x_msg_count,
1844 x_msg_data,
1845 '_PVT');
1846 WHEN OTHERS THEN
1847 IF c_get_k_top_line%ISOPEN THEN
1848 CLOSE c_get_k_top_line;
1849 END IF;
1850 IF get_orig_sys_id1%ISOPEN THEN
1851 CLOSE get_orig_sys_id1;
1852 END IF;
1853 IF get_trx_id%ISOPEN THEN
1854 CLOSE get_trx_id;
1855 END IF;
1856 IF c_get_k_stream%ISOPEN THEN
1857 CLOSE c_get_k_stream;
1858 END IF;
1859 IF c_get_je_trans%ISOPEN THEN
1860 CLOSE c_get_je_trans;
1861 END IF;
1862 IF c_get_source_id%ISOPEN THEN
1863 CLOSE c_get_source_id;
1864 END IF;
1865 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1866 l_api_name,
1867 G_PKG_NAME,
1868 'OTHERS',
1869 x_msg_count,
1870 x_msg_data,
1871 '_PVT');
1872 END l_delete_contract_line;
1873 -------------------------------------------------------------------------------------------------
1874 -- Start of Commnets
1875 -- RaviKiran Addanki
1876 -- Procedure Name : cancel_split_process
1877 -- Description : Cancel the Split Contract process.
1878 -- Business Rules :
1879 -- Parameters :
1880 -- Version :
1881 -- End of Commnets
1882 PROCEDURE cancel_split_process (p_api_version IN NUMBER,
1883 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1884 x_return_status OUT NOCOPY VARCHAR2,
1885 x_msg_count OUT NOCOPY NUMBER,
1886 x_msg_data OUT NOCOPY VARCHAR2,
1887 p_contract_id IN OKC_K_HEADERS_V.ID%TYPE) IS
1888
1889 l_api_name CONSTANT VARCHAR2(30) := 'cancel_split_process';
1890 ln_split_contract1 OKC_K_HEADERS_B.ID%TYPE := NULL;
1891 ln_split_contract2 OKC_K_HEADERS_B.ID%TYPE := NULL;
1892 ln_transaction_id OKL_TRX_CONTRACTS.ID%TYPE;
1893 ln_contract_id NUMBER := 0;
1894 lb_value BOOLEAN := FALSE;
1895 l_tcnv_rec OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
1896 lx_tcnv_rec OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
1897
1898 -- To check the existence of the contract.
1899 CURSOR c_get_contract(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
1900 SELECT 1
1901 FROM DUAL
1902 WHERE EXISTS (SELECT '1'
1903 FROM OKC_K_HEADERS_B
1904 WHERE ID = p_chr_id);
1905
1906 -- To get the transaction created for the contract.
1907 CURSOR c_get_trx_id(p_chr_id OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE) IS
1908 SELECT trx.id trx_id
1909 FROM okl_trx_contracts trx,
1910 okl_trx_types_tl try
1911 WHERE try.name = 'Split Contract'
1912 AND try.LANGUAGE = 'US'
1913 AND trx.try_id = try.id
1914 AND trx.tsu_code IN ('ENTERED','WORKING','WAITING','SUBMITTED')
1915 AND trx.tcn_type = 'SPLC'
1916 --rkuttiya added for 12.1.1 Multi GAAP
1917 AND trx.representation_type = 'PRIMARY'
1918 --
1919 AND trx.khr_id = p_chr_id;
1920
1921 -- To get the contracts created during Split process
1922 CURSOR c_get_split_contracts(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
1923 SELECT id
1924 FROM okc_k_headers_b
1925 WHERE orig_system_id1 = p_chr_id
1926 AND orig_system_source_code = 'OKL_SPLIT';
1927
1928 BEGIN
1929 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1930 -- Call start_activity to create savepoint, check compatibility
1931 -- and initialize message list
1932 x_return_status := OKL_API.START_ACTIVITY (
1933 l_api_name
1934 ,p_init_msg_list
1935 ,'_PVT'
1936 ,x_return_status);
1937 -- Check if activity started successfully
1938 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1939 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1940 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1941 RAISE OKL_API.G_EXCEPTION_ERROR;
1942 END IF;
1943
1944 IF (p_contract_id = OKL_API.G_MISS_NUM OR p_contract_id IS NULL) THEN
1945 x_return_status := OKL_API.G_RET_STS_ERROR;
1946 OKL_API.set_message(p_app_name => G_APP_NAME,
1947 p_msg_name => G_REQUIRED_VALUE,
1948 p_token1 => G_COL_NAME_TOKEN,
1949 p_token1_value => 'OKC_K_HEADERS_B.ID');
1950 -- halt validation as it is a required field
1951 RAISE G_EXCEPTION_STOP_VALIDATION;
1952 END IF;
1953
1954 -- Validate the Contract ID.
1955 OPEN c_get_contract(p_contract_id);
1956 FETCH c_get_contract INTO ln_contract_id;
1957 IF c_get_contract%NOTFOUND THEN
1958 x_return_status := OKL_API.G_RET_STS_ERROR;
1959 OKL_API.set_message(p_app_name => G_APP_NAME,
1960 p_msg_name => G_INVALID_CONTRACT);
1961 RAISE G_EXCEPTION_HALT_VALIDATION;
1962 END IF;
1963 CLOSE c_get_contract;
1964
1965 -- Obtain the Split contracts, if they were created during the process.
1966 FOR r_get_split_contracts IN c_get_split_contracts(p_chr_id => p_contract_id) LOOP
1967 IF (NOT lb_value) THEN
1968 ln_split_contract1 := r_get_split_contracts.id;
1969 END IF;
1970 IF (lb_value) THEN
1971 ln_split_contract2 := r_get_split_contracts.id;
1972 END IF;
1973 lb_value := TRUE;
1974 END LOOP;
1975
1976 IF (ln_split_contract1 IS NULL OR ln_split_contract1 = OKL_API.G_MISS_NUM) AND
1977 (ln_split_contract2 IS NULL OR ln_split_contract2 = OKL_API.G_MISS_NUM) THEN
1978 -- Split transaction created with no contracts. Cancel the transaction for
1979 -- the parent contract
1980 OPEN c_get_trx_id(p_chr_id => p_contract_id);
1981 FETCH c_get_trx_id INTO l_tcnv_rec.id;
1982 IF c_get_trx_id%FOUND THEN
1983 -- Cancel the transaction for Split Conntract.
1984 l_tcnv_rec.tsu_code := 'CANCELED';
1985 Okl_Trx_Contracts_Pub.update_trx_contracts(
1986 p_api_version => p_api_version,
1987 p_init_msg_list => p_init_msg_list,
1988 x_return_status => x_return_status,
1989 x_msg_count => x_msg_count,
1990 x_msg_data => x_msg_data,
1991 p_tcnv_rec => l_tcnv_rec,
1992 x_tcnv_rec => lx_tcnv_rec);
1993 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1994 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1995 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1996 RAISE OKL_API.G_EXCEPTION_ERROR;
1997 END IF;
1998 END IF;
1999 CLOSE c_get_trx_id;
2000
2001 ELSIF (ln_split_contract1 IS NOT NULL OR ln_split_contract1 <> OKL_API.G_MISS_NUM) AND
2002 (ln_split_contract2 IS NULL OR ln_split_contract2 = OKL_API.G_MISS_NUM) THEN
2003 -- One split contract already got created. Cancel the transaction and
2004 -- delete the created split contract.
2005 l_delete_contract_line(p_api_version => p_api_version,
2006 p_init_msg_list => p_init_msg_list,
2007 x_return_status => x_return_status,
2008 x_msg_count => x_msg_count,
2009 x_msg_data => x_msg_data,
2010 p_contract_id => ln_split_contract1);
2011 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2012 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2013 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2014 RAISE OKL_API.G_EXCEPTION_ERROR;
2015 END IF;
2016 END IF;
2017 OKL_API.END_ACTIVITY (x_msg_count,
2018 x_msg_data );
2019 EXCEPTION
2020 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2021 IF c_get_trx_id%ISOPEN THEN
2022 CLOSE c_get_trx_id;
2023 END IF;
2024 IF c_get_split_contracts%ISOPEN THEN
2025 CLOSE c_get_split_contracts;
2026 END IF;
2027 IF c_get_contract%ISOPEN THEN
2028 CLOSE c_get_contract;
2029 END IF;
2030 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2031 l_api_name,
2032 G_PKG_NAME,
2033 'OKL_API.G_RET_STS_ERROR',
2034 x_msg_count,
2035 x_msg_data,
2036 '_PVT');
2037 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2038 IF c_get_trx_id%ISOPEN THEN
2039 CLOSE c_get_trx_id;
2040 END IF;
2041 IF c_get_split_contracts%ISOPEN THEN
2042 CLOSE c_get_split_contracts;
2043 END IF;
2044 IF c_get_contract%ISOPEN THEN
2045 CLOSE c_get_contract;
2046 END IF;
2047 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2048 l_api_name,
2049 G_PKG_NAME,
2050 'OKL_API.G_RET_STS_UNEXP_ERROR',
2051 x_msg_count,
2052 x_msg_data,
2053 '_PVT');
2054 WHEN G_EXCEPTION_STOP_VALIDATION THEN
2055 IF c_get_trx_id%ISOPEN THEN
2056 CLOSE c_get_trx_id;
2057 END IF;
2058 IF c_get_split_contracts%ISOPEN THEN
2059 CLOSE c_get_split_contracts;
2060 END IF;
2061 IF c_get_contract%ISOPEN THEN
2062 CLOSE c_get_contract;
2063 END IF;
2064 x_return_status := OKL_API.G_RET_STS_ERROR;
2065
2066 WHEN OTHERS THEN
2067 IF c_get_trx_id%ISOPEN THEN
2068 CLOSE c_get_trx_id;
2069 END IF;
2070 IF c_get_split_contracts%ISOPEN THEN
2071 CLOSE c_get_split_contracts;
2072 END IF;
2073 IF c_get_contract%ISOPEN THEN
2074 CLOSE c_get_contract;
2075 END IF;
2076 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2077 l_api_name,
2078 G_PKG_NAME,
2079 'OTHERS',
2080 x_msg_count,
2081 x_msg_data,
2082 '_PVT');
2083 END cancel_split_process;
2084 -------------------------------------------------------------------------------------------------
2085 -- Start of Commnets
2086 -- RaviKiran Addanki
2087 -- Procedure Name : l_delete_fee_service_lines
2088 -- Description : Deletes service and fee lines not having linked assets
2089 -- Added this as a fix for Bug 3608423
2090 -- Business Rules :
2091 -- Parameters :
2092 -- Version :
2093 -- End of Commnets
2094 PROCEDURE l_delete_fee_service_lines(p_api_version IN NUMBER,
2095 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2096 x_return_status OUT NOCOPY VARCHAR2,
2097 x_msg_count OUT NOCOPY NUMBER,
2098 x_msg_data OUT NOCOPY VARCHAR2,
2099 p_contract_id IN OKC_K_HEADERS_V.ID%TYPE) IS
2100
2101 l_api_name CONSTANT VARCHAR2(30) := 'L_DEL_FEE_SERV_LINES';
2102 l_clev_rec clev_rec_type;
2103 l_klev_rec klev_rec_type;
2104 lx_clev_rec clev_rec_type;
2105 lx_klev_rec klev_rec_type;
2106
2107 CURSOR get_fee_service_lines(p_chr_id OKC_K_HEADERS_B.ID%TYPE)
2108 IS
2109 SELECT cle.id id
2110 FROM okl_k_lines_v kle,
2111 okc_k_lines_v cle,
2112 okc_line_styles_b lse
2113 WHERE cle.dnz_chr_id = p_chr_id
2114 AND cle.id = kle.id
2115 AND cle.lse_id = lse.id
2116 AND lse.lty_code IN (G_FEE_LINE_LTY_CODE, G_SER_LINE_LTY_CODE)
2117 AND lse.lse_type = 'TLS'
2118 AND cle.cle_id IS NULL
2119 AND cle.id NOT IN (SELECT DISTINCT(cle_sl.cle_id) cle_id
2120 FROM okl_k_lines_v kle_sl,
2121 okc_k_lines_v cle_sl,
2122 okc_line_styles_b lse_sl
2123 WHERE cle_sl.dnz_chr_id = p_chr_id
2124 AND cle_sl.id = kle_sl.id
2125 AND cle_sl.lse_id = lse_sl.id
2126 AND lse_sl.lty_code IN (G_FEL_LINE_LTY_CODE, G_SRL_LINE_LTY_CODE));
2127
2128 BEGIN
2129 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2130 -- Call start_activity to create savepoint, check compatibility
2131 -- and initialize message list
2132 x_return_status := OKL_API.START_ACTIVITY (
2133 l_api_name
2134 ,p_init_msg_list
2135 ,'_PVT'
2136 ,x_return_status);
2137 -- Check if activity started successfully
2138 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2139 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2140 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2141 RAISE OKL_API.G_EXCEPTION_ERROR;
2142 END IF;
2143
2144 -- Delete all service and fee lines not having linked assets.
2145 FOR r_get_fee_service_lines IN get_fee_service_lines(p_chr_id => p_contract_id) LOOP
2146 OKL_CONTRACT_PUB.delete_contract_line(
2147 p_api_version => p_api_version,
2148 p_init_msg_list => p_init_msg_list,
2149 x_return_status => x_return_status,
2150 x_msg_count => x_msg_count,
2151 x_msg_data => x_msg_data,
2152 p_line_id => r_get_fee_service_lines.id);
2153
2154 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2155 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
2156 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2157 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
2158 END IF;
2159 END LOOP;
2160 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2161 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2162 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2163 RAISE OKL_API.G_EXCEPTION_ERROR;
2164 END IF;
2165 OKL_API.END_ACTIVITY (x_msg_count,
2166 x_msg_data );
2167 EXCEPTION
2168 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2169 IF get_fee_service_lines%ISOPEN THEN
2170 CLOSE get_fee_service_lines;
2171 END IF;
2172 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2173 l_api_name,
2174 G_PKG_NAME,
2175 'OKL_API.G_RET_STS_ERROR',
2176 x_msg_count,
2177 x_msg_data,
2178 '_PVT');
2179 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2180 IF get_fee_service_lines%ISOPEN THEN
2181 CLOSE get_fee_service_lines;
2182 END IF;
2183 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2184 l_api_name,
2185 G_PKG_NAME,
2186 'OKL_API.G_RET_STS_UNEXP_ERROR',
2187 x_msg_count,
2188 x_msg_data,
2189 '_PVT');
2190 WHEN OTHERS THEN
2191 IF get_fee_service_lines%ISOPEN THEN
2192 CLOSE get_fee_service_lines;
2193 END IF;
2194 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2195 l_api_name,
2196 G_PKG_NAME,
2197 'OTHERS',
2198 x_msg_count,
2199 x_msg_data,
2200 '_PVT');
2201 END l_delete_fee_service_lines;
2202 -------------------------------------------------------------------------------------------------
2203 -- Start of Commnets
2204 -- Badrinath Kuchibholta
2205 -- Procedure Name : l_process_split_contract
2206 -- Description : Process Split Contract
2207 -- Business Rules :
2208 -- Parameters :
2209 -- Version :
2210 -- End of Commnets
2211 PROCEDURE l_process_split_contract(p_api_version IN NUMBER,
2212 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2213 x_return_status OUT NOCOPY VARCHAR2,
2214 x_msg_count OUT NOCOPY NUMBER,
2215 x_msg_data OUT NOCOPY VARCHAR2,
2216 p_contract_id IN OKC_K_HEADERS_V.ID%TYPE) IS
2217
2218 l_api_name CONSTANT VARCHAR2(30) := 'LOCAL_PROCESS_SPLIT_K';
2219 ln_old_chr_id OKC_K_HEADERS_B.ID%TYPE;
2220 ln_dummy NUMBER := 0;
2221 i NUMBER := 0;
2222 j NUMBER := 0;
2223 k NUMBER := 0;
2224 ln_calc_amt OKL_K_LINES_V.AMOUNT%TYPE := 0;
2225 ln_old_cap_amt OKL_K_LINES_V.CAPITAL_AMOUNT%TYPE := 0;
2226 ln_new_cap_amt OKL_K_LINES_V.CAPITAL_AMOUNT%TYPE := 0;
2227 ln_new_service_amt OKL_K_LINES_V.AMOUNT%TYPE := 0;
2228 ln_old_service_amt OKL_K_LINES_V.AMOUNT%TYPE := 0;
2229 ln_new_init_cost OKL_K_LINES_V.INITIAL_DIRECT_COST%TYPE := 0;
2230 ln_period NUMBER :=0;
2231 ln_payment_amount NUMBER :=0;
2232 ln_sls_payment_amount NUMBER :=0;
2233 l_clev_rec clev_rec_type;
2234 l_klev_rec klev_rec_type;
2235 lx_clev_rec clev_rec_type;
2236 lx_klev_rec klev_rec_type;
2237 l_rulv_rec rulv_rec_type;
2238 lp_rulv_rec rulv_rec_type;
2239 lx_rulv_rec rulv_rec_type;
2240 r_rulv_rec rulv_rec_type;
2241 rx_rulv_rec rulv_rec_type;
2242
2243 -- We need to find out weahter we have fee, service, and Usage lines first
2244 -- so that we can process further
2245
2246 CURSOR check_other_line(p_chr_id OKC_K_HEADERS_B.ID%TYPE)
2247 IS
2248 SELECT '1'
2249 FROM DUAL
2250 WHERE EXISTS (SELECT '1'
2251 FROM OKC_K_LINES_B cle,
2252 OKC_LINE_STYLES_b lse
2253 WHERE cle.dnz_chr_id = p_chr_id
2254 AND lse.id = cle.lse_id
2255 AND lse.lty_code IN (G_SER_LINE_LTY_CODE,
2256 G_SRL_LINE_LTY_CODE,
2257 G_FEE_LINE_LTY_CODE,
2258 G_FEL_LINE_LTY_CODE,
2259 G_USG_LINE_LTY_CODE,
2260 G_USL_LINE_LTY_CODE));
2261
2262 -- To get the orig system id for p_chr_id
2263 CURSOR get_orig_sys_id1(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
2264 SELECT orig_system_id1
2265 FROM okc_k_headers_b
2266 WHERE id = p_chr_id;
2267
2268 /*CURSOR get_asset_info(p_chr_id OKC_K_HEADERS_B.ID%TYPE)
2269 IS
2270 SELECT sum(cle.capital_amount) total_capital_amount
2271 FROM okl_k_lines_full_v cle,
2272 okc_line_styles_b lse
2273 WHERE cle.dnz_chr_id = p_chr_id
2274 AND cle.lse_id = lse.id
2275 AND lse.lty_code = G_FIN_LINE_LTY_CODE
2276 AND lse.lse_type = G_TLS_TYPE;*/
2277
2278 -- rravikir added for bug 3504415
2279 -- Get the top fee lines which are not having any assets link attached to it
2280 CURSOR get_item_info_tls(p_dnz_chr_Id OKC_K_HEADERS_V.ID%TYPE)
2281 IS
2282 select tcle.id id
2283 from okc_k_items tcim,
2284 okc_k_lines_b tcle,
2285 okc_line_styles_b lse
2286 where tcim.dnz_chr_id = p_dnz_chr_id
2287 and tcle.lse_id = lse.id
2288 and tcim.cle_id = tcle.id
2289 and tcim.dnz_chr_id = tcle.dnz_chr_id
2290 and lse.lty_code in (G_FEE_LINE_LTY_CODE, G_SER_LINE_LTY_CODE)
2291 and exists (select 1
2292 from okc_k_items cim,
2293 okc_k_lines_b cle
2294 where cim.dnz_chr_id = p_dnz_chr_id
2295 and cle.cle_id = tcle.id
2296 and cim.cle_id = cle.id
2297 and cim.jtot_object1_code = 'OKX_COVASST'
2298 and not exists
2299 (select 1
2300 from okc_k_lines_b cle,
2301 okc_line_styles_b lse
2302 where cle.dnz_chr_id = p_dnz_chr_id
2303 and cle.lse_id = lse.id
2304 and lse.lty_code in (G_FIN_LINE_LTY_CODE, G_SER_LINE_LTY_CODE)
2305 and lse.lse_type = G_TLS_TYPE
2306 and cle.id = cim.object1_id1))
2307 and not exists
2308 (select 1
2309 from okc_k_lines_b cle,
2310 okc_line_styles_b lse
2311 where cle.dnz_chr_id = p_dnz_chr_id
2312 and cle.lse_id = lse.id
2313 and lse.lty_code in (G_FIN_LINE_LTY_CODE, G_SER_LINE_LTY_CODE)
2314 and lse.lse_type = G_TLS_TYPE
2315 and exists(select 1
2316 from okc_k_lines_b scle,
2317 okc_k_items scim
2318 where scle.id = scim.cle_id -- sub line join with item
2319 and scim.dnz_chr_id = p_dnz_chr_id
2320 and scim.dnz_chr_id = tcle.dnz_chr_id
2321 and scle.cle_id = tcle.id -- fee top line join
2322 and scim.object1_id1 = cle.id));
2323
2324 -- Get the fee links which are not corresponding to this contract
2325 CURSOR get_item_info(p_dnz_chr_Id OKC_K_HEADERS_V.ID%TYPE)
2326 IS
2327 SELECT DISTINCT(cle.id) id
2328 FROM okl_k_lines_full_v cle,
2329 okc_k_items cim
2330 WHERE cle.dnz_chr_id = p_dnz_chr_Id
2331 AND cle.id = cim.cle_id
2332 AND cim.jtot_object1_code = 'OKX_COVASST'
2333 AND cim.object1_id1 NOT IN (SELECT cle.id
2334 FROM okc_k_lines_b cle,
2335 okc_line_styles_b lse
2336 WHERE cle.dnz_chr_id = p_dnz_chr_Id
2337 AND cle.lse_id = lse.id
2338 AND lse.lty_code IN (G_FIN_LINE_LTY_CODE, G_SER_LINE_LTY_CODE)
2339 AND lse.lse_type = G_TLS_TYPE);
2340 -- end rravikir added for bug 3504415
2341
2342 -- rravikir commented for bug 3504415
2343 /* CURSOR get_item_info_tls(p_dnz_chr_Id OKC_K_HEADERS_V.ID%TYPE)
2344 IS
2345 SELECT cle.cle_id fee_top_line_id, cim.cle_id linked_asset_id
2346 FROM okc_k_items cim,
2347 okc_k_lines_b cle
2348 WHERE cim.dnz_chr_id = p_dnz_chr_Id
2349 AND cim.cle_id = cle.id
2350 AND cim.dnz_chr_id = cle.dnz_chr_id
2351 AND cim.jtot_object1_code = 'OKX_COVASST'
2352 AND cim.object1_id1 not in (SELECT cle.id
2353 FROM okc_k_lines_b cle,
2354 okc_line_styles_b lse
2355 WHERE cle.dnz_chr_id = p_dnz_chr_Id
2356 AND cle.lse_id = lse.id
2357 AND lse.lty_code = G_FIN_LINE_LTY_CODE
2358 AND lse.lse_type = G_TLS_TYPE);
2359
2360 CURSOR get_item_info(p_dnz_chr_Id OKC_K_HEADERS_V.ID%TYPE)
2361 IS
2362 SELECT cim.cle_id cle_id
2363 FROM okc_k_items cim
2364 WHERE cim.dnz_chr_id = p_dnz_chr_Id
2365 AND cim.jtot_object1_code = 'OKX_COVASST'
2366 AND cim.object1_id1 not in (SELECT cle.id
2367 FROM okc_k_lines_b cle,
2368 okc_line_styles_b lse
2369 WHERE cle.dnz_chr_id = p_dnz_chr_Id
2370 AND cle.lse_id = lse.id
2371 AND lse.lty_code = G_FIN_LINE_LTY_CODE
2372 AND lse.lse_type = G_TLS_TYPE);*/
2373 -- end rravikir commented for bug 3504415
2374
2375 -- rravikir commented for bug
2376 /*CURSOR get_amt(p_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE)
2377 IS
2378 SELECT nvl(kle.amount,0) amount,
2379 nvl(kle.initial_direct_cost,0) initial_direct_cost,
2380 cle.id id
2381 FROM okl_k_lines_v kle,
2382 okc_k_lines_v cle,
2383 okc_line_styles_b lse
2384 WHERE cle.dnz_chr_id = p_chr_id
2385 AND cle.id = kle.id
2386 AND cle.lse_id = lse.id
2387 AND lse.lty_code in (G_SER_LINE_LTY_CODE,G_FEE_LINE_LTY_CODE)
2388 AND lse.lse_type = G_TLS_TYPE
2389 AND cle.cle_id IS NULL;*/
2390 -- end rravikir commented for bug
2391
2392 -- New Cursor for Bug 3608423
2393 CURSOR get_amt(p_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE)
2394 IS
2395 SELECT DISTINCT(cle.id) id,
2396 NVL(kle.amount,0) amount,
2397 NVL(kle.initial_direct_cost,0) initial_direct_cost
2398 FROM okl_k_lines_v kle_sl,
2399 okc_k_lines_v cle_sl,
2400 okc_line_styles_b lse_sl,
2401 okl_k_lines_v kle,
2402 okc_k_lines_v cle,
2403 okc_line_styles_b lse
2404 WHERE cle.dnz_chr_id = p_chr_id
2405 AND cle.id = kle.id
2406 AND cle.lse_id = lse.id
2407 AND lse.lty_code IN (G_SER_LINE_LTY_CODE,G_FEE_LINE_LTY_CODE)
2408 AND lse.lse_type = G_TLS_TYPE
2409 AND cle.cle_id IS NULL
2410 AND cle.id = cle_sl.cle_id
2411 AND cle_sl.id = kle_sl.id
2412 AND cle_sl.lse_id = lse_sl.id
2413 AND lse_sl.lty_code IN (G_SRL_LINE_LTY_CODE,G_FEL_LINE_LTY_CODE);
2414 -- End New Cursor for Bug 3608423
2415
2416 -- New Cursor to get the sum of capital amount of all capitalized link assets
2417 CURSOR get_cap_link_asset_amount(p_cle_id OKC_K_LINES_V.CLE_ID%TYPE)
2418 IS
2419 SELECT SUM(kle.CAPITAL_AMOUNT)
2420 FROM okl_k_lines_v kle,
2421 okc_line_styles_b lse,
2422 okc_k_lines_v cle
2423 WHERE cle.cle_id = p_cle_id
2424 AND cle.id = kle.id
2425 AND cle.lse_id = lse.id
2426 AND lse.lty_code IN (G_SRL_LINE_LTY_CODE,G_FEL_LINE_LTY_CODE);
2427 -- End New Cursor for Bug 3608423
2428
2429 /*CURSOR get_sls_amt(p_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE)
2430 IS
2431 SELECT sum(nvl(kle_sl.capital_amount,0)) amount,
2432 cle.id id,
2433 cle.orig_system_id1 orig_system_id1
2434 FROM okl_k_lines_v kle_sl,
2435 okc_k_lines_v cle_sl,
2436 okc_line_styles_b lse_sl,
2437 okl_k_lines_v kle,
2438 okc_k_lines_v cle,
2439 okc_line_styles_b lse
2440 WHERE cle.dnz_chr_id = p_chr_id
2441 AND cle.id = kle.id
2442 AND cle.lse_id = lse.id
2443 AND lse.lty_code in (G_SER_LINE_LTY_CODE,G_FEE_LINE_LTY_CODE)
2444 AND lse.lse_type = G_TLS_TYPE
2445 AND cle.cle_id IS NULL
2446 AND cle.id = cle_sl.cle_id
2447 and cle_sl.id = kle_sl.id
2448 AND cle_sl.lse_id = lse_sl.id
2449 AND lse_sl.lty_code in (G_SRL_LINE_LTY_CODE,G_FEL_LINE_LTY_CODE)
2450 group by cle.id,
2451 cle.orig_system_id1;*/
2452
2453 /*CURSOR get_rule_pymt(p_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE,
2454 p_cle_id OKC_K_LINES_V.ID%TYPE)
2455 IS
2456 SELECT rl.id,
2457 rl.rule_information6 payment_amount,
2458 rl.rule_information2 rl_date
2459 FROM okc_rule_groups_b rg,
2460 okc_rules_b rl,
2461 okc_k_lines_v cle,
2462 okc_line_styles_b lse
2463 WHERE cle.dnz_chr_id = p_chr_id
2464 AND cle.id = p_cle_id
2465 AND cle.lse_id = lse.id
2466 AND lse.lty_code in (G_SER_LINE_LTY_CODE,G_FEE_LINE_LTY_CODE)
2467 AND lse.lse_type = G_TLS_TYPE
2468 AND cle.cle_id IS NULL
2469 AND rg.dnz_chr_id = cle.dnz_chr_id
2470 AND rg.cle_id = cle.id
2471 AND rg.chr_id IS NULL
2472 AND rg.id = rl.rgp_id
2473 AND rg.rgd_code = 'LALEVL'
2474 AND rl.rule_information_category = 'LASLL'
2475 AND not exists (SELECT '1'
2476 FROM okc_k_lines_v cle_sl,
2477 okc_line_styles_b lse_sl
2478 WHERE cle_sl.dnz_chr_id = p_chr_id
2479 AND cle_sl.cle_id = cle.id
2480 AND cle_sl.lse_id = lse_sl.id
2481 AND lse_sl.lty_code in (G_SRL_LINE_LTY_CODE,G_FEL_LINE_LTY_CODE));*/
2482
2483 /*CURSOR get_sls_rule_pymt(p_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE,
2484 p_cle_id OKC_K_LINES_V.ID%TYPE)
2485 IS
2486 SELECT rl.id,
2487 rl.rule_information6 payment_amount
2488 FROM okc_rule_groups_b rg,
2489 okc_rules_b rl,
2490 okc_k_lines_v cle,
2491 okc_line_styles_b lse
2492 WHERE cle.dnz_chr_id = p_chr_id
2493 AND cle.id = p_cle_id
2494 AND cle.lse_id = lse.id
2495 AND lse.lty_code in (G_SER_LINE_LTY_CODE,G_FEE_LINE_LTY_CODE)
2496 AND lse.lse_type = G_TLS_TYPE
2497 AND cle.cle_id IS NULL
2498 AND rg.dnz_chr_id = cle.dnz_chr_id
2499 AND rg.cle_id = cle.id
2500 AND rg.chr_id IS NULL
2501 AND rg.id = rl.rgp_id
2502 AND rg.rgd_code = 'LALEVL'
2503 AND rl.rule_information_category = 'LASLL'
2504 AND exists (SELECT '1'
2505 FROM okc_k_lines_v cle_sl,
2506 okc_line_styles_b lse_sl
2507 WHERE cle_sl.dnz_chr_id = p_chr_id
2508 AND cle_sl.cle_id = cle.id
2509 AND cle_sl.lse_id = lse_sl.id
2510 AND lse_sl.lty_code in (G_SRL_LINE_LTY_CODE,G_FEL_LINE_LTY_CODE));*/
2511
2512 -- Get the rule that applies to service line
2513 CURSOR get_service_rule_info(p_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE,
2514 p_cle_id OKC_K_LINES_V.ID%TYPE)
2515 IS
2516 SELECT rl.id,
2517 cle.currency_code
2518 FROM okc_rule_groups_b rg,
2519 okc_rules_b rl,
2520 okc_k_lines_v cle,
2521 okc_line_styles_b lse
2522 WHERE cle.dnz_chr_id = p_chr_id
2523 AND cle.id = p_cle_id
2524 AND cle.lse_id = lse.id
2525 AND lse.lty_code = G_SER_LINE_LTY_CODE
2526 AND lse.lse_type = G_TLS_TYPE
2527 AND cle.cle_id IS NULL
2528 AND rg.dnz_chr_id = cle.dnz_chr_id
2529 AND rg.cle_id = cle.id
2530 AND rg.chr_id IS NULL
2531 AND rg.id = rl.rgp_id
2532 AND rg.rgd_code = 'LAFEXP'
2533 AND rl.rule_information_category = 'LAFEXP';
2534
2535 CURSOR get_expense_service_rule_info( p_rgd_code OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
2536 p_rgp_cat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
2537 p_chr_id NUMBER,
2538 p_cle_id NUMBER ) IS
2539 SELECT crl.RULE_INFORMATION1,
2540 crl.RULE_INFORMATION2
2541 FROM OKC_RULE_GROUPS_B crg,
2542 OKC_RULES_B crl
2543 WHERE crl.rgp_id = crg.id
2544 AND crg.RGD_CODE = p_rgd_code
2545 AND crl.RULE_INFORMATION_CATEGORY = p_rgp_cat
2546 AND crg.dnz_chr_id = p_chr_id
2547 AND NVL(crg.cle_id,-1) = p_cle_id;
2548
2549 CURSOR get_service_lines(p_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE)
2550 IS
2551 SELECT cle.id id,
2552 NVL(kle.amount,0) amount
2553 FROM okl_k_lines_v kle,
2554 okc_k_lines_v cle,
2555 okc_line_styles_b lse
2556 WHERE cle.dnz_chr_id = p_chr_id
2557 AND cle.id = kle.id
2558 AND cle.lse_id = lse.id
2559 AND lse.lty_code = G_SER_LINE_LTY_CODE
2560 AND lse.lse_type = G_TLS_TYPE
2561 AND cle.cle_id IS NULL;
2562
2563 CURSOR get_old_service_lines(p_new_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE,
2564 p_old_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE,
2565 p_new_cle_id OKL_K_LINES_V.ID%TYPE)
2566 IS
2567 SELECT cle.id id,
2568 NVL(kle.amount,0) amount
2569 FROM okl_k_lines_v kle,
2570 okc_k_lines_v cle,
2571 okc_line_styles_b lse
2572 WHERE cle.dnz_chr_id = p_old_chr_id
2573 AND cle.id = kle.id
2574 AND cle.lse_id = lse.id
2575 AND lse.lty_code = 'SOLD_SERVICE'
2576 AND lse.lse_type = 'TLS'
2577 AND cle.cle_id IS NULL
2578 AND cle.id = (SELECT klfv.ORIG_SYSTEM_ID1
2579 FROM okl_k_lines_full_v klfv
2580 WHERE klfv.id = p_new_cle_id
2581 AND klfv.dnz_chr_id = p_new_chr_id);
2582
2583
2584 CURSOR get_service_line_payments( p_rgd_code OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
2585 p_rgp_cat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
2586 p_chr_id NUMBER,
2587 p_cle_id NUMBER ) IS
2588 SELECT rl.id,
2589 rl.rule_information2,
2590 rl.rule_information3,
2591 rl.rule_information6,
2592 rl.rule_information7,
2593 rl.rule_information8,
2594 cle.currency_code
2595 FROM okc_rule_groups_b rg,
2596 okc_rules_b rl,
2597 okc_k_lines_v cle,
2598 okc_line_styles_b lse
2599 WHERE cle.dnz_chr_id = p_chr_id
2600 AND cle.id = p_cle_id
2601 AND cle.lse_id = lse.id
2602 AND cle.cle_id IS NULL
2603 AND rg.dnz_chr_id = cle.dnz_chr_id
2604 AND rg.cle_id = cle.id
2605 AND rg.id = rl.rgp_id
2606 AND rg.rgd_code = p_rgd_code
2607 AND rl.rule_information_category = p_rgp_cat;
2608
2609 /*SELECT crl.id,
2610 crl.RULE_INFORMATION3,
2611 crl.RULE_INFORMATION6
2612 FROM OKC_RULE_GROUPS_B crg,
2613 OKC_RULES_B crl
2614 WHERE crl.rgp_id = crg.id
2615 and crg.RGD_CODE = p_rgd_code
2616 and crl.RULE_INFORMATION_CATEGORY = p_rgp_cat
2617 and crg.dnz_chr_id = p_chr_id
2618 and nvl(crg.cle_id,-1) = p_cle_id; */
2619
2620 ln_old_line_id OKL_K_LINES_V.ID%TYPE;
2621 ln_service_line_id OKL_K_LINES_V.ID%TYPE;
2622 ln_rule_amount OKC_RULES_V.RULE_INFORMATION2%TYPE;
2623 ln_rule_id OKC_RULES_V.ID%TYPE;
2624 lv_num_periods OKC_RULES_V.RULE_INFORMATION1%TYPE;
2625 lv_currency_code OKC_K_LINES_V.CURRENCY_CODE%TYPE;
2626 ln_rule_info_3 OKC_RULES_V.RULE_INFORMATION3%TYPE;
2627 ln_rule_info_6 OKC_RULES_V.RULE_INFORMATION6%TYPE;
2628 ln_rule_info_2 OKC_RULES_V.RULE_INFORMATION2%TYPE;
2629 ln_rule_info_7 OKC_RULES_V.RULE_INFORMATION7%TYPE;
2630 ln_rule_info_8 OKC_RULES_V.RULE_INFORMATION8%TYPE;
2631 -- end rravikir added for bug 3504415
2632
2633 BEGIN
2634 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2635 -- Call start_activity to create savepoint, check compatibility
2636 -- and initialize message list
2637 x_return_status := OKL_API.START_ACTIVITY (
2638 l_api_name
2639 ,p_init_msg_list
2640 ,'_PVT'
2641 ,x_return_status);
2642 -- Check if activity started successfully
2643 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2644 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2645 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2646 RAISE OKL_API.G_EXCEPTION_ERROR;
2647 END IF;
2648
2649 OPEN check_other_line(p_chr_id => p_contract_id);
2650 FETCH check_other_line INTO ln_dummy;
2651 CLOSE check_other_line;
2652
2653 IF ln_dummy = 1 THEN
2654 OPEN get_orig_sys_id1(p_contract_id);
2655 FETCH get_orig_sys_id1 INTO ln_old_chr_id;
2656 CLOSE get_orig_sys_id1;
2657
2658 -- get the new information
2659 /*OPEN get_asset_info(p_chr_id => p_contract_id);
2660 FETCH get_asset_info INTO ln_new_cap_amt;
2661 CLOSE get_asset_info;*/
2662
2663 -- rravikir modified for bug 3504415
2664 -- Top lines which are not having associated asset links for this contract
2665 -- are deleted first and then the actual links are deleted
2666
2667 -- Delete the Top lines which do have any assets linked to that
2668 FOR r_get_item_info_tls IN get_item_info_tls(p_dnz_chr_id => p_contract_id) LOOP
2669 OKL_CONTRACT_PUB.delete_contract_line(
2670 p_api_version => p_api_version,
2671 p_init_msg_list => p_init_msg_list,
2672 x_return_status => x_return_status,
2673 x_msg_count => x_msg_count,
2674 x_msg_data => x_msg_data,
2675 p_line_id => r_get_item_info_tls.id);
2676 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2677 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
2678 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2679 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
2680 END IF;
2681 END LOOP;
2682
2683 IF (G_DEBUG_SPLIT) THEN
2684 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Fee/Service Top lines deletion when
2685 assets not linked finished with ' || x_return_status || ' in
2686 l_process_split_contract procedure');
2687 IF (x_return_status <> 'S') THEN
2688 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Information : ' || x_msg_data);
2689 END IF;
2690 END IF;
2691
2692 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2693 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2694 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2695 RAISE OKL_API.G_EXCEPTION_ERROR;
2696 END IF;
2697
2698 -- Delete links which are not needed since the assets do not belong this contract
2699 FOR r_get_item_info IN get_item_info(p_dnz_chr_id => p_contract_id) LOOP
2700 OKL_CONTRACT_PUB.delete_contract_line(
2701 p_api_version => p_api_version,
2702 p_init_msg_list => p_init_msg_list,
2703 x_return_status => x_return_status,
2704 x_msg_count => x_msg_count,
2705 x_msg_data => x_msg_data,
2706 p_line_id => r_get_item_info.id);
2707
2708 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2709 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
2710 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2711 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
2712 END IF;
2713 END LOOP;
2714
2715 IF (G_DEBUG_SPLIT) THEN
2716 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Fee/Service child links deletion
2717 finished with ' || x_return_status || ' in
2718 l_process_split_contract procedure');
2719 IF (x_return_status <> 'S') THEN
2720 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Information : ' || x_msg_data);
2721 END IF;
2722 END IF;
2723
2724 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2725 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2726 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2727 RAISE OKL_API.G_EXCEPTION_ERROR;
2728 END IF;
2729 -- end rravikir modified for bug 3504415
2730
2731 FOR r_get_amt IN get_amt(p_chr_id => p_contract_id) LOOP
2732 ln_new_cap_amt := NULL;
2733
2734 -- Commented for Bug 3608423
2735 /* IF r_get_amt.amount <> 0 AND
2736 ln_new_cap_amt <> 0 AND
2737 ln_old_cap_amt <> 0 THEN
2738 ln_new_amt := ln_new_cap_amt * r_get_amt.amount/ln_old_cap_amt;
2739 END IF;
2740 IF r_get_amt.initial_direct_cost <> 0 AND
2741 ln_new_cap_amt <> 0 AND
2742 ln_old_cap_amt <> 0 THEN
2743 ln_new_init_cost := ln_new_cap_amt * r_get_amt.initial_direct_cost/ln_old_cap_amt;
2744 END IF;*/
2745
2746 -- To Get the cle addon Line Record
2747 x_return_status := get_rec_clev(r_get_amt.id,
2748 l_clev_rec);
2749 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2750 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
2751 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2752 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
2753 END IF;
2754 -- To Get the kle Model Line Record
2755 x_return_status := get_rec_klev(r_get_amt.id,
2756 l_klev_rec);
2757 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2758 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
2759 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2760 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
2761 END IF;
2762 IF l_klev_rec.id <> l_clev_rec.id THEN
2763 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
2764 END IF;
2765
2766 -- Added for Bug 3608423
2767 OPEN get_cap_link_asset_amount(p_cle_id => l_clev_rec.id);
2768 FETCH get_cap_link_asset_amount INTO ln_new_cap_amt;
2769 CLOSE get_cap_link_asset_amount;
2770
2771 l_klev_rec.amount := ln_new_cap_amt;
2772 l_klev_rec.capital_amount := ln_new_cap_amt;
2773 -- End for Bug 3608423
2774
2775 OKL_CONTRACT_PUB.update_contract_line(
2776 p_api_version => p_api_version,
2777 p_init_msg_list => p_init_msg_list,
2778 x_return_status => x_return_status,
2779 x_msg_count => x_msg_count,
2780 x_msg_data => x_msg_data,
2781 p_clev_rec => l_clev_rec,
2782 p_klev_rec => l_klev_rec,
2783 x_clev_rec => lx_clev_rec,
2784 x_klev_rec => lx_klev_rec);
2785
2786 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2787 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
2788 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2789 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
2790 END IF;
2791 END LOOP;
2792
2793 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2794 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2795 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2796 RAISE OKL_API.G_EXCEPTION_ERROR;
2797 END IF;
2798
2799 -- Get all the service lines having both expense and associated assets
2800 -- Proportionate the amount in the expense rule
2801 FOR r_get_service_lines IN get_service_lines(p_chr_id => p_contract_id) LOOP
2802
2803 -- Get the old service line amount to proporationate the amounts
2804 OPEN get_old_service_lines(p_new_chr_id => p_contract_id,
2805 p_old_chr_id => ln_old_chr_id,
2806 p_new_cle_id => r_get_service_lines.id);
2807 FETCH get_old_service_lines INTO ln_old_line_id, ln_old_service_amt;
2808 CLOSE get_old_service_lines;
2809
2810 FOR r_get_service_rule_info IN get_service_rule_info(p_chr_id => p_contract_id,
2811 p_cle_id => r_get_service_lines.id) LOOP
2812 ln_rule_id := r_get_service_rule_info.id;
2813 lv_currency_code := r_get_service_rule_info.currency_code;
2814 ln_service_line_id := r_get_service_lines.id;
2815 ln_new_service_amt := r_get_service_lines.amount;
2816
2817 IF (ln_rule_id <> OKL_API.G_MISS_NUM) AND
2818 (ln_rule_id IS NOT NULL) THEN
2819
2820 OPEN get_expense_service_rule_info(p_rgd_code => 'LAFEXP',
2821 p_rgp_cat => 'LAFEXP',
2822 p_chr_id => p_contract_id,
2823 p_cle_id => ln_service_line_id);
2824 FETCH get_expense_service_rule_info INTO lv_num_periods, ln_rule_amount;
2825 CLOSE get_expense_service_rule_info;
2826
2827 /*IF (lv_num_periods <> OKL_API.G_MISS_CHAR) AND
2828 (lv_num_periods IS NOT NULL) THEN
2829 ln_new_amt := ln_new_amt / lv_num_periods;
2830 END IF;*/
2831
2832 ln_calc_amt := (ln_new_service_amt/ln_old_service_amt) * ln_rule_amount;
2833
2834 -- Get the correct rounding amount
2835 ln_calc_amt := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(ln_calc_amt,
2836 lv_currency_code);
2837
2838 l_rulv_rec.id := ln_rule_id;
2839 l_rulv_rec.rule_information2 := ln_calc_amt;
2840
2841 OKL_RULE_PUB.update_rule(
2842 p_api_version => p_api_version,
2843 p_init_msg_list => p_init_msg_list,
2844 x_return_status => x_return_status,
2845 x_msg_count => x_msg_count,
2846 x_msg_data => x_msg_data,
2847 p_rulv_rec => l_rulv_rec,
2848 x_rulv_rec => lx_rulv_rec);
2849 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2850 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
2851 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2852 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
2853 END IF;
2854 END IF;
2855 END LOOP;
2856 END LOOP;
2857
2858 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2859 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2860 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2861 RAISE OKL_API.G_EXCEPTION_ERROR;
2862 END IF;
2863
2864 -- Get the payments defined for service line
2865 -- Proportionate the amount
2866 FOR r_get_service_lines IN get_service_lines(p_chr_id => p_contract_id) LOOP
2867
2868 -- Get the old service line amount to proporationate the amounts
2869 OPEN get_old_service_lines(p_new_chr_id => p_contract_id,
2870 p_old_chr_id => ln_old_chr_id,
2871 p_new_cle_id => r_get_service_lines.id);
2872 FETCH get_old_service_lines INTO ln_old_line_id, ln_old_service_amt;
2873 CLOSE get_old_service_lines;
2874
2875 FOR r_get_service_line_payments IN get_service_line_payments(p_rgd_code => 'LALEVL',
2876 p_rgp_cat => 'LASLL',
2877 p_chr_id => p_contract_id,
2878 p_cle_id => r_get_service_lines.id) LOOP
2879 ln_rule_id := r_get_service_line_payments.id;
2880 lv_currency_code := r_get_service_line_payments.currency_code;
2881 ln_service_line_id := r_get_service_lines.id;
2882 ln_new_service_amt := r_get_service_lines.amount;
2883 ln_rule_info_3 := r_get_service_line_payments.rule_information3;
2884 ln_rule_info_6 := r_get_service_line_payments.rule_information6;
2885 ln_rule_info_2 := r_get_service_line_payments.rule_information2;
2886 ln_rule_info_7 := r_get_service_line_payments.rule_information7;
2887 ln_rule_info_8 := r_get_service_line_payments.rule_information8;
2888
2889 IF (ln_rule_id <> OKL_API.G_MISS_NUM) AND
2890 (ln_rule_id IS NOT NULL) THEN
2891
2892 IF (ln_rule_info_6 <> OKL_API.G_MISS_CHAR) AND
2893 (ln_rule_info_6 IS NOT NULL) THEN
2894
2895 ln_calc_amt := (ln_new_service_amt/ln_old_service_amt) * ln_rule_info_6;
2896 -- Get the correct rounding amount
2897 ln_calc_amt := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(ln_calc_amt,
2898 lv_currency_code);
2899
2900 lp_rulv_rec.id := ln_rule_id;
2901 lp_rulv_rec.rule_information6 := ln_calc_amt;
2902 lp_rulv_rec.rule_information2 := ln_rule_info_2;
2903 lp_rulv_rec.rule_information8 := NULL;
2904 ELSIF (ln_rule_info_8 <> OKL_API.G_MISS_CHAR) AND
2905 (ln_rule_info_8 IS NOT NULL) THEN
2906
2907 ln_calc_amt := (ln_new_service_amt/ln_old_service_amt) * ln_rule_info_8;
2908 -- Get the correct rounding amount
2909 ln_calc_amt := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(ln_calc_amt,
2910 lv_currency_code);
2911
2912 lp_rulv_rec.id := ln_rule_id;
2913 lp_rulv_rec.rule_information8 := ln_calc_amt;
2914 lp_rulv_rec.rule_information2 := ln_rule_info_2;
2915 lp_rulv_rec.rule_information6 := NULL;
2916 END IF;
2917
2918 OKL_RULE_PUB.update_rule(
2919 p_api_version => p_api_version,
2920 p_init_msg_list => p_init_msg_list,
2921 x_return_status => x_return_status,
2922 x_msg_count => x_msg_count,
2923 x_msg_data => x_msg_data,
2924 p_rulv_rec => lp_rulv_rec,
2925 x_rulv_rec => lx_rulv_rec);
2926 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2927 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
2928 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2929 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
2930 END IF;
2931 END IF;
2932 END LOOP;
2933 END LOOP;
2934
2935 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2936 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2937 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2938 RAISE OKL_API.G_EXCEPTION_ERROR;
2939 END IF;
2940
2941
2942 -- Commented for Bug 3608423
2943 -- rravikir added for bug 3504415
2944 -- Get the rule (applies to 'EXPENSE', 'MISCLLANEOUS' 'FINANCE' fee types)
2945 -- id and update the amount
2946 /* OPEN get_rule_pymt1(p_chr_id => p_contract_id,
2947 p_cle_id => l_clev_rec.id);
2948 FETCH get_rule_pymt1 INTO ln_rule_id, lv_currency_code;
2949 CLOSE get_rule_pymt1;
2950
2951 IF (ln_rule_id <> OKL_API.G_MISS_NUM) AND
2952 (ln_rule_id IS NOT NULL) THEN
2953
2954 OPEN get_rule_periods(p_rgd_code => 'LAFEXP',
2955 p_rgp_cat => 'LAFEXP',
2956 p_chr_id => p_contract_id,
2957 p_cle_id => l_clev_rec.id);
2958 FETCH get_rule_periods INTO lv_num_periods;
2959 CLOSE get_rule_periods;
2960
2961 IF (lv_num_periods <> OKL_API.G_MISS_CHAR) AND
2962 (lv_num_periods IS NOT NULL) THEN
2963 ln_new_amt := ln_new_amt / lv_num_periods;
2964 END IF;
2965
2966 -- Get the correct rounding amount
2967 ln_new_amt := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(ln_new_amt,
2968 lv_currency_code);
2969
2970 l_rulv_rec.id := ln_rule_id;
2971 l_rulv_rec.rule_information2 := ln_new_amt;
2972
2973 OKL_RULE_PUB.update_rule(
2974 p_api_version => p_api_version,
2975 p_init_msg_list => p_init_msg_list,
2976 x_return_status => x_return_status,
2977 x_msg_count => x_msg_count,
2978 x_msg_data => x_msg_data,
2979 p_rulv_rec => l_rulv_rec,
2980 x_rulv_rec => lx_rulv_rec);
2981 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2982 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
2983 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2984 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
2985 END IF;
2986 END IF;
2987 -- end rravikir added for bug 3504415
2988
2989 -- We need to pro-rate the payment amount of top service or fee line
2990 FOR r_get_rule_pymt IN get_rule_pymt(p_chr_id => p_contract_id,
2991 p_cle_id => r_get_amt.id) LOOP
2992 ln_payment_amount := r_get_rule_pymt.payment_amount * ln_new_cap_amt/ln_old_cap_amt;
2993 l_rulv_rec.id := r_get_rule_pymt.id;
2994 l_rulv_rec.rule_information6 := ln_payment_amount;
2995 l_rulv_rec.rule_information2 := r_get_rule_pymt.rl_date;
2996 OKL_RULE_PUB.update_rule(
2997 p_api_version => p_api_version,
2998 p_init_msg_list => p_init_msg_list,
2999 x_return_status => x_return_status,
3000 x_msg_count => x_msg_count,
3001 x_msg_data => x_msg_data,
3002 p_rulv_rec => l_rulv_rec,
3003 x_rulv_rec => lx_rulv_rec);
3004 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3005 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3006 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3007 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3008 END IF;
3009 END LOOP;
3010 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3011 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3012 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3013 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3014 END IF;
3015
3016 -- Get the amount of the old contract
3017 FOR r_get_sls_amt IN get_sls_amt(p_chr_id => ln_old_chr_id) LOOP
3018 -- we need the fee or service lines id.
3019 lt_old_cle_amt_tbl(k).cle_id := r_get_sls_amt.id;
3020 lt_old_cle_amt_tbl(k).amount := r_get_sls_amt.amount;
3021 lt_old_cle_amt_tbl(k).orig_cle_id := r_get_sls_amt.orig_system_id1;
3022 k := k + 1;
3023 END LOOP;
3024 -- Summing up amount of the sub lines populating Amount and Capital amount
3025 FOR r_get_sls_amt IN get_sls_amt(p_chr_id => p_contract_id) LOOP
3026 -- To Get the cle Line Record
3027 x_return_status := get_rec_clev(r_get_sls_amt.id,
3028 l_clev_rec);
3029 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3030 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
3031 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3032 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3033 END IF;
3034 -- To Get the kle Model Line Record
3035 x_return_status := get_rec_klev(r_get_sls_amt.id,
3036 l_klev_rec);
3037 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3038 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
3039 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3040 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3041 END IF;
3042 IF l_klev_rec.id <> l_clev_rec.id THEN
3043 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3044 END IF;
3045 l_klev_rec.amount := r_get_sls_amt.amount;
3046 l_klev_rec.capital_amount := r_get_sls_amt.amount;
3047
3048 OKL_CONTRACT_PUB.update_contract_line(
3049 p_api_version => p_api_version,
3050 p_init_msg_list => p_init_msg_list,
3051 x_return_status => x_return_status,
3052 x_msg_count => x_msg_count,
3053 x_msg_data => x_msg_data,
3054 p_clev_rec => l_clev_rec,
3055 p_klev_rec => l_klev_rec,
3056 x_clev_rec => lx_clev_rec,
3057 x_klev_rec => lx_klev_rec);
3058 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3059 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3060 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3061 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3062 END IF;
3063 -- We need to pro-rate the payment amount of sub service or fee line
3064 IF lt_old_cle_amt_tbl.COUNT > 0 THEN
3065 k := lt_old_cle_amt_tbl.FIRST;
3066 LOOP
3067 IF lt_old_cle_amt_tbl(k).cle_id = l_clev_rec.orig_system_id1 THEN
3068 FOR r_get_sls_rule_pymt IN get_sls_rule_pymt(p_chr_id => p_contract_id,
3069 p_cle_id => l_clev_rec.id) LOOP
3070 ln_sls_payment_amount := r_get_sls_rule_pymt.payment_amount * l_klev_rec.amount/lt_old_cle_amt_tbl(k).amount;
3071 r_rulv_rec.id := r_get_sls_rule_pymt.id;
3072 r_rulv_rec.rule_information6 := ln_sls_payment_amount;
3073 OKL_RULE_PUB.update_rule(
3074 p_api_version => p_api_version,
3075 p_init_msg_list => p_init_msg_list,
3076 x_return_status => x_return_status,
3077 x_msg_count => x_msg_count,
3078 x_msg_data => x_msg_data,
3079 p_rulv_rec => r_rulv_rec,
3080 x_rulv_rec => rx_rulv_rec);
3081
3082 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3083 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3084 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3085 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3086 END IF;
3087 END LOOP;
3088 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3089 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3090 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3091 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3092 END IF;
3093 END IF;
3094 EXIT WHEN (k = lt_old_cle_amt_tbl.LAST);
3095 k := lt_old_cle_amt_tbl.NEXT(k);
3096 END LOOP;
3097 END IF;
3098 END LOOP;
3099 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3100 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3101 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3102 RAISE OKL_API.G_EXCEPTION_ERROR;
3103 END IF;*/
3104 -- End Commented for Bug 3608423
3105
3106 END IF;
3107 OKL_API.END_ACTIVITY (x_msg_count,
3108 x_msg_data );
3109 EXCEPTION
3110 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3111 IF get_amt%ISOPEN THEN
3112 CLOSE get_amt;
3113 END IF;
3114 IF get_item_info%ISOPEN THEN
3115 CLOSE get_item_info;
3116 END IF;
3117 /*IF get_asset_info%ISOPEN THEN
3118 CLOSE get_asset_info;
3119 END IF;*/
3120 IF get_orig_sys_id1%ISOPEN THEN
3121 CLOSE get_orig_sys_id1;
3122 END IF;
3123 IF check_other_line%ISOPEN THEN
3124 CLOSE check_other_line;
3125 END IF;
3126 /*IF get_sls_rule_pymt%ISOPEN THEN
3127 CLOSE get_sls_rule_pymt;
3128 END IF;*/
3129 /*IF get_rule_pymt%ISOPEN THEN
3130 CLOSE get_rule_pymt;
3131 END IF;*/
3132 /*IF get_sls_amt%ISOPEN THEN
3133 CLOSE get_sls_amt;
3134 END IF;*/
3135 IF get_item_info_tls%ISOPEN THEN
3136 CLOSE get_item_info_tls;
3137 END IF;
3138 IF get_service_rule_info%ISOPEN THEN
3139 CLOSE get_service_rule_info;
3140 END IF;
3141 IF get_expense_service_rule_info%ISOPEN THEN
3142 CLOSE get_expense_service_rule_info;
3143 END IF;
3144 IF get_cap_link_asset_amount%ISOPEN THEN
3145 CLOSE get_cap_link_asset_amount;
3146 END IF;
3147 IF get_service_lines%ISOPEN THEN
3148 CLOSE get_service_lines;
3149 END IF;
3150 IF get_service_line_payments%ISOPEN THEN
3151 CLOSE get_service_line_payments;
3152 END IF;
3153 IF get_old_service_lines%ISOPEN THEN
3154 CLOSE get_old_service_lines;
3155 END IF;
3156 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3157 l_api_name,
3158 G_PKG_NAME,
3159 'OKL_API.G_RET_STS_ERROR',
3160 x_msg_count,
3161 x_msg_data,
3162 '_PVT');
3163 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3164 IF get_amt%ISOPEN THEN
3165 CLOSE get_amt;
3166 END IF;
3167 IF get_item_info%ISOPEN THEN
3168 CLOSE get_item_info;
3169 END IF;
3170 /*IF get_asset_info%ISOPEN THEN
3171 CLOSE get_asset_info;
3172 END IF;*/
3173 IF get_orig_sys_id1%ISOPEN THEN
3174 CLOSE get_orig_sys_id1;
3175 END IF;
3176 IF check_other_line%ISOPEN THEN
3177 CLOSE check_other_line;
3178 END IF;
3179 /*IF get_sls_rule_pymt%ISOPEN THEN
3180 CLOSE get_sls_rule_pymt;
3181 END IF;*/
3182 /*IF get_rule_pymt%ISOPEN THEN
3183 CLOSE get_rule_pymt;
3184 END IF;*/
3185 /*IF get_sls_amt%ISOPEN THEN
3186 CLOSE get_sls_amt;
3187 END IF;*/
3188 IF get_item_info_tls%ISOPEN THEN
3189 CLOSE get_item_info_tls;
3190 END IF;
3191 IF get_service_rule_info%ISOPEN THEN
3192 CLOSE get_service_rule_info;
3193 END IF;
3194 IF get_expense_service_rule_info%ISOPEN THEN
3195 CLOSE get_expense_service_rule_info;
3196 END IF;
3197 IF get_cap_link_asset_amount%ISOPEN THEN
3198 CLOSE get_cap_link_asset_amount;
3199 END IF;
3200 IF get_service_lines%ISOPEN THEN
3201 CLOSE get_service_lines;
3202 END IF;
3203 IF get_service_line_payments%ISOPEN THEN
3204 CLOSE get_service_line_payments;
3205 END IF;
3206 IF get_old_service_lines%ISOPEN THEN
3207 CLOSE get_old_service_lines;
3208 END IF;
3209 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
3210 l_api_name,
3211 G_PKG_NAME,
3212 'OKL_API.G_RET_STS_UNEXP_ERROR',
3213 x_msg_count,
3214 x_msg_data,
3215 '_PVT');
3216 WHEN OTHERS THEN
3217 IF get_amt%ISOPEN THEN
3218 CLOSE get_amt;
3219 END IF;
3220 IF get_item_info%ISOPEN THEN
3221 CLOSE get_item_info;
3222 END IF;
3223 /*IF get_asset_info%ISOPEN THEN
3224 CLOSE get_asset_info;
3225 END IF;*/
3226 IF get_orig_sys_id1%ISOPEN THEN
3227 CLOSE get_orig_sys_id1;
3228 END IF;
3229 IF check_other_line%ISOPEN THEN
3230 CLOSE check_other_line;
3231 END IF;
3232 /*IF get_sls_rule_pymt%ISOPEN THEN
3233 CLOSE get_sls_rule_pymt;
3234 END IF;*/
3235 /*IF get_rule_pymt%ISOPEN THEN
3236 CLOSE get_rule_pymt;
3237 END IF;*/
3238 /*IF get_sls_amt%ISOPEN THEN
3239 CLOSE get_sls_amt;
3240 END IF;*/
3241 IF get_item_info_tls%ISOPEN THEN
3242 CLOSE get_item_info_tls;
3243 END IF;
3244 IF get_service_rule_info%ISOPEN THEN
3245 CLOSE get_service_rule_info;
3246 END IF;
3247 IF get_expense_service_rule_info%ISOPEN THEN
3248 CLOSE get_expense_service_rule_info;
3249 END IF;
3250 IF get_cap_link_asset_amount%ISOPEN THEN
3251 CLOSE get_cap_link_asset_amount;
3252 END IF;
3253 IF get_service_lines%ISOPEN THEN
3254 CLOSE get_service_lines;
3255 END IF;
3256 IF get_service_line_payments%ISOPEN THEN
3257 CLOSE get_service_line_payments;
3258 END IF;
3259 IF get_old_service_lines%ISOPEN THEN
3260 CLOSE get_old_service_lines;
3261 END IF;
3262 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
3263 l_api_name,
3264 G_PKG_NAME,
3265 'OTHERS',
3266 x_msg_count,
3267 x_msg_data,
3268 '_PVT');
3269 END l_process_split_contract;
3270 -----------------------------------------------------------------------------------------------
3271 --------------------------------- split Contract after yield ----------------------------------
3272 -----------------------------------------------------------------------------------------------
3273 PROCEDURE split_contract_after_yield(p_api_version IN NUMBER,
3274 p_init_msg_list IN VARCHAR2,
3275 x_return_status OUT NOCOPY VARCHAR2,
3276 x_msg_count OUT NOCOPY NUMBER,
3277 x_msg_data OUT NOCOPY VARCHAR2,
3278 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE) IS
3279 l_api_name VARCHAR2(35) := 'SPLIT_CONTRACT_AFTER_YIELD';
3280 l_proc_name VARCHAR2(35) := 'SPLIT_CONTRACT_AFTER_YIELD';
3281
3282 REPORTING_EXCEPTION EXCEPTION;
3283 ln_dummy NUMBER :=0;
3284 ln1_dummy NUMBER :=0;
3285 ln_orig_system_id1 OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE;
3286 lv_ok_to_terminate_orig_K VARCHAR2(3):= 'N';
3287 i NUMBER := 0;
3288 j NUMBER := 0;
3289 k NUMBER := 0;
3290 l_cimv_rec cimv_rec_type;
3291 lx_cimv_rec cimv_rec_type;
3292 l_trxv_rec trxv_rec_type;
3293 lx_trxv_rec trxv_rec_type;
3294 l_tcnv_rec OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
3295 lx_tcnv_rec OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
3296
3297 -- rravikir added for Bug 2927173, 2901442
3298 lprv_rec OKL_REV_LOSS_PROV_PVT.lprv_rec_type;
3299 ln_orig_contract_id OKC_K_HEADERS_B.ID%TYPE;
3300 lv_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
3301 ld_contract_start_date OKC_K_HEADERS_B.START_DATE%TYPE;
3302 ld_split_date OKL_TRX_CONTRACTS.DATE_TRANSACTION_OCCURRED%TYPE;
3303 -- end for Bug 2927173, 2901442
3304
3305 -- rravikir added for Bug 3487162
3306 l_gl_date DATE;
3307 -- end for Bug 3487162
3308
3309 ln_chr_id OKC_K_HEADERS_V.ID%TYPE;
3310 ln_service_id OKC_K_HEADERS_V.ID%TYPE;
3311
3312 CURSOR get_old_service_id(p_chr_id OKC_K_HEADERS_B.ID%TYPE)
3313 IS
3314 SELECT object1_id1 service_contract_id
3315 FROM okc_k_rel_objs_v rel
3316 WHERE rel.chr_id = P_chr_id;
3317
3318 CURSOR check_other_line(p_chr_id OKC_K_HEADERS_B.ID%TYPE)
3319 IS
3320 SELECT '1'
3321 FROM DUAL
3322 WHERE EXISTS (SELECT '1'
3323 FROM OKC_K_LINES_B cle,
3324 OKC_LINE_STYLES_b lse
3325 WHERE cle.dnz_chr_id = p_chr_id
3326 AND lse.id = cle.lse_id
3327 AND lse.lty_code IN (G_USG_LINE_LTY_CODE,
3328 G_USL_LINE_LTY_CODE));
3329
3330 -- to check weather the given Chr id belongs to a Split Contract process
3331 CURSOR check_split_k_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
3332 SELECT 1
3333 FROM dual
3334 WHERE EXISTS (SELECT 1
3335 FROM okl_trx_contracts trx,
3336 okl_trx_types_tl try,
3337 okl_k_headers khr
3338 WHERE try.name = 'Split Contract'
3339 AND try.LANGUAGE = 'US'
3340 AND trx.try_id = try.id
3341 --rkuttiya added for 12.1.1 Multi GAAP
3342 AND trx.representation_type = 'PRIMARY'
3343 --
3344 AND trx.tsu_code = 'ENTERED'
3345 AND khr.id = trx.khr_id
3346 AND trx.khr_id = p_chr_id);
3347
3348 -- To get the orig system id for p_chr_id
3349 CURSOR get_orig_sys_id1(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
3350 SELECT orig_system_id1
3351 FROM okc_k_headers_b
3352 WHERE id = p_chr_id
3353 AND orig_system_source_code = 'OKL_SPLIT';
3354
3355 -- To get status of splited transaction Chr id
3356 CURSOR check_ctrct_status(p_chr_id OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE) IS
3357 SELECT CHR.id chr_id,
3358 sts_code sts_code,
3359 trx.id trx_id
3360 FROM okl_trx_contracts trx,
3361 okl_trx_types_tl try,
3362 okc_k_headers_b CHR
3363 WHERE try.name = 'Split Contract'
3364 AND try.LANGUAGE = 'US'
3365 AND trx.try_id = try.id
3366 AND trx.tsu_code = 'ENTERED'
3367 AND trx.khr_id = CHR.orig_system_id1
3368 --rkuttiya added for 12.1.1 Multi GAAP
3369 AND trx.representation_type = 'PRIMARY'
3370 --
3371 AND CHR.orig_system_source_code = 'OKL_SPLIT'
3372 AND CHR.orig_system_id1= p_chr_id;
3373
3374 -- To get the orig system id for Fixed Asset lines of p_chr_id
3375 CURSOR get_orig_fa(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
3376 SELECT orig_system_id1 orig_cle_fa,
3377 txl.tas_id tas_id_fa,
3378 cle.id id
3379 FROM OKC_K_LINES_V cle,
3380 OKC_LINE_STYLES_V lse,
3381 OKL_TXL_ASSETS_B txl
3382 WHERE cle.dnz_chr_id = p_chr_id
3383 AND cle.lse_id = lse.id
3384 AND lse.lty_code = G_FA_LINE_LTY_CODE
3385 AND cle.id = txl.kle_id;
3386
3387 -- To get the orig system id for Install Base lines of p_chr_id
3388 CURSOR get_orig_ib(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
3389 SELECT orig_system_id1 orig_cle_ib,
3390 iti.tas_id tas_id_ib,
3391 cle.id id
3392 FROM OKC_K_LINES_V cle,
3393 OKC_LINE_STYLES_V lse,
3394 OKL_TXL_ITM_INSTS iti
3395 WHERE cle.dnz_chr_id = p_chr_id
3396 AND cle.lse_id = lse.id
3397 AND lse.lty_code = G_IB_LINE_LTY_CODE
3398 AND cle.id = iti.kle_id;
3399
3400 -- To get the item information from original line id and original contract id
3401 CURSOR get_item_info(p_orig_chr_id OKC_K_HEADERS_B.ID%TYPE,
3402 p_orig_cle_id OKC_K_LINES_B.ID%TYPE) IS
3403 SELECT object1_id1,
3404 object1_id2
3405 FROM okc_k_items
3406 WHERE cle_id = p_orig_cle_id
3407 AND dnz_chr_Id = p_orig_chr_id;
3408
3409 -- To get the Contract number and Split contract transaction date
3410 -- rravikir added - Bug 2927173, 2901442
3411 CURSOR get_split_info(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
3412 SELECT KHR.ID, KHR.CONTRACT_NUMBER, KHR.START_DATE, TRX.DATE_TRANSACTION_OCCURRED
3413 FROM OKC_K_HEADERS_B KHR,
3414 OKL_TRX_CONTRACTS TRX
3415 WHERE TRX.KHR_ID = KHR.ID
3416 AND TRX.TSU_CODE = 'PROCESSED'
3417 AND TRX.TCN_TYPE = 'SPLC'
3418 --rkuttiya added for 12.1.1 Multi GAAP
3419 AND TRX.REPRESENTATION_TYPE = 'PRIMARY'
3420 --
3421 AND KHR.ID = p_chr_id;
3422 -- End Bug 2927173, 2901442
3423
3424 BEGIN
3425 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3426 -- Call start_activity to create savepoint, check compatibility
3427 -- and initialize message list
3428 x_return_status := OKL_API.START_ACTIVITY (
3429 l_api_name
3430 ,p_init_msg_list
3431 ,'_PVT'
3432 ,x_return_status);
3433 -- Check if activity started successfully
3434 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3435 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3436 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3437 RAISE OKL_API.G_EXCEPTION_ERROR;
3438 END IF;
3439 -- To get the orig system id for
3440 OPEN get_orig_sys_id1(p_chr_id => p_chr_id);
3441 FETCH get_orig_sys_id1 INTO ln_orig_system_id1;
3442 IF get_orig_sys_id1%NOTFOUND THEN
3443 ln_dummy := 0;
3444 ELSE
3445 ln_dummy := 1;
3446 END IF;
3447 CLOSE get_orig_sys_id1;
3448
3449 IF ln_dummy = 1 THEN
3450 -- we need to Make sure that the contract that we are finishing up is
3451 -- a split contract.
3452 OPEN check_split_k_csr(p_chr_id => ln_orig_system_id1);
3453 FETCH check_split_k_csr INTO ln_dummy;
3454 IF check_split_k_csr%NOTFOUND THEN
3455 OKL_API.set_message(p_app_name => G_APP_NAME,
3456 p_msg_name => G_NO_MATCHING_RECORD,
3457 p_token1 => G_COL_NAME_TOKEN,
3458 p_token1_value => 'OKC_K_HEADERS_B.ORIG_SYSTEM_ID1');
3459
3460 IF (G_DEBUG_SPLIT) THEN
3461 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Contract used in the Split process
3462 is not of the type "Split Contract"');
3463 END IF;
3464
3465 RAISE OKL_API.G_EXCEPTION_ERROR;
3466 END IF;
3467 CLOSE check_split_k_csr;
3468
3469 -- We have to call the journal Entries for the same
3470 OKL_LA_JE_PUB.generate_journal_entries(
3471 p_api_version => p_api_version,
3472 p_init_msg_list => p_init_msg_list,
3473 p_commit => OKL_API.G_FALSE,
3474 p_contract_id => p_chr_id,
3475 p_transaction_type => 'Split Contract',
3476 p_draft_yn => OKL_API.G_TRUE,
3477 p_memo_yn => OKL_API.G_FALSE,
3478 x_return_status => x_return_status,
3479 x_msg_count => x_msg_count,
3480 x_msg_data => x_msg_data);
3481
3482 IF (G_DEBUG_SPLIT) THEN
3483 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'OKL_LA_JE_PUB.generate_journal_entries
3484 procedure finished with status ' || x_return_status || ' in
3485 split_contract_after_yield procedure');
3486 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Information : ' || x_msg_data);
3487 END IF;
3488
3489 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3490 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3491 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3492 RAISE OKL_API.G_EXCEPTION_ERROR;
3493 END IF;
3494 -- To get all the assets for the p_chr_id
3495 FOR r_get_orig_fa IN get_orig_fa(p_chr_id => p_chr_id) LOOP
3496 IF get_orig_fa%NOTFOUND THEN
3497 OKL_API.set_message(p_app_name => G_APP_NAME,
3498 p_msg_name => G_NO_MATCHING_RECORD,
3499 p_token1 => G_COL_NAME_TOKEN,
3500 p_token1_value => 'p_chr_id');
3501 x_return_status := OKL_API.G_RET_STS_ERROR;
3502 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3503 END IF;
3504 -- to get all the new line item information
3505 x_return_status := get_rec_cimv(r_get_orig_fa.id,
3506 p_chr_id,
3507 l_cimv_rec);
3508 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3509 OKL_API.set_message(p_app_name => G_APP_NAME,
3510 p_msg_name => G_NO_MATCHING_RECORD,
3511 p_token1 => G_COL_NAME_TOKEN,
3512 p_token1_value => 'OKC_K_ITEMS_V record');
3513 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
3514 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3515 OKL_API.set_message(p_app_name => G_APP_NAME,
3516 p_msg_name => G_NO_MATCHING_RECORD,
3517 p_token1 => G_COL_NAME_TOKEN,
3518 p_token1_value => 'OKC_K_ITEMS_V record');
3519 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3520 END IF;
3521
3522 -- To get the old information of the old asset
3523 OPEN get_item_info(p_orig_chr_id => ln_orig_system_id1,
3524 p_orig_cle_id => r_get_orig_fa.orig_cle_fa);
3525 IF get_item_info%NOTFOUND THEN
3526 OKL_API.set_message(p_app_name => G_APP_NAME,
3527 p_msg_name => G_NO_MATCHING_RECORD,
3528 p_token1 => G_COL_NAME_TOKEN,
3529 p_token1_value => 'p_chr_id');
3530 x_return_status := OKL_API.G_RET_STS_ERROR;
3531 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3532 END IF;
3533 FETCH get_item_info INTO l_cimv_rec.object1_id1,
3534 l_cimv_rec.object1_id2;
3535 CLOSE get_item_info;
3536 OKL_OKC_MIGRATION_PVT.update_contract_item(p_api_version => p_api_version,
3537 p_init_msg_list => p_init_msg_list,
3538 x_return_status => x_return_status,
3539 x_msg_count => x_msg_count,
3540 x_msg_data => x_msg_data,
3541 p_cimv_rec => l_cimv_rec,
3542 x_cimv_rec => lx_cimv_rec);
3543 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3544 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
3545 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3546 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3547 END IF;
3548
3549 -- We need to make the changes to Transaction asset information as processed
3550 x_return_status := get_tasv_rec(r_get_orig_fa.tas_id_fa,
3551 l_trxv_rec);
3552 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3553 OKL_API.set_message(p_app_name => G_APP_NAME,
3554 p_msg_name => G_NO_MATCHING_RECORD,
3555 p_token1 => G_COL_NAME_TOKEN,
3556 p_token1_value => 'TAS Rec');
3557 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
3558 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3559 OKL_API.set_message(p_app_name => G_APP_NAME,
3560 p_msg_name => G_NO_MATCHING_RECORD,
3561 p_token1 => G_COL_NAME_TOKEN,
3562 p_token1_value => 'TAS Rec');
3563 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3564 END IF;
3565 l_trxv_rec.tsu_code := 'PROCESSED';
3566 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
3567 p_api_version => p_api_version,
3568 p_init_msg_list => p_init_msg_list,
3569 x_return_status => x_return_status,
3570 x_msg_count => x_msg_count,
3571 x_msg_data => x_msg_data,
3572 p_thpv_rec => l_trxv_rec,
3573 x_thpv_rec => lx_trxv_rec);
3574 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3575 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
3576 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3577 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3578 END IF;
3579 END LOOP;
3580
3581 IF (G_DEBUG_SPLIT) THEN
3582 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Switching Assets process
3583 finished with status ' || x_return_status || ' in
3584 split_contract_after_yield procedure');
3585 END IF;
3586
3587 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3588 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3589 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3590 RAISE OKL_API.G_EXCEPTION_ERROR;
3591 END IF;
3592
3593 -- To get the Install Base information for the p_chr_id
3594 FOR r_get_orig_ib IN get_orig_ib(p_chr_id => p_chr_id) LOOP
3595 IF get_orig_ib%NOTFOUND THEN
3596 x_return_status := OKL_API.G_RET_STS_ERROR;
3597 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3598 END IF;
3599 -- to get all the new line item information
3600 x_return_status := get_rec_cimv(r_get_orig_ib.id,
3601 p_chr_id,
3602 l_cimv_rec);
3603 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3604 OKL_API.set_message(p_app_name => G_APP_NAME,
3605 p_msg_name => G_NO_MATCHING_RECORD,
3606 p_token1 => G_COL_NAME_TOKEN,
3607 p_token1_value => 'OKC_K_ITEMS_V record');
3608 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
3609 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3610 OKL_API.set_message(p_app_name => G_APP_NAME,
3611 p_msg_name => G_NO_MATCHING_RECORD,
3612 p_token1 => G_COL_NAME_TOKEN,
3613 p_token1_value => 'OKC_K_ITEMS_V record');
3614 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3615 END IF;
3616 -- To get the old information of the old asset
3617 OPEN get_item_info(p_orig_chr_id => ln_orig_system_id1,
3618 p_orig_cle_id => r_get_orig_ib.orig_cle_ib);
3619 IF get_item_info%NOTFOUND THEN
3620 OKL_API.set_message(p_app_name => G_APP_NAME,
3621 p_msg_name => G_NO_MATCHING_RECORD,
3622 p_token1 => G_COL_NAME_TOKEN,
3623 p_token1_value => 'Orig system id1');
3624 x_return_status := OKL_API.G_RET_STS_ERROR;
3625 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3626 END IF;
3627 FETCH get_item_info INTO l_cimv_rec.object1_id1,
3628 l_cimv_rec.object1_id2;
3629 CLOSE get_item_info;
3630 OKL_OKC_MIGRATION_PVT.update_contract_item(p_api_version => p_api_version,
3631 p_init_msg_list => p_init_msg_list,
3632 x_return_status => x_return_status,
3633 x_msg_count => x_msg_count,
3634 x_msg_data => x_msg_data,
3635 p_cimv_rec => l_cimv_rec,
3636 x_cimv_rec => lx_cimv_rec);
3637 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3638 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
3639 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3640 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3641 END IF;
3642
3643 -- We need to make the changes to Transaction asset information as processed
3644 x_return_status := get_tasv_rec(r_get_orig_ib.tas_id_ib,
3645 l_trxv_rec);
3646 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3647 OKL_API.set_message(p_app_name => G_APP_NAME,
3648 p_msg_name => G_NO_MATCHING_RECORD,
3649 p_token1 => G_COL_NAME_TOKEN,
3650 p_token1_value => 'TAS Rec');
3651 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
3652 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3653 OKL_API.set_message(p_app_name => G_APP_NAME,
3654 p_msg_name => G_NO_MATCHING_RECORD,
3655 p_token1 => G_COL_NAME_TOKEN,
3656 p_token1_value => 'TAS Rec');
3657 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3658 END IF;
3659 l_trxv_rec.tsu_code := 'PROCESSED';
3660 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
3661 p_api_version => p_api_version,
3662 p_init_msg_list => p_init_msg_list,
3663 x_return_status => x_return_status,
3664 x_msg_count => x_msg_count,
3665 x_msg_data => x_msg_data,
3666 p_thpv_rec => l_trxv_rec,
3667 x_thpv_rec => lx_trxv_rec);
3668 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3669 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
3670 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3671 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3672 END IF;
3673 END LOOP;
3674
3675 IF (G_DEBUG_SPLIT) THEN
3676 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Obtaining Install Base information
3677 finished with status ' || x_return_status || ' in
3678 split_contract_after_yield procedure');
3679 IF (x_return_status <> 'S') THEN
3680 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
3681 END IF;
3682 END IF;
3683
3684 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3685 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3686 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3687 RAISE OKL_API.G_EXCEPTION_ERROR;
3688 END IF;
3689
3690 -- Activating the Usage lines also if exits
3691 OPEN check_other_line(p_chr_id => p_chr_id);
3692 FETCH check_other_line INTO ln1_dummy;
3693 CLOSE check_other_line;
3694 IF ln1_dummy <> 0 THEN
3695 --Process the Usage header
3696 OKL_UBB_INTEGRATION_PUB.create_ubb_contract (
3697 p_api_version => p_api_version,
3698 p_init_msg_list => p_init_msg_list,
3699 x_return_status => x_return_status,
3700 x_msg_count => x_msg_count,
3701 x_msg_data => x_msg_data,
3702 p_chr_id => p_chr_id,
3703 x_chr_id => ln_chr_id);
3704
3705 IF (G_DEBUG_SPLIT) THEN
3706 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'OKL_UBB_INTEGRATION_PUB.create_ubb_contract
3707 procedure finished with status ' || x_return_status || ' in
3708 split_contract_after_yield procedure');
3709 IF (x_return_status <> 'S') THEN
3710 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
3711 END IF;
3712 END IF;
3713
3714 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3715 RAISE OKL_API.G_EXCEPTION_ERROR;
3716 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3717 RAISE OKL_API.G_EXCEPTION_ERROR;
3718 END IF;
3719 END IF;
3720 -- We need to change the status of the contract
3721 OKL_CONTRACT_STATUS_PUB.update_contract_status(
3722 p_api_version => p_api_version,
3723 p_init_msg_list => p_init_msg_list,
3724 x_return_status => x_return_status,
3725 x_msg_count => x_msg_count,
3726 x_msg_data => x_msg_data,
3727 p_khr_status => 'BOOKED',
3728 p_chr_id => p_chr_id);
3729 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3730 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3731 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3732 RAISE OKL_API.G_EXCEPTION_ERROR;
3733 END IF;
3734
3735 IF (G_DEBUG_SPLIT) THEN
3736 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'OKL_CONTRACT_STATUS_PUB.update_contract_status
3737 procedure finished with status ' || x_return_status || ' in
3738 split_contract_after_yield procedure');
3739 END IF;
3740
3741 -- We need to change the status of the Lines for the contract
3742 OKL_CONTRACT_STATUS_PUB.cascade_lease_status(
3743 p_api_version => p_api_version,
3744 p_init_msg_list => p_init_msg_list,
3745 x_return_status => x_return_status,
3746 x_msg_count => x_msg_count,
3747 x_msg_data => x_msg_data,
3748 p_chr_id => p_chr_id);
3749 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3750 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3751 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3752 RAISE OKL_API.G_EXCEPTION_ERROR;
3753 END IF;
3754 -- To get status of splited transaction Chr id
3755 FOR r_check_ctrct_status IN check_ctrct_status(p_chr_id => ln_orig_system_id1) LOOP
3756 lt_chr_sts_tbl(i).chr_id := r_check_ctrct_status.chr_id;
3757 lt_chr_sts_tbl(i).sts_code := r_check_ctrct_status.sts_code;
3758 l_tcnv_rec.id := r_check_ctrct_status.trx_id;
3759
3760 IF check_ctrct_status%NOTFOUND THEN
3761 OKL_API.set_message(p_app_name => G_APP_NAME,
3762 p_msg_name => G_NO_MATCHING_RECORD,
3763 p_token1 => G_COL_NAME_TOKEN,
3764 p_token1_value => 'OKL_TRX_CONTRACTS.ID');
3765 x_return_status := OKL_API.G_RET_STS_ERROR;
3766 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
3767 END IF;
3768 i := i + 1;
3769 END LOOP;
3770 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3771 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3772 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3773 RAISE OKL_API.G_EXCEPTION_ERROR;
3774 END IF;
3775 IF lt_chr_sts_tbl.COUNT = 2 THEN
3776 j := lt_chr_sts_tbl.FIRST;
3777
3778 IF lt_chr_sts_tbl(j+1).sts_code = 'BOOKED' AND
3779 lt_chr_sts_tbl(j).sts_code = 'BOOKED' THEN
3780 lv_ok_to_terminate_orig_K := 'Y';
3781 END IF;
3782 ELSE
3783 OKL_API.set_message(p_app_name => G_APP_NAME,
3784 p_msg_name => G_CNT_REC);
3785 RAISE OKL_API.G_EXCEPTION_ERROR;
3786 END IF;
3787
3788 IF lv_ok_to_terminate_orig_K = 'Y' THEN
3789 -- Now since both the contract booked we can safely update the
3790 -- Transaction to PROCESSED
3791 l_tcnv_rec.tsu_code := 'PROCESSED';
3792 Okl_Trx_Contracts_Pub.update_trx_contracts(
3793 p_api_version => p_api_version,
3794 p_init_msg_list => p_init_msg_list,
3795 x_return_status => x_return_status,
3796 x_msg_count => x_msg_count,
3797 x_msg_data => x_msg_data,
3798 p_tcnv_rec => l_tcnv_rec,
3799 x_tcnv_rec => lx_tcnv_rec);
3800 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3801 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3802 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3803 RAISE OKL_API.G_EXCEPTION_ERROR;
3804 END IF;
3805
3806 /**
3807 * sjalasut, added API call to process subsidy pool transactions
3808 * before the contract is ammended. this api will reverse the transactions
3809 * on the old contract and add transactions from the split copies of the
3810 * contract. No change in the pool balance is expected. START.
3811 */
3812
3813 IF (G_DEBUG_SPLIT) THEN
3814 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Start Processing Subsidy Pool Transactions '|| x_return_status || ' in
3815 split_contract_after_yield procedure');
3816 IF (x_return_status <> 'S') THEN
3817 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
3818 END IF;
3819 END IF;
3820
3821 okl_subsidy_pool_auth_trx_pvt.create_pool_trx_khr_split(p_api_version => p_api_version
3822 ,p_init_msg_list => p_init_msg_list
3823 ,x_return_status => x_return_status
3824 ,x_msg_count => x_msg_count
3825 ,x_msg_data => x_msg_data
3826 ,p_new1_chr_id => lt_chr_sts_tbl(j).chr_id
3827 ,p_new2_chr_id => lt_chr_sts_tbl(j+1).chr_id
3828 );
3829 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3830 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3831 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3832 RAISE OKL_API.G_EXCEPTION_ERROR;
3833 END IF;
3834
3835 IF (G_DEBUG_SPLIT) THEN
3836 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Completed Processing Subsidy Pool Transactions '|| x_return_status || ' in
3837 split_contract_after_yield procedure');
3838 IF (x_return_status <> 'S') THEN
3839 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
3840 END IF;
3841 END IF;
3842
3843 /**
3844 * sjalasut, added API call to process subsidy pool transactions. END
3845 */
3846
3847 -- We need to change the status of the contract
3848 OKL_CONTRACT_STATUS_PUB.update_contract_status(
3849 p_api_version => p_api_version,
3850 p_init_msg_list => p_init_msg_list,
3851 x_return_status => x_return_status,
3852 x_msg_count => x_msg_count,
3853 x_msg_data => x_msg_data,
3854 p_khr_status => 'AMENDED',
3855 p_chr_id => ln_orig_system_id1);
3856 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3857 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3858 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3859 RAISE OKL_API.G_EXCEPTION_ERROR;
3860 END IF;
3861 -- We need to change the status of the Lines for the contract
3862 OKL_CONTRACT_STATUS_PUB.cascade_lease_status(
3863 p_api_version => p_api_version,
3864 p_init_msg_list => p_init_msg_list,
3865 x_return_status => x_return_status,
3866 x_msg_count => x_msg_count,
3867 x_msg_data => x_msg_data,
3868 p_chr_id => ln_orig_system_id1);
3869 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3870 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3871 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3872 RAISE OKL_API.G_EXCEPTION_ERROR;
3873 END IF;
3874 -- We can update the old servic contract also
3875 OPEN get_old_service_id(p_chr_id =>ln_orig_system_id1);
3876 FETCH get_old_service_id INTO ln_service_id;
3877 CLOSE get_old_service_id;
3878 IF ln_service_id IS NOT NULL OR
3879 ln_service_id <> OKL_API.G_MISS_NUM THEN
3880 -- We need to change the status of the contract
3881 OKL_CONTRACT_STATUS_PUB.update_contract_status(
3882 p_api_version => p_api_version,
3883 p_init_msg_list => p_init_msg_list,
3884 x_return_status => x_return_status,
3885 x_msg_count => x_msg_count,
3886 x_msg_data => x_msg_data,
3887 p_khr_status => 'AMENDED',
3888 p_chr_id => ln_service_id);
3889 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3890 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3891 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3892 RAISE OKL_API.G_EXCEPTION_ERROR;
3893 END IF;
3894 -- We need to change the status of the Lines for the contract
3895 OKL_CONTRACT_STATUS_PUB.cascade_lease_status(
3896 p_api_version => p_api_version,
3897 p_init_msg_list => p_init_msg_list,
3898 x_return_status => x_return_status,
3899 x_msg_count => x_msg_count,
3900 x_msg_data => x_msg_data,
3901 p_chr_id => ln_service_id);
3902 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3903 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3904 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3905 RAISE OKL_API.G_EXCEPTION_ERROR;
3906 END IF;
3907 END IF;
3908
3909 -- rravikir added for Bug 2927173, 2901442
3910 -- Reverse Loss provision transactions for the 'AMENDED' contract
3911
3912 -- Get Contract number and Split contract date
3913 OPEN get_split_info(p_chr_id =>ln_orig_system_id1);
3914 FETCH get_split_info INTO ln_orig_contract_id, lv_contract_number,
3915 ld_contract_start_date, ld_split_date;
3916 CLOSE get_split_info;
3917
3918 lprv_rec.cntrct_num := lv_contract_number;
3919 lprv_rec.reversal_date := ld_split_date;
3920 lprv_rec.reversal_type := NULL;
3921
3922 OKL_REV_LOSS_PROV_PUB.reverse_loss_provisions(
3923 p_api_version => p_api_version,
3924 p_init_msg_list => p_init_msg_list,
3925 x_msg_count => x_msg_count,
3926 x_msg_data => x_msg_data,
3927 x_return_status => x_return_status,
3928 p_lprv_rec => lprv_rec);
3929
3930 IF (G_DEBUG_SPLIT) THEN
3931 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Completed Reversal Loss provisions
3932 for the original Contract with Status ' || x_return_status || ' in
3933 split_contract_after_yield procedure');
3934 IF (x_return_status <> 'S') THEN
3935 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
3936 END IF;
3937 END IF;
3938
3939 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3940 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3941 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3942 RAISE OKL_API.G_EXCEPTION_ERROR;
3943 END IF;
3944
3945 -- rravikir added for Bug 3487162
3946 -- Get valid open period date by calling accounting util with split
3947 -- contract transaction date.
3948 l_gl_date := OKL_ACCOUNTING_UTIL.get_valid_gl_date(p_gl_date => ld_split_date);
3949 -- end for Bug 3487162
3950
3951 -- Reverse Accrual Accounting for the 'AMENDED' contract
3952 OKL_GENERATE_ACCRUALS_PUB.reverse_all_accruals(
3953 p_api_version => p_api_version,
3954 p_init_msg_list => p_init_msg_list,
3955 p_khr_id => ln_orig_contract_id,
3956 p_reverse_date => l_gl_date,
3957 p_description => 'Call from Split Contract API',
3958 x_return_status => x_return_status,
3959 x_msg_count => x_msg_count,
3960 x_msg_data => x_msg_data);
3961
3962 IF (G_DEBUG_SPLIT) THEN
3963 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Completed Reversal Accruals
3964 for the original Contract with Status ' || x_return_status || ' in
3965 split_contract_after_yield procedure');
3966 IF (x_return_status <> 'S') THEN
3967 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
3968 END IF;
3969 END IF;
3970
3971 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3972 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3973 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3974 RAISE OKL_API.G_EXCEPTION_ERROR;
3975 END IF;
3976 -- end for Bug 2927173, 2901442
3977
3978 END IF;
3979 END IF;
3980
3981 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3982 x_msg_data => x_msg_data);
3983
3984 EXCEPTION
3985 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3986 IF check_split_k_csr%ISOPEN THEN
3987 CLOSE check_split_k_csr;
3988 END IF;
3989 IF get_orig_sys_id1%ISOPEN THEN
3990 CLOSE get_orig_sys_id1;
3991 END IF;
3992 IF check_ctrct_status%ISOPEN THEN
3993 CLOSE check_ctrct_status;
3994 END IF;
3995 IF get_orig_fa%ISOPEN THEN
3996 CLOSE get_orig_fa;
3997 END IF;
3998 IF get_orig_ib%ISOPEN THEN
3999 CLOSE get_orig_ib;
4000 END IF;
4001 IF get_item_info%ISOPEN THEN
4002 CLOSE get_item_info;
4003 END IF;
4004 IF get_old_service_id%ISOPEN THEN
4005 CLOSE get_old_service_id;
4006 END IF;
4007 IF check_other_line%ISOPEN THEN
4008 CLOSE check_other_line;
4009 END IF;
4010 IF get_split_info%ISOPEN THEN
4011 CLOSE get_split_info;
4012 END IF;
4013 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4014 p_api_name => l_api_name,
4015 p_pkg_name => G_PKG_NAME,
4016 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4017 x_msg_count => x_msg_count,
4018 x_msg_data => x_msg_data,
4019 p_api_type => G_API_TYPE);
4020
4021 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4022 IF check_split_k_csr%ISOPEN THEN
4023 CLOSE check_split_k_csr;
4024 END IF;
4025 IF get_orig_sys_id1%ISOPEN THEN
4026 CLOSE get_orig_sys_id1;
4027 END IF;
4028 IF get_old_service_id%ISOPEN THEN
4029 CLOSE get_old_service_id;
4030 END IF;
4031 IF check_ctrct_status%ISOPEN THEN
4032 CLOSE check_ctrct_status;
4033 END IF;
4034 IF get_orig_fa%ISOPEN THEN
4035 CLOSE get_orig_fa;
4036 END IF;
4037 IF get_orig_ib%ISOPEN THEN
4038 CLOSE get_orig_ib;
4039 END IF;
4040 IF get_item_info%ISOPEN THEN
4041 CLOSE get_item_info;
4042 END IF;
4043 IF check_other_line%ISOPEN THEN
4044 CLOSE check_other_line;
4045 END IF;
4046 IF get_split_info%ISOPEN THEN
4047 CLOSE get_split_info;
4048 END IF;
4049 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4050 p_api_name => l_api_name,
4051 p_pkg_name => G_PKG_NAME,
4052 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4053 x_msg_count => x_msg_count,
4054 x_msg_data => x_msg_data,
4055 p_api_type => G_API_TYPE);
4056
4057 WHEN OTHERS THEN
4058 IF check_split_k_csr%ISOPEN THEN
4059 CLOSE check_split_k_csr;
4060 END IF;
4061 IF get_orig_sys_id1%ISOPEN THEN
4062 CLOSE get_orig_sys_id1;
4063 END IF;
4064 IF check_ctrct_status%ISOPEN THEN
4065 CLOSE check_ctrct_status;
4066 END IF;
4067 IF get_old_service_id%ISOPEN THEN
4068 CLOSE get_old_service_id;
4069 END IF;
4070 IF get_orig_fa%ISOPEN THEN
4071 CLOSE get_orig_fa;
4072 END IF;
4073 IF get_orig_ib%ISOPEN THEN
4074 CLOSE get_orig_ib;
4075 END IF;
4076 IF check_other_line%ISOPEN THEN
4077 CLOSE check_other_line;
4078 END IF;
4079 IF get_item_info%ISOPEN THEN
4080 CLOSE get_item_info;
4081 END IF;
4082 IF get_split_info%ISOPEN THEN
4083 CLOSE get_split_info;
4084 END IF;
4085 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4086 p_api_name => l_api_name,
4087 p_pkg_name => G_PKG_NAME,
4088 p_exc_name => 'OTHERS',
4089 x_msg_count => x_msg_count,
4090 x_msg_data => x_msg_data,
4091 p_api_type => G_API_TYPE);
4092 END split_contract_after_yield;
4093
4094 -----------------------------------------------------------------------------------------------
4095 ---------------------------- Main Process for split of Contract -------------------------------
4096 -----------------------------------------------------------------------------------------------
4097 PROCEDURE create_split_contract(
4098 p_api_version IN NUMBER,
4099 p_init_msg_list IN VARCHAR2,
4100 x_return_status OUT NOCOPY VARCHAR2,
4101 x_msg_count OUT NOCOPY NUMBER,
4102 x_msg_data OUT NOCOPY VARCHAR2,
4103 p_old_contract_number IN OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE,
4104 p_new_khr_top_line IN ktl_tbl_type,
4105 x_new_khr_top_line OUT NOCOPY ktl_tbl_type)
4106 IS
4107 l_api_name CONSTANT VARCHAR2(30) := 'OKL_SPLIT_CONTRACT_PVT';
4108 lr_ktl_rec ktl_rec_type;
4109 lrx_ktl_rec ktl_rec_type;
4110 lt_ktl_tbl ktl_tbl_type;
4111 ltx_ktl_tbl ktl_tbl_type;
4112 i NUMBER := 0;
4113 l_pre_line NUMBER := -1;
4114 ln_old_top_line_cnt NUMBER := 0;
4115 ln_old_chr_id OKC_K_HEADERS_V.ID%TYPE := 0;
4116 lx_new_header_id OKC_K_HEADERS_V.ID%TYPE := 0;
4117
4118 l_chrv_rec chrv_rec_type;
4119 l_khrv_rec khrv_rec_type;
4120 lx_chrv_rec chrv_rec_type;
4121 lx_khrv_rec khrv_rec_type;
4122
4123 CURSOR c_old_header_id(p_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE) IS
4124 SELECT id
4125 FROM OKC_K_HEADERS_V
4126 WHERE contract_number = p_contract_number;
4127
4128 CURSOR get_new_contract_number(p_header_id OKC_K_HEADERS_V.ID%TYPE) IS
4129 SELECT contract_number
4130 FROM OKC_K_HEADERS_V
4131 WHERE id = p_header_id;
4132
4133 CURSOR c_old_top_line_cnt(p_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE) IS
4134 SELECT COUNT(cle.id)
4135 FROM OKC_SUBCLASS_TOP_LINE stl,
4136 OKC_LINE_STYLES_V lse,
4137 OKC_K_LINES_V cle,
4138 OKC_K_HEADERS_V chrv
4139 WHERE chrv.contract_number = p_contract_number
4140 AND chrv.id = cle.dnz_chr_id
4141 AND cle.cle_id IS NULL
4142 AND cle.chr_id = chrv.id
4143 AND cle.lse_id = lse.id
4144 AND lse.lty_code = G_FIN_LINE_LTY_CODE
4145 AND lse.lse_type = G_TLS_TYPE
4146 AND lse.lse_parent_id IS NULL
4147 AND lse.id = stl.lse_id
4148 AND stl.scs_code IN (G_LEASE_SCS_CODE,G_LOAN_SCS_CODE);
4149
4150 BEGIN
4151 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4152 -- Call start_activity to create savepoint, check compatibility
4153 -- and initialize message list
4154 x_return_status := OKL_API.START_ACTIVITY (
4155 l_api_name
4156 ,p_init_msg_list
4157 ,'_PVT'
4158 ,x_return_status);
4159 -- Check if activity started successfully
4160 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4161 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4162 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4163 RAISE OKL_API.G_EXCEPTION_ERROR;
4164 END IF;
4165 -- Count of the top for the old contract
4166 -- Should match the imput parameter p_new_khr_top_line.COUNT
4167 OPEN c_old_top_line_cnt(p_contract_number => p_old_contract_number);
4168 IF c_old_top_line_cnt%NOTFOUND THEN
4169 OKL_API.set_message(p_app_name => G_APP_NAME,
4170 p_msg_name => 'OKL_LLA_CHR_ID');
4171 RAISE OKL_API.G_EXCEPTION_ERROR;
4172 END IF;
4173 FETCH c_old_top_line_cnt INTO ln_old_top_line_cnt;
4174 CLOSE c_old_top_line_cnt;
4175 IF ln_old_top_line_cnt = 0 THEN
4176 OKL_API.set_message(p_app_name => G_APP_NAME,
4177 p_msg_name => 'OKL_LLA_CHR_ID');
4178 RAISE OKL_API.G_EXCEPTION_ERROR;
4179 END IF;
4180 IF ln_old_top_line_cnt <> p_new_khr_top_line.COUNT THEN
4181 OKL_API.set_message(p_app_name => G_APP_NAME,
4182 p_msg_name => 'OKL_LLA_LINE_RECORD');
4183 RAISE OKL_API.G_EXCEPTION_ERROR;
4184 END IF;
4185 -- to get the old contract Header id
4186 OPEN c_old_header_id(p_contract_number => p_old_contract_number);
4187 IF c_old_header_id%NOTFOUND THEN
4188 OKL_API.set_message(p_app_name => G_APP_NAME,
4189 p_msg_name => 'OKL_LLA_CHR_ID');
4190 RAISE OKL_API.G_EXCEPTION_ERROR;
4191 END IF;
4192 FETCH c_old_header_id INTO ln_old_chr_id;
4193 CLOSE c_old_header_id;
4194 lt_ktl_tbl := p_new_khr_top_line;
4195 IF lt_ktl_tbl.COUNT > 0 THEN
4196 i := lt_ktl_tbl.FIRST;
4197 LOOP
4198 IF lt_ktl_tbl(i).line_number <> l_pre_line THEN
4199 -- Validate the top line for the Old Contract
4200 validate_chr_cle_id(p_dnz_chr_id => ln_old_chr_id,
4201 p_top_line_id => lt_ktl_tbl(i).kle_id,
4202 x_return_status => x_return_status);
4203 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4204 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
4205 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4206 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
4207 END IF;
4208 l_copy_contract_header(p_api_version => p_api_version,
4209 p_init_msg_list => p_init_msg_list,
4210 x_return_status => x_return_status,
4211 x_msg_count => x_msg_count,
4212 x_msg_data => x_msg_data,
4213 p_old_chr_id => ln_old_chr_id,
4214 p_new_contract_number => lt_ktl_tbl(i).contract_number,
4215 x_new_header_id => lx_new_header_id);
4216 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4217 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
4218 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4219 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
4220 END IF;
4221 -- To Change the orig_system_source_code to OKL_SPLIT
4222 l_chrv_rec.id := lx_new_header_id;
4223 l_khrv_rec.id := lx_new_header_id;
4224 l_update_contract_header(p_api_version => p_api_version,
4225 p_init_msg_list => p_init_msg_list,
4226 x_return_status => x_return_status,
4227 x_msg_count => x_msg_count,
4228 x_msg_data => x_msg_data,
4229 p_chrv_rec => l_chrv_rec,
4230 p_khrv_rec => l_khrv_rec,
4231 x_chrv_rec => lx_chrv_rec,
4232 x_khrv_rec => lx_khrv_rec);
4233 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4234 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
4235 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4236 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
4237 END IF;
4238 l_copy_contract_line(p_api_version => p_api_version,
4239 p_init_msg_list => p_init_msg_list,
4240 x_return_status => x_return_status,
4241 x_msg_count => x_msg_count,
4242 x_msg_data => x_msg_data,
4243 p_old_k_top_line => lt_ktl_tbl(i).kle_id,
4244 p_new_header_id => lx_new_header_id,
4245 x_new_k_top_id => x_new_khr_top_line(i).kle_id);
4246 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4247 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
4248 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4249 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
4250 END IF;
4251 -- To build the output information
4252 OPEN get_new_contract_number(lx_new_header_id);
4253 IF get_new_contract_number%NOTFOUND THEN
4254 OKL_API.set_message(p_app_name => G_APP_NAME,
4255 p_msg_name => G_NO_MATCHING_RECORD,
4256 p_token1 => G_COL_NAME_TOKEN,
4257 p_token1_value => 'New contract header id');
4258 x_return_status := OKL_API.G_RET_STS_ERROR;
4259 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
4260 END IF;
4261 FETCH get_new_contract_number INTO x_new_khr_top_line(i).contract_number;
4262 CLOSE get_new_contract_number;
4263 x_new_khr_top_line(i).line_number := lt_ktl_tbl(i).line_number;
4264 ELSIF lt_ktl_tbl(i).line_number = l_pre_line THEN
4265 -- Validate the top line for the Old Contract
4266 validate_chr_cle_id(p_dnz_chr_id => ln_old_chr_id,
4267 p_top_line_id => lt_ktl_tbl(i).kle_id,
4268 x_return_status => x_return_status);
4269 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4270 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
4271 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4272 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
4273 END IF;
4274 l_copy_contract_line(p_api_version => p_api_version,
4275 p_init_msg_list => p_init_msg_list,
4276 x_return_status => x_return_status,
4277 x_msg_count => x_msg_count,
4278 x_msg_data => x_msg_data,
4279 p_old_k_top_line => lt_ktl_tbl(i).kle_id,
4280 p_new_header_id => lx_new_header_id,
4281 x_new_k_top_id => x_new_khr_top_line(i).kle_id);
4282 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4283 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
4284 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4285 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
4286 END IF;
4287 -- To build the output information
4288 OPEN get_new_contract_number(lx_new_header_id);
4289 IF get_new_contract_number%NOTFOUND THEN
4290 OKL_API.set_message(p_app_name => G_APP_NAME,
4291 p_msg_name => G_NO_MATCHING_RECORD,
4292 p_token1 => G_COL_NAME_TOKEN,
4293 p_token1_value => 'New contract header id');
4294 x_return_status := OKL_API.G_RET_STS_ERROR;
4295 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
4296 END IF;
4297 FETCH get_new_contract_number INTO x_new_khr_top_line(i).contract_number;
4298 CLOSE get_new_contract_number;
4299 x_new_khr_top_line(i).line_number := lt_ktl_tbl(i).line_number;
4300 ELSE
4301 OKL_API.set_message(p_app_name => G_APP_NAME,
4302 p_msg_name => G_INVALID_CRITERIA,
4303 p_token1 => G_COL_NAME_TOKEN,
4304 p_token1_value => 'Line Number of KTL_TBL_TYPE');
4305 x_return_status := OKL_API.G_RET_STS_ERROR;
4306 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
4307 END IF;
4308 l_pre_line := lt_ktl_tbl(i).line_number;
4309 EXIT WHEN (i = lt_ktl_tbl.LAST);
4310 i := lt_ktl_tbl.NEXT(i);
4311 END LOOP;
4312 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4313 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4314 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4315 RAISE OKL_API.G_EXCEPTION_ERROR;
4316 END IF;
4317 END IF;
4318 OKL_API.END_ACTIVITY (x_msg_count,
4319 x_msg_data );
4320 EXCEPTION
4321 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4322 IF c_old_header_id%ISOPEN THEN
4323 CLOSE c_old_header_id;
4324 END IF;
4325 IF get_new_contract_number%ISOPEN THEN
4326 CLOSE get_new_contract_number;
4327 END IF;
4328 IF c_old_top_line_cnt%ISOPEN THEN
4329 CLOSE c_old_top_line_cnt;
4330 END IF;
4331 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4332 l_api_name,
4333 G_PKG_NAME,
4334 'OKL_API.G_RET_STS_ERROR',
4335 x_msg_count,
4336 x_msg_data,
4337 '_PVT');
4338 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4339 IF c_old_header_id%ISOPEN THEN
4340 CLOSE c_old_header_id;
4341 END IF;
4342 IF get_new_contract_number%ISOPEN THEN
4343 CLOSE get_new_contract_number;
4344 END IF;
4345 IF c_old_top_line_cnt%ISOPEN THEN
4346 CLOSE c_old_top_line_cnt;
4347 END IF;
4348 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4349 l_api_name,
4350 G_PKG_NAME,
4351 'OKL_API.G_RET_STS_UNEXP_ERROR',
4352 x_msg_count,
4353 x_msg_data,
4354 '_PVT');
4355 WHEN OTHERS THEN
4356 IF c_old_header_id%ISOPEN THEN
4357 CLOSE c_old_header_id;
4358 END IF;
4359 IF get_new_contract_number%ISOPEN THEN
4360 CLOSE get_new_contract_number;
4361 END IF;
4362 IF c_old_top_line_cnt%ISOPEN THEN
4363 CLOSE c_old_top_line_cnt;
4364 END IF;
4365 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4366 l_api_name,
4367 G_PKG_NAME,
4368 'OTHERS',
4369 x_msg_count,
4370 x_msg_data,
4371 '_PVT');
4372 END create_split_contract;
4373 -----------------------------------------------------------------------------------------------
4374 ------------------------- Set the context to Split process -----------------------------------
4375 -----------------------------------------------------------------------------------------------
4376 PROCEDURE set_context(
4377 p_api_version IN NUMBER,
4378 p_init_msg_list IN VARCHAR2,
4379 x_msg_count OUT NOCOPY NUMBER,
4380 x_msg_data OUT NOCOPY VARCHAR2,
4381 p_resp_id IN NUMBER,
4382 p_appl_id IN NUMBER,
4383 p_user_id IN NUMBER,
4384 x_return_status OUT NOCOPY VARCHAR2) IS
4385 l_api_name CONSTANT VARCHAR2(30) := 'set_context';
4386 BEGIN
4387 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4388 -- Call start_activity to create savepoint, check compatibility
4389 -- and initialize message list
4390 x_return_status := OKL_API.START_ACTIVITY (
4391 l_api_name
4392 ,p_init_msg_list
4393 ,'_PVT'
4394 ,x_return_status);
4395 -- Check if activity started successfully
4396 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4397 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4398 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4399 RAISE OKL_API.G_EXCEPTION_ERROR;
4400 END IF;
4401
4402 IF (p_resp_id = OKL_API.G_MISS_NUM OR
4403 p_resp_id IS NULL) THEN
4404 OKL_API.set_message(p_app_name => G_APP_NAME,
4405 p_msg_name => G_REQUIRED_VALUE,
4406 p_token1 => G_COL_NAME_TOKEN,
4407 p_token1_value => 'p_resp_id');
4408 RAISE G_EXCEPTION_STOP_VALIDATION;
4409 END IF;
4410 IF (p_appl_id = OKL_API.G_MISS_NUM OR
4411 p_appl_id IS NULL) THEN
4412 OKL_API.set_message(p_app_name => G_APP_NAME,
4413 p_msg_name => G_REQUIRED_VALUE,
4414 p_token1 => G_COL_NAME_TOKEN,
4415 p_token1_value => 'p_appl_id');
4416 RAISE G_EXCEPTION_STOP_VALIDATION;
4417 END IF;
4418 IF (p_user_id = OKL_API.G_MISS_NUM OR
4419 p_user_id IS NULL) THEN
4420 OKL_API.set_message(p_app_name => G_APP_NAME,
4421 p_msg_name => G_REQUIRED_VALUE,
4422 p_token1 => G_COL_NAME_TOKEN,
4423 p_token1_value => 'p_user_id');
4424 RAISE G_EXCEPTION_STOP_VALIDATION;
4425 END IF;
4426
4427 -- Set the context
4428 FND_GLOBAL.apps_initialize(user_id => p_user_id,
4429 resp_id => p_resp_id,
4430 resp_appl_id => p_appl_id);
4431
4432 OKL_API.END_ACTIVITY (x_msg_count,
4433 x_msg_data );
4434 EXCEPTION
4435 WHEN G_EXCEPTION_STOP_VALIDATION THEN
4436 -- We are here since the field is required
4437 -- Notify Error
4438 x_return_status := OKL_API.G_RET_STS_ERROR;
4439 WHEN OTHERS THEN
4440 -- store SQL error message on message stack
4441 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
4442 p_msg_name => G_UNEXPECTED_ERROR,
4443 p_token1 => G_SQLCODE_TOKEN,
4444 p_token1_value => SQLCODE,
4445 p_token2 => G_SQLERRM_TOKEN,
4446 p_token2_value => SQLERRM);
4447 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4448 END set_context;
4449 -----------------------------------------------------------------------------------------------
4450 ------------------------- Main Process for post split of Contract -----------------------------
4451 -----------------------------------------------------------------------------------------------
4452 PROCEDURE post_split_contract(
4453 p_api_version IN NUMBER,
4454 p_init_msg_list IN VARCHAR2,
4455 x_return_status OUT NOCOPY VARCHAR2,
4456 x_msg_count OUT NOCOPY NUMBER,
4457 x_msg_data OUT NOCOPY VARCHAR2,
4458 p_commit IN VARCHAR2,
4459 p_new1_contract_id IN OKC_K_HEADERS_V.ID%TYPE,
4460 p_new2_contract_id IN OKC_K_HEADERS_V.ID%TYPE,
4461 x_trx1_number OUT NOCOPY NUMBER,
4462 x_trx1_status OUT NOCOPY VARCHAR2,
4463 x_trx2_number OUT NOCOPY NUMBER,
4464 x_trx2_status OUT NOCOPY VARCHAR2)
4465 IS
4466 l_api_name CONSTANT VARCHAR2(30) := 'OKL_POST_SPLIT_CONTRACT';
4467 i NUMBER := 0;
4468 ln_qcl_id1 NUMBER;
4469 ln_qcl_id2 NUMBER;
4470 lt1_msg_tbl OKL_QA_CHECK_PUB.msg_tbl_type;
4471 lt2_msg_tbl OKL_QA_CHECK_PUB.msg_tbl_type;
4472 lv_severity OKC_QA_LIST_PROCESSES_V.SEVERITY%TYPE;
4473 lv1_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
4474 lv2_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
4475 lv_data VARCHAR2(2000);
4476 lv1_stream_id OKL_STREAM_INTERFACES.ID%TYPE;
4477 lv2_stream_id OKL_STREAM_INTERFACES.ID%TYPE;
4478 lv1_sis_code OKL_STREAM_INTERFACES.SIS_CODE%TYPE;
4479 lv2_sis_code OKL_STREAM_INTERFACES.SIS_CODE%TYPE;
4480 lv1_sts_code OKC_K_HEADERS_B.STS_CODE%TYPE;
4481 lv2_sts_code OKC_K_HEADERS_B.STS_CODE%TYPE;
4482 lv_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
4483 l_stream_path OKL_ST_GEN_TMPT_SETS.PRICING_ENGINE%TYPE;
4484
4485 -- Log Directory Path variable
4486 l_temp_dir VARCHAR2(1000);
4487
4488 -- To check if the stream generation of the contract went thru good.
4489 CURSOR c_ok_stream(p_khr_id OKL_STREAM_INTERFACES.KHR_ID%TYPE) IS
4490 SELECT a.id,
4491 a.sis_code,
4492 h.sts_code
4493 FROM okl_stream_interfaces a,
4494 okc_k_headers_b h
4495 WHERE a.khr_id = p_khr_id
4496 AND h.id = a.khr_id
4497 AND TRUNC(a.date_processed) IN (SELECT MAX(TRUNC(b.date_processed))
4498 FROM okl_stream_interfaces b
4499 WHERE b.khr_id = p_khr_id);
4500
4501 CURSOR get_dir IS
4502 SELECT NVL(SUBSTRB(TRANSLATE(LTRIM(value),',',' '), 1,
4503 INSTR(TRANSLATE(LTRIM(value),',',' '),' ') - 1),value)
4504 FROM v$parameter
4505 WHERE name = 'utl_file_dir';
4506
4507 CURSOR c_get_sts_code(p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
4508 SELECT sts_code
4509 FROM okc_k_headers_b
4510 WHERE id = p_khr_id;
4511
4512 CURSOR c_get_contract_number(p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
4513 SELECT contract_number
4514 FROM okc_k_headers_b
4515 WHERE id = (SELECT ORIG_SYSTEM_ID1
4516 FROM okc_k_headers_b
4517 WHERE id = p_khr_id);
4518
4519 CURSOR c_ok_stream_loop(p_khr_id OKL_STREAM_INTERFACES.KHR_ID%TYPE,
4520 p_stream_id OKL_STREAM_INTERFACES.ID%TYPE) IS
4521 SELECT a.sis_code,
4522 h.sts_code
4523 FROM okl_stream_interfaces a,
4524 okc_k_headers_b h
4525 WHERE a.id = p_stream_id
4526 AND a.khr_id = p_khr_id
4527 AND h.id = a.khr_id
4528 AND TRUNC(a.date_processed) IN (SELECT MAX(TRUNC(b.date_processed))
4529 FROM okl_stream_interfaces b
4530 WHERE b.khr_id = p_khr_id);
4531
4532 CURSOR c_get_source_id(p_khr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
4533 SELECT id
4534 FROM OKL_TXL_CNTRCT_LNS
4535 WHERE khr_id = p_khr_id;
4536
4537 CURSOR c_org_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
4538 SELECT authoring_org_id
4539 FROM okc_k_headers_b
4540 WHERE id = p_chr_id;
4541
4542 l_org_id okc_k_headers_b.authoring_org_id%TYPE;
4543
4544 /*
4545 -- mvasudev, 08/23/2004
4546 -- Added PROCEDURE to enable Business Event
4547 */
4548 PROCEDURE raise_business_event(
4549 x_return_status OUT NOCOPY VARCHAR2
4550 )
4551 IS
4552 -- Cursor to get the old chr id
4553 /*
4554 CURSOR l_old_chr_csr (p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
4555 SELECT chrb.orig_system_id1 old_chr_id
4556 ,trxb.date_transaction_occurred date_transaction_occurred
4557 FROM okc_k_headers_b chrb
4558 ,okl_trx_contracts trxb
4559 ,okl_trx_types_b tryv
4560 WHERE chrb.id = p_khr_id
4561 -- AND trxb.khr_id_old = chrb.orig_system_id1
4562 AND trxb.khr_id = chrb.id
4563 AND trxb.tsu_code = 'PROCESSED'
4564 AND trxb.try_id = tryv.id;
4565 */
4566
4567 CURSOR l_old_chr_csr (p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
4568 SELECT chrb.orig_system_id1 old_chr_id
4569 ,trxb2.date_transaction_occurred date_transaction_occurred
4570 FROM okc_k_headers_b chrb
4571 ,okl_trx_contracts trxb1
4572 ,okl_trx_types_b tryv
4573 ,okl_trx_contracts trxb2
4574 WHERE chrb.id = p_khr_id
4575 AND trxb1.khr_id = chrb.id
4576 AND trxb1.tsu_code = 'PROCESSED'
4577 --rkuttiya added for 12.1.1 MUlti GAAP
4578 AND trxb1.representation_type = 'PRIMARY'
4579 --
4580 AND trxb1.try_id = tryv.id
4581 AND trxb2.khr_id = chrb.orig_system_id1
4582 AND trxb2.TCN_TYPE = 'SPLC'
4583 AND trxb2.tsu_code = 'PROCESSED'
4584 AND trxb2.try_id = tryv.id;
4585
4586
4587 l_parameter_list wf_parameter_list_t;
4588 BEGIN
4589 FOR l_old_chr_rec IN l_old_chr_csr(p_new1_contract_id)
4590 LOOP
4591
4592 wf_event.AddParameterToList(G_WF_ITM_SRC_CONTRACT_ID,l_old_chr_rec.old_chr_id,l_parameter_list);
4593 wf_event.AddParameterToList(G_WF_ITM_REVISION_DATE,fnd_date.date_to_canonical(l_old_chr_rec.date_transaction_occurred),l_parameter_list);
4594 wf_event.AddParameterToList(G_WF_ITM_DEST_CONTRACT_ID_1,p_new1_contract_id,l_parameter_list);
4595 wf_event.AddParameterToList(G_WF_ITM_DEST_CONTRACT_ID_2,p_new2_contract_id,l_parameter_list);
4596
4597 OKL_WF_PVT.raise_event (p_api_version => p_api_version,
4598 p_init_msg_list => p_init_msg_list,
4599 x_return_status => x_return_status,
4600 x_msg_count => x_msg_count,
4601 x_msg_data => x_msg_data,
4602 p_event_name => G_WF_EVT_KHR_SPLIT_COMPLETED,
4603 p_parameters => l_parameter_list);
4604
4605
4606 END LOOP;
4607
4608 EXCEPTION
4609 WHEN OTHERS THEN
4610 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4611 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4612 END raise_business_event;
4613
4614
4615 /*
4616 -- mvasudev, 08/23/2004
4617 -- END, PROCEDURE to enable Business Event
4618 */
4619
4620
4621 BEGIN
4622 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4623 -- Call start_activity to create savepoint, check compatibility
4624 -- and initialize message list
4625 x_return_status := OKL_API.START_ACTIVITY (
4626 l_api_name
4627 ,p_init_msg_list
4628 ,'_PVT'
4629 ,x_return_status);
4630 -- Check if activity started successfully
4631 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4632 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4633 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4634 RAISE OKL_API.G_EXCEPTION_ERROR;
4635 END IF;
4636
4637 -- Get the output file directory to put log of Split Contract.
4638 /*IF (FND_PROFILE.VALUE('OKL_SPLIT_CONTRACT_DEBUG') = 'Y') THEN
4639 G_DEBUG_SPLIT := TRUE;
4640 END IF;*/
4641 -- Debugging Split Contract always set to True
4642 G_DEBUG_SPLIT := TRUE;
4643
4644 IF (G_DEBUG_SPLIT) THEN
4645 OPEN get_dir;
4646 FETCH get_dir INTO l_temp_dir;
4647 IF get_dir%NOTFOUND THEN
4648 NULL;
4649 END IF;
4650 CLOSE get_dir;
4651 END IF;
4652
4653 OPEN c_get_contract_number(p_khr_id => p_new1_contract_id);
4654 FETCH c_get_contract_number INTO lv_contract_number;
4655 CLOSE c_get_contract_number;
4656
4657 IF (G_DEBUG_SPLIT) THEN
4658 -- Setting file name and db writeable path for logging the process
4659 FND_FILE.PUT_NAMES('SPLIT_CONTRACT_'||lv_contract_number||'.log',
4660 'SPLIT_CONTRACT_'||lv_contract_number||'.out',
4661 l_temp_dir);
4662
4663 -- Split Contract process flow
4664 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '+--- Start Split Contract Process Flow ---+');
4665
4666 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Responsibilty ID ---> ' || FND_GLOBAL.resp_id);
4667 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Application ID ---> ' || FND_GLOBAL.resp_appl_id);
4668 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'User ID ---> ' || FND_GLOBAL.user_id);
4669
4670 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Oracle Lease Managment: Version :
4671 11.5.10 - Development');
4672 END IF;
4673
4674 -- Set Context
4675 OPEN c_org_csr(p_new1_contract_id);
4676 FETCH c_org_csr INTO l_org_id;
4677 CLOSE c_org_csr;
4678
4679 -- End
4680
4681 IF (G_DEBUG_SPLIT) THEN
4682 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Client Info Set for Contract-1 - ' || l_org_id);
4683 END IF;
4684
4685 -- Validate the Chr_id
4686 validate_chr_id(p_chr_id => p_new1_contract_id,
4687 x_contract_number => lv1_contract_number,
4688 x_return_status => x_return_status);
4689
4690 IF (G_DEBUG_SPLIT) THEN
4691 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'First Split Contract Validation Completed with ' || x_return_status);
4692 END IF;
4693
4694 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4695 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4696 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4697 RAISE OKL_API.G_EXCEPTION_ERROR;
4698 END IF;
4699 -- Validate the Chr_id
4700 validate_chr_id(p_chr_id => p_new2_contract_id,
4701 x_contract_number => lv2_contract_number,
4702 x_return_status => x_return_status);
4703
4704 IF (G_DEBUG_SPLIT) THEN
4705 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Second Split Contract Validation Completed with ' || x_return_status);
4706 END IF;
4707
4708 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4709 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4710 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4711 RAISE OKL_API.G_EXCEPTION_ERROR;
4712 END IF;
4713
4714 -- Process first split contract for fees and service lines having linked
4715 -- assets
4716 l_process_split_contract(p_api_version => p_api_version,
4717 p_init_msg_list => p_init_msg_list,
4718 x_return_status => x_return_status,
4719 x_msg_count => x_msg_count,
4720 x_msg_data => x_msg_data,
4721 p_contract_id => p_new1_contract_id);
4722
4723 IF (G_DEBUG_SPLIT) THEN
4724 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'l_process_split_contract procedure completed with '
4725 || x_return_status || ' for first Split contract');
4726 IF (x_return_status <> 'S') THEN
4727 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
4728 END IF;
4729 END IF;
4730
4731 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4732 OKL_API.set_message(p_app_name => G_APP_NAME,
4733 p_msg_name => G_ERROR_NAL_SPK);
4734 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4735 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4736 OKL_API.set_message(p_app_name => G_APP_NAME,
4737 p_msg_name => G_ERROR_NAL_SPK);
4738 RAISE OKL_API.G_EXCEPTION_ERROR;
4739 END IF;
4740
4741 -- Delete all the service and fee lines of second split contract not having
4742 -- linked asset(s) attached to them.
4743 l_delete_fee_service_lines(p_api_version => p_api_version,
4744 p_init_msg_list => p_init_msg_list,
4745 x_return_status => x_return_status,
4746 x_msg_count => x_msg_count,
4747 x_msg_data => x_msg_data,
4748 p_contract_id => p_new2_contract_id);
4749 IF (G_DEBUG_SPLIT) THEN
4750 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'l_delete_fee_service_lines procedure completed with '
4751 || x_return_status || ' for second Split contract');
4752 IF (x_return_status <> 'S') THEN
4753 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
4754 END IF;
4755 END IF;
4756
4757 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4758 OKL_API.set_message(p_app_name => G_APP_NAME,
4759 p_msg_name => G_ERROR_NAL_SPK);
4760 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4761 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4762 OKL_API.set_message(p_app_name => G_APP_NAME,
4763 p_msg_name => G_ERROR_NAL_SPK);
4764 RAISE OKL_API.G_EXCEPTION_ERROR;
4765 END IF;
4766
4767 -- Process second split contract for fees and service lines having linked
4768 -- assets
4769 l_process_split_contract(p_api_version => p_api_version,
4770 p_init_msg_list => p_init_msg_list,
4771 x_return_status => x_return_status,
4772 x_msg_count => x_msg_count,
4773 x_msg_data => x_msg_data,
4774 p_contract_id => p_new2_contract_id);
4775
4776 IF (G_DEBUG_SPLIT) THEN
4777 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'l_process_split_contract procedure completed with '
4778 || x_return_status || ' for second Split contract');
4779 IF (x_return_status <> 'S') THEN
4780 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
4781 END IF;
4782 END IF;
4783
4784 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4785 OKL_API.set_message(p_app_name => G_APP_NAME,
4786 p_msg_name => G_ERROR_NAL_SPK);
4787 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4788 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4789 OKL_API.set_message(p_app_name => G_APP_NAME,
4790 p_msg_name => G_ERROR_NAL_SPK);
4791 RAISE OKL_API.G_EXCEPTION_ERROR;
4792 END IF;
4793 -- To run the QA checker we need to get the QCL_ID for the first contract
4794 -- since we assume for now the split contract will be split into Two contracts
4795 -- only.If the source contract do not have QCL_ID(which is never the case)
4796 -- Then QCL_ID can be fetched from table okc_qa_check_lists_v using hard coded
4797 -- name as 'OKL LA QA CHECK LIST'.
4798 x_return_status := get_qcl_id(p_chr_id => p_new1_contract_id,
4799 x_qcl_id => ln_qcl_id1);
4800
4801 IF (G_DEBUG_SPLIT) THEN
4802 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'get_qcl_id procedure completed with '
4803 || x_return_status || ' for first Split contract');
4804 END IF;
4805
4806 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4807 OKL_API.set_message(p_app_name => G_APP_NAME,
4808 p_msg_name => G_NO_MATCHING_RECORD,
4809 p_token1 => G_COL_NAME_TOKEN,
4810 p_token1_value => 'qcl_id');
4811 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4812 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4813 OKL_API.set_message(p_app_name => G_APP_NAME,
4814 p_msg_name => G_NO_MATCHING_RECORD,
4815 p_token1 => G_COL_NAME_TOKEN,
4816 p_token1_value => 'qcl_id');
4817 RAISE OKL_API.G_EXCEPTION_ERROR;
4818 END IF;
4819 -- To run the QA checker we need to get the QCL_ID for the first Second
4820 -- since we assume for now the split contract will be split into Two contracts
4821 -- only.If the source contract do not have QCL_ID(which is never the case)
4822 -- Then QCL_ID can be fetched from table okc_qa_check_lists_v using hard coded
4823 -- name as 'OKL LA QA CHECK LIST'.
4824 x_return_status := get_qcl_id(p_chr_id => p_new2_contract_id,
4825 x_qcl_id => ln_qcl_id2);
4826
4827 IF (G_DEBUG_SPLIT) THEN
4828 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'get_qcl_id procedure completed with '
4829 || x_return_status || ' for second Split contract');
4830 END IF;
4831
4832 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4833 OKL_API.set_message(p_app_name => G_APP_NAME,
4834 p_msg_name => G_NO_MATCHING_RECORD,
4835 p_token1 => G_COL_NAME_TOKEN,
4836 p_token1_value => 'qcl_id');
4837 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4838 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4839 OKL_API.set_message(p_app_name => G_APP_NAME,
4840 p_msg_name => G_NO_MATCHING_RECORD,
4841 p_token1 => G_COL_NAME_TOKEN,
4842 p_token1_value => 'qcl_id');
4843 RAISE OKL_API.G_EXCEPTION_ERROR;
4844 END IF;
4845 -- Now we run the QA checker for the First Contract
4846 okl_contract_book_pub.execute_qa_check_list(p_api_version => p_api_version,
4847 p_init_msg_list => p_init_msg_list,
4848 x_return_status => x_return_status,
4849 x_msg_count => x_msg_count,
4850 x_msg_data => x_msg_data,
4851 p_qcl_id => ln_qcl_id1,
4852 p_chr_id => p_new1_contract_id,
4853 x_msg_tbl => lt1_msg_tbl);
4854
4855 IF (G_DEBUG_SPLIT) THEN
4856 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'okl_contract_book_pub.execute_qa_check_list procedure completed with '
4857 || x_return_status || ' for first Split contract');
4858 IF (x_return_status <> 'S') THEN
4859 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
4860 END IF;
4861 END IF;
4862
4863 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4864 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4865 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4866 RAISE OKL_API.G_EXCEPTION_ERROR;
4867 END IF;
4868 -- We need the handle the error so we run thru the message table to check if there
4869 -- is error severity out there.
4870 IF (lt1_msg_tbl.COUNT > 0) THEN
4871 i := lt1_msg_tbl.FIRST;
4872 LOOP
4873 lv_severity := lt1_msg_tbl(i).error_status;
4874 IF lv_severity = 'E' THEN
4875 OKL_API.set_message(p_app_name => G_APP_NAME,
4876 p_msg_name => G_ERROR_QA_CHECK);
4877 x_return_status := OKL_API.G_RET_STS_ERROR;
4878
4879 IF (G_DEBUG_SPLIT) THEN
4880 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Severity in QA processing
4881 for first Split contract ' || x_return_status);
4882 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error data : Name -> ' || lt1_msg_tbl(i).name);
4883 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error data : Description -> ' || lt1_msg_tbl(i).description);
4884 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error data : Package Name -> ' || lt1_msg_tbl(i).package_name);
4885 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error data : Procedure Name -> ' || lt1_msg_tbl(i).procedure_name);
4886 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error data : Error Status -> ' || lt1_msg_tbl(i).error_status);
4887 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error data : Error Severity -> ' || lt1_msg_tbl(i).severity);
4888 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error data : Data -> ' || lt1_msg_tbl(i).data);
4889 END IF;
4890
4891 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
4892 END IF;
4893 EXIT WHEN (i = lt1_msg_tbl.LAST);
4894 i := lt1_msg_tbl.NEXT(i);
4895 END LOOP;
4896 END IF;
4897
4898 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4899 -- halt validation as it has no parent record
4900 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4901 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4902 RAISE OKL_API.G_EXCEPTION_ERROR;
4903 END IF;
4904
4905 -- Now we are submitting the the first contract for stream Generation.
4906 OKL_LA_STREAM_PUB.GEN_INTR_EXTR_STREAM(p_api_version => p_api_version,
4907 p_init_msg_list => p_init_msg_list,
4908 x_return_status => x_return_status,
4909 x_msg_count => x_msg_count,
4910 x_msg_data => x_msg_data,
4911 p_khr_id => p_new1_contract_id,
4912 p_generation_ctx_code => 'AUTH',
4913 x_trx_number => x_trx1_number,
4914 x_trx_status => x_trx1_status);
4915
4916
4917 IF (G_DEBUG_SPLIT) THEN
4918 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'OKL_LA_STREAM_PUB.GEN_INTR_EXTR_STREAM procedure completed with '
4919 || x_return_status || ' for first Split contract');
4920 IF (x_return_status <> 'S') THEN
4921 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
4922 END IF;
4923 END IF;
4924
4925 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4926 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4927 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4928 RAISE OKL_API.G_EXCEPTION_ERROR;
4929 END IF;
4930 -- We need to commit this transaction also since we have to get the
4931 -- Stream generation Kicked off
4932 --l_stream_path := okl_streams_util.get_pricing_engine (p_khr_id => p_new1_contract_id);
4933 okl_streams_util.get_pricing_engine
4934 (p_khr_id => p_new1_contract_id,
4935 x_pricing_engine => l_stream_path,
4936 x_return_status => x_return_status
4937 );
4938 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4939 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4940 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4941 RAISE OKL_API.G_EXCEPTION_ERROR;
4942 END IF;
4943
4944 --IF (p_commit = OKL_API.G_TRUE AND (FND_PROFILE.VALUE('OKL_STREAMS_GEN_PATH') = 'EXTERNAL')) THEN
4945 IF (p_commit = OKL_API.G_TRUE AND (l_stream_path = 'EXTERNAL')) THEN
4946 COMMIT;
4947 END IF;
4948
4949 -- Need to make sure that the stream generation process has actually completed successfully
4950 -- for that first contract
4951 --IF (FND_PROFILE.VALUE('OKL_STREAMS_GEN_PATH') = 'EXTERNAL') THEN
4952 IF (l_stream_path = 'EXTERNAL') THEN
4953
4954 IF (G_DEBUG_SPLIT) THEN
4955 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'External Stream Generation in progress
4956 for first Split contract ..');
4957 END IF;
4958 FOR r_ok_stream IN c_ok_stream(p_khr_id => p_new1_contract_id) LOOP
4959 LOOP
4960 OPEN c_ok_stream_loop(p_khr_id => p_new1_contract_id,
4961 p_stream_id => r_ok_stream.id);
4962 FETCH c_ok_stream_loop INTO lv1_sis_code, lv1_sts_code;
4963 IF c_ok_stream_loop%NOTFOUND THEN
4964 OKL_API.set_message(p_app_name => G_APP_NAME,
4965 p_msg_name => G_NO_MATCHING_RECORD,
4966 p_token1 => G_COL_NAME_TOKEN,
4967 p_token1_value => 'OKL_STREAM_INTERFACES.SIS_CODE');
4968 x_return_status := OKL_API.G_RET_STS_ERROR;
4969
4970 IF (G_DEBUG_SPLIT) THEN
4971 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'External Stream Generation information missing
4972 for first Split contract ' || x_return_status);
4973 END IF;
4974
4975 EXIT WHEN(x_return_status = OKL_API.G_RET_STS_ERROR);
4976 END IF;
4977 CLOSE c_ok_stream_loop;
4978
4979 IF lv1_sis_code = 'PROCESS_COMPLETE' AND
4980 lv1_sts_code = 'COMPLETE' THEN
4981 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4982 EXIT WHEN(lv1_sis_code = 'PROCESS_COMPLETE');
4983 ELSIF lv1_sis_code NOT IN ('PROCESSING_REQUEST', 'PROCESS_COMPLETE', 'RET_DATA_RECEIVED') THEN
4984 OKL_API.set_message(p_app_name => G_APP_NAME,
4985 p_msg_name => G_ERROR_STR_GEN);
4986 x_return_status := OKL_API.G_RET_STS_ERROR;
4987 EXIT WHEN(x_return_status = OKL_API.G_RET_STS_ERROR);
4988 END IF;
4989 -- need below because of performance issue
4990 dbms_lock.sleep(5);
4991 END LOOP;
4992
4993 IF x_return_status = OKL_API.G_RET_STS_ERROR THEN
4994
4995 IF (G_DEBUG_SPLIT) THEN
4996 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'External Stream Generation completion status
4997 for first Split contract :' || x_return_status);
4998 END IF;
4999
5000 EXIT WHEN(x_return_status = OKL_API.G_RET_STS_ERROR);
5001 END IF;
5002 END LOOP;
5003 END IF;
5004
5005
5006 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5007 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5008 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5009 RAISE OKL_API.G_EXCEPTION_ERROR;
5010 END IF;
5011
5012 OPEN c_get_sts_code(p_khr_id => p_new1_contract_id);
5013 FETCH c_get_sts_code INTO lv1_sts_code;
5014 IF c_get_sts_code%NOTFOUND THEN
5015 OKL_API.set_message(p_app_name => G_APP_NAME,
5016 p_msg_name => G_NO_MATCHING_RECORD,
5017 p_token1 => G_COL_NAME_TOKEN,
5018 p_token1_value => 'OKC_K_HEADERS_B.STS_CODE');
5019 x_return_status := OKL_API.G_RET_STS_ERROR;
5020 END IF;
5021 CLOSE c_get_sts_code;
5022
5023 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Post Stream generation Contract status for first
5024 Split contract : ' || lv1_sts_code);
5025
5026 IF lv1_sts_code <> 'COMPLETE' THEN
5027 OKL_API.set_message(p_app_name => G_APP_NAME,
5028 p_msg_name => G_ERROR_STR_GEN);
5029 x_return_status := OKL_API.G_RET_STS_ERROR;
5030 END IF;
5031 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5032 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5033 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5034 RAISE OKL_API.G_EXCEPTION_ERROR;
5035 END IF;
5036
5037 -- We are now doing post stream generation process for first contract
5038 -- Like the Journal entries and Booking of the contract
5039 split_contract_after_yield(p_api_version => p_api_version,
5040 p_init_msg_list => p_init_msg_list,
5041 x_return_status => x_return_status,
5042 x_msg_count => x_msg_count,
5043 x_msg_data => x_msg_data,
5044 p_chr_id => p_new1_contract_id);
5045
5046 IF (G_DEBUG_SPLIT) THEN
5047 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'split_contract_after_yield procedure
5048 completed for first contract with status ' || x_return_status);
5049 IF (x_return_status <> 'S') THEN
5050 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
5051 END IF;
5052 END IF;
5053
5054 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5055 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5056 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5057 RAISE OKL_API.G_EXCEPTION_ERROR;
5058 END IF;
5059
5060 -- Set Context
5061 OPEN c_org_csr(p_new2_contract_id);
5062 FETCH c_org_csr INTO l_org_id;
5063 CLOSE c_org_csr;
5064
5065 -- End
5066
5067 -- We need the handle the error so we run thru the message table to check if there
5068 -- is error severity out there.
5069 -- Now we run the QA checker for the second Contract
5070 okl_contract_book_pub.execute_qa_check_list(p_api_version => p_api_version,
5071 p_init_msg_list => p_init_msg_list,
5072 x_return_status => x_return_status,
5073 x_msg_count => x_msg_count,
5074 x_msg_data => x_msg_data,
5075 p_qcl_id => ln_qcl_id2,
5076 p_chr_id => p_new2_contract_id,
5077 x_msg_tbl => lt2_msg_tbl);
5078
5079
5080 IF (G_DEBUG_SPLIT) THEN
5081 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'okl_contract_book_pub.execute_qa_check_list procedure completed with '
5082 || x_return_status || ' for second Split contract');
5083 IF (x_return_status <> 'S') THEN
5084 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
5085 END IF;
5086 END IF;
5087
5088 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5089 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5090 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5091 RAISE OKL_API.G_EXCEPTION_ERROR;
5092 END IF;
5093
5094 IF (lt2_msg_tbl.COUNT > 0) THEN
5095 i := lt2_msg_tbl.FIRST;
5096 LOOP
5097 lv_severity := lt2_msg_tbl(i).error_status;
5098 IF lv_severity = 'E' THEN
5099 OKL_API.set_message(p_app_name => G_APP_NAME,
5100 p_msg_name => G_ERROR_QA_CHECK);
5101 x_return_status := OKL_API.G_RET_STS_ERROR;
5102
5103 IF (G_DEBUG_SPLIT) THEN
5104 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Severity in QA processing
5105 for second Split contract ' || x_return_status);
5106 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error data : Name -> ' || lt2_msg_tbl(i).name);
5107 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error data : Description -> ' || lt2_msg_tbl(i).description);
5108 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error data : Package Name -> ' || lt2_msg_tbl(i).package_name);
5109 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error data : Procedure Name -> ' || lt2_msg_tbl(i).procedure_name);
5110 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error data : Error Status -> ' || lt2_msg_tbl(i).error_status);
5111 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error data : Error Severity -> ' || lt2_msg_tbl(i).severity);
5112 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error data : Data -> ' || lt2_msg_tbl(i).data);
5113 END IF;
5114
5115 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
5116 END IF;
5117 EXIT WHEN (i = lt2_msg_tbl.LAST);
5118 i := lt2_msg_tbl.NEXT(i);
5119 END LOOP;
5120 END IF;
5121
5122 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5123 -- halt validation as it has no parent record
5124 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5125 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5126 RAISE OKL_API.G_EXCEPTION_ERROR;
5127 END IF;
5128
5129
5130 -- Now we are submitting the the second contract for stream Generation.
5131 OKL_LA_STREAM_PUB.GEN_INTR_EXTR_STREAM(p_api_version => p_api_version,
5132 p_init_msg_list => p_init_msg_list,
5133 x_return_status => x_return_status,
5134 x_msg_count => x_msg_count,
5135 x_msg_data => x_msg_data,
5136 p_khr_id => p_new2_contract_id,
5137 p_generation_ctx_code => 'AUTH',
5138 x_trx_number => x_trx2_number,
5139 x_trx_status => x_trx2_status);
5140
5141
5142 IF (G_DEBUG_SPLIT) THEN
5143 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'OKL_LA_STREAM_PUB.GEN_INTR_EXTR_STREAM procedure completed with '
5144 || x_return_status || ' for second Split contract');
5145 IF (x_return_status <> 'S') THEN
5146 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
5147 END IF;
5148 END IF;
5149
5150 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5151 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5152 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5153 RAISE OKL_API.G_EXCEPTION_ERROR;
5154 END IF;
5155 -- We are using the below since we have commit the workflow
5156 -- Of generating the Streams
5157 --l_stream_path := okl_streams_util.get_pricing_engine (p_khr_id => p_new2_contract_id);
5158 okl_streams_util.get_pricing_engine
5159 (p_khr_id => p_new2_contract_id,
5160 x_pricing_engine => l_stream_path,
5161 x_return_status => x_return_status
5162 );
5163 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5164 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5165 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5166 RAISE OKL_API.G_EXCEPTION_ERROR;
5167 END IF;
5168
5169 --IF (p_commit = OKL_API.G_TRUE AND (FND_PROFILE.VALUE('OKL_STREAMS_GEN_PATH') = 'EXTERNAL')) THEN
5170 IF (p_commit = OKL_API.G_TRUE AND (l_stream_path = 'EXTERNAL')) THEN
5171 COMMIT;
5172 END IF;
5173
5174 -- Need to make sure that the stream generation process has actually completed successfully
5175 -- for that second contract
5176 --IF (FND_PROFILE.VALUE('OKL_STREAMS_GEN_PATH') = 'EXTERNAL') THEN
5177 IF (l_stream_path = 'EXTERNAL') THEN
5178 IF (G_DEBUG_SPLIT) THEN
5179 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'External Stream Generation in progress
5180 for second Split contract ..');
5181 END IF;
5182 FOR r_ok_stream IN c_ok_stream(p_khr_id => p_new2_contract_id) LOOP
5183 LOOP
5184 OPEN c_ok_stream_loop(p_khr_id => p_new2_contract_id,
5185 p_stream_id => r_ok_stream.id);
5186 FETCH c_ok_stream_loop INTO lv2_sis_code, lv2_sts_code;
5187 IF c_ok_stream_loop%NOTFOUND THEN
5188 OKL_API.set_message(p_app_name => G_APP_NAME,
5189 p_msg_name => G_NO_MATCHING_RECORD,
5190 p_token1 => G_COL_NAME_TOKEN,
5191 p_token1_value => 'OKL_STREAM_INTERFACES.SIS_CODE');
5192 x_return_status := OKL_API.G_RET_STS_ERROR;
5193
5194 IF (G_DEBUG_SPLIT) THEN
5195 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'External Stream Generation information missing
5196 for second Split contract ' || x_return_status);
5197 END IF;
5198
5199 EXIT WHEN(x_return_status = OKL_API.G_RET_STS_ERROR);
5200 END IF;
5201 CLOSE c_ok_stream_loop;
5202
5203 IF lv2_sis_code = 'PROCESS_COMPLETE'AND
5204 lv2_sts_code = 'COMPLETE' THEN
5205 x_return_status := OKL_API.G_RET_STS_SUCCESS;
5206 EXIT WHEN(lv2_sis_code = 'PROCESS_COMPLETE');
5207 ELSIF lv2_sis_code NOT IN ('PROCESSING_REQUEST', 'PROCESS_COMPLETE', 'RET_DATA_RECEIVED') THEN
5208 OKL_API.set_message(p_app_name => G_APP_NAME,
5209 p_msg_name => G_ERROR_STR_GEN);
5210 x_return_status := OKL_API.G_RET_STS_ERROR;
5211 EXIT WHEN(x_return_status = OKL_API.G_RET_STS_ERROR);
5212 END IF;
5213 -- need below because of performance issue
5214 dbms_lock.sleep(5);
5215 END LOOP;
5216 IF x_return_status = OKL_API.G_RET_STS_ERROR THEN
5217
5218 IF (G_DEBUG_SPLIT) THEN
5219 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'External Stream Generation completion status
5220 for second Split contract :' || x_return_status);
5221 END IF;
5222
5223 EXIT WHEN(x_return_status = OKL_API.G_RET_STS_ERROR);
5224 END IF;
5225 END LOOP;
5226 END IF;
5227 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5228 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5229 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5230 RAISE OKL_API.G_EXCEPTION_ERROR;
5231 END IF;
5232
5233 OPEN c_get_sts_code(p_khr_id => p_new2_contract_id);
5234 FETCH c_get_sts_code INTO lv2_sts_code;
5235 IF c_get_sts_code%NOTFOUND THEN
5236 OKL_API.set_message(p_app_name => G_APP_NAME,
5237 p_msg_name => G_NO_MATCHING_RECORD,
5238 p_token1 => G_COL_NAME_TOKEN,
5239 p_token1_value => 'OKC_K_HEADERS_B.STS_CODE');
5240 x_return_status := OKL_API.G_RET_STS_ERROR;
5241 END IF;
5242 CLOSE c_get_sts_code;
5243
5244 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Post Stream generation Contract status for second
5245 Split contract : ' || lv2_sts_code);
5246
5247 IF lv2_sts_code <> 'COMPLETE' THEN
5248 OKL_API.set_message(p_app_name => G_APP_NAME,
5249 p_msg_name => G_ERROR_STR_GEN);
5250 x_return_status := OKL_API.G_RET_STS_ERROR;
5251 END IF;
5252 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5253 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5254 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5255 RAISE OKL_API.G_EXCEPTION_ERROR;
5256 END IF;
5257
5258 -- We are now doing post stream generation process for second contract
5259 -- Like the Journal entries and Booking of the contract
5260 split_contract_after_yield(p_api_version => p_api_version,
5261 p_init_msg_list => p_init_msg_list,
5262 x_return_status => x_return_status,
5263 x_msg_count => x_msg_count,
5264 x_msg_data => x_msg_data,
5265 p_chr_id => p_new2_contract_id);
5266
5267 IF (G_DEBUG_SPLIT) THEN
5268 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'split_contract_after_yield procedure
5269 completed for second contract with status ' || x_return_status);
5270 IF (x_return_status <> 'S') THEN
5271 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
5272 END IF;
5273 END IF;
5274
5275 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5276 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5277 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5278 RAISE OKL_API.G_EXCEPTION_ERROR;
5279 END IF;
5280 -- Now post the Journal Entries into GL for the first contract
5281 FOR r_get_source_id IN c_get_source_id(p_khr_id => p_new1_contract_id) LOOP
5282 OKL_ACCOUNT_DIST_PUB.UPDATE_POST_TO_GL(
5283 p_api_version => p_api_version,
5284 p_init_msg_list => p_init_msg_list,
5285 x_return_status => x_return_status,
5286 x_msg_count => x_msg_count,
5287 x_msg_data => x_msg_data,
5288 p_source_id => r_get_source_id.id,
5289 p_source_table => 'OKL_TXL_CNTRCT_LNS');
5290 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5291 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
5292 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5293 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
5294 END IF;
5295 END LOOP;
5296
5297 IF (G_DEBUG_SPLIT) THEN
5298 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'OKL_ACCOUNT_DIST_PUB.UPDATE_POST_TO_GL
5299 procedure completed for first contract with status ' || x_return_status);
5300 IF (x_return_status <> 'S') THEN
5301 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
5302 END IF;
5303 END IF;
5304
5305 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5306 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5307 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5308 RAISE OKL_API.G_EXCEPTION_ERROR;
5309 END IF;
5310 -- Now post the Journal Entries into GL for the second contract
5311 FOR r_get_source_id IN c_get_source_id(p_khr_id => p_new2_contract_id) LOOP
5312 OKL_ACCOUNT_DIST_PUB.UPDATE_POST_TO_GL(
5313 p_api_version => p_api_version,
5314 p_init_msg_list => p_init_msg_list,
5315 x_return_status => x_return_status,
5316 x_msg_count => x_msg_count,
5317 x_msg_data => x_msg_data,
5318 p_source_id => r_get_source_id.id,
5319 p_source_table => 'OKL_TXL_CNTRCT_LNS');
5320 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5321 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
5322 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5323 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
5324 END IF;
5325 END LOOP;
5326
5327 IF (G_DEBUG_SPLIT) THEN
5328 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'OKL_ACCOUNT_DIST_PUB.UPDATE_POST_TO_GL
5329 procedure completed for second contract with status ' || x_return_status);
5330 IF (x_return_status <> 'S') THEN
5331 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message : ' || x_msg_data);
5332 END IF;
5333 END IF;
5334
5335 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5336 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5337 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5338 RAISE OKL_API.G_EXCEPTION_ERROR;
5339 END IF;
5340
5341 /*
5342 -- mvasudev, 08/23/2004
5343 -- Code change to enable Business Event
5344 */
5345 raise_business_event(x_return_status => x_return_status);
5346 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5347 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5348 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5349 RAISE OKL_API.G_EXCEPTION_ERROR;
5350 END IF;
5351
5352 /*
5353 -- mvasudev, 08/23/2004
5354 -- END, Code change to enable Business Event
5355 */
5356 OKL_API.END_ACTIVITY (x_msg_count,
5357 x_msg_data );
5358
5359 IF (G_DEBUG_SPLIT) THEN
5360 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '+--- End Split Contract Process Flow ---+');
5361 END IF;
5362 EXCEPTION
5363 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5364 IF c_ok_stream%ISOPEN THEN
5365 CLOSE c_ok_stream;
5366 END IF;
5367 IF c_get_source_id%ISOPEN THEN
5368 CLOSE c_get_source_id;
5369 END IF;
5370 IF c_ok_stream_loop%ISOPEN THEN
5371 CLOSE c_ok_stream_loop;
5372 END IF;
5373 IF c_org_csr%ISOPEN THEN
5374 CLOSE c_org_csr;
5375 END IF;
5376 l_delete_contract_line(p_api_version => p_api_version,
5377 p_init_msg_list => p_init_msg_list,
5378 x_return_status => x_return_status,
5379 x_msg_count => x_msg_count,
5380 x_msg_data => x_msg_data,
5381 p_contract_id => p_new1_contract_id);
5382 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5383 OKL_API.set_message(p_app_name => G_APP_NAME,
5384 p_msg_name => G_ERROR_CLEAN_SPK);
5385 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5386 OKL_API.set_message(p_app_name => G_APP_NAME,
5387 p_msg_name => G_ERROR_CLEAN_SPK);
5388 END IF;
5389 l_delete_contract_line(p_api_version => p_api_version,
5390 p_init_msg_list => p_init_msg_list,
5391 x_return_status => x_return_status,
5392 x_msg_count => x_msg_count,
5393 x_msg_data => x_msg_data,
5394 p_contract_id => p_new2_contract_id);
5395 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5396 OKL_API.set_message(p_app_name => G_APP_NAME,
5397 p_msg_name => G_ERROR_CLEAN_SPK);
5398 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5399 OKL_API.set_message(p_app_name => G_APP_NAME,
5400 p_msg_name => G_ERROR_CLEAN_SPK);
5401 END IF;
5402 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
5403 l_api_name,
5404 G_PKG_NAME,
5405 'OKL_API.G_RET_STS_ERROR',
5406 x_msg_count,
5407 x_msg_data,
5408 '_PVT');
5409 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5410 IF c_ok_stream%ISOPEN THEN
5411 CLOSE c_ok_stream;
5412 END IF;
5413 IF c_get_sts_code%ISOPEN THEN
5414 CLOSE c_get_sts_code;
5415 END IF;
5416 IF c_get_source_id%ISOPEN THEN
5417 CLOSE c_get_source_id;
5418 END IF;
5419 IF c_ok_stream_loop%ISOPEN THEN
5420 CLOSE c_ok_stream_loop;
5421 END IF;
5422 IF c_org_csr%ISOPEN THEN
5423 CLOSE c_org_csr;
5424 END IF;
5425 l_delete_contract_line(p_api_version => p_api_version,
5426 p_init_msg_list => p_init_msg_list,
5427 x_return_status => x_return_status,
5428 x_msg_count => x_msg_count,
5429 x_msg_data => x_msg_data,
5430 p_contract_id => p_new1_contract_id);
5431 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5432 OKL_API.set_message(p_app_name => G_APP_NAME,
5433 p_msg_name => G_ERROR_CLEAN_SPK);
5434 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5435 OKL_API.set_message(p_app_name => G_APP_NAME,
5436 p_msg_name => G_ERROR_CLEAN_SPK);
5437 END IF;
5438 l_delete_contract_line(p_api_version => p_api_version,
5439 p_init_msg_list => p_init_msg_list,
5440 x_return_status => x_return_status,
5441 x_msg_count => x_msg_count,
5442 x_msg_data => x_msg_data,
5443 p_contract_id => p_new2_contract_id);
5444 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5445 OKL_API.set_message(p_app_name => G_APP_NAME,
5446 p_msg_name => G_ERROR_CLEAN_SPK);
5447 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5448 OKL_API.set_message(p_app_name => G_APP_NAME,
5449 p_msg_name => G_ERROR_CLEAN_SPK);
5450 END IF;
5451 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
5452 l_api_name,
5453 G_PKG_NAME,
5454 'OKL_API.G_RET_STS_UNEXP_ERROR',
5455 x_msg_count,
5456 x_msg_data,
5457 '_PVT');
5458 WHEN OTHERS THEN
5459 IF c_ok_stream%ISOPEN THEN
5460 CLOSE c_ok_stream;
5461 END IF;
5462 IF c_get_sts_code%ISOPEN THEN
5463 CLOSE c_get_sts_code;
5464 END IF;
5465 IF c_get_source_id%ISOPEN THEN
5466 CLOSE c_get_source_id;
5467 END IF;
5468 IF c_ok_stream_loop%ISOPEN THEN
5469 CLOSE c_ok_stream_loop;
5470 END IF;
5471 IF c_org_csr%ISOPEN THEN
5472 CLOSE c_org_csr;
5473 END IF;
5474 l_delete_contract_line(p_api_version => p_api_version,
5475 p_init_msg_list => p_init_msg_list,
5476 x_return_status => x_return_status,
5477 x_msg_count => x_msg_count,
5478 x_msg_data => x_msg_data,
5479 p_contract_id => p_new1_contract_id);
5480 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5481 OKL_API.set_message(p_app_name => G_APP_NAME,
5482 p_msg_name => G_ERROR_CLEAN_SPK);
5483 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5484 OKL_API.set_message(p_app_name => G_APP_NAME,
5485 p_msg_name => G_ERROR_CLEAN_SPK);
5486 END IF;
5487 l_delete_contract_line(p_api_version => p_api_version,
5488 p_init_msg_list => p_init_msg_list,
5489 x_return_status => x_return_status,
5490 x_msg_count => x_msg_count,
5491 x_msg_data => x_msg_data,
5492 p_contract_id => p_new2_contract_id);
5493 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5494 OKL_API.set_message(p_app_name => G_APP_NAME,
5495 p_msg_name => G_ERROR_CLEAN_SPK);
5496 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5497 OKL_API.set_message(p_app_name => G_APP_NAME,
5498 p_msg_name => G_ERROR_CLEAN_SPK);
5499 END IF;
5500 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
5501 l_api_name,
5502 G_PKG_NAME,
5503 'OTHERS',
5504 x_msg_count,
5505 x_msg_data,
5506 '_PVT');
5507 END post_split_contract;
5508 END OKL_SPLIT_CONTRACT_PVT;