The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT
ra.receivable_application_id
FROM ar_receivable_applications ra,
ar_cash_receipts cr
WHERE ra.gl_date >= p_Prepare.GlDateFrom
AND ra.gl_date <= p_Prepare.GlDateTo
AND nvl(ra.postable,'Y') = 'Y'
AND nvl(ra.confirmed_flag,'Y') = 'Y'
AND ra.status <> 'APP' -- Bug 3519052
AND ra.application_type||'' = 'CASH'
AND cr.cash_receipt_id = ra.cash_receipt_id
AND ra.set_of_books_id = p_Prepare.SetOfBooksId
;
select 'x'
from igi_ar_rec_applications_all igi
where igi.receivable_application_id = fp_app_id
;
INSERT INTO igi_ar_rec_applications_all
( receivable_application_id
, arc_posting_control_id
, last_update_date
, last_updated_by
, last_update_login
, creation_Date
, created_by
)
VALUES ( LRA.receivable_application_id
, -3
, sysdate
, fnd_global.user_id
, fnd_global.login_id
, sysdate
, fnd_global.user_id
);
SELECT DISTINCT
DECODE(
l.lookup_code,
'1', 'N',
'2', 'Y'
) CmPsIdFlag,
ra.receivable_application_id
FROM ar_receivable_applications ra,
ra_cust_trx_types ctt,
ra_customer_trx ct,
ar_cash_receipts cr,
ar_cash_receipt_history crh,
ra_customer_trx ctcm,
ar_lookups l
WHERE ra.gl_date BETWEEN p_Prepare.GlDateFrom
AND p_Prepare.GlDateTo
AND nvl(ra.postable,'Y') = 'Y'
AND nvl(ra.confirmed_flag,'Y') = 'Y'
AND ra.status||'' = 'APP' -- Bug 3519052
AND ra.cash_receipt_id = cr.cash_receipt_id(+)
AND ra.cash_receipt_history_id = crh.cash_receipt_history_id(+)
AND ra.customer_trx_id = ctcm.customer_trx_id(+)
AND ctcm.previous_customer_trx_id IS NULL
AND ra.applied_customer_trx_id = ct.customer_trx_id
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ra.set_of_books_id = p_Prepare.SetOfBooksId
AND l.lookup_type = 'AR_CARTESIAN_JOIN'
AND (
( l.lookup_code ='1' )
OR
( l.lookup_code = '2'
AND
ra.application_type = 'CM' )
)
;
select 'x'
from igi_ar_rec_applications_all igi
where igi.receivable_application_id = fp_app_id
;
INSERT INTO igi_ar_rec_applications_all
( receivable_application_id
, arc_posting_control_id
, last_update_date
, last_updated_by
, last_update_login
, creation_Date
, created_by
)
VALUES ( LRA.receivable_application_id
, -3
, sysdate
, fnd_global.user_id
, fnd_global.login_id
, sysdate
, fnd_global.user_id
);
SELECT distinct crh.cash_receipt_history_id
FROM ar_cash_receipt_history crh,
ar_cash_receipts cr,
ar_distributions d
WHERE crh.gl_date BETWEEN p_Prepare.GlDateFrom
AND p_Prepare.GlDateTo
AND crh.postable_flag = 'Y'
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.cash_receipt_history_id = d.source_id
AND d.source_table = 'CRH' ;
select 'x'
from igi_ar_cash_receipt_hist_all igi
where igi.cash_receipt_history_id = fp_crh_id
;
INSERT into igi_ar_cash_receipt_hist_all (
cash_receipt_history_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
, arc_rev_post_control_id
, arc_posting_control_id
) values
( lcrh.cash_receipt_history_id
, sysdate
, fnd_global.user_id
, fnd_global.conc_login_id -- bug 4119243
, sysdate
, fnd_global.user_id -- bug 4119243
, null
, -3
);
SELECT distinct crh.cash_receipt_history_id
FROM ar_cash_receipt_history crh,
ar_cash_receipts cr,
ar_distributions d
WHERE crh.reversal_gl_date BETWEEN p_Prepare.GlDateFrom
AND p_Prepare.GlDateTo
AND crh.postable_flag = 'Y'
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.cash_receipt_history_id = d.source_id
AND d.source_table = 'CRH' ;
select 'x'
from igi_ar_cash_receipt_hist_all igi
where igi.cash_receipt_history_id = fp_crh_id
;
UPDATE igi_Ar_cash_receipt_hist_all
SET arc_rev_post_control_id = -3
, arc_rev_gl_posted_date = null
WHERE cash_receipt_history_id = lcrh.cash_receipt_history_id
;
INSERT into igi_ar_cash_receipt_hist_all (
cash_receipt_history_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
, arc_posting_control_id
, arc_rev_post_control_id
) values
( lcrh.cash_receipt_history_id
, sysdate
, fnd_global.user_id
, fnd_global.conc_login_id -- bug 4119243
, sysdate
, fnd_global.user_id
, -3 -- Bug 3519052
, null
);
SELECT distinct mcd.misc_cash_distribution_id
FROM ar_misc_cash_distributions mcd,
ar_cash_receipts cr
WHERE mcd.gl_date BETWEEN p_Prepare.GlDateFrom
AND p_Prepare.GlDateTo
AND mcd.set_of_books_id = p_Prepare.setofbooksid
AND cr.cash_receipt_id = mcd.cash_receipt_id ;
select 'x'
from igi_ar_misc_cash_dists_all igi
where igi.misc_cash_distribution_id = fp_mcd_id
;
INSERT INTO igi_ar_misc_cash_dists_all (
misc_cash_distribution_id
, arc_posting_control_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
) VALUES (
Rmcd.misc_cash_distribution_id
, -3
, sysdate
, fnd_global.user_id
, fnd_global.login_id
, sysdate
, fnd_global.user_id
);
SELECT message_text
INTO l_message_text
FROM fnd_new_messages
WHERE application_id = 222
AND message_name = p_message_name;
SELECT -- sob.currency_code,
sp.set_of_books_id
, igisp.arc_cash_sob_id
, igisp.arc_unalloc_rev_ccid
INTO -- l_prepare.FuncCurr ,
l_prepare.SetOfBooksId
, l_prepare.CashSetOfBooksId
, l_prepare.UnallocatedRevCcid
FROM ar_system_parameters sp
, igi_ar_system_options igisp
, gl_sets_of_books sob
WHERE sob.set_of_books_id = sp.set_of_books_id;