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

eAM : Type of Maintenance

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

Usually have these types.

  • Reactive :These are Commonly used in small organizations that do not have Maintenance Planners. As equipment fails, work orders are generated and crews are assigned.
  • Preventive :Organizations that practice preventive maintenance place a high value on asset performance and availability. These organizations commonly have Maintenance Planners that plan the resources and materials needed both long and short term. These organizations will typically have robust preventive maintenance plans to ensure that assets are maintained on a regular basis therefore decreasing the risk of failure.
  • Predictive / Reliability Centered Maintenance:Organizations that are driven by heavy production demands or regulatory compliance will invest heavily in Predictive Maintenance Practices. Predictive Maintenance closely observes the behavior and performance of assets based on capacity requirements, engineered capabilities, maintenance strategies and failure rates. Often these organizations will employ or charter a group to create a Reliability Centered Maintenance practice. These groups trend an asset’s performance and capture data such as Mean time between Asset Failures and Mean Time to Repair. This data then help the maintenance organization better strategize their PM programs.

dgreybarrow EAM Business Flow

Lets simulate the real time business scenario and align with eAM Business flow.

Within Company work is generally reported by non-maintenance personnel, usually called or referred to as a Work Request. The Work Request is then routed for approval and a Maintenance Planner is alerted to the need of repair or services.

The planner will then conduct a “walk through” to estimate the materials and trades people that are needed to conduct the repair job. Some repairs may require that an asset be shutdown or brought into the shop. The planner will then meet with operations to plan out the time best determined to access the asset with minimal impact on operations/production.

A typical business flow for Maintenance can be best understood as figure below:

EAM Maintence

Once the planner has identified the resources, materials, equipment and time needed to perform maintenance, the work order is scheduled to a crew (responsible department who then executes the work). Crew Supervisors pull the schedules defined by planners and assign the qualified trades person to the individual work order operations.

Materials are issued, requisitions are generated and time is entered against the work order operation as tasks are progressing. Upon completion of a task the trades person may enter additional information about the work as well as meter and inspection reading conducted through the course of work. The work order is then closed.

Posted in Oracle eAM | No Comments »

APP-AR-96981: Maximum Write-Off amount not set

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

When the user user try to apply a “Receipt Write-off” to the a receipt , the system did not allow to do , and throw the error: APP-AR-96982:

Support IT can find the total total write-off amount is already setup with range still donot understand why this error message .

In such case, You often setup the approval limit but this is required to setup the The Maximum Write-Off Amount on System Option form . Once you are able to setup , you can use this function.

Receipt Write-off Maximum System Limit
You can define the system level Maximum Write-off Amount for a single receipt in your functional currency.

No users can write off unapplied receipt amounts that are greater than the system maximum – even if their user limits allow it.

Therefore the system maximum overrides user approval limits when necessary.

Navigation: Setup>System>System Options (T) Miscellaneous

The Receipt Write-Off amount is determined from all write-off activities for the user on the receipt.

Posted in Oracle Receivable | No Comments »

20 Minute Guide to Oracle Integration Repository

Posted on December 22nd, 2008 by Sanjit Anand ||Email This Post Email This Post

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

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

dgreybarrow 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

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

dgreybarrow 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

dgreybarrowAccessing 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 [ /]

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.


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

dgreybarrowStep 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

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

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


  1. Interface Name
  2. Internal Name
  3. Product Family / Product
  4. Internal Name
  5. Interface Type
  6. Business Entity

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

dgreybarrow 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…


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

Posted in EBS Suite | 7 Comments »

Its all about TCA API

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

As you read some of my earlier post, its already known to you the TCA is an architecture that allows you to model and manage an electronic representation of the commercial community in which you do business.Trading Community Architecture (TCA) is literally an architecture designed to support complex trading communities which included in the TCA.Therefore in layman language this is:

  • A comprehensive database schema.
  • A set of public APIs for custom development.
  • Integration with 3rd party content providers for data enrichment.
  • A sophisticated set of data management utilities to keep the registry clean

The next question you might have how to get data Data Into TCA.

There are two ways recommended for getting the data into the TCA data model.They are

  • CRM and ERP/AR applications through Using the “Customer Interface Program”.
  • Application Programming Interfaces (APIs).

dgreybarrow Meeting Business Needs:

Programmatic access to the TCA Data model should go only if these are business needs:

  • Applications in the Oracle E-Business Suite can use the TCA public APIs to insert and update entities in the TCA model, as part of server side and middle tier business logic.
  • APIs provide a gateway to the TCA data model from applications that use Forms 6.0/9.0 user interfaces (UIs) as well as from HTML UIs.
  • Data migration from legacy systems into the TCA model.

dgreybarrow Important features of the Version 2 TCA API’s:

  • Flexible, easy to understand, and modular.
  • Extensive debugging capability.
  • Extensive error handling and reporting capability.
  • Robust validation in all of the APIs.
  • A ne locking mechanism based on the OBJECT_VERSION_NUMBER field,which has been included in all of the HZ entities for which the public APIs have been provided.
  • Standard signature and availability of common parameters.

dgreybarrow Categories of Entities Covered in TCA APIs:

The following are main categories of entities that are covered in TCA APIs :

  • Parties – Person, Organization, Relationship, Group
  • Locations, Party Sites, Party Site Uses
  • Contact Points
  • Customer Accounts, Account Sites and Site Uses
  • Relationships
  • Relationship, Relationship types
  • Classification

dgreybarrow APIs available for Trading Community Architecture:

Following are Some of the Important APIs Available for Trading Community Architecture.

  • Party APIs:
    • Create Organization API
    • Create Person API
    • Create Group API
  • Party Contact APIs:
    • Create Org Contact API
    • Update Org Contact API
  • Location APIs:
    • Create Location API
    • Update Location API
  • Party Site APIs:
    • Create Party Site API
    • Create Party Site Use API
  • Contact Point APIs:
    • Create Contact Point API (Person or Organization)
  • Relationship Type API:
    • Create Relationship Type API
  • Relationship API:
    • Create Relationship API
  • Customer Account APIs:
    • Create Customer Account API (Person or Organization)
    • Update Customer Account API
  • Customer Account Site APIs:
    • Create Customer Account Site API
    • Create Customer Account Site Use API
  • Customer Profile APIs:
    • Create Customer Profile API
    • Update Customer Profile API
  • Classification API:
    • Create Class Category API
    • Create Code Assignment API
  • Tax Assignment API:
    • Create Location Assignment API

You can check my old post for getting information for TCA tables structure to see how data get populated.

Posted in Oracle TCA | No Comments »

Evaluated Receipt Settlement

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

Do you know , Evaluated Receipt Settlement (ERS) is a procedure for the automatic settlement of goods receipts. It was pioneered by General Motors (GM) to save the company time and money . Major benefits of ERS include invoice variance prevention , the elimination of non-value-added work (like tasks associated with reconciliation), and opportunity cost of capital savings.

dgreybarrow What is it?

ERS is a business process between trading partners that conduct commerce without invoices. In an ERS transaction, the supplier ships goods based upon an Advance Shipping Notice (ASN), and the purchaser, upon receipt, confirms the existence of a corresponding purchase order or contract, verifies the identity and quantity of the goods, and then pays the supplier.

dgreybarrow In A typical Real Life senaraios with client using ERP prefreably Oracle EBS Suite.

Evaluated Receipt Settlement (ERS), also known as “Invoice on Receipt” or “Pay on Receipt”, is a process whereby company creates a system invoice on behalf of the supplier for receipts booked against PO’s or BPA releases. The processes from invoicing to payment are thereby automated, reducing the level of human error and improving payments to suppliers.

dgreybarrow Benefits of ERS

  • Provide enhanced automation in the Procure to Pay Business flow
  • Automate invoicing for suppliers on BPA’s where multiple releases and shipment lines will be created
  • Automate invoicing for PO’s with multiple PO lines and multiple shipment lines
  • Less paperwork
  • Matching of the PO/BPA release to the invoice is done automatically, without any manual intervention required by AP
  • Elimination of blocked invoices
  • Elimination of manual data entry errors
  • No delay in payment due to “lost” Invoices
  • Supplier does not need to send paper invoices
  • Improved on-time payment of invoices due to fewer PO/Invoice discrepancies and invoices lost in the mail
  • Regular on-time payments

If your customer have ERS Suppliers that uses BPA for direct material supplies where goods can be accurately received , you can explore this options.

dgreybarrowA note with Oracle EBS Functionality

This feature is alternately known in Oracle User Guides as:Evaluated Receipt Settlement (ERS) or Payment on Receipt (POR) or Self-Billing Invoices (SBI)

dgreybarrow The Oracle Process



dgreybarrow How does it work in Oracle ?

Receipt transactions trigger invoice creation

  • Receipt
  • Delivery
  • Advance Shipment Notice
  • Advance Shipment and Billing Notice

Therefore , Depending on your business needs, you can use Pay on Receipt to automatically create invoices for:

  • All your vendors; or
  • Select vendors; or
  • Select Purchase Orders

Invoices are generated by running the Pay on Receipt AutoInvoice and Payables Open Interface Import processes.

Invoices move normally through Approval and Payment processes.

Process Returns with automatic Debit Memos that are matched to the original Purchase Order.

In such cases, Invoices are consolidated at one of the following levels:

  • Packing Slip – Triggers one invoice per packing slip
  • Receipt – Triggers one invoice per receipt
  • Supplier Pay Site – Triggers one invoice per supplier pay site

    Hope this helps. let me know , if you have any question. next Will discuss more on tax part of ERS.

Posted in Oracle Purchasing | No Comments »

Bespoke Application & Utilizing HR/Payroll APIs

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

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

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

dgreybarrowIts 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

  • Business groups, payrolls related information : In this set you requires company and payroll reference information , which is required to validate employee information for your company
    • You can map with these payroll table
      • hr_organization_units
      • hr_locations
      • pay_payrolls
      • fnd_common_lookups
  • Counties, cities, states, zips : Contains the address info for employee address and tax validation. Tax validation is required if there is seperate rule for taxation or may have multiorg installation.
    • You can map with these payroll table
      • pay_us_states
      • pay_us_counties
      • pay_us_city_names
      • pay_us_zip_codes
  • Deductions and levies : These are all the deductions and levies whatever configured in Oracle Apps.
    • You can map with these payroll table
      • pay_element_types_f
      • pay_element_links_f
      • pay_element_values

2. Employee Tables : What you really required

  • Persons and address tables :These are employee personal and employment information
    • You can map with these payroll table
      • per_people_f
      • pay_people_groups
      • per_assignments_f
      • per_addresses
  • Deductions and levies
    • You can map with these payroll table
      • pay_element_entries_f
      • pay_element_entry_values_f
  • Direct Deposits
    • You can map with these payroll table
      • pay_external_accounts
      • pay_org_payment_methods_f
      • pay_personal_payment_methods_f

3. Payroll Tables : What you really required

  • Payroll summary :Contains all the payroll details such as earnings, deductions, bonuses, and hours in a few tables
    • You can map with these payroll table
      • pay_payroll_actions
      • pay_assignment_actions
      • pay_run_results
      • pay_run_result_values
      • pay_element_classifications
      • pay_element_types_f
      • pay_input_values_f
  • Payment summary :Contains check and direct deposit details such as amount and account numbers
    • You can map with these payroll table
      • pay_payroll_actions
      • pay_assignment_actions
      • pay_pre_payments
      • pay_org_payment_methods_f
      • pay_personal_payment_methods_f

dgreybarrowAPI Avability of HRMS

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

dgreybarrowAdd 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

dgreybarrowUpdate 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

dgreybarrowAdd 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.....    
Note: The API's have to be called in that order.


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


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

dgreybarrowSchools and Colleges

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

dgreybarrowInsert 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


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

dgreybarrowEnd 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

dgreybarrowInsert 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


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


  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.

dgreybarrowConsidertaion 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

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

dgreybarrowSuggested Reading

Posted in HRMS | 2 Comments »

Oracle Web ADI Information Query

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

Often at the time of working with Oracle Web ADIs, we want to know what all components are associated with it like Integrator, Layout, Mapping, Underlying PL/SQL procedure etc.

Also while migrating Web ADIs from one instance to another (in case of Installation upgrades, or from Development Instance to Production Instance), using FNDLOAD, we need to know each associated component of a Web ADI to migrate it completely.

<pre lang=”plsql”>

bit.application_id APP_ID,
bit.user_name INTEGRATOR_NAME,
bit.integrator_code INTEGRATOR_CODE,
blcv.layout_code INTEGRATOR_LAYOUT,
bm.mapping_code INTEGRATOR_MAPPING,
biv.interface_name API,
biv.upload_type TYPE,
bne_integrators_tl bit,
bne_layout_cols_v blcv,
bne_layouts_b bl,
bne_interfaces_vl biv,
bne_contents_vl BC,
bne_mappings_vl BM
AND biv.interface_code = blcv.INTERFACE_CODE
AND bit.integrator_code=bl.integrator_code
AND bit.integrator_code=bc.integrator_code
AND bit.integrator_code=bm.integrator_code
AND UPPER(bit.user_name) LIKE UPPER(‘<integrator USER name>’)
</pre >

The query enables a you to reduce his time spent on analysis of a Web ADI. All that one should know is the name of the Integrator of the Web ADI. Even the end user (Business User) would know the name of the Integrator that they are using.

Posted in Web ADI | No Comments »

Payroll to GL Transfer : “All you wanted to Know…..”

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

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.

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

dgreybarrowWho 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

dgreybarrowHow it works

payroll to GL transferThe 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

dgreybarrow Which program take care of payroll cost transfer.

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

dgreybarrowWhat 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

dgreybarrowTechnical Tables Involved

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

dgreybarrowStep 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_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:



8) 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.

dgreybarrow 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

dgreybarrow 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.:)

Posted in HRMS | 18 Comments »

R12 SLA :Reporting using SLA

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

With SLA, all accounting reports normally use SLA data which store as the single repository to generate and output accounting related reports.

These are some of the reports which can be used for SLA reporting.

  • Account Analysis Report
  • Accounting Program
  • Complete Multiperiod Accounting
  • Create Accounting
  • Journal Entries Report
  • Open Account AP Balances Listing (Accounts Payable Trial Balance)
  • Subledger Accounting Balances Update
  • Subledger Multiperiod Accounting and Accrual Reversal Report
  • Third Party Balances Report
  • Trading Partner Summary Report
  • Transfer Journal Entries to GL
  • Validate Application Accounting Definitions

dgreybarrow Similar Post

Posted in Subledger Accounting | No Comments »

AR to GL Transfer : Part II

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

Read This for AR to GL Transfer

AR to GL Transfer

dgreybarrow-2GL 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
  • REFERENCE29 :MISC_source type or TRADE_source_type

dgreybarrow-2Key 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

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 for Transaction.

        gjjlv.period_name               "Period" 
      ,                        "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"
      ,                        "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        glcc.segment1 ='30D'

dgreybarrow-2Run ARGLTP module: General Ledger Transfer Program

AR to GL transfer rECEIPT

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

Posted in Oracle Receivable | 1 Comment »

« Previous Entries