How to Change Journal Effective Date in GL for Auto Reversal "OR" How is the reversal date of a journal determined? - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/01/how-to-change-journal-effective-date-in.html

Q. When a journal is reversed, how is the Journal Default Effective Date derived into the Reversal Journal?

Q. How is the reversal date of a journal determined?

When creating automatically reversed journals via the Automatic reversal program, the journal entry created, may show an unexpected effective date, for example the last day of the month instead of the first day of the month.

SOLUTION

A. The effective date of the reversal journal is automatically derived in the following manner:

1. If the reversal period is the same as the original journal period, then the default date from the original journal is used.

Example (same period):
- Original Journal Effective Date is 20-JAN-2009
- Reversal Period is Jan-09
- then the effective date of reversal journal will be 20-JAN-2009 (the date of the original journal).

2. If current date is less than reversal period start date, then the period start date is used as the reversal date.

Example (period in the future):
- Original Journal Effective Date is 20-JAN-2009
- Reversal Period is Mar-09
- Current Date is 10-FEB-2009 which is less than reversal period start date i.e. 01-MAR-2009
- then the effective date of reversal journal will be the reversal period start date i.e. 01-MAR-2009

3. If current date is greater than reversal period end date, the period end date is used as the reversal date.

Example (period in the past):
- Original Journal Effective Date is 20-JAN-2009
- Reversal Period is Feb-09
- Current Date is 10-MAR-2009 which is greater than reversal period end date i.e. 28-FEB-2009
- then the effective date of reversal journal will be the reversal period end date i.e. 28-FEB-2009

4. If current date is in the reversal period, the system date is used as the reversal date.

Example (present period):
- Original Journal Effective Date is 20-JAN-2009
- Reversal Period is Feb-09
- Current Date is 10-FEB-2009 which falls in the reversal period date range
- then the effective date of reversal journal will be the system date i.e. 10-FEB-2009

The logic behind this functionality is to arrive the closest date to the current date.

The same defaults are also used by GLLEZL Journal Import when importing journals.

Note : This Note also holds true for journals that Auto Reversal reverses.

--------------------------------------------------------------------------------------

Query to change effective date from backend

SELECT * FROM GL_JE_BATCHES

--update gl_je_batches c set default_effective_date = to_date('01-MAY-15','dd-mon-rr')

WHERE je_batch_id IN

(

select JE_BATCH_ID from gl_je_headers gh

where gh.je_category='2'

and gh.period_name='MAY-15'

and gh.namelike'Rever%'

and gh.status='U'

)

select * from gl_je_headers gh

--update gl_je_headers gh set default_effective_date = to_date('01-MAY-15','dd-mon-rr')

where gh.je_category='2'

and gh.period_name='MAY-15'

and gh.namelike'Rever%'

and gh.status='U'

select * from gl_je_lines s

--update gl_je_lines s set effective_date = to_date('01-MAY-15','dd-mon-rr')

where s.je_header_id in

(select gh.je_header_id from gl_je_headers gh

where gh.je_category='2'

and gh.period_name='MAY-15'

and gh.namelike'Rever%'

and gh.status='U'

)