[Home] [Help]
PACKAGE BODY: APPS.OKL_INVESTOR_INVOICE_DISB_PVT
Source
1 PACKAGE BODY okl_investor_invoice_disb_pvt AS
2 /* $Header: OKLRIDBB.pls 120.36.12010000.3 2008/11/18 20:58:55 cklee ship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES';
5 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 L_LEVEL_PROCEDURE NUMBER;
7 IS_DEBUG_PROCEDURE_ON BOOLEAN;
8 -- End of wraper code generated automatically by Debug code generator
9
10 -- gboomina Bug 6788005 - Start
11 -- Moving cursors globaly so that this can be used in different API's
12
13 -----------------------------------------------------------------
14 -- Cursor to fetch Payout Attributes
15 -----------------------------------------------------------------
16 CURSOR payout_attrs_csr ( p_khr_id NUMBER ) IS
17 SELECT RULE_INFORMATION1,
18 RULE_INFORMATION2
19 FROM OKC_RULES_B rul,
20 Okc_rule_groups_B rgp
21 WHERE rul.rgp_id = rgp.id AND
22 rgp.rgd_code = 'LASEIR' AND
23 rul.rule_information_category = 'LASEIR' AND
24 rgp.dnz_chr_id = p_khr_id;
25
26 -----------------------------------------------------------------
27 -- Cursor to fetch Vendor Or Investor Name
28 -----------------------------------------------------------------
29 CURSOR vendor_name_csr ( p_vendor_id NUMBER ) IS
30 SELECT VENDOR_NAME
31 FROM po_vendors
32 WHERE VENDOR_ID = p_vendor_id;
33
34 -----------------------------------------------------------------
35 -- Cursor to fetch Vendor Site
36 -----------------------------------------------------------------
37 CURSOR vendor_site_csr ( p_vendor_site_id NUMBER ) IS
38 SELECT VENDOR_SITE_CODE
39 FROM po_vendor_sites
40 WHERE VENDOR_SITE_ID = p_vendor_site_id;
41
42 -----------------------------------------------------------------
43 -- Cursor to fetch Org Id
44 -----------------------------------------------------------------
45 CURSOR org_id_csr ( p_chr_id NUMBER ) IS
46 SELECT chr.authoring_org_id
47 FROM okc_k_headers_b chr
48 WHERE id = p_chr_id;
49
50 -----------------------------------------------------------------
51 -- Cursor to fetch Set Of Books
52 -----------------------------------------------------------------
53 CURSOR sob_csr ( p_org_id NUMBER ) IS
54 SELECT hru.set_of_books_id
55 FROM HR_OPERATING_UNITS HRU
56 WHERE ORGANIZATION_ID = p_org_id;
57
58 -----------------------------------------------------------------
59 -- Cursor to fetch Try Id
60 -----------------------------------------------------------------
61 CURSOR try_id_csr IS
62 SELECT id
63 FROM okl_trx_types_tl
64 WHERE name = 'Disbursement'
65 AND language= 'US';
66
67
68 -----------------------------------------------------------------
69 -- Cursor to fetch Investor Attributes
70 -----------------------------------------------------------------
71 CURSOR vendor_attrs_csr ( p_khr_id NUMBER, p_kle_id NUMBER ) IS
72 SELECT rul.object1_id1, -- Pay To Vendor Name
73 rul.object2_id1, -- Pay Site
74 rul.OBJECT3_ID1, -- Payment Term
75 RULE_INFORMATION1, -- Payment Method
76 RULE_INFORMATION2 -- Pay Group
77 FROM OKC_RULES_B rul,
78 Okc_rule_groups_B rgp
79 WHERE rul.rgp_id = rgp.id AND
80 rgp.rgd_code = 'LASEDB' AND
81 rul.rule_information_category = 'LASEDB' AND
82 rgp.dnz_chr_id = p_khr_id AND
83 rgp.cle_id = p_kle_id;
84
85
86 -----------------------------------------------------------------
87 -- Cursor to fetch Ap Interface Sequence Number
88 -----------------------------------------------------------------
89 CURSOR seq_csr IS
90 SELECT ap_invoices_interface_s.nextval
91 FROM dual;
92
93 -----------------------------------------------------------------
94 -- Cursor to fetch Stream Name
95 -----------------------------------------------------------------
96 CURSOR disb_strm_csr( p_sty_id NUMBER ) IS
97 SELECT name
98 FROM OKL_STRM_TYPE_V
99 WHERE id = p_sty_id;
100
101 -- gboomina Bug 6788005 - End
102
103 FUNCTION get_disb_amt(p_ia_id NUMBER
104 ,p_rbk_khr_id NUMBER
105 ,p_rbk_kle_id NUMBER)
106 RETURN NUMBER
107 AS
108
109 CURSOR disb_sel_amt_csr ( p_ia_id NUMBER,
110 p_rbk_khr_id NUMBER,
111 p_rbk_kle_id NUMBER ) IS
112 SELECT NVL(SUM(SEL.AMOUNT),0)
113 FROM OKL_STREAMS STM,
114 OKL_STRM_ELEMENTS SEL,
115 OKL_STRM_TYPE_V STY
116 WHERE STM.KHR_ID = p_rbk_khr_id
117 AND NVL(STM.KLE_ID,-99) = NVL ( p_rbk_kle_id,-99 )
118 AND STM.STY_ID = STY.ID
119 AND stm.source_table = 'OKL_K_HEADERS'
120 AND stm.source_id = p_ia_id
121 AND STM.SAY_CODE = 'CURR'
122 AND (STY.STREAM_TYPE_SUBCLASS = 'INVESTOR_DISBURSEMENT'
123 OR STY.stream_type_purpose in ( 'INVESTOR_RENT_DISB_BASIS','INVESTOR_PRINCIPAL_DISB_BASIS','INVESTOR_INTEREST_DISB_BASIS','INVESTOR_PPD_DISB_BASIS'))
124 AND SEL.STM_ID = STM.ID
125 AND SEL.DATE_BILLED IS NOT NULL;
126
127 l_rbk_adjst_amt NUMBER;
128 BEGIN
129
130 l_rbk_adjst_amt := 0;
131
132 OPEN disb_sel_amt_csr ( p_ia_id, p_rbk_khr_id, p_rbk_kle_id );
133 FETCH disb_sel_amt_csr INTO l_rbk_adjst_amt;
134 CLOSE disb_sel_amt_csr;
135
136 return NVL(l_rbk_adjst_amt,0);
137
138 EXCEPTION
139 WHEN others THEN
140 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '** EXCEPTION IN get_disb_amt: '||SQLERRM);
141 /* dbms_output.put_line('** EXCEPTION IN get_disb_amt: '||SQLERRM); */
142 return 0;
143 END get_disb_amt;
144
145 ---------------------------------------------------------------------------
146 -- FUNCTION get_seq_id
147 ---------------------------------------------------------------------------
148 FUNCTION get_seq_id RETURN NUMBER IS
149 BEGIN
150 RETURN(Okc_P_Util.raw_to_number(sys_guid()));
151 END get_seq_id;
152
153 FUNCTION get_next_pymt_date(p_start_date IN Date
154 ,p_frequency IN VARCHAR2
155 ,p_offset_date IN DATE DEFAULT SYSDATE) RETURN DATE
156 AS
157 l_next_date DATE := to_date(to_char(p_start_date, 'MM/DD') || to_char(p_offset_date, 'RRRR'), 'MM/DD/RRRR');
158 l_mnth_adder NUMBER := 0;
159 BEGIN
160 if(UPPER(p_frequency) = 'A') then
161 l_mnth_adder := 12;
162 elsif(UPPER(p_frequency) = 'Q') then
163 l_mnth_adder := 3;
164 elsif(UPPER(p_frequency) = 'M') then
165 l_mnth_adder := 1;
166 else
167 return null;
168 end if;
169
170 loop
171 select add_months(l_next_date, l_mnth_adder) INTO l_next_date from dual;
172 exit when l_next_date >= p_offset_date;
173 end loop;
174 return l_next_date;
175 EXCEPTION
176 WHEN others THEN
177 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '** EXCEPTION IN get_next_pymt_date: '||SQLERRM);
178 return null;
179 END get_next_pymt_date;
180
181 FUNCTION check_sel_billed( p_ref_sel_id NUMBER ) RETURN VARCHAR2
182
183 AS
184
185 /*
186 CURSOR lsm_csr ( p_ref_sel_id NUMBER ) IS
187 SELECT receivables_invoice_id
188 FROM okl_cnsld_ar_strms_b
189 WHERE sel_id = p_ref_sel_id AND
190 receivables_invoice_id IS NOT NULL;
191
192 */
193 --start:| 14-Mar-2007 cklee Bug fixed for billing impact |
194 -- CURSOR lsm_csr ( p_ref_sel_id NUMBER ) IS
195 CURSOR tld_csr ( p_ref_sel_id NUMBER ) IS
196 --end:| 14-Mar-2007 cklee Bug fixed for billing impact |
197 SELECT customer_trx_id
198 FROM okl_bpd_tld_ar_lines_v
199 WHERE sel_id = p_ref_sel_id AND
200 customer_trx_id IS NOT NULL;
201
202 --start:| 14-Mar-2007 cklee Bug fixed for billing impact |
203 -- l_recv_id okl_cnsld_ar_strms_b.receivables_invoice_id%TYPE;
204 l_recv_id okl_bpd_tld_ar_lines_v.customer_trx_id%TYPE;
205 --end:| 14-Mar-2007 cklee Bug fixed for billing impact |
206
207 BEGIN
208
209 IF p_ref_sel_id IS NULL THEN
210 return 'Y';
211 END IF;
212
213 l_recv_id := NULL;
214
215 --start:| 14-Mar-2007 cklee Bug fixed for billing impact |
216 -- OPEN lsm_csr ( p_ref_sel_id );
217 -- FETCH lsm_csr INTO l_recv_id;
218 -- CLOSE lsm_csr;
219 OPEN tld_csr ( p_ref_sel_id );
220 FETCH tld_csr INTO l_recv_id;
221 CLOSE tld_csr;
222 --end:| 14-Mar-2007 cklee Bug fixed for billing impact |
223
224 IF l_recv_id > 0 THEN
225 return 'Y';
226 ELSE
227 return 'N';
228 END IF;
229
230
231 EXCEPTION
232 WHEN others THEN
233 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '** EXCEPTION IN check_sel_billed: '||p_ref_sel_id||SQLERRM);
234 /* dbms_output.PUT_LINE ('** EXCEPTION IN check_sel_billed: '||p_ref_sel_id||SQLERRM); */
235 return 'N';
236 END check_sel_billed;
237
238
239
240 FUNCTION check_rcpts( p_ref_sel_id NUMBER ) RETURN VARCHAR2
241
242 AS
243
244 /*
245 CURSOR total_rcpts_csr ( p_lsm_id NUMBER ) IS
246 SELECT NVL(SUM(ARAPP.amount_applied),0)
247 FROM
248 OKL_CNSLD_AR_STRMS_V LSM,
249 AR_PAYMENT_SCHEDULES_ALL PMTSCH,
250 AR_RECEIVABLE_APPLICATIONS_ALL ARAPP
251 WHERE
252 LSM.ID = p_lsm_id AND
253 PMTSCH.customer_trx_id = LSM.receivables_invoice_id AND
254 ARAPP.applied_payment_schedule_id = PMTSCH.payment_schedule_id AND
255 PMTSCH.class = 'INV' AND
256 exists (
257 SELECT '1'
258 FROM OKL_INVESTOR_PAYOUT_SUMMARY_B pay
259 WHERE pay.receivable_application_id = ARAPP.receivable_application_id AND
260 pay.lsm_id = LSM.ID
261 );
262
263 */
264 CURSOR total_rcpts_csr ( p_tld_id NUMBER ) IS
265 SELECT NVL(SUM(ARAPP.amount_applied),0)
266 FROM
267 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
268 -- OKL_CNSLD_AR_STRMS_V LSM,
269 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
270 okl_bpd_tld_ar_lines_v TLD,
271 AR_PAYMENT_SCHEDULES_ALL PMTSCH,
272 AR_RECEIVABLE_APPLICATIONS_ALL ARAPP
273 WHERE
274 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
275 -- LSM.ID = p_lsm_id AND
276 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
277 TLD.TLD_ID = p_tld_id AND
278 PMTSCH.customer_trx_id = TLD.customer_trx_id AND
279 ARAPP.applied_payment_schedule_id = PMTSCH.payment_schedule_id AND
280 PMTSCH.class = 'INV' AND
281 exists (
282 SELECT '1'
283 FROM OKL_INVESTOR_PAYOUT_SUMMARY_B pay
284 WHERE pay.receivable_application_id = ARAPP.receivable_application_id AND
285 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
286 -- pay.lsm_id = TLD.TLD_ID );
287 pay.TLD_ID = TLD.TLD_ID );
288 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
289
290
291 /*
292 CURSOR total_bill_amt_csr ( p_lsm_id NUMBER ) IS
293 SELECT PMTSCH.amount_due_original
294 FROM OKL_CNSLD_AR_STRMS_V LSM,
295 AR_PAYMENT_SCHEDULES_ALL PMTSCH
296 WHERE LSM.ID = p_lsm_id AND
297 PMTSCH.customer_trx_id = LSM.receivables_invoice_id AND
298 PMTSCH.class = 'INV';
299
300 */
301
302 CURSOR total_bill_amt_csr ( p_tld_id NUMBER ) IS
303 SELECT PMTSCH.amount_due_original
304 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
305 -- FROM OKL_CNSLD_AR_STRMS_V LSM,
306 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
307 FROM okl_bpd_tld_ar_lines_v TLD,
308 AR_PAYMENT_SCHEDULES_ALL PMTSCH
309 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
310 -- WHERE LSM.ID = p_lsm_id AND
311 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
312 WHERE TLD.TLD_ID = p_tld_id AND
313 PMTSCH.customer_trx_id = TLD.customer_trx_id AND
314 PMTSCH.class = 'INV';
315 /*
316 CURSOR get_tld_id ( p_ref_sel_id NUMBER ) IS
317 SELECT TLD_ID
318 FROM okl_bpd_tld_ar_lines_v
319 WHERE sel_id = p_ref_sel_id;
320 */
321
322 /*
323 CURSOR get_lsm_id ( p_ref_sel_id NUMBER ) IS
324 SELECT ID
325 FROM OKL_CNSLD_AR_STRMS_V
326 WHERE sel_id = p_ref_sel_id;
327
328 */
329
330 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
331 -- CURSOR get_lsm_id ( p_ref_sel_id NUMBER ) IS
332 CURSOR get_tld_id ( p_ref_sel_id NUMBER ) IS
333 -- SELECT customer_trx_id
334 SELECT tld_id
335 FROM okl_bpd_tld_ar_lines_v
336 -- WHERE tld_id = p_ref_sel_id;
337 WHERE sel_id = p_ref_sel_id;
338 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
339
340 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
341 -- l_lsm_id okl_bpd_tld_ar_lines_v.customer_trx_id%TYPE;
342 l_tld_id OKL_TXD_AR_LN_DTLS_B.id%TYPE;
343 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
344 l_total_rcpt_amt AR_RECEIVABLE_APPLICATIONS_ALL.amount_applied%TYPE;
345 l_total_bill_amt AR_PAYMENT_SCHEDULES_ALL.amount_due_original%TYPE;
346
347 BEGIN
348 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
349 -- OPEN get_lsm_id ( p_ref_sel_id );
350 -- FETCH get_lsm_id INTO l_lsm_id;
351 -- CLOSE get_lsm_id;
352 OPEN get_tld_id ( p_ref_sel_id );
353 FETCH get_tld_id INTO l_tld_id;
354 CLOSE get_tld_id;
355 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
356
357 l_total_bill_amt := NULL;
358 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
359 -- OPEN total_bill_amt_csr( l_lsm_id );
360 OPEN total_bill_amt_csr( l_tld_id );
361 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
362 FETCH total_bill_amt_csr INTO l_total_bill_amt;
363 CLOSE total_bill_amt_csr;
364
365 l_total_rcpt_amt := NULL;
366 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
367 -- OPEN total_rcpts_csr( l_lsm_id );
368 OPEN total_rcpts_csr( l_tld_id );
369 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
370 FETCH total_rcpts_csr INTO l_total_rcpt_amt;
371 CLOSE total_rcpts_csr;
372
373 IF ( l_total_rcpt_amt >= l_total_bill_amt ) THEN
374 return 'FULL';
375 ELSE
376 return 'PARTIAL';
377 END IF;
378
379
380 EXCEPTION
381 WHEN others THEN
382 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '** EXCEPTION IN check_rcpts: '||p_ref_sel_id||SQLERRM);
383 /* dbms_output.put_line('** EXCEPTION IN check_rcpts: '||p_ref_sel_id||SQLERRM); */
384 return NULL;
385 END check_rcpts;
386
387 -- gboomina Created for Bug 6788005 - Start
388 -------------------------------------------------------------------------
389 -- Start of comments
390 --
391 -- Procedure Name : okl_investor_fee_disb
392 -- Description : This API is used to create disbursement transactions
393 -- for the fees defined in the Investor Agreement
394 -- Business Rules :
395 -- Parameters :
396 -- Version : 1.0
397 --
398 -- End of comments
399 -------------------------------------------------------------------------
400 PROCEDURE okl_investor_fee_disb(
401 p_api_version IN NUMBER
402 , p_init_msg_list IN VARCHAR2
403 , x_return_status OUT NOCOPY VARCHAR2
404 , x_msg_count OUT NOCOPY NUMBER
405 , x_msg_data OUT NOCOPY VARCHAR2
406 , p_investor_agreement IN VARCHAR2
407 , p_to_date IN DATE)
408 IS
409
410 -- Cursor to get Investor Agreement details
411 CURSOR ia_info_csr IS
412 SELECT ia.id,
413 ia.authoring_org_id,
414 ia.currency_code,
415 ia_okl.currency_conversion_type,
416 ia_okl.currency_conversion_rate,
417 ia_okl.currency_conversion_date,
418 ia_okl.legal_entity_id
419 FROM okc_k_headers_b ia,
420 okl_k_headers ia_okl
421 WHERE ia.contract_number = p_investor_agreement
422 AND ia_okl.id = ia.id;
423
424 ia_info_rec ia_info_csr%rowtype;
425
426 -- Cursor to get Investors defined in an Investor Agreement
427 CURSOR investor_line_csr( p_ia_id NUMBER) IS
428 SELECT
429 clet.id , -- investor line id
430 clet.cust_acct_id,
431 clet.bill_to_site_use_id,
432 klet.pay_investor_event,
433 klet.pay_investor_frequency,
434 klet.date_pay_investor_start,
435 klet.pay_investor_remittance_days
436 FROM okl_k_headers_full_v khr,
437 okl_k_lines klet,
438 okc_k_lines_b clet,
439 okc_line_styles_b lset
440 WHERE khr.id = p_ia_id
441 AND klet.id = clet.id
442 AND khr.id = clet.dnz_chr_id
443 AND clet.lse_id = lset.id
444 AND lset.lty_code = 'INVESTMENT'
445 ORDER BY 1;
446
447 investor_line_rec investor_line_csr%rowtype;
448
449 -- Cursor to get Fee lines defined for an Investor in an Investor Agreement
450 CURSOR investor_fee_line_csr ( p_ia_id NUMBER, p_investor_line_id NUMBER) IS
451 SELECT okc_fee_line.id
452 , okc_fee_line.start_date
453 , NVL(okl_fee_line.amount,0) amount
454 , okl_fee_line.fee_type
455 FROM okc_k_lines_b okc_fee_line ,
456 okl_k_lines okl_fee_line ,
457 okc_line_styles_b lse ,
458 okc_k_party_roles_b inv_line_role ,
459 okc_k_party_roles_b fee_line_role
460 WHERE inv_line_role.cle_id = p_investor_line_id
461 AND inv_line_role.dnz_chr_id = p_ia_id
462 AND inv_line_role.object1_id1 = fee_line_role.object1_id1
463 AND inv_line_role.rle_code = fee_line_role.rle_code
464 AND inv_line_role.dnz_chr_id = fee_line_role.dnz_chr_id
465 AND fee_line_role.cle_id = okc_fee_line.id
466 AND okc_fee_line.lse_id = lse.id
467 AND lse.lty_code = 'FEE'
468 AND okc_fee_line.chr_id = fee_line_role.dnz_chr_id
469 AND okc_fee_line.id = okl_fee_line.id
470 AND okl_fee_line.fee_type = 'EXPENSE'
471 AND trunc(okc_fee_line.start_date) <= trunc(NVL(p_to_date, SYSDATE));
472
473 investor_fee_line_rec investor_fee_line_csr%rowtype;
474
475 -- Cursor to get the total amount already disbursed to the investor
476 CURSOR c_tot_amt_disbursed (p_ia_id NUMBER, p_vendor_site_id NUMBER, p_fee_line_id NUMBER)
477 IS
478 SELECT NVL(SUM(b.amount),0)
479 FROM okl_trx_ap_invoices_b a,
480 okl_txl_ap_inv_lns_b b
481 WHERE a.id = b.tap_id
482 AND a.trx_status_code in ('ENTERED', 'APPROVED', 'PROCESSED')
483 AND b.amount > 0
484 AND b.khr_id = p_ia_id
485 AND b.kle_id = p_fee_line_id
486 AND EXISTS (SELECT NULL
487 FROM okx_vendor_sites_v vs
488 WHERE vs.id1 = a.ipvs_id
489 AND vs.id1 = p_vendor_site_id);
490
491
492 -- Cursor to get the fee stream type id
493 CURSOR fee_sty_id_csr(p_fee_line_id NUMBER) IS
494 SELECT object1_id1 fee_sty_id
495 FROM okc_k_items
496 WHERE cle_id = p_fee_line_id;
497
498 l_api_version CONSTANT NUMBER := 1;
499 l_api_name CONSTANT VARCHAR2(30) := 'okl_investor_fee_disb';
500 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
501
502 l_pay_to_id NUMBER;
503 l_pay_site_id NUMBER;
504 l_payment_term_id okl_trx_ap_invoices_v.ippt_id%TYPE;
505 l_payment_method okl_trx_ap_invoices_v.payment_method_code%TYPE;
506 l_pay_group_code okl_trx_ap_invoices_v.pay_group_lookup_code%TYPE;
507 l_fee_line_amount NUMBER;
508 l_amount_disbursed NUMBER;
509 l_amnt_to_be_disbursed NUMBER;
510 l_sty_id okl_strm_type_v.id%TYPE;
511 l_try_id okl_trx_types_v.id%TYPE;
512 l_payout_date DATE;
513 l_investor_name PO_VENDORS.vendor_name%TYPE;
514 l_investor_site_code PO_VENDOR_SITES.vendor_site_code%TYPE;
515 l_strm_name OKL_STRM_TYPE_V.name%TYPE;
516
517 l_ia_id okl_k_headers_full_v.id%type;
518 l_currency_code okl_k_headers_full_v.currency_code%TYPE;
519 l_currency_conv_type okl_k_headers_full_v.currency_conversion_type%TYPE;
520 l_currency_conv_rate okl_k_headers_full_v.currency_conversion_rate%TYPE;
521 l_currency_conv_date okl_k_headers_full_v.currency_conversion_date%TYPE;
522
523 i_tapv_rec Okl_Trx_Ap_Invoices_Pub.tapv_rec_type;
524 i_tplv_rec OKL_TXL_AP_INV_LNS_PUB.tplv_rec_type;
525 i_tplv_tbl okl_tpl_pvt.tplv_tbl_type;
526 r_tapv_rec Okl_Trx_Ap_Invoices_Pub.tapv_rec_type;
527 r_tplv_tbl okl_tpl_pvt.tplv_tbl_type;
528
529 BEGIN
530
531 ------------------------------------------------------------
532 -- Start Processing
533 ------------------------------------------------------------
534 x_return_status := OKL_API.G_RET_STS_SUCCESS;
535
536 l_return_status := OKL_API.START_ACTIVITY(
537 p_api_name => l_api_name,
538 p_pkg_name => g_pkg_name,
539 p_init_msg_list => p_init_msg_list,
540 l_api_version => l_api_version,
541 p_api_version => p_api_version,
542 p_api_type => '_PVT',
543 x_return_status => l_return_status);
544
545 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
546 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
547 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
548 RAISE OKL_API.G_EXCEPTION_ERROR;
549 END IF;
550
551 -- Get Investor Agreement details
552 OPEN ia_info_csr;
553 FETCH ia_info_csr INTO ia_info_rec;
554 CLOSE ia_info_csr;
555 l_ia_id := ia_info_rec.id;
556
557 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Processing for Disbursement of Fees :');
558 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Investor Agreement :'||p_investor_agreement );
559 -- Get the investors defined on an Investor Agreement
560 FOR investor_line_rec IN investor_line_csr( l_ia_id)
561 LOOP
562 -- Get all the expense fees defined for an Investor
563 FOR investor_fee_line_rec IN investor_fee_line_csr( l_ia_id
564 , investor_line_rec.id )
565 LOOP
566 -- Check whether the fee is already disbursed or not.
567 l_fee_line_amount := investor_fee_line_rec.amount;
568
569 OPEN vendor_attrs_csr( l_ia_id, investor_line_rec.id );
570 FETCH vendor_attrs_csr INTO l_pay_to_id, l_pay_site_id,
571 l_payment_term_id, l_payment_method,
572 l_pay_group_code;
573 CLOSE vendor_attrs_csr;
574
575 ----------------------------------------
576 -- Get Vendor Name from PO_Vendors_All
577 ----------------------------------------
578 l_investor_name := NULL;
579 OPEN vendor_name_csr ( l_pay_to_id );
580 FETCH vendor_name_csr INTO l_investor_name;
581 CLOSE vendor_name_csr;
582
583 ----------------------------------------
584 -- Get Vendor Site from po_vendor_sites
585 ----------------------------------------
586 l_investor_site_code := NULL;
587 OPEN vendor_site_csr ( l_pay_site_id );
588 FETCH vendor_site_csr INTO l_investor_site_code;
589 CLOSE vendor_site_csr;
590
591 -- Get the fee amount that has been disbursed already
592 OPEN c_tot_amt_disbursed( l_ia_id, l_pay_site_id, investor_fee_line_rec.id );
593 FETCH c_tot_amt_disbursed INTO l_amount_disbursed;
594 CLOSE c_tot_amt_disbursed;
595
596 -- Get the remaining amount to be disbursed
597 -- For the first time, amount to be disbursed will be same as the
598 -- fee line amount
599 l_amnt_to_be_disbursed := l_fee_line_amount - l_amount_disbursed;
600 IF ( l_amnt_to_be_disbursed > 0) THEN
601
602 -- Get Try Id
603 OPEN try_id_csr;
604 FETCH try_id_csr INTO l_try_id;
605 CLOSE try_id_csr;
606
607 -- Get Sty Id
608 OPEN fee_sty_id_csr ( investor_fee_line_rec.id );
609 FETCH fee_sty_id_csr INTO l_sty_id;
610 CLOSE fee_sty_id_csr;
611
612 -- Get Sty Id
613 OPEN disb_strm_csr ( l_sty_id );
614 FETCH disb_strm_csr INTO l_strm_name;
615 CLOSE disb_strm_csr;
616
617
618 -- Calculate Payout Date
619 l_payout_date := trunc(SYSDATE) + investor_line_rec.pay_investor_remittance_days;
620 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-------------------------------------------------------------');
621 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Investor: '||l_investor_name);
622 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Investor Site: '||l_investor_site_code);
623 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Fee Name: '||l_strm_name);
624 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Fee Type: '||investor_fee_line_rec.fee_type);
625 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Amount: '||investor_fee_line_rec.amount);
626 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Fee Effective From: '||investor_fee_line_rec.start_date);
627 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Payout Event: '||investor_line_rec.PAY_INVESTOR_EVENT);
628 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Payout Start Date: '||investor_line_rec.DATE_PAY_INVESTOR_START);
629 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Remittance Days: '||investor_line_rec.pay_investor_remittance_days);
630 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Payout Frequency: '||investor_line_rec.PAY_INVESTOR_FREQUENCY);
631 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-------------------------------------------------------------');
632 -- Populate Investor Disbursement Header
633 i_tapv_rec.org_id := ia_info_rec.authoring_org_id;
634 i_tapv_rec.currency_code := ia_info_rec.currency_code;
635 i_tapv_rec.CURRENCY_CONVERSION_TYPE := ia_info_rec.currency_conversion_type;
636 i_tapv_rec.CURRENCY_CONVERSION_RATE := ia_info_rec.currency_conversion_rate;
637 i_tapv_rec.CURRENCY_CONVERSION_DATE := ia_info_rec.currency_conversion_date;
638 i_tapv_rec.legal_entity_id := ia_info_rec.legal_entity_id;
639 i_tapv_rec.try_id := l_try_id;
640 i_tapv_rec.invoice_type := 'STANDARD';
641
642 -- The following parameters will be populated in
643 -- Okl_Create_Disb_Trans_Pvt if NULL is passed
644 i_tapv_rec.set_of_books_id := NULL;
645 i_tapv_rec.invoice_number := NULL;
646 i_tapv_rec.vendor_invoice_number := NULL;
647
648 i_tapv_rec.vendor_id := l_pay_to_id;
649 i_tapv_rec.ipvs_id := l_pay_site_id;
650 i_tapv_rec.khr_id := l_ia_id;
651
652 i_tapv_rec.payment_method_code := l_payment_method;
653 i_tapv_rec.date_entered := l_payout_date;
654 i_tapv_rec.date_invoiced := l_payout_date;
655 i_tapv_rec.invoice_category_code := NULL;
656 i_tapv_rec.ippt_id := l_payment_term_id;
657 i_tapv_rec.DATE_GL := l_payout_date;
658 i_tapv_rec.Pay_Group_lookup_code := l_pay_group_code;
659 i_tapv_rec.trx_status_code := 'ENTERED';
660 i_tapv_rec.nettable_yn := 'N';
661
662 i_tapv_rec.amount := okl_accounting_util.cross_currency_round_amount
663 (p_amount => l_amnt_to_be_disbursed
664 ,p_currency_code => ia_info_rec.currency_code);
665
666 -- Populate Investor Disbursement Lines
667 i_tplv_rec.tap_id := NULL;
668 i_tplv_rec.amount := i_tapv_rec.amount;
669 i_tplv_rec.inv_distr_line_code := 'INVESTOR';
670 i_tplv_rec.line_number := 1;
671 i_tplv_rec.org_id := ia_info_rec.authoring_org_id;
672 i_tplv_rec.disbursement_basis_code := 'BILL_DATE';
673 i_tplv_rec.khr_id := l_ia_id;
674 i_tplv_rec.kle_id := investor_fee_line_rec.id;
675 i_tplv_rec.sty_id := l_sty_id;
676
677 -- Add tpl_rec to table
678 i_tplv_tbl(1) := i_tplv_rec;
679
680 --Call the commong disbursement API to create transactions
681 Okl_Create_Disb_Trans_Pvt.create_disb_trx(
682 p_api_version => p_api_version
683 ,p_init_msg_list => p_init_msg_list
684 ,x_return_status => l_return_status
685 ,x_msg_count => x_msg_count
686 ,x_msg_data => x_msg_data
687 ,p_tapv_rec => i_tapv_rec
688 ,p_tplv_tbl => i_tplv_tbl
689 ,x_tapv_rec => r_tapv_rec
690 ,x_tplv_tbl => r_tplv_tbl);
691
692 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
693 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
694 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
695 RAISE OKL_API.G_EXCEPTION_ERROR;
696 END IF;
697
698 END IF;
699 END LOOP;
700 END LOOP;
701
702 ------------------------------------------------------------
703 -- End Processing
704 ------------------------------------------------------------
705 x_return_status := l_return_status;
706
707 Okl_Api.END_ACTIVITY (
708 x_msg_count => x_msg_count,
709 x_msg_data => x_msg_data);
710
711 EXCEPTION
712 ------------------------------------------------------------
713 -- Exception handling
714 ------------------------------------------------------------
715
716 WHEN OKL_API.G_EXCEPTION_ERROR THEN
717 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' ERROR 1: '||SQLERRM);
718 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
719 p_api_name => l_api_name,
720 p_pkg_name => G_PKG_NAME,
721 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
722 x_msg_count => x_msg_count,
723 x_msg_data => x_msg_data,
724 p_api_type => '_PVT');
725
726 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
727 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' ERROR 2: '||SQLERRM);
728 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
729 p_api_name => l_api_name,
730 p_pkg_name => G_PKG_NAME,
731 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
732 x_msg_count => x_msg_count,
733 x_msg_data => x_msg_data,
734 p_api_type => '_PVT');
735
736 WHEN OTHERS THEN
737 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' ERROR 3: '||SQLERRM);
738 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
739 p_api_name => l_api_name,
740 p_pkg_name => G_PKG_NAME,
741 p_exc_name => 'OTHERS',
742 x_msg_count => x_msg_count,
743 x_msg_data => x_msg_data,
744 p_api_type => '_PVT');
745
746 END okl_investor_fee_disb;
747 -- gboomina Bug 6788005 - End
748
749 PROCEDURE OKL_INVESTOR_DISBURSEMENT
750 (p_api_version IN NUMBER
751 ,p_init_msg_list IN VARCHAR2
752 ,x_return_status OUT NOCOPY VARCHAR2
753 ,x_msg_count OUT NOCOPY NUMBER
754 ,x_msg_data OUT NOCOPY VARCHAR2
755 ,p_investor_agreement IN VARCHAR2
756 ,p_to_date IN DATE)
757 IS
758 ------------------------------------------------------------
759 -- Declare Variables required by APIs
760 ------------------------------------------------------------
761 l_api_version CONSTANT NUMBER := 1;
762 l_api_name CONSTANT VARCHAR2(30) := 'OKL_INVESTOR_DISBURSEMENT';
763 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
764
765 -----------------------------------------------------------------
766 -- To pick up Investor Invoice Stream elements
767 -----------------------------------------------------------------
768 CURSOR inv_lease_k_csr ( p_inv_agr VARCHAR2 ) IS
769 SELECT DISTINCT
770 IA.contract_number Investor_Agreement,
771 IA.id Investor_Agreement_ID,
772 IA.pdt_id pdt_id,
773 IA.currency_code,
774 IA.currency_conversion_type,
775 IA.currency_conversion_rate,
776 IA.currency_conversion_date,
777 Poc.khr_id
778 -- 02-NOV-2006 ANSETHUR R12B - Legal Entity
779 ,IA.legal_entity_id
780 FROM OKL_POOLS pol,
781 okl_k_headers_full_v IA,
782 okl_pool_contents_v poc,
783 okl_k_headers_full_v LK_KHR
784 WHERE IA.Contract_number = NVL( p_inv_agr, IA.Contract_number)
785 AND IA.ID = POL.khr_id
786 AND pol.id = poc.pol_id
787 AND poc.khr_id = LK_KHR.id;
788 -- Bug#7009075 - Commented to ensure that IA that have inactive pool contents are also
789 -- considered for disbursement. Securtization of future streams and termination of contract
790 -- will inactivate the pool contents. In this case, the INVESTOR CONTRACT OBLIGATION streams
791 -- will never get disbursed if pool status is checked for Active.
792 -- AND POC.status_code = 'ACTIVE';
793
794 -----------------------------------------------------------------
795 -- To pick up Investor Invoice Stream elements
796 -----------------------------------------------------------------
797 CURSOR inv_disb_main_csr ( p_ia_id NUMBER,
798 p_lk_khr_id NUMBER,
799 p_date_to DATE ) IS
800 SELECT
801 stm.khr_id khr_id,
802 TRUNC (ste.stream_element_date) bill_date,
803 stm.kle_id kle_id,
804 ste.id sel_id,
805 ste.sel_id ref_sel_id,
806 stm.sty_id sty_id,
807 khr.contract_number lease_contract,
808 khr.currency_code currency_code,
809 khr.authoring_org_id authoring_org_id,
810 sty.stream_type_purpose stream_purpose,
811 sty.name stream_name,
812 sty.taxable_default_yn taxable_default_yn,
813 sty.stream_type_subclass subclass,
814 ste.amount amount,
815 khr.sts_code sts_code
816 --ssiruvol Bug 5000886 start
817 ,(select id
818 from OKL_TXD_AR_LN_DTLS_B cnsld
819 where cnsld.khr_id=p_lk_khr_id
820 AND cnsld.sel_id =ste.sel_id
821 --AND cnsld.receivables_invoice_id IS NOT NULL
822 )
823 cnsld_id,
824 --ssiruvol Bug 5000886 end
825 oklh.deal_type deal_type
826 FROM
827 okl_strm_elements ste,
828 okl_streams stm,
829 okl_strm_type_v sty,
830 okc_k_headers_b khr,
831 okl_k_headers oklh
832 WHERE trunc(ste.stream_element_date) <= trunc(NVL(p_date_to, SYSDATE))
833 AND ste.amount <> 0
834 AND stm.id = ste.stm_id
835 AND sty.stream_type_subclass IN ('INVESTOR_DISBURSEMENT','LATE_CHARGE')
836 --Added by bkatraga for bug 6983321
837 AND sty.stream_type_purpose NOT IN('INVESTOR_RENT_DISB_BASIS')
838 --end bkatraga
839 AND ste.date_billed IS NULL
840 AND stm.active_yn = 'Y'
841 AND stm.say_code = 'CURR'
842 -- New Criteria added for further refining the
843 -- search criteria
844 AND stm.source_table = 'OKL_K_HEADERS'
845 AND stm.source_id = p_ia_id
846 -- End new criteria
847 AND sty.id = stm.sty_id
848 AND khr.id = stm.khr_id
849 AND khr.ID = p_lk_khr_id
850 AND khr.id = oklh.id
851 --Added by bkatraga for bug 6983321
852 UNION ALL
853 --end bkatraga
854 SELECT
855 stm.khr_id khr_id,
856 TRUNC (ste.stream_element_date) bill_date,
857 stm.kle_id kle_id,
858 ste.id sel_id,
859 ste.sel_id ref_sel_id,
860 stm.sty_id sty_id,
861 khr.contract_number lease_contract,
862 khr.currency_code currency_code,
863 khr.authoring_org_id authoring_org_id,
864 sty.stream_type_purpose stream_purpose,
865 sty.name stream_name,
866 sty.taxable_default_yn taxable_default_yn,
867 sty.stream_type_subclass subclass,
868 ste.amount amount,
869 khr.sts_code sts_code
870 --ssiruvol Bug 5000886 start
871 ,(select id
872 from OKL_TXD_AR_LN_DTLS_B cnsld
873 where cnsld.khr_id=p_lk_khr_id
874 AND cnsld.sel_id =ste.sel_id
875 --AND cnsld.receivables_invoice_id IS NOT NULL
876 )
877 cnsld_id,
878 --ssiruvol Bug 5000886 end
879 khl.deal_type deal_type
880 FROM
881 okl_strm_elements ste,
882 -- Bug 4550607
883 okl_strm_elements ste1,
884 -- End Bug 4550607
885 okl_streams stm,
886 okl_strm_type_v sty,
887 okc_k_headers_b khr,
888 okl_k_headers khl,
889 okc_statuses_b khs ,
890 --Added by bkatraga for bug 6983321
891 okl_pools pl,
892 okl_pool_contents plcont
893 --pgomes fix for bug 4430377
894 WHERE trunc(ste.stream_element_date) <= trunc(NVL(p_date_to, SYSDATE))
895 AND ste.amount <> 0
896 AND stm.id = ste.stm_id
897 AND sty.stream_type_subclass IS NULL
898 AND sty.stream_type_purpose in ( 'INVESTOR_RENT_DISB_BASIS','INVESTOR_PRINCIPAL_DISB_BASIS','INVESTOR_INTEREST_DISB_BASIS','INVESTOR_PPD_DISB_BASIS')
899 AND ste.date_billed IS NULL
900 -- New Criteria added for further refining the
901 -- search criteria
902 AND stm.source_table = 'OKL_K_HEADERS'
903 AND stm.source_id = p_ia_id
904 -- End new criteria
905 AND sty.id = stm.sty_id
906 AND sty.billable_yn = 'N'
907 AND khr.id = stm.khr_id
908 AND khr.scs_code IN ('LEASE', 'LOAN')
909 -- AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
910 --Added TERMINATED, EXPIRED statuses by bkatraga for bug 7120711
911 AND khr.sts_code IN ( 'BOOKED','EVERGREEN','TERMINATED','EXPIRED')
912 AND khl.id = stm.khr_id
913 AND khl.deal_type IS NOT NULL
914 AND khs.code = khr.sts_code
915 -- AND khs.ste_code = 'ACTIVE'
916 --Added TERMINATED, EXPIRED statuses by bkatraga for bug 7120711
917 AND khs.ste_code IN ('ACTIVE','TERMINATED','EXPIRED')
918 --AND kle.id (+)= stm.kle_id
919 --AND kls.code (+)= kle.sts_code
920 --AND NVL (kls.ste_code, 'ACTIVE') = 'ACTIVE'
921 AND khr.ID = p_lk_khr_id
922 -- Bug 4550607
923 AND ste1.id = ste.sel_id
924 AND khl.id= khr.id
925 AND ste1.date_billed is not null
926 -- End Bug 4550607
927 --Added by bkatraga for bug 6983321
928 AND pl.khr_id = p_ia_id
929 AND pl.id = plcont.pol_id
930 AND plcont.khr_id = p_lk_khr_id
931 AND plcont.STATUS_CODE = 'ACTIVE'
932 AND plcont.stm_id = stm.stm_id
933 AND (trunc(ste.stream_element_date) <= trunc(plcont.streams_to_date) OR
934 plcont.streams_to_date IS NULL)
935 --end bkatraga
936 ORDER BY 1, 2, 3;
937
938 -----------------------------------------------------------------
939 -- To fetch revenue share lines
940 -----------------------------------------------------------------
941 CURSOR share_csr ( p_ia_id NUMBER ) IS
942 SELECT
943 CLET.ID TOP_LINE_ID,
944 CLET.cust_acct_id,
945 CLET.bill_to_site_use_id,
946 KLET.pay_investor_event,
947 KLET.pay_investor_frequency,
948 KLET.date_pay_investor_start,
949 KLET.pay_investor_remittance_days
950 FROM OKL_K_HEADERS_FULL_V KHR,
951 OKL_K_LINES KLET,
952 OKC_K_LINES_B CLET,
953 OKC_LINE_STYLES_B LSET
954 WHERE KHR.ID = p_ia_id
955 AND KLET.ID = CLET.ID
956 AND KHR.ID = CLET.DNZ_CHR_ID
957 AND CLET.LSE_ID = LSET.ID
958 AND LSET.LTY_CODE = 'INVESTMENT'
959 ORDER BY 1;
960
961 l_investor_name PO_VENDORS.vendor_name%TYPE;
962 l_investor_site_code PO_VENDOR_SITES.vendor_site_code%TYPE;
963
964 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
965 -- CURSOR rcpts_csr ( p_lsm_id NUMBER,
966 --start:| 25-Jun-2008 cklee Fixed bug: 6784252 |
967 /*
968 CURSOR rcpts_csr ( p_tld_id NUMBER,
969 p_inv_agr_id NUMBER,
970 p_inv_agr_line_id NUMBER ) IS
971 SELECT ARAPP.receivable_application_id,
972 ARAPP.cash_receipt_id,
973 ARAPP.LINE_APPLIED AMOUNT_APPLIED,
974 ARAPP.apply_date,
975 ARAPP.applied_customer_trx_id,
976 PMTSCH.trx_number,
977 PMTSCH.trx_date,
978 PMTSCH.amount_due_original,
979 PMTSCH.amount_due_remaining,
980 TLD.TLD_ID TLD_ID,
981 TLD.LINE_amount AMOUNT
982 FROM
983 okl_bpd_tld_ar_lines_v TLD,
984 AR_PAYMENT_SCHEDULES_ALL PMTSCH,
985 AR_RECEIVABLE_APPLICATIONS_ALL ARAPP,
986 ra_Customer_trx_Lines_All cUst_trx_Lines ,
987 ar_Activity_Details arl
988 WHERE
989 TLD.TLD_ID = p_tld_id AND
990 PMTSCH.customer_trx_id = TLD.customer_trx_id AND
991 ARAPP.LINE_APPLIED > 0 AND
992 ARAPP.applied_payment_schedule_id = PMTSCH.payment_schedule_id AND
993 PMTSCH.class = 'INV' AND
994 NOT EXISTS (
995 SELECT '1'
996 FROM OKL_INVESTOR_PAYOUT_SUMMARY_B pay
997 WHERE pay.receivable_application_id = ARAPP.receivable_application_id AND
998 pay.investor_agreement_id = p_inv_agr_id AND
999 pay.investor_line_id = p_inv_agr_line_id AND
1000 pay.TLD_ID = TLD.TLD_ID) AND
1001 TLD.Customer_trx_Line_Id = cUst_trx_Lines.Customer_trx_Line_Id AND cUst_trx_Lines.Line_Type = 'LINE' AND
1002 ARAPP.Receivable_Application_Id = arl.Source_Id AND arl.Source_Table = 'RA' AND arl.Customer_trx_Line_Id = cUst_trx_Lines.Customer_trx_Line_Id AND cUst_trx_Lines.Line_Type = 'LINE'
1003 AND PMTSCH.Payment_Schedule_Id = ARAPP.Applied_Payment_Schedule_Id ;*/
1004
1005 CURSOR rcpts_csr ( p_tld_id NUMBER,
1006 p_inv_agr_id NUMBER,
1007 p_inv_agr_line_id NUMBER ) IS
1008 SELECT ARAPP.receivable_application_id,
1009 ARAPP.cash_receipt_id,
1010 ARAPP.LINE_APPLIED AMOUNT_APPLIED,
1011 ARAPP.apply_date,
1012 ARAPP.applied_customer_trx_id,
1013 PMTSCH.trx_number,
1014 PMTSCH.trx_date,
1015 PMTSCH.amount_due_original,
1016 PMTSCH.amount_due_remaining,
1017 TLD.TLD_ID TLD_ID,
1018 TLD.LINE_amount AMOUNT
1019 FROM
1020 okl_bpd_tld_ar_lines_v TLD,
1021 AR_PAYMENT_SCHEDULES_ALL PMTSCH,
1022 AR_RECEIVABLE_APPLICATIONS_ALL ARAPP
1023 WHERE
1024 TLD.TLD_ID = p_tld_id AND
1025 PMTSCH.customer_trx_id = TLD.customer_trx_id AND
1026 ARAPP.LINE_APPLIED > 0 AND
1027 ARAPP.applied_payment_schedule_id = PMTSCH.payment_schedule_id AND
1028 PMTSCH.class = 'INV' AND
1029 PMTSCH.customer_trx_id = TLD.customer_trx_id AND -- cklee 06/25/08
1030 NOT EXISTS (
1031 SELECT '1'
1032 FROM OKL_INVESTOR_PAYOUT_SUMMARY_B pay
1033 WHERE pay.receivable_application_id = ARAPP.receivable_application_id AND
1034 pay.investor_agreement_id = p_inv_agr_id AND
1035 pay.investor_line_id = p_inv_agr_line_id AND
1036 pay.TLD_ID = TLD.TLD_ID);
1037 --end:| 25-Jun-2008 cklee Fixed bug: 6784252 |
1038
1039 CURSOR get_receipt_number(p_cash_receipt_id NUMBER) IS
1040 SELECT RECEIPT_NUMBER
1041 FROM ar_cash_receipts_all
1042 WHERE cash_receipt_id = p_cash_receipt_id;
1043
1044 CURSOR cm_number ( p_app_trx_id NUMBER ) IS
1045 SELECT trx_number
1046 FROM ra_customer_trx_all
1047 WHERE customer_trx_id = p_app_trx_id;
1048
1049 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
1050 CURSOR total_bill_amt_csr ( p_tld_id NUMBER ) IS
1051 SELECT PMTSCH.amount_due_original
1052 FROM okl_bpd_tld_ar_lines_v TLD,
1053 AR_PAYMENT_SCHEDULES_ALL PMTSCH
1054 WHERE TLD.TLD_ID = p_tld_id AND
1055 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
1056 PMTSCH.customer_trx_id = TLD.customer_trx_id AND
1057 PMTSCH.class = 'INV';
1058
1059
1060 -----------------------------------------------------------------
1061 -- Record definitions
1062 -----------------------------------------------------------------
1063 i_tapv_rec Okl_Trx_Ap_Invoices_Pub.tapv_rec_type;
1064 l_init_tapv_rec Okl_Trx_Ap_Invoices_Pub.tapv_rec_type;
1065 r_tapv_rec Okl_Trx_Ap_Invoices_Pub.tapv_rec_type;
1066
1067 i_tplv_rec OKL_TXL_AP_INV_LNS_PUB.tplv_rec_type;
1068 l_init_tplv_rec OKL_TXL_AP_INV_LNS_PUB.tplv_rec_type;
1069 r_tplv_rec OKL_TXL_AP_INV_LNS_PUB.tplv_rec_type;
1070
1071 /*ankushar 11-Jan-2007
1072 added table definitions
1073 Start Changes*/
1074 i_tplv_tbl okl_tpl_pvt.tplv_tbl_type;
1075 l_init_tplv_tbl okl_tpl_pvt.tplv_tbl_type;
1076 r_tplv_tbl okl_tpl_pvt.tplv_tbl_type;
1077 /*ankushar end changes*/
1078
1079 -----------------------------------------------------------------
1080 -- Local Variables
1081 -----------------------------------------------------------------
1082 l_commit_cnt NUMBER;
1083 l_MAX_commit_cnt NUMBER := 500;
1084 l_break_khr_id NUMBER;
1085 l_break_top_line_id NUMBER;
1086 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
1087 -- l_break_lsm_id NUMBER;
1088 l_break_tld_id NUMBER;
1089 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
1090 l_break_disb_type VARCHAR2(50);
1091
1092 -----------------------------------------------------------------
1093 -- Error Processing Variables
1094 -----------------------------------------------------------------
1095 l_error_status VARCHAR2(1);
1096 l_error_message VARCHAR2(300);
1097
1098 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
1099 -- TYPE lsm_err_rec_type IS RECORD (
1100 TYPE tld_err_rec_type IS RECORD (
1101 -- lsm_id okl_cnsld_ar_strms_v.id%TYPE,
1102 tld_id OKL_TXD_AR_LN_DTLS_B.id%TYPE,
1103 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
1104 tap_id okl_trx_ap_invoices_v.id%TYPE,
1105 tpl_id okl_txl_ap_inv_lns_v.id%TYPE,
1106 -- xpi_id okl_ext_pay_invs_v.id%TYPE,
1107 -- xlp_id okl_xtl_pay_invs_v.id%TYPE,
1108 proc_sel_id okl_strm_elements.id%TYPE,
1109 bill_date DATE,
1110 contract_number okc_k_headers_b.contract_number%type,
1111 stream_name okl_strm_type_v.name%type,
1112 amount okl_strm_elements.amount%type,
1113 error_message Varchar2(2000)
1114 );
1115
1116 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
1117 -- TYPE lsm_succ_rec_type IS RECORD (
1118 -- lsm_id okl_cnsld_ar_strms_v.id%TYPE,
1119 TYPE tld_succ_rec_type IS RECORD (
1120 tld_id OKL_TXD_AR_LN_DTLS_B.id%TYPE,
1121 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
1122 tap_id okl_trx_ap_invoices_v.id%TYPE,
1123 tpl_id okl_txl_ap_inv_lns_v.id%TYPE,
1124 -- xpi_id okl_ext_pay_invs_v.id%TYPE,
1125 -- xlp_id okl_xtl_pay_invs_v.id%TYPE,
1126 proc_sel_id okl_strm_elements.id%TYPE,
1127 bill_date DATE,
1128 contract_number okc_k_headers_b.contract_number%type,
1129 stream_name okl_strm_type_v.name%type,
1130 amount okl_strm_elements.amount%type,
1131 error_message Varchar2(2000)
1132 );
1133
1134 TYPE tld_err_tbl_type IS TABLE OF tld_err_rec_type
1135 INDEX BY BINARY_INTEGER;
1136
1137 TYPE tld_succ_tbl_type IS TABLE OF tld_succ_rec_type
1138 INDEX BY BINARY_INTEGER;
1139
1140 tld_error_log_table tld_err_tbl_type;
1141 l_init_tld_table tld_err_tbl_type;
1142
1143 tld_succ_log_table tld_succ_tbl_type;
1144 l_init_succ_table tld_succ_tbl_type;
1145
1146 l_succ_tab_index NUMBER;
1147 l_tld_tab_index NUMBER;
1148 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
1149
1150 -----------------------------------------------------------------
1151 -- Process Variables And Cursors
1152 -----------------------------------------------------------------
1153 l_org_id okc_k_headers_b.authoring_org_id%TYPE;
1154 l_sob_id HR_OPERATING_UNITS.set_of_books_id%TYPE;
1155 l_try_id okl_trx_types_v.id%TYPE;
1156 l_sty_id okl_strm_type_v.id%TYPE;
1157 l_pdt_id okl_products_v.id%TYPE;
1158
1159 l_payout_date DATE;
1160
1161 l_rct_num ar_cash_receipts_all.receipt_number%TYPE;
1162 l_trx_num ra_customer_trx_all.trx_number%TYPE;
1163
1164 l_total_rcpt_amt NUMBER;
1165 l_total_bill_amt NUMBER;
1166
1167 l_update_flag VARCHAR2(1);
1168
1169 l_investor_id NUMBER;
1170 l_investor_site_id NUMBER;
1171 l_pay_terms okl_trx_ap_invoices_v.ippt_id%TYPE;
1172 l_pay_method okl_trx_ap_invoices_v.payment_method_code%TYPE;
1173 l_pay_group_code okl_trx_ap_invoices_v.pay_group_lookup_code%TYPE;
1174
1175 l_payment_basis fnd_lookups.meaning%TYPE;
1176 l_payment_event fnd_lookups.meaning%TYPE;
1177
1178 l_currency_code okl_k_headers_full_v.currency_code%TYPE;
1179 l_currency_conv_type okl_k_headers_full_v.currency_conversion_type%TYPE;
1180 l_currency_conv_rate okl_k_headers_full_v.currency_conversion_rate%TYPE;
1181 l_currency_conv_date okl_k_headers_full_v.currency_conversion_date%TYPE;
1182
1183 l_code_combination_id OKL_AE_TMPT_LNES.code_combination_id%TYPE;
1184 l_ae_line_type OKL_AE_TMPT_LNES.ae_line_type%TYPE;
1185 l_crd_code OKL_AE_TMPT_LNES.crd_code%TYPE;
1186 l_account_builder_yn OKL_AE_TMPT_LNES.account_builder_yn%TYPE;
1187 l_percentage OKL_AE_TMPT_LNES.percentage%TYPE;
1188
1189 l_okl_application_id NUMBER(3) := 540;
1190 l_document_category VARCHAR2(100):= 'OKL Lease Pay Invoices';
1191 lX_dbseqnm VARCHAR2(2000):= '';
1192 lX_dbseqid NUMBER(38):= NULL;
1193
1194 l_invoice_number okl_trx_ap_invoices_v.invoice_number%TYPE;
1195
1196 -- Create Distributions
1197 CURSOR dstrs_csr( p_pdt_id NUMBER, p_try_id NUMBER, p_sty_id NUMBER,
1198 p_cr_dr_flag VARCHAR2,
1199 p_payout_date DATE ) IS
1200 SELECT
1201 C.CODE_COMBINATION_ID,
1202 C.AE_LINE_TYPE,
1203 C.CRD_CODE,
1204 C.ACCOUNT_BUILDER_YN,
1205 C.PERCENTAGE
1206 FROM OKL_AE_TEMPLATES A,
1207 OKL_PRODUCTS_V B,
1208 OKL_AE_TMPT_LNES C
1209 WHERE A.aes_id = b.aes_id AND
1210 A.start_date <= p_payout_date AND
1211 A.memo_yn = 'N' AND
1212 (A.end_date IS NULL OR
1213 A.end_date >= p_payout_date ) AND
1214 b.id = p_pdt_id AND
1215 a.sty_id = p_sty_id AND
1216 a.try_id = p_try_id AND
1217 C.avl_id = A.id AND
1218 C.CRD_CODE = p_cr_dr_flag;
1219
1220 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
1221 CURSOR get_ref_tld_id ( p_ref_sel_id NUMBER ) IS
1222 SELECT tld_Id
1223 FROM okl_bpd_tld_ar_lines_v
1224 WHERE sel_id = p_ref_sel_id
1225 AND CUSTOMER_TRX_ID IS NOT NULL;
1226 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
1227
1228
1229 -----------------------------------------------------------------
1230 -- Get parent sty Id for an SEL_ID
1231 -----------------------------------------------------------------
1232 CURSOR get_parent_sty ( p_ref_sel_id NUMBER ) IS
1233 SELECT STM.STY_ID
1234 FROM OKL_STRM_ELEMENTS SEL,
1235 OKL_STREAMS STM
1236 WHERE SEL.ID = p_ref_sel_id
1237 AND SEL.STM_ID = STM.ID;
1238
1239 CURSOR get_parent_stake ( p_top_line_id NUMBER
1240 ,p_sty_subclass VARCHAR2 ) IS
1241 SELECT
1242 KLES.percent_stake
1243 FROM OKL_K_LINES KLES,
1244 OKC_K_LINES_B CLES,
1245 OKC_LINE_STYLES_B LSES
1246 WHERE CLES.cle_id = p_top_line_id
1247 AND KLES.stream_type_subclass = p_sty_subclass
1248 AND KLES.ID = CLES.ID
1249 AND CLES.LSE_ID = LSES.ID
1250 AND LSES.LTY_CODE = 'REVENUE_SHARE';
1251
1252
1253 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
1254 l_ref_tld_id OKL_TXD_AR_LN_DTLS_B.id%TYPE;
1255 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
1256
1257 l_percent_stake okl_k_lines.percent_stake%TYPE;
1258
1259 l_parent_sty_id okl_strm_type_v.id%TYPE;
1260
1261 l_dstr_cnt NUMBER;
1262
1263 l_idh_id NUMBER;
1264
1265 l_rcpt_cnt NUMBER;
1266
1267 l_parent_sty_subclass OKL_STRM_TYPE_V.stream_type_subclass%TYPE;
1268
1269 l_disb_strm_name OKL_STRM_TYPE_V.name%TYPE;
1270
1271 -- Start Bug 4648410
1272 l_tmpl_identify_rec Okl_Account_Dist_Pvt.TMPL_IDENTIFY_REC_TYPE;
1273 l_init_tmpl_identify_rec Okl_Account_Dist_Pvt.TMPL_IDENTIFY_REC_TYPE;
1274
1275 l_dist_info_rec Okl_Account_Dist_Pvt.dist_info_REC_TYPE;
1276 l_init_dist_info_rec Okl_Account_Dist_Pvt.dist_info_REC_TYPE;
1277
1278 l_ctxt_val_tbl okl_execute_formula_pvt.ctxt_val_tbl_type;
1279
1280 l_acc_gen_primary_key_tbl Okl_Account_Generator_Pvt.primary_key_tbl;
1281 l_template_tbl Okl_Account_Dist_Pub.AVLV_TBL_TYPE;
1282 l_amount_tbl Okl_Account_Dist_Pub.AMOUNT_TBL_TYPE;
1283
1284 CURSOR acc_dstrs_csr ( p_source_id NUMBER, p_source_table VARCHAR2 ) IS
1285 SELECT CR_DR_FLAG,
1286 CODE_COMBINATION_ID,
1287 SOURCE_ID,
1288 AMOUNT,
1289 PERCENTAGE,
1290 NVL(COMMENTS,'-99') COMMENTS
1291 FROM okl_trns_acc_dstrs
1292 WHERE source_id = p_source_id
1293 AND source_table = p_source_table
1294 --fmiao bug 5079244
1295 ORDER BY CR_DR_FLAG;
1296
1297 CURSOR inv_code_csr(p_inv_agr_id NUMBER) IS
1298 select RULE_INFORMATION1
1299 from okc_rule_groups_v rgp,
1300 okc_rules_b rul
1301 where rgp.id = rul.rgp_id
1302 and rgp.dnz_chr_id = p_inv_agr_id
1303 and rul.RULE_INFORMATION_CATEGORY = 'LASEAC'
1304 and rgp.RGD_CODE = 'LASEAC';
1305 -- End Bug 4648410
1306
1307 --fmiao for bug 4961860: check whether residual exists
1308 CURSOR check_res_in_pool(p_khr_id NUMBER) IS
1309 SELECT 'Y'
1310 FROM dual
1311 WHERE EXISTS
1312 ( SELECT 1
1313 FROM OKL_POOLS pool, okl_pool_contents_v poc, okl_strm_type_v sty
1314 WHERE pool.khr_id = p_khr_id
1315 AND pool.id = poc.pol_id
1316 AND poc.sty_id = sty.id
1317 AND sty.stream_type_purpose = 'RESIDUAL_VALUE'
1318 AND poc.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE); --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
1319
1320 l_res_in_pool VARCHAR2(1);
1321 l_evrgrn_psthrgh_flg NUMBER := 0;
1322 -- end fmiao for bug 4961860
1323
1324 --ssiruvol Bug 5000886 start
1325 TYPE l_ele_id_tbl_type IS TABLE OF okl_strm_elements.id%TYPE
1326 INDEX BY BINARY_INTEGER;
1327
1328 strm_ele_idx NUMBER;
1329 inv_ele_idx NUMBER;
1330 l_strm_ele_id_tbl l_ele_id_tbl_type;
1331 l_inv_ele_id_tbl l_ele_id_tbl_type;
1332 l_temp_ele_id_tbl l_ele_id_tbl_type;
1333 inv_ele_prtl_idx NUMBER;
1334 l_inv_prtl_id_tbl l_ele_id_tbl_type;
1335 l_billed VARCHAR2(1);
1336 --ssiruvol Bug 5000886 end
1337
1338 TYPE lsm_succ_rec_type IS RECORD (
1339 lsm_id OKL_TXD_AR_LN_DTLS_B.id%TYPE,
1340 tap_id okl_trx_ap_invoices_v.id%TYPE,
1341 tpl_id okl_txl_ap_inv_lns_v.id%TYPE,
1342 xpi_id okl_ext_pay_invs_v.id%TYPE,
1343 xlp_id okl_xtl_pay_invs_v.id%TYPE,
1344 proc_sel_id okl_strm_elements.id%TYPE,
1345 bill_date DATE,
1346 contract_number okc_k_headers_b.contract_number%type,
1347 stream_name okl_strm_type_v.name%type,
1348 amount okl_strm_elements.amount%type,
1349 error_message Varchar2(2000)
1350 );
1351
1352 TYPE lsm_succ_tbl_type IS TABLE OF lsm_succ_rec_type
1353 INDEX BY BINARY_INTEGER;
1354
1355 lsm_succ_log_table lsm_succ_tbl_type;
1356
1357 BEGIN
1358
1359 /* dbms_application_info.set_client_info('204'); */
1360 ------------------------------------------------------------
1361 -- Start processing
1362 ------------------------------------------------------------
1363 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '=====*** START PROCEDURE OKL_INVESTOR_DISBURSEMENT ***=====');
1364
1365 --dbms_output.PUT_LINE ('=====*** START PROCEDURE OKL_INVESTOR_DISBURSEMENT ***=====');
1366
1367 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1368
1369 l_return_status := OKL_API.START_ACTIVITY(
1370 p_api_name => l_api_name,
1371 p_pkg_name => g_pkg_name,
1372 p_init_msg_list => p_init_msg_list,
1373 l_api_version => l_api_version,
1374 p_api_version => p_api_version,
1375 p_api_type => '_PVT',
1376 x_return_status => l_return_status);
1377
1378 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1379 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1380 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1381 RAISE OKL_API.G_EXCEPTION_ERROR;
1382 END IF;
1383
1384 ------------------------------------
1385 -- Initialize Variables
1386 ------------------------------------
1387 l_commit_cnt := 0;
1388
1389 -----------------------------------------------------------------
1390 -- Pick up lease contracts
1391 -- in an Investor Agreement
1392 -----------------------------------------------------------------
1393
1394 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 1 ### ');
1395
1396 FOR inv_lease_k_rec IN inv_lease_k_csr ( p_investor_agreement ) LOOP
1397 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 2 ### ');
1398 --ssiruvol Bug 5000886 start
1399 strm_ele_idx :=1;
1400 inv_ele_idx :=1;
1401 l_inv_ele_id_tbl:=l_temp_ele_id_tbl;
1402 inv_ele_prtl_idx := 1;
1403 l_inv_prtl_id_tbl := l_temp_ele_id_tbl;
1404 --ssiruvol Bug 5000886 end
1405
1406
1407 -----------------------------------------------------------------
1408 -- Pick up disbursable stream elements for lease contracts
1409 -- in an Investor Agreement
1410 -----------------------------------------------------------------
1411 FOR inv_disb_rec IN inv_disb_main_csr( inv_lease_k_rec.Investor_Agreement_ID
1412 ,inv_lease_k_rec.khr_id
1413 ,p_to_date) LOOP
1414
1415 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 3 ### ');
1416 ------------------------------------
1417 -- Initialize Variables
1418 ------------------------------------
1419 l_break_khr_id := -1;
1420 l_break_top_line_id := -1;
1421
1422 -- Added by fmiao for bug 4961860
1423 l_evrgrn_psthrgh_flg := 0;
1424 IF(inv_disb_rec.STREAM_PURPOSE = 'INVESTOR_EVERGREEN_RENT_PAY') THEN
1425 OPEN check_res_in_pool(inv_lease_k_rec.Investor_Agreement_ID);
1426 FETCH check_res_in_pool INTO l_res_in_pool;
1427 CLOSE check_res_in_pool;
1428 IF(l_res_in_pool IS NULL OR l_res_in_pool <> 'Y') THEN
1429 l_evrgrn_psthrgh_flg := 1;
1430 END IF;
1431 END IF;
1432 -- end fmiao for bug 4961860
1433
1434 --ssiruvol Bug 5000886 start
1435 l_billed := 'N';
1436 if(inv_disb_rec.ref_sel_id is NULL) then
1437 l_billed :='Y';
1438 elsif (inv_disb_rec.cnsld_id is NULL) then
1439 l_billed :='N';
1440 else
1441 l_billed :='Y';
1442 end if;
1443 --ssiruvol Bug 5000886 end
1444
1445 -----------------------------------------------------------------
1446 -- Check if the parent stream element has been billed
1447 -----------------------------------------------------------------
1448 -- Added condition l_evrgrn_psthrgh_flg by bkatraga for bug 4922294
1449 -- ssiruvol Bug 5000886 start
1450 --IF ((check_sel_billed( inv_disb_rec.ref_sel_id ) = 'Y') AND l_evrgrn_psthrgh_flg = 0) THEN
1451 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 4 ### ');
1452 IF (l_billed = 'Y' AND l_evrgrn_psthrgh_flg = 0) THEN
1453 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 5 ### ');
1454 -- ssiruvol Bug 5000886 end
1455
1456
1457 -----------------------------------------------------------------
1458 -- Reset Error Status
1459 -----------------------------------------------------------------
1460 l_error_status := 'S';
1461
1462 -----------------------------------------------------------------
1463 -- Reset Error Table and counter
1464 -----------------------------------------------------------------
1465 --start:| 09-Mar-2007 cklee code fixed to refer to proper FK
1466 tld_error_log_table := l_init_tld_table;
1467 l_tld_tab_index := 0;
1468
1469 tld_succ_log_table := l_init_succ_table;
1470 l_succ_tab_index := 0;
1471
1472 --end:| 09-Mar-2007 cklee code fixed to refer to proper FK
1473
1474 -----------------------------------------------------------------
1475 -- Determine investor and share
1476 -----------------------------------------------------------------
1477 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 6 ### ');
1478 FOR share_rec IN share_csr ( inv_lease_k_rec.Investor_Agreement_ID ) LOOP
1479 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 7 ### ');
1480
1481 l_sty_id := NULL;
1482 IF inv_disb_rec.Stream_purpose = 'INVESTOR_RENT_DISB_BASIS' THEN
1483
1484 Okl_Streams_Util.get_primary_stream_type(
1485 p_khr_id => inv_lease_k_rec.Investor_Agreement_ID,
1486 p_primary_sty_purpose => 'INVESTOR_RENT_PAYABLE',
1487 x_return_status => l_return_status,
1488 x_primary_sty_id => l_sty_id );
1489 IF l_sty_id IS NULL THEN
1490 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -*- No attached INVESTOR_RENT_PAYABLE stream type found.');
1491 END IF;
1492 ELSIF inv_disb_rec.Stream_purpose = 'INVESTOR_PRINCIPAL_DISB_BASIS' THEN
1493
1494 Okl_Streams_Util.get_primary_stream_type(
1495 p_khr_id => inv_lease_k_rec.Investor_Agreement_ID,
1496 p_primary_sty_purpose => 'INVESTOR_PRINCIPAL_PAYABLE',
1497 x_return_status => l_return_status,
1498 x_primary_sty_id => l_sty_id );
1499 IF l_sty_id IS NULL THEN
1500 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -*- No attached INVESTOR_PRINCIPAL_PAYABLE stream type found.');
1501 END IF;
1502 ELSIF inv_disb_rec.Stream_purpose = 'INVESTOR_INTEREST_DISB_BASIS' THEN
1503
1504 Okl_Streams_Util.get_primary_stream_type(
1505 p_khr_id => inv_lease_k_rec.Investor_Agreement_ID,
1506 p_primary_sty_purpose => 'INVESTOR_INTEREST_PAYABLE',
1507 x_return_status => l_return_status,
1508 x_primary_sty_id => l_sty_id );
1509 IF l_sty_id IS NULL THEN
1510 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -*- No attached INVESTOR_INTEREST_PAYABLE stream type found.');
1511 END IF;
1512 ELSIF inv_disb_rec.Stream_purpose = 'INVESTOR_PPD_DISB_BASIS' THEN
1513
1514 Okl_Streams_Util.get_primary_stream_type(
1515 p_khr_id => inv_lease_k_rec.Investor_Agreement_ID,
1516 p_primary_sty_purpose => 'INVESTOR_PAYDOWN_PAYABLE',
1517 x_return_status => l_return_status,
1518 x_primary_sty_id => l_sty_id );
1519 IF l_sty_id IS NULL THEN
1520 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -*- No attached INVESTOR_PAYDOWN_PAYABLE stream type found.');
1521 END IF;
1522 ELSIF inv_disb_rec.Stream_purpose IN ('INVESTOR_LATE_FEE_PAYABLE',
1523 'INVESTOR_LATE_INTEREST_PAY',
1524 'INVESTOR_RENT_BUYBACK',
1525 'INVESTOR_PRINCIPAL_BUYBACK',
1526 'INVESTOR_INTEREST_BUYBACK',
1527 'INVESTOR_PAYDOWN_BUYBACK',
1528 'INVESTOR_RESIDUAL_BUYBACK',
1529 'INVESTOR_EVERGREEN_RENT_PAY',
1530 'INVESTOR_RESIDUAL_PAY',
1531 'INVESTOR_CNTRCT_OBLIGATION_PAY',
1532 'INVESTOR_DISB_ADJUSTMENT')
1533 THEN
1534 l_sty_id := inv_disb_rec.sty_id;
1535 ELSE
1536 l_sty_id := NULL;
1537 END IF;
1538
1539 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 8 ### ');
1540 -- Get Disbursement Stream Name
1541 l_disb_strm_name := NULL;
1542 OPEN disb_strm_csr( l_sty_id );
1543 FETCH disb_strm_csr INTO l_disb_strm_name;
1544 CLOSE disb_strm_csr;
1545 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 9 ### ');
1546 ------------------------------------------------------------------
1547 -- Work out common variables
1548 ------------------------------------------------------------------
1549 IF ( inv_lease_k_rec.Investor_Agreement_id <> l_break_khr_id OR
1550 share_rec.TOP_LINE_ID <> l_break_top_line_id ) THEN
1551
1552 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 10 ### ');
1553 -------------------------------------------------------
1554 -- Reset Break detection variables
1555 -------------------------------------------------------
1556 l_break_khr_id := inv_lease_k_rec.Investor_Agreement_id;
1557 l_break_top_line_id := share_rec.TOP_LINE_ID;
1558
1559 -----------------------------------------------
1560 -- Resolve common data on break detection
1561 -----------------------------------------------
1562
1563 -- Set Local Variables to Null
1564
1565 l_org_id := NULL;
1566 l_sob_id := NULL;
1567 l_try_id := NULL;
1568
1569 l_pdt_id := NULL;
1570 l_currency_code := NULL;
1571 l_currency_conv_type := NULL;
1572 l_currency_conv_rate := NULL;
1573 l_currency_conv_date := NULL;
1574 l_investor_id := NULL;
1575 l_investor_site_id := NULL;
1576 l_pay_terms := NULL;
1577 l_pay_method := NULL;
1578 l_pay_group_code := NULL;
1579 l_payment_basis := NULL;
1580 l_payment_event := NULL;
1581
1582 -----------------------------------------------
1583 -- Fetch Org Id into Local Variable
1584 -----------------------------------------------
1585 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 11 ### ');
1586 OPEN org_id_csr ( inv_lease_k_rec.Investor_Agreement_id );
1587 FETCH org_id_csr INTO l_org_id;
1588 CLOSE org_id_csr;
1589
1590 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 12 ### ');
1591 -----------------------------------------------
1592 -- Fetch Set Of Books into Local Variable
1593 -----------------------------------------------
1594 OPEN sob_csr (l_org_id) ;
1595 FETCH sob_csr INTO l_sob_id;
1596 CLOSE sob_csr;
1597 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 13 ### ');
1598
1599 -----------------------------------------------
1600 -- Fetch try_id into Local Variable
1601 -----------------------------------------------
1602 OPEN try_id_csr;
1603 FETCH try_id_csr INTO l_try_id;
1604 CLOSE try_id_csr;
1605 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 14 ### ');
1606
1607 -----------------------------------------------
1608 -- Select Investor attributes
1609 -----------------------------------------------
1610 OPEN vendor_attrs_csr ( inv_lease_k_rec.Investor_Agreement_id, share_rec.TOP_LINE_ID );
1611 FETCH vendor_attrs_csr INTO l_investor_id,
1612 l_investor_site_id,
1613 l_pay_terms,
1614 l_pay_method,
1615 l_pay_group_code;
1616 CLOSE vendor_attrs_csr;
1617
1618 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 15 ### ');
1619 ----------------------------------------
1620 -- Get Vendor Name from PO_Vendors_All
1621 ----------------------------------------
1622 l_investor_name := NULL;
1623 OPEN vendor_name_csr ( l_investor_id );
1624 FETCH vendor_name_csr INTO l_investor_name;
1625 CLOSE vendor_name_csr;
1626 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 16 ### ');
1627
1628 ----------------------------------------
1629 -- Get Vendor Site from po_vendor_sites
1630 ----------------------------------------
1631 l_investor_site_code := NULL;
1632 OPEN vendor_site_csr ( l_investor_site_id );
1633 FETCH vendor_site_csr INTO l_investor_site_code;
1634 CLOSE vendor_site_csr;
1635
1636 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 17 ### ');
1637 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Processing Investor Agreement: '
1638 ||inv_lease_k_rec.Investor_Agreement
1639 ||' Investor: '||l_investor_name
1640 ||' Investor Site: '||l_investor_site_code);
1641 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Lease Contract: '||inv_disb_rec.Lease_Contract);
1642 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Source Stream Name: '||inv_disb_rec.Stream_Name);
1643 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Source Stream Purpose: '||inv_disb_rec.Stream_purpose);
1644 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Disbursed Stream: '||l_disb_strm_name);
1645 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Stream Amount: '||inv_disb_rec.amount);
1646 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Stream Date: '||inv_disb_rec.bill_date);
1647
1648 -----------------------------------------------
1649 -- Select Basis and Event
1650 -----------------------------------------------
1651 l_pdt_id := inv_lease_k_rec.pdt_id;
1652 l_currency_code := inv_lease_k_rec.currency_code;
1653 l_currency_conv_type := inv_lease_k_rec.currency_conversion_type;
1654 l_currency_conv_rate := inv_lease_k_rec.currency_conversion_rate;
1655 l_currency_conv_date := inv_lease_k_rec.currency_conversion_date;
1656
1657 -- Resolve Currency Convesion Parameters for Multi-Currency
1658 IF l_currency_conv_type IS NULL THEN
1659 l_currency_conv_type := 'User';
1660 l_currency_conv_rate := 1;
1661 l_currency_conv_date := SYSDATE;
1662 END IF;
1663 -- For date
1664 IF l_currency_conv_date IS NULL THEN
1665 l_currency_conv_date := SYSDATE;
1666 END IF;
1667
1668 -- For rate -- Work out the rate in a Spot or Corporate
1669 IF (l_currency_conv_type = 'User') THEN
1670 IF l_currency_conv_rate IS NULL THEN
1671 l_currency_conv_rate := 1;
1672 END IF;
1673 END IF;
1674 IF (l_currency_conv_type = 'Spot'
1675 OR l_currency_conv_type = 'Corporate') THEN
1676 l_currency_conv_rate := NULL;
1677 END IF;
1678 END IF; -- Investor Agreement Level Break
1679
1680
1681 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 20 ### ');
1682
1683 -----------------------------------------------------------------
1684 -- Determine Basis and Event
1685 -----------------------------------------------------------------
1686 OPEN payout_attrs_csr ( inv_lease_k_rec.Investor_Agreement_ID );
1687 FETCH payout_attrs_csr INTO l_payment_basis, l_payment_event;
1688 CLOSE payout_attrs_csr;
1689
1690 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 21 ### ');
1691 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Payout Basis: '||l_payment_event);
1692 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Payout Event: '||share_rec.PAY_INVESTOR_EVENT
1693 ||' Start Date: '||share_rec.DATE_PAY_INVESTOR_START
1694 ||' Remittance Days: '||share_rec.pay_investor_remittance_days);
1695 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Payout Frequency: '||share_rec.PAY_INVESTOR_FREQUENCY);
1696
1697 l_percent_stake := NULL;
1698
1699 -- --------------------------------------------------------------
1700 -- Get the Parent Subclass corresponding to the sty purpose that
1701 -- we are dealing with
1702 -- --------------------------------------------------------------
1703 l_parent_sty_subclass := NULL;
1704
1705 IF inv_disb_rec.Stream_purpose IN ('INVESTOR_RENT_BUYBACK',
1706 'INVESTOR_RENT_DISB_BASIS')
1707 THEN
1708 l_parent_sty_subclass := 'RENT';
1709 ELSIF inv_disb_rec.Stream_purpose IN ('INVESTOR_CNTRCT_OBLIGATION_PAY',
1710 'INVESTOR_DISB_ADJUSTMENT') AND inv_disb_rec.deal_type IN ('LEASEDF','LEASEOP','LEASEST') THEN
1711 l_parent_sty_subclass := 'RENT';
1712 ELSIF inv_disb_rec.Stream_purpose IN ('INVESTOR_CNTRCT_OBLIGATION_PAY',
1713 'INVESTOR_DISB_ADJUSTMENT') AND inv_disb_rec.deal_type = 'LOAN' THEN
1714 l_parent_sty_subclass := 'LOAN_PAYMENT';
1715 ELSIF inv_disb_rec.Stream_purpose IN ('INVESTOR_PRINCIPAL_BUYBACK',
1716 'INVESTOR_INTEREST_BUYBACK',
1717 'INVESTOR_PAYDOWN_BUYBACK',
1718 'INVESTOR_PRINCIPAL_DISB_BASIS',
1719 'INVESTOR_INTEREST_DISB_BASIS',
1720 'INVESTOR_PPD_DISB_BASIS')
1721 THEN
1722 l_parent_sty_subclass := 'LOAN_PAYMENT';
1723 ELSIF inv_disb_rec.Stream_purpose IN ('INVESTOR_RESIDUAL_DISB_BASIS',
1724 'INVESTOR_RESIDUAL_BUYBACK',
1725 'INVESTOR_EVERGREEN_RENT_PAY',
1726 --pgomes fix for bug 4430377
1727 'INVESTOR_RESIDUAL_PAY')
1728 THEN
1729 l_parent_sty_subclass := 'RESIDUAL';
1730 ELSIF inv_disb_rec.Stream_purpose = 'INVESTOR_LATE_FEE_PAYABLE' THEN
1731 l_parent_sty_subclass := 'LATE_CHARGE';
1732 ELSIF inv_disb_rec.Stream_purpose = 'INVESTOR_LATE_INTEREST_PAY' THEN
1733 l_parent_sty_subclass := 'LATE_INTEREST';
1734 END IF;
1735
1736 l_percent_stake := NULL;
1737
1738 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 22 ### ');
1739 OPEN get_parent_stake ( share_rec.TOP_LINE_ID
1740 ,l_parent_sty_subclass );
1741 FETCH get_parent_stake INTO l_percent_stake;
1742 CLOSE get_parent_stake;
1743
1744 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 23 ### ');
1745
1746 IF l_percent_stake IS NULL THEN
1747 l_percent_stake := 0;
1748 END IF;
1749
1750 ----------------------------------------------------
1751 -- Process Based on Billing Or Receipt
1752 -- Residual Subclass to be treated as Billing Based
1753 ----------------------------------------------------
1754 -- -----------------------------------------
1755 -- Bug 4040202 - Treat all cases of missing
1756 -- parent sel_id as a case of billing
1757 -- -----------------------------------------
1758 IF ( (NVL( l_payment_event,'BILLING' ) = 'BILLING') OR
1759 (inv_disb_rec.subclass = 'RESIDUAL') OR
1760 (inv_disb_rec.ref_sel_id IS NULL)
1761 ) THEN
1762 ----------------------------------------------------
1763 -- Billing Based disbursement
1764 ----------------------------------------------------
1765
1766 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 24 ### ');
1767 IF ( NVL(share_rec.PAY_INVESTOR_EVENT,'SCHEDULE' ) = 'SCHEDULE' ) THEN
1768 l_payout_date := get_next_pymt_date (
1769 share_rec.DATE_PAY_INVESTOR_START,
1770 share_rec.PAY_INVESTOR_FREQUENCY,
1771 inv_disb_rec.bill_date );
1772 ELSE
1773 l_payout_date := trunc(SYSDATE) + share_rec.pay_investor_remittance_days;
1774 END IF;
1775
1776
1777 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Payout Date: '||l_payout_date);
1778 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Percent Stake: '||l_percent_stake);
1779
1780 -- *********************************************** --
1781 -- Insert into TAP TPL
1782 -- *********************************************** --
1783
1784 -- Null Out record definitions
1785 i_tapv_rec := l_init_tapv_rec;
1786 r_tapv_rec := l_init_tapv_rec;
1787
1788 i_tplv_rec := l_init_tplv_rec;
1789 r_tplv_rec := l_init_tplv_rec;
1790
1791 /* ankushar 12-JAN-2007
1792 Null out table definitions
1793 start changes */
1794 i_tplv_tbl := l_init_tplv_tbl;
1795 r_tplv_tbl := l_init_tplv_tbl;
1796 /* ankushar end changes */
1797
1798 -- Increment Commit counter
1799 l_commit_cnt := l_commit_cnt + 1;
1800
1801 -- Reset Error Message
1802 l_error_message := NULL;
1803
1804 -- Get Next sequence
1805 l_invoice_number := NULL;
1806
1807 -- Create Investor Disbursement Header
1808 i_tapv_rec.org_id := l_org_id;
1809 i_tapv_rec.set_of_books_id := l_sob_id;
1810 i_tapv_rec.invoice_number := l_invoice_number;
1811 i_tapv_rec.vendor_invoice_number := l_invoice_number;
1812 i_tapv_rec.try_id := l_try_id;
1813 i_tapv_rec.invoice_type := 'STANDARD';
1814
1815 i_tapv_rec.vendor_id := l_investor_id;
1816 i_tapv_rec.ipvs_id := l_investor_site_id;
1817 i_tapv_rec.khr_id := inv_lease_k_rec.Investor_Agreement_id;
1818 i_tapv_rec.currency_code := l_currency_code;
1819 i_tapv_rec.CURRENCY_CONVERSION_TYPE := l_currency_conv_type;
1820 i_tapv_rec.CURRENCY_CONVERSION_RATE := l_currency_conv_rate;
1821 i_tapv_rec.CURRENCY_CONVERSION_DATE := l_currency_conv_date;
1822
1823 i_tapv_rec.payment_method_code := l_pay_method;
1824 i_tapv_rec.date_entered := l_payout_date;
1825 i_tapv_rec.date_invoiced := l_payout_date;
1826 i_tapv_rec.invoice_category_code := NULL;
1827 i_tapv_rec.ippt_id := l_pay_terms;
1828 i_tapv_rec.DATE_GL := l_payout_date;
1829 i_tapv_rec.Pay_Group_lookup_code := l_pay_group_code;
1830 i_tapv_rec.trx_status_code := 'ENTERED';--'PROCESSED'; --cklee 5/24/07
1831 i_tapv_rec.nettable_yn := 'N';
1832 -- 02-NOV-2006 ANSETHUR R12B - Legal Entity
1833 i_tapv_rec.legal_entity_id := inv_lease_k_rec.legal_entity_id;
1834 --------------------------------------------------------------
1835 -- Work out the amount
1836 --------------------------------------------------------------
1837 i_tapv_rec.amount := (inv_disb_rec.amount*l_percent_stake/100 );
1838
1839 i_tapv_rec.amount := okl_accounting_util.cross_currency_round_amount
1840 (p_amount => i_tapv_rec.amount
1841 ,p_currency_code => inv_disb_rec.currency_code);
1842
1843 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Amount Payable: '||i_tapv_rec.amount);
1844
1845 -- Create Investor Disbursement Lines
1846 i_tplv_rec.tap_id := r_tapv_rec.id;
1847 i_tplv_rec.amount := i_tapv_rec.amount;
1848 -- Start Bug 4648410
1849 i_tplv_rec.sty_id := l_sty_id; --inv_disb_rec.sty_id;
1850 -- End Bug 4648410
1851 i_tplv_rec.inv_distr_line_code := 'INVESTOR';
1852 i_tplv_rec.line_number := 1;
1853 i_tplv_rec.org_id := l_org_id;
1854 i_tplv_rec.disbursement_basis_code := 'BILL_DATE';
1855
1856 i_tplv_rec.sel_id := inv_disb_rec.sel_id;
1857 --inv_disb_rec.pay_investor_event;
1858 --start:| 09-Mar-2007 cklee Change khr_id from header to line |
1859 i_tplv_rec.khr_id := inv_lease_k_rec.Investor_Agreement_id;
1860 --end:| 09-Mar-2007 cklee Change khr_id from header to line |
1861
1862
1863 /* ankushar 17-JAN-2007
1864 Call to the common Disbursement API
1865 start changes */
1866
1867 -- Add tpl_rec to table
1868 i_tplv_tbl(1) := i_tplv_rec;
1869
1870 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 25 ### ');
1871 --Call the commong disbursement API to create transactions
1872 Okl_Create_Disb_Trans_Pvt.create_disb_trx(
1873 p_api_version => p_api_version
1874 ,p_init_msg_list => p_init_msg_list
1875 ,x_return_status => x_return_status
1876 ,x_msg_count => x_msg_count
1877 ,x_msg_data => x_msg_data
1878 ,p_tapv_rec => i_tapv_rec
1879 ,p_tplv_tbl => i_tplv_tbl
1880 ,x_tapv_rec => r_tapv_rec
1881 ,x_tplv_tbl => r_tplv_tbl);
1882
1883 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 26 ### ');
1884 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1885 l_error_status := 'E';
1886 l_error_message := l_error_message||'Error creating Investor Disbursement Transactions. ';
1887 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Error creating Investor Disbursement Transactions.');
1888 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1889 l_error_status := 'E';
1890 l_error_message := l_error_message||'Error creating Investor Disbursement Transactions ';
1891 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Error creating Investor Disbursement Transactions.');
1892 ELSIF (x_return_status = 'S') THEN
1893 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Created Investor Disbursement Transactions.');
1894 ELSE
1895 NULL;
1896 END IF;
1897
1898 /*ankushar end changes */
1899
1900 -- *********************************************** --
1901 -- Build an error Table
1902 -- *********************************************** --
1903 --start:| 09-Mar-2007 cklee Change khr_id from header to line |
1904
1905 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 26.1 ### ');
1906 IF l_error_status = 'E' THEN
1907 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 26.2 ### ');
1908 l_tld_tab_index := l_tld_tab_index + 1;
1909
1910 tld_error_log_table(l_tld_tab_index).tap_id
1911 := r_tapv_rec.id;
1912 tld_error_log_table(l_tld_tab_index).tpl_id
1913 := r_tplv_rec.id;
1914 tld_error_log_table(l_tld_tab_index).error_message
1915 := l_error_message;
1916 ELSE
1917 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 26.3 ### ');
1918 l_succ_tab_index := l_succ_tab_index + 1;
1919
1920 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 26.3.1 ### ' || to_char(r_tapv_rec.id));
1921
1922 tld_succ_log_table( l_succ_tab_index ).tap_id
1923 := r_tapv_rec.id;
1924 tld_succ_log_table( l_succ_tab_index ).tpl_id
1925 := r_tplv_rec.id;
1926 tld_succ_log_table( l_succ_tab_index ).proc_sel_id
1927 := inv_disb_rec.sel_id;
1928 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 26.4 ### ');
1929
1930 END IF;
1931 --end:| 09-Mar-2007 cklee Change khr_id from header to line |
1932
1933 ELSE -- If the basis is receipt
1934
1935 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 27 ### ');
1936 --dbms_output.PUT_LINE (' Receipt Based Processing ');
1937 --start:| 09-Mar-2007 cklee Change khr_id from header to line |
1938 l_ref_tld_id := NULL;
1939 --end:| 09-Mar-2007 cklee Change khr_id from header to line |
1940
1941 --start:| 09-Mar-2007 cklee Change khr_id from header to line |
1942 --ssiruvol Bug 5000886 start
1943 OPEN get_ref_tld_id ( inv_disb_rec.ref_sel_id );
1944 FETCH get_ref_tld_id INTO l_ref_tld_id;
1945 CLOSE get_ref_tld_id;
1946 --ssiruvol Bug 5000886 end
1947 --end:| 09-Mar-2007 cklee Change khr_id from header to line |
1948
1949 ----------------------------------------------------
1950 -- Receipt Based disbursement
1951 ----------------------------------------------------
1952 l_rcpt_cnt := 0;
1953 --start:| 09-Mar-2007 cklee Change khr_id from header to line |
1954 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 28 ### ');
1955 FOR rcpts_rec IN rcpts_csr (l_ref_tld_id,
1956 --end:| 09-Mar-2007 cklee Change khr_id from header to line |
1957 inv_lease_k_rec.Investor_Agreement_id,
1958 share_rec.TOP_LINE_ID
1959 ) LOOP
1960
1961 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 29 ### ');
1962 l_rcpt_cnt := l_rcpt_cnt + 1;
1963
1964
1965
1966 l_payout_date := NULL;
1967
1968 IF ( NVL(share_rec.PAY_INVESTOR_EVENT,'SCHEDULE' ) = 'SCHEDULE' ) THEN
1969 l_payout_date := get_next_pymt_date (
1970 share_rec.DATE_PAY_INVESTOR_START,
1971 share_rec.PAY_INVESTOR_FREQUENCY,
1972 rcpts_rec.apply_date );
1973 ELSE
1974 -------------------------------------------------------
1975 -- Should this be apply date instead of sysdate
1976 -- Check with PM
1977 -------------------------------------------------------
1978 l_payout_date := trunc(SYSDATE) + share_rec.pay_investor_remittance_days;
1979 END IF;
1980
1981 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Payout Date: '||l_payout_date);
1982 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Percent Stake: '||l_percent_stake);
1983
1984 --dbms_output.PUT_LINE (' Payout Date: '||l_payout_date);
1985 --dbms_output.PUT_LINE (' Percent Stake: '||l_percent_stake);
1986
1987
1988 -- *********************************************** --
1989 -- Insert into TAP TPL
1990 -- *********************************************** --
1991
1992 -- Null Out record definitions
1993 i_tapv_rec := l_init_tapv_rec;
1994 r_tapv_rec := l_init_tapv_rec;
1995
1996 i_tplv_rec := l_init_tplv_rec;
1997 r_tplv_rec := l_init_tplv_rec;
1998
1999 /* ankushar 17-JAN-2007
2000 Null out table definitions
2001 start changes */
2002 i_tplv_tbl := l_init_tplv_tbl;
2003 r_tplv_tbl := l_init_tplv_tbl;
2004 /* ankushar end changes */
2005
2006 -- Increment Commit counter
2007 l_commit_cnt := l_commit_cnt + 1;
2008
2009 -- Reset Error Message
2010 l_error_message := NULL;
2011
2012 -- Get Next sequence
2013 l_invoice_number := NULL;
2014
2015 -- Create Investor Disbursement Header
2016 i_tapv_rec.org_id := l_org_id;
2017 i_tapv_rec.set_of_books_id := l_sob_id;
2018 i_tapv_rec.invoice_number := l_invoice_number;
2019 i_tapv_rec.vendor_invoice_number := l_invoice_number;
2020 i_tapv_rec.try_id := l_try_id;
2021 i_tapv_rec.invoice_type := 'STANDARD';
2022
2023 i_tapv_rec.vendor_id := l_investor_id;
2024 i_tapv_rec.ipvs_id := l_investor_site_id;
2025 i_tapv_rec.khr_id := inv_lease_k_rec.Investor_Agreement_id;
2026 i_tapv_rec.currency_code := l_currency_code;
2027 i_tapv_rec.CURRENCY_CONVERSION_TYPE := l_currency_conv_type;
2028 i_tapv_rec.CURRENCY_CONVERSION_RATE := l_currency_conv_rate;
2029 i_tapv_rec.CURRENCY_CONVERSION_DATE := l_currency_conv_date;
2030
2031 i_tapv_rec.payment_method_code := l_pay_method;
2032 i_tapv_rec.date_entered := l_payout_date;
2033 i_tapv_rec.date_invoiced := l_payout_date;
2034 i_tapv_rec.invoice_category_code := NULL;
2035 i_tapv_rec.ippt_id := l_pay_terms;
2036 i_tapv_rec.DATE_GL := l_payout_date;
2037 i_tapv_rec.Pay_Group_lookup_code := l_pay_group_code;
2038 i_tapv_rec.trx_status_code := 'ENTERED';--'PROCESSED'; --cklee 5/24/07
2039 i_tapv_rec.nettable_yn := 'N';
2040 -- 02-NOV-2006 ANSETHUR R12B - Legal Entity
2041 i_tapv_rec.legal_entity_id := inv_lease_k_rec.legal_entity_id;
2042 -----------------------------------------------------------
2043 -- Work out the amount
2044 -----------------------------------------------------------
2045 i_tapv_rec.amount
2046 := (inv_disb_rec.amount*rcpts_rec.AMOUNT_APPLIED/rcpts_rec.amount)*(l_percent_stake/100 );
2047
2048 i_tapv_rec.amount := okl_accounting_util.cross_currency_round_amount
2049 (p_amount => i_tapv_rec.amount
2050 ,p_currency_code => inv_disb_rec.currency_code);
2051
2052 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Amount Payable: '||i_tapv_rec.amount);
2053
2054 --dbms_output.PUT_LINE (' Amount Payable: '||i_tapv_rec.amount);
2055
2056 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 30 ### ');
2057 -- Create Investor Disbursement Lines
2058 i_tplv_rec.tap_id := r_tapv_rec.id;
2059 i_tplv_rec.amount := i_tapv_rec.amount;
2060 i_tplv_rec.sty_id := l_sty_id; --inv_disb_rec.sty_id;
2061 i_tplv_rec.inv_distr_line_code := 'INVESTOR';
2062 i_tplv_rec.line_number := 1;
2063 i_tplv_rec.org_id := l_org_id;
2064 i_tplv_rec.disbursement_basis_code := 'CASH_RECEIPT';
2065 i_tplv_rec.sel_id := inv_disb_rec.sel_id;
2066 --start:| 09-Mar-2007 cklee Change khr_id from header to line |
2067 i_tplv_rec.khr_id := inv_lease_k_rec.Investor_Agreement_id;
2068 --end:| 09-Mar-2007 cklee Change khr_id from header to line |
2069 --inv_disb_rec.pay_investor_event;
2070
2071 /* ankushar 17-JAN-2007
2072 Call the common Disbursement API for creating disbursemant transactions
2073 start changes */
2074
2075 -- Add tpl_rec to table
2076 i_tplv_tbl(1) := i_tplv_rec;
2077
2078 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 31 ### ');
2079 --Call the commong disbursement API to create transactions
2080 Okl_Create_Disb_Trans_Pvt.create_disb_trx(
2081 p_api_version => p_api_version
2082 ,p_init_msg_list => p_init_msg_list
2083 ,x_return_status => x_return_status
2084 ,x_msg_count => x_msg_count
2085 ,x_msg_data => x_msg_data
2086 ,p_tapv_rec => i_tapv_rec
2087 ,p_tplv_tbl => i_tplv_tbl
2088 ,x_tapv_rec => r_tapv_rec
2089 ,x_tplv_tbl => r_tplv_tbl);
2090
2091 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 32 ### ');
2092 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2093 l_error_status := 'E';
2094 l_error_message := l_error_message||'Error creating Investor Disbursement Transactions. ';
2095 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Error creating Investor Disbursement Transactions.');
2096 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2097 l_error_status := 'E';
2098 l_error_message := l_error_message||'Error creating Investor Disbursement Transactions ';
2099 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Error creating Investor Disbursement Transactions.');
2100 ELSIF (x_return_status = 'S') THEN
2101 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Created Investor Disbursement Transactions.');
2102 ELSE
2103 NULL;
2104 END IF;
2105 /* ankushar end changes */
2106
2107 -- *********************************************** --
2108 -- Build an error Table
2109 -- *********************************************** --
2110 --start:| 09-Mar-2007 cklee Change khr_id from header to line |
2111
2112 IF l_error_status = 'E' THEN
2113 l_tld_tab_index := l_tld_tab_index + 1;
2114 tld_error_log_table(l_tld_tab_index).tap_id
2115 := r_tapv_rec.id;
2116 --tld_error_log_table(l_tld_tab_index).tpl_id
2117 -- := r_tplv_rec.id;
2118 tld_error_log_table(l_tld_tab_index).error_message
2119 := l_error_message;
2120 ELSE
2121 l_succ_tab_index := l_succ_tab_index + 1;
2122
2123 tld_succ_log_table( l_succ_tab_index ).tap_id
2124 := r_tapv_rec.id;
2125 tld_succ_log_table( l_succ_tab_index ).tpl_id
2126 := r_tplv_rec.id;
2127 tld_succ_log_table( l_succ_tab_index ).proc_sel_id
2128 := inv_disb_rec.sel_id;
2129 END IF;
2130 --end:| 09-Mar-2007 cklee Change khr_id from header to line |
2131
2132 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 33 ### ');
2133
2134 IF l_error_status = 'S' THEN
2135 ------------------------------
2136 -- Populate PK from sequence
2137 ------------------------------
2138 l_idh_id := NULL;
2139 l_idh_id := get_seq_id;
2140 INSERT INTO okl_investor_payout_summary_b
2141 ( ID,
2142 OBJECT_VERSION_NUMBER,
2143 CREATED_BY,
2144 CREATION_DATE,
2145 LAST_UPDATED_BY,
2146 LAST_UPDATE_DATE,
2147 LAST_UPDATE_LOGIN,
2148 INVESTOR_AGREEMENT_ID,
2149 INVESTOR_LINE_ID,
2150 TLD_ID,
2151 RECEIVABLE_APPLICATION_ID,
2152 ORG_ID
2153 )
2154 VALUES
2155 (
2156 l_idh_id,
2157 1,
2158 Fnd_Global.USER_ID,
2159 SYSDATE,
2160 Fnd_Global.USER_ID,
2161 SYSDATE,
2162 Fnd_Global.LOGIN_ID,
2163 inv_lease_k_rec.Investor_Agreement_id,
2164 share_rec.TOP_LINE_ID,
2165 rcpts_rec.tld_id,
2166 rcpts_rec.receivable_application_id,
2167 l_org_id
2168 );
2169
2170
2171 END IF;
2172
2173 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 34 ### ');
2174 END LOOP; -- For each receipt undisbursed
2175
2176 -- --------------------------------
2177 -- Check If receipts processed
2178 -- --------------------------------
2179 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 35 ### ');
2180 IF l_rcpt_cnt <= 0 THEN
2181 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' **** No Receipts to Process ***');
2182 END IF;
2183
2184 END IF;
2185
2186 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 36 ### ');
2187 END LOOP; -- Determine Investor Share
2188
2189 --dbms_output.put_line(' HERE 007: NEVER BEEN HERE ');
2190
2191
2192 -----------------------------------------------------
2193 -- Error Processing
2194 -----------------------------------------------------
2195 -- if ret_status = error then
2196 -- flag created txns in TAP and XPI as error
2197 -- else
2198 -- if event is Billing then
2199 -- update sel date billed with sysdate
2200 -- update lsm inv disb status to processed
2201 -- else
2202 -- if invoice has been paid in full
2203 -- update sel date billed with sysdate
2204 -- update lsm inv disb status to processed
2205 -- else
2206 -- update lsm inv disb status to partial
2207 -----------------------------------------------------
2208
2209 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 37 ### ');
2210 IF l_error_status = 'E' THEN
2211 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 38 ### ');
2212 --dbms_output.put_line(' HERE 99: '||SQLERRM);
2213 --start:| 09-Mar-2007 cklee Change khr_id from header to line |
2214 -- IF lsm_error_log_table.COUNT > 0 THEN
2215 IF tld_error_log_table.COUNT > 0 THEN
2216 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 39 ### ');
2217 -- FOR i IN lsm_error_log_table.FIRST..lsm_error_log_table.LAST LOOP
2218 FOR i IN tld_error_log_table.FIRST..tld_error_log_table.LAST LOOP
2219 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 40 ### ');
2220 UPDATE okl_trx_ap_invoices_b
2221 SET trx_status_code = 'ERROR'
2222 WHERE Id = tld_error_log_table(i).tap_id;
2223
2224 END LOOP;
2225 --end:| 09-Mar-2007 cklee Change khr_id from header to line |
2226 -----------------------------------------------------------------
2227 -- Reset Error Table and counter
2228 -----------------------------------------------------------------
2229 --start:| 09-Mar-2007 cklee Change khr_id from header to line |
2230 tld_error_log_table := l_init_tld_table;
2231 l_tld_tab_index := 0;
2232 --end:| 09-Mar-2007 cklee Change khr_id from header to line |
2233
2234 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 41 ### ');
2235 END IF;
2236 --dbms_output.put_line(' HERE 991: '||SQLERRM);
2237 ELSE
2238 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 42 ### ');
2239 --dbms_output.put_line(' HERE 100: '||SQLERRM||lsm_succ_log_table.COUNT);
2240 --start:| 09-Mar-2007 cklee Change khr_id from header to line |
2241 IF tld_succ_log_table.COUNT > 0 THEN
2242 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 43 ### ');
2243 FOR i IN tld_succ_log_table.FIRST..tld_succ_log_table.LAST LOOP
2244 --end:| 09-Mar-2007 cklee Change khr_id from header to line |
2245 IF ( NVL( l_payment_event,'BILLING' ) = 'BILLING' ) OR
2246 --pgomes fix for bug 4430377
2247 (inv_disb_rec.subclass = 'RESIDUAL') OR
2248 (inv_disb_rec.ref_sel_id IS NULL) THEN
2249
2250 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 43.1 ### ');
2251 --ssiruvol Bug 5000886 start
2252 UPDATE okl_strm_elements
2253 SET date_billed = SYSDATE
2254 WHERE id = tld_succ_log_table(i).proc_sel_id;
2255
2256 UPDATE OKL_TXD_AR_LN_DTLS_B
2257 SET investor_disb_status = 'PROCESSED'
2258 WHERE sel_id = inv_disb_rec.ref_sel_id;
2259
2260 l_strm_ele_id_tbl(strm_ele_idx):=tld_succ_log_table(i).proc_sel_id;
2261 l_inv_ele_id_tbl(inv_ele_idx) :=inv_disb_rec.ref_sel_id;
2262 strm_ele_idx:=strm_ele_idx+1;
2263 inv_ele_idx:=inv_ele_idx+1;
2264 --ssiruvol Bug 5000886 end;
2265
2266 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 43.2 ### ');
2267 ELSE
2268 --ssiruvol Bug 5000886 start
2269 -- IF (check_rcpts( inv_disb_rec.ref_sel_id ) = 'FULL') THEN
2270 IF (check_rcpts( inv_disb_rec.cnsld_id ) = 'FULL') THEN
2271 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 43.3 ### ');
2272
2273 UPDATE okl_strm_elements
2274 SET date_billed = SYSDATE
2275 WHERE id = tld_succ_log_table(i).proc_sel_id;
2276
2277 UPDATE OKL_TXD_AR_LN_DTLS_B
2278 SET investor_disb_status = 'PROCESSED'
2279 WHERE sel_id = inv_disb_rec.ref_sel_id;
2280
2281 l_strm_ele_id_tbl(strm_ele_idx):=tld_succ_log_table(i).proc_sel_id;
2282 l_inv_ele_id_tbl(inv_ele_idx) :=inv_disb_rec.ref_sel_id;
2283 strm_ele_idx:=strm_ele_idx+1;
2284 inv_ele_idx:=inv_ele_idx+1;
2285
2286 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 43.4 ### ');
2287 --ELSIF (check_rcpts( inv_disb_rec.ref_sel_id ) = 'PARTIAL') THEN
2288 ELSIF (check_rcpts( inv_disb_rec.cnsld_id ) = 'PARTIAL') THEN
2289
2290 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ### 43.5 ### ');
2291
2292 l_inv_prtl_id_tbl(inv_ele_prtl_idx) := inv_disb_rec.ref_sel_id;
2293 inv_ele_prtl_idx := inv_ele_prtl_idx + 1;
2294 --ssiruvol Bug 5000886 end
2295
2296
2297 ELSE
2298 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '** WARNING: Unhandled Condition');
2299 --dbms_output.put_line('** WARNING: Unhandled Condition');
2300 END IF;
2301
2302 END IF;
2303 END LOOP; -- Loop thru success table
2304
2305 --start:| 09-Mar-2007 cklee Change khr_id from header to line |
2306 -- lsm_succ_log_table := l_init_succ_table;
2307 tld_succ_log_table := l_init_succ_table;
2308 --end:| 09-Mar-2007 cklee Change khr_id from header to line |
2309 l_succ_tab_index := 0;
2310
2311 END IF;
2312 --dbms_output.put_line(' HERE 1001: '||SQLERRM);
2313 END IF; -- Check error status Code
2314
2315 END IF; -- Check if Billed
2316
2317 IF l_commit_cnt > l_MAX_commit_cnt THEN
2318 --ssiruvol Bug 5000886 start
2319 IF l_strm_ele_id_tbl.COUNT > 0 THEN
2320 FORALL indx in l_strm_ele_id_tbl.FIRST .. l_strm_ele_id_tbl.LAST
2321 UPDATE okl_strm_elements
2322 SET date_billed = SYSDATE--l_date_billed_tbl(indx);
2323 WHERE id = l_strm_ele_id_tbl(indx);
2324 l_strm_ele_id_tbl:=l_temp_ele_id_tbl;
2325 strm_ele_idx:=1;
2326 END IF;
2327
2328 IF l_inv_ele_id_tbl.COUNT > 0 THEN
2329 FORALL indx in l_inv_ele_id_tbl.FIRST .. l_inv_ele_id_tbl.LAST
2330 UPDATE OKL_TXD_AR_LN_DTLS_B
2331 SET investor_disb_status = 'PROCESSED'
2332 WHERE sel_id = l_inv_ele_id_tbl(indx)
2333 and khr_id =inv_lease_k_rec.khr_id;
2334 l_inv_ele_id_tbl:=l_temp_ele_id_tbl;
2335 inv_ele_idx:=1;
2336 END IF;
2337
2338 IF l_inv_prtl_id_tbl.COUNT > 0 THEN
2339 FORALL indx in l_inv_prtl_id_tbl.FIRST .. l_inv_prtl_id_tbl.LAST
2340 UPDATE OKL_TXD_AR_LN_DTLS_B
2341 SET investor_disb_status = 'PARTIAL'
2342 WHERE sel_id = l_inv_prtl_id_tbl(indx)
2343 and khr_id =inv_lease_k_rec.khr_id;
2344 l_inv_prtl_id_tbl := l_temp_ele_id_tbl;
2345 inv_ele_prtl_idx := 1;
2346 END IF;
2347 --ssiruvol Bug 5000886 end
2348 l_commit_cnt := 0;
2349 COMMIT;
2350 END IF;
2351
2352 END LOOP; -- Pick Up Disbursable stream elements
2353
2354 --ssiruvol Bug 5000886 start
2355 IF l_strm_ele_id_tbl.COUNT > 0 THEN
2356 FORALL indx in l_strm_ele_id_tbl.FIRST .. l_strm_ele_id_tbl.LAST
2357 UPDATE okl_strm_elements
2358 SET date_billed = SYSDATE
2359 WHERE id = l_strm_ele_id_tbl(indx);
2360 l_strm_ele_id_tbl:=l_temp_ele_id_tbl;
2361 strm_ele_idx:=1;
2362 END IF;
2363
2364 IF l_inv_ele_id_tbl.COUNT > 0 THEN
2365 FORALL indx in l_inv_ele_id_tbl.FIRST .. l_inv_ele_id_tbl.LAST
2366 UPDATE OKL_TXD_AR_LN_DTLS_B
2367 SET investor_disb_status = 'PROCESSED'
2368 WHERE sel_id = l_inv_ele_id_tbl(indx)
2369 and khr_id = inv_lease_k_rec.khr_id;
2370 l_inv_ele_id_tbl:=l_temp_ele_id_tbl;
2371 inv_ele_idx:=1;
2372 END IF;
2373
2374 IF l_inv_prtl_id_tbl.COUNT > 0 THEN
2375 FORALL indx in l_inv_prtl_id_tbl.FIRST .. l_inv_prtl_id_tbl.LAST
2376 UPDATE OKL_TXD_AR_LN_DTLS_B
2377 SET investor_disb_status = 'PARTIAL'
2378 WHERE sel_id = l_inv_prtl_id_tbl(indx)
2379 and khr_id =inv_lease_k_rec.khr_id;
2380 l_inv_prtl_id_tbl := l_temp_ele_id_tbl;
2381 inv_ele_prtl_idx := 1;
2382 END IF;
2383 --ssiruvol Bug 5000886 end
2384
2385 END LOOP; -- Pick up LK for an INvestor agreement
2386
2387 -- gboomina added for Bug 6788005 - Start
2388 -----------------------------------------------------------------
2389 -- Create disbursement for fees defined in the Investor Agreement
2390 -----------------------------------------------------------------
2391 okl_investor_fee_disb( p_api_version => p_api_version,
2392 p_init_msg_list => p_init_msg_list,
2393 x_return_status => l_return_status,
2394 x_msg_count => x_msg_count,
2395 x_msg_data => x_msg_data,
2396 p_investor_agreement => p_investor_agreement,
2397 p_to_date => p_to_date);
2398
2399 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2400 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2401 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2402 RAISE OKL_API.G_EXCEPTION_ERROR;
2403 END IF;
2404 -- gboomina added for Bug 6788005 - End
2405
2406 ------------------------------------------------------------
2407 -- End processing
2408 ------------------------------------------------------------
2409
2410 Okl_Api.END_ACTIVITY (
2411 x_msg_count => x_msg_count,
2412 x_msg_data => x_msg_data);
2413
2414
2415
2416 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '=====*** EXITING PROCEDURE OKL_INVESTOR_DISBURSEMENT ***=====');
2417
2418 --dbms_output.put_line('=====*** EXITING PROCEDURE OKL_INVESTOR_DISBURSEMENT ***=====');
2419 EXCEPTION
2420 ------------------------------------------------------------
2421 -- Exception handling
2422 ------------------------------------------------------------
2423
2424 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2425 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' ERROR 1: '||SQLERRM);
2426 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2427 p_api_name => l_api_name,
2428 p_pkg_name => G_PKG_NAME,
2429 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2430 x_msg_count => x_msg_count,
2431 x_msg_data => x_msg_data,
2432 p_api_type => '_PVT');
2433
2434 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2435 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' ERROR 2: '||SQLERRM);
2436 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2437 p_api_name => l_api_name,
2438 p_pkg_name => G_PKG_NAME,
2439 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2440 x_msg_count => x_msg_count,
2441 x_msg_data => x_msg_data,
2442 p_api_type => '_PVT');
2443
2444 WHEN OTHERS THEN
2445 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' ERROR 3: '||SQLERRM);
2446 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2447 p_api_name => l_api_name,
2448 p_pkg_name => G_PKG_NAME,
2449 p_exc_name => 'OTHERS',
2450 x_msg_count => x_msg_count,
2451 x_msg_data => x_msg_data,
2452 p_api_type => '_PVT');
2453
2454
2455 END OKL_INVESTOR_DISBURSEMENT;
2456
2457
2458 END okl_investor_invoice_disb_pvt;