Introduction
Transfer Journal Entries to GL process transfers inventory/work in process journal entries for a given period into the general ledger. The concurrent program from the subledger , for the entered program parameters, and transfers them to the gl_interface table.
The accounting information related to transactions in Oracle Inventory and Oracle Work in Process modules is held in relevant tables within these modules. However, they need to be accounted in General Ledger (GL) for proper consolidation. The accounting information related to transactions in Oracle Inventory and Oracle Work in Process modules is transferred to GL by running the ‘Transfer Transactions to GL’ concurrent program.
This program is automatically called when the ‘Period End Process (INCTPC)’ is run. However, it can also be explicitly run at any time during an open period. When the process is run with more than one period open, all the transactions from the earliest open period to the date up to which the process is run (Transfer Date) are considered for transfer to GL.
On running the ‘Transfer Transactions to GL’ program, the accounting information is inserted into gl_interface table. ‘Journal Import’ and ‘Post Journals’ processes in Oracle General Ledger can then be used to post the information in General Ledger.
The level of detail that is transferred to GL for the accounting information related to these transactions depends on the value chosen in the Organization Parameters Screen (Navigation: Oracle Inventory > Setup > Organization > Parameters).
The possible values for the ‘Transfer to GL’ field (in Organization Parameters Screen) are – Detail, Summary, None.
If ‘Summary’ is selected, the accounting information pertaining to the transactions is summarized and the summary entries are posted to GL. The advantage of running the process in 'Summary' mode over 'Detail' mode is minimising the posting times. Transferring detail transaction distributions to the general ledger increases general ledger posting times due to the number of records created.
If ‘Detail’ is selected, detailed accounting entries are transferred to GL. In this case, the posting times are larger due to a larger number of records. However, the advantage of this mode is creation of detailed accounting records in GL for every transaction in Inventory/WIP.
If none is selected, no transfer of accounting information to GL is done for this organization. This setting plays an important role, when both Periodic and Perpetual Costing methods are being used in an organization within the same set of books. The application then, prohibits creation of accounting distributions for Perpetual accounting if periodic accounting is used, but it will allow the Periodic accounting transactions to go through.
Parameters
Organization Code An inventory organization, for which the gl transfer has to be done.
Date A date in any open period. When more than one period is open, the transfer selects
transactions from the first open period up to this entered transfer date.
For summary transfers, this entered date is assigned as the date for summarized transactions for that open period. Current Period This value would default from the ‘Date’ parameter above. This is the open period containing the above date. Description Some appropriate text.
Technical Overview
The ‘Transfer Transactions to GL’ concurrent program calls the executable INCTGL which resides in the $INV_TOP/bin directory.
Following are the main tables accessed by the program:
GL_INTERFACE GROUP_ID, REFERENCE21, REFERENCE22,
ACCOUNTING_DATE,
USER_JE_CATEGORY_NAME,
GL_SL_LINK_TABLE
Insert
MTL_TRANSACTION_ACCOUNTS
GL_BATCH_ID , TRANSACTION_ID,
TRANSACTION_DATE
Select/Update
WIP_TRANSACTION_ACCOUNTS
GL_BATCH_ID , TRANSACTION_ID,
TRANSACTION_DATE
Select/Update
MTL_PARAMETERS GENERAL_LEDGER_UPDATE_CODE Select
ORG_GL_BATCHES GL_BATCH_ID, GL_BATCH_DATE,
ACCT_PERIOD_ID, ORGANIZATION_ID
Insert/Delete/Update
ORG_ACCT_PERIODS ACCT_PERIOD_ID, ORGANIZATION_ID,
PERIOD_NAME, OPEN_FLAG
Select
ORG_ORGANIZATION_
DEFINITIONS
Select
GL_SET_OF_BOOKS Select
Details of Tables Impacted
MTL_TRANSACTION_ACCOUNTS
Mtl_transaction_accounts holds the accounting information for each material transaction in mtl_material_transactions. The summarized details of the transaction are written into this table. The process transfer to GL picks transactions with gl_batch_id of –1 from this table and passes them to gl_interface. When it passes the transactions to the gl_interface it is at this time that the batch is given it’s proper batch number. This is used to overwrite the gl_batch_id and update it.
The transaction_values and base_transaction_values are transferred to the GL_INTERFACE table through the period close or GL transfer process, and until this process is run, the gl_batch_id is -1.
Form For This Table
Navigation: Inventory/Transactions/ Material Distribution or in the
Material Transactions form click on button “Distribution”. This is to view transaction distribution against different accounts.
Distribution of Accounts
In this table, the gl_batch_id will have a value of (-1) until the records have been posted to the General Ledger. The transaction_id is the primary key and it will match the transaction_id in the MTL_MATERIAL_TRANSACTIONS table. However, every record in the MTL_MATERIAL_TRANSACTIONS table will have at least two records in the mtl_transaction_accounts for asset items. One record will represent credit against an account and one record will represent debit against an account. For expense items there will be no records in this table.
Once the records have been transferred to the General Ledger we cannot identify the corresponding record in the mtl_transaction_accounts table.
If customer is out of balance, confirm the reference_account is not (-1). The join between records in the GL_INTERFACE table to records in the mtl_transaction_accounts table is by doing the GL Transfer in detail mode instead of summary mode. When using detail mode the gl transfer will insert the GL_BATCH_ID into the reference23 column in the GL_INTERFACE table. The same batch id will exist in mtl_transaction_accounts table for the gl_batch_id column. When performing gl transfer process in summary the reference23 field is not updated; therefore, no link will be available between these two tables. Expense items get posted to GL through Purchasing.
Check for records in the following tables:
WIP_MOVE_TXN_INTERFACE, WIP_COST_TXN_INTERFACE
Records not processing out of these tables will stop valuable costing information from WIP transactions.
Material Account Distribution Report should reflect balancing total of what gets posted to General Ledger.
WIP_TRANSACTION_ACCOUNTS
WIP_TRANSACTION_ACCOUNTS stores the accounting information
for each resource transaction in wip_transactions. The process Transfer to GL picks transactions with a gl_batch_id of –1 from this table and passes them to gl_interface. When it passes the transactions to the GL_INTERFACE it is at this time that the batch is given it’s proper batch number. This is used to overwrite the gl_batch_id and update it.
The transaction_value and base_transaction_value are transferred to the gl_interface table through period close or GL transfer (INTCGL) process. Until this process is run, the gl_batch_id is set to -1. Oracle Work in Process uses the information in this table to track the financial impact of resource transactions.
ORG_GL_BATCHES
ORG_GL_BATCHES holds history rows for every batch of transactions that have been interfaced to the gl_interface table. When GL Transfer or Period Close function is run, a new row is inserted into this table, using the next gl_batch_id number. This row contains the transfer date and the acct_period_id from org_acct_periods.
The gl_batch_id column in the accounting transaction tables is updated with this gl_batch_id, and this id prevents the accounting transaction from being interfaced twice to the gl_interface table.
Upon launching the GL Transfer function, the gl_batch_id is set to 0, which represents "Processing" status. The gl_batch_id is set to a positive number when successfully completed and to a negative number when failure occurs.
For Oracle Inventory, the accounting table is mtl_transaction_accounts and for Oracle Work In Process, the accounting table is wip_transaction_accounts.
ORG_ACCT_PERIODS
ORG_ACCT_PERIODS holds the open and closed financial periods for organizations. Transfer to GL program process gets the first acct period id, which is not closed, from this table.
The Transfer to GL program transfers the accounting info from the first accounting period to the accounting period passed in as input parameter.
MTL_PARAMETERS
This table maintains a set of defaults and controls like general ledger
accounts; locator, lot, and serial controls; inter-organization options;
costing method; etc. for each organization defined in Oracle Inventory.
Transfer to GL process looks at the value of the column general_ledger_update_code to determine whether summarized or detailed accounting information is to be transferred for an organization.
Possible column values are:
1. Detail records
2. Summary records
3. ‘No Transfer’ Understanding Transfer Transactions to GL Page 8
TRANSFER TO GL FLOWCHART
Begin Check flag general_ledger_update_code in mtl_parameters to determine whether to insert summary record or detail records into GL_INTERFACE table
• Get the gl_batch_id from org_gl_batches_s sequence
• Get the group_id from gl_interface_control_s sequance Create a batch record in org_gl_batches for the combination of organization_id/
acct_priod_id/gl_date/gl_description with gl_batch_id = 0. This is to indicate that GL transfer has been initiated and no other parallel gl transfer gets initiated by another user for the same input parameters
Get the first acct period id which is not closed,from the ORG_ACCT_PERIODS
table for the input acct_period_id passed to GL TRANSFER program
Get the currency information,chart of accounts and set of book information for inventory organization
A
Understanding Transfer Transactions to GL Page 9
A A For each of the accounting period id, get the start date and the end date
• Mark the batch of transactions with gl_batch_id for all the records in the mtl_transaction_accounts for each acct_period_ids start date and end date
Mark the batch of transactions with gl_batch_id for all the records in the wip_transaction_accounts for each acct_period_ids start date and end date
•Insert summary or detail data of inventory activities in GL_INTERFACE table for the above records in the mtl_transaction_accounts
• Inserting summary or detail data of working in process (wip) in GL_INTERFACE for the above records in the wip_transaction_accounts
Update gl_batch_id of the batch record in org_gl_batches with the actual value These records can now be imported to final GL tables using import to GL program
End
Understanding Transfer Transactions to GL Page 10
RUNNING ‘TRANSFER TO GL’ PROCESS
Transfer Transactions to GL process can be run from Oracle Inventory • Accounting Close Cycle .. General Ledger Transfers.
The log file would provide the Group ID for the transfer. This group Id can be used to query the records inserted into gl_interface owing to this transfer. This group_id can then be used as a parameter to import these records into General Ledger (Nav: Oracle General Ledger > Journals > Import > Run).
To view the details of the transfer, Navigate to Accounting Close Cycle. View General Ledger Transfers. Query the GL transfers based on appropriate criteria in the find screen to view the transfer details. This would give details of the transfer including: Processing Status, Transfer Date, GL Date, GL Batch, Period. This screen can be used further to drill down to transaction distributions.
Note the GL Batch Id from the field ‘GL Batch’. This gl_batch_Id can be
used to view the number of records from mtl_transaction_accounts &
wip_transaction_accounts transferred to gl_interface through this transfer.
When the ‘Transfer Transactions to GL’ process is run with ‘Transfer to GL’ as ‘Detail’ and more than one period is open, the accounting information from the earliest open period up to the Transfer Date is transferred to GL. In this case, the Accounting Date is the same as the Transaction Date of the transaction. If the value of Transfer to GL is ‘Summary’ and more than one period is open and the Transfer Date is in the later period, the transfer process assigns the period end date of the respective period as the Accounting Date for all the summarized accounting transactions in that period. For the last period, the process assigns the Transfer date as the Accounting Date to all the summarized accounting transactions.
The ability to run the ‘Transfer Transactions to GL’ process anytime during an open period provides an option to run it regularly before the Period End. Reconciliation of many issues can be done before the period ends leading to a smoother period close. This practice, would also quicken the period close, especially if detailed entries are to be transferred to GL. This is because transfer of a lot of records would have already occurred and relatively fewer records would need to be transferred during the period end.
Even before closing the period, it is suggested, if time permits, to run the GL Transfer process up to the period end date. This makes it possible to check the transactions and if necessary make adjustments by means of new inventory transactions in the same period. This is helpful as it is not possible to reopen a closed inventory period.
DIAGNOSTICS
Note 206576.1 - Diagnostic Test: Inventory (INV) Period Close Check
Diagnostics
This diagnostic test checks through the entire mandatory and recommended requirements necessary to allow a successful Inventory period month end close.
This test should be run prior to month-end close. It will gather and display information that would prevent a month end close for a given Inventory period. This enables easy identification of known month end close issues along with detail information on how to resolve these issues.
The report format can be created in summary or detail.
Error and warning messages, followed by suggested actions, identify possible problem areas.
Note 246467.1 - BDEprdcls.sql - Script for Checking Pending
Transactions
The script is used for identifying any pending or uncosted transactions and providing pertinent information. The transactions are identified by organization and period.
Note 246467.1 - CstCheck.sql – Cost Diagnostics Script
TOOLS
Note 102878.1 - Pending Transactions Guide
There are a variety of reasons for pending transactions. This paper will serve as a guide for troubleshooting and resolving pending transaction issues, which are preventing you from closing an inventory accounting period.
CONCLUSION
‘Transfer to GL’ process can be run at any time during an open period as a concurrent program or as a part of the period end process. When the process is run with more than one periods open, all the transactions from the earliest open period to the date up to which the process is run (Transfer Date) are considered for Transfer to GL. The transfer involves update/insert on four main tables gl_interface, org_gl_batches, mtl_transaction_accounts & wip_transaction_accounts. The accounting data can then be imported into General Ledger using ‘Journal Import’ program.
Organization Code An inventory organization, for which the gl transfer has to be done.
Date A date in any open period. When more than one period is open, the transfer selects
transactions from the first open period up to this entered transfer date.
For summary transfers, this entered date is assigned as the date for summarized transactions for that open period. Current Period This value would default from the ‘Date’ parameter above. This is the open period containing the above date. Description Some appropriate text.
Technical Overview
The ‘Transfer Transactions to GL’ concurrent program calls the executable INCTGL which resides in the $INV_TOP/bin directory.
Following are the main tables accessed by the program:
GL_INTERFACE GROUP_ID, REFERENCE21, REFERENCE22,
ACCOUNTING_DATE,
USER_JE_CATEGORY_NAME,
GL_SL_LINK_TABLE
Insert
MTL_TRANSACTION_ACCOUNTS
GL_BATCH_ID , TRANSACTION_ID,
TRANSACTION_DATE
Select/Update
WIP_TRANSACTION_ACCOUNTS
GL_BATCH_ID , TRANSACTION_ID,
TRANSACTION_DATE
Select/Update
MTL_PARAMETERS GENERAL_LEDGER_UPDATE_CODE Select
ORG_GL_BATCHES GL_BATCH_ID, GL_BATCH_DATE,
ACCT_PERIOD_ID, ORGANIZATION_ID
Insert/Delete/Update
ORG_ACCT_PERIODS ACCT_PERIOD_ID, ORGANIZATION_ID,
PERIOD_NAME, OPEN_FLAG
Select
ORG_ORGANIZATION_
DEFINITIONS
Select
GL_SET_OF_BOOKS Select
Details of Tables Impacted
MTL_TRANSACTION_ACCOUNTS
Mtl_transaction_accounts holds the accounting information for each material transaction in mtl_material_transactions. The summarized details of the transaction are written into this table. The process transfer to GL picks transactions with gl_batch_id of –1 from this table and passes them to gl_interface. When it passes the transactions to the gl_interface it is at this time that the batch is given it’s proper batch number. This is used to overwrite the gl_batch_id and update it.
The transaction_values and base_transaction_values are transferred to the GL_INTERFACE table through the period close or GL transfer process, and until this process is run, the gl_batch_id is -1.
Form For This Table
Navigation: Inventory/Transactions/ Material Distribution or in the
Material Transactions form click on button “Distribution”. This is to view transaction distribution against different accounts.
Distribution of Accounts
In this table, the gl_batch_id will have a value of (-1) until the records have been posted to the General Ledger. The transaction_id is the primary key and it will match the transaction_id in the MTL_MATERIAL_TRANSACTIONS table. However, every record in the MTL_MATERIAL_TRANSACTIONS table will have at least two records in the mtl_transaction_accounts for asset items. One record will represent credit against an account and one record will represent debit against an account. For expense items there will be no records in this table.
Once the records have been transferred to the General Ledger we cannot identify the corresponding record in the mtl_transaction_accounts table.
If customer is out of balance, confirm the reference_account is not (-1). The join between records in the GL_INTERFACE table to records in the mtl_transaction_accounts table is by doing the GL Transfer in detail mode instead of summary mode. When using detail mode the gl transfer will insert the GL_BATCH_ID into the reference23 column in the GL_INTERFACE table. The same batch id will exist in mtl_transaction_accounts table for the gl_batch_id column. When performing gl transfer process in summary the reference23 field is not updated; therefore, no link will be available between these two tables. Expense items get posted to GL through Purchasing.
Check for records in the following tables:
WIP_MOVE_TXN_INTERFACE, WIP_COST_TXN_INTERFACE
Records not processing out of these tables will stop valuable costing information from WIP transactions.
Material Account Distribution Report should reflect balancing total of what gets posted to General Ledger.
WIP_TRANSACTION_ACCOUNTS
WIP_TRANSACTION_ACCOUNTS stores the accounting information
for each resource transaction in wip_transactions. The process Transfer to GL picks transactions with a gl_batch_id of –1 from this table and passes them to gl_interface. When it passes the transactions to the GL_INTERFACE it is at this time that the batch is given it’s proper batch number. This is used to overwrite the gl_batch_id and update it.
The transaction_value and base_transaction_value are transferred to the gl_interface table through period close or GL transfer (INTCGL) process. Until this process is run, the gl_batch_id is set to -1. Oracle Work in Process uses the information in this table to track the financial impact of resource transactions.
ORG_GL_BATCHES
ORG_GL_BATCHES holds history rows for every batch of transactions that have been interfaced to the gl_interface table. When GL Transfer or Period Close function is run, a new row is inserted into this table, using the next gl_batch_id number. This row contains the transfer date and the acct_period_id from org_acct_periods.
The gl_batch_id column in the accounting transaction tables is updated with this gl_batch_id, and this id prevents the accounting transaction from being interfaced twice to the gl_interface table.
Upon launching the GL Transfer function, the gl_batch_id is set to 0, which represents "Processing" status. The gl_batch_id is set to a positive number when successfully completed and to a negative number when failure occurs.
For Oracle Inventory, the accounting table is mtl_transaction_accounts and for Oracle Work In Process, the accounting table is wip_transaction_accounts.
ORG_ACCT_PERIODS
ORG_ACCT_PERIODS holds the open and closed financial periods for organizations. Transfer to GL program process gets the first acct period id, which is not closed, from this table.
The Transfer to GL program transfers the accounting info from the first accounting period to the accounting period passed in as input parameter.
MTL_PARAMETERS
This table maintains a set of defaults and controls like general ledger
accounts; locator, lot, and serial controls; inter-organization options;
costing method; etc. for each organization defined in Oracle Inventory.
Transfer to GL process looks at the value of the column general_ledger_update_code to determine whether summarized or detailed accounting information is to be transferred for an organization.
Possible column values are:
1. Detail records
2. Summary records
3. ‘No Transfer’ Understanding Transfer Transactions to GL Page 8
TRANSFER TO GL FLOWCHART
Begin Check flag general_ledger_update_code in mtl_parameters to determine whether to insert summary record or detail records into GL_INTERFACE table
• Get the gl_batch_id from org_gl_batches_s sequence
• Get the group_id from gl_interface_control_s sequance Create a batch record in org_gl_batches for the combination of organization_id/
acct_priod_id/gl_date/gl_description with gl_batch_id = 0. This is to indicate that GL transfer has been initiated and no other parallel gl transfer gets initiated by another user for the same input parameters
Get the first acct period id which is not closed,from the ORG_ACCT_PERIODS
table for the input acct_period_id passed to GL TRANSFER program
Get the currency information,chart of accounts and set of book information for inventory organization
A
Understanding Transfer Transactions to GL Page 9
A A For each of the accounting period id, get the start date and the end date
• Mark the batch of transactions with gl_batch_id for all the records in the mtl_transaction_accounts for each acct_period_ids start date and end date
Mark the batch of transactions with gl_batch_id for all the records in the wip_transaction_accounts for each acct_period_ids start date and end date
•Insert summary or detail data of inventory activities in GL_INTERFACE table for the above records in the mtl_transaction_accounts
• Inserting summary or detail data of working in process (wip) in GL_INTERFACE for the above records in the wip_transaction_accounts
Update gl_batch_id of the batch record in org_gl_batches with the actual value These records can now be imported to final GL tables using import to GL program
End
Understanding Transfer Transactions to GL Page 10
RUNNING ‘TRANSFER TO GL’ PROCESS
Transfer Transactions to GL process can be run from Oracle Inventory • Accounting Close Cycle .. General Ledger Transfers.
The log file would provide the Group ID for the transfer. This group Id can be used to query the records inserted into gl_interface owing to this transfer. This group_id can then be used as a parameter to import these records into General Ledger (Nav: Oracle General Ledger > Journals > Import > Run).
To view the details of the transfer, Navigate to Accounting Close Cycle. View General Ledger Transfers. Query the GL transfers based on appropriate criteria in the find screen to view the transfer details. This would give details of the transfer including: Processing Status, Transfer Date, GL Date, GL Batch, Period. This screen can be used further to drill down to transaction distributions.
Note the GL Batch Id from the field ‘GL Batch’. This gl_batch_Id can be
used to view the number of records from mtl_transaction_accounts &
wip_transaction_accounts transferred to gl_interface through this transfer.
When the ‘Transfer Transactions to GL’ process is run with ‘Transfer to GL’ as ‘Detail’ and more than one period is open, the accounting information from the earliest open period up to the Transfer Date is transferred to GL. In this case, the Accounting Date is the same as the Transaction Date of the transaction. If the value of Transfer to GL is ‘Summary’ and more than one period is open and the Transfer Date is in the later period, the transfer process assigns the period end date of the respective period as the Accounting Date for all the summarized accounting transactions in that period. For the last period, the process assigns the Transfer date as the Accounting Date to all the summarized accounting transactions.
The ability to run the ‘Transfer Transactions to GL’ process anytime during an open period provides an option to run it regularly before the Period End. Reconciliation of many issues can be done before the period ends leading to a smoother period close. This practice, would also quicken the period close, especially if detailed entries are to be transferred to GL. This is because transfer of a lot of records would have already occurred and relatively fewer records would need to be transferred during the period end.
Even before closing the period, it is suggested, if time permits, to run the GL Transfer process up to the period end date. This makes it possible to check the transactions and if necessary make adjustments by means of new inventory transactions in the same period. This is helpful as it is not possible to reopen a closed inventory period.
DIAGNOSTICS
Note 206576.1 - Diagnostic Test: Inventory (INV) Period Close Check
Diagnostics
This diagnostic test checks through the entire mandatory and recommended requirements necessary to allow a successful Inventory period month end close.
This test should be run prior to month-end close. It will gather and display information that would prevent a month end close for a given Inventory period. This enables easy identification of known month end close issues along with detail information on how to resolve these issues.
The report format can be created in summary or detail.
Error and warning messages, followed by suggested actions, identify possible problem areas.
Note 246467.1 - BDEprdcls.sql - Script for Checking Pending
Transactions
The script is used for identifying any pending or uncosted transactions and providing pertinent information. The transactions are identified by organization and period.
Note 246467.1 - CstCheck.sql – Cost Diagnostics Script
TOOLS
Note 102878.1 - Pending Transactions Guide
There are a variety of reasons for pending transactions. This paper will serve as a guide for troubleshooting and resolving pending transaction issues, which are preventing you from closing an inventory accounting period.
CONCLUSION
‘Transfer to GL’ process can be run at any time during an open period as a concurrent program or as a part of the period end process. When the process is run with more than one periods open, all the transactions from the earliest open period to the date up to which the process is run (Transfer Date) are considered for Transfer to GL. The transfer involves update/insert on four main tables gl_interface, org_gl_batches, mtl_transaction_accounts & wip_transaction_accounts. The accounting data can then be imported into General Ledger using ‘Journal Import’ program.
No comments:
Post a Comment