[Home] [Help]
PACKAGE BODY: APPS.JAI_AR_VALIDATE_DATA_PKG
Source
1 PACKAGE BODY jai_ar_validate_data_pkg
2 /* $Header: jai_ar_val_data.plb 120.2.12010000.2 2008/11/06 11:55:22 csahoo ship $ */
3 AS
4
5 /*
6 CREATED BY : Bgowrava
7 CREATED DATE : 08-JUN-2007
8 BUG : 5484865
9 PURPOSE : It is a datafix to fix data corruption issues in AR base tables.
10
11 */
12 /* ------------------------------------------------------------------------------------------------------------------------
13 CHANGE HISTORY:
14 S.No DATE Author AND Details
15 ---------------------------------------------------------------------------------------------------------------------------
16 1 06-NOV-2008 CSahoo for bug#7445602, File Version 120.0.12000000.3
17 Issue: TAXES NOT FLOWING TO CREDIT MEMO WHEN INVOICNG RULE IS APPLIED FOR TRANSACTION
18 Fix: Modified the code in pre_validation. Added a cursor cur_chk_revrec_run_cm
19 --------------------------------------------------------------------------------------------------------------------------*/
20
21 PROCEDURE populate_error_table (p_error_table OUT NOCOPY jai_ar_validate_data_pkg.t_error_table,
22 p_process_status OUT NOCOPY VARCHAR2,
23 p_process_message OUT NOCOPY VARCHAR2)
24 IS
25 BEGIN
26 p_process_status := jai_constants.successful;
27
28 FOR i in 1..13 LOOP
29 p_error_table(i).type_of_error := 'Common';
30 END LOOP;
31
32 p_error_table(1).error_description := 'Difference in tax records in ra_customer_trx_lines_all and ja_in_ra_customer_trx_lines_all';
33 p_error_table(1).enable := 'Y';
34
35 p_error_table(2).error_description := 'Difference in tax records in ra_cust_trx_line_gl_dist_all and JAI_AR_TRX_TAX_LINES';
36 p_error_table(2).enable := 'Y';
37
38 p_error_table(3).error_description := 'ADO <> Amount in REC of ra_cust_trx_line_gl_dist_all';
39 p_error_table(3).enable := 'Y';
40
41 p_error_table(4).error_description := 'TO <> Amount in TAX of ra_cust_trx_line_gl_dist_all';
42 p_error_table(4).enable := 'Y';
43
44 p_error_table(5).error_description := 'FO <> Amount in FREIGHT of ra_cust_trx_line_gl_dist_all';
45 p_error_table(5).enable := 'Y';
46
47 p_error_table(6).error_description := 'ADO <> ALIO + TO + FO';
48
49 p_error_table(7).error_description := 'ADR <> ALIR + TR + FR';
50
51 p_error_table(8).error_description := 'ADR * exchange_rate <> AADR';
52
53 p_error_table(9).error_description := 'Status = CL and (AADR <> 0 OR ADR <> 0)';
54 p_error_table(9).enable := 'Y';
55
56 p_error_table(10).error_description := 'Status = OP and gl_date_closed <> 31-DEC-4712';
57 p_error_table(10).enable := 'Y';
58
59 p_error_table(11).error_description := 'Tax/Freight in GL Dist <> tax/freight in ra_customer_trx_lines_all';
60 p_error_table(11).enable := 'Y';
61
62 p_error_table(12).error_description := 'Sum of taxes for a line <> Tax amount of the line';
63 p_error_table(12).enable := 'Y';
64
65 p_error_table(13).error_description := 'Sum of taxes for an invoice <> Tax amount of the invoice';
66 p_error_table(13).enable := 'Y';
67
68 FOR i in 21..26 LOOP
69 p_error_table(i).type_of_error := 'CM';
70 END LOOP;
71
72 p_error_table(21).error_description := 'ADR, AADR, TR and FR should be 0 for CM';
73 p_error_table(21).enable := 'Y';
74
75 p_error_table(22).error_description := 'ADO of CM should be equal to amount applied of CM';
76 p_error_table(22).enable := 'Y';
77
78 p_error_table(23).error_description := 'ADO <> SUM(LA + TA + FA) for ar_receivable_applications_all';
79 p_error_table(23).enable := 'Y';
80
81 p_error_table(24).error_description := 'Amount_applied <> LA + TA + FA for ar_receivable_applications_all';
82 p_error_table(24).enable := 'Y';
83
84 p_error_table(25).error_description := 'AAAF <> AA * exchange_rate(CM) for ar_receivable_applications_all';
85 p_error_table(25).enable := 'Y';
86
87 p_error_table(26).error_description := 'AAAT <> AA * exchange_rate(INV) for ar_receivable_applications_all';
88 p_error_table(26).enable := 'Y';
89
90 FOR i in 41..42 LOOP
91 p_error_table(i).type_of_error := 'Invoice';
92 END LOOP;
93
94 p_error_table(41).error_description := 'ADO <> ADR + SUM(LA + TA + FA) of ar_receivable_applications';
95 p_error_table(42).error_description := 'Amount_credited <> SUM(amount_applied) of ar_receivable_applications';
96
97 EXCEPTION
98 WHEN OTHERS THEN
99 p_process_status := jai_constants.unexpected_error;
100 p_process_message := SUBSTR(SQLERRM,1,300);
101 END populate_error_table;
102
103
104 PROCEDURE display_error_summary(p_error_table IN jai_ar_validate_data_pkg.t_error_table,
105 p_total_count IN NUMBER,
106 p_filename IN VARCHAR2,
107 p_process_status OUT NOCOPY VARCHAR2,
108 p_process_message OUT NOCOPY VARCHAR2)
109 IS
110 ln_slno NUMBER := 0;
111 BEGIN
112 jai_cmn_utils_pkg.print_log(p_filename, fnd_global.local_chr(10));
113 jai_cmn_utils_pkg.print_log(p_filename, LPAD('=',62,'=')||'Summary'||LPAD('=',62,'='));
114 jai_cmn_utils_pkg.print_log(p_filename, 'Total number of Records processed : '||p_total_count);
115 jai_cmn_utils_pkg.print_log(p_filename, fnd_global.local_chr(10));
116 jai_cmn_utils_pkg.print_log(p_filename, LPAD('No.',3)||' '||RPAD('Error Description',120)||' '||LPAD('Count',6));
117 jai_cmn_utils_pkg.print_log(p_filename, LPAD('-',3,'-')||' '||RPAD('-',120,'-')||' '||LPAD('-',6,'-'));
118
119 For i IN p_error_table.first..p_error_table.last
120 LOOP
121 IF p_error_table.EXISTS(i) THEN
122
123 IF p_error_table(i).error_record_count > 0 THEN
124 ln_slno := ln_slno + 1;
125 jai_cmn_utils_pkg.print_log(p_filename, LPAD(ln_slno,2)||') '||RPAD(p_error_table(i).error_description,120)||' '||LPAD(p_error_table(i).error_record_count,6));
126 END IF;
127
128 END IF;
129
130 END LOOP;
131
132 jai_cmn_utils_pkg.print_log(p_filename, LPAD('=',131,'='));
133
134 END display_error_summary;
135
136 PROCEDURE calc_term_apportion_ratio(p_invoice_type IN ar_payment_schedules_all.class%TYPE,
137 p_term_id IN ar_payment_schedules_all.term_id%TYPE,
138 p_terms_sequence_number IN ar_payment_schedules_all.terms_sequence_number%TYPE,
139 p_apportion_ratio OUT NOCOPY NUMBER,
140 p_first_installment_code OUT NOCOPY ra_terms.first_installment_code%TYPE,
141 p_process_status OUT NOCOPY VARCHAR2,
142 p_process_message OUT NOCOPY VARCHAR2
143 )
144 IS
145
146 CURSOR cur_get_ra_term_ratio
147 IS
148 SELECT rtl.relative_amount/rt.base_amount apportion_ratio,
149 rt.first_installment_code
150 FROM ra_terms rt ,
151 ra_terms_lines rtl
152 WHERE rt.term_id = rtl.term_id
153 AND rtl.term_id = p_term_id
154 AND rtl.sequence_num = p_terms_sequence_number;
155
156 BEGIN
157 p_process_status := jai_constants.successful;
158 p_process_message := NULL;
159
160 /*
161 Process the records only for an invoice not for any other type.
162 */
163 p_apportion_ratio := 1;
164
165 IF p_invoice_type = 'INV' THEN
166 OPEN cur_get_ra_term_ratio ;
167 FETCH cur_get_ra_term_ratio INTO p_apportion_ratio,
168 p_first_installment_code;
169 CLOSE cur_get_ra_term_ratio ;
170
171 IF p_first_installment_code <> 'ALLOCATE' THEN
172 p_apportion_ratio := 1;
173 END IF;
174
175 END IF;
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 p_process_status := jai_constants.unexpected_error;
180 p_process_message := SUBSTR(SQLERRM,1,300);
181 END calc_term_apportion_ratio;
182
183 PROCEDURE rectify_ar_pay_sch(
184 p_customer_trx_id IN ar_payment_schedules_all.customer_trx_id%TYPE,
185 p_gl_rec_amount IN NUMBER DEFAULT NULL,
186 p_gl_tax_amount IN NUMBER DEFAULT NULL,
187 p_gl_freight_amount IN NUMBER DEFAULT NULL,
188 p_datafix_filename IN VARCHAR2,
189 p_process_status OUT NOCOPY VARCHAR2,
190 p_process_message OUT NOCOPY VARCHAR2)
191 IS
192 ln_apportion_ratio NUMBER ;
193 lv_first_installment_code RA_TERMS.FIRST_INSTALLMENT_CODE%TYPE;
194 lv_process_status VARCHAR2(2);
195 lv_process_message VARCHAR2(2000);
196 lv_sql_statement VARCHAR2(4000);
197
198 BEGIN
199 p_process_status := jai_constants.successful;
200 p_process_message := NULL;
201
202 FOR i IN( SELECT *
203 FROM ar_payment_schedules_all
204 WHERE customer_trx_id = p_customer_trx_id
205 ORDER BY payment_schedule_id)
206 LOOP
207
208 ln_apportion_ratio := null;
209 lv_first_installment_code := null;
210 lv_process_status := jai_constants.successful;
211 lv_process_message := null;
212
213 calc_term_apportion_ratio(p_invoice_type => i.class ,
214 p_term_id => i.term_id ,
215 p_terms_sequence_number => i.terms_sequence_number ,
216 p_apportion_ratio => ln_apportion_ratio ,
217 p_first_installment_code => lv_first_installment_code ,
218 p_process_status => lv_process_status ,
219 p_process_message => lv_process_message
220 );
221 IF lv_process_status <> jai_constants.successful THEN
222 p_process_status := lv_process_status;
223 p_process_message := lv_process_message;
224 EXIT;
225 END IF;
226
227 IF p_gl_rec_amount IS NOT NULL THEN
228
229 UPDATE ar_payment_schedules_all
230 SET amount_due_original = p_gl_rec_amount * ln_apportion_ratio,
231 last_update_date = sysdate,
232 last_updated_by = gn_bug_no
233 WHERE customer_trx_id = p_customer_trx_id
234 AND payment_schedule_id = i.payment_schedule_id;
235
236 lv_sql_statement := fnd_global.local_chr(10)||
237 'UPDATE ar_payment_schedules_all
238 SET amount_due_original = '||p_gl_rec_amount||' * '||ln_apportion_ratio||',
239 last_update_date = sysdate,
240 last_updated_by = '||gn_bug_no||'
241 WHERE customer_trx_id = '||p_customer_trx_id||'
242 AND payment_schedule_id = '||i.payment_schedule_id||';';
243
244 jai_cmn_utils_pkg.print_log(p_datafix_filename, lv_sql_statement);
245
246 lv_sql_statement := NULL;
247
248 ELSIF p_gl_tax_amount IS NOT NULL THEN
249
250 UPDATE ar_payment_schedules_all
251 SET tax_original = p_gl_tax_amount * ln_apportion_ratio,
252 last_update_date = sysdate,
253 last_updated_by = gn_bug_no
254 WHERE customer_trx_id = p_customer_trx_id
255 AND payment_schedule_id = i.payment_schedule_id;
256
257 lv_sql_statement := fnd_global.local_chr(10)||
258 'UPDATE ar_payment_schedules_all
259 SET tax_original = '||p_gl_tax_amount||' * '||ln_apportion_ratio||',
260 last_update_date = sysdate,
261 last_updated_by = '||gn_bug_no||'
262 WHERE customer_trx_id = '||p_customer_trx_id||'
266
263 AND payment_schedule_id = '||i.payment_schedule_id||';';
264
265 jai_cmn_utils_pkg.print_log(p_datafix_filename, lv_sql_statement);
267 lv_sql_statement := NULL;
268
269 ELSIF p_gl_freight_amount IS NOT NULL THEN
270
271 UPDATE ar_payment_schedules_all
272 SET freight_original = p_gl_freight_amount * ln_apportion_ratio,
273 last_update_date = sysdate,
274 last_updated_by = gn_bug_no
275 WHERE customer_trx_id = p_customer_trx_id
276 AND payment_schedule_id = i.payment_schedule_id;
277
278 lv_sql_statement := fnd_global.local_chr(10)||
279 'UPDATE ar_payment_schedules_all
280 SET freight_original = '||p_gl_freight_amount||' * '||ln_apportion_ratio||',
281 last_update_date = sysdate,
282 last_updated_by = '||gn_bug_no||'
283 WHERE customer_trx_id = '||p_customer_trx_id||'
284 AND payment_schedule_id = '||i.payment_schedule_id||';';
285
286 jai_cmn_utils_pkg.print_log(p_datafix_filename, lv_sql_statement);
287
288 lv_sql_statement := NULL;
289
290 END IF;
291
292 IF lv_first_installment_code <> 'ALLOCATE' THEN
293 EXIT;
294 END IF;
295
296 END LOOP;
297
298 EXCEPTION
299 WHEN OTHERS THEN
300 p_process_status := jai_constants.unexpected_error;
301 p_process_message := SUBSTR(SQLERRM,1,300);
302 END rectify_ar_pay_sch;
303
304 PROCEDURE rectify_ar_rec_appl(
305 p_customer_trx_id IN ar_payment_schedules_all.customer_trx_id%TYPE,
306 p_previous_trx_id IN ar_payment_schedules_all.customer_trx_id%TYPE,
307 p_arps_ado IN NUMBER DEFAULT NULL,
308 p_arps_to IN NUMBER DEFAULT NULL,
309 p_arps_fo IN NUMBER DEFAULT NULL,
310 p_datafix_filename IN VARCHAR2,
311 p_process_status OUT NOCOPY VARCHAR2,
312 p_process_message OUT NOCOPY VARCHAR2)
313 IS
314 ln_apportion_ratio NUMBER;
315 lv_first_installment_code RA_TERMS.FIRST_INSTALLMENT_CODE%TYPE;
316 lv_process_status VARCHAR2(2);
317 lv_process_message VARCHAR2(2000);
318 lv_sql_statement VARCHAR2(4000);
319
320 BEGIN
321 p_process_status := jai_constants.successful;
322 p_process_message := NULL;
323
324 FOR i IN( SELECT *
325 FROM ar_payment_schedules_all
326 WHERE customer_trx_id = p_previous_trx_id
327 ORDER BY payment_schedule_id)
328 LOOP
329
330 calc_term_apportion_ratio(p_invoice_type => i.class ,
331 p_term_id => i.term_id ,
332 p_terms_sequence_number => i.terms_sequence_number ,
333 p_apportion_ratio => ln_apportion_ratio ,
334 p_first_installment_code => lv_first_installment_code ,
335 p_process_status => lv_process_status ,
336 p_process_message => lv_process_message
337 );
338
339 IF lv_process_status <> jai_constants.successful THEN
340 p_process_status := lv_process_status;
341 p_process_message := lv_process_message;
342 EXIT;
343 END IF;
344
345 IF p_arps_ado IS NOT NULL THEN
346
347 UPDATE ar_receivable_applications_all
348 SET amount_applied = p_arps_ado * ln_apportion_ratio,
349 last_update_date = sysdate,
350 last_updated_by = gn_bug_no
351 WHERE customer_trx_id = p_customer_trx_id -- CM
352 AND applied_customer_trx_id = p_previous_trx_id -- INV
353 AND applied_payment_schedule_id = i.payment_schedule_id -- payment_schedule_id of INV
354 AND display = 'Y'
355 AND status = 'APP'
356 AND application_type = 'CM';
357
358 lv_sql_statement := fnd_global.local_chr(10)||
359 'UPDATE ar_receivable_applications_all
360 SET amount_applied = '||p_arps_ado||' * '||ln_apportion_ratio||',
361 last_update_date = sysdate,
362 last_updated_by = '||gn_bug_no||'
363 WHERE customer_trx_id = '||p_customer_trx_id||'
364 AND applied_customer_trx_id = '||p_previous_trx_id||'
365 AND applied_payment_schedule_id = '||i.payment_schedule_id||'
366 AND display = '||''''||'Y'||''''||'
367 AND status = '||''''||'APP'||''''||'
368 AND application_type = '||''''||'CM'||''''||';';
369
373
370 jai_cmn_utils_pkg.print_log(p_datafix_filename, lv_sql_statement);
371
372 lv_sql_statement := NULL;
374 ELSIF p_arps_to IS NOT NULL THEN
375
376 UPDATE ar_receivable_applications_all
377 SET tax_applied = p_arps_to * ln_apportion_ratio,
378 last_update_date = sysdate,
379 last_updated_by = gn_bug_no
380 WHERE customer_trx_id = p_customer_trx_id -- CM
381 AND applied_customer_trx_id = p_previous_trx_id -- INV
382 AND applied_payment_schedule_id = i.payment_schedule_id -- payment_schedule_id of INV
383 AND display = 'Y'
384 AND status = 'APP'
385 AND application_type = 'CM';
386
387 lv_sql_statement := fnd_global.local_chr(10)||
388 'UPDATE ar_receivable_applications_all
389 SET tax_applied = '||p_arps_to||' * '||ln_apportion_ratio||',
390 last_update_date = sysdate,
391 last_updated_by = '||gn_bug_no||'
392 WHERE customer_trx_id = '||p_customer_trx_id||'
393 AND applied_customer_trx_id = '||p_previous_trx_id||'
394 AND applied_payment_schedule_id = '||i.payment_schedule_id||'
395 AND display = '||''''||'Y'||''''||'
396 AND status = '||''''||'APP'||''''||'
397 AND application_type = '||''''||'CM'||''''||';';
398
399 jai_cmn_utils_pkg.print_log(p_datafix_filename, lv_sql_statement);
400
401 lv_sql_statement := NULL;
402
403 ELSIF p_arps_fo IS NOT NULL THEN
404
405 UPDATE ar_receivable_applications_all
406 SET freight_applied = p_arps_fo * ln_apportion_ratio,
407 last_update_date = sysdate,
408 last_updated_by = gn_bug_no
409 WHERE customer_trx_id = p_customer_trx_id -- CM
410 AND applied_customer_trx_id = p_previous_trx_id -- INV
411 AND applied_payment_schedule_id = i.payment_schedule_id -- payment_schedule_id of INV
412 AND display = 'Y'
413 AND status = 'APP'
414 AND application_type = 'CM';
415
416 lv_sql_statement := fnd_global.local_chr(10)||
417 'UPDATE ar_receivable_applications_all
418 SET freight_applied = '||p_arps_fo||' * '||ln_apportion_ratio||',
419 last_update_date = sysdate,
420 last_updated_by = '||gn_bug_no||'
421 WHERE customer_trx_id = '||p_customer_trx_id||'
422 AND applied_customer_trx_id = '||p_previous_trx_id||'
423 AND applied_payment_schedule_id = '||i.payment_schedule_id||'
424 AND display = '||''''||'Y'||''''||'
425 AND status = '||''''||'APP'||''''||'
426 AND application_type = '||''''||'CM'||''''||';';
427
428 jai_cmn_utils_pkg.print_log(p_datafix_filename, lv_sql_statement);
429
430 lv_sql_statement := NULL;
431 END IF;
432
433 IF lv_first_installment_code <> 'ALLOCATE' THEN
434 EXIT;
435 END IF;
436
437 END LOOP;
438
439 EXCEPTION
440 WHEN OTHERS THEN
441 p_process_status := jai_constants.unexpected_error;
442 p_process_message := SUBSTR(SQLERRM,1,300);
443 END rectify_ar_rec_appl;
444
445
446 PROCEDURE pre_validation( p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
447 p_process_status OUT NOCOPY VARCHAR2,
448 p_process_message OUT NOCOPY VARCHAR2)
449 IS
450 CURSOR cur_chk_non_il_taxes(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
451 IS
452 SELECT 1
453 FROM ra_customer_trx_lines_all rctl,
454 ar_vat_tax_all avtl
455 WHERE rctl.vat_tax_id = avtl.vat_tax_id
456 AND rctl.org_id = avtl.org_id
457 AND rctl.customer_trx_id = cp_customer_trx_id
458 AND avtl.tax_code <> jai_constants.tax_code_localization--'Localization' --Added by Bgowrava for Bug#5484865
459 AND rctl.org_id = rctl.org_id
460 AND rctl.line_type IN ('TAX','FREIGHT') ;
461
462 CURSOR cur_revrec_run(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
463 IS
464 SELECT 1
465 FROM ra_cust_trx_line_gl_dist_all gl_dist,
466 ra_customer_trx_all rctx
467 WHERE rctx.customer_trx_id = gl_dist.customer_trx_id
468 AND rctx.invoicing_rule_id IS NOT NULL
469 AND gl_dist.account_class = 'REC'
470 AND gl_dist.account_set_flag = 'N'
471 AND gl_dist.latest_rec_flag = 'Y'
472 AND gl_dist.customer_trx_id = cp_customer_trx_id;
473
474 --added this cursor for bug#7445602
478 FROM ra_customer_trx_all rcta,
475 CURSOR cur_chk_revrec_run_cm(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
476 IS
477 SELECT count(*)
479 ra_customer_trx_lines_all rctla,
480 ra_cust_trx_types_all rctta
481 WHERE rcta.customer_trx_id = rctla.customer_trx_id
482 AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
483 AND rctta.type = 'CM'
484 AND rctla. previous_customer_trx_id IS NOT NULL
485 AND rcta.customer_trx_id = cp_customer_trx_id;
486
487 CURSOR cur_chk_gl_posting(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
488 IS
489 SELECT 1
490 FROM ra_cust_trx_line_gl_dist_all
491 WHERE customer_trx_id = cp_customer_trx_id
492 AND account_set_flag = 'N'
493 AND posting_control_id <> -3
494 AND rownum = 1;
495
496 ln_exists NUMBER;
497 ln_cnt NUMBER; --added for bug#7445602
498
499 BEGIN
500 p_process_status := jai_constants.successful;
501 p_process_message := NULL;
502
503 OPEN cur_chk_non_il_taxes(p_customer_trx_id);
504 FETCH cur_chk_non_il_taxes INTO ln_exists;
505 CLOSE cur_chk_non_il_taxes;
506
507 IF ln_exists = 1 THEN
508 p_process_status := jai_constants.expected_error;
509 p_process_message := 'Invoice lines have taxes other than localization type of tax. Please delete it and reprocess the invoice';
510 goto EXIT_POINT;
511 END IF;
512
513 OPEN cur_revrec_run(p_customer_trx_id);
514 FETCH cur_revrec_run INTO ln_exists ;
515 CLOSE cur_revrec_run;
516
517 IF ln_exists = 1 THEN
518 --added for bug#7445602,start
519 OPEN cur_chk_revrec_run_cm(p_customer_trx_id);
520 FETCH cur_chk_revrec_run_cm INTO ln_cnt;
521 CLOSE cur_chk_revrec_run_cm;
522 --bug#7445602,end
523
524 IF ln_cnt = 0 THEN --added the IF condition for bug#7445602
525 p_process_status := jai_constants.expected_error;
526 p_process_message := 'Invoice has already been revenue recognised. Taxes related to invoice cannot be processed';
527 goto EXIT_POINT;
528 END IF;
529 END IF;
530
531 ln_exists := 0;
532
533 OPEN cur_chk_gl_posting(p_customer_trx_id);
534 FETCH cur_chk_gl_posting INTO ln_exists ;
535 CLOSE cur_chk_gl_posting;
536
537 IF ln_exists = 1 THEN
538 p_process_status := jai_constants.expected_error;
539 p_process_message := 'Invoice has already been GL posted. Taxes related to this invoice cannot be processed';
540 goto EXIT_POINT;
541 END IF;
542
543 <<EXIT_POINT>>
544 NULL;
545
546 EXCEPTION
547 WHEN OTHERS THEN
548 p_process_status := jai_constants.unexpected_error;
549 p_process_message := 'For Customer trx id - '||p_customer_trx_id||SUBSTR(SQLERRM,1,300);
550 END pre_validation;
551
552 PROCEDURE post_validation(p_start_date IN DATE DEFAULT NULL,
553 p_end_date IN DATE DEFAULT NULL,
554 p_customer_trx_id IN NUMBER DEFAULT NULL,
555 p_validate_first IN VARCHAR2 DEFAULT 'N',
556 p_validate_all IN VARCHAR2 DEFAULT 'N',
557 p_generate_log IN VARCHAR2 DEFAULT 'N',
558 p_generate_detail_log IN VARCHAR2 DEFAULT 'N',
559 p_fix_data IN VARCHAR2 DEFAULT 'N',
560 p_commit IN VARCHAR2 DEFAULT 'N',
561 p_log_filename IN VARCHAR2 DEFAULT NULL,
562 p_debug IN VARCHAR2 DEFAULT 'N',
563 p_process_status OUT NOCOPY VARCHAR2,
564 p_process_message OUT NOCOPY VARCHAR2)
565 IS
566
567 CURSOR cur_get_cust_trx(cp_start_date DATE,
568 cp_end_date DATE,
569 cp_customer_trx_id NUMBER)
570 IS
571 SELECT rcta.customer_trx_id,
572 rcta.previous_customer_trx_id,
573 rcta.set_of_books_id ,
574 rctta.type
575 FROM ra_customer_trx_all rcta ,
576 JAI_AR_TRXS jrcta ,
577 ra_cust_trx_types_all rctta /* added by aiyer to check that only INV and CM type of transactions are picked up */
578 WHERE rcta.customer_trx_id = jrcta.customer_trx_id
579 AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
580 AND nvl(rctta.type,'###') IN ('INV','CM')
581 AND jrcta.customer_trx_id = NVL(cp_customer_trx_id, jrcta.customer_trx_id)
582 AND trunc(rcta.trx_date) BETWEEN NVL(cp_start_date, trunc(rcta.trx_date)) AND NVL(cp_end_date, trunc(rcta.trx_date))
583 AND nvl(rcta.complete_flag,'N') = 'Y'
584 ORDER BY rcta.customer_trx_id;
585
586 --check if revenue recognition program has been run
587 CURSOR cur_revrec_run(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
588 IS
589 SELECT 1
590 FROM ra_cust_trx_line_gl_dist_all gl_dist,
591 ra_customer_trx_all rctx
592 WHERE rctx.customer_trx_id = gl_dist.customer_trx_id
593 AND rctx.invoicing_rule_id IS NOT NULL
594 AND gl_dist.account_class = 'REC'
595 AND gl_dist.account_set_flag = 'N'
596 AND gl_dist.latest_rec_flag = 'Y'
600 CURSOR cur_chk_gl_posting(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
597 AND gl_dist.customer_trx_id = cp_customer_trx_id;
598
599 --Get 1 if record has not been posted else null if posted
601 IS
602 SELECT 1
603 FROM ra_cust_trx_line_gl_dist_all
604 WHERE customer_trx_id = cp_customer_trx_id
605 AND account_set_flag = 'N'
606 AND account_class = 'REC'
607 AND latest_rec_flag = 'Y'
608 AND posting_control_id = -3;
609
610 CURSOR cur_tot_payment_schedule(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
611 IS
612 SELECT NVL(SUM(amount_due_remaining),0) amount_due_remaining,
613 NVL(SUM(amount_due_original),0) amount_due_original,
614 NVL(SUM(tax_original),0) tax_original,
615 NVL(SUM(freight_original),0) freight_original,
616 NVL(SUM(tax_remaining),0) tax_remaining,
617 NVL(SUM(freight_remaining),0) freight_remaining,
618 NVL(SUM(amount_applied),0) amount_applied,
619 NVL(SUM(amount_credited),0) amount_credited,
620 NVL(SUM(amount_line_items_original),0) amount_line_items_original,
621 NVL(SUM(amount_line_items_remaining),0) amount_line_items_remaining,
622 NVL(SUM(acctd_amount_due_remaining),0) acctd_amount_due_remaining,
623 NVL(SUM( NVL(amount_due_remaining,0) * NVL(exchange_rate,1) ),0) acctd_amount_due_remain_calc
624 FROM ar_payment_schedules_all
625 WHERE customer_trx_id = cp_customer_trx_id;
626
627 CURSOR cur_payment_schedules(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
628 IS
629 SELECT status,
630 gl_date_closed,
631 NVL(amount_due_remaining,0) amount_due_remaining,
632 NVL(acctd_amount_due_remaining,0) acctd_amount_due_remaining,
633 payment_schedule_id,
634 exchange_rate
635 FROM ar_payment_schedules_all
636 WHERE customer_trx_id = cp_customer_trx_id;
637
638 CURSOR cur_tot_cust_trx_gl_dist(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
639 cp_account_class ra_cust_trx_line_gl_dist_all.account_class%TYPE)
640 IS
641 SELECT NVL(SUM(amount),0) amount,
642 NVL(SUM(acctd_amount),0) acctd_amount
643 FROM ra_cust_trx_line_gl_dist_all
644 WHERE customer_trx_id = cp_customer_trx_id
645 AND (
646 ( account_class = 'REC'
647 AND latest_rec_flag = 'Y'
648 )
649 OR
650 (account_class <> 'REC')
651 )
652 AND account_class = NVL(cp_account_class, account_class);
653
654 CURSOR cur_il_tax_amount(cp_customer_trx_id JAI_AR_TRX_LINES.customer_trx_id%TYPE)
655 IS
656 SELECT rctl.customer_trx_id,
657 jrcttl.customer_trx_line_id,
658 jrcttl.tax_amount
659 FROM JAI_AR_TRX_TAX_LINES jrcttl,
660 JAI_AR_TRX_LINES jrctl,
661 ra_customer_trx_lines_all rctl
662 WHERE jrcttl.link_to_cust_trx_line_id = jrctl.customer_trx_line_id
663 AND jrcttl.customer_Trx_line_id = rctl.customer_trx_line_id
664 AND nvl(jrcttl.tax_amount,0) <> nvl(rctl.extended_amount,0)
665 AND rctl.customer_trx_id = jrctl.customer_trx_id
666 AND jrctl.customer_trx_id = cp_customer_trx_id
667 AND rctl.line_type IN ('TAX','FREIGHT');
668
669 CURSOR cur_cust_trx_gl_dist_tax (cp_customer_trx_id JAI_AR_TRX_LINES.customer_trx_id%TYPE)
670 IS
671 SELECT gl_dist.customer_trx_id,
672 jrcttl.customer_trx_line_id,
673 jrcttl.tax_amount ,
674 jrcttl.func_tax_amount
675 FROM JAI_AR_TRX_TAX_LINES jrcttl,
676 JAI_AR_TRX_LINES jrctl,
677 ra_cust_trx_line_gl_dist_all gl_dist
678 WHERE jrcttl.link_to_cust_trx_line_id = jrctl.customer_trx_line_id
679 AND jrcttl.customer_Trx_line_id = gl_dist.customer_trx_line_id
680 AND (ROUND(nvl(jrcttl.tax_amount,0)) <> ROUND(nvl(gl_dist.amount,0))
681 AND -- Need to check further if there is a way out in case tax amount in ja tax table itself is wrong ???
682 ROUND(nvl(jrcttl.func_tax_amount,0)) <> ROUND(nvl(gl_dist.acctd_amount,0))
683 )
684 AND gl_dist.customer_trx_id = jrctl.customer_trx_id
685 AND jrctl.customer_trx_id = cp_customer_trx_id
686 AND gl_dist.account_class IN ('TAX','FREIGHT');
687
688
689
690 CURSOR cur_chk_gl_dist_rctl (cp_customer_trx_id JAI_AR_TRX_LINES.customer_trx_id%TYPE)
691 IS
692 SELECT
693 gl_dist.customer_trx_line_id ,
694 gl_dist.amount amount
695 FROM ra_cust_trx_line_gl_dist_all gl_dist,
696 ra_customer_trx_lines_all rctl
697 WHERE gl_dist.customer_trx_id = rctl.customer_trx_id
698 AND gl_dist.customer_trx_line_id = rctl.customer_trx_line_id
699 AND gl_dist.account_class IN ('TAX','FREIGHT')
700 AND rctl.line_type IN ('TAX','FREIGHT')
701 AND ROUND(nvl(gl_dist.amount,0)) <> ROUND(nvl(extended_amount,0))
702 AND gl_dist.customer_trx_id = cp_customer_trx_id ;
703
704
705 CURSOR cur_cm_ar_recv_appl( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
709 SELECT NVL(SUM(line_applied),0) line_applied,
706 cp_exchange_rate ra_customer_trx_all.exchange_rate%TYPE,
707 cp_exchange_rate_prev ra_customer_trx_all.exchange_rate%TYPE)
708 IS
710 NVL(SUM(tax_applied),0) tax_applied,
711 NVL(SUM(freight_applied),0) freight_applied,
712 NVL(SUM(amount_applied),0) amount_applied,
713 NVL(SUM(acctd_amount_applied_from),0) acctd_amount_applied_from,
714 NVL(SUM(acctd_amount_applied_to),0) acctd_amount_applied_to,
715 NVL(SUM( NVL(amount_applied,0) * cp_exchange_rate),0) acctd_amount_applied_from_calc,
716 NVL(SUM( NVL(amount_applied,0) * cp_exchange_rate_prev),0) acctd_amount_applied_to_calc
717 FROM ar_receivable_applications_all
718 WHERE customer_trx_id = cp_customer_trx_id
719 AND application_type = 'CM'
720 AND display = 'Y'
721 AND status = 'APP';
722
723 --Validate that a Invoice has a 'CM' type of receivable application.
724 --This would be used for validating correctness of invoices w.r.t their receivable applications
725 CURSOR cur_chk_cm_exists (cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
726 IS
727 SELECT application_type
728 FROM ar_receivable_applications_all
729 WHERE applied_customer_trx_id = cp_customer_trx_id
730 AND application_type = 'CM'
731 AND display = 'Y'
732 AND status = 'APP';
733
734 CURSOR cur_inv_cash_cm_recv_appl(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
735 IS
736 SELECT NVL(SUM(line_applied),0) line_applied,
737 NVL(SUM(tax_applied),0) tax_applied,
738 NVL(SUM(freight_applied),0) freight_applied,
739 NVL(SUM(amount_applied),0) amount_applied
740 FROM ar_receivable_applications_all
741 WHERE applied_customer_trx_id = cp_customer_trx_id
742 AND application_type IN ('CM' ,'CASH')
743 AND display = 'Y'
744 AND status = 'APP';
745
746 CURSOR cur_inv_ar_recv_appl(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
747 IS
748 SELECT NVL(SUM(line_applied),0) line_applied,
749 NVL(SUM(tax_applied),0) tax_applied,
750 NVL(SUM(freight_applied),0) freight_applied,
751 NVL(SUM(amount_applied),0) amount_applied
752 FROM ar_receivable_applications_all
753 WHERE applied_customer_trx_id = cp_customer_trx_id
754 AND application_type = 'CM'
755 AND display = 'Y'
756 AND status = 'APP';
757
758 CURSOR cur_ra_customer_trx(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
759 IS
760 SELECT NVL(exchange_rate,1) exchange_rate,
761 set_of_books_id
762 FROM ra_customer_trx_all
763 WHERE customer_trx_id = cp_customer_trx_id;
764
765 CURSOR cur_utl_location
766 IS
767 SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,Value,SUBSTR (value,1,INSTR(value,',') -1)) utl_location
768 FROM v$parameter
769 WHERE name = 'utl_file_dir';
770
771 CURSOR cur_sync_il_line_tax (cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
772 IS
773 SELECT jrctl.customer_trx_line_id,
774 nvl(sum(jrcttl.tax_amount),0) tax_amount
775 FROM JAI_AR_TRX_LINES jrctl,
776 JAI_AR_TRX_TAX_LINES jrcttl
777 WHERE jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
778 AND jrctl.customer_trx_id = cp_customer_trx_id
779 GROUP BY jrctl.customer_trx_line_id
780 HAVING ROUND(nvl(sum(jrcttl.tax_amount),0)) <>( SELECT ROUND(NVL(tax_amount,0))
781 FROM JAI_AR_TRX_LINES a
782 WHERE a.customer_trx_line_id = jrctl.customer_trx_line_id);
783
784 CURSOR cur_sync_il_hdr_tax (cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
785 IS
786 SELECT jtrx.customer_trx_id ,
787 NVL(SUM(jrctl.tax_amount),0) tax_amount
788 FROM JAI_AR_TRX_LINES jrctl,
789 JAI_AR_TRXS jtrx
790 WHERE jrctl.customer_trx_id = cp_customer_trx_id
791 AND jrctl.customer_trx_id = jtrx.customer_trx_id
792 GROUP BY jtrx.customer_trx_id
793 HAVING ROUND(NVL(SUM(jrctl.tax_amount),0)) <> ( SELECT ROUND(NVL(tax_amount,0))
794 FROM JAI_AR_TRXS a
795 WHERE a.customer_trx_id = jtrx.customer_trx_id);
796
797 lv_utl_location VARCHAR2(512);
798 ln_file_hdl UTL_FILE.FILE_TYPE;
799 lv_process_status VARCHAR2(2);
800 lv_process_message VARCHAR2(1000);
801
802 rec_tot_payment_schedule cur_tot_payment_schedule%ROWTYPE;
803 rec_tot_payment_schedule_inv cur_tot_payment_schedule%ROWTYPE;
804 rec_tot_cust_trx_gl_dist_rec cur_tot_cust_trx_gl_dist%ROWTYPE;
805 rec_tot_cust_trx_gl_dist_tax cur_tot_cust_trx_gl_dist%ROWTYPE;
806 rec_tot_cust_trx_gl_dist_frt cur_tot_cust_trx_gl_dist%ROWTYPE;
807 rec_il_tax_amount cur_il_tax_amount%ROWTYPE;
808 rec_cust_trx_gl_dist_tax cur_cust_trx_gl_dist_tax%ROWTYPE;
812 rec_ra_customer_trx_prev cur_ra_customer_trx%ROWTYPE;
809 rec_payment_schedules cur_payment_schedules%ROWTYPE;
810 rec_cm_ar_recv_appl cur_cm_ar_recv_appl%ROWTYPE;
811 rec_ra_customer_trx cur_ra_customer_trx%ROWTYPE;
813 rec_inv_cash_cm_recv_appl cur_inv_cash_cm_recv_appl%ROWTYPE;
814 rec_inv_ar_recv_appl cur_inv_ar_recv_appl%ROWTYPE;
815 rec_chk_cm_exists cur_chk_cm_exists%ROWTYPE;
816 rec_chk_gl_dist_rctl cur_chk_gl_dist_rctl%ROWTYPE;
817
818 lv_validate_first VARCHAR2(1);
819 lv_validate_all VARCHAR2(1);
820 lv_generate_log VARCHAR2(1);
821 lv_generate_detail_log VARCHAR2(1);
822 lv_fix_data VARCHAR2(1);
823 lv_commit VARCHAR2(1);
824 lv_debug VARCHAR2(1);
825 lv_log_filename VARCHAR2(100);
826 lv_datafix_filename VARCHAR2(100);
827 ln_total_count NUMBER;
828 ln_err_num NUMBER;
829 ln_local_cnt NUMBER;
830 ln_error_cnt NUMBER;
831 ln_precision NUMBER;
832 lv_sql_statement VARCHAR2(4000);
833 ln_exists NUMBER;
834
835 lt_error_table jai_ar_validate_data_pkg.t_error_table;
836
837 BEGIN
838 p_process_status := jai_constants.successful;
839 p_process_message := NULL;
840
841 lv_validate_first := p_validate_first;
842 lv_validate_all := p_validate_all;
843 lv_generate_log := p_generate_log;
844 lv_generate_detail_log := p_generate_detail_log;
845 lv_fix_data := p_fix_data;
846 lv_commit := p_commit;
847 lv_debug := p_debug;
848 lv_log_filename := p_log_filename;
849
850 --Check to insure that either the process runs for a particular customer_trx_id OR start_date and end_date should be specified
851 IF p_customer_trx_id IS NULL THEN
852 IF p_start_date IS NULL OR p_end_date IS NULL THEN
853 p_process_status := jai_constants.expected_error;
854 p_process_message := 'Problem in call to procedure post_validation. If customer_trx_id is not given, then both start_date and end_date are required';
855
856 GOTO exit_point;
857 END IF;
858 END IF;
859
860 If lv_validate_first = 'Y' AND lv_validate_all = 'Y' THEN
861 p_process_status := jai_constants.expected_error;
862 p_process_message := 'Problem in call to procedure post_validation. Only one of these parameter - p_validate_first, p_validate_all should be Y';
863
864 GOTO exit_point;
865 ELSIF lv_validate_first = 'N' AND lv_validate_all = 'N' THEN
866 p_process_status := jai_constants.expected_error;
867 p_process_message := 'Problem in call to procedure post_validation. Only one of these parameter - p_validate_first, p_validate_all should be N';
868
869 GOTO exit_point;
870 END IF;
871
872 If lv_validate_first = 'Y' THEN
873 lv_generate_log := 'N';
874 lv_fix_data := 'N';
875 lv_commit := 'N';
876 END IF;
877
878 If lv_fix_data = 'Y' THEN
879 lv_generate_log := 'Y';
880 lv_generate_detail_log := 'Y';
881 END IF;
882
883 If lv_commit = 'Y' AND lv_fix_data = 'N' THEN
884 p_process_status := jai_constants.expected_error;
885 p_process_message := 'Problem in call to procedure post_validation. p_commit can be Y only if fix_data = Y';
886
887 GOTO exit_point;
888 NULL;
889 END IF;
890
891 IF lv_generate_detail_log = 'Y' THEN
892 lv_generate_log := 'Y';
893 END IF;
894
895 IF lv_generate_log = 'Y' THEN
896
897 OPEN cur_utl_location;
898 FETCH cur_utl_location INTO lv_utl_location;
899 CLOSE cur_utl_location;
900
901 IF p_log_filename IS NULL THEN
902
903 IF p_customer_trx_id IS NOT NULL THEN
904 lv_log_filename := 'jai_ar_recon_'||p_customer_trx_id||'.log';
905 lv_datafix_filename := 'jai_ar_recon_datafix_'||p_customer_trx_id||'.log';
906 ELSIF p_start_date IS NOT NULL THEN
907 lv_log_filename := 'jai_ar_recon_'||TO_CHAR(p_start_date,'YYYYMMDD')||'.log';
908 lv_datafix_filename := 'jai_ar_recon_datafix_'||TO_CHAR(p_start_date,'YYYYMMDD')||'.log';
909 ELSE
910 lv_log_filename := 'jai_ar_recon.log';
911 lv_datafix_filename := 'jai_ar_recon_datafix.log';
912 END IF;
913
914 ln_file_hdl := UTL_FILE.FOPEN (lv_utl_location, lv_log_filename,'W');
915 UTL_FILE.FCLOSE (ln_file_hdl);
916
917 ln_file_hdl := UTL_FILE.FOPEN (lv_utl_location, lv_datafix_filename,'W');
918 UTL_FILE.FCLOSE (ln_file_hdl);
919
920 ELSE
921 lv_log_filename := p_log_filename;
922 lv_datafix_filename := NVL(SUBSTR(lv_log_filename,1,instr(lv_log_filename,'.',1,1)-1),lv_log_filename)||'_datafix'||substr(lv_log_filename,instr(lv_log_filename,'.',1,1));
923 END IF;
924 END IF;
925
926 IF lv_log_filename IS NULL THEN
927 lv_log_filename := 'jai_ar_recon.log';
928 END IF;
929
930 SAVEPOINT start_program;
931
932 IF lv_generate_log = 'Y' THEN
936 ln_total_count := 0;
933 jai_cmn_utils_pkg.print_log(lv_log_filename, 'Start of JAI AR Reconcilation program at '||TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS'));
934 END IF;
935
937
938 populate_error_table( p_error_table => lt_error_table,
939 p_process_status => lv_process_status,
940 p_process_message => lv_process_message);
941
942 IF lv_process_status <> jai_constants.successful THEN
943 p_process_status := lv_process_status;
944 p_process_message := lv_process_message;
945
946 GOTO exit_point;
947 END IF;
948
949 /**************************************
950 ||Start of processing
951 ***************************************/
952 FOR rec_get_cust_trx IN cur_get_cust_trx( p_start_date,
953 p_end_date,
954 p_customer_trx_id)
955 LOOP
956
957 ln_error_cnt := 0;
958
959 IF lv_generate_detail_log = 'Y' THEN
960 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
961 jai_cmn_utils_pkg.print_log(lv_log_filename, 'Started processing customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
962 END IF;
963
964 ln_total_count := ln_total_count + 1;
965
966 ln_exists := null;
967
968 OPEN cur_revrec_run(cp_customer_trx_id => rec_get_cust_trx.customer_trx_id );
969 FETCH cur_revrec_run INTO ln_exists ;
970
971 IF cur_revrec_run%found THEN
972
973 --As revenue recognition program has already been run on this customer_trx_id hence skip this and go to next record.
974 IF lv_generate_detail_log = 'Y' THEN
975 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
976 jai_cmn_utils_pkg.print_log(lv_log_filename, 'Revenue recognition program has already been run on the customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
977 END IF;
978
979 CLOSE cur_revrec_run;
980 goto exit_point;
981 END IF;
982 CLOSE cur_revrec_run;
983
984 rec_il_tax_amount := NULL;
985
986 OPEN cur_il_tax_amount(rec_get_cust_trx.customer_trx_id);
987 FETCH cur_il_tax_amount INTO rec_il_tax_amount;
988 CLOSE cur_il_tax_amount;
989
990 rec_cust_trx_gl_dist_tax := NULL;
991
992 OPEN cur_cust_trx_gl_dist_tax(rec_get_cust_trx.customer_trx_id);
993 FETCH cur_cust_trx_gl_dist_tax INTO rec_cust_trx_gl_dist_tax;
994 CLOSE cur_cust_trx_gl_dist_tax;
995
996 OPEN cur_tot_payment_schedule(rec_get_cust_trx.customer_trx_id);
997 FETCH cur_tot_payment_schedule INTO rec_tot_payment_schedule;
998 CLOSE cur_tot_payment_schedule;
999
1000 OPEN cur_tot_cust_trx_gl_dist(rec_get_cust_trx.customer_trx_id,
1001 'REC');
1002 FETCH cur_tot_cust_trx_gl_dist INTO rec_tot_cust_trx_gl_dist_rec;
1003 CLOSE cur_tot_cust_trx_gl_dist;
1004
1005 OPEN cur_tot_cust_trx_gl_dist(rec_get_cust_trx.customer_trx_id,
1006 'TAX');
1007 FETCH cur_tot_cust_trx_gl_dist INTO rec_tot_cust_trx_gl_dist_tax;
1008 CLOSE cur_tot_cust_trx_gl_dist;
1009
1010 OPEN cur_tot_cust_trx_gl_dist(rec_get_cust_trx.customer_trx_id,
1011 'FREIGHT');
1012 FETCH cur_tot_cust_trx_gl_dist INTO rec_tot_cust_trx_gl_dist_frt;
1013 CLOSE cur_tot_cust_trx_gl_dist;
1014
1015 OPEN cur_ra_customer_trx(rec_get_cust_trx.customer_trx_id);
1016 FETCH cur_ra_customer_trx INTO rec_ra_customer_trx;
1017 CLOSE cur_ra_customer_trx;
1018
1019 OPEN cur_ra_customer_trx(rec_get_cust_trx.previous_customer_trx_id);
1020 FETCH cur_ra_customer_trx INTO rec_ra_customer_trx_prev;
1021 CLOSE cur_ra_customer_trx;
1022
1023 rec_chk_cm_exists := null;
1024
1025 OPEN cur_chk_cm_exists (cp_customer_trx_id => rec_get_cust_trx.customer_trx_id);
1026 FETCH cur_chk_cm_exists INTO rec_chk_cm_exists;
1027 CLOSE cur_chk_cm_exists ;
1028
1029 --=================================================================================================================--
1030 --Start of common validations--
1031 --=================================================================================================================--
1032 ln_exists := null;
1033
1034 --Get 1 if record has not been posted else null if posted
1035 OPEN cur_chk_gl_posting(cp_customer_trx_id => rec_get_cust_trx.customer_trx_id);
1036 FETCH cur_chk_gl_posting INTO ln_exists;
1037 CLOSE cur_chk_gl_posting;
1038
1039 IF nvl(ln_exists,0) = 1 THEN
1040
1041 --Record has not been posted hence process
1042
1043 ln_err_num := 1;
1044 --To check if the data in ja_in_ra_cust_trx_tax_lines is in sync with 'TAX'/'FREIGHT' records in ra_customer_trx_lines_all
1045 IF lt_error_table(ln_err_num).enable = 'Y' THEN
1046 IF rec_il_tax_amount.customer_trx_line_id IS NOT NULL THEN
1047 lv_process_status := jai_constants.expected_error;
1048 lv_process_message := lt_error_table(ln_err_num).error_description;
1049
1050 if lv_validate_first = 'Y' THEN
1051 goto EXIT_POINT;
1052 END IF;
1053
1057 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
1054 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
1055
1056 If lv_generate_detail_log = 'Y' THEN
1058 END IF;
1059
1060 IF lv_generate_log = 'Y' THEN
1061 IF ln_error_cnt = 0 THEN
1062 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
1063 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
1064 END IF;
1065
1066 ln_error_cnt := 1;
1067
1068 FOR i IN cur_il_tax_amount(rec_get_cust_trx.customer_trx_id)
1069 LOOP
1070 UPDATE ra_customer_trx_lines_all
1071 SET extended_amount = i.tax_amount,
1072 last_update_date = sysdate,
1073 last_updated_by = gn_bug_no
1074 WHERE customer_trx_line_id = i.customer_trx_line_id
1075 AND customer_trx_id = i.customer_trx_id;
1076
1077 lv_sql_statement := fnd_global.local_chr(10)||
1078 'UPDATE ra_customer_trx_lines_all'||fnd_global.local_chr(10)||
1079 'SET extended_amount = '||i.tax_amount||','||fnd_global.local_chr(10)||
1080 ' last_update_date = sysdate,'||fnd_global.local_chr(10)||
1081 ' last_updated_by = '||gn_bug_no||fnd_global.local_chr(10)||
1082 'WHERE customer_trx_line_id = '||i.customer_trx_line_id||fnd_global.local_chr(10)||
1083 'AND customer_trx_id = '||i.customer_trx_id||';';
1084
1085 jai_cmn_utils_pkg.print_log(lv_datafix_filename, lv_sql_statement);
1086
1087 lv_sql_statement := NULL;
1088
1089 END LOOP;
1090
1091 END IF;
1092
1093 END IF;
1094 END IF;
1095
1096 ln_err_num := 2;
1097 --To check if the data is correct in ja_in_ra_cust_trx_tax_lines corresponding to records in ra_cust_trx_line_gl_dist_all
1098 IF lt_error_table(ln_err_num).enable = 'Y' THEN
1099 IF rec_cust_trx_gl_dist_tax.customer_trx_line_id IS NOT NULL THEN
1100
1101 lv_process_status := jai_constants.expected_error;
1102 lv_process_message := lt_error_table(ln_err_num).error_description;
1103
1104 if lv_validate_first = 'Y' THEN
1105 goto EXIT_POINT;
1106 END IF;
1107
1108 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
1109
1110 If lv_generate_detail_log = 'Y' THEN
1111 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
1112 END IF;
1113
1114 IF lv_generate_log = 'Y' THEN
1115
1116 IF ln_error_cnt = 0 THEN
1117 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
1118 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
1119 END IF;
1120
1121 ln_error_cnt := 1;
1122
1123 ln_precision := null;
1124
1125 OPEN jai_ar_validate_data_pkg.cur_curr_precision(rec_ra_customer_trx.set_of_books_id);
1126 FETCH jai_ar_validate_data_pkg.cur_curr_precision INTO ln_precision;
1127 CLOSE jai_ar_validate_data_pkg.cur_curr_precision;
1128
1129 /*
1130 || set ra_cust_trx_line_gl_dist_all.amount = il.tax_amount for all account_class in TAX and 'FREIGHT'
1131 */
1132 FOR i IN cur_cust_trx_gl_dist_tax(rec_get_cust_trx.customer_trx_id)
1133 LOOP
1134 UPDATE ra_cust_trx_line_gl_dist_all
1135 SET amount = i.tax_amount,
1136 acctd_amount = round(i.func_tax_amount,ln_precision),
1137 last_update_date = sysdate,
1138 last_updated_by = gn_bug_no
1139 WHERE customer_trx_line_id = i.customer_trx_line_id
1140 AND customer_trx_id = i.customer_trx_id;
1141
1142 lv_sql_statement := fnd_global.local_chr(10)||
1143 'UPDATE ra_cust_trx_line_gl_dist_all'||fnd_global.local_chr(10)||
1144 'SET amount = '||i.tax_amount||','||fnd_global.local_chr(10)||
1145 ' acctd_amount = round('||i.func_tax_amount||','||ln_precision||'),'||fnd_global.local_chr(10)||
1146 ' last_update_date = sysdate,'||fnd_global.local_chr(10)||
1147 ' last_updated_by = '||gn_bug_no||fnd_global.local_chr(10)||
1148 'WHERE customer_trx_line_id = '||i.customer_trx_line_id||fnd_global.local_chr(10)||
1149 'AND customer_trx_id = '||i.customer_trx_id||';';
1150
1151 jai_cmn_utils_pkg.print_log(lv_datafix_filename, lv_sql_statement);
1152
1153 lv_sql_statement := NULL;
1154
1155 END LOOP;
1156
1157 /*
1161 SET amount = (SELECT nvl(sum(amount),0)
1158 || set REC.extended_amt = SUM ( all rows except REC for that customer_trx_id ) and latest
1159 */
1160 UPDATE ra_cust_trx_line_gl_dist_all rec
1162 FROM ra_cust_trx_line_gl_dist_all rev_tax_frt
1163 WHERE rec.customer_trx_id = rev_tax_frt.customer_trx_id
1164 AND rev_tax_frt.account_class <> 'REC'
1165 ),
1166 acctd_amount = (SELECT nvl(sum(acctd_amount),0)
1167 FROM ra_cust_trx_line_gl_dist_all rev_tax_frt
1168 WHERE rec.customer_trx_id = rev_tax_frt.customer_trx_id
1169 AND rev_tax_frt.account_class <> 'REC'
1170 ),
1171 last_update_date = sysdate,
1172 last_updated_by = gn_bug_no
1173 WHERE rec.account_class = 'REC'
1174 AND rec.latest_rec_flag = 'Y'
1175 AND customer_trx_id = rec_get_cust_trx.customer_trx_id;
1176
1177 lv_sql_statement := fnd_global.local_chr(10)||
1178 'UPDATE ra_cust_trx_line_gl_dist_all rec
1179 SET amount = (SELECT nvl(sum(amount),0)
1180 FROM ra_cust_trx_line_gl_dist_all rev_tax_frt
1181 WHERE rec.customer_trx_id = rev_tax_frt.customer_trx_id
1182 AND rev_tax_frt.account_class <> '||''''||'REC'||''''||
1183 '),
1184 acctd_amount = (SELECT nvl(sum(acctd_amount),0)
1185 FROM ra_cust_trx_line_gl_dist_all rev_tax_frt
1186 WHERE rec.customer_trx_id = rev_tax_frt.customer_trx_id
1187 AND rev_tax_frt.account_class <> '||''''||'REC'||''''||
1188 '),
1189 last_update_date = sysdate,
1190 last_updated_by = '||gn_bug_no||'
1191 WHERE rec.account_class = '||''''||'REC'||''''||'
1192 AND rec.latest_rec_flag = '||''''||'Y'||''''||'
1193 AND customer_trx_id = '||rec_get_cust_trx.customer_trx_id||';';
1194
1195 jai_cmn_utils_pkg.print_log(lv_datafix_filename, lv_sql_statement);
1196
1197 lv_sql_statement := NULL;
1198
1199
1200 END IF;
1201
1202 END IF;
1203 END IF;
1204 END IF; -- End of Record has not been posted check
1205
1206
1207 OPEN cur_chk_gl_dist_rctl(cp_customer_trx_id => rec_get_cust_trx.customer_trx_id);
1208 FETCH cur_chk_gl_dist_rctl INTO rec_chk_gl_dist_rctl;
1209 CLOSE cur_chk_gl_dist_rctl;
1210
1211 ln_err_num := 11;
1212
1213 --Check that ra_customer_Trx_lines_all is in sync with gl_dist
1214 IF lt_error_table(ln_err_num).enable = 'Y' THEN
1215 IF rec_chk_gl_dist_rctl.customer_trx_line_id IS NOT NULL THEN
1216 lv_process_status := jai_constants.expected_error;
1217 lv_process_message := lt_error_table(ln_err_num).error_description;
1218
1219 if lv_validate_first = 'Y' THEN
1220 goto EXIT_POINT;
1221 END IF;
1222
1223 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
1224
1225 If lv_generate_detail_log = 'Y' THEN
1226 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
1227 END IF;
1228
1229 IF lv_generate_log = 'Y' THEN
1230
1231 IF ln_error_cnt = 0 THEN
1232 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
1233 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
1234 END IF;
1235
1236 ln_error_cnt := 1;
1237
1238 FOR rec_gl_dist_rctl in cur_chk_gl_dist_rctl (cp_customer_trx_id => rec_get_cust_trx.customer_trx_id)
1239 LOOP
1240 UPDATE ra_customer_trx_lines_all
1241 SET extended_amount = rec_gl_dist_rctl.amount,
1242 last_update_date = sysdate,
1243 last_updated_by = gn_bug_no
1244 WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id
1245 AND customer_trx_line_id = rec_gl_dist_rctl.customer_trx_line_id;
1246
1247 lv_sql_statement := fnd_global.local_chr(10)||
1248 'UPDATE ra_customer_trx_lines_all
1249 SET extended_amount = '||rec_gl_dist_rctl.amount||',
1250 last_update_date = sysdate,
1251 last_updated_by = '||gn_bug_no||'
1252 WHERE customer_trx_id = '||rec_get_cust_trx.customer_trx_id||'
1256
1253 AND customer_trx_line_id = '||rec_gl_dist_rctl.customer_trx_line_id||';';
1254
1255 jai_cmn_utils_pkg.print_log(lv_datafix_filename, lv_sql_statement);
1257 lv_sql_statement := NULL;
1258
1259 END LOOP;
1260 END IF;
1261 END IF;
1262 END IF;
1263
1264 ln_err_num := 3;
1265 --To check if ADO in ar_payment_schedules_all is equal to amount of REC in ra_cust_trx_line_gl_dist_all
1266 IF lt_error_table(ln_err_num).enable = 'Y' THEN
1267 IF rec_tot_payment_schedule.amount_due_original <> rec_tot_cust_trx_gl_dist_rec.amount THEN
1268 lv_process_status := jai_constants.expected_error;
1269 lv_process_message := lt_error_table(ln_err_num).error_description;
1270
1271 if lv_validate_first = 'Y' THEN
1272 goto EXIT_POINT;
1273 END IF;
1274
1275 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
1276
1277 If lv_generate_detail_log = 'Y' THEN
1278 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
1279 END IF;
1280
1281 /*
1282 ||arps.ado = gl_dist.rec
1283 */
1284
1285 IF lv_generate_log = 'Y' THEN
1286
1287 IF ln_error_cnt = 0 THEN
1288 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
1289 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
1290 END IF;
1291
1292 ln_error_cnt := 1;
1293
1294 rectify_ar_pay_sch(p_customer_trx_id => rec_get_cust_trx.customer_trx_id,
1295 p_gl_rec_amount => rec_tot_cust_trx_gl_dist_rec.amount,
1296 p_datafix_filename => lv_datafix_filename,
1297 p_process_status => lv_process_status,
1298 p_process_message => lv_process_message);
1299 END IF;
1300 END IF;
1301 END IF;
1302
1303 ln_err_num := 4;
1304 --To check if TO in ar_payment_schedules_all is equal to amount of TAX in ra_cust_trx_line_gl_dist_all
1305 IF lt_error_table(ln_err_num).enable = 'Y' THEN
1306 IF rec_tot_payment_schedule.tax_original <> rec_tot_cust_trx_gl_dist_tax.amount THEN
1307 lv_process_status := jai_constants.expected_error;
1308 lv_process_message := lt_error_table(ln_err_num).error_description;
1309
1310 if lv_validate_first = 'Y' THEN
1311 goto EXIT_POINT;
1312 END IF;
1313
1314 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
1315
1316 If lv_generate_detail_log = 'Y' THEN
1317 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
1318 END IF;
1319
1320 /*
1321 ||arps.to = gl_dist.tax
1322 */
1323
1324 IF lv_generate_log = 'Y' THEN
1325
1326 IF ln_error_cnt = 0 THEN
1327 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
1328 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
1329 END IF;
1330
1331 ln_error_cnt := 1;
1332
1333 rectify_ar_pay_sch(p_customer_trx_id => rec_get_cust_trx.customer_trx_id,
1334 p_gl_tax_amount => rec_tot_cust_trx_gl_dist_tax.amount,
1335 p_datafix_filename => lv_datafix_filename,
1336 p_process_status => lv_process_status,
1337 p_process_message => lv_process_message);
1338 END IF;
1339 END IF;
1340 END IF;
1341
1342 ln_err_num := 5;
1343 --To check if FO in ar_payment_schedules_all is equal to amount of FREIGHT in ra_cust_trx_line_gl_dist_all
1344 IF lt_error_table(ln_err_num).enable = 'Y' THEN
1345 IF rec_tot_payment_schedule.freight_original <> rec_tot_cust_trx_gl_dist_frt.amount THEN
1346 lv_process_status := jai_constants.expected_error;
1347 lv_process_message := lt_error_table(ln_err_num).error_description;
1348
1349 if lv_validate_first = 'Y' THEN
1350 goto EXIT_POINT;
1351 END IF;
1352
1353 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
1354
1355 If lv_generate_detail_log = 'Y' THEN
1356 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
1357 END IF;
1358
1359 /*
1360 ||arps.FO = gl_dist.FRT
1361 */
1362
1363 IF lv_generate_log = 'Y' THEN
1364
1365 IF ln_error_cnt = 0 THEN
1366 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
1367 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
1368 END IF;
1369
1370 ln_error_cnt := 1;
1371
1372 rectify_ar_pay_sch(p_customer_trx_id => rec_get_cust_trx.customer_trx_id,
1376 p_process_message => lv_process_message);
1373 p_gl_freight_amount => rec_tot_cust_trx_gl_dist_frt.amount,
1374 p_datafix_filename => lv_datafix_filename,
1375 p_process_status => lv_process_status,
1377 END IF;
1378 END IF;
1379 END IF;
1380
1381 ln_err_num := 6;
1382 --To Check if ADO = ALIO + TO + FO in ar_payment_schedules_all
1383 IF lt_error_table(ln_err_num).enable = 'Y' THEN
1384 IF rec_tot_payment_schedule.amount_due_original <> ( rec_tot_payment_schedule.amount_line_items_original
1385 + rec_tot_payment_schedule.tax_original
1386 + rec_tot_payment_schedule.freight_original)
1387 THEN
1388 lv_process_status := jai_constants.expected_error;
1389 lv_process_message := lt_error_table(ln_err_num).error_description;
1390
1391 if lv_validate_first = 'Y' THEN
1392 goto EXIT_POINT;
1393 END IF;
1394
1395 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
1396
1397 If lv_generate_detail_log = 'Y' THEN
1398 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
1399 END IF;
1400
1401 /*
1402 || rectified as a part of common (3,4,5). No seperate update required
1403 */
1404 END IF;
1405 END IF;
1406
1407 ln_err_num := 7;
1408 --To Check if ADR = ALIR + TR + FR in ar_payment_schedules_all
1409 IF lt_error_table(ln_err_num).enable = 'Y' THEN
1410 IF rec_tot_payment_schedule.amount_due_remaining <> ( rec_tot_payment_schedule.amount_line_items_remaining
1411 + rec_tot_payment_schedule.tax_remaining
1412 + rec_tot_payment_schedule.freight_remaining)
1413 THEN
1414 lv_process_status := jai_constants.expected_error;
1415 lv_process_message := lt_error_table(ln_err_num).error_description;
1416
1417 if lv_validate_first = 'Y' THEN
1418 goto EXIT_POINT;
1419 END IF;
1420
1421 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
1422
1423 If lv_generate_detail_log = 'Y' THEN
1424 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
1425 END IF;
1426
1427 /*
1428 || rectified as a part of INVOICE (1).
1429 || rectified as a part of CM (1) No seperate update required
1430 */
1431
1432 END IF;
1433 END IF;
1434
1435 --=================================================================================================================--
1436 --Start of CM validations--
1437 --=================================================================================================================--
1438
1439 IF rec_get_cust_trx.type = 'CM' THEN
1440 ln_err_num := 21;
1441 --To check, if ADR, AADR, TR and FR are 0
1442 IF lt_error_table(ln_err_num).enable = 'Y' THEN
1443 IF rec_tot_payment_schedule.amount_due_remaining <> 0 OR
1444 rec_tot_payment_schedule.acctd_amount_due_remaining <> 0 OR
1445 rec_tot_payment_schedule.tax_remaining <> 0 OR
1446 rec_tot_payment_schedule.freight_remaining <> 0 THEN
1447
1448 lv_process_status := jai_constants.expected_error;
1449 lv_process_message := lt_error_table(ln_err_num).error_description;
1450
1451 if lv_validate_first = 'Y' THEN
1452 goto EXIT_POINT;
1453 END IF;
1454
1455 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
1456
1457 If lv_generate_detail_log = 'Y' THEN
1458 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
1459 END IF;
1460
1461 /*
1462 ||set all these values to 0
1463 */
1464
1465 IF lv_generate_log = 'Y' THEN
1466
1467 IF ln_error_cnt = 0 THEN
1468 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
1469 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
1470 END IF;
1471
1472 ln_error_cnt := 1;
1473
1474 UPDATE ar_payment_schedules_all
1475 SET amount_due_remaining = 0,
1476 acctd_amount_due_remaining = 0,
1477 tax_remaining = 0,
1478 freight_remaining = 0,
1479 last_update_date = sysdate,
1480 last_updated_by = gn_bug_no
1481 WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id;
1482
1483 lv_sql_statement := fnd_global.local_chr(10)||
1484 'UPDATE ar_payment_schedules_all
1488 freight_remaining = 0,
1485 SET amount_due_remaining = 0,
1486 acctd_amount_due_remaining = 0,
1487 tax_remaining = 0,
1489 last_update_date = sysdate,
1490 last_updated_by = '||gn_bug_no||'
1491 WHERE customer_trx_id = '||rec_get_cust_trx.customer_trx_id||';';
1492
1493 jai_cmn_utils_pkg.print_log(lv_datafix_filename, lv_sql_statement);
1494
1495 lv_sql_statement := NULL;
1496
1497
1498 END IF;
1499
1500 END IF;
1501 END IF;
1502
1503 ln_err_num := 22;
1504 --To check, if ADO = amount applied
1505 IF lt_error_table(ln_err_num).enable = 'Y' THEN
1506 IF rec_tot_payment_schedule.amount_due_original <> rec_tot_payment_schedule.amount_applied THEN
1507 lv_process_status := jai_constants.expected_error;
1508 lv_process_message := lt_error_table(ln_err_num).error_description;
1509
1510 if lv_validate_first = 'Y' THEN
1511 goto EXIT_POINT;
1512 END IF;
1513
1514 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
1515
1516 If lv_generate_detail_log = 'Y' THEN
1517 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
1518 END IF;
1519
1520 /*
1521 ||set amt_applied = ADO as has been corrected earlier
1522 */
1523
1524 IF lv_generate_log = 'Y' THEN
1525
1526 IF ln_error_cnt = 0 THEN
1527 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
1528 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
1529 END IF;
1530
1531 ln_error_cnt := 1;
1532
1533 UPDATE ar_payment_schedules_all
1534 SET amount_applied = amount_due_original,
1535 last_update_date = sysdate,
1536 last_updated_by = gn_bug_no
1537 WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id;
1538
1539 lv_sql_statement := fnd_global.local_chr(10)||
1540 'UPDATE ar_payment_schedules_all
1541 SET amount_applied = amount_due_original,
1542 last_update_date = sysdate,
1543 last_updated_by = '||gn_bug_no||
1544 ' WHERE customer_trx_id = '|| rec_get_cust_trx.customer_trx_id||';';
1545
1546 jai_cmn_utils_pkg.print_log(lv_datafix_filename, lv_sql_statement);
1547
1548 lv_sql_statement := NULL;
1549
1550 END IF;
1551
1552 END IF;
1553 END IF;
1554
1555 OPEN cur_cm_ar_recv_appl( rec_get_cust_trx.customer_trx_id,
1556 rec_ra_customer_trx.exchange_rate,
1557 rec_ra_customer_trx_prev.exchange_rate);
1558 FETCH cur_cm_ar_recv_appl INTO rec_cm_ar_recv_appl;
1559 CLOSE cur_cm_ar_recv_appl;
1560
1561 ln_err_num := 23;
1562 --To check, if ADO = SUM(LA + TA + FA) for ar_receivable_applications_all
1563 IF lt_error_table(ln_err_num).enable = 'Y' THEN
1564 IF ABS(rec_tot_payment_schedule.amount_due_original) <> ( ABS(rec_cm_ar_recv_appl.line_applied)
1565 + ABS(rec_cm_ar_recv_appl.tax_applied)
1566 + ABS(rec_cm_ar_recv_appl.freight_applied))
1567 THEN
1568 lv_process_status := jai_constants.expected_error;
1569 lv_process_message := lt_error_table(ln_err_num).error_description;
1570
1571 if lv_validate_first = 'Y' THEN
1572 goto EXIT_POINT;
1573 END IF;
1574
1575 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
1576
1577 If lv_generate_detail_log = 'Y' THEN
1578 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
1579 END IF;
1580
1581 /*
1582 ||IF CM has more than one applications then manual steps needs to be carried out else update and generate log
1583 ||For Single line -> ADO, FO,TO have already been corrected . Now set TA = TO, FA = FO
1584 */
1585
1586 IF lv_generate_log = 'Y' THEN
1587
1588 IF ln_error_cnt = 0 THEN
1589 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
1590 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
1591 END IF;
1592
1593 ln_error_cnt := 1;
1594
1595 rectify_ar_rec_appl(
1596 p_customer_trx_id => rec_get_cust_trx.customer_trx_id,
1600 p_arps_fo => ABS(rec_tot_payment_schedule.freight_original),
1597 p_previous_trx_id => rec_get_cust_trx.previous_customer_trx_id,
1598 p_arps_ado => ABS(rec_tot_payment_schedule.amount_due_original),
1599 p_arps_to => ABS(rec_tot_payment_schedule.tax_original),
1601 p_datafix_filename => lv_datafix_filename,
1602 p_process_status => lv_process_status,
1603 p_process_message => lv_process_message) ;
1604 END IF;
1605
1606 END IF;
1607
1608 END IF;
1609
1610 ln_err_num := 24;
1611 --To check, if amount_applied <> LA + TA + FA
1612 IF lt_error_table(ln_err_num).enable = 'Y' THEN
1613 IF rec_cm_ar_recv_appl.amount_applied <> ( rec_cm_ar_recv_appl.line_applied
1614 + rec_cm_ar_recv_appl.tax_applied
1615 + rec_cm_ar_recv_appl.freight_applied)
1616 THEN
1617 lv_process_status := jai_constants.expected_error;
1618 lv_process_message := lt_error_table(ln_err_num).error_description;
1619
1620 if lv_validate_first = 'Y' THEN
1621 goto EXIT_POINT;
1622 END IF;
1623
1624 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
1625
1626 If lv_generate_detail_log = 'Y' THEN
1627 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
1628 END IF;
1629
1630 IF lv_generate_log = 'Y' THEN
1631 UPDATE ar_receivable_applications_all
1632 SET amount_applied = NVL(line_applied,0) + NVL(tax_applied,0) + NVL(freight_applied,0),
1633 last_update_date = sysdate,
1634 last_updated_by = gn_bug_no
1635 WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id
1636 AND application_type = 'CM'
1637 AND display = 'Y'
1638 AND status = 'APP';
1639
1640 lv_sql_statement := fnd_global.local_chr(10)||
1641 'UPDATE ar_receivable_applications_all
1642 SET amount_applied = NVL(line_applied,0) + NVL(tax_applied,0) + NVL(freight_applied,0),
1643 last_update_date = sysdate,
1644 last_updated_by = '||gn_bug_no||'
1645 WHERE customer_trx_id = '||rec_get_cust_trx.customer_trx_id||'
1646 AND application_type = '||''''||'CM'||''''||'
1647 AND display = '||''''||'Y'||''''||'
1648 AND status = '||''''||'APP'||''''||';';
1649
1650 jai_cmn_utils_pkg.print_log(lv_datafix_filename, lv_sql_statement);
1651
1652 lv_sql_statement := NULL;
1653
1654 END IF;
1655 /*
1656 ||AA = LA + TA + FA
1657 */
1658
1659 END IF;
1660 END IF;
1661
1662 ln_precision := null;
1663
1664 OPEN jai_ar_validate_data_pkg.cur_curr_precision(rec_ra_customer_trx.set_of_books_id);
1665 FETCH jai_ar_validate_data_pkg.cur_curr_precision INTO ln_precision;
1666 CLOSE jai_ar_validate_data_pkg.cur_curr_precision;
1667
1668 ln_err_num := 25;
1669 --To check, if AAAF = AA * exchange_rate(CM) for ar_receivable_applications_all
1670 IF lt_error_table(ln_err_num).enable = 'Y' THEN
1671 IF ROUND(rec_cm_ar_recv_appl.acctd_amount_applied_from,0) <> ROUND(rec_cm_ar_recv_appl.acctd_amount_applied_from_calc,0)
1672 THEN
1673 lv_process_status := jai_constants.expected_error;
1674 lv_process_message := lt_error_table(ln_err_num).error_description;
1675
1676 if lv_validate_first = 'Y' THEN
1677 goto EXIT_POINT;
1678 END IF;
1679
1680 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
1681
1682 If lv_generate_detail_log = 'Y' THEN
1683 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
1684 END IF;
1685
1686 /*
1687 ||set AAAF = AA * exchange_rate(CM)
1688 */
1689
1690 IF lv_generate_log = 'Y' THEN
1691
1692 IF ln_error_cnt = 0 THEN
1693 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
1694 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
1695 END IF;
1696
1697 ln_error_cnt := 1;
1698
1699
1700 UPDATE ar_receivable_applications_all
1701 SET acctd_amount_applied_from = ROUND(amount_applied * rec_ra_customer_trx.exchange_rate, ln_precision),
1702 last_update_date = sysdate,
1703 last_updated_by = gn_bug_no
1704 WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id
1708
1705 AND application_type = 'CM'
1706 AND display = 'Y'
1707 AND status = 'APP';
1709 lv_sql_statement := fnd_global.local_chr(10)||
1710 'UPDATE ar_receivable_applications_all
1711 SET acctd_amount_applied_from = ROUND(amount_applied * '||rec_ra_customer_trx.exchange_rate||','|| ln_precision||'),
1712 last_update_date = sysdate,
1713 last_updated_by = '||gn_bug_no||'
1714 WHERE customer_trx_id = '||rec_get_cust_trx.customer_trx_id||'
1715 AND application_type = '||''''||'CM'||''''||'
1716 AND display = '||''''||'Y'||''''||'
1717 AND status = '||''''||'APP'||''''||';';
1718
1719 jai_cmn_utils_pkg.print_log(lv_datafix_filename, lv_sql_statement);
1720
1721 lv_sql_statement := NULL;
1722
1723 END IF;
1724
1725 END IF;
1726 END IF;
1727
1728 ln_err_num := 26;
1729 --To check, if AAAT = AA * exchange_rate(INV) for ar_receivable_applications_all
1730 IF lt_error_table(ln_err_num).enable = 'Y' THEN
1731 IF ROUND(rec_cm_ar_recv_appl.acctd_amount_applied_to,0) <> ROUND(rec_cm_ar_recv_appl.acctd_amount_applied_to_calc,0)
1732 THEN
1733 lv_process_status := jai_constants.expected_error;
1734 lv_process_message := lt_error_table(ln_err_num).error_description;
1735
1736 if lv_validate_first = 'Y' THEN
1737 goto EXIT_POINT;
1738 END IF;
1739
1740 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
1741
1742 If lv_generate_detail_log = 'Y' THEN
1743 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
1744 END IF;
1745
1746
1747 /*
1748 ||set AAAT = AA * exchange_rate(INV)
1749 */
1750
1751 IF lv_generate_log = 'Y' THEN
1752
1753 IF ln_error_cnt = 0 THEN
1754 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
1755 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
1756 END IF;
1757
1758 ln_error_cnt := 1;
1759
1760 UPDATE ar_receivable_applications_all
1761 SET acctd_amount_applied_to = ROUND(amount_applied * rec_ra_customer_trx_prev.exchange_rate, ln_precision),
1762 last_update_date = sysdate,
1763 last_updated_by = gn_bug_no
1764 WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id
1765 AND application_type = 'CM'
1766 AND display = 'Y'
1767 AND status = 'APP';
1768
1769 lv_sql_statement := fnd_global.local_chr(10)||
1770 'UPDATE ar_receivable_applications_all
1771 SET acctd_amount_applied_to = ROUND(amount_applied * '||rec_ra_customer_trx_prev.exchange_rate||','|| ln_precision||'),
1772 last_update_date = sysdate,
1773 last_updated_by = '||gn_bug_no||'
1774 WHERE customer_trx_id = '||rec_get_cust_trx.customer_trx_id||'
1775 AND application_type = '||''''||'CM'||''''||'
1776 AND display = '||''''||'Y'||''''||'
1777 AND status = '||''''||'APP'||''''||';';
1778
1779 jai_cmn_utils_pkg.print_log(lv_datafix_filename, lv_sql_statement);
1780
1781 lv_sql_statement := NULL;
1782
1783 END IF;
1784
1785 END IF;
1786 END IF;
1787 --=================================================================================================================--
1788 --End of CM validations--
1789 --=================================================================================================================--
1790
1791 --=================================================================================================================--
1792 --Start of Invoice validations--
1793 --Valid the correctness of Invoice w.r.t to receivable applications only when only if the one has atleast one
1794 -- CM applied against it
1795 --=================================================================================================================--
1796
1797 ELSIF rec_get_cust_trx.type = 'INV'
1798 AND rec_chk_cm_exists.application_type = 'CM'
1799 THEN
1800
1801 OPEN cur_inv_cash_cm_recv_appl(rec_get_cust_trx.customer_trx_id);
1802 FETCH cur_inv_cash_cm_recv_appl INTO rec_inv_cash_cm_recv_appl;
1803 CLOSE cur_inv_cash_cm_recv_appl;
1804
1805
1806 ln_err_num := 41;
1807 --To check, if ADO = ADR + SUM(LA + TA + FA) of ar_receivable_applications
1808 IF lt_error_table(ln_err_num).enable = 'Y' THEN
1812 + rec_inv_cash_cm_recv_appl.freight_applied)
1809 IF rec_tot_payment_schedule.amount_due_original <> ( rec_tot_payment_schedule.amount_due_remaining
1810 + rec_inv_cash_cm_recv_appl.line_applied
1811 + rec_inv_cash_cm_recv_appl.tax_applied
1813 THEN
1814 lv_process_status := jai_constants.expected_error;
1815 lv_process_message := lt_error_table(ln_err_num).error_description;
1816
1817 if lv_validate_first = 'Y' THEN
1818 goto EXIT_POINT;
1819 END IF;
1820
1821 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
1822
1823 If lv_generate_detail_log = 'Y' THEN
1824 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
1825 END IF;
1826
1827 /*
1828 ||set TR = TO - SUM(TA) for the arps.payment_schedule_id = RECA.applied_payment_schedule_id AND ARPS.customer_trx_id = RECA.applied_customer_trx_id
1829 || FR = FO - SUM(FA) for the arps.payment_schedule_id = RECA.applied_payment_schedule_id AND ARPS.customer_trx_id = RECA.applied_customer_trx_id
1830 || ADR = ADO - SUM(AA) for the arps.payment_schedule_id = RECA.applied_payment_schedule_id AND ARPS.customer_trx_id = RECA.applied_customer_trx_id
1831 */
1832
1833 IF lv_generate_log = 'Y' THEN
1834
1835 IF ln_error_cnt = 0 THEN
1836 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
1837 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
1838 END IF;
1839
1840 ln_error_cnt := 1;
1841
1842 UPDATE ar_payment_schedules_all inv_arps
1843 SET tax_remaining = nvl(tax_original,0) - ( SELECT
1844 nvl(sum(tax_applied),0) tot_tax_applied
1845 FROM
1846 ar_receivable_applications_all reca
1847 WHERE
1848 reca.applied_payment_schedule_id = inv_arps.payment_schedule_id
1849 AND reca.applied_customer_trx_id = inv_arps.customer_trx_id
1850 AND reca.display = 'Y'
1851 AND reca.status = 'APP'
1852 ) ,
1853 freight_remaining = nvl(freight_original,0) - ( SELECT
1854 nvl(sum(freight_applied),0) tot_frt_applied
1855 FROM
1856 ar_receivable_applications_all reca
1857 WHERE
1858 reca.applied_payment_schedule_id = inv_arps.payment_schedule_id
1859 AND reca.applied_customer_trx_id = inv_arps.customer_trx_id
1860 AND reca.display = 'Y'
1861 AND reca.status = 'APP'
1862 ) ,
1863 amount_due_remaining = nvl(amount_due_original,0) - ( SELECT
1864 nvl(sum(amount_applied),0) tot_amt_applied
1865 FROM
1866 ar_receivable_applications_all reca
1867 WHERE
1868 reca.applied_payment_schedule_id = inv_arps.payment_schedule_id
1869 AND reca.applied_customer_trx_id = inv_arps.customer_trx_id
1870 AND reca.display = 'Y'
1871 AND reca.status = 'APP'
1872 ) ,
1873 last_update_date = sysdate,
1874 last_updated_by = gn_bug_no
1875 WHERE
1876 customer_trx_id = rec_get_cust_trx.customer_trx_id;
1877
1878
1879 lv_sql_statement := fnd_global.local_chr(10)||
1880 'UPDATE ar_payment_schedules_all inv_arps
1881 SET tax_remaining = nvl(tax_original,0) - ( SELECT
1885 WHERE
1882 nvl(sum(tax_applied),0) tot_tax_applied
1883 FROM
1884 ar_receivable_applications_all reca
1886 reca.applied_payment_schedule_id = inv_arps.payment_schedule_id
1887 AND reca.applied_customer_trx_id = inv_arps.customer_trx_id
1888 AND reca.display = '||''''||'Y'||''''||'
1889 AND reca.status = '||''''||'APP'||''''||'
1890 ) ,
1891 freight_remaining = nvl(freight_original,0) - ( SELECT
1892 nvl(sum(freight_applied),0) tot_frt_applied
1893 FROM
1894 ar_receivable_applications_all reca
1895 WHERE
1896 reca.applied_payment_schedule_id = inv_arps.payment_schedule_id
1897 AND reca.applied_customer_trx_id = inv_arps.customer_trx_id
1898 AND reca.display = '||''''||'Y'||''''||'
1899 AND reca.status = '||''''||'APP'||''''||'
1900 ) ,
1901 amount_due_remaining = nvl(amount_due_original,0) - ( SELECT
1902 nvl(sum(amount_applied),0) tot_amt_applied
1903 FROM
1904 ar_receivable_applications_all reca
1905 WHERE
1906 reca.applied_payment_schedule_id = inv_arps.payment_schedule_id
1907 AND reca.applied_customer_trx_id = inv_arps.customer_trx_id
1908 AND reca.display = '||''''||'Y'||''''||'
1909 AND reca.status = '||''''||'APP'||''''||'
1910 ) ,
1911 last_update_date = sysdate,
1912 last_updated_by = '||gn_bug_no||'
1913 WHERE
1914 customer_trx_id = '||rec_get_cust_trx.customer_trx_id||';';
1915
1916
1917 jai_cmn_utils_pkg.print_log(lv_datafix_filename, lv_sql_statement);
1918
1919 lv_sql_statement := NULL;
1920
1921
1922 END IF;
1923
1924 END IF;
1925 END IF;
1926
1927 OPEN cur_inv_ar_recv_appl(rec_get_cust_trx.customer_trx_id);
1928 FETCH cur_inv_ar_recv_appl INTO rec_inv_ar_recv_appl;
1929 CLOSE cur_inv_ar_recv_appl;
1930
1931 ln_err_num := 42;
1932 --To check, if amount_credited = SUM(amount_applied) of ar_receivable_applications
1933 IF lt_error_table(ln_err_num).enable = 'Y' THEN
1934 IF ABS(rec_tot_payment_schedule.amount_credited) <> ABS(rec_inv_ar_recv_appl.amount_applied)
1935 THEN
1936 lv_process_status := jai_constants.expected_error;
1937 lv_process_message := lt_error_table(ln_err_num).error_description;
1938
1939 if lv_validate_first = 'Y' THEN
1940 goto EXIT_POINT;
1941 END IF;
1942
1943 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
1944
1945 If lv_generate_detail_log = 'Y' THEN
1946 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
1947 END IF;
1948
1949
1950 /*
1951 ||SET AC = SUM(AA) for the arps.payment_schedule_id = RECA.applied_payment_schedule_id AND ARPS.customer_trx_id = RECA.applied_customer_trx_id
1952 */
1953 IF lv_generate_log = 'Y' THEN
1954
1955 IF ln_error_cnt = 0 THEN
1956 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
1957 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
1958 END IF;
1959
1960 ln_error_cnt := 1;
1961
1962 UPDATE ar_payment_schedules_all inv_arps
1963 SET amount_credited = (SELECT
1967 WHERE
1964 (nvl(sum(amount_applied),0) * (-1) )tot_amt_applied
1965 FROM
1966 ar_receivable_applications_all reca
1968 reca.applied_payment_schedule_id = inv_arps.payment_schedule_id
1969 AND reca.applied_customer_trx_id = inv_arps.customer_trx_id
1970 AND reca.application_type = 'CM'
1971 AND reca.display = 'Y'
1972 AND reca.status = 'APP'
1973 ) ,
1974 last_update_date = sysdate,
1975 last_updated_by = gn_bug_no
1976 WHERE
1977 customer_trx_id = rec_get_cust_trx.customer_trx_id;
1978
1979
1980 lv_sql_statement := fnd_global.local_chr(10)||
1981 'UPDATE ar_payment_schedules_all inv_arps
1982 SET amount_credited = (SELECT
1983 (nvl(sum(amount_applied),0) * (-1) )tot_amt_applied
1984 FROM
1985 ar_receivable_applications_all reca
1986 WHERE
1987 reca.applied_payment_schedule_id = inv_arps.payment_schedule_id
1988 AND reca.applied_customer_trx_id = inv_arps.customer_trx_id
1989 AND reca.application_type = '||''''||'CM'||''''||'
1990 AND reca.display = '||''''||'Y'||''''||'
1991 AND reca.status = '||''''||'APP'||'
1992 ) ,
1993 last_update_date = sysdate,
1994 last_updated_by = '||gn_bug_no||'
1995 WHERE
1996 customer_trx_id = '||rec_get_cust_trx.customer_trx_id||';';
1997
1998 jai_cmn_utils_pkg.print_log(lv_datafix_filename, lv_sql_statement);
1999
2000 lv_sql_statement := NULL;
2001
2002
2003 END IF;
2004
2005 END IF;
2006 END IF;
2007 END IF;
2008
2009 --=================================================================================================================--
2010 --End of Invoice validations--
2011 --=================================================================================================================--
2012
2013 --=================================================================================================================--
2014 --Some more common validations--
2015 --=================================================================================================================--
2016
2017 OPEN jai_ar_validate_data_pkg.cur_curr_precision(rec_get_cust_trx.set_of_books_id);
2018 FETCH jai_ar_validate_data_pkg.cur_curr_precision INTO ln_precision;
2019 CLOSE jai_ar_validate_data_pkg.cur_curr_precision;
2020
2021 ln_local_cnt := 0;
2022 ln_err_num := 8;
2023 --To check if AADR = ADR * exchange_rate in ar_payment_schedules_all
2024 IF lt_error_table(ln_err_num).enable = 'Y' THEN
2025 FOR rec_payment_schedules IN cur_payment_schedules(rec_get_cust_trx.customer_trx_id)
2026 LOOP
2027 IF ROUND(rec_payment_schedules.acctd_amount_due_remaining)
2028 <> ROUND(rec_payment_schedules.amount_due_remaining * rec_payment_schedules.exchange_rate)
2029 THEN
2030 lv_process_status := jai_constants.expected_error;
2031 lv_process_message := lt_error_table(ln_err_num).error_description;
2032
2033 ln_local_cnt := ln_local_cnt + 1;
2034
2035 IF lv_validate_first = 'Y' THEN
2036 goto EXIT_POINT;
2037 END IF;
2038
2039 IF ln_local_cnt = 1 THEN
2040 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
2041
2042 If lv_generate_detail_log = 'Y' THEN
2043 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
2044 END IF;
2045 END IF;
2046
2047 IF lv_generate_log = 'Y' THEN
2048
2049 IF ln_error_cnt = 0 THEN
2050 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
2051 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
2052 END IF;
2053
2054 ln_error_cnt := 1;
2055
2056 UPDATE ar_payment_schedules_all
2057 SET acctd_amount_due_remaining = ROUND(rec_payment_schedules.amount_due_remaining * rec_payment_schedules.exchange_rate, ln_precision),
2058 last_update_date = sysdate,
2062
2059 last_updated_by = gn_bug_no
2060 WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id
2061 AND payment_schedule_id = rec_payment_schedules.payment_schedule_id;
2063 lv_sql_statement := fnd_global.local_chr(10)||
2064 'UPDATE ar_payment_schedules_all
2065 SET acctd_amount_due_remaining = ROUND('||rec_payment_schedules.amount_due_remaining||' * '||rec_payment_schedules.exchange_rate||', '||ln_precision||'),
2066 last_update_date = sysdate,
2067 last_updated_by = '||gn_bug_no||'
2068 WHERE customer_trx_id = '||rec_get_cust_trx.customer_trx_id||'
2069 AND payment_schedule_id = '||rec_payment_schedules.payment_schedule_id||';';
2070
2071
2072 jai_cmn_utils_pkg.print_log(lv_datafix_filename, lv_sql_statement);
2073
2074 lv_sql_statement := NULL;
2075
2076 END IF;
2077
2078 END IF;
2079
2080 END LOOP;
2081 END IF;
2082
2083 ln_local_cnt := 0;
2084 ln_err_num := 9;
2085
2086 IF lt_error_table(ln_err_num).enable = 'Y' THEN
2087 FOR rec_payment_schedules IN cur_payment_schedules(rec_get_cust_trx.customer_trx_id)
2088 LOOP
2089 --To check if status = 'CL' and AADR <> 0 and ADR <> 0
2090 IF rec_payment_schedules.status = 'CL' THEN
2091 IF ROUND(rec_payment_schedules.amount_due_remaining) <> 0 OR ROUND(rec_payment_schedules.acctd_amount_due_remaining) <> 0 THEN
2092 lv_process_status := jai_constants.expected_error;
2093 lv_process_message := lt_error_table(ln_err_num).error_description;
2094
2095 ln_local_cnt := ln_local_cnt + 1;
2096
2097 if lv_validate_first = 'Y' THEN
2098 goto EXIT_POINT;
2099 END IF;
2100
2101 IF ln_local_cnt = 1 THEN
2102 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
2103
2104 If lv_generate_detail_log = 'Y' THEN
2105 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
2106 END IF;
2107 END IF;
2108 /*
2109 ||set the staus = 'OP and gl_date_closed ='31-DEC-4712' and actual_date_closed '31-DEC-4712' as ADR and AADR
2110 ||have been set correctly
2111 */
2112
2113 IF lv_generate_log = 'Y' THEN
2114
2115 IF ln_error_cnt = 0 THEN
2116 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
2117 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
2118 END IF;
2119
2120 ln_error_cnt := 1;
2121
2122
2123 UPDATE ar_payment_schedules_all
2124 SET status = DECODE (amount_due_remaining, 0, 'CL', 'OP'),
2125 gl_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/YYYY')) ,
2126 actual_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/YYYY')),
2127 last_update_date = sysdate,
2128 last_updated_by = gn_bug_no
2129 WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id
2130 AND payment_schedule_id = rec_payment_schedules.payment_schedule_id;
2131
2132 lv_sql_statement := fnd_global.local_chr(10)||
2133 'UPDATE ar_payment_schedules_all
2134 SET status = DECODE (amount_due_remaining, 0, '||''''||'CL'||''''||', '||''''||'OP'||''''||'),
2135 gl_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('||''''||'31/12/4712'||''''||','||''''||'DD/MM/YYYY'||''''||')) ,
2136 actual_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('||''''||'31/12/4712'||''''||','||''''||'DD/MM/YYYY'||''''||')) ,
2137 last_update_date = sysdate,
2138 last_updated_by = '||gn_bug_no||'
2139 WHERE customer_trx_id = '||rec_get_cust_trx.customer_trx_id||'
2140 AND payment_schedule_id = '||rec_payment_schedules.payment_schedule_id||';';
2141
2142 jai_cmn_utils_pkg.print_log(lv_datafix_filename, lv_sql_statement);
2143
2144 lv_sql_statement := NULL;
2145
2146
2147 END IF;
2148
2149 END IF;
2150
2151 END IF;
2152
2153 END LOOP;
2154 END IF;
2155
2156 ln_local_cnt := 0;
2157 ln_err_num := 10;
2158
2159 IF lt_error_table(ln_err_num).enable = 'Y' THEN
2160 FOR rec_payment_schedules IN cur_payment_schedules(rec_get_cust_trx.customer_trx_id)
2161 LOOP
2162 --To Check if status = 'OP' and gl_date_closed = '31-DEC-4712'
2163 IF rec_payment_schedules.status = 'OP' THEN
2167
2164 IF rec_payment_schedules.gl_date_closed <> TO_DATE('31/12/4712','dd/mm/yyyy') THEN
2165 lv_process_status := jai_constants.expected_error;
2166 lv_process_message := lt_error_table(ln_err_num).error_description;
2168 ln_local_cnt := ln_local_cnt + 1;
2169
2170 if lv_validate_first = 'Y' THEN
2171 goto EXIT_POINT;
2172 END IF;
2173
2174 IF ln_local_cnt = 1 THEN
2175 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
2176
2177 If lv_generate_detail_log = 'Y' THEN
2178 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
2179 END IF;
2180 END IF;
2181
2182 /*
2183 ||set gl_date_closed = '31-dec-4712' and actual_date_closed = '31-dec-4712'
2184 */
2185
2186 IF lv_generate_log = 'Y' THEN
2187
2188 IF ln_error_cnt = 0 THEN
2189 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
2190 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
2191 END IF;
2192
2193 ln_error_cnt := 1;
2194
2195 UPDATE ar_payment_schedules_all
2196 SET status = DECODE (amount_due_remaining, 0, 'CL', 'OP'),
2197 gl_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/YYYY')) ,
2198 actual_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/YYYY')),
2199 last_update_date = sysdate,
2200 last_updated_by = gn_bug_no
2201 WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id
2202 AND payment_schedule_id = rec_payment_schedules.payment_schedule_id;
2203
2204
2205 lv_sql_statement := fnd_global.local_chr(10)||
2206 'UPDATE ar_payment_schedules_all
2207 SET status = DECODE (amount_due_remaining, 0, '||''''||'CL'||''''||', '||''''||'OP'||''''||'),
2208 gl_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('||''''||'31/12/4712'||''''||','||''''||'DD/MM/YYYY'||''''||')) ,
2209 actual_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('||''''||'31/12/4712'||''''||','||''''||'DD/MM/YYYY'||''''||')) ,
2210 last_update_date = sysdate,
2211 last_updated_by = '||gn_bug_no||'
2212 WHERE customer_trx_id = '||rec_get_cust_trx.customer_trx_id||'
2213 AND payment_schedule_id = '||rec_payment_schedules.payment_schedule_id||';';
2214
2215 jai_cmn_utils_pkg.print_log(lv_datafix_filename, lv_sql_statement);
2216
2217 lv_sql_statement := NULL;
2218
2219 END IF;
2220
2221 END IF;
2222
2223 END IF;
2224
2225 END LOOP;
2226 END IF;
2227
2228 ln_local_cnt := 0;
2229
2230 ln_err_num := 12;
2231
2232 IF lt_error_table(ln_err_num).enable = 'Y' THEN
2233 FOR rec_cur_sync_il_line_tax IN cur_sync_il_line_tax( cp_customer_trx_id => rec_get_cust_trx.customer_trx_id)
2234 LOOP
2235 lv_process_status := jai_constants.expected_error;
2236 lv_process_message := lt_error_table(ln_err_num).error_description;
2237
2238 ln_local_cnt := ln_local_cnt + 1;
2239
2240 IF lv_validate_first = 'Y' THEN
2241 goto EXIT_POINT;
2242 END IF;
2243
2244 IF ln_local_cnt = 1 THEN
2245 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
2246
2247 If lv_generate_detail_log = 'Y' THEN
2248 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
2249 END IF;
2250 END IF;
2251
2252 IF lv_generate_log = 'Y' THEN
2253
2254 IF ln_error_cnt = 0 THEN
2255 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
2256 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
2257 END IF;
2258
2259 ln_error_cnt := 1;
2260
2261
2262 UPDATE JAI_AR_TRX_LINES
2263 SET tax_amount = rec_cur_sync_il_line_tax.tax_amount ,
2264 total_amount = line_amount + rec_cur_sync_il_line_tax.tax_amount,
2265 last_update_date = sysdate,
2266 last_updated_by = gn_bug_no
2267 WHERE customer_trx_line_id = rec_cur_sync_il_line_tax.customer_trx_line_id;
2268
2269 lv_sql_statement := fnd_global.local_chr(10)||
2270 'UPDATE JAI_AR_TRX_LINES
2271 SET tax_amount = '||rec_cur_sync_il_line_tax.tax_amount||' ,
2272 total_amount = line_amount + '||rec_cur_sync_il_line_tax.tax_amount||',
2273 last_update_date = sysdate,
2274 last_updated_by = '||gn_bug_no||'
2275 WHERE customer_trx_line_id = '||rec_cur_sync_il_line_tax.customer_trx_line_id||';';
2276
2277 jai_cmn_utils_pkg.print_log(lv_datafix_filename, lv_sql_statement);
2278
2279 lv_sql_statement := NULL;
2280
2281 END IF;
2282
2283 END LOOP;
2284 END IF;
2285
2286 ln_local_cnt := 0;
2287
2288 ln_err_num := 13;
2289
2290 IF lt_error_table(ln_err_num).enable = 'Y' THEN
2291 FOR rec_sync_il_hdr_tax IN cur_sync_il_hdr_tax(cp_customer_trx_id => rec_get_cust_trx.customer_trx_id)
2292 LOOP
2293 lv_process_status := jai_constants.expected_error;
2294 lv_process_message := lt_error_table(ln_err_num).error_description;
2295
2296 ln_local_cnt := ln_local_cnt + 1;
2297
2298 IF lv_validate_first = 'Y' THEN
2299 goto EXIT_POINT;
2300 END IF;
2301
2302 IF ln_local_cnt = 1 THEN
2303 lt_error_table(ln_err_num).error_record_count := lt_error_table(ln_err_num).error_record_count + 1;
2304
2305 If lv_generate_detail_log = 'Y' THEN
2306 jai_cmn_utils_pkg.print_log(lv_log_filename, ' Encountered the error - '||lt_error_table(ln_err_num).error_description);
2307 END IF;
2308 END IF;
2309
2310 IF lv_generate_log = 'Y' THEN
2311
2312 IF ln_error_cnt = 0 THEN
2313 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
2314 jai_cmn_utils_pkg.print_log(lv_datafix_filename, '--Datafix for customer_trx_id : '||rec_get_cust_trx.customer_trx_id);
2315 END IF;
2316
2317 ln_error_cnt := 1;
2318
2319 UPDATE JAI_AR_TRXS
2320 SET tax_amount = rec_sync_il_hdr_tax.tax_amount ,
2321 total_amount = line_amount + rec_sync_il_hdr_tax.tax_amount,
2322 last_update_date = sysdate,
2323 last_updated_by = gn_bug_no
2324 WHERE CUSTOMER_TRX_ID = rec_sync_il_hdr_tax.customer_trx_id;
2325
2326 lv_sql_statement := fnd_global.local_chr(10)||
2327 'UPDATE JAI_AR_TRXS
2328 SET tax_amount = '||rec_sync_il_hdr_tax.tax_amount||' ,
2329 total_amount = line_amount + '||rec_sync_il_hdr_tax.tax_amount||',
2330 last_update_date = sysdate,
2331 last_updated_by = '||gn_bug_no||'
2332 WHERE CUSTOMER_TRX_ID = '||rec_sync_il_hdr_tax.customer_trx_id||';';
2333
2334 jai_cmn_utils_pkg.print_log(lv_datafix_filename, lv_sql_statement);
2335
2336 lv_sql_statement := NULL;
2337
2338 END IF;
2339
2340 END LOOP;
2341 END IF;
2342 --=================================================================================================================--
2343 --End of common validations--
2344 --=================================================================================================================--
2345 END LOOP; --Main cursor Loop
2346
2347 <<EXIT_POINT>>
2348
2349 IF lv_validate_all = 'Y' THEN
2350 IF lv_fix_data = 'Y' THEN
2351 IF lv_commit = 'Y' THEN
2352 COMMIT;
2353 ELSE
2354 NULL;
2355 END IF;
2356 ELSE
2357 ROLLBACK TO start_program;
2358 END IF;
2359 ELSE
2360 NULL;
2361 END IF;
2362
2363 IF lv_generate_log = 'Y' THEN
2364 display_error_summary(lt_error_table,
2365 ln_total_count,
2366 lv_log_filename,
2367 lv_process_status,
2368 lv_process_message);
2369 END IF;
2370
2371 EXCEPTION
2372 WHEN OTHERS THEN
2373 p_process_status := jai_constants.unexpected_error;
2374 p_process_message := SUBSTR(sqlerrm,1,300);
2375
2376 ROLLBACK to start_program;
2377
2378 jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
2379 jai_cmn_utils_pkg.print_log(lv_log_filename, 'Encountered an Oracle error : '||lv_process_message);
2380
2381 END post_validation;
2382
2383 END jai_ar_validate_data_pkg;