[Home] [Help]
PACKAGE BODY: APPS.ARP_TRX_VAL
Source
1 PACKAGE BODY ARP_TRX_VAL AS
2 /* $Header: ARTUVA3B.pls 120.11 2006/08/08 09:49:42 arnkumar ship $ */
3
4 /*===========================================================================+
5 | FUNCTION |
6 | check_commitment_overapp |
7 | |
8 | DESCRIPTION |
9 | Checks if the commitment balance is overapplied |
10 | |
11 | SCOPE - PUBLIC |
12 | |
13 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
14 | |
15 | ARGUMENTS : IN: |
16 | p_commitment_trx_id |
17 | p_commitment_class - DEP or GUAR |
18 | p_commitment_amount - original amount of the commitment |
19 | p_trx_amount - amount applied against the commitment |
20 | p_so_source_code |
21 | p_so_installed_flag |
22 | |
23 | OUT: |
24 | p_commitment_bal |
25 | |
26 | RETURNS |
27 | TRUE if commitment balance is not overapplied |
28 | FALSE if commitment balance is overapplied |
29 | |
30 | NOTES |
31 | |
32 | MODIFICATION HISTORY |
33 | 06-MAR-1996 Martin Johnson Created |
34 | |
35 +===========================================================================*/
36
37 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
38
39 FUNCTION check_commitment_overapp( p_commitment_trx_id IN number,
40 p_commitment_class IN varchar2,
41
42 p_commitment_amount IN number,
43 p_trx_amount IN number,
44 p_so_source_code IN varchar2,
45 p_so_installed_flag IN varchar2,
46 p_commitment_bal OUT NOCOPY number)
47 RETURN BOOLEAN IS
48
49 l_commitment_amount number;
50 l_commitment_bal number;
51
52 BEGIN
53
54 IF PG_DEBUG in ('Y', 'C') THEN
55 arp_util.debug('arp_trx_val.check_commitment_overapp()+');
56 END IF;
57
58 l_commitment_bal :=
59 arp_bal_util.get_commitment_balance(
60 p_commitment_trx_id,
61 p_commitment_class,
62 p_so_source_code,
63 p_so_installed_flag );
64
65 p_commitment_bal := l_commitment_bal;
66
67 /*------------------------------------------------------------+
68 | If p_commitment_amount was not passed, get value from db |
69 +------------------------------------------------------------*/
70
71 IF ( p_commitment_amount IS NULL )
72 THEN
73 SELECT amount
74 INTO l_commitment_amount
75 FROM ra_cust_trx_line_gl_dist
76 WHERE customer_trx_id = p_commitment_trx_id
77 AND latest_rec_flag = 'Y'
78 AND account_class = 'REC';
79 ELSE
80 l_commitment_amount := p_commitment_amount;
81 END IF;
82
83 IF PG_DEBUG in ('Y', 'C') THEN
84 arp_util.debug('check_commitment_overapp: ' || 'commitment amount = ' || to_char(l_commitment_amount));
85 arp_util.debug('check_commitment_overapp: ' || 'commitment bal = ' || to_char(l_commitment_bal));
86 arp_util.debug('check_commitment_overapp: ' || 'trx amount = ' || to_char(p_trx_amount));
87 END IF;
88
89 -- Bug 433549: changing logic to check if commitment is overapplied.
90 /* IF (
91 sign( l_commitment_amount ) !=
92 sign( l_commitment_bal + p_trx_amount )
93 )
94 AND
95 ( l_commitment_bal + p_trx_amount != 0 )
96 */
97 IF ( (p_trx_amount > l_commitment_bal))
98 THEN
99 IF PG_DEBUG in ('Y', 'C') THEN
100 arp_util.debug('arp_trx_val.check_commitment_overapp()-');
101 END IF;
102 return(FALSE);
103 ELSE
104 IF PG_DEBUG in ('Y', 'C') THEN
105 arp_util.debug('arp_trx_val.check_commitment_overapp()-');
106 END IF;
107 return(TRUE);
108 END IF;
109
110 EXCEPTION
111 WHEN OTHERS THEN
112 IF PG_DEBUG in ('Y', 'C') THEN
113 arp_util.debug('EXCEPTION: arp_trx_val.check_commitment_overapp()');
114 END IF;
115 RAISE;
116
117 END check_commitment_overapp;
118
119
120 /*===========================================================================+
121 | FUNCTION |
122 | check_currency_amounts |
123 | |
124 | DESCRIPTION |
125 | Checks if the transaction amounts are valid for the currency. |
126 | |
127 | SCOPE - PUBLIC |
128 | |
129 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
130 | |
131 | ARGUMENTS : IN: |
132 | p_customer_trx_id |
133 | p_currency_code |
134 | p_display_message_flag |
135 | |
136 | OUT: |
137 | None |
138 | |
139 | RETURNS |
140 | TRUE if the amounts are valid for the currency. |
141 | FALSE if the amounts are not valid for the currency |
142 | |
143 | NOTES |
144 | |
145 | MODIFICATION HISTORY |
146 | 21-AUG-1996 Charlie Tomberg Created |
147 | |
148 +===========================================================================*/
149
150 FUNCTION check_currency_amounts(
151 p_customer_trx_id IN number,
152 p_currency_code IN varchar2,
153 p_display_message_flag IN boolean
154 ) RETURN boolean IS
155
156 l_message varchar2(30);
157 l_dummy integer;
158 l_precision integer;
159 l_extended_precision integer;
160 l_min_acct_unit number;
161
162 CURSOR invalid_amounts IS
163 /* Currency references line amounts with invalid precision */
164 select 1,
165 'AR_TW_BAD_CURR_LINE_AMT'
166 from dual
167 where rownum = 1
168 and exists
169 (select 'invalid precision'
170 from ra_customer_trx_lines line
171 where (( decode(l_min_acct_unit, null,
172 round(extended_amount, l_precision),
173 round(extended_amount / l_min_acct_unit)
174 * l_min_acct_unit) - extended_amount <> 0 )
175 or
176 ( decode(l_min_acct_unit, null,
177 round(revenue_amount, l_precision),
178 round(revenue_amount / l_min_acct_unit)
179 * l_min_acct_unit) - revenue_amount <> 0 ))
180 and line.customer_trx_id = p_customer_trx_id)
181 UNION ALL
182 /* Currency references distribution amounts with invalid precision */
183 select 2,
184 'AR_TW_BAD_CURR_DIST_AMT'
185 from dual
186 where rownum = 1
187 and exists
188 (select 'invalid precision'
189 from ra_cust_trx_line_gl_dist
190 where ( decode(l_min_acct_unit, null,
191 round(amount, l_precision),
192 round(amount / l_min_acct_unit)
193 * l_min_acct_unit) - amount <> 0 )
194 and customer_trx_id = p_customer_trx_id
195 and (account_set_flag = 'N'
196 or account_class = 'REC') )
197 UNION ALL
198 /* Currency references salesrep amounts with invalid precision */
199 select 3,
200 'AR_TW_BAD_CURR_SREP_AMT'
201 from dual
202 where rownum = 1
203 and exists
204 (select 'invalid precision'
205 from ra_cust_trx_line_salesreps
206 where (( decode(l_min_acct_unit, null,
207 round(revenue_amount_split, l_precision),
208 round(revenue_amount_split / l_min_acct_unit)
209 * l_min_acct_unit) - revenue_amount_split <> 0 )
210 or
211 ( decode(l_min_acct_unit, null,
212 round(non_revenue_amount_split, l_precision),
213 round(non_revenue_amount_split / l_min_acct_unit)
214 * l_min_acct_unit) - non_revenue_amount_split <> 0 ))
215 and customer_trx_id = p_customer_trx_id
216 and customer_trx_line_id is not null)
217 UNION ALL
218 /* Currency references installment amounts with invalid precision */
219 select 4,
220 'AR_TW_BAD_CURR_PS_AMT'
221 from dual
222 where rownum = 1
223 and exists
224 (select 'invalid precision'
225 from ar_payment_schedules
226 where (( decode(l_min_acct_unit, null,
227 round(amount_due_original, l_precision),
228 round(amount_due_original / l_min_acct_unit)
229 * l_min_acct_unit) - amount_due_original <> 0 )
230 or
231 ( decode(l_min_acct_unit, null,
232 round(amount_line_items_original, l_precision),
233 round(amount_line_items_original / l_min_acct_unit)
234 * l_min_acct_unit) - amount_line_items_original <> 0 )
235 or
236 ( decode(l_min_acct_unit, null,
237 round(freight_original, l_precision),
238 round(freight_original / l_min_acct_unit)
239 * l_min_acct_unit) - freight_original <> 0 )
240 or
241 ( decode(l_min_acct_unit, null,
242 round(tax_original, l_precision),
243 round(tax_original / l_min_acct_unit)
244 * l_min_acct_unit) - tax_original <> 0 ))
245 and customer_trx_id = p_customer_trx_id)
246 ORDER BY 1;
247
248 BEGIN
249
250 IF PG_DEBUG in ('Y', 'C') THEN
251 arp_util.debug('arp_trx_val.check_currency_amounts()+');
252 END IF;
253
254 FND_CURRENCY.get_info( p_currency_code,
255 l_precision,
256 l_extended_precision,
257 l_min_acct_unit );
258
259 IF PG_DEBUG in ('Y', 'C') THEN
260 arp_util.debug('check_currency_amounts: ' || ' CTID: ' || TO_CHAR(p_customer_trx_id) ||
261 ' currency: ' || p_currency_code ||
262 ' precision: ' || TO_CHAR(l_precision) ||
263 ' extended: ' || TO_CHAR(l_extended_precision) ||
264 ' MAU: ' || TO_CHAR(l_min_acct_unit));
265 END IF;
266
267 OPEN invalid_amounts;
268
269 FETCH invalid_amounts
270 INTO l_dummy,
271 l_message;
272
273 CLOSE invalid_amounts;
274
275 IF ( l_message IS NULL )
276 THEN
277 IF PG_DEBUG in ('Y', 'C') THEN
278 arp_util.debug(' Passed check_currency_amounts check');
279 END IF;
280 RETURN(TRUE);
281
282 ELSE
283 IF PG_DEBUG in ('Y', 'C') THEN
284 arp_util.debug(' Failed check_currency_amounts check: ' || l_message ||
285 ' rows: ' || TO_CHAR(SQL%ROWCOUNT));
286 END IF;
287
288 IF ( p_display_message_flag = TRUE )
289 THEN
290 IF PG_DEBUG in ('Y', 'C') THEN
291 arp_util.debug('arp_trx_val.check_currency_amounts()-');
292 END IF;
293 FND_MESSAGE.set_name ('AR', l_message );
294 APP_EXCEPTION.raise_exception;
295 END IF;
296 END IF;
297
298 IF PG_DEBUG in ('Y', 'C') THEN
299 arp_util.debug('arp_trx_val.check_currency_amounts()-');
300 END IF;
301
302 RETURN(FALSE);
303
304 EXCEPTION
305 WHEN OTHERS THEN
306 IF PG_DEBUG in ('Y', 'C') THEN
307 arp_util.debug('EXCEPTION: arp_trx_val.check_currency_amounts()');
308 END IF;
309 RAISE;
310
311 END check_currency_amounts;
312
313 /*Bug3283086 */
314 /*===========================================================================+
315 | FUNCTION |
316 | check_payent_method_validate |
317 | |
318 | DESCRIPTION |
319 | Checks if the payment method is valid for the given transaction date |
320 | The reason to create this seperate function is |
321 | ARP_TRX_VALIDATE.VALIDATE_TRX_DATE uses p_customer_Trx_id which will |
322 | not be created in add mode (before save mode. Hence validation needs |
323 | to be done also).
324 | SCOPE - PUBLIC |
325 | |
326 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
327 | |
328 | ARGUMENTS : IN: |
329 | p_trx_date |
330 | p_currency_code |
331 | p_bill_to_customer_id |
332 | p_ship_to_customer_id |
333 | p_receipt_method_id |
334 | |
335 | OUT: |
336 | None |
337 | |
338 | RETURNS |
339 | TRUE if the payment method is valid for this trx date |
340 | FALSE if the payment method is not valid for this trx date |
341 | |
342 | NOTES |
343 | |
344 | MODIFICATION HISTORY |
345 | 21-JAN-2004 Srivasud |
346 | |
347 +===========================================================================*/
348
349 FUNCTION check_payment_method_validate(p_trx_date IN DATE,
350 p_currency_code IN VARCHAR2,
351 p_bill_to_customer_id IN NUMBER,
352 p_pay_to_customer_id IN NUMBER,
353 p_receipt_method_id IN NUMBER,
354 p_set_of_books_id IN NUMBER) RETURN BOOLEAN
355
356 IS
357 CURSOR receipt_creation_method_cur IS
358 SELECT arc.creation_method_code
359 FROM ar_receipt_methods arm,
360 ar_receipt_classes arc
361 WHERE arm.receipt_class_id = arc.receipt_class_id
362 AND arm.receipt_method_id = p_receipt_method_id;
363
364 receipt_creation_method_rec receipt_creation_method_cur%ROWTYPE;
365 l_temp VARCHAR2(100);
366 --5150135
367 l_pay_to_party_id hz_parties.party_id%type;
368 l_bill_to_party_id hz_parties.party_id%type;
369 BEGIN
370
371 IF ( p_receipt_method_id IS NOT NULL ) THEN
372
373 /*--------------------------------------------------------------------+
374 | 23-MAY-2000 J Rautiainen BR Implementation |
375 | BR payment method does not have bank account associated with it |
376 +--------------------------------------------------------------------*/
377 OPEN receipt_creation_method_cur;
378 FETCH receipt_creation_method_cur INTO receipt_creation_method_rec;
379 CLOSE receipt_creation_method_cur;
380
381
382 /*--------------------------------------------------------------------+
383 | 23-MAY-2000 J Rautiainen BR Implementation |
384 | BR payment method does not have bank account associated with it |
385 +--------------------------------------------------------------------*/
386
387 IF NVL(receipt_creation_method_rec.creation_method_code,'INV') = 'BR' THEN
388 BEGIN
389
390 /* If Payment Method creation code is BR then validate the receipt method
391 only*/
392
393 SELECT 'invalid_payment method'
394 INTO l_temp
395 FROM ar_receipt_methods arm,
396 ar_receipt_classes arc
397 WHERE arm.receipt_method_id = p_receipt_method_id
398 AND arm.receipt_class_id = arc.receipt_class_id
399 AND p_trx_date BETWEEN NVL(arm.start_date,p_trx_date)
400 AND NVL(arm.end_date,p_trx_date)
401 AND rownum = 1;
402 RETURN(TRUE);
403 EXCEPTION
404 WHEN NO_DATA_FOUND THEN
405 RETURN (FALSE);
406 WHEN OTHERS THEN
407 RETURN (FALSE);
408 END;
409
410 ELSE
411
412 BEGIN
413
414 /* We need to validate the following.
415 1. Receipt Method end date.
416 2. Receipt method account end date
417 3. Receipt method should have atleast one
418 bank account with valid end dates
419 4. Also bank account should be of invoice currency or
420 multi currency enabled.
421 5. and that valid bank account should have
422 atleast one bank valid branch.
423 6. Additionally If payment method creation is MANUAL or AUTOMATIC
424 then the trx currency is as same as payment method currency or
425 multi currency flag should be 'Y'
426 7. For Automatic methods if Payment type is NOT CREDIT_CARD
427 additionally the currency should be defined or associated
428 with paying or bill to customer bank accounts.This condition is
429 taken from paying customer payment method LOV.. to keep the
430 both validations in sync.*/
431
432 --5150135
433 l_bill_to_party_id := arp_trx_defaults_3.get_party_id(p_bill_to_customer_id);
434 l_pay_to_party_id := arp_trx_defaults_3.get_party_id(p_pay_to_customer_id);
435
436 SELECT 'invalid_payment method'
437 INTO l_temp
438 FROM ar_receipt_methods arm,
439 ar_receipt_method_accounts arma,
440 ce_bank_accounts cba,
441 ce_bank_acct_uses aba,
442 ar_receipt_classes arc,
443 ce_bank_branches_v bp
444 WHERE arm.receipt_method_id = arma.receipt_method_id
445 AND arm.receipt_class_id = arc.receipt_class_id
446 AND arma.remit_bank_acct_use_id = aba.bank_acct_use_id
447 AND aba.bank_account_id = cba.bank_account_id
448 /* New Condition added Begin*/
449 AND bp.branch_party_id = cba.bank_branch_id
450 AND p_trx_date <= NVL(bp.end_date,p_trx_date)
451 AND (cba.currency_code = p_currency_code or
452 cba.receipt_multi_currency_flag ='Y') /* New condition */
453 /* Removing the join condition based on currency code as part of bug fix 5346710
454 AND (arc.creation_method_code='MANUAL'
455 or (arc.creation_method_code='AUTOMATIC'
456 and ( (nvl(arm.payment_channel_code,'*') = 'CREDIT_CARD' )
457 or
458 (nvl(arm.payment_channel_code,'*') <> 'CREDIT_CARD'
459 AND p_currency_code in
460 (select currency_code from iby_fndcpt_payer_assgn_instr_v
461 where party_id in (l_pay_to_party_id,l_bill_to_party_id))))))*/
462 /* New Condition added Ends*/
463 -- AND aba.set_of_books_id = arp_global.set_of_books_id
464 AND arm.receipt_method_id = p_receipt_method_id
465 AND p_trx_date < NVL(cba.end_date,
466 TO_DATE('01/01/2200','DD/MM/YYYY') )
467 AND p_trx_date BETWEEN NVL(arm.start_date,
468 p_trx_date)
469 AND NVL(arm.end_date,
470 p_trx_date)
471 AND p_trx_date BETWEEN NVL(arma.start_date,
472 p_trx_date)
473 AND NVL(arma.end_date,
474 p_trx_date)
475 AND rownum = 1;
476
477 RETURN(TRUE);
478
479 EXCEPTION
480 WHEN NO_DATA_FOUND THEN
481 RETURN(FALSE);
482 WHEN OTHERS THEN
483 RETURN(FALSE);
484 END;
485 END IF;
486 END IF;
487 END check_payment_method_validate;
488 END ARP_TRX_VAL;