[Home] [Help]
PACKAGE BODY: APPS.OKL_COMBI_CASH_APP_RLS_PVT
Source
1 PACKAGE BODY okl_combi_cash_app_rls_pvt AS
2 /* $Header: OKLRCAAB.pls 120.6 2008/01/08 12:14:18 asawanka noship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES.SETUP';
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 ---------------------------------------------------------------------------
11 -- Function get_req_recs
12 ---------------------------------------------------------------------------
13
14 FUNCTION get_req_recs(invoice VARCHAR) RETURN BOOLEAN IS
15 is_in BOOLEAN DEFAULT TRUE;
16 i NUMBER;
17 BEGIN
18 i := 0;
19 LOOP
20 i := i + 1;
21 IF l_scn_rcpt_tbl(i).invoice_number = invoice THEN
22 is_in:= FALSE;
23 END IF;
24 EXIT WHEN i = l_scn_rcpt_tbl.LAST;
25 END LOOP;
26 RETURN(is_in);
27 END get_req_recs;
28
29 ---------------------------------------------------------------------------
30 -- PROCEDURE handle_combi_pay
31 ---------------------------------------------------------------------------
32
33 PROCEDURE handle_combi_pay
34 ( p_api_version IN NUMBER
35 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
36 ,x_return_status OUT NOCOPY VARCHAR2
37 ,x_msg_count OUT NOCOPY NUMBER
38 ,x_msg_data OUT NOCOPY VARCHAR2
39 ,p_customer_number IN VARCHAR2
40 ,p_rcpt_amount IN NUMBER
41 ,p_org_id IN NUMBER
42 ,p_currency_code IN VARCHAR2
43 ,x_appl_tbl OUT NOCOPY okl_auto_cash_appl_rules_pvt.okl_appl_dtls_tbl_type
44 ) IS
45
46 ---------------------------
47 -- DECLARE Local Variables
48 ---------------------------
49
50 l_customer_number VARCHAR2(30) DEFAULT p_customer_number;
51 l_cons_inv_number VARCHAR2(30) DEFAULT NULL;
52 l_ar_inv_number VARCHAR2(30) DEFAULT NULL;
53 l_contract_number VARCHAR2(30) DEFAULT NULL;
54 l_due_date DATE DEFAULT NULL;
55 l_rcpt_amount NUMBER DEFAULT p_rcpt_amount;
56
57 l_org_id Number:=p_org_id;
58 l_match_found NUMBER DEFAULT 0;
59 l_currency_code ar_cash_receipts.currency_code%TYPE DEFAULT p_currency_code;
60
61 l_api_version NUMBER := 1.0;
62 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
63 l_return_status VARCHAR2(1);
64 l_msg_count NUMBER;
65 l_msg_data VARCHAR2(2000);
66
67 ------------------------------
68 -- DECLARE Record/Table Types
69 ------------------------------
70 l_appl_tbl okl_auto_cash_appl_rules_pvt.okl_appl_dtls_tbl_type;
71 -------------------
72 -- DECLARE Cursors
73 -------------------
74
75 -- get unique cons bill numbers
76 CURSOR c_get_cons_invs(cp_cust_num IN VARCHAR2,cp_org_id IN NUMBER) IS
77 SELECT distinct(consolidated_invoice_number), due_date
78 FROM okl_rcpt_consinv_balances_uv
79 WHERE customer_number = cp_cust_num
80 AND org_id= cp_org_id
81 ORDER BY due_date;
82
83 ----------
84
85 -- get unique contract number
86 CURSOR c_get_contract(cp_cust_num IN VARCHAR2,cp_org_id IN NUMBER) IS
87 SELECT distinct(contract_number)
88 FROM okl_rcpt_cust_cont_balances_uv
89 WHERE customer_number = cp_cust_num
90 AND org_id= cp_org_id
91 ORDER BY invoice_due_date;
92
93 -- need to find due or start date for contracts and order by that.
94 ---------------
95
96 -- get unique ar invoice numbers
97 CURSOR c_get_ar_invs(cp_cust_num IN VARCHAR2,cp_org_id IN NUMBER) IS
98 SELECT distinct(invoice_number), invoice_due_date due_date
99 FROM okl_rcpt_arinv_balances_uv
100 WHERE customer_number = cp_cust_num
101 AND org_id= cp_org_id
102 ORDER BY invoice_due_date;
103
104
105 ----------
106
107
108 BEGIN
109
110 l_cons_inv_number := NULL;
111 l_contract_number := NULL;
112
113 OPEN c_get_ar_invs (l_customer_number,l_org_id); -- search by ar invoice(s)
114 LOOP
115 FETCH c_get_ar_invs INTO l_ar_inv_number,l_due_date;
116 EXIT WHEN c_get_ar_invs%NOTFOUND;
117
118 search_combi ( p_api_version => l_api_version
119 ,p_init_msg_list => p_init_msg_list
120 ,x_return_status => l_return_status
121 ,x_msg_count => l_msg_count
122 ,x_msg_data => l_msg_data
123 ,p_customer_number => l_customer_number
124 ,p_cons_inv_number => l_cons_inv_number
125 ,p_contract_number => l_contract_number
126 ,p_ar_inv_number => l_ar_inv_number
127 ,p_org_id => l_org_id
128 ,p_rcpt_amount => l_rcpt_amount
129 ,p_currency_code => l_currency_code
130 ,x_match_found => l_match_found
131 ,x_appl_tbl => l_appl_tbl
132 );
133
134
135 x_return_status := l_return_status;
136 x_msg_data := l_msg_data;
137 x_msg_count := l_msg_count;
138
139 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
140 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
141 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
142 RAISE OKL_API.G_EXCEPTION_ERROR;
143 END IF;
144
145 IF l_match_found = 1 THEN
146 EXIT;
147 END IF;
148
149 END LOOP;
150 CLOSE c_get_ar_invs;
151
152 IF l_match_found = 0 THEN
153
154 l_contract_number := NULL;
155 l_ar_inv_number :=NULL;
156
157 OPEN c_get_cons_invs (l_customer_number,l_org_id); -- search by consolidated inv related invoice(s)
158 LOOP
159 FETCH c_get_cons_invs INTO l_cons_inv_number, l_due_date;
160 EXIT WHEN c_get_cons_invs%NOTFOUND;
161
162 search_combi ( p_api_version => l_api_version
163 ,p_init_msg_list => l_init_msg_list
164 ,x_return_status => l_return_status
165 ,x_msg_count => l_msg_count
166 ,x_msg_data => l_msg_data
167 ,p_customer_number => l_customer_number
168 ,p_cons_inv_number => l_cons_inv_number
169 ,p_contract_number => l_contract_number
170 ,p_ar_inv_number => l_ar_inv_number
171 ,p_org_id => l_org_id
172 ,p_rcpt_amount => l_rcpt_amount
173 ,p_currency_code => l_currency_code
174 ,x_match_found => l_match_found
175 ,x_appl_tbl => l_appl_tbl
176 );
177
178 x_return_status := l_return_status;
179 x_msg_data := l_msg_data;
180 x_msg_count := l_msg_count;
181
182 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
183 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
184 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
185 RAISE OKL_API.G_EXCEPTION_ERROR;
186 END IF;
187
188 IF l_match_found = 1 THEN
189 EXIT;
190 END IF;
191
192 END LOOP;
193 CLOSE c_get_cons_invs;
194
195 END IF;
196
197 IF l_match_found = 0 THEN -- search by contract related invoice(s)
198
199 l_cons_inv_number := NULL;
200 l_ar_inv_number :=NULL;
201
202 OPEN c_get_contract (l_customer_number,l_org_id);
203 LOOP
204 FETCH c_get_contract INTO l_contract_number;
205 EXIT WHEN c_get_contract%NOTFOUND;
206
207 search_combi ( p_api_version => l_api_version
208 ,p_init_msg_list => p_init_msg_list
209 ,x_return_status => l_return_status
210 ,x_msg_count => l_msg_count
211 ,x_msg_data => l_msg_data
212 ,p_customer_number => l_customer_number
213 ,p_cons_inv_number => l_cons_inv_number
214 ,p_contract_number => l_contract_number
215 ,p_ar_inv_number => l_ar_inv_number
216 ,p_org_id => l_org_id
217 ,p_rcpt_amount => l_rcpt_amount
218 ,p_currency_code => l_currency_code
219 ,x_match_found => l_match_found
220 ,x_appl_tbl => l_appl_tbl
221 );
222
223
224 x_return_status := l_return_status;
225 x_msg_data := l_msg_data;
226 x_msg_count := l_msg_count;
227
228 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
229 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
230 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
231 RAISE OKL_API.G_EXCEPTION_ERROR;
232 END IF;
233
234 IF l_match_found = 1 THEN
235 EXIT;
236 END IF;
237
238 END LOOP;
239 CLOSE c_get_contract;
240
241 END IF;
242
243
244 IF l_match_found = 0 THEN -- all out search because still no match.
245
246 l_cons_inv_number := NULL;
247 l_contract_number := NULL;
248 l_ar_inv_number := NULL;
249
250 search_combi ( p_api_version => l_api_version
251 ,p_init_msg_list => p_init_msg_list
252 ,x_return_status => l_return_status
253 ,x_msg_count => l_msg_count
254 ,x_msg_data => l_msg_data
255 ,p_customer_number => l_customer_number
256 ,p_cons_inv_number => l_cons_inv_number
257 ,p_contract_number => l_contract_number
258 ,p_ar_inv_number => l_ar_inv_number
259 ,p_org_id => l_org_id
260 ,p_rcpt_amount => l_rcpt_amount
261 ,p_currency_code => l_currency_code
262 ,x_match_found => l_match_found
263 ,x_appl_tbl => l_appl_tbl
264 );
265
266 x_return_status := l_return_status;
267 x_msg_data := l_msg_data;
268 x_msg_count := l_msg_count;
269
270 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
271 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
272 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
273 RAISE OKL_API.G_EXCEPTION_ERROR;
274 END IF;
275
276 END IF;
277
278 x_appl_tbl:= l_appl_tbl;
279
280 EXCEPTION
281
282 WHEN OTHERS THEN
283 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
284 x_msg_count := l_msg_count ;
285 x_msg_data := l_msg_data ;
286
287 END handle_combi_pay;
288
289
290 PROCEDURE search_combi
291 ( p_api_version IN NUMBER
292 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
293 ,x_return_status OUT NOCOPY VARCHAR2
294 ,x_msg_count OUT NOCOPY NUMBER
295 ,x_msg_data OUT NOCOPY VARCHAR2
296 ,p_customer_number IN VARCHAR2
297 ,p_cons_inv_number IN VARCHAR2
298 ,p_contract_number IN VARCHAR2
299 ,p_ar_inv_number IN VARCHAR2
300 ,p_org_id IN NUMBER
301 ,p_rcpt_amount IN NUMBER
302 ,p_currency_code IN VARCHAR2
303 ,x_match_found OUT NOCOPY NUMBER
304 ,x_appl_tbl OUT NOCOPY okl_auto_cash_appl_rules_pvt.okl_appl_dtls_tbl_type
305 ) IS
306
307 ---------------------------
308 -- DECLARE Local Variables
309 ---------------------------
310
311
312 l_org_id NUMBER := p_org_id;
313
314 l_customer_number VARCHAR2(30) DEFAULT p_customer_number;
315 l_cons_inv_number VARCHAR2(30) DEFAULT p_cons_inv_number;
316 l_contract_number VARCHAR2(30) DEFAULT p_contract_number;
317 l_ar_inv_number VARCHAR2(30) DEFAULT p_ar_inv_number;
318
319 l_sty_id OKL_SRCH_STRM_TYPS.STY_ID%TYPE DEFAULT NULL;
320 l_amount_due_remaining NUMBER DEFAULT NULL;
321 l_currency_code ar_cash_receipts.currency_code%TYPE DEFAULT p_currency_code;
322
323 l_running_total NUMBER DEFAULT NULL;
324 l_rcpt_amount NUMBER DEFAULT p_rcpt_amount;
325 l_receivables_invoice_number NUMBER DEFAULT NULL;
326
327 l_inv_tot NUMBER DEFAULT NULL;
328
329 l_count NUMBER DEFAULT NULL;
330 l_init_count NUMBER DEFAULT NULL;
331 l_next_rule NUMBER DEFAULT 0;
332 l_match_found NUMBER DEFAULT 0;
333
334 i NUMBER DEFAULT NULL; -- for scn table count
335 j NUMBER DEFAULT NULL; -- for ttl table count
336 k NUMBER DEFAULT NULL; -- for tmc table count
337
338 l_cah_id OKL_SRCH_STRM_TYPS.CAH_ID%TYPE DEFAULT NULL;
339 l_seq_num OKL_CSH_ALLCT_SRCHS.SEQUENCE_NUMBER%TYPE DEFAULT NULL;
340 l_csh_type OKL_CSH_ALLCT_SRCHS.CASH_SEARCH_TYPE%TYPE DEFAULT NULL;
341
342 l_rule_sty_id OKL_SRCH_STRM_TYPS.STY_ID%TYPE DEFAULT NULL;
343 l_add_yn OKL_SRCH_STRM_TYPS.ADD_YN%TYPE DEFAULT NULL;
344
345 l_api_version NUMBER := 1.0;
346 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
347 l_return_status VARCHAR2(1);
348 l_msg_count NUMBER;
349 l_msg_data VARCHAR2(2000);
350
351 ------------------------------
352 -- DECLARE Record/Table Types
353 ------------------------------
354 l_appl_tbl okl_auto_cash_appl_rules_pvt.okl_appl_dtls_tbl_type;
355 l_scn_rcpt_tbl okl_auto_cash_appl_rules_pvt.okl_rcpt_dtls_tbl_type;
356 l_tmc_rcpt_tbl okl_auto_cash_appl_rules_pvt.okl_rcpt_dtls_tbl_type;
357 l_rcpt_tbl okl_auto_cash_appl_rules_pvt.okl_rcpt_dtls_tbl_type;
358 -------------------
359 -- DECLARE Cursors
360 -------------------
361
362 -- get all open invoices for customer and consolidated invoices
363 CURSOR c_open_invs_consinv( cp_customer_num IN VARCHAR2
364 ,cp_cons_bill_num IN VARCHAR2
365 ,cp_stream_type_id IN NUMBER
366 ,cp_org_id IN NUMBER) IS
367 SELECT AR_INVOICE_ID,
368 AR_INVOICE_NUMBER,
369 AR_INVOICE_LINE_ID INVOICE_LINE_ID,
370 LINE_NUMBER,
371 AMOUNT_DUE_REMAINING,
372 CURRENCY_CODE
373 FROM okl_rcpt_consinv_balances_uv lpt
374 WHERE lpt.consolidated_invoice_number = cp_cons_bill_num
375 AND lpt.customer_number = NVL (cp_customer_num, lpt.customer_number)
376 AND lpt.stream_type_id = NVL (cp_stream_type_id, lpt.stream_type_id)
377 AND lpt.amount_due_remaining > 0
378 AND lpt.org_id=cp_org_id
379 AND lpt.status='OP';
380
381
382 c_open_inv_rec c_open_invs_consinv%ROWTYPE;
383 c_open_invs_rec c_open_invs_consinv%ROWTYPE;
384
388 ,cp_ar_bill_num IN VARCHAR2
385 ----------
386 -- get all open invoices for customer ar invoices
387 CURSOR c_open_invs_arinv( cp_customer_num IN VARCHAR2
389 ,cp_stream_type_id IN NUMBER
390 ,cp_org_id IN NUMBER) IS
391 Select AR_INVOICE_ID Ar_Invoice_Id,
392 Invoice_Number AR_INVOICE_NUMBER,
393 INVOICE_LINE_ID invoice_line_id,
394 Line_Identifier Line_Number,
395 amount_due_remaining amount_due_remaining,
396 invoice_currency_code CURRENCY_CODE
397 From OKL_RCPT_ARINV_BALANCES_UV
398 Where Invoice_Number = cp_ar_bill_num
399 And Org_id = cp_org_id
400 AND amount_due_remaining > 0
401 AND status = 'OP'
402 And sty_Id = Nvl(cp_stream_type_id,sty_id);
403
404
405 ----------
406 -- get all open invoices for customer/contracts
407 CURSOR c_open_invs_contract( cp_customer_num IN VARCHAR2
408 ,cp_contract_num IN VARCHAR2
409 ,cp_stream_type_id IN NUMBER
410 ,cp_org_id IN NUMBER) IS
411 SELECT AR_INVOICE_ID,
412 AR_INVOICE_NUMBER,
413 INVOICE_LINE_ID,
414 LINE_NUMBER,
415 AMOUNT_DUE_REMAINING,
416 CURRENCY_CODE
417 FROM OKL_RCPT_CUST_CONT_BALANCES_UV
418 WHERE CONTRACT_NUMBER = NVL (cp_contract_num, CONTRACT_NUMBER)
419 AND STY_ID = NVL(cp_stream_type_id, STY_ID)
420 AND CUSTOMER_ACCOUNT_NUMBER = cp_customer_num
421 AND ORG_ID = cp_org_id
422 AND STATUS = 'OP'
423 AND AMOUNT_DUE_REMAINING > 0;
424
425
426 ------------
427
428 -- get search combination rule headers
429 CURSOR c_get_rules IS
430 SELECT id, sequence_number, cash_search_type
431 FROM okl_csh_allct_srchs
432 ORDER BY sequence_number;
433
434 ----------
435
436 -- get search combination rule lines
437 CURSOR c_get_rule_lines(cp_cah_id IN NUMBER) IS
438 SELECT sty_id, add_yn
439 FROM okl_srch_strm_typs
440 WHERE cah_id = cp_cah_id;
441
442 ----------
443
444 BEGIN
445
446 OPEN c_get_rules; -- get rule headers in seq order
447 LOOP
448 l_next_rule := 0;
449 l_running_total := 0; -- reset running total for new rule
450 l_init_count := 0;
451
452 i := 0;
453
454 FETCH c_get_rules INTO l_cah_id, l_seq_num, l_csh_type;
455 EXIT WHEN c_get_rules%NOTFOUND;
456
457 OPEN c_get_rule_lines(l_cah_id); -- get associated rule lines
458 LOOP
459 FETCH c_get_rule_lines INTO l_rule_sty_id, l_add_yn;
460 EXIT WHEN c_get_rule_lines%NOTFOUND;
461
462 IF l_cons_inv_number is not null THEN
463 OPEN c_open_invs_consinv (l_customer_number, l_cons_inv_number, l_rule_sty_id,l_org_id);
464 l_count := 0;
465
466 LOOP
467 FETCH c_open_invs_consinv INTO c_open_inv_rec;
468 l_amount_due_remaining:= c_open_inv_rec.amount_due_remaining;
469 IF c_open_invs_consinv%NOTFOUND AND
470 l_count <> 0 THEN
471 EXIT; -- exit out first loop and pick up next rule_sty_id from rule.
472 END IF;
473
474 IF c_open_invs_consinv%NOTFOUND AND
475 l_count = 0 THEN
476 l_next_rule := 1; -- rule has failed, next !
477 EXIT; -- exit loop
478 ELSE
479 l_count := l_count + 1;
480
481 IF l_add_yn = 'Y' THEN
482 l_running_total := l_running_total + l_amount_due_remaining;
483 ELSE
484 l_running_total := l_running_total - l_amount_due_remaining;
485 END IF;
486
487 IF l_init_count = 0 THEN -- initialise the table ...
488 l_scn_rcpt_tbl := l_initialize;
489 END IF;
490
491 l_init_count := l_init_count + 1;
492
493 i := i + 1;
494 -- l_scn_rcpt_tbl(i).invoice_number := l_receivables_invoice_number;
495 -- l_scn_rcpt_tbl(i).amount_applied := l_amount_due_remaining;
496 l_scn_rcpt_tbl(i).INVOICE_NUMBER := c_open_inv_rec.ar_invoice_number;
497 l_scn_rcpt_tbl(i).INVOICE_CURRENCY_CODE := c_open_inv_rec.currency_code;
498 l_scn_rcpt_tbl(i).INVOICE_ID := c_open_inv_rec.ar_invoice_id;
499 l_scn_rcpt_tbl(i).INVOICE_LINE_ID := c_open_inv_rec.invoice_line_id;
500 l_scn_rcpt_tbl(i).INVOICE_LINE_NUMBER := c_open_inv_rec.line_number;
501 l_scn_rcpt_tbl(i).AMOUNT_APPLIED := c_open_inv_rec.amount_due_remaining;
502
503
504 END IF;
505 END LOOP;
506 CLOSE c_open_invs_consinv;
507 END IF;
508
509
510 --- for ar invoices
511 IF l_ar_inv_number is not null THEN
512 OPEN c_open_invs_arinv (l_customer_number, l_ar_inv_number, l_rule_sty_id,l_org_id);
513 l_count := 0;
514
515 LOOP
516 FETCH c_open_invs_arinv INTO c_open_inv_rec;
517 l_amount_due_remaining:= c_open_inv_rec.amount_due_remaining;
518 IF c_open_invs_arinv%NOTFOUND AND
519 l_count <> 0 THEN
520 EXIT; -- exit out first loop and pick up next rule_sty_id from rule.
521 END IF;
522
523 IF c_open_invs_arinv%NOTFOUND AND
524 l_count = 0 THEN
528 l_count := l_count + 1;
525 l_next_rule := 1; -- rule has failed, next !
526 EXIT; -- exit loop
527 ELSE
529
530 IF l_add_yn = 'Y' THEN
531 l_running_total := l_running_total + l_amount_due_remaining;
532 ELSE
533 l_running_total := l_running_total - l_amount_due_remaining;
534 END IF;
535
536 IF l_init_count = 0 THEN -- initialise the table ...
537 l_scn_rcpt_tbl := l_initialize;
538 END IF;
539
540 l_init_count := l_init_count + 1;
541
542 i := i + 1;
543 l_scn_rcpt_tbl(i).INVOICE_NUMBER := c_open_inv_rec.ar_invoice_number;
544 l_scn_rcpt_tbl(i).INVOICE_CURRENCY_CODE := c_open_inv_rec.currency_code;
545 l_scn_rcpt_tbl(i).INVOICE_ID := c_open_inv_rec.ar_invoice_id;
546 l_scn_rcpt_tbl(i).INVOICE_LINE_ID := c_open_inv_rec.invoice_line_id;
547 l_scn_rcpt_tbl(i).INVOICE_LINE_NUMBER := c_open_inv_rec.line_number;
548 l_scn_rcpt_tbl(i).AMOUNT_APPLIED := c_open_inv_rec.amount_due_remaining;
549
550
551 END IF;
552 END LOOP;
553 CLOSE c_open_invs_arinv;
554 END IF;
555
556 ---- for contracts
557 IF l_contract_number is not null THEN
558 OPEN c_open_invs_contract (l_customer_number, l_contract_number, l_rule_sty_id,l_org_id);
559 l_count := 0;
560
561 LOOP
562 FETCH c_open_invs_contract INTO c_open_inv_rec;
563 l_amount_due_remaining:= c_open_inv_rec.amount_due_remaining;
564 IF c_open_invs_contract%NOTFOUND AND
565 l_count <> 0 THEN
566 EXIT; -- exit out first loop and pick up next rule_sty_id from rule.
567 END IF;
568
569 IF c_open_invs_contract%NOTFOUND AND
570 l_count = 0 THEN
571 l_next_rule := 1; -- rule has failed, next !
572 EXIT; -- exit loop
573 ELSE
574 l_count := l_count + 1;
575
576 IF l_add_yn = 'Y' THEN
577 l_running_total := l_running_total + l_amount_due_remaining;
578 ELSE
579 l_running_total := l_running_total - l_amount_due_remaining;
580 END IF;
581
582 IF l_init_count = 0 THEN -- initialise the table ...
583 l_scn_rcpt_tbl := l_initialize;
584 END IF;
585
586 l_init_count := l_init_count + 1;
587
588 i := i + 1;
589 l_scn_rcpt_tbl(i).INVOICE_NUMBER := c_open_inv_rec.ar_invoice_number;
590 l_scn_rcpt_tbl(i).INVOICE_CURRENCY_CODE := c_open_inv_rec.currency_code;
591 l_scn_rcpt_tbl(i).INVOICE_ID := c_open_inv_rec.ar_invoice_id;
592 l_scn_rcpt_tbl(i).INVOICE_LINE_ID := c_open_inv_rec.invoice_line_id;
593 l_scn_rcpt_tbl(i).INVOICE_LINE_NUMBER := c_open_inv_rec.line_number;
594 l_scn_rcpt_tbl(i).AMOUNT_APPLIED := c_open_inv_rec.amount_due_remaining;
595
596 END IF;
597 END LOOP;
598 CLOSE c_open_invs_contract;
599 END IF;
600
601
602 ------- for no match condition
603
604 IF l_cons_inv_number is null and l_ar_inv_number is null and l_contract_number is null THEN
605 OPEN c_open_invs_contract (l_customer_number, NULL, l_rule_sty_id,l_org_id);
606 l_count := 0;
607
608 LOOP
609 FETCH c_open_invs_contract INTO c_open_inv_rec;
610 l_amount_due_remaining:= c_open_inv_rec.amount_due_remaining;
611 IF c_open_invs_contract%NOTFOUND AND
612 l_count <> 0 THEN
613 EXIT; -- exit out first loop and pick up next rule_sty_id from rule.
614 END IF;
615
616 IF c_open_invs_contract%NOTFOUND AND
617 l_count = 0 THEN
618 l_next_rule := 1; -- rule has failed, next !
619 EXIT; -- exit loop
620 ELSE
621 l_count := l_count + 1;
622
623 IF l_add_yn = 'Y' THEN
624 l_running_total := l_running_total + l_amount_due_remaining;
625 ELSE
626 l_running_total := l_running_total - l_amount_due_remaining;
627 END IF;
628
629 IF l_init_count = 0 THEN -- initialise the table ...
630 l_scn_rcpt_tbl := l_initialize;
631 END IF;
632
633 l_init_count := l_init_count + 1;
634
635 i := i + 1;
636 l_scn_rcpt_tbl(i).INVOICE_NUMBER := c_open_inv_rec.ar_invoice_number;
637 l_scn_rcpt_tbl(i).INVOICE_CURRENCY_CODE := c_open_inv_rec.currency_code;
638 l_scn_rcpt_tbl(i).INVOICE_ID := c_open_inv_rec.ar_invoice_id;
639 l_scn_rcpt_tbl(i).INVOICE_LINE_ID := c_open_inv_rec.invoice_line_id;
640 l_scn_rcpt_tbl(i).INVOICE_LINE_NUMBER := c_open_inv_rec.line_number;
641 l_scn_rcpt_tbl(i).AMOUNT_APPLIED := c_open_inv_rec.amount_due_remaining;
642
643
644 END IF;
645 END LOOP;
646 CLOSE c_open_invs_contract;
647 END IF;
648
649
650 IF l_next_rule = 1 THEN
651 EXIT; -- exit loop and pick up next rule
652 END IF;
653
654 END LOOP;
655 CLOSE c_get_rule_lines;
656
657
658 IF l_next_rule = 0 THEN -- we have a match, prepare table
659
660 j := 1;
661 -- get invoice amount due remaining
662 IF l_ar_inv_number is not null THEN
663 FOR c_open_invs_rec IN c_open_invs_consinv (l_customer_number, l_cons_inv_number, l_rule_sty_id,l_org_id)
664 LOOP
668 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invs_rec.ar_invoice_number;
665 l_inv_tot := l_inv_tot + c_open_invs_rec.amount_due_remaining;
666
667 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invs_rec.amount_due_remaining;
669 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := c_open_invs_rec.currency_code;
670 l_rcpt_tbl(j).INVOICE_ID := c_open_invs_rec.ar_invoice_id;
671 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invs_rec.line_number;
672 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invs_rec.invoice_line_id;
673
674
675 j := j + 1;
676
677 END LOOP;
678
679 ELSIF l_ar_inv_number is not null THEN
680 FOR c_open_invs_rec IN c_open_invs_arinv (l_customer_number, l_ar_inv_number, l_rule_sty_id,l_org_id)
681 LOOP
682 l_inv_tot := l_inv_tot + c_open_invs_rec.amount_due_remaining;
683
684 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invs_rec.amount_due_remaining;
685 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invs_rec.ar_invoice_number;
686 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := c_open_invs_rec.currency_code;
687 l_rcpt_tbl(j).INVOICE_ID := c_open_invs_rec.ar_invoice_id;
688 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invs_rec.line_number;
689 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invs_rec.invoice_line_id;
690
691
692 j := j + 1;
693
694 END LOOP;
695
696 ELSIF l_contract_number is not null THEN
697
698 FOR c_open_invs_rec IN c_open_invs_contract (l_customer_number, l_contract_number, l_rule_sty_id,l_org_id)
699 LOOP
700 l_inv_tot := l_inv_tot + c_open_invs_rec.amount_due_remaining;
701
702 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invs_rec.amount_due_remaining;
703 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invs_rec.ar_invoice_number;
704 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := c_open_invs_rec.currency_code;
705 l_rcpt_tbl(j).INVOICE_ID := c_open_invs_rec.ar_invoice_id;
706 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invs_rec.line_number;
707 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invs_rec.invoice_line_id;
708
709
710 j := j + 1;
711
712 END LOOP;
713 ELSE
714 FOR c_open_invs_rec IN c_open_invs_contract (l_customer_number, null, l_rule_sty_id,l_org_id)
715 LOOP
716 l_inv_tot := l_inv_tot + c_open_invs_rec.amount_due_remaining;
717
718 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invs_rec.amount_due_remaining;
719 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invs_rec.ar_invoice_number;
720 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := c_open_invs_rec.currency_code;
721 l_rcpt_tbl(j).INVOICE_ID := c_open_invs_rec.ar_invoice_id;
722 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invs_rec.line_number;
723 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invs_rec.invoice_line_id;
724
725
726 j := j + 1;
727 END LOOP;
728 END IF;
729
730
731 IF l_rcpt_amount = l_running_total AND
732 l_csh_type = 'SCN' or l_csh_type = 'scn' THEN
733
734 /* -- we need to return the table in multiples of 8
735
736 IF (l_scn_rcpt_tbl.COUNT > 1 AND
737 l_scn_rcpt_tbl.COUNT < 8) OR
738 mod((l_scn_rcpt_tbl.COUNT), 8) <> 0 THEN
739 -- j := l_rcpt_tbl.NEXT(j);
740 LOOP
741
742 i:= i + 1;
743 l_scn_rcpt_tbl(i).INVOICE_CURRENCY_CODE :='USD'; -- used ONLY to buffer the records out !!
744 EXIT WHEN mod((l_scn_rcpt_tbl.COUNT), 8) = 0; -- multiple of 8
745 END LOOP;
746
747 END IF;*/
748 okl_auto_cash_appl_rules_pvt.GET_APPLICATIONS( p_rcpt_tbl => l_scn_rcpt_tbl
749 ,x_appl_tbl => l_appl_tbl);
750
751 l_match_found := 1;
752 x_appl_tbl := l_appl_tbl;
753
754 EXIT; -- rules loop - we're done !
755
756 ELSIF l_rcpt_amount = l_inv_tot - l_running_total AND
757 l_csh_type = 'TMC' or l_csh_type = 'tmc' THEN
758
759
760 j := 0;
761 k := 0;
762
763 LOOP
764 j:= j + 1;
765 IF l_rcpt_tbl(j).invoice_number IS NOT NULL THEN
766
767 IF get_req_recs(l_rcpt_tbl(j).invoice_number) THEN -- if i (scn) is not in j (ttl) ...
768 k := k + 1;
769 -- l_tmc_rcpt_tbl(k).invoice_number := l_rcpt_tbl(j).invoice_number;
770 -- l_tmc_rcpt_tbl(k).amount_applied := l_rcpt_tbl(j).amount_applied;
771 l_tmc_rcpt_tbl(k).INVOICE_NUMBER := l_rcpt_tbl(j).invoice_number;
772 l_tmc_rcpt_tbl(k).INVOICE_CURRENCY_CODE := l_rcpt_tbl(j).INVOICE_CURRENCY_CODE;
773 l_tmc_rcpt_tbl(k).INVOICE_ID := l_rcpt_tbl(j).INVOICE_ID ;
774 l_tmc_rcpt_tbl(k).INVOICE_LINE_ID := l_rcpt_tbl(j).INVOICE_LINE_ID;
775 l_tmc_rcpt_tbl(k).INVOICE_LINE_NUMBER := l_rcpt_tbl(j).INVOICE_LINE_NUMBER ;
776 l_tmc_rcpt_tbl(k).AMOUNT_APPLIED := l_rcpt_tbl(j).AMOUNT_APPLIED;
777
778 END IF;
779
780 END IF;
781
782 EXIT WHEN j = l_rcpt_tbl.LAST;
783
784 END LOOP;
785
786 -- we need to return the table in multiples of 8
787 /*
788 IF (l_tmc_rcpt_tbl.COUNT > 1 AND
789 l_tmc_rcpt_tbl.COUNT < 8) OR
790 mod((l_tmc_rcpt_tbl.COUNT), 8) <> 0 THEN
791 -- j := l_rcpt_tbl.NEXT(j);
792 LOOP
793 k := k + 1;
794 l_tmc_rcpt_tbl(k).INVOICE_CURRENCY_CODE := 'USD'; -- used ONLY to buffer the records out !!
795 EXIT WHEN mod((l_tmc_rcpt_tbl.COUNT), 8) = 0; -- multiple of 8
796 END LOOP;
797
798 END IF;*/
799 okl_auto_cash_appl_rules_pvt.GET_APPLICATIONS( p_rcpt_tbl => l_tmc_rcpt_tbl
800 ,x_appl_tbl => l_appl_tbl);
801
802 l_match_found := 1;
803 x_appl_tbl := l_appl_tbl;
804
805 -- x_appl_tbl := l_tmc_rcpt_tbl;
806 EXIT; -- rules loop - we're done !
807
808 END IF;
809
810 END IF;
811
812 END LOOP;
813 CLOSE c_get_rules;
814
815 x_match_found := l_match_found;
816
817 EXCEPTION
818
819 WHEN OTHERS THEN
820 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
821 x_msg_count := l_msg_count ;
822 x_msg_data := l_msg_data ;
823
824 END search_combi;
825
826 END OKL_COMBI_CASH_APP_RLS_PVT;