[Home] [Help]
PACKAGE BODY: APPS.OKL_RECEIPTS_PVT
Source
1 PACKAGE BODY OKL_RECEIPTS_PVT AS
2 /* $Header: OKLRRCTB.pls 120.39.12010000.3 2009/03/13 11:40:45 racheruv ship $ */
3 ---------------------------------------------------------------------------
4 -- GLOBAL DATASTRUCTURES
5 ---------------------------------------------------------------------------
6 TYPE rcpt_curr_conv_rec_type IS RECORD (
7 receipt_currency_code AR_CASH_RECEIPTS.CURRENCY_CODE%TYPE ,
8 receipt_date AR_CASH_RECEIPTS.RECEIPT_DATE%TYPE ,
9 currency_conv_date AR_CASH_RECEIPTS.EXCHANGE_DATE%TYPE ,
10 currency_conv_type AR_CASH_RECEIPTS.EXCHANGE_RATE_TYPE%TYPE ,
11 currency_conv_rate NUMBER ,
12 x_return_status VARCHAR2(1)
13 );
14
15 SUBTYPE line_appl_tbl_type IS okl_auto_cash_appl_rules_pvt.okl_appl_dtls_tbl_type;
16 SUBTYPE llca_tbl_type IS okl_auto_cash_appl_rules_pvt.okl_inv_line_tbl_type;
17 ---------------------------------------------------------------------------
18 -- GLOBAL VARIABLES
19 ---------------------------------------------------------------------------
20 ---------------------------------------------------------------------------
21 -- Procedures and Functions
22 ---------------------------------------------------------------------------
23
24 PROCEDURE log_debug(p_message IN VARCHAR2) IS
25 BEGIN
26 -- dbms_output.put_line(p_message);
27 -- debug_proc('ssy'||p_message);
28 NULL;
29 END log_debug;
30
31 procedure populate_receipt_rec(p_rcpt_rec IN OUT NOCOPY rcpt_rec_type) IS
32 CURSOR c_rec_dtls(cp_cash_receipt_id IN NUMBER ) IS
33 SELECT cr.amount,
34 cr.currency_code,
35 cr.receipt_number,
36 cr.receipt_date,
37 crh.gl_date
38 FROM ar_Cash_receipts_all cr, ar_cash_receipt_history_all crh
39 where cr.cash_Receipt_id = cp_cash_receipt_id
40 and cr.cash_receipt_id = crh.cash_receipt_id
41 and crh.current_record_flag = 'Y';
42 BEGIN
43 OPEN c_rec_dtls(p_rcpt_rec.cash_receipt_id);
44 FETCH c_rec_dtls INTO
45 p_rcpt_rec.amount,p_rcpt_rec.currency_code,p_rcpt_rec.receipt_number,p_rcpt_rec.receipt_date,p_rcpt_rec.gl_date;
46 CLOSE c_rec_dtls;
47 END populate_receipt_rec;
48
49 FUNCTION validate_gl_date(p_gl_date IN DATE)
50 RETURN VARCHAR2 IS
51
52 l_applic_month VARCHAR2(10);
53 l_gl_month VARCHAR2(10);
54 l_counter VARCHAR2(1);
55 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
56 l_gl_date DATE;
57
58 --get gl date from open accounting period
59 CURSOR c_get_gl_date(cp_date IN DATE) IS SELECT * from (
60 SELECT end_date gl_date, 1 Counter
61 FROM gl_period_statuses
62 WHERE application_id = 222
63 AND ledger_id = okl_accounting_util.get_set_of_books_id
64 AND closing_status IN ('F','O')
65 AND trunc(cp_date) between start_date and end_date
66 AND adjustment_period_flag = 'N'
67 UNION
68 SELECT MAX(end_date) gl_date, 2 Counter
69 FROM gl_period_statuses
70 WHERE application_id = 222
71 AND ledger_id = okl_accounting_util.get_set_of_books_id
72 AND closing_status IN ('F','O')
73 AND end_date <= trunc(cp_date)
74 AND adjustment_period_flag = 'N'
75 UNION
76 SELECT MIN(start_date) gl_date, 3 Counter
77 FROM gl_period_statuses
78 WHERE application_id = 222
79 AND ledger_id = okl_accounting_util.get_set_of_books_id
80 AND closing_status IN ('F','O')
81 AND start_date >= trunc(cp_date)
82 AND adjustment_period_flag = 'N'
83 )
84 where gl_date is not null
85 order by counter;
86
87 BEGIN
88
89 OPEN c_get_gl_date(p_gl_date);
90 FETCH c_get_gl_date INTO l_gl_date, l_counter;
91
92 log_debug('c_get_gl_date ');
93
94 IF c_get_gl_date%NOTFOUND THEN
95 CLOSE c_get_gl_date;
96
97 OKC_API.set_message( p_app_name => G_APP_NAME,
98 p_msg_name =>'OKL_BPD_GL_PERIOD_ERROR',
99 p_token1 => 'TRX_DATE',
100 p_token1_value => TRUNC(p_gl_date));
101
102 l_return_status := OKC_API.G_RET_STS_ERROR;
103 RAISE G_EXCEPTION_HALT_VALIDATION;
104 END IF;
105 CLOSE c_get_gl_date;
106
107 RETURN l_return_status;
108
109 EXCEPTION
110
111 WHEN G_EXCEPTION_HALT_VALIDATION THEN
112 l_return_status := OKL_API.G_RET_STS_ERROR;
113 RETURN l_return_status;
114
115 WHEN OTHERS THEN
116 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
117 Okl_api.set_message( p_app_name => g_app_name
118 , p_msg_name => g_unexpected_error
119 , p_token1 => g_sqlcode_token
120 , p_token1_value => SQLCODE
121 , p_token2 => g_sqlerrm_token
122 , p_token2_value => SQLERRM
123 ) ;
124 RETURN l_return_status;
125
126 END validate_gl_date;
127
128 FUNCTION validate_amount_to_apply(p_amt_to_apply IN nUMBER)
129 RETURN VARCHAR2 is
130
131 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
132
133 BEGIN
134 log_debug( 'In amount to apply');
135 log_debug( 'In amount to apply: amount to appli is'||p_amt_to_apply);
136 IF p_amt_to_apply < 0 THEN
137 OKL_API.set_message( p_app_name => G_APP_NAME,
138 p_msg_name => 'OKL_RCPT_AMT_TO_APPLY_GT_ZERO');
139
140 RAISE G_EXCEPTION_HALT_VALIDATION;
141 END IF;
142
143 RETURN l_return_status;
144
145 EXCEPTION
146 WHEN G_EXCEPTION_HALT_VALIDATION THEN
147 l_return_status := OKL_API.G_RET_STS_ERROR;
148 RETURN l_return_status;
149
150 WHEN OTHERS THEN
151 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
152 OKL_API.Set_Message(p_app_name => G_APP_NAME,
153 p_msg_name => G_UNEXPECTED_ERROR,
154 p_token1 => G_SQLCODE_TOKEN,
155 p_token1_value => SQLCODE,
156 p_token2 => G_SQLERRM_TOKEN,
157 p_token2_value => SQLERRM);
158 RETURN l_return_status;
159
160 END validate_amount_to_apply;
161
162 FUNCTION validate_cash_receipt_id(p_cash_receipt_id IN NUMBER)
163 RETURN VARCHAR2 is
164
165 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
166 l_cash_receipt_flag NUMBER DEFAULT NULL;
167
168 CURSOR csr_validate_cash_rcpt_id(l_cash_rcpt_id IN NUMBER) IS
169 SELECT '1'
170 FROM AR_CASH_RECEIPTS_ALL
171 WHERE cash_receipt_id = l_cash_rcpt_id;
172
173 BEGIN
174 OPEN csr_validate_cash_rcpt_id(p_cash_receipt_id);
175 FETCH csr_validate_cash_rcpt_id INTO l_cash_receipt_flag;
176 CLOSE csr_validate_cash_rcpt_id;
177
178 IF l_cash_receipt_flag IS NULL THEN
179 OKL_API.set_message( p_app_name => G_APP_NAME,
180 p_msg_name => 'OKL_RCPT_INV_CASH_RCPT');
181 RAISE G_EXCEPTION_HALT_VALIDATION;
182 END IF;
183
184 RETURN l_return_status;
185
186 EXCEPTION
187 WHEN G_EXCEPTION_HALT_VALIDATION THEN
188 l_return_status := OKL_API.G_RET_STS_ERROR;
189 RETURN l_return_status;
190
191 WHEN OTHERS THEN
192 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
193 OKL_API.Set_Message(p_app_name => G_APP_NAME,
194 p_msg_name => G_UNEXPECTED_ERROR,
195 p_token1 => G_SQLCODE_TOKEN,
196 p_token1_value => SQLCODE,
197 p_token2 => G_SQLERRM_TOKEN,
198 p_token2_value => SQLERRM);
199 RETURN l_return_status;
200
201 END validate_cash_receipt_id;
202
203 FUNCTION validate_receipt_attributes(p_rcpt_rec IN rcpt_rec_type)
204 RETURN VARCHAR2 IS
205
206 l_currency_code okl_k_headers_full_v.currency_code%type;
207 l_customer_id OKL_TRX_CSH_RECEIPT_V.ILE_id%TYPE DEFAULT p_rcpt_rec.customer_id;
208 l_customer_num HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE DEFAULT p_rcpt_rec.customer_number;
209 l_contract_id OKC_K_HEADERS_V.ID%TYPE DEFAULT NULL;
210 l_currency_conv_type OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_TYPE%TYPE DEFAULT p_rcpt_rec.exchange_rate_type;
211 l_currency_conv_date OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_DATE%TYPE DEFAULT p_rcpt_rec.exchange_date;
212 l_currency_conv_rate OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE%TYPE DEFAULT p_rcpt_rec.exchange_rate;
213 l_conversion_rate GL_DAILY_RATES_V.CONVERSION_RATE%TYPE DEFAULT 0;
214 l_functional_conversion_rate GL_DAILY_RATES_V.CONVERSION_RATE%TYPE DEFAULT 0;
215 l_inverse_conversion_rate GL_DAILY_RATES_V.INVERSE_CONVERSION_RATE%TYPE DEFAULT 0;
216 l_functional_currency OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL;
217 l_receipt_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT p_rcpt_rec.currency_code;
218 l_irm_id OKL_TRX_CSH_RECEIPT_V.IRM_ID%TYPE DEFAULT p_rcpt_rec.receipt_method_id;
219 l_check_number OKL_TRX_CSH_RECEIPT_V.CHECK_NUMBER%TYPE DEFAULT p_rcpt_rec.receipt_number;
220 l_rcpt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT p_rcpt_rec.amount;
221 l_converted_receipt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT NULL;
222 l_rcpt_date OKL_TRX_CSH_RECEIPT_V.DATE_EFFECTIVE%TYPE DEFAULT TRUNC(p_rcpt_rec.receipt_date);
223 l_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE DEFAULT p_rcpt_rec.gl_date;
224 l_org_id OKL_TRX_CSH_RECEIPT_V.ORG_ID%TYPE DEFAULT p_rcpt_rec.org_id;
225 l_dup_rcpt_flag NUMBER DEFAULT NULL;
226 x_return_status VARCHAR2(1) DEFAULT OKL_API.G_RET_STS_SUCCESS;
227 l_api_name CONSTANT VARCHAR2(30) := 'validate_receipt_attributes';
228 l_remittance_bank_account_id NUMBER DEFAULT p_rcpt_rec.remittance_bank_account_id;
229
230 l_rcpt_rec rcpt_rec_type := p_rcpt_rec;
231
232
233 BEGIN
234 log_debug('validate_receipt_attributes start');
235 l_functional_currency := okl_accounting_util.get_func_curr_code;
236
237 -- check for mandatory fields
238
239 IF l_receipt_currency_code IS NULL OR
240 l_receipt_currency_code = OKC_API.G_MISS_CHAR OR
241 l_irm_id IS NULL OR
242 l_irm_id = OKC_API.G_MISS_NUM OR
243 l_rcpt_date IS NULL OR
244 l_rcpt_date = OKC_API.G_MISS_DATE OR
245 l_gl_date IS NULL OR
246 l_gl_date = OKC_API.G_MISS_DATE OR
247 l_org_id IS NULL OR
248 l_org_id = OKC_API.G_MISS_NUM OR
249 l_rcpt_amount = 0 OR
250 l_rcpt_amount = OKC_API.G_MISS_NUM OR
251 l_remittance_bank_account_id = OKC_API.G_MISS_NUM OR
252 l_remittance_bank_account_id IS NULL THEN
253
254 -- Message Text: Please enter all mandatory fields
255 x_return_status := OKC_API.G_RET_STS_ERROR;
256 OKC_API.set_message( p_app_name => G_APP_NAME,
257 p_msg_name =>'OKL_BPD_MISSING_FIELDS');
258
259 RAISE G_EXCEPTION_HALT_VALIDATION;
260 -- Begin - varangan - Bug#6353486 - Sprint3 -Receipts
261 Elsif ((l_customer_id IS NULL OR l_customer_id = OKL_API.G_MISS_NUM ) AND
262 (l_customer_num IS NULL OR l_customer_num = okl_api.g_miss_char) AND
263 (l_rcpt_rec.create_mode <> 'UNAPPLIED' ) ) then
264
265 -- Message Text: A value must be entered for - COL_NAME.
266 x_return_status := OKC_API.G_RET_STS_ERROR;
267 OKC_API.set_message( p_app_name => G_APP_NAME,
268 p_msg_name =>'OKL_LLA_INCOMPLETE_RULE',
269 p_token1 => 'COL_NAME',
270 p_token1_value => 'Customer Name');
271 RAISE G_EXCEPTION_HALT_VALIDATION;
272 -- End - varangan - Bug#6353486 - Sprint3 -Receipts
273
274 END IF;
275 log_debug('Mandatory fieldss validations done');
276 -- Check for exceptions
277 IF l_rcpt_amount = 0 OR l_rcpt_amount IS NULL THEN
278 -- Message Text: The receipt cannot have a value of zero
279 x_return_status := OKC_API.G_RET_STS_ERROR;
280 OKC_API.set_message( p_app_name => G_APP_NAME,
281 p_msg_name => 'OKL_BPD_ZERO_RECEIPT');
282 RAISE G_EXCEPTION_HALT_VALIDATION;
283 END IF;
284 log_debug('Receipt Amount validations are done');
285
286 x_return_status := validate_gl_date(l_gl_date);
287
288 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
289 RAISE G_EXCEPTION_HALT_VALIDATION;
290 END IF;
291
292 log_debug('GL_DATE validations are done : ' || x_return_status);
293
294 RETURN x_return_status;
295 log_debug('validate_receipt_attributes end');
296 EXCEPTION
297 WHEN G_EXCEPTION_HALT_VALIDATION THEN
298 x_return_status := OKL_API.G_RET_STS_ERROR;
299 RETURN x_return_status;
300
301 WHEN OTHERS THEN
302 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
303 Okl_api.set_message( p_app_name => g_app_name
304 , p_msg_name => g_unexpected_error
305 , p_token1 => g_sqlcode_token
306 , p_token1_value => SQLCODE
307 , p_token2 => g_sqlerrm_token
308 , p_token2_value => SQLERRM
309 ) ;
310 RETURN x_return_status;
311 END validate_receipt_attributes;
312
313 FUNCTION validate_currency_conv_params(p_curr_conv_rec IN rcpt_curr_conv_rec_type)
314 RETURN rcpt_curr_conv_rec_type IS
315
316 l_currency_conv_type OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_TYPE%TYPE DEFAULT p_curr_conv_rec.currency_conv_type;
317 l_currency_conv_date OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_DATE%TYPE DEFAULT p_curr_conv_rec.currency_conv_date;
318 l_currency_conv_rate OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE%TYPE DEFAULT p_curr_conv_rec.currency_conv_rate;
319 l_functional_conversion_rate GL_DAILY_RATES_V.CONVERSION_RATE%TYPE DEFAULT 0;
320 l_inverse_conversion_rate GL_DAILY_RATES_V.INVERSE_CONVERSION_RATE%TYPE DEFAULT 0;
321 l_functional_currency OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL;
322 l_receipt_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT p_curr_conv_rec.receipt_currency_code;
323 x_rcpt_curr_conv_rec rcpt_curr_conv_rec_type;
324 l_return_status VARCHAR2(1);
325 l_rcpt_date AR_CASH_RECEIPTS.RECEIPT_DATE%TYPE DEFAULT p_curr_conv_rec.receipt_date;
326
327 BEGIN
328 log_debug('validate_currency_conv_params start');
329 l_functional_currency := okl_accounting_util.get_func_curr_code;
330
331 x_rcpt_curr_conv_rec := p_curr_conv_rec;
332
333 log_debug('l_functional_currency = '||l_functional_currency);
334 log_debug('l_receipt_currency_code = '||l_receipt_currency_code);
335 log_debug('l_currency_conv_type = '||l_currency_conv_type);
336 log_debug('l_currency_conv_rate = '||l_currency_conv_rate);
337 log_debug('l_currency_conv_date = '||l_currency_conv_date);
338 log_debug('CCV - 1');
339 IF l_functional_currency <> l_receipt_currency_code AND
340 l_currency_conv_type IS NULL THEN
341 -- Message Text: Please enter a currency type.
342 l_return_status := OKC_API.G_RET_STS_ERROR;
343 OKC_API.set_message( p_app_name => G_APP_NAME,
344 p_msg_name => 'OKL_BPD_PLS_ENT_CUR_TYPE');
345 RAISE G_EXCEPTION_HALT_VALIDATION;
346 END IF;
347 log_debug('CCV - 2');
348 IF l_functional_currency = l_receipt_currency_code THEN
349 IF l_currency_conv_type IS NOT NULL OR
350 nvl(l_currency_conv_rate,0) <> '0' THEN
351
352 -- Message Text: Currency conversion values are not required when the receipt and invoice currency's are the same.
353 l_return_status := OKC_API.G_RET_STS_ERROR;
354 OKC_API.set_message( p_app_name => G_APP_NAME,
355 p_msg_name => 'OKL_BPD_SAME_CURRENCY');
356 RAISE G_EXCEPTION_HALT_VALIDATION;
357 END IF;
358 END IF;
359
360 IF l_functional_currency <> l_receipt_currency_code AND
361 l_currency_conv_type NOT IN ('User') THEN
362 log_debug('CCV - 3');
363 IF l_currency_conv_date IS NULL OR l_currency_conv_date = '' THEN
364 l_currency_conv_date := trunc(l_rcpt_date);
365 END IF;
366 /*IF l_currency_conv_type = 'CORPORATE' THEN
367 l_currency_conv_type := 'Corporate';
368 ELSE
369 l_currency_conv_type := 'Spot';
370 END IF;*/
371
372 l_functional_conversion_rate := okl_accounting_util.get_curr_con_rate( l_receipt_currency_code
373 ,l_functional_currency
374 ,l_currency_conv_date
375 ,l_currency_conv_type
376 );
377
378 l_inverse_conversion_rate := okl_accounting_util.get_curr_con_rate( l_functional_currency
379 ,l_receipt_currency_code
380 ,l_currency_conv_date
381 ,l_currency_conv_type
382 );
383
384 IF l_functional_conversion_rate IN (0,-1) THEN
385 --No exchange rate defined
386 l_return_status := OKC_API.G_RET_STS_ERROR;
387 OKC_API.set_message( p_app_name => G_APP_NAME,
388 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
389 RAISE G_EXCEPTION_HALT_VALIDATION;
390 END IF;
391
392 l_currency_conv_rate := null;
393
394 ELSIF l_functional_currency <> l_receipt_currency_code AND
395 l_currency_conv_type IN ('User') THEN
396 log_debug('CCV - 4');
397 IF l_currency_conv_rate IS NULL OR l_currency_conv_rate = '0' THEN
398 -- Message Text: No exchange rate defined for currency conversion type USER.
399 l_return_status := OKC_API.G_RET_STS_ERROR;
400 OKC_API.set_message( p_app_name => G_APP_NAME,
401 p_msg_name => 'OKL_BPD_USR_RTE_SUPPLIED');
402 RAISE G_EXCEPTION_HALT_VALIDATION;
403 ELSE
404 l_functional_conversion_rate := l_currency_conv_rate;
405 l_inverse_conversion_rate := l_functional_conversion_rate / 1;
406 END IF;
407
408 l_currency_conv_type := 'User';
409 l_currency_conv_date := trunc(SYSDATE);
410 l_currency_conv_rate := l_functional_conversion_rate;
411 ELSE
412 -- no currency conversion required
413 l_currency_conv_date := NULL;
414 l_currency_conv_type := NULL;
415 l_currency_conv_rate := NULL;
416 END IF;
417
418 x_rcpt_curr_conv_rec.receipt_currency_code := l_receipt_currency_code;
419 x_rcpt_curr_conv_rec.currency_conv_type := l_currency_conv_type;
420 x_rcpt_curr_conv_rec.currency_conv_date := l_currency_conv_date;
421 x_rcpt_curr_conv_rec.currency_conv_rate := l_currency_conv_rate;
422 x_rcpt_curr_conv_rec.x_return_status := okl_api.g_ret_sts_success;
423 RETURN x_rcpt_curr_conv_rec;
424 EXCEPTION
425
426 WHEN G_EXCEPTION_HALT_VALIDATION THEN
427 x_rcpt_curr_conv_rec.x_return_status := OKL_API.G_RET_STS_ERROR;
428 RETURN x_rcpt_curr_conv_rec;
429
430 WHEN OTHERS THEN
431 x_rcpt_curr_conv_rec.x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
432 Okl_api.set_message( p_app_name => g_app_name
433 , p_msg_name => g_unexpected_error
434 , p_token1 => g_sqlcode_token
435 , p_token1_value => SQLCODE
436 , p_token2 => g_sqlerrm_token
437 , p_token2_value => SQLERRM
438 ) ;
439 RETURN x_rcpt_curr_conv_rec;
440
441 END validate_currency_conv_params;
442
443 PROCEDURE validate_receipt_details
444 ( x_return_status OUT NOCOPY VARCHAR2,
445 p_rcpt_rec IN OUT NOCOPY RCPT_REC_TYPE
446 )
447 IS
448
449 l_rcpt_curr_conv_rec rcpt_curr_conv_rec_type;
450 x_rcpt_curr_conv_rec rcpt_curr_conv_rec_type;
451
452 BEGIN
453 log_debug('validate_receipt_details start +');
454 x_return_status := okl_api.g_ret_sts_success;
455 log_debug('p_rcpt_rec.cash_receipt_id = '||p_rcpt_rec.cash_receipt_id);
456 IF p_rcpt_rec.cash_receipt_id IS NULL THEN
457 --do create mode validations
458 log_debug('p_rcpt_rec.RECEIPT_NUMBER = '||p_rcpt_rec.RECEIPT_NUMBER);
459 IF p_rcpt_rec.RECEIPT_NUMBER IS NULL OR p_rcpt_rec.RECEIPT_NUMBER = OKL_API.G_MISS_CHAR THEN
460 p_rcpt_rec.RECEIPT_NUMBER := to_char(sysdate, 'MONDDYYYYHH24MISS');
461 END IF;
462 log_debug('calling validate_receipt_attributes');
463 x_return_status := validate_receipt_attributes(p_rcpt_rec);
464 log_debug('RETURN status FROM validate_receipt_attributes = '|| x_return_status);
465 IF x_return_status = OKL_API.G_RET_STS_SUCCESS THEN
466 l_rcpt_curr_conv_rec.receipt_currency_code := p_rcpt_rec.currency_code;
467 l_rcpt_curr_conv_rec.receipt_date := p_rcpt_rec.receipt_date;
468 l_rcpt_curr_conv_rec.currency_conv_date := p_rcpt_rec.exchange_date;
469 l_rcpt_curr_conv_rec.currency_conv_type := p_rcpt_rec.exchange_rate_type;
470 l_rcpt_curr_conv_rec.currency_conv_rate := p_rcpt_rec.exchange_rate;
471 log_debug('calling validate_currency_conv_params');
472 x_rcpt_curr_conv_rec := validate_currency_conv_params(l_rcpt_curr_conv_rec);
473 x_return_status := x_rcpt_curr_conv_rec.x_return_status;
474 log_debug('RETURN status FROM validate_currency_conv_params = '|| x_return_status);
475 p_rcpt_rec.exchange_date := x_rcpt_curr_conv_rec.currency_conv_date;
476 p_rcpt_rec.exchange_rate_type := x_rcpt_curr_conv_rec.currency_conv_type;
477 p_rcpt_rec.exchange_rate := x_rcpt_curr_conv_rec.currency_conv_rate;
478 IF p_rcpt_rec.customer_id = okl_api.g_miss_num THEN
479 p_rcpt_rec.customer_id := NULL;
480 END IF;
481 IF p_rcpt_rec.customer_number = okl_api.g_miss_char THEN
482 p_rcpt_rec.customer_number := NULL;
483 END IF;
484 ELSE
485 RAISE G_EXCEPTION_HALT_VALIDATION;
486 END IF;
487
488 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
489 RAISE G_EXCEPTION_HALT_VALIDATION;
490 END IF;
491
492 ELSE
493 --do update mode validations
494 log_debug('calling validate_cash_receipt_id');
495 x_return_status := validate_cash_receipt_id(p_rcpt_rec.cash_receipt_id);
496 log_debug('RETURN status FROM validate_cash_receipt_id = '|| x_return_status);
497 END IF;
498 log_debug('validate_receipt_details end -');
499 EXCEPTION
500
501 WHEN G_EXCEPTION_HALT_VALIDATION THEN
502 x_return_status := OKL_API.G_RET_STS_ERROR;
503
504 WHEN OTHERS THEN
505 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
506 Okl_api.set_message( p_app_name => g_app_name
507 , p_msg_name => g_unexpected_error
508 , p_token1 => g_sqlcode_token
509 , p_token1_value => SQLCODE
510 , p_token2 => g_sqlerrm_token
511 , p_token2_value => SQLERRM
512 ) ;
513
514 END validate_receipt_details;
515
516 PROCEDURE validate_receipt_applications
517 ( p_cash_rcpt_id IN NUMBER,
518 p_appl_tbl IN OUT NOCOPY line_appl_tbl_type,
519 P_onacc_amount IN NUMBER,
520 x_return_status OUT NOCOPY VARCHAR2
521 )
522 IS
523
524 l_appl_tbl line_appl_tbl_type;
525 l_line_orig_amount NUMBER DEFAULT 0;
526 l_orig_freight_amount NUMBER DEFAULT 0;
527 l_orig_charges_amount NUMBER DEFAULT 0;
528 l_total_freight_amount NUMBER DEFAULT 0;
529 l_total_charges_amount NUMBER DEFAULT 0;
530 l_total_outstanding_amount NUMBER DEFAULT 0;
531 l_app_amount NUMBER DEFAULT 0;
532 l_onacc_amount NUMBER DEFAULT P_onacc_amount;
533 l_rcpt_amount NUMBER DEFAULT 0;
534
535 -- added by dcshanmu for 6326279. Cross currency conversions begins
536 l_rcpt_date DATE DEFAULT NULL;
537 l_invoice_currency_code ar_cash_receipts.currency_code%TYPE DEFAULT NULL;
538 l_receipt_currency_code ar_cash_receipts.currency_code%TYPE DEFAULT NULL;
539 l_conversion_rate NUMBER DEFAULT 0;
540 l_converted_receipt_amount NUMBER DEFAULT 0;
541 l_receipt_amount NUMBER DEFAULT 0;
542
543 CURSOR c_fetch_receipt_date(p_cash_rcpt_id IN NUMBER) IS
544 select receipt_date
545 from ar_cash_receipts_all
546 where cash_receipt_id=p_cash_rcpt_id;
547
548 CURSOR c_fetch_inv_currency(p_customer_trx_line_id IN NUMBER) IS
549 select b.INVOICE_CURRENCY_CODE
550 from ra_customer_trx_lines_all a,
551 ra_customer_trx_all b
552 where a.customer_trx_line_id=p_customer_trx_line_id
553 and a.customer_trx_id = b.customer_trx_id;
554
555 CURSOR c_fetch_receipt_currency(p_cash_rcpt_id IN NUMBER) IS
556 select currency_code
557 from ar_cash_receipts_all
558 where cash_receipt_id=p_cash_rcpt_id;
559
560 -- added by dcshanmu for 6326279. Cross currency conversions ends
561
562 CURSOR c_get_app_amount(p_cash_receipt_id IN NUMBER) IS
563 SELECT Nvl(SUM(app_Line.Amount_Applied),0)
564 FROM ar_Receivable_Applications_All app_Line
565 WHERE app_Line.Cash_Receipt_Id = p_cash_receipt_id
566 AND app_Line.Status = 'APP'
567 AND app_Line.Application_Type = 'CASH';
568
569 CURSOR c_get_onacc_amount(p_cash_receipt_id IN NUMBER) IS
570 SELECT Nvl(SUM(onAcc_Line.Amount_Applied),0)
571 FROM ar_Receivable_Applications_All onAcc_Line
572 WHERE onAcc_Line.Cash_Receipt_Id = p_cash_receipt_id
573 AND onAcc_Line.Status = 'ONACC'
574 AND onAcc_Line.Application_Type = 'CASH';
575
576 CURSOR c_get_rcpt_amount(p_cash_receipt_id IN NUMBER) IS
577 SELECT Nvl(SUM(rcpt_Line.Amount),0)
578 FROM ar_Cash_Receipts_All rcpt_Line
579 WHERE rcpt_Line.Cash_Receipt_Id = p_cash_receipt_id;
580
581 CURSOR c_get_orig_freight_amount(p_customer_trx_id IN NUMBER) IS
582 SELECT NVL(SUM(AMOUNT_DUE_ORIGINAL),0)
583 FROM RA_CUSTOMER_TRX_LINES_ALL
584 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
585 AND LINE_TYPE = 'FREIGHT';
586
587 CURSOR c_get_orig_charges_amount(p_customer_trx_id IN NUMBER) IS
588 SELECT NVL(SUM(AMOUNT_DUE_ORIGINAL),0)
589 FROM RA_CUSTOMER_TRX_LINES_ALL
590 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
591 AND LINE_TYPE = 'CHARGES';
592
593 BEGIN
594 log_debug( 'In validate receipt applications');
595 l_appl_tbl := p_appl_tbl;
596
597 -- added by dcshanmu for 6326279. Cross currency conversions begins
598 OPEN c_fetch_receipt_date(p_cash_rcpt_id);
599 FETCH c_fetch_receipt_date INTO l_rcpt_date;
600 CLOSE c_fetch_receipt_date;
601
602 OPEN c_fetch_receipt_currency(p_cash_rcpt_id);
603 FETCH c_fetch_receipt_currency INTO l_receipt_currency_code;
604 CLOSE c_fetch_receipt_currency;
605 -- added by dcshanmu for 6326279. Cross currency conversions ends
606
607 FOR i in l_appl_tbl.FIRST..l_appl_tbl.LAST LOOP
608 IF l_appl_tbl(i).inv_hdr_rec.freight_amount IS NOT NULL THEN
609 OPEN c_get_orig_freight_amount(l_appl_tbl(i).inv_hdr_rec.invoice_id);
610 FETCH c_get_orig_freight_amount INTO l_orig_freight_amount;
611 CLOSE c_get_orig_freight_amount;
612 l_total_freight_amount := l_total_freight_amount + l_appl_tbl(i).inv_hdr_rec.freight_amount;
613 IF l_appl_tbl(i).inv_hdr_rec.freight_amount > l_orig_freight_amount THEN
614 OKL_API.set_message( p_app_name => G_APP_NAME,
615 p_msg_name => 'OKL_RCPT_LN_AMT_GT_OUTSTD_AMT',
616 p_token1 => 'AMT_TO_APPLY',
617 p_token1_value => l_appl_tbl(i).inv_hdr_rec.freight_amount,
618 p_token2 => 'OUTSTD_AMOUNT',
619 p_token2_value => l_orig_freight_amount);
620 RAISE G_EXCEPTION_HALT_VALIDATION;
621 END IF;
622 END IF;
623
624 IF l_appl_tbl(i).inv_hdr_rec.charges_amount IS NOT NULL THEN
625 OPEN c_get_orig_charges_amount(l_appl_tbl(i).inv_hdr_rec.invoice_id);
626 FETCH c_get_orig_charges_amount INTO l_orig_charges_amount;
627 CLOSE c_get_orig_charges_amount;
628 l_total_charges_amount := l_total_charges_amount + l_appl_tbl(i).inv_hdr_rec.charges_amount;
629 IF l_appl_tbl(i).inv_hdr_rec.charges_amount > l_orig_charges_amount THEN
630 OKL_API.set_message( p_app_name => G_APP_NAME,
631 p_msg_name => 'OKL_RCPT_LN_AMT_GT_OUTSTD_AMT',
632 p_token1 => 'AMT_TO_APPLY',
633 p_token1_value => l_appl_tbl(i).inv_hdr_rec.charges_amount,
634 p_token2 => 'OUTSTD_AMOUNT',
635 p_token2_value => l_orig_charges_amount);
636 RAISE G_EXCEPTION_HALT_VALIDATION;
637 END IF;
638 END IF;
639
640 IF l_appl_tbl(i).inv_lines_tbl.COUNT > 0 THEN
641 FOR j in l_appl_tbl(i).inv_lines_tbl.FIRST..l_appl_tbl(i).inv_lines_tbl.LAST LOOP
642 x_return_status := validate_amount_to_apply(l_appl_tbl(i).inv_lines_tbl(j).amount_applied);
643 log_debug( 'RETURN Status FROM validate_amount_to_apply = '|| x_return_status);
644 IF x_return_status = OKL_API.G_RET_STS_SUCCESS THEN
645
646 log_debug( 'l_line_orig_amount FROM validate_receipt_applications = '|| l_line_orig_amount);
647 log_debug( 'l_appl_tbl(i).inv_lines_tbl(j).amount_applied FROM validate_receipt_applications = '|| l_appl_tbl(i).inv_lines_tbl(j).amount_applied);
648
649 IF l_appl_tbl(i).inv_lines_tbl(j).invoice_line_id IS NOT NULL THEN
650 l_line_orig_amount := Okl_Billing_Util_Pvt.invoice_line_amount_orig(
651 l_appl_tbl(i).inv_hdr_rec.invoice_id,l_appl_tbl(i).inv_lines_tbl(j).invoice_line_id);
652
653 IF l_appl_tbl(i).inv_lines_tbl(j).amount_applied > l_line_orig_amount THEN
654 OKL_API.set_message( p_app_name => G_APP_NAME,
655 p_msg_name => 'OKL_RCPT_LN_AMT_GT_OUTSTD_AMT',
656 p_token1 => 'AMT_TO_APPLY',
657 p_token1_value => l_appl_tbl(i).inv_lines_tbl(j).amount_applied,
658 p_token2 => 'OUTSTD_AMOUNT',
659 p_token2_value => l_line_orig_amount);
660
661 RAISE G_EXCEPTION_HALT_VALIDATION;
662 ELSE
663 -- added by dcshanmu for 6326279. Cross currency conversions begins
664 OPEN c_fetch_inv_currency(l_appl_tbl(i).inv_lines_tbl(j).invoice_line_id);
665 FETCH c_fetch_inv_currency INTO l_invoice_currency_code;
666 CLOSE c_fetch_inv_currency;
667
668 IF l_invoice_currency_code <> l_receipt_currency_code THEN
669
670 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_receipt_currency_code
671 ,l_invoice_currency_code
672 --,trunc(SYSDATE)
673 ,l_rcpt_date
674 ,'Corporate'
675 );
676
677 IF l_conversion_rate IN (0,-1) THEN
678
679 -- Message Text: No exchange rate defined
680 x_return_status := OKC_API.G_RET_STS_ERROR;
681 OKC_API.set_message( p_app_name => G_APP_NAME,
682 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
683
684 RAISE G_EXCEPTION_HALT_VALIDATION;
685
686 END IF;
687
688 -- convert receipt amount to the transaction currency ...
689 l_converted_receipt_amount := (l_appl_tbl(i).inv_lines_tbl(j).amount_applied * l_conversion_rate);
690 l_receipt_amount := l_converted_receipt_amount;
691
692 -- Check for exceptions
693
694 IF l_receipt_amount = 0 OR l_receipt_amount IS NULL THEN
695
696 -- Message Text: The receipt cannot have a value of zero
697 x_return_status := OKC_API.G_RET_STS_ERROR;
698 OKC_API.set_message( p_app_name => G_APP_NAME,
699 p_msg_name => 'OKL_BPD_ZERO_RECEIPT');
700
701 RAISE G_EXCEPTION_HALT_VALIDATION;
702 END IF;
703
704 l_appl_tbl(i).inv_lines_tbl(j).amount_applied_from := l_receipt_amount;
705 ELSE
706 l_appl_tbl(i).inv_lines_tbl(j).amount_applied_from := l_appl_tbl(i).inv_lines_tbl(j).amount_applied;
707 END IF;
708 p_appl_tbl := l_appl_tbl;
709
710 -- added by dcshanmu for 6326279. Cross currency conversions ends
711
712 l_total_outstanding_amount := l_total_outstanding_amount + l_appl_tbl(i).inv_lines_tbl(j).amount_applied_from;
713 END IF;
714 END IF;
715
716 ELSE
717 RAISE G_EXCEPTION_HALT_VALIDATION;
718 END IF;
719
720 END LOOP;
721 END IF;
722 END LOOP;
723
724 log_debug( 'p_cash_rcpt_id FROM validate_receipt_applications = '|| p_cash_rcpt_id);
725 /*
726 OPEN c_get_app_amount(p_cash_rcpt_id);
727 FETCH c_get_app_amount INTO l_app_amount;
728 CLOSE c_get_app_amount;
729
730 OPEN c_get_onacc_amount(p_cash_rcpt_id);
731 FETCH c_get_onacc_amount INTO l_onacc_amount;
732 CLOSE c_get_onacc_amount;
733 */
734 OPEN c_get_rcpt_amount(p_cash_rcpt_id);
735 FETCH c_get_rcpt_amount INTO l_rcpt_amount;
736 CLOSE c_get_rcpt_amount;
737
738 log_debug( 'sum of total outstanding and onaccount amount in validation FROM validate_receipt_applications = '|| (l_total_outstanding_amount + l_onacc_amount));
739 log_debug( 'receipt amount in validation FROM validate_receipt_applications = '|| l_rcpt_amount);
740
741 IF (l_total_outstanding_amount + l_onacc_amount +
742 l_total_charges_amount + l_total_freight_amount) > l_rcpt_amount THEN
743 OKL_API.set_message( p_app_name => G_APP_NAME,
744 p_msg_name => 'OKL_RCPT_TOT_AMT_GT_UNAPP_AMT');
745
746 RAISE G_EXCEPTION_HALT_VALIDATION;
747 END IF;
748
749 EXCEPTION
750
751 WHEN G_EXCEPTION_HALT_VALIDATION THEN
752 x_return_status := OKL_API.G_RET_STS_ERROR;
753
754 WHEN OTHERS THEN
755 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
756 Okl_api.set_message( p_app_name => g_app_name
757 , p_msg_name => g_unexpected_error
758 , p_token1 => g_sqlcode_token
759 , p_token1_value => SQLCODE
760 , p_token2 => g_sqlerrm_token
761 , p_token2_value => SQLERRM
762 ) ;
763
764 END validate_receipt_applications;
765 -- This procedure will be called for both Unapplied and Unidentified Receipt creation
766 -- If the customer account passed, then it will create an Unapplied receipt
767 -- If the customer account value is null, then it will create an Unidentified receipt
768 PROCEDURE create_unapplied_receipt
769 (p_api_version IN NUMBER,
770 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
771 x_return_status OUT NOCOPY VARCHAR2,
772 x_msg_count OUT NOCOPY NUMBER,
773 x_msg_data OUT NOCOPY VARCHAR2,
774 p_rcpt_rec IN RCPT_REC_TYPE,
775 x_cash_receipt_id OUT NOCOPY NUMBER)
776 IS
777 l_api_version NUMBER := 1.0;
778 l_init_msg_list VARCHAR2(1) := okl_api.g_false;
779 l_return_status VARCHAR2(1);
780 l_msg_count NUMBER;
781 l_msg_data VARCHAR2(2000);
782 l_api_name CONSTANT VARCHAR2(30) := 'create_unapplied_receipt';
783 l_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE;
784 l_rcpt_rec RCPT_REC_TYPE;
785 l_attribute_rec AR_RECEIPT_API_PUB.attribute_rec_type;
786 BEGIN
787 l_return_status := okl_api.start_activity(l_api_name,
788 g_pkg_name,
789 p_init_msg_list,
790 l_api_version,
791 p_api_version,
792 '_PVT',
793 l_return_status);
794
795 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
796 RAISE okl_api.g_exception_unexpected_error;
797 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
798 RAISE okl_api.g_exception_error;
799 END IF;
800 l_rcpt_rec := p_rcpt_rec;
801 l_attribute_rec.attribute_category := l_rcpt_rec.dff_attribute_category;
802 l_attribute_rec.attribute1 := l_rcpt_rec.dff_attribute1;
803 l_attribute_rec.attribute2 := l_rcpt_rec.dff_attribute2;
804 l_attribute_rec.attribute3 := l_rcpt_rec.dff_attribute3;
805 l_attribute_rec.attribute4 := l_rcpt_rec.dff_attribute4;
806 l_attribute_rec.attribute5 := l_rcpt_rec.dff_attribute5;
807 l_attribute_rec.attribute6 := l_rcpt_rec.dff_attribute6;
808 l_attribute_rec.attribute7 := l_rcpt_rec.dff_attribute7;
809 l_attribute_rec.attribute8 := l_rcpt_rec.dff_attribute8;
810 l_attribute_rec.attribute9 := l_rcpt_rec.dff_attribute9;
811 l_attribute_rec.attribute10 := l_rcpt_rec.dff_attribute10;
812 l_attribute_rec.attribute11 := l_rcpt_rec.dff_attribute11;
813 l_attribute_rec.attribute12 := l_rcpt_rec.dff_attribute12;
814 l_attribute_rec.attribute13 := l_rcpt_rec.dff_attribute13;
815 l_attribute_rec.attribute14 := l_rcpt_rec.dff_attribute14;
816 l_attribute_rec.attribute15 := l_rcpt_rec.dff_attribute15;
817 Ar_receipt_api_pub.Create_cash( p_api_version => l_api_version
818 ,p_init_msg_list => l_init_msg_list
819 ,x_return_status => l_return_status
820 ,x_msg_count => l_msg_count
821 ,x_msg_data => l_msg_data
822 ,p_receipt_number => l_rcpt_rec.receipt_number
823 ,p_receipt_date => l_rcpt_rec.receipt_date
824 ,p_customer_id => l_rcpt_rec.customer_id
825 ,p_customer_number => l_rcpt_rec.customer_number
826 ,p_amount => l_rcpt_rec.amount -- in receipt currency ...
827 ,p_currency_code => l_rcpt_rec.currency_code
828 ,p_exchange_rate_type => l_rcpt_rec.exchange_rate_type -- daily exchge rate required ...
829 ,p_exchange_rate => l_rcpt_rec.exchange_rate -- daily exchge rate required ...
830 ,p_exchange_rate_date => l_rcpt_rec.exchange_date -- daily exchge rate required ...
831 ,p_gl_date => l_rcpt_rec.gl_date
832 ,p_receipt_method_id => l_rcpt_rec.receipt_method_id
833 ,p_org_id => l_rcpt_rec.org_id
834 ,p_attribute_rec => l_attribute_rec
835 ,p_remittance_bank_account_id => l_rcpt_rec.REMITTANCE_BANK_ACCOUNT_ID
836 ,p_customer_bank_account_id => l_rcpt_rec.CUSTOMER_BANK_ACCOUNT_ID
837 ,p_payment_trxn_extension_id => l_rcpt_rec.PAYMENT_TRX_EXTENSION_ID
838 ,p_cr_id => l_cash_receipt_id -- OUT
839 );
840
841 x_cash_receipt_id := l_cash_receipt_id;
842
843 x_return_status := l_return_status;
844
845 IF x_return_status <> okl_api.g_ret_sts_success THEN
846 -- Message Text: Error creating receipt in AR
847
848 x_return_status := okl_api.g_ret_sts_error;
849
850 okl_api.set_message(p_app_name => g_app_name,p_msg_name => 'OKL_BPD_ERR_CRT_RCT_AR');
851
852 RAISE g_exception_halt_validation;
853 END IF;
854
855
856 okl_api.end_activity(l_msg_count,l_msg_data);
857
858 x_msg_data := l_msg_data;
859
860 x_msg_count := l_msg_count;
861 EXCEPTION
862 WHEN g_exception_halt_validation THEN
863 x_return_status := okl_api.g_ret_sts_error;
864 WHEN OKL_API.G_EXCEPTION_ERROR THEN
865 x_return_status := OKL_API.HANDLE_EXCEPTIONS
866 (
867 l_api_name,
868 G_PKG_NAME,
869 'OKL_API.G_RET_STS_ERROR',
870 x_msg_count,
871 x_msg_data,
872 '_PVT'
873 );
874
875 WHEN OTHERS THEN
876 x_return_status := OKL_API.HANDLE_EXCEPTIONS
877 (
878 l_api_name,
879 G_PKG_NAME,
880 'OTHERS',
881 x_msg_count,
882 x_msg_data,
883 '_PVT'
884 );
885 Okl_api.set_message( p_app_name => g_app_name
886 , p_msg_name => g_unexpected_error
887 , p_token1 => g_sqlcode_token
888 , p_token1_value => SQLCODE
889 , p_token2 => g_sqlerrm_token
890 , p_token2_value => SQLERRM
891 ) ;
892 END create_unapplied_receipt;
893
894 PROCEDURE create_onaccount_receipt
895 (p_api_version IN NUMBER,
896 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
897 x_return_status OUT NOCOPY VARCHAR2,
898 x_msg_count OUT NOCOPY NUMBER,
899 x_msg_data OUT NOCOPY VARCHAR2,
900 p_cons_bill_id IN okl_cnsld_ar_hdrs_v.id%TYPE DEFAULT NULL,
901 p_ar_inv_id IN NUMBER DEFAULT NULL,
902 p_contract_id IN okc_k_headers_all_b.id%TYPE DEFAULT NULL,
903 p_rcpt_rec IN RCPT_REC_TYPE,
904 x_cash_receipt_id OUT NOCOPY NUMBER)
905 IS
906 l_api_version NUMBER := 1.0;
907 l_init_msg_list VARCHAR2(1) := okl_api.g_false;
908 l_return_status VARCHAR2(1);
909 l_msg_count NUMBER;
910 l_msg_data VARCHAR2(2000);
911 l_api_name CONSTANT VARCHAR2(30) := 'create_onaccount_receipt';
912 l_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE;
913 l_rcpt_rec RCPT_REC_TYPE;
914 l_attribute_rec AR_RECEIPT_API_PUB.attribute_rec_type;
915 BEGIN
916 log_debug('create_onaccount_receipt start ');
917 l_return_status := okl_api.start_activity(l_api_name,
918 g_pkg_name,
919 p_init_msg_list,
920 l_api_version,
921 p_api_version,
922 '_PVT',
923 l_return_status);
924
925 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
926 RAISE okl_api.g_exception_unexpected_error;
927 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
928 RAISE okl_api.g_exception_error;
929 END IF;
930
931 l_rcpt_rec := p_rcpt_rec;
932
933 log_debug('calling create_unapplied_receipt');
934 create_unapplied_receipt(p_api_version => l_api_version,
935 p_init_msg_list => l_init_msg_list,
936 x_return_status => l_return_status,
937 x_msg_count => l_msg_count,
938 x_msg_data => l_msg_data,
939 p_rcpt_rec => l_rcpt_rec,
940 x_cash_receipt_id => x_cash_receipt_id);
941 log_debug( 'RETURN Status FROM create_unapplied_receipt = '|| l_return_status);
942 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
943 RAISE okl_api.g_exception_unexpected_error;
944 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
945 RAISE okl_api.g_exception_error;
946 END IF;
947 l_cash_receipt_id:=x_cash_receipt_id;
948
949 Ar_receipt_api_pub.Apply_on_account( p_api_version => l_api_version
950 ,p_init_msg_list => l_init_msg_list
951 ,x_return_status => l_return_status
952 ,x_msg_count => l_msg_count
953 ,x_msg_data => l_msg_data
954 ,p_cash_receipt_id => l_cash_receipt_id
955 ,p_amount_applied => l_rcpt_rec.amount
956 ,p_apply_date => l_rcpt_rec.receipt_date
957 ,p_apply_gl_date => l_rcpt_rec.gl_date
958 ,p_org_id => l_rcpt_rec.org_id
959 );
960
961 x_cash_receipt_id := l_cash_receipt_id;
962 log_debug('Return status from Ar_receipt_api_pub.Create_Apply_On_Acc = '||l_return_status);
963 x_cash_receipt_id := l_cash_receipt_id;
964
965 x_return_status := l_return_status;
966
967 IF x_return_status <> okl_api.g_ret_sts_success THEN
968 -- Message Text: Error creating receipt in AR
969
970 x_return_status := okl_api.g_ret_sts_error;
971
972 -- okl_api.set_message(p_app_name => g_app_name,p_msg_name => 'OKL_BPD_ERR_CRT_RCT_AR');
973
974 RAISE g_exception_halt_validation;
975 END IF;
976
977
978 okl_api.end_activity(x_msg_count,x_msg_data);
979
980 x_msg_data := l_msg_data;
981
982 x_msg_count := l_msg_count;
983 EXCEPTION
984 WHEN g_exception_halt_validation THEN
985 x_return_status := okl_api.g_ret_sts_error;
986 WHEN OKL_API.G_EXCEPTION_ERROR THEN
987 x_return_status := OKL_API.HANDLE_EXCEPTIONS
988 (
989 l_api_name,
990 G_PKG_NAME,
991 'OKL_API.G_RET_STS_ERROR',
992 x_msg_count,
993 x_msg_data,
994 '_PVT'
995 );
996
997 WHEN OTHERS THEN
998 x_return_status := OKL_API.HANDLE_EXCEPTIONS
999 (
1000 l_api_name,
1001 G_PKG_NAME,
1002 'OTHERS',
1003 x_msg_count,
1004 x_msg_data,
1005 '_PVT'
1006 );
1007 Okl_api.set_message( p_app_name => g_app_name
1008 , p_msg_name => g_unexpected_error
1009 , p_token1 => g_sqlcode_token
1010 , p_token1_value => SQLCODE
1011 , p_token2 => g_sqlerrm_token
1012 , p_token2_value => SQLERRM
1013 ) ;
1014 END create_onaccount_receipt;
1015
1016 PROCEDURE unapply_receipt ( p_api_version IN NUMBER
1017 ,p_init_msg_list IN VARCHAR2 DEFAULT OkL_Api.G_FALSE
1018 ,x_return_status OUT NOCOPY VARCHAR2
1019 ,x_msg_count OUT NOCOPY NUMBER
1020 ,x_msg_data OUT NOCOPY VARCHAR2
1021 ,p_customer_trx_id IN NUMBER DEFAULT NULL -- cash receipt id
1022 ,p_cash_receipt_id IN AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL -- cash receipt id
1023 ,p_org_id IN NUMBER
1024 ) IS
1025
1026 ---------------------------
1027 -- DECLARE Local Variables
1028 ---------------------------
1029
1030 l_api_version NUMBER := 1.0;
1031 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
1032 l_return_status VARCHAR2(1);
1033 l_msg_count NUMBER;
1034 l_msg_data VARCHAR2(2000);
1035 l_api_name CONSTANT VARCHAR2(30) := 'unapply_receipt';
1036
1037 l_customer_trx_id NUMBER DEFAULT p_customer_trx_id;
1038 l_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT p_cash_receipt_id;
1039 l_receivable_application_id AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID%TYPE DEFAULT NULL;
1040 l_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
1041 l_apply_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
1042 i NUMBER DEFAULT NULL;
1043 l_counter NUMBER;
1044 l_record_count NUMBER DEFAULT NULL;
1045 l_org_id NUMBER := p_org_id;
1046
1047 -- check receipt applic
1048 CURSOR c_ver_dup_applic( cp_customer_trx_id IN NUMBER
1049 ,cp_cash_receipt_id IN NUMBER) IS
1050 SELECT NVL(RECEIVABLE_APPLICATION_ID,0)
1051 FROM AR_RECEIVABLE_APPLICATIONS_ALL
1052 WHERE APPLIED_CUSTOMER_TRX_ID = cp_customer_trx_id
1053 AND CASH_RECEIPT_ID = cp_cash_receipt_id
1054 AND STATUS = 'APP'
1055 AND APPLICATION_TYPE ='CASH'
1056 AND DISPLAY ='Y'
1057 ORDER BY CREATION_DATE desc;
1058
1059 /*
1060 -- verify receipt applied amount
1061 CURSOR c_ver_app_amt(cp_csh_rcpt_id IN NUMBER) IS
1062 SELECT NVL(SUM(AMOUNT_APPLIED),0)
1063 FROM AR_RECEIVABLE_APPLICATIONS_ALL
1064 WHERE STATUS = 'APP'
1065 AND CASH_RECEIPT_ID = cp_csh_rcpt_id;
1066 -------------------------------------------------------------------------------
1067 */
1068 BEGIN
1069 l_return_status := OKL_API.G_RET_STS_SUCCESS;
1070
1071 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
1072 l_return_status := OKL_API.START_ACTIVITY(
1073 p_api_name => l_api_name
1074 , p_pkg_name => G_PKG_NAME
1075 , p_init_msg_list => p_init_msg_list
1076 , l_api_version => l_api_version
1077 , p_api_version => p_api_version
1078 , p_api_type => '_PVT'
1079 , x_return_status => x_return_status);
1080 -- check if activity started successfully
1081 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1082 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1083 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1084 RAISE OKL_API.G_EXCEPTION_ERROR;
1085 END IF;
1086
1087
1088 OPEN c_ver_dup_applic (l_customer_trx_id, l_cash_receipt_id);
1089 FETCH c_ver_dup_applic INTO l_receivable_application_id;
1090 CLOSE c_ver_dup_applic;
1091
1092 IF l_receivable_application_id IS NOT NULL THEN
1093 AR_RECEIPT_API_PUB.Unapply( p_api_version => l_api_version
1094 ,p_init_msg_list => l_init_msg_list
1095 ,x_return_status => l_return_status
1096 ,x_msg_count => l_msg_count
1097 ,x_msg_data => l_msg_data
1098 ,p_cash_receipt_id => l_cash_receipt_id
1099 ,p_customer_trx_id => l_customer_trx_id
1100 ,p_receivable_application_id => l_receivable_application_id
1101 ,p_reversal_gl_date => null
1102 ,p_org_id => l_org_id
1103 );
1104
1105 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1106 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1107 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1108 RAISE OKL_API.G_EXCEPTION_ERROR;
1109 END IF;
1110 END IF;
1111
1112 -- commit the savepoint
1113 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1114
1115 x_return_status := l_return_status;
1116
1117 EXCEPTION
1118 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1119 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1120 (
1121 l_api_name,
1122 G_PKG_NAME,
1123 'OKL_API.G_RET_STS_ERROR',
1124 x_msg_count,
1125 x_msg_data,
1126 '_PVT'
1127 );
1128
1129 WHEN OTHERS THEN
1130 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1131 (
1132 l_api_name,
1133 G_PKG_NAME,
1134 'OTHERS',
1135 x_msg_count,
1136 x_msg_data,
1137 '_PVT'
1138 );
1139 Okl_api.set_message( p_app_name => g_app_name
1140 , p_msg_name => g_unexpected_error
1141 , p_token1 => g_sqlcode_token
1142 , p_token1_value => SQLCODE
1143 , p_token2 => g_sqlerrm_token
1144 , p_token2_value => SQLERRM
1145 ) ;
1146
1147 END unapply_receipt;
1148
1149 PROCEDURE apply_receipt( p_api_version IN NUMBER
1150 ,p_init_msg_list IN VARCHAR2 DEFAULT OkL_Api.G_FALSE
1151 ,x_return_status OUT NOCOPY VARCHAR2
1152 ,x_msg_count OUT NOCOPY NUMBER
1153 ,x_msg_data OUT NOCOPY VARCHAR2
1154 ,p_cash_receipt_id IN AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE
1155 ,p_customer_trx_id IN NUMBER
1156 ,p_freight_amount IN NUMBER
1157 ,p_charges_amount IN NUMBER
1158 ,p_llca_tbl IN llca_tbl_type
1159 ,p_apply_gl_date IN DATE
1160 ,p_org_id IN NUMBER
1161 ,p_receipt_date IN DATE
1162 ,p_gl_date IN DATE
1163 ,p_trans_to_receipt_rate IN NUMBER DEFAULT NULL
1164 ) IS
1165
1166 ---------------------------
1167 -- DECLARE Local Variables
1168 ---------------------------
1169
1170 l_api_version NUMBER := 1.0;
1171 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
1172 l_return_status VARCHAR2(1);
1173 l_msg_count NUMBER;
1174 l_msg_data VARCHAR2(2000);
1175 l_api_name CONSTANT VARCHAR2(30) := 'apply_receipt';
1176
1177 l_customer_trx_id NUMBER DEFAULT p_customer_trx_id;
1178 l_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT p_cash_receipt_id;
1179 l_llca_tbl llca_tbl_type := p_llca_tbl;
1180 l_ar_llca_tbl ar_receipt_api_pub.llca_trx_lines_tbl_type;
1181 l_receivable_application_id AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID%TYPE DEFAULT NULL;
1182 l_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE DEFAULT p_gl_date;
1183 l_org_id NUMBER := p_org_id;
1184 l_apply_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
1185 i NUMBER DEFAULT NULL;
1186 k NUMBER DEFAULT NULL;
1187 l_counter NUMBER;
1188 l_record_count NUMBER DEFAULT NULL;
1189 l_rcpt_date DATE DEFAULT NULL;
1190 l_inv_due_date DATE DEFAULT NULL;
1191 l_apply_rcpt_date DATE DEFAULT NULL;
1192 l_trans_to_receipt_rate NUMBER DEFAULT p_trans_to_receipt_rate;
1193 lla_exists VARCHAR2(1) := 'N';
1194 p_count number;
1195
1196 -- added by dcshanmu for cross currency conversion as part of 6326279.Begin
1197 CURSOR c_fetch_receipt_date(p_cash_rcpt_id IN NUMBER) IS
1198 select receipt_date
1199 from ar_cash_receipts_all
1200 where cash_receipt_id=p_cash_rcpt_id;
1201
1202 CURSOR c_fetch_inv_due_date(p_customer_trx_id IN NUMBER) IS
1203 select a.due_date
1204 from ar_payment_schedules_all a
1205 where a.customer_trx_id=p_customer_trx_id;
1206 -- added by dcshanmu for cross currency conversion as part of 6326279.End
1207
1208 --Begain - handle onaccount - varangan - 6353486
1209 --Application Id for ON-Account record
1210 CURSOR c_get_onacc_app_id ( p_cash_rcpt_id IN NUMBER) IS
1211 SELECT NVL(RECEIVABLE_APPLICATION_ID,0)
1212 FROM AR_RECEIVABLE_APPLICATIONS_ALL
1213 WHERE CASH_RECEIPT_ID = p_cash_rcpt_id
1214 AND STATUS = 'ACC'
1215 AND APPLICATION_TYPE ='CASH'
1216 AND DISPLAY ='Y'
1217 ORDER BY CREATION_DATE desc ;
1218
1219 CURSOR c_get_trans_to_receipt_rate (p_cash_rcpt_id IN NUMBER
1220 , p_customer_trx_id IN NUMBER) IS
1221 SELECT TRANS_TO_RECEIPT_RATE
1222 FROM AR_RECEIVABLE_APPLICATIONS_ALL
1223 WHERE CASH_RECEIPT_ID = p_cash_rcpt_id
1224 AND APPLIED_CUSTOMER_TRX_ID = p_customer_trx_id
1225 AND NVL(DISPLAY,'N') = 'Y';
1226
1227 CURSOR c_get_cust_trx_id (p_line_id IN NUMBER) IS
1228 SELECT CUSTOMER_TRX_ID
1229 FROM RA_CUSTOMER_TRX_LINES_ALL
1230 WHERE CUSTOMER_TRX_LINE_ID = p_line_id;
1231
1232
1233 l_onacc_appplication_id NUMBER;
1234 l_receipt_date DATE DEFAULT p_receipt_date;
1235 --End - handle onaccount - varangan - 6353486
1236
1237 -- bug 6642572 .. get the error messages from gt table .. start
1238 cursor get_err_csr is
1239 select CUSTOMER_TRX_ID, error_message
1240 from ar_llca_trx_errors_gt;
1241
1242 l_err_rec get_err_csr%ROWTYPE;
1243 -- bug 6642572 .. get the error messages from gt table .. end
1244
1245 BEGIN
1246 log_debug('okl_receipts_pvt.apply_receipt start');
1247 l_return_status := OKL_API.G_RET_STS_SUCCESS;
1248
1249 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
1250 l_return_status := OKL_API.START_ACTIVITY(
1251 p_api_name => l_api_name
1252 , p_pkg_name => G_PKG_NAME
1253 , p_init_msg_list => p_init_msg_list
1254 , l_api_version => l_api_version
1255 , p_api_version => p_api_version
1256 , p_api_type => '_PVT'
1257 , x_return_status => x_return_status);
1258 -- check if activity started successfully
1259 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1260 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1261 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1262 RAISE OKL_API.G_EXCEPTION_ERROR;
1263 END IF;
1264
1265 IF l_llca_tbl.COUNT > 0 AND l_llca_tbl(l_llca_tbl.FIRST).invoice_line_id IS NOT NULL THEN
1266 OPEN c_get_cust_trx_id(l_llca_tbl(l_llca_tbl.FIRST).invoice_line_id);
1267 FETCH c_get_cust_trx_id INTO l_customer_trx_id;
1268 CLOSE c_get_cust_trx_id;
1269 END IF;
1270
1271 OPEN c_fetch_receipt_date(l_cash_receipt_id);
1272 FETCH c_fetch_receipt_date INTO l_rcpt_date;
1273 CLOSE c_fetch_receipt_date;
1274
1275 OPEN c_fetch_inv_due_date(l_customer_trx_id);
1276 FETCH c_fetch_inv_due_date INTO l_inv_due_date;
1277 CLOSE c_fetch_inv_due_date;
1278
1279 IF trunc(l_inv_due_date) < trunc(l_rcpt_date) THEN
1280 l_apply_rcpt_date := l_rcpt_date;
1281 ELSE
1282 l_apply_rcpt_date := l_inv_due_date;
1283 END IF;
1284
1285 /* IF trunc(l_rcpt_date) > trunc(sysdate) THEN
1286 OKL_API.set_message( p_app_name => G_APP_NAME,
1287 p_msg_name => 'OKL_RCPT_INVLD_APPLN_DATE');
1288 RAISE OKL_API.G_EXCEPTION_ERROR;
1289 END IF;
1290 */
1291
1292 --udhenuko Commented this code as we fetch the conversion rate directly as input.
1293 /*OPEN c_get_trans_to_receipt_rate(l_cash_receipt_id, l_customer_trx_id);
1294 FETCH c_get_trans_to_receipt_rate INTO l_trans_to_receipt_rate;
1295 CLOSE c_get_trans_to_receipt_rate;*/
1296
1297 log_debug('calling unapply receipt');
1298 -- unapply any existing application
1299 unapply_receipt( p_api_version => l_api_version,
1300 p_init_msg_list => l_init_msg_list,
1301 x_return_status => l_return_status,
1302 x_msg_count => l_msg_count,
1303 x_msg_data => l_msg_data,
1304 p_cash_receipt_id => l_cash_receipt_id,
1305 p_customer_trx_id => l_customer_trx_id,
1306 p_org_id => l_org_id);
1307 log_debug('return status of unapply_receipt = '||l_return_status);
1308 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1309 RAISE okl_api.g_exception_unexpected_error;
1310 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1311 RAISE okl_api.g_exception_error;
1312 END IF;
1313
1314 log_debug('l_llca_tbl.count = '||l_llca_tbl.COUNT);
1315 IF l_llca_tbl.COUNT > 0 THEN
1316 FOR i IN l_llca_tbl.FIRST..l_llca_tbl.LAST LOOP
1317 IF l_llca_tbl.exists(i) THEN
1318 IF l_llca_tbl(i).amount_applied <> 0 THEN
1319 l_ar_llca_tbl(i).customer_trx_line_id := l_llca_tbl(i).invoice_line_id;
1320 l_ar_llca_tbl(i).amount_applied := l_llca_tbl(i).amount_applied;
1321 l_ar_llca_tbl(i).amount_applied_from := l_llca_tbl(i).amount_applied_from;
1322 l_ar_llca_tbl(i).line_amount := l_llca_tbl(i).line_applied; --dkagrawa
1323 l_ar_llca_tbl(i).tax_amount := l_llca_tbl(i).tax_applied;
1324 log_debug('l_ar_llca_tbl('|| i ||').customer_trx_line_id = '||l_ar_llca_tbl(i).customer_trx_line_id);
1325 log_debug('l_ar_llca_tbl('|| i ||').amount_applied = '||l_ar_llca_tbl(i).amount_applied);
1326 log_debug('l_ar_llca_tbl('|| i ||').amount_applied_from = '||l_ar_llca_tbl(i).amount_applied_from);
1327 log_debug('l_ar_llca_tbl('|| i ||').amount_applied_from = '||l_ar_llca_tbl(i).line_amount);
1328 log_debug('l_ar_llca_tbl('|| i ||').amount_applied_from = '||l_ar_llca_tbl(i).tax_amount);
1329 END IF;
1330 END IF;
1331 END LOOP;
1332
1333
1334 IF l_ar_llca_tbl.COUNT = 0 AND
1335 (NVL(p_freight_amount,0) <> 0 OR NVL(p_charges_amount,0) <> 0) THEN
1336 i := l_llca_tbl.FIRST;
1337 l_ar_llca_tbl(i).customer_trx_line_id := l_llca_tbl(i).invoice_line_id;
1338 l_ar_llca_tbl(i).amount_applied := l_llca_tbl(i).amount_applied;
1339 END IF;
1340 log_debug('calling AR_RECEIPT_API_PUB.apply_in_detail');
1341 log_debug('Parameters: p_cash_receipt_id = '||l_cash_receipt_id);
1342 log_debug('p_customer_trx_id = '||l_customer_trx_id);
1343 log_debug('p_apply_gl_date = '||p_apply_gl_date);
1344 log_debug('l_apply_rcpt_date = '||l_apply_rcpt_date);
1345 log_debug('l_trans_to_receipt_rate = '||l_trans_to_receipt_rate);
1346 IF l_ar_llca_tbl.COUNT > 0 THEN
1347 lla_exists := okl_lckbx_csh_app_pvt.get_line_level_app(l_customer_trx_id , l_org_id);
1348 IF NVL(lla_exists,'N') = 'Y' THEN
1349 AR_RECEIPT_API_PUB.apply_in_detail( p_api_version => l_api_version
1350 ,p_init_msg_list => l_init_msg_list
1351 ,x_return_status => l_return_status
1352 ,x_msg_count => l_msg_count
1353 ,x_msg_data => l_msg_data
1354 ,p_cash_receipt_id => l_cash_receipt_id
1355 ,p_customer_trx_id => l_customer_trx_id
1356 ,p_llca_type => 'L'
1357 ,p_llca_trx_lines_tbl => l_ar_llca_tbl
1358 ,p_apply_date => trunc(l_apply_rcpt_date)
1359 ,p_apply_gl_date => p_apply_gl_date
1360 ,p_org_id => l_org_id
1361 ,p_freight_amount => p_freight_amount
1362 ,p_charges_amount => p_charges_amount
1363 ,p_trans_to_receipt_rate => l_trans_to_receipt_rate
1364 );
1365 log_debug('Return status of AR_RECEIPT_API_PUB.apply_in_detail = '|| l_return_status);
1366 ELSE
1367 AR_RECEIPT_API_PUB.apply( p_api_version => l_api_version
1368 ,p_init_msg_list => l_init_msg_list
1369 ,x_return_status => l_return_status
1370 ,x_msg_count => l_msg_count
1371 ,x_msg_data => l_msg_data
1372 ,p_cash_receipt_id => l_cash_receipt_id
1373 ,p_customer_trx_id => l_customer_trx_id
1374 ,p_apply_date => trunc(l_apply_rcpt_date)
1375 ,p_apply_gl_date => p_apply_gl_date
1376 ,p_org_id => l_org_id
1377 ,p_trans_to_receipt_rate => l_trans_to_receipt_rate
1378 ,p_comments => NULL
1379 ,p_amount_applied => l_ar_llca_tbl(l_ar_llca_tbl.FIRST).amount_applied
1380 );
1381 log_debug('Return status of AR_RECEIPT_API_PUB.apply = '|| l_return_status);
1382 END IF;
1383
1384 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1385 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1386 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1387 RAISE OKL_API.G_EXCEPTION_ERROR;
1388 END IF;
1389 END IF;
1390 END IF;
1391
1392 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1393
1394 x_return_status := l_return_status;
1395 log_debug('okl_receipts_pvt.apply_receipt end');
1396
1397 EXCEPTION
1398
1399 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1400 -- bug 6642572 .. get the error messages and set the stack. start
1401 if nvl(l_msg_count, 0) = 0 or l_msg_count = OKL_API.g_miss_num then
1402 open get_err_csr;
1403 loop
1404 fetch get_err_csr into l_err_rec;
1405 exit when get_err_csr%NOTFOUND;
1406
1407 Okl_Api.SET_MESSAGE(p_app_name => 'FND',
1408 p_msg_name => 'FND_GENERIC_MESSAGE',
1409 p_token1 => 'MESSAGE',
1410 p_token1_value => l_err_rec.error_message);
1411
1412 x_msg_count := x_msg_count + 1;
1413 end loop;
1414 close get_err_csr;
1415 end if;
1416 -- bug 6642572 .. get the error messages and set the stack . end
1417
1418 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1419 (
1420 l_api_name,
1421 G_PKG_NAME,
1422 'OKL_API.G_RET_STS_ERROR',
1423 x_msg_count,
1424 x_msg_data,
1425 '_PVT'
1426 );
1427
1428 -- bug 6642572 .. get the error messages and set the stack. start
1429 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1430 if nvl(l_msg_count, 0) = 0 or l_msg_count = OKL_API.g_miss_num then
1431 open get_err_csr;
1432 loop
1433 fetch get_err_csr into l_err_rec;
1434 exit when get_err_csr%NOTFOUND;
1435
1436 Okl_Api.SET_MESSAGE(p_app_name => 'FND',
1437 p_msg_name => 'FND_GENERIC_MESSAGE',
1438 p_token1 => 'MESSAGE',
1439 p_token1_value => l_err_rec.error_message);
1440
1441 x_msg_count := x_msg_count + 1;
1442 end loop;
1443 close get_err_csr;
1444 end if;
1445
1446 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1447 (
1448 l_api_name,
1449 G_PKG_NAME,
1450 'OKL_API.G_RET_STS_UNEXP_ERROR',
1451 x_msg_count,
1452 x_msg_data,
1453 '_PVT'
1454 );
1455 -- bug 6642572 .. get the error messages and set the stack . end
1456
1457 WHEN OTHERS THEN
1458 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1459 (
1460 l_api_name,
1461 G_PKG_NAME,
1462 'OTHERS',
1463 x_msg_count,
1464 x_msg_data,
1465 '_PVT'
1466 );
1467 Okl_api.set_message( p_app_name => g_app_name
1468 , p_msg_name => g_unexpected_error
1469 , p_token1 => g_sqlcode_token
1470 , p_token1_value => SQLCODE
1471 , p_token2 => g_sqlerrm_token
1472 , p_token2_value => SQLERRM
1473 ) ;
1474
1475 END apply_receipt;
1476
1477 /************************************************************************************
1478 -- Procedure to Handle Updation of On-Account Amount from Application Page
1479 ************************************************************************************/
1480
1481 PROCEDURE handle_onacc_update( p_api_version IN NUMBER
1482 ,p_init_msg_list IN VARCHAR2 DEFAULT OkL_Api.G_FALSE
1483 ,x_return_status OUT NOCOPY VARCHAR2
1484 ,x_msg_count OUT NOCOPY NUMBER
1485 ,x_msg_data OUT NOCOPY VARCHAR2
1486 ,p_cash_receipt_id IN AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE
1487 ,p_org_id IN NUMBER
1488 ,p_original_onacc_amount IN NUMBER
1489 ,p_apply_onacc_amount IN NUMBER
1490 ,p_receipt_date IN DATE
1491 ,p_gl_date IN DATE
1492 ) IS
1493
1494 ---------------------------
1495 -- DECLARE Local Variables
1496 ---------------------------
1497
1498 l_api_version NUMBER := 1.0;
1499 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
1500 l_return_status VARCHAR2(1);
1501 l_msg_count NUMBER;
1502 l_msg_data VARCHAR2(2000);
1503 l_api_name CONSTANT VARCHAR2(30) := 'handle_onacc_update';
1504
1505 l_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT p_cash_receipt_id;
1506 l_receivable_application_id AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID%TYPE DEFAULT NULL;
1507 l_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE DEFAULT p_gl_date;
1508 l_org_id NUMBER := p_org_id;
1509 l_apply_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
1510 i NUMBER DEFAULT NULL;
1511 k NUMBER DEFAULT NULL;
1512 l_counter NUMBER;
1513 l_record_count NUMBER DEFAULT NULL;
1514 l_rcpt_date DATE DEFAULT p_receipt_date;
1515 l_inv_due_date DATE DEFAULT NULL;
1516 l_apply_rcpt_date DATE DEFAULT NULL;
1517
1518 --Begain - handle onaccount - varangan - 6353486
1519 --Application Id for ON-Account record
1520 CURSOR c_get_onacc_app_id ( p_cash_rcpt_id IN NUMBER) IS
1521 SELECT NVL(RECEIVABLE_APPLICATION_ID,0)
1522 FROM AR_RECEIVABLE_APPLICATIONS_ALL
1523 WHERE CASH_RECEIPT_ID = p_cash_rcpt_id
1524 AND STATUS = 'ACC'
1525 AND APPLICATION_TYPE ='CASH'
1526 AND DISPLAY ='Y'
1527 ORDER BY CREATION_DATE desc ;
1528
1529 l_original_onacc_amount NUMBER DEFAULT p_original_onacc_amount;
1530 l_apply_onacc_amount NUMBER DEFAULT p_apply_onacc_amount;
1531 l_onacc_appplication_id NUMBER;
1532 l_receipt_date DATE DEFAULT p_receipt_date;
1533 --End - handle onaccount - varangan - 6353486
1534
1535 BEGIN
1536 log_debug('okl_receipts_pvt.Unapply_on_account start');
1537 l_return_status := OKL_API.G_RET_STS_SUCCESS;
1538
1539 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
1540 l_return_status := OKL_API.START_ACTIVITY(
1541 p_api_name => l_api_name
1542 , p_pkg_name => G_PKG_NAME
1543 , p_init_msg_list => p_init_msg_list
1544 , l_api_version => l_api_version
1545 , p_api_version => p_api_version
1546 , p_api_type => '_PVT'
1547 , x_return_status => x_return_status);
1548 -- check if activity started successfully
1549 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1550 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1551 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1552 RAISE OKL_API.G_EXCEPTION_ERROR;
1553 END IF;
1554
1555
1556 --Check if there is existing on-account amount
1557 If (Nvl(l_original_onacc_amount,0) > 0 ) Then
1558 -- Get onaccount application Id
1559 l_onacc_appplication_id := NULL;
1560 OPEN c_get_onacc_app_id(l_cash_receipt_id);
1561 FETCH c_get_onacc_app_id INTO l_onacc_appplication_id;
1562 CLOSE c_get_onacc_app_id;
1563
1564 IF l_onacc_appplication_id IS NOT NULL THEN
1565 -- Unapply the on-account amount
1566 Ar_receipt_api_pub.Unapply_on_account( p_api_version => l_api_version,
1567 p_init_msg_list => l_init_msg_list,
1568 x_return_status => l_return_status ,
1569 x_msg_count => l_msg_count ,
1570 x_msg_data => l_msg_data,
1571 p_cash_receipt_id => l_cash_receipt_id,
1572 p_receivable_application_id =>l_onacc_appplication_id,
1573 p_reversal_gl_date => NULL,
1574 p_org_id => l_org_id
1575 );
1576 log_debug('Return status of AR_RECEIPT_API_PUB.Unapply_on_account = '|| l_return_status);
1577 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1578 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1579 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1580 RAISE OKL_API.G_EXCEPTION_ERROR;
1581 END IF;
1582 END IF;
1583 END IF;
1584 -- Check if any On-Account amount to be applied
1585 If ( Nvl(l_apply_onacc_amount,0) > 0 ) Then
1586 log_debug('Ar_receipt_api_pub.Apply_on_account l_cash_receipt_id '||l_cash_receipt_id);
1587 log_debug('Ar_receipt_api_pub.Apply_on_account l_apply_onacc_amount '||l_apply_onacc_amount);
1588 log_debug('Ar_receipt_api_pub.Apply_on_account l_rcpt_date '||l_rcpt_date);
1589 log_debug('Ar_receipt_api_pub.Apply_on_account l_gl_date '||l_gl_date);
1590 -- Apply the on-account amount
1591 Ar_receipt_api_pub.Apply_on_account( p_api_version => l_api_version
1592 ,p_init_msg_list => l_init_msg_list
1593 ,x_return_status => l_return_status
1594 ,x_msg_count => l_msg_count
1595 ,x_msg_data => l_msg_data
1596 ,p_cash_receipt_id => l_cash_receipt_id
1597 ,p_amount_applied => l_apply_onacc_amount
1598 ,p_apply_date => l_rcpt_date
1599 ,p_apply_gl_date => l_gl_date
1600 ,p_org_id => l_org_id
1601 );
1602 log_debug('Return status of AR_RECEIPT_API_PUB.Apply_on_account = '|| l_return_status);
1603 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1604 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1605 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1606 RAISE OKL_API.G_EXCEPTION_ERROR;
1607 END IF;
1608 END IF;
1609
1610 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1611
1612 x_return_status := l_return_status;
1613 log_debug('okl_receipts_pvt.handle_onacc_update end');
1614 EXCEPTION
1615 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1616 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1617 (
1618 l_api_name,
1619 G_PKG_NAME,
1620 'OKL_API.G_RET_STS_ERROR',
1621 x_msg_count,
1622 x_msg_data,
1623 '_PVT'
1624 );
1625
1626 WHEN OTHERS THEN
1627 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1628 (
1629 l_api_name,
1630 G_PKG_NAME,
1631 'OTHERS',
1632 x_msg_count,
1633 x_msg_data,
1634 '_PVT'
1635 );
1636 Okl_api.set_message( p_app_name => g_app_name
1637 , p_msg_name => g_unexpected_error
1638 , p_token1 => g_sqlcode_token
1639 , p_token1_value => SQLCODE
1640 , p_token2 => g_sqlerrm_token
1641 , p_token2_value => SQLERRM
1642 ) ;
1643
1644 END handle_onacc_update;
1645
1646
1647 PROCEDURE handle_receipt
1648 (p_api_version IN NUMBER,
1649 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
1650 x_return_status OUT NOCOPY VARCHAR2,
1651 x_msg_count OUT NOCOPY NUMBER,
1652 x_msg_data OUT NOCOPY VARCHAR2,
1653 p_rcpt_rec IN RCPT_REC_TYPE,
1654 p_appl_tbl IN APPL_TBL_TYPE,
1655 x_cash_receipt_id OUT NOCOPY NUMBER)
1656 IS
1657
1658 l_rcpt_rec RCPT_REC_TYPE DEFAULT p_rcpt_rec;
1659 l_api_version NUMBER := 1.0;
1660 l_init_msg_list VARCHAR2(1) := okl_api.g_true;
1661 l_return_status VARCHAR2(1);
1662 l_msg_count NUMBER;
1663 l_msg_data VARCHAR2(2000);
1664 l_api_name CONSTANT VARCHAR2(30) := 'handle_receipt';
1665 l_onacc_appplication_id NUMBER;
1666
1667 l_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE DEFAULT p_rcpt_rec.cash_receipt_id;
1668 p_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE DEFAULT p_rcpt_rec.cash_receipt_id;
1669 l_header_count NUMBER;
1670 l_line_counter NUMBER;
1671 l_select_yn VARCHAR2(1);
1672 l_final_count NUMBER;
1673 l_call_apply BOOLEAN;
1674 l_call_unapply BOOLEAN;
1675
1676 l_currency_code ar_cash_receipts.currency_code%TYPE:= p_rcpt_rec.currency_code;
1677 l_customer_number hz_cust_accounts.account_number%TYPE:=p_rcpt_rec.customer_number;
1678 l_org_id NUMBER:= p_rcpt_rec.org_id;
1679 l_contract_number okc_k_headers_all_b.contract_number%TYPE;
1680 l_cons_inv_number okl_cnsld_ar_hdrs_all_b.consolidated_invoice_number%TYPE;
1681 l_ar_inv_number ra_customer_trx_all.trx_number%TYPE;
1682 l_ar_invoice_line_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE;
1683
1684
1685 l_appl_tbl APPL_TBL_TYPE DEFAULT p_appl_tbl;
1686 l_okl_rcpt_tbl line_appl_tbl_type;
1687 x_okl_rcpt_tbl line_appl_tbl_type;
1688 l_final_appl_tbl line_appl_tbl_type;
1689 l_inv_lns_tbl okl_auto_cash_appl_rules_pvt.okl_inv_line_tbl_type;
1690 l_init_inv_lines_tbl okl_auto_cash_appl_rules_pvt.okl_inv_line_tbl_type;
1691
1692 l_rctv_rec Okl_Rct_Pvt.rctv_rec_type;
1693 l_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
1694 x_rctv_rec Okl_Rct_Pvt.rctv_rec_type;
1695 x_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
1696
1697 l_rxh_rec okl_sla_acc_sources_pvt.rxh_rec_type;
1698
1699 l_apply_onacc_amount NUMBER:=0;
1700 l_unapply_amount NUMBER:=0;
1701 l_original_onacc_amount NUMBER:=0;
1702 l_freight_amount NUMBER DEFAULT NULL;
1703 l_charges_amount NUMBER DEFAULT NULL;
1704 l_rcpt_status VARCHAR2(50);
1705 x_status VARCHAR2(50);
1706
1707 l_trans_to_receipt_rate NUMBER;
1708 l_exchange_rate_type VARCHAR2(45);
1709 l_invoice_currency_code VARCHAR2(45);
1710
1711
1712 --Application Id for ON-Account record
1713 CURSOR c_get_onacc_app_id ( cp_cash_rcpt_id IN NUMBER) IS
1714 SELECT NVL(RECEIVABLE_APPLICATION_ID,0)
1715 FROM AR_RECEIVABLE_APPLICATIONS_ALL
1716 WHERE CASH_RECEIPT_ID = cp_cash_rcpt_id
1717 AND STATUS = 'ACC'
1718 AND APPLICATION_TYPE ='CASH'
1719 AND DISPLAY ='Y'
1720 ORDER BY CREATION_DATE desc ;
1721
1722 CURSOR c_cont_num( cp_contract_id IN NUMBER
1723 ,cp_org_id IN NUMBER
1724 ) IS
1725 SELECT contract_number
1726 FROM OKC_K_HEADERS_ALL_B
1727 WHERE id = cp_contract_id
1728 AND org_id = cp_org_id;
1729
1730 CURSOR c_cons_inv_num( cp_con_inv_id IN NUMBER
1731 ,cp_org_id IN NUMBER
1732 ) IS
1733 SELECT consolidated_invoice_number
1734 FROM okl_cnsld_ar_hdrs_all_b
1735 WHERE id = cp_con_inv_id
1736 AND org_id = cp_org_id;
1737
1738 CURSOR c_ar_inv_num( cp_ar_inv_id IN NUMBER
1739 ,cp_org_id IN NUMBER
1740 ) IS
1741 SELECT trx_number
1742 FROM ra_customer_trx_all
1743 WHERE customer_trx_id = cp_ar_inv_id
1744 AND org_id = cp_org_id;
1745
1746 CURSOR c_ar_inv_line_id(cp_ar_inv_id IN NUMBER, cp_org_id IN NUMBER) IS
1747 SELECT CUSTOMER_TRX_LINE_ID
1748 FROM RA_CUSTOMER_TRX_LINES_ALL
1749 WHERE CUSTOMER_TRX_ID = cp_ar_inv_id
1750 AND ORG_ID = cp_org_id
1751 AND LINE_TYPE = 'LINE'
1752 AND ROWNUM = 1;
1753
1754 CURSOR get_rcpt_sts(cp_cash_receipt_id IN NUMBER) IS
1755 SELECT status
1756 FROM ar_Cash_receipts_all
1757 WHERE cash_receipt_id = cp_cash_receipt_id;
1758
1759 CURSOR c_fetch_inv_currency (p_customer_trx_id IN NUMBER) IS
1760 SELECT a.invoice_currency_code
1761 FROM ra_customer_trx_all a
1762 WHERE a.customer_trx_id = p_customer_trx_id;
1763
1764 BEGIN
1765 log_debug('okl_receipts_pvt.handle_receipts start +');
1766
1767 l_return_status := okl_api.start_activity(l_api_name,
1768 g_pkg_name,
1769 p_init_msg_list,
1770 l_api_version,
1771 p_api_version,
1772 '_PVT',
1773 l_return_status);
1774
1775 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1776 RAISE okl_api.g_exception_unexpected_error;
1777 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1778 RAISE okl_api.g_exception_error;
1779 END IF;
1780 log_debug('calling validate_receipt_details');
1781 validate_receipt_details( x_return_status => l_return_status,
1782 p_rcpt_rec => l_rcpt_rec);
1783 log_debug( 'RETURN Status FROM validate_receipt_details = '|| l_return_status);
1784 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1785 RAISE okl_api.g_exception_unexpected_error;
1786 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1787 RAISE okl_api.g_exception_error;
1788 END IF;
1789
1790 IF l_rcpt_rec.cash_receipt_id is NULL OR l_rcpt_rec.cash_receipt_id = okl_api.g_miss_num THEN
1791 log_debug('l_rcpt_rec.CREATE_MODE = '||l_rcpt_rec.CREATE_MODE);
1792 IF l_rcpt_rec.CREATE_MODE = 'ONACCOUNT' OR l_rcpt_rec.CREATE_MODE = 'ADVANCED' THEN
1793 log_debug('calling create_onaccount_receipt');
1794 create_onaccount_receipt(p_api_version => l_api_version,
1795 p_init_msg_list => l_init_msg_list,
1796 x_return_status => l_return_status,
1797 x_msg_count => l_msg_count,
1798 x_msg_data => l_msg_data,
1799 p_rcpt_rec => l_rcpt_rec,
1800 x_cash_receipt_id => x_cash_receipt_id);
1801 log_debug( 'RETURN Status FROM create_onaccount_receipt = '|| l_return_status);
1802 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1803 RAISE okl_api.g_exception_unexpected_error;
1804 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1805 RAISE okl_api.g_exception_error;
1806 END IF;
1807 l_cash_receipt_id:=x_cash_receipt_id;
1808 ELSIF l_rcpt_rec.CREATE_MODE = 'UNAPPLIED' THEN -- This mode is for both Unapplied and Unidentified receipts
1809 log_debug('calling create_unapplied_receipt');
1810 create_unapplied_receipt(p_api_version => l_api_version,
1811 p_init_msg_list => l_init_msg_list,
1812 x_return_status => l_return_status,
1813 x_msg_count => l_msg_count,
1814 x_msg_data => l_msg_data,
1815 p_rcpt_rec => l_rcpt_rec,
1816 x_cash_receipt_id => x_cash_receipt_id);
1817 log_debug( 'RETURN Status FROM create_unapplied_receipt = '|| l_return_status);
1818 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1819 RAISE okl_api.g_exception_unexpected_error;
1820 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1821 RAISE okl_api.g_exception_error;
1822 END IF;
1823 l_cash_receipt_id:=x_cash_receipt_id;
1824 END IF;
1825
1826 IF l_rcpt_rec.CREATE_MODE = 'ADVANCED' THEN
1827 log_debug( 'GL date not equal = ');
1828 IF l_appl_tbl.count > 1 THEN
1829 OKL_API.set_message( p_app_name => G_APP_NAME,
1830 p_msg_name => 'OKL_RCPT_ONE_CONTRACT_REQD');
1831 RAISE G_EXCEPTION_HALT_VALIDATION;
1832 END IF;
1833 END IF;
1834 ELSE
1835 x_cash_receipt_id := l_rcpt_rec.cash_receipt_id;
1836 populate_receipt_rec(l_rcpt_rec);
1837 OPEN get_rcpt_sts(l_rcpt_rec.cash_receipt_id);
1838 FETCH get_rcpt_sts INTO l_rcpt_status;
1839 CLOSE get_rcpt_sts;
1840 IF l_rcpt_status = 'UNID' THEN
1841 IF l_rcpt_rec.customer_id IS NOT NULL AND l_rcpt_rec.customer_id <> okl_api.g_miss_num THEN
1842
1843 Ar_receipt_update_api_pub.update_receipt_unid_to_unapp(
1844 p_api_version => l_api_version
1845 ,p_init_msg_list => l_init_msg_list
1846 ,x_return_status => l_return_status
1847 ,x_msg_count => l_msg_count
1848 ,x_msg_data => l_msg_data
1849 ,p_cash_receipt_id => l_rcpt_rec.cash_receipt_id
1850 ,p_pay_from_customer => l_rcpt_rec.customer_id
1851 ,x_status => x_status
1852 ,p_customer_bank_account_id => l_rcpt_rec.customer_bank_account_id
1853 );
1854 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1855 RAISE okl_api.g_exception_unexpected_error;
1856 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1857 RAISE okl_api.g_exception_error;
1858 END IF;
1859 END IF;
1860 END IF;
1861 END IF;
1862 -- Capture Original and Updated On-Account Amount
1863 l_original_onacc_amount:= l_rcpt_rec.p_original_onacc_amount;
1864 l_apply_onacc_amount := l_rcpt_rec.p_apply_onacc_amount;
1865
1866
1867 IF l_appl_tbl.count > 0 THEN
1868 l_header_count:=0;
1869 l_line_counter:=0;
1870 FOR i IN l_appl_tbl.FIRST..l_appl_tbl.LAST LOOP
1871 l_select_yn:='N';
1872 l_inv_lns_tbl:=l_init_inv_lines_tbl;
1873 IF l_rcpt_rec.CREATE_MODE = 'ADVANCED' THEN
1874 l_rctv_rec.IRM_ID := l_rcpt_rec.receipt_method_id;
1875 l_rctv_rec.ILE_ID := l_rcpt_rec.customer_id;
1876 l_rctv_rec.CHECK_NUMBER := l_rcpt_rec.receipt_number;
1877 l_rctv_rec.AMOUNT := l_rcpt_rec.amount;
1878 l_rctv_rec.CURRENCY_CODE := l_rcpt_rec.currency_code;
1879 l_rctv_rec.EXCHANGE_RATE := l_rcpt_rec.EXCHANGE_RATE;
1880 l_rctv_rec.EXCHANGE_RATE_TYPE := l_rcpt_rec.EXCHANGE_RATE_TYPE;
1881 l_rctv_rec.EXCHANGE_RATE_DATE := l_rcpt_rec.EXCHANGE_DATE;
1882 l_rctv_rec.DATE_EFFECTIVE := l_rcpt_rec.receipt_Date;
1883 l_rctv_rec.GL_DATE := l_rcpt_rec.gl_date;
1884 l_rctv_rec.ORG_ID := l_rcpt_rec.org_id;
1885 l_rctv_rec.RECEIPT_TYPE := 'ADV';
1886 l_rctv_rec.cash_receipt_id := l_cash_receipt_id;
1887 l_rctv_rec.expired_flag := 'N';
1888 l_rctv_rec.fully_Applied_flag := 'N';
1889
1890 l_rcav_tbl(1).KHR_ID := l_appl_tbl(i).contract_id;
1891 l_rcav_tbl(1).ILE_ID := l_rcpt_rec.customer_id;
1892 l_rcav_tbl(1).AMOUNT := l_rcpt_rec.amount;
1893 l_rcav_tbl(1).ORG_ID := l_rcpt_rec.org_id;
1894 log_debug('calling Okl_Rct_Pub.create_internal_trans');
1895 Okl_Rct_Pub.create_internal_trans (l_api_version
1896 ,l_init_msg_list
1897 ,l_return_status
1898 ,l_msg_count
1899 ,l_msg_data
1900 ,l_rctv_rec
1901 ,l_rcav_tbl
1902 ,x_rctv_rec
1903 ,x_rcav_tbl);
1904 log_debug('Return status of Okl_Rct_Pub.create_internal_trans = '|| l_return_status);
1905 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1906 RAISE okl_api.g_exception_unexpected_error;
1907 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1908 RAISE okl_api.g_exception_error;
1909 END IF;
1910 log_debug('l_appl_tbl(i).contract_id : '||l_appl_tbl(i).contract_id);
1911 log_debug('l_appl_tbl(i).con_inv_id : '||l_appl_tbl(i).con_inv_id);
1912 log_debug('l_appl_tbl(i).ar_inv_id : '||l_appl_tbl(i).ar_inv_id);
1913 IF l_appl_tbl(i).contract_id IS NOT NULL and l_appl_tbl(i).contract_id <> okl_api.g_miss_num THEN
1914 l_rxh_rec.source_id := l_cash_receipt_id;
1915 l_rxh_rec.khr_id := l_appl_tbl(i).contract_id;
1916 log_debug(' okl_sla_acc_sources_pvt.populate_sources');
1917 okl_sla_acc_sources_pvt.populate_sources(
1918 p_api_version => l_api_version
1919 ,p_init_msg_list => l_init_msg_list
1920 ,p_rxh_rec => l_rxh_rec
1921 ,x_return_status => l_return_status
1922 ,x_msg_count => l_msg_count
1923 ,x_msg_data => l_msg_data
1924 );
1925
1926 log_debug('Return status of okl_sla_acc_sources_pvt.populate_sources = '|| l_return_status);
1927 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1928 RAISE okl_api.g_exception_unexpected_error;
1929 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1930 RAISE okl_api.g_exception_error;
1931 END IF;
1932 END IF;
1933 ELSIF l_appl_tbl(i).contract_id is not null then
1934 OPEN c_cont_num(l_appl_tbl(i).contract_id,l_org_id);
1935 FETCH c_cont_num into l_contract_number;
1936 CLOSE C_cont_num;
1937 log_debug('Before okl_auto_cash_appl_rules_pvt.auto_cashapp_for_contract');
1938 okl_auto_cash_appl_rules_pvt.auto_cashapp_for_contract( p_api_version => l_api_version,
1939 p_init_msg_list => l_init_msg_list,
1940 x_return_status => l_return_status,
1941 x_msg_count => l_msg_count,
1942 x_msg_data => l_msg_data,
1943 p_customer_num => l_customer_number,
1944 p_contract_num => l_contract_number,
1945 p_currency_code => l_currency_code,
1946 p_amount_app_to => l_appl_tbl(i).amount_to_apply,
1947 p_amount_app_from => l_appl_tbl(i).amount_applied_from,
1948 p_receipt_date => l_rcpt_rec.receipt_date,
1949 p_org_id => l_org_id,
1950 x_appl_tbl => x_okl_rcpt_tbl,
1951 x_onacc_amount => l_apply_onacc_amount,
1952 x_unapply_amount =>l_unapply_amount
1953 );
1954 log_debug('After okl_auto_cash_appl_rules_pvt.auto_cashapp_for_contract');
1955 log_debug('l_apply_onacc_amount = '||l_apply_onacc_amount);
1956 log_debug('l_unapply_amount = '||l_unapply_amount);
1957 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1958 RAISE okl_api.g_exception_unexpected_error;
1959 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1960 RAISE okl_api.g_exception_error;
1961 END IF;
1962 l_okl_rcpt_tbl:=x_okl_rcpt_tbl;
1963 log_debug( 'l_okl_rcpt_tbl= count '|| x_okl_rcpt_tbl.count);
1964 IF l_final_appl_tbl.COUNT>0 THEN
1965 l_final_count:=l_final_appl_tbl.LAST+1;
1966 FOR k IN l_okl_rcpt_tbl.FIRST..l_okl_rcpt_tbl.LAST LOOP
1967 log_debug('x_okl_rcpt_tbl(k).amount_applied = '||x_okl_rcpt_tbl(k).inv_hdr_rec.amount_applied);
1968 log_debug('x_okl_rcpt_tbl(k).amount_applied_from = '||x_okl_rcpt_tbl(k).inv_hdr_rec.amount_applied_from);
1969 l_final_appl_tbl(l_final_count) := x_okl_rcpt_tbl(k);
1970 l_final_count:=l_final_count+1;
1971 END LOOP;
1972 ELSE
1973 l_final_appl_tbl:=l_okl_rcpt_tbl;
1974 END IF;
1975 log_debug( 'l_final_appl_tbl count'|| l_final_appl_tbl.count);
1976 ELSIF l_appl_tbl(i).con_inv_id is not null then
1977 OPEN c_cons_inv_num(l_appl_tbl(i).con_inv_id,l_org_id);
1978 FETCH c_cons_inv_num into l_cons_inv_number;
1979 CLOSE c_cons_inv_num;
1980 log_debug('Before okl_auto_cash_appl_rules_pvt.auto_cashapp_for_consinv');
1981 okl_auto_cash_appl_rules_pvt.auto_cashapp_for_consinv ( p_api_version => l_api_version,
1982 p_init_msg_list => l_init_msg_list,
1983 x_return_status => l_return_status,
1984 x_msg_count => l_msg_count,
1985 x_msg_data => l_msg_data,
1986 p_customer_num => l_customer_number,
1987 p_cons_inv => l_cons_inv_number,
1988 p_currency_code => l_currency_code,
1989 p_amount_app_to => l_appl_tbl(i).amount_to_apply,
1990 p_amount_app_from => l_appl_tbl(i).amount_applied_from,
1991 p_receipt_date => l_rcpt_rec.receipt_date,
1992 p_org_id => l_org_id,
1993 x_appl_tbl => x_okl_rcpt_tbl,
1994 x_onacc_amount => l_apply_onacc_amount,
1995 x_unapply_amount =>l_unapply_amount
1996 );
1997 log_debug('After okl_auto_cash_appl_rules_pvt.auto_cashapp_for_consinv');
1998 log_debug('l_apply_onacc_amount = '||l_apply_onacc_amount);
1999 log_debug('l_unapply_amount = '||l_unapply_amount);
2000 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2001 RAISE okl_api.g_exception_unexpected_error;
2002 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2003 RAISE okl_api.g_exception_error;
2004 END IF;
2005 l_okl_rcpt_tbl:=x_okl_rcpt_tbl;
2006 IF l_final_appl_tbl.COUNT>0 THEN
2007 l_final_count:=l_final_appl_tbl.LAST+1;
2008 FOR k IN l_okl_rcpt_tbl.FIRST..l_okl_rcpt_tbl.LAST LOOP
2009 log_debug('x_okl_rcpt_tbl(k).amount_applied = '||x_okl_rcpt_tbl(k).inv_hdr_rec.amount_applied);
2010 log_debug('x_okl_rcpt_tbl(k).amount_applied_from = '||x_okl_rcpt_tbl(k).inv_hdr_rec.amount_applied_from);
2011 l_final_appl_tbl(l_final_count) := x_okl_rcpt_tbl(k);
2012 l_final_count:=l_final_count+1;
2013 END LOOP;
2014 ELSE
2015 l_final_appl_tbl:=l_okl_rcpt_tbl;
2016 END IF;
2017
2018 ELSIF l_appl_tbl(i).ar_inv_id is not null and l_appl_tbl(i).line_id is null
2019 and NVL(l_appl_tbl(i).line_type,'LINE') = 'LINE'
2020 and l_rcpt_rec.cash_receipt_id IS NULL then
2021 OPEN c_ar_inv_num(l_appl_tbl(i).ar_inv_id,l_org_id);
2022 FETCH c_ar_inv_num into l_ar_inv_number;
2023 CLOSE c_ar_inv_num;
2024 log_debug('Before okl_auto_cash_appl_rules_pvt.auto_cashapp_for_arinv');
2025 okl_auto_cash_appl_rules_pvt.auto_cashapp_for_arinv ( p_api_version => l_api_version,
2026 p_init_msg_list => l_init_msg_list,
2027 x_return_status => l_return_status,
2028 x_msg_count => l_msg_count,
2029 x_msg_data => l_msg_data,
2030 p_customer_num => l_customer_number,
2031 p_arinv_number => l_ar_inv_number,
2032 p_currency_code => l_currency_code,
2033 p_amount_app_to => l_appl_tbl(i).amount_to_apply,
2034 p_amount_app_from => l_appl_tbl(i).amount_applied_from,
2035 p_receipt_date => l_rcpt_rec.receipt_date,
2036 p_org_id => l_org_id,
2037 x_appl_tbl => x_okl_rcpt_tbl,
2038 x_onacc_amount => l_apply_onacc_amount,
2039 x_unapply_amount =>l_unapply_amount
2040 );
2041 log_debug('After okl_auto_cash_appl_rules_pvt.auto_cashapp_for_arinv');
2042 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2043 RAISE okl_api.g_exception_unexpected_error;
2044 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2045 RAISE okl_api.g_exception_error;
2046 END IF;
2047
2048 log_debug('l_apply_onacc_amount = '||l_apply_onacc_amount);
2049 log_debug('l_unapply_amount = '||l_unapply_amount);
2050 l_okl_rcpt_tbl:=x_okl_rcpt_tbl;
2051 IF l_final_appl_tbl.COUNT>0 THEN
2052 l_final_count:=l_final_appl_tbl.LAST+1;
2053 FOR k IN l_okl_rcpt_tbl.FIRST..l_okl_rcpt_tbl.LAST LOOP
2054 log_debug('x_okl_rcpt_tbl(k).amount_applied = '||x_okl_rcpt_tbl(k).inv_hdr_rec.amount_applied);
2055 log_debug('x_okl_rcpt_tbl(k).amount_applied_from = '||x_okl_rcpt_tbl(k).inv_hdr_rec.amount_applied_from);
2056 l_final_appl_tbl(l_final_count) := x_okl_rcpt_tbl(k);
2057 l_final_count:=l_final_count+1;
2058 END LOOP;
2059 ELSE
2060 l_final_appl_tbl:=l_okl_rcpt_tbl;
2061 END IF;
2062 ELSIF l_appl_tbl(i).ar_inv_id is not null
2063 and (l_appl_tbl(i).line_id is not null
2064 OR l_rcpt_rec.cash_receipt_id IS NOT NULL)
2065 and NVL(l_appl_tbl(i).line_type,'LINE') = 'LINE' then
2066 --- populate l_final_appl_tbl based on ar inv id
2067 -- first time condition
2068 IF l_final_appl_tbl.COUNT <=0 THEN
2069 l_final_appl_tbl(0).inv_hdr_rec.invoice_id:=l_appl_tbl(i).ar_inv_id;
2070 l_final_appl_tbl(0).inv_hdr_rec.gl_date:=l_appl_tbl(i).gl_date;
2071 l_final_appl_tbl(0).inv_hdr_rec.trans_to_receipt_rate:=l_appl_tbl(i).trans_to_receipt_rate;
2072 l_inv_lns_tbl(0).invoice_line_id := l_appl_tbl(i).line_id;
2073 l_inv_lns_tbl(0).amount_applied := l_appl_tbl(i).amount_to_apply;
2074 l_inv_lns_tbl(0).original_applied_amount := l_appl_tbl(i).original_applied_amount;
2075 l_inv_lns_tbl(0).line_applied := l_appl_tbl(i).line_applied;
2076 l_inv_lns_tbl(0).tax_applied := l_appl_tbl(i).tax_applied; --dkagrawa
2077 l_final_appl_tbl(0).inv_lines_tbl := l_inv_lns_tbl;
2078 ELSE
2079 FOR k IN l_final_appl_tbl.first..l_final_appl_tbl.LAST LOOP
2080 IF l_final_appl_tbl(k).inv_hdr_rec.invoice_id=l_appl_tbl(i).ar_inv_id THEN
2081 IF l_final_appl_tbl(k).inv_hdr_rec.gl_date IS NOT NULL AND
2082 l_appl_tbl(i).gl_date IS NOT NULL THEN
2083
2084 IF l_final_appl_tbl(k).inv_hdr_rec.gl_date <> l_appl_tbl(i).gl_date THEN
2085 log_debug( 'GL date not equal = ');
2086 OKL_API.set_message( p_app_name => G_APP_NAME,
2087 p_msg_name => 'OKL_RCPT_INVLD_GL_DATE_FOR_INV');
2088 RAISE G_EXCEPTION_HALT_VALIDATION;
2089 END IF;
2090 END IF;
2091 IF l_final_appl_tbl(k).inv_hdr_rec.trans_to_receipt_rate IS NOT NULL AND
2092 l_appl_tbl(i).trans_to_receipt_rate IS NOT NULL THEN
2093
2094 IF l_final_appl_tbl(k).inv_hdr_rec.trans_to_receipt_rate <> l_appl_tbl(i).trans_to_receipt_rate THEN
2095 log_debug( 'GL date not equal = ');
2096 OKL_API.set_message( p_app_name => G_APP_NAME,
2097 p_msg_name => 'OKL_RCPT_INVLD_RATE_FOR_INV');
2098 RAISE G_EXCEPTION_HALT_VALIDATION;
2099 END IF;
2100 END IF;
2101 l_header_count:=k;
2102 l_line_counter:=(l_final_appl_tbl(k).inv_lines_tbl.LAST+1);
2103 l_line_counter := NVL(l_line_counter,0);
2104 l_select_yn:='Y';
2105 EXIT;
2106 END IF;
2107 END LOOP;
2108
2109 IF l_select_yn='Y' THEN
2110 -- header record already present ,get the lines records in table and add new line
2111 l_inv_lns_tbl:=l_final_appl_tbl(l_header_count).inv_lines_tbl;
2112 l_inv_lns_tbl(l_line_counter).invoice_line_id := l_appl_tbl(i).line_id;
2113 l_inv_lns_tbl(l_line_counter).amount_applied := l_appl_tbl(i).amount_to_apply;
2114 l_inv_lns_tbl(l_line_counter).original_applied_amount := l_appl_tbl(i).original_applied_amount;
2115 l_inv_lns_tbl(l_line_counter).line_applied := l_appl_tbl(i).line_applied;
2116 l_inv_lns_tbl(l_line_counter).tax_applied := l_appl_tbl(i).tax_applied; --dkagrawa
2117 --assign the record back to the receipts table
2118 l_final_appl_tbl(l_header_count).inv_lines_tbl := l_inv_lns_tbl;
2119 ELSE
2120 l_header_count:=(l_final_appl_tbl.LAST+1);
2121 l_line_counter:=0;
2122 l_final_appl_tbl(l_header_count).inv_hdr_rec.invoice_id:=l_appl_tbl(i).ar_inv_id;
2123 l_final_appl_tbl(l_header_count).inv_hdr_rec.gl_date:=l_appl_tbl(i).gl_date;
2124 l_final_appl_tbl(l_header_count).inv_hdr_rec.trans_to_receipt_rate:=l_appl_tbl(i).trans_to_receipt_rate;
2125 l_inv_lns_tbl(l_line_counter).invoice_line_id := l_appl_tbl(i).line_id;
2126 l_inv_lns_tbl(l_line_counter).amount_applied := l_appl_tbl(i).amount_to_apply;
2127 l_inv_lns_tbl(l_line_counter).original_applied_amount := l_appl_tbl(i).original_applied_amount;
2128 l_inv_lns_tbl(l_line_counter).line_applied := l_appl_tbl(i).line_applied;
2129 l_inv_lns_tbl(l_line_counter).tax_applied := l_appl_tbl(i).tax_applied; --dkagrawa
2130 l_final_appl_tbl(l_header_count).inv_lines_tbl := l_inv_lns_tbl;
2131 END IF;
2132
2133 END IF;
2134 --If application line belongs to freight then handle it
2135 --separately
2136 ELSIF NVL(l_appl_tbl(i).line_type,'LINE') IN ('FREIGHT','CHARGES') THEN
2137 --- populate l_final_appl_tbl based on ar inv id
2138 -- first time condition
2139 IF l_final_appl_tbl.COUNT <=0 THEN
2140 l_final_appl_tbl(0).inv_hdr_rec.invoice_id:=l_appl_tbl(i).ar_inv_id;
2141 l_final_appl_tbl(0).inv_hdr_rec.gl_date:=l_appl_tbl(i).gl_date;
2142 l_final_appl_tbl(0).inv_hdr_rec.trans_to_receipt_rate:=l_appl_tbl(i).trans_to_receipt_rate;
2143 IF l_appl_tbl(i).line_type = 'FREIGHT' THEN
2144 l_final_appl_tbl(0).inv_hdr_rec.freight_amount := l_appl_tbl(i).amount_to_apply;
2145 ELSIF l_appl_tbl(i).line_type = 'CHARGES' THEN
2146 l_final_appl_tbl(0).inv_hdr_rec.charges_amount := l_appl_tbl(i).amount_to_apply;
2147 END IF;
2148 ELSE
2149 FOR k IN l_final_appl_tbl.first..l_final_appl_tbl.LAST LOOP
2150 IF l_final_appl_tbl(k).inv_hdr_rec.invoice_id=l_appl_tbl(i).ar_inv_id THEN
2151 IF l_final_appl_tbl(k).inv_hdr_rec.gl_date IS NOT NULL AND
2152 l_appl_tbl(i).gl_date IS NOT NULL THEN
2153 IF l_final_appl_tbl(k).inv_hdr_rec.gl_date <> l_appl_tbl(i).gl_date THEN
2154 OKL_API.set_message( p_app_name => G_APP_NAME,
2155 p_msg_name => 'OKL_RCPT_INVLD_GL_DATE_FOR_INV');
2156 RAISE G_EXCEPTION_HALT_VALIDATION;
2157 END IF;
2158 END IF;
2159 IF l_final_appl_tbl(k).inv_hdr_rec.trans_to_receipt_rate IS NOT NULL AND
2160 l_appl_tbl(i).trans_to_receipt_rate IS NOT NULL THEN
2161 IF l_final_appl_tbl(k).inv_hdr_rec.trans_to_receipt_rate <> l_appl_tbl(i).trans_to_receipt_rate THEN
2162 OKL_API.set_message( p_app_name => G_APP_NAME,
2163 p_msg_name => 'OKL_RCPT_INVLD_RATE_FOR_INV');
2164 RAISE G_EXCEPTION_HALT_VALIDATION;
2165 END IF;
2166 END IF;
2167 IF l_appl_tbl(i).line_type = 'FREIGHT' THEN
2168 l_final_appl_tbl(k).inv_hdr_rec.freight_amount :=
2169 NVL(l_final_appl_tbl(k).inv_hdr_rec.freight_amount,0) +
2170 l_appl_tbl(i).amount_to_apply;
2171 ELSIF l_appl_tbl(i).line_type = 'CHARGES' THEN
2172 l_final_appl_tbl(k).inv_hdr_rec.charges_amount :=
2173 NVL(l_final_appl_tbl(k).inv_hdr_rec.charges_amount,0) +
2174 l_appl_tbl(i).amount_to_apply;
2175 END IF;
2176 l_select_yn := 'Y';
2177 EXIT;
2178 END IF;
2179 END LOOP;
2180 IF l_select_yn = 'N' THEN
2181 l_header_count := l_final_appl_tbl.LAST + 1;
2182 l_final_appl_tbl(l_header_count).inv_hdr_rec.invoice_id:=l_appl_tbl(i).ar_inv_id;
2183 l_final_appl_tbl(l_header_count).inv_hdr_rec.gl_date:=l_appl_tbl(i).gl_date;
2184 l_final_appl_tbl(l_header_count).inv_hdr_rec.trans_to_receipt_rate:=l_appl_tbl(i).trans_to_receipt_rate;
2185 IF l_appl_tbl(i).line_type = 'FREIGHT' THEN
2186 l_final_appl_tbl(l_header_count).inv_hdr_rec.freight_amount := l_appl_tbl(i).amount_to_apply;
2187 ELSIF l_appl_tbl(i).line_type = 'CHARGES' THEN
2188 l_final_appl_tbl(l_header_count).inv_hdr_rec.charges_amount := l_appl_tbl(i).amount_to_apply;
2189 END IF;
2190 END IF;
2191
2192 END IF;
2193 END IF;
2194 END LOOP;
2195 END IF;
2196 -- Updating the On-Acc variables, if it is null/g_miss_num
2197 If (l_original_onacc_amount Is Null) Or (l_original_onacc_amount = okl_api.g_miss_num) Then
2198 l_original_onacc_amount:= 0;
2199 End If;
2200 If (l_apply_onacc_amount Is Null) Or (l_apply_onacc_amount = okl_api.g_miss_num) Then
2201 l_apply_onacc_amount:=0;
2202 End If;
2203
2204 log_debug('In the final count before looping'||l_final_appl_tbl.COUNT);
2205 IF l_final_appl_tbl.COUNT > 0 THEN
2206 log_debug('calling validate_receipt_applications');
2207
2208 IF p_rcpt_rec.cash_receipt_id IS NOT NULL THEN
2209 p_cash_receipt_id := p_rcpt_rec.cash_receipt_id;
2210 ELSE
2211 p_cash_receipt_id := x_cash_receipt_id;
2212 END IF;
2213 /* -- Commenting the Overapplication validation to let AR validate the applications
2214 validate_receipt_applications(
2215 p_cash_rcpt_id => p_cash_receipt_id,
2216 p_appl_tbl => l_final_appl_tbl,
2217 P_onacc_amount => l_apply_onacc_amount,
2218 x_return_status => l_return_status );
2219
2220 log_debug( 'RETURN Status FROM validate_receipt_applications = '|| l_return_status);
2221 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2222 RAISE okl_api.g_exception_unexpected_error;
2223 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2224 RAISE okl_api.g_exception_error;
2225 END IF;
2226 */
2227 -- unapply all the applications first which are changing, so that the amount is available for application
2228 FOR i IN l_final_appl_tbl.FIRST..l_final_appl_tbl.LAST LOOP
2229 --If freight amount or charges amount is there then check whether
2230 --any invoice lines are there or not. If no then create one invoice line
2231 --with applied amount 0. Otherwise keep as it is.
2232
2233 l_freight_amount := l_final_appl_tbl(i).inv_hdr_rec.freight_amount;
2234 IF l_freight_amount = OKL_API.G_MISS_NUM OR l_freight_amount = 0 THEN
2235 l_freight_amount := NULL;
2236 END IF;
2237 l_charges_amount := l_final_appl_tbl(i).inv_hdr_rec.charges_amount;
2238 IF l_charges_amount = OKL_API.G_MISS_NUM OR l_charges_amount = 0 THEN
2239 l_charges_amount := NULL;
2240 END IF;
2241 IF (l_freight_amount IS NOT NULL OR l_charges_amount IS NOT NULL) AND
2242 l_final_appl_tbl(i).inv_lines_tbl.count = 0 THEN
2243 l_inv_lns_tbl.delete;
2244 OPEN c_ar_inv_line_id(l_final_appl_tbl(i).inv_hdr_rec.invoice_id, l_org_id);
2245 FETCH c_ar_inv_line_id INTO l_ar_invoice_line_id;
2246 CLOSE c_ar_inv_line_id;
2247 l_inv_lns_tbl(0).invoice_line_id := l_ar_invoice_line_id;
2248 l_inv_lns_tbl(0).amount_applied := 0;
2249 l_final_appl_tbl(i).inv_lines_tbl := l_inv_lns_tbl;
2250 END IF;
2251 log_debug('In the final count');
2252
2253 IF l_final_appl_tbl(i).inv_lines_tbl.count <> 0 then
2254 l_call_unapply := FALSE;
2255 IF (l_final_appl_tbl(i).inv_hdr_rec.freight_amount IS NOT NULL OR l_final_appl_tbl(i).inv_hdr_rec.charges_amount IS NOT NULL) THEN
2256 l_call_unapply := TRUE;
2257 ELSE
2258 FOR ll IN l_final_appl_tbl(i).inv_lines_tbl.FIRST..l_final_appl_tbl(i).inv_lines_tbl.LAST LOOP
2259 IF nvl(l_final_appl_tbl(i).inv_lines_tbl(ll).original_applied_amount,0) <> l_final_appl_tbl(i).inv_lines_tbl(ll).amount_applied THEN
2260 l_call_unapply := TRUE;
2261 END IF;
2262 END LOOP;
2263 END IF;
2264
2265 IF l_call_unapply = TRUE THEN
2266 unapply_receipt( p_api_version => l_api_version,
2267 p_init_msg_list => l_init_msg_list,
2268 x_return_status => l_return_status,
2269 x_msg_count => l_msg_count,
2270 x_msg_data => l_msg_data,
2271 p_cash_receipt_id => l_cash_receipt_id,
2272 p_customer_trx_id => l_final_appl_tbl(i).inv_hdr_rec.invoice_id,
2273 p_org_id => l_org_id);
2274 log_debug('return status of unapply_receipt = '||l_return_status);
2275 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2276 RAISE okl_api.g_exception_unexpected_error;
2277 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2278 RAISE okl_api.g_exception_error;
2279 END IF;
2280 END IF;
2281 END IF;
2282 END LOOP;
2283 --Check if there is existing on-account amount
2284 If l_original_onacc_amount <> l_apply_onacc_amount Then
2285 If (Nvl(l_original_onacc_amount,0) > 0 ) Then
2286 -- Get onaccount application Id
2287 OPEN c_get_onacc_app_id(l_cash_receipt_id);
2288 FETCH c_get_onacc_app_id INTO l_onacc_appplication_id;
2289 CLOSE c_get_onacc_app_id;
2290
2291 -- Unapply the on-account amount
2292 Ar_receipt_api_pub.Unapply_on_account( p_api_version => l_api_version,
2293 p_init_msg_list => l_init_msg_list,
2294 x_return_status => l_return_status ,
2295 x_msg_count => l_msg_count ,
2296 x_msg_data => l_msg_data,
2297 p_cash_receipt_id => l_cash_receipt_id,
2298 p_receivable_application_id =>l_onacc_appplication_id,
2299 p_reversal_gl_date => NULL,
2300 p_org_id => l_org_id
2301 );
2302 log_debug('Return status of AR_RECEIPT_API_PUB.Unapply_on_account = '|| l_return_status);
2303 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2304 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2305 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2306 RAISE OKL_API.G_EXCEPTION_ERROR;
2307 END IF;
2308 END IF;
2309 END IF;
2310 FOR i IN l_final_appl_tbl.FIRST..l_final_appl_tbl.LAST LOOP
2311 --If freight amount or charges amount is there then check whether
2312 --any invoice lines are there or not. If no then create one invoice line
2313 --with applied amount 0. Otherwise keep as it is.
2314 --this code has been moved in above loop where we do unapplication, so commenting it out here
2315 /* l_freight_amount := l_final_appl_tbl(i).inv_hdr_rec.freight_amount;
2316 IF l_freight_amount = OKL_API.G_MISS_NUM OR l_freight_amount = 0 THEN
2317 l_freight_amount := NULL;
2318 END IF;
2319 l_charges_amount := l_final_appl_tbl(i).inv_hdr_rec.charges_amount;
2320 IF l_charges_amount = OKL_API.G_MISS_NUM OR l_charges_amount = 0 THEN
2321 l_charges_amount := NULL;
2322 END IF;
2323 IF (l_freight_amount IS NOT NULL OR l_charges_amount IS NOT NULL) AND
2324 l_final_appl_tbl(i).inv_lines_tbl.count = 0 THEN
2325 l_inv_lns_tbl.delete;
2326 OPEN c_ar_inv_line_id(l_final_appl_tbl(i).inv_hdr_rec.invoice_id, l_org_id);
2327 FETCH c_ar_inv_line_id INTO l_ar_invoice_line_id;
2328 CLOSE c_ar_inv_line_id;
2329 l_inv_lns_tbl(0).invoice_line_id := l_ar_invoice_line_id;
2330 l_inv_lns_tbl(0).amount_applied := 0;
2331 l_final_appl_tbl(i).inv_lines_tbl := l_inv_lns_tbl;
2332 END IF;*/
2333 log_debug('In the final count');
2334
2335 IF l_final_appl_tbl(i).inv_lines_tbl.count <> 0 then
2336 l_call_apply := FALSE;
2337 IF (l_final_appl_tbl(i).inv_hdr_rec.freight_amount IS NOT NULL OR l_final_appl_tbl(i).inv_hdr_rec.charges_amount IS NOT NULL) THEN
2338 l_call_apply := TRUE;
2339 ELSE
2340 FOR ll IN l_final_appl_tbl(i).inv_lines_tbl.FIRST..l_final_appl_tbl(i).inv_lines_tbl.LAST LOOP
2341 IF nvl(l_final_appl_tbl(i).inv_lines_tbl(ll).original_applied_amount,0) <> l_final_appl_tbl(i).inv_lines_tbl(ll).amount_applied THEN
2342 l_call_apply := TRUE;
2343 END IF;
2344 END LOOP;
2345 END IF;
2346
2347 IF l_call_apply = TRUE THEN
2348 -- We need to derive the conversion rate if not provided for cross currency application
2349 OPEN c_fetch_inv_currency(l_final_appl_tbl(i).inv_hdr_rec.invoice_id);
2350 FETCH c_fetch_inv_currency INTO l_invoice_currency_code;
2351 CLOSE c_fetch_inv_currency;
2352
2353 IF l_invoice_currency_code <> l_rcpt_rec.currency_code AND l_final_appl_tbl(i).inv_hdr_rec.trans_to_receipt_rate IS NULL
2354 THEN
2355 --Bug 7613040, by nikshah
2356 --Modified usage of profile with AR system options
2357 l_exchange_rate_type := cross_currency_rate_type(l_org_id); --fnd_profile.VALUE ('AR_CROSS_CURRENCY_RATE_TYPE');
2358 IF l_exchange_rate_type IS NULL
2359 THEN
2360 okl_api.set_message
2361 (p_app_name => g_app_name,
2362 p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
2363 );
2364 RAISE g_exception_halt_validation;
2365 ELSE
2366 l_trans_to_receipt_rate :=
2367 okl_accounting_util.get_curr_con_rate
2368 (l_invoice_currency_code,
2369 l_rcpt_rec.currency_code,
2370 l_rcpt_rec.receipt_Date,
2371 l_exchange_rate_type
2372 );
2373 IF l_trans_to_receipt_rate IN (0, -1)
2374 THEN
2375 -- Message Text: No exchange rate defined
2376 x_return_status := okc_api.g_ret_sts_error;
2377 okc_api.set_message
2378 (p_app_name => g_app_name,
2379 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE'
2380 );
2381 RAISE g_exception_halt_validation;
2382 END IF;
2383 l_final_appl_tbl(i).inv_hdr_rec.trans_to_receipt_rate := l_trans_to_receipt_rate;
2384
2385 END IF;
2386 END IF;
2387
2388 apply_receipt( p_api_version => l_api_version
2389 ,p_init_msg_list => l_init_msg_list
2390 ,x_return_status => l_return_status
2391 ,x_msg_count => l_msg_count
2392 ,x_msg_data => l_msg_data
2393 ,p_cash_receipt_id => l_cash_receipt_id
2394 ,p_customer_trx_id => l_final_appl_tbl(i).inv_hdr_rec.invoice_id
2395 ,p_freight_amount => l_freight_amount
2396 ,p_charges_amount => l_charges_amount
2397 ,p_llca_tbl => l_final_appl_tbl(i).inv_lines_tbl
2398 ,p_apply_gl_date => l_final_appl_tbl(i).inv_hdr_rec.gl_date
2399 ,p_org_id => l_org_id
2400 ,p_receipt_date => l_rcpt_rec.receipt_date
2401 ,p_gl_date => l_rcpt_rec.gl_date
2402 ,p_trans_to_receipt_rate => l_final_appl_tbl(i).inv_hdr_rec.trans_to_receipt_rate
2403 );
2404
2405 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2406 RAISE okl_api.g_exception_unexpected_error;
2407 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2408 RAISE okl_api.g_exception_error;
2409 END IF;
2410 END IF;
2411 ELSE
2412 l_final_appl_tbl(i).inv_hdr_rec.trans_to_receipt_rate := NULL;
2413 END IF;
2414
2415
2416
2417 END LOOP;
2418 END IF;
2419 log_debug('Before handle_onacc_update l_original_onacc_amount : '||l_original_onacc_amount);
2420 log_debug('Before handle_onacc_update l_apply_onacc_amount : '||l_apply_onacc_amount);
2421 --Begin - Handle On-Account Application
2422 If l_original_onacc_amount <> l_apply_onacc_amount Then
2423 --Validate the On-Account Amount
2424 IF l_appl_tbl.count = 0 THEN
2425 If l_rcpt_rec.amount < l_apply_onacc_amount THEN
2426 OKL_API.set_message( p_app_name => G_APP_NAME,
2427 p_msg_name => 'OKL_RCPT_TOT_AMT_GT_UNAPP_AMT');
2428
2429 RAISE G_EXCEPTION_HALT_VALIDATION;
2430 END IF;
2431 END IF;
2432
2433 handle_onacc_update(
2434 p_api_version => l_api_version
2435 ,p_init_msg_list => l_init_msg_list
2436 ,x_return_status => l_return_status
2437 ,x_msg_count => l_msg_count
2438 ,x_msg_data => l_msg_data
2439 ,p_cash_receipt_id => l_cash_receipt_id
2440 ,p_org_id => l_org_id
2441 ,p_original_onacc_amount => l_original_onacc_amount
2442 ,p_apply_onacc_amount => l_apply_onacc_amount
2443 ,p_receipt_date => l_rcpt_rec.receipt_date
2444 ,p_gl_date => l_rcpt_rec.gl_date);
2445
2446 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2447 RAISE okl_api.g_exception_unexpected_error;
2448 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2449 RAISE okl_api.g_exception_error;
2450 END IF;
2451 END IF;
2452
2453 --End - Handle On-Account Application
2454
2455 okl_api.end_activity(l_msg_count,l_msg_data);
2456 x_msg_data := l_msg_data;
2457 x_msg_count := l_msg_count;
2458 x_return_status := l_return_status;
2459 log_debug('okl_receipts_pvt.handle_receipt End -');
2460 EXCEPTION
2461 WHEN g_exception_halt_validation THEN
2462 x_return_status := okl_api.g_ret_sts_error;
2463 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2464 (
2465 l_api_name,
2466 G_PKG_NAME,
2467 'OKL_API.G_RET_STS_ERROR',
2468 x_msg_count,
2469 x_msg_data,
2470 '_PVT'
2471 );
2472 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2473 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2474 (
2475 l_api_name,
2476 G_PKG_NAME,
2477 'OKL_API.G_RET_STS_ERROR',
2478 x_msg_count,
2479 x_msg_data,
2480 '_PVT'
2481 );
2482
2483 WHEN OTHERS THEN
2484 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2485 (
2486 l_api_name,
2487 G_PKG_NAME,
2488 'OTHERS',
2489 x_msg_count,
2490 x_msg_data,
2491 '_PVT'
2492 );
2493 Okl_api.set_message( p_app_name => g_app_name
2494 , p_msg_name => g_unexpected_error
2495 , p_token1 => g_sqlcode_token
2496 , p_token1_value => SQLCODE
2497 , p_token2 => g_sqlerrm_token
2498 , p_token2_value => SQLERRM
2499 ) ;
2500 END handle_receipt;
2501
2502 FUNCTION cross_currency_rate_type
2503 (p_org_id IN NUMBER)
2504 RETURN VARCHAR2
2505 IS
2506 l_cc_rate_type VARCHAR2(30);
2507 CURSOR c_ar_rate_type(p_org_id NUMBER) IS
2508 SELECT cross_currency_rate_type
2509 FROM AR_SYSTEM_PARAMETERS_ALL
2510 WHERE org_id=p_org_id;
2511
2512 BEGIN
2513
2514 OPEN c_ar_rate_type(p_org_id);
2515 FETCH c_ar_rate_type INTO l_cc_rate_type;
2516 CLOSE c_ar_rate_type;
2517
2518 RETURN l_cc_rate_type;
2519 EXCEPTION
2520 WHEN OTHERS THEN
2521 Okl_Api.Set_Message(p_app_name => Okl_Api.G_APP_NAME,
2522 p_msg_name => 'OKL_UNEXPECTED_ERROR',
2523 p_token1 => 'OKL_SQLCODE',
2524 p_token1_value => SQLCODE,
2525 p_token2 => 'OKL_SQLERRM',
2526 p_token2_value => SQLERRM);
2527 RETURN NULL;
2528
2529 END cross_currency_rate_type;
2530
2531 END okl_receipts_pvt;