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