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