Few Important AP Tables

Few Important AP Tables

AP_SUPPLIERS:

• This table replaces the old PO_VENDORS table.
• It stores information about your supplier level attributes.
• Each row includes the purchasing, receiving, invoice, tax, classification, and general information.
• Oracle Purchasing uses this information to determine active suppliers.
• The supplier name, legal identifiers of the supplier will be stored in TCA and a reference to the party created in TCA will be stored in AP_SUPPLIERS.PARTY_ID, to link the party record in TCA.

AP_SUPPLIER_SITES_ALL:

• This table replaces the old PO_VENDOR_SITES_ALL table.
• It stores information about your supplier site level attributes.
• There is a row for unique combination of supplier address, operating unit and the business relationship that you have with the supplier.
• The supplier address information is not maintained in this table and is maintained in TCA. The reference to the internal identifier of address in TCA will be stored in AP_SUPPLIER_SITES_ALL.LOCATION_ID, to link the address record in TCA.
• Each row includes the supplier reference, purchasing, invoice, and general information.

AP_INVOICES_ALL:

• It contains records for invoices you enter.
• There is one row for each invoice you enter.
• An invoice can have one or more invoice distribution lines and can have one or more scheduled payments.  

AP_INVOICE_LINES_ALL:

• It contains records for invoice lines entered manually, generated automatically or imported from the Open Interface.
• An invoice can have one or more invoice lines.
• An invoice line represents goods (direct or indirect materials), service(s), and/or associated tax/freight/miscellaneous charges invoiced from a supplier.
• An invoice line should contain all the attributes that are present on the physical or electronic invoice presented by the supplier.

AP_INVOICE_DISTRIBUTIONS_ALL:

• It holds the distribution information that is manually entered or system-generated.
• There is one row for each invoice distribution and a distribution must be associated with an invoice.
• An invoice can have multiple distributions.

AP_INVOICE_PAYMENTS_ALL:

• It contains records of invoice payments that you made to suppliers.
• There is one row for each payment you make for each invoice and there is one payment and one invoice for each payment in this table.
• Oracle Payables application updates this table when you confirm an automatic payment batch, enter a manual payment, or process a Quick payment.
• When you void a payment, your Oracle Payables inserts an additional payment line that is the negative of the original payment line.

AP_PAYMENT_SCHEDULES_ALL:

• This table stores information about scheduled payment information on invoices.

AP_PAYMENT_HISTORY_ALL:

• It stores the clearing/unclearing history for payments.
• It also stores the maturity history for future dated payments.
• The table contains a row for each future dated payment, once the future dated payment matures, i.e. becomes negotiable.
• Any time a payment is cleared or uncleared, a row is inserted into this table for the payment.

AP_BATCHES_ALL:

• It contains summary information about invoices you enter in batches if you enable the Batch Control Payables option.
• There is one row for each batch of invoices you enter.
•  If you enable Batch Control, each invoice must correspond to a record in this table.
•  Your Oracle Payables application uses this information to group together invoices that one person entered in a batch.

AP_CHECKS_ALL:

• It stores information about payments issued to suppliers or refunds received from suppliers. 
• There is one row for each payment you issue to a supplier or refund received from a supplier.
• Oracle Payables application uses this information to record payments you make to suppliers or refunds you receive from suppliers.
• Oracle Payables application stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment. Oracle Payables application also stores address information for all payments.

AP_HOLDS_ALL:

• It contains information about holds that you or your Oracle Payables application place on an invoice.
• For non-matching holds, there is one row for each hold placed on an invoice. For matching holds, there is one row for each hold placed on an invoice-shipment match.
• An invoice may have one or more corresponding rows in this table.
• Your Oracle Payables application does not pay invoices that have one or more unreleased holds recorded in this table.

AP_BANK_ACCOUNTS_ALL:

• It contains information about your bank accounts.
• There is one row for each bank account you define and each bank account must be affiliated with one bank branch.

AP_BANK_ACCOUNT_USES_ALL:

• It stores information for the internal and external bank accounts you define in Oracle Payables and Oracle Receivables applications.

AP_CARDS_ALL:

• It stores information about the corporate credit cards issued to your employees by your corporate credit card providers.

AP_TRIAL_BALANCE:

• It contains denormalized information about invoices and payments posted to the accrual set of books.

Trading Community Architecture (TCA) in Oracle Apps

Trading Community Architecture (TCA) in Oracle Apps

 

Before TCA:

• There are multiple customer definitions across the enterprise.
• It was very difficult to track current and historical information about the customers.
• There was a lack of support for mixed business.
• It was quite tough to understand relationships between customers and others (suppliers, partners, competitors).

Customers: More important than anything else!

In any business, Customers and their data are always important. More than that what is important is to understand who your customer interacts with inside and outside the enterprise.

What is Trading Community?

The summation of all entities, inclusive of partners, suppliers, and competitors, that are related to your customers is called a Trading Community.

Trading Community Architecture:

Trading Community Architecture is the implementation of technology and applications to allow users to create and maintain relationships among entities. It is a way to understand who your customer interacts with inside and outside the enterprise.

It’s Main Purpose:

• Create a central repository for the entire E-Business Suite to store information relating to all members of a trading community versus separate tables for each member-Prospects, Customers, Contacts, Employees, Partners, Distributors, Suppliers, Banks, etc.
• Record complex business relationships between Trading Community entities (including 3rd party relationships).
• Support all business models, industries, and geographies.

TCA Data Model Components:

1] Party:

It represents any entity that can enter into business relationships with your organization – Organization, Person, or Group.

• Person – A unique individual (dead or alive) of interest to the user.
• Organization  – A legal entity recognized by some government authority.
• Group  – A combination of two or more people, organizations or groups.

2] Party Relationship:

It is a binary relationship between two parties such as a partnership.

• Has a Role – Specifies the nature of the relationship between parties (e.g., member of, contact at, married to).
• Indicates the Nature of the relationship – hierarchy or matrix.
• Indicates the Direction of the relationship – superior – subordinate.
• Can become a Party – a Relationship becomes a party in itself.

The relationship model enables you to:

• Understand the complex relationships among members of your trading community
• Use this information to make better business decisions

3] Location:

A Location is a point in geographical space described by a street address. In previous releases of Oracle, there was a risk of some data redundancy if more than one customer shared the same site or location. The new model eliminates this redundancy.

• Any number of location types can be defined. (e.g., bill-to, ship-to, mail-to).
• There is no duplication of an address.
• It is possible to maintain Customer History per address.
• It is also possible to maintain Important Install Base info.

4] Party Site:

It links a Party with a Location

• Describes the usage of that Location for the Party  (e.g., mailing address, billing address, home address, etc.).
• Allows Parties to be associated to one or more Locations and any one Location to be associated with Parties.

5] Contact:

A Contact is a person in the context of an organization, modeled as a relationship between an organization and a person or between two people, (this can be either a party contact or an account contact).

6] Contact Point:

A Contact Point is a means of contacting a party, for example, a phone number, e-mail address, or fax number.

This can be applied to:

• A Party (person, organization, group or relationship)
• A Site or Location
• A Party at a Site or Location

An entity may have one or more Contact Points.

7] Customer Account:

A Customer Account represents the business (selling) relationship that a company deploying Oracle Applications has with a party.

• Stores details about the Financial relationship between a Party and your business.
• A Party may have one or more Customer Accounts.

8] Customer Account Site:

A Customer Account Site is a party site that is used by a customer account, for example, for billing or shipping purposes.

9] Customer Account Contacts:

A party contact that is used as a means of contacting the customer regarding his/her account.

Parties vs. Accounts

• From an application perspective, one of the most important things to understand about the TCA model is that the concept of “customer” is separated into two layers: The Party layer and the Account layer. 
• When CRM applications refer to “Customer” they are referring to the Party Layer.
• On the other hand, when ERP applications refer to “Customer” they are referring to the Account Layer. 

New Trading Entities in R12

Below are the new entities that are merged in TCA architecture in R12.

• Banks & Bank Branches
• Suppliers
• Legal Entity

 Know More: 

What Happened to My Customers? (Trading Community Architecture) by Jeannie Dobney

Interfaces in Oracle Application: An Introduction

Interfaces in Oracle Application: An Introduction

 

What are Interfaces?

• Interfaces are used in Oracle Applications to integrate external systems and Data Conversion.
• The interfaces are mainly used to either transfer data from Oracle Applications to a flat file or data from legacy system to Oracle Applications.
• Used extensively at the time of Data Conversion from legacy/ old systems to a fresh implementation of Oracle Applications.
• Used also at regular intervals when data transfer is from other live systems if the systems are not defined in Oracle Applications implementation.
• Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications.

Types of Interfaces

There are two major types of Interfaces:

• Inbound Interface : These interfaces are used to transfer data from external systems to Oracle Applications.
• Outbound Interface :  These interfaces are used to transfer data from Oracle Applications to external systems.

Two other distinctions of Interfaces:

• Open Interface: If the interface logic is provided by Oracle Applications, it is called an Open Interface.
• Custom Interface: If the interface logic needs to be developed by the implementation team, it is called a Custom Interface.

Interface Components

Open Interface Logic

• First the data from the source application is loaded into a database table (called Interface table).
• Then the provided validation program logic validates the records whether they are correct or not .
• If the validation fails, the errors are transferred into another table (called Error Table).
• If the validation succeeds, the correct records are transferred through a process into the destination application table.

Components of an Interface

a] Source Application:

You obtain data from a source application to pass on to a destination application for further processing and/or storage.

b] Source Data Issues:

Type of file, Size, Frequency of upload, Record Length (Variable or fixed), Delimiter, Datatype for each field, Any unwanted data, Naming convention and uniqueness of file, Location of the file, Access on the file.

c] Destination Application:

You send data to a destination application so that the application can perform further processing and/or storage.

d] Interface Table:

For inbound interfaces, the interface table is the intermediary table where the data from your source application temporarily resides until it is validated and processed into the destination application.

e] Identifier columns:

Uniquely identify rows in the interface table provide foreign key reference to both the source and destination applications.

f] Control Columns:

• Control columns track the status of each row in the interface table, as it is inserted, validated, rejected, processed, and ultimately deleted.
• WHO columns are also control columns.

g] Data Columns:

• Stores the data that is being converted.
• Required columns store the minimum information needed by the destination application to successfully process the interface row.

h] Derived Columns:

Derived columns are created by the destination application from information in the required columns.

i] Optional Columns:

Optional columns are not necessarily required by the destination application, but can be used by the destination application for additional value-added functionality beyond the basics.

j] Error Table:

• For inbound interfaces, the errors table stores all errors found by the validation and processing functions.
• In some cases, the errors table is a child of the interface table. This allows each row in the interface table to have many errors, so that you can easily manage multiple errors at once.
• In other cases, the errors are stored in a column within the interface table, which requires you to fix each error independently.

Developing an Interface

1] Identification:

Find out if there exists an Open Interface to carry out the functionality.

2] Creation of Pre-Interface table ( staging Table):

A table in the format of the data file which can be pruned to load as clean a data into the Interface table.

3] Load data into Pre-Interface table:

SQL*LOADER can be used to load the flat file into the pre-interface table.

4] Validate data in the Pre-Interface table:

Basic validation of the data loaded into the Pre-Interface table can be carried out like:

• For checking NULL values in required columns
• Checking for Foreign Key and Quick Code values.
• Duplication Validation
• Business Rule validation

5] Mapping the values:

Generated fields in Oracle Applications can be mapped in this step to either default values or sequences.

6] Load data into Interface table:

• Once the data is as clean as you can get it, the data can be inserted into the Interface table.
• At such a time, certain columns, which are necessary in Applications but not found in legacy system, need to be populated accordingly like WHO columns.

7] Run the interface program

8] Check for Errors

9] Report on the Interface

Oracle Applications R12 Architecture

Oracle Applications R12 Architecture

The Oracle E-Business Suite Release 12 Architecture is a framework for multi-tiered, distributed computing that supports Oracle Applications products.

Architecture-R11i vs R12

In EBS R12, various servers or services are distributed among the following three levels, or tiers.

• The Desktop Tier
• The Application Tier
• The Database Tier

Oracle E-Business Suite Release 12 Architecture

1] The Desktop Tier

The client interface is provided through HTML for HTML-based applications, and via a Java applet in a Web browser for the traditional Forms-based applications.

In Oracle Applications Release 12, each user logs in to Oracle Applications through the E-Business Suite Home Page on a desktop client web browser. The E-Business Suite Home Page provides a single point of access to HTML-based applications, Forms-based applications, and Business Intelligence applications.

Oracle JInitiator will no longer be required to run Oracle Forms in E-Business Suite Release 12.  Oracle Forms in Release 12 will run directly in the native Sun Java2 Standard Edition plug-in.

The Forms client applet is a general-purpose presentation applet that supports all Oracle Applications Forms-based products, including those with customizations and extensions. The Forms client applet is packaged as a collection of Java Archive (JAR) files. The JAR files contain all Java classes required to run the presentation layer of Oracle Applications forms.

2] The Application Tier

The application tier has a dual role: hosting the various servers and service groups that process the business logic, and managing communication between the desktop tier and the database tier. This tier is sometimes referred to as the middle tier.

Four servers or service groups comprise the basic application tier for Oracle Applications:

• Web services
• Forms services
• Concurrent Processing server
• Admin server

3] The Database Tier

The database tier contains the Oracle database server, which stores all the data maintained by Oracle Applications. The database also stores the Oracle Applications online help information. More specifically, the database tier contains the Oracle data server files and Oracle Applications database executables that physically store the tables, indexes, and other database objects for your system. The database server does not communicate directly with the desktop clients, but rather with the servers on the application tier, which mediate the communications between the database server and the clients.

ERP Overview

What is ERP?

Enterprise Resource Planning or ERP is an industry term for integrated, multi-module application software packages that are designed to serve and support multiple business functions.
ERP provides the backbone for an enterprise-wide information system.
Evolving out of the manufacturing industry, ERP implies the use of packaged software rather than proprietary software written by or for one customer.
At the core of this enterprise software is a central database which draws data from and feeds data into modular applications that operate on a common computing platform, thus standardizing business processes and data definitions into a unified environment.

Brief History of ERP

The focus of manufacturing systems in the 1960’s was on Inventory control. Most of the software packages then (usually customized) were designed to handle inventory based on traditional inventory concepts. In the 1970’s the focus shifted to MRP (Material Requirement Planning) systems that translated the Master Schedule built for the end items into time-phased net requirements for the sub-assemblies, components and raw materials planning and procurement.

In the 1980’s the concept of MRP-II (Manufacturing Resources Planning) evolved which was an extension of MRP to shop floor and Distribution management activities. In the early 1990’s, MRP-II was further extended to cover areas like Engineering, Finance, Human Resources, Projects Management etc i.e. the complete gamut of activities within any business enterprise. Hence, the term ERP (Enterprise Resource Planning) was coined.

Why is it Necessary?

By becoming the integrated information solution across the entire organization, ERP systems allow companies to better understand their business.
With ERP software, companies can standardize business processes and more easily enact best practices.
By creating more efficient processes, companies can concentrate their efforts on serving their customers and maximizing profit.

Market Leaders

The top five ERP vendors, SAP, Oracle Corporation, Peoplesoft, Inc. (now Oracle Corp.), JD Edwards & Company, and Baan International, account for 64 percent of total ERP market revenue. These vendors continue to play a major role in shaping the landscape of new target markets, with expanded product functionality, and higher penetration rates. SAP dominates the $6.7 billion ERP applications market in Europe with 39% market share. Oracle and PeopleSoft come second and third respectively, followed by SAGE Group and Microsoft Business Solutions.

Overview of popular ERP Products

Oracle E- Business Suite

Oracle E-Business Suite is the industry’s only complete and integrated set of enterprise applications, working together seamlessly to streamline every area of your business—from sales, service, and marketing, through financials and human resources, to supply chain and manufacturing.

Oracle E-Business Suite is your fastest path to high-quality enterprise intelligence, bringing your company a true 360-degree view of your finances, your customers, and your supply chains, so you can make faster, better decisions and grow profitability in a competitive marketplace.

Oracle E- Business Suite – Industry Applications

Oracle E-Business Suite 11i.10 offers over 2,100 new capabilities, half of which meet specific industry needs, including:

Financial Services: SOP documentation and auditing for compliance with Sarbanes-Oxley and other regulations

Healthcare: Medication administration, patient encounter-specific financial information, integrated patient care and operational intelligence

Manufacturing/High Technology: Option-dependent sourcing, automated spare parts return and repair processing, international drop shipments, distribution planning

SAP

What is SAP?

SAP (Systems, Applications, and Products in Data Processing) is the leading ERP (Enterprise Resource Planning) software package.
SAP was the first to integrate a corporation’s worldwide functions tightly into one application.
SAP R/2 was released as the first version of their software in 1979. Its domination of the market occurred during the 1980s, expanding first throughout Europe (early 1980s) and then North America (1988).
SAP R/3, an advanced, client-server based version of the popular R/2 product, was released in 1992 and sparked a stunning takeover of America’s largest businesses — 44% of US companies were using it within five years of its expansion.

In 1999, SAP introduced its newest major product upgrade. Named mySAP.com to emphasize its shift to an e- business focus, mySAP.com builds on the strengths of the SAP R/3 product in a fully Internet enabled architecture

SAP R/3 Overview

SAP R/3 is SAP’s integrated software solution for client/server and distributed open systems. SAP’s R/3 is the world’s most-used standard business software for client/server computing. The software is highly customizable using SAP’s proprietary programming language, ABAP/4.
R/3 is scalable and highly suited for many types and sizes of organizations. SAP R/3 is based on various hardware and software architectures, running on most types of UNIX, on Windows NT and OS/400. SAP R/3 runs on several databases Oracle, Adabas D, Informix, DB2 for UNIX, DB2/400, and Microsoft’s SQL Server 6.0.

mySAP ERP

mySAP ERP combines the world’s most complete, scalable, and effective software for enterprise resource planning (ERP) with a flexible, open technology platform that can leverage and integrate SAP and non-SAP systems. It provides end-to-end software functionality for enterprise management and support — plus support for systems management — all powered by the SAP NetWeaver platform.

mySAP ERP comes with four individual solutions that support key business processes:
mySAP ERP Financials
mySAP ERP Human Capital Management
mySAP ERP Operations
mySAP ERP Corporate Services

PeopleSoft

PeopleSoft Enterprise is a suite of best-in-class applications built on our Pure Internet Architecture and designed for the most complex business requirements. Ideally suited for large, company-wide functions across all industries, it provides industry-leading web services integration with multi-vendor and homegrown applications. PeopleSoft Enterprise can be easily configured and adapted to meet the most unique customer requirements. In addition, it supports the broadest choice of technology infrastructure in the industry.

Campus Solutions
Customer Relationship Management
Financial Management
Human Capital Management
Service Automation
Supply Chain Management
Enterprise Tools and Technology
Siebel

What Is Siebel?

Siebel Systems is an eBusiness solutions company and a market-leading provider of Customer Relationship Management (CRM) software.
Tom Siebel (CEO) and Pat House (Executive Vice President) founded Siebel Systems in July 1993. Headquartered in San Mateo, California.

Siebel’s initial product set out to organize and automate the sales force with calendars and contact management systems. It has since added numerous other Customer Service, Marketing and e-Commerce applications to its product line.

Siebel Product Overview

Siebel’s applications allow companies to access information about their customers whenever they need it. Siebel’s main product line includes its Call Center, Field Services and Sales, and Marketing applications, which allow employees located in different parts of a company to have access to the information they need to assist, service and make additional sales to customers.

Advantages of Oracle E-Business Suite

Oracle ERP Suite is based on Oracle Apps framework. What that essentially means to the customers is that no matter which component of the suite you buy, you get the same look and feel.

Oracle Database

The biggest USP of Oracle ERP is that it is based on Oracle Database, which in itself is a market leader. So the Oracle ERP is best placed to take advantage of the advances in the database technology. The buzzword is “If it is in the database, you will get it and get it fast!!”

Consultants

As far as the components of the suite itself are concerned Oracle Financials is a market leader, Oracle HRMS and Payroll they are catching up pretty fast. So the market indications are pretty positive and you have enough consultants in the market to cash in.

UI

Oracle Apps UI comes in two flavours – The Oracle Forms and the Oracle Self Service Application (HTML). The former is meant for the power user and provides incredibly quick learning curves for people from the finance and other domains who are used to Fox pro and dBase forms.

The Oracle Self Service Application is an HTML based solution and specifically caters to the global organization where Internet plays a major role.

Role Based Security

Oracle has been a pioneer in Database Security systems and the buzz word of Oracle Database is “Unbreakable”. Oracle Apps seem to gain from this legacy of secure systems. The role based security system offered by Oracle Apps is one of the better security models available currently.

Advantage over Best of Breed Software

Best of breed software cater to specific areas like HRMS, Financials etc. If your business is looking for software in only one specific area and you don’t think s/w for the other areas are required, you might be tempted to go for these offering… But one word of caution, in case you go for s/w for other areas where you select another best of breed s/w, you might end up paying exceptional amount of money in integrating the two softwares.

This is where Oracle Apps and other Suite vendors like SAP score over the best of the breed software. Implementing different parts of the suite would be much less costly than best of breed software and getting them to talk to each other.

Fast adoption of standards

By past record Oracle has a good record of adoption of new legal and technological standards which keep arising from time to time. So standards like Rosettanet, uccnet would be adopted as they solidify.

Ease of customization and installation

Oracle has a good record on supporting products. Oracle Apps are quite easy to install and customize according to your business needs.