Post date: Jan 05, 2016 6:48:19 AM
Dear Ivan,
Here is the fresh set of action plan given by development. Please perform this in test instance only . After data-fix try to account the invoice and payment again, this should resolve the issue.
--Step 0 : Continue with next step even if this fails with table not exists
DROP TABLE xe_bk_22335794;
DROP TABLE aid_bk_22335794;
--Step 1 create backup:
CREATE TABLE xe_bk_22335794 as
SELECT *
FROM xla_events
WHERE event_id in (476653, 479625, 479648, 479683)
AND application_id = 200;
CREATE TABLE aid_bk_22335794 as
SELECT *
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = 87673
AND accounting_event_id = 476653;
--Step 2: Data-fix
UPDATE xla_events
SET event_status_code ='U',
process_status_code ='U',
last_update_date = SYSDATE
WHERE event_id = 476653
AND application_id = 200;
UPDATE ap_invoice_distributions_all aid
SET accrual_posted_flag = 'N',
posted_flag = 'N',
last_update_date =SYSDATE
WHERE aid.invoice_id = 87673
AND accounting_event_id =476653;
COMMIT;
--Step 4 : Open OCT-15 and NOV-15 period in AP and GL and account again.
--Step 5 : If you don't want to open above periods, sweep the event to latest period using below statement and the account again
Provide date input in DD-MON-YYYY format:
UPDATE xla_events xe
SET xe.event_date = TO_DATE('&new_accounting_date','DD-MON-YYYY'),
xe.last_updated_by = -169
WHERE event_id in (476653, 479625, 479648, 479683)
AND xe.application_id = 200;
COMMIT;