GL Journal Import Conversion Test

Oracle General Ledger Journal Import Process Overview

Abstract

Accounting transactions are originated as a result of normal business activities in Financial and Manufacturing modules of Oracle Applications, as well as in external modules. In order to register those transactions into the General Ledger, a process called Journal Import must take place.

This process works based on the existing data in an interface table. The table is called GL_INTERFACE.

Each subledger populates this table through one or more specific processes. Once the information is in the interface table, the Journal Import process loads the General Ledger tables creating unposted journal batches.
Details

Section 1: Journal Import Overview

Accounting transactions are originated as a result of normal business activities in Financial and Manufacturing modules of Oracle Applications, as well as in external modules. In order to register those transactions into the General Ledger, a process called Journal Import must take place.
This process works based on the existing data in an interface table. The table is called GL_INTERFACE.

Each subledger populates this table through one or more specific processes. Once the information is in the interface table, the Journal Import process loads the General Ledger tables creating unposted batches.
The only exception to this process is the Oracle Fixed Assets module before Family Pack G, which directly loads the General Ledger tables. Currently FA also populates the GL_INTERFACE table to transfer the accounting data to GL.

The following diagram illustrates the flow of the procedure:
SEQ.   ACTION   CONDITION   GO TO
1   Populate GL interface table (subledgers, external systems, ADI, etc.)       2
2   Import journal by Source / Group ID       3
3   Journal Import terminated without Errors? (check the execution report for errors or warnings)   Yes   11
No   4
4   Is the Source a standard Subledger?   Yes   5
No   9
5   Correct the errors in the Source. In case of a standard subledger you may need support assistance.       6
6   Is a R12 environment? (then no need to delete as already rolled back to XLA)   Yes   8
No   7
7   Delete the incorrect batch to be imported again.        8
8   Need to Transfer the corrected batch again from the source       1
9   Many Errors ?   Yes   7
No   10
10   Use the Correct Journals form to fix the errors in the interface       2
11   The batch is created ready for Posting       END

Section 2: Data structures involved in the Journal Import process
GL_INTERFACE
This table is used to import journal entry batches through Journal Import. You insert rows in this table and then use the Run Journal Import form to create journal batches. Currently the Journal Import can import from tables with different names, but the same structure.
You must supply values for all NOT NULL columns.
Not Null Columns (may have variations depending on the release):
STATUS
ACCOUNTING_DATE
CURRENCY_CODE
DATE_CREATED
CREATED_BY
ACTUAL_FLAG
USER_JE_CATEGORY_NAME
USER_JE_SOURCE_NAME
GL_INTERFACE_CONTROL
This table is used to control Journal Import execution. Whenever you start Journal Import from the Import Journals form, a row is inserted into this table for each source and group_id that you specified. When Journal Import completes, it deletes these rows from the table. If you run Journal Import from outside of the Import Journals form, you must insert a row into the GL_INTERFACE_CONTROL table first.
Is from this table that Journal Import knows the name of the interface table to import (INTERFACE_TABLE_NAME).
The phase of the process is indicated through the Status field. It can have the following values:
P - Pending; the journal import process has not been started.
S - Selected; the data group identified by Source and Group id has been selected for processing.
I - In process; the data group identified by Source and Group id has been selected and is actually in process.
Not Null Columns:
JE_SOURCE_NAME
STATUS
GL_INTERFACE_HISTORY
This table stores the rows that are successfully imported from the GL_INTERFACE table through the import process. The General Ledger application adds rows to this table every time you successfully run Journal Import, with the Archive Journal Import Data option enabled. This option is defined on the Concurrent Program Controls form. This option increases execution time but can be used for auditing purposes.
You use this information for historical reference only. General Ledger does not use the data stored in this table.
GL_IMPORT_REFERENCES
Depending on the Journal Source configuration, this table may be populated by the Journal Import Process. You can specify the Journal Entry sources for which you want to maintain your transaction's origin by entering 'Yes' in the Import Journal References field of the Journal Sources form. When Journal Import is run for a source that has 'Yes' set for the Import Journal References field, this table will be populated with the necessary information to match journal entry lines to the originating documents in the source. This functionality is known as Account Drilldown.
This uses the following fields to link to the subledger information:
gl_sl_link_id
gl_sl_link_table
In R12 normal subledgers this always points to the XLA tables (value is 'XLAJEL'), whilst in 11i it points to subledger specific tables.

The data stored in this table may vary depending on the release and type of transaction. The reference fields are used in different ways by each subledger.
In the following table is the use that some of the most common subledgers give to these fields:
Transactions   Reference
1   Reference
2   Reference
3   Reference
4   Reference
5   Reference
6   Reference
7   Reference
8   Reference
9   Reference
10
RELEASE 11i

Payables/Payments   Vendor name   Invoice id   Check id   External check number   Paid invoice number   'AP Payments'   SOB id   Invoice distribution line number   Invoice payment id   Cash, Discount, Liability, Exchange Gain,
Exchange Loss,
Future Pay
Payables/Invoices   Vendor name   Invoice id   Distribution line number
Invoice number   'AP Invoices'   SOB id

Expense, Liability
Receivables/Transactions   posting control id   customer trx id   cust trx line gl dist id   trx number   cust account number   CUSTOMER   bill to customer id   CM, DM, CB, INV   type||account class   RA_CUST_TRX_LINE_GL_DIST
Receivables/Receipts   posting control id   cash receipt id ||cash receipt history id   line id   receipt number

pay from customer   TRADE   TRADE_'source_type'   AR_CASH_RECEIPT_HISTORY
Receivables/Applications (cash)   posting control id   cash receipt id ||receivable application id   line id   receipt number   trx number, null   cust trx type   pay from customer   TRADE, CCUR   application type||source type   AR_RECEIVABLE_APPLICATIONS
Receivables/Applications (CM)   posting control id   receivable application id   line id   trx number   trx number, null   cust trx type   bill to customer id   CMAPP   application type||source type   AR_RECEIVABLE_APPLICATIONS
Receivables/Adjustments   posting control id   adjustment id   line id   trx number   adjustment number   cust trx type   bill to customer id   ADJ   ADJ_source type   AR_ADJUSTMENTS
Receivables/Miscel.Rec.   posting control id   cash receipt id   line id   receipt number   cash receipt history id
pay from customer   MISC   MISC_source type   AR_CASH_RECEIPT_HISTORY
Receivables/Bills Receivable   posting control id   transaction history id   line id   trx number   customer trx id   cust trx type   drawee id   cust trx type   BR_source type   AR_TRANSACTION_HISTORY
Purchasing   PO, REQ   doc header id   doc distribution id   doc number

Inventory   mta.gl batch id   mta.organization code

Consolidations (TRX)   consolidation id   consolidation run id   source hournal header id   source journal line num   source ccid   'Transaction'   subsidiary SOB
source batch id
Consolidations (BAL)   consolidation id   consolidation run id   source ccid   currency type   budget version id   'Balances'   subsidiary SOB   period   amount type   actual amount
Intercompany   transaction number   transaction id   offset line number   CLEARING, OFFSET   SENDER, RECEIVER

RELEASE 12

(all subledgers)

entity_id   accounting_event_id   ae_header_id   ae_line_num   DR amount   CR amount

Section 3: Transfer and Import Processes
A. Entering transactions into the Interface Table
In Oracle Applications, modules such as AP, AR, PO, INV and WIP have concurrent processes that take the information from database structures in which the business activity is stored and inserts it into the GL_INTERFACE table.

Some of the transferring concurrent programs by module in Release 11i are :
Module   Program   Executable
AP   Payables Transfer to General Ledger   APPPST
FA   Create Journal Entries   FAPOST
AR   General Ledger Transfer Program   ARGLTP
AX   AX Transfer to GL   AXXPSTGL
PA   Interface Burden Cost to GL   PADTBC
PRC: Interface Labor Costs to General Ledger   PAGGLT
PRC: Interface Usage Costs to General Ledger   PASGLT
PRC: Interface Revenue to General Ledger   PATTGL
INV   Transfer transactions to GL   INCTGL

In R12 the Create Accounting for each subledger populates the common XLA tables (Subledger Accounting). When the accounting is Final then the Transfer Journal Entries to GL process populates the GL interface and automatically submits the Journal Import. It can also submit the Post for the imported journals automatically.
For more detail information please see Create Accounting and Transfer Journal Entries to GL Programs.

If the information comes from an external module, the GL_INTERFACE table must be populated through a means such as SQL*Loader, Pro*C, a PL/SQL procedure or Applications Desktop Integrator (ADI or Web ADI)).

Following is a description of important fields in the interface table:

Account Combination
An account combination can be stored in two ways:
Entering a value for field CODE_COMBINATION_ID.
Entering a value for each segment of the accounting flexfield in use. The fields are SEGMENT1, SEGMENT2,..., SEGMENTn according to the definition of the structure.
If the account combination is stored in both ways, the Journal Import process will take the value stored in the SEGMENTn columns.

Group ID
This field is used by the sending process to group the transactions.
When the information comes from Oracle Applications subledgers, the value for this field is assigned automatically and is defined by the sequence GL_INTERFACE_CONTROL_S. The Journal Import process selects records based on SOURCE and optionally GROUP_ID.

Journal Type
Budget, encumbrance or actual journals can be entered into GL_INTERFACE. The type of journal that is being created is stored in the field ACTUAL_FLAG. The possible values are:
A : Actual
E : Encumbrance
B : Budget
Currency Conversion
Journals entered in a currency other than the functional currency can be stored in two ways:
Enter the amount, conversion type and conversion date in the fields ENTERED_CR or ENTERED_DR, USER_CURRENCY_CONVERSION_TYPE and CURRENCY_CONVERSION_DATE respectively. If you enter a rate type of User, then you must also enter a conversion rate in the CURRENCY_CONVERSION_RATE column. For all other conversion types you must enter a conversion date in the CURRENCY_CONVERSION_DATE column. In this case, the Journal Import process automatically calculates the amount in the functional currency.
Populate fields ENTERED_CR or ENTERED_DR and ACCOUNTED_CR or ACCOUNTED_DR without specifying a conversion type and date. The Accounted amount must be your converted debit or credit amount.
Journal Source
This value is stored in field USER_JE_SOURCE_NAME and corresponds to the name of the journal source. Examples: Receivables, Payables, Inventory, etc.

Reference Fields
The reference fields are optional, but if they are defined, they are interpreted in the following way:

REFERENCE1   Batch name. Its structure is: <Reference1><Source><Request ID><Balance Type ><Group ID>
REFERENCE2   Batch description; if one is not defined, it will look like: Journal Import <Source><Request ID>
REFERENCE4   Journal entry name; with the format: <Category><Currency>< Conversion Type >< Conversion Rate>< Conversion Date ><Encumbrance Type ID><Budget Version>. Some of this information depends on the journal type.
REFERENCE5   Journal entry description; if one is not given, it will follow this format: Journal Import - Request ID
REFERENCE6   Reference or journal number. If it is not defined, it is automatically defined as Journal Import Created.
REFERENCE7   Reverse Journal Flag
REFERENCE8   Reverse Journal Period
REFERENCE10   Journal line description
REFERENCE21...30   The values and meanings for these fields depend on the Journal Source and Release. They store information used to identify and match the journal with the source document. The values for these fields map to REFERENCE_1 through REFERENCE_10 in the GL_JE_LINES table and the GL_IMPORT_REFERENCES table.

B. Journal Import
Journal import is a complex tool, which performs a lot of validation on thousands of records at a time. Journal import loads data from the subledgers into the GL_INTERFACE table and then into GL_JE_BATCHES, GL_JE_HEADERS and GL_JE_LINES tables.
Define the concurrent program controls.
The Concurrent Program Controls form can be used to improve the performance of the Journal Import, MassAllocation/MassBudgeting, and Open Period programs. The performance of Journal Import can be improved by increasing the number of journal lines it holds in memory.
Before R12 this setup was done in a specific form:

Form name: GLXSTCPC
Responsibility: General Ledger Super User
Navigation: Setup > System > Control

In R12 the setup uses the Profile Options instead:
GL: Number of Accounts In Memory
GL: Archive Journal Import Data
GL: Number of Records to Process at Once
GL Journal Import: Separate Journals by Accounting Date
Submit Journal Import Run
Once the data is in the GL_INTERFACE table, you can manually submit the import process from the Import Journals form.

Form name: GLXJIRUN
Responsibility: General Ledger Super User
Navigation: Journals > Import > Run

Currently it is also possible to submit the journal import from the Submit Concurrent Requests form.

Source: Select the Source from which you want Journal Import to create journal entries, such as Receivables, Payables, etc..
Note that in R12, the transactions are only in the interface temporarily during the journal import (the data stays in the XLA tables), therefore it is not possible to submit the journal import for subledger sources using subledger accounting. The Journal Import program is automatically launched by the transfer accounting program.

Group ID: If there is a value in the GROUP_ID field in the GL_INTERFACE table, either intentionally populated by the user, or by the subledger creating these transactions, then to import these transactions into General Ledger, the user must specify the Group ID on the Import Journals form.
A list of values option is provided on the Group ID field on the Import Journals form, which allows the users to pick a valid value (existing in the interface tables). General Ledger will then import data with the journal source and Group ID combination you specify.
If you do not specify a Group ID, General Ledger imports data from the specified journal entry source with no corresponding Group ID (null Group ID).
In this case it can happen that different batches in the interface with null Group ID get merged during the journal import. To avoid this we recommend to always use the Group ID.

You can choose your run options as well as whether to import descriptive flexfields at this time.
Journal Import Concurrent Program: GLLEZL
This concurrent process generates a request log file and also an execution report. The request log file shows valuable information for troubleshooting purposes, such as:
Set of books or Ledger ID and name
Chart of accounts ID
Number of segments in the accounting flexfield and a description of each segment
Functional currency
Whether suspense accounting is enabled
The Journal Import Execution Report summarizes the results of the import and points out the lines that have errors.
It also contains a list in the end with the description of all the possible error codes (it doesn't mean that they have occurred).

C. Journal Import Correct
If the Journal Import run results in error, no records with the selected Source and Group_id will be imported. All of those rows will remain in the GL_INTERFACE table.
In R12, the rows comming from subledger accounting are rolled back to the XLA tables and removed from the interface, so they must be corrected at subledger level and then transferred again, to avoid reconciliation problems between GL and the subledgers.

You can use the Correct Journal Import Data form to correct the errors.
The first screen gives you the option to find the journals with certain errors.
Form name: GLXJICOR
Responsibility: General Ledger Super User
Navigation: Journals > Import > Correct

Query on the errors you find in the Journal Import Execution Report to correct the data. You can only query journal import lines that have a status of Error or Corrected.

This screen shows the actual journal with the errors.
Examples: EF04, EF02 etc.

Form name: GLXJICOR

Once corrected you can then click on the Import Journals button to rerun Journal Import.
The screen shows the different types of information in the journal you can choose from:
Batches/Journals,
Accounts,
Journal Lines,
Descriptive Flexfields
References.
Depending on what the error is, select the part of the journal you need to go to in order to make your corrections.

D. Journal Import Delete
Use this ONLY if the data can be repopulated into the GL_INTERFACE table, such as from a spreadsheet or from an external source. The principle is the same as for journal correction: only the batches with status of error can be deleted.
In R12 this is not possible to delete subledger batches because the rows are rolled back to the XLA tables and removed from the interface.
Currently the Deletion of the journals is submitted as a Concurrent Request: Program - Delete Journal Import Data.

Older versions use a form to delete the batch.

Form name: GLXJIDEL
Responsibility: General Ledger Super User
Navigation: Journals > Import > Delete

Select the Source, Request ID and Group ID.

WARNING!!! After you Delete, you can not get the data back again.
If from a spreadsheet or another system (i.e. not a subledger) then it can be re-sent.
So be careful when you decide to do this!

Section 4: Journal Import Error Codes
Following is a sample of Journal Import validation errors (Per Journal Import Execution Report). This may not be complete as some new codes may be added as needed.

Period Error Codes
EP01 This date is not in any open or future enterable period.
EP02 This set of books does not have any open or future enterable periods.
EP03 This date is not within any period in an open encumbrance year.
EP04 This date is not a business day.
EP05 There are no business days in this period.
Unbalanced Journal Error Codes
WU01 Warning: This journal entry is unbalanced. It is accepted because suspense posting is allowed in this set of books.
EU02 This journal entry is unbalanced and suspense posting is not allowed in this set of books.
EU03 This encumbrance journal entry is unbalanced and the Reserve for Encumbrance account is not defined.
Flexfield Error Codes
EF01 This Accounting Flexfield is inactive for this accounting date.
EF02 Detail posting not allowed for this Accounting Flexfield.
EF03 Disabled Accounting Flexfield.
EF04 This is an invalid Accounting Flexfield. Check your cross-validation rules and segment values.
EF05 There is no Accounting Flexfield with this Code Combination ID.
EF06 The alternate account is invalid
WF01 An alternate account was used instead of the original account
WF02 A suspense account was used instead of the original account
Foreign Currency Error Codes
EC01 A conversion rate must be entered when using the User conversion rate type.
EC02 There is no conversion date supplied.
EC03 A conversion rate type or an accounted amount must be supplied when entering foreign currency journal lines.
EC04 There is no conversion rate entered for this conversion date.
EC05 There is no conversion rate entered for this conversion rate type.
EC06 There are no conversion rates for this currency.
EC08 Invalid currency code.
EC09 No currencies are enabled.
EC10 Encumbrance journals cannot be created in a foreign currency.
EC11 Invalid conversion rate type.
EC12 The entered amount must equal the accounted amount in a functional or STAT currency journal line.
EC13 The entered amount multiplied by the conversion rate must equal the accounted amount.
ECW1 Warning: Converted amounts could not be validated because the conversion rate type is not specified.
Budget Error Codes
EB01 A budget version is required for budget lines.
EB02 Journals cannot be created for a frozen budget.
EB03 The budget year is not open.
EB04 This budget does not exist for this set of books.
EB05 The encumbrance_type_id column must be null for budget journals.
EB06 A period name is required for budget journals.
EB07 This period name is not valid. Check calendar for valid periods.
EB08 Average journals cannot be created for budgets.
Encumbrance Error Codes
EE01 An encumbrance type is required for encumbrance lines.
EE02 Invalid or disabled encumbrance type.
EE03 Encumbrance journals cannot be created in the STAT currency.
EE04 The budget_version_id column must be null for encumbrance lines.
EE05 Average journals cannot be created for encumbrances.
Reversal Error Codes
ER01 A reversal period name must be provided.
ER02 This reversal period name is invalid. Check your calendar for valid periods.
ER03 A reversal date must be provided
ER04 This reversal date is not in a valid period.
ER05 This reversal date is not in your database date format.
ER06 Your reversal date must be the same as or after your effective date.
ER07 This reversal date is not a business day.
ER08 There are no business days in your reversal period.
Descriptive Flexfield Error Codes
ED01 The context and attribute values do not form a valid descriptive flexfield for Journals - Journal Entry Lines.
ED02 The context and attribute values do not form a valid descriptive flexfield for Journals - Captured Information.
ED03 The context and attribute values do not form a valid descriptive flexfield for Value Added Tax.
Miscellaneous Error Codes
EM01 Invalid journal entry category.
EM02 There are no journal entry categories defined.
EM03 Invalid set of books id.
EM04 The value in the actual_flag must be "A" (actuals), "B" (budgets), or "E" (encumbrances).
EM05 The encumbrance_type_id column must be null for actual journals.
EM06 The budget_version_id column must be null for actual journals.
EM07 A statistical amount belongs in the entered_dr(cr) column when entering a STAT currency journal line.
EM09 There is no Transaction Code defined.
EM10 Invalid Transaction Code.
EM12 An Oracle error occurred when generating sequential numbering.
EM13 The assigned sequence is inactive.
EM14 There is a sequential numbering setup error resulting from a missing grant or synonym.
EM17 Sequential numbering is always used and there is no assignment for this set of books and journal entry category.
EM18 Manual document sequences cannot be used with Journal Import.
EM19 Value Added Tax data is only valid in conjunction with actual journals.
EM21 Budgetary Control must be enabled to import this batch.
EM22 A conversion rate must be defined for this accounting date, your default conversion rate type, and your dual currency.
EM23 There is no value entered for the Dual Currency Default Rate Type profile option.
EM24 Average journals can only be imported into consolidation sets of books.
EM25 Invalid average journal flag. Valid values are "Y", "N", and null.

Section 5: Troubleshooting Scripts
Following are some useful scripts to troubleshoot Journal Import errors.
Estimate the number of lines pending to import from GL_INTERFACE:
q_ji1.sql :
/* Lists all pending journal import groups and the number*/
/* of records in each group */
set linesize 120
col Currency format a8
spool q_ji1
Select set_of_books_id Book
, user_je_source_name Source
, user_je_category_name Category
, currency_code Currency
, trunc (date_created) Created
, actual_flag Journal
, count(*)
from gl_interface
group by set_of_books_id
, user_je_source_name
, user_je_category_name
, currency_code
, trunc (date_created)
, actual_flag
order by set_of_books_id, trunc (date_created)
/
spool off
Total the debit and credit amounts within a Group_ID:
q_ji2.sql :
/* Calculates the total amounts for a given group id */
set linesize 120
col s_en_cr format S999,999,999.99
col s_en_dr format S999,999,999.99
col s_acc_cr format S999,999,999.99
col s_acc_dr format S999,999,999.99
spool q_ji3
Select user_je_source_name Source
, User_je_category_name Category
, currency_code Currency
, sum (entered_cr) s_en_cr
, sum (entered_dr) s_en_dr
, sum (accounted_cr) s_acc_cr
, sum (accounted_dr) s_acc_dr
from gl_interface
where group_id = &group_id
group by user_je_source_name
, User_je_category_name
, currency_code
/
spool off
The following query groups journal lines for a particular group_id coming from Oracle Payables by Category and Internal Invoice Id, and sums the debit and credit amounts (in 11i):

q_ji3.sql :
set linesize 120
col s_en_cr format S999,999,999.99
col s_en_dr format S999,999,999.99
col s_acc_cr format S999,999,999.99
col s_acc_dr format S999,999,999.99
col reference22 format a15
spool q_ji4
Select USER_JE_CATEGORY_NAME category
, reference22 invoice_id
, sum (entered_cr) s_en_cr
, sum (entered_dr) s_en_dr
, sum (accounted_cr) s_acc_cr
, sum (accounted_dr) s_acc_dr
, count(*)
from gl_interface
where user_jr_source_name ='Payables' and
group_id = &group_id
group by USER_JE_CATEGORY_NAME, reference22
;
spool off

Query q_ji3.sql can be modified to be used with the reference fields for other sources.

Oracle General Ledger Journal Import

http://oracleappsfreetraining.blogspot.in/2013/06/general-ledger-journal-import-process.html

Oracle General Ledger Journal Import Process Overview

Abstract

Details

Section 1: Journal Import Overview

SEQ.	ACTION	CONDITION	GO TO
1	Populate GL interface table (subledgers, external systems, ADI, etc.)		2
2	Import journal by Source / Group ID		3
3	Journal Import terminated without Errors? (check the execution report for errors or warnings)	Yes	11
3		No	4
4	Is the Source a standard Subledger?	Yes	5
4	Is the Source a standard Subledger?	No	9
5	Correct the errors in the Source. In case of a standard subledger you may need support assistance.		6
6	Is a R12 environment? (then no need to delete as already rolled back to XLA)	Yes	8
6		No	7
7	Delete the incorrect batch to be imported again.		8
8	Need to Transfer the corrected batch again from the source		1
9	Many Errors ?	Yes	7
9	Many Errors ?	No	10
10	Use the Correct Journals form to fix the errors in the interface		2
11	The batch is created ready for Posting		END

Section 2: Data structures involved in the Journal Import process

GL_INTERFACE

This table is used to import journal entry batches through Journal Import. You insert rows in this table and then use the Run Journal Import form to create journal batches. Currently the Journal Import can import from tables with different names, but the same structure.
You must supply values for all NOT NULL columns.

Not Null Columns (may have variations depending on the release):

· STATUS

· ACCOUNTING_DATE

· CURRENCY_CODE

· DATE_CREATED

· CREATED_BY

· ACTUAL_FLAG

· USER_JE_CATEGORY_NAME

· USER_JE_SOURCE_NAME

GL_INTERFACE_CONTROL

This table is used to control Journal Import execution. Whenever you start Journal Import from the Import Journals form, a row is inserted into this table for each source and group_id that you specified. When Journal Import completes, it deletes these rows from the table. If you run Journal Import from outside of the Import Journals form, you must insert a row into the GL_INTERFACE_CONTROL table first.
Is from this table that Journal Import knows the name of the interface table to import (INTERFACE_TABLE_NAME).
The phase of the process is indicated through the Status field. It can have the following values:

· P - Pending; the journal import process has not been started.

· S - Selected; the data group identified by Source and Group id has been selected for processing.

· I - In process; the data group identified by Source and Group id has been selected and is actually in process.

Not Null Columns:

· JE_SOURCE_NAME

· STATUS

GL_INTERFACE_HISTORY

This table stores the rows that are successfully imported from the GL_INTERFACE table through the import process. The General Ledger application adds rows to this table every time you successfully run Journal Import, with the Archive Journal Import Data option enabled. This option is defined on the Concurrent Program Controls form. This option increases execution time but can be used for auditing purposes.
You use this information for historical reference only. General Ledger does not use the data stored in this table.

GL_IMPORT_REFERENCES

Depending on the Journal Source configuration, this table may be populated by the Journal Import Process. You can specify the Journal Entry sources for which you want to maintain your transaction's origin by entering 'Yes' in the Import Journal References field of the Journal Sources form. When Journal Import is run for a source that has 'Yes' set for the Import Journal References field, this table will be populated with the necessary information to match journal entry lines to the originating documents in the source. This functionality is known as Account Drilldown.
This uses the following fields to link to the subledger information:

· gl_sl_link_id

· gl_sl_link_table

In R12 normal subledgers this always points to the XLA tables (value is 'XLAJEL'), whilst in 11i it points to subledger specific tables.

The data stored in this table may vary depending on the release and type of transaction. The reference fields are used in different ways by each subledger.
In the following table is the use that some of the most common subledgers give to these fields:

Transactions	Reference 1	Reference 2	Reference 3		Reference 4	Reference 5	Reference 6	Reference 7	Reference 8	Reference 9	Reference 10
RELEASE 11i
Payables/Payments	Vendor name	Invoice id		Check id	External check number	Paid invoice number	'AP Payments'	SOB id	Invoice distribution line number	Invoice payment id	Cash, Discount, Liability, Exchange Gain, Exchange Loss, Future Pay
Payables/Invoices	Vendor name	Invoice id		Distribution line number		Invoice number	'AP Invoices'	SOB id			Expense, Liability
Receivables/Transactions	posting control id	customer trx id		cust trx line gl dist id	trx number	cust account number	CUSTOMER	bill to customer id	CM, DM, CB, INV	type\|\|account class	RA_CUST_TRX_LINE_GL_DIST
Receivables/Receipts	posting control id	cash receipt id \|\|cash receipt history id		line id	receipt number			pay from customer	TRADE	TRADE_'source_type'	AR_CASH_RECEIPT_HISTORY
Receivables/Applications (cash)	posting control id	cash receipt id \|\|receivable application id		line id	receipt number	trx number, null	cust trx type	pay from customer	TRADE, CCUR	application type\|\|source type	AR_RECEIVABLE_APPLICATIONS
Receivables/Applications (CM)	posting control id	receivable application id		line id	trx number	trx number, null	cust trx type	bill to customer id	CMAPP	application type\|\|source type	AR_RECEIVABLE_APPLICATIONS
Receivables/Adjustments	posting control id	adjustment id		line id	trx number	adjustment number	cust trx type	bill to customer id	ADJ	ADJ_source type	AR_ADJUSTMENTS
Receivables/Miscel.Rec.	posting control id	cash receipt id		line id	receipt number	cash receipt history id		pay from customer	MISC	MISC_source type	AR_CASH_RECEIPT_HISTORY
Receivables/Bills Receivable	posting control id	transaction history id		line id	trx number	customer trx id	cust trx type	drawee id	cust trx type	BR_source type	AR_TRANSACTION_HISTORY
Purchasing	PO, REQ	doc header id		doc distribution id	doc number
Inventory	mta.gl batch id	mta.organization code
Consolidations (TRX)	consolidation id	consolidation run id		source hournal header id	source journal line num	source ccid	'Transaction'	subsidiary SOB		source batch id
Consolidations (BAL)	consolidation id	consolidation run id		source ccid	currency type	budget version id	'Balances'	subsidiary SOB	period	amount type	actual amount
Intercompany	transaction number	transaction id		offset line number	CLEARING, OFFSET	SENDER, RECEIVER
RELEASE 12
(all subledgers)						entity_id	accounting_event_id	ae_header_id	ae_line_num	DR amount	CR amount

Section 3: Transfer and Import Processes

A. Entering transactions into the Interface Table

In Oracle Applications, modules such as AP, AR, PO, INV and WIP have concurrent processes that take the information from database structures in which the business activity is stored and inserts it into the GL_INTERFACE table.

Some of the transferring concurrent programs by module in Release 11i are :

Module	Program	Executable
AP	Payables Transfer to General Ledger	APPPST
FA	Create Journal Entries	FAPOST
AR	General Ledger Transfer Program	ARGLTP
AX	AX Transfer to GL	AXXPSTGL
PA	Interface Burden Cost to GL	PADTBC
	PRC: Interface Labor Costs to General Ledger	PAGGLT
	PRC: Interface Usage Costs to General Ledger	PASGLT
	PRC: Interface Revenue to General Ledger	PATTGL
INV	Transfer transactions to GL	INCTGL

In R12 the Create Accounting for each subledger populates the common XLA tables (Subledger Accounting). When the accounting is Final then the Transfer Journal Entries to GL process populates the GL interface and automatically submits the Journal Import. It can also submit the Post for the imported journals automatically.
For more detail information please see Create Accounting and Transfer Journal Entries to GL Programs.

If the information comes from an external module, the GL_INTERFACE table must be populated through a means such as SQL*Loader, Pro*C, a PL/SQL procedure or Applications Desktop Integrator (ADI or Web ADI)).

Following is a description of important fields in the interface table:

Account Combination

An account combination can be stored in two ways:

· Entering a value for field CODE_COMBINATION_ID.

· Entering a value for each segment of the accounting flexfield in use. The fields are SEGMENT1, SEGMENT2,..., SEGMENTn according to the definition of the structure.

If the account combination is stored in both ways, the Journal Import process will take the value stored in the SEGMENTn columns.

Group ID

This field is used by the sending process to group the transactions.
When the information comes from Oracle Applications subledgers, the value for this field is assigned automatically and is defined by the sequence GL_INTERFACE_CONTROL_S. The Journal Import process selects records based on SOURCE and optionally GROUP_ID.

Journal Type

Budget, encumbrance or actual journals can be entered into GL_INTERFACE. The type of journal that is being created is stored in the field ACTUAL_FLAG. The possible values are:

· A : Actual

· E : Encumbrance

· B : Budget

Currency Conversion

Journals entered in a currency other than the functional currency can be stored in two ways:

· Enter the amount, conversion type and conversion date in the fields ENTERED_CR or ENTERED_DR, USER_CURRENCY_CONVERSION_TYPE and CURRENCY_CONVERSION_DATE respectively. If you enter a rate type of User, then you must also enter a conversion rate in the CURRENCY_CONVERSION_RATE column. For all other conversion types you must enter a conversion date in the CURRENCY_CONVERSION_DATE column. In this case, the Journal Import process automatically calculates the amount in the functional currency.

· Populate fields ENTERED_CR or ENTERED_DR and ACCOUNTED_CR or ACCOUNTED_DR without specifying a conversion type and date. The Accounted amount must be your converted debit or credit amount.

Journal Source

This value is stored in field USER_JE_SOURCE_NAME and corresponds to the name of the journal source. Examples: Receivables, Payables, Inventory, etc.

Reference Fields

The reference fields are optional, but if they are defined, they are interpreted in the following way:

REFERENCE1	Batch name. Its structure is: <Reference1><Source><Request ID><Balance Type ><Group ID>
REFERENCE2	Batch description; if one is not defined, it will look like: Journal Import <Source><Request ID>
REFERENCE4	Journal entry name; with the format: <Category><Currency>< Conversion Type >< Conversion Rate>< Conversion Date ><Encumbrance Type ID><Budget Version>. Some of this information depends on the journal type.
REFERENCE5	Journal entry description; if one is not given, it will follow this format: Journal Import - Request ID
REFERENCE6	Reference or journal number. If it is not defined, it is automatically defined as Journal Import Created.
REFERENCE7	Reverse Journal Flag
REFERENCE8	Reverse Journal Period
REFERENCE10	Journal line description
REFERENCE21...30	The values and meanings for these fields depend on the Journal Source and Release. They store information used to identify and match the journal with the source document. The values for these fields map to REFERENCE_1 through REFERENCE_10 in the GL_JE_LINES table and the GL_IMPORT_REFERENCES table.

B. Journal Import

Journal import is a complex tool, which performs a lot of validation on thousands of records at a time. Journal import loads data from the subledgers into the GL_INTERFACE table and then into GL_JE_BATCHES, GL_JE_HEADERS and GL_JE_LINES tables.

Define the concurrent program controls.

The Concurrent Program Controls form can be used to improve the performance of the Journal Import, MassAllocation/MassBudgeting, and Open Period programs. The performance of Journal Import can be improved by increasing the number of journal lines it holds in memory.
Before R12 this setup was done in a specific form:

Form name: GLXSTCPC
Responsibility: General Ledger Super User
Navigation: Setup > System > Control

In R12 the setup uses the Profile Options instead:

· GL: Number of Accounts In Memory

· GL: Archive Journal Import Data

· GL: Number of Records to Process at Once

· GL Journal Import: Separate Journals by Accounting Date

Submit Journal Import Run

Once the data is in the GL_INTERFACE table, you can manually submit the import process from the Import Journals form.

Form name: GLXJIRUN
Responsibility: General Ledger Super User
Navigation: Journals > Import > Run

Currently it is also possible to submit the journal import from the Submit Concurrent Requests form.

Source: Select the Source from which you want Journal Import to create journal entries, such as Receivables, Payables, etc..
Note that in R12, the transactions are only in the interface temporarily during the journal import (the data stays in the XLA tables), therefore it is not possible to submit the journal import for subledger sources using subledger accounting. The Journal Import program is automatically launched by the transfer accounting program.

Group ID: If there is a value in the GROUP_ID field in the GL_INTERFACE table, either intentionally populated by the user, or by the subledger creating these transactions, then to import these transactions into General Ledger, the user must specify the Group ID on the Import Journals form.
A list of values option is provided on the Group ID field on the Import Journals form, which allows the users to pick a valid value (existing in the interface tables). General Ledger will then import data with the journal source and Group ID combination you specify.
If you do not specify a Group ID, General Ledger imports data from the specified journal entry source with no corresponding Group ID (null Group ID).
In this case it can happen that different batches in the interface with null Group ID get merged during the journal import. To avoid this we recommend to always use the Group ID.

You can choose your run options as well as whether to import descriptive flexfields at this time.

Journal Import Concurrent Program: GLLEZL

This concurrent process generates a request log file and also an execution report. The request log file shows valuable information for troubleshooting purposes, such as:

· Set of books or Ledger ID and name

· Chart of accounts ID

· Number of segments in the accounting flexfield and a description of each segment

· Functional currency

· Whether suspense accounting is enabled

The Journal Import Execution Report summarizes the results of the import and points out the lines that have errors.
It also contains a list in the end with the description of all the possible error codes (it doesn't mean that they have occurred).

C. Journal Import Correct

If the Journal Import run results in error, no records with the selected Source and Group_id will be imported. All of those rows will remain in the GL_INTERFACE table.
In R12, the rows comming from subledger accounting are rolled back to the XLA tables and removed from the interface, so they must be corrected at subledger level and then transferred again, to avoid reconciliation problems between GL and the subledgers.

You can use the Correct Journal Import Data form to correct the errors.

· The first screen gives you the option to find the journals with certain errors.

Form name: GLXJICOR
Responsibility: General Ledger Super User
Navigation: Journals > Import > Correct

Query on the errors you find in the Journal Import Execution Report to correct the data. You can only query journal import lines that have a status of Error or Corrected.

· This screen shows the actual journal with the errors.

Examples: EF04, EF02 etc.

Form name: GLXJICOR

Once corrected you can then click on the Import Journals button to rerun Journal Import.
The screen shows the different types of information in the journal you can choose from:

o Batches/Journals,

o Accounts,

o Journal Lines,

o Descriptive Flexfields

o References.

Depending on what the error is, select the part of the journal you need to go to in order to make your corrections.

D. Journal Import Delete

Use this ONLY if the data can be repopulated into the GL_INTERFACE table, such as from a spreadsheet or from an external source. The principle is the same as for journal correction: only the batches with status of error can be deleted.
In R12 this is not possible to delete subledger batches because the rows are rolled back to the XLA tables and removed from the interface.

Currently the Deletion of the journals is submitted as a Concurrent Request: Program - Delete Journal Import Data.

Older versions use a form to delete the batch.

Form name: GLXJIDEL
Responsibility: General Ledger Super User
Navigation: Journals > Import > Delete

Select the Source, Request ID and Group ID.

WARNING!!! After you Delete, you can not get the data back again.
If from a spreadsheet or another system (i.e. not a subledger) then it can be re-sent.
So be careful when you decide to do this!

Section 4: Journal Import Error Codes

Following is a sample of Journal Import validation errors (Per Journal Import Execution Report). This may not be complete as some new codes may be added as needed.

Period Error Codes

· EP01 This date is not in any open or future enterable period.

· EP02 This set of books does not have any open or future enterable periods.

· EP03 This date is not within any period in an open encumbrance year.

· EP04 This date is not a business day.

· EP05 There are no business days in this period.

Unbalanced Journal Error Codes

· WU01 Warning: This journal entry is unbalanced. It is accepted because suspense posting is allowed in this set of books.

· EU02 This journal entry is unbalanced and suspense posting is not allowed in this set of books.

· EU03 This encumbrance journal entry is unbalanced and the Reserve for Encumbrance account is not defined.

Flexfield Error Codes

· EF01 This Accounting Flexfield is inactive for this accounting date.

· EF02 Detail posting not allowed for this Accounting Flexfield.

· EF03 Disabled Accounting Flexfield.

· EF04 This is an invalid Accounting Flexfield. Check your cross-validation rules and segment values.

· EF05 There is no Accounting Flexfield with this Code Combination ID.

· EF06 The alternate account is invalid

· WF01 An alternate account was used instead of the original account

· WF02 A suspense account was used instead of the original account

Foreign Currency Error Codes

· EC01 A conversion rate must be entered when using the User conversion rate type.

· EC02 There is no conversion date supplied.

· EC03 A conversion rate type or an accounted amount must be supplied when entering foreign currency journal lines.

· EC04 There is no conversion rate entered for this conversion date.

· EC05 There is no conversion rate entered for this conversion rate type.

· EC06 There are no conversion rates for this currency.

· EC08 Invalid currency code.

· EC09 No currencies are enabled.

· EC10 Encumbrance journals cannot be created in a foreign currency.

· EC11 Invalid conversion rate type.

· EC12 The entered amount must equal the accounted amount in a functional or STAT currency journal line.

· EC13 The entered amount multiplied by the conversion rate must equal the accounted amount.

· ECW1 Warning: Converted amounts could not be validated because the conversion rate type is not specified.

Budget Error Codes

· EB01 A budget version is required for budget lines.

· EB02 Journals cannot be created for a frozen budget.

· EB03 The budget year is not open.

· EB04 This budget does not exist for this set of books.

· EB05 The encumbrance_type_id column must be null for budget journals.

· EB06 A period name is required for budget journals.

· EB07 This period name is not valid. Check calendar for valid periods.

· EB08 Average journals cannot be created for budgets.

Encumbrance Error Codes

· EE01 An encumbrance type is required for encumbrance lines.

· EE02 Invalid or disabled encumbrance type.

· EE03 Encumbrance journals cannot be created in the STAT currency.

· EE04 The budget_version_id column must be null for encumbrance lines.

· EE05 Average journals cannot be created for encumbrances.

Reversal Error Codes

· ER01 A reversal period name must be provided.

· ER02 This reversal period name is invalid. Check your calendar for valid periods.

· ER03 A reversal date must be provided

· ER04 This reversal date is not in a valid period.

· ER05 This reversal date is not in your database date format.

· ER06 Your reversal date must be the same as or after your effective date.

· ER07 This reversal date is not a business day.

· ER08 There are no business days in your reversal period.

Descriptive Flexfield Error Codes

· ED01 The context and attribute values do not form a valid descriptive flexfield for Journals - Journal Entry Lines.

· ED02 The context and attribute values do not form a valid descriptive flexfield for Journals - Captured Information.

· ED03 The context and attribute values do not form a valid descriptive flexfield for Value Added Tax.

Miscellaneous Error Codes

· EM01 Invalid journal entry category.

· EM02 There are no journal entry categories defined.

· EM03 Invalid set of books id.

· EM04 The value in the actual_flag must be "A" (actuals), "B" (budgets), or "E" (encumbrances).

· EM05 The encumbrance_type_id column must be null for actual journals.

· EM06 The budget_version_id column must be null for actual journals.

· EM07 A statistical amount belongs in the entered_dr(cr) column when entering a STAT currency journal line.

· EM09 There is no Transaction Code defined.

· EM10 Invalid Transaction Code.

· EM12 An Oracle error occurred when generating sequential numbering.

· EM13 The assigned sequence is inactive.

· EM14 There is a sequential numbering setup error resulting from a missing grant or synonym.

· EM17 Sequential numbering is always used and there is no assignment for this set of books and journal entry category.

· EM18 Manual document sequences cannot be used with Journal Import.

· EM19 Value Added Tax data is only valid in conjunction with actual journals.

· EM21 Budgetary Control must be enabled to import this batch.

· EM22 A conversion rate must be defined for this accounting date, your default conversion rate type, and your dual currency.

· EM23 There is no value entered for the Dual Currency Default Rate Type profile option.

· EM24 Average journals can only be imported into consolidation sets of books.

· EM25 Invalid average journal flag. Valid values are "Y", "N", and null.

Section 5: Troubleshooting Scripts

Following are some useful scripts to troubleshoot Journal Import errors.

· Estimate the number of lines pending to import from GL_INTERFACE:

q_ji1.sql :

/* Lists all pending journal import groups and the number*/
/* of records in each group */
set linesize 120
col Currency format a8
spool q_ji1
Select set_of_books_id Book
, user_je_source_name Source
, user_je_category_name Category
, currency_code Currency
, trunc (date_created) Created
, actual_flag Journal
, count(*)
from gl_interface
group by set_of_books_id
, user_je_source_name
, user_je_category_name
, currency_code
, trunc (date_created)
, actual_flag
order by set_of_books_id, trunc (date_created)
/
spool off

· Total the debit and credit amounts within a Group_ID:

q_ji2.sql :

/* Calculates the total amounts for a given group id */
set linesize 120
col s_en_cr format S999,999,999.99
col s_en_dr format S999,999,999.99
col s_acc_cr format S999,999,999.99
col s_acc_dr format S999,999,999.99
spool q_ji3
Select user_je_source_name Source
, User_je_category_name Category
, currency_code Currency
, sum (entered_cr) s_en_cr
, sum (entered_dr) s_en_dr
, sum (accounted_cr) s_acc_cr
, sum (accounted_dr) s_acc_dr
from gl_interface
where group_id = &group_id
group by user_je_source_name
, User_je_category_name
, currency_code
/
spool off

· The following query groups journal lines for a particular group_id coming from Oracle Payables by Category and Internal Invoice Id, and sums the debit and credit amounts (in 11i):

q_ji3.sql :

set linesize 120
col s_en_cr format S999,999,999.99
col s_en_dr format S999,999,999.99
col s_acc_cr format S999,999,999.99
col s_acc_dr format S999,999,999.99
col reference22 format a15
spool q_ji4
Select USER_JE_CATEGORY_NAME category
, reference22 invoice_id
, sum (entered_cr) s_en_cr
, sum (entered_dr) s_en_dr
, sum (accounted_cr) s_acc_cr
, sum (accounted_dr) s_acc_dr
, count(*)
from gl_interface
where user_jr_source_name ='Payables' and
group_id = &group_id
group by USER_JE_CATEGORY_NAME, reference22
;
spool off

Query q_ji3.sql can be modified to be used with the reference fields for other sources.

Oracle Secondary Ledger - Setup and Usage

Overview

The purpose of this document is to familiarize the users of Secondary Ledger (R12) with different levels of Secondary Ledger conversions feature. This document provides details on the Usage and Setup of Secondary Ledgers.

Secondary Ledger is an optional, additional ledger that is associated with the Primary Ledger.

A Secondary Ledger can be used to represent the Primary Ledger's data in another accounting representation, which differs from the primary in one or more of the following:

· Chart of accounts (COA)

· Accounting calendar/period type combination

· Currency

· Sub-ledger accounting method(SLA)

Introduction

In release 12, Secondary Ledgers can be created based on the legal requirements and are mainly used for creating Multiple Accounting Representations (MAR).

One may want to know the financial position of the company under different methods of Accounting, in which case we can create Secondary Ledgers and attach different Accounting methods to it.

For example, your Primary Ledger may be according to French Accounting principles and your Secondary Ledger may be according to US Accounting Principles. Using the concept of Secondary Ledgers, whenever any financial transaction occurs, it can be represented in both French Accounting and US Accounting Principles at the same time.

Difference between Reporting Currency and Secondary Ledger (MAR)

If the business requirement is to report every balance in a different currency then we can choose Reporting currency where as if we need to have different COA or SLA, Calendar Method then we can use Secondary Ledger.

Reporting currency can be used to capture the information in a reporting currency at Balance level, Sub-ledger level and Journal level.

Secondary Ledger could be used when you need to change the Chart of Accounts and Accounting convention method to meet the statutory and corporate requirements. This is used primarily where the organization need to report in different authorities like Local authorities, US GAAP,IFRS,IASB etc.

Reporting Currency Ledger and Secondary Ledger

Conversion Rules:

As Secondary Ledger can be used to represent the Primary Ledger's data in another accounting representation, the accounting representations depend on the following conversion rules:

· Chart of Accounts Conversion

· Calendar Conversion

· Currency Conversion

· Journal Conversion (Used by General Ledger Posting only)

Chart of Accounts Conversion:

The chart of accounts conversion rule is used when the chart of accounts are different between the Primary and the Secondary Ledger. In this scenario, a chart of accounts mapping is defined to provide instructions for mapping the accounts or the entire account segments from the Primary Ledger to the Secondary Ledger.

Calendar Conversion:

The calendar conversion rule is used when the Primary and Secondary Ledgers have different calendars. In this scenario, the journal effective date determines the corresponding non-adjusting period in the Secondary Ledger.

Currency Conversion:

The currency conversion rule is used when the Secondary Ledger currency is different from the Primary Ledger currency.

In this scenario, the system converts the data from the currency of the Primary Ledger to the currency of the Secondary Ledger using these conversion rules.

Journal Conversion (Used by GL Posting only):

Journal conversion is used to select the journals for transfer to the Secondary Ledger based on journal source and category combinations.

In this scenario, the GL Posting program uses these source and category conversion rules to determine which journals to automatically transfer to the Secondary Ledger during posting.

Data Conversion Levels:

Secondary Ledgers can be maintained at one of the following data conversion levels:

· Balance Level Secondary Ledgers

· Sub ledger Level Secondary Ledgers

· Journal Level Secondary Ledgers

· Adjustments Only Secondary Ledgers

Balance level Secondary Ledger:

The balance level Secondary Ledger only maintains the Primary Ledger balances in another accounting representation. To maintain balances in this type of Secondary Ledger, use General Ledger Consolidation to transfer the Primary Ledger balances to the Secondary Ledger.

If the balance level Secondary Ledger uses a different currency from the Primary Ledger, system automatically generates and assigns a balance level reporting currency ledger to the Primary Ledger.

General Ledger Consolidation is used to transfer balances from the Primary Ledger's balance level reporting currency to the balance level Secondary Ledger. This balance level reporting currency uses the same currency as the Secondary Ledger and represents the translated balances of the Primary Ledger.

Scenario: 1-Adding and Using a Balance Level Secondary Ledger

The balance level Secondary Ledger discussed in the below scenario uses a different currency and chart of accounts as shown below.

	*Primary Ledger*	*Secondary Ledger*
*Chart of Accounts*	SOracle_Coa (5 Segments)	SOracle_Secondary_Coa (3 Segments)
*Currency*	INR	USD
*Calendar*	Soracle_Cal	Soracle_Cal
*Sub-ledger Accounting Method*	Encumbrance Accrual	Optional (not used in this case)

Step 1:

Navigation: General Ledger responsibility: Setup: Financials: Accounting Setup Manager: Accounting Setups: Query the Primary Ledger. Select Add Secondary Ledger button.

Step 2:

Enter the name of the Secondary Ledger, chart of accounts, currency, and calendar and the data conversion level as shown below. Select Apply.

Please make a note of the information that system generated reporting currency has been assigned to your Primary Ledger as the Secondary Ledger currency is different. Select Apply.

Step 3:

Complete the ledger processing options for the Secondary Ledger

Now select the update icon for primary to Secondary Ledger mapping

Step 4:

Select Create mapping button and create the mapping in the applications. Note that the rate types are not populated

Create a Chart of accounts mapping using the steps below.

· Give the name and description.

· Enter the target as Secondary Ledger and source as Primary Ledger.

· Select segment rules button and enter the segment rules for the system to transfer the transactions/balances from the Primary Ledger to Secondary Ledger.

Assign the chart of accounts mapping that was created in the previous step

This has to be assigned in the page Update primary to Secondary Ledger Mapping

Step 5:

Now select the update icon for Reporting Currencies under the Setup step of the Primary Ledger

Observe that the reporting currency USD is already added by the system to the Primary Ledger

Enter the required information for reporting currency and complete the setup

Now check the Primary to Secondary Ledger mapping, the period rates are populated.

Step 6:

Navigation: Primary Ledger Responsibility: Journals: Enter

Place a manual journal and post the same.

Step 7:

Navigation: Currencies: Translation

Run the translation program from INR to USD

Check the translated trial balance.

Please note the lines in the above report as the same would be transferred to the Secondary Ledger after consolidation.

Step 8:

Navigation: Consolidation: Define: Consolidation

Define consolidation.

In the consolidation attributes, the parent is the Secondary Ledger and the subsidiary is the Primary Ledger.

Once the consolidation is defined, select transfer button.

Ensure Consolidation Transfer Program completes normal

Step 9:

Navigation: Secondary Ledger responsibility : Journals : Enter

Check the journal that has got generated and post the same

Only the first three segments of the Primary Ledger are mapped with the Secondary Ledger. That is why the account in the journal has only three segments.

Journal Level Secondary Ledgers

The journal level Secondary Ledger is an additional accounting representation of the Primary Ledger journal entries and balances.

This type of Secondary Ledger is maintained using the General Ledger Posting Program. Every time you post a journal in the Primary Ledger, the same journal is automatically replicated and maintained in the Secondary Ledger, depending on the journal conversion rules specified for the Secondary Ledger.

Scenario: 2-Adding and Using a Journal Level Secondary Ledger

The journal level Secondary Ledger in the below scenario uses currency as USD, which is different from the Primary Ledger currency.

	*Primary Ledger*	*Secondary Ledger*
*Chart of Accounts*	SOracle_Coa	SOracle_Coa
*Currency*	INR	USD
*Calendar*	SOracle_Cal	SOracle_Cal
*Sub-ledger Accounting Method*	Encumbrance Accrual	Optional (not used in this case)

Step1:

Navigation: General Ledger Super user: Setup: Financials: Accounting Setup Manager: Accounting Setups: Query Ledger: Update Accounting setup: Add Secondary Ledger

Step2:

Complete the Ledger Setups for the Journal Level Secondary Ledger

Step 3:

Primary to Secondary Ledger mapping – Select Create mapping button and create chart of accounts mapping

Step 4:

Define chart of accounts mapping: As per the mapping, all Primary Ledger balancing segment values would be transferred into single balancing segment value 1000 in Secondary Ledger.

Step 5:

See the Journal conversion rules defined as below.

Journal conversion rules:

These rules can be used to specify if the journals have to get posted automatically in the Secondary Ledger and if the journal creator has to be retained.

The Journal source and category conversion can be used to restrict the transactions of few sources and categories from getting transferred to the Secondary Ledger.

Step 6:

Define conversion rates between primary to Secondary Ledger

Navigation: Setup: Currencies: Rates: Daily

Step 7:

Enter and post a manual journal in Primary Ledger

Navigation: Journal : Enter

Note: We have entered 1000 & 2000 as BSV in Primary Ledger and we have mapped to single value 1000 in Secondary Ledger.

Posting the below journal replicates the journal in Secondary Ledger.

Step 8:

Check the Secondary Ledger for the journal created.

Please note the BSV is 1000 for all the lines based on the chart of accounts mapping.

Sub-ledger Level Secondary Ledgers

The sub ledger level Secondary Ledger maintains an additional accounting representation of the sub-ledger journals, journal entries, and balances.

The sub ledger level Secondary Ledger is maintained using both Sub-ledger accounting and the General Ledger Posting program.

Scenario: 3-Adding and Using a Sub ledger Level Secondary Ledger

In the below scenario, sub-ledger level Secondary Ledger uses a different currency and sub-ledger accounting method as shown below.

	*Primary Ledger*	*Secondary Ledger*
*Chart of Accounts*	SOracle_Coa	SOracle_Coa
*Currency*	INR	USD
*Calendar*	SOracle_Cal	SOracle_Cal
*Sub-ledger Accounting Method*	Encumbrance Accrual	Standard Accrual

Step 1:

Navigation: General Ledger Super user: Setup: Financials: Accounting Setup Manager: Accounting Setups

Query Ledger: Update Accounting setup: Add Secondary Ledger

Step 2:

Complete the Ledger processing options for the Secondary Ledger

Primary to Secondary Ledger mapping:

Step 3:

Define the journal conversion rules as below.

For sub-ledger level Secondary Ledgers, the journal sources that use Sub-ledger Accounting must be set to No in the Transfer Journals to this Secondary Ledger field.

Any journal source that uses Sub-ledger Accounting to generate its accounting entries must not be converted to the Secondary Ledger using General Ledger Posting because Sub-ledger Accounting automatically performs the conversion for these sub-ledger journals.

If the option is set to yes, duplicate journals will be created in the Secondary Ledger.

Step 4:

Navigation: AP responsibility : Invoices : Entry : Invoices to create an invoice.

Perform create accounting for the invoice created

Step 5:

Note there are two journal import requests triggered, one for primary and other one for Secondary Ledger

Posting requests for Primary and Secondary Ledgers

Step 6:

Journal created in Primary Ledger

Journal created in Secondary Ledger

Adjustments Only Secondary Ledgers

The adjustments only Secondary Ledger is an incomplete accounting representation that only holds manual adjustments created from general ledger.

Adjustments only Secondary Ledgers must share the same chart of accounts, accounting calendar/period type combination, and currency as the associated Primary Ledger.

Automated adjustments from sub-ledger accounting are not possible.

Scenario: 3-Adding and Using a Adjustments Only Secondary Ledger

The adjustments only Secondary Ledger shares the same chart of accounts, calendar and currency as that of the Primary Ledger as shown below.

	*Primary Ledger*	*Secondary Ledger*
*Chart of Accounts*	SOracle_Coa	SOracle_Coa
*Currency*	INR	INR
*Calendar*	SOracle_Cal	SOracle_Cal
*Sub-ledger Accounting Method*	Encumbrance Accrual	Not used in this case

Step 1:

Navigation: General Ledger Super user: Setup: Financials: Accounting Setup Manager: Accounting Setups: Query Ledger: Update Accounting setup: Add Secondary Ledger

Step 2:

Complete the ledger processing options for the Adjustment only Secondary ledger

Step 3:

Create a ledger set to check the combined balances

Navigation: Setup: Financials: Ledger Set

Navigation: Inquiry: Account

If user wants to reduce 100 from 190, the rectification entry can be passed in the Adjustments only Secondary Ledger

Step 4:

Create the rectification / adjustment entry in the adjustment only secondary ledger and post the journal

Navigation: Journals: Enter

Step 5:

Define and run a FSG report for the ledger set created to fetch combined balances.

Navigation: Reports: Define: Report

Step 6:

FSG output

Note the balance is reduced from 190 to 90 because of the entry posted in the adjustments only Secondary Ledger

Upgrade of Historical Data for Secondary Ledger

A new concurrent program:”SLA Secondary/ALC Ledger Historic Upgrade” is introduced to create the accounting for the Secondary Ledger for the historic transactions in SLA based on the corresponding Primary Ledger. This is based on the principle that the balances of Secondary Ledger are initialized based on Primary Ledger’s balances.

Following are the list of the validations performed in the concurrent program before any data can be processed:

1) The relation between Secondary Ledgers with the Primary Ledger should be at sub-ledger level

2) In the case of historical upgrade of Secondary Ledger, the application accounting definition of secondary and Primary Ledgers should be same.

3) All the final accounted journals in SLA for the Primary Ledger should be transferred to GL.

4) If the earlier run of the program is incomplete / failed, then the present run should be in Recovery mode, with the same parameters of the prior run.

5) For the conversion information provided, currency conversion rates between Primary Ledger’s entered / accounted currency to Secondary Ledger’s functional currency should be defined.

6) If the Secondary Ledger is of a different chart of accounts, the chart of account mappings should be defined.

Frequently Asked Questions (FAQ):

1. How to Disable a Secondary Ledger Already Created?

Once you disable the conversion of Secondary Ledgers, it immediately prevents the propagation of journals from the Primary Ledger to the Secondary Ledger.

The disabled Secondary Ledger is still available for historical reporting and manual journal entries.

Note: Balance level Secondary Ledgers cannot be disabled. To stop transferring balances from the source representation (Primary Ledger or balance level reporting currency) to the balances level Secondary Ledger, stop running consolidations.

Note: Adjustments only Secondary Ledgers cannot be disabled because journals are not automatically transferred to this Secondary Ledger.

To disable the conversion of Secondary Ledgers:

• Navigate to the Accounting Options page.

• In the Secondary Ledgers region, select the Disable Conversion icon for the

Secondary Ledger to be disabled.

Note: Once the conversion of a Secondary Ledger is disabled, the status of the Secondary Ledger is changed to Disabled.

2. Even after a Secondary Ledger is disabled, it still appears in the Accounting Setup Manager (ASM), in a disabled status. Is it possible to remove this from appearing from ASM at all?

No, it is not possible to completely stop this assignment from appearing in Accounting Setup Manager.

3. Can a Secondary Ledger be associated with more than one Primary Ledger?

No, a Secondary Ledger can only be associated with a single Primary Ledger.

4. Can Primary Ledger Be Changed To Secondary and Secondary To Primary After Few Years Of Operations?

Changing ledger types is not recommended.

5. How to delete a Secondary Ledger?

You can only delete Secondary Ledgers before the accounting setup is complete. After the accounting setup is complete, you can disable the conversion of Secondary Ledgers. This prevents any journals that are entered in the Primary Ledger or source representation from being transferred to the Secondary Ledger.

Deleting a Secondary Ledger removes the ledger and all of its setup steps.

To delete Secondary Ledgers:

• Navigate to the Accounting Options page.

• Find the Secondary Ledger to delete and select the Remove Secondary Ledger icon.

6. Can we map a non balancing segment in the primary ledger to a balancing segment in the secondary ledger?

You cannot map a non balancing segment in the primary ledger to a balancing segment in the secondary ledger. If you attempt to do this, the posting program is likely to fail.

7. The posting of a Reversal Journal in Primary Ledger does not create the corresponding Reversal in the Secondary Ledger. Why?

Enhancement Request/Bug 5686912 GL POSTING DIDN’T CREATE a REVERSED MANUAL JOURNAL IN SECONDARY LEGER was logged and it is implemented now.

Issue is fixed in the following files.

For R12.0: forms/US/GLXJEENT.fmb 120.81.12000000.36

For R12.1: forms/US/GLXJEENT.fmb 120.83.12010000.22

8. What is the Naming Convention of R12 Secondary Ledger Journal Batches?

When the posting runs it creates secondary ledger journals and while creating journal batches from primary ledger journal batches it follows below naming convention.

Secondary Ledger batch name = Secondary Ledger Short Name || Primary Ledger Journal Batch name || Primary ledger Journal Batch Id.

9. How to Transfer Historical Balances to a New Journal Level Secondary Ledger?

There is no program provided by Oracle to upgrade historical data for Journal Level Secondary and Reporting ledgers.

The "SLA Secondary/ALC Ledger Historic Upgrade" concurrent program is not for Journal level secondary ledgers. For Journal level secondary ledgers, this program should not be run.

The Secondary ledger balances in General Ledger need to be initialized.

Use the General Ledger Consolidation functionality to initialize the balances in GL for the Journal level secondary ledger.

10. What are the differences in behavior, between a Primary Ledger and a Secondary Ledger?

· Journal Reconciliation will have to be done separately for Secondary Ledger.

· Revaluation has to be done manually for Secondary Ledger.

· You can only enable average balances or average balance consolidation for your secondary ledger if average balances or average balance consolidation is also enabled for its primary ledger.

· By default, journals that use the following journal sources are

not transferred to the journal level secondary ledger:

* Move/Merge

* Move/Merge Reversal

* Revaluation

11. Is it possible to create automated adjustments using Sub ledger accounting?

It is not possible to create automated adjustments using Sub ledger Accounting. An internal bug has been raised with development for correcting this in the documentation.

Technical Details:

Tables that can be used to get the Secondary Ledger details:

· GL_LEDGER_CONFIG_DETAILS and GL_LEDGER_RELATIONSHIPS - To find the Secondary Ledgers associated with a Primary Ledger

· GL_JE_INCLUSION_RULES - To find the source and category conversion levels

· GL_DAILY_RATES - To find the source and category conversion levels

· GL_CONS_SEGMENT_MAP - To find the mapping rules

Journal Import Conversion– General Ledger

Introduction

Functional Requirement

Solution Designed

Implementation of Solution

Setups and Prerequisites

Database Object Designed

Data Loading

Validation

API’s Used

Error Encountered

Pseudo Code

Q & A

Introduction

This document defines the technical design and specifications required for Journal Conversion to convert journals from client Legacy systems to the Oracle R12.

The data will be given as extracted data file to Oracle conversion program. Conversion program will pick the data, validate them and convert into Oracle.

Functional Requirement

The extract will contain the entire necessary GL Journal data elements required to successfully create Journals in General Ledger. Cleansed Data will be provided as a flat CSV file which will be loaded into custom interface table. Cleansing is not in scope of this conversion program. In case any derivation rules fail the validation step, where ever possible a default value will be used. Required application setups must be completed before running the standard General Ledger Documents Open Interface program.

The scope of the conversion is to load data from the data files to the staging tables, validate the data , load the data into Interface tables and then call Standard GL Import Program to Load data into GL tables. Source System for data load will be AS400 or R11i only and all the other criteria will be as specified in CV40 Document. Posting of Journals need to be performed manually only.

Solution Designed

The solution for this conversion requirement can be broken into two parts, the loading of data into the temporary staging table and the processing and translation of that data before it is passed to interface table to populate standard Oracle base table using the open interface program.

Data supplied in coma delimited ASCII flat file and the format will be as defined in the CV040.

The first step is the data file is loaded into the staging table using the load program. Once data file has been processed, any SQL Loader log files, discard files, and bad files should be concatenated together and copied to the appropriate directory and file where the user may review them. After each run, the load program should accomplish any necessary cleanup.

The second step is the data validation program. This PL/SQL based program should be able to select records from staging table and process those records before processing them using open interface. The incoming data file will be written to a staging table by the load program. The validation program will select these records and process them accordingly. The validation program will have to maintain counts for total records processed, total records with errors, total errors and total records with zero value. The program should also identify any records with errors on the execution report.

The validation program should be run from within Oracle Applications. This program will need an execution record, a defined concurrent program and be attached to a request group.

Once the data in staging table is validated for all records without errors then valid data is inserted into standard open interface table.

In case any errors then errors needs to be corrected in coma delimited ASCII flat file and again processing needs to be started from first step.

The Third step is Standard Journal Import program will be submitted. This program will process the data in the interface table. And put them into standard GL tables.

Implementation of Solution

Conversion Assumptions

1. This document is prepared based on the approved functional design document (CV040 version 1.1) and the scope of the solution is limited to the features as mentioned explicitly in this document.

2. The data file will be in the format specified in CV040 doc irrespective of the data source.

3. There will be one comma (",") delimited file for this conversion.

4. All system level profile options have been setup.

5. Naming standards are as per build standards document.

6. All the required setups for this conversion will be completed before the program is run.

7. Client custom Top ($XXXX_TOP) will be created in Unix server to follow Oracle OnDemand development standards.

8. No changes are done to the data file without prior notification to the Oracle technical team after they are transferred to the required directory for upload.

9. The SQL*Loader will upload the records in the truncate mode, which means all the previous records present in the staging table will be truncated, and the staging table will be loaded with new records.

10. Bad, Discard and Log files are created in the $ XXXX_TOP/bin directory. Any file present in the “$XXXX_TOP/bin” with these names will be overwritten with bad, log and discard file generated by the SQL*Loader.

11. All database objects like packages, procedure, functions, views, tables, etc will be created in the XXXX_CUST schema.

12. The concurrent manager must be running in the applications before we can run the Import program.

13. The data will be batched in Ledgers and imported. No other batching will be applied on the data programatically. Depending on the various parameters GL import may put the records into different Batches – Ex : Period.

14. Posting will have to be run manually after import.

15. Zero balance accounts for a particular period will not be converted.

16. Cleansing of data is not in Scope of this object.

17. If a batch fails due to Accounting errors, correction of those journals will be handled through the Standard Correct Forms.

18. The Journals will have to be balanced per period. It’s up to the extraction team to take care that the journals are balanced.

19. No custom report will be developed for reconciling the data.

20. Only Functional Currency Journals will be converted for all the Ledgers.

21. Creation of Segment Values for individual segments is not in Scope of this Object.

22. Creation of Code Combination Id is subject to the fact that the relevant Segment Values Exits, No Rules Fail for the given Combination and Dynamic Insertion is on.

23. Chart of account Mapping between source and Oracle will be provided as cross reference table for which Separate CV40/CV60 will be defined. In GL Import Conversion we will have function to drive value based on mapping provided in this table. It is assumed that the Segment values given in the Extract File is already modified to suite Oracle Value. We can directly pass these values to the gl_interface for creation of Code Combinations.

24. If the record fails validation at the Standard Import level, the data in the staging table will be marked as Processed, since the correction and import of the same will be done using the Standard Correct Forms.

a. In case the User wants to correct the data and reload.

· Data should be Updated / Deleted from the Custom Staging Table.

· Data pertaining to that batch should be deleted form the gl_interface table.

· Then the entire process should be re-run.

Setups and Prerequisites

The following are the mandatory setups and prerequisites that need to be setup before running this conversion program:

1) Financial Options

2) Ledger Setups.

3) Segment Values for Code Combination Creation

4) Periods to be Setup and kept in Open Status

5) Mapping table to store AS400, 11i & R12 Segment Value Mappings.

Database Object Designed

The following tables are going to be populated in the Oracle application for the conversion of GL Journals Conversion:

Table Name	Select	Insert	Update	Delete
XXXX_GL_DATA_STG	X	X	X	X
XXXX_GL_COA_XREF	X
GL_INTERFACE		X
GL_JE_HEADERS		X
GL_JE_LINES		X
GL_JE_BATCHES		X
GL_CODE_COMBINATIONS	X	X
GL_PERIOD_STATUSES	X
GL_LEDGERS	X

Staging Table 1: XXXX_GL_BALANCE_STG

CREATE TABLE XXXX_GL_BALANCE_STG

(

LEDGER VARCHAR2(30)

,CURRENT_YEAR VARCHAR2(4)

,SYS_CURRENCY_CODE VARCHAR2(3)

,GL_DR_BALANCE_00 NUMBER

,ACTUAL_FLAG VARCHAR2(1)

,GL_DR_BALANCE_01 NUMBER

,GL_CR_BALANCE_01 NUMBER

,GL_DR_BALANCE_02 NUMBER

,GL_CR_BALANCE_02 NUMBER

,GL_DR_BALANCE_03 NUMBER

,GL_CR_BALANCE_03 NUMBER

,GL_DR_BALANCE_04 NUMBER

,GL_CR_BALANCE_04 NUMBER

,GL_DR_BALANCE_05 NUMBER

,GL_CR_BALANCE_05 NUMBER

,GL_DR_BALANCE_06 NUMBER

,GL_CR_BALANCE_06 NUMBER

,GL_DR_BALANCE_07 NUMBER

,GL_CR_BALANCE_07 NUMBER

,GL_DR_BALANCE_08 NUMBER

,GL_CR_BALANCE_08 NUMBER

,GL_DR_BALANCE_09 NUMBER

,GL_CR_BALANCE_09 NUMBER

,GL_DR_BALANCE_10 NUMBER

,GL_CR_BALANCE_10 NUMBER

,GL_DR_BALANCE_11 NUMBER

,GL_CR_BALANCE_11 NUMBER

,GL_DR_BALANCE_12 NUMBER

,GL_CR_BALANCE_12 NUMBER

,GL_DR_BALANCE_13 NUMBER

,GL_CR_BALANCE_13 NUMBER

,ACCOUNTED_DR NUMBER

,ACCOUNTED_CR NUMBER

,SEGMENT1 VARCHAR2(30)

,SEGMENT2 VARCHAR2(30)

,SEGMENT3 VARCHAR2(30)

,SEGMENT4 VARCHAR2(30)

,SEGMENT5 VARCHAR2(30)

,SEGMENT6 VARCHAR2(30)

,SEGMENT7 VARCHAR2(30)

,SEGMENT8 VARCHAR2(30)

,CODE_COMBINATION_ID NUMBER

,PROCESS_FLAG VARCHAR2(10)

,LAST_UPDATE_DATE DATE

,LAST_UPDATED_BY NUMBER

,CREATED_BY NUMBER

,CREATION_DATE DATE

,CREATED_BY_MODULE VARCHAR2(100)

,USER_JE_SOURCE_NAME VARCHAR2(100)

,USER_JE_CATEGORY_NAME VARCHAR2(100)

)

Data Loading

This section describes the logic for the conversion upload programs that will be built to support the conversion of GL Actual and Budget Balance Conversion.

1) There is a host concurrent program which takes data file path with its names as parameters and calls the SQL*Loader utility to load the data from the delimited file into the staging tables. The Staging tables are as follows:

XXXX_GL_BALANCE_STG

The following is the control file (CTL) script, which will upload the data from the flat file to the staging table (XXXX_GL_BALANCE_STG):

LOAD DATA

TRUNCATE

INTO TABLE XXXX_GL_BALANCE_STG

FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(

LEDGER CHAR "TRIM (:LEDGER)"

,CURRENT_YEAR CHAR "TRIM (:CURRENT_YEAR)"

,SYS_CURRENCY_CODE CHAR "TRIM (:SYS_CURRENCY_CODE)"

,GL_DR_BALANCE_00 CHAR "TRIM (:GL_DR_BALANCE_01)"

,GL_CR_BALANCE_00 CHAR "TRIM (:GL_CR_BALANCE_01

,GL_DR_BALANCE_01 CHAR "TRIM (:GL_DR_BALANCE_01)"

,GL_CR_BALANCE_01 CHAR "TRIM (:GL_CR_BALANCE_01)"

,GL_DR_BALANCE_02 CHAR "TRIM (:GL_DR_BALANCE_02)"

,GL_CR_BALANCE_02 CHAR "TRIM (:GL_CR_BALANCE_02)"

,GL_DR_BALANCE_03 CHAR "TRIM (:GL_DR_BALANCE_03)"

,GL_CR_BALANCE_03 CHAR "TRIM (:GL_CR_BALANCE_03)"

,GL_DR_BALANCE_04 CHAR "TRIM (:GL_DR_BALANCE_04)"

,GL_CR_BALANCE_04 CHAR "TRIM (:GL_CR_BALANCE_04)"

,GL_DR_BALANCE_05 CHAR "TRIM (:GL_DR_BALANCE_05)"

,GL_CR_BALANCE_05 CHAR "TRIM (:GL_CR_BALANCE_05)"

,GL_DR_BALANCE_06 CHAR "TRIM (:GL_DR_BALANCE_06)"

,GL_CR_BALANCE_06 CHAR "TRIM (:GL_CR_BALANCE_06)"

,GL_DR_BALANCE_07 CHAR "TRIM (:GL_DR_BALANCE_07)"

,GL_CR_BALANCE_07 CHAR "TRIM (:GL_CR_BALANCE_07)"

,GL_DR_BALANCE_08 CHAR "TRIM (:GL_DR_BALANCE_08)"

,GL_CR_BALANCE_08 CHAR "TRIM (:GL_CR_BALANCE_08)"

,GL_DR_BALANCE_09 CHAR "TRIM (:GL_DR_BALANCE_09)"

,GL_CR_BALANCE_09 CHAR "TRIM (:GL_CR_BALANCE_09)"

,GL_DR_BALANCE_10 CHAR "TRIM (:GL_DR_BALANCE_10)"

,GL_CR_BALANCE_10 CHAR "TRIM (:GL_CR_BALANCE_10)"

,GL_DR_BALANCE_11 CHAR "TRIM (:GL_DR_BALANCE_11)"

,GL_CR_BALANCE_11 CHAR "TRIM (:GL_CR_BALANCE_11)"

,GL_DR_BALANCE_12 CHAR "TRIM (:GL_DR_BALANCE_12)"

,GL_CR_BALANCE_12 CHAR "TRIM (:GL_CR_BALANCE_12)"

,GL_DR_BALANCE_13 CHAR "TRIM (:GL_DR_BALANCE_13)"

,GL_CR_BALANCE_13 CHAR "TRIM (:GL_CR_BALANCE_13)"

,ACCOUNTED_DR CHAR "TRIM (:ACCOUNTED_DR)"

,ACCOUNTED_CR CHAR "TRIM (:ACCOUNTED_CR)"

,SEGMENT1 CHAR "TRIM (:SEGMENT1)"

,SEGMENT2 CHAR "TRIM (:SEGMENT2)"

,SEGMENT3 CHAR "TRIM (:SEGMENT3)"

,SEGMENT4 CHAR "TRIM (:SEGMENT4)"

,SEGMENT5 CHAR "TRIM (:SEGMENT5)"

,SEGMENT6 CHAR "TRIM (:SEGMENT6)"

,SEGMENT7 CHAR "TRIM (:SEGMENT7)"

,SEGMENT8 CHAR "TRIM (:SEGMENT8)"

,LAST_UPDATE_DATE “SYSDATE”

,LAST_UPDATED_BY “FND_GLOBAL.USER_ID”

,CREATION_DATE “SYSDATE”

,CREATED_BY “FND_GLOBAL.USER_ID”

,CREATED_BY_MODULE CONSTANT ‘CONVERSION’

,PROCESS_FLAG CONSTANT 'NEW'

,ACTUAL_FLAG CONSTANT 'A' / CONSTANT ‘B'

)

2) Validation of data is done in the staging table. The Staging table is as follows:

XXXX_GL_BALANCE_STG

3) If a record fails during data validation then errors are inserted into the error table (XXXX_ERROR_DETAILS) and for the eroded out record the process_flag column in the staging tables will be updated to ‘ERROR’. Error reporting will take care of incorrect dates format, missing mandatory fields etc.

4) After validations are complete, the procedure is called to load the valid data into Oracle interface table for record that has been successfully validated without any errors. Oracle standard General Ledger open interface will be used to create records in Oracle base tables.

5) All the loaded data in interface tables will be marked as ‘PROCESSED’ (process_flag = ‘PROCESSED’) in the staging tables. Records with process_flag= ‘ERROR’ will not be processed in the interface tables.

Validation

This section describes the logic for the conversion interface/validation programs that will be built to support the conversion of GL Actual and Budget Balance Conversion.

Describes the program logic for the interface/validation program(s) used to convert the business object.

The conversion of Journals is initiated through a main program, which will in turn call rest of the programs depending on success.

XXXX: GL Actual Balance Conversion Main

XXXX: GL Actual Balance Conversion Main is the wrapper program, which will call the programs below.

· XXXX: GL Balance Conversion Staging Load

· XXXX: GL Balance Conversion Validate

· XXXX GL Balance Conversion Convert

Following are the various sub-procedures under “XXXX: GL Actual Balance Conversion Main” concurrent program:

§ MAIN_ACTUAL_P

This is the main procedure that starts the conversion processing. This procedure in turns calls other programs using FND_REQUEST.SUBMIT_REQUEST to load data into staging tables, validate staging table records and process them using open interface.

XXXX: GL Budget Balance Conversion Main

XXXX: GL Budget Balance Conversion Main is the wrapper program, which will call the programs below.

· XXXX: GL Balance Conversion Staging Load

· XXXX: GL Balance Conversion Validate

· XXXX GL Balance Conversion Convert

Following are the various sub-procedures under “XXXX: GL Budget Balance Conversion Main” concurrent program:

§ MAIN_BUDGET_P

XXXX: GL Balance Conversion Staging Load

The Host Program used to load the data into the staging table

XXXX_GL_BALANCE_CONV_LOAD

XXXX: GL Balance Conversion Validate

The PL/SQL program is used to validate the data in staging table and pass the necessary column values to GL Interface table to create Budget & Actual Journals.

Following are the various sub-procedures under “XXXX: GL Balance Conversion Validate” concurrent program:

§ VALIDATE_MAIN_P

This is the startup procedure for this concurrent program. This procedure in turns calls other procedures to validate the staging table records and also write the error information to the log file.

§ VALIDATE_RECORDS_P

In this procedure, validation of the staging table data will be done. If the record successfully passes through the validation process, process_flag is set to “VALIDATED”.

XXXX: GL Balance Conversion Convert

The PL/SQL program is used to process the data in the GL Interface table. This will call the Standard Journal Import to process the data.

Following are the various sub-procedures under “XXXX: GL Balance Conversion Convert” concurrent program:

§ PROCESS_MAIN_P

This is the startup procedure for this concurrent program. This procedure in turns calls other procedures to process the valid staging table records through GL open interface.

§ INSERT_IFACE_P

This procedure takes all validated records and inserts them into interface table GL_INTERFACE.

§ SUBMIT_IFACE_REQUEST_P

This procedure submits a request to Journal Import using FND_REQUEST.SUBMIT_REQUEST.

API’s Used

N/A

Error Encountered

Error Handling Program Logic

This section describes the logic for the error handling procedure that will be built to support the conversion of GL Actual and Budget Balance Conversion.

The following are the procedures that will be used in handling of errors.

XXXX_INSERT_ERROR procedure is used to insert all the error messages in to the error table.

XXXX_PRINT_LOG_ERRORS procedure is to print the records in the log file, based on the request id.

In case of any setup validation error, assign retcode: = 2. This is done so that the program completes in error.

The data in the staging table are not deleted so that error records can be identified if required. The SQL*Loader will delete data from the staging table before loading data to prevent storing of duplicate data in staging table.

Note: The program should complete all data & apps setup related validations (mentioned in validation section) before giving a consolidated error report. Program should list all data/setup errors before stopping rather than stopping when first validation /setup error occurs. In case of system errors, program should give the error message and stop execution (ret code=2).

Pseudo Code

MAIN_ACTUAL_P

Initialize the Error Handlers to capture the run-time errors.

If data file parameter is given, call load program “XXXX_GL_BALANCE_CONV_LOAD” using fnd_request.submit_request, otherwise go to next step.

Update the Actual Flag for all the records loaded to ‘A’. Update the Source Category also

1. Call validate program “XXXX_GL_BALANCE_CONV_VALIDATE” using fnd_request.submit_request. Pass Balance_Type parameter as ‘A’

2. Call process program “XXXX_GL_BALANCE_CONV_CONVERT” using fnd_request.submit_request. Pass Balance_Type parameter as ‘A’

MAIN_BUDGET_P

3. Initialize the Error Handlers to capture the run-time errors.

If data file parameter is given, call load program “XXXX_GL_BALANCE_CONV_LOAD” using fnd_request.submit_request, otherwise go to next step.

Update the Actual Flag for all the records loaded to ‘B’. Update the Source & Category also

4. Call validate program “XXXX_GL_BALANCE_CONV_VALIDATE” using fnd_request.submit_request. Pass Balance_Type parameter as ‘B’

5. Call process program “XXXX_GL_BALANCE_CONV_CONVERT” using fnd_request.submit_request. Pass Balance_Type parameter as ‘B’

VALIDATE_MAIN_P

Declare

Declare all local variables

CURSOR c_gl_balance IS

SELECT distinct current_year FROM XXXX_GL_BALANCE_STG

WHERE process_flag in (‘NEW’, ‘ERROR’)

AND Actual_flag = <Balance_Type Parameter>;

TYPE Table t_accounting_date index by Binary Integer;

gv_accounting_date t_accounting_date; - Let this table be a global table accessible to all procedure of the package.

BEGIN

Initialize the error flag.

Derive the Source, Category , Ledger & Budget Version Id (If balance type is ‘B’).

FOR c_gl_balance_rec IN c_gl_balance

LOOP

Lv_period : =0;

Loop

Lv_period := l_period+1;

Lv_period_count := c_gl_balance_rec|| lpad(Lv_period,2,0)

gv_accounting_date(Lv_period_count) := DR2,Pass Lv_period

End Loop;

END;

Move all records into VALIDATED Status.

PROCESS_MAIN_P

This procedure will insert valid records into interface table and calls Journal Import.

Declare all local variables

Ø CURSOR c_gl_balance IS

SELECT * FROM XXXX_GL_BALANCE_STG

WHERE process_flag = ‘VALIDATED’

Ln_set_process_id := 1;

Ln_record_count := 0;

Loop for cursor c_gl_balance.

Ln_record_count := ln_record_count + 1;

For each Journal record, populate the interface table GL_INTERFACE with values from necessary c_gl_balance columns.

For period_count in 1..13

IF (Current_year = 2006 AND period_count = 1) AND

(NVL(GL_DR_BALANCE_00,0)<>0 OR NVL(GL_CR_BALANCE_00,0) <> 0)

So while inserting into gl_interface the accounting_date would be

gv_accounting_date(c_gl_balance.current_year||lpad(period_count,2,0))

Perform the default rules while inserting the record.

END IF

IF period_count = 1 AND (If NVL(GL_DR_BALANCE_01,0) <> 0 OR NVL(GL_CR_BALANCE_01,0) <> 0) THEN

So while inserting into gl_interface the accounting_date would be

gv_accounting_date(c_gl_balance .current_year||lpad(period_count,2,0))

Perform the default rules while inserting the record.

End If;.

IF period_count = 13 AND (If NVL(GL_DR_BALANCE_13,0) <> 0 OR NVL(GL_CR_BALANCE_13,0) <> 0) THEN

So while inserting into gl_interface the accounting_date would be

gv_accounting_date(c_gl_balance .current_year||lpad(period_count,2,0))

Perform the default rules while inserting the record.

End If;.

End loop;

SELECT distinct source from gl_interface

For every source insert a record into gl_interface_control table.

SELECT apps.gl_journal_import_s.NEXTVAL INTO irun_id FROM DUAL;

INSERT INTO apps.gl_interface_control (status, set_of_books_id, je_source_name, group_id, interface_run_id) VALUES ('S', <ledger_id>, <source_name> ,NULL, irun_id);

commit;

Submit journal Import for each run_id.

v_req_id :=
apps.fnd_request.submit_request
('SQLGL',

'GLLEZL',

'',

FALSE,

to_char(irun_id),

1000,

‘N’,

'',

‘N’,

'Y',

chr(0),

'', '', '', '', '', '', '', '', '', '',

'');

3 comments:

UnknownApril 9, 2018 at 7:17 PM

Thanks for the given information you are providing content very usefull to users.
Learn Complete GL Interface Program in Oracle Apps R12 Actual cost of this course is 3,200/- But now Kuncham Software Solutions Pvt ltd is giving you Free access. Limited period offer Hurry-up Enroll Now at Udemy.

Hurry-Up Enroll Now and get Free Access to the Entire Course

UnknownNovember 5, 2018 at 1:47 AM
Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updatingmulesoft online training Hyderabad
AnonymousMay 18, 2022 at 12:10 AM
perde modelleri
sms onay
mobil ödeme bozdurma
Nft nasil alınır
ankara evden eve nakliyat
trafik sigortasi
Dedektor
web sitesi kurma
aşk kitapları

Oracle Apps World

Pages

GL

Oracle General Ledger Journal Import

Oracle Secondary Ledger - Setup and Usage

Introduction

Functional Requirement

Solution Designed

Implementation of Solution

Setups and Prerequisites

Database Object Designed

Data Loading

Validation

API’s Used

Error Encountered

Error Handling Program Logic

Pseudo Code

3 comments: