[Home] [Help]
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';
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';
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';
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:
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'
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;
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'
599: --Get 1 if record has not been posted else null if posted
600: CURSOR cur_chk_gl_posting(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
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'
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
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'
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 ???
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')
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;
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
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)||
1156:
1157: /*
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
1161: SET amount = (SELECT nvl(sum(amount),0)
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'
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
1161: SET amount = (SELECT nvl(sum(amount),0)
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)
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,
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'||''''||
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)
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,
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;
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;
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;