[Home] [Help]
PACKAGE BODY: APPS.OKL_PAY_INVOICES_DISB_PVT
Source
1 PACKAGE BODY OKL_PAY_INVOICES_DISB_PVT AS
2 /* $Header: OKLRPIDB.pls 120.27.12020000.2 2012/08/07 12:18:33 bkatraga 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 -- The pulic procedure auto_disbursement calls in a loop of receivable
11 -- invoices cursors to private procedure invoice_disbursement which
12 -- calls private procedure invoice_insert with or without loop for
13 -- non-syndication or syndication type association
14 -- For non evergreen passthrough percentage is applied.
15 -- For evergreen evergreen fees and passthrough percentage is applied.
16 -- The passed amount is disbursed to one or shared among investors.
17 -- The recivables headers transaction status get updated as PROCESSED_PAY_S
18 -- or PROCESSED_PAY_E even if one line in cursor fails.
19 --30/May/02 Print Proccessed consolidate invoice numbers.
20 -----------------------------------------------------------------
21
22 null_disb_rec disb_rec_type;
23
24 ----------------------------------------------------------------------
25
26 PROCEDURE print_line (p_message IN VARCHAR2) IS
27 BEGIN
28 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, p_message);
29 EXCEPTION
30 WHEN OTHERS THEN
31 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '** EXCEPTION IN print_line: '||SQLERRM);
32 END print_line;
33
34 ---------------------------------------------------------------------------
35
36 FUNCTION get_seq_id RETURN NUMBER IS
37 BEGIN
38 RETURN(Okc_P_Util.raw_to_number(sys_guid()));
39 END get_seq_id;
40
41 ----------------------------------------------------------------------
42 --sechawla 21-jan-2011 10282607 : added p_customer_trx_line_id function parameter
43 FUNCTION receipt_amount(p_customer_trx_id IN NUMBER,p_customer_trx_line_id IN NUMBER) RETURN NUMBER IS
44
45 l_receipt_amount NUMBER := 0;
46
47
48 /* --sechawla 10282607 21-jan-2011
49 CURSOR l_receipt_amount_csr(cp_customer_trx_id NUMBER) IS
50 SELECT NVL(SUM(LINE_APPLIED), 0)
51 FROM AR_RECEIVABLE_APPLICATIONS_ALL
52 WHERE APPLIED_CUSTOMER_TRX_ID = cp_customer_trx_id
53 AND APPLICATION_TYPE = 'CASH'
54 AND STATUS = 'APP';
55 */
56
57 --Bug# 12385206
58 /*
59 --sechawla 21-jan-2011 10282607
60 --Use view okl_receipt_applications_uv, which can be queried to get the line level amount
61 CURSOR l_receipt_amount_csr(cp_customer_trx_id NUMBER,cp_customer_trx_line_id NUMBER) IS
62 SELECT NVL(SUM(LINE_APPLIED), 0)
63 FROM okl_receipt_applications_uv
64 WHERE CUSTOMER_TRX_ID = cp_customer_trx_id
65 AND customer_trx_line_id = cp_customer_trx_line_id;
66 */
67
68 --Bug# 12385206
69 -- Modified cursor to query from ar_distributions_all to fetch receipt application
70 -- amounts where receipt has been applied either in OKL or in AR
71 CURSOR l_receipt_amount_csr(cp_customer_trx_id NUMBER,cp_customer_trx_line_id NUMBER) IS
72 SELECT SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) receipt_amount
73 FROM ar_receivable_applications_all raa,
74 ar_distributions_all ad
75 WHERE raa.applied_customer_trx_id = cp_customer_trx_id
76 AND raa.application_type = 'CASH'
77 AND raa.status = 'APP'
78 AND raa.display = 'Y'
79 AND raa.receivable_application_id = ad.source_id
80 AND ad.source_table = 'RA'
81 AND ad.ref_customer_trx_Line_Id = cp_customer_trx_line_id;
82
83 BEGIN
84 OPEN l_receipt_amount_csr(p_customer_trx_id,p_customer_trx_line_id); --sechawla 21-jan-2011 10282607 : added p_customer_trx_line_id
85 FETCH l_receipt_amount_csr INTO l_receipt_amount;
86 CLOSE l_receipt_amount_csr;
87
88 return NVL(l_receipt_amount, 0);
89 EXCEPTION
90 WHEN OTHERS THEN
91 return NVL(l_receipt_amount, 0);
92 END;
93
94 ----------------------------------------------------------------------
95
96 FUNCTION receipt_date(p_customer_trx_id IN NUMBER) RETURN DATE IS
97
98 l_receipt_date DATE := sysdate;
99
100 CURSOR l_receipt_date_csr(cp_customer_trx_id NUMBER) IS
101 SELECT MAX(apply_date)
102 FROM AR_RECEIVABLE_APPLICATIONS_ALL
103 WHERE APPLIED_CUSTOMER_TRX_ID = cp_customer_trx_id
104 AND APPLICATION_TYPE = 'CASH'
105 AND STATUS = 'APP';
106 BEGIN
107 OPEN l_receipt_date_csr(p_customer_trx_id);
108 FETCH l_receipt_date_csr INTO l_receipt_date;
109 CLOSE l_receipt_date_csr;
110
111 return TRUNC(l_receipt_date);
112 EXCEPTION
113 WHEN OTHERS THEN
114 return TRUNC(l_receipt_date);
115 END;
116
117 ----------------------------------------------------------------------
118
119 FUNCTION partial_receipt_amount(p_customer_trx_id IN NUMBER) RETURN NUMBER IS
120
121 l_receipt_amount NUMBER := 0;
122
123 --rkuttiya R12 B Billing Architecture modified the following cursor
124 /* 13619067
125 CURSOR l_receipt_amount_csr(cp_customer_trx_id NUMBER) IS
126 SELECT NVL(SUM(LINE_APPLIED), 0)
127 FROM AR_RECEIVABLE_APPLICATIONS_ALL RAA
128 ,OKL_BPD_TLD_AR_LINES_V ARL
129 WHERE RAA.APPLIED_CUSTOMER_TRX_ID = cp_customer_trx_id
130 AND RAA.APPLICATION_TYPE = 'CASH'
131 AND RAA.STATUS = 'APP'
132 AND RAA.applied_customer_trx_id = arl.customer_trx_id
133 AND NOT EXISTS (SELECT 1 FROM OKL_INVESTOR_PAYOUT_SUMMARY_B PAY
134 WHERE PAY.INVESTOR_AGREEMENT_ID IS NULL
135 AND PAY.TLD_ID = ARL.TLD_ID
136 AND PAY.RECEIVABLE_APPLICATION_ID = RAA.RECEIVABLE_APPLICATION_ID);
137 */
138
139 -- 13619067
140 CURSOR l_receipt_amount_csr(cp_customer_trx_id NUMBER) IS
141 select nvl(sum(line_applied),0)
142 from ar_receivable_applications_all raa,
143 okl_txd_ar_ln_dtls_b tld,
144 ra_customer_trx_lines_all ractrl
145 WHERE tld.ID = ractrl.interface_line_attribute14
146 and raa.applied_customer_trx_id=cp_customer_trx_id
147 and raa.applied_customer_trx_id = ractrl.customer_trx_id
148 AND NOT EXISTS (SELECT 1 FROM OKL_INVESTOR_PAYOUT_SUMMARY_B PAY WHERE
149 PAY.INVESTOR_AGREEMENT_ID IS NULL AND PAY.TLD_ID = TLD.ID AND
150 PAY.RECEIVABLE_APPLICATION_ID = RAA.RECEIVABLE_APPLICATION_ID);
151 BEGIN
152 OPEN l_receipt_amount_csr(p_customer_trx_id);
153 FETCH l_receipt_amount_csr INTO l_receipt_amount;
154 CLOSE l_receipt_amount_csr;
155
156 return NVL(l_receipt_amount, 0);
157 EXCEPTION
158 WHEN OTHERS THEN
159 return NVL(l_receipt_amount, 0);
160 END;
161
162 ----------------------------------------------------------------------
163
164 FUNCTION partial_receipt_date(p_customer_trx_id IN NUMBER) RETURN DATE IS
165
166 l_receipt_date DATE := SYSDATE;
167
168 --rkuttiya R12 B Billing modified following cursor
169 CURSOR l_receipt_date_csr(cp_customer_trx_id NUMBER) IS
170 SELECT MAX(raa.apply_date)
171 FROM AR_RECEIVABLE_APPLICATIONS_ALL RAA
172 ,OKL_BPD_TLD_AR_LINES_V ARL
173 WHERE RAA.APPLIED_CUSTOMER_TRX_ID = cp_customer_trx_id
174 AND RAA.APPLICATION_TYPE = 'CASH'
175 AND RAA.STATUS = 'APP'
176 AND RAA.applied_customer_trx_id = arl.customer_trx_id
177 AND NOT EXISTS (SELECT 1 FROM OKL_INVESTOR_PAYOUT_SUMMARY_B PAY
178 WHERE PAY.INVESTOR_AGREEMENT_ID IS NULL
179 AND PAY.TLD_ID = ARL.TLD_ID
180 AND PAY.RECEIVABLE_APPLICATION_ID = RAA.RECEIVABLE_APPLICATION_ID);
181 BEGIN
182 OPEN l_receipt_date_csr(p_customer_trx_id);
183 FETCH l_receipt_date_csr INTO l_receipt_date;
184 CLOSE l_receipt_date_csr;
185
186 return TRUNC(l_receipt_date);
187 EXCEPTION
188 WHEN OTHERS THEN
189 return TRUNC(l_receipt_date);
190 END;
191
192 ----------------------------------------------------------------------
193
194 FUNCTION get_next_pymt_date(p_start_date IN Date
195 ,p_frequency IN VARCHAR2
196 ,p_offset_date IN DATE) RETURN DATE
197 AS
198 --l_next_date DATE := to_date(to_char(p_start_date, 'MM/DD') || to_char(p_offset_date, 'RRRR'), 'MM/DD/RRRR');
199 l_next_date DATE := p_start_date;
200 l_mnth_adder NUMBER := 0;
201 BEGIN
202 if(UPPER(p_frequency) = 'A') then
203 l_mnth_adder := 12;
204 elsif(UPPER(p_frequency) = 'Q') then
205 l_mnth_adder := 3;
206 elsif(UPPER(p_frequency) = 'M') then
207 l_mnth_adder := 1;
208 else
209 return null;
210 end if;
211
212 loop
213 exit when l_next_date >= p_offset_date;
214 --select add_months(l_next_date, l_mnth_adder) INTO l_next_date from dual;
215 l_next_date := add_months(l_next_date, l_mnth_adder);
216 end loop;
217 return l_next_date;
218 EXCEPTION
219 WHEN others THEN
220 print_line ( '** EXCEPTION IN get_next_pymt_date: '||SQLERRM);
221 return null;
222 END get_next_pymt_date;
223
224 ----------------------------------------------------------------------
225
226 FUNCTION get_kle_party_pmt_hdr(p_khr_id IN NUMBER
227 ,p_kle_id IN Number
228 ,p_lyt_code IN VARCHAR2
229 ,p_term IN VARCHAR2) RETURN NUMBER IS
230
231 CURSOR l_pph_csr(cp_khr_id NUMBER, cp_kle_id NUMBER, cp_term VARCHAR2) IS
232 SELECT id FROM
233 OKL_PARTY_PAYMENT_HDR
234 WHERE dnz_chr_id = cp_khr_id
235 AND NVL(cle_id, -99) = cp_kle_id
236 AND passthru_term = cp_term;
237
238 l_party_pmt_hdr_id NUMBER:= NULL;
239 BEGIN
240 OPEN l_pph_csr(p_khr_id, p_kle_id, p_term);
241 FETCH l_pph_csr INTO l_party_pmt_hdr_id;
242 CLOSE l_pph_csr;
243
244 --for an asset line, if evergreen passthru parameters are not defined at the line level,
245 --then check to see if they are defined at the contract level
246 IF (l_party_pmt_hdr_id IS NULL AND p_lyt_code = 'FREE_FORM1') THEN
247 OPEN l_pph_csr(p_khr_id, -99, p_term);
248 FETCH l_pph_csr INTO l_party_pmt_hdr_id;
249 CLOSE l_pph_csr;
250 END IF;
251
252 return l_party_pmt_hdr_id;
253 EXCEPTION
254 WHEN OTHERS THEN
255 return l_party_pmt_hdr_id;
256 END;
257
258 ----------------------------------------------------------------------
259
260 PROCEDURE invoice_insert (
261 p_api_version IN NUMBER,
262 p_init_msg_list IN VARCHAR2,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_msg_count OUT NOCOPY NUMBER,
265 x_msg_data OUT NOCOPY VARCHAR2,
266 p_receivables_invoice_id IN NUMBER,
267 p_tapv_rec IN okl_tap_pvt.tapv_rec_type,
268 p_tplv_rec IN okl_tpl_pvt.tplv_rec_type,
269 x_tapv_rec OUT NOCOPY okl_tap_pvt.tapv_rec_type)
270 IS
271
272 -----------------------------------------------------------------
273 -- Declare Process Variable
274 -----------------------------------------------------------------
275 l_api_name CONSTANT VARCHAR2(30) := 'INVOICE_INSERT';
276 l_okl_application_id NUMBER(3) := 540;
277 l_document_category VARCHAR2(100):= 'OKL Lease Pay Invoices';
278 lX_dbseqnm VARCHAR2(2000):= '';
279 lX_dbseqid NUMBER(38):= NULL;
280 ------------------------------------------------------------
281 -- Declare records: Payable Invoice Headers, Lines and Distributions
282 ------------------------------------------------------------
283 l_tapv_rec Okl_tap_pvt.tapv_rec_type;
284 lx_tapv_rec Okl_tap_pvt.tapv_rec_type;
285 l_tplv_rec okl_tpl_pvt.tplv_rec_type;
286 lx_tplv_rec okl_tpl_pvt.tplv_rec_type;
287
288 --start:| 18-May-07 cklee -- Accounting API CR, Disbursement Central API uptake |
289 l_tplv_tbl okl_tpl_pvt.tplv_tbl_type;
290 lx_tplv_tbl okl_tpl_pvt.tplv_tbl_type;
291 --end:| 18-May-07 cklee -- Accounting API CR, Disbursement Central API uptake |
292
293 l_tmpl_identify_rec Okl_Account_Dist_Pvt.TMPL_IDENTIFY_REC_TYPE;
294 l_dist_info_rec Okl_Account_Dist_Pvt.dist_info_REC_TYPE;
295 l_ctxt_val_tbl okl_execute_formula_pvt.ctxt_val_tbl_type;
296 l_acc_gen_primary_key_tbl Okl_Account_Generator_Pvt.primary_key_tbl;
297 lu_tapv_rec Okl_tap_pvt.tapv_rec_type;
298 lux_tapv_rec Okl_tap_pvt.tapv_rec_type;
299
300 l_template_tbl Okl_Account_Dist_Pub.AVLV_TBL_TYPE;
301 l_amount_tbl Okl_Account_Dist_Pub.AMOUNT_TBL_TYPE;
302 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
303
304 CURSOR pdt_id_csr ( p_khr_id NUMBER ) IS
305 SELECT khr.pdt_id
306 FROM okl_k_headers khr
307 WHERE khr.id = p_khr_id;
308
309 -- SUBTYPE khr_id_type IS okc_k_headers_b.id%type;
310 l_khr_id okc_k_headers_b.id%type;
311 l_currency_code okc_k_headers_b.currency_code%type;
312 l_currency_conversion_type okl_k_headers.currency_conversion_type%type;
313 l_currency_conversion_rate okl_k_headers.currency_conversion_rate%type;
314 l_currency_conversion_date okl_k_headers.currency_conversion_date%type;
315
316 --Get currency conversion attributes for a contract
317 CURSOR l_curr_conv_csr(cp_khr_id IN okc_k_headers_b.id%TYPE) IS
318 SELECT currency_code
319 ,currency_conversion_type
320 ,currency_conversion_rate
321 ,currency_conversion_date
322 FROM okl_k_headers_full_v
323 WHERE id = cp_khr_id;
324
325 --End code added by pgomes on 02/12/2003
326
327 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
328
329 BEGIN
330 l_return_status := okl_api.start_activity(
331 p_api_name => l_api_name,
332 p_init_msg_list => p_init_msg_list,
333 p_api_type => '_PVT',
334 x_return_status => l_return_status);
335
336 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
337 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
338 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
339 RAISE OKL_API.G_EXCEPTION_ERROR;
340 END IF;
341
342 x_return_status := OKL_API.G_RET_STS_SUCCESS;
343 print_line ( '******** IN PROCEDURE INVOICE_INSERT ********');
344 ------------------------------------------------------------
345 -- Initialization of Parmaeters
346 ------------------------------------------------------------
347 l_tapv_rec := p_tapv_rec;
348 l_tplv_rec := p_tplv_rec;
349
350 ------------------------------------------------------------
351 -- Derive Invoice Number: l_tapv_rec.Invoice_Number
352 ------------------------------------------------------------
353 --start:| 18-May-07 cklee -- Accounting API CR, Disbursement Central API uptake |
354 /*
355 print_line ( ' -- Generating Invoice Number....');
356 l_tapv_rec.Invoice_Number := fnd_seqnum.get_next_sequence
357 (appid => l_okl_application_id,
358 cat_code => l_document_category,
359 sobid => P_tapv_rec.set_of_books_id,
360 met_code => 'A',
361 trx_date => SYSDATE,
362 dbseqnm => lx_dbseqnm,
363 dbseqid => lx_dbseqid);
364 l_tapv_rec.invoice_category_code := NULL;
365 l_tapv_rec.vendor_invoice_number := l_tapv_rec.Invoice_Number;
366
367 print_line ( ' -- Generated Invoice Number is: '||l_tapv_rec.Invoice_Number);
368
369 l_tapv_rec.date_gl := l_tapv_rec.date_invoiced;
370
371 print_line ( ' -- Invoice Date is: '||l_tapv_rec.date_invoiced ||' and GL Date is: '||l_tapv_rec.date_gl);
372
373 print_line ( ' -- Creating Header Record.');
374 ------------------------------------------------------------
375 -- Insert Invoice Headers
376 ------------------------------------------------------------
377
378 --Start code added by pgomes on 02/12/2003
379 --get contract currency parameters
380 --l_khr_id := p_tapv_rec.khr_id ;
381 l_khr_id := p_tplv_rec.khr_id ;
382
383 l_currency_code := null;
384 l_currency_conversion_type := null;
385 l_currency_conversion_rate := null;
386 l_currency_conversion_date := null;
387
388 FOR cur IN l_curr_conv_csr(l_khr_id) LOOP
389 l_currency_code := cur.currency_code;
390 l_currency_conversion_type := cur.currency_conversion_type;
391 l_currency_conversion_rate := cur.currency_conversion_rate;
392 l_currency_conversion_date := cur.currency_conversion_date;
393 END LOOP;
394 --End code added by pgomes on 02/12/2003
395
396 -- sjalasut, nullified l_tapv_rec.khr_id as part of OKLR12B disbursements proeject
397 l_tapv_rec.khr_id := NULL;
398
399 -- Start of wraper code generated automatically by Debug code generator for Okl_Trx_Ap_Invoices_Pub.insert_trx_ap_invoices
400 IF(L_DEBUG_ENABLED='Y') THEN
401 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
402 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
403 END IF;
404 IF(IS_DEBUG_PROCEDURE_ON) THEN
405 BEGIN
406 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPIDB.pls call Okl_Trx_Ap_Invoices_Pub.insert_trx_ap_invoices ');
407 END;
408 END IF;
409
410 --Start code added by pgomes on 02/12/2003
411
412 --Check for currency code
413 IF (NVL(l_tapv_rec.currency_code, okl_api.g_miss_char) = okl_api.g_miss_char) THEN
414 l_tapv_rec.currency_code := l_currency_code;
415 END IF;
416
417 --Check for currency conversion type
418 IF (NVL(l_tapv_rec.currency_conversion_type, okl_api.g_miss_char) = okl_api.g_miss_char) THEN
419 l_tapv_rec.currency_conversion_type := l_currency_conversion_type;
420 END IF;
421
422 --Check for currency conversion date
423 IF (NVL(l_tapv_rec.currency_conversion_date, okl_api.g_miss_date) = okl_api.g_miss_date) THEN
424 l_tapv_rec.currency_conversion_date := l_currency_conversion_date;
425 END IF;
426
427
428 --Uncommented the below block of code to handle currency conversion rate
429 IF (l_tapv_rec.currency_conversion_type = 'User') THEN
430 IF (l_tapv_rec.currency_code = okl_accounting_util.get_func_curr_code) THEN
431 l_tapv_rec.currency_conversion_rate := 1;
432 ELSE
433 IF (NVL(l_tapv_rec.currency_conversion_rate, okl_api.g_miss_num) = okl_api.g_miss_num) THEN
434 l_tapv_rec.currency_conversion_rate := l_currency_conversion_rate;
435 END IF;
436 END IF;
437 --pgomes 02/12/2003 added below code to get curr conv rate
438 --
439 ELSIF (l_tapv_rec.currency_conversion_type = 'Spot' OR l_tapv_rec.currency_conversion_type = 'Corporate') THEN
440 l_tapv_rec.currency_conversion_rate := okl_accounting_util.get_curr_con_rate
441 (p_from_curr_code => l_tapv_rec.currency_code,
442 p_to_curr_code => okl_accounting_util.get_func_curr_code,
443 p_con_date => l_tapv_rec.currency_conversion_date,
444 p_con_type => l_tapv_rec.currency_conversion_type);
445 --
446 END IF;
447
448
449 --End code added by pgomes on 02/12/2003
450
451 l_tapv_rec.amount := OKL_ACCOUNTING_UTIL.round_amount(l_tapv_rec.amount, l_tapv_rec.currency_code);
452 l_tplv_rec.amount := OKL_ACCOUNTING_UTIL.round_amount(l_tplv_rec.amount, l_tapv_rec.currency_code);
453
454 Okl_Trx_Ap_Invoices_Pub.insert_trx_ap_invoices(
455 p_api_version => p_api_version
456 ,p_init_msg_list => p_init_msg_list
457 ,x_return_status => x_return_status
458 ,x_msg_count => x_msg_count
459 ,x_msg_data => x_msg_data
460 ,p_tapv_rec => l_tapv_rec
461 ,x_tapv_rec => lx_tapv_rec);
462 IF(IS_DEBUG_PROCEDURE_ON) THEN
463 BEGIN
464 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPIDB.pls call Okl_Trx_Ap_Invoices_Pub.insert_trx_ap_invoices ');
465 END;
466 END IF;
467 -- End of wraper code generated automatically by Debug code generator for Okl_Trx_Ap_Invoices_Pub.insert_trx_ap_invoices
468
469 IF ( x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
470 print_line ( ' -- Created Header Record with Id '||lx_tapv_rec.id);
471 lu_tapv_rec := lx_tapv_rec;
472 ELSE
473 print_line ( '*=> ERROR : Creating Header.');
474 END IF;
475
476 ------------------------------------------------------------
477 -- Insert Invoice Line
478 ------------------------------------------------------------
479 l_tplv_rec.tap_id := lx_tapv_rec.id;
480
481 print_line ( ' -- Creating Line Record.');
482
483 -- Start of wraper code generated automatically by Debug code generator for OKL_TXL_AP_INV_LNS_PUB.insert_txl_ap_inv_lns
484 IF(IS_DEBUG_PROCEDURE_ON) THEN
485 BEGIN
486 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPIDB.pls call OKL_TXL_AP_INV_LNS_PUB.insert_txl_ap_inv_lns ');
487 END;
488 END IF;
489 OKL_TXL_AP_INV_LNS_PUB.insert_txl_ap_inv_lns(
490 p_api_version => p_api_version
491 ,p_init_msg_list => p_init_msg_list
492 ,x_return_status => x_return_status
493 ,x_msg_count => x_msg_count
494 ,x_msg_data => x_msg_data
495 ,p_tplv_rec => l_tplv_rec
496 ,x_tplv_rec => lx_tplv_rec);
497 IF(IS_DEBUG_PROCEDURE_ON) THEN
498 BEGIN
499 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPIDB.pls call OKL_TXL_AP_INV_LNS_PUB.insert_txl_ap_inv_lns ');
500 END;
501 END IF;
502 -- End of wraper code generated automatically by Debug code generator for OKL_TXL_AP_INV_LNS_PUB.insert_txl_ap_inv_lns
503
504 IF ( x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
505 print_line ( ' -- Created Line Record with Id '||lx_tplv_rec.id);
506 ELSE
507 print_line ( '*=> ERROR : Creating Line.');
508 END IF;
509
510 ------------------------------------------------------------
511 -- Derive and Insert Distribution Line
512 ------------------------------------------------------------
513
514 print_line ( ' -- Creating Distributions. Supplied parameters:');
515
516
517 ------------------ Accounting Engine Calls --------------------------
518 l_tmpl_identify_rec.product_id := NULL;
519
520 -- Get Product Id
521 OPEN pdt_id_csr ( l_khr_id );
522 FETCH pdt_id_csr INTO l_tmpl_identify_rec.product_id;
523 CLOSE pdt_id_csr;
524
525 l_tmpl_identify_rec.transaction_type_id := P_tapv_rec.try_id;
526 l_tmpl_identify_rec.stream_type_id := p_tplv_rec.sty_id;
527
528 l_tmpl_identify_rec.ADVANCE_ARREARS := NULL;
529 l_tmpl_identify_rec.FACTORING_SYND_FLAG := NULL;
530 l_tmpl_identify_rec.SYNDICATION_CODE := NULL;
531 --l_tmpl_identify_rec.FACTORING_CODE := NULL;
532 l_tmpl_identify_rec.MEMO_YN := 'N';
533 l_tmpl_identify_rec.PRIOR_YEAR_YN := 'N';
534
535 Okl_Securitization_Pvt.check_khr_ia_associated(p_api_version => p_api_version
536 ,p_init_msg_list => p_init_msg_list
537 ,x_return_status => x_return_status
538 ,x_msg_count => x_msg_count
539 ,x_msg_data => x_msg_data
540 ,p_khr_id => l_khr_id
541 ,p_scs_code => NULL
542 ,p_trx_date => p_tapv_rec.date_invoiced
543 ,x_fact_synd_code => l_tmpl_identify_rec.FACTORING_SYND_FLAG
544 ,x_inv_acct_code => l_tmpl_identify_rec.INVESTOR_CODE);
545
546 IF ( x_return_status = okl_api.g_ret_sts_success) THEN
547 print_line ( ' -- Okl_Securitization_Pvt.check_khr_ia_associated called successfully ');
548 ELSE
549 print_line ( '*=> ERROR : Calling Okl_Securitization_Pvt.check_khr_ia_associated.');
550 END IF;
551
552
553 l_dist_info_rec.source_id := lx_tplv_rec.id;
554 l_dist_info_rec.source_table := 'OKL_TXL_AP_INV_LNS_B';
555 l_dist_info_rec.accounting_date := l_tapv_rec.date_invoiced;
556 l_dist_info_rec.gl_reversal_flag :='N';
557 l_dist_info_rec.post_to_gl :='N';
558 l_dist_info_rec.amount := ABS(p_tapv_rec.amount);
559 l_dist_info_rec.currency_code := p_tapv_rec.currency_code;
560 l_dist_info_rec.contract_id := l_khr_id;
561 l_dist_info_rec.contract_line_id := p_tplv_rec.kle_id;
562
563 --Start code added by pgomes on 02/12/2003
564
565 --Check for currency code
566 l_dist_info_rec.currency_code := l_tapv_rec.currency_code;
567
568 IF (NVL(l_dist_info_rec.currency_code, okl_api.g_miss_char) = okl_api.g_miss_char) IS NULL THEN
569 l_dist_info_rec.currency_code := l_currency_code;
570 END IF;
571
572 --Check for currency conversion type
573 l_dist_info_rec.currency_conversion_type := l_tapv_rec.currency_conversion_type;
574
575 IF (NVL(l_dist_info_rec.currency_conversion_type, okl_api.g_miss_char) = okl_api.g_miss_char) THEN
576 l_dist_info_rec.currency_conversion_type := l_currency_conversion_type;
577 END IF;
578
579
580 --Check for currency conversion date
581 l_dist_info_rec.currency_conversion_date := l_tapv_rec.currency_conversion_date;
582
583 IF (NVL(l_dist_info_rec.currency_conversion_date, okl_api.g_miss_date) = okl_api.g_miss_date) THEN
584 l_dist_info_rec.currency_conversion_date := l_currency_conversion_date;
585 END IF;
586
587
588 --Uncommented the below block of code to handle currency conversion rate
589 IF (l_dist_info_rec.currency_conversion_type = 'User') THEN
590 IF (l_dist_info_rec.currency_code = okl_accounting_util.get_func_curr_code) THEN
591 l_dist_info_rec.currency_conversion_rate := 1;
592 ELSE
593 IF (NVL(l_tapv_rec.currency_conversion_rate, okl_api.g_miss_num) = okl_api.g_miss_num) THEN
594 l_dist_info_rec.currency_conversion_rate := l_currency_conversion_rate;
595 ELSE
596 l_dist_info_rec.currency_conversion_rate := l_tapv_rec.currency_conversion_rate;
597 END IF;
598 END IF;
599 --pgomes 02/12/2003 added below code to get curr conv rate
600 ELSIF (l_dist_info_rec.currency_conversion_type = 'Spot' OR l_dist_info_rec.currency_conversion_type = 'Corporate') THEN
601 l_dist_info_rec.currency_conversion_rate := okl_accounting_util.get_curr_con_rate
602 (p_from_curr_code => l_dist_info_rec.currency_code,
603 p_to_curr_code => okl_accounting_util.get_func_curr_code,
604 p_con_date => l_dist_info_rec.currency_conversion_date,
605 p_con_type => l_dist_info_rec.currency_conversion_type);
606 END IF;
607
608 --pgomes 02/12/2003 added below code to default rate so that acct dist are created
609 l_dist_info_rec.currency_conversion_rate := NVL(l_dist_info_rec.currency_conversion_rate, 1);
610
611 --End code added by pgomes on 02/12/2003
612
613 -- Start of wraper code generated automatically by Debug code generator for Okl_Acc_Call_Pvt.Okl_Populate_Acc_Gen
614 IF(IS_DEBUG_PROCEDURE_ON) THEN
615 BEGIN
616 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPIDB.pls call Okl_Acc_Call_Pvt.Okl_Populate_Acc_Gen ');
617 END;
618 END IF;
619 Okl_Acc_Call_Pvt.Okl_Populate_Acc_Gen (
620 p_contract_id => l_khr_id,
621 p_contract_line_id => p_tplv_rec.kle_id,
622 x_acc_gen_tbl => l_acc_gen_primary_key_tbl,
623 x_return_status => x_return_status);
624 IF(IS_DEBUG_PROCEDURE_ON) THEN
625 BEGIN
626 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPIDB.pls call Okl_Acc_Call_Pvt.Okl_Populate_Acc_Gen ');
627 END;
628 END IF;
629 -- End of wraper code generated automatically by Debug code generator for Okl_Acc_Call_Pvt.Okl_Populate_Acc_Gen
630
631 IF ( x_return_status = okl_api.g_ret_sts_success) THEN
632 print_line ( ' -- Accounting engine called successfully ');
633 ELSE
634 print_line ( '*=> ERROR : Calling Accounting engine.');
635 END IF;
636
637
638 --dbms_output.PUT_LINE (' --conversion type passed for distr creation =>' || l_dist_info_rec.currency_conversion_type);
639 -- Start of wraper code generated automatically by Debug code generator for Okl_Account_Dist_Pub.CREATE_ACCOUNTING_DIST
640 IF(IS_DEBUG_PROCEDURE_ON) THEN
641 BEGIN
642 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPIDB.pls call Okl_Account_Dist_Pub.CREATE_ACCOUNTING_DIST ');
643 END;
644 END IF;
645 Okl_Account_Dist_Pub.CREATE_ACCOUNTING_DIST(
646 p_api_version => p_api_version
647 ,p_init_msg_list => p_init_msg_list
648 ,x_return_status => x_return_status
649 ,x_msg_count => x_msg_count
650 ,x_msg_data => x_msg_data
651 ,p_tmpl_identify_rec => l_tmpl_identify_rec
652 ,p_dist_info_rec => l_dist_info_rec
653 ,p_ctxt_val_tbl => l_ctxt_val_tbl
654 ,p_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl
655 ,x_template_tbl => l_template_tbl
656 ,x_amount_tbl => l_amount_tbl);
657 IF(IS_DEBUG_PROCEDURE_ON) THEN
658 BEGIN
659 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPIDB.pls call Okl_Account_Dist_Pub.CREATE_ACCOUNTING_DIST ');
660 END;
661 END IF;
662 -- End of wraper code generated automatically by Debug code generator for Okl_Account_Dist_Pub.CREATE_ACCOUNTING_DIST
663
664 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
665 --RAISE G_EXCEPTION_HALT_VALIDATION;
666 print_line ( '*=> ERROR: Accounting distributions not created.');
667
668 --l_return_status := x_return_status;
669 lu_tapv_rec.TRX_STATUS_CODE := 'ERROR';
670
671 UPDATE Okl_Trx_Ap_Invoices_B
672 SET TRX_STATUS_CODE = 'ERROR'
673 WHERE id = lx_tapv_rec.id;
674
675 IF ( x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
676 print_line ( ' -- Updated Header Record with Id '||lx_tapv_rec.id || ' with ERROR Status');
677 ELSE
678 print_line ( '*=> ERROR : Updating Header with ERROR Status.');
679 END IF;
680 --x_return_status := l_return_status;
681 END IF;
682 ------------------End Accounting Engine Calls -----------------------
683 -- l_dist_info_rec.contract_line_id := p_tplv_rec.kle_id;
684
685 x_tapv_rec := lx_tapv_rec;
686
687 print_line ( ' -- KLE_ID: '||p_tplv_rec.kle_id);
688 print_line ( ' -- PDT_ID: '||l_tmpl_identify_rec.product_id);
689 print_line ( ' -- Amount: '||p_tapv_rec.amount);
690 print_line ( ' -- Currency: '||p_tapv_rec.currency_code);
691 print_line ( ' -- KHR_ID: '||l_khr_id);
692 */
693 /* cklee 18-May-07
694 Call to the common Disbursement API
695 start changes */
696 IF(L_DEBUG_ENABLED='Y') THEN
697 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
698 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
699 END IF;
700
701 IF(IS_DEBUG_PROCEDURE_ON) THEN
702 BEGIN
703 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Before create disbursement transaction, ap invoice header id : ' || lx_tapv_rec.id);
704 END;
705 END IF;
706 -- Add tpl_rec to table
707 l_tplv_tbl(1) := l_tplv_rec;
708
709 --Call the commong disbursement API to create transactions
710 Okl_Create_Disb_Trans_Pvt.create_disb_trx(
711 p_api_version => p_api_version
712 ,p_init_msg_list => p_init_msg_list
713 ,x_return_status => x_return_status
714 ,x_msg_count => x_msg_count
715 ,x_msg_data => x_msg_data
716 ,p_tapv_rec => l_tapv_rec
717 ,p_tplv_tbl => l_tplv_tbl
718 ,x_tapv_rec => lx_tapv_rec
719 ,x_tplv_tbl => lx_tplv_tbl);
720
721 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
722 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
723 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
724 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
725 END IF;
726
727 IF(IS_DEBUG_PROCEDURE_ON) THEN
728 BEGIN
729 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'after create disbursement transaction, ap invoice header id : ' || lx_tapv_rec.id);
730 END;
731 END IF;
732 /* cklee end changes */
733
734 --end:| 18-May-07 cklee -- Accounting API CR, Disbursement Central API uptake |
735
736 print_line ( '******** EXITING PROCEDURE INVOICE_INSERT ********');
737
738 okl_api.end_activity(x_msg_count => x_msg_count
739 ,x_msg_data => x_msg_data);
740 EXCEPTION
741 WHEN G_EXCEPTION_HALT_VALIDATION THEN
742 print_line ( '*=> ERROR: '||SQLERRM);
743 Okl_Api.SET_MESSAGE(p_app_name => g_app_name
744 ,p_msg_name => g_unexpected_error
745 ,p_token1 => g_sqlcode_token
746 ,p_token1_value => SQLCODE
747 ,p_token2 => g_sqlerrm_token
748 ,p_token2_value => SQLERRM);
749 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
750
751 WHEN OTHERS THEN
752 print_line ( '*=> ERROR: '||SQLERRM);
753 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
754
755 END invoice_insert;
756
757 PROCEDURE invoice_disbursement (
758 p_api_version IN NUMBER,
759 p_init_msg_list IN VARCHAR2,
760 x_return_status OUT NOCOPY VARCHAR2,
761 x_msg_count OUT NOCOPY NUMBER,
762 x_msg_data OUT NOCOPY VARCHAR2,
763 p_disb_rec IN disb_rec_type)
764
765 IS
766
767 ----------------------------------------------------------------
768 -- Declare passthru party payment details Cursor
769 -----------------------------------------------------------------
770 CURSOR c_vendor_dtls(cp_pph_id NUMBER) IS
771 SELECT ppd.vendor_id,
772 ppd.pay_site_id,
773 NVL(ppd.payment_term_id, pvs.terms_id) payment_term_id,
774 NVL(ppd.payment_method_code, pvs.payment_method_lookup_code) payment_method_code,
775 NVL(ppd.pay_group_code, pvs.pay_group_lookup_code) pay_group_code,
776 ppd.payment_basis,
777 TRUNC(NVL(ppd.payment_start_date, khr.start_date)) payment_start_date,
778 ppd.payment_frequency,
779 NVL(ppd.remit_days, 0) remit_days,
780 ppd.disbursement_basis,
781 ppd.disbursement_fixed_amount,
782 ppd.disbursement_percent,
783 ppd.processing_fee_basis,
784 ppd.processing_fee_fixed_amount,
785 ppd.processing_fee_percent
786 --ppd.processing_fee_formula
787 FROM okl_party_payment_hdr pph,
788 okl_party_payment_dtls ppd,
789 okc_k_headers_b khr,
790 po_vendor_sites pvs
791 WHERE pph.id = cp_pph_id
792 AND pph.id = ppd.payment_hdr_id
793 AND pph.dnz_chr_id = khr.id
794 AND ppd.pay_site_id = pvs.vendor_site_id;
795
796 -- Inner declare # 1
797 -----------------------------------------------------------------
798 -- Declare Process Variable
799 -----------------------------------------------------------------
800 l_api_name CONSTANT VARCHAR2(30) := 'INVOICE_DISBURSEMENT';
801
802 l_amount_pass NUMBER := 0;
803 l_process_fee NUMBER := 0;
804 l_invoice_date DATE := SYSDATE;
805
806 ------------------------------------------------------------
807 -- Declare records: Payable Invoice Headers, Lines and Distributions
808 ------------------------------------------------------------
809 l_tapv_rec Okl_tap_pvt.tapv_rec_type;
810 l_tplv_rec okl_tpl_pvt.tplv_rec_type;
811 l_tapv_rec_null Okl_tap_pvt.tapv_rec_type;
812 l_tplv_rec_null okl_tpl_pvt.tplv_rec_type;
813 lx_tapv_rec Okl_tap_pvt.tapv_rec_type;
814 l_disb_rec disb_rec_type;
815
816 -- Cursor to get the AR invoice Numbers for log file
817 CURSOR ar_inv_csr ( p_receivables_invoice_id NUMBER ) IS
818 SELECT trx_number
819 FROM ra_customer_trx_all
820 WHERE CUSTOMER_TRX_ID = p_receivables_invoice_id;
821
822 --cursor to get the receipt applications applied to a invoice
823 CURSOR l_receipt_applic_csr(cp_customer_trx_id NUMBER) IS
824 SELECT RAA.receivable_application_id
825 ,RAA.LINE_APPLIED
826 ,RAA.apply_date
827 ,ARL.tld_id tld_id
828 FROM AR_RECEIVABLE_APPLICATIONS_ALL RAA
829 ,OKL_BPD_TLD_AR_LINES_V ARL
830 WHERE RAA.APPLIED_CUSTOMER_TRX_ID = cp_customer_trx_id
831 AND RAA.APPLICATION_TYPE = 'CASH'
832 AND RAA.STATUS = 'APP'
833 AND RAA.applied_customer_trx_id = arl.customer_trx_id
834 AND NOT EXISTS (SELECT 1 FROM OKL_INVESTOR_PAYOUT_SUMMARY_B PAY
835 WHERE PAY.INVESTOR_AGREEMENT_ID IS NULL
836 AND PAY.TLD_ID = ARL.TLD_ID
837 AND PAY.RECEIVABLE_APPLICATION_ID = RAA.RECEIVABLE_APPLICATION_ID);
838
839
840 --Local Variables
841 l_ar_inv_number ra_customer_trx_all.trx_number%TYPE;
842 l_try_id okl_trx_ap_invoices_b.try_id%TYPE;
843 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
844 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
845
846 TYPE tap_id_tbl_type IS TABLE OF okl_trx_ap_invoices_b.id%type
847 INDEX BY BINARY_INTEGER;
848
849 l_tap_id_tbl tap_id_tbl_type;
850 id_ind NUMBER;
851 l_idh_id NUMBER;
852
853 l_lsm_rcpt_tbl lsm_rcpt_tbl_type;
854 lsm_rcpt_id_ind NUMBER;
855 BEGIN
856
857 l_return_status := okl_api.start_activity(
858 p_api_name => l_api_name,
859 p_init_msg_list => p_init_msg_list,
860 p_api_type => '_PVT',
861 x_return_status => l_return_status);
862
863 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
864 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
865 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
866 RAISE OKL_API.G_EXCEPTION_ERROR;
867 END IF;
868
869 x_return_status := OKL_API.G_RET_STS_SUCCESS;
870 l_overall_status := x_return_status;
871 print_line ( '******** IN PROCEDURE INVOICE_DISBURSEMENT ********');
872
873 ------------------------------------------------------------
874 -- Derive Transaction Type
875 ------------------------------------------------------------
876 BEGIN
877 SELECT id INTO l_try_id
878 FROM okl_trx_types_tl
879 WHERE name = 'Disbursement'
880 AND LANGUAGE= 'US' ;
881 EXCEPTION
882 WHEN OTHERS THEN
883 print_line ( '*=> ERROR: while deriving transaction type '||SQLERRM);
884 END;
885
886 l_disb_rec := p_disb_rec;
887 l_tap_id_tbl.delete;
888 id_ind := 0;
889
890 FOR cur_vendor_dtls IN c_vendor_dtls(cp_pph_id => l_disb_rec.pph_id) LOOP
891 IF NOT(cur_vendor_dtls.disbursement_basis = 'AMOUNT' AND SIGN(Fnd_Global.CONC_REQUEST_ID) = -1) THEN
892
893 l_tapv_rec := l_tapv_rec_null;
894 l_tplv_rec := l_tplv_rec_null;
895
896 IF (l_disb_rec.receivables_invoice_id IS NOT NULL) THEN
897 OPEN ar_inv_csr(l_disb_rec.receivables_invoice_id);
898 FETCH ar_inv_csr INTO l_ar_inv_number;
899 CLOSE ar_inv_csr;
900
901 print_line (' -- Processing Receivables Invoice: '||l_disb_rec.receivables_invoice_number||' AR Invoice: '||l_ar_inv_number);
902 END IF;
903
904 l_tapv_rec.nettable_yn := 'Y';
905 l_tapv_rec.wait_vendor_invoice_yn := 'N';
906 l_tapv_rec.vendor_id := cur_vendor_dtls.vendor_id;
907 l_tapv_rec.ipvs_id := cur_vendor_dtls.pay_site_id;
908 l_tapv_rec.payment_method_code := cur_vendor_dtls.payment_method_code;
909 l_tapv_rec.ippt_id := cur_vendor_dtls.payment_term_id;
910 l_tapv_rec.pay_group_lookup_code := cur_vendor_dtls.pay_group_code;
911 l_tapv_rec.try_id := l_try_id;
912 l_tapv_rec.set_of_books_id := l_disb_rec.set_of_books_id;
913 l_tapv_rec.org_id := l_disb_rec.org_id;
914 --l_tapv_rec.khr_id := l_disb_rec.khr_id;
915 l_tapv_rec.currency_code := l_disb_rec.currency_code;
916 l_tapv_rec.TRX_STATUS_CODE := 'ENTERED';
917 l_tapv_rec.date_entered := SYSDATE;
918 l_tapv_rec.workflow_yn := 'N';
919 l_tapv_rec.nettable_yn := 'Y';
920 l_tapv_rec.invoice_type := 'STANDARD';
921 -- 01-NOV-2006 ANSETHUR R12B - Legal Entity
922 l_tapv_rec.legal_entity_id := l_disb_rec.legal_entity_id;
923
924 --obtain the passthrough amount
925 print_line (' -- Computing Pass through Amount');
926 l_amount_pass := 0;
927
928 IF (l_disb_rec.payout_basis = 'PARTIAL_RECEIPT') THEN
929 l_disb_rec.amount := 0;
930 lsm_rcpt_id_ind := 0;
931 l_lsm_rcpt_tbl.delete;
932 FOR cur_receipt_applic_cur IN l_receipt_applic_csr(l_disb_rec.receivables_invoice_id) LOOP
933 l_disb_rec.amount := l_disb_rec.amount + cur_receipt_applic_cur.line_applied;
934
935 IF (cur_receipt_applic_cur.apply_date > l_disb_rec.transaction_date) THEN
936 l_disb_rec.transaction_date := cur_receipt_applic_cur.apply_date;
937 END IF;
938
939 lsm_rcpt_id_ind := lsm_rcpt_id_ind + 1;
940 --rkuttiya R12 B Billing Architecture commented out following
941 --l_lsm_rcpt_tbl(lsm_rcpt_id_ind).lsm_id := cur_receipt_applic_cur.lsm_id;
942 --rkuttiya R12 B Billing Architecture added following
943 l_lsm_rcpt_tbl(lsm_rcpt_id_ind).tld_id := cur_receipt_applic_cur.tld_id;
944 --
945 l_lsm_rcpt_tbl(lsm_rcpt_id_ind).receivable_application_id := cur_receipt_applic_cur.receivable_application_id;
946 END LOOP;
947 print_line (' -- payout_basis = ''PARTIAL_RECEIPT'' source amount : '||l_disb_rec.amount);
948 print_line (' -- payout_basis = ''PARTIAL_RECEIPT'' transaction_date : '||l_disb_rec.transaction_date);
949 print_line (' -- payout_basis = ''PARTIAL_RECEIPT'' l_lsm_rcpt_tbl count : '||l_lsm_rcpt_tbl.count);
950 END IF;
951
952 IF (l_disb_rec.payout_basis = 'FORMULA') THEN
953 --evaluate the formula payout_basis_formula to get the passthru amount
954 print_line (' -- payout_basis_formula: '||l_disb_rec.payout_basis_formula);
955 Okl_Execute_Formula_Pub.EXECUTE(p_api_version => p_api_version
956 ,p_init_msg_list =>p_init_msg_list
957 ,x_return_status =>x_return_status
958 ,x_msg_count =>x_msg_count
959 ,x_msg_data =>x_msg_data
960 ,p_formula_name =>l_disb_rec.payout_basis_formula
961 ,p_contract_id =>l_disb_rec.khr_id
962 ,p_line_id =>l_disb_rec.kle_id
963 ,x_value =>l_amount_pass
964 );
965
966 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
967 print_line ('*=>ERROR: Evaluating Payout Basis of FORMULA.');
968 l_overall_status := x_return_status;
969 END IF;
970
971 print_line (' -- passthru amount from formula: '||l_amount_pass);
972 ELSE
973 IF (cur_vendor_dtls.disbursement_basis = 'AMOUNT') THEN
974 print_line (' -- passthru fixed amount: '||cur_vendor_dtls.disbursement_fixed_amount);
975 l_amount_pass := cur_vendor_dtls.disbursement_fixed_amount;
976 ELSIF (cur_vendor_dtls.disbursement_basis = 'PERCENT') THEN
977 print_line (' -- passthru percent: '||cur_vendor_dtls.disbursement_percent);
978 l_amount_pass := ((l_disb_rec.amount * cur_vendor_dtls.disbursement_percent)/100);
979 ELSE
980 l_amount_pass := l_disb_rec.amount;
981 END IF;
982 END IF;
983
984 --obtain the passthrough processing fee
985 l_process_fee := 0;
986
987 IF (l_amount_pass > 0) THEN
988 IF (cur_vendor_dtls.processing_fee_basis = 'AMOUNT') THEN
989 print_line (' -- passthru fee fixed amount: '||cur_vendor_dtls.processing_fee_fixed_amount);
990 l_process_fee := cur_vendor_dtls.processing_fee_fixed_amount;
991 ELSIF (cur_vendor_dtls.processing_fee_basis = 'PERCENT') THEN
992 print_line (' -- passthru fee percent: '||cur_vendor_dtls.processing_fee_percent);
993 l_process_fee := ((cur_vendor_dtls.processing_fee_percent * l_amount_pass)/100);
994 END IF;
995 ELSE
996 print_line (' -- passthru fee not charged as passthru amt is -ve');
997 END IF;
998
999 -- bug 8411292: do not deduct the processing fee from the disbursement amount.
1000 -- l_tapv_rec.amount := NVL(l_amount_pass, 0) - NVL(l_process_fee, 0);
1001 l_tapv_rec.amount := NVL(l_amount_pass, 0);
1002
1003 IF SIGN(l_tapv_rec.amount) = -1 THEN
1004 l_tapv_rec.invoice_type := 'CREDIT';
1005 l_tapv_rec.amount := ABS(l_tapv_rec.amount);
1006 END IF;
1007
1008 --obtain the invoice date
1009 l_invoice_date := trunc(sysdate);
1010
1011 print_line (' -- payment_basis: '||cur_vendor_dtls.payment_basis);
1012 IF (cur_vendor_dtls.payment_basis = 'PROCESS_DATE') THEN
1013 print_line (' -- remit_days: '||cur_vendor_dtls.remit_days);
1014 l_invoice_date := l_invoice_date + cur_vendor_dtls.remit_days;
1015 ELSIF (cur_vendor_dtls.payment_basis = 'SCHEDULED') THEN
1016 print_line (' -- payment_frequency: '||cur_vendor_dtls.payment_frequency);
1017 print_line (' -- payment_start_date: '||cur_vendor_dtls.payment_start_date);
1018 l_invoice_date := get_next_pymt_date(p_start_date => cur_vendor_dtls.payment_start_date
1019 ,p_frequency => cur_vendor_dtls.payment_frequency
1020 ,p_offset_date => trunc(sysdate));
1021 ELSIF (cur_vendor_dtls.payment_basis = 'SOURCE_DATE') THEN
1022 l_invoice_date := l_disb_rec.transaction_date;
1023 END IF;
1024
1025 l_tapv_rec.date_invoiced := l_invoice_date;
1026
1027 print_line (' -- vendor_id (Vendor Id): '||l_tapv_rec.vendor_id);
1028 print_line (' -- ipvs_id (Vendor Site Id): '||l_tapv_rec.ipvs_id);
1029 print_line (' -- ippt_id (Terms Id): '||l_tapv_rec.ippt_id);
1030 print_line (' -- payment_method_code: '||l_tapv_rec.payment_method_code);
1031 print_line (' -- pay_group_lookup_code: '||l_tapv_rec.pay_group_lookup_code);
1032 print_line (' -- passthru processing fee: '||l_process_fee);
1033 print_line (' -- passthru amount: '||l_amount_pass);
1034 print_line (' -- invoice type: '||l_tapv_rec.invoice_type);
1035 print_line (' -- date_invoiced: '||l_tapv_rec.date_invoiced);
1036 print_line (' -- passthru_stream_type_id: '||l_disb_rec.passthru_stream_type_id);
1037
1038 print_line (' -- final passthru amount: '||l_tapv_rec.amount); -- Bug: 6663203 fixed
1039
1040 l_tplv_rec.amount := l_tapv_rec.amount;
1041 l_tplv_rec.org_id := l_tapv_rec.org_id;
1042 l_tplv_rec.khr_id := l_disb_rec.khr_id;
1043 l_tplv_rec.kle_id := l_disb_rec.kle_id;
1044 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
1045 -- l_tplv_rec.lsm_id := l_disb_rec.tld_id;
1046 l_tplv_rec.tld_id := l_disb_rec.tld_id;
1047 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
1048 l_tplv_rec.sel_id := l_disb_rec.sel_id;
1049 l_tplv_rec.line_number := 1;
1050
1051 IF (SIGN(Fnd_Global.CONC_REQUEST_ID) <> -1) THEN
1052 l_tplv_rec.inv_distr_line_code := 'AUTO_DISBURSEMENT';
1053 END IF;
1054 l_tplv_rec.sty_id := l_disb_rec.passthru_stream_type_id;
1055
1056 IF (l_disb_rec.payout_basis = 'BILLING') THEN
1057 l_tplv_rec.disbursement_basis_code := 'BILL_DATE';
1058 ELSIF (l_disb_rec.payout_basis IN ('FULL_RECEIPT', 'PARTIAL_RECEIPT')) THEN
1059 l_tplv_rec.disbursement_basis_code := 'CASH_RECEIPT';
1060 ELSIF (l_disb_rec.payout_basis = 'DUE_DATE') THEN
1061 l_tplv_rec.disbursement_basis_code := 'DUE_DATE';
1062 ELSIF (l_disb_rec.payout_basis = 'FORMULA') THEN
1063 l_tplv_rec.disbursement_basis_code := 'FORMULA';
1064 END IF;
1065
1066 print_line (' -- disbursement_basis_code: '||l_tplv_rec.disbursement_basis_code);
1067
1068 -- Test Flag
1069 IF l_tplv_rec.disbursement_basis_code IS NULL THEN
1070 print_line (' ++ Defaulting Disbursement BasisCode to BILL_DATE ');
1071 l_tplv_rec.disbursement_basis_code := 'BILL_DATE';
1072 END IF;
1073 /*
1074 | 06-Dec-07 cklee -- Bug: 6663203 fixed: |
1075 | skip the following passthrough requests: |
1076 | 1. if passthrough amount = 0 |
1077 | 2. if passthrough processing fee >= passthrough amount |
1078 | and record to concurrent request log file |
1079 */
1080 -- start: Bug: 6663203 fixed
1081 IF l_tapv_rec.amount = 0 THEN
1082 print_line (' *************************************************');
1083 print_line (' Skip passthrou request due to request amount = 0');
1084 print_line (' passthrough amount = passthru amount - passthru processing fee amount');
1085 print_line (' *************************************************');
1086 ELSIF NVL(l_process_fee, 0) > ABS(NVL(l_amount_pass, 0)) THEN
1087 print_line (' *************************************************');
1088 print_line (' Skip passthrou request due to processing fee amount > passthru amount');
1089 print_line (' passthrough amount = passthru amount - passthru processing fee amount');
1090 print_line (' *************************************************');
1091 ELSE
1092
1093 IF (NVL(l_overall_status, OKL_API.G_RET_STS_ERROR) = OKL_API.G_RET_STS_SUCCESS) THEN
1094 invoice_insert (
1095 p_api_version => p_api_version,
1096 p_init_msg_list => p_init_msg_list,
1097 x_return_status => x_return_status,
1098 x_msg_count => x_msg_count,
1099 x_msg_data => x_msg_data,
1100 p_receivables_invoice_id => l_disb_rec.receivables_invoice_id,
1101 P_tapv_rec => l_tapv_rec,
1102 p_tplv_rec => l_tplv_rec,
1103 x_tapv_rec => lx_tapv_rec);
1104
1105 id_ind := id_ind + 1;
1106 l_tap_id_tbl(id_ind) := lx_tapv_rec.id;
1107
1108 IF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1109 print_line ( ' -- Inserted Pay Invoices');
1110 ELSE
1111 l_overall_status := x_return_status;
1112 print_line ( '*=> ERROR : Inserting Pay Invoices');
1113 END IF;
1114 END IF;
1115 END IF;
1116 -- end: Bug: 6663203 fixed
1117
1118 IF (l_disb_rec.receivables_invoice_number IS NOT NULL) THEN
1119 print_line (' -- Processing Receivables Invoice: '||l_disb_rec.receivables_invoice_number||' AR Invoice: '||l_ar_inv_number);
1120 END IF;
1121 END IF;
1122 END LOOP;
1123
1124 IF (l_overall_status <> OKL_API.G_RET_STS_SUCCESS AND l_tap_id_tbl.count > 0 ) THEN
1125 print_line (' -- Updating pay invoices TRX_STATUS_CODE with ERROR status since l_overall_status = ' || l_overall_status);
1126 FOR id_ind IN l_tap_id_tbl.first..l_tap_id_tbl.last LOOP
1127 update okl_trx_ap_invoices_b
1128 set TRX_STATUS_CODE = 'ERROR'
1129 ,object_version_number = object_version_number + 1
1130 ,last_updated_by = FND_GLOBAL.USER_ID
1131 ,last_update_date = sysdate
1132 ,last_update_login = FND_GLOBAL.LOGIN_ID
1133 ,request_id = NVL(DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),null)
1134 where id = l_tap_id_tbl(id_ind);
1135 END LOOP;
1136 END IF;
1137
1138 IF (l_overall_status = OKL_API.G_RET_STS_SUCCESS
1139 AND l_disb_rec.payout_basis = 'PARTIAL_RECEIPT'
1140 AND l_lsm_rcpt_tbl.count > 0 ) THEN
1141 FOR lsm_rcpt_id_ind IN l_lsm_rcpt_tbl.first..l_lsm_rcpt_tbl.last LOOP
1142 ------------------------------
1143 -- Populate PK from sequence
1144 ------------------------------
1145 l_idh_id := get_seq_id;
1146 --Bug# 11935977: Added org_id to insert statement
1147 INSERT INTO okl_investor_payout_summary_b
1148 ( ID,
1149 OBJECT_VERSION_NUMBER,
1150 CREATED_BY,
1151 CREATION_DATE,
1152 LAST_UPDATED_BY,
1153 LAST_UPDATE_DATE,
1154 LAST_UPDATE_LOGIN,
1155 INVESTOR_AGREEMENT_ID,
1156 INVESTOR_LINE_ID,
1157 --rkuttiya R12 B Billing Architecture commented out following
1158 --LSM_ID,
1159 --rkuttiya R12 B Billing Architecture added following
1160 TLD_ID,
1161 --
1162 RECEIVABLE_APPLICATION_ID,
1163 ORG_ID
1164 )
1165 VALUES
1166 (
1167 l_idh_id,
1168 1,
1169 Fnd_Global.USER_ID,
1170 SYSDATE,
1171 Fnd_Global.USER_ID,
1172 SYSDATE,
1173 Fnd_Global.LOGIN_ID,
1174 null, --inv_lease_k_rec.Investor_Agreement_id,
1175 null, --share_rec.TOP_LINE_ID,
1176 --rkuttiya R12 B Billing Architecture commented out following
1177 -- l_lsm_rcpt_tbl(lsm_rcpt_id_ind).lsm_id,
1178 --rkuttiya R12 B Billing Architecture added following
1179 l_lsm_rcpt_tbl(lsm_rcpt_id_ind).tld_id,
1180 --
1181 l_lsm_rcpt_tbl(lsm_rcpt_id_ind).receivable_application_id,
1182 l_disb_rec.org_id
1183 );
1184 END LOOP;
1185 END IF;
1186
1187 x_return_status := l_overall_status;
1188 print_line ( '******** EXITING PROCEDURE INVOICE_DISBURSEMENT ********');
1189
1190 okl_api.end_activity(x_msg_count => x_msg_count
1191 ,x_msg_data => x_msg_data);
1192 EXCEPTION
1193 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1194
1195 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1196 p_api_name => l_api_name,
1197 p_pkg_name => G_PKG_NAME,
1198 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1199 x_msg_count => x_msg_count,
1200 x_msg_data => x_msg_data,
1201 p_api_type => '_PVT');
1202
1203 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1204
1205 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1206 p_api_name => l_api_name,
1207 p_pkg_name => G_PKG_NAME,
1208 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1209 x_msg_count => x_msg_count,
1210 x_msg_data => x_msg_data,
1211 p_api_type => '_PVT');
1212
1213 WHEN OTHERS THEN
1214
1215 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1216 p_api_name => l_api_name,
1217 p_pkg_name => G_PKG_NAME,
1218 p_exc_name => 'OTHERS',
1219 x_msg_count => x_msg_count,
1220 x_msg_data => x_msg_data,
1221 p_api_type => '_PVT');
1222 END invoice_disbursement;
1223
1224 ----------------------------------------------------------------------------------
1225 -- Start of comments
1226 --
1227 -- Procedure Name : update_invoice_pay_status
1228 -- Description : update the pay_status_code, date_disbursed of the cnsld
1229 -- invoice stream with the status the pay invoice creation
1230 -- Business Rules :
1231 -- Parameters :
1232 -- Version : 1.0
1233 -- End of comments
1234 ----------------------------------------------------------------------------------
1235 PROCEDURE update_invoice_pay_status(p_api_version IN NUMBER
1236 ,p_init_msg_list IN VARCHAR2
1237 ,x_return_status OUT NOCOPY VARCHAR2
1238 ,x_msg_count OUT NOCOPY NUMBER
1239 ,x_msg_data OUT NOCOPY VARCHAR2
1240 ,p_tld_id IN NUMBER
1241 ,p_status IN VARCHAR2)
1242 IS
1243 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INVOICE_PAY_STATUS';
1244 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1245
1246 --rkuttiya R12 B BIlling Architecture commented out following
1247 /*
1248 u_lsmv_rec Okl_Cnsld_Ar_Strms_Pub.lsmv_rec_type;
1249 r_lsmv_rec Okl_Cnsld_Ar_Strms_Pub.lsmv_rec_type;
1250 */
1251 --rkuttiya R12 B Billing Architecture added following
1252 l_txdv_rec okl_tld_pvt.tldv_rec_type;
1253 lx_txdv_rec okl_tld_pvt.tldv_rec_type;
1254
1255 l_msg_index_out NUMBER;
1256 i NUMBER;
1257 BEGIN
1258 print_line ( '******** IN PROCEDURE UPDATE_INVOICE_PAY_STATUS ********');
1259 print_line (' -- Updating okl_txd_ar_ln_dtls_pub with status : '||p_status);
1260 IF NVL(p_status, 'E') = OKL_API.G_RET_STS_SUCCESS THEN
1261
1262 --rkuttiya R12 B Billing Architecture commented out following
1263 -- u_lsmv_rec.id := p_lsm_id;
1264 -- u_lsmv_rec.pay_status_code := 'PROCESSED';
1265 -- u_lsmv_rec.date_disbursed := trunc(sysdate);
1266 --rkuttiya R12 B Billing Architecture added following
1267 l_txdv_rec.id := p_tld_id;
1268 l_txdv_rec.pay_status_code := 'PROCESSED';
1269 l_txdv_rec.date_disbursed := trunc(sysdate);
1270
1271 --rkuttiya R12 B Billing Architecture commented out following
1272 /*
1273 Okl_Cnsld_Ar_Strms_Pub.update_cnsld_ar_strms
1274 (p_api_version
1275 ,p_init_msg_list
1276 ,l_return_status
1277 ,x_msg_count
1278 ,x_msg_data
1279 ,u_lsmv_rec
1280 ,r_lsmv_rec);
1281 */
1282 --rkuttiya R12 B Billing Architecture added following
1283 okl_txd_ar_ln_dtls_pub.update_txd_ar_ln_dtls
1284 (p_api_version
1285 ,p_init_msg_list
1286 ,l_return_status
1287 ,x_msg_count
1288 ,x_msg_data
1289 ,l_txdv_rec
1290 ,lx_txdv_rec);
1291 --
1292 IF (l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1293 print_line ('*=>ERROR: Updating Invoice Lines .');
1294 END IF;
1295 ELSE
1296 IF x_msg_count >= 1 THEN
1297 FOR i IN 1..x_msg_count LOOP
1298 fnd_msg_pub.get(p_msg_index=> i,
1299 p_encoded => 'F',
1300 p_data => x_msg_data,
1301 p_msg_index_out => l_msg_index_out);
1302 print_line ( x_msg_data);
1303 END LOOP;
1304
1305 END IF;
1306
1307 --rkuttiya R12 B Billing Architecture commented out following
1308 --u_lsmv_rec.id := p_lsm_id;
1309 --u_lsmv_rec.pay_status_code := 'ERROR';
1310 --rkuttiya R12 B BIlling Architecture added following
1311 l_txdv_rec.id := p_tld_id;
1312 l_txdv_rec.pay_status_code := 'ERROR';
1313 --
1314 --rkuttiya R12 B Billing Architecture commented out following
1315 /* Okl_Cnsld_Ar_Strms_Pub.update_cnsld_ar_strms
1316 (p_api_version
1317 ,p_init_msg_list
1318 ,l_return_status
1319 ,x_msg_count
1320 ,x_msg_data
1321 ,u_lsmv_rec
1322 ,r_lsmv_rec); */
1323
1324 --rkuttiya R12 B Billing Architecture added following
1325 okl_txd_ar_ln_dtls_pub.update_txd_ar_ln_dtls
1326 (p_api_version
1327 ,p_init_msg_list
1328 ,l_return_status
1329 ,x_msg_count
1330 ,x_msg_data
1331 ,l_txdv_rec
1332 ,lx_txdv_rec);
1333 --
1334 IF (l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1335 print_line ('*=>ERROR: Updating Invoice Lines.');
1336 END IF;
1337
1338 END IF;
1339
1340 print_line ( '******** EXITING PROCEDURE UPDATE_INVOICE_PAY_STATUS ********');
1341 x_return_status := l_return_status;
1342 EXCEPTION
1343 ------------------------------------------------------------
1344 -- Exception handling
1345 ------------------------------------------------------------
1346
1347 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1348
1349 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1350 p_api_name => l_api_name,
1351 p_pkg_name => G_PKG_NAME,
1352 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1353 x_msg_count => x_msg_count,
1354 x_msg_data => x_msg_data,
1355 p_api_type => '_PVT');
1356
1357 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1358
1359 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1360 p_api_name => l_api_name,
1361 p_pkg_name => G_PKG_NAME,
1362 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1363 x_msg_count => x_msg_count,
1364 x_msg_data => x_msg_data,
1365 p_api_type => '_PVT');
1366
1367 WHEN OTHERS THEN
1368
1369 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1370 p_api_name => l_api_name,
1371 p_pkg_name => G_PKG_NAME,
1372 p_exc_name => 'OTHERS',
1373 x_msg_count => x_msg_count,
1374 x_msg_data => x_msg_data,
1375 p_api_type => '_PVT');
1376 END update_invoice_pay_status;
1377
1378 ----------------------------------------------------------------------------------
1379
1380 PROCEDURE auto_disbursement_tbl(p_api_version IN NUMBER
1381 ,p_init_msg_list IN VARCHAR2
1382 ,x_return_status OUT NOCOPY VARCHAR2
1383 ,x_msg_count OUT NOCOPY NUMBER
1384 ,x_msg_data OUT NOCOPY VARCHAR2
1385 ,p_disb_tbl IN disb_tbl_type)
1386 IS
1387 ------------------------------------------------------------
1388 -- Declare Variables required by APIs
1389 ------------------------------------------------------------
1390 l_api_version CONSTANT NUMBER := 1;
1391 l_api_name CONSTANT VARCHAR2(30) := 'AUTO_DISBURSEMENT_TBL';
1392 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1393
1394 l_disb_rec disb_rec_type;
1395 l_disb_tbl disb_tbl_type;
1396
1397 ind NUMBER;
1398 BEGIN
1399 print_line ( '******** IN PROCEDURE AUTO_DISBURSEMENT_TBL ********');
1400
1401 l_return_status := okl_api.start_activity(
1402 p_api_name => l_api_name,
1403 p_init_msg_list => p_init_msg_list,
1404 p_api_type => '_PVT',
1405 x_return_status => l_return_status);
1406
1407 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1408 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1409 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1410 RAISE OKL_API.G_EXCEPTION_ERROR;
1411 END IF;
1412
1413 l_disb_tbl := p_disb_tbl;
1414 FOR ind in l_disb_tbl.first..l_disb_tbl.last LOOP
1415 G_commit_count := G_commit_count + 1;
1416
1417 l_return_status := OKL_API.G_RET_STS_SUCCESS;
1418 l_disb_rec := null_disb_rec;
1419
1420 fnd_msg_pub.initialize;
1421
1422 --SAVEPOINT C_INVOICE_POINT;
1423
1424 --rkuttiya R12 B Billing commented out cnr_id and consolidated_invoice_number
1425 --l_disb_rec.cnr_id := l_disb_tbl(ind).cnr_id;
1426 --l_disb_rec.consolidated_invoice_number := l_disb_tbl(ind).consolidated_invoice_number;
1427 --rkuttiya R12 B Billing added receivables invoice number
1428 l_disb_rec.receivables_invoice_number := l_disb_tbl(ind).receivables_invoice_number;
1429 l_disb_rec.set_of_books_id := l_disb_tbl(ind).set_of_books_id;
1430 l_disb_rec.org_id := l_disb_tbl(ind).org_id;
1431 l_disb_rec.transaction_date := l_disb_tbl(ind).transaction_date;
1432 l_disb_rec.currency_code := l_disb_tbl(ind).currency_code;
1433 l_disb_rec.khr_id := l_disb_tbl(ind).khr_id;
1434 l_disb_rec.kle_id := l_disb_tbl(ind).kle_id;
1435 l_disb_rec.amount := l_disb_tbl(ind).amount;
1436 l_disb_rec.sty_id := l_disb_tbl(ind).sty_id;
1437 --rkuttiya R12B Billing commented out lsm_id and added tld_id
1438 --l_disb_rec.lsm_id := l_disb_tbl(ind).lsm_id;
1439 l_disb_rec.tld_id := l_disb_tbl(ind).tld_id;
1440 --
1441 l_disb_rec.receivables_invoice_id := l_disb_tbl(ind).receivables_invoice_id;
1442 l_disb_rec.sel_id := l_disb_tbl(ind).sel_id;
1443 l_disb_rec.pph_id := l_disb_tbl(ind).pph_id;
1444 l_disb_rec.passthru_stream_type_id := l_disb_tbl(ind).passthru_stream_type_id;
1445 l_disb_rec.payout_basis := l_disb_tbl(ind).payout_basis;
1446 l_disb_rec.payout_basis_formula := l_disb_tbl(ind).payout_basis_formula;
1447 l_disb_rec.contract_number := l_disb_tbl(ind).contract_number;
1448 -- 01-NOV-2006 ANSETHUR R12B - Legal Entity
1449 l_disb_rec.legal_entity_id := l_disb_tbl(ind).legal_entity_id;
1450
1451 print_line ('===========================================================================================');
1452 IF (l_disb_rec.payout_basis IN ('BILLING', 'FULL_RECEIPT', 'PARTIAL_RECEIPT')) THEN
1453 print_line ('Processing Receivables Invoice Number: ' || l_disb_rec.receivables_invoice_number);
1454 ELSIF (l_disb_rec.payout_basis IN ('DUE_DATE')) THEN
1455 print_line ('Processing Stream Element: ' || l_disb_rec.sel_id);
1456 ELSE
1457 print_line ('Processing Contract Line Number: ' || l_disb_rec.kle_id);
1458 END IF;
1459 print_line ('===========================================================================================');
1460
1461 print_line (' -- set_of_books_id : '||l_disb_rec.set_of_books_id);
1462 print_line (' -- org_id : '||l_disb_rec.org_id);
1463 print_line (' -- transaction_date : '||l_disb_rec.transaction_date);
1464 print_line (' -- currency_code : '||l_disb_rec.currency_code);
1465 print_line (' -- contract : '||l_disb_tbl(ind).contract_number);
1466 print_line (' -- kle_id : '||l_disb_rec.kle_id);
1467 print_line (' -- source amount : '||l_disb_rec.amount);
1468 print_line (' -- tld_id : '||l_disb_rec.tld_id);
1469 print_line (' -- receivables_invoice_id : '||l_disb_rec.receivables_invoice_id);
1470 print_line (' -- pph_id (party payment header) : '||l_disb_rec.pph_id);
1471 print_line (' -- passthru_stream_type_id : '||l_disb_rec.passthru_stream_type_id);
1472 print_line (' -- payout_basis : '||l_disb_rec.payout_basis);
1473 print_line (' -- payout_basis_formula : '||l_disb_rec.payout_basis_formula);
1474 -- 01-NOV-2006 ANSETHUR R12B - Legal Entity
1475 print_line (' -- legal_entity_id : '||l_disb_rec.legal_entity_id);
1476
1477 ------------------------------------------------------------
1478 -- Call for Invoice Disbursement
1479 ------------------------------------------------------------
1480 invoice_disbursement (
1481 p_api_version => p_api_version,
1482 p_init_msg_list => p_init_msg_list,
1483 x_return_status => l_return_status,
1484 x_msg_count => x_msg_count,
1485 x_msg_data => x_msg_data,
1486 p_disb_rec => l_disb_rec);
1487
1488
1489 IF NVL(l_return_status, 'E') = OKL_API.G_RET_STS_SUCCESS THEN
1490 print_line ('=====> Successfully Proccesed Passthrough Transaction');
1491 ELSE
1492 print_line ('*=>ERROR: Processing Passthrough Transaction');
1493 END IF;
1494
1495 IF (l_disb_rec.payout_basis IN ('BILLING', 'FULL_RECEIPT', 'PARTIAL_RECEIPT')) THEN
1496 --update consolidated stream with status of passthru transaction creation
1497 update_invoice_pay_status(
1498 p_api_version => p_api_version,
1499 p_init_msg_list => p_init_msg_list,
1500 x_return_status => x_return_status,
1501 x_msg_count => x_msg_count,
1502 x_msg_data => x_msg_data,
1503 p_tld_id => l_disb_rec.tld_id,
1504 p_status => l_return_status);
1505
1506 IF NVL(x_return_status, 'E') = OKL_API.G_RET_STS_SUCCESS THEN
1507 print_line ('=====> Successfully Updated Consolidated Invoice Stream : pay status code');
1508 ELSE
1509 print_line ('*=>ERROR: Updating Consolidated Invoice Stream : pay status code');
1510 END IF;
1511 ELSIF (l_disb_rec.payout_basis IN ('DUE_DATE')) THEN
1512 --update stream element with status of passthru transaction creation
1513 IF NVL(l_return_status, 'E') = OKL_API.G_RET_STS_SUCCESS THEN
1514 update okl_strm_elements
1515 set date_disbursed = trunc(sysdate)
1516 ,object_version_number = object_version_number + 1
1517 ,last_updated_by = FND_GLOBAL.USER_ID
1518 ,last_update_date = sysdate
1519 ,last_update_login = FND_GLOBAL.LOGIN_ID
1520 ,request_id = NVL(DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),null)
1521 where id = l_disb_rec.sel_id;
1522 END IF;
1523 END IF;
1524
1525 print_line ('===========================================================================================');
1526 IF (l_disb_rec.payout_basis IN ('BILLING', 'FULL_RECEIPT', 'PARTIAL_RECEIPT')) THEN
1527 print_line ('Done Processing Receivables Invoice Number: ' || l_disb_rec.receivables_invoice_number);
1528 ELSIF (l_disb_rec.payout_basis IN ('DUE_DATE')) THEN
1529 print_line ('Done Processing Stream Element: ' || l_disb_rec.sel_id);
1530 ELSE
1531 print_line ('Done Processing Contract Line Number: ' || l_disb_rec.kle_id);
1532 END IF;
1533 print_line ('===========================================================================================');
1534
1535 --Start code added by pgomes on 02/12/2003
1536 IF (MOD(G_commit_count, G_commit_after_records) = 0) THEN
1537 COMMIT;
1538 print_line ('===========================================================================================');
1539 print_line ('Done committing after processing ' || G_commit_count || ' transactions.');
1540 print_line ('===========================================================================================');
1541 END IF;
1542 END LOOP;
1543
1544 print_line ( '******** EXITING PROCEDURE AUTO_DISBURSEMENT_TBL ********');
1545
1546 okl_api.end_activity(x_msg_count => x_msg_count
1547 ,x_msg_data => x_msg_data);
1548 EXCEPTION
1549 ------------------------------------------------------------
1550 -- Exception handling
1551 ------------------------------------------------------------
1552
1553 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1554
1555 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1556 p_api_name => l_api_name,
1557 p_pkg_name => G_PKG_NAME,
1558 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1559 x_msg_count => x_msg_count,
1560 x_msg_data => x_msg_data,
1561 p_api_type => '_PVT');
1562
1563 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1564
1565 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1566 p_api_name => l_api_name,
1567 p_pkg_name => G_PKG_NAME,
1568 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1569 x_msg_count => x_msg_count,
1570 x_msg_data => x_msg_data,
1571 p_api_type => '_PVT');
1572
1573 WHEN OTHERS THEN
1574
1575 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1576 p_api_name => l_api_name,
1577 p_pkg_name => G_PKG_NAME,
1578 p_exc_name => 'OTHERS',
1579 x_msg_count => x_msg_count,
1580 x_msg_data => x_msg_data,
1581 p_api_type => '_PVT');
1582
1583 END auto_disbursement_tbl;
1584
1585 ----------------------------------------------------------------------------------
1586
1587 PROCEDURE auto_disbursement(p_api_version IN NUMBER
1588 ,p_init_msg_list IN VARCHAR2
1589 ,x_return_status OUT NOCOPY VARCHAR2
1590 ,x_msg_count OUT NOCOPY NUMBER
1591 ,x_msg_data OUT NOCOPY VARCHAR2
1592 ,p_from_date IN DATE
1593 ,p_to_date IN DATE
1594 ,p_contract_number IN VARCHAR2)
1595 IS
1596 ------------------------------------------------------------
1597 -- Declare Variables required by APIs
1598 ------------------------------------------------------------
1599 l_api_version CONSTANT NUMBER := 1;
1600 l_api_name CONSTANT VARCHAR2(30) := 'AUTO_DISBURSEMENT';
1601 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1602
1603 --------------------------------------------------------------------
1604 --Declare Cursor: Receivable Invoices eligible for disbursement with
1605 --payout_basis = 'BILLING' ,passthru_term = 'BASE', 'EVERGREEN'
1606 --for top lines, sub lines
1607 --------------------------------------------------------------------
1608 --02/26/07 rkuttiya modified following cursor for R12 B Billing Architecture
1609 --replaced data elements, tables, and modified where clause
1610 CURSOR c_invoice_bill(p_from_date DATE, p_to_date DATE, p_contract_number VARCHAR2)
1611 IS
1612 SELECT
1613 NULL cnr_id
1614 ,arl.receivables_invoice_number
1615 ,tai.set_of_books_id
1616 ,arv.org_id
1617 ,arv.date_consolidated
1618 ,arv.currency_code
1619 ,tai.khr_id
1620 ,til.kle_id
1621 ,arl.amount
1622 ,arl.sty_id
1623 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
1624 -- ,arl.id lsm_id
1625 ,tld.id tld_id
1626 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
1627 ,arl.receivables_invoice_id
1628 ,null sel_id
1629 ,pph.id pph_id
1630 ,NVL(pph.passthru_stream_type_id, arl.sty_id) passthru_stream_type_id
1631 ,pph.payout_basis
1632 ,null payout_basis_formula
1633 ,khr.contract_number
1634 -- 30-OCT-2006 ANSETHUR R12B - Legal Entity
1635 ,tai.legal_entity_id
1636 FROM okc_k_headers_b khr
1637 ,okc_k_lines_b kle
1638 --added for evergreen change request 08_nov_2005
1639 ,okc_line_styles_b lse
1640 ,okl_bpd_ar_inv_lines_v arl
1641 ,okl_bpd_ar_invoices_v arv
1642 ,okl_trx_ar_invoices_v tai
1643 ,okl_txl_ar_inv_lns_v til
1644 ,okl_txd_ar_ln_dtls_b tld
1645 ,okl_party_payment_hdr pph
1646 WHERE khr.contract_number = NVL(p_contract_number, khr.contract_number)
1647 AND khr.id = kle.dnz_chr_id
1648 AND kle.id = til.kle_id
1649 AND til.id = tld.til_id_details
1650 AND arl.receivables_invoice_id > 0
1651 --rkuttiya commented and changed for Billing Architecture
1652 --AND (lsm.pay_status_code is NULL OR lsm.pay_status_code = 'ERROR')
1653 AND (tld.pay_status_code IS NULL OR tld.pay_status_code = 'ERROR')
1654 --start fix for bug 5040815 by pgomes 24-mar-2006
1655 AND arl.sty_id NOT IN (SELECT id FROM okl_strm_type_v
1656 WHERE stream_type_purpose in ('LATE_FEE', 'LATE_INTEREST'))
1657 --end fix for bug 5040815 by pgomes 24-mar-2006
1658 --rkuttiya R12 B Billing Architecture commented out the following
1659 --AND lsm.lln_id = lln.id
1660 --AND lln.cnr_id = cnr.id
1661 --AND cnr.trx_status_code = 'PROCESSED'
1662 --
1663 --rkuttiya R12 B BIlling added the following
1664 AND til.tai_id = tai.id
1665 AND til.id = arl.til_id_details
1666 AND tai.trx_status_code = 'PROCESSED'
1667 AND arv.invoice_id = arl.receivables_invoice_id
1668 --
1669 --added for evergreen change request 08_nov_2005
1670 AND kle.lse_id = lse.id
1671 --commented for evergreen change request 08_nov_2005
1672 --AND kle.id = pph.cle_id
1673 AND kle.dnz_chr_id = pph.dnz_chr_id
1674 AND pph.payout_basis = 'BILLING'
1675 AND trunc(arv.date_consolidated) >= trunc(NVL(pph.passthru_start_date, arv.date_consolidated))
1676 AND (trunc(arv.date_consolidated) <= trunc(kle.end_date) AND pph.passthru_term = 'BASE'
1677 --added for evergreen change request 08_nov_2005
1678 AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'BASE') = pph.id
1679 OR
1680 trunc(arv.date_consolidated) > trunc(kle.end_date) AND pph.passthru_term = 'EVERGREEN'
1681 --added for evergreen change request 08_nov_2005
1682 AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'EVERGREEN') = pph.id)
1683 AND trunc(arv.date_consolidated) BETWEEN NVL (TRUNC(p_from_date), SYSDATE-10000)
1684 AND NVL (TRUNC(p_to_date), SYSDATE+10000)
1685 UNION ALL
1686 SELECT
1687 NULL cnr_id
1688 ,arl.receivables_invoice_number
1689 ,tai.set_of_books_id
1690 ,arv.org_id
1691 ,arv.date_consolidated
1692 ,arv.currency_code
1693 ,tai.khr_id
1694 ,til.kle_id
1695 ,arl.amount
1696 ,arl.sty_id
1697 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
1698 -- ,arl.id lsm_id
1699 ,tld.id tld_id
1700 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
1701 ,arl.receivables_invoice_id
1702 ,null sel_id
1703 ,pph.id pph_id
1704 ,NVL(pph.passthru_stream_type_id, arl.sty_id) passthru_stream_type_id
1705 ,pph.payout_basis
1706 ,null payout_basis_formula
1707 ,khr.contract_number
1708 -- 01-NOV-2006 ANSETHUR R12B - Legal Entity
1709 ,tai.legal_entity_id
1710 FROM okc_k_headers_b khr
1711 ,okc_k_lines_b kle
1712 ,okc_k_lines_b sub_kle
1713 --added for evergreen change request 08_nov_2005
1714 ,okc_line_styles_b lse
1715 ,okl_bpd_ar_inv_lines_v arl
1716 ,okl_bpd_ar_invoices_v arv
1717 ,okl_trx_ar_invoices_v tai
1718 ,okl_txl_ar_inv_lns_v til
1719 ,okl_txd_ar_ln_dtls_b tld
1720 ,okl_party_payment_hdr pph
1721 WHERE khr.contract_number = NVL(p_contract_number, khr.contract_number)
1722 AND khr.id = sub_kle.dnz_chr_id
1723 AND sub_kle.chr_id is null
1724 --rkuttiya commented for R12 B Billing Architecture
1725 -- AND sub_kle.id = lsm.kle_id
1726 AND sub_kle.id = til.kle_id
1727 AND til.id = tld.til_id_details
1728 --
1729 AND arl.receivables_invoice_id > 0
1730 AND (tld.pay_status_code is NULL OR tld.pay_status_code = 'ERROR')
1731 --start fix for bug 5040815 by pgomes 24-mar-2006
1732 AND arl.sty_id NOT IN (SELECT id FROM okl_strm_type_v
1733 WHERE stream_type_purpose in ('LATE_FEE', 'LATE_INTEREST'))
1734 --end fix for bug 5040815 by pgomes 24-mar-2006
1735 --rkuttiya commented following for R12 B Billing Architecture
1736 --AND lsm.lln_id = lln.id
1737 -- AND lln.cnr_id = cnr.id
1738 --AND cnr.trx_status_code = 'PROCESSED'
1739 --rkuttiya added for R12 B Billing Architecture
1740 AND til.tai_id = tai.id
1741 AND til.id = arl.til_id_details
1742 AND arv.invoice_id = arl.receivables_invoice_id
1743 AND tai.trx_status_code = 'PROCESSED'
1744 --
1745 AND khr.id = kle.chr_id
1746 AND sub_kle.cle_id = kle.id
1747 --added for evergreen change request 08_nov_2005
1748 AND kle.lse_id = lse.id
1749 --commented for evergreen change request 08_nov_2005
1750 --AND kle.id = pph.cle_id
1751 AND kle.dnz_chr_id = pph.dnz_chr_id
1752 AND pph.payout_basis = 'BILLING'
1753 --rkuttiya R12 B Billing Architecture replaced arv.date_consolidated by arv.date_consolidated
1754 AND trunc(arv.date_consolidated) >= trunc(NVL(pph.passthru_start_date, arv.date_consolidated))
1755 AND (trunc(arv.date_consolidated) <= trunc(kle.end_date) AND pph.passthru_term = 'BASE'
1756 --added for evergreen change request 08_nov_2005
1757 AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'BASE') = pph.id
1758 OR
1759 trunc(arv.date_consolidated) > trunc(kle.end_date) AND pph.passthru_term = 'EVERGREEN'
1760 --added for evergreen change request 08_nov_2005
1761 AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'EVERGREEN') = pph.id)
1762 AND trunc(arv.date_consolidated) BETWEEN NVL (TRUNC(p_from_date), SYSDATE-10000)
1763 AND NVL (TRUNC(p_to_date), SYSDATE+10000);
1764 --rkuttiya R12 B Billing Architecture end changes for this cursor
1765
1766
1767
1768 --------------------------------------------------------------------
1769 --Declare Cursor: Receivable Invoices eligible for disbursement with
1770 --payout_basis = 'FULL_RECEIPT' ,passthru_term = 'BASE', 'EVERGREEN'
1771 --for top lines, sub lines
1772 --------------------------------------------------------------------
1773 --rkuttiya R12 B Billing modified this cursor data elements, tables, where clause
1774 CURSOR c_invoice_full_rcpt(p_from_date DATE, p_to_date DATE, p_contract_number VARCHAR2)
1775 IS
1776 SELECT
1777 NULL cnr_id
1778 ,arl.receivables_invoice_number
1779 ,tai.set_of_books_id
1780 ,arv.org_id
1781 ,okl_pay_invoices_disb_pvt.receipt_date(arl.receivables_invoice_id) receipt_date
1782 ,arv.currency_code
1783 ,tai.khr_id
1784 ,til.kle_id
1785 -- ,okl_pay_invoices_disb_pvt.receipt_amount(arl.receivables_invoice_id) amount --21-Jan-2011 sechawla 10282607
1786 ,okl_pay_invoices_disb_pvt.receipt_amount(arl.receivables_invoice_id,arl.receivables_invoice_line_id) amount --21-Jan-2011 sechawla 10282607
1787 ,arl.sty_id
1788 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
1789 -- ,arl.id lsm_id
1790 ,tld.id tld_id
1791 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
1792 ,arl.receivables_invoice_id
1793 ,null sel_id
1794 ,pph.id pph_id
1795 ,NVL(pph.passthru_stream_type_id, arl.sty_id) passthru_stream_type_id
1796 ,pph.payout_basis
1797 ,null payout_basis_formula
1798 ,khr.contract_number
1799 -- 01-NOV-2006 ANSETHUR R12B - Legal Entity
1800 ,tai.legal_entity_id
1801 FROM okc_k_headers_b khr
1802 ,okc_k_lines_b kle
1803 --added for evergreen change request 08_nov_2005
1804 ,okc_line_styles_b lse
1805 ,okl_bpd_ar_inv_lines_v arl
1806 ,okl_bpd_ar_invoices_v arv
1807 ,okl_trx_ar_invoices_v tai
1808 ,okl_txl_ar_inv_lns_v til
1809 ,okl_txd_ar_ln_dtls_b tld
1810 ,okl_party_payment_hdr pph
1811 ,ar_payment_schedules_all aps
1812 WHERE khr.contract_number = NVL(p_contract_number, khr.contract_number)
1813 AND khr.id = kle.dnz_chr_id
1814 AND kle.id = til.kle_id
1815 AND arl.receivables_invoice_id > 0
1816 --rkuttiya R12 B Billing Architecture commented the following code and added code replacing lsm by arl
1817 --AND (lsm.pay_status_code is NULL OR lsm.pay_status_code = 'ERROR')
1818 AND til.id = tld.til_id_details
1819 AND (tld.pay_status_code IS NULL OR tld.pay_status_code = 'ERROR')
1820 --
1821 --start fix for bug 5040815 by pgomes 24-mar-2006
1822 AND arl.sty_id NOT IN (SELECT id FROM okl_strm_type_v
1823 WHERE stream_type_purpose in ('LATE_FEE', 'LATE_INTEREST'))
1824 --end fix for bug 5040815 by pgomes 24-mar-2006
1825 --rkuttiya R12 B Billing Architecture commented out the following
1826 --AND lsm.lln_id = lln.id
1827 --AND lln.cnr_id = cnr.id
1828 --AND cnr.trx_status_code = 'PROCESSED'
1829 --rkuttiya added for Billing Architecture
1830 AND til.tai_id = tai.id
1831 AND til.id = arl.til_id_details
1832 AND tai.trx_status_code = 'PROCESSED'
1833 AND arv.invoice_id = arl.receivables_invoice_id
1834 --
1835 --added for evergreen change request 08_nov_2005
1836 AND kle.lse_id = lse.id
1837 --commented for evergreen change request 08_nov_2005
1838 --AND kle.id = pph.cle_id
1839 AND kle.dnz_chr_id = pph.dnz_chr_id
1840 AND pph.payout_basis = 'FULL_RECEIPT'
1841 AND trunc(arv.date_consolidated) >= trunc(NVL(pph.passthru_start_date, arv.date_consolidated))
1842 AND (trunc(arv.date_consolidated) <= trunc(kle.end_date) AND pph.passthru_term = 'BASE'
1843 --added for evergreen change request 08_nov_2005
1844 AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'BASE') = pph.id
1845 OR
1846 trunc(arv.date_consolidated) > trunc(kle.end_date) AND pph.passthru_term = 'EVERGREEN'
1847 --added for evergreen change request 08_nov_2005
1848 AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'EVERGREEN') = pph.id)
1849 AND arl.receivables_invoice_id = aps.customer_trx_id
1850 AND aps.class = 'INV'
1851 AND aps.status = 'CL'
1852 AND trunc(arv.date_consolidated) BETWEEN NVL (TRUNC(p_from_date), SYSDATE-10000)
1853 AND NVL (TRUNC(p_to_date), SYSDATE+10000)
1854 UNION ALL
1855 SELECT
1856 NULL cnr_id
1857 ,arl.receivables_invoice_number
1858 ,tai.set_of_books_id
1859 ,arv.org_id
1860 ,okl_pay_invoices_disb_pvt.receipt_date(arl.receivables_invoice_id) receipt_date
1861 ,arv.currency_code
1862 ,tai.khr_id
1863 ,til.kle_id
1864 -- ,okl_pay_invoices_disb_pvt.receipt_amount(arl.receivables_invoice_id) amount --21-Jan-2011 sechawla 10282607
1865 , okl_pay_invoices_disb_pvt.receipt_amount(arl.receivables_invoice_id,arl.receivables_invoice_line_id) amount --21-Jan-2011 sechawla 10282607
1866
1867 ,arl.sty_id
1868 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
1869 -- ,arl.id lsm_id
1870 ,tld.id tld_id
1871 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
1872 ,arl.receivables_invoice_id
1873 ,null sel_id
1874 ,pph.id pph_id
1875 ,NVL(pph.passthru_stream_type_id, arl.sty_id) passthru_stream_type_id
1876 ,pph.payout_basis
1877 ,null payout_basis_formula
1878 ,khr.contract_number
1879 -- 01-NOV-2006 ANSETHUR R12B - Legal Entity
1880 ,tai.legal_entity_id
1881 FROM okc_k_headers_b khr
1882 ,okc_k_lines_b kle
1883 ,okc_k_lines_b sub_kle
1884 --added for evergreen change request 08_nov_2005
1885 ,okc_line_styles_b lse
1886 ,okl_bpd_ar_inv_lines_v arl
1887 ,okl_bpd_ar_invoices_v arv
1888 ,okl_trx_ar_invoices_v tai
1889 ,okl_txl_ar_inv_lns_v til
1890 ,okl_txd_ar_ln_dtls_b tld
1891 ,okl_party_payment_hdr pph
1892 ,ar_payment_schedules_all aps
1893 WHERE khr.contract_number = NVL(p_contract_number, khr.contract_number)
1894 AND khr.id = sub_kle.dnz_chr_id
1895 AND sub_kle.chr_id is null
1896 --rkuttiya commented for R12 B Billing Architecture
1897 --AND sub_kle.id = lsm.kle_id
1898 --rkuttiya R12 Billing Architecture
1899 AND sub_kle.id = til.kle_id
1900 AND til.id = tld.til_id_details -- 14-Jan-2011 sechawla 10279063 : added join condition
1901 AND arl.receivables_invoice_id > 0
1902 --rkuttiya commented for R12 B Billing Architecture
1903 --AND (lsm.pay_status_code is NULL OR lsm.pay_status_code = 'ERROR')
1904 --rkuttiya added R12B Billing Architecture
1905 AND (tld.pay_status_code IS NULL OR tld.pay_status_code = 'ERROR')
1906 --start fix for bug 5040815 by pgomes 24-mar-2006
1907 AND arl.sty_id NOT IN (SELECT id FROM okl_strm_type_v
1908 WHERE stream_type_purpose in ('LATE_FEE', 'LATE_INTEREST'))
1909 --end fix for bug 5040815 by pgomes 24-mar-2006
1910 --rkuttiya commented for R12 B Billing Architecture
1911 --AND lsm.lln_id = lln.id
1912 --AND lln.cnr_id = cnr.id
1913 --AND cnr.trx_status_code = 'PROCESSED'
1914 --rkuttiya added for R12 B Billing Architecture
1915 AND til.tai_id = tai.id
1916 AND til.id = arl.til_id_details
1917 AND tai.trx_status_code = 'PROCESSED'
1918 AND arv.invoice_id = arl.receivables_invoice_id
1919 --
1920 AND khr.id = kle.chr_id
1921 AND sub_kle.cle_id = kle.id
1922 --added for evergreen change request 08_nov_2005
1923 AND kle.lse_id = lse.id
1924 --commented for evergreen change request 08_nov_2005
1925 --AND kle.id = pph.cle_id
1926 AND kle.dnz_chr_id = pph.dnz_chr_id
1927 AND pph.payout_basis = 'FULL_RECEIPT'
1928 AND trunc(arv.date_consolidated) >= trunc(NVL(pph.passthru_start_date, arv.date_consolidated))
1929 AND (trunc(arv.date_consolidated) <= trunc(kle.end_date) AND pph.passthru_term = 'BASE'
1930 --added for evergreen change request 08_nov_2005
1931 AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'BASE') = pph.id
1932 OR
1933 trunc(arv.date_consolidated) > trunc(kle.end_date) AND pph.passthru_term = 'EVERGREEN'
1934 --added for evergreen change request 08_nov_2005
1935 AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'EVERGREEN') = pph.id)
1936 AND arl.receivables_invoice_id = aps.customer_trx_id
1937 AND aps.class = 'INV'
1938 AND aps.status = 'CL'
1939 AND trunc(arv.date_consolidated) BETWEEN NVL (TRUNC(p_from_date), SYSDATE-10000)
1940 AND NVL (TRUNC(p_to_date), SYSDATE+10000);
1941
1942
1943 --------------------------------------------------------------------
1944 --Declare Cursor: Receivable Invoices eligible for disbursement with
1945 --payout_basis = 'PARTIAL_RECEIPT' ,passthru_term = 'BASE', 'EVERGREEN'
1946 --for top lines, sub lines
1947 --------------------------------------------------------------------
1948 CURSOR c_invoice_part_rcpt(p_from_date DATE, p_to_date DATE, p_contract_number VARCHAR2)
1949 IS
1950 SELECT
1951 NULL cnr_id
1952 ,arl.receivables_invoice_number
1953 ,tai.set_of_books_id
1954 ,arv.org_id
1955 ,okl_pay_invoices_disb_pvt.partial_receipt_date(arl.receivables_invoice_id) partial_receipt_date
1956 ,arv.currency_code
1957 ,tai.khr_id
1958 ,til.kle_id
1959 ,okl_pay_invoices_disb_pvt.partial_receipt_amount(arl.receivables_invoice_id) amount
1960 ,arl.sty_id
1961 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
1962 -- ,arl.id lsm_id
1963 ,tld.id tld_id
1964 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
1965 ,arl.receivables_invoice_id
1966 ,null sel_id
1967 ,pph.id pph_id
1968 ,NVL(pph.passthru_stream_type_id, arl.sty_id) passthru_stream_type_id
1969 ,pph.payout_basis
1970 ,null payout_basis_formula
1971 ,khr.contract_number
1972 -- 01-NOV-2006 ANSETHUR R12B - Legal Entity
1973 ,tai.legal_entity_id
1974 FROM okc_k_headers_b khr
1975 ,okc_k_lines_b kle
1976 --added for evergreen change request 08_nov_2005
1977 ,okc_line_styles_b lse
1978 ,okl_bpd_ar_inv_lines_v arl
1979 ,okl_bpd_ar_invoices_v arv
1980 ,okl_trx_ar_invoices_v tai
1981 ,okl_txl_ar_inv_lns_v til
1982 -- 05/31/07 ansethur added for R12B Billing architecture Passthrough impacts starts
1983 ,okl_txd_ar_ln_dtls_b tld
1984 -- 05/31/07 ansethur added for R12B Billing architecture Passthrough impacts ends
1985 ,okl_party_payment_hdr pph
1986 ,ar_payment_schedules_all aps
1987 WHERE khr.contract_number = NVL(p_contract_number, khr.contract_number)
1988 AND khr.id = kle.dnz_chr_id
1989 AND kle.id = til.kle_id
1990 AND arl.receivables_invoice_id > 0
1991 --AND (lsm.pay_status_code is NULL OR lsm.pay_status_code = 'ERROR')
1992 --start fix for bug 5040815 by pgomes 24-mar-2006
1993 AND arl.sty_id NOT IN (SELECT id FROM okl_strm_type_v
1994 WHERE stream_type_purpose in ('LATE_FEE', 'LATE_INTEREST'))
1995 --end fix for bug 5040815 by pgomes 24-mar-2006
1996 --rkuttiya R12 B Billing Architecture commented the following
1997 --AND lsm.lln_id = lln.id
1998 --AND lln.cnr_id = cnr.id
1999 --AND cnr.trx_status_code = 'PROCESSED'
2000 AND til.tai_id = tai.id
2001 AND til.id = arl.til_id_details
2002 AND tai.trx_status_code = 'PROCESSED'
2003 -- 05/31/07 ansethur added for R12B Billing architecture Passthrough impacts starts
2004 AND til.id = tld.til_id_details
2005 AND (tld.pay_status_code IS NULL OR tld.pay_status_code = 'ERROR')
2006 -- 05/31/07 ansethur added for R12B Billing architecture Passthrough impacts ends
2007 AND arv.invoice_id = arl.receivables_invoice_id
2008 --added for evergreen change request 08_nov_2005
2009 AND kle.lse_id = lse.id
2010 --commented for evergreen change request 08_nov_2005
2011 --AND kle.id = pph.cle_id
2012 AND kle.dnz_chr_id = pph.dnz_chr_id
2013 AND pph.payout_basis = 'PARTIAL_RECEIPT'
2014 AND okl_pay_invoices_disb_pvt.partial_receipt_amount(arl.receivables_invoice_id) <> 0
2015 AND trunc(arv.date_consolidated) >= trunc(NVL(pph.passthru_start_date, arv.date_consolidated))
2016 AND (trunc(arv.date_consolidated) <= trunc(kle.end_date) AND pph.passthru_term = 'BASE'
2017 --added for evergreen change request 08_nov_2005
2018 AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'BASE') = pph.id
2019 OR
2020 trunc(arv.date_consolidated) > trunc(kle.end_date) AND pph.passthru_term = 'EVERGREEN'
2021 --added for evergreen change request 08_nov_2005
2022 AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'EVERGREEN') = pph.id)
2023 AND arl.receivables_invoice_id = aps.customer_trx_id
2024 AND aps.class = 'INV'
2025 AND trunc(arv.date_consolidated) BETWEEN NVL (TRUNC(p_from_date), SYSDATE-10000)
2026 AND NVL (TRUNC(p_to_date), SYSDATE+10000)
2027 UNION ALL
2028 SELECT
2029 NULL cnr_id
2030 ,arl.receivables_invoice_number
2031 ,tai.set_of_books_id
2032 ,arv.org_id
2033 ,okl_pay_invoices_disb_pvt.partial_receipt_date(arl.receivables_invoice_id) partial_receipt_date
2034 ,arv.currency_code
2035 ,tai.khr_id
2036 ,til.kle_id
2037 ,okl_pay_invoices_disb_pvt.partial_receipt_amount(arl.receivables_invoice_id) amount
2038 ,arl.sty_id
2039 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
2040 -- ,arl.id lsm_id
2041 ,tld.id tld_id
2042 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
2043 ,arl.receivables_invoice_id
2044 , null sel_id
2045 ,pph.id pph_id
2046 ,NVL(pph.passthru_stream_type_id, arl.sty_id) passthru_stream_type_id
2047 ,pph.payout_basis
2048 ,null payout_basis_formula
2049 ,khr.contract_number
2050 -- 01-NOV-2006 ANSETHUR R12B - Legal Entity
2051 ,tai.legal_entity_id
2052 FROM okc_k_headers_b khr
2053 ,okc_k_lines_b kle
2054 ,okc_k_lines_b sub_kle
2055 --added for evergreen change request 08_nov_2005
2056 ,okc_line_styles_b lse
2057 ,okl_bpd_ar_inv_lines_v arl
2058 ,okl_bpd_ar_invoices_v arv
2059 ,okl_trx_ar_invoices_v tai
2060 ,okl_txl_ar_inv_lns_v til
2061 -- 05/31/07 ansethur added for R12B Billing architecture Passthrough impacts starts
2062 ,okl_txd_ar_ln_dtls_b tld
2063 -- 05/31/07 ansethur added for R12B Billing architecture Passthrough impacts ends
2064 ,okl_party_payment_hdr pph
2065 ,ar_payment_schedules_all aps
2066 WHERE khr.contract_number = NVL(p_contract_number, khr.contract_number)
2067 AND khr.id = sub_kle.dnz_chr_id
2068 AND sub_kle.chr_id is null
2069 AND sub_kle.id = til.kle_id
2070 AND arl.receivables_invoice_id > 0
2071 --start fix for bug 5040815 by pgomes 24-mar-2006
2072 AND arl.sty_id NOT IN (SELECT id FROM okl_strm_type_v
2073 WHERE stream_type_purpose in ('LATE_FEE', 'LATE_INTEREST'))
2074 --end fix for bug 5040815 by pgomes 24-mar-2006
2075 --AND (lsm.pay_status_code is NULL OR lsm.pay_status_code = 'ERROR')
2076 --rkuttiya R12 B Billing Architecture commented out following
2077 --AND lsm.lln_id = lln.id
2078 --AND lln.cnr_id = cnr.id
2079 --AND cnr.trx_status_code = 'PROCESSED'
2080 --rkuttiya R12 B BIlling Architecture added following
2081 AND til.tai_id = tai.id
2082 AND til.id = arl.til_id_details
2083 AND tai.trx_status_code = 'PROCESSED'
2084 -- 05/31/07 ansethur added for R12B Billing architecture Passthrough impacts starts
2085 AND til.id = tld.til_id_details
2086 AND (tld.pay_status_code IS NULL OR tld.pay_status_code = 'ERROR')
2087 -- 05/31/07 ansethur added for R12B Billing architecture Passthrough impacts ends
2088 AND arv.invoice_id = arl.receivables_invoice_id
2089 --
2090 AND khr.id = kle.chr_id
2091 AND sub_kle.cle_id = kle.id
2092 --added for evergreen change request 08_nov_2005
2093 AND kle.lse_id = lse.id
2094 --commented for evergreen change request 08_nov_2005
2095 --AND kle.id = pph.cle_id
2096 AND kle.dnz_chr_id = pph.dnz_chr_id
2097 AND pph.payout_basis = 'PARTIAL_RECEIPT'
2098 AND okl_pay_invoices_disb_pvt.partial_receipt_amount(arl.receivables_invoice_id) <> 0
2099 AND trunc(arv.date_consolidated) >= trunc(NVL(pph.passthru_start_date, arv.date_consolidated))
2100 AND (trunc(arv.date_consolidated) <= trunc(kle.end_date) AND pph.passthru_term = 'BASE'
2101 --added for evergreen change request 08_nov_2005
2102 AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'BASE') = pph.id
2103 OR
2104 trunc(arv.date_consolidated) > trunc(kle.end_date) AND pph.passthru_term = 'EVERGREEN'
2105 --added for evergreen change request 08_nov_2005
2106 AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'EVERGREEN') = pph.id)
2107 AND arl.receivables_invoice_id = aps.customer_trx_id
2108 AND aps.class = 'INV'
2109 AND trunc(arv.date_consolidated) BETWEEN NVL (TRUNC(p_from_date), SYSDATE-10000)
2110 AND NVL (TRUNC(p_to_date), SYSDATE+10000);
2111
2112 --------------------------------------------------------------------
2113 --Declare Cursor: Stream elements eligible for disbursement with
2114 --payout_basis = 'DUE DATE' ,passthru_term = 'BASE'
2115 --for top lines, sub lines
2116 --------------------------------------------------------------------
2117 CURSOR c_pay_sel(p_from_date DATE, p_to_date DATE, p_contract_number VARCHAR2)
2118 IS
2119 SELECT null cnr_id,
2120 null consolidated_invoice_number,
2121 hou.set_of_books_id,
2122 khr.authoring_org_id org_id,
2123 ste.stream_element_date,
2124 khr.currency_code currency_code,
2125 stm.khr_id khr_id,
2126 stm.kle_id kle_id,
2127 ste.amount amount,
2128 stm.sty_id sty_id,
2129 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
2130 -- null lsm_id,
2131 null tld_id,
2132 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
2133 null receivables_invoice_id,
2134 ste.id sel_id,
2135 pph.id pph_id,
2136 NVL(pph.passthru_stream_type_id, stm.sty_id) passthru_stream_type_id,
2137 pph.payout_basis,
2138 null payout_basis_formula,
2139 khr.contract_number,
2140 -- 01-NOV-2006 ANSETHUR R12B - Legal Entity
2141 khl.legal_entity_id
2142 FROM okl_strm_elements ste,
2143 okl_streams stm,
2144 okl_strm_type_v sty,
2145 okc_k_headers_b khr,
2146 okl_k_headers khl,
2147 hr_operating_units hou,
2148 okc_k_lines_b kle,
2149 --added for evergreen change request 08_nov_2005
2150 okc_line_styles_b lse,
2151 okc_statuses_b khs,
2152 okc_statuses_b kls,
2153 okl_party_payment_hdr pph
2154 WHERE trunc(ste.stream_element_date) >=
2155 trunc(NVL (p_from_date, ste.stream_element_date))
2156 AND trunc(ste.stream_element_date) <=
2157 trunc((NVL (p_to_date, SYSDATE) ))
2158 AND ste.amount <> 0
2159 AND stm.id = ste.stm_id
2160 AND ste.date_disbursed IS NULL
2161 AND stm.active_yn = 'Y'
2162 AND stm.say_code = 'CURR'
2163 --start fix for bug 5040815 by pgomes 24-mar-2006
2164 AND stm.sty_id NOT IN (SELECT id FROM okl_strm_type_v
2165 WHERE stream_type_purpose in ('LATE_FEE', 'LATE_INTEREST'))
2166 --end fix for bug 5040815 by pgomes 24-mar-2006
2167 AND sty.id = stm.sty_id
2168 AND sty.billable_yn = 'Y'
2169 AND khr.id = stm.khr_id
2170 AND khr.scs_code IN ('LEASE', 'LOAN')
2171 AND khr.sts_code IN ( 'BOOKED','TERMINATED')
2172 AND khr.authoring_org_id = hou.organization_id
2173 AND khr.contract_number = NVL(p_contract_number, khr.contract_number)
2174 AND khl.id = stm.khr_id
2175 AND khl.deal_type IS NOT NULL
2176 AND khs.code = khr.sts_code
2177 AND kle.id = stm.kle_id
2178 AND kle.sts_code = kls.code
2179 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
2180 --added for evergreen change request 08_nov_2005
2181 AND kle.lse_id = lse.id
2182 --commented for evergreen change request 08_nov_2005
2183 --AND kle.id = pph.cle_id
2184 AND kle.dnz_chr_id = pph.dnz_chr_id
2185 --added for evergreen change request 08_nov_2005
2186 AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'BASE') = pph.id
2187 AND pph.payout_basis = 'DUE_DATE'
2188 AND trunc(ste.stream_element_date) >= trunc(NVL(pph.passthru_start_date, ste.stream_element_date))
2189 AND trunc(ste.stream_element_date) <= trunc(kle.end_date)
2190 AND pph.passthru_term = 'BASE'
2191 UNION ALL
2192 SELECT null cnr_id,
2193 null consolidated_invoice_number,
2194 hou.set_of_books_id,
2195 khr.authoring_org_id org_id,
2196 ste.stream_element_date,
2197 khr.currency_code currency_code,
2198 stm.khr_id khr_id,
2199 stm.kle_id kle_id,
2200 ste.amount amount,
2201 stm.sty_id sty_id,
2202 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
2203 -- null lsm_id,
2204 null tld_id,
2205 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
2206 null receivables_invoice_id,
2207 ste.id sel_id,
2208 pph.id pph_id,
2209 NVL(pph.passthru_stream_type_id, stm.sty_id) passthru_stream_type_id,
2210 pph.payout_basis,
2211 null payout_basis_formula,
2212 khr.contract_number,
2213 -- 01-NOV-2006 ANSETHUR R12B - Legal Entity
2214 khl.legal_entity_id
2215 FROM okl_strm_elements ste,
2216 okl_streams stm,
2217 okl_strm_type_v sty,
2218 okc_k_headers_b khr,
2219 okl_k_headers khl,
2220 hr_operating_units hou,
2221 okc_k_lines_b kle,
2222 okc_k_lines_b sub_kle,
2223 --added for evergreen change request 08_nov_2005
2224 okc_line_styles_b lse,
2225 okc_statuses_b khs,
2226 okc_statuses_b kls,
2227 okl_party_payment_hdr pph
2228 WHERE trunc(ste.stream_element_date) >=
2229 trunc(NVL (p_from_date, ste.stream_element_date))
2230 AND trunc(ste.stream_element_date) <=
2231 trunc((NVL (p_to_date, SYSDATE) ))
2232 AND ste.amount <> 0
2233 AND stm.id = ste.stm_id
2234 AND ste.date_disbursed IS NULL
2235 AND stm.active_yn = 'Y'
2236 AND stm.say_code = 'CURR'
2237 --start fix for bug 5040815 by pgomes 24-mar-2006
2238 AND stm.sty_id NOT IN (SELECT id FROM okl_strm_type_v
2239 WHERE stream_type_purpose in ('LATE_FEE', 'LATE_INTEREST'))
2240 --end fix for bug 5040815 by pgomes 24-mar-2006
2241 AND sty.id = stm.sty_id
2242 AND sty.billable_yn = 'Y'
2243 AND khr.id = stm.khr_id
2244 AND khr.scs_code IN ('LEASE', 'LOAN')
2245 AND khr.sts_code IN ( 'BOOKED','TERMINATED')
2246 AND khr.authoring_org_id = hou.organization_id
2247 AND khr.contract_number = NVL(p_contract_number, khr.contract_number)
2248 AND khl.id = stm.khr_id
2249 AND khl.deal_type IS NOT NULL
2250 AND khs.code = khr.sts_code
2251 AND khr.id = sub_kle.dnz_chr_id
2252 AND sub_kle.chr_id IS NULL
2253 AND sub_kle.id = stm.kle_id
2254 AND sub_kle.cle_id = kle.id
2255 AND kle.sts_code = kls.code
2256 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
2257 --added for evergreen change request 08_nov_2005
2258 AND kle.lse_id = lse.id
2259 --commented for evergreen change request 08_nov_2005
2260 --AND kle.id = pph.cle_id
2261 AND kle.dnz_chr_id = pph.dnz_chr_id
2262 --added for evergreen change request 08_nov_2005
2263 AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'BASE') = pph.id
2264 AND pph.payout_basis = 'DUE_DATE'
2265 AND trunc(ste.stream_element_date) >= trunc(NVL(pph.passthru_start_date, ste.stream_element_date))
2266 AND trunc(ste.stream_element_date) <= trunc(kle.end_date)
2267 AND pph.passthru_term = 'BASE';
2268
2269 --------------------------------------------------------------------
2270 --Declare Cursor: Contract Lines eligible for disbursement with
2271 --payout_basis = 'FORMULA' ,passthru_term = 'EVERGREEN'
2272 --for top lines
2273 --------------------------------------------------------------------
2274 CURSOR c_pay_formula(p_from_date DATE, p_to_date DATE, p_contract_number VARCHAR2)
2275 IS
2276 SELECT null cnr_id,
2277 null consolidated_invoice_number,
2278 hou.set_of_books_id,
2279 okch.authoring_org_id org_id,
2280 kle.start_date transaction_date,
2281 okch.currency_code,
2282 oklh.id khr_id,
2283 kle.id kle_id,
2284 null amount,
2285 null sty_id,
2286 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
2287 -- null lsm_id
2288 null tld_id,
2289 -- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
2290 null receivables_invoice_id,
2291 null sel_id,
2292 pph.id pph_id,
2293 pph.passthru_stream_type_id,
2294 pph.payout_basis,
2295 pph.payout_basis_formula,
2296 okch.contract_number,
2297 -- 01-NOV-2006 ANSETHUR R12B - Legal Entity
2298 oklh.legal_entity_id
2299 FROM okl_k_headers oklh,
2300 okc_k_headers_b okch,
2301 hr_operating_units hou,
2302 okc_k_lines_b kle,
2303 --added for evergreen change request 08_nov_2005
2304 okc_line_styles_b lse,
2305 okl_party_payment_hdr pph
2306 WHERE oklh.id = okch.id
2307 AND okch.contract_number = NVL (p_contract_number, okch.contract_number)
2308 AND okch.scs_code IN ('LEASE', 'LOAN')
2309 AND okch.sts_code = 'EVERGREEN'
2310 AND oklh.deal_type IS NOT NULL
2311 AND okch.authoring_org_id = hou.organization_id
2312 AND oklh.id = kle.dnz_chr_id
2313 AND kle.sts_code = 'EVERGREEN'
2314 --added for evergreen change request 08_nov_2005
2315 AND kle.lse_id = lse.id
2316 AND kle.dnz_chr_id = pph.dnz_chr_id
2317 --added for evergreen change request 08_nov_2005
2318 AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'EVERGREEN') = pph.id
2319 --commented for evergreen change request 08_nov_2005
2320 --AND kle.id = pph.cle_id
2321 AND pph.passthru_term = 'EVERGREEN'
2322 AND pph.payout_basis = 'FORMULA';
2323
2324 l_disb_tbl disb_tbl_type;
2325
2326 L_FETCH_SIZE NUMBER := 10000;
2327 BEGIN
2328 ------------------------------------------------------------
2329 -- Start processing
2330 ------------------------------------------------------------
2331 print_line ( '=====*** START PROCEDURE AUTO_DISBURSEMENT ***=====');
2332
2333 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2334
2335 l_return_status := OKL_API.START_ACTIVITY(
2336 p_api_name => l_api_name,
2337 p_pkg_name => g_pkg_name,
2338 p_init_msg_list => p_init_msg_list,
2339 l_api_version => l_api_version,
2340 p_api_version => p_api_version,
2341 p_api_type => '_PVT',
2342 x_return_status => l_return_status);
2343
2344 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2345 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2346 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2347 RAISE OKL_API.G_EXCEPTION_ERROR;
2348 END IF;
2349
2350 G_commit_count := 0;
2351
2352 ------------------------------------------------------------
2353 -- Open Billing Cursor
2354 ------------------------------------------------------------
2355 print_line ('==============================================');
2356 print_line ('=> Start - Processing payout basis of BILLING.');
2357 print_line ('==============================================');
2358 OPEN c_invoice_bill(p_from_date, p_to_date, p_contract_number);
2359 LOOP
2360 ------------------------------
2361 --Clear table contents
2362 ------------------------------
2363 l_disb_tbl.delete;
2364 FETCH c_invoice_bill BULK COLLECT INTO l_disb_tbl LIMIT L_FETCH_SIZE;
2365 FND_FILE.PUT_LINE (FND_FILE.LOG, 'c_invoice_bill l_disb_tbl count is: '||l_disb_tbl.COUNT);
2366
2367 IF l_disb_tbl.COUNT > 0 THEN
2368 auto_disbursement_tbl(p_api_version => p_api_version
2369 ,p_init_msg_list => p_init_msg_list
2370 ,x_return_status => x_return_status
2371 ,x_msg_count => x_msg_count
2372 ,x_msg_data => x_msg_data
2373 ,p_disb_tbl => l_disb_tbl);
2374 END IF;
2375
2376 EXIT WHEN c_invoice_bill%NOTFOUND;
2377 END LOOP;
2378 CLOSE c_invoice_bill;
2379 ------------------------------------------------------------
2380 -- Close Billing Cursor
2381 ------------------------------------------------------------
2382 print_line ('==============================================');
2383 print_line ('=> End - Processing payout basis of BILLING.');
2384 print_line ('==============================================');
2385 print_line (' ');
2386
2387 ------------------------------------------------------------
2388 -- Open Full Receipt Cursor
2389 ------------------------------------------------------------
2390 print_line ('==============================================');
2391 print_line ('=> Start - Processing payout basis of FULL_RECEIPT.');
2392 print_line ('==============================================');
2393 OPEN c_invoice_full_rcpt(p_from_date, p_to_date, p_contract_number);
2394 LOOP
2395 ------------------------------
2396 --Clear table contents
2397 ------------------------------
2398 l_disb_tbl.delete;
2399 FETCH c_invoice_full_rcpt BULK COLLECT INTO l_disb_tbl LIMIT L_FETCH_SIZE;
2400 FND_FILE.PUT_LINE (FND_FILE.LOG, 'c_invoice_full_rcpt l_disb_tbl count is: '||l_disb_tbl.COUNT);
2401
2402 IF l_disb_tbl.COUNT > 0 THEN
2403 auto_disbursement_tbl(p_api_version => p_api_version
2404 ,p_init_msg_list => p_init_msg_list
2405 ,x_return_status => x_return_status
2406 ,x_msg_count => x_msg_count
2407 ,x_msg_data => x_msg_data
2408 ,p_disb_tbl => l_disb_tbl);
2409 END IF;
2410
2411 EXIT WHEN c_invoice_full_rcpt%NOTFOUND;
2412 END LOOP;
2413 CLOSE c_invoice_full_rcpt;
2414 ------------------------------------------------------------
2415 -- Close Full Receipt Cursor
2416 ------------------------------------------------------------
2417 print_line ('==============================================');
2418 print_line ('=> End - Processing payout basis of FULL_RECEIPT.');
2419 print_line ('==============================================');
2420 print_line (' ');
2421
2422 ------------------------------------------------------------
2423 -- Open Partial Receipt Cursor
2424 ------------------------------------------------------------
2425 print_line ('==============================================');
2426 print_line ('=> Start - Processing payout basis of PARTIAL_RECEIPT.');
2427 print_line ('==============================================');
2428 OPEN c_invoice_part_rcpt(p_from_date, p_to_date, p_contract_number);
2429 LOOP
2430 ------------------------------
2431 --Clear table contents
2432 ------------------------------
2433 l_disb_tbl.delete;
2434 FETCH c_invoice_part_rcpt BULK COLLECT INTO l_disb_tbl LIMIT L_FETCH_SIZE;
2435 FND_FILE.PUT_LINE (FND_FILE.LOG, 'c_invoice_part_rcpt l_disb_tbl count is: '||l_disb_tbl.COUNT);
2436
2437 IF l_disb_tbl.COUNT > 0 THEN
2438 auto_disbursement_tbl(p_api_version => p_api_version
2439 ,p_init_msg_list => p_init_msg_list
2440 ,x_return_status => x_return_status
2441 ,x_msg_count => x_msg_count
2442 ,x_msg_data => x_msg_data
2443 ,p_disb_tbl => l_disb_tbl);
2444 END IF;
2445
2446 EXIT WHEN c_invoice_part_rcpt%NOTFOUND;
2447 END LOOP;
2448 CLOSE c_invoice_part_rcpt;
2449 ------------------------------------------------------------
2450 -- Close Partial Receipt Cursor
2451 ------------------------------------------------------------
2452 print_line ('==============================================');
2453 print_line ('=> End - Processing payout basis of PARTIAL_RECEIPT.');
2454 print_line ('==============================================');
2455 print_line (' ');
2456
2457 ------------------------------------------------------------
2458 -- Open Due Date Cursor
2459 ------------------------------------------------------------
2460 print_line ('==============================================');
2461 print_line ('=> Start - Processing payout basis of DUE DATE.');
2462 print_line ('==============================================');
2463 OPEN c_pay_sel(p_from_date, p_to_date, p_contract_number);
2464 LOOP
2465 ------------------------------
2466 --Clear table contents
2467 ------------------------------
2468 l_disb_tbl.delete;
2469 FETCH c_pay_sel BULK COLLECT INTO l_disb_tbl LIMIT L_FETCH_SIZE;
2470 FND_FILE.PUT_LINE (FND_FILE.LOG, 'c_pay_sel l_disb_tbl count is: '||l_disb_tbl.COUNT);
2471
2472 IF l_disb_tbl.COUNT > 0 THEN
2473 auto_disbursement_tbl(p_api_version => p_api_version
2474 ,p_init_msg_list => p_init_msg_list
2475 ,x_return_status => x_return_status
2476 ,x_msg_count => x_msg_count
2477 ,x_msg_data => x_msg_data
2478 ,p_disb_tbl => l_disb_tbl);
2479 END IF;
2480
2481 EXIT WHEN c_pay_sel%NOTFOUND;
2482 END LOOP;
2483 CLOSE c_pay_sel;
2484 ------------------------------------------------------------
2485 -- Close Due Date Cursor
2486 ------------------------------------------------------------
2487 print_line ('==============================================');
2488 print_line ('=> End - Processing payout basis of DUE DATE.');
2489 print_line ('==============================================');
2490 print_line (' ');
2491
2492 ------------------------------------------------------------
2493 -- Open Formula Cursor
2494 ------------------------------------------------------------
2495 print_line ('==============================================');
2496 print_line ('=> Start - Processing payout basis of FORMULA.');
2497 print_line ('==============================================');
2498 OPEN c_pay_formula(p_from_date, p_to_date, p_contract_number);
2499 LOOP
2500 ------------------------------
2501 --Clear table contents
2502 ------------------------------
2503 l_disb_tbl.delete;
2504 FETCH c_pay_formula BULK COLLECT INTO l_disb_tbl LIMIT L_FETCH_SIZE;
2505 FND_FILE.PUT_LINE (FND_FILE.LOG, 'c_pay_formula l_disb_tbl count is: '||l_disb_tbl.COUNT);
2506
2507 IF l_disb_tbl.COUNT > 0 THEN
2508 auto_disbursement_tbl(p_api_version => p_api_version
2509 ,p_init_msg_list => p_init_msg_list
2510 ,x_return_status => x_return_status
2511 ,x_msg_count => x_msg_count
2512 ,x_msg_data => x_msg_data
2513 ,p_disb_tbl => l_disb_tbl);
2514 END IF;
2515
2516 EXIT WHEN c_pay_formula%NOTFOUND;
2517 END LOOP;
2518 CLOSE c_pay_formula;
2519 ------------------------------------------------------------
2520 -- Close Formula Cursor
2521 ------------------------------------------------------------
2522 print_line ('==============================================');
2523 print_line ('=> End - Processing payout basis of FORMULA.');
2524 print_line ('==============================================');
2525 print_line (' ');
2526
2527 COMMIT;
2528 print_line ( '=====*** EXITING PROCEDURE AUTO_DISBURSEMENT ***=====');
2529
2530 okl_api.end_activity(x_msg_count => x_msg_count
2531 ,x_msg_data => x_msg_data);
2532 EXCEPTION
2533 ------------------------------------------------------------
2534 -- Exception handling
2535 ------------------------------------------------------------
2536
2537 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2538
2539 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2540 p_api_name => l_api_name,
2541 p_pkg_name => G_PKG_NAME,
2542 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2543 x_msg_count => x_msg_count,
2544 x_msg_data => x_msg_data,
2545 p_api_type => '_PVT');
2546
2547 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2548
2549 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2550 p_api_name => l_api_name,
2551 p_pkg_name => G_PKG_NAME,
2552 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2553 x_msg_count => x_msg_count,
2554 x_msg_data => x_msg_data,
2555 p_api_type => '_PVT');
2556
2557 WHEN OTHERS THEN
2558
2559 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
2560 p_api_name => l_api_name,
2561 p_pkg_name => G_PKG_NAME,
2562 p_exc_name => 'OTHERS',
2563 x_msg_count => x_msg_count,
2564 x_msg_data => x_msg_data,
2565 p_api_type => '_PVT');
2566
2567
2568 END auto_disbursement;
2569
2570 ----------------------------------------------------------------------------------
2571 -- Start of comments
2572 --
2573 -- Procedure Name : credit_check
2574 -- Description : Calculate total remaining total for a specific credit line
2575 -- This credit line may attach to MLAs (Master Lease Agreement)
2576 -- or other type of contracts.
2577 -- Business Rules :
2578 -- Parameters :
2579 -- Version : 1.0
2580 -- End of comments
2581 ----------------------------------------------------------------------------------
2582 FUNCTION credit_check(p_api_version IN NUMBER
2583 ,p_init_msg_list IN VARCHAR2
2584 ,x_return_status OUT NOCOPY VARCHAR2
2585 ,x_msg_count OUT NOCOPY NUMBER
2586 ,x_msg_data OUT NOCOPY VARCHAR2
2587 ,p_creditline_id IN NUMBER
2588 ,p_credit_max IN NUMBER
2589 ,P_trx_date IN DATE)
2590 RETURN NUMBER IS
2591
2592 l_credit_remain NUMBER := 0;
2593 l_disbursement_tot NUMBER := 0;
2594 l_is_revolving_credit BOOLEAN := false;
2595 l_dummy NUMBER;
2596 l_principal_tot NUMBER := 0;
2597
2598 -- sjalasut, modified the cursor to have khr_id referred to okl_txl_ap_inv_lns_all_b
2599 -- instead of okl_trx_ap_invoices_b. changes made as part of OKLR12B disbursements
2600 -- project.
2601 cursor c_disb_tot(p_creditline_id number) is
2602 SELECT NVL(SUM(NVL(TAP.AMOUNT,0)),0)
2603 FROM OKL_TRX_AP_INVOICES_B TAP
2604 ,OKL_TXL_AP_INV_LNS_ALL_B TPL
2605 WHERE TAP.ID = TPL.TAP_ID
2606 AND TAP.TRX_STATUS_CODE = 'PROCESSED' -- push to AP
2607 AND TRUNC(DATE_INVOICED) <= TRUNC(p_trx_date)
2608 AND
2609 ( EXISTS
2610 (
2611 -- indirect refer from MLA contract's credit line
2612 SELECT 1 -- op chrid
2613 FROM OKC_K_HEADERS_ALL_B KHR_OP
2614 WHERE KHR_OP.ID = TPL.KHR_ID -- link
2615 AND EXISTS (
2616 SELECT 1 -- MLA id
2617 FROM OKC_K_HEADERS_ALL_B KHR,
2618 OKC_GOVERNANCES MLA_GOV
2619 WHERE KHR.ID = MLA_GOV.CHR_ID_REFERRED
2620 AND KHR.SCS_CODE = 'MASTER_LEASE'
2621 AND MLA_GOV.DNZ_CHR_ID = KHR_OP.ID -- link
2622 AND EXISTS (
2623 SELECT 1 -- credit line id
2624 FROM OKC_K_HEADERS_ALL_B CRD,
2625 OKC_GOVERNANCES CRD_GOV
2626 WHERE CRD.ID = CRD_GOV.CHR_ID_REFERRED
2627 AND CRD.STS_CODE = 'ACTIVE'
2628 AND KHR.ID = CRD_GOV.DNZ_CHR_ID -- link
2629 AND CRD.ID = p_creditline_id
2630 )
2631 )
2632 )
2633 OR
2634 EXISTS
2635 (
2636 -- non-MLA contracts direct associated with credit line
2637 SELECT 1 -- op chrid
2638 FROM OKC_K_HEADERS_ALL_B KHR
2639 WHERE KHR.ID = TPL.KHR_ID -- link
2640 AND KHR.SCS_CODE <> 'MASTER_LEASE'
2641 AND EXISTS (
2642 SELECT 1 -- credit line id
2643 FROM OKC_K_HEADERS_ALL_B CRD,
2644 OKC_GOVERNANCES CRD_GOV
2645 WHERE CRD.ID = CRD_GOV.CHR_ID_REFERRED
2646 AND CRD.STS_CODE = 'ACTIVE'
2647 AND KHR.ID = CRD_GOV.DNZ_CHR_ID -- link
2648 AND CRD.ID = p_creditline_id
2649 )
2650 )
2651 )
2652 ;
2653
2654 cursor c_is_revolv_crd(p_creditline_id number) is
2655 select 1 -- Revloving line of credit line
2656 from okl_k_headers REV
2657 where rev.id = p_creditline_id
2658 and REV.REVOLVING_CREDIT_YN = 'Y'
2659 ;
2660
2661 cursor c_princ_tot(p_creditline_id number) is
2662 SELECT
2663 NVL(SUM(NVL(PS.AMOUNT_APPLIED,0)),0)
2664 FROM
2665 AR_PAYMENT_SCHEDULES_ALL PS,
2666 --rkuttiya R12 B Billing Architecture commented
2667 --OKL_CNSLD_AR_STRMS_B ST,
2668 okl_bpd_ar_inv_lines_v ST,
2669 --OKL_STRM_TYPE_TL SM,
2670 okl_strm_type_v SM,
2671 OKC_K_HEADERS_B CN
2672 WHERE
2673 PS.CLASS IN ('INV') AND
2674 ST.INVOICE_ID = PS.CUSTOMER_TRX_ID AND
2675 SM.ID = ST.STY_ID AND
2676 --SM.LANGUAGE = USERENV ('LANG') AND
2677 CN.ID = ST.CONTRACT_ID AND
2678 --SM.NAME = 'PRINCIPAL PAYMENT' AND
2679 SM.stream_type_purpose = 'PRINCIPAL_PAYMENT' AND
2680 TRUNC(NVL(PS.TRX_DATE, SYSDATE)) <= TRUNC(p_trx_date)
2681 AND
2682 ( EXISTS
2683 (
2684 -- indirect refer from MLA contract's credit line
2685 SELECT 1 -- op chrid
2686 FROM OKC_K_HEADERS_ALL_B KHR_OP
2687 WHERE KHR_OP.ID = CN.ID -- link
2688 AND EXISTS (
2689 SELECT 1 -- MLA id
2690 FROM OKC_K_HEADERS_ALL_B KHR,
2691 OKC_GOVERNANCES MLA_GOV
2692 WHERE KHR.ID = MLA_GOV.CHR_ID_REFERRED
2693 AND KHR.SCS_CODE = 'MASTER_LEASE'
2694 AND MLA_GOV.DNZ_CHR_ID = KHR_OP.ID -- link
2695 AND EXISTS (
2696 SELECT 1 -- credit line id
2697 FROM OKC_K_HEADERS_ALL_B CRD,
2698 OKC_GOVERNANCES CRD_GOV
2699 WHERE CRD.ID = CRD_GOV.CHR_ID_REFERRED
2700 AND CRD.STS_CODE = 'ACTIVE'
2701 AND KHR.ID = CRD_GOV.DNZ_CHR_ID -- link
2702 AND CRD.ID = p_creditline_id
2703 )
2704 )
2705 )
2706 OR
2707 EXISTS
2708 (
2709 -- non-MLA contracts direct associated with credit line
2710 SELECT 1 -- op chrid
2711 FROM OKC_K_HEADERS_ALL_B KHR
2712 WHERE KHR.ID = CN.ID -- link
2713 AND KHR.SCS_CODE <> 'MASTER_LEASE'
2714 AND EXISTS (
2715 SELECT 1 -- credit line id
2716 FROM OKC_K_HEADERS_ALL_B CRD,
2717 OKC_GOVERNANCES CRD_GOV
2718 WHERE CRD.ID = CRD_GOV.CHR_ID_REFERRED
2719 AND CRD.STS_CODE = 'ACTIVE'
2720 AND KHR.ID = CRD_GOV.DNZ_CHR_ID -- link
2721 AND CRD.ID = p_creditline_id
2722 )
2723 )
2724 )
2725 ;
2726
2727
2728 begin
2729
2730 OPEN c_disb_tot(p_creditline_id);
2731 FETCH c_disb_tot into l_disbursement_tot;
2732 CLOSE c_disb_tot;
2733
2734 OPEN c_is_revolv_crd(p_creditline_id);
2735 FETCH c_is_revolv_crd into l_dummy;
2736 l_is_revolving_credit := c_is_revolv_crd%FOUND;
2737 CLOSE c_is_revolv_crd;
2738
2739 IF (l_is_revolving_credit) THEN
2740
2741 OPEN c_princ_tot(p_creditline_id);
2742 FETCH c_princ_tot into l_principal_tot;
2743 CLOSE c_princ_tot;
2744
2745 l_credit_remain := p_credit_max - l_disbursement_tot + l_principal_tot;
2746 ELSE
2747 l_credit_remain := p_credit_max - l_disbursement_tot;
2748 END IF;
2749
2750 x_return_status := okl_api.G_RET_STS_SUCCESS;
2751 RETURN l_credit_remain;
2752
2753 EXCEPTION
2754 WHEN OTHERS THEN
2755 OKL_API.Set_Message(p_app_name => OKL_API.G_APP_NAME,
2756 p_msg_name => 'OKL_UNEXPECTED_ERROR',
2757 p_token1 => 'OKL_SQLCODE',
2758 p_token1_value => SQLCODE,
2759 p_token2 => 'OKL_SQLERRM',
2760 p_token2_value => SQLERRM);
2761 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
2762 RETURN NULL;
2763
2764
2765 END credit_check;
2766
2767
2768 END OKL_PAY_INVOICES_DISB_PVT;