[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cash_receipt_history_id,status, reversal_cash_receipt_hist_id,
prv_stat_cash_receipt_hist_id
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_cash_receipt_id
ORDER BY cash_receipt_history_id;
SELECT
mf.misc_cash_distribution_id,
mf.distribution_ccid,
ar.code_combination_id
FROM
psa_mf_misc_dist_all mf,
ar_misc_cash_distributions ar
WHERE
mf.reference1 = p_status
AND mf.misc_cash_distribution_id = ar.misc_cash_distribution_id
AND ar.cash_receipt_id = g_cash_receipt_id ;
SELECT COUNT(*) INTO mf_dist_count
FROM psa_mf_misc_dist_all psa,
ar_misc_cash_distributions ar
WHERE psa.misc_cash_distribution_id = ar.misc_cash_distribution_id
AND ar.cash_receipt_id = g_cash_receipt_id
AND psa.reference1 = I.status;
## delete all mf distributions and re-create them.
*/
IF NOT (PSA_MF_MISC_PKG.create_distributions (
errbuf => errbuf,
retcode => retcode,
p_mode => 'R',
p_error_message => p_error_message,
x_status => i.status,
x_cash_receipt_hist_id => i.cash_receipt_history_id )) THEN -- 3 IF
IF p_error_message IS NOT NULL OR retcode = 'F' THEN -- 4 IF
-- ========================= FND LOG ===========================
psa_utils.debug_other_string(g_excep_level,l_full_path,
' Generate_distributions --> Error Message --> '
|| p_error_message);
SELECT m.misc_cash_distribution_id,
m.code_combination_id,m.amount,
m.gl_date,status,reversal_date
FROM
ar_misc_cash_distributions m,
ar_cash_receipts cr
WHERE m.created_from LIKE DECODE(x_status,'REVERSED','%REVERSE%','%ARRERCT%') AND
m.cash_receipt_id = cr.cash_receipt_id AND
cr.cash_receipt_id = p_cash_rct_id;
SELECT
m.misc_cash_distribution_id,
m.code_combination_id,
m.amount,
m.gl_date,
status,
reversal_date
FROM
ar_misc_cash_distributions m,
ar_cash_receipts cr
WHERE
m.cash_receipt_id = cr.cash_receipt_id
AND m.gl_posted_date IS NOT NULL
AND cr.cash_receipt_id = p_cash_rect_id;
SELECT cash_ccid , remittance_ccid
FROM
ar_receipt_method_accounts acc,
ar_receipt_methods rm,
ar_cash_receipts cr
WHERE
acc.receipt_method_id = rm.receipt_method_id
AND rm.receipt_method_id = cr.receipt_method_id
AND cr.cash_receipt_id = p_cr_id
AND cr.remittance_bank_account_id = acc.remit_bank_acct_use_id;
SELECT crh.status curstatus, crh1.status prevstatus
FROM ar_cash_receipt_history crh, ar_cash_receipt_history crh1
WHERE crh.cash_receipt_history_id = x_cash_receipt_hist_id AND
crh.cash_receipt_history_id = crh1.reversal_cash_receipt_hist_id(+);
SELECT cash_ccid FROM psa_mf_misc_dist_all
WHERE reference1 = 'REMITTED'
AND misc_cash_distribution_id = p_misc_dist_id;
-- User deletes a core distribution
THEN we delete all MFAR entries and re-create them based on latest core distributions.
If count mismatch happens after posting,
the ONLY possibility is REVERSAL of Misc. Receipt since user cannot update/delete/insert
core distributions after posting.
When REVERSAL occurs, one reversing line is created for each original line.
Multi-Fund logic should CREATE MF lines ONLY FOR THESE NEW REVERSING LINES
because the MF entrie corresponding to the original core distributions have
already been posted. Deleting and re-creating them will result in duplication
when they get posted to GL.
The cursor identifies the Core distribution rows that are reversing lines.
=====================================================================================*/
-- ========================= FND LOG ===========================
psa_utils.debug_other_string(g_state_level,l_full_path,
' Create_distributions --> g_cash_receipt_id --> '
|| g_cash_receipt_id);
SELECT status INTO cr_status
FROM ar_cash_receipts
WHERE cash_receipt_id = g_cash_receipt_id;
-- we delete records from psa_mf_misc_dist_all when the Dist records
--have not yet been posted and the Receipt has been reversed.
--These records are re-created by the code written below.
DELETE FROM psa_mf_misc_dist_all
WHERE reference5 = g_cash_receipt_id
AND posting_control_id = -3;
' Create_distributions --> records deleted --> ' || SQL%ROWCOUNT);
SELECT COUNT(*) INTO psa_count
FROM psa_mf_misc_dist_all
WHERE misc_cash_distribution_id = misc_dist_new_rec.misc_cash_distribution_id
AND posting_control_id >0;
' Create_distributions --> calling psa_mf_misc_dist_all_pkg.insert_row ');
psa_mf_misc_dist_all_pkg.insert_row
(
X_ROWID => x_dummy,
X_MISC_MF_CASH_DIST_ID => 1001,
X_MISC_CASH_DISTRIBUTION_ID => misc_dist_new_rec.misc_cash_distribution_id,
X_DISTRIBUTION_CCID => misc_dist_new_rec.code_combination_id,
X_CASH_CCID => l_mf_cash_ccid,
X_COMMENTS => NULL, --'Insert',
X_POSTING_CONTROL_ID => -3,
X_GL_DATE => misc_dist_new_rec.gl_date,
X_ATTRIBUTE_CATEGORY => 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_REFERENCE1 => x_status,
X_REFERENCE2 => NULL,
X_REFERENCE3 => misc_dist_new_rec.reversal_date,
X_REFERENCE4 => misc_dist_new_rec.status,
X_REFERENCE5 => g_cash_receipt_id,
x_reversal_ccid => null
);
' Create_distributions --> delete from psa_mf_misc_dist_all ');
DELETE FROM psa_mf_misc_dist_all
WHERE misc_cash_distribution_id IN
(SELECT misc_cash_distribution_id
FROM ar_misc_cash_distributions
WHERE reference5 = g_cash_receipt_id);
' Create_distributions --> Calling psa_mf_misc_dist_all_pkg.insert_row ');
SELECT first_posted_record_flag INTO first_rec_flag
FROM ar_cash_receipt_history
WHERE cash_receipt_history_id = x_cash_receipt_hist_id;
psa_mf_misc_dist_all_pkg.insert_row
(
X_ROWID => x_dummy,
X_MISC_MF_CASH_DIST_ID => 1001,
X_MISC_CASH_DISTRIBUTION_ID => l_misc_dist_rec.misc_cash_distribution_id,
X_DISTRIBUTION_CCID => l_misc_dist_rec.code_combination_id,
X_CASH_CCID => l_mf_cash_ccid,
X_COMMENTS => NULL, --'Insert',
X_POSTING_CONTROL_ID => -3,
X_GL_DATE => l_misc_dist_rec.gl_date,
X_ATTRIBUTE_CATEGORY => 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_REFERENCE1 => x_status,
X_REFERENCE2 => NULL,
X_REFERENCE3 => l_misc_dist_rec.reversal_date,
X_REFERENCE4 => l_misc_dist_rec.status,
X_REFERENCE5 => g_cash_receipt_id,
x_reversal_ccid => l_reversal_ccid);
SELECT count(misc_cash_distribution_id) INTO ar_dist_count
FROM ar_misc_cash_distributions
WHERE cash_receipt_id = g_cash_receipt_id AND amount>0;
SELECT
COUNT(misc_cash_distribution_id) INTO ar_dist_count
FROM ar_misc_cash_distributions
WHERE cash_receipt_id = g_cash_receipt_id
AND amount < 0;
SELECT COUNT(MISC_MF_CASH_DIST_ID) INTO psa_dist_count
FROM psa_mf_misc_dist_all psa,
ar_misc_cash_distributions ar
WHERE psa.reference1 = p_status
AND psa.misc_cash_distribution_id = ar.misc_cash_distribution_id
AND ar.cash_receipt_id = g_cash_receipt_id;
' Misc_rct_changed --> delete psa_mf_misc_dist_all ' || SQL%ROWCOUNT);