[Home] [Help]
PACKAGE BODY: APPS.OKL_SEC_AGREEMENT_PVT
Source
1 PACKAGE BODY Okl_Sec_Agreement_Pvt AS
2 /* $Header: OKLRSZAB.pls 120.20.12010000.3 2008/10/01 22:47:29 rkuttiya 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 lp_khrv_rec.SECURITIZATION_TYPE := p_secAgreement_rec.SECURITIZATION_TYPE;
1673 IF (p_secAgreement_rec.LESSOR_SERV_ORG_CODE IS NULL OR
1674 p_secAgreement_rec.LESSOR_SERV_ORG_CODE = OKL_API.G_MISS_CHAR) THEN
1675 lp_khrv_rec.LESSOR_SERV_ORG_CODE := 'O';
1676 ELSE
1677 lp_khrv_rec.LESSOR_SERV_ORG_CODE := p_secAgreement_rec.LESSOR_SERV_ORG_CODE;
1678 END IF;
1679 IF (p_secAgreement_rec.RECOURSE_CODE IS NULL OR p_secAgreement_rec.RECOURSE_CODE = OKL_API.G_MISS_CHAR) THEN
1680 lp_khrv_rec.RECOURSE_CODE := 'N';
1681 ELSE
1682 lp_khrv_rec.RECOURSE_CODE := p_secAgreement_rec.RECOURSE_CODE;
1683 END IF;
1684
1685 lp_khrv_rec.CURRENCY_CONVERSION_TYPE := p_secAgreement_rec.CURRENCY_CONVERSION_TYPE;
1686 lp_khrv_rec.CURRENCY_CONVERSION_RATE := p_secAgreement_rec.CURRENCY_CONVERSION_RATE;
1687 lp_khrv_rec.CURRENCY_CONVERSION_DATE := p_secAgreement_rec.CURRENCY_CONVERSION_DATE;
1688 lp_khrv_rec.AFTER_TAX_YIELD := p_secAgreement_rec.AFTER_TAX_YIELD;
1689
1690 -- arajagop Begin Changes for Attributes (Flexfield Support)
1691 lp_khrv_rec.ATTRIBUTE_CATEGORY := p_secAgreement_rec.ATTRIBUTE_CATEGORY;
1692 lp_khrv_rec.ATTRIBUTE1 := p_secAgreement_rec.ATTRIBUTE1;
1693 lp_khrv_rec.ATTRIBUTE2 := p_secAgreement_rec.ATTRIBUTE2;
1694 lp_khrv_rec.ATTRIBUTE3 := p_secAgreement_rec.ATTRIBUTE3;
1695 lp_khrv_rec.ATTRIBUTE4 := p_secAgreement_rec.ATTRIBUTE4;
1696 lp_khrv_rec.ATTRIBUTE5 := p_secAgreement_rec.ATTRIBUTE5;
1697 lp_khrv_rec.ATTRIBUTE6 := p_secAgreement_rec.ATTRIBUTE6;
1698 lp_khrv_rec.ATTRIBUTE7 := p_secAgreement_rec.ATTRIBUTE7;
1699 lp_khrv_rec.ATTRIBUTE8 := p_secAgreement_rec.ATTRIBUTE8;
1700 lp_khrv_rec.ATTRIBUTE9 := p_secAgreement_rec.ATTRIBUTE9;
1701 lp_khrv_rec.ATTRIBUTE10 := p_secAgreement_rec.ATTRIBUTE10;
1702 lp_khrv_rec.ATTRIBUTE11 := p_secAgreement_rec.ATTRIBUTE11;
1703 lp_khrv_rec.ATTRIBUTE12 := p_secAgreement_rec.ATTRIBUTE12;
1704 lp_khrv_rec.ATTRIBUTE13 := p_secAgreement_rec.ATTRIBUTE13;
1705 lp_khrv_rec.ATTRIBUTE14 := p_secAgreement_rec.ATTRIBUTE14;
1706 lp_khrv_rec.ATTRIBUTE15 := p_secAgreement_rec.ATTRIBUTE15;
1707 -- arajagop End Changes for Attributes (Flexfield Support)
1708 lp_khrv_rec.legal_entity_id := p_secAgreement_rec.legal_entity_id;
1709
1710 --
1711 -- Contract header specific validation
1712 --
1713 x_return_status := validate_header_attributes(p_secAgreement_rec, 'C');
1714 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1715 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1716 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1717 RAISE OKC_API.G_EXCEPTION_ERROR;
1718 END IF;
1719
1720 OKL_CONTRACT_PUB.validate_contract_header(
1721 p_api_version => p_api_version,
1722 p_init_msg_list => p_init_msg_list,
1723 x_return_status => x_return_status,
1724 x_msg_count => x_msg_count,
1725 x_msg_data => x_msg_data,
1726 p_chrv_rec => lp_chrv_rec,
1727 p_khrv_rec => lp_khrv_rec);
1728
1729 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1730 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1731 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1732 RAISE OKC_API.G_EXCEPTION_ERROR;
1733 END IF;
1734
1735 OKL_CONTRACT_PUB.create_contract_header(
1736 p_api_version => p_api_version,
1737 p_init_msg_list => p_init_msg_list,
1738 x_return_status => x_return_status,
1739 x_msg_count => x_msg_count,
1740 x_msg_data => x_msg_data,
1741 p_chrv_rec => lp_chrv_rec,
1742 p_khrv_rec => lp_khrv_rec,
1743 x_chrv_rec => lx_chrv_rec,
1744 x_khrv_rec => lx_khrv_rec);
1745
1746 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1747 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1748 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1749 RAISE OKC_API.G_EXCEPTION_ERROR;
1750 END IF;
1751
1752 x_secAgreement_rec.ID := lx_chrv_rec.id;
1753
1754 --
1755 -- ********************* Lessor ***************************************
1756 --
1757
1758 -- x_chr_id := lx_chrv_rec.id;
1759
1760 -- now we attach the party to the header
1761 lp_cplv_rec.object_version_number := 1.0;
1762 lp_cplv_rec.sfwt_flag := OKC_API.G_FALSE;
1763 lp_cplv_rec.dnz_chr_id := lx_chrv_rec.id;
1764 lp_cplv_rec.chr_id := lx_chrv_rec.id;
1765 lp_cplv_rec.cle_id := NULL;
1766 lp_cplv_rec.object1_id1 := lp_chrv_rec.authoring_org_id;
1767 lp_cplv_rec.object1_id2 := '#';
1768 lp_cplv_rec.jtot_object1_code := G_LESSOR_JTOT_OBJECT1_CODE;
1769 lp_cplv_rec.rle_code := G_LESSOR_RLE_CODE;
1770
1771
1772 OKC_CONTRACT_PARTY_PUB.validate_k_party_role(
1773 p_api_version => p_api_version,
1774 p_init_msg_list => p_init_msg_list,
1775 x_return_status => x_return_status,
1776 x_msg_count => x_msg_count,
1777 x_msg_data => x_msg_data,
1778 p_cplv_rec => lp_cplv_rec);
1779
1780
1781 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1782 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1783 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1784 RAISE OKC_API.G_EXCEPTION_ERROR;
1785 END IF;
1786
1787 ----- Changes by Kanti
1788 ----- Validate the JTOT Object code, ID1 and ID2
1789
1790 OPEN role_csr(lp_cplv_rec.rle_code);
1791 FETCH role_csr INTO l_access_level;
1792 CLOSE role_csr;
1793
1794 IF (l_access_level = 'S') THEN
1795
1796 okl_la_validation_util_pvt.VALIDATE_ROLE_JTOT (p_api_version => p_api_version,
1797 p_init_msg_list => OKC_API.G_FALSE,
1798 x_return_status => x_return_status,
1799 x_msg_count => x_msg_count,
1800 x_msg_data => x_msg_data,
1801 p_object_name => lp_cplv_rec.jtot_object1_code,
1802 p_id1 => lp_cplv_rec.object1_id1,
1803 p_id2 => lp_cplv_rec.object1_id2);
1804 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1805 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1806 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1807 RAISE OKC_API.G_EXCEPTION_ERROR;
1808 END IF;
1809
1810 END IF;
1811
1812 ---- Changes End
1813
1814
1815 OKC_CONTRACT_PARTY_PUB.create_k_party_role(
1816 p_api_version => p_api_version,
1817 p_init_msg_list => p_init_msg_list,
1818
1819 x_return_status => x_return_status,
1820 x_msg_count => x_msg_count,
1821 x_msg_data => x_msg_data,
1822 p_cplv_rec => lp_cplv_rec,
1823 x_cplv_rec => lx_cplv_rec);
1824
1825 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1826 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1827 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1828 RAISE OKC_API.G_EXCEPTION_ERROR;
1829 END IF;
1830
1831 --
1832 -- ************************************************************
1833 --
1834 --
1835 -- ********************* Trustee ***************************************
1836 --
1837
1838 -- x_chr_id := lx_chrv_rec.id;
1839
1840 -- now we attach the party to the header
1841 lp_cplv_rec.object_version_number := 1.0;
1842 lp_cplv_rec.sfwt_flag := OKC_API.G_FALSE;
1843 lp_cplv_rec.dnz_chr_id := lx_chrv_rec.id;
1844 lp_cplv_rec.chr_id := lx_chrv_rec.id;
1845 lp_cplv_rec.cle_id := NULL;
1846 lp_cplv_rec.object1_id1 := p_secAgreement_rec.trustee_object1_id1;
1847 lp_cplv_rec.object1_id2 := p_secAgreement_rec.trustee_object1_id2;
1848 lp_cplv_rec.jtot_object1_code := G_TRUSTEE_JTOT_OBJECT1_CODE;
1849 lp_cplv_rec.rle_code := G_TRUSTEE_RLE_CODE;
1850
1851 OKC_CONTRACT_PARTY_PUB.validate_k_party_role(
1852 p_api_version => p_api_version,
1853 p_init_msg_list => p_init_msg_list,
1854
1855 x_return_status => x_return_status,
1856 x_msg_count => x_msg_count,
1857 x_msg_data => x_msg_data,
1858 p_cplv_rec => lp_cplv_rec);
1859
1860
1861 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1862 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1863 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1864 RAISE OKC_API.G_EXCEPTION_ERROR;
1865 END IF;
1866
1867 ----- Changes by Kanti
1868 ----- Validate the JTOT Object code, ID1 and ID2
1869
1870
1871 OPEN role_csr(lp_cplv_rec.rle_code);
1872 FETCH role_csr INTO l_access_level;
1873 CLOSE role_csr;
1874
1875 IF (l_access_level = 'S') THEN
1876
1877 okl_la_validation_util_pvt.VALIDATE_ROLE_JTOT (p_api_version => p_api_version,
1878 p_init_msg_list => OKC_API.G_FALSE,
1879 x_return_status => x_return_status,
1880 x_msg_count => x_msg_count,
1881 x_msg_data => x_msg_data,
1882 p_object_name => lp_cplv_rec.jtot_object1_code,
1883 p_id1 => lp_cplv_rec.object1_id1,
1884 p_id2 => lp_cplv_rec.object1_id2);
1885 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1886 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1887 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1888 RAISE OKC_API.G_EXCEPTION_ERROR;
1889 END IF;
1890
1891 END IF;
1892
1893 ---- Changes End
1894
1895
1896 OKC_CONTRACT_PARTY_PUB.create_k_party_role(
1897 p_api_version => p_api_version,
1898 p_init_msg_list => p_init_msg_list,
1899 x_return_status => x_return_status,
1900 x_msg_count => x_msg_count,
1901 x_msg_data => x_msg_data,
1902 p_cplv_rec => lp_cplv_rec,
1903 x_cplv_rec => lx_cplv_rec);
1904
1905 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1906 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1907 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1908 RAISE OKC_API.G_EXCEPTION_ERROR;
1909 END IF;
1910
1911 --
1912 -- ************************************************************
1913 --
1914 x_secAgreement_rec.trustee_party_roles_id := lx_cplv_rec.id;
1915
1916 --
1917 -- update okl_pools
1918 --
1919 l_polv_rec.id := p_secAgreement_rec.pol_id;
1920 l_polv_rec.khr_id := x_secAgreement_rec.ID;
1921
1922 OKL_POOL_PVT.update_pool(
1923 p_api_version => p_api_version,
1924 p_init_msg_list => p_init_msg_list,
1925
1926 x_return_status => x_return_status,
1927 x_msg_count => x_msg_count,
1928 x_msg_data => x_msg_data,
1929 p_polv_rec => l_polv_rec,
1930 x_polv_rec => x_polv_rec);
1931
1932 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1933 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1934 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1935 RAISE OKL_API.G_EXCEPTION_ERROR;
1936 END IF;
1937
1938 /*** End API body ******************************************************/
1939
1940 -- Get message count and if count is 1, get message info
1941 FND_MSG_PUB.Count_And_Get
1942 (p_count => x_msg_count,
1943 p_data => x_msg_data);
1944
1945 EXCEPTION
1946 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1947 ROLLBACK TO create_sec_agreement_PVT;
1948 x_return_status := OKL_API.G_RET_STS_ERROR;
1949 FND_MSG_PUB.Count_And_Get
1950 (p_count => x_msg_count,
1951 p_data => x_msg_data);
1952
1953 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1954 ROLLBACK TO create_sec_agreement_PVT;
1955 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1956 FND_MSG_PUB.Count_And_Get
1957 (p_count => x_msg_count,
1958 p_data => x_msg_data);
1959
1960 WHEN OTHERS THEN
1961 ROLLBACK TO create_sec_agreement_PVT;
1962 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1963 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1964 p_msg_name => G_UNEXPECTED_ERROR,
1965 p_token1 => G_SQLCODE_TOKEN,
1966 p_token1_value => SQLCODE,
1967 p_token2 => G_SQLERRM_TOKEN,
1968 p_token2_value => SQLERRM);
1969 FND_MSG_PUB.Count_And_Get
1970 (p_count => x_msg_count,
1971 p_data => x_msg_data);
1972 END;
1973
1974 ----------------------------------------------------------------------------------
1975 -- Start of comments
1976 --
1977 -- Procedure Name : update_sec_agreement
1978 -- Description : updates a securitization agreement
1979 -- Business Rules :
1980 -- Parameters :
1981 -- Version : 1.0
1982 -- End of comments
1983 ----------------------------------------------------------------------------------
1984 PROCEDURE update_sec_agreement(
1985 p_api_version IN NUMBER
1986 ,p_init_msg_list IN VARCHAR2
1987 ,x_return_status OUT NOCOPY VARCHAR2
1988 ,x_msg_count OUT NOCOPY NUMBER
1989 ,x_msg_data OUT NOCOPY VARCHAR2
1990 ,p_secAgreement_rec IN secAgreement_rec_type
1991 ,x_secAgreement_rec OUT NOCOPY secAgreement_rec_type)
1992 IS
1993 l_api_name CONSTANT VARCHAR2(30) := 'update_sec_agreement_pvt';
1994 l_api_version CONSTANT NUMBER := 1.0;
1995 i NUMBER;
1996 l_pol_id OKL_POOLS.ID%TYPE;
1997 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1998 lp_cplv_rec OKC_CONTRACT_PARTY_PUB.cplv_rec_type;
1999 lx_cplv_rec OKC_CONTRACT_PARTY_PUB.cplv_rec_type;
2000
2001 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
2002 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
2003 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
2004 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
2005 l_polv_rec polv_rec_type;
2006 x_polv_rec polv_rec_type;
2007
2008 lp_clev_rec clev_rec_type;
2009 lx_clev_rec clev_rec_type;
2010 lp_klev_rec klev_rec_type;
2011 lx_klev_rec klev_rec_type;
2012 l_kle_id okc_k_lines_b.id%TYPE;
2013
2014 CURSOR c_pool_upd IS
2015 SELECT id
2016 FROM okl_pools
2017 WHERE khr_id = p_secAgreement_rec.id
2018 AND id <> p_secAgreement_rec.pol_id
2019 ;
2020
2021 -- mvasudev, 11/04/2003
2022 /*
2023 -- Replacing this by writing two cursors with top line first
2024 -- and sub lines next
2025 CURSOR c_agr_lns(p_khr_id okc_k_headers_b.id%TYPE) IS
2026 SELECT cle.id
2027 FROM apps.okc_k_lines_b cle
2028 WHERE cle.dnz_chr_id = p_khr_id
2029 ;
2030 */
2031 CURSOR l_okl_top_lines_csr(p_khr_id IN NUMBER)
2032 IS
2033 SELECT clet.id
2034 FROM okc_k_lines_b clet
2035 WHERE clet.dnz_chr_id = p_khr_id
2036 AND clet.cle_id IS NULL;
2037
2038 CURSOR l_okl_sub_lines_csr(p_khr_id IN NUMBER,p_kle_id IN NUMBER)
2039 IS
2040 SELECT cles.id
2041 FROM okc_k_lines_b cles
2042 WHERE cles.dnz_chr_id = p_khr_id
2043 AND cles.cle_id = p_kle_id;
2044 -- end, mvasudev changes, 11/04/2003
2045
2046 CURSOR role_csr(p_rle_code VARCHAR2) IS
2047 SELECT access_level
2048 FROM OKC_ROLE_SOURCES
2049 WHERE rle_code = p_rle_code
2050 AND buy_or_sell = 'S';
2051
2052 l_access_level OKC_ROLE_SOURCES.access_level%TYPE;
2053
2054
2055 BEGIN
2056 -- Set API savepoint
2057 SAVEPOINT update_sec_agreement_PVT;
2058
2059 -- Check for call compatibility
2060 IF (NOT FND_API.Compatible_API_Call (l_api_version,
2061 p_api_version,
2062 l_api_name,
2063 G_PKG_NAME ))
2064 THEN
2065 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2066 END IF;
2067
2068 -- Initialize message list if requested
2069 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
2070 FND_MSG_PUB.initialize;
2071 END IF;
2072
2073 -- Initialize API status to success
2074 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2075
2076
2077 /*** Begin API body ****************************************************/
2078 lp_chrv_rec.id := p_secAgreement_rec.ID;
2079 lp_chrv_rec.contract_number := p_secAgreement_rec.CONTRACT_NUMBER;
2080 lp_chrv_rec.sts_code := p_secAgreement_rec.STS_CODE;
2081 lp_chrv_rec.description := p_secAgreement_rec.short_description;
2082 lp_chrv_rec.short_description := p_secAgreement_rec.short_description;
2083 lp_chrv_rec.start_date := p_secAgreement_rec.start_date;
2084 lp_chrv_rec.end_date := p_secAgreement_rec.end_date;
2085 lp_chrv_rec.date_approved := p_secAgreement_rec.date_approved;
2086
2087 lp_chrv_rec.currency_code := p_secAgreement_rec.CURRENCY_CODE;
2088 lp_khrv_rec.PDT_ID := p_secAgreement_rec.PDT_ID;
2089 lp_khrv_rec.SECURITIZATION_TYPE := p_secAgreement_rec.SECURITIZATION_TYPE;
2090 lp_khrv_rec.LESSOR_SERV_ORG_CODE := p_secAgreement_rec.LESSOR_SERV_ORG_CODE;
2091 lp_khrv_rec.RECOURSE_CODE := p_secAgreement_rec.RECOURSE_CODE;
2092
2093 lp_khrv_rec.CURRENCY_CONVERSION_TYPE := p_secAgreement_rec.CURRENCY_CONVERSION_TYPE;
2094 lp_khrv_rec.CURRENCY_CONVERSION_RATE := p_secAgreement_rec.CURRENCY_CONVERSION_RATE;
2095 lp_khrv_rec.CURRENCY_CONVERSION_DATE := p_secAgreement_rec.CURRENCY_CONVERSION_DATE;
2096 -- added for AFTER_TAX_YIELD akjain,v115.23
2097 lp_khrv_rec.AFTER_TAX_YIELD := p_secAgreement_rec.AFTER_TAX_YIELD;
2098
2099 -- arajagop Begin Changes for Attributes (Flexfield Support)
2100 lp_khrv_rec.ATTRIBUTE_CATEGORY := p_secAgreement_rec.ATTRIBUTE_CATEGORY;
2101 lp_khrv_rec.ATTRIBUTE1 := p_secAgreement_rec.ATTRIBUTE1;
2102 lp_khrv_rec.ATTRIBUTE2 := p_secAgreement_rec.ATTRIBUTE2;
2103 lp_khrv_rec.ATTRIBUTE3 := p_secAgreement_rec.ATTRIBUTE3;
2104 lp_khrv_rec.ATTRIBUTE4 := p_secAgreement_rec.ATTRIBUTE4;
2105 lp_khrv_rec.ATTRIBUTE5 := p_secAgreement_rec.ATTRIBUTE5;
2106 lp_khrv_rec.ATTRIBUTE6 := p_secAgreement_rec.ATTRIBUTE6;
2107 lp_khrv_rec.ATTRIBUTE7 := p_secAgreement_rec.ATTRIBUTE7;
2108 lp_khrv_rec.ATTRIBUTE8 := p_secAgreement_rec.ATTRIBUTE8;
2109 lp_khrv_rec.ATTRIBUTE9 := p_secAgreement_rec.ATTRIBUTE9;
2110 lp_khrv_rec.ATTRIBUTE10 := p_secAgreement_rec.ATTRIBUTE10;
2111 lp_khrv_rec.ATTRIBUTE11 := p_secAgreement_rec.ATTRIBUTE11;
2112 lp_khrv_rec.ATTRIBUTE12 := p_secAgreement_rec.ATTRIBUTE12;
2113 lp_khrv_rec.ATTRIBUTE13 := p_secAgreement_rec.ATTRIBUTE13;
2114 lp_khrv_rec.ATTRIBUTE14 := p_secAgreement_rec.ATTRIBUTE14;
2115 lp_khrv_rec.ATTRIBUTE15 := p_secAgreement_rec.ATTRIBUTE15;
2116 -- arajagop End Changes for Attributes (Flexfield Support)
2117 lp_khrv_rec.legal_entity_id := p_secAgreement_rec.legal_entity_id;
2118
2119 --
2120 -- Contract header specific validation
2121 --
2122 x_return_status := validate_header_attributes(p_secAgreement_rec, 'U');
2123 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2124 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2125 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2126 RAISE OKC_API.G_EXCEPTION_ERROR;
2127 END IF;
2128
2129 OKL_CONTRACT_PUB.update_contract_header(
2130 p_api_version => p_api_version,
2131 p_init_msg_list => p_init_msg_list,
2132 x_return_status => x_return_status,
2133 x_msg_count => x_msg_count,
2134 x_msg_data => x_msg_data,
2135 p_chrv_rec => lp_chrv_rec,
2136 p_khrv_rec => lp_khrv_rec,
2137 x_chrv_rec => lx_chrv_rec,
2138 x_khrv_rec => lx_khrv_rec);
2139
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 -- copy back to x_secAgreement_rec
2147
2148 x_secAgreement_rec.id := lx_chrv_rec.ID;
2149 x_secAgreement_rec.contract_number := lx_chrv_rec.CONTRACT_NUMBER;
2150 x_secAgreement_rec.sts_code := lx_chrv_rec.STS_CODE;
2151 x_secAgreement_rec.short_description := lx_chrv_rec.short_description;
2152 x_secAgreement_rec.start_date := lx_chrv_rec.start_date;
2153 x_secAgreement_rec.end_date := lx_chrv_rec.end_date;
2154 x_secAgreement_rec.date_approved := lx_chrv_rec.date_approved;
2155
2156 x_secAgreement_rec.currency_code := lx_chrv_rec.CURRENCY_CODE;
2157
2158 x_secAgreement_rec.PDT_ID := lx_khrv_rec.PDT_ID;
2159 -- x_secAgreement_rec.POL_ID := lx_khrv_rec.POL_ID;
2160 x_secAgreement_rec.SECURITIZATION_TYPE := lx_khrv_rec.SECURITIZATION_TYPE;
2161 x_secAgreement_rec.LESSOR_SERV_ORG_CODE := lx_khrv_rec.LESSOR_SERV_ORG_CODE;
2162 x_secAgreement_rec.RECOURSE_CODE := lx_khrv_rec.RECOURSE_CODE;
2163 x_secAgreement_rec.CURRENCY_CONVERSION_TYPE := lx_khrv_rec.CURRENCY_CONVERSION_TYPE;
2164 x_secAgreement_rec.CURRENCY_CONVERSION_RATE := lx_khrv_rec.CURRENCY_CONVERSION_RATE;
2165 x_secAgreement_rec.CURRENCY_CONVERSION_DATE := lx_khrv_rec.CURRENCY_CONVERSION_DATE;
2166
2167 --
2168 -- ********************* Trustee ***************************************
2169 --
2170 IF (p_secAgreement_rec.trustee_party_roles_id IS NOT NULL
2171 AND p_secAgreement_rec.trustee_party_roles_id <> OKL_API.G_MISS_NUM) THEN
2172
2173 -- x_chr_id := lx_chrv_rec.id;
2174
2175 -- now we attach the party to the header
2176 -- lp_cplv_rec.object_version_number := 1.0;
2177 -- lp_cplv_rec.sfwt_flag := OKC_API.G_FALSE;
2178
2179 -- lp_cplv_rec.dnz_chr_id := lx_chrv_rec.id;
2180 -- lp_cplv_rec.chr_id := lx_chrv_rec.id;
2181 -- lp_cplv_rec.cle_id := null;
2182 lp_cplv_rec.id := p_secAgreement_rec.trustee_party_roles_id;
2183 lp_cplv_rec.object1_id1 := p_secAgreement_rec.trustee_object1_id1;
2184 lp_cplv_rec.object1_id2 := p_secAgreement_rec.trustee_object1_id2;
2185 -- lp_cplv_rec.jtot_object1_code := G_TRUSTEE_JTOT_OBJECT1_CODE;
2186 -- lp_cplv_rec.rle_code := G_TRUSTEE_RLE_CODE;
2187
2188 OKC_CONTRACT_PARTY_PUB.validate_k_party_role(
2189 p_api_version => p_api_version,
2190 p_init_msg_list => p_init_msg_list,
2191 x_return_status => x_return_status,
2192 x_msg_count => x_msg_count,
2193 x_msg_data => x_msg_data,
2194 p_cplv_rec => lp_cplv_rec);
2195
2196
2197 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2198 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2199 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2200 RAISE OKC_API.G_EXCEPTION_ERROR;
2201 END IF;
2202
2203 ----- Changes by Kanti
2204 ----- Validate the JTOT Object code, ID1 and ID2
2205
2206 OPEN role_csr(lp_cplv_rec.rle_code);
2207 FETCH role_csr INTO l_access_level;
2208 CLOSE role_csr;
2209
2210 IF (l_access_level = 'S') THEN
2211
2212 okl_la_validation_util_pvt.VALIDATE_ROLE_JTOT (p_api_version => p_api_version,
2213 p_init_msg_list => OKC_API.G_FALSE,
2214 x_return_status => x_return_status,
2215 x_msg_count => x_msg_count,
2216 x_msg_data => x_msg_data,
2217 p_object_name => lp_cplv_rec.jtot_object1_code,
2218 p_id1 => lp_cplv_rec.object1_id1,
2219 p_id2 => lp_cplv_rec.object1_id2);
2220 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2221 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2222 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2223 RAISE OKC_API.G_EXCEPTION_ERROR;
2224 END IF;
2225
2226 END IF;
2227
2228 ---- Changes End
2229
2230 OKC_CONTRACT_PARTY_PUB.update_k_party_role(
2231 p_api_version => p_api_version,
2232 p_init_msg_list => p_init_msg_list,
2233 x_return_status => x_return_status,
2234 x_msg_count => x_msg_count,
2235 x_msg_data => x_msg_data,
2236 p_cplv_rec => lp_cplv_rec,
2237 x_cplv_rec => lx_cplv_rec);
2238
2239 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2240 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2241 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2242 RAISE OKC_API.G_EXCEPTION_ERROR;
2243 END IF;
2244
2245 -- copy back to x_secAgreement_rec
2246 x_secAgreement_rec.trustee_party_roles_id := lx_cplv_rec.id;
2247 x_secAgreement_rec.trustee_object1_id1 := lx_cplv_rec.object1_id1;
2248 x_secAgreement_rec.trustee_object1_id2 := lx_cplv_rec.object1_id2;
2249 x_secAgreement_rec.trustee_jtot_object1_code := lx_cplv_rec.jtot_object1_code;
2250
2251 END IF;
2252
2253 --
2254 -- ************************************************************
2255 --
2256
2257 --
2258 -- update okl_pools
2259 --
2260
2261 IF (p_secAgreement_rec.pol_id IS NOT NULL AND p_secAgreement_rec.pol_id <> OKL_API.G_MISS_NUM) THEN
2262 -- 1. update khr_id to null if user switch to different pol_id
2263
2264 OPEN c_pool_upd;
2265 i := 0;
2266 LOOP
2267 FETCH c_pool_upd INTO
2268 l_pol_id;
2269 EXIT WHEN c_pool_upd%NOTFOUND;
2270
2271 l_polv_rec.id := l_pol_id;
2272 l_polv_rec.khr_id := NULL;
2273
2274 OKL_POOL_PVT.update_pool(
2275 p_api_version => p_api_version,
2276 p_init_msg_list => p_init_msg_list,
2277 x_return_status => x_return_status,
2278 x_msg_count => x_msg_count,
2279 x_msg_data => x_msg_data,
2280 p_polv_rec => l_polv_rec,
2281 x_polv_rec => x_polv_rec);
2282
2283
2284 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2285 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2286 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2287 RAISE OKL_API.G_EXCEPTION_ERROR;
2288 END IF;
2289
2290 i := i + 1;
2291 END LOOP;
2292 CLOSE c_pool_upd;
2293
2294 -- 2. update khr_id to a specific pool header
2295
2296 l_polv_rec.id := p_secAgreement_rec.pol_id;
2297 l_polv_rec.khr_id := p_secAgreement_rec.ID;
2298
2299 OKL_POOL_PVT.update_pool(
2300 p_api_version => p_api_version,
2301 p_init_msg_list => p_init_msg_list,
2302 x_return_status => x_return_status,
2303 x_msg_count => x_msg_count,
2304 x_msg_data => x_msg_data,
2305 p_polv_rec => l_polv_rec,
2306 x_polv_rec => x_polv_rec);
2307
2308 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2309 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2310 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2311 RAISE OKL_API.G_EXCEPTION_ERROR;
2312 END IF;
2313
2314 -- copy back to x_secAgreement_rec
2315 x_secAgreement_rec.POL_ID := x_polv_rec.ID;
2316
2317 END IF;
2318 ----------------------------------------------------------------------------
2319 -- update agreement contract header lines end_date = header.end_date
2320 -- cascade update all associated line end_date = header.end_date
2321 -- loop
2322 ----------------------------------------------------------------------------
2323 -- mvasudev, commented , 11/04/2003
2324 -- Replacing this by writing two cursors with top line first
2325 -- and sub lines next
2326 /*
2327 OPEN c_agr_lns(p_secAgreement_rec.ID);
2328 i := 0;
2329 LOOP
2330 FETCH c_agr_lns INTO
2331 l_kle_id;
2332 EXIT WHEN c_agr_lns%NOTFOUND;
2333
2334
2335 lp_klev_rec.id := l_kle_id;
2336 lp_clev_rec.start_date := p_secAgreement_rec.start_date;
2337 lp_clev_rec.end_date := p_secAgreement_rec.end_date;
2338 lp_clev_rec.id := l_kle_id;
2339
2340
2341 okl_contract_pub.update_contract_line(
2342 p_api_version => p_api_version,
2343 p_init_msg_list => p_init_msg_list,
2344 x_return_status => x_return_status,
2345 x_msg_count => x_msg_count,
2346 x_msg_data => x_msg_data,
2347 p_clev_rec => lp_clev_rec,
2348 p_klev_rec => lp_klev_rec,
2349 x_clev_rec => lx_clev_rec,
2350 x_klev_rec => lx_klev_rec);
2351
2352 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2353 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2354 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2355 RAISE OKL_API.G_EXCEPTION_ERROR;
2356 END IF;
2357
2358 i := i + 1;
2359 END LOOP;
2360 CLOSE c_agr_lns;
2361 */
2362
2363 FOR l_okl_top_line_rec IN l_okl_top_lines_csr(p_secAgreement_rec.ID)
2364 LOOP
2365 -- Update Top Lines first
2366 lp_klev_rec.id := l_okl_top_line_rec.id;
2367 lp_clev_rec.start_date := p_secAgreement_rec.start_date;
2368 lp_clev_rec.end_date := p_secAgreement_rec.end_date;
2369 lp_clev_rec.id := l_okl_top_line_rec.id;
2370
2371
2372 okl_contract_pub.update_contract_line(
2373 p_api_version => p_api_version,
2374 p_init_msg_list => p_init_msg_list,
2375 x_return_status => x_return_status,
2376 x_msg_count => x_msg_count,
2377 x_msg_data => x_msg_data,
2378 p_clev_rec => lp_clev_rec,
2379 p_klev_rec => lp_klev_rec,
2380 x_clev_rec => lx_clev_rec,
2381 x_klev_rec => lx_klev_rec);
2382
2383 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2384 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2385 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2386 RAISE OKL_API.G_EXCEPTION_ERROR;
2387 END IF;
2388
2389 FOR l_okl_sub_line_rec IN l_okl_sub_lines_csr(p_secAgreement_rec.ID,l_okl_top_line_rec.id)
2390 LOOP
2391 -- Update Sub Lines next
2392 lp_klev_rec.id := l_okl_sub_line_rec.id;
2393 lp_clev_rec.start_date := p_secAgreement_rec.start_date;
2394 lp_clev_rec.end_date := p_secAgreement_rec.end_date;
2395 lp_clev_rec.id := l_okl_sub_line_rec.id;
2396
2397
2398 okl_contract_pub.update_contract_line(
2399 p_api_version => p_api_version,
2400 p_init_msg_list => p_init_msg_list,
2401 x_return_status => x_return_status,
2402 x_msg_count => x_msg_count,
2403 x_msg_data => x_msg_data,
2404 p_clev_rec => lp_clev_rec,
2405 p_klev_rec => lp_klev_rec,
2406 x_clev_rec => lx_clev_rec,
2407 x_klev_rec => lx_klev_rec);
2408
2409 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2410 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2411 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2412 RAISE OKL_API.G_EXCEPTION_ERROR;
2413 END IF;
2414
2415 END LOOP;
2416 END LOOP;
2417 -- end, mvasudev , changes , 11/04/2003
2418
2419 /*** End API body ******************************************************/
2420
2421 -- Get message count and if count is 1, get message info
2422 FND_MSG_PUB.Count_And_Get
2423 (p_count => x_msg_count,
2424 p_data => x_msg_data);
2425
2426 EXCEPTION
2427 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2428 ROLLBACK TO update_sec_agreement_PVT;
2429 x_return_status := OKL_API.G_RET_STS_ERROR;
2430
2431 FND_MSG_PUB.Count_And_Get
2432 (p_count => x_msg_count,
2433 p_data => x_msg_data);
2434
2435 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2436 ROLLBACK TO update_sec_agreement_PVT;
2437 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2438 FND_MSG_PUB.Count_And_Get
2439 (p_count => x_msg_count,
2440 p_data => x_msg_data);
2441
2442 WHEN OTHERS THEN
2443 ROLLBACK TO update_sec_agreement_PVT;
2444 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
2445 OKL_API.Set_Message(p_app_name => G_APP_NAME,
2446 p_msg_name => G_UNEXPECTED_ERROR,
2447 p_token1 => G_SQLCODE_TOKEN,
2448 p_token1_value => SQLCODE,
2449 p_token2 => G_SQLERRM_TOKEN,
2450 p_token2_value => SQLERRM);
2451 FND_MSG_PUB.Count_And_Get
2452 (p_count => x_msg_count,
2453 p_data => x_msg_data);
2454
2455 END;
2456
2457
2458 ----------------------------------------------------------------------------------
2459 -- Start of comments
2460 --
2461 -- Procedure Name : activate_sec_agreement
2462 -- Description : activate a securitization agreement
2463 -- Business Rules :
2464 -- Parameters :
2465 -- Version : 1.0
2466 -- End of comments
2467 ----------------------------------------------------------------------------------
2468 PROCEDURE activate_sec_agreement(
2469 p_api_version IN NUMBER
2470 ,p_init_msg_list IN VARCHAR2
2471 ,x_return_status OUT NOCOPY VARCHAR2
2472 ,x_msg_count OUT NOCOPY NUMBER
2473 ,x_msg_data OUT NOCOPY VARCHAR2
2474 ,p_khr_id IN OKC_K_HEADERS_B.ID%TYPE)
2475 IS
2476 l_api_name CONSTANT VARCHAR2(30) := 'activate_sec_agreement_pvt';
2477 l_api_version CONSTANT NUMBER := 1.0;
2478 i NUMBER;
2479 l_kle_id OKC_K_LINES_B.ID%TYPE;
2480 l_pol_id OKL_POOLS.ID%TYPE;
2481 l_currency_code OKL_POOLS.CURRENCY_CODE%TYPE;
2482 l_org_id OKL_POOLS.ORG_ID%TYPE;
2483 l_legal_entity_id OKL_POOLS.LEGAL_ENTITY_ID%TYPE;
2484 l_chr_id OKC_K_HEADERS_B.ID%TYPE;
2485
2486 l_pox_id OKL_POOL_CONTENTS.POX_ID%TYPE;
2487 l_poc_id OKL_POOL_CONTENTS.ID%TYPE;
2488 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2489 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
2490 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
2491 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
2492 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
2493
2494
2495
2496 lp_secagreement_rec secagreement_rec_type;
2497 lx_secagreement_rec secagreement_rec_type;
2498 l_row_found BOOLEAN := FALSE;
2499 l_row_not_found BOOLEAN := FALSE;
2500 lp_poxv_rec poxv_rec_type;
2501 lx_poxv_rec poxv_rec_type;
2502 lp_pocv_rec pocv_rec_type;
2503 lx_pocv_rec pocv_rec_type;
2504 lp_pocv2_rec pocv_rec_type;
2505 lx_pocv2_rec pocv_rec_type;
2506
2507
2508 CURSOR c_pool IS
2509 SELECT pol.id,
2510 pol.currency_code,
2511 pol.org_id,
2512 pol.legal_entity_id
2513 FROM okl_pools pol
2514 WHERE pol.khr_id = p_khr_id
2515 ;
2516
2517 CURSOR c_poc IS
2518 SELECT poc.id
2519 FROM okl_pool_contents poc
2520 WHERE poc.pol_id = l_pol_id
2521 ;
2522
2523 -- mvasudev, Fixed bug#3987171
2524 /*
2525 CURSOR c_pool_chr IS
2526 SELECT poc.khr_id
2527 FROM apps.okl_pool_contents poc
2528 WHERE poc.pol_id = l_pol_id
2529 ;
2530 */
2531 CURSOR c_pool_chr IS
2532 SELECT poc.khr_id
2533 FROM okl_pool_contents poc
2534 WHERE poc.pol_id = l_pol_id
2535 ;
2536
2537 /* ankushar Bug# 6773285 Added Principal Payment for Loan contracts also renamed Cursor
2538 Start Changes
2539 */
2540 -- mvasudev, 10/29/2003
2541 -- Cursor to check if sty is securitized ">=" effective_date
2542 CURSOR l_okl_sty_csr IS
2543 SELECT 1
2544 FROM okl_pools polb
2545 ,okl_strm_type_b styb
2546 ,okl_pool_contents pocb
2547 WHERE polb.khr_id = p_khr_id
2548 AND pocb.pol_id = polb.id
2549 AND pocb.sty_id = styb.id
2550 AND styb.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
2551 AND pocb.status_code='NEW';
2552 /* ankushar Bug# 6773285
2553 End Changes
2554 */
2555
2556 BEGIN
2557 -- Set API savepoint
2558 SAVEPOINT activate_sec_agreement_PVT;
2559
2560 -- Check for call compatibility
2561 IF (NOT FND_API.Compatible_API_Call (l_api_version,
2562 p_api_version,
2563 l_api_name,
2564 G_PKG_NAME ))
2565 THEN
2566 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2567 END IF;
2568
2569 -- Initialize message list if requested
2570 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
2571 FND_MSG_PUB.initialize;
2572 END IF;
2573
2574 -- Initialize API status to success
2575 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2576
2577 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2578 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Begin Activating IA');
2579 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug','p_khr_id:'|| p_khr_id);
2580 END IF;
2581
2582 /*** Begin API body ****************************************************/
2583 /* process steps
2584 --1 create a transaction for entire pool by pol_id
2585 --2 update pool contents to point to this transaction
2586 --3 Mark associated contract to securitizated
2587 --4 call Stream Generator API to generate streams for income and expense fees
2588 --5 call Streams Generator API to generate disbursement basis streams
2589 --6 call Streams Generator API to generate PV Streams of Securitized Streams
2590 --7 call Accrual API
2591 --8 call BPD AR api
2592 --9 call generate_journal_entries
2593 --10 call update_sec_agreement_sts to update agreement header, lines
2594 --11 update pool header and contents status to active
2595 */
2596
2597 ----------------------------------------------------------------------------
2598 --1 create a transaction for entire pool by pol_id
2599 -- initial transaction when pool become active
2600 --OKL_POOL_TRANSACTION_TYPE
2601 ----------------------------------------------------------------------------
2602 --dbms_output.put_line('1. CREATE a TRANSACTION FOR entire pool BY pol_id');
2603 --dbms_output.put_line('OKL_POOL_PVT.create_pool_transaction START');
2604 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2605 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');
2606 END IF;
2607 OPEN c_pool;
2608 FETCH c_pool INTO l_pol_id,
2609 l_currency_code,
2610 l_org_id,
2611 l_legal_entity_id;
2612 l_row_found := c_pool%FOUND;
2613 CLOSE c_pool;
2614 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2615 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'l_pol_id :' || l_pol_id);
2616 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'l_currency_code :' || l_currency_code);
2617 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'l_org_id :' || l_org_id);
2618 END IF;
2619
2620 --dbms_output.put_line('l_pol_id' || l_pol_id);
2621 --dbms_output.put_line('l_currency_code'|| l_currency_code);
2622 --dbms_output.put_line('l_org_id'|| l_org_id);
2623
2624 IF l_row_found THEN
2625
2626 lp_poxv_rec.POL_ID := l_pol_id;
2627 lp_poxv_rec.TRANSACTION_DATE := SYSDATE;
2628 lp_poxv_rec.TRANSACTION_TYPE := G_POOL_TRX_ADD;
2629 lp_poxv_rec.TRANSACTION_REASON := G_POOL_TRX_REASON_ACTIVE;
2630 lp_poxv_rec.CURRENCY_CODE := l_currency_code;
2631 --sosharma 03/12/2007 added to enable status on pool transaction
2632 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_COMPLETE;
2633 --added abhsaxen for Legal Entity Uptake
2634 lp_poxv_rec.LEGAL_ENTITY_ID := l_legal_entity_id;
2635 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2636 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Calling okl_pool_pvt.create_pool_transaction');
2637 END IF;
2638
2639 OKL_POOL_PVT.create_pool_transaction(
2640 p_api_version => p_api_version,
2641 p_init_msg_list => p_init_msg_list,
2642 x_return_status => x_return_status,
2643 x_msg_count => x_msg_count,
2644 x_msg_data => x_msg_data,
2645 p_poxv_rec => lp_poxv_rec,
2646 x_poxv_rec => lx_poxv_rec);
2647 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2648 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'l_pol_id :' || l_pol_id);
2649 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);
2650 END IF;
2651
2652 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2653 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2654 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2655 RAISE OKL_API.G_EXCEPTION_ERROR;
2656 END IF;
2657 END IF;
2658 --dbms_output.put_line('OKL_POOL_PVT.create_pool_transaction END');
2659 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2660 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'OKL_POOL_PVT.create_pool_transaction: END');
2661 END IF;
2662
2663 ----------------------------------------------------------------------------
2664 -- get pox_id after create trx entry
2665 --2. update pool contents to point to this transaction
2666 -- make association between pool transaction and pool contents' records
2667 ----------------------------------------------------------------------------
2668 -- loop
2669 --dbms_output.put_line('2. update pool contents to point to this transaction');
2670 --dbms_output.put_line('OKL_POOL_PVT.update_pool_contents Start');
2671 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2672 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');
2673 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');
2674 END IF;
2675
2676 OPEN c_poc;
2677 LOOP
2678 FETCH c_poc INTO
2679 l_poc_id;
2680 EXIT WHEN c_poc%NOTFOUND;
2681
2682 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2683 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'l_pol_id :' || l_pol_id);
2684 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'l_poc_id :' || l_poc_id);
2685 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);
2686 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);
2687 END IF;
2688
2689 lp_pocv_rec.ID := l_poc_id;
2690 lp_pocv_rec.POL_ID := l_pol_id;
2691 lp_pocv_rec.POX_ID := lx_poxv_rec.id;
2692 lp_pocv_rec.TRANSACTION_NUMBER_IN := lx_poxv_rec.TRANSACTION_NUMBER;
2693
2694 OKL_POOL_PVT.update_pool_contents(
2695 p_api_version => p_api_version,
2696 p_init_msg_list => p_init_msg_list,
2697 x_return_status => x_return_status,
2698 x_msg_count => x_msg_count,
2699 x_msg_data => x_msg_data,
2700 p_pocv_rec => lp_pocv_rec,
2701 x_pocv_rec => lx_pocv_rec);
2702
2703 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2704 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'l_poc_id :' || l_poc_id);
2705 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);
2706 END IF;
2707
2708 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2709 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2710 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2711 RAISE OKL_API.G_EXCEPTION_ERROR;
2712 END IF;
2713
2714 END LOOP;
2715 CLOSE c_poc;
2716 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2717 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'OKL_POOL_PVT.update_pool_contents : END');
2718 END IF;
2719
2720 --dbms_output.put_line('OKL_POOL_PVT.update_pool_contents End');
2721
2722 ----------------------------------------------------------------------------
2723 --3 Mark associated contract to securitizated
2724 ----------------------------------------------------------------------------
2725 --dbms_output.put_line('3. Mark associated contract to securtizated');
2726 --dbms_output.put_line('okl_contract_pub.update_contract_header start');
2727 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2728 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '3. Mark associated contract to securtizated.');
2729 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');
2730 END IF;
2731
2732
2733 OPEN c_pool_chr;
2734 LOOP
2735 FETCH c_pool_chr INTO
2736 l_chr_id;
2737 EXIT WHEN c_pool_chr%NOTFOUND;
2738
2739 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2740 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'l_chr_id :' || l_chr_id);
2741 END IF;
2742
2743 lp_chrv_rec.id := l_chr_id;
2744 lp_khrv_rec.id := l_chr_id;
2745 lp_khrv_rec.SECURITIZED_CODE := G_SECURITIZED_CODE_Y;
2746
2747 okl_contract_pub.update_contract_header(
2748 p_api_version => p_api_version,
2749 p_init_msg_list => p_init_msg_list,
2750 x_return_status => x_return_status,
2751 x_msg_count => x_msg_count,
2752 x_msg_data => x_msg_data,
2753 p_chrv_rec => lp_chrv_rec,
2754 p_khrv_rec => lp_khrv_rec,
2755 x_chrv_rec => lx_chrv_rec,
2756 x_khrv_rec => lx_khrv_rec);
2757
2758 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2759 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);
2760 END IF;
2761
2762 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2763 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2764 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2765 RAISE OKL_API.G_EXCEPTION_ERROR;
2766 END IF;
2767
2768 END LOOP;
2769 CLOSE c_pool_chr;
2770 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2771 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'okl_contract_pub.update_contract_header: END');
2772 END IF;
2773
2774 -- gboomina added for Bug 6763287 - Start
2775 ----------------------------------------------------------------------------
2776 --4 call Streams Generator API to generate streams for income and expense fees
2777 ----------------------------------------------------------------------------
2778 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2779 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');
2780 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');
2781 END IF;
2782 OKL_STREAM_GENERATOR_PVT.generate_streams_for_IA(
2783 p_api_version => p_api_version,
2784 p_init_msg_list => p_init_msg_list,
2785 p_khr_id => p_khr_id,
2786 x_return_status => x_return_status,
2787 x_msg_count => x_msg_count ,
2788 x_msg_data => x_msg_data );
2789
2790 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2791 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);
2792 END IF;
2793
2794 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2795 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2796 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2797 RAISE OKL_API.G_EXCEPTION_ERROR;
2798 END IF;
2799 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2800 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');
2801 END IF;
2802 -- gboomina added for Bug 6763287 - End
2803 -- added by zrehman for making entry into okl_k_control Bug#6788005 on 07-Feb-2008 start
2804 OKL_BILLING_CONTROLLER_PVT.track_next_bill_date(p_khr_id);
2805 -- added by zrehman for making entry into okl_k_control Bug#6788005 on 07-Feb-2008 end
2806 ----------------------------------------------------------------------------
2807
2808 --5 call Streams Generator API to generate disbursement basis streams
2809 --
2810 ----------------------------------------------------------------------------
2811 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2812 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.');
2813 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');
2814 END IF;
2815
2816 OKL_STREAM_GENERATOR_PVT.create_disb_streams(
2817 p_api_version => p_api_version,
2818 p_init_msg_list => p_init_msg_list,
2819 p_agreement_id => p_khr_id,
2820 x_return_status => x_return_status,
2821 x_msg_count => x_msg_count ,
2822 x_msg_data => x_msg_data );
2823 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2824 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);
2825 END IF;
2826
2827 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2828 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2829 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2830 RAISE OKL_API.G_EXCEPTION_ERROR;
2831 END IF;
2832 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2833 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');
2834 END IF;
2835
2836 ----------------------------------------------------------------------------
2837
2838 --6 call Streams Generator API to generate PV Streams of Securitized Streams
2839 --
2840 ----------------------------------------------------------------------------
2841 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2842 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');
2843 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');
2844 END IF;
2845
2846 OKL_STREAM_GENERATOR_PVT.create_pv_streams(
2847 p_api_version => p_api_version,
2848 p_init_msg_list => p_init_msg_list,
2849 p_agreement_id => p_khr_id,
2850 x_return_status => x_return_status,
2851 x_msg_count => x_msg_count ,
2852 x_msg_data => x_msg_data );
2853 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2854 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);
2855 END IF;
2856
2857 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2858 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2859 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2860 RAISE OKL_API.G_EXCEPTION_ERROR;
2861 END IF;
2862 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2863 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');
2864 END IF;
2865
2866
2867 ----------------------------------------------------------------------------
2868 --7 call Accrual API
2869 --
2870 ----------------------------------------------------------------------------
2871 --dbms_output.put_line('OKL_SECURITIZE_ACCRUAL_PVT.CREATE_STREAMS begin');
2872 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2873 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '7 call to the Accrual API: START');
2874 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Calling okl_accrual_sec_pvt.create_streams');
2875 END IF;
2876
2877
2878 -- OKL_SECURITIZE_ACCRUAL_PVT.CREATE_STREAMS(
2879
2880 -- mvasudev, 10/29/2003
2881 -- Call the Accrual API only when there are RENT streams
2882 -- This should loop EXACTLY one time
2883 FOR l_okl_sty_rent_rec IN l_okl_sty_csr
2884 LOOP
2885 OKL_ACCRUAL_SEC_PVT.CREATE_STREAMS(
2886 p_api_version => p_api_version,
2887 p_init_msg_list => p_init_msg_list,
2888 x_return_status => x_return_status,
2889 x_msg_count => x_msg_count,
2890 x_msg_data => x_msg_data,
2891 p_khr_id => p_khr_id);
2892
2893 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2894 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);
2895 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);
2896 END IF;
2897
2898 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2899 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2900 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2901 RAISE OKL_API.G_EXCEPTION_ERROR;
2902 END IF;
2903 -- Fixed Bug#3386816, mvasudev
2904 EXIT WHEN l_okl_sty_csr%FOUND;
2905 END LOOP;
2906 -- mvasudev, end, 10/29/2003
2907 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2908 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '7 call to the Accrual API: END');
2909 END IF;
2910
2911 --dbms_output.put_line('OKL_SECURITIZE_ACCRUAL_PVT.CREATE_STREAMS end');
2912 ----------------------------------------------------------------------------
2913 --8 call BPD AR api
2914 --
2915 ----------------------------------------------------------------------------
2916 --dbms_output.put_line('Okl_Investor_Billing_Pvt.create_investor_bill begin');
2917 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2918 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '8 call BPD Billing API: START');
2919 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');
2920 END IF;
2921
2922 Okl_Investor_Billing_Pvt.create_investor_bill(
2923 p_api_version => p_api_version,
2924 p_init_msg_list => p_init_msg_list,
2925 x_return_status => x_return_status,
2926 x_msg_count => x_msg_count,
2927 x_msg_data => x_msg_data,
2928 p_inv_agr => p_khr_id);
2929
2930 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2931 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);
2932 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);
2933 END IF;
2934
2935 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2936 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2937 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2938 RAISE OKL_API.G_EXCEPTION_ERROR;
2939 END IF;
2940
2941 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2942 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', '8 call BPD Billing API: END');
2943 END IF;
2944
2945 --dbms_output.put_line('Okl_Investor_Billing_Pvt.create_investor_bill end');
2946
2947 ----------------------------------------------------------------------------
2948 --9 call generate_journal_entries
2949 --
2950 ----------------------------------------------------------------------------
2951 --dbms_output.put_line('generate_journal_entries begin');
2952 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2953 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');
2954 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'Calling generate_journal_entries');
2955 END IF;
2956
2957 generate_journal_entries(
2958 p_api_version => p_api_version,
2959 p_init_msg_list => p_init_msg_list,
2960 x_return_status => x_return_status,
2961 x_msg_count => x_msg_count,
2962 x_msg_data => x_msg_data,
2963 p_contract_id => p_khr_id
2964 ,p_transaction_type => G_TRY_TYPE_INV);
2965
2966 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2967 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);
2968 END IF;
2969
2970 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2971 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2972 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2973 RAISE OKL_API.G_EXCEPTION_ERROR;
2974 END IF;
2975
2976 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2977 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');
2978 END IF;
2979
2980 --dbms_output.put_line('generate_journal_entries end');
2981 ----------------------------------------------------------------------------
2982 --10 call update_sec_agreement_sts to update agreement header, lines
2983 --
2984 ----------------------------------------------------------------------------
2985 --dbms_output.put_line('OKL_SEC_AGREEMENT_PVT.update_sec_agreement_sts begin');
2986
2987 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2988 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');
2989 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');
2990 END IF;
2991
2992 Okl_Sec_Agreement_Pvt.update_sec_agreement_sts(
2993 p_api_version => p_api_version,
2994 p_init_msg_list => p_init_msg_list,
2995 x_return_status => x_return_status,
2996 x_msg_count => x_msg_count,
2997 x_msg_data => x_msg_data,
2998 p_sec_agreement_status => G_STS_CODE_ACTIVE,
2999 p_sec_agreement_id => p_khr_id);
3000
3001 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3002 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);
3003 END IF;
3004
3005 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3006 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3007 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3008 RAISE OKL_API.G_EXCEPTION_ERROR;
3009 END IF;
3010
3011 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3012 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');
3013 END IF;
3014 --dbms_output.put_line('OKL_SEC_AGREEMENT_PVT.update_sec_agreement_sts end');
3015
3016 ----------------------------------------------------------------------------
3017 --11 update pool header and contents status to active
3018 ----------------------------------------------------------------------------
3019 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3020 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');
3021 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');
3022 END IF;
3023
3024 OKL_POOL_PVT.update_pool_status_active(
3025 p_api_version => p_api_version,
3026 p_init_msg_list => p_init_msg_list,
3027 x_return_status => x_return_status,
3028 x_msg_count => x_msg_count,
3029 x_msg_data => x_msg_data,
3030 p_pol_id => l_pol_id);
3031
3032 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3033 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);
3034 END IF;
3035
3036 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3037 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3038 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3039 RAISE OKL_API.G_EXCEPTION_ERROR;
3040 END IF;
3041
3042 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3043 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');
3044 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_sec_agreement.debug', 'End of Activating IA, completed Successfully');
3045 END IF;
3046
3047
3048 /*** End API body ******************************************************/
3049
3050 -- Get message count and if count is 1, get message info
3051 FND_MSG_PUB.Count_And_Get
3052 (p_count => x_msg_count,
3053 p_data => x_msg_data);
3054
3055 EXCEPTION
3056 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3057 ROLLBACK TO activate_sec_agreement_PVT;
3058 x_return_status := OKL_API.G_RET_STS_ERROR;
3059 FND_MSG_PUB.Count_And_Get
3060 (p_count => x_msg_count,
3061 p_data => x_msg_data);
3062
3063 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3064 ROLLBACK TO activate_sec_agreement_PVT;
3065 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3066 FND_MSG_PUB.Count_And_Get
3067 (p_count => x_msg_count,
3068 p_data => x_msg_data);
3069
3070 WHEN OTHERS THEN
3071 ROLLBACK TO activate_sec_agreement_PVT;
3072 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3073 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3074 p_msg_name => G_UNEXPECTED_ERROR,
3075 p_token1 => G_SQLCODE_TOKEN,
3076 p_token1_value => SQLCODE,
3077 p_token2 => G_SQLERRM_TOKEN,
3078 p_token2_value => SQLERRM);
3079 FND_MSG_PUB.Count_And_Get
3080 (p_count => x_msg_count,
3081 p_data => x_msg_data);
3082
3083 END activate_sec_agreement;
3084
3085 ----------------------------------------------------------------------------------
3086 -- Start of comments
3087 --
3088 -- Procedure Name : update_sec_agreement_sts
3089 -- Description : updates a securitization agreement header, all lines status
3090 -- Business Rules :
3091 -- Parameters :
3092 -- Version : 1.0
3093 -- End of comments
3094 ----------------------------------------------------------------------------------
3095 PROCEDURE update_sec_agreement_sts(
3096 p_api_version IN NUMBER
3097 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
3098 ,x_return_status OUT NOCOPY VARCHAR2
3099 ,x_msg_count OUT NOCOPY NUMBER
3100 ,x_msg_data OUT NOCOPY VARCHAR2
3101 ,p_sec_agreement_status IN okc_k_headers_b.sts_code%TYPE
3102 ,p_sec_agreement_id IN okc_k_headers_b.id%TYPE)
3103 IS
3104 l_api_name CONSTANT VARCHAR2(30) := 'update_sec_agreement_sts';
3105 l_api_version CONSTANT NUMBER := 1.0;
3106 i NUMBER;
3107 l_kle_id OKC_K_LINES_B.ID%TYPE;
3108 l_chr_id OKC_K_HEADERS_B.ID%TYPE;
3109
3110 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3111
3112 lp_secagreement_rec secagreement_rec_type;
3113 lx_secagreement_rec secagreement_rec_type;
3114 l_row_found BOOLEAN := FALSE;
3115 l_row_not_found BOOLEAN := FALSE;
3116
3117 lp_clev_rec clev_rec_type;
3118 lx_clev_rec clev_rec_type;
3119 lp_klev_rec klev_rec_type;
3120 lx_klev_rec klev_rec_type;
3121
3122 lp_khrv_rec khrv_rec_type;
3123 lx_khrv_rec khrv_rec_type;
3124 lp_chrv_rec chrv_rec_type;
3125 lx_chrv_rec chrv_rec_type;
3126 -- mvasudev, Fixed bug#3987171
3127 /*
3128 CURSOR c_agr_lns IS
3129 SELECT cle.id
3130 FROM apps.okc_k_lines_b cle
3131 WHERE cle.dnz_chr_id = p_sec_agreement_id
3132 ;
3133 */
3134 CURSOR c_agr_lns IS
3135 SELECT cle.id
3136 FROM okc_k_lines_b cle
3137 WHERE cle.dnz_chr_id = p_sec_agreement_id
3138 ;
3139
3140 BEGIN
3141 -- Set API savepoint
3142 SAVEPOINT update_sec_agreement_sts_PVT;
3143
3144 -- Check for call compatibility
3145 IF (NOT FND_API.Compatible_API_Call (l_api_version,
3146 p_api_version,
3147 l_api_name,
3148 G_PKG_NAME ))
3149 THEN
3150 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3151 END IF;
3152
3153 -- Initialize message list if requested
3154 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
3155 FND_MSG_PUB.initialize;
3156 END IF;
3157
3158 -- Initialize API status to success
3159 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3160
3161
3162 /*** Begin API body ****************************************************/
3163 ----------------------------------------------------------------------------
3164 --1. update agreement contract header status
3165 ----------------------------------------------------------------------------
3166 --dbms_output.put_line('1. update agreement contract header status');
3167 --dbms_output.put_line('OKL_SEC_AGREEMENT_PVT.update_sec_agreement start');
3168
3169 lp_secagreement_rec.ID := p_sec_agreement_id;
3170 lp_secagreement_rec.STS_CODE := p_sec_agreement_status;
3171
3172 Okl_Sec_Agreement_Pvt.update_sec_agreement(
3173 p_api_version => p_api_version,
3174 p_init_msg_list => p_init_msg_list,
3175 x_return_status => x_return_status,
3176 x_msg_count => x_msg_count,
3177 x_msg_data => x_msg_data,
3178 p_secagreement_rec => lp_secagreement_rec,
3179 x_secagreement_rec => lx_secagreement_rec);
3180
3181 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3182 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3183 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3184 RAISE OKL_API.G_EXCEPTION_ERROR;
3185 END IF;
3186 --dbms_output.put_line('OKL_SEC_AGREEMENT_PVT.update_sec_agreement end');
3187
3188 ----------------------------------------------------------------------------
3189 --2. update agreement contract header lines status
3190 -- cascade update all associated line sts_code to active
3191 -- loop
3192 ----------------------------------------------------------------------------
3193 --dbms_output.put_line('2. update agreement contract header lines status');
3194 --dbms_output.put_line('okl_contract_pub.update_contract_line start');
3195
3196 OPEN c_agr_lns;
3197 LOOP
3198 FETCH c_agr_lns INTO
3199 l_kle_id;
3200 EXIT WHEN c_agr_lns%NOTFOUND;
3201
3202
3203 lp_klev_rec.id := l_kle_id;
3204 lp_clev_rec.STS_CODE := p_sec_agreement_status;
3205 lp_clev_rec.id := l_kle_id;
3206
3207 okl_contract_pub.update_contract_line(
3208 p_api_version => p_api_version,
3209 p_init_msg_list => p_init_msg_list,
3210 x_return_status => x_return_status,
3211 x_msg_count => x_msg_count,
3212 x_msg_data => x_msg_data,
3213 p_clev_rec => lp_clev_rec,
3214 p_klev_rec => lp_klev_rec,
3215 x_clev_rec => lx_clev_rec,
3216 x_klev_rec => lx_klev_rec);
3217
3218 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3219 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3220 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3221 RAISE OKL_API.G_EXCEPTION_ERROR;
3222 END IF;
3223
3224 END LOOP;
3225 CLOSE c_agr_lns;
3226 --dbms_output.put_line('okl_contract_pub.update_contract_line start');
3227
3228 /*** End API body ******************************************************/
3229
3230 -- Get message count and if count is 1, get message info
3231 FND_MSG_PUB.Count_And_Get
3232 (p_count => x_msg_count,
3233 p_data => x_msg_data);
3234
3235 EXCEPTION
3236 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3237 ROLLBACK TO update_sec_agreement_sts_PVT;
3238 x_return_status := OKL_API.G_RET_STS_ERROR;
3239 FND_MSG_PUB.Count_And_Get
3240 (p_count => x_msg_count,
3241 p_data => x_msg_data);
3242
3243 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3244 ROLLBACK TO update_sec_agreement_sts_PVT;
3245 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3246 FND_MSG_PUB.Count_And_Get
3247 (p_count => x_msg_count,
3248 p_data => x_msg_data);
3249
3250 WHEN OTHERS THEN
3251 ROLLBACK TO update_sec_agreement_sts_PVT;
3252 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3253 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3254 p_msg_name => G_UNEXPECTED_ERROR,
3255 p_token1 => G_SQLCODE_TOKEN,
3256 p_token1_value => SQLCODE,
3257 p_token2 => G_SQLERRM_TOKEN,
3258 p_token2_value => SQLERRM);
3259 FND_MSG_PUB.Count_And_Get
3260 (p_count => x_msg_count,
3261 p_data => x_msg_data);
3262
3263 END update_sec_agreement_sts;
3264
3265 --Added by kthiruva on 18-Dec-2007
3266 -- New method to validate an add request on an active investor agreement
3267 --Bug 6691554 - Start of Changes
3268 Procedure validate_add_request(
3269 p_api_version IN NUMBER,
3270 p_init_msg_list IN VARCHAR2,
3271 x_return_status OUT NOCOPY VARCHAR2,
3272 x_msg_count OUT NOCOPY NUMBER,
3273 x_msg_data OUT NOCOPY VARCHAR2,
3274 p_chr_id IN NUMBER)
3275 IS
3276 --Declaring local variables
3277 l_stream_value NUMBER;
3278 l_api_name CONSTANT VARCHAR2(30) := 'validate_add_request';
3279 l_api_version CONSTANT NUMBER := 1.0;
3280
3281
3282 CURSOR get_pol_id_csr(p_chr_id NUMBER)
3283 IS
3284 SELECT ID
3285 FROM OKL_POOLS
3286 WHERE KHR_ID = p_chr_id
3287 AND STATUS_CODE = 'ACTIVE';
3288
3289 l_pol_id NUMBER;
3290 x_reconciled VARCHAR2(1);
3291
3292 BEGIN
3293 -- initialize return status
3294 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3295
3296
3297 x_return_status := OKL_API.START_ACTIVITY(
3298 p_api_name => l_api_name,
3299 p_pkg_name => g_pkg_name,
3300 p_init_msg_list => p_init_msg_list,
3301 l_api_version => l_api_version,
3302 p_api_version => p_api_version,
3303 p_api_type => G_API_TYPE,
3304 x_return_status => x_return_status);
3305
3306 -- check if activity started successfully
3307 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3308 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3309 ElSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3310 raise OKL_API.G_EXCEPTION_ERROR;
3311 End If;
3312
3313 FOR get_pol_id_rec IN get_pol_id_csr(p_chr_id)
3314 LOOP
3315 l_pol_id := get_pol_id_rec.id;
3316 END LOOP;
3317
3318 IF l_pol_id IS NULL
3319 THEN
3320 Okl_Api.set_message(G_APP_NAME,
3321 G_INVALID_VALUE,
3322 G_COL_NAME_TOKEN,
3323 'POOL_ID');
3324 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3325 END IF;
3326
3327 OKL_POOL_PVT.get_tot_recei_amt_pend(
3328 p_api_version => '1.0',
3329 p_init_msg_list => p_init_msg_list,
3330 x_return_status => x_return_status,
3331 x_msg_count => x_msg_count,
3332 x_msg_data => x_msg_data,
3333 x_value => l_stream_value,
3334 p_pol_id => l_pol_id );
3335
3336 If( l_stream_value IS NULL OR l_stream_value = 0 ) Then
3337 OKL_API.set_message(
3338 p_app_name => G_APP_NAME,
3339 p_msg_name => 'OKL_QA_STREAM_VALUE');
3340 -- notify caller of an error but do not raise an exception
3341 x_return_status := OKL_API.G_RET_STS_ERROR;
3342 END IF;
3343
3344 --Check to see if the pool recquires reconcilation
3345 OKL_POOL_PVT.reconcile_contents(
3346 p_api_version => p_api_version,
3347 p_init_msg_list => p_init_msg_list,
3348 x_return_status => x_return_status,
3349 x_msg_count => x_msg_count,
3350 x_msg_data => x_msg_data,
3351 p_pol_id => l_pol_id,
3352 p_mode => 'ACTIVE',
3353 x_reconciled => x_reconciled );
3354
3355 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3356 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3357 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3358 RAISE OKL_API.G_EXCEPTION_ERROR;
3359 END IF;
3360
3361 IF ( x_reconciled = OKL_API.G_TRUE ) Then
3362 x_return_status := OKL_API.G_RET_STS_ERROR;
3363 OKL_API.set_message(
3364 p_app_name => G_APP_NAME,
3365 p_msg_name => 'OKL_LLA_RECONCILED');
3366 raise OKL_API.G_EXCEPTION_ERROR;
3367 End If;
3368
3369 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3370
3371 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
3372
3373
3374 EXCEPTION
3375 when OKL_API.G_EXCEPTION_ERROR then
3376 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3377 p_api_name => l_api_name,
3378 p_pkg_name => g_pkg_name,
3379 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3380 x_msg_count => x_msg_count,
3381 x_msg_data => x_msg_data,
3382 p_api_type => g_api_type);
3383
3384 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
3385 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3386 p_api_name => l_api_name,
3387 p_pkg_name => g_pkg_name,
3388 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3389 x_msg_count => x_msg_count,
3390 x_msg_data => x_msg_data,
3391 p_api_type => g_api_type);
3392
3393 when OTHERS then
3394 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3395 p_api_name => l_api_name,
3396 p_pkg_name => g_pkg_name,
3397 p_exc_name => 'OTHERS',
3398 x_msg_count => x_msg_count,
3399 x_msg_data => x_msg_data,
3400 p_api_type => g_api_type);
3401
3402
3403 END validate_add_request;
3404
3405 Procedure activate_add_request (
3406 p_api_version IN NUMBER
3407 ,p_init_msg_list IN VARCHAR2
3408 ,x_return_status OUT NOCOPY VARCHAR2
3409 ,x_msg_count OUT NOCOPY NUMBER
3410 ,x_msg_data OUT NOCOPY VARCHAR2
3411 ,p_khr_id IN OKC_K_HEADERS_B.ID%TYPE)
3412 IS
3413
3414 l_api_name CONSTANT VARCHAR2(30) := 'activate_add_request';
3415 l_api_version CONSTANT NUMBER := 1.0;
3416 i NUMBER;
3417 l_kle_id OKC_K_LINES_B.ID%TYPE;
3418 l_pol_id OKL_POOLS.ID%TYPE;
3419 l_currency_code OKL_POOLS.CURRENCY_CODE%TYPE;
3420 l_org_id OKL_POOLS.ORG_ID%TYPE;
3421 l_legal_entity_id OKL_POOLS.LEGAL_ENTITY_ID%TYPE;
3422 l_chr_id OKC_K_HEADERS_B.ID%TYPE;
3423
3424 l_pox_id OKL_POOL_CONTENTS.POX_ID%TYPE;
3425 l_poc_id OKL_POOL_CONTENTS.ID%TYPE;
3426 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3427
3428 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
3429 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
3430 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
3431 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
3432
3433
3434
3435 lp_secagreement_rec secagreement_rec_type;
3436 lx_secagreement_rec secagreement_rec_type;
3437 l_row_found BOOLEAN := FALSE;
3438 l_row_not_found BOOLEAN := FALSE;
3439 lp_poxv_rec poxv_rec_type;
3440 lx_poxv_rec poxv_rec_type;
3441 lp_pocv_rec pocv_rec_type;
3442 lx_pocv_rec pocv_rec_type;
3443 lp_pocv2_rec pocv_rec_type;
3444 lx_pocv2_rec pocv_rec_type;
3445
3446
3447 CURSOR c_pool_csr(p_khr_id NUMBER)
3448 IS
3449 SELECT pol.id
3450 FROM okl_pools pol
3451 WHERE pol.khr_id = p_khr_id;
3452
3453 CURSOR c_pool_chr_csr(p_pol_id NUMBER)
3454 IS
3455 SELECT poc.khr_id,
3456 poc.id
3457 FROM okl_pool_contents poc
3458 WHERE poc.pol_id = p_pol_id
3459 AND poc.status_code = Okl_Pool_Pvt.G_POC_STS_PENDING;
3460
3461 /* ankushar Bug# 6773285 Added Principal Payment for Loan contracts also renamed Cursor
3462 Start Changes
3463 */
3464 -- mvasudev, 10/29/2003
3465 -- Cursor to check if sty is securitized ">=" effective_date
3466 CURSOR l_okl_sty_csr(p_khr_id NUMBER)
3467 IS
3468 SELECT 1
3469 FROM okl_pools polb
3470 ,okl_strm_type_b styb
3471 ,okl_pool_contents pocb
3472 WHERE polb.khr_id = p_khr_id
3473 AND pocb.pol_id = polb.id
3474 AND pocb.sty_id = styb.id
3475 AND styb.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
3476 AND pocb.status_code='PENDING';
3477 /* ankushar Bug# 6773285
3478 End Changes
3479 */
3480 CURSOR get_pox_csr(p_pol_id NUMBER)
3481 IS
3482 SELECT POX.ID
3483 FROM OKL_POOL_TRANSACTIONS POX
3484 WHERE POX.POL_ID = p_pol_id
3485 AND POX.TRANSACTION_STATUS = 'APPROVED'
3486 AND POX.TRANSACTION_TYPE = 'ADD'
3487 AND POX.TRANSACTION_REASON = 'ADJUSTMENTS';
3488
3489 --Cursor to fetch the investor stake
3490 --Cursor to fetch the investor stake
3491 CURSOR get_inv_stake_csr(p_khr_id NUMBER)
3492 IS
3493 SELECT TOP_KLE.ID,
3494 TOP_KLE.AMOUNT original_Stake,
3495 TOP_KLE.AMOUNT_STAKE additional_Stake
3496 FROM OKC_K_LINES_B TOP_LINE,
3497 OKL_K_LINES TOP_KLE,
3498 OKC_K_PARTY_ROLES_B PARTY_ROLE
3499 WHERE TOP_LINE.dnz_chr_id = p_khr_id
3500 AND TOP_KLE.ID = TOP_LINE.ID
3501 AND PARTY_ROLE.cle_id = TOP_LINE.id
3502 AND PARTY_ROLE.dnz_chr_id = TOP_LINE.dnz_chr_id
3503 AND PARTY_ROLE.rle_code = 'INVESTOR'
3504 AND PARTY_ROLE.jtot_object1_code = 'OKX_PARTY';
3505
3506 l_clev_rec clev_rec_type;
3507 l_klev_rec klev_rec_type;
3508 lx_clev_rec clev_rec_type;
3509 lx_klev_rec klev_rec_type;
3510
3511 BEGIN
3512 -- Set API savepoint
3513 SAVEPOINT activate_add_request_pvt;
3514
3515 -- Check for call compatibility
3516 IF (NOT FND_API.Compatible_API_Call (l_api_version,
3517 p_api_version,
3518 l_api_name,
3519 G_PKG_NAME ))
3520 THEN
3521 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3522 END IF;
3523
3524 -- Initialize message list if requested
3525 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
3526 FND_MSG_PUB.initialize;
3527 END IF;
3528
3529 -- Initialize API status to success
3530 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3531
3532 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3533 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Begin Processing Add Request');
3534 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug','p_khr_id:'|| p_khr_id);
3535 END IF;
3536
3537 /*** Begin API body ****************************************************/
3538 /* process steps
3539 --1 Validate the add request
3540 --2 Mark associated contract to securitizated
3541 --3 call Streams Generator API to generate disbursement basis streams
3542 --4 call Streams Generator API to generate PV Streams of Securitized Streams
3543 --5 call Accrual API
3544 --6 call generate_journal_entries
3545 --7 call BPD AR api
3546 --8 update pool header and contents status to active
3547 --9 Update the stake amount per investor and clear out the amount_stake field
3548
3549 */
3550
3551 ----------------------------------------------------------------------------
3552 --1 Validate the add request
3553 ----------------------------------------------------------------------------
3554 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3555 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '1. Validate the add request.');
3556 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Calling validate_add_request: START');
3557 END IF;
3558
3559 --Fetch the pool id from the Investor Agreement Id passed
3560 FOR c_pool_rec IN c_pool_csr(p_khr_id)
3561 LOOP
3562 l_pol_id := c_pool_rec.id;
3563 END LOOP;
3564
3565 IF l_pol_id IS NULL
3566 THEN
3567 Okl_Api.set_message(G_APP_NAME,
3568 G_INVALID_VALUE,
3569 G_COL_NAME_TOKEN,
3570 'POOL_ID');
3571 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3572 END IF;
3573
3574 --Fetch the transaction id of the Pool Transaction
3575 FOR get_pox_rec IN get_pox_csr(l_pol_id)
3576 LOOP
3577 l_pox_id := get_pox_rec.id;
3578 END LOOP;
3579
3580 IF l_pox_id IS NULL
3581 THEN
3582 Okl_Api.set_message(G_APP_NAME,
3583 G_INVALID_VALUE,
3584 G_COL_NAME_TOKEN,
3585 'POX_ID');
3586 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3587 END IF;
3588
3589 validate_add_request(
3590 p_api_version => p_api_version,
3591 p_init_msg_list => p_init_msg_list,
3592 x_return_status => x_return_status,
3593 x_msg_count => x_msg_count,
3594 x_msg_data => x_msg_data,
3595 p_chr_id => p_khr_id);
3596
3597 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3598 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);
3599 END IF;
3600
3601 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3602 --Update the status of the Pool Transaction to INCOMPLETE and Halt processing
3603 lp_poxv_rec.id := l_pox_id;
3604 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_INCOMPLETE;
3605
3606 OKL_POOL_PVT.update_pool_transaction(p_api_version => p_api_version,
3607 p_init_msg_list => p_init_msg_list,
3608 x_return_status => x_return_status,
3609 x_msg_count => x_msg_count,
3610 x_msg_data => x_msg_data,
3611 p_poxv_rec => lp_poxv_rec,
3612 x_poxv_rec => lx_poxv_rec);
3613
3614 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3615 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3616 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
3617 RAISE OKL_API.G_EXCEPTION_ERROR;
3618 END IF;
3619
3620 --Stop submit processing
3621 RAISE G_HALT_PROCESSING;
3622 END IF;
3623
3624
3625 ----------------------------------------------------------------------------
3626 --2 Mark associated contract to securitizated
3627 ----------------------------------------------------------------------------
3628 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3629 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '2. Mark associated contract to securtizated.');
3630 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');
3631 END IF;
3632
3633 OPEN c_pool_chr_csr(l_pol_id);
3634 LOOP
3635 FETCH c_pool_chr_csr INTO l_chr_id,l_poc_id;
3636 EXIT WHEN c_pool_chr_csr%NOTFOUND;
3637
3638 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3639 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'l_chr_id :' || l_chr_id);
3640 END IF;
3641
3642 lp_chrv_rec.id := l_chr_id;
3643 lp_khrv_rec.id := l_chr_id;
3644 lp_khrv_rec.SECURITIZED_CODE := G_SECURITIZED_CODE_Y;
3645
3646 okl_contract_pub.update_contract_header(
3647 p_api_version => p_api_version,
3648 p_init_msg_list => p_init_msg_list,
3649 x_return_status => x_return_status,
3650 x_msg_count => x_msg_count,
3651 x_msg_data => x_msg_data,
3652 p_chrv_rec => lp_chrv_rec,
3653 p_khrv_rec => lp_khrv_rec,
3654 x_chrv_rec => lx_chrv_rec,
3655 x_khrv_rec => lx_khrv_rec);
3656
3657 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3658 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);
3659 END IF;
3660
3661 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3662 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3663 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3664 RAISE OKL_API.G_EXCEPTION_ERROR;
3665 END IF;
3666
3667 END LOOP;
3668 CLOSE c_pool_chr_csr;
3669 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3670 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'okl_contract_pub.update_contract_header: END');
3671 END IF;
3672
3673
3674 ----------------------------------------------------------------------------
3675
3676 -- 3. call Streams Generator API to generate disbursement basis streams
3677 --
3678 ----------------------------------------------------------------------------
3679 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3680 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.');
3681 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');
3682 END IF;
3683
3684 OKL_STREAM_GENERATOR_PVT.create_disb_streams(
3685 p_api_version => p_api_version,
3686 p_init_msg_list => p_init_msg_list,
3687 p_agreement_id => p_khr_id,
3688 p_pool_status => 'ACTIVE',
3689 p_mode => 'ACTIVE',
3690 x_return_status => x_return_status,
3691 x_msg_count => x_msg_count ,
3692 x_msg_data => x_msg_data );
3693
3694 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3695 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);
3696 END IF;
3697
3698 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3699 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3700 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3701 RAISE OKL_API.G_EXCEPTION_ERROR;
3702 END IF;
3703
3704 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3705 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');
3706 END IF;
3707
3708 ----------------------------------------------------------------------------
3709
3710 --4 call Streams Generator API to generate PV Streams of Securitized Streams
3711 --
3712 ----------------------------------------------------------------------------
3713 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3714 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');
3715 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');
3716 END IF;
3717
3718 OKL_STREAM_GENERATOR_PVT.create_pv_streams(
3719 p_api_version => p_api_version,
3720 p_init_msg_list => p_init_msg_list,
3721 p_agreement_id => p_khr_id,
3722 p_pool_status => 'ACTIVE',
3723 p_mode => 'ACTIVE',
3724 x_return_status => x_return_status,
3725 x_msg_count => x_msg_count ,
3726 x_msg_data => x_msg_data );
3727
3728 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3729 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);
3730 END IF;
3731
3732 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3733 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3734 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3735 RAISE OKL_API.G_EXCEPTION_ERROR;
3736 END IF;
3737
3738 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3739 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');
3740 END IF;
3741
3742
3743 ----------------------------------------------------------------------------
3744 --5 call Accrual API
3745 --
3746 ----------------------------------------------------------------------------
3747 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3748 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '5 call to the Accrual API: START');
3749 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Calling okl_accrual_sec_pvt.create_streams');
3750 END IF;
3751
3752
3753 -- mvasudev, 10/29/2003
3754 -- Call the Accrual API only when there are RENT streams
3755 -- This should loop EXACTLY one time
3756 FOR l_okl_sty_rent_rec IN l_okl_sty_csr(p_khr_id)
3757 LOOP
3758 OKL_ACCRUAL_SEC_PVT.CREATE_STREAMS(
3759 p_api_version => p_api_version,
3760 p_init_msg_list => p_init_msg_list,
3761 x_return_status => x_return_status,
3762 x_msg_count => x_msg_count,
3763 x_msg_data => x_msg_data,
3764 p_khr_id => p_khr_id,
3765 p_mode => 'ACTIVE');
3766
3767 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3768 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);
3769 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);
3770 END IF;
3771
3772 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3773 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3774 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3775 RAISE OKL_API.G_EXCEPTION_ERROR;
3776 END IF;
3777 -- Fixed Bug#3386816, mvasudev
3778 EXIT WHEN l_okl_sty_csr%FOUND;
3779 END LOOP;
3780 -- mvasudev, end, 10/29/2003
3781 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3782 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '5 call to the Accrual API: END');
3783 END IF;
3784
3785 ----------------------------------------------------------------------------
3786 --6 call generate_journal_entries
3787 --
3788 ----------------------------------------------------------------------------
3789 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3790 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');
3791 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Calling generate_journal_entries');
3792 END IF;
3793
3794 generate_journal_entries(
3795 p_api_version => p_api_version,
3796 p_init_msg_list => p_init_msg_list,
3797 x_return_status => x_return_status,
3798 x_msg_count => x_msg_count,
3799 x_msg_data => x_msg_data,
3800 p_contract_id => p_khr_id
3801 ,p_transaction_type => G_TRY_TYPE_INV);
3802
3803 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3804 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);
3805 END IF;
3806
3807 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3808 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3809 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3810 RAISE OKL_API.G_EXCEPTION_ERROR;
3811 END IF;
3812
3813 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3814 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');
3815 END IF;
3816
3817 ----------------------------------------------------------------------------
3818 --7 call BPD AR api
3819 --
3820 ----------------------------------------------------------------------------
3821 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3822 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '7 call BPD Billing API: START');
3823 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');
3824 END IF;
3825
3826 Okl_Investor_Billing_Pvt.create_investor_bill(
3827 p_api_version => p_api_version,
3828 p_init_msg_list => p_init_msg_list,
3829 x_return_status => x_return_status,
3830 x_msg_count => x_msg_count,
3831 x_msg_data => x_msg_data,
3832 p_inv_agr => p_khr_id);
3833
3834 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3835 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);
3836 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);
3837 END IF;
3838
3839 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3840 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3841 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3842 RAISE OKL_API.G_EXCEPTION_ERROR;
3843 END IF;
3844
3845 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3846 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '7 call BPD Billing API: END');
3847 END IF;
3848
3849 --Update the total stake to include the additional stake
3850 FOR get_inv_stake_rec IN get_inv_stake_csr(p_khr_id)
3851 LOOP
3852 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3853 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'OKL_CONTRACT_PUB.update_contract_line');
3854 END IF;
3855
3856 --Only if an additional stake amount has been captured, then add this stake to the original stake
3857 IF get_inv_stake_rec.additional_stake IS NOT NULL THEN
3858 l_clev_rec.id := get_inv_stake_rec.id;
3859 l_klev_rec.id := get_inv_stake_rec.id;
3860 l_klev_rec.amount := get_inv_stake_rec.original_stake + get_inv_stake_rec.additional_stake;
3861 l_klev_rec.amount_stake := NULL;
3862
3863 OKL_CONTRACT_PUB.update_contract_line(
3864 p_api_version => p_api_version,
3865 p_init_msg_list => p_init_msg_list,
3866 x_return_status => x_return_status,
3867 x_msg_count => x_msg_count,
3868 x_msg_data => x_msg_data,
3869 p_clev_rec => l_clev_rec,
3870 p_klev_rec => l_klev_rec,
3871 x_clev_rec => lx_clev_rec,
3872 x_klev_rec => lx_klev_rec);
3873
3874 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3875 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);
3876 END IF;
3877
3878 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3879 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3880 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3881 RAISE OKL_API.G_EXCEPTION_ERROR;
3882 END IF;
3883 END IF;
3884 END LOOP;
3885
3886
3887
3888
3889 ----------------------------------------------------------------------------------
3890 --8 update pool contents status to active and pool transactions status to COMPLETE
3891 ----------------------------------------------------------------------------------
3892 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3893 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');
3894 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Calling OKL_POOL_PVT.update_pool_contents');
3895 END IF;
3896
3897 --Update pool contents to ACTIVE
3898 FOR c_pool_chr_rec IN c_pool_chr_csr(l_pol_id)
3899 LOOP
3900 lp_pocv_rec.id := c_pool_chr_rec.id;
3901 lp_pocv_rec.status_code := Okl_Pool_Pvt.G_POC_STS_ACTIVE;
3902
3903 Okl_Pool_Pvt.update_pool_contents(
3904 p_api_version => p_api_version,
3905 p_init_msg_list => p_init_msg_list,
3906 x_return_status => x_return_status,
3907 x_msg_count => x_msg_count,
3908 x_msg_data => x_msg_data,
3909 p_pocv_rec => lp_pocv_rec,
3910 x_pocv_rec => lx_pocv_rec);
3911
3912 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3913 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);
3914 END IF;
3915
3916
3917 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3918 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3919 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3920 RAISE Okl_Api.G_EXCEPTION_ERROR;
3921 END IF;
3922 END LOOP;
3923
3924
3925 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3926 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', '8 update pool transaction to Complete: START');
3927 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Calling OKL_POOL_PVT.update_pool_transaction');
3928 END IF;
3929
3930 --Update pool transaction to COMPLETE
3931 lp_poxv_rec.id := l_pox_id;
3932 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_COMPLETE;
3933
3934 OKL_POOL_PVT.update_pool_transaction(p_api_version => p_api_version,
3935 p_init_msg_list => p_init_msg_list,
3936 x_return_status => x_return_status,
3937 x_msg_count => x_msg_count,
3938 x_msg_data => x_msg_data,
3939 p_poxv_rec => lp_poxv_rec,
3940 x_poxv_rec => lx_poxv_rec);
3941
3942 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3943 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3944 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
3945 RAISE OKL_API.G_EXCEPTION_ERROR;
3946 END IF;
3947
3948
3949 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3950 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);
3951 END IF;
3952
3953 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3954 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3955 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3956 RAISE OKL_API.G_EXCEPTION_ERROR;
3957 END IF;
3958
3959 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3960 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'Update investor stake');
3961 END IF;
3962
3963 /*** End API body ******************************************************/
3964
3965 -- Get message count and if count is 1, get message info
3966 FND_MSG_PUB.Count_And_Get
3967 (p_count => x_msg_count,
3968 p_data => x_msg_data);
3969
3970 EXCEPTION
3971 --For this User defined exception, do not rollback
3972 WHEN G_HALT_PROCESSING THEN
3973 x_return_status := OKL_API.G_RET_STS_ERROR;
3974 FND_MSG_PUB.Count_And_Get
3975 (p_count => x_msg_count,
3976 p_data => x_msg_data);
3977
3978 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3979 ROLLBACK TO activate_add_request_pvt;
3980 x_return_status := OKL_API.G_RET_STS_ERROR;
3981 FND_MSG_PUB.Count_And_Get
3982 (p_count => x_msg_count,
3983 p_data => x_msg_data);
3984
3985 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3986 ROLLBACK TO activate_add_request_pvt;
3987 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3988 FND_MSG_PUB.Count_And_Get
3989 (p_count => x_msg_count,
3990 p_data => x_msg_data);
3991 WHEN OTHERS THEN
3992 ROLLBACK TO activate_add_request_pvt;
3993 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3994 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3995 p_msg_name => G_UNEXPECTED_ERROR,
3996 p_token1 => G_SQLCODE_TOKEN,
3997 p_token1_value => SQLCODE,
3998 p_token2 => G_SQLERRM_TOKEN,
3999 p_token2_value => SQLERRM);
4000 FND_MSG_PUB.Count_And_Get
4001 (p_count => x_msg_count,
4002 p_data => x_msg_data);
4003
4004
4005 END activate_add_request;
4006
4007 -- Bug 6691554 - End of Changes
4008
4009 -------------------------------------------------------------------------------------------------
4010 -- PROCEDURE submit_add_khr_request
4011 ---------------------------------------------------------------------------
4012 -- Start of comments
4013 --
4014 -- Procedure Name : submit_add_khr_request
4015 -- Description :
4016 -- Business Rules : Submit the Add Contracts Request for Approval.
4017 -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
4018 -- x_msg_data, , p_agreement_id, p_pool_id, x_pool_trx_status.
4019 -- Version : 1.0
4020 -- End of comments
4021 ---------------------------------------------------------------------------
4022 PROCEDURE submit_add_khr_request (p_api_version IN NUMBER,
4023 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
4024 x_return_status OUT NOCOPY VARCHAR2,
4025 x_msg_count OUT NOCOPY NUMBER,
4026 x_msg_data OUT NOCOPY VARCHAR2,
4027 p_agreement_id IN OKC_K_HEADERS_V.ID%TYPE,
4028 p_pool_id IN OKL_POOLS.ID%TYPE,
4029 x_pool_trx_status OUT NOCOPY OKL_POOL_TRANSACTIONS.TRANSACTION_STATUS%TYPE)
4030 IS
4031 -- Get Pool Transaction Details
4032 CURSOR c_fetch_pool_trans_id_csr(p_transaction_id OKL_POOLS.ID%TYPE)
4033 IS
4034 SELECT pox.id
4035 FROM okl_pool_transactions pox
4036 WHERE pox.pol_id = p_pool_id
4037 AND pox.TRANSACTION_STATUS IN (G_POOL_TRX_STATUS_NEW, G_POOL_TRX_STS_APPR_REJECTED, G_POOL_TRX_STATUS_INCOMPLETE);
4038
4039 l_return_status VARCHAR2(3);
4040 l_api_name CONSTANT VARCHAR2(30) := 'submit_add_khr_request';
4041 l_parameter_list wf_parameter_list_t;
4042 l_key VARCHAR2(240);
4043 l_event_name VARCHAR2(240) := 'oracle.apps.okl.ia.approve_add_contracts_request';
4044 l_agreement_id OKC_K_HEADERS_V.ID%TYPE;
4045 l_pool_id OKL_POOLS.ID%TYPE;
4046 l_pool_trans_id OKL_POOL_TRANSACTIONS.ID%TYPE;
4047 lp_poxv_rec poxv_rec_type;
4048 lx_poxv_rec poxv_rec_type;
4049 l_approval_process fnd_lookups.lookup_code%TYPE;
4050 l_msg_data VARCHAR2(2000);
4051 l_msg_count NUMBER;
4052
4053 BEGIN
4054
4055 l_return_status := OKL_API.G_RET_STS_SUCCESS;
4056 l_pool_id := p_pool_id;
4057 l_agreement_id := p_agreement_id;
4058 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4059
4060 -- Call start_activity to create savepoint, check compatibility
4061 -- and initialize message list
4062 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
4063 p_init_msg_list,
4064 '_PVT',
4065 x_return_status);
4066 -- Check if activity started successfully
4067 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4068 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4069 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4070 RAISE OKL_API.G_EXCEPTION_ERROR;
4071 END IF;
4072 -- 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);
4073
4074 -- read the profile OKL: Investor Add Contracts Approval Process
4075 l_approval_process := fnd_profile.value('OKL_IA_ADD_KHR_APPR_PROCESS');
4076
4077 -- get the pool transaction id which needs to be updated, since we can have only one transaction in 'NEW' status
4078 OPEN c_fetch_pool_trans_id_csr(p_pool_id);
4079 FETCH c_fetch_pool_trans_id_csr into l_pool_trans_id;
4080 IF c_fetch_pool_trans_id_csr%NOTFOUND THEN
4081 OKL_API.set_message(p_app_name => G_APP_NAME,
4082 p_msg_name => G_NO_MATCHING_RECORD,
4083 p_token1 => G_COL_NAME_TOKEN,
4084 p_token1_value => 'OKL_POOL_TRANSACTIONS.ID');
4085 RAISE OKL_API.G_EXCEPTION_ERROR;
4086 END IF;
4087 CLOSE c_fetch_pool_trans_id_csr ;
4088
4089 --Set the Pool Transaction Id for the update call
4090 lp_poxv_rec.ID := l_pool_trans_id;
4091
4092 -- basic validation. API call should be in status passed before it can be submitted for approval
4093 /* Place the Validation API Call here :TODO */
4094 validate_add_request(p_api_version,
4095 p_init_msg_list,
4096 x_return_status,
4097 l_msg_count,
4098 l_msg_data,
4099 l_agreement_id);
4100 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
4101 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_INCOMPLETE;
4102
4103 OKL_POOL_PVT.update_pool_transaction(p_api_version => p_api_version,
4104 p_init_msg_list => p_init_msg_list,
4105 x_return_status => x_return_status,
4106 x_msg_count => x_msg_count,
4107 x_msg_data => x_msg_data,
4108 p_poxv_rec => lp_poxv_rec,
4109 x_poxv_rec => lx_poxv_rec);
4110 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4111 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4112 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4113 RAISE OKL_API.G_EXCEPTION_ERROR;
4114 END IF;
4115 --Since the Validation returned an Error need to raise an exception and return the control,
4116 --no further processing needs to be done.
4117 RAISE G_HALT_PROCESSING;
4118
4119 END IF; --x_return_status <> OKL_API.G_RET_STS_SUCCESS
4120
4121 IF(NVL(l_approval_process, 'NONE')) = 'NONE' THEN
4122 -- since no option is set at the profile, approve the operating agreement by default
4123 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_APPROVED;
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 ELSIF(l_approval_process IN (G_ADD_KHR_REQUEST_APPROVAL_WF, G_ADD_KHR_REQUEST_APPRV_AME))THEN
4139
4140 -- We need to status to Approved Pending since We are sending for approval
4141 lp_poxv_rec.TRANSACTION_STATUS := G_POOL_TRX_STATUS_PENDING_APPR;
4142
4143 OKL_POOL_PVT.update_pool_transaction(p_api_version => p_api_version,
4144 p_init_msg_list => p_init_msg_list,
4145 x_return_status => x_return_status,
4146 x_msg_count => x_msg_count,
4147 x_msg_data => x_msg_data,
4148 p_poxv_rec => lp_poxv_rec,
4149 x_poxv_rec => lx_poxv_rec);
4150 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4151 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4152 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
4153 RAISE OKL_API.G_EXCEPTION_ERROR;
4154 END IF;
4155
4156 -- Raise Event
4157 OKL_SEC_AGREEMENT_WF.raise_add_khr_approval_event(p_api_version => p_api_version
4158 ,p_init_msg_list => p_init_msg_list
4159 ,x_return_status => x_return_status
4160 ,x_msg_count => x_msg_count
4161 ,x_msg_data => x_msg_data
4162 ,p_agreement_id => l_agreement_id
4163 ,p_pool_id => l_pool_id
4164 ,p_pool_trans_id => l_pool_trans_id);
4165
4166 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4167 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4168 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4169 RAISE OKL_API.G_EXCEPTION_ERROR;
4170 END IF;
4171 END IF; -- end of NVL(l_approval_process,'NONE')='NONE'
4172
4173 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
4174 x_msg_data => x_msg_data);
4175 EXCEPTION
4176 --For this User defined exception, do not rollback
4177 WHEN G_HALT_PROCESSING THEN
4178 x_return_status := OKL_API.G_RET_STS_ERROR;
4179 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count,
4180 p_data => l_msg_data);
4181
4182 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4183 x_return_status := OKL_API.G_RET_STS_ERROR;
4184 IF c_fetch_pool_trans_id_csr%ISOPEN THEN
4185 CLOSE c_fetch_pool_trans_id_csr;
4186 END IF;
4187 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4188 p_api_name => l_api_name,
4189 p_pkg_name => G_PKG_NAME,
4190 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4191 x_msg_count => x_msg_count,
4192 x_msg_data => x_msg_data,
4193 p_api_type => G_API_TYPE);
4194
4195 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4196 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4197 IF c_fetch_pool_trans_id_csr%ISOPEN THEN
4198 CLOSE c_fetch_pool_trans_id_csr;
4199 END IF;
4200 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4201 p_api_name => l_api_name,
4202 p_pkg_name => G_PKG_NAME,
4203 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4204 x_msg_count => x_msg_count,
4205 x_msg_data => x_msg_data,
4206 p_api_type => G_API_TYPE);
4207 WHEN OTHERS THEN
4208 IF c_fetch_pool_trans_id_csr%ISOPEN THEN
4209 CLOSE c_fetch_pool_trans_id_csr;
4210 END IF;
4211 -- store SQL error message on message stack
4212 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4213 p_api_name => l_api_name,
4214 p_pkg_name => G_PKG_NAME,
4215 p_exc_name => 'OTHERS',
4216 x_msg_count => x_msg_count,
4217 x_msg_data => x_msg_data,
4218 p_api_type => G_API_TYPE);
4219
4220 END submit_add_khr_request;
4221 /*
4222 19-Dec-2007, ankushar Bug# 6691554
4223 endchanges
4224 */
4225
4226 /* sosharma 03-01-2008
4227 Added procedure to cancel the add request on active Investor Agreement
4228 Start changes*/
4229
4230
4231 Procedure cancel_add_request(
4232 p_api_version IN NUMBER,
4233 p_init_msg_list IN VARCHAR2,
4234 x_return_status OUT NOCOPY VARCHAR2,
4235 x_msg_count OUT NOCOPY NUMBER,
4236 x_msg_data OUT NOCOPY VARCHAR2,
4237 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE)
4238 IS
4239 --Declaring local variables
4240 l_api_name CONSTANT VARCHAR2(30) := 'cancel_add_request';
4241 l_api_version CONSTANT NUMBER := 1.0;
4242
4243
4244 CURSOR get_pol_id_csr(p_chr_id NUMBER)
4245 IS
4246 SELECT ID
4247 FROM OKL_POOLS
4248 WHERE KHR_ID = p_chr_id
4249 AND STATUS_CODE = 'ACTIVE';
4250
4251 CURSOR get_pox_id_csr(p_pol_id NUMBER)
4252 IS
4253 SELECT ID
4254 FROM OKL_POOL_TRANSACTIONS
4255 WHERE pol_id = p_pol_id
4256 AND TRANSACTION_STATUS <> 'COMPLETE';
4257
4258
4259 CURSOR get_pol_contents_csr(p_pox_id NUMBER)
4260 IS
4261 SELECT ID
4262 FROM OKL_POOL_CONTENTS
4263 WHERE pox_id = p_pox_id
4264 AND STATUS_CODE <> 'ACTIVE';
4265
4266
4267 CURSOR get_inv_stake_csr(p_khr_id NUMBER)
4268 IS
4269 SELECT TOP_KLE.ID,
4270 TOP_KLE.AMOUNT original_Stake,
4271 TOP_KLE.AMOUNT_STAKE additional_Stake
4272 FROM OKC_K_LINES_B TOP_LINE,
4273 OKL_K_LINES TOP_KLE,
4274 OKC_K_PARTY_ROLES_B PARTY_ROLE
4275 WHERE TOP_LINE.dnz_chr_id = p_khr_id
4276 AND TOP_KLE.ID = TOP_LINE.ID
4277 AND PARTY_ROLE.cle_id = TOP_LINE.id
4278 AND PARTY_ROLE.dnz_chr_id = TOP_LINE.dnz_chr_id
4279 AND PARTY_ROLE.rle_code = 'INVESTOR'
4280 AND PARTY_ROLE.jtot_object1_code = 'OKX_PARTY';
4281
4282
4283 l_pol_id NUMBER;
4284 l_pox_id NUMBER;
4285 i NUMBER;
4286 l_pocv_tbl OKL_POC_PVT.pocv_tbl_type;
4287 l_poxv_rec OKL_POX_PVT.poxv_rec_type;
4288 l_clev_rec clev_rec_type;
4289 l_klev_rec klev_rec_type;
4290 lx_clev_rec clev_rec_type;
4291 lx_klev_rec klev_rec_type;
4292
4293
4294
4295 BEGIN
4296 -- initialize return status
4297 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4298
4299
4300 x_return_status := OKL_API.START_ACTIVITY(
4301 p_api_name => l_api_name,
4302 p_pkg_name => g_pkg_name,
4303 p_init_msg_list => p_init_msg_list,
4304 l_api_version => l_api_version,
4305 p_api_version => p_api_version,
4306 p_api_type => G_API_TYPE,
4307 x_return_status => x_return_status);
4308
4309 -- check if activity started successfully
4310 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
4311 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4312 ElSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
4313 raise OKL_API.G_EXCEPTION_ERROR;
4314 End If;
4315
4316 FOR get_pol_id_rec IN get_pol_id_csr(p_chr_id)
4317 LOOP
4318 l_pol_id := get_pol_id_rec.id;
4319 END LOOP;
4320
4321 IF l_pol_id IS NULL
4322 THEN
4323 Okl_Api.set_message(G_APP_NAME,
4324 G_INVALID_VALUE,
4325 G_COL_NAME_TOKEN,
4326 'POOL_ID');
4327 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4328 END IF;
4329
4330 FOR get_pox_id_rec IN get_pox_id_csr(l_pol_id)
4331 LOOP
4332 l_pox_id := get_pox_id_rec.id;
4333 END LOOP;
4334
4335 IF l_pox_id IS NULL
4336 THEN
4337 Okl_Api.set_message(G_APP_NAME,
4338 G_INVALID_VALUE,
4339 G_COL_NAME_TOKEN,
4340 'POX_ID');
4341 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4342 END IF;
4343
4344 -- create table of pool contents to be deleted
4345 i:=0;
4346 FOR get_pol_contents_rec IN get_pol_contents_csr(l_pox_id)
4347 LOOP
4348 l_pocv_tbl(i).id:= get_pol_contents_rec.id;
4349 i:=i+1;
4350 END LOOP;
4351
4352 -- delete pool contents
4353 OKL_POC_PVT.delete_row(
4354 p_api_version => p_api_version,
4355 p_init_msg_list => p_init_msg_list,
4356 x_return_status => x_return_status,
4357 x_msg_count => x_msg_count,
4358 x_msg_data => x_msg_data,
4359 p_pocv_tbl => l_pocv_tbl);
4360
4361 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4362 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4363 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4364 RAISE OKL_API.G_EXCEPTION_ERROR;
4365 END IF;
4366
4367 -- Assign pool transaction to be deleted to the record
4368
4369 l_poxv_rec.id:= l_pox_id;
4370
4371 --delete pool transactions
4372
4373 OKL_POX_PVT.delete_row(
4374 p_api_version => p_api_version,
4375 p_init_msg_list => p_init_msg_list,
4376 x_return_status => x_return_status,
4377 x_msg_count => x_msg_count,
4378 x_msg_data => x_msg_data,
4379 p_poxv_rec => l_poxv_rec);
4380
4381 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4382 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4383 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4384 RAISE OKL_API.G_EXCEPTION_ERROR;
4385 END IF;
4386
4387 --Update the additional stake on removing transaction
4388 FOR get_inv_stake_rec IN get_inv_stake_csr(p_chr_id)
4389 LOOP
4390 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
4391 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_sec_agreement_pvt.activate_add_request.debug', 'OKL_CONTRACT_PUB.update_contract_line');
4392 END IF;
4393
4394
4395 --Remove the additional stake amount
4396 IF get_inv_stake_rec.additional_stake IS NOT NULL THEN
4397 l_clev_rec.id := get_inv_stake_rec.id;
4398 l_klev_rec.id := get_inv_stake_rec.id;
4399 l_klev_rec.amount_stake := NULL;
4400
4401 OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => l_clev_rec,
4408 p_klev_rec => l_klev_rec,
4409 x_clev_rec => lx_clev_rec,
4410 x_klev_rec => lx_klev_rec);
4411
4412 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
4413 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);
4414 END IF;
4415
4416 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4417 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4418 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4419 RAISE OKL_API.G_EXCEPTION_ERROR;
4420 END IF;
4421 END IF;
4422 END LOOP;
4423
4424
4425
4426 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4427
4428 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
4429
4430
4431 EXCEPTION
4432 when OKL_API.G_EXCEPTION_ERROR then
4433 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4434 p_api_name => l_api_name,
4435 p_pkg_name => g_pkg_name,
4436 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4437 x_msg_count => x_msg_count,
4438 x_msg_data => x_msg_data,
4439 p_api_type => g_api_type);
4440
4441 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
4442 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4443 p_api_name => l_api_name,
4444 p_pkg_name => g_pkg_name,
4445 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4446 x_msg_count => x_msg_count,
4447 x_msg_data => x_msg_data,
4448 p_api_type => g_api_type);
4449
4450 when OTHERS then
4451 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4452 p_api_name => l_api_name,
4453 p_pkg_name => g_pkg_name,
4454 p_exc_name => 'OTHERS',
4455 x_msg_count => x_msg_count,
4456 x_msg_data => x_msg_data,
4457 p_api_type => g_api_type);
4458
4459
4460 END cancel_add_request;
4461 /*sosharma end changes*/
4462
4463
4464 END Okl_Sec_Agreement_Pvt;