These are the some of questions which was asked by loyal reader in last couple of months.

• where I get the information about API which is suitable for a specific business process?
• I need to know full description of an API?
• I need to understand full description of each parameter?
• Where I can get the technical information so that I can able to use these APIs?
• I need to know what other interface mechanisms are available for Integration with Oracle EBS Suite?
• How do I access the Oracle Integration Respository Screen Which Exposes Web Services , API's etc?
• Could you please explain what the Oracle Integration Repository is used for?
• Could you please guide me how to access irep...I see references in several documents, not clear as to how to access the screen?

So this is small but powerful post will help you get the answer of these question.

IREP ....Much about Nothing

Think of it as a knowledge base of all integration points for the e-business suite.

So...that mean the Oracle iRep(Integration Repository) is a compilation of information about the different service endpoints exposed by the Oracle E-business Suite of applications.

This provides you a complete catalog of Oracle E-Business Suite’s business service interfaces. You consider this as powerful tool that helps users to discover and deploy the appropriate business interface for integration with any system application .

There is no additional cost you need to pay to get this add-on tool. This is shipped as part of the E-Business Suite Bundle. As the instance is patched, the repository is automatically updated with content appropriate.

The key advantages of using Irep by Implementation consultants are..

• If you want to know how to get data in or out of the suite, you can go search inside it. Any customized modules/classes and functions written can also be published to integrators through the repository.
• You will get assurance from Oracle that you are using supported public interfaces and API's

How should you access IREP ?

Choice is yours , use any one of these options:

1) From E-business Suite, you need to add a Responsibility : Integration Repository
2) You can directly use Oracle's hosted url: http://irep.oracle.com/

What you can access in irep?

• PL/SQL Procedures and Functions :A business interface can be based on a PL/SQL package from which you invoke procedures and functions appropriate to a defined integration goal.
• Open Interface Tables :An open interface consists of the interface tables to store data from external sources and concurrent programs, to validate and apply this data into the Oracle Applications base tables. All open interfaces are implemented using concurrent programs.
• Interface Views :Interface views are database objects that make data from Oracle Applications products available for selection and used by destination applications.
• Concurrent Programs
• Java Methods :A business interface can be based on a Java class from which you invoke methods
• Web Services : Both Service Bean and XML Gateway message maps can be exposed as Web services, which are defined with Web Services Description Language (WSDL) content appropriate to the interface type.
• XML Gateway Maps :Oracle XML Gateway is a set of services that allows easy integration with Oracle Applications to support XML messaging.
• eCommerce Gateway Messages :Oracle e-Commerce Gateway is a file-based integration layer between Oracle Applications and any other external application.

You can get the details of these interfaces in these documents, like Service Data Objects ,XML Schema ,DTD ,Interface Tables ,Views

Accessing Oracle Integration Repository

Those who are still using 11i they can use Oracle url for accessing irep. Accessing through appropriate registration, you can use Oracle's hosted instance of Integration Repository [http://irep.oracle.com /]

Those who are using R12 , take a note, these are shipped with product therefore to access these you have to add a separate responsibility to acess irep.So Under sys admin Navigator menu,you can add Integration Repository responsibility.

Once responsibility is given then you can click the Integration Repository link that appears.

How Should I use irep/how irep Works for me?

There are 2 ways to use the Integration Repository.

• Either you can use through Navigation through the catalog
• or Use of the search page

1. IREP: Catalog Use:On the welcome page you can see on the left the tree of the catalog you can navigate through.
   
2. IREP: Search Page :Clicking on search button will drive you to the research page.You can specify criteria, helped by LOV for some of them.
   

Step by Step : Accessing Oracle Integration Repository

What you have to do minimum is , you can invoke the repository like any other Oracle E-Business Suite application. From the Navigator menu, select the Integration Repository responsibility, then click the Integration Repository link that appears.

Oracle Integration Repository has two main user navigation modes:

• Browse ..this is defualt options for you.
• And the second is Search

Let’s understand How it works ?

First and foremost you need to understand the "Business entities"..which are defined as objects that either perform business activities or have business activities performed on them .

Sales orders, employees, purchase orders, invoice, Payment, customers, and receipts are all examples of business entities.

An interface can be used by multiple business entities, and a business entity can be accessed using multiple interfaces.

Therefore if you are navigating through Integration Repository in that case you can browse directly to an appropriate list of interfaces if you know which product family and product you want to integrate with.

1)The Product Family view is organized as follows: Product Family > Product > Business Entity
For example, Financials > Payables > Payables Invoice.

2) The Interface Type view is organized as follows: Interface Type > Product Family > Product
For example, Web Service > Financials > Cash Management.

the business interface information in Oracle Integration Repository is organized for browsing and searching by interface type, based on the integration technology used. Use this view to see all of the interfaces available for a particular product that use a particular interface type.XML Gateway and Web service-based interfaces conform to various industry standards .

3)The Standard view is organized as follows: Standard and Version > Product Family > Product.
For example, OAG7.2 > Financials > Payables > Process Invoice.

Use this view to browse for a product’s XML Gateway maps and Web services belonging to the specified standard; for example, W3C or OAG 7.2.

Oracle Integration Repository user navigation modes: Searching for an Interface

You can click the Search button anywhere in Oracle Integration Repository to access the main Search page or you can search for interfaces with any combination of the following criteria. This is screen snapshots which you can use.

you can also click Show More Search Options to include any of the following additional criteria in your search:

• Category and Category Value: Used to qualify product-specific features
  • Scope
    • Public - these interfaces can be used by anyone.
    • Internal To Oracle - these interfaces are intended to be used only by Oracle’s product development teams.
    • Private To Application - these interfaces are intended to be used only by the owning product’s development team.
  • Standard: Based on the certain standard like OAG or RosettaNet
• Status
• Active
• Deprecated
• Obsolete
• Planned

How to view Interface Details

Very simple, if you able to find the details

The Interface Details are provided if you click on the Interface Name.

If you take the example of FND_GLOBAL, then you can find the details of a PL/SQL API are as follows:

1. Internal Name
2. Interface
3. Type (of Interface)
4. Business Entity
5. Status
6. Scope
7. Description
8. Signature
9. Parameters etc…

Using Export Feature

Once the list of the APIs is displayed, you can press an export button provides a csv format file: to establish a list of APIs

Therefore , Integration Repository is one of such powerful tool that must be used by any EBS implementor or inhouse IT departments to integrate with other application.

Before start of the topic , let me tell you intresting fact about the word "bespoke application".

Long time back I heard this term while working with a UK based client, in a telecon its sound like "They need a bespoke application with Oracle to cater Billing with Front end” ..What? The only time I had heard the words ‘spoke’ and ‘application’ in the same sentence was when someone told me about an application built on top of a hub and spoke architecture .Well after that , I realized that ‘bespoke application’ means the same ‘custom built application’ or simply ‘custom application’ that we normally used to called as. I really donot know why UK English don't use word custom here. Its sound Interesting localization tidbit.

Bespoke (pronounced bee-SPOHK)

Therefore , "Bespoke" was most commonly used for suits made specifically for a customer without the use of a pattern. In effect, the length of fabric was "spoken for".

The Need

IT, Health care based industry are Manpower intensive, therefore Human Capital management system is really is need to cater today growing need. Though Integrated system like Oracle or people soft or SAP there is capability of maintaining each function, but still companies prefer to have a front end application for maintaining some of the key information for employee management system.Therefore Custom web based application possibility can not be ruled out.

If this is the case inhouse development team would like to access the HRMS/Payroll via API's so that they can have there own presentation layer.

1. Is this possible ? Then How?
2. Where do I find the API documentation?

So answer of these questions are

1. Yes, it possible. You need to identify the tables and API's

2. There are enough documentation available for this.

Just Follow these post, you will get answer of most of your query.

Its all about Table and API

Such kind of Bespoke/custom application requires these Oracle tables access which are divided into three major tracks.

• Reference tables : The details for driving basic Information.
• Employee tables : The details for driving employee vital details
• Payroll tables : Payroll details for employee or any other elements.

1.Reference Tables : What you really required

2. Employee Tables : What you really required

3. Payroll Tables : What you really required

API Avability of HRMS

So here are few Business events required for your bespoken application and there underline API's details

Add a New Employee

1. Insert person :hr_entry_api.insert_element_entry
2. Update people group :hr_assignment_api.update_emp_asg_criteria
3. Update assignment :hr_assignment_api.update_us_emp_asg
4. Insert base salary :insert into per_pay_proposals
5. Insert check as the payment method :hr_entry_api. insert_element_entry

Update Employee Information

1. Update personal information if it changed: per_per_upd.upd
2. Update assignment and people group information if it exists, set mode to correct or update :hr_assignment_api.update_emp_asg_criteria

Add terminating employees

In Oracle On Terminate screen, select Actual Process Date and/or Final Process Date and click on Terminate button. Optionally, enter 'Leaving Reason,' 'Notified,' 'Projected,' 'Last Standard Proces' values.

These are API's are available for termination

 
Hr_ex_employee_api (File name: peexeapi.pkb)
This file contains three (3) procedures.....
hr_ex_employee_api.actual_termination_emp
hr_ex_employee_api.update_term_details_emp
hr_ex_employee_api.final_process_emp
Note: The API's have to be called in that order.

Address

1. Insert or Update Addresses :hr_person_address_api.create_person_address
2. Update existing address records :hr_person_address_api.update_person_address

Phones

1. Insert or create a phone :hr_phone_api.create_phone
2. update existing phone records :hr_person_address_api.update_person_address

Schools and Colleges

1. Insert or create a schol and college information :per_esa_ins.ins
2. update existing records :per_esa_upd.upd

Insert or Update Deductions

1. If deduction is ended, use the delete API to set end date :hr_entry_api.delete_element_entry
2. Update deduction if exists, else insert it :hr_entry_api.insert_element_entry

Qualifications

1. Insert or create a qualifications information :per_qualifications_api.create_qualification
2. update qualifications records :per_qualifications_api.update_qualification

End Date /delete Element Enties

1. End Date /delete Element Enties :py_element_entry_api.delete_element_entry

There are 4 date track delete modes available (variable p_datetrack_delete_mode)

• DELETE end date element entry
• DELETE_NEXT_CHANGE delete next changes
• FUTURE_CHANGE delete future changes
• ZAP completely remove element entry from the database

Insert or Update Direct Deposits

1. If direct deposit is ended, use the delete API to set end date :hr_personal_pay_method_api.delete_personal_pay_method
2. Insert bank account if it does not exist :insert into pay_external_accounts
3. Update direct deposit if exists, else insert it :hr_personal_pay_method_api.create_personal_pay_method

Inform Manager of Inactive Employee Credit card process

1. Use this if required inactivate employee credit card process : hr_ex_employee_api.actual_termination_emp

Jobs

1. Create new Job :hr_job_api.create_job
2. Updating the job details :hr_job_api.update_job

Positions

1. Create new position :hr_position_api.create_position
2. Updating the position details :hr_position_api.update_position

Where I can get the details for these API's

For Oracle release 11 ,11i release 12, the APIs are located in the operating system directories:

$PER_TOP/patch/xxx/sql and $PAY_TOP/patch/xxx/sql,

Refer to filenames like pe****api.pkh, py****api.pkh, and hr****api.pkh, where **** represents wildcard characters.

You can identify the package name and version of the API.

Run this query

 
 
SELECT text
FROM all_source
WHERE name LIKE '%your_api_name%'
AND text LIKE '%Header%';
 

This query will return the header line of the pac kage and package body showing the package name and current version number.

 
 
SELECT object_type,object_name
FROM user_objects
WHERE object_name
LIKE 'HR%API';
 

Publicly Callable Business Process APIs in Oracle HRMS

Oracle APIs are a logical grouping of all external process routines. The Oracle HRMS API sets delivers a set of PL/SQL packages procedures and functions that provide an open interface to the database.

There are three options for you to find the details:

1. Oracle Documents
   To find a current list of publicly callable business process APIs in Oracle HRMS; you need to go into your On Line Help from within applications and use the search field to find 'Publicly Callable Business'. In the Search Results frame you will see a link to 'Publicly Callable Business Process APIs in Oracle HRMS (Oracle HRMS)' listed.
2. Irep
   To find correct information on APIs you check Oracle Integration Repository (irep) which can accessed via MetaLink by selecting the Knowledge tab - Knowledge Browser sub-tab, then in the Online Documentation box select - Oracle Integration Repository.
3. Search the database
   You may find official documentation on Publicaly Callable APIs as in options 1.
   You can search in the HR Schema for the stored procedures with name 'HR%API'. Open the Package spec and then read the inline comment about the parameters. This is very useful to understand what is meant by each paramater and what the API does.
4. Etrm via metalink
   TRM helpful in selecting the appropriate API call.

Considertaion for using API

Before using any API's of payroll and HRMS you must have clear understanding of

1. Data Tracking
2. Validation paramater
3. Correction, insert mode of API's

Additional Note for API's documents

You can download "Configuring, Reporting and System Administration in Oracle HRMS" document from the Oracle documentation library. It has a list of api's.

Suggested Reading

• Understand Date Tracking in HRMS
• A Newbie’s Guide to E-Business Suite Integration (by Custom Code using API'S!”)
• API's or Open Interface
• The world of Oracle API

Okay.. this was again a hetric week , learn some of new things from HR area. As helping some fresh payroll guys who was doing some report development and other group came for some rescue for balances discrepancy from payroll they transfer to GL. Thought to have this post , and tried to cover some of the vital information which purely focus on Payroll to GL transfer Process.

What is purpose of Payroll Costing and Transfer to General Ledger ?

In Payroll area, the Costing process basically allocates payroll run which results to cost segments.

• The Transfer to the General Ledger process transfers cost information to Oracle General Ledger interface tables.
• At the installation - setting up segments of the Cost Allocation key flexfield to determine the windows in which users can select cost codes.
• Oracle Costing you use a post–payroll run process to accumulate payroll costs.
• For Oracle General Ledger you can map the segments of the Cost Allocation key flexfield to the corresponding segments of the General Ledger Accounting flexfield.
• If you do not using GL in same product, you may consider how to relate information on labor costs entered in Oracle Human Resources to your financial systems.

Who drives the payroll costing :Its Cost Allocation Flex field Segments

• Define a structure for your Cost Allocation Flex field which contains the segments you want to use for your Business Group. You will use this structure to enter your payroll costing details in Oracle HRMS.
• You use Segment Qualifiers to control the level at which costing information can be entered to the system. Each Qualifier determines the level at which costing information can be entered

How it works

The costing process picks up 'run results'. The specific 'payroll actions' that will be picked up by the costing process are in PAY_ACTION_CLASSIFICATION table and have a CLASSIFICATION_NAME of 'COSTED".

So the process will create an assignment_action for every assignment that has a run result from one of the 'costed' classifications.

The process then populates the PAY_COSTS table with the run result value (costed value). The actual cost code is stored on the PAY_COST_ALLOCATION_KEYFLEX table in the 'concatenated_segments' column, this is referenced using the 'cost_allocation_keyflex_id' from PAY_COSTS. (Adopted - Source metalink)

When the Payroll costing results are transferred to Oracle General Ledger only the mapped segments and the costing amounts are transferred. The data is transferred from the PAY_COSTS table into the GL_INTERFACE table by running the Oracle Payroll process Transfer to GL. After the transfer from Payroll is complete, the batch will need to be imported into GL. This is done by running the Oracle General Ledger standard GL Import process .

Transfer Program to GL process populates the following columns:

• status
• set_of_books_id
• accounting_date : This is effective date of payroll run
• currency_code
• date_created
• created_by :801...which is the application id for Payroll
• actual_flag
• user_je_category_name :hard-coded as Payroll
• user_je_source_name :hard-coded as Payroll
• entered_dr :the dollar amount to be debited
• entered_cr :the dollar amount to be credited
• reference21 :payroll_action_id of the transfer to GL process from pay_payroll_actions
• reference22 :cost_allocation_keyflex_id from pay_cost_allocation_keyflex

Which program take care of payroll cost transfer.

It is pytrangl.pkb, located in $PAY_TOP/patch/115/sql/pytrangl.pkb

What data is mapped from Payroll to General Ledger when information

Similar to other Application transfer program, Payroll transfer populates the following reference columns in GL_INTERFACE:

• Reference21 :Payroll_action_id of Payroll Transfer to GL process
• Reference22 :Cost_allocation_keyflex_id of Payroll cost flexfield for this entry
• Reference23 :effective date of the Payroll Run this entry is derived from
• Reference24 :Date_earned of the Payroll Run this entry is derived from
• Reference25 :Payroll_action_id of Payroll Run this entry is derived from

Journal import is then placed these in reference1, reference2, reference3, reference4 and reference5 in GL_IMPORT_REFERENCES table.

The GL_IMPORT_REFERENCES columns are actually populated during the GL Journal Import process. Although these are mapped from the columns in the GL_INTERFACE table, the 'Transfer to GL' process does not actually populate the GL_IMPORT_REFERENCES table

Technical Tables Involved

• pay_assignment_actions
• pay_action_interlocks
• pay_assignment_actions
• pay_gl_interface
• pay_cost_allocation_keyflex
• gl_import-refrence
• gl_interface

Step for Payroll Tranfer To GL

1) insert the following parameters in PAY_ACTION_PARAMETERS

Costing results are generated based on the check date of the payroll process. However, you do have the ability to set the accounting date for the transfer to date earned rather than date paid. To do this you would need to change the TGL_DATE_USED pay action parameter to "E". If you do not do this then the accounting date of the transfer defaults to the check date of the originating payroll

 
INSERT INTO PAY_ACTION_PARAMETERS VALUES ('TGL_DATE_USED','E');
INSERT INTO PAY_ACTION_PARAMETERS VALUES ('TGL_REVB_ACC_DATE','Y');
INSERT INTO PAY_ACTION_PARAMETERS VALUES ('TGL_GROUP_ID','Y');
 

2) Setup your costing flexfield qualifiers.

3) Create an element.

4) Link the element : You need to check the costing radio button,and transfer to GL checkbox. Then you have to enter the values for the costing/balancing field.

5) Then you need to run the quickpay/pre-payments/costing process

6) Then you can track the ASSIGNMENT_ACTION_ID and PAYROLL_ACTION_ID navigating to View-->Payroll Process Results/Assignment Process Results

7) Verify that the costing details are created in PAY_COSTS table.

This is small query which you can use to get the details:

 
 
SELECT * FROM PAY_COSTS WHERE ASSIGNMENT_ACTION_ID = &assignment_action_id;
 

Then you need to run the Payroll transfer to GL process: Here are the data get inserted in GL Interface.

 
 
SELECT gl.*
FROM pay_payroll_actions ppa,
    pay_assignment_actions paa,
    pay_costs pcv,
    gl_interface gl
WHERE ppa.payroll_action_id = :costing_payroll_action
AND   ppa.action_type='C'
AND pcv.transfer_to_gl_flag='Y'
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id=pcv.assignment_action_id
AND gl.user_je_source_name='Payroll'
AND gl.reference22=TO_CHAR(pcv.cost_allocation_keyflex_id));
 

9) Verify the data should be imported into gl_interfaces

Do a quick check

 
<pre>select * FROM gl_interface
WHERE set_of_books_id =1003 --(set_of_books_id)
AND GROUP_ID =123   -enter_group_id
 

10) Then import the same in GL by navigating to Journal-->Import.

Running Transfer To GL Process

1. Select View tab in the menu and then select requests
2. Select Submit a New Request button
3. Select Single Request radio button and then click ok button
4. Now enter as follows
   Name : Transfer To GL
   Select the parameters Field then enter the Following details
   Payroll Name: enter the Payroll Name for which you want to Process
   Consolidation Set : Consolidation Set name of a Payroll
   Start Date : Select the Start Period for which you want to run the Payroll
   End Date: Period End Date
5. Then after filling the above fields then select OK Button
6. Select Submit button to submit the

Query to get Payroll data in GL side

Here is small query that you can use to get the details of Payroll data from GL side.Script

Let me know, if you have any question and comments for GL transfer of Payroll area.:)

Read This for AR to GL Transfer

AR to GL Transfer

GL Transfer Can Capture the Fingerprints (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 Reference columns

• REFERENCE21 :posting_control_id
• REFERENCE22 :cash_receipt_id||cash_receipt_history_id or cash_receipt_id for MISC
• REFERENCE23 :line_id
• REFERENCE24 :receipt_number
• REFERENCE25 :null for CASH / cash_receipt_history_id for MISC
• REFERENCE26 :null
• REFERENCE27 :pay_from_customer
• REFERENCE28 :MISC / TRADE
• REFERENCE29 :MISC_source type or TRADE_source_type
• REFERENCE30 :AR_CASH_RECEIPT_HISTORY

Key Tables

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

• 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

Query 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"
      , gjb.name                        "Batch name"
      , gjjlv.header_name               "Journal Entry For"
      , gjjlv.je_source                 "Source"
      , glcc.concatenated_segments      "Accounts"
      , 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"
      , arm.name                        "Payment Method"
      , acra.receipt_number             "Receipt Num"
      , acra.receipt_date               "Receipt Date"
      , RA.CUSTOMER_NAME                "Reference"
      , gjjlv.created_by                "Gl Transfer By"
FROM    apps.gl_je_journal_lines_v gjjlv
      , gl_je_lines gje
      , gl_je_headers gjh
      , gl_je_batches gjb
      , ar_cash_receipts_all acra
      , apps.ra_customers ra
      , apps.gl_code_combinations_kfv glcc
      , ar_receipt_methods arm
WHERE      gjh.period_name IN ('OCT-2007','NOV-2007')
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 = acra.receipt_number
AND        ra.customer_id = acra.pay_from_customer
AND        acra.receipt_method_id = arm.receipt_method_id
AND        gjjlv.SUBLEDGER_DOC_SEQUENCE_VALUE = acra.DOC_SEQUENCE_VALUE
AND        gjjlv.SUBLEDGER_DOC_SEQUENCE_id = acra.DOC_SEQUENCE_ID
--and        glcc.segment1 ='30D'
 

Run ARGLTP module: General Ledger Transfer Program

If your program is running bit slow , you can run this in debug mode and investigate why performance is done.

Based out of experince , the performance hit would be related to the number of records. If you are trying to determine why records are not imported you could run a smaller range than a week and run that in degbug mode. If you are trying to determine performance issues the debug is likely not going to help.

Here are the steps to run in the debug mode.

1)Go to Run General Ledger Interface Screen:

Interfaces -> General Ledger

2) Activate DEBUG_FLAG parameter:

Menu: Help -> Tools -> Examine
Block: Control
Field: DEBUG_FLAG
Value: Y

3) Run ARGLTP module: General Ledger Transfer Program.

After activating the debug mode the ARGLTP log file will show you more detailed information about this process. In you are unable to debug you can ask Oracle support they will help you in addressing slow performance.

Hope this will be helpful for rootcausing any issue during month end or addressing daily reconciliation.Will back soon for PO and PRJ transfer details.

Oracle 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.

Posting Journals to GL

Your Navigation for transfer process would be

Interfaces> General ledger(11i)

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.

A 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.

Vanila 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.

What 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.

Connecting 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.

Adjustments

• 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
• REFERENCE28 :ADJ
• REFERENCE29 :source_type prefixed by 'ADJ'
• REFERENCE30 :AR_ADJUSTMENTS

Transactions

• REFERENCE21 :posting_control_id
• REFERENCE22 :customer_trx_id
• REFERENCE23 :cust_trx_line_gl_dist_id
• REFERENCE24 :trx_number
• REFERENCE25 :cust.account_number
• REFERENCE26 :CUSTOMER
• REFERENCE27 :bill_to_customer_id
• REFERENCE28 :type(CM/DM/CB/INV)
• REFERENCE29 :type||account_class
• REFERENCE30 :RA_CUST_TRX_LINE_GL_DIST

Applications

• 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
• REFERENCE30 :AR_RECEIVABLE_APPLICATIONS

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
• REFERENCE30 :AR_TRANSACTION_HISTORY

Key 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

Query 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"
        , gjb.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"
        , rctype.name                    "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.

How Inventory Passes Journals To General Ledger (GL)

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

The 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.

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

Technical 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,
  USER_JE_CATEGORY_NAME,GL_SL_LINK_TABLE
  • 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

Query 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:

 
 <pre> 
 
SELECT   gjh.period_name            "Period name"
        ,gjb.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"
        ,TO_CHAR(mta.transaction_id)"Transaction_number"
        ,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,
      MTL_SECONDARY_INVENTORIES cost,
      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(+) = 
 
          cost.expense_account 
 

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.

How to generate FA journal entries and transfer to GL?

In 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

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

• REFERENCE21(REFERENCE_1):TRANSACTION_HEADER_ID
• REFERENCE22(REFERENCE_2):ASSET_ID
• REFERENCE23(REFERENCE_3):DISTRIBUTION_ID
• REFERENCE24(REFERENCE_4):ADJUSTMENT_LINE_ID
• REFERENCE25(REFERENCE_5):BOOK_TYPE_CODE
• REFERENCE26(REFERENCE_6):PERIOD_COUNTER
• REFERENCE27(REFERENCE_7):FA_TRANSFER_TO_GL
• REFERENCE28(REFERENCE_8):ADJUSTMENT_TYPE / LOOKUP_CODE
• REFERENCE29(REFERENCE_9):CJE_ID

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

Query 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 gjjlv.period_name period_name
, gjb.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
, glcc.CONCATENATED_SEGMENTS
,gjjlv.created_by
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.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.je_header_id = fasv.je_header_id
AND glcc.segment1='22'
 

Verification 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

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.

Similar Post

• Fixed Assets:Journal Entries
• Fixed Asset & SLA