[Home] [Help]
775: AND TRUNC(aps.due_date) BETWEEN TRUNC(:v_agr_start_date)
776: AND TRUNC(:v_agr_end_date)
777: AND NOT EXISTS
778: (SELECT ''X''
779: FROM fun_net_ap_invs_all fnap,
780: fun_net_batches_all fnba
781: WHERE fnap.invoice_id = api.invoice_id
782: AND fnap.batch_id = fnba.batch_id
783: AND fnba.batch_status_code <> ''CANCELLED''
813: AND TRUNC(aps.due_date) BETWEEN TRUNC(:v_agr_start_date)
814: AND TRUNC(:v_agr_end_date)
815: AND NOT EXISTS
816: (SELECT ''X''
817: FROM fun_net_ap_invs_all fnap,
818: fun_net_batches_all fnba
819: WHERE fnap.invoice_id = api.invoice_id
820: AND fnap.batch_id = fnba.batch_id
821: AND fnba.batch_status_code <> ''CANCELLED''
1035: inv_rec InvCurTyp;
1036: TYPE amt_type IS TABLE OF fun_net_batches_all.total_netted_amt%TYPE INDEX BY BINARY_INTEGER;
1037: TYPE trx_type IS TABLE OF fun_net_ar_txns_all.customer_trx_id%TYPE INDEX BY BINARY_INTEGER;
1038: TYPE payment_num IS TABLE OF ap_payment_schedules_all.payment_num%TYPE INDEX BY BINARY_INTEGER;
1039: TYPE inv_rank IS TABLE OF fun_net_ap_invs_all.ap_txn_rank%TYPE INDEX BY BINARY_INTEGER;
1040: TYPE exchange_rate IS TABLE OF ap_invoices_all.exchange_rate%TYPE INDEX BY BINARY_INTEGER;
1041: TYPE precision_tab IS TABLE OF fnd_currencies.precision%TYPE INDEX BY BINARY_INTEGER;
1042: --l_invoice_id fun_net_ap_invs_all.invoice_id%TYPE;
1043: --l_invoice_amt fun_net_ap_invs_all.invoice_amt%TYPE;
1038: TYPE payment_num IS TABLE OF ap_payment_schedules_all.payment_num%TYPE INDEX BY BINARY_INTEGER;
1039: TYPE inv_rank IS TABLE OF fun_net_ap_invs_all.ap_txn_rank%TYPE INDEX BY BINARY_INTEGER;
1040: TYPE exchange_rate IS TABLE OF ap_invoices_all.exchange_rate%TYPE INDEX BY BINARY_INTEGER;
1041: TYPE precision_tab IS TABLE OF fnd_currencies.precision%TYPE INDEX BY BINARY_INTEGER;
1042: --l_invoice_id fun_net_ap_invs_all.invoice_id%TYPE;
1043: --l_invoice_amt fun_net_ap_invs_all.invoice_amt%TYPE;
1044: --l_open_amt fun_net_ap_invs_all.open_amt%TYPE;
1045: --l_inv_curr_open_amt fun_net_ap_invs_all.inv_curr_open_amt%TYPE;
1046: l_OPEN_AMOUNT_AFTERDISC amt_type; -- FOR ESD Logic
1039: TYPE inv_rank IS TABLE OF fun_net_ap_invs_all.ap_txn_rank%TYPE INDEX BY BINARY_INTEGER;
1040: TYPE exchange_rate IS TABLE OF ap_invoices_all.exchange_rate%TYPE INDEX BY BINARY_INTEGER;
1041: TYPE precision_tab IS TABLE OF fnd_currencies.precision%TYPE INDEX BY BINARY_INTEGER;
1042: --l_invoice_id fun_net_ap_invs_all.invoice_id%TYPE;
1043: --l_invoice_amt fun_net_ap_invs_all.invoice_amt%TYPE;
1044: --l_open_amt fun_net_ap_invs_all.open_amt%TYPE;
1045: --l_inv_curr_open_amt fun_net_ap_invs_all.inv_curr_open_amt%TYPE;
1046: l_OPEN_AMOUNT_AFTERDISC amt_type; -- FOR ESD Logic
1047: l_TC_OPEN_AMOUNT_AFTERDISC amt_type; -- FOR ESD Logic
1040: TYPE exchange_rate IS TABLE OF ap_invoices_all.exchange_rate%TYPE INDEX BY BINARY_INTEGER;
1041: TYPE precision_tab IS TABLE OF fnd_currencies.precision%TYPE INDEX BY BINARY_INTEGER;
1042: --l_invoice_id fun_net_ap_invs_all.invoice_id%TYPE;
1043: --l_invoice_amt fun_net_ap_invs_all.invoice_amt%TYPE;
1044: --l_open_amt fun_net_ap_invs_all.open_amt%TYPE;
1045: --l_inv_curr_open_amt fun_net_ap_invs_all.inv_curr_open_amt%TYPE;
1046: l_OPEN_AMOUNT_AFTERDISC amt_type; -- FOR ESD Logic
1047: l_TC_OPEN_AMOUNT_AFTERDISC amt_type; -- FOR ESD Logic
1048: l_invoice_id trx_type;
1041: TYPE precision_tab IS TABLE OF fnd_currencies.precision%TYPE INDEX BY BINARY_INTEGER;
1042: --l_invoice_id fun_net_ap_invs_all.invoice_id%TYPE;
1043: --l_invoice_amt fun_net_ap_invs_all.invoice_amt%TYPE;
1044: --l_open_amt fun_net_ap_invs_all.open_amt%TYPE;
1045: --l_inv_curr_open_amt fun_net_ap_invs_all.inv_curr_open_amt%TYPE;
1046: l_OPEN_AMOUNT_AFTERDISC amt_type; -- FOR ESD Logic
1047: l_TC_OPEN_AMOUNT_AFTERDISC amt_type; -- FOR ESD Logic
1048: l_invoice_id trx_type;
1049: l_invoice_amt amt_type;
1162: --EXIT WHEN inv_rec%NOTFOUND;
1163: --l_inv_rank(i) := i;
1164: --fun_net_util.Log_String(g_state_level,l_path,'Invoice ID:'||l_invoice_id);
1165: --fun_net_util.Log_String(g_state_level,l_path,'Fetching the AP invoices batch _id=');
1166: INSERT INTO fun_net_ap_invs_all
1167: (batch_id,
1168: invoice_id,
1169: object_version_number,
1170: ap_txn_rank,
1197: g_login_id);
1198: CLOSE inv_rec;
1199: Else
1200: FORALL i IN 1..l_invoice_id.COUNT
1201: INSERT INTO fun_net_ap_invs_all
1202: (batch_id,
1203: invoice_id,
1204: object_version_number,
1205: ap_txn_rank,
1304: -- l_inv_curr_open_amt;
1305: --EXIT WHEN inv_rec%NOTFOUND;
1306: --l_inv_rank := l_inv_rank + 1;
1307: --fun_net_util.Log_String(g_state_level,l_path,'Invoice ID:'||l_invoice_id);
1308: INSERT INTO fun_net_ap_invs_all
1309: (batch_id,
1310: invoice_id,
1311: object_version_number,
1312: ap_txn_rank,
1348: -- l_inv_curr_open_amt;
1349: --EXIT WHEN inv_rec%NOTFOUND;
1350: --l_inv_rank := l_inv_rank + 1;
1351: --fun_net_util.Log_String(g_state_level,l_path,'Invoice ID:'||l_invoice_id);
1352: INSERT INTO fun_net_ap_invs_all
1353: (batch_id,
1354: invoice_id,
1355: object_version_number,
1356: ap_txn_rank,
1822: END LOOP;
1823: -- UPDATING EITHER AP OR AR FUN TABLE WITH THE NETTED AMOUNT, INVOICE CURRENCY NETTED AMOUNT AND THE APPLIED DISCOUNT AMOUNT
1824: IF p_appln = 'AP' THEN
1825: FORALL i IN 1..l_trx_id.COUNT
1826: UPDATE fun_net_ap_invs_all
1827: SET netted_amt = l_net_amt(i),
1828: inv_curr_net_amt = l_inv_curr_net_amt(i),
1829: applied_disc = l_applieddisc(i)
1830: WHERE batch_id = g_batch_id
1843: fun_net_util.Log_String(g_state_level,l_path,sqlerrm);
1844: END update_net_balances_esd;
1845: PROCEDURE calculate_AP_AR_balances(p_amt_to_net OUT NOCOPY NUMBER,
1846: p_status_flag OUT NOCOPY VARCHAR2) IS
1847: l_ap_bal fun_net_ap_invs_all.open_amt%TYPE;
1848: l_ar_bal fun_net_ar_txns_all.open_amt%TYPE;
1849: l_amt_to_net fun_net_batches_all.total_netted_amt%TYPE;
1850: l_status_flag VARCHAR2(1);
1851: l_sql_stmt VARCHAR2(2000);
1862: SET netted_amt = open_amt,
1863: txn_curr_net_amt = txn_curr_open_amt
1864: WHERE batch_id = g_batch_id;
1865: /*Order the transactions by rank as the tnxs with a higher rank should be netted first */
1866: l_sql_stmt := 'SELECT open_amt,invoice_id,0,inv_curr_open_amt,0 FROM fun_net_ap_invs_all WHERE batch_id = :v_batch_id ORDER BY ap_txn_rank';
1867: update_net_balances(l_sql_stmt,l_amt_to_net,'AP');
1868: ELSIF l_ar_bal > l_ap_bal THEN
1869: fun_net_util.Log_String(g_state_level,l_path,'AR Balance > AP Balance');
1870: l_amt_to_net := l_ap_bal;
1868: ELSIF l_ar_bal > l_ap_bal THEN
1869: fun_net_util.Log_String(g_state_level,l_path,'AR Balance > AP Balance');
1870: l_amt_to_net := l_ap_bal;
1871: /* As the AP Bal = Total Net amount , update the net amount for each AP tnx with the open balance of that tnx */
1872: UPDATE fun_net_ap_invs_all
1873: SET netted_amt = open_amt,
1874: inv_curr_net_amt = inv_curr_open_amt
1875: WHERE batch_id = g_batch_id;
1876: l_sql_stmt := 'SELECT open_amt,customer_trx_id,0, txn_curr_open_amt,0 FROM fun_net_ar_txns_all WHERE batch_id = :v_batch_id ORDER BY ar_txn_rank';
1903: FROM fun_net_ar_txns_all
1904: WHERE batch_id = g_batch_id;
1905: CURSOR c_get_ap_open_amount IS
1906: SELECT SUM (OPEN_AMOUNT_AFTERDISC)
1907: FROM fun_net_ap_invs_all
1908: WHERE batch_id = g_batch_id;
1909: l_path varchar2(100);
1910: l_msg_data VARCHAR2(2000);
1911: BEGIN
1936: /* ------------------ ADDED NEWLY FOR ESD --------------------*/
1937: /* PROCEDURE FOR CALCULATING THE NETTED AMOUNT */
1938: PROCEDURE calculate_approx_netted_amount(p_amt_to_net OUT NOCOPY NUMBER,
1939: p_status_flag OUT NOCOPY VARCHAR2) IS
1940: l_ap_bal fun_net_ap_invs_all.open_amt%TYPE;
1941: l_ar_bal fun_net_ar_txns_all.open_amt%TYPE;
1942: l_amt_to_net fun_net_batches_all.total_netted_amt%TYPE;
1943: l_status_flag VARCHAR2(1);
1944: l_sql_stmt VARCHAR2(2000);
1955: txn_curr_net_amt = txn_curr_open_amount_afterdisc,
1956: applied_disc = open_amt - open_amount_afterdisc
1957: WHERE batch_id = g_batch_id;
1958: /*Order the transactions by rank as the tnxs with a higher rank should be netted first */
1959: l_sql_stmt := 'SELECT open_amt,invoice_id,0,inv_curr_open_amt,0,OPEN_AMOUNT_AFTERDISC,txn_curr_open_amount_afterdisc, 0 FROM fun_net_ap_invs_all WHERE batch_id = :v_batch_id ORDER BY ap_txn_rank';
1960: update_net_balances_esd(l_sql_stmt,l_amt_to_net,'AP');
1961: ELSIF l_ar_bal > l_ap_bal THEN
1962: fun_net_util.Log_String(g_state_level,l_path,'AR Balance > AP Balance');
1963: l_amt_to_net := l_ap_bal;
1960: update_net_balances_esd(l_sql_stmt,l_amt_to_net,'AP');
1961: ELSIF l_ar_bal > l_ap_bal THEN
1962: fun_net_util.Log_String(g_state_level,l_path,'AR Balance > AP Balance');
1963: l_amt_to_net := l_ap_bal;
1964: UPDATE fun_net_ap_invs_all
1965: SET netted_amt = open_amount_afterdisc,
1966: inv_curr_net_amt = txn_curr_open_amount_afterdisc,
1967: applied_disc = open_amt - open_amount_afterdisc
1968: WHERE batch_id = g_batch_id;
1991: FROM fun_net_ar_txns_all
1992: WHERE batch_id = g_batch_id;
1993: CURSOR c_get_ap_open_amount IS
1994: SELECT SUM (open_amt)
1995: FROM fun_net_ap_invs_all
1996: WHERE batch_id = g_batch_id;
1997: l_path varchar2(100);
1998: l_msg_data VARCHAR2(2000);
1999: BEGIN
2161: END batch_exists;
2162: PROCEDURE validate_exch_rate (p_status_flag OUT NOCOPY VARCHAR2) IS
2163: CURSOR trx_curr_cur IS
2164: SELECT INVOICE_CURRENCY_CODE
2165: FROM ap_invoices_all api, fun_net_ap_invs_all fnapi
2166: WHERE api.invoice_id = fnapi.invoice_id
2167: AND fnapi.batch_id = g_batch_id
2168: UNION
2169: SELECT INVOICE_CURRENCY_CODE
2291: END IF;
2292: END LOOP;
2293: IF p_appln = 'AP' THEN
2294: FORALL i IN 1..l_trx_id.COUNT
2295: UPDATE fun_net_ap_invs_all
2296: SET netted_amt = l_net_amt(i),
2297: inv_curr_net_amt = l_inv_curr_net_amt(i)
2298: WHERE batch_id = g_batch_id
2299: AND invoice_id = l_trx_id(i);
2659: x_msg_count OUT NOCOPY NUMBER,
2660: x_msg_data OUT NOCOPY VARCHAR2,
2661: -- ***** Netting batch input parameters *****
2662: p_batch_id IN NUMBER) IS
2663: l_ap_bal fun_net_ap_invs_all.open_amt%TYPE;
2664: l_ar_bal fun_net_ar_txns_all.open_amt%TYPE;
2665: l_status_flag VARCHAR2(1);
2666: l_TP_approver fun_net_agreements_all.approver_name%TYPE;
2667: l_return_status VARCHAR2(1);
3261: l_old_invoice ap_invoices.invoice_id%TYPE;
3262: amt_remaining ap_payment_schedules.amount_remaining%TYPE;
3263: l_bank_num ce_banks_v.bank_number%TYPE;
3264: -- Bug: 8537760
3265: l_amt_to_net fun_net_ap_invs.netted_amt%TYPE;
3266: l_applieddisc NUMBER;
3267: l_return_status VARCHAR2(1);
3268: m integer;
3269: l_path VARCHAR2(100);
3307: vendor_site_list,
3308: party_list,
3309: party_site_list,
3310: currency_list
3311: FROM fun_net_ap_invs finv,
3312: ap_invoices inv
3313: WHERE inv.invoice_id = finv.invoice_id
3314: AND finv.batch_id = g_batch_details.batch_id
3315: AND finv.inv_curr_net_amt <> 0
3404: BULK COLLECT INTO
3405: ap_payment_info_tab
3406: FROM
3407: ap_invoices inv,
3408: fun_net_ap_invs finv,
3409: ap_payment_schedules apps
3410: WHERE finv.invoice_id = inv.invoice_id
3411: AND apps.invoice_id = inv.invoice_id
3412: AND apps.checkrun_id = g_batch_details.checkrun_id
3430: SELECT amount_remaining
3431: BULK COLLECT INTO amtDueTab
3432: FROM
3433: ap_invoices inv,
3434: fun_net_ap_invs finv,
3435: ap_payment_schedules apps
3436: WHERE finv.invoice_id = inv.invoice_id
3437: AND apps.invoice_id = inv.invoice_id
3438: AND apps.checkrun_id = g_batch_details.checkrun_id
3528: IF l_check_id is null THEN
3529: x_return_status := FND_API.G_FALSE;
3530: RETURN;
3531: END IF;
3532: /* Update FUN_NET_AP_INVS all with the check Id */
3533: BEGIN
3534: FORALL k IN ap_invoice.FIRST..ap_invoice.LAST
3535: UPDATE FUN_NET_AP_INVS
3536: SET check_id = l_check_id
3531: END IF;
3532: /* Update FUN_NET_AP_INVS all with the check Id */
3533: BEGIN
3534: FORALL k IN ap_invoice.FIRST..ap_invoice.LAST
3535: UPDATE FUN_NET_AP_INVS
3536: SET check_id = l_check_id
3537: WHERE batch_id = g_batch_id
3538: AND inv_curr_net_amt <> 0
3539: AND invoice_id = ap_invoice(k);