How to Sweep Unprocessed Receipt in Next Month of AR (Not for data Corruption Receipts only to change GL Dates of the Receipt)

http://receivablesar.blogspot.com/2016/10/how-to-sweep-unprocessed-receipt-in.html

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;