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