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

A bit on IFRS

Posted on February 25th, 2008 by Sanjit Anand ||Email This Post Email This Post

Sometime back, I was talking to one of my accountant friend who pointed me some recent changes in IFRS ,and provided some link for the awareness and to understand the impact in EBS suite. I am not going to put the details, rather will walk through some information which is good to have with ERP consultant who is dealing with Financial products.

double-arrowWhat is IFRS?

IFRS,International Financial Reporting Standards (IFRS), together with International Accounting Standards (IAS), are a “principles-based ” set of standards that establish broad rules rather than dictating specific accounting treatments. From 1973 to 2001, IAS were issued by the International Accounting Standards Committee (IASC). In April 2001 the International Accounting Standards Board (IASB) adopted all IAS and began developing new standards called IFRS.

IFRS are used in many parts of the world, including the European Union, Hong Kong, Australia, Russia, South Africa, Singapore and Pakistan. Nearly 100 countries currently require or permit the use of, or have a policy of convergence with, IFRSs. Here is list of countries which adopted IFRS.

double-arrowWhat is meant IFRS for Oracle eBusiness consultant/ERP Consultant?

As we are quite aware GAAP which is so called Generally Accepted Accounting P rinciples for Shareholder Reporting.The term is not drafted by a legislature.

and ,in term of Accounting Principles this can be best understood as:

IFRS = Financial Reporting standards = Balance Definitions < /FONT >

which internally means more in term of “disclosure Requirements” and “Balance Definitions ” for finance controller.

double-arrowIFRS – What it is for?

The Other things IFRS would do is:

  • Providing a bookkeeping rules rather many of us have impression that they have some rule set for accounting
  • It will do Shareholder Reporting , and this is important when :
    • A group of companies owned by a Public company
    • Not the individual companies
  • It will also do external reporting
    • To your owners (investors, shareholders)
    • Through their stock markets
  • Management Reporting
    • Maximize investors returns, measured only with IFRS
    • Segment Reporting disclosure

double-arrowIFRS – What it is not for?

And more important it should be understand that IFRS is not meant for:

  • Statutory Reporting
  • Tax Reporting
  • Regulatory reporting
  • Subsidiary reporting

double-arrowIFRS – How new is it?

  • IASB has been around since 1980’s
  • IAS/IFRS pretty much converged with the Americans
  • If your ERP supports US GAAP, it supports IAS/IFRS
  • Key premises (recognition, measurement):
    • Mark to Market when possible
    • Reality of the Balance Sheet – real assets, real liabilities
    • Income Statement analyses the change in wealth
    • Deep Disclosure: owners right to know

double-arrowUS GAAP vs. IFRS – A difference

While looking to some more information on internet ,I discovered a nice article from the site of Fulcrum Inquiry who pointed out the key differences(adopted) between the two standards in there one of the article .

gaap vs IFRS

double-arrowDon’t worry about your ERP Side

  • Maybe requires some reconfiguration, maybe requires some data analysis
  • We have notice there is some big change in R12 AR to meet revenue recognition, R12 is partially on track for IFRS fitness in suite.
  • The new R12 Global Architecture introduced the concept of ledgers which has capability of providing differing accounting representations; one ledger for example could conform to IFRS, another to local GAAP.
  • From the Currency area we have already noticed some big changes like:
    • Balance level Translation or Remeasurement in Ledgers – within Ledger sets, run at one click
    • Balance & Activity level Translation or Remeasurement in Financial Consolidation Hub
    • Another is enhanced Revaluation [Functional Currency term dropped: conflicts with IAS 21 / FAS 52]


Those who are looking for some more insight about the changes suggested to refer white paper mention in the last.

double-arrowSome more from Oracle blog

Keep watching this space for having some more thoughts and insight for IFRS from Oracle .

double-arrowFurther Reading

Posted in Basic Accounting | 4 Comments »

Oracle General Ledger Integration

Posted on February 25th, 2008 by Sanjit Anand ||Email This Post Email This Post

Oracle General Ledger is one of the core product of EBS suite, and this is Integrated with almost every segment within EBS.Here is a list of just some of the Financial ,manufacturing and Human Resource Management products that integrate with General Ledger. Typically the integration of General ledger in EBS can be best understood as:

GL Integration

The integration data information can be best understood as below:

double-arrowOracle Financial

  • Oracle Payables sends invoices, payments, realized gain and loss on foreign currency, and invoice price variance to GL.
  • Oracle Receivables sends invoices, payments, adjustments, debit memos, credit memos, cash, chargebacks, and realized gain and loss on foreign currency to GL.
  • Oracle Assets sends capital and construction in process asset additions, cost adjustments, transfers, retirements, depreciation, and reclassifications to GL.
  • Oracle Purchasing sends accruals or receipts not invoiced, purchase orders, final closes, and cancellations to GL.
  • Oracle Projects sends cost distribution of labor and non-labor costs, and project revenue to GL.
  • Oracle Treasury sends revaluation and accrual entries to GL.
  • Oracle Property Manager sends revenues and expenses related to real estate to GL.
  • Oracle Lease Management sends accounting distributions related to leases, such as bookings of contracts, accruals, asset dispositions, terminations, and adjustments for multi-GAAP contracts to GL.

double-arrowOracle HRMS and Payroll

  • Oracle HR shares employee information with GL.
  • Oracle Payroll sends salary, deductions, and tax information to GL.

double-arrowOracle Manufacturing:

In terms of manufacturing this is Integrated as:

  • Oracle Inventory sends cycle counts, physical inventory adjustments, receiving transactions, delivery transactions, intercompany transfers, sales order issues, internal requisitions, sub-inventory transfers, and Cost of Goods Sold (COGS) to GL.
  • Work In Process(WIP) sends material issues or backflush from WIP to GL, along with completions, returns, resource and overhead transactions, and cost updates.
  • Oracle Labor Distribution normally sends salary costs to GL.

double-arrowOther Products

Oracle GL not only integrated with Application product, it does have capability to integrate with other products which is used for adhoc cum management Reporting, these tools are mostly.

  1. Business Intelligence/Analytic Solutions
  2. Enterprise Planning and Budgeting (EPB)
  3. Oracle Financial Services Applications (OFSA)
  4. Daily Business Intelligence (DBI)
  5. Activity-Based Management (OABM)
  • General Ledger’s integration with Oracle Enterprise Planning and Budgeting (EPB) allows us to easily identify, analyze, model, budget, forecast, and report on information stored in our general ledger. Using Oracle GL to maintain and report on account balances throughout the accounting period, and use Financial Analyzer to analyze financial data, such as actual and budget balances, which is after closing the period. We can automatically transfer actual, budget, or encumbrance data, as well as functional, statistical, and foreign entered data from General Ledger to Financial Analyzer, which is on of requirement if company does have different management reporting approach. With financial Analyzer, we can perform sophisticated budgeting and modeling, make changes to budgets and write back budget data to a new budget in GL or to several budget versions for comparative reporting. We would also drill directly from EPB balances to balances and transactions in Oracle General Ledger. With this extended functionality your EPB users with immediate and direct access to GL data without having to run reports or account inquiries in GL, that makes process efficient without any extra step.
  • Oracle Financial Services Applications (OFSA) is a product suite that helps financial services institutions assess enterprise performance. This integration allows the transfer of General Ledger balances to OFSA to reconcile OFSA instrument tables, calculate transfer pricing of non-interest balance sheet items, or perform allocations. The results of OFSA allocation and transfer pricing results can then be transferred back to GL for posting and reporting.
  • The integration with Oracle Daily Business Intelligence (DBI) allows us to get a daily snapshot of company’s financial picture through its E-Business Suite Portals. This is achieved by over 200 pre-inbuilt Portals provide every user in the enterprise with the right information that they need, about every aspect of their business. This makes a centralized place to see the information spans across multiple applications in real time basis.
  • The integration with Oracle Activity Based Management (OABM) allows you to perform complex analysis on costs that are collected in General Ledger in a separate analysis environment—apart from your GL data. OABM is optimized to support multi-layer complex cost assignment rules, activity hierarchies, and complex product and service definitions in terms of activities with complete activity definitions.

Posted in Integration, Oracle General Ledger | 1 Comment »

Order Management(OM) Integration Options

Posted on February 25th, 2008 by Sanjit Anand ||Email This Post Email This Post

OM is one of the most complex modules in EBS, and in typical complex business model, the integration of other product or third party can’t be denied. Processing an Order requires integration with many other business areas. Most integration points with other Oracle products are implemented via PL/SQL-based APIs.Here is brief discussion for Order Management EBS Integration points.

OM Integration

double-arrowHow Order Management integrated with iStore?

OM records customer orders placed via iStore, Order Capture and other CRM applications. It validates setup for shipping and payment options along with providing order status and the shipping information to customers. Once the quote is converted into an order, you can only make changes to the order through Order Management, prior to booking.

double-arrowHow Order Management integrated with Telesales?

Telesales’ eBusiness Center has several integrations with Order Management. There is an Order tab to view order history and create new orders.

double-arrowHow Order Management integrated with Cost Management?

OM call the the Cost Management CST_COST_API to obtain cost from cst_item_costs or cst_quantity_layers when the Gross Margin feature of OM is enabled.

double-arrowHow Order Management integrated with Field Service?

Field Service Report requires specifically that you setup Price Lists, Units of Measure (UOM), and two Inventory Item Attributes in Order Management. Price Lists contain the list price for an item. Items could be material, but also labor and expenses like units of driving distance. Once material, expense and labor transactions for a task have been taken down on the Field Service Debrief, this information is updated to Charges. In Charges the list price for the item is received from Order Management and is used to generate an invoice for a customer.

double-arrowHow Order Management integrated with Depot Repair?

This is used by Depot Repair to create RMA and Sales Orders, validate customer accounts, and invoice customers for repairs.

double-arrowHow Order Management integrated with Install Base?

Information about Install Base trackable items is interfaced to Install Base in the following ways:

  • Shippable Items: For both orders and returns, information is interfaced to Install Base via Inventory Interface.
  • Non-Shippable Items: For both order and returns, information is interfaced to Install Base via the Order Management Fulfillment workflow activity Install Base also supports Internal Sales Order transactions by appropriately creating /updating item instances as a result of transactions between internal organizations such as pick transactions, shipments, and receipts.

double-arrowHow Order Management integrated with Service Contracts?

Service Contracts need to pulls information from the Install Base newly created customer records and creates an ownership record.

  • Warranty: A Warranty contract is created when a Serviceable product is shipped.
  • Extended Warranty: An Extended Warranty contract is created when an Extended Warranty is sold on a sales order. Oracle EAM & OM integration
  • Subscription: Fulfillment starts after the contract approval process.
  • RMA: Service Contracts sends Order Management RMA information

double-arrowHow Order Management integrated with Advanced Planning System/Global Order Promising/ ASCP?

Order Management uses Advanced Supply Chain Planning’s Global Order Promising functionality to check the availability of ordered items and to schedule order lines.Scheduled Order Lines are viewed as demand by the Advanced Planning System.

double-arrowHow Order Management integrated with Purchasing?

Order Management integrates with Oracle Purchasing in the following functional areas:

  • Return Receipts: Order Management uses the Oracle Purchasing Receipt functionality to handle Return receipts. When an item is received, Purchasing calls Order Management to indicate delivery and to get COGS information.
  • Internal Orders: Oracle Purchasing uses Order Import to create internal orders.
  • Drop-Ship Orders: Order Management integrates with Purchasing to fulfill drop-ship orders. It populates the PO requisitions interface table with information for order lines that need to be fulfilled via an external source.

double-arrowHow Order Management integrated with Bills of Material ?

This is integrated with BOM when models and kits are entered on sales orders.Normally OM uses the Bill of Material defined for the model or kit or explode the model into its components, for the purpose of the user selecting options and for shipping purposes.

double-arrowHow Order Management integrated with Workflow ?

Oracle Workflow is heavily used in EBS and specially to manage Order and Line processing. These PL/SQL based Workflow is a natural replacement for Order Cycles functionality. It provides a Graphical User Interfaces for defining activities, notifications, flows and viewing flow status.

double-arrowHow Order Management integrated with Advanced Pricing ?

OM is tightly integrated with Advance Pricing which includes price lists, modifiers, and agreements. Order Management, through its Sales Agreement functionality, creates price lists and pushes them into the Advanced Pricing tables. Order Management calls the Pricing Engine to make pricing requests during the processing of orders, and receives pricing information back from Advanced Pricing.

double-arrowHow Order Management integrated with Configurator ?

Order Management integrates with Oracle Configurator to support ordering and validation of configurations. The Configurator window is a Java Applet that can be launched from the Sales Order form.

double-arrowHow Order Management integrated with Oracle Payment ?

As we know Oracle payment is new product and OM accepts Credit Card information when entered on orders. It integrates with Oracle Payment to validate this information and get Credit Card authorizations. This information is then interfaced to Receivables.

double-arrowHow Order Management integrated with Receivables ?

Order Management integrates with Oracle Receivables in the following function areas:

  • Invoice Interface: Order Management sends invoices and credit memos to Receivables via the Invoice Interface workflow activity. The seeded Invoice Interface – Line workflow sub-process populates the Receivables interface table.
  • Receipts: Order Management calls Receivables’ Receipt API to create receipts for prepaid credit card orders. Order Management receives a payment-set id from AR when the receipt is created, and then passes that id back to AR in the autoinvoice tables at invoicing time so that the invoice can be matched to the receipt.
  • Tax: Order Management calls the Global Tax Engine APIs to default the Tax Code (ARP_TAX.GET_DEFAULT_TAX_CODE) and to calculate estimated tax (ARP_PROCESS_TAX.SUMMARY) for the order Line. The estimated tax value is now stored on the line and re-calculated only when any of the attributes affecting tax change. Information about the tax value is also stored as Line Price Adjustments.
  • Credit Management: If the Credit Management product is installed, notifications are sent to it by Order Management when an order or line goes on credit hold, to initiate a credit review. If the credit review results in a decision to approve the order, a business event is posted which OM subscribes to and then releases the credit hold.

double-arrowHow Order Management integrated with Payables ?

OM accesses the AP Bank Accounts table to populate the Credit Card LOV when an order is being entered with a payment type of Credit Card. Additionally, if a new credit card number is entered and the authorization of that card through iPayment is successful, Order Management calls an AR API to create a new bank account record for the customer in the Bank Accounts table.

double-arrowHow Order Management integrated with Inventory Management ?

  • Order Management integrates with Oracle Inventory Management through Managing Reservations.
  • You can create reservations to on-hand quantities from the Sales Orders form.

double-arrowHow Order Management integrated with Trade Management ?

Order Management can receive RMA orders and lines from Oracle Trade Management as part of its dispute handling functionality.

Posted in Integration, Oracle Order Management | 6 Comments »

Quick notes :”Procure 2 Pay” Cycle

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

Similar to O2C in last post , these things you should Know about Procure 2 Pay Cycle.

Oracle Purchasing

  • You enter purchasing documents by operating unit. These include standard and blanket agreement purchase orders, requisitions, RFQ’s and quotes. You assign a PO shipment to an inventory organization, any inventory organization in the same set of books as the PO’s operating unit.
  • You enter receipts by inventory organization.

Account Payable

  • You setup one default liability account by operating unit.
  • You enter invoices in one operating unit at a time.
  • You run payment runs in one operating unit at a time.
  • You can consolidate supplier invoices on one payment only within an operating unit.
  • You setup bank accounts and associated cash accounts within an operating unit.
  • You select invoices for payment on one payment run for one bank account based on pay group, priority, amount,currency, or payment method (i.e. check, electronic) within an operating unit.


  • You setup suppliers for the entire database instance but addresses for each operating unit.
  • You merge suppliers and addresses within an operating unit.
  • You report supplier/customer netting within an operating unit.

Posted in Oracle Payable, Oracle Purchasing | 2 Comments »

“Business Group” in EBS

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

Lets talk about one of the basic concept of Oracle Application which is so called “Business Group” , part of Multi-org architecture. Understanding this is important for non HRMS consultant as in R12 Finance the new legal Entity architecture is equally important.

double-arrowBusiness Group in HRMS Shared Mode

A business group is a basically Human Resources organization to which you assign employees. You assign each operating unit to a business group in the financial options setup. You can assign the same business group to different operating units including to operating units in different financial sets of books. You can setup a separate business group for each operating unit if you want to segregate and maintain each group of employees separately. Oracle provides one setup business group you can use.

In case of not having HRMS (Full mode),what you have to do is ,to create at least one business group to cater requirement for Oracle Financials and Manufacturing, so that only the employees can perform some of application functionality , which are as follows:

  • Entry and approvals of purchase orders and requisitions require requisitioners, buyers, and approvers to be setup as employees.
  • Purchasing receiving staff must be setup as employees in order to use receiving screens.
  • Engineering change order approver lists use the employee database to validate approvers.
  • AP expense report features allow you to create suppliers from employee information and to generate expense report coding using employee default account numbers.
  • iExpense requires users who enter or approve expense reports to be setup as employees.
  • Fixed Assets allows you to optionally assign assets to employees for tracking purposes.
  • Standard Workflow approvals of GL journal entries in Release 11 require approvers to be setup as employees.
  • Standard Workflow approvals of AR credit memos in Release 11i require approvers to be setup as employees.
  • AP may add invoice approval through AME , which will likely require approvers to be setup as employees.

There are few exceptions, where you have to understand when your installation site is having HR and Projects modules installed, as there are significant implications of business group and HR organizations.

double-arrowBusiness Group is part of MultiOrg model of Organizations

The Multi-Org model provides a four level hierarchy that dictates how transactions flow through different business units and how those business units interact

  • Set of Books- GL- Balancing Entities/Funds
  • Business Group
  • Legal Entity -HR- Fin- Legislative Reporting
  • Balancing Entity
  • Operating Unit- AP, AR, PO, OE -Bal Segment 1, Bal Segment 2,Bal Segment 3
  • Inventory Org MFG, INV, Ship

There is one confusion between Oracle Financial and HRMS consultant, as they simply assume SOB is more meant for non HRMS module and BG is more for HRMS, in certain extend this is true.Lets investigate this on GRE\LE and OU as follows:


Know something more for SOB

  • As covered in earlier post ,a set of books is a financial reporting entity that shares the three C’s that is Chart of accounts(accounting flexfield structure), functional currency and calendar.
  • Oracle General Ledger secures transaction information (journal entry),When we use General Ledger, we choose a responsibility that specifies a set of books. We then see information only for that set of books.
  • In the multiple set of books, the concept of “global” item master Organization exist, since the item master organization is used for item definition and not item accounting information.
  • More over all accounting activity releated to the item master can be controlled at the item or organization level. Example is consolidation at the corporate office or Head office.


Is SOB linked to HR module?

Yes,. Once SOB is created from GL setup part, this can be linked in HRMS by such navigation

Work structure => GL organization Description ==> Business Group


What is a Business Group/Legal Entity?

  • Represents the consolidated enterprise, a major division, or an operation company.
  • Human resources information is secured by business group.(For example, when you request a list of employees, you see all employees assigned to the Business Group of which your organization is a part. )
  • This is true in all applications except the Human Resources applications themselves, which support more granular security by a lower-level organization unit.
  • Multiple sets of books can share the same business group if they share the same business group attributes, including Human Resources flexfield structures.
  • Business Groups separate major segments of a business. Each can have it’s own set of books.
  • Each Group will also have a structure of other organizations classifications assigned to it.
  • Till Release 11i Legal Entities post to a
    • Set of Books
    • Operating Units are part of a Legal Entity
    • Inventory Organizations are part of an Operating Unit ·
    • Inventory Organizations define and maintain items used by other manufacturing modules (Order Entry,Purchasing, MRP, etc.). They also collect and pass data to the Financials modules.

double-arrowOperating Unit vs Business Group

  • With Multi-Org you can define an owning Operating Unit and associate this with a profile option -MO:Operating Unit. When you define data or execute a business process within Oracle Financials, then the system automatically attaches the id of your Operating Unit organization to the data or transaction.
  • With Business Group security model in HRMS you can secure data and processes by id of the Business
    Group.However, in HRMS, the Business Group id is closely related to the country of operation, and not simply a division or line of business.
  • Both look the same but it is important to realize that these are distinct and different mechanisms for two different functional areas. This is especially true for any global implementation of HRMS where the data for each country exists within a single business group but Operating Units exist across many countries, and business groups.

double-arrowSetup Business Group

As per standard User Documentation: A ’Setup’ Business Group is supplied with Oracle HRMS. This business group is used by the default responsibility. You can use this business group with all of its default definitions as the starting point for your own Business Group, or you can define other business groups to meet your own needs.


A business group enables you to group and manage data in accordance with the rules and reporting requirements of each country, and to control access to data.

The critical factors for deciding when to use a separate business group, or an international business group, are based on the following factors:

  • If you use Oracle Payroll
  • The number of people you employ in a country
  • If you require legislative support for Oracle HR

Generally the laws are so different in each country that to be compliant, there must be a different business group for each country in which an enterprise has employees.

double-arrowBU in HRMS and other Integration

The need for cross functional integration cann’t be denied as most of the base information is pulled from HRMS to Oracle Finance , Manufacturing as well as CRM Modules. A typical integration aspect with some of the core module can be best understood as figure below:

CrossFunctional BU HRMS

double-arrowSimilar Post

Posted in EBS Suite, HRMS | 2 Comments »

Quick notes : ‘Order 2 Cash’ Cycle

Posted on February 21st, 2008 by Sanjit Anand ||Email This Post Email This Post

Things you should Know about Order to Cash Cycle.

double-arrowOrder Management

  • You enter sales and return orders within an operating unit.
  • You define price list and discount names for the entire instance. You can also define price list lines, discount lines, and quantity breaks for the entire instance. If you have more than one validation organization in the instance, you may experience error messages when querying a price list if the inventory item is not valid in your operating unit’s OM validation organization. Thus it is best to define price lists by operating unit.
  • You can check credit within an operating unit.
  • You assign order lines and shipments to a specific warehouse (inventory organization).
  • You pick release orders and print pick slips by operating unit.
  • You ship confirm orders and print shipping documents by inventory organization. You can ship from another operating unit, even if in a different set of books.
  • You receive returned goods in the inventory organization specified on the return.
  • Inventory and Manufacturing integration functions, such as reservations, available to promise checking, sales order demand, and assemble to order job creation are processed in the appropriate inventory organization.

double-arrow Order Management /Account Receivable

  • You setup customers for the entire instance but addresses for each operating unit.
  • You merge customers and addresses within an operating unit.
  • You report supplier/customer netting within an operating unit.

double-arrowAccount Receivable

  • You setup receivable account defaults within an operating unit.
  • You produce invoices, statements, and dunning letters within an operating unit.
  • You enter and track customer commitments within an operating unit.
  • You setup bank accounts and associated cash accounts within an operating unit till Release 11i, as subsequent changes has been made in R12.
  • Till Release 11i you can enter deposits and individual receipts by bank account within an operating unit.

Similar Post

Posted in Oracle Order Management, Oracle Receivable | 2 Comments »

Receivables Integration

Posted on February 18th, 2008 by Sanjit Anand ||Email This Post Email This Post

Receivables is integrated with the following modules Cash Management, General Ledger and ipayment in Oracle Financial and have a integration with OM , Pricing , Inventory module within EBS. This can be best understood as:

AR Intergration
Lets take some of integration aspect of Oracle AR.

double-arrowProjects to Receivables

Normally invoices are transferred from Projects to Receivables for billable projects by running the PRC: Interface Invoices to Receivables request from a Projects Responsibility.

Important data in the information flow are: Customer, Line Detail.

double-arrowReceivables to Cash Management

Cash Management selects Receipts directly from the Receivables tables to reconcile with bank statements.

Important data in the information flow are: Receipt Number, Receipt Amount, Date, Customer, Application Detail.Cash Management selects Unpaid Invoices and Receipts directly from the Receivables tables as Inflow components in the Cash Forecast.

Important data in the information flow are: Amount.

double-arrowCash Management to Receivables

Cash Management directly updates Receivables tables with reconciled Receipt information. Important data in the information flow are: Amount Cleared, Receipt Cleared Date, Bank Charges, Bank Errors, Rate Gain/Loss.

double-arrowReceivables to General Ledger

Invoices (including Debit Memos and Credit Memos), Receipts (including Miscellaneous), Adjustments, and Charge backs are transferred from Receivables to the General Ledger by running the General Ledger Interface request from a Receivables Responsibility. Important data in the information flow are: Code Combination, Journal Amount.

double-arrowShared Data

The following data is shared with other modules: Customers, Banks, Payments Terms, Tax Codes, Code Combinations, Units of Measure, Items, Categories, Employees.

double-arrowReceivable Integration in OPM

The following diagram clearly explain the Receivable Integration in OPM.

OPM and AR

Posted in Oracle Receivable | 7 Comments »

AR : Back to Basic(Technical Foundation)

Posted on February 18th, 2008 by Sanjit Anand ||Email This Post Email This Post

This post is more on basic table structure of Oracle Account Receivable.

1.Table that holds AR Invoice data

The following tables can give most of the invoice information.

  • RA_CUSTOMER_TRX_ALL stores invoice header information. RA_CUSTOMER_TRX_LINES_ALL stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines.
  • The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts.
  • RA_CUST_TRX_LINE_GL_DIST_ALL stores accounting distribution records for all transaction lines except bills receivable.The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line.
  • RA_CUSTOMERS – Customer information
  • RA_CUST_TRX_TYPES_ALL – Customer Transaction Type
  • RA_CUSTOMER_TRX_LINES_ALL – Transaction Line information
  • MTL_SYSTEM_ITEMS – Base table for item

The ER Diagram for a customer Transaction can be easily understood as:


2.Customer Payment

These are the main tables which holds Customer Payment information

  • AR_CASH_RECEIPTS_ALL stores one record for each receipt entry.
  • AR_CASH_RECEIPT_HISTORY_ALL stores all of the activity that is contained for the life cycle of a receipt.
  • Each row represents one step., Possible statuses are Approved, Cleared,Confirmed, Remitted, and Reversed.
  • AR_MISC_CASH_DISTRIBUTIONS_ALL stores all accounting entries for miscellaneous cash applications.
  • AR_DISTRIBUTIONS_ALL stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments,credit memo applications, cash receipt applications, and bills receivable transactions.
  • AR_RECEIVABLE_APPLICATIONS_ALL stores all accounting entries for cash and credit memo applications.
  • Each row includes the amount applied, status, and accounting flexfield information.

The ER Diagram for a customer Payment can be easily understood as:


3. Accounting Link between the tables

  • For Invoice:

ra_customers (This is for capturing customers information)

and joins are customer_trx_id for ar_payment_schedules_all & ra_customer_trx_all & ra_customer_trx_lines_all .
ra_cust_trx_types_all.type IN (‘INV’)

  • For the Receipts:


ar_receivable_applications_all.application_type = ‘CASH’,
ar_receivable_applications_all.cash_receipt_id = ar_cash_receipts_all.cash_receipt_id,
ar_receivable_applications_all.applied_customer_trx_id = ra_customer_trx_all.customer_trx_id

Posted in Oracle Receivable | 10 Comments »

General Procurement Workflows

Posted on February 18th, 2008 by Sanjit Anand ||Email This Post Email This Post

Oracle provides Workflow technology in this operational area as a vehicle to assist companies with leveraging their investment in the applications.

Oracle supports using Workflow Builder to modify the standard workflows that manage purchasing document creation, document approvals, receipts, invoices and reminders. This post is focus on listing for such workflow which helps you to understand and how to leverage procure-to-pay workflows for operational efficiency.

S.N Workflows Name Purpose
1 Requisition Account Generator

Workflow for automatically generating accrual, budget, charge, and variance accounts on purchase orders and releases

  • Defaults charge, budget, accrual, variance
  • Check cross validation, security rules
  • Generate project, task, expenditure types
2 RequisitionApproval

Workflow for approving requisitions

  • Determine completeness
  • Verify Approval Authority
  • Notify Approvers
  • Process Responses
3 Requestor Change Order
  • Manage Requestor changes after PO Creation
  • Date, quantity, price, cancellation
  • Calls PO Change Tolerance Check
  • Process notifications and responses – Approver
  • Process notifications and responses – Buyer
4 PO Change Request Tolerance
  • Date, quantity, price, cancellation
  • Determines re-approval need on requisition
5 PO Change Order for Requestor
  • Process buyer response
  • Notify requestor
  • Initiate change order for PO
6 PO Create Documents

Workflow for automatically creating purchase orders and releases

  • Evaluate eligibility of requisition Lines
  • Group eligible requisition lines
  • Create standard orders or releases
  • Submit PO Approval Workflow
7 PO Account Generator
  • Default charge, budget, variance accounts
  • Check cross validation, security rules
  • Generate project, task, expenditure types
8 PO Approval

Workflow for approving purchase orders

  • Determine completeness
  • Verify Approval Authority
  • Notify Approvers
  • Process Responses
  • Execute Document Transmission
  • Create-Update Sourcing
9 Change Order

Workflow for controlling which changes to purchase orders require a manual reapproval and which are automatically reapproved; the change order workflow is really a group of workflow processes contained in the PO Approval workflow

  • Gather details of change
  • Determine re-approval requirements
  • Notify approver
  • Process response
  • Transmit Document
10 PO Confirm Receipts

Workflow for sending receipt notifications to requesters or buyers notifying them that they should have received their order

  • Notify requestors of overdue receipts
  • Process responses
  • Create receipts from notification response
  • Notify buyers of exceptions
11 Debit Memo Notification
  • Notify Buyer of Automatic Debit Memo Exception
12 PO Approval Error

Workflow for troubleshooting errors that occur when using the PO Approval workflow

  • Capture timeout errors (180 sec)
  • Capture DAM errors (not active)
  • Capture Exceptions
  • Notify Last Approver
  • Notify Sysadmin
  • Retry Workflow
13 PO Send Notifications

Notify requestors of incomplete requisitions

  • Notify Buyers of incomplete documents
  • Notify Buyers of past due acceptance
  • Notify Buyers of quotes expiring
  • Notify Contactor assignment ending
  • Notify amount billed near budget
14 Invoice Approval

Manage Approval Routing of Invoices (intended for non-PO)

  • Setup Rules in AME
  • Setup Routing in AME

Here are some more from other Procurement Modules

S.N … Other Procurement Modules WorkFlow Name
1 iSupplier Portal Supplier Registration
2 iSupplier Portal Supplier Change Order
3 iSupplier Portal Advance Shipment
4 iSupplier Portal PO Acknowledgement
5 Sourcing Sourcing Publish
6 Sourcing Sourcing Negotiation
7 Sourcing Sourcing Approval
8 Sourcing Sourcing Auction
9 Services Procurement Requisition Approval
10 Services Procurement PO Create Documents
11 Services Procurement Contractor Cancellation
12 Procurement Contracts Deliverables Notification
13 Procurement Contracts Contract Clause Approval
14 Procurement Contracts Contract Template Approval
15 Procurement Contracts Contract Rep. Approval

Posted in Oracle Purchasing, Technical | No Comments »

The Base Cause of ” FRM-40654 “

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

Very common problem your end user normally reports “FRM-40654: Record has been updated by another user. Re-query to see change

Cause: Another user has updated this record since you performed a query and has changed at least one field in the record. Your actions have not changed the record in memory.

Action: You can update or delete this record now only if another user has restored the field values back to the way they were when you performed the query. Otherwise, you must re-query to fetch and display the new record into the form before you can update or delete it.

This is very common issue after migration or with any external interfaces, If you have taken care by TRIM function , then there is nothing to bother, else your end user will report this, which need a proper investigation.

arrow upThe reason and resolutions

This error is caused because of any of the following

  1. FRM-40654 Caused by unnecessary foreign key fields
  2. Trapping problem of some sort in Oracle Forms with triggers, execute_query
  3. Record locked ie Another user changed the record and saved after you queried the record
  4. Some of the columns have trailing spaces, and Forms by default truncates the trailing spaces and hence the database value and form value will not match and you get the error.
  5. Date field stored in database is with time stamp but form showing only Date component and hence Form value and database value will not match and hence you get the error. (you need to remove the time component in DB or show date time in Form)

Bottom line is the value in Database and value in form are not matching, so you have to check for each column and see where is the difference. I know this is going to be painful checking out every field for changes, but there is no other options left.

What you have to do is to investigate the column first and then make a update with TRIM function as

update <table name>
set <column name> = trim(<column name>);

arrow upResolutions Example

Example 1

User reported the problem at suppliers site,

  • Taking which table hold the supplier data.
  • Check a column value for a VARCHAR2 column. In this case VENDOR_SITE_CODE. or Address_line1
    SELECT ‘[‘||vendor_site_code||’]’
    FROM po_vendor_sites_all
    WHERE vendor_id=<VENDOR ID>;
  • If the column has trailing spaces the output will looks very similar to


  • Once you get the column , then use update script, with TRIM function, like
  • UPDATE po_vendor_sites
    SET vendor_site_code = TRIM(vendor_site_code)
    WHERE vendor_id = <VENDOR ID>;

Example 2 : Your bank statement Reconcile program ending up with error

In similar way you have to check like

1. select ‘[‘||trx_text||’]’ from ce_statement_lines_interface;
2. select ‘[‘||trx_text||’]’ from ce_statement_lines;

The above mentioned scripts need to be run against all varchar2 columns, not just trx_text, in either ce_statement_lines or ce_statement_lines_interface.

If trailing spaces are found, run the following scripts to correct them:

update ce_statement_lines_interface
set trx_text=trim(trx_text,’ ‘)

Change the column and table name to those with trailing spaces

Example 3:Invoice Through Auto invoice (External system)

Same way to find the column and apply by this kind of script
update ra_customer_trx_lines_all
where customer_trx_id = <customer trx id>;

arrow upWhat if , not have “trailing space” Problem

Confirm with DBA , is table is having any lock or not . If there is no lock then you need to ask Oracle support .

arrow up

Tips of Developing Conversion and Interface

Always use TRIM function in your insert portion of the program

arrow upFurther Reading on metalink

Posted in Technical | 3 Comments »

« Previous Entries