R12 Advance Global Intercompany System

R12 Advanced Global intercompany System (AGIS) is used to managing complex requirements for intercompany accounting. 

R12 introduces the new product Advanced Global Intercompany System (AGIS), which takes forward the features provided by GIS in 11i GL and adds a number of important new capabilities. The major new capabilities are:

• Creation of Documentation (ie. Payables and Receivables Invoices) for AGIS transactions
• Web ADI Integration to facilitate uploading transactions from Excel
• One Sender to many receivers in the same transaction batch (the receivers can be in different ledgers, with different chart of accounts, currencies, periods etc)
• Integration with Oracle Approvals Manager for fully flexible approval rules with no coding
• Introduction of Intercompany Periods, which can be opened and closed by AGIS transaction type
• New user grants based security so many subsidiaries can be accessed from the same responsibility
• Online Reconciliation reporting tool, allows drilldown from account balances to the transactions. Uses XML Publisher to provide export to Excel and customization of outputs

The Intercompany balancing feature in GL is now under the AGIS product and has some new features

• Define Intercompany Payables and receivabls accounts by Legal Entity
• Define separate Payables and Receivables accounts for each direction of Intercompany trading relationships
• Intercompany Balancing performed in the subledger when transactions are accounted by SLA

An important point to note is that although we add a lot of new features any GIS set up you have in 11i will be upgraded and you can keep running as in 11i and start taking advntage f the new features if and when you are ready and it makes sense to you.

Oracle standard reports used to reconcile intercompany accounting.

Auto Cash Rule Sets in Receivables

Define AutoCash Rule Sets to determine the sequence of AutoCash Rules that Post QuickCash uses to update your customer's account balances. You specify the sequence and the AutoCash Rules for each AutoCash Rule Set. The AutoCash Rule Sets you define display as list of values choices in the Customer Profile Classes window.
You assign an AutoCash Rule Set to a customer credit profile class in the Customer Profile Classes window. Receivables provides a default AutoCash Rule Set when you assign a customer to a credit profile, but you can modify individual AutoCash Rule Set assignments at both the customer and customer site levels. If you do not assign an AutoCash Rule Set to a customer's credit profile, and you enter a receipt for this customer, Receivables uses the AutoCash Rule Set that you entered in the System Options window along with the number of Discount Grace Days you specified in this customer's credit profile to determine this receipt application. If you assign an AutoCash Rule Set to a customer, but none of the AutoCash Rules apply, Receivables enters the remaining amount as either Unapplied or On-Account.
If you have set up your system to use bank charges and a tolerance limit, Post QuickCash will also consider these amounts if the current AutoCash rule fails (this is true for all rules except 'Apply to the Oldest Invoice First'). If it finds a match, Post QuickCash applies the receipt; otherwise, it looks at the next rule in the sequence. For more information, s
You can disable an existing AutoCash Rule Set by changing its status to Inactive and then saving your work.

   To define an AutoCash Rule set:

1. Navigate to the AutoCash Rule Sets window.

2. Enter the Name of this AutoCash rule set.

3. Enter a description for this AutoCash rule set (optional).

4. Enter the type of Discount you want to automatically give to your customer for this AutoCash Rule Set. Choose one of the following Discount options:

Earned Only: Your customer can take earned discounts according to the receipt terms of sale. You negotiate earned discount percentages when you define specific receipt terms. You can enter this option if Unearned Discounts is set to Yes in the System Options window. In this case, Receivables only allows earned discounts for this AutoCash Rule Set.

Earned and Unearned: Your customer can take both earned and unearned discounts. An unearned discount is one taken after the discount period passes. You cannot choose this option if the system option Unearned Discounts is set to No.

None: Your customer cannot take discounts (this is the default).

5. To include items in dispute when calculating your customer's open balance, check the Items in Dispute check box.

6. To include finance charges when calculating your customer's open balance, check the Finance Charges check box.

7. Define the Automatic Matching Rule for this AutoCash Rule set.

8. If this rule set will include the Apply to the Oldest Invoice First rule, choose how you want to apply any Remaining Remittance Amount. Receivables uses this value to determine how to enter the remaining amount of the receipt if none of the AutoCash Rules within this rule set apply. Choose 'Unapplied' to mark remaining receipt amounts as Unapplied. Choose 'On-Account' to place remaining receipt amounts On-Account.

9. To automatically apply partial receipts when using the Apply to the Oldest Invoice First rule, check the Apply Partial Receipts check box. A partial receipt is one in which the receipt minus the applicable discount does not close the debit item to which this receipt is applied.

The applicable discount that Receivables uses for this rule depends upon the value you entered in the Discounts field for this AutoCash Rule Set. If you exclude finance charges (by setting Finance Charges to No) and the amount of your receipt is equal to the amount of the debit item to which you are applying this receipt minus the finance charges, Receivables defines this receipt as a partial receipt. In this case, Receivables does not close the debit item because the finance charges for this debit item are still outstanding.

If Apply Partial Receipts is set to No, this AutoCash Rule Set will not apply partial receipts and will either mark the remaining receipt amount 'Unapplied' or place it on-account, depending on the value you entered in the Remaining Remittance Amount field (see step 8).

10. Enter a Sequence number to specify the order of each rule in this AutoCash Rule Set (optional). Receivables uses the rule assigned to sequence 1, then sequence 2, and so on when applying receipts using this AutoCash Rule Set.

11. Enter one or more AutoCash Rules for this AutoCash rule set. Choose from the following AutoCash rules:

Apply to the Oldest Invoice First: This rule matches receipts to debit items starting with the oldest debit item first. This rule uses the transaction due date when determining which transaction to apply to first. This rule uses the values you specified for this AutoCash Rule Set's open balance calculation to determine your customer's oldest outstanding debit item.

Post QuickCash uses the next rule in the set if any of the following are true:

• all of your debit items are closed

• the entire receipt amount is applied

• it encounters a partial receipt application and Allow Partial Receipts is set to No for this AutoCash Rule Set

• the next oldest debit item includes finance charges and Finance Charges is set to No for this AutoCash Rule Set

This rule marks any remaining receipt amount 'Unapplied' or places it on-account, depending on the value you entered in the Remaining Remittance Amount field for this AutoCash Rule set (see step 8).

Clear the Account: Post QuickCash uses this rule only if your customer's account balance exactly matches the amount of the receipt. If the receipt amount does not exactly match this customer's account balance, Post QuickCash uses the next rule in the set. This rule calculates your customer's account balance by using the values you specified for this AutoCash Rule Set's open balance calculation and the number of Discount Grace Days in this customer's profile class. This rule also includes all of this customer's debit and credit items when calculating their account balance. This rule ignores the value of the Partial Payments option.

This AutoCash Rule uses the following equation to calculate the open balance for each debit item:

Open Balance = Original Balance + Finance Charges - Discount

Receivables then adds the balance for each debit item to determine the customer's total account balance. The 'Clear the Account' rule uses this equation for each invoice, chargeback, debit memo, credit memo, and application of an Unapplied or On-Account receipt to a debit item.

Note: The discount amount for each item depends upon the payment terms of the item and the value of the Discounts field for this AutoCash Rule Set. The number of Discount Grace Days in this customer's credit profile, along with the payment terms assigned to their outstanding invoices, determine the actual due dates of each debit item.

Clear Past Due Invoices: This rule is similar to the 'Clear the Account' rule because it applies the receipt to your customer's debit and credit items only if the total of these items exactly matches the amount of this receipt. However, this rule only applies the receipt to items that are currently past due. A debit item is considered past due if its due date is earlier than the receipt deposit date. This rule considers credit items (i.e. any pre-existing, unapplied receipt or credit memo) to be past due if the deposit date of the receipt is either the same as or later than the deposit date of this pre-existing receipt or credit memo. In this case, this rule uses a pre-existing receipt or credit memo before the current receipt for your AutoCash receipt applications.

If this AutoCash Rule Set's open balance calculation does not include finance charges or disputed items, and this customer has past due items that are in dispute or items whose balances include finance charges, this rule will not close these items. This rule ignores the value of the Partial Payments option.

Clear Past Due Invoices Grouped by Payment Term: This rule is similar to the 'Clear Past Due Invoices' rule, but it first groups past due invoices by their payment term, and then uses the oldest transaction due date within the group as the group due date. When using this rule, Receivables can only apply the receipt if the receipt amount exactly matches the sum of your customer's credit memos and past due invoices.

A debit item is considered past due if the invoice due date is earlier than the deposit date of the receipt you are applying. For credit memos, Receivables uses the credit memo date to determine whether to include these amounts in the customer's account balance. For example, if you are applying a receipt with a receipt date of 10-JAN-93, credit memos that have a transaction date (credit memo date) on or earlier than 10-JAN-93 will be included. Credit memos do not have payment terms, so they are included in each group.

Match Payment with Invoice: This rule applies the receipt to a single invoice, debit memo, or chargeback that has a remaining amount due exactly equal to the receipt amount. This rule uses the values that you enter for this AutoCash Rule Set's open balance calculation to determine the remaining amount due of this customer's debit items. For example, if Finance Charges is No for this rule set and the amount of this receipt is equal to the amount due for a debit item minus its finance charges, this rule applies the receipt to that debit item. If this rule cannot find a debit item that matches the receipt amount, Post QuickCash looks at the next rule in the set. This rule ignores the value of the Partial Payments option.

12. Save your work.

Receivables Application Rule Sets

 Application Rules Sets window to review existing and define new application rule sets. Application rule sets specify the default payment steps for your receipt applications and how discounts affect the open balance for each type of associated charges.
By defining your own application rule set, you can determine how Receivables reduces the balance due for a transaction's line, tax, freight, and finance charges.
Receivables provides the following application rules:

• Line First - Tax After: Apply to the open line item amount first. Apply any remaining amount in the following order: tax, freight, and then finance charges.

• Line First - Tax Prorate: Apply a proportionate amount to the open line item amount and the open tax amount for each line. Apply any remaining amount to freight and then to finance charges.

• Prorate All: Apply a proportionate amount to the line, tax, freight, and finance charges.

   To define an application rule set:

1. Navigate to the Application Rule Sets window.

2. Enter a Name and Description for this rule set.

3. Enter the Sequence number for this application rule. Receivables applies payments in this sequence, beginning with the lowest sequence number.

Note: You cannot enter a sequence number for the Overapplication rule. By default, this rule is last in the sequence for each application rule set.

4. Enter an application Rule. Each rule will correspond to a line type (for example, lines, freight, or charges), so you should give your rule a descriptive name. Each rule set must have at least one application rule.

Attention: Receivables automatically assigns the Overapplication rule to each application rule set. You cannot delete this rule. The Overapplication rule applies any remaining amount after the balance due for each item has been reduced to zero. If the transaction type of the debit item allows overapplication, this rule prorates the remaining amount between each line and its associated tax amount, making these amounts negative. If the transaction type does not allow overapplication, you can either place the remaining amount on-account or leave it 'Unapplied'.

5. Enter Rule Details for this application rule. This section indicates the type of charges and the tax handling for this rule. Choose a Type of Line, Freight, or Charges. You need to enter at least one type for your rule set.

6. If you chose a Type of 'Line', choose a Tax Treatment. Choose one of the following:

Prorate: Choose this option to proportionately reduce the net amount of the line and associated tax amounts.

Before: Choose this option to first reduce the open tax amount, then apply any remaining amount to the line.

After: Choose this option to reduce the open line amount, then apply any remaining amount to the associated tax.

Note: The default Tax Treatment for your Freight and Charges types is None. This option ignores tax, since you cannot tax freight and charges in Receivables. You cannot choose None for your Line type.

7. To automatically adjust this line type to account for any rounding corrections within this rule set, check the Rounding Correction check box. When an amount is prorated among several line types, Receivables must use one of the line types to account for the rounding adjustment. Each application rule set must have one rounding correction line type.

Suggestion: Assign the Rounding Correction to the line type that is usually the largest portion of your invoices. By doing this, the rounding correction will have the least effect on the overall remaining and applied amounts for this line type.

8. Repeat the previous steps for each rule you want to add to this rule set.

9. Save your work.

10. When you are satisfied with this rule set definition, check the Freeze check box. Receivables verifies that your application rule set is defined properly and that it does not violate any basic application guidelines. If this rule set fails validation, Receivables displays an error message. In this case, modify your rule set definition, then check the Freeze check box again to revalidate it.

Attention: A rule set must be 'frozen' before you can assign it to a transaction type or use it as your default rule it in the System Options window. Additionally, after you freeze an application rule set, you cannot update or delete it.

Lock Box Setup in Oracle

Define lockboxes to use the Receivables AutoLockbox program. AutoLockbox automatically creates receipts in Receivables using electronic information that your bank provides. Receivables lets you specify the payment method for each lockbox you define. Payment methods provide the default accounting information for receipts you create through AutoLockbox.
Receivables displays active lockboxes as list of values choices in the Submit Lockbox Processing window.

To define a lockbox:

1. Navigate to the Lockboxes window.

2. Enter the lockbox Number provided by your bank.

3. Enter the receipt Batch Source for this lockbox. You must enter a batch source that uses automatic numbering. Receivables enters the bank name and account, address, contact person, and accounting flexfield information associated with this batch source.

4. Enter the Bank Origination Number provided by your bank. This number uniquely identifies the bank branch that sends you lockbox information.

5. Open the Receipts alternative region, then enter the Batch Size you want the Lockbox Validation program to assign to each receipt batch. For example, if you have 991 receipts, and you set Batch Size to 10, Receivables will create 99 batches with 10 receipts and 1 batch with 1 receipt. If you do not want Receivables to separate your lockbox batch into multiple receipt batches, enter a number that is larger than the number of receipts in your lockbox transmission for this lockbox, then check the Complete Batches Only check box in the Submit Lockbox Processing window when you submit your lockbox transmission. 

6. Enter your GL Date Source. This source determines the general ledger date for your receipts in this lockbox. Choose from the following sources:

• Constant Date: Receivables uses the date you enter in the GL Date field of the Submit Lockbox Processing window. If you do not enter a date when you choose Constant Date, Receivables does not validate your data.

• Deposit Date: Receivables uses the date that your bank deposits your receipts. If you choose this source and the lockbox transmission's deposit date is not defined, Receivables displays an error message indicating that you must define a deposit date to submit the lockbox.

• Import Date: Receivables uses the date on which you import your receipts.

7. If you are using this lockbox to transfer foreign currency receipts and you did not specify exchange rate type in the bank file, enter an Exchange Rate Type.

8. Enter the Receipt Method to assign to this lockbox. The default is the payment method associated with the receipt batch source you entered.

9. If you want AutoLockbox to be able to transfer receipts without billing locations into Receivables, uncheck the Require Billing Location check box. If this box is checked, AutoLockbox will only validate the receipt if the billing location is provided; otherwise, Lockbox will import and validate these receipts successfully.

Attention: If the system option Require Billing Location for receipts is set to Yes, this option should also be set to Yes for your Lockbox. If the system option is set to Yes but it is set to No for your Lockbox, Receivables displays an error message when you submit AutoLockbox. The setting at the system options level determines whether Post QuickCash can process receipts without billing locations.

10. Choose a Match Receipts By method. Lockbox uses this value to determine what type of matching numbers will be used in this transmission. Choose one of the following methods:

• Transaction Number: Match receipts with transaction numbers.

• Consolidated Billing Number: Match receipts with consolidated billing invoice numbers. To use this method, both the user profile option AR: Show Billing Number and the Send Consolidated Billing Invoice option for this customer must be set to Yes. 

Lockbox uses the Consolidated Billing Invoice number to identify the customer. Post QuickCash then uses this customer's AutoCash Rule Set to determine how to apply the receipt to each invoice. For more information, refer to the 'Clear Past Due Invoices Grouped by Payment Term' rule

• Sales Order: Match receipts with sales order numbers. Lockbox uses this number to determine the corresponding invoice number.

Note: Receivables allows more than one sales order number per invoice because different invoice lines can be generated from different sales orders. Therefore, this method is valid even if other lines on the same invoice reference different sales orders.

• Purchase Order: Match receipts with purchase order numbers. Lockbox uses this number to determine the corresponding invoice number.

Note: Receivables allows more than one invoice per sales order or purchase order. If you choose a Match Receipt By method of Sales Order or Purchase Order, Lockbox will match with the first invoice that it finds.

• Hook: Match receipts to any other type of matching number that is passed with this transmission. This is a custom matching method that you define. Lockbox uses this number to determine the corresponding invoice number

11. Choose whether to Match on Corresponding Date for transactions in this Lockbox transmission. The matching date will correspond to either the transaction, sales order, purchase order, or consolidated billing invoice date, depending on the Match Receipts By method you choose. Choose one of the following:

• Always: Always verify that the date for the transaction or other matched item is the same as the date specified in this transmission.

• Duplicates Only: Only verify that the matching date and the specified date are the same if duplicate matching numbers were found and Lockbox needs to determine which is correct.

• Never: Ignore the specified date. This is the default value.

Suggestion: If you have customers that match receipts using different methods and either Allow Payment of Unrelated Invoices is Yes for this Lockbox submission or AutoAssociate is Yes for this Lockbox, set Match on Corresponding Date to Always. Because different customers can have transactions with the same number, setting the Match on Corresponding Date option to Always ensures that Lockbox will check both the transaction number and date before matching it with a receipt.

12. If you do not want the Lockbox Validation program to use the debit item number to determine a customer, open the Transactions alternative region, uncheck the Auto Associate check box. By default, the Lockbox Validation program uses an invoice or debit memo number to determine the customer with which the receipt should be associated (if there is no customer information or MICR number in your Lockbox transmission).

13. Choose how this Lockbox will handle remaining receipt amounts that could not be applied because of invalid transaction numbers. For example, your receipt record indicates that Lockbox should apply the receipt to several invoices, but one of the invoices is invalid. Depending on how you set this option, Lockbox will:

• Post Partial Amount as Unapplied: Apply the receipt to the valid transactions, then import the remaining receipt amount with a status of Unapplied. You can then manually apply the receipt to the invalid transaction using the Applications window.

• Reject Entire Receipt: Do not import the receipt (it will remain in the AR_PAYMENTS_INTERFACE table). You need to edit the invalid record(s) in the Lockbox Transmission Data window, then resubmit the Validation step for the receipt before Lockbox can import it into Receivables.

14. Save your work.

Oracle General Ledger FAQ

Oracle General Ledger 

1. How to reference SLA tables

GL SL Link Id from GL Interface table is unique column number generated and is available in XLA tables has reference

2. What is Group ID used for?

The Group ID distinguishes data to import within a particular source, i.e. Oracle Receivables, Payables or other subledgers / legacy systems.
Since minipack 11i.GL.D (or with Patch: 1455528) the Journal Import Submission form can select "All Group Ids" from a particular source.

The same does not happen for Standard Report Submission for Journal Import , where a specific group_id (or null group_id) must be specified.

When a subledger does not populate the group_id (null) then it may occur that different batches from the same source are merged by the first Journal Import process meeting the criteria.
If the Journal Import is automatically submitted by the subledger, and several users are transferring to GL at the same time, then some of the processes may get the 'No Data Found' error, because the expected lines were already imported by the first process. This does not happen if the group_id is used.

 Can Reversals be automatically generated?

Journal Import does not automatically create reversing journal entries.

If the reversal flag and reversal period were populated in the GL_INTERFACE table, then the reversal must be generated after Journal Import is run.

In 11i the Autoreversal procedure can be setup to create reversing Journals automatically once an imported journal is created.

3. How is the Effective Date derived?

Journal Import will store the value for the DEFAULT_EFFECTIVE_DATE in GL_JE_HEADERS picked from one of the accounting dates in GL_INTERFACE lines.
It will be the first line that appears on the journal under that header.
This is decided by the values in the code combination and is arbitrary from a financial point of view although treated consistently for each journal header in a batch.

All the journal headers within a batch may not have the same default effective date when imported in SUMMARY mode but all the lines will match the header for that journal.
If you wish the lines to have different effective dates then do not use the Create Summary Journals option.

Exceptions:
If you use Average Daily Balances (ADB) this behaviour is different.
If you have the new profile option: GL Journal Import: Separate Journals by Accounting Date, journal import will only group together journal lines that have the same date, while lines with different effective dates will be put under different journal headers even if they belong to the same period.
This is available on 11.5.10 family pack 11i.FIN_PF.E.

4. How is the GL Batch Name derived?

The batch name uses the first 50 characters from REFERENCE1 in GL_INTERFACE (if populated) followed by:
 - Source
 - Request ID
 - Actual Flag
 - Group ID
In Consolidation journals, the profile option GL Consolidation: Preserve Journal Batching set to Yes will preserve up to 50 characters of the original batch name plus batch ID in the source set of books to the target set of books.

5. How is the Period determined?

Journal Import selects the period corresponding to the ACCOUNTING_DATE populated in GL_INTERFACE.

Then the imported lines are grouped by period and the Journal Entry is created with the ACCOUNTING_DATE of the last calendar day of the period.

Prior to 11i.GL.F the contents of the PERIOD_NAME column in the GL_INTERFACE table is ignored, therefore it is not possible to import for adjusting periods.
After 11i.GL.F the behavior has changed to allow Journal Import to import data into adjustment periods, for that purpose the PERIOD_NAME is used in combination with the ACCOUNTING_DATE.

Journal Import groups lines with the same Period into the same journal, even if the lines have different Accounting Dates, for all sets of books except:
- the set of books is an Average Daily Balances set of books
- the profile option GL Journal Import: Separate Journals by Accounting Date is set to YES

In these cases it would put lines with different Accounting Dates into separate journals.

6. How to import journals for Adjusting Periods?

Before 11i.GL.F it is not possible to import journals directly for adjusting periods. The selected period is the regular period corresponding to the effective date calculated.

The workaround for this is to import for a regular period and, before posting, use Change Period function on the Enter Journals form.

Since 11i.GL.F the behavior has changed to allow Journal Import to import data into adjustment periods and also to import data that is to be reversed into adjustment periods.
For budgets the PERIOD_NAME column in the GL_INTERFACE table will now be able to hold both adjustment and non-adjustment periods.
For actual and encumbrance data the period that the data will be imported to will now be controlled by a combination of the ACCOUNTING_DATE and the PERIOD_NAME:
- if a valid period name is specified and that period contains the accounting date then that period is used;
- otherwise the non-adjusting period that contains the accounting date is used
The REFERENCE8 column in the GL_INTERFACE table will now be able to hold both adjusting and non-adjusting reversal periods or reversal date for Average Balance data.

7. Subledger transactions were transferred but are missing in GL

The transfer from the subledger could be successful, but the journals may have errored out in Journal Import, leaving the transactions in GL_INTERFACE table.

Check the Journal Import Execution report for any possible validation errors and the Journal Import log file for execution errors.
In that case the journals would not be seen neither in the Posting form nor the Enter Journals form, but would be available on the Journal Import Correct form.

You can also check the contents of the GL_INTERFACE table by running the simple SQL in Note:77684.1.
If the transactions are in GL_INTERFACE then you need to correct them and re-run Journal Import . See Note:1056801.6 for more information.

If the journals are still not found, verify that the responsibility you are using does not have security rules that would prevent you from viewing the data.

See also Note:330821.1 Journal Import Troubleshooting Guide

8. Create Summary Journals: what is the advantage of using it?

Importing journals using this run option selected will summarize all transactions for the same period, account and currency, into one debit/credit journal line.

This will make your reports more manageable in size, but you lose the one to one mapping of your detail transactions to the summary journal lines created by Journal Import.
You can still maintain a mapping of how Journal Import summarizes your detail transactions from your feeder systems into journal lines, if the Journal Source definition has the Import Journal References option checked.

9. Can Descriptive Flexfield be Imported?

Yes. Descriptive Flexfields can be imported with or without validation.
The Descriptive Flexfields that can be imported via Journal Import are the ones that map to the reference fields of the GL_JE_LINES table.

There are 3 options for Descriptive Flexfields (DFF) import:
- No: DFF information will be ignored by GL.
- With Validation: Journal Import generates journals only if DFF are valid.
- Without Validation: the DFF information is imported into GL as it is, without any validation.

See Note:1062015.6 and Note:115983.1 for more information.

10. Rollback Segment: can this be modified?

There is no option available for Journal Import to choose the Rollback Segment.
There is a setup option available for the Number Of Lines to Process at Once that affects Journal Import.

Navigation: Setup > System >Controls.

11. Can Journal Import be Automated or included in a request set?

The Journal Import Program (GLLEZLSRS) is a new functionality included in 11.5.10 which allows the submission of journal import from the standard report submission screen like any normal concurrent process.
Therefore it can also be scheduled or included in a Report Set.
However it is currently limited to specific or NULL group_ids, the 'All Group IDs' option does not exist. See Note:277891.1 and Note:371189.1 for more information.

It is also possible to use the CONCSUB utility to submit a Journal Import from outside of the application and create batch jobs to automatically run the Journal Import. However this process needs to receive, as a parameter, the value of the GL_INTERFACE_CONTROL.INTERFACE_RUN_ID column.

For more information on CONCSUB see the Oracle Applications System Administrator's Guide.
See also Note:1079972.6, Note:198041.1 for more information .

12. How to improve Performance?

There may be different causes for a bad performance in Journal Import process.

If you have a general poor performance in Journal Import then please follow the recommendations from Note:198437.1 How to Improve Journal Import Performance and Performance of Other GL Programs in 11i

If the performance problem only occurs for high volume batches then review Note:294959.1 GLLEZL: Problems Importing Very Large Journal Batches

Specific patches effecting Journal Import Performance:
Patch:1455528: Multi-Table Journal Import
Patch:2608405: GLLEZL SIGNAL 11 When a GROUP_ID has a huge number of lines in GL_INTERFACE
Patch:2717598: The records with the STATUS 'PROCESSED' in GL_INTERFACE remain forever...
Patch:3087842: The records with the STATUS 'PROCESSED' in GL_INTERFACE remain
Patch:3535059: APPSPERF:GL: GLLEZL not using BIND variables

13. Where to check for the Error Messages (Status codes)?

The Journal Import automatically generates the Journal Execution Report with information about the created journals.
If validation errors are detected then the exception lines with the respective error codes are also listed. The final section of the report is a complete list of validation error codes and their meanings.
See Note:1056801.6 for a list of these errors.

If Patch: 2162483: Enhancement to provide Warning Statuses for Journal Import, is installed then Journal Import will end in a warning status in the following cases:
- When no data matching the specified criteria is found in the GL_INTERFACE table.
- When data is not imported successfully.
- When data is imported with a warning.

Some unexpected execution errors may also occur. Those are shown in the log file and the process is terminated in error.

14. Can lines be deleted from GL_INTERFACE?

Yes, lines from GL_INTERFACE can be deleted.
However, this procedure is not recommended, as data originating in the subledger / feeder system may be lost or no longer retrievable.
The Correct Journal Import Data form should be used to correct Journal Import errors. You should also refer back to the subledgers where the data originated.
This form can only be used to delete batches with lines that have already been processed and rejected in error. If no Group ID is selected only lines with GROUP_ID as NULL will be deleted.

15. How to check the contents of the GL_INTERFACE table?

Run the sql statement below for a summary of the GL_INTERFACE contents.
It will give an idea of the batches waiting to be imported.

select
 set_of_books_id, user_je_source_name, actual_flag, group_id,
 period_name, status, request_id, sum(nvl(accounted_dr,0)),
 sum(nvl(accounted_cr,0)), count(*)
from gl_interface
group by set_of_books_id, user_je_source_name,
 actual_flag, group_id, period_name, status, request_id

See Note:77684.1 for more information

16. How to correct Validation Errors in the GL_INTERFACE table?

Validation errors usually mean that the data populated in the interface table is incorrect, for instance an undefined period, or that General Ledger is not ready to receive those journals, for instance the receiving period is not Open.

See Note:1056801.6 for a list of these errors.

The corrective actions to fix the errors may depend on the error code and on the source of the transactions.

The Correct Journal Import Data form can be used to manually correct the lines with a status error in GL_INTERFACE table. However this does not fix the data in the subledger / feeder system.

See Note:330821.1 Journal Import Troubleshooting Guide, for more information

17. What are the primary GL tables updated by Journal Import?

The primary GL tables populated during Journal Import are:

- GL_JE_BATCHES
- GL_JE_HEADERS
- GL_JE_LINES
- GL_IMPORT_REFERENCES (link to source transactions)
- GL_INTERFACE_HISTORY (optional)
- GL_BC_PACKETS (budgetary control)

18. Are Cross-Validation or Security Rules validated?

Journal Import does not check for Security Rules. It is the feeder system that must validate the account code combinations populated in GL_INTERFACE table.

You can also populate the accounting segments directly into the gl_interface table and let Journal Import populate the code_combination_id. If dynamic insertion is enabled, and this is a new combination, then the import program will check for cross validation rule violations.

Verify data between Inventory and GL through SLA transactions

 GL transfer is done through SLA so the GL_SL_LINK_ID in GL would be matching with

the GL_SL_LINK_ID of SLA tables and not of MTL_TRANSACTION_ACCOUNTS.
2) To compare the value of MTA- SLA - GL customer can do the following :

Make sure that you run the create accounting for the
period and also transferred to GL.

/*------- Verify that all MTA txns transferred to SLA -----------
If the following query returns any rows then that means there are
accounting in MTA which has not been created in SLA---------------*/

SELECT *
FROM mtl_transaction_accounts a
WHERE a.transaction_date between TO_DATE('&&from_dt','DD-MM-YYYY')
AND TO_DATE('&&to_dt','DD-MM-YYYY')+0.99999
AND a.reference_account =&&Account_id
AND a.organization_id = &&organization_id
AND NOT EXISTS (SELECT NULL
FROM xla_distribution_links
WHERE source_distribution_type =
'MTL_TRANSACTION_ACCOUNTS'
AND application_id = 707
AND source_distribution_id_num_1 =
a.inv_sub_ledger_id)




/*-------- Verify MTA values matches that transferred to SLA ----
If the following query returns any rows then that means there are
accounting in MTA which has been created in SLA but the value in SLA
doesn't match with MTA--------------------------------------------*/

SELECT SUM(NVL(l.accounted_dr,0) - NVL(l.accounted_cr,0)) SLA_VAL,
SUM(a.base_transaction_value) MTA_VAL,
l.ae_header_id,
l.ae_line_num,
l.code_combination_id
FROM mtl_transaction_accounts a,
xla_distribution_links b,
xla_ae_lines l
WHERE a.transaction_date between TO_DATE('&&from_dt','DD-MM-YYYY')
AND TO_DATE('&&to_dt','DD-MM-YYYY')+0.99999
AND a.reference_account =&&Account_id
AND a.organization_id = &&organization_id
AND b.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
AND b.application_id = 707
AND b.source_distribution_id_num_1 = a.inv_sub_ledger_id
AND l.application_id =707
AND l.ae_header_id = b.ae_header_id
AND l.ae_line_num = b.ae_line_num
GROUP BY l.code_combination_id,
l.ae_header_id,
l.ae_line_num,
l.code_combination_id
HAVING (SUM(NVL(l.accounted_dr,0) - NVL(l.accounted_cr,0))
- SUM(a.base_transaction_value)) <> 0

/* -- SLA marked as transferred to GL but don't exist in GL ------
If the following query returns any rows then that means there are
accounting in SLA which has not been created in GL---------------*/

SELECT l.ae_header_id,
l.gl_sl_link_id,
l.gl_sl_link_table,
l.code_combination_id,
SUM(NVL(l.accounted_dr,0) - NVL(l.accounted_cr,0)) SLA_VAL
FROM xla_ae_lines l,
xla_ae_headers h,
xla_distribution_links b,
mtl_transaction_accounts a
WHERE a.transaction_date between TO_DATE('&&from_dt','DD-MM-YYYY')
AND TO_DATE('&&to_dt','DD-MM-YYYY')+0.99999
AND a.organization_id = &&organization_id
AND b.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
AND b.application_id = 707
AND b.source_distribution_id_num_1 = a.inv_sub_ledger_id
AND l.ae_header_id = b.ae_header_id
AND l.ae_line_num = b.ae_line_num
AND l.application_id=h.application_id
AND l.ae_header_id=h.ae_header_id
AND h.application_id = 707
AND h.ledger_id= &&Ledger_id
AND l.code_combination_id = &&Account_id
AND h.gl_transfer_status_code ='Y'
AND h.accounting_entry_status_code='F'
AND h.accounting_date BETWEEN TO_DATE('&&from_dt','DD-MM-YYYY')
AND TO_DATE('&&to_dt','DD-MM-YYYY')+0.99999
AND NOT EXISTS
(SELECT 1 FROM gl_import_references ir
WHERE ir.gl_sl_link_id=l.gl_sl_link_id
AND ir.gl_sl_link_table=l.gl_sl_link_table)
group by l.ae_header_id,
l.gl_sl_link_id,
l.gl_sl_link_table,
l.code_combination_id;

Once you have detected that all distributions from MTA are posted to XLA.

The reconciliation problem is between XLA and GL.

To check the balance from GL, user should use GL account balance inquiry.