1 Package Body AR_DEPOSIT_VAL_PVT AS
2 /* $Header: ARXDEPVB.pls 120.5 2011/07/19 02:15:53 dgaurab ship $ */
3
4 --Validation procedures are contained in this package
5
6 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
7 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
8 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
9 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
10 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
11 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
12
13 PROCEDURE Validate_deposit_Date(p_deposit_date IN DATE,
14 p_return_status OUT NOCOPY VARCHAR2) IS
15 BEGIN
16 arp_util.debug('AR_DEPOSIT_VAL_PVT.Validate_deposit_Date()+');
17
18 p_return_status := FND_API.G_RET_STS_SUCCESS;
19
20 arp_util.debug('AR_DEPOSIT_VAL_PVT.Validate_deposit_Date()-');
21 END Validate_deposit_Date;
22
23 PROCEDURE Validate_batch_source(p_batch_source_id IN NUMBER ,
24 p_return_status OUT NOCOPY VARCHAR2) IS
25 l_dummy NUMBER :=NULL;
26 BEGIN
27 arp_util.debug('AR_DEPOSIT_VAL_PVT.Validate_batch_source()+');
28
29 p_return_status := FND_API.G_RET_STS_SUCCESS;
30 if ar_deposit_lib_pvt.pg_profile_batch_source is null and
31 p_batch_source_id is null
32 then
33 FND_MESSAGE.set_name( 'AR', 'AR_DAPI_NO_BATCH' );
34 FND_MSG_PUB.Add;
35 p_return_status := FND_API.G_RET_STS_ERROR;
36 end if;
37
38
39 BEGIN
40 IF p_batch_source_id is not null THEN
41 SELECT batch_source_id
42 INTO l_dummy
43 FROM ra_batch_sources
44 WHERE batch_source_id = p_batch_source_id;
45 END IF;
46 EXCEPTION
47 WHEN no_data_found THEN
48 arp_util.debug('EXCEPTION: no_data_found
49 Validate_batch_source() ');
50 FND_MESSAGE.set_name( 'AR', 'AR_DAPI_COMM_BATCH_INVALID' );
51 FND_MSG_PUB.Add;
52 p_return_status := FND_API.G_RET_STS_ERROR;
53
54
55 WHEN others THEN
56 arp_util.debug('EXCEPTION:others Validate_batch_source() ');
57 p_return_status := FND_API.G_RET_STS_ERROR;
58 RAISE;
59
60 END;
61
62 arp_util.debug('AR_DEPOSIT_VAL_PVT.Validate_batch_source()-');
63 END Validate_batch_source;
64
65
66 PROCEDURE Validate_Gl_Date(p_gl_date IN DATE,
67 p_return_status OUT NOCOPY VARCHAR2) IS
68 BEGIN
69 arp_util.debug('AR_DEPOSIT_VAL_PVT.Validate_Gl_Date ()+');
70 p_return_status := FND_API.G_RET_STS_SUCCESS;
71
72 IF ( NOT arp_util.is_gl_date_valid( p_gl_date )) THEN
73 FND_MESSAGE.set_name( 'AR', 'AR_INVALID_APP_GL_DATE' );
74 --Int'l Calendar Project
75 FND_MESSAGE.set_token( 'GL_DATE', fnd_date.date_to_chardate(p_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
76 FND_MSG_PUB.Add;
77 p_return_status := FND_API.G_RET_STS_ERROR;
78 END IF;
79 arp_util.debug('AR_DEPOSIT_VAL_PVT.Validate_Gl_Date ()+');
80 END Validate_Gl_Date;
81
82
83 PROCEDURE Validate_amount(p_amount IN NUMBER,
84 p_return_status OUT NOCOPY VARCHAR2) IS
85 BEGIN
86 arp_util.debug('AR_DEPOSIT_VAL_PVT.Validate_amount () +');
87 p_return_status := FND_API.G_RET_STS_SUCCESS;
88
89 --Raise error if the deposit amount is null or negative
90 IF p_amount IS NULL THEN
91 p_return_status := FND_API.G_RET_STS_ERROR;
92 FND_MESSAGE.SET_NAME('AR','AR_DAPI_COMM_AMOUNT_NULL');
93 FND_MSG_PUB.Add;
94
95 ElSIF (p_amount < 0)
96 THEN
97 p_return_status := FND_API.G_RET_STS_ERROR;
98 FND_MESSAGE.SET_NAME('AR','AR_TW_COMMIT_AMOUNT_NEGATIVE');
99 FND_MSG_PUB.Add;
100
101 END IF;
102
103 arp_util.debug('AR_DEPOSIT_VAL_PVT.Validate_amount () -');
104 END Validate_amount;
105
106 PROCEDURE Validate_Exchange_Rate(
107 p_currency_code IN VARCHAR2,
108 p_exchange_rate_type IN VARCHAR2,
109 p_exchange_rate IN NUMBER,
110 p_exchange_rate_date IN DATE,
111 p_return_status OUT NOCOPY VARCHAR2) IS
112 l_euro_to_emu_rate NUMBER;
113 l_cross_rate NUMBER;
114 l_conversion_rate NUMBER;
115 l_exchange_rate_valid varchar2(2);
116 BEGIN
117 arp_util.debug('AR_DEPOSIT_VAL_PVT.Validate_Exchange_Rate () +');
118 p_return_status := FND_API.G_RET_STS_SUCCESS;
119
120 IF p_currency_code <> arp_global.functional_currency THEN
121
122 IF p_exchange_rate_type IS NULL THEN
123
124 -- raise exception
125 p_return_status := FND_API.G_RET_STS_ERROR;
126 FND_MESSAGE.SET_NAME('AR','AR_RAPI_X_RATE_TYPE_NULL');
127 FND_MSG_PUB.Add;
128 Return;
129
130 ELSE
131 -- Validate the rate_type against the database values
132 -- if invalid then return
133 BEGIN
134 SELECT 'Y'
135 INTO l_exchange_rate_valid
136 FROM gl_daily_conversion_types
137 WHERE conversion_type = p_exchange_rate_type;
138 EXCEPTION
139 WHEN no_data_found THEN
140 p_return_status := FND_API.G_RET_STS_ERROR;
141 FND_MESSAGE.SET_NAME('AR','AR_RAPI_X_RATE_TYPE_INVALID');
142 FND_MSG_PUB.Add;
143 Return;
144 END;
145
146 IF p_exchange_rate_type = 'User' THEN
147
148 IF p_exchange_rate IS NULL THEN
149 --*** raise error message, because for rate_type 'User'
150 --*** the rate should be specified.
151 p_return_status := FND_API.G_RET_STS_ERROR;
152 FND_MESSAGE.SET_NAME('AR','AR_RAPI_X_RATE_NULL');
153 FND_MSG_PUB.Add;
154 ELSIF p_exchange_rate = 0 THEN
155 p_return_status := FND_API.G_RET_STS_ERROR ;
156 FND_MESSAGE.SET_NAME('AR','AR_EXCHANGE_RATE_ZERO');
157 FND_MSG_PUB.Add;
158 ELSIF p_exchange_rate < 0 THEN
159 p_return_status := FND_API.G_RET_STS_ERROR ;
160 FND_MESSAGE.SET_NAME('AR','AR_EXCHANGE_RATE_NEGATIVE');
161 FND_MSG_PUB.Add;
162 END IF;
163
164 ELSE
165 --this is the case where rate_type <> 'User'
166 IF p_exchange_rate IS NULL THEN
167 --This could happen only in case if the defaulting routines
168 --could not get the exchange_rate
169 --raise an error message in that case
170
171 p_return_status := FND_API.G_RET_STS_ERROR;
172 FND_MESSAGE.SET_NAME('AR','AR_NO_RATE_DATA_FOUND');
173 FND_MSG_PUB.Add;
174 END IF;
175
176 END IF;
177
178 END IF;
179 ELSE
180 --the functional and the entered currency are same
181 --so there should be no exchange_rate information
182
183 IF (p_exchange_rate IS NOT NULL) THEN
184 p_return_status := FND_API.G_RET_STS_ERROR ;
185 FND_MESSAGE.SET_NAME('AR','AR_RAPI_X_RATE_INVALID');
186 FND_MSG_PUB.Add;
187 END IF;
188 IF (p_exchange_rate_type IS NOT NULL) THEN
189 p_return_status := FND_API.G_RET_STS_ERROR ;
190 FND_MESSAGE.SET_NAME('AR','AR_RAPI_X_RATE_TYPE_INVALID');
191 FND_MSG_PUB.Add;
192 END IF;
193 IF (p_exchange_rate_date IS NOT NULL) THEN
194 p_return_status := FND_API.G_RET_STS_ERROR ;
195 FND_MESSAGE.SET_NAME('AR','AR_RAPI_X_RATE_DATE_INVALID');
196 FND_MSG_PUB.Add;
197 END IF;
198 END IF;
199 arp_util.debug('AR_DEPOSIT_VAL_PVT.Validate_Exchange_Rate () -');
200 EXCEPTION
201 WHEN others THEN
202 arp_util.debug('EXCEPTION: Validate_Exchange_Rate() ');
203 arp_util.debug('p_exchange_rate_type = '
204 ||p_exchange_rate_type);
205 RAISE;
206 END Validate_Exchange_Rate;
207
208 FUNCTION Is_currency_valid(p_currency_code IN
209 ra_customer_trx.invoice_currency_code%TYPE)
210 RETURN VARCHAR2 IS
211 l_currency_valid VARCHAR2(1);
212 BEGIN
213 arp_util.debug('AR_DEPOSIT_VAL_PVT.Is_currency_valid () +');
214 SELECT 'Y'
215 INTO l_currency_valid
216 FROM fnd_currencies
217 WHERE p_currency_code = currency_code;
218 arp_util.debug('AR_DEPOSIT_VAL_PVT.Is_currency_valid () -');
219
220 RETURN(l_currency_valid);
224 l_currency_valid := 'N';
221
222 EXCEPTION
223 WHEN no_data_found THEN
225 RETURN(l_currency_valid);
226 WHEN others THEN
227 arp_util.debug('EXCEPTION: Validate_Exchange_Rate() ');
228 arp_util.debug('p_currency_code = '||p_currency_code);
229 raise;
230 END Is_currency_valid;
231
232 PROCEDURE Validate_Currency(
233 p_currency_code in ra_customer_trx.invoice_currency_code%TYPE,
234 p_exchange_rate_type IN ra_customer_trx.exchange_rate_type%TYPE,
235 p_exchange_rate IN ra_customer_trx.exchange_rate%TYPE,
236 p_exchange_rate_date IN ar_cash_receipts.exchange_date%TYPE,
237 p_return_status OUT NOCOPY VARCHAR2) IS
238 BEGIN
239 p_return_status := FND_API.G_RET_STS_SUCCESS;
240 arp_util.debug('AR_DEPOSIT_VAL_PVT.Validate_Currency () +');
241 IF (Is_currency_valid(p_currency_code) = 'Y') THEN
242
243 IF ((arp_global.functional_currency <> p_currency_code) OR
244 (p_exchange_rate_type IS NOT NULL OR
245 p_exchange_rate IS NOT NULL OR
246 p_exchange_rate_date IS NOT NULL)) THEN
247
248 Validate_Exchange_Rate(p_currency_code,
249 p_exchange_rate_type,
250 p_exchange_rate,
251 p_exchange_rate_date,
252 p_return_status);
253 END IF;
254 ELSE
255 --the entered currency is invalid
256 p_return_status := FND_API.G_RET_STS_ERROR;
257 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CURR_CODE_INVALID');
258 FND_MSG_PUB.Add;
259 END IF;
260 arp_util.debug('AR_DEPOSIT_VAL_PVT.Validate_Currency () -');
261 END Validate_Currency;
262
263 /*========================================================================
264 | PUBLIC PROCEDURE Validate_Deposit
265 |
266 | DESCRIPTION
267 | Enter a brief description of what the package procedure does.
268 | ----------------------------------------
269 | This procedure does the following ...... |
270 | Perform some of basic validation
271 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
272 | Enter a list of all local procedures and functions which
273 | are call this package.
274 |
275 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
276 | Enter a list of all local procedures and funtions which
277 | this package calls.
278 | Validate_deposit_Date
279 | Validate_batch_source'
280 | Validate_Gl_Date
281 | Validate_amount
282 | Validate_Exchange_Rate
283 | Is_currency_valid
284 | Validate_Currency
285 | arp_util.debug(
286 | FND_MESSAGE.SET_NAME
287 | FND_MSG_PUB.Add;
288 |
289 | PARAMETERS
290 | Parameter Type Description
291 | p_batch_source_id IN Batch source
292 | p_deposit_date IN Deposit date
293 | p_gl_date IN Gl Date
294 | p_doc_sequence_value IN Doc seq no value
295 | p_amount IN Deposit amount
296 | p_currency_code IN Currenct code
297 | p_exchange_rate_type IN Exchange type
298 | p_exchange_rate IN Exchange rate
299 | p_exchange_rate_date IN Exchange rate date
300 | p_printing_option IN Printing option
301 | p_status_trx IN Transaction status
302 | p_default_tax_exempt_flag IN Tax exempt flag
303 | p_financial_charges IN Financial Charges
304 | p_return_status OUT NOCOPY Return Status
305 |
306 | NOTES
307 | Any interesting aspect of the code in the package body which needs
308 | to be stated.
309 |
310 | MODIFICATION HISTORY
311 | Date Author Description of Changes
312 | 21-MAY-2001 Anuj Created
313 | DD-MON-YYYY Name Bug #####, modified amount ..
314 |
315 *=======================================================================*/
316
317 PROCEDURE Validate_Deposit(
318 p_batch_source_id IN ra_batch_sources.batch_source_id%type,
319 p_deposit_date IN date,
320 p_gl_date IN date,
321 p_doc_sequence_value IN ra_customer_trx.doc_sequence_value%type,
322 p_amount IN ra_customer_trx_lines.extended_amount%type,
323 p_currency_code IN ra_customer_trx.invoice_currency_code%TYPE,
324 p_exchange_rate_type IN ra_customer_trx.exchange_rate_type%TYPE,
325 p_exchange_rate IN ra_customer_trx.exchange_rate%TYPE,
326 p_exchange_rate_date IN ra_customer_trx.exchange_date%TYPE,
327 p_printing_option IN VARCHAR2,
328 p_status_trx IN VARCHAR2,
329 p_default_tax_exempt_flag IN VARCHAR2,
330 p_financial_charges IN VARCHAR2 DEFAULT NULL,
331 p_return_status OUT NOCOPY VARCHAR2)
332 IS
333 l_deposit_date_return_status VARCHAR2(1);
334 l_gl_date_return_status VARCHAR2(1);
335 l_batch_return_status VARCHAR2(1);
336 l_amount_return_status VARCHAR2(1);
337 l_currency_return_status VARCHAR2(1);
338 l_doc_seq_return_status VARCHAR2(1);
339 l_po_return_status VARCHAR2(1);
340 l_status_trx_return_status VARCHAR2(1);
341 l_fc_return_status VARCHAR2(1);
342
343 cursor c_status_cur is SELECT lookup_code CODE
344 FROM AR_LOOKUPS
345 WHERE LOOKUP_TYPE = 'INVOICE_TRX_STATUS';
346
347 c_status_rec c_status_cur%rowtype;
348 c_status_result VARCHAR2(1) := 'N';
349
350 cursor c_tax_flag_cur is
354 lookup_code <>
351 select lookup_code CODE
352 from ar_lookups
353 where lookup_type = 'TAX_CONTROL_FLAG' and
355 decode(nvl(ar_deposit_lib_pvt.pg_profile_trxln_excpt_flag,'Y'),
356 'Y', '!@#$', 'N', 'E');
357 c_tax_flag c_tax_flag_cur%rowtype;
358 l_tax_flag_return_status VARCHAR2(1);
359 c_tax_flag_result VARCHAR2(1) := 'N';
360
361 BEGIN
362 arp_util.debug('AR_DEPOSIT_VAL_PVT.Validate_Deposit () +');
363
364 p_return_status := FND_API.G_RET_STS_SUCCESS;
365 l_deposit_date_return_status := FND_API.G_RET_STS_SUCCESS;
366 l_gl_date_return_status := FND_API.G_RET_STS_SUCCESS;
367 l_batch_return_status := FND_API.G_RET_STS_SUCCESS;
368 l_amount_return_status := FND_API.G_RET_STS_SUCCESS;
369 l_currency_return_status := FND_API.G_RET_STS_SUCCESS;
370 l_doc_seq_return_status := FND_API.G_RET_STS_SUCCESS;
371 l_po_return_status := FND_API.G_RET_STS_SUCCESS;
372 l_status_trx_return_status := FND_API.G_RET_STS_SUCCESS;
373 l_fc_return_status := FND_API.G_RET_STS_SUCCESS;
374
375
376
377 --validate batch source
378 Validate_batch_source(p_batch_source_id,l_batch_return_status);
379 arp_util.debug('l_batch_return_status : '||l_batch_return_status);
380 --Validate deposit_Date
381
382 Validate_deposit_Date(p_deposit_date,
383 l_deposit_date_return_status);
384
385
386 --Validate gl_date
387
388 Validate_Gl_Date(p_gl_date,
389 l_gl_date_return_status);
390 arp_util.debug('l_gl_date_return_status : '||l_gl_date_return_status);
391
392
393 --Validate document sequence value
394
395 IF(NVL(ar_deposit_lib_pvt.pg_profile_doc_seq, 'N') = 'N' ) AND
396 p_doc_sequence_value IS NOT NULL
397 THEN
398 l_doc_seq_return_status := FND_API.G_RET_STS_ERROR ;
399 FND_MESSAGE.SET_NAME('AR','AR_RAPI_DOC_SEQ_VAL_INVALID');
400 FND_MSG_PUB.Add;
401 END IF;
402
403 --Validate amount
404 Validate_amount(p_amount ,
405 l_amount_return_status);
406 arp_util.debug('l_amount_return_status : '||l_amount_return_status);
407
408 --Validate Customer info
409
410
411 --Validate currency and exchange rate info.
412 IF p_currency_code <> arp_global.functional_currency OR
413 p_exchange_rate_type IS NOT NULL OR
414 p_exchange_rate IS NOT NULL OR
415 p_exchange_rate_date IS NOT NULL THEN
416 Validate_currency(p_currency_code,
417 p_exchange_rate_type,
418 p_exchange_rate,
419 p_exchange_rate_date,
420 l_currency_return_status);
421 END IF;
422 arp_util.debug('l_currency_return_status : '||l_currency_return_status);
423
424 IF p_printing_option not in ('PRI','NOT') and
425 p_printing_option is not null
426 THEN
427 l_po_return_status := FND_API.G_RET_STS_ERROR ;
428 FND_MESSAGE.SET_NAME('AR','AR_DAPI_PO_INVALID');
429 FND_MSG_PUB.Add;
430
431 END IF;
432
433 IF p_financial_charges not in ('Y','N') and
434 p_financial_charges is not null
435 THEN
436 l_fc_return_status := FND_API.G_RET_STS_ERROR ;
437 FND_MESSAGE.SET_NAME('AR','AR_DAPI_FC_INVALID');
438 FND_MSG_PUB.Add;
439
440 END IF;
441
442 --validation transaction status
443 FOR c_status_rec in c_status_cur LOOP
444
445 IF p_status_trx = c_status_rec.code or
446 p_status_trx is null
447 THEN
448 c_status_result := 'Y';
449 END IF;
450
451 END LOOP;
452 IF c_status_result = 'N' THEN
453 l_status_trx_return_status := FND_API.G_RET_STS_ERROR ;
454 FND_MESSAGE.SET_NAME('AR','AR_DAPI_STATUS_TRX_INVALID');
455 FND_MSG_PUB.Add;
456
457 END IF;
458
459 --validation tax flag
460 FOR c_tax_flag_rec in c_tax_flag_cur LOOP
461 IF p_default_tax_exempt_flag = c_tax_flag_rec.code or
462 p_default_tax_exempt_flag is null
463 THEN
464 c_tax_flag_result := 'Y';
465 END IF;
466
467 END LOOP;
468
469 IF c_tax_flag_result = 'N' THEN
470 l_tax_flag_return_status := FND_API.G_RET_STS_ERROR ;
471 FND_MESSAGE.SET_NAME('AR','AR_DAPI_TAX_FLAG_INVALID');
472 FND_MSG_PUB.Add;
473
474 END IF;
475
476
477 IF (l_gl_date_return_status = FND_API.G_RET_STS_ERROR) OR
478 (l_deposit_date_return_status = FND_API.G_RET_STS_ERROR) OR
479 (l_amount_return_status = FND_API.G_RET_STS_ERROR) OR
480 (l_currency_return_status = FND_API.G_RET_STS_ERROR) OR
481 (l_doc_seq_return_status = FND_API.G_RET_STS_ERROR) OR
482 (l_batch_return_status = FND_API.G_RET_STS_ERROR) OR
483 (l_status_trx_return_status = FND_API.G_RET_STS_ERROR) OR
484 (l_po_return_status = FND_API.G_RET_STS_ERROR) OR
485 (l_tax_flag_return_status = FND_API.G_RET_STS_ERROR) OR
486 (l_fc_return_status = FND_API.G_RET_STS_ERROR)
487 THEN
488 p_return_status := FND_API.G_RET_STS_ERROR;
489 END IF;
490 arp_util.debug('Validate_Cash_Receipt Return status :'||p_return_status);
491 arp_util.debug('AR_DEPOSIT_VAL_PVT.Validate_Deposit () -');
492 EXCEPTION
493 WHEN others THEN
494 raise;
495
496 END Validate_deposit;
497
498 END AR_DEPOSIT_VAL_PVT;
[Home] [Help]