[Home] [Help]
992: l_path VARCHAR2(100);
993: BEGIN
994: l_path := g_path || 'lock_ap_pymt_schedules';
995: fun_net_util.Log_String(g_state_level,l_path,'Start of locking');
996: x_return_status := FND_API.G_TRUE;
997: fun_net_util.Log_String(g_state_level,l_path,'p_schd_tab.count :'||p_schd_tab.count);
998: fun_net_util.Log_String(g_state_level,l_path,'p_batch_id :'||p_batch_id);
999: -- Check for Mandatory Parameters
1000: IF p_schd_tab.count = 0 OR p_batch_id IS NULL THEN
997: fun_net_util.Log_String(g_state_level,l_path,'p_schd_tab.count :'||p_schd_tab.count);
998: fun_net_util.Log_String(g_state_level,l_path,'p_batch_id :'||p_batch_id);
999: -- Check for Mandatory Parameters
1000: IF p_schd_tab.count = 0 OR p_batch_id IS NULL THEN
1001: x_return_status := FND_API.G_FALSE;
1002: RETURN;
1003: END IF;
1004: --AND TRUNC(due_date) <= NVL(p_trx_due_date,TRUNC(due_date))
1005: select checkrun_id
1025: fun_net_util.Log_String(g_state_level,l_path,'Payment schedules updated');
1026: EXCEPTION
1027: WHEN OTHERS THEN
1028: fun_net_util.Log_String(g_state_level,l_path,'Failure in locking ap payment schedules');
1029: x_return_status := FND_API.G_FALSE;
1030: END lock_ap_pymt_schedules;
1031: PROCEDURE insert_transactions(p_inv_cur VARCHAR2,p_currency_code VARCHAR2, p_appln VARCHAR2) IS
1032: l_batch_exists VARCHAR2(1);
1033: --l_inv_rank NUMBER;
1852: l_path varchar2(100);
1853: BEGIN
1854: l_path := g_path || 'Calculate_AP_AR_Balances';
1855: validate_AP_AR_balances(l_ar_bal, l_ap_bal, l_status_flag);
1856: IF l_status_flag = FND_API.G_TRUE THEN
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 */
1880: /*UPDATE fun_net_batches_all
1881: SET total_netted_amt = l_amt_to_net
1882: WHERE batch_id = g_batch_id; */
1883: p_amt_to_net := l_amt_to_net;
1884: p_status_flag := FND_API.G_TRUE;
1885: ELSE
1886: /*Unlock AP and AR Transactions that have been locked */
1887: -- 12707226
1888: 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');
1885: ELSE
1886: /*Unlock AP and AR Transactions that have been locked */
1887: -- 12707226
1888: 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');
1889: p_status_flag := FND_API.G_FALSE;
1890: END IF;
1891: EXCEPTION
1892: WHEN NO_DATA_FOUND then
1893: fun_net_util.Log_String(g_proc_level,l_path,'EXCEPTION : '||sqlerrm);
1909: l_path varchar2(100);
1910: l_msg_data VARCHAR2(2000);
1911: BEGIN
1912: l_path := g_path || 'Validate_AP_AR_Balances';
1913: p_status_flag := FND_API.G_TRUE;
1914: OPEN c_get_ar_open_amount;
1915: FETCH c_get_ar_open_amount INTO p_ar_bal;
1916: CLOSE c_get_ar_open_amount;
1917: OPEN c_get_ap_open_amount;
1919: CLOSE c_get_ap_open_amount;
1920: fun_net_util.Log_String(g_state_level,l_path,'AP open amount :'||p_ap_bal);
1921: fun_net_util.Log_String(g_state_level,l_path,'AR open amount :'||p_ar_bal);
1922: IF nvl(p_ap_bal,0) = 0 OR nvl(p_ar_bal,0) = 0 THEN
1923: p_status_flag := FND_API.G_FALSE;
1924: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_BALANCES');
1925: l_msg_data := FND_MESSAGE.GET;
1926: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
1927: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1945: l_path varchar2(100);
1946: BEGIN
1947: l_path := g_path || 'calculate_approx_netted_amount';
1948: AP_AR_balances_with_approxdisc(l_ar_bal, l_ap_bal, l_status_flag);
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
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;
1974: p_status_flag := FND_API.G_TRUE;
1975: ELSE
1976: /*Unlock AP and AR Transactions that have been locked */
1977: -- 10707226
1978: 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');
1975: ELSE
1976: /*Unlock AP and AR Transactions that have been locked */
1977: -- 10707226
1978: 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');
1979: p_status_flag := FND_API.G_FALSE;
1980: END IF;
1981: EXCEPTION
1982: WHEN NO_DATA_FOUND then
1983: fun_net_util.Log_String(g_proc_level,l_path,'EXCEPTION : '||sqlerrm);
1997: l_path varchar2(100);
1998: l_msg_data VARCHAR2(2000);
1999: BEGIN
2000: l_path := g_path || 'Validate_AP_AR_Balances';
2001: p_status_flag := FND_API.G_TRUE;
2002: OPEN c_get_ar_open_amount;
2003: FETCH c_get_ar_open_amount INTO p_ar_bal;
2004: CLOSE c_get_ar_open_amount;
2005: OPEN c_get_ap_open_amount;
2012: --ERROR MESSAGE : 'Netting cannot be performed as the Outstanding balance is zero'
2013: /*UPDATE fun_net_batches_all
2014: SET batch_status_code = 'ERROR'
2015: WHERE batch_id = g_batch_id; */
2016: p_status_flag := FND_API.G_FALSE;
2017: /*IF NOT update_Batch_Status('ERROR') THEN
2018: p_status_flag := FND_API.G_FALSE;
2019: END IF; */
2020: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_BALANCES');
2014: SET batch_status_code = 'ERROR'
2015: WHERE batch_id = g_batch_id; */
2016: p_status_flag := FND_API.G_FALSE;
2017: /*IF NOT update_Batch_Status('ERROR') THEN
2018: p_status_flag := FND_API.G_FALSE;
2019: END IF; */
2020: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_BALANCES');
2021: l_msg_data := FND_MESSAGE.GET;
2022: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
2178: l_msg_data VARCHAR2(2000);
2179: BEGIN
2180: l_path := g_path || 'validate_exch_rate';
2181: l_count := 0;
2182: p_status_flag := FND_API.G_TRUE;
2183: SELECT USER_CONVERSION_TYPE
2184: INTO l_conv_rate
2185: FROM GL_DAILY_CONVERSION_TYPES
2186: WHERE CONVERSION_TYPE = g_batch_details.exchange_rate_type;
2212: trx_curr_REC.invoice_currency_code || ' -> ' || g_func_currency );
2213: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2214: END IF;
2215: IF l_exc_rate is NULL THEN
2216: p_status_flag := FND_API.G_FALSE;
2217: END IF;
2218: END LOOP;
2219: fun_net_util.Log_String(g_state_level,l_path,' p_status_flag :'
2220: || p_status_flag);
2332: NULL;
2333: END calculate_ar_trx_amt;
2334: PROCEDURE create_net_batch(
2335: -- ***** Standard API Parameters *****
2336: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2337: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2338: x_return_status OUT NOCOPY VARCHAR2,
2339: x_msg_count OUT NOCOPY NUMBER,
2340: x_msg_data OUT NOCOPY VARCHAR2,
2333: END calculate_ar_trx_amt;
2334: PROCEDURE create_net_batch(
2335: -- ***** Standard API Parameters *****
2336: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2337: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2338: x_return_status OUT NOCOPY VARCHAR2,
2339: x_msg_count OUT NOCOPY NUMBER,
2340: x_msg_data OUT NOCOPY VARCHAR2,
2341: -- ***** Netting batch input parameters *****
2374: g_login_id := fnd_global.login_id;
2375: -- **** Standard start of API savepoint ****
2376: SAVEPOINT create_net_batch_SP;
2377: -- **** Initialize message list if p_init_msg_list is set to TRUE. ****
2378: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2379: FND_MSG_PUB.initialize;
2380: END IF;
2381: -- **** Initialize return status to SUCCESS *****
2382: x_return_status := FND_API.G_RET_STS_SUCCESS;
2378: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2379: FND_MSG_PUB.initialize;
2380: END IF;
2381: -- **** Initialize return status to SUCCESS *****
2382: x_return_status := FND_API.G_RET_STS_SUCCESS;
2383: /*-----------------------------------------------+
2384: | ======== START OF API BODY ============ |
2385: +-----------------------------------------------*/
2386: /* Check for mandatory parameters */
2384: | ======== START OF API BODY ============ |
2385: +-----------------------------------------------*/
2386: /* Check for mandatory parameters */
2387: IF p_batch_id IS NULL THEN
2388: RAISE FND_API.G_EXC_ERROR;
2389: END IF;
2390: g_batch_id := p_batch_id;
2391: FND_FILE.PUT_LINE(FND_FILE.LOG,'Create Netting Batch - Batch ID '||To_char(g_batch_id));
2392: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2393: --Call the procedure to get the batch details
2394: fun_net_util.Log_String(g_state_level,l_path,'Fetching batch details');
2395: IF NOT get_batch_details THEN
2396: fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching batch details');
2397: RAISE FND_API.G_EXC_ERROR;
2398: END IF;
2399: --If the call is successful then call the procedure to update batch status
2400: IF NOT update_batch_status('RUNNING') THEN
2401: fun_net_util.Log_String(g_state_level,l_path,'Error in updating batch status');
2398: END IF;
2399: --If the call is successful then call the procedure to update batch status
2400: IF NOT update_batch_status('RUNNING') THEN
2401: fun_net_util.Log_String(g_state_level,l_path,'Error in updating batch status');
2402: RAISE FND_API.G_EXC_ERROR;
2403: END IF; -- Return Code
2404: --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.
2405: -- If the call is successful then get the agreement details and select transactions for every agreement
2406: fun_net_util.Log_String(g_state_level,l_path,'Before processing the agreements');
2439: --g_currency_count := 0;
2440: --If the call is successful then call the procedure to select all customer transactions --and insert them into the customer transactions table
2441: IF NOT prepare_ar_transactions THEN
2442: fun_net_util.Log_String(g_state_level,l_path,'Error in prepare AR Transactions');
2443: RAISE FND_API.G_EXC_ERROR;
2444: END IF;
2445: --If the call is successful then call the procedure to select all supplier transactions and insert them into the supplier invoices table.
2446: IF NOT prepare_ap_transactions THEN
2447: fun_net_util.Log_String(g_state_level,l_path,'Error in prepare AP transactions');
2444: END IF;
2445: --If the call is successful then call the procedure to select all supplier transactions and insert them into the supplier invoices table.
2446: IF NOT prepare_ap_transactions THEN
2447: fun_net_util.Log_String(g_state_level,l_path,'Error in prepare AP transactions');
2448: RAISE FND_API.G_EXC_ERROR;
2449: END IF;
2450: END LOOP;
2451: fun_net_util.Log_String(g_state_level,l_path,'Processing the batches');
2452: FOR i IN 1..g_idx
2450: END LOOP;
2451: fun_net_util.Log_String(g_state_level,l_path,'Processing the batches');
2452: FOR i IN 1..g_idx
2453: LOOP
2454: l_status_flag := FND_API.G_TRUE;
2455:
2456: g_batch_id := g_batch_list(i).batch_id;
2457: IF NOT get_batch_details THEN
2458: fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching batch details');
2455:
2456: g_batch_id := g_batch_list(i).batch_id;
2457: IF NOT get_batch_details THEN
2458: fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching batch details');
2459: -- RAISE FND_API.G_EXC_ERROR;
2460: l_status_flag := FND_API.G_FALSE;
2461: END IF;
2462: g_agreement_id := g_batch_list(i).agreement_id;
2463:
2456: g_batch_id := g_batch_list(i).batch_id;
2457: IF NOT get_batch_details THEN
2458: fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching batch details');
2459: -- RAISE FND_API.G_EXC_ERROR;
2460: l_status_flag := FND_API.G_FALSE;
2461: END IF;
2462: g_agreement_id := g_batch_list(i).agreement_id;
2463:
2464: IF l_status_flag = FND_API.G_TRUE
2460: l_status_flag := FND_API.G_FALSE;
2461: END IF;
2462: g_agreement_id := g_batch_list(i).agreement_id;
2463:
2464: IF l_status_flag = FND_API.G_TRUE
2465: THEN
2466: IF NOT get_agreement_details THEN
2467: fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching agreement details');
2468: --RAISE FND_API.G_EXC_ERROR;
2464: IF l_status_flag = FND_API.G_TRUE
2465: THEN
2466: IF NOT get_agreement_details THEN
2467: fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching agreement details');
2468: --RAISE FND_API.G_EXC_ERROR;
2469: l_status_flag := FND_API.G_FALSE;
2470: END IF;
2471: END IF;
2472: fun_net_util.Log_String(g_state_level,l_path,'Batch ID:'||g_batch_id);
2465: THEN
2466: IF NOT get_agreement_details THEN
2467: fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching agreement details');
2468: --RAISE FND_API.G_EXC_ERROR;
2469: l_status_flag := FND_API.G_FALSE;
2470: END IF;
2471: END IF;
2472: fun_net_util.Log_String(g_state_level,l_path,'Batch ID:'||g_batch_id);
2473: FND_FILE.PUT_LINE(FND_FILE.LOG,'Batch ID '||To_char(g_batch_id));
2474: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2475:
2476: -- verify currency rates
2477:
2478: IF l_status_flag = FND_API.G_TRUE
2479: THEN
2480: validate_exch_rate(l_return_status);
2481: IF l_return_status = FND_API.G_FALSE THEN
2482: fun_net_util.Log_String(g_event_level,l_path, 'validate_exch_rate returns false');
2477:
2478: IF l_status_flag = FND_API.G_TRUE
2479: THEN
2480: validate_exch_rate(l_return_status);
2481: IF l_return_status = FND_API.G_FALSE THEN
2482: fun_net_util.Log_String(g_event_level,l_path, 'validate_exch_rate returns false');
2483: -- RAISE FND_API.G_EXC_ERROR;
2484: l_status_flag := FND_API.G_FALSE;
2485: END IF;
2479: THEN
2480: validate_exch_rate(l_return_status);
2481: IF l_return_status = FND_API.G_FALSE THEN
2482: fun_net_util.Log_String(g_event_level,l_path, 'validate_exch_rate returns false');
2483: -- RAISE FND_API.G_EXC_ERROR;
2484: l_status_flag := FND_API.G_FALSE;
2485: END IF;
2486: END IF;
2487:
2480: validate_exch_rate(l_return_status);
2481: IF l_return_status = FND_API.G_FALSE THEN
2482: fun_net_util.Log_String(g_event_level,l_path, 'validate_exch_rate returns false');
2483: -- RAISE FND_API.G_EXC_ERROR;
2484: l_status_flag := FND_API.G_FALSE;
2485: END IF;
2486: END IF;
2487:
2488: IF l_status_flag = FND_API.G_TRUE
2484: l_status_flag := FND_API.G_FALSE;
2485: END IF;
2486: END IF;
2487:
2488: IF l_status_flag = FND_API.G_TRUE
2489: THEN
2490: IF l_allow_disc_flag='N' THEN -- FOR NON ESD Agreements
2491: calculate_AP_AR_balances(l_amt_to_net,l_status_flag);
2492: ELSE -- FOR ESD Enabled Agreements
2493: calculate_approx_netted_amount(l_amt_to_net,l_status_flag);
2494: END IF;
2495: END IF;
2496: /* Set the status of the Batch to Selected */
2497: IF l_status_flag = FND_API.G_TRUE THEN
2498: UPDATE fun_net_batches_all
2499: SET batch_status_code = 'SELECTED',
2500: batch_currency = g_batch_list(i).currency,
2501: agreement_id = g_batch_list(i).agreement_id,
2520: IF g_batch_details.review_netting_batch_flag = 'Y'
2521: AND g_batch_details.batch_status_code = 'SELECTED' THEN
2522: fun_net_util.Log_String(g_state_level,l_path,'Submitting Netting Batch');
2523: submit_net_batch (
2524: p_init_msg_list => FND_API.G_FALSE,
2525: p_commit => FND_API.G_FALSE,
2526: x_return_status => l_return_status,
2527: x_msg_count => l_msg_count,
2528: x_msg_data => l_msg_data,
2521: AND g_batch_details.batch_status_code = 'SELECTED' THEN
2522: fun_net_util.Log_String(g_state_level,l_path,'Submitting Netting Batch');
2523: submit_net_batch (
2524: p_init_msg_list => FND_API.G_FALSE,
2525: p_commit => FND_API.G_FALSE,
2526: x_return_status => l_return_status,
2527: x_msg_count => l_msg_count,
2528: x_msg_data => l_msg_data,
2529: p_batch_id => g_batch_id);
2530: END IF;
2531: END LOOP;
2532: -- Standard check of p_commit.
2533: fun_net_util.Log_String(g_state_level,l_path,'p_commit :'|| p_commit);
2534: IF FND_API.To_Boolean( p_commit ) THEN
2535: fun_net_util.Log_String(g_state_level,l_path,'Saving the batches');
2536: COMMIT WORK;
2537: END IF;
2538: EXCEPTION
2535: fun_net_util.Log_String(g_state_level,l_path,'Saving the batches');
2536: COMMIT WORK;
2537: END IF;
2538: EXCEPTION
2539: WHEN FND_API.G_EXC_ERROR THEN
2540: fun_net_util.Log_String(g_state_level,l_path,'netting batch creation failed');
2541: ROLLBACK TO create_net_batch_SP;
2542: batch_status_flag := update_batch_status('ERROR');
2543: x_return_status := FND_API.G_RET_STS_ERROR;
2539: WHEN FND_API.G_EXC_ERROR THEN
2540: fun_net_util.Log_String(g_state_level,l_path,'netting batch creation failed');
2541: ROLLBACK TO create_net_batch_SP;
2542: batch_status_flag := update_batch_status('ERROR');
2543: x_return_status := FND_API.G_RET_STS_ERROR;
2544: -- 10707226
2545: FND_MSG_PUB.Count_And_Get (
2546: p_count => x_msg_count,
2547: p_data => x_msg_data );
2545: FND_MSG_PUB.Count_And_Get (
2546: p_count => x_msg_count,
2547: p_data => x_msg_data );
2548: COMMIT WORK;
2549: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2550: fun_net_util.Log_String(g_state_level,l_path,'netting batch creation failed');
2551: ROLLBACK TO create_net_batch_SP;
2552: batch_status_flag := update_batch_status('ERROR');
2553: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2549: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2550: fun_net_util.Log_String(g_state_level,l_path,'netting batch creation failed');
2551: ROLLBACK TO create_net_batch_SP;
2552: batch_status_flag := update_batch_status('ERROR');
2553: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2554: -- 10707226
2555: FND_MSG_PUB.Count_And_Get (
2556: p_count => x_msg_count,
2557: p_data => x_msg_data );
2559: WHEN OTHERS THEN
2560: fun_net_util.Log_String(g_state_level,l_path,'netting batch creation failed');
2561: ROLLBACK TO Create_Net_Batch_SP;
2562: batch_status_flag := update_batch_status('ERROR');
2563: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2564: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2565: --FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
2566: FND_MSG_PUB.Add_Exc_Msg( 'FUN_ARAP_NET_PKG', 'create_net_batch');
2567: END IF;
2586: from fun_net_batches_all
2587: where batch_id = g_batch_id;
2588: exception
2589: when others then
2590: RAISE FND_API.G_EXC_ERROR;
2591: end;
2592: if l_batch_currency<> g_func_currency then
2593: l_count:=1;
2594: end if;
2612: and rm.receipt_method_id = rma.receipt_method_id
2613: and rma.remit_bank_acct_use_id = ba.bank_acct_use_id;
2614: exception
2615: when others then
2616: RAISE FND_API.G_EXC_ERROR;
2617: end;
2618: if ( l_flag <>'Y' ) then
2619: FND_MESSAGE.SET_NAME('FUN','FUN_NET_MULTI_CUR_FLAG_ERR');
2620: l_msg_data := FND_MESSAGE.GET;
2619: FND_MESSAGE.SET_NAME('FUN','FUN_NET_MULTI_CUR_FLAG_ERR');
2620: l_msg_data := FND_MESSAGE.GET;
2621: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
2622: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2623: RAISE FND_API.G_EXC_ERROR;
2624: end if;
2625: end if;
2626: END IF;
2627: END Validate_multi_currency_flag;
2652: null;
2653: END perform_common_tasks;
2654: PROCEDURE submit_net_batch (
2655: -- ***** Standard API Parameters *****
2656: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2657: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2658: x_return_status OUT NOCOPY VARCHAR2,
2659: x_msg_count OUT NOCOPY NUMBER,
2660: x_msg_data OUT NOCOPY VARCHAR2,
2653: END perform_common_tasks;
2654: PROCEDURE submit_net_batch (
2655: -- ***** Standard API Parameters *****
2656: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2657: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2658: x_return_status OUT NOCOPY VARCHAR2,
2659: x_msg_count OUT NOCOPY NUMBER,
2660: x_msg_data OUT NOCOPY VARCHAR2,
2661: -- ***** Netting batch input parameters *****
2684: /* Intialize message list */
2685: x_msg_count := NULL;
2686: x_msg_data := NULL;
2687: -- **** Initialize message list if p_init_msg_list is set to TRUE. ****
2688: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2689: FND_MSG_PUB.initialize;
2690: END IF;
2691: -- **** Initialize return status to SUCCESS *****
2692: x_return_status := FND_API.G_RET_STS_SUCCESS;
2688: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2689: FND_MSG_PUB.initialize;
2690: END IF;
2691: -- **** Initialize return status to SUCCESS *****
2692: x_return_status := FND_API.G_RET_STS_SUCCESS;
2693: /* Create Save point */
2694: SAVEPOINT submit_net_batch_SP;
2695: /* Validate Mandatory parameters */
2696: /* Get Batch Details for the given batch id*/
2695: /* Validate Mandatory parameters */
2696: /* Get Batch Details for the given batch id*/
2697: IF p_batch_id IS NULL THEN
2698: fun_net_util.Log_String(g_state_level,l_path,'Batch id passed to submit netting batch procedure is null');
2699: RAISE FND_API.G_EXC_ERROR;
2700: END IF;
2701: g_batch_id := p_batch_id;
2702: FND_FILE.PUT_LINE(FND_FILE.LOG,'Submit Netting Batch - Batch ID '||To_char(g_batch_id));
2703: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2702: FND_FILE.PUT_LINE(FND_FILE.LOG,'Submit Netting Batch - Batch ID '||To_char(g_batch_id));
2703: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2704: IF NOT get_batch_details THEN
2705: 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);
2706: RAISE FND_API.G_EXC_ERROR;
2707: END IF;
2708: fun_net_util.Log_String(g_state_level,l_path,'Validating batch');
2709: /* Compares Final AP and AR Balances. Need to check the balance as the batch might have been modified by the user */
2710: g_agreement_id := g_batch_details.agreement_id;
2710: g_agreement_id := g_batch_details.agreement_id;
2711: IF NOT get_agreement_details THEN
2712: fun_net_util.Log_String(g_event_level,l_path,
2713: 'Error getting Agreement details');
2714: RAISE FND_API.G_EXC_ERROR;
2715: END IF;
2716: Validate_multi_currency_flag;
2717: SELECT ALLOW_DISC_FLAG into l_allow_disc_flag FROM FUN_NET_AGREEMENTS_all WHERE Agreement_id=g_agreement_id; -- ADDED FOR ESD
2718: IF l_allow_disc_flag='Y' THEN -- ADDED FOR ESD
2726: FND_MESSAGE.SET_NAME('FUN','FUN_NET_BATCH_NEG_AP_AR_BAL');
2727: l_msg_data := FND_MESSAGE.GET;
2728: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
2729: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2730: RAISE FND_API.G_EXC_ERROR;
2731: END IF;
2732: IF g_net_balance_rule='NET_PAYABLES' and l_ar_bal > l_ap_bal THEN
2733: FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_BAL_LESS_THAN_AP');
2734: l_msg_data := FND_MESSAGE.GET;
2733: FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_BAL_LESS_THAN_AP');
2734: l_msg_data := FND_MESSAGE.GET;
2735: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
2736: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2737: RAISE FND_API.G_EXC_ERROR;
2738: END IF;
2739: IF l_status_flag = FND_API.G_TRUE then
2740: SELECT ALLOW_DISC_FLAG -- ADDED FOR ESD BY SRAMPURE
2741: INTO l_allow_disc_flag
2735: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
2736: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2737: RAISE FND_API.G_EXC_ERROR;
2738: END IF;
2739: IF l_status_flag = FND_API.G_TRUE then
2740: SELECT ALLOW_DISC_FLAG -- ADDED FOR ESD BY SRAMPURE
2741: INTO l_allow_disc_flag
2742: FROM FUN_NET_AGREEMENTS_all
2743: WHERE Agreement_id = g_agreement_id;
2769: END IF;
2770: ELSIF g_batch_details.batch_status_code IN('SELECTED','ERROR') THEN /* TP Approval is not necessary */
2771: /* If TP approval is not necessary call Netting Settlement Date API */
2772: /*l_batch_status := validate_netting_dates(
2773: p_init_msg_list => FND_API.G_FALSE,
2774: p_commit => FND_API.G_FALSE,
2775: x_return_status => l_return_status,
2776: x_msg_count => l_msg_count,
2777: x_msg_data => l_msg_data,
2770: ELSIF g_batch_details.batch_status_code IN('SELECTED','ERROR') THEN /* TP Approval is not necessary */
2771: /* If TP approval is not necessary call Netting Settlement Date API */
2772: /*l_batch_status := validate_netting_dates(
2773: p_init_msg_list => FND_API.G_FALSE,
2774: p_commit => FND_API.G_FALSE,
2775: x_return_status => l_return_status,
2776: x_msg_count => l_msg_count,
2777: x_msg_data => l_msg_data,
2778: p_batch_id => g_batch_id,
2777: x_msg_data => l_msg_data,
2778: p_batch_id => g_batch_id,
2779: p_net_settle_date => g_batch_details.settlement_date,
2780: p_response_date => NULL);
2781: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2782: g_batch_details.batch_status_code := l_batch_status;
2783: END IF; */
2784: UPDATE fun_net_batches
2785: SET batch_status_code = 'APPROVED'
2788: g_batch_details.batch_status_code := 'APPROVED';
2789: END IF;
2790: END IF;
2791: ELSE
2792: RAISE FND_API.G_EXC_ERROR;
2793: END IF;
2794: IF g_batch_details.batch_status_code = 'APPROVED' then
2795: UPDATE fun_net_batches
2796: SET batch_status_code = 'CLEARING'
2807: );
2808: fun_net_util.Log_String(g_state_level,l_path,'Settle batch request:'||l_request_id);
2809: END IF;
2810: -- Standard check of p_commit.
2811: IF FND_API.To_Boolean( p_commit ) THEN
2812: COMMIT WORK;
2813: END IF;
2814: EXCEPTION
2815: WHEN FND_API.G_EXC_ERROR THEN
2811: IF FND_API.To_Boolean( p_commit ) THEN
2812: COMMIT WORK;
2813: END IF;
2814: EXCEPTION
2815: WHEN FND_API.G_EXC_ERROR THEN
2816: ROLLBACK TO submit_net_batch_SP;
2817: l_batch_status_flag := update_batch_status('ERROR');
2818: x_return_status := FND_API.G_RET_STS_ERROR;
2819: FND_MSG_PUB.Count_And_Get (
2814: EXCEPTION
2815: WHEN FND_API.G_EXC_ERROR THEN
2816: ROLLBACK TO submit_net_batch_SP;
2817: l_batch_status_flag := update_batch_status('ERROR');
2818: x_return_status := FND_API.G_RET_STS_ERROR;
2819: FND_MSG_PUB.Count_And_Get (
2820: p_count => x_msg_count,
2821: p_data => x_msg_data );
2822: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2818: x_return_status := FND_API.G_RET_STS_ERROR;
2819: FND_MSG_PUB.Count_And_Get (
2820: p_count => x_msg_count,
2821: p_data => x_msg_data );
2822: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2823: ROLLBACK TO submit_net_batch_SP;
2824: l_batch_status_flag := update_batch_status('ERROR');
2825: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2826: FND_MSG_PUB.Count_And_Get (
2821: p_data => x_msg_data );
2822: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2823: ROLLBACK TO submit_net_batch_SP;
2824: l_batch_status_flag := update_batch_status('ERROR');
2825: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2826: FND_MSG_PUB.Count_And_Get (
2827: p_count => x_msg_count,
2828: p_data => x_msg_data );
2829: WHEN VALIDATE_SETTLEMENT_DATE_FAIL THEN -- ADDED FOR ESD BY SRAMPURE
2829: WHEN VALIDATE_SETTLEMENT_DATE_FAIL THEN -- ADDED FOR ESD BY SRAMPURE
2830: ROLLBACK TO submit_net_batch_SP;
2831: l_batch_status_flag := update_batch_status('CANCELLED');
2832: perform_common_tasks;
2833: x_return_status := FND_API.G_RET_STS_ERROR;
2834: FND_MSG_PUB.Count_And_Get (
2835: p_count => x_msg_count,
2836: p_data => x_msg_data );
2837: WHEN OTHERS THEN
2836: p_data => x_msg_data );
2837: WHEN OTHERS THEN
2838: ROLLBACK TO submit_Net_Batch_SP;
2839: l_batch_status_flag := update_batch_status('ERROR');
2840: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2841: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2842: --FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
2843: FND_MSG_PUB.Add_Exc_Msg( 'FUN_ARAP_NET_PKG', 'submit_net_batch');
2844: END IF;
2847: p_data => x_msg_data );
2848: END submit_net_batch;
2849: FUNCTION Validate_Netting_Dates(
2850: -- ***** Standard API Parameters *****
2851: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2852: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2853: x_return_status OUT NOCOPY VARCHAR2,
2854: x_msg_count OUT NOCOPY NUMBER,
2855: x_msg_data OUT NOCOPY VARCHAR2,
2848: END submit_net_batch;
2849: FUNCTION Validate_Netting_Dates(
2850: -- ***** Standard API Parameters *****
2851: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2852: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2853: x_return_status OUT NOCOPY VARCHAR2,
2854: x_msg_count OUT NOCOPY NUMBER,
2855: x_msg_data OUT NOCOPY VARCHAR2,
2856: -- ***** Netting batch input parameters *****
2868: /* Initialize standard API parameters */
2869: x_msg_count := NULL;
2870: x_msg_data := NULL;
2871: -- **** Initialize message list if p_init_msg_list is set to TRUE. ****
2872: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2873: FND_MSG_PUB.initialize;
2874: END IF;
2875: -- **** Initialize return status to SUCCESS *****
2876: x_return_status := FND_API.G_RET_STS_SUCCESS;
2872: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2873: FND_MSG_PUB.initialize;
2874: END IF;
2875: -- **** Initialize return status to SUCCESS *****
2876: x_return_status := FND_API.G_RET_STS_SUCCESS;
2877: /* Create Save point */
2878: SAVEPOINT validate_netting_dates_SP;
2879: /* Validate Mandatory parameters */
2880: /* Get Batch Details for the given batch id*/
2879: /* Validate Mandatory parameters */
2880: /* Get Batch Details for the given batch id*/
2881: IF p_batch_id IS NULL OR (p_net_settle_date IS NULL AND p_response_date IS NULL) THEN
2882: fun_net_util.Log_String(g_state_level,l_path,'One of the mandatory arguments passed to the validate_netting_dates procedure is null');
2883: RAISE FND_API.G_EXC_ERROR;
2884: END IF;
2885: g_batch_id := p_batch_id;
2886: /* Validate NSD with the date of
2887: when the Netting Batch is changed from Submitted to Approved.
2891: IF TRUNC(p_net_settle_date) < TRUNC(SYSDATE) THEN
2892: 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');
2893: --Error ' Batch has gone past the Netting Settlement Date';
2894: IF NOT Update_batch_status('SUSPENDED') THEN
2895: RAISE FND_API.G_EXC_ERROR;
2896: ELSE
2897: RETURN 'SUSPENDED';
2898: END IF;
2899: ELSE
2898: END IF;
2899: ELSE
2900: IF NOT update_batch_status ('APPROVED') THEN
2901: fun_net_util.Log_String(g_state_level,l_path,'Unable to update the batch status to approved');
2902: RAISE FND_API.G_EXC_ERROR;
2903: ELSE
2904: RETURN 'APPROVED';
2905: END IF;
2906: END IF;
2912: OPEN c_non_response_action;
2913: FETCH c_non_response_action INTO l_non_response_action;
2914: CLOSE c_non_response_action;
2915: IF l_non_response_action IS NULL THEN
2916: RAISE FND_API.G_EXC_ERROR;
2917: END IF;
2918: IF TRUNC(p_response_date) < TRUNC(SYSDATE) THEN
2919: IF l_non_response_action = 'APPROVE' then
2920: IF NOT Update_batch_status('APPROVED') THEN
2917: END IF;
2918: IF TRUNC(p_response_date) < TRUNC(SYSDATE) THEN
2919: IF l_non_response_action = 'APPROVE' then
2920: IF NOT Update_batch_status('APPROVED') THEN
2921: RAISE FND_API.G_EXC_ERROR;
2922: END IF;
2923: RETURN 'APPROVED';
2924: ELSIF l_non_response_action = 'REJECT' THEN
2925: IF NOT Update_batch_status('REJECTED') THEN
2922: END IF;
2923: RETURN 'APPROVED';
2924: ELSIF l_non_response_action = 'REJECT' THEN
2925: IF NOT Update_batch_status('REJECTED') THEN
2926: RAISE FND_API.G_EXC_ERROR;
2927: END IF;
2928: RETURN 'REJECTED';
2929: END IF;
2930: END IF;
2929: END IF;
2930: END IF;
2931: END IF;
2932: -- Standard check of p_commit.
2933: IF FND_API.To_Boolean( p_commit ) THEN
2934: COMMIT WORK;
2935: END IF;
2936: EXCEPTION
2937: /* Handle standard exceptions */
2934: COMMIT WORK;
2935: END IF;
2936: EXCEPTION
2937: /* Handle standard exceptions */
2938: WHEN FND_API.G_EXC_ERROR THEN
2939: ROLLBACK TO validate_netting_dates_SP;
2940: x_return_status := FND_API.G_RET_STS_ERROR;
2941: FND_MSG_PUB.Count_And_Get (
2942: p_count => x_msg_count,
2936: EXCEPTION
2937: /* Handle standard exceptions */
2938: WHEN FND_API.G_EXC_ERROR THEN
2939: ROLLBACK TO validate_netting_dates_SP;
2940: x_return_status := FND_API.G_RET_STS_ERROR;
2941: FND_MSG_PUB.Count_And_Get (
2942: p_count => x_msg_count,
2943: p_data => x_msg_data );
2944: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2940: x_return_status := FND_API.G_RET_STS_ERROR;
2941: FND_MSG_PUB.Count_And_Get (
2942: p_count => x_msg_count,
2943: p_data => x_msg_data );
2944: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2945: ROLLBACK TO validate_netting_dates_SP;
2946: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2947: FND_MSG_PUB.Count_And_Get (
2948: p_count => x_msg_count,
2942: p_count => x_msg_count,
2943: p_data => x_msg_data );
2944: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2945: ROLLBACK TO validate_netting_dates_SP;
2946: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2947: FND_MSG_PUB.Count_And_Get (
2948: p_count => x_msg_count,
2949: p_data => x_msg_data );
2950: WHEN OTHERS THEN
2948: p_count => x_msg_count,
2949: p_data => x_msg_data );
2950: WHEN OTHERS THEN
2951: ROLLBACK TO validate_netting_dates_SP;
2952: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2953: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2954: --FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
2955: FND_MSG_PUB.Add_Exc_Msg( 'FUN_ARAP_NET_PKG', 'validate_netting_dates');
2956: END IF;
2965: x_return_status OUT NOCOPY VARCHAR2)
2966: IS
2967: l_agreement_id fun_net_agreements.agreement_id%TYPE;
2968: BEGIN
2969: x_return_status := FND_API.G_TRUE;
2970: IF x_agreement_id IS NOT NULL THEN
2971: SELECT agreement_id
2972: INTO l_agreement_id
2973: FROM fun_net_batches
2978: 'SUBMITTED',
2979: 'REVERSING',
2980: 'CLEARING');
2981: ELSE
2982: x_return_status := FND_API.G_FALSE;
2983: END IF;
2984: EXCEPTION
2985: WHEN TOO_MANY_ROWS THEN
2986: null;
2994: x_agreement_id => x_agreement_id,
2995: x_in_process_flag => 'N');
2996: END IF;
2997: WHEN OTHERS THEN
2998: x_return_status := FND_API.G_FALSE;
2999: END Set_Agreement_Status;
3000: /* Derive Bank Account Information. Find the Bank Account Use Id
3001: from the Bank Account Id. Netting Bank is stored in the agreement details */
3002: PROCEDURE Get_Netting_Bank_Details(
3007: p_bank_num OUT NOCOPY VARCHAR2,
3008: p_return_status OUT NOCOPY VARCHAR2)
3009: IS
3010: BEGIN
3011: p_return_status := FND_API.G_TRUE;
3012: SELECT bank_acct_use_id ,
3013: ac.bank_account_name,
3014: ac.bank_account_num,
3015: ac.account_owner_org_id,
3027: AND ac_use.org_id = g_batch_details.org_id
3028: AND ba.bank_party_id = ac.bank_id;
3029: EXCEPTION
3030: WHEN OTHERS THEN
3031: p_return_status := FND_API.G_FALSE;
3032: END Get_Netting_Bank_Details;
3033: /* Get the Customer Details to Create Receipt . If there is more than
3034: one customer with the same priority then select the customer with
3035: minimum customer id */
3070:
3071: BEGIN
3072: l_path := g_path||'Get_Customer_Details';
3073:
3074: p_return_status := FND_API.G_TRUE;
3075: p_cust_site_use_id := NULL;
3076: /* Select First Customer Account on Receipt */
3077: --Bug:12982007
3078: SELECT min(cust_account_id), cust_priority
3107: END IF;
3108:
3109: EXCEPTION
3110: WHEN OTHERS THEN
3111: p_return_status := FND_API.G_FALSE;
3112: END Get_Customer_Details;
3113: /* Unlocks Payment Schedule lines */
3114: PROCEDURE unlock_ap_pymt_schedules(
3115: p_batch_id IN fun_net_batches.batch_id%TYPE,
3119: l_checkrun_id ap_inv_selection_criteria_all.checkrun_id%TYPE;
3120: l_path varchar2(200);
3121: l_org_id NUMBER(15);
3122: BEGIN
3123: x_return_status := FND_API.G_TRUE;
3124: l_path := g_path||'unlock_payment_schedules';
3125: -- Check for Mandatory Parameters
3126: IF p_batch_id IS NULL THEN
3127: x_return_status := FND_API.G_FALSE;
3123: x_return_status := FND_API.G_TRUE;
3124: l_path := g_path||'unlock_payment_schedules';
3125: -- Check for Mandatory Parameters
3126: IF p_batch_id IS NULL THEN
3127: x_return_status := FND_API.G_FALSE;
3128: END IF;
3129: -- Select the checkrun ID for the
3130: -- Netting Batch
3131: SELECT checkrun_id , org_id
3148: IF SQL%FOUND THEN
3149: COMMIT;
3150: ELSE
3151: ROLLBACK;
3152: x_return_status := FND_API.G_FALSE;
3153: RETURN;
3154: END IF;
3155: EXCEPTION
3156: WHEN OTHERS THEN
3154: END IF;
3155: EXCEPTION
3156: WHEN OTHERS THEN
3157: fun_net_util.Log_String(g_state_level,l_path,'error while unlocking payment schedules');
3158: x_return_status := FND_API.G_FALSE;
3159: END unlock_ap_pymt_schedules;
3160: /* Derives Netting Exchange Rate . Returns 1 if the from and the to
3161: currency are the same . If the Rate type = 'User' then derives the
3162: rate from fun_net_batches table */
3221: l_path varchar2(200);
3222: BEGIN
3223: l_path := g_path||'validate_settlement_period';
3224: /* Check if GL Period is open*/
3225: x_return_status := FND_API.G_TRUE;
3226: SELECT set_of_books_id
3227: INTO l_ledger_id
3228: FROM hr_operating_units
3229: WHERE organization_id = g_batch_details.org_id;
3237: x_period_num,
3238: x_period_type);
3239: IF (x_period_name IS NULL and x_closing_status IS NULL) OR
3240: x_closing_status not in ('O','F') THEN
3241: x_return_status := FND_API.G_FALSE;
3242: END IF;
3243: EXCEPTION
3244: WHEN OTHERS THEN
3245: 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 ');
3242: END IF;
3243: EXCEPTION
3244: WHEN OTHERS THEN
3245: 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 ');
3246: x_return_status := FND_API.G_FALSE;
3247: END Validate_Settlement_Period;
3248: /* Creates AP Check per vendor / vendor site / currency . Also creates
3249: invoice payments for each AP Check */
3250: PROCEDURE settle_ap_invs(
3288: ap_check_rec ap_checks_all%ROWTYPE;
3289: p_schd_tab pymt_sch_tab;
3290: BEGIN
3291: -- Group Invoices by Vendor , Vendor Site and Payment Currency .
3292: x_return_status := FND_API.G_TRUE;
3293: BEGIN
3294: l_path := g_path || 'Settle AP Transactions';
3295: fun_net_util.Log_String(g_event_level,l_path,'Group AP Invoices');
3296: SELECT
3320: inv.payment_currency_code;
3321: EXCEPTION
3322: WHEN OTHERS THEN
3323: fun_net_util.Log_String(g_event_level,l_path,sqlcode || sqlerrm);
3324: RAISE FND_API.G_EXC_ERROR;
3325: END;
3326: fun_net_util.Log_String(g_event_level,l_path,'Processing Txns by Vendor'||
3327: vendor_list.count);
3328: FOR i IN 1..vendor_list.count
3418: AND apps.hold_flag = 'N'
3419: ORDER BY inv.invoice_id,apps.payment_num;
3420: EXCEPTION
3421: WHEN OTHERS THEN
3422: RAISE FND_API.G_EXC_ERROR;
3423: END;
3424: m := 0;
3425: IF (ap_invoice.EXISTS(1)) THEN
3426: ap_invoice.DELETE;
3525: fun_net_util.Log_String(g_event_level,
3526: l_path,
3527: 'Check ID :'||l_check_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
3540: END;
3541: END LOOP;
3542: EXCEPTION
3543: WHEN OTHERS THEN
3544: x_return_status := FND_API.G_FALSE;
3545: RETURN;
3546: END;
3547: /* Calculates the Total Open Amount for an AR Transaction based on the
3548: Due date of the batch */
3573: txnCur IN OUT NOCOPY txnCurType,
3574: x_return_status OUT NOCOPY VARCHAR2)
3575: IS
3576: BEGIN
3577: x_return_status := FND_API.G_TRUE;
3578: OPEN txnCur FOR
3579: SELECT trx.invoice_currency_code AS invoice_currency_code,
3580: trx.customer_trx_id AS customer_trx_id,
3581: trx.trx_number AS trx_number,
3609: -- FOR UPDATE of ftxn.batch_id, trx.customer_trx_id,trx_line.customer_trx_id;
3610: -- arps.payment_schedule_id;
3611: EXCEPTION
3612: WHEN OTHERS THEN
3613: x_return_status := FND_API.G_FALSE;
3614: END;
3615: */
3616: /* Validates the following
3617: The current Invoice Amount and the Open Amount against the Amount
3627: l_path VARCHAR2(100);
3628: round_diff ra_customer_trx_lines.extended_amount%TYPE;
3629: l_count_test number;
3630: BEGIN
3631: x_return_status := FND_API.G_TRUE;
3632: l_path := g_path || 'Validate AR Transactions';
3633: fun_net_util.Log_String(g_event_level,l_path,
3634: 'Validating AR Transactions');
3635: IF get_esd_flag(g_batch_details.batch_id)='Y' THEN
3769: -- compare the transaction amounts in transaction currecy between the fun tables and the transaction tables.
3770: -- IF txnTable(i).txn_amt <> l_inv_amt THEN Commented for bug 5326485
3771: IF txnTable(i).txn_curr_amt <> txnTable(i).txn_amt THEN
3772: IF abs(round_diff) > 0.01 THEN
3773: x_return_status := FND_API.G_FALSE;
3774: EXIT;
3775: END IF;
3776: END IF;
3777: -- Compare the open amounts in transaction currency between the fun tables and the payment schedules tables.
3781: fun_net_util.Log_String(g_event_level,l_path,
3782: 'Txn CurrOpen Amt fail2 ' || txnTable(i).txn_curr_open_amt);
3783: fun_net_util.Log_String(g_event_level,l_path,
3784: 'Amt Remaining fail2 ' || txnTable(i).amt_remaining);
3785: x_return_status := FND_API.G_FALSE;
3786: EXIT;
3787: END IF;
3788: -- If the Payment Type = CREDIT CARD then raise error
3789: IF txnTable(i).payment_type_code = 'CREDIT_CARD' THEN
3786: EXIT;
3787: END IF;
3788: -- If the Payment Type = CREDIT CARD then raise error
3789: IF txnTable(i).payment_type_code = 'CREDIT_CARD' THEN
3790: x_return_status := FND_API.G_FALSE;
3791: EXIT;
3792: END IF;
3793: END LOOP;
3794: ELSE
3791: EXIT;
3792: END IF;
3793: END LOOP;
3794: ELSE
3795: x_return_status := FND_API.G_FALSE;
3796: END IF;
3797: EXCEPTION
3798: WHEN OTHERS THEN
3799: x_return_status := FND_API.G_FALSE;
3795: x_return_status := FND_API.G_FALSE;
3796: END IF;
3797: EXCEPTION
3798: WHEN OTHERS THEN
3799: x_return_status := FND_API.G_FALSE;
3800: END Validate_AR_Txns;
3801: PROCEDURE create_cash_receipt(
3802: pARReceiptRec IN arReceiptRecType,
3803: x_cash_receipt_id OUT NOCOPY ar_cash_receipts.cash_receipt_id%TYPE,
3816: fun_net_util.log_string(g_event_level,l_path,'amount:'||pARReceiptRec.amount);
3817: AR_RECEIPT_API_PUB.Create_cash(
3818: -- Standard API parameters.
3819: p_api_version => 1.0,
3820: p_init_msg_list => FND_API.G_TRUE,
3821: p_commit => FND_API.G_FALSE,
3822: x_return_status => x_return_status,
3823: x_msg_count => x_msg_count,
3824: x_msg_data => x_msg_data,
3817: AR_RECEIPT_API_PUB.Create_cash(
3818: -- Standard API parameters.
3819: p_api_version => 1.0,
3820: p_init_msg_list => FND_API.G_TRUE,
3821: p_commit => FND_API.G_FALSE,
3822: x_return_status => x_return_status,
3823: x_msg_count => x_msg_count,
3824: x_msg_data => x_msg_data,
3825: -- Receipt info. Parameters
3841: p_org_id => pARReceiptRec.org_id,
3842: p_customer_receipt_reference => g_batch_details.batch_number,
3843: -- ** OUT NOCOPY variables
3844: p_cr_id => x_cash_receipt_id);
3845: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3846: fun_net_util.log_string(g_event_level,l_path
3847: ,'Cash receipt creation failed either because AR_RECEIPT_API_PUB. Creat_cash failed or there is no receipt class associated ');
3848: FND_MSG_PUB.Count_And_Get (
3849: p_count => x_msg_count,
3849: p_count => x_msg_count,
3850: p_data => x_msg_data );
3851: IF x_msg_count > 1 THEN
3852: FOR x IN 1..x_msg_count LOOP
3853: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
3854: FND_FILE.PUT_LINE(FND_FILE.LOG,x_msg_data);
3855: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3856: fun_net_util.Log_String(g_unexp_level,l_path,'Error message:' ||x_msg_data);
3857: END LOOP;
3855: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3856: fun_net_util.Log_String(g_unexp_level,l_path,'Error message:' ||x_msg_data);
3857: END LOOP;
3858: END IF;
3859: x_return_status := FND_API.G_FALSE;
3860: RETURN;
3861: END IF;
3862: EXCEPTION
3863: WHEN OTHERS THEN
3860: RETURN;
3861: END IF;
3862: EXCEPTION
3863: WHEN OTHERS THEN
3864: x_return_status := FND_API.G_FALSE;
3865: END create_cash_receipt;
3866: /*PROCEDURE apply2_cash_receipt
3867: (txnTable IN txnTblType,
3868: p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
3908: AND due_date between g_agr_start_date and g_agr_end_date
3909: AND ps.status = 'OP';
3910: l_open_amt := txnTable(i).txn_curr_open_amt;
3911: IF l_open_amt <> current_amt_due THEN
3912: x_return_status := FND_API.G_FALSE;
3913: EXIT;
3914: END IF;
3915: FOR apply_rec in apply_trx_cur(txnTable(i).customer_trx_id)
3916: LOOP
3933: END IF;
3934: AR_RECEIPT_API_PUB.Apply(
3935: -- Standard API parameters.
3936: p_api_version => 1.0,
3937: p_init_msg_list => FND_API.G_FALSE,
3938: p_commit => FND_API.G_FALSE,
3939: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3940: x_return_status => l_return_status,
3941: x_msg_count => l_msg_count,
3934: AR_RECEIPT_API_PUB.Apply(
3935: -- Standard API parameters.
3936: p_api_version => 1.0,
3937: p_init_msg_list => FND_API.G_FALSE,
3938: p_commit => FND_API.G_FALSE,
3939: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3940: x_return_status => l_return_status,
3941: x_msg_count => l_msg_count,
3942: x_msg_data => l_msg_data,
3935: -- Standard API parameters.
3936: p_api_version => 1.0,
3937: p_init_msg_list => FND_API.G_FALSE,
3938: p_commit => FND_API.G_FALSE,
3939: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3940: x_return_status => l_return_status,
3941: x_msg_count => l_msg_count,
3942: x_msg_data => l_msg_data,
3943: -- Receipt application parameters.
3955: p_customer_trx_line_id => apply_rec.cust_trx_line_id,
3956: p_org_id => g_batch_details.org_id
3957: );
3958: dbms_output.put_line('Apply' || l_return_status);
3959: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3960: x_return_status := FND_API.G_FALSE;
3961: RETURN;
3962: END IF;
3963: END LOOP;
3956: p_org_id => g_batch_details.org_id
3957: );
3958: dbms_output.put_line('Apply' || l_return_status);
3959: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3960: x_return_status := FND_API.G_FALSE;
3961: RETURN;
3962: END IF;
3963: END LOOP;
3964: END LOOP;
3963: END LOOP;
3964: END LOOP;
3965: EXCEPTION
3966: WHEN OTHERS THEN
3967: x_return_status := FND_API.G_FALSE;
3968: END apply2_cash_receipt; */
3969: PROCEDURE apply_cash_receipt
3970: (txnTable IN txnTblType,
3971: p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
4065: FROM AR_PAYMENT_SCHEDULES APS
4066: WHERE APS.customer_Trx_id = txnTable(i).customer_Trx_id;
4067: l_open_amt := txnTable(i).txn_curr_open_amt;
4068: IF l_open_amt <> current_amt_due THEN
4069: x_return_status := FND_API.G_FALSE;
4070: EXIT;
4071: END IF;
4072: -- Exchange rate related logic for Bug 5463394
4073: IF txnTable(i).invoice_currency_code <> g_func_currency THEN
4168: AND trx_line.line_type IN ('LINE')
4169: AND nvl(trx_line.amount_due_remaining,trx_line.extended_amount) <> 0 ;
4170: EXCEPTION
4171: WHEN OTHERS THEN
4172: RAISE FND_API.G_EXC_ERROR;
4173: END;
4174: ELSE
4175: BEGIN
4176: SELECT trx_line.customer_trx_line_id AS cust_trx_line_id,
4187: AND trx_line.line_type IN ('LINE')
4188: AND nvl(trx_line.amount_due_remaining,trx_line.extended_amount) <> 0 ;
4189: EXCEPTION
4190: WHEN OTHERS THEN
4191: RAISE FND_API.G_EXC_ERROR;
4192: END;
4193: END IF;
4194: -- get the freight amount
4195: SELECT round(SUM((nvl(trx_line.amount_due_remaining,trx_line.extended_amount)
4231: fun_net_util.Log_String(g_event_level,l_path,'Calling APPLY IN DETAIL');
4232: AR_RECEIPT_API_PUB.Apply_In_Detail(
4233: -- Standard API parameters.
4234: p_api_version => 1.0,
4235: p_init_msg_list => FND_API.G_FALSE,
4236: p_commit => FND_API.G_FALSE,
4237: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4238: x_return_status => l_return_status,
4239: x_msg_count => l_msg_count,
4232: AR_RECEIPT_API_PUB.Apply_In_Detail(
4233: -- Standard API parameters.
4234: p_api_version => 1.0,
4235: p_init_msg_list => FND_API.G_FALSE,
4236: p_commit => FND_API.G_FALSE,
4237: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4238: x_return_status => l_return_status,
4239: x_msg_count => l_msg_count,
4240: x_msg_data => l_msg_data,
4233: -- Standard API parameters.
4234: p_api_version => 1.0,
4235: p_init_msg_list => FND_API.G_FALSE,
4236: p_commit => FND_API.G_FALSE,
4237: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4238: x_return_status => l_return_status,
4239: x_msg_count => l_msg_count,
4240: x_msg_data => l_msg_data,
4241: -- Receipt application parameters.
4257: fun_net_util.Log_String(g_event_level,l_path,'txnTable(i).customer_trx_id = ' || txnTable(i).customer_trx_id );
4258: fun_net_util.Log_String(g_event_level,l_path,'Calling APPLY');
4259: Ar_receipt_api_pub.apply(
4260: p_api_version => 1.0,
4261: p_init_msg_list => FND_API.G_TRUE,
4262: p_cash_receipt_id => p_cash_receipt_id,
4263: p_customer_trx_id => txnTable(i).customer_trx_id,
4264: p_applied_payment_schedule_id => apply_rec.pymt_schedule_id,
4265: x_return_status => l_return_status,
4267: x_msg_data => l_msg_data
4268: );
4269: END IF;
4270: fun_net_util.Log_String(g_event_level,l_path,'After Calling APPLY/APPLY IN DETAIL');
4271: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4272: FND_MSG_PUB.Count_And_Get (
4273: p_count => l_msg_count,
4274: p_data => l_msg_data );
4275: fun_net_util.Log_String(g_event_level,l_path
4275: fun_net_util.Log_String(g_event_level,l_path
4276: ,'apply cash receipt package after AR_RECEIPT_API_PUB.Apply:' ||l_msg_data);
4277: IF l_msg_count > 1 THEN
4278: FOR x IN 1..l_msg_count LOOP
4279: l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
4280: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
4281: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4282: fun_net_util.Log_String(g_event_level,l_path
4283: ,'apply cash receipt package Error message AR_RECEIPT_API_PUB.Apply' ||l_msg_data||' '||' '||x);
4282: fun_net_util.Log_String(g_event_level,l_path
4283: ,'apply cash receipt package Error message AR_RECEIPT_API_PUB.Apply' ||l_msg_data||' '||' '||x);
4284: END LOOP;
4285: END IF;
4286: x_return_status := FND_API.G_FALSE;
4287: RETURN;
4288: END IF;
4289: END LOOP;
4290: END LOOP;
4289: END LOOP;
4290: END LOOP;
4291: EXCEPTION
4292: WHEN OTHERS THEN
4293: x_return_status := FND_API.G_FALSE;
4294: END apply_cash_receipt;
4295: /******************************************************************************
4296: |PROCEDURE settle_ar_txns : Settles AR Transactions for the given batch |
4297: | |
4327: Get_Customer_Details (p_cust_acct_id => l_cust_acct_id,
4328: p_cust_site_use_id => l_cust_site_use_id,
4329: p_return_status => l_return_status
4330: );
4331: IF l_return_status = FND_API.G_FALSE then
4332: fun_net_util.Log_String(g_event_level,
4333: l_path
4334: ,'No Customer Details');
4335: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_CUST_DETAILS');
4335: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_CUST_DETAILS');
4336: l_msg_data := FND_MESSAGE.GET;
4337: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
4338: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4339: x_return_status := FND_API.G_FALSE;
4340: RETURN;
4341: ELSE
4342: fun_net_util.Log_String(g_event_level,
4343: l_path
4358: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_RECEIPT_METHOD');
4359: l_msg_data := FND_MESSAGE.GET;
4360: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
4361: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4362: x_return_status := FND_API.G_FALSE;
4363: RETURN;
4364: END;
4365: BEGIN
4366: SELECT remit_bank_acct_use_id
4377: FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_BANK_ACCT_MISSING');
4378: l_msg_data := FND_MESSAGE.GET;
4379: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
4380: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4381: x_return_status := FND_API.G_FALSE;
4382: RETURN;
4383: END;
4384: -- Set all inputs to create cash receipt --
4385: arReceiptRec.currency_code := g_batch_details.batch_currency;
4421: ,'After Create Cash Receipt');
4422: fun_net_util.Log_String(g_event_level,
4423: l_path
4424: ,x_msg_data|| ' with message ' || x_msg_count);
4425: IF x_return_status = FND_API.G_FALSE THEN
4426: fun_net_util.Log_String(g_event_level,
4427: l_path
4428: ,'Cash Receipt Creation Failed');
4429: FND_MESSAGE.SET_NAME('FUN','FUN_NET_CASH_RECEIPT_FAIL');
4546: (txnTable => txnTable,
4547: p_cash_receipt_id => l_cash_receipt_id,
4548: x_return_status => x_return_status);
4549: END IF;
4550: IF x_return_status = FND_API.G_FALSE THEN
4551: FND_MESSAGE.SET_NAME('FUN','FUN_NET_APPLY_RECEIPT_FAIL');
4552: l_msg_data := FND_MESSAGE.GET;
4553: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
4554: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4566: WHEN OTHERS THEN
4567: fun_net_util.Log_String(g_event_level,
4568: l_path
4569: ,'Failed to associate AR transactions in the batch with cash receipt');
4570: x_return_status := FND_API.G_FALSE;
4571: END;
4572: END IF;
4573: EXCEPTION
4574: WHEN OTHERS THEN
4574: WHEN OTHERS THEN
4575: fun_net_util.Log_String(g_event_level,
4576: l_path
4577: ,'Unknown error in settle_ar_txns procedure');
4578: x_return_status := FND_API.G_FALSE;
4579: END settle_ar_txns;
4580:
4581: /***************************************************************************
4582: |PROCEDURE apply_cash_receipt : Applies a Receipt to AR transactions in the|
4593: END IF;
4594: IF g_batch_details.exchange_rate_type IS NULL THEN
4595: g_batch_details.exchange_rate_type := 'Corporate';
4596: END IF;
4597: RETURN FND_API.G_TRUE;
4598: EXCEPTION
4599: WHEN OTHERS THEN
4600: RETURN FND_API.G_FALSE;
4601: END default_batch_details;
4596: END IF;
4597: RETURN FND_API.G_TRUE;
4598: EXCEPTION
4599: WHEN OTHERS THEN
4600: RETURN FND_API.G_FALSE;
4601: END default_batch_details;
4602: PROCEDURE settle_net_batch (
4603: -- ***** Standard API Parameters *****
4604: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
4600: RETURN FND_API.G_FALSE;
4601: END default_batch_details;
4602: PROCEDURE settle_net_batch (
4603: -- ***** Standard API Parameters *****
4604: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
4605: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4606: x_return_status OUT NOCOPY VARCHAR2,
4607: x_msg_count OUT NOCOPY NUMBER,
4608: x_msg_data OUT NOCOPY VARCHAR2,
4601: END default_batch_details;
4602: PROCEDURE settle_net_batch (
4603: -- ***** Standard API Parameters *****
4604: p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
4605: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4606: x_return_status OUT NOCOPY VARCHAR2,
4607: x_msg_count OUT NOCOPY NUMBER,
4608: x_msg_data OUT NOCOPY VARCHAR2,
4609: -- ***** Netting batch input parameters *****
4634: /* Intialize message list */
4635: x_msg_count := NULL;
4636: x_msg_data := NULL;
4637: -- **** Initialize message list if p_init_msg_list is set to TRUE. ****
4638: IF FND_API.to_Boolean( p_init_msg_list ) THEN
4639: FND_MSG_PUB.initialize;
4640: END IF;
4641: -- **** Initialize return status to SUCCESS *****
4642: x_return_status := FND_API.G_RET_STS_SUCCESS;
4638: IF FND_API.to_Boolean( p_init_msg_list ) THEN
4639: FND_MSG_PUB.initialize;
4640: END IF;
4641: -- **** Initialize return status to SUCCESS *****
4642: x_return_status := FND_API.G_RET_STS_SUCCESS;
4643: /* Create Save point */
4644: SAVEPOINT settle_net_batch_SP;
4645: /* Get Batch Details for the given batch id*/
4646: FND_FILE.PUT_LINE(FND_FILE.LOG,'Settle Netting Batch-Batch ID '||To_char(p_batch_id));
4652: FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4653: l_msg_data := FND_MESSAGE.GET;
4654: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
4655: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4656: RAISE FND_API.G_EXC_ERROR;
4657: END IF;
4658: g_batch_id := p_batch_id;
4659: IF NOT get_batch_details THEN
4660: fun_net_util.Log_String(g_event_level,l_path,
4662: FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4663: l_msg_data := FND_MESSAGE.GET;
4664: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
4665: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4666: RAISE FND_API.G_EXC_ERROR;
4667: END IF;
4668: g_agreement_id := g_batch_details.agreement_id;
4669: IF NOT get_agreement_details THEN
4670: fun_net_util.Log_String(g_event_level,l_path,
4668: g_agreement_id := g_batch_details.agreement_id;
4669: IF NOT get_agreement_details THEN
4670: fun_net_util.Log_String(g_event_level,l_path,
4671: 'Error getting Agreement details');
4672: RAISE FND_API.G_EXC_ERROR;
4673: END IF;
4674: IF g_batch_details.batch_status_code NOT IN ( 'CLEARING',
4675: 'SUBMITTED') THEN
4676: fun_net_util.Log_String(g_event_level,l_path,
4678: FND_MESSAGE.SET_NAME('FUN','FUN_NET_BATCH_STATUS_ERROR');
4679: l_msg_data := FND_MESSAGE.GET;
4680: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
4681: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4682: RAISE FND_API.G_EXC_ERROR;
4683: END IF;
4684: /* Get Functional Currency */
4685: get_functional_currency;
4686: /* Default Batch Details */
4683: END IF;
4684: /* Get Functional Currency */
4685: get_functional_currency;
4686: /* Default Batch Details */
4687: IF default_batch_details = FND_API.G_FALSE THEN
4688: fun_net_util.Log_String(g_event_level,l_path,
4689: 'Error defaulting Batch Details');
4690: FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4691: l_msg_data := FND_MESSAGE.GET;
4690: FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4691: l_msg_data := FND_MESSAGE.GET;
4692: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
4693: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4694: RAISE FND_API.G_EXC_ERROR;
4695: END IF;
4696: -- Calculate Netting balances
4697: fun_net_util.Log_String(g_event_level,l_path,'Before Selecting' || g_agreement_id);
4698: SELECT ALLOW_DISC_FLAG into l_allow_disc_flag FROM FUN_NET_AGREEMENTS_all WHERE Agreement_id=g_agreement_id; -- ADDED FOR ESD
4701: ELSE
4702: calculate_AP_AR_balances(l_amt_to_net,l_status_flag);
4703: END IF;
4704: fun_net_util.Log_String(g_event_level,l_path,'Before Selecting' || l_allow_disc_flag);
4705: IF l_status_flag = FND_API.G_FALSE THEN
4706: RAISE FND_API.G_EXC_ERROR;
4707: END IF;
4708: /* Validate AR Transactions to check if they have changed */
4709: Validate_AR_Txns(
4702: calculate_AP_AR_balances(l_amt_to_net,l_status_flag);
4703: END IF;
4704: fun_net_util.Log_String(g_event_level,l_path,'Before Selecting' || l_allow_disc_flag);
4705: IF l_status_flag = FND_API.G_FALSE THEN
4706: RAISE FND_API.G_EXC_ERROR;
4707: END IF;
4708: /* Validate AR Transactions to check if they have changed */
4709: Validate_AR_Txns(
4710: txnTable => txnTable,
4709: Validate_AR_Txns(
4710: txnTable => txnTable,
4711: x_return_status => l_return_status
4712: );
4713: IF l_return_status = FND_API.G_FALSE THEN
4714: fun_net_util.Log_String(g_event_level,l_path,
4715: 'Error Validating AR Transactions');
4716: FND_MESSAGE.SET_NAME('FUN','FUN_NET_VAL_AR_TXN_FAIL');
4717: l_msg_data := FND_MESSAGE.GET;
4724: x_appln_id => 200,
4725: x_period_name => l_period_name,
4726: x_return_status => l_return_status ,
4727: x_return_msg => l_msg_data);
4728: IF l_return_status = FND_API.G_FALSE THEN
4729: fun_net_util.Log_String(g_event_level,l_path,
4730: 'Error Validating AP Period');
4731: FND_MESSAGE.SET_NAME('FUN','FUN_NET_AP_PERIOD_FAIL');
4732: FND_MESSAGE.SET_TOKEN('SETTLE_DATE',
4733: g_batch_details.settlement_date,FALSE);
4734: l_msg_data := FND_MESSAGE.GET;
4735: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
4736: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4737: RAISE FND_API.G_EXC_ERROR;
4738: END IF;
4739: /* Validate GL Period - AR */
4740: Validate_Settlement_Period
4741: (x_appln_id => 222,
4741: (x_appln_id => 222,
4742: x_period_name => l_period_name,
4743: x_return_status => l_return_status ,
4744: x_return_msg => l_msg_data);
4745: IF l_return_status = FND_API.G_FALSE THEN
4746: fun_net_util.Log_String(g_event_level,l_path,
4747: 'Error Validating AR Period');
4748: FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_PERIOD_FAIL');
4749: FND_MESSAGE.SET_TOKEN('SETTLE_DATE',
4750: g_batch_details.settlement_date,FALSE);
4751: l_msg_data := FND_MESSAGE.GET;
4752: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
4753: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4754: RAISE FND_API.G_EXC_ERROR;
4755: END IF;
4756: Validate_Settlement_Period
4757: (x_appln_id => 101,
4758: x_period_name => l_period_name,
4757: (x_appln_id => 101,
4758: x_period_name => l_period_name,
4759: x_return_status => l_return_status ,
4760: x_return_msg => l_msg_data);
4761: IF l_return_status = FND_API.G_FALSE THEN
4762: fun_net_util.Log_String(g_event_level,l_path,
4763: 'Error Validating GL Period');
4764: FND_MESSAGE.SET_NAME('FUN','FUN_NET_GL_PERIOD_FAIL');
4765: FND_MESSAGE.SET_TOKEN('SETTLE_DATE',
4766: g_batch_details.settlement_date,FALSE);
4767: l_msg_data := FND_MESSAGE.GET;
4768: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
4769: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4770: RAISE FND_API.G_EXC_ERROR;
4771: END IF;
4772: /* Get Netting Bank Account Information */
4773: Get_Netting_Bank_Details(l_bank_acct_use_id,
4774: l_bank_acct_name,
4775: l_bank_acct_num,
4776: l_le_id,
4777: l_bank_num,
4778: l_return_status);
4779: IF l_return_status = FND_API.G_FALSE THEN
4780: fun_net_util.Log_String(g_event_level,l_path,
4781: 'Error in Getting Netting Bank Details');
4782: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_BANK_DETAILS');
4783: l_msg_data := FND_MESSAGE.GET;
4782: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_BANK_DETAILS');
4783: l_msg_data := FND_MESSAGE.GET;
4784: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
4785: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4786: RAISE FND_API.G_EXC_ERROR;
4787: END IF;
4788: --------------------------------------------------------------------
4789: /******************** Settle AP Transactions ***********************/
4790: --------------------------------------------------------------------
4795: p_le_id => l_le_id,
4796: p_bank_num => l_bank_num,
4797: x_return_status => l_return_status);
4798: fun_net_util.Log_String(g_event_level,l_path,'AP invoices settled successfully');
4799: IF l_return_status = FND_API.G_FALSE THEN
4800: fun_net_util.Log_String(g_event_level,l_path,
4801: 'Error in Settling AP Invoices');
4802: -- FND_MESSAGE.SET_NAME('FUN','FUN_NET_AP_SETTLE_FAIL');
4803: RAISE FND_API.G_EXC_ERROR;
4799: IF l_return_status = FND_API.G_FALSE THEN
4800: fun_net_util.Log_String(g_event_level,l_path,
4801: 'Error in Settling AP Invoices');
4802: -- FND_MESSAGE.SET_NAME('FUN','FUN_NET_AP_SETTLE_FAIL');
4803: RAISE FND_API.G_EXC_ERROR;
4804: END IF;
4805: /* -----------------------------------------------------------------*/
4806: /*********************** SETTLE AR TRANSACTIONS *******************/
4807: -------------------------------------------------------------------*/
4813: x_return_status => l_return_status,
4814: x_msg_data => l_msg_data,
4815: x_msg_count => l_msg_count);
4816: fun_net_util.Log_String(g_event_level,l_path,'AR TRX Settlement successful');
4817: IF l_return_status = FND_API.G_FALSE THEN
4818: -- x_msg_count := l_msg_count;
4819: -- x_msg_data := l_msg_data;
4820: fun_net_util.Log_String(g_event_level,l_path,
4821: 'Error in Settling AR Transactions');
4819: -- x_msg_data := l_msg_data;
4820: fun_net_util.Log_String(g_event_level,l_path,
4821: 'Error in Settling AR Transactions');
4822: -- FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_SETTLE_FAIL');
4823: RAISE FND_API.G_EXC_ERROR;
4824: END IF;
4825: -- Update Batch Status to Complete
4826: IF NOT update_batch_status('COMPLETE') THEN
4827: fun_net_util.Log_String(g_event_level,l_path,
4829: FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4830: l_msg_data := FND_MESSAGE.GET;
4831: FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data );
4832: FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4833: RAISE FND_API.G_EXC_ERROR;
4834: ELSE
4835: -- Submit the Data Extract report
4836: l_Request_id := FND_REQUEST.SUBMIT_REQUEST ( 'FUN'
4837: , 'FUNNTDEX'
4843: fun_net_util.Log_String(g_state_level,l_path,
4844: 'Data Extract request: '||l_request_id);
4845: END IF;
4846: -- Standard check of p_commit.
4847: IF FND_API.To_Boolean( p_commit ) THEN
4848: COMMIT WORK;
4849: END IF;
4850: -- Call Procedure to perform common tasks after committing
4851: -- Example : Unlocking txns , setting Agreement Status
4850: -- Call Procedure to perform common tasks after committing
4851: -- Example : Unlocking txns , setting Agreement Status
4852: perform_common_tasks;
4853: EXCEPTION
4854: WHEN FND_API.G_EXC_ERROR THEN
4855: ROLLBACK TO settle_net_batch_SP;
4856: batch_status_flag := update_batch_status('ERROR');
4857: --perform_common_tasks;
4858: x_return_status := FND_API.G_RET_STS_ERROR;
4854: WHEN FND_API.G_EXC_ERROR THEN
4855: ROLLBACK TO settle_net_batch_SP;
4856: batch_status_flag := update_batch_status('ERROR');
4857: --perform_common_tasks;
4858: x_return_status := FND_API.G_RET_STS_ERROR;
4859: FND_MSG_PUB.Count_And_Get (
4860: p_count => x_msg_count,
4861: p_data => x_msg_data );
4862: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4858: x_return_status := FND_API.G_RET_STS_ERROR;
4859: FND_MSG_PUB.Count_And_Get (
4860: p_count => x_msg_count,
4861: p_data => x_msg_data );
4862: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4863: ROLLBACK TO settle_net_batch_SP;
4864: batch_status_flag := update_batch_status('ERROR');
4865: --perform_common_tasks;
4866: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4862: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4863: ROLLBACK TO settle_net_batch_SP;
4864: batch_status_flag := update_batch_status('ERROR');
4865: --perform_common_tasks;
4866: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4867: FND_MSG_PUB.Count_And_Get (
4868: p_count => x_msg_count,
4869: p_data => x_msg_data );
4870: WHEN VALIDATE_AR_TXN_FAIL THEN
4870: WHEN VALIDATE_AR_TXN_FAIL THEN
4871: ROLLBACK TO settle_Net_Batch_SP;
4872: batch_status_flag := update_batch_status('CANCELLED');
4873: perform_common_tasks; -- Bug 5608043
4874: x_return_status := FND_API.G_RET_STS_ERROR;
4875: FND_MSG_PUB.Count_And_Get (
4876: p_count => x_msg_count,
4877: p_data => x_msg_data );
4878: WHEN OTHERS THEN
4878: WHEN OTHERS THEN
4879: ROLLBACK TO settle_Net_Batch_SP;
4880: batch_status_flag := update_batch_status('ERROR');
4881: -- perform_common_tasks;
4882: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4883: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4884: FND_MSG_PUB.Add_Exc_Msg( 'FUN_ARAP_NET_PKG', 'settle_net_batch');
4885: END IF;
4886: FND_MSG_PUB.Count_And_Get (