The below steps need to be performed for setting up Period Type in Oracle EBS /Apps R12

Why we need Adjustment Period ?

Adjustment Period is used to make adjustment to manage the accounting entries for the reconciliation of Financial Periods. The Adjustment Period is not visible in General Ledger.

Calendar Types in Oracle EBS R12 

General Ledger uses the year type to assign a year in the accounting period . In Oracle EBS R12 Calendar can be defined in 2 ways

• Fiscal: year in which an accounting period begins which means the Financial Year is considered as (01-Jul-20 to 30-Jun-21)
• Calendar : year in which an accounting period ends which means the Financial Year is considered as (01-Jan-20 to 31-Dec-20)

• Navigate to General Ledger > Setup > Financials > Calendars > Types
• Enter the Period Type (‘OAH Period’)
• Enter Period Per Year (13) includes 1 Adjustment Period
• Select Year Type as ‘Fiscal’

Define Accounting Calendar in Oracle EBS R12

Define the 12 Periods from JAN-DEC for the specific accounting period

• Enter the Calendar Name (‘OAH Calendar’)
• Enter the Calendar Description
• Enter the Period Prefix for the Accounting Period like JAN for Month January
• Enter the Period Type defined in the Period Type Setup (‘OAH Period’)
• Enter the Period Year in which Accounting Period to be defined (2021)
• Enter Total Number of Quarters in an Accounting Period
• Enter From Date ( ’01-JAN-2020′)
• Enter To Date ( ’31-JAN-2020′)

Define the Adjustment Period as 13 Period for the specific accounting period

• Enter the Period Prefix for the Accounting Period like ADJ for Adjustment Period
• Enter the Period Type defined in the Period Type Setup (‘OAH Period’)
• Enter the Period Year in which Accounting Period to be defined (2021)
• Enter Total Number of Quarters in an Accounting Period
• Enter From Date ( ’31-DEC-2020′)
• Enter To Date ( ’31-DEC-2020′)

Calendar Validation 

Once the defined periods are saved in Calendar, System popups the Calendar Validation option to validate calendar period data. Select Current as the option

The validations are performed to validate the entered Calendar Period data

• Validates Periods overlap
• Validates if Periods have date gaps
• Validates Adjustment period is checked or not
• Validates if Period numbers are greater than the maximum period number for current period type
• Validates if Periods are not in sequential order by date
• Validates if Quarters are missing
• Validates if Quarters are not in sequential order by period
• Validates if Period numbers are missing
• Validates if Period’s start or end dates are more than one year before or after its fiscal year

The Oracle Apps Journal Import validates the accounting data received from other systems in the GL_INTERFACE Table. The list of  journal error lines will be published in the Journal Import Execution Report.

Batch Level Validation:

Journal Import validates the below attributes so that the  batch name available for processing should not already exist for the same Ledger Id  Period in the Oracle Apps  General Ledger application for the particular Period

• Ledger Id
• Period Name
• Batch Name

Journal Level Validation:

Journal Import validates the below attributes so that the journals available for processing should have the required accounting data

• Ledger Id
• Period Name
• Journal Source Name
• Journal Category Name
• Currency Code
• Encumbrance type ID
• Actual Flag
• User Conversion Type
• Budget Version Id
• Accounting Date

Journal Entry Line Level Validation:

Journal Import validates the below attributes so that the journals available for processing should have the required accounting data

Account Validation ( Code Combination) : 

Code Combinations will be considered valid and journal import will be processed successfully if the below validations exists:

• If detail posting to segment combinations are allowed
• If code combinations are enabled for the specified accounting date
• If  code combinations does not include summary accounts

Effective Date Validation: 

Journal Import validates if the Accounting Date is a valid business day and thus General Ledger considers it as the transaction’s effective date.  Below are the Effective Date Rule defined in oracle apps for General Ledger Accounting Date:

• Fail:   Journal Import will Reject the transactions
• Leave Alone: Journal Import will consider transactions regardless of the Accounting Date
• Roll Date:  Journal Import will roll the Accounting Date  to the nearest valid business day

Descriptive Flexfield Validation:

Journal Import validates the descriptive flexfield segments depending on the particular descriptive flexfield.

• Journals – Journal Entry Line Descriptive Flexfield
  • The descriptive flexfield global segments have valid values

• Journals – Captured Information Descriptive Flexfield
  • The descriptive flexfield context dependent segments , descriptive flexfield context  and descriptive flexfield context dependent segments  have valid values
• Value Added Tax Descriptive Flexfield
  • The descriptive flexfield context is set to Yes or No and descriptive flexfield context dependent segments have valid values

USSGL Transaction Code:

In case of  Public  Sector General Ledger, Journal Import validates the USSGL Transaction Code

The Oracle apps General Ledger Interface Table – GL_INTERFACE is the entry point for all journals accounting data which have been imported from other systems. The Journal Import Program validates the accounting data received in the GL_INTERFACE Table and imports the available data into Journal entries in the Oracle Apps GL Application.

The Journal Import Program uses the GL_INTERFACE Table Column data to store the accounting data into the Journal defined categories.

Below given are few of the main columns for which Journal Import uses during processing

General Ledger SELECT Query to get the details for the Budget Name and Budget Entity Name

SELECT GBI.* FROM GL_BUDGET_INTERFACE GBI
WHERE 1=1
AND GBI.LEDGER_ID = <PROVIDE_LEDGER_ID>
AND GBI.BUDGET_NAME= '<PROVIDE_BUDGET_NAME>'
AND GBI.BUDGET_ENTITY_NAME= '<PROVIDE_BUDGET_ENTITY_NAME>'
AND GBI.FISCAL_YEAR = '<PROVIDE_FISCAL_YEAR>' --2016

Get the count for the Budget lines from the Budget Interface Table that need to be deleted

SELECT count(1) FROM GL_BUDGET_INTERFACE GBI
WHERE 1=1
AND GBI.LEDGER_ID = <PROVIDE_LEDGER_ID>
AND GBI.BUDGET_NAME= '<PROVIDE_BUDGET_NAME>'
AND GBI.BUDGET_ENTITY_NAME= '<PROVIDE_BUDGET_ENTITY_NAME>'
AND GBI.FISCAL_YEAR = '<PROVIDE_FISCAL_YEAR>'

General Ledger DELETE Query to remove /delete  the budget lines  for the provided Budget Name and Budget Entity Name

DELETE FROM GL_BUDGET_INTERFACE
WHERE 1=1
AND GBI.LEDGER_ID = <PROVIDE_LEDGER_ID>
AND GBI.BUDGET_NAME= '<PROVIDE_BUDGET_NAME>'
AND GBI.BUDGET_ENTITY_NAME= '<PROVIDE_BUDGET_ENTITY_NAME>'
AND GBI.FISCAL_YEAR = '<PROVIDE_FISCAL_YEAR>'

General Ledger SQL Query  to get Journal Batch workflow Item and process activities:

SELECT WF_PROCESS.PROCESS_NAME,
WF_IT_ACT_STATUS.*
FROM WF_ITEM_ACTIVITY_STATUSES WF_IT_ACT_STATUS,
WF_PROCESS_ACTIVITIES WF_PROCESS
WHERE 1=1
AND ITEM_KEY LIKE '%<PROVIDE_JE_BATCH_ID>%'
AND WF_IT_ACT_STATUS.ITEM_TYPE = 'GLBATCH'
AND WF_PROCESS.INSTANCE_ID = WF_IT_ACT_STATUS.PROCESS_ACTIVITY
ORDER BY BEGIN_DATE, END_DATE

SQL Query in Oracle apps R12 to get user supervisor details with user authorized limit

SELECT
FU.USER_NAME                                      " USER ID" ,
PER_PEOPLE.FULL_NAME                              " USER FULL NAME",
PER_ASSIGN.D_SUPERVISOR_ID                        " SUPERVISOR USER ID" ,
GL_AUTH_LIMIT1.AUTHORIZATION_LIMIT                " USER AUTH LIMIT" ,
GL_AUTH_LIMIT2.AUTHORIZATION_LIMIT                " SUPERVISOR AUTH LIMIT"
FROM
FND_USER FU,
PER_ALL_PEOPLE_F PER_PEOPLE,
PER_ASSIGNMENTS_V7 PER_ASSIGN,
GL_AUTHORIZATION_LIMITS_V GL_AUTH_LIMIT1,
GL_AUTHORIZATION_LIMITS_V GL_AUTH_LIMIT2
WHERE 1=1
AND FU.EMPLOYEE_ID=PER_PEOPLE.PERSON_ID
AND PER_PEOPLE.PERSON_ID=PER_ASSIGN.PERSON_ID(+)
AND FU.EMPLOYEE_ID= GL_AUTH_LIMIT1.EMPLOYEE_ID(+)
AND PER_ASSIGN.SUPERVISOR_ID= GL_AUTH_LIMIT2.EMPLOYEE_ID(+)
AND FU.USER_NAME IN (<PROVIDE_USER_NAME>)

Query to get the Journal Header Level Attributes for the Journal Batches

• Journal Posted Date is NOT NULL
• Period Name let you fetch records for a particular Period
• Source Name provides from which Source it is being received
• Category Name provides for which category Journal is being received

SELECT GJH.JE_BATCH_ID
, GJB.NAME " JOURNAL BATCH NAME"
, GJH.NAME " JOURNAL NAME"
, SOB.SHORT_NAME ||'-'||GL.NAME " LEDGER NAME"
, GJH.PERIOD_NAME " PERIOD NAME"
, GJH.STATUS " JOURNAL STATUS"
, GLS.USER_JE_SOURCE_NAME " JOURNAL SOURCE"
, GLC.USER_JE_CATEGORY_NAME " JOURNAL CATEGORY"
, GJH.CURRENCY_CODE " CURRENCY"
, GJH.POSTED_DATE " POSTED DATE"
, GJH.CREATION_DATE " CREATION DATE"
FROM GL_LEDGERS GL ,
GL_JE_BATCHES GJB,
GL_JE_HEADERS GJH,
GL_JE_SOURCES GLS,
GL_JE_CATEGORIES GLC,
GL_SETS_OF_BOOKS SOB
WHERE 1=1
AND GL.LEDGER_ID = SOB.SET_OF_BOOKS_ID
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GLS.JE_SOURCE_NAME = GJH.JE_SOURCE
AND GLC.JE_CATEGORY_NAME = GJH.JE_CATEGORY
AND GJH.POSTED_DATE IS NOT NULL
AND GJH.PERIOD_NAME ='MAR-16'