[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct crh.status , crh.cash_receipt_id
FROM ar_cash_receipt_history_all crh,
ar_receivable_applications_all ra
WHERE crh.cash_receipt_id = ra.cash_receipt_id
AND ra.receivable_application_id = p_receivable_app_id
ORDER BY crh.status desc;
SELECT app.applied_customer_trx_id cust_trx_id,
app.applied_customer_trx_line_id cust_trx_line_id,
app.code_combination_id rec_ccid,
app.cash_receipt_id cash_receipt_id,
app.amount_applied amount_applied,
app.earned_discount_taken earned_discount,
app.unearned_discount_taken unearned_discount,
app.earned_discount_ccid earned_discount_ccid,
app.unearned_discount_ccid unearned_discount_ccid,
app.customer_trx_id cm_trx_id
FROM ar_receivable_applications_all app
WHERE app.receivable_application_id = p_receivable_app_id
AND app.status = 'APP'
FOR UPDATE;
SELECT crh.account_code_combination_id cash_ccid, crh.status
FROM ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh
WHERE cr.cash_receipt_id = c_cash_receipt_id
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.status = c_status
AND NOT (cr.type = 'MISC');
SELECT previous_customer_trx_id
FROM ra_customer_trx_all
WHERE customer_trx_id = c_cust_trx_id;
-- Modified the select list in cursor c_direct_cm
-- From: lines.previous_customer_trx_line_id trx_line_id, dist.amount amount
-- To : distinct lines.previous_customer_trx_line_id trx_line_id
--
CURSOR c_direct_cm (c_cust_trx_id NUMBER)
IS
SELECT distinct lines.previous_customer_trx_line_id trx_line_id
FROM ra_customer_trx_lines lines, ra_cust_trx_line_gl_dist dist
WHERE lines.customer_trx_id = c_cust_trx_id
AND lines.customer_trx_line_id = dist.customer_trx_line_id
AND dist.account_class <> 'REC'
AND lines.extended_amount <> 0;
Select sum(dist.amount) line_amount
From ra_customer_trx_lines lines,
ra_cust_trx_line_gl_dist dist
Where lines.customer_trx_id = c_cust_trx_id
And lines.previous_customer_trx_line_id = c_trx_line_id
And lines.customer_trx_line_id = dist.customer_trx_line_id
And dist.account_class <> 'REC'
And lines.extended_amount <> 0;
SELECT currency_code
INTO l_currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id, l_exception_message);
PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
p_error_message);
SELECT count(rct.receivable_application_id)
INTO l_rct_dist_count
FROM psa_mf_rct_dist_all rct
WHERE rct.receivable_application_id = p_receivable_app_id;
SELECT count(rct.receivable_application_id)
INTO l_rct_dist_count
FROM psa_mf_rct_dist_all rct
WHERE rct.receivable_application_id = p_receivable_app_id;
SELECT
mf_cash_ccid
FROM psa_mf_rct_dist_all
WHERE receivable_application_id = p_rcv_app_id
AND cust_trx_line_gl_dist_id = p_gl_dist_id;
Select mf_trx_dist.mf_receivables_ccid rcv_ccid,
mf_trx_dist.cust_trx_line_gl_dist_id trx_line_dist_id,
trx_line.customer_trx_line_id trx_line_id,
trx_line.link_to_cust_trx_line_id link_trx_line_id,
-- column below changed by RM for 1604281
decode (sum_adr, 0, mf_balances.amount_due_original,
mf_balances.amount_due_remaining) amount_due
From ra_customer_trx_lines_all trx_line,
ra_cust_trx_line_gl_dist_all trx_dist,
psa_mf_trx_dist_all mf_trx_dist,
psa_mf_balances_view mf_balances
Where trx_line.customer_trx_id = p_cust_trx_id
And mf_balances.customer_trx_id = p_cust_trx_id
And trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id
And trx_dist.cust_trx_line_gl_dist_id = mf_trx_dist.cust_trx_line_gl_dist_id
And mf_trx_dist.cust_trx_line_gl_dist_id = mf_balances.cust_trx_line_gl_dist_id
and trx_line.customer_trx_line_id = nvl (p_cust_trx_line_id,trx_line.customer_trx_line_id)
AND EXISTS (SELECT 1 FROM ra_customer_trx_lines_all x
WHERE x.customer_trx_line_id = trx_line.customer_trx_line_id
AND NVL(extended_amount, 0) <> 0)
ORDER BY 2 DESC;
Select trx_dist.code_combination_id rev_ccid,
trx_dist.cust_trx_line_gl_dist_id trx_line_dist_id
From ra_customer_trx_all trx,
ra_customer_trx_lines_all trx_line,
ra_cust_trx_line_gl_dist_all trx_dist
Where trx.customer_trx_id = p_cust_trx_id
And trx.customer_trx_id = trx_line.customer_trx_id
And trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id
And trx_dist.account_class = 'REV';
select psa_mF_error_log_s.currval
into run_num
from sys.dual;
SELECT 1
INTO l_zero_amt_flag
FROM DUAL
WHERE EXISTS (SELECT 1
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = p_cust_trx_id
AND extended_amount <> 0 );
SELECT
decode (sum(mf_balances.amount_due_remaining),0,
sum(mf_balances.amount_due_original),
decode (l_zero_amt_flag,1,sum(mf_balances.amount_due_original),
sum(mf_balances.amount_due_remaining))) total_amount_due,
decode (l_zero_amt_flag,1,sum(mf_balances.amount_due_original),
sum(mf_balances.amount_due_remaining)) sum_amt_due_rem
INTO l_total_amount_due,
sum_amt_due_rem
FROM ra_customer_trx_lines_all trx_line,
ra_cust_trx_line_gl_dist_all trx_dist,
psa_mf_balances_view mf_balances
WHERE trx_line.customer_trx_id = p_cust_trx_id
AND mf_balances.customer_trx_id = p_cust_trx_id
AND trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id
AND trx_dist.cust_trx_line_gl_dist_id = mf_balances.cust_trx_line_gl_dist_id
AND trx_line.customer_trx_line_id = nvl(p_cust_trx_line_id, trx_line.customer_trx_line_id);
'Select mf_trx_dist.mf_receivables_ccid rcv_ccid, ' ||
' mf_trx_dist.cust_trx_line_gl_dist_id trx_line_dist_id, ' ||
' trx_line.customer_trx_line_id trx_line_id, ' ||
' trx_line.link_to_cust_trx_line_id link_trx_line_id, ' ||
' decode (:l_zero_amt_flag, 1, mf_balances.amount_due_original, mf_balances.amount_due_remaining) amount_due ' ||
' From ra_customer_trx_lines trx_line, ' ||
' ra_cust_trx_line_gl_dist trx_dist, ' ||
' psa_mf_trx_dist_all mf_trx_dist, ' ||
' psa_mf_balances_view mf_balances ' ||
' Where trx_line.customer_trx_id = :p_cust_trx_id_1 ' ||
' And mf_balances.customer_trx_id = :p_cust_trx_id_2 ' ||
' And trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id ' ||
' And trx_dist.cust_trx_line_gl_dist_id = mf_trx_dist.cust_trx_line_gl_dist_id ' ||
' And mf_trx_dist.cust_trx_line_gl_dist_id = mf_balances.cust_trx_line_gl_dist_id ' ||
' And trx_line.customer_trx_line_id = nvl(:p_cust_trx_line_id,trx_line.customer_trx_line_id) ';
' And EXISTS (SELECT 1 FROM ra_customer_trx_lines_all x ' ||
' WHERE x.customer_trx_line_id = trx_line.customer_trx_line_id AND NVL(extended_amount, 0) <> 0) '; */
'Select trx_dist.code_combination_id rcv_ccid, ' ||
' trx_dist.cust_trx_line_gl_dist_id trx_line_dist_id, ' ||
' trx_line.customer_trx_line_id trx_line_id, ' ||
' trx_line.link_to_cust_trx_line_id link_trx_line_id, ' ||
' decode (:l_zero_amt_flag, 1, mf_balances.amount_due_original, mf_balances.amount_due_remaining) amount_due ' ||
' From ra_customer_trx_lines trx_line, ' ||
' ra_cust_trx_line_gl_dist trx_dist, ' ||
' psa_mf_balances_view mf_balances ' ||
' Where trx_line.customer_trx_id = :p_cust_trx_id_1 ' ||
' And mf_balances.customer_trx_id = :p_cust_trx_id_2 ' ||
' And trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id ' ||
' And trx_dist.cust_trx_line_gl_dist_id = mf_balances.cust_trx_line_gl_dist_id ' ||
' And trx_dist.account_class <> '''||'REC'||''' ' ||
' And trx_line.customer_trx_line_id = nvl(:p_cust_trx_line_id,trx_line.customer_trx_line_id) ';
' And EXISTS (SELECT 1 FROM ra_customer_trx_lines_all x ' ||
' WHERE x.customer_trx_line_id = trx_line.customer_trx_line_id AND NVL(extended_amount, 0) <> 0) '; */
psa_utils.debug_other_string(g_state_level,l_full_path,' calling PSA_MFAR_RECEIPTS_COVER_PKG.INSERT_ROW');
-- Insert into psa_mf_rct_dist_all
--
PSA_MFAR_RECEIPTS_COVER_PKG.INSERT_ROW
(
x_rowid => l_rowid,
x_receivable_application_id => p_rcv_app_id,
x_cust_trx_line_gl_dist_id => l_accrual_rec.trx_line_dist_id,
x_attribute_category => NULL,
x_mf_cash_ccid => p_ccid,
x_amount => nvl(l_amount, 0),
x_percent => nvl(l_percent,0),
x_discount_ccid => p_mf_earned_discount_ccid,
x_ue_discount_ccid => p_mf_unearned_discount_ccid,
x_discount_amount => nvl(l_earned_discount,0),
x_ue_discount_amount => nvl(l_unearned_discount,0),
x_comments => NULL,
x_posting_control_id => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
X_REFERENCE4 => NULL,
X_REFERENCE5 => NULL,
X_REFERENCE2 => NULL,
X_REFERENCE1 => p_crh_status,
X_REFERENCE3 => NULL,
X_REVERSAL_CCID => l_remit_reversal_ccid,
x_mode => 'R' );
SELECT
mf_cash_ccid
FROM psa_mf_rct_dist_all
WHERE receivable_application_id = p_rcv_app_id
AND cust_trx_line_gl_dist_id = p_gl_dist_id;
select psa_mF_error_log_s.currval
into run_num
from sys.dual;
SELECT
decode (sum(mf_balances.amount_due_remaining),0,
sum(mf_balances.amount_due_original),
sum(mf_balances.amount_due_original)) total_amount_due,
sum(mf_balances.amount_due_original) sum_amt_due_rem
INTO l_total_amount_due,
sum_amt_due_rem
FROM ra_customer_trx_lines_all trx_line,
ra_cust_trx_line_gl_dist_all trx_dist,
psa_mf_balances_view mf_balances
WHERE trx_line.customer_trx_id = p_cust_trx_id
AND mf_balances.customer_trx_id = p_cust_trx_id
AND trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id
AND trx_dist.cust_trx_line_gl_dist_id = mf_balances.cust_trx_line_gl_dist_id
AND trx_line.customer_trx_line_id = nvl(p_cust_trx_line_id, trx_line.customer_trx_line_id);
SELECT -1*LINE_Applied , -1*TAX_Applied
INTO l_line_amount, l_tax_amount
FROM ar_receivable_applications_all app
WHERE app.receivable_application_id = p_rcv_app_id
AND app.status = 'APP';
'Select mf_trx_dist.mf_receivables_ccid rcv_ccid, ' ||
' mf_trx_dist.cust_trx_line_gl_dist_id trx_line_dist_id, ' ||
' trx_line.customer_trx_line_id trx_line_id, ' ||
' trx_line.link_to_cust_trx_line_id link_trx_line_id, ' ||
-- ' decode (:sum_adr, 0, mf_balances.amount_due_original, mf_balances.amount_due_remaining) amount_due, ' ||
' decode (:sum_adr, 0, mf_balances.amount_due_original, mf_balances.amount_due_original) amount_due, '||
' trx_dist.account_class account_class ' ||
' From ra_customer_trx_lines trx_line, ' ||
' ra_cust_trx_line_gl_dist trx_dist, ' ||
' psa_mf_trx_dist_all mf_trx_dist, ' ||
' psa_mf_balances_view mf_balances ' ||
' Where trx_line.customer_trx_id = :p_cust_trx_id_1 ' ||
' And mf_balances.customer_trx_id = :p_cust_trx_id_2 ' ||
' And trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id ' ||
' And trx_dist.cust_trx_line_gl_dist_id = mf_trx_dist.cust_trx_line_gl_dist_id ' ||
' And mf_trx_dist.cust_trx_line_gl_dist_id = mf_balances.cust_trx_line_gl_dist_id ' ||
' And trx_line.customer_trx_line_id = nvl(:p_cust_trx_line_id,trx_line.customer_trx_line_id) ';
'Select trx_dist.code_combination_id rcv_ccid, ' ||
' trx_dist.cust_trx_line_gl_dist_id trx_line_dist_id, ' ||
' trx_line.customer_trx_line_id trx_line_id, ' ||
' trx_line.link_to_cust_trx_line_id link_trx_line_id, ' ||
-- ' decode (:sum_adr, 0, mf_balances.amount_due_original, mf_balances.amount_due_remaining) amount_due, '||
' decode (:sum_adr, 0, mf_balances.amount_due_original, mf_balances.amount_due_original) amount_due, '||
' trx_dist.account_class account_class' ||
' From ra_customer_trx_lines trx_line, ' ||
' ra_cust_trx_line_gl_dist trx_dist, ' ||
' psa_mf_balances_view mf_balances ' ||
' Where trx_line.customer_trx_id = :p_cust_trx_id_1 ' ||
' And mf_balances.customer_trx_id = :p_cust_trx_id_2 ' ||
' And trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id ' ||
' And trx_dist.cust_trx_line_gl_dist_id = mf_balances.cust_trx_line_gl_dist_id ' ||
' And trx_dist.account_class <> '''||'REC'||''' ' ||
' And trx_line.customer_trx_line_id = nvl(:p_cust_trx_line_id,trx_line.customer_trx_line_id) ';
SELECT count(1)
INTO l_count
FROM ra_customer_trx_lines trx_line,
ra_cust_trx_line_gl_dist trx_dist,
psa_mf_trx_dist_all mf_trx_dist
WHERE trx_line.customer_trx_id = p_cust_trx_id
AND trx_dist.account_class = 'REV'
AND trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id
AND trx_dist.cust_trx_line_gl_dist_id = mf_trx_dist.cust_trx_line_gl_dist_id
AND trx_line.customer_trx_line_id = nvl(p_cust_trx_line_id,trx_line.customer_trx_line_id);
psa_utils.debug_other_string(g_state_level,l_full_path,' calling PSA_MFAR_RECEIPTS_COVER_PKG.INSERT_ROW');
-- Insert into psa_mf_rct_dist_all
--
IF l_count = 1 THEN
l_amount := -1*(-1*p_amount_applied - (-1*l_running_amount - (-1*l_amount)));
PSA_MFAR_RECEIPTS_COVER_PKG.INSERT_ROW
(
x_rowid => l_rowid,
x_receivable_application_id => p_rcv_app_id,
x_cust_trx_line_gl_dist_id => l_accrual_rec.trx_line_dist_id,
x_attribute_category => NULL,
x_mf_cash_ccid => p_ccid,
x_amount => nvl(l_amount, 0),
x_percent => nvl(l_percent,0),
x_discount_ccid => p_mf_earned_discount_ccid,
x_ue_discount_ccid => p_mf_unearned_discount_ccid,
x_discount_amount => nvl(l_earned_discount,0),
x_ue_discount_amount => nvl(l_unearned_discount,0),
x_comments => NULL,
x_posting_control_id => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
X_REFERENCE4 => NULL,
X_REFERENCE5 => NULL,
X_REFERENCE2 => NULL,
X_REFERENCE1 => p_crh_status,
X_REFERENCE3 => NULL,
X_REVERSAL_CCID => l_remit_reversal_ccid,
x_mode => 'R' );
SELECT distinct app.receivable_application_id rcv_app_id
FROM ar_receivable_applications app,
psa_mf_rct_dist_all mf_dist
WHERE app.receivable_application_id = mf_dist.receivable_application_id
AND (NOT(app.status = 'APP'));
DELETE FROM psa_mf_rct_dist_all
WHERE receivable_application_id = l_invalid_distributions_rec.rcv_app_id;
PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
l_exception_message);
SELECT terms.calc_discount_on_lines_flag discount_basis,
trx.created_from created_from
FROM ra_customer_trx trx,
ra_terms_b terms
WHERE trx.customer_trx_id = p_customer_trx_id
AND trx.term_id = terms.term_id;
PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
l_exception_message);
SELECT customer_trx_line_id, link_to_cust_trx_line_id, line_type
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id
AND include_manual_line (discount_basis, link_to_cust_trx_line_id, line_type) = 'Y';
PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
l_exception_message);
SELECT line_type
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = p_link_to_cust_trx_line_id;
PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
l_exception_message);
SELECT line_type, customer_trx_line_id, link_to_cust_trx_line_id, inventory_item_id
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id
AND include_imported_line
(p_discount_basis, link_to_cust_trx_line_id, line_type, inventory_item_id) = 'Y';
PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
l_exception_message);
Select inventory_item_id
From ra_customer_trx_lines
Where customer_trx_line_id = p_link_to_cust_trx_line_id;
Select line_type
From ra_customer_trx_lines
Where customer_trx_line_id = p_link_to_cust_trx_line_id;
PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
l_exception_message);
PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
l_exception_message);
TrxLinesTab.DELETE(i);
PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
l_exception_message);