[Home] [Help]
PACKAGE BODY: APPS.ARP_TRX_COMPLETE_CHK
Source
1 PACKAGE BODY ARP_TRX_COMPLETE_CHK AS
2 /* $Header: ARTUVA2B.pls 120.28 2011/07/18 23:26:55 dgaurab ship $ */
3
4
5 pg_ai_pds_exist_cursor integer;
6 pg_ai_overlapping_pds_cursor integer;
7 pg_form_pds_exist_cursor integer;
8 pg_form_overlapping_pds_cursor integer;
9
10 pg_salesrep_required_flag ar_system_parameters.salesrep_required_flag%type;
11 pg_set_of_books_id ar_system_parameters.set_of_books_id%type;
12 pg_base_curr_code gl_sets_of_books.currency_code%type;
13 pg_so_source_code varchar2(240);
14 pg_so_installed_flag varchar2(1);
15
16
17 /*===========================================================================+
18 | PROCEDURE |
19 | check_tax_and_accounting() |
20 | |
21 | DESCRIPTION |
22 | Checks if the transaction can be completed. |
23 | |
24 | The following checks are performed: |
25 | - Check the existance and validtity of account assignments or |
26 | account sets: |
27 | Constraints: |
28 | - records exists for each line |
29 | - all code combinations are valid |
30 | - For account assignments, the sum of the assignment amounts |
31 | must equal the line amount. |
32 | - For account sets, the sum of the percents for each line and |
33 | account class must equal 100%. |
34 | - If TAX_CALCULATION_FLAG is Yes, then tax is required for all invoice |
35 | lines unless it's a memo line not of type LINE. |
36 | - Tax is also required if TAX_CALCULATION_FLAG is No and |
37 | TAX_EXEMPT_FLAG is Require. |
38 | |
39 | SCOPE - PRIVATE |
40 | |
41 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
42 | arp_bal_util.get_commitment_balance |
43 | arp_ct_pkg.fetch_p |
44 | arp_non_db_pkg.check_creation_sign |
45 | arp_non_db_pkg.check_natural_application |
46 | arp_trx_global.profile_info.use_inv_acct_for_cm_flag |
47 | arp_trx_util.get_summary_trx_balances |
48 | arp_trx_validate.validate_trx_date |
49 | arp_util.debug |
50 | arp_util.validate_and_default_gl_date |
51 | |
52 | ARGUMENTS : IN: |
53 | p_customer_trx_id |
54 | p_so_source_code |
55 | p_so_installed_flag |
56 | |
57 | OUT: |
58 | p_error_count |
59 | |
60 | RETURNS : p_error_count |
61 | |
62 | NOTES |
63 | |
64 | MODIFICATION HISTORY |
65 | 26-JUN-96 Charlie Tomberg Created |
66 | |
67 +===========================================================================*/
68
69 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
70
71 FUNCTION check_tax_and_accounting(
72 p_mode IN varchar2,
73 p_customer_trx_id IN integer,
74 p_previous_customer_trx_id IN integer,
75 p_trx_number IN varchar2,
76 p_class IN varchar2,
77 p_tax_calculation_flag IN varchar2,
78 p_invoicing_rule_id IN integer,
79 p_error_count IN OUT NOCOPY integer,
80 p_error_line_number OUT NOCOPY integer,
81 p_error_other_line_number OUT NOCOPY integer
82 ) RETURN BOOLEAN IS
83
84 /* 5093094 - removed cursor tax_check */
85
86 CURSOR dist_check IS
87 SELECT ctl.customer_trx_line_id customer_trx_line_id,
88 NVL(ctl_line.line_number,
89 ctl.line_number) line_number,
90 DECODE(ctl_line.customer_trx_line_id,
91 NULL, TO_NUMBER(NULL),
92 ctl.line_number) other_line_number,
93 ctl.extended_amount line_amount,
94 SUM(lgd.amount) dist_amount,
95 MAX(lgd.account_class) account_class,
96 DECODE(p_invoicing_rule_id,
97 NULL,
98 -- no rules case
99 DECODE( MAX(lgd.cust_trx_line_gl_dist_id),
100 NULL, DECODE( ctl.line_type,
101 'LINE', 'AR_TW_NO_LINE_DISTS',
102 'CHARGES', 'AR_TW_NO_CHARGES_DISTS',
103 'TAX', 'AR_TW_NO_TAX_DISTS',
104 'FREIGHT', 'AR_TW_NO_FREIGHT_DISTS',
105 'AR_TW_NO_REC_DIST'),
106 DECODE( MIN(lgd.code_combination_id),
107 -1, DECODE( ctl.line_type,
108 'LINE', 'AR_TW_BAD_LINE_DISTS',
109 'CHARGES', 'AR_TW_BAD_CHARGES_DISTS',
110 'TAX', 'AR_TW_BAD_TAX_DISTS',
111 'FREIGHT', 'AR_TW_BAD_FREIGHT_DISTS',
112 'AR_TW_BAD_REC_DIST'),
113 DECODE( ctl.extended_amount,
114 SUM(lgd.amount), NULL,
115 DECODE( ctl.line_type,
116 'LINE', 'AR_TW_LINE_DIST_AMT',
117 'CHARGES', 'AR_TW_CHARGES_DIST_AMT',
118 'TAX', 'AR_TW_TAX_DIST_AMT',
119 'FREIGHT', 'AR_TW_FREIGHT_DIST_AMT')
120 )
121 )
122 ),
123 -- rules case
124 DECODE( MAX(lgd.cust_trx_line_gl_dist_id),
125 NULL,
126 -- Bug 2137682: changed the MAX(lgd.account_class) to MAX(ctl.line_type)
127 DECODE( MAX(ctl.line_type),
128 'REV', 'AR_TW_NO_REVENUE_SETS',
129 'SUSPENSE', 'AR_TW_NO_SUSPENSE_SETS',
130 'UNEARN', 'AR_TW_NO_UNEARN_SETS',
131 'UNBILL', 'AR_TW_NO_UNBILL_SETS',
132 'CHARGES', 'AR_TW_NO_CHARGES_SETS',
133 'TAX', 'AR_TW_NO_TAX_SETS',
134 'FREIGHT', 'AR_TW_NO_FREIGHT_SETS',
135 'AR_TW_NO_REC_SETS'),
136 DECODE( MIN(lgd.code_combination_id),
137 -1, DECODE( MAX(lgd.account_class),
138 'REV', 'AR_TW_BAD_REVENUE_SETS',
139 'SUSPENSE','AR_TW_BAD_SUSPENSE_SETS',
140 'UNEARN', 'AR_TW_BAD_UNEARN_SETS',
141 'UNBILL', 'AR_TW_BAD_UNBILL_SETS',
142 'CHARGES', 'AR_TW_BAD_CHARGES_SETS',
143 'TAX', 'AR_TW_BAD_TAX_SETS',
144 'FREIGHT', 'AR_TW_BAD_FREIGHT_SETS',
145 'AR_TW_BAD_REC_SETS'),
146 DECODE( SUM(lgd.percent),
147 100, NULL,
148 DECODE( MAX(lgd.account_class),
149 'REV', 'AR_TW_REVENUE_SETS_PCT',
150 'SUSPENSE','AR_TW_SUSPENSE_SETS_PCT',
151 'UNEARN', 'AR_TW_UNEARN_SETS_PCT',
152 'UNBILL', 'AR_TW_UNBILL_SETS_PCT',
153 'CHARGES', 'AR_TW_CHARGES_SETS_PCT',
154 'TAX', 'AR_TW_TAX_SETS_PCT',
155 'FREIGHT','AR_TW_FREIGHT_SETS_PCT')
156 )
157 )
158 )
159 ) message_name
160 FROM ra_customer_trx_lines ctl_line,
161 ra_customer_trx_lines ctl,
162 ra_cust_trx_line_gl_dist lgd,
163 ra_customer_trx ct
164 WHERE ct.customer_trx_id = p_customer_trx_id
165 AND ct.customer_trx_id = ctl.customer_trx_id(+)
166 AND ctl.customer_trx_line_id = lgd.customer_trx_line_id(+)
167 AND ctl.link_to_cust_trx_line_id = ctl_line.customer_trx_line_id(+)
168 AND DECODE(p_invoicing_rule_id,
169 NULL, 'N',
170 'Y') = lgd.account_set_flag(+)
171 GROUP BY ctl.customer_trx_line_id,
172 ctl.line_number,
173 ctl_line.line_number,
174 ctl_line.customer_trx_line_id,
175 ctl.extended_amount,
176 ctl.line_type,
177 DECODE(p_invoicing_rule_id,
178 NULL, NULL,
179 lgd.account_class)
180 HAVING (
181 MAX(lgd.cust_trx_line_gl_dist_id) IS NULL
182 OR
183 MIN(lgd.code_combination_id) < 0
184 OR (
185 p_invoicing_rule_id IS NULL
186 AND ctl.extended_amount <> SUM(lgd.amount)
187 )
188 OR (
189 (
190 p_invoicing_rule_id IS NOT NULL
191 OR MAX(lgd.account_class) = 'REC'
192 )
193 AND SUM(lgd.percent) <> 100
194 )
195 )
196 UNION
197 SELECT -- Receivables case
198 TO_NUMBER(NULL),
199 TO_NUMBER(NULL),
200 TO_NUMBER(NULL),
201 TO_NUMBER(NULL),
202 lgd.amount,
203 'REC',
204 DECODE(p_invoicing_rule_id,
205 NULL,
206 -- no rules case
207 DECODE( lgd.cust_trx_line_gl_dist_id,
208 NULL, 'AR_TW_NO_REC_DISTS',
209 'AR_TW_BAD_REC_DISTS'),
210 -- rules case
211 DECODE( lgd.cust_trx_line_gl_dist_id,
212 NULL, 'AR_TW_NO_REC_SETS',
213 'AR_TW_BAD_REC_SETS')
214 ) message_name
215 FROM ra_cust_trx_line_gl_dist lgd
216 WHERE lgd.customer_trx_id = p_customer_trx_id
217 AND lgd.account_class = 'REC'
218 AND lgd.latest_rec_flag = 'Y'
219 AND (
220 NVL(lgd.code_combination_id, -1) < 0
221 OR lgd.percent <> 100
222 )
223 ORDER BY 1;
224
225 l_error_mode varchar2(15);
226 /*
227 * arp_process_tax.validate_tax_enforcement() returns the following:
228 */
229 p_line_number NUMBER;
230 p_gl_tax_code zx_rates_b.tax_rate_code%TYPE := NULL;
231 p_validation_status BOOLEAN := TRUE;
232
233 l_error_count NUMBER;
234 l_valid_for_tax VARCHAR2(1);
235
236 BEGIN
237
238 IF PG_DEBUG in ('Y', 'C') THEN
239 arp_util.debug('arp_trx_validate.check_tax_and_accounting()+');
240 END IF;
241
242
243 /*-----------------------------------------------------------------------+
244 | Check account assignments or account sets |
245 | Unless the current transaction is a credit memo against a specific |
246 | transaction with rules and the Use Invoice Accounting flag is Yes. |
247 | (In that case, the CM will not have any line level account |
248 | assignments or account sets because it will use the account sets of |
249 | the invoice that it is crediting.) |
250 | |
251 | Constraints: |
252 | - records exists for each line |
253 | - all code combinations are valid |
254 | - For account assignments, the sum of the assignment amounts must |
255 | equal the line amount. |
256 | - For account sets, the sum of the percents for each line and |
257 | account class must equal 100%. |
258 +-----------------------------------------------------------------------*/
259
260
261 IF PG_DEBUG in ('Y', 'C') THEN
262 arp_util.debug('check_tax_and_accounting: ' || 'check account assignments and account sets');
263 END IF;
264
265 -- Bug 540962: need to compare the 'HANDLER' and 'STANDARD'
266 -- to make sure error is raised when procedure is
267 -- called as part of completion checking
268 -- (used to be compared only to 'HANDLER', which
269 -- caused l_error_mode to be NO_EXCEPTION for
270 -- p_mode=STANDARD
271
272 IF (p_mode IN ('HANDLER', 'STANDARD'))
273 THEN
274 l_error_mode := 'STANDARD';
275 ELSIF (p_mode = 'PL/SQL')
276 THEN l_error_mode := 'PL/SQL';
277 ELSE
278 l_error_mode := 'NO_EXCEPTION';
279 END IF;
280
281 IF (
282 p_previous_customer_trx_id IS NULL
283 OR p_invoicing_rule_id IS NULL
284 OR arp_trx_global.profile_info.use_inv_acct_for_cm_flag = 'N'
285 )
286 THEN
287
288 BEGIN
289 FOR l_error_rec IN dist_check LOOP
290
291 IF PG_DEBUG in ('Y', 'C') THEN
292 arp_util.debug('check_tax_and_accounting: ' || 'ERROR: The accounts for line ' ||
293 TO_CHAR(l_error_rec.line_number) || '-' ||
294 TO_CHAR(l_error_rec.other_line_number) ||
295 ' is invalid');
296 arp_util.debug('check_tax_and_accounting: ' || 'line amt: ' ||
297 TO_CHAR(l_error_rec.line_amount) ||
298 ' dist amt: ' ||
299 TO_CHAR(l_error_rec.dist_amount) ||
300 ' class: ' || l_error_rec.account_class);
301 END IF;
302
303
304 arp_trx_validate.add_to_error_list(
305 l_error_mode,
306 p_error_count,
307 p_customer_trx_id,
308 p_trx_number,
309 l_error_rec.line_number,
310 l_error_rec.other_line_number,
311 l_error_rec.message_name,
312 NULL, -- p_error_location,
313 'LINE_NUMBER',
314 l_error_rec.line_number,
315 'OTHER_LINE_NUMBER',
316 l_error_rec.other_line_number
317 );
318
319 IF (p_mode = 'FORM')
320 THEN
321 p_error_line_number := l_error_rec.line_number;
322 p_error_other_line_number := l_error_rec.other_line_number;
323 RETURN(FALSE);
324 END IF;
325
326 END LOOP;
327
328 EXCEPTION
329 WHEN NO_DATA_FOUND THEN NULL;
330 WHEN OTHERS
331 THEN RAISE;
332 END;
333
334 END IF;
335
336 /* 5093094 - Removed entire tax validation section based on
337 tax_check cursor. We no longer validate existance of tax
338 based on trx_type. */
339
340 /* 4188835 - removed call to arp_process_tax.validate_tax_enforcement
341 as this validation is handled automatically by etax call
342 to calculate_tax */
343
344 /* 5373072 - Added call to arp_etax_services_pkg.validate_for_tax
345 This routine determines if the tax is still active/valid at
346 the point of completion. It uses the error handling from
347 arp_trx_validate to log and raise messages based on the p_mode
348 parameter */
349
350 arp_etax_services_pkg.validate_for_tax(
351 p_customer_trx_id => p_customer_trx_id,
352 p_error_mode => l_error_mode,
353 p_valid_for_tax => l_valid_for_tax,
354 p_number_of_errors=> l_error_count);
355
356 p_error_count := p_error_count + l_error_count;
357
358 IF PG_DEBUG in ('Y', 'C') THEN
359 arp_util.debug(' num of etax validation errors = ' || l_error_count);
360 arp_util.debug('arp_trx_validate.check_tax_and_accounting()-');
361 END IF;
362
363 RETURN(TRUE);
364
365 EXCEPTION
366 WHEN OTHERS THEN
367 IF PG_DEBUG in ('Y', 'C') THEN
368 arp_util.debug('check_tax_and_accounting: ' ||
369 'EXCEPTION: arp_trx_validate.check_tax_and_accounting()');
370 END IF;
371 RAISE;
372
373 END check_tax_and_accounting;
374
375 /*===========================================================================+
376 | PROCEDURE |
377 | check_tax_and_accounting() |
378 | |
379 | DESCRIPTION |
380 | Checks if the transaction can be completed. |
381 | |
382 | The following checks are performed: |
383 | - Check the existance and validtity of account assignments or |
384 | account sets: |
385 | Constraints: |
386 | - records exists for each line |
387 | - all code combinations are valid |
388 | - For account assignments, the sum of the assignment amounts |
389 | must equal the line amount. |
390 | - For account sets, the sum of the percents for each line and |
391 | account class must equal 100%. |
392 | - If TAX_CALCULATION_FLAG is Yes, then tax is required for all invoice |
393 | lines unless it's a memo line not of type LINE. |
394 | - Tax is also required if TAX_CALCULATION_FLAG is No and |
395 | TAX_EXEMPT_FLAG is Require. |
396 | |
397 | SCOPE - PRIVATE |
398 | |
399 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
400 | arp_bal_util.get_commitment_balance |
401 | arp_ct_pkg.fetch_p |
402 | arp_non_db_pkg.check_creation_sign |
403 | arp_non_db_pkg.check_natural_application |
404 | arp_trx_global.profile_info.use_inv_acct_for_cm_flag |
405 | arp_trx_util.get_summary_trx_balances |
406 | arp_trx_validate.validate_trx_date |
407 | arp_util.debug |
408 | arp_util.validate_and_default_gl_date |
409 | |
410 | ARGUMENTS : IN: |
411 | p_customer_trx_id |
412 | p_so_source_code |
413 | p_so_installed_flag |
414 | |
415 | OUT: |
416 | p_error_count |
417 | |
418 | RETURNS : p_error_count |
419 | |
420 | NOTES |
421 | |
422 | MODIFICATION HISTORY |
423 | 26-JUN-96 Charlie Tomberg Created |
424 | |
425 +===========================================================================*/
426
427 FUNCTION check_tax_and_accounting(
428 p_query_string IN varchar2,
429 p_error_trx_number OUT NOCOPY varchar2,
430 p_error_line_number OUT NOCOPY number,
431 p_error_other_line_number OUT NOCOPY number
432 ) RETURN BOOLEAN IS
433
434 l_cursor integer;
435 l_error_count integer;
436 l_error_line_number integer;
437 l_error_other_line_number integer;
438
439 l_customer_trx_id integer;
440 l_previous_customer_trx_id integer;
441 l_trx_number varchar2(20);
442 l_invoicing_rule_id integer;
443 l_class varchar2(20);
444 l_tax_calculation_flag varchar2(1);
445 l_dummy integer;
446 l_result boolean;
447 l_sql_statement varchar2(800);
448
449 BEGIN
450
451 IF PG_DEBUG in ('Y', 'C') THEN
452 arp_util.debug('arp_trx_validate.check_tax_and_accounting()+');
453 END IF;
454
455 -- Bug 540962: error count variable needs to be initialized
456
457 l_error_count := 0;
458
459 l_cursor := dbms_sql.open_cursor;
460
461 l_sql_statement :=
462 'SELECT ct.previous_customer_trx_id previous_customer_trx_id,
463 ct.trx_number trx_number,
464 ct.invoicing_rule_id invoicing_rule_id,
465 ctt.type class,
466 ctt.tax_calculation_flag tax_calculation_flag,
467 ct.customer_trx_id customer_trx_id
468 FROM ra_customer_trx ct,
469 ra_cust_trx_types ctt
470 WHERE ct.cust_trx_type_id = ctt.cust_trx_type_id
471 AND ct.customer_trx_id IN (' || p_query_string || ')';
472
473 IF PG_DEBUG in ('Y', 'C') THEN
474 arp_util.debug('check_tax_and_accounting: ' || 'sql statement: ');
475 arp_util.debug('check_tax_and_accounting: ' || l_sql_statement );
476 END IF;
477
478 dbms_sql.parse(l_cursor,
479 l_sql_statement,
480 dbms_sql.v7);
481
482 dbms_sql.define_column(l_cursor, 1, l_previous_customer_trx_id);
483 dbms_sql.define_column(l_cursor, 2, l_trx_number, 20);
484 dbms_sql.define_column(l_cursor, 3, l_invoicing_rule_id);
485 dbms_sql.define_column(l_cursor, 4, l_class, 20);
486 dbms_sql.define_column(l_cursor, 5, l_tax_calculation_flag, 1);
487 dbms_sql.define_column(l_cursor, 6, l_customer_trx_id);
488 l_dummy := dbms_sql.execute(l_cursor);
489
490
491 LOOP
492 IF (dbms_sql.fetch_rows(l_cursor) > 0)
493 THEN
494
495 dbms_sql.column_value(l_cursor, 1, l_previous_customer_trx_id);
496 dbms_sql.column_value(l_cursor, 2, l_trx_number);
497 dbms_sql.column_value(l_cursor, 3, l_invoicing_rule_id);
498 dbms_sql.column_value(l_cursor, 4, l_class);
499 dbms_sql.column_value(l_cursor, 5, l_tax_calculation_flag);
500 dbms_sql.column_value(l_cursor, 6, l_customer_trx_id);
501
502 IF PG_DEBUG in ('Y', 'C') THEN
503 arp_util.debug('check_tax_and_accounting: ' || '');
504 arp_util.debug('check_tax_and_accounting: ' || 'Checking: ');
505 arp_util.debug('check_tax_and_accounting: ' || 'customer_trx_id = ' ||
506 TO_CHAR(l_customer_trx_id));
507 arp_util.debug('check_tax_and_accounting: ' || 'previous_customer_trx_id = ' ||
508 TO_CHAR(l_previous_customer_trx_id));
509 arp_util.debug('check_tax_and_accounting: ' || 'trx_number = ' ||
510 l_trx_number);
511 arp_util.debug('check_tax_and_accounting: ' || 'invoicing_rule_id = ' ||
512 TO_CHAR(l_invoicing_rule_id));
513 arp_util.debug('check_tax_and_accounting: ' || 'class = ' ||
514 l_class);
515 arp_util.debug('check_tax_and_accounting: ' || 'tax_calculation_flag = ' ||
516 l_tax_calculation_flag);
517 END IF;
518
519 IF ( check_tax_and_accounting(
520 'FORM',
521 l_customer_trx_id,
522 l_previous_customer_trx_id,
523 l_trx_number,
524 l_class,
525 l_tax_calculation_flag,
526 l_invoicing_rule_id,
527 l_error_count,
528 l_error_line_number,
529 l_error_other_line_number
530 ) = FALSE )
531 THEN
532 p_error_trx_number := l_trx_number;
533 p_error_line_number := l_error_line_number;
534 p_error_other_line_number := l_error_other_line_number;
535
536 IF PG_DEBUG in ('Y', 'C') THEN
537 arp_util.debug('check_tax_and_accounting: ' || 'Setting error out NOCOPY parameters to: ');
538 arp_util.debug('check_tax_and_accounting: ' || 'p_error_trx_number = ' ||
539 l_trx_number);
540 arp_util.debug('check_tax_and_accounting: ' || 'p_error_line_number = ' ||
541 l_error_line_number);
542 arp_util.debug('check_tax_and_accounting: ' || 'p_error_other_line_number = ' ||
543 l_error_other_line_number);
544 END IF;
545
546 dbms_sql.close_cursor(l_cursor);
547
548 IF PG_DEBUG in ('Y', 'C') THEN
549 arp_util.debug('check_tax_and_accounting: ' || 'returning FALSE');
550 arp_util.debug('check_tax_and_accounting: ' ||
551 'arp_trx_validate.check_tax_and_accounting()-');
552 END IF;
553
554 RETURN(FALSE);
555 END IF;
556
557 ELSE
558 EXIT;
559 END IF;
560 END LOOP;
561
562 dbms_sql.close_cursor(l_cursor);
563
564 IF PG_DEBUG in ('Y', 'C') THEN
565 arp_util.debug('check_tax_and_accounting: ' || 'returning TRUE');
566 arp_util.debug('arp_trx_validate.check_tax_and_accounting()-');
567 END IF;
568
569 RETURN(TRUE);
570
571 EXCEPTION
572 WHEN OTHERS THEN
573 IF PG_DEBUG in ('Y', 'C') THEN
574 arp_util.debug('check_tax_and_accounting: ' ||
575 'EXCEPTION: arp_trx_validate.check_tax_and_accounting()');
576 arp_util.debug('======= parameters for check_tax_and_accounting: ' ||
577 '=======');
578 arp_util.debug('check_tax_and_accounting: ' || 'p_query_string = ' || p_query_string );
579 END IF;
580 IF (dbms_sql.is_open(l_cursor))
581 THEN dbms_sql.close_cursor(l_cursor);
582 END IF;
583
584 RAISE;
585
586 END check_tax_and_accounting;
587
588 /*===========================================================================+
589 | PROCEDURE |
590 | do_completion_checking() |
591 | |
592 | DESCRIPTION |
593 | Checks if the transaction can be completed. |
594 | |
595 | The following checks are performed: |
596 | - Insure that at least one line or freight line exists. |
597 | - Insure that all entities that have start / end dates are valid for |
598 | the specified trx date. |
599 | - Insure that if a commitment has been specified, it is valid with |
600 | the transaction's trx_date and gl_date |
601 | - If salescredits are required, the total salescredits for each line |
602 | must equal 100% of the line amount. |
603 | - If salescredits are not required, either no salescredits exist for |
604 | a line or they sum to 100%. |
605 | - Check the existance and validtity of account assignments or |
606 | account sets: |
607 | Constraints: |
608 | - records exists for each line |
609 | - all code combinations are valid |
610 | - For account assignments, the sum of the assignment amounts |
611 | must equal the line amount. |
612 | - For account sets, the sum of the percents for each line and |
613 | account class must equal 100%. |
614 | - If an invoicing rule has been specified, verify that all lines |
615 | have accounting rules and rule start dates. |
616 | - If TAX_CALCULATION_FLAG is Yes, then tax is required for all invoice |
617 | lines unless it's a memo line not of type LINE. |
618 | - Tax is also required if TAX_CALCULATION_FLAG is No and |
619 | TAX_EXEMPT_FLAG is Require. |
620 | - Check the creation sign of the transaction |
621 | - Verify that the GL Date is in an Opened, Future or |
622 | Never Opened (Arrears only) Period. |
623 | |
624 | The following validations only apply to credit memos against |
625 | transactions. |
626 | |
627 | - Check for illegal overapplications. |
628 | - The GL Date must be >= the credited transaction's GL Date. |
629 | - There can be no later credit memos applied to the same transaction. |
630 | |
631 | SCOPE - PUBLIC |
632 | |
633 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
634 | arp_bal_util.get_commitment_balance |
635 | arp_ct_pkg.fetch_p |
636 | arp_non_db_pkg.check_creation_sign |
637 | arp_non_db_pkg.check_natural_application |
638 | arp_trx_global.profile_info.use_inv_acct_for_cm_flag |
639 | arp_trx_util.get_summary_trx_balances |
640 | arp_trx_validate.validate_trx_date |
641 | arp_util.debug |
642 | arp_util.validate_and_default_gl_date |
643 | |
644 | ARGUMENTS : IN: |
645 | p_customer_trx_id |
646 | p_so_source_code |
647 | p_so_installed_flag |
648 | |
649 | OUT: |
650 | p_error_count |
651 | |
652 | RETURNS : p_error_count |
653 | |
654 | NOTES |
655 | |
656 | MODIFICATION HISTORY |
657 | 06-DEC-95 Charlie Tomberg Created |
658 | |
659 | 01-FEB-02 Michael Raymond Bug 2164863 - Added parameter with
660 | default value to do_completion_checking.
661 | The parameter, 'p_check_tax_acct' is
662 | used to conditionally execute the
663 | check_tax_and_accounting procedure.
664 | Values:
665 | 'Y' - Execute tax/acct validation only
666 | 'N' - Execute all other validations
667 | 'B' - Execute all validations (Y + N)
668 | 13-MAr-2003 B chatterjee Bug 2836430 - modified salesrep_check cursor
669 | to compare %'s of round (SUM , 4)
670 | 18-DEC-2003 Srivasud Added a procedure dm_reversal_amount_chk
671 | to check whether the amount of Debit memo
672 | is greater than original debit memo amount
673 +===========================================================================*/
674
675 PROCEDURE do_completion_checking(
676 p_customer_trx_id IN
677 ra_customer_trx.customer_trx_id%type,
678 p_so_source_code IN varchar2,
679 p_so_installed_flag IN varchar2,
680 p_error_count OUT NOCOPY number
681 ) IS
682 BEGIN
683
684 do_completion_checking(
685 p_customer_trx_id,
686 p_so_source_code,
687 p_so_installed_flag,
688 'STANDARD',
689 p_error_count,
690 'B' -- 2164863
691 );
692
693
694
695 END;
696
697 PROCEDURE do_completion_checking(
698 p_customer_trx_id IN
699 ra_customer_trx.customer_trx_id%type,
700 p_so_source_code IN varchar2,
701 p_so_installed_flag IN varchar2,
702 p_error_mode IN VARCHAR2,
703 p_error_count OUT NOCOPY number,
704 p_check_tax_acct IN VARCHAR2 DEFAULT 'B'
705 ) IS
706
707
708 l_trx_rec ra_customer_trx%rowtype;
709 l_prev_trx_rec ra_customer_trx%rowtype;
710 l_commit_trx_rec ra_customer_trx%rowtype;
711
712 l_class ra_cust_trx_types.type%type;
713 l_credited_class ra_cust_trx_types.type%type;
714 l_creation_sign ra_cust_trx_types.creation_sign%type;
715 l_allow_overapplication_flag
716 ra_cust_trx_types.allow_overapplication_flag%type;
717 l_natural_app_only_flag
718 ra_cust_trx_types.natural_application_only_flag%type;
719
720 l_open_receivables_flag ra_cust_trx_types.accounting_affect_flag%type;
721 l_tax_calculation_flag ra_cust_trx_types.tax_calculation_flag%type;
722
723
724 l_trx_gl_date ra_cust_trx_line_gl_dist.gl_date%type;
725 l_commit_gl_date ra_cust_trx_line_gl_dist.gl_date%type;
726 l_prev_gl_date ra_cust_trx_line_gl_dist.gl_date%type;
727 l_default_gl_date ra_cust_trx_line_gl_dist.gl_date%type;
728
729 l_commit_amount number;
730 l_trx_amount number;
731 l_commitment_balance number;
732 l_line_amount number;
733 l_tax_amount number;
734 l_freight_amount number;
735 l_prev_line_original number;
736 l_prev_line_remaining number;
737 l_prev_tax_original number;
738 l_prev_tax_remaining number;
739 l_prev_freight_original number;
740 l_prev_freight_remaining number;
741 l_prev_charges_original number;
742 l_prev_charges_remaining number;
743 l_prev_total_original number;
744 l_prev_total_remaining number;
745
746 l_dummy varchar2(128);
747 l_dummy_date date;
748 l_dummy_flag boolean;
749 l_dummy_number number;
750 l_result number;
751 l_result_flag boolean;
752 l_error_message VARCHAR2(30);
753 l_error_count integer;
754 l_so_source_code varchar2(240);
755 l_so_installed_flag varchar2(1);
756 l_rule_flag varchar2(1):='N';
757
758 /* Bug 882789 */
759 l_commit_adj_amount number;
760 /* Bug 2534132 */
761 l_commit_line_amount NUMBER;
762 l_commit_tax_amount NUMBER;
763 l_commit_frt_amount NUMBER;
764
765 --Bug Fix: 3261620 Begin#1
766 l_gl_account_ccid ra_cust_trx_line_gl_dist.code_combination_id%type;
767 l_dist_gl_date DATE;
768 --Bug Fix: 3261620 End#1
769 /* Bug fix 5444418 */
770 l_account_class ra_cust_trx_line_gl_dist.account_class%TYPE;
771 l_account_set_flag ra_cust_trx_line_gl_dist.account_set_flag%TYPE;
772 l_revrec_complete varchar2(1);
773 /*bug 2836430*/
774 CURSOR salesrep_check IS
775 SELECT l.line_number line_number,
776 l.customer_trx_line_id customer_trx_line_id,
777 round(SUM(s.revenue_percent_split),4) error_amount
778 FROM ra_customer_trx_lines l,
779 ra_cust_trx_line_salesreps s
780 WHERE pg_salesrep_required_flag = 'Y'
781 AND l.customer_trx_id = p_customer_trx_id
782 AND l.customer_trx_line_id = s.customer_trx_line_id(+)
783 AND l.line_type = 'LINE'
784 GROUP BY l.line_number,
785 l.customer_trx_line_id
786 HAVING round(SUM(NVL(s.revenue_percent_split, 0)),4) <> 100
787 UNION
788 SELECT l.line_number line_number,
789 l.customer_trx_line_id customer_trx_line_id,
790 round(SUM(s.revenue_percent_split),4) error_amount
791 FROM ra_customer_trx_lines l,
792 ra_cust_trx_line_salesreps s
793 WHERE pg_salesrep_required_flag = 'N'
794 AND l.customer_trx_id = p_customer_trx_id
795 AND l.customer_trx_line_id = s.customer_trx_line_id
796 AND l.line_type = 'LINE'
797 GROUP BY l.line_number,
798 l.customer_trx_line_id
799 HAVING round(SUM(NVL(s.revenue_percent_split, 0)),4) <> 100
800 AND SUM(s.revenue_percent_split) IS NOT NULL
801 ORDER BY 1,3,2;
802
803 CURSOR rule_check IS
804 SELECT l.line_number line_number
805 FROM ra_customer_trx_lines l
806 WHERE l.customer_trx_id = p_customer_trx_id
807 AND (
808 l.accounting_rule_id IS NULL
809 OR
810 l.rule_start_date IS NULL
811 )
812 AND l.line_type = 'LINE'
813 ORDER BY l.line_number;
814
815 --Bug Fix: 3261620 Begin#2
816 CURSOR gl_account_ccid_cur IS
817 /* modified bug5444418*/
818 SELECT DISTINCT code_combination_id , gl_date, account_class,
819 account_set_flag
820 FROM ra_cust_trx_line_gl_dist
821 WHERE customer_trx_id = p_customer_trx_id;
822 --Bug Fix: 3261620 End#2
823
824 /* Bug3185358 */
825 l_status VARCHAR2(1);
826 BEGIN
827
828 IF PG_DEBUG in ('Y', 'C') THEN
829 arp_util.debug('arp_trx_completion_chk.do_completion_checking()+');
830 arp_util.debug(' p_check_tax_acct = ' || p_check_tax_acct);
831 END IF;
832 -- Bug 540962: l_error_count variable needs to be initialized.
833
834 p_error_count := 0;
835 l_error_count := 0;
836
837 /*-------------------------------------------------+
838 | Get information about the current transaction |
839 +-------------------------------------------------*/
840
841 IF PG_DEBUG in ('Y', 'C') THEN
842 arp_util.debug('Get information about the transaction');
843 END IF;
844
845 arp_ct_pkg.fetch_p(l_trx_rec,
846 p_customer_trx_id);
847
848
849 /* Bug 3185358 Additional check added to check whether
850 debit memo amount is lesser than original receipt amount .
851 This is applicable only for DM Reversals */
852
853 IF l_trx_rec.reversed_cash_receipt_id IS NOT NULL THEN
854 dm_reversal_amount_chk(l_trx_rec.customer_trx_id,l_trx_rec.reversed_cash_receipt_id,l_status);
855 IF l_status='E' THEN
856 arp_trx_validate.add_to_error_list(
857 p_error_mode,
858 l_error_count,
859 l_trx_rec.customer_trx_id,
860 l_trx_rec.trx_number,
861 NULL, -- line_number
862 NULL, -- other_line_number
863 'AR_DEBIT_REVERSAL_AMOUNT',
864 NULL, -- p_error_location,
865 NULL, -- token name 1
866 NULL, -- token 1
867 NULL, -- token name 2
868 NULL -- token 2
869 );
870 END IF;
871 END IF;
872 /* End of Bug 3185358 */
873
874
875 SELECT type,
876 creation_sign,
877 tax_calculation_flag
878 INTO l_class,
879 l_creation_sign,
880 l_tax_calculation_flag
881 FROM ra_cust_trx_types
882 WHERE cust_trx_type_id = l_trx_rec.cust_trx_type_id;
883
884 SELECT lgd_trx.gl_date,
885 lgd_trx.amount
886 INTO l_trx_gl_date,
887 l_trx_amount
888 FROM ra_cust_trx_line_gl_dist lgd_trx
889 WHERE lgd_trx.customer_trx_id = l_trx_rec.customer_trx_id
890 AND lgd_trx.latest_rec_flag = 'Y'
891 AND lgd_trx.account_class = 'REC';
892
893 /* Bug 2164863 - We now conditionally execute the check_tax_and_accounting
894 routine based on the value of p_check_tax_acct parameter.
895 Y - Execute only check_tax_and_accounting
896 N - Execute all other validations (except tax/acct)
897 B - Execute all validations (Y + N) */
898
899 IF (p_check_tax_acct = 'N' OR p_check_tax_acct = 'B')
900 THEN
901
902 /*-------------------------------------------------------------------+
903 | Get information about the transaction's commitment if one exists |
904 +-------------------------------------------------------------------*/
905
906 IF ( l_trx_rec.initial_customer_trx_id IS NOT NULL )
907 THEN
908
909 IF PG_DEBUG in ('Y', 'C') THEN
910 arp_util.debug('Get information about the commitment');
911 END IF;
912
913 arp_ct_pkg.fetch_p(l_commit_trx_rec,
914 l_trx_rec.initial_customer_trx_id);
915
916 SELECT lgd_trx.gl_date,
917 lgd_trx.amount
918 INTO l_commit_gl_date,
919 l_commit_amount
920 FROM ra_cust_trx_line_gl_dist lgd_trx
921 WHERE lgd_trx.customer_trx_id = l_trx_rec.initial_customer_trx_id
922 AND lgd_trx.latest_rec_flag = 'Y'
923 AND lgd_trx.account_class = 'REC';
924
925 END IF;
926
927 /*------------------------------------------------*
928 | validate that the transaction number is unique |
929 *------------------------------------------------*/
930
931 IF PG_DEBUG in ('Y', 'C') THEN
932 arp_util.debug('Insure that the transaction number is unique.');
933 END IF;
934
935 BEGIN
936 arp_trx_validate.validate_trx_number( l_trx_rec.batch_source_id,
937 l_trx_rec.trx_number,
938 l_trx_rec.customer_trx_id);
939
940 EXCEPTION
941 WHEN OTHERS THEN
942 IF PG_DEBUG in ('Y', 'C') THEN
943 arp_util.debug('ERROR: transaction number uniqueness check failed');
944 END IF;
945
946 arp_trx_validate.add_to_error_list(
947 p_error_mode,
948 l_error_count,
949 l_trx_rec.customer_trx_id,
950 l_trx_rec.trx_number,
951 NULL, -- line_number
952 NULL, -- other_line_number
953 'AR_TW_INVALID_TRX_NUMBER',
954 NULL, -- p_error_location,
955 NULL, -- token name 1
956 NULL, -- token 1
957 NULL, -- token name 2
958 NULL -- token 2
959 );
960
961 END;
962
963 /*---------------------------------------------*
964 | validate that the document number is unique |
965 *---------------------------------------------*/
966
967 IF PG_DEBUG in ('Y', 'C') THEN
968 arp_util.debug('Insure that the document number is unique.');
969 END IF;
970
971 /* 4537055 - Corrected this validation to use validate_doc_number.
972 it was using validate_trx_number (typo) before. Also exception
973 block was coded to raise another exception */
974
975 BEGIN
976 arp_trx_validate.validate_doc_number( l_trx_rec.batch_source_id,
977 l_trx_rec.doc_sequence_value,
978 l_trx_rec.customer_trx_id);
979
980 EXCEPTION
981 WHEN OTHERS THEN
982 IF PG_DEBUG in ('Y', 'C') THEN
983 arp_util.debug('ERROR: document number uniqueness check failed');
984 END IF;
985
986 arp_trx_validate.add_to_error_list(
987 p_error_mode,
988 l_error_count,
989 l_trx_rec.customer_trx_id,
990 l_trx_rec.trx_number,
991 NULL, -- line_number
992 NULL, -- other_line_number
993 'UNIQUE-DUPLICATE SEQUENCE',
994 NULL, -- p_error_location,
995 NULL, -- token name 1
996 NULL, -- token 1
997 NULL, -- token name 2
998 NULL -- token 2
999 );
1000
1001 END;
1002
1003 /*---------------------------------------------------------+
1004 | Insure that at least one line or freight line exists. |
1005 +---------------------------------------------------------*/
1006
1007 IF PG_DEBUG in ('Y', 'C') THEN
1008 arp_util.debug('Insure that at least one line or freight line exists.');
1009 END IF;
1010
1011 SELECT COUNT(*)
1012 INTO l_result
1013 FROM ra_customer_trx_lines
1014 WHERE customer_trx_id = p_customer_trx_id;
1015
1016 IF (l_result < 1)
1017 THEN
1018 IF PG_DEBUG in ('Y', 'C') THEN
1019 arp_util.debug('ERROR: line existance validation failed');
1020 END IF;
1021
1022 arp_trx_validate.add_to_error_list(
1023 p_error_mode,
1024 l_error_count,
1025 l_trx_rec.customer_trx_id,
1026 l_trx_rec.trx_number,
1027 NULL, -- line_number
1028 NULL, -- other_line_number
1029 'AR_TW_NO_LINES',
1030 NULL, -- p_error_location,
1031 NULL, -- token name 1
1032 NULL, -- token 1
1033 NULL, -- token name 2
1034 NULL -- token 2
1035 );
1036
1037 END IF;
1038
1039 --Bug 2141727 fix begins
1040 --Bug 4188835 - refit for eTax
1041 /*--------------------------------------------------------+
1042 | Insure that if Tax is calculated after discount then |
1043 | Payment term should be such that the discount is |
1044 | calculated based on Invoice Line amounts only. |
1045 +--------------------------------------------------------*/
1046 IF PG_DEBUG in ('Y', 'C') THEN
1047 arp_util.debug('Insure that Payment term and Tax code do not conflict');
1048 END IF;
1049
1050 SELECT count(*)
1051 INTO l_result
1052 FROM ra_terms tm,
1053 ra_customer_trx_lines cl,
1054 ra_customer_trx cs,
1055 zx_lines zl,
1056 zx_formula_b zf
1057 WHERE cs.customer_trx_id = p_customer_trx_id
1058 AND cs.term_id = tm.term_id
1059 AND tm.calc_discount_on_lines_flag <> 'L'
1060 AND cl.customer_trx_id = cs.customer_trx_id
1061 AND cl.line_type = 'TAX'
1062 AND cl.tax_line_id = zl.tax_line_id
1063 AND zl.taxable_basis_formula = zf.formula_code
1064 AND zl.tax_determine_date between zf.effective_from and nvl(zf.effective_to, zl.trx_date)
1065 AND zf.formula_type_code = 'TAXABLE_BASIS'
1066 AND zf.cash_discount_appl_flag = 'Y';
1067
1068 IF (l_result > 0)
1069 THEN
1070 IF PG_DEBUG in ('Y', 'C') THEN
1071 arp_util.debug('ERROR: Tax and payment term conflict exist');
1072 END IF;
1073 arp_trx_validate.add_to_error_list(
1074 p_error_mode,
1075 l_error_count,
1076 l_trx_rec.customer_trx_id,
1077 l_trx_rec.trx_number,
1078 NULL, -- line_number
1079 NULL, -- other_line_number
1080 'AR_TERM_TAX_CONFLICT',
1081 'TGW_HEADER.RAT_TERM_NAME_MIR', -- p_error_location,
1082 NULL, -- token name 1
1083 NULL, -- token 1
1084 NULL -- token 2
1085 );
1086 END IF;
1087 --Bug 2141727 fix ends.
1088
1089
1090 /*--------------------------------------------------------+
1091 | Insure that all entities that have start / end dates |
1092 | are valid for the specified trx date. |
1093 +--------------------------------------------------------*/
1094
1095 IF PG_DEBUG in ('Y', 'C') THEN
1096 arp_util.debug('Check date ranges');
1097 END IF;
1098
1099 arp_trx_validate.validate_trx_date(
1100 p_error_mode,
1101 l_trx_rec.trx_date,
1102 l_prev_trx_rec.trx_date,
1103 l_commit_trx_rec.trx_date,
1104 l_trx_rec.customer_trx_id,
1105 l_trx_rec.trx_number,
1106 l_trx_rec.previous_customer_trx_id,
1107 l_trx_rec.initial_customer_trx_id,
1108 l_trx_rec.agreement_id,
1109 l_trx_rec.batch_source_id,
1110 l_trx_rec.cust_trx_type_id,
1111 l_trx_rec.term_id,
1112 l_trx_rec.ship_via,
1113 l_trx_rec.primary_salesrep_id,
1114 l_trx_rec.reason_code,
1115 l_trx_rec.status_trx,
1116 l_trx_rec.invoice_currency_code,
1117 l_trx_rec.receipt_method_id,
1118 l_trx_rec.customer_bank_account_id,
1119 l_dummy_date,
1120 l_result_flag,
1121 l_dummy_flag,
1122 l_dummy_flag,
1123 l_dummy_flag,
1124 l_dummy_flag,
1125 l_dummy_flag,
1126 l_dummy_flag,
1127 l_dummy_flag,
1128 l_dummy_flag,
1129 l_dummy_flag,
1130 l_dummy_flag,
1131 l_dummy_flag,
1132 l_dummy_flag,
1133 l_dummy_flag,
1134 l_dummy_flag,
1135 l_dummy_flag,
1136 l_dummy_flag,
1137 l_dummy_flag,
1138 l_dummy_flag,
1139 l_error_count
1140 );
1141
1142 IF (l_result_flag = FALSE)
1143 THEN
1144
1145 IF PG_DEBUG in ('Y', 'C') THEN
1146 arp_util.debug( 'ERROR: date range validation failed');
1147 END IF;
1148 app_exception.raise_exception;
1149
1150 END IF;
1151
1152
1153 /*--------------------------------------------------------------+
1154 | Insure that the exchange rate fields are filled in if the |
1155 | transaction is in a foreign currency. |
1156 +--------------------------------------------------------------*/
1157
1158 IF PG_DEBUG in ('Y', 'C') THEN
1159 arp_util.debug('Insure that the exchange rate fields are filled in');
1160 END IF;
1161
1162 IF ( l_trx_rec.invoice_currency_code <> arp_global.functional_currency )
1163 THEN
1164 IF ( l_trx_rec.exchange_rate IS NULL )
1165 THEN
1166
1167 IF PG_DEBUG in ('Y', 'C') THEN
1168 arp_util.debug( 'ERROR: exchange rate is null');
1169 END IF;
1170
1171 arp_trx_validate.add_to_error_list(
1172 p_error_mode,
1173 l_error_count,
1174 l_trx_rec.customer_trx_id,
1175 l_trx_rec.trx_number,
1176 NULL, -- line_number
1177 NULL, -- other_line_number
1178 'AR_TW_NULL_EXCHANGE_RATE',
1179 'TGW_HEADER.EXCHANGE_RATE',
1180 -- p_error_location,
1181 NULL, -- token name 1
1182 NULL, -- token 1
1183 NULL, -- token name 2
1184 NULL -- token 2
1185 );
1186 END IF;
1187
1188 IF ( l_trx_rec.exchange_rate_type IS NULL )
1189 THEN
1190
1191 IF PG_DEBUG in ('Y', 'C') THEN
1192 arp_util.debug( 'ERROR: exchange rate type is null');
1193 END IF;
1194
1195 arp_trx_validate.add_to_error_list(
1196 p_error_mode,
1197 l_error_count,
1198 l_trx_rec.customer_trx_id,
1199 l_trx_rec.trx_number,
1200 NULL, -- line_number
1201 NULL, -- other_line_number
1202 'AR_TW_NULL_EXCHANGE_RATE_TYPE',
1203 'TGW_HEADER.EXCHANGE_RATE_TYPE',
1204 -- p_error_location,
1205 NULL, -- token name 1
1206 NULL, -- token 1
1207 NULL, -- token name 2
1208 NULL -- token 2
1209 );
1210 END IF;
1211
1212 IF ( l_trx_rec.exchange_date IS NULL )
1213 THEN
1214
1215 IF PG_DEBUG in ('Y', 'C') THEN
1216 arp_util.debug( 'ERROR: exchange rate date is null');
1217 END IF;
1218
1219 arp_trx_validate.add_to_error_list(
1220 p_error_mode,
1221 l_error_count,
1222 l_trx_rec.customer_trx_id,
1223 l_trx_rec.trx_number,
1224 NULL, -- line_number
1225 NULL, -- other_line_number
1226 'AR_TW_NULL_EXCHANGE_DATE',
1227 'TGW_HEADER.EXCHANGE_DATE',
1228 -- p_error_location,
1229 NULL, -- token name 1
1230 NULL, -- token 1
1231 NULL, -- token name 2
1232 NULL -- token 2
1233 );
1234 END IF;
1235
1236
1237 END IF;
1238
1239 /*----------------------------------------------------------------+
1240 | Insure that if a commitment has been specified, it is valid |
1241 | with the transaction's trx_date and gl_date |
1242 +----------------------------------------------------------------*/
1243
1244 /*----------------------------------------------------------+
1245 | The transaction's trx_date must be in the commitment's |
1246 | start / end date range. |
1247 +----------------------------------------------------------*/
1248
1249 IF PG_DEBUG in ('Y', 'C') THEN
1250 arp_util.debug( 'Check the dates of the transaction''s commitment');
1251 END IF;
1252
1253 IF ( l_trx_rec.initial_customer_trx_id IS NOT NULL )
1254 THEN
1255
1256
1257 IF (
1258 l_trx_rec.trx_date NOT BETWEEN
1259 NVL( l_commit_trx_rec.start_date_commitment,
1260 l_trx_rec.trx_date)
1261 AND
1262 NVL( l_commit_trx_rec.end_date_commitment,
1263 l_trx_rec.trx_date)
1264 )
1265 THEN
1266 IF PG_DEBUG in ('Y', 'C') THEN
1267 arp_util.debug(
1268 'ERROR: commitment dates do not include the trx_date');
1269 END IF;
1270
1271 arp_trx_validate.add_to_error_list(
1272 p_error_mode,
1273 l_error_count,
1274 l_trx_rec.customer_trx_id,
1275 l_trx_rec.trx_number,
1276 NULL, -- line_number
1277 NULL, -- other_line_number
1278 'AR_TW_BAD_COMMITMT_DATE_RANGE',
1279 'TGW_HEADER.EXCHANGE_DATE',
1280 'START_DATE',
1281 -- Bug 12722509
1282 fnd_date.date_to_chardate(l_commit_trx_rec.start_date_commitment, calendar_aware=> FND_DATE.calendar_aware_alt),
1283 'END_DATE',
1284 fnd_date.date_to_chardate(l_commit_trx_rec.end_date_commitment, calendar_aware=> FND_DATE.calendar_aware_alt)
1285 );
1286
1287 END IF;
1288
1289
1290 /*-----------------------------------------------------+
1291 | The transaction's GL Date must be on or after the |
1292 | commitment's GL Date. |
1293 +-----------------------------------------------------*/
1294
1295
1296 IF (
1297 l_commit_gl_date > l_trx_gl_date
1298 )
1299 THEN
1300
1301 IF PG_DEBUG in ('Y', 'C') THEN
1302 arp_util.debug( 'ERROR: The transaction''s GL date is before '||
1303 'the commitment''s GL date');
1304 END IF;
1305
1306 arp_trx_validate.add_to_error_list(
1307 p_error_mode,
1308 l_error_count,
1309 l_trx_rec.customer_trx_id,
1310 l_trx_rec.trx_number,
1311 NULL, -- line_number
1312 NULL, -- other_line_number
1313 'AR_TW_GL_DATE_BEFORE_COMMIT_GL',
1314 'TGW_HEADER.CT_COMMITMENT_NUMBER',
1315 -- p_error_location,
1316 NULL,
1317 NULL,
1318 'GL_DATE',
1319 -- Bug 12722509
1320 fnd_date.date_to_chardate(l_commit_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt)
1321 );
1322
1323 END IF;
1324
1325 END IF;
1326
1327
1328 /*---------------------------------------------------------------------+
1329 | If salescredits are required, the total salescredits for each line |
1330 | must equal 100% of the line amount. |
1331 | If salescredits are not required, either no salescredits exist for |
1332 | a line or they sum to 100%. |
1333 +---------------------------------------------------------------------*/
1334
1335 IF PG_DEBUG in ('Y', 'C') THEN
1336 arp_util.debug( 'Check salescredits');
1337 END IF;
1338
1339 BEGIN
1340 FOR l_error_rec IN salesrep_check LOOP
1341
1342 IF PG_DEBUG in ('Y', 'C') THEN
1343 arp_util.debug( 'ERROR: salescredits for line ' ||
1344 TO_CHAR(l_error_rec.line_number) ||
1345 ' are invalid');
1346 END IF;
1347
1348 /* Bug 2215439 - call to public sector API for industry specific message */
1349 arp_trx_validate.add_to_error_list(
1350 p_error_mode,
1351 l_error_count,
1352 l_trx_rec.customer_trx_id,
1353 l_trx_rec.trx_number,
1354 l_error_rec.line_number,
1355 NULL, -- other_line_number
1356 gl_public_sector.get_message_name
1357 (p_message_name =>
1358 'AR_TW_SALESCREDITS_INCOMPLT',
1359 p_app_short_name => 'AR'),
1360 NULL, -- p_error_location,
1361 'LINE_NUMBER',
1362 l_error_rec.line_number,
1363 'ERROR_AMOUNT',
1364 l_error_rec.error_amount
1365 );
1366
1367 END LOOP;
1368
1369 EXCEPTION
1370 WHEN NO_DATA_FOUND THEN NULL;
1371 WHEN OTHERS
1372 THEN RAISE;
1373 END;
1374
1375 /* Bug 2164863 - Only call check_tax_and_accounting here
1376 if all validations (B) specified */
1377 IF (p_check_tax_acct = 'B')
1378 THEN
1379 l_dummy_flag := check_tax_and_accounting(
1380 p_error_mode,
1381 p_customer_trx_id,
1382 l_trx_rec.previous_customer_trx_id,
1383 l_trx_rec.trx_number,
1384 l_class,
1385 l_tax_calculation_flag,
1386 l_trx_rec.invoicing_rule_id,
1387 l_error_count,
1388 l_dummy_number,
1389 l_dummy_number);
1390 END IF;
1391
1392 /*---------------------------------------------------------------------+
1393 | If an invoicing rule has been specified, |
1394 | verify that all lines have accounting rules and rule start dates. |
1395 +---------------------------------------------------------------------*/
1396
1397 IF PG_DEBUG in ('Y', 'C') THEN
1398 arp_util.debug( 'check that all lines have rule information if rules are '||
1399 'being used');
1400 END IF;
1401
1402 IF ( l_trx_rec.invoicing_rule_id IS NOT NULL )
1403 THEN
1404
1405 /* Its a Release 9 Invoice i.e l_rule_flag ='N', don't do checking * /
1406
1407 /* Modified For Bug 461391 */
1408 SELECT decode( max(d.customer_trx_id),
1409 null, 'N',
1410 'Y')
1411 INTO l_rule_flag
1412 FROM ra_customer_trx trx,
1413 ra_cust_trx_line_gl_dist d
1414 WHERE trx.customer_trx_id = l_trx_rec.customer_trx_id
1415 and trx.previous_customer_trx_id = d.customer_trx_id
1416 and d.account_class in ('UNEARN', 'UNBILL');
1417 IF ( l_rule_flag ='Y')
1418 THEN
1419
1420
1421 BEGIN
1422 FOR l_error_rec IN rule_check LOOP
1423
1424 IF PG_DEBUG in ('Y', 'C') THEN
1425 arp_util.debug( 'ERROR: the rule information is invalid ' ||
1426 ' for line ' ||
1427 TO_CHAR(l_error_rec.line_number));
1428 END IF;
1429
1430 arp_trx_validate.add_to_error_list(
1431 p_error_mode,
1432 l_error_count,
1433 l_trx_rec.customer_trx_id,
1434 l_trx_rec.trx_number,
1435 l_error_rec.line_number,
1436 NULL, -- other_line_number
1437 'AR_TW_LINE_RULE_INCOMPLETE',
1438 NULL, -- p_error_location,
1439 'LINE_NUMBER',
1440 l_error_rec.line_number,
1441 NULL,
1442 NULL
1443 );
1444
1445 END LOOP;
1446
1447 EXCEPTION
1448 WHEN NO_DATA_FOUND THEN NULL;
1449 WHEN OTHERS
1450 THEN RAISE;
1451 END;
1452
1453 END IF;
1454 END IF;
1455
1456 /*----------------------------------------------+
1457 | Check the creation sign of the transaction |
1458 +----------------------------------------------*/
1459
1460 IF PG_DEBUG in ('Y', 'C') THEN
1461 arp_util.debug( 'check creation sign');
1462 END IF;
1463
1464 arp_non_db_pkg.check_creation_sign(
1465 l_creation_sign,
1466 l_trx_amount,
1467 NULL,
1468 l_error_message
1469 );
1470 IF ( l_error_message IS NOT NULL )
1471 THEN
1472
1473 IF PG_DEBUG in ('Y', 'C') THEN
1474 arp_util.debug( 'ERROR: the transaction violate the creation sign');
1475 END IF;
1476
1477 arp_trx_validate.add_to_error_list(
1478 p_error_mode,
1479 l_error_count,
1480 l_trx_rec.customer_trx_id,
1481 l_trx_rec.trx_number,
1482 NULL, -- line_number
1483 NULL, -- other_line_number
1484 l_error_message,
1485 'TGW_HEADER.CTT_TYPE_NAME',
1486 NULL,
1487 NULL,
1488 NULL,
1489 NULL
1490 );
1491
1492 END IF;
1493
1494
1495
1496 /*---------------------+
1497 | Check the GL Date |
1498 +---------------------*/
1499
1500 IF PG_DEBUG in ('Y', 'C') THEN
1501 arp_util.debug( 'check the GL date');
1502 END IF;
1503
1504 /*------------------------------------------------------+
1505 | Verify that the GL Date is in an Opened, Future or |
1506 | Never Opened (Arrears only) Period. |
1507 +------------------------------------------------------*/
1508
1509 l_result_flag := arp_util.validate_and_default_gl_date(
1510 l_trx_gl_date,
1511 NULL,
1512 NULL,
1513 NULL,
1514 null,
1515 l_trx_gl_date,
1516 null,
1517 null,
1518 null,
1519 TO_CHAR(l_trx_rec.invoicing_rule_id),
1520 pg_set_of_books_id,
1521 222,
1522 l_default_gl_date,
1523 l_dummy,
1524 l_dummy);
1525
1526 IF ( l_trx_gl_date <> l_default_gl_date )
1527 THEN
1528 IF PG_DEBUG in ('Y', 'C') THEN
1529 arp_util.debug( 'ERROR: the GL date is invalid');
1530 END IF;
1531
1532 arp_trx_validate.add_to_error_list(
1533 p_error_mode,
1534 l_error_count,
1535 l_trx_rec.customer_trx_id,
1536 l_trx_rec.trx_number,
1537 NULL,
1538 NULL, -- other_line_number
1539 'AR_INVALID_APP_GL_DATE',
1540 'TGW_HEADER.GD_GL_DATE',
1541 'GL_DATE',
1542 l_trx_gl_date,
1543 NULL,
1544 NULL
1545 );
1546
1547 END IF;
1548
1549
1550
1551 /*==================================+
1552 | Credit Memo Validations Follow |
1553 +==================================*/
1554
1555 IF ( l_trx_rec.previous_customer_trx_id IS NOT NULL )
1556 THEN
1557
1558 IF PG_DEBUG in ('Y', 'C') THEN
1559 arp_util.debug( 'check credit memos against specific transactions');
1560 arp_util.debug( 'get credit memo information');
1561 END IF;
1562
1563 arp_ct_pkg.fetch_p(l_prev_trx_rec,
1564 l_trx_rec.previous_customer_trx_id);
1565
1566 SELECT lgd_trx.gl_date
1567 INTO l_prev_gl_date
1568 FROM ra_cust_trx_line_gl_dist lgd_trx
1569 WHERE lgd_trx.customer_trx_id = l_trx_rec.previous_customer_trx_id
1570 AND lgd_trx.latest_rec_flag = 'Y'
1571 AND lgd_trx.account_class = 'REC';
1572
1573 SELECT allow_overapplication_flag,
1574 natural_application_only_flag,
1575 accounting_affect_flag,
1576 type
1577 INTO l_allow_overapplication_flag,
1578 l_natural_app_only_flag,
1579 l_open_receivables_flag,
1580 l_credited_class
1581 FROM ra_cust_trx_types
1582 WHERE cust_trx_type_id = l_prev_trx_rec.cust_trx_type_id;
1583
1584
1585 /*--------------------------------------+
1586 | Get the amounts of the credit memo |
1587 +--------------------------------------*/
1588
1589 SELECT SUM(
1590 DECODE( ctl.line_type,
1591 'LINE', ctl.extended_amount,
1592 'CHARGES', ctl.extended_amount,
1593 0 )
1594 ),
1595 SUM(
1596 DECODE( ctl.line_type,
1597 'TAX', ctl.extended_amount,
1598 0 )
1599 ),
1600 SUM(
1601 DECODE( ctl.line_type,
1602 'FREIGHT', ctl.extended_amount,
1603 0 )
1604 )
1605 INTO l_line_amount,
1606 l_tax_amount,
1607 l_freight_amount
1608 FROM ra_customer_trx_lines ctl
1609 WHERE customer_trx_id = l_trx_rec.customer_trx_id;
1610
1611
1612 /*-------------------------------------------------+
1613 | Get the balances for the credited transaction |
1614 +-------------------------------------------------*/
1615
1616 arp_trx_util.get_summary_trx_balances( l_trx_rec.previous_customer_trx_id,
1617 l_open_receivables_flag,
1618 l_prev_line_original,
1619 l_prev_line_remaining,
1620 l_prev_tax_original,
1621 l_prev_tax_remaining,
1622 l_prev_freight_original,
1623 l_prev_freight_remaining,
1624 l_prev_charges_original,
1625 l_prev_charges_remaining,
1626 l_prev_total_original,
1627 l_prev_total_remaining);
1628
1629 IF PG_DEBUG in ('Y', 'C') THEN
1630 arp_util.debug( 'previous_customer_trx_id : '||l_trx_rec.previous_customer_trx_id);
1631 arp_util.debug( 'l_line_amount : '||l_line_amount);
1632 arp_util.debug( 'l_tax_amount : '||l_tax_amount);
1633 arp_util.debug( 'l_freight_amount : '||l_freight_amount);
1634 arp_util.debug( 'l_open_receivables_flag : '||l_open_receivables_flag);
1635 arp_util.debug( 'l_prev_line_original : '||l_prev_line_original);
1636 arp_util.debug( 'l_prev_line_remaining : '||l_prev_line_remaining);
1637 arp_util.debug( 'l_prev_tax_original : '||l_prev_tax_original);
1638 arp_util.debug( 'l_prev_tax_remaining : '||l_prev_tax_remaining);
1639 arp_util.debug( 'l_prev_freight_original : '||l_prev_freight_original);
1640 arp_util.debug( 'l_prev_freight_remaining : '||l_prev_freight_remaining);
1641 arp_util.debug( 'l_prev_total_original : '||l_prev_total_original);
1642 arp_util.debug( 'l_prev_total_remaining : '||l_prev_total_remaining);
1643 arp_util.debug( 'l_open_receivables_flag : '||l_open_receivables_flag);
1644 END IF;
1645
1646 /* Bug 882789: Get commitment adjustment amount for the credited
1647 transaction. This amount should be added to l_prev_total_remaining
1648 when checking natural application since the commitment adjustment
1649 will be reversed when we complete the credit memo. Otherwise,
1650 natural application checking will fail since the credit amount
1651 is more than the amount remaining for the credited transaction */
1652
1653 /* Bug 2534132: Get Line,tax and freight buckets of the Commitment Adjustment
1654 and add to the line_remaining, tax_remaining and freight_remaining while
1655 checking natural application since the commitment adjustment will be reversed
1656 when we complete the credit memo. */
1657
1658 select nvl(sum(amount),0),nvl(sum(line_adjusted),0),nvl(sum(tax_adjusted),0),nvl(sum(freight_adjusted),0)
1659 into l_commit_adj_amount,l_commit_line_amount,l_commit_tax_amount,l_commit_frt_amount
1660 from ar_adjustments
1661 where customer_trx_id = l_trx_rec.previous_customer_trx_id
1662 and receivables_trx_id = -1;
1663
1664 IF PG_DEBUG in ('Y', 'C') THEN
1665 arp_util.debug( 'l_commit_adj_amount : ' || to_char(l_commit_adj_amount));
1666 END IF;
1667
1668 /*----------------------------------------------------+
1669 | For credit memos against specific transactions, |
1670 | check for illegal overapplications. |
1671 +----------------------------------------------------*/
1672
1673 /*----------------------------------------+
1674 | Check the transaction's total amount |
1675 +----------------------------------------*/
1676
1677 IF PG_DEBUG in ('Y', 'C') THEN
1678 arp_util.debug( 'check for total overapplication');
1679 END IF;
1680
1681 /* Bug 882789: minus l_commit_adj_amount from l_prev_total_remaining */
1682 arp_non_db_pkg.check_natural_application(
1683 l_creation_sign,
1684 l_allow_overapplication_flag,
1685 l_natural_app_only_flag,
1686 '+', -- p_sign_of_ps
1687 'Y', -- p_chk_overapp_if_zero
1688 l_trx_amount,
1689 0, -- p_discount_taken
1690 l_prev_total_remaining -
1691 l_commit_adj_amount,
1692 l_prev_total_original,
1693 NULL, -- event
1694 l_error_message
1695 );
1696
1697 IF ( l_error_message IS NOT NULL )
1698 THEN
1699
1700 IF PG_DEBUG in ('Y', 'C') THEN
1701 arp_util.debug( 'ERROR: overapplication of the total amount');
1702 END IF;
1703
1704 arp_trx_validate.add_to_error_list(
1705 p_error_mode,
1706 l_error_count,
1707 l_trx_rec.customer_trx_id,
1708 l_trx_rec.trx_number,
1709 NULL, -- line_number
1710 NULL, -- other_line_number
1711 l_error_message,
1712 NULL,
1713 NULL,
1714 NULL,
1715 NULL,
1716 NULL
1717 );
1718
1719 END IF;
1720
1721
1722 /*---------------------------------------+
1723 | Check the transaction's line amount |
1724 +---------------------------------------*/
1725
1726 IF PG_DEBUG in ('Y', 'C') THEN
1727 arp_util.debug( 'check for line overapplication');
1728 END IF;
1729
1730 /* Bug 882789: minus l_commit_adj_amount from l_prev_line_remaining */
1731 arp_non_db_pkg.check_natural_application(
1732 l_creation_sign,
1733 l_allow_overapplication_flag,
1734 l_natural_app_only_flag,
1735 '+', -- p_sign_of_ps
1736 'Y', -- p_chk_overapp_if_zero
1737 l_line_amount,
1738 0, -- p_discount_taken
1739 l_prev_line_remaining -
1740 l_commit_line_amount, /* Bug2534132*/
1741 l_prev_line_original,
1742 NULL, -- event
1743 l_error_message
1744 );
1745
1746 IF ( l_error_message IS NOT NULL )
1747 THEN
1748
1749 IF PG_DEBUG in ('Y', 'C') THEN
1750 arp_util.debug( 'ERROR: overapplication of the line amount');
1751 END IF;
1752
1753 arp_trx_validate.add_to_error_list(
1754 p_error_mode,
1755 l_error_count,
1756 l_trx_rec.customer_trx_id,
1757 l_trx_rec.trx_number,
1758 NULL, -- line_number
1759 NULL, -- other_line_number
1760 l_error_message,
1761 NULL,
1762 NULL,
1763 NULL,
1764 NULL,
1765 NULL
1766 );
1767
1768 END IF;
1769
1770 /*--------------------------------------+
1771 | Check the transaction's tax amount |
1772 +--------------------------------------*/
1773
1774 IF PG_DEBUG in ('Y', 'C') THEN
1775 arp_util.debug( 'check for tax overapplication');
1776 END IF;
1777
1778 arp_non_db_pkg.check_natural_application(
1779 l_creation_sign,
1780 l_allow_overapplication_flag,
1781 l_natural_app_only_flag,
1782 '+', -- p_sign_of_ps
1783 'Y', -- p_chk_overapp_if_zero
1784 l_tax_amount,
1785 0, -- p_discount_taken
1786 l_prev_tax_remaining -
1787 l_commit_tax_amount,/*Bug2534132*/
1788 l_prev_tax_original,
1789 NULL, -- event
1790 l_error_message
1791 );
1792
1793 IF ( l_error_message IS NOT NULL )
1794 THEN
1795
1796 IF PG_DEBUG in ('Y', 'C') THEN
1797 arp_util.debug( 'ERROR: overapplication of the tax amount');
1798 END IF;
1799
1800 arp_trx_validate.add_to_error_list(
1801 p_error_mode,
1802 l_error_count,
1803 l_trx_rec.customer_trx_id,
1804 l_trx_rec.trx_number,
1805 NULL, -- line_number
1806 NULL, -- other_line_number
1807 l_error_message,
1808 NULL,
1809 NULL,
1810 NULL,
1811 NULL,
1812 NULL
1813 );
1814
1815 END IF;
1816
1817 /*------------------------------------------+
1818 | Check the transaction's freight amount |
1819 +------------------------------------------*/
1820
1821 IF PG_DEBUG in ('Y', 'C') THEN
1822 arp_util.debug( 'check for freight overapplication');
1823 END IF;
1824
1825 arp_non_db_pkg.check_natural_application(
1826 l_creation_sign,
1827 l_allow_overapplication_flag,
1828 l_natural_app_only_flag,
1829 '+', -- p_sign_of_ps
1830 'Y', -- p_chk_overapp_if_zero
1831 l_freight_amount,
1832 0, -- p_discount_taken
1833 l_prev_freight_remaining -
1834 l_commit_frt_amount, /*Bug2534132*/
1835 l_prev_freight_original,
1836 NULL, -- event
1837 l_error_message
1838 );
1839
1840 IF ( l_error_message IS NOT NULL )
1841 THEN
1842
1843 IF PG_DEBUG in ('Y', 'C') THEN
1844 arp_util.debug( 'ERROR: overapplication of the freight amount');
1845 END IF;
1846
1847 arp_trx_validate.add_to_error_list(
1848 p_error_mode,
1849 l_error_count,
1850 l_trx_rec.customer_trx_id,
1851 l_trx_rec.trx_number,
1852 NULL, -- line_number
1853 NULL, -- other_line_number
1854 l_error_message,
1855 NULL,
1856 NULL,
1857 NULL,
1858 NULL,
1859 NULL
1860 );
1861
1862 END IF;
1863
1864 /*---------------------------------------------------------------+
1865 | Insure that the credit memo does not overapply the Deposit |
1866 | that it is crediting. |
1867 +---------------------------------------------------------------*/
1868
1869 IF PG_DEBUG in ('Y', 'C') THEN
1870 arp_util.debug( 'check for commitment balance overapplication');
1871 END IF;
1872
1873 IF ( l_credited_class = 'DEP' )
1874 THEN
1875
1876 /*---------------------------------------------------------------+
1877 | Get the so_source_code and so_installed_flag values |
1878 | from the passed in parameter, the cached value ffrom the DB. |
1879 +---------------------------------------------------------------*/
1880
1881 IF (p_so_source_code IS NOT NULL )
1882 THEN
1883 pg_so_source_code := p_so_source_code;
1884 l_so_source_code := pg_so_source_code;
1885 ELSE
1886 IF (pg_so_source_code IS NOT NULL)
1887 THEN
1888 l_so_source_code := pg_so_source_code;
1889 ELSE
1890 -- OE/OM change
1891 -- fnd_profile.get( 'SO_SOURCE_CODE', l_so_source_code );
1892 oe_profile.get( 'SO_SOURCE_CODE', l_so_source_code );
1893 pg_so_source_code := l_so_source_code;
1894 END IF;
1895 END IF;
1896
1897 IF (p_so_installed_flag IS NOT NULL )
1898 THEN
1899 pg_so_installed_flag := p_so_installed_flag;
1900 l_so_installed_flag := pg_so_installed_flag;
1901 ELSE
1902 IF (pg_so_installed_flag IS NOT NULL)
1903 THEN
1904 l_so_installed_flag := pg_so_installed_flag;
1905 ELSE
1906 l_so_installed_flag := 'N';
1907 l_result_flag := fnd_installation.get_app_info('OE',
1908 l_so_installed_flag,
1909 l_dummy,
1910 l_dummy);
1911
1912 pg_so_installed_flag := l_so_installed_flag;
1913 END IF;
1914 END IF;
1915
1916 /* Bug 3249432. Check for commitment overapplication only if trx is incomplete. */
1917
1918 IF l_trx_rec.complete_flag = 'N'
1919 AND ( NOT arp_trx_val.check_commitment_overapp(
1920 l_trx_rec.previous_customer_trx_id,
1921 l_credited_class,
1922 l_commit_amount,
1923 ABS(l_trx_amount),
1924 l_so_source_code,
1925 p_so_installed_flag,
1926 l_commitment_balance) )
1927 THEN
1928 IF PG_DEBUG in ('Y', 'C') THEN
1929 arp_util.debug(
1930 'ERROR: overapplication of the commitment balance');
1931 END IF;
1932
1933 arp_trx_validate.add_to_error_list(
1934 p_error_mode,
1935 l_error_count,
1936 l_trx_rec.customer_trx_id,
1937 l_trx_rec.trx_number,
1938 NULL,
1939 NULL, -- other_line_number
1940 'AR_TW_CM_COMMIT_BAL_OVERAPP',
1941 NULL,
1942 'COMMITMENT_BALANCE',
1943 TO_CHAR(l_commitment_balance),
1944 NULL,
1945 NULL
1946 );
1947 END IF;
1948
1949 END IF;
1950
1951 /*-------------------------------------------------------+
1952 | The GL Date must be >= the credited transaction's |
1953 | GL Date. |
1954 +-------------------------------------------------------*/
1955
1956 IF PG_DEBUG in ('Y', 'C') THEN
1957 arp_util.debug( 'check the GL date against the credited transaction''s');
1958 END IF;
1959
1960 IF ( l_trx_gl_date < l_prev_gl_date )
1961 THEN
1962
1963 IF PG_DEBUG in ('Y', 'C') THEN
1964 arp_util.debug(
1965 'ERROR: GL date is before the credited transaction''s');
1966 END IF;
1967
1968 arp_trx_validate.add_to_error_list(
1969 p_error_mode,
1970 l_error_count,
1971 l_trx_rec.customer_trx_id,
1972 l_trx_rec.trx_number,
1973 NULL, -- line number
1974 NULL, -- other_line_number
1975 'AR_TW_GL_DATE_BEFORE_INV_GL_DT',
1976 'TGW_HEADER.GD_GL_DATE',
1977 'GL_DATE',
1978 -- Bug 12722509
1979 fnd_date.date_to_chardate(l_prev_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt),
1980 NULL,
1981 NULL
1982 );
1983
1984 END IF;
1985
1986 /*-------------------------------------------------------------------------+
1987 | For credit memos against specific transactions, |
1988 | check for later credit memos. |
1989 | |
1990 | If any other credit memos against the same invoice have been completed |
1991 | between the time when this credit memo was created and when it is made |
1992 | complete, the accounting for this CM will be wrong. For example, if the|
1993 | Credit Method For Split Term Invoices is LIFO, then the same periods |
1994 | could be credited more than once. In this case, the accounting for the |
1995 | credit memo must be redone before the CM can be made complete. |
1996 +-------------------------------------------------------------------------*/
1997
1998 IF PG_DEBUG in ('Y', 'C') THEN
1999 arp_util.debug( 'check for later credit memos');
2000 END IF;
2001 SELECT MAX( other_ct.customer_trx_id )
2002 INTO l_result
2003 FROM ra_customer_trx other_ct,
2004 ra_customer_trx this_ct
2005 WHERE this_ct.customer_trx_id = p_customer_trx_id
2006 AND other_ct.previous_customer_trx_id =
2007 this_ct.previous_customer_trx_id
2008 AND other_ct.customer_trx_id <> this_ct.customer_trx_id
2009 /*3606541*/
2010 AND other_ct.creation_date > this_ct.creation_date
2011 AND NVL(other_ct.complete_flag,'N')='Y';
2012
2013 IF ( l_result IS NOT NULL )
2014 THEN
2015
2016 IF PG_DEBUG in ('Y', 'C') THEN
2017 arp_util.debug( 'ERROR: later credit memos exist');
2018 END IF;
2019
2020 arp_trx_validate.add_to_error_list(
2021 p_error_mode,
2022 l_error_count,
2023 l_trx_rec.customer_trx_id,
2024 l_trx_rec.trx_number,
2025 NULL,
2026 NULL, -- other_line_number
2027 'AR_TW_STALE_CM',
2028 NULL,
2029 NULL,
2030 NULL,
2031 NULL,
2032 NULL
2033 );
2034
2035 END IF;
2036
2037
2038
2039 END IF; -- credit memo against specific transaction case
2040
2041 /* Bug 2164863 - p_check_tax_acct = 'Y' condition */
2042 ELSIF (p_check_tax_acct = 'Y')
2043 THEN
2044
2045 l_dummy_flag := check_tax_and_accounting(
2046 p_error_mode,
2047 p_customer_trx_id,
2048 l_trx_rec.previous_customer_trx_id,
2049 l_trx_rec.trx_number,
2050 l_class,
2051 l_tax_calculation_flag,
2052 l_trx_rec.invoicing_rule_id,
2053 l_error_count,
2054 l_dummy_number,
2055 l_dummy_number);
2056
2057 END IF;
2058
2059 --Bug Fix: 3261620 Begin#3
2060 /*-------------------------------------------------------------------------+
2061 |Bug Fix: 3261620 |
2062 | Check whether the GL Accounts of the current transaction are either|
2063 |invalid or end dated. |
2064 +-------------------------------------------------------------------------*/
2065 IF PG_DEBUG in ('Y', 'C') THEN
2066 arp_util.debug( 'Check for invalid GL Accounts');
2067 END IF;
2068 /* Bug fix 4398445
2069 Check if the transaction is revenue recognized */
2070
2071 select decode(account_set_flag,'Y','N','N','Y','Y')
2072 into l_revrec_complete
2073 from ra_cust_trx_line_gl_dist
2074 where customer_trx_id = p_customer_trx_id
2075 and account_class = 'REC'
2076 and latest_rec_flag = 'Y';
2077
2078 IF PG_DEBUG in ('Y', 'C') THEN
2079 arp_util.debug('do_completion_checking: ' || 'Revenue Recognition Complete Flag : '||l_revrec_complete);
2080 END IF;
2081 /*End bug5444418*/
2082
2083 OPEN gl_account_ccid_cur;
2084 LOOP
2085 FETCH gl_account_ccid_cur INTO l_gl_account_ccid,l_dist_gl_date,
2086 l_account_class, l_account_set_flag;
2087 EXIT WHEN gl_account_ccid_cur%NOTFOUND;
2088 /* Bug fix 5444418
2089 Do not check the validity of the CCID if it is derived from the Invoice */
2090 IF NOT (l_trx_rec.invoicing_rule_id IS NOT NULL AND
2091 l_account_class in ('REV','UNEARN','UNBILL') AND
2092 l_account_set_flag = 'N' AND
2093 l_revrec_complete = 'N') THEN
2094 IF PG_DEBUG in ('Y', 'C') THEN
2095 arp_util.debug('do_completion_checking: '||'Checking CCID '||l_gl_account_ccid);
2096 END IF;
2097 IF (
2098 -- If the GL Account is excluded using a Security Rule
2099 -- 3567612 : pass ARP_GLOBAL.chart_of_accounts_id instead of 101
2100 ( NOT fnd_flex_keyval.validate_ccid ( appl_short_name => 'SQLGL',
2101 key_flex_code => 'GL#',
2102 structure_number => ARP_GLOBAL.chart_of_accounts_id,
2103 combination_id => l_gl_account_ccid,
2104 security => 'ENFORCE'
2105 )
2106 )
2107 OR
2108 -- If the GL Account is End Dated
2109 (fnd_flex_keyval.start_date > l_dist_gl_date)
2110 OR
2111 (fnd_flex_keyval.end_date < l_dist_gl_date)
2112 /*3679531*/
2113 OR (fnd_flex_keyval.enabled_flag=FALSE)
2114 )
2115 THEN
2116
2117 IF PG_DEBUG in ('Y', 'C') THEN
2118 arp_util.debug(
2119 'Error: Atleast one of the GL Accounts is either invalid or end dated');
2120 END IF;
2121 arp_trx_validate.add_to_error_list(
2122 p_error_mode,
2123 l_error_count,
2124 l_trx_rec.customer_trx_id,
2125 l_trx_rec.trx_number,
2126 NULL, -- line_number
2127 NULL, -- other_line_number
2128 'AR_INVALID_GL_ACCOUNT',--Message Name
2129 NULL, -- p_error_location,
2130 NULL, -- token name 1
2131 NULL, -- token 1
2132 NULL, -- token name 2
2133 NULL -- token 2
2134 );
2135 END IF;
2136 END IF;
2137 END LOOP;
2138 CLOSE gl_account_ccid_cur;
2139
2140 --Bug Fix: 3261620 End#3
2141
2142 p_error_count := l_error_count;
2143
2144 IF PG_DEBUG in ('Y', 'C') THEN
2145 arp_util.debug( 'completion error count: ' || to_char(l_error_count));
2146 arp_util.debug('arp_trx_completion_chk.do_completion_checking()-');
2147 END IF;
2148
2149 EXCEPTION
2150 WHEN OTHERS THEN
2151 IF PG_DEBUG in ('Y', 'C') THEN
2152 arp_util.debug(
2153 'EXCEPTION: arp_trx_completion_chk.do_completion_checking()');
2154 END IF;
2155 RAISE;
2156
2157 END do_completion_checking;
2158
2159 /* Bug 3185358 */
2160 PROCEDURE dm_reversal_amount_chk(
2161 p_customer_trx_id IN
2162 ra_customer_trx.customer_trx_id%type,
2163 p_reversed_cash_receipt_id IN
2164 ra_customer_trx.reversed_cash_receipt_id%type,
2165 p_status OUT NOCOPY VARCHAR2) IS
2166 l_original_amount ra_customer_trx_lines.gross_extended_amount%type;
2167 l_dm_amount ra_customer_trx_lines.gross_extended_amount%type;
2168 line_amount ra_customer_trx_lines.gross_extended_amount%type;
2169 tax_amount ra_customer_trx_lines.gross_extended_amount%type;
2170 frt_amount ra_customer_trx_lines.gross_extended_amount%type;
2171 BEGIN
2172 /* step 1: Fetch the original receipt amount from ar_cash_receipts
2173 step 2: Fetch total amount from trx (dm)
2174 step 3: compare .
2175 step 4: if dm amount < origianl return "E"
2176 else return "S"
2177 */
2178 /* Fetch Original receipt amount */
2179 l_original_amount:=0;
2180 BEGIN
2181 SELECT amount INTO
2182 l_original_amount
2183 FROM
2184 AR_CASH_RECEIPTS WHERE cash_receipt_id=p_reversed_cash_receipt_id;
2185 EXCEPTION
2186 WHEN OTHERS THEN
2187 l_original_amount:=0;
2188 END;
2189
2190 /* Fetch Amount due original for debit memo */
2191 l_dm_amount:=0;
2192 line_amount:=0;
2193 tax_amount:=0;
2194 frt_amount:=0;
2195
2196 SELECT sum(decode(ctl.line_type, 'LINE', ctl.extended_amount,
2197 'CB', ctl.extended_amount, 0)),
2198 sum(decode(ctl.line_type, 'TAX', ctl.extended_amount, 0)),
2199 sum(decode(ctl.line_type, 'FREIGHT', ctl.extended_amount, 0))
2200 INTO
2201 line_amount,
2202 tax_amount,
2203 frt_amount
2204 FROM
2205 ra_customer_trx_lines ctl
2206 WHERE ctl.customer_trx_id = p_customer_trx_id;
2207
2208 l_dm_amount:=nvl(line_amount,0)+nvl(tax_amount,0)+nvl(frt_amount,0);
2209
2210 /* Debit memo amount < original amount Raise error */
2211
2212 IF l_dm_amount < l_original_amount THEN
2213 p_status:='E';
2214 ELSE
2215 p_status:='S';
2216 END IF;
2217 END dm_reversal_amount_chk;
2218
2219
2220 /*---------------------------------------------+
2221 | Package initialization section. |
2222 +---------------------------------------------*/
2223 PROCEDURE init IS
2224 BEGIN
2225
2226 pg_base_curr_code := arp_global.functional_currency;
2227 pg_salesrep_required_flag :=
2228 arp_trx_global.system_info.system_parameters.salesrep_required_flag;
2229 pg_set_of_books_id :=
2230 arp_trx_global.system_info.system_parameters.set_of_books_id;
2231 END init;
2232
2233 BEGIN
2234 init;
2235 END ARP_TRX_COMPLETE_CHK;