[Home] [Help]
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';
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';
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';
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';
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
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||'
372: lv_sql_statement := NULL;
373:
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
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||'
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
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||'
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';
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';
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';
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';
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))
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
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||'
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;
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
1706: AND display = 'Y'
1707: AND status = 'APP';
1708:
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||'
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;
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
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||'
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'
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'
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'
1880: 'UPDATE ar_payment_schedules_all inv_arps
1881: SET tax_remaining = nvl(tax_original,0) - ( SELECT
1882: nvl(sum(tax_applied),0) tot_tax_applied
1883: FROM
1884: ar_receivable_applications_all reca
1885: WHERE
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'||''''||'
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'||''''||'
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'||''''||'
1962: UPDATE ar_payment_schedules_all inv_arps
1963: SET amount_credited = (SELECT
1964: (nvl(sum(amount_applied),0) * (-1) )tot_amt_applied
1965: FROM
1966: ar_receivable_applications_all reca
1967: WHERE
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'
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'||''''||'