Oracle EBS Currency Balance Types

The ETL process extracts four different currency balance types from Oracle EBS, namely base currency, translated currency, entered currency, and statistical balance. These records are loaded and stored in W_GL_BALANCE_F table in the Oracle Data Warehouse.
The following table shows sample records of the different balance types. All four records are from ledger 'US Ledger' with U.S. dollars (USD) as the base ledger currency. These five columns in the following table are columns in the physical data warehouse table:

• LOC_CURR_CODE
  
• ACCT_CURR_CODE
  
• TRANSLATED_FLAG
  
• BALANCE_LOC_AMT
  
• BALANCE_ACCT_AMT
  

Line	Ledger Name	LOC_CURR_CODE	ACCT_CURR_CODE	TRANSLATED_FLAG	BALANCE_LOC_AMT	BALANCE_ACCT_AMT	Balance Type Description
1	US Ledger	USD	NULL	N	200	0	A Base Currency Balance record with base ledger currency of USD
2	US Ledger	USD	GBP	Y	0	100	A Translated Currency Balance record with translation currency of GBP (British pounds). USD is the base ledger currency.
3	US Ledger	USD	JPY	N	0	10000	An Entered Currency Balance record with entered currency in JPY (USD is the base ledger currency)
4	US Ledger	USD	STAT	N	0	150	A Statistical balance amount

Users can query these balances by using different value columns and filters. The following tables contain sample queries that you can use to view different currency balance types and their corresponding results from the sample warehouse data in the preceding table.

Note:

Ledger 'US Ledger' corresponds to LEDGER_WID = 85228 in the following sample scenarios.

Sample	Desired Queries	Physical Table Value Column	Physical Query Filters	Result for Value Column
A	To query: balance amount for base currency of USD, in ledger 'US Ledger'	BALANCE_LOC_AMT	LEDGER_WID = 85228	200
B	To query: translated balance amount for translated currency of GBP, in ledger 'US Ledger'	BALANCE_ACCT_AMT	LEDGER_WID 85228 AND TRANSLATED_FLAG = 'Y' AND ACCT_CURR_CODE = 'GBP'	100
C	To query: entered balance amount for entered currency of JPY, in ledger 'US Ledger'	BALANCE_ACCT_AMT	LEDGER_WID = 85228 AND ACCT_CURR_CODE = 'JPY' AND TRANSLATED_FLAG = 'N'	10000
D	To query: statistical balance amounts, in ledger 'US Ledger'	BALANCE_ACCT_AMT	LEDGER_WID = 85228 AND ACCT_CURR_CODE = 'STAT'	150

Oracle BI Answers users can create reports on currency balance types by selecting the desired facts from the Financials - GL Balance Sheet presentation table, as shown in the following table.

Sample Scenario	Desired Report	Presentation Table	Presentation Column	Result in Report
A	View the local currency balance amounts (Debit Local Amount), such as in sample query A in the preceding table	Ledger Facts – GL Balance	Ledger name Debit Local Amount	Apply filter on: "Ledger Name" = 'US Ledger' Ledger Name = US Ledger Debit Local Amount = 200
B	View the translated currency balance amounts (Debit Translated Amount), such as in sample query B in the preceding table	Ledger Facts – GL Balance GL Balance Details	Ledger Name Debit Translated Amount Translated Currency Code	Apply filters on: "Ledger Name" = 'US Ledger' "Translated Currency Code" = 'GBP' Ledger Name = US Leger Debit Translated Amount = 100 Translated Currency Code = GBP
C	View the entered currency balance amounts (Debit Entered Amount), such as in sample query C in the preceding table	Ledger Facts – GL Balance GL Balance Details	Ledger Name Debit Entered Amount Entered Currency Code	Apply filters on: "Ledger Name" = 'US Ledger' "Entered Currency Code" = 'JPY' Ledger Name = US Leger Debit Entered Amount = 10000 Entered Currency Code = JPY
D	View the statistical amount (Statistical Amount), such as in sample query D in the preceding table	Ledger Facts - Balance Sheet Statement	Ledger Name Statistical Amount	Ledger Name = US Leger Statistical Amount = 150

Specify the Ledger or Set of Books for which GL Data is Extracted

If you have an Oracle EBS source system, you can specify from which set of books or ledgers you extract the GL data.
Oracle BI Applications enables you to configure:

• The list of ledgers or set of books from which to extract the GL data.
  
• The types of ledgers or set of books for which to extract the GL data.
  

You can use either of these configuration points separately and combine them. When installed by default, Oracle BI Applications extract all GL data for all ledgers or set of books.
For Oracle 11i customers, to configure the list of sets of books for which to extract the GL data, do the following:

1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
   
2. Display the Source System Parameters tab.
   
3. Query for the parameter $$FILTER_BY_SET_OF_BOOKS_ID.
   
4. Double-click the Value column to open the text box, and enter 'Y'.
   Make sure to put single quotes around the Y.
   
5. Save the changes.
   
6. Query for the parameter $$SET_OF_BOOKS_ID_LIST.
   
7. Double-click the Value column to open the text box, and enter the IDs of the sets of books for which you want to extract GL data.
   Specify the list of sets of book IDs separated by commas (do not enter single or double quotes).
   For example, if you want to extract GL data for sets of books with IDs: 101, 207, and 303, then set the value of this parameter to the following:
   

   101, 207, 303
   

8. Save the changes.
   

You can also specify the Set of Books type for which you want to extract GL data. In Oracle 11i, there are three set of books types (this is based on the column GL_SETS_OF_BOOKS.mrc_sob_type_code):

• P (Parent)
  
• R (Reporting)
  
• N (None)
  

For Oracle 11i customers, to configure the types of set of books to extract the GL data, do the following:

1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
   
2. Display the Source System Parameters tab.
   
3. Query for the parameter $$FILTER_BY_SET_OF_BOOKS_TYPE.
   
4. Double-click the Value column to open the text box, and enter the value 'Y'.
   Make sure to put single quotes around the Y.
   
5. Save the changes.
   
6. Query for the parameter $$SET_OF_BOOKS_TYPE_LIST.
   
7. Double-click the Value column to open the text box, and enter the types of set of books for which you want to extract GL data.
   Specify the list of set of book IDs in single quotes and separated by commas.
   For example, if you want to extract GL data for all Parent set of books and all Reporting set of books, then set the value of this parameter as follows:
   

   'P', 'R'
   

8. Save the changes.
   

For Oracle EBS R12 customers, to configure the list of ledgers to extract the GL data for, do the following:

1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
   
2. Display the Source System Parameters tab.
   
3. Query for the parameter $$FILTER_BY_LEDGER_ID.
   
4. Double-click the Value column to open the text box, and enter the value 'Y'.
   Make sure to put single quotes around the Y.
   
5. Save the changes.
   
6. Query for the parameter $$LEDGER_ID_LIST.
   
7. Double-click the Value column to open the text box, and enter the IDs of ledgers for which you want to extract GL data for.
   Specify the list of ledger IDs separated by commas (do not use single quotes).
   For example, if you want to extract GL data for ledgers with IDs: 101, 207, and 303, then set the value of this parameter as follows:
   

   101, 207, 303
   

8. Save the changes.
   

You can also specify types of ledgers you want to extract GL data for. In Oracle EBS R12, there are four types of ledgers (this is based on the column GL_LEDGERS.ledger_category_code):

• PRIMARY
  
• SECONDARY
  
• ALC
  
• NONE
  

For R12 customers, to configure the types of ledgers to extract the GL data for, do the following:

1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
   
2. Display the Source System Parameters tab, and query for the parameter $$FILTER_BY_LEDGER_TYPE.
   
3. Double-click the Value column to open the text box, and enter the value 'Y'.
   Make sure to put single quotes around the Y.
   
4. Save the changes.
   
5. Query for the parameter $$LEDGER_TYPE_LIST.
   
6. Double-click the Value column to open the text box, and enter the types of ledgers for which you want to extract GL data for.
   Specify the list of set of book IDs in single quotes and separated by commas.
   For example, if you want to extract GL data for all Primary ledgers and all Secondary ledgers, then simply set the value of this parameter to as follows:
   

   'PRIMARY', 'SECONDARY'
   

7. Save the changes.
   

Enable Project Analytics Integration with Financial Subject Areas

You can enable Oracle Financial Analytics for EBS to use dimension tables in Oracle Project Analytics. You can only perform this integration if you have licensed Oracle Project Analytics.
You can configure the following Oracle Financial Analytics for EBS Subject Areas to use Project Analytics for EBS tables:

• Financials -Payables
  
• Financials -Receivables
  
• Financials - Cost of Goods Sold
  
• Financials - Revenue
  

The following Oracle Financial Analytics fact tables integrate with Project Analytics dimensions:

• W_AP_INV_DIST_F
  
• W_AP_XACT_F
  
• W_AR_XACT_F
  
• W_GL_COGS_F
  
• W_GL_REVN_F
  
• W_AP_AGING_INVOICE_A
  
• W_AR_AGING_INVOICE_A
  

To enable the integration:

1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
   
2. Select each Subject Area, and then in the Configuration Tags tab, clear the Inactive check box for the Enable Project Dimensions configuration tag.
   
3. Assemble the Subject Areas.
   
4. Click the Execute button and build the Execution Plan for the Subject Areas that you updated.
   

Configure the AR Credit-Memo Application Extract for Oracle Receivables Analytics

By default, Oracle Receivables Analytics extracts only confirmed, credit-memo application entries against accounts receivable transactions. Confirmed credit memos are entries where the AR_RECEIVABLE_APPLICATIONS_ALL.CONFIRMED_FLAG = Y OR NULL. If you want to extract additional types of AR credit-memo application entries, you can remove the filter. By modifying or removing the filter, you can extract other entries such as unconfirmed, credit memos.
You must modify both the regular mapplet (mplt_BC_ORA_ARTransactionFact_CreditmemoApplication), as well as the primary extract mapplet (mplt_BC_ORA_ARTransactionFact_CreditmemoApplicationPrimary). Repeat the following procedure for each mapplet.
To modify the extract filter for Accounts Receivable Credit-Memo Application:

1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORAVersion_Adaptor).
   
2. In Mapplet Designer, open the mplt_BC_ORA_ARTransactionFact_CreditmemoApplication mapplet.
   
3. Double-click the Source Qualifier to open the Edit Transformations dialog, and display the Properties tab.
   In the User Defined Join field and in the SQL Query field, modify the statement:
   

   AND NVL(AR_RECEIVABLE_APPLICATIONS_ALL.CONFIRMED_FLAG,'Y') = Y 
   

4. Validate and save your changes to the repository.
   
5. Repeat Step 2 to Step 4 for the mplt_BC_ORA_ARTransactionFact_CreditmemoApplicationPrimary mapplet.
   

Configure the AR Cash Receipt Application Extract for Oracle Receivables Analytics

By default, Oracle Receivables Analytics extracts only confirmed, cash-receipt application entries against accounts receivable transactions. Confirmed receipts are entries where the AR_RECEIVABLE_APPLICATIONS_ALL.CONFIRMED_FLAG = Y OR NULL. If you want to extract additional types of cash-receipt application entries, you can remove the filter in the Business Component mapplet. By modifying or removing the filter, you can extract other entries, such as unconfirmed applications.
You must modify both the regular mapplet (mplt_BC_ORA_ARTransactionFact_ReceivableApplication) as well as the primary extract mapplet (mplt_BC_ORA_ARTransactionFact_ReceivableApplicationPrimary).
To modify the extract filter for AR cash receipt application:

1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORAVersion_Adaptor).
   
2. In Mapplet Designer, open the mplt_BC_ORA_ARTransactionFact_ReceivableApplication mapplet.
   
3. Double-click the Source Qualifier to open the Edit Transformations dialog, and display the Properties tab.
   In the User Defined Join field and in the SQL Query field, modify the statement:
   

   AND NVL(AR_RECEIVABLE_APPLICATIONS_ALL.CONFIRMED_FLAG,'Y') = Y
   

4. Validate and save your changes to the repository.
   
5. Repeat Step 2 to Step 4 for the mplt_BC_ORA_ARTransactionFact_ReceivableApplicationPrimary mapplet.
   

Configure the AR Schedules Extract

By default, Oracle Receivables Analytics extracts only completed schedules; that is, transactions where the RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG(+) = Y. If you want to extract additional types of AR schedule entries, you must remove the filter in the Business Component mapplet. By modifying or removing the filter, you can extract other entries, such as those that were marked as incomplete.
To modify the extract filter for Accounts Receivable schedules:

1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORAVersion_Adaptor).
   
2. In Mapplet Designer, open the mplt_BC_ORA_ARTransactionFact_ARSchedules mapplet.
   
3. Double-click the Source Qualifier to open the Edit Transformations dialog.
   In the User Defined Join field and in the SQL Query field, modify the statement:
   

   AND RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG(+) = Y
   

4. Validate and save your changes to the repository.
   

Configure the AR Adjustments Extract for Oracle Receivables Analytics

By default, Oracle Receivables Analytics extracts only approved adjustment entries against accounts receivable transactions. Approved adjustments are entries where the AR_ADJUSTMENTS_ALL.STATUS = A. If you want to extract additional types of AR adjustment entries, you can remove the filter in the Business Component mapplet. By modifying or removing the filter, you can extract other entries, such as those that require more research, those that are rejected, and those that are not accrued charges.
To modify the extract filter for Accounts Receivable adjustments:

1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORAVersion_Adaptor).
   
2. In Mapplet Designer, open the mplt_BC_ORA_ARTransactionFact_Adjust mapplet.
   
3. Double-click the Source Qualifier to open the Edit Transformations dialog, and display the Properties tab.
   In the SQL Query field and in the User Defined Join field, modify the statement:
   

   AND AR_ADJUSTMENTS_ALL.STATUS = A
   

4. Validate and save your changes to the repository.
   
5. Repeat Step 2 to Step 4 for the mplt_BC_ORA_ARTransactionFact_AdjustPrimary mapplet.
   

Configure AR Balance ID for Oracle Receivables Analytics and Oracle General Ledger and Profitability Analytics

The AR Balance ID controls the level at which the balance in W_AR_BALANCE_F is maintained.
By default, the AR Balance ID is maintained at the following granularity:

set_of_books_id || '~' || code_combination_id || '~' || customer_id || '~' ||
customer_site_use_id || '~' transaction_currency_code || '~' || org_id

However, if you want to maintain your AR balance at a different grain, you can redefine the Balance ID value in the applicable mapplets.
To modify the AR Balance ID:

Note:

To modify the AR Balance ID, you must modify the following mapplets:

• mplt_SA_ORA_ARTransactionFact_Adjust
  
• mplt_SA_ORA_ARTransactionFact_ARScheduleDerive
  
• mplt_SA_ORA_ARTransactionFact_CreditMemoApplication
  
• mplt_SA_ORA_ARTransactionFact_ReceivableApplication
  

1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORAVersion_Adaptor).
   
2. In Mapplet Designer, open the mapplet (for example, mplt_SA_ORA_ARTransactionFact_Adjust).
   
3. Double-click the Expression transformation to open the Edit Transformations dialog.
   
4. Display the Ports tab, and edit the expression for the EXT_NU_AR_BALANCE_ID port.
   For example, you might click the Expression field to display the Expression Editor and modify the text in the Formula box.
   
5. Validate and save your changes to the repository.
   
6. Repeat steps 1 to 5 for each mapplet that is listed above.
   

Configure AP Balance ID for Oracle Payables Analytics

The AP Balance ID controls the level at which the balance in W_AP_BALANCE_F is maintained. This section contains configuration information for Oracle Payables Analytics that is specific to Oracle. By default, the Accounts Payable (AP) Balance ID is maintained at the following granularity:

SET_OF_BOOKS_ID||'~'||CODE_COMBINATION_ID||'~'||VENDOR_SITE_ID||'~'||
ORG_ID||'~'||VENDOR_ID

However, if you want to maintain your AP balance at a different grain, you can redefine the Balance ID value in the applicable mapplets.
To modify the Accounts Payable Balance ID:

Note:

To modify the Accounts Payable Balance ID, you must modify the following mapplets:

• mplt_SA_ORA_APTransactionFact_LiabilityDistribution
• mplt_SA_ORA_APTransactionFact_ExpenseDistribution
• mplt_SA_ORA_APTransactionFact_Payment

1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORAVersion_Adaptor).
2. In Mapplet Designer, open the mapplet (for example, mplt_SA_ORA_APTransactionFact_LiabilityDistribution).
3. Double-click the Expression transformation to open the Edit Transformations dialog.
4. Display the Ports tab, and edit the expression for the EXT_BALANCE_ID port.
   For example, you might click the Expression field to display the Expression Editor and modify the text in the Formula box.
5. Validate and save your changes to the repository.
6. Repeat steps 1 to 5 for each mapplet that is listed above.
   

Configure Oracle Profitability Analytics Transaction Extracts

There are two separate transaction extracts for Oracle General Ledger and Profitability Analytics—General Ledger Revenue and General Ledger COGS. By default, the Oracle General Ledger Analytics application extracts only Completed revenue and COGS that have been posted to the general ledger. Completed revenue transactions are those where the RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG = Y. If you want to extract incomplete revenue transactions, you can remove the filter in the Business Component.
To modify the extract filter for Oracle Profitability Analytics Revenue:

1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORAVersion_Adaptor).
2. In Mapplet Designer, open the mplt_BC_ORA_GLRevenueFact mapplet.
3. Double-click the Source Qualifier to open the Edit Transformations dialog, and display the Properties tab.
   In the User Defined Join field and in the SQL Query field, remove the statement:

   AND RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG = Y
   

4. Validate and save your changes to the repository.
   

GL Balances Are Populated in Oracle EBS

Oracle BI Applications provides two ways to populate the GL balances (stored in the W_GL_BALANCE_F table), as follows:

• By extracting the GL balances directly from Oracle General Ledger, as follows:
  1. In DAC, go to the Design view, and then display the Subject Areas tab.
  2. Query for the Subject Area 'Financials – General Ledger'. Display the Configuration Tags subtab, and query for the tag 'Oracle – Extract GL Balance'. Activate the tag by deselecting the Inactive check box.
  3. Inactivate the tag 'Financials – Calculate GL Balance by selecting the is Inactive check box.
  4. Click Assemble to reassemble the Subject Area.
  5. Rebuild the Execution Plans that contain this Subject Area.

• By calculating the GL balances based on the records in the W_GL_OTHER_F table, which stores all journal lines, as follows:
  1. In DAC, go to the Design view, and then display the Subject Areas tab.
  2. Query for the Subject Area 'Financials – General Ledger'. Display the Configuration Tags subtab, and query for the tag 'Financials – Calculate GL Balance'. Activate the tag by deselecting the Inactive check box.
  3. Inactivate the tag 'Oracle – Extract GL Balance' by selecting the is Inactive check box.
  4. Click Assemble to reassemble the Subject Area.
  5. Rebuild the Execution Plans that contain this Subject Area.

Set Up Drill Down in Oracle BI Answers from General Ledger to Subledger

Note:

The Drill Down feature is not supported on Oracle JD Edwards source systems.

Oracle BI Applications enables you to trace a GL Journal to the subledger transaction that created that journal. This ability (or drill down) is achieved through the 'Navigation' feature in Oracle BI Answers.
To set up drill down in Oracle BI Answers from General Ledger to subledger:

1. Create your subledger request from 'Financials - AP Transactions' or 'Financials - AR Transactions' catalog as applicable.
   
2. In your request, add a filter on the column 'GL Journal ID' under the 'Document Details' folder and the set the operator of the filter to 'Is Prompted'.
   
3. Build your GL Journal request from the 'Financials - GL Detail Transactions' catalog.
   
4. To your request, add the column 'GL Journal ID' under the 'Document Details' folder.
   
5. Navigate to the Column Properties of this column, and set the Value Interaction property in the Column Format tab to 'Navigate'.
   
6. Add a navigation target and set the target location to the sub ledger request you created earlier.
   You might add multiple navigation targets if your GL report shows transactions from multiple subledgers and you want to drill from GL to the appropriate Subledger report. For example, if your GL report shows transactions from AP, AR and Revenue, and you have three subledger reports for each of these, you can add three navigation targets (by selecting the option 'Add Navigation Targets') and set the locations to each of these reports. Subsequently, when you run the GL report and when you click the "GL Journal ID" column Value, a popup appears, where you need to click the appropriate target based on the journal you selected. This will not happen automatically. For example, if you click a journal transaction originating from AP, you need to pick the appropriate subledger report (that is, the AP report in this case) to drill into the AP report and see the details. You can add the Group Account Number attribute from GL Account Dimension to your GL report to easily identify the subledger that the GL transaction belongs to.
   

Note:

For COGS, the 'GL Journal ID' column is not exposed in any Subjects Areas. It is available in the Business Model and Mapping layer of the Oracle BI repository under the logical tables 'Dim - GL COGS Details'. As a workaround, you can create Subjects Areas to report of detail level transactions for COGS and expose this column under the 'Document Details' folder in the Subjects Area and use similar steps as above to set up a drill-down from GL to COGS.
To create a Subjects Area for COGS:

1. Using the Administration Tool, open OracleBIAnalyticsApps.rpd.
   The OracleBIAnalyticsApps.rpd file is located at:
   

   ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\
   coreapplication_obisn\repository
   

2. Create an empty Subjects Area (for example, Financials – GL Cost of Goods Sold). Set properties by following other Subjects Areas.
   
3. Drag 'Dim – GL COGS Details' and 'Fact - Fins - GL Cost of Goods Sold Posted' to the Subjects Area.
   
4. Drag other dimensions.
   
5. Rename the presentation table 'Dim – GL COGS Details' to 'Document Details'.
   
6. Rename the presentation table 'Fact - Fins - GL Cost of Goods Sold Posted' to 'Facts - GL Cost of Goods Sold'. Rename other dimensions if necessary.
   

You might also follow this same process to create a presentation table for Revenue to be able to drill from GL to Revenue level detail transactions.

Configure the AP/AR Aging Tables

This section explains how to control the lengths of the aging buckets in the AP and AR aging snapshot tables. These tables are:

• W_AP_AGING_INVOICE_A
  
• W_AR_AGING_INVOICE_A
  
• W_AP_AGING_SUPPLIER_A
  
• W_AR_AGING_CUSTOMER_A
  

In these four tables, outstanding AP/AR balance information is broken out into rows. Each row represents the outstanding balance information for a particular aging bucket. Four aging buckets are provided by default, with the following durations:

• Bucket 1: 0 – 30 days
  
• Bucket 2: 31 – 60 days
  
• Bucket 3: 61 – 90 days
  
• Bucket 4: 90+ days
  

Use DAC to configure the lengths of these aging buckets, as described in the following procedure.
To configure the length of aging buckets:

1. In the Design view, display the Tasks tab.
   
2. Query for the following tasks:
   
   • PLP_APSnapshotInvoiceAging
     
   • PLP_ARSnapshotInvoiceAging
     
3. For each of these tasks, display the Parameters tab in the lower pane and update the parameters with names starting with '$$BUCKET'.
   You can also use the $$HISTORY_MONTHS parameter to specify how many historic month end snapshots you want to retain (the default value is 24 months).
   Note: You cannot create aging snapshots for months prior to your initial load ETL. Aging snapshots are created one month at a time, as you go forward with your regular ETL runs. For example, if you start your system in January 2009 (that is, run the initial ETL some time in January 2009), and you set $$HISTORY_MONTHS to 12, you will have one snapshot at the end of January 2009, two at the end of February 2009, three at the end of March 2009 and so on until the end of December 2009 when you will have 12 snapshots.
   
4. Save your changes.
   

Include Unapplied Payments in AR Aging Calculations

Follow these steps to include Unapplied Payments in Aging Calculations for AR Aging tables:

1. In Informatica Designer, open the mapping PLP_ARSnapshotInvoiceAging in the PLP Folder.
   
2. Edit the Source Qualifier transformation SQ_IA_AR_XACTS, and open the SQL Query in the Properties tab.
   
3. In the WHERE clause of the SQL statement:
   
   1. Change the SQL text from:
      

      W_XACT_TYPE_D.W_XACT_SUBTYPE_CODE IN ('INVOICE','CR MEMO','DR MEMO') AND
      

      To:
      

      W_XACT_TYPE_D.W_XACT_SUBTYPE_CODE IN ('INVOICE','CR MEMO','DR MEMO',
      'PAYMENT') AND
      

   2. Change the SQL text from:
      

      W_XACT_TYPE_D.W_XACT_TYPE_CODE = 'ORIGINAL' AND
      

      To:
      

      W_XACT_TYPE_D.W_XACT_TYPE_CODE IN ('ORIGINAL','PAYMENT') AND
      

4. Save the changes.
   The next ETL will populate the Aging tables using UnApplied payments in the calculations.
   

Configure Transaction Types for Oracle General Ledger and Profitability Analytics

 configure Transaction Types for Oracle General Ledger and Profitability Analytics, Release 12, using the configuration file domainValues_Xact_Types_DocTypes_ora12.csv.

To configure Transaction types for Oracle General Ledger and Profitability Analytics:

1. Identify the entry types in the Oracle Inventory application by using the following SQL:

2.   select mtt.transaction_type_id, mtt.description from mtl_transaction_types mtt;

This query gives the transaction type codes in the Oracle Inventory application and their corresponding descriptions.

3. Open the domainValues_Xact_Types_DocTypes_ora12.csv file using a text editor in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).

Look for only the rows in the file which has XACT_CODE = 'COGS' or XACT_CODE = 'DEFERRED_COGS'. From among these records, look for those with W_XACT_TYPE_CODE = 'DELIVERY' or 'RETURN'. The column XACT_TYPE_CODE will contain the entry type codes from the Oracle Inventory application, and the column W_XACT_TYPE_CODE is the corresponding domain value to which this entry type code will be mapped to.

4. Map the entry type codes returned by the query above from the Oracle Inventory application to the domain values.

Make sure the entry type codes go into the XACT_TYPE_CODE column in the file. Note that it is not necessarily a one-to-one mapping between the domain values and the entry types. For example, you can have multiple entry types mapped into the DELIVERY/RETURN domain value. Be careful not to map the same entry type to two different domain values.

5. Save and close the file.
6. In Informatica PowerCenter Designer, open the mapping SDE_ORA_GLCOGSFact in the folder SDE_ORAVersion_Adaptor.
7. Open the mapplet mplt_BC_ORA_GLCOGSFact contained inside this mapping
8. Open the Source Qualifier SQ_MTL_TRANSACTION_ACCOUNTS.
9. Modify the SQL so that it extracts the additional transaction type IDs, from step 1, that need to be extracted.
10. In Informatica PowerCenter Workflow Manager, make the same change to the SQL Override in the full load session SDE_ORA_GLCOGSFact_Full.