[Home] [Help]
PACKAGE BODY: APPS.ARP_TRX_COMPLETE_CHK
Source
1 PACKAGE BODY ARP_TRX_COMPLETE_CHK AS
2 /* $Header: ARTUVA2B.pls 120.23.12010000.2 2008/11/04 09:31:03 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 TO_CHAR(l_commit_trx_rec.start_date_commitment),
1282 'END_DATE',
1283 TO_CHAR(l_commit_trx_rec.end_date_commitment)
1284 );
1285
1286 END IF;
1287
1288
1289 /*-----------------------------------------------------+
1290 | The transaction's GL Date must be on or after the |
1291 | commitment's GL Date. |
1292 +-----------------------------------------------------*/
1293
1294
1295 IF (
1296 l_commit_gl_date > l_trx_gl_date
1297 )
1298 THEN
1299
1300 IF PG_DEBUG in ('Y', 'C') THEN
1301 arp_util.debug( 'ERROR: The transaction''s GL date is before '||
1302 'the commitment''s GL date');
1303 END IF;
1304
1305 arp_trx_validate.add_to_error_list(
1306 p_error_mode,
1307 l_error_count,
1308 l_trx_rec.customer_trx_id,
1309 l_trx_rec.trx_number,
1310 NULL, -- line_number
1311 NULL, -- other_line_number
1312 'AR_TW_GL_DATE_BEFORE_COMMIT_GL',
1313 'TGW_HEADER.CT_COMMITMENT_NUMBER',
1314 -- p_error_location,
1315 NULL,
1316 NULL,
1317 'GL_DATE',
1318 TO_CHAR(l_commit_gl_date)
1319 );
1320
1321 END IF;
1322
1323 END IF;
1324
1325
1326 /*---------------------------------------------------------------------+
1327 | If salescredits are required, the total salescredits for each line |
1328 | must equal 100% of the line amount. |
1329 | If salescredits are not required, either no salescredits exist for |
1330 | a line or they sum to 100%. |
1331 +---------------------------------------------------------------------*/
1332
1333 IF PG_DEBUG in ('Y', 'C') THEN
1334 arp_util.debug( 'Check salescredits');
1335 END IF;
1336
1337 BEGIN
1338 FOR l_error_rec IN salesrep_check LOOP
1339
1340 IF PG_DEBUG in ('Y', 'C') THEN
1341 arp_util.debug( 'ERROR: salescredits for line ' ||
1342 TO_CHAR(l_error_rec.line_number) ||
1343 ' are invalid');
1344 END IF;
1345
1346 /* Bug 2215439 - call to public sector API for industry specific message */
1347 arp_trx_validate.add_to_error_list(
1348 p_error_mode,
1349 l_error_count,
1350 l_trx_rec.customer_trx_id,
1351 l_trx_rec.trx_number,
1352 l_error_rec.line_number,
1353 NULL, -- other_line_number
1354 gl_public_sector.get_message_name
1355 (p_message_name =>
1356 'AR_TW_SALESCREDITS_INCOMPLT',
1357 p_app_short_name => 'AR'),
1358 NULL, -- p_error_location,
1359 'LINE_NUMBER',
1360 l_error_rec.line_number,
1361 'ERROR_AMOUNT',
1362 l_error_rec.error_amount
1363 );
1364
1365 END LOOP;
1366
1367 EXCEPTION
1368 WHEN NO_DATA_FOUND THEN NULL;
1369 WHEN OTHERS
1370 THEN RAISE;
1371 END;
1372
1373 /* Bug 2164863 - Only call check_tax_and_accounting here
1374 if all validations (B) specified */
1375 IF (p_check_tax_acct = 'B')
1376 THEN
1377 l_dummy_flag := check_tax_and_accounting(
1378 p_error_mode,
1379 p_customer_trx_id,
1380 l_trx_rec.previous_customer_trx_id,
1381 l_trx_rec.trx_number,
1382 l_class,
1383 l_tax_calculation_flag,
1384 l_trx_rec.invoicing_rule_id,
1385 l_error_count,
1386 l_dummy_number,
1387 l_dummy_number);
1388 END IF;
1389
1390 /*---------------------------------------------------------------------+
1391 | If an invoicing rule has been specified, |
1392 | verify that all lines have accounting rules and rule start dates. |
1393 +---------------------------------------------------------------------*/
1394
1395 IF PG_DEBUG in ('Y', 'C') THEN
1396 arp_util.debug( 'check that all lines have rule information if rules are '||
1397 'being used');
1398 END IF;
1399
1400 IF ( l_trx_rec.invoicing_rule_id IS NOT NULL )
1401 THEN
1402
1403 /* Its a Release 9 Invoice i.e l_rule_flag ='N', don't do checking * /
1404
1405 /* Modified For Bug 461391 */
1406 SELECT decode( max(d.customer_trx_id),
1407 null, 'N',
1408 'Y')
1409 INTO l_rule_flag
1410 FROM ra_customer_trx trx,
1411 ra_cust_trx_line_gl_dist d
1412 WHERE trx.customer_trx_id = l_trx_rec.customer_trx_id
1413 and trx.previous_customer_trx_id = d.customer_trx_id
1414 and d.account_class in ('UNEARN', 'UNBILL');
1415 IF ( l_rule_flag ='Y')
1416 THEN
1417
1418
1419 BEGIN
1420 FOR l_error_rec IN rule_check LOOP
1421
1422 IF PG_DEBUG in ('Y', 'C') THEN
1423 arp_util.debug( 'ERROR: the rule information is invalid ' ||
1424 ' for line ' ||
1425 TO_CHAR(l_error_rec.line_number));
1426 END IF;
1427
1428 arp_trx_validate.add_to_error_list(
1429 p_error_mode,
1430 l_error_count,
1431 l_trx_rec.customer_trx_id,
1432 l_trx_rec.trx_number,
1433 l_error_rec.line_number,
1434 NULL, -- other_line_number
1435 'AR_TW_LINE_RULE_INCOMPLETE',
1436 NULL, -- p_error_location,
1437 'LINE_NUMBER',
1438 l_error_rec.line_number,
1439 NULL,
1440 NULL
1441 );
1442
1443 END LOOP;
1444
1445 EXCEPTION
1446 WHEN NO_DATA_FOUND THEN NULL;
1447 WHEN OTHERS
1448 THEN RAISE;
1449 END;
1450
1451 END IF;
1452 END IF;
1453
1454 /*----------------------------------------------+
1455 | Check the creation sign of the transaction |
1456 +----------------------------------------------*/
1457
1458 IF PG_DEBUG in ('Y', 'C') THEN
1459 arp_util.debug( 'check creation sign');
1460 END IF;
1461
1462 arp_non_db_pkg.check_creation_sign(
1463 l_creation_sign,
1464 l_trx_amount,
1465 NULL,
1466 l_error_message
1467 );
1468 IF ( l_error_message IS NOT NULL )
1469 THEN
1470
1471 IF PG_DEBUG in ('Y', 'C') THEN
1472 arp_util.debug( 'ERROR: the transaction violate the creation sign');
1473 END IF;
1474
1475 arp_trx_validate.add_to_error_list(
1476 p_error_mode,
1477 l_error_count,
1478 l_trx_rec.customer_trx_id,
1479 l_trx_rec.trx_number,
1480 NULL, -- line_number
1481 NULL, -- other_line_number
1482 l_error_message,
1483 'TGW_HEADER.CTT_TYPE_NAME',
1484 NULL,
1485 NULL,
1486 NULL,
1487 NULL
1488 );
1489
1490 END IF;
1491
1492
1493
1494 /*---------------------+
1495 | Check the GL Date |
1496 +---------------------*/
1497
1498 IF PG_DEBUG in ('Y', 'C') THEN
1499 arp_util.debug( 'check the GL date');
1500 END IF;
1501
1502 /*------------------------------------------------------+
1503 | Verify that the GL Date is in an Opened, Future or |
1504 | Never Opened (Arrears only) Period. |
1505 +------------------------------------------------------*/
1506
1507 l_result_flag := arp_util.validate_and_default_gl_date(
1508 l_trx_gl_date,
1509 NULL,
1510 NULL,
1511 NULL,
1512 null,
1513 l_trx_gl_date,
1514 null,
1515 null,
1516 null,
1517 TO_CHAR(l_trx_rec.invoicing_rule_id),
1518 pg_set_of_books_id,
1519 222,
1520 l_default_gl_date,
1521 l_dummy,
1522 l_dummy);
1523
1524 IF ( l_trx_gl_date <> l_default_gl_date )
1525 THEN
1526 IF PG_DEBUG in ('Y', 'C') THEN
1527 arp_util.debug( 'ERROR: the GL date is invalid');
1528 END IF;
1529
1530 arp_trx_validate.add_to_error_list(
1531 p_error_mode,
1532 l_error_count,
1533 l_trx_rec.customer_trx_id,
1534 l_trx_rec.trx_number,
1535 NULL,
1536 NULL, -- other_line_number
1537 'AR_INVALID_APP_GL_DATE',
1538 'TGW_HEADER.GD_GL_DATE',
1539 'GL_DATE',
1540 l_trx_gl_date,
1541 NULL,
1542 NULL
1543 );
1544
1545 END IF;
1546
1547
1548
1549 /*==================================+
1550 | Credit Memo Validations Follow |
1551 +==================================*/
1552
1553 IF ( l_trx_rec.previous_customer_trx_id IS NOT NULL )
1554 THEN
1555
1556 IF PG_DEBUG in ('Y', 'C') THEN
1557 arp_util.debug( 'check credit memos against specific transactions');
1558 arp_util.debug( 'get credit memo information');
1559 END IF;
1560
1561 arp_ct_pkg.fetch_p(l_prev_trx_rec,
1562 l_trx_rec.previous_customer_trx_id);
1563
1564 SELECT lgd_trx.gl_date
1565 INTO l_prev_gl_date
1566 FROM ra_cust_trx_line_gl_dist lgd_trx
1567 WHERE lgd_trx.customer_trx_id = l_trx_rec.previous_customer_trx_id
1568 AND lgd_trx.latest_rec_flag = 'Y'
1569 AND lgd_trx.account_class = 'REC';
1570
1571 SELECT allow_overapplication_flag,
1572 natural_application_only_flag,
1573 accounting_affect_flag,
1574 type
1575 INTO l_allow_overapplication_flag,
1576 l_natural_app_only_flag,
1577 l_open_receivables_flag,
1578 l_credited_class
1579 FROM ra_cust_trx_types
1580 WHERE cust_trx_type_id = l_prev_trx_rec.cust_trx_type_id;
1581
1582
1583 /*--------------------------------------+
1584 | Get the amounts of the credit memo |
1585 +--------------------------------------*/
1586
1587 SELECT SUM(
1588 DECODE( ctl.line_type,
1589 'LINE', ctl.extended_amount,
1590 'CHARGES', ctl.extended_amount,
1591 0 )
1592 ),
1593 SUM(
1594 DECODE( ctl.line_type,
1595 'TAX', ctl.extended_amount,
1596 0 )
1597 ),
1598 SUM(
1599 DECODE( ctl.line_type,
1600 'FREIGHT', ctl.extended_amount,
1601 0 )
1602 )
1603 INTO l_line_amount,
1604 l_tax_amount,
1605 l_freight_amount
1606 FROM ra_customer_trx_lines ctl
1607 WHERE customer_trx_id = l_trx_rec.customer_trx_id;
1608
1609
1610 /*-------------------------------------------------+
1611 | Get the balances for the credited transaction |
1612 +-------------------------------------------------*/
1613
1614 arp_trx_util.get_summary_trx_balances( l_trx_rec.previous_customer_trx_id,
1615 l_open_receivables_flag,
1616 l_prev_line_original,
1617 l_prev_line_remaining,
1618 l_prev_tax_original,
1619 l_prev_tax_remaining,
1620 l_prev_freight_original,
1621 l_prev_freight_remaining,
1622 l_prev_charges_original,
1623 l_prev_charges_remaining,
1624 l_prev_total_original,
1625 l_prev_total_remaining);
1626
1627 IF PG_DEBUG in ('Y', 'C') THEN
1628 arp_util.debug( 'previous_customer_trx_id : '||l_trx_rec.previous_customer_trx_id);
1629 arp_util.debug( 'l_line_amount : '||l_line_amount);
1630 arp_util.debug( 'l_tax_amount : '||l_tax_amount);
1631 arp_util.debug( 'l_freight_amount : '||l_freight_amount);
1632 arp_util.debug( 'l_open_receivables_flag : '||l_open_receivables_flag);
1633 arp_util.debug( 'l_prev_line_original : '||l_prev_line_original);
1634 arp_util.debug( 'l_prev_line_remaining : '||l_prev_line_remaining);
1635 arp_util.debug( 'l_prev_tax_original : '||l_prev_tax_original);
1636 arp_util.debug( 'l_prev_tax_remaining : '||l_prev_tax_remaining);
1637 arp_util.debug( 'l_prev_freight_original : '||l_prev_freight_original);
1638 arp_util.debug( 'l_prev_freight_remaining : '||l_prev_freight_remaining);
1639 arp_util.debug( 'l_prev_total_original : '||l_prev_total_original);
1640 arp_util.debug( 'l_prev_total_remaining : '||l_prev_total_remaining);
1641 arp_util.debug( 'l_open_receivables_flag : '||l_open_receivables_flag);
1642 END IF;
1643
1644 /* Bug 882789: Get commitment adjustment amount for the credited
1645 transaction. This amount should be added to l_prev_total_remaining
1646 when checking natural application since the commitment adjustment
1647 will be reversed when we complete the credit memo. Otherwise,
1648 natural application checking will fail since the credit amount
1649 is more than the amount remaining for the credited transaction */
1650
1651 /* Bug 2534132: Get Line,tax and freight buckets of the Commitment Adjustment
1652 and add to the line_remaining, tax_remaining and freight_remaining while
1653 checking natural application since the commitment adjustment will be reversed
1654 when we complete the credit memo. */
1655
1656 select nvl(sum(amount),0),nvl(sum(line_adjusted),0),nvl(sum(tax_adjusted),0),nvl(sum(freight_adjusted),0)
1657 into l_commit_adj_amount,l_commit_line_amount,l_commit_tax_amount,l_commit_frt_amount
1658 from ar_adjustments
1659 where customer_trx_id = l_trx_rec.previous_customer_trx_id
1660 and receivables_trx_id = -1;
1661
1662 IF PG_DEBUG in ('Y', 'C') THEN
1663 arp_util.debug( 'l_commit_adj_amount : ' || to_char(l_commit_adj_amount));
1664 END IF;
1665
1666 /*----------------------------------------------------+
1667 | For credit memos against specific transactions, |
1668 | check for illegal overapplications. |
1669 +----------------------------------------------------*/
1670
1671 /*----------------------------------------+
1672 | Check the transaction's total amount |
1673 +----------------------------------------*/
1674
1675 IF PG_DEBUG in ('Y', 'C') THEN
1676 arp_util.debug( 'check for total overapplication');
1677 END IF;
1678
1679 /* Bug 882789: minus l_commit_adj_amount from l_prev_total_remaining */
1680 arp_non_db_pkg.check_natural_application(
1681 l_creation_sign,
1682 l_allow_overapplication_flag,
1683 l_natural_app_only_flag,
1684 '+', -- p_sign_of_ps
1685 'Y', -- p_chk_overapp_if_zero
1686 l_trx_amount,
1687 0, -- p_discount_taken
1688 l_prev_total_remaining -
1689 l_commit_adj_amount,
1690 l_prev_total_original,
1691 NULL, -- event
1692 l_error_message
1693 );
1694
1695 IF ( l_error_message IS NOT NULL )
1696 THEN
1697
1698 IF PG_DEBUG in ('Y', 'C') THEN
1699 arp_util.debug( 'ERROR: overapplication of the total amount');
1700 END IF;
1701
1702 arp_trx_validate.add_to_error_list(
1703 p_error_mode,
1704 l_error_count,
1705 l_trx_rec.customer_trx_id,
1706 l_trx_rec.trx_number,
1707 NULL, -- line_number
1708 NULL, -- other_line_number
1709 l_error_message,
1710 NULL,
1711 NULL,
1712 NULL,
1713 NULL,
1714 NULL
1715 );
1716
1717 END IF;
1718
1719
1720 /*---------------------------------------+
1721 | Check the transaction's line amount |
1722 +---------------------------------------*/
1723
1724 IF PG_DEBUG in ('Y', 'C') THEN
1725 arp_util.debug( 'check for line overapplication');
1726 END IF;
1727
1728 /* Bug 882789: minus l_commit_adj_amount from l_prev_line_remaining */
1729 arp_non_db_pkg.check_natural_application(
1730 l_creation_sign,
1731 l_allow_overapplication_flag,
1732 l_natural_app_only_flag,
1733 '+', -- p_sign_of_ps
1734 'Y', -- p_chk_overapp_if_zero
1735 l_line_amount,
1736 0, -- p_discount_taken
1737 l_prev_line_remaining -
1738 l_commit_line_amount, /* Bug2534132*/
1739 l_prev_line_original,
1740 NULL, -- event
1741 l_error_message
1742 );
1743
1744 IF ( l_error_message IS NOT NULL )
1745 THEN
1746
1747 IF PG_DEBUG in ('Y', 'C') THEN
1748 arp_util.debug( 'ERROR: overapplication of the line amount');
1749 END IF;
1750
1751 arp_trx_validate.add_to_error_list(
1752 p_error_mode,
1753 l_error_count,
1754 l_trx_rec.customer_trx_id,
1755 l_trx_rec.trx_number,
1756 NULL, -- line_number
1757 NULL, -- other_line_number
1758 l_error_message,
1759 NULL,
1760 NULL,
1761 NULL,
1762 NULL,
1763 NULL
1764 );
1765
1766 END IF;
1767
1768 /*--------------------------------------+
1769 | Check the transaction's tax amount |
1770 +--------------------------------------*/
1771
1772 IF PG_DEBUG in ('Y', 'C') THEN
1773 arp_util.debug( 'check for tax overapplication');
1774 END IF;
1775
1776 arp_non_db_pkg.check_natural_application(
1777 l_creation_sign,
1778 l_allow_overapplication_flag,
1779 l_natural_app_only_flag,
1780 '+', -- p_sign_of_ps
1781 'Y', -- p_chk_overapp_if_zero
1782 l_tax_amount,
1783 0, -- p_discount_taken
1784 l_prev_tax_remaining -
1785 l_commit_tax_amount,/*Bug2534132*/
1786 l_prev_tax_original,
1787 NULL, -- event
1788 l_error_message
1789 );
1790
1791 IF ( l_error_message IS NOT NULL )
1792 THEN
1793
1794 IF PG_DEBUG in ('Y', 'C') THEN
1795 arp_util.debug( 'ERROR: overapplication of the tax amount');
1796 END IF;
1797
1798 arp_trx_validate.add_to_error_list(
1799 p_error_mode,
1800 l_error_count,
1801 l_trx_rec.customer_trx_id,
1802 l_trx_rec.trx_number,
1803 NULL, -- line_number
1804 NULL, -- other_line_number
1805 l_error_message,
1806 NULL,
1807 NULL,
1808 NULL,
1809 NULL,
1810 NULL
1811 );
1812
1813 END IF;
1814
1815 /*------------------------------------------+
1816 | Check the transaction's freight amount |
1817 +------------------------------------------*/
1818
1819 IF PG_DEBUG in ('Y', 'C') THEN
1820 arp_util.debug( 'check for freight overapplication');
1821 END IF;
1822
1823 arp_non_db_pkg.check_natural_application(
1824 l_creation_sign,
1825 l_allow_overapplication_flag,
1826 l_natural_app_only_flag,
1827 '+', -- p_sign_of_ps
1828 'Y', -- p_chk_overapp_if_zero
1829 l_freight_amount,
1830 0, -- p_discount_taken
1831 l_prev_freight_remaining -
1832 l_commit_frt_amount, /*Bug2534132*/
1833 l_prev_freight_original,
1834 NULL, -- event
1835 l_error_message
1836 );
1837
1838 IF ( l_error_message IS NOT NULL )
1839 THEN
1840
1841 IF PG_DEBUG in ('Y', 'C') THEN
1842 arp_util.debug( 'ERROR: overapplication of the freight amount');
1843 END IF;
1844
1845 arp_trx_validate.add_to_error_list(
1846 p_error_mode,
1847 l_error_count,
1848 l_trx_rec.customer_trx_id,
1849 l_trx_rec.trx_number,
1850 NULL, -- line_number
1851 NULL, -- other_line_number
1852 l_error_message,
1853 NULL,
1854 NULL,
1855 NULL,
1856 NULL,
1857 NULL
1858 );
1859
1860 END IF;
1861
1862 /*---------------------------------------------------------------+
1863 | Insure that the credit memo does not overapply the Deposit |
1864 | that it is crediting. |
1865 +---------------------------------------------------------------*/
1866
1867 IF PG_DEBUG in ('Y', 'C') THEN
1868 arp_util.debug( 'check for commitment balance overapplication');
1869 END IF;
1870
1871 IF ( l_credited_class = 'DEP' )
1872 THEN
1873
1874 /*---------------------------------------------------------------+
1875 | Get the so_source_code and so_installed_flag values |
1876 | from the passed in parameter, the cached value ffrom the DB. |
1877 +---------------------------------------------------------------*/
1878
1879 IF (p_so_source_code IS NOT NULL )
1880 THEN
1881 pg_so_source_code := p_so_source_code;
1882 l_so_source_code := pg_so_source_code;
1883 ELSE
1884 IF (pg_so_source_code IS NOT NULL)
1885 THEN
1886 l_so_source_code := pg_so_source_code;
1887 ELSE
1888 -- OE/OM change
1889 -- fnd_profile.get( 'SO_SOURCE_CODE', l_so_source_code );
1890 oe_profile.get( 'SO_SOURCE_CODE', l_so_source_code );
1891 pg_so_source_code := l_so_source_code;
1892 END IF;
1893 END IF;
1894
1895 IF (p_so_installed_flag IS NOT NULL )
1896 THEN
1897 pg_so_installed_flag := p_so_installed_flag;
1898 l_so_installed_flag := pg_so_installed_flag;
1899 ELSE
1900 IF (pg_so_installed_flag IS NOT NULL)
1901 THEN
1902 l_so_installed_flag := pg_so_installed_flag;
1903 ELSE
1904 l_so_installed_flag := 'N';
1905 l_result_flag := fnd_installation.get_app_info('OE',
1906 l_so_installed_flag,
1907 l_dummy,
1908 l_dummy);
1909
1910 pg_so_installed_flag := l_so_installed_flag;
1911 END IF;
1912 END IF;
1913
1914 /* Bug 3249432. Check for commitment overapplication only if trx is incomplete. */
1915
1916 IF l_trx_rec.complete_flag = 'N'
1917 AND ( NOT arp_trx_val.check_commitment_overapp(
1918 l_trx_rec.previous_customer_trx_id,
1919 l_credited_class,
1920 l_commit_amount,
1921 ABS(l_trx_amount),
1922 l_so_source_code,
1923 p_so_installed_flag,
1924 l_commitment_balance) )
1925 THEN
1926 IF PG_DEBUG in ('Y', 'C') THEN
1927 arp_util.debug(
1928 'ERROR: overapplication of the commitment balance');
1929 END IF;
1930
1931 arp_trx_validate.add_to_error_list(
1932 p_error_mode,
1933 l_error_count,
1934 l_trx_rec.customer_trx_id,
1935 l_trx_rec.trx_number,
1936 NULL,
1937 NULL, -- other_line_number
1938 'AR_TW_CM_COMMIT_BAL_OVERAPP',
1939 NULL,
1940 'COMMITMENT_BALANCE',
1941 TO_CHAR(l_commitment_balance),
1942 NULL,
1943 NULL
1944 );
1945 END IF;
1946
1947 END IF;
1948
1949 /*-------------------------------------------------------+
1950 | The GL Date must be >= the credited transaction's |
1951 | GL Date. |
1952 +-------------------------------------------------------*/
1953
1954 IF PG_DEBUG in ('Y', 'C') THEN
1955 arp_util.debug( 'check the GL date against the credited transaction''s');
1956 END IF;
1957
1958 IF ( l_trx_gl_date < l_prev_gl_date )
1959 THEN
1960
1961 IF PG_DEBUG in ('Y', 'C') THEN
1962 arp_util.debug(
1963 'ERROR: GL date is before the credited transaction''s');
1964 END IF;
1965
1966 arp_trx_validate.add_to_error_list(
1967 p_error_mode,
1968 l_error_count,
1969 l_trx_rec.customer_trx_id,
1970 l_trx_rec.trx_number,
1971 NULL, -- line number
1972 NULL, -- other_line_number
1973 'AR_TW_GL_DATE_BEFORE_INV_GL_DT',
1974 'TGW_HEADER.GD_GL_DATE',
1975 'GL_DATE',
1976 TO_CHAR(l_prev_gl_date, 'DD-MON-YYYY'),
1977 NULL,
1978 NULL
1979 );
1980
1981 END IF;
1982
1983 /*-------------------------------------------------------------------------+
1984 | For credit memos against specific transactions, |
1985 | check for later credit memos. |
1986 | |
1987 | If any other credit memos against the same invoice have been completed |
1988 | between the time when this credit memo was created and when it is made |
1989 | complete, the accounting for this CM will be wrong. For example, if the|
1990 | Credit Method For Split Term Invoices is LIFO, then the same periods |
1991 | could be credited more than once. In this case, the accounting for the |
1992 | credit memo must be redone before the CM can be made complete. |
1993 +-------------------------------------------------------------------------*/
1994
1995 IF PG_DEBUG in ('Y', 'C') THEN
1996 arp_util.debug( 'check for later credit memos');
1997 END IF;
1998 SELECT MAX( other_ct.customer_trx_id )
1999 INTO l_result
2000 FROM ra_customer_trx other_ct,
2001 ra_customer_trx this_ct
2002 WHERE this_ct.customer_trx_id = p_customer_trx_id
2003 AND other_ct.previous_customer_trx_id =
2004 this_ct.previous_customer_trx_id
2005 AND other_ct.customer_trx_id <> this_ct.customer_trx_id
2006 /*3606541*/
2007 AND other_ct.creation_date > this_ct.creation_date
2008 AND NVL(other_ct.complete_flag,'N')='Y';
2009
2010 IF ( l_result IS NOT NULL )
2011 THEN
2012
2013 IF PG_DEBUG in ('Y', 'C') THEN
2014 arp_util.debug( 'ERROR: later credit memos exist');
2015 END IF;
2016
2017 arp_trx_validate.add_to_error_list(
2018 p_error_mode,
2019 l_error_count,
2020 l_trx_rec.customer_trx_id,
2021 l_trx_rec.trx_number,
2022 NULL,
2023 NULL, -- other_line_number
2024 'AR_TW_STALE_CM',
2025 NULL,
2026 NULL,
2027 NULL,
2028 NULL,
2029 NULL
2030 );
2031
2032 END IF;
2033
2034
2035
2036 END IF; -- credit memo against specific transaction case
2037
2038 /* Bug 2164863 - p_check_tax_acct = 'Y' condition */
2039 ELSIF (p_check_tax_acct = 'Y')
2040 THEN
2041
2042 l_dummy_flag := check_tax_and_accounting(
2043 p_error_mode,
2044 p_customer_trx_id,
2045 l_trx_rec.previous_customer_trx_id,
2046 l_trx_rec.trx_number,
2047 l_class,
2048 l_tax_calculation_flag,
2049 l_trx_rec.invoicing_rule_id,
2050 l_error_count,
2051 l_dummy_number,
2052 l_dummy_number);
2053
2054 END IF;
2055
2056 --Bug Fix: 3261620 Begin#3
2057 /*-------------------------------------------------------------------------+
2058 |Bug Fix: 3261620 |
2059 | Check whether the GL Accounts of the current transaction are either|
2060 |invalid or end dated. |
2061 +-------------------------------------------------------------------------*/
2062 IF PG_DEBUG in ('Y', 'C') THEN
2063 arp_util.debug( 'Check for invalid GL Accounts');
2064 END IF;
2065 /* Bug fix 4398445
2066 Check if the transaction is revenue recognized */
2067
2068 select decode(account_set_flag,'Y','N','N','Y','Y')
2069 into l_revrec_complete
2070 from ra_cust_trx_line_gl_dist
2071 where customer_trx_id = p_customer_trx_id
2072 and account_class = 'REC'
2073 and latest_rec_flag = 'Y';
2074
2075 IF PG_DEBUG in ('Y', 'C') THEN
2076 arp_util.debug('do_completion_checking: ' || 'Revenue Recognition Complete Flag : '||l_revrec_complete);
2077 END IF;
2078 /*End bug5444418*/
2079
2080 OPEN gl_account_ccid_cur;
2081 LOOP
2082 FETCH gl_account_ccid_cur INTO l_gl_account_ccid,l_dist_gl_date,
2083 l_account_class, l_account_set_flag;
2084 EXIT WHEN gl_account_ccid_cur%NOTFOUND;
2085 /* Bug fix 5444418
2086 Do not check the validity of the CCID if it is derived from the Invoice */
2087 IF NOT (l_trx_rec.invoicing_rule_id IS NOT NULL AND
2088 l_account_class in ('REV','UNEARN','UNBILL') AND
2089 l_account_set_flag = 'N' AND
2090 l_revrec_complete = 'N') THEN
2091 IF PG_DEBUG in ('Y', 'C') THEN
2092 arp_util.debug('do_completion_checking: '||'Checking CCID '||l_gl_account_ccid);
2093 END IF;
2094 IF (
2095 -- If the GL Account is excluded using a Security Rule
2096 -- 3567612 : pass ARP_GLOBAL.chart_of_accounts_id instead of 101
2097 ( NOT fnd_flex_keyval.validate_ccid ( appl_short_name => 'SQLGL',
2098 key_flex_code => 'GL#',
2099 structure_number => ARP_GLOBAL.chart_of_accounts_id,
2100 combination_id => l_gl_account_ccid,
2101 security => 'ENFORCE'
2102 )
2103 )
2104 OR
2105 -- If the GL Account is End Dated
2106 (fnd_flex_keyval.start_date > l_dist_gl_date)
2107 OR
2108 (fnd_flex_keyval.end_date < l_dist_gl_date)
2109 /*3679531*/
2110 OR (fnd_flex_keyval.enabled_flag=FALSE)
2111 )
2112 THEN
2113
2114 IF PG_DEBUG in ('Y', 'C') THEN
2115 arp_util.debug(
2116 'Error: Atleast one of the GL Accounts is either invalid or end dated');
2117 END IF;
2118 arp_trx_validate.add_to_error_list(
2119 p_error_mode,
2120 l_error_count,
2121 l_trx_rec.customer_trx_id,
2122 l_trx_rec.trx_number,
2123 NULL, -- line_number
2124 NULL, -- other_line_number
2125 'AR_INVALID_GL_ACCOUNT',--Message Name
2126 NULL, -- p_error_location,
2127 NULL, -- token name 1
2128 NULL, -- token 1
2129 NULL, -- token name 2
2130 NULL -- token 2
2131 );
2132 END IF;
2133 END IF;
2134 END LOOP;
2135 CLOSE gl_account_ccid_cur;
2136
2137 --Bug Fix: 3261620 End#3
2138
2139 p_error_count := l_error_count;
2140
2141 IF PG_DEBUG in ('Y', 'C') THEN
2142 arp_util.debug( 'completion error count: ' || to_char(l_error_count));
2143 arp_util.debug('arp_trx_completion_chk.do_completion_checking()-');
2144 END IF;
2145
2146 EXCEPTION
2147 WHEN OTHERS THEN
2148 IF PG_DEBUG in ('Y', 'C') THEN
2149 arp_util.debug(
2150 'EXCEPTION: arp_trx_completion_chk.do_completion_checking()');
2151 END IF;
2152 RAISE;
2153
2154 END do_completion_checking;
2155
2156 /* Bug 3185358 */
2157 PROCEDURE dm_reversal_amount_chk(
2158 p_customer_trx_id IN
2159 ra_customer_trx.customer_trx_id%type,
2160 p_reversed_cash_receipt_id IN
2161 ra_customer_trx.reversed_cash_receipt_id%type,
2162 p_status OUT NOCOPY VARCHAR2) IS
2163 l_original_amount ra_customer_trx_lines.gross_extended_amount%type;
2164 l_dm_amount ra_customer_trx_lines.gross_extended_amount%type;
2165 line_amount ra_customer_trx_lines.gross_extended_amount%type;
2166 tax_amount ra_customer_trx_lines.gross_extended_amount%type;
2167 frt_amount ra_customer_trx_lines.gross_extended_amount%type;
2168 BEGIN
2169 /* step 1: Fetch the original receipt amount from ar_cash_receipts
2170 step 2: Fetch total amount from trx (dm)
2171 step 3: compare .
2172 step 4: if dm amount < origianl return "E"
2173 else return "S"
2174 */
2175 /* Fetch Original receipt amount */
2176 l_original_amount:=0;
2177 BEGIN
2178 SELECT amount INTO
2179 l_original_amount
2180 FROM
2181 AR_CASH_RECEIPTS WHERE cash_receipt_id=p_reversed_cash_receipt_id;
2182 EXCEPTION
2183 WHEN OTHERS THEN
2184 l_original_amount:=0;
2185 END;
2186
2187 /* Fetch Amount due original for debit memo */
2188 l_dm_amount:=0;
2189 line_amount:=0;
2190 tax_amount:=0;
2191 frt_amount:=0;
2192
2193 SELECT sum(decode(ctl.line_type, 'LINE', ctl.extended_amount,
2194 'CB', ctl.extended_amount, 0)),
2195 sum(decode(ctl.line_type, 'TAX', ctl.extended_amount, 0)),
2196 sum(decode(ctl.line_type, 'FREIGHT', ctl.extended_amount, 0))
2197 INTO
2198 line_amount,
2199 tax_amount,
2200 frt_amount
2201 FROM
2202 ra_customer_trx_lines ctl
2203 WHERE ctl.customer_trx_id = p_customer_trx_id;
2204
2205 l_dm_amount:=nvl(line_amount,0)+nvl(tax_amount,0)+nvl(frt_amount,0);
2206
2207 /* Debit memo amount < original amount Raise error */
2208
2209 IF l_dm_amount < l_original_amount THEN
2210 p_status:='E';
2211 ELSE
2212 p_status:='S';
2213 END IF;
2214 END dm_reversal_amount_chk;
2215
2216
2217 /*---------------------------------------------+
2218 | Package initialization section. |
2219 +---------------------------------------------*/
2220 PROCEDURE init IS
2221 BEGIN
2222
2223 pg_base_curr_code := arp_global.functional_currency;
2224 pg_salesrep_required_flag :=
2225 arp_trx_global.system_info.system_parameters.salesrep_required_flag;
2226 pg_set_of_books_id :=
2227 arp_trx_global.system_info.system_parameters.set_of_books_id;
2228 END init;
2229
2230 BEGIN
2231 init;
2232 END ARP_TRX_COMPLETE_CHK;