[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT currency_code
INTO g_func_currency
FROM gl_ledgers
WHERE ledger_id = l_ledger_id;
SELECT batch_id,
object_version_number,
agreement_id,
batch_name,
batch_number,
review_netting_batch_flag,
batch_currency,
batch_status_code,
total_netted_amt,
transaction_due_date,
settlement_date,
response_date,
exchange_rate_type,
exchange_rate,
gl_date,
org_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
checkrun_id
FROM fun_net_batches_all
WHERE batch_id = g_batch_id;
SELECT net_currency_rule_code,
net_order_rule_code,
net_balance_rule_code,
bank_account_id,
net_currency_code,
agreement_start_date,
agreement_end_date,
nvl(days_past_due,0),
sel_rec_past_due_txns_flag
FROM fun_net_agreements
WHERE agreement_id = g_agreement_id;
FUNCTION update_batch_status(p_status VARCHAR2) RETURN BOOLEAN IS
l_path VARCHAR2(100);
l_path := g_path || 'Update_Batch_Status';
FUN_NET_BATCHES_PKG.Update_Row
(x_batch_id => g_batch_id,
x_batch_status_code => p_status);
fun_net_util.Log_String(g_state_level,l_path,'Successfully updated batch status');
END update_batch_status;
l_trx_select_clause VARCHAR2(2000);
l_trx_select_clause :=
'SELECT rct.customer_trx_id,
fun_net_arap_pkg.Calculate_ar_trx_amt(rct.customer_trx_id) transaction_amount,
sum(arps.amount_due_remaining) AS open_amount ' ;
fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
/* Only select the transactions where the due date is on or before the Tnx due date in the batch */
/* Only select transactions where the tnx due date is between the start and end date of the agreement */
/* Select only completed AR Transactions */
/* Past Due Transactions */
/* Do not select transactions that have prepayments ie : Preparyment flag = 'Y' */
/* Disputed transactions should not be selected */
/* Only select the transactions whose invoice types have been defined in the agreement */
/* Do not select transactions where the payment type code = 'CREDIT CARD' */
/* Do not select transactions that are already selected in another batch that is not in Status Complete */
l_trx_where_clause :=
' WHERE arps.customer_trx_id = rct.customer_trx_id
AND TRUNC(arps.due_date) <= trunc(:v_transaction_due_date)
AND arps.status = ''OP''
AND rct.complete_flag = ''Y''
AND TRUNC(arps.due_date) BETWEEN trunc(:v_agr_start_date)
AND trunc(:v_agr_end_date)
AND NVL(rct.prepayment_flag, ''N'') = ''N''
AND rct.cust_trx_type_id = rctt.cust_trx_type_id
AND NVL(arpt_sql_func_util.get_dispute_amount
(rct.customer_trx_id,rctt.type,rctt.accounting_affect_flag),0) = 0
AND arpt_sql_func_util.get_dispute_date
(rct.customer_trx_id,rctt.type,rctt.accounting_affect_flag) IS NULL
AND rct.bill_to_customer_id = fnc.cust_account_id
AND rct.bill_to_site_use_id = nvl(fnc.cust_site_use_id, rct.bill_to_site_use_id)
AND fnc.agreement_id = :v_agreement_id1
AND EXISTS
(SELECT ''X''
FROM fun_net_ar_trx_types_all fnar
WHERE fnar.cust_trx_type_id = rct.cust_trx_type_id
AND fnar.agreement_id = :v_agreement_id2)
AND NOT EXISTS
(SELECT ''X''
FROM ar_receipt_methods arm
WHERE arm.receipt_method_id = rct.receipt_method_id
AND arm.payment_type_code = ''CREDIT_CARD'')
AND NOT EXISTS
(SELECT ''X''
FROM fun_net_ar_txns_all fnar,
fun_net_batches_all fnba
WHERE Fnar.customer_trx_id = rct.customer_trx_id
AND fnar.batch_id = fnba.batch_id
AND fnba.batch_status_code <> ''CANCELLED''
AND fnba.batch_status_code <> ''COMPLETE''
AND fnba.batch_status_code <> ''REVERSED'')
AND fnc.org_id = :v_org_id1
AND rct.org_id = fnc.org_id
AND rctt.org_id = fnc.org_id
AND ((:v_sel_past_due_date1=''N'') OR (:v_sel_past_due_date2=''Y'' AND
TRUNC(arps.due_date) + nvl(:v_days_past_due,0) < trunc(sysdate) ))
AND arps.org_id = fnc.org_id';
/* Select the transactions with currency specified in the agreement if the netting currency rule is Single currency */
fun_net_util.Log_String(g_state_level,l_path,'currency rule :'||g_net_currency_rule);
fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause ;
insert_transactions(l_sql_stmt , g_net_currency, 'AR');
l_trx_select_clause :=
'SELECT rct.customer_trx_id,
fun_net_arap_pkg.Calculate_ar_trx_amt(rct.customer_trx_id) transaction_amount,
gl_currency_api.convert_amount_sql(rct.invoice_currency_code,
:batch_currency,
trunc(:SETTLEMENT_DATE),
:exchange_rate_type,
sum(arps.amount_due_remaining)) AS open_amount,
sum(arps.amount_due_remaining) AS txn_curr_open_amt ' ;
fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause;
insert_transactions(l_sql_stmt , g_func_currency, 'AR');
l_sql_stmt := 'SELECT DISTINCT RCT.INVOICE_CURRENCY_CODE CURRENCY_CODE ' || l_trx_from_clause || l_trx_where_clause ;
--Set the select where clause to select all transactions belonging to the cursor currency, as shown above in the first if condition.
g_currency_count := g_currency_count + 1;
fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause;
insert_transactions (l_sql_stmt , l_currency_code, 'AR');
l_inv_select_clause VARCHAR2(2000);
/* Build the select clause */
l_inv_select_clause :=
-- Select all ap_invoices based on the batch id
'SELECT
api.invoice_id,
api.invoice_amount AS transaction_amount,
SUM(aps.amount_remaining) AS open_amount,
min(aps.payment_num),
max(aps.payment_num) ';
fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
/* Do not select invoices that are on hold */
/* Select invoices that have been approved */
/* Select the invoices whose invoice types have been defined in the agreement */
/* Select only invoices where the transaction due date is on or before the Transaction Due date in the Batch */
/* Select transactions where the transactions due deate is between the start and end dates of the agreement */
/* Do not select invoices that are already selected in another batch where the batch status is not COMPLETE */
l_inv_where_clause :=
' WHERE api.invoice_id = aps.invoice_id
AND aps.hold_flag = ''N''
AND aps.payment_status_flag <> ''Y''
AND ap_invoices_pkg.get_approval_status(api.invoice_id,
api.invoice_amount,
api.payment_status_flag,
api.invoice_type_lookup_code) = ''APPROVED''
AND fns.agreement_id = :v_agreement_id1
AND fns.supplier_id = api.vendor_id
AND NVL(fns.supplier_site_id, api.vendor_site_id)= api.vendor_site_id
AND EXISTS
(SELECT ''X''
FROM fun_net_ap_inv_types_all fnai
WHERE fnai.invoice_type = api.invoice_type_lookup_code
AND fnai.agreement_id = :v_agreement_id2)
AND TRUNC(aps.due_date) <= TRUNC(:v_transaction_due_date)
AND TRUNC(aps.due_date) BETWEEN TRUNC(:v_agr_start_date)
AND TRUNC(:v_agr_end_date)
AND NOT EXISTS
(SELECT ''X''
FROM fun_net_ap_invs_all fnap,
fun_net_batches_all fnba
WHERE fnap.invoice_id = api.invoice_id
AND fnap.batch_id = fnba.batch_id
AND fnba.batch_status_code <> ''CANCELLED''
AND fnba.batch_status_code <> ''COMPLETE''
AND fnba.batch_status_code <> ''REVERSED'')
AND fns.org_id = :v_org_id
AND api.org_id = fns.org_id
AND aps.org_id = fns.org_id';
Y - "Yes" - Only invoices lines that were matched to PO lines with the OSA flag checked are selected for Netting
N - "No" -Only invoices lines that were matched to PO lines with the OSA flag not checked are selected for Netting
D - "Disregard"- All AP invoices selected for Netting; no filtering, therefore will select everything, as if the profile is "Off"
l_inv_where_clause := l_inv_where_clause || ' AND EXISTS ' || ' (SELECT apd.distribution_line_number '|| ' FROM ap_invoice_distributions_all apd '|| 'WHERE apd.invoice_id = api.invoice_id '|| 'AND apd.po_distribution_id IS NOT NULL) ';
/* Build the entire select statement */
fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
/* Call the procedure to insert AP netting transactions */
insert_transactions(l_sql_stmt , g_net_currency, 'AP');
l_inv_select_clause := 'SELECT
api.invoice_id,
api.invoice_amount as transaction_amount,
nvl(api.exchange_rate,1),
fc.precision,
SUM(aps.amount_remaining),
gl_currency_api.convert_amount_sql(api.invoice_currency_code,
:batch_currency,
trunc(:SETTLEMENT_DATE),
:exchange_rate_type,
SUM(aps.amount_remaining)) AS open_amount,
min(aps.payment_num),
max(aps.payment_num) ';
/* Build the entire select statement */
fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
l_sql_stmt := l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
insert_transactions(l_sql_stmt, g_func_currency, 'AP');
l_sql_stmt := 'SELECT DISTINCT api.invoice_currency_code ' || l_inv_from_clause || l_inv_where_clause ;
fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
l_sql_stmt := l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
insert_transactions(l_sql_stmt , l_currency_code, 'AP');
l_checkrun_id ap_inv_selection_criteria_all.checkrun_id%TYPE;
select checkrun_id
INTO l_checkrun_id
from FUN_NET_BATCHES_ALL
WHERE batch_id = p_batch_id;
UPDATE AP_PAYMENT_SCHEDULES aps
SET checkrun_id = l_checkrun_id
WHERE aps.invoice_id = p_schd_tab(i).invoice_id
AND amount_remaining <> 0
AND TRUNC(due_date) <= NVL(p_trx_due_date,TRUNC(due_date))
AND aps.payment_num BETWEEN p_schd_tab(i).min_payment_num
AND p_schd_tab(i).max_payment_num;
fun_net_util.Log_String(g_state_level,l_path,'Payment schedules updated');
PROCEDURE insert_transactions(p_inv_cur VARCHAR2,p_currency_code VARCHAR2, p_appln VARCHAR2) IS
l_batch_exists VARCHAR2(1);
l_checkrun_id ap_inv_selection_criteria_all.checkrun_id%TYPE;
l_path := g_path || 'Insert_Transactions';
insert_batch_record(p_currency_code);
select checkrun_id
INTO l_checkrun_id
from FUN_NET_BATCHES_ALL
WHERE batch_id = g_batch_id;
SELECT ap_inv_selection_criteria_s.nextval
INTO l_checkrun_id
FROM dual;
-- Update Netting Batch with the Checkrun id
UPDATE FUN_NET_BATCHES_ALL
SET checkrun_id = l_checkrun_id
WHERE batch_id = g_batch_id;
fun_net_util.Log_String(g_state_level,l_path,'Batch updated');
fun_net_util.Log_String(g_state_level,l_path,'Inserting invoices into Netting tables');
INSERT INTO fun_net_ap_invs_all
(batch_id,
invoice_id,
object_version_number,
ap_txn_rank,
invoice_amt,
open_amt,
inv_curr_open_amt,
org_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(g_batch_id,
l_invoice_id(i),
1,
l_invoice_rank(i),
l_invoice_amt(i),
l_open_amt(i),
l_open_amt(i),
g_batch_details.org_id,
sysdate,
g_user_id,
sysdate,
g_user_id,
g_login_id);
INSERT INTO fun_net_ap_invs_all
(batch_id,
invoice_id,
object_version_number,
ap_txn_rank,
invoice_amt,
open_amt,
inv_curr_open_amt,
org_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(g_batch_id,
l_invoice_id(i),
1,
l_invoice_rank(i),
l_invoice_amt(i),
l_open_amt(i),
l_inv_curr_open_amt(i),
g_batch_details.org_id,
sysdate,
g_user_id,
sysdate,
g_user_id,
g_login_id);
INSERT INTO fun_net_ar_txns_all
(batch_id,
customer_trx_id,
object_version_number,
ar_txn_rank,
transaction_amt,
open_amt,
txn_curr_open_amt,
org_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(g_batch_id,
l_invoice_id(i),
1,
l_invoice_rank(i),
l_invoice_amt(i),
l_open_amt(i),
l_open_amt(i),
g_batch_details.org_id,
sysdate,
g_user_id,
sysdate,
g_user_id,
g_login_id);
INSERT INTO fun_net_ar_txns_all
(batch_id,
customer_trx_id,
object_version_number,
ar_txn_rank,
transaction_amt,
open_amt,
txn_curr_open_amt,
org_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(g_batch_id,
l_invoice_id(i),
1,
l_invoice_rank(i),
l_invoice_amt(i),
l_open_amt(i),
l_inv_curr_open_amt(i),
g_batch_details.org_id,
sysdate,
g_user_id,
sysdate,
g_user_id,
g_login_id);
END insert_transactions;
/* As the Ar Bal = Total Net amount , update the net amount for each AR tnx with the open balance of that tnx */
UPDATE fun_net_ar_txns_all
SET netted_amt = open_amt,
txn_curr_net_amt = txn_curr_open_amt
WHERE batch_id = g_batch_id;
l_sql_stmt := 'SELECT open_amt,invoice_id,0,inv_curr_open_amt,0 FROM fun_net_ap_invs_all WHERE batch_id = :v_batch_id ORDER BY ap_txn_rank';
update_net_balances(l_sql_stmt,l_amt_to_net,'AP');
/* As the AP Bal = Total Net amount , update the net amount for each AP tnx with the open balance of that tnx */
UPDATE fun_net_ap_invs_all
SET netted_amt = open_amt,
inv_curr_net_amt = inv_curr_open_amt
WHERE batch_id = g_batch_id;
l_sql_stmt := 'SELECT open_amt,customer_trx_id,0, txn_curr_open_amt,0 FROM fun_net_ar_txns_all WHERE batch_id = :v_batch_id ORDER BY ar_txn_rank';
update_net_balances(l_sql_stmt,l_amt_to_net,'AR');
/*UPDATE fun_net_batches_all
SET total_netted_amt = l_amt_to_net
WHERE batch_id = g_batch_id; */
SELECT SUM (open_amt)
FROM fun_net_ar_txns_all
WHERE batch_id = g_batch_id;
SELECT SUM (open_amt)
FROM fun_net_ap_invs_all
WHERE batch_id = g_batch_id;
/*UPDATE fun_net_batches_all
SET batch_status_code = 'ERROR'
WHERE batch_id = g_batch_id; */
/*IF NOT update_Batch_Status('ERROR') THEN
p_status_flag := FND_API.G_FALSE;
PROCEDURE insert_batch_record(p_currency_code VARCHAR2) IS
l_batch_id fun_net_batches_all.batch_id%TYPE;
l_path := g_path||'insert_batch_record';
/* Check for mandatory parameters and all values that are going to be inserted */
SELECT fun_net_batches_s.NEXTVAL
INTO g_batch_id
FROM DUAL;
INSERT INTO FUN_NET_BATCHES_ALL
(batch_id,
object_version_number,
agreement_id,
batch_name,
batch_number,
review_netting_batch_flag,
batch_currency,
batch_status_code,
total_netted_amt,
transaction_due_date,
settlement_date,
response_date,
exchange_rate_type,
exchange_rate,
gl_date,
org_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20)
VALUES
(g_batch_id,
1,
g_agreement_id,
g_batch_details.batch_name,
g_batch_id,
g_batch_details.review_netting_batch_flag,
p_currency_code,
g_batch_details.batch_status_code,
g_batch_details.total_netted_amt,
g_batch_details.transaction_due_date,
g_batch_details.settlement_date,
g_batch_details.response_date,
g_batch_details.exchange_rate_type,
g_batch_details.exchange_rate,
g_batch_details.gl_date,
g_batch_details.org_id,
sysdate,
g_user_id,
sysdate,
g_user_id,
g_login_id,
g_batch_details.attribute_category,
g_batch_details.attribute1,
g_batch_details.attribute2,
g_batch_details.attribute3,
g_batch_details.attribute4,
g_batch_details.attribute5,
g_batch_details.attribute6,
g_batch_details.attribute7,
g_batch_details.attribute8,
g_batch_details.attribute9,
g_batch_details.attribute10,
g_batch_details.attribute11,
g_batch_details.attribute12,
g_batch_details.attribute13,
g_batch_details.attribute14,
g_batch_details.attribute15,
g_batch_details.attribute16,
g_batch_details.attribute17,
g_batch_details.attribute18,
g_batch_details.attribute19,
g_batch_details.attribute20);
fun_net_util.Log_String(g_state_level,l_path,'Insertion of batch_record failed.');
END insert_batch_record;
the same batch if the 'Netting within Currency' option is Selected. */
FOR i IN 1..g_idx LOOP
IF g_batch_list(i).agreement_id = g_agreement_id AND
g_batch_list(i).currency = p_currency_code THEN
fun_net_util.Log_String(g_state_level,l_path,'Agreement_id: '||g_batch_list(i).agreement_id);
SELECT INVOICE_CURRENCY_CODE
FROM ap_invoices_all api, fun_net_ap_invs_all fnapi
WHERE api.invoice_id = fnapi.invoice_id
AND fnapi.batch_id = g_batch_id
UNION
SELECT INVOICE_CURRENCY_CODE
FROM ra_customer_trx_all rct, fun_net_ar_txns_all fnart
WHERE rct.customer_trx_id = fnart.customer_trx_id
AND fnart.batch_id = g_batch_id;
SELECT USER_CONVERSION_TYPE
INTO l_conv_rate
FROM GL_DAILY_CONVERSION_TYPES
WHERE CONVERSION_TYPE = g_batch_details.exchange_rate_type;
PROCEDURE update_net_balances(p_sql_stmt VARCHAR2,
p_amt_to_net NUMBER,
p_appln VARCHAR2) IS
TYPE amt_type IS TABLE OF fun_net_batches_all.total_netted_amt%TYPE;
l_path := g_path || 'Update_Net_Balances';
/* Scan the table for every tnx selected and compute the net amount .
Example :if the Total Net amount = 950
Tot_Net_Amount = 950
Rank Tnx Open Amount Net Amount
1 1001 400 400
2 1002 500 500
3 1003 600 50 */
FOR i IN 1..l_trx_id.COUNT
LOOP
IF l_open_amt(i) < l_amt_to_net THEN
l_net_amt(i) := l_open_amt(i);
SELECT fc.currency_code,fc.precision
INTO l_inv_currency,l_precision
FROM ap_invoices_all api, fnd_currencies fc
WHERE api.invoice_id = l_trx_id(i)
AND api.invoice_currency_code = fc.currency_code;
SELECT fc.currency_code,fc.precision
INTO l_inv_currency,l_precision
FROM ra_customer_trx_all rct, fnd_currencies fc
WHERE rct.customer_trx_id = l_trx_id(i)
AND rct.invoice_currency_code = fc.currency_code;
UPDATE fun_net_ap_invs_all
SET netted_amt = l_net_amt(i),
inv_curr_net_amt = l_inv_curr_net_amt(i)
WHERE batch_id = g_batch_id
AND invoice_id = l_trx_id(i);
UPDATE fun_net_ar_txns_all
SET netted_amt = l_net_amt(i),
txn_curr_net_amt = l_inv_curr_net_amt(i)
WHERE batch_id = g_batch_id
AND customer_trx_id = l_trx_id(i);
END update_net_balances;
PROCEDURE Update_Net_Amounts(p_batch_id NUMBER, p_amt_to_net NUMBER, p_appln VARCHAR2) IS
BEGIN
NULL;
END Update_Net_Amounts;
SELECT sum(amount)
INTO l_total_amount
from ra_cust_trx_line_gl_dist dist,
ra_customer_trx_lines_all lines
Where lines.customer_trx_id = p_customer_trx_id
And lines.customer_trx_line_id = dist.customer_trx_line_id
And dist.account_class <> 'REC';
SELECT agreement_id,
net_currency_rule_code,
net_order_rule_code,
net_balance_rule_code,
bank_account_id,
net_currency_code,
agreement_start_date,
agreement_end_date,
shikyu_rule_code,
days_past_due,
sel_rec_past_due_txns_flag
FROM fun_net_agreements
WHERE org_id = g_batch_details.org_id
AND TRUNC(agreement_start_date) <= TRUNC(g_batch_details.settlement_date)
AND TRUNC(nvl(agreement_end_date,sysdate)) >= TRUNC(sysdate)
AND agreement_id = nvl(g_agreement_id,agreement_id);
IF NOT update_batch_status('RUNNING') THEN
fun_net_util.Log_String(g_state_level,l_path,'Error in updating batch status');
/* Set the status of the Batch to Selected */
IF l_status_flag = FND_API.G_TRUE THEN
UPDATE fun_net_batches_all
SET batch_status_code = 'SELECTED',
batch_currency = g_batch_list(i).currency,
agreement_id = g_batch_list(i).agreement_id,
total_netted_amt = l_amt_to_net
WHERE batch_id = g_batch_id;
g_batch_details.batch_status_code := 'SELECTED';
UPDATE fun_net_batches_all
SET batch_status_code = 'ERROR',
batch_currency = g_batch_list(i).currency,
agreement_id = g_batch_list(i).agreement_id,
total_netted_amt = l_amt_to_net
WHERE batch_id = g_batch_id;
/* IF NOT update_batch_status('SELECTED') THEN
fun_net_util.Log_String(g_state_level,l_path,'Error in updating batch status to SELECTED');
AND g_batch_details.batch_status_code = 'SELECTED' THEN
fun_net_util.Log_String(g_state_level,l_path,'Submitting Netting Batch');
batch_status_flag := update_batch_status('ERROR');
batch_status_flag := update_batch_status('ERROR');
batch_status_flag := update_batch_status('ERROR');
select batch_currency into l_batch_currency
from fun_net_batches_all
where batch_id = g_batch_id;
SELECT count (DISTINCT rac.invoice_currency_code)
into l_count
FROM ra_customer_trx_all rac,fun_net_ar_txns_all fnar
WHERE rac.customer_trx_id = fnar.customer_trx_id
AND fnar.batch_id = g_batch_id
AND rac.invoice_currency_code <> g_func_currency;
select cba.receipt_multi_currency_flag
into l_flag
from ce_bank_accounts cba, ce_bank_acct_uses_ALL ba,ar_receipt_classes rc,
ar_receipt_methods rm,ar_receipt_method_accounts_ALL rma
where rc.creation_method_code = 'NETTING'
and rc.receipt_class_id = rm.receipt_class_id
and ba.bank_account_id = cba.bank_account_id
and rm.receipt_method_id = rma.receipt_method_id
and rma.remit_bank_acct_use_id = ba.bank_acct_use_id;
SELECT approver_name
FROM FUN_NET_AGREEMENTS
WHERE agreement_id = g_agreement_id;
UPDATE fun_net_batches
SET batch_status_code = 'SUSPENDED'
WHERE batch_id = g_batch_id;
IF g_batch_details.batch_status_code IN ( 'SELECTED','REJECTED','ERROR' ) THEN
fun_net_util.Log_String(g_state_level,l_path,'Setting status to SUBMITTED');
UPDATE fun_net_batches
SET batch_status_code = 'SUBMITTED'
WHERE batch_id = g_batch_id;
ELSIF g_batch_details.batch_status_code IN('SELECTED','ERROR') THEN /* TP Approval is not necessary */
/* If TP approval is not necessary call Netting Settlement Date API */
/*l_batch_status := validate_netting_dates(
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_batch_id => g_batch_id,
p_net_settle_date => g_batch_details.settlement_date,
p_response_date => NULL);
UPDATE fun_net_batches
SET batch_status_code = 'APPROVED'
WHERE batch_id = g_batch_id;
UPDATE fun_net_batches
SET batch_status_code = 'CLEARING'
WHERE batch_id = g_batch_id;
l_batch_status_flag := update_batch_status('ERROR');
l_batch_status_flag := update_batch_status('ERROR');
l_batch_status_flag := update_batch_status('ERROR');
SELECT non_response_action_code
FROM FUN_NET_AGREEMENTS
WHERE agreement_id = g_agreement_id;
IF NOT Update_batch_status('SUSPENDED') THEN
RAISE FND_API.G_EXC_ERROR;
IF NOT update_batch_status ('APPROVED') THEN
fun_net_util.Log_String(g_state_level,l_path,'Unable to update the batch status to approved');
IF NOT Update_batch_status('APPROVED') THEN
RAISE FND_API.G_EXC_ERROR;
IF NOT Update_batch_status('REJECTED') THEN
RAISE FND_API.G_EXC_ERROR;
SELECT agreement_id
INTO l_agreement_id
FROM fun_net_batches
WHERE agreement_id = x_agreement_id
AND batch_id <> x_batch_id
AND batch_status_code IN ('RUNNING',
'SELECTED',
'SUBMITTED',
'REVERSING',
'CLEARING');
FUN_NET_AGREEMENTS_PKG.Update_Row(
x_agreement_id => x_agreement_id,
x_in_process_flag => 'Y');
FUN_NET_AGREEMENTS_PKG.Update_Row(
x_agreement_id => x_agreement_id,
x_in_process_flag => 'N');
SELECT bank_acct_use_id ,
ac.bank_account_name,
ac.bank_account_num,
ac.account_owner_org_id,
ba.bank_number
INTO p_bank_acct_use_id,
p_bank_acct_name,
p_bank_acct_num,
p_le_id,
p_bank_num
FROM ce_bank_acct_uses ac_use,
ce_bank_accounts ac,
ce_banks_v ba
WHERE ac.bank_account_id = g_bank_account_id
AND ac.bank_account_id = ac_use.bank_account_id
AND ac_use.org_id = g_batch_details.org_id
AND ba.bank_party_id = ac.bank_id;
one customer with the same priority then select the customer with
minimum customer id */
PROCEDURE Get_Customer_Details (p_cust_acct_id OUT NOCOPY NUMBER,
p_cust_site_use_id OUT NOCOPY NUMBER,
p_return_status OUT NOCOPY VARCHAR2)
IS
BEGIN
p_return_status := FND_API.G_TRUE;
/* Select First Customer Account on Receipt */
SELECT min(cust_account_id)
INTO p_cust_acct_id
FROM fun_net_customers ca
WHERE ca.agreement_id = g_agreement_id
AND cust_priority = 1;
SELECT u.site_use_id
INTO p_cust_site_use_id
FROM
fun_net_customers fc,
hz_cust_acct_sites s,
hz_cust_site_uses u
WHERE
fc.agreement_id = g_agreement_id
AND
fc.cust_account_id = p_cust_acct_id
AND fc.cust_account_id = s.cust_account_id
AND s.cust_acct_site_id = u.cust_acct_site_id
AND ( (u.site_use_id = fc.cust_site_use_id AND
u.primary_flag='Y'
and u.site_use_code='BILL_TO')
OR ( u.site_use_code = 'BILL_TO'
AND u.primary_flag = 'Y'
AND fc.cust_site_use_id IS NULL))
AND u.org_id = fc.org_id ;
l_checkrun_id ap_inv_selection_criteria_all.checkrun_id%TYPE;
SELECT checkrun_id , org_id
INTO l_checkrun_id , l_org_id
FROM fun_net_batches_all
WHERE batch_id = p_batch_id ;
UPDATE AP_PAYMENT_SCHEDULES_ALL
SET checkrun_id = NULL
WHERE checkrun_id =l_checkrun_id
AND org_id = l_org_id;
UPDATE FUN_NET_BATCHES_ALL
SET checkrun_id = NULL
WHERE batch_id = p_batch_id ;
SELECT default_exchange_rate_type
INTO l_exchange_rate_type
FROM ap_system_parameters_all
WHERE org_id = g_batch_details.org_id;
SELECT set_of_books_id
INTO l_ledger_id
FROM hr_operating_units
WHERE organization_id = g_batch_details.org_id;
SELECT
sum(finv.inv_curr_net_amt) AS pymt_amt,
-- sum(finv.netted_amt) AS base_pymt_amt,
inv.vendor_id,
inv.vendor_site_id,
inv.party_id,
inv.party_site_id,
inv.payment_currency_code
BULK COLLECT INTO ap_check_amt_list,
-- ap_check_base_amt_list,
vendor_list,
vendor_site_list,
party_list,
party_site_list,
currency_list
FROM fun_net_ap_invs finv,
ap_invoices inv
WHERE inv.invoice_id = finv.invoice_id
AND finv.batch_id = g_batch_details.batch_id
AND finv.inv_curr_net_amt <> 0
GROUP BY vendor_id,
vendor_site_id,
party_id,
party_site_id,
inv.payment_currency_code;
SELECT fun.fun_net_ap_checks_s.nextval
INTO ap_check_rec.check_number
FROM DUAL;
ap_check_rec.LAST_UPDATED_BY := fnd_global.user_id;
ap_check_rec.LAST_UPDATE_DATE := sysdate;
ap_check_rec.LAST_UPDATE_LOGIN := fnd_global.login_id;
/* Selects invoices per vendor. Amt to be paid reflects the total amount to to be paid per invoice. Calculate the amt to be paid per installment */
fun_net_util.Log_String(g_event_level,
l_path,
' checkrun_id :'||g_batch_details.checkrun_id ||
' batch_id :'||g_batch_details.batch_id ||
' currency :' || currency_list(i) );
SELECT
inv.invoice_id AS invoice_id,
apps.payment_num AS payment_num,
finv.inv_curr_net_amt AS pymt_amt
--Amt in Payment Currency = Invoice Currency
BULK COLLECT INTO
ap_payment_info_tab
FROM
ap_invoices inv,
fun_net_ap_invs finv,
ap_payment_schedules apps
WHERE finv.invoice_id = inv.invoice_id
AND apps.invoice_id = inv.invoice_id
AND apps.checkrun_id = g_batch_details.checkrun_id
AND finv.batch_id = g_batch_details.batch_id
AND inv.vendor_id = vendor_list(i)
AND inv.vendor_site_id = vendor_site_list(i)
AND inv.invoice_currency_code = currency_list(i)
AND finv.inv_curr_net_amt <> 0
ORDER BY inv.invoice_id,apps.payment_num;
ap_invoice.DELETE;
SELECT amount_remaining
BULK COLLECT INTO amtDueTab
FROM ap_payment_schedules apps,
ap_invoices inv
WHERE apps.invoice_id = inv.invoice_id
AND apps.checkrun_id = g_batch_details.checkrun_id
AND inv.vendor_id = vendor_list(i)
AND inv.vendor_site_id = vendor_site_list(i)
AND inv.invoice_currency_code = currency_list(i)
ORDER BY inv.invoice_id,apps.payment_num;
/* Update FUN_NET_AP_INVS all with the check Id */
BEGIN
FORALL k IN ap_invoice.FIRST..ap_invoice.LAST
UPDATE FUN_NET_AP_INVS
SET check_id = l_check_id
WHERE batch_id = g_batch_id
AND inv_curr_net_amt <> 0
AND invoice_id = ap_invoice(k);
SELECT SUM(decode(p_inv_currency_code,
g_batch_details.batch_currency,
amount_due_remaining,
p_exchange_rate * amount_due_remaining))
INTO l_amount
FROM AR_PAYMENT_SCHEDULES
WHERE due_date <= g_batch_details.transaction_due_date
AND status = 'OP'
AND customer_trx_id = p_customer_trx_id;
/* Selects all the AR Transactions that need to be locked */
/*PROCEDURE Lock_AR_Txn(
txnCur IN OUT NOCOPY txnCurType,
x_return_status OUT NOCOPY VARCHAR2)
IS
BEGIN
x_return_status := FND_API.G_TRUE;
SELECT trx.invoice_currency_code AS invoice_currency_code,
trx.customer_trx_id AS customer_trx_id,
trx.trx_number AS trx_number,
trx_line.customer_trx_line_id AS customer_trx_line_id,
trx_line.line_number AS line_number,
ftxn.transaction_amt AS txn_amt,
trx_line.extended_amount AS line_amt,
ARPS.payment_schedule_id AS pymt_schedule_id,
ARPS.amount_due_remaining AS amt_remaining,
ftxn.netted_amt AS net_amt,
ftxn.open_amt AS open_amt,
Derive_net_exchg_rate(trx.invoice_currency_code,
g_func_currency) trans_receipt_rate,
Derive_net_exchg_rate(g_func_currency,
trx.invoice_currency_code) receipt_trans_rate,
arm.name AS receipt_name,
arm.payment_type_code AS payment_type_code
FROM FUN_NET_AR_TXNS ftxn,
RA_CUSTOMER_TRX trx,
RA_CUSTOMER_TRX_LINES trx_line,
AR_PAYMENT_SCHEDULES ARPS,
AR_RECEIPT_METHODS arm
WHERE ftxn.customer_trx_id = trx.customer_trx_id
AND trx.customer_trx_id = trx_line.customer_trx_id
AND ARPS.customer_Trx_id = trx.customer_Trx_id
AND ARPS.DUE_DATE <= g_batch_details.TRANSACTION_DUE_DATE
AND ARPS.status = 'OP'
AND ftxn.batch_id = g_batch_details.batch_id
AND arm.receipt_method_id = trx.receipt_method_id
ORDER BY ftxn.customer_trx_id;
-- FOR UPDATE of ftxn.batch_id, trx.customer_trx_id,trx_line.customer_trx_id;
SELECT trx.customer_trx_id AS customer_trx_id,
trx.exchange_rate AS inv_exchange_rate,
sum(ARPS.amount_due_remaining) AS amt_remaining,
ftxn.transaction_amt AS txn_amount,
ftxn.open_amt AS open_amt,
ftxn.txn_curr_open_amt AS txn_curr_open_amt,
0 AS txn_curr_amt,
ftxn.txn_curr_net_amt AS txn_curr_net_amt,
ftxn.netted_amt AS net_amt,
trx.invoice_currency_code AS invoice_currency_code,
arm.payment_type_code AS payment_type_code
BULK COLLECT INTO txnTable
FROM FUN_NET_AR_TXNS ftxn,
RA_CUSTOMER_TRX trx,
AR_PAYMENT_SCHEDULES ARPS,
AR_RECEIPT_METHODS arm,
RA_CUST_TRX_TYPES ctype
WHERE ftxn.customer_trx_id = trx.customer_trx_id
AND ARPS.customer_Trx_id = trx.customer_Trx_id
AND ARPS.DUE_DATE <= g_batch_details.TRANSACTION_DUE_DATE
AND ARPS.DUE_DATE between g_agr_start_date and g_agr_end_date
AND ARPS.status = 'OP'
AND ftxn.batch_id = g_batch_details.batch_id
AND arm.receipt_method_id (+) = trx.receipt_method_id
AND ctype.cust_trx_type_id = trx.cust_trx_type_id
and trx.org_id = ftxn.org_id
and trx.org_id = arps.org_id
and trx.org_id = ctype.org_id
GROUP BY trx.customer_trx_id,
trx.exchange_rate,
ftxn.transaction_amt,
ftxn.open_amt,
ftxn.netted_amt,
ftxn.txn_curr_open_amt,
ftxn.txn_curr_net_amt,
trx.invoice_currency_code,
arm.payment_type_code,
ctype.type
ORDER BY ctype.type;
SELECT trx_line.customer_trx_line_id AS cust_trx_line_id,
trx_line.extended_amount AS extended_amount,
ps.payment_schedule_id AS pymt_schedule_id
FROM fun_net_ar_txns ftxn,
ra_customer_trx_lines trx_line,
ar_payment_schedules ps
WHERE ftxn.batch_id = g_batch_details.batch_id
AND ftxn.customer_trx_id = p_cust_trx_id
AND trx_line.customer_trx_id = ftxn.customer_trx_id
AND trx_line.line_type = 'LINE'
AND ps.customer_trx_id = trx_line.customer_trx_id
AND ps.due_date <= g_batch_details.transaction_due_date
AND ps.due_date between g_agr_start_date and g_agr_end_date
AND ps.status = 'OP';
SELECT SUM(amount_due_remaining)
INTO current_amt_due
FROM ar_payment_schedules ps
WHERE customer_trx_id = txnTable(i).customer_Trx_id
AND due_date <= g_batch_details.TRANSACTION_DUE_DATE
AND due_date between g_agr_start_date and g_agr_end_date
AND ps.status = 'OP';
SELECT ps.payment_schedule_id AS pymt_schedule_id,
ps.amount_due_remaining AS amt_due_remain
FROM ar_payment_schedules ps
WHERE ps.customer_trx_id = p_cust_trx_id
AND trunc(ps.due_date) <= trunc(g_batch_details.transaction_due_date)
AND trunc(ps.due_date) between trunc(g_agr_start_date) and trunc(g_agr_end_date)
AND ps.status = 'OP';
SELECT trx_line.customer_trx_line_id AS cust_trx_line_id,
trx_line.extended_amount AS extended_amount,
trx_line.line_type AS line_type,
trx_line.amount_due_remaining AS line_am_due_remaining
FROM ra_customer_trx_lines trx_line
WHERE trx_line.customer_trx_id = p_cust_trx_id
AND trx_line.line_type IN ('LINE');
select currency_code into l_receipt_currecycode
from ar_cash_receipts
where cash_receipt_id = p_cash_receipt_id;
SELECT SUM(amount_due_remaining)
INTO current_amt_due
FROM ar_payment_schedules ps
WHERE customer_trx_id = txnTable(i).customer_Trx_id
AND due_date <= g_batch_details.TRANSACTION_DUE_DATE
AND due_date between g_agr_start_date and g_agr_end_date
AND ps.status = 'OP';
SELECT sum(APS.amount_due_original)
INTO l_original_amt
FROM AR_PAYMENT_SCHEDULES APS
WHERE APS.customer_Trx_id = txnTable(i).customer_Trx_id;
SELECT fc.precision
INTO l_precision
FROM fnd_currencies fc
WHERE fc.currency_code = txnTable(i).invoice_currency_code;
SELECT trx_line.customer_trx_line_id AS cust_trx_line_id,
trx_line.line_number AS line_number,'','',
round(((((nvl(trx_line.amount_due_remaining,trx_line.extended_amount)/current_amt_due)*l_applied_amt) +
( select nvl((sum( nvl(trx_line_tax.amount_due_remaining,trx_line_tax.extended_amount) )/current_amt_due)*l_applied_amt,0)
FROM ra_customer_trx_lines trx_line_tax
WHERE trx_line_tax.link_to_cust_trx_line_id = trx_line.customer_trx_line_id
AND trx_line_tax.customer_trx_id = trx_line.customer_trx_id
AND trx_line_tax.line_type IN ('TAX') ))),l_precision) AS amount_applied,'','','','','','','','',''
,'','','','','','','','','',''
BULK COLLECT INTO llca_def_trx_lines_tbl
FROM ra_customer_trx_lines trx_line
WHERE trx_line.customer_trx_id = txnTable(i).customer_trx_id
AND trx_line.line_type IN ('LINE')
AND nvl(trx_line.amount_due_remaining,trx_line.extended_amount) <> 0 ;
SELECT round(SUM((nvl(trx_line.amount_due_remaining,trx_line.extended_amount)
/current_amt_due)*l_applied_amt),l_precision)
INTO l_freight_amount
FROM ra_customer_trx_lines trx_line
WHERE trx_line.customer_trx_id = txnTable(i).customer_trx_id
AND trx_line.line_type IN ('FREIGHT')
AND nvl(trx_line.amount_due_remaining,trx_line.extended_amount) <> 0 ;
SELECT count(*) into l_line_or_header
FROM ra_batch_sources bs, ra_customer_trx ct
WHERE ct.customer_trx_id = txnTable(i).customer_trx_id AND
ct.batch_Source_id = bs.batch_source_id AND
NVL(gen_line_level_bal_flag,'Y') = 'Y';
Select Class into l_class from ar_payment_schedules_all
where customer_trx_id = txnTable(i).customer_trx_id;
SELECT receipt_method_id
INTO l_receipt_method_id
FROM ar_receipt_methods
WHERE receipt_method_id= -1;
SELECT remit_bank_acct_use_id
INTO l_bank_acct_used
FROM ar_receipt_method_accounts_all
WHERE receipt_method_id = l_receipt_method_id
and org_id =g_batch_details.org_id
and remit_bank_acct_use_id= p_bank_acct_use_id;
UPDATE fun_net_ar_txns
SET cash_receipt_id = l_cash_receipt_id
WHERE
batch_id = g_batch_id;
-- Update Agreement Status
Set_Agreement_Status(
x_agreement_id => g_batch_details.agreement_id,
x_batch_id => g_batch_id,
x_mode => 'UNSET',
x_return_status => l_return_status);
-- Update Batch Status to Complete
IF NOT update_batch_status('COMPLETE') THEN
fun_net_util.Log_String(g_event_level,l_path,
'Error in Setting Status to Complete');
batch_status_flag := update_batch_status('ERROR');
batch_status_flag := update_batch_status('ERROR');
batch_status_flag := update_batch_status('CANCELLED');
batch_status_flag := update_batch_status('ERROR');