[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.3 2008/08/29 08:12:50 nikshah 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 IF l_dflt_tolerance IS NULL OR l_dflt_under_payment IS NULL
1808 OR l_dflt_over_payment IS NULL OR l_dflt_receipt_msmtch IS NULL THEN
1809 RAISE OKL_API.G_EXCEPTION_ERROR;
1810 END IF;
1811
1812 -- START OKL CASH APPLICATION.
1813 IF l_contract_num IS NOT NULL THEN
1814 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1815 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inside if block of l_contract_num is not null ');
1816 END IF;
1817
1818 IF l_contract_id IS NULL THEN
1819 OPEN c_get_contract_id(l_contract_num);
1820 FETCH c_get_contract_id INTO l_contract_id,l_inv_curr_Code;
1821 CLOSE c_get_contract_id;
1822 END IF;
1823
1824 --get cash application rule
1825 OPEN c_cash_rle_id_csr (l_contract_id);
1826 FETCH c_cash_rle_id_csr INTO l_cau_id;
1827 CLOSE c_cash_rle_id_csr;
1828
1829 IF l_cau_id IS NOT NULL THEN
1830 OPEN c_cash_rule_csr (l_cau_id);
1831 FETCH c_cash_rule_csr INTO l_cat_id
1832 ,l_tolerance
1833 ,l_days_past_quote_valid
1834 ,l_months_to_bill_ahead
1835 ,l_under_payment
1836 ,l_over_payment
1837 ,l_receipt_msmtch;
1838 CLOSE c_cash_rule_csr;
1839
1840 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1841 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_tolerance : '||l_tolerance);
1842 END IF;
1843
1844 IF l_tolerance IS NULL THEN
1845 l_cat_id := l_dflt_cat_id;
1846 l_tolerance := l_dflt_tolerance;
1847 l_days_past_quote_valid := l_dflt_days_past_quote_valid;
1848 l_months_to_bill_ahead := l_dflt_months_to_bill_ahead;
1849 l_under_payment := l_dflt_under_payment;
1850 l_over_payment := l_dflt_over_payment;
1851 l_receipt_msmtch := l_dflt_receipt_msmtch;
1852 END IF;
1853 ELSE -- use default rule
1854
1855 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1856 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Using default CAR since l_cau_id is NULL');
1857 END IF;
1858 l_cat_id := l_dflt_cat_id;
1859 l_tolerance := l_dflt_tolerance;
1860 l_days_past_quote_valid := l_dflt_days_past_quote_valid;
1861 l_months_to_bill_ahead := l_dflt_months_to_bill_ahead;
1862 l_under_payment := l_dflt_under_payment;
1863 l_over_payment := l_dflt_over_payment;
1864 l_receipt_msmtch := l_dflt_receipt_msmtch;
1865 END IF;
1866
1867 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1868 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Opening cursor c_open_invoices_contract ('||l_contract_num||', '||l_customer_num||', NULL)..');
1869 END IF;
1870
1871 IF NVL(l_cau_id, 0) = -1 THEN -- VR 07-Oct-2005 GE-20 Receipts - On Account CAR
1872 -- Receipt needs to be left as unapplied
1873 j := 1;
1874 l_rcpt_tbl(j).INVOICE_ID := NULL;
1875 l_rcpt_tbl(j).INVOICE_LINE_ID := NULL;
1876 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := NULL;
1877 l_rcpt_tbl(j).INVOICE_NUMBER := NULL;
1878 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
1879 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
1880 ELSE
1881
1882 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
1883 LOOP
1884 --l_invoice_currency_code := c_open_invoices_contract_rec.currency_code;
1885 l_inv_tot := l_inv_tot + open_inv_contract_tbl(i).amount_due_remaining; -- changed from remaining to original
1886 END LOOP;
1887 -- TOLERANCE CHECK
1888
1889 IF l_inv_tot > l_amount_app_to THEN
1890 l_appl_tolerance := l_inv_tot * (1 - l_tolerance / 100);
1891 ELSE
1892 l_appl_tolerance := l_inv_tot;
1893 END IF;
1894
1895 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1896 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_inv_tot : ' || l_inv_tot);
1897 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_amount_app_to : ' || l_amount_app_to);
1898 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_appl_tolerance : ' || l_appl_tolerance);
1899 END IF;
1900
1901 IF l_inv_tot > l_amount_app_to AND l_appl_tolerance > l_amount_app_to THEN -- UNDERPAYMENT (2)
1902 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1903 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'auto_cashapp_for_contract > UNDERPAYMENT ...');
1904 END IF;
1905
1906 IF l_under_payment In ('U','u') THEN -- Unapply underpayment (3)
1907 IF l_currency_code = l_inv_curr_code THEN
1908 l_unapply_amount:=l_amount_app_to;
1909 ELSE
1910 l_unapply_amount:= l_amount_app_to * l_conversion_rate;
1911 l_unapply_amount:=GET_ROUNDED_AMOUNT(l_unapply_amount,l_currency_code);
1912 END IF;
1913 ELSIF l_under_payment IN ('T','t') THEN -- ORDERED (3)
1914 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1915 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ORDERED ...');
1916 END IF;
1917
1918 j := 0;
1919 OPEN c_stream_alloc (l_ordered, l_cat_id);
1920 LOOP
1921 FETCH c_stream_alloc INTO l_sty_id;
1922 EXIT WHEN c_stream_alloc%NOTFOUND OR l_amount_app_to = 0 OR l_amount_app_to IS NULL;
1923
1924 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
1925 LOOP
1926 c_open_invoices_contract_rec := open_inv_contract_tbl(i);
1927 IF c_open_invoices_contract_rec.sty_id = l_sty_id THEN
1928 EXIT WHEN l_amount_app_to = 0 OR l_amount_app_to IS NULL;
1929 j := j + 1;
1930 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invoices_contract_rec.amount_due_remaining;
1931 IF l_rcpt_tbl(j).AMOUNT_APPLIED > l_amount_app_to THEN
1932 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
1933 l_amount_app_to := 0;
1934 ELSE
1935 l_amount_app_to := l_amount_app_to - l_rcpt_tbl(j).AMOUNT_APPLIED;
1936 END IF;
1937 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invoices_contract_rec.ar_invoice_number;
1938 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
1939 l_rcpt_tbl(j).INVOICE_ID := c_open_invoices_contract_rec.ar_invoice_id;
1940 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invoices_contract_rec.invoice_line_id;
1941 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invoices_contract_rec.line_number;
1942 IF l_currency_code <> l_inv_curr_code THEN
1943 IF l_inv_to_rct_rate is null THEN
1944 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
1945 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
1946 IF l_exchange_rate_type IS NULL THEN
1947 OKL_API.set_message( p_app_name => G_APP_NAME
1948 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
1949 );
1950 RAISE G_EXCEPTION_HALT_VALIDATION;
1951 ELSE
1952 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
1953 ,l_currency_code
1954 ,l_receipt_date
1955 ,l_exchange_rate_type
1956 );
1957
1958 IF l_conversion_rate IN (0,-1) THEN
1959
1960 -- Message Text: No exchange rate defined
1961 x_return_status := okl_api.G_RET_STS_ERROR;
1962 okl_api.set_message( p_app_name => G_APP_NAME,
1963 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
1964 RAISE G_EXCEPTION_HALT_VALIDATION;
1965 END IF;
1966 END IF;
1967 ELSE
1968 l_conversion_rate := l_inv_to_rct_rate;
1969 END IF;
1970 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
1971 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
1972 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
1973 ELSE
1974 l_rcpt_tbl(j).trans_to_receipt_rate := null;
1975 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
1976 END IF;
1977 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1978 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_ID : '||l_rcpt_tbl(j).INVOICE_ID);
1979 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
1980 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
1981 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);
1982 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);
1983 END IF;
1984 END IF;
1985 END LOOP;
1986 END LOOP;
1987 CLOSE c_stream_alloc;
1988
1989 ELSIF l_under_payment IN ('P','p') THEN -- PRO RATE (3)
1990 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1991 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'PRO RATE');
1992 END IF;
1993
1994 j := 0;
1995 -- obtain all the streams that are part of the pro rate user defined list.
1996
1997 FOR c_stream_alloc_rec IN c_stream_alloc (l_prorate, l_cat_id)
1998 LOOP
1999 l_sty_id := c_stream_alloc_rec.sty_id;
2000 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
2001 LOOP
2002 c_open_invoices_contract_rec := open_inv_contract_tbl(i);
2003 IF c_open_invoices_contract_rec.sty_id = l_sty_id THEN
2004 j := j + 1;
2005 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invoices_contract_rec.ar_invoice_number;
2006 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
2007 l_rcpt_tbl(j).INVOICE_ID := c_open_invoices_contract_rec.ar_invoice_id;
2008 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invoices_contract_rec.invoice_line_id;
2009 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invoices_contract_rec.line_number;
2010 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invoices_contract_rec.amount_due_remaining;
2011 l_pro_rate_inv_total := l_pro_rate_inv_total + l_rcpt_tbl(j).AMOUNT_APPLIED;
2012
2013 IF l_currency_code <> l_inv_curr_code THEN
2014 IF l_inv_to_rct_rate is null THEN
2015 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
2016 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
2017 IF l_exchange_rate_type IS NULL THEN
2018 OKL_API.set_message( p_app_name => G_APP_NAME
2019 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
2020 );
2021 RAISE G_EXCEPTION_HALT_VALIDATION;
2022 ELSE
2023 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
2024 ,l_currency_code
2025 ,l_receipt_date
2026 ,l_exchange_rate_type
2027 );
2028
2029 IF l_conversion_rate IN (0,-1) THEN
2030
2031 -- Message Text: No exchange rate defined
2032 x_return_status := okl_api.G_RET_STS_ERROR;
2033 okl_api.set_message( p_app_name => G_APP_NAME,
2034 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
2035 RAISE G_EXCEPTION_HALT_VALIDATION;
2036 END IF;
2037 END IF;
2038 ELSE
2039 l_conversion_rate := l_inv_to_rct_rate;
2040 END IF;
2041 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
2042 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
2043 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
2044 ELSE
2045 l_rcpt_tbl(j).trans_to_receipt_rate := null;
2046 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
2047 END IF;
2048 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2049 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_ID : '||l_rcpt_tbl(j).INVOICE_ID);
2050 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
2051 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
2052 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);
2053 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);
2054 END IF;
2055 END IF;
2056 END LOOP; -- c_open_invs
2057 END LOOP; -- c_stream_alloc
2058
2059 -- Calc Pro Ration
2060 -- only if total amount of prorated invoices is greater than receipt
2061 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2062 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_pro_rate_inv_total: '||l_pro_rate_inv_total);
2063 END IF;
2064 IF l_pro_rate_inv_total IS NULL OR l_pro_rate_inv_total = 0 THEN
2065 -- Message Text: No prorated transaction types
2066 --x_return_status := OKC_API.G_RET_STS_ERROR;
2067 --OKC_API.set_message( p_app_name => G_APP_NAME,
2068 -- p_msg_name => 'OKL_BPD_NO_PRORATED_STRMS');
2069 --RAISE OKL_API.G_EXCEPTION_ERROR;
2070 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Pro-rate invoice total is zero or null');
2071 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2072 x_appl_tbl.delete;
2073 RETURN;
2074 END IF;
2075
2076 IF (l_pro_rate_inv_total > l_amount_app_to) THEN
2077 j := l_rcpt_tbl.FIRST;
2078 l_temp_val := l_amount_app_to / l_pro_rate_inv_total;
2079 LOOP
2080 l_rcpt_tbl(j).AMOUNT_APPLIED := l_temp_val * l_rcpt_tbl(j).AMOUNT_APPLIED;
2081 l_rcpt_tbl(j).AMOUNT_APPLIED:=GET_ROUNDED_AMOUNT(l_rcpt_tbl(j).AMOUNT_APPLIED,l_inv_curr_Code);
2082 l_inv_total_amt := l_inv_total_amt + l_rcpt_tbl(j).AMOUNT_APPLIED;
2083 IF l_currency_code <> l_inv_curr_code THEN
2084 IF l_inv_to_rct_rate is null THEN
2085 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
2086 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
2087 IF l_exchange_rate_type IS NULL THEN
2088 OKL_API.set_message( p_app_name => G_APP_NAME
2089 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
2090 );
2091 RAISE G_EXCEPTION_HALT_VALIDATION;
2092 ELSE
2093 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
2094 ,l_currency_code
2095 ,l_receipt_date
2096 ,l_exchange_rate_type
2097 );
2098
2099 IF l_conversion_rate IN (0,-1) THEN
2100
2101 -- Message Text: No exchange rate defined
2102 x_return_status := okl_api.G_RET_STS_ERROR;
2103 okl_api.set_message( p_app_name => G_APP_NAME,
2104 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
2105 RAISE G_EXCEPTION_HALT_VALIDATION;
2106 END IF;
2107 END IF;
2108 ELSE
2109 l_conversion_rate := l_inv_to_rct_rate;
2110 END IF;
2111 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
2112 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
2113 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
2114
2115 ELSE
2116 l_rcpt_tbl(j).trans_to_receipt_rate := null;
2117 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
2118 END IF;
2119 EXIT WHEN (j = l_rcpt_tbl.LAST);
2120 j := j + 1;
2121 END LOOP;
2122 l_diff_amount := l_amount_app_to - l_inv_total_amt;
2123 if l_diff_amount > 0 then
2124 l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied := l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied + l_diff_amount;
2125 IF l_currency_code <> l_inv_curr_code THEN
2126 IF l_inv_to_rct_rate is null THEN
2127 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
2128 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
2129 IF l_exchange_rate_type IS NULL THEN
2130 OKL_API.set_message( p_app_name => G_APP_NAME
2131 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
2132 );
2133 RAISE G_EXCEPTION_HALT_VALIDATION;
2134 ELSE
2135 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
2136 ,l_currency_code
2137 ,l_receipt_date
2138 ,l_exchange_rate_type
2139 );
2140
2141 IF l_conversion_rate IN (0,-1) THEN
2142
2143 -- Message Text: No exchange rate defined
2144 x_return_status := okl_api.G_RET_STS_ERROR;
2145 okl_api.set_message( p_app_name => G_APP_NAME,
2146 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
2147 RAISE G_EXCEPTION_HALT_VALIDATION;
2148 END IF;
2149 END IF;
2150 ELSE
2151 l_conversion_rate := l_inv_to_rct_rate;
2152 END IF;
2153 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
2154 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
2155 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
2156 ELSE
2157 l_rcpt_tbl(j).trans_to_receipt_rate := null;
2158 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
2159 END IF;
2160 end if;
2161 END IF;
2162 END IF; -- (3)
2163 ELSE -- EXACT or OVERPAYMENT or TOLERANCE (2)
2164 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2165 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'auto_cashapp_for_contract > EXACT or OVERPAYMENT or TOLERANCE');
2166 END IF;
2167
2168 j := 0;
2169
2170 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
2171 LOOP
2172 EXIT WHEN l_amount_app_to = 0 OR l_amount_app_to IS NULL;
2173 c_open_invoices_contract_rec := open_inv_contract_tbl(i);
2174 j := j + 1;
2175
2176 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invoices_contract_rec.ar_invoice_number;
2177 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
2178 l_rcpt_tbl(j).INVOICE_ID := c_open_invoices_contract_rec.ar_invoice_id;
2179 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invoices_contract_rec.invoice_line_id;
2180 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invoices_contract_rec.line_number;
2181 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invoices_contract_rec.amount_due_remaining;
2182
2183 IF l_amount_app_to < l_rcpt_tbl(j).AMOUNT_APPLIED THEN
2184 -- TOLERANCE
2185 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
2186 l_amount_app_to := 0;
2187 ELSE
2188 l_amount_app_to := l_amount_app_to - l_rcpt_tbl(j).AMOUNT_APPLIED;
2189 END IF;
2190 IF l_currency_code <> l_inv_curr_code THEN
2191 IF l_inv_to_rct_rate is null THEN
2192 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
2193 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
2194 IF l_exchange_rate_type IS NULL THEN
2195 OKL_API.set_message( p_app_name => G_APP_NAME
2196 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
2197 );
2198 RAISE G_EXCEPTION_HALT_VALIDATION;
2199 ELSE
2200 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
2201 ,l_currency_code
2202 ,l_receipt_date
2203 ,l_exchange_rate_type
2204 );
2205
2206 IF l_conversion_rate IN (0,-1) THEN
2207
2208 -- Message Text: No exchange rate defined
2209 x_return_status := okl_api.G_RET_STS_ERROR;
2210 okl_api.set_message( p_app_name => G_APP_NAME,
2211 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
2212 RAISE G_EXCEPTION_HALT_VALIDATION;
2213 END IF;
2214 END IF;
2215 ELSE
2216 l_conversion_rate := l_inv_to_rct_rate;
2217 END IF;
2218 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
2219 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
2220 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
2221 ELSE
2222 l_rcpt_tbl(j).trans_to_receipt_rate := null;
2223 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
2224 END IF;
2225 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2226 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_ID : '||l_rcpt_tbl(j).INVOICE_ID);
2227 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
2228 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
2229 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);
2230 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);
2231 END IF;
2232 END LOOP;
2233
2234 -- Apply the remaining balance as per the Cash Application Rule
2235 If l_over_payment In ('B','b') Then -- Onaccount --move cash to customer balances -OVP
2236 IF l_currency_code = l_inv_curr_code THEN
2237 l_onacc_amount:=l_amount_app_to;
2238 ELSE
2239 l_onacc_amount:= l_amount_app_to * l_conversion_rate;
2240 l_onacc_amount:=GET_ROUNDED_AMOUNT(l_onacc_amount,l_currency_code);
2241 END IF;
2242 Elsif l_over_payment In ('F','f') Then --Unapply -- move cash to unapplied -OVP
2243 IF l_currency_code = l_inv_curr_code THEN
2244 l_unapply_amount:=l_amount_app_to;
2245 ELSE
2246 l_unapply_amount:= l_amount_app_to * l_conversion_rate;
2247 l_unapply_amount:=GET_ROUNDED_AMOUNT(l_unapply_amount,l_currency_code);
2248 END IF;
2249 End If;
2250
2251 END IF; -- under payment.;
2252 END IF; -- VR 07-Oct-2005 GE-20 Receipts - On Account CAR
2253
2254 --Get grouped application table (in the form of invoice header > multiple invoice lines table)
2255 GET_APPLICATIONS( p_rcpt_tbl => l_rcpt_tbl
2256 ,x_appl_tbl => l_appl_tbl);
2257 x_appl_tbl := l_appl_tbl;
2258 END IF;
2259
2260 l_tot_amt_app_from := 0;
2261 IF x_appl_tbl.COUNT > 0 THEN
2262 IF x_appl_tbl(x_appl_tbl.FIRST).inv_hdr_rec.trans_to_receipt_rate IS NOT NULL THEN
2263 FOR ll IN x_appl_tbl.FIRST..x_appl_tbl.LAST LOOP
2264 l_tot_amt_app_from := l_tot_amt_app_from + nvl(x_appl_tbl(ll).inv_hdr_rec.amount_applied_from,0);
2265 END LOOP;
2266 IF (l_onacc_amount + l_unapply_amount + l_tot_amt_app_from ) <> l_amount_app_from THEN
2267 l_diff_amount := l_amount_app_from - (l_onacc_amount + l_unapply_amount + l_tot_amt_app_from );
2268 log_debug('l_diff_amount = '||l_diff_amount);
2269 IF nvl(l_onacc_amount,0) <> 0 THEN
2270 l_onacc_amount := l_onacc_amount + l_diff_amount;
2271 log_debug('l_onacc_amount = '||l_onacc_amount);
2272 ELSIF nvl(l_unapply_amount,0) <> 0 THEN
2273 l_unapply_amount := l_unapply_amount + l_diff_amount;
2274 END IF;
2275 END IF;
2276 END IF;
2277 END IF;
2278
2279 x_onacc_amount :=l_onacc_amount;
2280 x_unapply_amount :=l_unapply_amount;
2281
2282 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2283 EXCEPTION
2284 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2285 x_appl_tbl.DELETE;
2286 x_return_status := OKL_API.G_RET_STS_ERROR;
2287 x_appl_tbl.delete;
2288 WHEN OTHERS THEN
2289 x_appl_tbl.DELETE;
2290 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2291 x_msg_count := l_msg_count;
2292 x_msg_data := sqlerrm;
2293 END auto_cashapp_for_contract;
2294
2295
2296
2297 --Receipt mismatch which will identify all the invoice lines
2298 --for the given customer based on CAR setup i.e. Newest invoices or Oldest invoices
2299 PROCEDURE receipt_mismatch(p_api_version IN NUMBER
2300 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
2301 ,x_return_status OUT NOCOPY VARCHAR2
2302 ,x_msg_count OUT NOCOPY NUMBER
2303 ,x_msg_data OUT NOCOPY VARCHAR2
2304 ,p_customer_num IN VARCHAR2 DEFAULT NULL
2305 ,p_currency_code IN VARCHAR2
2306 ,p_rcpt_amount IN NUMBER
2307 ,p_org_id IN NUMBER
2308 ,p_receipt_date IN DATE
2309 ,x_appl_tbl OUT NOCOPY okl_appl_dtls_tbl_type
2310 ,x_onacc_amount OUT NOCOPY NUMBER
2311 ) IS
2312
2313 --Variables declaration
2314 l_dflt_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
2315 l_dflt_tolerance OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE DEFAULT NULL;
2316 l_dflt_days_past_quote_valid OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE DEFAULT NULL;
2317 l_dflt_months_to_bill_ahead OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE DEFAULT NULL;
2318 l_dflt_under_payment OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
2319 l_dflt_over_payment OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
2320 l_dflt_receipt_msmtch OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE DEFAULT NULL;
2321
2322 l_customer_num HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE := p_customer_num;
2323 l_currency_code VARCHAR2(45) := p_currency_code;
2324 l_rcpt_amount NUMBER := p_rcpt_amount;
2325 l_org_id NUMBER := p_org_id;
2326 j NUMBER;
2327
2328 l_msg_count NUMBER;
2329 l_msg_data VARCHAR2(2000);
2330
2331 --Record/Table Definitions
2332 l_rcpt_tbl okl_rcpt_dtls_tbl_type;
2333 l_appl_tbl okl_appl_dtls_tbl_type;
2334
2335 --Cursor definitions
2336
2337 CURSOR c_all_open_invs (cp_customer_num IN VARCHAR2, cp_org_id IN NUMBER,cp_curr_code IN VARCHAR2) IS
2338 SELECT AR_INVOICE_ID,
2339 AR_INVOICE_NUMBER,
2340 INVOICE_LINE_ID,
2341 LINE_NUMBER,
2342 AMOUNT_DUE_REMAINING,
2343 INVOICE_DUE_DATE
2344 FROM OKL_RCPT_CUST_CONT_BALANCES_UV
2345 WHERE CUSTOMER_ACCOUNT_NUMBER = cp_customer_num
2346 AND ORG_ID = cp_org_id
2347 AND STATUS = 'OP'
2348 AND CURRENCY_CODE = cp_curr_code
2349 -- AND AMOUNT_DUE_REMAINING > 0
2350 ORDER BY INVOICE_DUE_DATE;
2351
2352 c_all_open_invs_rec c_all_open_invs%ROWTYPE;
2353
2354
2355 CURSOR c_all_open_invs_desc (cp_customer_num IN VARCHAR2, cp_org_id IN NUMBER,cp_curr_code IN VARCHAR2) IS
2356 SELECT AR_INVOICE_ID,
2357 AR_INVOICE_NUMBER,
2358 INVOICE_LINE_ID,
2359 LINE_NUMBER,
2360 AMOUNT_DUE_REMAINING,
2361 INVOICE_DUE_DATE
2362 FROM OKL_RCPT_CUST_CONT_BALANCES_UV
2363 WHERE CUSTOMER_ACCOUNT_NUMBER = cp_customer_num
2364 AND ORG_ID = cp_org_id
2365 AND STATUS = 'OP'
2366 -- AND AMOUNT_DUE_REMAINING > 0
2367 AND CURRENCY_CODE = cp_curr_code
2368 ORDER BY INVOICE_DUE_DATE DESC;
2369
2370 c_all_open_invs_desc_rec c_all_open_invs_desc%ROWTYPE;
2371
2372 l_onacc_amount Number:=0;
2373
2374 BEGIN
2375 IF (G_DEBUG_ENABLED = 'Y') THEN
2376 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2377 END IF;
2378 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2379 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Starting execution of OKL_AUTO_CASH_APPL_RULES_PVT.receipt_mismatch ...');
2380 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Procedure parameters, p_customer_num : '|| p_customer_num);
2381 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_org_id : '|| p_org_id);
2382 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_currency_code : '|| p_currency_code);
2383 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_rcpt_amount : '|| p_rcpt_amount);
2384 END IF;
2385
2386 --get default cash application rules.
2387 get_default_cash_app_rule( p_org_id => l_org_id
2388 ,x_dflt_cat_id => l_dflt_cat_id
2389 ,x_dflt_tolerance => l_dflt_tolerance
2390 ,x_dflt_days_past_quote_valid => l_dflt_days_past_quote_valid
2391 ,x_dflt_months_to_bill_ahead => l_dflt_months_to_bill_ahead
2392 ,x_dflt_under_payment => l_dflt_under_payment
2393 ,x_dflt_over_payment => l_dflt_over_payment
2394 ,x_dflt_receipt_msmtch => l_dflt_receipt_msmtch);
2395
2396 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2397 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Default Tolerance: ' || l_dflt_tolerance);
2398 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Default Under Payment: ' || l_dflt_under_payment);
2399 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Default Over Payment: ' || l_dflt_over_payment);
2400 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Default Receipt Mismatch: ' || l_dflt_receipt_msmtch);
2401 END IF;
2402
2403 --If default cash application rule is not defined then
2404 --raise an exception
2405 IF l_dflt_tolerance IS NULL OR l_dflt_under_payment IS NULL
2406 OR l_dflt_over_payment IS NULL OR l_dflt_receipt_msmtch IS NULL THEN
2407 RAISE OKL_API.G_EXCEPTION_ERROR;
2408 END IF;
2409
2410 -- START OKL CASH APPLICATION.
2411
2412 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2413 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Checking whether l_customer_num is not null ..');
2414 END IF;
2415
2416 --If customer is not null then find open invoices for this customer
2417 IF l_customer_num IS NOT NULL THEN
2418 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2419 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inside branch for l_customer_num IS NOT NULL');
2420 END IF;
2421 j := 0;
2422
2423 IF l_dflt_receipt_msmtch In ('A' ,'a') THEN -- Apply 'on-account'
2424 l_onacc_amount :=l_rcpt_amount;
2425 ELSIF l_dflt_receipt_msmtch = 'O' or -- APPLY TO CUSTOMER'S OLDEST INVOICES FIRST
2426 l_dflt_receipt_msmtch = 'o' THEN
2427 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2428 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Apply to customers oldest invoices first');
2429 END IF;
2430 OPEN c_all_open_invs (l_customer_num, l_org_id,l_currency_code);
2431 LOOP
2432 FETCH c_all_open_invs INTO c_all_open_invs_rec;
2433 EXIT WHEN c_all_open_invs%NOTFOUND OR l_rcpt_amount = 0 OR l_rcpt_amount IS NULL;
2434 IF c_all_open_invs_rec.AMOUNT_DUE_REMAINING > 0 THEN
2435 j := j + 1;
2436
2437 --Populate receipt table
2438 l_rcpt_tbl(j).INVOICE_ID := c_all_open_invs_rec.ar_invoice_id;
2439 l_rcpt_tbl(j).INVOICE_NUMBER := c_all_open_invs_rec.ar_invoice_number;
2440 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_currency_code;
2441 l_rcpt_tbl(j).INVOICE_LINE_ID := c_all_open_invs_rec.invoice_line_id;
2442 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_all_open_invs_rec.line_number;
2443
2444 --Amount Applied will be total amount applied including line and tax
2445 l_rcpt_tbl(j).AMOUNT_APPLIED := c_all_open_invs_rec.amount_due_remaining;
2446
2447 IF l_rcpt_amount < l_rcpt_tbl(j).AMOUNT_APPLIED THEN
2448 l_rcpt_tbl(j).AMOUNT_APPLIED := l_rcpt_amount;
2449 l_rcpt_amount := 0;
2450 ELSE
2451 l_rcpt_amount := l_rcpt_amount - l_rcpt_tbl(j).AMOUNT_APPLIED;
2452 END IF;
2453
2454 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2455 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_ID : '||l_rcpt_tbl(j).INVOICE_ID);
2456 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
2457 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);
2458 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);
2459 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
2460 END IF;
2461 END IF;
2462 END LOOP;
2463 CLOSE c_all_open_invs;
2464
2465 ELSIF l_dflt_receipt_msmtch ='N' or -- APPLY TO CUSTOMER'S NEWEST INVOICES FIRST
2466 l_dflt_receipt_msmtch ='n' THEN
2467
2468 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2469 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Apply to customers newest invoices first');
2470 END IF;
2471
2472 OPEN c_all_open_invs_desc (l_customer_num, l_org_id,l_currency_code);
2473 LOOP
2474 FETCH c_all_open_invs_desc INTO c_all_open_invs_desc_rec;
2475 EXIT WHEN c_all_open_invs_desc%NOTFOUND OR l_rcpt_amount = 0 OR l_rcpt_amount IS NULL;
2476 IF c_all_open_invs_desc_rec.AMOUNT_DUE_REMAINING > 0 THEN
2477 j := j + 1;
2478
2479 --Populate receipt table
2480 l_rcpt_tbl(j).INVOICE_ID := c_all_open_invs_desc_rec.ar_invoice_id;
2481 l_rcpt_tbl(j).INVOICE_NUMBER := c_all_open_invs_desc_rec.ar_invoice_number;
2482 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_currency_code;
2483 l_rcpt_tbl(j).INVOICE_LINE_ID := c_all_open_invs_desc_rec.invoice_line_id;
2484 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_all_open_invs_desc_rec.line_number;
2485
2486 --Amount Applied will be total amount applied including line and tax
2487 l_rcpt_tbl(j).AMOUNT_APPLIED := c_all_open_invs_desc_rec.amount_due_remaining;
2488
2489 IF l_rcpt_amount < l_rcpt_tbl(j).AMOUNT_APPLIED THEN
2490 l_rcpt_tbl(j).AMOUNT_APPLIED := l_rcpt_amount;
2491 l_rcpt_amount := 0;
2492 ELSE
2493 l_rcpt_amount := l_rcpt_amount - l_rcpt_tbl(j).AMOUNT_APPLIED;
2494 END IF;
2495
2496 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2497 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_ID : '||l_rcpt_tbl(j).INVOICE_ID);
2498 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
2499 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);
2500 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);
2501 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
2502 END IF;
2503 END IF;
2504 END LOOP;
2505 CLOSE c_all_open_invs_desc;
2506 END IF;
2507 --Get grouped application table (in the form of invoice header > multiple invoice lines table)
2508 GET_APPLICATIONS( p_rcpt_tbl => l_rcpt_tbl
2509 ,x_appl_tbl => l_appl_tbl);
2510 x_appl_tbl := l_appl_tbl;
2511 END IF;
2512
2513 x_onacc_amount :=l_onacc_amount;
2514 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2515 --to be coded -onacc
2516
2517 EXCEPTION
2518 WHEN OTHERS THEN
2519 x_appl_tbl.DELETE;
2520 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2521 x_msg_count := l_msg_count;
2522 x_msg_data := sqlerrm;
2523 END receipt_mismatch;
2524
2525
2526 --Get default cash application rule
2527 PROCEDURE get_default_cash_app_rule(p_org_id IN OKL_CASH_ALLCTN_RLS.ORG_ID%TYPE,
2528 x_dflt_cat_id OUT NOCOPY OKL_CASH_ALLCTN_RLS.ID%TYPE,
2529 x_dflt_tolerance OUT NOCOPY OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE,
2530 x_dflt_days_past_quote_valid OUT NOCOPY OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE,
2531 x_dflt_months_to_bill_ahead OUT NOCOPY OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE,
2532 x_dflt_under_payment OUT NOCOPY OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE,
2533 x_dflt_over_payment OUT NOCOPY OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE ,
2534 x_dflt_receipt_msmtch OUT NOCOPY OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE
2535 ) IS
2536
2537 ---------------------------
2538 -- DECLARE Local Variables
2539 ---------------------------
2540 l_org_id OKL_CASH_ALLCTN_RLS.ORG_ID%TYPE := p_org_id;
2541 l_dflt_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
2542 l_dflt_tolerance OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE DEFAULT NULL;
2543 l_dflt_days_past_quote_valid OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE DEFAULT NULL;
2544 l_dflt_months_to_bill_ahead OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE DEFAULT NULL;
2545 l_dflt_under_payment OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
2546 l_dflt_over_payment OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
2547 l_dflt_receipt_msmtch OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE DEFAULT NULL;
2548
2549
2550 -------------------
2551 -- DECLARE Cursors
2552 -------------------
2553 -- get default cash applic rule for organization
2554 CURSOR c_dflt_cash_applic_rule(p_org_id NUMBER) IS
2555 SELECT ID
2556 ,AMOUNT_TOLERANCE_PERCENT
2557 ,DAYS_PAST_QUOTE_VALID_TOLERANC
2558 ,MONTHS_TO_BILL_AHEAD
2559 ,UNDER_PAYMENT_ALLOCATION_CODE
2560 ,OVER_PAYMENT_ALLOCATION_CODE
2561 ,RECEIPT_MSMTCH_ALLOCATION_CODE
2562 FROM OKL_CASH_ALLCTN_RLS_ALL
2563 WHERE default_rule = 'YES'
2564 AND TRUNC(end_date) IS NULL
2565 AND org_id = p_org_id;
2566
2567 BEGIN
2568 IF (G_DEBUG_ENABLED = 'Y') THEN
2569 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2570 END IF;
2571 -- get default cash application rules.
2572
2573 OPEN c_dflt_cash_applic_rule(l_org_id);
2574 FETCH c_dflt_cash_applic_rule INTO l_dflt_cat_id
2575 ,l_dflt_tolerance
2576 ,l_dflt_days_past_quote_valid
2577 ,l_dflt_months_to_bill_ahead
2578 ,l_dflt_under_payment
2579 ,l_dflt_over_payment
2580 ,l_dflt_receipt_msmtch;
2581 CLOSE c_dflt_cash_applic_rule;
2582
2583 x_dflt_cat_id := l_dflt_cat_id;
2584 x_dflt_tolerance:=l_dflt_tolerance;
2585 x_dflt_days_past_quote_valid := l_dflt_days_past_quote_valid;
2586 x_dflt_months_to_bill_ahead :=l_dflt_months_to_bill_ahead;
2587 x_dflt_under_payment:=l_dflt_under_payment;
2588 x_dflt_over_payment:=l_dflt_over_payment;
2589 x_dflt_receipt_msmtch := l_dflt_receipt_msmtch;
2590
2591 EXCEPTION
2592 WHEN OTHERS THEN
2593 l_dflt_cat_id:=null;
2594 END get_default_cash_app_rule;
2595
2596
2597 --Get application details table for the given receipt table
2598 --So the application details table will be table of invoice header
2599 --and its corresponding invoice lines with its applied amount
2600 PROCEDURE GET_APPLICATIONS ( p_rcpt_tbl IN okl_rcpt_dtls_tbl_type
2601 ,x_appl_tbl OUT NOCOPY okl_appl_dtls_tbl_type) IS
2602
2603 l_inv_num RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE DEFAULT NULL;
2604 l_prev_inv_num RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE DEFAULT NULL;
2605 line_counter NUMBER;
2606 hdr_counter NUMBER;
2607 i NUMBER;
2608 j NUMBER;
2609 complete_cycle BOOLEAN;
2610 counter NUMBER;
2611 l_total_amount_applied NUMBER := 0;
2612 l_line_amount_round NUMBER:=0;
2613 l_rcpt_tbl okl_rcpt_dtls_tbl_type := p_rcpt_tbl;
2614 l_appl_tbl okl_appl_dtls_tbl_type;
2615 l_inv_lns_tbl okl_inv_line_tbl_type;
2616 l_total_amount_app_from NUMBER := 0;
2617
2618 BEGIN
2619 /*
2620 Description:
2621 . We will have l_rcpt_tbl which is flat table where each row consists of
2622 invoice header and invoice line information.
2623 . We need to group invoice lines to corresponding invoice header. It is not
2624 necessary that all invoice headers will come sequentially in this table.
2625 So the logic to group invoice lines is not straight forward.
2626 . And return the resulting table back to calling procedure, which in turn, will
2627 return to calling procedure (OKL Lockbox API)
2628 */
2629
2630 /*
2631 Logic Flow:
2632 . hdr_counter is the counter for invoice header record
2633 . line_counter is the counter for invoice line record within each invoice header
2634 . complete_cycle ( = TRUE) indicates that l_rcpt_tbl has been traversed
2635 completely.
2636 . once invoice line is grouped in any invoice header, then will make amount_applied
2637 field to -1 in l_rcpt_tbl
2638 . counter is the counter for number of invoice lines whose amount_applied
2639 has been set to -1. We will increment counter everytime will update amount to -1
2640 . If value of counter is equal to count of l_rcpt_tbl then will terminate the loop
2641 . It makes a check in the loop whether amount is -1 if it is -1 then leave it
2642 otherwise if it is accessing any record from top of l_rcpt_tbl for the first time
2643 then assign create invoice header record and create 1st invoice line for this header.
2644 Also set amount to -1 and increment counter.
2645 . For the subsequent records in l_rcpt_tbl, if same invoice header is found then
2646 create another line record in invoice lines table. Also set amount to -1 and
2647 increment counter.
2648 . If the index of l_rcpt_tbl is the last then associate invoice lines table to
2649 invoice header record. Set complete_cycle to TRUE. Re-initialize i to 1,
2650 which will point to first record of l_rcpt_tbl. Re-initialize line_counter to 1.
2651 Increment hdr_counter.
2652 */
2653 IF (G_DEBUG_ENABLED = 'Y') THEN
2654 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2655 END IF;
2656 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2657 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Starting execution of OKL_AUTO_CASH_APPL_RULES_PVT.GET_APPLICATIONS ...');
2658 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Count of l_rcpt_tbl: '|| l_rcpt_tbl.COUNT);
2659 END IF;
2660
2661 IF (l_rcpt_tbl IS NOT NULL AND l_rcpt_tbl.COUNT > 0) THEN
2662 hdr_counter := l_rcpt_tbl.FIRST;
2663 complete_cycle := FALSE;
2664 line_counter := l_rcpt_tbl.FIRST;
2665 i := l_rcpt_tbl.FIRST;
2666 j := i;
2667 counter := 0;
2668 WHILE (i <= l_rcpt_tbl.LAST)
2669 LOOP
2670 IF (l_rcpt_tbl(i).amount_applied <> -1) THEN
2671 l_inv_num := l_rcpt_tbl(i).INVOICE_NUMBER;
2672
2673 IF ( i = l_rcpt_tbl.FIRST OR complete_cycle = TRUE) THEN
2674 complete_cycle := FALSE;
2675 --Assign invoice header record in applications table
2676 l_appl_tbl(hdr_counter).inv_hdr_rec.invoice_number := l_inv_num;
2677 l_appl_tbl(hdr_counter).inv_hdr_rec.invoice_id := l_rcpt_tbl(i).invoice_id;
2678 l_appl_tbl(hdr_counter).inv_hdr_rec.invoice_currency_code:= l_rcpt_tbl(i).invoice_currency_code;
2679 --Assign invoice lines record in invoice lines table
2680 l_inv_lns_tbl(line_counter).invoice_line_id := l_rcpt_tbl(i).invoice_line_id;
2681 l_inv_lns_tbl(line_counter).invoice_line_number := l_rcpt_tbl(i).invoice_line_number;
2682 -- round off the amount applied
2683 l_inv_lns_tbl(line_counter).amount_applied := l_rcpt_tbl(i).AMOUNT_APPLIED;
2684 l_inv_lns_tbl(line_counter).amount_applied_from := l_rcpt_tbl(i).AMOUNT_APPLIED_FROM;
2685 l_inv_lns_tbl(line_counter).trans_to_receipt_rate := l_rcpt_tbl(i).trans_to_receipt_rate;
2686 l_total_amount_app_from := l_total_amount_app_from + l_inv_lns_tbl(line_counter).amount_applied_from;
2687 l_total_amount_applied := l_total_amount_applied + l_inv_lns_tbl(line_counter).amount_applied;
2688
2689 line_counter := line_counter + 1;
2690 l_prev_inv_num := l_inv_num;
2691 l_rcpt_tbl(i).amount_applied := -1;
2692 counter := counter + 1;
2693 ELSIF (i <> l_rcpt_tbl.FIRST) THEN
2694 IF (l_inv_num = l_prev_inv_num) THEN
2695 --Assign invoice lines record in invoice lines table
2696 l_inv_lns_tbl(line_counter).invoice_line_id := l_rcpt_tbl(i).invoice_line_id;
2697 l_inv_lns_tbl(line_counter).invoice_line_number := l_rcpt_tbl(i).invoice_line_number;
2698 -- round off the amount applied
2699 l_inv_lns_tbl(line_counter).amount_applied := l_rcpt_tbl(i).AMOUNT_APPLIED;
2700 l_inv_lns_tbl(line_counter).amount_applied_from := l_rcpt_tbl(i).AMOUNT_APPLIED_FROM;
2701 l_inv_lns_tbl(line_counter).trans_to_receipt_rate := l_rcpt_tbl(i).trans_to_receipt_rate;
2702 l_total_amount_app_from := l_total_amount_app_from + l_inv_lns_tbl(line_counter).amount_applied_from;
2703
2704 l_total_amount_applied := l_total_amount_applied + l_inv_lns_tbl(line_counter).amount_applied;
2705
2706 line_counter := line_counter + 1;
2707 l_prev_inv_num := l_inv_num;
2708 l_rcpt_tbl(i).amount_applied := -1;
2709 counter := counter + 1;
2710 END IF;
2711 END IF;
2712 IF ( i = l_rcpt_tbl.LAST) THEN
2713 i := l_rcpt_tbl.FIRST;
2714 complete_cycle := TRUE;
2715 --Assign lines table generated into applications table
2716 l_appl_tbl(hdr_counter).inv_hdr_rec.amount_applied := l_total_amount_applied;
2717 l_appl_tbl(hdr_counter).inv_hdr_rec.amount_applied_from := l_total_amount_app_from;
2718 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;
2719 l_total_amount_app_from := 0;
2720 l_total_amount_applied := 0;
2721 l_appl_tbl(hdr_counter).inv_lines_tbl := l_inv_lns_tbl;
2722 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2723 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Created invoice header and its lines');
2724 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Invoice Number: '|| l_appl_tbl(hdr_counter).inv_hdr_rec.invoice_number);
2725 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Number of lines for this invoice number: '|| l_inv_lns_tbl.COUNT);
2726 END IF;
2727 --Delete lines table and reinitialize
2728 l_inv_lns_tbl.delete;
2729 line_counter := 1;
2730 hdr_counter := hdr_counter + 1;
2731 ELSE
2732 i := i + 1;
2733 END IF;
2734 ELSE
2735 IF ( i = l_rcpt_tbl.LAST) THEN
2736 i := l_rcpt_tbl.FIRST;
2737 complete_cycle := TRUE;
2738 --Assign lines table into applications table
2739 l_appl_tbl(hdr_counter).inv_hdr_rec.amount_applied := l_total_amount_applied;
2740 l_appl_tbl(hdr_counter).inv_hdr_rec.amount_applied_from := l_total_amount_app_from;
2741 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;
2742 l_total_amount_app_from := 0;
2743 l_total_amount_applied := 0;
2744
2745 l_appl_tbl(hdr_counter).inv_lines_tbl := l_inv_lns_tbl;
2746 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2747 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Created invoice header and its lines');
2748 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Invoice Number: '|| l_appl_tbl(hdr_counter).inv_hdr_rec.invoice_number);
2749 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Number of lines for this invoice number: '|| l_inv_lns_tbl.COUNT);
2750 END IF;
2751 --Delete lines table and reinitialize
2752 l_inv_lns_tbl.delete;
2753 line_counter := 1;
2754 hdr_counter := hdr_counter + 1;
2755 ELSE
2756 i := i + 1;
2757 END IF;
2758 END IF;
2759 IF (counter = l_rcpt_tbl.COUNT) THEN
2760 EXIT;
2761 END IF;
2762 END LOOP;
2763 --If invoice lines table is not null and count is greater than 0
2764 --Then assign invoice lines table to last created invoice header.
2765 IF (l_inv_lns_tbl IS NOT NULL AND l_inv_lns_tbl.COUNT > 0) THEN
2766 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2767 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Created invoice header and its lines');
2768 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Invoice Number: '|| l_appl_tbl(hdr_counter).inv_hdr_rec.invoice_number);
2769 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Number of lines for this invoice number: '|| l_inv_lns_tbl.COUNT);
2770 END IF;
2771 l_appl_tbl(hdr_counter).inv_hdr_rec.amount_applied := l_total_amount_applied;
2772 l_appl_tbl(hdr_counter).inv_hdr_rec.amount_applied_from := l_total_amount_app_from;
2773 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;
2774 l_total_amount_app_from := 0;
2775 l_total_amount_applied := 0;
2776 l_appl_tbl(hdr_counter).inv_lines_tbl := l_inv_lns_tbl;
2777 END IF;
2778 END IF;
2779
2780 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2781 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Padding remaining invoice headers to make number of invoice headers divisible by 8');
2782 END IF;
2783
2784 --Pad additional application records
2785 IF (l_appl_tbl.COUNT > 0 AND l_appl_tbl.COUNT < 8) OR mod((l_appl_tbl.COUNT), 8) <> 0 THEN
2786 j := l_appl_tbl.LAST;
2787 LOOP
2788 EXIT WHEN mod((l_appl_tbl.COUNT), 8) = 0; -- multiple of 8
2789 j := j + 1;
2790 l_appl_tbl(j).inv_hdr_rec.INVOICE_CURRENCY_CODE := ''; --this is just to buffer the record out !!
2791 END LOOP;
2792 END IF;
2793 --Assign local application details table to out variable
2794 x_appl_tbl := l_appl_tbl;
2795 EXCEPTION
2796 WHEN OTHERS THEN
2797 NULL;
2798 END GET_APPLICATIONS;
2799
2800 --END: Bug 6275659 by nikshah
2801
2802
2803 /* sosharma 30-jul-2007
2804 Added proceudure to handle cash application rules on consolidated Invoices
2805 */
2806 PROCEDURE auto_cashapp_for_consinv ( p_api_version IN NUMBER
2807 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
2808 ,x_return_status OUT NOCOPY VARCHAR2
2809 ,x_msg_count OUT NOCOPY NUMBER
2810 ,x_msg_data OUT NOCOPY VARCHAR2
2811 ,p_customer_num IN VARCHAR2 DEFAULT NULL
2812 ,p_cons_inv IN VARCHAR2
2813 ,p_currency_code IN VARCHAR2
2814 ,p_amount_app_to IN NUMBER DEFAULT NULL
2815 ,p_amount_app_from IN NUMBER DEFAULT NULL
2816 ,p_inv_to_rct_rate IN NUMBER DEFAULT NULL
2817 ,p_receipt_date IN DATE
2818 ,p_org_id IN NUMBER
2819 ,x_appl_tbl OUT NOCOPY okl_appl_dtls_tbl_type
2820 ,x_onacc_amount OUT NOCOPY NUMBER
2821 ,x_unapply_amount OUT NOCOPY NUMBER
2822 ) IS
2823
2824 ---------------------------
2825 -- DECLARE Local Variables
2826 ---------------------------
2827
2828
2829 l_amount_apply_pref VARCHAR2(15) := 'PRORATE';
2830
2831
2832 l_original_line_amount NUMBER;
2833 l_original_tax_amount NUMBER;
2834 l_total_amount NUMBER;
2835 l_line_amount_applied NUMBER;
2836 l_tax_amount_applied NUMBER;
2837
2838
2839 l_cons_inv VARCHAR2(120) := p_cons_inv;
2840 l_currency_code VARCHAR2(45) := p_currency_code;
2841 l_amount_app_from NUMBER := p_amount_app_from;
2842 l_amount_app_to NUMBER := p_amount_app_to;
2843 l_inv_to_rct_rate NUMBER := p_inv_to_rct_rate;
2844 l_inv_curr_Code VARCHAR2(45);
2845 l_receipt_Date DATE := p_receipt_date;
2846 l_cross_curr_enabled varchar2(3):='N';
2847 l_conversion_rate NUMBER;
2848 l_exchange_rate_type VARCHAR2(45);
2849 l_tot_amt_app_from NUMBER;
2850
2851 l_due_date DATE DEFAULT NULL;
2852
2853 l_customer_id NUMBER;
2854 l_customer_num VARCHAR2(30) := p_customer_num;
2855 l_cons_bill_id NUMBER;
2856 l_cons_bill_num VARCHAR2(90);
2857 l_last_contract_id OKC_K_HEADERS_V.ID%TYPE DEFAULT 1;
2858 l_contract_id NUMBER;
2859 l_contract_num VARCHAR2(120);
2860 l_contract_number_start_date OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT NULL;
2861 l_contract_number_id OKC_K_HEADERS_V.ID%TYPE DEFAULT NULL;
2862
2863 l_receivables_invoice_num NUMBER DEFAULT NULL;
2864 l_over_pay VARCHAR(1) DEFAULT NULL;
2865 l_ordered CONSTANT VARCHAR2(3) := 'ODD';
2866 l_prorate CONSTANT VARCHAR2(3) := 'PRO';
2867
2868
2869 l_org_id NUMBER := p_org_id;
2870
2871 i NUMBER;
2872 j NUMBER;
2873 d NUMBER DEFAULT NULL;
2874
2875 l_first_prorate_rec NUMBER DEFAULT NULL;
2876 l_first_prorate_rec_j NUMBER DEFAULT NULL;
2877
2878 l_appl_tolerance NUMBER;
2879 l_temp_val NUMBER;
2880 l_inv_tot NUMBER := 0;
2881 l_cont_tot NUMBER := 0;
2882 l_pro_rate_inv_total NUMBER := 0;
2883 l_stream_tot NUMBER := 0;
2884 l_diff_amount NUMBER := 0;
2885 l_inv_total_amt NUMBER := 0;
2886
2887
2888 l_start_date DATE;
2889 l_same_date VARCHAR(1) DEFAULT NULL;
2890 l_same_cash_app_rule VARCHAR(1) DEFAULT NULL;
2891
2892 l_count NUMBER DEFAULT NULL;
2893
2894
2895 l_check_cau_id OKL_CSH_ALLCTN_RL_HDR.ID%TYPE DEFAULT NULL;
2896 l_cau_id OKL_CSH_ALLCTN_RL_HDR.ID%TYPE DEFAULT NULL;
2897 l_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
2898 l_sty_id OKL_CNSLD_AR_STRMS_V.STY_ID%TYPE;
2899 l_tolerance OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE;
2900 l_days_past_quote_valid OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE;
2901 l_months_to_bill_ahead OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE;
2902 l_under_payment OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE;
2903 l_over_payment OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE;
2904 l_receipt_msmtch OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE;
2905
2906 l_dflt_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
2907 l_dflt_tolerance OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE DEFAULT NULL;
2908 l_dflt_days_past_quote_valid OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE DEFAULT NULL;
2909 l_dflt_months_to_bill_ahead OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE DEFAULT NULL;
2910 l_dflt_under_payment OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
2911 l_dflt_over_payment OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
2912 l_dflt_receipt_msmtch OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE DEFAULT NULL;
2913
2914 l_valid_yn VARCHAR2(1);
2915
2916 l_api_version NUMBER := 1.0;
2917 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
2918 l_return_status VARCHAR2(1);
2919 l_msg_count NUMBER;
2920 l_msg_data VARCHAR2(2000);
2921
2922 l_inv_num RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE DEFAULT NULL;
2923 l_prev_inv_num RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE DEFAULT NULL;
2924 line_counter NUMBER;
2925 hdr_counter NUMBER;
2926
2927 ------------------------------
2928 -- DECLARE Record/Table Types
2929 ------------------------------
2930
2931
2932 l_rcpt_tbl okl_auto_cash_appl_rules_pvt.okl_rcpt_dtls_tbl_type;
2933 l_appl_tbl okl_auto_cash_appl_rules_pvt.okl_appl_dtls_tbl_type;
2934 l_inv_lns_tbl okl_auto_cash_appl_rules_pvt.okl_inv_line_tbl_type;
2935
2936 -- ** internal use only ** --
2937
2938 TYPE okl_inv_refs_type IS RECORD ( contract_number AR_PAYMENTS_INTERFACE_ALL.INVOICE1%TYPE DEFAULT NULL
2939 ,cons_inv_number AR_PAYMENTS_INTERFACE_ALL.INVOICE1%TYPE DEFAULT NULL
2940 );
2941
2942 TYPE okl_inv_refs_tbl_type IS TABLE OF okl_inv_refs_type
2943 INDEX BY BINARY_INTEGER;
2944
2945 l_cust_inv_ref okl_inv_refs_tbl_type;
2946
2947
2948 -------------------
2949 -- DECLARE Cursors
2950 -------------------
2951 -- cursor to get contracts information
2952
2953 CURSOR c_open_invs2( cp_cons_bill_id IN NUMBER
2954 ,cp_org_id IN NUMBER
2955 ,cp_customer_num IN VARCHAR2
2956 ) IS
2957 SELECT lpt.stream_type_id
2958 ,lpt.amount_due_remaining invoice_due_remaining
2959 ,lpt.amount_due_original invoice_due_original
2960 ,lpt.currency_code invoice_currency_code
2961 ,lpt.AR_INVOICE_NUMBER
2962 ,lpt.AR_INVOICE_ID
2963 ,lpt.stream_element_id
2964 ,lpt.ar_invoice_line_id
2965 ,lpt.ar_invoice_line_number
2966 ,lpt.trx_date invoice_date
2967 ,lpt.contract_number
2968 FROM okl_rcpt_consinv_balances_uv lpt
2969 WHERE lpt.consolidated_invoice_id = cp_cons_bill_id
2970 --asawanka changed for bug #5391874
2971 AND lpt.customer_account_number = nvl(cp_customer_num,lpt.customer_account_number)
2972 AND lpt.org_id=cp_org_id
2973 AND lpt.status='OP';
2974
2975 c_open_invs_rec c_open_invs2%ROWTYPE;
2976 TYPE open_inv_tbl_type IS TABLE OF c_open_invs2%ROWTYPE INDEX BY BINARY_INTEGER;
2977 open_inv_tbl open_inv_tbl_type;
2978 open_inv_contract_tbl open_inv_tbl_type;
2979
2980 ----------
2981
2982 CURSOR c_inv_date ( cp_cons_bill_id IN NUMBER
2983 ,cp_customer_num IN VARCHAR2) IS
2984 SELECT DISTINCT(lpt.contract_number)
2985 ,lpt.invoice_date Start_date, lpt.contract_id,lpt.currency_code
2986 FROM okl_rcpt_consinv_balances_uv lpt
2987 WHERE lpt.consolidated_invoice_id = cp_cons_bill_id --Always passing cp_cons_bill_id as not null so no need to have nvl
2988 AND lpt.customer_account_number = NVL (cp_customer_num, lpt.customer_account_number);
2989
2990
2991 c_inv_date_rec c_inv_date%ROWTYPE;
2992
2993 ----------
2994
2995 -- get stream application order
2996 CURSOR c_stream_alloc ( cp_str_all_type IN VARCHAR2
2997 ,cp_cat_id IN NUMBER ) IS
2998 SELECT sty_id
2999 FROM OKL_STRM_TYP_ALLOCS
3000 WHERE stream_allc_type = cp_str_all_type
3001 AND cat_id = cp_cat_id
3002 ORDER BY sequence_number;
3003
3004 ----------
3005
3006 /*
3007 -- get cash applic rule id
3008 */
3009
3010 CURSOR c_cash_rle_id_csr ( cp_khr_id IN NUMBER) IS
3011 SELECT to_number(a.object1_id1)
3012 FROM OKC_RULES_B a, OKC_RULE_GROUPS_B b
3013 WHERE a.rgp_id = b.id
3014 AND b.rgd_code = 'LABILL'
3015 AND a.rule_information_category = 'LAINVD'
3016 AND a.dnz_chr_id = b.chr_id
3017 AND a.dnz_chr_id = cp_khr_id;
3018
3019 ----------
3020
3021 -- get cash applic rule for contract
3022 CURSOR c_cash_rule_csr ( cp_cau_id IN NUMBER ) IS
3023 SELECT ID
3024 ,AMOUNT_TOLERANCE_PERCENT
3025 ,DAYS_PAST_QUOTE_VALID_TOLERANC
3026 ,MONTHS_TO_BILL_AHEAD
3027 ,UNDER_PAYMENT_ALLOCATION_CODE
3028 ,OVER_PAYMENT_ALLOCATION_CODE
3029 ,RECEIPT_MSMTCH_ALLOCATION_CODE
3030 FROM OKL_CASH_ALLCTN_RLS
3031 WHERE CAU_ID = cp_cau_id
3032 AND START_DATE <= trunc(SYSDATE)
3033 AND (END_DATE >= trunc(SYSDATE) OR END_DATE IS NULL);
3034
3035
3036 ----------
3037 -- get a contract number if not known
3038 CURSOR c_get_contract_num (cp_cons_bill_id IN NUMBER) IS
3039 SELECT lpt.contract_id, lpt.contract_number
3040 FROM okl_rcpt_consinv_balances_uv lpt, okc_k_headers_all_b khr
3041 WHERE lpt.consolidated_invoice_id = cp_cons_bill_id
3042 AND lpt.status = 'OP'
3043 AND lpt.amount_due_remaining > 0
3044 AND khr.id = lpt.contract_id
3045 ORDER BY khr.start_date;
3046 ----------
3047
3048
3049 CURSOR valid_consinv(cl_cons_inv IN VARCHAR2)
3050 IS
3051 select ID
3052 from OKL_CNSLD_AR_HDRS_B
3053 where consolidated_invoice_number=cl_cons_inv;
3054
3055 l_unapply_amount NUMBER:=0;
3056 l_onacc_amount NUMBER:=0;
3057
3058 BEGIN
3059
3060 OPEN valid_consinv(l_cons_inv);
3061 FETCH valid_consinv into l_cons_bill_id;
3062 IF l_cons_bill_id IS NULL THEN
3063 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3064 RETURN;
3065 END IF;
3066 CLOSE valid_consinv;
3067
3068 IF l_cons_inv IS NOT NULL THEN
3069 l_count := 0;
3070 i := 0;
3071 FOR c_open_invs_rec IN c_open_invs2 (l_cons_bill_id,l_org_id, l_customer_num)
3072 LOOP
3073 IF c_open_invs_rec.invoice_due_remaining > 0 THEN
3074 i := i + 1;
3075 open_inv_tbl(i) := c_open_invs_rec;
3076 l_count := l_count + 1;
3077 END IF;
3078 END LOOP;
3079 i := 0;
3080 IF l_count > 0 THEN
3081 l_cons_bill_num := l_cons_inv;
3082 l_contract_num := NULL;
3083 END IF;
3084
3085 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3086 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_contract_num : '||l_contract_num);
3087 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cons_bill_num : '||l_cons_bill_num);
3088 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cons_bill_id : '||l_cons_bill_id);
3089 END IF;
3090 END IF;
3091
3092
3093
3094
3095 -- START OKL CASH APPLICATION.
3096
3097 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3098 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Checking whether l_contract_num or l_cons_bill_num is not null ..');
3099
3100 END IF;
3101
3102 IF l_cons_bill_num IS NOT NULL THEN --(1)
3103
3104 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3105 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cons_bill_num is not null - '||l_cons_bill_num);
3106
3107 END IF;
3108
3109 j := 0;
3110
3111 OPEN c_inv_date(l_cons_bill_id, l_customer_num);
3112 FETCH c_inv_date INTO l_contract_number_start_date, l_start_date, l_contract_number_id,l_inv_curr_Code;
3113 CLOSE c_inv_date;
3114
3115 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3116 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_contract_number_start_date, l_start_date, l_contract_number_id : '||
3117 l_contract_number_start_date||', '||l_start_date||', '||l_contract_number_id);
3118
3119 END IF;
3120
3121 d :=0;
3122 FOR c_inv_date_rec IN c_inv_date(l_cons_bill_id, l_customer_num)
3123 LOOP
3124
3125 IF TRUNC(l_start_date) = TRUNC(c_inv_date_rec.start_date) THEN
3126 l_same_date := 'Y';
3127 d := d + 1;
3128
3129 ELSE
3130 l_same_date := 'N';
3131 EXIT;
3132 END IF;
3133
3134 END LOOP;
3135
3136 IF d = 1 THEN
3137 l_same_date := 'N';
3138 END IF;
3139
3140 -- ************************************************
3141 -- Check for same cash application rule
3142 -- ************************************************
3143
3144 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3145 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Check for same cash application rule');
3146
3147
3148 END IF;
3149 OPEN c_cash_rle_id_csr (l_contract_number_id);
3150 FETCH c_cash_rle_id_csr INTO l_cau_id;
3151 CLOSE c_cash_rle_id_csr;
3152
3153 d := 0;
3154 FOR c_inv_date_rec IN c_inv_date(l_cons_bill_id, l_customer_num)
3155 LOOP
3156
3157 l_check_cau_id := NULL;
3158
3159 OPEN c_cash_rle_id_csr (c_inv_date_rec.contract_id);
3160 FETCH c_cash_rle_id_csr INTO l_check_cau_id;
3161 CLOSE c_cash_rle_id_csr;
3162
3163 IF l_check_cau_id IS NULL THEN
3164 l_same_cash_app_rule := 'N';
3165 EXIT;
3166 END IF;
3167
3168 IF l_cau_id = l_check_cau_id THEN
3169 l_same_cash_app_rule := 'Y';
3170 d := d + 1;
3171 ELSE
3172 l_same_cash_app_rule := 'N';
3173 EXIT;
3174 END IF;
3175
3176 END LOOP;
3177
3178 IF d = 1 THEN
3179 l_same_cash_app_rule := 'N';
3180 END IF;
3181
3182 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3183 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_same_date, l_same_cash_app_rule : '||
3184 l_same_date||', '||l_same_cash_app_rule);
3185
3186 END IF;
3187
3188
3189
3190 log_debug('Receipt Currency = '||l_currency_code);
3191 log_debug('Contract Currency = '||l_inv_curr_Code);
3192 log_debug('l_amount_app_from = '||l_amount_app_from);
3193 log_debug('l_amount_app_to = '||l_amount_app_to);
3194 log_debug('l_inv_to_rct_rate = '||l_inv_to_rct_rate);
3195 IF l_currency_code = l_inv_curr_Code THEN
3196 IF l_amount_app_from IS NULL AND l_amount_app_to IS NULL THEN
3197 OKL_API.set_message( p_app_name => G_APP_NAME
3198 ,p_msg_name => 'OKL_BPD_INVALID_PARAMS'
3199 );
3200 RAISE G_EXCEPTION_HALT_VALIDATION;
3201 ELSIF l_amount_app_from IS NULL THEN
3202 l_amount_app_from := l_amount_app_to;
3203 ELSE
3204 l_amount_app_to := l_amount_app_from;
3205 END IF;
3206 ELSE
3207 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
3208 IF ( l_amount_app_to * l_inv_to_rct_rate) <> l_amount_app_from THEN
3209 OKL_API.set_message( p_app_name => G_APP_NAME
3210 ,p_msg_name => 'OKL_BPD_PARAMS_MISMATCH'
3211 );
3212 END IF;
3213 IF l_inv_to_rct_rate <> 0 and ( ( l_amount_app_from / l_inv_to_rct_rate) <> l_amount_app_to) THEN
3214 OKL_API.set_message( p_app_name => G_APP_NAME
3215 ,p_msg_name => 'OKL_BPD_PARAMS_MISMATCH'
3216 );
3217 END IF;
3218 END IF;
3219 /* l_cross_curr_enabled := nvl(FND_PROFILE.value('AR_ENABLE_CROSS_CURRENCY'),'N');
3220 log_debug('l_cross_curr_enabled = '||l_cross_curr_enabled);
3221 IF l_cross_curr_enabled <> 'Y' THEN
3222 OKL_API.set_message( p_app_name => G_APP_NAME
3223 ,p_msg_name => 'OKL_BPD_CROSS_CURR_NA'
3224 );
3225 RAISE G_EXCEPTION_HALT_VALIDATION;
3226 ELSE*/
3227 IF l_inv_to_rct_rate is null THEN
3228 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
3229 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
3230 IF l_exchange_rate_type IS NULL THEN
3231 OKL_API.set_message( p_app_name => G_APP_NAME
3232 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
3233 );
3234 RAISE G_EXCEPTION_HALT_VALIDATION;
3235 ELSE
3236 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
3237 ,l_currency_code
3238 ,l_receipt_date
3239 ,l_exchange_rate_type
3240 );
3241
3242 IF l_conversion_rate IN (0,-1) THEN
3243
3244 -- Message Text: No exchange rate defined
3245 x_return_status := okl_api.G_RET_STS_ERROR;
3246 okl_api.set_message( p_app_name => G_APP_NAME,
3247 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
3248 RAISE G_EXCEPTION_HALT_VALIDATION;
3249 END IF;
3250 END IF;
3251 ELSE
3252 l_conversion_rate := l_inv_to_rct_rate;
3253 END IF;
3254 log_debug('l_conversion_rate ='||l_conversion_rate);
3255 IF l_amount_app_from IS NULL AND l_amount_app_to IS NULL THEN
3256 OKL_API.set_message( p_app_name => G_APP_NAME
3257 ,p_msg_name => 'OKL_BPD_INVALID_PARAMS'
3258 );
3259 RAISE G_EXCEPTION_HALT_VALIDATION;
3260 ELSIF l_amount_app_from IS NULL THEN
3261 l_amount_app_from := l_amount_app_to * l_conversion_rate;
3262 ELSE
3263 l_amount_app_to := l_amount_app_from * (1/l_conversion_rate);
3264 END IF;
3265 -- END IF;
3266 END IF;
3267 l_amount_app_from := arp_util.CurrRound(l_amount_app_from,l_currency_code);
3268 l_amount_app_to := arp_util.CurrRound(l_amount_app_to,l_inv_curr_code);
3269 log_debug('l_amount_app_from = '||l_amount_app_from);
3270 log_debug('l_amount_app_to = '||l_amount_app_to);
3271 log_debug('l_inv_to_rct_rate = '||l_inv_to_rct_rate);
3272
3273 -- IF l_same_date = 'Y' AND l_same_cash_app_rule = 'Y' THEN --(1)
3274 IF l_same_date = 'Y' THEN
3275
3276 IF l_same_cash_app_rule = 'Y' THEN -- Use Common Cash Application
3277 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3278 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Date and CAR are same for all contracts ...');
3279 END IF;
3280 -- *******************************************************
3281 -- Start Line level cash application using the same cash
3282 -- application rule for all
3283 -- *******************************************************
3284 OPEN c_cash_rule_csr (l_cau_id);
3285 FETCH c_cash_rule_csr
3286 INTO l_cat_id
3287 ,l_tolerance
3288 ,l_days_past_quote_valid
3289 ,l_months_to_bill_ahead
3290 ,l_under_payment
3291 ,l_over_payment
3292 ,l_receipt_msmtch;
3293 CLOSE c_cash_rule_csr;
3294 Elsif l_same_cash_app_rule = 'N' THEN -- Use Default Cash Application
3295 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3296 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Same Date but different CAR for all contracts ...');
3297 END IF;
3298 -- *******************************************************
3299 -- Start Line level cash application using the same cash
3300 -- application rule for all
3301 -- *******************************************************
3302 Get_Default_Cash_App_Rule(
3303 l_org_id
3304 ,l_cat_id
3305 ,l_tolerance
3306 ,l_days_past_quote_valid
3307 ,l_months_to_bill_ahead
3308 ,l_under_payment
3309 ,l_over_payment
3310 ,l_receipt_msmtch);
3311 End If;
3312 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3313 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Date and CAR are same for all contracts ...');
3314
3315 END IF;
3316
3317 -- ************************************************
3318 -- Stream level cash application processing BEGINS
3319 -- ************************************************
3320
3321 -- get stream total
3322
3323 l_stream_tot := 0;
3324 FOR i IN open_inv_tbl.FIRST..open_inv_tbl.LAST
3325 LOOP
3326 -- l_invoice_currency_code := c_open_invs_rec.currency_code;
3327 c_open_invs_rec := open_inv_tbl(i);
3328 IF c_open_invs_rec.invoice_due_remaining > 0 THEN
3329 l_stream_tot := l_stream_tot + c_open_invs_rec.invoice_due_remaining;
3330 END IF;
3331 END LOOP;
3332
3333 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3334 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_stream_tot : ' || l_stream_tot);
3335 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_amount_app_to : ' || l_amount_app_to);
3336
3337
3338
3339 END IF;
3340 -- calculate tolerance
3341 IF l_stream_tot > l_amount_app_to THEN
3342 l_appl_tolerance := l_stream_tot * (1 - l_tolerance / 100);
3343 ELSE
3344 l_appl_tolerance := l_stream_tot;
3345 END IF;
3346
3347 IF l_stream_tot > l_amount_app_to AND l_appl_tolerance > l_amount_app_to THEN -- UNDERPAYMENT (2)
3348
3349 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3350 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'UNDERPAYMENT ...');
3351
3352 END IF;
3353
3354 IF l_under_payment In ('U','u') THEN -- Unapply underpayment (3)
3355 IF l_currency_code = l_inv_curr_code THEN
3356 l_unapply_amount:=l_amount_app_to;
3357 ELSE
3358 l_unapply_amount:= l_amount_app_to * l_conversion_rate;
3359 l_unapply_amount := GET_ROUNDED_AMOUNT(l_unapply_amount,l_currency_code);
3360 END IF;
3361 ELSIF l_under_payment IN ('T','t') THEN -- ORDERED (3)
3362
3363 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3364 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ORDERED ...');
3365
3366 END IF;
3367 OPEN c_stream_alloc (l_ordered, l_cat_id);
3368 LOOP
3369 FETCH c_stream_alloc INTO l_sty_id;
3370 EXIT WHEN c_stream_alloc%NOTFOUND
3371 OR l_amount_app_to = 0
3372 OR l_amount_app_to IS NULL;
3373
3374 FOR i IN open_inv_tbl.FIRST..open_inv_tbl.LAST
3375 LOOP
3376 c_open_invs_rec := open_inv_tbl(i);
3377 EXIT WHEN l_amount_app_to = 0 OR l_amount_app_to IS NULL;
3378 IF c_open_invs_rec.stream_type_id = l_sty_id THEN
3379 j := j + 1;
3380
3381 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invs_rec.ar_invoice_number;
3382 -- added for AR changes
3383 l_rcpt_tbl(j).INVOICE_ID := c_open_invs_rec.ar_invoice_id;
3384 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invs_rec.ar_invoice_line_number;
3385 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invs_rec.ar_invoice_line_id;
3386 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
3387 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invs_rec.invoice_due_remaining;
3388
3389 IF l_rcpt_tbl(j).AMOUNT_APPLIED > l_amount_app_to THEN
3390 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
3391 l_amount_app_to := 0;
3392 ELSE
3393 l_amount_app_to := l_amount_app_to - l_rcpt_tbl(j).AMOUNT_APPLIED;
3394 END IF;
3395 IF l_currency_code <> l_inv_curr_code THEN
3396 IF l_inv_to_rct_rate is null THEN
3397 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
3398 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
3399 IF l_exchange_rate_type IS NULL THEN
3400 OKL_API.set_message( p_app_name => G_APP_NAME
3401 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
3402 );
3403 RAISE G_EXCEPTION_HALT_VALIDATION;
3404 ELSE
3405 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
3406 ,l_currency_code
3407 ,l_receipt_date
3408 ,l_exchange_rate_type
3409 );
3410
3411 IF l_conversion_rate IN (0,-1) THEN
3412
3413 -- Message Text: No exchange rate defined
3414 x_return_status := okl_api.G_RET_STS_ERROR;
3415 okl_api.set_message( p_app_name => G_APP_NAME,
3416 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
3417 RAISE G_EXCEPTION_HALT_VALIDATION;
3418 END IF;
3419 END IF;
3420 ELSE
3421 l_conversion_rate := l_inv_to_rct_rate;
3422 END IF;
3423 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
3424 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
3425 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
3426 ELSE
3427 l_rcpt_tbl(j).trans_to_receipt_rate := null;
3428 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
3429 END IF;
3430 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3431 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
3432 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
3433 END IF;
3434 END IF;
3435 END LOOP;
3436 END LOOP;
3437 CLOSE c_stream_alloc;
3438
3439 ELSIF l_under_payment IN ('P','p') THEN -- PRO RATE (3)
3440
3441 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3442 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'PRO RATE ...');
3443
3444 END IF;
3445
3446 l_first_prorate_rec_j := j + 1;
3447
3448 -- i := 1;
3449 -- obtain all the streams that are part of the pro rate default rule.
3450
3451 FOR c_stream_alloc_rec IN c_stream_alloc (l_prorate, l_cat_id)
3452 LOOP
3453
3454 l_sty_id := c_stream_alloc_rec.sty_id;
3455 FOR i IN open_inv_tbl.FIRST..open_inv_tbl.LAST
3456 LOOP
3457 c_open_invs_rec := open_inv_tbl(i);
3458 IF c_open_invs_rec.stream_type_id = l_sty_id THEN
3459 j := j + 1;
3460
3461 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invs_rec.invoice_due_remaining;
3462 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invs_rec.ar_invoice_number;
3463 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
3464 l_rcpt_tbl(j).INVOICE_ID := c_open_invs_rec.ar_invoice_id;
3465 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invs_rec.ar_invoice_line_number;
3466 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invs_rec.ar_invoice_line_id;
3467 IF l_currency_code <> l_inv_curr_code THEN
3468 IF l_inv_to_rct_rate is null THEN
3469 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
3470 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
3471 IF l_exchange_rate_type IS NULL THEN
3472 OKL_API.set_message( p_app_name => G_APP_NAME
3473 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
3474 );
3475 RAISE G_EXCEPTION_HALT_VALIDATION;
3476 ELSE
3477 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
3478 ,l_currency_code
3479 ,l_receipt_date
3480 ,l_exchange_rate_type
3481 );
3482
3483 IF l_conversion_rate IN (0,-1) THEN
3484
3485 -- Message Text: No exchange rate defined
3486 x_return_status := okl_api.G_RET_STS_ERROR;
3487 okl_api.set_message( p_app_name => G_APP_NAME,
3488 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
3489 RAISE G_EXCEPTION_HALT_VALIDATION;
3490 END IF;
3491 END IF;
3492 ELSE
3493 l_conversion_rate := l_inv_to_rct_rate;
3494 END IF;
3495 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
3496 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
3497 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM:=arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
3498 ELSE
3499 l_rcpt_tbl(j).trans_to_receipt_rate := null;
3500 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
3501 END IF;
3502 l_pro_rate_inv_total := l_pro_rate_inv_total + l_rcpt_tbl(j).AMOUNT_APPLIED;
3503
3504 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3505 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
3506 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
3507 END IF;
3508 END IF;
3509 END LOOP; -- c_open_invs
3510 END LOOP; -- c_stream_alloc
3511
3512 -- Calc Pro Ration
3513 -- only if total amount of prorated invoices is greater than receipt
3514
3515 IF l_pro_rate_inv_total IS NULL OR l_pro_rate_inv_total = 0 THEN
3516
3517 -- Message Text: No prorated transaction types for contract.
3518 x_return_status := OKC_API.G_RET_STS_ERROR;
3519
3520 OKC_API.set_message( p_app_name => G_APP_NAME
3521 ,p_msg_name => 'OKL_BPD_DEF_NO_PRO'
3522 );
3523
3524 RAISE G_EXCEPTION_HALT_VALIDATION;
3525
3526 END IF;
3527
3528 IF (l_pro_rate_inv_total > l_amount_app_to) THEN
3529
3530 j := l_first_prorate_rec_j;
3531
3532 l_temp_val := l_amount_app_to / l_pro_rate_inv_total;
3533
3534 -- l_amount_app_to := 0;
3535
3536 LOOP
3537 l_rcpt_tbl(j).AMOUNT_APPLIED := l_temp_val * l_rcpt_tbl(j).AMOUNT_APPLIED;
3538 l_rcpt_tbl(j).AMOUNT_APPLIED:=GET_ROUNDED_AMOUNT(l_rcpt_tbl(j).AMOUNT_APPLIED,l_inv_curr_Code);
3539 IF l_currency_code <> l_inv_curr_code THEN
3540 IF l_inv_to_rct_rate is null THEN
3541 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
3542 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
3543 IF l_exchange_rate_type IS NULL THEN
3544 OKL_API.set_message( p_app_name => G_APP_NAME
3545 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
3546 );
3547 RAISE G_EXCEPTION_HALT_VALIDATION;
3548 ELSE
3549 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
3550 ,l_currency_code
3551 ,l_receipt_date
3552 ,l_exchange_rate_type
3553 );
3554
3555 IF l_conversion_rate IN (0,-1) THEN
3556
3557 -- Message Text: No exchange rate defined
3558 x_return_status := okl_api.G_RET_STS_ERROR;
3559 okl_api.set_message( p_app_name => G_APP_NAME,
3560 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
3561 RAISE G_EXCEPTION_HALT_VALIDATION;
3562 END IF;
3563 END IF;
3564 ELSE
3565 l_conversion_rate := l_inv_to_rct_rate;
3566 END IF;
3567 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
3568 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
3569 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
3570 ELSE
3571 l_rcpt_tbl(j).trans_to_receipt_rate := null;
3572 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
3573 END IF;
3574 l_inv_total_amt := l_inv_total_amt + l_rcpt_tbl(j).AMOUNT_APPLIED;
3575 EXIT WHEN (j = l_rcpt_tbl.LAST);
3576 j := j + 1;
3577 END LOOP;
3578 l_diff_amount := l_amount_app_to - l_inv_total_amt;
3579 if l_diff_amount > 0 then
3580 l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied := l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied + l_diff_amount;
3581 IF l_currency_code <> l_inv_curr_code THEN
3582 IF l_inv_to_rct_rate is null THEN
3583 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
3584 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
3585 IF l_exchange_rate_type IS NULL THEN
3586 OKL_API.set_message( p_app_name => G_APP_NAME
3587 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
3588 );
3589 RAISE G_EXCEPTION_HALT_VALIDATION;
3590 ELSE
3591 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
3592 ,l_currency_code
3593 ,l_receipt_date
3594 ,l_exchange_rate_type
3595 );
3596
3597 IF l_conversion_rate IN (0,-1) THEN
3598
3599 -- Message Text: No exchange rate defined
3600 x_return_status := okl_api.G_RET_STS_ERROR;
3601 okl_api.set_message( p_app_name => G_APP_NAME,
3602 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
3603 RAISE G_EXCEPTION_HALT_VALIDATION;
3604 END IF;
3605 END IF;
3606 ELSE
3607 l_conversion_rate := l_inv_to_rct_rate;
3608 END IF;
3609 l_rcpt_tbl(l_rcpt_tbl.LAST).trans_to_receipt_rate := l_conversion_rate;
3610 l_rcpt_tbl(l_rcpt_tbl.LAST).AMOUNT_APPLIED_FROM := l_rcpt_tbl(l_rcpt_tbl.LAST).AMOUNT_APPLIED * l_conversion_rate;
3611 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);
3612 ELSE
3613 l_rcpt_tbl(l_rcpt_tbl.LAST).trans_to_receipt_rate := null;
3614 l_rcpt_tbl(l_rcpt_tbl.LAST).AMOUNT_APPLIED_FROM := null;
3615 END IF;
3616 end if;
3617
3618
3619 END IF; -- bug 5221326
3620
3621 END IF; -- (3)
3622
3623 ELSE -- EXACT or OVERPAYMENT or TOLERANCE (2)
3624
3625 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3626 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'EXACT or OVERPAYMENT or TOLERANCE');
3627
3628 END IF;
3629 -- CREATE LINES TABLE
3630
3631 FOR i IN open_inv_tbl.FIRST..open_inv_tbl.LAST
3632 LOOP
3633 c_open_invs_rec := open_inv_tbl(i);
3634 EXIT WHEN l_amount_app_to = 0 OR l_amount_app_to IS NULL;
3635 j := j + 1;
3636 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invs_rec.invoice_due_remaining;
3637 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invs_rec.ar_invoice_number;
3638 l_rcpt_tbl(j).INVOICE_ID := c_open_invs_rec.ar_invoice_id;
3639 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invs_rec.ar_invoice_line_number;
3640 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invs_rec.ar_invoice_line_id;
3641 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
3642
3643 IF l_amount_app_to < l_rcpt_tbl(j).AMOUNT_APPLIED THEN
3644 -- TOLERANCE
3645 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
3646 l_amount_app_to := 0;
3647 ELSE
3648 l_amount_app_to := l_amount_app_to - l_rcpt_tbl(j).AMOUNT_APPLIED;
3649 END IF;
3650 IF l_currency_code <> l_inv_curr_code THEN
3651 IF l_inv_to_rct_rate is null THEN
3652 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
3653 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
3654 IF l_exchange_rate_type IS NULL THEN
3655 OKL_API.set_message( p_app_name => G_APP_NAME
3656 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
3657 );
3658 RAISE G_EXCEPTION_HALT_VALIDATION;
3659 ELSE
3660 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
3661 ,l_currency_code
3662 ,l_receipt_date
3663 ,l_exchange_rate_type
3664 );
3665
3666 IF l_conversion_rate IN (0,-1) THEN
3667
3668 -- Message Text: No exchange rate defined
3669 x_return_status := okl_api.G_RET_STS_ERROR;
3670 okl_api.set_message( p_app_name => G_APP_NAME,
3671 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
3672 RAISE G_EXCEPTION_HALT_VALIDATION;
3673 END IF;
3674 END IF;
3675 ELSE
3676 l_conversion_rate := l_inv_to_rct_rate;
3677 END IF;
3678 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
3679 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
3680 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
3681 ELSE
3682 l_rcpt_tbl(j).trans_to_receipt_rate := null;
3683 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
3684 END IF;
3685 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3686 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
3687 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
3688 END IF;
3689 END LOOP;
3690 -- Apply the remaining balance as per the Cash Application Rule
3691 If l_over_payment In ('B','b') Then -- Onaccount --move cash to customer balances -OVP
3692 IF l_currency_code = l_inv_curr_code THEN
3693 l_onacc_amount:=l_amount_app_to;
3694 ELSE
3695 l_onacc_amount:= l_amount_app_to * l_conversion_rate;
3696 l_onacc_amount := GET_ROUNDED_AMOUNT(l_onacc_amount,l_currency_code);
3697 END IF;
3698 Elsif l_over_payment In ('F','f') Then --Unapply -- move cash to unapplied -OVP
3699 IF l_currency_code = l_inv_curr_code THEN
3700 l_unapply_amount:=l_amount_app_to;
3701 ELSE
3702 l_unapply_amount:= l_amount_app_to * l_conversion_rate;
3703 l_unapply_amount := GET_ROUNDED_AMOUNT(l_unapply_amount,l_currency_code);
3704 END IF;
3705 End If;
3706 END IF; -- under payment. (2)
3707
3708
3709 -- **********************************************
3710 -- Stream level cash application processing ENDS
3711 -- **********************************************
3712
3713 ELSE
3714
3715 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3716 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Per/Contract level cash application processing BEGINS');
3717
3718 END IF;
3719 -- ******************************************************
3720 -- Per/Contract level cash application processing BEGINS
3721 -- ******************************************************
3722
3723
3724 OPEN c_get_contract_num(l_cons_bill_id);
3725
3726 LOOP
3727
3728 FETCH c_get_contract_num INTO l_contract_id, l_contract_num;
3729
3730 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3731 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_contract_num : '||l_contract_num);
3732 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_amount_app_to : '||l_amount_app_to);
3733
3734 END IF;
3735 EXIT WHEN c_get_contract_num%NOTFOUND
3736 OR l_amount_app_to = 0
3737 OR l_amount_app_to IS NULL;
3738
3739 IF l_last_contract_id <> l_contract_id THEN -- added by bv
3740
3741 l_last_contract_id := l_contract_id; -- added by bv
3742
3743 IF l_contract_num IS NOT NULL THEN
3744
3745 OPEN c_cash_rle_id_csr (l_contract_id);
3746 FETCH c_cash_rle_id_csr INTO l_cau_id;
3747 CLOSE c_cash_rle_id_csr;
3748
3749 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3750 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cau_id : '||l_cau_id);
3751
3752 END IF;
3753 IF l_cau_id IS NOT NULL THEN
3754
3755 OPEN c_cash_rule_csr (l_cau_id);
3756 FETCH c_cash_rule_csr INTO l_cat_id
3757 ,l_tolerance
3758 ,l_days_past_quote_valid
3759 ,l_months_to_bill_ahead
3760 ,l_under_payment
3761 ,l_over_payment
3762 ,l_receipt_msmtch;
3763 CLOSE c_cash_rule_csr;
3764
3765 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3766 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_tolerance : '||l_tolerance);
3767
3768 END IF;
3769 IF l_tolerance IS NULL THEN
3770 Get_Default_Cash_App_Rule(
3771 l_org_id
3772 ,l_cat_id
3773 ,l_tolerance
3774 ,l_days_past_quote_valid
3775 ,l_months_to_bill_ahead
3776 ,l_under_payment
3777 ,l_over_payment
3778 ,l_receipt_msmtch);
3779 END IF;
3780
3781 ELSE -- use default rule
3782
3783 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3784 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cau_id is null, using default cash appln rule');
3785
3786 END IF;
3787 Get_Default_Cash_App_Rule(
3788 l_org_id
3789 ,l_cat_id
3790 ,l_tolerance
3791 ,l_days_past_quote_valid
3792 ,l_months_to_bill_ahead
3793 ,l_under_payment
3794 ,l_over_payment
3795 ,l_receipt_msmtch);
3796
3797 END IF;
3798
3799 -- get contract total
3800 l_cont_tot := 0;
3801 j := 0;
3802 FOR i IN open_inv_tbl.FIRST..open_inv_tbl.LAST
3803 LOOP
3804 IF open_inv_tbl(i).contract_number = l_contract_num THEN
3805 -- l_invoice_currency_code := c_open_invs_rec.currency_code;
3806 j := j + 1;
3807 open_inv_contract_tbl(j) := open_inv_tbl(i);
3808 l_cont_tot := l_cont_tot + open_inv_tbl(i).invoice_due_remaining;
3809 END IF;
3810 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3811 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cont_tot : '||l_cont_tot);
3812 END IF;
3813 END LOOP;
3814 j := 0;
3815
3816 IF NVL(l_cau_id, 0) = -1 THEN -- VR 07-Oct-2005 GE-20 Receipts - On Account CAR
3817 -- Receipt needs to be left as unapplied
3818 j := 1;
3819 l_rcpt_tbl(j).INVOICE_ID := NULL;
3820 l_rcpt_tbl(j).INVOICE_LINE_ID := NULL;
3821 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := NULL;
3822 l_rcpt_tbl(j).INVOICE_NUMBER := NULL;
3823 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
3824 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
3825
3826 ELSE
3827
3828 -- calculate tolerance
3829 IF l_cont_tot > l_amount_app_to THEN
3830 l_appl_tolerance := l_cont_tot * (1 - l_tolerance / 100);
3831 ELSE
3832 l_appl_tolerance := l_cont_tot;
3833 END IF;
3834
3835 -- Contract level cash application processing begins.
3836 -- *************************************************
3837 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3838 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Checking UNDERPAYMENT/OVERPAYMENT/MATCH');
3839 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cont_tot : '||l_cont_tot);
3840 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_amount_app_to : '||l_amount_app_to);
3841 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_appl_tolerance : '||l_appl_tolerance);
3842 END IF;
3843 IF l_cont_tot > l_amount_app_to AND l_appl_tolerance > l_amount_app_to THEN -- UNDERPAYMENT (2)
3844 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3845 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'UNDERPAYMENT');
3846 END IF;
3847
3848 IF l_under_payment In ('U','u') THEN -- Unapply underpayment (3)
3849 IF l_currency_code = l_inv_curr_code THEN
3850 l_unapply_amount:=l_amount_app_to;
3851 ELSE
3852 l_unapply_amount:= l_amount_app_to * l_conversion_rate;
3853 l_unapply_amount := GET_ROUNDED_AMOUNT(l_unapply_amount,l_currency_code);
3854 END IF;
3855 ELSIF l_under_payment IN ('T','t') THEN -- ORDERED (3)
3856
3857 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3858 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ORDERED');
3859 END IF;
3860 OPEN c_stream_alloc (l_ordered, l_cat_id);
3861 LOOP
3862 FETCH c_stream_alloc INTO l_sty_id;
3863 EXIT WHEN c_stream_alloc%NOTFOUND
3864 OR l_amount_app_to = 0
3865 OR l_amount_app_to IS NULL;
3866
3867 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
3868 LOOP
3869 c_open_invs_rec := open_inv_contract_tbl(i);
3870 EXIT WHEN l_amount_app_to = 0 OR l_amount_app_to IS NULL;
3871 IF c_open_invs_rec.stream_type_id = l_sty_id THEN
3872 j := j + 1;
3873
3874 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invs_rec.invoice_due_remaining;
3875 l_rcpt_tbl(j).INVOICE_ID := c_open_invs_rec.ar_invoice_id;
3876 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invs_rec.ar_invoice_number;
3877 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
3878 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invs_rec.ar_invoice_line_id;
3879 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invs_rec.ar_invoice_line_number;
3880
3881 IF l_rcpt_tbl(j).AMOUNT_APPLIED > l_amount_app_to THEN
3882 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
3883 l_amount_app_to := 0;
3884 ELSE
3885 l_amount_app_to := l_amount_app_to - l_rcpt_tbl(j).AMOUNT_APPLIED;
3886 END IF;
3887
3888 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
3889
3890 --j := j + 1;
3891 IF l_currency_code <> l_inv_curr_code THEN
3892 IF l_inv_to_rct_rate is null THEN
3893 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
3894 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
3895 IF l_exchange_rate_type IS NULL THEN
3896 OKL_API.set_message( p_app_name => G_APP_NAME
3897 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
3898 );
3899 RAISE G_EXCEPTION_HALT_VALIDATION;
3900 ELSE
3901 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
3902 ,l_currency_code
3903 ,l_receipt_date
3904 ,l_exchange_rate_type
3905 );
3906
3907 IF l_conversion_rate IN (0,-1) THEN
3908
3909 -- Message Text: No exchange rate defined
3910 x_return_status := okl_api.G_RET_STS_ERROR;
3911 okl_api.set_message( p_app_name => G_APP_NAME,
3912 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
3913 RAISE G_EXCEPTION_HALT_VALIDATION;
3914 END IF;
3915 END IF;
3916 ELSE
3917 l_conversion_rate := l_inv_to_rct_rate;
3918 END IF;
3919 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
3920 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
3921 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
3922 ELSE
3923 l_rcpt_tbl(j).trans_to_receipt_rate := null;
3924 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
3925 END IF;
3926 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3927 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
3928 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
3929 END IF;
3930 END IF;
3931 END LOOP;
3932 END LOOP;
3933 CLOSE c_stream_alloc;
3934
3935 ELSIF l_under_payment IN ('P','p') THEN -- PRO RATE (3)
3936
3937 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3938 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'PRO RATE');
3939 END IF;
3940 l_first_prorate_rec_j := j + 1;
3941
3942 -- obtain all the streams that are part of the pro rate user defined list.
3943
3944 FOR c_stream_alloc_rec IN c_stream_alloc (l_prorate, l_cat_id)
3945 LOOP
3946
3947 l_sty_id := c_stream_alloc_rec.sty_id;
3948 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
3949 LOOP
3950 c_open_invs_rec := open_inv_contract_tbl(i);
3951 IF c_open_invs_rec.stream_type_id = l_sty_id THEN
3952 j := j + 1;
3953
3954 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invs_rec.invoice_due_remaining;
3955 l_rcpt_tbl(j).INVOICE_ID := c_open_invs_rec.ar_invoice_id;
3956 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invs_rec.ar_invoice_number;
3957 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
3958 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invs_rec.ar_invoice_line_id;
3959 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invs_rec.ar_invoice_line_number;
3960 IF l_currency_code <> l_inv_curr_code THEN
3961 IF l_inv_to_rct_rate is null THEN
3962 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
3963 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
3964 IF l_exchange_rate_type IS NULL THEN
3965 OKL_API.set_message( p_app_name => G_APP_NAME
3966 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
3967 );
3968 RAISE G_EXCEPTION_HALT_VALIDATION;
3969 ELSE
3970 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
3971 ,l_currency_code
3972 ,l_receipt_date
3973 ,l_exchange_rate_type
3974 );
3975
3976 IF l_conversion_rate IN (0,-1) THEN
3977
3978 -- Message Text: No exchange rate defined
3979 x_return_status := okl_api.G_RET_STS_ERROR;
3980 okl_api.set_message( p_app_name => G_APP_NAME,
3981 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
3982 RAISE G_EXCEPTION_HALT_VALIDATION;
3983 END IF;
3984 END IF;
3985 ELSE
3986 l_conversion_rate := l_inv_to_rct_rate;
3987 END IF;
3988 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
3989 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
3990 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
3991 ELSE
3992 l_rcpt_tbl(j).trans_to_receipt_rate := null;
3993 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
3994 END IF;
3995 l_pro_rate_inv_total := l_pro_rate_inv_total + l_rcpt_tbl(j).AMOUNT_APPLIED;
3996
3997 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3998 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
3999 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
4000 END IF;
4001 END IF;
4002 END LOOP; -- c_open_invs
4003 END LOOP; -- c_stream_alloc
4004
4005 -- Calc Pro Ration
4006 -- only if total amount of prorated invoices is greater than receipt
4007
4008 IF l_pro_rate_inv_total IS NULL OR l_pro_rate_inv_total = 0 THEN
4009
4010 -- Message Text: No prorated transaction types
4011 x_return_status := OKC_API.G_RET_STS_ERROR;
4012 OKC_API.set_message( p_app_name => G_APP_NAME,
4013 p_msg_name => 'OKL_BPD_NO_PRORATED_STRMS');
4014
4015 RAISE G_EXCEPTION_HALT_VALIDATION;
4016
4017 END IF;
4018
4019 IF (l_pro_rate_inv_total > l_amount_app_to) THEN
4020
4021 j := l_first_prorate_rec_j;
4022
4023 l_temp_val := l_amount_app_to / l_pro_rate_inv_total;
4024
4025 LOOP
4026 l_rcpt_tbl(j).AMOUNT_APPLIED := l_temp_val * l_rcpt_tbl(j).AMOUNT_APPLIED;
4027 l_rcpt_tbl(j).AMOUNT_APPLIED:=GET_ROUNDED_AMOUNT(l_rcpt_tbl(j).AMOUNT_APPLIED,l_inv_curr_Code);
4028 IF l_currency_code <> l_inv_curr_code THEN
4029 IF l_inv_to_rct_rate is null THEN
4030 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
4031 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
4032 IF l_exchange_rate_type IS NULL THEN
4033 OKL_API.set_message( p_app_name => G_APP_NAME
4034 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
4035 );
4036 RAISE G_EXCEPTION_HALT_VALIDATION;
4037 ELSE
4038 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
4039 ,l_currency_code
4040 ,l_receipt_date
4041 ,l_exchange_rate_type
4042 );
4043
4044 IF l_conversion_rate IN (0,-1) THEN
4045
4046 -- Message Text: No exchange rate defined
4047 x_return_status := okl_api.G_RET_STS_ERROR;
4048 okl_api.set_message( p_app_name => G_APP_NAME,
4049 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
4050 RAISE G_EXCEPTION_HALT_VALIDATION;
4051 END IF;
4052 END IF;
4053 ELSE
4054 l_conversion_rate := l_inv_to_rct_rate;
4055 END IF;
4056 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
4057 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
4058 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
4059 ELSE
4060 l_rcpt_tbl(j).trans_to_receipt_rate := null;
4061 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
4062 END IF;
4063 l_inv_total_amt := l_inv_total_amt + l_rcpt_tbl(j).AMOUNT_APPLIED;
4064 EXIT WHEN (j = l_rcpt_tbl.LAST);
4065 j := j + 1;
4066 END LOOP;
4067 l_diff_amount := l_amount_app_to - l_inv_total_amt;
4068 if l_diff_amount > 0 then
4069 l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied := l_rcpt_tbl(l_rcpt_tbl.LAST).amount_Applied + l_diff_amount;
4070 end if;
4071 END IF;
4072
4073 END IF; -- (3)
4074
4075 ELSE -- EXACT or OVERPAYMENT or TOLERANCE (2)
4076 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4077 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'EXACT or OVERPAYMENT or TOLERANCE');
4078
4079
4080 END IF;
4081 -- CREATE LINES TABLE
4082 FOR i IN open_inv_contract_tbl.FIRST..open_inv_contract_tbl.LAST
4083 LOOP
4084 c_open_invs_rec := open_inv_contract_tbl(i);
4085 EXIT WHEN l_amount_app_to = 0 OR l_amount_app_to IS NULL;
4086
4087 j := j + 1;
4088 l_rcpt_tbl(j).AMOUNT_APPLIED := c_open_invs_rec.invoice_due_remaining;
4089 l_rcpt_tbl(j).INVOICE_ID := c_open_invs_rec.ar_invoice_id;
4090 l_rcpt_tbl(j).INVOICE_NUMBER := c_open_invs_rec.ar_invoice_number;
4091 l_rcpt_tbl(j).INVOICE_CURRENCY_CODE := l_inv_curr_Code;
4092 l_rcpt_tbl(j).INVOICE_LINE_ID := c_open_invs_rec.ar_invoice_line_id;
4093 l_rcpt_tbl(j).INVOICE_LINE_NUMBER := c_open_invs_rec.ar_invoice_line_number;
4094
4095 IF l_amount_app_to < l_rcpt_tbl(j).AMOUNT_APPLIED THEN
4096 -- TOLERANCE
4097 --l_xcav_tbl(i).AMOUNT_APPLIED := l_amount_app_to;
4098 l_rcpt_tbl(j).AMOUNT_APPLIED := l_amount_app_to;
4099
4100 l_amount_app_to := 0;
4101
4102 ELSE
4103
4104 l_amount_app_to := l_amount_app_to - l_rcpt_tbl(j).AMOUNT_APPLIED;
4105
4106 END IF;
4107 IF l_currency_code <> l_inv_curr_code THEN
4108 IF l_inv_to_rct_rate is null THEN
4109 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(p_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
4110 log_debug('l_exchange_rate_type = '||l_exchange_rate_type);
4111 IF l_exchange_rate_type IS NULL THEN
4112 OKL_API.set_message( p_app_name => G_APP_NAME
4113 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
4114 );
4115 RAISE G_EXCEPTION_HALT_VALIDATION;
4116 ELSE
4117 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_inv_curr_code
4118 ,l_currency_code
4119 ,l_receipt_date
4120 ,l_exchange_rate_type
4121 );
4122
4123 IF l_conversion_rate IN (0,-1) THEN
4124
4125 -- Message Text: No exchange rate defined
4126 x_return_status := okl_api.G_RET_STS_ERROR;
4127 okl_api.set_message( p_app_name => G_APP_NAME,
4128 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
4129 RAISE G_EXCEPTION_HALT_VALIDATION;
4130 END IF;
4131 END IF;
4132 ELSE
4133 l_conversion_rate := l_inv_to_rct_rate;
4134 END IF;
4135 l_rcpt_tbl(j).trans_to_receipt_rate := l_conversion_rate;
4136 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := l_rcpt_tbl(j).AMOUNT_APPLIED * l_conversion_rate;
4137 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := arp_util.CurrRound(l_rcpt_tbl(j).AMOUNT_APPLIED_FROM,l_currency_code);
4138 ELSE
4139 l_rcpt_tbl(j).trans_to_receipt_rate := null;
4140 l_rcpt_tbl(j).AMOUNT_APPLIED_FROM := null;
4141 END IF;
4142 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4143 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).INVOICE_NUMBER : '||l_rcpt_tbl(j).INVOICE_NUMBER);
4144 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_rcpt_tbl(j).AMOUNT_APPLIED : '||l_rcpt_tbl(j).AMOUNT_APPLIED);
4145
4146 END IF;
4147 END LOOP;
4148 -- Apply the remaining balance as per the Cash Application Rule
4149 If l_over_payment In ('B','b') Then -- Onaccount --move cash to customer balances -OVP
4150 IF l_currency_code = l_inv_curr_code THEN
4151 l_onacc_amount:=l_amount_app_to;
4152 ELSE
4153 l_onacc_amount:= l_amount_app_to * l_conversion_rate;
4154 l_onacc_amount := GET_ROUNDED_AMOUNT(l_onacc_amount,l_currency_code);
4155 END IF;
4156 Elsif l_over_payment In ('F','f') Then --Unapply -- move cash to unapplied -OVP
4157 IF l_currency_code = l_inv_curr_code THEN
4158 l_unapply_amount:=l_amount_app_to;
4159 ELSE
4160 l_unapply_amount:= l_amount_app_to * l_conversion_rate;
4161 l_unapply_amount := GET_ROUNDED_AMOUNT(l_unapply_amount,l_currency_code);
4162 END IF;
4163 End If;
4164
4165 END IF; -- under payment.
4166
4167 END IF; -- VR 07-Oct-2005 GE-20 Receipts - On Account CAR
4168
4169 -- Contract level cash application processing ends.
4170 -- *************************************************
4171
4172 END IF;
4173
4174 ELSE -- added by bv
4175
4176 NULL; -- added by bv
4177
4178 END IF; -- added by bv
4179
4180 END LOOP;
4181 CLOSE c_get_contract_num;
4182
4183 END IF; -- l_same_date/l_same_cash_rule
4184
4185 END IF;
4186
4187
4188
4189 -- END OKL CASH APPLICATION.
4190
4191
4192 --Get grouped application table (in the form of invoice header > multiple invoice lines table)
4193 GET_APPLICATIONS( p_rcpt_tbl => l_rcpt_tbl
4194 ,x_appl_tbl => l_appl_tbl);
4195 x_appl_tbl := l_appl_tbl;
4196
4197 l_tot_amt_app_from := 0;
4198 IF x_appl_tbl.COUNT > 0 THEN
4199 IF x_appl_tbl(x_appl_tbl.FIRST).inv_hdr_rec.trans_to_receipt_rate IS NOT NULL THEN
4200 FOR ll IN x_appl_tbl.FIRST..x_appl_tbl.LAST LOOP
4201 l_tot_amt_app_from := l_tot_amt_app_from + nvl(x_appl_tbl(ll).inv_hdr_rec.amount_applied_from,0);
4202 END LOOP;
4203 IF (l_onacc_amount + l_unapply_amount + l_tot_amt_app_from ) <> l_amount_app_from THEN
4204 l_diff_amount := l_amount_app_from - (l_onacc_amount + l_unapply_amount + l_tot_amt_app_from );
4205 log_debug('l_diff_amount = '||l_diff_amount);
4206 IF nvl(l_onacc_amount,0) <> 0 THEN
4207 l_onacc_amount := l_onacc_amount + l_diff_amount;
4208 log_debug('l_onacc_amount = '||l_onacc_amount);
4209 ELSIF nvl(l_unapply_amount,0) <> 0 THEN
4210 l_unapply_amount := l_unapply_amount + l_diff_amount;
4211 END IF;
4212 END IF;
4213 END IF;
4214 END IF;
4215 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4216 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 ...');
4217
4218
4219 END IF;
4220 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4221
4222 x_onacc_amount:= l_onacc_amount ;
4223 x_unapply_amount:= l_unapply_amount;
4224
4225 EXCEPTION
4226
4227 WHEN OTHERS THEN
4228 x_appl_tbl.DELETE;
4229 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
4230 x_msg_count := l_msg_count;
4231 x_msg_data := l_msg_data;
4232
4233 END auto_cashapp_for_consinv;
4234
4235 FUNCTION GET_ROUNDED_AMOUNT( p_amount_to_round IN NUMBER
4236 ,p_currency_code IN VARCHAR2)
4237 RETURN NUMBER
4238 AS
4239 BEGIN
4240 -- RETURN( arpcurr.CurrRound( p_amount_to_round, p_currency_code ) );
4241 RETURN okl_accounting_util.round_amount(p_amount_to_round,p_currency_code);
4242 EXCEPTION
4243 WHEN OTHERS THEN
4244 RETURN 0;
4245 END GET_ROUNDED_AMOUNT;
4246
4247 END OKL_AUTO_CASH_APPL_RULES_PVT;