[Home] [Help]
257: AND FNB.AGREEMENT_ID = FNA.AGREEMENT_ID;
258: fun_net_util.Log_String(g_state_level,l_path,'l_net_currency_rule_code:'|| l_net_currency_rule_code);
259: SELECT
260: DISTINCT ORG_ID INTO l_org_id
261: FROM FUN_NET_AR_TXNS_ALL
262: WHERE CUSTOMER_TRX_ID = p_cust_txn_id;
263: MO_GLOBAL.SET_POLICY_CONTEXT('S',l_org_id);
264: IF(l_net_currency_rule_code = 'ACCOUNTING_CURRENCY') THEN
265: l_amt_to_net := Derive_Conv_Amt(p_batch_id, p_cust_txn_id, l_amt_to_net, 'AR');
540: WHERE arm.receipt_method_id = rct.receipt_method_id
541: AND arm.payment_type_code = ''CREDIT_CARD'')
542: AND NOT EXISTS
543: (SELECT ''X''
544: FROM fun_net_ar_txns_all fnar,
545: fun_net_batches_all fnba
546: WHERE Fnar.customer_trx_id = rct.customer_trx_id
547: AND fnar.batch_id = fnba.batch_id
548: AND fnba.batch_status_code <> ''CANCELLED''
1033: --l_inv_rank NUMBER;
1034: TYPE InvCurTyp IS REF CURSOR;
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;
1056: l_precision precision_tab;
1057: l_rank NUMBER;
1058: l_pymt_sch_table pymt_sch_tab;
1059: l_return_status VARCHAR2(1);
1060: --l_customer_trx_id fun_net_ar_txns_all.customer_trx_id%TYPE;
1061: --l_transaction_amt fun_net_ar_txns_all.transaction_amt%TYPE;
1062: l_path varchar2(100);
1063: l_checkrun_id ap_inv_selection_criteria_all.checkrun_id%TYPE;
1064: BEGIN
1057: l_rank NUMBER;
1058: l_pymt_sch_table pymt_sch_tab;
1059: l_return_status VARCHAR2(1);
1060: --l_customer_trx_id fun_net_ar_txns_all.customer_trx_id%TYPE;
1061: --l_transaction_amt fun_net_ar_txns_all.transaction_amt%TYPE;
1062: l_path varchar2(100);
1063: l_checkrun_id ap_inv_selection_criteria_all.checkrun_id%TYPE;
1064: BEGIN
1065: l_path := g_path || 'Insert_Transactions';
1426: --fun_net_util.Log_String(g_state_level,l_path,'Transaction ID:'||l_customer_trx_id);
1427: IF l_allow_disc_flag='Y' THEN -- FOR ESD Enabled Agreements
1428: fun_net_util.Log_String(g_state_level,l_path,'Before Inserting');
1429: FORALL i IN 1..l_invoice_id.COUNT
1430: INSERT INTO fun_net_ar_txns_all
1431: (batch_id,
1432: customer_trx_id,
1433: object_version_number,
1434: ar_txn_rank,
1461: g_login_id);
1462: CLOSE inv_rec;
1463: ELSE -- FOR Non ESD Agreements
1464: FORALL i IN 1..l_invoice_id.COUNT
1465: INSERT INTO fun_net_ar_txns_all
1466: (batch_id,
1467: customer_trx_id,
1468: object_version_number,
1469: ar_txn_rank,
1561: -- fun_net_util.Log_String(g_state_level,l_path,'Transaction ID:'||l_customer_trx_id);
1562: IF l_allow_disc_flag='Y' THEN -- FOR ESD Enabled Agreements
1563: fun_net_util.Log_String(g_state_level,l_path,'Inside IF condition');
1564: FORALL i IN 1..l_invoice_id.COUNT
1565: INSERT INTO fun_net_ar_txns_all
1566: (batch_id,
1567: customer_trx_id,
1568: object_version_number,
1569: ar_txn_rank,
1596: g_login_id);
1597: CLOSE inv_rec;
1598: ELSE -- FOR NON ESD Agreements
1599: FORALL i IN 1..l_invoice_id.COUNT
1600: INSERT INTO fun_net_ar_txns_all
1601: (batch_id,
1602: customer_trx_id,
1603: object_version_number,
1604: ar_txn_rank,
1640: PROCEDURE update_net_balances_esd(p_sql_stmt VARCHAR2,
1641: p_amt_to_net NUMBER,
1642: p_appln VARCHAR2) IS
1643: TYPE amt_type IS TABLE OF fun_net_batches_all.total_netted_amt%TYPE index by pls_integer;
1644: TYPE trx_type IS TABLE OF fun_net_ar_txns_all.customer_trx_id%TYPE;
1645: l_open_amt_afterdisc amt_type;
1646: l_tc_open_amt_afterdisc amt_type;
1647: l_applieddisc amt_type;
1648: l_open_amt amt_type;
1830: WHERE batch_id = g_batch_id
1831: AND invoice_id = l_trx_id(i);
1832: ELSIF p_appln = 'AR' THEN
1833: FORALL i IN 1..l_trx_id.COUNT
1834: UPDATE fun_net_ar_txns_all
1835: SET netted_amt = l_net_amt(i),
1836: txn_curr_net_amt = l_inv_curr_net_amt(i),
1837: applied_disc = l_applieddisc(i)
1838: WHERE batch_id = g_batch_id
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);
1852: l_path varchar2(100);
1857: IF l_ap_bal >= l_ar_bal THEN
1858: fun_net_util.Log_String(g_state_level,l_path,'AP Balance > AR Balance');
1859: l_amt_to_net := l_ar_bal;
1860: /* As the Ar Bal = Total Net amount , update the net amount for each AR tnx with the open balance of that tnx */
1861: UPDATE fun_net_ar_txns_all
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 */
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';
1877: update_net_balances(l_sql_stmt,l_amt_to_net,'AR');
1878: END IF;
1879: fun_net_util.Log_String(g_state_level,l_path,'Total Netted Amount :'||l_amt_to_net);
1880: /*UPDATE fun_net_batches_all
1899: p_ap_bal OUT NOCOPY NUMBER,
1900: p_status_flag OUT NOCOPY VARCHAR2) IS
1901: CURSOR c_get_ar_open_amount IS
1902: SELECT SUM (OPEN_AMOUNT_AFTERDISC)
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
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);
1945: l_path varchar2(100);
1949: IF l_status_flag = FND_API.G_TRUE THEN
1950: IF l_ap_bal >= l_ar_bal THEN
1951: fun_net_util.Log_String(g_state_level,l_path,'AP Balance > AR Balance');
1952: l_amt_to_net := l_ar_bal;
1953: UPDATE fun_net_ar_txns_all
1954: SET netted_amt = open_amount_afterdisc,
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;
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;
1969: l_sql_stmt := 'SELECT open_amt,customer_trx_id,0, txn_curr_open_amt,0,OPEN_AMOUNT_AFTERDISC,txn_curr_open_amount_afterdisc, 0 FROM fun_net_ar_txns_all WHERE batch_id = :v_batch_id ORDER BY ar_txn_rank';
1970: update_net_balances_esd(l_sql_stmt,l_amt_to_net,'AR');
1971: END IF;
1972: fun_net_util.Log_String(g_state_level,l_path,'Total Netted Amount :'||l_amt_to_net);
1973: p_amt_to_net := l_amt_to_net;
1987: p_ap_bal OUT NOCOPY NUMBER,
1988: p_status_flag OUT NOCOPY VARCHAR2) IS
1989: CURSOR c_get_ar_open_amount IS
1990: SELECT SUM (open_amt)
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
2166: WHERE api.invoice_id = fnapi.invoice_id
2167: AND fnapi.batch_id = g_batch_id
2168: UNION
2169: SELECT INVOICE_CURRENCY_CODE
2170: FROM ra_customer_trx_all rct, fun_net_ar_txns_all fnart
2171: WHERE rct.customer_trx_id = fnart.customer_trx_id
2172: AND fnart.batch_id = g_batch_id;
2173: l_count NUMBER;
2174: l_exc_rate NUMBER;
2222: PROCEDURE update_net_balances(p_sql_stmt VARCHAR2,
2223: p_amt_to_net NUMBER,
2224: p_appln VARCHAR2) IS
2225: TYPE amt_type IS TABLE OF fun_net_batches_all.total_netted_amt%TYPE;
2226: TYPE trx_type IS TABLE OF fun_net_ar_txns_all.customer_trx_id%TYPE;
2227: l_open_amt amt_type;
2228: l_exc_rate NUMBER;
2229: l_net_amt amt_type;
2230: l_trx_id trx_type;
2298: WHERE batch_id = g_batch_id
2299: AND invoice_id = l_trx_id(i);
2300: ELSIF p_appln = 'AR' THEN
2301: FORALL i IN 1..l_trx_id.COUNT
2302: UPDATE fun_net_ar_txns_all
2303: SET netted_amt = l_net_amt(i),
2304: txn_curr_net_amt = l_inv_curr_net_amt(i)
2305: WHERE batch_id = g_batch_id
2306: AND customer_trx_id = l_trx_id(i);
2594: end if;
2595: elsif (g_net_currency_rule = 'WITHIN_CURRENCY') then
2596: SELECT count (DISTINCT rac.invoice_currency_code)
2597: into l_count
2598: FROM ra_customer_trx_all rac,fun_net_ar_txns_all fnar
2599: WHERE rac.customer_trx_id = fnar.customer_trx_id
2600: AND fnar.batch_id = g_batch_id
2601: AND rac.invoice_currency_code <> g_func_currency;
2602: end if;
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);
2668: l_msg_count NUMBER;
4147: IF get_esd_flag(g_batch_details.batch_id)='Y' THEN
4148: -- ADDED FOR ESD
4149: BEGIN
4150: SELECT Nvl(APPLIED_DISC,0) INTO l_discount_amount
4151: FROM FUN_NET_AR_TXNS_ALL
4152: WHERE batch_id=g_batch_details.batch_id
4153: AND CUSTOMER_TRX_ID=txnTable(i).customer_trx_id;
4154: l_discount_amount:=round(l_discount_amount * Derive_Net_Exchg_Rate(txnTable(i).invoice_currency_code,g_func_currency), l_precision); -- converting the discount amount to invoice currecny.
4155: SELECT trx_line.customer_trx_line_id AS cust_trx_line_id,