[Home] [Help]
PACKAGE BODY: APPS.OKL_RECEIPTS_PVT
Source
1 PACKAGE BODY OKL_RECEIPTS_PVT AS
2 /* $Header: OKLRRCTB.pls 120.39.12010000.2 2008/12/05 05:49:00 nikshah 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 BEGIN
1238 log_debug('okl_receipts_pvt.apply_receipt start');
1239 l_return_status := OKL_API.G_RET_STS_SUCCESS;
1240
1241 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
1242 l_return_status := OKL_API.START_ACTIVITY(
1243 p_api_name => l_api_name
1244 , p_pkg_name => G_PKG_NAME
1245 , p_init_msg_list => p_init_msg_list
1246 , l_api_version => l_api_version
1247 , p_api_version => p_api_version
1248 , p_api_type => '_PVT'
1249 , x_return_status => x_return_status);
1250 -- check if activity started successfully
1251 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1252 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1253 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1254 RAISE OKL_API.G_EXCEPTION_ERROR;
1255 END IF;
1256
1257 IF l_llca_tbl.COUNT > 0 AND l_llca_tbl(l_llca_tbl.FIRST).invoice_line_id IS NOT NULL THEN
1258 OPEN c_get_cust_trx_id(l_llca_tbl(l_llca_tbl.FIRST).invoice_line_id);
1259 FETCH c_get_cust_trx_id INTO l_customer_trx_id;
1260 CLOSE c_get_cust_trx_id;
1261 END IF;
1262
1263 OPEN c_fetch_receipt_date(l_cash_receipt_id);
1264 FETCH c_fetch_receipt_date INTO l_rcpt_date;
1265 CLOSE c_fetch_receipt_date;
1266
1267 OPEN c_fetch_inv_due_date(l_customer_trx_id);
1268 FETCH c_fetch_inv_due_date INTO l_inv_due_date;
1269 CLOSE c_fetch_inv_due_date;
1270
1271 IF trunc(l_inv_due_date) < trunc(l_rcpt_date) THEN
1272 l_apply_rcpt_date := l_rcpt_date;
1273 ELSE
1274 l_apply_rcpt_date := l_inv_due_date;
1275 END IF;
1276
1277 /* IF trunc(l_rcpt_date) > trunc(sysdate) THEN
1278 OKL_API.set_message( p_app_name => G_APP_NAME,
1279 p_msg_name => 'OKL_RCPT_INVLD_APPLN_DATE');
1280 RAISE OKL_API.G_EXCEPTION_ERROR;
1281 END IF;
1282 */
1283
1284 --udhenuko Commented this code as we fetch the conversion rate directly as input.
1285 /*OPEN c_get_trans_to_receipt_rate(l_cash_receipt_id, l_customer_trx_id);
1286 FETCH c_get_trans_to_receipt_rate INTO l_trans_to_receipt_rate;
1287 CLOSE c_get_trans_to_receipt_rate;*/
1288
1289 log_debug('calling unapply receipt');
1290 -- unapply any existing application
1291 unapply_receipt( p_api_version => l_api_version,
1292 p_init_msg_list => l_init_msg_list,
1293 x_return_status => l_return_status,
1294 x_msg_count => l_msg_count,
1295 x_msg_data => l_msg_data,
1296 p_cash_receipt_id => l_cash_receipt_id,
1297 p_customer_trx_id => l_customer_trx_id,
1298 p_org_id => l_org_id);
1299 log_debug('return status of unapply_receipt = '||l_return_status);
1300 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1301 RAISE okl_api.g_exception_unexpected_error;
1302 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1303 RAISE okl_api.g_exception_error;
1304 END IF;
1305
1306 log_debug('l_llca_tbl.count = '||l_llca_tbl.COUNT);
1307 IF l_llca_tbl.COUNT > 0 THEN
1308 FOR i IN l_llca_tbl.FIRST..l_llca_tbl.LAST LOOP
1309 IF l_llca_tbl.exists(i) THEN
1310 IF l_llca_tbl(i).amount_applied <> 0 THEN
1311 l_ar_llca_tbl(i).customer_trx_line_id := l_llca_tbl(i).invoice_line_id;
1312 l_ar_llca_tbl(i).amount_applied := l_llca_tbl(i).amount_applied;
1313 l_ar_llca_tbl(i).amount_applied_from := l_llca_tbl(i).amount_applied_from;
1314 l_ar_llca_tbl(i).line_amount := l_llca_tbl(i).line_applied; --dkagrawa
1315 l_ar_llca_tbl(i).tax_amount := l_llca_tbl(i).tax_applied;
1316 log_debug('l_ar_llca_tbl('|| i ||').customer_trx_line_id = '||l_ar_llca_tbl(i).customer_trx_line_id);
1317 log_debug('l_ar_llca_tbl('|| i ||').amount_applied = '||l_ar_llca_tbl(i).amount_applied);
1318 log_debug('l_ar_llca_tbl('|| i ||').amount_applied_from = '||l_ar_llca_tbl(i).amount_applied_from);
1319 log_debug('l_ar_llca_tbl('|| i ||').amount_applied_from = '||l_ar_llca_tbl(i).line_amount);
1320 log_debug('l_ar_llca_tbl('|| i ||').amount_applied_from = '||l_ar_llca_tbl(i).tax_amount);
1321 END IF;
1322 END IF;
1323 END LOOP;
1324
1325
1326 IF l_ar_llca_tbl.COUNT = 0 AND
1327 (NVL(p_freight_amount,0) <> 0 OR NVL(p_charges_amount,0) <> 0) THEN
1328 i := l_llca_tbl.FIRST;
1329 l_ar_llca_tbl(i).customer_trx_line_id := l_llca_tbl(i).invoice_line_id;
1330 l_ar_llca_tbl(i).amount_applied := l_llca_tbl(i).amount_applied;
1331 END IF;
1332 log_debug('calling AR_RECEIPT_API_PUB.apply_in_detail');
1333 log_debug('Parameters: p_cash_receipt_id = '||l_cash_receipt_id);
1334 log_debug('p_customer_trx_id = '||l_customer_trx_id);
1335 log_debug('p_apply_gl_date = '||p_apply_gl_date);
1336 log_debug('l_apply_rcpt_date = '||l_apply_rcpt_date);
1337 log_debug('l_trans_to_receipt_rate = '||l_trans_to_receipt_rate);
1338 IF l_ar_llca_tbl.COUNT > 0 THEN
1339 lla_exists := okl_lckbx_csh_app_pvt.get_line_level_app(l_customer_trx_id , l_org_id);
1340 IF NVL(lla_exists,'N') = 'Y' THEN
1341 AR_RECEIPT_API_PUB.apply_in_detail( p_api_version => l_api_version
1342 ,p_init_msg_list => l_init_msg_list
1343 ,x_return_status => l_return_status
1344 ,x_msg_count => l_msg_count
1345 ,x_msg_data => l_msg_data
1346 ,p_cash_receipt_id => l_cash_receipt_id
1347 ,p_customer_trx_id => l_customer_trx_id
1348 ,p_llca_type => 'L'
1349 ,p_llca_trx_lines_tbl => l_ar_llca_tbl
1350 ,p_apply_date => trunc(l_apply_rcpt_date)
1351 ,p_apply_gl_date => p_apply_gl_date
1352 ,p_org_id => l_org_id
1353 ,p_freight_amount => p_freight_amount
1354 ,p_charges_amount => p_charges_amount
1355 ,p_trans_to_receipt_rate => l_trans_to_receipt_rate
1356 );
1357 log_debug('Return status of AR_RECEIPT_API_PUB.apply_in_detail = '|| l_return_status);
1358 ELSE
1359 AR_RECEIPT_API_PUB.apply( p_api_version => l_api_version
1360 ,p_init_msg_list => l_init_msg_list
1361 ,x_return_status => l_return_status
1362 ,x_msg_count => l_msg_count
1363 ,x_msg_data => l_msg_data
1364 ,p_cash_receipt_id => l_cash_receipt_id
1365 ,p_customer_trx_id => l_customer_trx_id
1366 ,p_apply_date => trunc(l_apply_rcpt_date)
1367 ,p_apply_gl_date => p_apply_gl_date
1368 ,p_org_id => l_org_id
1369 ,p_trans_to_receipt_rate => l_trans_to_receipt_rate
1370 ,p_comments => NULL
1371 ,p_amount_applied => l_ar_llca_tbl(l_ar_llca_tbl.FIRST).amount_applied
1372 );
1373 log_debug('Return status of AR_RECEIPT_API_PUB.apply = '|| l_return_status);
1374 END IF;
1375
1376 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1377 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1378 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1379 RAISE OKL_API.G_EXCEPTION_ERROR;
1380 END IF;
1381 END IF;
1382 END IF;
1383
1384 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1385
1386 x_return_status := l_return_status;
1387 log_debug('okl_receipts_pvt.apply_receipt end');
1388 EXCEPTION
1389 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1390 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1391 (
1392 l_api_name,
1393 G_PKG_NAME,
1394 'OKL_API.G_RET_STS_ERROR',
1395 x_msg_count,
1396 x_msg_data,
1397 '_PVT'
1398 );
1399
1400 WHEN OTHERS THEN
1401 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1402 (
1403 l_api_name,
1404 G_PKG_NAME,
1405 'OTHERS',
1406 x_msg_count,
1407 x_msg_data,
1408 '_PVT'
1409 );
1410 Okl_api.set_message( p_app_name => g_app_name
1411 , p_msg_name => g_unexpected_error
1412 , p_token1 => g_sqlcode_token
1413 , p_token1_value => SQLCODE
1414 , p_token2 => g_sqlerrm_token
1415 , p_token2_value => SQLERRM
1416 ) ;
1417
1418 END apply_receipt;
1419
1420 /************************************************************************************
1421 -- Procedure to Handle Updation of On-Account Amount from Application Page
1422 ************************************************************************************/
1423
1424 PROCEDURE handle_onacc_update( p_api_version IN NUMBER
1425 ,p_init_msg_list IN VARCHAR2 DEFAULT OkL_Api.G_FALSE
1426 ,x_return_status OUT NOCOPY VARCHAR2
1427 ,x_msg_count OUT NOCOPY NUMBER
1428 ,x_msg_data OUT NOCOPY VARCHAR2
1429 ,p_cash_receipt_id IN AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE
1430 ,p_org_id IN NUMBER
1431 ,p_original_onacc_amount IN NUMBER
1432 ,p_apply_onacc_amount IN NUMBER
1433 ,p_receipt_date IN DATE
1434 ,p_gl_date IN DATE
1435 ) IS
1436
1437 ---------------------------
1438 -- DECLARE Local Variables
1439 ---------------------------
1440
1441 l_api_version NUMBER := 1.0;
1442 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
1443 l_return_status VARCHAR2(1);
1444 l_msg_count NUMBER;
1445 l_msg_data VARCHAR2(2000);
1446 l_api_name CONSTANT VARCHAR2(30) := 'handle_onacc_update';
1447
1448 l_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT p_cash_receipt_id;
1449 l_receivable_application_id AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID%TYPE DEFAULT NULL;
1450 l_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE DEFAULT p_gl_date;
1451 l_org_id NUMBER := p_org_id;
1452 l_apply_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
1453 i NUMBER DEFAULT NULL;
1454 k NUMBER DEFAULT NULL;
1455 l_counter NUMBER;
1456 l_record_count NUMBER DEFAULT NULL;
1457 l_rcpt_date DATE DEFAULT p_receipt_date;
1458 l_inv_due_date DATE DEFAULT NULL;
1459 l_apply_rcpt_date DATE DEFAULT NULL;
1460
1461 --Begain - handle onaccount - varangan - 6353486
1462 --Application Id for ON-Account record
1463 CURSOR c_get_onacc_app_id ( p_cash_rcpt_id IN NUMBER) IS
1464 SELECT NVL(RECEIVABLE_APPLICATION_ID,0)
1465 FROM AR_RECEIVABLE_APPLICATIONS_ALL
1466 WHERE CASH_RECEIPT_ID = p_cash_rcpt_id
1467 AND STATUS = 'ACC'
1468 AND APPLICATION_TYPE ='CASH'
1469 AND DISPLAY ='Y'
1470 ORDER BY CREATION_DATE desc ;
1471
1472 l_original_onacc_amount NUMBER DEFAULT p_original_onacc_amount;
1473 l_apply_onacc_amount NUMBER DEFAULT p_apply_onacc_amount;
1474 l_onacc_appplication_id NUMBER;
1475 l_receipt_date DATE DEFAULT p_receipt_date;
1476 --End - handle onaccount - varangan - 6353486
1477
1478 BEGIN
1479 log_debug('okl_receipts_pvt.Unapply_on_account start');
1480 l_return_status := OKL_API.G_RET_STS_SUCCESS;
1481
1482 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
1483 l_return_status := OKL_API.START_ACTIVITY(
1484 p_api_name => l_api_name
1485 , p_pkg_name => G_PKG_NAME
1486 , p_init_msg_list => p_init_msg_list
1487 , l_api_version => l_api_version
1488 , p_api_version => p_api_version
1489 , p_api_type => '_PVT'
1490 , x_return_status => x_return_status);
1491 -- check if activity started successfully
1492 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1493 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1494 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1495 RAISE OKL_API.G_EXCEPTION_ERROR;
1496 END IF;
1497
1498
1499 --Check if there is existing on-account amount
1500 If (Nvl(l_original_onacc_amount,0) > 0 ) Then
1501 -- Get onaccount application Id
1502 l_onacc_appplication_id := NULL;
1503 OPEN c_get_onacc_app_id(l_cash_receipt_id);
1504 FETCH c_get_onacc_app_id INTO l_onacc_appplication_id;
1505 CLOSE c_get_onacc_app_id;
1506
1507 IF l_onacc_appplication_id IS NOT NULL THEN
1508 -- Unapply the on-account amount
1509 Ar_receipt_api_pub.Unapply_on_account( p_api_version => l_api_version,
1510 p_init_msg_list => l_init_msg_list,
1511 x_return_status => l_return_status ,
1512 x_msg_count => l_msg_count ,
1513 x_msg_data => l_msg_data,
1514 p_cash_receipt_id => l_cash_receipt_id,
1515 p_receivable_application_id =>l_onacc_appplication_id,
1516 p_reversal_gl_date => NULL,
1517 p_org_id => l_org_id
1518 );
1519 log_debug('Return status of AR_RECEIPT_API_PUB.Unapply_on_account = '|| l_return_status);
1520 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1521 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1522 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1523 RAISE OKL_API.G_EXCEPTION_ERROR;
1524 END IF;
1525 END IF;
1526 END IF;
1527 -- Check if any On-Account amount to be applied
1528 If ( Nvl(l_apply_onacc_amount,0) > 0 ) Then
1529 log_debug('Ar_receipt_api_pub.Apply_on_account l_cash_receipt_id '||l_cash_receipt_id);
1530 log_debug('Ar_receipt_api_pub.Apply_on_account l_apply_onacc_amount '||l_apply_onacc_amount);
1531 log_debug('Ar_receipt_api_pub.Apply_on_account l_rcpt_date '||l_rcpt_date);
1532 log_debug('Ar_receipt_api_pub.Apply_on_account l_gl_date '||l_gl_date);
1533 -- Apply the on-account amount
1534 Ar_receipt_api_pub.Apply_on_account( p_api_version => l_api_version
1535 ,p_init_msg_list => l_init_msg_list
1536 ,x_return_status => l_return_status
1537 ,x_msg_count => l_msg_count
1538 ,x_msg_data => l_msg_data
1539 ,p_cash_receipt_id => l_cash_receipt_id
1540 ,p_amount_applied => l_apply_onacc_amount
1541 ,p_apply_date => l_rcpt_date
1542 ,p_apply_gl_date => l_gl_date
1543 ,p_org_id => l_org_id
1544 );
1545 log_debug('Return status of AR_RECEIPT_API_PUB.Apply_on_account = '|| l_return_status);
1546 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1547 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1548 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1549 RAISE OKL_API.G_EXCEPTION_ERROR;
1550 END IF;
1551 END IF;
1552
1553 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1554
1555 x_return_status := l_return_status;
1556 log_debug('okl_receipts_pvt.handle_onacc_update end');
1557 EXCEPTION
1558 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1559 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1560 (
1561 l_api_name,
1562 G_PKG_NAME,
1563 'OKL_API.G_RET_STS_ERROR',
1564 x_msg_count,
1565 x_msg_data,
1566 '_PVT'
1567 );
1568
1569 WHEN OTHERS THEN
1570 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1571 (
1572 l_api_name,
1573 G_PKG_NAME,
1574 'OTHERS',
1575 x_msg_count,
1576 x_msg_data,
1577 '_PVT'
1578 );
1579 Okl_api.set_message( p_app_name => g_app_name
1580 , p_msg_name => g_unexpected_error
1581 , p_token1 => g_sqlcode_token
1582 , p_token1_value => SQLCODE
1583 , p_token2 => g_sqlerrm_token
1584 , p_token2_value => SQLERRM
1585 ) ;
1586
1587 END handle_onacc_update;
1588
1589
1590 PROCEDURE handle_receipt
1591 (p_api_version IN NUMBER,
1592 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
1593 x_return_status OUT NOCOPY VARCHAR2,
1594 x_msg_count OUT NOCOPY NUMBER,
1595 x_msg_data OUT NOCOPY VARCHAR2,
1596 p_rcpt_rec IN RCPT_REC_TYPE,
1597 p_appl_tbl IN APPL_TBL_TYPE,
1598 x_cash_receipt_id OUT NOCOPY NUMBER)
1599 IS
1600
1601 l_rcpt_rec RCPT_REC_TYPE DEFAULT p_rcpt_rec;
1602 l_api_version NUMBER := 1.0;
1603 l_init_msg_list VARCHAR2(1) := okl_api.g_true;
1604 l_return_status VARCHAR2(1);
1605 l_msg_count NUMBER;
1606 l_msg_data VARCHAR2(2000);
1607 l_api_name CONSTANT VARCHAR2(30) := 'handle_receipt';
1608 l_onacc_appplication_id NUMBER;
1609
1610 l_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE DEFAULT p_rcpt_rec.cash_receipt_id;
1611 p_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE DEFAULT p_rcpt_rec.cash_receipt_id;
1612 l_header_count NUMBER;
1613 l_line_counter NUMBER;
1614 l_select_yn VARCHAR2(1);
1615 l_final_count NUMBER;
1616 l_call_apply BOOLEAN;
1617 l_call_unapply BOOLEAN;
1618
1619 l_currency_code ar_cash_receipts.currency_code%TYPE:= p_rcpt_rec.currency_code;
1620 l_customer_number hz_cust_accounts.account_number%TYPE:=p_rcpt_rec.customer_number;
1621 l_org_id NUMBER:= p_rcpt_rec.org_id;
1622 l_contract_number okc_k_headers_all_b.contract_number%TYPE;
1623 l_cons_inv_number okl_cnsld_ar_hdrs_all_b.consolidated_invoice_number%TYPE;
1624 l_ar_inv_number ra_customer_trx_all.trx_number%TYPE;
1625 l_ar_invoice_line_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE;
1626
1627
1628 l_appl_tbl APPL_TBL_TYPE DEFAULT p_appl_tbl;
1629 l_okl_rcpt_tbl line_appl_tbl_type;
1630 x_okl_rcpt_tbl line_appl_tbl_type;
1631 l_final_appl_tbl line_appl_tbl_type;
1632 l_inv_lns_tbl okl_auto_cash_appl_rules_pvt.okl_inv_line_tbl_type;
1633 l_init_inv_lines_tbl okl_auto_cash_appl_rules_pvt.okl_inv_line_tbl_type;
1634
1635 l_rctv_rec Okl_Rct_Pvt.rctv_rec_type;
1636 l_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
1637 x_rctv_rec Okl_Rct_Pvt.rctv_rec_type;
1638 x_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
1639
1640 l_rxh_rec okl_sla_acc_sources_pvt.rxh_rec_type;
1641
1642 l_apply_onacc_amount NUMBER:=0;
1643 l_unapply_amount NUMBER:=0;
1644 l_original_onacc_amount NUMBER:=0;
1645 l_freight_amount NUMBER DEFAULT NULL;
1646 l_charges_amount NUMBER DEFAULT NULL;
1647 l_rcpt_status VARCHAR2(50);
1648 x_status VARCHAR2(50);
1649
1650 l_trans_to_receipt_rate NUMBER;
1651 l_exchange_rate_type VARCHAR2(45);
1652 l_invoice_currency_code VARCHAR2(45);
1653
1654
1655 --Application Id for ON-Account record
1656 CURSOR c_get_onacc_app_id ( cp_cash_rcpt_id IN NUMBER) IS
1657 SELECT NVL(RECEIVABLE_APPLICATION_ID,0)
1658 FROM AR_RECEIVABLE_APPLICATIONS_ALL
1659 WHERE CASH_RECEIPT_ID = cp_cash_rcpt_id
1660 AND STATUS = 'ACC'
1661 AND APPLICATION_TYPE ='CASH'
1662 AND DISPLAY ='Y'
1663 ORDER BY CREATION_DATE desc ;
1664
1665 CURSOR c_cont_num( cp_contract_id IN NUMBER
1666 ,cp_org_id IN NUMBER
1667 ) IS
1668 SELECT contract_number
1669 FROM OKC_K_HEADERS_ALL_B
1670 WHERE id = cp_contract_id
1671 AND org_id = cp_org_id;
1672
1673 CURSOR c_cons_inv_num( cp_con_inv_id IN NUMBER
1674 ,cp_org_id IN NUMBER
1675 ) IS
1676 SELECT consolidated_invoice_number
1677 FROM okl_cnsld_ar_hdrs_all_b
1678 WHERE id = cp_con_inv_id
1679 AND org_id = cp_org_id;
1680
1681 CURSOR c_ar_inv_num( cp_ar_inv_id IN NUMBER
1682 ,cp_org_id IN NUMBER
1683 ) IS
1684 SELECT trx_number
1685 FROM ra_customer_trx_all
1686 WHERE customer_trx_id = cp_ar_inv_id
1687 AND org_id = cp_org_id;
1688
1689 CURSOR c_ar_inv_line_id(cp_ar_inv_id IN NUMBER, cp_org_id IN NUMBER) IS
1690 SELECT CUSTOMER_TRX_LINE_ID
1691 FROM RA_CUSTOMER_TRX_LINES_ALL
1692 WHERE CUSTOMER_TRX_ID = cp_ar_inv_id
1693 AND ORG_ID = cp_org_id
1694 AND LINE_TYPE = 'LINE'
1695 AND ROWNUM = 1;
1696
1697 CURSOR get_rcpt_sts(cp_cash_receipt_id IN NUMBER) IS
1698 SELECT status
1699 FROM ar_Cash_receipts_all
1700 WHERE cash_receipt_id = cp_cash_receipt_id;
1701
1702 CURSOR c_fetch_inv_currency (p_customer_trx_id IN NUMBER) IS
1703 SELECT a.invoice_currency_code
1704 FROM ra_customer_trx_all a
1705 WHERE a.customer_trx_id = p_customer_trx_id;
1706
1707 BEGIN
1708 log_debug('okl_receipts_pvt.handle_receipts start +');
1709
1710 l_return_status := okl_api.start_activity(l_api_name,
1711 g_pkg_name,
1712 p_init_msg_list,
1713 l_api_version,
1714 p_api_version,
1715 '_PVT',
1716 l_return_status);
1717
1718 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1719 RAISE okl_api.g_exception_unexpected_error;
1720 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1721 RAISE okl_api.g_exception_error;
1722 END IF;
1723 log_debug('calling validate_receipt_details');
1724 validate_receipt_details( x_return_status => l_return_status,
1725 p_rcpt_rec => l_rcpt_rec);
1726 log_debug( 'RETURN Status FROM validate_receipt_details = '|| l_return_status);
1727 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1728 RAISE okl_api.g_exception_unexpected_error;
1729 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1730 RAISE okl_api.g_exception_error;
1731 END IF;
1732
1733 IF l_rcpt_rec.cash_receipt_id is NULL OR l_rcpt_rec.cash_receipt_id = okl_api.g_miss_num THEN
1734 log_debug('l_rcpt_rec.CREATE_MODE = '||l_rcpt_rec.CREATE_MODE);
1735 IF l_rcpt_rec.CREATE_MODE = 'ONACCOUNT' OR l_rcpt_rec.CREATE_MODE = 'ADVANCED' THEN
1736 log_debug('calling create_onaccount_receipt');
1737 create_onaccount_receipt(p_api_version => l_api_version,
1738 p_init_msg_list => l_init_msg_list,
1739 x_return_status => l_return_status,
1740 x_msg_count => l_msg_count,
1741 x_msg_data => l_msg_data,
1742 p_rcpt_rec => l_rcpt_rec,
1743 x_cash_receipt_id => x_cash_receipt_id);
1744 log_debug( 'RETURN Status FROM create_onaccount_receipt = '|| l_return_status);
1745 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1746 RAISE okl_api.g_exception_unexpected_error;
1747 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1748 RAISE okl_api.g_exception_error;
1749 END IF;
1750 l_cash_receipt_id:=x_cash_receipt_id;
1751 ELSIF l_rcpt_rec.CREATE_MODE = 'UNAPPLIED' THEN -- This mode is for both Unapplied and Unidentified receipts
1752 log_debug('calling create_unapplied_receipt');
1753 create_unapplied_receipt(p_api_version => l_api_version,
1754 p_init_msg_list => l_init_msg_list,
1755 x_return_status => l_return_status,
1756 x_msg_count => l_msg_count,
1757 x_msg_data => l_msg_data,
1758 p_rcpt_rec => l_rcpt_rec,
1759 x_cash_receipt_id => x_cash_receipt_id);
1760 log_debug( 'RETURN Status FROM create_unapplied_receipt = '|| l_return_status);
1761 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1762 RAISE okl_api.g_exception_unexpected_error;
1763 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1764 RAISE okl_api.g_exception_error;
1765 END IF;
1766 l_cash_receipt_id:=x_cash_receipt_id;
1767 END IF;
1768
1769 IF l_rcpt_rec.CREATE_MODE = 'ADVANCED' THEN
1770 log_debug( 'GL date not equal = ');
1771 IF l_appl_tbl.count > 1 THEN
1772 OKL_API.set_message( p_app_name => G_APP_NAME,
1773 p_msg_name => 'OKL_RCPT_ONE_CONTRACT_REQD');
1774 RAISE G_EXCEPTION_HALT_VALIDATION;
1775 END IF;
1776 END IF;
1777 ELSE
1778 x_cash_receipt_id := l_rcpt_rec.cash_receipt_id;
1779 populate_receipt_rec(l_rcpt_rec);
1780 OPEN get_rcpt_sts(l_rcpt_rec.cash_receipt_id);
1781 FETCH get_rcpt_sts INTO l_rcpt_status;
1782 CLOSE get_rcpt_sts;
1783 IF l_rcpt_status = 'UNID' THEN
1784 IF l_rcpt_rec.customer_id IS NOT NULL AND l_rcpt_rec.customer_id <> okl_api.g_miss_num THEN
1785
1786 Ar_receipt_update_api_pub.update_receipt_unid_to_unapp(
1787 p_api_version => l_api_version
1788 ,p_init_msg_list => l_init_msg_list
1789 ,x_return_status => l_return_status
1790 ,x_msg_count => l_msg_count
1791 ,x_msg_data => l_msg_data
1792 ,p_cash_receipt_id => l_rcpt_rec.cash_receipt_id
1793 ,p_pay_from_customer => l_rcpt_rec.customer_id
1794 ,x_status => x_status
1795 ,p_customer_bank_account_id => l_rcpt_rec.customer_bank_account_id
1796 );
1797 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1798 RAISE okl_api.g_exception_unexpected_error;
1799 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1800 RAISE okl_api.g_exception_error;
1801 END IF;
1802 END IF;
1803 END IF;
1804 END IF;
1805 -- Capture Original and Updated On-Account Amount
1806 l_original_onacc_amount:= l_rcpt_rec.p_original_onacc_amount;
1807 l_apply_onacc_amount := l_rcpt_rec.p_apply_onacc_amount;
1808
1809
1810 IF l_appl_tbl.count > 0 THEN
1811 l_header_count:=0;
1812 l_line_counter:=0;
1813 FOR i IN l_appl_tbl.FIRST..l_appl_tbl.LAST LOOP
1814 l_select_yn:='N';
1815 l_inv_lns_tbl:=l_init_inv_lines_tbl;
1816 IF l_rcpt_rec.CREATE_MODE = 'ADVANCED' THEN
1817 l_rctv_rec.IRM_ID := l_rcpt_rec.receipt_method_id;
1818 l_rctv_rec.ILE_ID := l_rcpt_rec.customer_id;
1819 l_rctv_rec.CHECK_NUMBER := l_rcpt_rec.receipt_number;
1820 l_rctv_rec.AMOUNT := l_rcpt_rec.amount;
1821 l_rctv_rec.CURRENCY_CODE := l_rcpt_rec.currency_code;
1822 l_rctv_rec.EXCHANGE_RATE := l_rcpt_rec.EXCHANGE_RATE;
1823 l_rctv_rec.EXCHANGE_RATE_TYPE := l_rcpt_rec.EXCHANGE_RATE_TYPE;
1824 l_rctv_rec.EXCHANGE_RATE_DATE := l_rcpt_rec.EXCHANGE_DATE;
1825 l_rctv_rec.DATE_EFFECTIVE := l_rcpt_rec.receipt_Date;
1826 l_rctv_rec.GL_DATE := l_rcpt_rec.gl_date;
1827 l_rctv_rec.ORG_ID := l_rcpt_rec.org_id;
1828 l_rctv_rec.RECEIPT_TYPE := 'ADV';
1829 l_rctv_rec.cash_receipt_id := l_cash_receipt_id;
1830 l_rctv_rec.expired_flag := 'N';
1831 l_rctv_rec.fully_Applied_flag := 'N';
1832
1833 l_rcav_tbl(1).KHR_ID := l_appl_tbl(i).contract_id;
1834 l_rcav_tbl(1).ILE_ID := l_rcpt_rec.customer_id;
1835 l_rcav_tbl(1).AMOUNT := l_rcpt_rec.amount;
1836 l_rcav_tbl(1).ORG_ID := l_rcpt_rec.org_id;
1837 log_debug('calling Okl_Rct_Pub.create_internal_trans');
1838 Okl_Rct_Pub.create_internal_trans (l_api_version
1839 ,l_init_msg_list
1840 ,l_return_status
1841 ,l_msg_count
1842 ,l_msg_data
1843 ,l_rctv_rec
1844 ,l_rcav_tbl
1845 ,x_rctv_rec
1846 ,x_rcav_tbl);
1847 log_debug('Return status of Okl_Rct_Pub.create_internal_trans = '|| l_return_status);
1848 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1849 RAISE okl_api.g_exception_unexpected_error;
1850 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1851 RAISE okl_api.g_exception_error;
1852 END IF;
1853 log_debug('l_appl_tbl(i).contract_id : '||l_appl_tbl(i).contract_id);
1854 log_debug('l_appl_tbl(i).con_inv_id : '||l_appl_tbl(i).con_inv_id);
1855 log_debug('l_appl_tbl(i).ar_inv_id : '||l_appl_tbl(i).ar_inv_id);
1856 IF l_appl_tbl(i).contract_id IS NOT NULL and l_appl_tbl(i).contract_id <> okl_api.g_miss_num THEN
1857 l_rxh_rec.source_id := l_cash_receipt_id;
1858 l_rxh_rec.khr_id := l_appl_tbl(i).contract_id;
1859 log_debug(' okl_sla_acc_sources_pvt.populate_sources');
1860 okl_sla_acc_sources_pvt.populate_sources(
1861 p_api_version => l_api_version
1862 ,p_init_msg_list => l_init_msg_list
1863 ,p_rxh_rec => l_rxh_rec
1864 ,x_return_status => l_return_status
1865 ,x_msg_count => l_msg_count
1866 ,x_msg_data => l_msg_data
1867 );
1868
1869 log_debug('Return status of okl_sla_acc_sources_pvt.populate_sources = '|| l_return_status);
1870 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1871 RAISE okl_api.g_exception_unexpected_error;
1872 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1873 RAISE okl_api.g_exception_error;
1874 END IF;
1875 END IF;
1876 ELSIF l_appl_tbl(i).contract_id is not null then
1877 OPEN c_cont_num(l_appl_tbl(i).contract_id,l_org_id);
1878 FETCH c_cont_num into l_contract_number;
1879 CLOSE C_cont_num;
1880 log_debug('Before okl_auto_cash_appl_rules_pvt.auto_cashapp_for_contract');
1881 okl_auto_cash_appl_rules_pvt.auto_cashapp_for_contract( p_api_version => l_api_version,
1882 p_init_msg_list => l_init_msg_list,
1883 x_return_status => l_return_status,
1884 x_msg_count => l_msg_count,
1885 x_msg_data => l_msg_data,
1886 p_customer_num => l_customer_number,
1887 p_contract_num => l_contract_number,
1888 p_currency_code => l_currency_code,
1889 p_amount_app_to => l_appl_tbl(i).amount_to_apply,
1890 p_amount_app_from => l_appl_tbl(i).amount_applied_from,
1891 p_receipt_date => l_rcpt_rec.receipt_date,
1892 p_org_id => l_org_id,
1893 x_appl_tbl => x_okl_rcpt_tbl,
1894 x_onacc_amount => l_apply_onacc_amount,
1895 x_unapply_amount =>l_unapply_amount
1896 );
1897 log_debug('After okl_auto_cash_appl_rules_pvt.auto_cashapp_for_contract');
1898 log_debug('l_apply_onacc_amount = '||l_apply_onacc_amount);
1899 log_debug('l_unapply_amount = '||l_unapply_amount);
1900 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1901 RAISE okl_api.g_exception_unexpected_error;
1902 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1903 RAISE okl_api.g_exception_error;
1904 END IF;
1905 l_okl_rcpt_tbl:=x_okl_rcpt_tbl;
1906 log_debug( 'l_okl_rcpt_tbl= count '|| x_okl_rcpt_tbl.count);
1907 IF l_final_appl_tbl.COUNT>0 THEN
1908 l_final_count:=l_final_appl_tbl.LAST+1;
1909 FOR k IN l_okl_rcpt_tbl.FIRST..l_okl_rcpt_tbl.LAST LOOP
1910 log_debug('x_okl_rcpt_tbl(k).amount_applied = '||x_okl_rcpt_tbl(k).inv_hdr_rec.amount_applied);
1911 log_debug('x_okl_rcpt_tbl(k).amount_applied_from = '||x_okl_rcpt_tbl(k).inv_hdr_rec.amount_applied_from);
1912 l_final_appl_tbl(l_final_count) := x_okl_rcpt_tbl(k);
1913 l_final_count:=l_final_count+1;
1914 END LOOP;
1915 ELSE
1916 l_final_appl_tbl:=l_okl_rcpt_tbl;
1917 END IF;
1918 log_debug( 'l_final_appl_tbl count'|| l_final_appl_tbl.count);
1919 ELSIF l_appl_tbl(i).con_inv_id is not null then
1920 OPEN c_cons_inv_num(l_appl_tbl(i).con_inv_id,l_org_id);
1921 FETCH c_cons_inv_num into l_cons_inv_number;
1922 CLOSE c_cons_inv_num;
1923 log_debug('Before okl_auto_cash_appl_rules_pvt.auto_cashapp_for_consinv');
1924 okl_auto_cash_appl_rules_pvt.auto_cashapp_for_consinv ( p_api_version => l_api_version,
1925 p_init_msg_list => l_init_msg_list,
1926 x_return_status => l_return_status,
1927 x_msg_count => l_msg_count,
1928 x_msg_data => l_msg_data,
1929 p_customer_num => l_customer_number,
1930 p_cons_inv => l_cons_inv_number,
1931 p_currency_code => l_currency_code,
1932 p_amount_app_to => l_appl_tbl(i).amount_to_apply,
1933 p_amount_app_from => l_appl_tbl(i).amount_applied_from,
1934 p_receipt_date => l_rcpt_rec.receipt_date,
1935 p_org_id => l_org_id,
1936 x_appl_tbl => x_okl_rcpt_tbl,
1937 x_onacc_amount => l_apply_onacc_amount,
1938 x_unapply_amount =>l_unapply_amount
1939 );
1940 log_debug('After okl_auto_cash_appl_rules_pvt.auto_cashapp_for_consinv');
1941 log_debug('l_apply_onacc_amount = '||l_apply_onacc_amount);
1942 log_debug('l_unapply_amount = '||l_unapply_amount);
1943 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1944 RAISE okl_api.g_exception_unexpected_error;
1945 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1946 RAISE okl_api.g_exception_error;
1947 END IF;
1948 l_okl_rcpt_tbl:=x_okl_rcpt_tbl;
1949 IF l_final_appl_tbl.COUNT>0 THEN
1950 l_final_count:=l_final_appl_tbl.LAST+1;
1951 FOR k IN l_okl_rcpt_tbl.FIRST..l_okl_rcpt_tbl.LAST LOOP
1952 log_debug('x_okl_rcpt_tbl(k).amount_applied = '||x_okl_rcpt_tbl(k).inv_hdr_rec.amount_applied);
1953 log_debug('x_okl_rcpt_tbl(k).amount_applied_from = '||x_okl_rcpt_tbl(k).inv_hdr_rec.amount_applied_from);
1954 l_final_appl_tbl(l_final_count) := x_okl_rcpt_tbl(k);
1955 l_final_count:=l_final_count+1;
1956 END LOOP;
1957 ELSE
1958 l_final_appl_tbl:=l_okl_rcpt_tbl;
1959 END IF;
1960
1961 ELSIF l_appl_tbl(i).ar_inv_id is not null and l_appl_tbl(i).line_id is null
1962 and NVL(l_appl_tbl(i).line_type,'LINE') = 'LINE'
1963 and l_rcpt_rec.cash_receipt_id IS NULL then
1964 OPEN c_ar_inv_num(l_appl_tbl(i).ar_inv_id,l_org_id);
1965 FETCH c_ar_inv_num into l_ar_inv_number;
1966 CLOSE c_ar_inv_num;
1967 log_debug('Before okl_auto_cash_appl_rules_pvt.auto_cashapp_for_arinv');
1968 okl_auto_cash_appl_rules_pvt.auto_cashapp_for_arinv ( p_api_version => l_api_version,
1969 p_init_msg_list => l_init_msg_list,
1970 x_return_status => l_return_status,
1971 x_msg_count => l_msg_count,
1972 x_msg_data => l_msg_data,
1973 p_customer_num => l_customer_number,
1974 p_arinv_number => l_ar_inv_number,
1975 p_currency_code => l_currency_code,
1976 p_amount_app_to => l_appl_tbl(i).amount_to_apply,
1977 p_amount_app_from => l_appl_tbl(i).amount_applied_from,
1978 p_receipt_date => l_rcpt_rec.receipt_date,
1979 p_org_id => l_org_id,
1980 x_appl_tbl => x_okl_rcpt_tbl,
1981 x_onacc_amount => l_apply_onacc_amount,
1982 x_unapply_amount =>l_unapply_amount
1983 );
1984 log_debug('After okl_auto_cash_appl_rules_pvt.auto_cashapp_for_arinv');
1985 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1986 RAISE okl_api.g_exception_unexpected_error;
1987 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1988 RAISE okl_api.g_exception_error;
1989 END IF;
1990
1991 log_debug('l_apply_onacc_amount = '||l_apply_onacc_amount);
1992 log_debug('l_unapply_amount = '||l_unapply_amount);
1993 l_okl_rcpt_tbl:=x_okl_rcpt_tbl;
1994 IF l_final_appl_tbl.COUNT>0 THEN
1995 l_final_count:=l_final_appl_tbl.LAST+1;
1996 FOR k IN l_okl_rcpt_tbl.FIRST..l_okl_rcpt_tbl.LAST LOOP
1997 log_debug('x_okl_rcpt_tbl(k).amount_applied = '||x_okl_rcpt_tbl(k).inv_hdr_rec.amount_applied);
1998 log_debug('x_okl_rcpt_tbl(k).amount_applied_from = '||x_okl_rcpt_tbl(k).inv_hdr_rec.amount_applied_from);
1999 l_final_appl_tbl(l_final_count) := x_okl_rcpt_tbl(k);
2000 l_final_count:=l_final_count+1;
2001 END LOOP;
2002 ELSE
2003 l_final_appl_tbl:=l_okl_rcpt_tbl;
2004 END IF;
2005 ELSIF l_appl_tbl(i).ar_inv_id is not null
2006 and (l_appl_tbl(i).line_id is not null
2007 OR l_rcpt_rec.cash_receipt_id IS NOT NULL)
2008 and NVL(l_appl_tbl(i).line_type,'LINE') = 'LINE' then
2009 --- populate l_final_appl_tbl based on ar inv id
2010 -- first time condition
2011 IF l_final_appl_tbl.COUNT <=0 THEN
2012 l_final_appl_tbl(0).inv_hdr_rec.invoice_id:=l_appl_tbl(i).ar_inv_id;
2013 l_final_appl_tbl(0).inv_hdr_rec.gl_date:=l_appl_tbl(i).gl_date;
2014 l_final_appl_tbl(0).inv_hdr_rec.trans_to_receipt_rate:=l_appl_tbl(i).trans_to_receipt_rate;
2015 l_inv_lns_tbl(0).invoice_line_id := l_appl_tbl(i).line_id;
2016 l_inv_lns_tbl(0).amount_applied := l_appl_tbl(i).amount_to_apply;
2017 l_inv_lns_tbl(0).original_applied_amount := l_appl_tbl(i).original_applied_amount;
2018 l_inv_lns_tbl(0).line_applied := l_appl_tbl(i).line_applied;
2019 l_inv_lns_tbl(0).tax_applied := l_appl_tbl(i).tax_applied; --dkagrawa
2020 l_final_appl_tbl(0).inv_lines_tbl := l_inv_lns_tbl;
2021 ELSE
2022 FOR k IN l_final_appl_tbl.first..l_final_appl_tbl.LAST LOOP
2023 IF l_final_appl_tbl(k).inv_hdr_rec.invoice_id=l_appl_tbl(i).ar_inv_id THEN
2024 IF l_final_appl_tbl(k).inv_hdr_rec.gl_date IS NOT NULL AND
2025 l_appl_tbl(i).gl_date IS NOT NULL THEN
2026
2027 IF l_final_appl_tbl(k).inv_hdr_rec.gl_date <> l_appl_tbl(i).gl_date THEN
2028 log_debug( 'GL date not equal = ');
2029 OKL_API.set_message( p_app_name => G_APP_NAME,
2030 p_msg_name => 'OKL_RCPT_INVLD_GL_DATE_FOR_INV');
2031 RAISE G_EXCEPTION_HALT_VALIDATION;
2032 END IF;
2033 END IF;
2034 IF l_final_appl_tbl(k).inv_hdr_rec.trans_to_receipt_rate IS NOT NULL AND
2035 l_appl_tbl(i).trans_to_receipt_rate IS NOT NULL THEN
2036
2037 IF l_final_appl_tbl(k).inv_hdr_rec.trans_to_receipt_rate <> l_appl_tbl(i).trans_to_receipt_rate THEN
2038 log_debug( 'GL date not equal = ');
2039 OKL_API.set_message( p_app_name => G_APP_NAME,
2040 p_msg_name => 'OKL_RCPT_INVLD_RATE_FOR_INV');
2041 RAISE G_EXCEPTION_HALT_VALIDATION;
2042 END IF;
2043 END IF;
2044 l_header_count:=k;
2045 l_line_counter:=(l_final_appl_tbl(k).inv_lines_tbl.LAST+1);
2046 l_line_counter := NVL(l_line_counter,0);
2047 l_select_yn:='Y';
2048 EXIT;
2049 END IF;
2050 END LOOP;
2051
2052 IF l_select_yn='Y' THEN
2053 -- header record already present ,get the lines records in table and add new line
2054 l_inv_lns_tbl:=l_final_appl_tbl(l_header_count).inv_lines_tbl;
2055 l_inv_lns_tbl(l_line_counter).invoice_line_id := l_appl_tbl(i).line_id;
2056 l_inv_lns_tbl(l_line_counter).amount_applied := l_appl_tbl(i).amount_to_apply;
2057 l_inv_lns_tbl(l_line_counter).original_applied_amount := l_appl_tbl(i).original_applied_amount;
2058 l_inv_lns_tbl(l_line_counter).line_applied := l_appl_tbl(i).line_applied;
2059 l_inv_lns_tbl(l_line_counter).tax_applied := l_appl_tbl(i).tax_applied; --dkagrawa
2060 --assign the record back to the receipts table
2061 l_final_appl_tbl(l_header_count).inv_lines_tbl := l_inv_lns_tbl;
2062 ELSE
2063 l_header_count:=(l_final_appl_tbl.LAST+1);
2064 l_line_counter:=0;
2065 l_final_appl_tbl(l_header_count).inv_hdr_rec.invoice_id:=l_appl_tbl(i).ar_inv_id;
2066 l_final_appl_tbl(l_header_count).inv_hdr_rec.gl_date:=l_appl_tbl(i).gl_date;
2067 l_final_appl_tbl(l_header_count).inv_hdr_rec.trans_to_receipt_rate:=l_appl_tbl(i).trans_to_receipt_rate;
2068 l_inv_lns_tbl(l_line_counter).invoice_line_id := l_appl_tbl(i).line_id;
2069 l_inv_lns_tbl(l_line_counter).amount_applied := l_appl_tbl(i).amount_to_apply;
2070 l_inv_lns_tbl(l_line_counter).original_applied_amount := l_appl_tbl(i).original_applied_amount;
2071 l_inv_lns_tbl(l_line_counter).line_applied := l_appl_tbl(i).line_applied;
2072 l_inv_lns_tbl(l_line_counter).tax_applied := l_appl_tbl(i).tax_applied; --dkagrawa
2073 l_final_appl_tbl(l_header_count).inv_lines_tbl := l_inv_lns_tbl;
2074 END IF;
2075
2076 END IF;
2077 --If application line belongs to freight then handle it
2078 --separately
2079 ELSIF NVL(l_appl_tbl(i).line_type,'LINE') IN ('FREIGHT','CHARGES') THEN
2080 --- populate l_final_appl_tbl based on ar inv id
2081 -- first time condition
2082 IF l_final_appl_tbl.COUNT <=0 THEN
2083 l_final_appl_tbl(0).inv_hdr_rec.invoice_id:=l_appl_tbl(i).ar_inv_id;
2084 l_final_appl_tbl(0).inv_hdr_rec.gl_date:=l_appl_tbl(i).gl_date;
2085 l_final_appl_tbl(0).inv_hdr_rec.trans_to_receipt_rate:=l_appl_tbl(i).trans_to_receipt_rate;
2086 IF l_appl_tbl(i).line_type = 'FREIGHT' THEN
2087 l_final_appl_tbl(0).inv_hdr_rec.freight_amount := l_appl_tbl(i).amount_to_apply;
2088 ELSIF l_appl_tbl(i).line_type = 'CHARGES' THEN
2089 l_final_appl_tbl(0).inv_hdr_rec.charges_amount := l_appl_tbl(i).amount_to_apply;
2090 END IF;
2091 ELSE
2092 FOR k IN l_final_appl_tbl.first..l_final_appl_tbl.LAST LOOP
2093 IF l_final_appl_tbl(k).inv_hdr_rec.invoice_id=l_appl_tbl(i).ar_inv_id THEN
2094 IF l_final_appl_tbl(k).inv_hdr_rec.gl_date IS NOT NULL AND
2095 l_appl_tbl(i).gl_date IS NOT NULL THEN
2096 IF l_final_appl_tbl(k).inv_hdr_rec.gl_date <> l_appl_tbl(i).gl_date THEN
2097 OKL_API.set_message( p_app_name => G_APP_NAME,
2098 p_msg_name => 'OKL_RCPT_INVLD_GL_DATE_FOR_INV');
2099 RAISE G_EXCEPTION_HALT_VALIDATION;
2100 END IF;
2101 END IF;
2102 IF l_final_appl_tbl(k).inv_hdr_rec.trans_to_receipt_rate IS NOT NULL AND
2103 l_appl_tbl(i).trans_to_receipt_rate IS NOT NULL THEN
2104 IF l_final_appl_tbl(k).inv_hdr_rec.trans_to_receipt_rate <> l_appl_tbl(i).trans_to_receipt_rate THEN
2105 OKL_API.set_message( p_app_name => G_APP_NAME,
2106 p_msg_name => 'OKL_RCPT_INVLD_RATE_FOR_INV');
2107 RAISE G_EXCEPTION_HALT_VALIDATION;
2108 END IF;
2109 END IF;
2110 IF l_appl_tbl(i).line_type = 'FREIGHT' THEN
2111 l_final_appl_tbl(k).inv_hdr_rec.freight_amount :=
2112 NVL(l_final_appl_tbl(k).inv_hdr_rec.freight_amount,0) +
2113 l_appl_tbl(i).amount_to_apply;
2114 ELSIF l_appl_tbl(i).line_type = 'CHARGES' THEN
2115 l_final_appl_tbl(k).inv_hdr_rec.charges_amount :=
2116 NVL(l_final_appl_tbl(k).inv_hdr_rec.charges_amount,0) +
2117 l_appl_tbl(i).amount_to_apply;
2118 END IF;
2119 l_select_yn := 'Y';
2120 EXIT;
2121 END IF;
2122 END LOOP;
2123 IF l_select_yn = 'N' THEN
2124 l_header_count := l_final_appl_tbl.LAST + 1;
2125 l_final_appl_tbl(l_header_count).inv_hdr_rec.invoice_id:=l_appl_tbl(i).ar_inv_id;
2126 l_final_appl_tbl(l_header_count).inv_hdr_rec.gl_date:=l_appl_tbl(i).gl_date;
2127 l_final_appl_tbl(l_header_count).inv_hdr_rec.trans_to_receipt_rate:=l_appl_tbl(i).trans_to_receipt_rate;
2128 IF l_appl_tbl(i).line_type = 'FREIGHT' THEN
2129 l_final_appl_tbl(l_header_count).inv_hdr_rec.freight_amount := l_appl_tbl(i).amount_to_apply;
2130 ELSIF l_appl_tbl(i).line_type = 'CHARGES' THEN
2131 l_final_appl_tbl(l_header_count).inv_hdr_rec.charges_amount := l_appl_tbl(i).amount_to_apply;
2132 END IF;
2133 END IF;
2134
2135 END IF;
2136 END IF;
2137 END LOOP;
2138 END IF;
2139 -- Updating the On-Acc variables, if it is null/g_miss_num
2140 If (l_original_onacc_amount Is Null) Or (l_original_onacc_amount = okl_api.g_miss_num) Then
2141 l_original_onacc_amount:= 0;
2142 End If;
2143 If (l_apply_onacc_amount Is Null) Or (l_apply_onacc_amount = okl_api.g_miss_num) Then
2144 l_apply_onacc_amount:=0;
2145 End If;
2146
2147 log_debug('In the final count before looping'||l_final_appl_tbl.COUNT);
2148 IF l_final_appl_tbl.COUNT > 0 THEN
2149 log_debug('calling validate_receipt_applications');
2150
2151 IF p_rcpt_rec.cash_receipt_id IS NOT NULL THEN
2152 p_cash_receipt_id := p_rcpt_rec.cash_receipt_id;
2153 ELSE
2154 p_cash_receipt_id := x_cash_receipt_id;
2155 END IF;
2156 /* -- Commenting the Overapplication validation to let AR validate the applications
2157 validate_receipt_applications(
2158 p_cash_rcpt_id => p_cash_receipt_id,
2159 p_appl_tbl => l_final_appl_tbl,
2160 P_onacc_amount => l_apply_onacc_amount,
2161 x_return_status => l_return_status );
2162
2163 log_debug( 'RETURN Status FROM validate_receipt_applications = '|| l_return_status);
2164 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2165 RAISE okl_api.g_exception_unexpected_error;
2166 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2167 RAISE okl_api.g_exception_error;
2168 END IF;
2169 */
2170 -- unapply all the applications first which are changing, so that the amount is available for application
2171 FOR i IN l_final_appl_tbl.FIRST..l_final_appl_tbl.LAST LOOP
2172 --If freight amount or charges amount is there then check whether
2173 --any invoice lines are there or not. If no then create one invoice line
2174 --with applied amount 0. Otherwise keep as it is.
2175
2176 l_freight_amount := l_final_appl_tbl(i).inv_hdr_rec.freight_amount;
2177 IF l_freight_amount = OKL_API.G_MISS_NUM OR l_freight_amount = 0 THEN
2178 l_freight_amount := NULL;
2179 END IF;
2180 l_charges_amount := l_final_appl_tbl(i).inv_hdr_rec.charges_amount;
2181 IF l_charges_amount = OKL_API.G_MISS_NUM OR l_charges_amount = 0 THEN
2182 l_charges_amount := NULL;
2183 END IF;
2184 IF (l_freight_amount IS NOT NULL OR l_charges_amount IS NOT NULL) AND
2185 l_final_appl_tbl(i).inv_lines_tbl.count = 0 THEN
2186 l_inv_lns_tbl.delete;
2187 OPEN c_ar_inv_line_id(l_final_appl_tbl(i).inv_hdr_rec.invoice_id, l_org_id);
2188 FETCH c_ar_inv_line_id INTO l_ar_invoice_line_id;
2189 CLOSE c_ar_inv_line_id;
2190 l_inv_lns_tbl(0).invoice_line_id := l_ar_invoice_line_id;
2191 l_inv_lns_tbl(0).amount_applied := 0;
2192 l_final_appl_tbl(i).inv_lines_tbl := l_inv_lns_tbl;
2193 END IF;
2194 log_debug('In the final count');
2195
2196 IF l_final_appl_tbl(i).inv_lines_tbl.count <> 0 then
2197 l_call_unapply := FALSE;
2198 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
2199 l_call_unapply := TRUE;
2200 ELSE
2201 FOR ll IN l_final_appl_tbl(i).inv_lines_tbl.FIRST..l_final_appl_tbl(i).inv_lines_tbl.LAST LOOP
2202 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
2203 l_call_unapply := TRUE;
2204 END IF;
2205 END LOOP;
2206 END IF;
2207
2208 IF l_call_unapply = TRUE THEN
2209 unapply_receipt( p_api_version => l_api_version,
2210 p_init_msg_list => l_init_msg_list,
2211 x_return_status => l_return_status,
2212 x_msg_count => l_msg_count,
2213 x_msg_data => l_msg_data,
2214 p_cash_receipt_id => l_cash_receipt_id,
2215 p_customer_trx_id => l_final_appl_tbl(i).inv_hdr_rec.invoice_id,
2216 p_org_id => l_org_id);
2217 log_debug('return status of unapply_receipt = '||l_return_status);
2218 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2219 RAISE okl_api.g_exception_unexpected_error;
2220 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2221 RAISE okl_api.g_exception_error;
2222 END IF;
2223 END IF;
2224 END IF;
2225 END LOOP;
2226 --Check if there is existing on-account amount
2227 If l_original_onacc_amount <> l_apply_onacc_amount Then
2228 If (Nvl(l_original_onacc_amount,0) > 0 ) Then
2229 -- Get onaccount application Id
2230 OPEN c_get_onacc_app_id(l_cash_receipt_id);
2231 FETCH c_get_onacc_app_id INTO l_onacc_appplication_id;
2232 CLOSE c_get_onacc_app_id;
2233
2234 -- Unapply the on-account amount
2235 Ar_receipt_api_pub.Unapply_on_account( p_api_version => l_api_version,
2236 p_init_msg_list => l_init_msg_list,
2237 x_return_status => l_return_status ,
2238 x_msg_count => l_msg_count ,
2239 x_msg_data => l_msg_data,
2240 p_cash_receipt_id => l_cash_receipt_id,
2241 p_receivable_application_id =>l_onacc_appplication_id,
2242 p_reversal_gl_date => NULL,
2243 p_org_id => l_org_id
2244 );
2245 log_debug('Return status of AR_RECEIPT_API_PUB.Unapply_on_account = '|| l_return_status);
2246 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2247 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2248 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2249 RAISE OKL_API.G_EXCEPTION_ERROR;
2250 END IF;
2251 END IF;
2252 END IF;
2253 FOR i IN l_final_appl_tbl.FIRST..l_final_appl_tbl.LAST LOOP
2254 --If freight amount or charges amount is there then check whether
2255 --any invoice lines are there or not. If no then create one invoice line
2256 --with applied amount 0. Otherwise keep as it is.
2257 --this code has been moved in above loop where we do unapplication, so commenting it out here
2258 /* l_freight_amount := l_final_appl_tbl(i).inv_hdr_rec.freight_amount;
2259 IF l_freight_amount = OKL_API.G_MISS_NUM OR l_freight_amount = 0 THEN
2260 l_freight_amount := NULL;
2261 END IF;
2262 l_charges_amount := l_final_appl_tbl(i).inv_hdr_rec.charges_amount;
2263 IF l_charges_amount = OKL_API.G_MISS_NUM OR l_charges_amount = 0 THEN
2264 l_charges_amount := NULL;
2265 END IF;
2266 IF (l_freight_amount IS NOT NULL OR l_charges_amount IS NOT NULL) AND
2267 l_final_appl_tbl(i).inv_lines_tbl.count = 0 THEN
2268 l_inv_lns_tbl.delete;
2269 OPEN c_ar_inv_line_id(l_final_appl_tbl(i).inv_hdr_rec.invoice_id, l_org_id);
2270 FETCH c_ar_inv_line_id INTO l_ar_invoice_line_id;
2271 CLOSE c_ar_inv_line_id;
2272 l_inv_lns_tbl(0).invoice_line_id := l_ar_invoice_line_id;
2273 l_inv_lns_tbl(0).amount_applied := 0;
2274 l_final_appl_tbl(i).inv_lines_tbl := l_inv_lns_tbl;
2275 END IF;*/
2276 log_debug('In the final count');
2277
2278 IF l_final_appl_tbl(i).inv_lines_tbl.count <> 0 then
2279 l_call_apply := FALSE;
2280 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
2281 l_call_apply := TRUE;
2282 ELSE
2283 FOR ll IN l_final_appl_tbl(i).inv_lines_tbl.FIRST..l_final_appl_tbl(i).inv_lines_tbl.LAST LOOP
2284 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
2285 l_call_apply := TRUE;
2286 END IF;
2287 END LOOP;
2288 END IF;
2289
2290 IF l_call_apply = TRUE THEN
2291 -- We need to derive the conversion rate if not provided for cross currency application
2292 OPEN c_fetch_inv_currency(l_final_appl_tbl(i).inv_hdr_rec.invoice_id);
2293 FETCH c_fetch_inv_currency INTO l_invoice_currency_code;
2294 CLOSE c_fetch_inv_currency;
2295
2296 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
2297 THEN
2298 --Bug 7613040, by nikshah
2299 --Modified usage of profile with AR system options
2300 l_exchange_rate_type := cross_currency_rate_type(l_org_id); --fnd_profile.VALUE ('AR_CROSS_CURRENCY_RATE_TYPE');
2301 IF l_exchange_rate_type IS NULL
2302 THEN
2303 okl_api.set_message
2304 (p_app_name => g_app_name,
2305 p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
2306 );
2307 RAISE g_exception_halt_validation;
2308 ELSE
2309 l_trans_to_receipt_rate :=
2310 okl_accounting_util.get_curr_con_rate
2311 (l_invoice_currency_code,
2312 l_rcpt_rec.currency_code,
2313 l_rcpt_rec.receipt_Date,
2314 l_exchange_rate_type
2315 );
2316 IF l_trans_to_receipt_rate IN (0, -1)
2317 THEN
2318 -- Message Text: No exchange rate defined
2319 x_return_status := okc_api.g_ret_sts_error;
2320 okc_api.set_message
2321 (p_app_name => g_app_name,
2322 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE'
2323 );
2324 RAISE g_exception_halt_validation;
2325 END IF;
2326 l_final_appl_tbl(i).inv_hdr_rec.trans_to_receipt_rate := l_trans_to_receipt_rate;
2327
2328 END IF;
2329 END IF;
2330
2331 apply_receipt( p_api_version => l_api_version
2332 ,p_init_msg_list => l_init_msg_list
2333 ,x_return_status => l_return_status
2334 ,x_msg_count => l_msg_count
2335 ,x_msg_data => l_msg_data
2336 ,p_cash_receipt_id => l_cash_receipt_id
2337 ,p_customer_trx_id => l_final_appl_tbl(i).inv_hdr_rec.invoice_id
2338 ,p_freight_amount => l_freight_amount
2339 ,p_charges_amount => l_charges_amount
2340 ,p_llca_tbl => l_final_appl_tbl(i).inv_lines_tbl
2341 ,p_apply_gl_date => l_final_appl_tbl(i).inv_hdr_rec.gl_date
2342 ,p_org_id => l_org_id
2343 ,p_receipt_date => l_rcpt_rec.receipt_date
2344 ,p_gl_date => l_rcpt_rec.gl_date
2345 ,p_trans_to_receipt_rate => l_final_appl_tbl(i).inv_hdr_rec.trans_to_receipt_rate
2346 );
2347
2348 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2349 RAISE okl_api.g_exception_unexpected_error;
2350 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2351 RAISE okl_api.g_exception_error;
2352 END IF;
2353 END IF;
2354 ELSE
2355 l_final_appl_tbl(i).inv_hdr_rec.trans_to_receipt_rate := NULL;
2356 END IF;
2357
2358
2359
2360 END LOOP;
2361 END IF;
2362 log_debug('Before handle_onacc_update l_original_onacc_amount : '||l_original_onacc_amount);
2363 log_debug('Before handle_onacc_update l_apply_onacc_amount : '||l_apply_onacc_amount);
2364 --Begin - Handle On-Account Application
2365 If l_original_onacc_amount <> l_apply_onacc_amount Then
2366 --Validate the On-Account Amount
2367 IF l_appl_tbl.count = 0 THEN
2368 If l_rcpt_rec.amount < l_apply_onacc_amount THEN
2369 OKL_API.set_message( p_app_name => G_APP_NAME,
2370 p_msg_name => 'OKL_RCPT_TOT_AMT_GT_UNAPP_AMT');
2371
2372 RAISE G_EXCEPTION_HALT_VALIDATION;
2373 END IF;
2374 END IF;
2375
2376 handle_onacc_update(
2377 p_api_version => l_api_version
2378 ,p_init_msg_list => l_init_msg_list
2379 ,x_return_status => l_return_status
2380 ,x_msg_count => l_msg_count
2381 ,x_msg_data => l_msg_data
2382 ,p_cash_receipt_id => l_cash_receipt_id
2383 ,p_org_id => l_org_id
2384 ,p_original_onacc_amount => l_original_onacc_amount
2385 ,p_apply_onacc_amount => l_apply_onacc_amount
2386 ,p_receipt_date => l_rcpt_rec.receipt_date
2387 ,p_gl_date => l_rcpt_rec.gl_date);
2388
2389 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2390 RAISE okl_api.g_exception_unexpected_error;
2391 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2392 RAISE okl_api.g_exception_error;
2393 END IF;
2394 END IF;
2395
2396 --End - Handle On-Account Application
2397
2398 okl_api.end_activity(l_msg_count,l_msg_data);
2399 x_msg_data := l_msg_data;
2400 x_msg_count := l_msg_count;
2401 x_return_status := l_return_status;
2402 log_debug('okl_receipts_pvt.handle_receipt End -');
2403 EXCEPTION
2404 WHEN g_exception_halt_validation THEN
2405 x_return_status := okl_api.g_ret_sts_error;
2406 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2407 (
2408 l_api_name,
2409 G_PKG_NAME,
2410 'OKL_API.G_RET_STS_ERROR',
2411 x_msg_count,
2412 x_msg_data,
2413 '_PVT'
2414 );
2415 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2416 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2417 (
2418 l_api_name,
2419 G_PKG_NAME,
2420 'OKL_API.G_RET_STS_ERROR',
2421 x_msg_count,
2422 x_msg_data,
2423 '_PVT'
2424 );
2425
2426 WHEN OTHERS THEN
2427 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2428 (
2429 l_api_name,
2430 G_PKG_NAME,
2431 'OTHERS',
2432 x_msg_count,
2433 x_msg_data,
2434 '_PVT'
2435 );
2436 Okl_api.set_message( p_app_name => g_app_name
2437 , p_msg_name => g_unexpected_error
2438 , p_token1 => g_sqlcode_token
2439 , p_token1_value => SQLCODE
2440 , p_token2 => g_sqlerrm_token
2441 , p_token2_value => SQLERRM
2442 ) ;
2443 END handle_receipt;
2444
2445 FUNCTION cross_currency_rate_type
2446 (p_org_id IN NUMBER)
2447 RETURN VARCHAR2
2448 IS
2449 l_cc_rate_type VARCHAR2(30);
2450 CURSOR c_ar_rate_type(p_org_id NUMBER) IS
2451 SELECT cross_currency_rate_type
2452 FROM AR_SYSTEM_PARAMETERS_ALL
2453 WHERE org_id=p_org_id;
2454
2455 BEGIN
2456
2457 OPEN c_ar_rate_type(p_org_id);
2458 FETCH c_ar_rate_type INTO l_cc_rate_type;
2459 CLOSE c_ar_rate_type;
2460
2461 RETURN l_cc_rate_type;
2462 EXCEPTION
2463 WHEN OTHERS THEN
2464 Okl_Api.Set_Message(p_app_name => Okl_Api.G_APP_NAME,
2465 p_msg_name => 'OKL_UNEXPECTED_ERROR',
2466 p_token1 => 'OKL_SQLCODE',
2467 p_token1_value => SQLCODE,
2468 p_token2 => 'OKL_SQLERRM',
2469 p_token2_value => SQLERRM);
2470 RETURN NULL;
2471
2472 END cross_currency_rate_type;
2473
2474 END okl_receipts_pvt;