Oracle Cloud offers a broad portfolio of software as a service applications, platform as a service, and social capabilities, all on a subscription basis. Oracle Cloud delivers instant value and productivity for end users, administrators, and developers alike through functionally rich, integrated, secure, enterprise cloud services.
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Subscribe to the OracleAppsHub to receive notifications when there are new posts:

 get RSS feed
 Oracle Fusion Applications (OFA) is a portfolio of next generation suite of software applications from Oracle Corporation. It is distributed across various product families; including financial management, human capital management, customer relationship management, supply chain management, procurement, governance, and project portfolio management
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

No items, feed is empty.

Change Data Capture

Posted on December 7th, 2008 by Sanjit Anand ||Email This Post Email This Post

Typically, the traditional tracking methods provides different levels of change information for data and these are the how how developers typically doing CDC Requirement:

  • Trigger based tracking
  • Time stamp columns
  • Join queries
  • Additional table to track deletes

dgreybarrow What is CDC

Change Data Capture (CDC) is an innovative integration technology, which is based on the identification, capture, and delivery of only the changes made to enterprise data sources.

dgreybarrowWho need CDC

  • BI , because of Increasing demand for up-to-date/real-time data
  • Increasing data volumes
  • Shrinking batch windows
  • Demand for event driven processing
  • Reduce cost may be you can say saving in processing large amounts of data

dgreybarrowWhere to use

  • Data Warehousing & ETL/ELT
  • Operational Reporting and Data Integration
  • Data Replication, Propagation
  • Synchronization of Data and Applications
  • Master Data Management (MDM)
  • Legacy Modernization & Migration

If you are working in Oracle Technology space , then some of event-driven Processing like ESB (OSB), BPEL, CEP, BAM

dgreybarrow What industries need CDC?

CDC need is across the industry. That means this fits any industry. Typical example such as:

  • If you are in Financial Services the best example would be having efficient BI which may be event detection
  • In Manufacturing Typical example can be a real-time synchronization
  • In Retail similar to Financial CDC need must be to provide real-time BI for marketing
  • Govt and Public Sector mostly for efficient DW/BI

Therefore understand CDC requirement in ETL in next section.

dgreybarrowCDC for ELT

CDC typically provide up-to-date data for data warehouses, ODS, reporting databases, etc.

dgreybarrow How this works:

CDC detects new, updated and deleted records

  • When ODI processes are invoked they can be restricted to these records. Less data to process.
  • Allows to run the ODI processes much more often
  • Removes “batch-window” processing
  • Real/Right-time data warehousing

dgreybarrow Major benefits with use of CDC in ETL would be:

  • First is Efficiency
  • Next would be Low latency/Real-Time Integration
  • Lower cost
  • The most important it’s Event driven architecture

Therefore because of number of data acquisition methods, including Change Data Capture, its important now a day for become important for organizations to deal with greater diversity in their data architectures.

Posted in Emerging Technologies, Integration | No Comments »

AR to GL Transfer

Posted on December 6th, 2008 by Sanjit Anand ||Email This Post Email This Post

dgreybarrowOracle Financials Accounts Receivables Transfer Process

The AR Transfer process is a SRS based Concurrent Process which can be used for transfer process from

to GL . This Program knows as ARGLTP.

dgreybarrowPosting Journals to GL

Your Navigation for transfer process would be

Interfaces> General ledger(11i)

AR to GL transfer1

You can run the General Ledger Interface program to transfer Receivables Transaction Accounting Distributions to the GL Interface table (GL_INTERFACE) and create either Detailed Journal batches or summary Journal batches . Receivables lets you create Unposted Journal Entries in GL when you run General Ledger Interface.

Here is five simple step to complete the process:

  1. Navigate to the Run General Ledger Interface window.
  2. Choose the Posting Detail or Summary. Chose Detail in the Posting Detail field. If you transfer transactions in detail, the General Ledger Journal Import Program creates at least one journal entry for each transaction in your posting submission. (If you transfer in summary, it creates one journal entry for each general ledger account)
  3. Enter the GL Posted Date for this submission. The default is the current date, but you can change it. Receivables updates all of the posted transactions that you transfer to the general ledger interface area with the GL posted date you enter.
  4. Enter the range of GL Dates for your submission. The dates must be within both an open receivables period and an open or future General Ledger period. When you enter a start date, the default GL end date is the last day of the period that you entered for the GL start date.
  5. Receivables creates the Posting Execution Report. Use this report to see a summary of transactions that are imported into the GL_INTERFACE table. Transactions that failed validation appear in the Unposted Items Report.

dgreybarrowA note on Posting Execution Report

You can use this report to view a Summary of all Transactions by category and currency that make up your Entries to general ledger.

The good is that AR automatically generates this report when you run General Ledger Interface.

The sum of the entries in the General Ledger Journal Report is equal to the sum of all of the categories of transactions that the Posting Execution Report includes for the same period. The report tells you if posting discovered Errors in your Journals or in your Journal lines.

dgreybarrowVanila Oracle AR Transfer Process : Addressing user Need

AR to General ledger Transfer Program(ARGLTP) does not have any provision to run with specific categories like Invoices, Receipts, etc.

In real time scenarios finance user need to transfer AR’s datas outside the sales invoice category any time. Because The sales invoice category can not be transferred before reconciliation with customer , So user need to transfer other datas outside of sales invoice category, thus they may request you for category options for that you need to modify the General Ledger interface Program by adding some new parameters Category From /Category To.

dgreybarrowWhat Category we have in GL

  • Trade Receipts
  • Misc Receipts
  • Adjustments
  • Sales Invoices
  • Debit Memos
  • Chargebacks
  • Credit Memos
  • CM Applications

Sales Invoices ,Debit Memos ,Chargebacks and Credit Memos are tracked back with Customer Num

CM Applications are tracked backed with Inv Num

Trade Receipts or Misc Receipt are tracked back with Receipt Number.

dgreybarrowConnecting World :Link between GL to AR




When you run AR Transfer to GL , GL_INTERFACE table get first populated with reference columns that is then pushed the detailes in GL_JE_LINES table. Here are the details for Refrence columns.


  • REFERENCE21 :posting_control_id
  • REFERENCE22 :adjustment_id
  • REFERENCE23 :line_id
  • REFERENCE24 :trx_number
  • REFERENCE25 :adjustment_number
  • REFERENCE26 :cust_trx_type
  • REFERENCE27 :bill_to_customer_id
  • REFERENCE29 :source_type prefixed by ‘ADJ’


  • REFERENCE21 :posting_control_id
  • REFERENCE22 :customer_trx_id
  • REFERENCE23 :cust_trx_line_gl_dist_id
  • REFERENCE24 :trx_number
  • REFERENCE25 :cust.account_number
  • REFERENCE27 :bill_to_customer_id
  • REFERENCE29 :type||account_class


  • REFERENCE21 :posting_control_id
  • REFERENCE22 :cash_receipt_id||receivable_application_id for CASH /receivable_application_id for CM
  • REFERENCE23 :line_id
  • REFERENCE24 :receipt_number for CASH / trx_number for CM
  • REFERENCE25 :trx_number if status = ¿APP¿ / NULL for unapplied records
  • REFERENCE26 :cust_trx_type
  • REFERENCE27 :pay_from_customer for CASH / bill_to_customer_id for CM
  • REFERENCE28 :application_type (TRADE or CCURR for CASH / CMAPP for CM)
  • REFERENCE29 :application_type||source_type

Bills Receivable

  • REFERENCE21 :posting_control_id
  • REFERENCE22 :transaction_history_id
  • REFERENCE23 :line_id
  • REFERENCE24 :trx_number
  • REFERENCE25 :customer_Trx_id
  • REFERENCE26 :cust_trx_type
  • REFERENCE27 :drawee_id
  • REFERENCE28 :cust_trx_type
  • REFERENCE29 :BR_||source_type

dgreybarrowKey Tables

In order to obtain what is going to be tranfered to GL you can these Major AR table to drill down information.

  • ra_customer_trx_all: Transactions accounting
  • ra_cust_trx_line_gl_dist_all: Transactions accounting
  • ar_adjustments_all:Adjustments accounting
  • ar_distributions_all:Adjustments accounting
  • ar_cash_receipt_history_all:Receitps accounting
  • ar_distributions_all: Receitps accounting
  • ar_receivable_applications_all: Receipt applications accounting
  • ar_distributions_all:Receipt applications accounting & misc receipts accounting
  • ar_misc_cash_distributions_all:Misc receipts accounting

dgreybarrowQuery for Subledger Transfer to GL

If you want to get details of different journals transferred to GL, use this to get the result. You can also fine tune with period , currency or clearing company code or Journal Type.Here is the query for Transaction.


select    gjjlv.period_name             "Period Name" 
        ,                      "Batch Name" 
        , gjjlv.header_name             "Journal Entry For" 
        , gjjlv.je_source               "Source" 
        ,glcc.concatenated_segments     "Accounts"
        , NVL(gjjlv.line_entered_dr,0)  "Entered Debit" 
        , NVL(gjjlv.line_entered_cr,0)   "Entered Credit" 
        , NVL(gjjlv.line_accounted_dr,0) "Accounted Debit" 
        , NVL(gjjlv.line_accounted_cr,0) "Accounted Credit" 
        , gjjlv.currency_code            "Currency"
        ,                    "Trx type" 
        , rcta.trx_number                "Trx Number" 
        , rcta.trx_date                  "Trx Date"
        , RA.CUSTOMER_NAME               "Trx Reference"
        , gjh.STATUS                     "Posting Status"
        , trunc(gjh.DATE_CREATED)        "GL Transfer Dt"
        , gjjlv.created_by               "Transfer By"
from    apps.GL_JE_JOURNAL_LINES_V gjjlv 
      , gl_je_lines gje
      , gl_je_headers gjh 
      , gl_je_batches gjb
      , ra_customer_trx_all rcta 
      , apps.ra_customers ra 
      , apps.gl_code_combinations_kfv glcc 
      , ra_cust_trx_types_all rctype
where     gjh.period_name IN ('OCT-2008','NOV-2008') 
and       glcc.code_combination_id = gje.code_combination_id 
and       gjh.je_batch_id = gjb.je_batch_id 
and       gjh.je_header_id = gje.je_header_id
and       gjh.period_name = gjb.default_period_name
and       gjh.period_name = gje.period_name 
and       gjjlv.period_name = gjh.period_name 
and       gjjlv.je_batch_id = gjh.je_batch_id 
and       gjjlv.je_header_id = gjh.je_header_id 
and       gjjlv.line_je_line_num  = gje.je_line_num
and       gjjlv.line_code_combination_id = glcc.code_combination_id 
and       gjjlv.line_reference_4 = rcta.trx_number    
and       rcta.cust_trx_type_id = rctype.cust_trx_type_id 
and       rcta.org_id = rctype.org_id 
and       ra.customer_id = rcta.bill_to_customer_id
--and       glcc.segment1 ='30D

Will discuss for Receipt Transfer query in another post.

Posted in Oracle Receivable | 12 Comments »

Inventory Transfer To GL

Posted on December 3rd, 2008 by Sanjit Anand ||Email This Post Email This Post

dgreybarrow-2How Inventory Passes Journals To General Ledger (GL)

Inventory Transactions transfer to GL normally done by Program called INCTGL.

Inventory to GLThe accounting information related to transactions in INV module is transferred to GL by running the ‘Transfer Transactions to GL’ concurrent program. This is part of ‘Period End Process’ . However, you can run explicitly any time during an open period. When you run this process and in case if more than one period is 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.

When you run this program all 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

Each parameter has there own significance.

  1. Organization Code : This is one of inventory organization, for which the gl transfer has to be done.
  2. Date :A date in any open period.
  3. Current Period
  4. GL Description

The possible values for the ‘Transfer to GL’ field (in Organization Parameters Screen) are – Detail, Summary, None.

  • When Summary is selected :The accounting information pertaining to the transactions is summarized and the summary entries are posted to GL. One of the advantage of running the process in ‘Summary’ mode over ‘Detail’ mode is minimising the posting times.
  • When ‘Detail’ is selected :The detailed accounting entries are transferred to GL. In this case, the posting times are larger due to a larger number of records. The advantage of this mode is creation of detailed accounting records in GL for every transaction in Inventory/WIP.
  • When none is selected: This case no transfer of accounting information to GL is done for this organization.

dgreybarrow-2 Running ‘Transfer to GL’ process

You can run Transfer Transactions to GL process can be run from Oracle Inventory

Accounting Close Cycle .. General Ledger Transfers

dgreybarrow-2Technical Details

These are the tables which get affected By transfer Program.

  • GL_INTERFACE : Based out of Program logic, new row inserted into this table. The connecting link are established by these columns.GROUP_ID, REFERENCE21, REFERENCE22,ACCOUNTING_DATE,

    • Action on this table :Insert
  • MTL_TRANSACTION_ACCOUNTS : Mtl_transaction_accounts holds the accounting information for each
    material transaction in mtl_material_transactions.

    • Action on this table :Select/Update
  • WIP_TRANSACTION_ACCOUNTS :This table stores the accounting information for each resource transaction in wip_transactions.
    • Action on this table :Select/Update
  • 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.
    • Action on this table :Select
  • ORG_GL_BATCHES :This table holds history rows for every batch of transactions that have been interfaced to the gl_interface table.
    • Action on this table :Insert/Delete/Update
  • ORG_ACCT_PERIODS :This table holds the open and closed financial periods for organizations.
    • Action on this table :Select
  • ORG_ORGANIZATION_DEFINITIONS : Defination of the Org is derived from this table
    • Action on this table :Select
  • GL_SET_OF_BOOKS : Table holds the setof book data used for joining purpose.
    • Action on this table :Select

dgreybarrow-2Query for Subledger Transfer to GL

If you want to get details of different journals transferred to GL, use this to get the result. You can also fine tune with period , currency or clearing company code or Journal Type.

Here is the query:


SELECT   gjh.period_name            "Period name"
        ,                   "Batch name"
        ,gjjlv.header_name          "Journal entry" 
        ,gjjlv.je_source            "Source"
        ,glcc.concatenated_segments "Accounts"
        ,mmt.subinventory_code      "Subinventory"
        ,glcc3.segment4             "Costcenter"
        ,gjjlv.line_entered_dr      "Entered debit"
        ,gjjlv.line_entered_cr      "Entered credit"
        ,gjjlv.line_accounted_dr    "Accounted debit"
        ,gjjlv.line_accounted_cr    "Accounted credit"
        ,gjjlv.currency_code        "Currency"
        ,mtt.transaction_type_name  "Transaction type" 
        ,mta.transaction_date       "Transaction_date"
        ,msi.segment1               "Reference"
FROM  apps.gl_je_journal_lines_v gjjlv,
      gl_je_lines gje,
      mtl_transaction_accounts mta,
      mtl_material_transactions mmt, 
      mtl_system_items_b msi,
      gl_je_headers gjh, 
      gl_je_batches gjb,
      apps.gl_code_combinations_kfv glcc,
      apps.gl_code_combinations_kfv glcc2, 
      mtl_secondary_inventories msin,
      mtl_transaction_types mtt,
      gl_code_combinations glcc3
WHERE       gjjlv.period_name  BETWEEN 'NOV-2008' AND 'DEC-2008'
AND         gje.code_combination_id = gje.code_combination_id
and         gjjlv.line_je_line_num  = gje.je_line_num
AND         gl_sl_link_table   = 'MTA'
AND         gjjlv.je_header_id = gje.je_header_id
AND         mmt.inventory_item_id = msi.inventory_item_id 
AND         gje.je_header_id = gjh.je_header_id
and         gjh.je_batch_id = gjb.je_batch_id 
and         mmt.organization_id   = msi.organization_id 
and         mmt.organization_id    = msin.organization_id 
and         mmt.subinventory_code= msin.secondary_inventory_name 
and         mta.gl_sl_link_id= gje.gl_sl_link_id 
and         mta.reference_account = glcc.code_combination_id 
and         msin.expense_account = glcc2.code_combination_id 
and         mmt.transaction_id = mta.transaction_id 
and         mtt.transaction_type_id = mmt.transaction_type_id 
and         cost.organization_id(+) = mmt.organization_id 
and         cost.secondary_inventory_name(+) = mmt.subinventory_code
and         glcc3.code_combination_id(+) = 

Posted in Oracle General Ledger | 2 Comments »

Fixed Asset Transfer to GL

Posted on December 1st, 2008 by Sanjit Anand ||Email This Post Email This Post

dgreybarrow How Fixed Assets (FA) Passes Journals To General Ledger (GL)

Before 11.5.9 and Fixed Assets patchset M (11i.FA.M), Journals created by the Create Journals process in FA were entered directly to the journal tables. For some time it was possible to have different period names between the two ledgers and change them using the General Ledger Change Periods feature.

Financials Family pack G or higher all journals to be passed from FA through the gl_interface table using the standard import mechanism (GLLEZL). Initially the import had to be launched from GL separately.Check out old post for R12 SLA Changes.

dgreybarrowHow to generate FA journal entries and transfer to GL?

Oracle FA GL TransferIn FA module, the journals should be generated after asset addition, depreciation,revaluation, adjustment, transfer, and retirement. It is suggested that in end of each month, after run depreciation and close FA module, user should generate journal entries and transfer to GL module.

1. Login to system and switch to FA responsibility, select Other -> Request -> Run, select “Single Request”, click “OK” button.
2. In “Submit Request” form, input “Create Journal Entries” in name field.
3.Input or select the book and period that you want to generate journals entries

dgreybarrow How and what FA Populates into GL

These are the columns which are used for capturing the information from FA to GL.

Convention :GL_INTERFACE table column (GL_LINES table COLUMN) :FA REFERENCE


dgreybarrow Journal categories used while FA to GL Transfer

These are the transaction type used for JE creation in GL.

  • Addition
  • Reinstatement
  • Full Retirement
  • Transfer
  • Partial Retirement
  • Adjustment
  • Unit Adjustment
  • Reclass
  • Depreciation
  • Revaluation

dgreybarrowQuery for Subledger Transfer to GL

If you want to get details of different journals transferred to GL, use this to get the result. You can also fine tune with period , currency or clearing company code or Journal Type.

JE GL Transfer

Here is the query:

select gjjlv.period_name period_name
, batch_name
, gjjlv.header_name Journal_Entry
, gjjlv.je_source Source
, gjjlv.line_entered_dr Entered_Debit
, gjjlv.line_entered_cr Entered_credit
, gjjlv.line_accounted_dr Accounted_Debit
, gjjlv.line_accounted_cr Accounted_Credit
, gjjlv.currency_code Currency
, fasv.TRX_TYPE_NAME Trans_Type
, fasv.TRX_NUMBER_DISPLAYED Transaction_Number
, fasv.TRX_DATE Transaction_Date
, fasv.ASSET_NUMBER Reference
from apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gje
, apps.fa_ael_gl_v fasv
, gl_je_headers gjh
, gl_je_batches gjb
, apps.gl_code_combinations_kfv glcc
where gjh.period_name between 'SEP-2008' and 'OCT-2008'
and glcc.code_combination_id = gje.code_combination_id
and glcc.code_combination_id = fasv.code_combination_id
and gjh.JE_BATCH_ID = gjb.JE_BATCH_ID
and gjh.period_name = gjb.default_period_name
and gjh.period_name = gje.period_name
and gjjlv.period_name = gjh.period_name
and gjjlv.je_batch_id = gjh.je_batch_id
and gjjlv.je_header_id = gjh.je_header_id
and gjjlv.LINE_JE_LINE_NUM = gje.je_line_num
and gjjlv.je_header_id = fasv.je_header_id
AND glcc.segment1='22'

dgreybarrowVerification Report

You can use these Verification Report for your FA and GL monthly Reconcilation.

  1. Cost Detail Report :Use the Cost Detail and Cost Summary reports to reconcile your asset cost accounts to your general ledger to reconcile with Oracle General Ledger, compare the Cost Summary report with the Account Analysis Report.
  2. Asset Retirements Report :Use this report to review the assets you retired for the Book and accounting Period range you choose. The report is sorted by balancing segment, asset type, asset
    account, cost center, and asset number. It prints totals for each cost center, account,asset type, and balancing segment.
  3. Asset Reclassification Reconciliation Report
  4. Asset Transfer Reconciliation Report :Use this report to review asset transfers for the Book and Period you choose. For each transaction Oracle Assets lists the expense account, balancing segment, cost center,and location of the asset before and after the transfer. Oracle Assets sorts the report by
    asset number.
  5. Journal Entry Reserve Ledger Report : This report can be used to review how much depreciation Oracle Assets charged to a depreciation reserve account in an accounting period. The report is sorted by, and prints totals for each balancing segment, asset account, reserve account, and cost center.
  6. CIP Capitalization Report
  7. CIP Assets Report
  8. Unposted Mass Additions Report
  9. CIP Detail Report (If using adding asset through Project)
  10. Asset Addition Report
  11. Cost Adjustment Report

dgreybarrow Important FAQ – that user may ask

1. Are journal entries transferred to the GL in summary or detail format?

Journal entries are summarized to code combination ids within journal categories.

2. Why do I have journal entries for zero dollar amounts (i.e., debit 0, credit 0)?

The expense segment is part of a single distribution row, which also contains the assets owner and location. Changes to any one of these items will result in the creation of a new distribution. If a change is made in the location or owner, journal entries are posted to reflect the new distribution, even though there has been no accounting impact (thus the zero dollar accounting entries).

3. How often can I create journal entries?

You can run the Create Journal Entries program once per accounting period after you run the depreciation program.

4. What happens if I forget to create journal entries for an accounting period? For example, I created journal entries for AUG-08 and OCT-08, but forgot to create journal entries for SEP-08).

Oracle Assets lets you create journal entries for accounting periods in any order as long as you have ran depreciation for the period and the period is open in your general ledger.

5. How do I reconcile Oracle Assets with my general ledger?

Oracle Assets has a set of reports you use to reconcile asset cost, depreciation expense, and depreciation reserve accounts to your general ledger.

dgreybarrow Similar Post

Posted in Oracle Asset | 3 Comments »

Next Entries »