HOW AND WHEN COGS ACCOUNTS HITS FOR TRANSACTION

HOW AND WHEN COGS ACCOUNTS HITS FOR TRANSACTION

Inventory Interface generates the cost of goods sold account for transactions when passing the transactions to Oracle Inventory. Receivables Interface generates a receivable account, a revenue account, a tax account, a freight account, and others, but not the cost of goods sold account. Ship confirm and pick release do not generate any accounts. 
Oracle Application R12 COGS 

In Oracle Application R12 COGS process has been changed. Reason for that are aggressive revenue
recognition practices as well as the guidelines from various governing bodies.

Till R11 Cost of goods sold has been recognized as soon as the Order line has shipped, as shown in below
steps

After ship confirm, user run the interface trip stop (ITS).
ITS in turns run the OM Interface and Inventory Interface.
Inventory Interface calls Inventory transaction manager which in turns call COGS WF.
But as per new practices COGS should be recognized along with the revenue.

In R12 used need to define deferred cogs account. These deferred cogs account can be defined at each
inventory org level.

During shipping process Inventory tables will hold the deferred COGS accounts. Only after invoicing has
done in AR, AR will notify the Costing and Costing in turns call the COGS account generator to get the
cogs account .In that way COGS and revenue will be recognized in the same period.

There are few exceptions like how to get the COGS for 
1. Ship only line (No Invoice will be created).  
To handle above cases Close-line activity of the order line workflow has modified to call the costing API
to get the cogs value  
What is the Deferred COGS account in R12 
The deferred COGS of goods account is the new feature introduced in Release 12. The basic
fundamental behind the enhancement is that the COGS is now directly matched to the Revenue. The
same was not possible till now.

Prior to this enhancement, the value of goods shipped from inventory were expensed to COGS upon
ship confirm, despite the fact that revenue may not yet have been earned on that shipment. With this
enhancement, the value of goods shipped from inventory will be put in a Deferred COGS account. As
percentages of Revenue are recognized, a matching percentage of the value of goods shipped from
inventory will be moved from the Deferred COGS account to the COGS account, thus synchronizing 
the recognition of revenue and COGS in accordance with the recommendations of generally accepted
accounting principles.

The Matching Principle is a fundamental accounting directive that mandates that revenue and its
associated cost of goods sold must be recognized in the same accounting period. This enhancement will
automate the matching of Cost of Goods Sold (COGS) for a sales order line to the revenue that is billed
for that sales order line.

The deferral of COGS applies to sales orders of both non-configurable and configurable items (Pick-To Order and Assemble-To-Order). It applies to sales orders from the customer facing operating units in the 
case of drop shipments when the new accounting flow introduced in 11.5.10 is used. And finally, it also
applies to RMAs that references a sales order whose COGS was deferred. Such RMAs will be accounted
using the original sales order cost in such a way that it will maintain the latest known COGS recognition percentage.
To set the deferrred COGS account.
 Inventory –Setup–Organization–Parameters–Other Accounts

A new account is added which is referred as the Deffered COGS accounts.

NEW ACCOUNTING:

Release 12 :
  
When a Sales order is shipped the following accounting takes place:

Inventory Valuation Account : Credit.  
Deferred COGS account : Debit

Once the revenue is recognised, you would need to decide the percentage you wish to recognize the
Revenue. A COGS recognition transaction will be created to reflect a change in the revenue recognition
percentage for a sales order line.

The steps to generate such transactions are as follows: 
1. Run the Collect Revenue Recognition Information program. This program will collect the change in
revenue recognition percentage based on AR events within the user specified date range. 
2. Run the Generate COGS Recognition Events. This program will create the COGS recognition
transaction for each sales order line where there is a mismatch between the latest revenue recognition
percentage and the current COGS recognition percentage.

Note that users can choose how often they want to create the COGS Recognition Events. 

Navigation to run the COGS recognition request : 
– Cost > COGS Recognition > Collect Revenue Recognition Information 
– Cost > COGS Recognition > Generate COGS Recognition Events 
– Cost > View Transactions > Material Transactions

The distribution for the COGS Recognition transaction associated with the Sales Order transaction now
would be as follows:

Deffered COGS : Debit y revenue percentage 
COGS : Credit (Actual revenue percentage )

Thus, essentially the recognized COGS balance is to move the value from Deferred COGS to COGS.

This particular COGS recognition transaction actually correspond to a revenue recognition percentage
change.

You can view the transactions as : 
Navigation: 
– Cost > View Transactions > Material Transactions > Distributions

A new COGS Revenue Matching Report shows the revenue and COGS information of sales order that fall
within the user specified date range by sales order line

SIMPLER TERMS ( Table level details ) :

Once the whole cycle is complete we will have 2 transactions lines in mtl_material_transactions.

1. Sales Order 
2. COGS Recognition transaction

Accounting will be in mtl_transaction_accounts and the Subledger accounting tables as follows: 

Transaction 1: 
Inventory Valuation Account : Credit. (item_cost) 
Deferred COGS account : Debit (item_cost)

Transaction 2: 
Deffered COGS : Credit (Actual revenue percentage) 
COGS : Debit (Actual revenue percentage )

COGS (Cost of Goods Sold) in Oracle E-Business Suite Release 12 
Deferred COGS is a new feature introduced in Oracle E-Business Suite Release 12. The basic
fundamentals behind the enhancement are that the COGS are now directly matched to the
Revenue. 
  
Prior to this enhancement, the value of goods shipped from inventory were expensed to COGS
upon ship confirm, despite the fact that revenue may not yet have been earned on that shipment.
With this enhancement, the value of goods shipped from inventory will be put in a Deferred
COGS account. As percentages of Revenue are recognized, a matching percentage of the value
of goods shipped from inventory will be moved from the Deferred COGS account to the COGS
account, thus synchronizing the recognition of revenue and COGS in accordance with the
recommendations of generally accepted accounting principles. 
  
While this helps solve some key accounting issues, there are some key issues one needs to be
aware of: 
    
• Currently Deferred COGS accounting cannot be turned off in Oracle EBS Release 12. 
• The activity of recording COGS recognition is now a multi-step process 
• Run AR Revenue Recognition, and Submit Accounting Processes 
• Run a set of concurrent processes in Cost Manager to record Sales Order and revenue
recognition transactions and to create and cost COGS recognition transactions. These
COGS recognition transactions adjust deferred and earned COGS in an amount that
synchronizes the % of earned COGS to earned revenue on Sales Order shipment lines. 
• Record Order Management Transactions: records new sales order transaction activity
such as shipments and RMA returns in Oracle Order Management. 
• Collect Revenue Recognition Information: determines the percentage of recognized or 
earned revenue related to invoiced sales order shipment lines in Oracle Receivables. 
• Generate COGS Recognition Events: creates and costs COGS recognition events for
new sales order shipments/returns and changes in revenue recognition and credits for
invoiced sales order shipment lines. 
The end result of these activities is a series of COGS Recognition Material Distributions.
However these distributions will not be visible on the Material Transaction screen, unless the
‘Include Logical Transaction’ checkbox is checked.

R12 Order Management: Revenue-COGS Matching Part I 
It is a relief to see this much awaited functionality. We heard our accounting departments
complaining about the period mismatches in for our COGS and Revenue accounting for one
order. We ship an order on the last day of the month and COGS gets posted to this month, but if
the invoice is created with next period’s GL date as the current AR period is closed by the time
the order is invoiced. Now all that is changed. Revenue-COGS matching is a standard
functionality now. In simple terms, this means, COGS for an order line will be recognized only if
the revenue is recognized for that line making sure that the revenue and COGS are posted in the
same month. 
All of us have spent a lot of time working on COGS accounting workflow to achieve what we
want for our clients/companies. In some cases we even customized Revenue accounting
generation (avoiding auto accounting logic) by using ra_interface, distributions_all table. We had
a handle on accounts generation in this process but not on the actual events of accounting
recognition. 
We all know this.
When we ship the order and run the Interface Trip Stops program, inventory gets reduced and
orders get updated to move forward in the workflow to the next activity. Interface Trip Stops
program calls the OE_FLEX_COGS_PUB to generate the COGS account as per design. This
gets passed on to the mtl_material_transactions table in the distribution_account column. When
Cost Manager runs, distribution_account from mtl_material_transactions is picked up to generate
accounting as shown. 
                                Cr Inventory Material account $100
                                            Dr COGS Account $100 
The role of COGS
workflow is not changed. It is still the same which generates the account of our choice per
workflow design. It still passes the generated account to the mtl_material_transactions table into
the distribution_account column. But what changed in R12 is accounting. In order to match
Revenue with COGS accounting in terms of timing, COGS account cannot be used at the time
shipping. Instead revenue recognition process of the invoice for that order line should generate
COGS accounting. 
To achieve this, a new account called Deferred COGS account is introduced at the inventory
organization parameters level. So when the order shipped instead of the above entries the entry
will be 
                       Cr Inventory Material account             $100
                                    Dr Deferred COGS Account    $100 
When you invoice is this order line, if you have no revenue recognition policies or specialized
accounting rules, revenue should be instantly recognized (upon running revenue recognition
program). 
After revenue is recognized, the following programs need to be run to relieve deferred COGS
value and debit actual COGS account. 
Record Order Management Transactions: This program collects all the transactions that
belong to transaction types Sales order issue and Logical Sales Order Issue which are not costed
and the order line is invoiceable. The source table is mtl_material_transactions. This program 
inserts rows into two tables: cst_cogs_events and cst_revenue_cogs_match_lines. This program
is not necessary to run. When not run, Cost Manager will insert rows into these tables. So from
implementation considerations, this program is not required to be run. 
Collect Revenue Recognition Information: This program collects invoice line information of
the order line after the revenue is recognized. The source tables are ra_customer_trx_lines_all
and ra_cust_trx_line_gl_dist_all. It will check the percentage of the revenue recognized (we can
recognize revenue partially for a specific order line based on accounting rule or contingency
rules) and inserts that information into this table: cst_revenue_recognition_lines. Also the table
cst_revenue_cogs_control table is updated with the latest run date with high date of this
parameter, which is used in the next run of the same program. 
Generate COGS
Recognition Events: The role of this program is to record a logical material transaction, which
is used to create final COGS entry. This program takes information from the above tables and
creates one logical inventory transaction in mtl_material_transactions with a new transaction
type called COGS Recognition. In the same program these transactions will be costed (not by the
cost manager) creating the following accounting entries. The COGS account in this entry is taken
from the distribution_account in mtl_material_transactions table (which was generated earlier by
COGS workflow). 
                                Cr Deferred account                     $100
                                              Dr COGS Account             $100 
This is the concept in simple terms. There are different cases (well documented in the Cost
Management User Guide) in this same flow which, I will take one at a time to discuss in the
coming posts. 
SQL statements that help understand the data model are below, 
SELECT header_id
  FROM oe_order_headers_all
 WHERE order_number = &your_order_number;

SELECT line_id
  FROM oe_order_lines_all 
 WHERE header_id = (SELECT header_id
                      FROM oe_order_headers_all
                     WHERE order_number = &your_order_number);

SELECT *
  FROM mtl_material_transactions
 WHERE trx_source_line_id IN (SELECT line_id
                                FROM oe_order_lines_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number =
&your_order_number))
       AND transaction_type_id IN (33, 10008);

SELECT *
  FROM mtl_transaction_accounts
 WHERE transaction_id IN (
          SELECT transaction_id
            FROM mtl_material_transactions
           WHERE trx_source_line_id IN (SELECT line_id
                                          FROM oe_order_lines_all
                                         WHERE header_id = (SELECT header_id
                                                              FROM
oe_order_headers_all
                                                             WHERE
order_number = &your_order_number))
             AND transaction_type_id IN (33, 10008));

SELECT *
  FROM cst_revenue_cogs_match_lines
 WHERE cogs_om_line_id IN (SELECT line_id
                             FROM oe_order_lines_all
                            WHERE header_id = (SELECT header_id
                                                 FROM oe_order_headers_all
                                                WHERE order_number =
&your_order_number));

SELECT *
  FROM cst_cogs_events
 WHERE cogs_om_line_id IN (SELECT line_id
                             FROM oe_order_lines_all 
                            WHERE header_id = (SELECT header_id
                                                 FROM oe_order_headers_all
                                                WHERE order_number =
&your_order_number));

SELECT *
  FROM cst_revenue_cogs_control;

SELECT *
  FROM ra_customer_trx_lines_all
 WHERE interface_line_context = ‘ORDER ENTRY’
   AND interface_line_attribute6 IN (SELECT line_id
                                       FROM oe_order_lines_all
                                      WHERE header_id = (SELECT header_id
                                                           FROM
oe_order_headers_all 
                                                          WHERE order_number
= &your_order_number));

SELECT *
  FROM ra_cust_trx_line_gl_dist_all
 WHERE customer_trx_line_id IN (
          SELECT customer_trx_line_id
            FROM ra_customer_trx_lines_all
           WHERE interface_line_context = ‘ORDER ENTRY’
             AND interface_line_attribute6 IN (SELECT line_id
                                                 FROM oe_order_lines_all
                                                WHERE header_id = (SELECT
header_id
                                                                     FROM
oe_order_headers_all
                                                                    WHERE
order_number = &your_order_number))
             AND account_set_flag = ‘N’
             AND account_class = ‘REV’);

SELECT *
  FROM cst_revenue_recognition_lines
 WHERE revenue_om_line_id IN (SELECT line_id
                                FROM oe_order_lines_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number =
&your_order_number));

SELECT *
  FROM mtl_transaction_accounts
 WHERE transaction_id IN (
          SELECT transaction_id
            FROM mtl_material_transactions
           WHERE trx_source_line_id IN (SELECT line_id
                                          FROM oe_order_lines_all
                                         WHERE header_id = (SELECT header_id
                                                              FROM
oe_order_headers_all
                                                             WHERE 
order_number = &your_order_number))
             AND transaction_type_id IN (33, 10008));

Oracle Apps R12 Technical Course Details

Oracle Apps R12 Technical Course Details

Oracle Apps Technical is the Enterprise Resource Package (ERP) package. It has designed on the base of Generally Acceptable Accounting Principles (GAAP). This Oracle Application has the business software or else application software of the Oracle Corporation.

Oracle Apps Technical Course is a collection of complete end-to-end solutions that serve businesses with their own functionality. The course provides an in-depth explanation on the technical module of data integration. This module makes you understand the architecture, table relations, designing reports and forms using Oracle Apps.

From this course, you get a clear idea of

Types of Roles, Application Object Library, Application Development, Registration of Custom Objects, Interfaces and Conversions, Flex fields, Profiles, Value Sets, Multi-org Concepts, Functional Modules Overview, Reports and Forms in Oracle Applications and development of XML Reports.

There are incredible job opportunities for the desired aspirants with highest paid salary range. It will be a great career advantage by taking Oracle Apps Technical Course.

Who should take this oracle Apps technical course

The following software or industry professionals were deserved to take this Oracle Apps technical training.

senior architecture and developers java and non java background. senior IT Software professionals Freshers

The following job roles will get benefited from this course:

Functional Consultants Technical Consultants Business Analysts Finance Functional Implementers End Users Candidates interested in taking up a career under Oracle Related Jobs and Positions in the Industry

Oracle Apps R12 Financials Course Details 

Oracle Apps R12 Financials is software product offered by Oracle corporation. Oracle R12 Financials Applications have great features to manage Organization's Financial and accounting information. With Oracle Financials Applications Organization's can track detailed business transaction data, same data can be used for reporting and decision making purpose.

Oracle Apps R12 Financials consist of below applications:

Oracle General Ledger

Oracle Accounts Payable

Oracle Accounts Receivable

Oracle Fixed Assets

Oracle Cash Management

From this course, you get a clear idea of

Introduction to ERP, Versions in Oracle Apps, Modules in Oracle Application, What is Implementation Process, General Ledger, Introduction to GL Module, Key Flex Fields, Segments, Design Charts of Accounts, Value Sets, Define Currency and Calendar, Define Primary Ledger, Creation of Journals, Open/Close Periods, Reverse Journals, Journal Batch, Recurring Journals, Suspense Journals, Statistical Journals, Mass Allocation, Budgets, Inter-company Journals, Journal Approval Process, Tax Journals, Currency Translation, Reporting Currency, Revaluation, Consolidation, Cross Validation Rules, Standard Report (Trail Balance), FSG (Financial Statement Generator), Account Alias, Security Rules, Access Set, Purchasing overview, Financial Option, Overview of Procure to Pay Process, Purchasing Options, Requisitions, Document Types, RFQs, Quotations, Purchase Orders, Receipt Creation, Matching Options and Receipt Routing Methods, Invoice and payment creation, Account Payable's overview, Payable Options, Financial Options, Accounting Periods, Payable System Setups, Payment Terms, Distribution Sets, Defining Suppliers, Standard Invoice, Debit Memo, Credit Memo, Prepayment Invoice, Invoice Created Against PO, Introduction to Expense report, Employee Expense Reports, Withholding Tax Payment, Payment Format, Bank Creations, Bank Account Creation, Bank Branch Creation, overview of multi organization structure, business group setup, Defining Operating units, Defining Inventory organizations, Defining Sub Inventories, Account Receivables Introduction, Key Flex Fields, Sales Tax Location, Territory, Statement Cycle, System Options, Remittance to Address, Payment Terms, Introduction to Dunning Letters, Collectors, Customer Creation, Auto Cash Role Set, Customer Profile Class, Customer Bank Creation, Transaction Types, Invoice Transaction, Credit Memo, Deposit transaction, Bills receivable, Guarantee, Chargeback, transaction Sources, Receipt Classes, Receipt Sources, Creating Invoice, Applying the Receipt to Invoice, Rise the Credit memo, Creating Deposit Invoice and Applying the Deposit to Invoice, Applying the receipt, Miscellaneous Receipts, Remittance Receipts, month ending process, Fixed Assets introduction, Flex Field, System Controls, Financial Year Calendar, Values, Prorate Conversion Calendar, Depreciation Calendar, Book Controls, Depreciation Methods, Asset Categories, Changes, Additions, Create accounting, Asset Transfer, Mass Additions, Retirement, Cash Management, System Parameters, creation of new bank, creation of new branch, creation of bank account, Bank Statements and Reconciliation, Forecasting Vs Reconciliation, Bank Charges, Bank Transfer, Introduction to HRMS.

Who is the target audience?

End Users Business Analysts Sales Consultants Support Engineer Technical Consultant Functional Implementer

Oracle Apps R12 SCM Course Details

Oracle R12 Supply Chain Management (SCM) is a systematic approach to manage the seamless flow of information, materials, and services from raw material suppliers through factories warehouses to the end customer.

Oracle Apps R12 SCM  Consist of Below Modules

Oracle Purchasing

Oracle Inventory Management

Oracle Order Management.

From this course, you get a clear idea of

Procure to Pay Cycle Overview, Overview of Purchasing Process, Integration with other Oracle Applications, Overview of Suppliers, Supplier Information, Supplier Site Information, Defining and Maintaining Items, Item Categories, Item Attributes, Enterprise Structure, Locations, Organizations, Units of Measure and Items, Defining Locations, Inventory Organizations, Establishing Inventory Parameters, Requisition Life cycle, Requisition Structure, Creating and Maintaining, Creation of RFQs and Quotations, Request for Quotation Life cycle, Request for Quotation Life cycle Implementation, Creation of Purchase Orders, Purchase Order Types, Standard Purchase Order Elements, Blanket Purchase Agreements, Contract Purchase, Planned purchase order, Receiving Locations, Receipt Routing, Receipt Processing Methods, Creation of Blanket Purchase Agreement, Creation of Contract Purchase Agreement, Planned Purchase Order, Blind Receiving Transactions, Express Transactions, Unordered Receipts and Cascade transactions, Inventory Overview, Inventory Capabilities, Oracle Inventory Applications Integration, Integration of Oracle Inventory to Financials, Understanding the Receipt to Issue Life cycle, Understanding Receiving Inventory, Defining Inventory Organizations, Understanding the Inventory Organizations, Understanding the Structure of an Inventory Organization, Understanding Locations, Defining Locations, Difference between Master and Child Organization, Units of Measure Overview, Units of Measure Class Overview, Defining Unit of Measure Class, Defining Unit of Measure, Defining Unit of Measure Conversions, Defining and Maintaining Items, Item Attributes and Statuses, Assigning Items to Organizations, Sub inventories, Defining Subinventory, Inventory Controls, Concepts, Explaining Lot Control (Item Level), Lot Control (Org Level), Explaining Serial Control (Item Level), Explaining Serial Control (Org Level), Explaining Revision Control, Explaining Locator Control, Subinventory Transfers, Explaining Move Order, Creating Manual Move Order, Creating Requisition Move Order, Requisition Move Order Approval Process, Transact Move Orders with 2 Methods, Miscellaneous Transactions, Restrict Subinventories, Inter-Org Transfer, Shipping Methods, Shipping Network, Transit times for Shipping Methods, Entering inter-org transfer charge, Entering Freight Charges, Direct Inter-Organisation Transfer, Performing Reorder-point Planning, Performing Min-max Planning, Explaining Cycle Counting, Explaining Physical Inventory, Defining and Maintaining a Cycle Count, Defining Cycle Count Scheduling and Requests, Overview of Oracle Order Management, Order to Cash Life cycle Overview, Ordering Process, Inventory Organizations, Define Shipping Parameters, Customer Creation, Auto Cash Role Set, Customer Profile Class, Customer Bank Creation, Transaction Types, Transaction Sources, Creating Invoice, Creating Deposit Invoice and Applying the Deposit to Invoice, Enter Orders, Managing Order, Drop Shipment Implementation, Back to Back Order Implementation, Pick Release Sales Orders, Ship Confirm a Delivery, Define a Pick Slip Grouping Rule, Define a Release Sequence Rules, Order Management Transaction Setups, Defining Order Transaction Type, Defining Line Transaction Type, Defining Document Sequence, Return Material Authorization, Return With Credit No Receipt, Return With Receipt Only, Return With Receipt, Credit Auto Invoice, Internal Sales Order Implementation..

Six Power-User Tips for Searching My Oracle Support

[Editor: This article from Robert Farrington contains some excellent generic tips for searching My Oracle Support, but please remember that concerns or questions about My Oracle Support should be logged as Service Requests; this blog is intended to help EBS DBAs with technical EBS topics, not to troubleshoot MOS issues.] The My Oracle Support (MOS) knowledge base contains a plethora of information about all of Oracle's products. However, it is not always easy to know how best to locate what you need to solve a problem or make a decision.   

This article provides six tips to help you find what you're looking for. All except the last apply to both the main (Flash-based) site at support.oracle.com, and the HTML-based site at supporthtml.oracle.com. TIP 1: Remember that search terms are ANDed The key point to remember is that search terms you enter are all joined by a logical AND before the search is performed. So it is essential to choose the terms (and their number) carefully. Too few will result in a search that is not selective enough, and will return irrelevant results. On the other hand, too many terms will result in a search that is too restrictive, and which may exclude documents that would have met your needs. TIP 2: Understand how the standard search criteria work When performing a search, knowledge of the following search criteria and options may prove useful, particularly if a simple search does not yield any useful results. (The bold italics are used solely for emphasis in this article.)  

• All searches are case-insensitive. For example: TNS and tns are considered to be the same. So you might as well use lower case.
• Because all search terms are joined with logical AND for processing, tns listener will look for documents that contain both tns and listener.

TIP 3: Use Advanced Searches to narrow the scope By default, My Oracle Support performs a simple search. You can perform an advanced search by choosing Advanced Search from the Sources dropdown menu to the left of the Search Knowledge Base box at the top right of the screen:  

Or you can simply click on the Advanced link at the end of the same line, to open an Advanced Search popup window:  

TIP 4: Use special Advanced Search options As an alternative to using the Advanced Search window shown above, you can enter advanced options with your search terms:

• Use OR by separating the search terms with a vertical bar (same as the UNIX pipe character, but in MOS you must have a space either side of the character). For example: tns | listener will look for documents that contain either tns or listener.
• Enclose a search phrase in quotes to find exact matches only. For example: "tns listener"
• Exclude words with a dash (hyphen), with no space after the dash. For example: tns -listener
• Do not use the plus (+) character to force a term to be included in the results (for example, tns +listener), because (as already stated) an AND search is performed by default.
• Group words with (term1 | term2) term3. For example, (adstrtal.sh | adstpall.sh) autoconfigfinds documents about AutoConfig that also mention either adstrtal.sh or adstpall.sh.

TIP 5:  Understand the hidden search assumptions

• Search uses stemming - that is, if you search for start, documents with starting will also be returned. However, greater weight is placed on an exact match.
• For searches that use multiple terms, proximity is important: the terms must not be separated by more than 100 words. For example, script start application tier services will return all documents containing these five words, provided they are not separated by more than 100 words.
• The asterisk (*) can be used as a wildcard character, but not at the beginning of a search term.

TIP 6: Use PowerView The final power user tip is to use PowerView, which allows you to limit your search to a product of your choice. Note that this option is only available with the main (Flash-based) site, not the HTML-based one. To use this option, you first need to create a new PowerView:  

In the following example, Application Management Pack for Oracle E-Business Suite is being specified as the product of interest:  

You can then switch PowerView on, and the status will change accordingly:  

Tips & Tricks for Navigating Oracle Applications

Tips & Tricks for Navigating Oracle Applications

Tips & Tricks for Navigating Oracle Applications

Help : Keyboard Help or – Lists Hot Keys

Tools : Close Other Forms – check box to auto close forms when a new form is opened

Query Tips and Tricks
to enter query by example
to recall the last query parameters
to Execute Query
Query : Count Matching Records (after putting in the query parameters)
Exit Query

Query Wild Card Characters
% one or more characters
_ exactly one character

Query Operators
#between and 
#is null, #is not null
> Greater Than
>= Greater Than or Equal
< Less Than <= Less Than or Equal = Equal != Not Equal View : Record : First Record or Last Record – shows first or last record returned by query

What SQL Statement did the Oracle Applications run to fill the form? 
Help : Diagnostics : Examine (requires apps password)
Block = SYSTEM
Field = last_query

Other ways to determine the underlying table structure:
1) Help : Record History
2) Trace the form (requires apps password)

XML Publisher
The XML Publisher concurrent program joins the XML concurrent report data with the registered templates to generate PDF output. The following template types are supported: Portable Document Format (PDF), Rich Text Format (RTF) and XSL-FO.
- www.norcaloaug.org, Training Day 2004, Paper by Brent Mosher,
‘Oracle Tech update part 1: XML Publisher/Oracle Applications Framework’
- Metalink Note:269605.1, ‘About Oracle XML Publisher Release 4.5’
- Metalink Note:276691.1, ‘XML Publisher and Concurrent Manager Integration’
- Metalink Note:258332.1, ‘About Oracle XML Publisher Minipack XDO.H’
- Metalink B13817-01, ‘Oracle® XML Publisher User’s Guide Release 11i

Retrieving Data From SAP Applications

Many companies implement SAP ERP system and Warehouse Builder enables easy access to the data in these SAP systems.

This chapter describes how you can retrieve data from an SAP system. It describes why you require an SAP connector, how to import metadata from SAP tables, use them in a mapping, generate ABAP code for the mappings, and deploy them to a SAP system. The chapter also describes the various methods by which you can retrieve data from the SAP system and load this data to a target table on the Warehouse Builder system.

It consists of the following topics:

• "Why SAP Connector"
• "Supported SAP Versions"
• "Overview of SAP Objects"
• "Overview of the Warehouse Builder-SAP Interaction"
• "Implementing an SAP Data Retrieval Mechanism"
• "Connecting to an SAP System"
• "Importing Metadata from SAP Tables"
• "Creating SAP Extraction Mappings"
• "Retrieving Data From the SAP System"

Why SAP Connector

An SAP R/3 system operates differently compared to SQL based systems like EBusiness Suite and PeopleSoft.

The major differences include:

• The native data manipulation language is ABAP, which is a proprietary SAP language.
• Table names are cryptic compared to those in SQL based ERP systems.
• In addition to database tables, SAP contains logical tables called pool tables and cluster tables. These tables contain multiple physical tables and must be managed differently.

The SAP connector assists you in managing all these issues. Furthermore, the SAP connector allows you to comply with the administrative and security processes of the SAP environment.

Supported SAP Versions

For information on the SAP R/3 versions supported by Oracle Warehouse Builder 11g, log in to https://metalink.oracle.com, and navigate to the Certify link.

Overview of SAP Objects

This section provides a brief overview of the different types of tables in SAP, and how data is organized within an SAP system. The section consists of the following topics:

• "SAP Object Types"
• "SAP Business Domains"

SAP Object Types

With the SAP Connector, you can import metadata definitions for the following SAP table types:

• Transparent: A transparent table is a database table that stores data. You can access the table from non-SAP systems as well, for example, using SQL statements. However, Warehouse Builder uses ABAP code to access transparent tables.
• Cluster: A cluster table is usually used to store control data. It can also be used to store temporary data or documentation. Because cluster tables are data dictionary tables and not database tables, you can only access these tables using ABAP.
• Pooled: This is a logical table that must be assigned to a table pool in the database. A table pool consists of multiple pooled tables. A pooled table is used to store control data such as program parameters. You require ABAP code to access pooled tables.

SAP Business Domains

SAP application systems logically group tables under different business domains. In SAP, a business domain is an organizational unit in an enterprise that groups product and market areas. For example, the Financial Accounting (FI) business domain represents data describing financial accounting transactions. These transactions might include General Ledger Accounting, Accounts Payable, Accounts Receivable, and Closing and Reporting.

When you import SAP definitions, you can use a graphical navigation tree in the Business Domain Hierarchy dialog box to search the business domain structure in the SAP source application. This navigation tree enables you to select SAP tables from the SAP application server.

Overview of the Warehouse Builder-SAP Interaction

Moving data from an SAP system to an Oracle database using Warehouse Builder consists of the following tasks:

1. Connecting to an SAP system.
2. Importing metadata from SAP.
3. Creating an extraction mapping in Warehouse Builder that defines:
   • The SAP source tables from which data is to be retrieved
   • The transformation operators that operate on the source tables to retrieve data based on certain criteria
   • The target table in Warehouse Builder to store the data retrieved from the SAP source tables
4. Deploying the mapping.
   This creates the ABAP code for the mapping.
5. Starting the mapping.
   This results in the following sequence of tasks, all of which are performed automatically by Warehouse Builder:
   • Transfer of the ABAP code to the SAP server
   • Compiling of the ABAP code
   • Execution of the ABAP code, which results in the generation of a data file (this file has a .dat extension).
   • Transfer of the data file to the OWB server using FTP.
   • Loading of the target table with the data from the data file using SQL*Loader.
   To execute ABAP code on an SAP system you require SAP Function Modules.

SAP Function Modules

To access SAP data from non-SAP systems, you typically use a function module to execute an ABAP program that retrieves the data. A function module in SAP is a procedure that is defined in a special ABAP program known as function group. Once defined, the function module can then be called from any ABAP program.

SAP contains a predefined function module called RFC_ABAP_INSTALL_AND_RUN to execute ABAP code. To upload the Warehouse Builder generated ABAP code and execute it in SAP, you need access rights to this function module.

Alternately, you can ask the SAP administrator to create a customized function module that executes a specific ABAP program. You can then use this function module to execute the ABAP code generated by Warehouse Builder.

Data Retrieval

Data retrieval from the SAP system can be Completely Managed By Warehouse Builder, Managed By Warehouse Builder With SAP Verification, or Manual depending on whether you have access rights to the predefined function module or the customized function module.

Completely Managed By Warehouse Builder

In this system, Warehouse Builder has access to upload and execute the generated ABAP using the default function module, and to use FTP to retrieve the generated data file from the SAP system.

Thus the entire process of retrieving data from the SAP system and creating a target table is managed by the Warehouse Builder and can be completely automated. It is therefore the simplest method of data retrieval. See "Automated System" for more details on implementing this data retrieval mechanism.

Managed By Warehouse Builder With SAP Verification

In this system, as a Warehouse Builder user, you do not have access rights to the default function module that executes the ABAP code in the SAP system. Instead the SAP administrator first verifies the ABAP code generated by Warehouse Builder, and then creates a customized function module to execute this ABAP code. You can then run the ABAP code on the SAP system using this function module.

See "Semi Automated System" for more details on implementing this data retrieval mechanism.

Manual

In this method, as a Warehouse Builder user, you cannot directly run the ABAP code on the SAP system. Instead, you generate the ABAP code for the mapping, and send it to the SAP administrator, who runs the code on the SAP system. You then retrieve the generated data file and load the target table.

The tasks involved in retrieving data using FTP and creating the Oracle table are implemented using a Process Flow. See "Manual System" for more details on implementing this system.

Implementing an SAP Data Retrieval Mechanism

As a Warehouse Builder user, you need to be aware of certain restrictions while trying to retrieve data from an SAP system.

Since the SAP and Oracle Warehouse Builder systems are totally independent systems, as a Warehouse Builder user, you may only have restricted access rights to the SAP data (especially in the production environment). You will have to interact with the SAP administrator to retrieve data from the system.

Access rights to the SAP system is most often determined by whether it is the development, test, or the production environment. Each of these data retrieval mechanisms can be implemented in the development, test, or production environment depending on the privileges granted by the SAP system administrator.

Development Environment

Usually, you can access the SAP development environment using the predefined function module RFC_ABAP_INSTALL_AND_RUN. As a result, you can implement a completely Automated System for data retrieval.

Test and Production Environment

In the test and production environments, you are not usually given access rights to use the predefined function module. Instead, the SAP administrator verifies the ABAP code, and either creates a customized function module that you can use, or runs the ABAP code on the SAP system, and allows you to retrieve the resultant data. You can therefore implement either a Semi Automated System or a Manual System for data retrieval.

A typical data retrieval system consists of any of the three mechanisms implemented in the different environments.

Scenario 1

You run the automated system in the SAP development environment. Once you verify the ABAP code in this environment, you then move the ABAP code to the SAP test environment and test the code using a customized function module. You then finally move this to the SAP production environment.

This implementation is recommended by Oracle, as it automates and simplifies the data retrieval task.

Scenario 2

Depending on the access rights to the development, test, and production environments, you implement any one of the data retrieval mechanisms.

The following sections provide details of the tasks involved in retrieving data from an SAP system:

1. "Connecting to an SAP System"
2. "Importing Metadata from SAP Tables"
3. "Creating SAP Extraction Mappings"
4. "Retrieving Data From the SAP System".

Connecting to an SAP System

To connect to an SAP system from Warehouse Builder, you require certain SAP-specific DLL files. Once you establish connection, you can then import metadata from SAP tables into SAP modules in Warehouse Builder.

This section contains the following topics:

• Required Files For SAP Connector
• Creating SAP Module Definitions
• Troubleshooting Connection Errors
• Creating SAP Module Definitions

Required Files For SAP Connector

Different sets of files are required depending on whether you are working on a Windows or an Unix system.

Files Required In Windows

The SAP Connector requires a dynamic link library file named librfc32.dll to use remote function calls on the client computer. You must copy librfc32.dll to the location specified in java.library.path on your client system.

To find this location, click MyComputer, Properties, and then click Advanced. Next click Environment Variables, and under System variables, check the locations specified for the variable Path.

You can copy the librfc32.dll file to any one of the multiple locations specified in Path. One of the locations will correspond to OWB_ORACLE_HOME, and is therefore the preferred location. This location is usually OWB_ORACLE_HOME\owb\bin.

See Table 7-1 for the list of files required in Windows.

Table 7-1 Required Files for Windows

Required Files	Path	Description
librfc32.dll	OWB_ORACLE_HOME\owb\bin	This file is available on the SAP Application Installation CD.
sapjcorfc.dll	OWB_ORACLE_HOME\owb\bin	Copy this file to the same location where you placed librfc32.dll
sapjco.jar	OWB_ORACLE_HOME\owb\lib\int

Make sure that you restart the client after copying these files.

Files Required In Unix

The SAP Connector requires a dynamic link library file named librfccm.so to use remote function calls on the client computer. You need to copy this file to the location specified by the Unix environment variable path LD_LIBRARY_PATH on your client system.

By default, OWB_ORACLE_HOME/owb/bin/admin is the location specified in LD_LIBRARY_PATH. If it is not, then make sure to add OWB_ORACLE_HOME\owb\bin\admin to LD_LIBRARY_PATH.

See Table 7-2 for the list of files required in Unix.

Table 7-2 Required Files for Unix

Required Files	Path	Description
librfcccm.so	OWB_ORACLE_HOME\owb\bin\admin	This file is available on the SAP Application Installation CD.
libsapjcorfc.so	OWB_ORACLE_HOME\owb\bin\admin	Copy this file to the same location where you placed librfcccm.so
sapjco.jar	OWB_ORACLE_HOME\owb\lib\int

Make sure that you restart the client after copying the files.

Note:

Different versions of SAP R/3 might require different versions of the DLL, SO, and JAR files. The correct versions are available in the SAP installation CD. The files can also be downloaded from:

http://service.sap.com/patches

Troubleshooting Connection Errors

The most common errors while connecting to an SAP system are listed in Table 7-3:

Table 7-3 SAP Connection Errors

Error Message	Possible Reason
Connection failed.You are not authorized to logon to the target system (error code 1).	Incorrect User Name or Password to connect to the SAP server.
Connection failed. Connect to SAP gateway failed.	Incorrect Application Server, System Number, or Client details.
Some Location Details are missing. Please verify the location information is completely specified.	Missing DLL files, or DLL files placed in the wrong location.
Missing saprfc32.dll	Missing saprfc32.dll file or the file placed in the wrong location.

Note:

If you create an SAP source module and import SAP tables but cannot see the columns in the tables, then you have an incompatible librfc32.dll file. Download the correct version of the DLL file from the SAP Website.

Creating SAP Module Definitions

Use the Create Module Wizard to create an SAP source module that stores data from an SAP source. You can choose either SAP R/3 version 3.x or SAP R/3 version 4.x system type as your source. After you select the application version, you need to set the connection information between Warehouse Builder and the SAP application server. You can set the connection either by selecting from existing SAP locations or by creating a new SAP location as defined in "Connecting to an SAP System".

Note:

Before you begin the task of creating a SAP location, ensure that you have all the necessary information to create the location. You can provide the location information either while creating the module or before importing metadata into the module. You need the following information to create the location: The server name, the user name, password, system number, and client number. Obtain these details from your system administrator.

When you set the connection information, you can choose the following connection types:

Remote Function Call (RFC)

A remote function call enables you to call a function module on a remote system. This method requires specific IP Address information for the SAP application server.

SAP Remote Function Call (SAPRFC.INI)

You can also specify the connection information in a file called SAPRFC.INI, and copy this file to the following location: OWB_ORACLE_HOME\owb\bin\admin.

Using the SAPRFC.INI file requires prior knowledge of ABAP parameters, as you need to specify the values for certain parameters to make a SAP connection, and is not the recommended connection method if you are not familiar with ABAP.

Note:

The SAPRFC.INI file comes with the SAP installation CD.

The Create Module Wizard creates the module for you based on the metadata contained in the SAP application server.

Connecting to an SAP System

1. Select one of the following connection types:
   • Remote Function Call (RFC)
     This is the recommended connection type, and is selected by default in Warehouse Builder.
   • SAP Remote Function Call (SAPRFC.INI)
   For more information about these connection types, see "Creating SAP Module Definitions".
2. Type the connection information in the appropriate fields. The fields displayed on this page depend on the connection type you choose.
   Note:

   Make sure that you have copied the DLL files to the right location. For more information, see "Required Files For SAP Connector".
   You must obtain the connection information to your SAP Application server from your system administrator before you can complete this step.
   RFC Connection type requires the following connection information:
   Application Server: The alias name or the IP address of the SAP application server.
   System Number: The SAP system number. This must be provided by the SAP system administrator.
   Client: The SAP client number. This must be provided by the SAP system administrator.
   User Name: The user name with access rights to the SAP system. This name is supplied by the SAP system administrator.
   Language: EN for English or DE for German. If you select DE, the description text displays in German and all other text displays in English.
   SAPRFC connection type requires the following connection information:
   RFC Destination: Type the alias for the SAP connection information.
   In addition, both the connection types require the following connection information if the ABAP code is to be executed in SAP using a function module and the data file is to be transferred by FTP to Warehouse Builder:
   Host Login User Name: A valid user name on the system that hosts the SAP application server. This user must have access rights to copy the data file using FTP.
   FTP Directory: The directory where the data file retrieved from the SAP system is stored. For systems where the ftp directory structure is identical to the operating system directory structure, this field can be left blank. For systems where the file system directory structure is mapped to the ftp directory structure, enter the ftp directory path that is mapped to staging file directory in the file system directory structure. For example, on a computer that runs Windows, the staging file directory "C:\temp" is mapped to "/" in the FTP directory structure, then enter "/" in this field.
   Execution Function Module: In a SAP instance, if a remote function module other than the SAP delivered function module: RFC_ABAP_INSTALL_AND_RUN is used to remotely execute ABAP reports through RFC connections, then enter the remote function module name here.
3. Click Test Connection to verify that the connection information you provided are correct.
4. Click OK to go back to the Connection Information page of the Create Module wizard.

Importing Metadata from SAP Tables

Once you establish a connection with the SAP server, you can import metadata from SAP tables.

This section contains the following topics:

• Importing SAP Metadata Definitions
• Analyzing Metadata Details

Importing SAP Metadata Definitions

After creating the SAP source module, you can import metadata definitions from SAP tables using the Import Metadata Wizard. This wizard enables you to filter which SAP tables to import, verify those tables, and reimport them. You can import metadata for transparent tables, cluster tables, or pool tables.

Perform the following steps to import SAP metadata:

1. From the Project Explorer, expand the Applications node.
2. Right-click the SAP source module into which you want to import metadata and select Import.
   Warehouse Builder displays the Welcome page for the Import Metadata Wizard.
3. Click Next.
4. Complete the following tasks:
   • Filtering SAP Metadata
   • Selecting Objects for Metadata Import
   • Reviewing Import Summary

Filtering SAP Metadata

You can filter objects to import by business domain or by text strings. Select a filtering method and click Next.

Filtering SAP Metadata by Business Domain

1. Select Business Domain and click Browse to display the SAP R/3 Business Domain Hierarchy dialog box.
   The Import Metadata wizard displays the Loading Progress dialog box while it is retrieving the business domains.
2. The Business Domain Hierarchy dialog box lists the available SAP business domains.
   Note:

   It may take a few minutes to list the business domains depending on the network location of the SAP application server, the type of LAN used, or the size of the SAP application database.
   Use the Business Domain Hierarchy dialog box to select the SAP business domains that contain the metadata tables you want to import.
3. Select a folder and click Show Tables to view the tables available in a business domain.
   The Import Wizard displays a list of tables in the selected business domain in the Folder dialog box.
4. Review this dialog box to ensure that you are selecting the required tables.
   Some business domains can contain more than 1000 tables. Importing such a large amount of metadata can take time, depending on the network connection speed and the processing power of the source and target systems.
5. Click OK.
   The wizard displays the Filter Information page with the SAP business domain displayed in the Business Domain field.

Filtering SAP Metadata by Text String

1. Select Text String, where object and use the Name matches or Description matches entry field to type a string and obtain matching tables from the SAP data source.
   The Description matches field is case sensitive, the Name matches field is not.
   Create a filter for object selection by using the wildcard characters % for zero or more matching characters, and _ for a single matching character.
   For example, if you want to search the business domain for tables whose descriptions contain the word CURRENCY, then select Description matches and type %CURRENCY%. You can also search for tables by their names.
2. Specify the number of tables you want to import in the Maximum number of objects displayed field.

Selecting Objects for Metadata Import

The Object Selection page contains a description of the tables and enables you to select the tables you want to import into the SAP module. To select the tables:

1. Move the tables from the available list to the selected list.
   The Import Metadata Wizard also enables you to choose whether you want to import tables with foreign key relationships for each table that you choose to import. You can select one of the following:
   None: Import only the tables in the Selected list.
   One Level: Import the tables in the Selected list and any tables linked to them directly through a foreign key relationship.
   All Levels: Import the tables in the Selected list and all tables linked to them hrough foreign key relationships.
2. Click Next.
   If you select One Level or All Levels, the Confirm Import Selection dialog box is displayed.
   Review this dialog box to ensure that you are selecting the required tables.
3. Click OK.
   The selected tables appear in the Selected list of the Table Selection page.
4. Click Next.
   The wizard displays the Summary and Import page.

Reviewing Import Summary

The wizard imports the definitions for the selected tables from the SAP Application Server, stores them in the SAP source module, and then displays the Summary and Import page.

You can edit the descriptions for each table by selecting the Description field and typing a new description.

Review the information on the Summary and Import page and click Finish.

The SAP Connector reads the table definitions from the SAP application server and creates the metadata objects in the workspace.

The time it takes to import the SAP metadata into the workspace depends on the size and number of tables and the connection between the SAP application server and the workspace. It is a best practice to import small batches of tables to allow better performance.

When the import completes, the Import Results dialog box displays. Click OK to finish importing metadata.

Reimporting SAP Tables

To reimport SAP tables, follow the importing procedure using the Import Metadata Wizard. Prior to starting the import, the wizard checks the source for tables with the same name as those you are importing. The tables that have already been imported appear in bold in the Object Selection page. On the Summary and Import page, the Action column indicates that these tables will be reimported. The wizard then activates the Advanced Synchronize Options button so that you can control the reimport options.

Note:

If you wish to undo the reimport, click Undo. This ensures that no changes are made to the existing metadata.

Analyzing Metadata Details

With SAP tables, you cannot view the data after you import the metadata from these tables. However, you can get a good insight about the data that is stored in the tables by viewing the Column Descriptions and the Constraints Details.

Column Descriptions

You can view the column description of each of the columns in a table. This is valuable because the column names in SAP can be non-descriptive, and difficult to interpret if you have not previously seen the data in the table.

To view the descriptions, double-click the table to open the object editor for the table, and then click the Columns editor. The description for the columns of the table are visible as shown in Figure 7-1.

Figure 7-1 The Columns Editor with the Description for the Columns of SAP Table

Description of "Figure 7-1 The Columns Editor with the Description for the Columns of SAP Table"

Constraints Details

The other benefit of data object editor is that you can get information on the primary and foreign keys within the table. To view the key constraints, click the Constraints editor.

Note:

It is also a useful practice to display the business names of the SAP tables in the Project Explorer. Business names provide a description of the tables and are therefore more intuitive than the physical names. To view the business names for tables in Warehouse Builder, from the main menu, click Tools, Preferences, Naming, and then select Business Names in the Naming Mode field.

Creating SAP Extraction Mappings

After importing metadata from SAP tables, you must define the extraction mapping to retrieve data from the SAP system.

Note:

For details of mappings in Warehouse Builder,

Defining an SAP Extraction Mapping

You can use the Mapping Editor to create a mapping containing SAP tables. Creating a mapping with SAP tables is similar to creating mappings with other database objects. However, there are restrictions on the operators that can be used in the mapping. You can only use Table, Filter, Joiner, and Mapping Input Parameter mapping operators in a mapping containing SAP tables.

A typical SAP extraction mapping consists of one or more SAP source tables (transparent, cluster, or pooled), one or more filter or joiner operators, and a non-SAP target table (typically an Oracle table) to store the retrieved data, as shown in Figure 7-2.

Note:

You cannot have both SAP and non-SAP (Oracle) source tables in a mapping. The staging table though is an Oracle table.

Figure 7-2 SAP Extraction Mapping

Description of "Figure 7-2 SAP Extraction Mapping"

In this example, the Input Parameter holds a Date value, and the data from table BKPF is filtered based on this date. The Joiner operator enables you to join data from multiple tables, and the combined data set is stored in a staging table.

This section contains the following topics:

• Adding SAP Tables to the Mapping
• Setting the Loading Type
• Setting Configuration Properties for the Mapping

Adding SAP Tables to the Mapping

To add an SAP table to a mapping:

On the Mapping Editor drag and drop the required SAP table onto the Mapping Editor canvas.

The editor places a Table operator on the mapping canvas to represent the SAP table.

Setting the Loading Type

Use the Operator properties panel of the Mapping Editor to set the SQL*Loader properties for the tables in the mapping.

To set the loading type for an SAP Source Table:

1. On the Mapping Editor, select the SAP source table. The Table Operator Properties panel displays the properties of the SAP table operator.
2. Select a loading type from the Loading Type list. With ABAP code as the language for the mapping, the SQL*Loader code is generated as indicated in Table 7-4.
   Table 7-4 SQL*Loader Code Generated in ABAP

   Loading Type	Resulting Load Type in SQL*Loader
   INSERT	APPEND
   CHECK/INSERT	INSERT
   TRUNCATE/INSERT	TRUNCATE
   DELETE/INSERT	REPLACE
   All other types	APPEND

Setting Configuration Properties for the Mapping

• Use the Configuration Properties dialog box to define the code generation language as described in Setting the Language Parameter.
• Set ABAP specific parameters, and the directory and initialization file settings in the Configuration Properties dialog box as described in Setting the Runtime Parameters.

Setting the Language Parameter

This parameter enables you to choose the type of code you want to generate for a mapping. For mappings containing SAP source tables, Warehouse Builder automatically sets the language parameter to ABAP.

Setting the Runtime Parameters

With the language set to ABAP, you can expand the Runtime Parameters node in the Configuration Properties dialog box to display settings specific to ABAP code generation.

Some of these settings come with preset properties that optimize code generation. It is recommended that these settings be retained, as altering them may slow down the code generation process.

The following Runtime parameters are available for SAP mappings:

• Background Job: Select this option if you wish to run the ABAP report as a background job in the SAP system. Enable this option for the longer running jobs. Foreground batch jobs that run for a long duration are considered hanging in SAP after a certain time. Therefore it is ideal to have background job running for such extracts.
• File Delimiter for Staging File: Specifies the column separator in a SQL data file.
• Data File Name: Specifies the name of the data file that is generated when the ABAP code for the mapping is run in the SAP system.
• SQL Join Collapsing: Specifies the following hint, if possible, to generate ABAP code.

  SELECT < > INTO < > FROM (T1 as T1 inner join T2 as T2) ON <condition > 
  

  The default setting is TRUE.
• Primary Foreign Key for Join: Specifies the primary key to be used for a join.
• ABAP Report Name: Specifies the name of the ABAP code file generated by the mapping. This is required only when you are running a custom function module to execute the ABAP code.
• SAP System Version: Specifies the SAP system version number to which you want to deploy the ABAP code. For MySAP ERP and all other versions, select SAP R/3 4.7. Note that different ABAP code is required for versions prior to 4.7.
• Staging File Directory: Specifies the location of the directory in the SAP system where the data file generated by ABAP code resides.
• SAP Location: The location of the SAP instance from where the data can be extracted.
• Use Select Single: Indicates whether Select Single is generated, if possible.
• Nested Loop: Specifies a hint to generate nested loop code for a join, if possible.

Setting the Join Rank

You need to set this parameter only if the mapping contains the Joiner operator, and you wish to explicitly specify the driving table. Unlike SQL, ABAP code generation is rule based. Therefore, you must design the mapping in such a way that the tables are loaded in the right order. Or you can explicitly specify the order in which the tables have to be joined. To do this, from the Configuration Properties dialog box, expand Table Operators, and then for each table, specify the Join Rank. The driving table must have the Join Rank value set to 1, with increasing values for the subsequent tables.

You can also let Warehouse Builder decide the driving table, as well as the order of joining the other tables. In such cases, do not enter values for Join Rank.

Retrieving Data From the SAP System

After designing the extraction mapping, you must validate, generate, and deploy the mapping, as you do with all mappings in Warehouse Builder.

To generate the script for the SAP mapping:

1. Right-click the SAP mapping and select Generate.
   The Generation Results window is displayed.
2. On the Script tab, select the script name and select View Code.
   The generated code is displayed in the Code Viewer.
   You can edit, print, or save the file using the code editor. Close the Code Viewer to return to the Generation Results window.
3. To save the file, click Save as File and save the ABAP program to your hard drive.

After you generate the SAP mapping, you must deploy the mapping to create the logical objects in the target location. To deploy an SAP mapping, right-click the mapping and select Deploy. You can also deploy the mapping from Control Center Manager.

For detailed information about deployment, see "Deploying to Target Schemas and Executing ETL Logic" in Oracle Warehouse Builder Transformation Guide.

When an SAP mapping is deployed, an ABAP mapping is created and stored in the Warehouse Builder runtime schema. Warehouse Builder also saves the ABAP file under OWB_ORACLE_HOME\owb\deployed_files, where OWB_ORACLE_HOME is the location of the Oracle home directory of your Warehouse Builder installation. Note that if you are using the Warehouse Builder installation that comes with Oracle Database, then this is the same as the database home.

Depending on whether data retrieval from the SAP system is fully automated, semi-automated, or manual, you need to carry out the subsequent tasks. This section consists of the following topics:

• "Automated System"
• "Semi Automated System"
• "Manual System"

Automated System

In a completely automated system, as a Warehouse Builder user you have access to the predefined function module in the SAP system. This allows you to execute any ABAP code and retrieve data directly from the SAP system without being dependent on the SAP administrator, as shown in Figure 7-3.

Figure 7-3 Automated Data Retrieval

Description of "Figure 7-3 Automated Data Retrieval"

Because there is no dependence, you can automate the process of sending the ABAP code to the SAP system and retrieving the data file from the SAP system. Warehouse Builder will then use FTP to transfer the data file to the Warehouse Builder system, and load the target file with the retrieved data using SQL*Loader.

An automated system works as follows:

1. You design the extraction mapping and generate the ABAP code for this mapping.
2. Before deploying the mapping, ensure that you have set the following configuration properties for the mapping:
   • ABAP Report Name: The file that stores the ABAP code generated for the mapping.
   • SAP Location: The location on the SAP system from where data is retrieved.
   • Data File Name: Name of the data file to store the data generated by the execution of ABAP code.
   Also ensure that you have provided the following additional connection details for the SAP location:
   • Execution Function Module: Provide the name of the predefined SAP function module. Upon execution, this function module will take the ABAP report name as the parameter, and execute the ABAP code.
   • FTP Directory: The directory on the Warehouse Builder system. The data file generated upon the execution of the function module will be sent using FTP to this directory.
   • Also provide a username who has write permissions on the FTP directory.
3. You then start the mapping, following which the following tasks are automatically performed:
   • Warehouse Builder deploys the ABAP and uses RFC_ABAP_INSTALL_AND_RUN to both load the ABAP and execute it in SAP.
     The ABAP code is sent to the SAP system using a Remote Function Call (RFC).
4. In the SAP system, the code retrieves data from the source tables and creates a data file.
   This data file is stored in the location specified by Staging File Directory.
5. Warehouse Builder uses FTP to transfer this data file back to the Warehouse Builder system.
   The file is stored in the location specified in the FTP Directory field.
6. Using SQL*Loader, Warehouse Builder loads the target table in the mapping with the data from the data file.

The advantage of this system is that you can create a fully automated end-to-end solution to retrieve SAP data. As a user, you just create the extraction mapping and run it from Warehouse Builder, which then creates the ABAP code, sends it to the SAP system, retrieves the resultant data file, and loads the target table with the retrieved data.

Semi Automated System

In a semi automated system, as a Warehouse Builder user, you do not have access to the predefined function module, and therefore cannot use this function module to execute ABAP code. You create an extraction mapping, deploy it, and then send the ABAP code to the SAP administrator who verifies the code before allowing you to run it in the SAP system, as shown in Figure 7-4.

Figure 7-4 Semi Automated Implementation

Description of "Figure 7-4 Semi Automated Implementation"

A semi automated system works as follows:

1. You design the extraction mapping and generate the ABAP code for this mapping.
2. You then transport the ABAP code to the test system to test the code.
3. You then send the ABAP code to the SAP administrator, who loads it to the SAP repository.
4. The SAP administrator creates a new ABAP Report Name.
5. You can then call this ABAP Report Name to execute the ABAP code in the production environment.
6. Before you run the mapping in the SAP system, ensure that you have set the following configuration properties for the mapping:
   • ABAP Report Name: The SAP administrator will provide the report name after verifying the ABAP code. You will then execute this ABAP file.
   • SAP Location: The location on the SAP system from where data is retrieved.
   • Data File Name: Name of the data file to store the data generated during execution of ABAP code.
   Also ensure that you have provided the following additional connection details for the SAP location:
   • Execution Function Module: Provide the name of the custom function module created by the SAP administrator. On execution, this function module takes the ABAP report name as the parameter, and executes the ABAP code. You must obtain the function module name from the SAP administrator.
   • FTP Directory: A directory on the Warehouse Builder system. The data file generated by the execution of the ABAP code is sent using FTP to this directory.
   • Also provide a username who has Write permissions on the FTP directory.
7. In the production environment, when you run the mapping, Warehouse Builder generates the ABAP code and sends it to the SAP system using a Remote Function Call (RFC).
8. In the SAP system, the ABAP code gets executed using the customized function module and a data file is generated.
   This data file is stored in the location specified by Staging File Directory.
9. Warehouse Builder uses FTP to transfer this data file back to the Warehouse Builder system.
   The file is stored in the location specified in the FTP Directory field.
10. Warehouse Builder uses SQL*Loader to load the target table with data from the data file.

Manual System

In a manual system, your role as a Warehouse Builder user is restricted to generating the ABAP code for the mapping, and sending the ABAP code to the SAP administrator. The tasks involved in this system are:

1. You create an extraction mapping, and generate the ABAP code for the mapping.
2. While designing the mapping, make sure that you specify the Data File Name to store the data file.
3. You send the ABAP code to the SAP administrator.
4. The SAP administrator executes the ABAP code in the SAP system.
5. On execution of the code, a data file is generated.

On the Warehouse Builder end, you can create a Process Flow to retrieve the data file. The Process Flow must contain the following activities as shown in Figure 7-5:

Figure 7-5 Process Flow to Retrieve SAP Data

Description of "Figure 7-5 Process Flow to Retrieve SAP Data"

1. A File Exists activity to check for the presence of the data file.
2. If the file exists, then an FTP activity transfers the file to the Warehouse Builder system.
3. If the file does not exist, then it must wait till the file is made available, and then perform an FTP.
4. Using SQL*Loader, the target table is loaded with data from the data file.

In certain applications, the SAP administrator may not allow any other user to access the SAP system. In such cases, implementing the manual system may be the only viable option.