Interfaces-New

Development &
Modifications
What to Expect from the session

After the session there will be clear understanding of

below mentioned topics
• Types of interfaces / integrations - Batch Processing + Real time
• Understanding of APIs and Standard Open interfaces
• Extracting and loading using SQL Loader, Oracle Loader and
UTL_FILE
• Understanding inbound interface requirement document - MD50
walkthrough
• Develop / walkthrough of interface built for that MD50
• Understanding outbound interface requirement document -
MD50 walkthrough
• Develop New Interfaces - (Inbound and Outbound)
• Modify Existing Oracle E-BS Interfaces
• Interface Scheduling & Debugging
• Code Packaging & Deployment
• Walkthrough of MD050, MD070 and MD120

Copyright © Capgemini 2017. All Rights Reserved 2

Topics

• Interface Overview

• Interface Types

• Oracle Interface Strategy

• Oracle Interface Example

• Order Import Interface

• Names of Standard Interface

• Interface Tables In Oracle Apps

Copyright © Capgemini 2017. All Rights Reserved 3

Oracle Interface Overview

Oracle provides 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 or to
transfer the data from one module to another module.

There are 2 types of interfaces:

1. Inbound Interface: will be used to upload the data from legacy

system (Flat files) into Oracle Applications base tables.

2. Outbound Interface: will be used to extract the data from oracle

Database tables into the flat files.

Copyright © Capgemini 2017. All Rights Reserved 4

Oracle Interface Types

There are 2 types of interfaces:

1. Real Time
2. Batch Processing

Methods of uploading and downloading the data

1. SQL loader
2. Oracle Loader
3. UTL_FILE API

Copyright © Capgemini 2017. All Rights Reserved 5

Oracle Interface Strategy

• Data Mapping
During the data mapping process, list of all the data sets and data
elements that will need to be moved into the Oracle tables as part
of conversion are identified. Data mapping tables are prepared as
part of this activity that show what are the data elements that are
needed by the target system to meet the business requirements
and from where they will be extracted in the old system.

• Download Programs
After the conversion data mapping is complete, download programs
are developed that are used to extract the identified conversion
data elements from the current systems in the form of an ASCII flat
file. The structure of the flat file must match the structure of the
Oracle standard interface tables. These flat files generated may be
in text form or a comma or space delimited, variable or fixed format
data file.

Copyright © Capgemini 2017. All Rights Reserved 6

Oracle Interface Strategy

• Upload Program
Once the data has been extracted to a flat file, it is then moved to
the target file system and the data from the file is loaded into user
defined staging tables in the target database using SQL Loader or
UTL_FILE utilities. Then programs are written and run which
validate the data in the staging tables and insert the same into the
Oracle provided standard Interface tables

• Interface Program
Once the interface tables are populated, the respective interface
program (each data element interface has a specific interface
program to run) is submitted. The interface programs validate the
data, derive and assign the default values and ultimately populate
the production base tables.

Copyright © Capgemini 2017. All Rights Reserved 7

▪ A typical path to transfer the data from Legacy System to Oracle Apps

Copyright © Capgemini 2017. All Rights Reserved 8

Interface examples and details

The below is the list of interfaces

• Order Import Interface (Sales Order Conversion)

• Customer conversion
• Item import (Item conversion)
• Inventory On-hand quantity Interface
• Customer conversion
• Auto Invoice Interface
• AR Receipts
• AP Invoices
• Vendor
• Purchase Orders
• Requisition
• Receiving
• Journal import
• Budget import
• Daily Conversion Rates

Copyright © Capgemini 2017. All Rights Reserved 9

Names of Standard Interface

Below is the list of Standard Interfaces

• Accounts Payables ( AP) Open Interfaces

• Accounts Receivables (AR) Open Interfaces
• Fixed Assets (FA) Open Interfaces
• General Ledger (GL) Open Interfaces
• Order Management ( OM) Open Interfaces
• Purchasing (PO) Open Interfaces
• Subledger Accounting ( XLA ) Transfer Journal Entries to GL Interface

Copyright © Capgemini 2017. All Rights Reserved 10

Order Import Interface (Sales Order Interface)

Will discuss one Interface in detail

• Order Import Interface (Sales Order Conversion)

Order Import enables to import Sales Orders into Oracle Apps instead of
manually entering them

Orders can be categorized based on their status:

1. Entered orders
2. Booked orders
3. Closed orders

Order Import API OE_ORDER_PUB.GET_ORDER and PROCESS_ORDER

can also be used to import orders

Copyright © Capgemini 2017. All Rights Reserved 11

Order Import Interface (Sales Order Interface)

Pre-requisites:
• Order Type
• Line Type
• Items
• Customers
• Ship Method/ Freight Carrier
• Sales Person
• Sales Territories
• Customer Order Holds
• Sub Inventory/ Locations
• On hand Quantity

Copyright © Capgemini 2017. All Rights Reserved 12

Order Import Interface (Sales Order Interface)

Interface tables:
• OE_HEADERS_IFACE_ALL
• OE_LINES_IFACE_ALL
• OE_ACTIONS_IFACE_ALL
• OE_ORDER_CUST_IFACE_ALL
• OE_PRICE_ADJS_IFACE_ALL
• OE_PRICE_ATTS_IFACE_ALL

Base tables:
• OE_ORDER_HEADERS_ALL
• OE_ORDER_LINES_ALL
• Pricing tables: QP_PRICING_ATTRIBUTES

Copyright © Capgemini 2017. All Rights Reserved 13

Order Import Interface (Sales Order Interface)

Concurrent Program:
Order Import

Validations:
• Check for sold_to_org_id. If does not exist, create new customer by
calling create_new_cust_info API.
• Check for sales_rep_id. Should exist for a booked order.
• Ordered_date should exist (header level)
• Delivery_lead_time should exist (line level)
• Earliest_acceptable_date should exist.
• Freight_terms should exist

Copyright © Capgemini 2017. All Rights Reserved 14

Important Interface Tables in Oracle Apps

GL INTERFACE TABLES

GL_BUDGET_INTERFACE
GL_DAILY_RATES_INTERFACE
GL_IEA_INTERFACE
GL_INTERFACE
GL_INTERFACE_CONTROL
GL_INTERFACE_HISTORY

AP INTERFACE TABLES

AP_INTERFACE_CONTROLS
AP_INTERFACE_REJECTIONS
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE

Copyright © Capgemini 2017. All Rights Reserved 15

Important Interface Tables in Oracle Apps

AR INTERFACE TABLES

AR_PAYMENTS_INTERFACE_ALL
AR_TAX_INTERFACE
HZ_DQM_SYNC_INTERFACE
HZ_PARTY_INTERFACE
HZ_PARTY_INTERFACE_ERRORS
RA_CUSTOMERS_INTERFACE_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_ERRORS_ALL
RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS_ALL

Copyright © Capgemini 2017. All Rights Reserved 16

Important Interface Tables in Oracle Apps

PO INTERFACE TABLES

PO_DISTRIBUTIONS_INTERFACE
PO_HEADERS_INTERFACE
PO_INTERFACE_ERRORS
PO_LINES_INTERFACE
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_RESCHEDULE_INTERFACE
RCV_HEADERS_INTERFACE
RCV_LOTS_INTERFACE
RCV_SERIALS_INTERFACE
RCV_TRANSACTIONS_INTERFACE

Copyright © Capgemini 2017. All Rights Reserved 17

Questions and Answers

Copyright © Capgemini 2017. All Rights Reserved 18

About Capgemini
With more than 180,000 people in 44 countries, Capgemini
is one of the world's foremost providers of consulting,
technology and outsourcing services. The Group reported
2014 global revenues of EUR 10.1 billion.
Together with its clients, Capgemini creates and delivers
business and technology solutions that fit their needs and
drive the results they want. A deeply multicultural
organization, Capgemini has developed its own way of
working, the Collaborative Business ExperienceTM, and
draws on Rightshore ®, its worldwide delivery model.

Rightshore® is a trademark belonging to Capgemini

[Link]

The information contained in this presentation is proprietary.

© 2012 Capgemini. All rights reserved.