[Home] [Help]
PACKAGE BODY: APPS.OKL_SEC_AGREEMENT_PVT
Source
1 PACKAGE BODY Okl_Sec_Agreement_Pvt AS
2 /* $Header: OKLRSZAB.pls 120.23.12020000.2 2012/08/07 13:42:06 rpillay ship $ */
3 ----------------------------------------------------------------------------
4 -- Global Message Constants
5 ----------------------------------------------------------------------------
6 -- see FND_NEW_MESSAGES for full message text
7 G_NOT_FOUND CONSTANT VARCHAR2(30) := 'OKC_NOT_FOUND'; -- message_name
8 G_NOT_FOUND_V1 CONSTANT VARCHAR2(30) := 'VALUE1'; -- token 1
9 G_NOT_FOUND_V2 CONSTANT VARCHAR2(30) := 'VALUE2'; -- token 2
10
11 G_NOT_UNIQUE CONSTANT VARCHAR2(30) := 'OKL_LLA_NOT_UNIQUE';
12 G_REQUIRED_VALUE CONSTANT VARCHAR2(30) := 'OKL_REQUIRED_VALUE';
13 G_COL_NAME_TOKEN CONSTANT VARCHAR2(30) := OKL_API.G_COL_NAME_TOKEN;
14 G_INVALID_VALUE CONSTANT VARCHAR2(30) := 'OKL_INVALID_VALUE';
15
16 G_LESSOR_RLE_CODE CONSTANT VARCHAR2(10) := 'LESSOR';
17 G_TRUSTEE_RLE_CODE CONSTANT VARCHAR2(10) := 'TRUSTEE';
18 G_STS_CODE_NEW CONSTANT VARCHAR2(10) := 'NEW';
19 G_STS_CODE_ACTIVE CONSTANT VARCHAR2(10) := 'ACTIVE';
20 G_STS_CODE_BOOKED CONSTANT VARCHAR2(10) := 'BOOKED';
21 G_SCS_CODE CONSTANT VARCHAR2(30) := 'INVESTOR';
22 G_POOL_TRX_ADD CONSTANT VARCHAR2(30) := 'ADD';
23 G_POOL_TRX_REASON_ACTIVE CONSTANT VARCHAR2(30) := 'ACTIVATION';
24 G_SECURITIZED_CODE_Y CONSTANT VARCHAR2(30) := 'Y';
25 G_SECURITIZED_CODE_N CONSTANT VARCHAR2(30) := 'N';
26 G_LESSOR_JTOT_OBJECT1_CODE CONSTANT VARCHAR2(30) := 'OKX_OPERUNIT';
27 G_TRUSTEE_JTOT_OBJECT1_CODE CONSTANT VARCHAR2(30) := 'OKX_VENDOR';
28
29 G_POC_STS_NEW CONSTANT VARCHAR2(3) := OKL_POOL_PVT.G_POC_STS_NEW;
30 G_POC_STS_ACTIVE CONSTANT VARCHAR2(6) := OKL_POOL_PVT.G_POC_STS_ACTIVE;
31 G_POC_STS_INACTIVE CONSTANT VARCHAR2(8) := OKL_POOL_PVT.G_POC_STS_INACTIVE;
32
33 G_FACTORING_SYND_FLAG_INVESTOR CONSTANT VARCHAR2(45) := 'INVESTOR';
34 G_OKL_SEC_ACCT_TRX_DESC CONSTANT VARCHAR2(45) := 'OKL_SEC_ACCT_TRX_DESC';
35 G_RULE_GRP_LASEAC CONSTANT VARCHAR2(45) := 'LASEAC';
36 G_RULE_LASEAC CONSTANT VARCHAR2(45) := 'LASEAC';
37 G_TRY_TYPE_INV CONSTANT VARCHAR2(30) := 'Investor';
38
39 -- sosharma added codes for tranaction_status
40 G_POOL_TRX_STATUS_COMPLETE CONSTANT VARCHAR2(30) := 'COMPLETE';
41 G_POOL_TRX_STATUS_PENDING CONSTANT VARCHAR2(30) := 'PENDING';
42 G_POOL_TRX_STATUS_INCOMPLETE CONSTANT VARCHAR2(30) := 'INCOMPLETE';
43
44 --added by kthiruva for bug 6691554
45 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
46 G_HALT_PROCESSING EXCEPTION;
47
48 -- ankushar Added constants to be used by the workflow
49 G_ADD_KHR_REQUEST_APPROVAL_WF CONSTANT VARCHAR2(2) := 'WF';
50 G_ADD_KHR_REQUEST_APPRV_AME CONSTANT VARCHAR2(3) := 'AME';
51 G_POOL_TRX_STATUS_SUBMITTED CONSTANT VARCHAR2(30) := 'SUBMITTED';
52 G_POOL_TRX_STATUS_APPROVED CONSTANT VARCHAR2(30) := 'APPROVED';
53 G_POOL_TRX_STATUS_NEW CONSTANT VARCHAR2(30) := 'NEW';
54 G_NO_MATCHING_RECORD CONSTANT VARCHAR2(200) := 'OKL_LLA_NO_MATCHING_RECORD';
55 G_POOL_TRX_STS_APPR_REJECTED CONSTANT VARCHAR2(30) := 'APPROVAL_REJECTED';
56 G_POOL_TRX_STATUS_PENDING_APPR CONSTANT VARCHAR2(30) := 'PENDING_APPROVAL';
57
58 ----------------------------------------------------------------------------
59 -- Data Structures
60 ----------------------------------------------------------------------------
61
62 SUBTYPE khrv_rec_type IS OKL_CONTRACT_PVT.khrv_rec_type;
63 SUBTYPE khrv_tbl_type IS OKL_CONTRACT_PVT.khrv_tbl_type;
64 SUBTYPE klev_rec_type IS okl_CONTRACT_PVT.klev_rec_type;
65 SUBTYPE klev_tbl_type IS okl_CONTRACT_PVT.klev_tbl_type;
66 SUBTYPE clev_rec_type IS okl_okc_migration_pvt.clev_rec_type;
67 SUBTYPE clev_tbl_type IS okl_okc_migration_pvt.clev_tbl_type;
68 SUBTYPE chrv_rec_type IS OKL_OKC_MIGRATION_PVT.chrv_rec_type;
69
70 SUBTYPE polv_rec_type IS OKL_POL_PVT.polv_rec_type;
71 SUBTYPE polv_tbl_type IS OKL_POL_PVT.polv_tbl_type;
72 SUBTYPE pocv_rec_type IS OKL_POC_PVT.pocv_rec_type;
73 SUBTYPE pocv_tbl_type IS OKL_POC_PVT.pocv_tbl_type;
74 SUBTYPE poxv_rec_type IS OKL_POX_PVT.poxv_rec_type;
75 SUBTYPE poxv_tbl_type IS OKL_POX_PVT.poxv_tbl_type;
76
77 SUBTYPE taiv_rec_type IS OKL_TAI_PVT.taiv_rec_type;
78 SUBTYPE taiv_tbl_type IS OKL_TAI_PVT.taiv_tbl_type;
79 SUBTYPE tilv_rec_type IS OKL_TIL_PVT.tilv_rec_type;
80 SUBTYPE tilv_tbl_type IS OKL_TIL_PVT.tilv_tbl_type;
81
82
83 ----------------------------------------------------------------------------
84 -- Procedures and Functions
85 ----------------------------------------------------------------------------
86 ----------------------------------------------------------------------------------
87 -- Start of comments
88 --
89 -- Procedure Name : generate_journal_entries
90 -- Description : generate journal entries for securitization agreement
91 -- Business Rules :
92 -- Parameters :
93 -- Version : 1.0
94 -- Version : 2.0 - Uptaken new Accounting Engine functionality in R12 codeline.
95 -- - Varangan - Bug#5964482.
96 -- End of comments
97 ----------------------------------------------------------------------------------
98 PROCEDURE generate_journal_entries(
99 p_api_version IN NUMBER
100 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
101 ,x_return_status OUT NOCOPY VARCHAR2
102 ,x_msg_count OUT NOCOPY NUMBER
103 ,x_msg_data OUT NOCOPY VARCHAR2
104 ,p_contract_id IN NUMBER
105 ,p_transaction_type IN VARCHAR2 -- 'INV'
106 ,p_transaction_date IN DATE DEFAULT NULL)
107 IS
108 l_api_name CONSTANT VARCHAR2(30) := 'generate_journal_entries';
109 l_api_version CONSTANT NUMBER := 1.0;
110
111 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
112 --
113 -- Define PL/SQL Records and Tables
114 l_trxH_in_rec Okl_Trx_Contracts_Pvt.tcnv_rec_type;
115 l_trxL_in_rec Okl_Trx_Contracts_Pvt.tclv_rec_type;
116 l_trxH_out_rec Okl_Trx_Contracts_Pvt.tcnv_rec_type;
117 l_trxL_out_rec Okl_Trx_Contracts_Pvt.tclv_rec_type;
118
119 -- Define variables
120 l_sysdate DATE;
121 l_sysdate_trunc DATE;
122 l_post_to_gl_yn VARCHAR2(1);
123
124 i NUMBER;
125 l_amount NUMBER;
126 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
127 l_msg_count NUMBER;
128 l_msg_data VARCHAR2(2000);
129 l_currency_code okl_txl_cntrct_lns.currency_code%TYPE;
130 l_fnd_profile VARCHAR2(256);
131 l_cust_trx_type_id NUMBER;
132
133 l_msg_index_out NUMBER; --TBR
134
135 CURSOR fnd_pro_csr IS
136 SELECT mo_global.get_current_org_id() l_fnd_profile
137 FROM dual;
138 fnd_pro_rec fnd_pro_csr%ROWTYPE;
139 /*
140 Cursor ra_cust_csr IS
141 select cust_trx_type_id l_cust_trx_type_id
142 from ra_cust_trx_types
143 where name = 'Investor-OKL';
144 ra_cust_rec ra_cust_csr%ROWTYPE;
145
146 Cursor salesP_csr( chrId NUMBER) IS
147 select ct.object1_id1 id
148 from okc_contacts ct,
149 okc_contact_sources csrc,
150 okc_k_party_roles_b pty,
151 okc_k_headers_b chr
152 where ct.cpl_id = pty.id
153 and ct.cro_code = csrc.cro_code
154 and ct.jtot_object1_code = csrc.jtot_object_code
155 and ct.dnz_chr_id = chr.id
156 and pty.rle_code = csrc.rle_code
157 and csrc.cro_code = 'SALESPERSON'
158 and csrc.rle_code = 'LESSOR'
159 and csrc.buy_or_sell = chr.buy_or_sell
160 and pty.dnz_chr_id = chr.id
161 and pty.chr_id = chr.id
162 and chr.id = chrId;
163
164 l_salesP_rec salesP_csr%ROWTYPE;
165
166
167 Cursor custBillTo_csr( chrId NUMBER) IS
168 select object1_id1 cust_acct_site_id
169 from okc_rules_b rul
170 where rul.rule_information_category = 'BTO'
171 and exists (select '1'
172 from okc_rule_groups_b rgp
173 where rgp.id = rul.rgp_id
174 and rgp.rgd_code = 'LABILL'
175 and rgp.dnz_chr_id = chrId );
176
177 l_custBillTo_rec custBillTo_csr%ROWTYPE;
178 */
179 CURSOR Product_csr (p_contract_id IN okl_products_v.id%TYPE) IS
180 SELECT pdt.id product_id
181 ,pdt.name product_name
182 ,khr.sts_code contract_status
183 ,khr.start_date start_date
184 ,khr.currency_code currency_code
185 ,khr.authoring_org_id authoring_org_id
186 ,khr.currency_conversion_rate currency_conversion_rate
187 ,khr.currency_conversion_type currency_conversion_type
188 ,khr.currency_conversion_date currency_conversion_date
189 --Bug# 4622198
190 ,khr.scs_code
191 FROM okl_products_v pdt
192 ,okl_k_headers_full_v khr
193 WHERE khr.id = p_contract_id
194 AND khr.pdt_id = pdt.id;
195
196 l_func_curr_code OKL_K_HEADERS_FULL_V.CURRENCY_CODE%TYPE;
197 l_chr_curr_code OKL_K_HEADERS_FULL_V.CURRENCY_CODE%TYPE;
198 x_currency_conversion_rate okl_k_headers_full_v.currency_conversion_rate%TYPE;
199 x_currency_conversion_type okl_k_headers_full_v.currency_conversion_type%TYPE;
200 x_currency_conversion_date okl_k_headers_full_v.currency_conversion_date%TYPE;
201
202 CURSOR Transaction_Type_csr (p_transaction_type IN okl_trx_types_v.name%TYPE ) IS
203 SELECT id trx_try_id
204 FROM okl_trx_types_tl
205 WHERE name = p_transaction_type
206 AND LANGUAGE = 'US';
207
208 CURSOR fnd_lookups_csr( lkp_type VARCHAR2, mng VARCHAR2 ) IS
209 SELECT description,
210 lookup_code
211 FROM fnd_lookup_values
212 WHERE LANGUAGE = 'US'
213 AND lookup_type = lkp_type
214 AND meaning = mng;
215
216 CURSOR trx_csr( khrId NUMBER, tcntype VARCHAR2 ) IS
217 SELECT txh.ID HeaderTransID,
218 txl.ID LineTransID,
219 txh.date_transaction_occurred date_transaction_occurred
220 FROM okl_trx_contracts txh,
221 okl_txl_cntrct_lns txl
222 WHERE txl.tcn_id = txh.id
223 AND txh.tcn_type = tcntype
224 --rkuttiya added for 12.1.1 Multi GAAP
225 AND txh.representation_type = 'PRIMARY'
226 --
227 AND txl.khr_id = khrId;
228
229
230 -- investor code for agreement special accounting
231 CURSOR special_acct_rec_csr( chrId NUMBER) IS
232 SELECT rul.rule_information1 investor_code
233 FROM okc_rules_b rul,
234 okc_rule_groups_b rgp
235 WHERE rgp.id = rul.rgp_id
236 AND rul.rule_information_category = G_RULE_GRP_LASEAC --'LASEAC'
237 AND rgp.rgd_code = G_RULE_LASEAC --'LASEAC'
238 AND rul.dnz_chr_id = chrId
239 ;
240
241 -- Cursor Types
242 l_Product_rec Product_csr%ROWTYPE;
243 l_Trx_Type_rec Transaction_Type_csr%ROWTYPE;
244 l_fnd_rec fnd_lookups_csr%ROWTYPE;
245 l_fnd_rec1 fnd_lookups_csr%ROWTYPE;
246 l_trx_rec trx_csr%ROWTYPE;
247 l_special_acct_rec special_acct_rec_csr%ROWTYPE;
248
249 l_transaction_type VARCHAR2(256) := p_transaction_type;
250 l_transaction_date DATE;
251
252 l_tmpl_identify_rec OKL_ACCOUNT_DIST_PVT.TMPL_IDENTIFY_REC_TYPE;
253 l_dist_info_rec OKL_ACCOUNT_DIST_PVT.dist_info_REC_TYPE;
254 l_template_tbl OKL_ACCOUNT_DIST_PVT.AVLV_TBL_TYPE;
255 l_amount_tbl OKL_ACCOUNT_DIST_PVT.AMOUNT_TBL_TYPE;
256
257 -- Begin - Bug#5964482 - AE Uptake Changes
258 l_tmpl_identify_tbl Okl_Account_Dist_Pvt.tmpl_identify_tbl_type;
259 l_dist_info_tbl Okl_Account_Dist_Pvt.dist_info_tbl_type;
260 l_ctxt_tbl Okl_Account_Dist_Pvt.CTXT_TBL_TYPE;
261 l_template_out_tbl Okl_Account_Dist_Pvt.avlv_out_tbl_type;
262 l_amount_out_tbl Okl_Account_Dist_Pvt.amount_out_tbl_type;
263 l_acc_gen_tbl Okl_Account_Dist_Pvt.ACC_GEN_TBL_TYPE;
264 l_tcn_id NUMBER;
265 l_tclv_tbl OKL_TRX_CONTRACTS_PUB.tclv_tbl_type;
266 x_tclv_tbl Okl_Trx_Contracts_Pub.tclv_tbl_type;
267 l_count NUMBER;
268
269
270 -- End - Bug#5964482 - AE Uptake Changes
271 l_ctxt_val_tbl OKL_ACCOUNT_DIST_PVT.CTXT_VAL_TBL_TYPE;
272 l_acc_gen_primary_key_tbl OKL_ACCOUNT_DIST_PVT.acc_gen_primary_key;
273
274 l_has_trans VARCHAR2(1);
275 l_trx_desc VARCHAR2(2000);
276
277 --Bug# 4622198
278 l_fact_synd_code FND_LOOKUPS.Lookup_code%TYPE;
279 l_inv_acct_code OKC_RULES_B.Rule_Information1%TYPE;
280 --Bug# 4622198
281 --
282 --Added by kthiruva for Bug 6354647
283 l_legal_entity_id NUMBER;
284
285 BEGIN
286 -- Set API savepoint
287 SAVEPOINT generate_journal_entries_PVT;
288
289 -- Check for call compatibility
290 IF (NOT FND_API.Compatible_API_Call (l_api_version,
291 p_api_version,
292 l_api_name,
293 G_PKG_NAME ))
294 THEN
295 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
296 END IF;
297
298 -- Initialize message list if requested
299 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
300 FND_MSG_PUB.initialize;
301 END IF;
302
303 -- Initialize API status to success
304 x_return_status := OKL_API.G_RET_STS_SUCCESS;
305
306
307 /*** Begin API body ****************************************************/
308 --------------------------------------------------------------------------
309 -- Initialize API variables
310 --------------------------------------------------------------------------
311 --dbms_output.put_line('Initialize API variables');
312
313 l_sysdate := SYSDATE;
314 l_sysdate_trunc := TRUNC(SYSDATE);
315 i := 0;
316 l_post_to_gl_yn := 'Y';
317
318 --------------------------------------------------------------------------
319 -- Get indirect values
320 --------------------------------------------------------------------------
321 --dbms_output.put_line('Get indirect values');
322
323 -- Get product_id
324 OPEN Product_csr(p_contract_id);
325 FETCH Product_csr INTO l_Product_rec;
326 IF Product_csr%NOTFOUND THEN
327 Okl_Api.SET_MESSAGE(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN, 'Product');
328 CLOSE Product_csr;
329 RAISE Okl_Api.G_EXCEPTION_ERROR;
330 END IF;
331 CLOSE Product_csr;
332
333 l_currency_code := l_Product_rec.currency_code;
334
335 -- default trsanction date to contract start date
336 IF ( p_transaction_date IS NULL ) THEN
337 l_transaction_date := l_Product_rec.start_date;
338 ELSE
339 l_transaction_date := p_transaction_date;
340 END IF;
341
342 -- get translated transaction message
343 l_trx_desc := fnd_message.get_string(G_APP_NAME, G_OKL_SEC_ACCT_TRX_DESC);
344 IF l_trx_desc IS NULL THEN
345 l_trx_desc := 'Journals - ' || l_transaction_type;
346 END IF;
347
348 --------------------------------------------------------------------------
349 -- multi-currency setup
350 --------------------------------------------------------------------------
351 --dbms_output.put_line('multi-currency setup');
352
353
354 l_chr_curr_code := l_Product_rec.CURRENCY_CODE;
355 l_func_curr_code := OKC_CURRENCY_API.GET_OU_CURRENCY(l_Product_rec.authoring_org_id);
356
357 x_currency_conversion_rate := NULL;
358 x_currency_conversion_type := NULL;
359 x_currency_conversion_date := NULL;
360
361 IF ( ( l_func_curr_code IS NOT NULL) AND
362 ( l_chr_curr_code <> l_func_curr_code ) ) THEN
363
364 x_currency_conversion_type := l_Product_rec.currency_conversion_type;
365 x_currency_conversion_date := l_Product_rec.start_date;
366
367 IF ( l_Product_rec.currency_conversion_type = 'User') THEN
368 x_currency_conversion_rate := l_Product_rec.currency_conversion_rate;
369 x_currency_conversion_date := l_Product_rec.currency_conversion_date;
370 ELSE
371 x_currency_conversion_rate := okl_accounting_util.get_curr_con_rate(
372 p_from_curr_code => l_chr_curr_code,
373 p_to_curr_code => l_func_curr_code,
374 p_con_date => l_Product_rec.start_date,
375 p_con_type => l_Product_rec.currency_conversion_type);
376
377 END IF;
378
379 END IF;
380
381 --------------------------------------------------------------------------
382 -- Validate passed parameters
383 --------------------------------------------------------------------------
384 --dbms_output.put_line('Validate passed parameters');
385
386
387 -- check contract
388 IF ( p_contract_id = Okl_Api.G_MISS_NUM )
389 OR ( p_contract_id IS NULL ) THEN
390 Okl_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN, 'contract');
391 RAISE Okl_Api.G_EXCEPTION_ERROR;
392 END IF;
393
394 IF ( l_transaction_type = Okl_Api.G_MISS_CHAR )
395 OR ( l_transaction_type IS NULL ) THEN
396 Okl_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN, l_transaction_type);
397 RAISE Okl_Api.G_EXCEPTION_ERROR;
398 END IF;
399
400 -- Check Transaction_Type
401 OPEN Transaction_Type_csr(l_transaction_type);
402 FETCH Transaction_Type_csr INTO l_Trx_Type_rec;
403 IF Transaction_Type_csr%NOTFOUND THEN
404 Okl_Api.SET_MESSAGE(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN, l_transaction_type);
405 CLOSE Transaction_Type_csr;
406 RAISE Okl_Api.G_EXCEPTION_ERROR;
407 END IF;
408 CLOSE Transaction_Type_csr;
409
410 OPEN fnd_lookups_csr('OKL_TCN_TYPE', l_transaction_type);
411 FETCH fnd_lookups_csr INTO l_fnd_rec;
412 IF fnd_lookups_csr%NOTFOUND THEN
413 Okl_Api.SET_MESSAGE(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN, l_transaction_type);
414 CLOSE fnd_lookups_csr;
415 RAISE Okl_Api.G_EXCEPTION_ERROR;
416 END IF;
417 CLOSE fnd_lookups_csr;
418
419 OPEN trx_csr(p_contract_id,l_fnd_rec.lookup_code);
420 FETCH trx_csr INTO l_trx_rec;
421 IF trx_csr%NOTFOUND THEN -- While activation, create a new trans always.
422 l_has_trans := OKL_API.G_FALSE;
423 ELSE
424 l_has_trans := OKL_API.G_TRUE;
425 END IF;
426 CLOSE trx_csr;
427
428 -- Check special accounting code
429 OPEN special_acct_rec_csr(p_contract_id);
430 FETCH special_acct_rec_csr INTO l_special_acct_rec;
431 /* comment out. it's not a required attribute
432 IF special_acct_rec_csr%NOTFOUND THEN
433 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Agreement Accounting Code');
434 CLOSE special_acct_rec_csr;
435 RAISE Okl_Api.G_EXCEPTION_ERROR;
436 END IF;
437 */
438 CLOSE special_acct_rec_csr;
439
440 --------------------------------------------------------------------------
441 -- Assign passed in record values for transaction header and line
442 --------------------------------------------------------------------------
443 --dbms_output.put_line('Assign passed in record values for transaction header and line');
444
445 --Added by kthiruva on 22-Aug-2007
446 --Bug 6354647 - Start of Changes
447 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_contract_id) ;
448
449 IF l_legal_entity_id IS NOT NULL THEN
450 l_trxH_in_rec.legal_entity_id := l_legal_entity_id;
451 END IF;
452 --Bug 6354647 - End of Changes
453
454
455 l_trxH_in_rec.khr_id := p_contract_id;
456 l_trxH_in_rec.pdt_id := l_Product_rec.product_id;
457 l_trxH_in_rec.tcn_type := l_fnd_rec.lookup_code;
458 l_trxH_in_rec.currency_code := l_currency_code;
459 l_trxH_in_rec.try_id := l_Trx_Type_rec.trx_try_id;
460 l_trxH_in_rec.description := l_trx_desc;
461
462 l_trxH_in_rec.currency_conversion_rate := x_currency_conversion_rate;
463 l_trxH_in_rec.currency_conversion_type := x_currency_conversion_type;
464 l_trxH_in_rec.currency_conversion_date := x_currency_conversion_date;
465
466 OPEN fnd_lookups_csr('OKL_TCL_TYPE', l_transaction_type);
467 FETCH fnd_lookups_csr INTO l_fnd_rec1;
468 IF fnd_lookups_csr%NOTFOUND THEN
469 Okl_Api.SET_MESSAGE(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN, l_transaction_type);
470 CLOSE fnd_lookups_csr;
471 RAISE Okl_Api.G_EXCEPTION_ERROR;
472 END IF;
473 CLOSE fnd_lookups_csr;
474
475
476
477
478 --------------------------------------------------------------------------
479 -- Create transaction Header and line
480 --------------------------------------------------------------------------
481 --dbms_output.put_line('Create transaction Header and line');
482
483
484 -- IF ( l_has_trans = OKL_API.G_FALSE ) THEN
485 -- Commenting the above IF condition to create Accounting Header, Lines and Distributions
486 -- Always while activating an agreement
487
488 OPEN fnd_lookups_csr('OKL_TRANSACTION_STATUS', 'Submitted');
489 FETCH fnd_lookups_csr INTO l_fnd_rec;
490 IF fnd_lookups_csr%NOTFOUND THEN
491 Okl_Api.SET_MESSAGE(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,l_transaction_type);
492 CLOSE fnd_lookups_csr;
493 RAISE Okl_Api.G_EXCEPTION_ERROR;
494 END IF;
495 CLOSE fnd_lookups_csr;
496
497 l_trxH_in_rec.tsu_code := l_fnd_rec.lookup_code;
498
499 l_trxH_in_rec.date_transaction_occurred := l_transaction_date;
500
501 l_trxH_in_rec.description := l_fnd_rec.description;
502
503 -- Create Transaction Header, Lines
504 Okl_Trx_Contracts_Pub.create_trx_contracts(
505 p_api_version => l_api_version
506 ,p_init_msg_list => l_init_msg_list
507 ,x_return_status => l_return_status
508 ,x_msg_count => l_msg_count
509 ,x_msg_data => l_msg_data
510 ,p_tcnv_rec => l_trxH_in_rec
511 ,x_tcnv_rec => l_trxH_out_rec);
512
513 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
514 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
515 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
516 RAISE Okl_Api.G_EXCEPTION_ERROR;
517 END IF;
518
519 IF ((l_trxH_out_rec.id = OKL_API.G_MISS_NUM) OR
520 (l_trxH_out_rec.id IS NULL) ) THEN
521 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'TRANSACTION_ID');
522 RAISE OKL_API.G_EXCEPTION_ERROR;
523 END IF;
524 l_fnd_rec := NULL;
525
526 -------------------------------------------------------------------------
527 --Bug# 4622198 :For special accounting treatment
528 --------------------------------------------------------------------------
529 OKL_SECURITIZATION_PVT.Check_Khr_ia_associated(
530 p_api_version => l_api_version,
531 p_init_msg_list => l_init_msg_list,
532 x_return_status => l_return_status,
533 x_msg_count => l_msg_count,
534 x_msg_data => l_msg_data,
535 p_khr_id => p_contract_id,
536 p_scs_code => l_product_rec.scs_code,
537 p_trx_date => l_transaction_date,
538 x_fact_synd_code => l_fact_synd_code,
539 x_inv_acct_code => l_inv_acct_code
540 );
541
542 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
543 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
544 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
545 RAISE OKL_API.G_EXCEPTION_ERROR;
546 END IF;
547
548 -------------------------------------------------------------------------
549 -- Determine Number of transaction lines to create - accounting template record
550 --------------------------------------------------------------------------
551 l_tmpl_identify_rec.TRANSACTION_TYPE_ID := l_Trx_Type_rec.trx_try_id;
552 l_tmpl_identify_rec.PRODUCT_ID := l_Product_rec.product_id;
553 l_tmpl_identify_rec.FACTORING_SYND_FLAG := G_FACTORING_SYND_FLAG_INVESTOR;
554 l_tmpl_identify_rec.INVESTOR_CODE := l_special_acct_rec.investor_code;
555 l_tmpl_identify_rec.factoring_synd_flag := l_fact_synd_code;
556 l_tmpl_identify_rec.investor_code := l_inv_acct_code;
557
558 -------------------------------------------------------------------------
559 --Call to get_template_info to determine the number of transaction lines to be created
560 -------------------------------------------------------------------------
561 Okl_Account_Dist_Pub.GET_TEMPLATE_INFO(
562 p_api_version => p_api_version,
563 p_init_msg_list => p_init_msg_list,
564 x_return_status => l_return_status,
565 x_msg_count => l_msg_count,
566 x_msg_data => l_msg_data,
567 p_tmpl_identify_rec => l_tmpl_identify_rec,
568 x_template_tbl => l_template_tbl,
569 p_validity_date => l_transaction_date);
570 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
571 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
572 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
573 RAISE Okl_Api.G_EXCEPTION_ERROR;
574 END IF;
575
576 -------------------------------------------------------------------------
577 -- Build the transaction line table of records
578 -------------------------------------------------------------------------
579 FOR i IN l_template_tbl.FIRST..l_template_tbl.LAST
580 LOOP
581 l_tclv_tbl(i).line_number := i;
582 l_tclv_tbl(i).khr_id := p_contract_id;
583 l_tclv_tbl(i).tcl_type := l_fnd_rec1.lookup_code;
584 l_tclv_tbl(i).currency_code := l_currency_code;
585 l_tclv_tbl(i).description := l_trx_desc;
586 l_tclv_tbl(i).tcn_id := l_trxH_out_rec.id;
587 l_tclv_tbl(i).sty_id := l_template_tbl(i).sty_id;
588 END LOOP;
589
590
591
592
593 --------------------------------------------------------------------------
594 -- Create Transaction Header, Lines
595 --------------------------------------------------------------------------
596 Okl_Trx_Contracts_Pub.create_trx_cntrct_lines(
597 p_api_version => l_api_version,
598 p_init_msg_list => l_init_msg_list,
599 x_return_status => l_return_status,
600 x_msg_count => l_msg_count,
601 x_msg_data => l_msg_data,
602 p_tclv_tbl => l_tclv_tbl,
603 x_tclv_tbl => x_tclv_tbl); --l_trxL_out_rec
604
605
606
607 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
608 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
609 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
610 RAISE Okl_Api.G_EXCEPTION_ERROR;
611 END IF;
612
613 ----------------------------------------------------------------------------------------
614 -- Populating the tmpl_identify_tbl from the template_tbl returned by get_template_info
615 ----------------------------------------------------------------------------------------
616 FOR i in l_template_tbl.FIRST.. l_template_tbl.LAST
617 LOOP
618 l_tmpl_identify_tbl(i).product_id := l_Product_rec.product_id;
619 l_tmpl_identify_tbl(i).transaction_type_id := l_Trx_Type_rec.trx_try_id;
620 l_tmpl_identify_tbl(i).stream_type_id := l_template_tbl(i).sty_id;
621 l_tmpl_identify_tbl(i).advance_arrears := l_template_tbl(i).advance_arrears;
622 l_tmpl_identify_tbl(i).prior_year_yn := l_template_tbl(i).prior_year_yn;
623 l_tmpl_identify_tbl(i).memo_yn := l_template_tbl(i).memo_yn;
624 l_tmpl_identify_tbl(i).factoring_synd_flag := l_template_tbl(i).factoring_synd_flag;
625 l_tmpl_identify_tbl(i).investor_code := l_template_tbl(i).inv_code;
626 l_tmpl_identify_tbl(i).SYNDICATION_CODE := l_template_tbl(i).syt_code;
627 l_tmpl_identify_tbl(i).FACTORING_CODE := l_template_tbl(i).fac_code;
628 END LOOP;
629
630 ------------------------------------------------------------------------------------------------
631 -- Populate account generator sources
632 ------------------------------------------------------------------------------------------------
633 OKL_ACC_CALL_PVT.okl_populate_acc_gen(
634 p_contract_id =>p_contract_id ,
635 x_acc_gen_tbl => l_acc_gen_primary_key_tbl,
636 x_return_status => l_return_status);
637
638
639 IF l_Product_rec.contract_status = 'ACTIVE'
640 THEN
641 l_ctxt_val_tbl(0).NAME := 'p_transaction_reason';
642 l_ctxt_val_tbl(0).VALUE := 'ADJUSTMENTS';
643 END IF;
644
645
646 --------------------------------------------------------------------------
647 /* Populating the dist_info_Tbl */
648 --------------------------------------------------------------------------
649
650 FOR i in x_tclv_tbl.FIRST..x_tclv_tbl.LAST
651 LOOP
652 IF ((x_tclv_tbl(i).id = OKL_API.G_MISS_NUM) OR
653 (x_tclv_tbl(i).id IS NULL) ) THEN
654 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'TRANSACTION_ID');
655 RAISE OKL_API.G_EXCEPTION_ERROR;
656 END IF;
657 --Assigning the account generator table
658 l_acc_gen_tbl(i).acc_gen_key_tbl := l_acc_gen_primary_key_tbl;
659 l_acc_gen_tbl(i).source_id := x_tclv_tbl(i).id;
660
661 IF (l_ctxt_val_tbl.COUNT > 0) THEN
662 l_ctxt_tbl(i).ctxt_val_tbl := l_ctxt_val_tbl;
663 l_ctxt_tbl(i).source_id := x_tclv_tbl(i).id;
664 END IF;
665
666 l_dist_info_tbl(i).SOURCE_ID := x_tclv_tbl(i).id;
667 l_dist_info_tbl(i).ACCOUNTING_DATE := l_trxH_out_rec.date_transaction_occurred;
668 l_dist_info_tbl(i).SOURCE_TABLE := 'OKL_TXL_CNTRCT_LNS';
669 l_dist_info_tbl(i).GL_REVERSAL_FLAG := 'N';
670 l_dist_info_tbl(i).POST_TO_GL := l_post_to_gl_yn;
671 l_dist_info_tbl(i).CONTRACT_ID := p_contract_id;
672 l_dist_info_tbl(i).currency_conversion_rate := x_currency_conversion_rate;
673 l_dist_info_tbl(i).currency_conversion_type := x_currency_conversion_type;
674 l_dist_info_tbl(i).currency_conversion_date := x_currency_conversion_date;
675 l_dist_info_tbl(i).currency_code := l_currency_code;
676 END LOOP;
677
678 /* ELSE -- Commenting out this Else condition, since there is no possibility of
679 -- of just doing accounting distributions with existing Accounting header line and line
680 -- while activating an agreement.
681 -- Pls. update this code appropriately in future, if only accounting distribution
682 -- alone should be done while activating an Investor Agreement with already existing
683 -- Accounting entries
684 --------------------------------------------------------------------------
685 -- Refer transaction line ID if already exists -- future needs
686 --------------------------------------------------------------------------
687
688 l_dist_info_rec.SOURCE_ID := l_trx_rec.LineTransId;
689 l_dist_info_rec.ACCOUNTING_DATE := l_trx_rec.date_transaction_occurred;
690 END IF; */
691
692
693 ------------------------------------------------------------------------------------------------
694 -- Call Okl_Account_Dist_Pub API to create accounting entries for this transaction- new signature
695 ------------------------------------------------------------------------------------------------
696 --Assigning transaction header id from the transaction header record created
697
698 l_tcn_id := l_trxH_out_rec.id;
699
700 Okl_Account_Dist_Pvt.CREATE_ACCOUNTING_DIST(
701 p_api_version => 1.0,
702 p_init_msg_list => l_init_msg_list,
703 x_return_status => l_return_status,
704 x_msg_count => l_msg_count,
705 x_msg_data => l_msg_data,
706 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
707 p_dist_info_tbl => l_dist_info_tbl,
708 p_ctxt_val_tbl => l_ctxt_tbl,
709 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
710 x_template_tbl => l_template_out_tbl,
711 x_amount_tbl => l_amount_out_tbl,
712 p_trx_header_id => l_tcn_id);
713
714 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
715 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
716 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
717 RAISE Okl_Api.G_EXCEPTION_ERROR;
718 END IF;
719
720 --------------------------------------------------------------------------
721 -- Summation amount after accounting API generate transction records from
722 -- formula functions
723 --------------------------------------------------------------------------
724 --dbms_output.put_line('Summation amount after accounting API generate transction records from formula functions');
725
726
727 -- Check Status
728 IF(l_return_status = Okl_Api.G_RET_STS_SUCCESS) THEN
729
730 --From the l_amount_out_tbl returned , the transaction line amount and header amount
731 --need to be updated back on the contract
732 l_tclv_tbl := x_tclv_tbl;
733 l_trxH_in_rec := l_trxH_out_rec;
734 l_count := l_amount_out_tbl.FIRST;
735 FOR i in l_tclv_tbl.FIRST..l_tclv_tbl.LAST LOOP
736 IF l_tclv_tbl(i).id = l_amount_out_tbl(l_count).source_id THEN
737 /*
738 05-Sep-2007, ankushar Bug# 6391302
739 start changes, added check for NULL and G_MISS_NUM for amount field
740 */
741 IF l_tclv_tbl(i).amount IS NULL OR l_tclv_tbl(i).amount = OKL_API.G_MISS_NUM THEN
742 l_tclv_tbl(i).amount :=0;
743 END IF;
744 /* 05-Sep-2007 ankushar end changes */
745
746 l_amount_tbl := l_amount_out_tbl(l_count).amount_tbl;
747 IF l_amount_tbl.COUNT > 0 THEN
748 FOR j in l_amount_tbl.FIRST..l_amount_tbl.LAST LOOP
749 l_tclv_tbl(i).amount := l_tclv_tbl(i).amount + l_amount_tbl(j);
750 END LOOP;
751 END IF;
752 END IF;
753 l_tclv_tbl(i).currency_code := l_currency_code;
754 /*
755 05-Sep-2007, ankushar Bug# 6391302
756 start changes, added check for NULL and G_MISS_NUM for amount field
757 */
758 IF l_trxH_in_rec.amount IS NULL OR l_trxH_in_rec.amount = OKL_API.G_MISS_NUM THEN
759 l_trxH_in_rec.amount :=0;
760 END IF;
761 /* 05-Sep-2007 ankushar end changes */
762
763 l_trxH_in_rec.amount := l_trxH_in_rec.amount + l_tclv_tbl(i).amount;
764 l_count := l_count + 1;
765 END LOOP;
766
767 l_trxH_in_rec.currency_code := l_currency_code;
768
769 OPEN fnd_lookups_csr('OKL_TRANSACTION_STATUS', 'Processed');
770 FETCH fnd_lookups_csr INTO l_fnd_rec;
771 IF fnd_lookups_csr%NOTFOUND THEN
772 Okl_Api.SET_MESSAGE(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,l_transaction_type);
773 CLOSE fnd_lookups_csr;
774 RAISE Okl_Api.G_EXCEPTION_ERROR;
775 END IF;
776 CLOSE fnd_lookups_csr;
777 l_trxH_in_rec.tsu_code := l_fnd_rec.lookup_code;
778
779 ELSE
780
781 OPEN fnd_lookups_csr('OKL_TRANSACTION_STATUS', 'Error');
782 FETCH fnd_lookups_csr INTO l_fnd_rec;
783 IF fnd_lookups_csr%NOTFOUND THEN
784 Okl_Api.SET_MESSAGE(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,l_transaction_type);
785 CLOSE fnd_lookups_csr;
786 RAISE Okl_Api.G_EXCEPTION_ERROR;
787 END IF;
788 CLOSE fnd_lookups_csr;
789 l_trxH_in_rec.tsu_code := l_fnd_rec.lookup_code;
790 l_trxH_in_rec.amount := NULL;
791 FOR i in l_tclv_tbl.FIRST..l_tclv_tbl.LAST
792 LOOP
793 l_tclv_tbl(i).amount := NULL;
794 END LOOP;
795 --l_trxL_in_rec.amount := NULL;
796
797 END IF;
798 --------------------------------------------------------------------------
799 -- Update amount and tsu_code for parent record refer from all chrildren
800 -- records generated by accounting distribution API
801 --------------------------------------------------------------------------
802 --dbms_output.put_line('Update amount and tsu_code for parent record refer from all chrildren...');
803
804
805 Okl_Trx_Contracts_Pub.update_trx_contracts(
806 p_api_version => l_api_version
807 ,p_init_msg_list => l_init_msg_list
808 ,x_return_status => l_return_status
809 ,x_msg_count => l_msg_count
810 ,x_msg_data => l_msg_data
811 ,p_tcnv_rec => l_trxH_in_rec
812 ,x_tcnv_rec => l_trxH_out_rec);
813
814 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
815 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
816 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
817 RAISE Okl_Api.G_EXCEPTION_ERROR;
818 END IF;
819
820 Okl_Trx_Contracts_Pub.update_trx_cntrct_lines(
821 p_api_version => l_api_version
822 ,p_init_msg_list => l_init_msg_list
823 ,x_return_status => l_return_status
824 ,x_msg_count => l_msg_count
825 ,x_msg_data => l_msg_data
826 ,p_tclv_tbl => l_tclv_tbl
827 ,x_tclv_tbl => x_tclv_tbl);
828
829 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
830 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
831 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
832 RAISE Okl_Api.G_EXCEPTION_ERROR;
833 END IF;
834
835 OKL_MULTIGAAP_ENGINE_PVT.CREATE_SEC_REP_TRX
836 (p_api_version => p_api_version
837 ,p_init_msg_list => p_init_msg_list
838 ,x_return_status => l_return_status
839 ,x_msg_count => x_msg_count
840 ,x_msg_data => x_msg_data
841 ,P_TCNV_REC => l_trxH_out_rec
842 ,P_TCLV_TBL => x_tclv_tbl
843 ,p_ctxt_val_tbl => l_ctxt_tbl
844 ,p_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl);
845
846 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
847 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
848 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
849 RAISE Okl_Api.G_EXCEPTION_ERROR;
850 END IF;
851
852 /*** End API body ******************************************************/
853
854 -- Get message count and if count is 1, get message info
855 FND_MSG_PUB.Count_And_Get
856 (p_count => x_msg_count,
857 p_data => x_msg_data);
858
859 EXCEPTION
860 WHEN OKL_API.G_EXCEPTION_ERROR THEN
861 ROLLBACK TO generate_journal_entries_PVT;
862 x_return_status := OKL_API.G_RET_STS_ERROR;
863 FND_MSG_PUB.Count_And_Get
864 (p_count => x_msg_count,
865 p_data => x_msg_data);
866
867 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
868 ROLLBACK TO generate_journal_entries_PVT;
869 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
870 FND_MSG_PUB.Count_And_Get
871 (p_count => x_msg_count,
872 p_data => x_msg_data);
873
874 WHEN OTHERS THEN
875 ROLLBACK TO generate_journal_entries_PVT;
876 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
877 OKL_API.Set_Message(p_app_name => G_APP_NAME,
878 p_msg_name => G_UNEXPECTED_ERROR,
879 p_token1 => G_SQLCODE_TOKEN,
880 p_token1_value => SQLCODE,
881 p_token2 => G_SQLERRM_TOKEN,
882 p_token2_value => SQLERRM);
883 FND_MSG_PUB.Count_And_Get
884 (p_count => x_msg_count,
885 p_data => x_msg_data);
886
887 END generate_journal_entries;
888
889 --------------------------------------------------------------------------
890 ----- Validate Contract Number uniqueness check
891 --------------------------------------------------------------------------
892 FUNCTION validate_contract_number(
893 p_secAgreement_rec IN secAgreement_rec_type
894 ,p_mode VARCHAR2
895 ) RETURN VARCHAR2
896 IS
897 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
898 l_dummy VARCHAR2(1) := '?';
899
900 CURSOR c (p_contract_number VARCHAR2)
901 IS
902 SELECT 'X'
903 FROM okc_k_headers_b k
904 WHERE k.contract_number = p_contract_number
905 ;
906
907 CURSOR c2 (p_contract_number VARCHAR2, p_id NUMBER)
908 IS
909 SELECT 'X'
910 FROM okc_k_headers_b k
911 WHERE k.contract_number = p_contract_number
912 AND k.id <> p_id -- except itself
913 ;
914
915 BEGIN
916
917 -- check only if contract number exists
918 IF (p_secAgreement_rec.contract_number IS NOT NULL AND
919 p_secAgreement_rec.contract_number <> OKL_API.G_MISS_CHAR)
920 THEN
921
922 IF (p_mode = 'C') THEN
923 OPEN c(p_secAgreement_rec.contract_number);
924 FETCH c INTO l_dummy;
925 CLOSE c;
926 ELSIF (p_mode = 'U') THEN
927 OPEN c2(p_secAgreement_rec.contract_number, p_secAgreement_rec.id);
928 FETCH c2 INTO l_dummy;
929 CLOSE c2;
930 END IF;
931
932 IF (l_dummy = 'X')
933 THEN
934 OKL_API.Set_Message(p_app_name => G_APP_NAME,
935 p_msg_name => 'OKL_LLA_NOT_UNIQUE',
936 p_token1 => 'COL_NAME',
937 p_token1_value => 'Agreement Number');
938
939 RAISE G_EXCEPTION_HALT_VALIDATION;
940 END IF;
941
942 END IF;
943
944 RETURN l_return_status;
945
946 EXCEPTION
947 WHEN G_EXCEPTION_HALT_VALIDATION THEN
948 l_return_status := OKL_API.G_RET_STS_ERROR;
949 RETURN l_return_status;
950 WHEN OTHERS THEN
951 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
952 OKL_API.Set_Message(p_app_name => G_APP_NAME,
953 p_msg_name => G_UNEXPECTED_ERROR,
954 p_token1 => G_SQLCODE_TOKEN,
955 p_token1_value => SQLCODE,
956 p_token2 => G_SQLERRM_TOKEN,
957 p_token2_value => SQLERRM);
958 RETURN l_return_status;
959 END;
960 --------------------------------------------------------------------------
961 ----- Validate Product
962 --------------------------------------------------------------------------
963 FUNCTION validate_product(
964 p_secAgreement_rec IN secAgreement_rec_type
965 ,p_mode VARCHAR2
966 ) RETURN VARCHAR2
967 IS
968 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
969 l_dummy VARCHAR2(10);
970 l_row_not_found BOOLEAN := FALSE;
971
972 CURSOR c_pdt IS
973 SELECT 'x'
974 FROM okl_products_v
975 WHERE id = p_secAgreement_rec.pdt_id
976 ;
977
978 BEGIN
979
980 IF (p_mode = 'C') THEN
981 -- check required
982 IF (p_secAgreement_rec.pdt_id IS NULL) OR
983 (p_secAgreement_rec.pdt_id = OKL_API.G_MISS_NUM)
984 THEN
985 OKL_API.Set_Message(p_app_name => G_APP_NAME,
986 p_msg_name => G_REQUIRED_VALUE,
987 p_token1 => G_COL_NAME_TOKEN,
988 p_token1_value => 'Product');
989 RAISE G_EXCEPTION_HALT_VALIDATION;
990 END IF;
991 END IF;
992
993 IF (p_secAgreement_rec.pdt_id IS NOT NULL) AND
994 (p_secAgreement_rec.pdt_id <> OKL_API.G_MISS_NUM)
995 THEN
996
997 -- check FK
998 OPEN c_pdt;
999 FETCH c_pdt INTO l_dummy;
1000 l_row_not_found := c_pdt%NOTFOUND;
1001 CLOSE c_pdt;
1002
1003 IF l_row_not_found THEN
1004 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1005 p_msg_name => G_INVALID_VALUE,
1006 p_token1 => G_COL_NAME_TOKEN,
1007 p_token1_value => 'pdt_id');
1008 RAISE G_EXCEPTION_HALT_VALIDATION;
1009 END IF;
1010 END IF;
1011
1012 RETURN l_return_status;
1013 EXCEPTION
1014 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1015 l_return_status := OKL_API.G_RET_STS_ERROR;
1016 RETURN l_return_status;
1017 WHEN OTHERS THEN
1018 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1019 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1020 p_msg_name => G_UNEXPECTED_ERROR,
1021 p_token1 => G_SQLCODE_TOKEN,
1022 p_token1_value => SQLCODE,
1023 p_token2 => G_SQLERRM_TOKEN,
1024 p_token2_value => SQLERRM);
1025 RETURN l_return_status;
1026 END;
1027
1028 --------------------------------------------------------------------------
1029 ----- Validate Pool number
1030 --------------------------------------------------------------------------
1031 FUNCTION validate_pool_number(
1032 p_secAgreement_rec IN secAgreement_rec_type
1033 ,p_mode VARCHAR2
1034 ) RETURN VARCHAR2
1035 IS
1036 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1037 l_dummy VARCHAR2(10);
1038 l_row_not_found BOOLEAN := FALSE;
1039 l_row_found BOOLEAN := FALSE;
1040
1041 CURSOR c_pool IS
1042 SELECT 'x'
1043 FROM okl_pools
1044 WHERE id = p_secAgreement_rec.pol_id
1045 ;
1046 BEGIN
1047
1048 IF (p_mode = 'C') THEN
1049 IF (p_secAgreement_rec.pol_id IS NULL) OR
1050 (p_secAgreement_rec.pol_id = OKL_API.G_MISS_NUM)
1051 THEN
1052 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1053 p_msg_name => G_REQUIRED_VALUE,
1054 p_token1 => G_COL_NAME_TOKEN,
1055 p_token1_value => 'Pool Number');
1056 RAISE G_EXCEPTION_HALT_VALIDATION;
1057 END IF;
1058 END IF;
1059
1060 IF (p_secAgreement_rec.pol_id IS NOT NULL) AND
1061 (p_secAgreement_rec.pol_id <> OKL_API.G_MISS_NUM)
1062 THEN
1063
1064 -- check FK
1065 OPEN c_pool;
1066 FETCH c_pool INTO l_dummy;
1067 l_row_not_found := c_pool%NOTFOUND;
1068 CLOSE c_pool;
1069
1070 IF l_row_not_found THEN
1071 OKL_API.Set_Message(p_app_name => 'OKC',
1072 p_msg_name => G_INVALID_VALUE,
1073 p_token1 => G_COL_NAME_TOKEN,
1074 p_token1_value => 'pol_id');
1075 RAISE G_EXCEPTION_HALT_VALIDATION;
1076 END IF;
1077 END IF;
1078
1079 RETURN l_return_status;
1080 EXCEPTION
1081 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1082 l_return_status := OKL_API.G_RET_STS_ERROR;
1083 RETURN l_return_status;
1084 WHEN OTHERS THEN
1085 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1086 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1087 p_msg_name => G_UNEXPECTED_ERROR,
1088 p_token1 => G_SQLCODE_TOKEN,
1089 p_token1_value => SQLCODE,
1090 p_token2 => G_SQLERRM_TOKEN,
1091 p_token2_value => SQLERRM);
1092 RETURN l_return_status;
1093 END;
1094 --------------------------------------------------------------------------
1095 ----- Validate pool number vs existing link for khr_id
1096 --------------------------------------------------------------------------
1097 FUNCTION validate_pool_number_unique(
1098 p_secAgreement_rec IN secAgreement_rec_type
1099 ,p_mode VARCHAR2
1100 ) RETURN VARCHAR2
1101 IS
1102 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1103 l_dummy VARCHAR2(10);
1104 l_row_not_found BOOLEAN := FALSE;
1105 l_row_found BOOLEAN := FALSE;
1106
1107 CURSOR c_pool IS
1108 SELECT 'x'
1109 FROM okl_pools
1110 WHERE khr_id IS NOT NULL
1111 AND id = p_secAgreement_rec.pol_id
1112 ;
1113
1114 CURSOR c_pool_upd IS
1115 SELECT 'x'
1116 FROM okl_pools
1117 WHERE khr_id IS NOT NULL
1118 AND id = p_secAgreement_rec.pol_id
1119 AND khr_id <> p_secAgreement_rec.id
1120 ;
1121
1122
1123 BEGIN
1124
1125 IF (p_secAgreement_rec.pol_id IS NOT NULL) AND
1126 (p_secAgreement_rec.pol_id <> OKL_API.G_MISS_NUM)
1127 THEN
1128
1129 -- check 1 on 1 relationship between agreement contract and pool
1130 IF (p_mode = 'C') THEN
1131 OPEN c_pool;
1132 FETCH c_pool INTO l_dummy;
1133 l_row_found := c_pool%FOUND;
1134 CLOSE c_pool;
1135 ELSE
1136 OPEN c_pool_upd;
1137 FETCH c_pool_upd INTO l_dummy;
1138 l_row_found := c_pool_upd%FOUND;
1139 CLOSE c_pool_upd;
1140 END IF;
1141
1142 IF l_row_found THEN
1143 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1144 p_msg_name => 'OKL_LLA_INVALID_POOL_NUM');
1145 RAISE G_EXCEPTION_HALT_VALIDATION;
1146 END IF;
1147 END IF;
1148
1149 RETURN l_return_status;
1150 EXCEPTION
1151 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1152 l_return_status := OKL_API.G_RET_STS_ERROR;
1153 RETURN l_return_status;
1154 WHEN OTHERS THEN
1155 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1156 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1157 p_msg_name => G_UNEXPECTED_ERROR,
1158 p_token1 => G_SQLCODE_TOKEN,
1159 p_token1_value => SQLCODE,
1160 p_token2 => G_SQLERRM_TOKEN,
1161 p_token2_value => SQLERRM);
1162 RETURN l_return_status;
1163 END;
1164
1165 --------------------------------------------------------------------------
1166 ----- Validate Description
1167 --------------------------------------------------------------------------
1168
1169
1170 FUNCTION validate_description(
1171 p_secAgreement_rec IN secAgreement_rec_type
1172 ) RETURN VARCHAR2
1173 IS
1174 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1175 BEGIN
1176
1177 IF (p_secAgreement_rec.short_description IS NOT NULL AND
1178 p_secAgreement_rec.short_description <> OKL_API.G_MISS_CHAR)
1179 THEN
1180
1181 IF (LENGTH(p_secAgreement_rec.short_description) > 600) THEN
1182
1183 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1184 p_msg_name => 'OKL_LLA_EXCEED_MAXIMUM_LENGTH',
1185 p_token1 => 'MAX_CHARS',
1186 p_token1_value => '600',
1187 p_token2 => 'COL_NAME',
1188 p_token2_value => 'Note');
1189
1190 RAISE G_EXCEPTION_HALT_VALIDATION;
1191 END IF;
1192 END IF;
1193
1194 RETURN l_return_status;
1195 EXCEPTION
1196 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1197 l_return_status := OKL_API.G_RET_STS_ERROR;
1198 RETURN l_return_status;
1199 WHEN OTHERS THEN
1200 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1201 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1202 p_msg_name => G_UNEXPECTED_ERROR,
1203 p_token1 => G_SQLCODE_TOKEN,
1204 p_token1_value => SQLCODE,
1205 p_token2 => G_SQLERRM_TOKEN,
1206 p_token2_value => SQLERRM);
1207 RETURN l_return_status;
1208 END;
1209 --------------------------------------------------------------------------
1210 ----- Validate Effectve From
1211 --------------------------------------------------------------------------
1212 FUNCTION validate_effective_from(
1213 p_secAgreement_rec IN secAgreement_rec_type
1214 ,p_mode VARCHAR2
1215 ) RETURN VARCHAR2
1216 IS
1217 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1218 BEGIN
1219
1220 IF (p_mode = 'C') THEN
1221 IF (p_secAgreement_rec.start_date IS NULL) OR
1222 (p_secAgreement_rec.start_date = OKL_API.G_MISS_DATE)
1223 THEN
1224 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1225 p_msg_name => G_REQUIRED_VALUE,
1226 p_token1 => G_COL_NAME_TOKEN,
1227 p_token1_value => 'Effective From');
1228 RAISE G_EXCEPTION_HALT_VALIDATION;
1229 END IF;
1230 END IF;
1231
1232 RETURN l_return_status;
1233 EXCEPTION
1234 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1235 l_return_status := OKL_API.G_RET_STS_ERROR;
1236 RETURN l_return_status;
1237 WHEN OTHERS THEN
1238 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1239 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1240 p_msg_name => G_UNEXPECTED_ERROR,
1241 p_token1 => G_SQLCODE_TOKEN,
1242 p_token1_value => SQLCODE,
1243 p_token2 => G_SQLERRM_TOKEN,
1244 p_token2_value => SQLERRM);
1245
1246 RETURN l_return_status;
1247 END;
1248 --------------------------------------------------------------------------
1249 ----- Validate securitization type
1250 --------------------------------------------------------------------------
1251 FUNCTION validate_securitization_type(
1252 p_secAgreement_rec IN secAgreement_rec_type
1253 ,p_mode VARCHAR2
1254 ) RETURN VARCHAR2
1255 IS
1256 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1257 l_dummy VARCHAR2(10);
1258
1259 l_row_not_found BOOLEAN := FALSE;
1260 l_row_found BOOLEAN := FALSE;
1261
1262 CURSOR c_sec_type IS
1263 SELECT 'x'
1264 FROM fnd_lookups
1265 WHERE lookup_type = 'OKL_SECURITIZATION_TYPE'
1266 AND lookup_code = p_secAgreement_rec.SECURITIZATION_TYPE
1267 ;
1268
1269 BEGIN
1270
1271 IF (p_mode = 'C') THEN
1272 IF (p_secAgreement_rec.securitization_type IS NULL) OR
1273 (p_secAgreement_rec.securitization_type = OKL_API.G_MISS_CHAR)
1274 THEN
1275 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1276 p_msg_name => G_REQUIRED_VALUE,
1277 p_token1 => G_COL_NAME_TOKEN,
1278 p_token1_value => 'Securitization Type');
1279 RAISE G_EXCEPTION_HALT_VALIDATION;
1280 END IF;
1281 END IF;
1282
1283 IF (p_secAgreement_rec.securitization_type IS NOT NULL) AND
1284 (p_secAgreement_rec.securitization_type <> OKL_API.G_MISS_CHAR)
1285 THEN
1286
1287 -- check FK
1288 OPEN c_sec_type;
1289 FETCH c_sec_type INTO l_dummy;
1290 l_row_not_found := c_sec_type%NOTFOUND;
1291 CLOSE c_sec_type;
1292
1293
1294 IF l_row_not_found THEN
1295 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1296 p_msg_name => G_INVALID_VALUE,
1297 p_token1 => G_COL_NAME_TOKEN,
1298 p_token1_value => 'Securitization Type');
1299 RAISE G_EXCEPTION_HALT_VALIDATION;
1300 END IF;
1301 END IF;
1302
1303 RETURN l_return_status;
1304 EXCEPTION
1305 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1306 l_return_status := OKL_API.G_RET_STS_ERROR;
1307 RETURN l_return_status;
1308 WHEN OTHERS THEN
1309 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1310 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1311 p_msg_name => G_UNEXPECTED_ERROR,
1312 p_token1 => G_SQLCODE_TOKEN,
1313 p_token1_value => SQLCODE,
1314 p_token2 => G_SQLERRM_TOKEN,
1315 p_token2_value => SQLERRM);
1316 RETURN l_return_status;
1317 END;
1318
1319 --------------------------------------------------------------------------
1320 ----- Validate Recourse
1321 --------------------------------------------------------------------------
1322 FUNCTION validate_recourse(
1323 p_secAgreement_rec IN secAgreement_rec_type
1324 ,p_mode VARCHAR2
1325 ) RETURN VARCHAR2
1326 IS
1327 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1328 l_dummy VARCHAR2(10);
1329 l_row_not_found BOOLEAN := FALSE;
1330
1331 CURSOR c_rcs IS
1332 SELECT 'x'
1333 FROM fnd_lookups
1334 WHERE lookup_type = 'OKL_SEC_RECOURSE'
1335 ;
1336
1337 BEGIN
1338
1339 IF (p_secAgreement_rec.RECOURSE_CODE IS NOT NULL) AND
1340 (p_secAgreement_rec.RECOURSE_CODE <> OKL_API.G_MISS_CHAR)
1341 THEN
1342
1343 -- check FK
1344 OPEN c_rcs;
1345 FETCH c_rcs INTO l_dummy;
1346 l_row_not_found := c_rcs%NOTFOUND;
1347 CLOSE c_rcs;
1348
1349 IF l_row_not_found THEN
1350 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1351 p_msg_name => G_INVALID_VALUE,
1352 p_token1 => G_COL_NAME_TOKEN,
1353 p_token1_value => 'RECOURSE_CODE');
1354 RAISE G_EXCEPTION_HALT_VALIDATION;
1355 END IF;
1356 END IF;
1357
1358 RETURN l_return_status;
1359 EXCEPTION
1360 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1361 l_return_status := OKL_API.G_RET_STS_ERROR;
1362 RETURN l_return_status;
1363 WHEN OTHERS THEN
1364 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1365 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1366 p_msg_name => G_UNEXPECTED_ERROR,
1367 p_token1 => G_SQLCODE_TOKEN,
1368 p_token1_value => SQLCODE,
1369 p_token2 => G_SQLERRM_TOKEN,
1370 p_token2_value => SQLERRM);
1371 RETURN l_return_status;
1372 END;
1373 --------------------------------------------------------------------------
1374 ----- Validate LESSOR_SERV_ORG_CODE
1375 --------------------------------------------------------------------------
1376 FUNCTION validate_lessor_serv(
1377 p_secAgreement_rec IN secAgreement_rec_type
1378 ,p_mode VARCHAR2
1379 ) RETURN VARCHAR2
1380 IS
1381 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1382 l_dummy VARCHAR2(10);
1383 l_row_not_found BOOLEAN := FALSE;
1384
1385 CURSOR c_serv IS
1386 SELECT 'x'
1387 FROM fnd_lookups
1388 WHERE lookup_type = 'OKL_SEC_SERVICE_ORG'
1389 ;
1390
1391 BEGIN
1392
1393 IF (p_secAgreement_rec.LESSOR_SERV_ORG_CODE IS NOT NULL) AND
1394 (p_secAgreement_rec.LESSOR_SERV_ORG_CODE <> OKL_API.G_MISS_CHAR)
1395 THEN
1396
1397 -- check FK
1398 OPEN c_serv;
1399 FETCH c_serv INTO l_dummy;
1400 l_row_not_found := c_serv%NOTFOUND;
1401 CLOSE c_serv;
1402
1403 IF l_row_not_found THEN
1404 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1405 p_msg_name => G_INVALID_VALUE,
1406 p_token1 => G_COL_NAME_TOKEN,
1407 p_token1_value => 'LESSOR_SERV_ORG_CODE');
1408 RAISE G_EXCEPTION_HALT_VALIDATION;
1409 END IF;
1410 END IF;
1411
1412 RETURN l_return_status;
1413
1414 EXCEPTION
1415 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1416 l_return_status := OKL_API.G_RET_STS_ERROR;
1417 RETURN l_return_status;
1418 WHEN OTHERS THEN
1419 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1420 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1421 p_msg_name => G_UNEXPECTED_ERROR,
1422 p_token1 => G_SQLCODE_TOKEN,
1423 p_token1_value => SQLCODE,
1424 p_token2 => G_SQLERRM_TOKEN,
1425 p_token2_value => SQLERRM);
1426 RETURN l_return_status;
1427 END;
1428
1429 --------------------------------------------------------------------------
1430 FUNCTION validate_header_attributes(
1431 p_secAgreement_rec IN secAgreement_rec_type
1432 ,p_mode VARCHAR2
1433 ) RETURN VARCHAR2
1434 IS
1435 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1436 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1437
1438 BEGIN
1439
1440
1441 -- Do formal attribute validation:
1442 l_return_status := validate_contract_number(p_secAgreement_rec, p_mode);
1443 --- Store the highest degree of error
1444 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1445 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1446 x_return_status := l_return_status;
1447 END IF;
1448 RAISE G_EXCEPTION_HALT_VALIDATION;
1449 END IF;
1450
1451 l_return_status := validate_product(p_secAgreement_rec, p_mode);
1452 --- Store the highest degree of error
1453 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1454 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1455 x_return_status := l_return_status;
1456 END IF;
1457 RAISE G_EXCEPTION_HALT_VALIDATION;
1458 END IF;
1459
1460 l_return_status := validate_pool_number(p_secAgreement_rec, p_mode);
1461 --- Store the highest degree of error
1462 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1463 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1464 x_return_status := l_return_status;
1465 END IF;
1466 RAISE G_EXCEPTION_HALT_VALIDATION;
1467 END IF;
1468
1469 l_return_status := validate_pool_number_unique(p_secAgreement_rec, p_mode);
1470 --- Store the highest degree of error
1471 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1472 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1473 x_return_status := l_return_status;
1474 END IF;
1475 RAISE G_EXCEPTION_HALT_VALIDATION;
1476 END IF;
1477
1478 l_return_status := validate_description(p_secAgreement_rec);
1479 --- Store the highest degree of error
1480 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1481 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1482 x_return_status := l_return_status;
1483 END IF;
1484 RAISE G_EXCEPTION_HALT_VALIDATION;
1485 END IF;
1486
1487 l_return_status := validate_effective_from(p_secAgreement_rec, p_mode);
1488 --- Store the highest degree of error
1489 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1490 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1491 x_return_status := l_return_status;
1492 END IF;
1493 RAISE G_EXCEPTION_HALT_VALIDATION;
1494 END IF;
1495
1496 l_return_status := validate_securitization_type(p_secAgreement_rec, p_mode);
1497 --- Store the highest degree of error
1498 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1499 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1500 x_return_status := l_return_status;
1501 END IF;
1502 RAISE G_EXCEPTION_HALT_VALIDATION;
1503 END IF;
1504
1505 l_return_status := validate_recourse(p_secAgreement_rec, p_mode);
1506 --- Store the highest degree of error
1507 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1508 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1509 x_return_status := l_return_status;
1510 END IF;
1511 RAISE G_EXCEPTION_HALT_VALIDATION;
1512 END IF;
1513
1514 l_return_status := validate_lessor_serv(p_secAgreement_rec, p_mode);
1515 --- Store the highest degree of error
1516
1517 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1518 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1519 x_return_status := l_return_status;
1520 END IF;
1521 RAISE G_EXCEPTION_HALT_VALIDATION;
1522 END IF;
1523
1524 RETURN x_return_status;
1525 EXCEPTION
1526 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1527 RETURN x_return_status;
1528 WHEN OTHERS THEN
1529 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1530 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1531 p_msg_name => G_UNEXPECTED_ERROR,
1532 p_token1 => G_SQLCODE_TOKEN,
1533 p_token1_value => SQLCODE,
1534 p_token2 => G_SQLERRM_TOKEN,
1535 p_token2_value => SQLERRM);
1536 RETURN l_return_status;
1537 END validate_header_attributes;
1538
1539 ----------------------------------------------------------------------------------
1540 -- Start of comments
1541 --
1542 -- Procedure Name : create_sec_agreement
1543 -- Description : creates a securitization agreement
1544 -- Business Rules :
1545 -- Parameters :
1546 -- Version : 1.0
1547 -- End of comments
1548 ----------------------------------------------------------------------------------
1549 PROCEDURE create_sec_agreement(
1550 p_api_version IN NUMBER
1551 ,p_init_msg_list IN VARCHAR2
1552 ,x_return_status OUT NOCOPY VARCHAR2
1553 ,x_msg_count OUT NOCOPY NUMBER
1554 ,x_msg_data OUT NOCOPY VARCHAR2
1555 ,p_secAgreement_rec IN secAgreement_rec_type
1556 ,x_secAgreement_rec OUT NOCOPY secAgreement_rec_type)
1557 IS
1558 l_api_name CONSTANT VARCHAR2(30) := 'create_sec_agreement_pvt';
1559 l_api_version CONSTANT NUMBER := 1.0;
1560 i NUMBER;
1561 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1562 l_qcl_id OKC_QA_CHECK_LISTS_TL.ID%TYPE;
1563 l_row_not_found BOOLEAN := FALSE;
1564
1565 lp_cplv_rec OKC_CONTRACT_PARTY_PUB.cplv_rec_type;
1566 lx_cplv_rec OKC_CONTRACT_PARTY_PUB.cplv_rec_type;
1567
1568 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
1569 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
1570 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
1571 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
1572
1573 l_polv_rec polv_rec_type;
1574 x_polv_rec polv_rec_type;
1575
1576
1577 CURSOR c_qcl IS
1578 SELECT qcl.id
1579 FROM OKC_QA_CHECK_LISTS_TL qcl,
1580 OKC_QA_CHECK_LISTS_B qclv
1581 WHERE qclv.Id = qcl.id
1582 AND UPPER(qcl.name) = 'OKL LA QA INVESTOR AGMNT'
1583 AND qcl.LANGUAGE = 'US'
1584 ;
1585
1586
1587 CURSOR role_csr(p_rle_code VARCHAR2) IS
1588 SELECT access_level
1589 FROM OKC_ROLE_SOURCES
1590 WHERE rle_code = p_rle_code
1591 AND buy_or_sell = 'S';
1592
1593 l_access_level OKC_ROLE_SOURCES.access_level%TYPE;
1594
1595 BEGIN
1596 -- Set API savepoint
1597 SAVEPOINT create_sec_agreement_PVT;
1598
1599 -- Check for call compatibility
1600 IF (NOT FND_API.Compatible_API_Call (l_api_version,
1601 p_api_version,
1602 l_api_name,
1603 G_PKG_NAME ))
1604 THEN
1605 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1606 END IF;
1607
1608 -- Initialize message list if requested
1609 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
1610 FND_MSG_PUB.initialize;
1611 END IF;
1612
1613 -- Initialize API status to success
1614 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1615
1616
1617 /*** Begin API body ****************************************************/
1618
1619 -- get qcl_id for QA checker
1620 OPEN c_qcl;
1621 FETCH c_qcl INTO l_qcl_id;
1622 l_row_not_found := c_qcl%NOTFOUND;
1623 CLOSE c_qcl;
1624
1625 lp_chrv_rec.qcl_id := l_qcl_id;
1626
1627 --
1628 -- creates an agreement
1629 --
1630 lp_chrv_rec.sfwt_flag := 'N';
1631 lp_chrv_rec.object_version_number := 1.0;
1632 lp_chrv_rec.sts_code := G_STS_CODE_NEW; -- 'ENTERED';
1633 lp_chrv_rec.scs_code := G_SCS_CODE;--p_scs_code;
1634 lp_chrv_rec.contract_number := p_secAgreement_rec.CONTRACT_NUMBER;
1635
1636 -- lp_chrv_rec.sts_code := p_secAgreement_rec.STS_CODE;
1637 lp_chrv_rec.description := p_secAgreement_rec.short_description;
1638 lp_chrv_rec.short_description := p_secAgreement_rec.short_description;
1639 lp_chrv_rec.start_date := p_secAgreement_rec.start_date;
1640 -- IF (p_secAgreement_rec.end_date is null or p_secAgreement_rec.end_date = OKL_API.G_MISS_DATE) THEN
1641 -- lp_chrv_rec.end_date := null;
1642 -- ELSE
1643 lp_chrv_rec.end_date := p_secAgreement_rec.end_date;
1644 -- END IF;
1645 lp_chrv_rec.date_approved := p_secAgreement_rec.date_approved;
1646
1647 -- to resolve the validation for sign_by_date
1648 -- lp_chrv_rec.sign_by_date := lp_chrv_rec.end_date;
1649 lp_chrv_rec.sign_by_date := NULL;
1650 lp_chrv_rec.currency_code := p_secAgreement_rec.CURRENCY_CODE;
1651
1652 -- set the OKL context from profiles
1653 OKL_CONTEXT.set_okc_org_context(p_org_id => mo_global.get_current_org_id); --MOAC
1654 lp_chrv_rec.authoring_org_id := OKL_CONTEXT.GET_OKC_ORG_ID;
1655 lp_chrv_rec.inv_organization_id := OKL_CONTEXT.get_okc_organization_id;
1656 -- lp_chrv_rec.inv_organization_id := 204;
1657 -- lp_chrv_rec.currency_code := OKC_CURRENCY_API.GET_OU_CURRENCY(OKL_CONTEXT.GET_OKC_ORG_ID);
1658 lp_chrv_rec.currency_code_renewed := NULL;
1659 lp_chrv_rec.template_yn := 'N';
1660 lp_chrv_rec.chr_type := 'CYA';
1661 lp_chrv_rec.archived_yn := 'N';
1662 lp_chrv_rec.deleted_yn := 'N';
1663 lp_chrv_rec.buy_or_sell := 'S';
1664 lp_chrv_rec.issue_or_receive := 'I';
1665 --
1666 lp_khrv_rec.object_version_number := 1.0;
1667 -- lp_khrv_rec.khr_id := 1;
1668 lp_khrv_rec.generate_accrual_yn := 'Y';
1669 lp_khrv_rec.generate_accrual_override_yn := 'N';
1670 --
1671 lp_khrv_rec.PDT_ID := p_secAgreement_rec.PDT_ID;
1672
1673 -- racheruv .. R12.1.2 .. start
1674 select decode(reporting_pdt_id, NULL, NULL, 'Y')
1675 into lp_khrv_rec.multi_gaap_yn
1676 from okl_products
1677 where id = p_secAgreement_rec.PDT_ID;
1678 -- racheruv .. R12.1.2 end
1679
1680 lp_khrv_rec.SECURITIZATION_TYPE := p_secAgreement_rec.SECURITIZATION_TYPE;
1681 IF (p_secAgreement_rec.LESSOR_SERV_ORG_CODE IS NULL OR
1682 p_secAgreement_rec.LESSOR_SERV_ORG_CODE = OKL_API.G_MISS_CHAR) THEN
1683 lp_khrv_rec.LESSOR_SERV_ORG_CODE := 'O';
1684 ELSE
1685 lp_khrv_rec.LESSOR_SERV_ORG_CODE := p_secAgreement_rec.LESSOR_SERV_ORG_CODE;
1686 END IF;
1687 IF (p_secAgreement_rec.RECOURSE_CODE IS NULL OR p_secAgreement_rec.RECOURSE_CODE = OKL_API.G_MISS_CHAR) THEN
1688 lp_khrv_rec.RECOURSE_CODE := 'N';
1689 ELSE
1690 lp_khrv_rec.RECOURSE_CODE := p_secAgreement_rec.RECOURSE_CODE;
1691 END IF;
1692
1693 lp_khrv_rec.CURRENCY_CONVERSION_TYPE := p_secAgreement_rec.CURRENCY_CONVERSION_TYPE;
1694 lp_khrv_rec.CURRENCY_CONVERSION_RATE := p_secAgreement_rec.CURRENCY_CONVERSION_RATE;
1695 lp_khrv_rec.CURRENCY_CONVERSION_DATE := p_secAgreement_rec.CURRENCY_CONVERSION_DATE;
1696 lp_khrv_rec.AFTER_TAX_YIELD := p_secAgreement_rec.AFTER_TAX_YIELD;
1697
1698 -- arajagop Begin Changes for Attributes (Flexfield Support)
1699 lp_khrv_rec.ATTRIBUTE_CATEGORY := p_secAgreement_rec.ATTRIBUTE_CATEGORY;
1700 lp_khrv_rec.ATTRIBUTE1 := p_secAgreement_rec.ATTRIBUTE1;
1701 lp_khrv_rec.ATTRIBUTE2 := p_secAgreement_rec.ATTRIBUTE2;
1702 lp_khrv_rec.ATTRIBUTE3 := p_secAgreement_rec.ATTRIBUTE3;
1703 lp_khrv_rec.ATTRIBUTE4 := p_secAgreement_rec.ATTRIBUTE4;
1704 lp_khrv_rec.ATTRIBUTE5 := p_secAgreement_rec.ATTRIBUTE5;
1705 lp_khrv_rec.ATTRIBUTE6 := p_secAgreement_rec.ATTRIBUTE6;
1706 lp_khrv_rec.ATTRIBUTE7 := p_secAgreement_rec.ATTRIBUTE7;
1707 lp_khrv_rec.ATTRIBUTE8 := p_secAgreement_rec.ATTRIBUTE8;
1708 lp_khrv_rec.ATTRIBUTE9 := p_secAgreement_rec.ATTRIBUTE9;
1709 lp_khrv_rec.ATTRIBUTE10 := p_secAgreement_rec.ATTRIBUTE10;
1710 lp_khrv_rec.ATTRIBUTE11 := p_secAgreement_rec.ATTRIBUTE11;
1711 lp_khrv_rec.ATTRIBUTE12 := p_secAgreement_rec.ATTRIBUTE12;
1712 lp_khrv_rec.ATTRIBUTE13 := p_secAgreement_rec.ATTRIBUTE13;
1713 lp_khrv_rec.ATTRIBUTE14 := p_secAgreement_rec.ATTRIBUTE14;
1714 lp_khrv_rec.ATTRIBUTE15 := p_secAgreement_rec.ATTRIBUTE15;
1715 -- arajagop End Changes for Attributes (Flexfield Support)
1716 lp_khrv_rec.legal_entity_id := p_secAgreement_rec.legal_entity_id;
1717
1718 --
1719 -- Contract header specific validation
1720 --
1721 x_return_status := validate_header_attributes(p_secAgreement_rec, 'C');
1722 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1723 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1724 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1725 RAISE OKC_API.G_EXCEPTION_ERROR;
1726 END IF;
1727
1728 OKL_CONTRACT_PUB.validate_contract_header(
1729 p_api_version => p_api_version,
1730 p_init_msg_list => p_init_msg_list,
1731 x_return_status => x_return_status,
1732 x_msg_count => x_msg_count,
1733 x_msg_data => x_msg_data,
1734 p_chrv_rec => lp_chrv_rec,
1735 p_khrv_rec => lp_khrv_rec);
1736
1737 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1738 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1739 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1740 RAISE OKC_API.G_EXCEPTION_ERROR;
1741 END IF;
1742
1743 OKL_CONTRACT_PUB.create_contract_header(
1744 p_api_version => p_api_version,
1745 p_init_msg_list => p_init_msg_list,
1746 x_return_status => x_return_status,
1747 x_msg_count => x_msg_count,
1748 x_msg_data => x_msg_data,
1749 p_chrv_rec => lp_chrv_rec,
1750 p_khrv_rec => lp_khrv_rec,
1751 x_chrv_rec => lx_chrv_rec,
1752 x_khrv_rec => lx_khrv_rec);
1753
1754 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1755 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1756 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1757 RAISE OKC_API.G_EXCEPTION_ERROR;
1758 END IF;
1759
1760 x_secAgreement_rec.ID := lx_chrv_rec.id;
1761
1762 --
1763 -- ********************* Lessor ***************************************
1764 --
1765
1766 -- x_chr_id := lx_chrv_rec.id;
1767
1768 -- now we attach the party to the header
1769 lp_cplv_rec.object_version_number := 1.0;
1770 lp_cplv_rec.sfwt_flag := OKC_API.G_FALSE;
1771 lp_cplv_rec.dnz_chr_id := lx_chrv_rec.id;
1772 lp_cplv_rec.chr_id := lx_chrv_rec.id;
1773 lp_cplv_rec.cle_id := NULL;
1774 lp_cplv_rec.object1_id1 := lp_chrv_rec.authoring_org_id;
1775 lp_cplv_rec.object1_id2 := '#';
1776 lp_cplv_rec.jtot_object1_code := G_LESSOR_JTOT_OBJECT1_CODE;
1777 lp_cplv_rec.rle_code := G_LESSOR_RLE_CODE;
1778
1779
1780 OKC_CONTRACT_PARTY_PUB.validate_k_party_role(
1781 p_api_version => p_api_version,
1782 p_init_msg_list => p_init_msg_list,
1783 x_return_status => x_return_status,
1784 x_msg_count => x_msg_count,
1785 x_msg_data => x_msg_data,
1786 p_cplv_rec => lp_cplv_rec);
1787
1788
1789 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1790 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1791 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1792 RAISE OKC_API.G_EXCEPTION_ERROR;
1793 END IF;
1794
1795 ----- Changes by Kanti
1796 ----- Validate the JTOT Object code, ID1 and ID2
1797
1798 OPEN role_csr(lp_cplv_rec.rle_code);
1799 FETCH role_csr INTO l_access_level;
1800 CLOSE role_csr;
1801
1802 IF (l_access_level = 'S') THEN
1803
1804 okl_la_validation_util_pvt.VALIDATE_ROLE_JTOT (p_api_version => p_api_version,
1805 p_init_msg_list => OKC_API.G_FALSE,
1806 x_return_status => x_return_status,
1807 x_msg_count => x_msg_count,
1808 x_msg_data => x_msg_data,
1809 p_object_name => lp_cplv_rec.jtot_object1_code,
1810 p_id1 => lp_cplv_rec.object1_id1,
1811 p_id2 => lp_cplv_rec.object1_id2);
1812 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1813 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1814 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1815 RAISE OKC_API.G_EXCEPTION_ERROR;
1816 END IF;
1817
1818 END IF;
1819
1820 ---- Changes End
1821
1822
1823 OKC_CONTRACT_PARTY_PUB.create_k_party_role(
1824 p_api_version => p_api_version,
1825 p_init_msg_list => p_init_msg_list,
1826
1827 x_return_status => x_return_status,
1828 x_msg_count => x_msg_count,
1829 x_msg_data => x_msg_data,
1830 p_cplv_rec => lp_cplv_rec,
1831 x_cplv_rec => lx_cplv_rec);
1832
1833 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1834 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1835 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1836 RAISE OKC_API.G_EXCEPTION_ERROR;
1837 END IF;
1838
1839 --
1840 -- ************************************************************
1841 --
1842 --
1843 -- ********************* Trustee ***************************************
1844 --
1845
1846 -- x_chr_id := lx_chrv_rec.id;
1847
1848 -- now we attach the party to the header
1849 lp_cplv_rec.object_version_number := 1.0;
1850 lp_cplv_rec.sfwt_flag := OKC_API.G_FALSE;
1851 lp_cplv_rec.dnz_chr_id := lx_chrv_rec.id;
1852 lp_cplv_rec.chr_id := lx_chrv_rec.id;
1853 lp_cplv_rec.cle_id := NULL;
1854 lp_cplv_rec.object1_id1 := p_secAgreement_rec.trustee_object1_id1;
1855 lp_cplv_rec.object1_id2 := p_secAgreement_rec.trustee_object1_id2;
1856 lp_cplv_rec.jtot_object1_code := G_TRUSTEE_JTOT_OBJECT1_CODE;
1857 lp_cplv_rec.rle_code := G_TRUSTEE_RLE_CODE;
1858
1859 OKC_CONTRACT_PARTY_PUB.validate_k_party_role(
1860 p_api_version => p_api_version,
1861 p_init_msg_list => p_init_msg_list,
1862
1863 x_return_status => x_return_status,
1864 x_msg_count => x_msg_count,
1865 x_msg_data => x_msg_data,
1866 p_cplv_rec => lp_cplv_rec);
1867
1868
1869 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1870 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1871 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1872 RAISE OKC_API.G_EXCEPTION_ERROR;
1873 END IF;
1874
1875 ----- Changes by Kanti
1876 ----- Validate the JTOT Object code, ID1 and ID2
1877
1878
1879 OPEN role_csr(lp_cplv_rec.rle_code);
1880 FETCH role_csr INTO l_access_level;
1881 CLOSE role_csr;
1882
1883 IF (l_access_level = 'S') THEN
1884
1885 okl_la_validation_util_pvt.VALIDATE_ROLE_JTOT (p_api_version => p_api_version,
1886 p_init_msg_list => OKC_API.G_FALSE,
1887 x_return_status => x_return_status,
1888 x_msg_count => x_msg_count,
1889 x_msg_data => x_msg_data,
1890 p_object_name => lp_cplv_rec.jtot_object1_code,
1891 p_id1 => lp_cplv_rec.object1_id1,
1892 p_id2 => lp_cplv_rec.object1_id2);
1893 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1894 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1895 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1896 RAISE OKC_API.G_EXCEPTION_ERROR;
1897 END IF;
1898
1899 END IF;
1900
1901 ---- Changes End
1902
1903
1904 OKC_CONTRACT_PARTY_PUB.create_k_party_role(
1905 p_api_version => p_api_version,
1906 p_init_msg_list => p_init_msg_list,
1907 x_return_status => x_return_status,
1908 x_msg_count => x_msg_count,
1909 x_msg_data => x_msg_data,
1910 p_cplv_rec => lp_cplv_rec,
1911 x_cplv_rec => lx_cplv_rec);
1912
1913 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1914 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1915 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1916 RAISE OKC_API.G_EXCEPTION_ERROR;
1917 END IF;
1918
1919 --
1920 -- ************************************************************
1921 --
1922 x_secAgreement_rec.trustee_party_roles_id := lx_cplv_rec.id;
1923
1924 --
1925 -- update okl_pools
1926 --
1927 l_polv_rec.id := p_secAgreement_rec.pol_id;
1928 l_polv_rec.khr_id := x_secAgreement_rec.ID;
1929
1930 OKL_POOL_PVT.update_pool(
1931 p_api_version => p_api_version,
1932 p_init_msg_list => p_init_msg_list,
1933
1934 x_return_status => x_return_status,
1935 x_msg_count => x_msg_count,
1936 x_msg_data => x_msg_data,
1937 p_polv_rec => l_polv_rec,
1938 x_polv_rec => x_polv_rec);
1939
1940 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1941 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1942 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1943 RAISE OKL_API.G_EXCEPTION_ERROR;
1944 END IF;
1945
1946 /*** End API body ******************************************************/
1947
1948 -- Get message count and if count is 1, get message info
1949 FND_MSG_PUB.Count_And_Get
1950 (p_count => x_msg_count,
1951 p_data => x_msg_data);
1952
1953 EXCEPTION
1954 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1955 ROLLBACK TO create_sec_agreement_PVT;
1956 x_return_status := OKL_API.G_RET_STS_ERROR;
1957 FND_MSG_PUB.Count_And_Get
1958 (p_count => x_msg_count,
1959 p_data => x_msg_data);
1960
1961 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1962 ROLLBACK TO create_sec_agreement_PVT;
1963 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1964 FND_MSG_PUB.Count_And_Get
1965 (p_count => x_msg_count,
1966 p_data => x_msg_data);
1967
1968 WHEN OTHERS THEN
1969 ROLLBACK TO create_sec_agreement_PVT;
1970 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1971 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1972 p_msg_name => G_UNEXPECTED_ERROR,
1973 p_token1 => G_SQLCODE_TOKEN,
1974 p_token1_value => SQLCODE,
1975 p_token2 => G_SQLERRM_TOKEN,
1976 p_token2_value => SQLERRM);
1977 FND_MSG_PUB.Count_And_Get
1978 (p_count => x_msg_count,
1979 p_data => x_msg_data);
1980 END;
1981
1982 ----------------------------------------------------------------------------------
1983 -- Start of comments
1984 --
1985 -- Procedure Name : update_sec_agreement
1986 -- Description : updates a securitization agreement
1987 -- Business Rules :
1988 -- Parameters :
1989 -- Version : 1.0
1990 -- End of comments
1991 ----------------------------------------------------------------------------------
1992 PROCEDURE update_sec_agreement(
1993 p_api_version IN NUMBER
1994 ,p_init_msg_list IN VARCHAR2
1995 ,x_return_status OUT NOCOPY VARCHAR2
1996 ,x_msg_count OUT NOCOPY NUMBER
1997 ,x_msg_data OUT NOCOPY VARCHAR2
1998 ,p_secAgreement_rec IN secAgreement_rec_type
1999 ,x_secAgreement_rec OUT NOCOPY secAgreement_rec_type)
2000 IS
2001 l_api_name CONSTANT VARCHAR2(30) := 'update_sec_agreement_pvt';
2002 l_api_version CONSTANT NUMBER := 1.0;
2003 i NUMBER;
2004 l_pol_id OKL_POOLS.ID%TYPE;
2005 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2006 lp_cplv_rec OKC_CONTRACT_PARTY_PUB.cplv_rec_type;
2007 lx_cplv_rec OKC_CONTRACT_PARTY_PUB.cplv_rec_type;
2008
2009 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
2010 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
2011 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
2012 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
2013 l_polv_rec polv_rec_type;
2014 x_polv_rec polv_rec_type;
2015
2016 lp_clev_rec clev_rec_type;
2017 lx_clev_rec clev_rec_type;
2018 lp_klev_rec klev_rec_type;
2019 lx_klev_rec klev_rec_type;
2020 l_kle_id okc_k_lines_b.id%TYPE;
2021
2022 CURSOR c_pool_upd IS
2023 SELECT id
2024 FROM okl_pools
2025 WHERE khr_id = p_secAgreement_rec.id
2026 AND id <> p_secAgreement_rec.pol_id
2027 ;
2028
2029 -- mvasudev, 11/04/2003
2030 /*
2031 -- Replacing this by writing two cursors with top line first
2032 -- and sub lines next
2033 CURSOR c_agr_lns(p_khr_id okc_k_headers_b.id%TYPE) IS
2034 SELECT cle.id
2035 FROM apps.okc_k_lines_b cle
2036 WHERE cle.dnz_chr_id = p_khr_id
2037 ;
2038 */
2039 CURSOR l_okl_top_lines_csr(p_khr_id IN NUMBER)
2040 IS
2041 SELECT clet.id
2042 FROM okc_k_lines_b clet
2043 WHERE clet.dnz_chr_id = p_khr_id
2044 AND clet.cle_id IS NULL;
2045
2046 CURSOR l_okl_sub_lines_csr(p_khr_id IN NUMBER,p_kle_id IN NUMBER)
2047 IS
2048 SELECT cles.id
2049 FROM okc_k_lines_b cles
2050 WHERE cles.dnz_chr_id = p_khr_id
2051 AND cles.cle_id = p_kle_id;
2052 -- end, mvasudev changes, 11/04/2003
2053
2054 CURSOR role_csr(p_rle_code VARCHAR2) IS
2055 SELECT access_level
2056 FROM OKC_ROLE_SOURCES
2057 WHERE rle_code = p_rle_code
2058 AND buy_or_sell = 'S';
2059
2060 l_access_level OKC_ROLE_SOURCES.access_level%TYPE;
2061
2062
2063 BEGIN
2064 -- Set API savepoint
2065 SAVEPOINT update_sec_agreement_PVT;
2066
2067 -- Check for call compatibility
2068 IF (NOT FND_API.Compatible_API_Call (l_api_version,
2069 p_api_version,
2070 l_api_name,
2071 G_PKG_NAME ))
2072 THEN
2073 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2074 END IF;
2075
2076 -- Initialize message list if requested
2077 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
2078 FND_MSG_PUB.initialize;
2079 END IF;
2080
2081 -- Initialize API status to success
2082 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2083
2084
2085 /*** Begin API body ****************************************************/
2086 lp_chrv_rec.id := p_secAgreement_rec.ID;
2087 lp_chrv_rec.contract_number := p_secAgreement_rec.CONTRACT_NUMBER;
2088 lp_chrv_rec.sts_code := p_secAgreement_rec.STS_CODE;
2089 lp_chrv_rec.description := p_secAgreement_rec.short_description;
2090 lp_chrv_rec.short_description := p_secAgreement_rec.short_description;
2091 lp_chrv_rec.start_date := p_secAgreement_rec.start_date;
2092 lp_chrv_rec.end_date := p_secAgreement_rec.end_date;
2093 lp_chrv_rec.date_approved := p_secAgreement_rec.date_approved;
2094
2095 lp_chrv_rec.currency_code := p_secAgreement_rec.CURRENCY_CODE;
2096 lp_khrv_rec.PDT_ID := p_secAgreement_rec.PDT_ID;
2097
2098 -- racheruv .. R12.1.2 .. start
2099 select decode(reporting_pdt_id, NULL, NULL, 'Y')
2100 into lp_khrv_rec.multi_gaap_yn
2101 from okl_products a, okl_k_headers b
2102 where b.pdt_id = a.id
2103 and b.id = lp_chrv_rec.id;
2104 -- racheruv .. R12.1.2 end
2105
2106 lp_khrv_rec.SECURITIZATION_TYPE := p_secAgreement_rec.SECURITIZATION_TYPE;
2107 lp_khrv_rec.LESSOR_SERV_ORG_CODE := p_secAgreement_rec.LESSOR_SERV_ORG_CODE;
2108 lp_khrv_rec.RECOURSE_CODE := p_secAgreement_rec.RECOURSE_CODE;
2109
2110 lp_khrv_rec.CURRENCY_CONVERSION_TYPE := p_secAgreement_rec.CURRENCY_CONVERSION_TYPE;
2111 lp_khrv_rec.CURRENCY_CONVERSION_RATE := p_secAgreement_rec.CURRENCY_CONVERSION_RATE;
2112 lp_khrv_rec.CURRENCY_CONVERSION_DATE := p_secAgreement_rec.CURRENCY_CONVERSION_DATE;
2113 -- added for AFTER_TAX_YIELD akjain,v115.23
2114 lp_khrv_rec.AFTER_TAX_YIELD := p_secAgreement_rec.AFTER_TAX_YIELD;
2115
2116 -- arajagop Begin Changes for Attributes (Flexfield Support)
2117 lp_khrv_rec.ATTRIBUTE_CATEGORY := p_secAgreement_rec.ATTRIBUTE_CATEGORY;
2118 lp_khrv_rec.ATTRIBUTE1 := p_secAgreement_rec.ATTRIBUTE1;
2119 lp_khrv_rec.ATTRIBUTE2 := p_secAgreement_rec.ATTRIBUTE2;
2120 lp_khrv_rec.ATTRIBUTE3 := p_secAgreement_rec.ATTRIBUTE3;
2121 lp_khrv_rec.ATTRIBUTE4 := p_secAgreement_rec.ATTRIBUTE4;
2122 lp_khrv_rec.ATTRIBUTE5 := p_secAgreement_rec.ATTRIBUTE5;
2123 lp_khrv_rec.ATTRIBUTE6 := p_secAgreement_rec.ATTRIBUTE6;
2124 lp_khrv_rec.ATTRIBUTE7 := p_secAgreement_rec.ATTRIBUTE7;
2125 lp_khrv_rec.ATTRIBUTE8 := p_secAgreement_rec.ATTRIBUTE8;
2126 lp_khrv_rec.ATTRIBUTE9 := p_secAgreement_rec.ATTRIBUTE9;
2127 lp_khrv_rec.ATTRIBUTE10 := p_secAgreement_rec.ATTRIBUTE10;
2128 lp_khrv_rec.ATTRIBUTE11 := p_secAgreement_rec.ATTRIBUTE11;
2129 lp_khrv_rec.ATTRIBUTE12 := p_secAgreement_rec.ATTRIBUTE12;
2130 lp_khrv_rec.ATTRIBUTE13 := p_secAgreement_rec.ATTRIBUTE13;
2131 lp_khrv_rec.ATTRIBUTE14 := p_secAgreement_rec.ATTRIBUTE14;
2132 lp_khrv_rec.ATTRIBUTE15 := p_secAgreement_rec.ATTRIBUTE15;
2133 -- arajagop End Changes for Attributes (Flexfield Support)
2134 lp_khrv_rec.legal_entity_id := p_secAgreement_rec.legal_entity_id;
2135
2136 --
2137 -- Contract header specific validation
2138 --
2139 x_return_status := validate_header_attributes(p_secAgreement_rec, 'U');
2140 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2141 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2142 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2143 RAISE OKC_API.G_EXCEPTION_ERROR;
2144 END IF;
2145
2146 OKL_CONTRACT_PUB.update_contract_header(
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_chrv_rec => lp_chrv_rec,
2153 p_khrv_rec => lp_khrv_rec,
2154 x_chrv_rec => lx_chrv_rec,
2155 x_khrv_rec => lx_khrv_rec);
2156
2157 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2158 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2159 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2160 RAISE OKC_API.G_EXCEPTION_ERROR;
2161 END IF;
2162
2163 -- copy back to x_secAgreement_rec
2164
2165 x_secAgreement_rec.id := lx_chrv_rec.ID;
2166 x_secAgreement_rec.contract_number := lx_chrv_rec.CONTRACT_NUMBER;
2167 x_secAgreement_rec.sts_code := lx_chrv_rec.STS_CODE;
2168 x_secAgreement_rec.short_description := lx_chrv_rec.short_description;
2169 x_secAgreement_rec.start_date := lx_chrv_rec.start_date;
2170 x_secAgreement_rec.end_date := lx_chrv_rec.end_date;
2171 x_secAgreement_rec.date_approved := lx_chrv_rec.date_approved;
2172
2173 x_secAgreement_rec.currency_code := lx_chrv_rec.CURRENCY_CODE;
2174
2175 x_secAgreement_rec.PDT_ID := lx_khrv_rec.PDT_ID;
2176 -- x_secAgreement_rec.POL_ID := lx_khrv_rec.POL_ID;
2177 x_secAgreement_rec.SECURITIZATION_TYPE := lx_khrv_rec.SECURITIZATION_TYPE;
2178 x_secAgreement_rec.LESSOR_SERV_ORG_CODE := lx_khrv_rec.LESSOR_SERV_ORG_CODE;
2179 x_secAgreement_rec.RECOURSE_CODE := lx_khrv_rec.RECOURSE_CODE;
2180 x_secAgreement_rec.CURRENCY_CONVERSION_TYPE := lx_khrv_rec.CURRENCY_CONVERSION_TYPE;
2181 x_secAgreement_rec.CURRENCY_CONVERSION_RATE := lx_khrv_rec.CURRENCY_CONVERSION_RATE;
2182 x_secAgreement_rec.CURRENCY_CONVERSION_DATE := lx_khrv_rec.CURRENCY_CONVERSION_DATE;
2183
2184 --
2185 -- ********************* Trustee ***************************************
2186 --
2187 IF (p_secAgreement_rec.trustee_party_roles_id IS NOT NULL
2188 AND p_secAgreement_rec.trustee_party_roles_id <> OKL_API.G_MISS_NUM) THEN
2189
2190 -- x_chr_id := lx_chrv_rec.id;
2191
2192 -- now we attach the party to the header
2193 -- lp_cplv_rec.object_version_number := 1.0;
2194 -- lp_cplv_rec.sfwt_flag := OKC_API.G_FALSE;
2195
2196 -- lp_cplv_rec.dnz_chr_id := lx_chrv_rec.id;
2197 -- lp_cplv_rec.chr_id := lx_chrv_rec.id;
2198 -- lp_cplv_rec.cle_id := null;
2199 lp_cplv_rec.id := p_secAgreement_rec.trustee_party_roles_id;
2200 lp_cplv_rec.object1_id1 := p_secAgreement_rec.trustee_object1_id1;
2201 lp_cplv_rec.object1_id2 := p_secAgreement_rec.trustee_object1_id2;
2202 -- lp_cplv_rec.jtot_object1_code := G_TRUSTEE_JTOT_OBJECT1_CODE;
2203 -- lp_cplv_rec.rle_code := G_TRUSTEE_RLE_CODE;
2204
2205 OKC_CONTRACT_PARTY_PUB.validate_k_party_role(
2206 p_api_version => p_api_version,
2207 p_init_msg_list => p_init_msg_list,
2208 x_return_status => x_return_status,
2209 x_msg_count => x_msg_count,
2210 x_msg_data => x_msg_data,
2211 p_cplv_rec => lp_cplv_rec);
2212
2213
2214 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2215 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2216 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2217 RAISE OKC_API.G_EXCEPTION_ERROR;
2218 END IF;
2219
2220 ----- Changes by Kanti
2221 ----- Validate the JTOT Object code, ID1 and ID2
2222
2223 OPEN role_csr(lp_cplv_rec.rle_code);
2224 FETCH role_csr INTO l_access_level;
2225 CLOSE role_csr;
2226
2227 IF (l_access_level = 'S') THEN
2228
2229 okl_la_validation_util_pvt.VALIDATE_ROLE_JTOT (p_api_version => p_api_version,
2230 p_init_msg_list => OKC_API.G_FALSE,
2231 x_return_status => x_return_status,
2232 x_msg_count => x_msg_count,
2233 x_msg_data => x_msg_data,
2234 p_object_name => lp_cplv_rec.jtot_object1_code,
2235 p_id1 => lp_cplv_rec.object1_id1,
2236 p_id2 => lp_cplv_rec.object1_id2);
2237 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2238 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2239 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2240 RAISE OKC_API.G_EXCEPTION_ERROR;
2241 END IF;
2242
2243 END IF;
2244
2245 ---- Changes End
2246
2247 OKC_CONTRACT_PARTY_PUB.update_k_party_role(
2248 p_api_version => p_api_version,
2249 p_init_msg_list => p_init_msg_list,
2250 x_return_status => x_return_status,
2251 x_msg_count => x_msg_count,
2252 x_msg_data => x_msg_data,
2253 p_cplv_rec => lp_cplv_rec,
2254 x_cplv_rec => lx_cplv_rec);
2255
2256 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2257 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2258 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2259 RAISE OKC_API.G_EXCEPTION_ERROR;
2260 END IF;
2261
2262 -- copy back to x_secAgreement_rec
2263 x_secAgreement_rec.trustee_party_roles_id := lx_cplv_rec.id;
2264 x_secAgreement_rec.trustee_object1_id1 := lx_cplv_rec.object1_id1;
2265 x_secAgreement_rec.trustee_object1_id2 := lx_cplv_rec.object1_id2;
2266 x_secAgreement_rec.trustee_jtot_object1_code := lx_cplv_rec.jtot_object1_code;
2267
2268 END IF;
2269
2270 --
2271 -- ************************************************************
2272 --
2273
2274 --
2275 -- update okl_pools
2276 --
2277
2278 IF (p_secAgreement_rec.pol_id IS NOT NULL AND p_secAgreement_rec.pol_id <> OKL_API.G_MISS_NUM) THEN
2279 -- 1. update khr_id to null if user switch to different pol_id
2280
2281 OPEN c_pool_upd;
2282 i := 0;
2283 LOOP
2284 FETCH c_pool_upd INTO
2285 l_pol_id;
2286 EXIT WHEN c_pool_upd%NOTFOUND;
2287
2288 l_polv_rec.id := l_pol_id;
2289 l_polv_rec.khr_id := NULL;
2290
2291 OKL_POOL_PVT.update_pool(
2292 p_api_version => p_api_version,
2293 p_init_msg_list => p_init_msg_list,
2294 x_return_status => x_return_status,
2295 x_msg_count => x_msg_count,
2296 x_msg_data => x_msg_data,
2297 p_polv_rec => l_polv_rec,
2298 x_polv_rec => x_polv_rec);
2299
2300
2301 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2302 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2303 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2304 RAISE OKL_API.G_EXCEPTION_ERROR;
2305 END IF;
2306
2307 i := i + 1;
2308 END LOOP;
2309 CLOSE c_pool_upd;
2310
2311 -- 2. update khr_id to a specific pool header
2312
2313 l_polv_rec.id := p_secAgreement_rec.pol_id;
2314 l_polv_rec.khr_id := p_secAgreement_rec.ID;
2315
2316 OKL_POOL_PVT.update_pool(
2317 p_api_version => p_api_version,
2318 p_init_msg_list => p_init_msg_list,
2319 x_return_status => x_return_status,
2320 x_msg_count => x_msg_count,
2321 x_msg_data => x_msg_data,
2322 p_polv_rec => l_polv_rec,
2323 x_polv_rec => x_polv_rec);
2324
2325 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2326 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2327 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2328 RAISE OKL_API.G_EXCEPTION_ERROR;
2329 END IF;
2330
2331 -- copy back to x_secAgreement_rec
2332 x_secAgreement_rec.POL_ID := x_polv_rec.ID;
2333
2334 END IF;
2335 ----------------------------------------------------------------------------
2336 -- update agreement contract header lines end_date = header.end_date
2337 -- cascade update all associated line end_date = header.end_date
2338 -- loop
2339 ----------------------------------------------------------------------------
2340 -- mvasudev, commented , 11/04/2003
2341 -- Replacing this by writing two cursors with top line first
2342 -- and sub lines next
2343 /*
2344 OPEN c_agr_lns(p_secAgreement_rec.ID);
2345 i := 0;
2346 LOOP
2347 FETCH c_agr_lns INTO
2348 l_kle_id;
2349 EXIT WHEN c_agr_lns%NOTFOUND;
2350
2351
2352 lp_klev_rec.id := l_kle_id;
2353 lp_clev_rec.start_date := p_secAgreement_rec.start_date;
2354 lp_clev_rec.end_date := p_secAgreement_rec.end_date;
2355 lp_clev_rec.id := l_kle_id;
2356
2357
2358 okl_contract_pub.update_contract_line(
2359 p_api_version => p_api_version,
2360 p_init_msg_list => p_init_msg_list,
2361 x_return_status => x_return_status,
2362 x_msg_count => x_msg_count,
2363 x_msg_data => x_msg_data,
2364 p_clev_rec => lp_clev_rec,
2365 p_klev_rec => lp_klev_rec,
2366 x_clev_rec => lx_clev_rec,
2367 x_klev_rec => lx_klev_rec);
2368
2369 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2370 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2371 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2372 RAISE OKL_API.G_EXCEPTION_ERROR;
2373 END IF;
2374
2375 i := i + 1;
2376 END LOOP;
2377 CLOSE c_agr_lns;
2378 */
2379
2380 FOR l_okl_top_line_rec IN l_okl_top_lines_csr(p_secAgreement_rec.ID)
2381 LOOP
2382 -- Update Top Lines first
2383 lp_klev_rec.id := l_okl_top_line_rec.id;
2384 lp_clev_rec.start_date := p_secAgreement_rec.start_date;
2385 lp_clev_rec.end_date := p_secAgreement_rec.end_date;
2386 lp_clev_rec.id := l_okl_top_line_rec.id;
2387
2388
2389 okl_contract_pub.update_contract_line(
2390 p_api_version => p_api_version,
2391 p_init_msg_list => p_init_msg_list,
2392 x_return_status => x_return_status,
2393 x_msg_count => x_msg_count,
2394 x_msg_data => x_msg_data,
2395 p_clev_rec => lp_clev_rec,
2396 p_klev_rec => lp_klev_rec,
2397 x_clev_rec => lx_clev_rec,
2398 x_klev_rec => lx_klev_rec);
2399
2400 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2401 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2402 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2403 RAISE OKL_API.G_EXCEPTION_ERROR;
2404 END IF;
2405
2406 FOR l_okl_sub_line_rec IN l_okl_sub_lines_csr(p_secAgreement_rec.ID,l_okl_top_line_rec.id)
2407 LOOP
2408 -- Update Sub Lines next
2409 lp_klev_rec.id := l_okl_sub_line_rec.id;
2410 lp_clev_rec.start_date := p_secAgreement_rec.start_date;
2411 lp_clev_rec.end_date := p_secAgreement_rec.end_date;
2412 lp_clev_rec.id := l_okl_sub_line_rec.id;
2413
2414
2415 okl_contract_pub.update_contract_line(
2416 p_api_version => p_api_version,
2417 p_init_msg_list => p_init_msg_list,
2418 x_return_status => x_return_status,
2419 x_msg_count => x_msg_count,
2420 x_msg_data => x_msg_data,
2421 p_clev_rec => lp_clev_rec,
2422 p_klev_rec => lp_klev_rec,
2423 x_clev_rec => lx_clev_rec,
2424 x_klev_rec => lx_klev_rec);
2425
2426 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2427 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2428 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2429 RAISE OKL_API.G_EXCEPTION_ERROR;
2430 END IF;
2431
2432 END LOOP;
2433 END LOOP;
2434 -- end, mvasudev , changes , 11/04/2003
2435
2436 /*** End API body ******************************************************/
2437
2438 -- Get message count and if count is 1, get message info
2439 FND_MSG_PUB.Count_And_Get
2440 (p_count => x_msg_count,
2441 p_data => x_msg_data);
2442
2443 EXCEPTION
2444 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2445 ROLLBACK TO update_sec_agreement_PVT;
2446 x_return_status := OKL_API.G_RET_STS_ERROR;
2447
2448 FND_MSG_PUB.Count_And_Get
2449 (p_count => x_msg_count,
2450 p_data => x_msg_data);
2451
2452 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2453 ROLLBACK TO update_sec_agreement_PVT;
2454 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2455 FND_MSG_PUB.Count_And_Get
2456 (p_count => x_msg_count,
2457 p_data => x_msg_data);
2458
2459 WHEN OTHERS THEN
2460 ROLLBACK TO update_sec_agreement_PVT;
2461 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2462 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2463 p_msg_name => G_UNEXPECTED_ERROR,
2464 p_token1 => G_SQLCODE_TOKEN,
2465 p_token1_value => SQLCODE,
2466 p_token2 => G_SQLERRM_TOKEN,
2467 p_token2_value => SQLERRM);
2468 FND_MSG_PUB.Count_And_Get
2469 (p_count => x_msg_count,
2470 p_data => x_msg_data);
2471
2472 END;
2473
2474
2475 ----------------------------------------------------------------------------------
2476 -- Start of comments
2477 --
2478 -- Procedure Name : activate_sec_agreement
2479 -- Description : activate a securitization agreement
2480 -- Business Rules :
2481 -- Parameters :
2482 -- Version : 1.0
2483 -- End of comments
2484 ----------------------------------------------------------------------------------
2485 PROCEDURE activate_sec_agreement(
2486 p_api_version IN NUMBER
2487 ,p_init_msg_list IN VARCHAR2
2488 ,x_return_status OUT NOCOPY VARCHAR2
2489 ,x_msg_count OUT NOCOPY NUMBER
2490 ,x_msg_data OUT NOCOPY VARCHAR2
2491 ,p_khr_id IN OKC_K_HEADERS_B.ID%TYPE)
2492 IS
2493 l_api_name CONSTANT VARCHAR2(30) := 'activate_sec_agreement_pvt';
2494 l_api_version CONSTANT NUMBER := 1.0;
2495 i NUMBER;
2496 l_kle_id OKC_K_LINES_B.ID%TYPE;
2497 l_pol_id OKL_POOLS.ID%TYPE;
2498 l_currency_code OKL_POOLS.CURRENCY_CODE%TYPE;
2499 l_org_id OKL_POOLS.ORG_ID%TYPE;
2500 l_legal_entity_id OKL_POOLS.LEGAL_ENTITY_ID%TYPE;
2501 l_chr_id OKC_K_HEADERS_B.ID%TYPE;
2502
2503 l_pox_id OKL_POOL_CONTENTS.POX_ID%TYPE;
2504 l_poc_id OKL_POOL_CONTENTS.ID%TYPE;
2505 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2506 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
2507 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
2508 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
2509 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
2510
2511
2512
2513 lp_secagreement_rec secagreement_rec_type;
2514 lx_secagreement_rec secagreement_rec_type;
2515 l_row_found BOOLEAN := FALSE;
2516 l_row_not_found BOOLEAN := FALSE;
2517 lp_poxv_rec poxv_rec_type;
2518 lx_poxv_rec poxv_rec_type;
2519 lp_pocv_rec pocv_rec_type;
2520 lx_pocv_rec pocv_rec_type;
2521 lp_pocv2_rec pocv_rec_type;
2522 lx_pocv2_rec pocv_rec_type;
2523
2524
2525 CURSOR c_pool IS
2526 SELECT pol.id,
2527 pol.currency_code,
2528 pol.org_id,
2529 pol.legal_entity_id
2530 FROM okl_pools pol
2531 WHERE pol.khr_id = p_khr_id
2532 ;
2533
2534 CURSOR c_poc IS
2535 SELECT poc.id
2536 FROM okl_pool_contents poc
2537 WHERE poc.pol_id = l_pol_id
2538 ;
2539
2540 -- mvasudev, Fixed bug#3987171
2541 /*
2542 CURSOR c_pool_chr IS
2543 SELECT poc.khr_id
2544 FROM apps.okl_pool_contents poc
2545 WHERE poc.pol_id = l_pol_id
2546 ;
2547 */
2548 CURSOR c_pool_chr IS
2549 SELECT poc.khr_id
2550 FROM okl_pool_contents poc
2551 WHERE poc.pol_id = l_pol_id
2552 ;
2553
2554 /* ankushar Bug# 6773285 Added Principal Payment for Loan contracts also renamed Cursor
2555 Start Changes
2556 */
2557 -- mvasudev, 10/29/2003
2558 -- Cursor to check if sty is securitized ">=" effective_date
2559 CURSOR l_okl_sty_csr IS
2560 SELECT 1
2561 FROM okl_pools polb
2562 ,okl_strm_type_b styb
2563 ,okl_pool_contents pocb
2564 WHERE polb.khr_id = p_khr_id
2565 AND pocb.pol_id = polb.id
2566 AND pocb.sty_id = styb.id
2567 AND styb.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
2568 AND pocb.status_code='NEW';
2569 /* ankushar Bug# 6773285
2570 End Changes
2571 */
2572
2573 BEGIN
2574 -- Set API savepoint
2575 SAVEPOINT activate_sec_agreement_PVT;
2576
2577 -- Check for call compatibility
2578 IF (NOT FND_API.Compatible_API_Call (l_api_version,
2579 p_api_version,
2580 l_api_name,
2581 G_PKG_NAME ))
2582 THEN
2583 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2584 END IF;
2585
2586 -- Initialize message list if requested
2587 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
2588 FND_MSG_PUB.initialize;
2589 END IF;
2590
2591 -- Initialize API status to success
2592 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2593
2594 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2595 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Begin Activating IA');
2596 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug','p_khr_id:'|| p_khr_id);
2597 END IF;
2598
2599 /*** Begin API body ****************************************************/
2600 /* process steps
2601 --1 create a transaction for entire pool by pol_id
2602 --2 update pool contents to point to this transaction
2603 --3 Mark associated contract to securitizated
2604 --4 call Stream Generator API to generate streams for income and expense fees
2605 --5 call Streams Generator API to generate disbursement basis streams
2606 --6 call Streams Generator API to generate PV Streams of Securitized Streams
2607 --7 call Accrual API
2608 --8 call BPD AR api
2609 --9 call generate_journal_entries
2610 --10 call update_sec_agreement_sts to update agreement header, lines
2611 --11 update pool header and contents status to active
2612 */
2613
2614 ----------------------------------------------------------------------------
2615 --1 create a transaction for entire pool by pol_id
2616 -- initial transaction when pool become active
2617 --OKL_POOL_TRANSACTION_TYPE
2618 ----------------------------------------------------------------------------
2619 --dbms_output.put_line('1. CREATE a TRANSACTION FOR entire pool BY pol_id');
2620 --dbms_output.put_line('OKL_POOL_PVT.create_pool_transaction START');
2621 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2622 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '1. CREATE a TRANSACTION FOR entire pool BY pol_id: START');
2623 END IF;
2624 OPEN c_pool;
2625 FETCH c_pool INTO l_pol_id,
2626 l_currency_code,
2627 l_org_id,
2628 l_legal_entity_id;
2629 l_row_found := c_pool%FOUND;
2630 CLOSE c_pool;
2631 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2632 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'l_pol_id :' || l_pol_id);
2633 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'l_currency_code :' || l_currency_code);
2634 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'l_org_id :' || l_org_id);
2635 END IF;
2636
2637 --dbms_output.put_line('l_pol_id' || l_pol_id);
2638 --dbms_output.put_line('l_currency_code'|| l_currency_code);
2639 --dbms_output.put_line('l_org_id'|| l_org_id);
2640
2641 IF l_row_found THEN
2642
2643 lp_poxv_rec.POL_ID := l_pol_id;
2644 lp_poxv_rec.TRANSACTION_DATE := SYSDATE;
2645 lp_poxv_rec.TRANSACTION_TYPE := G_POOL_TRX_ADD;
2646 lp_poxv_rec.TRANSACTION_REASON := G_POOL_TRX_REASON_ACTIVE;
2647 lp_poxv_rec.CURRENCY_CODE := l_currency_code;
2648 --sosharma 03/12/2007 added to enable status on pool transaction
2649 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_COMPLETE;
2650 --added abhsaxen for Legal Entity Uptake
2651 lp_poxv_rec.LEGAL_ENTITY_ID := l_legal_entity_id;
2652 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2653 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Calling okl_pool_pvt.create_pool_transaction');
2654 END IF;
2655
2656 OKL_POOL_PVT.create_pool_transaction(
2657 p_api_version => p_api_version,
2658 p_init_msg_list => p_init_msg_list,
2659 x_return_status => x_return_status,
2660 x_msg_count => x_msg_count,
2661 x_msg_data => x_msg_data,
2662 p_poxv_rec => lp_poxv_rec,
2663 x_poxv_rec => lx_poxv_rec);
2664 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2665 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'l_pol_id :' || l_pol_id);
2666 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'OKL_POOL_PVT.create_pool_transaction x_return_status :' || x_return_status);
2667 END IF;
2668
2669 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2670 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2671 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2672 RAISE OKL_API.G_EXCEPTION_ERROR;
2673 END IF;
2674 END IF;
2675 --dbms_output.put_line('OKL_POOL_PVT.create_pool_transaction END');
2676 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2677 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'OKL_POOL_PVT.create_pool_transaction: END');
2678 END IF;
2679
2680 ----------------------------------------------------------------------------
2681 -- get pox_id after create trx entry
2682 --2. update pool contents to point to this transaction
2683 -- make association between pool transaction and pool contents' records
2684 ----------------------------------------------------------------------------
2685 -- loop
2686 --dbms_output.put_line('2. update pool contents to point to this transaction');
2687 --dbms_output.put_line('OKL_POOL_PVT.update_pool_contents Start');
2688 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2689 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '2. update pool contents to point to this transaction');
2690 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Calling OKL_POOL_PVT.update_pool_contents: START');
2691 END IF;
2692
2693 OPEN c_poc;
2694 LOOP
2695 FETCH c_poc INTO
2696 l_poc_id;
2697 EXIT WHEN c_poc%NOTFOUND;
2698
2699 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2700 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'l_pol_id :' || l_pol_id);
2701 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'l_poc_id :' || l_poc_id);
2702 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'lx_poxv_rec.id :' || lx_poxv_rec.id);
2703 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'lx_poxv_rec.TRANSACTION_NUMBER' ||lx_poxv_rec.TRANSACTION_NUMBER);
2704 END IF;
2705
2706 lp_pocv_rec.ID := l_poc_id;
2707 lp_pocv_rec.POL_ID := l_pol_id;
2708 lp_pocv_rec.POX_ID := lx_poxv_rec.id;
2709 lp_pocv_rec.TRANSACTION_NUMBER_IN := lx_poxv_rec.TRANSACTION_NUMBER;
2710
2711 OKL_POOL_PVT.update_pool_contents(
2712 p_api_version => p_api_version,
2713 p_init_msg_list => p_init_msg_list,
2714 x_return_status => x_return_status,
2715 x_msg_count => x_msg_count,
2716 x_msg_data => x_msg_data,
2717 p_pocv_rec => lp_pocv_rec,
2718 x_pocv_rec => lx_pocv_rec);
2719
2720 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2721 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'l_poc_id :' || l_poc_id);
2722 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'OKL_POOL_PVT.update_pool_contents x_return_status :' || x_return_status);
2723 END IF;
2724
2725 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2726 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2727 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2728 RAISE OKL_API.G_EXCEPTION_ERROR;
2729 END IF;
2730
2731 END LOOP;
2732 CLOSE c_poc;
2733 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2734 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'OKL_POOL_PVT.update_pool_contents : END');
2735 END IF;
2736
2737 --dbms_output.put_line('OKL_POOL_PVT.update_pool_contents End');
2738
2739 ----------------------------------------------------------------------------
2740 --3 Mark associated contract to securitizated
2741 ----------------------------------------------------------------------------
2742 --dbms_output.put_line('3. Mark associated contract to securtizated');
2743 --dbms_output.put_line('okl_contract_pub.update_contract_header start');
2744 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2745 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '3. Mark associated contract to securtizated.');
2746 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Calling okl_contract_pub.update_contract_header: START');
2747 END IF;
2748
2749
2750 OPEN c_pool_chr;
2751 LOOP
2752 FETCH c_pool_chr INTO
2753 l_chr_id;
2754 EXIT WHEN c_pool_chr%NOTFOUND;
2755
2756 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2757 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'l_chr_id :' || l_chr_id);
2758 END IF;
2759
2760 lp_chrv_rec.id := l_chr_id;
2761 lp_khrv_rec.id := l_chr_id;
2762 lp_khrv_rec.SECURITIZED_CODE := G_SECURITIZED_CODE_Y;
2763
2764 okl_contract_pub.update_contract_header(
2765 p_api_version => p_api_version,
2766 p_init_msg_list => p_init_msg_list,
2767 x_return_status => x_return_status,
2768 x_msg_count => x_msg_count,
2769 x_msg_data => x_msg_data,
2770 p_chrv_rec => lp_chrv_rec,
2771 p_khrv_rec => lp_khrv_rec,
2772 x_chrv_rec => lx_chrv_rec,
2773 x_khrv_rec => lx_khrv_rec);
2774
2775 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2776 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'okl_contract_pub.update_contract_header x_return_status :' || x_return_status);
2777 END IF;
2778
2779 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2780 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2781 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2782 RAISE OKL_API.G_EXCEPTION_ERROR;
2783 END IF;
2784
2785 END LOOP;
2786 CLOSE c_pool_chr;
2787 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2788 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'okl_contract_pub.update_contract_header: END');
2789 END IF;
2790
2791 -- gboomina added for Bug 6763287 - Start
2792 ----------------------------------------------------------------------------
2793 --4 call Streams Generator API to generate streams for income and expense fees
2794 ----------------------------------------------------------------------------
2795 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2796 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '4 call Streams Generator API to generate streams for income and expense fees: START');
2797 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Calling okl_stream_generator_pvt.generate_streams_for_IA');
2798 END IF;
2799 OKL_STREAM_GENERATOR_PVT.generate_streams_for_IA(
2800 p_api_version => p_api_version,
2801 p_init_msg_list => p_init_msg_list,
2802 p_khr_id => p_khr_id,
2803 x_return_status => x_return_status,
2804 x_msg_count => x_msg_count ,
2805 x_msg_data => x_msg_data );
2806
2807 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2808 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'okl_stream_generator_pvt.generate_streams_for_IA x_return_status :' || x_return_status);
2809 END IF;
2810
2811 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2812 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2813 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2814 RAISE OKL_API.G_EXCEPTION_ERROR;
2815 END IF;
2816 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2817 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '4 call Streams Generator API to generate streams for income and expense fees: END');
2818 END IF;
2819 -- gboomina added for Bug 6763287 - End
2820 -- added by zrehman for making entry into okl_k_control Bug#6788005 on 07-Feb-2008 start
2821 OKL_BILLING_CONTROLLER_PVT.track_next_bill_date(p_khr_id);
2822 -- added by zrehman for making entry into okl_k_control Bug#6788005 on 07-Feb-2008 end
2823 ----------------------------------------------------------------------------
2824
2825 --5 call Streams Generator API to generate disbursement basis streams
2826 --
2827 ----------------------------------------------------------------------------
2828 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2829 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '5 call Streams Generator API to generate disbursement basis streams.');
2830 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Calling okl_stream_generator_pvt.create_disb_streams: START');
2831 END IF;
2832
2833 OKL_STREAM_GENERATOR_PVT.create_disb_streams(
2834 p_api_version => p_api_version,
2835 p_init_msg_list => p_init_msg_list,
2836 p_agreement_id => p_khr_id,
2837 x_return_status => x_return_status,
2838 x_msg_count => x_msg_count ,
2839 x_msg_data => x_msg_data );
2840 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2841 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'okl_stream_generator_pvt.create_disb_streams x_return_status :' || x_return_status);
2842 END IF;
2843
2844 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2845 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2846 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2847 RAISE OKL_API.G_EXCEPTION_ERROR;
2848 END IF;
2849 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2850 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '5 call Streams Generator API to generate disbursement basis streams: END');
2851 END IF;
2852
2853 ----------------------------------------------------------------------------
2854
2855 --6 call Streams Generator API to generate PV Streams of Securitized Streams
2856 --
2857 ----------------------------------------------------------------------------
2858 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2859 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '6 call Streams Generator API to generate PV Streams of Securitized Streams: START');
2860 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Calling okl_stream_generator_pvt.create_pv_streams');
2861 END IF;
2862
2863 OKL_STREAM_GENERATOR_PVT.create_pv_streams(
2864 p_api_version => p_api_version,
2865 p_init_msg_list => p_init_msg_list,
2866 p_agreement_id => p_khr_id,
2867 x_return_status => x_return_status,
2868 x_msg_count => x_msg_count ,
2869 x_msg_data => x_msg_data );
2870 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2871 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'okl_stream_generator_pvt.create_pv_streams x_return_status :' || x_return_status);
2872 END IF;
2873
2874 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2875 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2876 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2877 RAISE OKL_API.G_EXCEPTION_ERROR;
2878 END IF;
2879 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2880 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '6 call Streams Generator API to generate PV Streams of Securitized Streams: END');
2881 END IF;
2882
2883
2884 ----------------------------------------------------------------------------
2885 --7 call Accrual API
2886 --
2887 ----------------------------------------------------------------------------
2888 --dbms_output.put_line('OKL_SECURITIZE_ACCRUAL_PVT.CREATE_STREAMS begin');
2889 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2890 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '7 call to the Accrual API: START');
2891 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Calling okl_accrual_sec_pvt.create_streams');
2892 END IF;
2893
2894
2895 -- OKL_SECURITIZE_ACCRUAL_PVT.CREATE_STREAMS(
2896
2897 -- mvasudev, 10/29/2003
2898 -- Call the Accrual API only when there are RENT streams
2899 -- This should loop EXACTLY one time
2900 FOR l_okl_sty_rent_rec IN l_okl_sty_csr
2901 LOOP
2902 OKL_ACCRUAL_SEC_PVT.CREATE_STREAMS(
2903 p_api_version => p_api_version,
2904 p_init_msg_list => p_init_msg_list,
2905 x_return_status => x_return_status,
2906 x_msg_count => x_msg_count,
2907 x_msg_data => x_msg_data,
2908 p_khr_id => p_khr_id);
2909
2910 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2911 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'okl_stream_generator_pvt.create_pv_streams p_khr_id:' || p_khr_id);
2912 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'okl_stream_generator_pvt.create_pv_streams x_return_status :' || x_return_status);
2913 END IF;
2914
2915 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2916 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2917 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2918 RAISE OKL_API.G_EXCEPTION_ERROR;
2919 END IF;
2920 -- Fixed Bug#3386816, mvasudev
2921 EXIT WHEN l_okl_sty_csr%FOUND;
2922 END LOOP;
2923 -- mvasudev, end, 10/29/2003
2924 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2925 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '7 call to the Accrual API: END');
2926 END IF;
2927
2928 --dbms_output.put_line('OKL_SECURITIZE_ACCRUAL_PVT.CREATE_STREAMS end');
2929 ----------------------------------------------------------------------------
2930 --8 call BPD AR api
2931 --
2932 ----------------------------------------------------------------------------
2933 --dbms_output.put_line('Okl_Investor_Billing_Pvt.create_investor_bill begin');
2934 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2935 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '8 call BPD Billing API: START');
2936 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Calling Okl_Investor_Billing_Pvt.create_investor_bill');
2937 END IF;
2938
2939 Okl_Investor_Billing_Pvt.create_investor_bill(
2940 p_api_version => p_api_version,
2941 p_init_msg_list => p_init_msg_list,
2942 x_return_status => x_return_status,
2943 x_msg_count => x_msg_count,
2944 x_msg_data => x_msg_data,
2945 p_inv_agr => p_khr_id);
2946
2947 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2948 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Okl_Investor_Billing_Pvt.create_investor_bill p_khr_id:' || p_khr_id);
2949 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Okl_Investor_Billing_Pvt.create_investor_bill x_return_status :' || x_return_status);
2950 END IF;
2951
2952 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2953 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2954 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2955 RAISE OKL_API.G_EXCEPTION_ERROR;
2956 END IF;
2957
2958 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2959 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '8 call BPD Billing API: END');
2960 END IF;
2961
2962 --dbms_output.put_line('Okl_Investor_Billing_Pvt.create_investor_bill end');
2963
2964 ----------------------------------------------------------------------------
2965 --9 call generate_journal_entries
2966 --
2967 ----------------------------------------------------------------------------
2968 --dbms_output.put_line('generate_journal_entries begin');
2969 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2970 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '9 call to the generate_journal_entries: START');
2971 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Calling generate_journal_entries');
2972 END IF;
2973
2974 generate_journal_entries(
2975 p_api_version => p_api_version,
2976 p_init_msg_list => p_init_msg_list,
2977 x_return_status => x_return_status,
2978 x_msg_count => x_msg_count,
2979 x_msg_data => x_msg_data,
2980 p_contract_id => p_khr_id
2981 ,p_transaction_type => G_TRY_TYPE_INV);
2982
2983 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2984 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Okl_Investor_Billing_Pvt.create_investor_bill x_return_status :' || x_return_status);
2985 END IF;
2986
2987 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2988 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2989 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2990 RAISE OKL_API.G_EXCEPTION_ERROR;
2991 END IF;
2992
2993 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2994 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '9 call to the generate_journal_entries: END');
2995 END IF;
2996
2997 --dbms_output.put_line('generate_journal_entries end');
2998 ----------------------------------------------------------------------------
2999 --10 call update_sec_agreement_sts to update agreement header, lines
3000 --
3001 ----------------------------------------------------------------------------
3002 --dbms_output.put_line('OKL_SEC_AGREEMENT_PVT.update_sec_agreement_sts begin');
3003
3004 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3005 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '10 call update_sec_agreement_sts to update agreement header, lines: START');
3006 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Calling Okl_Sec_Agreement_Pvt.update_sec_agreement_sts');
3007 END IF;
3008
3009 Okl_Sec_Agreement_Pvt.update_sec_agreement_sts(
3010 p_api_version => p_api_version,
3011 p_init_msg_list => p_init_msg_list,
3012 x_return_status => x_return_status,
3013 x_msg_count => x_msg_count,
3014 x_msg_data => x_msg_data,
3015 p_sec_agreement_status => G_STS_CODE_ACTIVE,
3016 p_sec_agreement_id => p_khr_id);
3017
3018 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3019 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Okl_Sec_Agreement_Pvt.update_sec_agreement_sts x_return_status :' || x_return_status);
3020 END IF;
3021
3022 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3023 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3024 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3025 RAISE OKL_API.G_EXCEPTION_ERROR;
3026 END IF;
3027
3028 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3029 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '10 call update_sec_agreement_sts to update agreement header, lines: END');
3030 END IF;
3031 --dbms_output.put_line('OKL_SEC_AGREEMENT_PVT.update_sec_agreement_sts end');
3032
3033 ----------------------------------------------------------------------------
3034 --11 update pool header and contents status to active
3035 ----------------------------------------------------------------------------
3036 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3037 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '11 update pool header and contents status to active: START');
3038 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Calling OKL_POOL_PVT.update_pool_status_active');
3039 END IF;
3040
3041 OKL_POOL_PVT.update_pool_status_active(
3042 p_api_version => p_api_version,
3043 p_init_msg_list => p_init_msg_list,
3044 x_return_status => x_return_status,
3045 x_msg_count => x_msg_count,
3046 x_msg_data => x_msg_data,
3047 p_pol_id => l_pol_id);
3048
3049 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3050 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'OKL_POOL_PVT.update_pool_status_active x_return_status :' || x_return_status);
3051 END IF;
3052
3053 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3054 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3055 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3056 RAISE OKL_API.G_EXCEPTION_ERROR;
3057 END IF;
3058
3059 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3060 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '11 update pool header and contents status to active: END');
3061 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'End of Activating IA, completed Successfully');
3062 END IF;
3063
3064
3065 /*** End API body ******************************************************/
3066
3067 -- Get message count and if count is 1, get message info
3068 FND_MSG_PUB.Count_And_Get
3069 (p_count => x_msg_count,
3070 p_data => x_msg_data);
3071
3072 EXCEPTION
3073 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3074 ROLLBACK TO activate_sec_agreement_PVT;
3075 x_return_status := OKL_API.G_RET_STS_ERROR;
3076 FND_MSG_PUB.Count_And_Get
3077 (p_count => x_msg_count,
3078 p_data => x_msg_data);
3079
3080 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3081 ROLLBACK TO activate_sec_agreement_PVT;
3082 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3083 FND_MSG_PUB.Count_And_Get
3084 (p_count => x_msg_count,
3085 p_data => x_msg_data);
3086
3087 WHEN OTHERS THEN
3088 ROLLBACK TO activate_sec_agreement_PVT;
3089 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3090 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3091 p_msg_name => G_UNEXPECTED_ERROR,
3092 p_token1 => G_SQLCODE_TOKEN,
3093 p_token1_value => SQLCODE,
3094 p_token2 => G_SQLERRM_TOKEN,
3095 p_token2_value => SQLERRM);
3096 FND_MSG_PUB.Count_And_Get
3097 (p_count => x_msg_count,
3098 p_data => x_msg_data);
3099
3100 END activate_sec_agreement;
3101
3102 ----------------------------------------------------------------------------------
3103 -- Start of comments
3104 --
3105 -- Procedure Name : update_sec_agreement_sts
3106 -- Description : updates a securitization agreement header, all lines status
3107 -- Business Rules :
3108 -- Parameters :
3109 -- Version : 1.0
3110 -- End of comments
3111 ----------------------------------------------------------------------------------
3112 PROCEDURE update_sec_agreement_sts(
3113 p_api_version IN NUMBER
3114 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
3115 ,x_return_status OUT NOCOPY VARCHAR2
3116 ,x_msg_count OUT NOCOPY NUMBER
3117 ,x_msg_data OUT NOCOPY VARCHAR2
3118 ,p_sec_agreement_status IN okc_k_headers_b.sts_code%TYPE
3119 ,p_sec_agreement_id IN okc_k_headers_b.id%TYPE)
3120 IS
3121 l_api_name CONSTANT VARCHAR2(30) := 'update_sec_agreement_sts';
3122 l_api_version CONSTANT NUMBER := 1.0;
3123 i NUMBER;
3124 l_kle_id OKC_K_LINES_B.ID%TYPE;
3125 l_chr_id OKC_K_HEADERS_B.ID%TYPE;
3126
3127 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3128
3129 lp_secagreement_rec secagreement_rec_type;
3130 lx_secagreement_rec secagreement_rec_type;
3131 l_row_found BOOLEAN := FALSE;
3132 l_row_not_found BOOLEAN := FALSE;
3133
3134 lp_clev_rec clev_rec_type;
3135 lx_clev_rec clev_rec_type;
3136 lp_klev_rec klev_rec_type;
3137 lx_klev_rec klev_rec_type;
3138
3139 lp_khrv_rec khrv_rec_type;
3140 lx_khrv_rec khrv_rec_type;
3141 lp_chrv_rec chrv_rec_type;
3142 lx_chrv_rec chrv_rec_type;
3143 -- mvasudev, Fixed bug#3987171
3144 /*
3145 CURSOR c_agr_lns IS
3146 SELECT cle.id
3147 FROM apps.okc_k_lines_b cle
3148 WHERE cle.dnz_chr_id = p_sec_agreement_id
3149 ;
3150 */
3151 CURSOR c_agr_lns IS
3152 SELECT cle.id
3153 FROM okc_k_lines_b cle
3154 WHERE cle.dnz_chr_id = p_sec_agreement_id
3155 ;
3156
3157 BEGIN
3158 -- Set API savepoint
3159 SAVEPOINT update_sec_agreement_sts_PVT;
3160
3161 -- Check for call compatibility
3162 IF (NOT FND_API.Compatible_API_Call (l_api_version,
3163 p_api_version,
3164 l_api_name,
3165 G_PKG_NAME ))
3166 THEN
3167 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3168 END IF;
3169
3170 -- Initialize message list if requested
3171 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
3172 FND_MSG_PUB.initialize;
3173 END IF;
3174
3175 -- Initialize API status to success
3176 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3177
3178
3179 /*** Begin API body ****************************************************/
3180 ----------------------------------------------------------------------------
3181 --1. update agreement contract header status
3182 ----------------------------------------------------------------------------
3183 --dbms_output.put_line('1. update agreement contract header status');
3184 --dbms_output.put_line('OKL_SEC_AGREEMENT_PVT.update_sec_agreement start');
3185
3186 lp_secagreement_rec.ID := p_sec_agreement_id;
3187 lp_secagreement_rec.STS_CODE := p_sec_agreement_status;
3188
3189 Okl_Sec_Agreement_Pvt.update_sec_agreement(
3190 p_api_version => p_api_version,
3191 p_init_msg_list => p_init_msg_list,
3192 x_return_status => x_return_status,
3193 x_msg_count => x_msg_count,
3194 x_msg_data => x_msg_data,
3195 p_secagreement_rec => lp_secagreement_rec,
3196 x_secagreement_rec => lx_secagreement_rec);
3197
3198 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3199 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3200 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3201 RAISE OKL_API.G_EXCEPTION_ERROR;
3202 END IF;
3203 --dbms_output.put_line('OKL_SEC_AGREEMENT_PVT.update_sec_agreement end');
3204
3205 ----------------------------------------------------------------------------
3206 --2. update agreement contract header lines status
3207 -- cascade update all associated line sts_code to active
3208 -- loop
3209 ----------------------------------------------------------------------------
3210 --dbms_output.put_line('2. update agreement contract header lines status');
3211 --dbms_output.put_line('okl_contract_pub.update_contract_line start');
3212
3213 OPEN c_agr_lns;
3214 LOOP
3215 FETCH c_agr_lns INTO
3216 l_kle_id;
3217 EXIT WHEN c_agr_lns%NOTFOUND;
3218
3219
3220 lp_klev_rec.id := l_kle_id;
3221 lp_clev_rec.STS_CODE := p_sec_agreement_status;
3222 lp_clev_rec.id := l_kle_id;
3223
3224 okl_contract_pub.update_contract_line(
3225 p_api_version => p_api_version,
3226 p_init_msg_list => p_init_msg_list,
3227 x_return_status => x_return_status,
3228 x_msg_count => x_msg_count,
3229 x_msg_data => x_msg_data,
3230 p_clev_rec => lp_clev_rec,
3231 p_klev_rec => lp_klev_rec,
3232 x_clev_rec => lx_clev_rec,
3233 x_klev_rec => lx_klev_rec);
3234
3235 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3236 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3237 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3238 RAISE OKL_API.G_EXCEPTION_ERROR;
3239 END IF;
3240
3241 END LOOP;
3242 CLOSE c_agr_lns;
3243 --dbms_output.put_line('okl_contract_pub.update_contract_line start');
3244
3245 /*** End API body ******************************************************/
3246
3247 -- Get message count and if count is 1, get message info
3248 FND_MSG_PUB.Count_And_Get
3249 (p_count => x_msg_count,
3250 p_data => x_msg_data);
3251
3252 EXCEPTION
3253 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3254 ROLLBACK TO update_sec_agreement_sts_PVT;
3255 x_return_status := OKL_API.G_RET_STS_ERROR;
3256 FND_MSG_PUB.Count_And_Get
3257 (p_count => x_msg_count,
3258 p_data => x_msg_data);
3259
3260 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3261 ROLLBACK TO update_sec_agreement_sts_PVT;
3262 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3263 FND_MSG_PUB.Count_And_Get
3264 (p_count => x_msg_count,
3265 p_data => x_msg_data);
3266
3267 WHEN OTHERS THEN
3268 ROLLBACK TO update_sec_agreement_sts_PVT;
3269 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3270 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3271 p_msg_name => G_UNEXPECTED_ERROR,
3272 p_token1 => G_SQLCODE_TOKEN,
3273 p_token1_value => SQLCODE,
3274 p_token2 => G_SQLERRM_TOKEN,
3275 p_token2_value => SQLERRM);
3276 FND_MSG_PUB.Count_And_Get
3277 (p_count => x_msg_count,
3278 p_data => x_msg_data);
3279
3280 END update_sec_agreement_sts;
3281
3282 --Added by kthiruva on 18-Dec-2007
3283 -- New method to validate an add request on an active investor agreement
3284 --Bug 6691554 - Start of Changes
3285 Procedure validate_add_request(
3286 p_api_version IN NUMBER,
3287 p_init_msg_list IN VARCHAR2,
3288 x_return_status OUT NOCOPY VARCHAR2,
3289 x_msg_count OUT NOCOPY NUMBER,
3290 x_msg_data OUT NOCOPY VARCHAR2,
3291 p_chr_id IN NUMBER)
3292 IS
3293 --Declaring local variables
3294 l_stream_value NUMBER;
3295 l_api_name CONSTANT VARCHAR2(30) := 'validate_add_request';
3296 l_api_version CONSTANT NUMBER := 1.0;
3297
3298
3299 CURSOR get_pol_id_csr(p_chr_id NUMBER)
3300 IS
3301 SELECT ID
3302 FROM OKL_POOLS
3303 WHERE KHR_ID = p_chr_id
3304 AND STATUS_CODE = 'ACTIVE';
3305
3306 l_pol_id NUMBER;
3307 x_reconciled VARCHAR2(1);
3308
3309 BEGIN
3310 -- initialize return status
3311 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3312
3313
3314 x_return_status := OKL_API.START_ACTIVITY(
3315 p_api_name => l_api_name,
3316 p_pkg_name => g_pkg_name,
3317 p_init_msg_list => p_init_msg_list,
3318 l_api_version => l_api_version,
3319 p_api_version => p_api_version,
3320 p_api_type => G_API_TYPE,
3321 x_return_status => x_return_status);
3322
3323 -- check if activity started successfully
3324 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3325 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3326 ElSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3327 raise OKL_API.G_EXCEPTION_ERROR;
3328 End If;
3329
3330 FOR get_pol_id_rec IN get_pol_id_csr(p_chr_id)
3331 LOOP
3332 l_pol_id := get_pol_id_rec.id;
3333 END LOOP;
3334
3335 IF l_pol_id IS NULL
3336 THEN
3337 Okl_Api.set_message(G_APP_NAME,
3338 G_INVALID_VALUE,
3339 G_COL_NAME_TOKEN,
3340 'POOL_ID');
3341 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3342 END IF;
3343
3344 OKL_POOL_PVT.get_tot_recei_amt_pend(
3345 p_api_version => '1.0',
3346 p_init_msg_list => p_init_msg_list,
3347 x_return_status => x_return_status,
3348 x_msg_count => x_msg_count,
3349 x_msg_data => x_msg_data,
3350 x_value => l_stream_value,
3351 p_pol_id => l_pol_id );
3352
3353 If( l_stream_value IS NULL OR l_stream_value = 0 ) Then
3354 OKL_API.set_message(
3355 p_app_name => G_APP_NAME,
3356 p_msg_name => 'OKL_QA_STREAM_VALUE');
3357 -- notify caller of an error but do not raise an exception
3358 x_return_status := OKL_API.G_RET_STS_ERROR;
3359 END IF;
3360
3361 --Check to see if the pool recquires reconcilation
3362 OKL_POOL_PVT.reconcile_contents(
3363 p_api_version => p_api_version,
3364 p_init_msg_list => p_init_msg_list,
3365 x_return_status => x_return_status,
3366 x_msg_count => x_msg_count,
3367 x_msg_data => x_msg_data,
3368 p_pol_id => l_pol_id,
3369 p_mode => 'ACTIVE',
3370 x_reconciled => x_reconciled );
3371
3372 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3373 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3374 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3375 RAISE OKL_API.G_EXCEPTION_ERROR;
3376 END IF;
3377
3378 IF ( x_reconciled = OKL_API.G_TRUE ) Then
3379 x_return_status := OKL_API.G_RET_STS_ERROR;
3380 -- OKL_API.set_message(
3381 -- p_app_name => G_APP_NAME,
3382 -- p_msg_name => 'OKL_LLA_RECONCILED');
3383 raise OKL_API.G_EXCEPTION_ERROR;
3384 End If;
3385
3386 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3387
3388 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
3389
3390
3391 EXCEPTION
3392 when OKL_API.G_EXCEPTION_ERROR then
3393 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3394 p_api_name => l_api_name,
3395 p_pkg_name => g_pkg_name,
3396 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3397 x_msg_count => x_msg_count,
3398 x_msg_data => x_msg_data,
3399 p_api_type => g_api_type);
3400
3401 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
3402 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3403 p_api_name => l_api_name,
3404 p_pkg_name => g_pkg_name,
3405 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3406 x_msg_count => x_msg_count,
3407 x_msg_data => x_msg_data,
3408 p_api_type => g_api_type);
3409
3410 when OTHERS then
3411 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3412 p_api_name => l_api_name,
3413 p_pkg_name => g_pkg_name,
3414 p_exc_name => 'OTHERS',
3415 x_msg_count => x_msg_count,
3416 x_msg_data => x_msg_data,
3417 p_api_type => g_api_type);
3418
3419
3420 END validate_add_request;
3421
3422 Procedure activate_add_request (
3423 p_api_version IN NUMBER
3424 ,p_init_msg_list IN VARCHAR2
3425 ,x_return_status OUT NOCOPY VARCHAR2
3426 ,x_msg_count OUT NOCOPY NUMBER
3427 ,x_msg_data OUT NOCOPY VARCHAR2
3428 ,p_khr_id IN OKC_K_HEADERS_B.ID%TYPE)
3429 IS
3430
3431 l_api_name CONSTANT VARCHAR2(30) := 'activate_add_request';
3432 l_api_version CONSTANT NUMBER := 1.0;
3433 i NUMBER;
3434 l_kle_id OKC_K_LINES_B.ID%TYPE;
3435 l_pol_id OKL_POOLS.ID%TYPE;
3436 l_currency_code OKL_POOLS.CURRENCY_CODE%TYPE;
3437 l_org_id OKL_POOLS.ORG_ID%TYPE;
3438 l_legal_entity_id OKL_POOLS.LEGAL_ENTITY_ID%TYPE;
3439 l_chr_id OKC_K_HEADERS_B.ID%TYPE;
3440
3441 l_pox_id OKL_POOL_CONTENTS.POX_ID%TYPE;
3442 l_poc_id OKL_POOL_CONTENTS.ID%TYPE;
3443 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3444
3445 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
3446 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
3447 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
3448 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
3449
3450
3451
3452 lp_secagreement_rec secagreement_rec_type;
3453 lx_secagreement_rec secagreement_rec_type;
3454 l_row_found BOOLEAN := FALSE;
3455 l_row_not_found BOOLEAN := FALSE;
3456 lp_poxv_rec poxv_rec_type;
3457 lx_poxv_rec poxv_rec_type;
3458 lp_pocv_rec pocv_rec_type;
3459 lx_pocv_rec pocv_rec_type;
3460 lp_pocv2_rec pocv_rec_type;
3461 lx_pocv2_rec pocv_rec_type;
3462
3463
3464 CURSOR c_pool_csr(p_khr_id NUMBER)
3465 IS
3466 SELECT pol.id
3467 FROM okl_pools pol
3468 WHERE pol.khr_id = p_khr_id;
3469
3470 CURSOR c_pool_chr_csr(p_pol_id NUMBER)
3471 IS
3472 SELECT poc.khr_id,
3473 poc.id
3474 FROM okl_pool_contents poc
3475 WHERE poc.pol_id = p_pol_id
3476 AND poc.status_code = Okl_Pool_Pvt.G_POC_STS_PENDING;
3477
3478 /* ankushar Bug# 6773285 Added Principal Payment for Loan contracts also renamed Cursor
3479 Start Changes
3480 */
3481 -- mvasudev, 10/29/2003
3482 -- Cursor to check if sty is securitized ">=" effective_date
3483 CURSOR l_okl_sty_csr(p_khr_id NUMBER)
3484 IS
3485 SELECT 1
3486 FROM okl_pools polb
3487 ,okl_strm_type_b styb
3488 ,okl_pool_contents pocb
3489 WHERE polb.khr_id = p_khr_id
3490 AND pocb.pol_id = polb.id
3491 AND pocb.sty_id = styb.id
3492 AND styb.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
3493 AND pocb.status_code='PENDING';
3494 /* ankushar Bug# 6773285
3495 End Changes
3496 */
3497 CURSOR get_pox_csr(p_pol_id NUMBER)
3498 IS
3499 SELECT POX.ID
3500 FROM OKL_POOL_TRANSACTIONS POX
3501 WHERE POX.POL_ID = p_pol_id
3502 AND POX.TRANSACTION_STATUS = 'APPROVED'
3503 AND POX.TRANSACTION_TYPE = 'ADD'
3504 AND POX.TRANSACTION_REASON = 'ADJUSTMENTS';
3505
3506 --Cursor to fetch the investor stake
3507 --Cursor to fetch the investor stake
3508 CURSOR get_inv_stake_csr(p_khr_id NUMBER)
3509 IS
3510 SELECT TOP_KLE.ID,
3511 TOP_KLE.AMOUNT original_Stake,
3512 TOP_KLE.AMOUNT_STAKE additional_Stake
3513 FROM OKC_K_LINES_B TOP_LINE,
3514 OKL_K_LINES TOP_KLE,
3515 OKC_K_PARTY_ROLES_B PARTY_ROLE
3516 WHERE TOP_LINE.dnz_chr_id = p_khr_id
3517 AND TOP_KLE.ID = TOP_LINE.ID
3518 AND PARTY_ROLE.cle_id = TOP_LINE.id
3519 AND PARTY_ROLE.dnz_chr_id = TOP_LINE.dnz_chr_id
3520 AND PARTY_ROLE.rle_code = 'INVESTOR'
3521 AND PARTY_ROLE.jtot_object1_code = 'OKX_PARTY';
3522
3523 l_clev_rec clev_rec_type;
3524 l_klev_rec klev_rec_type;
3525 lx_clev_rec clev_rec_type;
3526 lx_klev_rec klev_rec_type;
3527
3528 BEGIN
3529 -- Set API savepoint
3530 SAVEPOINT activate_add_request_pvt;
3531
3532 -- Check for call compatibility
3533 IF (NOT FND_API.Compatible_API_Call (l_api_version,
3534 p_api_version,
3535 l_api_name,
3536 G_PKG_NAME ))
3537 THEN
3538 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3539 END IF;
3540
3541 -- Initialize message list if requested
3542 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
3543 FND_MSG_PUB.initialize;
3544 END IF;
3545
3546 -- Initialize API status to success
3547 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3548
3549 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3550 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Begin Processing Add Request');
3551 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug','p_khr_id:'|| p_khr_id);
3552 END IF;
3553
3554 /*** Begin API body ****************************************************/
3555 /* process steps
3556 --1 Validate the add request
3557 --2 Mark associated contract to securitizated
3558 --3 call Streams Generator API to generate disbursement basis streams
3559 --4 call Streams Generator API to generate PV Streams of Securitized Streams
3560 --5 call Accrual API
3561 --6 call generate_journal_entries
3562 --7 call BPD AR api
3563 --8 update pool header and contents status to active
3564 --9 Update the stake amount per investor and clear out the amount_stake field
3565
3566 */
3567
3568 ----------------------------------------------------------------------------
3569 --1 Validate the add request
3570 ----------------------------------------------------------------------------
3571 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3572 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '1. Validate the add request.');
3573 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Calling validate_add_request: START');
3574 END IF;
3575
3576 --Fetch the pool id from the Investor Agreement Id passed
3577 FOR c_pool_rec IN c_pool_csr(p_khr_id)
3578 LOOP
3579 l_pol_id := c_pool_rec.id;
3580 END LOOP;
3581
3582 IF l_pol_id IS NULL
3583 THEN
3584 Okl_Api.set_message(G_APP_NAME,
3585 G_INVALID_VALUE,
3586 G_COL_NAME_TOKEN,
3587 'POOL_ID');
3588 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3589 END IF;
3590
3591 --Fetch the transaction id of the Pool Transaction
3592 FOR get_pox_rec IN get_pox_csr(l_pol_id)
3593 LOOP
3594 l_pox_id := get_pox_rec.id;
3595 END LOOP;
3596
3597 IF l_pox_id IS NULL
3598 THEN
3599 Okl_Api.set_message(G_APP_NAME,
3600 G_INVALID_VALUE,
3601 G_COL_NAME_TOKEN,
3602 'POX_ID');
3603 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3604 END IF;
3605
3606 validate_add_request(
3607 p_api_version => p_api_version,
3608 p_init_msg_list => p_init_msg_list,
3609 x_return_status => x_return_status,
3610 x_msg_count => x_msg_count,
3611 x_msg_data => x_msg_data,
3612 p_chr_id => p_khr_id);
3613
3614 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3615 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'validate_add_request x_return_status :' || x_return_status);
3616 END IF;
3617
3618 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3619 --Update the status of the Pool Transaction to INCOMPLETE and Halt processing
3620 lp_poxv_rec.id := l_pox_id;
3621 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_INCOMPLETE;
3622
3623 OKL_POOL_PVT.update_pool_transaction(p_api_version => p_api_version,
3624 p_init_msg_list => p_init_msg_list,
3625 x_return_status => x_return_status,
3626 x_msg_count => x_msg_count,
3627 x_msg_data => x_msg_data,
3628 p_poxv_rec => lp_poxv_rec,
3629 x_poxv_rec => lx_poxv_rec);
3630
3631 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3632 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3633 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
3634 RAISE OKL_API.G_EXCEPTION_ERROR;
3635 END IF;
3636
3637 --Stop submit processing
3638 RAISE G_HALT_PROCESSING;
3639 END IF;
3640
3641
3642 ----------------------------------------------------------------------------
3643 --2 Mark associated contract to securitizated
3644 ----------------------------------------------------------------------------
3645 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3646 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '2. Mark associated contract to securtizated.');
3647 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Calling okl_contract_pub.update_contract_header: START');
3648 END IF;
3649
3650 OPEN c_pool_chr_csr(l_pol_id);
3651 LOOP
3652 FETCH c_pool_chr_csr INTO l_chr_id,l_poc_id;
3653 EXIT WHEN c_pool_chr_csr%NOTFOUND;
3654
3655 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3656 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'l_chr_id :' || l_chr_id);
3657 END IF;
3658
3659 lp_chrv_rec.id := l_chr_id;
3660 lp_khrv_rec.id := l_chr_id;
3661 lp_khrv_rec.SECURITIZED_CODE := G_SECURITIZED_CODE_Y;
3662
3663 okl_contract_pub.update_contract_header(
3664 p_api_version => p_api_version,
3665 p_init_msg_list => p_init_msg_list,
3666 x_return_status => x_return_status,
3667 x_msg_count => x_msg_count,
3668 x_msg_data => x_msg_data,
3669 p_chrv_rec => lp_chrv_rec,
3670 p_khrv_rec => lp_khrv_rec,
3671 x_chrv_rec => lx_chrv_rec,
3672 x_khrv_rec => lx_khrv_rec);
3673
3674 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3675 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'okl_contract_pub.update_contract_header x_return_status :' || x_return_status);
3676 END IF;
3677
3678 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3679 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3680 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3681 RAISE OKL_API.G_EXCEPTION_ERROR;
3682 END IF;
3683
3684 END LOOP;
3685 CLOSE c_pool_chr_csr;
3686 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3687 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'okl_contract_pub.update_contract_header: END');
3688 END IF;
3689
3690
3691 ----------------------------------------------------------------------------
3692
3693 -- 3. call Streams Generator API to generate disbursement basis streams
3694 --
3695 ----------------------------------------------------------------------------
3696 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3697 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '3 call Streams Generator API to generate disbursement basis streams.');
3698 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Calling okl_stream_generator_pvt.create_disb_streams: START');
3699 END IF;
3700
3701 OKL_STREAM_GENERATOR_PVT.create_disb_streams(
3702 p_api_version => p_api_version,
3703 p_init_msg_list => p_init_msg_list,
3704 p_agreement_id => p_khr_id,
3705 p_pool_status => 'ACTIVE',
3706 p_mode => 'ACTIVE',
3707 x_return_status => x_return_status,
3708 x_msg_count => x_msg_count ,
3709 x_msg_data => x_msg_data );
3710
3711 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3712 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'okl_stream_generator_pvt.create_disb_streams x_return_status :' || x_return_status);
3713 END IF;
3714
3715 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3716 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3717 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3718 RAISE OKL_API.G_EXCEPTION_ERROR;
3719 END IF;
3720
3721 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3722 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '3 call Streams Generator API to generate disbursement basis streams: END');
3723 END IF;
3724
3725 ----------------------------------------------------------------------------
3726
3727 --4 call Streams Generator API to generate PV Streams of Securitized Streams
3728 --
3729 ----------------------------------------------------------------------------
3730 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3731 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '4 call Streams Generator API to generate PV Streams of Securitized Streams: START');
3732 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Calling okl_stream_generator_pvt.create_pv_streams');
3733 END IF;
3734
3735 OKL_STREAM_GENERATOR_PVT.create_pv_streams(
3736 p_api_version => p_api_version,
3737 p_init_msg_list => p_init_msg_list,
3738 p_agreement_id => p_khr_id,
3739 p_pool_status => 'ACTIVE',
3740 p_mode => 'ACTIVE',
3741 x_return_status => x_return_status,
3742 x_msg_count => x_msg_count ,
3743 x_msg_data => x_msg_data );
3744
3745 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3746 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'okl_stream_generator_pvt.create_pv_streams x_return_status :' || x_return_status);
3747 END IF;
3748
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
3755 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3756 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '4 call Streams Generator API to generate PV Streams of Securitized Streams: END');
3757 END IF;
3758
3759
3760 ----------------------------------------------------------------------------
3761 --5 call Accrual API
3762 --
3763 ----------------------------------------------------------------------------
3764 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3765 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '5 call to the Accrual API: START');
3766 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Calling okl_accrual_sec_pvt.create_streams');
3767 END IF;
3768
3769
3770 -- mvasudev, 10/29/2003
3771 -- Call the Accrual API only when there are RENT streams
3772 -- This should loop EXACTLY one time
3773 FOR l_okl_sty_rent_rec IN l_okl_sty_csr(p_khr_id)
3774 LOOP
3775 OKL_ACCRUAL_SEC_PVT.CREATE_STREAMS(
3776 p_api_version => p_api_version,
3777 p_init_msg_list => p_init_msg_list,
3778 x_return_status => x_return_status,
3779 x_msg_count => x_msg_count,
3780 x_msg_data => x_msg_data,
3781 p_khr_id => p_khr_id,
3782 p_mode => 'ACTIVE');
3783
3784 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3785 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'okl_stream_generator_pvt.create_pv_streams p_khr_id:' || p_khr_id);
3786 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'okl_stream_generator_pvt.create_pv_streams x_return_status :' || x_return_status);
3787 END IF;
3788
3789 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3790 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3791 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3792 RAISE OKL_API.G_EXCEPTION_ERROR;
3793 END IF;
3794 -- Fixed Bug#3386816, mvasudev
3795 EXIT WHEN l_okl_sty_csr%FOUND;
3796 END LOOP;
3797 -- mvasudev, end, 10/29/2003
3798 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3799 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '5 call to the Accrual API: END');
3800 END IF;
3801
3802 ----------------------------------------------------------------------------
3803 --6 call generate_journal_entries
3804 --
3805 ----------------------------------------------------------------------------
3806 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3807 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '6 call to the generate_journal_entries: START');
3808 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Calling generate_journal_entries');
3809 END IF;
3810
3811 generate_journal_entries(
3812 p_api_version => p_api_version,
3813 p_init_msg_list => p_init_msg_list,
3814 x_return_status => x_return_status,
3815 x_msg_count => x_msg_count,
3816 x_msg_data => x_msg_data,
3817 p_contract_id => p_khr_id
3818 ,p_transaction_type => G_TRY_TYPE_INV);
3819
3820 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3821 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'generate_journal_entries x_return_status :' || x_return_status);
3822 END IF;
3823
3824 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3825 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3826 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3827 RAISE OKL_API.G_EXCEPTION_ERROR;
3828 END IF;
3829
3830 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3831 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '6 call to the generate_journal_entries: END');
3832 END IF;
3833
3834 ----------------------------------------------------------------------------
3835 --7 call BPD AR api
3836 --
3837 ----------------------------------------------------------------------------
3838 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3839 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '7 call BPD Billing API: START');
3840 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Calling Okl_Investor_Billing_Pvt.create_investor_bill');
3841 END IF;
3842
3843 Okl_Investor_Billing_Pvt.create_investor_bill(
3844 p_api_version => p_api_version,
3845 p_init_msg_list => p_init_msg_list,
3846 x_return_status => x_return_status,
3847 x_msg_count => x_msg_count,
3848 x_msg_data => x_msg_data,
3849 p_inv_agr => p_khr_id);
3850
3851 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3852 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Okl_Investor_Billing_Pvt.create_investor_bill p_khr_id:' || p_khr_id);
3853 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Okl_Investor_Billing_Pvt.create_investor_bill x_return_status :' || x_return_status);
3854 END IF;
3855
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
3862 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3863 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '7 call BPD Billing API: END');
3864 END IF;
3865
3866 --Update the total stake to include the additional stake
3867 FOR get_inv_stake_rec IN get_inv_stake_csr(p_khr_id)
3868 LOOP
3869 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3870 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'OKL_CONTRACT_PUB.update_contract_line');
3871 END IF;
3872
3873 --Only if an additional stake amount has been captured, then add this stake to the original stake
3874 IF get_inv_stake_rec.additional_stake IS NOT NULL THEN
3875 l_clev_rec.id := get_inv_stake_rec.id;
3876 l_klev_rec.id := get_inv_stake_rec.id;
3877 l_klev_rec.amount := get_inv_stake_rec.original_stake + get_inv_stake_rec.additional_stake;
3878 l_klev_rec.amount_stake := NULL;
3879
3880 OKL_CONTRACT_PUB.update_contract_line(
3881 p_api_version => p_api_version,
3882 p_init_msg_list => p_init_msg_list,
3883 x_return_status => x_return_status,
3884 x_msg_count => x_msg_count,
3885 x_msg_data => x_msg_data,
3886 p_clev_rec => l_clev_rec,
3887 p_klev_rec => l_klev_rec,
3888 x_clev_rec => lx_clev_rec,
3889 x_klev_rec => lx_klev_rec);
3890
3891 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3892 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Okl_Sec_Investor_Pvt.update_investor' || x_return_status);
3893 END IF;
3894
3895 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3896 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3897 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3898 RAISE OKL_API.G_EXCEPTION_ERROR;
3899 END IF;
3900 END IF;
3901 END LOOP;
3902
3903
3904
3905
3906 ----------------------------------------------------------------------------------
3907 --8 update pool contents status to active and pool transactions status to COMPLETE
3908 ----------------------------------------------------------------------------------
3909 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3910 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '8 update pool contents status to active: START');
3911 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Calling OKL_POOL_PVT.update_pool_contents');
3912 END IF;
3913
3914 --Update pool contents to ACTIVE
3915 FOR c_pool_chr_rec IN c_pool_chr_csr(l_pol_id)
3916 LOOP
3917 lp_pocv_rec.id := c_pool_chr_rec.id;
3918 lp_pocv_rec.status_code := Okl_Pool_Pvt.G_POC_STS_ACTIVE;
3919
3920 Okl_Pool_Pvt.update_pool_contents(
3921 p_api_version => p_api_version,
3922 p_init_msg_list => p_init_msg_list,
3923 x_return_status => x_return_status,
3924 x_msg_count => x_msg_count,
3925 x_msg_data => x_msg_data,
3926 p_pocv_rec => lp_pocv_rec,
3927 x_pocv_rec => lx_pocv_rec);
3928
3929 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3930 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'OKL_POOL_PVT.update_pool_status_active x_return_status :' || x_return_status);
3931 END IF;
3932
3933
3934 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3935 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3936 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3937 RAISE Okl_Api.G_EXCEPTION_ERROR;
3938 END IF;
3939 END LOOP;
3940
3941
3942 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3943 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '8 update pool transaction to Complete: START');
3944 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Calling OKL_POOL_PVT.update_pool_transaction');
3945 END IF;
3946
3947 --Update pool transaction to COMPLETE
3948 lp_poxv_rec.id := l_pox_id;
3949 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_COMPLETE;
3950
3951 OKL_POOL_PVT.update_pool_transaction(p_api_version => p_api_version,
3952 p_init_msg_list => p_init_msg_list,
3953 x_return_status => x_return_status,
3954 x_msg_count => x_msg_count,
3955 x_msg_data => x_msg_data,
3956 p_poxv_rec => lp_poxv_rec,
3957 x_poxv_rec => lx_poxv_rec);
3958
3959 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3960 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3961 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
3962 RAISE OKL_API.G_EXCEPTION_ERROR;
3963 END IF;
3964
3965
3966 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3967 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'OKL_POOL_PVT.update_pool_transaction x_return_status :' || x_return_status);
3968 END IF;
3969
3970 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3971 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3972 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3973 RAISE OKL_API.G_EXCEPTION_ERROR;
3974 END IF;
3975
3976 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3977 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Update investor stake');
3978 END IF;
3979
3980 /*** End API body ******************************************************/
3981
3982 -- Get message count and if count is 1, get message info
3983 FND_MSG_PUB.Count_And_Get
3984 (p_count => x_msg_count,
3985 p_data => x_msg_data);
3986
3987 EXCEPTION
3988 --For this User defined exception, do not rollback
3989 WHEN G_HALT_PROCESSING THEN
3990 x_return_status := OKL_API.G_RET_STS_ERROR;
3991 FND_MSG_PUB.Count_And_Get
3992 (p_count => x_msg_count,
3993 p_data => x_msg_data);
3994
3995 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3996 ROLLBACK TO activate_add_request_pvt;
3997 x_return_status := OKL_API.G_RET_STS_ERROR;
3998 FND_MSG_PUB.Count_And_Get
3999 (p_count => x_msg_count,
4000 p_data => x_msg_data);
4001
4002 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4003 ROLLBACK TO activate_add_request_pvt;
4004 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4005 FND_MSG_PUB.Count_And_Get
4006 (p_count => x_msg_count,
4007 p_data => x_msg_data);
4008 WHEN OTHERS THEN
4009 ROLLBACK TO activate_add_request_pvt;
4010 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
4011 OKL_API.Set_Message(p_app_name => G_APP_NAME,
4012 p_msg_name => G_UNEXPECTED_ERROR,
4013 p_token1 => G_SQLCODE_TOKEN,
4014 p_token1_value => SQLCODE,
4015 p_token2 => G_SQLERRM_TOKEN,
4016 p_token2_value => SQLERRM);
4017 FND_MSG_PUB.Count_And_Get
4018 (p_count => x_msg_count,
4019 p_data => x_msg_data);
4020
4021
4022 END activate_add_request;
4023
4024 -- Bug 6691554 - End of Changes
4025
4026 -------------------------------------------------------------------------------------------------
4027 -- PROCEDURE submit_add_khr_request
4028 ---------------------------------------------------------------------------
4029 -- Start of comments
4030 --
4031 -- Procedure Name : submit_add_khr_request
4032 -- Description :
4033 -- Business Rules : Submit the Add Contracts Request for Approval.
4034 -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
4035 -- x_msg_data, , p_agreement_id, p_pool_id, x_pool_trx_status.
4036 -- Version : 1.0
4037 -- End of comments
4038 ---------------------------------------------------------------------------
4039 PROCEDURE submit_add_khr_request (p_api_version IN NUMBER,
4040 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
4041 x_return_status OUT NOCOPY VARCHAR2,
4042 x_msg_count OUT NOCOPY NUMBER,
4043 x_msg_data OUT NOCOPY VARCHAR2,
4044 p_agreement_id IN OKC_K_HEADERS_V.ID%TYPE,
4045 p_pool_id IN OKL_POOLS.ID%TYPE,
4046 x_pool_trx_status OUT NOCOPY OKL_POOL_TRANSACTIONS.TRANSACTION_STATUS%TYPE)
4047 IS
4048 -- Get Pool Transaction Details
4049 CURSOR c_fetch_pool_trans_id_csr(p_transaction_id OKL_POOLS.ID%TYPE)
4050 IS
4051 SELECT pox.id
4052 FROM okl_pool_transactions pox
4053 WHERE pox.pol_id = p_pool_id
4054 AND pox.TRANSACTION_STATUS IN (G_POOL_TRX_STATUS_NEW, G_POOL_TRX_STS_APPR_REJECTED, G_POOL_TRX_STATUS_INCOMPLETE);
4055
4056 l_return_status VARCHAR2(3);
4057 l_api_name CONSTANT VARCHAR2(30) := 'submit_add_khr_request';
4058 l_parameter_list wf_parameter_list_t;
4059 l_key VARCHAR2(240);
4060 l_event_name VARCHAR2(240) := 'oracle.apps.okl.ia.approve_add_contracts_request';
4061 l_agreement_id OKC_K_HEADERS_V.ID%TYPE;
4062 l_pool_id OKL_POOLS.ID%TYPE;
4063 l_pool_trans_id OKL_POOL_TRANSACTIONS.ID%TYPE;
4064 lp_poxv_rec poxv_rec_type;
4065 lx_poxv_rec poxv_rec_type;
4066 l_approval_process fnd_lookups.lookup_code%TYPE;
4067 l_msg_data VARCHAR2(2000);
4068 l_msg_count NUMBER;
4069
4070 BEGIN
4071
4072 -- initializing the message list
4073 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
4074 FND_MSG_PUB.initialize;
4075 END IF;
4076
4077 l_return_status := OKL_API.G_RET_STS_SUCCESS;
4078 l_pool_id := p_pool_id;
4079 l_agreement_id := p_agreement_id;
4080 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4081
4082 -- Call start_activity to create savepoint, check compatibility
4083 -- and initialize message list
4084 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
4085 p_init_msg_list,
4086 '_PVT',
4087 x_return_status);
4088 -- Check if activity started successfully
4089 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4090 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4091 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4092 RAISE OKL_API.G_EXCEPTION_ERROR;
4093 END IF;
4094 -- fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.submit_add_khr_request.debug', 'Okl_Sec_Agreement_Pvt.submit_add_khr_request x_return_status :' || x_return_status);
4095
4096 -- read the profile OKL: Investor Add Contracts Approval Process
4097 l_approval_process := fnd_profile.value('OKL_IA_ADD_KHR_APPR_PROCESS');
4098
4099 -- get the pool transaction id which needs to be updated, since we can have only one transaction in 'NEW' status
4100 OPEN c_fetch_pool_trans_id_csr(p_pool_id);
4101 FETCH c_fetch_pool_trans_id_csr into l_pool_trans_id;
4102 IF c_fetch_pool_trans_id_csr%NOTFOUND THEN
4103 OKL_API.set_message(p_app_name => G_APP_NAME,
4104 p_msg_name => G_NO_MATCHING_RECORD,
4105 p_token1 => G_COL_NAME_TOKEN,
4106 p_token1_value => 'OKL_POOL_TRANSACTIONS.ID');
4107 RAISE OKL_API.G_EXCEPTION_ERROR;
4108 END IF;
4109 CLOSE c_fetch_pool_trans_id_csr ;
4110
4111 --Set the Pool Transaction Id for the update call
4112 lp_poxv_rec.ID := l_pool_trans_id;
4113
4114 -- basic validation. API call should be in status passed before it can be submitted for approval
4115 /* Place the Validation API Call here :TODO */
4116 validate_add_request(p_api_version,
4117 p_init_msg_list,
4118 x_return_status,
4119 l_msg_count,
4120 l_msg_data,
4121 l_agreement_id);
4122 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
4123 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_INCOMPLETE;
4124
4125 OKL_POOL_PVT.update_pool_transaction(p_api_version => p_api_version,
4126 p_init_msg_list => p_init_msg_list,
4127 x_return_status => x_return_status,
4128 x_msg_count => x_msg_count,
4129 x_msg_data => x_msg_data,
4130 p_poxv_rec => lp_poxv_rec,
4131 x_poxv_rec => lx_poxv_rec);
4132 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4133 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4134 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4135 RAISE OKL_API.G_EXCEPTION_ERROR;
4136 END IF;
4137
4138 --Since the Validation returned an Error need to raise an exception and return the control,
4139 --no further processing needs to be done.
4140 -- vsgandhi Added for Bug 13997160
4141 OKL_API.set_message(
4142 p_app_name => G_APP_NAME,
4143 p_msg_name => 'OKL_LLA_RECONCILED');
4144
4145 RAISE G_HALT_PROCESSING;
4146
4147 END IF; --x_return_status <> OKL_API.G_RET_STS_SUCCESS
4148
4149 IF(NVL(l_approval_process, 'NONE')) = 'NONE' THEN
4150 -- since no option is set at the profile, approve the operating agreement by default
4151 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_APPROVED;
4152
4153 OKL_POOL_PVT.update_pool_transaction(p_api_version => p_api_version,
4154 p_init_msg_list => p_init_msg_list,
4155 x_return_status => x_return_status,
4156 x_msg_count => x_msg_count,
4157 x_msg_data => x_msg_data,
4158 p_poxv_rec => lp_poxv_rec,
4159 x_poxv_rec => lx_poxv_rec);
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
4166 ELSIF(l_approval_process IN (G_ADD_KHR_REQUEST_APPROVAL_WF, G_ADD_KHR_REQUEST_APPRV_AME))THEN
4167
4168 -- We need to status to Approved Pending since We are sending for approval
4169 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_PENDING_APPR;
4170
4171 OKL_POOL_PVT.update_pool_transaction(p_api_version => p_api_version,
4172 p_init_msg_list => p_init_msg_list,
4173 x_return_status => x_return_status,
4174 x_msg_count => x_msg_count,
4175 x_msg_data => x_msg_data,
4176 p_poxv_rec => lp_poxv_rec,
4177 x_poxv_rec => lx_poxv_rec);
4178 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4179 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4180 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
4181 RAISE OKL_API.G_EXCEPTION_ERROR;
4182 END IF;
4183
4184 -- Raise Event
4185 OKL_SEC_AGREEMENT_WF.raise_add_khr_approval_event(p_api_version => p_api_version
4186 ,p_init_msg_list => p_init_msg_list
4187 ,x_return_status => x_return_status
4188 ,x_msg_count => x_msg_count
4189 ,x_msg_data => x_msg_data
4190 ,p_agreement_id => l_agreement_id
4191 ,p_pool_id => l_pool_id
4192 ,p_pool_trans_id => l_pool_trans_id);
4193
4194 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4195 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4196 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4197 RAISE OKL_API.G_EXCEPTION_ERROR;
4198 END IF;
4199 END IF; -- end of NVL(l_approval_process,'NONE')='NONE'
4200
4201 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
4202 x_msg_data => x_msg_data);
4203 EXCEPTION
4204 --For this User defined exception, do not rollback
4205 WHEN G_HALT_PROCESSING THEN
4206 x_return_status := OKL_API.G_RET_STS_ERROR;
4207 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4208 p_data => x_msg_data);
4209
4210 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4211 x_return_status := OKL_API.G_RET_STS_ERROR;
4212 IF c_fetch_pool_trans_id_csr%ISOPEN THEN
4213 CLOSE c_fetch_pool_trans_id_csr;
4214 END IF;
4215 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4216 p_api_name => l_api_name,
4217 p_pkg_name => G_PKG_NAME,
4218 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4219 x_msg_count => x_msg_count,
4220 x_msg_data => x_msg_data,
4221 p_api_type => G_API_TYPE);
4222
4223 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4224 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4225 IF c_fetch_pool_trans_id_csr%ISOPEN THEN
4226 CLOSE c_fetch_pool_trans_id_csr;
4227 END IF;
4228 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4229 p_api_name => l_api_name,
4230 p_pkg_name => G_PKG_NAME,
4231 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4232 x_msg_count => x_msg_count,
4233 x_msg_data => x_msg_data,
4234 p_api_type => G_API_TYPE);
4235 WHEN OTHERS THEN
4236 IF c_fetch_pool_trans_id_csr%ISOPEN THEN
4237 CLOSE c_fetch_pool_trans_id_csr;
4238 END IF;
4239 -- store SQL error message on message stack
4240 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4241 p_api_name => l_api_name,
4242 p_pkg_name => G_PKG_NAME,
4243 p_exc_name => 'OTHERS',
4244 x_msg_count => x_msg_count,
4245 x_msg_data => x_msg_data,
4246 p_api_type => G_API_TYPE);
4247
4248 END submit_add_khr_request;
4249 /*
4250 19-Dec-2007, ankushar Bug# 6691554
4251 endchanges
4252 */
4253
4254 /* sosharma 03-01-2008
4255 Added procedure to cancel the add request on active Investor Agreement
4256 Start changes*/
4257
4258
4259 Procedure cancel_add_request(
4260 p_api_version IN NUMBER,
4261 p_init_msg_list IN VARCHAR2,
4262 x_return_status OUT NOCOPY VARCHAR2,
4263 x_msg_count OUT NOCOPY NUMBER,
4264 x_msg_data OUT NOCOPY VARCHAR2,
4265 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE)
4266 IS
4267 --Declaring local variables
4268 l_api_name CONSTANT VARCHAR2(30) := 'cancel_add_request';
4269 l_api_version CONSTANT NUMBER := 1.0;
4270
4271
4272 CURSOR get_pol_id_csr(p_chr_id NUMBER)
4273 IS
4274 SELECT ID
4275 FROM OKL_POOLS
4276 WHERE KHR_ID = p_chr_id
4277 AND STATUS_CODE = 'ACTIVE';
4278
4279 CURSOR get_pox_id_csr(p_pol_id NUMBER)
4280 IS
4281 SELECT ID
4282 FROM OKL_POOL_TRANSACTIONS
4283 WHERE pol_id = p_pol_id
4284 AND TRANSACTION_STATUS <> 'COMPLETE';
4285
4286
4287 CURSOR get_pol_contents_csr(p_pox_id NUMBER)
4288 IS
4289 SELECT ID
4290 FROM OKL_POOL_CONTENTS
4291 WHERE pox_id = p_pox_id
4292 AND STATUS_CODE <> 'ACTIVE';
4293
4294
4295 CURSOR get_inv_stake_csr(p_khr_id NUMBER)
4296 IS
4297 SELECT TOP_KLE.ID,
4298 TOP_KLE.AMOUNT original_Stake,
4299 TOP_KLE.AMOUNT_STAKE additional_Stake
4300 FROM OKC_K_LINES_B TOP_LINE,
4301 OKL_K_LINES TOP_KLE,
4302 OKC_K_PARTY_ROLES_B PARTY_ROLE
4303 WHERE TOP_LINE.dnz_chr_id = p_khr_id
4304 AND TOP_KLE.ID = TOP_LINE.ID
4305 AND PARTY_ROLE.cle_id = TOP_LINE.id
4306 AND PARTY_ROLE.dnz_chr_id = TOP_LINE.dnz_chr_id
4307 AND PARTY_ROLE.rle_code = 'INVESTOR'
4308 AND PARTY_ROLE.jtot_object1_code = 'OKX_PARTY';
4309
4310
4311 l_pol_id NUMBER;
4312 l_pox_id NUMBER;
4313 i NUMBER;
4314 l_pocv_tbl OKL_POC_PVT.pocv_tbl_type;
4315 l_poxv_rec OKL_POX_PVT.poxv_rec_type;
4316 l_clev_rec clev_rec_type;
4317 l_klev_rec klev_rec_type;
4318 lx_clev_rec clev_rec_type;
4319 lx_klev_rec klev_rec_type;
4320
4321
4322
4323 BEGIN
4324 -- initialize return status
4325 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4326
4327
4328 x_return_status := OKL_API.START_ACTIVITY(
4329 p_api_name => l_api_name,
4330 p_pkg_name => g_pkg_name,
4331 p_init_msg_list => p_init_msg_list,
4332 l_api_version => l_api_version,
4333 p_api_version => p_api_version,
4334 p_api_type => G_API_TYPE,
4335 x_return_status => x_return_status);
4336
4337 -- check if activity started successfully
4338 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
4339 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4340 ElSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
4341 raise OKL_API.G_EXCEPTION_ERROR;
4342 End If;
4343
4344 FOR get_pol_id_rec IN get_pol_id_csr(p_chr_id)
4345 LOOP
4346 l_pol_id := get_pol_id_rec.id;
4347 END LOOP;
4348
4349 IF l_pol_id IS NULL
4350 THEN
4351 Okl_Api.set_message(G_APP_NAME,
4352 G_INVALID_VALUE,
4353 G_COL_NAME_TOKEN,
4354 'POOL_ID');
4355 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4356 END IF;
4357
4358 FOR get_pox_id_rec IN get_pox_id_csr(l_pol_id)
4359 LOOP
4360 l_pox_id := get_pox_id_rec.id;
4361 END LOOP;
4362
4363 IF l_pox_id IS NULL
4364 THEN
4365 Okl_Api.set_message(G_APP_NAME,
4366 G_INVALID_VALUE,
4367 G_COL_NAME_TOKEN,
4368 'POX_ID');
4369 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4370 END IF;
4371
4372 -- create table of pool contents to be deleted
4373 i:=0;
4374 FOR get_pol_contents_rec IN get_pol_contents_csr(l_pox_id)
4375 LOOP
4376 l_pocv_tbl(i).id:= get_pol_contents_rec.id;
4377 i:=i+1;
4378 END LOOP;
4379
4380 -- delete pool contents
4381 OKL_POC_PVT.delete_row(
4382 p_api_version => p_api_version,
4383 p_init_msg_list => p_init_msg_list,
4384 x_return_status => x_return_status,
4385 x_msg_count => x_msg_count,
4386 x_msg_data => x_msg_data,
4387 p_pocv_tbl => l_pocv_tbl);
4388
4389 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4390 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4391 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4392 RAISE OKL_API.G_EXCEPTION_ERROR;
4393 END IF;
4394
4395 -- Assign pool transaction to be deleted to the record
4396
4397 l_poxv_rec.id:= l_pox_id;
4398
4399 --delete pool transactions
4400
4401 OKL_POX_PVT.delete_row(
4402 p_api_version => p_api_version,
4403 p_init_msg_list => p_init_msg_list,
4404 x_return_status => x_return_status,
4405 x_msg_count => x_msg_count,
4406 x_msg_data => x_msg_data,
4407 p_poxv_rec => l_poxv_rec);
4408
4409 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4410 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4411 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4412 RAISE OKL_API.G_EXCEPTION_ERROR;
4413 END IF;
4414
4415 --Update the additional stake on removing transaction
4416 FOR get_inv_stake_rec IN get_inv_stake_csr(p_chr_id)
4417 LOOP
4418 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
4419 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'OKL_CONTRACT_PUB.update_contract_line');
4420 END IF;
4421
4422
4423 --Remove the additional stake amount
4424 IF get_inv_stake_rec.additional_stake IS NOT NULL THEN
4425 l_clev_rec.id := get_inv_stake_rec.id;
4426 l_klev_rec.id := get_inv_stake_rec.id;
4427 l_klev_rec.amount_stake := NULL;
4428
4429 OKL_CONTRACT_PUB.update_contract_line(
4430 p_api_version => p_api_version,
4431 p_init_msg_list => p_init_msg_list,
4432 x_return_status => x_return_status,
4433 x_msg_count => x_msg_count,
4434 x_msg_data => x_msg_data,
4435 p_clev_rec => l_clev_rec,
4436 p_klev_rec => l_klev_rec,
4437 x_clev_rec => lx_clev_rec,
4438 x_klev_rec => lx_klev_rec);
4439
4440 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
4441 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.cancel_add_request.debug', 'Okl_Sec_Investor_Pvt.update_investor' || x_return_status);
4442 END IF;
4443
4444 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4445 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4446 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4447 RAISE OKL_API.G_EXCEPTION_ERROR;
4448 END IF;
4449 END IF;
4450 END LOOP;
4451
4452
4453
4454 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4455
4456 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
4457
4458
4459 EXCEPTION
4460 when OKL_API.G_EXCEPTION_ERROR then
4461 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4462 p_api_name => l_api_name,
4463 p_pkg_name => g_pkg_name,
4464 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4465 x_msg_count => x_msg_count,
4466 x_msg_data => x_msg_data,
4467 p_api_type => g_api_type);
4468
4469 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
4470 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4471 p_api_name => l_api_name,
4472 p_pkg_name => g_pkg_name,
4473 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4474 x_msg_count => x_msg_count,
4475 x_msg_data => x_msg_data,
4476 p_api_type => g_api_type);
4477
4478 when OTHERS then
4479 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4480 p_api_name => l_api_name,
4481 p_pkg_name => g_pkg_name,
4482 p_exc_name => 'OTHERS',
4483 x_msg_count => x_msg_count,
4484 x_msg_data => x_msg_data,
4485 p_api_type => g_api_type);
4486
4487
4488 END cancel_add_request;
4489 /*sosharma end changes*/
4490
4491
4492 END Okl_Sec_Agreement_Pvt;