[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Selects the Batch Status of the given Batch ids */
PROCEDURE get_batch_status(
p_batch_id IN fun_net_batches_all.batch_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2)
IS
BEGIN
x_return_status := FND_API.G_TRUE;
SELECT batch_status_code
INTO g_batch_status
FROM
fun_net_batches
WHERE
batch_id = p_batch_id;
'SELECTED','SUSPENDED','CANCELLED','REJECTED','ERROR'
If mode = Reverse , then the batch should be in status 'COMPLETE' */
PROCEDURE Validate_Batch_Status
(p_mode IN VARCHAR2,
p_batch_id IN fun_net_batches.batch_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_return_status VARCHAR2(1);
IF g_batch_status NOT IN ('SELECTED','SUSPENDED',
'CANCELLED','REJECTED','ERROR') THEN
x_return_status := FND_API.G_FALSE;
'SELECTED' and deletes Netting AP Invoices in FUN_NET_AP_INVS given a Batch Id */
PROCEDURE delete_ap_invs (
p_batch_id IN fun_net_batches.batch_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2)
IS
TYPE l_inv_tab_type IS TABLE OF fun_net_ap_invs.invoice_id%TYPE;
/* Unlock AP Transactions if the Batch Status = SELECTED
AR Transactions are not locked at this point */
IF (g_batch_status = 'SELECTED') OR (g_batch_status='ERROR') THEN
FUN_NET_ARAP_PKG.unlock_ap_pymt_schedules(
p_batch_id => p_batch_id,
x_return_status => x_return_status);
SELECT invoice_id
BULK COLLECT INTO l_inv_tab
FROM fun_net_ap_invs
WHERE batch_id = p_batch_id;
FUN_NET_AP_INVS_PKG.Delete_Row(
x_batch_id => p_batch_id,
x_invoice_id => l_inv_tab(i));
END delete_ap_invs;
/* Deletes Netting AR transactions for the given batch */
PROCEDURE delete_ar_txns (
x_batch_id IN fun_net_batches.batch_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2)
IS
TYPE l_txn_tab_type IS TABLE OF fun_net_ar_txns.customer_trx_id%TYPE;
SELECT customer_trx_id
BULK COLLECT INTO l_txn_tab
FROM fun_net_ar_txns
WHERE batch_id = x_batch_id;
FUN_NET_AR_TXNS_PKG.Delete_Row(
x_batch_id => x_batch_id,
x_customer_trx_id => l_txn_tab(i));
END delete_ar_txns;
SELECT agreement_id
INTO x_agreement_id
FROM fun_net_batches
WHERE batch_id = p_batch_id;
/* Deletes a Batch and all the transactions in a batch
that is not in COMPLETE Status. Unlocks AP Transaction
if the Batch Status is 'SELECTED' */
PROCEDURE cancel_net_batch(
-- ***** Standard API Parameters *****
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
-- ***** Netting batch input parameters *****
p_batch_id IN NUMBER) IS
-- ***** local variables *****
l_return_status VARCHAR2(1);
/* Delete AP Invoices belonging to the Batch */
delete_ap_invs(
p_batch_id => p_batch_id,
x_return_status => l_return_status);
/* Delete AR Transactions belonging to the Batch */
delete_ar_txns(
x_batch_id => p_batch_id,
x_return_status => l_return_status);
/* Delete the Batch */
FUN_NET_BATCHES_PKG.Delete_Row(
x_batch_id => p_batch_id);
SELECT org_id,
gl_date,
settlement_date
FROM fun_net_batches_all
WHERE batch_id = p_batch_id;
/*SELECT set_of_books_id
INTO l_ledger_id
FROM hr_operating_units
WHERE organization_id = g_batch_details.org_id; */
SELECT DISTINCT check_id AS check_id
FROM fun_net_ap_invs_all
WHERE batch_id = p_batch_id
AND check_id is not null;
P_Last_Updated_By => g_user_id,
P_Last_Update_Login => g_login_id,
P_Num_Cancelled => l_num_cancelled,
P_Num_Not_Cancelled => l_num_not_cancelled,
P_Calling_Sequence => 'Netting Batch - Reversing',
X_return_status => l_return_status,
X_msg_count => l_msg_count,
X_msg_data => l_msg_data);
SELECT DISTINCT txn.cash_receipt_id,
cr.receipt_number,
cr.receipt_date,
txn.org_id
FROM
fun_net_ar_txns txn,
ar_cash_receipts_all cr
WHERE txn.batch_id = p_batch_id
AND txn.cash_receipt_id = cr.cash_receipt_id
AND txn.org_id = cr.org_id;
SELECT max(crh.gl_date)
FROM ar_cash_receipt_history crh
WHERE crh.cash_receipt_id = p_cr_id;
PROCEDURE Update_Amounts(
p_batch_id IN fun_net_batches.batch_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2)
IS
BEGIN
BEGIN
UPDATE FUN_NET_AP_INVS
SET netted_amt = 0
WHERE batch_id = p_batch_id;
UPDATE FUN_NET_AR_TXNS
SET netted_amt = 0
WHERE batch_id = p_batch_id;
UPDATE FUN_NET_BATCHES
SET total_netted_amt = 0
WHERE batch_id = p_batch_id;
/* Update Batch Status to Reversing */
fun_net_util.Log_String(g_state_level,l_path,'Updating batch status');
IF NOT FUN_NET_ARAP_PKG.update_batch_status('REVERSING') THEN
RAISE FND_API.G_EXC_ERROR;
/* Update Agreement Status */
fun_net_util.Log_String(g_state_level,l_path,'Get agreement');
/* Update Amounts */
fun_net_util.Log_String(g_state_level,l_path,'Updating batch amounts');
Update_amounts(
p_batch_id => p_batch_id,
x_return_status => l_return_status);
/* Update Batch Status */
fun_net_util.Log_String(g_state_level,l_path,'Updating batch status to REVERSED');
UPDATE fun_net_batches
SET batch_status_code = 'REVERSED'
WHERE batch_id = p_batch_id;
/*IF NOT FUN_NET_ARAP_PKG.update_batch_status('REVERSED') THEN
RAISE FND_API.G_EXC_ERROR;