Hi,
I am trying to understand the validations normally performed when data is transfered from staging tables to interface tables. For example, staging table to GL_INTERFACE_TABLE. Can any one give some examples the kind of validations u may perform in this scenario??
thanks and regards
Prhlly
Help needed in understanding validations...
hi,
I read the GL user guides and understood the validations GL import programme will do when importing data from GL_INTERFACE table to production tables. But i am wondering how one would perform validations when transfering data from temporary tables to interface tables. Can any one explain the concepts with some examples?
I read the GL user guides and understood the validations GL import programme will do when importing data from GL_INTERFACE table to production tables. But i am wondering how one would perform validations when transfering data from temporary tables to interface tables. Can any one explain the concepts with some examples?
see documentaion of gl
Exporting Data From Your Feeder System
Journal Import receives accounting data from the GL_INTERFACE
table. For non?Oracle applications, you must import data from your
feeder systems to this table. Use an import utility, or have your on?site
MIS personnel or Oracle consultant develop an import program for
you.
Your import program should convert data from your feeder system
into a standard data format that Journal Import can read from the
GL_INTERFACE table. Journal Import can then convert your import
data into your General Ledger application journal entries. You can
write an import program to import data from a non?Oracle system, or
you can write an import program to import historical data from your
previous accounting system.
The GL_INTERFACE Table
The GL_INTERFACE table is where Journal Import receives accounting
data that you import from other systems. When Journal Import
receives this data, it validates and converts your import data into
journal entries within your General Ledger application. The
GL_INTERFACE table is organized by columns in which your General
Ledger application categorizes and stores specific accounting data. For
example, journal entry source information is stored in the column
called JE_SOURCE_NAME. GL_INTERFACE contains the columns
shown in the the following table:
Column Name Null? Type
STATUS NOT NULL VARCHAR2 (50)
SET_OF_BOOKS_ID NOT NULL NUMBER (15)
USER_JE_SOURCE_NAME NOT NULL VARCHAR2 (25)
USER_JE_CATEGORY_NAME NOT NULL VARCHAR2 (25)
ACCOUNTING_DATE NOT NULL DATE
CURRENCY_CODE NOT NULL VARCHAR2 (15)
DATE_CREATED NOT NULL DATE
CREATED_BY NOT NULL NUMBER (15)
ACTUAL_FLAG NOT NULL VARCHAR2 (1)
Table 1 ? 17 GL_INTERFACE Table (Page 1 of 3)
Journal Entry 1 ? 121
Column Name Null? Type
ENCUMBRANCE_TYPE_ID NUMBER
BUDGET_VERSION_ID NUMBER
CURRENCY_
CONVERSION_DATE
DATE
USER_CURRENCY_
CONVERSION_TYPE
VARCHAR2 (30)
CURRENCY_
CONVERSION_RATE
NUMBER
SEGMENT1 through
SEGMENT30
VARCHAR (25)
ENTERED_DR NUMBER
ENTERED_CR NUMBER
ACCOUNTED_DR NUMBER
ACCOUNTED_CR NUMBER
TRANSACTION_DATE DATE
REFERENCE1 VARCHAR2 (100)
REFERENCE2 VARCHAR2 (240)
REFERENCE3 VARCHAR2 (100)
REFERENCE4 VARCHAR2 (100)
REFERENCE5 VARCHAR2 (240)
REFERENCE6 through
REFERENCE9
VARCHAR2 (100)
REFERENCE10 VARCHAR2 (240)
REFERENCE11 through
REFERENCE20
VARCHAR2 (100)
REFERENCE21 through
REFERENCE30
VARCHAR2 (240)
GROUP_ID NUMBER (15)
JE_BATCH_ID NUMBER (15)
PERIOD_NAME VARCHAR2 (15)
JE_HEADER_ID NUMBER (15)
JE_LINE_NUM NUMBER (15)
CHART_OF_ACCOUNTS_ID NUMBER (15)
Table 1 ? 17 GL_INTERFACE Table (Page 2 of 3)
1 ? 122 Oracle General Ledger User Guide
Column Name Null? Type
FUNCTIONAL_
CURRENCY_CODE
VARCHAR2 (15)
CODE_COMBINATION_ID NUMBER (15)
DATE_CREATED_IN_GL DATE
WARNING_CODE VARCHAR2 (4)
STATUS_DESCRIPTION VARCHAR2 (240)
DESCR_FLEX_ERROR_
MESSAGE
VARCHAR2 (240)
STAT_AMOUNT NUMBER
REQUEST_ID NUMBER (15)
SUBLEDGER_DOC_
SEQUENCE_ID
NUMBER
SUBLEDGER_DOC_
SEQUENCE_VALUE
NUMBER
USSGL_TRANSACTION_CODE VARCHAR2 (30)
ATTRIBUTE1 through
ATTRIBUTE20
VARCHAR2 (150)
CONTEXT VARCHAR2 (150)
CONTEXT2 VARCHAR2 (150)
CONTEXT3 VARCHAR2 (150)
INVOICE_DATE DATE
INVOICE_AMOUNT NUMBER
INVOICE_IDENTIFIER VARCHAR2 (20)
TAX_CODE VARCHAR2 (15)
REFERENCE_DATE DATE
Table 1 ? 17 GL_INTERFACE Table (Page 3 of 3)
Assigning Values for Accounts
You can specify your accounts in the GL_INTERFACE table in one of
two ways: segment specification or code combination ID specification.
Journal Entry 1 ? 123
Segment Specification
Assign an account value for each segment that you enabled in your
General Ledger application. For example, if you enabled four account
segments, you need to first determine into which columns of the
GL_INTERFACE table you should enter data. This can be done by
looking at the Column field of each segment in the Key Flexfield
Segments window. In this example we find that:
? Segment 1 corresponds to the column SEGMENT1
? Segment 2 corresponds to the column SEGMENT2
? Segment 3 corresponds to the column SEGMENT4
? Segment 4 corresponds to the column SEGMENT5
Note: The column named SEGMENT3 is not used.
Given the above information above, you should load the data as shown
in the following table:
Data for Flexfield Load Into:
Segment 1 GL_INTERFACE.SEGMENT1
Segment 2 GL_INTERFACE.SEGMENT2
Segment 3 GL_INTERFACE.SEGMENT4
Segment 4 GL_INTERFACE.SEGMENT5
Load valid enabled segment values for your enabled segments into the
GL_INTERFACE table. The segment values must already be defined in
your General Ledger application.
For example, value 01 is not the same as value 1. You can specify
Maximum Size and Right?justify Zero?fill Numbers when you define
your value sets in the Value Sets form. Maximum Size indicates the
maximum width of each segment value that Journal Import expects.
Right?justify Zero?fill Numbers indicates whether your account should
right justify and zero?fill numbers when you enter values for a
particular value set. If you have the Right?justify Zero?fill Numbers
option enabled, and your Maximum Size is three, then your segment
value would be 001. However, if your Maximum Size is four, then your
segment value would be 0001. Journal Import does not allow null
values in enabled segments.
1 ? 124 Oracle General Ledger User Guide
Code Combination ID Specification
Alternatively, you can enter a code combination ID to identify your
account segments. You can find a list of valid account code
combinations and their corresponding code combination IDs in the
GL_CODE_COMBINATIONS table. If you want Journal Import to use
the code combination ID to create your journal entries, enter the
appropriate code combination ID in the CODE_COMBINATION_ID
column of the GL_INTERFACE table and do not enter values in the
SEGMENT1 through SEGMENT30 columns.
If you enter values for your account segments in the SEGMENT1
through SEGMENT30 columns and enter a value in the
CODE_COMBINATION_ID column, Journal Import uses the Segment
column values to create your journal entries.
If you enter segment values for an invalid account in the
GL_INTERFACE table, General Ledger prints the invalid account in
your Journal Import Execution Report. If you enter a code combination
ID and if suspense posting is disabled, General Ledger prints the
invalid code combination ID in your Journal Import Execution Report.
If you enter a code combination ID and if suspense posting is enabled,
General Ledger prints only the segment value separators in your
Journal Import Execution Report. Therefore, we recommend that you
disable suspense posting if entering code combination IDs.
See Also
Overview of Setting Up: page 9 ? 2
Defining Accounts: page 9 ? 49
Assigning Values for Additional Required and Conditionally Required Columns
You must enter values in all columns of the GL_INTERFACE table that
require values, which includes all of the not null columns, in order for
Journal Import to successfully convert your import data into journal
entries.
Enter values in the following required columns of the GL_INTERFACE
table:
STATUS: Enter the value NEW to indicate that you are bringing
new data into your General Ledger application.
Journal Entry 1 ? 125
SET_OF_BOOKS_ID: Enter the appropriate set of books ID for
your transaction. You define your set of books in the Set of Books
form of your General Ledger application. You can find a list of
valid values in the SET_OF_BOOKS_ID column of the Sets of
Books table (GL_SETS_OF_BOOKS. SET_OF_BOOKS_ID).
Suggestion: You may use the following SQL*Statement to
access the appropriate set of books ID:
SELECT SET_OF_BOOKS_ID, NAME
FROM GL_SETS_OF_BOOKS;
USER_JE_SOURCE_NAME: Enter the journal entry source name
for your transaction. You define journal sources in the Journal
Sources form of your General Ledger application. You can find a
list of valid values in the USER_JE_SOURCE_NAME column of the
Journal Entry Sources table
(GL_JE_SOURCES.USER_JE_SOURCE_NAME).
USER_JE_CATEGORY_NAME: Enter the journal category name
for your transaction. You define journal categories in the Journal
Categories form of your General Ledger application. You can find
a list of valid values in the USER_JE_CATEGORY_ NAME column
of the Journal Entry Categories table
(GL_JE_CATEGORIES.USER_JE_ CATEGORY_NAME).
ACCOUNTING_DATE: Enter the accounting date on which your
transaction occurred. Your General Ledger application
automatically assigns your journal batch to the accounting period
that includes your accounting date. If you have average balance
processing enabled, General Ledger uses your defined Effective
Date Rules to validate the accounting date against your transaction
calendar to determine the transaction?s effective date.
CURRENCY_CODE: Enter the currency code for your transaction.
You define new currency codes in the Currencies form of your
General Ledger application. You can find a list of valid values in
the CURRENCY_CODE column of the Currencies table
(FND_CURRENCIES.CURRENCY_CODE).
DATE_CREATED: Enter the date your import journal entry line
was created. The information you enter here is for your own
records, and does not appear in your General Ledger application.
CREATED_BY: Enter an ID that you can use to identify the data
coming from your feeder system. The ID you enter provides you
with an audit trail from Journal Import data to your feeder system.
However, your Journal Import data will be removed from the
1 ? 126 Oracle General Ledger User Guide
GL_INTERFACE table after it is successfully imported, and this ID
will not appear in your General Ledger application.
ACTUAL_FLAG: Enter the value A for actual amounts, B for
Budget amounts, or E for encumbrance amounts.
ENCUMBRANCE_TYPE_ID: If you entered the value E in the
ACTUAL_FLAG column of the GL_INTERFACE table, you must
enter the appropriate encumbrance ID. You define new
encumbrance types in the Encumbrance Types form of your
General Ledger application. You can find a list of valid values in
the ENCUMBRANCE_TYPE_ID column of the Encumbrance Types
table (GL_ ENCUMBRANCE_TYPES. ENCUMBRANCE_
TYPE_ID).
Suggestion: We recommend you use the following
SQL*Statement to identify the appropriate encumbrance type
ID:
SELECT ENCUMBRANCE_TYPE_ID,
ENCUMBRANCE_TYPE
FROM GL_ENCUMBRANCE_TYPES
WHERE ENABLED_FLAG = ?Y?;
BUDGET_VERSION_ID: If you entered the value B in the
ACTUAL_FLAG column of the GL_INTERFACE table, you must
enter the appropriate budget ID. You define new budget versions
in the Define Budget form of your General Ledger application. You
can find a list of valid values in the BUDGET_ VERSION_ID
column of the Budget Versions table
(GL_BUDGET_VERSIONS.BUDGET_VERSION_ ID).
Suggestion: We recommend you use the following
SQL*Statement to identify the appropriate budget version ID:
SELECT BUDGET_VERSION_ID,
BUDGET_NAME
FROM GL_BUDGET_VERSIONS
WHERE STATUS IN (?C?,?O?);
PERIOD_NAME: To import actual and encumbrance journals,
specify the period name and an accounting date within that period.
Journal Import will import journals into adjusting and
non?adjusting periods.
To import budget transactions, only enter a period name for your
budget transactions (ACTUAL_FLAG = B). This column is
required when you are importing budget data using Journal
Import. If you want to import budget data using Journal Import,
Journal Entry 1 ? 127
you must supply a period name instead of an accounting date.
And, your period name must be associated with an open budget
fiscal year. Journal Import will import budget journals into
adjusting and non?adjusting periods.
ENTERED_DR: Enter the debit amount for each line of your
transaction. You can enter a value for the ENTERED_DR and the
ENTERED_CR in one row.
ENTERED_CR: Enter the credit amount for each line of your
transaction. You can enter a value for the ENTERED_DR and the
ENTERED_CR in one row.
See Also
Defining Sets of Books: page 9 ? 96
Defining Budgets: page 2 ? 18
Defining Currencies: page 11 ? 6
Defining Encumbrance Types: page 12 ? 6
Defining Journal Sources: page 9 ? 111
Defining Journal Categeories: page 9 ? 115
Understanding the GL_INTERFACE Table: page 1 ? 120
Overview (of Average Balance Processing): page 13 ? 2
Assigning Values for Currency Conversion
You can enter values for your actual foreign currency data in one of
two ways. You can specify the entered amount along with a conversion
rate type and date and let your General Ledger application calculate
the converted amount for you. Or, you can directly specify the entered
and converted amounts and not specify the conversion rate, type and
date.
Do not enter values in the following columns for encumbrance and
budget foreign currency data. Enter values for your actual foreign
currency data only in the following columns of the GL_INTERFACE
table:
1 ? 128 Oracle General Ledger User Guide
System?Calculated Conversion
USER_CURRENCY_CONVERSION_TYPE: Enter a currency
conversion type for your actual foreign currency transactions.
Acceptable values are User, Spot, Corporate, or any other type you
define in the Conversion Rate Types form. 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.
You can find a list of valid values in the
USER_CURRENCY_CONVERSION_TYPE column of the
Conversion Types table (GL_DAILY_
CONVERSION_TYPES.USER_CURRENCY_
CONVERSION_TYPE).
CURRENCY_CONVERSION_DATE: Enter a currency conversion
date for your actual foreign currency transactions. If you enter a
conversion type other than User in the
USER_CURRENCY_CONVERSION_TYPE column, you must enter
a value in this column. If your conversion type is User, the default
value for this column is the accounting date.
CURRENCY_CONVERSION_RATE: Enter a currency conversion
rate for your actual foreign currency transactions. If you enter a
conversion type of User in the USER_CURRENCY_
CONVERSION_TYPE column, you must enter a value in this
column. If you enter a conversion type other than USER, do not
enter anything in this column.
User?Entered Conversion
ACCOUNTED_DR: Enter a converted debit amount for your
actual foreign currency transactions. You can enter a value for the
ACCOUNTED_DR and ACCOUNTED_CR in one row. You must
enter a value for ENTERED_DR if you entered a value for
ACCOUNTED_DR.
ACCOUNTED_CR: Enter a converted credit amount for your
actual foreign currency transactions. You can enter a value for the
ACCOUNTED_DR and ACCOUNTED_CR in one row. You must
enter a value for ENTERED_CR if you entered a value for
ACCOUNTED_CR.
Journal Entry 1 ? 129
Assigning Values to Optional Columns
You can enter values for many optional columns in the
GL_INTERFACE table. Enter values in these columns for maximum
control over the way Journal Import groups the journal entry lines it
creates into journal entries.
If you have enabled average balance processing, Journal Import will
group transactions by Accounting Date. Transactions are grouped
before they are validated and, if the Effective Date Rule is Roll Date,
rolled to the nearest valid business day within the period.
If you do not enter a value in an optional column and a default value
exists for that particular column, Journal Import automatically enters
the default value.
Enter values in the following optional columns of the GL_INTERFACE
table:
REFERENCE1 (Batch Name): Enter a batch name for your import
batch. Journal Import creates a default batch name using the
following format: (Optional User?Entered REFERENCE1) (Source)
(Request ID) (Actual Flag) (Group ID). If you enter a batch name,
Journal Import prefixes the first 50 characters of your batch name
to the above format.
REFERENCE2 (Batch Description): Enter a description for your
batch. If you do not enter a batch description, Journal Import
automatically gives your batch a description using the format:
Journal Import (Source) (Request Id).
REFERENCE4 (Journal entry name): Enter a journal entry name
for your journal entry. Journal Import creates a default journal
entry name using the following format: (Category Name)
(Currency) (Currency Conversion Type, if applicable) (Currency
Conversion Rate, if applicable) (Currency Conversion Date, if
applicable) (Encumbrance Type ID, if applicable) (Budget Version
ID, if applicable). If you enter a journal entry name, Journal Import
prepends the first 25 characters of your journal entry name to the
above format.
REFERENCE5 (Journal entry description): Enter a description for
your journal entry. If you do not enter a journal entry description,
Journal Import automatically gives your journal entry a description
using the format: Journal Import ? Concurrent Request ID.
REFERENCE6 (Journal entry reference): Enter a reference name or
number for your journal entry. If you do not enter a journal entry
1 ? 130 Oracle General Ledger User Guide
reference, Journal Import automatically creates a journal entry
reference called Journal Import Created.
REFERENCE7 (Journal entry reversal flag): Enter Yes to mark
your journal entry for reversal. If you do not enter Yes, Journal
Import automatically defaults to No.
REFERENCE8 (Journal entry reversal period): Enter the name of
the period to which you want to reverse your journal entry. If you
enter Yes in the REFERENCE7 column, you must enter a value in
this column.
If you have enabled average balance processing, enter the effective
date for the reversal. General Ledger will determine the
appropriate reversal period based on the date you supply.
You can specify adjustment and non?adjustment reversal periods.
Note: The effective date only applies to Actual balances, not
Budget or Encumbrance balances.
Note: If you enter Yes in the REFERENCE7 column, you must
enter a value in this column.
REFERENCE9 (Journal reversal method): Enter Yes to use the
change sign method, No to use the switch debit/credit method.
REFERENCE10 (Journal entry line description): Enter a
description for your journal entry line. If you do not enter a
journal entry line description, Journal Import uses the subledger
document sequence value. If there is no document sequence value,
Journal Import creates a journal entry description called Journal
Import Created.
REFERENCE21 through REFERENCE30: Enter a reference name
or number to further identify your import journal entry lines.
Columns REFERENCE21 through REFERENCE30 map into
columns REFERENCE_1 through REFERENCE_10, respectively, of
the GL_JE_LINES table.
Once in the GL_JE_LINES table, your General Ledger application
prints the value stored in REFERENCE_1 in standard reports run
with Line detail, and prints the value stored in REFERENCE_4 in
standard reports run with Source detail. The other reference
columns are for descriptive or tracking purposes only. The values
in these columns are not used in your General Ledger application.
GROUP_ID: Enter a unique group number to distinguish import
data within a source. You can run Journal Import in parallel for the
same source if you specify a unique group number for each
request.
Journal Entry 1 ? 131
STAT_AMOUNT: Enter the statistical amount associated with
your journal entry line data. You define statistical units of measure
in the Statistical Units of Measure form of your General Ledger
application. You must use this column when you want to see
statistical and monetary amounts in the same journal entry line.
USSGL_TRANSACTION_CODE: Enter a valid USSGL
transaction code for your journal entry line. Journal Import
validates and imports the USSGL transaction codes when you have
the profile option Enable Transaction Code set to Yes, and you have
defined your USSGL transaction codes using the Public Sector
Transaction Codes window.
Note: This column is ignored for commercial installations of
General Ledger.
ATTRIBUTE1 through ATTRIBUTE 10: Enter values for your
descriptive flexfield ?Journals ? Journal Entry Line?. The values
you enter depend on how you defined your descriptive flexfield in
the Descriptive Flexfield Segments form. See: Defining Descriptive
Flexfields for Oracle General Ledger: page 9 ? 25. See Oracle
Applications User Guide.
ATTRIBUTE11 through ATTRIBUTE 20: Enter values for your
descriptive flexfield ?Journals ? Captured Information?. The
values you enter depend on how you defined your descriptive
flexfield in the Descriptive Flexfield Segments form. The context for
Journals ? Captured Information is the natural account value of the
account used on each line. See: Defining Descriptive Flexfields for
Oracle General Ledger: page 9 ? 25. See Oracle Applications User
Guide.
CONTEXT: Enter the context field value for the descriptive
flexfield ?Journals ? Journal Entry Line? that identifies the
structure of your descriptive flexfield. If you enter a value, you can
also enter some combination of values in the columns
ATTRIBUTE1 through ATTRIBUTE10.
CONTEXT2: Enter Yes to identify your Value Added Tax
Descriptive Flexfield structure. You must use this column if you
import data for the Value Added Tax Descriptive Flexfield. Enter
No to indicate that your journal entry line is not a tax item. If you
enter No, the four Value Added Tax Descriptive Flexfield related
columns must be null.
CONTEXT3: Enter the context field value (natural account) for the
descriptive flexfield ?Journals ? Captured Information? that
identifies the structure of your descriptive flexfield. Enter a value
only if you are importing the descriptive flexfield ?Journals ?
1 ? 132 Oracle General Ledger User Guide
Captured Information? without validation. If you enter a value,
you can also enter some combination of values in the columns
ATTRIBUTE11 through ATTRIBUTE20.
INVOICE_DATE: Enter the date on which you paid or collected
tax on your tax journal entry line. Enter the date in the format
DD?MON?YY or the default date format for your language. Your
invoice date should correspond to the date when tax amounts were
paid or received for this invoice. You must use this column if you
import data for the Value Added Tax Descriptive Flexfield.
INVOICE_AMOUNT: Enter an invoice amount. Enter the net
invoice amount that relates to your tax journal entry line amount.
You must use this column if you import data for the Value Added
Tax Descriptive Flexfield.
TAX_CODE: Enter a valid tax code that identifies the type of tax
paid for this invoice. You define a list of valid tax codes for this
field when you define your descriptive flexfield values. You must
use this column if you import data for the Value Added Tax
Descriptive Flexfield.
REFERENCE_DATE: Enter a date to capture additional date
information about your journal. The Reference Date column
satisfies Libro Giornale requirements in Italy, but can be used to
capture any other date information you want to store at the journal
header level. This column is not used to group journal lines. If
multiple rows in the GL_INTERFACE table use different reference
dates, they can be grouped into the same journal entry which will
choose a reference date from one of the rows to use as a reference
date for the journal entry.
INVOICE_IDENTIFIER: Enter an invoice identifier. Enter
reference information about the source document or invoice upon
which you paid or collected tax. You must use this column if you
import data for the Value Added Tax Descriptive Flexfield.
Overview (of Average Balance Processing): page 13 ? 2
Required NULL Columns in the GL_INTERFACE Table
Some columns in the GL_INTERFACE table must be NULL as Journal
Import uses them for internal processing or does not use them in the
current release. The following columns must be NULL in your General
Ledger application:
REFERENCE3: Do not enter a value in this column.
Journal Entry 1 ? 133
REFERENCE11 through REFERENCE20: Do not enter a value in
this column.
TRANSACTION_DATE: Do not enter a value in this column.
JE_BATCH_ID: Do not enter a value in this column.
JE_HEADER_ID: Do not enter a value in this column.
JE_LINE_NUM: Do not enter a value in this column.
CHART_OF_ACCOUNTS_ID: Do not enter a value in this
column.
FUNCTIONAL_CURRENCY_CODE: Do not enter a value in this
column.
DATE_CREATED_IN_GL: Do not enter a value in this column.
WARNING_CODE: Do not enter a value in this column.
STATUS_DESCRIPTION: Do not enter a value in this column.
DESC_FLEX_ERROR_MESSAGE: Do not enter a value in this
column.
REQUEST_ID: Do not enter a value in this column.
SUBLEDGER_DOC_SEQUENCE_ID: Do not enter a value in this
column.
SUBLEDGER_DOC_SEQUENCE_VALUE: Used for
communication between General Ledger and the subledgers. Do
not populate with your own data.
See Also
Exporting Data From Your Feeder System
Journal Import receives accounting data from the GL_INTERFACE
table. For non?Oracle applications, you must import data from your
feeder systems to this table. Use an import utility, or have your on?site
MIS personnel or Oracle consultant develop an import program for
you.
Your import program should convert data from your feeder system
into a standard data format that Journal Import can read from the
GL_INTERFACE table. Journal Import can then convert your import
data into your General Ledger application journal entries. You can
write an import program to import data from a non?Oracle system, or
you can write an import program to import historical data from your
previous accounting system.
The GL_INTERFACE Table
The GL_INTERFACE table is where Journal Import receives accounting
data that you import from other systems. When Journal Import
receives this data, it validates and converts your import data into
journal entries within your General Ledger application. The
GL_INTERFACE table is organized by columns in which your General
Ledger application categorizes and stores specific accounting data. For
example, journal entry source information is stored in the column
called JE_SOURCE_NAME. GL_INTERFACE contains the columns
shown in the the following table:
Column Name Null? Type
STATUS NOT NULL VARCHAR2 (50)
SET_OF_BOOKS_ID NOT NULL NUMBER (15)
USER_JE_SOURCE_NAME NOT NULL VARCHAR2 (25)
USER_JE_CATEGORY_NAME NOT NULL VARCHAR2 (25)
ACCOUNTING_DATE NOT NULL DATE
CURRENCY_CODE NOT NULL VARCHAR2 (15)
DATE_CREATED NOT NULL DATE
CREATED_BY NOT NULL NUMBER (15)
ACTUAL_FLAG NOT NULL VARCHAR2 (1)
Table 1 ? 17 GL_INTERFACE Table (Page 1 of 3)
Journal Entry 1 ? 121
Column Name Null? Type
ENCUMBRANCE_TYPE_ID NUMBER
BUDGET_VERSION_ID NUMBER
CURRENCY_
CONVERSION_DATE
DATE
USER_CURRENCY_
CONVERSION_TYPE
VARCHAR2 (30)
CURRENCY_
CONVERSION_RATE
NUMBER
SEGMENT1 through
SEGMENT30
VARCHAR (25)
ENTERED_DR NUMBER
ENTERED_CR NUMBER
ACCOUNTED_DR NUMBER
ACCOUNTED_CR NUMBER
TRANSACTION_DATE DATE
REFERENCE1 VARCHAR2 (100)
REFERENCE2 VARCHAR2 (240)
REFERENCE3 VARCHAR2 (100)
REFERENCE4 VARCHAR2 (100)
REFERENCE5 VARCHAR2 (240)
REFERENCE6 through
REFERENCE9
VARCHAR2 (100)
REFERENCE10 VARCHAR2 (240)
REFERENCE11 through
REFERENCE20
VARCHAR2 (100)
REFERENCE21 through
REFERENCE30
VARCHAR2 (240)
GROUP_ID NUMBER (15)
JE_BATCH_ID NUMBER (15)
PERIOD_NAME VARCHAR2 (15)
JE_HEADER_ID NUMBER (15)
JE_LINE_NUM NUMBER (15)
CHART_OF_ACCOUNTS_ID NUMBER (15)
Table 1 ? 17 GL_INTERFACE Table (Page 2 of 3)
1 ? 122 Oracle General Ledger User Guide
Column Name Null? Type
FUNCTIONAL_
CURRENCY_CODE
VARCHAR2 (15)
CODE_COMBINATION_ID NUMBER (15)
DATE_CREATED_IN_GL DATE
WARNING_CODE VARCHAR2 (4)
STATUS_DESCRIPTION VARCHAR2 (240)
DESCR_FLEX_ERROR_
MESSAGE
VARCHAR2 (240)
STAT_AMOUNT NUMBER
REQUEST_ID NUMBER (15)
SUBLEDGER_DOC_
SEQUENCE_ID
NUMBER
SUBLEDGER_DOC_
SEQUENCE_VALUE
NUMBER
USSGL_TRANSACTION_CODE VARCHAR2 (30)
ATTRIBUTE1 through
ATTRIBUTE20
VARCHAR2 (150)
CONTEXT VARCHAR2 (150)
CONTEXT2 VARCHAR2 (150)
CONTEXT3 VARCHAR2 (150)
INVOICE_DATE DATE
INVOICE_AMOUNT NUMBER
INVOICE_IDENTIFIER VARCHAR2 (20)
TAX_CODE VARCHAR2 (15)
REFERENCE_DATE DATE
Table 1 ? 17 GL_INTERFACE Table (Page 3 of 3)
Assigning Values for Accounts
You can specify your accounts in the GL_INTERFACE table in one of
two ways: segment specification or code combination ID specification.
Journal Entry 1 ? 123
Segment Specification
Assign an account value for each segment that you enabled in your
General Ledger application. For example, if you enabled four account
segments, you need to first determine into which columns of the
GL_INTERFACE table you should enter data. This can be done by
looking at the Column field of each segment in the Key Flexfield
Segments window. In this example we find that:
? Segment 1 corresponds to the column SEGMENT1
? Segment 2 corresponds to the column SEGMENT2
? Segment 3 corresponds to the column SEGMENT4
? Segment 4 corresponds to the column SEGMENT5
Note: The column named SEGMENT3 is not used.
Given the above information above, you should load the data as shown
in the following table:
Data for Flexfield Load Into:
Segment 1 GL_INTERFACE.SEGMENT1
Segment 2 GL_INTERFACE.SEGMENT2
Segment 3 GL_INTERFACE.SEGMENT4
Segment 4 GL_INTERFACE.SEGMENT5
Load valid enabled segment values for your enabled segments into the
GL_INTERFACE table. The segment values must already be defined in
your General Ledger application.
For example, value 01 is not the same as value 1. You can specify
Maximum Size and Right?justify Zero?fill Numbers when you define
your value sets in the Value Sets form. Maximum Size indicates the
maximum width of each segment value that Journal Import expects.
Right?justify Zero?fill Numbers indicates whether your account should
right justify and zero?fill numbers when you enter values for a
particular value set. If you have the Right?justify Zero?fill Numbers
option enabled, and your Maximum Size is three, then your segment
value would be 001. However, if your Maximum Size is four, then your
segment value would be 0001. Journal Import does not allow null
values in enabled segments.
1 ? 124 Oracle General Ledger User Guide
Code Combination ID Specification
Alternatively, you can enter a code combination ID to identify your
account segments. You can find a list of valid account code
combinations and their corresponding code combination IDs in the
GL_CODE_COMBINATIONS table. If you want Journal Import to use
the code combination ID to create your journal entries, enter the
appropriate code combination ID in the CODE_COMBINATION_ID
column of the GL_INTERFACE table and do not enter values in the
SEGMENT1 through SEGMENT30 columns.
If you enter values for your account segments in the SEGMENT1
through SEGMENT30 columns and enter a value in the
CODE_COMBINATION_ID column, Journal Import uses the Segment
column values to create your journal entries.
If you enter segment values for an invalid account in the
GL_INTERFACE table, General Ledger prints the invalid account in
your Journal Import Execution Report. If you enter a code combination
ID and if suspense posting is disabled, General Ledger prints the
invalid code combination ID in your Journal Import Execution Report.
If you enter a code combination ID and if suspense posting is enabled,
General Ledger prints only the segment value separators in your
Journal Import Execution Report. Therefore, we recommend that you
disable suspense posting if entering code combination IDs.
See Also
Overview of Setting Up: page 9 ? 2
Defining Accounts: page 9 ? 49
Assigning Values for Additional Required and Conditionally Required Columns
You must enter values in all columns of the GL_INTERFACE table that
require values, which includes all of the not null columns, in order for
Journal Import to successfully convert your import data into journal
entries.
Enter values in the following required columns of the GL_INTERFACE
table:
STATUS: Enter the value NEW to indicate that you are bringing
new data into your General Ledger application.
Journal Entry 1 ? 125
SET_OF_BOOKS_ID: Enter the appropriate set of books ID for
your transaction. You define your set of books in the Set of Books
form of your General Ledger application. You can find a list of
valid values in the SET_OF_BOOKS_ID column of the Sets of
Books table (GL_SETS_OF_BOOKS. SET_OF_BOOKS_ID).
Suggestion: You may use the following SQL*Statement to
access the appropriate set of books ID:
SELECT SET_OF_BOOKS_ID, NAME
FROM GL_SETS_OF_BOOKS;
USER_JE_SOURCE_NAME: Enter the journal entry source name
for your transaction. You define journal sources in the Journal
Sources form of your General Ledger application. You can find a
list of valid values in the USER_JE_SOURCE_NAME column of the
Journal Entry Sources table
(GL_JE_SOURCES.USER_JE_SOURCE_NAME).
USER_JE_CATEGORY_NAME: Enter the journal category name
for your transaction. You define journal categories in the Journal
Categories form of your General Ledger application. You can find
a list of valid values in the USER_JE_CATEGORY_ NAME column
of the Journal Entry Categories table
(GL_JE_CATEGORIES.USER_JE_ CATEGORY_NAME).
ACCOUNTING_DATE: Enter the accounting date on which your
transaction occurred. Your General Ledger application
automatically assigns your journal batch to the accounting period
that includes your accounting date. If you have average balance
processing enabled, General Ledger uses your defined Effective
Date Rules to validate the accounting date against your transaction
calendar to determine the transaction?s effective date.
CURRENCY_CODE: Enter the currency code for your transaction.
You define new currency codes in the Currencies form of your
General Ledger application. You can find a list of valid values in
the CURRENCY_CODE column of the Currencies table
(FND_CURRENCIES.CURRENCY_CODE).
DATE_CREATED: Enter the date your import journal entry line
was created. The information you enter here is for your own
records, and does not appear in your General Ledger application.
CREATED_BY: Enter an ID that you can use to identify the data
coming from your feeder system. The ID you enter provides you
with an audit trail from Journal Import data to your feeder system.
However, your Journal Import data will be removed from the
1 ? 126 Oracle General Ledger User Guide
GL_INTERFACE table after it is successfully imported, and this ID
will not appear in your General Ledger application.
ACTUAL_FLAG: Enter the value A for actual amounts, B for
Budget amounts, or E for encumbrance amounts.
ENCUMBRANCE_TYPE_ID: If you entered the value E in the
ACTUAL_FLAG column of the GL_INTERFACE table, you must
enter the appropriate encumbrance ID. You define new
encumbrance types in the Encumbrance Types form of your
General Ledger application. You can find a list of valid values in
the ENCUMBRANCE_TYPE_ID column of the Encumbrance Types
table (GL_ ENCUMBRANCE_TYPES. ENCUMBRANCE_
TYPE_ID).
Suggestion: We recommend you use the following
SQL*Statement to identify the appropriate encumbrance type
ID:
SELECT ENCUMBRANCE_TYPE_ID,
ENCUMBRANCE_TYPE
FROM GL_ENCUMBRANCE_TYPES
WHERE ENABLED_FLAG = ?Y?;
BUDGET_VERSION_ID: If you entered the value B in the
ACTUAL_FLAG column of the GL_INTERFACE table, you must
enter the appropriate budget ID. You define new budget versions
in the Define Budget form of your General Ledger application. You
can find a list of valid values in the BUDGET_ VERSION_ID
column of the Budget Versions table
(GL_BUDGET_VERSIONS.BUDGET_VERSION_ ID).
Suggestion: We recommend you use the following
SQL*Statement to identify the appropriate budget version ID:
SELECT BUDGET_VERSION_ID,
BUDGET_NAME
FROM GL_BUDGET_VERSIONS
WHERE STATUS IN (?C?,?O?);
PERIOD_NAME: To import actual and encumbrance journals,
specify the period name and an accounting date within that period.
Journal Import will import journals into adjusting and
non?adjusting periods.
To import budget transactions, only enter a period name for your
budget transactions (ACTUAL_FLAG = B). This column is
required when you are importing budget data using Journal
Import. If you want to import budget data using Journal Import,
Journal Entry 1 ? 127
you must supply a period name instead of an accounting date.
And, your period name must be associated with an open budget
fiscal year. Journal Import will import budget journals into
adjusting and non?adjusting periods.
ENTERED_DR: Enter the debit amount for each line of your
transaction. You can enter a value for the ENTERED_DR and the
ENTERED_CR in one row.
ENTERED_CR: Enter the credit amount for each line of your
transaction. You can enter a value for the ENTERED_DR and the
ENTERED_CR in one row.
See Also
Defining Sets of Books: page 9 ? 96
Defining Budgets: page 2 ? 18
Defining Currencies: page 11 ? 6
Defining Encumbrance Types: page 12 ? 6
Defining Journal Sources: page 9 ? 111
Defining Journal Categeories: page 9 ? 115
Understanding the GL_INTERFACE Table: page 1 ? 120
Overview (of Average Balance Processing): page 13 ? 2
Assigning Values for Currency Conversion
You can enter values for your actual foreign currency data in one of
two ways. You can specify the entered amount along with a conversion
rate type and date and let your General Ledger application calculate
the converted amount for you. Or, you can directly specify the entered
and converted amounts and not specify the conversion rate, type and
date.
Do not enter values in the following columns for encumbrance and
budget foreign currency data. Enter values for your actual foreign
currency data only in the following columns of the GL_INTERFACE
table:
1 ? 128 Oracle General Ledger User Guide
System?Calculated Conversion
USER_CURRENCY_CONVERSION_TYPE: Enter a currency
conversion type for your actual foreign currency transactions.
Acceptable values are User, Spot, Corporate, or any other type you
define in the Conversion Rate Types form. 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.
You can find a list of valid values in the
USER_CURRENCY_CONVERSION_TYPE column of the
Conversion Types table (GL_DAILY_
CONVERSION_TYPES.USER_CURRENCY_
CONVERSION_TYPE).
CURRENCY_CONVERSION_DATE: Enter a currency conversion
date for your actual foreign currency transactions. If you enter a
conversion type other than User in the
USER_CURRENCY_CONVERSION_TYPE column, you must enter
a value in this column. If your conversion type is User, the default
value for this column is the accounting date.
CURRENCY_CONVERSION_RATE: Enter a currency conversion
rate for your actual foreign currency transactions. If you enter a
conversion type of User in the USER_CURRENCY_
CONVERSION_TYPE column, you must enter a value in this
column. If you enter a conversion type other than USER, do not
enter anything in this column.
User?Entered Conversion
ACCOUNTED_DR: Enter a converted debit amount for your
actual foreign currency transactions. You can enter a value for the
ACCOUNTED_DR and ACCOUNTED_CR in one row. You must
enter a value for ENTERED_DR if you entered a value for
ACCOUNTED_DR.
ACCOUNTED_CR: Enter a converted credit amount for your
actual foreign currency transactions. You can enter a value for the
ACCOUNTED_DR and ACCOUNTED_CR in one row. You must
enter a value for ENTERED_CR if you entered a value for
ACCOUNTED_CR.
Journal Entry 1 ? 129
Assigning Values to Optional Columns
You can enter values for many optional columns in the
GL_INTERFACE table. Enter values in these columns for maximum
control over the way Journal Import groups the journal entry lines it
creates into journal entries.
If you have enabled average balance processing, Journal Import will
group transactions by Accounting Date. Transactions are grouped
before they are validated and, if the Effective Date Rule is Roll Date,
rolled to the nearest valid business day within the period.
If you do not enter a value in an optional column and a default value
exists for that particular column, Journal Import automatically enters
the default value.
Enter values in the following optional columns of the GL_INTERFACE
table:
REFERENCE1 (Batch Name): Enter a batch name for your import
batch. Journal Import creates a default batch name using the
following format: (Optional User?Entered REFERENCE1) (Source)
(Request ID) (Actual Flag) (Group ID). If you enter a batch name,
Journal Import prefixes the first 50 characters of your batch name
to the above format.
REFERENCE2 (Batch Description): Enter a description for your
batch. If you do not enter a batch description, Journal Import
automatically gives your batch a description using the format:
Journal Import (Source) (Request Id).
REFERENCE4 (Journal entry name): Enter a journal entry name
for your journal entry. Journal Import creates a default journal
entry name using the following format: (Category Name)
(Currency) (Currency Conversion Type, if applicable) (Currency
Conversion Rate, if applicable) (Currency Conversion Date, if
applicable) (Encumbrance Type ID, if applicable) (Budget Version
ID, if applicable). If you enter a journal entry name, Journal Import
prepends the first 25 characters of your journal entry name to the
above format.
REFERENCE5 (Journal entry description): Enter a description for
your journal entry. If you do not enter a journal entry description,
Journal Import automatically gives your journal entry a description
using the format: Journal Import ? Concurrent Request ID.
REFERENCE6 (Journal entry reference): Enter a reference name or
number for your journal entry. If you do not enter a journal entry
1 ? 130 Oracle General Ledger User Guide
reference, Journal Import automatically creates a journal entry
reference called Journal Import Created.
REFERENCE7 (Journal entry reversal flag): Enter Yes to mark
your journal entry for reversal. If you do not enter Yes, Journal
Import automatically defaults to No.
REFERENCE8 (Journal entry reversal period): Enter the name of
the period to which you want to reverse your journal entry. If you
enter Yes in the REFERENCE7 column, you must enter a value in
this column.
If you have enabled average balance processing, enter the effective
date for the reversal. General Ledger will determine the
appropriate reversal period based on the date you supply.
You can specify adjustment and non?adjustment reversal periods.
Note: The effective date only applies to Actual balances, not
Budget or Encumbrance balances.
Note: If you enter Yes in the REFERENCE7 column, you must
enter a value in this column.
REFERENCE9 (Journal reversal method): Enter Yes to use the
change sign method, No to use the switch debit/credit method.
REFERENCE10 (Journal entry line description): Enter a
description for your journal entry line. If you do not enter a
journal entry line description, Journal Import uses the subledger
document sequence value. If there is no document sequence value,
Journal Import creates a journal entry description called Journal
Import Created.
REFERENCE21 through REFERENCE30: Enter a reference name
or number to further identify your import journal entry lines.
Columns REFERENCE21 through REFERENCE30 map into
columns REFERENCE_1 through REFERENCE_10, respectively, of
the GL_JE_LINES table.
Once in the GL_JE_LINES table, your General Ledger application
prints the value stored in REFERENCE_1 in standard reports run
with Line detail, and prints the value stored in REFERENCE_4 in
standard reports run with Source detail. The other reference
columns are for descriptive or tracking purposes only. The values
in these columns are not used in your General Ledger application.
GROUP_ID: Enter a unique group number to distinguish import
data within a source. You can run Journal Import in parallel for the
same source if you specify a unique group number for each
request.
Journal Entry 1 ? 131
STAT_AMOUNT: Enter the statistical amount associated with
your journal entry line data. You define statistical units of measure
in the Statistical Units of Measure form of your General Ledger
application. You must use this column when you want to see
statistical and monetary amounts in the same journal entry line.
USSGL_TRANSACTION_CODE: Enter a valid USSGL
transaction code for your journal entry line. Journal Import
validates and imports the USSGL transaction codes when you have
the profile option Enable Transaction Code set to Yes, and you have
defined your USSGL transaction codes using the Public Sector
Transaction Codes window.
Note: This column is ignored for commercial installations of
General Ledger.
ATTRIBUTE1 through ATTRIBUTE 10: Enter values for your
descriptive flexfield ?Journals ? Journal Entry Line?. The values
you enter depend on how you defined your descriptive flexfield in
the Descriptive Flexfield Segments form. See: Defining Descriptive
Flexfields for Oracle General Ledger: page 9 ? 25. See Oracle
Applications User Guide.
ATTRIBUTE11 through ATTRIBUTE 20: Enter values for your
descriptive flexfield ?Journals ? Captured Information?. The
values you enter depend on how you defined your descriptive
flexfield in the Descriptive Flexfield Segments form. The context for
Journals ? Captured Information is the natural account value of the
account used on each line. See: Defining Descriptive Flexfields for
Oracle General Ledger: page 9 ? 25. See Oracle Applications User
Guide.
CONTEXT: Enter the context field value for the descriptive
flexfield ?Journals ? Journal Entry Line? that identifies the
structure of your descriptive flexfield. If you enter a value, you can
also enter some combination of values in the columns
ATTRIBUTE1 through ATTRIBUTE10.
CONTEXT2: Enter Yes to identify your Value Added Tax
Descriptive Flexfield structure. You must use this column if you
import data for the Value Added Tax Descriptive Flexfield. Enter
No to indicate that your journal entry line is not a tax item. If you
enter No, the four Value Added Tax Descriptive Flexfield related
columns must be null.
CONTEXT3: Enter the context field value (natural account) for the
descriptive flexfield ?Journals ? Captured Information? that
identifies the structure of your descriptive flexfield. Enter a value
only if you are importing the descriptive flexfield ?Journals ?
1 ? 132 Oracle General Ledger User Guide
Captured Information? without validation. If you enter a value,
you can also enter some combination of values in the columns
ATTRIBUTE11 through ATTRIBUTE20.
INVOICE_DATE: Enter the date on which you paid or collected
tax on your tax journal entry line. Enter the date in the format
DD?MON?YY or the default date format for your language. Your
invoice date should correspond to the date when tax amounts were
paid or received for this invoice. You must use this column if you
import data for the Value Added Tax Descriptive Flexfield.
INVOICE_AMOUNT: Enter an invoice amount. Enter the net
invoice amount that relates to your tax journal entry line amount.
You must use this column if you import data for the Value Added
Tax Descriptive Flexfield.
TAX_CODE: Enter a valid tax code that identifies the type of tax
paid for this invoice. You define a list of valid tax codes for this
field when you define your descriptive flexfield values. You must
use this column if you import data for the Value Added Tax
Descriptive Flexfield.
REFERENCE_DATE: Enter a date to capture additional date
information about your journal. The Reference Date column
satisfies Libro Giornale requirements in Italy, but can be used to
capture any other date information you want to store at the journal
header level. This column is not used to group journal lines. If
multiple rows in the GL_INTERFACE table use different reference
dates, they can be grouped into the same journal entry which will
choose a reference date from one of the rows to use as a reference
date for the journal entry.
INVOICE_IDENTIFIER: Enter an invoice identifier. Enter
reference information about the source document or invoice upon
which you paid or collected tax. You must use this column if you
import data for the Value Added Tax Descriptive Flexfield.
Overview (of Average Balance Processing): page 13 ? 2
Required NULL Columns in the GL_INTERFACE Table
Some columns in the GL_INTERFACE table must be NULL as Journal
Import uses them for internal processing or does not use them in the
current release. The following columns must be NULL in your General
Ledger application:
REFERENCE3: Do not enter a value in this column.
Journal Entry 1 ? 133
REFERENCE11 through REFERENCE20: Do not enter a value in
this column.
TRANSACTION_DATE: Do not enter a value in this column.
JE_BATCH_ID: Do not enter a value in this column.
JE_HEADER_ID: Do not enter a value in this column.
JE_LINE_NUM: Do not enter a value in this column.
CHART_OF_ACCOUNTS_ID: Do not enter a value in this
column.
FUNCTIONAL_CURRENCY_CODE: Do not enter a value in this
column.
DATE_CREATED_IN_GL: Do not enter a value in this column.
WARNING_CODE: Do not enter a value in this column.
STATUS_DESCRIPTION: Do not enter a value in this column.
DESC_FLEX_ERROR_MESSAGE: Do not enter a value in this
column.
REQUEST_ID: Do not enter a value in this column.
SUBLEDGER_DOC_SEQUENCE_ID: Do not enter a value in this
column.
SUBLEDGER_DOC_SEQUENCE_VALUE: Used for
communication between General Ledger and the subledgers. Do
not populate with your own data.
See Also
Who is online
Users browsing this forum: Google Adsense [Bot] and 4 guests