[Home] [Help]
PACKAGE BODY: APPS.OKL_VR_DOCUMENT_PVT
Source
1 PACKAGE BODY OKL_VR_DOCUMENT_PVT AS
2 /* $Header: OKLDVARB.pls 120.0.12020000.3 2013/02/07 20:47:38 rpillay noship $ */
3
4 G_DEBUG CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
5 G_INIT_NUMBER CONSTANT NUMBER := -9999;
6 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
7 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_REQUIRED_VALUE;
8 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
9
10 -- varangan - Billing-Inline changes - Bug#5898792 - New constant added -Begin
11 -- sosharma changed the the value from STREAMS to VARIABLE_RATE
12 G_SOURCE_BILLING_TRX CONSTANT VARCHAR2(200) :='VARIABLE_RATE';
13 -- varangan - Billing-Inline changes - Bug#5898792 - New constant added -End
14
15 -- Start of comments
16 --
17 -- Procedure Name : get_reporting_product
18 -- Description : This procedure checks if there is a reporting product attached to the contract
19 -- Business Rules :
20 -- Parameters : p_contract_id - Contract ID
21 -- Version : 1.0
22 -- History : SECHAWLA 20-feb-2009 MG Impact on var rate contracts - Created
23 -- End of comments
24 PROCEDURE get_reporting_product(p_api_version IN NUMBER,
25 p_init_msg_list IN VARCHAR2,
26 x_return_status OUT NOCOPY VARCHAR2,
27 x_msg_count OUT NOCOPY NUMBER,
28 x_msg_data OUT NOCOPY VARCHAR2,
29 p_contract_id IN NUMBER,
30 x_rep_product OUT NOCOPY VARCHAR2,
31 x_rep_product_id OUT NOCOPY NUMBER) IS
32
33 -- Get the financial product of the contract
34 CURSOR l_get_fin_product(cp_khr_id IN NUMBER) IS
35 SELECT a.start_date, a.contract_number, b.pdt_id
36 FROM okc_k_headers_b a, okl_k_headers b
37 WHERE a.id = b.id
38 AND a.id = cp_khr_id;
39
40 SUBTYPE pdtv_rec_type IS OKL_SETUPPRODUCTS_PUB.pdtv_rec_type;
41 SUBTYPE pdt_parameters_rec_type IS OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
42
43 l_fin_product_id NUMBER;
44 l_start_date DATE;
45 l_contract_number VARCHAR2(120);
46 lp_pdtv_rec pdtv_rec_type;
47 lp_empty_pdtv_rec pdtv_rec_type;
48 lx_pdt_parameter_rec pdt_parameters_rec_type ;
49 l_reporting_product OKL_PRODUCTS_V.NAME%TYPE;
50 l_reporting_product_id NUMBER;
51
52 lx_no_data_found BOOLEAN;
53
54
55 l_mg_rep_book fa_book_controls.book_type_code%TYPE;
56 mg_error EXCEPTION;
57
58
59
60
61 BEGIN
62 -- get the financial product of the contract
63 OPEN l_get_fin_product(p_contract_id);
64 FETCH l_get_fin_product INTO l_start_date, l_contract_number, l_fin_product_id;
65 CLOSE l_get_fin_product;
66
67 lp_pdtv_rec.id := l_fin_product_id;
68
69 -- check if the fin product has a reporting product
70 OKL_SETUPPRODUCTS_PUB.Getpdt_parameters( p_api_version => p_api_version,
71 p_init_msg_list => OKC_API.G_FALSE,
72 x_return_status => x_return_status,
73 x_no_data_found => lx_no_data_found,
74 x_msg_count => x_msg_count,
75 x_msg_data => x_msg_data,
76 p_pdtv_rec => lp_pdtv_rec,
77 p_product_date => l_start_date,
78 p_pdt_parameter_rec => lx_pdt_parameter_rec);
79
80 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
81 -- Error getting financial product parameters for contract CONTRACT_NUMBER.
82 OKC_API.set_message( p_app_name => 'OKL',
83 p_msg_name => 'OKL_AM_FIN_PROD_PARAM_ERR',
84 p_token1 => 'CONTRACT_NUMBER',
85 p_token1_value => l_contract_number);
86
87
88
89 ELSE
90
91 x_rep_product := lx_pdt_parameter_rec.reporting_product;
92 x_rep_product_id := lx_pdt_parameter_rec.reporting_pdt_id;
93
94 END IF;
95
96 EXCEPTION
97
98 WHEN OTHERS THEN
99 IF l_get_fin_product%ISOPEN THEN
100 CLOSE l_get_fin_product;
101 END IF;
102 OKL_API.set_message(p_app_name => 'OKC',
103 p_msg_name => g_unexpected_error,
104 p_token1 => g_sqlcode_token,
105 p_token1_value => sqlcode,
106 p_token2 => g_sqlerrm_token,
107 p_token2_value => sqlerrm);
108 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
109 END get_reporting_product;
110
111 ------------------------------------------------------------------------------
112
113 -- Start of Comments
114 -- Created By: Ramesh Seela
115 -- Procedure Name Create_Stream_Invoice
116 -- Description: This procedure is called by Variable Interest Calculation for Loans
117 -- Inputs :
118 -- Output :
119 -- Dependencies:
120 -- Parameters:
121 -- Version: 1.0
122 -- End of Comments
123
124 ------------------------------------------------------------------------------
125 Procedure Create_Stream_Invoice (
126 p_api_version IN NUMBER,
127 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
128 x_return_status OUT NOCOPY VARCHAR2,
129 x_msg_count OUT NOCOPY NUMBER,
130 x_msg_data OUT NOCOPY VARCHAR2,
131 p_contract_id IN NUMBER,
132 p_line_id IN NUMBER DEFAULT NULL,
133 p_amount IN NUMBER,
134 p_due_date IN DATE,
135 p_stream_type_purpose IN VARCHAR2,
136 p_create_invoice_flag IN VARCHAR2 DEFAULT OKL_API.G_TRUE,
137 p_process_flag IN VARCHAR2 DEFAULT NULL,
138 p_parent_strm_element_id IN NUMBER DEFAULT NULL,
139 x_invoice_id OUT NOCOPY NUMBER,
140 x_stream_element_id OUT NOCOPY NUMBER) IS
141
142 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_STREAM_INVOICE';
143 l_api_version CONSTANT NUMBER := 1.0;
144 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
145 l_msg_count NUMBER;
146 l_msg_data VARCHAR2(2000);
147 l_sty_id OKL_STRM_TYPE_V.id%TYPE;
148 l_stream_exists VARCHAR2(1) := '?';
149 l_stmv_rec Okl_Streams_Pub.stmv_rec_type;
150 lx_stmv_rec Okl_Streams_Pub.stmv_rec_type;
151 l_selv_rec Okl_Streams_Pub.selv_rec_type;
152 lx_selv_rec Okl_Streams_Pub.selv_rec_type;
153 i_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
154 r_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
155 l_def_desc CONSTANT VARCHAR2(80) := 'Variable Interest Stream Billing';
156 l_final_status CONSTANT VARCHAR2(30) := 'SUBMITTED';
157 -- l_trx_type_name CONSTANT VARCHAR2(30) := 'Billing';
158 l_date_entered CONSTANT DATE := SYSDATE;
159 -- l_trx_type_id NUMBER;
160 l_line_code CONSTANT VARCHAR2(30) := 'LINE';
161
162 -- Begin - Billing Inline changes - Bug#5898792 - varangan - 28/2/2007
163
164 -----------------------------------------------------------
165 -- Variables for billing API call
166 -----------------------------------------------------------
167 lp_taiv_rec okl_tai_pvt.taiv_rec_type;
168 lp_tilv_rec okl_til_pvt.tilv_rec_type;
169 lp_tilv_tbl okl_til_pvt.tilv_tbl_type;
170 lp_tldv_rec okl_tld_pvt.tldv_rec_type;
171 lp_tldv_tbl okl_tld_pvt.tldv_tbl_type;
172
173 lx_taiv_rec okl_tai_pvt.taiv_rec_type;
174 lx_tilv_tbl okl_til_pvt.tilv_tbl_type;
175 lx_tldv_tbl okl_tld_pvt.tldv_tbl_type;
176 -- End - Billing Inline changes - Bug#5898792 - varangan - 28/2/2007
177
178 l_msg_index_out NUMBER;
179 l_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
180 l_set_of_books_id Hr_operating_units.set_of_books_id%TYPE;
181 l_trxH_in_rec Okl_Trx_Contracts_Pub.tcnv_rec_type;
182 l_trxH_out_rec Okl_Trx_Contracts_Pub.tcnv_rec_type;
183 l_trxL_in_tbl Okl_Trx_Contracts_Pub.tclv_tbl_type;
184 l_trxL_out_tbl Okl_Trx_Contracts_Pub.tclv_tbl_type;
185 l_acc_gen_primary_key_tbl Okl_Account_Generator_Pvt.primary_key_tbl;
186 l_meaning FND_LOOKUPS.meaning%TYPE;
187 l_description FND_LOOKUPS.description%TYPE;
188
189 l_stream_created VARCHAR2(1) := OKL_API.G_FALSE;
190 l_strm_element_created VARCHAR2(1) := OKL_API.G_FALSE;
191 l_okl_trx_created VARCHAR2(1) := OKL_API.G_FALSE;
192 create_stream_invoice_failed EXCEPTION;
193
194 -- 5033120
195 l_trx_type_id Okl_Trx_Types_V.id%TYPE;
196 l_trx_try_id Okl_Trx_Types_V.try_id%TYPE;
197 l_trx_desc Okl_Trx_Types_V.description%TYPE;
198
199 l_tcn_type FND_LOOKUPS.lookup_code%TYPE;
200 l_tcn_meaning FND_LOOKUPS.meaning%TYPE;
201 l_tcn_desc FND_LOOKUPS.description%TYPE;
202
203 l_tcl_type FND_LOOKUPS.lookup_code%TYPE;
204 l_tcl_meaning FND_LOOKUPS.meaning%TYPE;
205 l_tcl_desc FND_LOOKUPS.description%TYPE;
206
207 -- Bug 5964482 dpsingh SLA Uptake Project
208 l_tcn_id NUMBER;
209 l_tmpl_identify_tbl Okl_Account_Dist_Pvt.tmpl_identify_tbl_type;
210 l_dist_info_tbl Okl_Account_Dist_Pvt.dist_info_tbl_type;
211 l_ctxt_tbl Okl_Account_Dist_Pvt.CTXT_TBL_TYPE;
212 l_acc_gen_tbl Okl_Account_Dist_Pvt.ACC_GEN_TBL_TYPE;
213 l_template_out_tbl Okl_Account_Dist_Pvt.avlv_out_tbl_type;
214 l_amount_out_tbl Okl_Account_Dist_Pvt.amount_out_tbl_type;
215 --END: Added by dpsingh for SLA Uptake, Bug 5964482
216
217 CURSOR stream_csr(p_khr_id NUMBER, p_kle_id NUMBER, p_sty_id NUMBER) IS
218 SELECT stm.id
219 FROM okl_streams stm
220 WHERE stm.khr_id = p_khr_id
221 AND nvl(stm.kle_id, -9999) = NVL(p_kle_id, -9999)
222 AND stm.say_code = 'CURR'
223 AND stm.active_yn = 'Y'
224 AND stm.sty_id = p_sty_id;
225
226 --sechawla 20-feb-09 MG Impact on var rate
227 CURSOR rep_stream_csr(p_khr_id NUMBER, p_kle_id NUMBER, p_rep_sty_id NUMBER) IS
228 SELECT stm.id
229 FROM okl_streams stm
230 WHERE stm.khr_id = p_khr_id
231 AND nvl(stm.kle_id, -9999) = NVL(p_kle_id, -9999)
232 AND stm.say_code = 'CURR'
233 AND stm.active_yn = 'N'
234 AND stm.purpose_code = 'REPORT'
235 AND stm.sty_id = p_rep_sty_id;
236
237 CURSOR tran_num_csr IS
238 SELECT okl_sif_seq.nextval
239 FROM dual;
240
241 CURSOR c_stm_id_line_number(c_stm_id NUMBER) IS
242 SELECT SE_LINE_NUMBER
243 FROM OKL_STRM_ELEMENTS_V
244 WHERE stm_id = c_stm_id
245 ORDER BY SE_LINE_NUMBER DESC;
246
247
248 CURSOR set_of_books_csr IS
249 SELECT set_of_books_id
250 FROM OKL_SYS_ACCT_OPTS;
251
252
253 CURSOR trx_type_csr (p_trx_name VARCHAR2) IS
254 SELECT id, try_id, description
255 FROM okl_trx_types_v
256 WHERE name = p_trx_name;
257
258
259 CURSOR fnd_lookup_csr (p_lookup_type VARCHAR2, p_lookup_code VARCHAR2) IS
260 SELECT lookup_code, meaning, description
261 FROM fnd_lookups
262 WHERE lookup_code = p_lookup_code
263 AND lookup_type = p_lookup_type;
264
265 --Added by dpsingh for LE Uptake
266 CURSOR get_contract_number(p_khr_id NUMBER) IS
267 SELECT CONTRACT_NUMBER
268 FROM OKC_K_HEADERS_B
269 WHERE ID = p_khr_id ;
270
271 --Added by dpsingh for LE Uptake
272 -- Bug 5964482 dpsingh for AE signature Uptake start
273 CURSOR get_dff_fields(p_khr_id NUMBER) IS
274 SELECT ATTRIBUTE_CATEGORY,
275 ATTRIBUTE1,
276 ATTRIBUTE2,
277 ATTRIBUTE3,
278 ATTRIBUTE4,
279 ATTRIBUTE5,
280 ATTRIBUTE6,
281 ATTRIBUTE7,
282 ATTRIBUTE8,
283 ATTRIBUTE9,
284 ATTRIBUTE10,
285 ATTRIBUTE11,
286 ATTRIBUTE12,
287 ATTRIBUTE13,
288 ATTRIBUTE14,
289 ATTRIBUTE15
290 FROM OKL_K_HEADERS
291 WHERE ID = p_khr_id ;
292
293 --sechawla 20-feb-09 MG Impact on var rate
294
295 /*
296 -- Get secondary_rep_method
297 CURSOR l_sec_rep_method_csr IS
298 SELECT secondary_rep_method
299 FROM okl_sys_acct_opts;
300
301 l_sec_rep_method VARCHAR2(30);
302 */
303 l_rep_sty_id OKL_STRM_TYPE_V.id%TYPE;
304 l_rep_strm_element_created VARCHAR2(1) := OKL_API.G_FALSE;
305 l_rep_stream_created VARCHAR2(1) := OKL_API.G_FALSE;
306 lx_rep_product OKL_PRODUCTS_V.NAME%TYPE;
307 lx_rep_product_id NUMBER;
308
309
310
311 -- Bug 5964482 dpsingh for AE signature Uptake end
312 l_legal_entity_id NUMBER;
313 l_cntrct_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
314 l_rep_stmv_rec Okl_Streams_Pub.stmv_rec_type;
315 lx_rep_stmv_rec Okl_Streams_Pub.stmv_rec_type;
316 l_rep_selv_rec Okl_Streams_Pub.selv_rec_type;
317 lx_rep_selv_rec Okl_Streams_Pub.selv_rec_type;
318
319
320 BEGIN
321 OKL_VARIABLE_INTEREST_PVT.print_debug('Executing procedure CREATE_STREAM_INVOICE using following parameters : ');
322 OKL_VARIABLE_INTEREST_PVT.print_debug(' p_contract_id : '|| p_contract_id );
323 OKL_VARIABLE_INTEREST_PVT.print_debug(' p_line_id : '|| p_line_id);
324 OKL_VARIABLE_INTEREST_PVT.print_debug(' p_amount : '|| p_amount );
325 OKL_VARIABLE_INTEREST_PVT.print_debug(' p_due_date: '|| p_due_date);
326 OKL_VARIABLE_INTEREST_PVT.print_debug(' p_stream_type_purpose : '||p_stream_type_purpose);
327 OKL_VARIABLE_INTEREST_PVT.print_debug(' p_create_invoice_flag: '|| p_create_invoice_flag);
328 OKL_VARIABLE_INTEREST_PVT.print_debug(' p_process_flag : '|| p_process_flag);
329 OKL_VARIABLE_INTEREST_PVT.print_debug(' p_parent_strm_element_id : '|| p_parent_strm_element_id);
330
331 x_return_status := OKL_API.G_RET_STS_SUCCESS;
332
333 -- Get sty_id for the contract
334 IF (p_stream_type_purpose = 'VARIABLE_INTEREST') THEN
335 Okl_Streams_Util.get_primary_stream_type(
336 p_khr_id => p_contract_id,
337 p_primary_sty_purpose => p_stream_type_purpose,
338 x_return_status => x_return_status,
339 x_primary_sty_id => l_sty_id
340 );
341 ELSE
342 Okl_Streams_Util.get_dependent_stream_type(
343 p_khr_id => p_contract_id,
344 p_primary_sty_purpose => 'RENT',
345 p_dependent_sty_purpose => p_stream_type_purpose,
346 x_return_status => x_return_status,
347 x_dependent_sty_id => l_sty_id
348 );
349 END IF;
350
351 IF (x_return_status = 'S' ) THEN
352 OKL_VARIABLE_INTEREST_PVT.print_debug (' -- Stream Id for purpose '|| p_stream_type_purpose || 'retrieved.');
353 ELSE
354 OKL_VARIABLE_INTEREST_PVT.print_debug( ' -- ERROR: Could not retrieve Stream Id for purpose '|| p_stream_type_purpose );
355 END IF;
356
357 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
358 OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to Okl_Streams_Util.get_primary/dependent_stream_type');
359 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
360 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
361 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to Okl_Streams_Util.get_primary/dependent_stream_type');
362 RAISE create_stream_invoice_failed;
363 END IF;
364
365 l_selv_rec.stm_id := NULL;
366
367 OPEN stream_csr (p_contract_id, p_line_id, l_sty_id);
368 FETCH stream_csr INTO l_selv_rec.stm_id;
369 IF (stream_csr%NOTFOUND) THEN
370 NULL;
371 OKL_VARIABLE_INTEREST_PVT.print_debug('Stream Not Found : Creating a new stream');
372 END IF;
373 CLOSE stream_csr;
374
375 IF (l_selv_rec.stm_id IS NULL) THEN
376 OPEN tran_num_csr;
377 FETCH tran_num_csr INTO l_stmv_rec.transaction_number;
378 CLOSE tran_num_csr;
379
380 l_stmv_rec.sty_id := l_sty_id;
381 l_stmv_rec.khr_id := p_contract_id;
382 l_stmv_rec.kle_id := p_line_id;
383 l_stmv_rec.sgn_code := 'INTC';
384 l_stmv_rec.say_code := 'CURR';
385 l_stmv_rec.active_yn := 'Y';
386 l_stmv_rec.date_current := sysdate;
387
388 IF (p_process_flag = 'DAILY_INTEREST') THEN
389 l_stmv_rec.comments := 'Daily Interest';
390 ELSE
391 l_stmv_rec.comments := 'Variable Interest';
392 END IF;
393
394 OKL_VARIABLE_INTEREST_PVT.print_debug('Executing procedure OKL_STREAMS_PUB.CREATE_STREAMS');
395
396 Okl_Streams_Pub.create_streams(
397 p_api_version => p_api_version,
398 p_init_msg_list => p_init_msg_list,
399 x_return_status => x_return_status,
400 x_msg_count => x_msg_count,
401 x_msg_data => x_msg_data,
402 p_stmv_rec => l_stmv_rec,
403 x_stmv_rec => lx_stmv_rec);
404
405
406 IF (x_return_status = 'S' ) THEN
407 OKL_VARIABLE_INTEREST_PVT.print_debug (' -- Success in Stream Creation.');
408 ELSE
409 OKL_VARIABLE_INTEREST_PVT.print_debug (' -- Error: '||x_msg_data);
410 END IF;
411
412 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
413 OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to Okl_Streams_Pub.create_streams');
414 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
415 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
416 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to Okl_Streams_Pub.create_streams');
417 RAISE create_stream_invoice_failed;
418 END IF;
419
420 l_stream_created := OKL_API.G_TRUE;
421
422 OKL_VARIABLE_INTEREST_PVT.print_debug ('Stm ID: '||lx_stmv_rec.id);
423 l_selv_rec.stm_id := lx_stmv_rec.id;
424 OKL_VARIABLE_INTEREST_PVT.print_debug ('Stm ID: '||l_selv_rec.stm_id);
425
426 END IF;
427
428 --change on 16 Nov 2005 by pgomes for bug fix 4740008
429 --setting the value of accrued_yn to NULL instead of 'N'
430 l_selv_rec.accrued_yn := NULL;
431 l_selv_rec.stream_element_date := p_due_date;
432 l_selv_rec.date_billed := trunc(sysdate);
433 l_selv_rec.amount := p_amount;
434 l_selv_rec.sel_id := p_parent_strm_element_id;
435
436 l_selv_rec.se_line_number := NULL;
437 OPEN c_stm_id_line_number(l_selv_rec.stm_id);
438 FETCH c_stm_id_line_number INTO l_selv_rec.se_line_number;
439 IF (c_stm_id_line_number%NOTFOUND) THEN
440 OKL_VARIABLE_INTEREST_PVT.print_debug ('Stream Elements do not exist');
441 l_selv_rec.se_line_number := 1;
442 ELSE
443 l_selv_rec.se_line_number := l_selv_rec.se_line_number + 1;
444 END IF;
445 CLOSE c_stm_id_line_number;
446
447 IF (p_process_flag = 'DAILY_INTEREST') THEN
448 l_selv_rec.comments := 'Daily Interest';
449 ELSE
450 l_selv_rec.comments := 'Variable Interest';
451 END IF;
452
453
454 OKL_VARIABLE_INTEREST_PVT.print_debug('Executing procedure OKL_STREAMS_PUB.CREATE_STREAM_ELEMENTS');
455
456 Okl_Streams_Pub.create_stream_elements(
457 p_api_version => p_api_version,
458 p_init_msg_list => p_init_msg_list,
459 x_return_status => x_return_status,
460 x_msg_count => x_msg_count,
461 x_msg_data => x_msg_data,
462 p_selv_rec => l_selv_rec,
463 x_selv_rec => lx_selv_rec);
464
465 IF (x_return_status = 'S' ) THEN
466 OKL_VARIABLE_INTEREST_PVT.print_debug (' -- Success in Creating Stream Elements.');
467 ELSE
468 OKL_VARIABLE_INTEREST_PVT.print_debug (' -- Error: '||x_msg_data);
469 END IF;
470
471 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
472 OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to Okl_Streams_Pub.create_stream_elements');
473 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
474 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
475 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to Okl_Streams_Pub.create_stream_elements');
476 RAISE create_stream_invoice_failed;
477 END IF;
478
479 l_strm_element_created := OKL_API.G_TRUE;
480
481 x_stream_element_id := lx_selv_rec.id;
482
483 ---------------sechawla 19-feb-09 MG Impact on Varibale Rate user story BEGIN ------------
484 ---------------Create reporting streams for secondary representation ----------------
485 --This procedure is modified to create reporting streams for the following types of variable rate contracts
486 --and corresponding stream type purposes.
487
488 --Float_Factor_Lease ---> stream_type_purpose 'FLOAT_FACTOR_ADJUSTMENT'
489 --Loan-Float- Estimated and Billed ---> stream_type_purpose 'VARIABLE_INTEREST'
490 --Catchup-Cleanup Streams ---> stream_type_purpose --> 'INTEREST_CATCHUP', 'PRINCIPAL_CATCHUP'
491
492 --Reporitng streams will be created if the financial product has a reporting product and
493 --and secondary_rep_method is 'Automated' for the OU
494
495 IF p_stream_type_purpose IN ('FLOAT_FACTOR_ADJUSTMENT','VARIABLE_INTEREST','INTEREST_CATCHUP', 'PRINCIPAL_CATCHUP') THEN
496 --Check if financial product has a reporting product
497 get_reporting_product(
498 p_api_version => p_api_version,
499 p_init_msg_list => OKC_API.G_FALSE,
500 x_return_status => x_return_status,
501 x_msg_count => x_msg_count,
502 x_msg_data => x_msg_data,
503 p_contract_id => p_contract_id,
504 x_rep_product => lx_rep_product,
505 x_rep_product_id => lx_rep_product_id);
506
507 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
508 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
509 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
510 RAISE OKL_API.G_EXCEPTION_ERROR;
511 END IF;
512
513 /* --Check the secondary_rep_method
514 OPEN l_sec_rep_method_csr ;
515 FETCH l_sec_rep_method_csr INTO l_sec_rep_method;
516 IF l_sec_rep_method_csr%NOTFOUND THEN
517 print_error_message('Secondary rep method cursor did not return any records');
518 RAISE create_stream_invoice_failed;
519 END IF;
520 CLOSE l_sec_rep_method_csr ;
521 */
522 -- IF lx_rep_product IS NOT NULL AND l_sec_rep_method = 'AUTOMATED' THEN
523 IF lx_rep_product IS NOT NULL THEN
524
525 IF (p_stream_type_purpose = 'VARIABLE_INTEREST') THEN --will it also be primary purpose on rep product SGT ?
526 Okl_Streams_Util.get_primary_stream_type_rep(
527 p_khr_id => p_contract_id,
528 p_primary_sty_purpose => p_stream_type_purpose,
529 x_return_status => x_return_status,
530 x_primary_sty_id => l_rep_sty_id
531 );
532
533 ELSE
534 Okl_Streams_Util.get_dependent_stream_type_rep(
535 p_khr_id => p_contract_id,
536 p_primary_sty_purpose => 'RENT',
537 p_dependent_sty_purpose => p_stream_type_purpose,
538 x_return_status => x_return_status,
539 x_dependent_sty_id => l_rep_sty_id
540 );
541 END IF;
542
543 IF (x_return_status = 'S' ) THEN
544 OKL_VARIABLE_INTEREST_PVT.print_debug (' -- Reporting Stream Id for purpose '|| p_stream_type_purpose || 'retrieved.');
545 ELSE
546 OKL_VARIABLE_INTEREST_PVT.print_debug( ' -- ERROR: Could not retrieve Reporting Stream Id for purpose '|| p_stream_type_purpose );
547 END IF;
548
549 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
550 OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to Okl_Streams_Util.get_primary/dependent_stream_type_rep');
551 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
552 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
553 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to Okl_Streams_Util.get_primary/dependent_stream_type_rep');
554 RAISE create_stream_invoice_failed;
555 END IF;
556
557 l_rep_selv_rec.stm_id := NULL;
558
559 OPEN rep_stream_csr (p_contract_id, p_line_id, l_rep_sty_id);
560 FETCH rep_stream_csr INTO l_rep_selv_rec.stm_id;
561 IF (rep_stream_csr%NOTFOUND) THEN
562 NULL;
563 OKL_VARIABLE_INTEREST_PVT.print_debug('Reporting Stream Not Found : Creating a new stream');
564 END IF;
565 CLOSE rep_stream_csr;
566
567 IF (l_rep_selv_rec.stm_id IS NULL) THEN
568 OPEN tran_num_csr;
569 FETCH tran_num_csr INTO l_rep_stmv_rec.transaction_number;
570 CLOSE tran_num_csr;
571
572 l_rep_stmv_rec.sty_id := l_rep_sty_id;
573 l_rep_stmv_rec.khr_id := p_contract_id;
574 l_rep_stmv_rec.kle_id := p_line_id;
575 l_rep_stmv_rec.sgn_code := 'INTC'; --- wil it be this for reporting stream ?
576 l_rep_stmv_rec.say_code := 'CURR';
577 l_rep_stmv_rec.active_yn := 'N';
578 l_rep_stmv_rec.date_current := sysdate;
579 l_rep_stmv_rec.purpose_code := 'REPORT';
580 l_rep_stmv_rec.comments := 'Variable Interest';
581
582 OKL_VARIABLE_INTEREST_PVT.print_debug('Executing procedure OKL_STREAMS_PUB.CREATE_STREAMS');
583
584 Okl_Streams_Pub.create_streams(
585 p_api_version => p_api_version,
586 p_init_msg_list => p_init_msg_list,
587 x_return_status => x_return_status,
588 x_msg_count => x_msg_count,
589 x_msg_data => x_msg_data,
590 p_stmv_rec => l_rep_stmv_rec,
591 x_stmv_rec => lx_rep_stmv_rec);
592
593 IF (x_return_status = 'S' ) THEN
594 OKL_VARIABLE_INTEREST_PVT.print_debug (' -- Success in Reporting Stream Creation.');
595 ELSE
596 OKL_VARIABLE_INTEREST_PVT.print_debug (' -- Error: '||x_msg_data);
597 END IF;
598
599 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
600 OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to Okl_Streams_Pub.create_streams for reporting streams');
601 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
602 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
603 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to Okl_Streams_Pub.create_streams for reporting streams');
604 RAISE create_stream_invoice_failed;
605 END IF;
606
607 l_rep_stream_created := OKL_API.G_TRUE;
608
609 OKL_VARIABLE_INTEREST_PVT.print_debug ('Stm ID: '||lx_rep_stmv_rec.id);
610 l_rep_selv_rec.stm_id := lx_rep_stmv_rec.id;
611 OKL_VARIABLE_INTEREST_PVT.print_debug ('Stm ID: '||l_rep_selv_rec.stm_id);
612
613 END IF;
614
615 --setting the value of accrued_yn to NULL instead of 'N'
616 l_rep_selv_rec.accrued_yn := NULL; --check
617 l_rep_selv_rec.stream_element_date := p_due_date;
618
619 --Bug# 16244578: Set date_billed to NULL for Reporting Product streams
620 -- to ensure that these are not treated as Billed streams
621 -- by downstream processes such as Rebook
622 -- l_rep_selv_rec.date_billed := trunc(sysdate);
623 l_rep_selv_rec.date_billed := NULL;
624
625 l_rep_selv_rec.amount := p_amount;
626 l_rep_selv_rec.sel_id := p_parent_strm_element_id; --what is this ?
627 l_rep_selv_rec.se_line_number := NULL;
628
629 OPEN c_stm_id_line_number(l_rep_selv_rec.stm_id);
630 FETCH c_stm_id_line_number INTO l_rep_selv_rec.se_line_number;
631 IF (c_stm_id_line_number%NOTFOUND) THEN
632 OKL_VARIABLE_INTEREST_PVT.print_debug ('Reporting Stream Elements do not exist');
633 l_rep_selv_rec.se_line_number := 1;
634 ELSE
635 l_rep_selv_rec.se_line_number := l_rep_selv_rec.se_line_number + 1;
636 END IF;
637 CLOSE c_stm_id_line_number;
638
639 l_rep_selv_rec.comments := 'Variable Interest';
640
641
642 OKL_VARIABLE_INTEREST_PVT.print_debug('Executing procedure OKL_STREAMS_PUB.CREATE_STREAM_ELEMENTS for reporting streams');
643
644 Okl_Streams_Pub.create_stream_elements(
645 p_api_version => p_api_version,
646 p_init_msg_list => p_init_msg_list,
647 x_return_status => x_return_status,
648 x_msg_count => x_msg_count,
649 x_msg_data => x_msg_data,
650 p_selv_rec => l_rep_selv_rec,
651 x_selv_rec => lx_rep_selv_rec);
652
653 IF (x_return_status = 'S' ) THEN
654 OKL_VARIABLE_INTEREST_PVT.print_debug (' -- Success in Creating Reporing Stream Elements.');
655 ELSE
656 OKL_VARIABLE_INTEREST_PVT.print_debug (' -- Error: '||x_msg_data);
657 END IF;
658
659 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
660 OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to Okl_Streams_Pub.create_stream_elements for reporting streams');
661 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
662 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
663 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to Okl_Streams_Pub.create_stream_elements for reporting streams');
664 RAISE create_stream_invoice_failed;
665 END IF;
666
667 l_rep_strm_element_created := OKL_API.G_TRUE;
668
669 END IF;
670 END IF;
671 ---------------sechawla 19-feb-09 MG Impact on Varibale Rate user story END ------------
672
673
674
675 OPEN set_of_books_csr;
676 FETCH set_of_books_csr INTO l_set_of_books_id;
677 IF (set_of_books_csr%NOTFOUND) THEN
678 OKL_VARIABLE_INTEREST_PVT.print_error_message('Set of books cursor did not return any records');
679 RAISE create_stream_invoice_failed;
680 END IF;
681 CLOSE set_of_books_csr;
682
683 IF (p_create_invoice_flag = OKL_API.G_TRUE) THEN
684 IF (OKL_VARIABLE_INTEREST_PVT.G_BILLING_TRX_TYPE_ID IS NULL) THEN
685 OPEN trx_type_csr ('Billing');
686 FETCH trx_type_csr INTO OKL_VARIABLE_INTEREST_PVT.G_BILLING_TRX_TYPE_ID, OKL_VARIABLE_INTEREST_PVT.G_BILLING_TRX_TRY_ID, OKL_VARIABLE_INTEREST_PVT.G_BILLING_TRX_DESC;
687 IF (trx_type_csr%NOTFOUND) THEN
688 OKL_VARIABLE_INTEREST_PVT.print_error_message('Transaction type cursor did not return any records');
689 RAISE create_stream_invoice_failed;
690 END IF;
691 CLOSE trx_type_csr;
692 END IF;
693
694 OKL_VARIABLE_INTEREST_PVT.print_debug('G_CONTRACT_ID : '|| OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_ID);
695 -- Begin - Billing Inline changes - Bug#5898792 - varangan - 28/2/2007
696 lp_taiv_rec.khr_id := p_contract_id;
697 lp_taiv_rec.date_invoiced := p_due_date;
698 lp_taiv_rec.try_id := OKL_VARIABLE_INTEREST_PVT.G_BILLING_TRX_TYPE_ID;
699 lp_taiv_rec.date_entered := l_date_entered;
700 lp_taiv_rec.description := l_def_desc;
701 lp_taiv_rec.trx_status_code := l_final_status;
702 lp_taiv_rec.amount := p_amount;
703 lp_taiv_rec.currency_code := OKL_VARIABLE_INTEREST_PVT.G_CURRENCY_CODE;
704 lp_taiv_rec.org_id := OKL_VARIABLE_INTEREST_PVT.G_AUTHORING_ORG_ID;
705 lp_taiv_rec.set_of_books_id := l_set_of_books_id;
706 lp_taiv_rec.OKL_SOURCE_BILLING_TRX := G_SOURCE_BILLING_TRX;
707 -- Added by dpsingh for LE Uptake
708 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_contract_id) ;
709 IF l_legal_entity_id IS NOT NULL THEN
710 lp_taiv_rec.legal_entity_id := l_legal_entity_id;
711 ELSE
712 -- get the contract number
713 OPEN get_contract_number(p_contract_id);
714 FETCH get_contract_number INTO l_cntrct_number;
715 CLOSE get_contract_number;
716 Okl_Api.set_message(p_app_name => g_app_name,
717 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
718 p_token1 => 'CONTRACT_NUMBER',
719 p_token1_value => l_cntrct_number);
720 RAISE OKL_API.G_EXCEPTION_ERROR;
721 END IF;
722
723 OKL_VARIABLE_INTEREST_PVT.print_debug('Executing procedure OKL_TRX_AR_INVOICES_PUB.INSERT_TRX_AR_INVOICES');
724
725
726 -- Populate the Line record
727 lp_tilv_rec.org_id := OKL_VARIABLE_INTEREST_PVT.G_AUTHORING_ORG_ID;
728 lp_tilv_rec.line_number := 1;
729 lp_tilv_rec.kle_id := p_line_id;
730 -- x_invoice_id := r_taiv_rec.id;
731 lp_tilv_rec.description := l_def_desc;
732 lp_tilv_rec.inv_receiv_line_code := l_line_code;
733 lp_tilv_rec.amount := p_amount;
734 lp_tilv_rec.date_bill_period_start := p_due_date;
735 lp_tilv_rec.til_id_reverses := NULL;
736 lp_tilv_rec.tpl_id := NULL;
737 lp_tilv_rec.acn_id_cost := NULL;
738 lp_tilv_rec.sty_id := NULL;
739 lp_tilv_rec.quantity := NULL;
740 lp_tilv_rec.amount_applied := NULL;
741 lp_tilv_rec.receivables_invoice_id := NULL;
742 --sosharma added txl_ar_line_number start changes
743 lp_tilv_rec.txl_ar_line_number := 1;
744 -- sosharma end changes
745 lp_tilv_tbl(1) := lp_tilv_rec; -- Assign the line record in tilv_tbl structure
746
747
748 lp_tldv_rec.sty_id := l_sty_id;
749 lp_tldv_rec.amount := p_amount;
750 lp_tldv_rec.description := l_def_desc;
751 lp_tldv_rec.sel_id := lx_selv_rec.id;
752 --i_tldv_rec.til_id_details := r_tilv_rec.id;
753 lp_tldv_rec.line_detail_number := 1;
754 lp_tldv_rec.date_calculation := SYSDATE;
755 lp_tldv_rec.org_id := OKL_VARIABLE_INTEREST_PVT.G_AUTHORING_ORG_ID; --r_taiv_rec.org_id;
756 --sosharma added for billing issues
757 lp_tldv_rec.txl_ar_line_number := 1;
758 lp_tldv_tbl(1) := lp_tldv_rec;
759
760 ---------------------------------------------------------------------------
761 -- Call to Billing Centralized API
762 ---------------------------------------------------------------------------
763 okl_internal_billing_pvt.create_billing_trx(p_api_version =>l_api_version,
764 p_init_msg_list =>p_init_msg_list,
765 x_return_status => x_return_status,
766 x_msg_count => x_msg_count,
767 x_msg_data => x_msg_data,
768 p_taiv_rec => lp_taiv_rec,
769 p_tilv_tbl => lp_tilv_tbl,
770 p_tldv_tbl => lp_tldv_tbl,
771 x_taiv_rec => lx_taiv_rec,
772 x_tilv_tbl => lx_tilv_tbl,
773 x_tldv_tbl => lx_tldv_tbl);
774
775 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
776 OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to okl_internal_billing_pvt.create_billing_trx');
777 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
778 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
779 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to okl_internal_billing_pvt.create_billing_trx');
780 RAISE create_stream_invoice_failed;
781 END IF;
782 --sosharma added for billing changes
783 x_invoice_id := lx_taiv_rec.id;
784 --sosharma end
785
786
787
788 -- End - Billing Inline changes - Bug#5898792 - varangan - 28/2/2007
789
790 END IF;
791
792 IF (p_process_flag in ('DAILY_INTEREST', 'PRINCIPAL_CATCHUP')) THEN
793 OKL_VARIABLE_INTEREST_PVT.print_debug('Stream Type Purpose: '|| p_stream_type_purpose);
794 IF (p_stream_type_purpose IN ('DAILY_INTEREST_PRINCIPAL', 'DAILY_INTEREST_INTEREST')) THEN
795 ------------------------------------------------------------
796 -- Create Contract Transaction Header and Line
797 -- in Submitted Status
798 ------------------------------------------------------------
799 IF (OKL_VARIABLE_INTEREST_PVT.G_RCPT_APP_TRX_TYPE_ID IS NULL) THEN
800 OPEN trx_type_csr('Receipt Application');
801 FETCH trx_type_csr INTO OKL_VARIABLE_INTEREST_PVT.G_RCPT_APP_TRX_TYPE_ID, OKL_VARIABLE_INTEREST_PVT.G_RCPT_APP_TRX_TRY_ID, OKL_VARIABLE_INTEREST_PVT.G_RCPT_APP_TRX_DESC;
802 IF (trx_type_csr%NOTFOUND) THEN
803 CLOSE trx_type_csr;
804 OKL_VARIABLE_INTEREST_PVT.print_error_message('Transaction type cursor did not return any records');
805 RAISE create_stream_invoice_failed;
806 END IF;
807 CLOSE trx_type_csr;
808 END IF;
809 l_trx_type_id := OKL_VARIABLE_INTEREST_PVT.G_RCPT_APP_TRX_TYPE_ID;
810 l_trx_try_id := OKL_VARIABLE_INTEREST_PVT.G_RCPT_APP_TRX_TRY_ID;
811 l_trx_desc := OKL_VARIABLE_INTEREST_PVT.G_RCPT_APP_TRX_DESC;
812
813 IF (OKL_VARIABLE_INTEREST_PVT.G_RAP_TCN_TYPE IS NULL) THEN
814 OPEN fnd_lookup_csr('OKL_TCN_TYPE', 'RAP');
815 FETCH fnd_lookup_csr INTO OKL_VARIABLE_INTEREST_PVT.G_RAP_TCN_TYPE,OKL_VARIABLE_INTEREST_PVT.G_RAP_TCN_MEANING, OKL_VARIABLE_INTEREST_PVT.G_RAP_TCN_DESC;
816 IF (fnd_lookup_csr%NOTFOUND) THEN
817 CLOSE fnd_lookup_csr;
818 OKL_VARIABLE_INTEREST_PVT.print_error_message('FND lookup cursor did not return any records');
819 RAISE create_stream_invoice_failed;
820 END IF;
821 CLOSE fnd_lookup_csr;
822 END IF;
823
824 l_tcn_type := OKL_VARIABLE_INTEREST_PVT.G_RAP_TCN_TYPE;
825 l_tcn_meaning := OKL_VARIABLE_INTEREST_PVT.G_RAP_TCN_MEANING;
826 l_tcn_desc := OKL_VARIABLE_INTEREST_PVT.G_RAP_TCN_DESC;
827 -- new
828 ELSIF (p_stream_type_purpose IN ('PRINCIPAL_CATCHUP')) THEN
829 ------------------------------------------------------------
830 -- Create Contract Transaction Header and Line for Principal Adjustment
831 -- in Submitted Status
832 ------------------------------------------------------------
833 IF (OKL_VARIABLE_INTEREST_PVT.G_PAD_TRX_TYPE_ID IS NULL) THEN
834 OPEN trx_type_csr('Principal Adjustment');
835 FETCH trx_type_csr INTO OKL_VARIABLE_INTEREST_PVT.G_PAD_TRX_TYPE_ID, OKL_VARIABLE_INTEREST_PVT.G_PAD_TRX_TRY_ID, OKL_VARIABLE_INTEREST_PVT.G_PAD_TRX_DESC;
836 IF (trx_type_csr%NOTFOUND) THEN
837 CLOSE trx_type_csr;
838 OKL_VARIABLE_INTEREST_PVT.print_error_message('Transaction type cursor did not return any records');
839 RAISE create_stream_invoice_failed;
840 END IF;
841 CLOSE trx_type_csr;
842 END IF;
843 l_trx_type_id := OKL_VARIABLE_INTEREST_PVT.G_PAD_TRX_TYPE_ID;
844 l_trx_try_id := OKL_VARIABLE_INTEREST_PVT.G_PAD_TRX_TRY_ID;
845 l_trx_desc := OKL_VARIABLE_INTEREST_PVT.G_PAD_TRX_DESC;
846
847 IF (OKL_VARIABLE_INTEREST_PVT.G_PAD_TCN_TYPE IS NULL) THEN
848 OPEN fnd_lookup_csr('OKL_TCN_TYPE', 'PAD');
849 FETCH fnd_lookup_csr INTO OKL_VARIABLE_INTEREST_PVT.G_PAD_TCN_TYPE,OKL_VARIABLE_INTEREST_PVT.G_PAD_TCN_MEANING, OKL_VARIABLE_INTEREST_PVT.G_PAD_TCN_DESC;
850 IF (fnd_lookup_csr%NOTFOUND) THEN
851 CLOSE fnd_lookup_csr;
852 OKL_VARIABLE_INTEREST_PVT.print_error_message('FND lookup cursor did not return any records');
853 RAISE create_stream_invoice_failed;
854 END IF;
855 CLOSE fnd_lookup_csr;
856 END IF;
857
858 l_tcn_type := OKL_VARIABLE_INTEREST_PVT.G_PAD_TCN_TYPE;
859 l_tcn_meaning := OKL_VARIABLE_INTEREST_PVT.G_PAD_TCN_MEANING;
860 l_tcn_desc := OKL_VARIABLE_INTEREST_PVT.G_PAD_TCN_DESC;
861
862 END IF; -- new
863
864 OKL_VARIABLE_INTEREST_PVT.print_debug('G_CONTRACT_ID : '|| OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_ID);
865 OKL_VARIABLE_INTEREST_PVT.print_debug('l_tcn_type: '|| l_tcn_type);
866 OKL_VARIABLE_INTEREST_PVT.print_debug('l_trx_desc: '|| l_trx_desc);
867 OKL_VARIABLE_INTEREST_PVT.print_debug('l_trx_type_id: '|| l_trx_type_id);
868
869 l_trxH_in_rec.khr_id := p_contract_id;
870 l_trxH_in_rec.pdt_id := OKL_VARIABLE_INTEREST_PVT.G_PRODUCT_ID;
871 l_trxH_in_rec.set_of_books_id := l_set_of_books_id;
872 l_trxH_in_rec.tsu_code := 'PROCESSED';
873 l_trxH_in_rec.tcn_type := l_tcn_type; --G_RAP_TCN_TYPE;
874 l_trxH_in_rec.description := l_trx_desc; --G_RCPT_APP_TRX_DESC;
875 l_trxH_in_rec.date_transaction_occurred := p_due_date;
876 l_trxH_in_rec.try_id := l_trx_type_id; --G_RCPT_APP_TRX_TYPE_ID;
877 l_trxH_in_rec.amount := p_amount;
878 l_trxH_in_rec.currency_code := OKL_VARIABLE_INTEREST_PVT.G_CURRENCY_CODE;
879 l_trxH_in_rec.org_id := OKL_VARIABLE_INTEREST_PVT.G_AUTHORING_ORG_ID;
880 l_trxH_in_rec.request_id := OKL_VARIABLE_INTEREST_PVT.g_request_id;
881 -- Bug 5964482 dpsingh for AE signature Uptake start
882 OPEN get_dff_fields(p_contract_id);
883 FETCH get_dff_fields into l_trxH_in_rec.ATTRIBUTE_CATEGORY,
884 l_trxH_in_rec.ATTRIBUTE1,
885 l_trxH_in_rec.ATTRIBUTE2,
886 l_trxH_in_rec.ATTRIBUTE3,
887 l_trxH_in_rec.ATTRIBUTE4,
888 l_trxH_in_rec.ATTRIBUTE5,
889 l_trxH_in_rec.ATTRIBUTE6,
890 l_trxH_in_rec.ATTRIBUTE7,
891 l_trxH_in_rec.ATTRIBUTE8,
892 l_trxH_in_rec.ATTRIBUTE9,
893 l_trxH_in_rec.ATTRIBUTE10,
894 l_trxH_in_rec.ATTRIBUTE11,
895 l_trxH_in_rec.ATTRIBUTE12,
896 l_trxH_in_rec.ATTRIBUTE13,
897 l_trxH_in_rec.ATTRIBUTE14,
898 l_trxH_in_rec.ATTRIBUTE15;
899 CLOSE get_dff_fields;
900 -- Bug 5964482 dpsingh for AE signature Uptake start
901 -- Added by dpsingh for LE Uptake
902 l_trxH_in_rec.legal_entity_id := l_legal_entity_id;
903
904 IF (p_stream_type_purpose IN ('DAILY_INTEREST_PRINCIPAL', 'DAILY_INTEREST_INTEREST')) THEN
905
906 IF (OKL_VARIABLE_INTEREST_PVT.G_RAP_TCL_TYPE IS NULL) THEN
907 OPEN fnd_lookup_csr('OKL_TCL_TYPE', 'RAP');
908 FETCH fnd_lookup_csr INTO OKL_VARIABLE_INTEREST_PVT.G_RAP_TCL_TYPE,OKL_VARIABLE_INTEREST_PVT.G_RAP_TCL_MEANING, OKL_VARIABLE_INTEREST_PVT.G_RAP_TCL_DESC;
909 IF (fnd_lookup_csr%NOTFOUND) THEN
910 CLOSE fnd_lookup_csr;
911 OKL_VARIABLE_INTEREST_PVT.print_error_message('FND lookup cursor did not return any records');
912 RAISE create_stream_invoice_failed;
913 END IF;
914 CLOSE fnd_lookup_csr;
915 END IF;
916 l_tcl_type := OKL_VARIABLE_INTEREST_PVT.G_RAP_TCL_TYPE;
917 l_tcl_meaning := OKL_VARIABLE_INTEREST_PVT.G_RAP_TCL_MEANING;
918 l_tcl_desc := OKL_VARIABLE_INTEREST_PVT.G_RAP_TCL_DESC;
919 ELSIF (p_stream_type_purpose = 'PRINCIPAL_CATCHUP') THEN
920 IF (OKL_VARIABLE_INTEREST_PVT.G_PAD_TCL_TYPE IS NULL) THEN
921 OPEN fnd_lookup_csr('OKL_TCL_TYPE', 'PAD');
922 FETCH fnd_lookup_csr INTO OKL_VARIABLE_INTEREST_PVT.G_PAD_TCL_TYPE,OKL_VARIABLE_INTEREST_PVT.G_PAD_TCL_MEANING, OKL_VARIABLE_INTEREST_PVT.G_PAD_TCL_DESC;
923 IF (fnd_lookup_csr%NOTFOUND) THEN
924 CLOSE fnd_lookup_csr;
925 OKL_VARIABLE_INTEREST_PVT.print_error_message('FND lookup cursor did not return any records');
926 RAISE create_stream_invoice_failed;
927 END IF;
928 CLOSE fnd_lookup_csr;
929 END IF;
930 l_tcl_type := OKL_VARIABLE_INTEREST_PVT.G_PAD_TCL_TYPE;
931 l_tcl_meaning := OKL_VARIABLE_INTEREST_PVT.G_PAD_TCL_MEANING;
932 l_tcl_desc := OKL_VARIABLE_INTEREST_PVT.G_PAD_TCL_DESC;
933 END IF;
934
935 OKL_VARIABLE_INTEREST_PVT.print_debug('l_tcl_type : '||l_tcl_type);
936 OKL_VARIABLE_INTEREST_PVT.print_debug('l_tcl_desc : '||l_tcl_desc);
937
938 l_trxL_in_tbl(1).line_number := 1;
939 l_trxL_in_tbl(1).khr_id := p_contract_id;
940 l_trxL_in_tbl(1).sty_id := l_sty_id;
941 l_trxL_in_tbl(1).tcl_type := l_tcl_type; --G_RAP_TCL_TYPE;
942 l_trxL_in_tbl(1).description := l_tcl_desc; --G_RAP_TCL_DESC;
943 l_trxL_in_tbl(1).amount := p_amount;
944 l_trxL_in_tbl(1).currency_code := OKL_VARIABLE_INTEREST_PVT.G_CURRENCY_CODE;
945
946 OKL_VARIABLE_INTEREST_PVT.print_debug('Creating Contract Transaction.');
947 Okl_Trx_Contracts_Pub.create_trx_contracts(
948 p_api_version => l_api_version,
949 p_init_msg_list => p_init_msg_list,
950 x_return_status => x_return_status,
951 x_msg_count => x_msg_count,
952 x_msg_data => x_msg_data,
953 p_tcnv_rec => l_trxH_in_rec,
954 p_tclv_tbl => l_trxL_in_tbl,
955 x_tcnv_rec => l_trxH_out_rec,
956 x_tclv_tbl => l_trxL_out_tbl);
957
958 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
959 OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to Okl_Trx_Contracts_Pub.create_trx_contracts');
960 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
961 ELSIF (x_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
962 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to Okl_Trx_Contracts_Pub.create_trx_contracts');
963 RAISE create_stream_invoice_failed;
964 END IF;
965
966 IF ((l_trxH_out_rec.id = OKL_API.G_MISS_NUM) OR
967 (l_trxH_out_rec.id IS NULL) ) THEN
968 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'TRANSACTION_ID');
969 OKL_VARIABLE_INTEREST_PVT.print_error_message ('ERROR : OKL transaction ID is NULL');
970 RAISE create_stream_invoice_failed;
971 END IF;
972
973 l_okl_trx_created := OKL_API.G_TRUE;
974
975 ------------------------------------------------------------
976 -- Derive and Insert Distribution Line
977 ------------------------------------------------------------
978
979 OKL_VARIABLE_INTEREST_PVT.print_debug(' -- Creating Distributions. Supplied parameters:');
980
981 ------------------ Accounting Engine Calls --------------------------
982 --START: Changes by dpsingh for SLA Uptake, Bug #5964482
983 Okl_Acc_Call_Pvt.Okl_Populate_Acc_Gen (
984 p_contract_id => p_contract_id,
985 p_contract_line_id => p_line_id,
986 x_acc_gen_tbl => l_acc_gen_primary_key_tbl,
987 x_return_status => x_return_status);
988
989 OKL_VARIABLE_INTEREST_PVT.print_debug('End Debug OKLRVARB.pls call Okl_Acc_Call_Pvt.Okl_Populate_Acc_Gen ');
990 IF ( x_return_status = okl_api.g_ret_sts_success) THEN
991 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Accounting engine called successfully ');
992 ELSE
993 FND_FILE.PUT_LINE (FND_FILE.LOG, '*=> ERROR : Calling Accounting engine.');
994 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
995 OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to Okl_Acc_Call_Pvt.Okl_Populate_Acc_Gen');
996 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
997 ELSIF (x_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
998 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to Okl_Acc_Call_Pvt.Okl_Populate_Acc_Gen');
999 RAISE create_stream_invoice_failed;
1000 END IF;
1001 END IF;
1002 l_tcn_id := l_trxH_out_rec.id;
1003 IF l_trxL_out_tbl.count >0 THEN
1004 FOR i IN l_trxL_out_tbl.FIRST..l_trxL_out_tbl.LAST
1005 LOOP
1006 l_acc_gen_tbl(i).acc_gen_key_tbl := l_acc_gen_primary_key_tbl;
1007 l_acc_gen_tbl(i).source_id := l_trxL_out_tbl(i).id;
1008
1009 l_tmpl_identify_tbl(i).product_id := OKL_VARIABLE_INTEREST_PVT.G_PRODUCT_ID;
1010 l_tmpl_identify_tbl(i).transaction_type_id := l_trx_type_id; --G_RCPT_APP_TRX_TYPE_ID;
1011 l_tmpl_identify_tbl(i).stream_type_id := l_sty_id;
1012 l_tmpl_identify_tbl(i).ADVANCE_ARREARS := NULL;
1013 l_tmpl_identify_tbl(i).FACTORING_SYND_FLAG := NULL;
1014 l_tmpl_identify_tbl(i).SYNDICATION_CODE := NULL;
1015 l_tmpl_identify_tbl(i).FACTORING_CODE := NULL;
1016 l_tmpl_identify_tbl(i).MEMO_YN := 'N';
1017 l_tmpl_identify_tbl(i).PRIOR_YEAR_YN := 'N';
1018
1019 -- 4872347 Modified the source table and source id
1020 l_dist_info_tbl(i).source_id := l_trxL_out_tbl(1).id;
1021 l_dist_info_tbl(i).source_table := 'OKL_TXL_CNTRCT_LNS';
1022 l_dist_info_tbl(i).accounting_date := p_due_date;
1023 l_dist_info_tbl(i).gl_reversal_flag := 'N';
1024 l_dist_info_tbl(i).post_to_gl := 'Y';
1025 l_dist_info_tbl(i).amount := p_amount;
1026 l_dist_info_tbl(i).currency_code :=
1027 OKL_VARIABLE_INTEREST_PVT.G_CURRENCY_CODE;
1028 l_dist_info_tbl(i).contract_id := p_contract_id;
1029 l_dist_info_tbl(i).contract_line_id := p_line_id;
1030 END LOOP;
1031 END IF;
1032
1033 OKL_VARIABLE_INTEREST_PVT.print_debug('Begin Debug OKLRVARB.pls call Okl_Account_Dist_Pub.CREATE_ACCOUNTING_DIST ');
1034
1035 -- Call new signature
1036 Okl_Account_Dist_Pvt.CREATE_ACCOUNTING_DIST(
1037 p_api_version => p_api_version,
1038 p_init_msg_list => p_init_msg_list,
1039 x_return_status => x_return_status,
1040 x_msg_count => x_msg_count,
1041 x_msg_data => x_msg_data,
1042 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
1043 p_dist_info_tbl => l_dist_info_tbl,
1044 p_ctxt_val_tbl => l_ctxt_tbl,
1045 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
1046 x_template_tbl => l_template_out_tbl,
1047 x_amount_tbl => l_amount_out_tbl,
1048 p_trx_header_id => l_tcn_id);
1049
1050 --END: Changes by dpsingh for SLA Uptake, Bug #5964482
1051
1052 OKL_VARIABLE_INTEREST_PVT.print_debug('End Debug OKLRVARB.pls call Okl_Account_Dist_Pub.CREATE_ACCOUNTING_DIST ');
1053
1054 IF ( x_return_status = okl_api.g_ret_sts_success) THEN
1055 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Accounting distributions created. ');
1056 ELSE
1057 FND_FILE.PUT_LINE (FND_FILE.LOG, '*=> ERROR : Accounting distributions not created.'||x_msg_count || x_msg_data);
1058 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1059 OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to Okl_Account_Dist_Pub.CREATE_ACCOUNTING_DIST');
1060 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1061 ELSIF (x_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1062 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to Okl_Account_Dist_Pub.CREATE_ACCOUNTING_DIST');
1063 RAISE create_stream_invoice_failed;
1064 END IF;
1065 END IF;
1066 -- END IF;
1067
1068 -- Bug 7624242. SGIYER. Uncommented the MG Engine call for Variable Rate Contracts
1069 -- moved the call to MG engine here .. racheruv. Bug 7690456
1070 OKL_MULTIGAAP_ENGINE_PVT.CREATE_SEC_REP_TRX
1071 (p_api_version => p_api_version
1072 ,p_init_msg_list => p_init_msg_list
1073 ,x_return_status => x_return_status
1074 ,x_msg_count => x_msg_count
1075 ,x_msg_data => x_msg_data
1076 ,P_TCNV_REC => l_trxH_out_rec
1077 ,P_TCLV_TBL => l_trxL_out_tbl
1078 ,p_ctxt_val_tbl => l_ctxt_tbl
1079 ,p_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl);
1080
1081 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1082 OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to OKL_MULTIGAAP_ENGINE_PVT.CREATE_SEC_REP_TRX');
1083 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1084 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1085 OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to OKL_MULTIGAAP_ENGINE_PVT.CREATE_SEC_REP_TRX');
1086 RAISE Okl_Api.G_EXCEPTION_ERROR;
1087 END IF;
1088 -- end the call to MG engine .. racheruv. Bug 7690456
1089 END IF;
1090
1091
1092 EXCEPTION
1093 WHEN create_stream_invoice_failed THEN
1094 OKL_VARIABLE_INTEREST_PVT.print_error_message('Exception create_stream_invoice_failed raised in procedure CREATE_STREAM_INVOICE');
1095 IF (stream_csr%ISOPEN) THEN
1096 CLOSE stream_csr;
1097 END IF;
1098
1099 IF (trx_type_csr%ISOPEN) THEN
1100 CLOSE trx_type_csr;
1101 END IF;
1102
1103 IF (set_of_books_csr%ISOPEN) THEN
1104 CLOSE set_of_books_csr;
1105 END IF;
1106 /*
1107 IF l_sec_rep_method_csr%ISOPEN THEN
1108 CLOSE l_sec_rep_method_csr;
1109 END IF;
1110 */
1111 IF rep_stream_csr%ISOPEN THEN
1112 CLOSE rep_stream_csr;
1113 END IF;
1114
1115 -- Daily Interest calculation conc. program does not use savepoint. Delete the streams/Trx
1116 IF (p_process_flag = 'DAILY_INTEREST') THEN
1117 IF (l_strm_element_created = OKL_API.G_TRUE) THEN
1118 OKL_STREAMS_PUB.delete_stream_elements(
1119 p_api_version => 1.0,
1120 p_init_msg_list => OKC_API.G_FALSE,
1121 x_return_status => x_return_status,
1122 x_msg_count => x_msg_count,
1123 x_msg_data => x_msg_data,
1124 p_selv_rec => lx_selv_rec);
1125 END IF;
1126 IF (l_stream_created = OKL_API.G_TRUE) THEN
1127 OKL_STREAMS_PUB.delete_streams(
1128 p_api_version => 1.0,
1129 p_init_msg_list => OKC_API.G_FALSE,
1130 x_return_status => x_return_status,
1131 x_msg_count => x_msg_count,
1132 x_msg_data => x_msg_data,
1133 p_stmv_rec => lx_stmv_rec);
1134 END IF;
1135
1136 IF (l_okl_trx_created = OKL_API.G_TRUE) THEN
1137 OKL_TRX_CONTRACTS_PUB.delete_trx_contracts(
1138 p_api_version => 1.0,
1139 p_init_msg_list => OKC_API.G_FALSE,
1140 x_return_status => x_return_status,
1141 x_msg_count => x_msg_count,
1142 x_msg_data => x_msg_data,
1143 p_tcnv_rec => l_trxH_out_rec);
1144 END IF;
1145
1146 END IF;
1147 x_return_status := OKL_API.G_RET_STS_ERROR;
1148
1149 WHEN OTHERS THEN
1150 OKL_VARIABLE_INTEREST_PVT.print_error_message('Exception raised in procedure CREATE_STREAM_INVOICE');
1151 IF (stream_csr%ISOPEN) THEN
1152 CLOSE stream_csr;
1153 END IF;
1154
1155 IF (trx_type_csr%ISOPEN) THEN
1156 CLOSE trx_type_csr;
1157 END IF;
1158
1159 IF (set_of_books_csr%ISOPEN) THEN
1160 CLOSE set_of_books_csr;
1161 END IF;
1162 /*
1163 IF l_sec_rep_method_csr%ISOPEN THEN
1164 CLOSE l_sec_rep_method_csr;
1165 END IF;
1166 */
1167 IF rep_stream_csr%ISOPEN THEN
1168 CLOSE rep_stream_csr;
1169 END IF;
1170 -- Daily Interest calculation conc. program does not use savepoint. Delete the streams/Trx
1171 IF (p_process_flag = 'DAILY_INTEREST') THEN
1172 IF (l_strm_element_created = OKL_API.G_TRUE) THEN
1173 OKL_STREAMS_PUB.delete_stream_elements(
1174 p_api_version => 1.0,
1175 p_init_msg_list => OKC_API.G_FALSE,
1176 x_return_status => x_return_status,
1177 x_msg_count => x_msg_count,
1178 x_msg_data => x_msg_data,
1179 p_selv_rec => lx_selv_rec);
1180 END IF;
1181 IF (l_stream_created = OKL_API.G_TRUE) THEN
1182 OKL_STREAMS_PUB.delete_streams(
1183 p_api_version => 1.0,
1184 p_init_msg_list => OKC_API.G_FALSE,
1185 x_return_status => x_return_status,
1186 x_msg_count => x_msg_count,
1187 x_msg_data => x_msg_data,
1188 p_stmv_rec => lx_stmv_rec);
1189 END IF;
1190
1191 IF (l_okl_trx_created = OKL_API.G_TRUE) THEN
1192 OKL_TRX_CONTRACTS_PUB.delete_trx_contracts(
1193 p_api_version => 1.0,
1194 p_init_msg_list => OKC_API.G_FALSE,
1195 x_return_status => x_return_status,
1196 x_msg_count => x_msg_count,
1197 x_msg_data => x_msg_data,
1198 p_tcnv_rec => l_trxH_out_rec);
1199 END IF;
1200
1201 END IF;
1202
1203 Okl_Api.SET_MESSAGE(
1204 p_app_name => G_APP_NAME,
1205 p_msg_name => G_UNEXPECTED_ERROR,
1206 p_token1 => G_SQLCODE_TOKEN,
1207 p_token1_value => SQLCODE,
1208 p_token2 => G_SQLERRM_TOKEN,
1209 p_token2_value => SQLERRM);
1210
1211 x_return_status := OKL_API.G_RET_STS_ERROR;
1212
1213 END Create_Stream_Invoice;
1214
1215 END;