[Home] [Help]
PACKAGE BODY: APPS.OKL_AUTO_CASH_APPL_RULES_PVT
Source
1 PACKAGE BODY okl_auto_cash_appl_rules_pvt AS
2 /* $Header: OKLRACUB.pls 120.34.12010000.5 2010/06/03 10:01:34 sosharma ship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7
8 --asawanka added for llca start
9 PROCEDURE log_debug(p_message IN varchar2)
10 IS
11 BEGIN
12 -- IF (is_debug_statement_on) THEN
13 -- dbms_output.put_line(p_message);
14 --okl_debug_pub.LogMessage(p_message,l_level_statement,'Y');
15 -- END IF;
16 null;
17 END log_debug;
18 --asawanka added for llca end
19
20 --Begin - varangan - receipts project
21 ---------------------------------------------------------------------------
22 -- PROCEDURE Cash Application Rules for AR Invoice Number
23 ---------------------------------------------------------------------------
24 /*Description: This procedure accepts AR invoice number as a primary input parameter
25 and process the cash application rule logic based on the
26 way of contracts grouped together under this AR invoice.
27 IN Parameters:
28 p_customer_num - Holds the Customer Number
29 p_arinv_number - AR Invoice Number
30 p_currency_code - Receipt Currency Code
31 p_check_number - Check Number
32 p_rcpt_amount - Receipt Number
33 p_arinv_id - AR Invoice Id
34 p_org_id - Operating Unit
35
36 OUT Parameter
37 x_appl_tbl - Holds the receipt amount split up based on the cash application rules
38 to be applied on each invoice line.
39
40 */
41 PROCEDURE auto_cashapp_for_arinv (
42 p_api_version IN NUMBER
43 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
44 ,x_return_status OUT NOCOPY VARCHAR2
45 ,x_msg_count OUT NOCOPY NUMBER
46 ,x_msg_data OUT NOCOPY VARCHAR2
47 ,p_customer_num IN VARCHAR2 DEFAULT NULL
48 ,p_arinv_number IN VARCHAR2 DEFAULT NULL
49 ,p_currency_code IN VARCHAR2
50 ,p_amount_app_to IN NUMBER DEFAULT NULL
51 ,p_amount_app_from IN NUMBER DEFAULT NULL
52 ,p_inv_to_rct_rate IN NUMBER DEFAULT NULL
53 ,p_receipt_date IN DATE
54 ,p_arinv_id IN NUMBER DEFAULT NULL
55 ,p_org_id IN Number
56 ,x_appl_tbl OUT NOCOPY okl_appl_dtls_tbl_type
57 ,x_onacc_amount OUT NOCOPY NUMBER
58 ,x_unapply_amount OUT NOCOPY NUMBER
59 ) IS
60
61 ---------------------------
62 -- DECLARE Local Variables
63 ---------------------------
64
65 l_inv_ref VARCHAR2(120) := p_arinv_number;
66 l_org_id Number :=p_org_id;
67 l_currency_code VARCHAR2(45) := p_currency_code;
68 l_amount_app_from NUMBER := p_amount_app_from;
69 l_amount_app_to NUMBER := p_amount_app_to;
70 l_inv_to_rct_rate NUMBER := p_inv_to_rct_rate;
71 l_inv_curr_Code VARCHAR2(45);
72 l_receipt_Date DATE := p_receipt_date;
73 l_cross_curr_enabled varchar2(3):='N';
74 l_conversion_rate NUMBER;
75 l_exchange_rate_type VARCHAR2(45);
76 l_orig_rcpt_amount NUMBER := p_amount_app_to;
77 l_due_date DATE DEFAULT NULL;
78 l_customer_id NUMBER;
79 l_customer_num VARCHAR2(30) := p_customer_num;
80 l_cons_bill_num VARCHAR2(90);
81 l_last_contract_id OKC_K_HEADERS_V.ID%TYPE DEFAULT 1;
82 l_contract_id NUMBER;
83 l_contract_num VARCHAR2(120);
84 l_contract_number_start_date OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT NULL;
85 l_contract_number_id OKC_K_HEADERS_V.ID%TYPE DEFAULT NULL;
86
87 l_receivables_invoice_num NUMBER DEFAULT NULL;
88 l_over_pay VARCHAR(1) DEFAULT NULL;
89 l_ordered CONSTANT VARCHAR2(3) := 'ODD';
90 l_prorate CONSTANT VARCHAR2(3) := 'PRO';
91 i NUMBER;
92 j NUMBER;
93 k NUMBER;
94 d NUMBER DEFAULT NULL;
95
96 l_first_prorate_rec NUMBER DEFAULT NULL;
97 l_first_prorate_rec_j NUMBER DEFAULT NULL;
98
99 l_appl_tolerance NUMBER;
100 l_temp_val NUMBER;
101 l_inv_tot NUMBER := 0;
102 l_cont_tot NUMBER := 0;
103 l_pro_rate_inv_total NUMBER := 0;
104 l_line_tot NUMBER := 0;
105 l_diff_amount NUMBER := 0;
106 l_inv_total_amt NUMBER := 0;
107 l_tot_amt_app_from NUMBER := 0;
108
109 l_start_date DATE;
110 l_same_date VARCHAR(1) DEFAULT NULL;
111 l_same_cash_app_rule VARCHAR(1) DEFAULT NULL;
112
113 l_count NUMBER DEFAULT NULL;
114 l_rct_id OKL_TRX_CSH_RECEIPT_V.ID%TYPE;
115 l_rca_id OKL_TXL_RCPT_APPS_V.ID%TYPE;
116 -- l_xcr_id OKL_EXT_CSH_RCPTS_V.ID%TYPE;
117 l_check_cau_id OKL_CSH_ALLCTN_RL_HDR.ID%TYPE DEFAULT NULL;
118 l_cau_id OKL_CSH_ALLCTN_RL_HDR.ID%TYPE DEFAULT NULL;
119 l_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
120 l_sty_id OKL_CNSLD_AR_STRMS_V.STY_ID%TYPE;
121 l_tolerance OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE;
122 l_days_past_quote_valid OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE;
123 l_months_to_bill_ahead OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE;
124 l_under_payment OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE;
125 l_over_payment OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE;
126 l_receipt_msmtch OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE;
127 l_api_version NUMBER := 1.0;
128 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
129 l_return_status VARCHAR2(1);
130 l_msg_count NUMBER;
131 l_msg_data VARCHAR2(2000);
132
133 -- Begin - local variables for Receipts Project - varangan
134 --Record/Table Definitions
135 l_rcpt_tbl okl_rcpt_dtls_tbl_type;
136 l_appl_tbl okl_appl_dtls_tbl_type;
137 l_ar_inv_num VARCHAR2(120);
138 l_amount_apply_pref VARCHAR2(15) := 'PRORATE';
139 l_original_line_amount NUMBER;
140 l_original_tax_amount NUMBER;
141 l_total_amount NUMBER;
142 l_has_invoices BOOLEAN := FALSE;
143 No_Open_Invoices_Exception EXCEPTION;
144 -- End - local variables for Receipts Project - varangan
145
146 -------------------
147 -- DECLARE Cursors
148 -------------------
149 --Cursor to fetch the open invoice lines for given AR Invoice Number and Contract number
150 CURSOR c_open_invs1 ( cp_arinv_num IN VARCHAR2,
151 cp_org_id IN Number
152 ) IS
153 SELECT
154 AR_INVOICE_ID Ar_Invoice_Id,
155 Invoice_Number Invoice_number,
156 invoice_currency_code ,
157 INVOICE_LINE_ID invoice_line_id,
158 Line_Identifier Line_Number,
159 amount_due_remaining amount_due_remaining,
160 line_identifier Line_Identifier,
161 sty_id,
162 CONTRACT_NUMBER
163 From OKL_RCPT_ARINV_BALANCES_UV
164 Where Invoice_Number = cp_arinv_num
165 And Org_id = cp_org_id
166 --asawanka changed for bug #5391874
167 AND customer_account_number = nvl(p_customer_num,customer_account_number)
168 AND status = 'OP';
169
170 c_all_open_invs_rec c_open_invs1%ROWTYPE;
171 TYPE open_inv_tbl_type IS TABLE OF c_open_invs1%ROWTYPE INDEX BY BINARY_INTEGER;
172 open_inv_tbl open_inv_tbl_type;
173 open_inv_contract_tbl open_inv_tbl_type;
174
175 ----------
176
177 -- Cursor to fetch contract Id and contract start date with the AR invoice Number
178
179 Cursor c_inv_date (cp_arinv_num IN VARCHAR2 , cp_org_id Number) IS
180 SELECT A.Id contract_id , A.start_date Start_Date
181 From Okc_k_headers_all_b A, OKL_RCPT_ARINV_BALANCES_UV B
182 Where B.Invoice_Number = cp_arinv_num
183 and a.contract_number = b.contract_number
184 And b.org_id= cp_org_id;
185
186 ----------
187 -- Cursor to get the Stream type Id for the given allocation order.
188 CURSOR c_stream_alloc ( cp_str_all_type IN VARCHAR2
189 ,cp_cat_id IN NUMBER ) IS
190 SELECT sty_id
191 FROM OKL_STRM_TYP_ALLOCS
192 WHERE stream_allc_type = cp_str_all_type
193 AND cat_id = cp_cat_id
194 ORDER BY sequence_number;
195
196 ----------
197 -- Cursor to fetch the Cash Application Rule for the given Contract Id
198
199 CURSOR c_cash_rle_id_csr ( cp_khr_id IN NUMBER) IS
200 SELECT to_number(a.object1_id1)
201 FROM OKC_RULES_B a, OKC_RULE_GROUPS_B b
202 WHERE a.rgp_id = b.id
203 AND b.rgd_code = 'LABILL'
204 AND a.rule_information_category = 'LAINVD'
205 AND a.dnz_chr_id = b.chr_id
206 AND a.dnz_chr_id = cp_khr_id;
207
208 ----------
209 -- Cursor to fetch the cash application rule details for the given CAR Id
210 CURSOR c_cash_rule_csr ( cp_cau_id IN NUMBER ) IS
211 SELECT ID
212 ,AMOUNT_TOLERANCE_PERCENT
213 ,DAYS_PAST_QUOTE_VALID_TOLERANC
214 ,MONTHS_TO_BILL_AHEAD
215 ,UNDER_PAYMENT_ALLOCATION_CODE
216 ,OVER_PAYMENT_ALLOCATION_CODE
217 ,RECEIPT_MSMTCH_ALLOCATION_CODE
218 FROM OKL_CASH_ALLCTN_RLS
219 WHERE CAU_ID = cp_cau_id
220 AND START_DATE <= trunc(SYSDATE)
221 AND (END_DATE >= trunc(SYSDATE) OR END_DATE IS NULL);
222 ----------
223
224 -- Cursor to fetch contract id , contract number group under an AR Invoice Number
225 CURSOR c_get_contract_num (l_inv_ref IN VARCHAR2,l_org_id IN NUMBER) IS
226 SELECT lpt.khr_id contract_id, lpt.CONTRACT_NUMBER contract_number
227 FROM OKL_RCPT_ARINV_BALANCES_UV lpt
228 WHERE lpt.INVOICE_NUMBER = l_inv_ref
229 AND lpt.amount_due_remaining > 0
230 And lpt.org_id= l_org_id
231 ORDER BY lpt.start_date;
232
233 --
234 CURSOR c_get_arinv ( l_inv_ref IN VARCHAR2,cp_org_id IN NUMBER) IS
235 SELECT TRX_NUMBER, invoice_currency_code
236 FROM RA_CUSTOMER_TRX_ALL
237 WHERE TRX_NUMBER= l_inv_ref
238 AND org_id = cp_org_id;
239
240 l_trx_number NUMBER;
241
242 l_unapply_amount NUMBER :=0;
243 l_onacc_amount NUMBER :=0;
244
245 BEGIN
246 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
247 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Cash Application Rules - AR Invoice Number - '||l_ar_inv_num);
248 END IF;
249 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
250 /* Begin - validate AR Invoice Reference and Org Id */
251 If p_arinv_number is Null and P_arinv_id Is Null Then
252 x_return_status := OKC_API.G_RET_STS_ERROR;
253 OKC_API.set_message( p_app_name => G_APP_NAME
254 ,p_msg_name => 'OKL_REQUIRED_VALUE'
255 ,p_token1 => 'COL_NAME'
256 ,p_token1_value => 'Either p_arinv_number or P_arinv_id'
257 );
258 RAISE G_EXCEPTION_HALT_VALIDATION;
259 End If;
260
261 If p_org_id Is Null Then
262 x_return_status := OKC_API.G_RET_STS_ERROR;
263 OKC_API.set_message( p_app_name => G_APP_NAME
264 ,p_msg_name => 'OKL_REQUIRED_VALUE'
265 ,p_token1 => 'COL_NAME'
266 ,p_token1_value => 'Org_Id'
267 );
268 RAISE G_EXCEPTION_HALT_VALIDATION;
269 End If;
270 --Check whether AR Invoice number provided is having any open invoices or not
271 l_has_invoices := FALSE;
272 OPEN c_get_arinv ( p_arinv_number,p_org_id);
273 LOOP
274 FETCH c_get_arinv INTO l_trx_number,l_inv_curr_code;
275 EXIT WHEN c_get_arinv%NOTFOUND;
276 l_has_invoices := TRUE;
277 END LOOP;
278 CLOSE c_get_arinv;
279
280 IF (l_has_invoices = FALSE) THEN
281 RAISE No_Open_Invoices_Exception;
282 END IF;
283
284 /* End - validate AR Invoice Reference and Org Id */
285 i := 0;
286 j := 0;
287 l_inv_ref := p_arinv_number;
288 l_org_id := p_org_id;
289
290 OPEN c_inv_date(l_inv_ref,l_org_id);
291 FETCH c_inv_date INTO l_contract_number_id , l_start_date;
292 CLOSE c_inv_date;
293 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
294 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_contract_number_id , l_start_date : '||
295 l_contract_number_id||', '||l_start_date);
296 END IF;
297 d := 0;
298 log_debug('l_inv_ref = '||l_inv_ref);
299 log_debug('l_org_id = '|| l_org_id);
300 FOR c_inv_date_rec IN c_inv_date(l_inv_ref,l_org_id)
301 LOOP
302 IF TRUNC(l_start_date) = TRUNC(c_inv_date_rec.start_date) THEN
303 l_same_date := 'Y';
304 d := d + 1;
305 ELSE
306 l_same_date := 'N';
307 EXIT;
308 END IF;
309
310 END LOOP;
311
312 IF d = 1 THEN
313 l_same_date := 'N';
314 END IF;
315 log_debug('l_same_date = '||l_same_date);
316 -- ************************************************
317 -- Check for same cash application rule
318 -- ************************************************
319
320 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
321 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Check for same cash application rule');
322 END IF;
323 OPEN c_cash_rle_id_csr (l_contract_number_id);
324 FETCH c_cash_rle_id_csr INTO l_cau_id;
325 CLOSE c_cash_rle_id_csr;
326
327 d := 0;
328 FOR c_inv_date_rec IN c_inv_date(l_inv_ref,l_org_id)
329 LOOP
330
331 l_check_cau_id := NULL;
332
333 OPEN c_cash_rle_id_csr (c_inv_date_rec.contract_id);
334 FETCH c_cash_rle_id_csr INTO l_check_cau_id;
335 CLOSE c_cash_rle_id_csr;
336
337 IF l_check_cau_id IS NULL THEN
338 l_same_cash_app_rule := 'N';
339 EXIT;
340 END IF;
341
342 IF l_cau_id = l_check_cau_id THEN
343 l_same_cash_app_rule := 'Y';
344 d := d + 1;
345 ELSE
346 l_same_cash_app_rule := 'N';
347 EXIT;
348 END IF;
349
350 END LOOP;
351
352 IF d = 1 THEN
353 l_same_cash_app_rule := 'N';
354 END IF;
355 log_debug('l_same_cash_app_rule ='||l_same_cash_app_rule);
356 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
357 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_same_date, l_same_cash_app_rule : '||
358 l_same_date||', '||l_same_cash_app_rule);
359 END IF;
360 log_debug('Receipt Currency = '||l_currency_code);
361 log_debug('Invoice Currency = '||l_inv_curr_Code);
362 log_debug('l_amount_app_from = '||l_amount_app_from);
363 log_debug('l_amount_app_to = '||l_amount_app_to);
364 log_debug('l_inv_to_rct_rate = '||l_inv_to_rct_rate);
365
366 IF l_currency_code = l_inv_curr_Code THEN
367 IF l_amount_app_from IS NULL AND l_amount_app_to IS NULL THEN
368 OKL_API.set_message( p_app_name => G_APP_NAME
369 ,p_msg_name => 'OKL_BPD_INVALID_PARAMS'
370 );
371 RAISE G_EXCEPTION_HALT_VALIDATION;
372 ELSIF l_amount_app_from IS NULL THEN
373 l_amount_app_from := l_amount_app_to;
374 ELSE
375 l_amount_app_to := l_amount_app_from;
376 END IF;
377 ELSE
378 IF l_amount_app_from IS NOT NULL AND l_amount_app_to IS NOT NULL AND l_inv_to_rct_rate IS NOT NULL THEN
379 IF ( l_amount_app_to * l_inv_to_rct_rate) <> l_amount_app_from THEN
380 OKL_API.set_message( p_app_name => G_APP_NAME
381 ,p_msg_name => 'OKL_BPD_PARAMS_MISMATCH'
382 );
383 END IF;
384 IF l_inv_to_rct_rate <> 0 and ( ( l_amount_app_from / l_inv_to_rct_rate) <> l_amount_app_to) THEN
385 OKL_API.set_message( p_app_name => G_APP_NAME
386 ,p_msg_name => 'OKL_BPD_PARAMS_MISMATCH'
387 );
388 END IF;
389 END IF;
390 /* l_cross_curr_enabled := nvl(FND_PROFILE.value('AR_ENABLE_CROSS_CURRENCY'),'N');
391 log_debug('l_cross_curr_enabled = '||l_cross_curr_enabled);
392 IF l_cross_curr_enabled <> 'Y' THEN
393 OKL_API.set_message( p_app_name => G_APP_NAME
394 ,p_msg_name => 'OKL_BPD_CROSS_CURR_NA'
395 );
396 RAISE G_EXCEPTION_HALT_VALIDATION;
397 ELSE*/
398 IF l_inv_to_rct_rate is null THEN
399 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
400 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
401 IF l_exchange_rate_type IS NULL THEN
402 OKL_API.set_message( p_app_name => G_APP_NAME
403 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
404 );
405 RAISE G_EXCEPTION_HALT_VALIDATION;
406 ELSE
407 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
408 ,l_currency_code
409 ,l_receipt_date
410 ,l_exchange_rate_type
411 );
412
413 IF l_conversion_rate IN (0,-1) THEN
414
415 -- Message Text: No exchange rate defined
416 x_return_status := okl_api.G_RET_STS_ERROR;
417 okl_api.set_message( p_app_name => G_APP_NAME,
418 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
419 RAISE G_EXCEPTION_HALT_VALIDATION;
420 END IF;
421 END IF;
422 ELSE
423 l_conversion_rate := l_inv_to_rct_rate;
424 END IF;
425 log_debug('l_conversion_rate ='||l_conversion_rate);
426 IF l_amount_app_from IS NULL AND l_amount_app_to IS NULL THEN
427 OKL_API.set_message( p_app_name => G_APP_NAME
428 ,p_msg_name => 'OKL_BPD_INVALID_PARAMS'
429 );
430 RAISE G_EXCEPTION_HALT_VALIDATION;
431 ELSIF l_amount_app_from IS NULL THEN
432 l_amount_app_from := l_amount_app_to * l_conversion_rate;
433 ELSE
434 l_amount_app_to := l_amount_app_from * (1/l_conversion_rate);
435 END IF;
436 -- END IF;
437 END IF;
438 l_amount_app_from := arp_util.CurrRound(l_amount_app_from,l_currency_code);
439 l_amount_app_to := arp_util.CurrRound(l_amount_app_to,l_inv_curr_code);
440
441 --22--Apr-2008 ankushar Bug# 6978225, Passed the value to l_orig_rcpt_amount
442 l_orig_rcpt_amount := l_amount_app_to;
443 --22-Apr-2008 ankushar End Changes
444
445 log_debug('l_amount_app_from = '||l_amount_app_from);
446 log_debug('l_amount_app_to = '||l_amount_app_to);
447 log_debug('l_inv_to_rct_rate = '||l_inv_to_rct_rate);
448 IF l_same_date = 'Y' THEN
449
450 IF l_same_cash_app_rule = 'Y' THEN -- Use Common Cash Application
451 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
452 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Date and CAR are same for all contracts ...');
453 END IF;
454 -- *******************************************************
455 -- Start Line level cash application using the same cash
456 -- application rule for all
457 -- *******************************************************
458 OPEN c_cash_rule_csr (l_cau_id);
459 FETCH c_cash_rule_csr
460 INTO l_cat_id
461 ,l_tolerance
462 ,l_days_past_quote_valid
463 ,l_months_to_bill_ahead
464 ,l_under_payment
465 ,l_over_payment
466 ,l_receipt_msmtch;
467 CLOSE c_cash_rule_csr;
468 Elsif l_same_cash_app_rule = 'N' THEN -- Use Default Cash Application
469 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
470 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Same Date but different CAR for all contracts ...');
471 END IF;
472 -- *******************************************************
473 -- Start Line level cash application using the same cash
474 -- application rule for all
475 -- *******************************************************
476 Get_Default_Cash_App_Rule(
477 l_org_id
478 ,l_cat_id
479 ,l_tolerance
480 ,l_days_past_quote_valid
481 ,l_months_to_bill_ahead
482 ,l_under_payment
483 ,l_over_payment
484 ,l_receipt_msmtch);
485 End If;
486 -- ************************************************
487 -- Line level cash application processing BEGINS
488 -- ************************************************
489
490 -- Get Line total
491
492 l_line_tot := 0;
493 i := 0;
494 FOR c_open_invs_rec IN c_open_invs1 (l_inv_ref,l_org_id)
495 LOOP
496 IF c_open_invs_rec.amount_due_remaining > 0 THEN
497 i := i + 1;
498 open_inv_tbl(i) := c_open_invs_rec;
499 l_inv_curr_Code := c_open_invs_rec.invoice_currency_code;
500 l_line_tot := l_line_tot + c_open_invs_rec.amount_due_remaining;
501 END IF;
502 END LOOP;
503
504 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
505 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_line_tot : ' || l_line_tot);
506 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_amount_app_to : ' || l_amount_app_to);
507 END IF;
508 -- calculate tolerance
509 IF l_line_tot > l_amount_app_to THEN
510 l_appl_tolerance := l_line_tot * (1 - l_tolerance / 100);
511 ELSE
512 l_appl_tolerance := l_line_tot;
513 END IF;
514 log_debug('l_line_tot = '||l_line_tot);
515 log_debug('l_amount_app_to = '||l_amount_app_to);
516 log_debug('l_appl_tolerance = '||l_appl_tolerance);
517 log_debug('l_under_payment = '||l_under_payment);
518 IF l_line_tot > l_amount_app_to AND l_appl_tolerance > l_amount_app_to THEN -- UNDERPAYMENT (2)
519
520 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
521 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'UNDERPAYMENT ...');
522 END IF;
523 IF l_under_payment IN ('U','u') THEN -- Unapply underpayment (3)
524
525 IF l_currency_code = l_inv_curr_code THEN
526 l_unapply_amount:=l_amount_app_to;
527 ELSE
528 l_unapply_amount:= l_amount_app_to * l_conversion_rate;
529 l_unapply_amount := GET_ROUNDED_AMOUNT(l_unapply_amount,l_currency_code);
530 END IF;
531
532 ELSIF l_under_payment IN ('T','t') THEN -- ORDERED (3)
533
534 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
535 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ORDERED ...');
536 END IF;
537 OPEN c_stream_alloc (l_ordered, l_cat_id);
538 LOOP
539 FETCH c_stream_alloc INTO l_sty_id;
540 EXIT WHEN c_stream_alloc%NOTFOUND OR l_amount_app_to = 0 OR l_amount_app_to IS NULL;
541
542 FOR i IN open_inv_tbl.FIRST..open_inv_tbl.LAST
543 LOOP
544 c_all_open_invs_rec := open_inv_tbl(i);
545 EXIT WHEN l_amount_app_to = 0 OR l_amount_app_to IS NULL;
546 IF c_all_open_invs_rec.sty_id = l_sty_id THEN
547 j := j + 1;
548 --Populate receipt table
549 l_rcpt_tbl(j).INVOICE_ID := c_all_open_invs_rec.ar_invoice_id;
550 l_rcpt_tbl(j).INVOICE_NUMBER := c_all_open_invs_rec.invoice_number;
551 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_code;
552 l_rcpt_tbl(j).INVOICE_LINE_ID := c_all_open_invs_rec.invoice_line_id;
553 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_all_open_invs_rec.line_identifier;
554 --Code without linelevel prorate
555 --Amount Applied will be total amount applied including line and tax
556 l_rcpt_tbl(j).AMOUNT_APPLIED := c_all_open_invs_rec.amount_due_remaining;
557 IF l_amount_app_to < l_rcpt_tbl(j).AMOUNT_APPLIED THEN
558 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
559 l_amount_app_to := 0;
560 ELSE
561 l_amount_app_to := l_amount_app_to - l_rcpt_tbl(j).AMOUNT_APPLIED;
562 END IF;
563
564 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
565 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
566 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).INVOICE_LINE_NUMBER : '||l_rcpt_tbl(j).INVOICE_LINE_NUMBER);
567 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
568 END IF;
569 IF l_currency_code <> l_inv_curr_code THEN
570 IF l_inv_to_rct_rate is null THEN
571 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
572 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
573 IF l_exchange_rate_type IS NULL THEN
574 OKL_API.set_message( p_app_name => G_APP_NAME
575 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
576 );
577 RAISE G_EXCEPTION_HALT_VALIDATION;
578 ELSE
579 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
580 ,l_currency_code
581 ,l_receipt_date
582 ,l_exchange_rate_type
583 );
584
585 IF l_conversion_rate IN (0,-1) THEN
586
587 -- Message Text: No exchange rate defined
588 x_return_status := okl_api.G_RET_STS_ERROR;
589 okl_api.set_message( p_app_name => G_APP_NAME,
590 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
591 RAISE G_EXCEPTION_HALT_VALIDATION;
592 END IF;
593 END IF;
594 ELSE
595 l_conversion_rate := l_inv_to_rct_rate;
596 END IF;
597 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
598 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
599 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
600 ELSE
601 l_rcpt_tbl(j).trans_to_receipt_rate := null;
602 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
603 END IF;
604 END IF;
605 END LOOP;
606 END LOOP;
607 CLOSE c_stream_alloc;
608
609 ELSIF l_under_payment IN ('P','p') THEN -- PRO RATE (3)
610
611 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
612 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'PRO RATE ...');
613 END IF;
614 j := 0;
615 -- obtain all the streams that are part of the pro rate default rule.
616
617 FOR c_stream_alloc_rec IN c_stream_alloc (l_prorate, l_cat_id)
618 LOOP
619
620 l_sty_id := c_stream_alloc_rec.sty_id;
621 FOR i IN open_inv_tbl.FIRST..open_inv_tbl.LAST
622 LOOP
623 c_all_open_invs_rec := open_inv_tbl(i);
624 IF c_all_open_invs_rec.sty_id = l_sty_id THEN
625
626 j := j + 1;
627 --Populate receipt table
628 l_rcpt_tbl(j).INVOICE_ID := c_all_open_invs_rec.ar_invoice_id;
629 l_rcpt_tbl(j).INVOICE_NUMBER := c_all_open_invs_rec.invoice_number;
630 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_code;
631 l_rcpt_tbl(j).INVOICE_LINE_ID := c_all_open_invs_rec.invoice_line_id;
632 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_all_open_invs_rec.line_identifier;
633 l_rcpt_tbl(j).AMOUNT_APPLIED := c_all_open_invs_rec.amount_due_remaining;
634 l_pro_rate_inv_total :=l_pro_rate_inv_total + l_rcpt_tbl(j).AMOUNT_APPLIED;
635 IF l_currency_code <> l_inv_curr_code THEN
636 IF l_inv_to_rct_rate is null THEN
637 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
638 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
639 IF l_exchange_rate_type IS NULL THEN
640 OKL_API.set_message( p_app_name => G_APP_NAME
641 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
642 );
643 RAISE G_EXCEPTION_HALT_VALIDATION;
644 ELSE
645 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
646 ,l_currency_code
647 ,l_receipt_date
648 ,l_exchange_rate_type
649 );
650
651 IF l_conversion_rate IN (0,-1) THEN
652
653 -- Message Text: No exchange rate defined
654 x_return_status := okl_api.G_RET_STS_ERROR;
655 okl_api.set_message( p_app_name => G_APP_NAME,
656 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
657 RAISE G_EXCEPTION_HALT_VALIDATION;
658 END IF;
659 END IF;
660 ELSE
661 l_conversion_rate := l_inv_to_rct_rate;
662 END IF;
663 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
664 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
665 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
666 ELSE
667 l_rcpt_tbl(j).trans_to_receipt_rate := null;
668 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
669 END IF;
670 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
671 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
672 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).INVOICE_LINE_NUMBER : '||l_rcpt_tbl(j).INVOICE_LINE_NUMBER);
673 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
674
675 END IF;
676 END IF;
677 END LOOP; -- c_open_invs
678 END LOOP; -- c_stream_alloc
679
680 -- Calc Pro Ration
681 -- only if total amount of prorated invoices is greater than receipt
682
683 IF l_pro_rate_inv_total IS NULL OR l_pro_rate_inv_total = 0 THEN
684 -- Message Text: No prorated transaction types for contract.
685 x_return_status := OKC_API.G_RET_STS_ERROR;
686 OKC_API.set_message( p_app_name => G_APP_NAME
687 ,p_msg_name => 'OKL_BPD_DEF_NO_PRO'
688 );
689 RAISE G_EXCEPTION_HALT_VALIDATION;
690 END IF;
691 log_debug('l_pro_rate_inv_total = '||l_pro_rate_inv_total);
692 log_debug('l_orig_rcpt_amount = '||l_orig_rcpt_amount);
693 IF (l_pro_rate_inv_total > l_orig_rcpt_amount) THEN
694
695 j := 1;
696 l_temp_val := l_orig_rcpt_amount / l_pro_rate_inv_total;
697
698 LOOP
699 l_rcpt_tbl(j).AMOUNT_APPLIED := l_temp_val * l_rcpt_tbl(j).AMOUNT_APPLIED;
700 l_rcpt_tbl(j).AMOUNT_APPLIED:=GET_ROUNDED_AMOUNT(l_rcpt_tbl(j).AMOUNT_APPLIED,l_inv_curr_code);
701 IF l_currency_code <> l_inv_curr_code THEN
702 IF l_inv_to_rct_rate is null THEN
703 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
704 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
705 IF l_exchange_rate_type IS NULL THEN
706 OKL_API.set_message( p_app_name => G_APP_NAME
707 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
708 );
709 RAISE G_EXCEPTION_HALT_VALIDATION;
710 ELSE
711 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
712 ,l_currency_code
713 ,l_receipt_date
714 ,l_exchange_rate_type
715 );
716
717 IF l_conversion_rate IN (0,-1) THEN
718
719 -- Message Text: No exchange rate defined
720 x_return_status := okl_api.G_RET_STS_ERROR;
721 okl_api.set_message( p_app_name => G_APP_NAME,
722 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
723 RAISE G_EXCEPTION_HALT_VALIDATION;
724 END IF;
725 END IF;
726 ELSE
727 l_conversion_rate := l_inv_to_rct_rate;
728 END IF;
729 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
730 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
731 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
732 ELSE
733 l_rcpt_tbl(j).trans_to_receipt_rate := null;
734 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
735 END IF;
736 l_inv_total_amt := l_inv_total_amt + l_rcpt_tbl(j).AMOUNT_APPLIED;
737 EXIT WHEN (j = l_rcpt_tbl.LAST);
738 j := j + 1;
739 END LOOP;
740 l_diff_amount := l_amount_app_to - l_inv_total_amt;
741 if l_diff_amount > 0 then
742 l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied := l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied + l_diff_amount;
743 IF l_currency_code <> l_inv_curr_code THEN
744 IF l_inv_to_rct_rate is null THEN
745 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
746 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
747 IF l_exchange_rate_type IS NULL THEN
748 OKL_API.set_message( p_app_name => G_APP_NAME
749 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
750 );
751 RAISE G_EXCEPTION_HALT_VALIDATION;
752 ELSE
753 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
754 ,l_currency_code
755 ,l_receipt_date
756 ,l_exchange_rate_type
757 );
758
759 IF l_conversion_rate IN (0,-1) THEN
760
761 -- Message Text: No exchange rate defined
762 x_return_status := okl_api.G_RET_STS_ERROR;
763 okl_api.set_message( p_app_name => G_APP_NAME,
764 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
765 RAISE G_EXCEPTION_HALT_VALIDATION;
766 END IF;
767 END IF;
768 ELSE
769 l_conversion_rate := l_inv_to_rct_rate;
770 END IF;
771 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
772 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
773 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
774 ELSE
775 l_rcpt_tbl(j).trans_to_receipt_rate := null;
776 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
777 END IF;
778 end if;
779 log_debug('l_amount_app_from = '||l_amount_app_from);
780 /* l_diff_amount := l_amount_app_from - l_tot_amt_app_from;
781 if l_diff_amount > 0 then
782 l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied_from := l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied_from + l_diff_amount;
783 end if;*/
784 log_debug('l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied_from = '||l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied_from);
785 END IF; -- bug 5221326
786
787 END IF; -- (3)
788
789 ELSE -- EXACT or OVERPAYMENT or TOLERANCE (2)
790
791 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
792 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'EXACT or OVERPAYMENT or TOLERANCE');
793 END IF;
794 j := 0;
795 -- CREATE LINES TABLE
796 FOR i IN open_inv_tbl.FIRST..open_inv_tbl.LAST
797 LOOP
798 c_all_open_invs_rec := open_inv_tbl(i);
799 EXIT WHEN l_amount_app_to = 0 OR l_amount_app_to IS NULL;
800 j := j + 1;
801 --Populate receipt table
802 l_rcpt_tbl(j).INVOICE_ID := c_all_open_invs_rec.ar_invoice_id;
803 l_rcpt_tbl(j).INVOICE_NUMBER := c_all_open_invs_rec.invoice_number;
804 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_code;
805 l_rcpt_tbl(j).INVOICE_LINE_ID := c_all_open_invs_rec.invoice_line_id;
806 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_all_open_invs_rec.line_identifier;
807 --Amount Applied will be total amount applied including line and tax
808 l_rcpt_tbl(j).AMOUNT_APPLIED := c_all_open_invs_rec.amount_due_remaining;
809 IF l_amount_app_to < l_rcpt_tbl(j).AMOUNT_APPLIED THEN
810 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
811 l_amount_app_to := 0;
812 ELSE
813 l_amount_app_to := l_amount_app_to - l_rcpt_tbl(j).AMOUNT_APPLIED;
814 END IF;
815 IF l_currency_code <> l_inv_curr_code THEN
816 IF l_inv_to_rct_rate is null THEN
817 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
818 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
819 IF l_exchange_rate_type IS NULL THEN
820 OKL_API.set_message( p_app_name => G_APP_NAME
821 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
822 );
823 RAISE G_EXCEPTION_HALT_VALIDATION;
824 ELSE
825 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
826 ,l_currency_code
827 ,l_receipt_date
828 ,l_exchange_rate_type
829 );
830
831 IF l_conversion_rate IN (0,-1) THEN
832
833 -- Message Text: No exchange rate defined
834 x_return_status := okl_api.G_RET_STS_ERROR;
835 okl_api.set_message( p_app_name => G_APP_NAME,
836 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
837 RAISE G_EXCEPTION_HALT_VALIDATION;
838 END IF;
839 END IF;
840 ELSE
841 l_conversion_rate := l_inv_to_rct_rate;
842 END IF;
843 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
844 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
845 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
846 ELSE
847 l_rcpt_tbl(j).trans_to_receipt_rate := null;
848 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
849 END IF;
850 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
851 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
852 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).INVOICE_LINE_NUMBER : '||l_rcpt_tbl(j).INVOICE_LINE_NUMBER);
853 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
854 END IF;
855 END LOOP;
856 -- Apply the remaining balance as per the Cash Application Rule
857
858 If l_over_payment In ('B','b') Then -- Onaccount --move cash to customer balances -OVP
859 IF l_currency_code = l_inv_curr_code THEN
860 l_onacc_amount:=l_amount_app_to;
861 ELSE
862 l_onacc_amount := l_amount_app_to * l_conversion_rate;
863 l_onacc_amount := GET_ROUNDED_AMOUNT(l_onacc_amount,l_currency_code);
864 END IF;
865 Elsif l_over_payment In ('F','f') Then --Unapply -- move cash to unapplied -OVP
866 IF l_currency_code = l_inv_curr_code THEN
867 l_unapply_amount:=l_amount_app_to;
868 ELSE
869 l_unapply_amount:= l_amount_app_to * l_conversion_rate;
870 l_unapply_amount := GET_ROUNDED_AMOUNT(l_unapply_amount,l_currency_code);
871 END IF;
872 End If;
873
874 END IF; -- under payment. (2)
875
876
877 -- **********************************************
878 -- Line level cash application processing ENDS
879 -- **********************************************
880
881 ELSE /*IF l_same_date = 'N' THEN */
882 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
883 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Contract level cash application processing BEGINS');
884 END IF;
885 -- ******************************************************
886 -- Contract level cash application processing BEGINS
887 -- ******************************************************
888 j := 0;
889 open_inv_tbl.delete;
890 FOR c_open_invs_rec IN c_open_invs1 (l_inv_ref,l_org_id)
891 LOOP
892 IF c_open_invs_rec.amount_due_remaining > 0 THEN
893 i := i + 1;
894 open_inv_tbl(i) := c_open_invs_rec;
895 END IF;
896 END LOOP;
897 log_debug('open_inv_tbl.count = '||open_inv_tbl.count);
898 OPEN c_get_contract_num(l_inv_ref,l_org_id);
899 LOOP
900 FETCH c_get_contract_num INTO l_contract_id, l_contract_num;
901 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
902 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_contract_num : '||l_contract_num);
903 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_amount_app_to : '||l_amount_app_to);
904 END IF;
905 log_debug('l_contract_num = '||l_contract_num);
906 log_debug('l_amount_app_to = '||l_amount_app_to);
907 EXIT WHEN c_get_contract_num%NOTFOUND
908 OR l_amount_app_to = 0
909 OR l_amount_app_to IS NULL;
910 IF l_last_contract_id <> l_contract_id THEN -- added by bv
911
912 l_last_contract_id := l_contract_id; -- added by bv
913
914 IF l_contract_num IS NOT NULL THEN
915 OPEN c_cash_rle_id_csr (l_contract_id);
916 FETCH c_cash_rle_id_csr INTO l_cau_id;
917 CLOSE c_cash_rle_id_csr;
918
919 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
920 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cau_id : '||l_cau_id);
921 END IF;
922 IF l_cau_id IS NOT NULL THEN -- Process with Contract's CAR
923 OPEN c_cash_rule_csr (l_cau_id);
924 FETCH c_cash_rule_csr INTO l_cat_id
925 ,l_tolerance
926 ,l_days_past_quote_valid
927 ,l_months_to_bill_ahead
928 ,l_under_payment
929 ,l_over_payment
930 ,l_receipt_msmtch;
931 CLOSE c_cash_rule_csr;
932
933 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
934 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_tolerance : '||l_tolerance);
935 END IF;
936 IF l_tolerance IS NULL THEN -- Use Default CAR
937 -- Process with default cash application rule
938 Get_Default_Cash_App_Rule (
939 l_org_id
940 ,l_cat_id
941 ,l_tolerance
942 ,l_days_past_quote_valid
943 ,l_months_to_bill_ahead
944 ,l_under_payment
945 ,l_over_payment
946 ,l_receipt_msmtch
947 );
948 END IF;
949
950 ELSE /* If CAR is not defined on this contract process with default CAR */
951
952 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
953 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cau_id is null, using default cash appln rule');
954 END IF;
955 Get_Default_Cash_App_Rule (
956 l_org_id
957 ,l_cat_id
958 ,l_tolerance
959 ,l_days_past_quote_valid
960 ,l_months_to_bill_ahead
961 ,l_under_payment
962 ,l_over_payment
963 ,l_receipt_msmtch
964 );
965
966 END IF; /* End Contract's CAR check */
967
968 -- get contract total
969 l_cont_tot := 0;
970 k := 0;
971 FOR i IN open_inv_tbl.FIRST..open_inv_tbl.LAST
972 LOOP
973 IF open_inv_tbl(i).contract_number = l_contract_num THEN
974 k := k + 1;
975 c_all_open_invs_rec := open_inv_tbl(i);
976 open_inv_contract_tbl(k) := open_inv_tbl(i);
977 -- l_invoice_currency_code := c_open_invs_rec.currency_code;
978 l_cont_tot := l_cont_tot + c_all_open_invs_rec.amount_due_remaining;
979 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
980 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cont_tot : '||l_cont_tot);
981 END IF;
982 END IF;
983 END LOOP;
984 log_debug('l_cau_id ='||l_cau_id);
985 IF NVL(l_cau_id, 0) = -1 THEN -- VR 07-Oct-2005 GE-20 Receipts - On Account CAR
986 -- Receipt needs to be left as unapplied
987 j := 1;
988 --Populate receipt table
989 l_rcpt_tbl(j).INVOICE_ID := NULL;
990 l_rcpt_tbl(j).INVOICE_NUMBER := NULL;
991 l_rcpt_tbl(j).INVOICE_LINE_ID := NULL;
992 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := NULL;
993 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_code;
994 --Amount Applied will be total amount applied including line and tax
995 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
996 IF l_currency_code <> l_inv_curr_code THEN
997 IF l_inv_to_rct_rate is null THEN
998 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
999 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
1000 IF l_exchange_rate_type IS NULL THEN
1001 OKL_API.set_message( p_app_name => G_APP_NAME
1002 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
1003 );
1004 RAISE G_EXCEPTION_HALT_VALIDATION;
1005 ELSE
1006 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
1007 ,l_currency_code
1008 ,l_receipt_date
1009 ,l_exchange_rate_type
1010 );
1011
1012 IF l_conversion_rate IN (0,-1) THEN
1013
1014 -- Message Text: No exchange rate defined
1015 x_return_status := okl_api.G_RET_STS_ERROR;
1016 okl_api.set_message( p_app_name => G_APP_NAME,
1017 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
1018 RAISE G_EXCEPTION_HALT_VALIDATION;
1019 END IF;
1020 END IF;
1021 ELSE
1022 l_conversion_rate := l_inv_to_rct_rate;
1023 END IF;
1024 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
1025 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
1026 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
1027 ELSE
1028 l_rcpt_tbl(j).trans_to_receipt_rate := null;
1029 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
1030 END IF;
1031
1032 ELSE
1033 -- calculate tolerance
1034 IF l_cont_tot > l_amount_app_to THEN
1035 l_appl_tolerance := l_cont_tot * (1 - l_tolerance / 100);
1036 ELSE
1037 l_appl_tolerance := l_cont_tot;
1038 END IF;
1039
1040 -- Contract level cash application processing begins.
1041 -- *************************************************
1042 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1043 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Checking UNDERPAYMENT/OVERPAYMENT/MATCH');
1044 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cont_tot : '||l_cont_tot);
1045 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_amount_app_to : '||l_amount_app_to);
1046 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_appl_tolerance : '||l_appl_tolerance);
1047 END IF;
1048 log_debug('l_cont_tot ='||l_cont_tot);
1049 IF l_cont_tot > l_amount_app_to AND l_appl_tolerance > l_amount_app_to THEN -- UNDERPAYMENT (2)
1050 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1051 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'UNDERPAYMENT');
1052 END IF;
1053 log_Debug('l_under_payment ='||l_under_payment);
1054 IF l_under_payment In ('U','u') THEN -- Unapply underpayment (3)
1055 IF l_currency_code = l_inv_curr_code THEN
1056 l_unapply_amount:=l_amount_app_to;
1057 ELSE
1058 l_unapply_amount:= l_amount_app_to * l_conversion_rate;
1059 l_unapply_amount := GET_ROUNDED_AMOUNT(l_unapply_amount,l_currency_code);
1060 END IF;
1061
1062 ELSIF l_under_payment IN ('T','t') THEN -- ORDERED (3)
1063
1064 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1065 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ORDERED ...');
1066 END IF;
1067
1068 OPEN c_stream_alloc (l_ordered, l_cat_id);
1069 LOOP
1070 FETCH c_stream_alloc INTO l_sty_id;
1071 EXIT WHEN c_stream_alloc%NOTFOUND OR l_amount_app_to = 0 OR l_amount_app_to IS NULL;
1072 log_Debug('l_sty_id = '||l_sty_id);
1073
1074 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
1075 LOOP
1076 c_all_open_invs_rec := open_inv_contract_tbl(i);
1077 log_debug('c_all_open_invs_rec.sty_id = '||c_all_open_invs_rec.sty_id);
1078 EXIT WHEN l_amount_app_to = 0 OR l_amount_app_to IS NULL;
1079 IF c_all_open_invs_rec.sty_id = l_sty_id THEN
1080 j := j + 1;
1081 --Populate receipt table
1082 l_rcpt_tbl(j).INVOICE_ID := c_all_open_invs_rec.ar_invoice_id;
1083 l_rcpt_tbl(j).INVOICE_NUMBER := c_all_open_invs_rec.invoice_number;
1084 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_code;
1085 l_rcpt_tbl(j).INVOICE_LINE_ID := c_all_open_invs_rec.invoice_line_id;
1086 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_all_open_invs_rec.line_identifier;
1087 --Code without linelevel prorate
1088 --Amount Applied will be total amount applied including line and tax
1089 l_rcpt_tbl(j).AMOUNT_APPLIED := c_all_open_invs_rec.amount_due_remaining;
1090 IF l_amount_app_to < l_rcpt_tbl(j).AMOUNT_APPLIED THEN
1091 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
1092 l_amount_app_to := 0;
1093 ELSE
1094 l_amount_app_to := l_amount_app_to - l_rcpt_tbl(j).AMOUNT_APPLIED;
1095 END IF;
1096 IF l_currency_code <> l_inv_curr_code THEN
1097 IF l_inv_to_rct_rate is null THEN
1098 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
1099 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
1100 IF l_exchange_rate_type IS NULL THEN
1101 OKL_API.set_message( p_app_name => G_APP_NAME
1102 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
1103 );
1104 RAISE G_EXCEPTION_HALT_VALIDATION;
1105 ELSE
1106 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
1107 ,l_currency_code
1108 ,l_receipt_date
1109 ,l_exchange_rate_type
1110 );
1111
1112 IF l_conversion_rate IN (0,-1) THEN
1113
1114 -- Message Text: No exchange rate defined
1115 x_return_status := okl_api.G_RET_STS_ERROR;
1116 okl_api.set_message( p_app_name => G_APP_NAME,
1117 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
1118 RAISE G_EXCEPTION_HALT_VALIDATION;
1119 END IF;
1120 END IF;
1121 ELSE
1122 l_conversion_rate := l_inv_to_rct_rate;
1123 END IF;
1124 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
1125 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
1126 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
1127 ELSE
1128 l_rcpt_tbl(j).trans_to_receipt_rate := null;
1129 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
1130 END IF;
1131 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1132 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
1133 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).INVOICE_LINE_NUMBER : '||l_rcpt_tbl(j).INVOICE_LINE_NUMBER);
1134 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
1135 END IF;
1136 END IF;
1137 END LOOP;
1138 END LOOP;
1139 CLOSE c_stream_alloc;
1140 /* IF l_rcpt_tbl.count > 0 THEN
1141 l_diff_amount := l_amount_app_from - l_tot_amt_app_from;
1142 if l_diff_amount > 0 then
1143 l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied_from := l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied_from + l_diff_amount;
1144 end if;
1145 end if;*/
1146 ELSIF l_under_payment IN ('P','p') THEN -- PRO RATE (3)
1147 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1148 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'PRO RATE ...');
1149 END IF;
1150
1151 -- obtain all the streams that are part of the pro rate default rule.
1152
1153 FOR c_stream_alloc_rec IN c_stream_alloc (l_prorate, l_cat_id)
1154 LOOP
1155
1156 l_sty_id := c_stream_alloc_rec.sty_id;
1157 log_debug('l_sty_id = '||l_sty_id);
1158 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
1159 LOOP
1160 c_all_open_invs_rec := open_inv_contract_tbl(i);
1161 IF c_all_open_invs_rec.sty_id = l_sty_id THEN
1162 j := j + 1;
1163 --Populate receipt table
1164 l_rcpt_tbl(j).INVOICE_ID := c_all_open_invs_rec.ar_invoice_id;
1165 l_rcpt_tbl(j).INVOICE_NUMBER := c_all_open_invs_rec.invoice_number;
1166 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_code;
1167 l_rcpt_tbl(j).INVOICE_LINE_ID := c_all_open_invs_rec.invoice_line_id;
1168 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_all_open_invs_rec.line_identifier;
1169 l_rcpt_tbl(j).AMOUNT_APPLIED := c_all_open_invs_rec.amount_due_remaining;
1170 IF l_currency_code <> l_inv_curr_code THEN
1171 IF l_inv_to_rct_rate is null THEN
1172 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
1173 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
1174 IF l_exchange_rate_type IS NULL THEN
1175 OKL_API.set_message( p_app_name => G_APP_NAME
1176 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
1177 );
1178 RAISE G_EXCEPTION_HALT_VALIDATION;
1179 ELSE
1180 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
1181 ,l_currency_code
1182 ,l_receipt_date
1183 ,l_exchange_rate_type
1184 );
1185
1186 IF l_conversion_rate IN (0,-1) THEN
1187
1188 -- Message Text: No exchange rate defined
1189 x_return_status := okl_api.G_RET_STS_ERROR;
1190 okl_api.set_message( p_app_name => G_APP_NAME,
1191 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
1192 RAISE G_EXCEPTION_HALT_VALIDATION;
1193 END IF;
1194 END IF;
1195 ELSE
1196 l_conversion_rate := l_inv_to_rct_rate;
1197 END IF;
1198 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
1199 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
1200 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
1201 ELSE
1202 l_rcpt_tbl(j).trans_to_receipt_rate := null;
1203 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
1204 END IF;
1205 l_pro_rate_inv_total :=l_pro_rate_inv_total + l_rcpt_tbl(j).AMOUNT_APPLIED;
1206 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1207 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
1208 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).INVOICE_LINE_NUMBER : '||l_rcpt_tbl(j).INVOICE_LINE_NUMBER);
1209 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
1210 END IF;
1211 END IF;
1212
1213 END LOOP; -- c_open_invs
1214 END LOOP; -- c_stream_alloc
1215
1216 -- Calc Pro Ration
1217 -- only if total amount of prorated invoices is greater than receipt
1218
1219 IF l_pro_rate_inv_total IS NULL OR l_pro_rate_inv_total = 0 THEN
1220 -- Message Text: No prorated transaction types for contract.
1221 x_return_status := OKC_API.G_RET_STS_ERROR;
1222 OKC_API.set_message( p_app_name => G_APP_NAME
1223 ,p_msg_name => 'OKL_BPD_DEF_NO_PRO'
1224 );
1225 RAISE G_EXCEPTION_HALT_VALIDATION;
1226 END IF;
1227
1228 IF (l_pro_rate_inv_total > l_orig_rcpt_amount) THEN
1229 j := 1;
1230 l_temp_val := l_orig_rcpt_amount / l_pro_rate_inv_total;
1231
1232 LOOP
1233 l_rcpt_tbl(j).AMOUNT_APPLIED := l_temp_val * l_rcpt_tbl(j).AMOUNT_APPLIED;
1234 l_rcpt_tbl(j).AMOUNT_APPLIED:=GET_ROUNDED_AMOUNT(l_rcpt_tbl(j).AMOUNT_APPLIED,l_inv_curr_code);
1235 IF l_currency_code <> l_inv_curr_code THEN
1236 IF l_inv_to_rct_rate is null THEN
1237 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
1238 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
1239 IF l_exchange_rate_type IS NULL THEN
1240 OKL_API.set_message( p_app_name => G_APP_NAME
1241 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
1242 );
1243 RAISE G_EXCEPTION_HALT_VALIDATION;
1244 ELSE
1245 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
1246 ,l_currency_code
1247 ,l_receipt_date
1248 ,l_exchange_rate_type
1249 );
1250
1251 IF l_conversion_rate IN (0,-1) THEN
1252
1253 -- Message Text: No exchange rate defined
1254 x_return_status := okl_api.G_RET_STS_ERROR;
1255 okl_api.set_message( p_app_name => G_APP_NAME,
1256 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
1257 RAISE G_EXCEPTION_HALT_VALIDATION;
1258 END IF;
1259 END IF;
1260 ELSE
1261 l_conversion_rate := l_inv_to_rct_rate;
1262 END IF;
1263 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
1264 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
1265 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
1266 ELSE
1267 l_rcpt_tbl(j).trans_to_receipt_rate := null;
1268 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
1269 END IF;
1270 l_inv_total_amt := l_inv_total_amt + l_rcpt_tbl(j).AMOUNT_APPLIED;
1271 EXIT WHEN (j = l_rcpt_tbl.LAST);
1272 j := j + 1;
1273 END LOOP;
1274 l_diff_amount := l_amount_app_to - l_inv_total_amt;
1275 if l_diff_amount > 0 then
1276 l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied := l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied + l_diff_amount;
1277 IF l_currency_code <> l_inv_curr_code THEN
1278 IF l_inv_to_rct_rate is null THEN
1279 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
1280 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
1281 IF l_exchange_rate_type IS NULL THEN
1282 OKL_API.set_message( p_app_name => G_APP_NAME
1283 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
1284 );
1285 RAISE G_EXCEPTION_HALT_VALIDATION;
1286 ELSE
1287 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
1288 ,l_currency_code
1289 ,l_receipt_date
1290 ,l_exchange_rate_type
1291 );
1292
1293 IF l_conversion_rate IN (0,-1) THEN
1294
1295 -- Message Text: No exchange rate defined
1296 x_return_status := okl_api.G_RET_STS_ERROR;
1297 okl_api.set_message( p_app_name => G_APP_NAME,
1298 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
1299 RAISE G_EXCEPTION_HALT_VALIDATION;
1300 END IF;
1301 END IF;
1302 ELSE
1303 l_conversion_rate := l_inv_to_rct_rate;
1304 END IF;
1305 l_rcpt_tbl(l_rcpt_tbl.LAST).trans_to_receipt_rate := l_conversion_rate;
1306
1307 l_rcpt_tbl(l_rcpt_tbl.LAST).AMOUNT_APPLIED_FROM := l_rcpt_tbl(l_rcpt_tbl.LAST).AMOUNT_APPLIED * l_conversion_rate;
1308 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
1309
1310 ELSE
1311 l_rcpt_tbl(l_rcpt_tbl.LAST).trans_to_receipt_rate := null;
1312 l_rcpt_tbl(l_rcpt_tbl.LAST).AMOUNT_APPLIED_FROM := null;
1313 END IF;
1314 end if;
1315 log_debug('l_amount_app_from = '||l_amount_app_from);
1316
1317 /* if l_diff_amount > 0 then
1318 l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied_from := l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied_from + l_diff_amount;
1319 END IF;*/
1320 log_debug('l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied_from = '||l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied_from);
1321 END IF;-- bug 5221326
1322 END IF; -- (3)
1323 ELSE -- EXACT or OVERPAYMENT or TOLERANCE (2)
1324 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1325 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'EXACT or OVERPAYMENT or TOLERANCE');
1326 END IF;
1327
1328 -- CREATE LINES TABLE
1329 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
1330 LOOP
1331 c_all_open_invs_rec := open_inv_contract_tbl(i);
1332 EXIT WHEN l_amount_app_to = 0 OR l_amount_app_to IS NULL;
1333 j := j + 1;
1334 --Populate receipt table
1335 l_rcpt_tbl(j).INVOICE_ID := c_all_open_invs_rec.ar_invoice_id;
1336 l_rcpt_tbl(j).INVOICE_NUMBER := c_all_open_invs_rec.invoice_number;
1337 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_code;
1338 l_rcpt_tbl(j).INVOICE_LINE_ID := c_all_open_invs_rec.invoice_line_id;
1339 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_all_open_invs_rec.line_identifier;
1340 --Amount Applied will be total amount applied including line and tax
1341 l_rcpt_tbl(j).AMOUNT_APPLIED := c_all_open_invs_rec.amount_due_remaining;
1342 IF l_amount_app_to < l_rcpt_tbl(j).AMOUNT_APPLIED THEN
1343 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
1344 l_amount_app_to := 0;
1345 ELSE
1346 l_amount_app_to := l_amount_app_to - l_rcpt_tbl(j).AMOUNT_APPLIED;
1347 END IF;
1348 IF l_currency_code <> l_inv_curr_code THEN
1349 IF l_inv_to_rct_rate is null THEN
1350 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
1351 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
1352 IF l_exchange_rate_type IS NULL THEN
1353 OKL_API.set_message( p_app_name => G_APP_NAME
1354 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
1355 );
1356 RAISE G_EXCEPTION_HALT_VALIDATION;
1357 ELSE
1358 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
1359 ,l_currency_code
1360 ,l_receipt_date
1361 ,l_exchange_rate_type
1362 );
1363
1364 IF l_conversion_rate IN (0,-1) THEN
1365
1366 -- Message Text: No exchange rate defined
1367 x_return_status := okl_api.G_RET_STS_ERROR;
1368 okl_api.set_message( p_app_name => G_APP_NAME,
1369 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
1370 RAISE G_EXCEPTION_HALT_VALIDATION;
1371 END IF;
1372 END IF;
1373 ELSE
1374 l_conversion_rate := l_inv_to_rct_rate;
1375 END IF;
1376 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
1377 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
1378 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
1379 ELSE
1380 l_rcpt_tbl(j).trans_to_receipt_rate := null;
1381 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
1382 END IF;
1383 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1384 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
1385 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).INVOICE_LINE_NUMBER : '||l_rcpt_tbl(j).INVOICE_LINE_NUMBER);
1386 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
1387 END IF;
1388 END LOOP;
1389
1390 -- Apply the remaining balance as per the Cash Application Rule
1391 If l_over_payment In ('B','b') Then -- Onaccount --move cash to customer balances -OVP
1392 IF l_currency_code = l_inv_curr_code THEN
1393 l_onacc_amount:=l_amount_app_to;
1394 ELSE
1395 l_onacc_amount:= l_amount_app_to * l_conversion_rate;
1396 l_onacc_amount := GET_ROUNDED_AMOUNT(l_onacc_amount,l_currency_code);
1397 END IF;
1398 Elsif l_over_payment In ('F','f') Then --Unapply -- move cash to unapplied -OVP
1399 IF l_currency_code = l_inv_curr_code THEN
1400 l_unapply_amount:=l_amount_app_to;
1401 ELSE
1402 l_unapply_amount:= l_amount_app_to * l_conversion_rate;
1403 l_unapply_amount := GET_ROUNDED_AMOUNT(l_unapply_amount,l_currency_code);
1404 END IF;
1405 End If;
1406
1407 END IF; -- under payment. (2)
1408 END IF; -- VR 07-Oct-2005 GE-20 Receipts - On Account CAR
1409 -- **********************************************
1410 -- Contract level cash application processing ends.
1411 -- *************************************************
1412
1413 END IF; /* l_contract_num IS NOT NULL check Ends*/
1414 END IF; /* l_last_contract_id <> l_contract_id check Ends */
1415
1416 END LOOP;
1417 CLOSE c_get_contract_num;
1418 END IF; /* Process if l_same_date = 'N' : Ends */
1419 log_debug('Calling get_applications');
1420
1421 GET_APPLICATIONS(l_rcpt_tbl,x_appl_tbl);
1422
1423 log_debug('Called get_applications');
1424 l_tot_amt_app_from := 0;
1425 IF x_appl_tbl.COUNT > 0 THEN
1426 IF x_appl_tbl(x_appl_tbl.FIRST).inv_hdr_rec.trans_to_receipt_rate IS NOT NULL THEN
1427 FOR ll IN x_appl_tbl.FIRST..x_appl_tbl.LAST LOOP
1428 l_tot_amt_app_from := l_tot_amt_app_from + nvl(x_appl_tbl(ll).inv_hdr_rec.amount_applied_from,0);
1429 END LOOP;
1430 IF (l_onacc_amount + l_unapply_amount + l_tot_amt_app_from ) <> l_amount_app_from THEN
1431 l_diff_amount := l_amount_app_from - (l_onacc_amount + l_unapply_amount + l_tot_amt_app_from );
1432 log_debug('l_diff_amount = '||l_diff_amount);
1433 IF nvl(l_onacc_amount,0) <> 0 THEN
1434 l_onacc_amount := l_onacc_amount + l_diff_amount;
1435 log_debug('l_onacc_amount = '||l_onacc_amount);
1436 ELSIF nvl(l_unapply_amount,0) <> 0 THEN
1437 l_unapply_amount := l_unapply_amount + l_diff_amount;
1438 END IF;
1439 END IF;
1440 END IF;
1441 END IF;
1442
1443 x_onacc_amount :=l_onacc_amount;
1444 x_unapply_amount :=l_unapply_amount;
1445
1446 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1447 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1448 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1449 RAISE OKL_API.G_EXCEPTION_ERROR;
1450 END IF;
1451 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1452 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done execution of OKL_AUTO_CASH_APPL_RULES_PVT.auto_cashapp_for_arinv ...');
1453 END IF;
1454 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1455 EXCEPTION
1456 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1457 x_return_status := OKL_API.G_RET_STS_ERROR;
1458 x_appl_tbl.delete;
1459
1460 WHEN No_Open_Invoices_Exception THEN
1461 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1462 x_appl_tbl.delete;
1463
1464 WHEN OTHERS THEN
1465 x_appl_tbl.DELETE;
1466 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1467 x_msg_count := l_msg_count ;
1468 x_msg_data := l_msg_data ;
1469
1470
1471 END auto_cashapp_for_arinv;
1472
1473 /*---------------------------------------------------------------------------
1474 -- Cash Application Rules for AR Invoice Number Ends
1475 -- End - varangan - receipts project
1476 ---------------------------------------------------------------------------*/
1477
1478 --START: Bug 6275659 by nikshah
1479
1480 --Identifies all invoice lines to be applied against for a given contract number
1481 --based on CAR setup for the contract
1482 PROCEDURE auto_cashapp_for_contract(p_api_version IN NUMBER
1483 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
1484 ,x_return_status OUT NOCOPY VARCHAR2
1485 ,x_msg_count OUT NOCOPY NUMBER
1486 ,x_msg_data OUT NOCOPY VARCHAR2
1487 ,p_customer_num IN VARCHAR2 DEFAULT NULL
1488 ,p_contract_num IN VARCHAR2 DEFAULT NULL
1489 ,p_currency_code IN VARCHAR2
1490 ,p_amount_app_to IN NUMBER DEFAULT NULL
1491 ,p_amount_app_from IN NUMBER DEFAULT NULL
1492 ,p_inv_to_rct_rate IN NUMBER DEFAULT NULL
1493 ,p_receipt_date IN DATE
1494 ,p_org_id IN NUMBER
1495 ,x_appl_tbl OUT NOCOPY okl_appl_dtls_tbl_type
1496 ,x_onacc_amount OUT NOCOPY NUMBER
1497 ,x_unapply_amount OUT NOCOPY NUMBER
1498 ) IS
1499
1500 --Variables declaration
1501 l_dflt_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
1502 l_dflt_tolerance OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE DEFAULT NULL;
1503 l_dflt_days_past_quote_valid OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE DEFAULT NULL;
1504 l_dflt_months_to_bill_ahead OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE DEFAULT NULL;
1505 l_dflt_under_payment OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
1506 l_dflt_over_payment OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
1507 l_dflt_receipt_msmtch OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE DEFAULT NULL;
1508
1509 l_msg_count NUMBER;
1510 l_msg_data VARCHAR2(2000);
1511
1512 l_contract_id NUMBER;
1513 l_contract_num OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE := p_contract_num;
1514 l_customer_num HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE := p_customer_num;
1515 l_currency_code VARCHAR2(45) := p_currency_code;
1516 l_amount_app_from NUMBER := p_amount_app_from;
1517 l_amount_app_to NUMBER := p_amount_app_to;
1518 l_inv_to_rct_rate NUMBER := p_inv_to_rct_rate;
1519 l_inv_curr_Code VARCHAR2(45);
1520 l_receipt_Date DATE := p_receipt_date;
1521 l_cross_curr_enabled varchar2(3):='N';
1522 l_conversion_rate NUMBER;
1523 l_exchange_rate_type VARCHAR2(45);
1524
1525 l_org_id NUMBER := p_org_id;
1526 j NUMBER;
1527 i NUMBER;
1528
1529 l_over_pay VARCHAR(1) DEFAULT NULL;
1530 l_ordered CONSTANT VARCHAR2(3) := 'ODD';
1531 l_prorate CONSTANT VARCHAR2(3) := 'PRO';
1532 l_appl_tolerance NUMBER;
1533 l_has_invoices BOOLEAN;
1534 l_valid_contract BOOLEAN;
1535 l_temp_val NUMBER;
1536 l_inv_tot NUMBER := 0;
1537 l_pro_rate_inv_total NUMBER := 0;
1538 l_diff_amount NUMBER := 0;
1539 l_inv_total_amt NUMBER := 0;
1540
1541 l_cau_id OKL_CSH_ALLCTN_RL_HDR.ID%TYPE DEFAULT NULL;
1542 l_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
1543 l_sty_id OKL_CNSLD_AR_STRMS_V.STY_ID%TYPE;
1544 l_tolerance OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE;
1545 l_days_past_quote_valid OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE;
1546 l_months_to_bill_ahead OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE;
1547 l_under_payment OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE;
1548 l_over_payment OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE;
1549 l_receipt_msmtch OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE;
1550
1551
1552 --Record/Table Definitions
1553 l_rcpt_tbl okl_auto_cash_appl_rules_pvt.okl_rcpt_dtls_tbl_type;
1554 l_appl_tbl okl_auto_cash_appl_rules_pvt.okl_appl_dtls_tbl_type;
1555 l_tot_amt_app_from NUMBEr := 0;
1556
1557 --Cursor definitions
1558
1559 CURSOR c_open_invoices_contract ( cp_org_id IN NUMBER
1560 ,cp_contract_id IN NUMBER) IS
1561 SELECT AR_INVOICE_ID,
1562 AR_INVOICE_NUMBER,
1563 INVOICE_LINE_ID,
1564 LINE_NUMBER,
1565 AMOUNT_DUE_REMAINING,
1566 STY_ID
1567 FROM OKL_RCPT_CUST_CONT_BALANCES_UV
1568 WHERE KHR_ID = cp_contract_id
1569 AND ORG_ID = cp_org_id
1570 AND STATUS = 'OP';
1571
1572 -- Bug 7022180 by nikshah:
1573 -- Splitted c_open_invoices_contract into two.
1574 -- Removed nvl condition from above cursor and attached direct join in
1575 -- c_open_invoices_contract_cust curose
1576 CURSOR c_open_invoices_contract_cust ( cp_org_id IN NUMBER
1577 ,cp_contract_id IN NUMBER
1578 ,cp_customer_num IN VARCHAR2) IS
1579 SELECT AR_INVOICE_ID,
1580 AR_INVOICE_NUMBER,
1581 INVOICE_LINE_ID,
1582 LINE_NUMBER,
1583 AMOUNT_DUE_REMAINING,
1584 STY_ID
1585 FROM OKL_RCPT_CUST_CONT_BALANCES_UV
1586 WHERE KHR_ID = cp_contract_id
1587 --asawanka changed for bug #5391874
1588 AND CUSTOMER_ACCOUNT_NUMBER = cp_customer_num
1589 AND ORG_ID = cp_org_id
1590 AND STATUS = 'OP';
1591
1592 c_open_invoices_contract_rec c_open_invoices_contract%ROWTYPE;
1593 TYPE open_inv_contract_tbl_type IS TABLE OF c_open_invoices_contract%ROWTYPE INDEX BY BINARY_INTEGER;
1594 open_inv_contract_tbl open_inv_contract_tbl_type;
1595
1596 -- get stream application order
1597 CURSOR c_stream_alloc ( cp_str_all_type IN VARCHAR2
1598 ,cp_cat_id IN NUMBER ) IS
1599 SELECT sty_id
1600 FROM OKL_STRM_TYP_ALLOCS
1601 WHERE stream_allc_type = cp_str_all_type
1602 AND cat_id = cp_cat_id
1603 ORDER BY sequence_number;
1604
1605 CURSOR c_cash_rle_id_csr ( cp_khr_id IN NUMBER) IS
1606 SELECT to_number(a.object1_id1)
1607 FROM OKC_RULES_B a, OKC_RULE_GROUPS_B b
1608 WHERE a.rgp_id = b.id
1609 AND b.rgd_code = 'LABILL'
1610 AND a.rule_information_category = 'LAINVD'
1611 AND a.dnz_chr_id = b.chr_id
1612 AND a.dnz_chr_id = cp_khr_id;
1613
1614 -- get cash applic rule for contract
1615 CURSOR c_cash_rule_csr ( cp_cau_id IN NUMBER ) IS
1616 SELECT ID
1617 ,AMOUNT_TOLERANCE_PERCENT
1618 ,DAYS_PAST_QUOTE_VALID_TOLERANC
1619 ,MONTHS_TO_BILL_AHEAD
1620 ,UNDER_PAYMENT_ALLOCATION_CODE
1621 ,OVER_PAYMENT_ALLOCATION_CODE
1622 ,RECEIPT_MSMTCH_ALLOCATION_CODE
1623 FROM OKL_CASH_ALLCTN_RLS_ALL
1624 WHERE CAU_ID = cp_cau_id
1625 AND START_DATE <= trunc(SYSDATE)
1626 AND (END_DATE >= trunc(SYSDATE) OR END_DATE IS NULL);
1627
1628 -- get a contract id if not known
1629 CURSOR c_get_contract_id (cp_contract_num IN VARCHAR2) IS
1630 SELECT ID CONTRACT_ID, currency_code
1631 FROM OKC_K_HEADERS_ALL_B
1632 WHERE contract_number = cp_contract_num;
1633
1634 l_unapply_amount NUMBER:=0;
1635 l_onacc_amount NUMBER:=0;
1636
1637 BEGIN
1638 IF (G_DEBUG_ENABLED = 'Y') THEN
1639 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1640 END IF;
1641 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1642 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Starting execution of OKL_AUTO_CASH_APPL_RULES_PVT.auto_cashapp_for_contract ...');
1643 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Procedure parameters, p_customer_num : '|| p_customer_num);
1644 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_contract_num : '|| p_contract_num);
1645 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_currency_code : '|| p_currency_code);
1646 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_amount_app_to : '|| p_amount_app_to);
1647 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_org_id : '|| p_org_id);
1648 END IF;
1649
1650 --Check whether contract number provided is having any open invoices or not
1651 l_valid_contract := FALSE;
1652 OPEN c_get_contract_id(l_contract_num);
1653 FETCH c_get_contract_id INTO l_contract_id,l_inv_curr_Code;
1654 IF c_get_contract_id%FOUND THEN
1655 l_valid_contract := TRUE;
1656 END IF;
1657 CLOSE c_get_contract_id;
1658
1659 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Contract is valid');
1660 IF (l_valid_contract = FALSE) THEN
1661 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Contract is not valid');
1662 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1663 RETURN;
1664 ELSE
1665 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Contract is valid');
1666 END IF;
1667
1668 l_has_invoices := FALSE;
1669 i := 0;
1670 IF l_customer_num IS NOT NULL THEN
1671 OPEN c_open_invoices_contract_cust(l_org_id, l_contract_id, l_customer_num);
1672 LOOP
1673 FETCH c_open_invoices_contract_cust INTO c_open_invoices_contract_rec;
1674 EXIT WHEN c_open_invoices_contract_cust%NOTFOUND;
1675 IF c_open_invoices_contract_rec.amount_due_remaining > 0 THEN
1676 i := i + 1;
1677 open_inv_contract_tbl(i) := c_open_invoices_contract_rec;
1678 l_has_invoices := TRUE;
1679 END IF;
1680 END LOOP;
1681 i := 0;
1682 CLOSE c_open_invoices_contract_cust;
1683 ELSE
1684 OPEN c_open_invoices_contract(l_org_id, l_contract_id);
1685 LOOP
1686 FETCH c_open_invoices_contract INTO c_open_invoices_contract_rec;
1687 EXIT WHEN c_open_invoices_contract%NOTFOUND;
1688 IF c_open_invoices_contract_rec.amount_due_remaining > 0 THEN
1689 i := i + 1;
1690 open_inv_contract_tbl(i) := c_open_invoices_contract_rec;
1691 l_has_invoices := TRUE;
1692 END IF;
1693 END LOOP;
1694 i := 0;
1695 CLOSE c_open_invoices_contract;
1696 END IF;
1697 IF (l_has_invoices = FALSE) THEN
1698 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Contract is having no open invoices');
1699 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1700 RETURN;
1701 ELSE
1702 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Contract is having open invoices');
1703 END IF;
1704
1705 log_debug('Receipt Currency = '||l_currency_code);
1706 log_debug('Contract Currency = '||l_inv_curr_Code);
1707 log_debug('l_amount_app_from = '||l_amount_app_from);
1708 log_debug('l_amount_app_to = '||l_amount_app_to);
1709 log_debug('l_inv_to_rct_rate = '||l_inv_to_rct_rate);
1710 IF l_currency_code = l_inv_curr_Code THEN
1711 IF l_amount_app_from IS NULL AND l_amount_app_to IS NULL THEN
1712 OKL_API.set_message( p_app_name => G_APP_NAME
1713 ,p_msg_name => 'OKL_BPD_INVALID_PARAMS'
1714 );
1715 RAISE G_EXCEPTION_HALT_VALIDATION;
1716 ELSIF l_amount_app_from IS NULL THEN
1717 l_amount_app_from := l_amount_app_to;
1718 ELSE
1719 l_amount_app_to := l_amount_app_from;
1720 END IF;
1721 ELSE
1722 IF l_amount_app_from IS NOT NULL AND l_amount_app_to IS NOT NULL AND l_inv_to_rct_rate IS NOT NULL THEN
1723 IF ( l_amount_app_to * l_inv_to_rct_rate) <> l_amount_app_from THEN
1724 OKL_API.set_message( p_app_name => G_APP_NAME
1725 ,p_msg_name => 'OKL_BPD_PARAMS_MISMATCH'
1726 );
1727 END IF;
1728 IF l_inv_to_rct_rate <> 0 and ( ( l_amount_app_from / l_inv_to_rct_rate) <> l_amount_app_to) THEN
1729 OKL_API.set_message( p_app_name => G_APP_NAME
1730 ,p_msg_name => 'OKL_BPD_PARAMS_MISMATCH'
1731 );
1732 END IF;
1733 END IF;
1734 l_cross_curr_enabled := nvl(FND_PROFILE.value('AR_ENABLE_CROSS_CURRENCY'),'N');
1735 log_debug('l_cross_curr_enabled = '||l_cross_curr_enabled);
1736 /* IF l_cross_curr_enabled <> 'Y' THEN
1737 OKL_API.set_message( p_app_name => G_APP_NAME
1738 ,p_msg_name => 'OKL_BPD_CROSS_CURR_NA'
1739 );
1740 RAISE G_EXCEPTION_HALT_VALIDATION;
1741 ELSE*/
1742 IF l_inv_to_rct_rate is null THEN
1743 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
1744 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
1745 IF l_exchange_rate_type IS NULL THEN
1746 OKL_API.set_message( p_app_name => G_APP_NAME
1747 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
1748 );
1749 RAISE G_EXCEPTION_HALT_VALIDATION;
1750 ELSE
1751 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
1752 ,l_currency_code
1753 ,l_receipt_date
1754 ,l_exchange_rate_type
1755 );
1756
1757 IF l_conversion_rate IN (0,-1) THEN
1758
1759 -- Message Text: No exchange rate defined
1760 x_return_status := okl_api.G_RET_STS_ERROR;
1761 okl_api.set_message( p_app_name => G_APP_NAME,
1762 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
1763 RAISE G_EXCEPTION_HALT_VALIDATION;
1764 END IF;
1765 END IF;
1766 ELSE
1767 l_conversion_rate := l_inv_to_rct_rate;
1768 END IF;
1769 log_debug('l_conversion_rate ='||l_conversion_rate);
1770 IF l_amount_app_from IS NULL AND l_amount_app_to IS NULL THEN
1771 OKL_API.set_message( p_app_name => G_APP_NAME
1772 ,p_msg_name => 'OKL_BPD_INVALID_PARAMS'
1773 );
1774 RAISE G_EXCEPTION_HALT_VALIDATION;
1775 ELSIF l_amount_app_from IS NULL THEN
1776 l_amount_app_from := l_amount_app_to * l_conversion_rate;
1777 ELSE
1778 l_amount_app_to := l_amount_app_from * (1/l_conversion_rate);
1779 END IF;
1780 --END IF;
1781 END IF;
1782 l_amount_app_from := arp_util.CurrRound(l_amount_app_from,l_currency_code);
1783 l_amount_app_to := arp_util.CurrRound(l_amount_app_to,l_inv_curr_code);
1784 log_debug('l_amount_app_from = '||l_amount_app_from);
1785 log_debug('l_amount_app_to = '||l_amount_app_to);
1786 log_debug('l_inv_to_rct_rate = '||l_inv_to_rct_rate);
1787
1788 --get default cash application rules.
1789 get_default_cash_app_rule( p_org_id => l_org_id
1790 ,x_dflt_cat_id => l_dflt_cat_id
1791 ,x_dflt_tolerance => l_dflt_tolerance
1792 ,x_dflt_days_past_quote_valid => l_dflt_days_past_quote_valid
1793 ,x_dflt_months_to_bill_ahead => l_dflt_months_to_bill_ahead
1794 ,x_dflt_under_payment => l_dflt_under_payment
1795 ,x_dflt_over_payment => l_dflt_over_payment
1796 ,x_dflt_receipt_msmtch => l_dflt_receipt_msmtch);
1797
1798 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1799 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Default Tolerance: ' || l_dflt_tolerance);
1800 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Default Under Payment: ' || l_dflt_under_payment);
1801 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Default Over Payment: ' || l_dflt_over_payment);
1802 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Default Receipt Mismatch: ' || l_dflt_receipt_msmtch);
1803 END IF;
1804
1805 --If default cash application rule is not defined then
1806 --raise an exception
1807 -- sosharma Modified the check for bug 9771644
1808 /* IF l_dflt_tolerance IS NULL OR l_dflt_under_payment IS NULL
1809 OR l_dflt_over_payment IS NULL OR l_dflt_receipt_msmtch IS NULL THEN
1810 RAISE OKL_API.G_EXCEPTION_ERROR;
1811 END IF;*/
1812
1813 IF l_dflt_tolerance IS NULL OR l_dflt_under_payment IS NULL
1814 OR l_dflt_over_payment IS NULL THEN
1815 RAISE OKL_API.G_EXCEPTION_ERROR;
1816 END IF;
1817
1818 -- START OKL CASH APPLICATION.
1819 IF l_contract_num IS NOT NULL THEN
1820 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1821 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inside if block of l_contract_num is not null ');
1822 END IF;
1823
1824 IF l_contract_id IS NULL THEN
1825 OPEN c_get_contract_id(l_contract_num);
1826 FETCH c_get_contract_id INTO l_contract_id,l_inv_curr_Code;
1827 CLOSE c_get_contract_id;
1828 END IF;
1829
1830 --get cash application rule
1831 OPEN c_cash_rle_id_csr (l_contract_id);
1832 FETCH c_cash_rle_id_csr INTO l_cau_id;
1833 CLOSE c_cash_rle_id_csr;
1834
1835 IF l_cau_id IS NOT NULL THEN
1836 OPEN c_cash_rule_csr (l_cau_id);
1837 FETCH c_cash_rule_csr INTO l_cat_id
1838 ,l_tolerance
1839 ,l_days_past_quote_valid
1840 ,l_months_to_bill_ahead
1841 ,l_under_payment
1842 ,l_over_payment
1843 ,l_receipt_msmtch;
1844 CLOSE c_cash_rule_csr;
1845
1846 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1847 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_tolerance : '||l_tolerance);
1848 END IF;
1849
1850 IF l_tolerance IS NULL THEN
1851 l_cat_id := l_dflt_cat_id;
1852 l_tolerance := l_dflt_tolerance;
1853 l_days_past_quote_valid := l_dflt_days_past_quote_valid;
1854 l_months_to_bill_ahead := l_dflt_months_to_bill_ahead;
1855 l_under_payment := l_dflt_under_payment;
1856 l_over_payment := l_dflt_over_payment;
1857 l_receipt_msmtch := l_dflt_receipt_msmtch;
1858 END IF;
1859 ELSE -- use default rule
1860
1861 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1862 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Using default CAR since l_cau_id is NULL');
1863 END IF;
1864 l_cat_id := l_dflt_cat_id;
1865 l_tolerance := l_dflt_tolerance;
1866 l_days_past_quote_valid := l_dflt_days_past_quote_valid;
1867 l_months_to_bill_ahead := l_dflt_months_to_bill_ahead;
1868 l_under_payment := l_dflt_under_payment;
1869 l_over_payment := l_dflt_over_payment;
1870 l_receipt_msmtch := l_dflt_receipt_msmtch;
1871 END IF;
1872
1873 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1874 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Opening cursor c_open_invoices_contract ('||l_contract_num||', '||l_customer_num||', NULL)..');
1875 END IF;
1876
1877 IF NVL(l_cau_id, 0) = -1 THEN -- VR 07-Oct-2005 GE-20 Receipts - On Account CAR
1878 -- Receipt needs to be left as unapplied
1879 j := 1;
1880 l_rcpt_tbl(j).INVOICE_ID := NULL;
1881 l_rcpt_tbl(j).INVOICE_LINE_ID := NULL;
1882 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := NULL;
1883 l_rcpt_tbl(j).INVOICE_NUMBER := NULL;
1884 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
1885 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
1886 ELSE
1887
1888 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
1889 LOOP
1890 --l_invoice_currency_code := c_open_invoices_contract_rec.currency_code;
1891 l_inv_tot := l_inv_tot + open_inv_contract_tbl(i).amount_due_remaining; -- changed from remaining to original
1892 END LOOP;
1893 -- TOLERANCE CHECK
1894
1895 IF l_inv_tot > l_amount_app_to THEN
1896 l_appl_tolerance := l_inv_tot * (1 - l_tolerance / 100);
1897 ELSE
1898 l_appl_tolerance := l_inv_tot;
1899 END IF;
1900
1901 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1902 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_inv_tot : ' || l_inv_tot);
1903 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_amount_app_to : ' || l_amount_app_to);
1904 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_appl_tolerance : ' || l_appl_tolerance);
1905 END IF;
1906
1907 IF l_inv_tot > l_amount_app_to AND l_appl_tolerance > l_amount_app_to THEN -- UNDERPAYMENT (2)
1908 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1909 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'auto_cashapp_for_contract > UNDERPAYMENT ...');
1910 END IF;
1911
1912 IF l_under_payment In ('U','u') THEN -- Unapply underpayment (3)
1913 IF l_currency_code = l_inv_curr_code THEN
1914 l_unapply_amount:=l_amount_app_to;
1915 ELSE
1916 l_unapply_amount:= l_amount_app_to * l_conversion_rate;
1917 l_unapply_amount:=GET_ROUNDED_AMOUNT(l_unapply_amount,l_currency_code);
1918 END IF;
1919 ELSIF l_under_payment IN ('T','t') THEN -- ORDERED (3)
1920 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1921 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ORDERED ...');
1922 END IF;
1923
1924 j := 0;
1925 OPEN c_stream_alloc (l_ordered, l_cat_id);
1926 LOOP
1927 FETCH c_stream_alloc INTO l_sty_id;
1928 EXIT WHEN c_stream_alloc%NOTFOUND OR l_amount_app_to = 0 OR l_amount_app_to IS NULL;
1929
1930 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
1931 LOOP
1932 c_open_invoices_contract_rec := open_inv_contract_tbl(i);
1933 IF c_open_invoices_contract_rec.sty_id = l_sty_id THEN
1934 EXIT WHEN l_amount_app_to = 0 OR l_amount_app_to IS NULL;
1935 j := j + 1;
1936 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invoices_contract_rec.amount_due_remaining;
1937 IF l_rcpt_tbl(j).AMOUNT_APPLIED > l_amount_app_to THEN
1938 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
1939 l_amount_app_to := 0;
1940 ELSE
1941 l_amount_app_to := l_amount_app_to - l_rcpt_tbl(j).AMOUNT_APPLIED;
1942 END IF;
1943 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invoices_contract_rec.ar_invoice_number;
1944 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
1945 l_rcpt_tbl(j).INVOICE_ID := c_open_invoices_contract_rec.ar_invoice_id;
1946 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invoices_contract_rec.invoice_line_id;
1947 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invoices_contract_rec.line_number;
1948 IF l_currency_code <> l_inv_curr_code THEN
1949 IF l_inv_to_rct_rate is null THEN
1950 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
1951 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
1952 IF l_exchange_rate_type IS NULL THEN
1953 OKL_API.set_message( p_app_name => G_APP_NAME
1954 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
1955 );
1956 RAISE G_EXCEPTION_HALT_VALIDATION;
1957 ELSE
1958 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
1959 ,l_currency_code
1960 ,l_receipt_date
1961 ,l_exchange_rate_type
1962 );
1963
1964 IF l_conversion_rate IN (0,-1) THEN
1965
1966 -- Message Text: No exchange rate defined
1967 x_return_status := okl_api.G_RET_STS_ERROR;
1968 okl_api.set_message( p_app_name => G_APP_NAME,
1969 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
1970 RAISE G_EXCEPTION_HALT_VALIDATION;
1971 END IF;
1972 END IF;
1973 ELSE
1974 l_conversion_rate := l_inv_to_rct_rate;
1975 END IF;
1976 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
1977 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
1978 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
1979 ELSE
1980 l_rcpt_tbl(j).trans_to_receipt_rate := null;
1981 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
1982 END IF;
1983 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1984 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_ID : '||l_rcpt_tbl(j).INVOICE_ID);
1985 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
1986 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
1987 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_LINE_ID : '||l_rcpt_tbl(j).INVOICE_LINE_ID);
1988 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_LINE_NUMBER : '||l_rcpt_tbl(j).INVOICE_LINE_NUMBER);
1989 END IF;
1990 END IF;
1991 END LOOP;
1992 END LOOP;
1993 CLOSE c_stream_alloc;
1994
1995 ELSIF l_under_payment IN ('P','p') THEN -- PRO RATE (3)
1996 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1997 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'PRO RATE');
1998 END IF;
1999
2000 j := 0;
2001 -- obtain all the streams that are part of the pro rate user defined list.
2002
2003 FOR c_stream_alloc_rec IN c_stream_alloc (l_prorate, l_cat_id)
2004 LOOP
2005 l_sty_id := c_stream_alloc_rec.sty_id;
2006 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
2007 LOOP
2008 c_open_invoices_contract_rec := open_inv_contract_tbl(i);
2009 IF c_open_invoices_contract_rec.sty_id = l_sty_id THEN
2010 j := j + 1;
2011 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invoices_contract_rec.ar_invoice_number;
2012 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
2013 l_rcpt_tbl(j).INVOICE_ID := c_open_invoices_contract_rec.ar_invoice_id;
2014 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invoices_contract_rec.invoice_line_id;
2015 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invoices_contract_rec.line_number;
2016 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invoices_contract_rec.amount_due_remaining;
2017 l_pro_rate_inv_total := l_pro_rate_inv_total + l_rcpt_tbl(j).AMOUNT_APPLIED;
2018
2019 IF l_currency_code <> l_inv_curr_code THEN
2020 IF l_inv_to_rct_rate is null THEN
2021 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
2022 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
2023 IF l_exchange_rate_type IS NULL THEN
2024 OKL_API.set_message( p_app_name => G_APP_NAME
2025 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
2026 );
2027 RAISE G_EXCEPTION_HALT_VALIDATION;
2028 ELSE
2029 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
2030 ,l_currency_code
2031 ,l_receipt_date
2032 ,l_exchange_rate_type
2033 );
2034
2035 IF l_conversion_rate IN (0,-1) THEN
2036
2037 -- Message Text: No exchange rate defined
2038 x_return_status := okl_api.G_RET_STS_ERROR;
2039 okl_api.set_message( p_app_name => G_APP_NAME,
2040 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
2041 RAISE G_EXCEPTION_HALT_VALIDATION;
2042 END IF;
2043 END IF;
2044 ELSE
2045 l_conversion_rate := l_inv_to_rct_rate;
2046 END IF;
2047 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
2048 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
2049 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
2050 ELSE
2051 l_rcpt_tbl(j).trans_to_receipt_rate := null;
2052 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
2053 END IF;
2054 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2055 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_ID : '||l_rcpt_tbl(j).INVOICE_ID);
2056 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
2057 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
2058 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_LINE_ID : '||l_rcpt_tbl(j).INVOICE_LINE_ID);
2059 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_LINE_NUMBER : '||l_rcpt_tbl(j).INVOICE_LINE_NUMBER);
2060 END IF;
2061 END IF;
2062 END LOOP; -- c_open_invs
2063 END LOOP; -- c_stream_alloc
2064
2065 -- Calc Pro Ration
2066 -- only if total amount of prorated invoices is greater than receipt
2067 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2068 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_pro_rate_inv_total: '||l_pro_rate_inv_total);
2069 END IF;
2070 IF l_pro_rate_inv_total IS NULL OR l_pro_rate_inv_total = 0 THEN
2071 -- Message Text: No prorated transaction types
2072 --x_return_status := OKC_API.G_RET_STS_ERROR;
2073 --OKC_API.set_message( p_app_name => G_APP_NAME,
2074 -- p_msg_name => 'OKL_BPD_NO_PRORATED_STRMS');
2075 --RAISE OKL_API.G_EXCEPTION_ERROR;
2076 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Pro-rate invoice total is zero or null');
2077 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2078 x_appl_tbl.delete;
2079 RETURN;
2080 END IF;
2081
2082 IF (l_pro_rate_inv_total > l_amount_app_to) THEN
2083 j := l_rcpt_tbl.FIRST;
2084 l_temp_val := l_amount_app_to / l_pro_rate_inv_total;
2085 LOOP
2086 l_rcpt_tbl(j).AMOUNT_APPLIED := l_temp_val * l_rcpt_tbl(j).AMOUNT_APPLIED;
2087 l_rcpt_tbl(j).AMOUNT_APPLIED:=GET_ROUNDED_AMOUNT(l_rcpt_tbl(j).AMOUNT_APPLIED,l_inv_curr_Code);
2088 l_inv_total_amt := l_inv_total_amt + l_rcpt_tbl(j).AMOUNT_APPLIED;
2089 IF l_currency_code <> l_inv_curr_code THEN
2090 IF l_inv_to_rct_rate is null THEN
2091 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
2092 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
2093 IF l_exchange_rate_type IS NULL THEN
2094 OKL_API.set_message( p_app_name => G_APP_NAME
2095 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
2096 );
2097 RAISE G_EXCEPTION_HALT_VALIDATION;
2098 ELSE
2099 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
2100 ,l_currency_code
2101 ,l_receipt_date
2102 ,l_exchange_rate_type
2103 );
2104
2105 IF l_conversion_rate IN (0,-1) THEN
2106
2107 -- Message Text: No exchange rate defined
2108 x_return_status := okl_api.G_RET_STS_ERROR;
2109 okl_api.set_message( p_app_name => G_APP_NAME,
2110 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
2111 RAISE G_EXCEPTION_HALT_VALIDATION;
2112 END IF;
2113 END IF;
2114 ELSE
2115 l_conversion_rate := l_inv_to_rct_rate;
2116 END IF;
2117 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
2118 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
2119 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
2120
2121 ELSE
2122 l_rcpt_tbl(j).trans_to_receipt_rate := null;
2123 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
2124 END IF;
2125 EXIT WHEN (j = l_rcpt_tbl.LAST);
2126 j := j + 1;
2127 END LOOP;
2128 l_diff_amount := l_amount_app_to - l_inv_total_amt;
2129 if l_diff_amount > 0 then
2130 l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied := l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied + l_diff_amount;
2131 IF l_currency_code <> l_inv_curr_code THEN
2132 IF l_inv_to_rct_rate is null THEN
2133 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
2134 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
2135 IF l_exchange_rate_type IS NULL THEN
2136 OKL_API.set_message( p_app_name => G_APP_NAME
2137 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
2138 );
2139 RAISE G_EXCEPTION_HALT_VALIDATION;
2140 ELSE
2141 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
2142 ,l_currency_code
2143 ,l_receipt_date
2144 ,l_exchange_rate_type
2145 );
2146
2147 IF l_conversion_rate IN (0,-1) THEN
2148
2149 -- Message Text: No exchange rate defined
2150 x_return_status := okl_api.G_RET_STS_ERROR;
2151 okl_api.set_message( p_app_name => G_APP_NAME,
2152 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
2153 RAISE G_EXCEPTION_HALT_VALIDATION;
2154 END IF;
2155 END IF;
2156 ELSE
2157 l_conversion_rate := l_inv_to_rct_rate;
2158 END IF;
2159 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
2160 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
2161 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
2162 ELSE
2163 l_rcpt_tbl(j).trans_to_receipt_rate := null;
2164 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
2165 END IF;
2166 end if;
2167 END IF;
2168 END IF; -- (3)
2169 ELSE -- EXACT or OVERPAYMENT or TOLERANCE (2)
2170 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2171 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'auto_cashapp_for_contract > EXACT or OVERPAYMENT or TOLERANCE');
2172 END IF;
2173
2174 j := 0;
2175
2176 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
2177 LOOP
2178 EXIT WHEN l_amount_app_to = 0 OR l_amount_app_to IS NULL;
2179 c_open_invoices_contract_rec := open_inv_contract_tbl(i);
2180 j := j + 1;
2181
2182 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invoices_contract_rec.ar_invoice_number;
2183 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
2184 l_rcpt_tbl(j).INVOICE_ID := c_open_invoices_contract_rec.ar_invoice_id;
2185 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invoices_contract_rec.invoice_line_id;
2186 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invoices_contract_rec.line_number;
2187 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invoices_contract_rec.amount_due_remaining;
2188
2189 IF l_amount_app_to < l_rcpt_tbl(j).AMOUNT_APPLIED THEN
2190 -- TOLERANCE
2191 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
2192 l_amount_app_to := 0;
2193 ELSE
2194 l_amount_app_to := l_amount_app_to - l_rcpt_tbl(j).AMOUNT_APPLIED;
2195 END IF;
2196 IF l_currency_code <> l_inv_curr_code THEN
2197 IF l_inv_to_rct_rate is null THEN
2198 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
2199 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
2200 IF l_exchange_rate_type IS NULL THEN
2201 OKL_API.set_message( p_app_name => G_APP_NAME
2202 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
2203 );
2204 RAISE G_EXCEPTION_HALT_VALIDATION;
2205 ELSE
2206 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
2207 ,l_currency_code
2208 ,l_receipt_date
2209 ,l_exchange_rate_type
2210 );
2211
2212 IF l_conversion_rate IN (0,-1) THEN
2213
2214 -- Message Text: No exchange rate defined
2215 x_return_status := okl_api.G_RET_STS_ERROR;
2216 okl_api.set_message( p_app_name => G_APP_NAME,
2217 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
2218 RAISE G_EXCEPTION_HALT_VALIDATION;
2219 END IF;
2220 END IF;
2221 ELSE
2222 l_conversion_rate := l_inv_to_rct_rate;
2223 END IF;
2224 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
2225 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
2226 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
2227 ELSE
2228 l_rcpt_tbl(j).trans_to_receipt_rate := null;
2229 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
2230 END IF;
2231 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2232 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_ID : '||l_rcpt_tbl(j).INVOICE_ID);
2233 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
2234 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
2235 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_LINE_ID : '||l_rcpt_tbl(j).INVOICE_LINE_ID);
2236 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_LINE_NUMBER : '||l_rcpt_tbl(j).INVOICE_LINE_NUMBER);
2237 END IF;
2238 END LOOP;
2239
2240 -- Apply the remaining balance as per the Cash Application Rule
2241 If l_over_payment In ('B','b') Then -- Onaccount --move cash to customer balances -OVP
2242 IF l_currency_code = l_inv_curr_code THEN
2243 l_onacc_amount:=l_amount_app_to;
2244 ELSE
2245 l_onacc_amount:= l_amount_app_to * l_conversion_rate;
2246 l_onacc_amount:=GET_ROUNDED_AMOUNT(l_onacc_amount,l_currency_code);
2247 END IF;
2248 Elsif l_over_payment In ('F','f') Then --Unapply -- move cash to unapplied -OVP
2249 IF l_currency_code = l_inv_curr_code THEN
2250 l_unapply_amount:=l_amount_app_to;
2251 ELSE
2252 l_unapply_amount:= l_amount_app_to * l_conversion_rate;
2253 l_unapply_amount:=GET_ROUNDED_AMOUNT(l_unapply_amount,l_currency_code);
2254 END IF;
2255 End If;
2256
2257 END IF; -- under payment.;
2258 END IF; -- VR 07-Oct-2005 GE-20 Receipts - On Account CAR
2259
2260 --Get grouped application table (in the form of invoice header > multiple invoice lines table)
2261 GET_APPLICATIONS( p_rcpt_tbl => l_rcpt_tbl
2262 ,x_appl_tbl => l_appl_tbl);
2263 x_appl_tbl := l_appl_tbl;
2264 END IF;
2265
2266 l_tot_amt_app_from := 0;
2267 IF x_appl_tbl.COUNT > 0 THEN
2268 IF x_appl_tbl(x_appl_tbl.FIRST).inv_hdr_rec.trans_to_receipt_rate IS NOT NULL THEN
2269 FOR ll IN x_appl_tbl.FIRST..x_appl_tbl.LAST LOOP
2270 l_tot_amt_app_from := l_tot_amt_app_from + nvl(x_appl_tbl(ll).inv_hdr_rec.amount_applied_from,0);
2271 END LOOP;
2272 IF (l_onacc_amount + l_unapply_amount + l_tot_amt_app_from ) <> l_amount_app_from THEN
2273 l_diff_amount := l_amount_app_from - (l_onacc_amount + l_unapply_amount + l_tot_amt_app_from );
2274 log_debug('l_diff_amount = '||l_diff_amount);
2275 IF nvl(l_onacc_amount,0) <> 0 THEN
2276 l_onacc_amount := l_onacc_amount + l_diff_amount;
2277 log_debug('l_onacc_amount = '||l_onacc_amount);
2278 ELSIF nvl(l_unapply_amount,0) <> 0 THEN
2279 l_unapply_amount := l_unapply_amount + l_diff_amount;
2280 END IF;
2281 END IF;
2282 END IF;
2283 END IF;
2284
2285 x_onacc_amount :=l_onacc_amount;
2286 x_unapply_amount :=l_unapply_amount;
2287
2288 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2289 EXCEPTION
2290 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2291 x_appl_tbl.DELETE;
2292 x_return_status := OKL_API.G_RET_STS_ERROR;
2293 x_appl_tbl.delete;
2294 WHEN OTHERS THEN
2295 x_appl_tbl.DELETE;
2296 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2297 x_msg_count := l_msg_count;
2298 x_msg_data := sqlerrm;
2299 END auto_cashapp_for_contract;
2300
2301
2302
2303 --Receipt mismatch which will identify all the invoice lines
2304 --for the given customer based on CAR setup i.e. Newest invoices or Oldest invoices
2305 PROCEDURE receipt_mismatch(p_api_version IN NUMBER
2306 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
2307 ,x_return_status OUT NOCOPY VARCHAR2
2308 ,x_msg_count OUT NOCOPY NUMBER
2309 ,x_msg_data OUT NOCOPY VARCHAR2
2310 ,p_customer_num IN VARCHAR2 DEFAULT NULL
2311 ,p_currency_code IN VARCHAR2
2312 ,p_rcpt_amount IN NUMBER
2313 ,p_org_id IN NUMBER
2314 ,p_receipt_date IN DATE
2315 ,x_appl_tbl OUT NOCOPY okl_appl_dtls_tbl_type
2316 ,x_onacc_amount OUT NOCOPY NUMBER
2317 ) IS
2318
2319 --Variables declaration
2320 l_dflt_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
2321 l_dflt_tolerance OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE DEFAULT NULL;
2322 l_dflt_days_past_quote_valid OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE DEFAULT NULL;
2323 l_dflt_months_to_bill_ahead OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE DEFAULT NULL;
2324 l_dflt_under_payment OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
2325 l_dflt_over_payment OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
2326 l_dflt_receipt_msmtch OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE DEFAULT NULL;
2327
2328 l_customer_num HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE := p_customer_num;
2329 l_currency_code VARCHAR2(45) := p_currency_code;
2330 l_rcpt_amount NUMBER := p_rcpt_amount;
2331 l_org_id NUMBER := p_org_id;
2332 j NUMBER;
2333
2334 l_msg_count NUMBER;
2335 l_msg_data VARCHAR2(2000);
2336
2337 --Record/Table Definitions
2338 l_rcpt_tbl okl_rcpt_dtls_tbl_type;
2339 l_appl_tbl okl_appl_dtls_tbl_type;
2340
2341 --Cursor definitions
2342
2343 CURSOR c_all_open_invs (cp_customer_num IN VARCHAR2, cp_org_id IN NUMBER,cp_curr_code IN VARCHAR2) IS
2344 SELECT AR_INVOICE_ID,
2345 AR_INVOICE_NUMBER,
2346 INVOICE_LINE_ID,
2347 LINE_NUMBER,
2348 AMOUNT_DUE_REMAINING,
2349 INVOICE_DUE_DATE
2350 FROM OKL_RCPT_CUST_CONT_BALANCES_UV
2351 WHERE CUSTOMER_ACCOUNT_NUMBER = cp_customer_num
2352 AND ORG_ID = cp_org_id
2353 AND STATUS = 'OP'
2354 AND CURRENCY_CODE = cp_curr_code
2355 -- AND AMOUNT_DUE_REMAINING > 0
2356 ORDER BY INVOICE_DUE_DATE;
2357
2358 c_all_open_invs_rec c_all_open_invs%ROWTYPE;
2359
2360
2361 CURSOR c_all_open_invs_desc (cp_customer_num IN VARCHAR2, cp_org_id IN NUMBER,cp_curr_code IN VARCHAR2) IS
2362 SELECT AR_INVOICE_ID,
2363 AR_INVOICE_NUMBER,
2364 INVOICE_LINE_ID,
2365 LINE_NUMBER,
2366 AMOUNT_DUE_REMAINING,
2367 INVOICE_DUE_DATE
2368 FROM OKL_RCPT_CUST_CONT_BALANCES_UV
2369 WHERE CUSTOMER_ACCOUNT_NUMBER = cp_customer_num
2370 AND ORG_ID = cp_org_id
2371 AND STATUS = 'OP'
2372 -- AND AMOUNT_DUE_REMAINING > 0
2373 AND CURRENCY_CODE = cp_curr_code
2374 ORDER BY INVOICE_DUE_DATE DESC;
2375
2376 c_all_open_invs_desc_rec c_all_open_invs_desc%ROWTYPE;
2377
2378 l_onacc_amount Number:=0;
2379
2380 BEGIN
2381 IF (G_DEBUG_ENABLED = 'Y') THEN
2382 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2383 END IF;
2384 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2385 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Starting execution of OKL_AUTO_CASH_APPL_RULES_PVT.receipt_mismatch ...');
2386 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Procedure parameters, p_customer_num : '|| p_customer_num);
2387 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_org_id : '|| p_org_id);
2388 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_currency_code : '|| p_currency_code);
2389 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_rcpt_amount : '|| p_rcpt_amount);
2390 END IF;
2391
2392 --get default cash application rules.
2393 get_default_cash_app_rule( p_org_id => l_org_id
2394 ,x_dflt_cat_id => l_dflt_cat_id
2395 ,x_dflt_tolerance => l_dflt_tolerance
2396 ,x_dflt_days_past_quote_valid => l_dflt_days_past_quote_valid
2397 ,x_dflt_months_to_bill_ahead => l_dflt_months_to_bill_ahead
2398 ,x_dflt_under_payment => l_dflt_under_payment
2399 ,x_dflt_over_payment => l_dflt_over_payment
2400 ,x_dflt_receipt_msmtch => l_dflt_receipt_msmtch);
2401
2402 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2403 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Default Tolerance: ' || l_dflt_tolerance);
2404 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Default Under Payment: ' || l_dflt_under_payment);
2405 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Default Over Payment: ' || l_dflt_over_payment);
2406 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Default Receipt Mismatch: ' || l_dflt_receipt_msmtch);
2407 END IF;
2408
2409 --If default cash application rule is not defined then
2410 --raise an exception
2411 IF l_dflt_tolerance IS NULL OR l_dflt_under_payment IS NULL
2412 OR l_dflt_over_payment IS NULL OR l_dflt_receipt_msmtch IS NULL THEN
2413 RAISE OKL_API.G_EXCEPTION_ERROR;
2414 END IF;
2415
2416 -- START OKL CASH APPLICATION.
2417
2418 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2419 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Checking whether l_customer_num is not null ..');
2420 END IF;
2421
2422 --If customer is not null then find open invoices for this customer
2423 IF l_customer_num IS NOT NULL THEN
2424 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2425 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inside branch for l_customer_num IS NOT NULL');
2426 END IF;
2427 j := 0;
2428
2429 IF l_dflt_receipt_msmtch In ('A' ,'a') THEN -- Apply 'on-account'
2430 l_onacc_amount :=l_rcpt_amount;
2431 ELSIF l_dflt_receipt_msmtch = 'O' or -- APPLY TO CUSTOMER'S OLDEST INVOICES FIRST
2432 l_dflt_receipt_msmtch = 'o' THEN
2433 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2434 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Apply to customers oldest invoices first');
2435 END IF;
2436 OPEN c_all_open_invs (l_customer_num, l_org_id,l_currency_code);
2437 LOOP
2438 FETCH c_all_open_invs INTO c_all_open_invs_rec;
2439 EXIT WHEN c_all_open_invs%NOTFOUND OR l_rcpt_amount = 0 OR l_rcpt_amount IS NULL;
2440 IF c_all_open_invs_rec.AMOUNT_DUE_REMAINING > 0 THEN
2441 j := j + 1;
2442
2443 --Populate receipt table
2444 l_rcpt_tbl(j).INVOICE_ID := c_all_open_invs_rec.ar_invoice_id;
2445 l_rcpt_tbl(j).INVOICE_NUMBER := c_all_open_invs_rec.ar_invoice_number;
2446 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_currency_code;
2447 l_rcpt_tbl(j).INVOICE_LINE_ID := c_all_open_invs_rec.invoice_line_id;
2448 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_all_open_invs_rec.line_number;
2449
2450 --Amount Applied will be total amount applied including line and tax
2451 l_rcpt_tbl(j).AMOUNT_APPLIED := c_all_open_invs_rec.amount_due_remaining;
2452
2453 IF l_rcpt_amount < l_rcpt_tbl(j).AMOUNT_APPLIED THEN
2454 l_rcpt_tbl(j).AMOUNT_APPLIED := l_rcpt_amount;
2455 l_rcpt_amount := 0;
2456 ELSE
2457 l_rcpt_amount := l_rcpt_amount - l_rcpt_tbl(j).AMOUNT_APPLIED;
2458 END IF;
2459
2460 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2461 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_ID : '||l_rcpt_tbl(j).INVOICE_ID);
2462 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
2463 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).INVOICE_LINE_ID : '||l_rcpt_tbl(j).INVOICE_LINE_ID);
2464 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).INVOICE_LINE_NUMBER : '||l_rcpt_tbl(j).INVOICE_LINE_NUMBER);
2465 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
2466 END IF;
2467 END IF;
2468 END LOOP;
2469 CLOSE c_all_open_invs;
2470
2471 ELSIF l_dflt_receipt_msmtch ='N' or -- APPLY TO CUSTOMER'S NEWEST INVOICES FIRST
2472 l_dflt_receipt_msmtch ='n' THEN
2473
2474 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2475 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Apply to customers newest invoices first');
2476 END IF;
2477
2478 OPEN c_all_open_invs_desc (l_customer_num, l_org_id,l_currency_code);
2479 LOOP
2480 FETCH c_all_open_invs_desc INTO c_all_open_invs_desc_rec;
2481 EXIT WHEN c_all_open_invs_desc%NOTFOUND OR l_rcpt_amount = 0 OR l_rcpt_amount IS NULL;
2482 IF c_all_open_invs_desc_rec.AMOUNT_DUE_REMAINING > 0 THEN
2483 j := j + 1;
2484
2485 --Populate receipt table
2486 l_rcpt_tbl(j).INVOICE_ID := c_all_open_invs_desc_rec.ar_invoice_id;
2487 l_rcpt_tbl(j).INVOICE_NUMBER := c_all_open_invs_desc_rec.ar_invoice_number;
2488 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_currency_code;
2489 l_rcpt_tbl(j).INVOICE_LINE_ID := c_all_open_invs_desc_rec.invoice_line_id;
2490 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_all_open_invs_desc_rec.line_number;
2491
2492 --Amount Applied will be total amount applied including line and tax
2493 l_rcpt_tbl(j).AMOUNT_APPLIED := c_all_open_invs_desc_rec.amount_due_remaining;
2494
2495 IF l_rcpt_amount < l_rcpt_tbl(j).AMOUNT_APPLIED THEN
2496 l_rcpt_tbl(j).AMOUNT_APPLIED := l_rcpt_amount;
2497 l_rcpt_amount := 0;
2498 ELSE
2499 l_rcpt_amount := l_rcpt_amount - l_rcpt_tbl(j).AMOUNT_APPLIED;
2500 END IF;
2501
2502 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2503 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_ID : '||l_rcpt_tbl(j).INVOICE_ID);
2504 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
2505 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).INVOICE_LINE_ID : '||l_rcpt_tbl(j).INVOICE_LINE_ID);
2506 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).INVOICE_LINE_NUMBER : '||l_rcpt_tbl(j).INVOICE_LINE_NUMBER);
2507 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
2508 END IF;
2509 END IF;
2510 END LOOP;
2511 CLOSE c_all_open_invs_desc;
2512 END IF;
2513 --Get grouped application table (in the form of invoice header > multiple invoice lines table)
2514 GET_APPLICATIONS( p_rcpt_tbl => l_rcpt_tbl
2515 ,x_appl_tbl => l_appl_tbl);
2516 x_appl_tbl := l_appl_tbl;
2517 END IF;
2518
2519 --Commented on account amount assignment as it is not supported from 12.1.3
2520 --For bug 8521220 - by NIKSHAH
2521 --x_onacc_amount :=l_onacc_amount;
2522 x_onacc_amount := 0;
2523
2524 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2525 --to be coded -onacc
2526
2527 EXCEPTION
2528 WHEN OTHERS THEN
2529 x_appl_tbl.DELETE;
2530 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2531 x_msg_count := l_msg_count;
2532 x_msg_data := sqlerrm;
2533 END receipt_mismatch;
2534
2535
2536 --Get default cash application rule
2537 PROCEDURE get_default_cash_app_rule(p_org_id IN OKL_CASH_ALLCTN_RLS.ORG_ID%TYPE,
2538 x_dflt_cat_id OUT NOCOPY OKL_CASH_ALLCTN_RLS.ID%TYPE,
2539 x_dflt_tolerance OUT NOCOPY OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE,
2540 x_dflt_days_past_quote_valid OUT NOCOPY OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE,
2541 x_dflt_months_to_bill_ahead OUT NOCOPY OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE,
2542 x_dflt_under_payment OUT NOCOPY OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE,
2543 x_dflt_over_payment OUT NOCOPY OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE ,
2544 x_dflt_receipt_msmtch OUT NOCOPY OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE
2545 ) IS
2546
2547 ---------------------------
2548 -- DECLARE Local Variables
2549 ---------------------------
2550 l_org_id OKL_CASH_ALLCTN_RLS.ORG_ID%TYPE := p_org_id;
2551 l_dflt_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
2552 l_dflt_tolerance OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE DEFAULT NULL;
2553 l_dflt_days_past_quote_valid OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE DEFAULT NULL;
2554 l_dflt_months_to_bill_ahead OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE DEFAULT NULL;
2555 l_dflt_under_payment OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
2556 l_dflt_over_payment OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
2557 l_dflt_receipt_msmtch OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE DEFAULT NULL;
2558
2559
2560 -------------------
2561 -- DECLARE Cursors
2562 -------------------
2563 -- get default cash applic rule for organization
2564 CURSOR c_dflt_cash_applic_rule(p_org_id NUMBER) IS
2565 SELECT ID
2566 ,AMOUNT_TOLERANCE_PERCENT
2567 ,DAYS_PAST_QUOTE_VALID_TOLERANC
2568 ,MONTHS_TO_BILL_AHEAD
2569 ,UNDER_PAYMENT_ALLOCATION_CODE
2570 ,OVER_PAYMENT_ALLOCATION_CODE
2571 ,RECEIPT_MSMTCH_ALLOCATION_CODE
2572 FROM OKL_CASH_ALLCTN_RLS_ALL
2573 WHERE default_rule = 'YES'
2574 AND TRUNC(end_date) IS NULL
2575 AND org_id = p_org_id;
2576
2577 BEGIN
2578 IF (G_DEBUG_ENABLED = 'Y') THEN
2579 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2580 END IF;
2581 -- get default cash application rules.
2582
2583 OPEN c_dflt_cash_applic_rule(l_org_id);
2584 FETCH c_dflt_cash_applic_rule INTO l_dflt_cat_id
2585 ,l_dflt_tolerance
2586 ,l_dflt_days_past_quote_valid
2587 ,l_dflt_months_to_bill_ahead
2588 ,l_dflt_under_payment
2589 ,l_dflt_over_payment
2590 ,l_dflt_receipt_msmtch;
2591 CLOSE c_dflt_cash_applic_rule;
2592
2593 x_dflt_cat_id := l_dflt_cat_id;
2594 x_dflt_tolerance:=l_dflt_tolerance;
2595 x_dflt_days_past_quote_valid := l_dflt_days_past_quote_valid;
2596 x_dflt_months_to_bill_ahead :=l_dflt_months_to_bill_ahead;
2597 x_dflt_under_payment:=l_dflt_under_payment;
2598 x_dflt_over_payment:=l_dflt_over_payment;
2599 x_dflt_receipt_msmtch := l_dflt_receipt_msmtch;
2600
2601 EXCEPTION
2602 WHEN OTHERS THEN
2603 l_dflt_cat_id:=null;
2604 END get_default_cash_app_rule;
2605
2606
2607 --Get application details table for the given receipt table
2608 --So the application details table will be table of invoice header
2609 --and its corresponding invoice lines with its applied amount
2610 PROCEDURE GET_APPLICATIONS ( p_rcpt_tbl IN okl_rcpt_dtls_tbl_type
2611 ,x_appl_tbl OUT NOCOPY okl_appl_dtls_tbl_type) IS
2612
2613 l_inv_num RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE DEFAULT NULL;
2614 l_prev_inv_num RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE DEFAULT NULL;
2615 line_counter NUMBER;
2616 hdr_counter NUMBER;
2617 i NUMBER;
2618 j NUMBER;
2619 complete_cycle BOOLEAN;
2620 counter NUMBER;
2621 l_total_amount_applied NUMBER := 0;
2622 l_line_amount_round NUMBER:=0;
2623 l_rcpt_tbl okl_rcpt_dtls_tbl_type := p_rcpt_tbl;
2624 l_appl_tbl okl_appl_dtls_tbl_type;
2625 l_inv_lns_tbl okl_inv_line_tbl_type;
2626 l_total_amount_app_from NUMBER := 0;
2627
2628 BEGIN
2629 /*
2630 Description:
2631 . We will have l_rcpt_tbl which is flat table where each row consists of
2632 invoice header and invoice line information.
2633 . We need to group invoice lines to corresponding invoice header. It is not
2634 necessary that all invoice headers will come sequentially in this table.
2635 So the logic to group invoice lines is not straight forward.
2636 . And return the resulting table back to calling procedure, which in turn, will
2637 return to calling procedure (OKL Lockbox API)
2638 */
2639
2640 /*
2641 Logic Flow:
2642 . hdr_counter is the counter for invoice header record
2643 . line_counter is the counter for invoice line record within each invoice header
2644 . complete_cycle ( = TRUE) indicates that l_rcpt_tbl has been traversed
2645 completely.
2646 . once invoice line is grouped in any invoice header, then will make amount_applied
2647 field to -1 in l_rcpt_tbl
2648 . counter is the counter for number of invoice lines whose amount_applied
2649 has been set to -1. We will increment counter everytime will update amount to -1
2650 . If value of counter is equal to count of l_rcpt_tbl then will terminate the loop
2651 . It makes a check in the loop whether amount is -1 if it is -1 then leave it
2652 otherwise if it is accessing any record from top of l_rcpt_tbl for the first time
2653 then assign create invoice header record and create 1st invoice line for this header.
2654 Also set amount to -1 and increment counter.
2655 . For the subsequent records in l_rcpt_tbl, if same invoice header is found then
2656 create another line record in invoice lines table. Also set amount to -1 and
2657 increment counter.
2658 . If the index of l_rcpt_tbl is the last then associate invoice lines table to
2659 invoice header record. Set complete_cycle to TRUE. Re-initialize i to 1,
2660 which will point to first record of l_rcpt_tbl. Re-initialize line_counter to 1.
2661 Increment hdr_counter.
2662 */
2663 IF (G_DEBUG_ENABLED = 'Y') THEN
2664 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2665 END IF;
2666 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2667 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Starting execution of OKL_AUTO_CASH_APPL_RULES_PVT.GET_APPLICATIONS ...');
2668 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Count of l_rcpt_tbl: '|| l_rcpt_tbl.COUNT);
2669 END IF;
2670
2671 IF (l_rcpt_tbl IS NOT NULL AND l_rcpt_tbl.COUNT > 0) THEN
2672 hdr_counter := l_rcpt_tbl.FIRST;
2673 complete_cycle := FALSE;
2674 line_counter := l_rcpt_tbl.FIRST;
2675 i := l_rcpt_tbl.FIRST;
2676 j := i;
2677 counter := 0;
2678 WHILE (i <= l_rcpt_tbl.LAST)
2679 LOOP
2680 IF (l_rcpt_tbl(i).amount_applied <> -1) THEN
2681 l_inv_num := l_rcpt_tbl(i).INVOICE_NUMBER;
2682
2683 IF ( i = l_rcpt_tbl.FIRST OR complete_cycle = TRUE) THEN
2684 complete_cycle := FALSE;
2685 --Assign invoice header record in applications table
2686 l_appl_tbl(hdr_counter).inv_hdr_rec.invoice_number := l_inv_num;
2687 l_appl_tbl(hdr_counter).inv_hdr_rec.invoice_id := l_rcpt_tbl(i).invoice_id;
2688 l_appl_tbl(hdr_counter).inv_hdr_rec.invoice_currency_code:= l_rcpt_tbl(i).invoice_currency_code;
2689 --Assign invoice lines record in invoice lines table
2690 l_inv_lns_tbl(line_counter).invoice_line_id := l_rcpt_tbl(i).invoice_line_id;
2691 l_inv_lns_tbl(line_counter).invoice_line_number := l_rcpt_tbl(i).invoice_line_number;
2692 -- round off the amount applied
2693 l_inv_lns_tbl(line_counter).amount_applied := l_rcpt_tbl(i).AMOUNT_APPLIED;
2694 l_inv_lns_tbl(line_counter).amount_applied_from := l_rcpt_tbl(i).AMOUNT_APPLIED_FROM;
2695 l_inv_lns_tbl(line_counter).trans_to_receipt_rate := l_rcpt_tbl(i).trans_to_receipt_rate;
2696 l_total_amount_app_from := l_total_amount_app_from + l_inv_lns_tbl(line_counter).amount_applied_from;
2697 l_total_amount_applied := l_total_amount_applied + l_inv_lns_tbl(line_counter).amount_applied;
2698
2699 line_counter := line_counter + 1;
2700 l_prev_inv_num := l_inv_num;
2701 l_rcpt_tbl(i).amount_applied := -1;
2702 counter := counter + 1;
2703 ELSIF (i <> l_rcpt_tbl.FIRST) THEN
2704 IF (l_inv_num = l_prev_inv_num) THEN
2705 --Assign invoice lines record in invoice lines table
2706 l_inv_lns_tbl(line_counter).invoice_line_id := l_rcpt_tbl(i).invoice_line_id;
2707 l_inv_lns_tbl(line_counter).invoice_line_number := l_rcpt_tbl(i).invoice_line_number;
2708 -- round off the amount applied
2709 l_inv_lns_tbl(line_counter).amount_applied := l_rcpt_tbl(i).AMOUNT_APPLIED;
2710 l_inv_lns_tbl(line_counter).amount_applied_from := l_rcpt_tbl(i).AMOUNT_APPLIED_FROM;
2711 l_inv_lns_tbl(line_counter).trans_to_receipt_rate := l_rcpt_tbl(i).trans_to_receipt_rate;
2712 l_total_amount_app_from := l_total_amount_app_from + l_inv_lns_tbl(line_counter).amount_applied_from;
2713
2714 l_total_amount_applied := l_total_amount_applied + l_inv_lns_tbl(line_counter).amount_applied;
2715
2716 line_counter := line_counter + 1;
2717 l_prev_inv_num := l_inv_num;
2718 l_rcpt_tbl(i).amount_applied := -1;
2719 counter := counter + 1;
2720 END IF;
2721 END IF;
2722 IF ( i = l_rcpt_tbl.LAST) THEN
2723 i := l_rcpt_tbl.FIRST;
2724 complete_cycle := TRUE;
2725 --Assign lines table generated into applications table
2726 l_appl_tbl(hdr_counter).inv_hdr_rec.amount_applied := l_total_amount_applied;
2727 l_appl_tbl(hdr_counter).inv_hdr_rec.amount_applied_from := l_total_amount_app_from;
2728 l_appl_tbl(hdr_counter).inv_hdr_rec.trans_to_receipt_rate := l_inv_lns_tbl(l_inv_lns_tbl.FIRST).trans_to_receipt_rate;
2729 l_total_amount_app_from := 0;
2730 l_total_amount_applied := 0;
2731 l_appl_tbl(hdr_counter).inv_lines_tbl := l_inv_lns_tbl;
2732 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2733 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Created invoice header and its lines');
2734 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Invoice Number: '|| l_appl_tbl(hdr_counter).inv_hdr_rec.invoice_number);
2735 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Number of lines for this invoice number: '|| l_inv_lns_tbl.COUNT);
2736 END IF;
2737 --Delete lines table and reinitialize
2738 l_inv_lns_tbl.delete;
2739 line_counter := 1;
2740 hdr_counter := hdr_counter + 1;
2741 ELSE
2742 i := i + 1;
2743 END IF;
2744 ELSE
2745 IF ( i = l_rcpt_tbl.LAST) THEN
2746 i := l_rcpt_tbl.FIRST;
2747 complete_cycle := TRUE;
2748 --Assign lines table into applications table
2749 l_appl_tbl(hdr_counter).inv_hdr_rec.amount_applied := l_total_amount_applied;
2750 l_appl_tbl(hdr_counter).inv_hdr_rec.amount_applied_from := l_total_amount_app_from;
2751 l_appl_tbl(hdr_counter).inv_hdr_rec.trans_to_receipt_rate := l_inv_lns_tbl(l_inv_lns_tbl.FIRST).trans_to_receipt_rate;
2752 l_total_amount_app_from := 0;
2753 l_total_amount_applied := 0;
2754
2755 l_appl_tbl(hdr_counter).inv_lines_tbl := l_inv_lns_tbl;
2756 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2757 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Created invoice header and its lines');
2758 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Invoice Number: '|| l_appl_tbl(hdr_counter).inv_hdr_rec.invoice_number);
2759 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Number of lines for this invoice number: '|| l_inv_lns_tbl.COUNT);
2760 END IF;
2761 --Delete lines table and reinitialize
2762 l_inv_lns_tbl.delete;
2763 line_counter := 1;
2764 hdr_counter := hdr_counter + 1;
2765 ELSE
2766 i := i + 1;
2767 END IF;
2768 END IF;
2769 IF (counter = l_rcpt_tbl.COUNT) THEN
2770 EXIT;
2771 END IF;
2772 END LOOP;
2773 --If invoice lines table is not null and count is greater than 0
2774 --Then assign invoice lines table to last created invoice header.
2775 IF (l_inv_lns_tbl IS NOT NULL AND l_inv_lns_tbl.COUNT > 0) THEN
2776 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2777 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Created invoice header and its lines');
2778 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Invoice Number: '|| l_appl_tbl(hdr_counter).inv_hdr_rec.invoice_number);
2779 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Number of lines for this invoice number: '|| l_inv_lns_tbl.COUNT);
2780 END IF;
2781 l_appl_tbl(hdr_counter).inv_hdr_rec.amount_applied := l_total_amount_applied;
2782 l_appl_tbl(hdr_counter).inv_hdr_rec.amount_applied_from := l_total_amount_app_from;
2783 l_appl_tbl(hdr_counter).inv_hdr_rec.trans_to_receipt_rate := l_inv_lns_tbl(l_inv_lns_tbl.FIRST).trans_to_receipt_rate;
2784 l_total_amount_app_from := 0;
2785 l_total_amount_applied := 0;
2786 l_appl_tbl(hdr_counter).inv_lines_tbl := l_inv_lns_tbl;
2787 END IF;
2788 END IF;
2789
2790 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2791 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Padding remaining invoice headers to make number of invoice headers divisible by 8');
2792 END IF;
2793
2794 --Pad additional application records
2795 IF (l_appl_tbl.COUNT > 0 AND l_appl_tbl.COUNT < 8) OR mod((l_appl_tbl.COUNT), 8) <> 0 THEN
2796 j := l_appl_tbl.LAST;
2797 LOOP
2798 EXIT WHEN mod((l_appl_tbl.COUNT), 8) = 0; -- multiple of 8
2799 j := j + 1;
2800 l_appl_tbl(j).inv_hdr_rec.INVOICE_CURRENCY_CODE := ''; --this is just to buffer the record out !!
2801 END LOOP;
2802 END IF;
2803 --Assign local application details table to out variable
2804 x_appl_tbl := l_appl_tbl;
2805 EXCEPTION
2806 WHEN OTHERS THEN
2807 NULL;
2808 END GET_APPLICATIONS;
2809
2810 --END: Bug 6275659 by nikshah
2811
2812
2813 /* sosharma 30-jul-2007
2814 Added proceudure to handle cash application rules on consolidated Invoices
2815 */
2816 PROCEDURE auto_cashapp_for_consinv ( p_api_version IN NUMBER
2817 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
2818 ,x_return_status OUT NOCOPY VARCHAR2
2819 ,x_msg_count OUT NOCOPY NUMBER
2820 ,x_msg_data OUT NOCOPY VARCHAR2
2821 ,p_customer_num IN VARCHAR2 DEFAULT NULL
2822 ,p_cons_inv IN VARCHAR2
2823 ,p_currency_code IN VARCHAR2
2824 ,p_amount_app_to IN NUMBER DEFAULT NULL
2825 ,p_amount_app_from IN NUMBER DEFAULT NULL
2826 ,p_inv_to_rct_rate IN NUMBER DEFAULT NULL
2827 ,p_receipt_date IN DATE
2828 ,p_org_id IN NUMBER
2829 ,x_appl_tbl OUT NOCOPY okl_appl_dtls_tbl_type
2830 ,x_onacc_amount OUT NOCOPY NUMBER
2831 ,x_unapply_amount OUT NOCOPY NUMBER
2832 ) IS
2833
2834 ---------------------------
2835 -- DECLARE Local Variables
2836 ---------------------------
2837
2838
2839 l_amount_apply_pref VARCHAR2(15) := 'PRORATE';
2840
2841
2842 l_original_line_amount NUMBER;
2843 l_original_tax_amount NUMBER;
2844 l_total_amount NUMBER;
2845 l_line_amount_applied NUMBER;
2846 l_tax_amount_applied NUMBER;
2847
2848
2849 l_cons_inv VARCHAR2(120) := p_cons_inv;
2850 l_currency_code VARCHAR2(45) := p_currency_code;
2851 l_amount_app_from NUMBER := p_amount_app_from;
2852 l_amount_app_to NUMBER := p_amount_app_to;
2853 l_inv_to_rct_rate NUMBER := p_inv_to_rct_rate;
2854 l_inv_curr_Code VARCHAR2(45);
2855 l_receipt_Date DATE := p_receipt_date;
2856 l_cross_curr_enabled varchar2(3):='N';
2857 l_conversion_rate NUMBER;
2858 l_exchange_rate_type VARCHAR2(45);
2859 l_tot_amt_app_from NUMBER;
2860
2861 l_due_date DATE DEFAULT NULL;
2862
2863 l_customer_id NUMBER;
2864 l_customer_num VARCHAR2(30) := p_customer_num;
2865 l_cons_bill_id NUMBER;
2866 l_cons_bill_num VARCHAR2(90);
2867 l_last_contract_id OKC_K_HEADERS_V.ID%TYPE DEFAULT 1;
2868 l_contract_id NUMBER;
2869 l_contract_num VARCHAR2(120);
2870 l_contract_number_start_date OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT NULL;
2871 l_contract_number_id OKC_K_HEADERS_V.ID%TYPE DEFAULT NULL;
2872
2873 l_receivables_invoice_num NUMBER DEFAULT NULL;
2874 l_over_pay VARCHAR(1) DEFAULT NULL;
2875 l_ordered CONSTANT VARCHAR2(3) := 'ODD';
2876 l_prorate CONSTANT VARCHAR2(3) := 'PRO';
2877
2878
2879 l_org_id NUMBER := p_org_id;
2880
2881 i NUMBER;
2882 j NUMBER;
2883 d NUMBER DEFAULT NULL;
2884
2885 l_first_prorate_rec NUMBER DEFAULT NULL;
2886 l_first_prorate_rec_j NUMBER DEFAULT NULL;
2887
2888 l_appl_tolerance NUMBER;
2889 l_temp_val NUMBER;
2890 l_inv_tot NUMBER := 0;
2891 l_cont_tot NUMBER := 0;
2892 l_pro_rate_inv_total NUMBER := 0;
2893 l_stream_tot NUMBER := 0;
2894 l_diff_amount NUMBER := 0;
2895 l_inv_total_amt NUMBER := 0;
2896
2897
2898 l_start_date DATE;
2899 l_same_date VARCHAR(1) DEFAULT NULL;
2900 l_same_cash_app_rule VARCHAR(1) DEFAULT NULL;
2901
2902 l_count NUMBER DEFAULT NULL;
2903
2904
2905 l_check_cau_id OKL_CSH_ALLCTN_RL_HDR.ID%TYPE DEFAULT NULL;
2906 l_cau_id OKL_CSH_ALLCTN_RL_HDR.ID%TYPE DEFAULT NULL;
2907 l_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
2908 l_sty_id OKL_CNSLD_AR_STRMS_V.STY_ID%TYPE;
2909 l_tolerance OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE;
2910 l_days_past_quote_valid OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE;
2911 l_months_to_bill_ahead OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE;
2912 l_under_payment OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE;
2913 l_over_payment OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE;
2914 l_receipt_msmtch OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE;
2915
2916 l_dflt_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
2917 l_dflt_tolerance OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE DEFAULT NULL;
2918 l_dflt_days_past_quote_valid OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE DEFAULT NULL;
2919 l_dflt_months_to_bill_ahead OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE DEFAULT NULL;
2920 l_dflt_under_payment OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
2921 l_dflt_over_payment OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
2922 l_dflt_receipt_msmtch OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE DEFAULT NULL;
2923
2924 l_valid_yn VARCHAR2(1);
2925
2926 l_api_version NUMBER := 1.0;
2927 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
2928 l_return_status VARCHAR2(1);
2929 l_msg_count NUMBER;
2930 l_msg_data VARCHAR2(2000);
2931
2932 l_inv_num RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE DEFAULT NULL;
2933 l_prev_inv_num RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE DEFAULT NULL;
2934 line_counter NUMBER;
2935 hdr_counter NUMBER;
2936
2937 ------------------------------
2938 -- DECLARE Record/Table Types
2939 ------------------------------
2940
2941
2942 l_rcpt_tbl okl_auto_cash_appl_rules_pvt.okl_rcpt_dtls_tbl_type;
2943 l_appl_tbl okl_auto_cash_appl_rules_pvt.okl_appl_dtls_tbl_type;
2944 l_inv_lns_tbl okl_auto_cash_appl_rules_pvt.okl_inv_line_tbl_type;
2945
2946 -- ** internal use only ** --
2947
2948 TYPE okl_inv_refs_type IS RECORD ( contract_number AR_PAYMENTS_INTERFACE_ALL.INVOICE1%TYPE DEFAULT NULL
2949 ,cons_inv_number AR_PAYMENTS_INTERFACE_ALL.INVOICE1%TYPE DEFAULT NULL
2950 );
2951
2952 TYPE okl_inv_refs_tbl_type IS TABLE OF okl_inv_refs_type
2953 INDEX BY BINARY_INTEGER;
2954
2955 l_cust_inv_ref okl_inv_refs_tbl_type;
2956
2957
2958 -------------------
2959 -- DECLARE Cursors
2960 -------------------
2961 -- cursor to get contracts information
2962
2963 CURSOR c_open_invs2( cp_cons_bill_id IN NUMBER
2964 ,cp_org_id IN NUMBER
2965 ,cp_customer_num IN VARCHAR2
2966 ) IS
2967 SELECT lpt.stream_type_id
2968 ,lpt.amount_due_remaining invoice_due_remaining
2969 ,lpt.amount_due_original invoice_due_original
2970 ,lpt.currency_code invoice_currency_code
2971 ,lpt.AR_INVOICE_NUMBER
2972 ,lpt.AR_INVOICE_ID
2973 ,lpt.stream_element_id
2974 ,lpt.ar_invoice_line_id
2975 ,lpt.ar_invoice_line_number
2976 ,lpt.trx_date invoice_date
2977 ,lpt.contract_number
2978 FROM okl_rcpt_consinv_balances_uv lpt
2979 WHERE lpt.consolidated_invoice_id = cp_cons_bill_id
2980 --asawanka changed for bug #5391874
2981 AND lpt.customer_account_number = nvl(cp_customer_num,lpt.customer_account_number)
2982 AND lpt.org_id=cp_org_id
2983 AND lpt.status='OP';
2984
2985 c_open_invs_rec c_open_invs2%ROWTYPE;
2986 TYPE open_inv_tbl_type IS TABLE OF c_open_invs2%ROWTYPE INDEX BY BINARY_INTEGER;
2987 open_inv_tbl open_inv_tbl_type;
2988 open_inv_contract_tbl open_inv_tbl_type;
2989
2990 ----------
2991
2992 CURSOR c_inv_date ( cp_cons_bill_id IN NUMBER
2993 ,cp_customer_num IN VARCHAR2) IS
2994 SELECT DISTINCT(lpt.contract_number)
2995 ,lpt.invoice_date Start_date, lpt.contract_id,lpt.currency_code
2996 FROM okl_rcpt_consinv_balances_uv lpt
2997 WHERE lpt.consolidated_invoice_id = cp_cons_bill_id --Always passing cp_cons_bill_id as not null so no need to have nvl
2998 AND lpt.customer_account_number = NVL (cp_customer_num, lpt.customer_account_number);
2999
3000
3001 c_inv_date_rec c_inv_date%ROWTYPE;
3002
3003 ----------
3004
3005 -- get stream application order
3006 CURSOR c_stream_alloc ( cp_str_all_type IN VARCHAR2
3007 ,cp_cat_id IN NUMBER ) IS
3008 SELECT sty_id
3009 FROM OKL_STRM_TYP_ALLOCS
3010 WHERE stream_allc_type = cp_str_all_type
3011 AND cat_id = cp_cat_id
3012 ORDER BY sequence_number;
3013
3014 ----------
3015
3016 /*
3017 -- get cash applic rule id
3018 */
3019
3020 CURSOR c_cash_rle_id_csr ( cp_khr_id IN NUMBER) IS
3021 SELECT to_number(a.object1_id1)
3022 FROM OKC_RULES_B a, OKC_RULE_GROUPS_B b
3023 WHERE a.rgp_id = b.id
3024 AND b.rgd_code = 'LABILL'
3025 AND a.rule_information_category = 'LAINVD'
3026 AND a.dnz_chr_id = b.chr_id
3027 AND a.dnz_chr_id = cp_khr_id;
3028
3029 ----------
3030
3031 -- get cash applic rule for contract
3032 CURSOR c_cash_rule_csr ( cp_cau_id IN NUMBER ) IS
3033 SELECT ID
3034 ,AMOUNT_TOLERANCE_PERCENT
3035 ,DAYS_PAST_QUOTE_VALID_TOLERANC
3036 ,MONTHS_TO_BILL_AHEAD
3037 ,UNDER_PAYMENT_ALLOCATION_CODE
3038 ,OVER_PAYMENT_ALLOCATION_CODE
3039 ,RECEIPT_MSMTCH_ALLOCATION_CODE
3040 FROM OKL_CASH_ALLCTN_RLS
3041 WHERE CAU_ID = cp_cau_id
3042 AND START_DATE <= trunc(SYSDATE)
3043 AND (END_DATE >= trunc(SYSDATE) OR END_DATE IS NULL);
3044
3045
3046 ----------
3047 -- get a contract number if not known
3048 CURSOR c_get_contract_num (cp_cons_bill_id IN NUMBER) IS
3049 SELECT lpt.contract_id, lpt.contract_number
3050 FROM okl_rcpt_consinv_balances_uv lpt, okc_k_headers_all_b khr
3051 WHERE lpt.consolidated_invoice_id = cp_cons_bill_id
3052 AND lpt.status = 'OP'
3053 AND lpt.amount_due_remaining > 0
3054 AND khr.id = lpt.contract_id
3055 ORDER BY khr.start_date;
3056 ----------
3057
3058
3059 CURSOR valid_consinv(cl_cons_inv IN VARCHAR2)
3060 IS
3061 select ID
3062 from OKL_CNSLD_AR_HDRS_B
3063 where consolidated_invoice_number=cl_cons_inv;
3064
3065 l_unapply_amount NUMBER:=0;
3066 l_onacc_amount NUMBER:=0;
3067
3068 BEGIN
3069
3070 OPEN valid_consinv(l_cons_inv);
3071 FETCH valid_consinv into l_cons_bill_id;
3072 IF l_cons_bill_id IS NULL THEN
3073 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3074 RETURN;
3075 END IF;
3076 CLOSE valid_consinv;
3077
3078 IF l_cons_inv IS NOT NULL THEN
3079 l_count := 0;
3080 i := 0;
3081 FOR c_open_invs_rec IN c_open_invs2 (l_cons_bill_id,l_org_id, l_customer_num)
3082 LOOP
3083 IF c_open_invs_rec.invoice_due_remaining > 0 THEN
3084 i := i + 1;
3085 open_inv_tbl(i) := c_open_invs_rec;
3086 l_count := l_count + 1;
3087 END IF;
3088 END LOOP;
3089 i := 0;
3090 IF l_count > 0 THEN
3091 l_cons_bill_num := l_cons_inv;
3092 l_contract_num := NULL;
3093 END IF;
3094
3095 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3096 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_contract_num : '||l_contract_num);
3097 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cons_bill_num : '||l_cons_bill_num);
3098 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cons_bill_id : '||l_cons_bill_id);
3099 END IF;
3100 END IF;
3101
3102
3103
3104
3105 -- START OKL CASH APPLICATION.
3106
3107 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3108 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Checking whether l_contract_num or l_cons_bill_num is not null ..');
3109
3110 END IF;
3111
3112 IF l_cons_bill_num IS NOT NULL THEN --(1)
3113
3114 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3115 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cons_bill_num is not null - '||l_cons_bill_num);
3116
3117 END IF;
3118
3119 j := 0;
3120
3121 OPEN c_inv_date(l_cons_bill_id, l_customer_num);
3122 FETCH c_inv_date INTO l_contract_number_start_date, l_start_date, l_contract_number_id,l_inv_curr_Code;
3123 CLOSE c_inv_date;
3124
3125 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3126 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_contract_number_start_date, l_start_date, l_contract_number_id : '||
3127 l_contract_number_start_date||', '||l_start_date||', '||l_contract_number_id);
3128
3129 END IF;
3130
3131 d :=0;
3132 FOR c_inv_date_rec IN c_inv_date(l_cons_bill_id, l_customer_num)
3133 LOOP
3134
3135 IF TRUNC(l_start_date) = TRUNC(c_inv_date_rec.start_date) THEN
3136 l_same_date := 'Y';
3137 d := d + 1;
3138
3139 ELSE
3140 l_same_date := 'N';
3141 EXIT;
3142 END IF;
3143
3144 END LOOP;
3145
3146 IF d = 1 THEN
3147 l_same_date := 'N';
3148 END IF;
3149
3150 -- ************************************************
3151 -- Check for same cash application rule
3152 -- ************************************************
3153
3154 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3155 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Check for same cash application rule');
3156
3157
3158 END IF;
3159 OPEN c_cash_rle_id_csr (l_contract_number_id);
3160 FETCH c_cash_rle_id_csr INTO l_cau_id;
3161 CLOSE c_cash_rle_id_csr;
3162
3163 d := 0;
3164 FOR c_inv_date_rec IN c_inv_date(l_cons_bill_id, l_customer_num)
3165 LOOP
3166
3167 l_check_cau_id := NULL;
3168
3169 OPEN c_cash_rle_id_csr (c_inv_date_rec.contract_id);
3170 FETCH c_cash_rle_id_csr INTO l_check_cau_id;
3171 CLOSE c_cash_rle_id_csr;
3172
3173 IF l_check_cau_id IS NULL THEN
3174 l_same_cash_app_rule := 'N';
3175 EXIT;
3176 END IF;
3177
3178 IF l_cau_id = l_check_cau_id THEN
3179 l_same_cash_app_rule := 'Y';
3180 d := d + 1;
3181 ELSE
3182 l_same_cash_app_rule := 'N';
3183 EXIT;
3184 END IF;
3185
3186 END LOOP;
3187
3188 IF d = 1 THEN
3189 l_same_cash_app_rule := 'N';
3190 END IF;
3191
3192 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3193 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_same_date, l_same_cash_app_rule : '||
3194 l_same_date||', '||l_same_cash_app_rule);
3195
3196 END IF;
3197
3198
3199
3200 log_debug('Receipt Currency = '||l_currency_code);
3201 log_debug('Contract Currency = '||l_inv_curr_Code);
3202 log_debug('l_amount_app_from = '||l_amount_app_from);
3203 log_debug('l_amount_app_to = '||l_amount_app_to);
3204 log_debug('l_inv_to_rct_rate = '||l_inv_to_rct_rate);
3205 IF l_currency_code = l_inv_curr_Code THEN
3206 IF l_amount_app_from IS NULL AND l_amount_app_to IS NULL THEN
3207 OKL_API.set_message( p_app_name => G_APP_NAME
3208 ,p_msg_name => 'OKL_BPD_INVALID_PARAMS'
3209 );
3210 RAISE G_EXCEPTION_HALT_VALIDATION;
3211 ELSIF l_amount_app_from IS NULL THEN
3212 l_amount_app_from := l_amount_app_to;
3213 ELSE
3214 l_amount_app_to := l_amount_app_from;
3215 END IF;
3216 ELSE
3217 IF l_amount_app_from IS NOT NULL AND l_amount_app_to IS NOT NULL AND l_inv_to_rct_rate IS NOT NULL THEN
3218 IF ( l_amount_app_to * l_inv_to_rct_rate) <> l_amount_app_from THEN
3219 OKL_API.set_message( p_app_name => G_APP_NAME
3220 ,p_msg_name => 'OKL_BPD_PARAMS_MISMATCH'
3221 );
3222 END IF;
3223 IF l_inv_to_rct_rate <> 0 and ( ( l_amount_app_from / l_inv_to_rct_rate) <> l_amount_app_to) THEN
3224 OKL_API.set_message( p_app_name => G_APP_NAME
3225 ,p_msg_name => 'OKL_BPD_PARAMS_MISMATCH'
3226 );
3227 END IF;
3228 END IF;
3229 /* l_cross_curr_enabled := nvl(FND_PROFILE.value('AR_ENABLE_CROSS_CURRENCY'),'N');
3230 log_debug('l_cross_curr_enabled = '||l_cross_curr_enabled);
3231 IF l_cross_curr_enabled <> 'Y' THEN
3232 OKL_API.set_message( p_app_name => G_APP_NAME
3233 ,p_msg_name => 'OKL_BPD_CROSS_CURR_NA'
3234 );
3235 RAISE G_EXCEPTION_HALT_VALIDATION;
3236 ELSE*/
3237 IF l_inv_to_rct_rate is null THEN
3238 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
3239 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
3240 IF l_exchange_rate_type IS NULL THEN
3241 OKL_API.set_message( p_app_name => G_APP_NAME
3242 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
3243 );
3244 RAISE G_EXCEPTION_HALT_VALIDATION;
3245 ELSE
3246 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
3247 ,l_currency_code
3248 ,l_receipt_date
3249 ,l_exchange_rate_type
3250 );
3251
3252 IF l_conversion_rate IN (0,-1) THEN
3253
3254 -- Message Text: No exchange rate defined
3255 x_return_status := okl_api.G_RET_STS_ERROR;
3256 okl_api.set_message( p_app_name => G_APP_NAME,
3257 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
3258 RAISE G_EXCEPTION_HALT_VALIDATION;
3259 END IF;
3260 END IF;
3261 ELSE
3262 l_conversion_rate := l_inv_to_rct_rate;
3263 END IF;
3264 log_debug('l_conversion_rate ='||l_conversion_rate);
3265 IF l_amount_app_from IS NULL AND l_amount_app_to IS NULL THEN
3266 OKL_API.set_message( p_app_name => G_APP_NAME
3267 ,p_msg_name => 'OKL_BPD_INVALID_PARAMS'
3268 );
3269 RAISE G_EXCEPTION_HALT_VALIDATION;
3270 ELSIF l_amount_app_from IS NULL THEN
3271 l_amount_app_from := l_amount_app_to * l_conversion_rate;
3272 ELSE
3273 l_amount_app_to := l_amount_app_from * (1/l_conversion_rate);
3274 END IF;
3275 -- END IF;
3276 END IF;
3277 l_amount_app_from := arp_util.CurrRound(l_amount_app_from,l_currency_code);
3278 l_amount_app_to := arp_util.CurrRound(l_amount_app_to,l_inv_curr_code);
3279 log_debug('l_amount_app_from = '||l_amount_app_from);
3280 log_debug('l_amount_app_to = '||l_amount_app_to);
3281 log_debug('l_inv_to_rct_rate = '||l_inv_to_rct_rate);
3282
3283 -- IF l_same_date = 'Y' AND l_same_cash_app_rule = 'Y' THEN --(1)
3284 IF l_same_date = 'Y' THEN
3285
3286 IF l_same_cash_app_rule = 'Y' THEN -- Use Common Cash Application
3287 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3288 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Date and CAR are same for all contracts ...');
3289 END IF;
3290 -- *******************************************************
3291 -- Start Line level cash application using the same cash
3292 -- application rule for all
3293 -- *******************************************************
3294 OPEN c_cash_rule_csr (l_cau_id);
3295 FETCH c_cash_rule_csr
3296 INTO l_cat_id
3297 ,l_tolerance
3298 ,l_days_past_quote_valid
3299 ,l_months_to_bill_ahead
3300 ,l_under_payment
3301 ,l_over_payment
3302 ,l_receipt_msmtch;
3303 CLOSE c_cash_rule_csr;
3304 Elsif l_same_cash_app_rule = 'N' THEN -- Use Default Cash Application
3305 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3306 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Same Date but different CAR for all contracts ...');
3307 END IF;
3308 -- *******************************************************
3309 -- Start Line level cash application using the same cash
3310 -- application rule for all
3311 -- *******************************************************
3312 Get_Default_Cash_App_Rule(
3313 l_org_id
3314 ,l_cat_id
3315 ,l_tolerance
3316 ,l_days_past_quote_valid
3317 ,l_months_to_bill_ahead
3318 ,l_under_payment
3319 ,l_over_payment
3320 ,l_receipt_msmtch);
3321 End If;
3322 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3323 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Date and CAR are same for all contracts ...');
3324
3325 END IF;
3326
3327 -- ************************************************
3328 -- Stream level cash application processing BEGINS
3329 -- ************************************************
3330
3331 -- get stream total
3332
3333 l_stream_tot := 0;
3334 FOR i IN open_inv_tbl.FIRST..open_inv_tbl.LAST
3335 LOOP
3336 -- l_invoice_currency_code := c_open_invs_rec.currency_code;
3337 c_open_invs_rec := open_inv_tbl(i);
3338 IF c_open_invs_rec.invoice_due_remaining > 0 THEN
3339 l_stream_tot := l_stream_tot + c_open_invs_rec.invoice_due_remaining;
3340 END IF;
3341 END LOOP;
3342
3343 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3344 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_stream_tot : ' || l_stream_tot);
3345 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_amount_app_to : ' || l_amount_app_to);
3346
3347
3348
3349 END IF;
3350 -- calculate tolerance
3351 IF l_stream_tot > l_amount_app_to THEN
3352 l_appl_tolerance := l_stream_tot * (1 - l_tolerance / 100);
3353 ELSE
3354 l_appl_tolerance := l_stream_tot;
3355 END IF;
3356
3357 IF l_stream_tot > l_amount_app_to AND l_appl_tolerance > l_amount_app_to THEN -- UNDERPAYMENT (2)
3358
3359 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3360 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'UNDERPAYMENT ...');
3361
3362 END IF;
3363
3364 IF l_under_payment In ('U','u') THEN -- Unapply underpayment (3)
3365 IF l_currency_code = l_inv_curr_code THEN
3366 l_unapply_amount:=l_amount_app_to;
3367 ELSE
3368 l_unapply_amount:= l_amount_app_to * l_conversion_rate;
3369 l_unapply_amount := GET_ROUNDED_AMOUNT(l_unapply_amount,l_currency_code);
3370 END IF;
3371 ELSIF l_under_payment IN ('T','t') THEN -- ORDERED (3)
3372
3373 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3374 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ORDERED ...');
3375
3376 END IF;
3377 OPEN c_stream_alloc (l_ordered, l_cat_id);
3378 LOOP
3379 FETCH c_stream_alloc INTO l_sty_id;
3380 EXIT WHEN c_stream_alloc%NOTFOUND
3381 OR l_amount_app_to = 0
3382 OR l_amount_app_to IS NULL;
3383
3384 FOR i IN open_inv_tbl.FIRST..open_inv_tbl.LAST
3385 LOOP
3386 c_open_invs_rec := open_inv_tbl(i);
3387 EXIT WHEN l_amount_app_to = 0 OR l_amount_app_to IS NULL;
3388 IF c_open_invs_rec.stream_type_id = l_sty_id THEN
3389 j := j + 1;
3390
3391 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invs_rec.ar_invoice_number;
3392 -- added for AR changes
3393 l_rcpt_tbl(j).INVOICE_ID := c_open_invs_rec.ar_invoice_id;
3394 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invs_rec.ar_invoice_line_number;
3395 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invs_rec.ar_invoice_line_id;
3396 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
3397 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invs_rec.invoice_due_remaining;
3398
3399 IF l_rcpt_tbl(j).AMOUNT_APPLIED > l_amount_app_to THEN
3400 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
3401 l_amount_app_to := 0;
3402 ELSE
3403 l_amount_app_to := l_amount_app_to - l_rcpt_tbl(j).AMOUNT_APPLIED;
3404 END IF;
3405 IF l_currency_code <> l_inv_curr_code THEN
3406 IF l_inv_to_rct_rate is null THEN
3407 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
3408 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
3409 IF l_exchange_rate_type IS NULL THEN
3410 OKL_API.set_message( p_app_name => G_APP_NAME
3411 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
3412 );
3413 RAISE G_EXCEPTION_HALT_VALIDATION;
3414 ELSE
3415 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
3416 ,l_currency_code
3417 ,l_receipt_date
3418 ,l_exchange_rate_type
3419 );
3420
3421 IF l_conversion_rate IN (0,-1) THEN
3422
3423 -- Message Text: No exchange rate defined
3424 x_return_status := okl_api.G_RET_STS_ERROR;
3425 okl_api.set_message( p_app_name => G_APP_NAME,
3426 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
3427 RAISE G_EXCEPTION_HALT_VALIDATION;
3428 END IF;
3429 END IF;
3430 ELSE
3431 l_conversion_rate := l_inv_to_rct_rate;
3432 END IF;
3433 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
3434 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
3435 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
3436 ELSE
3437 l_rcpt_tbl(j).trans_to_receipt_rate := null;
3438 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
3439 END IF;
3440 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3441 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
3442 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
3443 END IF;
3444 END IF;
3445 END LOOP;
3446 END LOOP;
3447 CLOSE c_stream_alloc;
3448
3449 ELSIF l_under_payment IN ('P','p') THEN -- PRO RATE (3)
3450
3451 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3452 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'PRO RATE ...');
3453
3454 END IF;
3455
3456 l_first_prorate_rec_j := j + 1;
3457
3458 -- i := 1;
3459 -- obtain all the streams that are part of the pro rate default rule.
3460
3461 FOR c_stream_alloc_rec IN c_stream_alloc (l_prorate, l_cat_id)
3462 LOOP
3463
3464 l_sty_id := c_stream_alloc_rec.sty_id;
3465 FOR i IN open_inv_tbl.FIRST..open_inv_tbl.LAST
3466 LOOP
3467 c_open_invs_rec := open_inv_tbl(i);
3468 IF c_open_invs_rec.stream_type_id = l_sty_id THEN
3469 j := j + 1;
3470
3471 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invs_rec.invoice_due_remaining;
3472 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invs_rec.ar_invoice_number;
3473 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
3474 l_rcpt_tbl(j).INVOICE_ID := c_open_invs_rec.ar_invoice_id;
3475 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invs_rec.ar_invoice_line_number;
3476 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invs_rec.ar_invoice_line_id;
3477 IF l_currency_code <> l_inv_curr_code THEN
3478 IF l_inv_to_rct_rate is null THEN
3479 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
3480 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
3481 IF l_exchange_rate_type IS NULL THEN
3482 OKL_API.set_message( p_app_name => G_APP_NAME
3483 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
3484 );
3485 RAISE G_EXCEPTION_HALT_VALIDATION;
3486 ELSE
3487 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
3488 ,l_currency_code
3489 ,l_receipt_date
3490 ,l_exchange_rate_type
3491 );
3492
3493 IF l_conversion_rate IN (0,-1) THEN
3494
3495 -- Message Text: No exchange rate defined
3496 x_return_status := okl_api.G_RET_STS_ERROR;
3497 okl_api.set_message( p_app_name => G_APP_NAME,
3498 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
3499 RAISE G_EXCEPTION_HALT_VALIDATION;
3500 END IF;
3501 END IF;
3502 ELSE
3503 l_conversion_rate := l_inv_to_rct_rate;
3504 END IF;
3505 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
3506 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
3507 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
3508 ELSE
3509 l_rcpt_tbl(j).trans_to_receipt_rate := null;
3510 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
3511 END IF;
3512 l_pro_rate_inv_total := l_pro_rate_inv_total + l_rcpt_tbl(j).AMOUNT_APPLIED;
3513
3514 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3515 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
3516 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
3517 END IF;
3518 END IF;
3519 END LOOP; -- c_open_invs
3520 END LOOP; -- c_stream_alloc
3521
3522 -- Calc Pro Ration
3523 -- only if total amount of prorated invoices is greater than receipt
3524
3525 IF l_pro_rate_inv_total IS NULL OR l_pro_rate_inv_total = 0 THEN
3526
3527 -- Message Text: No prorated transaction types for contract.
3528 x_return_status := OKC_API.G_RET_STS_ERROR;
3529
3530 OKC_API.set_message( p_app_name => G_APP_NAME
3531 ,p_msg_name => 'OKL_BPD_DEF_NO_PRO'
3532 );
3533
3534 RAISE G_EXCEPTION_HALT_VALIDATION;
3535
3536 END IF;
3537
3538 IF (l_pro_rate_inv_total > l_amount_app_to) THEN
3539
3540 j := l_first_prorate_rec_j;
3541
3542 l_temp_val := l_amount_app_to / l_pro_rate_inv_total;
3543
3544 -- l_amount_app_to := 0;
3545
3546 LOOP
3547 l_rcpt_tbl(j).AMOUNT_APPLIED := l_temp_val * l_rcpt_tbl(j).AMOUNT_APPLIED;
3548 l_rcpt_tbl(j).AMOUNT_APPLIED:=GET_ROUNDED_AMOUNT(l_rcpt_tbl(j).AMOUNT_APPLIED,l_inv_curr_Code);
3549 IF l_currency_code <> l_inv_curr_code THEN
3550 IF l_inv_to_rct_rate is null THEN
3551 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
3552 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
3553 IF l_exchange_rate_type IS NULL THEN
3554 OKL_API.set_message( p_app_name => G_APP_NAME
3555 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
3556 );
3557 RAISE G_EXCEPTION_HALT_VALIDATION;
3558 ELSE
3559 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
3560 ,l_currency_code
3561 ,l_receipt_date
3562 ,l_exchange_rate_type
3563 );
3564
3565 IF l_conversion_rate IN (0,-1) THEN
3566
3567 -- Message Text: No exchange rate defined
3568 x_return_status := okl_api.G_RET_STS_ERROR;
3569 okl_api.set_message( p_app_name => G_APP_NAME,
3570 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
3571 RAISE G_EXCEPTION_HALT_VALIDATION;
3572 END IF;
3573 END IF;
3574 ELSE
3575 l_conversion_rate := l_inv_to_rct_rate;
3576 END IF;
3577 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
3578 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
3579 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
3580 ELSE
3581 l_rcpt_tbl(j).trans_to_receipt_rate := null;
3582 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
3583 END IF;
3584 l_inv_total_amt := l_inv_total_amt + l_rcpt_tbl(j).AMOUNT_APPLIED;
3585 EXIT WHEN (j = l_rcpt_tbl.LAST);
3586 j := j + 1;
3587 END LOOP;
3588 l_diff_amount := l_amount_app_to - l_inv_total_amt;
3589 if l_diff_amount > 0 then
3590 l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied := l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied + l_diff_amount;
3591 IF l_currency_code <> l_inv_curr_code THEN
3592 IF l_inv_to_rct_rate is null THEN
3593 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
3594 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
3595 IF l_exchange_rate_type IS NULL THEN
3596 OKL_API.set_message( p_app_name => G_APP_NAME
3597 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
3598 );
3599 RAISE G_EXCEPTION_HALT_VALIDATION;
3600 ELSE
3601 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
3602 ,l_currency_code
3603 ,l_receipt_date
3604 ,l_exchange_rate_type
3605 );
3606
3607 IF l_conversion_rate IN (0,-1) THEN
3608
3609 -- Message Text: No exchange rate defined
3610 x_return_status := okl_api.G_RET_STS_ERROR;
3611 okl_api.set_message( p_app_name => G_APP_NAME,
3612 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
3613 RAISE G_EXCEPTION_HALT_VALIDATION;
3614 END IF;
3615 END IF;
3616 ELSE
3617 l_conversion_rate := l_inv_to_rct_rate;
3618 END IF;
3619 l_rcpt_tbl(l_rcpt_tbl.LAST).trans_to_receipt_rate := l_conversion_rate;
3620 l_rcpt_tbl(l_rcpt_tbl.LAST).AMOUNT_APPLIED_FROM := l_rcpt_tbl(l_rcpt_tbl.LAST).AMOUNT_APPLIED * l_conversion_rate;
3621 l_rcpt_tbl(l_rcpt_tbl.LAST).AMOUNT_APPLIED_FROM := arp_util.CurrRound(l_rcpt_tbl(l_rcpt_tbl.LAST).AMOUNT_APPLIED_FROM,l_currency_code);
3622 ELSE
3623 l_rcpt_tbl(l_rcpt_tbl.LAST).trans_to_receipt_rate := null;
3624 l_rcpt_tbl(l_rcpt_tbl.LAST).AMOUNT_APPLIED_FROM := null;
3625 END IF;
3626 end if;
3627
3628
3629 END IF; -- bug 5221326
3630
3631 END IF; -- (3)
3632
3633 ELSE -- EXACT or OVERPAYMENT or TOLERANCE (2)
3634
3635 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3636 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'EXACT or OVERPAYMENT or TOLERANCE');
3637
3638 END IF;
3639 -- CREATE LINES TABLE
3640
3641 FOR i IN open_inv_tbl.FIRST..open_inv_tbl.LAST
3642 LOOP
3643 c_open_invs_rec := open_inv_tbl(i);
3644 EXIT WHEN l_amount_app_to = 0 OR l_amount_app_to IS NULL;
3645 j := j + 1;
3646 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invs_rec.invoice_due_remaining;
3647 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invs_rec.ar_invoice_number;
3648 l_rcpt_tbl(j).INVOICE_ID := c_open_invs_rec.ar_invoice_id;
3649 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invs_rec.ar_invoice_line_number;
3650 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invs_rec.ar_invoice_line_id;
3651 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
3652
3653 IF l_amount_app_to < l_rcpt_tbl(j).AMOUNT_APPLIED THEN
3654 -- TOLERANCE
3655 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
3656 l_amount_app_to := 0;
3657 ELSE
3658 l_amount_app_to := l_amount_app_to - l_rcpt_tbl(j).AMOUNT_APPLIED;
3659 END IF;
3660 IF l_currency_code <> l_inv_curr_code THEN
3661 IF l_inv_to_rct_rate is null THEN
3662 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
3663 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
3664 IF l_exchange_rate_type IS NULL THEN
3665 OKL_API.set_message( p_app_name => G_APP_NAME
3666 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
3667 );
3668 RAISE G_EXCEPTION_HALT_VALIDATION;
3669 ELSE
3670 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
3671 ,l_currency_code
3672 ,l_receipt_date
3673 ,l_exchange_rate_type
3674 );
3675
3676 IF l_conversion_rate IN (0,-1) THEN
3677
3678 -- Message Text: No exchange rate defined
3679 x_return_status := okl_api.G_RET_STS_ERROR;
3680 okl_api.set_message( p_app_name => G_APP_NAME,
3681 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
3682 RAISE G_EXCEPTION_HALT_VALIDATION;
3683 END IF;
3684 END IF;
3685 ELSE
3686 l_conversion_rate := l_inv_to_rct_rate;
3687 END IF;
3688 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
3689 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
3690 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
3691 ELSE
3692 l_rcpt_tbl(j).trans_to_receipt_rate := null;
3693 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
3694 END IF;
3695 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3696 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
3697 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
3698 END IF;
3699 END LOOP;
3700 -- Apply the remaining balance as per the Cash Application Rule
3701 If l_over_payment In ('B','b') Then -- Onaccount --move cash to customer balances -OVP
3702 IF l_currency_code = l_inv_curr_code THEN
3703 l_onacc_amount:=l_amount_app_to;
3704 ELSE
3705 l_onacc_amount:= l_amount_app_to * l_conversion_rate;
3706 l_onacc_amount := GET_ROUNDED_AMOUNT(l_onacc_amount,l_currency_code);
3707 END IF;
3708 Elsif l_over_payment In ('F','f') Then --Unapply -- move cash to unapplied -OVP
3709 IF l_currency_code = l_inv_curr_code THEN
3710 l_unapply_amount:=l_amount_app_to;
3711 ELSE
3712 l_unapply_amount:= l_amount_app_to * l_conversion_rate;
3713 l_unapply_amount := GET_ROUNDED_AMOUNT(l_unapply_amount,l_currency_code);
3714 END IF;
3715 End If;
3716 END IF; -- under payment. (2)
3717
3718
3719 -- **********************************************
3720 -- Stream level cash application processing ENDS
3721 -- **********************************************
3722
3723 ELSE
3724
3725 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3726 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Per/Contract level cash application processing BEGINS');
3727
3728 END IF;
3729 -- ******************************************************
3730 -- Per/Contract level cash application processing BEGINS
3731 -- ******************************************************
3732
3733
3734 OPEN c_get_contract_num(l_cons_bill_id);
3735
3736 LOOP
3737
3738 FETCH c_get_contract_num INTO l_contract_id, l_contract_num;
3739
3740 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3741 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_contract_num : '||l_contract_num);
3742 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_amount_app_to : '||l_amount_app_to);
3743
3744 END IF;
3745 EXIT WHEN c_get_contract_num%NOTFOUND
3746 OR l_amount_app_to = 0
3747 OR l_amount_app_to IS NULL;
3748
3749 IF l_last_contract_id <> l_contract_id THEN -- added by bv
3750
3751 l_last_contract_id := l_contract_id; -- added by bv
3752
3753 IF l_contract_num IS NOT NULL THEN
3754
3755 OPEN c_cash_rle_id_csr (l_contract_id);
3756 FETCH c_cash_rle_id_csr INTO l_cau_id;
3757 CLOSE c_cash_rle_id_csr;
3758
3759 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3760 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cau_id : '||l_cau_id);
3761
3762 END IF;
3763 IF l_cau_id IS NOT NULL THEN
3764
3765 OPEN c_cash_rule_csr (l_cau_id);
3766 FETCH c_cash_rule_csr INTO l_cat_id
3767 ,l_tolerance
3768 ,l_days_past_quote_valid
3769 ,l_months_to_bill_ahead
3770 ,l_under_payment
3771 ,l_over_payment
3772 ,l_receipt_msmtch;
3773 CLOSE c_cash_rule_csr;
3774
3775 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3776 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_tolerance : '||l_tolerance);
3777
3778 END IF;
3779 IF l_tolerance IS NULL THEN
3780 Get_Default_Cash_App_Rule(
3781 l_org_id
3782 ,l_cat_id
3783 ,l_tolerance
3784 ,l_days_past_quote_valid
3785 ,l_months_to_bill_ahead
3786 ,l_under_payment
3787 ,l_over_payment
3788 ,l_receipt_msmtch);
3789 END IF;
3790
3791 ELSE -- use default rule
3792
3793 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3794 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cau_id is null, using default cash appln rule');
3795
3796 END IF;
3797 Get_Default_Cash_App_Rule(
3798 l_org_id
3799 ,l_cat_id
3800 ,l_tolerance
3801 ,l_days_past_quote_valid
3802 ,l_months_to_bill_ahead
3803 ,l_under_payment
3804 ,l_over_payment
3805 ,l_receipt_msmtch);
3806
3807 END IF;
3808
3809 -- get contract total
3810 l_cont_tot := 0;
3811 j := 0;
3812 FOR i IN open_inv_tbl.FIRST..open_inv_tbl.LAST
3813 LOOP
3814 IF open_inv_tbl(i).contract_number = l_contract_num THEN
3815 -- l_invoice_currency_code := c_open_invs_rec.currency_code;
3816 j := j + 1;
3817 open_inv_contract_tbl(j) := open_inv_tbl(i);
3818 l_cont_tot := l_cont_tot + open_inv_tbl(i).invoice_due_remaining;
3819 END IF;
3820 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3821 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cont_tot : '||l_cont_tot);
3822 END IF;
3823 END LOOP;
3824 j := 0;
3825
3826 IF NVL(l_cau_id, 0) = -1 THEN -- VR 07-Oct-2005 GE-20 Receipts - On Account CAR
3827 -- Receipt needs to be left as unapplied
3828 j := 1;
3829 l_rcpt_tbl(j).INVOICE_ID := NULL;
3830 l_rcpt_tbl(j).INVOICE_LINE_ID := NULL;
3831 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := NULL;
3832 l_rcpt_tbl(j).INVOICE_NUMBER := NULL;
3833 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
3834 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
3835
3836 ELSE
3837
3838 -- calculate tolerance
3839 IF l_cont_tot > l_amount_app_to THEN
3840 l_appl_tolerance := l_cont_tot * (1 - l_tolerance / 100);
3841 ELSE
3842 l_appl_tolerance := l_cont_tot;
3843 END IF;
3844
3845 -- Contract level cash application processing begins.
3846 -- *************************************************
3847 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3848 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Checking UNDERPAYMENT/OVERPAYMENT/MATCH');
3849 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cont_tot : '||l_cont_tot);
3850 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_amount_app_to : '||l_amount_app_to);
3851 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_appl_tolerance : '||l_appl_tolerance);
3852 END IF;
3853 IF l_cont_tot > l_amount_app_to AND l_appl_tolerance > l_amount_app_to THEN -- UNDERPAYMENT (2)
3854 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3855 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'UNDERPAYMENT');
3856 END IF;
3857
3858 IF l_under_payment In ('U','u') THEN -- Unapply underpayment (3)
3859 IF l_currency_code = l_inv_curr_code THEN
3860 l_unapply_amount:=l_amount_app_to;
3861 ELSE
3862 l_unapply_amount:= l_amount_app_to * l_conversion_rate;
3863 l_unapply_amount := GET_ROUNDED_AMOUNT(l_unapply_amount,l_currency_code);
3864 END IF;
3865 ELSIF l_under_payment IN ('T','t') THEN -- ORDERED (3)
3866
3867 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3868 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ORDERED');
3869 END IF;
3870 OPEN c_stream_alloc (l_ordered, l_cat_id);
3871 LOOP
3872 FETCH c_stream_alloc INTO l_sty_id;
3873 EXIT WHEN c_stream_alloc%NOTFOUND
3874 OR l_amount_app_to = 0
3875 OR l_amount_app_to IS NULL;
3876
3877 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
3878 LOOP
3879 c_open_invs_rec := open_inv_contract_tbl(i);
3880 EXIT WHEN l_amount_app_to = 0 OR l_amount_app_to IS NULL;
3881 IF c_open_invs_rec.stream_type_id = l_sty_id THEN
3882 j := j + 1;
3883
3884 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invs_rec.invoice_due_remaining;
3885 l_rcpt_tbl(j).INVOICE_ID := c_open_invs_rec.ar_invoice_id;
3886 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invs_rec.ar_invoice_number;
3887 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
3888 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invs_rec.ar_invoice_line_id;
3889 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invs_rec.ar_invoice_line_number;
3890
3891 IF l_rcpt_tbl(j).AMOUNT_APPLIED > l_amount_app_to THEN
3892 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
3893 l_amount_app_to := 0;
3894 ELSE
3895 l_amount_app_to := l_amount_app_to - l_rcpt_tbl(j).AMOUNT_APPLIED;
3896 END IF;
3897
3898 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
3899
3900 --j := j + 1;
3901 IF l_currency_code <> l_inv_curr_code THEN
3902 IF l_inv_to_rct_rate is null THEN
3903 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
3904 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
3905 IF l_exchange_rate_type IS NULL THEN
3906 OKL_API.set_message( p_app_name => G_APP_NAME
3907 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
3908 );
3909 RAISE G_EXCEPTION_HALT_VALIDATION;
3910 ELSE
3911 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
3912 ,l_currency_code
3913 ,l_receipt_date
3914 ,l_exchange_rate_type
3915 );
3916
3917 IF l_conversion_rate IN (0,-1) THEN
3918
3919 -- Message Text: No exchange rate defined
3920 x_return_status := okl_api.G_RET_STS_ERROR;
3921 okl_api.set_message( p_app_name => G_APP_NAME,
3922 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
3923 RAISE G_EXCEPTION_HALT_VALIDATION;
3924 END IF;
3925 END IF;
3926 ELSE
3927 l_conversion_rate := l_inv_to_rct_rate;
3928 END IF;
3929 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
3930 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
3931 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
3932 ELSE
3933 l_rcpt_tbl(j).trans_to_receipt_rate := null;
3934 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
3935 END IF;
3936 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3937 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
3938 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
3939 END IF;
3940 END IF;
3941 END LOOP;
3942 END LOOP;
3943 CLOSE c_stream_alloc;
3944
3945 ELSIF l_under_payment IN ('P','p') THEN -- PRO RATE (3)
3946
3947 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3948 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'PRO RATE');
3949 END IF;
3950 l_first_prorate_rec_j := j + 1;
3951
3952 -- obtain all the streams that are part of the pro rate user defined list.
3953
3954 FOR c_stream_alloc_rec IN c_stream_alloc (l_prorate, l_cat_id)
3955 LOOP
3956
3957 l_sty_id := c_stream_alloc_rec.sty_id;
3958 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
3959 LOOP
3960 c_open_invs_rec := open_inv_contract_tbl(i);
3961 IF c_open_invs_rec.stream_type_id = l_sty_id THEN
3962 j := j + 1;
3963
3964 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invs_rec.invoice_due_remaining;
3965 l_rcpt_tbl(j).INVOICE_ID := c_open_invs_rec.ar_invoice_id;
3966 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invs_rec.ar_invoice_number;
3967 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
3968 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invs_rec.ar_invoice_line_id;
3969 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invs_rec.ar_invoice_line_number;
3970 IF l_currency_code <> l_inv_curr_code THEN
3971 IF l_inv_to_rct_rate is null THEN
3972 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
3973 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
3974 IF l_exchange_rate_type IS NULL THEN
3975 OKL_API.set_message( p_app_name => G_APP_NAME
3976 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
3977 );
3978 RAISE G_EXCEPTION_HALT_VALIDATION;
3979 ELSE
3980 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
3981 ,l_currency_code
3982 ,l_receipt_date
3983 ,l_exchange_rate_type
3984 );
3985
3986 IF l_conversion_rate IN (0,-1) THEN
3987
3988 -- Message Text: No exchange rate defined
3989 x_return_status := okl_api.G_RET_STS_ERROR;
3990 okl_api.set_message( p_app_name => G_APP_NAME,
3991 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
3992 RAISE G_EXCEPTION_HALT_VALIDATION;
3993 END IF;
3994 END IF;
3995 ELSE
3996 l_conversion_rate := l_inv_to_rct_rate;
3997 END IF;
3998 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
3999 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
4000 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
4001 ELSE
4002 l_rcpt_tbl(j).trans_to_receipt_rate := null;
4003 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
4004 END IF;
4005 l_pro_rate_inv_total := l_pro_rate_inv_total + l_rcpt_tbl(j).AMOUNT_APPLIED;
4006
4007 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4008 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
4009 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
4010 END IF;
4011 END IF;
4012 END LOOP; -- c_open_invs
4013 END LOOP; -- c_stream_alloc
4014
4015 -- Calc Pro Ration
4016 -- only if total amount of prorated invoices is greater than receipt
4017
4018 IF l_pro_rate_inv_total IS NULL OR l_pro_rate_inv_total = 0 THEN
4019
4020 -- Message Text: No prorated transaction types
4021 x_return_status := OKC_API.G_RET_STS_ERROR;
4022 OKC_API.set_message( p_app_name => G_APP_NAME,
4023 p_msg_name => 'OKL_BPD_NO_PRORATED_STRMS');
4024
4025 RAISE G_EXCEPTION_HALT_VALIDATION;
4026
4027 END IF;
4028
4029 IF (l_pro_rate_inv_total > l_amount_app_to) THEN
4030
4031 j := l_first_prorate_rec_j;
4032
4033 l_temp_val := l_amount_app_to / l_pro_rate_inv_total;
4034
4035 LOOP
4036 l_rcpt_tbl(j).AMOUNT_APPLIED := l_temp_val * l_rcpt_tbl(j).AMOUNT_APPLIED;
4037 l_rcpt_tbl(j).AMOUNT_APPLIED:=GET_ROUNDED_AMOUNT(l_rcpt_tbl(j).AMOUNT_APPLIED,l_inv_curr_Code);
4038 IF l_currency_code <> l_inv_curr_code THEN
4039 IF l_inv_to_rct_rate is null THEN
4040 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
4041 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
4042 IF l_exchange_rate_type IS NULL THEN
4043 OKL_API.set_message( p_app_name => G_APP_NAME
4044 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
4045 );
4046 RAISE G_EXCEPTION_HALT_VALIDATION;
4047 ELSE
4048 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
4049 ,l_currency_code
4050 ,l_receipt_date
4051 ,l_exchange_rate_type
4052 );
4053
4054 IF l_conversion_rate IN (0,-1) THEN
4055
4056 -- Message Text: No exchange rate defined
4057 x_return_status := okl_api.G_RET_STS_ERROR;
4058 okl_api.set_message( p_app_name => G_APP_NAME,
4059 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
4060 RAISE G_EXCEPTION_HALT_VALIDATION;
4061 END IF;
4062 END IF;
4063 ELSE
4064 l_conversion_rate := l_inv_to_rct_rate;
4065 END IF;
4066 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
4067 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
4068 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
4069 ELSE
4070 l_rcpt_tbl(j).trans_to_receipt_rate := null;
4071 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
4072 END IF;
4073 l_inv_total_amt := l_inv_total_amt + l_rcpt_tbl(j).AMOUNT_APPLIED;
4074 EXIT WHEN (j = l_rcpt_tbl.LAST);
4075 j := j + 1;
4076 END LOOP;
4077 l_diff_amount := l_amount_app_to - l_inv_total_amt;
4078 if l_diff_amount > 0 then
4079 l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied := l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied + l_diff_amount;
4080 end if;
4081 END IF;
4082
4083 END IF; -- (3)
4084
4085 ELSE -- EXACT or OVERPAYMENT or TOLERANCE (2)
4086 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4087 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'EXACT or OVERPAYMENT or TOLERANCE');
4088
4089
4090 END IF;
4091 -- CREATE LINES TABLE
4092 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
4093 LOOP
4094 c_open_invs_rec := open_inv_contract_tbl(i);
4095 EXIT WHEN l_amount_app_to = 0 OR l_amount_app_to IS NULL;
4096
4097 j := j + 1;
4098 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invs_rec.invoice_due_remaining;
4099 l_rcpt_tbl(j).INVOICE_ID := c_open_invs_rec.ar_invoice_id;
4100 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invs_rec.ar_invoice_number;
4101 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
4102 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invs_rec.ar_invoice_line_id;
4103 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invs_rec.ar_invoice_line_number;
4104
4105 IF l_amount_app_to < l_rcpt_tbl(j).AMOUNT_APPLIED THEN
4106 -- TOLERANCE
4107 --l_xcav_tbl(i).AMOUNT_APPLIED := l_amount_app_to;
4108 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
4109
4110 l_amount_app_to := 0;
4111
4112 ELSE
4113
4114 l_amount_app_to := l_amount_app_to - l_rcpt_tbl(j).AMOUNT_APPLIED;
4115
4116 END IF;
4117 IF l_currency_code <> l_inv_curr_code THEN
4118 IF l_inv_to_rct_rate is null THEN
4119 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
4120 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
4121 IF l_exchange_rate_type IS NULL THEN
4122 OKL_API.set_message( p_app_name => G_APP_NAME
4123 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
4124 );
4125 RAISE G_EXCEPTION_HALT_VALIDATION;
4126 ELSE
4127 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
4128 ,l_currency_code
4129 ,l_receipt_date
4130 ,l_exchange_rate_type
4131 );
4132
4133 IF l_conversion_rate IN (0,-1) THEN
4134
4135 -- Message Text: No exchange rate defined
4136 x_return_status := okl_api.G_RET_STS_ERROR;
4137 okl_api.set_message( p_app_name => G_APP_NAME,
4138 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
4139 RAISE G_EXCEPTION_HALT_VALIDATION;
4140 END IF;
4141 END IF;
4142 ELSE
4143 l_conversion_rate := l_inv_to_rct_rate;
4144 END IF;
4145 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
4146 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
4147 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
4148 ELSE
4149 l_rcpt_tbl(j).trans_to_receipt_rate := null;
4150 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
4151 END IF;
4152 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4153 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
4154 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
4155
4156 END IF;
4157 END LOOP;
4158 -- Apply the remaining balance as per the Cash Application Rule
4159 If l_over_payment In ('B','b') Then -- Onaccount --move cash to customer balances -OVP
4160 IF l_currency_code = l_inv_curr_code THEN
4161 l_onacc_amount:=l_amount_app_to;
4162 ELSE
4163 l_onacc_amount:= l_amount_app_to * l_conversion_rate;
4164 l_onacc_amount := GET_ROUNDED_AMOUNT(l_onacc_amount,l_currency_code);
4165 END IF;
4166 Elsif l_over_payment In ('F','f') Then --Unapply -- move cash to unapplied -OVP
4167 IF l_currency_code = l_inv_curr_code THEN
4168 l_unapply_amount:=l_amount_app_to;
4169 ELSE
4170 l_unapply_amount:= l_amount_app_to * l_conversion_rate;
4171 l_unapply_amount := GET_ROUNDED_AMOUNT(l_unapply_amount,l_currency_code);
4172 END IF;
4173 End If;
4174
4175 END IF; -- under payment.
4176
4177 END IF; -- VR 07-Oct-2005 GE-20 Receipts - On Account CAR
4178
4179 -- Contract level cash application processing ends.
4180 -- *************************************************
4181
4182 END IF;
4183
4184 ELSE -- added by bv
4185
4186 NULL; -- added by bv
4187
4188 END IF; -- added by bv
4189
4190 END LOOP;
4191 CLOSE c_get_contract_num;
4192
4193 END IF; -- l_same_date/l_same_cash_rule
4194
4195 END IF;
4196
4197
4198
4199 -- END OKL CASH APPLICATION.
4200
4201
4202 --Get grouped application table (in the form of invoice header > multiple invoice lines table)
4203 GET_APPLICATIONS( p_rcpt_tbl => l_rcpt_tbl
4204 ,x_appl_tbl => l_appl_tbl);
4205 x_appl_tbl := l_appl_tbl;
4206
4207 l_tot_amt_app_from := 0;
4208 IF x_appl_tbl.COUNT > 0 THEN
4209 IF x_appl_tbl(x_appl_tbl.FIRST).inv_hdr_rec.trans_to_receipt_rate IS NOT NULL THEN
4210 FOR ll IN x_appl_tbl.FIRST..x_appl_tbl.LAST LOOP
4211 l_tot_amt_app_from := l_tot_amt_app_from + nvl(x_appl_tbl(ll).inv_hdr_rec.amount_applied_from,0);
4212 END LOOP;
4213 IF (l_onacc_amount + l_unapply_amount + l_tot_amt_app_from ) <> l_amount_app_from THEN
4214 l_diff_amount := l_amount_app_from - (l_onacc_amount + l_unapply_amount + l_tot_amt_app_from );
4215 log_debug('l_diff_amount = '||l_diff_amount);
4216 IF nvl(l_onacc_amount,0) <> 0 THEN
4217 l_onacc_amount := l_onacc_amount + l_diff_amount;
4218 log_debug('l_onacc_amount = '||l_onacc_amount);
4219 ELSIF nvl(l_unapply_amount,0) <> 0 THEN
4220 l_unapply_amount := l_unapply_amount + l_diff_amount;
4221 END IF;
4222 END IF;
4223 END IF;
4224 END IF;
4225 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4226 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done execution of OKL_AUTO_CASH_APPL_RULES_pvt.auto_cash_app_for_consinv ...');
4227
4228
4229 END IF;
4230 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4231
4232 x_onacc_amount:= l_onacc_amount ;
4233 x_unapply_amount:= l_unapply_amount;
4234
4235 EXCEPTION
4236
4237 WHEN OTHERS THEN
4238 x_appl_tbl.DELETE;
4239 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
4240 x_msg_count := l_msg_count;
4241 x_msg_data := l_msg_data;
4242
4243 END auto_cashapp_for_consinv;
4244
4245 FUNCTION GET_ROUNDED_AMOUNT( p_amount_to_round IN NUMBER
4246 ,p_currency_code IN VARCHAR2)
4247 RETURN NUMBER
4248 AS
4249 BEGIN
4250 -- RETURN( arpcurr.CurrRound( p_amount_to_round, p_currency_code ) );
4251 RETURN okl_accounting_util.round_amount(p_amount_to_round,p_currency_code);
4252 EXCEPTION
4253 WHEN OTHERS THEN
4254 RETURN 0;
4255 END GET_ROUNDED_AMOUNT;
4256
4257 END OKL_AUTO_CASH_APPL_RULES_PVT;