Issue:
Period not Closing due to Unprocessed Receipts
Solution:
IN this case please run the following update to sweep the data:
1. backup
create table ar_rcpt_hist_4781 as
select * from AR_CASH_RECEIPT_HISTORY_ALL
where CASH_RECEIPT_HISTORY_ID in (4861, 4862)
and CASH_RECEIPT_ID = 4781;
create table ar_rec_appl_4781 as
select * from AR_RECEIVABLE_APPLICATIONS_ALL
where CASH_RECEIPT_ID = 4781;
create table ar_pmt_sched_4781 as
select * from AR_PAYMENT_SCHEDULES_ALL
where CASH_RECEIPT_ID = 4781;
create table xla_ev_4781 as
select ev.* from XLA_EVENTS ev
where ev.entity_id in (select et.entity_id from xla.xla_transaction_entities et
where et.source_id_int_1 = 4781
and et.application_id = 222)
and ev.APPLICATION_ID = 222;
2. datafix
update AR_CASH_RECEIPT_HISTORY_ALL
set REVERSAL_GL_DATE = to_date('01-OCT-2016','DD-MON-YYYY')
where CASH_RECEIPT_HISTORY_ID in (4861)
and CASH_RECEIPT_ID = 4781;
commit;
update AR_CASH_RECEIPT_HISTORY_ALL
set GL_DATE = to_date('01-OCT-2016','DD-MON-YYYY')
where posting_control_id = -3
and gl_date between to_date('01-SEP-2016','DD-MON-YYYY')
and to_date('30-SEP-2016', 'dd-mon-yyyy')
and CASH_RECEIPT_ID = 4781;
commit;
update AR_RECEIVABLE_APPLICATIONS_ALL
set GL_DATE = to_date('01-OCT-2016','DD-MON-YYYY')
where posting_control_id = -3
and gl_date between to_date('01-SEP-2016','DD-MON-YYYY')
and to_date('30-SEP-2016', 'dd-mon-yyyy')
and CASH_RECEIPT_ID = 4781;
commit;
update AR_PAYMENT_SCHEDULES_ALL
set GL_DATE_CLOSED = to_date('01-OCT-2016','DD-MON-YYYY'),
ACTUAL_DATE_CLOSED = to_date('01-OCT-2016','DD-MON-YYYY'),
GL_DATE = to_date('01-OCT-2016','DD-MON-YYYY')
where CASH_RECEIPT_ID = 4781;
commit;
update xla.XLA_EVENTS ev
set ev.EVENT_DATE = to_date('01-OCT-2016','DD-MON-YYYY')
where ev.event_date between to_date('01-SEP-2016','DD-MON-YYYY')
and to_date('30-SEP-2016', 'dd-mon-yyyy')
and ev.event_status_code <> 'P'
and ev.APPLICATION_ID = 222
and ev.ENTITY_ID in (
select et.entity_id
from xla.xla_transaction_entities et
where et.source_id_int_1 = 4781
and et.application_id = 222);
commit;
