[Home] [Help]
738: l_path VARCHAR2(100);
739: BEGIN
740: l_path := g_path || 'lock_ap_pymt_schedules';
741: fun_net_util.Log_String(g_state_level,l_path,'Start of locking');
742: x_return_status := FND_API.G_TRUE;
743: fun_net_util.Log_String(g_state_level,l_path,'p_schd_tab.count :'||p_schd_tab.count);
744: fun_net_util.Log_String(g_state_level,l_path,'p_batch_id :'||p_batch_id);
745: -- Check for Mandatory Parameters
746: IF p_schd_tab.count = 0 OR p_batch_id IS NULL THEN
743: fun_net_util.Log_String(g_state_level,l_path,'p_schd_tab.count :'||p_schd_tab.count);
744: fun_net_util.Log_String(g_state_level,l_path,'p_batch_id :'||p_batch_id);
745: -- Check for Mandatory Parameters
746: IF p_schd_tab.count = 0 OR p_batch_id IS NULL THEN
747: x_return_status := FND_API.G_FALSE;
748: RETURN;
749: END IF;
750:
751: select checkrun_id
771:
772: WHEN OTHERS THEN
773:
774: fun_net_util.Log_String(g_state_level,l_path,'Failure in locking ap payment schedules');
775: x_return_status := FND_API.G_FALSE;
776: END lock_ap_pymt_schedules;
777:
778: PROCEDURE insert_transactions(p_inv_cur VARCHAR2,p_currency_code VARCHAR2, p_appln VARCHAR2) IS
779: l_batch_exists VARCHAR2(1);
1148: BEGIN
1149: l_path := g_path || 'Calculate_AP_AR_Balances';
1150:
1151: validate_AP_AR_balances(l_ar_bal, l_ap_bal, l_status_flag);
1152: IF l_status_flag = FND_API.G_TRUE THEN
1153: IF l_ap_bal >= l_ar_bal THEN
1154: fun_net_util.Log_String(g_state_level,l_path,'AP Balance > AR Balance');
1155: l_amt_to_net := l_ar_bal;
1156: /* As the Ar Bal = Total Net amount , update the net amount for each AR tnx with the open balance of that tnx */
1179: /*UPDATE fun_net_batches_all
1180: SET total_netted_amt = l_amt_to_net
1181: WHERE batch_id = g_batch_id; */
1182: p_amt_to_net := l_amt_to_net;
1183: p_status_flag := FND_API.G_TRUE;
1184: ELSE
1185: /*Unlock AP and AR Transactions that have been locked */
1186:
1187: fun_net_util.Log_String(g_state_level,l_path,'validation of ap and ar balances failed.Some transactions in AP and AR might have to be unlocked manually');
1184: ELSE
1185: /*Unlock AP and AR Transactions that have been locked */
1186:
1187: fun_net_util.Log_String(g_state_level,l_path,'validation of ap and ar balances failed.Some transactions in AP and AR might have to be unlocked manually');
1188: p_status_flag := FND_API.G_FALSE;
1189: END IF;
1190:
1191: EXCEPTION
1192: WHEN NO_DATA_FOUND then
1211: l_path varchar2(100);
1212: BEGIN
1213: l_path := g_path || 'Validate_AP_AR_Balances';
1214:
1215: p_status_flag := FND_API.G_TRUE;
1216:
1217: OPEN c_get_ar_open_amount;
1218: FETCH c_get_ar_open_amount INTO p_ar_bal;
1219: CLOSE c_get_ar_open_amount;
1231: /*UPDATE fun_net_batches_all
1232: SET batch_status_code = 'ERROR'
1233: WHERE batch_id = g_batch_id; */
1234:
1235: p_status_flag := FND_API.G_FALSE;
1236: /*IF NOT update_Batch_Status('ERROR') THEN
1237: p_status_flag := FND_API.G_FALSE;
1238: END IF; */
1239:
1233: WHERE batch_id = g_batch_id; */
1234:
1235: p_status_flag := FND_API.G_FALSE;
1236: /*IF NOT update_Batch_Status('ERROR') THEN
1237: p_status_flag := FND_API.G_FALSE;
1238: END IF; */
1239:
1240: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_BALANCES');
1241:
1413: BEGIN
1414:
1415: l_path := g_path || 'validate_exch_rate';
1416: l_count := 0;
1417: p_status_flag := FND_API.G_TRUE;
1418:
1419: SELECT USER_CONVERSION_TYPE
1420: INTO l_conv_rate
1421: FROM GL_DAILY_CONVERSION_TYPES
1464:
1465: END IF;
1466:
1467: IF l_exc_rate is NULL THEN
1468: p_status_flag := FND_API.G_FALSE;
1469: END IF;
1470:
1471: END LOOP;
1472: fun_net_util.Log_String(g_state_level,l_path,' p_status_flag :'
1613: END calculate_ar_trx_amt;
1614:
1615: PROCEDURE create_net_batch(
1616: -- ***** Standard API Parameters *****
1617: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1618: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1619: x_return_status OUT NOCOPY VARCHAR2,
1620: x_msg_count OUT NOCOPY NUMBER,
1621: x_msg_data OUT NOCOPY VARCHAR2,
1614:
1615: PROCEDURE create_net_batch(
1616: -- ***** Standard API Parameters *****
1617: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1618: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1619: x_return_status OUT NOCOPY VARCHAR2,
1620: x_msg_count OUT NOCOPY NUMBER,
1621: x_msg_data OUT NOCOPY VARCHAR2,
1622: -- ***** Netting batch input parameters *****
1662: -- **** Standard start of API savepoint ****
1663: SAVEPOINT create_net_batch_SP;
1664:
1665: -- **** Initialize message list if p_init_msg_list is set to TRUE. ****
1666: IF FND_API.to_Boolean( p_init_msg_list ) THEN
1667: FND_MSG_PUB.initialize;
1668: END IF;
1669:
1670: -- **** Initialize return status to SUCCESS *****
1667: FND_MSG_PUB.initialize;
1668: END IF;
1669:
1670: -- **** Initialize return status to SUCCESS *****
1671: x_return_status := FND_API.G_RET_STS_SUCCESS;
1672:
1673: /*-----------------------------------------------+
1674: | ======== START OF API BODY ============ |
1675: +-----------------------------------------------*/
1676:
1677: /* Check for mandatory parameters */
1678:
1679: IF p_batch_id IS NULL THEN
1680: RAISE FND_API.G_EXC_ERROR;
1681: END IF;
1682:
1683: g_batch_id := p_batch_id;
1684:
1685: --Call the procedure to get the batch details
1686: fun_net_util.Log_String(g_state_level,l_path,'Fetching batch details');
1687: IF NOT get_batch_details THEN
1688: fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching batch details');
1689: RAISE FND_API.G_EXC_ERROR;
1690: END IF;
1691:
1692: --If the call is successful then call the procedure to update batch status
1693: IF NOT update_batch_status('RUNNING') THEN
1691:
1692: --If the call is successful then call the procedure to update batch status
1693: IF NOT update_batch_status('RUNNING') THEN
1694: fun_net_util.Log_String(g_state_level,l_path,'Error in updating batch status');
1695: RAISE FND_API.G_EXC_ERROR;
1696: END IF; -- Return Code
1697:
1698: --If the call is successful then call the procedure to get all agreement details. If agreement id is null , then loop through each agreement and select tnx for every agreement.
1699: -- If the call is successful then get the agreement details and select transactions for every agreement
1740: --g_currency_count := 0;
1741: --If the call is successful then call the procedure to select all customer transactions --and insert them into the customer transactions table
1742: IF NOT prepare_ar_transactions THEN
1743: fun_net_util.Log_String(g_state_level,l_path,'Error in prepare AR Transactions');
1744: RAISE FND_API.G_EXC_ERROR;
1745: END IF;
1746:
1747: --If the call is successful then call the procedure to select all supplier transactions and insert them into the supplier invoices table.
1748: IF NOT prepare_ap_transactions THEN
1746:
1747: --If the call is successful then call the procedure to select all supplier transactions and insert them into the supplier invoices table.
1748: IF NOT prepare_ap_transactions THEN
1749: fun_net_util.Log_String(g_state_level,l_path,'Error in prepare AP transactions');
1750: RAISE FND_API.G_EXC_ERROR;
1751: END IF;
1752:
1753: END LOOP;
1754:
1757: LOOP
1758: g_batch_id := g_batch_list(i).batch_id;
1759: IF NOT get_batch_details THEN
1760: fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching batch details');
1761: RAISE FND_API.G_EXC_ERROR;
1762: END IF;
1763:
1764: g_agreement_id := g_batch_list(i).agreement_id;
1765: IF NOT get_agreement_details THEN
1763:
1764: g_agreement_id := g_batch_list(i).agreement_id;
1765: IF NOT get_agreement_details THEN
1766: fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching agreement details');
1767: RAISE FND_API.G_EXC_ERROR;
1768: END IF;
1769:
1770: fun_net_util.Log_String(g_state_level,l_path,'Batch ID:'||g_batch_id);
1771:
1773:
1774: validate_exch_rate(l_return_status);
1775:
1776:
1777: IF l_return_status = FND_API.G_FALSE THEN
1778:
1779: fun_net_util.Log_String(g_event_level,l_path, 'validate_exch_rate returns false');
1780: RAISE FND_API.G_EXC_ERROR;
1781:
1776:
1777: IF l_return_status = FND_API.G_FALSE THEN
1778:
1779: fun_net_util.Log_String(g_event_level,l_path, 'validate_exch_rate returns false');
1780: RAISE FND_API.G_EXC_ERROR;
1781:
1782: END IF;
1783:
1784:
1784:
1785: calculate_AP_AR_balances(l_amt_to_net,l_status_flag);
1786:
1787: /* Set the status of the Batch to Selected */
1788: IF l_status_flag = FND_API.G_TRUE THEN
1789: UPDATE fun_net_batches_all
1790: SET batch_status_code = 'SELECTED',
1791: batch_currency = g_batch_list(i).currency,
1792: agreement_id = g_batch_list(i).agreement_id,
1813: IF g_batch_details.review_netting_batch_flag = 'Y'
1814: AND g_batch_details.batch_status_code = 'SELECTED' THEN
1815: fun_net_util.Log_String(g_state_level,l_path,'Submitting Netting Batch');
1816: submit_net_batch (
1817: p_init_msg_list => FND_API.G_FALSE,
1818: p_commit => FND_API.G_FALSE,
1819: x_return_status => l_return_status,
1820: x_msg_count => l_msg_count,
1821: x_msg_data => l_msg_data,
1814: AND g_batch_details.batch_status_code = 'SELECTED' THEN
1815: fun_net_util.Log_String(g_state_level,l_path,'Submitting Netting Batch');
1816: submit_net_batch (
1817: p_init_msg_list => FND_API.G_FALSE,
1818: p_commit => FND_API.G_FALSE,
1819: x_return_status => l_return_status,
1820: x_msg_count => l_msg_count,
1821: x_msg_data => l_msg_data,
1822: p_batch_id => g_batch_id);
1824: END LOOP;
1825:
1826: -- Standard check of p_commit.
1827: fun_net_util.Log_String(g_state_level,l_path,'p_commit :'|| p_commit);
1828: IF FND_API.To_Boolean( p_commit ) THEN
1829: fun_net_util.Log_String(g_state_level,l_path,'Saving the batches');
1830: COMMIT WORK;
1831: END IF;
1832:
1830: COMMIT WORK;
1831: END IF;
1832:
1833: EXCEPTION
1834: WHEN FND_API.G_EXC_ERROR THEN
1835:
1836: fun_net_util.Log_String(g_state_level,l_path,'netting batch creation failed');
1837: ROLLBACK TO create_net_batch_SP;
1838: batch_status_flag := update_batch_status('ERROR');
1835:
1836: fun_net_util.Log_String(g_state_level,l_path,'netting batch creation failed');
1837: ROLLBACK TO create_net_batch_SP;
1838: batch_status_flag := update_batch_status('ERROR');
1839: x_return_status := FND_API.G_RET_STS_ERROR;
1840: FND_MSG_PUB.Count_And_Get (
1841: p_count => x_msg_count,
1842: p_data => x_msg_data );
1843:
1840: FND_MSG_PUB.Count_And_Get (
1841: p_count => x_msg_count,
1842: p_data => x_msg_data );
1843:
1844: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1845:
1846: fun_net_util.Log_String(g_state_level,l_path,'netting batch creation failed');
1847: ROLLBACK TO create_net_batch_SP;
1848: batch_status_flag := update_batch_status('ERROR');
1845:
1846: fun_net_util.Log_String(g_state_level,l_path,'netting batch creation failed');
1847: ROLLBACK TO create_net_batch_SP;
1848: batch_status_flag := update_batch_status('ERROR');
1849: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1850: FND_MSG_PUB.Count_And_Get (
1851: p_count => x_msg_count,
1852: p_data => x_msg_data );
1853:
1855:
1856: fun_net_util.Log_String(g_state_level,l_path,'netting batch creation failed');
1857: ROLLBACK TO Create_Net_Batch_SP;
1858: batch_status_flag := update_batch_status('ERROR');
1859: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1860: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1861: --FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
1862: FND_MSG_PUB.Add_Exc_Msg( 'FUN_ARAP_NET_PKG', 'create_net_batch');
1863: END IF;
1887: from fun_net_batches_all
1888: where batch_id = g_batch_id;
1889: exception
1890: when others then
1891: RAISE FND_API.G_EXC_ERROR;
1892: end;
1893:
1894: if l_batch_currency<> g_func_currency then
1895: l_count:=1;
1919: and rm.receipt_method_id = rma.receipt_method_id
1920: and rma.remit_bank_acct_use_id = ba.bank_acct_use_id;
1921: exception
1922: when others then
1923: RAISE FND_API.G_EXC_ERROR;
1924: end;
1925: if ( l_flag <>'Y' ) then
1926: FND_MESSAGE.SET_NAME('FUN','FUN_NET_MULTI_CUR_FLAG_ERR');
1927: RAISE FND_API.G_EXC_ERROR;
1923: RAISE FND_API.G_EXC_ERROR;
1924: end;
1925: if ( l_flag <>'Y' ) then
1926: FND_MESSAGE.SET_NAME('FUN','FUN_NET_MULTI_CUR_FLAG_ERR');
1927: RAISE FND_API.G_EXC_ERROR;
1928: end if;
1929:
1930: end if;
1931:
1937:
1938:
1939: PROCEDURE submit_net_batch (
1940: -- ***** Standard API Parameters *****
1941: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1942: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1943: x_return_status OUT NOCOPY VARCHAR2,
1944: x_msg_count OUT NOCOPY NUMBER,
1945: x_msg_data OUT NOCOPY VARCHAR2,
1938:
1939: PROCEDURE submit_net_batch (
1940: -- ***** Standard API Parameters *****
1941: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1942: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1943: x_return_status OUT NOCOPY VARCHAR2,
1944: x_msg_count OUT NOCOPY NUMBER,
1945: x_msg_data OUT NOCOPY VARCHAR2,
1946: -- ***** Netting batch input parameters *****
1971: x_msg_count := NULL;
1972: x_msg_data := NULL;
1973:
1974: -- **** Initialize message list if p_init_msg_list is set to TRUE. ****
1975: IF FND_API.to_Boolean( p_init_msg_list ) THEN
1976: FND_MSG_PUB.initialize;
1977: END IF;
1978:
1979: -- **** Initialize return status to SUCCESS *****
1976: FND_MSG_PUB.initialize;
1977: END IF;
1978:
1979: -- **** Initialize return status to SUCCESS *****
1980: x_return_status := FND_API.G_RET_STS_SUCCESS;
1981:
1982: /* Create Save point */
1983: SAVEPOINT submit_net_batch_SP;
1984:
1986: /* Get Batch Details for the given batch id*/
1987: IF p_batch_id IS NULL THEN
1988:
1989: fun_net_util.Log_String(g_state_level,l_path,'Batch id passed to submit netting batch procedure is null');
1990: RAISE FND_API.G_EXC_ERROR;
1991: END IF;
1992:
1993: g_batch_id := p_batch_id;
1994: IF NOT get_batch_details THEN
1993: g_batch_id := p_batch_id;
1994: IF NOT get_batch_details THEN
1995:
1996: fun_net_util.Log_String(g_state_level,l_path,'Unable to get batch details for the netting batch with batch id :'||g_batch_id);
1997: RAISE FND_API.G_EXC_ERROR;
1998: END IF;
1999:
2000: fun_net_util.Log_String(g_state_level,l_path,'Validating batch');
2001: /* Compares Final AP and AR Balances. Need to check the balance as the batch might have been modified by the user */
2004: IF NOT get_agreement_details THEN
2005:
2006: fun_net_util.Log_String(g_event_level,l_path,
2007: 'Error getting Agreement details');
2008: RAISE FND_API.G_EXC_ERROR;
2009: END IF;
2010:
2011:
2012: Validate_multi_currency_flag;
2016: -- Check for negative AP or AR balances
2017:
2018: IF l_ar_bal < 0 or l_ap_bal < 0 THEN
2019: FND_MESSAGE.SET_NAME('FUN','FUN_NET_BATCH_NEG_AP_AR_BAL');
2020: RAISE FND_API.G_EXC_ERROR;
2021: END IF;
2022:
2023: IF g_net_balance_rule='NET_PAYABLES' and l_ar_bal > l_ap_bal THEN
2024:
2022:
2023: IF g_net_balance_rule='NET_PAYABLES' and l_ar_bal > l_ap_bal THEN
2024:
2025: FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_BAL_LESS_THAN_AP');
2026: RAISE FND_API.G_EXC_ERROR;
2027:
2028: END IF;
2029:
2030:
2028: END IF;
2029:
2030:
2031:
2032: IF l_status_flag = FND_API.G_TRUE then
2033: /*Check for Trading Partner Approval and set status to Submitted if TP approval is required*/
2034: IF g_batch_details.settlement_date < TRUNC(SYSDATE) THEN
2035: fun_net_util.Log_String(g_state_level,l_path,'Setting status to SUSPENDED');
2036: UPDATE fun_net_batches
2054: END IF;
2055: ELSIF g_batch_details.batch_status_code IN('SELECTED','ERROR') THEN /* TP Approval is not necessary */
2056: /* If TP approval is not necessary call Netting Settlement Date API */
2057: /*l_batch_status := validate_netting_dates(
2058: p_init_msg_list => FND_API.G_FALSE,
2059: p_commit => FND_API.G_FALSE,
2060: x_return_status => l_return_status,
2061: x_msg_count => l_msg_count,
2062: x_msg_data => l_msg_data,
2055: ELSIF g_batch_details.batch_status_code IN('SELECTED','ERROR') THEN /* TP Approval is not necessary */
2056: /* If TP approval is not necessary call Netting Settlement Date API */
2057: /*l_batch_status := validate_netting_dates(
2058: p_init_msg_list => FND_API.G_FALSE,
2059: p_commit => FND_API.G_FALSE,
2060: x_return_status => l_return_status,
2061: x_msg_count => l_msg_count,
2062: x_msg_data => l_msg_data,
2063: p_batch_id => g_batch_id,
2062: x_msg_data => l_msg_data,
2063: p_batch_id => g_batch_id,
2064: p_net_settle_date => g_batch_details.settlement_date,
2065: p_response_date => NULL);
2066: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2067:
2068: g_batch_details.batch_status_code := l_batch_status;
2069: END IF; */
2070: UPDATE fun_net_batches
2075:
2076: END IF;
2077: END IF;
2078: ELSE
2079: RAISE FND_API.G_EXC_ERROR;
2080: END IF;
2081:
2082: IF g_batch_details.batch_status_code = 'APPROVED' then
2083: UPDATE fun_net_batches
2097: fun_net_util.Log_String(g_state_level,l_path,'Settle batch request:'||l_request_id);
2098: END IF;
2099:
2100: -- Standard check of p_commit.
2101: IF FND_API.To_Boolean( p_commit ) THEN
2102: COMMIT WORK;
2103: END IF;
2104:
2105: EXCEPTION
2102: COMMIT WORK;
2103: END IF;
2104:
2105: EXCEPTION
2106: WHEN FND_API.G_EXC_ERROR THEN
2107: ROLLBACK TO submit_net_batch_SP;
2108: l_batch_status_flag := update_batch_status('ERROR');
2109: x_return_status := FND_API.G_RET_STS_ERROR;
2110: FND_MSG_PUB.Count_And_Get (
2105: EXCEPTION
2106: WHEN FND_API.G_EXC_ERROR THEN
2107: ROLLBACK TO submit_net_batch_SP;
2108: l_batch_status_flag := update_batch_status('ERROR');
2109: x_return_status := FND_API.G_RET_STS_ERROR;
2110: FND_MSG_PUB.Count_And_Get (
2111: p_count => x_msg_count,
2112: p_data => x_msg_data );
2113:
2110: FND_MSG_PUB.Count_And_Get (
2111: p_count => x_msg_count,
2112: p_data => x_msg_data );
2113:
2114: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2115: ROLLBACK TO submit_net_batch_SP;
2116: l_batch_status_flag := update_batch_status('ERROR');
2117: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2118: FND_MSG_PUB.Count_And_Get (
2113:
2114: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2115: ROLLBACK TO submit_net_batch_SP;
2116: l_batch_status_flag := update_batch_status('ERROR');
2117: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2118: FND_MSG_PUB.Count_And_Get (
2119: p_count => x_msg_count,
2120: p_data => x_msg_data );
2121:
2121:
2122: WHEN OTHERS THEN
2123: ROLLBACK TO submit_Net_Batch_SP;
2124: l_batch_status_flag := update_batch_status('ERROR');
2125: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2126: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2127: --FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
2128: FND_MSG_PUB.Add_Exc_Msg( 'FUN_ARAP_NET_PKG', 'submit_net_batch');
2129: END IF;
2134:
2135:
2136: FUNCTION Validate_Netting_Dates(
2137: -- ***** Standard API Parameters *****
2138: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2139: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2140: x_return_status OUT NOCOPY VARCHAR2,
2141: x_msg_count OUT NOCOPY NUMBER,
2142: x_msg_data OUT NOCOPY VARCHAR2,
2135:
2136: FUNCTION Validate_Netting_Dates(
2137: -- ***** Standard API Parameters *****
2138: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2139: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2140: x_return_status OUT NOCOPY VARCHAR2,
2141: x_msg_count OUT NOCOPY NUMBER,
2142: x_msg_data OUT NOCOPY VARCHAR2,
2143: -- ***** Netting batch input parameters *****
2160: x_msg_count := NULL;
2161: x_msg_data := NULL;
2162:
2163: -- **** Initialize message list if p_init_msg_list is set to TRUE. ****
2164: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2165: FND_MSG_PUB.initialize;
2166: END IF;
2167:
2168: -- **** Initialize return status to SUCCESS *****
2165: FND_MSG_PUB.initialize;
2166: END IF;
2167:
2168: -- **** Initialize return status to SUCCESS *****
2169: x_return_status := FND_API.G_RET_STS_SUCCESS;
2170:
2171: /* Create Save point */
2172: SAVEPOINT validate_netting_dates_SP;
2173:
2175: /* Get Batch Details for the given batch id*/
2176: IF p_batch_id IS NULL OR (p_net_settle_date IS NULL AND p_response_date IS NULL) THEN
2177:
2178: fun_net_util.Log_String(g_state_level,l_path,'One of the mandatory arguments passed to the validate_netting_dates procedure is null');
2179: RAISE FND_API.G_EXC_ERROR;
2180: END IF;
2181:
2182: g_batch_id := p_batch_id;
2183: /* Validate NSD with the date of
2189:
2190: fun_net_util.Log_String(g_state_level,l_path,'The netting batch with batch_id:'||g_batch_id||'has gone past the netting settlement date');
2191: --Error ' Batch has gone past the Netting Settlement Date';
2192: IF NOT Update_batch_status('SUSPENDED') THEN
2193: RAISE FND_API.G_EXC_ERROR;
2194: ELSE
2195: RETURN 'SUSPENDED';
2196: END IF;
2197: ELSE
2196: END IF;
2197: ELSE
2198: IF NOT update_batch_status ('APPROVED') THEN
2199: fun_net_util.Log_String(g_state_level,l_path,'Unable to update the batch status to approved');
2200: RAISE FND_API.G_EXC_ERROR;
2201: ELSE
2202: RETURN 'APPROVED';
2203: END IF;
2204: END IF;
2212: FETCH c_non_response_action INTO l_non_response_action;
2213: CLOSE c_non_response_action;
2214:
2215: IF l_non_response_action IS NULL THEN
2216: RAISE FND_API.G_EXC_ERROR;
2217: END IF;
2218:
2219: IF TRUNC(p_response_date) < TRUNC(SYSDATE) THEN
2220: IF l_non_response_action = 'APPROVE' then
2218:
2219: IF TRUNC(p_response_date) < TRUNC(SYSDATE) THEN
2220: IF l_non_response_action = 'APPROVE' then
2221: IF NOT Update_batch_status('APPROVED') THEN
2222: RAISE FND_API.G_EXC_ERROR;
2223: END IF;
2224: RETURN 'APPROVED';
2225: ELSIF l_non_response_action = 'REJECT' THEN
2226: IF NOT Update_batch_status('REJECTED') THEN
2223: END IF;
2224: RETURN 'APPROVED';
2225: ELSIF l_non_response_action = 'REJECT' THEN
2226: IF NOT Update_batch_status('REJECTED') THEN
2227: RAISE FND_API.G_EXC_ERROR;
2228: END IF;
2229: RETURN 'REJECTED';
2230: END IF;
2231: END IF;
2231: END IF;
2232: END IF;
2233:
2234: -- Standard check of p_commit.
2235: IF FND_API.To_Boolean( p_commit ) THEN
2236: COMMIT WORK;
2237: END IF;
2238:
2239: EXCEPTION
2237: END IF;
2238:
2239: EXCEPTION
2240: /* Handle standard exceptions */
2241: WHEN FND_API.G_EXC_ERROR THEN
2242: ROLLBACK TO validate_netting_dates_SP;
2243: x_return_status := FND_API.G_RET_STS_ERROR;
2244: FND_MSG_PUB.Count_And_Get (
2245: p_count => x_msg_count,
2239: EXCEPTION
2240: /* Handle standard exceptions */
2241: WHEN FND_API.G_EXC_ERROR THEN
2242: ROLLBACK TO validate_netting_dates_SP;
2243: x_return_status := FND_API.G_RET_STS_ERROR;
2244: FND_MSG_PUB.Count_And_Get (
2245: p_count => x_msg_count,
2246: p_data => x_msg_data );
2247:
2244: FND_MSG_PUB.Count_And_Get (
2245: p_count => x_msg_count,
2246: p_data => x_msg_data );
2247:
2248: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2249: ROLLBACK TO validate_netting_dates_SP;
2250: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2251: FND_MSG_PUB.Count_And_Get (
2252: p_count => x_msg_count,
2246: p_data => x_msg_data );
2247:
2248: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2249: ROLLBACK TO validate_netting_dates_SP;
2250: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2251: FND_MSG_PUB.Count_And_Get (
2252: p_count => x_msg_count,
2253: p_data => x_msg_data );
2254:
2253: p_data => x_msg_data );
2254:
2255: WHEN OTHERS THEN
2256: ROLLBACK TO validate_netting_dates_SP;
2257: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2258: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2259: --FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
2260: FND_MSG_PUB.Add_Exc_Msg( 'FUN_ARAP_NET_PKG', 'validate_netting_dates');
2261: END IF;
2272: x_return_status OUT NOCOPY VARCHAR2)
2273: IS
2274: l_agreement_id fun_net_agreements.agreement_id%TYPE;
2275: BEGIN
2276: x_return_status := FND_API.G_TRUE;
2277: IF x_agreement_id IS NOT NULL THEN
2278:
2279: SELECT agreement_id
2280: INTO l_agreement_id
2287: 'REVERSING',
2288: 'CLEARING');
2289:
2290: ELSE
2291: x_return_status := FND_API.G_FALSE;
2292: END IF;
2293:
2294: EXCEPTION
2295: WHEN TOO_MANY_ROWS THEN
2307: x_in_process_flag => 'N');
2308: END IF;
2309:
2310: WHEN OTHERS THEN
2311: x_return_status := FND_API.G_FALSE;
2312: END Set_Agreement_Status;
2313:
2314: /* Derive Bank Account Information. Find the Bank Account Use Id
2315: from the Bank Account Id. Netting Bank is stored in the agreement details */
2322: p_bank_num OUT NOCOPY VARCHAR2,
2323: p_return_status OUT NOCOPY VARCHAR2)
2324: IS
2325: BEGIN
2326: p_return_status := FND_API.G_TRUE;
2327: SELECT bank_acct_use_id ,
2328: ac.bank_account_name,
2329: ac.bank_account_num,
2330: ac.account_owner_org_id,
2344:
2345: EXCEPTION
2346:
2347: WHEN OTHERS THEN
2348: p_return_status := FND_API.G_FALSE;
2349:
2350: END Get_Netting_Bank_Details;
2351:
2352: /* Get the Customer Details to Create Receipt . If there is more than
2357: p_cust_site_use_id OUT NOCOPY NUMBER,
2358: p_return_status OUT NOCOPY VARCHAR2)
2359: IS
2360: BEGIN
2361: p_return_status := FND_API.G_TRUE;
2362:
2363: /* Select First Customer Account on Receipt */
2364:
2365:
2394:
2395:
2396: EXCEPTION
2397: WHEN OTHERS THEN
2398: p_return_status := FND_API.G_FALSE;
2399: END Get_Customer_Details;
2400:
2401: /* Unlocks Payment Schedule lines */
2402:
2409: l_path varchar2(200);
2410: l_org_id NUMBER(15);
2411: BEGIN
2412:
2413: x_return_status := FND_API.G_TRUE;
2414: l_path := g_path||'unlock_payment_schedules';
2415: -- Check for Mandatory Parameters
2416: IF p_batch_id IS NULL THEN
2417: x_return_status := FND_API.G_FALSE;
2413: x_return_status := FND_API.G_TRUE;
2414: l_path := g_path||'unlock_payment_schedules';
2415: -- Check for Mandatory Parameters
2416: IF p_batch_id IS NULL THEN
2417: x_return_status := FND_API.G_FALSE;
2418: END IF;
2419:
2420: -- Select the checkrun ID for the
2421: -- Netting Batch
2446: COMMIT;
2447: ELSE
2448: ROLLBACK;
2449:
2450: x_return_status := FND_API.G_FALSE;
2451: RETURN;
2452: END IF;
2453: EXCEPTION
2454: WHEN OTHERS THEN
2452: END IF;
2453: EXCEPTION
2454: WHEN OTHERS THEN
2455: fun_net_util.Log_String(g_state_level,l_path,'error while unlocking payment schedules');
2456: x_return_status := FND_API.G_FALSE;
2457: END unlock_ap_pymt_schedules;
2458:
2459: /* Derives Netting Exchange Rate . Returns 1 if the from and the to
2460: currency are the same . If the Rate type = 'User' then derives the
2534:
2535:
2536: l_path := g_path||'validate_settlement_period';
2537: /* Check if GL Period is open*/
2538: x_return_status := FND_API.G_TRUE;
2539:
2540: SELECT set_of_books_id
2541: INTO l_ledger_id
2542: FROM hr_operating_units
2553: x_period_type);
2554:
2555: IF (x_period_name IS NULL and x_closing_status IS NULL) OR
2556: x_closing_status not in ('O','F') THEN
2557: x_return_status := FND_API.G_FALSE;
2558: END IF;
2559: EXCEPTION
2560: WHEN OTHERS THEN
2561:
2559: EXCEPTION
2560: WHEN OTHERS THEN
2561:
2562: fun_net_util.Log_String(g_state_level,l_path,'The GL period for the settlement of netting batch is closed or not yet opened ');
2563: x_return_status := FND_API.G_FALSE;
2564: END Validate_Settlement_Period;
2565: /* Creates AP Check per vendor / vendor site / currency . Also creates
2566: invoice payments for each AP Check */
2567:
2608:
2609: BEGIN
2610:
2611: -- Group Invoices by Vendor , Vendor Site and Payment Currency .
2612: x_return_status := FND_API.G_TRUE;
2613:
2614: BEGIN
2615: l_path := g_path || 'Settle AP Transactions';
2616: fun_net_util.Log_String(g_event_level,l_path,'Group AP Invoices');
2643: EXCEPTION
2644: WHEN OTHERS THEN
2645:
2646: fun_net_util.Log_String(g_event_level,l_path,sqlcode || sqlerrm);
2647: RAISE FND_API.G_EXC_ERROR;
2648: END;
2649:
2650:
2651:
2764: AND finv.inv_curr_net_amt <> 0
2765: ORDER BY inv.invoice_id,apps.payment_num;
2766: EXCEPTION
2767: WHEN OTHERS THEN
2768: RAISE FND_API.G_EXC_ERROR;
2769: END;
2770:
2771: m := 0;
2772:
2861:
2862:
2863: IF l_check_id is null THEN
2864:
2865: x_return_status := FND_API.G_FALSE;
2866: RETURN;
2867: END IF;
2868:
2869:
2880: END;
2881: END LOOP;
2882: EXCEPTION
2883: WHEN OTHERS THEN
2884: x_return_status := FND_API.G_FALSE;
2885: RETURN;
2886: END;
2887:
2888: /* Calculates the Total Open Amount for an AR Transaction based on the
2920: txnCur IN OUT NOCOPY txnCurType,
2921: x_return_status OUT NOCOPY VARCHAR2)
2922: IS
2923: BEGIN
2924: x_return_status := FND_API.G_TRUE;
2925: OPEN txnCur FOR
2926: SELECT trx.invoice_currency_code AS invoice_currency_code,
2927: trx.customer_trx_id AS customer_trx_id,
2928: trx.trx_number AS trx_number,
2957: -- arps.payment_schedule_id;
2958:
2959: EXCEPTION
2960: WHEN OTHERS THEN
2961: x_return_status := FND_API.G_FALSE;
2962: END;
2963: */
2964: /* Validates the following
2965: The current Invoice Amount and the Open Amount against the Amount
2978: round_diff ra_customer_trx_lines.extended_amount%TYPE;
2979: l_count_test number;
2980:
2981: BEGIN
2982: x_return_status := FND_API.G_TRUE;
2983: l_path := g_path || 'Validate AR Transactions';
2984: fun_net_util.Log_String(g_event_level,l_path,
2985: 'Validating AR Transactions');
2986:
3084: -- IF txnTable(i).txn_amt <> l_inv_amt THEN Commented for bug 5326485
3085: IF txnTable(i).txn_curr_amt <> txnTable(i).txn_amt THEN
3086:
3087: IF abs(round_diff) > 0.01 THEN
3088: x_return_status := FND_API.G_FALSE;
3089: EXIT;
3090: END IF;
3091: END IF;
3092:
3101:
3102: fun_net_util.Log_String(g_event_level,l_path,
3103: 'Amt Remaining fail2 ' || txnTable(i).amt_remaining);
3104:
3105: x_return_status := FND_API.G_FALSE;
3106: EXIT;
3107: END IF;
3108:
3109: -- If the Payment Type = CREDIT CARD then raise error
3107: END IF;
3108:
3109: -- If the Payment Type = CREDIT CARD then raise error
3110: IF txnTable(i).payment_type_code = 'CREDIT_CARD' THEN
3111: x_return_status := FND_API.G_FALSE;
3112: EXIT;
3113: END IF;
3114:
3115: END LOOP;
3114:
3115: END LOOP;
3116: ELSE
3117:
3118: x_return_status := FND_API.G_FALSE;
3119: END IF;
3120:
3121: EXCEPTION
3122: WHEN OTHERS THEN
3119: END IF;
3120:
3121: EXCEPTION
3122: WHEN OTHERS THEN
3123: x_return_status := FND_API.G_FALSE;
3124: END Validate_AR_Txns;
3125:
3126: PROCEDURE create_cash_receipt(
3127: pARReceiptRec IN arReceiptRecType,
3150:
3151: AR_RECEIPT_API_PUB.Create_cash(
3152: -- Standard API parameters.
3153: p_api_version => 1.0,
3154: p_init_msg_list => FND_API.G_TRUE,
3155: p_commit => FND_API.G_FALSE,
3156: x_return_status => x_return_status,
3157: x_msg_count => x_msg_count,
3158: x_msg_data => x_msg_data,
3151: AR_RECEIPT_API_PUB.Create_cash(
3152: -- Standard API parameters.
3153: p_api_version => 1.0,
3154: p_init_msg_list => FND_API.G_TRUE,
3155: p_commit => FND_API.G_FALSE,
3156: x_return_status => x_return_status,
3157: x_msg_count => x_msg_count,
3158: x_msg_data => x_msg_data,
3159:
3179:
3180: -- ** OUT NOCOPY variables
3181: p_cr_id => x_cash_receipt_id);
3182:
3183: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3184: fun_net_util.log_string(g_event_level,l_path
3185: ,'Cash receipt creation failed either because AR_RECEIPT_API_PUB. Creat_cash failed or there is no receipt class associated ');
3186:
3187: FND_MSG_PUB.Count_And_Get (
3191:
3192:
3193: FOR x IN 1..x_msg_count LOOP
3194:
3195: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
3196: fun_net_util.Log_String(g_unexp_level,l_path,'Error message:' ||x_msg_data);
3197:
3198: END LOOP;
3199: END IF;
3199: END IF;
3200:
3201:
3202:
3203: x_return_status := FND_API.G_FALSE;
3204: RETURN;
3205: END IF;
3206: EXCEPTION
3207: WHEN OTHERS THEN
3204: RETURN;
3205: END IF;
3206: EXCEPTION
3207: WHEN OTHERS THEN
3208: x_return_status := FND_API.G_FALSE;
3209: END create_cash_receipt;
3210:
3211: /*PROCEDURE apply2_cash_receipt
3212: (txnTable IN txnTblType,
3262:
3263: l_open_amt := txnTable(i).txn_curr_open_amt;
3264:
3265: IF l_open_amt <> current_amt_due THEN
3266: x_return_status := FND_API.G_FALSE;
3267: EXIT;
3268: END IF;
3269:
3270: FOR apply_rec in apply_trx_cur(txnTable(i).customer_trx_id)
3296:
3297: AR_RECEIPT_API_PUB.Apply(
3298: -- Standard API parameters.
3299: p_api_version => 1.0,
3300: p_init_msg_list => FND_API.G_FALSE,
3301: p_commit => FND_API.G_FALSE,
3302: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3303: x_return_status => l_return_status,
3304: x_msg_count => l_msg_count,
3297: AR_RECEIPT_API_PUB.Apply(
3298: -- Standard API parameters.
3299: p_api_version => 1.0,
3300: p_init_msg_list => FND_API.G_FALSE,
3301: p_commit => FND_API.G_FALSE,
3302: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3303: x_return_status => l_return_status,
3304: x_msg_count => l_msg_count,
3305: x_msg_data => l_msg_data,
3298: -- Standard API parameters.
3299: p_api_version => 1.0,
3300: p_init_msg_list => FND_API.G_FALSE,
3301: p_commit => FND_API.G_FALSE,
3302: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3303: x_return_status => l_return_status,
3304: x_msg_count => l_msg_count,
3305: x_msg_data => l_msg_data,
3306: -- Receipt application parameters.
3320: );
3321:
3322: dbms_output.put_line('Apply' || l_return_status);
3323:
3324: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3325: x_return_status := FND_API.G_FALSE;
3326: RETURN;
3327: END IF;
3328:
3321:
3322: dbms_output.put_line('Apply' || l_return_status);
3323:
3324: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3325: x_return_status := FND_API.G_FALSE;
3326: RETURN;
3327: END IF;
3328:
3329: END LOOP;
3330: END LOOP;
3331:
3332: EXCEPTION
3333: WHEN OTHERS THEN
3334: x_return_status := FND_API.G_FALSE;
3335: END apply2_cash_receipt; */
3336:
3337:
3338:
3422:
3423: l_open_amt := txnTable(i).txn_curr_open_amt;
3424:
3425: IF l_open_amt <> current_amt_due THEN
3426: x_return_status := FND_API.G_FALSE;
3427: EXIT;
3428: END IF;
3429:
3430: -- Exchange rate related logic for Bug 5463394
3537: AND trx_line.line_type IN ('LINE')
3538: AND nvl(trx_line.amount_due_remaining,trx_line.extended_amount) <> 0 ;
3539: EXCEPTION
3540: WHEN OTHERS THEN
3541: RAISE FND_API.G_EXC_ERROR;
3542: END;
3543:
3544:
3545: -- get the freight amount
3601: IF(l_class = 'INV' OR l_class = 'DM') THEN
3602: AR_RECEIPT_API_PUB.Apply_In_Detail(
3603: -- Standard API parameters.
3604: p_api_version => 1.0,
3605: p_init_msg_list => FND_API.G_FALSE,
3606: p_commit => FND_API.G_FALSE,
3607: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3608: x_return_status => l_return_status,
3609: x_msg_count => l_msg_count,
3602: AR_RECEIPT_API_PUB.Apply_In_Detail(
3603: -- Standard API parameters.
3604: p_api_version => 1.0,
3605: p_init_msg_list => FND_API.G_FALSE,
3606: p_commit => FND_API.G_FALSE,
3607: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3608: x_return_status => l_return_status,
3609: x_msg_count => l_msg_count,
3610: x_msg_data => l_msg_data,
3603: -- Standard API parameters.
3604: p_api_version => 1.0,
3605: p_init_msg_list => FND_API.G_FALSE,
3606: p_commit => FND_API.G_FALSE,
3607: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3608: x_return_status => l_return_status,
3609: x_msg_count => l_msg_count,
3610: x_msg_data => l_msg_data,
3611: -- Receipt application parameters.
3624:
3625: ELSE
3626: Ar_receipt_api_pub.apply(
3627: p_api_version => 1.0,
3628: p_init_msg_list => FND_API.G_TRUE,
3629: p_cash_receipt_id => p_cash_receipt_id,
3630: p_customer_trx_id => txnTable(i).customer_trx_id,
3631: x_return_status => l_return_status,
3632: x_msg_count => l_msg_count,
3635: END IF;
3636: ELSE
3637: Ar_receipt_api_pub.apply(
3638: p_api_version => 1.0,
3639: p_init_msg_list => FND_API.G_TRUE,
3640: p_cash_receipt_id => p_cash_receipt_id,
3641: p_customer_trx_id => txnTable(i).customer_trx_id,
3642: x_return_status => l_return_status,
3643: x_msg_count => l_msg_count,
3646:
3647: END IF;
3648:
3649:
3650: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3651:
3652: FND_MSG_PUB.Count_And_Get (
3653: p_count => l_msg_count,
3654: p_data => l_msg_data );
3661:
3662:
3663: FOR x IN 1..l_msg_count LOOP
3664:
3665: l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
3666: fun_net_util.Log_String(g_event_level,l_path
3667: ,'apply cash receipt package Error message AR_RECEIPT_API_PUB.Apply' ||l_msg_data||' '||' '||x);
3668:
3669: END LOOP;
3668:
3669: END LOOP;
3670: END IF;
3671:
3672: x_return_status := FND_API.G_FALSE;
3673: RETURN;
3674: END IF;
3675:
3676: END LOOP;
3677: END LOOP;
3678:
3679: EXCEPTION
3680: WHEN OTHERS THEN
3681: x_return_status := FND_API.G_FALSE;
3682: END apply_cash_receipt;
3683:
3684:
3685:
3722: p_return_status => l_return_status
3723: );
3724:
3725:
3726: IF l_return_status = FND_API.G_FALSE then
3727: fun_net_util.Log_String(g_event_level,
3728: l_path
3729: ,'No Customer Details');
3730: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_CUST_DETAILS');
3727: fun_net_util.Log_String(g_event_level,
3728: l_path
3729: ,'No Customer Details');
3730: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_CUST_DETAILS');
3731: x_return_status := FND_API.G_FALSE;
3732: RETURN;
3733: ELSE
3734: fun_net_util.Log_String(g_event_level,
3735: l_path
3750: fun_net_util.Log_String(g_event_level,
3751: l_path
3752: ,'Receipt Method not found');
3753: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_RECEIPT_METHOD');
3754: x_return_status := FND_API.G_FALSE;
3755: RETURN;
3756: END;
3757:
3758: BEGIN
3769: fun_net_util.Log_String(g_event_level,
3770: l_path
3771: ,'Agreement Bank Account not associated to Receipt Method AP/AR Netting ');
3772: FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_BANK_ACCT_MISSING');
3773: x_return_status := FND_API.G_FALSE;
3774: RETURN;
3775: END;
3776:
3777: -- Set all inputs to create cash receipt --
3823: fun_net_util.Log_String(g_event_level,
3824: l_path
3825: ,x_msg_data|| ' with message ' || x_msg_count);
3826:
3827: IF x_return_status = FND_API.G_FALSE THEN
3828:
3829: fun_net_util.Log_String(g_event_level,
3830: l_path
3831: ,'Cash Receipt Creation Failed');
3844: (txnTable => txnTable,
3845: p_cash_receipt_id => l_cash_receipt_id,
3846: x_return_status => x_return_status);
3847:
3848: IF x_return_status = FND_API.G_FALSE THEN
3849: FND_MESSAGE.SET_NAME('FUN','FUN_NET_APPLY_RECEIPT_FAIL');
3850: RETURN;
3851: END IF;
3852:
3866: fun_net_util.Log_String(g_event_level,
3867: l_path
3868: ,'Failed to associate AR transactions in the batch with cash receipt');
3869:
3870: x_return_status := FND_API.G_FALSE;
3871: END;
3872: END IF;
3873:
3874: EXCEPTION
3876: fun_net_util.Log_String(g_event_level,
3877: l_path
3878: ,'Unknown error in settle_ar_txns procedure');
3879:
3880: x_return_status := FND_API.G_FALSE;
3881: END settle_ar_txns;
3882:
3883:
3884: /*************************************************************************
3933: END IF;
3934: IF g_batch_details.exchange_rate_type IS NULL THEN
3935: g_batch_details.exchange_rate_type := 'Corporate';
3936: END IF;
3937: RETURN FND_API.G_TRUE;
3938: EXCEPTION
3939: WHEN OTHERS THEN
3940: RETURN FND_API.G_FALSE;
3941:
3936: END IF;
3937: RETURN FND_API.G_TRUE;
3938: EXCEPTION
3939: WHEN OTHERS THEN
3940: RETURN FND_API.G_FALSE;
3941:
3942: END default_batch_details;
3943:
3944:
3943:
3944:
3945: PROCEDURE settle_net_batch (
3946: -- ***** Standard API Parameters *****
3947: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
3948: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3949: x_return_status OUT NOCOPY VARCHAR2,
3950: x_msg_count OUT NOCOPY NUMBER,
3951: x_msg_data OUT NOCOPY VARCHAR2,
3944:
3945: PROCEDURE settle_net_batch (
3946: -- ***** Standard API Parameters *****
3947: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
3948: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3949: x_return_status OUT NOCOPY VARCHAR2,
3950: x_msg_count OUT NOCOPY NUMBER,
3951: x_msg_data OUT NOCOPY VARCHAR2,
3952: -- ***** Netting batch input parameters *****
3981: x_msg_count := NULL;
3982: x_msg_data := NULL;
3983:
3984: -- **** Initialize message list if p_init_msg_list is set to TRUE. ****
3985: IF FND_API.to_Boolean( p_init_msg_list ) THEN
3986: FND_MSG_PUB.initialize;
3987: END IF;
3988:
3989: -- **** Initialize return status to SUCCESS *****
3986: FND_MSG_PUB.initialize;
3987: END IF;
3988:
3989: -- **** Initialize return status to SUCCESS *****
3990: x_return_status := FND_API.G_RET_STS_SUCCESS;
3991:
3992: /* Create Save point */
3993: SAVEPOINT settle_net_batch_SP;
3994:
3998: fun_net_util.Log_String(
3999: g_event_level
4000: ,l_path,'Batch Id is null');
4001: FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4002: RAISE FND_API.G_EXC_ERROR;
4003: END IF;
4004:
4005: g_batch_id := p_batch_id;
4006:
4008:
4009: fun_net_util.Log_String(g_event_level,l_path,
4010: 'Error getting Batch details');
4011: FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4012: RAISE FND_API.G_EXC_ERROR;
4013: END IF;
4014:
4015: g_agreement_id := g_batch_details.agreement_id;
4016:
4017: IF NOT get_agreement_details THEN
4018:
4019: fun_net_util.Log_String(g_event_level,l_path,
4020: 'Error getting Agreement details');
4021: RAISE FND_API.G_EXC_ERROR;
4022: END IF;
4023:
4024: IF g_batch_details.batch_status_code NOT IN ( 'CLEARING',
4025: 'SUBMITTED') THEN
4026:
4027: fun_net_util.Log_String(g_event_level,l_path,
4028: 'Batch Status is not CLEARING');
4029: FND_MESSAGE.SET_NAME('FUN','FUN_NET_BATCH_STATUS_ERROR');
4030: RAISE FND_API.G_EXC_ERROR;
4031: END IF;
4032:
4033: /* Get Functional Currency */
4034: get_functional_currency;
4035:
4036:
4037: /* Default Batch Details */
4038:
4039: IF default_batch_details = FND_API.G_FALSE THEN
4040:
4041: fun_net_util.Log_String(g_event_level,l_path,
4042: 'Error defaulting Batch Details');
4043: FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4040:
4041: fun_net_util.Log_String(g_event_level,l_path,
4042: 'Error defaulting Batch Details');
4043: FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4044: RAISE FND_API.G_EXC_ERROR;
4045: END IF;
4046:
4047: -- Calculate Netting balances
4048:
4047: -- Calculate Netting balances
4048:
4049: calculate_AP_AR_balances(l_amt_to_net,l_status_flag);
4050:
4051: IF l_status_flag = FND_API.G_FALSE THEN
4052:
4053: RAISE FND_API.G_EXC_ERROR;
4054:
4055: END IF;
4049: calculate_AP_AR_balances(l_amt_to_net,l_status_flag);
4050:
4051: IF l_status_flag = FND_API.G_FALSE THEN
4052:
4053: RAISE FND_API.G_EXC_ERROR;
4054:
4055: END IF;
4056:
4057:
4063: txnTable => txnTable,
4064: x_return_status => l_return_status
4065: );
4066:
4067: IF l_return_status = FND_API.G_FALSE THEN
4068:
4069: fun_net_util.Log_String(g_event_level,l_path,
4070: 'Error Validating AR Transactions');
4071: FND_MESSAGE.SET_NAME('FUN','FUN_NET_VAL_AR_TXN_FAIL');
4082: x_period_name => l_period_name,
4083: x_return_status => l_return_status ,
4084: x_return_msg => l_msg_data);
4085:
4086: IF l_return_status = FND_API.G_FALSE THEN
4087:
4088: fun_net_util.Log_String(g_event_level,l_path,
4089: 'Error Validating AP Period');
4090: FND_MESSAGE.SET_NAME('FUN','FUN_NET_AP_PERIOD_FAIL');
4089: 'Error Validating AP Period');
4090: FND_MESSAGE.SET_NAME('FUN','FUN_NET_AP_PERIOD_FAIL');
4091: FND_MESSAGE.SET_TOKEN('SETTLE_DATE',
4092: g_batch_details.settlement_date,FALSE);
4093: RAISE FND_API.G_EXC_ERROR;
4094: END IF;
4095:
4096: /* Validate GL Period - AR */
4097:
4100: x_period_name => l_period_name,
4101: x_return_status => l_return_status ,
4102: x_return_msg => l_msg_data);
4103:
4104: IF l_return_status = FND_API.G_FALSE THEN
4105:
4106: fun_net_util.Log_String(g_event_level,l_path,
4107: 'Error Validating AR Period');
4108: FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_PERIOD_FAIL');
4107: 'Error Validating AR Period');
4108: FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_PERIOD_FAIL');
4109: FND_MESSAGE.SET_TOKEN('SETTLE_DATE',
4110: g_batch_details.settlement_date,FALSE);
4111: RAISE FND_API.G_EXC_ERROR;
4112: END IF;
4113:
4114: Validate_Settlement_Period
4115: (x_appln_id => 101,
4116: x_period_name => l_period_name,
4117: x_return_status => l_return_status ,
4118: x_return_msg => l_msg_data);
4119:
4120: IF l_return_status = FND_API.G_FALSE THEN
4121:
4122: fun_net_util.Log_String(g_event_level,l_path,
4123: 'Error Validating GL Period');
4124: FND_MESSAGE.SET_NAME('FUN','FUN_NET_GL_PERIOD_FAIL');
4123: 'Error Validating GL Period');
4124: FND_MESSAGE.SET_NAME('FUN','FUN_NET_GL_PERIOD_FAIL');
4125: FND_MESSAGE.SET_TOKEN('SETTLE_DATE',
4126: g_batch_details.settlement_date,FALSE);
4127: RAISE FND_API.G_EXC_ERROR;
4128: END IF;
4129:
4130:
4131: /* Get Netting Bank Account Information */
4135: l_le_id,
4136: l_bank_num,
4137: l_return_status);
4138:
4139: IF l_return_status = FND_API.G_FALSE THEN
4140:
4141: fun_net_util.Log_String(g_event_level,l_path,
4142: 'Error in Getting Netting Bank Details');
4143: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_BANK_DETAILS');
4140:
4141: fun_net_util.Log_String(g_event_level,l_path,
4142: 'Error in Getting Netting Bank Details');
4143: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_BANK_DETAILS');
4144: RAISE FND_API.G_EXC_ERROR;
4145: END IF;
4146:
4147: --------------------------------------------------------------------
4148: /******************** Settle AP Transactions ***********************/
4155: p_bank_num => l_bank_num,
4156: x_return_status => l_return_status);
4157:
4158:
4159: IF l_return_status = FND_API.G_FALSE THEN
4160:
4161: fun_net_util.Log_String(g_event_level,l_path,
4162: 'Error in Settling AP Invoices');
4163: -- FND_MESSAGE.SET_NAME('FUN','FUN_NET_AP_SETTLE_FAIL');
4160:
4161: fun_net_util.Log_String(g_event_level,l_path,
4162: 'Error in Settling AP Invoices');
4163: -- FND_MESSAGE.SET_NAME('FUN','FUN_NET_AP_SETTLE_FAIL');
4164: RAISE FND_API.G_EXC_ERROR;
4165: END IF;
4166:
4167:
4168: /* -----------------------------------------------------------------*/
4177: x_msg_data => l_msg_data,
4178: x_msg_count => l_msg_count);
4179:
4180:
4181: IF l_return_status = FND_API.G_FALSE THEN
4182: -- x_msg_count := l_msg_count;
4183: -- x_msg_data := l_msg_data;
4184:
4185: fun_net_util.Log_String(g_event_level,l_path,
4184:
4185: fun_net_util.Log_String(g_event_level,l_path,
4186: 'Error in Settling AR Transactions');
4187: -- FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_SETTLE_FAIL');
4188: RAISE FND_API.G_EXC_ERROR;
4189: END IF;
4190:
4191:
4192:
4196:
4197: fun_net_util.Log_String(g_event_level,l_path,
4198: 'Error in Setting Status to Complete');
4199: FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4200: RAISE FND_API.G_EXC_ERROR;
4201:
4202: ELSE
4203:
4204: -- Submit the Data Extract report
4218: END IF;
4219:
4220:
4221: -- Standard check of p_commit.
4222: IF FND_API.To_Boolean( p_commit ) THEN
4223: COMMIT WORK;
4224: END IF;
4225:
4226: -- Call Procedure to perform common tasks after committing
4230:
4231: EXCEPTION
4232:
4233:
4234: WHEN FND_API.G_EXC_ERROR THEN
4235: ROLLBACK TO settle_net_batch_SP;
4236: batch_status_flag := update_batch_status('ERROR');
4237:
4238: --perform_common_tasks;
4235: ROLLBACK TO settle_net_batch_SP;
4236: batch_status_flag := update_batch_status('ERROR');
4237:
4238: --perform_common_tasks;
4239: x_return_status := FND_API.G_RET_STS_ERROR;
4240: FND_MSG_PUB.Count_And_Get (
4241: p_count => x_msg_count,
4242: p_data => x_msg_data );
4243:
4240: FND_MSG_PUB.Count_And_Get (
4241: p_count => x_msg_count,
4242: p_data => x_msg_data );
4243:
4244: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4245: ROLLBACK TO settle_net_batch_SP;
4246: batch_status_flag := update_batch_status('ERROR');
4247:
4248: --perform_common_tasks;
4246: batch_status_flag := update_batch_status('ERROR');
4247:
4248: --perform_common_tasks;
4249:
4250: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4251:
4252: FND_MSG_PUB.Count_And_Get (
4253: p_count => x_msg_count,
4254: p_data => x_msg_data );
4257: ROLLBACK TO settle_Net_Batch_SP;
4258: batch_status_flag := update_batch_status('CANCELLED');
4259: perform_common_tasks; -- Bug 5608043
4260:
4261: x_return_status := FND_API.G_RET_STS_ERROR;
4262: FND_MSG_PUB.Count_And_Get (
4263: p_count => x_msg_count,
4264: p_data => x_msg_data );
4265:
4269: ROLLBACK TO settle_Net_Batch_SP;
4270: batch_status_flag := update_batch_status('ERROR');
4271:
4272: -- perform_common_tasks;
4273: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4274:
4275: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4276: FND_MSG_PUB.Add_Exc_Msg( 'FUN_ARAP_NET_PKG', 'settle_net_batch');
4277: END IF;